[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_TDS_TAX_DEFAULTATION
Source
1 PACKAGE BODY jai_ap_tds_tax_defaultation AS
2 /* $Header: jai_ap_tds_dflt.plb 120.28.12020000.2 2012/07/16 11:23:08 nkodakan ship $ */
3
4 /* ----------------------------------------------------------------------------
5 FILENAME : jai_ap_tds_tax_defaultation_pkg_b.sql
6
7 Created By : Aparajita
8
9 Created Date : 24-dec-2004
10
11 Bug :
12
13 Purpose : Implementation of tax defaultation functionality on AP invoice.
14
15 Called from : Trigger ja_in_ap_aia_after_trg
16 Trigger ja_in_ap_aida_after_trg
17
18 CHANGE HISTORY:
19 -------------------------------------------------------------------------------
20 S.No Date Author and Details
21 -------------------------------------------------------------------------------
22 1. 24/12/2004 Aparajita for bug#4088186. version#115.0. TDS Clean Up.
23
24 Created this package for implementing the tax defaultation
25 functionality onto AP invoice.
26
27 2. 2/05/2005 rchandan for bug#4323338. Version 116.0
28 India Org Info DFF is eliminated as a part of JA migration. A table by name ja_in_ap_tds_org_tan is dropped
29 and a view jai_ap_tds_org_tan_v is created to capture the PAN No,TAN NO and WARD NO. The code changes are done
30 to refer to the new view instead of the dropped table.
31
32
33 3. 08-Jun-2005 Version 116.1 jai_ap_tds_dflt -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
34 as required for CASE COMPLAINCE.
35
36 3. 14-Jun-2005 rchandan for bug#4428980, Version 116.2
37 Modified the object to remove literals from DML statements and CURSORS.
38
39 4. 24-jun-2005 Aparajita for /* AP lines uptake
40
41 5. 29-Jun-2005 ssumaith - bug#4448789 - removal of hr_operating_units.legal_entity_id from this trigger.
42
43 6. 29-Jul-2005 Bug4522540. Added by Lakshmi Gopalsami Version 120.3
44 Start date and end date of a threshold type was not
45 being considered while selecting the applicable
46 threshold. This has been modified to check
47 threshold validity date range against the GL_date of
48 invoice distributions
49
50 Dependency(Functional)
51 ----------------------
52 jai_ap_tds_gen.plb Version 120.4
53
54 7. 21-Dec-2007 Sanjikum for Bug#6708042, Version 120.5
55 Obsoleted the changes done for verion 120.4 and rechecked in the version 120.3 as 120.5
56
57 8. 07/Jul/2009 Bgowrava for Bug 5911913 . File Version 120.3.12000000.9
58 Added two parameters
59 (1) p_old_input_dff_value_wct
60 (2) p_old_input_dff_value_essi
61 in procedure processs_invoice.
62 Added a check to set the value of lv_user_deleted_flag
63 for section_type in ('WCT_SECTION' and 'ESSI_SECTION')
64
65 9. 18-DEC-2009 Code modified by Eric Ma for PF bug#7340818
66
67 10. 16-Mar-2011 Bug 11830186 by amandali
68 Description:TDS INVOICE IS GENERATING WITHOUT CHECKING THRESHOLD LIMIT
69 Fix:When defaulted TDS tax id is populated after saving the distributions form, system is considering as it is a manually attached tds tax id.
70 So added if clause in process_input_dff_tds to check whether it is by default or manually attached one and proceed accordingly.
71 Also added a condition in populate_localization_inv_tax to check if it is a defaulted tax, and if yes,set user_deleted_tax_flag to Y and actual_tax_id to null.
72
73 11. 13-Jun-2011 Bug 12640899
74 Description: Incorrect TDS Invoice is generated if an Invoice(with multiple distributions) breaches SINGLE Threshold and
75 SINGLE and CUMULATIVE Slabs have different rates
76 Fix: Total Invoice amount needs to compared with SINGLE Threshold Slab From Amount
77
78 12. 23-Aug-2011 mmurtuza for bug12858951
79 Description: TDS CALCULATED FOR SERVICE TAX LINES WHEN THRESHOLD CROSSED
80 Fix: Modified the call and definition of procedure populate_localization_inv_tax by adding the parameter p_line_type_lookup_code
81 Declared two cursors c_upd_servtax_flag_n, c_upd_servtax_flag_y and two variables ln_tds_inv_tax_id_upd_n and ln_tds_inv_tax_id_upd_y
82 to update values of flag column consider_amt_for_tds in table jai_ap_tds_inv_taxes to 'N' or 'Y' based on section attached.
83
84 13. 13-Mar-2012 mmurtuza for bug12858951 Post review
85 Description: TDS CALCULATED FOR SERVICE TAX LINES WHEN THRESHOLD CROSSED
86 Fix; Modified the call and definition of procedure populate_localization_inv_tax by adding tow more parameters p_po_distribution_id and p_rcv_transaction_id
87 Commneted two cursors c_upd_servtax_flag_n, c_upd_servtax_flag_y and two variables ln_tds_inv_tax_id_upd_n and ln_tds_inv_tax_id_upd_y.
88 Added three cursors c_no_tds_service_excise_st, c_no_tds_service_excise_po, c_no_tds_service_excise_rec and varaible ln_tds_inv_tax_id_st
89 to update values of flag column consider_amt_for_tds in table jai_ap_tds_inv_taxes to 'N' or 'Y' based on section and tax type attached.
90
91 14. 20-Apr-2012 mmurtuza for bug 13983975
92 Description: SEVERE PERFORMANCE ISSUES WITH SERVICE TAX ATTACHMENT AND VALIDATION OF INVOICE
93 Fix: Removed nvl cluase from cursors c_no_tds_service_excise_st, c_no_tds_service_excise_po and c_no_tds_service_excise_rec
94
95 15. 04-May-2012 amandali for bug 14019234
96 Description:TDS not deducted on Excise tax lines
97 Fix:Commented the union all in cursors c_no_tds_service_excise_rec, c_no_tds_service_excise_po, c_no_tds_service_excise_st
98 where we have a check for excise and customs tax lines.
99 16. 05-Jun-2012 amandali for bug 14052883
100 Description:TDS not deducted on tax lines other than Service tax
101 Fix:Added AND clause to have a join for jai_cmn_taxes_all(jcta2) in cursors c_no_tds_service_excise_po and c_no_tds_service_excise_rec
102
103 ---------------------------------------------------------------------------- */
104
105 procedure process_invoice
106 (
107 p_invoice_id in number,
108 p_invoice_line_number in number default null,
109 p_invoice_distribution_id in number default null,
110 p_line_type_lookup_code in varchar2,
111 p_distribution_line_number in number,
112 p_parent_reversal_id in number,
113 p_reversal_flag in varchar2,
114 p_amount in number,
115 p_invoice_currency_code in varchar2,
116 p_exchange_rate in number,
117 p_set_of_books_id in number,
118 p_po_distribution_id in number default null,
119 p_rcv_transaction_id in number default null,
120 p_vendor_id in number,
121 p_vendor_site_id in number,
122 p_input_dff_value_tds in varchar2,
123 p_input_dff_value_wct in varchar2,
124 p_old_input_dff_value_wct in varchar2, --Added by Bgowrava for Bug#5911913
125 p_input_dff_value_essi in varchar2,
126 p_old_input_dff_value_essi in varchar2, --Added by Bgowrava for Bug#5911913
127 p_org_id in number,
128 p_accounting_date in date,
129 p_call_from in varchar2,
130 p_final_tds_tax_id out nocopy number,
131 p_process_flag out nocopy varchar2,
132 p_process_message out nocopy varchar2,
133 p_codepath in out nocopy varchar2
134 )
135 is
136
137 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
138 select currency_code
139 from gl_sets_of_books
140 where set_of_books_id = cp_set_of_books_id;
141
142 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
143
144 lv_default_tds_section_code jai_ap_tds_inv_taxes.default_section_code%type;
145 ln_default_tds_tax_id jai_ap_tds_inv_taxes.default_tax_id%type;
146 lv_default_from jai_ap_tds_inv_taxes.default_from%type;
147 lv_default_type jai_ap_tds_inv_taxes.default_type%type;
148 ln_exchange_rate ap_invoices_all.exchange_rate%type;
149
150
151 begin
152
153 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.process_invoice', 'START'); /* 1 */
154 /* Check if defaulting can happen for the invoice */
155
156 validate_status_for_default
157 (
158 p_invoice_id => p_invoice_id,
159 p_invoice_line_number => p_invoice_line_number,
160 p_invoice_distribution_id => p_invoice_distribution_id,
161 p_line_type_lookup_code => p_line_type_lookup_code,
162 p_process_flag => p_process_flag,
163 P_process_message => P_process_message,
164 p_codepath => p_codepath
165 );
166
167 if nvl(p_process_flag, 'N') <> 'Y' then
168 /* p_process_flag has the value of Y whenever TDS defaultation can take place */
169 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
170 goto exit_from_procedure;
171 end if;
172
173 open c_gl_sets_of_books(p_set_of_books_id);
174 fetch c_gl_sets_of_books into r_gl_sets_of_books;
175 close c_gl_sets_of_books;
176
177 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
178 if r_gl_sets_of_books.currency_code <> p_invoice_currency_code then
179 /* Foreign currency invoice */
180 p_codepath := jai_general_pkg.plot_codepath(3.1, p_codepath); /* 3.1 */
181 ln_exchange_rate := p_exchange_rate;
182 end if;
183
184 ln_exchange_rate := nvl(ln_exchange_rate, 1);
185
186
187 if p_input_dff_value_wct is not null or
188 p_old_input_dff_value_wct is not null then --Added by Bgowrava for Bug#5911913
189
190 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
191
192 populate_localization_inv_tax
193 (
194 p_invoice_id => p_invoice_id,
195 p_invoice_line_number => p_invoice_line_number,
196 p_invoice_distribution_id => p_invoice_distribution_id,
197 P_distribution_line_number => P_distribution_line_number,
198 p_amount => p_amount,
199 p_exchange_rate => ln_exchange_rate,
200 p_section_type => 'WCT_SECTION',
201 p_default_type => null,
202 p_default_section_code => null,
203 p_default_tax_id => null,
204 p_input_dff_value => p_input_dff_value_wct,
205 p_default_from => null,
206 p_vendor_id => p_vendor_id,
207 p_vendor_site_id => p_vendor_site_id,
208 p_org_id => p_org_id,
209 p_accounting_date => p_accounting_date,
210 p_final_tds_tax_id => p_final_tds_tax_id,
211 p_line_type_lookup_code => p_line_type_lookup_code, -- by mmurtuza for bug12858951
212 p_po_distribution_id => p_po_distribution_id, -- by mmurtuza for bug12858951 post review
213 p_rcv_transaction_id => p_rcv_transaction_id, -- by mmurtuza for bug12858951 post review
214 p_process_flag => p_process_flag,
215 P_process_message => P_process_message,
216 p_codepath => p_codepath
217 );
218
219
220
221 if nvl(p_process_flag, 'N') = 'E' then
222 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
223 goto exit_from_procedure;
224 end if;
225
226 end if; /* p_input_dff_value_wct */
227
228
229 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
230
231 if p_input_dff_value_essi is not null or
232 p_old_input_dff_value_essi is not null then --Added by Bgowrava for Bug#5911913
233
234 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
235
236 populate_localization_inv_tax
237 (
238 p_invoice_id => p_invoice_id,
239 p_invoice_line_number => p_invoice_line_number,
240 p_invoice_distribution_id => p_invoice_distribution_id,
241 P_distribution_line_number => P_distribution_line_number,
242 p_amount => p_amount,
243 p_exchange_rate => ln_exchange_rate,
244 p_section_type => 'ESSI_SECTION',
245 p_default_type => null,
246 p_default_section_code => null,
247 p_default_tax_id => null,
248 p_input_dff_value => p_input_dff_value_essi,
249 p_default_from => null,
250 p_vendor_id => p_vendor_id,
251 p_vendor_site_id => p_vendor_site_id,
252 p_org_id => p_org_id ,
253 p_accounting_date => p_accounting_date,
254 p_line_type_lookup_code => p_line_type_lookup_code, -- by mmurtuza for bug12858951
255 p_po_distribution_id => p_po_distribution_id, -- by mmurtuza for bug12858951 post review
256 p_rcv_transaction_id => p_rcv_transaction_id, -- by mmurtuza for bug12858951 post review
257 p_final_tds_tax_id => p_final_tds_tax_id,
258 p_process_flag => p_process_flag,
259 P_process_message => P_process_message,
260 p_codepath => p_codepath
261 );
262
263 if nvl(p_process_flag, 'N') = 'E' then
264 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
265 goto exit_from_procedure;
266 end if;
267
268 end if; /* p_input_dff_value_essi */
269
270
271 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
272
273 if p_rcv_transaction_id is not null then
274
275 /* If the invoice has a receipt reference get the tax from receipt */
276 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
277
278 default_tds_from_receipt
279 (
280 p_invoice_id => p_invoice_id,
281 p_invoice_line_number => p_invoice_line_number,
282 p_invoice_distribution_id => p_invoice_distribution_id,
283 p_line_type_lookup_code => p_line_type_lookup_code,
284 p_distribution_line_number => p_distribution_line_number,
285 p_rcv_transaction_id => p_rcv_transaction_id,
286 p_tds_section_code => lv_default_tds_section_code,
287 p_tds_tax_id => ln_default_tds_tax_id,
288 p_default_from => lv_default_from,
289 p_process_flag => p_process_flag,
290 P_process_message => P_process_message,
291 p_codepath => p_codepath
292 );
293
294 if nvl(p_process_flag, 'N') = 'E' then
295 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
296 goto exit_from_procedure;
297 end if;
298
299 elsif p_po_distribution_id is not null then
300 /* If the invoice has a PO reference get the tax from PO */
301
302 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
303 default_tds_from_po
304 (
305 p_invoice_id => p_invoice_id,
306 p_invoice_line_number => p_invoice_line_number,
307 p_invoice_distribution_id => p_invoice_distribution_id,
308 p_line_type_lookup_code => p_line_type_lookup_code,
309 p_distribution_line_number => p_distribution_line_number,
310 p_po_distribution_id => p_po_distribution_id,
311 p_tds_section_code => lv_default_tds_section_code,
312 p_tds_tax_id => ln_default_tds_tax_id,
313 p_default_from => lv_default_from,
314 p_process_flag => p_process_flag,
315 P_process_message => P_process_message,
316 p_codepath => p_codepath
317 );
318
319 if nvl(p_process_flag, 'N') = 'E' then
320 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
321 goto exit_from_procedure;
322 end if;
323
324
325 end if;
326
327 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
328
329 if ln_default_tds_tax_id is null then
330 /* Default from setup if not already defaulted from PO or Receipt */
331 default_tds_from_setup
332 (
333 p_vendor_id => p_vendor_id,
334 p_vendor_site_id => p_vendor_site_id,
335 p_default_type => lv_default_type,
336 p_tds_section_code => lv_default_tds_section_code,
337 p_tds_tax_id => ln_default_tds_tax_id,
338 p_default_from => lv_default_from,
339 p_process_flag => p_process_flag,
340 P_process_message => P_process_message,
341 p_codepath => p_codepath
342 );
343 end if;
344
345 if nvl(p_process_flag, 'N') = 'E' then
346 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
347 goto exit_from_procedure;
348 end if;
349
350 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath); /* 16 */
351 validate_default_tds
352 (
353 p_vendor_id => p_vendor_id,
354 p_vendor_site_id => p_vendor_site_id,
355 p_tds_section_code => lv_default_tds_section_code,
356 p_tds_tax_id => ln_default_tds_tax_id,
357 p_process_flag => p_process_flag,
358 P_process_message => P_process_message,
359 p_codepath => p_codepath
360 );
361
362 if nvl(p_process_flag, 'N') = 'E' then
363 p_codepath := jai_general_pkg.plot_codepath(16.1, p_codepath); /* 16.1 */
364 goto exit_from_procedure;
365 end if;
366
367 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath); /* 17 */
368
369 populate_localization_inv_tax
370 (
371 p_invoice_id => p_invoice_id,
372 p_invoice_line_number => p_invoice_line_number,
373 p_invoice_distribution_id => p_invoice_distribution_id,
374 P_distribution_line_number => P_distribution_line_number,
375 p_amount => p_amount,
376 p_exchange_rate => ln_exchange_rate,
377 p_section_type => 'TDS_SECTION',
378 p_default_type => lv_default_type,
379 p_default_section_code => lv_default_tds_section_code,
380 p_default_tax_id => ln_default_tds_tax_id,
381 p_input_dff_value => p_input_dff_value_tds,
382 p_default_from => lv_default_from,
383 p_vendor_id => p_vendor_id,
384 p_vendor_site_id => p_vendor_site_id,
385 p_org_id => p_org_id ,
386 p_accounting_date => p_accounting_date,
387 p_line_type_lookup_code => p_line_type_lookup_code, -- by mmurtuza for bug12858951
388 p_po_distribution_id => p_po_distribution_id, -- by mmurtuza for bug12858951 post review
389 p_rcv_transaction_id => p_rcv_transaction_id, -- by mmurtuza for bug12858951 post review
390 p_final_tds_tax_id => p_final_tds_tax_id,
391 p_process_flag => p_process_flag,
392 P_process_message => P_process_message,
393 p_codepath => p_codepath
394 );
395
396 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath); /* 18 */
397
398 << exit_from_procedure >>
399 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath, null, 'END'); /* 19 */
400 return;
401
402 exception
403 when others then
404 p_process_flag := 'E';
405 P_process_message := 'Error from process_invoice :' || sqlerrm;
406 return;
407 end process_invoice;
408
409 /* ************************************* process_invoice ************************************ */
410
411
412 /* ******************************* validate_status_for_default ****************************** */
413 procedure validate_status_for_default
414 (
415 p_invoice_id in number,
416 p_invoice_line_number in number default null,
417 p_invoice_distribution_id in number default null,
418 p_line_type_lookup_code in varchar2,
419 p_process_flag out nocopy varchar2,
420 p_process_message out nocopy varchar2,
421 p_codepath in out nocopy varchar2
422 )
423 is
424
425
426 cursor c_check_tds_already_processed(p_invoice_id number,p_process_status jai_ap_tds_inv_taxes.process_status%type) is--rchandan for bug#4428980
427 select 'P'
428 from jai_ap_tds_inv_taxes
429 where invoice_id = p_invoice_id
430 and process_status = p_process_status;
431
432 cursor c_check_old_tds_processed(p_invoice_id number) is
433 select 'Y'
434 from jai_ap_tds_invoices
435 where invoice_id = p_invoice_id;
436
437 lv_tds_process_status varchar2(1);
438
439 begin
440
441 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.validate_status_for_default', 'START'); /* 1 */
442 open c_check_tds_already_processed(p_invoice_id,'P');--rchandan for bug#4428980
443 fetch c_check_tds_already_processed into lv_tds_process_status;
444 close c_check_tds_already_processed;
445
446 if nvl(lv_tds_process_status, 'N') = 'P'then
447 /* TDS invoice has already been processed for this invoice, Cannot process again */
448 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
449 p_process_flag := 'P';
450 P_process_message := 'TDS is already processed for this invoice ';
451 goto exit_from_procedure;
452 end if;
453
454 lv_tds_process_status := null;
455 open c_check_old_tds_processed(p_invoice_id);
456 fetch c_check_old_tds_processed into lv_tds_process_status;
457 close c_check_old_tds_processed;
458
459 if nvl(lv_tds_process_status, 'N') = 'Y' then
460 p_codepath := jai_general_pkg.plot_codepath(2.1, p_codepath); /* 2.1 */
461 p_process_flag := 'P';
462 P_process_message := 'TDS is already processed for this invoice in the old system, cannot process.';
463 goto exit_from_procedure;
464 end if;
465
466
467 /* Currently defaulting happens only for ITEM lines
468 This will be extended to MISCELLANEOUS lines once the tax precedences ER is in place */
469
470 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
471 if p_line_type_lookup_code not in ('ITEM', 'MISCELLANEOUS', 'ACCRUAL', 'IPV') then /* ACCRUAL - AP lines uptake */ --Added IPV by Bgowrava for bug#9214036
472 p_process_flag := 'X';
473 P_process_message := 'TDS is not applicable as the line is not an ITEM, ACCRUAL or or MISCELLANEOUS line';
474 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
475 goto exit_from_procedure;
476 end if;
477
478
479 << exit_from_procedure >>
480 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
481 if p_process_flag is null then
482 /* All checks fine, TDS defaultation can take place */
483 p_process_flag := 'Y';
484 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
485 end if;
486
487 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath, null, 'END'); /* 7 */
488 return;
489
490 exception
491 when others then
492 p_process_flag := 'E';
493 P_process_message := 'Error from validate_status_for_default :' || sqlerrm;
494 return;
495 end validate_status_for_default;
496
497 /* ******************************* validate_status_for_default ****************************** */
498
499
500 /* *********************************** default_from_receipt ********************************** */
501
502 procedure default_tds_from_receipt
503 (
504 p_invoice_id in number,
505 p_invoice_line_number in number default null,
506 p_invoice_distribution_id in number default null,
507 p_line_type_lookup_code in varchar2,
508 p_distribution_line_number in number default null, /* AP lines uptake */
509 p_rcv_transaction_id in number,
510 p_tds_section_code out nocopy varchar2,
511 p_tds_tax_id out nocopy number,
512 p_default_from out nocopy varchar2,
513 p_process_flag out nocopy varchar2,
514 P_process_message out nocopy varchar2,
515 p_codepath in out nocopy varchar2
516 )
517 is
518
519 cursor c_rcv_transactions(p_rcv_transaction_id number) is
520 select shipment_header_id,
521 shipment_line_id
522 from rcv_transactions
523 where transaction_id = p_rcv_transaction_id;
524
525 cursor c_check_receipt_tds_tax(p_shipment_header_id number, p_shipment_line_id number,p_section_type jai_cmn_taxes_all.section_type%type) is--rchandan for bug#4428980
526 select jtc.section_code section_code,
527 jrtl.tax_id tax_id
528 from jai_rcv_line_taxes jrtl,
529 jai_cmn_taxes_all jtc
530 where jtc.tax_id = jrtl.tax_id
531 and jrtl.tax_type = jai_constants.tax_type_tds
532 and jtc.section_type = p_section_type--rchandan for bug#4428980
533 and jrtl.shipment_header_id = p_shipment_header_id
534 and jrtl.shipment_line_id = p_shipment_line_id
535 order by jrtl.tax_line_no asc;
536
537 c_rec_rcv_transactions c_rcv_transactions%rowtype;
538 c_rec_check_receipt_tds_tax c_check_receipt_tds_tax%rowtype;
539
540 begin
541
542 /* Get Receipt Details */
543 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.default_tds_from_receipt', 'START'); /* 1 */
544 open c_rcv_transactions(p_rcv_transaction_id);
545 fetch c_rcv_transactions into c_rec_rcv_transactions;
546 close c_rcv_transactions;
547
548 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
549 /* Check if TDS type of tax exists against the shipment line in Receipt taxes */
550 open c_check_receipt_tds_tax
551 (c_rec_rcv_transactions.shipment_header_id, c_rec_rcv_transactions.shipment_line_id,'TDS_SECTION');--rchandan for bug#4428980
552 fetch c_check_receipt_tds_tax into c_rec_check_receipt_tds_tax;
553 close c_check_receipt_tds_tax;
554
555 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
556 if c_rec_check_receipt_tds_tax.section_code is null then
557 /* No TDS tax exists against the receipt line */
558 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
559 goto exit_from_procedure;
560 end if;
561
562 /* Control comes here only when a TDS tax exists against the receipt */
563
564 p_tds_section_code := c_rec_check_receipt_tds_tax.section_code;
565 p_tds_tax_id := c_rec_check_receipt_tds_tax.tax_id;
566 p_default_from := 'Receipt';
567
568 << exit_from_procedure >>
569 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath, null, 'END'); /* 5 */
570 return;
571
572 exception
573 when others then
574 p_process_flag := 'E';
575 P_process_message := 'Error from default_from_receipt :' || sqlerrm;
576 return;
577
578 end default_tds_from_receipt;
579
580 /* *********************************** default_from_receipt ********************************** */
581
582 /* ************************************** default_from_po ************************************ */
583 procedure default_tds_from_po
584 (
585 p_invoice_id in number,
586 p_invoice_line_number in number default null,
587 p_invoice_distribution_id in number default null,
588 p_line_type_lookup_code in varchar2,
589 p_distribution_line_number in number default null, /* AP lines uptake */
590 p_po_distribution_id in number,
591 p_tds_section_code out nocopy varchar2,
592 p_tds_tax_id out nocopy number,
593 p_default_from out nocopy varchar2,
594 p_process_flag out nocopy varchar2,
595 P_process_message out nocopy varchar2,
596 p_codepath in out nocopy varchar2
597 )
598 is
599
600 cursor c_po_distributions_all(p_po_distribution_id number) is
601 select po_header_id,
602 po_line_id,
603 line_location_id
604 from po_distributions_all
605 where po_distribution_id = p_po_distribution_id;
606
607
608 cursor c_po_taxes(p_po_header_id number, p_po_line_id number, p_line_location_id number,p_section_type jai_cmn_taxes_all.section_type%type)--rchandan for bug#4428980
609 is
610 select jtc.section_code section_code,
611 jpllt.tax_id tax_id
612 from jai_po_taxes jpllt,
613 jai_cmn_taxes_all jtc
614 where jpllt.tax_id = jtc.tax_id
615 and jpllt.po_header_id = p_po_header_id
616 and jpllt.po_line_id = p_po_line_id
617 and jpllt.line_location_id = p_line_location_id
618 and jtc.tax_type = jai_constants.tax_type_tds
619 and jtc.section_type = p_section_type--rchandan for bug#4428980
620 order by jpllt.tax_line_no asc;
621
622
623 c_rec_po_distributions_all c_po_distributions_all%rowtype;
624 lv_last_section_type JAI_CMN_TAXES_ALL.section_type%type;
625 c_rec_po_taxes c_po_taxes%rowtype;
626
627 begin
628
629 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.default_tds_from_po', 'START'); /* 1 */
630 open c_po_distributions_all(p_po_distribution_id);
631 fetch c_po_distributions_all into c_rec_po_distributions_all;
632 close c_po_distributions_all;
633
634 /* Check if TDS type of tax exists against if PO taxes */
635 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
636 open c_po_taxes
637 (
638 c_rec_po_distributions_all.po_header_id,
639 c_rec_po_distributions_all.po_line_id,
640 c_rec_po_distributions_all.line_location_id,
641 'TDS_SECTION' --rchandan for bug#4428980
642 );
643 fetch c_po_taxes into c_rec_po_taxes;
644 close c_po_taxes;
645
646 if c_rec_po_taxes.section_code is null then
647 /* No TDS tax exists against the receipt line */
648 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
649 goto exit_from_procedure;
650 end if;
651
652 /* Control comes here only when a TDS tax exists against the receipt */
653 p_tds_section_code := c_rec_po_taxes.section_code;
654 p_tds_tax_id := c_rec_po_taxes.tax_id;
655 p_default_from := 'PO';
656
657 << exit_from_procedure >>
658 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath, null, 'END'); /* 4 */
659 return;
660
661 exception
662 when others then
663 p_process_flag := 'E';
664 P_process_message := 'Error from default_from_po :' || sqlerrm;
665 return;
666
667 end default_tds_from_po;
668 /* ************************************** default_from_po ************************************ */
669
670
671 /* ************************************* default_from_setup *********************************** */
672
673 procedure default_tds_from_setup
674 (
675 p_vendor_id in number,
676 p_vendor_site_id in number,
677 p_default_type out nocopy varchar2,
678 p_tds_section_code out nocopy varchar2,
679 p_tds_tax_id out nocopy number,
680 p_default_from out nocopy varchar2,
681 p_process_flag out nocopy varchar2,
682 P_process_message out nocopy varchar2,
683 p_codepath in out nocopy varchar2
684 )
685 is
686
687 cursor c_ja_in_vendor_tds_info_hdr (p_vendor_id number, p_vendor_site_id number)
688 is
689 select section_code,
690 tax_id
691 from JAI_AP_TDS_VENDOR_HDRS
692 where vendor_id = p_vendor_id
693 and vendor_site_id = p_vendor_site_id;
694
695 crec_ja_in_vendor_tds_info_hdr c_ja_in_vendor_tds_info_hdr%rowtype;
696
697 begin
698
699 /* Check from setup for vendor and site */
700 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.default_tds_from_setup', 'START'); /* 1 */
701 p_default_from := 'Vendor Site Setup';
702 open c_ja_in_vendor_tds_info_hdr(p_vendor_id, p_vendor_site_id);
703 fetch c_ja_in_vendor_tds_info_hdr into crec_ja_in_vendor_tds_info_hdr;
704 close c_ja_in_vendor_tds_info_hdr;
705
706 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
707 if crec_ja_in_vendor_tds_info_hdr.tax_id is null and
708 crec_ja_in_vendor_tds_info_hdr.section_code is null
709 then
710 /* No setup exists for site, check for null site */
711 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
712 p_default_from := 'Vendor Null Site Setup';
713
714 crec_ja_in_vendor_tds_info_hdr := null;
715 open c_ja_in_vendor_tds_info_hdr(p_vendor_id, 0);
716 fetch c_ja_in_vendor_tds_info_hdr into crec_ja_in_vendor_tds_info_hdr;
717 close c_ja_in_vendor_tds_info_hdr;
718 end if;
719
720 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
721 if crec_ja_in_vendor_tds_info_hdr.tax_id is not null and
722 crec_ja_in_vendor_tds_info_hdr.section_code is not null
723 then
724
725 /* Tax has been define as the default */
726 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
727 p_tds_section_code := crec_ja_in_vendor_tds_info_hdr.section_code;
728 p_tds_tax_id := crec_ja_in_vendor_tds_info_hdr.tax_id;
729 p_default_type := 'TAX';
730
731 elsif crec_ja_in_vendor_tds_info_hdr.tax_id is null and
732 crec_ja_in_vendor_tds_info_hdr.section_code is not null
733 then
734
735 /* Section has been define as the default */
736 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
737 p_tds_section_code := crec_ja_in_vendor_tds_info_hdr.section_code;
738 p_default_type := 'SECTION';
739
740 else
741
742 /* No Default has been setup for the vendor */
743 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
744 goto exit_from_procedure;
745
746 end if;
747
748
749 << exit_from_procedure >>
750 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath, null, 'END'); /* 9 */
751 return;
752
753 exception
754 when others then
755 p_process_flag := 'E';
756 P_process_message := 'Error from default_from_setup :' || sqlerrm;
757 return;
758 end default_tds_from_setup;
759
760 /* ************************************* default_from_setup *********************************** */
761
762 /* ************************************* validate_default_value *********************************** */
763 procedure validate_default_tds
764 (
765 p_vendor_id in number,
766 p_vendor_site_id in number,
767 p_tds_section_code in varchar2,
768 p_tds_tax_id in number,
769 p_process_flag out nocopy varchar2,
770 P_process_message out nocopy varchar2,
771 p_codepath in out nocopy varchar2
772 )
773 is
774
775 cursor c_ja_in_vendor_tds_info_hdr(p_vendor_id number, p_vendor_site_id number) is
776 select nvl(confirm_pan_flag, 'N') confirm_pan_flag
777 from JAI_AP_TDS_VENDOR_HDRS
778 where vendor_id = p_vendor_id
779 and vendor_site_id = p_vendor_site_id;
780
781 cursor c_check_section_applicable(p_vendor_id number, p_vendor_site_id number, p_tds_section_code varchar2,p_section_type JAI_CMN_TAXES_ALL.section_type%type) is--rchandan for bug#4428980
782 select 'Y'
783 from JAI_AP_TDS_TH_VSITE_V
784 where vendor_id = p_vendor_id
785 and vendor_site_id = p_vendor_site_id
786 and section_type = p_section_type--rchandan for bug#4428980
787 and section_code = p_tds_section_code;
788
789 lv_confirm_pan_flag JAI_AP_TDS_VENDOR_HDRS.confirm_pan_flag%type;
790 lv_check_section_applicable varchar2(1);
791
792 begin
793
794 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.validate_default_tds', 'START'); /* 1 */
795 open c_ja_in_vendor_tds_info_hdr(p_vendor_id, p_vendor_site_id);
796 fetch c_ja_in_vendor_tds_info_hdr into lv_confirm_pan_flag;
797 close c_ja_in_vendor_tds_info_hdr;
798
799 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
800 if lv_confirm_pan_flag = 'N' then
801 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
802 p_process_flag := 'V';
803 P_process_message := 'PAN of the vendor site not confirmed';
804 /*goto exit_from_procedure; Bug#11896260*/
805 end if;
806
807 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
808
809
810 /* Check if section is applicable because of regular setup */
811 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
812 open c_check_section_applicable(p_vendor_id, p_vendor_site_id, p_tds_section_code,'TDS_SECTION' );
813 fetch c_check_section_applicable into lv_check_section_applicable;
814 close c_check_section_applicable;
815
816 if nvl(lv_check_section_applicable, 'N') <> 'Y' then
817 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
818 p_process_flag := 'V';
819 P_process_message := 'Section is not applicable to the vendor and / or site';
820 end if;
821
822
823 << exit_from_procedure >>
824 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath, null, 'END'); /* 8 */
825 return;
826
827 exception
828 when others then
829 p_process_flag := 'E';
830 P_process_message := 'Error from validate_default_value :' || sqlerrm;
831 return;
832
833 end validate_default_tds;
834
835 /* ************************************* validate_default_value *********************************** */
836
837 /* *********************************** populate_localization_inv_tax ****************************** */
838 procedure populate_localization_inv_tax
839 (
840 p_invoice_id in number,
841 p_invoice_line_number in number default null, /* AP lines uptake */
842 p_invoice_distribution_id in number default null, /* AP lines uptake */
843 P_distribution_line_number in number default null, /* AP lines uptake */
844 p_amount in number,
845 p_exchange_rate in number,
846 p_section_type in varchar2,
847 p_default_type in varchar2,
848 p_default_section_code in varchar2,
849 p_default_tax_id in number,
850 p_input_dff_value in varchar2,
851 p_default_from in varchar2,
852 p_vendor_id in number,
853 p_vendor_site_id in number,
854 p_org_id in number,
855 p_accounting_date in date,
856 p_line_type_lookup_code in varchar2 default null, -- by mmurtuza for bug12858951
857 p_po_distribution_id in number default null, -- mmurtuza for bug12858951 Post review
858 p_rcv_transaction_id in number default null, -- mmurtuza for bug12858951 Post review
859 p_final_tds_tax_id out nocopy number,
860 p_process_flag out nocopy varchar2,
861 P_process_message out nocopy varchar2,
862 p_codepath in out nocopy varchar2
863 )
864 is
865
866 cursor c_check_if_record_exists
867 (p_invoice_id number, p_invoice_line_number number, p_invoice_distribution_id number) is
868 select tds_inv_tax_id, actual_tax_id --Added by Bgowrava for Bug#5911913
869 from jai_ap_tds_inv_taxes
870 where invoice_id = p_invoice_id
871 and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, -9999)
872 and nvl(invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
873 and section_type = p_section_type;
874
875 /*Start Additions for bug 12858951 by mmurtuza*/
876
877 /*Start Commenting by mmurtuza for bug 12858951 Post review*/
878
879 /*Cursor c_upd_servtax_flag_n(cp_invoice_id number, cp_invoice_distribution_id number) is
880 select tds_inv_tax_id from
881 jai_ap_tds_inv_taxes jadit, jai_cmn_document_taxes jcdt, jai_cmn_taxes_all jcta,
882 jai_rgm_registrations jrr, jai_rgm_definitions jrd
883 where jadit.invoice_id=jcdt.source_doc_id
884 and jadit.actual_tax_id=jcta.tax_id
885 and (NVL(upper(nvl(actual_section_code, default_section_code)), '-XX') NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE')
886 AND upper(jcta.section_code) NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE'))
887 AND jcdt.tax_type = jrr.attribute_code
888 AND jrr.regime_id = jrd.regime_id
889 AND jrr.registration_type = jai_constants.regn_type_tax_types
890 AND jrd.regime_code = jai_constants.service_regime
891 and nvl(jadit.invoice_distribution_id, -9999) = nvl(cp_invoice_distribution_id, -9999)
892 and nvl(jadit.invoice_id, -9999) = nvl(cp_invoice_id, -9999);
893
894 Cursor c_upd_servtax_flag_y(cp_invoice_id number, cp_invoice_distribution_id number) is
895 select tds_inv_tax_id from
896 jai_ap_tds_inv_taxes jadit, jai_cmn_document_taxes jcdt, jai_cmn_taxes_all jcta,
897 jai_rgm_registrations jrr, jai_rgm_definitions jrd
898 where jadit.invoice_id=jcdt.source_doc_id
899 and jadit.actual_tax_id=jcta.tax_id
900 and NOT (NVL(upper(nvl(actual_section_code, default_section_code)), '-XX') NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE')
901 AND upper(jcta.section_code) NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE'))
902 AND jcdt.tax_type = jrr.attribute_code
903 AND jrr.regime_id = jrd.regime_id
904 AND jrr.registration_type = jai_constants.regn_type_tax_types
905 AND jrd.regime_code = jai_constants.service_regime
906 and nvl(jadit.invoice_distribution_id, -9999) = nvl(cp_invoice_distribution_id, -9999)
907 and nvl(jadit.invoice_id, -9999) = nvl(cp_invoice_id, -9999);
908
909 ln_tds_inv_tax_id_upd_n jai_ap_tds_inv_taxes.tds_inv_tax_id%type;
910 ln_tds_inv_tax_id_upd_y jai_ap_tds_inv_taxes.tds_inv_tax_id%type;*/
911
912 /*End Commenting by mmurtuza for bug 12858951 Post review*/
913
914 /*End Additions for bug 12858951 by mmurtuza*/
915
916 /*Start additions by mmurtuza for bug 12858951 Post review*/
917
918 cursor c_no_tds_service_excise_st is
919 select jadit.tds_inv_tax_id from
920 jai_ap_tds_inv_taxes jadit, jai_cmn_document_taxes jcdt, jai_cmn_taxes_all jcta,
921 jai_rgm_registrations jrr, jai_rgm_definitions jrd
922 where jadit.invoice_id=jcdt.source_doc_id
923 and nvl(jadit.actual_tax_id, jadit.default_tax_id)=jcta.tax_id
924 AND upper(jcta.section_code) NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE')
925 AND jcdt.tax_type = jrr.attribute_code
926 AND jrr.regime_id = jrd.regime_id
927 AND jrr.registration_type = jai_constants.regn_type_tax_types
928 AND jrd.regime_code = jai_constants.service_regime
929 /*and nvl(jadit.invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
930 and nvl(jadit.invoice_id, -9999) = nvl(p_invoice_id, -9999)*/ /*Commented nvl clause and added below by mmurtuza for bug 13983975*/
931 and jadit.invoice_distribution_id = p_invoice_distribution_id
932 and jadit.invoice_id = p_invoice_id
933 /* Commented below union all for bug 14019234 */
934 /* union all
935
936 SELECT tds_inv_tax_id
937 FROM jai_ap_tds_inv_taxes jadit,
938 jai_cmn_document_taxes jcdt,
939 jai_cmn_taxes_all jcta
940 WHERE jcdt.tax_id = jcta.tax_id
941 --AND (UPPER(jcta.tax_type) LIKE '%EXCISE%' OR UPPER(jcta.tax_type) LIKE '%CUSTOMS%')
942 AND jcta.tax_type in (JAI_CONSTANTS.TAX_TYPE_EXCISE, JAI_CONSTANTS.TAX_TYPE_EXC_ADDITIONAL, JAI_CONSTANTS.TAX_TYPE_EXC_OTHER,
943 JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_CUSTOMS,
944 JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS)
945 AND jadit.invoice_id=jcdt.source_doc_id
946 --AND nvl(jadit.invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
947 --and nvl(jadit.invoice_id, -9999) = nvl(p_invoice_id, -9999); --Commented nvl clause and added below by mmurtuza for bug 13983975
948 AND jadit.invoice_distribution_id = p_invoice_distribution_id
949 and jadit.invoice_id = p_invoice_id */;
950
951
952 cursor c_no_tds_service_excise_po is
953 select jadit.tds_inv_tax_id from
954 jai_ap_tds_inv_taxes jadit, JAI_AP_MATCH_INV_TAXES jamit , jai_cmn_taxes_all jcta1, jai_cmn_taxes_all jcta2,
955 jai_rgm_registrations jrr, jai_rgm_definitions jrd
956 where jadit.invoice_id = jamit.invoice_id
957 and nvl(jadit.actual_tax_id, default_tax_id)=jcta1.tax_id
958 and nvl(jamit.po_distribution_id, -9999) = p_po_distribution_id
959 AND upper(jcta1.section_code) NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE')
960 and jamit.tax_id=jcta2.tax_id /* Added AND condition for bug 14052883 */
961 and jadit.invoice_line_number=jamit.invoice_line_number /* Added AND condition for bug 14052883 */
962 AND jcta2.tax_type = jrr.attribute_code
963 AND jrr.regime_id = jrd.regime_id
964 AND jrr.registration_type = jai_constants.regn_type_tax_types
965 AND jrd.regime_code = jai_constants.service_regime
966 /*and nvl(jadit.invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
967 and nvl(jadit.invoice_id, -9999) = nvl(p_invoice_id, -9999)*/ /*Commented nvl clause and added below by mmurtuza for bug 13983975*/
968 and jadit.invoice_distribution_id = p_invoice_distribution_id
969 and jadit.invoice_id = p_invoice_id
970 /* Commented below union all for bug 14019234 */
971 /* union all
972
973 select tds_inv_tax_id from
974 jai_ap_tds_inv_taxes jadit, JAI_AP_MATCH_INV_TAXES jamit, jai_cmn_taxes_all jcta
975 where --(upper(jcta.tax_type) like '%EXCISE%' OR UPPER(jcta.tax_type) LIKE '%CUSTOMS%')
976 jcta.tax_type in (JAI_CONSTANTS.TAX_TYPE_EXCISE, JAI_CONSTANTS.TAX_TYPE_EXC_ADDITIONAL, JAI_CONSTANTS.TAX_TYPE_EXC_OTHER,
977 JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_CUSTOMS,
978 JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS)
979 and jamit.tax_id = jcta.tax_id
980 and nvl(jamit.po_distribution_id, -9999) = p_po_distribution_id
981 and jadit.invoice_distribution_id = jamit.invoice_distribution_id
982 --and nvl(jadit.invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
983 --and nvl(jadit.invoice_id, -9999) = nvl(p_invoice_id, -9999);--Commented nvl clause and added below by mmurtuza for bug 13983975
984 and jadit.invoice_distribution_id = p_invoice_distribution_id
985 and jadit.invoice_id = p_invoice_id */;
986
987
988 cursor c_no_tds_service_excise_rec is
989 select jadit.tds_inv_tax_id from
990 jai_ap_tds_inv_taxes jadit, JAI_AP_MATCH_INV_TAXES jamit , jai_cmn_taxes_all jcta1, jai_cmn_taxes_all jcta2,
991 jai_rgm_registrations jrr, jai_rgm_definitions jrd
992 where jadit.invoice_id = jamit.invoice_id
993 and nvl(jadit.actual_tax_id, default_tax_id)=jcta1.tax_id
994 and nvl(jamit.rcv_transaction_id, -9999) = p_rcv_transaction_id
995 AND upper(jcta1.section_code) NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE')
996 and jamit.tax_id=jcta2.tax_id /* Added AND condition for bug 14052883 */
997 and jadit.invoice_line_number=jamit.invoice_line_number /* Added AND condition for bug 14052883 */
998 AND jcta2.tax_type = jrr.attribute_code
999 AND jrr.regime_id = jrd.regime_id
1000 AND jrr.registration_type = jai_constants.regn_type_tax_types
1001 AND jrd.regime_code = jai_constants.service_regime
1002 /*and nvl(jadit.invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
1003 and nvl(jadit.invoice_id, -9999) = nvl(p_invoice_id, -9999)*/ /*Commented nvl clause and added below by mmurtuza for bug 13983975*/
1004 and jadit.invoice_distribution_id = p_invoice_distribution_id
1005 and jadit.invoice_id = p_invoice_id
1006 /* Commented below union all for bug 14019234 */
1007 /* union all
1008
1009 select tds_inv_tax_id from
1010 jai_ap_tds_inv_taxes jadit, JAI_AP_MATCH_INV_TAXES jamit, jai_cmn_taxes_all jcta
1011 where --(upper(jcta.tax_type) like '%EXCISE%' OR UPPER(jcta.tax_type) LIKE '%CUSTOMS%')
1012 jcta.tax_type in (JAI_CONSTANTS.TAX_TYPE_EXCISE, JAI_CONSTANTS.TAX_TYPE_EXC_ADDITIONAL, JAI_CONSTANTS.TAX_TYPE_EXC_OTHER,
1013 JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_CUSTOMS,
1014 JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS)
1015 and jamit.tax_id = jcta.tax_id
1016 and nvl(jamit.rcv_transaction_id, -9999) = p_rcv_transaction_id
1017 and jadit.invoice_distribution_id = jamit.invoice_distribution_id
1018 --and nvl(jadit.invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
1019 --and nvl(jadit.invoice_id, -9999) = nvl(p_invoice_id, -9999);--Commented nvl clause and added below by mmurtuza for bug 13983975
1020 and jadit.invoice_distribution_id = p_invoice_distribution_id
1021 and jadit.invoice_id = p_invoice_id */;
1022
1023
1024 ln_tds_inv_tax_id_st jai_ap_tds_inv_taxes.tds_inv_tax_id%type;
1025
1026 /*End additions by mmurtuza for bug 12858951 Post review*/
1027
1028 ln_tds_inv_tax_id jai_ap_tds_inv_taxes.tds_inv_tax_id%type;
1029 ln_check_if_tax_is_input number; --File.Sql.35 Cbabu :=0;
1030 lv_actual_section_code jai_ap_tds_inv_taxes.actual_section_code%type;
1031 ln_actual_tax_id jai_ap_tds_inv_taxes.actual_tax_id%type;
1032 ln_default_tax_id jai_ap_tds_inv_taxes.default_tax_id%type;
1033 lv_consider_for_redefault jai_ap_tds_inv_taxes.consider_for_redefault%type; --File.Sql.35 Cbabu := 'N';
1034
1035 ln_default_threshold_grp_id jai_ap_tds_inv_taxes.default_threshold_grp_id%type;
1036 ln_default_cum_threshold_slab jai_ap_tds_inv_taxes.default_cum_threshold_slab_id%type;
1037 lv_default_cum_threshold_stage jai_ap_tds_inv_taxes.default_cum_threshold_stage%type;
1038 ln_default_sin_threshold_slab jai_ap_tds_inv_taxes.default_sin_threshold_slab_id%type;
1039
1040 lv_input_dff_value varchar2(50);
1041 lv_user_deleted_tax_flag jai_ap_tds_inv_taxes.user_deleted_tax_flag%type; --File.Sql.35 Cbabu := 'N';
1042 lv_process_status jai_ap_tds_inv_taxes.process_status%type;
1043
1044 ln_actual_dff_tax_id number; --Added by Bgowrava for Bug#5911913
1045
1046 begin
1047
1048 ln_check_if_tax_is_input :=0;
1049 lv_consider_for_redefault := 'N';
1050 lv_user_deleted_tax_flag := 'N';
1051
1052 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.populate_localization_inv_tax', 'START'); /* 2 */
1053
1054 ln_default_tax_id := p_default_tax_id;
1055 ln_actual_tax_id := to_number(p_input_dff_value);
1056 ln_actual_dff_tax_id := 0; --Added by Bgowrava for Bug#5911913
1057
1058 if p_section_type = 'TDS_SECTION' then
1059
1060 /* If default value is SECTION check the default and the given tax */
1061
1062 if p_default_type = 'SECTION' and p_default_section_code is not null then
1063
1064 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1065
1066 get_default_tax_from_section
1067 (
1068 p_invoice_id => p_invoice_id ,
1069 p_invoice_line_number => p_invoice_line_number ,
1070 p_invoice_distribution_id => p_invoice_distribution_id ,
1071 p_vendor_id => p_vendor_id ,
1072 p_vendor_site_id => p_vendor_site_id ,
1073 p_amount => p_amount ,
1074 p_exchange_rate => p_exchange_rate ,
1075 p_tds_section_code => p_default_section_code ,
1076 p_org_id => p_org_id ,
1077 p_accounting_date => p_accounting_date ,
1078 p_tds_tax_id => ln_default_tax_id ,
1079 p_threshold_grp_id => ln_default_threshold_grp_id ,
1080 p_cumulative_threshold_slab_id => ln_default_cum_threshold_slab ,
1081 p_cumulative_threshold_stage => lv_default_cum_threshold_stage ,
1082 p_single_threshold_slab_id => ln_default_sin_threshold_slab ,
1083 p_process_flag => p_process_flag ,
1084 P_process_message => P_process_message ,
1085 p_codepath => p_codepath
1086 );
1087
1088 end if;
1089
1090 /* process the input tds dff value */
1091 process_input_dff_tds
1092 (
1093 p_invoice_id => p_invoice_id ,
1094 p_invoice_line_number => p_invoice_line_number ,
1095 p_invoice_distribution_id => p_invoice_distribution_id ,
1096 p_input_tds_dff_value => p_input_dff_value ,
1097 p_output_tds_dff_value => lv_input_dff_value ,
1098 p_process_flag => p_process_flag ,
1099 P_process_message => P_process_message ,
1100 p_codepath => p_codepath
1101 );
1102
1103
1104 if lv_input_dff_value = 'NO TDS' then
1105 p_codepath := jai_general_pkg.plot_codepath(6.1, p_codepath); /* 6.1 */
1106 lv_user_deleted_tax_flag := 'Y';
1107 elsif lv_input_dff_value ='DEFAULTED TAX' then/*Added the elsif condition for bug 11830186 by amandali*/
1108 lv_user_deleted_tax_flag := 'Y';
1109 ln_actual_tax_id := NULL;
1110 elsif lv_input_dff_value is not null then
1111 ln_actual_tax_id := to_number(lv_input_dff_value);
1112 end if;
1113
1114 /* Value for consider_for_redefault */
1115 if ln_actual_tax_id is null and lv_user_deleted_tax_flag <> 'Y' and p_default_from not in ('PO', 'Receipt') then
1116 /* User has not given any input, or also has not specifically deleted the defaulted value or
1117 default is not because of PO or Receipt */
1118 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
1119 if p_default_type = 'SECTION' then
1120 lv_consider_for_redefault := 'Y';
1121 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
1122 end if;
1123 end if;
1124 /* Value for consider_for_redefault */
1125
1126 end if; /* if p_section_type */
1127
1128 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath); /* 19 */
1129 open c_check_if_record_exists(p_invoice_id, p_invoice_line_number, p_invoice_distribution_id);
1130 fetch c_check_if_record_exists into ln_tds_inv_tax_id, ln_actual_dff_tax_id; --Modified by Bgowrava for Bug#5911913
1131 close c_check_if_record_exists;
1132
1133 --Added below by Bgowrava for Bug#5911913
1134 if p_section_type in ('WCT_SECTION', 'ESSI_SECTION') then
1135 if ln_actual_dff_tax_id is not null and ln_actual_tax_id is null then
1136 lv_user_deleted_tax_flag := 'Y';
1137 end if ;
1138 end if ;
1139
1140 if ln_tds_inv_tax_id is null then
1141
1142 p_codepath := jai_general_pkg.plot_codepath(20, p_codepath); /* 20 */
1143 lv_process_status := 'D';
1144 insert into jai_ap_tds_inv_taxes
1145 (
1146 tds_inv_tax_id ,
1147 invoice_id ,
1148 invoice_line_number ,
1149 invoice_distribution_id ,
1150 distribution_line_number ,
1151 amount ,
1152 section_type ,
1153 default_type ,
1154 default_section_code ,
1155 default_tax_id ,
1156 actual_section_code ,
1157 actual_tax_id ,
1158 user_deleted_tax_flag ,
1159 default_threshold_grp_id ,
1160 default_cum_threshold_slab_id ,
1161 default_cum_threshold_stage ,
1162 default_sin_threshold_slab_id ,
1163 default_from ,
1164 consider_for_redefault ,
1165 process_status ,
1166 codepath ,
1167 created_by ,
1168 creation_date ,
1169 last_updated_by ,
1170 last_update_date ,
1171 last_update_login
1172 )
1173 values
1174 (
1175 jai_ap_tds_inv_taxes_s.nextval ,
1176 p_invoice_id ,
1177 p_invoice_line_number ,
1178 p_invoice_distribution_id ,
1179 P_distribution_line_number ,
1180 p_amount ,
1181 p_section_type ,
1182 p_default_type ,
1183 p_default_section_code ,
1184 ln_default_tax_id ,
1185 lv_actual_section_code ,
1186 ln_actual_tax_id ,
1187 lv_user_deleted_tax_flag ,
1188 ln_default_threshold_grp_id ,
1189 ln_default_cum_threshold_slab ,
1190 lv_default_cum_threshold_stage ,
1191 ln_default_sin_threshold_slab ,
1192 p_default_from ,
1193 lv_consider_for_redefault ,
1194 lv_process_status ,
1195 p_codepath ,
1196 fnd_global.user_id ,
1197 sysdate ,
1198 fnd_global.user_id ,
1199 sysdate ,
1200 fnd_global.login_id
1201 );
1202
1203 else
1204
1205 p_codepath := jai_general_pkg.plot_codepath(21, p_codepath); /* 21 */
1206
1207 update jai_ap_tds_inv_taxes
1208 set amount = p_amount ,
1209 section_type = p_section_type ,
1210 default_type = p_default_type ,
1211 default_section_code = p_default_section_code ,
1212 default_tax_id = ln_default_tax_id ,
1213 actual_section_code = lv_actual_section_code ,
1214 actual_tax_id = ln_actual_tax_id ,
1215 user_deleted_tax_flag = lv_user_deleted_tax_flag ,
1216 default_threshold_grp_id = ln_default_threshold_grp_id ,
1217 default_cum_threshold_slab_id = ln_default_cum_threshold_slab ,
1218 default_cum_threshold_stage = lv_default_cum_threshold_stage ,
1219 default_sin_threshold_slab_id = ln_default_sin_threshold_slab ,
1220 default_from = p_default_from ,
1221 consider_for_redefault = lv_consider_for_redefault ,
1222 process_status = lv_process_status ,
1223 codepath = p_codepath ,
1224 last_updated_by = fnd_global.user_id ,
1225 last_update_date = sysdate
1226 where tds_inv_tax_id = ln_tds_inv_tax_id;
1227
1228 /*Start Additions for bug 12858951 by mmurtuza*/
1229
1230 /*Start commenting for bug 12858951 Post review by mmurtuza*/
1231
1232 /*if (p_line_type_lookup_code = 'MISCELLANEOUS' and p_default_from not in ('PO', 'Receipt')) then
1233 open c_upd_servtax_flag_n(p_invoice_id, p_invoice_distribution_id);
1234 fetch c_upd_servtax_flag_n into ln_tds_inv_tax_id_upd_n;
1235 close c_upd_servtax_flag_n;
1236 update jai_ap_tds_inv_taxes
1237 set consider_amt_for_tds = 'N'
1238 where tds_inv_tax_id = ln_tds_inv_tax_id_upd_n;
1239
1240 if(SQL%ROWCOUNT = 0) then
1241
1242 open c_upd_servtax_flag_y(p_invoice_id, p_invoice_distribution_id);
1243 fetch c_upd_servtax_flag_y into ln_tds_inv_tax_id_upd_y;
1244 close c_upd_servtax_flag_y;
1245 update jai_ap_tds_inv_taxes
1246 set consider_amt_for_tds = 'Y'
1247 where tds_inv_tax_id = ln_tds_inv_tax_id_upd_y;
1248
1249 end if;
1250 end if;*/
1251
1252 /* End Additions for bug 12858951 by mmurtuza*/
1253
1254 /*End commenting for bug 12858951 Post review by mmurtuza*/
1255
1256 /*Start additions for bug 12858951 Post review by mmurtuza*/
1257
1258 if (p_line_type_lookup_code = 'MISCELLANEOUS' and p_po_distribution_id is null and p_rcv_transaction_id is null) then
1259 open c_no_tds_service_excise_st;
1260 fetch c_no_tds_service_excise_st into ln_tds_inv_tax_id_st;
1261 close c_no_tds_service_excise_st;
1262 if ln_tds_inv_tax_id_st is not null then
1263 update jai_ap_tds_inv_taxes
1264 set consider_amt_for_tds = 'N'
1265 where tds_inv_tax_id = ln_tds_inv_tax_id_st;
1266 else
1267 update jai_ap_tds_inv_taxes
1268 set consider_amt_for_tds = 'Y'
1269 where invoice_id=p_invoice_id
1270 and invoice_distribution_id = p_invoice_distribution_id;
1271 end if;
1272 end if;
1273
1274 if (p_line_type_lookup_code = 'MISCELLANEOUS' and p_po_distribution_id is not null) then
1275 open c_no_tds_service_excise_po;
1276 fetch c_no_tds_service_excise_po into ln_tds_inv_tax_id_st;
1277 close c_no_tds_service_excise_po;
1278 if ln_tds_inv_tax_id_st is not null then
1279 update jai_ap_tds_inv_taxes
1280 set consider_amt_for_tds = 'N'
1281 where tds_inv_tax_id = ln_tds_inv_tax_id_st;
1282 else
1283 update jai_ap_tds_inv_taxes
1284 set consider_amt_for_tds = 'Y'
1285 where invoice_id=p_invoice_id
1286 and invoice_distribution_id = p_invoice_distribution_id;
1287 end if;
1288 end if;
1289
1290 if (p_line_type_lookup_code = 'MISCELLANEOUS' and p_rcv_transaction_id is not null and p_po_distribution_id is null) then
1291 open c_no_tds_service_excise_rec;
1292 fetch c_no_tds_service_excise_rec into ln_tds_inv_tax_id_st;
1293 close c_no_tds_service_excise_rec;
1294 if ln_tds_inv_tax_id_st is not null then
1295 update jai_ap_tds_inv_taxes
1296 set consider_amt_for_tds = 'N'
1297 where tds_inv_tax_id = ln_tds_inv_tax_id_st;
1298 else
1299 update jai_ap_tds_inv_taxes
1300 set consider_amt_for_tds = 'Y'
1301 where invoice_id=p_invoice_id
1302 and invoice_distribution_id = p_invoice_distribution_id;
1303 end if;
1304 end if;
1305
1306 /*End additions for bug 12858951 Post review by mmurtuza*/
1307
1308 end if;
1309
1310
1311 if lv_consider_for_redefault = 'Y' and p_section_type = 'TDS_SECTION' then
1312
1313 p_codepath := jai_general_pkg.plot_codepath(22, p_codepath); /* 22 */
1314
1315 update jai_ap_tds_inv_taxes
1316 set default_tax_id = ln_default_tax_id
1317 where tds_inv_tax_id <> ln_tds_inv_tax_id
1318 and invoice_id = p_invoice_id
1319 and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, -9999)
1320 and consider_for_redefault = lv_consider_for_redefault
1321 and section_type = p_section_type;
1322
1323 end if; /* lv_consider_for_redefault = 'Y' */
1324
1325 if p_section_type = 'TDS_SECTION' then
1326 p_codepath := jai_general_pkg.plot_codepath(23, p_codepath); /* 23 */
1327 p_final_tds_tax_id := nvl(ln_actual_tax_id, ln_default_tax_id);
1328 end if;
1329
1330 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, NULL, 'END'); /*100 */
1331 return;
1332
1333 exception
1334 when others then
1335 p_process_flag := 'E';
1336 P_process_message := 'Error from populate_localization_inv_tax :' || sqlerrm;
1337 return;
1338
1339 end populate_localization_inv_tax;
1340
1341 /* ******************************** populate_localization_inv_tax ****************************** */
1342
1343 /* ******************************** get_default_tax_from_section ****************************** */
1344
1345 procedure get_default_tax_from_section
1346 (
1347 p_invoice_id in number,
1348 p_invoice_line_number in number default null, /* AP lines uptake */
1349 p_invoice_distribution_id in number default null, /* AP lines uptake */
1350 p_vendor_id in number,
1351 p_vendor_site_id in number,
1352 p_amount in number,
1353 p_exchange_rate in number,
1354 p_tds_section_code in varchar2,
1355 p_org_id in number,
1356 p_accounting_date in date,
1357 p_tds_tax_id out nocopy number,
1358 p_threshold_grp_id out nocopy number,
1359 p_cumulative_threshold_slab_id out nocopy number,
1360 p_cumulative_threshold_stage out nocopy varchar2,
1361 p_single_threshold_slab_id out nocopy number,
1362 p_process_flag out nocopy varchar2,
1363 P_process_message out nocopy varchar2,
1364 p_codepath in out nocopy varchar2
1365 )
1366 is
1367
1368 cursor c_get_amount_for_redefault /* AP lines uptake - introduced line */
1369 (p_invoice_id number, p_invoice_line_number number, p_invoice_distribution_id number, p_consider_for_redefault jai_ap_tds_inv_taxes.consider_for_redefault%type) is--rchandan for bug#4428980
1370 select sum(amount)
1371 from jai_ap_tds_inv_taxes
1372 where invoice_id = p_invoice_id
1373 --Commented out by Eric Ma for PF bug#7340818 and consider_for_redefault = p_consider_for_redefault--rchandan for bug#4428980
1374 --Commented out by Eric Ma for PF bug#7340818 and user_deleted_tax_flag <> 'Y'
1375 and ( (p_invoice_distribution_id is null ) or (p_invoice_distribution_id is not null and invoice_distribution_id <> p_invoice_distribution_id ) )
1376 /*and ( (p_invoice_line_number is null ) or ( p_invoice_line_number is not null and invoice_line_number <> p_invoice_line_number) )
1377 This is not required as we need to consider all distributions for redefaulting*/
1378 and invoice_distribution_id < p_invoice_distribution_id; --Added by Eric Ma for PF bug#7340818
1379
1380 cursor c_get_threshold
1381 (p_vendor_id number, p_vendor_site_id number, p_tds_section_code varchar2,p_section_type jai_cmn_taxes_all.section_type%type) is--rchandan for bug#4428980
1382 select threshold_hdr_id
1383 from jai_ap_tds_th_vsite_v
1384 where vendor_id = p_vendor_id
1385 and vendor_site_id = p_vendor_site_id
1386 and section_type = p_section_type--rchandan for bug#4428980
1387 and section_code = p_tds_section_code;
1388
1389 cursor c_jai_ap_tds_thhold_grps(p_threshold_grp_id number) is
1390 select (
1391 nvl(total_invoice_amount, 0) -
1392 nvl(total_invoice_cancel_amount, 0) -
1393 nvl(total_invoice_apply_amount, 0) +
1394 nvl(total_invoice_unapply_amount, 0)
1395 )
1396 total_invoice_amount
1397 from jai_ap_tds_thhold_grps
1398 where threshold_grp_id = p_threshold_grp_id;
1399
1400 cursor c_jai_ap_tds_thhold_slabs
1401 ( p_threshold_hdr_id number, p_threshold_type varchar2, p_amount number) is
1402 select threshold_slab_id, threshold_type_id, from_amount, to_amount
1403 from jai_ap_tds_thhold_slabs
1404 where threshold_hdr_id = p_threshold_hdr_id
1405 and threshold_type_id in
1406 ( select threshold_type_id
1407 from jai_ap_tds_thhold_types
1408 where threshold_hdr_id = p_threshold_hdr_id
1409 and threshold_type = p_threshold_type
1410 /* Bug 4522540. Added by Lakshmi Gopalsami
1411 Added the date condition */
1412 and trunc(p_accounting_Date) between from_date
1413 and nvl(to_date, p_accounting_date + 1)
1414 )
1415 and nvl(to_amount, p_amount) >= p_amount
1416 order by from_amount asc;
1417
1418 cursor c_jai_ap_tds_thhold_taxes(p_threshold_slab_id number, p_org_id number) is
1419 select tax_id
1420 from jai_ap_tds_thhold_taxes
1421 where threshold_slab_id = p_threshold_slab_id
1422 and operating_unit_id = p_org_id;
1423
1424 lv_attr_code VARCHAR2(25);
1425 lv_attr_type_code VARCHAR2(25);
1426 lv_tds_regime VARCHAR2(25);
1427 lv_regn_type_others VARCHAR2(25);
1428
1429 cursor c_get_fin_year(p_gl_date date, p_org_id number) is
1430 select fin_year
1431 from jai_ap_tds_years
1432 where tan_no in /* where clause and subquery added by ssumaith - bug# 4448789*/
1433 (
1434 SELECT attribute_value
1435 FROM JAI_RGM_ORG_REGNS_V
1436 WHERE regime_code = lv_tds_regime
1437 AND registration_type = lv_regn_type_others
1438 AND attribute_type_code = lv_attr_type_Code
1439 AND attribute_code = lv_attr_code
1440 AND organization_id = p_org_id
1441 )
1442 and p_gl_date between start_date and end_date;
1443
1444
1445 cursor c_get_vendor_pan_tan(p_vendor_id number , p_vendor_site_id number) is
1446 select c.pan_no pan_no,
1447 d.org_tan_num tan_no
1448 from po_vendors a,
1449 po_vendor_sites_all b,
1450 jai_ap_tds_vendor_hdrs c,
1451 jai_ap_tds_org_tan_v d --- JAI_AP_TDS_ORG_TANS is changed to view jai_ap_tds_org_tan_v 4323338
1452 where a.vendor_id = b.vendor_id
1453 and b.vendor_id = c.vendor_id
1454 and b.vendor_site_id = c.vendor_site_id
1455 and b.org_id = d.organization_id
1456 and a.vendor_id = p_vendor_id
1457 and b.vendor_site_id = p_vendor_site_id;
1458
1459 cursor c_get_threshold_group
1460 (p_vendor_id number, p_tan_no varchar2, p_pan_no varchar2, p_tds_section_code varchar2 , p_fin_year number,p_section_type jai_ap_tds_thhold_grps.section_type%type) is--rchandan for bug#4428980
1461 select threshold_grp_id
1462 from jai_ap_tds_thhold_grps
1463 where vendor_id = p_vendor_id
1464 and section_type = p_section_type --rchandan for bug#4428980
1465 and section_code = p_tds_section_code
1466 and org_tan_num = p_tan_no
1467 and vendor_pan_num = p_pan_no
1468 and fin_year = p_fin_year;
1469
1470 /*Bug 12640899 - Fetch Invoice Amount*/
1471 CURSOR c_get_invoice_amount
1472 IS
1473 SELECT invoice_amount
1474 FROM ap_invoices_all
1475 WHERE invoice_id = p_invoice_id;
1476
1477 r_get_threshold c_get_threshold%rowtype;
1478 r_jai_ap_tds_thhold_slabs c_jai_ap_tds_thhold_slabs%rowtype;
1479 r_jai_ap_tds_thhold_taxes c_jai_ap_tds_thhold_taxes%rowtype;
1480
1481 lv_pan_no jai_ap_tds_vendor_hdrs.pan_no%type;
1482 lv_tan_no jai_ap_tds_org_tan_v.org_tan_num %type;--- JAI_AP_TDS_ORG_TANS is changed to view jai_ap_tds_org_tan_v 4323338
1483
1484
1485 ln_total_invoice_amount number;
1486 ln_amount_for_redefault number;
1487 ln_fin_year number;
1488 ln_threshold_grp_id number;
1489 ln_amount number;
1490 ln_invoice_amount number;/*Bug 12640899*/
1491
1492
1493 begin
1494
1495 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.get_default_tax_from_section', 'START'); /* 1 */
1496
1497 ln_amount := p_amount * nvl(p_exchange_rate, 1);
1498
1499
1500 lv_attr_code := 'TAN NO';
1501 lv_attr_type_code := 'PRIMARY';
1502 lv_tds_regime := 'TDS';
1503 lv_regn_type_others := 'OTHERS';
1504
1505
1506
1507
1508 /* Get the fin year */
1509 open c_get_fin_year(p_accounting_date, p_org_id);
1510 fetch c_get_fin_year into ln_fin_year;
1511 close c_get_fin_year;
1512
1513 /* Get Pan number and Tan number for the vendor */
1514 open c_get_vendor_pan_tan(p_vendor_id, p_vendor_site_id);
1515 fetch c_get_vendor_pan_tan into lv_pan_no, lv_tan_no;
1516 close c_get_vendor_pan_tan;
1517
1518
1519 open c_get_amount_for_redefault(p_invoice_id, p_invoice_line_number, p_invoice_distribution_id,'Y');--rchandan for bug#4428980
1520 fetch c_get_amount_for_redefault into ln_amount_for_redefault;
1521 close c_get_amount_for_redefault;
1522
1523 ln_amount_for_redefault := nvl(ln_amount_for_redefault, 0);
1524 ln_amount_for_redefault := ln_amount_for_redefault * nvl(p_exchange_rate, 1);
1525
1526 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
1527 /* Get threshold id */
1528 open c_get_threshold(p_vendor_id, p_vendor_site_id, p_tds_section_code,'TDS_SECTION');--rchandan for bug#4428980
1529 fetch c_get_threshold into r_get_threshold;
1530 close c_get_threshold;
1531
1532
1533 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1534 if r_get_threshold.threshold_hdr_id is null then
1535 /* No threshold has been setup for the section and vendor,
1536 it is not possible to default a tax from section */
1537 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1538 p_tds_tax_id := null;
1539 goto exit_from_procedure;
1540 end if;
1541
1542 /* Get threshold group id */
1543 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1544 open c_get_threshold_group(p_vendor_id, lv_tan_no, lv_pan_no, p_tds_section_code, ln_fin_year,'TDS_SECTION');
1545 fetch c_get_threshold_group into ln_threshold_grp_id;
1546 close c_get_threshold_group;
1547
1548 /* if there is no threshold group details,
1549 it means no transaction has happened for that section and vendor combination */
1550 if ln_threshold_grp_id is not null then
1551 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
1552 open c_jai_ap_tds_thhold_grps(ln_threshold_grp_id);
1553 fetch c_jai_ap_tds_thhold_grps into ln_total_invoice_amount;
1554 close c_jai_ap_tds_thhold_grps;
1555 p_threshold_grp_id := ln_threshold_grp_id;
1556 end if;
1557
1558 ln_total_invoice_amount := nvl(ln_total_invoice_amount, 0 ) ;
1559
1560 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
1561 /* Check if Cumulative threshold is reached */
1562 open c_jai_ap_tds_thhold_slabs
1563 (r_get_threshold.threshold_hdr_id, 'CUMULATIVE', ln_total_invoice_amount + ln_amount + ln_amount_for_redefault);
1564 fetch c_jai_ap_tds_thhold_slabs into r_jai_ap_tds_thhold_slabs;
1565 close c_jai_ap_tds_thhold_slabs;
1566
1567 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
1568
1569 p_cumulative_threshold_slab_id := r_jai_ap_tds_thhold_slabs.threshold_slab_id;
1570
1571 if ln_total_invoice_amount >= r_jai_ap_tds_thhold_slabs.from_amount then
1572 /* Cumulative threshold amount is already reached */
1573 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
1574 p_cumulative_threshold_stage := 'AFTER THRESHOLD';
1575
1576 elsif (ln_total_invoice_amount + ln_amount + ln_amount_for_redefault) >= r_jai_ap_tds_thhold_slabs.from_amount then
1577
1578 /* Threshold reached with this transaction */
1579 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
1580 p_cumulative_threshold_stage := 'AT THRESHOLD';
1581
1582 else
1583
1584 p_cumulative_threshold_stage := 'BEFORE THRESHOLD';
1585
1586 /* Cumulative threshold is not reached, default the tax id anyway but
1587 check for SINGLE invoice threshold. This has to be checked with only invoice amount */
1588
1589 r_jai_ap_tds_thhold_slabs:= null;
1590 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
1591 /*Bug 12640899 - Fetch Total Invoice Amount before verifying if the Invoice breaches SINGLE Threshold.
1592 Each (distribution amount + previous distribution amounts) should not be compared with SINGLE Threshold From Amount*/
1593 OPEN c_get_invoice_amount;
1594 FETCH c_get_invoice_amount INTO ln_invoice_amount;
1595 CLOSE c_get_invoice_amount;
1596
1597 open c_jai_ap_tds_thhold_slabs(r_get_threshold.threshold_hdr_id, 'SINGLE', ln_invoice_amount);
1598 fetch c_jai_ap_tds_thhold_slabs into r_jai_ap_tds_thhold_slabs;
1599 close c_jai_ap_tds_thhold_slabs;
1600
1601 if ln_invoice_amount >= r_jai_ap_tds_thhold_slabs.from_amount then
1602 /* Cumulative threshold amount is reached */
1603 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
1604 p_single_threshold_slab_id := r_jai_ap_tds_thhold_slabs.threshold_slab_id;
1605 end if;
1606
1607 end if; /* Cumulative or single threshold amount */
1608
1609 /* Get the tax id attached to the slab */
1610 open c_jai_ap_tds_thhold_taxes(nvl(p_single_threshold_slab_id, p_cumulative_threshold_slab_id), p_org_id);
1611 fetch c_jai_ap_tds_thhold_taxes into r_jai_ap_tds_thhold_taxes;
1612 close c_jai_ap_tds_thhold_taxes;
1613
1614 p_tds_tax_id := r_jai_ap_tds_thhold_taxes.tax_id;
1615
1616 << exit_from_procedure >>
1617 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath, null, 'END'); /* 13 */
1618 return;
1619
1620 exception
1621 when others then
1622 p_process_flag := 'E';
1623 P_process_message := 'Error from get_default_tax_from_section :' || sqlerrm;
1624 return;
1625
1626 end get_default_tax_from_section;
1627
1628
1629 /* ******************************** get_default_tax_from_section ****************************** */
1630
1631
1632 /* ********************************************* process_input_dff_tds ********************************************* */
1633 procedure process_input_dff_tds
1634 (
1635 p_invoice_id in number,
1636 p_invoice_line_number in number default null, /* AP lines uptake */
1637 p_invoice_distribution_id in number default null, /* AP lines uptake */
1638 p_input_tds_dff_value in varchar2,
1639 p_output_tds_dff_value out nocopy varchar2,
1640 p_process_flag out nocopy varchar2,
1641 P_process_message out nocopy varchar2,
1642 p_codepath in out nocopy varchar2
1643 )
1644 is
1645
1646 cursor c_get_existing_dff_values
1647 (p_invoice_id number, p_invoice_line_number number, p_invoice_distribution_id number, p_section_type jai_cmn_taxes_all.section_type%type) is--rchandan for bug#4428980
1648 select tds_inv_tax_id, default_tax_id, actual_tax_id
1649 from jai_ap_tds_inv_taxes
1650 where invoice_id = p_invoice_id
1651 and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, -9999)
1652 and nvl(invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
1653 and section_type = p_section_type;--rchandan for bug#4428980
1654
1655
1656 r_get_existing_dff_values c_get_existing_dff_values%rowtype;
1657
1658
1659 begin
1660
1661 p_codepath :=
1662 jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.process_input_dff_tds', 'START'); /* 1 */
1663
1664 p_output_tds_dff_value := p_input_tds_dff_value;
1665
1666 open c_get_existing_dff_values(p_invoice_id, p_invoice_line_number, p_invoice_distribution_id,'TDS_SECTION');--rchandan for bug#4428980
1667 fetch c_get_existing_dff_values into r_get_existing_dff_values;
1668 close c_get_existing_dff_values;
1669
1670 if r_get_existing_dff_values.tds_inv_tax_id is null then
1671 /* TDS defaultation Record does not exist */
1672 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
1673 goto exit_from_procedure;
1674 end if;
1675
1676 /* Control comes here only when defaultation details already exists */
1677
1678 if p_input_tds_dff_value is null then
1679
1680 /* user has not provided any input or has deleted the defaulted or earlier given value */
1681 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1682
1683 if r_get_existing_dff_values.default_tax_id is not null or
1684 r_get_existing_dff_values.actual_tax_id is not null then
1685
1686 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1687 /* User has deleted the earlier given or defaulted value no TDS should be deducted. */
1688 p_output_tds_dff_value := 'NO TDS';
1689
1690 end if;
1691
1692 elsif p_input_tds_dff_value = r_get_existing_dff_values.default_tax_id then
1693 /* User has given the same value as default. Actual can be set to null */
1694 /*Added the below condition for bug 11830186 by amandali*/
1695 if(r_get_existing_dff_values.actual_tax_id is null)then
1696 /*If the tds tax_id is attached by default */
1697 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1698 p_output_tds_dff_value := 'DEFAULTED TAX';
1699 else
1700 /*if the default tax id is given manually*/
1701 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 5 */
1702 p_output_tds_dff_value := null;
1703 end if;
1704 end if; /* p_input_tds_dff_value */
1705
1706 << exit_from_procedure >>
1707 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
1708 return;
1709
1710 exception
1711 when others then
1712 p_process_flag := 'E';
1713 P_process_message := 'Error from jai_ap_tds_tax_defaultation.process_input_dff_tds :' || sqlerrm;
1714 return;
1715 end process_input_dff_tds;
1716
1717 /* ********************************************* process_input_dff_tds ********************************************* */
1718
1719 /* ********************************************* process_delete ********************************************* */
1720
1721 procedure process_delete
1722 (
1723 p_invoice_id in number,
1724 p_invoice_line_number in number default null, /* AP lines uptake */
1725 p_invoice_distribution_id in number default null,
1726 p_process_flag out nocopy varchar2,
1727 P_process_message out nocopy varchar2
1728 )
1729 is
1730 /* Change History
1731 -------------------------------------------------------------------------------
1732 S.No Date Author and Details
1733 -------------------------------------------------------------------------------
1734 1. 16/05/2008 JMEENA for bug#6995295.
1735 Added NVL for process_status
1736 */
1737 begin
1738
1739 /* AP lines uptake - introduced line */
1740 delete jai_ap_tds_inv_taxes
1741 where invoice_id = p_invoice_id
1742 and (
1743 (p_invoice_line_number is null ) or
1744 (p_invoice_line_number is not null and invoice_line_number = p_invoice_line_number)
1745 )
1746 and (
1747 (p_invoice_distribution_id is null ) or
1748 (p_invoice_distribution_id is not null and invoice_distribution_id = p_invoice_distribution_id)
1749 )
1750 and NVL(process_status,'D') <> 'P'; -- Added NVL by JMEENA for bug#6995295
1751
1752 << exit_from_procedure >>
1753 return;
1754
1755 exception
1756 when others then
1757 p_process_flag := 'E';
1758 P_process_message := 'Error from jai_ap_tds_tax_defaultation.process_delete :' || sqlerrm;
1759 return;
1760 end process_delete;
1761
1762 /* ********************************************* process_delete ********************************************* */
1763
1764
1765 /* ********************************************* check_old_transaction ********************************************* */
1766 procedure check_old_transaction
1767 (
1768 p_invoice_id in number default null,
1769 p_invoice_distribution_id in number default null,
1770 p_new_transaction out nocopy varchar2
1771 )
1772 is
1773
1774 cursor c_jai_ap_tds_inv_taxes_inv(p_invoice_id number) is
1775 select 'Y'
1776 from jai_ap_tds_inv_taxes
1777 where invoice_id = p_invoice_id;
1778
1779
1780 cursor c_jai_ap_tds_inv_taxes_dist(p_invoice_distribution_id number) is
1781 select 'Y'
1782 from jai_ap_tds_inv_taxes
1783 where invoice_distribution_id = p_invoice_distribution_id;
1784
1785 lv_new_transaction varchar2(1);
1786
1787 begin
1788
1789 lv_new_transaction := 'N';
1790
1791 if p_invoice_id is not null then
1792
1793 open c_jai_ap_tds_inv_taxes_inv(p_invoice_id);
1794 fetch c_jai_ap_tds_inv_taxes_inv into lv_new_transaction;
1795 close c_jai_ap_tds_inv_taxes_inv;
1796
1797 elsif p_invoice_distribution_id is not null then
1798
1799 open c_jai_ap_tds_inv_taxes_dist(p_invoice_distribution_id);
1800 fetch c_jai_ap_tds_inv_taxes_dist into lv_new_transaction;
1801 close c_jai_ap_tds_inv_taxes_dist;
1802
1803 end if;
1804
1805 p_new_transaction := nvl(lv_new_transaction, 'N');
1806
1807 << exit_from_procedure >>
1808 return;
1809
1810 exception
1811 when others then
1812 return;
1813 end check_old_transaction;
1814
1815
1816
1817 /* ********************************************* check_old_transaction ********************************************* */
1818
1819 END jai_ap_tds_tax_defaultation;