[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.5.12010000.3 2008/11/25 11:03:56 mbremkum 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. 24-Aug-2008 JMEENA for bug#7309921
58 Modified procedure populate_localization_inv_tax and added cursor c_check_user_deleted_tax_flag to check the
59 user_deleted_tax_flag.
60 Added the condition if user_deleted_tax_flag is Y then do not default the TDS Tax.
61 Called the procedure get_default_tax_from_section after process_input_dff_tds, earlier it was being called before process_input_dff_tds.
62
63 ---------------------------------------------------------------------------- */
64
65 procedure process_invoice
66 (
67 p_invoice_id in number,
68 p_invoice_line_number in number default null,
69 p_invoice_distribution_id in number default null,
70 p_line_type_lookup_code in varchar2,
71 p_distribution_line_number in number,
72 p_parent_reversal_id in number,
73 p_reversal_flag in varchar2,
74 p_amount in number,
75 p_invoice_currency_code in varchar2,
76 p_exchange_rate in number,
77 p_set_of_books_id in number,
78 p_po_distribution_id in number default null,
79 p_rcv_transaction_id in number default null,
80 p_vendor_id in number,
81 p_vendor_site_id in number,
82 p_input_dff_value_tds in varchar2,
83 p_input_dff_value_wct in varchar2,
84 p_input_dff_value_essi in varchar2,
85 p_org_id in number,
86 p_accounting_date in date,
87 p_call_from in varchar2,
88 p_final_tds_tax_id out nocopy number,
89 p_process_flag out nocopy varchar2,
90 p_process_message out nocopy varchar2,
91 p_codepath in out nocopy varchar2
92 )
93 is
94
95 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
96 select currency_code
97 from gl_sets_of_books
98 where set_of_books_id = cp_set_of_books_id;
99
100 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
101
102 lv_default_tds_section_code jai_ap_tds_inv_taxes.default_section_code%type;
103 ln_default_tds_tax_id jai_ap_tds_inv_taxes.default_tax_id%type;
104 lv_default_from jai_ap_tds_inv_taxes.default_from%type;
105 lv_default_type jai_ap_tds_inv_taxes.default_type%type;
106 ln_exchange_rate ap_invoices_all.exchange_rate%type;
107
108
109 begin
110
111 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.process_invoice', 'START'); /* 1 */
112 /* Check if defaulting can happen for the invoice */
113
114 validate_status_for_default
115 (
116 p_invoice_id => p_invoice_id,
117 p_invoice_line_number => p_invoice_line_number,
118 p_invoice_distribution_id => p_invoice_distribution_id,
119 p_line_type_lookup_code => p_line_type_lookup_code,
120 p_process_flag => p_process_flag,
121 P_process_message => P_process_message,
122 p_codepath => p_codepath
123 );
124
125 if nvl(p_process_flag, 'N') <> 'Y' then
126 /* p_process_flag has the value of Y whenever TDS defaultation can take place */
127 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
128 goto exit_from_procedure;
129 end if;
130
131 open c_gl_sets_of_books(p_set_of_books_id);
132 fetch c_gl_sets_of_books into r_gl_sets_of_books;
133 close c_gl_sets_of_books;
134
135 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
136 if r_gl_sets_of_books.currency_code <> p_invoice_currency_code then
137 /* Foreign currency invoice */
138 p_codepath := jai_general_pkg.plot_codepath(3.1, p_codepath); /* 3.1 */
139 ln_exchange_rate := p_exchange_rate;
140 end if;
141
142 ln_exchange_rate := nvl(ln_exchange_rate, 1);
143
144
145 if p_input_dff_value_wct is not null then
146
147 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
148
149 populate_localization_inv_tax
150 (
151 p_invoice_id => p_invoice_id,
152 p_invoice_line_number => p_invoice_line_number,
153 p_invoice_distribution_id => p_invoice_distribution_id,
154 P_distribution_line_number => P_distribution_line_number,
155 p_amount => p_amount,
156 p_exchange_rate => ln_exchange_rate,
157 p_section_type => 'WCT_SECTION',
158 p_default_type => null,
159 p_default_section_code => null,
160 p_default_tax_id => null,
161 p_input_dff_value => p_input_dff_value_wct,
162 p_default_from => null,
163 p_vendor_id => p_vendor_id,
164 p_vendor_site_id => p_vendor_site_id,
165 p_org_id => p_org_id,
166 p_accounting_date => p_accounting_date,
167 p_final_tds_tax_id => p_final_tds_tax_id,
168 p_process_flag => p_process_flag,
169 P_process_message => P_process_message,
170 p_codepath => p_codepath
171 );
172
173
174
175 if nvl(p_process_flag, 'N') = 'E' then
176 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
177 goto exit_from_procedure;
178 end if;
179
180 end if; /* p_input_dff_value_wct */
181
182
183 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
184
185 if p_input_dff_value_essi is not null then
186
187 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
188
189 populate_localization_inv_tax
190 (
191 p_invoice_id => p_invoice_id,
192 p_invoice_line_number => p_invoice_line_number,
193 p_invoice_distribution_id => p_invoice_distribution_id,
194 P_distribution_line_number => P_distribution_line_number,
195 p_amount => p_amount,
196 p_exchange_rate => ln_exchange_rate,
197 p_section_type => 'ESSI_SECTION',
198 p_default_type => null,
199 p_default_section_code => null,
200 p_default_tax_id => null,
201 p_input_dff_value => p_input_dff_value_essi,
202 p_default_from => null,
203 p_vendor_id => p_vendor_id,
204 p_vendor_site_id => p_vendor_site_id,
205 p_org_id => p_org_id ,
206 p_accounting_date => p_accounting_date,
207 p_final_tds_tax_id => p_final_tds_tax_id,
208 p_process_flag => p_process_flag,
209 P_process_message => P_process_message,
210 p_codepath => p_codepath
211 );
212
213 if nvl(p_process_flag, 'N') = 'E' then
214 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
215 goto exit_from_procedure;
216 end if;
217
218 end if; /* p_input_dff_value_essi */
219
220
221 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
222
223 if p_rcv_transaction_id is not null then
224
225 /* If the invoice has a receipt reference get the tax from receipt */
226 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
227
228 default_tds_from_receipt
229 (
230 p_invoice_id => p_invoice_id,
231 p_invoice_line_number => p_invoice_line_number,
232 p_invoice_distribution_id => p_invoice_distribution_id,
233 p_line_type_lookup_code => p_line_type_lookup_code,
234 p_distribution_line_number => p_distribution_line_number,
235 p_rcv_transaction_id => p_rcv_transaction_id,
236 p_tds_section_code => lv_default_tds_section_code,
237 p_tds_tax_id => ln_default_tds_tax_id,
238 p_default_from => lv_default_from,
239 p_process_flag => p_process_flag,
240 P_process_message => P_process_message,
241 p_codepath => p_codepath
242 );
243
244 if nvl(p_process_flag, 'N') = 'E' then
245 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
246 goto exit_from_procedure;
247 end if;
248
249 elsif p_po_distribution_id is not null then
250 /* If the invoice has a PO reference get the tax from PO */
251
252 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
253 default_tds_from_po
254 (
255 p_invoice_id => p_invoice_id,
256 p_invoice_line_number => p_invoice_line_number,
257 p_invoice_distribution_id => p_invoice_distribution_id,
258 p_line_type_lookup_code => p_line_type_lookup_code,
259 p_distribution_line_number => p_distribution_line_number,
260 p_po_distribution_id => p_po_distribution_id,
261 p_tds_section_code => lv_default_tds_section_code,
262 p_tds_tax_id => ln_default_tds_tax_id,
263 p_default_from => lv_default_from,
264 p_process_flag => p_process_flag,
265 P_process_message => P_process_message,
266 p_codepath => p_codepath
267 );
268
269 if nvl(p_process_flag, 'N') = 'E' then
270 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath); /* 13 */
271 goto exit_from_procedure;
272 end if;
273
274
275 end if;
276
277 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
278
279 if ln_default_tds_tax_id is null then
280 /* Default from setup if not already defaulted from PO or Receipt */
281 default_tds_from_setup
282 (
283 p_vendor_id => p_vendor_id,
284 p_vendor_site_id => p_vendor_site_id,
285 p_default_type => lv_default_type,
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 end if;
294
295 if nvl(p_process_flag, 'N') = 'E' then
296 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
297 goto exit_from_procedure;
298 end if;
299
300 p_codepath := jai_general_pkg.plot_codepath(16, p_codepath); /* 16 */
301 validate_default_tds
302 (
303 p_vendor_id => p_vendor_id,
304 p_vendor_site_id => p_vendor_site_id,
305 p_tds_section_code => lv_default_tds_section_code,
306 p_tds_tax_id => ln_default_tds_tax_id,
307 p_process_flag => p_process_flag,
308 P_process_message => P_process_message,
309 p_codepath => p_codepath
310 );
311
312 if nvl(p_process_flag, 'N') = 'E' then
313 p_codepath := jai_general_pkg.plot_codepath(16.1, p_codepath); /* 16.1 */
314 goto exit_from_procedure;
315 end if;
316
317 p_codepath := jai_general_pkg.plot_codepath(17, p_codepath); /* 17 */
318
319 populate_localization_inv_tax
320 (
321 p_invoice_id => p_invoice_id,
322 p_invoice_line_number => p_invoice_line_number,
323 p_invoice_distribution_id => p_invoice_distribution_id,
324 P_distribution_line_number => P_distribution_line_number,
325 p_amount => p_amount,
326 p_exchange_rate => ln_exchange_rate,
327 p_section_type => 'TDS_SECTION',
328 p_default_type => lv_default_type,
329 p_default_section_code => lv_default_tds_section_code,
333 p_vendor_id => p_vendor_id,
330 p_default_tax_id => ln_default_tds_tax_id,
331 p_input_dff_value => p_input_dff_value_tds,
332 p_default_from => lv_default_from,
334 p_vendor_site_id => p_vendor_site_id,
335 p_org_id => p_org_id ,
336 p_accounting_date => p_accounting_date,
337 p_final_tds_tax_id => p_final_tds_tax_id,
338 p_process_flag => p_process_flag,
339 P_process_message => P_process_message,
340 p_codepath => p_codepath
341 );
342
343 p_codepath := jai_general_pkg.plot_codepath(18, p_codepath); /* 18 */
344
345 << exit_from_procedure >>
346 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath, null, 'END'); /* 19 */
347 return;
348
349 exception
350 when others then
351 p_process_flag := 'E';
352 P_process_message := 'Error from process_invoice :' || sqlerrm;
353 return;
354 end process_invoice;
355
356 /* ************************************* process_invoice ************************************ */
357
358
359 /* ******************************* validate_status_for_default ****************************** */
360 procedure validate_status_for_default
361 (
362 p_invoice_id in number,
363 p_invoice_line_number in number default null,
364 p_invoice_distribution_id in number default null,
365 p_line_type_lookup_code in varchar2,
366 p_process_flag out nocopy varchar2,
367 p_process_message out nocopy varchar2,
368 p_codepath in out nocopy varchar2
369 )
370 is
371
372
373 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
374 select 'P'
375 from jai_ap_tds_inv_taxes
376 where invoice_id = p_invoice_id
377 and process_status = p_process_status;
378
379 cursor c_check_old_tds_processed(p_invoice_id number) is
380 select 'Y'
381 from jai_ap_tds_invoices
382 where invoice_id = p_invoice_id;
383
384 lv_tds_process_status varchar2(1);
385
386 begin
387
388 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.validate_status_for_default', 'START'); /* 1 */
389 open c_check_tds_already_processed(p_invoice_id,'P');--rchandan for bug#4428980
390 fetch c_check_tds_already_processed into lv_tds_process_status;
391 close c_check_tds_already_processed;
392
393 if nvl(lv_tds_process_status, 'N') = 'P'then
394 /* TDS invoice has already been processed for this invoice, Cannot process again */
395 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
396 p_process_flag := 'P';
397 P_process_message := 'TDS is already processed for this invoice ';
398 goto exit_from_procedure;
399 end if;
400
401 lv_tds_process_status := null;
402 open c_check_old_tds_processed(p_invoice_id);
403 fetch c_check_old_tds_processed into lv_tds_process_status;
404 close c_check_old_tds_processed;
405
406 if nvl(lv_tds_process_status, 'N') = 'Y' then
407 p_codepath := jai_general_pkg.plot_codepath(2.1, p_codepath); /* 2.1 */
408 p_process_flag := 'P';
409 P_process_message := 'TDS is already processed for this invoice in the old system, cannot process.';
410 goto exit_from_procedure;
411 end if;
412
413
414 /* Currently defaulting happens only for ITEM lines
415 This will be extended to MISCELLANEOUS lines once the tax precedences ER is in place */
416
417 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
418 if p_line_type_lookup_code not in ('ITEM', 'MISCELLANEOUS', 'ACCRUAL' ) then /* ACCRUAL - AP lines uptake */
419 p_process_flag := 'X';
420 P_process_message := 'TDS is not applicable as the line is not an ITEM, ACCRUAL or or MISCELLANEOUS line';
421 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
422 goto exit_from_procedure;
423 end if;
424
425
426 << exit_from_procedure >>
427 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
428 if p_process_flag is null then
429 /* All checks fine, TDS defaultation can take place */
430 p_process_flag := 'Y';
431 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
432 end if;
433
434 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath, null, 'END'); /* 7 */
435 return;
436
437 exception
438 when others then
439 p_process_flag := 'E';
440 P_process_message := 'Error from validate_status_for_default :' || sqlerrm;
441 return;
442 end validate_status_for_default;
443
444 /* ******************************* validate_status_for_default ****************************** */
445
446
447 /* *********************************** default_from_receipt ********************************** */
448
452 p_invoice_line_number in number default null,
449 procedure default_tds_from_receipt
450 (
451 p_invoice_id in number,
453 p_invoice_distribution_id in number default null,
454 p_line_type_lookup_code in varchar2,
455 p_distribution_line_number in number default null, /* AP lines uptake */
456 p_rcv_transaction_id in number,
457 p_tds_section_code out nocopy varchar2,
458 p_tds_tax_id out nocopy number,
459 p_default_from out nocopy varchar2,
460 p_process_flag out nocopy varchar2,
461 P_process_message out nocopy varchar2,
462 p_codepath in out nocopy varchar2
463 )
464 is
465
466 cursor c_rcv_transactions(p_rcv_transaction_id number) is
467 select shipment_header_id,
468 shipment_line_id
469 from rcv_transactions
470 where transaction_id = p_rcv_transaction_id;
471
472 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
473 select jtc.section_code section_code,
474 jrtl.tax_id tax_id
475 from jai_rcv_line_taxes jrtl,
476 jai_cmn_taxes_all jtc
477 where jtc.tax_id = jrtl.tax_id
478 and jrtl.tax_type = jai_constants.tax_type_tds
479 and jtc.section_type = p_section_type--rchandan for bug#4428980
480 and jrtl.shipment_header_id = p_shipment_header_id
481 and jrtl.shipment_line_id = p_shipment_line_id
482 order by jrtl.tax_line_no asc;
483
484 c_rec_rcv_transactions c_rcv_transactions%rowtype;
485 c_rec_check_receipt_tds_tax c_check_receipt_tds_tax%rowtype;
486
487 begin
488
489 /* Get Receipt Details */
490 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.default_tds_from_receipt', 'START'); /* 1 */
491 open c_rcv_transactions(p_rcv_transaction_id);
492 fetch c_rcv_transactions into c_rec_rcv_transactions;
493 close c_rcv_transactions;
494
495 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
496 /* Check if TDS type of tax exists against the shipment line in Receipt taxes */
497 open c_check_receipt_tds_tax
498 (c_rec_rcv_transactions.shipment_header_id, c_rec_rcv_transactions.shipment_line_id,'TDS_SECTION');--rchandan for bug#4428980
499 fetch c_check_receipt_tds_tax into c_rec_check_receipt_tds_tax;
500 close c_check_receipt_tds_tax;
501
502 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
503 if c_rec_check_receipt_tds_tax.section_code is null then
504 /* No TDS tax exists against the receipt line */
505 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
506 goto exit_from_procedure;
507 end if;
508
509 /* Control comes here only when a TDS tax exists against the receipt */
510
511 p_tds_section_code := c_rec_check_receipt_tds_tax.section_code;
512 p_tds_tax_id := c_rec_check_receipt_tds_tax.tax_id;
513 p_default_from := 'Receipt';
514
515 << exit_from_procedure >>
516 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath, null, 'END'); /* 5 */
517 return;
518
519 exception
520 when others then
521 p_process_flag := 'E';
522 P_process_message := 'Error from default_from_receipt :' || sqlerrm;
523 return;
524
525 end default_tds_from_receipt;
526
527 /* *********************************** default_from_receipt ********************************** */
528
529 /* ************************************** default_from_po ************************************ */
530 procedure default_tds_from_po
531 (
532 p_invoice_id in number,
533 p_invoice_line_number in number default null,
534 p_invoice_distribution_id in number default null,
535 p_line_type_lookup_code in varchar2,
536 p_distribution_line_number in number default null, /* AP lines uptake */
537 p_po_distribution_id in number,
538 p_tds_section_code out nocopy varchar2,
539 p_tds_tax_id out nocopy number,
540 p_default_from out nocopy varchar2,
541 p_process_flag out nocopy varchar2,
542 P_process_message out nocopy varchar2,
543 p_codepath in out nocopy varchar2
544 )
545 is
546
547 cursor c_po_distributions_all(p_po_distribution_id number) is
548 select po_header_id,
549 po_line_id,
550 line_location_id
551 from po_distributions_all
552 where po_distribution_id = p_po_distribution_id;
553
554
555 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
556 is
560 jai_cmn_taxes_all jtc
557 select jtc.section_code section_code,
558 jpllt.tax_id tax_id
559 from jai_po_taxes jpllt,
561 where jpllt.tax_id = jtc.tax_id
562 and jpllt.po_header_id = p_po_header_id
563 and jpllt.po_line_id = p_po_line_id
564 and jpllt.line_location_id = p_line_location_id
565 and jtc.tax_type = jai_constants.tax_type_tds
566 and jtc.section_type = p_section_type--rchandan for bug#4428980
567 order by jpllt.tax_line_no asc;
568
569
570 c_rec_po_distributions_all c_po_distributions_all%rowtype;
571 lv_last_section_type JAI_CMN_TAXES_ALL.section_type%type;
572 c_rec_po_taxes c_po_taxes%rowtype;
573
574 begin
575
576 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.default_tds_from_po', 'START'); /* 1 */
577 open c_po_distributions_all(p_po_distribution_id);
578 fetch c_po_distributions_all into c_rec_po_distributions_all;
579 close c_po_distributions_all;
580
581 /* Check if TDS type of tax exists against if PO taxes */
582 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
583 open c_po_taxes
584 (
585 c_rec_po_distributions_all.po_header_id,
586 c_rec_po_distributions_all.po_line_id,
587 c_rec_po_distributions_all.line_location_id,
588 'TDS_SECTION' --rchandan for bug#4428980
589 );
590 fetch c_po_taxes into c_rec_po_taxes;
591 close c_po_taxes;
592
593 if c_rec_po_taxes.section_code is null then
594 /* No TDS tax exists against the receipt line */
595 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
596 goto exit_from_procedure;
597 end if;
598
599 /* Control comes here only when a TDS tax exists against the receipt */
600 p_tds_section_code := c_rec_po_taxes.section_code;
601 p_tds_tax_id := c_rec_po_taxes.tax_id;
602 p_default_from := 'PO';
603
604 << exit_from_procedure >>
605 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath, null, 'END'); /* 4 */
606 return;
607
608 exception
609 when others then
610 p_process_flag := 'E';
611 P_process_message := 'Error from default_from_po :' || sqlerrm;
612 return;
613
614 end default_tds_from_po;
615 /* ************************************** default_from_po ************************************ */
616
617
618 /* ************************************* default_from_setup *********************************** */
619
620 procedure default_tds_from_setup
621 (
622 p_vendor_id in number,
623 p_vendor_site_id in number,
624 p_default_type out nocopy varchar2,
625 p_tds_section_code out nocopy varchar2,
626 p_tds_tax_id out nocopy number,
627 p_default_from out nocopy varchar2,
628 p_process_flag out nocopy varchar2,
629 P_process_message out nocopy varchar2,
630 p_codepath in out nocopy varchar2
631 )
632 is
633
634 cursor c_ja_in_vendor_tds_info_hdr (p_vendor_id number, p_vendor_site_id number)
635 is
636 select section_code,
637 tax_id
638 from JAI_AP_TDS_VENDOR_HDRS
639 where vendor_id = p_vendor_id
640 and vendor_site_id = p_vendor_site_id;
641
642 crec_ja_in_vendor_tds_info_hdr c_ja_in_vendor_tds_info_hdr%rowtype;
643
644 begin
645
646 /* Check from setup for vendor and site */
647 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.default_tds_from_setup', 'START'); /* 1 */
648 p_default_from := 'Vendor Site Setup';
649 open c_ja_in_vendor_tds_info_hdr(p_vendor_id, p_vendor_site_id);
650 fetch c_ja_in_vendor_tds_info_hdr into crec_ja_in_vendor_tds_info_hdr;
651 close c_ja_in_vendor_tds_info_hdr;
652
653 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
654 if crec_ja_in_vendor_tds_info_hdr.tax_id is null and
655 crec_ja_in_vendor_tds_info_hdr.section_code is null
656 then
657 /* No setup exists for site, check for null site */
658 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
659 p_default_from := 'Vendor Null Site Setup';
660
661 crec_ja_in_vendor_tds_info_hdr := null;
662 open c_ja_in_vendor_tds_info_hdr(p_vendor_id, 0);
663 fetch c_ja_in_vendor_tds_info_hdr into crec_ja_in_vendor_tds_info_hdr;
664 close c_ja_in_vendor_tds_info_hdr;
665 end if;
666
667 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
668 if crec_ja_in_vendor_tds_info_hdr.tax_id is not null and
669 crec_ja_in_vendor_tds_info_hdr.section_code is not null
670 then
671
672 /* Tax has been define as the default */
673 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
674 p_tds_section_code := crec_ja_in_vendor_tds_info_hdr.section_code;
675 p_tds_tax_id := crec_ja_in_vendor_tds_info_hdr.tax_id;
676 p_default_type := 'TAX';
677
678 elsif crec_ja_in_vendor_tds_info_hdr.tax_id is null and
682 /* Section has been define as the default */
679 crec_ja_in_vendor_tds_info_hdr.section_code is not null
680 then
681
683 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
684 p_tds_section_code := crec_ja_in_vendor_tds_info_hdr.section_code;
685 p_default_type := 'SECTION';
686
687 else
688
689 /* No Default has been setup for the vendor */
690 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
691 goto exit_from_procedure;
692
693 end if;
694
695
696 << exit_from_procedure >>
697 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath, null, 'END'); /* 9 */
698 return;
699
700 exception
701 when others then
702 p_process_flag := 'E';
703 P_process_message := 'Error from default_from_setup :' || sqlerrm;
704 return;
705 end default_tds_from_setup;
706
707 /* ************************************* default_from_setup *********************************** */
708
709 /* ************************************* validate_default_value *********************************** */
710 procedure validate_default_tds
711 (
712 p_vendor_id in number,
713 p_vendor_site_id in number,
714 p_tds_section_code in varchar2,
715 p_tds_tax_id in number,
716 p_process_flag out nocopy varchar2,
717 P_process_message out nocopy varchar2,
718 p_codepath in out nocopy varchar2
719 )
720 is
721
722 cursor c_ja_in_vendor_tds_info_hdr(p_vendor_id number, p_vendor_site_id number) is
723 select nvl(confirm_pan_flag, 'N') confirm_pan_flag
724 from JAI_AP_TDS_VENDOR_HDRS
725 where vendor_id = p_vendor_id
726 and vendor_site_id = p_vendor_site_id;
727
728 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
729 select 'Y'
730 from JAI_AP_TDS_TH_VSITE_V
731 where vendor_id = p_vendor_id
732 and vendor_site_id = p_vendor_site_id
733 and section_type = p_section_type--rchandan for bug#4428980
734 and section_code = p_tds_section_code;
735
736 lv_confirm_pan_flag JAI_AP_TDS_VENDOR_HDRS.confirm_pan_flag%type;
737 lv_check_section_applicable varchar2(1);
738
739 begin
740
741 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.validate_default_tds', 'START'); /* 1 */
742 open c_ja_in_vendor_tds_info_hdr(p_vendor_id, p_vendor_site_id);
743 fetch c_ja_in_vendor_tds_info_hdr into lv_confirm_pan_flag;
744 close c_ja_in_vendor_tds_info_hdr;
745
746 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
747 if lv_confirm_pan_flag = 'N' then
748 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
749 p_process_flag := 'V';
750 P_process_message := 'PAN of the vendor site not confirmed';
751 goto exit_from_procedure;
752 end if;
753
754 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
755
756
757 /* Check if section is applicable because of regular setup */
758 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
759 open c_check_section_applicable(p_vendor_id, p_vendor_site_id, p_tds_section_code,'TDS_SECTION' );
760 fetch c_check_section_applicable into lv_check_section_applicable;
761 close c_check_section_applicable;
762
763 if nvl(lv_check_section_applicable, 'N') <> 'Y' then
764 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
765 p_process_flag := 'V';
766 P_process_message := 'Section is not applicable to the vendor and / or site';
767 end if;
768
769
770 << exit_from_procedure >>
771 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath, null, 'END'); /* 8 */
772 return;
773
774 exception
775 when others then
776 p_process_flag := 'E';
777 P_process_message := 'Error from validate_default_value :' || sqlerrm;
778 return;
779
780 end validate_default_tds;
781
782 /* ************************************* validate_default_value *********************************** */
783
784 /* *********************************** populate_localization_inv_tax ****************************** */
785 procedure populate_localization_inv_tax
786 (
787 p_invoice_id in number,
788 p_invoice_line_number in number default null, /* AP lines uptake */
789 p_invoice_distribution_id in number default null, /* AP lines uptake */
790 P_distribution_line_number in number default null, /* AP lines uptake */
791 p_amount in number,
792 p_exchange_rate in number,
793 p_section_type in varchar2,
794 p_default_type in varchar2,
795 p_default_section_code in varchar2,
799 p_vendor_id in number,
796 p_default_tax_id in number,
797 p_input_dff_value in varchar2,
798 p_default_from in varchar2,
800 p_vendor_site_id in number,
801 p_org_id in number,
802 p_accounting_date in date,
803 p_final_tds_tax_id out nocopy number,
804 p_process_flag out nocopy varchar2,
805 P_process_message out nocopy varchar2,
806 p_codepath in out nocopy varchar2
807 )
808 is
809
810 cursor c_check_if_record_exists
811 (p_invoice_id number, p_invoice_line_number number, p_invoice_distribution_id number) is
812 select tds_inv_tax_id
813 from jai_ap_tds_inv_taxes
814 where invoice_id = p_invoice_id
815 and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, -9999)
816 and nvl(invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
817 and section_type = p_section_type;
818 /*Added below cursor for bug#7309921 by JMEENA */
819 cursor c_check_user_deleted_tax_flag(p_tds_inv_tax_id NUMBER) IS
820 select user_deleted_tax_flag
821 from jai_ap_tds_inv_taxes
822 where tds_inv_tax_id = p_tds_inv_tax_id;
823
824 ln_tds_inv_tax_id jai_ap_tds_inv_taxes.tds_inv_tax_id%type;
825 ln_check_if_tax_is_input number; --File.Sql.35 Cbabu :=0;
826 lv_actual_section_code jai_ap_tds_inv_taxes.actual_section_code%type;
827 ln_actual_tax_id jai_ap_tds_inv_taxes.actual_tax_id%type;
828 ln_default_tax_id jai_ap_tds_inv_taxes.default_tax_id%type;
829 lv_consider_for_redefault jai_ap_tds_inv_taxes.consider_for_redefault%type; --File.Sql.35 Cbabu := 'N';
830
831 ln_default_threshold_grp_id jai_ap_tds_inv_taxes.default_threshold_grp_id%type;
832 ln_default_cum_threshold_slab jai_ap_tds_inv_taxes.default_cum_threshold_slab_id%type;
833 lv_default_cum_threshold_stage jai_ap_tds_inv_taxes.default_cum_threshold_stage%type;
834 ln_default_sin_threshold_slab jai_ap_tds_inv_taxes.default_sin_threshold_slab_id%type;
835
836 lv_input_dff_value varchar2(50);
837 lv_user_deleted_tax_flag jai_ap_tds_inv_taxes.user_deleted_tax_flag%type; --File.Sql.35 Cbabu := 'N';
838 lv_process_status jai_ap_tds_inv_taxes.process_status%type;
839
840
841 begin
842
843 ln_check_if_tax_is_input :=0;
844 lv_consider_for_redefault := 'N';
845 lv_user_deleted_tax_flag := 'N';
846
847 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.populate_localization_inv_tax', 'START'); /* 2 */
848
849 ln_default_tax_id := p_default_tax_id;
850 ln_actual_tax_id := to_number(p_input_dff_value);
851
852 if p_section_type = 'TDS_SECTION' then
853
854 /* process the input tds dff value */
855 process_input_dff_tds
856 (
857 p_invoice_id => p_invoice_id ,
858 p_invoice_line_number => p_invoice_line_number ,
859 p_invoice_distribution_id => p_invoice_distribution_id ,
860 p_input_tds_dff_value => p_input_dff_value ,
861 p_output_tds_dff_value => lv_input_dff_value ,
862 p_process_flag => p_process_flag ,
863 P_process_message => P_process_message ,
864 p_codepath => p_codepath
865 );
866
867 if lv_input_dff_value = 'NO TDS' then
868 p_codepath := jai_general_pkg.plot_codepath(6.1, p_codepath); /* 6.1 */
869 lv_user_deleted_tax_flag := 'Y';
870 elsif lv_input_dff_value is not null then
871 ln_actual_tax_id := to_number(lv_input_dff_value);
872 /*Added below elsif condition for bug#7309921 by JMEENA */
873 elsif lv_input_dff_value is null then
874 open c_check_if_record_exists(p_invoice_id, p_invoice_line_number, p_invoice_distribution_id);
875 fetch c_check_if_record_exists into ln_tds_inv_tax_id;
876 close c_check_if_record_exists;
877
878 open c_check_user_deleted_tax_flag(ln_tds_inv_tax_id);
879 fetch c_check_user_deleted_tax_flag into lv_user_deleted_tax_flag;
880 close c_check_user_deleted_tax_flag;
881 end if;
882
883 /* If default value is SECTION check the default and the given tax */
884 --Added condition and lv_user_deleted_tax_flag <>'Y' for bug#7309921 by JMEENA
885 if p_default_type = 'SECTION' and p_default_section_code is not null and lv_user_deleted_tax_flag <>'Y' then
886
887 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
888
889 get_default_tax_from_section
890 (
891 p_invoice_id => p_invoice_id ,
892 p_invoice_line_number => p_invoice_line_number ,
893 p_invoice_distribution_id => p_invoice_distribution_id ,
894 p_vendor_id => p_vendor_id ,
898 p_tds_section_code => p_default_section_code ,
895 p_vendor_site_id => p_vendor_site_id ,
896 p_amount => p_amount ,
897 p_exchange_rate => p_exchange_rate ,
899 p_org_id => p_org_id ,
900 p_accounting_date => p_accounting_date ,
901 p_tds_tax_id => ln_default_tax_id ,
902 p_threshold_grp_id => ln_default_threshold_grp_id ,
903 p_cumulative_threshold_slab_id => ln_default_cum_threshold_slab ,
904 p_cumulative_threshold_stage => lv_default_cum_threshold_stage ,
905 p_single_threshold_slab_id => ln_default_sin_threshold_slab ,
906 p_process_flag => p_process_flag ,
907 P_process_message => P_process_message ,
908 p_codepath => p_codepath
909 );
910
911 end if;
912
913 /* Value for consider_for_redefault */
914 if ln_actual_tax_id is null and lv_user_deleted_tax_flag <> 'Y' and p_default_from not in ('PO', 'Receipt') then
915 /* User has not given any input, or also has not specifically deleted the defaulted value or
916 default is not because of PO or Receipt */
917 p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
918 if p_default_type = 'SECTION' then
919 lv_consider_for_redefault := 'Y';
920 p_codepath := jai_general_pkg.plot_codepath(15, p_codepath); /* 15 */
921 end if;
922 end if;
923 /* Value for consider_for_redefault */
924
925 end if; /* if p_section_type */
926
927 p_codepath := jai_general_pkg.plot_codepath(19, p_codepath); /* 19 */
928 open c_check_if_record_exists(p_invoice_id, p_invoice_line_number, p_invoice_distribution_id);
929 fetch c_check_if_record_exists into ln_tds_inv_tax_id;
930 close c_check_if_record_exists;
931
932 if ln_tds_inv_tax_id is null then
933
934 p_codepath := jai_general_pkg.plot_codepath(20, p_codepath); /* 20 */
935 lv_process_status := 'D';
936 insert into jai_ap_tds_inv_taxes
937 (
938 tds_inv_tax_id ,
939 invoice_id ,
940 invoice_line_number ,
941 invoice_distribution_id ,
942 distribution_line_number ,
943 amount ,
944 section_type ,
945 default_type ,
946 default_section_code ,
947 default_tax_id ,
948 actual_section_code ,
949 actual_tax_id ,
950 user_deleted_tax_flag ,
951 default_threshold_grp_id ,
952 default_cum_threshold_slab_id ,
953 default_cum_threshold_stage ,
954 default_sin_threshold_slab_id ,
955 default_from ,
956 consider_for_redefault ,
957 process_status ,
958 codepath ,
959 created_by ,
960 creation_date ,
961 last_updated_by ,
962 last_update_date ,
963 last_update_login
964 )
965 values
966 (
967 jai_ap_tds_inv_taxes_s.nextval ,
968 p_invoice_id ,
969 p_invoice_line_number ,
970 p_invoice_distribution_id ,
971 P_distribution_line_number ,
972 p_amount ,
973 p_section_type ,
974 p_default_type ,
975 p_default_section_code ,
976 ln_default_tax_id ,
977 lv_actual_section_code ,
978 ln_actual_tax_id ,
979 lv_user_deleted_tax_flag ,
980 ln_default_threshold_grp_id ,
981 ln_default_cum_threshold_slab ,
982 lv_default_cum_threshold_stage ,
983 ln_default_sin_threshold_slab ,
984 p_default_from ,
985 lv_consider_for_redefault ,
986 lv_process_status ,
987 p_codepath ,
988 fnd_global.user_id ,
989 sysdate ,
990 fnd_global.user_id ,
991 sysdate ,
992 fnd_global.login_id
993 );
994
995 else
996
997 p_codepath := jai_general_pkg.plot_codepath(21, p_codepath); /* 21 */
998
999 update jai_ap_tds_inv_taxes
1000 set amount = p_amount ,
1001 section_type = p_section_type ,
1002 default_type = p_default_type ,
1003 default_section_code = p_default_section_code ,
1007 user_deleted_tax_flag = lv_user_deleted_tax_flag ,
1004 default_tax_id = ln_default_tax_id ,
1005 actual_section_code = lv_actual_section_code ,
1006 actual_tax_id = ln_actual_tax_id ,
1008 default_threshold_grp_id = ln_default_threshold_grp_id ,
1009 default_cum_threshold_slab_id = ln_default_cum_threshold_slab ,
1010 default_cum_threshold_stage = lv_default_cum_threshold_stage ,
1011 default_sin_threshold_slab_id = ln_default_sin_threshold_slab ,
1012 default_from = p_default_from ,
1013 consider_for_redefault = lv_consider_for_redefault ,
1014 process_status = lv_process_status ,
1015 codepath = p_codepath ,
1016 last_updated_by = fnd_global.user_id ,
1017 last_update_date = sysdate
1018 where tds_inv_tax_id = ln_tds_inv_tax_id;
1019
1020 end if;
1021
1022
1023 if lv_consider_for_redefault = 'Y' and p_section_type = 'TDS_SECTION' then
1024
1025 p_codepath := jai_general_pkg.plot_codepath(22, p_codepath); /* 22 */
1026
1027 update jai_ap_tds_inv_taxes
1028 set default_tax_id = ln_default_tax_id
1029 where tds_inv_tax_id <> ln_tds_inv_tax_id
1030 and invoice_id = p_invoice_id
1031 and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, -9999)
1032 and consider_for_redefault = lv_consider_for_redefault
1033 and section_type = p_section_type;
1034
1035 end if; /* lv_consider_for_redefault = 'Y' */
1036
1037 if p_section_type = 'TDS_SECTION' then
1038 p_codepath := jai_general_pkg.plot_codepath(23, p_codepath); /* 23 */
1039 p_final_tds_tax_id := nvl(ln_actual_tax_id, ln_default_tax_id);
1040 end if;
1041
1042 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, NULL, 'END'); /*100 */
1043 return;
1044
1045 exception
1046 when others then
1047 p_process_flag := 'E';
1048 P_process_message := 'Error from populate_localization_inv_tax :' || sqlerrm;
1049 return;
1050
1051 end populate_localization_inv_tax;
1052
1053 /* ******************************** populate_localization_inv_tax ****************************** */
1054
1055 /* ******************************** get_default_tax_from_section ****************************** */
1056
1057 procedure get_default_tax_from_section
1058 (
1059 p_invoice_id in number,
1060 p_invoice_line_number in number default null, /* AP lines uptake */
1061 p_invoice_distribution_id in number default null, /* AP lines uptake */
1062 p_vendor_id in number,
1063 p_vendor_site_id in number,
1064 p_amount in number,
1065 p_exchange_rate in number,
1066 p_tds_section_code in varchar2,
1067 p_org_id in number,
1068 p_accounting_date in date,
1069 p_tds_tax_id out nocopy number,
1070 p_threshold_grp_id out nocopy number,
1071 p_cumulative_threshold_slab_id out nocopy number,
1072 p_cumulative_threshold_stage out nocopy varchar2,
1073 p_single_threshold_slab_id out nocopy number,
1074 p_process_flag out nocopy varchar2,
1075 P_process_message out nocopy varchar2,
1076 p_codepath in out nocopy varchar2
1077 )
1078 is
1079
1080 cursor c_get_amount_for_redefault /* AP lines uptake - introduced line */
1081 (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
1082 select sum(amount)
1083 from jai_ap_tds_inv_taxes
1084 where invoice_id = p_invoice_id
1085 and consider_for_redefault = p_consider_for_redefault--rchandan for bug#4428980
1086 and user_deleted_tax_flag <> 'Y'
1087 and ( (p_invoice_distribution_id is null ) or (p_invoice_distribution_id is not null and invoice_distribution_id <> p_invoice_distribution_id ) )
1088 /*and ( (p_invoice_line_number is null ) or ( p_invoice_line_number is not null and invoice_line_number <> p_invoice_line_number) )
1089 This is not required as we need to consider all distributions for redefaulting*/
1090 ;
1091
1092 cursor c_get_threshold
1093 (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
1094 select threshold_hdr_id
1095 from jai_ap_tds_th_vsite_v
1096 where vendor_id = p_vendor_id
1097 and vendor_site_id = p_vendor_site_id
1101 cursor c_jai_ap_tds_thhold_grps(p_threshold_grp_id number) is
1098 and section_type = p_section_type--rchandan for bug#4428980
1099 and section_code = p_tds_section_code;
1100
1102 select (
1103 nvl(total_invoice_amount, 0) -
1104 nvl(total_invoice_cancel_amount, 0) -
1105 nvl(total_invoice_apply_amount, 0) +
1106 nvl(total_invoice_unapply_amount, 0)
1107 )
1108 total_invoice_amount
1109 from jai_ap_tds_thhold_grps
1110 where threshold_grp_id = p_threshold_grp_id;
1111
1112 cursor c_jai_ap_tds_thhold_slabs
1113 ( p_threshold_hdr_id number, p_threshold_type varchar2, p_amount number) is
1114 select threshold_slab_id, threshold_type_id, from_amount, to_amount
1115 from jai_ap_tds_thhold_slabs
1116 where threshold_hdr_id = p_threshold_hdr_id
1117 and threshold_type_id in
1118 ( select threshold_type_id
1119 from jai_ap_tds_thhold_types
1120 where threshold_hdr_id = p_threshold_hdr_id
1121 and threshold_type = p_threshold_type
1122 /* Bug 4522540. Added by Lakshmi Gopalsami
1123 Added the date condition */
1124 and trunc(p_accounting_Date) between from_date
1125 and nvl(to_date, p_accounting_date + 1)
1126 )
1127 and nvl(to_amount, p_amount) >= p_amount
1128 order by from_amount asc;
1129
1130 cursor c_jai_ap_tds_thhold_taxes(p_threshold_slab_id number, p_org_id number) is
1131 select tax_id
1132 from jai_ap_tds_thhold_taxes
1133 where threshold_slab_id = p_threshold_slab_id
1134 and operating_unit_id = p_org_id;
1135
1136 lv_attr_code VARCHAR2(25);
1137 lv_attr_type_code VARCHAR2(25);
1138 lv_tds_regime VARCHAR2(25);
1139 lv_regn_type_others VARCHAR2(25);
1140
1141 cursor c_get_fin_year(p_gl_date date, p_org_id number) is
1142 select fin_year
1143 from jai_ap_tds_years
1144 where tan_no in /* where clause and subquery added by ssumaith - bug# 4448789*/
1145 (
1146 SELECT attribute_value
1147 FROM JAI_RGM_ORG_REGNS_V
1148 WHERE regime_code = lv_tds_regime
1149 AND registration_type = lv_regn_type_others
1150 AND attribute_type_code = lv_attr_type_Code
1151 AND attribute_code = lv_attr_code
1152 AND organization_id = p_org_id
1153 )
1154 and p_gl_date between start_date and end_date;
1155
1156
1157 cursor c_get_vendor_pan_tan(p_vendor_id number , p_vendor_site_id number) is
1158 select c.pan_no pan_no,
1159 d.org_tan_num tan_no
1160 from po_vendors a,
1161 po_vendor_sites_all b,
1162 jai_ap_tds_vendor_hdrs c,
1163 jai_ap_tds_org_tan_v d --- JAI_AP_TDS_ORG_TANS is changed to view jai_ap_tds_org_tan_v 4323338
1164 where a.vendor_id = b.vendor_id
1165 and b.vendor_id = c.vendor_id
1166 and b.vendor_site_id = c.vendor_site_id
1167 and b.org_id = d.organization_id
1168 and a.vendor_id = p_vendor_id
1169 and b.vendor_site_id = p_vendor_site_id;
1170
1171 cursor c_get_threshold_group
1172 (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
1173 select threshold_grp_id
1174 from jai_ap_tds_thhold_grps
1175 where vendor_id = p_vendor_id
1176 and section_type = p_section_type --rchandan for bug#4428980
1177 and section_code = p_tds_section_code
1178 and org_tan_num = p_tan_no
1179 and vendor_pan_num = p_pan_no
1180 and fin_year = p_fin_year;
1181
1182 r_get_threshold c_get_threshold%rowtype;
1183 r_jai_ap_tds_thhold_slabs c_jai_ap_tds_thhold_slabs%rowtype;
1184 r_jai_ap_tds_thhold_taxes c_jai_ap_tds_thhold_taxes%rowtype;
1185
1186 lv_pan_no jai_ap_tds_vendor_hdrs.pan_no%type;
1187 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
1188
1189
1190 ln_total_invoice_amount number;
1191 ln_amount_for_redefault number;
1192 ln_fin_year number;
1193 ln_threshold_grp_id number;
1194 ln_amount number;
1195
1196
1197 begin
1198
1199 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.get_default_tax_from_section', 'START'); /* 1 */
1200
1201 ln_amount := p_amount * nvl(p_exchange_rate, 1);
1202
1203
1204 lv_attr_code := 'TAN NO';
1205 lv_attr_type_code := 'PRIMARY';
1206 lv_tds_regime := 'TDS';
1207 lv_regn_type_others := 'OTHERS';
1208
1209
1210
1211
1212 /* Get the fin year */
1213 open c_get_fin_year(p_accounting_date, p_org_id);
1214 fetch c_get_fin_year into ln_fin_year;
1215 close c_get_fin_year;
1216
1217 /* Get Pan number and Tan number for the vendor */
1218 open c_get_vendor_pan_tan(p_vendor_id, p_vendor_site_id);
1219 fetch c_get_vendor_pan_tan into lv_pan_no, lv_tan_no;
1220 close c_get_vendor_pan_tan;
1221
1222
1226
1223 open c_get_amount_for_redefault(p_invoice_id, p_invoice_line_number, p_invoice_distribution_id,'Y');--rchandan for bug#4428980
1224 fetch c_get_amount_for_redefault into ln_amount_for_redefault;
1225 close c_get_amount_for_redefault;
1227 ln_amount_for_redefault := nvl(ln_amount_for_redefault, 0);
1228 ln_amount_for_redefault := ln_amount_for_redefault * nvl(p_exchange_rate, 1);
1229
1230 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
1231 /* Get threshold id */
1232 open c_get_threshold(p_vendor_id, p_vendor_site_id, p_tds_section_code,'TDS_SECTION');--rchandan for bug#4428980
1233 fetch c_get_threshold into r_get_threshold;
1234 close c_get_threshold;
1235
1236
1237 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1238 if r_get_threshold.threshold_hdr_id is null then
1239 /* No threshold has been setup for the section and vendor,
1240 it is not possible to default a tax from section */
1241 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1242 p_tds_tax_id := null;
1243 goto exit_from_procedure;
1244 end if;
1245
1246 /* Get threshold group id */
1247 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1248 open c_get_threshold_group(p_vendor_id, lv_tan_no, lv_pan_no, p_tds_section_code, ln_fin_year,'TDS_SECTION');
1249 fetch c_get_threshold_group into ln_threshold_grp_id;
1250 close c_get_threshold_group;
1251
1252 /* if there is no threshold group details,
1253 it means no transaction has happened for that section and vendor combination */
1254 if ln_threshold_grp_id is not null then
1255 p_codepath := jai_general_pkg.plot_codepath(6, p_codepath); /* 6 */
1256 open c_jai_ap_tds_thhold_grps(ln_threshold_grp_id);
1257 fetch c_jai_ap_tds_thhold_grps into ln_total_invoice_amount;
1258 close c_jai_ap_tds_thhold_grps;
1259 p_threshold_grp_id := ln_threshold_grp_id;
1260 end if;
1261
1262 ln_total_invoice_amount := nvl(ln_total_invoice_amount, 0 ) ;
1263
1264 p_codepath := jai_general_pkg.plot_codepath(7, p_codepath); /* 7 */
1265 /* Check if Cumulative threshold is reached */
1266 open c_jai_ap_tds_thhold_slabs
1267 (r_get_threshold.threshold_hdr_id, 'CUMULATIVE', ln_total_invoice_amount + ln_amount + ln_amount_for_redefault);
1268 fetch c_jai_ap_tds_thhold_slabs into r_jai_ap_tds_thhold_slabs;
1269 close c_jai_ap_tds_thhold_slabs;
1270
1271 p_codepath := jai_general_pkg.plot_codepath(8, p_codepath); /* 8 */
1272
1273 p_cumulative_threshold_slab_id := r_jai_ap_tds_thhold_slabs.threshold_slab_id;
1274
1275 if ln_total_invoice_amount >= r_jai_ap_tds_thhold_slabs.from_amount then
1276 /* Cumulative threshold amount is already reached */
1277 p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
1278 p_cumulative_threshold_stage := 'AFTER THRESHOLD';
1279
1280 elsif (ln_total_invoice_amount + ln_amount + ln_amount_for_redefault) >= r_jai_ap_tds_thhold_slabs.from_amount then
1281
1282 /* Threshold reached with this transaction */
1283 p_codepath := jai_general_pkg.plot_codepath(10, p_codepath); /* 10 */
1284 p_cumulative_threshold_stage := 'AT THRESHOLD';
1285
1286 else
1287
1288 p_cumulative_threshold_stage := 'BEFORE THRESHOLD';
1289
1290 /* Cumulative threshold is not reached, default the tax id anyway but
1291 check for SINGLE invoice threshold. This has to be checked with only invoice amount */
1292
1293 r_jai_ap_tds_thhold_slabs:= null;
1294 p_codepath := jai_general_pkg.plot_codepath(11, p_codepath); /* 11 */
1295 open c_jai_ap_tds_thhold_slabs(r_get_threshold.threshold_hdr_id, 'SINGLE', ln_amount + ln_amount_for_redefault);
1296 fetch c_jai_ap_tds_thhold_slabs into r_jai_ap_tds_thhold_slabs;
1297 close c_jai_ap_tds_thhold_slabs;
1298
1299 if ln_amount + ln_amount_for_redefault >= r_jai_ap_tds_thhold_slabs.from_amount then
1300 /* Cumulative threshold amount is reached */
1301 p_codepath := jai_general_pkg.plot_codepath(12, p_codepath); /* 12 */
1302 p_single_threshold_slab_id := r_jai_ap_tds_thhold_slabs.threshold_slab_id;
1303 end if;
1304
1305 end if; /* Cumulative or single threshold amount */
1306
1307 /* Get the tax id attached to the slab */
1308 open c_jai_ap_tds_thhold_taxes(nvl(p_single_threshold_slab_id, p_cumulative_threshold_slab_id), p_org_id);
1309 fetch c_jai_ap_tds_thhold_taxes into r_jai_ap_tds_thhold_taxes;
1310 close c_jai_ap_tds_thhold_taxes;
1311
1312 p_tds_tax_id := r_jai_ap_tds_thhold_taxes.tax_id;
1313
1314 << exit_from_procedure >>
1315 p_codepath := jai_general_pkg.plot_codepath(13, p_codepath, null, 'END'); /* 13 */
1316 return;
1317
1318 exception
1319 when others then
1320 p_process_flag := 'E';
1321 P_process_message := 'Error from get_default_tax_from_section :' || sqlerrm;
1322 return;
1323
1324 end get_default_tax_from_section;
1325
1326
1327 /* ******************************** get_default_tax_from_section ****************************** */
1328
1329
1330 /* ********************************************* process_input_dff_tds ********************************************* */
1331 procedure process_input_dff_tds
1332 (
1333 p_invoice_id in number,
1334 p_invoice_line_number in number default null, /* AP lines uptake */
1338 p_process_flag out nocopy varchar2,
1335 p_invoice_distribution_id in number default null, /* AP lines uptake */
1336 p_input_tds_dff_value in varchar2,
1337 p_output_tds_dff_value out nocopy varchar2,
1339 P_process_message out nocopy varchar2,
1340 p_codepath in out nocopy varchar2
1341 )
1342 is
1343
1344 cursor c_get_existing_dff_values
1345 (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
1346 select tds_inv_tax_id, default_tax_id, actual_tax_id
1347 from jai_ap_tds_inv_taxes
1348 where invoice_id = p_invoice_id
1349 and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, -9999)
1350 and nvl(invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
1351 and section_type = p_section_type;--rchandan for bug#4428980
1352
1353
1354 r_get_existing_dff_values c_get_existing_dff_values%rowtype;
1355
1356
1357 begin
1358
1359 p_codepath :=
1360 jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_tax_defaultation.process_input_dff_tds', 'START'); /* 1 */
1361
1362 p_output_tds_dff_value := p_input_tds_dff_value;
1363
1364 open c_get_existing_dff_values(p_invoice_id, p_invoice_line_number, p_invoice_distribution_id,'TDS_SECTION');--rchandan for bug#4428980
1365 fetch c_get_existing_dff_values into r_get_existing_dff_values;
1366 close c_get_existing_dff_values;
1367
1368 if r_get_existing_dff_values.tds_inv_tax_id is null then
1369 /* TDS defaultation Record does not exist */
1370 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
1371 goto exit_from_procedure;
1372 end if;
1373
1374 /* Control comes here only when defaultation details already exists */
1375
1376 if p_input_tds_dff_value is null then
1377
1378 /* user has not provided any input or has deleted the defaulted or earlier given value */
1379 p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
1380
1381 if r_get_existing_dff_values.default_tax_id is not null or
1382 r_get_existing_dff_values.actual_tax_id is not null then
1383
1384 p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
1385 /* User has deleted the earlier given or defaulted value no TDS should be deducted. */
1386 p_output_tds_dff_value := 'NO TDS';
1387
1388 end if;
1389
1390 elsif p_input_tds_dff_value = r_get_existing_dff_values.default_tax_id then
1391 /* User has given the same value as default. Actual can be set to null */
1392 p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
1393 p_output_tds_dff_value := null;
1394
1395 end if; /* p_input_tds_dff_value */
1396
1397 << exit_from_procedure >>
1398 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
1399 return;
1400
1401 exception
1402 when others then
1403 p_process_flag := 'E';
1404 P_process_message := 'Error from jai_ap_tds_tax_defaultation.process_input_dff_tds :' || sqlerrm;
1405 return;
1406 end process_input_dff_tds;
1407
1408 /* ********************************************* process_input_dff_tds ********************************************* */
1409
1410 /* ********************************************* process_delete ********************************************* */
1411
1412 procedure process_delete
1413 (
1414 p_invoice_id in number,
1415 p_invoice_line_number in number default null, /* AP lines uptake */
1416 p_invoice_distribution_id in number default null,
1417 p_process_flag out nocopy varchar2,
1418 P_process_message out nocopy varchar2
1419 )
1420 is
1421 /* Change History
1422 -------------------------------------------------------------------------------
1423 S.No Date Author and Details
1424 -------------------------------------------------------------------------------
1425 1. 16/05/2008 JMEENA for bug#6995295.
1426 Added NVL for process_status
1427 */
1428 begin
1429
1430 /* AP lines uptake - introduced line */
1431 delete jai_ap_tds_inv_taxes
1432 where invoice_id = p_invoice_id
1433 and (
1434 (p_invoice_line_number is null ) or
1435 (p_invoice_line_number is not null and invoice_line_number = p_invoice_line_number)
1436 )
1437 and (
1438 (p_invoice_distribution_id is null ) or
1439 (p_invoice_distribution_id is not null and invoice_distribution_id = p_invoice_distribution_id)
1440 )
1441 and NVL(process_status,'D') <> 'P'; -- Added NVL by JMEENA for bug#6995295
1442
1443 << exit_from_procedure >>
1444 return;
1445
1446 exception
1447 when others then
1448 p_process_flag := 'E';
1449 P_process_message := 'Error from jai_ap_tds_tax_defaultation.process_delete :' || sqlerrm;
1450 return;
1451 end process_delete;
1452
1453 /* ********************************************* process_delete ********************************************* */
1454
1455
1456 /* ********************************************* check_old_transaction ********************************************* */
1457 procedure check_old_transaction
1458 (
1459 p_invoice_id in number default null,
1460 p_invoice_distribution_id in number default null,
1461 p_new_transaction out nocopy varchar2
1462 )
1463 is
1464
1465 cursor c_jai_ap_tds_inv_taxes_inv(p_invoice_id number) is
1466 select 'Y'
1467 from jai_ap_tds_inv_taxes
1468 where invoice_id = p_invoice_id;
1469
1470
1471 cursor c_jai_ap_tds_inv_taxes_dist(p_invoice_distribution_id number) is
1472 select 'Y'
1473 from jai_ap_tds_inv_taxes
1474 where invoice_distribution_id = p_invoice_distribution_id;
1475
1476 lv_new_transaction varchar2(1);
1477
1478 begin
1479
1480 lv_new_transaction := 'N';
1481
1482 if p_invoice_id is not null then
1483
1484 open c_jai_ap_tds_inv_taxes_inv(p_invoice_id);
1485 fetch c_jai_ap_tds_inv_taxes_inv into lv_new_transaction;
1486 close c_jai_ap_tds_inv_taxes_inv;
1487
1488 elsif p_invoice_distribution_id is not null then
1489
1490 open c_jai_ap_tds_inv_taxes_dist(p_invoice_distribution_id);
1491 fetch c_jai_ap_tds_inv_taxes_dist into lv_new_transaction;
1492 close c_jai_ap_tds_inv_taxes_dist;
1493
1494 end if;
1495
1496 p_new_transaction := nvl(lv_new_transaction, 'N');
1497
1498 << exit_from_procedure >>
1499 return;
1500
1501 exception
1502 when others then
1503 return;
1504 end check_old_transaction;
1505
1506
1507
1508 /* ********************************************* check_old_transaction ********************************************* */
1509
1510 END jai_ap_tds_tax_defaultation;