[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.35.12020000.8 2013/03/18 05:59:30 zxin 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 --| FUNCTION Validate_Prepayment_Invoice | --Added by Chong.Lei for POT code port
36 --| HISTORY |
37 --| 2007/08/23 Eric Ma Created |
38 --| 2007/12/24 Eric Ma for inclusive tax |
39 --| 2008/01/25 Eric Ma for Bug#6770835 File verison 120.1 |
40 --| 2008/01/28 Eric Ma for Bug of not deleting tax lines |
41 --| 2008/01/29 Eric Ma for Bug#6784111 |
42 --| 2008/02/18 Eric Ma Changed Create_Tax_Lines for bug#6824857 |
43 --| 2008/03/19 Eric Ma Changed Populate_Stnd_Inv_Taxes for bug#6898716
44 --| 2008/04/23 Eric Ma Code change in Populate_Stnd_Inv_Taxes for bug6923963
45 --| 2008/11/21 Walton liu Code change in Create_Tax_Lines for bug#7202316
46 --| 2011/02/10 Xiao Lv Code change for Open Interface ER bug#11683927
47 --| 2011/05/12 Zhiwei Hou Code change for Open Interface ER bug#12537533
48 --| 2011/05/24 Xiao Lv Code change for POT, reg bug#12533434 --Added by Chong.Lei for POT code port
49 --| 2011/09/30 Zhiwei Hou Code change for Open API of POT change #13023443
50 --| 2011/12/27 amandali Code fix for 13535569 to avoid unique constraint (JA.JAI_AP_INVOICE_LINES_U2) violated error |
51 --| 2012/02/01 amandali code fix for bug 13617527 to avoid "ap_invoice_distributions_All.accounting date cannot be null"
52 --| 2012/03/04 amandali Code fix for bug 13793724 to pick the non recoverable tax lines by create mass additions concurrent
53 --| 2012/08/29 nkodakan Code fix for bug 14541702 added the 'End If' in two places.
54 --| 2012/10/10 amandali Bug:14650698
55 --| Description:0 tax lines are populated when a standalone invoice is cancelled for a vendor having default service tax category.
56 --| Fix: call to Create_tax_lines procedure is restricted incase of cancelled invoices.Changes are done in Populate_Stnd_Inv_Taxes
57 --| 2012/10/29 anupgupt Bug:14681650
58 --| Description: R12 AP INVOICE NAVIGATION PERFORMANCE ISSUE
59 --| Fix: Commented update statement on ap_invoice_lines_all
60 --+======================================================================*/
61
62 --==========================================================================
63 -- FUNCTION NAME:
64 --
65 -- Validate_Item_Dist_Lines Private
66 -- If any item doesn't have a distribution line of a given invoice id,
67 -- the function will return FALSE,otherwise it returns TRUE.
68 -- DESCRIPTION:
69 --
70 --
71 -- PARAMETERS:
72 -- In: pn_invoice_id IN NUMBER invoice id
73 --
74 -- Out: RETURN BOOLEAN
75 --
76 -- PRE-COND : invoice item line and tax lines exist
77 -- EXCEPTIONS:
78 --
79
80 -- 31-aug-2010 vkaranam for bug#9775984
81 -- Issue:UNABLE TO APPLY SERVICE TAX ON THIRD PARTY TAX INVOICES
82 -- Reason:this issue is occuring due to the fix in bug#9341898.
83 --- Validate_Item_Dist_Lines is returning as false when both the invoice distributions exists and default ccid
84 -- -- logic to populate the miscellaneous lines is :
85 -- if ("distribution line apid" does not exists and "default distribution ccid" exists)
86 -- then
87 -- populate the miscellaneous lines for taxes;
88 --
89 -- elsif ("distribution line apid" exists and "default distribution
90 -- ccid" not exists)
91 -- then
92 --
93 -- populate the miscellaneous lines for taxes;
94 --
95 -- else
96 --
97 -- do not populate the taxes.
98 -- end if;
99 --- fix: 1.changes are done in FUNCTION Validate_Item_Dist_Lines
100 -- if ("distribution line apid" exists OR "default distribution ccid" exists)
101 -- populate the miscellaneous lines for taxes;
102 -- else
103 --- do not populate the taxes.
104 -- end if;
105 -- 2.changes are done in Get_Dist_Account_Ccid function to get the default ccid
106 -- for non recoverable taxes.
107
108
109
110 --===========================================================================
111 FUNCTION Validate_Item_Dist_Lines
112 ( pn_invoice_id IN NUMBER)
113 --, 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
114 RETURN BOOLEAN
115 IS
116 CURSOR get_dist_line_number_cur IS
117 SELECT
118 aila.line_number
119 FROM
120 AP_INVOICE_LINES_ALL aila
121 , Ap_Invoice_Distributions_All aida
122 WHERE aila.INVOICE_ID = aida.invoice_id (+) --rollback to original logic
123 AND aila.line_number = aida.invoice_line_number (+) --rollback to original logic
124 AND aila.invoice_id = pn_invoice_id
125 --AND aila.line_number = pn_line_number -- Added by Jia Li for inclusive tax on 2008/01/25
126 --,commented out for the bug of deleting not working on Jan 28,2008
127 AND aila.line_type_lookup_code = GV_CONSTANT_ITEM -- ;
128 AND aida.invoice_line_number IS NULL
129 AND aida.invoice_id IS NULL ;
130
131 /*To check whether User has provided Distribution Account at the Lines Leve for bug 9341898*/
132 Cursor get_dist_line_number_lines_cur IS
133 Select 1
134 FROM AP_INVOICE_LINES_ALL
135 WHERE invoice_id = pn_invoice_id
136 and default_dist_ccid is not null;
137
138 ln_line_number NUMBER ;
139 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
140 ln_proc_level NUMBER := FND_LOG.level_procedure;
141 lv_proc_name VARCHAR2 (100) := 'Validate_Item_Dist_Lines';
142 v_num NUMBER;
143
144 BEGIN
145
146 IF ( ln_proc_level >= ln_dbg_level)
147 THEN
148 FND_LOG.STRING ( ln_proc_level
149 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
150 , 'Enter procedure'
151 );
152 FND_LOG.STRING ( ln_proc_level
153 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
154 , 'pn_invoice_id ' || pn_invoice_id
155 );
156 END IF;
157
158 OPEN get_dist_line_number_cur;
159 FETCH get_dist_line_number_cur
160 INTO
161 ln_line_number;
162 CLOSE get_dist_line_number_cur;
163
164 OPEN get_dist_line_number_lines_cur; /*Added by nprashar for bug # 9341898*/
165 FETCH get_dist_line_number_lines_cur INTO v_num;
166 CLOSE get_dist_line_number_lines_cur;
167
168 --log for debug
169 IF ( ln_proc_level >= ln_dbg_level)
170 THEN
171
172 FND_LOG.STRING ( ln_proc_level
173 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
174 , 'ln_line_number ' || NVL(ln_line_number,-99)
175 );
176
177 FND_LOG.STRING ( ln_proc_level
178 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
179 , 'Exit Function'
180 );
181 END IF; --( ln_proc_level >= ln_dbg_level )
182
183 /* IF (ln_line_number IS NOT NULL)
184 THEN
185 RETURN TRUE;
186 ELSE
187 RETURN FALSE;
188 END IF;
189 */
190 --rollback to original logic
191 /*Code Added by nprashar for bug # 9341898*/
192 /*commented by vkaranam for bug#9775984
193 IF ln_line_number IS NOT NULL and v_num IS NOT NULL
194 THEN
195 RETURN TRUE;
196 ELSIF ln_line_number IS NULL and v_num IS NULL
197 THEN
198 RETURN TRUE;
199 ELSE
200 RETURN FALSE;
201 END IF; /*Ends Here*
202 **/
203
204 --start addiitons for bug#9775984
205 /**miscellaneous lines can be generated ,if either the default ccid at the line lvel
206 or distribution for the line exists*/
207 IF ln_line_number IS NULL OR v_num IS NOT NULL
208 then
209 return true;
210 else
211 return false;
212 end if;
213
214 --end addiitons for bug#9775984
215
216
217
218 /* Commented by nprashar for bug # 9341898
219 IF (ln_line_number IS NULL)
220 THEN
221 RETURN TRUE;
222 ELSE
223 RETURN FALSE;
224 END IF; */
225
226 EXCEPTION
227 WHEN OTHERS THEN
228 IF ( ln_proc_level >= ln_dbg_level)
229 THEN
230 FND_LOG.STRING ( ln_proc_level
231 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
232 || '. Other_Exception '
233 , SQLCODE || ':' || SQLERRM
234 );
235 END IF; --( ln_proc_level >= ln_dbg_level) ;
236
237 RETURN FALSE;
238 END Validate_Item_Dist_Lines;
239
240 -- Added by Chong.Lei for POT code port begin
241 --==========================================================================
242 -- FUNCTION NAME:
243 --
244 -- Validate_Prepayment_Invoice Private
245 -- As the defualt service tax should not be created for Prepayment of
246 -- Ap invoice, add this function to avoid the tax generation.
247 -- DESCRIPTION:
248 --
249 --
250 --
251 -- PARAMETERS:
252 -- In: pn_invoice_id number
253 --
254 --
255 -- Out:
256 --
257 --
258 -- DESIGN REFERENCES:
259 --
260 --
261 -- CHANGE HISTORY:
262 -- Sep-09-2011 Eric Ma Added for Bug 12943892
263 ------------------------------------------------------------------------------
264 FUNCTION Validate_Prepayment_Invoice ( pn_invoice_id IN NUMBER)
265 RETURN BOOLEAN
266 IS
267
268 CURSOR get_invoice_id_cur IS
269 SELECT
270 aila.invoice_id
271 FROM
272 AP_INVOICES_ALL aila
273 WHERE aila.invoice_id = pn_invoice_id
274 AND aila.invoice_type_lookup_code ='PREPAYMENT';
275
276 ln_invoice_id AP_INVOICES_ALL.invoice_id%TYPE;
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) := 'Validate_Prepayment_Invoice';
280 BEGIN
281
282 IF ( ln_proc_level >= ln_dbg_level)
283 THEN
284 FND_LOG.STRING ( ln_proc_level
285 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
286 , 'Enter procedure'
287 );
288 FND_LOG.STRING ( ln_proc_level
289 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
290 , 'pn_invoice_id ' || pn_invoice_id
291 );
292 END IF;
293
294 OPEN get_invoice_id_cur;
295 FETCH get_invoice_id_cur
296 INTO ln_invoice_id;
297 CLOSE get_invoice_id_cur;
298
299 IF ( ln_proc_level >= ln_dbg_level)
300 THEN
301
302 FND_LOG.STRING ( ln_proc_level
303 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
304 , 'ln_invoice_id ' || NVL(ln_invoice_id,-99)
305 );
306
307 FND_LOG.STRING ( ln_proc_level
308 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
309 , 'Exit Function'
310 );
311 END IF; --( ln_proc_level >= ln_dbg_level )
312
313 IF (ln_invoice_id IS NOT NULL)
314 THEN
315 RETURN TRUE;
316 ELSE
317 RETURN FALSE;
318 END IF;
319
320 RETURN FALSE;
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 IF ( ln_proc_level >= ln_dbg_level)
325 THEN
326 FND_LOG.STRING ( ln_proc_level
327 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
328 || '. Other_Exception '
329 , SQLCODE || ':' || SQLERRM
330 );
331 END IF; --( ln_proc_level >= ln_dbg_level) ;
332
333 RETURN FALSE;
334 END Validate_Prepayment_Invoice;
335 -- Added by Chong.Lei for POT code port end
336
337 --==========================================================================
338 -- FUNCTION NAME:
339 --
340 -- Validate_3rd_party_cm_Invoice Private
341 -- As the defualt service tax should not be created for CM of
342 -- 3rd invoice, add this function to avoid the tax generation.
343 -- DESCRIPTION:
344 --
345 --
346 -- PARAMETERS:
347 -- In: pn_invoice_id IN NUMBER invoice id
348 --
349 -- Out: RETURN BOOLEAN
350 --
351 -- PRE-COND : invoice item line and tax lines exist
352 -- EXCEPTIONS:
353 --
354 --===========================================================================
355 FUNCTION Validate_3rd_party_cm_Invoice ( pn_invoice_id IN NUMBER)
356 RETURN BOOLEAN
357 IS
358
359 CURSOR get_invoice_id_cur IS
360 SELECT
361 aila.invoice_id
362 FROM
363 AP_INVOICES_ALL aila
364 WHERE aila.invoice_id = pn_invoice_id
365 AND aila.INVOICE_NUM LIKE 'ITP-CM/%'
366 AND aila.description LIKE 'Credit Memo for inclusive 3rd party taxes for receipt%'
367 AND aila.SOURCE = 'INDIA TAX INVOICE'
368 AND aila.invoice_type_lookup_code ='CREDIT';
369
370 ln_invoice_id AP_INVOICES_ALL.invoice_id%TYPE;
371 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
372 ln_proc_level NUMBER := FND_LOG.level_procedure;
373 lv_proc_name VARCHAR2 (100) := 'Validate_3rd_party_cm_Invoice';
374 BEGIN
375
376 IF ( ln_proc_level >= ln_dbg_level)
377 THEN
378 FND_LOG.STRING ( ln_proc_level
379 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
380 , 'Enter procedure'
381 );
382 FND_LOG.STRING ( ln_proc_level
383 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
384 , 'pn_invoice_id ' || pn_invoice_id
385 );
386 END IF;
387
388 OPEN get_invoice_id_cur;
389 FETCH get_invoice_id_cur
390 INTO ln_invoice_id;
391 CLOSE get_invoice_id_cur;
392
393 IF ( ln_proc_level >= ln_dbg_level)
394 THEN
395
396 FND_LOG.STRING ( ln_proc_level
397 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
398 , 'ln_invoice_id ' || NVL(ln_invoice_id,-99)
399 );
400
401 FND_LOG.STRING ( ln_proc_level
402 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
403 , 'Exit Function'
404 );
405 END IF; --( ln_proc_level >= ln_dbg_level )
406
407 IF (ln_invoice_id IS NOT NULL)
408 THEN
409 RETURN TRUE;
410 ELSE
411 RETURN FALSE;
412 END IF;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 IF ( ln_proc_level >= ln_dbg_level)
417 THEN
418 FND_LOG.STRING ( ln_proc_level
419 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
420 || '. Other_Exception '
421 , SQLCODE || ':' || SQLERRM
422 );
423 END IF; --( ln_proc_level >= ln_dbg_level) ;
424
425 RETURN FALSE;
426 END Validate_3rd_party_cm_Invoice;
427
428
429 --==========================================================================
430 -- PROCEDURE NAME:
431 --
432 -- Insert_Tax_Distribution_Lines Private
433 --
434 -- DESCRIPTION:
435 -- Insert tax distribution lines. The allocation numbers of tax line should
436 -- be same as the numbers of coressponding item lines
437 --
438 -- PARAMETERS:
439 -- In: pn_invoice_id IN NUMBER invoice id
440 -- pn_invoice_line_number IN NUMBER line number
441 -- pn_item_allocation_number IN NUMBER item line allocation numbers
442 -- pn_tax_allocation_number IN NUMBER tax line allocation numbers
443 -- Out:
444 --
445 -- PRE-COND : invoice item line and tax lines exist
446 -- EXCEPTIONS:
447 --
448 --===========================================================================
449
450
451 PROCEDURE Insert_Tax_Distribution_Lines
452 ( pn_invoice_id IN NUMBER
453 , pn_invoice_line_number IN NUMBER
454 , pn_item_allocation_number IN NUMBER
455 , pn_tax_allocation_number IN NUMBER
456 )
457 IS
458
459 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
460 ln_proc_level NUMBER := FND_LOG.level_procedure;
461 lv_proc_name VARCHAR2 (100) := 'Insert_Tax_Distribution_Lines';
462 BEGIN
463
464 --log for debug
465 IF ( ln_proc_level >= ln_dbg_level)
466 THEN
467 FND_LOG.STRING ( ln_proc_level
468 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
469 , 'Enter procedure'
470 );
471 FND_LOG.STRING ( ln_proc_level
472 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
473 , 'pn_invoice_id ' || pn_invoice_id
474 );
475 FND_LOG.STRING ( ln_proc_level
476 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
477 , 'pn_invoice_line_number ' || pn_invoice_line_number
478 );
479 FND_LOG.STRING ( ln_proc_level
480 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
481 , 'pn_item_allocation_number ' || pn_item_allocation_number
482 );
483 FND_LOG.STRING ( ln_proc_level
484 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
485 , 'pn_tax_allocation_number ' || pn_tax_allocation_number
486 );
487 END IF;
488
489 FOR i IN (pn_tax_allocation_number+1) .. pn_item_allocation_number
490 LOOP
491 INSERT INTO AP_INVOICE_DISTRIBUTIONS_ALL
492 ( accounting_date
493 , accrual_posted_flag
494 , assets_addition_flag
495 , assets_tracking_flag
496 , cash_posted_flag
497 , distribution_line_number
498 , dist_code_combination_id
499 , invoice_id
500 , last_updated_by
501 , last_update_date
502 , line_type_lookup_code
503 , period_name
504 , set_of_books_id
505 , accts_pay_code_combination_id
506 , amount
507 , base_amount
508 , base_invoice_price_variance
509 , batch_id
510 , created_by
511 , creation_date
512 , description
513 , exchange_rate_variance
514 , final_match_flag
515 , income_tax_region
516 , invoice_price_variance
517 , last_update_login
518 , match_status_flag
519 , posted_flag
520 , po_distribution_id
521 , program_application_id
522 , program_id
523 , program_update_date
524 , quantity_invoiced
525 , rate_var_code_combination_id
526 , request_id
527 , reversal_flag
528 , type_1099
529 , unit_price
530 , amount_encumbered
531 , base_amount_encumbered
532 , encumbered_flag
533 , exchange_date
534 , exchange_rate
535 , exchange_rate_type
536 , price_adjustment_flag
537 , price_var_code_combination_id
538 , quantity_unencumbered
539 , stat_amount
540 , amount_to_post
541 , attribute1
542 , attribute10
543 , attribute11
544 , attribute12
545 , attribute13
546 , attribute14
547 , attribute15
548 , attribute2
549 , attribute3
550 , attribute4
551 , attribute5
552 , attribute6
553 , attribute7
554 , attribute8
555 , attribute9
556 , attribute_category
557 , base_amount_to_post
558 , cash_je_batch_id
559 , expenditure_item_date
560 , expenditure_organization_id
561 , expenditure_type
562 , je_batch_id
563 , parent_invoice_id
564 , pa_addition_flag
565 , pa_quantity
566 , posted_amount
567 , posted_base_amount
568 , prepay_amount_remaining
569 , project_accounting_context
570 , project_id
571 , task_id
572 , ussgl_transaction_code
573 , ussgl_trx_code_context
574 , earliest_settlement_date
575 , req_distribution_id
576 , quantity_variance
577 , base_quantity_variance
578 , packet_id
579 , awt_flag
580 , awt_group_id
581 , awt_tax_rate_id
582 , awt_gross_amount
583 , awt_invoice_id
584 , awt_origin_group_id
585 , reference_1
586 , reference_2
587 , org_id
588 , other_invoice_id
589 , awt_invoice_payment_id
590 , global_attribute_category
591 , global_attribute1
592 , global_attribute2
593 , global_attribute3
594 , global_attribute4
595 , global_attribute5
596 , global_attribute6
597 , global_attribute7
598 , global_attribute8
599 , global_attribute9
600 , global_attribute10
601 , global_attribute11
602 , global_attribute12
603 , global_attribute13
604 , global_attribute14
605 , global_attribute15
606 , global_attribute16
607 , global_attribute17
608 , global_attribute18
609 , global_attribute19
610 , global_attribute20
611 , line_group_number
612 , receipt_verified_flag
613 , receipt_required_flag
614 , receipt_missing_flag
615 , justification
616 , expense_group
617 , start_expense_date
618 , end_expense_date
619 , receipt_currency_code
620 , receipt_conversion_rate
621 , receipt_currency_amount
622 , daily_amount
623 , web_parameter_id
624 , adjustment_reason
625 , award_id
626 , mrc_accrual_posted_flag
627 , mrc_cash_posted_flag
628 , mrc_dist_code_combination_id
629 , mrc_amount
630 , mrc_base_amount
631 , mrc_base_inv_price_variance
632 , mrc_exchange_rate_variance
633 , mrc_posted_flag
634 , mrc_program_application_id
635 , mrc_program_id
636 , mrc_program_update_date
637 , mrc_rate_var_ccid
638 , mrc_request_id
639 , mrc_exchange_date
640 , mrc_exchange_rate
641 , mrc_exchange_rate_type
642 , mrc_amount_to_post
643 , mrc_base_amount_to_post
644 , mrc_cash_je_batch_id
645 , mrc_je_batch_id
646 , mrc_posted_amount
647 , mrc_posted_base_amount
648 , mrc_receipt_conversion_rate
649 , credit_card_trx_id
650 , dist_match_type
651 , rcv_transaction_id
652 , invoice_distribution_id
653 , parent_reversal_id
654 , tax_recoverable_flag
655 , pa_cc_ar_invoice_id
656 , pa_cc_ar_invoice_line_num
657 , pa_cc_processed_code
658 , merchant_document_number
659 , merchant_name
660 , merchant_reference
661 , merchant_tax_reg_number
662 , merchant_taxpayer_id
663 , country_of_supply
664 , matched_uom_lookup_code
665 , gms_burdenable_raw_cost
666 , accounting_event_id
667 , prepay_distribution_id
668 , upgrade_posted_amt
669 , upgrade_base_posted_amt
670 , inventory_transfer_status
671 , company_prepaid_invoice_id
672 , cc_reversal_flag
673 , awt_withheld_amt
674 , invoice_includes_prepay_flag
675 , price_correct_inv_id
676 , price_correct_qty
677 , pa_cmt_xface_flag
678 , cancellation_flag
679 , invoice_line_number
680 , corrected_invoice_dist_id
681 , rounding_amt
682 , charge_applicable_to_dist_id
683 , corrected_quantity
684 , related_id
685 , asset_book_type_code
686 , asset_category_id
687 , distribution_class
688 , final_payment_rounding
689 , final_application_rounding
690 , amount_at_prepay_xrate
691 , cash_basis_final_app_rounding
692 , amount_at_prepay_pay_xrate
693 , intended_use
694 , detail_tax_dist_id
695 , rec_nrec_rate
696 , recovery_rate_id
697 , recovery_rate_name
698 , recovery_type_code
699 , recovery_rate_code
700 , withholding_tax_code_id
701 , tax_already_distributed_flag
702 , summary_tax_line_id
703 , taxable_amount
707 , tax_code_id
704 , taxable_base_amount
705 , extra_po_erv
706 , prepay_tax_diff_amount
708 , vat_code
709 , amount_includes_tax_flag
710 , tax_calculated_flag
711 , tax_recovery_rate
712 , tax_recovery_override_flag
713 , tax_code_override_flag
714 , total_dist_amount
715 , total_dist_base_amount
716 , prepay_tax_parent_id
717 , cancelled_flag
718 , old_distribution_id
719 , old_dist_line_number
720 , amount_variance
721 , base_amount_variance
722 , historical_flag
723 , rcv_charge_addition_flag
724 , awt_related_id
725 , related_retainage_dist_id
726 , retained_amount_remaining
727 , bc_event_id
728 , retained_invoice_dist_id
729 , final_release_rounding
730 , fully_paid_acctd_flag
731 , root_distribution_id
732 , xinv_parent_reversal_id
733 , recurring_payment_id
734 , release_inv_dist_derived_from
735 )
736 SELECT
737 accounting_date
738 , accrual_posted_flag
739 , assets_addition_flag
740 , assets_tracking_flag
741 , cash_posted_flag
742 , i --distribution_line_number
743 , dist_code_combination_id
744 , invoice_id
745 , last_updated_by
746 , last_update_date
747 , line_type_lookup_code
748 , period_name
749 , set_of_books_id
750 , accts_pay_code_combination_id
751 , amount
752 , base_amount
753 , base_invoice_price_variance
754 , batch_id
755 , created_by
756 , creation_date
757 , description
758 , exchange_rate_variance
759 , final_match_flag
760 , income_tax_region
761 , invoice_price_variance
762 , last_update_login
763 , match_status_flag
764 , posted_flag
765 , po_distribution_id
766 , program_application_id
767 , program_id
768 , program_update_date
769 , quantity_invoiced
770 , rate_var_code_combination_id
771 , request_id
772 , reversal_flag
773 , type_1099
774 , unit_price
775 , amount_encumbered
776 , base_amount_encumbered
777 , encumbered_flag
778 , exchange_date
779 , exchange_rate
780 , exchange_rate_type
781 , price_adjustment_flag
782 , price_var_code_combination_id
783 , quantity_unencumbered
784 , stat_amount
785 , amount_to_post
786 , attribute1
787 , attribute10
788 , attribute11
789 , attribute12
790 , attribute13
791 , attribute14
792 , attribute15
793 , attribute2
794 , attribute3
795 , attribute4
796 , attribute5
797 , attribute6
798 , attribute7
799 , attribute8
800 , attribute9
801 , attribute_category
802 , base_amount_to_post
803 , cash_je_batch_id
804 , expenditure_item_date
805 , expenditure_organization_id
806 , expenditure_type
807 , je_batch_id
808 , parent_invoice_id
809 , pa_addition_flag
810 , pa_quantity
811 , posted_amount
812 , posted_base_amount
813 , prepay_amount_remaining
814 , project_accounting_context
815 , project_id
816 , task_id
817 , ussgl_transaction_code
818 , ussgl_trx_code_context
819 , earliest_settlement_date
820 , req_distribution_id
821 , quantity_variance
822 , base_quantity_variance
823 , packet_id
824 , awt_flag
825 , awt_group_id
826 , awt_tax_rate_id
827 , awt_gross_amount
828 , awt_invoice_id
829 , awt_origin_group_id
830 , reference_1
831 , reference_2
832 , org_id
833 , other_invoice_id
834 , awt_invoice_payment_id
835 , global_attribute_category
836 , global_attribute1
837 , global_attribute2
838 , global_attribute3
839 , global_attribute4
840 , global_attribute5
841 , global_attribute6
842 , global_attribute7
843 , global_attribute8
844 , global_attribute9
845 , global_attribute10
846 , global_attribute11
847 , global_attribute12
848 , global_attribute13
849 , global_attribute14
850 , global_attribute15
851 , global_attribute16
852 , global_attribute17
853 , global_attribute18
854 , global_attribute19
855 , global_attribute20
856 , line_group_number
857 , receipt_verified_flag
858 , receipt_required_flag
859 , receipt_missing_flag
860 , justification
861 , expense_group
862 , start_expense_date
863 , end_expense_date
864 , receipt_currency_code
865 , receipt_conversion_rate
866 , receipt_currency_amount
867 , daily_amount
868 , web_parameter_id
869 , adjustment_reason
870 , award_id
871 , mrc_accrual_posted_flag
872 , mrc_cash_posted_flag
873 , mrc_dist_code_combination_id
874 , mrc_amount
875 , mrc_base_amount
876 , mrc_base_inv_price_variance
877 , mrc_exchange_rate_variance
878 , mrc_posted_flag
879 , mrc_program_application_id
880 , mrc_program_id
881 , mrc_program_update_date
882 , mrc_rate_var_ccid
883 , mrc_request_id
884 , mrc_exchange_date
885 , mrc_exchange_rate
886 , mrc_exchange_rate_type
887 , mrc_amount_to_post
888 , mrc_base_amount_to_post
889 , mrc_cash_je_batch_id
890 , mrc_je_batch_id
891 , mrc_posted_amount
892 , mrc_posted_base_amount
893 , mrc_receipt_conversion_rate
894 , credit_card_trx_id
895 , dist_match_type
896 , rcv_transaction_id
897 , ap_invoice_distributions_s.NEXTVAL --invoice_distribution_id
898 , parent_reversal_id
899 , tax_recoverable_flag
900 , pa_cc_ar_invoice_id
901 , pa_cc_ar_invoice_line_num
902 , pa_cc_processed_code
903 , merchant_document_number
904 , merchant_name
905 , merchant_reference
906 , merchant_tax_reg_number
907 , merchant_taxpayer_id
908 , country_of_supply
909 , matched_uom_lookup_code
910 , gms_burdenable_raw_cost
911 , accounting_event_id
912 , prepay_distribution_id
913 , upgrade_posted_amt
914 , upgrade_base_posted_amt
915 , inventory_transfer_status
916 , company_prepaid_invoice_id
917 , cc_reversal_flag
918 , awt_withheld_amt
919 , invoice_includes_prepay_flag
920 , price_correct_inv_id
921 , price_correct_qty
922 , pa_cmt_xface_flag
923 , cancellation_flag
924 , invoice_line_number
925 , corrected_invoice_dist_id
926 , rounding_amt
927 , charge_applicable_to_dist_id
928 , corrected_quantity
929 , related_id
930 , asset_book_type_code
931 , asset_category_id
932 , distribution_class
933 , final_payment_rounding
934 , final_application_rounding
935 , amount_at_prepay_xrate
936 , cash_basis_final_app_rounding
937 , amount_at_prepay_pay_xrate
938 , intended_use
939 , detail_tax_dist_id
940 , rec_nrec_rate
941 , recovery_rate_id
942 , recovery_rate_name
943 , recovery_type_code
944 , recovery_rate_code
945 , withholding_tax_code_id
946 , tax_already_distributed_flag
947 , summary_tax_line_id
948 , taxable_amount
949 , taxable_base_amount
950 , extra_po_erv
951 , prepay_tax_diff_amount
952 , tax_code_id
953 , vat_code
954 , amount_includes_tax_flag
955 , tax_calculated_flag
956 , tax_recovery_rate
957 , tax_recovery_override_flag
958 , tax_code_override_flag
959 , total_dist_amount
960 , total_dist_base_amount
961 , prepay_tax_parent_id
962 , cancelled_flag
963 , old_distribution_id
964 , old_dist_line_number
965 , amount_variance
966 , base_amount_variance
967 , historical_flag
968 , rcv_charge_addition_flag
969 , awt_related_id
970 , related_retainage_dist_id
971 , retained_amount_remaining
972 , bc_event_id
973 , retained_invoice_dist_id
974 , final_release_rounding
975 , fully_paid_acctd_flag
976 , root_distribution_id
977 , xinv_parent_reversal_id
978 , recurring_payment_id
979 , release_inv_dist_derived_from
980 FROM
981 ap_invoice_distributions_all
982 WHERE invoice_id = pn_invoice_id
983 AND invoice_line_number = pn_invoice_line_number
984 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
985 AND distribution_line_number = (select min(distribution_line_number) from ap_invoice_distributions_all where
986 invoice_id = pn_invoice_id
987 AND invoice_line_number = pn_invoice_line_number); /* Bug 13617527 */
988 END LOOP; --(i IN (pn_tax_allocation_number+1) .. pn_item_allocation_number)
989
990 IF ( ln_proc_level >= ln_dbg_level)
991 THEN
992 FND_LOG.STRING ( ln_proc_level
993 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
994 , 'Exit procedure'
995 );
996 END IF; --( ln_proc_level >= ln_dbg_level )
997 EXCEPTION
998 WHEN OTHERS THEN
999 IF ( ln_proc_level >= ln_dbg_level)
1000 THEN
1001 FND_LOG.STRING ( ln_proc_level
1002 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
1003 || '. Other_Exception '
1004 , SQLCODE || ':' || SQLERRM
1005 );
1006 END IF; --( ln_proc_level >= ln_dbg_level) ;
1007
1008 END Insert_Tax_Distribution_Lines;
1009
1010 --==========================================================================
1011 -- PROCEDURE NAME:
1012 --
1013 -- Delete_Tax_Distribution_Lines Private
1014 --
1015 -- DESCRIPTION:
1016 -- Insert tax distribution lines. The allocation numbers of tax line should
1017 -- be same as the numbers of coressponding item lines
1018 --
1019 -- PARAMETERS:
1020 -- In: pn_invoice_id IN NUMBER invoice id
1021 -- pn_invoice_line_number IN NUMBER line number
1022 -- pn_item_allocation_number IN NUMBER item line allocation numbers
1023 -- pn_tax_allocation_number IN NUMBER tax line allocation numbers
1024 -- Out:
1025 --
1026 -- PRE-COND : invoice item line and tax lines exist
1027 -- EXCEPTIONS:
1028 --
1029 --===========================================================================
1030 PROCEDURE Delete_Tax_Distribution_Lines
1031 ( pn_invoice_id IN NUMBER
1032 , pn_invoice_line_number IN NUMBER
1033 , pn_item_allocation_number IN NUMBER
1034 )
1035 IS
1036
1037 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1038 ln_proc_level NUMBER := FND_LOG.level_procedure;
1039 lv_proc_name VARCHAR2 (100) := 'Delete_Tax_Distribution_Lines';
1040 BEGIN
1041 --log for debug
1042 IF ( ln_proc_level >= ln_dbg_level)
1043 THEN
1044 FND_LOG.STRING ( ln_proc_level
1045 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
1046 , 'Enter procedure'
1047 );
1048 FND_LOG.STRING ( ln_proc_level
1049 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1050 , 'pn_invoice_id ' || pn_invoice_id
1051 );
1052 FND_LOG.STRING ( ln_proc_level
1053 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1054 , 'pn_invoice_line_number ' || pn_invoice_line_number
1055 );
1056 FND_LOG.STRING ( ln_proc_level
1057 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1058 , 'pn_item_allocation_number ' || pn_item_allocation_number
1059 );
1060 END IF;
1061
1062 DELETE
1063 FROM
1064 ap_invoice_distributions_all
1065 WHERE invoice_id = pn_invoice_id
1066 AND invoice_line_number = pn_invoice_line_number
1067 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
1068 AND distribution_line_number > pn_item_allocation_number;
1069
1070 IF ( ln_proc_level >= ln_dbg_level)
1071 THEN
1072 FND_LOG.STRING ( ln_proc_level
1073 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
1074 , 'Exit procedure'
1075 );
1076
1077 FND_LOG.STRING ( ln_proc_level
1078 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
1079 , 'Exit procedure'
1080 );
1081 END IF; --( ln_proc_level >= ln_dbg_level )
1082
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 IF ( ln_proc_level >= ln_dbg_level)
1086 THEN
1087 FND_LOG.STRING ( ln_proc_level
1088 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
1089 || '. Other_Exception '
1090 , SQLCODE || ':' || SQLERRM
1091 );
1092 END IF; --( ln_proc_level >= ln_dbg_level) ;
1093 END Delete_Tax_Distribution_Lines;
1094
1095
1096 --==========================================================================
1097 -- PROCEDURE NAME:
1098 --
1099 -- Allocate_Tax_Dist_Lines Private
1100 --
1101 -- DESCRIPTION:
1102 -- Insert tax distribution lines. The allocation numbers of tax line should
1103 -- be same as the numbers of coressponding item lines
1104 --
1105 -- PARAMETERS:
1106 -- In: pn_invoice_id IN NUMBER invoice id
1107 -- pn_invoice_item_line_number IN NUMBER line number
1108 -- Out:
1109 --
1110 -- PRE-COND : invoice item line and tax lines exist
1111 -- EXCEPTIONS:
1112 --
1113 --Changed History:
1114 --09-July-2012 amandali for bug 14218386
1115 -- Issue:INVOICE ACCOUNTING FOR ANY OTHER TAX TYPE NOT COMING CORRECTLY
1116 -- Fix: In the second loop of tax_line_rec, added a call to the cursor get_tax_cur to fetch the tax details
1117 --===========================================================================
1118
1119 PROCEDURE Allocate_Tax_Dist_Lines
1120 ( pn_invoice_id IN NUMBER
1121 , pn_invoice_item_line_number IN NUMBER
1122 )
1123 IS
1127
1124 ln_invoice_id NUMBER := pn_invoice_id;
1125 ln_invoice_item_line_number NUMBER := pn_invoice_item_line_number;
1126
1128 CURSOR get_tax_cur (pn_tax_id NUMBER)
1129 IS
1130 SELECT
1131 tax_name
1132 , tax_account_id
1133 , mod_cr_percentage
1134 , adhoc_flag
1135 , NVL (tax_rate, -1) tax_rate
1136 , tax_type
1137 , NVL(rounding_factor,0) rounding_factor
1138 FROM
1139 jai_cmn_taxes_all
1140 WHERE tax_id = pn_tax_id;
1141
1142 CURSOR item_line_cur IS
1143 SELECT
1144 line_number
1145 , amount
1146 FROM
1147 ap_invoice_lines_all
1148 WHERE invoice_id = ln_invoice_id
1149 AND line_number = NVL(ln_invoice_item_line_number,line_number)
1150 AND line_type_lookup_code = GV_CONSTANT_ITEM;
1151
1152 CURSOR invoice_dist_line_cur (pn_invoice_item_ln_number NUMBER)
1153 IS
1154 SELECT
1155 amount
1156 , dist_code_combination_id
1157 , assets_tracking_flag
1158 , assets_addition_flag
1159 , project_id
1160 , task_id
1161 , expenditure_type
1162 , pa_addition_flag
1163 , ASSET_BOOK_TYPE_CODE
1164 , ASSET_CATEGORY_ID
1165 FROM
1166 ap_invoice_distributions_all
1167 WHERE invoice_id = ln_invoice_id
1168 AND invoice_line_number = pn_invoice_item_ln_number;
1169
1170 CURSOR tax_line_cur (pn_invoice_item_ln_number NUMBER)
1171 IS
1172 SELECT
1173 jail.invoice_line_number invoice_line_number
1174 , jail.line_amount line_amount
1175 , jcdt.tax_id tax_id
1176 , NVL(jcdt.modvat_flag,'N') modvat_flag
1177 , aila.base_amount base_amount
1178 FROM
1179 jai_ap_invoice_lines jail
1180 , jai_cmn_document_taxes jcdt
1181 , ap_invoice_lines_all aila
1182 WHERE jcdt.source_doc_id = jail.invoice_id
1183 AND jcdt.source_doc_line_id = jail.invoice_line_number
1184 AND aila.invoice_id = jail.invoice_id
1185 AND aila.line_number = jail.invoice_line_number
1186 AND jail.parent_invoice_line_number = pn_invoice_item_ln_number
1187 AND jail.line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
1188 AND jail.invoice_id = ln_invoice_id
1189 AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice --Added by eric on Jan 29,2008
1190 ORDER BY jail.invoice_line_number;
1191
1192 CURSOR get_allocation_numbers_cur (pn_invoice_line_number NUMBER)
1193 IS
1194 SELECT
1195 COUNT(1)
1196 FROM
1197 ap_invoice_distributions_all
1198 WHERE invoice_id = ln_invoice_id
1199 AND invoice_line_number = pn_invoice_line_number;
1200
1201 CURSOR get_dist_total_amount_cur
1202 ( pn_invoice_line_number NUMBER
1203 , pn_dist_line_number NUMBER
1204 )
1205 IS
1206 SELECT
1207 SUM( amount )
1208 , SUM( base_amount )
1209 FROM
1210 ap_invoice_distributions_all
1211 WHERE invoice_id = ln_invoice_id
1212 AND invoice_line_number = pn_invoice_line_number
1213 AND distribution_line_number < pn_dist_line_number;
1214
1215 ln_item_allocation_number NUMBER ;
1216 ln_tax_allocation_number NUMBER ;
1217 ln_allocation_factor NUMBER ;
1218 ln_loop_counter NUMBER ;
1219 tax_rec get_tax_cur%ROWTYPE;
1220 ln_dist_total_amount NUMBER;
1221 ln_dist_total_base_amount NUMBER;
1222
1223 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1224 ln_proc_level NUMBER := FND_LOG.level_procedure;
1225 lv_proc_name VARCHAR2 (100) := 'Allocate_Tax_Dist_Lines';
1226 BEGIN
1227 --log for debug
1228 IF ( ln_proc_level >= ln_dbg_level)
1229 THEN
1230 FND_LOG.STRING ( ln_proc_level
1231 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
1232 , 'Enter procedure'
1233 );
1234 FND_LOG.STRING ( ln_proc_level
1235 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1236 , 'pn_invoice_id ' || pn_invoice_id
1237 );
1238 FND_LOG.STRING ( ln_proc_level
1239 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1240 , 'pn_invoice_item_line_number '
1241 || pn_invoice_item_line_number
1242 );
1243 END IF;
1244
1245 FOR item_line_rec IN item_line_cur
1246 LOOP
1247 --initialize the allocation variables
1248 ln_loop_counter :=0;
1249
1250 --Get item allocation total numbers
1251 OPEN get_allocation_numbers_cur (item_line_rec.line_number);
1252 FETCH get_allocation_numbers_cur
1253 INTO
1254 ln_item_allocation_number;
1255 CLOSE get_allocation_numbers_cur;
1256 --Get item allocation total numbers end
1257
1258 -- make dist line numbers of each nonrec tax line same as the dist
1259 -- line number of its corresponding item line,sync dist line numbers
1260 FOR tax_line_rec IN tax_line_cur (item_line_rec.line_number)
1261 LOOP
1262
1263 --Get item allocation total numbers
1264 OPEN get_allocation_numbers_cur (tax_line_rec.invoice_line_number);
1265 FETCH get_allocation_numbers_cur
1266 INTO
1267 ln_tax_allocation_number;
1268 CLOSE get_allocation_numbers_cur;
1269 --Get item allocation total numbers end
1270
1271 --get tax definition parameters
1272 OPEN get_tax_cur (tax_line_rec.tax_id);
1273 FETCH get_tax_cur
1274 INTO
1275 tax_rec;
1276 CLOSE get_tax_cur;
1277 --get tax definition parameters end
1278
1279 --for non recoverable tax lines distribution numbers should
1280 --be same as the dist line numbers of its item line
1281 IF (NVL (tax_line_rec.modvat_flag, 'N') = jai_constants.no
1282 OR NVL (tax_rec.mod_cr_percentage, -1) <= 0
1283 )
1284 THEN
1285 -- if item dist line number > dist line numbers of current tax line
1286 -- ,insert tax lines.
1287 IF (ln_item_allocation_number >ln_tax_allocation_number)
1288 THEN
1289 insert_tax_distribution_lines
1290 ( pn_invoice_id => ln_invoice_id
1291 , pn_invoice_line_number => tax_line_rec.invoice_line_number
1292 , pn_item_allocation_number => ln_item_allocation_number
1293 , pn_tax_allocation_number => ln_tax_allocation_number
1294 );
1295 ELSIF(ln_item_allocation_number <ln_tax_allocation_number)
1296 THEN
1297 -- if item dist line number < dist line numbers of current tax line
1298 -- ,delete tax lines.
1299 delete_tax_distribution_lines
1300 ( pn_invoice_id => ln_invoice_id
1301 , pn_invoice_line_number => tax_line_rec.invoice_line_number
1302 , pn_item_allocation_number => ln_item_allocation_number
1303 );
1304 END IF;
1305 ELSE --(recoverable tax)
1306 --no requirement of taking any action for the recoverable tax
1307 NULL;
1308 END IF;--(non recoverable)
1309 END LOOP; --(tax_line_rec IN tax_line_cur,sync dist line numbers end)
1310
1311 --item distribution lines loop
1312 FOR item_dist_line_rec IN invoice_dist_line_cur(item_line_rec.line_number)
1313 LOOP
1314 ln_loop_counter :=ln_loop_counter + 1;
1315
1316 ln_allocation_factor :=
1317 item_dist_line_rec.amount/item_line_rec.amount;
1318
1319 FOR tax_line_rec IN tax_line_cur (item_line_rec.line_number)
1320 LOOP
1321 /* Bug 14218386 -start*/
1322 OPEN get_tax_cur (tax_line_rec.tax_id);
1323 FETCH get_tax_cur
1324 INTO
1325 tax_rec;
1326 CLOSE get_tax_cur;
1327 /* Bug 14218386 -end */
1328 IF (NVL (tax_line_rec.modvat_flag, 'N') = jai_constants.no
1329 OR NVL (tax_rec.mod_cr_percentage, -1) <= 0
1330 )
1331 THEN
1332 IF (ln_item_allocation_number >1)
1333 THEN
1334 IF (ln_loop_counter < ln_item_allocation_number )
1335 THEN
1336 --get tax definition parameters
1337 OPEN get_tax_cur (tax_line_rec.tax_id);
1338 FETCH get_tax_cur
1339 INTO
1340 tax_rec;
1341 CLOSE get_tax_cur;
1342
1343 --allocation tax amount according to the proportion
1344 --of item dist lines
1345 UPDATE
1346 ap_invoice_distributions_all
1347 SET
1348 amount =tax_line_rec.line_amount * ln_allocation_factor
1349 /*
1350 ROUND( tax_line_rec.line_amount *
1351 ln_allocation_factor,tax_rec.rounding_factor
1352 )
1353 */
1354
1355 , base_amount =tax_line_rec.base_amount *ln_allocation_factor
1356 /*
1357 ROUND( tax_line_rec.base_amount *
1358 ln_allocation_factor,tax_rec.rounding_factor
1359 )
1360 */
1361
1362 , assets_tracking_flag = item_dist_line_rec.assets_tracking_flag
1363 , assets_addition_flag = item_dist_line_rec.assets_addition_flag
1364 , project_id = item_dist_line_rec.project_id
1365 , task_id = item_dist_line_rec.task_id
1366 , expenditure_type = item_dist_line_rec.expenditure_type
1367 , pa_addition_flag = item_dist_line_rec.PA_ADDITION_FLAG
1368 , dist_code_combination_id =
1369 item_dist_line_rec.dist_code_combination_id
1370 , ASSET_BOOK_TYPE_CODE = item_dist_line_rec.ASSET_BOOK_TYPE_CODE
1371 , ASSET_CATEGORY_ID = item_dist_line_rec.ASSET_CATEGORY_ID
1372 WHERE invoice_id = pn_invoice_id
1373 AND invoice_line_number = tax_line_rec.invoice_line_number
1374 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
1375 AND distribution_line_number = ln_loop_counter;
1376 ELSE --(ln_loop_counter = ln_item_allocation_number,last loop)
1377 OPEN get_dist_total_amount_cur
1378 ( tax_line_rec.invoice_line_number
1379 , ln_loop_counter
1380 );
1381 FETCH get_dist_total_amount_cur
1382 INTO
1383 ln_dist_total_amount
1384 , ln_dist_total_base_amount;
1385 CLOSE get_dist_total_amount_cur;
1386
1387 UPDATE
1388 ap_invoice_distributions_all
1389 SET
1390 amount =
1391 tax_line_rec.line_amount - ln_dist_total_amount
1392 , base_amount =
1393 tax_line_rec.base_amount - ln_dist_total_base_amount
1394 , assets_tracking_flag = item_dist_line_rec.assets_tracking_flag
1395 , assets_addition_flag = item_dist_line_rec.assets_addition_flag
1396 , project_id = item_dist_line_rec.project_id
1397 , task_id = item_dist_line_rec.task_id
1398 , expenditure_type = item_dist_line_rec.expenditure_type
1399 , pa_addition_flag = item_dist_line_rec.pa_addition_flag
1400 , dist_code_combination_id =
1401 item_dist_line_rec.dist_code_combination_id
1402 , ASSET_BOOK_TYPE_CODE = item_dist_line_rec.ASSET_BOOK_TYPE_CODE
1403 , ASSET_CATEGORY_ID = item_dist_line_rec.ASSET_CATEGORY_ID
1404 WHERE invoice_id = pn_invoice_id
1405 AND invoice_line_number = tax_line_rec.invoice_line_number
1406 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
1407 AND distribution_line_number = ln_loop_counter;
1408 END IF;-- (ln_loop_counter < ln_item_allocation_number )
1409
1410 ELSE --(ln_item_allocation_number =1)
1411 --As only one item distribution line,tax lines are not
1412 --required to be allocated.
1413
1414 UPDATE
1415 ap_invoice_distributions_all
1416 SET
1417 amount = tax_line_rec.line_amount
1418 , base_amount = tax_line_rec.base_amount
1419 , assets_tracking_flag = item_dist_line_rec.assets_tracking_flag
1420 , assets_addition_flag = item_dist_line_rec.assets_addition_flag
1421 , project_id = item_dist_line_rec.project_id
1422 , task_id = item_dist_line_rec.task_id
1423 , expenditure_type = item_dist_line_rec.expenditure_type
1424 , pa_addition_flag = item_dist_line_rec.pa_addition_flag
1425 , dist_code_combination_id =
1426 item_dist_line_rec.dist_code_combination_id
1427 , ASSET_BOOK_TYPE_CODE = item_dist_line_rec.ASSET_BOOK_TYPE_CODE
1428 , ASSET_CATEGORY_ID = item_dist_line_rec.ASSET_CATEGORY_ID
1429 WHERE invoice_id = pn_invoice_id
1430 AND invoice_line_number = tax_line_rec.invoice_line_number
1431 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS
1432 AND distribution_line_number = ln_loop_counter;
1433 END IF;-- (ln_item_allocation_number >1)
1434 ELSE --(recoverable tax)
1435 --no requirement of taking any action for the recoverable tax
1436 NULL;
1437 END IF;--(non recoverable)
1438 END LOOP;--(item tax lines loop)
1439 END LOOP;--(item distribution lines loop)
1440 END LOOP ; -- (item_line_rec IN item_line_cur,item lines loop)
1441 EXCEPTION
1442 WHEN OTHERS THEN
1443 IF ( ln_proc_level >= ln_dbg_level)
1444 THEN
1445 FND_LOG.STRING ( ln_proc_level
1446 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
1447 || '. Other_Exception '
1448 , SQLCODE || ':' || SQLERRM
1449 );
1450 END IF; --( ln_proc_level >= ln_dbg_level) ;
1451 END Allocate_Tax_Dist_Lines;
1452
1453 --==========================================================================
1454 -- FUNCTION NAME:
1455 --
1456 -- Get_Service_Tax_Flag Private
1457 --
1458 -- DESCRIPTION:
1459 -- With given pv_tax_type to check if it is in Service Tax Regime
1460 -- and return 'Y' if service tax, 'N' if non service tax.
1461 --
1462 --
1463 -- PARAMETERS:
1464 -- In: pv_tax_type IN VARCHAR Tax Type
1465 --
1466 --
1467 -- Out: RETURN VARCHAR2 'Y' or 'N'
1468 --
1469 --
1470 -- PRE-COND :
1471 -- EXCEPTIONS:
1472 --
1473 -- 08-Dec-2010 Created by Xiao for Open Interface ER bug#11683927
1474 --===========================================================================
1475 FUNCTION Get_Service_Tax_Flag(pv_tax_type VARCHAR2)
1476 RETURN VARCHAR2
1477 IS
1478 CURSOR Get_Service_Tax_Flag
1479 IS
1480 SELECT 'Y'
1481 FROM dual
1482 WHERE EXISTS (
1483 SELECT 'X'
1484 FROM jai_rgm_registrations
1485 WHERE registration_type = jai_constants.regn_type_tax_types
1486 AND attribute_code = pv_tax_type
1487 AND regime_id IN ( SELECT regime_id
1488 FROM jai_rgm_definitions
1489 WHERE regime_code = jai_constants.service_regime
1490 )
1491 );
1492
1493
1494 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1495 ln_proc_level NUMBER := FND_LOG.level_procedure;
1496 lv_proc_name VARCHAR2 (100) := 'Get_Service_Tax_Flag';
1497 lv_service_tax_flag VARCHAR2(1);
1498 BEGIN
1499 IF ( ln_proc_level >= ln_dbg_level)
1500 THEN
1501 FND_LOG.STRING ( ln_proc_level
1502 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
1503 , 'Enter procedure'
1504 );
1505 FND_LOG.STRING ( ln_proc_level
1506 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1507 , 'pn_tax_id ' || to_char(pv_tax_type)
1508 );
1509 END IF; --(ln_proc_level >= ln_dbg_level)
1510
1511 OPEN Get_Service_Tax_Flag;
1512 FETCH Get_Service_Tax_Flag INTO lv_service_tax_flag;
1513 CLOSE Get_Service_Tax_Flag;
1514
1515 RETURN nvl(lv_service_tax_flag, 'N');
1516 EXCEPTION
1517 WHEN OTHERS THEN
1518 IF ( ln_proc_level >= ln_dbg_level)
1519 THEN
1520 FND_LOG.STRING ( ln_proc_level
1521 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
1522 || '. Other_Exception '
1523 , SQLCODE || ':' || SQLERRM
1524 );
1525 END IF; --( ln_proc_level >= ln_dbg_level) ;
1526
1527 RETURN nvl(lv_service_tax_flag, 'N');
1528 END Get_Service_Tax_Flag;
1529
1530 --==========================================================================
1531 -- FUNCTION NAME:
1532 --
1533 -- Get_Tax_Type Private
1534 --
1535 -- DESCRIPTION:
1536 -- With given modvat_flag and credit percentage, return the tax type.
1537 -- Tax type can be FR,fully recoverable,NR,not recoverable,or PR,partially
1538 -- recoverable
1539 --
1540 -- PARAMETERS:
1541 -- In: pv_modvat_flag IN VARCHAR Y or N
1542 -- pn_cr_percentage IN NUMBER Credit percentage
1543 --
1544 -- Out: RETURN VARCHAR2
1545 --
1546 --
1547 -- PRE-COND : invoice exists
1548 -- EXCEPTIONS:
1549 --
1550 --===========================================================================
1551 FUNCTION Get_Tax_Type
1552 ( pv_modvat_flag VARCHAR2
1553 , pn_cr_percentage NUMBER
1554 )
1555 RETURN VARCHAR2
1556 IS
1557 lv_tax_type VARCHAR2(10) ;
1558 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1559 ln_proc_level NUMBER := FND_LOG.level_procedure;
1560 lv_proc_name VARCHAR2 (100) := 'Get_Tax_Type';
1561 BEGIN
1562 IF ( ln_proc_level >= ln_dbg_level)
1563 THEN
1564 FND_LOG.STRING ( ln_proc_level
1565 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
1566 , 'Enter procedure'
1567 );
1568 FND_LOG.STRING ( ln_proc_level
1569 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1570 , 'pv_modvat_flag ' || pv_modvat_flag
1571 );
1572 FND_LOG.STRING ( ln_proc_level
1573 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1574 , 'pn_cr_percentage ' || pn_cr_percentage
1575 );
1576 END IF; --(ln_proc_level >= ln_dbg_level)
1577
1578 IF ( NVL (pv_modvat_flag, 'N') = jai_constants.no
1579 OR NVL (pn_cr_percentage, -1) <= 0
1580 )
1581 THEN
1582 lv_tax_type := 'NR' ; --NON RECOVERABLE
1583 ELSIF
1584 ( NVL (pv_modvat_flag, 'N') = jai_constants.yes
1585 AND NVL (pn_cr_percentage, -1) = 100
1586 )
1587 THEN
1588 lv_tax_type := 'FR' ; --FULLY RECOVERABLE
1589 ELSIF
1590 ( NVL (pv_modvat_flag, 'N') = jai_constants.yes
1591 AND NVL (pn_cr_percentage, -1) < 100
1592 )
1593 THEN
1594 lv_tax_type := 'PR' ; --PARTIALLY RECOVERABLE
1595 END IF;--( (pv_modvat_flag, 'N') = jai_constants.no)
1596
1597
1598 IF ( ln_proc_level >= ln_dbg_level)
1599 THEN
1600 FND_LOG.STRING ( ln_proc_level
1601 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1602 , 'lv_tax_type ' || lv_tax_type
1603 );
1604
1605 FND_LOG.STRING ( ln_proc_level
1606 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
1607 , 'Exit procedure'
1608 );
1609 END IF; --( ln_proc_level >= ln_dbg_level )
1610
1611 RETURN lv_tax_type;
1612 EXCEPTION
1613 WHEN OTHERS THEN
1614 IF ( ln_proc_level >= ln_dbg_level)
1615 THEN
1616 FND_LOG.STRING ( ln_proc_level
1617 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
1618 || '. Other_Exception '
1619 , SQLCODE || ':' || SQLERRM
1620 );
1621 END IF; --( ln_proc_level >= ln_dbg_level) ;
1622
1623 RETURN lv_tax_type;
1624 END Get_Tax_Type;
1625
1626 --==========================================================================
1627 -- FUNCTION NAME:
1628 --
1629 -- Get_Dist_Account_Ccid Private
1630 --
1631 -- DESCRIPTION:
1632 -- Get the distribution account ccid for a given tax type code
1633 -- 1.get account ccid from regim level
1634 -- 2.get account ccid from org-location combination level
1635 -- 3.get account ccid from tax definition level
1636 --
1637 -- PARAMETERS:
1638 -- In:
1639 -- pn_invoice_id IN NUMBER
1640 -- pn_item_line_number IN NUMBER
1641 -- pn_organization_id IN NUMBER
1642 -- pn_location_id IN NUMBER
1643 -- pn_tax_type_code IN VARCHAR2
1644 -- pn_tax_acct_ccid IN NUMBER
1645 -- pv_tax_type IN VARCHAR2
1646 --
1647 -- Out: RETURN number, account ccid
1648 --
1649 --
1650 -- PRE-COND :
1651 -- EXCEPTIONS:
1652 --
1653 --===========================================================================
1654 FUNCTION Get_Dist_Account_Ccid
1655 ( pn_invoice_id IN NUMBER
1656 , pn_item_line_number IN NUMBER
1657 , pn_organization_id IN NUMBER
1658 , pn_location_id IN NUMBER
1659 , pn_tax_type_code IN VARCHAR2
1660 , pn_tax_acct_ccid IN NUMBER
1661 , pv_tax_type IN VARCHAR2
1662 )
1663 RETURN NUMBER
1664 IS
1665 CURSOR item_dist_account_cur IS
1666 SELECT
1667 dist_code_combination_id
1668 FROM
1669 ap_invoice_distributions_all
1670 WHERE invoice_id = pn_invoice_id
1671 AND invoice_line_number = pn_item_line_number
1672 AND distribution_line_number =1;
1673
1674
1675 CURSOR jai_regimes_cur
1676 (
1677 pv_regime_code IN jai_rgm_definitions.regime_code%TYPE
1678 )
1679 IS
1680 SELECT
1681 regime_id
1682 FROM
1683 jai_rgm_definitions
1684 WHERE regime_code = pv_regime_code;
1685
1686 CURSOR regime_tax_type_cur
1687 ( pn_regime_id NUMBER
1688 , pv_tax_type_code VARCHAR2
1689 )
1690 IS
1691 SELECT
1692 attribute_code tax_type
1693 FROM
1694 jai_rgm_registrations
1695 WHERE regime_id = pn_regime_id
1696 AND registration_type =jai_constants.regn_type_tax_types --tax type
1697 AND attribute_code = pv_tax_type_code;
1698
1699 CURSOR regime_account_cur
1700 ( pn_regime_id NUMBER
1701 , pn_tax_type VARCHAR2
1702 , pv_account_name VARCHAR2--Add by Xiao for POT change, reg bug#12533434 --Added by Chong.Lei for POT code port
1703 )
1704 IS
1705 SELECT
1706 TO_NUMBER (accnts.attribute_value)
1707 FROM
1708 jai_rgm_registrations tax_types
1709 , jai_rgm_registrations accnts
1710 WHERE tax_types.regime_id = pn_regime_id
1711 AND tax_types.registration_type = jai_constants.regn_type_tax_types
1712 AND tax_types.attribute_code = pn_tax_type
1713 AND accnts.regime_id = tax_types.regime_id
1714 AND accnts.registration_type = jai_constants.regn_type_accounts
1715 AND accnts.parent_registration_id = tax_types.registration_id
1716 -- AND accnts.attribute_code = jai_constants.recovery_interim; --Comment by Chong.Lei for POT code port
1717 AND accnts.attribute_code = pv_account_name;--Modified by Xiao for POT changes, reg bug#12533434, --Added by Chong.Lei for POT code port
1718
1719 --start additions for bug#9775984
1720 Cursor get_dflt_ccid IS
1724 and line_number = pn_item_line_number;
1721 Select default_dist_ccid
1722 FROM AP_INVOICE_LINES_ALL
1723 WHERE invoice_id = pn_invoice_id
1725 --end additions for bug#9775984
1726
1727
1728 service_regimes_rec jai_regimes_cur%ROWTYPE;
1729 vat_regimes_rec jai_regimes_cur%ROWTYPE;
1730 ln_dist_acct_ccid NUMBER;
1731 ln_regime_id jai_rgm_definitions.regime_id%TYPE;
1732 lv_regime_code jai_rgm_definitions.regime_code%TYPE;
1733 lv_regim_tax_type jai_rgm_registrations.attribute_code%TYPE;
1734
1735 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1736 ln_proc_level NUMBER := FND_LOG.level_procedure;
1737 lv_proc_name VARCHAR2 (100) := 'Get_Dist_Account_Ccid';
1738 -- Added by Chong.Lei for POT code port begin
1739 --Add by Xiao for POT changes, reg bug#12533434, begin
1740 ---------------------------------------------------------------
1741 CURSOR get_invoice_type_cur IS
1742 SELECT invoice_type_lookup_code
1743 FROM ap_invoices_all
1744 WHERE invoice_id = pn_invoice_id;
1745 lv_invoice_type VARCHAR2(30);
1746 lv_account_name VARCHAR2(30);
1747 ---------------------------------------------------------------
1748 --Add by Xiao for POT changes, reg bug#12533434, end
1749 -- Added by Chong.Lei for POT code port end
1750
1751 BEGIN
1752 --log for debug
1753 IF ( ln_proc_level >= ln_dbg_level)
1754 THEN
1755 FND_LOG.STRING ( ln_proc_level
1756 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
1757 , 'Enter procedure'
1758 );
1759 FND_LOG.STRING ( ln_proc_level
1760 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1761 , 'pn_organization_id ' || pn_organization_id
1762 );
1763 FND_LOG.STRING ( ln_proc_level
1764 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1765 , 'pn_location_id ' || pn_location_id
1766 );
1767 FND_LOG.STRING ( ln_proc_level
1768 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1769 , 'pn_organization_id ' || pn_organization_id
1770 );
1771 FND_LOG.STRING ( ln_proc_level
1772 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1773 , 'pn_tax_acct_ccid ' || pn_tax_acct_ccid
1774 );
1775 FND_LOG.STRING ( ln_proc_level
1776 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1777 , 'pv_tax_type ' || pv_tax_type
1778 );
1779 END IF;
1780
1781 IF (pv_tax_type='NR') --NON recoverable tax
1782 THEN
1783 OPEN item_dist_account_cur;
1784 FETCH item_dist_account_cur
1785 INTO
1786 ln_dist_acct_ccid ;
1787 CLOSE item_dist_account_cur;
1788
1789 --start additions for bug#9775984
1790 /*if the distribution in distribution table doesnot exists
1791 then fetch ccid from dflt ccid attached at the invoice line level*/
1792 if ln_dist_acct_ccid is null
1793 then
1794 open get_dflt_ccid;
1795 fetch get_dflt_ccid into ln_dist_acct_ccid;
1796 close get_dflt_ccid;
1797 end if;
1798 --end additions for bug#9775984
1799 ELSE -- recoverable tax
1800 OPEN jai_regimes_cur (jai_constants.service_regime);
1801 FETCH jai_regimes_cur
1802 INTO
1803 service_regimes_rec;
1804 CLOSE jai_regimes_cur;
1805
1806 OPEN jai_regimes_cur (jai_constants.vat_regime);
1807 FETCH jai_regimes_cur
1808 INTO
1809 vat_regimes_rec;
1810 CLOSE jai_regimes_cur;
1811
1812 --check the tax is service taxes or not
1813 OPEN regime_tax_type_cur ( service_regimes_rec.regime_id
1814 , pn_tax_type_code
1815 );
1816 FETCH regime_tax_type_cur
1817 INTO
1818 lv_regim_tax_type;
1819 CLOSE regime_tax_type_cur;
1820
1821 IF lv_regim_tax_type IS NOT NULL
1822 THEN
1823 lv_regime_code := jai_constants.service_regime;
1824 ELSE -- (r_service_regime_tax is null)
1825
1826 -- vat taxes
1827 OPEN regime_tax_type_cur ( vat_regimes_rec.regime_id
1828 , pn_tax_type_code
1829 );
1830 FETCH regime_tax_type_cur
1831 INTO
1832 lv_regim_tax_type;
1833 CLOSE regime_tax_type_cur;
1834
1835
1836
1837 IF lv_regim_tax_type IS NOT NULL
1838 THEN
1839 lv_regime_code := jai_constants.vat_regime;
1840 END IF; --(lv_regim_tax_type IS NOT NULL)
1841
1842 END IF; --( end of r_service_regime_tax_type level)
1843
1844 -- Added by Chong.Lei for POT code port begin
1845 --Add by Xiao for POT change, reg bug#12533434, begin
1846 --------------------------------------------------------------------
1847 OPEN get_invoice_type_cur;
1848 FETCH get_invoice_type_cur INTO lv_invoice_type;
1849 CLOSE get_invoice_type_cur;
1850
1851 IF lv_invoice_type = 'CREDIT' THEN --Xiao for DM/CM Accounting.
1852 --IF lv_invoice_type IN ('CREDIT', 'DEBIT') THEN
1853 lv_account_name := jai_constants.liability_interim;
1854 ELSE
1855 lv_account_name := jai_constants.recovery_interim;
1856 END IF;
1857 --------------------------------------------------------------------
1858 --Add by Xiao for POT change, reg bug#12533434, end.
1859 -- Added by Chong.Lei for POT code port end
1860
1861 --try to get account from regim level
1862 IF lv_regime_code IS NULL --(tax is not difined in regim level)
1863 THEN
1864 ln_dist_acct_ccid := pn_tax_acct_ccid;
1868 FETCH jai_regimes_cur
1865 ELSE --(lv_regime_code is NOT null,tax has beend difined in regim level)
1866 OPEN jai_regimes_cur (lv_regime_code);
1867
1869 INTO
1870 ln_regime_id;
1871 CLOSE jai_regimes_cur;
1872
1873 IF ( pn_organization_id IS NULL
1874 AND pn_location_id IS NULL
1875 )
1876 THEN
1877 OPEN regime_account_cur
1878 ( ln_regime_id
1879 , pn_tax_type_code
1880 , lv_account_name--Add by Xiao for POT change, reg bug#12533434 --Added by Chong.Lei for POT code port
1881 );
1882
1883 FETCH regime_account_cur
1884 INTO
1885 ln_dist_acct_ccid;
1886 CLOSE regime_account_cur;
1887 ELSIF( pn_organization_id IS NOT NULL
1888 AND pn_location_id IS NOT NULL
1889 )
1890 THEN
1891 ln_dist_acct_ccid :=
1892 jai_cmn_rgm_recording_pkg.get_account
1893 ( p_regime_id => ln_regime_id
1894 , p_organization_type => jai_constants.orgn_type_io
1895 , p_organization_id => pn_organization_id
1896 , p_location_id => pn_location_id
1897 , p_tax_type => pn_tax_type_code
1898 -- , p_account_name => jai_constants.recovery_interim --Comment by Chong.Lei for POT code port
1899 , p_account_name => lv_account_name--Modified by Xiao for POT changes. reg bug#12533434 --Added by Chong.Lei for POT code port
1900 );
1901
1902 END IF; --(pn_organization_id IS NULL AND pn_location_id IS NULL )
1903 END IF; --(lv_regime_code IS NULL)
1904 END IF; --(lv_recoverable_flag = 'N')
1905
1906 IF ( ln_proc_level >= ln_dbg_level)
1907 THEN
1908 FND_LOG.STRING ( ln_proc_level
1909 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1910 , 'ln_dist_acct_ccid ' || ln_dist_acct_ccid
1911 );
1912
1913 FND_LOG.STRING ( ln_proc_level
1914 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
1915 , 'Exit procedure'
1916 );
1917 END IF; --( ln_proc_level >= ln_dbg_level )
1918
1919 RETURN ln_dist_acct_ccid ;
1920 EXCEPTION
1921 WHEN OTHERS THEN
1922 IF ( ln_proc_level >= ln_dbg_level)
1923 THEN
1924 FND_LOG.STRING ( ln_proc_level
1925 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
1926 || '. Other_Exception '
1927 , SQLCODE || ':' || SQLERRM
1928 );
1929 END IF; --( ln_proc_level >= ln_dbg_level) ;
1930
1931 RETURN NULL;
1932 END Get_Dist_Account_Ccid;
1933
1934
1935 --==========================================================================
1936 -- FUNCTION NAME:
1937 --
1938 -- Get_Max_Invoice_Line_Number Private
1939 --
1940 -- DESCRIPTION:
1941 -- Get the max invoice line number for a given invoice id
1942 --
1943 --
1944 -- PARAMETERS:
1945 -- In: pn_invoice_id IN NUMBER invoice id
1946 --
1947 -- Out: RETURN number
1948 --
1949 --
1950 -- PRE-COND : invoice exists
1951 -- EXCEPTIONS:
1952 --
1953 --===========================================================================
1954 FUNCTION Get_Max_Invoice_Line_Number (pn_invoice_id NUMBER)
1955 RETURN NUMBER
1956 IS
1957 ln_max_line_number NUMBER;
1958 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
1959 ln_proc_level NUMBER := FND_LOG.level_procedure;
1960 lv_proc_name VARCHAR2 (100) := 'Get_Max_Invoice_Line_Number';
1961 BEGIN
1962 --log for debug
1963 IF ( ln_proc_level >= ln_dbg_level)
1964 THEN
1965 FND_LOG.STRING ( ln_proc_level
1966 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
1967 , 'Enter procedure'
1968 );
1969 FND_LOG.STRING ( ln_proc_level
1970 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1971 , 'pn_invoice_id ' || pn_invoice_id
1972 );
1973 END IF;
1974
1975 -- add row level lock to the table ,to avoid duplicated lines created
1976 /*UPDATE ap_invoice_lines_all
1977 SET invoice_id = pn_invoice_id
1978 WHERE invoice_id = pn_invoice_id;*/ -- for bug 14681650 by anupgupt
1979
1980 SELECT
1981 NVL(MAX (line_number), 0)
1982 INTO
1983 ln_max_line_number
1984 FROM
1985 ap_invoice_lines_all
1986 WHERE invoice_id = pn_invoice_id;
1987
1988 IF ( ln_proc_level >= ln_dbg_level)
1989 THEN
1990 FND_LOG.STRING ( ln_proc_level
1991 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
1992 , 'ln_max_line_number ' || ln_max_line_number
1993 );
1994 FND_LOG.STRING ( ln_proc_level
1995 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
1996 , 'Exit procedure'
1997 );
1998 END IF; --( ln_proc_level >= ln_dbg_level )
1999
2000 RETURN ln_max_line_number;
2001 EXCEPTION
2002 WHEN OTHERS THEN
2003 IF ( ln_proc_level >= ln_dbg_level)
2004 THEN
2005 FND_LOG.STRING ( ln_proc_level
2006 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2007 || '. Other_Exception '
2008 , SQLCODE || ':' || SQLERRM
2009 );
2010 END IF; --( ln_proc_level >= ln_dbg_level) ;
2011
2012 RETURN 0;
2013 END Get_Max_Invoice_Line_Number;
2014
2018 -- Get_Max_Doc_Source_Line_Id Private
2015 --==========================================================================
2016 -- FUNCTION NAME:
2017 --
2019 --
2020 -- DESCRIPTION:
2021 -- Get the max invoice line number( source doc line id )for a given
2022 -- invoice id (source id)
2023 --
2024 --
2025 -- PARAMETERS:
2026 -- In: pn_invoice_id IN NUMBER invoice id
2027 --
2028 -- Out: RETURN number
2029 --
2030 --
2031 -- PRE-COND : invoice exists
2032 -- EXCEPTIONS:
2033 --
2034 -- CHANGE HISTORY:
2035 -- 1 29-Jan-2008 Eric Ma Created for bug#6784111
2036 --
2037 --===========================================================================
2038 FUNCTION Get_Max_Doc_Source_Line_Id (pn_invoice_id NUMBER)
2039 RETURN NUMBER
2040 IS
2041 ln_max_line_number NUMBER;
2042 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2043 ln_proc_level NUMBER := FND_LOG.level_procedure;
2044 lv_proc_name VARCHAR2 (100) := 'Get_Max_Doc_Source_Line_Id';
2045 BEGIN
2046 --log for debug
2047 IF ( ln_proc_level >= ln_dbg_level)
2048 THEN
2049 FND_LOG.STRING ( ln_proc_level
2050 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2051 , 'Enter procedure'
2052 );
2053 FND_LOG.STRING ( ln_proc_level
2054 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2055 , 'pn_invoice_id ' || pn_invoice_id
2056 );
2057 END IF;
2058
2059 -- add row level lock to the table
2060 UPDATE
2061 jai_cmn_document_taxes
2062 SET
2063 source_doc_id = pn_invoice_id
2064 WHERE source_doc_id = pn_invoice_id
2065 AND source_doc_type = jai_constants.g_ap_standalone_invoice;
2066
2067 SELECT
2068 NVL(MAX(source_doc_line_id), 0)
2069 INTO
2070 ln_max_line_number
2071 FROM
2072 jai_cmn_document_taxes
2073 WHERE source_doc_id = pn_invoice_id
2074 AND source_doc_type = jai_constants.g_ap_standalone_invoice;
2075
2076
2077 IF ( ln_proc_level >= ln_dbg_level)
2078 THEN
2079 FND_LOG.STRING ( ln_proc_level
2080 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2081 , 'ln_max_line_number ' || ln_max_line_number
2082 );
2083 FND_LOG.STRING ( ln_proc_level
2084 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2085 , 'Exit procedure'
2086 );
2087 END IF; --( ln_proc_level >= ln_dbg_level )
2088
2089 RETURN ln_max_line_number;
2090 EXCEPTION
2091 WHEN OTHERS THEN
2092 IF ( ln_proc_level >= ln_dbg_level)
2093 THEN
2094 FND_LOG.STRING ( ln_proc_level
2095 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2096 || '. Other_Exception '
2097 , SQLCODE || ':' || SQLERRM
2098 );
2099 END IF; --( ln_proc_level >= ln_dbg_level) ;
2100
2101 RETURN 0;
2102 END Get_Max_Doc_Source_Line_Id;
2103
2104 --==========================================================================
2105 -- FUNCTION NAME:
2106 --
2107 -- Get_Max_Tax_Line_Number Private
2108 --
2109 -- DESCRIPTION:
2110 -- Get the max tax line number for a given invoice id
2111 --
2112 --
2113 -- PARAMETERS:
2114 -- In: pn_invoice_id IN NUMBER invoice id
2115 -- pn_parent_invoice_line_number IN NUMBER item line number
2116 -- Out: RETURN number
2117 --
2118 --
2119 -- PRE-COND : invoice exists
2120 -- EXCEPTIONS:
2121 --
2122 --===========================================================================
2123 FUNCTION Get_Max_Tax_Line_Number
2124 ( pn_invoice_id NUMBER
2125 , pn_parent_invoice_line_number NUMBER
2126 )
2127 RETURN NUMBER
2128 IS
2129 ln_max_tax_line_num NUMBER;
2130 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2131 ln_proc_level NUMBER := FND_LOG.level_procedure;
2132 lv_proc_name VARCHAR2 (100) := 'Get_Max_Tax_Line_Number';
2133 BEGIN
2134 --log for debug
2135 IF ( ln_proc_level >= ln_dbg_level)
2136 THEN
2137 FND_LOG.STRING ( ln_proc_level
2138 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2139 , 'Enter procedure'
2140 );
2141 FND_LOG.STRING ( ln_proc_level
2142 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2143 , 'pn_invoice_id ' || pn_invoice_id
2144 );
2145 FND_LOG.STRING ( ln_proc_level
2146 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2147 , 'pn_parent_invoice_line_number ' ||
2148 pn_parent_invoice_line_number
2149 );
2150 END IF;
2151
2152 --add row level lock on the table to avoid data conflication
2153 UPDATE
2154 jai_cmn_document_taxes
2155 SET
2156 source_doc_parent_line_no = pn_parent_invoice_line_number
2157 WHERE source_doc_id = pn_invoice_id
2158 AND source_doc_parent_line_no = pn_parent_invoice_line_number
2159 AND source_doc_type = jai_constants.g_ap_standalone_invoice;
2160
2161 SELECT
2162 NVL(MAX(tax_line_no),0)
2163 INTO
2164 ln_max_tax_line_num
2165 FROM
2166 jai_cmn_document_taxes
2167 WHERE source_doc_id = pn_invoice_id
2171 IF ( ln_proc_level >= ln_dbg_level)
2168 AND source_doc_parent_line_no = pn_parent_invoice_line_number
2169 AND source_doc_type = jai_constants.g_ap_standalone_invoice;
2170
2172 THEN
2173 FND_LOG.STRING ( ln_proc_level
2174 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2175 , 'ln_max_tax_line_num ' || ln_max_tax_line_num
2176 );
2177 FND_LOG.STRING ( ln_proc_level
2178 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2179 , 'Exit procedure'
2180 );
2181 END IF; --( ln_proc_level >= ln_dbg_level )
2182
2183 RETURN ln_max_tax_line_num;
2184 EXCEPTION
2185 WHEN OTHERS THEN
2186 IF ( ln_proc_level >= ln_dbg_level)
2187 THEN
2188 FND_LOG.STRING ( ln_proc_level
2189 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2190 || '. Other_Exception '
2191 , SQLCODE || ':' || SQLERRM
2192 );
2193 END IF; --( ln_proc_level >= ln_dbg_level) ;
2194
2195 RETURN 0;
2196 END Get_Max_Tax_Line_Number;
2197
2198
2199 --==========================================================================
2200 -- FUNCTION NAME:
2201 --
2202 -- Get_Gl_Account_Type Private
2203 --
2204 -- DESCRIPTION:
2205 -- Get the account type for a given ccid
2206 --
2207 --
2208 -- PARAMETERS:
2209 -- In: pn_code_combination_id NUMBER code combnation id
2210 --
2211 -- Out: RETURN account_type
2212 --
2213 -- PRE-COND : ccid exists
2214 -- EXCEPTIONS:
2215 --
2216 --===========================================================================
2217 FUNCTION Get_Gl_Account_Type (pn_code_combination_id IN NUMBER)
2218 RETURN VARCHAR2
2219 IS
2220 CURSOR get_account_cur IS
2221 SELECT
2222 account_type
2223 FROM
2224 gl_code_combinations
2225 WHERE code_combination_id = pn_code_combination_id;
2226
2227 lv_account_type gl_code_combinations.account_type%TYPE;
2228
2229 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2230 ln_proc_level NUMBER := FND_LOG.level_procedure;
2231 lv_proc_name VARCHAR2 (100) := 'Get_Gl_Account_Type';
2232 BEGIN
2233 --log for debug
2234 IF ( ln_proc_level >= ln_dbg_level)
2235 THEN
2236 FND_LOG.STRING ( ln_proc_level
2237 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2238 , 'Enter procedure'
2239 );
2240 FND_LOG.STRING ( ln_proc_level
2241 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2242 , 'pn_code_combination_id ' || pn_code_combination_id
2243 );
2244 END IF;--( ln_proc_level >= ln_dbg_level )
2245
2246 OPEN get_account_cur;
2247 FETCH get_account_cur
2248 INTO
2249 lv_account_type;
2250 CLOSE get_account_cur;
2251
2252 IF ( ln_proc_level >= ln_dbg_level)
2253 THEN
2254 FND_LOG.STRING ( ln_proc_level
2255 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2256 , 'lv_account_type ' || lv_account_type
2257 );
2258 FND_LOG.STRING ( ln_proc_level
2259 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2260 , 'Exit procedure'
2261 );
2262 END IF; --( ln_proc_level >= ln_dbg_level )
2263
2264
2265 RETURN lv_account_type;
2266 EXCEPTION
2267 WHEN OTHERS THEN
2268 IF ( ln_proc_level >= ln_dbg_level)
2269 THEN
2270 FND_LOG.STRING ( ln_proc_level
2271 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2272 || '. Other_Exception '
2273 , SQLCODE || ':' || SQLERRM
2274 );
2275 END IF; --( ln_proc_level >= ln_dbg_level) ;
2276
2277 RETURN NULL; -- if required exception can be handled.
2278 END Get_Gl_Account_Type;
2279
2280 --==========================================================================
2281 -- PROCEDURE NAME:
2282 --
2283 -- Get_Tax_Cat_Serv_Type Private
2284 --
2285 -- DESCRIPTION:
2286 -- Get the tax category and service type code for a given vendor site
2287 -- and vendor id
2288 --
2289 --
2290 -- PARAMETERS:
2291 -- In: pn_invoice_id NUMBER invoice id
2292 -- pn_vendor_site_id NUMBER vendor site id
2293 --
2294 -- Out: x_tax_category_id NUMBER tax category id
2295 -- x_service_type_code NUMBER service type code
2296 --
2297 -- PRE-COND : vendor exists
2298 -- EXCEPTIONS:
2299 --
2300 --===========================================================================
2301 PROCEDURE Get_Tax_Cat_Serv_Type
2302 ( pn_vendor_id IN NUMBER
2303 , pn_vendor_site_id IN NUMBER
2304 , xn_tax_category_id OUT NOCOPY NUMBER
2305 , xv_service_type_code OUT NOCOPY VARCHAR2
2306 )
2307 IS
2308 CURSOR get_tax_service_cur IS
2309 SELECT
2310 tax_category_id, service_type_code
2311 FROM
2312 jai_cmn_vendor_sites
2313 WHERE NVL (vendor_site_id, 0) = pn_vendor_site_id
2314 AND vendor_id = pn_vendor_id;
2315
2316 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2317 ln_proc_level NUMBER := FND_LOG.level_procedure;
2318 lv_proc_name VARCHAR2 (100) := 'Get_Tax_Cat_Serv_Type';
2319 BEGIN
2320 --log for debug
2321 IF ( ln_proc_level >= ln_dbg_level)
2322 THEN
2323 FND_LOG.STRING ( ln_proc_level
2324 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2328 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2325 , 'Enter procedure'
2326 );
2327 FND_LOG.STRING ( ln_proc_level
2329 , 'pn_vendor_id ' || pn_vendor_id
2330 );
2331 FND_LOG.STRING ( ln_proc_level
2332 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2333 , 'pn_vendor_site_id ' || pn_vendor_site_id
2334 );
2335 END IF;
2336
2337 OPEN get_tax_service_cur;
2338
2339 FETCH get_tax_service_cur
2340 INTO
2341 xn_tax_category_id
2342 , xv_service_type_code;
2343 CLOSE get_tax_service_cur;
2344
2345 IF ( ln_proc_level >= ln_dbg_level)
2346 THEN
2347 FND_LOG.STRING ( ln_proc_level
2348 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2349 , 'xn_tax_category_id ' || xn_tax_category_id
2350 );
2351 FND_LOG.STRING ( ln_proc_level
2352 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2353 , 'xv_service_type_code ' || xv_service_type_code
2354 );
2355 FND_LOG.STRING ( ln_proc_level
2356 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2357 , 'Exit procedure'
2358 );
2359 END IF; --( ln_proc_level >= ln_dbg_level )
2360 EXCEPTION
2361 WHEN OTHERS THEN
2362 IF ( ln_proc_level >= ln_dbg_level)
2363 THEN
2364 FND_LOG.STRING ( ln_proc_level
2365 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2366 || '. Other_Exception '
2367 ,SQLCODE || ':' || SQLERRM
2368 );
2369 END IF; --( ln_proc_level >= ln_dbg_level) ;
2370 RAISE;
2371 END Get_Tax_Cat_Serv_Type;
2372
2373 --==========================================================================
2374 -- PROCEDURE NAME:
2375 --
2376 -- PROCEDURE Get_Invoice_Header_Infor Private
2377 --
2378 -- DESCRIPTION:
2379 --
2380 -- For a given invoice id RETURN vendor id,vendor site id, currency code
2381 -- and exchange rate
2382 --
2383 -- PARAMETERS:
2384 -- In: pn_invoice_id NUMBER invoice id
2385 --
2386 --
2387 -- Out: xn_vendor_id number vendor id
2388 -- xn_vendor_site_id number vendor site id
2389 -- xv_currency_code varchar2 currency code
2390 -- xn_exchange_rate number exchange rate
2391 -- xn_batch_id number xn_batch_id
2392 --
2393 -- PRE-COND : invoice exists
2394 -- EXCEPTIONS:
2395 --
2396 --
2397 --========================================================================
2398 PROCEDURE Get_Invoice_Header_Infor
2399 ( pn_invoice_id IN NUMBER
2400 , xn_vendor_id OUT NOCOPY NUMBER
2401 , xn_vendor_site_id OUT NOCOPY NUMBER
2402 , xv_currency_code OUT NOCOPY VARCHAR2
2403 , xn_exchange_rate OUT NOCOPY NUMBER
2404 , xn_batch_id OUT NOCOPY NUMBER
2405 )
2406 IS
2407 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2408 ln_proc_level NUMBER := FND_LOG.level_procedure;
2409 lv_proc_name VARCHAR2 (100) := 'Get_Invoice_Header_Infor';
2410
2411 CURSOR Get_Invoice_Header_Infor_cur IS
2412 SELECT
2413 vendor_id
2414 , vendor_site_id
2415 , invoice_currency_code
2416 , exchange_rate
2417 , batch_id
2418 FROM
2419 ap_invoices_all
2420 WHERE invoice_id = pn_invoice_id;
2421 BEGIN
2422 --log for debug
2423 IF ( ln_proc_level >= ln_dbg_level)
2424 THEN
2425 FND_LOG.STRING ( ln_proc_level
2426 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2427 , 'Enter procedure'
2428 );
2429 FND_LOG.STRING ( ln_proc_level
2430 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2431 , 'pn_invoice_id ' || pn_invoice_id
2432 );
2433 END IF; --( ln_proc_level >= ln_dbg_level) ;
2434
2435 OPEN Get_Invoice_Header_Infor_cur ;
2436 FETCH Get_Invoice_Header_Infor_cur
2437 INTO
2438 xn_vendor_id
2439 , xn_vendor_site_id
2440 , xv_currency_code
2441 , xn_exchange_rate
2442 , xn_batch_id ;
2443 CLOSE Get_Invoice_Header_Infor_cur ;
2444
2445 --log for debug
2446 IF ( ln_proc_level >= ln_dbg_level)
2447 THEN
2448 FND_LOG.STRING ( ln_proc_level
2449 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2450 , 'xn_vendor_id ' || xn_vendor_id
2451 );
2452 FND_LOG.STRING ( ln_proc_level
2453 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2454 , 'xn_vendor_site_id ' || xn_vendor_site_id
2455 );
2456 FND_LOG.STRING ( ln_proc_level
2457 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2458 , 'xv_currency_code ' || xv_currency_code
2459 );
2460 FND_LOG.STRING ( ln_proc_level
2461 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2462 , 'xn_exchange_rate ' || xn_exchange_rate
2463 );
2464 FND_LOG.STRING ( ln_proc_level
2465 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2466 , 'xn_batch_id ' || xn_batch_id
2467 );
2468 FND_LOG.STRING ( ln_proc_level
2469 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2470 , 'Exit procedure'
2471 );
2472 END IF; --( ln_proc_level >= ln_dbg_level )
2473 EXCEPTION
2474 WHEN OTHERS THEN
2475 IF ( ln_proc_level >= ln_dbg_level)
2476 THEN
2477 FND_LOG.STRING ( ln_proc_level
2478 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2479 || '. Other_Exception '
2480 , SQLCODE || ':' || SQLERRM
2481 );
2482 END IF; --( ln_proc_level >= ln_dbg_level) ;
2483 RAISE;
2484 END Get_Invoice_Header_Infor;
2485
2486 --==========================================================================
2487 -- PROCEDURE NAME:
2488 --
2489 -- PROCEDURE Delete_Tax_Lines Private
2490 --
2491 -- DESCRIPTION:
2492 --
2493 -- Delete exclusive taxes from ap invoice/dist lines table and
2494 -- jai_ap_invoice_line. Besides, all tax lines in jai_cmn_document_taxes
2495 -- will be deleted in case of pv_modified_only_flag='N'
2496 -- PARAMETERS:
2497 -- In: pn_invoice_id NUMBER invoice id
2498 -- pn_line_number NUMBER invoice item line number
2499 -- pv_modified_only_flag VARCHAR2 indicate flag of
2500 -- tax line modification
2501 -- Out:
2502 --
2503 --
2504 -- DESIGN REFERENCES:
2505 -- AP Technical Design 2.1.doc
2506 --
2507 -- CHANGE HISTORY:
2508 --
2509 -- 1 23-Aug-2007 Eric Ma Created
2510 -- 2 20-Nov-2007 Eric Ma modified for inclusive tax
2511 --===========================================================================
2512 PROCEDURE Delete_Tax_Lines
2513 ( pn_invoice_id NUMBER
2514 , pn_line_number NUMBER
2515 , pv_modified_only_flag VARCHAR2 DEFAULT 'N'
2516 )
2517 IS
2518 ln_invoice_id NUMBER := pn_invoice_id;
2519 ln_invoice_line_number NUMBER := pn_line_number;
2520 lv_modified_only_flag VARCHAR2 (1) := pv_modified_only_flag;
2521 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2522 ln_proc_level NUMBER := FND_LOG.level_procedure;
2523 lv_proc_name VARCHAR2 (100) := 'Delete_Tax_Lines';
2524 BEGIN
2525 --log for debug
2526 IF ( ln_proc_level >= ln_dbg_level)
2527 THEN
2528 FND_LOG.STRING ( ln_proc_level
2529 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2530 , 'Enter procedure'
2531 );
2532 FND_LOG.STRING ( ln_proc_level
2533 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2534 , 'pn_invoice_id ' || pn_invoice_id
2535 );
2536 FND_LOG.STRING ( ln_proc_level
2537 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2538 , 'pn_line_number '|| pn_line_number
2539 );
2540 FND_LOG.STRING ( ln_proc_level
2541 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2542 , 'pv_modified_only_flag ' || pv_modified_only_flag
2543 );
2544 END IF; --( ln_proc_level >= ln_dbg_level )
2545
2546 IF (lv_modified_only_flag = 'N')
2547 THEN
2548 --delete all taxes lines for the specified invoice id and invoice item line
2549 --if invoice line number is null, all lines for the invoice will be deleted
2550 DELETE
2551 FROM
2552 jai_cmn_document_taxes jcdt
2553 WHERE jcdt.source_doc_id = ln_invoice_id
2554 AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
2555 --added by eric for inclusive tax
2556 --------------------------------------------------------------------
2557 AND jcdt.source_doc_parent_line_no=
2558 NVL( ln_invoice_line_number, jcdt.source_doc_parent_line_no);
2559
2560
2564 AND EXISTS
2561 --end of modification -----------------------------------------------
2562
2563 /*Commented out by eric for inclusive tax
2565 ( SELECT
2566 'X'
2567 FROM
2568 jai_ap_invoice_lines jail
2569 WHERE jail.invoice_line_number = jcdt.source_doc_line_id
2570 AND jail.invoice_id = ln_invoice_id
2571 AND NVL(jail.parent_invoice_line_number,-1) =
2572 NVL(NVL( ln_invoice_line_number
2573 , jail.parent_invoice_line_number),-1)
2574 );
2575 */
2576 IF ( ln_proc_level >= ln_dbg_level)
2577 THEN
2578 FND_LOG.STRING( ln_proc_level
2579 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
2580 || '.Delete from jai_cmn_document_taxes'
2581 , SQL%ROWCOUNT||' ROWS DELETED '
2582 );
2583 END IF;
2584 END IF; --(lv_modified_only_flag = 'N')
2585
2586 --Delete all exclusive taxes lines for the specified invoice id and item line
2587 --number. If invoice line number is null, all exclusive tax lines for the
2588 --invoice will be deleted from ap_invoice_lines_all
2589 DELETE
2590 FROM
2591 ap_invoice_lines_all aila
2592 WHERE aila.invoice_id = ln_invoice_id
2593 AND EXISTS
2594 (
2595 SELECT
2596 'X'
2597 FROM
2598 jai_ap_invoice_lines jail
2599 WHERE jail.invoice_id = ln_invoice_id
2600 AND jail.invoice_line_number = aila.line_number
2601 AND jail.parent_invoice_line_number =
2602 NVL ( ln_invoice_line_number
2603 , parent_invoice_line_number
2604 )
2605 )
2606 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
2607
2608 IF ( ln_proc_level >= ln_dbg_level)
2609 THEN
2610 FND_LOG.STRING( ln_proc_level
2611 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
2612 || '.Delete from ap_invoice_lines_all'
2613 , SQL%ROWCOUNT||' ROWS DELETED '
2614 );
2615 END IF;--( ln_proc_level >= ln_dbg_level)
2616
2617 --Delete all exclusive taxes lines for the specified invoice id and item line
2618 --number. If invoice line number is null, all exclusive tax lines for the
2619 --invoice will be deleted from ap_invoice_distributions_all
2620 DELETE
2621 FROM
2622 ap_invoice_distributions_all aida
2623 WHERE aida.invoice_id = ln_invoice_id
2624 AND EXISTS
2625 (
2626 SELECT
2627 'X'
2628 FROM
2629 jai_ap_invoice_lines jail
2630 WHERE jail.invoice_id = ln_invoice_id
2631 AND jail.invoice_line_number = aida.invoice_line_number
2632 AND jail.parent_invoice_line_number =
2633 NVL (ln_invoice_line_number, parent_invoice_line_number)
2634 )
2635 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
2636
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 || '.Delete from ap_invoice_distributions_all'
2642 , SQL%ROWCOUNT||' ROWS DELETED '
2643 );
2644 END IF;
2645
2646
2647 --Delete all exclusive taxes lines for the specified invoice id and item line
2648 --number. If invoice line number is null, all exclusive tax lines for the
2649 --invoice will be deleted from jai_ap_invoice_lines
2650
2651
2652 DELETE
2653 FROM
2654 jai_ap_invoice_lines
2655 WHERE invoice_id = ln_invoice_id
2656 AND parent_invoice_line_number =
2657 NVL(ln_invoice_line_number,parent_invoice_line_number)
2658 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
2659
2660 IF ( ln_proc_level >= ln_dbg_level)
2661 THEN
2662 FND_LOG.STRING( ln_proc_level
2663 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
2664 || '.Delete from jai_ap_invoice_lines'
2665 , SQL%ROWCOUNT||' ROWS DELETED '
2666 );
2667 FND_LOG.STRING ( ln_proc_level
2668 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2669 , 'Exit procedure'
2670 );
2671 END IF; --( ln_proc_level >= ln_dbg_level )
2672
2673 EXCEPTION
2674 WHEN OTHERS THEN
2675 IF ( ln_proc_level >= ln_dbg_level)
2676 THEN
2677 FND_LOG.STRING ( ln_proc_level
2678 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2679 || '. Other_Exception '
2680 , SQLCODE || ':' || SQLERRM
2681 );
2682 END IF; --( ln_proc_level >= ln_dbg_level) ;
2683 END Delete_Tax_Lines;
2684
2685 --==========================================================================
2686 -- PROCEDURE NAME:
2687 --
2688 -- PROCEDURE delete_useless_line Private
2689 --
2690 -- DESCRIPTION:
2691 --
2692 -- For a given invoice id ,delete all lines that are not related to the
2693 -- invoice. Both item lines and tax line in starndard AP and JAI
2694 -- AP module are deleted
2695 --
2696 -- PARAMETERS:
2697 -- In: pn_invoice_id NUMBER invoice id
2698 --
2699 -- Out:
2700 --
2701 --
2702 -- DESIGN REFERENCES:
2703 -- AP Technical Design 2.1.doc
2704 --
2705 -- CHANGE HISTORY:
2706 --
2707 -- 1 23-Aug-2007 Eric Ma Created
2708 -- 2 30-Nov-2007 Eric Ma Modified for inclusive tax
2709 --===========================================================================
2710 PROCEDURE Delete_Useless_Lines (pn_invoice_id IN NUMBER)
2711 IS
2712 ln_invoice_id NUMBER := pn_invoice_id;
2713 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2714 ln_proc_level NUMBER := FND_LOG.level_procedure;
2715 lv_proc_name VARCHAR2 (100) := 'Delete_Useless_Lines';
2716 BEGIN
2717 --log for debug
2718 IF ( ln_proc_level >= ln_dbg_level)
2719 THEN
2720 FND_LOG.STRING ( ln_proc_level
2721 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2722 , 'Enter procedure'
2723 );
2724 FND_LOG.STRING ( ln_proc_level
2725 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2726 , 'pn_invoice_id ' || pn_invoice_id
2727 );
2728 END IF; --( ln_proc_level >= ln_dbg_level )
2729
2730 -- when a item line is deleted from the Ap invoice work bench,
2731 -- the related tax rows have to be deleted from other 4 tables as well
2732 -- and the corresoponding item row in jai_ap_invoice_lines need to be erased
2733 -- either
2734
2735 --delete all tax lines not attached to a item that exists in AP Inv Line
2736 --table from jai_cmn_document_taxes
2737 DELETE
2738 FROM
2739 jai_cmn_document_taxes jcdt
2740 WHERE jcdt.source_doc_id = ln_invoice_id
2741 AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
2742 --modified by eric for inclusive taxes
2743 ----------------------------------------------------------------
2744 AND NOT EXISTS
2745 (
2746 SELECT
2747 'X'
2748 FROM
2749 ap_invoice_lines_all aila
2750 WHERE aila.invoice_id = ln_invoice_id
2751 AND aila.line_number = jcdt.source_doc_parent_line_no
2752 );
2753 --end of modification by eric for inclusive taxes-----------------
2754
2755 /*commented out by eric for inclusive taxes
2756
2757 AND EXISTS
2758 (
2759 SELECT
2760 'X'
2761 FROM
2762 jai_ap_invoice_lines jail
2763 WHERE jail.invoice_id = ln_invoice_id
2764 AND jail.invoice_line_number = jcdt.source_doc_line_id
2765 AND NOT EXISTS
2766 (
2767 SELECT
2768 'X'
2769 FROM
2770 ap_invoice_lines_all aila
2771 WHERE aila.invoice_id = ln_invoice_id
2772 AND aila.line_number =jail.parent_invoice_line_number
2773 )
2774 );
2775 */
2776
2777
2778 -- delete miscellaneous from ap_invoice_distributions_all
2779 DELETE
2780 FROM
2781 ap_invoice_distributions_all aida
2782 WHERE aida.invoice_id = ln_invoice_id
2783 AND EXISTS
2784 (
2785 SELECT
2786 'X'
2787 FROM
2788 jai_ap_invoice_lines jail
2789 WHERE invoice_id = ln_invoice_id
2790 AND jail.invoice_line_number = aida.invoice_line_number
2791 AND NOT EXISTS
2792 (
2793 SELECT
2794 'X'
2795 FROM
2796 ap_invoice_lines_all aila
2797 WHERE aila.invoice_id = ln_invoice_id
2798 AND (aila.line_number =jail.parent_invoice_line_number OR aila.line_type_lookup_code = GV_CONSTANT_ITEM) --added by Bgowrava for Bug#9387830
2799 )
2800 )
2801 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
2802
2803 --delete miscellaneous lines in ap_invoice_lines_all
2804 DELETE
2805 FROM
2806 ap_invoice_lines_all aila
2807 WHERE aila.invoice_id = ln_invoice_id
2808 AND EXISTS
2809 (
2810 SELECT
2811 'X'
2812 FROM
2813 jai_ap_invoice_lines jail
2814 WHERE jail.invoice_id = ln_invoice_id
2815 AND jail.invoice_line_number = aila.line_number
2816 AND NOT EXISTS
2817 (
2818 SELECT
2819 'X'
2820 FROM
2821 ap_invoice_lines_all aila
2822 WHERE aila.invoice_id =ln_invoice_id
2823 AND aila.line_number = jail.parent_invoice_line_number
2824 )
2825 )
2826 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
2827
2828 -- delete ITEM lines from jai_ap_invoice_lines
2829 DELETE
2830 FROM
2831 jai_ap_invoice_lines jail
2832 WHERE jail.invoice_id = ln_invoice_id
2833 AND NOT EXISTS
2834 (
2835 SELECT
2836 line_number
2840 AND aila.line_number = jail.invoice_line_number
2837 FROM
2838 ap_invoice_lines_all aila
2839 WHERE aila.invoice_id = ln_invoice_id
2841 )
2842 AND line_type_lookup_code = GV_CONSTANT_ITEM;
2843
2844 -- delete miscelleaneous line from jai_ap_invoice_lines
2845 DELETE
2846 FROM
2847 jai_ap_invoice_lines jail
2848 WHERE
2849 jail.invoice_id = ln_invoice_id
2850 AND NOT EXISTS
2851 (
2852 SELECT
2853 'X'
2854 FROM
2855 ap_invoice_lines_all aila
2856 WHERE aila.invoice_id =ln_invoice_id
2857 AND aila.line_number = jail.parent_invoice_line_number
2858 )
2859 AND line_type_lookup_code = GV_CONSTANT_MISCELLANEOUS;
2860
2861 --log for debug
2862 IF ( ln_proc_level >= ln_dbg_level)
2863 THEN
2864 FND_LOG.STRING ( ln_proc_level
2865 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2866 , 'Exit procedure'
2867 );
2868 END IF; --( ln_proc_level >= ln_dbg_level )
2869 EXCEPTION
2870 WHEN OTHERS THEN
2871 IF ( ln_proc_level >= ln_dbg_level)
2872 THEN
2873 FND_LOG.STRING ( ln_proc_level
2874 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2875 || '. Other_Exception '
2876 , SQLCODE || ':' || SQLERRM
2877 );
2878 END IF; --( ln_proc_level >= ln_dbg_level) ;
2879 END Delete_Useless_Lines;
2880
2881 --==========================================================================
2882 -- PROCEDURE NAME:
2883 --
2884 -- Update_Jai_Line_Amount Private
2885 --
2886 -- DESCRIPTION:
2887 --
2888 -- update item lines in jai_ap_invoice_lines table,tax category,
2889 -- location_id, can be changed from IL form while line amount, currency,
2890 -- vendor_site_id, are only allowed to be modified from AP invoice work
2891 -- bench.
2892 --
2893 -- PARAMETERS:
2894 -- In: pn_invoice_id NUMBER
2895 -- pn_line_number NUMBER
2896 -- pn_line_amount NUMBER
2897 --
2898 -- Out:
2899 --
2900 --
2901 -- DESIGN REFERENCES:
2902 -- AP Technical Design 2.1.doc
2903 --
2904 -- CHANGE HISTORY:
2905 --
2906 -- 1 09-SEP-2007 Eric Ma Created
2907 --
2908 --===========================================================================
2909 PROCEDURE Update_Jai_Line_Amount
2910 ( pn_invoice_id IN NUMBER
2911 , pn_line_number IN NUMBER
2912 , pn_line_amount IN NUMBER
2913 )
2914 IS
2915 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
2916 ln_proc_level NUMBER := FND_LOG.level_procedure;
2917 lv_proc_name VARCHAR2 (100) := 'Update_Jai_Line_Amount';
2918 BEGIN
2919 IF ( ln_proc_level >= ln_dbg_level)
2920 THEN
2921 FND_LOG.STRING ( ln_proc_level
2922 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
2923 , 'Enter procedure'
2924 );
2925 FND_LOG.STRING ( ln_proc_level
2926 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2927 , 'pn_invoice_id ' || pn_invoice_id
2928 );
2929 FND_LOG.STRING ( ln_proc_level
2930 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2931 , 'pn_line_number ' || pn_line_number
2932 );
2933 FND_LOG.STRING ( ln_proc_level
2934 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
2935 , 'pn_line_amount ' || pn_line_amount
2936 );
2937 END IF; --( ln_proc_level >= ln_dbg_level )
2938
2939 UPDATE
2940 jai_ap_invoice_lines
2941 SET
2942 line_amount = pn_line_amount
2943 where invoice_id = pn_invoice_id
2944 AND invoice_line_number = pn_line_number ;
2945
2946 --log for debug
2947 IF ( ln_proc_level >= ln_dbg_level)
2948 THEN
2949 FND_LOG.STRING ( ln_proc_level
2950 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
2951 || '.DML (UPDATE jai_ap_invoice_lines)'
2952 ,SQL%ROWCOUNT || ' ROWS UPDATED.'
2953 );
2954 FND_LOG.STRING ( ln_proc_level
2955 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
2956 , 'Exit procedure'
2957 );
2958 END IF; --( ln_proc_level >= ln_dbg_level )
2959
2960 EXCEPTION
2961 WHEN OTHERS THEN
2962 IF ( ln_proc_level >= ln_dbg_level)
2963 THEN
2964 FND_LOG.STRING ( ln_proc_level
2965 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
2966 || '. Other_Exception '
2967 , SQLCODE || ':' || SQLERRM
2968 );
2969 END IF; --( ln_proc_level >= ln_dbg_level) ;
2970 END Update_Jai_Line_Amount;
2971
2972 --==========================================================================
2973 -- PROCEDURE NAME:
2974 --
2975 -- Update_Jai_Item_Info Private
2976 --
2977 -- DESCRIPTION:
2978 --
2979 -- update item lines in jai_ap_invoice_lines table,tax category,
2980 -- location_id, can be changed from IL form while line amount, currency,
2981 -- vendor_site_id, are only allowed to be modified from AP invoice work
2982 -- bench.
2983 --
2984 -- PARAMETERS:
2985 -- In: pn_invoice_id NUMBER
2986 -- pn_vndr_site_id NUMBER
2987 -- pn_currency_code NUMBER
2988 --
2989 -- Out:
2990 --
2991 --
2992 -- DESIGN REFERENCES:
2993 -- AP Technical Design 2.1.doc
2994 --
2995 -- CHANGE HISTORY:
2996 --
2997 -- 1 09-SEP-2007 Eric Ma Created
2998 --
2999 --===========================================================================
3000 PROCEDURE Update_Jai_Item_Info
3001 ( pn_invoice_id IN NUMBER
3002 , pn_vndr_site_id IN NUMBER
3003 , pn_currency_code IN VARCHAR2
3004 , pn_tax_category_id IN NUMBER
3005 )
3006 IS
3007 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
3008 ln_proc_level NUMBER := FND_LOG.level_procedure;
3009 lv_proc_name VARCHAR2 (100) := 'Update_Jai_Item_Info';
3010 BEGIN
3011 IF ( ln_proc_level >= ln_dbg_level)
3012 THEN
3013 FND_LOG.STRING ( ln_proc_level
3014 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
3015 , 'Enter procedure'
3016 );
3017 FND_LOG.STRING ( ln_proc_level
3018 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3019 , 'pn_invoice_id ' || pn_invoice_id
3020 );
3021 FND_LOG.STRING ( ln_proc_level
3022 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3023 , 'pn_vndr_site_id ' || pn_vndr_site_id
3024 );
3025 FND_LOG.STRING ( ln_proc_level
3026 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3027 , 'pn_currency_code ' || pn_currency_code
3028 );
3029 FND_LOG.STRING ( ln_proc_level
3030 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3031 , 'pn_tax_category_id ' || pn_tax_category_id
3032 );
3033 END IF; --( ln_proc_level >= ln_dbg_level )
3034
3035
3036 UPDATE
3037 jai_ap_invoice_lines
3038 SET
3039 supplier_site_id = pn_vndr_site_id
3040 , currency_code = pn_currency_code
3041 , tax_category_id = pn_tax_category_id
3042 WHERE invoice_id = pn_invoice_id
3043 AND line_type_lookup_code = GV_CONSTANT_ITEM ;
3044
3045 --log for debug
3046 IF ( ln_proc_level >= ln_dbg_level)
3047 THEN
3048 FND_LOG.STRING ( ln_proc_level
3049 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
3050 || '.DML (UPDATE jai_ap_invoice_lines)'
3051 ,SQL%ROWCOUNT || ' ROWS UPDATED.'
3052 );
3053 FND_LOG.STRING ( ln_proc_level
3054 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
3055 , 'Exit procedure'
3056 );
3057 END IF; --( ln_proc_level >= ln_dbg_level )
3058
3059 EXCEPTION
3060 WHEN OTHERS THEN
3061 IF ( ln_proc_level >= ln_dbg_level)
3062 THEN
3063 FND_LOG.STRING ( ln_proc_level
3064 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
3065 || '. Other_Exception '
3066 , SQLCODE || ':' || SQLERRM
3067 );
3068 END IF; --( ln_proc_level >= ln_dbg_level) ;
3069 END Update_Jai_Item_Info;
3070
3071 --==========================================================================
3072 -- PROCEDURE NAME:
3073 --
3074 -- UPDATE_CCID Public
3075 --
3076 -- DESCRIPTION:
3077 --
3078 -- This procedure updates the Distribution Accounts based on Regime Setup
3079 -- if Organization or Location are changed
3080 --
3081 -- PARAMETERS:
3082 -- In: pn_invoice_id number
3083 -- pn_line_number NUMBER
3084 -- pn_org_id NUMBER
3085 -- pn_location_id NUMBER
3086 --
3087 --
3088 -- Out:
3089 --
3090 -- CHANGE HISTORY:
3091 --
3092 -- 1 10-Jun-2010 Created - Added for Bug 9626284
3093 -- 2 20-DEC-2012 Changed by Qiong for reverse charge bug#16001407
3094 -- Update get_tax_cur cursor to skip reverse charge service tax line.
3095 -- Call new created JAI_AP_ST_REVERSE_PROCESS.UPDATE_CCID to process
3096 -- reverse charge service tax line.
3097 --===========================================================================
3098
3099 PROCEDURE UPDATE_CCID(pn_invoice_id IN NUMBER,
3100 pn_line_number IN NUMBER,
3101 pn_org_id IN NUMBER,
3102 pn_location_id IN NUMBER) IS
3103
3104 ln_chargeble_acct_ccid NUMBER;
3105 ln_dist_acct_ccid NUMBER;
3106 lv_account_type VARCHAR2(1);
3107 lv_tax_type VARCHAR2(2);
3108 lv_assets_tracking_flag VARCHAR2(1);
3109
3110
3111 CURSOR c_jai_cmn_document_taxes
3112 IS
3113 SELECT *
3114 FROM jai_cmn_document_taxes
3115 WHERE source_doc_type = 'STANDALONE_INVOICE'
3116 AND source_table_name = 'JAI_AP_INVOICE_LINES'
3117 AND source_doc_id = pn_invoice_id
3118 AND source_doc_parent_line_no = pn_line_number;
3119
3120 CURSOR get_tax_cur (pn_tax_id NUMBER) IS
3121 SELECT
3122 tax_name,
3123 tax_account_id,
3124 mod_cr_percentage,
3125 adhoc_flag,
3126 NVL (tax_rate, -1) tax_rate,
3127 tax_type,
3128 NVL(reverse_charge_flag,'N') reverse_charge_flag, --Added by Qiong for reverse charge bug#16001407
3129 NVL(rounding_factor,0) rounding_factor
3130 FROM jai_cmn_taxes_all
3131 WHERE tax_id = pn_tax_id;
3132
3133 /* changes done by abhidutt for bug 12358850. AP_INVOICE_DISTRIBUTIONS_ALL fetches no records when we directly enter Default-Distribution-Account without using distribution button for the invoice line
3134 in payables invoices. Added the cursor "get_assets_tracking_flag" so that asset_tracking_flag value will be fetched from the cursor instead of fetching it from SELECT query directly */
3135
3136 CURSOR get_assets_tracking_flag
3137 IS
3138 SELECT assets_tracking_flag
3139 FROM ap_invoice_distributions_all
3140 WHERE invoice_id = pn_invoice_id
3141 AND invoice_line_number = pn_line_number
3142 AND distribution_line_number = (select min(distribution_line_number) from ap_invoice_distributions_all
3143 where invoice_id = pn_invoice_id AND invoice_line_number = pn_line_number) ; /* Bug 13617527 */
3144
3145 tax_rec get_tax_cur%ROWTYPE;
3146 BEGIN
3147 IF (pn_org_id IS NOT NULL AND pn_location_id IS NOT NULL)
3148 THEN
3149 FOR r_jai_cmn_document_taxes IN c_jai_cmn_document_taxes
3150 LOOP
3151
3152 OPEN get_tax_cur (r_jai_cmn_document_taxes.tax_id);
3153 FETCH get_tax_cur INTO tax_rec;
3154 CLOSE get_tax_cur;
3155 -- Added by Qiong for reverse charge bug#16001407
3156 ----------------------------------------------------------------------
3157 IF tax_rec.reverse_charge_flag = 'Y' THEN
3158 Continue;
3159 END IF;
3160 ----------------------------------------------------------------------
3161 -- Added by Qiong for reverse charge bug#16001407
3162 lv_tax_type := jai_ap_stnd_tax_process.get_tax_type
3163 ( pv_modvat_flag => r_jai_cmn_document_taxes.modvat_flag,
3164 pn_cr_percentage => tax_rec.mod_cr_percentage
3165 );
3166 ln_dist_acct_ccid :=
3167 jai_ap_stnd_tax_process.get_dist_account_ccid
3168 ( pn_invoice_id => pn_invoice_id,
3169 pn_item_line_number => pn_line_number,
3170 pn_organization_id => pn_org_id,
3171 pn_location_id => pn_location_id,
3172 pn_tax_type_code => tax_rec.tax_type,
3173 pn_tax_acct_ccid => tax_rec.tax_account_id,
3174 pv_tax_type => lv_tax_type
3175 );
3176 /* commented by abhidutt for bug 12358850 SELECT assets_tracking_flag INTO lv_assets_tracking_flag
3177 FROM ap_invoice_distributions_all
3178 WHERE invoice_id = pn_invoice_id
3179 AND invoice_line_number = pn_line_number
3180 AND distribution_line_number = 1; */
3181
3182
3183 OPEN get_assets_tracking_flag;
3184 FETCH get_assets_tracking_flag INTO lv_assets_tracking_flag;
3185 CLOSE get_assets_tracking_flag;
3186
3187 IF (lv_assets_tracking_flag = 'N')
3188 THEN
3189 ln_chargeble_acct_ccid :=NULL;
3190 ELSE
3191 lv_account_type := jai_ap_stnd_tax_process.get_gl_account_type (ln_dist_acct_ccid);
3192
3193 IF lv_account_type ='A'
3194 THEN
3195 ln_chargeble_acct_ccid := ln_dist_acct_ccid;
3196 ELSE
3197 ln_chargeble_acct_ccid := NULL;
3198 END IF;
3199 END IF;
3200
3201 UPDATE ap_invoice_distributions_all
3202 SET dist_code_combination_id = ln_dist_acct_ccid
3203 --charge_applicable_to_dist_id = ln_chargeble_acct_ccid /* Bug 13793724 -commented as the charge_applicable_to_dist_id is not dependent on ccid */
3204 WHERE invoice_line_number = r_jai_cmn_document_taxes.source_doc_line_id
3205 AND invoice_id = r_jai_cmn_document_taxes.source_doc_id
3206 AND distribution_line_number = (select min(distribution_line_number) from ap_invoice_distributions_all where
3207 invoice_line_number = r_jai_cmn_document_taxes.source_doc_line_id
3208 AND invoice_id = r_jai_cmn_document_taxes.source_doc_id); /* Bug 13617527 */
3209
3210 END LOOP;
3211 COMMIT;
3212 -- Added by Qiong for reverse charge bug#16001407
3213 ----------------------------------------------------------------------
3214 JAI_AP_ST_REVERSE_PROCESS.UPDATE_CCID(pn_invoice_id,
3215 pn_line_number,
3216 pn_org_id,
3217 pn_location_id);
3218 ----------------------------------------------------------------------
3219 -- Added by Chong for Qiong for reverse charge bug#16001407
3220 END IF;
3221 END UPDATE_CCID;
3222
3223 --==========================================================================
3224 -- PROCEDURE NAME:
3225 --
3226 -- Populate_Stnd_Inv_Taxes Public
3227 --
3228 -- DESCRIPTION:
3229 --
3230 -- This procedure is main entrance procedure used by form JAINAPST and
3231 -- standard AP invoice workbench.it invokes the procedure create_tax_line
3232 -- to populate the tax lines
3233 --
3234 -- PARAMETERS:
3235 -- In: pn_invoice_id number
3236 -- pn_line_number NUMBER
3237 -- pn_vendor_site_id NUMBER
3238 -- pv_currency VARCHAR2
3239 -- pn_line_amount NUMBER
3240 -- pn_tax_category_id number
3241 -- pv_tax_modified VARCHAR2
3242 --
3243 --
3244 -- Out:
3245 --
3246 --
3247 -- DESIGN REFERENCES:
3248 -- AP Technical Design 2.1.doc
3249 --
3250 -- CHANGE HISTORY:
3251 --
3252 -- 1 23-Aug-2007 Eric Ma Created
3253 -- 2 19-Mar-2008 Changed Default_Calculate_Taxes for bug#6898716
3254 -- 3 23-Apr-2008 Eric Ma Code change in Populate_Stnd_Inv_Taxes for bug6923963
3255 -- 4 03-Feb-2010 JMEENA for bug 9237446
3256 -- Added cursor cur_credit_memo_check to check if the invoice is TDS Credit memo then service tax should not be defaulted.
3257 --vkaranam for bug#10044104
3258 --Issue:SERVICE TAX NOT GET REVERSED WHEN AP INVOICE IS CANCELLED
3259 --Reason :Issue is with the JAI_AP_STND_TAX_PROCESS.Populate_Stnd_Inv_Taxes:
3260 --
3261 --FOR diff_inv_lines_rec IN diff_inv_lines_cur
3262 -- LOOP
3263
3264 ---
3265 -- Delete_Tax_Lines
3266 -- ( pn_invoice_id => ln_std_invoice_id
3267 -- , pn_line_number => diff_inv_lines_rec.line_number
3268 -- );
3269
3270 -- delete_tax_lines is deleting the misc lines.
3271 --fix : modified Populate_Stnd_Inv_Taxes ,such that deletion of taxes will not happen for cancellation.
3272 --===========================================================================
3273 PROCEDURE Populate_Stnd_Inv_Taxes
3274 ( pn_invoice_id NUMBER
3275 , pn_line_number NUMBER
3276 , pn_vendor_site_id NUMBER
3277 , pv_currency VARCHAR2
3278 , pn_line_amount NUMBER DEFAULT NULL
3279 , pn_tax_category_id NUMBER DEFAULT NULL
3280 , pv_tax_modified VARCHAR2
3281 , pn_old_tax_category_id VARCHAR2
3282 )
3283
3284 IS
3285 ln_std_invoice_id NUMBER := pn_invoice_id;
3286 ln_std_line_number NUMBER := pn_line_number;
3287 ln_std_vendor_site_id NUMBER := pn_vendor_site_id;
3288 lv_std_currency_code VARCHAR2 (15) := pv_currency;
3289 ln_std_tax_category_id NUMBER := pn_tax_category_id;
3290 ln_jai_tax_line_ctg_id NUMBER;
3291 lv_std_tax_modified VARCHAR2 (1) := pv_tax_modified;
3292 ln_jai_vndr_site_id NUMBER := NULL;
3293 lv_jai_currency_code VARCHAR2 (15) := NULL;
3294 ln_jai_tax_category_id NUMBER := NULL;
3295 ln_jai_line_amount NUMBER := NULL;
3296 ln_vendor_id NUMBER;
3297 ln_vendor_site_id NUMBER;
3298 lv_currency_code VARCHAR2 (15);
3299 ln_exchange_rate NUMBER;
3300 lv_service_type_code VARCHAR2 (30);
3301 ln_batch_id NUMBER;
3302 ln_tax_category_id NUMBER;
3303 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
3304 ln_proc_level NUMBER := FND_LOG.level_procedure;
3305 lv_proc_name VARCHAR2 (100) := 'Populate_Stnd_Inv_Taxes';
3306 ln_supplier_id NUMBER;
3307 l_chk_del_flag VARCHAR2(1) ;
3308 ln_tax_line_no NUMBER; -- Added by eric ma for the bug 6898716 on Mar 19,2008
3309 LN_CANCEL_CNT number;--bug#10044104
3310 -- Get the details of inv in the IL table
3311
3312 CURSOR jai_invoice_exist_cur IS
3313 SELECT
3314 supplier_site_id
3315 , currency_code
3316 , tax_category_id
3317 , line_amount
3318 FROM
3319 jai_ap_invoice_lines
3320 WHERE invoice_id = pn_invoice_id
3321 AND invoice_line_number = NVL (pn_line_number, invoice_line_number)
3322 AND line_type_lookup_code = GV_CONSTANT_ITEM;
3323
3324 CURSOR jai_tax_line_ctg_cur IS
3325 SELECT
3326 tax_category_id
3327 FROM
3328 jai_ap_invoice_lines
3329 WHERE invoice_id = pn_invoice_id
3330 AND parent_invoice_line_number = pn_line_number;
3331
3332
3333
3334 -- Get the details of supplier id
3335 CURSOR jai_get_supplier_id (pn_invoice_id NUMBER) IS
3336 SELECT vendor_id
3337 FROM ap_invoices_all
3338 WHERE invoice_id = pn_invoice_id;
3339
3340
3341 -- Get the tax category_id of supplier id
3342 cursor get_setup_tax_category_id ( p_supplier_id number , p_supplier_site_id number) is
3343 select tax_category_id from jai_cmn_vendor_sites where vendor_id =p_supplier_id
3344 and vendor_site_id = p_supplier_site_id;
3345
3346
3347
3348 --Get the changed amount in the invoice line level
3349 --part 1 is the case of line amount changed
3350 --part 2 is the case of line added or deleted
3351 CURSOR diff_inv_lines_cur IS
3352 SELECT
3353 apia.line_number line_number
3354 , apia.amount line_amount
3355 FROM
3356 ap_invoice_lines_all apia
3357 , jai_ap_invoice_lines jail
3358 WHERE apia.invoice_id = jail.invoice_id
3359 AND apia.line_number = jail.invoice_line_number
3360 AND apia.invoice_id = ln_std_invoice_id
3361 AND apia.amount <> jail.line_amount
3362 AND apia.line_type_lookup_code = jail.line_type_lookup_code
3363 AND apia.line_type_lookup_code = GV_CONSTANT_ITEM
3364 --Added by Qiong for bug15875609 begin
3365 AND EXISTS
3366 (
3367 SELECT 'X'
3368 FROM ap_invoice_distributions_all aida
3369 WHERE aida.invoice_id = apia.invoice_id
3370 AND aida.invoice_line_number = apia.line_number
3371 )
3372 --Added by Qiong for bug15875609 end
3373 UNION ALL
3374
3375 SELECT
3376 apia.line_number line_number
3377 , apia.amount line_amount
3378 FROM
3379 ap_invoice_lines_all apia
3380 , jai_ap_invoice_lines jail
3381 WHERE apia.invoice_id = jail.invoice_id (+)
3382 AND apia.line_number = jail.invoice_line_number(+)
3383 AND apia.invoice_id = ln_std_invoice_id
3384 AND apia.line_type_lookup_code = GV_CONSTANT_ITEM
3385 AND jail.invoice_id IS NULL
3386 AND jail.invoice_line_number IS NULL
3387 --Added by Qiong for bug15875609 begin
3388 AND EXISTS
3389 (
3390 SELECT 'X'
3391 FROM ap_invoice_distributions_all aida
3392 WHERE aida.invoice_id = apia.invoice_id
3393 AND aida.invoice_line_number = apia.line_number
3394 ) ;
3395 --Added by Qiong for bug15875609 end
3396 --Added for bug#9237446 by JMEENA
3397 CURSOR cur_credit_memo_check( p_invoice_id number) IS
3398 Select invoice_amount, source
3399 from AP_INVOICES_ALL
3400 where invoice_id = p_invoice_id;
3401
3402 CURSOR c_invoice_id IS -- for bug 14681650 by anupgupt
3403 SELECT invoice_id
3404 FROM ap_invoice_lines_all
3405 WHERE invoice_id = pn_invoice_id
3406 FOR UPDATE;
3407
3408 v_invoice_amount NUMBER;
3409 v_invoice_source VARCHAR2(200);
3410 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 begin
3411 v_cancel_flag BOOLEAN := FALSE; --Added by Chong for ZX investigation 20120816
3412 -- code ported from Bl12.1.3 by zhiwei.xin on 15-MAR-2013 end.
3413 --End bug#9237446
3414 BEGIN
3415 --log for debug
3416 IF ( ln_proc_level >= ln_dbg_level)
3417 THEN
3418 FND_LOG.STRING ( ln_proc_level
3419 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
3420 , 'Enter procedure'
3421 );
3422 FND_LOG.STRING ( ln_proc_level
3423 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3424 , 'pn_invoice_id ' || pn_invoice_id
3425 );
3426 FND_LOG.STRING ( ln_proc_level
3427 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3428 , 'pn_line_number ' || pn_line_number
3429 );
3430 FND_LOG.STRING ( ln_proc_level
3431 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3432 , 'pn_vendor_site_id ' || pn_vendor_site_id
3433 );
3434 FND_LOG.STRING ( ln_proc_level
3435 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3436 , 'pv_currency ' || pv_currency
3437 );
3438 FND_LOG.STRING ( ln_proc_level
3439 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3440 , 'pn_line_amount ' || pn_line_amount
3441 );
3442 FND_LOG.STRING ( ln_proc_level
3443 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3444 , 'pn_tax_category_id ' || pn_tax_category_id
3445 );
3446 FND_LOG.STRING ( ln_proc_level
3447 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3448 , 'pv_tax_modified ' || pv_tax_modified
3449 );
3450 FND_LOG.STRING ( ln_proc_level
3451 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3452 , 'pn_old_tax_category_id ' || pn_old_tax_category_id
3453 );
3454 END IF; --( ln_proc_level >= ln_dbg_level)
3455
3456 --insert into eric_log values ( 0.1,'pn_invoice_id :'||pn_invoice_id,sysdate);
3457 --insert into eric_log values ( 0.2,'pn_line_number :'||pn_line_number,sysdate);
3458 --insert into eric_log values ( 0.3,'pn_vendor_site_id :'||pn_vendor_site_id,sysdate);
3459 --insert into eric_log values ( 0.4,'pv_currency :'||pv_currency,sysdate);
3460 --insert into eric_log values ( 0.5,'pn_line_amount :'||pn_line_amount,sysdate);
3461 --insert into eric_log values ( 0.6,'pn_tax_category_id :'||pn_tax_category_id,sysdate);
3462 --insert into eric_log values ( 0.7,'pv_tax_modified :'||pv_tax_modified,sysdate);
3463 --Added for bug#9237446 by JMEENA
3464
3465 OPEN c_invoice_id; -- for bug 14681650 by anupgupt
3466
3467 OPEN cur_credit_memo_check (pn_invoice_id );
3468 FETCH cur_credit_memo_check INTO v_invoice_amount, v_invoice_source;
3469 CLOSE cur_credit_memo_check;
3470 IF ( ln_proc_level >= ln_dbg_level)
3471 THEN
3472 FND_LOG.STRING ( ln_proc_level
3473 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3474 , 'v_invoice_amount ' || v_invoice_amount||'-v_invoice_source:'||v_invoice_source
3475 );
3476 END IF;
3477 IF NVL(v_invoice_amount, 0) < 0 AND v_invoice_source = 'INDIA TDS' THEN
3478 IF ( ln_proc_level >= ln_dbg_level)
3479 THEN
3480 FND_LOG.STRING ( ln_proc_level
3481 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
3482 , 'Invoice is TDS Credit Memo so not defaulting Service tax'
3483 );
3484 END IF;
3485 RETURN;
3486 END IF;
3487 --End bug#9237446
3488
3489 --Commented by Chong.Lei for POT code port begin
3490 /*
3491 -- if any of item line fails the mandatory distirbution lines validation
3492 -- no tax lines will be processed
3493 IF ((Validate_Item_Dist_Lines( ln_std_invoice_id))AND (NOT Validate_3rd_party_cm_Invoice(ln_std_invoice_id)) )
3494 -- , ln_std_line_number)) --Added a parameter for validate function by Jia Li on 2008/01/25
3495 -- according eakta's require.
3496 --,commented out for the bug of deleting not working on Jan 28,2008
3497 */
3498 --Commented by Chong.Lei for POT code port end
3499 -- Added by Chong.Lei for POT code port begin
3500 ------------------------------------------------------------------------------------
3501 -- if invoice is 3rd party payment cm invoice
3502 -- no tax lines will be generated
3503
3504 -- if invoice is prepayment invoice ,no tax lines should not be generated or Added from OFI Tax UI
3505 -- Added by Eric Ma for Bug 12943892
3506
3507 IF ((Validate_Item_Dist_Lines( ln_std_invoice_id))
3508 AND (NOT Validate_3rd_party_cm_Invoice(ln_std_invoice_id))
3509 AND (NOT Validate_Prepayment_Invoice(ln_std_invoice_id))
3510 )
3511 ------------------------------------------------------------------------------------
3512 -- Added by Chong.Lei for POT code port end
3513 THEN
3514
3515 --insert into eric_log values ( 1.1,'Item_Dist_Lines_Validated',sysdate);
3516
3517 --only the tax_category_id in the cursor can be modified in the IL form
3518 --if the current function is invoked by IL form, line_number will be
3519 --specified. in the standard AP invoice form only line amount is changed
3520 --in the line level while vndr_site_id or currency_code are in header level
3521 --we need fetch one line rather than loop every item lines here
3522
3523 OPEN jai_invoice_exist_cur;
3524 FETCH jai_invoice_exist_cur
3525 INTO
3526 ln_jai_vndr_site_id
3527 , lv_jai_currency_code
3528 , ln_jai_tax_category_id
3529 , ln_jai_line_amount;
3530 CLOSE jai_invoice_exist_cur;
3531 --insert into eric_log values ( 1.2,'ln_jai_vndr_site_id :'||ln_jai_vndr_site_id,sysdate);
3532 --insert into eric_log values ( 1.3,'lv_jai_currency_code :'||lv_jai_currency_code,sysdate);
3533 --insert into eric_log values ( 1.4,'ln_jai_tax_category_id :'||ln_jai_tax_category_id,sysdate);
3534 --insert into eric_log values ( 1.5,'ln_jai_line_amount :'||ln_jai_line_amount,sysdate);
3535 OPEN jai_tax_line_ctg_cur;
3536 FETCH jai_tax_line_ctg_cur
3537 INTO
3538 ln_jai_tax_line_ctg_id ;
3539 CLOSE jai_tax_line_ctg_cur;
3540 --insert into eric_log values ( 1.6,'ln_jai_tax_line_ctg_id :'||ln_jai_tax_line_ctg_id,sysdate);
3541
3542 OPEN jai_get_supplier_id (pn_invoice_id);
3543 FETCH jai_get_supplier_id into ln_supplier_id;
3544 close jai_get_supplier_id;
3545 --insert into eric_log values ( 1.7,'ln_supplier_id :'||ln_supplier_id,sysdate);
3546
3547 IF((ln_jai_tax_line_ctg_id IS NULL) AND (pn_old_tax_category_id IS NOT NULL))
3548 THEN
3549 ln_jai_tax_line_ctg_id := pn_old_tax_category_id ;
3550 END IF;
3551
3552 --insert into eric_log values ( 1.8,'ln_jai_tax_line_ctg_id :'||ln_jai_tax_line_ctg_id,sysdate);
3553
3554 --added by eric ma on Mar 19 ,2008 for the bug 6898716 ,begin
3555 ---------------------------------------------------------------------------
3556 SELECT
3557 COUNT(*)
3558 INTO
3559 ln_tax_line_no
3560 FROM
3561 jai_cmn_document_taxes jcdt
3562 WHERE jcdt.source_doc_id = pn_invoice_id
3563 AND jcdt.source_doc_parent_line_no = pn_line_number
3564 AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice;
3565
3566
3567 IF ln_tax_line_no > 0
3568 THEN
3569 GV_LINES_CREATEED := 'YES';
3570 --added by eric for bug# 6923963 on Apr 23,2008,begin
3571 ------------------------------------------------------
3572 ELSE
3573 GV_LINES_CREATEED := 'NO';
3574 ----------------------------------------------------
3575 --added by eric for bug# 6923963 on Apr 23,2008,end
3576 END IF;
3577
3578 --insert into eric_log values ( 1.9,'GV_LINES_CREATEED :'||GV_LINES_CREATEED,sysdate) ;
3579 ---------------------------------------------------------------------------
3580 --added by eric ma on Mar 19 ,2008 for the bug 6898716 ,end
3581
3582 -- standalone invoice insert is going to happen in the following cases
3583
3584 -- 1. create a new invoice from ap invoice workbench
3585 -- 2. attach a tax category to an existing invoice line whose tax ctg is null
3586 -- 3. user again attaches a tax category to an invoice which was previouly removed
3587
3588 IF ((ln_jai_vndr_site_id IS NULL and GV_LINES_CREATEED = 'NO')
3589 --added "and GV_LINES_CREATEED = 'NO'" in the below two lines
3590 --modified by eric ma on Mar 19 ,2008 for the bug 6898716 ,begin
3591 -------------------------------------------------------------
3592 OR (ln_jai_tax_category_id IS NULL AND ln_std_tax_category_id IS NOT NULL and GV_LINES_CREATEED = 'NO' )
3593 OR (ln_jai_tax_line_ctg_id IS NULL AND ln_std_tax_category_id IS NOT NULL and GV_LINES_CREATEED = 'NO' )
3594 )
3595 -------------------------------------------------------------
3596 --modified by eric ma on Mar 19 ,2008 for the bug 6898716 ,end
3597 THEN
3598 IF ( ln_proc_level >= ln_dbg_level)
3599 THEN
3600 FND_LOG.STRING ( ln_proc_level
3601 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
3602 , 'Case 1'
3603 );
3604 END IF;-- ( ln_proc_level >= ln_dbg_level)
3605 --insert into eric_log values ( 2.1,'Case 1',sysdate);
3606
3607 if ln_std_tax_category_id is null then
3608
3609 ln_std_tax_category_id := JAI_AP_IL_ORG_PKG.fun_tax_cat_id (ln_supplier_id ,
3610 ln_std_vendor_site_id ,
3611 ln_std_invoice_id ,
3612 ln_std_line_number );
3613 end if ;
3614 --insert into eric_log values ( 2.11,'ln_std_tax_category_id :'||ln_std_tax_category_id,sysdate);
3615 --insert into eric_log values ( 2.12,'lv_std_tax_modified :'||lv_std_tax_modified,sysdate);
3616 --insert into eric_log values ( 2.13,'ln_std_line_number :'||ln_std_line_number,sysdate);
3617 --insert
3618 Create_Tax_Lines ( pn_organization_id => NULL
3619 , pv_currency => lv_std_currency_code
3620 , pn_location_id => NULL
3621 , pn_invoice_id => ln_std_invoice_id
3622 , pn_line_number => ln_std_line_number
3623 , pn_tax_category_id => ln_std_tax_category_id
3624 , pv_tax_modified => lv_std_tax_modified
3625 );
3626 --insert into eric_log values ( 2.14,'Create_Tax_Lines() done',sysdate);
3627
3628 GV_LINES_CREATEED := 'YES';
3629 --insert into eric_log values ( 2.15,'GV_LINES_CREATEED :'||GV_LINES_CREATEED,sysdate);
3630 END IF;
3631
3632 -- vendor site is changed in AP , currency is updated in AP ,
3633 -- delete the taxes ,update related information in jai_ap_invoice_lines
3634 -- and recalculate tax lines
3635
3636 IF ( ( ln_jai_vndr_site_id <> ln_std_vendor_site_id)
3637 OR ( lv_jai_currency_code <> lv_std_currency_code)
3638 )
3639 THEN
3643 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
3640 IF ( ln_proc_level >= ln_dbg_level)
3641 THEN
3642 FND_LOG.STRING ( ln_proc_level
3644 , 'Case 2'
3645 );
3646 END IF;-- ( ln_proc_level >= ln_dbg_level)
3647
3648 --insert into eric_log values ( 2.2,'Case 2',sysdate);
3649
3650 open get_setup_tax_category_id ( ln_supplier_id , ln_std_vendor_site_id );
3651 fetch get_setup_tax_category_id into ln_std_tax_category_id;
3652 close get_setup_tax_category_id;
3653
3654
3655
3656 Delete_Tax_Lines ( pn_invoice_id => ln_std_invoice_id
3657 , pn_line_number => ln_std_line_number
3658 );
3659
3660
3661 Get_Invoice_Header_Infor ( pn_invoice_id => ln_std_invoice_id
3662 , xn_vendor_id => ln_vendor_id
3663 , xn_vendor_site_id => ln_vendor_site_id
3664 , xv_currency_code => lv_currency_code
3665 , xn_exchange_rate => ln_exchange_rate
3666 , xn_batch_id => ln_batch_id
3667 );
3668
3669 Get_Tax_Cat_Serv_Type ( pn_vendor_id => ln_vendor_id
3670 , pn_vendor_site_id => ln_vendor_site_id
3671 , xn_tax_category_id => ln_std_tax_category_id ---ln_tax_category_id
3672 , xv_service_type_code => lv_service_type_code
3673 );
3674
3675 Update_Jai_Item_Info
3676 ( pn_invoice_id => ln_std_invoice_id
3677 , pn_vndr_site_id => ln_std_vendor_site_id
3678 , pn_currency_code => lv_std_currency_code
3679 , pn_tax_category_id => ln_std_tax_category_id ---ln_tax_category_id
3680 );
3681
3682
3683 /* ln_std_tax_category_id := JAI_AP_IL_ORG_PKG.fun_tax_cat_id (ln_supplier_id ,
3684 ln_std_vendor_site_id ,
3685 ln_std_invoice_id ,
3686 ln_std_line_number ); */
3687
3688
3689
3690 --insert
3691 Create_Tax_Lines ( pn_organization_id => NULL
3692 , pv_currency => lv_std_currency_code
3693 , pn_location_id => NULL
3694 , pn_invoice_id => ln_std_invoice_id
3695 , pn_line_number => ln_std_line_number
3696 , pn_tax_category_id => ln_std_tax_category_id
3697 , pv_tax_modified => lv_std_tax_modified
3698 );
3699
3700 GV_LINES_CREATEED := 'YES';
3701 END IF;--(vndr_site_id changed or currency_code changed)
3702
3703 --as the data may be changed by case 1 , re-selete table for getting the latest values.
3704 --added by eric for bug# 6923963 on Apr 23,2008,begin
3705 ------------------------------------------------------
3706 OPEN jai_invoice_exist_cur;
3707 FETCH jai_invoice_exist_cur
3708 INTO
3709 ln_jai_vndr_site_id
3710 , lv_jai_currency_code
3711 , ln_jai_tax_category_id
3712 , ln_jai_line_amount;
3713 CLOSE jai_invoice_exist_cur;
3714 --insert into eric_log values ( 10.2,'ln_jai_vndr_site_id :'||ln_jai_vndr_site_id,sysdate);
3715 --insert into eric_log values ( 10.3,'lv_jai_currency_code :'||lv_jai_currency_code,sysdate);
3716 --insert into eric_log values ( 10.4,'ln_jai_tax_category_id :'||ln_jai_tax_category_id,sysdate);
3717 --insert into eric_log values ( 10.5,'ln_jai_line_amount :'||ln_jai_line_amount,sysdate);
3718 OPEN jai_tax_line_ctg_cur;
3719 FETCH jai_tax_line_ctg_cur
3720 INTO
3721 ln_jai_tax_line_ctg_id ;
3722 CLOSE jai_tax_line_ctg_cur;
3723 --insert into eric_log values ( 10.6,'ln_jai_tax_line_ctg_id :'||ln_jai_tax_line_ctg_id,sysdate);
3724
3725 ------------------------------------------------------
3726 --added by eric for bug# 6923963 on Apr 23,2008,end
3727
3728
3729
3730 -- tax category changed from IL form
3731 -- tax lines exists but the ctg_id in tax lines are different
3732 -- OR the original tax category is null and tax lines exist
3733 -- from the tax ctgs of item line
3734 IF ( ( ln_jai_tax_line_ctg_id IS NOT NULL
3735 AND ln_std_tax_category_id IS NOT NULL
3736 AND ln_std_tax_category_id <> ln_jai_tax_line_ctg_id
3737 )
3738 --added the below creteria for bug 6898716
3739 --added by eric ma on Mar 19 ,2008 for the bug 6898716 ,begin
3740 -------------------------------------------------------------
3741 OR
3742 ( GV_LINES_CREATEED = 'YES'
3743 AND ln_jai_tax_line_ctg_id IS NULL
3744 AND ln_std_tax_category_id IS NOT NULL
3745 )
3746 -------------------------------------------------------------
3747 --added by eric ma on Mar 19 ,2008 for the bug 6898716 ,end
3748 )
3749 THEN
3750
3751 IF ( ln_proc_level >= ln_dbg_level)
3752 THEN
3753 FND_LOG.STRING ( ln_proc_level
3754 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
3755 , 'Case 3'
3756 );
3757 END IF; -- ( ln_proc_level >= ln_dbg_level)
3758 --insert into eric_log values ( 2.3,'Case 3',sysdate);
3759
3760 Delete_Tax_Lines ( pn_invoice_id => ln_std_invoice_id
3761 , pn_line_number => ln_std_line_number
3762 );
3763
3764 --insert
3765 Create_Tax_Lines ( pn_organization_id => NULL
3766 , pv_currency => lv_std_currency_code
3767 , pn_location_id => NULL
3768 , pn_invoice_id => ln_std_invoice_id
3769 , pn_line_number => ln_std_line_number
3770 , pn_tax_category_id => ln_std_tax_category_id
3771 , pv_tax_modified => lv_std_tax_modified
3772 );
3773
3774 --if the category changed, ignore the changes of tax lines level
3775 lv_std_tax_modified :='N';
3776 END IF;
3777
3778 -- the tax category is updated to null in IL form delete the taxes
3779 -- from all the tables
3780
3781
3782 /* IF ( ln_std_tax_category_id IS NULL
3783 AND ln_jai_tax_line_ctg_id IS NOT NULL
3784 AND GV_LINES_CREATEED = 'NO') THEN */--
3785
3786
3787
3788 if ln_std_tax_category_id IS NULL and ln_jai_tax_line_ctg_id IS NOT NULL then
3789
3790 /* vendor updated and GV_LINES_CREATEED is yes , nothing has to be deleted
3791 l_chk_del_flag = 'Y' is to stop deletion of tax lines due to second call of Post form
3792 commit trigger in APXINWKB */
3793
3794 IF pn_old_tax_category_id is null
3795 THEN
3796 l_chk_del_flag := 'Y' ;
3797
3798 END IF;
3799
3800 if nvl(l_chk_del_flag, '$' ) = 'Y' then
3801
3802 null;
3803 -- invoice is queried and tax-category_id is updated to null
3804 elsif GV_LINES_CREATEED = 'NO' then
3805 Delete_Tax_Lines ( pn_invoice_id => ln_std_invoice_id
3806 , pn_line_number => ln_std_line_number
3807 );
3808
3809 -- new invoice is created and then tax category is set to null without closing the form
3810 elsif GV_LINES_CREATEED = 'YES' then
3811 Delete_Tax_Lines ( pn_invoice_id => ln_std_invoice_id
3812 , pn_line_number => ln_std_line_number
3813 );
3814 end if;
3815
3816 END IF;
3817
3818 -- the tax category remains unchanged but the user enters new taxes
3819 -- DO NOT DELETE lines from jai_cmn_document_taxes
3820 -- delte from other tables and then default
3821 IF ( NVL ( ln_std_tax_category_id, -999) =
3822 NVL ( ln_jai_tax_line_ctg_id, -999)
3823 AND NVL (lv_std_tax_modified, 'N') = 'Y'
3824 )
3825 THEN
3826 --insert into eric_log values ( 2.5,'Case 5',sysdate);
3827
3828 IF ( ln_proc_level >= ln_dbg_level)
3829 THEN
3830 FND_LOG.STRING ( ln_proc_level
3831 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
3832 , 'Case 5'
3833 );
3834 END IF; -- ( ln_proc_level >= ln_dbg_level)
3835
3836 --insert
3837 Create_Tax_Lines ( pn_organization_id => NULL
3838 , pv_currency => lv_std_currency_code
3839 , pn_location_id => NULL
3840 , pn_invoice_id => ln_std_invoice_id
3841 , pn_line_number => ln_std_line_number
3842 , pn_tax_category_id => ln_std_tax_category_id
3843 , pv_tax_modified => lv_std_tax_modified
3844 );
3845 END IF; --( (lv_std_tax_modified, 'N') = 'Y',Modified Tax line in IL form)
3846
3847 -- 1. When the line amount is changed in the standard form
3848 -- program going to this branch
3849 -- 2. A new item line inserted from standard AP form
3850
3851 -- For the first case, program need to update the item line amount in
3852 -- jai_ap_invoice_lines
3853 --get invoice header information
3854
3855
3856 FOR diff_inv_lines_rec IN diff_inv_lines_cur
3857 LOOP
3858
3859 IF ( ln_proc_level >= ln_dbg_level)
3860 THEN
3861 FND_LOG.STRING ( ln_proc_level
3862 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
3863 , 'Case 6'
3864 );
3865 END IF;-- ( ln_proc_level >= ln_dbg_level)
3866 --insert into eric_log values ( 2.6,'Case 6',sysdate);
3867 Get_Invoice_Header_Infor ( ln_std_invoice_id
3868 , ln_vendor_id
3869 , ln_vendor_site_id
3870 , lv_currency_code
3871 , ln_exchange_rate
3872 , ln_batch_id
3873 );
3874 --insert into eric_log values ( 2.61,'ln_vendor_id :'|| ln_vendor_id,sysdate);
3875 --insert into eric_log values ( 2.62,'ln_vendor_site_id :'|| ln_vendor_site_id,sysdate);
3876
3877 Get_Tax_Cat_Serv_Type ( pn_vendor_id => ln_vendor_id
3878 , pn_vendor_site_id => ln_vendor_site_id
3879 , xn_tax_category_id => ln_tax_category_id
3880 , xv_service_type_code => lv_service_type_code
3881 );
3882 --insert into eric_log values ( 2.63,'ln_tax_category_id :'|| ln_tax_category_id,sysdate);
3883 --insert into eric_log values ( 2.64,'lv_service_type_code :'|| lv_service_type_code,sysdate);
3884
3885 --start additions for bug#10044104
3886 select count(*)
3887 INTO LN_CANCEL_CNT
3888 from ap_invoice_lines_all
3889 where cancelled_flag='Y'
3890 AND INVOICE_ID= ln_std_invoice_id
3891 and line_number = diff_inv_lines_rec.line_number; /* Added and condition for bug 14650698 */
3892
3893 IF LN_CANCEL_CNT>0
3894 THEN
3895 update jai_cmn_document_taxes
3896 set
3897 tax_amt=0,
3898 --base_tax_amount=0,
3899 func_tax_amt=0
3900 WHERE source_doc_id = ln_std_invoice_id
3901 AND source_doc_type = jai_constants.g_ap_standalone_invoice
3902 AND source_doc_parent_line_no=
3903 NVL( diff_inv_lines_rec.line_number, source_doc_parent_line_no);
3904 /* Added call to Update_Jai_Line_Amount for bug 14650698 */
3905 Update_Jai_Line_Amount
3906 ( pn_invoice_id => ln_std_invoice_id
3907 , pn_line_number => diff_inv_lines_rec.line_number
3908 , pn_line_amount => diff_inv_lines_rec.line_amount
3909 );
3910 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 begin
3911 --Added by Chong for ZX investigation 20120816 start
3912 ---------------------------------------------------------------------------
3913 --Invoice cancellation comes here.
3914 v_cancel_flag := TRUE;
3915 ---------------------------------------------------------------------------
3916 --Added by Chong for ZX investigation 20120816 end
3917 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 end.
3918 else
3919 Delete_Tax_Lines
3920 ( pn_invoice_id => ln_std_invoice_id
3921 , pn_line_number => diff_inv_lines_rec.line_number
3922 );
3923 -- end if; /* commented for bug 14650698 */
3924 --end bug#10044104
3925 --insert into eric_log values ( 2.65,'ln_std_invoice_id :'|| ln_std_invoice_id,sysdate);
3926 --insert into eric_log values ( 2.66,'line_number :'||diff_inv_lines_rec.line_number,sysdate);
3927
3928
3929 Update_Jai_Line_Amount
3930 ( pn_invoice_id => ln_std_invoice_id
3931 , pn_line_number => diff_inv_lines_rec.line_number
3932 , pn_line_amount => diff_inv_lines_rec.line_amount
3933 );
3934 --insert into eric_log values ( 2.67,'line_amount :'|| diff_inv_lines_rec.line_amount,sysdate);
3935
3936 --insert
3937 Create_Tax_Lines( pn_organization_id => NULL
3938 , pv_currency => lv_std_currency_code
3939 , pn_location_id => NULL
3940 , pn_invoice_id => ln_std_invoice_id
3941 , pn_line_number => diff_inv_lines_rec.line_number
3942 , pn_tax_category_id => ln_tax_category_id
3943 , pv_tax_modified => lv_std_tax_modified
3944 );
3945 end if; /* Commented above end if and added here for bug 14650698 */
3946 END LOOP; --(diff_inv_lines_rec IN diff_inv_lines_cur)
3947
3948 --delete item lines from Standard Invoice Work Bench
3949 --then delete its related taxes invoice lines and tax dist lines
3950 Delete_Useless_Lines ( pn_invoice_id => ln_std_invoice_id );
3951
3955 ( pn_invoice_id => ln_std_invoice_id
3952 --allocate tax amount in distribution lines according to the propotion
3953 --of item distirbution lines
3954 Allocate_Tax_Dist_Lines
3956 , pn_invoice_item_line_number => ln_std_line_number
3957 );
3958 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 begin
3959 --we should not explicitly commit here, for this procedure only invoked in Form UI, commit will break transaction.
3960 --COMMIT; --Commented by Chong for ZX investigation 20120816
3961 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 end.
3962
3963 ELSE -- (Validate_Item_Dist_Lines not passed or Validate_3rd_party_cm_Invoice(ln_std_invoice_id) passed)
3964
3965 --insert into eric_log values ( 1.20,'Error : Item_Dist_Lines_not_Validated or 3rd_party_cm_Invoice_validated',sysdate);
3966 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 begin
3967 jai_cmn_utils_pkg.WRITE_FND_LOG_MSG('JAI.PLSQL.JAI_AP_STND_TAX_PROCESS.Populate_Stnd_Inv_Taxes', 'ELSE ...');
3968 --Added by Chong for ZX investigation 20120816 start
3969 ---------------------------------------------------------------------------
3970 FOR diff_inv_lines_rec IN diff_inv_lines_cur
3971 LOOP
3972 select count(*)
3973 INTO LN_CANCEL_CNT
3974 from ap_invoice_lines_all
3975 where cancelled_flag='Y'
3976 AND INVOICE_ID= ln_std_invoice_id;
3977
3978 IF LN_CANCEL_CNT>0
3979 THEN
3980 v_cancel_flag := TRUE;
3981 ELSE
3982 NULL;
3983 END IF;
3984 END LOOP;
3985 ---------------------------------------------------------------------------
3986 --Added by Chong for ZX investigation 20120816 end
3987 -- code ported from BL12.1.3 by zhiwe.xin on 15-MAR-2013 end.
3988 NULL ;
3989 END IF; -- (Validate_Item_Dist_Lines(ln_std_invoice_id))
3990
3991 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 begin
3992 --Added by Chong for ZX investigation 20120816 start
3993 ---------------------------------------------------------------------------
3994 IF v_cancel_flag = TRUE THEN
3995 --Call ZX warp package to invoke DTC cancellation, if cancel an TDS base invoice.
3996 JAI_TAX_PROCESSING_PKG.ap_dtc_cancel_invoice(pn_invoice_id => ln_std_invoice_id);
3997 END IF;
3998 ---------------------------------------------------------------------------
3999 --Added by Chong for ZX investigation 20120816 end
4000 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 end.
4001 /* Error msg will be populated AP Invoice workbench when changing the data in header
4002 UPDATE
4003 AP_INVOICES_ALL
4004 SET
4005 invoice_amount = (SELECT SUM(amount) FROM AP_INVOICE_LINES_ALL WHERE invoice_id =pn_invoice_id ),
4006 base_amount = (SELECT SUM(base_amount) FROM AP_INVOICE_LINES_ALL WHERE invoice_id =pn_invoice_id )
4007 WHERE invoice_id =pn_invoice_id;
4008 */
4009
4010 CLOSE c_invoice_id; -- for bug 14681650 by anupgupt
4011
4012 --log for debug
4013 IF ( ln_proc_level >= ln_dbg_level)
4014 THEN
4015 FND_LOG.STRING ( ln_proc_level
4016 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
4017 , 'Exit procedure'
4018 );
4019 END IF; --( ln_proc_level >= ln_dbg_level )
4020 EXCEPTION
4021 WHEN OTHERS THEN
4022 IF ( ln_proc_level >= ln_dbg_level)
4023 THEN
4024 FND_LOG.STRING ( ln_proc_level
4025 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
4026 || '. Other_Exception '
4027 , SQLCODE || ':' || SQLERRM
4028 );
4029 END IF; --( ln_proc_level >= ln_dbg_level) ;
4030
4031 IF c_invoice_id%ISOPEN THEN -- for bug 14681650 by anupgupt
4032 CLOSE c_invoice_id;
4033 END IF;
4034 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 begin
4035 --ROLLBACK; --Commented by Chong for ZX investigation 20120816
4036 --If unexcepted error here, we should raise error to AP workbench and display error message.
4037 --Added by Chong for ZX investigation 20120816 start
4038 fnd_message.set_name ('JA','JAI_GENERIC_MSG');
4039 fnd_message.set_token ('MSG_TEXT', 'Encountered the error in JAI_AP_STND_TAX_PROCESS.Populate_Stnd_Inv_Taxes: ' || substr(SQLERRM,1,1900));
4040 jai_cmn_utils_pkg.WRITE_FND_LOG_MSG('JAI.PLSQL.JAI_AP_STND_TAX_PROCESS.Populate_Stnd_Inv_Taxes', substr(SQLERRM,1,1900));
4041 app_exception.raise_exception;
4042 --Added by Chong for ZX investigation 20120816 end
4043 -- code ported from BL12.1.3 by zhiwei.xin on 15-MAR-2013 end.
4044 END Populate_Stnd_Inv_Taxes;
4045
4046 --==========================================================================
4047 -- PROCEDURE NAME:
4048 --
4049 -- Default_Calculate_Taxes Public
4050 --
4051 -- DESCRIPTION:
4052 --
4053 -- This procedure is to invoke standard procedure to insert item information
4054 -- into jai_cmn_document_taxes
4055 --
4056 -- PARAMETERS:
4057 -- In: pn_invoice_id IN NUMBER invoice id
4058 -- pn_line_number IN NUMBER item line number
4059 -- xn_tax_amount IN OUT NUMBER tax ou
4060 -- pv_currency_code IN VARCHAR2 currency code
4061 -- pn_tax_category_id IN NUMBER tax category
4062 -- pv_tax_modified IN VARCHAR2 tax modified flag
4063 -- pn_supplier_site_id in NUMBER supplier site id
4064 -- pn_supplier_id in NUMBER suppolier id
4065 --
4066 --
4067 -- Out:
4068 --
4069 --
4070 -- DESIGN REFERENCES:
4071 -- AP Technical Design 2.1.doc
4072 --
4073 -- CHANGE HISTORY:
4074 --
4075 -- 1 23-Aug-2007 Eric Ma Created
4076 -- 2 10-Sep-2010 Jia Li for GST Bug#10043656
4077 --===========================================================================
4078
4079 PROCEDURE Default_Calculate_Taxes
4080 ( pn_invoice_id IN NUMBER
4081 , pn_line_number IN NUMBER
4082 , xn_tax_amount IN OUT NOCOPY NUMBER
4083 , pn_vendor_id IN NUMBER
4084 , pn_vendor_site_id IN NUMBER
4085 , pv_currency_code IN VARCHAR2
4086 , pn_tax_category_id IN NUMBER
4087 , pv_tax_modified IN VARCHAR2
4088 )
4089 IS
4090 ln_invoice_id NUMBER := pn_invoice_id;
4091 ln_line_number NUMBER := pn_line_number;
4092 lv_currency_code VARCHAR2 (15) := pv_currency_code;
4093 ln_tax_category_id NUMBER := pn_tax_category_id;
4094 ln_vendor_id NUMBER := pn_vendor_id;
4098 ln_user_id NUMBER := fnd_global.user_id;
4095 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
4096 ln_proc_level NUMBER := FND_LOG.level_procedure;
4097 lv_proc_name VARCHAR2 (100) := 'Default_Calculate_Taxes';
4099 ln_login_id NUMBER := fnd_global.login_id;
4100 BEGIN
4101 --log for debug
4102 IF ( ln_proc_level >= ln_dbg_level)
4103 THEN
4104 FND_LOG.STRING ( ln_proc_level
4105 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
4106 , 'Enter procedure'
4107 );
4108 FND_LOG.STRING ( ln_proc_level
4109 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4110 , 'pn_invoice_id ' || pn_invoice_id
4111 );
4112 FND_LOG.STRING ( ln_proc_level
4113 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4114 , 'pn_line_number ' || pn_line_number
4115 );
4116 FND_LOG.STRING ( ln_proc_level
4117 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4118 , 'pv_currency_code ' || pv_currency_code
4119 );
4120 FND_LOG.STRING ( ln_proc_level
4121 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4122 , 'pn_tax_category_id ' || pn_tax_category_id
4123 );
4124 FND_LOG.STRING ( ln_proc_level
4125 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4126 , 'pv_tax_modified ' || pv_tax_modified
4127 );
4128 FND_LOG.STRING ( ln_proc_level
4129 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4130 , 'pn_vendor_id ' || pn_vendor_id
4131 );
4132 FND_LOG.STRING ( ln_proc_level
4133 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4134 , 'pn_vendor_site_id ' || pn_vendor_site_id
4135 );
4136 END IF; --( ln_proc_level >= ln_dbg_level )
4137
4138 jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes
4139 ( transaction_name => jai_constants.g_ap_standalone_invoice
4140 , p_tax_category_id => ln_tax_category_id
4141 , p_header_id => ln_invoice_id
4142 , p_line_id => ln_line_number
4143 --, p_assessable_value => 0 modified by eric on Jan 25th,2008
4144 , p_assessable_value => xn_tax_amount --modified by eric ,replace 0 with line amount
4145 , p_tax_amount => xn_tax_amount
4146 , p_inventory_item_id => NULL
4147 , p_line_quantity => 1
4148 , p_uom_code => NULL
4149 , p_vendor_id => ln_vendor_id
4150 , p_currency => lv_currency_code
4151 , p_currency_conv_factor => NULL
4152 , p_creation_date => SYSDATE
4153 , p_created_by => ln_user_id
4154 , p_last_update_date => SYSDATE
4155 , p_last_updated_by => ln_user_id
4156 , p_last_update_login => ln_login_id
4157 , p_operation_flag => NULL
4158 --, p_vat_assessable_value => 0
4159 , p_vat_assessable_value => xn_tax_amount --modified by eric ,replace 0 with line amount
4160 , p_source_trx_type => jai_constants.G_AP_STANDALONE_INVOICE
4161 , p_source_table_name => GV_JAI_AP_INVOICE_LINES --'JAI_AP_INVOICE_LINES'
4162 , p_action => jai_constants.default_taxes
4163 --, pn_gst_assessable_value => xn_tax_amount
4164 );
4165
4166 --log for debug
4167 IF ( ln_proc_level >= ln_dbg_level)
4168 THEN
4169 FND_LOG.STRING ( ln_proc_level
4170 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
4171 , 'Exit procedure'
4172 );
4173 END IF; --( ln_proc_level >= ln_dbg_level )
4174 EXCEPTION
4175 WHEN OTHERS THEN
4176 IF ( ln_proc_level >= ln_dbg_level)
4177 THEN
4178 FND_LOG.STRING ( ln_proc_level
4179 , GV_MODULE_PREFIX || '.' || lv_proc_name
4180 || '. Other_Exception '
4181 , SQLCODE || ':' || SQLERRM
4182 );
4183 END IF; --( ln_proc_level >= ln_dbg_level) ;
4184 END Default_Calculate_Taxes;
4185
4186 --==========================================================================
4187 -- FUNCTION NAME:
4188 --
4189 -- Check_Inclusive_Tax Private
4190 --
4191 -- DESCRIPTION:
4192 --
4193 -- This procedure is to check wether the input tax is inclusive tax or
4194 -- exclusive tax. If return is false,then the tax is exclusive tax.
4195 -- Otherwise ,it's a inclusive tax
4196 --
4197 -- PARAMETERS:
4198 -- In: pn_tax_id NUMBER tax id
4199 --
4200 --
4201 -- Out:
4202 -- return Boolean TURE /FALSE
4203 --
4204 -- DESIGN REFERENCES:
4205 -- AP Inclusive TD
4206 --
4207 -- CHANGE HISTORY:
4208 --
4209 -- 1 12-Dec-2007 Eric Ma Created
4210 --===========================================================================
4211 FUNCTION Check_Inclusive_Tax (pn_tax_id NUMBER)
4212 RETURN BOOLEAN
4213 IS
4214 CURSOR get_inclusive_tax_flag IS
4215 SELECT NVL(inclusive_tax_flag,'N')
4216 FROM
4217 jai_cmn_taxes_all
4218 WHERE
4219 tax_id = pn_tax_id;
4220
4221 lv_inclusive_tax_flag VARCHAR2(1);
4222 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
4223 ln_proc_level NUMBER := FND_LOG.level_procedure;
4224 lv_proc_name VARCHAR2 (100) := 'Check_Inclusive_Tax';
4225 BEGIN
4226 OPEN get_inclusive_tax_flag;
4227 FETCH get_inclusive_tax_flag
4228 INTO
4229 lv_inclusive_tax_flag;
4230 CLOSE get_inclusive_tax_flag;
4231
4232 --log for debug
4233 IF ( ln_proc_level >= ln_dbg_level)
4234 THEN
4235 FND_LOG.STRING ( ln_proc_level
4236 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
4237 , 'Enter procedure'
4238 );
4239 FND_LOG.STRING ( ln_proc_level
4240 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4241 , 'pn_tax_id ' || pn_tax_id
4242 );
4243 END IF;
4244 --log for debug
4245 IF ( ln_proc_level >= ln_dbg_level)
4246 THEN
4247 FND_LOG.STRING ( ln_proc_level
4248 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
4249 , 'Exit FUNCTION'
4250 );
4251 END IF; --( ln_proc_level >= ln_dbg_level )
4252
4253 IF (lv_inclusive_tax_flag='Y')
4254 THEN
4255 RETURN TRUE;
4256 ELSE
4257 RETURN FALSE;
4258 END IF; --(lv_inclusive_tax_flag='N')
4259
4260 EXCEPTION
4261 WHEN OTHERS THEN
4262 IF ( ln_proc_level >= ln_dbg_level)
4263 THEN
4264 FND_LOG.STRING ( ln_proc_level
4265 , GV_MODULE_PREFIX || '.' || lv_proc_name
4266 || '. Other_Exception '
4267 , SQLCODE || ':' || SQLERRM
4268 );
4269 END IF; --( ln_proc_level >= ln_dbg_level) ;
4270 END Check_Inclusive_Tax;
4271
4272 --==========================================================================
4273 -- PROCEDURE NAME:
4274 --
4275 -- Get_Pr_Processed_Flag Private
4276 --
4277 -- DESCRIPTION:
4278 --
4279 -- If we splited pr tax into two 2 portions, then the recoverable
4280 -- portion shold not be splited again. The splited PR tax has the
4281 -- following features: tax_id and tax_line_no are same.
4282 --
4283 -- PARAMETERS:
4284 -- In: pn_source_doc_id NUMBER Invoice id
4285 -- pn_source_parent_line_no NUMBER Invoice item line no
4286 --
4287 -- Out:
4288 --
4289 -- DESIGN REFERENCES:
4290 -- AP Technical Design 2.1.doc
4291 --
4292 -- CHANGE HISTORY:
4293 -- 1 29-Jan-2008 Eric Ma created for bug#6784111
4294 --===========================================================================
4295 FUNCTION Get_Pr_Processed_Flag
4296 ( pn_source_doc_id IN NUMBER
4297 , pn_source_parent_line_no IN NUMBER
4298 , pn_tax_id IN NUMBER
4299 )
4300 RETURN VARCHAR2
4301 IS
4302 ln_count NUMBER;
4303 lv_pr_processed_flag VARCHAR2 (1) DEFAULT NULL;
4304
4305 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
4306 ln_proc_level NUMBER := FND_LOG.level_procedure;
4307 lv_proc_name VARCHAR2 (100) := 'Get_Pr_Processed_Flag';
4308 BEGIN
4309 --log for debug
4310 IF ( ln_proc_level >= ln_dbg_level)
4311 THEN
4312 FND_LOG.STRING ( ln_proc_level
4313 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
4314 , 'Enter procedure'
4315 );
4316 FND_LOG.STRING ( ln_proc_level
4317 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4318 , 'pn_source_doc_id ' || pn_source_doc_id
4319 );
4320 FND_LOG.STRING ( ln_proc_level
4321 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4322 , 'pn_source_parent_line_no ' || pn_source_parent_line_no
4323 );
4324 FND_LOG.STRING ( ln_proc_level
4325 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4326 , 'pn_tax_id ' || pn_tax_id
4327 );
4328 END IF;--( ln_proc_level >= ln_dbg_level)
4329 --log for debug
4330
4331 SELECT
4332 COUNT(tax_id)
4333 INTO
4334 ln_count
4335 FROM
4336 jai_cmn_document_taxes
4337 WHERE source_doc_id = pn_source_doc_id
4338 AND source_DOC_parent_line_no = pn_source_parent_line_no
4339 AND tax_id = pn_tax_id
4340 AND source_doc_type = jai_constants.g_ap_standalone_invoice;
4341
4342 IF (ln_count >1)
4343 THEN
4344 lv_pr_processed_flag := jai_constants.yes ;
4345 ELSE
4346 lv_pr_processed_flag := jai_constants.no ;
4347 END IF;
4348
4349 IF ( ln_proc_level >= ln_dbg_level)
4350 THEN
4351 FND_LOG.STRING ( ln_proc_level
4352 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
4353 , 'lv_pr_processed_flag ' || lv_pr_processed_flag
4354 );
4355 FND_LOG.STRING ( ln_proc_level
4356 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
4357 , 'Exit FUNCTION'
4358 );
4359 END IF; --( ln_proc_level >= ln_dbg_level )
4360
4361 RETURN lv_pr_processed_flag;
4362 EXCEPTION
4363 WHEN OTHERS THEN
4364 IF ( ln_proc_level >= ln_dbg_level)
4365 THEN
4366 FND_LOG.STRING ( ln_proc_level
4367 , GV_MODULE_PREFIX || '.' || lv_proc_name
4368 || '. Other_Exception '
4369 , SQLCODE || ':' || SQLERRM
4370 );
4371 END IF; --( ln_proc_level >= ln_dbg_level) ;
4372 END Get_Pr_Processed_Flag;
4373 --==========================================================================
4374 -- PROCEDURE NAME:
4375 --
4376 -- Create_Tax_Lines Public
4377 --
4378 -- DESCRIPTION:
4379 --
4380 -- This procedure is to create tax invoice line and distribution line in
4381 -- both standard tables of ap module and jai ap modules
4382 --
4383 -- This procedures will fetch all the related information which we need to
4384 -- populate the base tables and IlL tables. Maintly to get the project
4385 -- realted information for the Project invoices, asset related info for
4386 -- theasset realted invoices and the various cahrge accounts for the
4387 -- distributions
4388 --
4389 -- PARAMETERS:
4390 -- In: pn_organization_id NUMBER organization id
4391 -- pv_currency VARCHAR2 currency
4392 -- pn_location_id NUMBER location id
4393 -- pn_invoice_id NUMBER invoice id
4394 -- pn_line_number NUMBER item line number
4395 -- p_action VARCHAR2 normally it is DEFAULT_TAXES, it can
4396 -- be jai_constants.recalculate_taxes
4397 -- pn_tax_category_id NUMBER tax category id
4398 --
4399 --
4400 -- Out:
4401 --
4402 --
4403 -- DESIGN REFERENCES:
4404 -- AP Technical Design 2.1.doc
4405 --
4406 -- CHANGE HISTORY:
4407 --
4408 -- 1 23-Aug-2007 Eric Ma Created
4409 -- 2 30-Nov-2007 Eric Ma modified for inclusive tax
4410 -- 3 18-Feb-2008 Eric Ma modified for bug#6824857
4411 -- 4 21-Nov-2008 Walton modified for bug#7202316
4412 -- 5 10-Nov-2009 Bgowrava for Bug#8975118
4413 -- Added a new variable lv_dist_class with value as 'PERMANENT' and used it for the value of distribution_class column while
4414 -- inserting into ap_invoice_distributions_all table.
4415 -- 6. 26-Nov-2009 JMEENA for bug#9098529
4416 -- Added cursor get_service_type and fetched service_type_code to
4417 -- populate in the table jai_ap_invoice_lines.
4418 -- 7. 17-Nov-2009 JMEENA for bug#9206909
4419 -- Modified cursor get_service_type and fetched organization and location id.
4420 -- Passed same values to function Get_Dist_Account_Ccid to fetch the accounts for organization.
4421 --
4422 -- 8. 22-Jun-2010 Modified by Jia for bug#9666819
4423 -- Issue: If the tax distribution line was generated before item distribution line,
4424 -- the NULL will be insert into column reversal_flag.
4425 -- Fix: Modified EXECUTE IMMEDIATE lv_insert_ap_inv_dist_ln_sql,
4426 -- USING NVL(ap_invoice_dist_rec.reversal_flag,'N')
4427 --
4428 -- 9. 08-Mar-2011 Modified by Xiao for Open Interface ER bug#11683927.
4429 -- Fix: Modified to correct accounting for PR taxes.
4430 -- Modified to correct accounting for project.
4431 -- Modified to fetch external ccid from JAI Interface tables for Open Interface ER
4432 -- for 'External' source.
4433 --
4434 -- 10. 02-Apr-2011 Modified by Xiao for bug#11936390
4435 -- Fix: Addd nvl to ln_open_source as: IF nvl(ln_open_source, 0) = 0 THEN
4436 --
4437 -- 12 20-DEC-2012 Changed by Qiong for reverse charge bug#16001407
4438 -- Update jai_doc_taxes_cur cursor to skip RECOVERABLE reverse charge service tax line.
4439 -- Call new created JAI_AP_ST_REVERSE_PROCESS.Populate_ST_RVRS_Dist to process
4440 -- RECOVERABLE reverse charge service tax line.
4441 --===========================================================================
4442
4443 PROCEDURE Create_Tax_Lines
4444 ( pn_organization_id IN NUMBER
4445 , pv_currency IN VARCHAR2
4446 , pn_location_id IN NUMBER
4447 , pn_invoice_id IN NUMBER
4448 , pn_line_number IN NUMBER DEFAULT NULL
4449 , pv_action IN VARCHAR2 DEFAULT jai_constants.default_taxes
4450 , pn_tax_category_id IN NUMBER
4451 , pv_tax_modified IN VARCHAR2
4452 )
4453 IS
4454 ln_invoice_id NUMBER := pn_invoice_id;
4455 ln_line_number NUMBER := pn_line_number;
4456 ln_tax_category_id NUMBER := pn_tax_category_id;
4457 lv_tax_modified VARCHAR2 (1) := pv_tax_modified;
4458 ln_distribution_line_number number; /* Bug 13617527 */
4459 CURSOR ap_invoice_lines_cur IS
4460 SELECT
4461 invoice_id
4462 , line_number
4463 , line_type_lookup_code
4464 , description
4465 , org_id
4466 , assets_tracking_flag
4467 , match_type
4468 , accounting_date
4469 , period_name
4470 , deferred_acctg_flag
4471 , def_acctg_start_date
4472 , def_acctg_end_date
4473 , def_acctg_number_of_periods
4474 , def_acctg_period_type
4475 , set_of_books_id
4476 , amount
4477 , wfapproval_status
4478 , creation_date
4479 , created_by
4480 , last_updated_by
4481 , last_update_date
4482 , last_update_login
4483 , project_id
4484 , task_id
4485 , expenditure_type
4486 , expenditure_item_date
4487 , expenditure_organization_id
4488 FROM
4489 ap_invoice_lines_all
4490 WHERE invoice_id = ln_invoice_id
4491 AND line_type_lookup_code = GV_CONSTANT_ITEM
4492 AND match_type = GV_NOT_MATCH_TYPE
4493 AND line_number = NVL (ln_line_number, line_number);
4494
4495 CURSOR jai_doc_taxes_cur
4496 ( pn_invoice_id NUMBER
4497 , pn_parent_line_number NUMBER
4498 )
4499 IS
4500 SELECT
4501 jcdt.doc_tax_id
4502 , jcdt.tax_line_no
4503 , jcdt.tax_id
4504 , jcdt.tax_type
4505 , jcdt.currency_code
4506 , jcdt.tax_rate
4507 , jcdt.qty_rate
4508 , jcdt.uom
4509 , jcdt.tax_amt
4510 , jcdt.func_tax_amt
4511 , jcdt.modvat_flag
4512 , jcdt.tax_category_id
4513 , jcdt.source_doc_type
4514 , jcdt.source_doc_id
4515 , jcdt.source_doc_line_id
4516 , jcdt.source_table_name
4517 , jcdt.tax_modified_by
4518 , jcdt.adhoc_flag
4519 , jcdt.precedence_1
4520 , jcdt.precedence_2
4521 , jcdt.precedence_3
4522 , jcdt.precedence_4
4523 , jcdt.precedence_5
4524 , jcdt.precedence_6
4525 , jcdt.precedence_7
4526 , jcdt.precedence_8
4527 , jcdt.precedence_9
4528 , jcdt.precedence_10
4529 , jcdt.creation_date
4530 , jcdt.created_by
4531 , jcdt.last_update_date
4532 , jcdt.last_updated_by
4533 , jcdt.last_update_login
4534 , jcdt.object_version_number
4535 , jcdt.vendor_id
4536 , jcdt.source_doc_parent_line_no
4537 , jcta.inclusive_tax_flag inc_tax_flag --Added by Eric for Inclusive Tax
4538 FROM
4539 jai_cmn_document_taxes jcdt
4540 , jai_cmn_taxes_all jcta --Added by Eric for Inclusive Tax
4541 WHERE jcdt.source_doc_id = pn_invoice_id
4542 AND jcdt.source_doc_parent_line_no = pn_parent_line_number
4543 AND jcdt.tax_id = jcta.tax_id --Added by Eric for Inclusive Tax
4544 AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
4545 --Added by Qiong for reverse charge bug#16001407 Begin
4546 ------------------------------------------------------------------
4547 AND ( NVL(jcta.reverse_charge_flag,'N') = 'N'
4548 OR ( NVL(jcta.reverse_charge_flag,'N') = 'Y'
4549
4550 AND NVL(jcdt.modvat_flag,'N') = 'N'
4551 )
4552 )
4553 ------------------------------------------------------------------
4554 --Added by Qiong for reverse charge bug#16001407 End
4555
4556 ORDER BY jcdt.doc_tax_id FOR UPDATE OF source_doc_line_id; --Add for update by Xiao for Accounting Issue, port changes for pot bug#12533434
4557
4558 CURSOR jai_default_doc_taxes_cur
4559 ( pn_invoice_id NUMBER
4560 , pn_line_number NUMBER
4561 )
4562 IS
4563 SELECT
4564 jcdt.doc_tax_id
4565 , jcdt.tax_line_no
4566 , jcdt.tax_id
4567 , jcdt.tax_type
4568 , jcdt.currency_code
4569 , jcdt.tax_rate
4570 , jcdt.qty_rate
4571 , jcdt.uom
4572 , jcdt.tax_amt
4573 , jcdt.func_tax_amt
4574 , jcdt.modvat_flag
4575 , jcdt.tax_category_id
4576 , jcdt.source_doc_type
4577 , jcdt.source_doc_id
4578 , jcdt.source_doc_line_id
4579 , jcdt.source_table_name
4580 , jcdt.tax_modified_by
4581 , jcdt.adhoc_flag
4582 , jcdt.precedence_1
4583 , jcdt.precedence_2
4584 , jcdt.precedence_3
4585 , jcdt.precedence_4
4586 , jcdt.precedence_5
4587 , jcdt.precedence_6
4588 , jcdt.precedence_7
4589 , jcdt.precedence_8
4590 , jcdt.precedence_9
4591 , jcdt.precedence_10
4592 , jcdt.creation_date
4593 , jcdt.created_by
4594 , jcdt.last_update_date
4595 , jcdt.last_updated_by
4596 , jcdt.last_update_login
4597 , jcdt.object_version_number
4598 , jcdt.vendor_id
4599 , jcdt.source_doc_parent_line_no
4600 FROM
4601 jai_cmn_document_taxes jcdt
4602 WHERE jcdt.source_doc_id = pn_invoice_id
4603 AND jcdt.source_doc_line_id = pn_line_number
4604 AND jcdt.source_doc_parent_line_no = pn_line_number
4605 AND jcdt.source_doc_type = jai_constants.g_ap_standalone_invoice
4606 ORDER BY jcdt.tax_line_no FOR UPDATE;
4607
4608 CURSOR get_tax_cur (pn_tax_id NUMBER) IS
4609 SELECT
4610 tax_name
4611 , tax_account_id
4612 , mod_cr_percentage
4613 , adhoc_flag
4614 , NVL (tax_rate, -1) tax_rate
4615 , tax_type
4616 , NVL(rounding_factor,0) rounding_factor
4617 FROM
4618 jai_cmn_taxes_all
4619 WHERE tax_id = pn_tax_id;
4620
4621 CURSOR ap_invoice_dist_cur (pn_line_number NUMBER , pn_distribution_line_number number) IS /* Bug 13617527 -added pn_distribution_line_number */
4622 SELECT
4623 accounting_date
4624 , accrual_posted_flag
4625 , assets_addition_flag
4626 , assets_tracking_flag
4627 , cash_posted_flag
4628 , distribution_line_number
4629 , dist_code_combination_id
4630 , invoice_id
4631 , last_updated_by
4632 , last_update_date
4633 , line_type_lookup_code
4634 , period_name
4635 , set_of_books_id
4636 , amount
4637 , base_amount
4638 , batch_id
4639 , created_by
4640 , creation_date
4641 , description
4642 , exchange_rate
4643 , exchange_rate_variance
4644 , last_update_login
4645 , match_status_flag
4646 , posted_flag
4647 , rate_var_code_combination_id
4648 , reversal_flag
4649 , program_application_id
4650 , program_id
4651 , program_update_date
4652 , accts_pay_code_combination_id
4653 , invoice_distribution_id
4654 , quantity_invoiced
4655 , po_distribution_id
4656 , rcv_transaction_id
4657 , price_var_code_combination_id
4658 , invoice_price_variance
4659 , base_invoice_price_variance
4660 , matched_uom_lookup_code
4661 , invoice_line_number
4662 , org_id
4663 , charge_applicable_to_dist_id
4664 , project_id
4665 , task_id
4666 , expenditure_type
4667 , expenditure_item_date
4668 , expenditure_organization_id
4669 , project_accounting_context
4670 , pa_addition_flag
4671 , distribution_class
4672 , ASSET_BOOK_TYPE_CODE
4673 , ASSET_CATEGORY_ID
4674 ,accounting_event_id --added for bug#10044104
4675 FROM
4676 ap_invoice_distributions_all
4677 WHERE invoice_id = ln_invoice_id
4678 AND invoice_line_number = pn_line_number
4679 AND distribution_line_number = pn_distribution_line_number; /*1; Bug 13617527 */
4680
4681 CURSOR new_invoice_lines_cur (pn_inovoice_line_num IN NUMBER) IS
4682 SELECT
4683 invoice_id
4684 , line_number
4685 , line_type_lookup_code
4686 , description
4687 , org_id
4688 , assets_tracking_flag
4689 , match_type
4690 , accounting_date
4691 , period_name
4692 , deferred_acctg_flag
4693 , def_acctg_start_date
4694 , def_acctg_end_date
4695 , def_acctg_number_of_periods
4696 , def_acctg_period_type
4697 , set_of_books_id
4698 , amount
4699 , wfapproval_status
4700 , creation_date
4701 , created_by
4702 , last_updated_by
4703 , last_update_date
4704 , last_update_login
4705 , project_id
4706 , task_id
4707 , expenditure_type
4708 , expenditure_item_date
4709 , expenditure_organization_id
4710 FROM
4711 ap_invoice_lines_all a
4712 WHERE invoice_id = ln_invoice_id
4713 AND line_number = pn_inovoice_line_num
4714 AND NOT EXISTS
4715 (
4716 SELECT
4717 'X'
4718 FROM
4719 jai_ap_invoice_lines b
4720 WHERE a.invoice_id = b.invoice_id
4721 AND a.line_number = b.invoice_line_number
4722 );
4723 --Added below cursor for bug#9098529 by JMEENA
4724 Cursor get_service_type(p_invoice_id NUMBER,p_invoice_line_number NUMBER) IS
4725 SELECT service_type_code,organization_id, location_id -- Added organization_id,location_id from bug#9206909
4726 FROM jai_ap_invoice_lines
4727 WHERE invoice_id = p_invoice_id
4728 AND invoice_line_number = p_invoice_line_number;
4729
4733 ap_invoice_dist_rec ap_invoice_dist_cur%ROWTYPE;
4730 lv_organization_id jai_ap_invoice_lines.ORGANIZATION_ID%type;
4731 lv_location_id jai_ap_invoice_lines.LOCATION_ID%type;
4732
4734 tax_rec get_tax_cur%ROWTYPE;
4735 lv_account_type gl_code_combinations.account_type%TYPE;
4736 ln_inv_dist_id NUMBER;
4737 ln_dist_acct_ccid NUMBER;
4738 ln_tax_amount NUMBER;
4739 ln_vendor_id NUMBER;
4740 ln_vendor_site_id NUMBER;
4741 lv_currency_code VARCHAR2 (15);
4742 ln_exchange_rate NUMBER;
4743 lv_service_type_code VARCHAR2 (30);
4744 lv_service_type_code_tmp VARCHAR2 (30); --added by walton for bug#7202316
4745 ln_batch_id NUMBER;
4746 ln_max_inv_line_num NUMBER;
4747 ln_source_doc_line_id NUMBER; --added by eric for inclusive tax
4748 ln_max_tax_line_num NUMBER;
4749 ln_max_pro_line_num NUMBER; --Added by Jia Li for inclusive tax on 2008/01/23
4750 ln_recur_tax_amt NUMBER;
4751 ln_nrecur_tax_amt NUMBER;
4752 ln_func_tax_amount NUMBER;
4753 ln_recur_func_tax_amt NUMBER;
4754 ln_nrecur_func_tax_amt NUMBER;
4755 lv_tax_type VARCHAR2(10);
4756 ln_doc_tax_id NUMBER;
4757 ln_chargeble_acct_ccid NUMBER;
4758 lv_tax_recoverable_flag VARCHAR2(1);
4759 lv_insert_jai_inv_sql VARCHAR2(32000);
4760 lv_insert_jai_tax_sql VARCHAR2(32000);
4761 lv_insert_ap_inv_ln_sql VARCHAR2(32000);
4762 lv_insert_ap_inv_dist_ln_sql VARCHAR2(32000);
4763 lv_pr_processed_flag VARCHAR2(1); --added by eric on jan 29,2008
4764 ln_max_source_line_id NUMBER; --added by eric on jan 29,2008
4765 ln_asset_track_flag ap_invoice_lines_all.assets_tracking_flag%TYPE;
4766 ln_project_id ap_invoice_lines_all.project_id%TYPE;
4767 ln_task_id ap_invoice_lines_all.task_id%TYPE;
4768 lv_expenditure_type ap_invoice_lines_all.expenditure_type%TYPE;
4769 ld_exp_item_date ap_invoice_lines_all.expenditure_item_date%TYPE;
4770 ln_exp_org_id ap_invoice_lines_all.expenditure_organization_id%TYPE;
4771 ld_sys_date DATE; --Eric added on 18-Feb-2008,for bug#6824857
4772 /* Start bug-13535569 */
4773 cursor c_exists_jai_ap_invoice_lines (p_invoice_id number, p_invoice_line_number number) is
4774 select count(*)
4775 from jai_ap_invoice_lines
4776 where invoice_id=p_invoice_id
4777 and invoice_line_number=p_invoice_line_number;
4778
4779 cursor c_exists_ap_invoice_lines (p_invoice_id number, p_invoice_line_number number) is
4780 select count(*)
4781 from ap_invoice_lines_all
4782 where invoice_id=p_invoice_id
4783 and line_number=p_invoice_line_number;
4784
4785 cursor c_exists_ap_invoice_dist_lines (p_invoice_id number, p_invoice_line_number number, p_distribution_line_number number) is
4786 select count(*)
4787 from ap_invoice_distributions_all
4788 where invoice_id = p_invoice_id
4789 and invoice_line_number = p_invoice_line_number
4790 and distribution_line_number = p_distribution_line_number;
4791
4792 --Added by Qiong for bug15875609 begin
4793 cursor c_get_min_dist is
4794 select invoice_distribution_id
4795 FROM ap_invoice_distributions_all
4796 WHERE invoice_id = pn_invoice_id
4797 AND invoice_line_number = pn_line_number
4798 AND distribution_line_number =(select min(distribution_line_number) from ap_invoice_distributions_all
4799 where invoice_id = pn_invoice_id AND invoice_line_number = pn_line_number) ;
4800 --Added by Qiong for bug15875609 end
4801
4802 ln_exists number := null;
4803 /* End bug-13535569 */
4804 ln_dist_asst_add_flag
4805 ap_invoice_distributions_all.assets_addition_flag%TYPE;
4806 ln_dist_asst_trck_flag
4807 ap_invoice_distributions_all.assets_tracking_flag%TYPE;
4808 ln_dist_project_id
4809 ap_invoice_distributions_all.project_id%TYPE;
4810 ln_dist_task_id
4811 ap_invoice_distributions_all.task_id%TYPE;
4812 ln_dist_exp_type
4813 ap_invoice_distributions_all.expenditure_type%TYPE;
4814 ld_dist_exp_item_date
4815 ap_invoice_distributions_all.expenditure_item_date%TYPE;
4816 ln_dist_exp_org_id
4817 ap_invoice_distributions_all.expenditure_organization_id%TYPE;
4818 ln_dist_pa_context
4819 ap_invoice_distributions_all.project_accounting_context%TYPE;
4820 ln_dist_pa_addition_flag
4821 ap_invoice_distributions_all.pa_addition_flag%TYPE;
4822 lv_asset_book_type_code
4823 ap_invoice_distributions_all.asset_book_type_code%TYPE;
4824 ln_asset_category_id
4825 ap_invoice_distributions_all.asset_category_id%TYPE;
4826 ln_dbg_level NUMBER := FND_LOG.g_current_runtime_level;
4827 ln_proc_level NUMBER := FND_LOG.level_procedure;
4828 lv_proc_name VARCHAR2 (100):= 'Create_Tax_Lines';
4829 ln_user_id NUMBER := fnd_global.user_id;
4830 ln_login_id NUMBER := fnd_global.login_id;
4831
4832 lb_inclusive_tax boolean; --added by eric for inclusive tax
4833 ln_jai_inv_line_id NUMBER;
4834 lv_dist_class VARCHAR2(50) := 'PERMANENT'; --Added by Bgowrava for Bug#8975118
4835 ln_accounting_event_id number;--10044104
4836 l_null_event_id number; --10044104
4837 --cursor added for bug#10044104
4838 cursor get_event_id(cp_invoice_id in number)
4839 is
4840 select accounting_event_id
4841 from
4842 ap_invoicE_distributions_all
4843 where line_type_lookup_code = 'ITEM'
4844 and invoice_id = cp_invoice_id
4845 AND NVL(posted_flag, 'N') = 'N';
4846 --start additions for bug#12946186
4847 ln_dist_taxamt ap_invoice_distributions_all.amount%type;
4848 ln_dist_lineno number;
4849 ln_invoice_line_amt ap_invoice_lines_all.amount%type;
4850
4851
4852 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
4853 ------------------------------------------------------------------------------
4854 CURSOR get_external_flag_cur(pn_invoice_id NUMBER, pn_line_number NUMBER) IS
4855 SELECT COUNT(lines.invoice_id)
4856 FROM ap_invoice_lines_all lines,
4857 jai_interface_lines_all intfs
4858 WHERE lines.line_number = intfs.internal_trx_line_id
4859 AND lines.invoice_id = intfs.internal_trx_id
4860 AND lines.line_number = pn_line_number
4861 AND lines.invoice_id = pn_invoice_id
4862 AND lines.reference_key3 = 'OFI TAX IMPORT'
4863 AND intfs.taxable_event = 'EXTERNAL';
4864
4865 CURSOR get_open_source_cur(pn_invoice_id NUMBER, pn_line_number NUMBER) IS
4866 SELECT COUNT(lines.invoice_id)
4867 FROM ap_invoice_lines_all lines,
4868 jai_interface_lines_all intfs
4869 WHERE lines.line_number = intfs.internal_trx_line_id
4870 AND lines.invoice_id = intfs.internal_trx_id
4871 AND lines.line_number = pn_line_number
4872 AND lines.invoice_id = pn_invoice_id
4873 AND lines.reference_key3 = 'OFI TAX IMPORT';
4874
4875 lv_service_tax_flag VARCHAR2(1);
4876 ln_open_source NUMBER;
4877 ln_external_flag NUMBER;
4878 ------------------------------------------------------------------------------
4879 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
4880
4881 --Added by zhiwei for Bug#12588962 on 20110610 begin
4882 ------------------------------------------------------------
4883 CURSOR chk_from_open_api(pn_invoice_id NUMBER, pn_line_number NUMBER) IS
4884 SELECT COUNT(lines.invoice_id)
4885 FROM ap_invoice_lines_all lines
4886 WHERE 1=1
4887 AND lines.line_number = pn_line_number
4888 AND lines.invoice_id = pn_invoice_id
4889 AND lines.reference_key3 = 'OFI TAX IMPORT';
4890 ------------------------------------------------------------
4891 --Added by zhiwei for Bug#12588962 on 20110610 end
4892
4893 --Added by zhiwei for POT change Bug#13023443 begin
4894 -----------------------------------------------------------------------
4895 CURSOR c_get_st_accrual_date(p_regime_id NUMBER, p_organization_id NUMBER, p_location_id NUMBER) IS
4896 select to_date(attribute_value, 'DD/MM/YYYY')
4897 from JAI_RGM_ORG_REGNS_V
4898 where regime_id = p_regime_id
4899 and organization_id = p_organization_id
4900 and location_id = p_location_id
4901 AND attribute_code = 'EFF_DATE_ST_PT'
4902 AND attribute_type_code = 'OTHERS'
4903 AND registration_type = 'OTHERS'
4904 AND (NOT EXISTS
4905 (select '1'
4906 from JAI_RGM_ORG_REGNS_V
4907 where regime_id = p_regime_id
4908 and attribute_code IN 'INV_ORG_CLASSIFICATION'
4909 and attribute_value <> 'ORGANIZATION'
4910 and organization_id = p_organization_id
4911 and location_id = p_location_id)
4912 OR
4913 NOT EXISTS
4914 (select '1'
4915 from JAI_RGM_ORG_REGNS_V
4916 where regime_id = p_regime_id
4917 and attribute_code IN 'SERVICE TYPE'
4918 and attribute_value <> 'OTHER'
4919 and organization_id = p_organization_id
4920 and location_id = p_location_id)
4921 );
4922
4923 CURSOR get_regime_id_cur IS
4924 SELECT regime_id
4925 FROM jai_rgm_definitions
4926 WHERE regime_code = jai_constants.service_regime;
4927
4928 ln_regime_id number;
4929 ld_accrual_date DATE;
4930 -----------------------------------------------------------------------
4931 --Added by zhiwei for POT change Bug#13023443 end
4932 lv_invoice_distribution_id number; /* Bug 13793724 */
4933
4934 BEGIN
4935
4936 lv_insert_jai_inv_sql:=
4937 'INSERT INTO jai_ap_invoice_lines
4938 ( jai_ap_invoice_lines_id
4939 , organization_id
4940 , location_id
4941 , invoice_id
4942 , invoice_line_number
4943 , supplier_site_id
4944 , parent_invoice_line_number
4945 , tax_category_id
4949 , line_amount
4946 , service_type_code
4947 , match_type
4948 , currency_code
4950 , line_type_lookup_code
4951 , created_by
4952 , creation_date
4953 , last_update_date
4954 , last_update_login
4955 , last_updated_by
4956 )
4957 VALUES
4958 ( :1
4959 , :2
4960 , :3
4961 , :4
4962 , :5
4963 , :6
4964 , :7
4965 , :8
4966 , :9
4967 , :10
4968 , :11
4969 , :12
4970 , :13
4971 , :14
4972 , :15
4973 , :16
4974 , :17
4975 , :18
4976 )';
4977
4978 lv_insert_jai_tax_sql :=
4979 'INSERT INTO jai_cmn_document_taxes
4980 ( doc_tax_id
4981 , tax_line_no
4982 , tax_id
4983 , tax_type
4984 , currency_code
4985 , tax_rate
4986 , qty_rate
4987 , uom
4988 , tax_amt
4989 , func_tax_amt
4990 , modvat_flag
4991 , tax_category_id
4992 , source_doc_type
4993 , source_doc_id
4994 , source_doc_line_id
4995 , source_table_name
4996 , tax_modified_by
4997 , adhoc_flag
4998 , precedence_1
4999 , precedence_2
5000 , precedence_3
5001 , precedence_4
5002 , precedence_5
5003 , precedence_6
5004 , precedence_7
5005 , precedence_8
5006 , precedence_9
5007 , precedence_10
5008 , creation_date
5009 , created_by
5010 , last_update_date
5011 , last_updated_by
5012 , last_update_login
5013 , object_version_number
5014 , vendor_id
5015 , source_doc_parent_line_no
5016 )
5017 VALUES
5018 ( :1
5019 , :2
5020 , :3
5021 , :4
5022 , :5
5023 , :6
5024 , :7
5025 , :8
5026 , :9
5027 , :10
5028 , :11
5029 , :12
5030 , :13
5031 , :14
5032 , :15
5033 , :16
5034 , :17
5035 , :18
5036 , :19
5037 , :20
5038 , :21
5039 , :22
5040 , :23
5041 , :24
5042 , :25
5043 , :26
5044 , :27
5045 , :28
5046 , :29
5047 , :30
5048 , :31
5049 , :32
5050 , :33
5051 , :34
5052 , :35
5053 , :36
5054 )';
5055
5056 lv_insert_ap_inv_ln_sql :=
5057 'INSERT INTO ap_invoice_lines_all
5058 ( invoice_id
5059 , line_number
5060 , line_type_lookup_code
5061 , description
5062 , org_id
5063 , assets_tracking_flag
5064 , match_type
5065 , accounting_date
5066 , period_name
5067 , deferred_acctg_flag
5068 , def_acctg_start_date
5069 , def_acctg_end_date
5070 , def_acctg_number_of_periods
5071 , def_acctg_period_type
5072 , set_of_books_id
5073 , amount
5074 , wfapproval_status
5075 , creation_date
5076 , created_by
5077 , last_updated_by
5078 , last_update_date
5079 , last_update_login
5080 , project_id
5081 , task_id
5082 , expenditure_type
5083 , expenditure_item_date
5084 , expenditure_organization_id
5085 )
5086 VALUES
5087 ( :1
5088 , :2
5089 , :3
5090 , :4
5091 , :5
5092 , :6
5093 , :7
5094 , :8
5095 , :9
5096 , :10
5097 , :11
5098 , :12
5099 , :13
5100 , :14
5101 , :15
5102 , :16
5103 , :17
5104 , :18
5105 , :19
5106 , :20
5107 , :21
5108 , :22
5109 , :23
5110 , :24
5111 , :25
5112 , :26
5113 , :27
5114 )';
5115
5116 lv_insert_ap_inv_dist_ln_sql :=
5117 'INSERT INTO ap_invoice_distributions_all
5118 ( accounting_date
5119 , accrual_posted_flag
5120 , assets_addition_flag
5121 , assets_tracking_flag
5122 , cash_posted_flag
5123 , distribution_line_number
5124 , dist_code_combination_id
5125 , invoice_id
5126 , last_updated_by
5127 , last_update_date
5128 , line_type_lookup_code
5129 , period_name
5130 , set_of_books_id
5131 , amount
5132 --, base_amount deleted by eric on 2008-Jan-08, as po_matched case not populate the column
5133 , batch_id
5134 , created_by
5135 , creation_date
5136 , description
5137 , exchange_rate_variance
5138 , last_update_login
5139 , match_status_flag
5140 , posted_flag
5141 , rate_var_code_combination_id
5142 , reversal_flag
5143 , program_application_id
5144 , program_id
5145 , program_update_date
5146 , accts_pay_code_combination_id
5147 , invoice_distribution_id
5148 , quantity_invoiced
5149 , po_distribution_id
5150 , rcv_transaction_id
5151 , price_var_code_combination_id
5152 , invoice_price_variance
5153 , base_invoice_price_variance
5154 , matched_uom_lookup_code
5155 , invoice_line_number
5156 , org_id
5157 , charge_applicable_to_dist_id
5158 , project_id
5159 , task_id
5160 , expenditure_type
5161 , expenditure_item_date
5162 , expenditure_organization_id
5163 , project_accounting_context
5164 , pa_addition_flag
5165 , DISTRIBUTION_CLASS
5166 , TAX_RECOVERABLE_FLAG
5167 ,accounting_event_id --added for bug#10044104
5168 )
5169 VALUES
5170 ( :1
5171 , :2
5172 , :3
5173 , :4
5174 , :5
5175 , :6
5176 , :7
5177 , :8
5178 , :9
5179 , :10
5180 , :11
5181 , :12
5182 , :13
5183 , :14
5184 , :15
5185 , :16
5186 , :17
5187 , :18
5188 , :19
5189 , :20
5190 , :21
5191 , :22
5192 , :23
5193 , :24
5194 , :25
5195 , :26
5196 , :27
5197 , :28
5198 , :29
5199 , :30
5200 , :31
5201 , :32
5202 , :33
5203 , :34
5204 , :35
5205 , :36
5206 , :37
5207 , :38
5208 , :39
5209 , :40
5210 , :41
5211 , :42
5212 , :43
5213 , :44
5214 , :45
5215 , :46
5216 , :47
5217 , :48
5218 ,:49
5219 )';
5220
5221 --log for debug
5222 IF ( ln_proc_level >= ln_dbg_level)
5223 THEN
5224 FND_LOG.STRING ( ln_proc_level
5225 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.begin'
5226 , 'Enter procedure'
5227 );
5228 FND_LOG.STRING ( ln_proc_level
5229 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
5230 , 'pn_invoice_id ' || pn_invoice_id
5231 );
5232 FND_LOG.STRING ( ln_proc_level
5233 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
5234 , 'pn_line_number ' || pn_line_number
5235 );
5236 FND_LOG.STRING ( ln_proc_level
5237 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
5238 , 'pv_tax_modified ' || pv_tax_modified
5239 );
5240 FND_LOG.STRING ( ln_proc_level
5241 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
5242 , 'pn_organization_id ' || pn_organization_id
5243 );
5244 FND_LOG.STRING ( ln_proc_level
5245 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
5246 , 'pn_location_id ' || pn_location_id
5247 );
5248 FND_LOG.STRING ( ln_proc_level
5249 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
5250 , 'pv_currency ' || pv_currency
5251 );
5252 FND_LOG.STRING ( ln_proc_level
5253 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.parameters'
5254 , 'pn_tax_category_id ' || pn_tax_category_id
5255 );
5256 END IF; --( ln_proc_level >= ln_dbg_level )
5257
5258 --insert into eric_log values (7.0,'Procedure Create_Tax_Lines',sysdate);
5259 --get invoice header information
5260 Get_Invoice_Header_Infor ( ln_invoice_id
5261 , ln_vendor_id
5262 , ln_vendor_site_id
5263 , lv_currency_code
5264 , ln_exchange_rate
5265 , ln_batch_id
5266 );
5267
5268 --When updating tax category from IL form,set the tax_category_id
5269 --as the input parementer
5270
5271 --If the program invoked from Standard AP invoice workbench,
5272 --Get the tax_category_id from configration of vendor-vndr site combination
5273 IF ( pn_tax_category_id is null
5274 AND pn_line_number is null ) --invoked form standard ap form
5275 THEN
5276 Get_Tax_Cat_Serv_Type ( pn_vendor_id => ln_vendor_id
5277 , pn_vendor_site_id => ln_vendor_site_id
5278 , xn_tax_category_id => ln_tax_category_id
5279 , xv_service_type_code => lv_service_type_code
5280 );
5281 ELSIF (pn_line_number is not null) --invoked form IL form or Case 6
5282 THEN
5283 Get_Tax_Cat_Serv_Type ( pn_vendor_id => ln_vendor_id
5284 , pn_vendor_site_id => ln_vendor_site_id
5285 , xn_tax_category_id => ln_tax_category_id
5286 , xv_service_type_code => lv_service_type_code_tmp
5287 ); --added by walton for bug#7202316
5288 ln_tax_category_id := pn_tax_category_id;
5292
5289 --added by walton for bug#7202316
5290 lv_service_type_code:=nvl(lv_service_type_code,lv_service_type_code_tmp);
5291 END IF;
5293 --Get Max Line number
5294 ln_max_inv_line_num := Get_Max_Invoice_Line_Number (ln_invoice_id);
5295
5296 --log for debug
5297 IF ( ln_proc_level >= ln_dbg_level)
5298 THEN
5299 FND_LOG.STRING ( ln_proc_level
5300 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.debug Info.'
5301 , 'Item Loop of First Time '
5302 );
5303 END IF; --( ln_proc_level >= ln_dbg_level )
5304
5305 --Loop all item lines in the ap_invoice_lines
5306 FOR ap_invoice_lines_rec IN ap_invoice_lines_cur
5307 LOOP
5308 --insert into eric_log values (7.1,'Go into Create_Tax_Lines.ap_invoice_lines_cur',sysdate);
5309 --insert into eric_log values (7.2,'ap_invoice_lines_rec.invoice_id :'|| ap_invoice_lines_rec.invoice_id,sysdate);
5310 --insert into eric_log values (7.3,'ap_invoice_lines_rec.line_number :'||ap_invoice_lines_rec.line_number,sysdate);
5311
5312 ln_tax_amount := ap_invoice_lines_rec.amount;
5313
5314 -- inserts taxes into jai_cmn_document_taxes
5315
5316 /*--Comments by zhiwei for Bug#12588962 on 20110610 begin
5317 --Add IF condition by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
5318 -------------------------------------------------------------------------------------------
5319 OPEN get_open_source_cur(ap_invoice_lines_rec.invoice_id
5320 , ap_invoice_lines_rec.line_number);
5321 FETCH get_open_source_cur INTO ln_open_source;
5322 CLOSE get_open_source_cur;
5323 -------------------------------------------------------------------------------------------
5324 --Add IF condition by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
5325 */--Comments by zhiwei for Bug#12588962 on 20110610 end
5326 --Added by zhiwei for Bug#12588962 on 20110610 begin
5327 -------------------------------------------------------------------------------------------
5328 OPEN chk_from_open_api(ap_invoice_lines_rec.invoice_id
5329 , ap_invoice_lines_rec.line_number);
5330 FETCH chk_from_open_api INTO ln_open_source;
5331 CLOSE chk_from_open_api;
5332 -------------------------------------------------------------------------------------------
5333 --Added by zhiwei for Bug#12588962 on 20110610 end
5334
5335 IF NVL (pv_tax_modified, 'N') = 'N'
5336 THEN
5337 --Add IF condition by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011
5338 --ln_open_source = 0, means the case of AP standalone.
5339 IF nvl(ln_open_source, 0) = 0 THEN --Add nvl by Xiao for bug#11936390
5340 Default_Calculate_Taxes
5341 ( pn_invoice_id => ap_invoice_lines_rec.invoice_id
5342 , pn_line_number => ap_invoice_lines_rec.line_number
5343 , xn_tax_amount => ln_tax_amount
5344 , pn_vendor_id => ln_vendor_id
5345 , pn_vendor_site_id => ln_vendor_site_id
5346 , pv_currency_code => lv_currency_code
5347 , pn_tax_category_id => ln_tax_category_id
5348 , pv_tax_modified => lv_tax_modified
5349 );
5350 END IF; --ln_open_source = 0, Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011
5351 --into jai_ap_invoice_lines if item line exists in ap_invoice_lines_all
5352 --and not in jai_ap_invoice_lines
5353
5354 FOR item_line_rec IN
5355 new_invoice_lines_cur (ap_invoice_lines_rec.line_number)
5356 LOOP
5357 SELECT
5358 jai_ap_invoice_lines_s.NEXTVAL
5359 INTO
5360 ln_jai_inv_line_id
5361 FROM DUAL;
5362 /* Bug 13535569 -added the below cursor call and the if condition*/
5363 open c_exists_jai_ap_invoice_lines(item_line_rec.invoice_id,item_line_rec.line_number);
5364 fetch c_exists_jai_ap_invoice_lines into ln_exists;
5365 close c_exists_jai_ap_invoice_lines;
5366 if (ln_exists =0)then
5367
5368 EXECUTE IMMEDIATE lv_insert_jai_inv_sql
5369 USING ln_jai_inv_line_id
5370 , pn_organization_id
5371 , pn_location_id
5372 , item_line_rec.invoice_id
5373 , item_line_rec.line_number
5374 , ln_vendor_site_id
5375 , ''
5376 , ln_tax_category_id
5377 , lv_service_type_code
5378 , item_line_rec.match_type
5379 , lv_currency_code
5380 , item_line_rec.amount
5381 , item_line_rec.line_type_lookup_code
5382 , item_line_rec.created_by
5383 , item_line_rec.creation_date
5384 , item_line_rec.last_update_date
5385 , item_line_rec.last_update_login
5386 , item_line_rec.last_updated_by ;
5387 end if; -- by nkodakan for the bug 14541702. Added the end if;
5388 END LOOP;
5389 --insert into eric_log values (7.4,'lv_insert_jai_inv_sql executed for item line :'||ap_invoice_lines_rec.line_number,sysdate);
5390
5391
5392 END IF;
5393
5394 --get max tax line numbers
5395 ln_max_tax_line_num := Get_Max_Tax_Line_Number
5396 ( ln_invoice_id
5397 , ap_invoice_lines_rec.line_number
5398 );
5399 --update the tax line number for the filed of
5400 --jai_cmn_document_taxes.source_doc_line_id
5401 --and split each PR taxes into to two lines
5402 FOR jai_default_doc_taxes_rec IN
5403 jai_default_doc_taxes_cur
5404 ( pn_invoice_id =>ln_invoice_id
5405 , pn_line_number =>ap_invoice_lines_rec.line_number
5406 )
5407 LOOP
5408 --insert into eric_log values (7.5,'Go into Create_Tax_Lines.ap_invoice_lines_cur.jai_default_doc_taxes_cur',sysdate);
5409 lb_inclusive_tax := check_inclusive_tax
5413 --commented out by eric for inclusive tax
5410 (jai_default_doc_taxes_rec.tax_id);
5411 --insert into eric_log values (7.6,'jai_default_doc_taxes_rec.tax_id: '||jai_default_doc_taxes_rec.tax_id,sysdate);
5412
5414 ----------------------------------------------------------
5415 --ln_max_inv_line_num :=ln_max_inv_line_num + 1;
5416 ----------------------------------------------------------
5417
5418 --added by eric for inclusive tax
5419 --for a inclusive tax, line source id is item LN #
5420 --for a exclusive tax, line source id is its corresponding invoice LN #
5421 ------------------------------------------------------------
5422 IF (lb_inclusive_tax) -- inclusive tax
5423 THEN
5424 --insert into eric_log values (7.7,'lb_inclusive_tax: '||'inclusive tax',sysdate);
5425 ln_source_doc_line_id := ap_invoice_lines_rec.line_number;
5426 ELSE -- exclusive tax
5427 --insert into eric_log values (7.7,'lb_inclusive_tax: '||'exclusive tax',sysdate);
5428 ln_max_inv_line_num := ln_max_inv_line_num + 1;
5429 ln_source_doc_line_id := ln_max_inv_line_num ;
5430 END IF;--(lb_inclusive_tax)
5431 ------------------------------------------------------------
5432
5433 IF pv_action = jai_constants.default_taxes
5434 THEN
5435 OPEN get_tax_cur (jai_default_doc_taxes_rec.tax_id);
5436 FETCH get_tax_cur
5437 INTO
5438 tax_rec;
5439 CLOSE get_tax_cur;
5440
5441 lv_tax_type := Get_Tax_Type
5442 ( pv_modvat_flag =>jai_default_doc_taxes_rec.modvat_flag
5443 , pn_cr_percentage =>tax_rec.mod_cr_percentage
5444 );
5445
5446 --added by eric to fix the bug bug#6784111 on Jan 29,2008 ,begin
5447 --------------------------------------------------------------------
5448 lv_pr_processed_flag := Get_Pr_Processed_Flag
5449 ( pn_source_doc_id =>jai_default_doc_taxes_rec.source_doc_id
5450 , pn_source_parent_line_no =>jai_default_doc_taxes_rec.source_doc_parent_line_no
5451 , pn_tax_id =>jai_default_doc_taxes_rec.tax_id
5452 );
5453 --------------------------------------------------------------------
5454 --added by eric to fix the bug bug#6784111 on Jan 29,2008,end
5455
5456 --common variables
5457 ln_tax_amount := NVL(jai_default_doc_taxes_rec.tax_amt,0);
5458 ln_func_tax_amount := NVL(jai_default_doc_taxes_rec.func_tax_amt,0);
5459
5460
5461 --log for debug
5462 IF ( ln_proc_level >= ln_dbg_level)
5463 THEN
5464 FND_LOG.STRING ( ln_proc_level
5465 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.ap_invoice_lines_rec.line_number'
5466 , ap_invoice_lines_rec.line_number
5467 );
5468
5469 FND_LOG.STRING ( ln_proc_level
5470 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.lv_tax_type'
5471 , lv_tax_type
5472 );
5473 END IF; --( ln_proc_level >= ln_dbg_level )
5474
5475 --Added by Zhiwei for Open Interface ER bug#12537533 on 12-May-2011, begin
5476 -----------------------------------------------------------------------------------------------
5477 OPEN get_external_flag_cur(ln_invoice_id
5478 , ap_invoice_lines_rec.line_number);
5479 FETCH get_external_flag_cur INTO ln_external_flag;
5480 CLOSE get_external_flag_cur;
5481
5482 --For taxes from Open Interface with External Event should not be splitted into two parts
5483 IF(NVL(ln_external_flag,0) > 0)THEN
5484
5485 -- update the source_doc_line_id to the real invoice line number
5486 UPDATE
5487 jai_cmn_document_taxes
5488 SET
5489 source_doc_line_id = ln_source_doc_line_id
5490 WHERE CURRENT OF jai_default_doc_taxes_cur ;
5491
5492
5493 ELSE
5494 --For other taxes not from Open Interface with External Event should be splitted into two parts
5495 -----------------------------------------------------------------------------------------------
5496 --Added by zhiwei for Open Interface ER bug#12537533 on 12-May-2011 end
5497
5498
5499
5500 --fully recoverable /non recoverable tax
5501 --only one tax line are created
5502 IF (lv_tax_type='FR' OR lv_tax_type='NR')
5503 THEN
5504 -- update the source_doc_line_id to the real invoice line number
5505 UPDATE
5506 jai_cmn_document_taxes
5507 SET
5508 --modified by eric for inclusive tax
5509 ----------------------------------------------------------------
5510 source_doc_line_id = ln_source_doc_line_id --ln_max_inv_line_num
5511 ----------------------------------------------------------------
5512 WHERE CURRENT OF jai_default_doc_taxes_cur ;
5513
5514 --deleted by eric to fix the bug bug#6784111 on Jan 29,2008 ,begin
5515 ----------------------------------------------------------------------
5516 -- partially recoverable lines
5517 -- ELSIF ( lv_tax_type='PR' )
5518 --------------------------------------------------------------------
5519 --added by eric to fix the bug bug#6784111 on Jan 29,2008 ,end
5520
5521 --To fix the bug of processing the PR tax on the splitted Recvoerable portion
5522 --added by eric to fix the bug bug#6784111 on Jan 29,2008 ,begin
5523 ----------------------------------------------------------------------
5524 ELSIF ( lv_tax_type='PR' AND lv_pr_processed_flag =JAI_CONSTANTS.no)
5525 THEN
5526 --------------------------------------------------------------------
5527 --added by eric to fix the bug bug#6784111 on Jan 29,2008 ,end
5528
5529 -- if the tax is partially recoverable tax, the tax line of table
5530 -- jai_cmn_document_taxes is required to be splited in two lines
5531 -- recoverable part and non-recoverable part
5532
5533 ln_recur_tax_amt :=
5534 NVL(ROUND( ln_tax_amount
5535 * (tax_rec.mod_cr_percentage / 100)
5536 , tax_rec.rounding_factor
5537 ),0);
5538
5539 ln_recur_func_tax_amt :=
5540 NVL(ROUND( ln_func_tax_amount
5541 * (tax_rec.mod_cr_percentage / 100)
5542 , tax_rec.rounding_factor
5543 ),0);
5544 ln_nrecur_tax_amt :=ln_tax_amount-ln_recur_tax_amt;
5545 ln_nrecur_func_tax_amt :=ln_func_tax_amount -ln_recur_func_tax_amt;
5546
5547 --log for debug
5548 IF ( ln_proc_level >= ln_dbg_level)
5549 THEN
5550 FND_LOG.STRING ( ln_proc_level
5551 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.ln_recur_tax_amt'
5552 , ln_recur_tax_amt
5553 );
5554
5555 FND_LOG.STRING ( ln_proc_level
5556 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.ln_recur_func_tax_amt'
5557 , ln_recur_func_tax_amt
5558 );
5559 FND_LOG.STRING ( ln_proc_level
5560 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.ln_nrecur_tax_amt'
5561 , ln_nrecur_tax_amt
5562 );
5563 FND_LOG.STRING ( ln_proc_level
5564 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.ln_nrecur_func_tax_amt'
5565 , ln_nrecur_func_tax_amt
5566 );
5567 END IF; --( ln_proc_level >= ln_dbg_level )
5568
5569 -- To make the 2 PR tax line with same creation date and last update
5570 ld_sys_date := sysdate; --Eric added on 18-Feb-2008,for bug#6824857
5571
5572 FOR i IN 1..2
5573 LOOP
5574
5575 --log for debug
5576 IF ( ln_proc_level >= ln_dbg_level)
5577 THEN
5578 FND_LOG.STRING ( ln_proc_level
5579 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.i'
5580 , i
5581 );
5582 END IF; --( ln_proc_level >= ln_dbg_level )
5583
5584 -- insert revocerable part
5585 IF (i = 1)
5586 THEN
5587 -- update the source_doc_line_id to the invoice line number
5588 UPDATE
5589 jai_cmn_document_taxes
5590 SET
5591 --modified by eric for inclusive tax
5592 ----------------------------------------------------------------
5593 source_doc_line_id = ln_source_doc_line_id --ln_max_inv_line_num
5594 ----------------------------------------------------------------
5595 , tax_amt = ln_recur_tax_amt
5596 , func_tax_amt = ln_recur_func_tax_amt
5597 , modvat_flag = 'Y'
5598 , creation_date = ld_sys_date --Eric added on 18-Feb-2008,for bug#6824857
5599 , last_update_date = ld_sys_date --sysdate,Eric changed on 18-Feb-2008,for bug#6824857
5600 , created_by = ln_user_id --Eric added on 18-Feb-2008,for bug#6824857
5601 , last_updated_by = ln_user_id
5602 , last_update_login = ln_login_id
5603 WHERE CURRENT OF jai_default_doc_taxes_cur ;
5604 ELSIF (i=2)
5605 THEN
5606 --commented out by eric for inclusive tax
5607 ----------------------------------------------------------
5608 --ln_max_inv_line_num :=ln_max_inv_line_num + 1;
5609 ----------------------------------------------------------
5610
5611 --added by eric for inclusive tax
5612 --for a inclusive tax, line source id is item LN #
5616 THEN
5613 --for a exclusive tax, line source id is its corresponding invoice LN #
5614 ------------------------------------------------------------
5615 IF (lb_inclusive_tax) -- inclusive tax
5617 ln_source_doc_line_id := ap_invoice_lines_rec.line_number;
5618 ELSE -- exclusive tax
5619 ln_max_inv_line_num := ln_max_inv_line_num + 1;
5620 ln_source_doc_line_id := ln_max_inv_line_num ;
5621 END IF;--(lb_inclusive_tax)
5622
5623 --ln_max_tax_line_num := ln_max_tax_line_num +1; Eric deleted for two records shown in Form
5624
5625 --Eric Ma added for two records shown in Form,begin
5626 --------------------------------------------------
5627 ln_max_tax_line_num := jai_default_doc_taxes_rec.tax_line_no;
5628 ------------------------------------------------------
5629 --Eric Ma added for two records shown in Form,end
5630
5631 SELECT
5632 jai_cmn_document_taxes_s.nextval
5633 INTO
5634 ln_doc_tax_id
5635 FROM DUAL;
5636
5637 EXECUTE IMMEDIATE lv_insert_jai_tax_sql
5638 USING ln_doc_tax_id
5639 , ln_max_tax_line_num
5640 , jai_default_doc_taxes_rec.tax_id
5641 , jai_default_doc_taxes_rec.tax_type
5642 , jai_default_doc_taxes_rec.currency_code
5643 , jai_default_doc_taxes_rec.tax_rate
5644 , jai_default_doc_taxes_rec.qty_rate
5645 , jai_default_doc_taxes_rec.uom
5646 , ln_nrecur_tax_amt --TAX_AMT
5647 , ln_nrecur_func_tax_amt --FUNC_TAX_AMT
5648 , 'N' --MODVAT_FLAG
5649 , jai_default_doc_taxes_rec.tax_category_id
5650 , jai_default_doc_taxes_rec.source_doc_type
5651 , jai_default_doc_taxes_rec.source_doc_id
5652 --modified by eric for inclusive tax
5653 -----------------------------------------------
5654 ,ln_source_doc_line_id --, ln_max_inv_line_num
5655 -----------------------------------------------
5656 , jai_default_doc_taxes_rec.source_table_name
5657 , jai_default_doc_taxes_rec.tax_modified_by
5658 , jai_default_doc_taxes_rec.adhoc_flag
5659 , jai_default_doc_taxes_rec.precedence_1
5660 , jai_default_doc_taxes_rec.precedence_2
5661 , jai_default_doc_taxes_rec.precedence_3
5662 , jai_default_doc_taxes_rec.precedence_4
5663 , jai_default_doc_taxes_rec.precedence_5
5664 , jai_default_doc_taxes_rec.precedence_6
5665 , jai_default_doc_taxes_rec.precedence_7
5666 , jai_default_doc_taxes_rec.precedence_8
5667 , jai_default_doc_taxes_rec.precedence_9
5668 , jai_default_doc_taxes_rec.precedence_10
5669 , ld_sys_date --SYSDATE,Eric changed on 18-Feb-2008,for bug#6824857 --creation_date
5670 , ln_user_id --created_by
5671 , ld_sys_date --SYSDATE,Eric changed on 18-Feb-2008,for bug#6824857 --last_update_date
5672 , ln_user_id --last_updated_by
5673 , ln_login_id --last_update_login
5674 , jai_default_doc_taxes_rec.object_version_number
5675 , jai_default_doc_taxes_rec.vendor_id
5676 , ap_invoice_lines_rec.line_number;
5677 END IF; --(i=1)
5678 END LOOP; --(i IN 1..2)
5679 END IF; --(lv_tax_type='FR' OR lv_tax_type='NR')
5680 --Added by zhiwei for Open Interface ER bug#12537533 on 12-May-2011 begin
5681 ---------------------------------------------------------------------------------------------
5682 END IF;
5683 ---------------------------------------------------------------------------------------------
5684 --Added by zhiwei for Open Interface ER bug#12537533 on 12-May-2011 end
5685 END IF; -- (default_tax)
5686 END LOOP; -- (jai_default_doc_taxes_cur)
5687 END LOOP; -- (ap_invoice_lines_rec AP INVOICE ITEM LINE LEVEL)
5688
5689 --delete taxes from ap invoice/dist lines,jai_ap_invoice_lines
5690 IF (pv_tax_modified ='Y')
5691 THEN
5692 Delete_Tax_Lines ( pn_invoice_id => ln_invoice_id
5693 , pn_line_number => ln_line_number
5694 , pv_modified_only_flag => 'Y'
5695 );
5696 END IF;--(pv_tax_modified ='Y')
5697
5698 --log for debug
5699 IF ( ln_proc_level >= ln_dbg_level)
5700 THEN
5701 FND_LOG.STRING ( ln_proc_level
5702 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.debug Info.'
5703 , 'Item Loop of Second Time '
5704 );
5705 END IF; --( ln_proc_level >= ln_dbg_level )
5706
5707 --The below 2 loops is to synchronize exclusive taxes information from
5708 --jai_cmn_document_taxes to other 3 tables
5709
5710 --insert into eric_log values (7.8,'prepare to sync data from jai_cmn_document_taxes to other 3 tables ',sysdate);
5711
5712 --Loop 1, item level: Loop all item lines in the ap_invoice_lines
5713 FOR ap_invoice_lines_rec IN ap_invoice_lines_cur
5714 LOOP
5715
5716 --Loop 2,tax level:Loop all tax lines in jai_cmn_document_taxes
5717 FOR jai_doc_taxes_rec IN
5718 jai_doc_taxes_cur
5719 ( pn_invoice_id => ln_invoice_id
5720 , pn_parent_line_number => ap_invoice_lines_rec.line_number
5721 )
5722 LOOP
5723 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
5724 --------------------------------------------------------------------
5725 OPEN get_external_flag_cur(ln_invoice_id
5726 , ap_invoice_lines_rec.line_number);
5727 FETCH get_external_flag_cur INTO ln_external_flag;
5728 CLOSE get_external_flag_cur;
5729 --------------------------------------------------------------------
5730 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
5731
5732 --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);
5733 --insert into eric_log values (7.802,'jai_doc_taxes_rec.tax_id = '|| jai_doc_taxes_rec.tax_id,sysdate);
5734
5735 /*Removed this IF from here by Jia Li for inclusive tax on 2008/01/23
5736 --added by eric for inclusive tax
5737 --insert exclusive tax into jai_ap_invoice_lines and standard ap tables
5738 ----------------------------------------------------------
5739 IF (NVL(jai_doc_taxes_rec.inc_tax_flag,'N')='N')
5740 THEN
5741 ----------------------------------------------------------
5742 */
5743 OPEN get_tax_cur (jai_doc_taxes_rec.tax_id);
5744 FETCH get_tax_cur
5745 INTO
5746 tax_rec;
5747 CLOSE get_tax_cur;
5748
5749 lv_tax_type :=
5750 Get_Tax_Type
5751 ( pv_modvat_flag =>jai_doc_taxes_rec.modvat_flag
5752 , pn_cr_percentage =>tax_rec.mod_cr_percentage
5753 );
5754 /* start- Bug 13617527 */
5755 select min(distribution_line_number) into ln_distribution_line_number
5756 from ap_invoice_distributions_all where invoice_id = pn_invoice_id
5757 and invoice_line_number = ap_invoice_lines_rec.line_number;
5758 /* end- bug 13617527 */
5759 --get mandantory parameters from item line
5760 --the acct distribution will be handled in other procedure
5761 OPEN ap_invoice_dist_cur (ap_invoice_lines_rec.line_number, ln_distribution_line_number); /* Bug 13617527 - Added ln_distribution_line_number */
5762 FETCH
5763 ap_invoice_dist_cur
5764 INTO
5765 ap_invoice_dist_rec ;
5766 CLOSE ap_invoice_dist_cur;
5767 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
5768 ---------------------------------------------------------------------------
5769 lv_service_tax_flag := Get_Service_Tax_Flag(tax_rec.tax_type);
5770 ---------------------------------------------------------------------------
5771 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
5772 IF (lv_tax_type = 'NR')
5773 THEN
5774 ln_asset_track_flag := ap_invoice_lines_rec.assets_tracking_flag;
5775 ln_project_id := ap_invoice_lines_rec.project_id;
5776 ln_task_id := ap_invoice_lines_rec.task_id;
5777 lv_expenditure_type := ap_invoice_lines_rec.expenditure_type;
5778 ld_exp_item_date := ap_invoice_lines_rec.expenditure_item_date;
5779 ln_exp_org_id := ap_invoice_lines_rec.expenditure_organization_id;
5780
5781 ln_dist_asst_add_flag :=ap_invoice_dist_rec.assets_addition_flag ;
5782 ln_dist_asst_trck_flag:=ap_invoice_dist_rec.assets_tracking_flag ;
5783 ln_dist_project_id :=ap_invoice_dist_rec.project_id;
5784 ln_dist_task_id :=ap_invoice_dist_rec.task_id;
5785 ln_dist_exp_type :=ap_invoice_dist_rec.expenditure_type;
5786 ld_dist_exp_item_date :=ap_invoice_dist_rec.expenditure_item_date;
5787 ln_dist_exp_org_id :=ap_invoice_dist_rec.expenditure_organization_id;
5788 ln_dist_pa_context :=ap_invoice_dist_rec.project_accounting_context;
5789 ln_dist_pa_addition_flag :=ap_invoice_dist_rec.pa_addition_flag;
5790 lv_asset_book_type_code :=ap_invoice_dist_rec.asset_book_type_code;
5791 ln_asset_category_id :=ap_invoice_dist_rec.asset_category_id;
5792 lv_tax_recoverable_flag :='N';
5793
5794 ELSE --(RECOVERABLE)
5795 ln_asset_track_flag := 'N';
5796 ln_project_id := NULL;
5797 ln_task_id := NULL;
5798 lv_expenditure_type := NULL;
5799 ld_exp_item_date := NULL;
5800 ln_exp_org_id := NULL;
5801
5802 ln_dist_asst_add_flag := 'U';
5803 ln_dist_asst_trck_flag := 'N';
5804 ln_dist_project_id := NULL;
5805 ln_dist_task_id := NULL;
5806 ln_dist_exp_type := NULL;
5807 ld_dist_exp_item_date := NULL;
5808 ln_dist_exp_org_id := NULL;
5809 ln_dist_pa_context := NULL;
5810 ln_dist_pa_addition_flag := 'E'; --NOT PROJECT RELATED
5811 lv_tax_recoverable_flag := 'Y';
5812 lv_asset_book_type_code := NULL;
5813 ln_asset_category_id := NULL;
5814 END IF; --(lv_tax_type = 'NR')
5815
5816 --Moved IF to here by Jia Li for inclusive tax on 2008/01/23
5817 --added by eric for inclusive tax
5818 --insert exclusive tax into jai_ap_invoice_lines and standard ap tables
5819 ----------------------------------------------------------
5820 IF (NVL(jai_doc_taxes_rec.inc_tax_flag,'N')='N')
5821 THEN
5822 ----------------------------------------------------------
5823 SELECT
5824 jai_ap_invoice_lines_s.NEXTVAL
5825 INTO
5826 ln_jai_inv_line_id
5827 FROM DUAL;
5828
5829 /*bug 9539642 - this cursor should be executed in all casses, to fetch
5830 the organization-location details. So changed the target variables and
5831 replaced the if-end if with nvl at the end*/
5832 lv_service_type_code_tmp := null;
5833 OPEN get_service_type (ln_invoice_id, ap_invoice_lines_rec.line_number);
5834 FETCH get_service_type INTO lv_service_type_code_tmp, lv_organization_id,lv_location_id ; --Added organization location for bug#9206909 by JMEENA
5835 CLOSE get_service_type;
5836 lv_service_type_code := nvl(lv_service_type_code, lv_service_type_code_tmp);
5837
5838 --insert into eric_log values (7.81,'jai_doc_taxes_rec.inc_tax_flag =''N'' Branch ',sysdate);
5839 --insert into jai_ap_invoice_lines
5840 /* Bug 13535569 -added the below cursor call and the if condition*/
5841 open c_exists_jai_ap_invoice_lines(jai_doc_taxes_rec.source_doc_id,jai_doc_taxes_rec.source_doc_line_id);
5842 fetch c_exists_jai_ap_invoice_lines into ln_exists;
5843 close c_exists_jai_ap_invoice_lines;
5844 if (ln_exists =0) then
5845 EXECUTE IMMEDIATE lv_insert_jai_inv_sql
5846 USING ln_jai_inv_line_id
5847 , lv_organization_id --Replaced pn_organization_id with lv_organization_id for bug#9206909
5848 , lv_location_id --Replaced pn_location_id with lv_location_id for bug#9206909
5849 , jai_doc_taxes_rec.source_doc_id
5850 , jai_doc_taxes_rec.source_doc_line_id
5851 , ln_vendor_site_id
5852 , jai_doc_taxes_rec.source_doc_parent_line_no
5853 , jai_doc_taxes_rec.tax_category_id
5854 , lv_service_type_code --Added for bug#9098529 by JMEENA
5855 , ap_invoice_lines_rec.match_type
5856 , lv_currency_code
5857 , jai_doc_taxes_rec.tax_amt
5858 , GV_CONSTANT_MISCELLANEOUS
5859 , ln_user_id
5860 , SYSDATE
5861 , SYSDATE
5862 , ln_login_id
5863 , ln_user_id ;
5864
5865 --insert into eric_log values (7.82,'lv_insert_jai_inv_sql executed for item line number :'|| ap_invoice_lines_rec.line_number,sysdate);
5866 --log for debug
5867 IF ( ln_proc_level >= ln_dbg_level)
5868 THEN
5869 FND_LOG.STRING ( ln_proc_level
5870 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
5871 || '.debug Info.'
5872 , 'Table jai_ap_invoice_lines inserted '
5873 );
5874 END IF; --( ln_proc_level >= ln_dbg_level )
5875 end if;
5876 --insert into ap_invoice_lines_all
5877 /* Bug 13535569 */
5878 open c_exists_ap_invoice_lines(jai_doc_taxes_rec.source_doc_id,jai_doc_taxes_rec.source_doc_line_id);
5879 fetch c_exists_ap_invoice_lines into ln_exists;
5880 close c_exists_ap_invoice_lines;
5881 if (ln_exists =0)then
5882 EXECUTE IMMEDIATE lv_insert_ap_inv_ln_sql
5883 USING jai_doc_taxes_rec.source_doc_id
5884 , jai_doc_taxes_rec.source_doc_line_id
5885 , GV_CONSTANT_MISCELLANEOUS
5886 , tax_rec.tax_name
5887 , ap_invoice_lines_rec.org_id
5888 , ln_asset_track_flag
5889 , ap_invoice_lines_rec.match_type
5890 , ap_invoice_lines_rec.accounting_date
5891 , ap_invoice_lines_rec.period_name
5892 , ap_invoice_lines_rec.deferred_acctg_flag
5893 , ap_invoice_lines_rec.def_acctg_start_date
5894 , ap_invoice_lines_rec.def_acctg_end_date
5895 , ap_invoice_lines_rec.def_acctg_number_of_periods
5896 , ap_invoice_lines_rec.def_acctg_period_type
5897 , ap_invoice_lines_rec.set_of_books_id
5898 , jai_doc_taxes_rec.tax_amt
5899 , ap_invoice_lines_rec.wfapproval_status
5900 , SYSDATE
5901 , ln_user_id
5902 , ln_user_id
5903 , SYSDATE
5904 , ln_login_id
5905 , ln_project_id
5906 , ln_task_id
5907 , lv_expenditure_type
5908 , ld_exp_item_date
5909 , ln_exp_org_id ;
5910
5911 --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);
5912
5913 --log for debug
5914 IF ( ln_proc_level >= ln_dbg_level)
5915 THEN
5916 FND_LOG.STRING ( ln_proc_level
5917 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
5918 || '.debug Info.'
5919 , 'Table ap_invoice_lines_all inserted '
5920 );
5921 END IF; --( ln_proc_level >= ln_dbg_level )
5922 end if;
5923 ln_dist_acct_ccid :=
5924 Get_Dist_Account_Ccid
5925 ( pn_invoice_id => ln_invoice_id
5926 , pn_item_line_number => ap_invoice_lines_rec.line_number
5927 , pn_organization_id => lv_organization_id --Replaced pn_organization_id with lv_organization_id for bug#9206909
5928 , pn_location_id => lv_location_id --Replaced pn_location_id with lv_location_id for bug#9206909
5929 , pn_tax_type_code => tax_rec.tax_type
5930 , pn_tax_acct_ccid => tax_rec.tax_account_id
5931 , pv_tax_type => lv_tax_type
5932 );
5933
5934 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
5935 --------------------------------------------------------------------
5936 --Only for case of non service tax for AP in external source, accounting should be fetch
5937 --directly from jai interface tax table.
5938
5939 IF ln_external_flag > 0 --AND lv_service_tax_flag = 'N' --Comments by zhiwei for bug#12537533 on 20110510
5940 THEN
5941 --Added by zhiwei for POT change Bug#13023443 begin
5942 -------------------------------------------------------------
5943 --For External from OpenAPI, if Service tax & GL>POT then get acct from API
5944 -- if not Service Tax, get acct from API
5945
5946 if nvl(lv_service_tax_flag,'N') = 'Y'then
5947
5948 open get_regime_id_cur;
5949 fetch get_regime_id_cur into ln_regime_id;
5950 close get_regime_id_cur;
5951
5952 open c_get_st_accrual_date(ln_regime_id , lv_organization_id,lv_location_id);
5953 fetch c_get_st_accrual_date into ld_accrual_date;
5954 close c_get_st_accrual_date;
5955
5956 if ap_invoice_dist_rec.accounting_date >= ld_accrual_date then
5957 -------------------------------------------------------------
5958 --Added by zhiwei for POT change Bug#13023443 end
5959 ln_dist_acct_ccid := jai_open_api_pkg.get_external_dist_account_ccid(ln_invoice_id
5960 , ap_invoice_lines_rec.line_number
5961 , jai_doc_taxes_rec.tax_line_no);
5962
5963 --Added by zhiwei for POT change Bug#13023443 begin
5964 --------------------------------------------------------------------------
5965 end if;
5966
5967 else
5968 ln_dist_acct_ccid := jai_open_api_pkg.get_external_dist_account_ccid(ln_invoice_id
5969 , ap_invoice_lines_rec.line_number
5970 , jai_doc_taxes_rec.tax_line_no);
5971
5972 end if;
5973 ---------------------------------------------------------------------------
5974 --Added by zhiwei for POT change Bug#13023443 end
5975
5976 END IF;
5977 --------------------------------------------------------------------
5978 --AAdded by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
5979
5980
5981 SELECT
5982 ap_invoice_distributions_s.NEXTVAL
5983 INTO
5984 ln_inv_dist_id
5985 FROM DUAL;
5986
5987 /* Bug 13793724 -start*/
5988 /* Commented by Qiong for bug15875609
5989 select invoice_distribution_id into lv_invoice_distribution_id
5990 FROM ap_invoice_distributions_all
5991 WHERE invoice_id = pn_invoice_id
5992 AND invoice_line_number = pn_line_number
5993 AND distribution_line_number =(select min(distribution_line_number) from ap_invoice_distributions_all
5994 where invoice_id = pn_invoice_id AND invoice_line_number = pn_line_number) ;
5995 */
5996 --Added by Qiong for bug15875609 begin
5997 Open c_get_min_dist;
5998 Fetch c_get_min_dist into lv_invoice_distribution_id;
5999 Close c_get_min_dist;
6000 --Added by Qiong for bug15875609 end
6001 /* Bug 13793724 -end */
6002 -- IF (ap_invoice_dist_rec.assets_tracking_flag = 'N')
6003 IF ( ln_dist_asst_trck_flag = 'N') /* Bug -13793724 Modified the if condition */
6004 THEN
6005 ln_chargeble_acct_ccid :=NULL;
6006 ELSE
6007 ln_chargeble_acct_ccid := lv_invoice_distribution_id; /* Commented the below part and added assignment statement for bug 13793724 */
6008 /* lv_account_type := Get_Gl_Account_Type (ln_dist_acct_ccid);
6009
6010 IF lv_account_type ='A'
6011 THEN
6012 ln_chargeble_acct_ccid := ln_dist_acct_ccid;
6013 ELSE
6014 ln_chargeble_acct_ccid := NULL;
6015 END IF; */
6016 END IF;
6017 --start additions for bug#10044104
6018
6019 SELECT count(*)
6020 INTO l_null_event_id
6021 FROM ap_invoice_distributions aid
6022 WHERE aid.invoice_id = ln_invoice_id
6023 AND aid.accounting_event_id is NULL;
6024
6025 if l_null_event_id>0
6026 then
6027
6028 AP_Accounting_Events_Pkg.Create_Events(
6029 p_event_type => 'INVOICES',
6030 p_doc_type => NULL,
6031 p_doc_id => ln_invoice_id,
6032 p_accounting_date => NULL,
6033 p_accounting_event_id => ln_accounting_event_id,
6034 p_checkrun_name => NULL,
6035 p_calling_sequence => 'Called from JAIAPPRCB');
6036
6037 /*
6038 open get_event_id(ln_invoice_id);
6039 fetch get_event_id into ln_accounting_event_id;
6040 close get_event_id; */
6041
6042 end if;
6043
6044 --end additions for bug#10044104
6045
6046 --insert into ap_distribution_lines_all
6047 /* Bug 13535569 -added the below cursor call and the if condition*/
6048 open c_exists_ap_invoice_dist_lines(ln_invoice_id,jai_doc_taxes_rec.source_doc_line_id,ln_distribution_line_number );/* Bug 13617527 -modified 1 to ln_distribution_line_number */
6049 fetch c_exists_ap_invoice_dist_lines into ln_exists;
6050 close c_exists_ap_invoice_dist_lines;
6051 if (ln_exists =0)then
6052 EXECUTE IMMEDIATE lv_insert_ap_inv_dist_ln_sql
6053 -- USING ap_invoice_lines_rec.accounting_date --Comment by Chong.Lei for POT code port
6054 -- Added by Chong.Lei for POT code port begin
6055 USING ap_invoice_dist_rec.accounting_date--Changed by Xiao for POT reg bug#12533434,
6056 --accounting in ap distribution line, should be same with item line.--ap_invoice_lines_rec.accounting_date
6057 -- Added by Chong.Lei for POT code port end
6058 , 'N'
6059 , ln_dist_asst_add_flag
6060 , ln_dist_asst_trck_flag
6061 , 'N'
6062 , ln_distribution_line_number --1 --distribution_line_number/* Bug 13617527 -modified 1 to ln_distribution_line_number */
6063 , ln_dist_acct_ccid
6064 , ln_invoice_id
6065 , ln_user_id
6066 , SYSDATE
6067 , GV_CONSTANT_MISCELLANEOUS
6068 , ap_invoice_lines_rec.period_name
6069 , ap_invoice_lines_rec.set_of_books_id
6070 , jai_doc_taxes_rec.tax_amt
6071 -- , jai_doc_taxes_rec.func_tax_amt :deleted by eric on 2008-Jan-08, as po_matched case not populate the column
6072 , ln_batch_id --invoice header level
6073 , ln_user_id
6074 , SYSDATE
6075 , tax_rec.tax_name
6076 , ''
6077 , ln_login_id
6078 , ap_invoice_dist_rec.match_status_flag
6079 , 'N' -- posted_flag
6080 , ''
6081 --, ap_invoice_dist_rec.reversal_flag -- Comments by Jia for bug#9666819
6082 , NVL(ap_invoice_dist_rec.reversal_flag,'N') -- Modified by Jia for bug#9666819
6083 , ap_invoice_dist_rec.program_application_id
6084 , ap_invoice_dist_rec.program_id
6085 , ap_invoice_dist_rec.program_update_date
6086 , ap_invoice_dist_rec.accts_pay_code_combination_id
6087 , ln_inv_dist_id
6088 , -1
6089 , ''
6090 , ''
6091 , ap_invoice_dist_rec.rcv_transaction_id
6092 , ap_invoice_dist_rec.invoice_price_variance
6093 , ap_invoice_dist_rec.base_invoice_price_variance
6094 , ap_invoice_dist_rec.matched_uom_lookup_code
6095 , jai_doc_taxes_rec.source_doc_line_id
6096 , ap_invoice_lines_rec.org_id
6097 , ln_chargeble_acct_ccid
6098 , ln_dist_project_id
6099 , ln_dist_task_id
6100 , ln_dist_exp_type
6101 , ld_dist_exp_item_date
6102 , ln_dist_exp_org_id
6103 , ln_dist_pa_context
6104 , ln_dist_pa_addition_flag
6105 , lv_dist_class --ap_invoice_dist_rec.distribution_class --Added by Bgowrava for Bug#8975118
6106 , lv_tax_recoverable_flag
6107 ,nvl(ap_invoice_dist_rec.accounting_event_id ,ln_accounting_event_id); --bug#10044104;
6108
6109 --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);
6110 --log for debug
6111 IF ( ln_proc_level >= ln_dbg_level)
6112 THEN
6113 FND_LOG.STRING ( ln_proc_level
6114 , GV_MODULE_PREFIX ||'.'|| lv_proc_name
6115 || '.debug Info.'
6116 , 'Table ap_distribution_lines_all inserted '
6117 );
6118 END IF; --( ln_proc_level >= ln_dbg_level )
6119 end if;
6120 --added by eric for inclusive tax
6121 ----------------------------------------------------------------------
6122
6123 -- Added by Jia Li for inclusive tax on 2008/01/23, Begin
6124 -- insert two lines with inclusive recoverable tax
6125 -- One line is negative with project info
6126 -- another line is positive with no project info
6127 ----------------------------------------------------------
6128 ELSIF ( NVL(jai_doc_taxes_rec.inc_tax_flag,'N') = 'Y' )
6129 AND ( lv_tax_type <> 'NR' ) --Commented by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011
6130 -- AND ( ap_invoice_lines_rec.project_id IS NOT NULL ) --Commented by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011
6131 THEN
6132
6133 --insert into eric_log values (7.91,'jai_doc_taxes_rec.inc_tax_flag =''Y'' AND recoverable with project case ',sysdate);
6134
6135 -- Insert negative line with project info
6136 -- Line number got from max jai_cmn_document_taxes.source_doc_line_id
6137 -- or max ap_invoice_lines_all.line_number
6138 ln_max_inv_line_num := Get_Max_Invoice_Line_Number(ln_invoice_id);
6139
6140 -- deleted by eric for fixing the bug of bug#6784111 on 29-JAN,2008,begin
6141 /*
6142 ln_max_tax_line_num := Get_Max_Tax_Line_Number
6143 ( ln_invoice_id
6144 , ap_invoice_lines_rec.line_number );
6145
6146 IF ln_max_inv_line_num >= ln_max_tax_line_num
6147 THEN
6148 ln_max_pro_line_num := ln_max_inv_line_num + 1;
6149 ELSE
6150 ln_max_pro_line_num := ln_max_tax_line_num + 1;
6151 END IF;
6152 */
6153 -- deleted by eric for fixing the bug of bug#6784111 on 29-JAN,2008,end
6154
6155 --added by eric for fixing the bug of bug#6784111 on 29-JAN,2008,begin
6156 ----------------------------------------------------------------------
6157 ln_max_source_line_id := Get_Max_Doc_Source_Line_Id(ln_invoice_id);
6158
6159 IF (ln_max_inv_line_num >= ln_max_source_line_id )
6160 THEN
6161 ln_max_pro_line_num := ln_max_inv_line_num + 1;
6162 ELSE
6163 ln_max_pro_line_num := ln_max_source_line_id + 1;
6164 END IF; --(ln_max_inv_line_num >= ln_max_source_line_id )
6165 ----------------------------------------------------------------------
6166 --added by eric for fixing the bug of bug#6784111 on 29-JAN,2008,end
6167
6168 SELECT
6169 jai_ap_invoice_lines_s.NEXTVAL
6170 INTO
6171 ln_jai_inv_line_id
6172 FROM DUAL;
6173
6174 /*bug 9539642 - this cursor should be executed in all casses, to fetch
6175 the organization-location details. So changed the target variables and
6176 replaced the if-end if with nvl at the end*/
6177 lv_service_type_code_tmp := null;
6178 OPEN get_service_type (ln_invoice_id, ap_invoice_lines_rec.line_number);
6179 FETCH get_service_type INTO lv_service_type_code_tmp, lv_organization_id,lv_location_id ; --Added organization location for bug#9206909 by JMEENA
6180 CLOSE get_service_type;
6181 lv_service_type_code := nvl(lv_service_type_code, lv_service_type_code_tmp);
6182 /* Bug 13535569 -added the below cursor call and the if condition*/
6183 open c_exists_jai_ap_invoice_lines(jai_doc_taxes_rec.source_doc_id,ln_max_pro_line_num);
6184 fetch c_exists_jai_ap_invoice_lines into ln_exists;
6185 close c_exists_jai_ap_invoice_lines;
6186 if (ln_exists =0) then
6187 EXECUTE IMMEDIATE lv_insert_jai_inv_sql
6188 USING ln_jai_inv_line_id
6189 , lv_organization_id --Replaced pn_organization_id with lv_organization_id for bug#9206909
6190 , lv_location_id --Replaced pn_location_id with lv_location_id for bug#9206909
6191 , jai_doc_taxes_rec.source_doc_id -- invoice_id
6192 , ln_max_pro_line_num -- invoice_line_num
6193 , ln_vendor_site_id
6194 , jai_doc_taxes_rec.source_doc_parent_line_no
6195 , jai_doc_taxes_rec.tax_category_id
6196 , lv_service_type_code --Added for bug#9098529 by JMEENA
6197 , ap_invoice_lines_rec.match_type
6198 , lv_currency_code
6199 , -jai_doc_taxes_rec.tax_amt -- negative tax amount
6200 , GV_CONSTANT_MISCELLANEOUS
6201 , ln_user_id
6202 , SYSDATE
6203 , SYSDATE
6204 , ln_login_id
6205 , ln_user_id ;
6206 end if;
6207 --insert into eric_log values (7.92,'lv_insert_jai_inv_sql executed for item line number :'|| ap_invoice_lines_rec.line_number,sysdate);
6208 /* Bug 13535569 */
6209 open c_exists_ap_invoice_lines(jai_doc_taxes_rec.source_doc_id,ln_max_pro_line_num);
6210 fetch c_exists_ap_invoice_lines into ln_exists;
6211 close c_exists_ap_invoice_lines;
6212 if (ln_exists =0)then
6213 EXECUTE IMMEDIATE lv_insert_ap_inv_ln_sql
6214 USING jai_doc_taxes_rec.source_doc_id -- invoice_id
6215 , ln_max_pro_line_num -- line_number
6216 , GV_CONSTANT_MISCELLANEOUS
6217 , tax_rec.tax_name
6218 , ap_invoice_lines_rec.org_id
6219 , ap_invoice_lines_rec.assets_tracking_flag
6220 , ap_invoice_lines_rec.match_type
6221 , ap_invoice_lines_rec.accounting_date
6222 , ap_invoice_lines_rec.period_name
6223 , ap_invoice_lines_rec.deferred_acctg_flag
6224 , ap_invoice_lines_rec.def_acctg_start_date
6225 , ap_invoice_lines_rec.def_acctg_end_date
6226 , ap_invoice_lines_rec.def_acctg_number_of_periods
6227 , ap_invoice_lines_rec.def_acctg_period_type
6228 , ap_invoice_lines_rec.set_of_books_id
6229 , -jai_doc_taxes_rec.tax_amt -- negative tax amount
6230 , ap_invoice_lines_rec.wfapproval_status
6231 , SYSDATE
6232 , ln_user_id
6233 , ln_user_id
6234 , SYSDATE
6235 , ln_login_id
6236 , ap_invoice_lines_rec.project_id
6237 , ap_invoice_lines_rec.task_id
6238 , ap_invoice_lines_rec.expenditure_type
6239 , ap_invoice_lines_rec.expenditure_item_date
6240 , ap_invoice_lines_rec.expenditure_organization_id ;
6241 end if;
6242 --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);
6243
6244 /* -- Modified by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
6245 ----------------------------------------------------------------------------------------------
6246 -- Accounting of negative tax amount distribution line, should be the same as its Item line.
6247
6248 ln_dist_acct_ccid := Get_Dist_Account_Ccid
6249 ( pn_invoice_id => ln_invoice_id
6250 , pn_item_line_number => ap_invoice_lines_rec.line_number
6251 , pn_organization_id => lv_organization_id --Replaced pn_organization_id with lv_organization_id for bug#9206909
6252 , pn_location_id => lv_location_id --Replaced pn_location_id with lv_location_id for bug#9206909
6253 , pn_tax_type_code => tax_rec.tax_type
6254 , pn_tax_acct_ccid => tax_rec.tax_account_id
6255 , pv_tax_type => lv_tax_type
6256 );
6257
6258 */
6259 --start additions for bug#10044104
6260
6261 SELECT count(*)
6262 INTO l_null_event_id
6263 FROM ap_invoice_distributions aid
6264 WHERE aid.invoice_id = ln_invoice_id
6265 AND aid.accounting_event_id is NULL;
6266
6267 if l_null_event_id>0
6268 then
6269
6270 AP_Accounting_Events_Pkg.Create_Events(
6271 p_event_type => 'INVOICES',
6272 p_doc_type => NULL,
6273 p_doc_id => ln_invoice_id,
6274 p_accounting_date => NULL,
6275 p_accounting_event_id => ln_accounting_event_id,
6276 p_checkrun_name => NULL,
6277 p_calling_sequence => 'Called from JAIAPPRCB');
6278
6279 /*
6280 open get_event_id(ln_invoice_id);
6281 fetch get_event_id into ln_accounting_event_id;
6282 close get_event_id; */
6283
6284 end if;
6285
6286 --end additions for bug#10044104
6287 /*start additions by vkaranam for bug#12946186
6288 generate the multiple distribution for the negative line*/
6289 ln_dist_lineno := null;
6290 for ap_invoice_dist_rec in (select * FROM
6291 ap_invoice_distributions_all
6292 WHERE invoice_id = ln_invoice_id
6293 AND invoice_line_number = ap_invoice_lines_rec.line_number)
6294 loop
6295 /*commented for bug#12946186
6296 -- Fetch ccid from Item line.
6297 SELECT dist_code_combination_id
6298 INTO ln_dist_acct_ccid
6299 FROM ap_invoice_distributions_all
6300 WHERE invoice_id = ln_invoice_id
6301 AND invoice_line_number = ap_invoice_lines_rec.line_number;
6302 ----------------------------------------------------------------------------------------------
6303 -- Porting the changes by Xiao POT bug#12533434, end
6304 */
6305 /*end additions for bug#12946186*/
6306
6307 ----------------------------------------------------------------------------------------------
6308 -- Modified by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
6309
6310 SELECT
6311 ap_invoice_distributions_s.NEXTVAL
6312 INTO
6313 ln_inv_dist_id
6314 FROM DUAL;
6315 --start additions for bug#12946186
6316 ln_dist_lineno :=nvl(ln_dist_lineno,0)+1;
6317 select amount
6318 into ln_invoice_line_amt
6319 from ap_invoice_lines_all
6320 where invoice_id=ln_invoice_id
6321 and line_number =ap_invoice_lines_rec.line_number;
6322
6323 ln_dist_taxamt := jai_doc_taxes_rec.tax_amt*ap_invoice_dist_rec.amount/ln_invoice_line_amt ;
6324
6325 --end additions for bug#12946186
6326
6327
6328 IF (ap_invoice_dist_rec.assets_tracking_flag = 'N')
6329 THEN
6330 ln_chargeble_acct_ccid :=NULL;
6331 ELSE
6332 ln_chargeble_acct_ccid := lv_invoice_distribution_id;/* Commented the below part and added assignment statement for bug 13793724 */
6333 /* lv_account_type := Get_Gl_Account_Type (ln_dist_acct_ccid);
6334
6335 IF lv_account_type ='A'
6336 THEN
6337 ln_chargeble_acct_ccid := ln_dist_acct_ccid;
6338 ELSE
6339 ln_chargeble_acct_ccid := NULL;
6340 END IF; */ -- by nkodakan for the bug 14541702. Removed the comment from below END IF and added to here
6341 END IF;
6342
6343 /* Bug 13535569 -added the below cursor call and the if condition*/
6344 open c_exists_ap_invoice_dist_lines(ln_invoice_id,ln_max_pro_line_num,ln_dist_lineno);
6345 fetch c_exists_ap_invoice_dist_lines into ln_exists;
6346 close c_exists_ap_invoice_dist_lines;
6347 if (ln_exists =0)then
6348
6349 EXECUTE IMMEDIATE lv_insert_ap_inv_dist_ln_sql
6350 -- USING ap_invoice_lines_rec.accounting_date --Comment by Chong.Lei for POT code port
6351 -- Added by Chong.Lei for POT code port begin
6352 USING ap_invoice_dist_rec.accounting_date--Changed by Xiao for POT reg bug#12533434,
6353 --accounting in ap distribution line, should be same with item line.--ap_invoice_lines_rec.accounting_date
6354 -- Added by Chong.Lei for POT code port end
6355 , 'N'
6356 , ap_invoice_dist_rec.assets_addition_flag
6357 , ap_invoice_dist_rec.assets_tracking_flag
6358 , 'N'
6359 -- , 1 --distribution_line_number 12946186
6360 , ln_dist_lineno --12946186
6361 -- , ln_dist_acct_ccid 12946186
6362 , ap_invoice_dist_rec. dist_code_combination_id
6363 , ln_invoice_id
6364 , ln_user_id
6365 , SYSDATE
6366 , GV_CONSTANT_MISCELLANEOUS
6367 , ap_invoice_lines_rec.period_name
6368 , ap_invoice_lines_rec.set_of_books_id
6369 -- , -jai_doc_taxes_rec.tax_amt -- negative tax amount 12946186
6370 ,-ln_dist_taxamt
6371 -- , jai_doc_taxes_rec.func_tax_amt :deleted by eric on 2008-Jan-08, as po_matched case not populate the column
6372 , ln_batch_id -- invoice header level
6373 , ln_user_id
6374 , SYSDATE
6375 , tax_rec.tax_name
6376 , ''
6377 , ln_login_id
6378 , ap_invoice_dist_rec.match_status_flag
6379 , 'N' -- posted_flag
6380 , ''
6381 , ap_invoice_dist_rec.reversal_flag
6382 , ap_invoice_dist_rec.program_application_id
6383 , ap_invoice_dist_rec.program_id
6384 , ap_invoice_dist_rec.program_update_date
6385 , ap_invoice_dist_rec.accts_pay_code_combination_id
6386 , ln_inv_dist_id
6387 , -1
6388 , ''
6389 , ''
6390 , ap_invoice_dist_rec.price_var_code_combination_id
6391 , ap_invoice_dist_rec.invoice_price_variance
6392 , ap_invoice_dist_rec.base_invoice_price_variance
6393 , ap_invoice_dist_rec.matched_uom_lookup_code
6394 , ln_max_pro_line_num -- invoice_line_number
6395 , ap_invoice_lines_rec.org_id
6396 , ln_chargeble_acct_ccid
6397 , ap_invoice_dist_rec.project_id
6398 , ap_invoice_dist_rec.task_id
6399 , ap_invoice_dist_rec.expenditure_type
6400 , ap_invoice_dist_rec.expenditure_item_date
6401 , ap_invoice_dist_rec.expenditure_organization_id
6402 , ap_invoice_dist_rec.project_accounting_context
6403 , ap_invoice_dist_rec.pa_addition_flag
6404 , lv_dist_class --ap_invoice_dist_rec.distribution_class --Added by Bgowrava for Bug#8975118
6405 , 'Y'
6406 ,nvl(ap_invoice_dist_rec.accounting_event_id ,ln_accounting_event_id); --bug#10044104;
6407 end if;
6408 --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);
6409 end loop;--12946186
6410 -- Insert positive line with no project info
6411 ln_max_pro_line_num := ln_max_pro_line_num + 1;
6412
6413 SELECT
6414 jai_ap_invoice_lines_s.NEXTVAL
6415 INTO
6416 ln_jai_inv_line_id
6417 FROM DUAL;
6418
6419 --insert into eric_log values (7.94,'prepare to insert ositive line with no project info ',sysdate);
6420 /*bug 9539642 - this cursor should be executed in all casses, to fetch
6421 the organization-location details. So changed the target variables and
6422 replaced the if-end if with nvl at the end*/
6423 lv_service_type_code_tmp := null;
6424 OPEN get_service_type (ln_invoice_id, ap_invoice_lines_rec.line_number);
6425 FETCH get_service_type INTO lv_service_type_code_tmp, lv_organization_id,lv_location_id ; --Added organization location for bug#9206909 by JMEENA
6426 CLOSE get_service_type;
6427 lv_service_type_code := nvl(lv_service_type_code, lv_service_type_code_tmp);
6428 /* Bug 13535569 -added the below cursor call and the if condition*/
6429 open c_exists_jai_ap_invoice_lines(jai_doc_taxes_rec.source_doc_id,ln_max_pro_line_num);
6430 fetch c_exists_jai_ap_invoice_lines into ln_exists;
6431 close c_exists_jai_ap_invoice_lines;
6432 if (ln_exists =0) then
6433 EXECUTE IMMEDIATE lv_insert_jai_inv_sql
6434 USING ln_jai_inv_line_id
6435 , lv_organization_id --Replaced pn_organization_id with lv_organization_id for bug#9206909
6436 , lv_location_id --Replaced pn_location_id with lv_location_id for bug#9206909
6437 , jai_doc_taxes_rec.source_doc_id -- invoice_id
6438 , ln_max_pro_line_num -- line_number
6439 , ln_vendor_site_id
6440 , jai_doc_taxes_rec.source_doc_parent_line_no
6441 , jai_doc_taxes_rec.tax_category_id
6442 , lv_service_type_code --Added for bug#9098529 by JMEENA
6443 , ap_invoice_lines_rec.match_type
6444 , lv_currency_code
6445 , jai_doc_taxes_rec.tax_amt -- positive tax amount
6446 , GV_CONSTANT_MISCELLANEOUS
6447 , ln_user_id
6448 , SYSDATE
6449 , SYSDATE
6450 , ln_login_id
6451 , ln_user_id ;
6452 end if;
6453 --insert into eric_log values (7.95,'lv_insert_jai_inv_sql executed for item line number :'|| ap_invoice_lines_rec.line_number,sysdate);
6454 /* Bug 13535569 */
6455 open c_exists_ap_invoice_lines(jai_doc_taxes_rec.source_doc_id,ln_max_pro_line_num);
6456 fetch c_exists_ap_invoice_lines into ln_exists;
6457 close c_exists_ap_invoice_lines;
6458 if (ln_exists =0)then
6459 EXECUTE IMMEDIATE lv_insert_ap_inv_ln_sql
6460 USING jai_doc_taxes_rec.source_doc_id -- invoice_id
6461 , ln_max_pro_line_num -- line_number
6462 , GV_CONSTANT_MISCELLANEOUS
6463 , tax_rec.tax_name
6464 , ap_invoice_lines_rec.org_id
6465 , ln_asset_track_flag--, 'N'--Changed by zhiwei for Bug#12598850 on 20110609
6466 , ap_invoice_lines_rec.match_type
6467 , ap_invoice_lines_rec.accounting_date
6468 , ap_invoice_lines_rec.period_name
6469 , ap_invoice_lines_rec.deferred_acctg_flag
6470 , ap_invoice_lines_rec.def_acctg_start_date
6471 , ap_invoice_lines_rec.def_acctg_end_date
6472 , ap_invoice_lines_rec.def_acctg_number_of_periods
6473 , ap_invoice_lines_rec.def_acctg_period_type
6474 , ap_invoice_lines_rec.set_of_books_id
6475 , jai_doc_taxes_rec.tax_amt -- positive tax amount
6476 , ap_invoice_lines_rec.wfapproval_status
6477 , SYSDATE
6478 , ln_user_id
6479 , ln_user_id
6480 , SYSDATE
6481 , ln_login_id
6482 --Modified by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
6483 ------------------------------------------------------
6484 , ln_project_id
6485 , ln_task_id
6486 , lv_expenditure_type
6487 , ld_exp_item_date
6488 , ln_exp_org_id ;
6489 end if;
6490 /*
6491 , ''
6492 , ''
6493 , ''
6494 , ''
6495 , '' ;
6496 */
6500 --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);
6497 ------------------------------------------------------
6498 --Modified by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
6499
6501
6502 ln_dist_acct_ccid := Get_Dist_Account_Ccid
6503 ( pn_invoice_id => ln_invoice_id
6504 , pn_item_line_number => ap_invoice_lines_rec.line_number
6505 , pn_organization_id => lv_organization_id --Replaced pn_organization_id with lv_organization_id for bug#9206909
6506 , pn_location_id => lv_location_id --Replaced pn_location_id with lv_location_id for bug#9206909
6507 , pn_tax_type_code => tax_rec.tax_type
6508 , pn_tax_acct_ccid => tax_rec.tax_account_id
6509 , pv_tax_type => lv_tax_type
6510 );
6511
6512 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
6513 --------------------------------------------------------------------
6514 --Only for case of non service tax for AP in external source, accounting should be fetch
6515 --directly from jai interface tax table.
6516 IF ln_external_flag > 0 --AND lv_service_tax_flag = 'N' --Comments by zhiwei for bug#12537533 on 20110510
6517 THEN
6518 --Added by zhiwei for POT change Bug#13023443 begin
6519 -------------------------------------------------------------
6520 --For External from OpenAPI, if Service tax & GL>POT then get acct from API
6521 -- if not Service Tax, get acct from API
6522
6523 if nvl(lv_service_tax_flag,'N') = 'Y'then
6524
6525 open get_regime_id_cur;
6526 fetch get_regime_id_cur into ln_regime_id;
6527 close get_regime_id_cur;
6528
6529 open c_get_st_accrual_date(ln_regime_id , lv_organization_id,lv_location_id);
6530 fetch c_get_st_accrual_date into ld_accrual_date;
6531 close c_get_st_accrual_date;
6532
6533 if ap_invoice_dist_rec.accounting_date >= ld_accrual_date then
6534 -------------------------------------------------------------
6535 --Added by zhiwei for POT change Bug#13023443 end
6536
6537
6538 ln_dist_acct_ccid := jai_open_api_pkg.get_external_dist_account_ccid(ln_invoice_id
6539 , ap_invoice_lines_rec.line_number
6540 , jai_doc_taxes_rec.tax_line_no);
6541
6542 --Added by zhiwei for POT change Bug#13023443 begin
6543 --------------------------------------------------------------------------
6544 end if;
6545
6546 else
6547 ln_dist_acct_ccid := jai_open_api_pkg.get_external_dist_account_ccid(ln_invoice_id
6548 , ap_invoice_lines_rec.line_number
6549 , jai_doc_taxes_rec.tax_line_no);
6550
6551 end if;
6552 ---------------------------------------------------------------------------
6553 --Added by zhiwei for POT change Bug#13023443 end
6554
6555 END IF;
6556 --------------------------------------------------------------------
6557 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
6558
6559 SELECT
6560 ap_invoice_distributions_s.NEXTVAL
6561 INTO
6562 ln_inv_dist_id
6563 FROM DUAL;
6564 ln_chargeble_acct_ccid := null; /* Bug 13793724 - assets tracking flag is set to 'N' and hence setting ln_chargeble_acct_ccid to null */
6565 /* IF (ap_invoice_dist_rec.assets_tracking_flag = 'N')
6566 THEN
6567 ln_chargeble_acct_ccid :=NULL;
6568 ELSE
6569 lv_account_type := Get_Gl_Account_Type (ln_dist_acct_ccid);
6570
6571 IF lv_account_type ='A'
6572 THEN
6573 ln_chargeble_acct_ccid := ln_dist_acct_ccid;
6574 ELSE
6575 ln_chargeble_acct_ccid := NULL;
6576 END IF;
6577 END IF; */
6578 /* Bug 13535569 -added the below cursor call and the if condition*/
6579 open c_exists_ap_invoice_dist_lines(ln_invoice_id,ln_max_pro_line_num,ln_distribution_line_number); /* Bug 13617527 -modified from 1 to ln_distribution_line_number */
6580 fetch c_exists_ap_invoice_dist_lines into ln_exists;
6581 close c_exists_ap_invoice_dist_lines;
6582 if (ln_exists =0)then
6583 EXECUTE IMMEDIATE lv_insert_ap_inv_dist_ln_sql
6584 -- USING ap_invoice_lines_rec.accounting_date --Comment by Chong.Lei for POT code port
6585 -- Added by Chong.Lei for POT code port begin
6586 USING ap_invoice_dist_rec.accounting_date--Changed by Xiao for POT reg bug#12533434,
6587 --accounting in ap distribution line, should be same with item line.--ap_invoice_lines_rec.accounting_date
6588 -- Added by Chong.Lei for POT code port end
6589 , 'N'
6590 , 'U' -- assets_addition_flag
6591 , 'N' -- assets_tracking_flag
6592 , 'N' -- cash_posted_flag
6593 , ln_distribution_line_number --1 -- distribution_line_number /* Bug 13617527 -modified from 1 to ln_distribution_line_number */
6594 , ln_dist_acct_ccid
6595 , ln_invoice_id
6596 , ln_user_id
6597 , SYSDATE
6598 , GV_CONSTANT_MISCELLANEOUS
6599 , ap_invoice_lines_rec.period_name
6600 , ap_invoice_lines_rec.set_of_books_id
6601 , jai_doc_taxes_rec.tax_amt -- positive tax amount
6602 -- , jai_doc_taxes_rec.func_tax_amt :deleted by eric on 2008-Jan-08, as po_matched case not populate the column
6603 , ln_batch_id -- invoice header level
6604 , ln_user_id
6605 , SYSDATE
6606 , tax_rec.tax_name
6607 , ''
6608 , ln_login_id
6609 , ap_invoice_dist_rec.match_status_flag
6610 , 'N' -- posted_flag
6611 , ''
6612 , ap_invoice_dist_rec.reversal_flag
6613 , ap_invoice_dist_rec.program_application_id
6614 , ap_invoice_dist_rec.program_id
6615 , ap_invoice_dist_rec.program_update_date
6616 , ap_invoice_dist_rec.accts_pay_code_combination_id
6617 , ln_inv_dist_id
6618 , -1
6619 , ''
6620 , ''
6621 , ap_invoice_dist_rec.price_var_code_combination_id
6622 , ap_invoice_dist_rec.invoice_price_variance
6623 , ap_invoice_dist_rec.base_invoice_price_variance
6624 , ap_invoice_dist_rec.matched_uom_lookup_code
6625 , ln_max_pro_line_num -- invoice_line_number
6626 , ap_invoice_lines_rec.org_id
6627 , ln_chargeble_acct_ccid
6628 --Modified by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
6629 ---------------------------------------------------------------------------------------------
6630 /* , '' -- project_id
6631 , '' -- task_id
6632 , '' -- expenditure_type
6633 , '' -- expenditure_item_date
6634 , '' -- expenditure_organization_id
6635 , '' -- project_accounting_context
6636 , 'E' -- pa_addition_flag
6637 , lv_dist_class --ap_invoice_dist_rec.distribution_class --Added by Bgowrava for Bug#8975118
6638 , 'Y';
6639 */
6640 , ln_dist_project_id
6641 , ln_dist_task_id
6642 , ln_dist_exp_type
6643 , ld_dist_exp_item_date
6644 , ln_dist_exp_org_id
6645 , ln_dist_pa_context
6646 , ln_dist_pa_addition_flag
6647 , lv_dist_class
6648 , lv_tax_recoverable_flag
6649 , nvl(ap_invoice_dist_rec.accounting_event_id ,ln_accounting_event_id); --bug#10044104;
6650 end if;
6651 ---------------------------------------------------------------------------------------------
6652 --Modified by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
6653
6654 -- Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
6655 -------------------------------------------------------------------------------------
6656 -- For Inclusive tax in jai_cmn_document_taxes, source_doc_line_id should be updated
6657 -- according to real invoice line number
6658
6659 UPDATE jai_cmn_document_taxes
6660 SET source_doc_line_id = ln_max_pro_line_num
6661 WHERE CURRENT OF jai_doc_taxes_cur ;
6662 -------------------------------------------------------------------------------------
6663 -- Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
6664
6665 --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);
6666
6667 ----------------------------------------------------------
6668 -- Added by Jia Li for inclusive tax on 2008/01/23, End
6669
6670 END IF; -- NVL(jai_doc_taxes_rec.inc_tax_flag,'N')='N'
6671 ----------------------------------------------------------------------
6672 END LOOP; -- (all taxes for a given parent line number)
6673 END LOOP; -- (ap_invoice_lines_rec IN ap_invoice_lines_cur,second time)
6674
6675 -- Added by Qiong for reverse charge bug#16001407 Start
6676 ----------------------------------------------------------------------
6677 JAI_AP_ST_REVERSE_PROCESS.Populate_ST_RVRS_Dist( pn_invoice_id => pn_invoice_id
6678 , pn_line_number => pn_line_number
6679 );
6680 ----------------------------------------------------------------------
6681 -- Added by Qiong for reverse charge bug#16001407 End
6682
6683 --log for debug
6684 IF ( ln_proc_level >= ln_dbg_level)
6685 THEN
6686 FND_LOG.STRING ( ln_proc_level
6687 , GV_MODULE_PREFIX ||'.'|| lv_proc_name || '.end'
6688 , 'Exit procedure'
6689 );
6690 END IF; --( ln_proc_level >= ln_dbg_level )
6691
6692 EXCEPTION
6693 WHEN OTHERS THEN
6694 IF ( ln_proc_level >= ln_dbg_level)
6695 THEN
6696 FND_LOG.STRING ( ln_proc_level
6697 , GV_MODULE_PREFIX|| '.'|| lv_proc_name
6698 || '. Other_Exception '
6699 , SQLCODE || ':' || SQLERRM
6700 );
6701 END IF; --( ln_proc_level >= ln_dbg_level) ;
6702 RAISE;
6703 END Create_Tax_Lines;
6704 END JAI_AP_STND_TAX_PROCESS;
6705
6706