[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_STND_TAX_PROCESS
Source
1 PACKAGE BODY JAI_AP_STND_TAX_PROCESS AS
2 --$Header: jaiapprcb.pls 120.6.12010000.2 2008/11/24 03:13:38 huhuliu ship $
3 --|+======================================================================+
4 --| Copyright (c) 2007 Oracle Corporation Redwood Shores, California, USA |
5 --| All rights reserved. |
6 --+=======================================================================+
7 --| FILENAME |
8 --| JAI_AP_STND_TAX_PROCESS.plb |
9 --| |
10 --| DESCRIPTION |
11 --| This package offer funcitons to calculate tax amount and creat |
12 --| tax lines. Also it provide the tax modification and delete |
13 --| functionalities |
14 --| |
15 --| |
16 --| |
17 --| PROCEDURE LIST |
18 --| FUNCTION Get_Max_Invoice_Line_Number |
19 --| FUNCTION Get_Max_Tax_Line_Number |
20 --| FUNCTION Get_Gl_Account_Type |
21 --| PROCEDURE Get_Tax_Cat_Serv_Type |
22 --| PROCEDURE Get_Invoice_Header_Infor |
23 --| PROCEDURE Delete_Tax_Lines |
24 --| PROCEDURE Delete_Useless_Lines |
25 --| PROCEDURE Populate_Stnd_Inv_Taxes |
26 --| PROCEDURE Default_Calculate_Taxes |
27 --| PROCEDURE Create_Tax_Lines |
28 --| PROCEDURE Insert_Tax_Distribution_Lines |
29 --| PROCEDURE Delete_Tax_Distribution_Lines |
30 --| PROCEDURE Allocate_Tax_Dist_Lines |
31 --| FUNCTION Validate_Item_Dist_Lines |
32 --| FUNCTION Get_Pr_Processed_Flag |
33 --| FUNCTION Get_Max_Doc_Source_Line_Id |
34 --| FUNCTION Validate_3rd_party_cm_Invoice |
35 --| HISTORY |
36 --| 2007/08/23 Eric Ma Created |
37 --| 2007/12/24 Eric Ma for inclusive tax |
38 --| 2008/01/25 Eric Ma for Bug#6770835 File verison 120.1 |
39 --| 2008/01/28 Eric Ma for Bug of not deleting tax lines |
40 --| 2008/01/29 Eric Ma for Bug#6784111 |
41 --| 2008/02/18 Eric Ma Changed Create_Tax_Lines for bug#6824857 |
42 --| 2008/03/19 Eric Ma Changed Populate_Stnd_Inv_Taxes for bug#6898716
43 --| 2008/04/23 Eric Ma Code change in Populate_Stnd_Inv_Taxes for bug6923963
44 --| 2008/11/21 Walton liu Code change in Create_Tax_Lines for bug#7202316
45 --+======================================================================*/
46
47 --==========================================================================
48 -- FUNCTION NAME:
49 --
50 -- Validate_Item_Dist_Lines Private
51 -- If any item doesn't have a distribution line of a given invoice id,
52 -- the function will return FALSE,otherwise it returns TRUE.
53 -- DESCRIPTION:
54 --
55 --
56 -- PARAMETERS:
57 -- In: pn_invoice_id IN NUMBER invoice id
58 --
59 -- Out: RETURN BOOLEAN
60 --
61 -- PRE-COND : invoice item line and tax lines exist
62 -- EXCEPTIONS:
63 --
64 --===========================================================================
65 FUNCTION Validate_Item_Dist_Lines
66 ( pn_invoice_id IN NUMBER)
67 --, pn_line_number IN NUMBER)--According eakta's require,added a parameter by Jia Li for inclusive tax on 2008/01/25,commented out for a bug on Jan 28,2008
68 RETURN BOOLEAN
69 IS
70 CURSOR get_dist_line_number_cur IS
71 SELECT
72 aila.line_number
73 FROM
74 AP_INVOICE_LINES_ALL aila
75 , Ap_Invoice_Distributions_All aida
76 WHERE aila.INVOICE_ID = aida.invoice_id (+) --rollback to original logic
77 AND aila.line_number = aida.invoice_line_number (+) --rollback to original logic
78 AND aila.invoice_id = pn_invoice_id
79 --AND aila.line_number = pn_line_number -- Added by Jia Li for inclusive tax on 2008/01/25
80 --,commented out for the bug of deleting not working on Jan 28,2008
81 AND aila.line_type_lookup_code = GV_CONSTANT_ITEM -- ;
82 AND aida.invoice_line_number IS NULL
83 AND aida.invoice_id IS NULL ;
84
85 ln_line_number NUMBER ;
86 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
87 ln_proc_level NUMBER := FND_LOG.level_procedure;
88 lv_proc_name VARCHAR2 (100) := 'Validate_Item_Dist_Lines';
89
90 BEGIN
91
92 IF ( ln_proc_level >= ln_dbg_level)
93 THEN
94 FND_LOG.STRING ( ln_proc_level
95 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
96 , 'Enter procedure'
97 );
98 FND_LOG.STRING ( ln_proc_level
99 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
100 , 'pn_invoice_id ' || pn_invoice_id
101 );
102 END IF;
103
104 OPEN get_dist_line_number_cur;
105 FETCH get_dist_line_number_cur
106 INTO
107 ln_line_number;
108 CLOSE get_dist_line_number_cur;
109
110 --log for debug
111 IF ( ln_proc_level >= ln_dbg_level)
112 THEN
113
114 FND_LOG.STRING ( ln_proc_level
115 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
116 , 'ln_line_number ' || NVL(ln_line_number,-99)
117 );
118
119 FND_LOG.STRING ( ln_proc_level
120 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
121 , 'Exit Function'
122 );
123 END IF; --( ln_proc_level >= ln_dbg_level )
124
125 /* IF (ln_line_number IS NOT NULL)
126 THEN
127 RETURN TRUE;
128 ELSE
129 RETURN FALSE;
130 END IF;
131 */
132 --rollback to original logic
133
134 IF (ln_line_number IS NULL)
135 THEN
136 RETURN TRUE;
137 ELSE
138 RETURN FALSE;
139 END IF;
140
141 EXCEPTION
142 WHEN OTHERS THEN
143 IF ( ln_proc_level >= ln_dbg_level)
144 THEN
145 FND_LOG.STRING ( ln_proc_level
146 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
147 || '. Other_Exception '
148 , SQLCODE || ':' || SQLERRM
149 );
150 END IF; --( ln_proc_level >= ln_dbg_level) ;
151
152 RETURN FALSE;
153 END Validate_Item_Dist_Lines;
154
155 --==========================================================================
156 -- FUNCTION NAME:
157 --
158 -- Validate_3rd_party_cm_Invoice Private
159 -- As the defualt service tax should not be created for CM of
160 -- 3rd invoice, add this function to avoid the tax generation.
161 -- DESCRIPTION:
162 --
163 --
164 -- PARAMETERS:
165 -- In: pn_invoice_id IN NUMBER invoice id
166 --
167 -- Out: RETURN BOOLEAN
168 --
169 -- PRE-COND : invoice item line and tax lines exist
170 -- EXCEPTIONS:
171 --
172 --===========================================================================
173 FUNCTION Validate_3rd_party_cm_Invoice ( pn_invoice_id IN NUMBER)
174 RETURN BOOLEAN
175 IS
176
177 CURSOR get_invoice_id_cur IS
178 SELECT
179 aila.invoice_id
180 FROM
181 AP_INVOICES_ALL aila
182 WHERE aila.invoice_id = pn_invoice_id
183 AND aila.INVOICE_NUM LIKE 'ITP-CM/%'
184 AND aila.description LIKE 'Credit Memo for inclusive 3rd party taxes for receipt%'
185 AND aila.SOURCE = 'INDIA TAX INVOICE'
186 AND aila.invoice_type_lookup_code ='CREDIT';
187
188 ln_invoice_id AP_INVOICES_ALL.invoice_id%TYPE;
189 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
190 ln_proc_level NUMBER := FND_LOG.level_procedure;
191 lv_proc_name VARCHAR2 (100) := 'Validate_3rd_party_cm_Invoice';
192 BEGIN
193
194 IF ( ln_proc_level >= ln_dbg_level)
195 THEN
196 FND_LOG.STRING ( ln_proc_level
197 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
198 , 'Enter procedure'
199 );
200 FND_LOG.STRING ( ln_proc_level
201 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
202 , 'pn_invoice_id ' || pn_invoice_id
203 );
204 END IF;
205
206 OPEN get_invoice_id_cur;
207 FETCH get_invoice_id_cur
208 INTO ln_invoice_id;
209 CLOSE get_invoice_id_cur;
210
211 IF ( ln_proc_level >= ln_dbg_level)
212 THEN
213
214 FND_LOG.STRING ( ln_proc_level
215 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
216 , 'ln_invoice_id ' || NVL(ln_invoice_id,-99)
217 );
218
219 FND_LOG.STRING ( ln_proc_level
220 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
221 , 'Exit Function'
222 );
223 END IF; --( ln_proc_level >= ln_dbg_level )
224
225 IF (ln_invoice_id IS NOT NULL)
226 THEN
227 RETURN TRUE;
228 ELSE
229 RETURN FALSE;
230 END IF;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 IF ( ln_proc_level >= ln_dbg_level)
235 THEN
236 FND_LOG.STRING ( ln_proc_level
237 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
238 || '. Other_Exception '
239 , SQLCODE || ':' || SQLERRM
240 );
241 END IF; --( ln_proc_level >= ln_dbg_level) ;
242
243 RETURN FALSE;
244 END Validate_3rd_party_cm_Invoice;
245
246
247 --==========================================================================
248 -- PROCEDURE NAME:
249 --
250 -- Insert_Tax_Distribution_Lines Private
251 --
252 -- DESCRIPTION:
253 -- Insert tax distribution lines. The allocation numbers of tax line should
254 -- be same as the numbers of coressponding item lines
255 --
256 -- PARAMETERS:
257 -- In: pn_invoice_id IN NUMBER invoice id
258 -- pn_invoice_line_number IN NUMBER line number
259 -- pn_item_allocation_number IN NUMBER item line allocation numbers
260 -- pn_tax_allocation_number IN NUMBER tax line allocation numbers
261 -- Out:
262 --
263 -- PRE-COND : invoice item line and tax lines exist
264 -- EXCEPTIONS:
265 --
266 --===========================================================================
267
268
269 PROCEDURE Insert_Tax_Distribution_Lines
270 ( pn_invoice_id IN NUMBER
271 , pn_invoice_line_number IN NUMBER
272 , pn_item_allocation_number IN NUMBER
273 , pn_tax_allocation_number IN NUMBER
274 )
275 IS
276
277 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
278 ln_proc_level NUMBER := FND_LOG.level_procedure;
279 lv_proc_name VARCHAR2 (100) := 'Insert_Tax_Distribution_Lines';
280 BEGIN
281
282 --log for debug
283 IF ( ln_proc_level >= ln_dbg_level)
284 THEN
285 FND_LOG.STRING ( ln_proc_level
286 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
287 , 'Enter procedure'
288 );
289 FND_LOG.STRING ( ln_proc_level
290 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
291 , 'pn_invoice_id ' || pn_invoice_id
292 );
293 FND_LOG.STRING ( ln_proc_level
294 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
295 , 'pn_invoice_line_number ' || pn_invoice_line_number
296 );
297 FND_LOG.STRING ( ln_proc_level
298 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
299 , 'pn_item_allocation_number ' || pn_item_allocation_number
300 );
301 FND_LOG.STRING ( ln_proc_level
302 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
303 , 'pn_tax_allocation_number ' || pn_tax_allocation_number
304 );
305 END IF;
306
307 FOR i IN (pn_tax_allocation_number+1) .. pn_item_allocation_number
308 LOOP
309 INSERT INTO AP_INVOICE_DISTRIBUTIONS_ALL
310 ( accounting_date
311 , accrual_posted_flag
312 , assets_addition_flag
313 , assets_tracking_flag
314 , cash_posted_flag
315 , distribution_line_number
316 , dist_code_combination_id
317 , invoice_id
318 , last_updated_by
319 , last_update_date
320 , line_type_lookup_code
321 , period_name
322 , set_of_books_id
323 , accts_pay_code_combination_id
324 , amount
325 , base_amount
326 , base_invoice_price_variance
327 , batch_id
328 , created_by
329 , creation_date
330 , description
331 , exchange_rate_variance
332 , final_match_flag
333 , income_tax_region
334 , invoice_price_variance
335 , last_update_login
336 , match_status_flag
337 , posted_flag
338 , po_distribution_id
339 , program_application_id
340 , program_id
341 , program_update_date
342 , quantity_invoiced
343 , rate_var_code_combination_id
344 , request_id
345 , reversal_flag
346 , type_1099
347 , unit_price
348 , amount_encumbered
349 , base_amount_encumbered
350 , encumbered_flag
351 , exchange_date
352 , exchange_rate
353 , exchange_rate_type
354 , price_adjustment_flag
355 , price_var_code_combination_id
356 , quantity_unencumbered
357 , stat_amount
358 , amount_to_post
359 , attribute1
360 , attribute10
361 , attribute11
362 , attribute12
363 , attribute13
364 , attribute14
365 , attribute15
366 , attribute2
367 , attribute3
368 , attribute4
369 , attribute5
370 , attribute6
371 , attribute7
372 , attribute8
373 , attribute9
374 , attribute_category
375 , base_amount_to_post
376 , cash_je_batch_id
377 , expenditure_item_date
378 , expenditure_organization_id
379 , expenditure_type
380 , je_batch_id
381 , parent_invoice_id
382 , pa_addition_flag
383 , pa_quantity
384 , posted_amount
385 , posted_base_amount
386 , prepay_amount_remaining
387 , project_accounting_context
388 , project_id
389 , task_id
390 , ussgl_transaction_code
391 , ussgl_trx_code_context
392 , earliest_settlement_date
393 , req_distribution_id
394 , quantity_variance
395 , base_quantity_variance
396 , packet_id
397 , awt_flag
398 , awt_group_id
399 , awt_tax_rate_id
400 , awt_gross_amount
401 , awt_invoice_id
402 , awt_origin_group_id
403 , reference_1
404 , reference_2
405 , org_id
406 , other_invoice_id
407 , awt_invoice_payment_id
408 , global_attribute_category
409 , global_attribute1
410 , global_attribute2
411 , global_attribute3
412 , global_attribute4
413 , global_attribute5
414 , global_attribute6
415 , global_attribute7
416 , global_attribute8
417 , global_attribute9
418 , global_attribute10
419 , global_attribute11
420 , global_attribute12
421 , global_attribute13
422 , global_attribute14
423 , global_attribute15
424 , global_attribute16
425 , global_attribute17
426 , global_attribute18
427 , global_attribute19
428 , global_attribute20
429 , line_group_number
430 , receipt_verified_flag
431 , receipt_required_flag
432 , receipt_missing_flag
433 , justification
434 , expense_group
435 , start_expense_date
436 , end_expense_date
437 , receipt_currency_code
438 , receipt_conversion_rate
439 , receipt_currency_amount
440 , daily_amount
441 , web_parameter_id
442 , adjustment_reason
443 , award_id
444 , mrc_accrual_posted_flag
445 , mrc_cash_posted_flag
446 , mrc_dist_code_combination_id
447 , mrc_amount
448 , mrc_base_amount
449 , mrc_base_inv_price_variance
450 , mrc_exchange_rate_variance
451 , mrc_posted_flag
452 , mrc_program_application_id
453 , mrc_program_id
454 , mrc_program_update_date
455 , mrc_rate_var_ccid
456 , mrc_request_id
457 , mrc_exchange_date
458 , mrc_exchange_rate
459 , mrc_exchange_rate_type
460 , mrc_amount_to_post
461 , mrc_base_amount_to_post
462 , mrc_cash_je_batch_id
463 , mrc_je_batch_id
464 , mrc_posted_amount
465 , mrc_posted_base_amount
466 , mrc_receipt_conversion_rate
467 , credit_card_trx_id
468 , dist_match_type
469 , rcv_transaction_id
470 , invoice_distribution_id
471 , parent_reversal_id
472 , tax_recoverable_flag
473 , pa_cc_ar_invoice_id
474 , pa_cc_ar_invoice_line_num
475 , pa_cc_processed_code
476 , merchant_document_number
477 , merchant_name
478 , merchant_reference
479 , merchant_tax_reg_number
480 , merchant_taxpayer_id
481 , country_of_supply
482 , matched_uom_lookup_code
483 , gms_burdenable_raw_cost
484 , accounting_event_id
485 , prepay_distribution_id
486 , upgrade_posted_amt
487 , upgrade_base_posted_amt
488 , inventory_transfer_status
489 , company_prepaid_invoice_id
490 , cc_reversal_flag
491 , awt_withheld_amt
492 , invoice_includes_prepay_flag
493 , price_correct_inv_id
494 , price_correct_qty
495 , pa_cmt_xface_flag
496 , cancellation_flag
497 , invoice_line_number
498 , corrected_invoice_dist_id
499 , rounding_amt
500 , charge_applicable_to_dist_id
501 , corrected_quantity
502 , related_id
503 , asset_book_type_code
504 , asset_category_id
505 , distribution_class
506 , final_payment_rounding
507 , final_application_rounding
508 , amount_at_prepay_xrate
509 , cash_basis_final_app_rounding
510 , amount_at_prepay_pay_xrate
511 , intended_use
512 , detail_tax_dist_id
513 , rec_nrec_rate
514 , recovery_rate_id
515 , recovery_rate_name
516 , recovery_type_code
517 , recovery_rate_code
518 , withholding_tax_code_id
519 , tax_already_distributed_flag
520 , summary_tax_line_id
521 , taxable_amount
522 , taxable_base_amount
523 , extra_po_erv
524 , prepay_tax_diff_amount
525 , tax_code_id
526 , vat_code
527 , amount_includes_tax_flag
528 , tax_calculated_flag
529 , tax_recovery_rate
530 , tax_recovery_override_flag
531 , tax_code_override_flag
532 , total_dist_amount
533 , total_dist_base_amount
534 , prepay_tax_parent_id
535 , cancelled_flag
536 , old_distribution_id
537 , old_dist_line_number
538 , amount_variance
539 , base_amount_variance
540 , historical_flag
541 , rcv_charge_addition_flag
542 , awt_related_id
543 , related_retainage_dist_id
544 , retained_amount_remaining
545 , bc_event_id
546 , retained_invoice_dist_id
547 , final_release_rounding
548 , fully_paid_acctd_flag
549 , root_distribution_id
550 , xinv_parent_reversal_id
551 , recurring_payment_id
552 , release_inv_dist_derived_from
553 )
554 SELECT
555 accounting_date
556 , accrual_posted_flag
557 , assets_addition_flag
558 , assets_tracking_flag
559 , cash_posted_flag
560 , i --distribution_line_number
561 , dist_code_combination_id
562 , invoice_id
563 , last_updated_by
564 , last_update_date
565 , line_type_lookup_code
566 , period_name
567 , set_of_books_id
568 , accts_pay_code_combination_id
569 , amount
570 , base_amount
571 , base_invoice_price_variance
572 , batch_id
573 , created_by
574 , creation_date
575 , description
576 , exchange_rate_variance
577 , final_match_flag
578 , income_tax_region
579 , invoice_price_variance
580 , last_update_login
581 , match_status_flag
582 , posted_flag
583 , po_distribution_id
584 , program_application_id
585 , program_id
586 , program_update_date
587 , quantity_invoiced
588 , rate_var_code_combination_id
589 , request_id
590 , reversal_flag
591 , type_1099
592 , unit_price
593 , amount_encumbered
594 , base_amount_encumbered
595 , encumbered_flag
596 , exchange_date
597 , exchange_rate
598 , exchange_rate_type
599 , price_adjustment_flag
600 , price_var_code_combination_id
601 , quantity_unencumbered
602 , stat_amount
603 , amount_to_post
604 , attribute1
605 , attribute10
606 , attribute11
607 , attribute12
608 , attribute13
609 , attribute14
610 , attribute15
611 , attribute2
612 , attribute3
613 , attribute4
614 , attribute5
615 , attribute6
616 , attribute7
617 , attribute8
618 , attribute9
619 , attribute_category
620 , base_amount_to_post
621 , cash_je_batch_id
622 , expenditure_item_date
623 , expenditure_organization_id
624 , expenditure_type
625 , je_batch_id
626 , parent_invoice_id
627 , pa_addition_flag
628 , pa_quantity
629 , posted_amount
630 , posted_base_amount
631 , prepay_amount_remaining
632 , project_accounting_context
633 , project_id
634 , task_id
635 , ussgl_transaction_code
636 , ussgl_trx_code_context
637 , earliest_settlement_date
638 , req_distribution_id
639 , quantity_variance
640 , base_quantity_variance
641 , packet_id
642 , awt_flag
643 , awt_group_id
644 , awt_tax_rate_id
645 , awt_gross_amount
646 , awt_invoice_id
647 , awt_origin_group_id
648 , reference_1
649 , reference_2
650 , org_id
651 , other_invoice_id
652 , awt_invoice_payment_id
653 , global_attribute_category
654 , global_attribute1
655 , global_attribute2
656 , global_attribute3
657 , global_attribute4
658 , global_attribute5
659 , global_attribute6
660 , global_attribute7
661 , global_attribute8
662 , global_attribute9
663 , global_attribute10
664 , global_attribute11
665 , global_attribute12
666 , global_attribute13
667 , global_attribute14
668 , global_attribute15
669 , global_attribute16
670 , global_attribute17
671 , global_attribute18
672 , global_attribute19
673 , global_attribute20
674 , line_group_number
675 , receipt_verified_flag
676 , receipt_required_flag
677 , receipt_missing_flag
678 , justification
679 , expense_group
680 , start_expense_date
681 , end_expense_date
682 , receipt_currency_code
683 , receipt_conversion_rate
684 , receipt_currency_amount
685 , daily_amount
686 , web_parameter_id
687 , adjustment_reason
688 , award_id
689 , mrc_accrual_posted_flag
690 , mrc_cash_posted_flag
691 , mrc_dist_code_combination_id
692 , mrc_amount
693 , mrc_base_amount
694 , mrc_base_inv_price_variance
695 , mrc_exchange_rate_variance
696 , mrc_posted_flag
697 , mrc_program_application_id
698 , mrc_program_id
699 , mrc_program_update_date
700 , mrc_rate_var_ccid
701 , mrc_request_id
702 , mrc_exchange_date
703 , mrc_exchange_rate
704 , mrc_exchange_rate_type
705 , mrc_amount_to_post
706 , mrc_base_amount_to_post
707 , mrc_cash_je_batch_id
708 , mrc_je_batch_id
709 , mrc_posted_amount
710 , mrc_posted_base_amount
711 , mrc_receipt_conversion_rate
712 , credit_card_trx_id
713 , dist_match_type
714 , rcv_transaction_id
715 , ap_invoice_distributions_s.NEXTVAL --invoice_distribution_id
716 , parent_reversal_id
717 , tax_recoverable_flag
718 , pa_cc_ar_invoice_id
719 , pa_cc_ar_invoice_line_num
720 , pa_cc_processed_code
721 , merchant_document_number
722 , merchant_name
723 , merchant_reference
724 , merchant_tax_reg_number
725 , merchant_taxpayer_id
726 , country_of_supply
727 , matched_uom_lookup_code
728 , gms_burdenable_raw_cost
729 , accounting_event_id
730 , prepay_distribution_id
731 , upgrade_posted_amt
732 , upgrade_base_posted_amt
733 , inventory_transfer_status
734 , company_prepaid_invoice_id
735 , cc_reversal_flag
736 , awt_withheld_amt
737 , invoice_includes_prepay_flag
738 , price_correct_inv_id
739 , price_correct_qty
740 , pa_cmt_xface_flag
741 , cancellation_flag
742 , invoice_line_number
743 , corrected_invoice_dist_id
744 , rounding_amt
745 , charge_applicable_to_dist_id
746 , corrected_quantity
747 , related_id
748 , asset_book_type_code
749 , asset_category_id
750 , distribution_class
751 , final_payment_rounding
752 , final_application_rounding
753 , amount_at_prepay_xrate
754 , cash_basis_final_app_rounding
755 , amount_at_prepay_pay_xrate
756 , intended_use
757 , detail_tax_dist_id
758 , rec_nrec_rate
759 , recovery_rate_id
760 , recovery_rate_name
761 , recovery_type_code
762 , recovery_rate_code
763 , withholding_tax_code_id
764 , tax_already_distributed_flag
765 , summary_tax_line_id
766 , taxable_amount
767 , taxable_base_amount
768 , extra_po_erv
769 , prepay_tax_diff_amount
770 , tax_code_id
771 , vat_code
772 , amount_includes_tax_flag
773 , tax_calculated_flag
774 , tax_recovery_rate
775 , tax_recovery_override_flag
776 , tax_code_override_flag
777 , total_dist_amount
778 , total_dist_base_amount
779 , prepay_tax_parent_id
780 , cancelled_flag
781 , old_distribution_id
782 , old_dist_line_number
783 , amount_variance
784 , base_amount_variance
785 , historical_flag
786 , rcv_charge_addition_flag
787 , awt_related_id
788 , related_retainage_dist_id
789 , retained_amount_remaining
790 , bc_event_id
791 , retained_invoice_dist_id
792 , final_release_rounding
793 , fully_paid_acctd_flag
794 , root_distribution_id
795 , xinv_parent_reversal_id
796 , recurring_payment_id
797 , release_inv_dist_derived_from
798 FROM
799 ap_invoice_distributions_all
800 WHERE invoice_id = pn_invoice_id
801 AND invoice_line_number = pn_invoice_line_number
802 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
803 AND distribution_line_number = 1;
804 END LOOP; --(i IN (pn_tax_allocation_number+1) .. pn_item_allocation_number)
805
806 IF ( ln_proc_level >= ln_dbg_level)
807 THEN
808 FND_LOG.STRING ( ln_proc_level
809 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
810 , 'Exit procedure'
811 );
812 END IF; --( ln_proc_level >= ln_dbg_level )
813 EXCEPTION
814 WHEN OTHERS THEN
815 IF ( ln_proc_level >= ln_dbg_level)
816 THEN
817 FND_LOG.STRING ( ln_proc_level
818 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
819 || '. Other_Exception '
820 , SQLCODE || ':' || SQLERRM
821 );
822 END IF; --( ln_proc_level >= ln_dbg_level) ;
823
824 END Insert_Tax_Distribution_Lines;
825
826 --==========================================================================
827 -- PROCEDURE NAME:
828 --
829 -- Delete_Tax_Distribution_Lines Private
830 --
831 -- DESCRIPTION:
832 -- Insert tax distribution lines. The allocation numbers of tax line should
833 -- be same as the numbers of coressponding item lines
834 --
835 -- PARAMETERS:
836 -- In: pn_invoice_id IN NUMBER invoice id
837 -- pn_invoice_line_number IN NUMBER line number
838 -- pn_item_allocation_number IN NUMBER item line allocation numbers
839 -- pn_tax_allocation_number IN NUMBER tax line allocation numbers
840 -- Out:
841 --
842 -- PRE-COND : invoice item line and tax lines exist
843 -- EXCEPTIONS:
844 --
845 --===========================================================================
846 PROCEDURE Delete_Tax_Distribution_Lines
847 ( pn_invoice_id IN NUMBER
848 , pn_invoice_line_number IN NUMBER
849 , pn_item_allocation_number IN NUMBER
850 )
851 IS
852
853 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
854 ln_proc_level NUMBER := FND_LOG.level_procedure;
855 lv_proc_name VARCHAR2 (100) := 'Delete_Tax_Distribution_Lines';
856 BEGIN
857 --log for debug
858 IF ( ln_proc_level >= ln_dbg_level)
859 THEN
860 FND_LOG.STRING ( ln_proc_level
861 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
862 , 'Enter procedure'
863 );
864 FND_LOG.STRING ( ln_proc_level
865 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
866 , 'pn_invoice_id ' || pn_invoice_id
867 );
868 FND_LOG.STRING ( ln_proc_level
869 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
870 , 'pn_invoice_line_number ' || pn_invoice_line_number
871 );
872 FND_LOG.STRING ( ln_proc_level
873 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
874 , 'pn_item_allocation_number ' || pn_item_allocation_number
875 );
876 END IF;
877
878 DELETE
879 FROM
880 ap_invoice_distributions_all
881 WHERE invoice_id = pn_invoice_id
882 AND invoice_line_number = pn_invoice_line_number
883 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
884 AND distribution_line_number > pn_item_allocation_number;
885
886 IF ( ln_proc_level >= ln_dbg_level)
887 THEN
888 FND_LOG.STRING ( ln_proc_level
889 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
890 , 'Exit procedure'
891 );
892
893 FND_LOG.STRING ( ln_proc_level
894 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
895 , 'Exit procedure'
896 );
897 END IF; --( ln_proc_level >= ln_dbg_level )
898
899 EXCEPTION
900 WHEN OTHERS THEN
901 IF ( ln_proc_level >= ln_dbg_level)
902 THEN
903 FND_LOG.STRING ( ln_proc_level
904 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
905 || '. Other_Exception '
906 , SQLCODE || ':' || SQLERRM
907 );
908 END IF; --( ln_proc_level >= ln_dbg_level) ;
909 END Delete_Tax_Distribution_Lines;
910
911
912 --==========================================================================
913 -- PROCEDURE NAME:
914 --
915 -- Allocate_Tax_Dist_Lines Private
916 --
917 -- DESCRIPTION:
918 -- Insert tax distribution lines. The allocation numbers of tax line should
919 -- be same as the numbers of coressponding item lines
920 --
921 -- PARAMETERS:
922 -- In: pn_invoice_id IN NUMBER invoice id
923 -- pn_invoice_item_line_number IN NUMBER line number
924 -- Out:
925 --
926 -- PRE-COND : invoice item line and tax lines exist
927 -- EXCEPTIONS:
928 --
929 --===========================================================================
930
931 PROCEDURE Allocate_Tax_Dist_Lines
932 ( pn_invoice_id IN NUMBER
933 , pn_invoice_item_line_number IN NUMBER
934 )
935 IS
936 ln_invoice_id NUMBER := pn_invoice_id;
937 ln_invoice_item_line_number NUMBER := pn_invoice_item_line_number;
938
939
940 CURSOR get_tax_cur (pn_tax_id NUMBER)
941 IS
942 SELECT
943 tax_name
944 , tax_account_id
945 , mod_cr_percentage
946 , adhoc_flag
947 , NVL (tax_rate, -1) tax_rate
948 , tax_type
949 , NVL(rounding_factor,0) rounding_factor
950 FROM
951 jai_cmn_taxes_all
952 WHERE tax_id = pn_tax_id;
953
954 CURSOR item_line_cur IS
955 SELECT
956 line_number
957 , amount
958 FROM
959 ap_invoice_lines_all
960 WHERE invoice_id = ln_invoice_id
961 AND line_number = NVL(ln_invoice_item_line_number,line_number)
962 AND line_type_lookup_code = GV_CONSTANT_ITEM;
963
964 CURSOR invoice_dist_line_cur (pn_invoice_item_ln_number NUMBER)
965 IS
966 SELECT
967 amount
968 , dist_code_combination_id
969 , assets_tracking_flag
970 , assets_addition_flag
971 , project_id
972 , task_id
973 , expenditure_type
974 , pa_addition_flag
975 , ASSET_BOOK_TYPE_CODE
976 , ASSET_CATEGORY_ID
977 FROM
978 ap_invoice_distributions_all
979 WHERE invoice_id = ln_invoice_id
980 AND invoice_line_number = pn_invoice_item_ln_number;
981
982 CURSOR tax_line_cur (pn_invoice_item_ln_number NUMBER)
983 IS
984 SELECT
985 jail.invoice_line_number invoice_line_number
986 , jail.line_amount line_amount
987 , jcdt.tax_id tax_id
988 , NVL(jcdt.modvat_flag,'N') modvat_flag
989 , aila.base_amount base_amount
990 FROM
991 jai_ap_invoice_lines jail
992 , jai_cmn_document_taxes jcdt
993 , ap_invoice_lines_all aila
994 WHERE jcdt.source_doc_id = jail.invoice_id
995 AND jcdt.source_doc_line_id = jail.invoice_line_number
996 AND aila.invoice_id = jail.invoice_id
997 AND aila.line_number = jail.invoice_line_number
998 AND jail.parent_invoice_line_number = pn_invoice_item_ln_number
999 AND jail.line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
1000 AND jail.invoice_id = ln_invoice_id
1001 AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice --Added by eric on Jan 29,2008
1002 ORDER BY jail.invoice_line_number;
1003
1004 CURSOR get_allocation_numbers_cur (pn_invoice_line_number NUMBER)
1005 IS
1006 SELECT
1007 COUNT(1)
1008 FROM
1009 ap_invoice_distributions_all
1010 WHERE invoice_id = ln_invoice_id
1011 AND invoice_line_number = pn_invoice_line_number;
1012
1013 CURSOR get_dist_total_amount_cur
1014 ( pn_invoice_line_number NUMBER
1015 , pn_dist_line_number NUMBER
1016 )
1017 IS
1018 SELECT
1019 SUM( amount )
1020 , SUM( base_amount )
1021 FROM
1022 ap_invoice_distributions_all
1023 WHERE invoice_id = ln_invoice_id
1024 AND invoice_line_number = pn_invoice_line_number
1025 AND distribution_line_number < pn_dist_line_number;
1026
1027 ln_item_allocation_number NUMBER ;
1028 ln_tax_allocation_number NUMBER ;
1029 ln_allocation_factor NUMBER ;
1030 ln_loop_counter NUMBER ;
1031 tax_rec get_tax_cur%ROWTYPE;
1032 ln_dist_total_amount NUMBER;
1033 ln_dist_total_base_amount NUMBER;
1034
1035 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1036 ln_proc_level NUMBER := FND_LOG.level_procedure;
1037 lv_proc_name VARCHAR2 (100) := 'Allocate_Tax_Dist_Lines';
1038 BEGIN
1039 --log for debug
1040 IF ( ln_proc_level >= ln_dbg_level)
1041 THEN
1042 FND_LOG.STRING ( ln_proc_level
1043 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
1044 , 'Enter procedure'
1045 );
1046 FND_LOG.STRING ( ln_proc_level
1047 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1048 , 'pn_invoice_id ' || pn_invoice_id
1049 );
1050 FND_LOG.STRING ( ln_proc_level
1051 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1052 , 'pn_invoice_item_line_number '
1053 || pn_invoice_item_line_number
1054 );
1055 END IF;
1056
1057 FOR item_line_rec IN item_line_cur
1058 LOOP
1059 --initialize the allocation variables
1060 ln_loop_counter :=0;
1061
1062 --Get item allocation total numbers
1063 OPEN get_allocation_numbers_cur (item_line_rec.line_number);
1064 FETCH get_allocation_numbers_cur
1065 INTO
1066 ln_item_allocation_number;
1067 CLOSE get_allocation_numbers_cur;
1068 --Get item allocation total numbers end
1069
1070 -- make dist line numbers of each nonrec tax line same as the dist
1071 -- line number of its corresponding item line,sync dist line numbers
1072 FOR tax_line_rec IN tax_line_cur (item_line_rec.line_number)
1073 LOOP
1074
1075 --Get item allocation total numbers
1076 OPEN get_allocation_numbers_cur (tax_line_rec.invoice_line_number);
1077 FETCH get_allocation_numbers_cur
1078 INTO
1079 ln_tax_allocation_number;
1080 CLOSE get_allocation_numbers_cur;
1081 --Get item allocation total numbers end
1082
1083 --get tax definition parameters
1084 OPEN get_tax_cur (tax_line_rec.tax_id);
1085 FETCH get_tax_cur
1086 INTO
1087 tax_rec;
1088 CLOSE get_tax_cur;
1089 --get tax definition parameters end
1090
1091 --for non recoverable tax lines distribution numbers should
1092 --be same as the dist line numbers of its item line
1093 IF (NVL (tax_line_rec.modvat_flag, 'N') = jai_constants.no
1094 OR NVL (tax_rec.mod_cr_percentage, -1) <= 0
1095 )
1096 THEN
1097 -- if item dist line number > dist line numbers of current tax line
1098 -- ,insert tax lines.
1099 IF (ln_item_allocation_number >ln_tax_allocation_number)
1100 THEN
1101 insert_tax_distribution_lines
1102 ( pn_invoice_id => ln_invoice_id
1103 , pn_invoice_line_number => tax_line_rec.invoice_line_number
1104 , pn_item_allocation_number => ln_item_allocation_number
1105 , pn_tax_allocation_number => ln_tax_allocation_number
1106 );
1107 ELSIF(ln_item_allocation_number <ln_tax_allocation_number)
1108 THEN
1109 -- if item dist line number < dist line numbers of current tax line
1110 -- ,delete tax lines.
1111 delete_tax_distribution_lines
1112 ( pn_invoice_id => ln_invoice_id
1113 , pn_invoice_line_number => tax_line_rec.invoice_line_number
1114 , pn_item_allocation_number => ln_item_allocation_number
1115 );
1116 END IF;
1117 ELSE --(recoverable tax)
1118 --no requirement of taking any action for the recoverable tax
1119 NULL;
1120 END IF;--(non recoverable)
1121 END LOOP; --(tax_line_rec IN tax_line_cur,sync dist line numbers end)
1122
1123 --item distribution lines loop
1124 FOR item_dist_line_rec IN invoice_dist_line_cur(item_line_rec.line_number)
1125 LOOP
1126 ln_loop_counter :=ln_loop_counter + 1;
1127
1128 ln_allocation_factor :=
1129 item_dist_line_rec.amount/item_line_rec.amount;
1130
1131 FOR tax_line_rec IN tax_line_cur (item_line_rec.line_number)
1132 LOOP
1133 IF (NVL (tax_line_rec.modvat_flag, 'N') = jai_constants.no
1134 OR NVL (tax_rec.mod_cr_percentage, -1) <= 0
1135 )
1136 THEN
1137 IF (ln_item_allocation_number >1)
1138 THEN
1139 IF (ln_loop_counter < ln_item_allocation_number )
1140 THEN
1141 --get tax definition parameters
1142 OPEN get_tax_cur (tax_line_rec.tax_id);
1143 FETCH get_tax_cur
1144 INTO
1145 tax_rec;
1146 CLOSE get_tax_cur;
1147
1148 --allocation tax amount according to the proportion
1149 --of item dist lines
1150 UPDATE
1151 ap_invoice_distributions_all
1152 SET
1153 amount =tax_line_rec.line_amount * ln_allocation_factor
1154 /*
1155 ROUND( tax_line_rec.line_amount *
1156 ln_allocation_factor,tax_rec.rounding_factor
1157 )
1158 */
1159
1160 , base_amount =tax_line_rec.base_amount *ln_allocation_factor
1161 /*
1162 ROUND( tax_line_rec.base_amount *
1163 ln_allocation_factor,tax_rec.rounding_factor
1164 )
1165 */
1166
1167 , assets_tracking_flag = item_dist_line_rec.assets_tracking_flag
1168 , assets_addition_flag = item_dist_line_rec.assets_addition_flag
1169 , project_id = item_dist_line_rec.project_id
1170 , task_id = item_dist_line_rec.task_id
1171 , expenditure_type = item_dist_line_rec.expenditure_type
1172 , pa_addition_flag = item_dist_line_rec.PA_ADDITION_FLAG
1173 , dist_code_combination_id =
1174 item_dist_line_rec.dist_code_combination_id
1175 , ASSET_BOOK_TYPE_CODE = item_dist_line_rec.ASSET_BOOK_TYPE_CODE
1176 , ASSET_CATEGORY_ID = item_dist_line_rec.ASSET_CATEGORY_ID
1177 WHERE invoice_id = pn_invoice_id
1178 AND invoice_line_number = tax_line_rec.invoice_line_number
1179 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
1180 AND distribution_line_number = ln_loop_counter;
1181 ELSE --(ln_loop_counter = ln_item_allocation_number,last loop)
1182 OPEN get_dist_total_amount_cur
1183 ( tax_line_rec.invoice_line_number
1184 , ln_loop_counter
1185 );
1186 FETCH get_dist_total_amount_cur
1187 INTO
1188 ln_dist_total_amount
1189 , ln_dist_total_base_amount;
1190 CLOSE get_dist_total_amount_cur;
1191
1192 UPDATE
1193 ap_invoice_distributions_all
1194 SET
1195 amount =
1196 tax_line_rec.line_amount - ln_dist_total_amount
1197 , base_amount =
1198 tax_line_rec.base_amount - ln_dist_total_base_amount
1199 , assets_tracking_flag = item_dist_line_rec.assets_tracking_flag
1200 , assets_addition_flag = item_dist_line_rec.assets_addition_flag
1201 , project_id = item_dist_line_rec.project_id
1202 , task_id = item_dist_line_rec.task_id
1203 , expenditure_type = item_dist_line_rec.expenditure_type
1204 , pa_addition_flag = item_dist_line_rec.pa_addition_flag
1205 , dist_code_combination_id =
1206 item_dist_line_rec.dist_code_combination_id
1207 , ASSET_BOOK_TYPE_CODE = item_dist_line_rec.ASSET_BOOK_TYPE_CODE
1208 , ASSET_CATEGORY_ID = item_dist_line_rec.ASSET_CATEGORY_ID
1209 WHERE invoice_id = pn_invoice_id
1210 AND invoice_line_number = tax_line_rec.invoice_line_number
1211 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
1212 AND distribution_line_number = ln_loop_counter;
1213 END IF;-- (ln_loop_counter < ln_item_allocation_number )
1214
1215 ELSE --(ln_item_allocation_number =1)
1216 --As only one item distribution line,tax lines are not
1217 --required to be allocated.
1218
1219 UPDATE
1220 ap_invoice_distributions_all
1221 SET
1222 amount = tax_line_rec.line_amount
1223 , base_amount = tax_line_rec.base_amount
1224 , assets_tracking_flag = item_dist_line_rec.assets_tracking_flag
1225 , assets_addition_flag = item_dist_line_rec.assets_addition_flag
1226 , project_id = item_dist_line_rec.project_id
1227 , task_id = item_dist_line_rec.task_id
1228 , expenditure_type = item_dist_line_rec.expenditure_type
1229 , pa_addition_flag = item_dist_line_rec.pa_addition_flag
1230 , dist_code_combination_id =
1231 item_dist_line_rec.dist_code_combination_id
1232 , ASSET_BOOK_TYPE_CODE = item_dist_line_rec.ASSET_BOOK_TYPE_CODE
1233 , ASSET_CATEGORY_ID = item_dist_line_rec.ASSET_CATEGORY_ID
1234 WHERE invoice_id = pn_invoice_id
1235 AND invoice_line_number = tax_line_rec.invoice_line_number
1236 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
1237 AND distribution_line_number = ln_loop_counter;
1238 END IF;-- (ln_item_allocation_number >1)
1239 ELSE --(recoverable tax)
1240 --no requirement of taking any action for the recoverable tax
1241 NULL;
1242 END IF;--(non recoverable)
1243 END LOOP;--(item tax lines loop)
1244 END LOOP;--(item distribution lines loop)
1245 END LOOP ; -- (item_line_rec IN item_line_cur,item lines loop)
1246 EXCEPTION
1247 WHEN OTHERS THEN
1248 IF ( ln_proc_level >= ln_dbg_level)
1249 THEN
1250 FND_LOG.STRING ( ln_proc_level
1251 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
1252 || '. Other_Exception '
1253 , SQLCODE || ':' || SQLERRM
1254 );
1255 END IF; --( ln_proc_level >= ln_dbg_level) ;
1256 END Allocate_Tax_Dist_Lines;
1257
1258 --==========================================================================
1259 -- FUNCTION NAME:
1260 --
1261 -- Get_Tax_Type Private
1262 --
1263 -- DESCRIPTION:
1264 -- With given modvat_flag and credit percentage, return the tax type.
1265 -- Tax type can be FR,fully recoverable,NR,not recoverable,or PR,partially
1266 -- recoverable
1267 --
1268 -- PARAMETERS:
1269 -- In: pv_modvat_flag IN VARCHAR Y or N
1270 -- pn_cr_percentage IN NUMBER Credit percentage
1271 --
1272 -- Out: RETURN VARCHAR2
1273 --
1274 --
1275 -- PRE-COND : invoice exists
1276 -- EXCEPTIONS:
1277 --
1278 --===========================================================================
1279 FUNCTION Get_Tax_Type
1280 ( pv_modvat_flag VARCHAR2
1281 , pn_cr_percentage NUMBER
1282 )
1283 RETURN VARCHAR2
1284 IS
1285 lv_tax_type VARCHAR2(10) ;
1286 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1287 ln_proc_level NUMBER := FND_LOG.level_procedure;
1288 lv_proc_name VARCHAR2 (100) := 'Get_Tax_Type';
1289 BEGIN
1290 IF ( ln_proc_level >= ln_dbg_level)
1291 THEN
1292 FND_LOG.STRING ( ln_proc_level
1293 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
1294 , 'Enter procedure'
1295 );
1296 FND_LOG.STRING ( ln_proc_level
1297 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1298 , 'pv_modvat_flag ' || pv_modvat_flag
1299 );
1300 FND_LOG.STRING ( ln_proc_level
1301 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1302 , 'pn_cr_percentage ' || pn_cr_percentage
1303 );
1304 END IF; --(ln_proc_level >= ln_dbg_level)
1305
1306 IF ( NVL (pv_modvat_flag, 'N') = jai_constants.no
1307 OR NVL (pn_cr_percentage, -1) <= 0
1308 )
1309 THEN
1310 lv_tax_type := 'NR' ; --NON RECOVERABLE
1311 ELSIF
1312 ( NVL (pv_modvat_flag, 'N') = jai_constants.yes
1313 AND NVL (pn_cr_percentage, -1) = 100
1314 )
1315 THEN
1316 lv_tax_type := 'FR' ; --FULLY RECOVERABLE
1317 ELSIF
1318 ( NVL (pv_modvat_flag, 'N') = jai_constants.yes
1319 AND NVL (pn_cr_percentage, -1) < 100
1320 )
1321 THEN
1322 lv_tax_type := 'PR' ; --PARTIALLY RECOVERABLE
1323 END IF;--( (pv_modvat_flag, 'N') = jai_constants.no)
1324
1325
1326 IF ( ln_proc_level >= ln_dbg_level)
1327 THEN
1328 FND_LOG.STRING ( ln_proc_level
1329 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1330 , 'lv_tax_type ' || lv_tax_type
1331 );
1332
1333 FND_LOG.STRING ( ln_proc_level
1334 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
1335 , 'Exit procedure'
1336 );
1337 END IF; --( ln_proc_level >= ln_dbg_level )
1338
1339 RETURN lv_tax_type;
1340 EXCEPTION
1341 WHEN OTHERS THEN
1342 IF ( ln_proc_level >= ln_dbg_level)
1343 THEN
1344 FND_LOG.STRING ( ln_proc_level
1345 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
1346 || '. Other_Exception '
1347 , SQLCODE || ':' || SQLERRM
1348 );
1349 END IF; --( ln_proc_level >= ln_dbg_level) ;
1350
1351 RETURN lv_tax_type;
1352 END Get_Tax_Type;
1353
1354 --==========================================================================
1355 -- FUNCTION NAME:
1356 --
1357 -- Get_Dist_Account_Ccid Private
1358 --
1359 -- DESCRIPTION:
1360 -- Get the distribution account ccid for a given tax type code
1361 -- 1.get account ccid from regim level
1362 -- 2.get account ccid from org-location combination level
1363 -- 3.get account ccid from tax definition level
1364 --
1365 -- PARAMETERS:
1366 -- In:
1367 -- pn_invoice_id IN NUMBER
1368 -- pn_item_line_number IN NUMBER
1369 -- pn_organization_id IN NUMBER
1370 -- pn_location_id IN NUMBER
1371 -- pn_tax_type_code IN VARCHAR2
1372 -- pn_tax_acct_ccid IN NUMBER
1373 -- pv_tax_type IN VARCHAR2
1374 --
1375 -- Out: RETURN number, account ccid
1376 --
1377 --
1378 -- PRE-COND :
1379 -- EXCEPTIONS:
1380 --
1381 --===========================================================================
1382 FUNCTION Get_Dist_Account_Ccid
1383 ( pn_invoice_id IN NUMBER
1384 , pn_item_line_number IN NUMBER
1385 , pn_organization_id IN NUMBER
1386 , pn_location_id IN NUMBER
1387 , pn_tax_type_code IN VARCHAR2
1388 , pn_tax_acct_ccid IN NUMBER
1389 , pv_tax_type IN VARCHAR2
1390 )
1391 RETURN NUMBER
1392 IS
1393 CURSOR item_dist_account_cur IS
1394 SELECT
1395 dist_code_combination_id
1396 FROM
1397 ap_invoice_distributions_all
1398 WHERE invoice_id = pn_invoice_id
1399 AND invoice_line_number = pn_item_line_number
1400 AND distribution_line_number =1;
1401
1402
1403 CURSOR jai_regimes_cur
1404 (
1405 pv_regime_code IN jai_rgm_definitions.regime_code%TYPE
1406 )
1407 IS
1408 SELECT
1409 regime_id
1410 FROM
1411 jai_rgm_definitions
1412 WHERE regime_code = pv_regime_code;
1413
1414 CURSOR regime_tax_type_cur
1415 ( pn_regime_id NUMBER
1416 , pv_tax_type_code VARCHAR2
1417 )
1418 IS
1419 SELECT
1420 attribute_code tax_type
1421 FROM
1422 jai_rgm_registrations
1423 WHERE regime_id = pn_regime_id
1424 AND registration_type =jai_constants.regn_type_tax_types --tax type
1425 AND attribute_code = pv_tax_type_code;
1426
1427 CURSOR regime_account_cur
1428 ( pn_regime_id NUMBER
1429 , pn_tax_type VARCHAR2
1430 )
1431 IS
1432 SELECT
1433 TO_NUMBER (accnts.attribute_value)
1434 FROM
1435 jai_rgm_registrations tax_types
1436 , jai_rgm_registrations accnts
1437 WHERE tax_types.regime_id = pn_regime_id
1438 AND tax_types.registration_type = jai_constants.regn_type_tax_types
1439 AND tax_types.attribute_code = pn_tax_type
1440 AND accnts.regime_id = tax_types.regime_id
1441 AND accnts.registration_type = jai_constants.regn_type_accounts
1442 AND accnts.parent_registration_id = tax_types.registration_id
1443 AND accnts.attribute_code = jai_constants.recovery_interim;
1444
1445 service_regimes_rec jai_regimes_cur%ROWTYPE;
1446 vat_regimes_rec jai_regimes_cur%ROWTYPE;
1447 ln_dist_acct_ccid NUMBER;
1448 ln_regime_id jai_rgm_definitions.regime_id%TYPE;
1449 lv_regime_code jai_rgm_definitions.regime_code%TYPE;
1450 lv_regim_tax_type jai_rgm_registrations.attribute_code%TYPE;
1451
1452 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1453 ln_proc_level NUMBER := FND_LOG.level_procedure;
1454 lv_proc_name VARCHAR2 (100) := 'Get_Dist_Account_Ccid';
1455
1456 BEGIN
1457 --log for debug
1458 IF ( ln_proc_level >= ln_dbg_level)
1459 THEN
1460 FND_LOG.STRING ( ln_proc_level
1461 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
1462 , 'Enter procedure'
1463 );
1464 FND_LOG.STRING ( ln_proc_level
1465 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1466 , 'pn_organization_id ' || pn_organization_id
1467 );
1468 FND_LOG.STRING ( ln_proc_level
1469 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1470 , 'pn_location_id ' || pn_location_id
1471 );
1472 FND_LOG.STRING ( ln_proc_level
1473 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1474 , 'pn_organization_id ' || pn_organization_id
1475 );
1476 FND_LOG.STRING ( ln_proc_level
1477 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1478 , 'pn_tax_acct_ccid ' || pn_tax_acct_ccid
1479 );
1480 FND_LOG.STRING ( ln_proc_level
1481 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1482 , 'pv_tax_type ' || pv_tax_type
1483 );
1484 END IF;
1485
1486 IF (pv_tax_type='NR') --NON recoverable tax
1487 THEN
1488 OPEN item_dist_account_cur;
1489 FETCH item_dist_account_cur
1490 INTO
1491 ln_dist_acct_ccid ;
1492 CLOSE item_dist_account_cur;
1493 ELSE -- recoverable tax
1494 OPEN jai_regimes_cur (jai_constants.service_regime);
1495 FETCH jai_regimes_cur
1496 INTO
1497 service_regimes_rec;
1498 CLOSE jai_regimes_cur;
1499
1500 OPEN jai_regimes_cur (jai_constants.vat_regime);
1501 FETCH jai_regimes_cur
1502 INTO
1503 vat_regimes_rec;
1504 CLOSE jai_regimes_cur;
1505
1506 --check the tax is service taxes or not
1507 OPEN regime_tax_type_cur ( service_regimes_rec.regime_id
1508 , pn_tax_type_code
1509 );
1510 FETCH regime_tax_type_cur
1511 INTO
1512 lv_regim_tax_type;
1513 CLOSE regime_tax_type_cur;
1514
1515 IF lv_regim_tax_type IS NOT NULL
1516 THEN
1517 lv_regime_code := jai_constants.service_regime;
1518 ELSE -- (r_service_regime_tax is null)
1519
1520 -- vat taxes
1521 OPEN regime_tax_type_cur ( vat_regimes_rec.regime_id
1522 , pn_tax_type_code
1523 );
1524 FETCH regime_tax_type_cur
1525 INTO
1526 lv_regim_tax_type;
1527 CLOSE regime_tax_type_cur;
1528
1529
1530
1531 IF lv_regim_tax_type IS NOT NULL
1532 THEN
1533 lv_regime_code := jai_constants.vat_regime;
1534 END IF; --(lv_regim_tax_type IS NOT NULL)
1535
1536 END IF; --( end of r_service_regime_tax_type level)
1537
1538 --try to get account from regim level
1539 IF lv_regime_code IS NULL --(tax is not difined in regim level)
1540 THEN
1541 ln_dist_acct_ccid := pn_tax_acct_ccid;
1542 ELSE --(lv_regime_code is NOT null,tax has beend difined in regim level)
1543 OPEN jai_regimes_cur (lv_regime_code);
1544
1545 FETCH jai_regimes_cur
1546 INTO
1547 ln_regime_id;
1548 CLOSE jai_regimes_cur;
1549
1550 IF ( pn_organization_id IS NULL
1551 AND pn_location_id IS NULL
1552 )
1553 THEN
1554 OPEN regime_account_cur
1555 ( ln_regime_id
1556 , pn_tax_type_code
1557 );
1558
1559 FETCH regime_account_cur
1560 INTO
1561 ln_dist_acct_ccid;
1562 CLOSE regime_account_cur;
1563 ELSIF( pn_organization_id IS NOT NULL
1564 AND pn_location_id IS NOT NULL
1565 )
1566 THEN
1567 ln_dist_acct_ccid :=
1568 jai_cmn_rgm_recording_pkg.get_account
1569 ( p_regime_id => ln_regime_id
1570 , p_organization_type => jai_constants.orgn_type_io
1571 , p_organization_id => pn_organization_id
1572 , p_location_id => pn_location_id
1573 , p_tax_type => pn_tax_type_code
1574 , p_account_name => jai_constants.recovery_interim
1575 );
1576
1577 END IF; --(pn_organization_id IS NULL AND pn_location_id IS NULL )
1578 END IF; --(lv_regime_code IS NULL)
1579 END IF; --(lv_recoverable_flag = 'N')
1580
1581 IF ( ln_proc_level >= ln_dbg_level)
1582 THEN
1583 FND_LOG.STRING ( ln_proc_level
1584 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1585 , 'ln_dist_acct_ccid ' || ln_dist_acct_ccid
1586 );
1587
1588 FND_LOG.STRING ( ln_proc_level
1589 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
1590 , 'Exit procedure'
1591 );
1592 END IF; --( ln_proc_level >= ln_dbg_level )
1593
1594 RETURN ln_dist_acct_ccid ;
1595 EXCEPTION
1596 WHEN OTHERS THEN
1597 IF ( ln_proc_level >= ln_dbg_level)
1598 THEN
1599 FND_LOG.STRING ( ln_proc_level
1600 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
1601 || '. Other_Exception '
1602 , SQLCODE || ':' || SQLERRM
1603 );
1604 END IF; --( ln_proc_level >= ln_dbg_level) ;
1605
1606 RETURN NULL;
1607 END Get_Dist_Account_Ccid;
1608
1609
1610 --==========================================================================
1611 -- FUNCTION NAME:
1612 --
1613 -- Get_Max_Invoice_Line_Number Private
1614 --
1615 -- DESCRIPTION:
1616 -- Get the max invoice line number for a given invoice id
1617 --
1618 --
1619 -- PARAMETERS:
1620 -- In: pn_invoice_id IN NUMBER invoice id
1621 --
1622 -- Out: RETURN number
1623 --
1624 --
1625 -- PRE-COND : invoice exists
1626 -- EXCEPTIONS:
1627 --
1628 --===========================================================================
1629 FUNCTION Get_Max_Invoice_Line_Number (pn_invoice_id NUMBER)
1630 RETURN NUMBER
1631 IS
1632 ln_max_line_number NUMBER;
1633 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1634 ln_proc_level NUMBER := FND_LOG.level_procedure;
1635 lv_proc_name VARCHAR2 (100) := 'Get_Max_Invoice_Line_Number';
1636 BEGIN
1637 --log for debug
1638 IF ( ln_proc_level >= ln_dbg_level)
1639 THEN
1640 FND_LOG.STRING ( ln_proc_level
1641 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
1642 , 'Enter procedure'
1643 );
1644 FND_LOG.STRING ( ln_proc_level
1645 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1646 , 'pn_invoice_id ' || pn_invoice_id
1647 );
1648 END IF;
1649
1650 -- add row level lock to the table ,to avoid duplicated lines created
1651 UPDATE ap_invoice_lines_all
1652 SET invoice_id = pn_invoice_id
1653 WHERE invoice_id = pn_invoice_id;
1654
1655 SELECT
1656 NVL(MAX (line_number), 0)
1657 INTO
1658 ln_max_line_number
1659 FROM
1660 ap_invoice_lines_all
1661 WHERE invoice_id = pn_invoice_id;
1662
1663 IF ( ln_proc_level >= ln_dbg_level)
1664 THEN
1665 FND_LOG.STRING ( ln_proc_level
1666 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1667 , 'ln_max_line_number ' || ln_max_line_number
1668 );
1669 FND_LOG.STRING ( ln_proc_level
1670 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
1671 , 'Exit procedure'
1672 );
1673 END IF; --( ln_proc_level >= ln_dbg_level )
1674
1675 RETURN ln_max_line_number;
1676 EXCEPTION
1677 WHEN OTHERS THEN
1678 IF ( ln_proc_level >= ln_dbg_level)
1679 THEN
1680 FND_LOG.STRING ( ln_proc_level
1681 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
1682 || '. Other_Exception '
1683 , SQLCODE || ':' || SQLERRM
1684 );
1685 END IF; --( ln_proc_level >= ln_dbg_level) ;
1686
1687 RETURN 0;
1688 END Get_Max_Invoice_Line_Number;
1689
1690 --==========================================================================
1691 -- FUNCTION NAME:
1692 --
1693 -- Get_Max_Doc_Source_Line_Id Private
1694 --
1695 -- DESCRIPTION:
1696 -- Get the max invoice line number( source doc line id )for a given
1697 -- invoice id (source id)
1698 --
1699 --
1700 -- PARAMETERS:
1701 -- In: pn_invoice_id IN NUMBER invoice id
1702 --
1703 -- Out: RETURN number
1704 --
1705 --
1706 -- PRE-COND : invoice exists
1707 -- EXCEPTIONS:
1708 --
1709 -- CHANGE HISTORY:
1710 -- 1 29-Jan-2008 Eric Ma Created for bug#6784111
1711 --
1712 --===========================================================================
1713 FUNCTION Get_Max_Doc_Source_Line_Id (pn_invoice_id NUMBER)
1714 RETURN NUMBER
1715 IS
1716 ln_max_line_number NUMBER;
1717 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1718 ln_proc_level NUMBER := FND_LOG.level_procedure;
1719 lv_proc_name VARCHAR2 (100) := 'Get_Max_Doc_Source_Line_Id';
1720 BEGIN
1721 --log for debug
1722 IF ( ln_proc_level >= ln_dbg_level)
1723 THEN
1724 FND_LOG.STRING ( ln_proc_level
1725 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
1726 , 'Enter procedure'
1727 );
1728 FND_LOG.STRING ( ln_proc_level
1729 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1730 , 'pn_invoice_id ' || pn_invoice_id
1731 );
1732 END IF;
1733
1734 -- add row level lock to the table
1735 UPDATE
1736 jai_cmn_document_taxes
1737 SET
1738 source_doc_id = pn_invoice_id
1739 WHERE source_doc_id = pn_invoice_id
1740 AND source_doc_type = jai_constants.g_ap_standalone_invoice;
1741
1742 SELECT
1743 NVL(MAX(source_doc_line_id), 0)
1744 INTO
1745 ln_max_line_number
1746 FROM
1747 jai_cmn_document_taxes
1748 WHERE source_doc_id = pn_invoice_id
1749 AND source_doc_type = jai_constants.g_ap_standalone_invoice;
1750
1751
1752 IF ( ln_proc_level >= ln_dbg_level)
1753 THEN
1754 FND_LOG.STRING ( ln_proc_level
1755 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1756 , 'ln_max_line_number ' || ln_max_line_number
1757 );
1758 FND_LOG.STRING ( ln_proc_level
1759 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
1760 , 'Exit procedure'
1761 );
1762 END IF; --( ln_proc_level >= ln_dbg_level )
1763
1764 RETURN ln_max_line_number;
1765 EXCEPTION
1766 WHEN OTHERS THEN
1767 IF ( ln_proc_level >= ln_dbg_level)
1768 THEN
1769 FND_LOG.STRING ( ln_proc_level
1770 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
1771 || '. Other_Exception '
1772 , SQLCODE || ':' || SQLERRM
1773 );
1774 END IF; --( ln_proc_level >= ln_dbg_level) ;
1775
1776 RETURN 0;
1777 END Get_Max_Doc_Source_Line_Id;
1778
1779 --==========================================================================
1780 -- FUNCTION NAME:
1781 --
1782 -- Get_Max_Tax_Line_Number Private
1783 --
1784 -- DESCRIPTION:
1785 -- Get the max tax line number for a given invoice id
1786 --
1787 --
1788 -- PARAMETERS:
1789 -- In: pn_invoice_id IN NUMBER invoice id
1790 -- pn_parent_invoice_line_number IN NUMBER item line number
1791 -- Out: RETURN number
1792 --
1793 --
1794 -- PRE-COND : invoice exists
1795 -- EXCEPTIONS:
1796 --
1797 --===========================================================================
1798 FUNCTION Get_Max_Tax_Line_Number
1799 ( pn_invoice_id NUMBER
1800 , pn_parent_invoice_line_number NUMBER
1801 )
1802 RETURN NUMBER
1803 IS
1804 ln_max_tax_line_num NUMBER;
1805 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1806 ln_proc_level NUMBER := FND_LOG.level_procedure;
1807 lv_proc_name VARCHAR2 (100) := 'Get_Max_Tax_Line_Number';
1808 BEGIN
1809 --log for debug
1810 IF ( ln_proc_level >= ln_dbg_level)
1811 THEN
1812 FND_LOG.STRING ( ln_proc_level
1813 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
1814 , 'Enter procedure'
1815 );
1816 FND_LOG.STRING ( ln_proc_level
1817 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1818 , 'pn_invoice_id ' || pn_invoice_id
1819 );
1820 FND_LOG.STRING ( ln_proc_level
1821 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1822 , 'pn_parent_invoice_line_number ' ||
1823 pn_parent_invoice_line_number
1824 );
1825 END IF;
1826
1827 --add row level lock on the table to avoid data conflication
1828 UPDATE
1829 jai_cmn_document_taxes
1830 SET
1831 source_doc_parent_line_no = pn_parent_invoice_line_number
1832 WHERE source_doc_id = pn_invoice_id
1833 AND source_doc_parent_line_no = pn_parent_invoice_line_number
1834 AND source_doc_type = jai_constants.g_ap_standalone_invoice;
1835
1836 SELECT
1837 NVL(MAX(tax_line_no),0)
1838 INTO
1839 ln_max_tax_line_num
1840 FROM
1841 jai_cmn_document_taxes
1842 WHERE source_doc_id = pn_invoice_id
1843 AND source_doc_parent_line_no = pn_parent_invoice_line_number
1844 AND source_doc_type = jai_constants.g_ap_standalone_invoice;
1845
1846 IF ( ln_proc_level >= ln_dbg_level)
1847 THEN
1848 FND_LOG.STRING ( ln_proc_level
1849 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1850 , 'ln_max_tax_line_num ' || ln_max_tax_line_num
1851 );
1852 FND_LOG.STRING ( ln_proc_level
1853 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
1854 , 'Exit procedure'
1855 );
1856 END IF; --( ln_proc_level >= ln_dbg_level )
1857
1858 RETURN ln_max_tax_line_num;
1859 EXCEPTION
1860 WHEN OTHERS THEN
1861 IF ( ln_proc_level >= ln_dbg_level)
1862 THEN
1863 FND_LOG.STRING ( ln_proc_level
1864 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
1865 || '. Other_Exception '
1866 , SQLCODE || ':' || SQLERRM
1867 );
1868 END IF; --( ln_proc_level >= ln_dbg_level) ;
1869
1870 RETURN 0;
1871 END Get_Max_Tax_Line_Number;
1872
1873
1874 --==========================================================================
1875 -- FUNCTION NAME:
1876 --
1877 -- Get_Gl_Account_Type Private
1878 --
1879 -- DESCRIPTION:
1880 -- Get the account type for a given ccid
1881 --
1882 --
1883 -- PARAMETERS:
1884 -- In: pn_code_combination_id NUMBER code combnation id
1885 --
1886 -- Out: RETURN account_type
1887 --
1888 -- PRE-COND : ccid exists
1889 -- EXCEPTIONS:
1890 --
1891 --===========================================================================
1892 FUNCTION Get_Gl_Account_Type (pn_code_combination_id IN NUMBER)
1893 RETURN VARCHAR2
1894 IS
1895 CURSOR get_account_cur IS
1896 SELECT
1897 account_type
1898 FROM
1899 gl_code_combinations
1900 WHERE code_combination_id = pn_code_combination_id;
1901
1902 lv_account_type gl_code_combinations.account_type%TYPE;
1903
1904 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1905 ln_proc_level NUMBER := FND_LOG.level_procedure;
1906 lv_proc_name VARCHAR2 (100) := 'Get_Gl_Account_Type';
1907 BEGIN
1908 --log for debug
1909 IF ( ln_proc_level >= ln_dbg_level)
1910 THEN
1911 FND_LOG.STRING ( ln_proc_level
1912 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
1913 , 'Enter procedure'
1914 );
1915 FND_LOG.STRING ( ln_proc_level
1916 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1917 , 'pn_code_combination_id ' || pn_code_combination_id
1918 );
1919 END IF;--( ln_proc_level >= ln_dbg_level )
1920
1921 OPEN get_account_cur;
1922 FETCH get_account_cur
1923 INTO
1924 lv_account_type;
1925 CLOSE get_account_cur;
1926
1927 IF ( ln_proc_level >= ln_dbg_level)
1928 THEN
1929 FND_LOG.STRING ( ln_proc_level
1930 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1931 , 'lv_account_type ' || lv_account_type
1932 );
1933 FND_LOG.STRING ( ln_proc_level
1934 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
1935 , 'Exit procedure'
1936 );
1937 END IF; --( ln_proc_level >= ln_dbg_level )
1938
1939
1940 RETURN lv_account_type;
1941 EXCEPTION
1942 WHEN OTHERS THEN
1943 IF ( ln_proc_level >= ln_dbg_level)
1944 THEN
1945 FND_LOG.STRING ( ln_proc_level
1946 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
1947 || '. Other_Exception '
1948 , SQLCODE || ':' || SQLERRM
1949 );
1950 END IF; --( ln_proc_level >= ln_dbg_level) ;
1951
1952 RETURN NULL; -- if required exception can be handled.
1953 END Get_Gl_Account_Type;
1954
1955 --==========================================================================
1956 -- PROCEDURE NAME:
1957 --
1958 -- Get_Tax_Cat_Serv_Type Private
1959 --
1960 -- DESCRIPTION:
1961 -- Get the tax category and service type code for a given vendor site
1962 -- and vendor id
1963 --
1964 --
1965 -- PARAMETERS:
1966 -- In: pn_invoice_id NUMBER invoice id
1967 -- pn_vendor_site_id NUMBER vendor site id
1968 --
1969 -- Out: x_tax_category_id NUMBER tax category id
1970 -- x_service_type_code NUMBER service type code
1971 --
1972 -- PRE-COND : vendor exists
1973 -- EXCEPTIONS:
1974 --
1975 --===========================================================================
1976 PROCEDURE Get_Tax_Cat_Serv_Type
1977 ( pn_vendor_id IN NUMBER
1978 , pn_vendor_site_id IN NUMBER
1979 , xn_tax_category_id OUT NOCOPY NUMBER
1980 , xv_service_type_code OUT NOCOPY VARCHAR2
1981 )
1982 IS
1983 CURSOR get_tax_service_cur IS
1984 SELECT
1985 tax_category_id, service_type_code
1986 FROM
1987 jai_cmn_vendor_sites
1988 WHERE NVL (vendor_site_id, 0) = pn_vendor_site_id
1989 AND vendor_id = pn_vendor_id;
1990
1991 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1992 ln_proc_level NUMBER := FND_LOG.level_procedure;
1993 lv_proc_name VARCHAR2 (100) := 'Get_Tax_Cat_Serv_Type';
1994 BEGIN
1995 --log for debug
1996 IF ( ln_proc_level >= ln_dbg_level)
1997 THEN
1998 FND_LOG.STRING ( ln_proc_level
1999 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2000 , 'Enter procedure'
2001 );
2002 FND_LOG.STRING ( ln_proc_level
2003 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2004 , 'pn_vendor_id ' || pn_vendor_id
2005 );
2006 FND_LOG.STRING ( ln_proc_level
2007 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2008 , 'pn_vendor_site_id ' || pn_vendor_site_id
2009 );
2010 END IF;
2011
2012 OPEN get_tax_service_cur;
2013
2014 FETCH get_tax_service_cur
2015 INTO
2016 xn_tax_category_id
2017 , xv_service_type_code;
2018 CLOSE get_tax_service_cur;
2019
2020 IF ( ln_proc_level >= ln_dbg_level)
2021 THEN
2022 FND_LOG.STRING ( ln_proc_level
2023 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2024 , 'xn_tax_category_id ' || xn_tax_category_id
2025 );
2026 FND_LOG.STRING ( ln_proc_level
2027 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2028 , 'xv_service_type_code ' || xv_service_type_code
2029 );
2030 FND_LOG.STRING ( ln_proc_level
2031 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2032 , 'Exit procedure'
2033 );
2034 END IF; --( ln_proc_level >= ln_dbg_level )
2035 EXCEPTION
2036 WHEN OTHERS THEN
2037 IF ( ln_proc_level >= ln_dbg_level)
2038 THEN
2039 FND_LOG.STRING ( ln_proc_level
2040 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2041 || '. Other_Exception '
2042 ,SQLCODE || ':' || SQLERRM
2043 );
2044 END IF; --( ln_proc_level >= ln_dbg_level) ;
2045 RAISE;
2046 END Get_Tax_Cat_Serv_Type;
2047
2048 --==========================================================================
2049 -- PROCEDURE NAME:
2050 --
2051 -- PROCEDURE Get_Invoice_Header_Infor Private
2052 --
2053 -- DESCRIPTION:
2054 --
2055 -- For a given invoice id RETURN vendor id,vendor site id, currency code
2056 -- and exchange rate
2057 --
2058 -- PARAMETERS:
2059 -- In: pn_invoice_id NUMBER invoice id
2060 --
2061 --
2062 -- Out: xn_vendor_id number vendor id
2063 -- xn_vendor_site_id number vendor site id
2064 -- xv_currency_code varchar2 currency code
2065 -- xn_exchange_rate number exchange rate
2066 -- xn_batch_id number xn_batch_id
2067 --
2068 -- PRE-COND : invoice exists
2069 -- EXCEPTIONS:
2070 --
2071 --
2072 --========================================================================
2073 PROCEDURE Get_Invoice_Header_Infor
2074 ( pn_invoice_id IN NUMBER
2075 , xn_vendor_id OUT NOCOPY NUMBER
2076 , xn_vendor_site_id OUT NOCOPY NUMBER
2077 , xv_currency_code OUT NOCOPY VARCHAR2
2078 , xn_exchange_rate OUT NOCOPY NUMBER
2079 , xn_batch_id OUT NOCOPY NUMBER
2080 )
2081 IS
2082 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2083 ln_proc_level NUMBER := FND_LOG.level_procedure;
2084 lv_proc_name VARCHAR2 (100) := 'Get_Invoice_Header_Infor';
2085
2086 CURSOR Get_Invoice_Header_Infor_cur IS
2087 SELECT
2088 vendor_id
2089 , vendor_site_id
2090 , invoice_currency_code
2091 , exchange_rate
2092 , batch_id
2093 FROM
2094 ap_invoices_all
2095 WHERE invoice_id = pn_invoice_id;
2096 BEGIN
2097 --log for debug
2098 IF ( ln_proc_level >= ln_dbg_level)
2099 THEN
2100 FND_LOG.STRING ( ln_proc_level
2101 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2102 , 'Enter procedure'
2103 );
2104 FND_LOG.STRING ( ln_proc_level
2105 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2106 , 'pn_invoice_id ' || pn_invoice_id
2107 );
2108 END IF; --( ln_proc_level >= ln_dbg_level) ;
2109
2110 OPEN Get_Invoice_Header_Infor_cur ;
2111 FETCH Get_Invoice_Header_Infor_cur
2112 INTO
2113 xn_vendor_id
2114 , xn_vendor_site_id
2115 , xv_currency_code
2116 , xn_exchange_rate
2117 , xn_batch_id ;
2118 CLOSE Get_Invoice_Header_Infor_cur ;
2119
2120 --log for debug
2121 IF ( ln_proc_level >= ln_dbg_level)
2122 THEN
2123 FND_LOG.STRING ( ln_proc_level
2124 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2125 , 'xn_vendor_id ' || xn_vendor_id
2126 );
2127 FND_LOG.STRING ( ln_proc_level
2128 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2129 , 'xn_vendor_site_id ' || xn_vendor_site_id
2130 );
2131 FND_LOG.STRING ( ln_proc_level
2132 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2133 , 'xv_currency_code ' || xv_currency_code
2134 );
2135 FND_LOG.STRING ( ln_proc_level
2136 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2137 , 'xn_exchange_rate ' || xn_exchange_rate
2138 );
2139 FND_LOG.STRING ( ln_proc_level
2140 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2141 , 'xn_batch_id ' || xn_batch_id
2142 );
2143 FND_LOG.STRING ( ln_proc_level
2144 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2145 , 'Exit procedure'
2146 );
2147 END IF; --( ln_proc_level >= ln_dbg_level )
2148 EXCEPTION
2149 WHEN OTHERS THEN
2150 IF ( ln_proc_level >= ln_dbg_level)
2151 THEN
2152 FND_LOG.STRING ( ln_proc_level
2153 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2154 || '. Other_Exception '
2155 , SQLCODE || ':' || SQLERRM
2156 );
2157 END IF; --( ln_proc_level >= ln_dbg_level) ;
2158 RAISE;
2159 END Get_Invoice_Header_Infor;
2160
2161 --==========================================================================
2162 -- PROCEDURE NAME:
2163 --
2164 -- PROCEDURE Delete_Tax_Lines Private
2165 --
2166 -- DESCRIPTION:
2167 --
2168 -- Delete exclusive taxes from ap invoice/dist lines table and
2169 -- jai_ap_invoice_line. Besides, all tax lines in jai_cmn_document_taxes
2170 -- will be deleted in case of pv_modified_only_flag='N'
2171 -- PARAMETERS:
2172 -- In: pn_invoice_id NUMBER invoice id
2173 -- pn_line_number NUMBER invoice item line number
2174 -- pv_modified_only_flag VARCHAR2 indicate flag of
2175 -- tax line modification
2176 -- Out:
2177 --
2178 --
2179 -- DESIGN REFERENCES:
2180 -- AP Technical Design 2.1.doc
2181 --
2182 -- CHANGE HISTORY:
2183 --
2184 -- 1 23-Aug-2007 Eric Ma Created
2185 -- 2 20-Nov-2007 Eric Ma modified for inclusive tax
2186 --===========================================================================
2187 PROCEDURE Delete_Tax_Lines
2188 ( pn_invoice_id NUMBER
2189 , pn_line_number NUMBER
2190 , pv_modified_only_flag VARCHAR2 DEFAULT 'N'
2191 )
2192 IS
2193 ln_invoice_id NUMBER := pn_invoice_id;
2194 ln_invoice_line_number NUMBER := pn_line_number;
2195 lv_modified_only_flag VARCHAR2 (1) := pv_modified_only_flag;
2196 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2197 ln_proc_level NUMBER := FND_LOG.level_procedure;
2198 lv_proc_name VARCHAR2 (100) := 'Delete_Tax_Lines';
2199 BEGIN
2200 --log for debug
2201 IF ( ln_proc_level >= ln_dbg_level)
2202 THEN
2203 FND_LOG.STRING ( ln_proc_level
2204 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2205 , 'Enter procedure'
2206 );
2207 FND_LOG.STRING ( ln_proc_level
2208 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2209 , 'pn_invoice_id ' || pn_invoice_id
2210 );
2211 FND_LOG.STRING ( ln_proc_level
2212 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2213 , 'pn_line_number '|| pn_line_number
2214 );
2215 FND_LOG.STRING ( ln_proc_level
2216 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2217 , 'pv_modified_only_flag ' || pv_modified_only_flag
2218 );
2219 END IF; --( ln_proc_level >= ln_dbg_level )
2220
2221 IF (lv_modified_only_flag = 'N')
2222 THEN
2223 --delete all taxes lines for the specified invoice id and invoice item line
2224 --if invoice line number is null, all lines for the invoice will be deleted
2225 DELETE
2226 FROM
2227 jai_cmn_document_taxes jcdt
2228 WHERE jcdt.source_doc_id = ln_invoice_id
2229 AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
2230 --added by eric for inclusive tax
2231 --------------------------------------------------------------------
2232 AND jcdt.source_doc_parent_line_no=
2233 NVL( ln_invoice_line_number, jcdt.source_doc_parent_line_no);
2234
2235
2236 --end of modification -----------------------------------------------
2237
2238 /*Commented out by eric for inclusive tax
2239 AND EXISTS
2240 ( SELECT
2241 'X'
2242 FROM
2243 jai_ap_invoice_lines jail
2244 WHERE jail.invoice_line_number = jcdt.source_doc_line_id
2245 AND jail.invoice_id = ln_invoice_id
2246 AND NVL(jail.parent_invoice_line_number,-1) =
2247 NVL(NVL( ln_invoice_line_number
2248 , jail.parent_invoice_line_number),-1)
2249 );
2250 */
2251 IF ( ln_proc_level >= ln_dbg_level)
2252 THEN
2253 FND_LOG.STRING( ln_proc_level
2254 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
2255 || '.Delete from jai_cmn_document_taxes'
2256 , SQL%ROWCOUNT||' ROWS DELETED '
2257 );
2258 END IF;
2259 END IF; --(lv_modified_only_flag = 'N')
2260
2261 --Delete all exclusive taxes lines for the specified invoice id and item line
2262 --number. If invoice line number is null, all exclusive tax lines for the
2263 --invoice will be deleted from ap_invoice_lines_all
2264 DELETE
2265 FROM
2266 ap_invoice_lines_all aila
2267 WHERE aila.invoice_id = ln_invoice_id
2268 AND EXISTS
2269 (
2270 SELECT
2271 'X'
2272 FROM
2273 jai_ap_invoice_lines jail
2274 WHERE jail.invoice_id = ln_invoice_id
2275 AND jail.invoice_line_number = aila.line_number
2276 AND jail.parent_invoice_line_number =
2277 NVL ( ln_invoice_line_number
2278 , parent_invoice_line_number
2279 )
2280 )
2281 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
2282
2283 IF ( ln_proc_level >= ln_dbg_level)
2284 THEN
2285 FND_LOG.STRING( ln_proc_level
2286 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
2287 || '.Delete from ap_invoice_lines_all'
2288 , SQL%ROWCOUNT||' ROWS DELETED '
2289 );
2290 END IF;--( ln_proc_level >= ln_dbg_level)
2291
2292 --Delete all exclusive taxes lines for the specified invoice id and item line
2293 --number. If invoice line number is null, all exclusive tax lines for the
2294 --invoice will be deleted from ap_invoice_distributions_all
2295 DELETE
2296 FROM
2297 ap_invoice_distributions_all aida
2298 WHERE aida.invoice_id = ln_invoice_id
2299 AND EXISTS
2300 (
2301 SELECT
2302 'X'
2303 FROM
2304 jai_ap_invoice_lines jail
2305 WHERE jail.invoice_id = ln_invoice_id
2306 AND jail.invoice_line_number = aida.invoice_line_number
2307 AND jail.parent_invoice_line_number =
2308 NVL (ln_invoice_line_number, parent_invoice_line_number)
2309 )
2310 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
2311
2312 IF ( ln_proc_level >= ln_dbg_level)
2313 THEN
2314 FND_LOG.STRING( ln_proc_level
2315 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
2316 || '.Delete from ap_invoice_distributions_all'
2317 , SQL%ROWCOUNT||' ROWS DELETED '
2318 );
2319 END IF;
2320
2321
2322 --Delete all exclusive taxes lines for the specified invoice id and item line
2323 --number. If invoice line number is null, all exclusive tax lines for the
2324 --invoice will be deleted from jai_ap_invoice_lines
2325
2326
2327 DELETE
2328 FROM
2329 jai_ap_invoice_lines
2330 WHERE invoice_id = ln_invoice_id
2331 AND parent_invoice_line_number =
2332 NVL(ln_invoice_line_number,parent_invoice_line_number)
2333 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
2334
2335 IF ( ln_proc_level >= ln_dbg_level)
2336 THEN
2337 FND_LOG.STRING( ln_proc_level
2338 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
2339 || '.Delete from jai_ap_invoice_lines'
2340 , SQL%ROWCOUNT||' ROWS DELETED '
2341 );
2342 FND_LOG.STRING ( ln_proc_level
2343 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2344 , 'Exit procedure'
2345 );
2346 END IF; --( ln_proc_level >= ln_dbg_level )
2347
2348 EXCEPTION
2349 WHEN OTHERS THEN
2350 IF ( ln_proc_level >= ln_dbg_level)
2351 THEN
2352 FND_LOG.STRING ( ln_proc_level
2353 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2354 || '. Other_Exception '
2355 , SQLCODE || ':' || SQLERRM
2356 );
2357 END IF; --( ln_proc_level >= ln_dbg_level) ;
2358 END Delete_Tax_Lines;
2359
2360 --==========================================================================
2361 -- PROCEDURE NAME:
2362 --
2363 -- PROCEDURE delete_useless_line Private
2364 --
2365 -- DESCRIPTION:
2366 --
2367 -- For a given invoice id ,delete all lines that are not related to the
2368 -- invoice. Both item lines and tax line in starndard AP and JAI
2369 -- AP module are deleted
2370 --
2371 -- PARAMETERS:
2372 -- In: pn_invoice_id NUMBER invoice id
2373 --
2374 -- Out:
2375 --
2376 --
2377 -- DESIGN REFERENCES:
2378 -- AP Technical Design 2.1.doc
2379 --
2380 -- CHANGE HISTORY:
2381 --
2382 -- 1 23-Aug-2007 Eric Ma Created
2383 -- 2 30-Nov-2007 Eric Ma Modified for inclusive tax
2384 --===========================================================================
2385 PROCEDURE Delete_Useless_Lines (pn_invoice_id IN NUMBER)
2386 IS
2387 ln_invoice_id NUMBER := pn_invoice_id;
2388 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2389 ln_proc_level NUMBER := FND_LOG.level_procedure;
2390 lv_proc_name VARCHAR2 (100) := 'Delete_Useless_Lines';
2391 BEGIN
2392 --log for debug
2393 IF ( ln_proc_level >= ln_dbg_level)
2394 THEN
2395 FND_LOG.STRING ( ln_proc_level
2396 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2397 , 'Enter procedure'
2398 );
2399 FND_LOG.STRING ( ln_proc_level
2400 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2401 , 'pn_invoice_id ' || pn_invoice_id
2402 );
2403 END IF; --( ln_proc_level >= ln_dbg_level )
2404
2405 -- when a item line is deleted from the Ap invoice work bench,
2406 -- the related tax rows have to be deleted from other 4 tables as well
2407 -- and the corresoponding item row in jai_ap_invoice_lines need to be erased
2408 -- either
2409
2410 --delete all tax lines not attached to a item that exists in AP Inv Line
2411 --table from jai_cmn_document_taxes
2412 DELETE
2413 FROM
2414 jai_cmn_document_taxes jcdt
2415 WHERE jcdt.source_doc_id = ln_invoice_id
2416 AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
2417 --modified by eric for inclusive taxes
2418 ----------------------------------------------------------------
2419 AND NOT EXISTS
2420 (
2421 SELECT
2422 'X'
2423 FROM
2424 ap_invoice_lines_all aila
2425 WHERE aila.invoice_id = ln_invoice_id
2426 AND aila.line_number = jcdt.source_doc_parent_line_no
2427 );
2428 --end of modification by eric for inclusive taxes-----------------
2429
2430 /*commented out by eric for inclusive taxes
2431
2432 AND EXISTS
2433 (
2434 SELECT
2435 'X'
2436 FROM
2437 jai_ap_invoice_lines jail
2438 WHERE jail.invoice_id = ln_invoice_id
2439 AND jail.invoice_line_number = jcdt.source_doc_line_id
2440 AND NOT EXISTS
2441 (
2442 SELECT
2443 'X'
2444 FROM
2445 ap_invoice_lines_all aila
2446 WHERE aila.invoice_id = ln_invoice_id
2447 AND aila.line_number =jail.parent_invoice_line_number
2448 )
2449 );
2450 */
2451
2452
2453 -- delete miscellaneous from ap_invoice_distributions_all
2454 DELETE
2455 FROM
2456 ap_invoice_distributions_all aida
2457 WHERE aida.invoice_id = ln_invoice_id
2458 AND EXISTS
2459 (
2460 SELECT
2461 'X'
2462 FROM
2463 jai_ap_invoice_lines jail
2464 WHERE invoice_id = ln_invoice_id
2465 AND jail.invoice_line_number = aida.invoice_line_number
2466 AND NOT EXISTS
2467 (
2468 SELECT
2469 'X'
2470 FROM
2471 ap_invoice_lines_all aila
2472 WHERE aila.invoice_id = ln_invoice_id
2473 AND aila.line_number =jail.parent_invoice_line_number
2474 )
2475 )
2476 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
2477
2478 --delete miscellaneous lines in ap_invoice_lines_all
2479 DELETE
2480 FROM
2481 ap_invoice_lines_all aila
2482 WHERE aila.invoice_id = ln_invoice_id
2483 AND EXISTS
2484 (
2485 SELECT
2486 'X'
2487 FROM
2488 jai_ap_invoice_lines jail
2489 WHERE jail.invoice_id = ln_invoice_id
2490 AND jail.invoice_line_number = aila.line_number
2491 AND NOT EXISTS
2492 (
2493 SELECT
2494 'X'
2495 FROM
2496 ap_invoice_lines_all aila
2497 WHERE aila.invoice_id =ln_invoice_id
2498 AND aila.line_number = jail.parent_invoice_line_number
2499 )
2500 )
2501 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
2502
2503 -- delete ITEM lines from jai_ap_invoice_lines
2504 DELETE
2505 FROM
2506 jai_ap_invoice_lines jail
2507 WHERE jail.invoice_id = ln_invoice_id
2508 AND NOT EXISTS
2509 (
2510 SELECT
2511 line_number
2512 FROM
2513 ap_invoice_lines_all aila
2514 WHERE aila.invoice_id = ln_invoice_id
2515 AND aila.line_number = jail.invoice_line_number
2516 )
2517 AND line_type_lookup_code = GV_CONSTANT_ITEM;
2518
2519 -- delete miscelleaneous line from jai_ap_invoice_lines
2520 DELETE
2521 FROM
2522 jai_ap_invoice_lines jail
2523 WHERE
2524 jail.invoice_id = ln_invoice_id
2525 AND NOT EXISTS
2526 (
2527 SELECT
2528 'X'
2529 FROM
2530 ap_invoice_lines_all aila
2531 WHERE aila.invoice_id =ln_invoice_id
2532 AND aila.line_number = jail.parent_invoice_line_number
2533 )
2534 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
2535
2536 --log for debug
2537 IF ( ln_proc_level >= ln_dbg_level)
2538 THEN
2539 FND_LOG.STRING ( ln_proc_level
2540 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2541 , 'Exit procedure'
2542 );
2543 END IF; --( ln_proc_level >= ln_dbg_level )
2544 EXCEPTION
2545 WHEN OTHERS THEN
2546 IF ( ln_proc_level >= ln_dbg_level)
2547 THEN
2548 FND_LOG.STRING ( ln_proc_level
2549 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2550 || '. Other_Exception '
2551 , SQLCODE || ':' || SQLERRM
2552 );
2553 END IF; --( ln_proc_level >= ln_dbg_level) ;
2554 END Delete_Useless_Lines;
2555
2556 --==========================================================================
2557 -- PROCEDURE NAME:
2558 --
2559 -- Update_Jai_Line_Amount Private
2560 --
2561 -- DESCRIPTION:
2562 --
2563 -- update item lines in jai_ap_invoice_lines table,tax category,
2564 -- location_id, can be changed from IL form while line amount, currency,
2565 -- vendor_site_id, are only allowed to be modified from AP invoice work
2566 -- bench.
2567 --
2568 -- PARAMETERS:
2569 -- In: pn_invoice_id NUMBER
2570 -- pn_line_number NUMBER
2571 -- pn_line_amount NUMBER
2572 --
2573 -- Out:
2574 --
2575 --
2576 -- DESIGN REFERENCES:
2577 -- AP Technical Design 2.1.doc
2578 --
2579 -- CHANGE HISTORY:
2580 --
2581 -- 1 09-SEP-2007 Eric Ma Created
2582 --
2583 --===========================================================================
2584 PROCEDURE Update_Jai_Line_Amount
2585 ( pn_invoice_id IN NUMBER
2586 , pn_line_number IN NUMBER
2587 , pn_line_amount IN NUMBER
2588 )
2589 IS
2590 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2591 ln_proc_level NUMBER := FND_LOG.level_procedure;
2592 lv_proc_name VARCHAR2 (100) := 'Update_Jai_Line_Amount';
2593 BEGIN
2594 IF ( ln_proc_level >= ln_dbg_level)
2595 THEN
2596 FND_LOG.STRING ( ln_proc_level
2597 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2598 , 'Enter procedure'
2599 );
2600 FND_LOG.STRING ( ln_proc_level
2601 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2602 , 'pn_invoice_id ' || pn_invoice_id
2603 );
2604 FND_LOG.STRING ( ln_proc_level
2605 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2606 , 'pn_line_number ' || pn_line_number
2607 );
2608 FND_LOG.STRING ( ln_proc_level
2609 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2610 , 'pn_line_amount ' || pn_line_amount
2611 );
2612 END IF; --( ln_proc_level >= ln_dbg_level )
2613
2614 UPDATE
2615 jai_ap_invoice_lines
2616 SET
2617 line_amount = pn_line_amount
2618 where invoice_id = pn_invoice_id
2619 AND invoice_line_number = pn_line_number ;
2620
2621 --log for debug
2622 IF ( ln_proc_level >= ln_dbg_level)
2623 THEN
2624 FND_LOG.STRING ( ln_proc_level
2625 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
2626 || '.DML (UPDATE jai_ap_invoice_lines)'
2627 ,SQL%ROWCOUNT || ' ROWS UPDATED.'
2628 );
2629 FND_LOG.STRING ( ln_proc_level
2630 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2631 , 'Exit procedure'
2632 );
2633 END IF; --( ln_proc_level >= ln_dbg_level )
2634
2635 EXCEPTION
2636 WHEN OTHERS THEN
2637 IF ( ln_proc_level >= ln_dbg_level)
2638 THEN
2639 FND_LOG.STRING ( ln_proc_level
2640 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2641 || '. Other_Exception '
2642 , SQLCODE || ':' || SQLERRM
2643 );
2644 END IF; --( ln_proc_level >= ln_dbg_level) ;
2645 END Update_Jai_Line_Amount;
2646
2647 --==========================================================================
2648 -- PROCEDURE NAME:
2649 --
2650 -- Update_Jai_Item_Info Private
2651 --
2652 -- DESCRIPTION:
2653 --
2654 -- update item lines in jai_ap_invoice_lines table,tax category,
2655 -- location_id, can be changed from IL form while line amount, currency,
2656 -- vendor_site_id, are only allowed to be modified from AP invoice work
2657 -- bench.
2658 --
2659 -- PARAMETERS:
2660 -- In: pn_invoice_id NUMBER
2661 -- pn_vndr_site_id NUMBER
2662 -- pn_currency_code NUMBER
2663 --
2664 -- Out:
2665 --
2666 --
2667 -- DESIGN REFERENCES:
2668 -- AP Technical Design 2.1.doc
2669 --
2670 -- CHANGE HISTORY:
2671 --
2672 -- 1 09-SEP-2007 Eric Ma Created
2673 --
2674 --===========================================================================
2675 PROCEDURE Update_Jai_Item_Info
2676 ( pn_invoice_id IN NUMBER
2677 , pn_vndr_site_id IN NUMBER
2678 , pn_currency_code IN VARCHAR2
2679 , pn_tax_category_id IN NUMBER
2680 )
2681 IS
2682 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2683 ln_proc_level NUMBER := FND_LOG.level_procedure;
2684 lv_proc_name VARCHAR2 (100) := 'Update_Jai_Item_Info';
2685 BEGIN
2686 IF ( ln_proc_level >= ln_dbg_level)
2687 THEN
2688 FND_LOG.STRING ( ln_proc_level
2689 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2690 , 'Enter procedure'
2691 );
2692 FND_LOG.STRING ( ln_proc_level
2693 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2694 , 'pn_invoice_id ' || pn_invoice_id
2695 );
2696 FND_LOG.STRING ( ln_proc_level
2697 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2698 , 'pn_vndr_site_id ' || pn_vndr_site_id
2699 );
2700 FND_LOG.STRING ( ln_proc_level
2701 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2702 , 'pn_currency_code ' || pn_currency_code
2703 );
2704 FND_LOG.STRING ( ln_proc_level
2705 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2706 , 'pn_tax_category_id ' || pn_tax_category_id
2707 );
2708 END IF; --( ln_proc_level >= ln_dbg_level )
2709
2710
2711 UPDATE
2712 jai_ap_invoice_lines
2713 SET
2714 supplier_site_id = pn_vndr_site_id
2715 , currency_code = pn_currency_code
2716 , tax_category_id = pn_tax_category_id
2717 WHERE invoice_id = pn_invoice_id
2718 AND line_type_lookup_code = GV_CONSTANT_ITEM ;
2719
2720 --log for debug
2721 IF ( ln_proc_level >= ln_dbg_level)
2722 THEN
2723 FND_LOG.STRING ( ln_proc_level
2724 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
2725 || '.DML (UPDATE jai_ap_invoice_lines)'
2726 ,SQL%ROWCOUNT || ' ROWS UPDATED.'
2727 );
2728 FND_LOG.STRING ( ln_proc_level
2729 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2730 , 'Exit procedure'
2731 );
2732 END IF; --( ln_proc_level >= ln_dbg_level )
2733
2734 EXCEPTION
2735 WHEN OTHERS THEN
2736 IF ( ln_proc_level >= ln_dbg_level)
2737 THEN
2738 FND_LOG.STRING ( ln_proc_level
2739 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2740 || '. Other_Exception '
2741 , SQLCODE || ':' || SQLERRM
2742 );
2743 END IF; --( ln_proc_level >= ln_dbg_level) ;
2744 END Update_Jai_Item_Info;
2745
2746 --==========================================================================
2747 -- PROCEDURE NAME:
2748 --
2749 -- Populate_Stnd_Inv_Taxes Public
2750 --
2751 -- DESCRIPTION:
2752 --
2753 -- This procedure is main entrance procedure used by form JAINAPST and
2754 -- standard AP invoice workbench.it invokes the procedure create_tax_line
2755 -- to populate the tax lines
2756 --
2757 -- PARAMETERS:
2758 -- In: pn_invoice_id number
2759 -- pn_line_number NUMBER
2760 -- pn_vendor_site_id NUMBER
2761 -- pv_currency VARCHAR2
2762 -- pn_line_amount NUMBER
2763 -- pn_tax_category_id number
2764 -- pv_tax_modified VARCHAR2
2765 --
2766 --
2767 -- Out:
2768 --
2769 --
2770 -- DESIGN REFERENCES:
2771 -- AP Technical Design 2.1.doc
2772 --
2773 -- CHANGE HISTORY:
2774 --
2775 -- 1 23-Aug-2007 Eric Ma Created
2776 -- 2 19-Mar-2008 Changed Default_Calculate_Taxes for bug#6898716
2777 -- 3 23-Apr-2008 Eric Ma Code change in Populate_Stnd_Inv_Taxes for bug6923963
2778 --===========================================================================
2779 PROCEDURE Populate_Stnd_Inv_Taxes
2780 ( pn_invoice_id NUMBER
2781 , pn_line_number NUMBER
2782 , pn_vendor_site_id NUMBER
2783 , pv_currency VARCHAR2
2784 , pn_line_amount NUMBER DEFAULT NULL
2785 , pn_tax_category_id NUMBER DEFAULT NULL
2786 , pv_tax_modified VARCHAR2
2787 , pn_old_tax_category_id VARCHAR2
2788 )
2789
2790 IS
2791 ln_std_invoice_id NUMBER := pn_invoice_id;
2792 ln_std_line_number NUMBER := pn_line_number;
2793 ln_std_vendor_site_id NUMBER := pn_vendor_site_id;
2794 lv_std_currency_code VARCHAR2 (15) := pv_currency;
2795 ln_std_tax_category_id NUMBER := pn_tax_category_id;
2796 ln_jai_tax_line_ctg_id NUMBER;
2797 lv_std_tax_modified VARCHAR2 (1) := pv_tax_modified;
2798 ln_jai_vndr_site_id NUMBER := NULL;
2799 lv_jai_currency_code VARCHAR2 (15) := NULL;
2800 ln_jai_tax_category_id NUMBER := NULL;
2801 ln_jai_line_amount NUMBER := NULL;
2802 ln_vendor_id NUMBER;
2803 ln_vendor_site_id NUMBER;
2804 lv_currency_code VARCHAR2 (15);
2805 ln_exchange_rate NUMBER;
2806 lv_service_type_code VARCHAR2 (30);
2807 ln_batch_id NUMBER;
2808 ln_tax_category_id NUMBER;
2809 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2810 ln_proc_level NUMBER := FND_LOG.level_procedure;
2811 lv_proc_name VARCHAR2 (100) := 'Populate_Stnd_Inv_Taxes';
2812 ln_supplier_id NUMBER;
2813 l_chk_del_flag VARCHAR2(1) ;
2814 ln_tax_line_no NUMBER; -- Added by eric ma for the bug 6898716 on Mar 19,2008
2815 -- Get the details of inv in the IL table
2816
2817 CURSOR jai_invoice_exist_cur IS
2818 SELECT
2819 supplier_site_id
2820 , currency_code
2821 , tax_category_id
2822 , line_amount
2823 FROM
2824 jai_ap_invoice_lines
2825 WHERE invoice_id = pn_invoice_id
2826 AND invoice_line_number = NVL (pn_line_number, invoice_line_number)
2827 AND line_type_lookup_code = GV_CONSTANT_ITEM;
2828
2829 CURSOR jai_tax_line_ctg_cur IS
2830 SELECT
2831 tax_category_id
2832 FROM
2833 jai_ap_invoice_lines
2834 WHERE invoice_id = pn_invoice_id
2835 AND parent_invoice_line_number = pn_line_number;
2836
2837
2838
2839 -- Get the details of supplier id
2840 CURSOR jai_get_supplier_id (pn_invoice_id NUMBER) IS
2841 SELECT vendor_id
2842 FROM ap_invoices_all
2843 WHERE invoice_id = pn_invoice_id;
2844
2845
2846 -- Get the tax category_id of supplier id
2847 cursor get_setup_tax_category_id ( p_supplier_id number , p_supplier_site_id number) is
2848 select tax_category_id from jai_cmn_vendor_sites where vendor_id =p_supplier_id
2849 and vendor_site_id = p_supplier_site_id;
2850
2851
2852
2853 --Get the changed amount in the invoice line level
2854 --part 1 is the case of line amount changed
2855 --part 2 is the case of line added or deleted
2856 CURSOR diff_inv_lines_cur IS
2857 SELECT
2858 apia.line_number line_number
2859 , apia.amount line_amount
2860 FROM
2861 ap_invoice_lines_all apia
2862 , jai_ap_invoice_lines jail
2863 WHERE apia.invoice_id = jail.invoice_id
2864 AND apia.line_number = jail.invoice_line_number
2865 AND apia.invoice_id = ln_std_invoice_id
2866 AND apia.amount <> jail.line_amount
2867 AND apia.line_type_lookup_code = jail.line_type_lookup_code
2868 AND apia.line_type_lookup_code = GV_CONSTANT_ITEM
2869
2870 UNION ALL
2871
2872 SELECT
2873 apia.line_number line_number
2874 , apia.amount line_amount
2875 FROM
2876 ap_invoice_lines_all apia
2877 , jai_ap_invoice_lines jail
2878 WHERE apia.invoice_id = jail.invoice_id (+)
2879 AND apia.line_number = jail.invoice_line_number(+)
2880 AND apia.invoice_id = ln_std_invoice_id
2881 AND apia.line_type_lookup_code = GV_CONSTANT_ITEM
2882 AND jail.invoice_id IS NULL
2883 AND jail.invoice_line_number IS NULL;
2884
2885 BEGIN
2886 --log for debug
2887 IF ( ln_proc_level >= ln_dbg_level)
2888 THEN
2889 FND_LOG.STRING ( ln_proc_level
2890 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2891 , 'Enter procedure'
2892 );
2893 FND_LOG.STRING ( ln_proc_level
2894 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2895 , 'pn_invoice_id ' || pn_invoice_id
2896 );
2897 FND_LOG.STRING ( ln_proc_level
2898 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2899 , 'pn_line_number ' || pn_line_number
2900 );
2901 FND_LOG.STRING ( ln_proc_level
2902 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2903 , 'pn_vendor_site_id ' || pn_vendor_site_id
2904 );
2905 FND_LOG.STRING ( ln_proc_level
2906 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2907 , 'pv_currency ' || pv_currency
2908 );
2909 FND_LOG.STRING ( ln_proc_level
2910 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2911 , 'pn_line_amount ' || pn_line_amount
2912 );
2913 FND_LOG.STRING ( ln_proc_level
2914 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2915 , 'pn_tax_category_id ' || pn_tax_category_id
2916 );
2917 FND_LOG.STRING ( ln_proc_level
2918 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2919 , 'pv_tax_modified ' || pv_tax_modified
2920 );
2921 FND_LOG.STRING ( ln_proc_level
2922 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2923 , 'pn_old_tax_category_id ' || pn_old_tax_category_id
2924 );
2925 END IF; --( ln_proc_level >= ln_dbg_level)
2926
2927 --insert into eric_log values ( 0.1,'pn_invoice_id :'||pn_invoice_id,sysdate);
2928 --insert into eric_log values ( 0.2,'pn_line_number :'||pn_line_number,sysdate);
2929 --insert into eric_log values ( 0.3,'pn_vendor_site_id :'||pn_vendor_site_id,sysdate);
2930 --insert into eric_log values ( 0.4,'pv_currency :'||pv_currency,sysdate);
2931 --insert into eric_log values ( 0.5,'pn_line_amount :'||pn_line_amount,sysdate);
2932 --insert into eric_log values ( 0.6,'pn_tax_category_id :'||pn_tax_category_id,sysdate);
2933 --insert into eric_log values ( 0.7,'pv_tax_modified :'||pv_tax_modified,sysdate);
2934
2935
2936
2937
2938 -- if any of item line fails the mandatory distirbution lines validation
2939 -- no tax lines will be processed
2940 IF ((Validate_Item_Dist_Lines( ln_std_invoice_id))AND (NOT Validate_3rd_party_cm_Invoice(ln_std_invoice_id)) )
2941 -- , ln_std_line_number)) --Added a parameter for validate function by Jia Li on 2008/01/25
2942 -- according eakta's require.
2943 --,commented out for the bug of deleting not working on Jan 28,2008
2944 THEN
2945
2946 --insert into eric_log values ( 1.1,'Item_Dist_Lines_Validated',sysdate);
2947
2948 --only the tax_category_id in the cursor can be modified in the IL form
2949 --if the current function is invoked by IL form, line_number will be
2950 --specified. in the standard AP invoice form only line amount is changed
2951 --in the line level while vndr_site_id or currency_code are in header level
2952 --we need fetch one line rather than loop every item lines here
2953
2954 OPEN jai_invoice_exist_cur;
2955 FETCH jai_invoice_exist_cur
2956 INTO
2957 ln_jai_vndr_site_id
2958 , lv_jai_currency_code
2959 , ln_jai_tax_category_id
2960 , ln_jai_line_amount;
2961 CLOSE jai_invoice_exist_cur;
2962 --insert into eric_log values ( 1.2,'ln_jai_vndr_site_id :'||ln_jai_vndr_site_id,sysdate);
2963 --insert into eric_log values ( 1.3,'lv_jai_currency_code :'||lv_jai_currency_code,sysdate);
2964 --insert into eric_log values ( 1.4,'ln_jai_tax_category_id :'||ln_jai_tax_category_id,sysdate);
2965 --insert into eric_log values ( 1.5,'ln_jai_line_amount :'||ln_jai_line_amount,sysdate);
2966 OPEN jai_tax_line_ctg_cur;
2967 FETCH jai_tax_line_ctg_cur
2968 INTO
2969 ln_jai_tax_line_ctg_id ;
2970 CLOSE jai_tax_line_ctg_cur;
2971 --insert into eric_log values ( 1.6,'ln_jai_tax_line_ctg_id :'||ln_jai_tax_line_ctg_id,sysdate);
2972
2973 OPEN jai_get_supplier_id (pn_invoice_id);
2974 FETCH jai_get_supplier_id into ln_supplier_id;
2975 close jai_get_supplier_id;
2976 --insert into eric_log values ( 1.7,'ln_supplier_id :'||ln_supplier_id,sysdate);
2977
2978 IF((ln_jai_tax_line_ctg_id IS NULL) AND (pn_old_tax_category_id IS NOT NULL))
2979 THEN
2980 ln_jai_tax_line_ctg_id := pn_old_tax_category_id ;
2981 END IF;
2982
2983 --insert into eric_log values ( 1.8,'ln_jai_tax_line_ctg_id :'||ln_jai_tax_line_ctg_id,sysdate);
2984
2985 --added by eric ma on Mar 19 ,2008 for the bug 6898716 ,begin
2986 ---------------------------------------------------------------------------
2987 SELECT
2988 COUNT(*)
2989 INTO
2990 ln_tax_line_no
2991 FROM
2992 jai_cmn_document_taxes jcdt
2993 WHERE jcdt.source_doc_id = pn_invoice_id
2994 AND jcdt.source_doc_parent_line_no = pn_line_number
2995 AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice;
2996
2997
2998 IF ln_tax_line_no > 0
2999 THEN
3000 GV_LINES_CREATEED := 'YES';
3001 --added by eric for bug# 6923963 on Apr 23,2008,begin
3002 ------------------------------------------------------
3003 ELSE
3004 GV_LINES_CREATEED := 'NO';
3005 ----------------------------------------------------
3006 --added by eric for bug# 6923963 on Apr 23,2008,end
3007 END IF;
3008
3009 --insert into eric_log values ( 1.9,'GV_LINES_CREATEED :'||GV_LINES_CREATEED,sysdate) ;
3010 ---------------------------------------------------------------------------
3011 --added by eric ma on Mar 19 ,2008 for the bug 6898716 ,end
3012
3013 -- standalone invoice insert is going to happen in the following cases
3014
3015 -- 1. create a new invoice from ap invoice workbench
3016 -- 2. attach a tax category to an existing invoice line whose tax ctg is null
3017 -- 3. user again attaches a tax category to an invoice which was previouly removed
3018
3019 IF ((ln_jai_vndr_site_id IS NULL and GV_LINES_CREATEED = 'NO')
3020 --added "and GV_LINES_CREATEED = 'NO'" in the below two lines
3021 --modified by eric ma on Mar 19 ,2008 for the bug 6898716 ,begin
3022 -------------------------------------------------------------
3023 OR (ln_jai_tax_category_id IS NULL AND ln_std_tax_category_id IS NOT NULL and GV_LINES_CREATEED = 'NO' )
3024 OR (ln_jai_tax_line_ctg_id IS NULL AND ln_std_tax_category_id IS NOT NULL and GV_LINES_CREATEED = 'NO' )
3025 )
3026 -------------------------------------------------------------
3027 --modified by eric ma on Mar 19 ,2008 for the bug 6898716 ,end
3028 THEN
3029 IF ( ln_proc_level >= ln_dbg_level)
3030 THEN
3031 FND_LOG.STRING ( ln_proc_level
3032 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
3033 , 'Case 1'
3034 );
3035 END IF;-- ( ln_proc_level >= ln_dbg_level)
3036 --insert into eric_log values ( 2.1,'Case 1',sysdate);
3037
3038 if ln_std_tax_category_id is null then
3039
3040 ln_std_tax_category_id := JAI_AP_IL_ORG_PKG.fun_tax_cat_id (ln_supplier_id ,
3041 ln_std_vendor_site_id ,
3042 ln_std_invoice_id ,
3043 ln_std_line_number );
3044 end if ;
3045 --insert into eric_log values ( 2.11,'ln_std_tax_category_id :'||ln_std_tax_category_id,sysdate);
3046 --insert into eric_log values ( 2.12,'lv_std_tax_modified :'||lv_std_tax_modified,sysdate);
3047 --insert into eric_log values ( 2.13,'ln_std_line_number :'||ln_std_line_number,sysdate);
3048 --insert
3049 Create_Tax_Lines ( pn_organization_id => NULL
3050 , pv_currency => lv_std_currency_code
3051 , pn_location_id => NULL
3052 , pn_invoice_id => ln_std_invoice_id
3053 , pn_line_number => ln_std_line_number
3054 , pn_tax_category_id => ln_std_tax_category_id
3055 , pv_tax_modified => lv_std_tax_modified
3056 );
3057 --insert into eric_log values ( 2.14,'Create_Tax_Lines() done',sysdate);
3058
3059 GV_LINES_CREATEED := 'YES';
3060 --insert into eric_log values ( 2.15,'GV_LINES_CREATEED :'||GV_LINES_CREATEED,sysdate);
3061 END IF;
3062
3063 -- vendor site is changed in AP , currency is updated in AP ,
3064 -- delete the taxes ,update related information in jai_ap_invoice_lines
3065 -- and recalculate tax lines
3066
3067 IF ( ( ln_jai_vndr_site_id <> ln_std_vendor_site_id)
3068 OR ( lv_jai_currency_code <> lv_std_currency_code)
3069 )
3070 THEN
3071 IF ( ln_proc_level >= ln_dbg_level)
3072 THEN
3073 FND_LOG.STRING ( ln_proc_level
3074 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
3075 , 'Case 2'
3076 );
3077 END IF;-- ( ln_proc_level >= ln_dbg_level)
3078
3079 --insert into eric_log values ( 2.2,'Case 2',sysdate);
3080
3081 open get_setup_tax_category_id ( ln_supplier_id , ln_std_vendor_site_id );
3082 fetch get_setup_tax_category_id into ln_std_tax_category_id;
3083 close get_setup_tax_category_id;
3084
3085
3086
3087 Delete_Tax_Lines ( pn_invoice_id => ln_std_invoice_id
3088 , pn_line_number => ln_std_line_number
3089 );
3090
3091
3092 Get_Invoice_Header_Infor ( pn_invoice_id => ln_std_invoice_id
3093 , xn_vendor_id => ln_vendor_id
3094 , xn_vendor_site_id => ln_vendor_site_id
3095 , xv_currency_code => lv_currency_code
3096 , xn_exchange_rate => ln_exchange_rate
3097 , xn_batch_id => ln_batch_id
3098 );
3099
3100 Get_Tax_Cat_Serv_Type ( pn_vendor_id => ln_vendor_id
3101 , pn_vendor_site_id => ln_vendor_site_id
3102 , xn_tax_category_id => ln_std_tax_category_id ---ln_tax_category_id
3103 , xv_service_type_code => lv_service_type_code
3104 );
3105
3106 Update_Jai_Item_Info
3107 ( pn_invoice_id => ln_std_invoice_id
3108 , pn_vndr_site_id => ln_std_vendor_site_id
3109 , pn_currency_code => lv_std_currency_code
3110 , pn_tax_category_id => ln_std_tax_category_id ---ln_tax_category_id
3111 );
3112
3113
3114 /* ln_std_tax_category_id := JAI_AP_IL_ORG_PKG.fun_tax_cat_id (ln_supplier_id ,
3115 ln_std_vendor_site_id ,
3116 ln_std_invoice_id ,
3117 ln_std_line_number ); */
3118
3119
3120
3121 --insert
3122 Create_Tax_Lines ( pn_organization_id => NULL
3123 , pv_currency => lv_std_currency_code
3124 , pn_location_id => NULL
3125 , pn_invoice_id => ln_std_invoice_id
3126 , pn_line_number => ln_std_line_number
3127 , pn_tax_category_id => ln_std_tax_category_id
3128 , pv_tax_modified => lv_std_tax_modified
3129 );
3130
3131 GV_LINES_CREATEED := 'YES';
3132 END IF;--(vndr_site_id changed or currency_code changed)
3133
3134 --as the data may be changed by case 1 , re-selete table for getting the latest values.
3135 --added by eric for bug# 6923963 on Apr 23,2008,begin
3136 ------------------------------------------------------
3137 OPEN jai_invoice_exist_cur;
3138 FETCH jai_invoice_exist_cur
3139 INTO
3140 ln_jai_vndr_site_id
3141 , lv_jai_currency_code
3142 , ln_jai_tax_category_id
3143 , ln_jai_line_amount;
3144 CLOSE jai_invoice_exist_cur;
3145 --insert into eric_log values ( 10.2,'ln_jai_vndr_site_id :'||ln_jai_vndr_site_id,sysdate);
3146 --insert into eric_log values ( 10.3,'lv_jai_currency_code :'||lv_jai_currency_code,sysdate);
3147 --insert into eric_log values ( 10.4,'ln_jai_tax_category_id :'||ln_jai_tax_category_id,sysdate);
3148 --insert into eric_log values ( 10.5,'ln_jai_line_amount :'||ln_jai_line_amount,sysdate);
3149 OPEN jai_tax_line_ctg_cur;
3150 FETCH jai_tax_line_ctg_cur
3151 INTO
3152 ln_jai_tax_line_ctg_id ;
3153 CLOSE jai_tax_line_ctg_cur;
3154 --insert into eric_log values ( 10.6,'ln_jai_tax_line_ctg_id :'||ln_jai_tax_line_ctg_id,sysdate);
3155
3156 ------------------------------------------------------
3157 --added by eric for bug# 6923963 on Apr 23,2008,end
3158
3159
3160
3161 -- tax category changed from IL form
3162 -- tax lines exists but the ctg_id in tax lines are different
3163 -- OR the original tax category is null and tax lines exist
3164 -- from the tax ctgs of item line
3165 IF ( ( ln_jai_tax_line_ctg_id IS NOT NULL
3166 AND ln_std_tax_category_id IS NOT NULL
3167 AND ln_std_tax_category_id <> ln_jai_tax_line_ctg_id
3168 )
3169 --added the below creteria for bug 6898716
3170 --added by eric ma on Mar 19 ,2008 for the bug 6898716 ,begin
3171 -------------------------------------------------------------
3172 OR
3173 ( GV_LINES_CREATEED = 'YES'
3174 AND ln_jai_tax_line_ctg_id IS NULL
3175 AND ln_std_tax_category_id IS NOT NULL
3176 )
3177 -------------------------------------------------------------
3178 --added by eric ma on Mar 19 ,2008 for the bug 6898716 ,end
3179 )
3180 THEN
3181
3182 IF ( ln_proc_level >= ln_dbg_level)
3183 THEN
3184 FND_LOG.STRING ( ln_proc_level
3185 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
3186 , 'Case 3'
3187 );
3188 END IF; -- ( ln_proc_level >= ln_dbg_level)
3189 --insert into eric_log values ( 2.3,'Case 3',sysdate);
3190
3191 Delete_Tax_Lines ( pn_invoice_id => ln_std_invoice_id
3192 , pn_line_number => ln_std_line_number
3193 );
3194
3195 --insert
3196 Create_Tax_Lines ( pn_organization_id => NULL
3197 , pv_currency => lv_std_currency_code
3198 , pn_location_id => NULL
3199 , pn_invoice_id => ln_std_invoice_id
3200 , pn_line_number => ln_std_line_number
3201 , pn_tax_category_id => ln_std_tax_category_id
3202 , pv_tax_modified => lv_std_tax_modified
3203 );
3204
3205 --if the category changed, ignore the changes of tax lines level
3206 lv_std_tax_modified :='N';
3207 END IF;
3208
3209 -- the tax category is updated to null in IL form delete the taxes
3210 -- from all the tables
3211
3212
3213 /* IF ( ln_std_tax_category_id IS NULL
3214 AND ln_jai_tax_line_ctg_id IS NOT NULL
3215 AND GV_LINES_CREATEED = 'NO') THEN */--
3216
3217
3218
3219 if ln_std_tax_category_id IS NULL and ln_jai_tax_line_ctg_id IS NOT NULL then
3220
3221 /* vendor updated and GV_LINES_CREATEED is yes , nothing has to be deleted
3222 l_chk_del_flag = 'Y' is to stop deletion of tax lines due to second call of Post form
3223 commit trigger in APXINWKB */
3224
3225 IF pn_old_tax_category_id is null
3226 THEN
3227 l_chk_del_flag := 'Y' ;
3228
3229 END IF;
3230
3231 if nvl(l_chk_del_flag, '$' ) = 'Y' then
3232
3233 null;
3234 -- invoice is queried and tax-category_id is updated to null
3235 elsif GV_LINES_CREATEED = 'NO' then
3236 Delete_Tax_Lines ( pn_invoice_id => ln_std_invoice_id
3237 , pn_line_number => ln_std_line_number
3238 );
3239
3240 -- new invoice is created and then tax category is set to null without closing the form
3241 elsif GV_LINES_CREATEED = 'YES' then
3242 Delete_Tax_Lines ( pn_invoice_id => ln_std_invoice_id
3243 , pn_line_number => ln_std_line_number
3244 );
3245 end if;
3246
3247 END IF;
3248
3249 -- the tax category remains unchanged but the user enters new taxes
3250 -- DO NOT DELETE lines from jai_cmn_document_taxes
3251 -- delte from other tables and then default
3252 IF ( NVL ( ln_std_tax_category_id, -999) =
3253 NVL ( ln_jai_tax_line_ctg_id, -999)
3254 AND NVL (lv_std_tax_modified, 'N') = 'Y'
3255 )
3256 THEN
3257 --insert into eric_log values ( 2.5,'Case 5',sysdate);
3258
3259 IF ( ln_proc_level >= ln_dbg_level)
3260 THEN
3261 FND_LOG.STRING ( ln_proc_level
3262 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
3263 , 'Case 5'
3264 );
3265 END IF; -- ( ln_proc_level >= ln_dbg_level)
3266
3267 --insert
3268 Create_Tax_Lines ( pn_organization_id => NULL
3269 , pv_currency => lv_std_currency_code
3270 , pn_location_id => NULL
3271 , pn_invoice_id => ln_std_invoice_id
3272 , pn_line_number => ln_std_line_number
3273 , pn_tax_category_id => ln_std_tax_category_id
3274 , pv_tax_modified => lv_std_tax_modified
3275 );
3276 END IF; --( (lv_std_tax_modified, 'N') = 'Y',Modified Tax line in IL form)
3277
3278 -- 1. When the line amount is changed in the standard form
3279 -- program going to this branch
3280 -- 2. A new item line inserted from standard AP form
3281
3282 -- For the first case, program need to update the item line amount in
3283 -- jai_ap_invoice_lines
3284 --get invoice header information
3285
3286
3287 FOR diff_inv_lines_rec IN diff_inv_lines_cur
3288 LOOP
3289
3290 IF ( ln_proc_level >= ln_dbg_level)
3291 THEN
3292 FND_LOG.STRING ( ln_proc_level
3293 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
3294 , 'Case 6'
3295 );
3296 END IF;-- ( ln_proc_level >= ln_dbg_level)
3297 --insert into eric_log values ( 2.6,'Case 6',sysdate);
3298 Get_Invoice_Header_Infor ( ln_std_invoice_id
3299 , ln_vendor_id
3300 , ln_vendor_site_id
3301 , lv_currency_code
3302 , ln_exchange_rate
3303 , ln_batch_id
3304 );
3305 --insert into eric_log values ( 2.61,'ln_vendor_id :'|| ln_vendor_id,sysdate);
3306 --insert into eric_log values ( 2.62,'ln_vendor_site_id :'|| ln_vendor_site_id,sysdate);
3307
3308 Get_Tax_Cat_Serv_Type ( pn_vendor_id => ln_vendor_id
3309 , pn_vendor_site_id => ln_vendor_site_id
3310 , xn_tax_category_id => ln_tax_category_id
3311 , xv_service_type_code => lv_service_type_code
3312 );
3313 --insert into eric_log values ( 2.63,'ln_tax_category_id :'|| ln_tax_category_id,sysdate);
3314 --insert into eric_log values ( 2.64,'lv_service_type_code :'|| lv_service_type_code,sysdate);
3315
3316 Delete_Tax_Lines
3317 ( pn_invoice_id => ln_std_invoice_id
3318 , pn_line_number => diff_inv_lines_rec.line_number
3319 );
3320 --insert into eric_log values ( 2.65,'ln_std_invoice_id :'|| ln_std_invoice_id,sysdate);
3321 --insert into eric_log values ( 2.66,'line_number :'||diff_inv_lines_rec.line_number,sysdate);
3322
3323
3324 Update_Jai_Line_Amount
3325 ( pn_invoice_id => ln_std_invoice_id
3326 , pn_line_number => diff_inv_lines_rec.line_number
3327 , pn_line_amount => diff_inv_lines_rec.line_amount
3328 );
3329 --insert into eric_log values ( 2.67,'line_amount :'|| diff_inv_lines_rec.line_amount,sysdate);
3330
3331 --insert
3332 Create_Tax_Lines( pn_organization_id => NULL
3333 , pv_currency => lv_std_currency_code
3334 , pn_location_id => NULL
3335 , pn_invoice_id => ln_std_invoice_id
3336 , pn_line_number => diff_inv_lines_rec.line_number
3337 , pn_tax_category_id => ln_tax_category_id
3338 , pv_tax_modified => lv_std_tax_modified
3339 );
3340 END LOOP; --(diff_inv_lines_rec IN diff_inv_lines_cur)
3341
3342 --delete item lines from Standard Invoice Work Bench
3343 --then delete its related taxes invoice lines and tax dist lines
3344 Delete_Useless_Lines ( pn_invoice_id => ln_std_invoice_id );
3345
3346 --allocate tax amount in distribution lines according to the propotion
3347 --of item distirbution lines
3348 Allocate_Tax_Dist_Lines
3349 ( pn_invoice_id => ln_std_invoice_id
3350 , pn_invoice_item_line_number => ln_std_line_number
3351 );
3352 COMMIT;
3353
3354 ELSE -- (Validate_Item_Dist_Lines not passed or Validate_3rd_party_cm_Invoice(ln_std_invoice_id) passed)
3355
3356 --insert into eric_log values ( 1.20,'Error : Item_Dist_Lines_not_Validated or 3rd_party_cm_Invoice_validated',sysdate);
3357 NULL ;
3358 END IF; -- (Validate_Item_Dist_Lines(ln_std_invoice_id))
3359
3360
3361 /* Error msg will be populated AP Invoice workbench when changing the data in header
3362 UPDATE
3363 AP_INVOICES_ALL
3364 SET
3365 invoice_amount = (SELECT SUM(amount) FROM AP_INVOICE_LINES_ALL WHERE invoice_id =pn_invoice_id ),
3366 base_amount = (SELECT SUM(base_amount) FROM AP_INVOICE_LINES_ALL WHERE invoice_id =pn_invoice_id )
3367 WHERE invoice_id =pn_invoice_id;
3368 */
3369
3370 --log for debug
3371 IF ( ln_proc_level >= ln_dbg_level)
3372 THEN
3373 FND_LOG.STRING ( ln_proc_level
3374 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
3375 , 'Exit procedure'
3376 );
3377 END IF; --( ln_proc_level >= ln_dbg_level )
3378 EXCEPTION
3379 WHEN OTHERS THEN
3380 IF ( ln_proc_level >= ln_dbg_level)
3381 THEN
3382 FND_LOG.STRING ( ln_proc_level
3383 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
3384 || '. Other_Exception '
3385 , SQLCODE || ':' || SQLERRM
3386 );
3387 END IF; --( ln_proc_level >= ln_dbg_level) ;
3388
3389 ROLLBACK;
3390 END Populate_Stnd_Inv_Taxes;
3391
3392 --==========================================================================
3393 -- PROCEDURE NAME:
3394 --
3395 -- Default_Calculate_Taxes Public
3396 --
3397 -- DESCRIPTION:
3398 --
3399 -- This procedure is to invoke standard procedure to insert item information
3400 -- into jai_cmn_document_taxes
3401 --
3402 -- PARAMETERS:
3403 -- In: pn_invoice_id IN NUMBER invoice id
3404 -- pn_line_number IN NUMBER item line number
3405 -- xn_tax_amount IN OUT NUMBER tax ou
3406 -- pv_currency_code IN VARCHAR2 currency code
3407 -- pn_tax_category_id IN NUMBER tax category
3408 -- pv_tax_modified IN VARCHAR2 tax modified flag
3409 -- pn_supplier_site_id in NUMBER supplier site id
3410 -- pn_supplier_id in NUMBER suppolier id
3411 --
3412 --
3413 -- Out:
3414 --
3415 --
3416 -- DESIGN REFERENCES:
3417 -- AP Technical Design 2.1.doc
3418 --
3419 -- CHANGE HISTORY:
3420 --
3421 -- 1 23-Aug-2007 Eric Ma Created
3422 --
3423 --===========================================================================
3424
3425 PROCEDURE Default_Calculate_Taxes
3426 ( pn_invoice_id IN NUMBER
3427 , pn_line_number IN NUMBER
3428 , xn_tax_amount IN OUT NOCOPY NUMBER
3429 , pn_vendor_id IN NUMBER
3430 , pn_vendor_site_id IN NUMBER
3431 , pv_currency_code IN VARCHAR2
3432 , pn_tax_category_id IN NUMBER
3433 , pv_tax_modified IN VARCHAR2
3434 )
3435 IS
3436 ln_invoice_id NUMBER := pn_invoice_id;
3437 ln_line_number NUMBER := pn_line_number;
3438 lv_currency_code VARCHAR2 (15) := pv_currency_code;
3439 ln_tax_category_id NUMBER := pn_tax_category_id;
3440 ln_vendor_id NUMBER := pn_vendor_id;
3441 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
3442 ln_proc_level NUMBER := FND_LOG.level_procedure;
3443 lv_proc_name VARCHAR2 (100) := 'Default_Calculate_Taxes';
3444 ln_user_id NUMBER := fnd_global.user_id;
3445 ln_login_id NUMBER := fnd_global.login_id;
3446 BEGIN
3447 --log for debug
3448 IF ( ln_proc_level >= ln_dbg_level)
3449 THEN
3450 FND_LOG.STRING ( ln_proc_level
3451 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
3452 , 'Enter procedure'
3453 );
3454 FND_LOG.STRING ( ln_proc_level
3455 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3456 , 'pn_invoice_id ' || pn_invoice_id
3457 );
3458 FND_LOG.STRING ( ln_proc_level
3459 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3460 , 'pn_line_number ' || pn_line_number
3461 );
3462 FND_LOG.STRING ( ln_proc_level
3463 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3464 , 'pv_currency_code ' || pv_currency_code
3465 );
3466 FND_LOG.STRING ( ln_proc_level
3467 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3468 , 'pn_tax_category_id ' || pn_tax_category_id
3469 );
3470 FND_LOG.STRING ( ln_proc_level
3471 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3472 , 'pv_tax_modified ' || pv_tax_modified
3473 );
3474 FND_LOG.STRING ( ln_proc_level
3475 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3476 , 'pn_vendor_id ' || pn_vendor_id
3477 );
3478 FND_LOG.STRING ( ln_proc_level
3479 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3480 , 'pn_vendor_site_id ' || pn_vendor_site_id
3481 );
3482 END IF; --( ln_proc_level >= ln_dbg_level )
3483
3484 jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes
3485 ( transaction_name => jai_constants.g_ap_standalone_invoice
3486 , p_tax_category_id => ln_tax_category_id
3487 , p_header_id => ln_invoice_id
3488 , p_line_id => ln_line_number
3489 --, p_assessable_value => 0 modified by eric on Jan 25th,2008
3490 , p_assessable_value => xn_tax_amount --modified by eric ,replace 0 with line amount
3491 , p_tax_amount => xn_tax_amount
3492 , p_inventory_item_id => NULL
3493 , p_line_quantity => 1
3494 , p_uom_code => NULL
3495 , p_vendor_id => ln_vendor_id
3496 , p_currency => lv_currency_code
3497 , p_currency_conv_factor => NULL
3498 , p_creation_date => SYSDATE
3499 , p_created_by => ln_user_id
3500 , p_last_update_date => SYSDATE
3501 , p_last_updated_by => ln_user_id
3502 , p_last_update_login => ln_login_id
3503 , p_operation_flag => NULL
3504 --, p_vat_assessable_value => 0
3505 , p_vat_assessable_value => xn_tax_amount --modified by eric ,replace 0 with line amount
3506 , p_source_trx_type => jai_constants.G_AP_STANDALONE_INVOICE
3507 , p_source_table_name => GV_JAI_AP_INVOICE_LINES --'JAI_AP_INVOICE_LINES'
3508 , p_action => jai_constants.default_taxes
3509 );
3510
3511 --log for debug
3512 IF ( ln_proc_level >= ln_dbg_level)
3513 THEN
3514 FND_LOG.STRING ( ln_proc_level
3515 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
3516 , 'Exit procedure'
3517 );
3518 END IF; --( ln_proc_level >= ln_dbg_level )
3519 EXCEPTION
3520 WHEN OTHERS THEN
3521 IF ( ln_proc_level >= ln_dbg_level)
3522 THEN
3523 FND_LOG.STRING ( ln_proc_level
3524 , GV_MODULE_PREFIX || '.' || lv_proc_name
3525 || '. Other_Exception '
3526 , SQLCODE || ':' || SQLERRM
3527 );
3528 END IF; --( ln_proc_level >= ln_dbg_level) ;
3529 END Default_Calculate_Taxes;
3530
3531 --==========================================================================
3532 -- FUNCTION NAME:
3533 --
3534 -- Check_Inclusive_Tax Private
3535 --
3536 -- DESCRIPTION:
3537 --
3538 -- This procedure is to check wether the input tax is inclusive tax or
3539 -- exclusive tax. If return is false,then the tax is exclusive tax.
3540 -- Otherwise ,it's a inclusive tax
3541 --
3542 -- PARAMETERS:
3543 -- In: pn_tax_id NUMBER tax id
3544 --
3545 --
3546 -- Out:
3547 -- return Boolean TURE /FALSE
3548 --
3549 -- DESIGN REFERENCES:
3550 -- AP Inclusive TD
3551 --
3552 -- CHANGE HISTORY:
3553 --
3554 -- 1 12-Dec-2007 Eric Ma Created
3555 --===========================================================================
3556 FUNCTION Check_Inclusive_Tax (pn_tax_id NUMBER)
3557 RETURN BOOLEAN
3558 IS
3559 CURSOR get_inclusive_tax_flag IS
3560 SELECT NVL(inclusive_tax_flag,'N')
3561 FROM
3562 jai_cmn_taxes_all
3563 WHERE
3564 tax_id = pn_tax_id;
3565
3566 lv_inclusive_tax_flag VARCHAR2(1);
3567 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
3568 ln_proc_level NUMBER := FND_LOG.level_procedure;
3569 lv_proc_name VARCHAR2 (100) := 'Check_Inclusive_Tax';
3570 BEGIN
3571 OPEN get_inclusive_tax_flag;
3572 FETCH get_inclusive_tax_flag
3573 INTO
3574 lv_inclusive_tax_flag;
3575 CLOSE get_inclusive_tax_flag;
3576
3577 --log for debug
3578 IF ( ln_proc_level >= ln_dbg_level)
3579 THEN
3580 FND_LOG.STRING ( ln_proc_level
3581 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
3582 , 'Enter procedure'
3583 );
3584 FND_LOG.STRING ( ln_proc_level
3585 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3586 , 'pn_tax_id ' || pn_tax_id
3587 );
3588 END IF;
3589 --log for debug
3590 IF ( ln_proc_level >= ln_dbg_level)
3591 THEN
3592 FND_LOG.STRING ( ln_proc_level
3593 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
3594 , 'Exit FUNCTION'
3595 );
3596 END IF; --( ln_proc_level >= ln_dbg_level )
3597
3598 IF (lv_inclusive_tax_flag='Y')
3599 THEN
3600 RETURN TRUE;
3601 ELSE
3602 RETURN FALSE;
3603 END IF; --(lv_inclusive_tax_flag='N')
3604
3605 EXCEPTION
3606 WHEN OTHERS THEN
3607 IF ( ln_proc_level >= ln_dbg_level)
3608 THEN
3609 FND_LOG.STRING ( ln_proc_level
3610 , GV_MODULE_PREFIX || '.' || lv_proc_name
3611 || '. Other_Exception '
3612 , SQLCODE || ':' || SQLERRM
3613 );
3614 END IF; --( ln_proc_level >= ln_dbg_level) ;
3615 END Check_Inclusive_Tax;
3616
3617 --==========================================================================
3618 -- PROCEDURE NAME:
3619 --
3620 -- Get_Pr_Processed_Flag Private
3621 --
3622 -- DESCRIPTION:
3623 --
3624 -- If we splited pr tax into two 2 portions, then the recoverable
3625 -- portion shold not be splited again. The splited PR tax has the
3626 -- following features: tax_id and tax_line_no are same.
3627 --
3628 -- PARAMETERS:
3629 -- In: pn_source_doc_id NUMBER Invoice id
3630 -- pn_source_parent_line_no NUMBER Invoice item line no
3631 --
3632 -- Out:
3633 --
3634 -- DESIGN REFERENCES:
3635 -- AP Technical Design 2.1.doc
3636 --
3637 -- CHANGE HISTORY:
3638 -- 1 29-Jan-2008 Eric Ma created for bug#6784111
3639 --===========================================================================
3640 FUNCTION Get_Pr_Processed_Flag
3641 ( pn_source_doc_id IN NUMBER
3642 , pn_source_parent_line_no IN NUMBER
3643 , pn_tax_id IN NUMBER
3644 )
3645 RETURN VARCHAR2
3646 IS
3647 ln_count NUMBER;
3648 lv_pr_processed_flag VARCHAR2 (1) DEFAULT NULL;
3649
3650 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
3651 ln_proc_level NUMBER := FND_LOG.level_procedure;
3652 lv_proc_name VARCHAR2 (100) := 'Get_Pr_Processed_Flag';
3653 BEGIN
3654 --log for debug
3655 IF ( ln_proc_level >= ln_dbg_level)
3656 THEN
3657 FND_LOG.STRING ( ln_proc_level
3658 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
3659 , 'Enter procedure'
3660 );
3661 FND_LOG.STRING ( ln_proc_level
3662 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3663 , 'pn_source_doc_id ' || pn_source_doc_id
3664 );
3665 FND_LOG.STRING ( ln_proc_level
3666 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3667 , 'pn_source_parent_line_no ' || pn_source_parent_line_no
3668 );
3669 FND_LOG.STRING ( ln_proc_level
3670 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3671 , 'pn_tax_id ' || pn_tax_id
3672 );
3673 END IF;--( ln_proc_level >= ln_dbg_level)
3674 --log for debug
3675
3676 SELECT
3677 COUNT(tax_id)
3678 INTO
3679 ln_count
3680 FROM
3681 jai_cmn_document_taxes
3682 WHERE source_doc_id = pn_source_doc_id
3683 AND source_DOC_parent_line_no = pn_source_parent_line_no
3684 AND tax_id = pn_tax_id
3685 AND source_doc_type = jai_constants.g_ap_standalone_invoice;
3686
3687 IF (ln_count >1)
3688 THEN
3689 lv_pr_processed_flag := jai_constants.yes ;
3690 ELSE
3691 lv_pr_processed_flag := jai_constants.no ;
3692 END IF;
3693
3694 IF ( ln_proc_level >= ln_dbg_level)
3695 THEN
3696 FND_LOG.STRING ( ln_proc_level
3697 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3698 , 'lv_pr_processed_flag ' || lv_pr_processed_flag
3699 );
3700 FND_LOG.STRING ( ln_proc_level
3701 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
3702 , 'Exit FUNCTION'
3703 );
3704 END IF; --( ln_proc_level >= ln_dbg_level )
3705
3706 RETURN lv_pr_processed_flag;
3707 EXCEPTION
3708 WHEN OTHERS THEN
3709 IF ( ln_proc_level >= ln_dbg_level)
3710 THEN
3711 FND_LOG.STRING ( ln_proc_level
3712 , GV_MODULE_PREFIX || '.' || lv_proc_name
3713 || '. Other_Exception '
3714 , SQLCODE || ':' || SQLERRM
3715 );
3716 END IF; --( ln_proc_level >= ln_dbg_level) ;
3717 END Get_Pr_Processed_Flag;
3718 --==========================================================================
3719 -- PROCEDURE NAME:
3720 --
3721 -- Create_Tax_Lines Public
3722 --
3723 -- DESCRIPTION:
3724 --
3725 -- This procedure is to create tax invoice line and distribution line in
3726 -- both standard tables of ap module and jai ap modules
3727 --
3728 -- This procedures will fetch all the related information which we need to
3729 -- populate the base tables and IlL tables. Maintly to get the project
3730 -- realted information for the Project invoices, asset related info for
3731 -- theasset realted invoices and the various cahrge accounts for the
3732 -- distributions
3733 --
3734 -- PARAMETERS:
3735 -- In: pn_organization_id NUMBER organization id
3736 -- pv_currency VARCHAR2 currency
3737 -- pn_location_id NUMBER location id
3738 -- pn_invoice_id NUMBER invoice id
3739 -- pn_line_number NUMBER item line number
3740 -- p_action VARCHAR2 normally it is DEFAULT_TAXES, it can
3741 -- be jai_constants.recalculate_taxes
3742 -- pn_tax_category_id NUMBER tax category id
3743 --
3744 --
3745 -- Out:
3746 --
3747 --
3748 -- DESIGN REFERENCES:
3749 -- AP Technical Design 2.1.doc
3750 --
3751 -- CHANGE HISTORY:
3752 --
3753 -- 1 23-Aug-2007 Eric Ma Created
3754 -- 2 30-Nov-2007 Eric Ma modified for inclusive tax
3755 -- 3 18-Feb-2008 Eric Ma modified for bug#6824857
3756 -- 4 21-Nov-2008 Walton modified for bug#7202316
3757 --===========================================================================
3758
3759 PROCEDURE Create_Tax_Lines
3760 ( pn_organization_id IN NUMBER
3761 , pv_currency IN VARCHAR2
3762 , pn_location_id IN NUMBER
3763 , pn_invoice_id IN NUMBER
3764 , pn_line_number IN NUMBER DEFAULT NULL
3765 , pv_action IN VARCHAR2 DEFAULT jai_constants.default_taxes
3766 , pn_tax_category_id IN NUMBER
3767 , pv_tax_modified IN VARCHAR2
3768 )
3769 IS
3770 ln_invoice_id NUMBER := pn_invoice_id;
3771 ln_line_number NUMBER := pn_line_number;
3772 ln_tax_category_id NUMBER := pn_tax_category_id;
3773 lv_tax_modified VARCHAR2 (1) := pv_tax_modified;
3774
3775 CURSOR ap_invoice_lines_cur IS
3776 SELECT
3777 invoice_id
3778 , line_number
3779 , line_type_lookup_code
3780 , description
3781 , org_id
3782 , assets_tracking_flag
3783 , match_type
3784 , accounting_date
3785 , period_name
3786 , deferred_acctg_flag
3787 , def_acctg_start_date
3788 , def_acctg_end_date
3789 , def_acctg_number_of_periods
3790 , def_acctg_period_type
3791 , set_of_books_id
3792 , amount
3793 , wfapproval_status
3794 , creation_date
3795 , created_by
3796 , last_updated_by
3797 , last_update_date
3798 , last_update_login
3799 , project_id
3800 , task_id
3801 , expenditure_type
3802 , expenditure_item_date
3803 , expenditure_organization_id
3804 FROM
3805 ap_invoice_lines_all
3806 WHERE invoice_id = ln_invoice_id
3807 AND line_type_lookup_code = GV_CONSTANT_ITEM
3808 AND match_type = GV_NOT_MATCH_TYPE
3809 AND line_number = NVL (ln_line_number, line_number);
3810
3811 CURSOR jai_doc_taxes_cur
3812 ( pn_invoice_id NUMBER
3813 , pn_parent_line_number NUMBER
3814 )
3815 IS
3816 SELECT
3817 jcdt.doc_tax_id
3818 , jcdt.tax_line_no
3819 , jcdt.tax_id
3820 , jcdt.tax_type
3821 , jcdt.currency_code
3822 , jcdt.tax_rate
3823 , jcdt.qty_rate
3824 , jcdt.uom
3825 , jcdt.tax_amt
3826 , jcdt.func_tax_amt
3827 , jcdt.modvat_flag
3828 , jcdt.tax_category_id
3829 , jcdt.source_doc_type
3830 , jcdt.source_doc_id
3831 , jcdt.source_doc_line_id
3832 , jcdt.source_table_name
3833 , jcdt.tax_modified_by
3834 , jcdt.adhoc_flag
3835 , jcdt.precedence_1
3836 , jcdt.precedence_2
3837 , jcdt.precedence_3
3838 , jcdt.precedence_4
3839 , jcdt.precedence_5
3840 , jcdt.precedence_6
3841 , jcdt.precedence_7
3842 , jcdt.precedence_8
3843 , jcdt.precedence_9
3844 , jcdt.precedence_10
3845 , jcdt.creation_date
3846 , jcdt.created_by
3847 , jcdt.last_update_date
3848 , jcdt.last_updated_by
3849 , jcdt.last_update_login
3850 , jcdt.object_version_number
3851 , jcdt.vendor_id
3852 , jcdt.source_doc_parent_line_no
3853 , jcta.inclusive_tax_flag inc_tax_flag --Added by Eric for Inclusive Tax
3854 FROM
3855 jai_cmn_document_taxes jcdt
3856 , jai_cmn_taxes_all jcta --Added by Eric for Inclusive Tax
3857 WHERE jcdt.source_doc_id = pn_invoice_id
3858 AND jcdt.source_doc_parent_line_no = pn_parent_line_number
3859 AND jcdt.tax_id = jcta.tax_id --Added by Eric for Inclusive Tax
3860 AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
3861 ORDER BY jcdt.doc_tax_id;
3862
3863 CURSOR jai_default_doc_taxes_cur
3864 ( pn_invoice_id NUMBER
3865 , pn_line_number NUMBER
3866 )
3867 IS
3868 SELECT
3869 jcdt.doc_tax_id
3870 , jcdt.tax_line_no
3871 , jcdt.tax_id
3872 , jcdt.tax_type
3873 , jcdt.currency_code
3874 , jcdt.tax_rate
3875 , jcdt.qty_rate
3876 , jcdt.uom
3877 , jcdt.tax_amt
3878 , jcdt.func_tax_amt
3879 , jcdt.modvat_flag
3880 , jcdt.tax_category_id
3881 , jcdt.source_doc_type
3882 , jcdt.source_doc_id
3883 , jcdt.source_doc_line_id
3884 , jcdt.source_table_name
3885 , jcdt.tax_modified_by
3886 , jcdt.adhoc_flag
3887 , jcdt.precedence_1
3888 , jcdt.precedence_2
3889 , jcdt.precedence_3
3890 , jcdt.precedence_4
3891 , jcdt.precedence_5
3892 , jcdt.precedence_6
3893 , jcdt.precedence_7
3894 , jcdt.precedence_8
3895 , jcdt.precedence_9
3896 , jcdt.precedence_10
3897 , jcdt.creation_date
3898 , jcdt.created_by
3899 , jcdt.last_update_date
3900 , jcdt.last_updated_by
3901 , jcdt.last_update_login
3902 , jcdt.object_version_number
3903 , jcdt.vendor_id
3904 , jcdt.source_doc_parent_line_no
3905 FROM
3906 jai_cmn_document_taxes jcdt
3907 WHERE jcdt.source_doc_id = pn_invoice_id
3908 AND jcdt.source_doc_line_id = pn_line_number
3909 AND jcdt.source_doc_parent_line_no = pn_line_number
3910 AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
3911 ORDER BY jcdt.tax_line_no FOR UPDATE;
3912
3913 CURSOR get_tax_cur (pn_tax_id NUMBER) IS
3914 SELECT
3915 tax_name
3916 , tax_account_id
3917 , mod_cr_percentage
3918 , adhoc_flag
3919 , NVL (tax_rate, -1) tax_rate
3920 , tax_type
3921 , NVL(rounding_factor,0) rounding_factor
3922 FROM
3923 jai_cmn_taxes_all
3924 WHERE tax_id = pn_tax_id;
3925
3926 CURSOR ap_invoice_dist_cur (pn_line_number NUMBER) IS
3927 SELECT
3928 accounting_date
3929 , accrual_posted_flag
3930 , assets_addition_flag
3931 , assets_tracking_flag
3932 , cash_posted_flag
3933 , distribution_line_number
3934 , dist_code_combination_id
3935 , invoice_id
3936 , last_updated_by
3937 , last_update_date
3938 , line_type_lookup_code
3939 , period_name
3940 , set_of_books_id
3941 , amount
3942 , base_amount
3943 , batch_id
3944 , created_by
3945 , creation_date
3946 , description
3947 , exchange_rate
3948 , exchange_rate_variance
3949 , last_update_login
3950 , match_status_flag
3951 , posted_flag
3952 , rate_var_code_combination_id
3953 , reversal_flag
3954 , program_application_id
3955 , program_id
3956 , program_update_date
3957 , accts_pay_code_combination_id
3958 , invoice_distribution_id
3959 , quantity_invoiced
3960 , po_distribution_id
3961 , rcv_transaction_id
3962 , price_var_code_combination_id
3963 , invoice_price_variance
3964 , base_invoice_price_variance
3965 , matched_uom_lookup_code
3966 , invoice_line_number
3967 , org_id
3968 , charge_applicable_to_dist_id
3969 , project_id
3970 , task_id
3971 , expenditure_type
3972 , expenditure_item_date
3973 , expenditure_organization_id
3974 , project_accounting_context
3975 , pa_addition_flag
3976 , distribution_class
3977 , ASSET_BOOK_TYPE_CODE
3978 , ASSET_CATEGORY_ID
3979 FROM
3980 ap_invoice_distributions_all
3981 WHERE invoice_id = ln_invoice_id
3982 AND invoice_line_number = pn_line_number
3983 AND distribution_line_number = 1;
3984
3985 CURSOR new_invoice_lines_cur (pn_inovoice_line_num IN NUMBER) IS
3986 SELECT
3987 invoice_id
3988 , line_number
3989 , line_type_lookup_code
3990 , description
3991 , org_id
3992 , assets_tracking_flag
3993 , match_type
3994 , accounting_date
3995 , period_name
3996 , deferred_acctg_flag
3997 , def_acctg_start_date
3998 , def_acctg_end_date
3999 , def_acctg_number_of_periods
4000 , def_acctg_period_type
4001 , set_of_books_id
4002 , amount
4003 , wfapproval_status
4004 , creation_date
4005 , created_by
4006 , last_updated_by
4007 , last_update_date
4008 , last_update_login
4009 , project_id
4010 , task_id
4011 , expenditure_type
4012 , expenditure_item_date
4013 , expenditure_organization_id
4014 FROM
4015 ap_invoice_lines_all a
4016 WHERE invoice_id = ln_invoice_id
4017 AND line_number = pn_inovoice_line_num
4018 AND NOT EXISTS
4019 (
4020 SELECT
4021 'X'
4022 FROM
4023 jai_ap_invoice_lines b
4024 WHERE a.invoice_id = b.invoice_id
4025 AND a.line_number = b.invoice_line_number
4026 );
4027
4028 ap_invoice_dist_rec ap_invoice_dist_cur%ROWTYPE;
4029 tax_rec get_tax_cur%ROWTYPE;
4030 lv_account_type gl_code_combinations.account_type%TYPE;
4031 ln_inv_dist_id NUMBER;
4032 ln_dist_acct_ccid NUMBER;
4033 ln_tax_amount NUMBER;
4034 ln_vendor_id NUMBER;
4035 ln_vendor_site_id NUMBER;
4036 lv_currency_code VARCHAR2 (15);
4037 ln_exchange_rate NUMBER;
4038 lv_service_type_code VARCHAR2 (30);
4039 lv_service_type_code_tmp VARCHAR2 (30); --added by walton for bug#7202316
4040 ln_batch_id NUMBER;
4041 ln_max_inv_line_num NUMBER;
4042 ln_source_doc_line_id NUMBER; --added by eric for inclusive tax
4043 ln_max_tax_line_num NUMBER;
4044 ln_max_pro_line_num NUMBER; --Added by Jia Li for inclusive tax on 2008/01/23
4045 ln_recur_tax_amt NUMBER;
4046 ln_nrecur_tax_amt NUMBER;
4047 ln_func_tax_amount NUMBER;
4048 ln_recur_func_tax_amt NUMBER;
4049 ln_nrecur_func_tax_amt NUMBER;
4050 lv_tax_type VARCHAR2(10);
4051 ln_doc_tax_id NUMBER;
4052 ln_chargeble_acct_ccid NUMBER;
4053 lv_tax_recoverable_flag VARCHAR2(1);
4054 lv_insert_jai_inv_sql VARCHAR2(32000);
4055 lv_insert_jai_tax_sql VARCHAR2(32000);
4056 lv_insert_ap_inv_ln_sql VARCHAR2(32000);
4057 lv_insert_ap_inv_dist_ln_sql VARCHAR2(32000);
4058 lv_pr_processed_flag VARCHAR2(1); --added by eric on jan 29,2008
4059 ln_max_source_line_id NUMBER; --added by eric on jan 29,2008
4060 ln_asset_track_flag ap_invoice_lines_all.assets_tracking_flag%TYPE;
4061 ln_project_id ap_invoice_lines_all.project_id%TYPE;
4062 ln_task_id ap_invoice_lines_all.task_id%TYPE;
4063 lv_expenditure_type ap_invoice_lines_all.expenditure_type%TYPE;
4064 ld_exp_item_date ap_invoice_lines_all.expenditure_item_date%TYPE;
4065 ln_exp_org_id ap_invoice_lines_all.expenditure_organization_id%TYPE;
4066 ld_sys_date DATE; --Eric added on 18-Feb-2008,for bug#6824857
4067
4068 ln_dist_asst_add_flag
4069 ap_invoice_distributions_all.assets_addition_flag%TYPE;
4070 ln_dist_asst_trck_flag
4071 ap_invoice_distributions_all.assets_tracking_flag%TYPE;
4072 ln_dist_project_id
4073 ap_invoice_distributions_all.project_id%TYPE;
4074 ln_dist_task_id
4075 ap_invoice_distributions_all.task_id%TYPE;
4076 ln_dist_exp_type
4077 ap_invoice_distributions_all.expenditure_type%TYPE;
4078 ld_dist_exp_item_date
4079 ap_invoice_distributions_all.expenditure_item_date%TYPE;
4080 ln_dist_exp_org_id
4081 ap_invoice_distributions_all.expenditure_organization_id%TYPE;
4082 ln_dist_pa_context
4083 ap_invoice_distributions_all.project_accounting_context%TYPE;
4084 ln_dist_pa_addition_flag
4085 ap_invoice_distributions_all.pa_addition_flag%TYPE;
4086 lv_asset_book_type_code
4087 ap_invoice_distributions_all.asset_book_type_code%TYPE;
4088 ln_asset_category_id
4089 ap_invoice_distributions_all.asset_category_id%TYPE;
4090 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
4091 ln_proc_level NUMBER := FND_LOG.level_procedure;
4092 lv_proc_name VARCHAR2 (100):= 'Create_Tax_Lines';
4093 ln_user_id NUMBER := fnd_global.user_id;
4094 ln_login_id NUMBER := fnd_global.login_id;
4095
4096 lb_inclusive_tax boolean; --added by eric for inclusive tax
4097 ln_jai_inv_line_id NUMBER;
4098 BEGIN
4099
4100 lv_insert_jai_inv_sql:=
4101 'INSERT INTO jai_ap_invoice_lines
4102 ( jai_ap_invoice_lines_id
4103 , organization_id
4104 , location_id
4105 , invoice_id
4106 , invoice_line_number
4107 , supplier_site_id
4108 , parent_invoice_line_number
4109 , tax_category_id
4110 , service_type_code
4111 , match_type
4112 , currency_code
4113 , line_amount
4114 , line_type_lookup_code
4115 , created_by
4116 , creation_date
4117 , last_update_date
4118 , last_update_login
4119 , last_updated_by
4120 )
4121 VALUES
4122 ( :1
4123 , :2
4124 , :3
4125 , :4
4126 , :5
4127 , :6
4128 , :7
4129 , :8
4130 , :9
4131 , :10
4132 , :11
4133 , :12
4134 , :13
4135 , :14
4136 , :15
4137 , :16
4138 , :17
4139 , :18
4140 )';
4141
4142 lv_insert_jai_tax_sql :=
4143 'INSERT INTO jai_cmn_document_taxes
4144 ( doc_tax_id
4145 , tax_line_no
4146 , tax_id
4147 , tax_type
4148 , currency_code
4149 , tax_rate
4150 , qty_rate
4151 , uom
4152 , tax_amt
4153 , func_tax_amt
4154 , modvat_flag
4155 , tax_category_id
4156 , source_doc_type
4157 , source_doc_id
4158 , source_doc_line_id
4159 , source_table_name
4160 , tax_modified_by
4161 , adhoc_flag
4162 , precedence_1
4163 , precedence_2
4164 , precedence_3
4165 , precedence_4
4166 , precedence_5
4167 , precedence_6
4168 , precedence_7
4169 , precedence_8
4170 , precedence_9
4171 , precedence_10
4172 , creation_date
4173 , created_by
4174 , last_update_date
4175 , last_updated_by
4176 , last_update_login
4177 , object_version_number
4178 , vendor_id
4179 , source_doc_parent_line_no
4180 )
4181 VALUES
4182 ( :1
4183 , :2
4184 , :3
4185 , :4
4186 , :5
4187 , :6
4188 , :7
4189 , :8
4190 , :9
4191 , :10
4192 , :11
4193 , :12
4194 , :13
4195 , :14
4196 , :15
4197 , :16
4198 , :17
4199 , :18
4200 , :19
4201 , :20
4202 , :21
4203 , :22
4204 , :23
4205 , :24
4206 , :25
4207 , :26
4208 , :27
4209 , :28
4210 , :29
4211 , :30
4212 , :31
4213 , :32
4214 , :33
4215 , :34
4216 , :35
4217 , :36
4218 )';
4219
4220 lv_insert_ap_inv_ln_sql :=
4221 'INSERT INTO ap_invoice_lines_all
4222 ( invoice_id
4223 , line_number
4224 , line_type_lookup_code
4225 , description
4226 , org_id
4227 , assets_tracking_flag
4228 , match_type
4229 , accounting_date
4230 , period_name
4231 , deferred_acctg_flag
4232 , def_acctg_start_date
4233 , def_acctg_end_date
4234 , def_acctg_number_of_periods
4235 , def_acctg_period_type
4236 , set_of_books_id
4237 , amount
4238 , wfapproval_status
4239 , creation_date
4240 , created_by
4241 , last_updated_by
4242 , last_update_date
4243 , last_update_login
4244 , project_id
4245 , task_id
4246 , expenditure_type
4247 , expenditure_item_date
4248 , expenditure_organization_id
4249 )
4250 VALUES
4251 ( :1
4252 , :2
4253 , :3
4254 , :4
4255 , :5
4256 , :6
4257 , :7
4258 , :8
4259 , :9
4260 , :10
4261 , :11
4262 , :12
4263 , :13
4264 , :14
4265 , :15
4266 , :16
4267 , :17
4268 , :18
4269 , :19
4270 , :20
4271 , :21
4272 , :22
4273 , :23
4274 , :24
4275 , :25
4276 , :26
4277 , :27
4278 )';
4279
4280 lv_insert_ap_inv_dist_ln_sql :=
4281 'INSERT INTO ap_invoice_distributions_all
4282 ( accounting_date
4283 , accrual_posted_flag
4284 , assets_addition_flag
4285 , assets_tracking_flag
4286 , cash_posted_flag
4287 , distribution_line_number
4288 , dist_code_combination_id
4289 , invoice_id
4290 , last_updated_by
4291 , last_update_date
4292 , line_type_lookup_code
4293 , period_name
4294 , set_of_books_id
4295 , amount
4296 --, base_amount deleted by eric on 2008-Jan-08, as po_matched case not populate the column
4297 , batch_id
4298 , created_by
4299 , creation_date
4300 , description
4301 , exchange_rate_variance
4302 , last_update_login
4303 , match_status_flag
4304 , posted_flag
4305 , rate_var_code_combination_id
4306 , reversal_flag
4307 , program_application_id
4308 , program_id
4309 , program_update_date
4310 , accts_pay_code_combination_id
4311 , invoice_distribution_id
4312 , quantity_invoiced
4313 , po_distribution_id
4314 , rcv_transaction_id
4315 , price_var_code_combination_id
4316 , invoice_price_variance
4317 , base_invoice_price_variance
4318 , matched_uom_lookup_code
4319 , invoice_line_number
4320 , org_id
4321 , charge_applicable_to_dist_id
4322 , project_id
4323 , task_id
4324 , expenditure_type
4325 , expenditure_item_date
4326 , expenditure_organization_id
4327 , project_accounting_context
4328 , pa_addition_flag
4329 , DISTRIBUTION_CLASS
4330 , TAX_RECOVERABLE_FLAG
4331 )
4332 VALUES
4333 ( :1
4334 , :2
4335 , :3
4336 , :4
4337 , :5
4338 , :6
4339 , :7
4340 , :8
4341 , :9
4342 , :10
4343 , :11
4344 , :12
4345 , :13
4346 , :14
4347 , :15
4348 , :16
4349 , :17
4350 , :18
4351 , :19
4352 , :20
4353 , :21
4354 , :22
4355 , :23
4356 , :24
4357 , :25
4358 , :26
4359 , :27
4360 , :28
4361 , :29
4362 , :30
4363 , :31
4364 , :32
4365 , :33
4366 , :34
4367 , :35
4368 , :36
4369 , :37
4370 , :38
4371 , :39
4372 , :40
4373 , :41
4374 , :42
4375 , :43
4376 , :44
4377 , :45
4378 , :46
4379 , :47
4380 , :48
4381 )';
4382
4383 --log for debug
4384 IF ( ln_proc_level >= ln_dbg_level)
4385 THEN
4386 FND_LOG.STRING ( ln_proc_level
4387 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
4388 , 'Enter procedure'
4389 );
4390 FND_LOG.STRING ( ln_proc_level
4391 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4392 , 'pn_invoice_id ' || pn_invoice_id
4393 );
4394 FND_LOG.STRING ( ln_proc_level
4395 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4396 , 'pn_line_number ' || pn_line_number
4397 );
4398 FND_LOG.STRING ( ln_proc_level
4399 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4400 , 'pv_tax_modified ' || pv_tax_modified
4401 );
4402 FND_LOG.STRING ( ln_proc_level
4403 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4404 , 'pn_organization_id ' || pn_organization_id
4405 );
4406 FND_LOG.STRING ( ln_proc_level
4407 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4408 , 'pn_location_id ' || pn_location_id
4409 );
4410 FND_LOG.STRING ( ln_proc_level
4411 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4412 , 'pv_currency ' || pv_currency
4413 );
4414 FND_LOG.STRING ( ln_proc_level
4415 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4416 , 'pn_tax_category_id ' || pn_tax_category_id
4417 );
4418 END IF; --( ln_proc_level >= ln_dbg_level )
4419
4420 --insert into eric_log values (7.0,'Procedure Create_Tax_Lines',sysdate);
4421 --get invoice header information
4422 Get_Invoice_Header_Infor ( ln_invoice_id
4423 , ln_vendor_id
4424 , ln_vendor_site_id
4425 , lv_currency_code
4426 , ln_exchange_rate
4427 , ln_batch_id
4428 );
4429
4430 --When updating tax category from IL form,set the tax_category_id
4431 --as the input parementer
4432
4433 --If the program invoked from Standard AP invoice workbench,
4434 --Get the tax_category_id from configration of vendor-vndr site combination
4435 IF ( pn_tax_category_id is null
4436 AND pn_line_number is null ) --invoked form standard ap form
4437 THEN
4438 Get_Tax_Cat_Serv_Type ( pn_vendor_id => ln_vendor_id
4439 , pn_vendor_site_id => ln_vendor_site_id
4440 , xn_tax_category_id => ln_tax_category_id
4441 , xv_service_type_code => lv_service_type_code
4442 );
4443 ELSIF (pn_line_number is not null) --invoked form IL form or Case 6
4444 THEN
4445 Get_Tax_Cat_Serv_Type ( pn_vendor_id => ln_vendor_id
4446 , pn_vendor_site_id => ln_vendor_site_id
4447 , xn_tax_category_id => ln_tax_category_id
4448 , xv_service_type_code => lv_service_type_code_tmp
4449 ); --added by walton for bug#7202316
4450 ln_tax_category_id := pn_tax_category_id;
4451 --added by walton for bug#7202316
4452 lv_service_type_code:=nvl(lv_service_type_code,lv_service_type_code_tmp);
4453 END IF;
4454
4455 --Get Max Line number
4456 ln_max_inv_line_num := Get_Max_Invoice_Line_Number (ln_invoice_id);
4457
4458 --log for debug
4459 IF ( ln_proc_level >= ln_dbg_level)
4460 THEN
4461 FND_LOG.STRING ( ln_proc_level
4462 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.debug Info.'
4463 , 'Item Loop of First Time '
4464 );
4465 END IF; --( ln_proc_level >= ln_dbg_level )
4466
4467 --Loop all item lines in the ap_invoice_lines
4468 FOR ap_invoice_lines_rec IN ap_invoice_lines_cur
4469 LOOP
4470 --insert into eric_log values (7.1,'Go into Create_Tax_Lines.ap_invoice_lines_cur',sysdate);
4471 --insert into eric_log values (7.2,'ap_invoice_lines_rec.invoice_id :'|| ap_invoice_lines_rec.invoice_id,sysdate);
4472 --insert into eric_log values (7.3,'ap_invoice_lines_rec.line_number :'||ap_invoice_lines_rec.line_number,sysdate);
4473
4474 ln_tax_amount := ap_invoice_lines_rec.amount;
4475
4476 -- inserts taxes into jai_cmn_document_taxes
4477 IF NVL (pv_tax_modified, 'N') = 'N'
4478 THEN
4479 Default_Calculate_Taxes
4480 ( pn_invoice_id => ap_invoice_lines_rec.invoice_id
4481 , pn_line_number => ap_invoice_lines_rec.line_number
4482 , xn_tax_amount => ln_tax_amount
4483 , pn_vendor_id => ln_vendor_id
4484 , pn_vendor_site_id => ln_vendor_site_id
4485 , pv_currency_code => lv_currency_code
4486 , pn_tax_category_id => ln_tax_category_id
4487 , pv_tax_modified => lv_tax_modified
4488 );
4489
4490 --into jai_ap_invoice_lines if item line exists in ap_invoice_lines_all
4491 --and not in jai_ap_invoice_lines
4492
4493 FOR item_line_rec IN
4494 new_invoice_lines_cur (ap_invoice_lines_rec.line_number)
4495 LOOP
4496 SELECT
4497 jai_ap_invoice_lines_s.NEXTVAL
4498 INTO
4499 ln_jai_inv_line_id
4500 FROM DUAL;
4501
4502 EXECUTE IMMEDIATE lv_insert_jai_inv_sql
4503 USING ln_jai_inv_line_id
4504 , pn_organization_id
4505 , pn_location_id
4506 , item_line_rec.invoice_id
4507 , item_line_rec.line_number
4508 , ln_vendor_site_id
4509 , ''
4510 , ln_tax_category_id
4511 , lv_service_type_code
4512 , item_line_rec.match_type
4513 , lv_currency_code
4514 , item_line_rec.amount
4515 , item_line_rec.line_type_lookup_code
4516 , item_line_rec.created_by
4517 , item_line_rec.creation_date
4518 , item_line_rec.last_update_date
4519 , item_line_rec.last_update_login
4520 , item_line_rec.last_updated_by ;
4521 END LOOP;
4522 --insert into eric_log values (7.4,'lv_insert_jai_inv_sql executed for item line :'||ap_invoice_lines_rec.line_number,sysdate);
4523
4524
4525 END IF;
4526
4527 --get max tax line numbers
4528 ln_max_tax_line_num := Get_Max_Tax_Line_Number
4529 ( ln_invoice_id
4530 , ap_invoice_lines_rec.line_number
4531 );
4532 --update the tax line number for the filed of
4533 --jai_cmn_document_taxes.source_doc_line_id
4534 --and split each PR taxes into to two lines
4535 FOR jai_default_doc_taxes_rec IN
4536 jai_default_doc_taxes_cur
4537 ( pn_invoice_id =>ln_invoice_id
4538 , pn_line_number =>ap_invoice_lines_rec.line_number
4539 )
4540 LOOP
4541 --insert into eric_log values (7.5,'Go into Create_Tax_Lines.ap_invoice_lines_cur.jai_default_doc_taxes_cur',sysdate);
4542 lb_inclusive_tax := check_inclusive_tax
4543 (jai_default_doc_taxes_rec.tax_id);
4544 --insert into eric_log values (7.6,'jai_default_doc_taxes_rec.tax_id: '||jai_default_doc_taxes_rec.tax_id,sysdate);
4545
4546 --commented out by eric for inclusive tax
4547 ----------------------------------------------------------
4548 --ln_max_inv_line_num :=ln_max_inv_line_num + 1;
4549 ----------------------------------------------------------
4550
4551 --added by eric for inclusive tax
4552 --for a inclusive tax, line source id is item LN #
4553 --for a exclusive tax, line source id is its corresponding invoice LN #
4554 ------------------------------------------------------------
4555 IF (lb_inclusive_tax) -- inclusive tax
4556 THEN
4557 --insert into eric_log values (7.7,'lb_inclusive_tax: '||'inclusive tax',sysdate);
4558 ln_source_doc_line_id := ap_invoice_lines_rec.line_number;
4559 ELSE -- exclusive tax
4560 --insert into eric_log values (7.7,'lb_inclusive_tax: '||'exclusive tax',sysdate);
4561 ln_max_inv_line_num := ln_max_inv_line_num + 1;
4562 ln_source_doc_line_id := ln_max_inv_line_num ;
4563 END IF;--(lb_inclusive_tax)
4564 ------------------------------------------------------------
4565
4566 IF pv_action = jai_constants.default_taxes
4567 THEN
4568 OPEN get_tax_cur (jai_default_doc_taxes_rec.tax_id);
4569 FETCH get_tax_cur
4570 INTO
4571 tax_rec;
4572 CLOSE get_tax_cur;
4573
4574 lv_tax_type := Get_Tax_Type
4575 ( pv_modvat_flag =>jai_default_doc_taxes_rec.modvat_flag
4576 , pn_cr_percentage =>tax_rec.mod_cr_percentage
4577 );
4578
4579 --added by eric to fix the bug bug#6784111 on Jan 29,2008 ,begin
4580 --------------------------------------------------------------------
4581 lv_pr_processed_flag := Get_Pr_Processed_Flag
4582 ( pn_source_doc_id =>jai_default_doc_taxes_rec.source_doc_id
4583 , pn_source_parent_line_no =>jai_default_doc_taxes_rec.source_doc_parent_line_no
4584 , pn_tax_id =>jai_default_doc_taxes_rec.tax_id
4585 );
4586 --------------------------------------------------------------------
4587 --added by eric to fix the bug bug#6784111 on Jan 29,2008,end
4588
4589 --common variables
4590 ln_tax_amount := NVL(jai_default_doc_taxes_rec.tax_amt,0);
4591 ln_func_tax_amount := NVL(jai_default_doc_taxes_rec.func_tax_amt,0);
4592
4593
4594 --log for debug
4595 IF ( ln_proc_level >= ln_dbg_level)
4596 THEN
4597 FND_LOG.STRING ( ln_proc_level
4598 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.ap_invoice_lines_rec.line_number'
4599 , ap_invoice_lines_rec.line_number
4600 );
4601
4602 FND_LOG.STRING ( ln_proc_level
4603 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.lv_tax_type'
4604 , lv_tax_type
4605 );
4606 END IF; --( ln_proc_level >= ln_dbg_level )
4607
4608 --fully recoverable /non recoverable tax
4609 --only one tax line are created
4610 IF (lv_tax_type='FR' OR lv_tax_type='NR')
4611 THEN
4612 -- update the source_doc_line_id to the real invoice line number
4613 UPDATE
4614 jai_cmn_document_taxes
4615 SET
4616 --modified by eric for inclusive tax
4617 ----------------------------------------------------------------
4618 source_doc_line_id = ln_source_doc_line_id --ln_max_inv_line_num
4619 ----------------------------------------------------------------
4620 WHERE CURRENT OF jai_default_doc_taxes_cur ;
4621
4622 --deleted by eric to fix the bug bug#6784111 on Jan 29,2008 ,begin
4623 ----------------------------------------------------------------------
4624 -- partially recoverable lines
4625 -- ELSIF ( lv_tax_type='PR' )
4626 --------------------------------------------------------------------
4627 --added by eric to fix the bug bug#6784111 on Jan 29,2008 ,end
4628
4629 --To fix the bug of processing the PR tax on the splitted Recvoerable portion
4630 --added by eric to fix the bug bug#6784111 on Jan 29,2008 ,begin
4631 ----------------------------------------------------------------------
4632 ELSIF ( lv_tax_type='PR' AND lv_pr_processed_flag =JAI_CONSTANTS.no)
4633 THEN
4634 --------------------------------------------------------------------
4635 --added by eric to fix the bug bug#6784111 on Jan 29,2008 ,end
4636
4637 -- if the tax is partially recoverable tax, the tax line of table
4638 -- jai_cmn_document_taxes is required to be splited in two lines
4639 -- recoverable part and non-recoverable part
4640
4641 ln_recur_tax_amt :=
4642 NVL(ROUND( ln_tax_amount
4643 * (tax_rec.mod_cr_percentage / 100)
4644 , tax_rec.rounding_factor
4645 ),0);
4646
4647 ln_recur_func_tax_amt :=
4648 NVL(ROUND( ln_func_tax_amount
4649 * (tax_rec.mod_cr_percentage / 100)
4650 , tax_rec.rounding_factor
4651 ),0);
4652 ln_nrecur_tax_amt :=ln_tax_amount-ln_recur_tax_amt;
4653 ln_nrecur_func_tax_amt :=ln_func_tax_amount -ln_recur_func_tax_amt;
4654
4655 --log for debug
4656 IF ( ln_proc_level >= ln_dbg_level)
4657 THEN
4658 FND_LOG.STRING ( ln_proc_level
4659 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.ln_recur_tax_amt'
4660 , ln_recur_tax_amt
4661 );
4662
4663 FND_LOG.STRING ( ln_proc_level
4664 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.ln_recur_func_tax_amt'
4665 , ln_recur_func_tax_amt
4666 );
4667 FND_LOG.STRING ( ln_proc_level
4668 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.ln_nrecur_tax_amt'
4669 , ln_nrecur_tax_amt
4670 );
4671 FND_LOG.STRING ( ln_proc_level
4672 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.ln_nrecur_func_tax_amt'
4673 , ln_nrecur_func_tax_amt
4674 );
4675 END IF; --( ln_proc_level >= ln_dbg_level )
4676
4677 -- To make the 2 PR tax line with same creation date and last update
4678 ld_sys_date := sysdate; --Eric added on 18-Feb-2008,for bug#6824857
4679
4680 FOR i IN 1..2
4681 LOOP
4682
4683 --log for debug
4684 IF ( ln_proc_level >= ln_dbg_level)
4685 THEN
4686 FND_LOG.STRING ( ln_proc_level
4687 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.i'
4688 , i
4689 );
4690 END IF; --( ln_proc_level >= ln_dbg_level )
4691
4692 -- insert revocerable part
4693 IF (i = 1)
4694 THEN
4695 -- update the source_doc_line_id to the invoice line number
4696 UPDATE
4697 jai_cmn_document_taxes
4698 SET
4699 --modified by eric for inclusive tax
4700 ----------------------------------------------------------------
4701 source_doc_line_id = ln_source_doc_line_id --ln_max_inv_line_num
4702 ----------------------------------------------------------------
4703 , tax_amt = ln_recur_tax_amt
4704 , func_tax_amt = ln_recur_func_tax_amt
4705 , modvat_flag = 'Y'
4706 , creation_date = ld_sys_date --Eric added on 18-Feb-2008,for bug#6824857
4707 , last_update_date = ld_sys_date --sysdate,Eric changed on 18-Feb-2008,for bug#6824857
4708 , created_by = ln_user_id --Eric added on 18-Feb-2008,for bug#6824857
4709 , last_updated_by = ln_user_id
4710 , last_update_login = ln_login_id
4711 WHERE CURRENT OF jai_default_doc_taxes_cur ;
4712 ELSIF (i=2)
4713 THEN
4714 --commented out by eric for inclusive tax
4715 ----------------------------------------------------------
4716 --ln_max_inv_line_num :=ln_max_inv_line_num + 1;
4717 ----------------------------------------------------------
4718
4719 --added by eric for inclusive tax
4720 --for a inclusive tax, line source id is item LN #
4721 --for a exclusive tax, line source id is its corresponding invoice LN #
4722 ------------------------------------------------------------
4723 IF (lb_inclusive_tax) -- inclusive tax
4724 THEN
4725 ln_source_doc_line_id := ap_invoice_lines_rec.line_number;
4726 ELSE -- exclusive tax
4727 ln_max_inv_line_num := ln_max_inv_line_num + 1;
4728 ln_source_doc_line_id := ln_max_inv_line_num ;
4729 END IF;--(lb_inclusive_tax)
4730
4731 --ln_max_tax_line_num := ln_max_tax_line_num +1; Eric deleted for two records shown in Form
4732
4733 --Eric Ma added for two records shown in Form,begin
4734 --------------------------------------------------
4735 ln_max_tax_line_num := jai_default_doc_taxes_rec.tax_line_no;
4736 ------------------------------------------------------
4737 --Eric Ma added for two records shown in Form,end
4738
4739 SELECT
4740 jai_cmn_document_taxes_s.nextval
4741 INTO
4742 ln_doc_tax_id
4743 FROM DUAL;
4744
4745 EXECUTE IMMEDIATE lv_insert_jai_tax_sql
4746 USING ln_doc_tax_id
4747 , ln_max_tax_line_num
4748 , jai_default_doc_taxes_rec.tax_id
4749 , jai_default_doc_taxes_rec.tax_type
4750 , jai_default_doc_taxes_rec.currency_code
4751 , jai_default_doc_taxes_rec.tax_rate
4752 , jai_default_doc_taxes_rec.qty_rate
4753 , jai_default_doc_taxes_rec.uom
4754 , ln_nrecur_tax_amt --TAX_AMT
4755 , ln_nrecur_func_tax_amt --FUNC_TAX_AMT
4756 , 'N' --MODVAT_FLAG
4757 , jai_default_doc_taxes_rec.tax_category_id
4758 , jai_default_doc_taxes_rec.source_doc_type
4759 , jai_default_doc_taxes_rec.source_doc_id
4760 --modified by eric for inclusive tax
4761 -----------------------------------------------
4762 ,ln_source_doc_line_id --, ln_max_inv_line_num
4763 -----------------------------------------------
4764 , jai_default_doc_taxes_rec.source_table_name
4765 , jai_default_doc_taxes_rec.tax_modified_by
4766 , jai_default_doc_taxes_rec.adhoc_flag
4767 , jai_default_doc_taxes_rec.precedence_1
4768 , jai_default_doc_taxes_rec.precedence_2
4769 , jai_default_doc_taxes_rec.precedence_3
4770 , jai_default_doc_taxes_rec.precedence_4
4771 , jai_default_doc_taxes_rec.precedence_5
4772 , jai_default_doc_taxes_rec.precedence_6
4773 , jai_default_doc_taxes_rec.precedence_7
4774 , jai_default_doc_taxes_rec.precedence_8
4775 , jai_default_doc_taxes_rec.precedence_9
4776 , jai_default_doc_taxes_rec.precedence_10
4777 , ld_sys_date --SYSDATE,Eric changed on 18-Feb-2008,for bug#6824857 --creation_date
4778 , ln_user_id --created_by
4779 , ld_sys_date --SYSDATE,Eric changed on 18-Feb-2008,for bug#6824857 --last_update_date
4780 , ln_user_id --last_updated_by
4781 , ln_login_id --last_update_login
4782 , jai_default_doc_taxes_rec.object_version_number
4783 , jai_default_doc_taxes_rec.vendor_id
4784 , ap_invoice_lines_rec.line_number;
4785 END IF; --(i=1)
4786 END LOOP; --(i IN 1..2)
4787 END IF; --(lv_tax_type='FR' OR lv_tax_type='NR')
4788 END IF; -- (default_tax)
4789 END LOOP; -- (jai_default_doc_taxes_cur)
4790 END LOOP; -- (ap_invoice_lines_rec AP INVOICE ITEM LINE LEVEL)
4791
4792 --delete taxes from ap invoice/dist lines,jai_ap_invoice_lines
4793 IF (pv_tax_modified ='Y')
4794 THEN
4795 Delete_Tax_Lines ( pn_invoice_id => ln_invoice_id
4796 , pn_line_number => ln_line_number
4797 , pv_modified_only_flag => 'Y'
4798 );
4799 END IF;--(pv_tax_modified ='Y')
4800
4801 --log for debug
4802 IF ( ln_proc_level >= ln_dbg_level)
4803 THEN
4804 FND_LOG.STRING ( ln_proc_level
4805 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.debug Info.'
4806 , 'Item Loop of Second Time '
4807 );
4808 END IF; --( ln_proc_level >= ln_dbg_level )
4809
4810 --The below 2 loops is to synchronize exclusive taxes information from
4811 --jai_cmn_document_taxes to other 3 tables
4812
4813 --insert into eric_log values (7.8,'prepare to sync data from jai_cmn_document_taxes to other 3 tables ',sysdate);
4814
4815 --Loop 1, item level: Loop all item lines in the ap_invoice_lines
4816 FOR ap_invoice_lines_rec IN ap_invoice_lines_cur
4817 LOOP
4818
4819 --Loop 2,tax level:Loop all tax lines in jai_cmn_document_taxes
4820 FOR jai_doc_taxes_rec IN
4821 jai_doc_taxes_cur
4822 ( pn_invoice_id => ln_invoice_id
4823 , pn_parent_line_number => ap_invoice_lines_rec.line_number
4824 )
4825 LOOP
4826 --insert into eric_log values (7.801,'come into loop ap_invoice_lines_rec.jai_doc_taxes_rec for item line :'|| ap_invoice_lines_rec.line_number,sysdate);
4827 --insert into eric_log values (7.802,'jai_doc_taxes_rec.tax_id = '|| jai_doc_taxes_rec.tax_id,sysdate);
4828
4829 /*Removed this IF from here by Jia Li for inclusive tax on 2008/01/23
4830 --added by eric for inclusive tax
4831 --insert exclusive tax into jai_ap_invoice_lines and standard ap tables
4832 ----------------------------------------------------------
4833 IF (NVL(jai_doc_taxes_rec.inc_tax_flag,'N')='N')
4834 THEN
4835 ----------------------------------------------------------
4836 */
4837 OPEN get_tax_cur (jai_doc_taxes_rec.tax_id);
4838 FETCH get_tax_cur
4839 INTO
4840 tax_rec;
4841 CLOSE get_tax_cur;
4842
4843 lv_tax_type :=
4844 Get_Tax_Type
4845 ( pv_modvat_flag =>jai_doc_taxes_rec.modvat_flag
4846 , pn_cr_percentage =>tax_rec.mod_cr_percentage
4847 );
4848
4849 --get mandantory parameters from item line
4850 --the acct distribution will be handled in other procedure
4851 OPEN ap_invoice_dist_cur (ap_invoice_lines_rec.line_number);
4852 FETCH
4853 ap_invoice_dist_cur
4854 INTO
4855 ap_invoice_dist_rec ;
4856 CLOSE ap_invoice_dist_cur;
4857
4858 IF (lv_tax_type = 'NR')
4859 THEN
4860 ln_asset_track_flag := ap_invoice_lines_rec.assets_tracking_flag;
4861 ln_project_id := ap_invoice_lines_rec.project_id;
4862 ln_task_id := ap_invoice_lines_rec.task_id;
4863 lv_expenditure_type := ap_invoice_lines_rec.expenditure_type;
4864 ld_exp_item_date := ap_invoice_lines_rec.expenditure_item_date;
4865 ln_exp_org_id := ap_invoice_lines_rec.expenditure_organization_id;
4866
4867 ln_dist_asst_add_flag :=ap_invoice_dist_rec.assets_addition_flag ;
4868 ln_dist_asst_trck_flag:=ap_invoice_dist_rec.assets_tracking_flag ;
4869 ln_dist_project_id :=ap_invoice_dist_rec.project_id;
4870 ln_dist_task_id :=ap_invoice_dist_rec.task_id;
4871 ln_dist_exp_type :=ap_invoice_dist_rec.expenditure_type;
4872 ld_dist_exp_item_date :=ap_invoice_dist_rec.expenditure_item_date;
4873 ln_dist_exp_org_id :=ap_invoice_dist_rec.expenditure_organization_id;
4874 ln_dist_pa_context :=ap_invoice_dist_rec.project_accounting_context;
4875 ln_dist_pa_addition_flag :=ap_invoice_dist_rec.pa_addition_flag;
4876 lv_asset_book_type_code :=ap_invoice_dist_rec.asset_book_type_code;
4877 ln_asset_category_id :=ap_invoice_dist_rec.asset_category_id;
4878 lv_tax_recoverable_flag :='N';
4879
4880 ELSE --(RECOVERABLE)
4881 ln_asset_track_flag := 'N';
4882 ln_project_id := NULL;
4883 ln_task_id := NULL;
4884 lv_expenditure_type := NULL;
4885 ld_exp_item_date := NULL;
4886 ln_exp_org_id := NULL;
4887
4888 ln_dist_asst_add_flag := 'U';
4889 ln_dist_asst_trck_flag := 'N';
4890 ln_dist_project_id := NULL;
4891 ln_dist_task_id := NULL;
4892 ln_dist_exp_type := NULL;
4893 ld_dist_exp_item_date := NULL;
4894 ln_dist_exp_org_id := NULL;
4895 ln_dist_pa_context := NULL;
4896 ln_dist_pa_addition_flag := 'E'; --NOT PROJECT RELATED
4897 lv_tax_recoverable_flag := 'Y';
4898 lv_asset_book_type_code := NULL;
4899 ln_asset_category_id := NULL;
4900 END IF; --(lv_tax_type = 'NR')
4901
4902 --Moved IF to here by Jia Li for inclusive tax on 2008/01/23
4903 --added by eric for inclusive tax
4904 --insert exclusive tax into jai_ap_invoice_lines and standard ap tables
4905 ----------------------------------------------------------
4906 IF (NVL(jai_doc_taxes_rec.inc_tax_flag,'N')='N')
4907 THEN
4908 ----------------------------------------------------------
4909 SELECT
4910 jai_ap_invoice_lines_s.NEXTVAL
4911 INTO
4912 ln_jai_inv_line_id
4913 FROM DUAL;
4914
4915 --insert into eric_log values (7.81,'jai_doc_taxes_rec.inc_tax_flag =''N'' Branch ',sysdate);
4916 --insert into jai_ap_invoice_lines
4917 EXECUTE IMMEDIATE lv_insert_jai_inv_sql
4918 USING ln_jai_inv_line_id
4919 , pn_organization_id
4920 , pn_location_id
4921 , jai_doc_taxes_rec.source_doc_id
4922 , jai_doc_taxes_rec.source_doc_line_id
4923 , ln_vendor_site_id
4924 , jai_doc_taxes_rec.source_doc_parent_line_no
4925 , jai_doc_taxes_rec.tax_category_id
4926 , '' --service_type ,used by item line only
4927 , ap_invoice_lines_rec.match_type
4928 , lv_currency_code
4929 , jai_doc_taxes_rec.tax_amt
4930 , GV_CONSTANT_MISCELLANEOUS
4931 , ln_user_id
4932 , SYSDATE
4933 , SYSDATE
4934 , ln_login_id
4935 , ln_user_id ;
4936
4937 --insert into eric_log values (7.82,'lv_insert_jai_inv_sql executed for item line number :'|| ap_invoice_lines_rec.line_number,sysdate);
4938 --log for debug
4939 IF ( ln_proc_level >= ln_dbg_level)
4940 THEN
4941 FND_LOG.STRING ( ln_proc_level
4942 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
4943 || '.debug Info.'
4944 , 'Table jai_ap_invoice_lines inserted '
4945 );
4946 END IF; --( ln_proc_level >= ln_dbg_level )
4947
4948 --insert into ap_invoice_lines_all
4949 EXECUTE IMMEDIATE lv_insert_ap_inv_ln_sql
4950 USING jai_doc_taxes_rec.source_doc_id
4951 , jai_doc_taxes_rec.source_doc_line_id
4952 , GV_CONSTANT_MISCELLANEOUS
4953 , tax_rec.tax_name
4954 , ap_invoice_lines_rec.org_id
4955 , ln_asset_track_flag
4956 , ap_invoice_lines_rec.match_type
4957 , ap_invoice_lines_rec.accounting_date
4958 , ap_invoice_lines_rec.period_name
4959 , ap_invoice_lines_rec.deferred_acctg_flag
4960 , ap_invoice_lines_rec.def_acctg_start_date
4961 , ap_invoice_lines_rec.def_acctg_end_date
4962 , ap_invoice_lines_rec.def_acctg_number_of_periods
4963 , ap_invoice_lines_rec.def_acctg_period_type
4964 , ap_invoice_lines_rec.set_of_books_id
4965 , jai_doc_taxes_rec.tax_amt
4966 , ap_invoice_lines_rec.wfapproval_status
4967 , SYSDATE
4968 , ln_user_id
4969 , ln_user_id
4970 , SYSDATE
4971 , ln_login_id
4972 , ln_project_id
4973 , ln_task_id
4974 , lv_expenditure_type
4975 , ld_exp_item_date
4976 , ln_exp_org_id ;
4977
4978 --insert into eric_log values (7.83,'lv_insert_ap_inv_ln_sql executed for item line number :'|| ap_invoice_lines_rec.line_number,sysdate);
4979
4980 --log for debug
4981 IF ( ln_proc_level >= ln_dbg_level)
4982 THEN
4983 FND_LOG.STRING ( ln_proc_level
4984 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
4985 || '.debug Info.'
4986 , 'Table ap_invoice_lines_all inserted '
4987 );
4988 END IF; --( ln_proc_level >= ln_dbg_level )
4989
4990 ln_dist_acct_ccid :=
4991 Get_Dist_Account_Ccid
4992 ( pn_invoice_id => ln_invoice_id
4993 , pn_item_line_number => ap_invoice_lines_rec.line_number
4994 , pn_organization_id => pn_organization_id
4995 , pn_location_id => pn_location_id
4996 , pn_tax_type_code => tax_rec.tax_type
4997 , pn_tax_acct_ccid => tax_rec.tax_account_id
4998 , pv_tax_type => lv_tax_type
4999 );
5000
5001 SELECT
5002 ap_invoice_distributions_s.NEXTVAL
5003 INTO
5004 ln_inv_dist_id
5005 FROM DUAL;
5006
5007 IF (ap_invoice_dist_rec.assets_tracking_flag = 'N')
5008 THEN
5009 ln_chargeble_acct_ccid :=NULL;
5010 ELSE
5011 lv_account_type := Get_Gl_Account_Type (ln_dist_acct_ccid);
5012
5013 IF lv_account_type ='A'
5014 THEN
5015 ln_chargeble_acct_ccid := ln_dist_acct_ccid;
5016 ELSE
5017 ln_chargeble_acct_ccid := NULL;
5018 END IF;
5019 END IF;
5020
5021 --insert into ap_distribution_lines_all
5022 EXECUTE IMMEDIATE lv_insert_ap_inv_dist_ln_sql
5023 USING ap_invoice_lines_rec.accounting_date
5024 , 'N'
5025 , ln_dist_asst_add_flag
5026 , ln_dist_asst_trck_flag
5027 , 'N'
5028 , 1 --distribution_line_number
5029 , ln_dist_acct_ccid
5030 , ln_invoice_id
5031 , ln_user_id
5032 , SYSDATE
5033 , GV_CONSTANT_MISCELLANEOUS
5034 , ap_invoice_lines_rec.period_name
5035 , ap_invoice_lines_rec.set_of_books_id
5036 , jai_doc_taxes_rec.tax_amt
5037 -- , jai_doc_taxes_rec.func_tax_amt :deleted by eric on 2008-Jan-08, as po_matched case not populate the column
5038 , ln_batch_id --invoice header level
5039 , ln_user_id
5040 , SYSDATE
5041 , tax_rec.tax_name
5042 , ''
5043 , ln_login_id
5044 , ap_invoice_dist_rec.match_status_flag
5045 , 'N' -- posted_flag
5046 , ''
5047 , ap_invoice_dist_rec.reversal_flag
5048 , ap_invoice_dist_rec.program_application_id
5049 , ap_invoice_dist_rec.program_id
5050 , ap_invoice_dist_rec.program_update_date
5051 , ap_invoice_dist_rec.accts_pay_code_combination_id
5052 , ln_inv_dist_id
5053 , -1
5054 , ''
5055 , ''
5056 , ap_invoice_dist_rec.rcv_transaction_id
5057 , ap_invoice_dist_rec.invoice_price_variance
5058 , ap_invoice_dist_rec.base_invoice_price_variance
5059 , ap_invoice_dist_rec.matched_uom_lookup_code
5060 , jai_doc_taxes_rec.source_doc_line_id
5061 , ap_invoice_lines_rec.org_id
5062 , ln_chargeble_acct_ccid
5063 , ln_dist_project_id
5064 , ln_dist_task_id
5065 , ln_dist_exp_type
5066 , ld_dist_exp_item_date
5067 , ln_dist_exp_org_id
5068 , ln_dist_pa_context
5069 , ln_dist_pa_addition_flag
5070 , ap_invoice_dist_rec.distribution_class
5071 , lv_tax_recoverable_flag;
5072
5073 --insert into eric_log values (7.84,'lv_insert_ap_inv_dist_ln_sql executed for item line number :'|| ap_invoice_lines_rec.line_number,sysdate);
5074 --log for debug
5075 IF ( ln_proc_level >= ln_dbg_level)
5076 THEN
5077 FND_LOG.STRING ( ln_proc_level
5078 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
5079 || '.debug Info.'
5080 , 'Table ap_distribution_lines_all inserted '
5081 );
5082 END IF; --( ln_proc_level >= ln_dbg_level )
5083 --added by eric for inclusive tax
5084 ----------------------------------------------------------------------
5085
5086 -- Added by Jia Li for inclusive tax on 2008/01/23, Begin
5087 -- insert two lines with inclusive recoverable tax
5088 -- One line is negative with project info
5089 -- another line is positive with no project info
5090 ----------------------------------------------------------
5091 ELSIF ( NVL(jai_doc_taxes_rec.inc_tax_flag,'N') = 'Y' )
5092 AND ( lv_tax_type <> 'NR' )
5093 AND ( ap_invoice_lines_rec.project_id IS NOT NULL )
5094 THEN
5095
5096 --insert into eric_log values (7.91,'jai_doc_taxes_rec.inc_tax_flag =''Y'' AND recoverable with project case ',sysdate);
5097
5098 -- Insert negative line with project info
5099 -- Line number got from max jai_cmn_document_taxes.source_doc_line_id
5100 -- or max ap_invoice_lines_all.line_number
5101 ln_max_inv_line_num := Get_Max_Invoice_Line_Number(ln_invoice_id);
5102
5103 -- deleted by eric for fixing the bug of bug#6784111 on 29-JAN,2008,begin
5104 /*
5105 ln_max_tax_line_num := Get_Max_Tax_Line_Number
5106 ( ln_invoice_id
5107 , ap_invoice_lines_rec.line_number );
5108
5109 IF ln_max_inv_line_num >= ln_max_tax_line_num
5110 THEN
5111 ln_max_pro_line_num := ln_max_inv_line_num + 1;
5112 ELSE
5113 ln_max_pro_line_num := ln_max_tax_line_num + 1;
5114 END IF;
5115 */
5116 -- deleted by eric for fixing the bug of bug#6784111 on 29-JAN,2008,end
5117
5118 --added by eric for fixing the bug of bug#6784111 on 29-JAN,2008,begin
5119 ----------------------------------------------------------------------
5120 ln_max_source_line_id := Get_Max_Doc_Source_Line_Id(ln_invoice_id);
5121
5122 IF (ln_max_inv_line_num >= ln_max_source_line_id )
5123 THEN
5124 ln_max_pro_line_num := ln_max_inv_line_num + 1;
5125 ELSE
5126 ln_max_pro_line_num := ln_max_source_line_id + 1;
5127 END IF; --(ln_max_inv_line_num >= ln_max_source_line_id )
5128 ----------------------------------------------------------------------
5129 --added by eric for fixing the bug of bug#6784111 on 29-JAN,2008,end
5130
5131 SELECT
5132 jai_ap_invoice_lines_s.NEXTVAL
5133 INTO
5134 ln_jai_inv_line_id
5135 FROM DUAL;
5136
5137 EXECUTE IMMEDIATE lv_insert_jai_inv_sql
5138 USING ln_jai_inv_line_id
5139 , pn_organization_id
5140 , pn_location_id
5141 , jai_doc_taxes_rec.source_doc_id -- invoice_id
5142 , ln_max_pro_line_num -- invoice_line_num
5143 , ln_vendor_site_id
5144 , jai_doc_taxes_rec.source_doc_parent_line_no
5145 , jai_doc_taxes_rec.tax_category_id
5146 , '' --service_type ,used by item line only
5147 , ap_invoice_lines_rec.match_type
5148 , lv_currency_code
5149 , -jai_doc_taxes_rec.tax_amt -- negative tax amount
5150 , GV_CONSTANT_MISCELLANEOUS
5151 , ln_user_id
5152 , SYSDATE
5153 , SYSDATE
5154 , ln_login_id
5155 , ln_user_id ;
5156
5157 --insert into eric_log values (7.92,'lv_insert_jai_inv_sql executed for item line number :'|| ap_invoice_lines_rec.line_number,sysdate);
5158
5159 EXECUTE IMMEDIATE lv_insert_ap_inv_ln_sql
5160 USING jai_doc_taxes_rec.source_doc_id -- invoice_id
5161 , ln_max_pro_line_num -- line_number
5162 , GV_CONSTANT_MISCELLANEOUS
5163 , tax_rec.tax_name
5164 , ap_invoice_lines_rec.org_id
5165 , ap_invoice_lines_rec.assets_tracking_flag
5166 , ap_invoice_lines_rec.match_type
5167 , ap_invoice_lines_rec.accounting_date
5168 , ap_invoice_lines_rec.period_name
5169 , ap_invoice_lines_rec.deferred_acctg_flag
5170 , ap_invoice_lines_rec.def_acctg_start_date
5171 , ap_invoice_lines_rec.def_acctg_end_date
5172 , ap_invoice_lines_rec.def_acctg_number_of_periods
5173 , ap_invoice_lines_rec.def_acctg_period_type
5174 , ap_invoice_lines_rec.set_of_books_id
5175 , -jai_doc_taxes_rec.tax_amt -- negative tax amount
5176 , ap_invoice_lines_rec.wfapproval_status
5177 , SYSDATE
5178 , ln_user_id
5179 , ln_user_id
5180 , SYSDATE
5181 , ln_login_id
5182 , ap_invoice_lines_rec.project_id
5183 , ap_invoice_lines_rec.task_id
5184 , ap_invoice_lines_rec.expenditure_type
5185 , ap_invoice_lines_rec.expenditure_item_date
5186 , ap_invoice_lines_rec.expenditure_organization_id ;
5187
5188 --insert into eric_log values (7.92,'lv_insert_ap_inv_ln_sql executed for item line number :'|| ap_invoice_lines_rec.line_number,sysdate);
5189
5190 ln_dist_acct_ccid := Get_Dist_Account_Ccid
5191 ( pn_invoice_id => ln_invoice_id
5192 , pn_item_line_number => ap_invoice_lines_rec.line_number
5193 , pn_organization_id => pn_organization_id
5194 , pn_location_id => pn_location_id
5195 , pn_tax_type_code => tax_rec.tax_type
5196 , pn_tax_acct_ccid => tax_rec.tax_account_id
5197 , pv_tax_type => lv_tax_type
5198 );
5199
5200 SELECT
5201 ap_invoice_distributions_s.NEXTVAL
5202 INTO
5203 ln_inv_dist_id
5204 FROM DUAL;
5205
5206 IF (ap_invoice_dist_rec.assets_tracking_flag = 'N')
5207 THEN
5208 ln_chargeble_acct_ccid :=NULL;
5209 ELSE
5210 lv_account_type := Get_Gl_Account_Type (ln_dist_acct_ccid);
5211
5212 IF lv_account_type ='A'
5213 THEN
5214 ln_chargeble_acct_ccid := ln_dist_acct_ccid;
5215 ELSE
5216 ln_chargeble_acct_ccid := NULL;
5217 END IF;
5218 END IF;
5219
5220 EXECUTE IMMEDIATE lv_insert_ap_inv_dist_ln_sql
5221 USING ap_invoice_lines_rec.accounting_date
5222 , 'N'
5223 , ap_invoice_dist_rec.assets_addition_flag
5224 , ap_invoice_dist_rec.assets_tracking_flag
5225 , 'N'
5226 , 1 --distribution_line_number
5227 , ln_dist_acct_ccid
5228 , ln_invoice_id
5229 , ln_user_id
5230 , SYSDATE
5231 , GV_CONSTANT_MISCELLANEOUS
5232 , ap_invoice_lines_rec.period_name
5233 , ap_invoice_lines_rec.set_of_books_id
5234 , -jai_doc_taxes_rec.tax_amt -- negative tax amount
5235 -- , jai_doc_taxes_rec.func_tax_amt :deleted by eric on 2008-Jan-08, as po_matched case not populate the column
5236 , ln_batch_id -- invoice header level
5237 , ln_user_id
5238 , SYSDATE
5239 , tax_rec.tax_name
5240 , ''
5241 , ln_login_id
5242 , ap_invoice_dist_rec.match_status_flag
5243 , 'N' -- posted_flag
5244 , ''
5245 , ap_invoice_dist_rec.reversal_flag
5246 , ap_invoice_dist_rec.program_application_id
5247 , ap_invoice_dist_rec.program_id
5248 , ap_invoice_dist_rec.program_update_date
5249 , ap_invoice_dist_rec.accts_pay_code_combination_id
5250 , ln_inv_dist_id
5251 , -1
5252 , ''
5253 , ''
5254 , ap_invoice_dist_rec.price_var_code_combination_id
5255 , ap_invoice_dist_rec.invoice_price_variance
5256 , ap_invoice_dist_rec.base_invoice_price_variance
5257 , ap_invoice_dist_rec.matched_uom_lookup_code
5258 , ln_max_pro_line_num -- invoice_line_number
5259 , ap_invoice_lines_rec.org_id
5260 , ln_chargeble_acct_ccid
5261 , ap_invoice_dist_rec.project_id
5262 , ap_invoice_dist_rec.task_id
5263 , ap_invoice_dist_rec.expenditure_type
5264 , ap_invoice_dist_rec.expenditure_item_date
5265 , ap_invoice_dist_rec.expenditure_organization_id
5266 , ap_invoice_dist_rec.project_accounting_context
5267 , ap_invoice_dist_rec.pa_addition_flag
5268 , ap_invoice_dist_rec.distribution_class
5269 , 'Y';
5270
5271 --insert into eric_log values (7.93,'lv_insert_ap_inv_dist_ln_sql executed for item line number :'|| ap_invoice_lines_rec.line_number,sysdate);
5272
5273 -- Insert positive line with no project info
5274 ln_max_pro_line_num := ln_max_pro_line_num + 1;
5275
5276 SELECT
5277 jai_ap_invoice_lines_s.NEXTVAL
5278 INTO
5279 ln_jai_inv_line_id
5280 FROM DUAL;
5281
5282 --insert into eric_log values (7.94,'prepare to insert ositive line with no project info ',sysdate);
5283
5284 EXECUTE IMMEDIATE lv_insert_jai_inv_sql
5285 USING ln_jai_inv_line_id
5286 , pn_organization_id
5287 , pn_location_id
5288 , jai_doc_taxes_rec.source_doc_id -- invoice_id
5289 , ln_max_pro_line_num -- line_number
5290 , ln_vendor_site_id
5291 , jai_doc_taxes_rec.source_doc_parent_line_no
5292 , jai_doc_taxes_rec.tax_category_id
5293 , '' --service_type ,used by item line only
5294 , ap_invoice_lines_rec.match_type
5295 , lv_currency_code
5296 , jai_doc_taxes_rec.tax_amt -- positive tax amount
5297 , GV_CONSTANT_MISCELLANEOUS
5298 , ln_user_id
5299 , SYSDATE
5300 , SYSDATE
5301 , ln_login_id
5302 , ln_user_id ;
5303
5304 --insert into eric_log values (7.95,'lv_insert_jai_inv_sql executed for item line number :'|| ap_invoice_lines_rec.line_number,sysdate);
5305
5306 EXECUTE IMMEDIATE lv_insert_ap_inv_ln_sql
5307 USING jai_doc_taxes_rec.source_doc_id -- invoice_id
5308 , ln_max_pro_line_num -- line_number
5309 , GV_CONSTANT_MISCELLANEOUS
5310 , tax_rec.tax_name
5311 , ap_invoice_lines_rec.org_id
5312 , 'N'
5313 , ap_invoice_lines_rec.match_type
5314 , ap_invoice_lines_rec.accounting_date
5315 , ap_invoice_lines_rec.period_name
5316 , ap_invoice_lines_rec.deferred_acctg_flag
5317 , ap_invoice_lines_rec.def_acctg_start_date
5318 , ap_invoice_lines_rec.def_acctg_end_date
5319 , ap_invoice_lines_rec.def_acctg_number_of_periods
5320 , ap_invoice_lines_rec.def_acctg_period_type
5321 , ap_invoice_lines_rec.set_of_books_id
5322 , jai_doc_taxes_rec.tax_amt -- positive tax amount
5323 , ap_invoice_lines_rec.wfapproval_status
5324 , SYSDATE
5325 , ln_user_id
5326 , ln_user_id
5327 , SYSDATE
5328 , ln_login_id
5329 , ''
5330 , ''
5331 , ''
5332 , ''
5333 , '' ;
5334
5335 --insert into eric_log values (7.96,'lv_insert_ap_inv_ln_sql executed for item line number :'|| ap_invoice_lines_rec.line_number,sysdate);
5336
5337 ln_dist_acct_ccid := Get_Dist_Account_Ccid
5338 ( pn_invoice_id => ln_invoice_id
5339 , pn_item_line_number => ap_invoice_lines_rec.line_number
5340 , pn_organization_id => pn_organization_id
5341 , pn_location_id => pn_location_id
5342 , pn_tax_type_code => tax_rec.tax_type
5343 , pn_tax_acct_ccid => tax_rec.tax_account_id
5344 , pv_tax_type => lv_tax_type
5345 );
5346
5347 SELECT
5348 ap_invoice_distributions_s.NEXTVAL
5349 INTO
5350 ln_inv_dist_id
5351 FROM DUAL;
5352
5353 IF (ap_invoice_dist_rec.assets_tracking_flag = 'N')
5354 THEN
5355 ln_chargeble_acct_ccid :=NULL;
5356 ELSE
5357 lv_account_type := Get_Gl_Account_Type (ln_dist_acct_ccid);
5358
5359 IF lv_account_type ='A'
5360 THEN
5361 ln_chargeble_acct_ccid := ln_dist_acct_ccid;
5362 ELSE
5363 ln_chargeble_acct_ccid := NULL;
5364 END IF;
5365 END IF;
5366
5367 EXECUTE IMMEDIATE lv_insert_ap_inv_dist_ln_sql
5368 USING ap_invoice_lines_rec.accounting_date
5369 , 'N'
5370 , 'U' -- assets_addition_flag
5371 , 'N' -- assets_tracking_flag
5372 , 'N' -- cash_posted_flag
5373 , 1 -- distribution_line_number
5374 , ln_dist_acct_ccid
5375 , ln_invoice_id
5376 , ln_user_id
5377 , SYSDATE
5378 , GV_CONSTANT_MISCELLANEOUS
5379 , ap_invoice_lines_rec.period_name
5380 , ap_invoice_lines_rec.set_of_books_id
5381 , jai_doc_taxes_rec.tax_amt -- positive tax amount
5382 -- , jai_doc_taxes_rec.func_tax_amt :deleted by eric on 2008-Jan-08, as po_matched case not populate the column
5383 , ln_batch_id -- invoice header level
5384 , ln_user_id
5385 , SYSDATE
5386 , tax_rec.tax_name
5387 , ''
5388 , ln_login_id
5389 , ap_invoice_dist_rec.match_status_flag
5390 , 'N' -- posted_flag
5391 , ''
5392 , ap_invoice_dist_rec.reversal_flag
5393 , ap_invoice_dist_rec.program_application_id
5394 , ap_invoice_dist_rec.program_id
5395 , ap_invoice_dist_rec.program_update_date
5396 , ap_invoice_dist_rec.accts_pay_code_combination_id
5397 , ln_inv_dist_id
5398 , -1
5399 , ''
5400 , ''
5401 , ap_invoice_dist_rec.price_var_code_combination_id
5402 , ap_invoice_dist_rec.invoice_price_variance
5403 , ap_invoice_dist_rec.base_invoice_price_variance
5404 , ap_invoice_dist_rec.matched_uom_lookup_code
5405 , ln_max_pro_line_num -- invoice_line_number
5406 , ap_invoice_lines_rec.org_id
5407 , ln_chargeble_acct_ccid
5408 , '' -- project_id
5409 , '' -- task_id
5410 , '' -- expenditure_type
5411 , '' -- expenditure_item_date
5412 , '' -- expenditure_organization_id
5413 , '' -- project_accounting_context
5414 , 'E' -- pa_addition_flag
5415 , ap_invoice_dist_rec.distribution_class
5416 , 'Y';
5417
5418 --insert into eric_log values (7.97,'lv_insert_ap_inv_dist_ln_sql executed for item line number :'|| ap_invoice_lines_rec.line_number,sysdate);
5419
5420 ----------------------------------------------------------
5421 -- Added by Jia Li for inclusive tax on 2008/01/23, End
5422
5423 END IF; -- NVL(jai_doc_taxes_rec.inc_tax_flag,'N')='N'
5424 ----------------------------------------------------------------------
5425 END LOOP; -- (all taxes for a given parent line number)
5426 END LOOP; -- (ap_invoice_lines_rec IN ap_invoice_lines_cur,second time)
5427
5428 --log for debug
5429 IF ( ln_proc_level >= ln_dbg_level)
5430 THEN
5431 FND_LOG.STRING ( ln_proc_level
5432 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
5433 , 'Exit procedure'
5434 );
5435 END IF; --( ln_proc_level >= ln_dbg_level )
5436
5437 EXCEPTION
5438 WHEN OTHERS THEN
5439 IF ( ln_proc_level >= ln_dbg_level)
5440 THEN
5441 FND_LOG.STRING ( ln_proc_level
5442 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
5443 || '. Other_Exception '
5444 , SQLCODE || ':' || SQLERRM
5445 );
5446 END IF; --( ln_proc_level >= ln_dbg_level) ;
5447 RAISE;
5448 END Create_Tax_Lines;
5449 END JAI_AP_STND_TAX_PROCESS;