[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_DTC_PREPAYMENTS_PKG
Source
1 PACKAGE BODY jai_ap_dtc_prepayments_pkg AS
2 /* $Header: jai_ap_dtc_ppay.plb 120.22.12020000.4 2013/04/10 07:24:14 cholei noship $ */
3
4 /* ----------------------------------------------------------------------------
5 FILENAME : jai_ap_dtc_prepayemnts_pkg.sql
6
7 Created By : 25/Dec/2011 Wenqiong Zhou Created
8
9 Created Date : 07-Feb-2012
10
11 Bug :
12
13 Purpose : Implementation of prepayment functionality for TDS.
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. 07/02/2012 Zhhou created for bug#13359892. copy from jai_ap_tds_ppay.pls/plb
23
24 Created this package for implementing the TDS prepayemnts
25 functionality onto AP invoice.
26
27 2. 14-MAR-2012 Modified by Zhiwei.xin for bug #13837788
28 Fixed :
29 Get tax category id separately for Prepayment and Standard Invoice.
30
31 --------------------------------------------------------------------------- */
32
33 -- Added by Jia for FP bug6929483, Begin
34 -----------------------------------------------------------------------------
35
36 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JAI_AP_DTC_PREPAYMENTS_PKG';
37 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
38 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
39 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
40 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
41 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
42 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
43
44 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
45 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
46 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
47 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
48 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
49 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
50 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
51 G_MODULE_NAME CONSTANT VARCHAR2(40) := 'JAI.PLSQL.JAI_AP_DTC_PREPAYMENTS_PKG.';
52
53 PROCEDURE get_prepay_invoice_id
54 (
55 p_prepay_inv_dist_id NUMBER,
56 p_prepay_inv_id OUT NOCOPY NUMBER
57 )
58 IS
59 PRAGMA AUTONOMOUS_TRANSACTION;
60 BEGIN
61 BEGIN
62 SELECT invoice_id
63 INTO p_prepay_inv_id
64 FROM ap_invoice_distributions_all
65 WHERE invoice_distribution_id = p_prepay_inv_dist_id;
66 EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 p_prepay_inv_id := null;
69 END;
70 END get_prepay_invoice_id;
71
72 /*Bug 8431516 - Start*/
73 FUNCTION get_reversal_flag(pn_invoice_dist_id NUMBER) RETURN VARCHAR2
74 IS
75 PRAGMA AUTONOMOUS_TRANSACTION;
76 CURSOR c_get_reversal_flag(p_inv_dist_id NUMBER) is
77 SELECT reversal_flag
78 FROM ap_invoice_distributions_all
79 WHERE invoice_distribution_id = p_inv_dist_id;
80 v_reversal_flag VARCHAR2(1);
81 BEGIN
82 OPEN c_get_reversal_flag(pn_invoice_dist_id);
83 FETCH c_get_reversal_flag INTO v_reversal_flag;
84 CLOSE c_get_reversal_flag;
85 v_reversal_flag := NVL(v_reversal_flag,'N');
86 RETURN v_reversal_flag;
87 END get_reversal_flag;
88 /*Bug 8431516 - End*/
89
90 -----------------------------------------------------------------------------
91 -- Added by Jia for FP bug6929483, End
92
93
94 procedure process_prepayment
95 (
96 p_event in varchar2, --Added for Bug 8431516
97 p_invoice_id in number,
98 p_invoice_distribution_id in number,
99 p_prepay_distribution_id in number,
100 p_parent_reversal_id in number,
101 p_prepay_amount in number,
102 p_vendor_id in number,
103 p_vendor_site_id in number,
104 p_accounting_date in date,
105 p_invoice_currency_code in varchar2,
106 p_exchange_rate in number,
107 p_set_of_books_id in number,
108 p_org_id in number,
109 -- Bug 5722028. Added by CSahoo
110 p_creation_date in date,
111 p_process_flag out nocopy varchar2,
112 p_process_message out nocopy varchar2,
113 p_codepath in out nocopy varchar2
114 )
115 is
116 /*Bug 5751783 - Start*/
117 cursor c_get_prepay_apply(cp_invoice_id number, cp_inv_dist_id number) is
118 select tds_threshold_trx_id_apply, count(1)
119 from jai_ap_tds_prepayments
120 where invoice_id = cp_invoice_id
121 and invoice_distribution_id_prepay = cp_inv_dist_id
122 group by tds_threshold_trx_id_apply;
123
124
125 /*Start Additions by mmurtuza for bug 13620923*/
126 cursor c_chk_tds_deducted(cp_invoice_id number) is
127 select count(1) from jai_ap_tds_thhold_trxs
128 where invoice_id = cp_invoice_id
129 and tds_event = 'INVOICE VALIDATE';
130
131 ln_cnt_thrshold number := 0;
132
133 /*End Additions by mmurtuza for bug 13620923*/
134
135 ln_prepay_apply number;
136 ln_prepay_apply_trx_id number;
137 /*Bug 5751783 - End*/
138 begin
139
140 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.process_prepayment', 'START'); /* 1 */
141 if p_prepay_amount < 0 then
142
143 /* Event is APPLY of prepayment */
144 /* Comment out the jai_ap_tds_prepayments_pkg.allocate_prepayment with new procedure
145 jai_tds_prepayments_pkg.allocate_prepay_section. By Wenqiong for bug13359892 on Dec 26 2011.
146 jai_ap_tds_prepayments_pkg.allocate_prepayment
147 (
148 p_invoice_id => p_invoice_id ,
149 p_invoice_distribution_id => p_invoice_distribution_id ,
150 p_prepay_amount => p_prepay_amount ,
151 p_process_flag => p_process_flag ,
152 p_process_message => p_process_message ,
153 p_codepath => p_codepath
154 );
155
156 if p_process_flag = 'E' then
157 goto exit_from_procedure;
158 end if;
159
160 jai_ap_tds_prepayments_pkg.populate_section_tax
161 (
162 p_invoice_id => p_invoice_id ,
163 p_invoice_distribution_id => p_invoice_distribution_id ,
164 p_prepay_distribution_id => p_prepay_distribution_id ,
165 p_process_flag => p_process_flag ,
166 p_process_message => p_process_message ,
167 p_codepath => p_codepath
168 );
169
170 */
171
172 jai_ap_dtc_prepayments_pkg.allocate_prepay_section
173 (
174 p_invoice_id => p_invoice_id ,
175 p_invoice_distribution_id => p_invoice_distribution_id ,
176 p_prepay_distribution_id => p_prepay_distribution_id ,
177 p_prepay_amount => p_prepay_amount ,
178 p_process_flag => p_process_flag ,
179 p_process_message => p_process_message ,
180 p_codepath => p_codepath
181 );
182
183 if p_process_flag = 'E' THEN
184 goto exit_from_procedure;
185 end if;
186
187
188 /* Comment out the jai_ap_tds_prepayments_pkg.process_tds_invoices with new procedure
189 jai_tds_prepayments_pkg.generate_rtn. By Wenqiong for bug13359892 on Dec 26 2011.
190
191 jai_ap_tds_prepayments_pkg.process_tds_invoices
192 (
193 p_event => p_event , --Added for Bug 8431516
194 p_invoice_id => p_invoice_id ,
195 p_invoice_distribution_id => p_invoice_distribution_id ,
196 p_prepay_distribution_id => p_prepay_distribution_id ,
197 p_prepay_amount => p_prepay_amount ,
198 p_vendor_id => p_vendor_id ,
199 p_vendor_site_id => p_vendor_site_id ,
200 p_accounting_date => p_accounting_date ,
201 p_invoice_currency_code => p_invoice_currency_code ,
202 p_exchange_rate => p_exchange_rate ,
203 p_set_of_books_id => p_set_of_books_id ,
204 p_org_id => p_org_id ,
205 -- Bug 5722028. Added by Lakshmi Gopalsami
206 p_creation_date => p_creation_date,
207 p_process_flag => p_process_flag ,
208 p_process_message => p_process_message ,
209 p_codepath => p_codepath
210 );
211 */
212 jai_ap_dtc_prepayments_pkg.generate_rtn
213 (
214 p_event => p_event ,
215 p_invoice_id => p_invoice_id ,
216 p_invoice_distribution_id => p_invoice_distribution_id ,
217 p_prepay_distribution_id => p_prepay_distribution_id ,
218 p_prepay_amount => p_prepay_amount ,
219 p_accounting_date => p_accounting_date ,
220 p_invoice_currency_code => p_invoice_currency_code ,
221 p_exchange_rate => p_exchange_rate ,
222 p_set_of_books_id => p_set_of_books_id ,
223 p_org_id => p_org_id ,
224 p_creation_date => p_creation_date,
225 p_process_flag => p_process_flag ,
226 p_process_message => p_process_message ,
227 p_codepath => p_codepath
228 );
229
230 if p_process_flag = 'E' then
231 goto exit_from_procedure;
232 end if;
233
234
235 elsif p_prepay_amount > 0 then
236
237 /* Event is UNAPPLY of prepayment */
238 /* Bug 5721614. Added by Lakshmi Gopalsami
239 * Included parameter p_prepay_distribution_id
240 */
241 -- Bug 12392890. Modified the cursor c_get_prepay_apply
242 -- Changed from p_invoice_distribution_id to p_parent_reversal_id
243 open c_get_prepay_apply(p_invoice_id, p_parent_reversal_id);
244 fetch c_get_prepay_apply into ln_prepay_apply_trx_id, ln_prepay_apply;
245 close c_get_prepay_apply;
246
247 /*Start Additions by mmurtuza for bug 13620923*/
248 open c_chk_tds_deducted(p_invoice_id);
249 fetch c_chk_tds_deducted into ln_cnt_thrshold;
250 close c_chk_tds_deducted;
251 /*End Additions by mmurtuza for bug 13620923*/
252
253 if p_event = 'INSERT' and nvl(ln_prepay_apply,0) > 0 and nvl(ln_prepay_apply_trx_id, 0) = 0 and ln_cnt_thrshold <> 0 then
254 /*added condition of ln_cnt_thrshold by mmurtuza for bug 13620923*/
255 p_process_flag := 'E';
256 P_process_message := 'Error - Cannot Unapply prepayment as it was Applied before Validating the Standard invoice';
257 goto exit_from_procedure;
258 end if;
259
260 jai_cmn_utils_pkg.WRITE_FND_LOG_MSG('JAI.PLSQL.JAI_AP_DTC_PREPAYMENTS_PKG.PROCESS_PREPAYMENT', '1.2 process_unapply ');
261
262 jai_ap_dtc_prepayments_pkg.process_unapply
263 (
264 p_event => p_event , --Added for Bug 8431516
265 p_invoice_id => p_invoice_id ,
266 p_invoice_distribution_id => p_invoice_distribution_id ,
267 p_parent_distribution_id => p_parent_reversal_id ,
268 p_prepay_distribution_id => p_prepay_distribution_id , /*Bug 5751783*/
269 p_prepay_amount => p_prepay_amount ,
270 p_vendor_id => p_vendor_id ,
271 p_vendor_site_id => p_vendor_site_id ,
272 p_accounting_date => p_accounting_date ,
273 p_invoice_currency_code => p_invoice_currency_code ,
274 p_exchange_rate => p_exchange_rate ,
275 p_set_of_books_id => p_set_of_books_id ,
276 p_org_id => p_org_id ,
277 -- Bug 5722028. Added by CSahoo
278 p_creation_date => p_creation_date,
279 p_process_flag => p_process_flag ,
280 p_process_message => p_process_message ,
281 p_codepath => p_codepath
282 );
283
284 --Added by Sanjikum for Bug#5131075(4722011)
285 IF p_process_flag = 'E' THEN
286 goto exit_from_procedure;
287 END IF;
288
289 end if;
290
291
292 << exit_from_procedure >>
293 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
294 return;
295
296 exception
297 when others then
298 p_process_flag := 'E';
299 P_process_message := 'jai_ap_tds_prepayemnts_pkg.process_prepayment :' || sqlerrm;
300 return;
301 end process_prepayment;
302
303
304
305 /***********************************************************************************************/
306 procedure process_unapply
307 (
308 p_event in varchar2, --Added for Bug 8431516
309 p_invoice_id in number,
310 p_invoice_distribution_id in number, /* PREPAY UNAPPLY distribution */
311 p_parent_distribution_id in number, /* parent PREPAY APPLY distribution */
312 p_prepay_distribution_id in number, /* Distribution id of the prepay line - Bug 5751783*/
313 p_prepay_amount in number,
314 p_vendor_id in number,
315 p_vendor_site_id in number,
316 p_accounting_date in date,
317 p_invoice_currency_code in varchar2,
318 p_exchange_rate in number,
319 p_set_of_books_id in number,
320 p_org_id in number,
321 -- Bug 5722028. Added by CSahoo
322 p_creation_date in date,
323 p_process_flag out nocopy varchar2,
324 p_process_message out nocopy varchar2,
325 p_codepath in out nocopy varchar2
326 )
327 is
328
329 /* Bug 5751783
330 * Fetched the non-rounded value of the tds paid in order to avoid
331 * any rounding issues.
332 */
333 /* bug 12965614. Added by Avanija
334 * We should not convert the application amount back to functional currency as prepayment application is in
335 * invoice currency and prepayment unapplication should also be in same currency
336 * Removed the p_exchange_rate for taxable basis
337 */
338 /**
339 * Change the logic here, convert the application amount back to functional currency for bug13833254.
340 */
341 cursor c_get_total_prepayment_tax
342 (p_invoice_id number, p_invoice_distribution_id number, p_exchange_rate number) is
343 select sum( decode(tds_applicable_flag , 'Y', application_amount, 0) )* nvl(p_exchange_rate,1) tds_taxable_basis,
344 sum( decode(tds_applicable_flag , 'Y', calc_tds_appln_amt, 0) ) tds_amount,
345 sum( decode(tds_applicable_flag , 'Y', tds_application_amount, 0) ) tds_amount_orig,
346 sum( decode(wct_applicable_flag, 'Y', application_amount, 0) )* nvl(p_exchange_rate,1) wct_taxable_basis,
347 sum( decode(wct_applicable_flag, 'Y', calc_wct_appln_amt, 0) ) wct_amount,
348 sum( decode(wct_applicable_flag , 'Y', wct_application_amount, 0) ) wct_amount_orig,
349 sum( decode(essi_applicable_flag, 'Y', application_amount, 0) )* nvl(p_exchange_rate,1) essi_taxable_basis,
350 sum( decode(essi_applicable_flag, 'Y', calc_essi_appln_amt, 0) ) essi_amount,
351 sum( decode(essi_applicable_flag, 'Y', essi_application_amount, 0) ) essi_amount_orig
352 from jai_ap_tds_prepayments
353 where invoice_id = p_invoice_id
354 and invoice_distribution_id_prepay = p_invoice_distribution_id;
355
356 /* bug 12965614. Added by Avanija
357 * We should not convert the application amount back to functional currency as prepayment application is in
358 * invoice currency and prepayment unapplication should also be in same currency
359 * Removed the p_exchange_rate for taxable basis
360 */
361 /**
362 * Change the logic here, convert the application amount back to functional currency for bug13833254.
363 */
364 cursor c_tds_details_apply(p_invoice_id number, p_invoice_distribution_id number, p_exchange_rate in number) is
365 SELECT get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
366 tds_section_code_prepay section_code,
367 tds_threshold_grp_id,
368 tds_threshold_trx_id_apply,
369 -- Bug 6363056
370 sum(decode(tds_applicable_flag , 'Y', application_amount, 0))* nvl(p_exchange_rate,1) tds_taxable_basis,
371 sum(decode(tds_applicable_flag , 'Y', calc_tds_appln_amt, 0)) tds_amount,
372 sum(decode(tds_applicable_flag , 'Y', tds_application_amount, 0)) tds_amount_orig,
373 sum(decode(wct_applicable_flag, 'Y', application_amount, 0))* nvl(p_exchange_rate,1) wct_taxable_basis,
374 sum(decode(wct_applicable_flag, 'Y', calc_wct_appln_amt, 0)) wct_amount,
375 sum(decode(wct_applicable_flag , 'Y', wct_application_amount, 0)) wct_amount_orig,
376 sum(decode(essi_applicable_flag, 'Y', application_amount, 0))* nvl(p_exchange_rate,1) essi_taxable_basis,
377 sum(decode(essi_applicable_flag, 'Y', calc_essi_appln_amt, 0)) essi_amount,
378 sum(decode(essi_applicable_flag , 'Y', essi_application_amount, 0)) essi_amount_orig
379 from jai_ap_tds_prepayments jatp
380 where invoice_id = p_invoice_id
381 and invoice_distribution_id_prepay = p_invoice_distribution_id
382 and tds_threshold_grp_id is not null
383 and nvl(unapply_flag, 'N') <> 'Y' -- Bug 6363056
384 group BY
385 get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)),
386 tds_section_code_prepay,
387 tds_threshold_grp_id,
388 tds_threshold_trx_id_apply; /*Bug 9132694 - Added Group By clause to sum the tax amounts and create a single RTN reversal entry on unapplication*/
389
390 cursor c_wct_details_apply(p_invoice_id number, p_invoice_distribution_id number) IS
391 select get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
392 tds_section_code_prepay section_code,
393 wct_threshold_trx_id_apply,
394 sum(decode(wct_applicable_flag, 'Y', application_amount, 0))* nvl(p_exchange_rate,1) wct_taxable_basis,
395 sum(decode(wct_applicable_flag, 'Y', calc_wct_appln_amt, 0)) wct_amount
396 from jai_ap_tds_prepayments jatp
397 where invoice_id = p_invoice_id
398 and invoice_distribution_id_prepay = p_invoice_distribution_id
399 and wct_threshold_trx_id_apply is not NULL
400 and wct_threshold_trx_id_unapply IS NULL
401 GROUP BY
402 get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)),
403 tds_section_code_prepay,
404 wct_threshold_trx_id_apply
405 ;
406 cursor c_essi_details_apply(p_invoice_id number, p_invoice_distribution_id number) is
407 select get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
408 tds_section_code_prepay section_code,
409 essi_threshold_trx_id_apply,
410 sum(decode(essi_applicable_flag, 'Y', application_amount, 0))* nvl(p_exchange_rate,1) essi_taxable_basis,
411 sum(decode(essi_applicable_flag, 'Y', calc_essi_appln_amt, 0)) essi_amount
412 from jai_ap_tds_prepayments jatp
413 where invoice_id = p_invoice_id
414 and invoice_distribution_id_prepay = p_invoice_distribution_id
415 and essi_threshold_trx_id_apply is not NULL
416 and essi_threshold_trx_id_unapply IS NULL
417 GROUP BY
418 get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)),
419 tds_section_code_prepay,
420 essi_threshold_trx_id_apply
421 ;
422 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
423 select currency_code
424 from gl_sets_of_books
425 where set_of_books_id = cp_set_of_books_id;
426
427 cursor c_get_tds_tax_id(p_invoice_id number, p_prepay_distribution_id number) is
428 select tds_tax_id_prepay
429 from jai_ap_tds_prepayments
430 where invoice_id = p_invoice_id
431 and invoice_distribution_id_prepay = p_prepay_distribution_id
432 and tds_tax_id_prepay is not null
433 and tds_applicable_flag = 'Y';
434
435 cursor c_get_wct_tax_id(p_invoice_id number, p_prepay_distribution_id number) is
436 select wct_tax_id_prepay
437 from jai_ap_tds_prepayments
438 where invoice_id = p_invoice_id
439 and invoice_distribution_id_prepay = p_prepay_distribution_id
440 and wct_tax_id_prepay is not null
441 and wct_applicable_flag = 'Y';
442
443 cursor c_get_essi_tax_id(p_invoice_id number, p_prepay_distribution_id number) is
444 select essi_tax_id_prepay
445 from jai_ap_tds_prepayments
446 where invoice_id = p_invoice_id
447 and invoice_distribution_id_prepay = p_prepay_distribution_id
448 and essi_tax_id_prepay is not null
449 and essi_applicable_flag = 'Y';
450
451 cursor c_get_invoice_num_of_apply(p_threshold_trx_id number) is
452 select invoice_to_tds_authority_num,
453 invoice_to_vendor_num,
454 /* Bug 5751783
455 * Pass the Prepayment application invoice_id for generating the
456 * prepayment unapplication
457 */
458 invoice_id,
459 tax_id
460 from jai_ap_tds_thhold_trxs
461 where threshold_trx_id = p_threshold_trx_id;
462 --Added by Wenqiong for bug13359892 begin
463 CURSOR c_get_tax_cat_section_code (p_invoice_distribution_id NUMBER) IS
464 SELECT tt.tax_category_id, tt.actual_section_code
465 FROM jai_ap_tds_prepayments tp, jai_ap_tds_inv_taxes tt
466 WHERE tp.invoice_distribution_id_prepay = p_invoice_distribution_id AND
467 tp.invoice_distribution_id = tt.invoice_distribution_id AND rownum = 1;
468 --Added by Wenqiong for bug13359892 end
469
470 r_get_total_prepayment_tax c_get_total_prepayment_tax%rowtype;
471 r_tds_details_apply c_tds_details_apply%rowtype;
472 r_wct_details_apply c_wct_details_apply%ROWTYPE;
473 r_essi_details_apply c_essi_details_apply%ROWTYPE;
474 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
475
476 lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
477 lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
478 ln_threshold_trx_id_apply number;
479 ln_threshold_trx_id_tds number;
480 ln_threshold_trx_id_wct number;
481 ln_threshold_trx_id_essi number;
482 ln_start_threshold_trx_id number;
483 ln_exchange_rate number;
484 ln_tax_id number;
485 ln_threshold_grp_id number;
486 ln_threshold_grp_audit_id number;
487 lv_invoice_num_to_tds_apply ap_invoices_all.invoice_num%type;
488 lv_invoice_num_to_vendor_apply ap_invoices_all.invoice_num%type;
489 /*Bug 5751783 - Start*/
490 ln_parent_pp_invoice_id NUMBER ;
491 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
492 lv_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
493 ln_after_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
494 lv_after_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
495 ln_temp_threshold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
496 ln_temp_threshold_hdr_id jai_ap_tds_thhold_hdrs.threshold_hdr_id%TYPE;
497 lv_slab_transition_tds_event jai_ap_tds_thhold_trxs.tds_event%type;
498 lv_ppu_tds_inv_num ap_invoices_all.invoice_num%type;
499 lv_ppu_tds_cm_num ap_invoices_all.invoice_num%type;
500 /*Bug 5751783 - End*/
501 -- Bug 6031679. Added by Lakshmi Gopalsami
502 ln_inv_dist_id_apply ap_invoice_distributions_all.invoice_distribution_id%TYPE ;
503 --Added by Wenqiong for bug13359892 begin
504 ln_tax_category_id jai_ap_tds_inv_taxes.tax_category_id%TYPE;
505 lv_section_code jai_ap_tds_inv_taxes.actual_section_code%TYPE;
506 ln_tot_tds_amt NUMBER;
507 --Added by Wenqiong for bug13359892 end
508 pre_pay_inv_id ap_invoice_distributions_all.invoice_id%TYPE; -- Added by Chong.Lei for bug#13787158
509 l_api_name CONSTANT VARCHAR2(50) := 'process_unapply()';
510 begin
511 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
512 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.process_unapply', 'START'); /* 1 */
513 get_prepay_invoice_id(p_prepay_distribution_id,pre_pay_inv_id); -- Added by Chong.Lei for bug#13787158
514
515 open c_gl_sets_of_books(p_set_of_books_id);
516 fetch c_gl_sets_of_books into r_gl_sets_of_books;
517 close c_gl_sets_of_books;
518
519 if r_gl_sets_of_books.currency_code <> p_invoice_currency_code then
520 /* Foreign currency invoice */
521 p_codepath := jai_general_pkg.plot_codepath(2, p_codepath); /* 2 */
522 ln_exchange_rate := p_exchange_rate;
523 end if;
524
525 ln_exchange_rate := nvl(ln_exchange_rate, 1);
526
527 open c_get_total_prepayment_tax(p_invoice_id, p_parent_distribution_id, ln_exchange_rate);
528 fetch c_get_total_prepayment_tax into r_get_total_prepayment_tax;
529 close c_get_total_prepayment_tax;
530
531 /* Bug 5751783
532 * Call to procedure - get_tds_threshold_slab,
533 * Store the current Threshold slab and type
534 * before PP Unapplication
535 */
536 /* Unapply TDS */
537
538 --Modified by ChongLei for bug13787158 begin
539 -------------------------------------------------------------------------------------------------
540 --if r_get_total_prepayment_tax.tds_amount > 0 then
541 if r_get_total_prepayment_tax.tds_taxable_basis > 0 then
542 -------------------------------------------------------------------------------------------------
543 --Modified by ChongLei for bug13787158 end
544 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'p_invoice_id: '||p_invoice_id);
545 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'p_parent_distribution_id: '||p_parent_distribution_id);
546 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'ln_exchange_rate: '||ln_exchange_rate);
547
548 OPEN c_tds_details_apply(p_invoice_id, p_parent_distribution_id, ln_exchange_rate);
549 LOOP
550 FETCH c_tds_details_apply INTO r_tds_details_apply;
551 EXIT WHEN c_tds_details_apply%NOTFOUND ;
552
553 ln_temp_threshold_grp_id := r_tds_details_apply.tds_threshold_grp_id;
554 /*Updated by Wenqiong for bug 13359892
555 *Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
556 --jai_ap_dtc_generation_pkg.get_tds_threshold_slab(
557 jai_ap_dtc_generation_pkg.get_tds_threshold_slab(
558 p_invoice_id => p_invoice_id,
559 p_prepay_distribution_id => p_prepay_distribution_id,
560 p_threshold_grp_id => ln_temp_threshold_grp_id,
561 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
562 p_threshold_slab_id => ln_threshold_slab_id,
563 p_threshold_type => lv_threshold_type,
564 p_process_flag => p_process_flag,
565 p_process_message => p_process_message,
566 p_codepath => p_codepath);
567
568 IF p_process_flag = 'E' THEN
569 goto exit_from_procedure;
570 END IF;
571
572
573 ln_threshold_grp_id:= r_tds_details_apply.tds_threshold_grp_id;
574 /*Updated by Wenqiong for bug 13359892
575 *Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
576 --jai_ap_tds_generation_pkg.maintain_thhold_grps
577 jai_ap_dtc_generation_pkg.maintain_thhold_grps
578 (
579 p_threshold_grp_id => ln_threshold_grp_id,
580 p_trx_invoice_unapply_amount => r_tds_details_apply.tds_taxable_basis,/*5751783*/
581 p_tds_event => 'PREPAYMENT UNAPPLICATION',
582 p_invoice_id => p_invoice_id,
583 p_invoice_distribution_id => p_invoice_distribution_id,
584 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
585 p_process_flag => p_process_flag,
586 P_process_message => p_process_message,
587 p_codepath => p_codepath
588 );
589
590 --Added by Sanjikum for Bug#5131075(4722011)
591 IF p_process_flag = 'E' THEN
592 goto exit_from_procedure;
593 END IF;
594
595
596 if r_tds_details_apply.tds_threshold_trx_id_apply is not null then
597
598 lv_invoice_to_tds_num := null;
599 lv_invoice_to_vendor_num := null;
600 ln_tax_id := null;
601
602 /* get the tds invoice numbers at apply */
603 /* Bug 5721614. Added by Lakshmi Gopalsami
604 * Fetched the invoice_id to be passed for generating the TDS invoice
605 * for prepayment unapplication.
606 */
607 open c_get_invoice_num_of_apply(r_tds_details_apply.tds_threshold_trx_id_apply);
608 fetch c_get_invoice_num_of_apply into
609 lv_invoice_num_to_tds_apply,
610 lv_invoice_num_to_vendor_apply,
611 ln_parent_pp_invoice_id,
612 ln_tax_id; -- bug 6031679
613 close c_get_invoice_num_of_apply ;
614
615 /* Bug 5751783
616 * Changed from p_invoice_id to ln_parent_pp_invoice_id ie,
617 * invoice_id of the prepayment application.
618 */
619 /*Updated by Wenqiong for bug 13359892
620 *Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
621 ln_tax_category_id := r_tds_details_apply.tax_category_id;
622 lv_section_code := r_tds_details_apply.section_code;
623 ln_tot_tds_amt := r_tds_details_apply.tds_amount;--Update to function tds amount for bug13833254 .
624 ln_tot_tds_amt := round(ln_tot_tds_amt,jai_ap_dtc_generation_pkg.get_tax_rounding(ln_parent_pp_invoice_id));
625
626
627 --jai_ap_tds_generation_pkg.generate_tds_invoices
628 jai_ap_dtc_generation_pkg.generate_dtc_invoices
629 (
630 pn_invoice_id => ln_parent_pp_invoice_id ,
631 pn_invoice_distribution_id => p_invoice_distribution_id ,
632 pv_invoice_num_to_tds_apply => lv_invoice_num_to_tds_apply ,
633 pv_invoice_num_to_vendor_apply => lv_invoice_num_to_vendor_apply ,
634 pn_taxable_amount => r_tds_details_apply.tds_taxable_basis ,/*5751783*/
635 --Updated by Wenqiong for bug 13359892 begin
636 pn_tax_amount => ln_tot_tds_amt ,/*5751783*/
637 pn_tax_category_id => ln_tax_category_id ,
638 pv_section_type => 'TDS_SECTION' ,
639 pv_section_code => lv_section_code ,
640 --Updated by Wenqiong for bug 13359892 end
641 pd_accounting_date => p_accounting_date ,
642 pv_tds_event => 'PREPAYMENT UNAPPLICATION' ,
643 pn_threshold_grp_id => r_tds_details_apply.tds_threshold_grp_id ,
644 pn_threshold_hdr_id => ln_temp_threshold_hdr_id, --Added by Chong for eTDS bug#16414088 20130320
645 pv_tds_invoice_num => lv_invoice_to_tds_num ,
646 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
647 pn_threshold_trx_id => ln_threshold_trx_id_tds ,
648 pd_creation_date => p_creation_date, -- Bug 5722028. Added by CSahoo
649 pn_calc_tax_amount => r_tds_details_apply.tds_amount, /*Added for bug 12965614 */
650 p_process_flag => p_process_flag ,
651 p_process_message => p_process_message
652 );
653
654 if p_process_flag = 'E' then
655 goto exit_from_procedure;
656 end if;
657
658 /* prepayment apply scenario for backward compatibility*/
659 /*update JAI_AP_TDS_INVOICES
660 set amt_reversed = nvl(amt_reversed, 0) - r_get_total_prepayment_tax.tds_amount,
661 amt_applied = nvl(amt_applied, 0) - abs(r_get_total_prepayment_tax.tds_taxable_basis)
662 where invoice_id = p_invoice_id;*/
663 /* prepayment apply scenario for backward compatibility*/
664
665 /* Update the threshold group */
666
667 ln_threshold_grp_id:= r_tds_details_apply.tds_threshold_grp_id;
668 if p_event = 'INSERT' then /*Added for Bug 8431516*/
669 /*Updated by Wenqiong for bug 13359892
670 *Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
671 --jai_ap_tds_generation_pkg.maintain_thhold_grps
672 jai_ap_dtc_generation_pkg.maintain_thhold_grps
673 (
674 p_threshold_grp_id => ln_threshold_grp_id,
675 --p_trx_tax_paid => r_get_total_prepayment_tax.tds_amount, --Commented by Zhiwei Hou for DTC enhancement Bug#13828149 on 20120312
676 p_trx_tax_paid => ln_tot_tds_amt,--Added by Zhiwei Hou for DTC enhancement Bug#13828149 on 20120312
677 p_tds_event => 'PREPAYMENT UNAPPLICATION',
678 p_invoice_id => p_invoice_id,
679 p_invoice_distribution_id => p_invoice_distribution_id,
680 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
681 p_process_flag => p_process_flag,
682 P_process_message => p_process_message,
683 p_codepath => p_codepath
684 );
685
686 --Added by Sanjikum for Bug#5131075(4722011)
687 IF p_process_flag = 'E' THEN
688 goto exit_from_procedure;
689 END IF;
690 END IF; /*if p_event = 'INSERT' then*/
691
692 update jai_ap_tds_prepayments
693 set tds_threshold_trx_id_unapply = ln_threshold_trx_id_tds
694 where invoice_id = p_invoice_id
695 and invoice_distribution_id_prepay = p_parent_distribution_id
696 and tds_threshold_trx_id_apply is not null
697 and tds_applicable_flag = 'Y';
698
699 if ln_start_threshold_trx_id is null then
700 ln_start_threshold_trx_id := ln_threshold_trx_id_tds;
701 end if;
702
703 end if; /* r_tds_details_apply.tds_threshold_trx_id_apply is not null */
704
705 /* update the unapply flag for invoice distribution */
706 update jai_ap_tds_prepayments
707 set unapply_flag = 'Y'
708 where invoice_id = p_invoice_id
709 and invoice_distribution_id_prepay = p_parent_distribution_id;
710
711 --Added by Chong for bug#16414088 eTDS ER Start
712 ----------------------------------------------------------------------
713 --Hook code add here after the call generate_dtc_invoices
714 IF ln_threshold_trx_id_tds IS NULL THEN -- no TDS generated
715 jai_ap_tds_pop_rpst_pkg.populate_repository(
716 pn_source_invoice_id => p_invoice_id
717 ,pn_invoice_id => NULL
718 ,pv_event => 'PREPAYMENT UNAPPLICATION'
719 ,pv_section_type => 'TDS_SECTION'
720 ,pv_section_code => r_tds_details_apply.section_code
721 ,pn_threshold_grp_id => r_tds_details_apply.tds_threshold_grp_id
722 ,pn_threshold_hdr_id => ln_temp_threshold_hdr_id
723 ,pn_invoice_distribution_id => p_invoice_distribution_id --create Prepy line in applied standard invoice
724 ,pn_prepay_distribution_id => p_prepay_distribution_id --distribution of the PP invoice applied
725 ,pn_threshold_type_id => NULL
726 ,pn_threshold_slab_id => NULL
727 );
728 END IF; --n_threshold_trx_id IS NULL
729 ----------------------------------------------------------------------
730 --Added by Chong for bug#16414088 eTDS ER End
731
732 /*Bug 9132694 - Only one Unapplication entry would be created in jai_ap_tds_thhold_trxs for one unapplication*/
733
734 /* Bug 5751783
735 * Call to procedure - get_tds_threshold_slab,
736 * Store the current Threshold slab and type
737 * After PP Unapplication
738 */
739 /*Updated by Wenqiong for bug 13359892
740 *Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
741 --jai_ap_tds_generation_pkg.get_tds_threshold_slab(
742 jai_ap_dtc_generation_pkg.get_tds_threshold_slab(
743 p_invoice_id => p_invoice_id,
744 p_prepay_distribution_id => p_prepay_distribution_id,
745 p_threshold_grp_id => ln_temp_threshold_grp_id,
746 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
747 p_threshold_slab_id => ln_after_threshold_slab_id,
748 p_threshold_type => lv_after_threshold_type,
749 p_process_flag => p_process_flag,
750 p_process_message => p_process_message,
751 p_codepath => p_codepath);
752
753 IF p_process_flag = 'E' THEN
754 goto exit_from_procedure;
755 END IF;
756 --Modified by ChongLei for bug13787158 begin
757 -------------------------------------------------------------------------------------------------
758 -- IF ln_threshold_slab_id <> ln_after_threshold_slab_id THEN
759 IF (ln_threshold_slab_id IS NULL and ln_after_threshold_slab_id IS NOT NULL) OR
760 ln_threshold_slab_id <> ln_after_threshold_slab_id THEN
761 -------------------------------------------------------------------------------------------------
762 --Modified by ChongLei for bug13787158 end
763 lv_slab_transition_tds_event :=
764 'THRESHOLD TRANSITION-PPUA(from slab id -' || ln_threshold_slab_id ||
765 'to slab id - ' || ln_after_threshold_slab_id || ')';
766 /*Updated by Wenqiong for bug 13359892
767 *Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
768 --jai_ap_tds_generation_pkg.process_threshold_transition
769 jai_ap_dtc_generation_pkg.process_threshold_transition
770 (
771 p_threshold_grp_id => ln_temp_threshold_grp_id,
772 p_threshold_slab_id => ln_after_threshold_slab_id,
773 p_invoice_id => ln_parent_pp_invoice_id,
774 p_vendor_id => p_vendor_id,
775 p_vendor_site_id => p_vendor_site_id,
776 p_accounting_date => p_accounting_date,
777 p_tds_event => lv_slab_transition_tds_event,
778 p_org_id => p_org_id,
779 pn_prepayment_inovice_id => pre_pay_inv_id, -- Added by Chong.Lei for bug#13787158
780 pn_unapply_amount => r_get_total_prepayment_tax.tds_taxable_basis, -- Added by Chong.Lei for bug#13787158
781 pv_tds_invoice_num => lv_ppu_tds_inv_num,
782 pv_cm_invoice_num => lv_ppu_tds_cm_num,
783 p_process_flag => p_process_flag,
784 p_process_message => p_process_message
785 );
786
787 IF p_process_flag = 'E' THEN
788 goto exit_from_procedure;
789 END IF;
790 END IF ; /* ln_threshold_slab_id <> ln_after_threshold_slab_id */
791 ln_threshold_trx_id_tds := NULL ;
792 /*Bug 5751783*/
793 END LOOP ;
794 CLOSE c_tds_details_apply;
795
796
797 end if; /* r_get_total_prepayment_tax.tds_amount > 0*/
798 /* Unapply TDS */
799
800
801 /* Unapply WCT */
802 ln_threshold_trx_id_apply := null;
803 if r_get_total_prepayment_tax.wct_amount > 0 then
804
805 OPEN c_wct_details_apply(p_invoice_id, p_parent_distribution_id);
806 LOOP
807 FETCH c_wct_details_apply into r_wct_details_apply;
808 EXIT WHEN c_wct_details_apply%NOTFOUND ;
809
810 ln_threshold_trx_id_apply := r_wct_details_apply.wct_threshold_trx_id_apply;
811
812 if ln_threshold_trx_id_apply is not null then
813
814 lv_invoice_to_tds_num := null;
815 lv_invoice_to_vendor_num := null;
816 ln_tax_id := null;
817
818 /* get the tds invoice numbers at apply */
819 /* Bug 5751783
820 * Fetched the invoice_id to be passed for generating the TDS invoice
821 * for prepayment unapplication.
822 */
823 open c_get_invoice_num_of_apply(ln_threshold_trx_id_apply);
824 fetch c_get_invoice_num_of_apply into
825 lv_invoice_num_to_tds_apply,
826 lv_invoice_num_to_vendor_apply,
827 ln_parent_pp_invoice_id,
828 ln_tax_id ;
829 close c_get_invoice_num_of_apply ;
830
831 /* Bug 5751783
832 * Changed from p_invoice_id to ln_parent_pp_invoice_id ie,
833 * invoice_id of the prepayment application.
834 */
835 /*Updated by Wenqiong for bug 13359892
836 *Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
837 ln_tax_category_id := r_wct_details_apply.tax_category_id;
838 lv_section_code := r_wct_details_apply.section_code;
839 ln_tot_tds_amt := r_wct_details_apply.wct_amount;
840 ln_tot_tds_amt := round(ln_tot_tds_amt,jai_ap_dtc_generation_pkg.get_tax_rounding(ln_parent_pp_invoice_id));
841
842 --jai_ap_tds_generation_pkg.generate_tds_invoices
843 jai_ap_dtc_generation_pkg.generate_dtc_invoices
844 (
845 pn_invoice_id => ln_parent_pp_invoice_id ,
846 pn_invoice_distribution_id => p_invoice_distribution_id ,
847 pv_invoice_num_to_tds_apply => lv_invoice_num_to_tds_apply ,
848 pv_invoice_num_to_vendor_apply => lv_invoice_num_to_vendor_apply ,
849 pn_taxable_amount => r_wct_details_apply.wct_taxable_basis ,
850 --Updated by Wenqiong for bug 13359892 begin
851 pn_tax_amount => ln_tot_tds_amt ,
852 pn_tax_category_id => ln_tax_category_id ,
853 pv_section_type => 'WCT_SECTION' ,
854 pv_section_code => lv_section_code ,
855 --Updated by Wenqiong for bug 13359892 end
856 pd_accounting_date => p_accounting_date ,
857 pv_tds_event => 'PREPAYMENT UNAPPLICATION' ,
858 pn_threshold_grp_id => null ,
859 pn_threshold_hdr_id => ln_temp_threshold_hdr_id, --Added by Chong for eTDS bug#16414088 20130320
860 pv_tds_invoice_num => lv_invoice_to_tds_num ,
861 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
862 pn_threshold_trx_id => ln_threshold_trx_id_wct ,
863 pd_creation_date => p_creation_date, -- Bug 5722028. Added by csahoo
864 pn_calc_tax_amount => r_tds_details_apply.wct_amount, /*Added for bug 12965614 */
865 p_process_flag => p_process_flag ,
866 p_process_message => p_process_message
867 );
868
869 if p_process_flag = 'E' then
870 goto exit_from_procedure;
871 end if;
872
873 update jai_ap_tds_prepayments
874 set wct_threshold_trx_id_unapply = ln_threshold_trx_id_wct
875 where invoice_id = p_invoice_id
876 and invoice_distribution_id_prepay = p_parent_distribution_id
877 and wct_threshold_trx_id_apply is not null
878 and wct_applicable_flag = 'Y';
879
880 if ln_start_threshold_trx_id is null then
881 ln_start_threshold_trx_id := ln_threshold_trx_id_wct;
882 end if;
883
884 end if; /* ln_threshold_trx_id_apply.tds_threshold_trx_id_apply is not null */
885 ln_threshold_trx_id_apply := null;
886 ln_threshold_trx_id_wct := null;
887 END LOOP ;
888 CLOSE c_wct_details_apply;
889 end if;
890 /* Unapply WCT */
891
892 /* Unapply ESSI */
893 ln_threshold_trx_id_apply := null;
894 /*Bug 5751783. Changed to ESSI instead of wct_amount*/
895 if r_get_total_prepayment_tax.essi_amount > 0 then
896
897 OPEN c_essi_details_apply(p_invoice_id, p_parent_distribution_id);
898 LOOP
899 FETCH c_essi_details_apply into r_essi_details_apply;
900 EXIT WHEN c_essi_details_apply%NOTFOUND ;
901
902 ln_threshold_trx_id_apply := r_essi_details_apply.essi_threshold_trx_id_apply;
903 if ln_threshold_trx_id_apply is not null then
904
905 lv_invoice_to_tds_num := null;
906 lv_invoice_to_vendor_num := null;
907 ln_tax_id := null;
908
909 /* get the tds invoice numbers at apply */
910 /* Bug 5751783
911 * Fetched the invoice_id to be passed for generating the TDS invoice
912 * for prepayment unapplication.
913 */
914 open c_get_invoice_num_of_apply(ln_threshold_trx_id_apply);
915 fetch c_get_invoice_num_of_apply into
916 lv_invoice_num_to_tds_apply,
917 lv_invoice_num_to_vendor_apply,
918 ln_parent_pp_invoice_id,
919 ln_tax_id;
920 close c_get_invoice_num_of_apply ;
921
922 /* Bug 5721614. Added by Lakshmi Gopalsami
923 * Changed from p_invoice_id to ln_parent_pp_invoice_id ie,
924 * invoice_id of the prepayment application.
925 */
926 /*Updated by Wenqiong for bug 13359892
927 *Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
928
929 ln_tax_category_id := r_essi_details_apply.tax_category_id;
930 lv_section_code := r_essi_details_apply.section_code;
931 ln_tot_tds_amt := r_essi_details_apply.essi_amount;
932 ln_tot_tds_amt := round(ln_tot_tds_amt,jai_ap_dtc_generation_pkg.get_tax_rounding(ln_parent_pp_invoice_id));
933
934 --jai_ap_tds_generation_pkg.generate_tds_invoices
935 jai_ap_dtc_generation_pkg.generate_dtc_invoices
936 (
937 pn_invoice_id => ln_parent_pp_invoice_id ,
938 pn_invoice_distribution_id => p_invoice_distribution_id ,
939 pv_invoice_num_to_tds_apply => lv_invoice_num_to_tds_apply ,
940 pv_invoice_num_to_vendor_apply => lv_invoice_num_to_vendor_apply ,
941 pn_taxable_amount => r_essi_details_apply.essi_taxable_basis ,
942 --Updated by Wenqiong for bug 13359892 begin
943 pn_tax_amount => ln_tot_tds_amt ,
944 pn_tax_category_id => ln_tax_category_id ,
945 pv_section_type => 'WCT_SECTION' ,
946 pv_section_code => lv_section_code ,
947 --Updated by Wenqiong for bug 13359892 end
948 pd_accounting_date => p_accounting_date ,
949 pv_tds_event => 'PREPAYMENT UNAPPLICATION' ,
950 pn_threshold_grp_id => null ,
951 pn_threshold_hdr_id => ln_temp_threshold_hdr_id, --Added by Chong for eTDS bug#16414088 20130320
952 pv_tds_invoice_num => lv_invoice_to_tds_num ,
953 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
954 pn_threshold_trx_id => ln_threshold_trx_id_essi ,
955 pd_creation_date => p_creation_date, -- Bug 5722028. Added by CSahoo
956 pn_calc_tax_amount => r_tds_details_apply.essi_amount, /*Added for bug 12965614 */
957 p_process_flag => p_process_flag ,
958 p_process_message => p_process_message
959 );
960
961 if p_process_flag = 'E' then
962 goto exit_from_procedure;
963 end if;
964
965 update jai_ap_tds_prepayments
966 set essi_threshold_trx_id_unapply = ln_threshold_trx_id_essi
967 where invoice_id = p_invoice_id
968 and invoice_distribution_id_prepay = p_parent_distribution_id
969 and essi_threshold_trx_id_apply is not null
970 and essi_applicable_flag = 'Y';
971
972
973 if ln_start_threshold_trx_id is null then
974 ln_start_threshold_trx_id := ln_threshold_trx_id_essi;
975 end if;
976
977 end if; /* ln_threshold_trx_id_apply.tds_threshold_trx_id_apply is not null */
978 ln_threshold_trx_id_apply := null;
979 ln_threshold_trx_id_essi := null;
980 END LOOP ;
981 CLOSE c_essi_details_apply;
982 end if;
983 /* Unapply ESSI */
984
985 /* update the unapply flag for all */
986 update jai_ap_tds_prepayments
987 set unapply_flag = 'Y'
988 where invoice_id = p_invoice_id
989 and invoice_distribution_id_prepay = p_parent_distribution_id;
990
991 /* prepayment apply scenario for backward compatibility*/
992 update JAI_AP_TDS_INVOICES
993 set amt_reversed = nvl(amt_reversed, 0) - r_get_total_prepayment_tax.tds_amount,
994 amt_applied = nvl(amt_applied, 0) - abs(p_prepay_amount * nvl(p_exchange_rate,1))
995 where invoice_id = p_invoice_id;
996
997 if ln_start_threshold_trx_id is not null then
998 /*Updated by Wenqiong for bug 13359892
999 *Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
1000 --jai_ap_tds_generation_pkg.import_and_approve
1001 jai_ap_dtc_generation_pkg.import_and_approve
1002 (
1003 p_invoice_id => ln_parent_pp_invoice_id, /*Bug 5751783*/
1004 p_start_thhold_trx_id => ln_start_threshold_trx_id,
1005 p_tds_event => 'PREPAYMENT UNAPPLICATION',
1006 p_process_flag => p_process_flag,
1007 p_process_message => p_process_message
1008 );
1009
1010 --Added by Sanjikum for Bug#5131075(4722011)
1011 IF p_process_flag = 'E' THEN
1012 goto exit_from_procedure;
1013 END IF;
1014
1015 end if;
1016
1017 << exit_from_procedure >>
1018 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
1019 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.END', G_PKG_NAME || ': '||l_api_name||'()-');
1020 return;
1021
1022 exception
1023 when others then
1024 p_process_flag := 'E';
1025 P_process_message := 'jai_ap_tds_prepayemnts_pkg.process_unapply :' || sqlerrm;
1026 return;
1027 end process_unapply;
1028
1029 /* --Commented by Chong for DTC ER START, all these thress procedures are be replaced by new procedures
1030 \***********************************************************************************************\
1031
1032 procedure allocate_prepayment
1033 (
1034 p_invoice_id in number,
1035 p_invoice_distribution_id in number, \* Of the PREPAY line *\
1036 p_prepay_amount in number,
1037 p_process_flag out nocopy varchar2,
1038 p_process_message out nocopy varchar2,
1039 p_codepath in out nocopy varchar2
1040 )
1041 is
1042 \*Bug 9494469 - Removed parameter cp_section_type from c_jai_ap_tds_inv_taxes*\
1043 cursor c_jai_ap_tds_inv_taxes(p_invoice_id number, p_prepay_distribution_id number) is
1044 select invoice_distribution_id, amount, invoice_line_number, invoice_id
1045 from jai_ap_tds_inv_taxes
1046 where invoice_id = p_invoice_id
1047 and invoice_distribution_id <> p_prepay_distribution_id
1048 --and section_type = cp_section_type \*Commented for Bug 9494469*\
1049 and nvl(actual_tax_id, default_tax_id) is not null \*Bug 8431516*\
1050 and amount > 0; --Added by bgowrava for bug#9214036
1051
1052 cursor c_get_amount_already_applied(p_invoice_distribution_id number) is
1053 select sum(application_amount)
1054 from jai_ap_tds_prepayments
1055 where invoice_distribution_id = p_invoice_distribution_id
1056 and nvl(unapply_flag, 'N') <> 'Y';
1057
1058 \*START, Added by bgowrava for bug#9214036*\
1059 cursor c_get_effective_available_amt(p_invoice_id number, p_invoice_line_num number) is
1060 select sum(amount) amount
1061 from jai_ap_tds_inv_taxes
1062 where invoice_id = p_invoice_id
1063 and invoice_line_number = p_invoice_line_num
1064 and amount < 0;
1065 \*END, Added by bgowrava for bug#9214036*\
1066
1067 ln_remaining_prepayment_amount number;
1068 ln_effective_available_amount number;
1069 ln_already_applied_amount number;
1070 ln_application_amount number;
1071 ln_less_amount number; --Added by bgowrava for bug#9214036
1072 lv_reversal_flag varchar2(1); \*Bug 8431516*\
1073
1074 begin
1075
1076 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.allocate_prepayment', 'START'); \* 1 *\
1077
1078 ln_remaining_prepayment_amount := abs(p_prepay_amount); \* Apply amount is negative *\
1079
1080 \* Loop through to get the taxable basis for each line in jai_ap_tds_inv_taxes *\
1081 \* It is ok to loop through section_type = 'TDS_SECTION as considering any one section type
1082 is ok and tds section will always be there *\
1083
1084 -- Bug 4754213. Added by Lakshmi Gopalsami
1085 for cur_si_distributions_rec in c_jai_ap_tds_inv_taxes(p_invoice_id, p_invoice_distribution_id) \*Bug 9494469 - Removed parameter cp_section_type*\
1086 loop
1087
1088 lv_reversal_flag := get_reversal_flag(cur_si_distributions_rec.invoice_distribution_id); \*Bug 8431516*\
1089 if lv_reversal_flag = 'N' then \*Bug 8431516*\
1090
1091 ln_already_applied_amount:= 0;
1092 ln_effective_available_amount := 0;
1093 ln_application_amount := 0;
1094
1095 open c_get_amount_already_applied(cur_si_distributions_rec.invoice_distribution_id);
1096 fetch c_get_amount_already_applied into ln_already_applied_amount;
1097 close c_get_amount_already_applied;
1098
1099 ln_already_applied_amount := nvl(ln_already_applied_amount, 0);
1100
1101 \*START, Added by bgowrava for bug#9214036*\
1102 open c_get_effective_available_amt(cur_si_distributions_rec.invoice_id, cur_si_distributions_rec.invoice_line_number);
1103 fetch c_get_effective_available_amt into ln_less_amount;
1104 close c_get_effective_available_amt;
1105 ln_less_amount := nvl(ln_less_amount, 0);
1106 \*END, Added by bgowrava for bug#9214036*\
1107
1108 ln_effective_available_amount := cur_si_distributions_rec.amount - ln_already_applied_amount - abs(ln_less_amount); --Added abs(ln_less_amount) by Bgowrava for Bug#9214036
1109
1110 ln_application_amount := least(ln_remaining_prepayment_amount, ln_effective_available_amount);
1111
1112 if ln_application_amount > 0 then
1113
1114 \* Insert into jai_ap_tds_prepayments *\
1115 insert into jai_ap_tds_prepayments
1116 (
1117 tds_prepayment_id ,
1118 invoice_id ,
1119 invoice_distribution_id_prepay ,
1120 invoice_distribution_id ,
1121 application_amount ,
1122 created_by ,
1123 creation_date ,
1124 last_updated_by ,
1125 last_update_date ,
1126 last_update_login
1127 )
1128 values
1129 (
1130 jai_ap_tds_prepayments_s.nextval ,
1131 p_invoice_id ,
1132 p_invoice_distribution_id ,
1133 cur_si_distributions_rec.invoice_distribution_id ,
1134 ln_application_amount ,
1135 fnd_global.user_id ,
1136 sysdate ,
1137 fnd_global.user_id ,
1138 sysdate ,
1139 fnd_global.login_id
1140 );
1141
1142 end if;
1143
1144 ln_remaining_prepayment_amount := ln_remaining_prepayment_amount - ln_application_amount;
1145
1146 if ln_remaining_prepayment_amount <= 0 then
1147 goto exit_from_procedure;
1148 end if;
1149
1150 end if; \*if lv_reversal_flag = 'N' then*\
1151
1152 end loop; \* cur_si_distributions_rec in c_jai_ap_tds_inv_taxes *\
1153
1154
1155 << exit_from_procedure >>
1156 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); \* 100 *\
1157 return;
1158
1159 exception
1160 when others then
1161 p_process_flag := 'E';
1162 P_process_message := 'jai_ap_tds_prepayemnts_pkg.allocate_prepayment :' || sqlerrm;
1163 return;
1164 end allocate_prepayment;
1165
1166 \***********************************************************************************************\
1167
1168 procedure populate_section_tax
1169 (
1170 p_invoice_id in number,
1171 p_invoice_distribution_id in number, \* Of the PREPAY line in the SI*\
1172 p_prepay_distribution_id in number, \*Distribution id of the PP invoice *\
1173 p_process_flag out nocopy varchar2,
1174 p_process_message out nocopy varchar2,
1175 p_codepath in out nocopy varchar2
1176 )
1177 is
1178
1179 cursor c_get_tax_details_pp_inv_dist(p_pre_pay_inv_id number, p_prepay_distribution_id number) is -- Added parameter p_pre_pay_inv_id by Jia for FP bug6929483
1180 select section_type,
1181 nvl(actual_section_code, default_section_code) section_code, --Added NVL condition for Bug 8431516
1182 nvl(actual_tax_id, default_tax_id) tax_id --Added NVL condition for Bug 8431516
1183 from jai_ap_tds_inv_taxes
1184 where invoice_id = p_pre_pay_inv_id -- Added where clause p_pre_pay_inv_id by Jia for FP bug6929483
1185 and invoice_distribution_id = p_prepay_distribution_id
1186 and nvl(actual_tax_id, default_tax_id) is not null; --Added NVL condition for Bug 8431516
1187
1188 cursor c_get_tax_details_si_inv_dist(p_invoice_id number, p_invoice_distribution_id number) is
1189 select section_type,
1190 nvl(actual_section_code, default_section_code) section_code,
1191 nvl(actual_tax_id, default_tax_id) tax_id
1192 from jai_ap_tds_inv_taxes
1193 where invoice_id = p_invoice_id
1194 and invoice_distribution_id = p_invoice_distribution_id;
1195
1196
1197 cursor c_jai_ap_tds_prepayments(p_invoice_id number, p_invoice_distribution_id number) is
1198 select tds_prepayment_id,
1199 invoice_distribution_id
1200 from jai_ap_tds_prepayments
1201 where invoice_id = p_invoice_id
1202 and invoice_distribution_id_prepay = p_invoice_distribution_id;
1203
1204
1205
1206 cursor c_get_tds_application_basis(p_invoice_id number) is
1207 select 'N'
1208 from jai_ap_tds_inv_taxes
1209 where invoice_id = p_invoice_id
1210 and nvl(match_status_flag, 'N') <> 'A';
1211
1212 \* Bug 5751783 - Start*\
1213 \* added parameter p_pre_pay_inv_id to cursor for bug 6929483*\
1214 CURSOR get_threshold_trx_id (p_pre_pay_inv_id number, p_invoice_distribution_id IN NUMBER )
1215 IS
1216 SELECT threshold_trx_id
1217 FROM jai_ap_tds_inv_taxes
1218 WHERE invoice_id = p_pre_pay_inv_id
1219 AND invoice_distribution_id = p_invoice_distribution_id ;
1220
1221 lv_si_thhold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%TYPE;
1222 lv_pp_thhold_trx_id jai_ap_tds_thhold_trxs.threshold_trx_id%TYPE;
1223 \* Bug 5751783 - End*\
1224
1225 lv_applicable_flag varchar2(1);
1226 lv_is_si_validated_flag varchar2(1);
1227
1228 lv_tds_section_code_prepay jai_ap_tds_prepayments.tds_section_code_prepay%type;
1229 ln_tds_tax_id_prepay jai_ap_tds_prepayments.tds_tax_id_prepay%type;
1230 ln_wct_tax_id_prepay jai_ap_tds_prepayments.wct_tax_id_prepay%type;
1231 ln_essi_tax_id_prepay jai_ap_tds_prepayments.essi_tax_id_prepay%type;
1232 lv_application_basis jai_ap_tds_prepayments.application_basis%type;
1233
1234
1235 lv_tds_section_code_other jai_ap_tds_prepayments.tds_section_code_other%type;
1236 ln_tds_tax_id_other jai_ap_tds_prepayments.tds_tax_id_other%type;
1237 lv_tds_applicable_flag jai_ap_tds_prepayments.tds_applicable_flag%type;
1238 ln_wct_tax_id_other jai_ap_tds_prepayments.wct_tax_id_other%type;
1239 lv_wct_applicable_flag jai_ap_tds_prepayments.wct_applicable_flag%type;
1240 ln_essi_tax_id_other jai_ap_tds_prepayments.essi_tax_id_other%type;
1241 lv_essi_applicable_flag jai_ap_tds_prepayments.essi_applicable_flag%type;
1242
1243 pre_pay_inv_id ap_invoice_distributions_all.invoice_id%TYPE; -- Added by Jia for FP bug6929483
1244
1245
1246 begin
1247
1248 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.populate_section_tax', 'START'); \* 1 *\
1249
1250 get_prepay_invoice_id(p_prepay_distribution_id,pre_pay_inv_id); -- Added by Jia for FP bug6929483
1251
1252 \*
1253 open c_get_tds_application_basis(p_invoice_id);
1254 fetch c_get_tds_application_basis into lv_is_si_validated_flag;
1255 close c_get_tds_application_basis;
1256
1257 if nvl(lv_is_si_validated_flag, 'Y') = 'Y' then
1258 lv_application_basis := 'STANDARD INVOICE';
1259 else
1260 lv_application_basis := 'PREPAYMENT';
1261 end if;
1262 *\
1263
1264 \* Bug 5751783
1265 * Commented the above logic as the above is obsoleted and the logic
1266 * for the deriving the basis is changed.
1267 * We should get the details of the invoice which is created latest in the
1268 * system. i.e., whichever is validated later in the system. We can get
1269 * these details by getting the value of threshold_trx_id from
1270 * jai_ap_tds_inv_taxes.
1271 *\
1272
1273 -- Get the tds_threshold_trx_id of the prepay invoice.
1274 OPEN get_threshold_trx_id (pre_pay_inv_id,p_prepay_distribution_id );
1275 FETCH get_threshold_trx_id INTO lv_pp_thhold_trx_id ;
1276 CLOSE get_threshold_trx_id;
1277
1278 -- Get the threshold_trx_id of the standard invoice.
1279 SELECT max(nvl(threshold_trx_id, 0))
1280 INTO lv_si_thhold_trx_id
1281 FROM jai_ap_tds_inv_taxes
1282 WHERE invoice_id = p_invoice_id ;
1283
1284 IF (lv_si_thhold_trx_id > NVL (lv_pp_thhold_trx_id,0 )) THEN
1285 lv_application_basis := 'STANDARD INVOICE';
1286 ELSIF ( NVL (lv_pp_thhold_trx_id,0 ) <> 0 ) THEN
1287 lv_application_basis := 'PREPAYMENT';
1288 END IF ;
1289 \*Bug 5751783 - End*\
1290
1291 \* Get the details of the taxes of all sections that was applicable on the distribution line as in the Prepayment *\
1292 for cur_rec_pp_tax_details in c_get_tax_details_pp_inv_dist(pre_pay_inv_id,p_prepay_distribution_id) loop -- Added parameter pre_pay_inv_id by Jia for FP bug6929483
1293 -- Bug 4754213. Added by Lakshmi Gopalsami
1294 if cur_rec_pp_tax_details.section_type = 'TDS_SECTION' then
1295 lv_tds_section_code_prepay := cur_rec_pp_tax_details.section_code;
1296 ln_tds_tax_id_prepay := cur_rec_pp_tax_details.tax_id;
1297 elsif cur_rec_pp_tax_details.section_type = 'WCT_SECTION' then
1298 ln_wct_tax_id_prepay := cur_rec_pp_tax_details.tax_id;
1299 elsif cur_rec_pp_tax_details.section_type = 'ESSI_SECTION' then
1300 ln_essi_tax_id_prepay := cur_rec_pp_tax_details.tax_id;
1301 end if;
1302
1303 end loop; \* cur_rec_pp_tax_details *\
1304
1305
1306 \* Loop and get all the distribution is that has been been allocated for this prepayment and
1307 get the tax details that is applicable on the allocated line *\
1308 for cur_rec_pp_allocations in c_jai_ap_tds_prepayments(p_invoice_id, p_invoice_distribution_id) loop
1309
1310 for cur_rec in c_get_tax_details_si_inv_dist(p_invoice_id, cur_rec_pp_allocations.invoice_distribution_id) loop
1311 -- Bug 4754213. Added by Lakshmi Gopalsami
1312 if cur_rec.section_type = 'TDS_SECTION' then
1313
1314 lv_tds_section_code_other := cur_rec.section_code;
1315 ln_tds_tax_id_other := cur_rec.tax_id;
1316
1317 if lv_tds_section_code_other = lv_tds_section_code_prepay and
1318 lv_tds_section_code_other is not null and
1319 lv_tds_section_code_prepay is not null
1320 then
1321 lv_tds_applicable_flag := 'Y';
1322 else
1323 lv_tds_applicable_flag := 'N';
1324 end if;
1325
1326 elsif cur_rec.section_type = 'WCT_SECTION' then
1327
1328 ln_wct_tax_id_other := cur_rec.tax_id;
1329
1330 if ln_wct_tax_id_prepay is not null and ln_wct_tax_id_other is not null then
1331 lv_wct_applicable_flag := 'Y';
1332 else
1333 lv_wct_applicable_flag := 'N';
1334 end if;
1335
1336 elsif cur_rec.section_type = 'ESSI_SECTION' then
1337
1338 ln_essi_tax_id_other := cur_rec.tax_id;
1339
1340 if ln_essi_tax_id_prepay is not null and ln_essi_tax_id_other is not null then
1341 lv_essi_applicable_flag := 'Y';
1342 else
1343 lv_essi_applicable_flag := 'N';
1344 end if;
1345
1346 end if; \* Section type of the SI distributions *\
1347
1348 end loop; \* Cur rec *\
1349
1350
1351 \* Update jai_ap_tds_prepayments *\
1352 update jai_ap_tds_prepayments
1353 set application_basis = lv_application_basis ,
1354 tds_section_code_prepay = lv_tds_section_code_prepay ,
1355 tds_section_code_other = lv_tds_section_code_other ,
1356 tds_tax_id_prepay = ln_tds_tax_id_prepay ,
1357 tds_tax_id_other = ln_tds_tax_id_other ,
1358 tds_applicable_flag = lv_tds_applicable_flag ,
1359 wct_tax_id_prepay = ln_wct_tax_id_prepay ,
1360 wct_tax_id_other = ln_wct_tax_id_other ,
1361 wct_applicable_flag = lv_wct_applicable_flag ,
1362 essi_tax_id_prepay = ln_essi_tax_id_prepay ,
1363 essi_tax_id_other = ln_essi_tax_id_other ,
1364 essi_applicable_flag = lv_essi_applicable_flag
1365 where tds_prepayment_id = cur_rec_pp_allocations.tds_prepayment_id;
1366
1367
1368 end loop; \* cur_rec_pp_allocations *\
1369
1370
1371 << exit_from_procedure >>
1372 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); \* 100 *\
1373 return;
1374
1375 exception
1376 when others then
1377 p_process_flag := 'E';
1378 P_process_message := 'jai_ap_tds_prepayemnts_pkg.populate_section_tax :' || sqlerrm;
1379 return;
1380 end populate_section_tax;
1381
1382
1383 \***********************************************************************************************\
1384 procedure process_tds_invoices
1385 (
1386 p_event in varchar2, \*Bug 8431516*\
1387 p_invoice_id in number,
1388 p_invoice_distribution_id in number,
1389 p_prepay_distribution_id in number,
1390 p_prepay_amount in number,
1391 p_vendor_id in number,
1392 p_vendor_site_id in number,
1393 p_accounting_date in date,
1394 p_invoice_currency_code in varchar2,
1395 p_exchange_rate in number,
1396 p_set_of_books_id in number,
1397 p_org_id in number,
1398 -- Bug 5722028. Added by CSahoo
1399 p_creation_date in date,
1400 p_process_flag out nocopy varchar2,
1401 p_process_message out nocopy varchar2,
1402 p_codepath in out nocopy varchar2
1403 )
1404 is
1405
1406 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
1407 select currency_code
1408 from gl_sets_of_books
1409 where set_of_books_id = cp_set_of_books_id;
1410
1411 cursor c_jai_ap_tds_prepayments(p_invoice_id number, p_invoice_distribution_id number) is
1412 select tds_prepayment_id,
1413 application_amount,
1414 application_basis,
1415 \*
1416 decode(tds_applicable_flag, 'Y',
1417 decode(application_basis, 'STANDARD INVOICE', tds_tax_id_other, tds_tax_id_prepay),
1418 null) tds_tax_id,
1419 decode(wct_applicable_flag, 'Y',
1420 decode(application_basis, 'STANDARD INVOICE', wct_tax_id_other, wct_tax_id_prepay),
1421 null) wct_tax_id,
1422 decode(essi_applicable_flag, 'Y',
1423 decode(application_basis, 'STANDARD INVOICE', essi_tax_id_other, essi_tax_id_prepay),
1424 null) essi_tax_id
1425 *\
1426 \* Bug 6363056. Commented the above
1427 * and added the following. Need to selected the lowest rate between
1428 * SI and PP
1429 *\
1430 tds_applicable_flag, tds_tax_id_other, tds_tax_id_prepay,
1431 wct_applicable_flag, wct_tax_id_other, wct_tax_id_prepay,
1432 essi_applicable_flag, essi_tax_id_other, essi_tax_id_prepay
1433 from jai_ap_tds_prepayments
1434 where invoice_id = p_invoice_id
1435 and invoice_distribution_id_prepay = p_invoice_distribution_id;
1436
1437
1438 cursor c_ja_in_tax_codes(p_tax_id number) is
1439 select nvl(tax_rate, 0) tax_rate
1440 from JAI_CMN_TAXES_ALL
1441 where tax_id = p_tax_id;
1442
1443 --Add parameter p_pre_pay_inv_id in cursor c_get_prepayment_throup by Jia for FP bug6929483, Begin
1444 cursor c_get_prepayment_thgroup(p_pre_pay_inv_id number, p_prepay_distribution_id number,cp_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
1445 select threshold_grp_id,
1446 actual_tax_id,
1447 threshold_trx_id \*Bug 6363056*\
1448 from jai_ap_tds_inv_taxes
1449 where invoice_id = p_pre_pay_inv_id -- Added by Jia for FP bug6929483
1450 and invoice_distribution_id = p_prepay_distribution_id
1451 and section_type = cp_section_type; --rchandan for bug#4428980
1452
1453 cursor c_get_pp_section_tax_id(p_prepay_distribution_id number, p_section_type varchar2) is
1454 select actual_tax_id, invoice_id \*Bug 5751783*\
1455 from jai_ap_tds_inv_taxes
1456 where invoice_distribution_id = p_prepay_distribution_id
1457 and section_type = p_section_type;
1458
1459
1460 cursor c_jai_ap_tds_thhold_grps(p_threshold_grp_id number) is
1461 select nvl(current_threshold_slab_id, 0) current_threshold_slab_id
1462 from jai_ap_tds_thhold_grps
1463 where threshold_grp_id = p_threshold_grp_id;
1464
1465 cursor c_ap_invoices_all (p_invoice_distribution_id number) is
1466 select invoice_num, invoice_id \*Bug 5751783*\
1467 from ap_invoices_all
1468 where invoice_id in
1469 ( select invoice_id
1470 from jai_ap_tds_inv_taxes \* ap_invoice_distributions not used for mutation problem *\
1471 where invoice_distribution_id = p_invoice_distribution_id);
1472
1473
1474 cursor c_get_total_prepayment_tax
1475 (p_invoice_id number, p_invoice_distribution_id number, p_exchange_rate number) is
1476 select sum( decode(tds_applicable_flag , 'Y', application_amount*p_exchange_rate, 0) ) tds_taxable_basis,
1477 sum( decode(tds_applicable_flag , 'Y', tds_application_amount, 0) ) tds_amount,
1478 sum( decode(wct_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) wct_taxable_basis,
1479 sum( decode(wct_applicable_flag, 'Y', wct_application_amount, 0) ) wct_amount,
1480 sum( decode(essi_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) essi_taxable_basis,
1481 sum( decode(essi_applicable_flag, 'Y', essi_application_amount, 0) ) essi_amount
1482 from jai_ap_tds_prepayments
1483 where invoice_id = p_invoice_id
1484 and invoice_distribution_id_prepay = p_invoice_distribution_id;
1485
1486 \* Bug 4522507. Added by Lakshmi Gopalsami *\
1487
1488 cursor c_get_if_tds_inv_generated_pp(p_prepay_distribution_id number) is
1489 select threshold_trx_id
1490 from jai_ap_tds_inv_taxes
1491 where invoice_distribution_id = p_prepay_distribution_id
1492 -- Bug 4754213. Added by Lakshmi Gopalsami
1493 and section_type = 'TDS_SECTION';
1494
1495 \*Bug 6363056 - Replaced p_invoice_distribution_id with p_item_distribution_id*\
1496 cursor c_get_amt_tds_inv_generated_si(p_invoice_id number, p_item_distribution_id number) is
1497 select sum(calc_tds_appln_amt) , sum(application_amount)
1498 from jai_ap_tds_prepayments jatp
1499 where invoice_id = p_invoice_id
1500 and invoice_distribution_id_prepay = p_invoice_distribution_id
1501 \*Bug 6363056. Added invoice_distribution_id condition also*\
1502 and invoice_distribution_id = p_item_distribution_id
1503 and tds_applicable_flag = 'Y'
1504 and exists (select '1'
1505 from jai_ap_tds_inv_taxes
1506 where invoice_distribution_id = jatp.invoice_distribution_id
1507 -- Bug 4754213. Added by Lakshmi Gopalsami
1508 and section_type = 'TDS_SECTION'
1509 and threshold_trx_id is not null
1510 );
1511
1512 \*Bug 6363056 Start*\
1513 cursor c_si_ap_invoices_all (p_invoice_id number) is
1514 select invoice_num, invoice_id
1515 from ap_invoices_all
1516 where invoice_id = p_invoice_id;
1517
1518 CURSOR c_get_thgrp_det ( p_threshold_grp_id NUMBER ) IS
1519 SELECT *
1520 FROM jai_ap_tds_thhold_grps
1521 WHERE threshold_grp_id = p_threshold_grp_id;
1522 \*Bug 6363056 End*\
1523
1524
1525 --Added by Xiao Lv for Bug#8345080 on 7-Jan-10, begin
1526
1527 cursor c_get_grp_details_si_inv_dist(p_invoice_id number, p_invoice_distribution_id number)
1528 is
1529 select threshold_grp_id
1530 from jai_ap_tds_inv_taxes
1531 where invoice_id = p_invoice_id
1532 and invoice_distribution_id = p_invoice_distribution_id
1533 and section_type = 'TDS_SECTION'; --Added for bug#8855650 by JMEENA
1534
1535 cursor c_get_tax_sec_det(p_invoice_id number, p_invoice_distribution_id number)
1536 is
1537 select tds_section_code_other, tds_tax_id_other, application_amount, invoice_distribution_id
1538 from jai_ap_tds_prepayments
1539 where invoice_id = p_invoice_id
1540 and invoice_distribution_id_prepay = p_invoice_distribution_id;
1541
1542 ln_si_thgrp_id number;
1543 r_get_tax_sec_det c_get_tax_sec_det%rowtype;
1544
1545 --Added by Xiao Lv for Bug#8345080 on 7-Jan-10, end
1546 \*Bug 12671504 - Start*\
1547 \*Fetch the taxable amount for which TDS is generated*\
1548 CURSOR c_taxable_amount(p_invoice_id NUMBER)
1549 IS
1550 SELECT nvl(sum(taxable_amount), 0)
1551 FROM jai_ap_tds_thhold_trxs
1552 WHERE invoice_id = p_invoice_id
1553 AND tds_event = 'INVOICE VALIDATE'
1554 group by invoice_id;
1555
1556 \*Fetch the taxable basis for which TDS would be generated*\
1557 CURSOR c_available_amount (p_invoice_id number, p_exchange_rate NUMBER)
1558 IS
1559 SELECT nvl(sum(amount*nvl(p_exchange_rate, 1)), 0)
1560 FROM jai_ap_tds_inv_taxes
1561 WHERE invoice_id = p_invoice_id
1562 AND nvl(actual_tax_id, default_tax_id) is not null
1563 AND section_type = 'TDS_SECTION'
1564 AND actual_section_code IS NOT NULL
1565 AND threshold_trx_id IS NOT NULL
1566 group by invoice_id;
1567 \*Bug 12671504 - End*\
1568
1569 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
1570 r_ja_in_tax_codes c_ja_in_tax_codes%rowtype;
1571 r_get_total_prepayment_tax c_get_total_prepayment_tax%rowtype;
1572
1573 ln_exchange_rate number;
1574 ln_threshold_grp_id number;
1575 ln_total_tds_amount number;
1576 ln_current_threshold_slab_id jai_ap_tds_thhold_grps.current_threshold_slab_id%type;
1577 ln_prepay_tax_id number;
1578
1579 lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
1580 lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
1581 lv_invoice_num_prepay_apply ap_invoices_all.invoice_num%type;
1582 ln_threshold_trx_id_tds number;
1583 ln_threshold_trx_id_wct number;
1584 ln_threshold_trx_id_essi number;
1585 ln_start_threshold_trx_id number;
1586 ln_prepayment_amount number;
1587
1588 lb_result boolean;
1589 ln_req_id number;
1590 ln_pp_section_tax_id number;
1591 ln_threshold_grp_audit_id number;
1592 lv_application_basis jai_ap_tds_prepayments.application_basis%type;
1593 \* Bug 4522507. Added by Lakshmi Gopalsami *\
1594 ln_threshold_trx_id_prepay jai_ap_tds_inv_taxes.threshold_trx_id%type;
1595 ln_amt_tds_inv_generated_si number;
1596 --Added the below 6 variables by Sanjikum for Bug#5131075(4718907)
1597 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
1598 lv_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
1599 ln_after_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
1600 lv_after_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
1601 ln_temp_threshold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
1602 ln_temp_threshold_hdr_id jai_ap_tds_thhold_hdrs.threshold_hdr_id%TYPE;
1603
1604 -- Bug 5722028. Added by CSahoo
1605 ln_tds_tmp_amt number;
1606 \*Bug 5751783 - Start*\
1607 ln_si_tax_id NUMBER ;
1608 ln_parent_invoice_id NUMBER ;
1609 ln_pp_section_invoice_id NUMBER ;
1610 \*Bug 5751783 - End*\
1611 \*Bug 6363056 - Start*\
1612 r_ja_in_tax_codes_prepay c_ja_in_tax_codes%rowtype;
1613 ln_tax_rate_basis JAI_CMN_TAXES_ALL.tax_rate%TYPE ;
1614 ln_si_wct_tax_id JAI_CMN_TAXES_ALL.tax_id%TYPE ;
1615 ln_si_essi_tax_id JAI_CMN_TAXES_ALL.tax_id%TYPE ;
1616 ln_si_thhold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
1617 ln_pp_thhold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
1618 ln_parent_tax_id JAI_CMN_TAXES_ALL.tax_id%TYPE ;
1619 ln_tds_application_amt jai_ap_tds_prepayments.application_amount%TYPE ;
1620 r_pp_jai_ap_tds_thhold_grps c_get_thgrp_det%ROWTYPE ;
1621 r_si_jai_ap_tds_thhold_grps c_get_thgrp_det%ROWTYPE ;
1622 \*Bug 6363056 - End*\
1623 pre_pay_inv_id ap_invoice_distributions_all.invoice_id%TYPE; -- Added by Jia for FP bug6929483
1624 \*START, Bgowrava for Bug#7626202*\
1625 ln_tot_tds_amt number := 0;
1626 ln_tot_appln_amt number := 0;
1627 \*END, Bgowrava for Bug#7626202*\
1628 ln_application_mode VARCHAR2(1); \*Bug 12671504*\
1629 ln_taxable_amount NUMBER; \*Bug 12671504*\
1630 ln_available_amount NUMBER; \*Bug 12671504*\
1631
1632
1633 begin
1634
1635 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.process_tds_invoices', 'START'); \* 1 *\
1636
1637 get_prepay_invoice_id(p_prepay_distribution_id,pre_pay_inv_id); -- Added by Jia for FP bug6929483
1638
1639 open c_gl_sets_of_books(p_set_of_books_id);
1640 fetch c_gl_sets_of_books into r_gl_sets_of_books;
1641 close c_gl_sets_of_books;
1642
1643 if r_gl_sets_of_books.currency_code <> p_invoice_currency_code then
1644 \* Foreign currency invoice *\
1645 p_codepath := jai_general_pkg.plot_codepath(6.1, p_codepath); \* 6.1 *\
1646 ln_exchange_rate := p_exchange_rate;
1647 end if;
1648
1649 ln_exchange_rate := nvl(ln_exchange_rate, 1);
1650
1651 ln_prepayment_amount := -1 * p_prepay_amount * ln_exchange_rate;
1652
1653 \* update the tax amount for the prepayements *\
1654 for cur_rec in c_jai_ap_tds_prepayments(p_invoice_id, p_invoice_distribution_id)
1655 loop
1656
1657 if lv_application_basis is null then
1658 lv_application_basis := cur_rec.application_basis;
1659 end if;
1660
1661 \* TDS application amount *\
1662 if cur_rec.tds_tax_id_other is not null AND
1663 cur_rec.tds_tax_id_prepay IS NOT NULL AND -- Bug 6363056
1664 cur_rec.tds_applicable_flag = 'Y' -- Bug 6363056
1665 THEN
1666
1667 r_ja_in_tax_codes := null;
1668 open c_ja_in_tax_codes(cur_rec.tds_tax_id_other); -- Bug 6363056
1669 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
1670 close c_ja_in_tax_codes;
1671
1672 ln_tax_rate_basis := r_ja_in_tax_codes.tax_rate; -- bug 6363056
1673 ln_si_tax_id := cur_rec.tds_tax_id_other; -- bug 6363056
1674
1675
1676 \* Bug 5722028. Addd by CSahoo
1677 * Need to round the value as per the setup.
1678 *\
1679 ln_tds_tmp_amt := 0;
1680 if r_gl_sets_of_books.currency_code = p_invoice_currency_code then
1681 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1682 * (ln_tax_rate_basis/100), \*Bug 6363056*\
1683 jai_ap_tds_generation_pkg.g_inr_currency_rounding);
1684 else
1685 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1686 * (ln_tax_rate_basis/100), \*Bug 6363056*\
1687 jai_ap_tds_generation_pkg.g_fcy_currency_rounding);
1688 end if;
1689 IF trunc(p_creation_date) >=
1690 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
1691 ln_tds_tmp_amt := jai_ap_tds_generation_pkg.get_rnded_value(ln_tds_tmp_amt);
1692 END IF;
1693 -- End for bug 5722028
1694
1695
1696 update jai_ap_tds_prepayments
1697 set tds_application_amount = ln_tds_tmp_amt, -- Bug 5722028
1698 \*Bug 5751783. Added the update for non-rounded value also*\
1699 calc_tds_appln_amt = cur_rec.application_amount * ln_exchange_rate * (ln_tax_rate_basis/100)
1700 where tds_prepayment_id = cur_rec.tds_prepayment_id;
1701
1702 end if; \* TDS *\
1703
1704 \* WCT application amount *\
1705 if cur_rec.wct_tax_id_other is not null AND
1706 cur_rec.wct_tax_id_prepay IS NOT NULL AND -- Bug 6363056
1707 cur_rec.wct_applicable_flag = 'Y' -- Bug 6363056
1708 THEN
1709
1710 r_ja_in_tax_codes := null;
1711 open c_ja_in_tax_codes(cur_rec.wct_tax_id_other); -- Bug 6363056
1712 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
1713 close c_ja_in_tax_codes;
1714
1715 \*Bug 6363056 - Start*\
1716 ln_tax_rate_basis := r_ja_in_tax_codes.tax_rate;
1717 ln_si_wct_tax_id := cur_rec.wct_tax_id_other;
1718
1719 IF cur_rec.wct_tax_id_other <> cur_rec.wct_tax_id_prepay THEN
1720 r_ja_in_tax_codes_prepay := NULL ;
1721 OPEN c_ja_in_tax_codes(cur_rec.wct_tax_id_prepay);
1722 FETCH c_ja_in_tax_codes INTO r_ja_in_tax_codes_prepay;
1723 CLOSE c_ja_in_tax_codes;
1724 IF ln_tax_rate_basis > r_ja_in_tax_codes_prepay.tax_rate THEN
1725 ln_tax_rate_basis := r_ja_in_tax_codes_prepay.tax_rate; \* Modified r_ja_in_tax_codes to r_ja_in_tax_codes_prepay for Bug 6972230 *\
1726 ln_si_wct_tax_id := cur_rec.wct_tax_id_prepay; \* Modified wct_tax_id_other to wct_tax_id_prepay for Bug 6972230 *\
1727 END IF ;
1728 END IF ;
1729 \*Bug 6363056 - End*\
1730
1731
1732 \* Bug 5722028. Addd by CSahoo
1733 * Need to round the value as per the setup.
1734 *\
1735 ln_tds_tmp_amt := 0;
1736 if r_gl_sets_of_books.currency_code = p_invoice_currency_code then
1737 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1738 * (ln_tax_rate_basis/100), \*Bug 6363056*\
1739 jai_ap_tds_generation_pkg.g_inr_currency_rounding);
1740 else
1741 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1742 * (ln_tax_rate_basis/100), \*Bug 6363056*\
1743 jai_ap_tds_generation_pkg.g_fcy_currency_rounding);
1744 end if;
1745
1746 \* Bug 7280925. Commented by Lakshmi Gopalsami
1747 * Rounding to 10 is applicable only for TDS.
1748 * WCT and ESSI should be rounded to Re. 1
1749 IF trunc(p_creation_date) >=
1750 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
1751 ln_tds_tmp_amt := jai_ap_tds_generation_pkg.get_rnded_value(ln_tds_tmp_amt);
1752 END IF;
1753 *\
1754 -- End for bug 5722028
1755
1756 update jai_ap_tds_prepayments
1757 set wct_application_amount = ln_tds_tmp_amt, -- Bug 5722028
1758 \*Bug 5751783. Added the update for non-rounded value also*\
1759 calc_wct_appln_amt = cur_rec.application_amount * ln_exchange_rate * (ln_tax_rate_basis/100)
1760 where tds_prepayment_id = cur_rec.tds_prepayment_id;
1761
1762 end if; \* WCT *\
1763
1764 \* ESSI application amount *\
1765 if cur_rec.essi_tax_id_other is not null AND
1766 cur_rec.essi_tax_id_prepay IS NOT NULL AND -- Bug 6363056
1767 cur_rec.essi_applicable_flag = 'Y' -- Bug 6363056
1768 THEN
1769
1770 r_ja_in_tax_codes := null;
1771 open c_ja_in_tax_codes(cur_rec.essi_tax_id_other); --Bug 6363056
1772 fetch c_ja_in_tax_codes into r_ja_in_tax_codes;
1773 close c_ja_in_tax_codes;
1774
1775 \*Bug 6363056 - Start*\
1776 ln_tax_rate_basis := r_ja_in_tax_codes.tax_rate;
1777 ln_si_essi_tax_id := cur_rec.essi_tax_id_other;
1778
1779 IF cur_rec.essi_tax_id_other <> cur_rec.essi_tax_id_prepay THEN
1780 r_ja_in_tax_codes_prepay := NULL ;
1781 OPEN c_ja_in_tax_codes(cur_rec.essi_tax_id_prepay);
1782 FETCH c_ja_in_tax_codes INTO r_ja_in_tax_codes_prepay;
1783 CLOSE c_ja_in_tax_codes;
1784 IF ln_tax_rate_basis > r_ja_in_tax_codes_prepay.tax_rate THEN
1785 ln_tax_rate_basis := r_ja_in_tax_codes.tax_rate;
1786 ln_si_essi_tax_id := cur_rec.wct_tax_id_prepay;
1787 END IF ;
1788 END IF ;
1789 \*Bug 6363056 - End*\
1790
1791 \* Bug 5722028. Addd by Lakshmi Gopalsami
1792 * Need to round the value as per the setup.
1793 *\
1794 ln_tds_tmp_amt := 0;
1795 if r_gl_sets_of_books.currency_code = p_invoice_currency_code then
1796 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1797 * (ln_tax_rate_basis/100),
1798 jai_ap_tds_generation_pkg.g_inr_currency_rounding);
1799 else
1800 ln_tds_tmp_amt := round(cur_rec.application_amount *ln_exchange_rate
1801 * (ln_tax_rate_basis/100),
1802 jai_ap_tds_generation_pkg.g_fcy_currency_rounding);
1803 end if;
1804 \* Bug 7280925. Commented by Lakshmi Gopalsami
1805 * Rounding to 10 is applicable only for TDS.
1806 * WCT and ESSI should be rounded to Re. 1
1807 IF trunc(p_creation_date) >=
1808 trunc(jai_ap_tds_generation_pkg.gd_tds_rounding_effective_date) THEN
1809 ln_tds_tmp_amt := jai_ap_tds_generation_pkg.get_rnded_value(ln_tds_tmp_amt);
1810 END IF;
1811 *\
1812 -- End for bug 5722028
1813
1814 update jai_ap_tds_prepayments
1815 set essi_application_amount = ln_tds_tmp_amt, --Bug 5722028
1816 \*Bug 5751783. Added the update for non-rounded value also*\
1817 calc_essi_appln_amt = cur_rec.application_amount * ln_exchange_rate * (ln_tax_rate_basis/100)
1818 where tds_prepayment_id = cur_rec.tds_prepayment_id;
1819
1820 end if; \* ESSI *\
1821
1822 end loop;
1823
1824 open c_get_total_prepayment_tax(p_invoice_id, p_invoice_distribution_id, ln_exchange_rate);
1825 fetch c_get_total_prepayment_tax into r_get_total_prepayment_tax;
1826 close c_get_total_prepayment_tax;
1827
1828 --Added by Xiao Lv for bug#8345080 on 07-Jan-10, begin
1829
1830 open c_get_tax_sec_det(p_invoice_id, p_invoice_distribution_id);
1831 fetch c_get_tax_sec_det into r_get_tax_sec_det;
1832 close c_get_tax_sec_det;
1833
1834 \*Bug 12671504 - Start*\
1835 \*Compare the Taxable amount for which TDS is deducted with the amount for which TDS would be deducted.
1836 If the Taxable amount is less than the Available amount then Prepayment was applied prior to validation.
1837 Taxable amount for Invoice validated after Prepayment application would be effective amount and hence
1838 Prepayment application mode would be 'B' indicating prepayment application happened prior to validation.
1839 Threshold Groups need not be updated with the Prepayment application amount as only Effective amount i.e. Invoice - Prepay
1840 was passed to Threshold Group during validation*\
1841 ln_application_mode := NULL;
1842 OPEN c_taxable_amount(p_invoice_id);
1843 FETCH c_taxable_amount INTO ln_taxable_amount;
1844 CLOSE c_taxable_amount;
1845
1846 IF ln_taxable_amount > 0 THEN
1847 OPEN c_available_amount(p_invoice_id, p_exchange_rate);
1848 FETCH c_available_amount INTO ln_available_amount;
1849 CLOSE c_available_amount;
1850
1851 IF ln_taxable_amount < ln_available_amount THEN
1852 ln_application_mode := 'B';
1853 ELSE
1854 ln_application_mode := 'A';
1855 END IF;
1856 ELSE
1857 ln_application_mode := 'A';
1858 END IF;
1859 \*Bug 12671504 - End*\
1860
1861 if r_get_tax_sec_det.application_amount > 0
1862 and (r_get_tax_sec_det.tds_section_code_other is not null or r_get_tax_sec_det.tds_tax_id_other is not null)
1863 and r_get_total_prepayment_tax.tds_amount = 0
1864 and ln_application_mode = 'A'
1865 then
1866 open c_get_grp_details_si_inv_dist(p_invoice_id, r_get_tax_sec_det.invoice_distribution_id);
1867 fetch c_get_grp_details_si_inv_dist into ln_si_thgrp_id;
1868 close c_get_grp_details_si_inv_dist;
1869
1870 jai_ap_tds_generation_pkg.maintain_thhold_grps(
1871 p_threshold_grp_id => ln_si_thgrp_id,
1872 p_trx_invoice_apply_amount => r_get_tax_sec_det.application_amount,
1873 p_tds_event => 'PREPAYMENT APPLICATION',
1874 p_invoice_id => p_invoice_id,
1875 p_invoice_distribution_id => p_invoice_distribution_id,
1876 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
1877 p_creation_Date => p_creation_date,
1878 p_process_flag => p_process_flag,
1879 P_process_message => p_process_message,
1880 p_codepath => p_codepath
1881 );
1882 end if; --r_get_tax_sec_det.application_amount > 0
1883
1884 --Added by Xiao Lv for bug#8345080 on 07-Jan-10, end
1885
1886 if r_get_total_prepayment_tax.tds_amount > 0 AND ln_application_mode = 'A' then
1887
1888 \* Bug 6363056
1889 * Get the details of threshold grp for prepay and invoice.
1890 * This determines which group needs to be hit.
1891 *\
1892
1893 -- Added parameter pre_pay_inv_id by Jia for FP bug6929483,Begin
1894 ----------------------------------------------------------------------
1895 --open c_get_prepayment_thgroup(p_prepay_distribution_id,'TDS_SECTION'); --rchandan for bug#4428980
1896 open c_get_prepayment_thgroup(pre_pay_inv_id,p_prepay_distribution_id,'TDS_SECTION');
1897 ----------------------------------------------------------------------
1898 -- Added parameter pre_pay_inv_id by Jia for FP bug6929483,End
1899 fetch c_get_prepayment_thgroup into ln_pp_thhold_grp_id, ln_prepay_tax_id, ln_threshold_trx_id_prepay;
1900 close c_get_prepayment_thgroup;
1901
1902 IF ln_pp_thhold_grp_id IS NULL
1903 AND (r_get_total_prepayment_tax.tds_amount > 0 OR
1904 r_get_total_prepayment_tax.wct_amount > 0 OR
1905 r_get_total_prepayment_tax.essi_amount > 0) THEN
1906 p_process_flag := 'E';
1907 P_process_message := 'Threshold group identifier is not found against the prepayment invoice TDS tax, cannot proceed.';
1908 goto exit_from_procedure;
1909 end if;
1910
1911 OPEN c_get_thgrp_det(ln_pp_thhold_grp_id);
1912 FETCH c_get_thgrp_det INTO r_pp_jai_ap_tds_thhold_grps;
1913 CLOSE c_get_thgrp_det;
1914
1915 FOR get_si_det IN (SELECT jattt.*,
1916 jatp.tds_prepayment_id tds_prepayment_id,
1917 jatp.application_amount tds_taxable_basis,
1918 jatp.invoice_distribution_id tax_dist
1919 FROM jai_ap_tds_thhold_trxs jattt,
1920 jai_ap_tds_prepayments jatp
1921 WHERE jattt.invoice_id = jatp.invoice_id
1922 AND jattt.tds_event = 'INVOICE VALIDATE'
1923 AND jatp.tds_applicable_flag ='Y'
1924 AND invoice_distribution_id_prepay = p_invoice_distribution_id
1925 AND jattt.invoice_id = p_invoice_id
1926 AND jatp.invoice_distribution_id in
1927 (select invoice_distribution_id
1928 from jai_ap_tdS_inv_taxes
1929 where threshold_trx_id = jattt.threshold_trx_id
1930 and invoice_id = p_invoice_id
1931 and section_type ='TDS_SECTION'
1932 )
1933 )
1934 LOOP
1935
1936 ln_temp_threshold_grp_id := get_si_det.threshold_grp_id;
1937 ln_parent_tax_id := get_si_det.tax_id ;
1938
1939 IF NVL (ln_pp_thhold_grp_id, 0) <> 0 AND
1940 NVL (ln_temp_threshold_grp_id, 0) <> 0 AND
1941 NVL (ln_temp_threshold_grp_id,0 ) <> NVL (ln_pp_thhold_grp_id, 0)
1942 THEN
1943 OPEN c_get_thgrp_det(ln_temp_threshold_grp_id);
1944 FETCH c_get_thgrp_det INTO r_si_jai_ap_tds_thhold_grps;
1945 CLOSE c_get_thgrp_det;
1946 IF r_pp_jai_ap_tds_thhold_grps.fin_year > r_si_jai_ap_tds_thhold_grps.fin_year THEN
1947 ln_temp_threshold_grp_id := ln_pp_thhold_grp_id;
1948 END IF ;
1949 END IF ;
1950
1951 ln_threshold_grp_id := ln_temp_threshold_grp_id;
1952
1953 --Call to procedure - get_tds_threshold_slab, Store the current Threshold slab and type before PP application
1954 jai_ap_tds_generation_pkg.get_tds_threshold_slab(
1955 p_prepay_distribution_id => p_prepay_distribution_id,
1956 p_threshold_grp_id => ln_temp_threshold_grp_id,
1957 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
1958 p_threshold_slab_id => ln_threshold_slab_id,
1959 p_threshold_type => lv_threshold_type,
1960 p_process_flag => p_process_flag,
1961 p_process_message => p_process_message,
1962 p_codepath => p_codepath);
1963
1964 IF p_process_flag = 'E' THEN
1965 goto exit_from_procedure;
1966 END IF;
1967
1968 if r_get_total_prepayment_tax.tds_amount > 0 THEN
1969 \* update the threshold with the tds amount that will be impacted because of this application *\
1970 jai_ap_tds_generation_pkg.maintain_thhold_grps
1971 (
1972 p_threshold_grp_id => ln_threshold_grp_id,
1973 p_trx_invoice_apply_amount => get_si_det.tds_taxable_basis,
1974 p_tds_event => 'PREPAYMENT APPLICATION',
1975 p_invoice_id => p_invoice_id,
1976 p_invoice_distribution_id => p_invoice_distribution_id,
1977 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
1978 p_creation_Date => p_creation_date,
1979 p_process_flag => p_process_flag,
1980 P_process_message => p_process_message,
1981 p_codepath => p_codepath
1982 );
1983
1984 IF p_process_flag = 'E' THEN
1985 GOTO exit_from_procedure;
1986 END IF;
1987
1988 -- Update each distribution with the threshold grp id as
1989 -- it may vary depending on the date and the group.
1990 update jai_ap_tds_prepayments
1991 set tds_threshold_grp_id = ln_threshold_grp_id
1992 where tds_prepayment_id = get_si_det.tds_prepayment_id; -- Bug 6363056
1993
1994 \* TDS invoice was generated against the Prepayment,
1995 check for what amount of the SI, TDS invoice was generated *\
1996
1997 \* Bug 6363056
1998 * Changed from p_invoice_distribution_id to get_si_det.invoice_distribution_id
1999 * as we need to generate for each line in jai_ap_tds_thhold_thhold_trxs
2000 *\
2001 open c_get_amt_tds_inv_generated_si(p_invoice_id, get_si_det.tax_dist);
2002 fetch c_get_amt_tds_inv_generated_si INTO ln_amt_tds_inv_generated_si, ln_tds_application_amt;
2003 close c_get_amt_tds_inv_generated_si;
2004
2005 \*Bug 8431516 - Start*\
2006 ln_tot_tds_amt := ln_tot_tds_amt + ln_amt_tds_inv_generated_si;
2007 ln_tot_appln_amt := ln_tot_appln_amt + ln_tds_application_amt;
2008 if p_event = 'INSERT' then
2009 update jai_ap_tds_prepayments
2010 set tds_threshold_trx_id_apply = -999
2011 where tds_prepayment_id = get_si_det.tds_prepayment_id; --Bug 6031679
2012 end if;
2013 \*Bug 8431516 - End*\
2014
2015 IF ln_amt_tds_inv_generated_si > 0 THEN
2016 IF lv_application_basis = 'STANDARD INVOICE' THEN
2017 \* get the standard invoice number *\
2018 OPEN c_si_ap_invoices_all(p_invoice_id);
2019 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
2020 CLOSE c_si_ap_invoices_all;
2021 ELSE
2022 \*Bug 8606302 - Start*\
2023 \*cursor c_ap_invoices_all would not fetch the Invoice ID if the Prepayment did not
2024 suffer TDS when it was validated initially, but only when Threshold was breached
2025 In the above case there would be no records in jai_ap_tds_inv_taxes with the distribution
2026 ID of the Prepayment Invoice*\
2027 get_prepay_invoice_id(p_prepay_distribution_id, ln_parent_invoice_id);
2028 OPEN c_si_ap_invoices_all (ln_parent_invoice_id);
2029 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
2030 CLOSE c_si_ap_invoices_all;
2031 \*Bug 8606302 - End*\
2032 END IF ; \* lv_application_basis*\
2033 end if; \* if ln_amt_tds_inv_generated_si > 0 then *\
2034 end if ;
2035
2036
2037 --Call to procedure - get_tds_threshold_slab. Store the current Threshold slab and type After PP application
2038 jai_ap_tds_generation_pkg.get_tds_threshold_slab(
2039 p_prepay_distribution_id => p_prepay_distribution_id,
2040 p_threshold_grp_id => ln_temp_threshold_grp_id,
2041 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
2042 p_threshold_slab_id => ln_after_threshold_slab_id,
2043 p_threshold_type => lv_after_threshold_type,
2044 p_process_flag => p_process_flag,
2045 p_process_message => p_process_message,
2046 p_codepath => p_codepath);
2047
2048 IF p_process_flag = 'E' THEN
2049 goto exit_from_procedure;
2050 END IF;
2051
2052 END LOOP ; \* get_si_det *\
2053 end if; \* if r_get_total_prepayment_tax.tds_amount > 0 then *\ --moved this statement from above to here for Bug 6972230
2054
2055 \*Bug 8431516 - Start*\
2056 IF ln_tot_tds_amt > 0 THEN
2057 IF lv_application_basis = 'STANDARD INVOICE' THEN
2058 \* get the standard invoice number *\
2059 OPEN c_si_ap_invoices_all(p_invoice_id);
2060 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
2061 CLOSE c_si_ap_invoices_all;
2062 ELSE
2063 \*Bug 8606302 - Start*\
2064 \*cursor c_ap_invoices_all would not fetch the Invoice ID if the Prepayment did not
2065 suffer TDS when it was validated initially, but only when Threshold was breached
2066 In the above case there would be no records in jai_ap_tds_inv_taxes with the distribution
2067 ID of the Prepayment Invoice*\
2068 get_prepay_invoice_id(p_prepay_distribution_id, ln_parent_invoice_id);
2069 OPEN c_si_ap_invoices_all (ln_parent_invoice_id);
2070 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
2071 CLOSE c_si_ap_invoices_all;
2072 \*Bug 8606302 - End*\
2073 END IF ; \* lv_application_basis*\
2074
2075 fnd_file.put_line(FND_FILE.log, ' value of dist id '|| p_invoice_distribution_id);
2076 fnd_file.put_line(FND_FILE.log, ' value of prepay dist id '|| p_prepay_distribution_id);
2077 fnd_file.put_line(FND_FILE.log, ' value of invoice id '|| ln_parent_invoice_id);
2078 fnd_file.put_line(FND_FILE.log, ' value of invoice num '||lv_invoice_num_prepay_apply);
2079
2080 if p_event = 'INSERT' then \*Bug 8431516*\
2081 \*Bug 5751783. Changed from invoice_id to ln_parent_invoice_id*\
2082 jai_ap_tds_generation_pkg.generate_tds_invoices
2083 (
2084 pn_invoice_id => ln_parent_invoice_id,
2085 pn_invoice_distribution_id => p_invoice_distribution_id,
2086 pv_invoice_num_prepay_apply => lv_invoice_num_prepay_apply,
2087 pn_taxable_amount => ln_tot_appln_amt, \*Bug 6363056*\
2088 pn_tax_amount => ln_tot_tds_amt,
2089 pn_tax_id => ln_parent_tax_id,
2090 pd_accounting_date => p_accounting_date,
2091 pv_tds_event => 'PREPAYMENT APPLICATION',
2092 pn_threshold_grp_id => ln_threshold_grp_id,
2093 pv_tds_invoice_num => lv_invoice_to_tds_num,
2094 pv_cm_invoice_num => lv_invoice_to_vendor_num,
2095 pn_threshold_trx_id => ln_threshold_trx_id_tds,
2096 pd_creation_date => p_creation_date, -- Bug 5722028. Added by Lakshmi Gopalsami
2097 pn_calc_tax_amount => 0,\* Added for bug 12965614 *\
2098 p_process_flag => p_process_flag,
2099 p_process_message => p_process_message
2100 );
2101
2102 IF p_process_flag = 'E' THEN
2103 GOTO exit_from_procedure;
2104 END IF ;
2105
2106 \* prepayment apply scenario for backward compatibility*\
2107
2108 IF ln_start_threshold_trx_id is null THEN
2109 ln_start_threshold_trx_id := ln_threshold_trx_id_tds;
2110 END IF ;
2111
2112 \* Update the threshold group *\
2113 jai_ap_tds_generation_pkg.maintain_thhold_grps
2114 ( p_threshold_grp_id => ln_threshold_grp_id,
2115 p_trx_tax_paid => (-1 * ln_tot_tds_amt),
2116 p_tds_event => 'PREPAYMENT APPLICATION',
2117 p_invoice_id => p_invoice_id,
2118 p_invoice_distribution_id => p_invoice_distribution_id,
2119 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
2120 p_creation_date => p_creation_date,
2121 p_process_flag => p_process_flag,
2122 P_process_message => p_process_message,
2123 p_codepath => p_codepath
2124 );
2125
2126 --Added by Sanjikum for Bug#4722011
2127 IF p_process_flag = 'E' THEN
2128 GOTO exit_from_procedure;
2129 END IF;
2130
2131 \* Update jai_ap_tds_prepayments with threshold_trx_id_apply*\
2132 -- Update each distribution with the threshold grp id as
2133 -- it may vary depending on the date and the group.
2134 -- changed invoice_distribution_id_prepay to invoice_distribution_id.
2135 update jai_ap_tds_prepayments
2136 set tds_threshold_trx_id_apply = ln_threshold_trx_id_tds
2137 where tds_threshold_trx_id_apply = -999
2138 and invoice_id = p_invoice_id
2139 and invoice_distribution_id_prepay = p_invoice_distribution_id; \*Bug 6363056*\
2140
2141 if p_event = 'INSERT' then --Added for Bug 8431516
2142 \* Bug 5751783
2143 * Changed from p_invoice_id to ln_parent_invoice_id
2144 * Parent invoice_id should be depending on the TDS invoice
2145 * created.
2146 *\
2147 jai_ap_tds_generation_pkg.process_threshold_rollback
2148 ( p_invoice_id => ln_parent_invoice_id,
2149 p_before_threshold_type => lv_threshold_type,
2150 p_after_threshold_type => lv_after_threshold_type,
2151 p_before_threshold_slab_id => ln_threshold_slab_id,
2152 p_after_threshold_slab_id => ln_after_threshold_slab_id,
2153 p_threshold_grp_id => ln_temp_threshold_grp_id,
2154 p_org_id => p_org_id,
2155 p_accounting_date => p_accounting_date,
2156 p_invoice_distribution_id => p_invoice_distribution_id,
2157 p_prepay_distribution_id => p_prepay_distribution_id,
2158 p_process_flag => p_process_flag,
2159 p_process_message => p_process_message,
2160 p_codepath => p_codepath);
2161
2162 IF p_process_flag = 'E' THEN
2163 goto exit_from_procedure;
2164 END IF;
2165 end if; \*if p_event = 'INSERT' then*\ --Added for Bug 8431516
2166
2167 end if; \*if p_event = 'INSERT' then*\ --Added for Bug 8431516
2168 end if ; \* IF ln_tot_tds_amt > 0 THEN *\
2169 \*Bug 8431516 - End*\
2170
2171 \* prepayment apply scenario for backward compatibility*\
2172 update JAI_AP_TDS_INVOICES
2173 set amt_reversed = nvl(amt_reversed, 0) + r_get_total_prepayment_tax.tds_amount,
2174 amt_applied = nvl(amt_applied, 0) + abs(p_prepay_amount)
2175 where invoice_id = p_invoice_id;
2176
2177 -- End for bug 6363056.
2178
2179 if r_get_total_prepayment_tax.wct_amount > 0 then
2180 \* get the tax_id *\
2181 ln_pp_section_tax_id := null;
2182 ln_parent_invoice_id := null;
2183
2184 \*Bug 6363056*\
2185 if lv_application_basis = 'STANDARD INVOICE' then
2186 \* get the standard invoice number *\
2187 ln_parent_invoice_id := p_invoice_id;
2188 else
2189 \* Get the prepayment number *\
2190 open c_get_pp_section_tax_id(p_prepay_distribution_id, 'WCT_SECTION');
2191 fetch c_get_pp_section_tax_id into ln_pp_section_tax_id, ln_parent_invoice_id;
2192 close c_get_pp_section_tax_id;
2193 end if;
2194
2195 \*Bug 6363056*\
2196 IF nvl(ln_pp_section_tax_id,-1) <> ln_si_wct_tax_id THEN
2197 ln_pp_section_tax_id := ln_si_wct_tax_id;
2198 END IF ;
2199
2200 if p_event = 'INSERT' then --Added for Bug 8431516
2201 \*Bug 5751783 - Changed from p_invoice_id to ln_pp_section_invoice_id*\
2202 jai_ap_tds_generation_pkg.generate_tds_invoices
2203 (
2204 pn_invoice_id => ln_parent_invoice_id ,
2205 pn_invoice_distribution_id => p_invoice_distribution_id ,
2206 pn_taxable_amount => r_get_total_prepayment_tax.wct_taxable_basis ,
2207 pn_tax_amount => r_get_total_prepayment_tax.wct_amount ,
2208 pn_tax_id => ln_pp_section_tax_id ,
2209 pd_accounting_date => p_accounting_date ,
2210 pv_tds_event => 'PREPAYMENT APPLICATION' ,
2211 pn_threshold_grp_id => null ,
2212 pv_tds_invoice_num => lv_invoice_to_tds_num ,
2213 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
2214 pn_threshold_trx_id => ln_threshold_trx_id_wct ,
2215 pd_creation_date => p_creation_date ,
2216 pn_calc_tax_amount => 0, \*Added for bug 12965614 *\
2217 p_process_flag => p_process_flag ,
2218 p_process_message => p_process_message
2219 );
2220
2221 if p_process_flag = 'E' then
2222 goto exit_from_procedure;
2223 end if;
2224
2225 update jai_ap_tds_prepayments
2226 set wct_threshold_trx_id_apply = ln_threshold_trx_id_wct
2227 where invoice_id = p_invoice_id
2228 and invoice_distribution_id_prepay = p_invoice_distribution_id
2229 and wct_applicable_flag = 'Y';
2230
2231 if ln_start_threshold_trx_id is null then
2232 ln_start_threshold_trx_id := ln_threshold_trx_id_wct;
2233 end if;
2234 end if; \*if p_event = 'INSERT' then*\ --Added for Bug 8431516
2235 \* Generate the return invoices *\
2236 end if; \* if r_get_total_prepayment_tax.wct_amount > 0 then *\
2237
2238
2239 if r_get_total_prepayment_tax.essi_amount > 0 then
2240 \* get the tax_id *\
2241 ln_pp_section_tax_id := null;
2242 ln_parent_invoice_id := null;
2243 \*Bug 6363056*\
2244 if lv_application_basis = 'STANDARD INVOICE' then
2245 \* get the standard invoice number *\
2246 ln_parent_invoice_id := p_invoice_id;
2247 else
2248 \* Get the prepayment number *\
2249 open c_get_pp_section_tax_id(p_prepay_distribution_id, 'WCT_SECTION');
2250 fetch c_get_pp_section_tax_id into ln_pp_section_tax_id, ln_parent_invoice_id;
2251 close c_get_pp_section_tax_id;
2252 end if;
2253
2254 \*Bug 6363056*\
2255 IF nvl(ln_pp_section_tax_id,-1) <> ln_si_essi_tax_id THEN
2256 ln_pp_section_tax_id := ln_si_essi_tax_id;
2257 END IF ;
2258
2259 IF p_event = 'INSERT' then --Added for Bug 8431516
2260 \*Bug 5751783 - Changed from p_invoice_id to ln_pp_section_invoice_id*\
2261 jai_ap_tds_generation_pkg.generate_tds_invoices
2262 (
2263 pn_invoice_id => ln_parent_invoice_id ,
2264 pn_invoice_distribution_id => p_invoice_distribution_id ,
2265 pn_taxable_amount => r_get_total_prepayment_tax.essi_taxable_basis ,
2266 pn_tax_amount => r_get_total_prepayment_tax.essi_amount ,
2267 pn_tax_id => ln_pp_section_tax_id ,
2268 pd_accounting_date => p_accounting_date ,
2269 pv_tds_event => 'PREPAYMENT APPLICATION' ,
2270 pn_threshold_grp_id => null ,
2271 pv_tds_invoice_num => lv_invoice_to_tds_num ,
2272 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
2273 pn_threshold_trx_id => ln_threshold_trx_id_essi ,
2274 pd_creation_date => p_creation_date ,
2275 pn_calc_tax_amount => 0, \*Added for bug 12965614 *\
2276 p_process_flag => p_process_flag ,
2277 p_process_message => p_process_message
2278 );
2279
2280 if p_process_flag = 'E' then
2281 goto exit_from_procedure;
2282 end if;
2283
2284 update jai_ap_tds_prepayments
2285 set essi_threshold_trx_id_apply = ln_threshold_trx_id_essi
2286 where invoice_id = p_invoice_id
2287 and invoice_distribution_id_prepay = p_invoice_distribution_id
2288 and essi_applicable_flag = 'Y';
2289
2290 if ln_start_threshold_trx_id is null then
2291 ln_start_threshold_trx_id := ln_threshold_trx_id_essi;
2292 end if;
2293 end if; \*IF p_event = 'INSERT' then*\ --Added for Bug 8431516
2294 end if; \* if r_get_total_prepayment_tax.essi_amount > 0 then *\
2295
2296 if ln_start_threshold_trx_id is not null then
2297 \*Bug - 9826422
2298 Records are inserted into AP Interface tables using Standard Invoice, but import_and_approve
2299 was called using the Prepayment Invoice ID. Hence wrong group_id was getting passed and no
2300 Invoices were getting improved*\
2301 jai_ap_tds_generation_pkg.import_and_approve
2302 (
2303 p_invoice_id => ln_parent_invoice_id,
2304 p_start_thhold_trx_id => ln_start_threshold_trx_id,
2305 p_tds_event => 'PREPAYMENT APPLICATION',
2306 p_process_flag => p_process_flag,
2307 p_process_message => p_process_message
2308 );
2309
2310 end if;
2311
2312 << exit_from_procedure >>
2313 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); \* 100 *\
2314 return;
2315
2316 exception
2317 when others then
2318 p_process_flag := 'E';
2319 P_process_message := 'jai_ap_tds_prepayemnts_pkg.process_tds_invoices :' || sqlerrm;
2320 return;
2321 end process_tds_invoices;
2322
2323 \***********************************************************************************************\
2324 */ --Commented by Chong for DTC ER END 20121011
2325
2326
2327 /* **************************************** process_old_transaction **************************************** */
2328
2329 procedure process_old_transaction
2330 (
2331 p_invoice_id in number,
2332 p_invoice_distribution_id in number,
2333 p_prepay_distribution_id in number,
2334 p_amount in number,
2335 p_last_updated_by in number,
2336 p_last_update_date in date,
2337 p_created_by in number,
2338 p_creation_date in date,
2339 p_org_id in number,
2340 p_process_flag out nocopy varchar2,
2341 p_process_message out nocopy varchar2
2342 )
2343 is
2344
2345 cursor c_tds_count(p_invoice_id number, p_source_attribute varchar2) is
2346 select count(1)
2347 from JAI_AP_TDS_INVOICES
2348 where invoice_id = p_invoice_id
2349 and source_attribute = p_source_attribute;
2350
2351 /* Following cursor definition has been changed to cater for the obsoletion of table ja_in_ap_tds_inv_temp */
2352 cursor c_tds_count_unapp(p_invoice_id number, p_section_type varchar2) IS --rchandan for bug#4428980
2353 select count(1)
2354 from jai_ap_tds_inv_taxes
2355 where invoice_id = p_invoice_id
2356 and section_type = p_section_type; --rchandan for bug#4428980
2357
2358 ln_tds_count_attribute1 number;
2359 ln_tds_count_attribute2 number;
2360 ln_tds_count_attribute3 number;
2361 lb_result boolean;
2362 ln_req_id number;
2363
2364 begin
2365 /* This code is to replace the following three triggers of the old tds regime
2366 1. ja_in_prepay_insert_trg
2367 2. ja_in_prepay_insert_wct_trg
2368 3. ja_in_prepay_insert_wct1_trg
2369 */
2370
2371 open c_tds_count(p_invoice_id, 'ATTRIBUTE1');
2372 fetch c_tds_count into ln_tds_count_attribute1;
2373 close c_tds_count;
2374
2375 if ln_tds_count_attribute1 = 0 then
2376 -- the standard invoice does not have tds attached to it.
2377 -- check if there is TDS record in temp table, this would happen when the invoice is unapproved.
2378 -- Bug 4754213. Added by Lakshmi Gopalsami
2379 open c_tds_count_unapp(p_invoice_id, 'TDS_SECTION'); --rchandan for bug#4428980
2380 fetch c_tds_count_unapp into ln_tds_count_attribute1;
2381 close c_tds_count_unapp;
2382
2383 ln_tds_count_attribute1 := nvl(ln_tds_count_attribute1, 0);
2384
2385 if ln_tds_count_attribute1 = 0 then
2386 goto attribut2_processing;
2387 end if;
2388
2389 end if;
2390
2391
2392 if p_amount < 0 then /* Case of Apply */
2393
2394 lb_result := fnd_request.set_mode(TRUE);
2395 ln_req_id :=
2396 fnd_request.submit_request
2397 (
2398 'JA',
2399 'JAINPREP',
2400 'To Insert Prepayment Distributions',
2401 '',
2402 FALSE,
2403 p_invoice_id,
2404 p_invoice_distribution_id,
2405 abs(p_amount),
2406 p_last_updated_by,
2407 p_last_update_date,
2408 p_created_by ,
2409 p_creation_date,
2410 p_org_id,
2411 p_prepay_distribution_id,
2412 'I',
2413 'ATTRIBUTE1'
2414 );
2415
2416 elsif p_amount > 0 then
2417
2418 lb_result := fnd_request.set_mode(TRUE);
2419 ln_req_id :=
2420 fnd_request.submit_request
2421 (
2422 'JA',
2423 'JAINUNPR',
2424 'To Unapply Prepayment Distributions',
2425 '',
2426 FALSE,
2427 p_invoice_id,
2428 p_last_updated_by,
2429 p_last_update_date,
2430 p_created_by ,
2431 p_creation_date,
2432 p_org_id,
2433 p_prepay_distribution_id,
2434 p_invoice_distribution_id,
2435 'ATTRIBUTE1'
2436 );
2437
2438 end if;
2439
2440 /* Check for WCT tax */
2441 << attribut2_processing >>
2442 open c_tds_count(p_invoice_id, 'ATTRIBUTE2');
2443 fetch c_tds_count into ln_tds_count_attribute2;
2444 close c_tds_count;
2445
2446 if ln_tds_count_attribute2 = 0 then
2447 -- the standard invoice does not have tds attached to it.
2448 -- check if there is TDS record in temp table, this would happen when the invoice is unapproved.
2449 open c_tds_count_unapp(p_invoice_id, 'WCT_SECTION');
2450 fetch c_tds_count_unapp into ln_tds_count_attribute2;
2451 close c_tds_count_unapp;
2452
2453 ln_tds_count_attribute2 := nvl(ln_tds_count_attribute2, 0);
2454
2455 if ln_tds_count_attribute2 = 0 then
2456 goto attribut3_processing;
2457 end if;
2458
2459 end if;
2460
2461
2462 if p_amount < 0 then /* Case of Apply */
2463
2464 lb_result := fnd_request.set_mode(TRUE);
2465 ln_req_id :=
2466 fnd_request.submit_request
2467 (
2468 'JA',
2469 'JAINPREP',
2470 'To Insert Prepayment Distributions',
2471 '',
2472 FALSE,
2473 p_invoice_id,
2474 p_invoice_distribution_id,
2475 abs(p_amount),
2476 p_last_updated_by,
2477 p_last_update_date,
2478 p_created_by ,
2479 p_creation_date,
2480 p_org_id,
2481 p_prepay_distribution_id,
2482 'I',
2483 'ATTRIBUTE2'
2484 );
2485
2486 elsif p_amount > 0 then
2487
2488 lb_result := fnd_request.set_mode(TRUE);
2489 ln_req_id :=
2490 fnd_request.submit_request
2491 (
2492 'JA',
2493 'JAINUNPR',
2494 'To Unapply Prepayment Distributions',
2495 '',
2496 FALSE,
2497 p_invoice_id,
2498 p_last_updated_by,
2499 p_last_update_date,
2500 p_created_by ,
2501 p_creation_date,
2502 p_org_id,
2503 p_prepay_distribution_id,
2504 p_invoice_distribution_id,
2505 'ATTRIBUTE2'
2506 );
2507
2508 end if;
2509
2510 /* Check for ESSI Tax */
2511 << attribut3_processing >>
2512 open c_tds_count(p_invoice_id, 'ATTRIBUTE3');
2513 fetch c_tds_count into ln_tds_count_attribute3;
2514 close c_tds_count;
2515
2516 if ln_tds_count_attribute3 = 0 then
2517 -- the standard invoice does not have tds attached to it.
2518 -- check if there is TDS record in temp table, this would happen when the invoice is unapproved.
2519 open c_tds_count_unapp(p_invoice_id, 'ESSI_SECTION');
2520 fetch c_tds_count_unapp into ln_tds_count_attribute3;
2521 close c_tds_count_unapp;
2522
2523 ln_tds_count_attribute3 := nvl(ln_tds_count_attribute3, 0);
2524
2525 if ln_tds_count_attribute3 = 0 then
2526 goto exit_from_procedure;
2527 end if;
2528
2529 end if;
2530
2531
2532 if p_amount < 0 then /* Case of Apply */
2533
2534 lb_result := fnd_request.set_mode(TRUE);
2535 ln_req_id :=
2536 fnd_request.submit_request
2537 (
2538 'JA',
2539 'JAINPREP',
2540 'To Insert Prepayment Distributions',
2541 '',
2542 FALSE,
2543 p_invoice_id,
2544 p_invoice_distribution_id,
2545 abs(p_amount),
2546 p_last_updated_by,
2547 p_last_update_date,
2548 p_created_by ,
2549 p_creation_date,
2550 p_org_id,
2551 p_prepay_distribution_id,
2552 'I',
2553 'ATTRIBUTE3'
2554 );
2555
2556 elsif p_amount > 0 then
2557
2558 lb_result := fnd_request.set_mode(TRUE);
2559 ln_req_id :=
2560 fnd_request.submit_request
2561 (
2562 'JA',
2563 'JAINUNPR',
2564 'To Unapply Prepayment Distributions',
2565 '',
2566 FALSE,
2567 p_invoice_id,
2568 p_last_updated_by,
2569 p_last_update_date,
2570 p_created_by ,
2571 p_creation_date,
2572 p_org_id,
2573 p_prepay_distribution_id,
2574 p_invoice_distribution_id,
2575 'ATTRIBUTE3'
2576 );
2577
2578 end if;
2579
2580 << exit_from_procedure >>
2581 return;
2582
2583 exception
2584 when others then
2585 p_process_flag := 'E';
2586 P_process_message := 'jai_ap_dtc_prepayemnts_pkg.process_old_transaction :' || sqlerrm;
2587 return;
2588 end process_old_transaction;
2589
2590 /* **************************************** process_old_transaction **************************************** */
2591 /*-------------------------------------------------------------------------------------------------------------------------------+
2592 | Created By : WenqiongZhou |
2593 | Creation Date : 25/Dec/2011 |
2594 | Bug Number/ER Name : DTC |
2595 | SubProgram Name : allocate_prepay_section |
2596 | Type : PROCEDURE |
2597 | Purpose : Merge procedures allocate_prepayment and populate_section_tax into a single procedure |
2598 | TDD Reference : Section 16 |
2599 | Assumptions : |
2600 | Called From : jai_ap_tds_prepayments_pkg.process_prepayment |
2601 |--------------------------------------------------------------------------------------------------------------------------------|
2602 | parameters IN/OUT Type Required Description and Purpose |
2603 | ------------ -------- ------ ---------- ------------------------- |
2604 | p_invoice_id IN NUMBER yes invoice id |
2605 | p_invoice_distribution_id IN NUMBER yes invoice distribution id |
2606 | p_prepay_amount IN NUMBER yes prepay amount |
2607 | p_prepay_distribution_id IN NUMBER yes prepay invoice distribution id |
2608 | p_process_flag OUT VARCHAR2 yes return process result |
2609 | p_process_message OUT VARCHAR2 yes return process message |
2610 ---------------------------------------------------------------------------------------------------------------------------------*/
2611
2612 procedure allocate_prepay_section
2613 (
2614 p_invoice_id in number,
2615 p_invoice_distribution_id in number, /* Of the PREPAY line */
2616 p_prepay_amount in number,
2617 p_prepay_distribution_id in number, /* Distribution id of the PP invoice */
2618 p_process_flag out nocopy varchar2,
2619 p_process_message out nocopy varchar2,
2620 p_codepath in out nocopy varchar2
2621 ) IS
2622 --cursor get applied taxes
2623 CURSOR C_JAI_AP_TDS_INV_TAXES
2624 (CN_INVOICE_ID NUMBER,
2625 CN_PREPAY_DISTRIBUTION_ID NUMBER,
2626 CV_TDS_SECTION_CODE VARCHAR2,
2627 CV_WCT_APPLICABLE VARCHAR2,
2628 CV_ESSI_APPLICABLE VARCHAR2
2629 )
2630 IS
2631 SELECT INVOICE_DISTRIBUTION_ID,
2632 AMOUNT,
2633 INVOICE_LINE_NUMBER,
2634 INVOICE_ID,
2635 (SELECT DISTINCT ACTUAL_SECTION_CODE
2636 FROM JAI_AP_TDS_INV_TAXES
2637 WHERE INVOICE_ID = CN_INVOICE_ID AND
2638 INVOICE_DISTRIBUTION_ID = JATIT.INVOICE_DISTRIBUTION_ID AND
2639 ACTUAL_SECTION_CODE IS NOT NULL AND
2640 SECTION_TYPE = 'TDS_SECTION' AND
2641 ACTUAL_SECTION_CODE = CV_TDS_SECTION_CODE AND
2642 ROWNUM = 1) TDS_SECTION_CODE,
2643 wct_essi_applicable(CN_INVOICE_ID,JATIT.INVOICE_DISTRIBUTION_ID,'WCT_SECTION') WCT_APPLICABLE,
2644 wct_essi_applicable(CN_INVOICE_ID,JATIT.INVOICE_DISTRIBUTION_ID,'ESSI_SECTION') ESSI_APPLICABLE
2645 FROM JAI_AP_TDS_INV_TAXES JATIT
2646 WHERE INVOICE_ID = CN_INVOICE_ID AND
2647 INVOICE_DISTRIBUTION_ID <> CN_PREPAY_DISTRIBUTION_ID AND
2648 --ACTUAL_TAX_ID IS NOT NULL AND --Commented by Zhiwei Hou on 20120116
2649 (wct_essi_applicable(CN_INVOICE_ID,JATIT.INVOICE_DISTRIBUTION_ID,'WCT_SECTION') = CV_WCT_APPLICABLE OR
2650 CV_WCT_APPLICABLE IS NULL ) AND
2651 (wct_essi_applicable(CN_INVOICE_ID,JATIT.INVOICE_DISTRIBUTION_ID,'ESSI_SECTION') = CV_ESSI_APPLICABLE OR
2652 CV_ESSI_APPLICABLE IS NULL ) AND
2653 INVOICE_DISTRIBUTION_ID <> 1 AND -- Added by Chong for bug#13802244 2012/09/19
2654 AMOUNT > 0;
2655 --cursor get gl date of invoice
2656 CURSOR c_gl_date(cn_invoice_id NUMBER) IS
2657 SELECT gl_date
2658 FROM ap_invoices_all
2659 WHERE invoice_id = cn_invoice_id;
2660 --cursor get section code of prepay distribution id
2661 CURSOR c_section_code IS
2662 SELECT distinct actual_section_code
2663 FROM jai_ap_tds_inv_taxes
2664 WHERE invoice_distribution_id = p_prepay_distribution_id;
2665
2666 CURSOR c_applicable (cn_section_type Varchar2,cn_dist_id NUMBER) IS
2667 SELECT 'Y'
2668 FROM JAI_AP_TDS_INV_TAXES
2669 WHERE INVOICE_DISTRIBUTION_ID = cn_dist_id AND
2670 SECTION_TYPE = cn_section_type;
2671
2672 CURSOR c_applied_amount(cn_invoice_distribution_id NUMBER) IS
2673 SELECT nvl(sum(application_amount),0)
2674 FROM JAI_AP_TDS_PREPAYMENTS
2675 WHERE invoice_distribution_id = cn_invoice_distribution_id
2676 AND nvl(unapply_flag, 'N') <> 'Y';
2677
2678 cursor c_get_effective_available_amt(cn_invoice_id number, cn_invoice_line_num number) is
2679 select sum(amount) amount
2680 from jai_ap_tds_inv_taxes
2681 where invoice_id = cn_invoice_id
2682 and invoice_line_number = cn_invoice_line_num
2683 and amount < 0;
2684
2685 ln_remaining_prepayment_amount NUMBER;
2686 ln_prepay_invoice_id NUMBER;
2687 ld_prepay_gl_date DATE;
2688 ld_standard_gl_date DATE;
2689 lv_application_basis VARCHAR2(50);
2690 lv_ppay_tds_section_code VARCHAR2(100);
2691 lv_ppay_wct_applicable VARCHAR2(1);
2692 lv_ppay_essi_applicable VARCHAR2(1);
2693 lv_wct_applicable VARCHAR2(1);
2694 lv_essi_applicable VARCHAR2(1);
2695 ln_effective_available_amount number;
2696 ln_already_applied_amount number;
2697 ln_application_amount number;
2698 ln_less_amount number;
2699 lv_tds_applicable VARCHAR2(1);
2700 l_api_name CONSTANT VARCHAR2(50) := 'allocate_prepay_section()';
2701
2702
2703 BEGIN
2704 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
2705 ln_remaining_prepayment_amount := abs(p_prepay_amount);
2706
2707 get_prepay_invoice_id(p_prepay_distribution_id,ln_prepay_invoice_id);
2708 get_prepay_invoice_gl_date(p_prepay_distribution_id,ld_prepay_gl_date);
2709
2710 OPEN c_gl_date(p_invoice_id);
2711 FETCH c_gl_date INTO ld_standard_gl_date;
2712 CLOSE c_gl_date;
2713
2714 IF ld_prepay_gl_date > ld_standard_gl_date THEN
2715 lv_application_basis := 'PREPAYMENT';
2716 ELSE
2717 lv_application_basis := 'STANDARD';
2718 END IF;
2719
2720 OPEN c_section_code;
2721 FETCH c_section_code INTO lv_ppay_tds_section_code;
2722 CLOSE c_section_code;
2723
2724 --Added by Zhiwei Hou for DTC migration Bug#13359892 on 20120309 begin
2725 lv_ppay_tds_section_code := jai_ap_utils_pkg.get_sec_code_mapping(lv_ppay_tds_section_code);
2726 --Added by Zhiwei Hou for DTC migration Bug#13359892 on 20120309 end
2727
2728 OPEN c_applicable('WCT_SECTION',p_prepay_distribution_id);
2729 FETCH c_applicable INTO lv_ppay_wct_applicable;
2730 CLOSE c_applicable;
2731
2732 OPEN c_applicable('ESSI_SECTION',p_prepay_distribution_id);
2733 FETCH c_applicable INTO lv_ppay_essi_applicable;
2734 CLOSE c_applicable;
2735
2736 lv_ppay_wct_applicable := nvl(lv_ppay_wct_applicable,'N');
2737 lv_ppay_essi_applicable := nvl(lv_ppay_essi_applicable,'N');
2738
2739 /*Ensure allotment is first made to distributions that match across TDS, WCT and ESSI*/
2740 FOR cur_si_distributions_rec
2741 IN c_jai_ap_tds_inv_taxes(p_invoice_id,
2742 p_invoice_distribution_id,
2743 lv_ppay_tds_section_code,
2744 lv_ppay_wct_applicable,
2745 lv_ppay_essi_applicable)
2746 LOOP
2747 ln_already_applied_amount:= 0;
2748 ln_effective_available_amount := 0;
2749 ln_application_amount := 0;
2750
2751 OPEN c_applied_amount(cur_si_distributions_rec.invoice_distribution_id);
2752 FETCH c_applied_amount INTO ln_already_applied_amount;
2753 CLOSE c_applied_amount;
2754
2755 open c_get_effective_available_amt(cur_si_distributions_rec.invoice_id, cur_si_distributions_rec.invoice_line_number);
2756 fetch c_get_effective_available_amt into ln_less_amount;
2757 close c_get_effective_available_amt;
2758
2759 ln_less_amount := nvl(ln_less_amount, 0);
2760 ln_effective_available_amount := cur_si_distributions_rec.amount - ln_already_applied_amount - abs(ln_less_amount);
2761 ln_application_amount := least(ln_remaining_prepayment_amount, ln_effective_available_amount);
2762
2763 IF cur_si_distributions_rec.wct_applicable ='Y' AND lv_ppay_wct_applicable ='Y' THEN
2764 lv_wct_applicable := 'Y';
2765 ELSE
2766 lv_wct_applicable := 'N';
2767 END IF;
2768
2769
2770 IF cur_si_distributions_rec.essi_applicable ='Y' AND lv_ppay_essi_applicable ='Y' THEN
2771 lv_essi_applicable := 'Y';
2772 ELSE
2773 lv_essi_applicable := 'N';
2774 END IF;
2775
2776
2777 IF (cur_si_distributions_rec.tds_section_code <> lv_ppay_tds_section_code
2778 OR (cur_si_distributions_rec.tds_section_code IS NULL OR lv_ppay_tds_section_code IS NULL )
2779 ) AND NVL(jai_populate_attribute.is_legacy_invoice(ln_prepay_invoice_id), 'N') = 'N' THEN
2780 /* Added above AND condition for bug 16626598 -
2781 This validation for legacy invoice is checked only if the Client Extension in jai_populate_attribute.is_legacy_invoice function enable_customization is set to Y */
2782
2783 -- p_process_flag := 'E';
2784 p_process_message := 'Cannot Apply Prepayment across TDS Section Codes';
2785
2786 ELSIF cur_si_distributions_rec.tds_section_code = lv_ppay_tds_section_code THEN
2787 lv_tds_applicable := 'Y';
2788 END IF;
2789
2790 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'Loop1 ln_application_amount:' || ln_application_amount);
2791
2792 if ln_application_amount > 0 AND nvl(lv_tds_applicable,'N') = 'Y' then
2793 insert into jai_ap_tds_prepayments
2794 (
2795 tds_prepayment_id ,
2796 invoice_id ,
2797 invoice_distribution_id_prepay ,
2798 invoice_distribution_id ,
2799 application_amount ,
2800 application_basis ,
2801 tds_section_code_prepay ,
2802 tds_section_code_other ,
2803 tds_applicable_flag ,
2804 wct_applicable_flag ,
2805 essi_applicable_flag ,
2806 created_by ,
2807 creation_date ,
2808 last_updated_by ,
2809 last_update_date ,
2810 last_update_login
2811 )
2812 values
2813 (
2814 jai_ap_tds_prepayments_s.nextval ,
2815 p_invoice_id ,
2816 p_invoice_distribution_id ,
2817 cur_si_distributions_rec.invoice_distribution_id ,
2818 ln_application_amount ,
2819 lv_application_basis ,
2820 lv_ppay_tds_section_code ,
2821 cur_si_distributions_rec.tds_section_code ,
2822 lv_tds_applicable ,
2823 lv_wct_applicable ,
2824 lv_essi_applicable ,
2825 fnd_global.user_id ,
2826 sysdate ,
2827 fnd_global.user_id ,
2828 sysdate ,
2829 fnd_global.login_id
2830 );
2831
2832 ln_remaining_prepayment_amount := ln_remaining_prepayment_amount - ln_application_amount;
2833 END IF;
2834 END LOOP; /* cur_si_distributions_rec in c_jai_ap_tds_inv_taxes */
2835
2836 /*Ensure allotment is first made to distributions that match only to TDS*/
2837 FOR cur_si_distributions_rec
2838 IN c_jai_ap_tds_inv_taxes(p_invoice_id,
2839 p_invoice_distribution_id,
2840 lv_ppay_tds_section_code,
2841 NULL,
2842 NULL)
2843 LOOP
2844 ln_already_applied_amount:= 0;
2845 ln_effective_available_amount := 0;
2846 ln_application_amount := 0;
2847
2848 OPEN c_applied_amount(cur_si_distributions_rec.invoice_distribution_id);
2849 FETCH c_applied_amount INTO ln_already_applied_amount;
2850 CLOSE c_applied_amount;
2851
2852 open c_get_effective_available_amt(cur_si_distributions_rec.invoice_id, cur_si_distributions_rec.invoice_line_number);
2853 fetch c_get_effective_available_amt into ln_less_amount;
2854 close c_get_effective_available_amt;
2855
2856 ln_less_amount := nvl(ln_less_amount, 0);
2857 ln_effective_available_amount := cur_si_distributions_rec.amount - ln_already_applied_amount - abs(ln_less_amount);
2858 ln_application_amount := least(ln_remaining_prepayment_amount, ln_effective_available_amount);
2859
2860 IF cur_si_distributions_rec.wct_applicable ='Y' AND lv_ppay_wct_applicable ='Y' THEN
2861 lv_wct_applicable := 'Y';
2862 ELSE
2863 lv_wct_applicable := 'N';
2864 END IF;
2865
2866 IF cur_si_distributions_rec.essi_applicable ='Y' AND lv_ppay_essi_applicable ='Y' THEN
2867 lv_essi_applicable := 'Y';
2868 ELSE
2869 lv_essi_applicable := 'N';
2870 END IF;
2871
2872 IF (cur_si_distributions_rec.tds_section_code <> lv_ppay_tds_section_code
2873 OR (cur_si_distributions_rec.tds_section_code IS NULL OR lv_ppay_tds_section_code IS NULL )
2874 ) AND NVL(jai_populate_attribute.is_legacy_invoice(ln_prepay_invoice_id), 'N') = 'N' THEN
2875 /* Added above AND condition for bug 16626598 -
2876 This validation for legacy invoice is checked only if the Client Extension in jai_populate_attribute.is_legacy_invoice function enable_customization is set to Y */
2877
2878 --p_process_flag := 'E';
2879 p_process_message := 'Cannot Apply Prepayment across TDS Section Codes';
2880
2881
2882 ELSIF cur_si_distributions_rec.tds_section_code = lv_ppay_tds_section_code THEN
2883 lv_tds_applicable := 'Y';
2884 END IF;
2885
2886 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'Loop2 ln_application_amount:' || ln_application_amount);
2887 if ln_application_amount > 0 AND nvl(lv_tds_applicable,'N') = 'Y' then
2888 insert into jai_ap_tds_prepayments
2889 (
2890 tds_prepayment_id ,
2891 invoice_id ,
2892 invoice_distribution_id_prepay ,
2893 invoice_distribution_id ,
2894 application_amount ,
2895 application_basis ,
2896 tds_section_code_prepay ,
2897 tds_section_code_other ,
2898 tds_applicable_flag ,
2899 wct_applicable_flag ,
2900 essi_applicable_flag ,
2901 created_by ,
2902 creation_date ,
2903 last_updated_by ,
2904 last_update_date ,
2905 last_update_login
2906 )
2907 values
2908 (
2909 jai_ap_tds_prepayments_s.nextval ,
2910 p_invoice_id ,
2911 p_invoice_distribution_id ,
2912 cur_si_distributions_rec.invoice_distribution_id ,
2913 ln_application_amount ,
2914 lv_application_basis ,
2915 lv_ppay_tds_section_code ,
2916 cur_si_distributions_rec.tds_section_code ,
2917 lv_tds_applicable ,
2918 lv_wct_applicable ,
2919 lv_essi_applicable ,
2920 fnd_global.user_id ,
2921 sysdate ,
2922 fnd_global.user_id ,
2923 sysdate ,
2924 fnd_global.login_id
2925 );
2926
2927 ln_remaining_prepayment_amount := ln_remaining_prepayment_amount - ln_application_amount;
2928
2929 END IF;
2930 END LOOP; /* cur_si_distributions_rec in c_jai_ap_tds_inv_taxes */
2931
2932 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.END', G_PKG_NAME || ': '||l_api_name||'()-');
2933 IF ln_remaining_prepayment_amount > 0 THEN
2934 p_process_flag := 'E';
2935 p_process_message := 'Cannot Apply Prepayment across TDS Section Codes';
2936
2937 END IF;
2938
2939 exception
2940 when others then
2941 p_process_flag := 'E';
2942 P_process_message := 'jai_ap_dtc_prepayments_pkg.allocate_prepay_section :' || sqlerrm;
2943 return;
2944 END allocate_prepay_section;
2945 /*-------------------------------------------------------------------------------------------------------------------------------+
2946 | Created By : WenqiongZhou |
2947 | Creation Date : 25/Dec/2011 |
2948 | Bug Number/ER Name : DTC |
2949 | SubProgram Name : generate_rtn |
2950 | Type : PROCEDURE |
2951 | Purpose : Group by section code and generate RTN invoice |
2952 | TDD Reference : Section 16 |
2953 | Assumptions : |
2954 | Called From : jai_ap_tds_prepayments_pkg.process_prepayment |
2955 |--------------------------------------------------------------------------------------------------------------------------------|
2956 | parameters IN/OUT Type Required Description and Purpose |
2957 | ------------ -------- ------ ---------- ------------------------- |
2958 | p_event IN VARCHAR2 yes event
2959 | p_invoice_id IN NUMBER yes invoice id |
2960 | p_invoice_distribution_id IN NUMBER yes invoice distribution id |
2961 | p_prepay_amount IN NUMBER yes prepay amount |
2962 | p_prepay_distribution_id IN NUMBER yes prepay invoice distribution id |
2963 | p_accounting_date IN DATE yes accounting date |
2964 | p_invoice_currency_code IN varchar2 yes invoice currency code |
2965 | p_exchange_rate IN NUMBER yes currency exchange rate |
2966 | p_set_of_books_id IN NUMBER yes set of book id |
2967 | p_org_id IN NUMBER yes org id |
2968 | p_creation_date IN DATE yes creation date |
2969 | p_process_flag OUT VARCHAR2 yes return process result |
2970 | p_process_message OUT VARCHAR2 yes return process message |
2971 ---------------------------------------------------------------------------------------------------------------------------------*/
2972 procedure generate_rtn
2973 (
2974 p_event in varchar2,
2975 p_invoice_id in number,
2976 p_invoice_distribution_id in number,
2977 p_prepay_distribution_id in number,
2978 p_prepay_amount in number,
2979 p_accounting_date in date,
2980 p_invoice_currency_code in varchar2,
2981 p_exchange_rate in number,
2982 p_set_of_books_id in number,
2983 p_org_id in number,
2984 p_creation_date in date,
2985 p_process_flag out nocopy varchar2,
2986 p_process_message out nocopy varchar2,
2987 p_codepath in out nocopy varchar2
2988 )
2989 is
2990
2991 cursor c_gl_sets_of_books(cp_set_of_books_id number) is
2992 select currency_code
2993 from gl_sets_of_books
2994 where set_of_books_id = cp_set_of_books_id;
2995
2996 cursor c_jai_ap_tds_prepayments(p_invoice_id number, p_invoice_distribution_id number) is
2997 select tds_prepayment_id,
2998 invoice_distribution_id,
2999 application_amount,
3000 application_basis,
3001 tds_section_code_prepay,
3002 tds_section_code_other,
3003 tds_applicable_flag,
3004 wct_applicable_flag,
3005 essi_applicable_flag
3006 from jai_ap_tds_prepayments
3007 where invoice_id = p_invoice_id
3008 and invoice_distribution_id_prepay = p_invoice_distribution_id;
3009
3010 CURSOR c_jai_tds_tax(cn_invoice_distribution_id number, cv_section_code VARCHAR2, cv_section_type VARCHAR2) IS
3011 SELECT SUM(nvl(tax_amount,0))
3012 FROM jai_ap_tds_inv_taxes
3013 --WHERE invoice_distribution_id = cn_invoice_distribution_id --Commented by Chong for issue120920-66 2012/10/09
3014 WHERE (invoice_distribution_id = cn_invoice_distribution_id --Added by Chong for issue120920-66 2012/10/09
3015 OR invoice_distribution_id = 1) --Added by Chong for issue120920-66 2012/10/09
3016 AND invoice_id = p_invoice_id --Added by Chong for issue120920-66 2012/10/09
3017 AND ACTUAL_SECTION_CODE = cv_section_code
3018 AND section_type = cv_section_type;
3019
3020 CURSOR c_jai_dist_amount(cn_invoice_distribution_id number) IS
3021 SELECT nvl(amount,0)
3022 FROM jai_ap_tds_inv_taxes
3023 --WHERE invoice_distribution_id = cn_invoice_distribution_id --Commented by Chong for issue120920-66 2012/10/09
3024 WHERE (invoice_distribution_id = cn_invoice_distribution_id --Added by Chong for issue120920-66 2012/10/09
3025 OR invoice_distribution_id = 1) --Added by Chong for issue120920-66 2012/10/09
3026 AND invoice_id = p_invoice_id --Added by Chong for issue120920-66 2012/10/09
3027 AND amount > 0;
3028 /*Update for adding exchange rate for bug13833254 */
3029 cursor c_get_tax_sec_det(p_invoice_id number, p_invoice_distribution_id number)
3030 is
3031 select tds_section_code_other, application_amount * nvl(p_exchange_rate,1) application_amount, invoice_distribution_id
3032 from jai_ap_tds_prepayments
3033 where invoice_id = p_invoice_id
3034 and invoice_distribution_id_prepay = p_invoice_distribution_id;
3035
3036 cursor c_get_prepayment_thgroup(p_pre_pay_inv_id number, p_prepay_distribution_id number,cp_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
3037 select threshold_grp_id,
3038 actual_tax_id,
3039 threshold_trx_id
3040 from jai_ap_tds_inv_taxes
3041 where invoice_id = p_pre_pay_inv_id
3042 and invoice_distribution_id = p_prepay_distribution_id
3043 and section_type = cp_section_type;
3044
3045 cursor c_get_total_prepayment_tax
3046 (p_invoice_id number, p_invoice_distribution_id number, p_exchange_rate number) is
3047 select sum( decode(tds_applicable_flag , 'Y', application_amount*p_exchange_rate, 0) ) tds_taxable_basis,
3048 sum( decode(tds_applicable_flag , 'Y', tds_application_amount, 0) ) tds_amount,
3049 sum( decode(wct_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) wct_taxable_basis,
3050 sum( decode(wct_applicable_flag, 'Y', wct_application_amount, 0) ) wct_amount,
3051 sum( decode(essi_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) essi_taxable_basis,
3052 sum( decode(essi_applicable_flag, 'Y', essi_application_amount, 0) ) essi_amount
3053 from jai_ap_tds_prepayments
3054 where invoice_id = p_invoice_id
3055 and invoice_distribution_id_prepay = p_invoice_distribution_id;
3056
3057 cursor c_get_amt_tds_inv_generated_si(p_invoice_id number, p_item_distribution_id number) is
3058 select sum(calc_tds_appln_amt) , sum(application_amount)
3059 from jai_ap_tds_prepayments jatp
3060 where invoice_id = p_invoice_id
3061 and invoice_distribution_id_prepay = p_invoice_distribution_id
3062 and invoice_distribution_id = p_item_distribution_id
3063 and tds_applicable_flag = 'Y'
3064 and exists (select '1'
3065 from jai_ap_tds_inv_taxes
3066 where invoice_distribution_id = jatp.invoice_distribution_id
3067 and section_type = 'TDS_SECTION'
3068 --and threshold_trx_id is not null --Commented by Chong for issue120920-66 2012/10/09
3069 );
3070
3071 CURSOR c_get_section_code IS
3072 SELECT DISTINCT tt.actual_SECTION_CODE
3073 FROM JAI_AP_TDS_PREPAYMENTS tp, JAI_AP_TDS_INV_TAXES tt
3074 WHERE tp.invoice_distribution_id = tt.invoice_distribution_id
3075 AND tp.tds_section_code_other = tt.actual_section_code
3076 AND tp.invoice_distribution_id_prepay = p_invoice_distribution_id;
3077
3078 CURSOR c_get_tds_cate(cn_invoice_distribution_id NUMBER) IS
3079 SELECT DISTINCT tt.tax_category_id
3080 FROM JAI_AP_TDS_INV_TAXES tt
3081 WHERE tt.invoice_distribution_id = cn_invoice_distribution_id;
3082
3083 /*Update for adding exchange rate for bug13833254 */
3084 --Updated by Wenqiong for bug13787605 begin
3085 CURSOR c_get_tds_group_amt (cv_section_code VARCHAR2)IS
3086 -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 begin
3087 --select get_tax_category(jatp.invoice_distribution_id ) tax_category_id,
3088 select get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
3089 -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 end.
3090 sum(tds_application_amount)* nvl(p_exchange_rate,1) tds_amount,
3091 sum(application_amount)* nvl(p_exchange_rate,1) amount
3092 from jai_ap_tds_prepayments jatp
3093 where jatp.invoice_id = p_invoice_id
3094 and jatp.tds_applicable_flag = 'Y'
3095 AND jatp.tds_section_code_other= cv_section_code
3096 AND jatp.invoice_distribution_id_prepay = p_invoice_distribution_id
3097 -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 begin
3098 --GROUP BY get_tax_category(jatp.invoice_distribution_id );
3099 GROUP BY get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id));
3100 -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 end.
3101 --Updated by Wenqiong for bug13787605 end
3102
3103 /*Update for adding exchange rate for bug13833254 */
3104 --Updated by Wenqiong for bug13787605 begin
3105 CURSOR c_get_wct_group_amt (cv_section_code VARCHAR2)IS
3106 -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 begin
3107 --SELECT get_tax_category(jatp.invoice_distribution_id ) tax_category_id,
3108 select get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
3109 -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 end.
3110 sum(wct_application_amount)* nvl(p_exchange_rate,1) wct_amount,
3111 sum(application_amount)* nvl(p_exchange_rate,1) amount
3112 from jai_ap_tds_prepayments jatp
3113 where jatp.invoice_id = p_invoice_id
3114 and jatp.wct_applicable_flag = 'Y'
3115 AND jatp.tds_section_code_other= cv_section_code
3116 AND jatp.invoice_distribution_id_prepay = p_invoice_distribution_id
3117 -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 begin
3118 --GROUP BY get_tax_category(jatp.invoice_distribution_id );
3119 GROUP BY get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id));
3120 -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 end.
3121 --Updated by Wenqiong for bug13787605 end
3122
3123 /*Update for adding exchange rate for bug13833254 */
3124 CURSOR c_get_essi_group_amt (cv_section_code VARCHAR2)IS
3125 -- modifed by zhiwei.xin for bug 13837788 on 14-Mar-2012 begin
3126 --select get_tax_category(jatp.invoice_distribution_id ) tax_category_id,
3127 select get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
3128 -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 end.
3129 sum(essi_application_amount)* nvl(p_exchange_rate,1) essi_amount,
3130 sum(application_amount)* nvl(p_exchange_rate,1) amount
3131 from jai_ap_tds_prepayments jatp
3132 where jatp.invoice_id = p_invoice_id
3133 and jatp.essi_applicable_flag = 'Y'
3134 AND jatp.tds_section_code_other= cv_section_code
3135 AND jatp.invoice_distribution_id_prepay = p_invoice_distribution_id
3136 -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 begin
3137 --GROUP BY get_tax_category(jatp.invoice_distribution_id );
3138 GROUP BY get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id));
3139 -- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 end.
3140 --Updated by Wenqiong for bug13787605.
3141
3142 cursor c_si_ap_invoices_all (p_invoice_id number) is
3143 select invoice_num, invoice_id
3144 from ap_invoices_all
3145 where invoice_id = p_invoice_id;
3146
3147 CURSOR c_get_thgrp_det ( p_threshold_grp_id NUMBER ) IS
3148 SELECT *
3149 FROM jai_ap_tds_thhold_grps
3150 WHERE threshold_grp_id = p_threshold_grp_id;
3151
3152 cursor c_get_grp_details_si_inv_dist(p_invoice_id number, p_invoice_distribution_id number)
3153 is
3154 select threshold_grp_id
3155 from jai_ap_tds_inv_taxes
3156 where invoice_id = p_invoice_id
3157 and invoice_distribution_id = p_invoice_distribution_id
3158 and section_type = 'TDS_SECTION';
3159
3160
3161 ln_si_thgrp_id number;
3162
3163 /*Fetch the taxable amount for which TDS is generated*/
3164 CURSOR c_taxable_amount(p_invoice_id NUMBER)
3165 IS
3166 SELECT nvl(sum(taxable_amount), 0)
3167 FROM jai_ap_tds_thhold_trxs
3168 WHERE invoice_id = p_invoice_id
3169 AND tds_event = 'INVOICE VALIDATE'
3170 group by invoice_id;
3171
3172 /*Fetch the taxable basis for which TDS would be generated*/
3173 CURSOR c_available_amount (p_invoice_id number, p_exchange_rate NUMBER)
3174 IS
3175 SELECT nvl(sum(amount), 0)--Remove multiple rate for bug13833254
3176 FROM jai_ap_tds_inv_taxes
3177 WHERE invoice_id = p_invoice_id
3178 AND nvl(actual_tax_id, default_tax_id) is not null
3179 AND section_type = 'TDS_SECTION'
3180 AND actual_section_code IS NOT NULL
3181 AND threshold_trx_id IS NOT NULL
3182 group by invoice_id;
3183
3184 CURSOR c_tds_tax(cn_invoice_distribution_id NUMBER) IS
3185 SELECT tax_category_id,
3186 section_type,
3187 actual_section_code
3188 FROM JAI_AP_TDS_INV_TAXES
3189 WHERE INVOICE_DISTRIBUTION_ID = cn_invoice_distribution_id
3190 AND ROWNUM = 1;
3191
3192 r_gl_sets_of_books c_gl_sets_of_books%rowtype;
3193 r_get_total_prepayment_tax c_get_total_prepayment_tax%rowtype;
3194 r_get_tax_sec_det c_get_tax_sec_det%ROWTYPE;
3195 ln_exchange_rate number;
3196 ln_threshold_grp_id number;
3197 ln_prepay_tax_id number;
3198
3199 lv_invoice_to_tds_num ap_invoices_all.invoice_num%type;
3200 lv_invoice_to_vendor_num ap_invoices_all.invoice_num%type;
3201 lv_invoice_num_prepay_apply ap_invoices_all.invoice_num%type;
3202 ln_threshold_trx_id_tds number;
3203 ln_threshold_trx_id_wct number;
3204 ln_threshold_trx_id_essi number;
3205 ln_start_threshold_trx_id number;
3206 ln_prepayment_amount number;
3207 ln_pp_section_tax_id number;
3208 ln_threshold_grp_audit_id number;
3209 lv_application_basis jai_ap_tds_prepayments.application_basis%type;
3210 ln_threshold_trx_id_prepay jai_ap_tds_inv_taxes.threshold_trx_id%type;
3211 ln_amt_tds_inv_generated_si number;
3212 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
3213 lv_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
3214 ln_after_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%TYPE;
3215 lv_after_threshold_type jai_ap_tds_thhold_types.threshold_type%TYPE;
3216 ln_temp_threshold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
3217 ln_temp_threshold_hdr_id jai_ap_tds_thhold_hdrs.threshold_hdr_id%TYPE;
3218
3219
3220 ln_tds_tmp_amt number;
3221 ln_parent_invoice_id NUMBER ;
3222 ln_pp_thhold_grp_id jai_ap_tds_thhold_grps.threshold_grp_id%TYPE;
3223 ln_tds_application_amt jai_ap_tds_prepayments.application_amount%TYPE ;
3224 r_pp_jai_ap_tds_thhold_grps c_get_thgrp_det%ROWTYPE ;
3225 r_si_jai_ap_tds_thhold_grps c_get_thgrp_det%ROWTYPE ;
3226 pre_pay_inv_id ap_invoice_distributions_all.invoice_id%TYPE;
3227 ln_tot_tds_amt number := 0;
3228 ln_tot_appln_amt number := 0;
3229 ln_application_mode VARCHAR2(1);
3230 ln_taxable_amount NUMBER;
3231 ln_available_amount NUMBER;
3232
3233
3234 ln_tax_amount NUMBER;
3235 ln_taxable_basis NUMBER;
3236
3237 ln_tax_category_id NUMBER;
3238 lv_section_type JAI_AP_TDS_INV_TAXES.section_type%TYPE;
3239 lv_section_code JAI_AP_TDS_INV_TAXES.actual_section_code%TYPE;
3240 l_api_name CONSTANT VARCHAR2(50) := 'generate_rtn()';
3241
3242 begin
3243 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.BEGIN', G_PKG_NAME || ': '||l_api_name||'()+');
3244
3245 p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_tds_prepayemnts_pkg.generate_rtn', 'START'); /* 1 */
3246
3247 get_prepay_invoice_id(p_prepay_distribution_id,pre_pay_inv_id);
3248
3249 open c_gl_sets_of_books(p_set_of_books_id);
3250 fetch c_gl_sets_of_books into r_gl_sets_of_books;
3251 close c_gl_sets_of_books;
3252
3253 if r_gl_sets_of_books.currency_code <> p_invoice_currency_code then
3254 /* Foreign currency invoice */
3255 p_codepath := jai_general_pkg.plot_codepath(6.1, p_codepath); /* 6.1 */
3256 ln_exchange_rate := p_exchange_rate;
3257 end if;
3258
3259 ln_exchange_rate := nvl(ln_exchange_rate, 1);
3260
3261 ln_prepayment_amount := -1 * p_prepay_amount * ln_exchange_rate;
3262
3263 /* update the tax amount for the prepayements */
3264 for cur_rec in c_jai_ap_tds_prepayments(p_invoice_id, p_invoice_distribution_id)
3265 loop
3266
3267 if lv_application_basis is null then
3268 lv_application_basis := cur_rec.application_basis;
3269 end if;
3270
3271 /* TDS application amount */
3272 IF cur_rec.tds_applicable_flag = 'Y' THEN
3273
3274 IF lv_application_basis = 'STANDARD' THEN
3275 OPEN c_jai_tds_tax(cur_rec.invoice_distribution_id,cur_rec.tds_section_code_other,'TDS_SECTION');
3276 FETCH c_jai_tds_tax INTO ln_tax_amount;
3277 CLOSE c_jai_tds_tax;
3278
3279 OPEN c_jai_dist_amount (cur_rec.invoice_distribution_id);
3280 FETCH c_jai_dist_amount INTO ln_taxable_basis;
3281 CLOSE c_jai_dist_amount;
3282 ELSIF lv_application_basis = 'PREPAYMENT' THEN
3283 OPEN c_jai_tds_tax(p_prepay_distribution_id,cur_rec.tds_section_code_prepay,'TDS_SECTION');
3284 FETCH c_jai_tds_tax INTO ln_tax_amount;
3285 CLOSE c_jai_tds_tax;
3286
3287 OPEN c_jai_dist_amount (p_prepay_distribution_id);
3288 FETCH c_jai_dist_amount INTO ln_taxable_basis;
3289 CLOSE c_jai_dist_amount;
3290
3291 END IF;
3292 ln_tds_tmp_amt := cur_rec.application_amount * ln_tax_amount/ln_taxable_basis ;
3293
3294 update jai_ap_tds_prepayments
3295 set tds_application_amount = ln_tds_tmp_amt,
3296 calc_tds_appln_amt = ln_tds_tmp_amt * ln_exchange_rate --updated by Wenqiong for bug 13771948
3297 where tds_prepayment_id = cur_rec.tds_prepayment_id;
3298
3299 --Added by Chong.Lei for bug#13802244 begin
3300 -----------------------------------------------------------------------------------------------------
3301 --Add update for tds_threshold_grp_id. Threshold Grping shud be updated irrespective of whether TDS Invoices are generated or not
3302 open c_get_prepayment_thgroup(pre_pay_inv_id,p_prepay_distribution_id,'TDS_SECTION');
3303 fetch c_get_prepayment_thgroup into ln_pp_thhold_grp_id, ln_prepay_tax_id, ln_threshold_trx_id_prepay;
3304 close c_get_prepayment_thgroup;
3305
3306 update jai_ap_tds_prepayments
3307 set tds_threshold_grp_id = ln_pp_thhold_grp_id
3308 where tds_prepayment_id = cur_rec.tds_prepayment_id;
3309 -----------------------------------------------------------------------------------------------------
3310 --Added by Chong.Lei for bug#13802244 end
3311
3312 END IF;
3313 IF cur_rec.wct_applicable_flag = 'Y' THEN /* WCT application*/
3314 IF lv_application_basis = 'STANDARD' THEN
3315 OPEN c_jai_tds_tax(cur_rec.invoice_distribution_id,cur_rec.tds_section_code_other,'WCT_SECTION');
3316 FETCH c_jai_tds_tax INTO ln_tax_amount;
3317 CLOSE c_jai_tds_tax;
3318
3319 OPEN c_jai_dist_amount (cur_rec.invoice_distribution_id);
3320 FETCH c_jai_dist_amount INTO ln_taxable_basis;
3321 CLOSE c_jai_dist_amount;
3322 ELSIF lv_application_basis = 'PREPAYMENT' THEN
3323 OPEN c_jai_tds_tax(p_prepay_distribution_id,cur_rec.tds_section_code_prepay,'WCT_SECTION');
3324 FETCH c_jai_tds_tax INTO ln_tax_amount;
3325 CLOSE c_jai_tds_tax;
3326
3327 OPEN c_jai_dist_amount (p_prepay_distribution_id);
3328 FETCH c_jai_dist_amount INTO ln_taxable_basis;
3329 CLOSE c_jai_dist_amount;
3330
3331 END IF;
3332 ln_tds_tmp_amt := cur_rec.application_amount * ln_tax_amount/ln_taxable_basis ;
3333
3334 update jai_ap_tds_prepayments
3335 set wct_application_amount = ln_tds_tmp_amt,
3336 calc_wct_appln_amt = ln_tds_tmp_amt * ln_exchange_rate --updated by Wenqiong for bug 13771948
3337 where tds_prepayment_id = cur_rec.tds_prepayment_id;
3338 END IF;
3339 IF cur_rec.essi_applicable_flag = 'Y' THEN /* ESSI application */
3340 IF lv_application_basis = 'STANDARD' THEN
3341 OPEN c_jai_tds_tax(cur_rec.invoice_distribution_id,cur_rec.tds_section_code_other,'ESSI_SECTION');
3342 FETCH c_jai_tds_tax INTO ln_tax_amount;
3343 CLOSE c_jai_tds_tax;
3344
3345 OPEN c_jai_dist_amount (p_invoice_distribution_id);
3346 FETCH c_jai_dist_amount INTO ln_taxable_basis;
3347 CLOSE c_jai_dist_amount;
3348 ELSIF lv_application_basis = 'PREPAYMENT' THEN
3349 OPEN c_jai_tds_tax(cur_rec.invoice_distribution_id,cur_rec.tds_section_code_prepay,'ESSI_SECTION');
3350 FETCH c_jai_tds_tax INTO ln_tax_amount;
3351 CLOSE c_jai_tds_tax;
3352
3353 OPEN c_jai_dist_amount (p_prepay_distribution_id);
3354 FETCH c_jai_dist_amount INTO ln_taxable_basis;
3355 CLOSE c_jai_dist_amount;
3356
3357 END IF;
3358 ln_tds_tmp_amt := cur_rec.application_amount * ln_tax_amount/ln_taxable_basis ;
3359
3360 update jai_ap_tds_prepayments
3361 set essi_application_amount = ln_tds_tmp_amt,
3362 calc_essi_appln_amt = ln_tds_tmp_amt * ln_exchange_rate --updated by Wenqiong for bug 13771948
3363 where tds_prepayment_id = cur_rec.tds_prepayment_id;
3364
3365 END IF; /* TDS */
3366 end loop;
3367
3368
3369 /*Compare the Taxable amount for which TDS is deducted with the amount for which TDS would be deducted.
3370 If the Taxable amount is less than the Available amount then Prepayment was applied prior to validation.
3371 Taxable amount for Invoice validated after Prepayment application would be effective amount and hence
3372 Prepayment application mode would be 'B' indicating prepayment application happened prior to validation.
3373 Threshold Groups need not be updated with the Prepayment application amount as only Effective amount i.e. Invoice - Prepay
3374 was passed to Threshold Group during validation*/
3375 ln_application_mode := NULL;
3376 OPEN c_taxable_amount(p_invoice_id);
3377 FETCH c_taxable_amount INTO ln_taxable_amount;
3378 CLOSE c_taxable_amount;
3379
3380 IF ln_taxable_amount > 0 THEN
3381 OPEN c_available_amount(p_invoice_id, p_exchange_rate);
3382 FETCH c_available_amount INTO ln_available_amount;
3383 CLOSE c_available_amount;
3384
3385 IF ln_taxable_amount < ln_available_amount THEN
3386 ln_application_mode := 'B';
3387 ELSE
3388 ln_application_mode := 'A';
3389 END IF;
3390 ELSE
3391 ln_application_mode := 'A';
3392 END IF;
3393
3394 open c_get_total_prepayment_tax(p_invoice_id, p_invoice_distribution_id, ln_exchange_rate);
3395 fetch c_get_total_prepayment_tax into r_get_total_prepayment_tax;
3396 close c_get_total_prepayment_tax;
3397
3398 open c_get_tax_sec_det(p_invoice_id, p_invoice_distribution_id);
3399 fetch c_get_tax_sec_det into r_get_tax_sec_det;
3400 close c_get_tax_sec_det;
3401
3402 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'r_get_tax_sec_det.application_amount: '||r_get_tax_sec_det.application_amount);
3403 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'r_get_tax_sec_det.tds_section_code_other: '||r_get_tax_sec_det.tds_section_code_other);
3404 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'r_get_total_prepayment_tax.tds_amount: '||r_get_total_prepayment_tax.tds_amount);
3405 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'ln_application_mode: '||ln_application_mode);
3406 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'p_invoice_id: '||p_invoice_id);
3407 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'p_invoice_distribution_id: '||p_invoice_distribution_id);
3408 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'p_prepay_distribution_id: '||p_prepay_distribution_id);
3409
3410 if r_get_tax_sec_det.application_amount > 0
3411 and (r_get_tax_sec_det.tds_section_code_other is not NULL)
3412 --and r_get_total_prepayment_tax.tds_amount = 0 --Commented by Chong for bug#13802244 2012/09/19
3413 and ln_application_mode = 'A'
3414 then
3415 open c_get_grp_details_si_inv_dist(p_invoice_id, r_get_tax_sec_det.invoice_distribution_id);
3416 fetch c_get_grp_details_si_inv_dist into ln_si_thgrp_id;
3417 close c_get_grp_details_si_inv_dist;
3418
3419 --Added by Chong.Lei for bug#13802244 begin
3420 -----------------------------------------------------------------------------------------------------
3421 --Call to procedure - get_tds_threshold_slab, Store the current Threshold slab and type before PP application
3422 jai_ap_dtc_generation_pkg.get_tds_threshold_slab(
3423 p_invoice_id => p_invoice_id, -- added by Chong.lei for DTC #13359892 20120206
3424 p_prepay_distribution_id => p_prepay_distribution_id,
3425 p_threshold_grp_id => ln_si_thgrp_id,
3426 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
3427 p_threshold_slab_id => ln_threshold_slab_id,
3428 p_threshold_type => lv_threshold_type,
3429 p_process_flag => p_process_flag,
3430 p_process_message => p_process_message,
3431 p_codepath => p_codepath);
3432
3433 IF p_process_flag = 'E' THEN
3434 goto exit_from_procedure;
3435 END IF;
3436 -----------------------------------------------------------------------------------------------------
3437 --Added by Chong.Lei for bug#13802244 begin
3438
3439 jai_ap_dtc_generation_pkg.maintain_thhold_grps(
3440 p_threshold_grp_id => ln_si_thgrp_id,
3441 p_trx_invoice_apply_amount => r_get_tax_sec_det.application_amount,
3442 p_tds_event => 'PREPAYMENT APPLICATION',
3443 p_invoice_id => p_invoice_id,
3444 p_invoice_distribution_id => p_invoice_distribution_id,
3445 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
3446 p_creation_Date => p_creation_date,
3447 p_process_flag => p_process_flag,
3448 P_process_message => p_process_message,
3449 p_codepath => p_codepath
3450 );
3451 end if; --r_get_tax_sec_det.application_amount > 0
3452
3453 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'r_get_total_prepayment_tax.tds_amount: '||r_get_total_prepayment_tax.tds_amount);
3454 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'ln_application_mode: '||ln_application_mode);
3455
3456 if r_get_total_prepayment_tax.tds_amount > 0 AND ln_application_mode = 'A' then
3457
3458 /*
3459 * Get the details of threshold grp for prepay and invoice.
3460 * This determines which group needs to be hit.
3461 */
3462 open c_get_prepayment_thgroup(pre_pay_inv_id,p_prepay_distribution_id,'TDS_SECTION');
3463 fetch c_get_prepayment_thgroup into ln_pp_thhold_grp_id, ln_prepay_tax_id, ln_threshold_trx_id_prepay;
3464 close c_get_prepayment_thgroup;
3465
3466 IF ln_pp_thhold_grp_id IS NULL
3467 AND (r_get_total_prepayment_tax.tds_amount > 0 OR
3468 r_get_total_prepayment_tax.wct_amount > 0 OR
3469 r_get_total_prepayment_tax.essi_amount > 0) THEN
3470 p_process_flag := 'E';
3471 P_process_message := 'Threshold group identifier is not found against the prepayment invoice TDS tax, cannot proceed.';
3472 goto exit_from_procedure;
3473 end if;
3474
3475 OPEN c_get_thgrp_det(ln_pp_thhold_grp_id);
3476 FETCH c_get_thgrp_det INTO r_pp_jai_ap_tds_thhold_grps;
3477 CLOSE c_get_thgrp_det;
3478
3479 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'ln_application_mode: '||ln_application_mode);
3480 FOR get_si_det IN (SELECT jattt.*,
3481 jatp.tds_prepayment_id tds_prepayment_id,
3482 jatp.application_amount * nvl(p_exchange_rate,1) tds_taxable_basis,
3483 jatp.invoice_distribution_id tax_dist
3484 FROM jai_ap_tds_thhold_trxs jattt,
3485 jai_ap_tds_prepayments jatp
3486 WHERE jattt.invoice_id = jatp.invoice_id
3487 AND (jattt.tds_event = 'INVOICE VALIDATE'
3488 OR jattt.tds_event LIKE 'THRESHOLD TRANSITION(%' --Added threshold transition by Chong for issue120920-66 2012/10/09
3489 )
3490 AND jatp.tds_applicable_flag ='Y'
3491 AND invoice_distribution_id_prepay = p_invoice_distribution_id
3492 AND jattt.invoice_id = p_invoice_id
3493 AND jatp.invoice_distribution_id in
3494 (select invoice_distribution_id
3495 from jai_ap_tdS_inv_taxes
3496 where --threshold_trx_id = jattt.threshold_trx_id --Commented by Chong for issue120920-66 2012/10/09
3497 actual_section_code = jattt.tds_section_code --Added by Chong for issue120920-66 2012/10/09
3498 and invoice_id = p_invoice_id
3499 and section_type ='TDS_SECTION'
3500 )
3501 AND jattt.tds_section_code IS NOT NULL --Added By Chong, Only pick up TDS section records in trx table 20130330
3502 )
3503 LOOP
3504
3505 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'in loop : '||get_si_det.threshold_grp_id);
3506
3507 ln_temp_threshold_grp_id := get_si_det.threshold_grp_id;
3508
3509 IF NVL (ln_pp_thhold_grp_id, 0) <> 0 AND
3510 NVL (ln_temp_threshold_grp_id, 0) <> 0 AND
3511 NVL (ln_temp_threshold_grp_id,0 ) <> NVL (ln_pp_thhold_grp_id, 0)
3512 THEN
3513 OPEN c_get_thgrp_det(ln_temp_threshold_grp_id);
3514 FETCH c_get_thgrp_det INTO r_si_jai_ap_tds_thhold_grps;
3515 CLOSE c_get_thgrp_det;
3516 IF r_pp_jai_ap_tds_thhold_grps.fin_year > r_si_jai_ap_tds_thhold_grps.fin_year THEN
3517 ln_temp_threshold_grp_id := ln_pp_thhold_grp_id;
3518 END IF ;
3519 END IF ;
3520
3521 ln_threshold_grp_id := ln_temp_threshold_grp_id;
3522
3523 /* --Commented by Chong for issue120920-66 2012/10/09
3524 --Call to procedure - get_tds_threshold_slab, Store the current Threshold slab and type before PP application
3525 jai_ap_dtc_generation_pkg.get_tds_threshold_slab(
3526 p_invoice_id => p_invoice_id, -- added by Chong.lei for DTC #13359892 20120206
3527 p_prepay_distribution_id => p_prepay_distribution_id,
3528 p_threshold_grp_id => ln_temp_threshold_grp_id,
3529 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
3530 p_threshold_slab_id => ln_threshold_slab_id,
3531 p_threshold_type => lv_threshold_type,
3532 p_process_flag => p_process_flag,
3533 p_process_message => p_process_message,
3534 p_codepath => p_codepath);
3535
3536 IF p_process_flag = 'E' THEN
3537 goto exit_from_procedure;
3538 END IF;
3539 */
3540
3541 if r_get_total_prepayment_tax.tds_amount > 0 THEN
3542 /* --Commented by Chong for issue120920-66 2012/10/09
3543 \* update the threshold with the tds amount that will be impacted because of this application *\
3544 jai_ap_dtc_generation_pkg.maintain_thhold_grps
3545 (
3546 p_threshold_grp_id => ln_threshold_grp_id,
3547 p_trx_invoice_apply_amount => get_si_det.tds_taxable_basis,
3548 p_tds_event => 'PREPAYMENT APPLICATION',
3549 p_invoice_id => p_invoice_id,
3550 p_invoice_distribution_id => p_invoice_distribution_id,
3551 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
3552 p_creation_Date => p_creation_date,
3553 p_process_flag => p_process_flag,
3554 P_process_message => p_process_message,
3555 p_codepath => p_codepath
3556 );
3557
3558 IF p_process_flag = 'E' THEN
3559 GOTO exit_from_procedure;
3560 END IF;
3561 */
3562
3563 -- Update each distribution with the threshold grp id as
3564 -- it may vary depending on the date and the group.
3565 update jai_ap_tds_prepayments
3566 set tds_threshold_grp_id = ln_threshold_grp_id
3567 where tds_prepayment_id = get_si_det.tds_prepayment_id;
3568
3569 /* TDS invoice was generated against the Prepayment,
3570 check for what amount of the SI, TDS invoice was generated */
3571
3572 /*
3573 * Changed from p_invoice_distribution_id to get_si_det.invoice_distribution_id
3574 * as we need to generate for each line in jai_ap_tds_thhold_thhold_trxs
3575 */
3576 open c_get_amt_tds_inv_generated_si(p_invoice_id, get_si_det.tax_dist);
3577 fetch c_get_amt_tds_inv_generated_si INTO ln_amt_tds_inv_generated_si, ln_tds_application_amt;
3578 close c_get_amt_tds_inv_generated_si;
3579
3580 ln_tot_tds_amt := ln_tot_tds_amt + ln_amt_tds_inv_generated_si;
3581 ln_tot_appln_amt := ln_tot_appln_amt + ln_tds_application_amt;
3582 if p_event = 'INSERT' then
3583 update jai_ap_tds_prepayments
3584 set tds_threshold_trx_id_apply = -999
3585 where tds_prepayment_id = get_si_det.tds_prepayment_id;
3586 end if;
3587
3588
3589 IF ln_amt_tds_inv_generated_si > 0 THEN
3590 IF lv_application_basis = 'STANDARD' THEN
3591 /* get the standard invoice number */
3592 OPEN c_si_ap_invoices_all(p_invoice_id);
3593 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
3594 CLOSE c_si_ap_invoices_all;
3595 ELSE
3596
3597 /*cursor c_ap_invoices_all would not fetch the Invoice ID if the Prepayment did not
3598 suffer TDS when it was validated initially, but only when Threshold was breached
3599 In the above case there would be no records in jai_ap_tds_inv_taxes with the distribution
3600 ID of the Prepayment Invoice*/
3601 get_prepay_invoice_id(p_prepay_distribution_id, ln_parent_invoice_id);
3602 OPEN c_si_ap_invoices_all (ln_parent_invoice_id);
3603 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
3604 CLOSE c_si_ap_invoices_all;
3605
3606 END IF ; /* lv_application_basis*/
3607 end if; /* if ln_amt_tds_inv_generated_si > 0 then */
3608 end if ;
3609
3610
3611 --Call to procedure - get_tds_threshold_slab. Store the current Threshold slab and type After PP application
3612 jai_ap_dtc_generation_pkg.get_tds_threshold_slab(
3613 p_invoice_id => p_invoice_id, -- added by Chong.lei for DTC #13359892 20120206
3614 p_prepay_distribution_id => p_prepay_distribution_id,
3615 p_threshold_grp_id => ln_temp_threshold_grp_id,
3616 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
3617 p_threshold_slab_id => ln_after_threshold_slab_id,
3618 p_threshold_type => lv_after_threshold_type,
3619 p_process_flag => p_process_flag,
3620 p_process_message => p_process_message,
3621 p_codepath => p_codepath);
3622
3623 IF p_process_flag = 'E' THEN
3624 goto exit_from_procedure;
3625 END IF;
3626
3627 END LOOP ; /* get_si_det */
3628 end if; /* if r_get_total_prepayment_tax.tds_amount > 0 then */
3629 --Added by Wenqiong for bug13787605
3630 OPEN c_get_section_code;
3631 FETCH c_get_section_code INTO lv_section_code;
3632 CLOSE c_get_section_code;
3633 --Added by Wenqiong for bug13787605
3634
3635
3636 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'in loop ln_tot_tds_amt: '|| ln_tot_tds_amt);
3637
3638 IF ln_tot_tds_amt > 0 THEN
3639 IF lv_application_basis = 'STANDARD' THEN
3640 /* get the standard invoice number */
3641 OPEN c_si_ap_invoices_all(p_invoice_id);
3642 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
3643 CLOSE c_si_ap_invoices_all;
3644
3645 ELSE
3646 /*cursor c_ap_invoices_all would not fetch the Invoice ID if the Prepayment did not
3647 suffer TDS when it was validated initially, but only when Threshold was breached
3648 In the above case there would be no records in jai_ap_tds_inv_taxes with the distribution
3649 ID of the Prepayment Invoice*/
3650 get_prepay_invoice_id(p_prepay_distribution_id, ln_parent_invoice_id);
3651 OPEN c_si_ap_invoices_all (ln_parent_invoice_id);
3652 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
3653 CLOSE c_si_ap_invoices_all;
3654
3655 END IF ; /* lv_application_basis*/
3656
3657 lv_section_type := 'TDS_SECTION';
3658
3659 FOR r_get_tds_group_amt IN c_get_tds_group_amt(lv_section_code)
3660 LOOP
3661 --Added by Wenqiong for bug13787605 begin
3662 ln_tax_category_id := r_get_tds_group_amt.tax_category_id;
3663 --Added by Wenqiong for bug13787605 end
3664 ln_tot_appln_amt := r_get_tds_group_amt.amount;
3665 ln_tot_tds_amt := r_get_tds_group_amt.tds_amount;
3666 ln_tot_tds_amt := round(ln_tot_tds_amt,jai_ap_dtc_generation_pkg.get_tax_rounding(ln_parent_invoice_id));
3667
3668 if p_event = 'INSERT' then
3669
3670 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, '@@@in loop generate_dtc_invoices lv_invoice_num_prepay_apply: '|| lv_invoice_num_prepay_apply);
3671
3672 jai_ap_dtc_generation_pkg.generate_dtc_invoices
3673 (
3674 pn_invoice_id => ln_parent_invoice_id ,
3675 pn_invoice_distribution_id => p_invoice_distribution_id ,
3676 pv_invoice_num_prepay_apply=> lv_invoice_num_prepay_apply ,
3677 pn_taxable_amount => ln_tot_appln_amt ,
3678 pn_tax_amount => ln_tot_tds_amt ,
3679 pd_accounting_date => p_accounting_date ,
3680 pv_tds_event => 'PREPAYMENT APPLICATION' ,
3681 pn_threshold_grp_id => ln_threshold_grp_id ,
3682 pn_threshold_hdr_id => ln_temp_threshold_hdr_id, --Added by Chong for eTDS bug#16414088 20130320
3683 pn_tax_category_id => ln_tax_category_id ,
3684 pv_section_type => lv_section_type ,
3685 pv_section_code => lv_section_code ,
3686 pv_tds_invoice_num => lv_invoice_to_tds_num ,
3687 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
3688 pn_threshold_trx_id => ln_threshold_trx_id_tds ,
3689 pd_creation_date => sysdate ,
3690 p_process_flag => p_process_flag ,
3691 p_process_message => p_process_message
3692 );
3693
3694 IF p_process_flag = 'E' THEN
3695 GOTO exit_from_procedure;
3696 END IF ;
3697
3698 /* prepayment apply scenario for backward compatibility*/
3699
3700 IF ln_start_threshold_trx_id is null THEN
3701 ln_start_threshold_trx_id := ln_threshold_trx_id_tds;
3702 END IF ;
3703
3704 /* Update the threshold group */
3705 jai_ap_dtc_generation_pkg.maintain_thhold_grps
3706 ( p_threshold_grp_id => ln_threshold_grp_id,
3707 p_trx_tax_paid => (-1 * ln_tot_tds_amt),
3708 p_tds_event => 'PREPAYMENT APPLICATION',
3709 p_invoice_id => p_invoice_id,
3710 p_invoice_distribution_id => p_invoice_distribution_id,
3711 p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
3712 p_creation_date => p_creation_date,
3713 p_process_flag => p_process_flag,
3714 P_process_message => p_process_message,
3715 p_codepath => p_codepath
3716 );
3717
3718 --Added by Sanjikum for Bug#4722011
3719 IF p_process_flag = 'E' THEN
3720 GOTO exit_from_procedure;
3721 END IF;
3722
3723 /* Update jai_ap_tds_prepayments with threshold_trx_id_apply*/
3724 -- Update each distribution with the threshold grp id as
3725 -- it may vary depending on the date and the group.
3726 -- changed invoice_distribution_id_prepay to invoice_distribution_id.
3727 IF lv_application_basis = 'STANDARD' THEN
3728 update jai_ap_tds_prepayments tds_prepay
3729 set tds_threshold_trx_id_apply = ln_threshold_trx_id_tds
3730 where tds_threshold_trx_id_apply = -999
3731 and invoice_id = p_invoice_id
3732 and invoice_distribution_id_prepay = p_invoice_distribution_id
3733 AND EXISTS (SELECT 1 FROM JAI_AP_TDS_INV_TAXES tds_tax WHERE
3734 --tds_tax.invoice_distribution_id = tds_prepay.invoice_distribution_id --Commented by Chong for issue120920-66 2012/10/09
3735 tds_tax.actual_section_code = tds_prepay.tds_section_code_prepay --Added by Chong for issue120920-66 2012/10/09
3736 AND tds_tax.invoice_id = p_invoice_id
3737 AND tds_tax.tax_category_id = ln_tax_category_id);
3738 ELSE
3739 update jai_ap_tds_prepayments tds_prepay
3740 set tds_threshold_trx_id_apply = ln_threshold_trx_id_tds
3741 where tds_threshold_trx_id_apply = -999
3742 and invoice_id = p_invoice_id
3743 and invoice_distribution_id_prepay = p_invoice_distribution_id;
3744 END IF;
3745 --Commented by Chong.Lei for bug#13802244 begin
3746 /*if p_event = 'INSERT' then
3747 \*
3748 * Changed from p_invoice_id to ln_parent_invoice_id
3749 * Parent invoice_id should be depending on the TDS invoice
3750 * created.
3751 *\
3752
3753 jai_ap_dtc_generation_pkg.process_threshold_rollback
3754 ( p_invoice_id => ln_parent_invoice_id,
3755 p_before_threshold_type => lv_threshold_type,
3756 p_after_threshold_type => lv_after_threshold_type,
3757 p_before_threshold_slab_id => ln_threshold_slab_id,
3758 p_after_threshold_slab_id => ln_after_threshold_slab_id,
3759 p_threshold_grp_id => ln_temp_threshold_grp_id,
3760 p_org_id => p_org_id,
3761 p_accounting_date => p_accounting_date,
3762 p_invoice_distribution_id => p_invoice_distribution_id,
3763 p_prepay_distribution_id => p_prepay_distribution_id,
3764 p_called_from => 'PREPAY' ,
3765 p_process_flag => p_process_flag,
3766 p_process_message => p_process_message,
3767 p_codepath => p_codepath);
3768
3769 IF p_process_flag = 'E' THEN
3770 goto exit_from_procedure;
3771 END IF;
3772 end if; \*if p_event = 'INSERT' then*\ */
3773 --Commented by Chong.Lei for bug#13802244 begin
3774
3775 end if; /*if p_event = 'INSERT' then*/
3776 END LOOP;
3777 end if ; /* IF ln_tot_tds_amt > 0 THEN */
3778
3779 --Added by Chong for bug#16414088 eTDS ER Start
3780 ----------------------------------------------------------------------
3781 --Hook code add here after the loop of call generate_dtc_invoices
3782 IF ln_threshold_trx_id_tds IS NULL THEN -- no TDS generated
3783 jai_ap_tds_pop_rpst_pkg.populate_repository(
3784 pn_source_invoice_id => p_invoice_id
3785 ,pn_invoice_id => NULL
3786 ,pv_event => 'PREPAYMENT APPLICATION'
3787 ,pv_section_type => 'TDS_SECTION'
3788 ,pv_section_code => lv_section_code
3789 ,pn_threshold_grp_id => NVL(ln_threshold_grp_id,ln_si_thgrp_id)
3790 ,pn_threshold_hdr_id => ln_temp_threshold_hdr_id
3791 ,pn_invoice_distribution_id => p_invoice_distribution_id --create Prepy line in applied standard invoice
3792 ,pn_prepay_distribution_id => p_prepay_distribution_id --distribution of the PP invoice applied
3793 ,pn_threshold_type_id => NULL
3794 ,pn_threshold_slab_id => NULL
3795 );
3796 END IF; --n_threshold_trx_id IS NULL
3797 ----------------------------------------------------------------------
3798 --Added by Chong for bug#16414088 eTDS ER End
3799
3800 --Added by Chong.Lei for bug#13802244 begin
3801 -------------------------------------------------------------------------------------------
3802 if p_event = 'INSERT' then
3803 ln_temp_threshold_grp_id := NVL(ln_temp_threshold_grp_id,ln_si_thgrp_id);
3804
3805 --Call to procedure - get_tds_threshold_slab. Store the current Threshold slab and type After PP application
3806 jai_ap_dtc_generation_pkg.get_tds_threshold_slab(
3807 p_invoice_id => p_invoice_id, -- added by Chong.lei for DTC #13359892 20120206
3808 p_prepay_distribution_id => p_prepay_distribution_id,
3809 p_threshold_grp_id => ln_temp_threshold_grp_id,
3810 p_threshold_hdr_id => ln_temp_threshold_hdr_id,
3811 p_threshold_slab_id => ln_after_threshold_slab_id,
3812 p_threshold_type => lv_after_threshold_type,
3813 p_process_flag => p_process_flag,
3814 p_process_message => p_process_message,
3815 p_codepath => p_codepath);
3816
3817 IF p_process_flag = 'E' THEN
3818 goto exit_from_procedure;
3819 END IF;
3820
3821 /*If not TDS is created, ln_parent_invoice_id should be NULL and will create rollback be p_invoice_id*/
3822 IF ln_parent_invoice_id IS NULL THEN
3823
3824 ln_parent_invoice_id := p_invoice_id;
3825 END IF; /* ln_parent_invoice_id IS NULL THEN */
3826
3827 jai_ap_dtc_generation_pkg.process_threshold_rollback
3828 ( p_invoice_id => ln_parent_invoice_id,
3829 p_before_threshold_type => lv_threshold_type,
3830 p_after_threshold_type => lv_after_threshold_type,
3831 p_before_threshold_slab_id => ln_threshold_slab_id,
3832 p_after_threshold_slab_id => ln_after_threshold_slab_id,
3833 p_threshold_grp_id => ln_temp_threshold_grp_id,
3834 p_org_id => p_org_id,
3835 p_accounting_date => p_accounting_date,
3836 p_invoice_distribution_id => p_invoice_distribution_id,
3837 p_prepay_distribution_id => p_prepay_distribution_id,
3838 p_called_from => 'PREPAY' ,
3839 p_process_flag => p_process_flag,
3840 p_process_message => p_process_message,
3841 p_codepath => p_codepath);
3842
3843 IF p_process_flag = 'E' THEN
3844 goto exit_from_procedure;
3845 END IF;
3846 end if; /*if p_event = 'INSERT' then*/
3847 -------------------------------------------------------------------------------------------
3848 --Added by Chong.Lei for bug#13802244 end
3849
3850 /* prepayment apply scenario for backward compatibility*/
3851 update JAI_AP_TDS_INVOICES
3852 set amt_reversed = nvl(amt_reversed, 0) + r_get_total_prepayment_tax.tds_amount * nvl(p_exchange_rate,1),
3853 amt_applied = nvl(amt_applied, 0) + abs(p_prepay_amount) * nvl(p_exchange_rate,1)
3854 where invoice_id = p_invoice_id;
3855
3856 if r_get_total_prepayment_tax.wct_amount > 0 THEN
3857
3858 IF lv_application_basis = 'STANDARD' THEN
3859 /* get the standard invoice number */
3860 OPEN c_si_ap_invoices_all(p_invoice_id);
3861 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
3862 CLOSE c_si_ap_invoices_all;
3863 ELSE
3864 /*cursor c_ap_invoices_all would not fetch the Invoice ID if the Prepayment did not
3865 suffer TDS when it was validated initially, but only when Threshold was breached
3866 In the above case there would be no records in jai_ap_tds_inv_taxes with the distribution
3867 ID of the Prepayment Invoice*/
3868 get_prepay_invoice_id(p_prepay_distribution_id, ln_parent_invoice_id);
3869 OPEN c_si_ap_invoices_all (ln_parent_invoice_id);
3870 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
3871 CLOSE c_si_ap_invoices_all;
3872
3873 END IF ; /* lv_application_basis*/
3874
3875 lv_section_type := 'WCT_SECTION';
3876
3877 FOR r_get_wct_group_amt IN c_get_wct_group_amt(lv_section_code)
3878 LOOP
3879
3880 --Added by Wenqiong for bug13787605 begin
3881 ln_tax_category_id := r_get_wct_group_amt.tax_category_id;
3882 --Added by Wenqiong for bug13787605 end
3883
3884 ln_tot_appln_amt := r_get_wct_group_amt.amount;
3885 ln_tot_tds_amt := r_get_wct_group_amt.wct_amount;
3886 ln_tot_tds_amt := round(ln_tot_tds_amt,jai_ap_dtc_generation_pkg.get_tax_rounding(ln_parent_invoice_id));
3887 if p_event = 'INSERT' then
3888
3889 jai_ap_dtc_generation_pkg.generate_dtc_invoices
3890 (
3891 pn_invoice_id => ln_parent_invoice_id ,
3892 pn_invoice_distribution_id => p_invoice_distribution_id ,
3893 pv_invoice_num_prepay_apply=> lv_invoice_num_prepay_apply ,
3894 pn_taxable_amount => ln_tot_appln_amt ,
3895 pn_tax_amount => ln_tot_tds_amt ,
3896 pd_accounting_date => p_accounting_date ,
3897 pv_tds_event => 'PREPAYMENT APPLICATION' ,
3898 pn_threshold_grp_id => ln_threshold_grp_id ,
3899 pn_threshold_hdr_id => ln_temp_threshold_hdr_id, --Added by Chong for eTDS bug#16414088 20130320
3900 pn_tax_category_id => ln_tax_category_id ,
3901 pv_section_type => lv_section_type ,
3902 pv_section_code => lv_section_code ,
3903 pv_tds_invoice_num => lv_invoice_to_tds_num ,
3904 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
3905 pn_threshold_trx_id => ln_threshold_trx_id_wct ,
3906 pd_creation_date => sysdate ,
3907 p_process_flag => p_process_flag ,
3908 p_process_message => p_process_message
3909 );
3910 if p_process_flag = 'E' then
3911 goto exit_from_procedure;
3912 end if;
3913
3914 IF lv_application_basis = 'STANDARD' THEN
3915 update jai_ap_tds_prepayments tds_prepay
3916 set wct_threshold_trx_id_apply = ln_threshold_trx_id_wct
3917 where invoice_id = p_invoice_id
3918 and invoice_distribution_id_prepay = p_invoice_distribution_id
3919 and wct_applicable_flag = 'Y'
3920 AND EXISTS (SELECT 1 FROM JAI_AP_TDS_INV_TAXES tds_tax WHERE
3921 tds_tax.invoice_distribution_id = tds_prepay.invoice_distribution_id
3922 AND tds_tax.invoice_id = p_invoice_id
3923 AND tds_tax.tax_category_id = ln_tax_category_id);
3924 ELSE
3925 update jai_ap_tds_prepayments tds_prepay
3926 set wct_threshold_trx_id_apply = ln_threshold_trx_id_wct
3927 where invoice_id = p_invoice_id
3928 and invoice_distribution_id_prepay = p_invoice_distribution_id
3929 and wct_applicable_flag = 'Y';
3930 END IF;
3931 if ln_start_threshold_trx_id is null then
3932 ln_start_threshold_trx_id := ln_threshold_trx_id_wct;
3933 end if;
3934 end if; /*if p_event = 'INSERT' then*/
3935 /* Generate the return invoices */
3936 END LOOP;
3937 end if; /* if r_get_total_prepayment_tax.wct_amount > 0 then */
3938
3939
3940 if r_get_total_prepayment_tax.essi_amount > 0 then
3941 IF lv_application_basis = 'STANDARD' THEN
3942 /* get the standard invoice number */
3943 OPEN c_si_ap_invoices_all(p_invoice_id);
3944 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
3945 CLOSE c_si_ap_invoices_all;
3946
3947 ELSE
3948 /*cursor c_ap_invoices_all would not fetch the Invoice ID if the Prepayment did not
3949 suffer TDS when it was validated initially, but only when Threshold was breached
3950 In the above case there would be no records in jai_ap_tds_inv_taxes with the distribution
3951 ID of the Prepayment Invoice*/
3952 get_prepay_invoice_id(p_prepay_distribution_id, ln_parent_invoice_id);
3953 OPEN c_si_ap_invoices_all (ln_parent_invoice_id);
3954 FETCH c_si_ap_invoices_all INTO lv_invoice_num_prepay_apply, ln_parent_invoice_id;
3955 CLOSE c_si_ap_invoices_all;
3956
3957 END IF ; /* lv_application_basis*/
3958 lv_section_type := 'ESSI_SECTION';
3959 FOR r_get_essi_group_amt IN c_get_essi_group_amt(lv_section_code)
3960 LOOP
3961 --Added by Wenqiong for bug13787605 begin
3962 ln_tax_category_id := r_get_essi_group_amt.tax_category_id;
3963 --Added by Wenqiong for bug13787605 end
3964
3965 ln_tot_appln_amt := r_get_essi_group_amt.amount;
3966 ln_tot_tds_amt := r_get_essi_group_amt.essi_amount;
3967 ln_tot_tds_amt := round(ln_tot_tds_amt,jai_ap_dtc_generation_pkg.get_tax_rounding(ln_parent_invoice_id));
3968 if p_event = 'INSERT' then
3969
3970 jai_ap_dtc_generation_pkg.generate_dtc_invoices
3971 (
3972 pn_invoice_id => ln_parent_invoice_id ,
3973 pn_invoice_distribution_id => p_invoice_distribution_id ,
3974 pv_invoice_num_prepay_apply=> lv_invoice_num_prepay_apply ,
3975 pn_taxable_amount => ln_tot_appln_amt ,
3976 pn_tax_amount => ln_tot_tds_amt ,
3977 pd_accounting_date => p_accounting_date ,
3978 pv_tds_event => 'PREPAYMENT APPLICATION' ,
3979 pn_threshold_grp_id => ln_threshold_grp_id ,
3980 pn_threshold_hdr_id => ln_temp_threshold_hdr_id, --Added by Chong for eTDS bug#16414088 20130320
3981 pn_tax_category_id => ln_tax_category_id ,
3982 pv_section_type => lv_section_type ,
3983 pv_section_code => lv_section_code ,
3984 pv_tds_invoice_num => lv_invoice_to_tds_num ,
3985 pv_cm_invoice_num => lv_invoice_to_vendor_num ,
3986 pn_threshold_trx_id => ln_threshold_trx_id_wct ,
3987 pd_creation_date => sysdate ,
3988 p_process_flag => p_process_flag ,
3989 p_process_message => p_process_message
3990 );
3991 if p_process_flag = 'E' then
3992 goto exit_from_procedure;
3993 end if;
3994 IF lv_application_basis = 'STANDARD' THEN
3995
3996 update jai_ap_tds_prepayments tds_prepay
3997 set essi_threshold_trx_id_apply = ln_threshold_trx_id_essi
3998 where invoice_id = p_invoice_id
3999 and invoice_distribution_id_prepay = p_invoice_distribution_id
4000 and essi_applicable_flag = 'Y'
4001 AND EXISTS (SELECT 1 FROM JAI_AP_TDS_INV_TAXES tds_tax WHERE
4002 tds_tax.invoice_distribution_id = tds_prepay.invoice_distribution_id
4003 AND tds_tax.invoice_id = p_invoice_id
4004 AND tds_tax.tax_category_id = ln_tax_category_id);
4005 ELSE
4006 update jai_ap_tds_prepayments tds_prepay
4007 set essi_threshold_trx_id_apply = ln_threshold_trx_id_essi
4008 where invoice_id = p_invoice_id
4009 and invoice_distribution_id_prepay = p_invoice_distribution_id
4010 and essi_applicable_flag = 'Y';
4011 END IF;
4012 if ln_start_threshold_trx_id is null then
4013 ln_start_threshold_trx_id := ln_threshold_trx_id_essi;
4014 end if;
4015 end if; /*IF p_event = 'INSERT' then*/ --Added for Bug 8431516
4016 END LOOP;
4017 end if; /* if r_get_total_prepayment_tax.essi_amount > 0 then */
4018
4019 if ln_start_threshold_trx_id is not null then
4020 /*
4021 Records are inserted into AP Interface tables using Standard Invoice, but import_and_approve
4022 was called using the Prepayment Invoice ID. Hence wrong group_id was getting passed and no
4023 Invoices were getting improved*/
4024 jai_ap_dtc_generation_pkg.import_and_approve
4025 (
4026 p_invoice_id => ln_parent_invoice_id,
4027 p_start_thhold_trx_id => ln_start_threshold_trx_id,
4028 p_tds_event => 'PREPAYMENT APPLICATION',
4029 p_process_flag => p_process_flag,
4030 p_process_message => p_process_message
4031 );
4032
4033 end if;
4034
4035 << exit_from_procedure >>
4036 p_codepath := jai_general_pkg.plot_codepath(100, p_codepath, null, 'END'); /* 100 */
4037 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_api_name||'.END', G_PKG_NAME || ': '||l_api_name||'()-');
4038
4039 return;
4040
4041 exception
4042 when others then
4043 p_process_flag := 'E';
4044 P_process_message := 'jai_ap_tds_prepayemnts_pkg.process_tds_invoices :' || sqlerrm;
4045 return;
4046 end generate_rtn;
4047
4048
4049 PROCEDURE get_prepay_invoice_gl_date
4050 (
4051 p_prepay_inv_dist_id NUMBER,
4052 p_prepay_gl_date OUT NOCOPY DATE
4053 )
4054 IS
4055 PRAGMA AUTONOMOUS_TRANSACTION;
4056 BEGIN
4057 BEGIN
4058 SELECT accounting_date
4059 INTO p_prepay_gl_date
4060 FROM ap_invoice_distributions_all
4061 WHERE invoice_distribution_id = p_prepay_inv_dist_id;
4062 EXCEPTION
4063 WHEN NO_DATA_FOUND THEN
4064 p_prepay_gl_date := NULL;
4065 END;
4066 END get_prepay_invoice_gl_date;
4067
4068 FUNCTION wct_essi_applicable(cn_invoice_id NUMBER, cn_distribution_id NUMBER, cv_section_type VARCHAR2)
4069 RETURN VARCHAR2 IS
4070 CURSOR wct_essi_applicable IS
4071 SELECT 'Y' FROM JAI_AP_TDS_INV_TAXES
4072 WHERE INVOICE_ID = cn_invoice_id
4073 AND INVOICE_DISTRIBUTION_ID = cn_distribution_id
4074 AND SECTION_TYPE = cv_section_type;
4075 lv_applicable VARCHAR2(1) := 'N';
4076 BEGIN
4077 OPEN wct_essi_applicable;
4078 FETCH wct_essi_applicable INTO lv_applicable;
4079 CLOSE wct_essi_applicable;
4080 RETURN lv_applicable;
4081 END wct_essi_applicable;
4082
4083 FUNCTION get_tax_category(cn_distribution_id NUMBER)
4084 RETURN NUMBER IS
4085 /* --Commented by Chong for issue120920-66 2012/10/09
4086 CURSOR get_tax_category IS
4087 SELECT DISTINCT tax_category_id FROM JAI_AP_TDS_INV_TAXES
4088 WHERE INVOICE_DISTRIBUTION_ID = cn_distribution_id;
4089 */
4090 --Added by Chong for issue120920-66 2012/10/09 start
4091 --------------------------------------------------------------
4092 CURSOR get_tax_category IS
4093 SELECT DISTINCT jatit.tax_category_id
4094 FROM JAI_AP_TDS_INV_TAXES jatit
4095 ,(
4096 SELECT DISTINCT invoice_id
4097 ,actual_section_code
4098 FROM JAI_AP_TDS_INV_TAXES
4099 WHERE INVOICE_DISTRIBUTION_ID = cn_distribution_id
4100 ) jatit_sct
4101 WHERE jatit.invoice_id =jatit_sct.invoice_id
4102 AND jatit.actual_section_code =jatit_sct.actual_section_code
4103 AND jatit.tax_category_id IS NOT NULL;
4104 --------------------------------------------------------------
4105 --Added by Chong for issue120920-66 2012/10/09 end
4106
4107 ln_tax_category_id NUMBER;
4108 BEGIN
4109 OPEN get_tax_category;
4110 FETCH get_tax_category INTO ln_tax_category_id;
4111 CLOSE get_tax_category;
4112
4113 jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_PROCEDURE, G_MODULE_NAME||'.get_tax_category', 'cn_distribution_id: '|| cn_distribution_id || ' ln_tax_category_id' || ln_tax_category_id );
4114
4115 RETURN ln_tax_category_id;
4116 END get_tax_category;
4117
4118 end jai_ap_dtc_prepayments_pkg;