1 PACKAGE BODY jai_ap_tds_old_pkg
2 /* $Header: jai_ap_tds_old.plb 120.8 2007/05/10 09:46:39 rallamse ship $ */
3 AS
4
5 /*------------------------------------------------------------------------------------------
6 CHANGE HISTORY for FILENAME: jai_ap_tds_old.plb
7 ------------------------------------------------------------------------------------------
8 S.No Date Author and Details
9 ------------------------------------------------------------------------------------------
10 1. 02-Sep-2005 Ramananda for Bug#4584221. File Version 120.3
11 1. Procedure PROCESS_PREPAYMENT_UNAPPLY
12 ===================================
13 Made the following changes
14 1) Before submitting the request - APXIIMPT,
15 called the jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id) to get the batch_name.
16 2) In submitting the request - APXIIMPT,
17 changed the parameter batch_name from hardcoded value to variable - lv_batch_name
18
19 2. Procedure PROCESS_PREPAYMENT_APPLY
20 ==================================
21 Made the following changes -
22 1) Before submitting the request - APXIIMPT,
23 called the jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id) to get the batch_name.
24 2) In submitting the request - APXIIMPT,
25 changed the parameter batch_name from hardcoded value to variable - lv_batch_name
26
27 3. Procedure CANCEL_INVOICE
28 ========================
29 Made the following changes -
30 1) Before submitting the request - APXIIMPT,
31 called the jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id) to get the batch_name.
32 2) In submitting the request - APXIIMPT,
33 changed the parameter batch_name from hardcoded value to variable - lv_batch_name
34
35 Dependency Due to this Bug (Functional)
36 --------------------------
37 jai_ap_utils.pls (120.2)
38 jai_ap_utils.plb (120.2)
39 jai_ap_tds_gen.plb (120.8)
40 jai_constants.pls (120.3)
41 jaiorgdffsetup.sql (120.2)
42 jaivmlu.ldt 120.3
43
44 2. 02-Dec-2005 Bug 4774647. Added by Lakshmi Gopalsami Version 120.4
45 Passed operating unit also as this parameter
46 has been added by base .
47
48 3 07/12/2005 Hjujjuru ,File version 120.5
49 Bug 4866533
50 added the who columns in the insert of JAI_CMN_ERRORS_T
51 Dependencies Due to this bug:-
52 None
53 Bug 4870243, File version 120.5
54 Issue : Invoice Import Program is rejecting the Invoices.
55 Fix : Commented the voucher_num insert into the ap_invoices_interface table
56 4 23/02/07 bduvarag for bug#4716884,File version 120.7
57 Forward porting the changes done in 11i bug 4629783
58
59 ------------------------------------------------------------------------------------------------------------------------*/
60
61 PROCEDURE cancel_invoice
62 (
63 errbuf OUT NOCOPY VARCHAR2,
64 retcode OUT NOCOPY VARCHAR2,
65 p_invoice_id IN NUMBER --cbabu Bug#2448040
66 ) IS
67
68 --cbabu Bug#2448040
69 lv_statement_no VARCHAR2(3); -- := '0'; --Ramananda for File.Sql.35
70 lv_procedure_name VARCHAR2(25); -- := 'jai_ap_tds_old_pkg.cancel_invoice'; --Ramananda for File.Sql.35
71 lv_error_mesg VARCHAR2(255); -- := ''; --Ramananda for File.Sql.35
72
73 /*CURSOR cancelled_invoices IS
74 SELECT *
75 FROM ja_in_ap_inv_cancel_temp;
76 */
77
78 --cbabu Bug#2448040
79 /* TDS clean up > commented the cursor definition by the one below
80
81 CURSOR cancelled_invoices(p_inv_id IN NUMBER) IS
82 SELECT *
83 FROM ja_in_ap_inv_cancel_temp
84 WHERE invoice_id = p_inv_id;*/
85
86 cursor cancelled_invoices(p_inv_id in number) is
87 select
88 invoice_id,
89 invoice_num,
90 vendor_id,
91 org_id,
92 creation_date,
93 created_by,
94 last_update_date,
95 last_updated_by,
96 last_update_login
97 from ap_invoices_all
98 where invoice_id = p_inv_id;
99
100 CURSOR tds_invoices(inv_id NUMBER) IS
101 SELECT tds_invoice_num,tds_tax_id,dm_invoice_num,tds_amount,tds_tax_rate,invoice_amount -- 4333449
102 FROM JAI_AP_TDS_INVOICES
103 WHERE invoice_id = inv_id;
104
105 CURSOR vendor(t_id NUMBER) IS
106 SELECT vendor_id
107 FROM JAI_CMN_TAXES_ALL
108 WHERE tax_id = t_id;
109
110
111 CURSOR for_payment_status(inv_num VARCHAR2,vend_id NUMBER,organization NUMBER) IS ----for information about tds invoice
112 SELECT payment_status_flag,invoice_amount,invoice_id, cancelled_date
113 FROM ap_invoices_all
114 WHERE invoice_num = inv_num
115 AND vendor_id = vend_id
116 AND NVL(org_id, 0) = NVL(organization, 0);
117
118 CURSOR for_distribution_insertion(inv_id NUMBER) IS
119 SELECT distribution_line_number,accounting_date,accrual_posted_flag,reversal_flag,
120 assets_addition_flag,assets_tracking_flag,cash_posted_flag,dist_code_combination_id,
121 accts_pay_code_combination_id,
122 period_name,set_of_books_id,
123 amount,match_status_flag,base_amount_to_post,prepay_amount_remaining,
124 parent_invoice_id,org_id,description
125 FROM ap_invoice_distributions_all
126 WHERE invoice_id = inv_id
127 AND distribution_line_number = (SELECT MAX(distribution_line_number)
128 FROM ap_invoice_distributions_all
129 WHERE invoice_id = inv_id);
130
131 CURSOR for_std_invoice(inv_id NUMBER) IS
132 SELECT invoice_type_lookup_code,vendor_id,vendor_site_id,invoice_currency_code,
133 exchange_rate,exchange_rate_type,exchange_date,terms_id,payment_method_lookup_code,
134 pay_group_lookup_code,goods_received_date,invoice_received_date --added on 03-12-2001
135 FROM ap_invoices_all
136 WHERE invoice_id = inv_id;
137
138 CURSOR Fetch_Inv_Date_Cur( inv_id IN NUMBER ) IS
139 SELECT Invoice_date
140 FROM Ap_Invoices_All
141 WHERE Invoice_Id = inv_id;
142
143 CURSOR Fetch_App_Inv_Flag_Cur( p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER ) IS
144 SELECT NVL( Approved_Invoice_Flag, 'N' ) Approved_Invoice_Flag
145 FROM JAI_CMN_VENDOR_SITES
146 WHERE Vendor_Id = p_vendor_id
147 AND Vendor_Site_Id = p_vendor_site_id;
148
149 -- start added for bug#3556035
150 cursor c_get_can_inv_amount(p_invoice_num in varchar2,
151 p_vendor_id in number,
152 p_vendor_site_id in number
153 ) is
154 select -1 * invoice_amount
155 from ap_invoices_all
156 where invoice_num = p_invoice_num
157 and vendor_id = p_vendor_id
158 and vendor_site_id = p_vendor_site_id;
159
160 CURSOR cur_prnt_pay_priority -- 4333449
161 IS
162 SELECT payment_priority
163 FROM ap_payment_schedules_all
164 WHERE invoice_id = p_invoice_id;
165
166 CURSOR cur_prnt_exchange_rate -- 4333449
167 IS
168 SELECT exchange_rate
169 FROM ap_invoices_all
170 WHERE invoice_id = p_invoice_id;
171
172 -- end added for bug#3556035
173
174
175
176 for_pay_status_tds_rec for_payment_status%ROWTYPE;
177 for_pay_status_original_rec for_payment_status%ROWTYPE;
178 for_distribution_insertion_rec for_distribution_insertion%ROWTYPE;
179 for_std_invoice_rec for_std_invoice%ROWTYPE;
180 for_insertion_invoice_id NUMBER;
181 insertion_amount NUMBER := 0;
182 new_std_invoice_amount NUMBER := 0;
183 cancelled_invoices_rec cancelled_invoices%ROWTYPE;
184 vendor_rec vendor%ROWTYPE;
185 tds_invoices_rec tds_invoices%ROWTYPE;
186 result BOOLEAN;
187 req_id NUMBER;
188 req1_id NUMBER;
189
190
191 v_invoice_date NUMBER;
192 v_flag VARCHAR2(1); -- := 'N'; --Ramananda for File.Sql.35
193 v_approve_flag VARCHAR2(1);
194
195 v_Approved_Invoice_Flag CHAR(1); -- := 'N'; -- added by Aparajita on 29/07/2002 for bug # 2475416 --Ramananda for File.Sql.35
196
197 -- start added by Aparajita on 05/11/2002 for bug # 2586784
198 v_open_period gl_period_statuses.period_name%type;
199 v_open_gl_date date;
200 -- start added by Aparajita on 05/11/2002 for bug # 2586784
201
202 -- start for bug#3536079
203 v_out_message_name varchar2(240);
204 v_out_invoice_amount number;
205 v_out_base_amount number;
206 v_out_tax_amount number;
207 v_out_temp_cancelled_amount number;
208 v_out_cancelled_by number;
209 v_out_cancelled_amount number;
210 v_out_cancelled_date date;
211 v_out_last_update_date date;
212 v_out_original_prepay_amount number;
213 v_out_pay_curr_inv_amount number;
214 v_return_value boolean;
215 ln_invoice_id NUMBER; --4333449
216 ln_prnt_pay_priority ap_payment_schedules_all.payment_priority%TYPE; -- 4333449
217 ln_prnt_exchange_rate ap_invoices_all.exchange_rate%TYPE; --4333449
218 lv_invoice_num ap_invoices_interface.invoice_num%type; --rchandan for bug#4428980
219 lv_description ap_invoices_interface.description%type;--rchandan for bug#4428980
220 lv_lookup_type_code ap_invoices_interface.invoice_type_lookup_code%type;--rchandan for bug#4428980
221 lv_source ap_invoices_interface.source%type;--rchandan for bug#4428980
222 lv_voucher_num ap_invoices_interface.voucher_num%type;--rchandan for bug#4428980
223 -- End for bug#3536079
224
225 -- bug#3607133
226 cursor c_get_cm_status(p_invoice_num in varchar2,
227 p_vendor_id in number,
228 p_vendor_site_id in number)is
229 select cancelled_date
230 from ap_invoices_all
231 where invoice_num = p_invoice_num
232 and vendor_id = p_vendor_id
233 and vendor_site_id = p_vendor_site_id ;
234
235 v_cancelled_date_cm date;
236
237 -- bug#3607133
238
239
240 /* start additions by ssumaith - bug# 4448789 */
241 ln_legal_entity_id NUMBER;
242 lv_legal_entity_name VARCHAR2(240);
243 lv_return_status VARCHAR2(100);
244 ln_msg_count NUMBER;
245 ln_msg_data VARCHAR2(1000);
246 /* ends additions by ssumaith - bug# 4448789*/
247
248 lv_token VARCHAR2(100);
249
250 lv_batch_name ap_batches_all.batch_name%TYPE; --added by Ramananda for Bug#4584221
251
252 BEGIN
253
254 /* Cancellation of invoice */
255 /*------------------------------------------------------------------------------------------
256 CHANGE HISTORY for FILENAME: jai_ap_tds_old_pkg.cancel_invoice_p.sql
257 S.No Date Author and Details
258 ------------------------------------------------------------------------------------------
259 1 03-Dec-2001 Pavan : Code modified to populate the fields goods_received_date,
260 invoice_received_date into interfaces inorder to prevent it from
261 getting stuck up in interfaces
262
263 2 04-JUL-2002 Vijay Shankar: Code added to process only one invoice and delete the same
264 Bug#2448040
265
266 3. 29-july-2002 Aparajita for bug # 2475416
267 Approved TDS and CM setup was allowed at null site, so changed the code to look into null site setup
268 if the site setup does not exist for the vendor for pre approved TDS and CM.
269 This was done to find out if the approval of TDS an CM concurrent needs to be submitted.
270
271 4. 23-sep-2002 Aparajita for bug # 2503751
272 Populate the invoice id of the original invoice in attribute1 of the tds related invoice for
273 context value 'India Original Invoice for TDS'
274
275 5. 05-nov-2002 Aparajita for bug # 2586784
276 While generating the negative distribution line, checking for open/close accounting
277 period for accounting date.
278
279 Using ap_utilities_pkg procedures get_current_gl_date and get_open_gl_date.
280
281 6. 26-MAR-2003 Vijay Shankar for Bug# 2869481, FileVersion# 615.4
282 when an invoice is cancelled, then related TDS invoices also gets cancelled. upon cancelling the tds invoice,
283 ap_invoice_payments_all should get updated
284 with amount_remaining as 0 which is not happening previously and fixed with this bug.
285 An update statement is written on ap_payment_schedules_all to update amount_remaining and gross_amount fields
286
287 7. 27-apr-2003 Aparajita for bug#2906202. Version#616.1
288 The negative distribution line in the TDS invoice, has the period name of the original distribution line it was reversing.
289 Added the check to populate the period name of the current accounting period.
290
291 8. 15-oct-2003 kpvs for bug # 3109138, version # 616.2
292 Changed the procedure to insert v_open_gl_date instead of trunc(sysdate)
293 as invoice_date into ap_invoices_interface and as accounting_date into
294 ap_invoice_lines_interface.
295 This is for the invoice_date and GL date of the SI created for
296 supplier on cancellation of the vendor invoice.
297
298 9. 29-oct-2003 Aparajita for bug#3205957. Version#616.3
299 When the base invoice is getting cancelled, this procedure is cancelling the related tds
300 invoice. It was not updating the pay_curr_invoice_amount field in ap_invoices_all for the
301 tds invoice. Because of this the amount was still being displayed as before cancellation
302 in the payment schedule screen. Added code to update it to 0.
303
304 10. 8-apr-2004 Aparajita for bug#3536079. Version#619.1
305
306 TDS invoice was being cancelled manually, that is being manually updated
307 by our code. This was creating problem as in this case accounting was not
308 being done for the reversed line as all info like accounting event id is
309 not being populated. Moreoevr, with any additional change / validation
310 in base, we had to change the code.
311
312 To avoid all this, used base API ap_cancel_pkg.Ap_Cancel_Single_Invoice
313 to cancel the TDS invoice. This way,much of the task is being done by base
314 and hence no inconsistency.
315
316 11 12-apr-2004 Aparajita for bug#3556035. Version#619.2
317
318 When the stadard invoice being cancelled is in forex, the
319 cancellation invoice generated for rversing the TDS amount was
320 getting generated in wrong currency. Amount always in INR, but
321 currency same as the stadard invoice.
322
323 Added a cursor c_get_can_inv_amount to fetch the amount from
324 the credit memo created initially for the invoice.
325
326 12. 5-may-2004 Aparajita for bug#3607133. Version#619.3
327 This procedure gets invoked whenever a base invoice having TDS is cancelled.
328 The functionality required here is to cancel the related TDS invoice and generate
329 a CAN invoice to negate the credit memo.
330
331 The problem is if the credit memo is already cancelled, there is no need to
332 generate the CAN invoice to negate it. Added a cursor to check the status
333 of the credit memo before creating the CAN invoice.
334
335 13. 31-jan-2005 rchandan - bug#4149343 - File version 115.1
336
337 When an invoice is cancelled , it was not getting cancelled and instead throwing an error
338 cannot update null into ap_invoices_all.last_update_date. This error was captured in
339 JAI_CMN_ERRORS_T on the client's instance through an exception handler.
340 The value to be set into this column was retreived through an API call to ap_cancel_pkg.Ap_Cancel_Single_Invoice
341
342 Due to some internal exception, the out parameter corresponding to the the last_update_date is null
343
344 Spoke to lgopalsa she was of the opinion that in the ap_cancel_pkg.Ap_Cancel_Single_Invoice API ,
345 the standard who columns are being set anyway and there is no need to set it explicitly in the
346 procedure.
347
348 As part of this fix , removed the last_update_date from the columns in the update ap_invoices_all
349 table.
350
351 14 25/3/2005 Aparajita - Bug#4088186 . File Version # 115.3 TDS Clean up.
352
353 Removed usage of table ja_in_ap_inv_cancel_temp.
354
355 15. 02/05/2005 rchandan for bug#4333449. Version 116.1
356 India Original Invoice for TDS DFF is eliminated. So attribute1 of ap_invoices_al
357 is not populated whenevr an invoice is generated. Instead the Invoice details are
358 populated into jai_ap_tds_thhold_trxs. So whenever data is inserted into interface
359 tables the jai_ap_tds_thhold_trxs table is also populated.
360
361 16. 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
362 Changed AP Lookup code from 'TDS' to 'INDIA TDS'
363
364 17. 02/05/2005 Ramananda for bug#4407165 File Version: 116.2
365 Added Exception block for non-compliant procedures and functions
366
367 18. 02/05/2005 Ramananda for bug# 4407184 File Version: 116.3
368 SQL Bind Varibale Compliance is done
369
370 19. 08-Jun-2005 Version 116.4 jai_ap_tds_old -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
371 as required for CASE COMPLAINCE.
372
373 20. 14-Jun-2005 rchandan for bug#4428980, Version 116.5
374 Modified the object to remove literals from DML statements and CURSORS.
375
376 21. 02/05/2005 Ramananda for bug# 4407184 File Version: 116.6
377 Re-done: SQL Bind Varibale Compliance
378
379 22. 23-Aug-2005 Bug 4559756. Added by Lakshmi Gopalsami version 120.2
380 Passed org_id in call to ap_utilities_pkg to get the correct gl_date and period_name
381
382
383 --------------------------------------------------------------------------------------------*/
384
385 -- OPEN cancelled_invoices;
386
387 /* Ramananda for File.Sql.35 */
388 lv_statement_no := '0';
389 lv_procedure_name := 'jai_ap_tds_old_pkg.cancel_invoice';
390 lv_error_mesg := '';
391 v_flag := 'N';
392 v_Approved_Invoice_Flag := 'N'; -- added by Aparajita on 29/07/2002 for bug # 2475416
393
394 /* Ramananda for File.Sql.35 */
395
396
397 lv_statement_no := '1';
398 OPEN cancelled_invoices(p_invoice_id); --cbabu Bug#2448040
399 FETCH cancelled_invoices INTO cancelled_invoices_rec;
400 CLOSE cancelled_invoices;
401
402 OPEN cur_prnt_pay_priority; -- 4333449
403 FETCH cur_prnt_pay_priority INTO ln_prnt_pay_priority;
404 CLOSE cur_prnt_pay_priority;
405
406 OPEN cur_prnt_exchange_rate; -- 4333449
407 FETCH cur_prnt_exchange_rate INTO ln_prnt_exchange_rate;
408 CLOSE cur_prnt_exchange_rate;
409
410 lv_statement_no := '2';
411 OPEN tds_invoices(cancelled_invoices_rec.invoice_id);
412
413 LOOP
414 FETCH tds_invoices INTO tds_invoices_rec;
415 IF tds_invoices%NOTFOUND THEN
416 EXIT; --RETURN;
417 END IF;
418
419 lv_statement_no := '3';
420
421 OPEN vendor(tds_invoices_rec.tds_tax_id);
422 FETCH vendor INTO vendor_rec;
423 CLOSE vendor;
424
425 lv_statement_no := '4';
426 OPEN for_payment_status(tds_invoices_rec.tds_invoice_num,vendor_rec.vendor_id, cancelled_invoices_rec.org_id);
427 FETCH for_payment_status INTO for_pay_status_tds_rec;
428 CLOSE for_payment_status;
429
430 if for_pay_status_tds_rec.cancelled_date is not null then -- bug#3607133
431 Fnd_File.put_line(Fnd_File.LOG, 'The TDS invoice has already been cancelled');
432 end if;
433
434 IF for_pay_status_tds_rec.payment_status_flag = 'N' THEN --1
435
436 lv_statement_no := '5';
437
438 /* Start comment for bug#3536079
439
440 UPDATE ap_invoices_all
441 SET cancelled_date = SYSDATE,
442 cancelled_amount = for_pay_status_tds_rec.invoice_amount,
443 cancelled_by = cancelled_invoices_rec.last_updated_by,
444 base_amount = 0,
445 invoice_amount = 0,
446 pay_curr_invoice_amount = 0 -- added by bug#3205957
447 WHERE invoice_num = tds_invoices_rec.tds_invoice_num
448 AND vendor_id = vendor_rec.vendor_id
449 AND NVL(org_id, 0) = NVL(cancelled_invoices_rec.org_id, 0);
450
451 -- cbabu for Bug# 2869481
452 UPDATE ap_payment_schedules_all
453 SET gross_amount = 0,
454 amount_remaining = 0
455 -- , inv_curr_gross_amount = 0
456 -- base applications is not updating this field when a standard invoice is cancelled. so not updating this field in our code also
457 WHERE invoice_id in (select invoice_id
458 from ap_invoices_all
459 WHERE invoice_num = tds_invoices_rec.tds_invoice_num
460 AND vendor_id = vendor_rec.vendor_id
461 AND NVL(org_id, 999999) = NVL(cancelled_invoices_rec.org_id, 999999)
462 );
463
464 End comment for bug#3536079 */
465
466 lv_statement_no := '6';
467
468 OPEN for_distribution_insertion(for_pay_status_tds_rec.invoice_id);
469 FETCH for_distribution_insertion INTO for_distribution_insertion_rec;
470 CLOSE for_distribution_insertion;
471
472 -- new_std_invoice_amount := for_pay_status_tds_rec.invoice_amount; Commented for bug#3556035
473
474 lv_statement_no := '7';
475 OPEN for_std_invoice(cancelled_invoices_rec.invoice_id);
476 FETCH for_std_invoice INTO for_std_invoice_rec;
477 CLOSE for_std_invoice;
478
479 -- Start added for bug#3556035
480 if for_std_invoice_rec.invoice_currency_code <> 'INR' then
481 open c_get_can_inv_amount( tds_invoices_rec.dm_invoice_num,
482 for_std_invoice_rec.vendor_id,
483 for_std_invoice_rec.vendor_site_id);
484 fetch c_get_can_inv_amount into new_std_invoice_amount;
485 close c_get_can_inv_amount;
486 else
487 new_std_invoice_amount := for_pay_status_tds_rec.invoice_amount;
488 end if;
489 -- end added for bug#3556035
490
491
492
493 --REVERSE THE DISRTIBUTION LINE IN THE TDS INVOICE
494 /*UPDATE ap_invoices_all
495 SET invoice_amount = 0
496 WHERE invoice_id = for_pay_status_tds_rec.invoice_id;
497 */
498
499 lv_statement_no := '8';
500
501 -- start added by Aparajita on 05/11/2002 for bug # 2586784
502
503 /* Bug 4559756. Added by Lakshmi Gopalsami
504 Passed org_id to ap_utilities_pkg
505 */
506
507 v_open_period := ap_utilities_pkg.get_current_gl_date(
508 for_distribution_insertion_rec.accounting_date,
509 cancelled_invoices_rec.org_id);
510
511 /* Bug 4559756. Added by Lakshmi Gopalsami
512 Passed org_id to ap_utilities_pkg
513 */
514
515 if v_open_period is null then
516
517 ap_utilities_pkg.get_open_gl_date
518 (
519 for_distribution_insertion_rec.accounting_date,
520 v_open_period,
521 v_open_gl_date,
522 cancelled_invoices_rec.org_id
523 );
524
525 if v_open_period is null then
526 raise_application_error(-20001,'No Open period ... after '||for_distribution_insertion_rec.accounting_date);
527 end if;
528
529 else
530
531 v_open_gl_date := for_distribution_insertion_rec.accounting_date;
532 v_open_period := for_distribution_insertion_rec.period_name; -- bug#2906202
533
534 end if;
535
536 -- end added by Aparajita on 05/11/2002 for bug # 2586784
537
538 -- Start for bug#3536079
539 v_return_value :=
540 ap_cancel_pkg.Ap_Cancel_Single_Invoice
541 (
542 for_pay_status_tds_rec.invoice_id ,
543 cancelled_invoices_rec.last_updated_by,
544 cancelled_invoices_rec.last_update_login ,
545 --for_distribution_insertion_rec.set_of_books_id ,
546 v_open_gl_date ,
547 --v_open_period ,
548 v_out_message_name ,
549 v_out_invoice_amount ,
550 v_out_base_amount ,
551 --v_out_tax_amount ,
552 v_out_temp_cancelled_amount ,
553 v_out_cancelled_by ,
554 v_out_cancelled_amount ,
555 v_out_cancelled_date ,
556 v_out_last_update_date ,
557 v_out_original_prepay_amount,
558 --null, -- check_id ,
559 v_out_pay_curr_inv_amount ,
560 lv_token,
561 'India Localization - cancel TDS invoice'
562 );
563
564
565 update ap_invoices_all
566 set
567 invoice_amount = v_out_invoice_amount ,
568 base_amount = v_out_base_amount ,
569 tax_amount = v_out_tax_amount ,
570 temp_cancelled_amount = v_out_temp_cancelled_amount ,
571 cancelled_by = v_out_cancelled_by ,
572 cancelled_amount = v_out_cancelled_amount ,
573 cancelled_date = v_out_cancelled_date ,---4149343
574 original_prepayment_amount = v_out_original_prepay_amount,
575 pay_curr_invoice_amount = v_out_pay_curr_inv_amount
576 where invoice_id = for_pay_status_tds_rec.invoice_id;
577
578
579 -- End for bug#3536079
580
581
582 /* Start comment for bug#3536079
583
584 INSERT INTO ap_invoice_distributions_all
585 (
586 accounting_date,
587 accrual_posted_flag,
588 reversal_flag,
589 assets_addition_flag,
590 assets_tracking_flag,
591 cash_posted_flag,
592 distribution_line_number,
593 dist_code_combination_id,
594 accts_pay_code_combination_id,
595 invoice_id,
596 period_name,
597 set_of_books_id,
598 amount,
599 match_status_flag,
600 base_amount_to_post,
601 prepay_amount_remaining,
602 parent_invoice_id,
603 line_type_lookup_code,
604 last_updated_by,
605 last_update_date,
606 org_id,
607 invoice_distribution_id, -- Added on 15-Sep-2000
608 description,
609 posted_flag
610 )
611 VALUES
612 (
613 v_open_gl_date, -- for_distribution_insertion_rec.accounting_date, commented by Aparajita on 05/11/2002 for bug # 2586784
614 'N', --for_distribution_insertion_rec.accrual_posted_flag,
615 'Y',
616 for_distribution_insertion_rec.assets_addition_flag,
617 for_distribution_insertion_rec.assets_tracking_flag,
618 for_distribution_insertion_rec.cash_posted_flag,
619 for_distribution_insertion_rec.distribution_line_number + 1,
620 for_distribution_insertion_rec.dist_code_combination_id,
621 for_distribution_insertion_rec.accts_pay_code_combination_id,
622 for_pay_status_tds_rec.invoice_id,
623 v_open_period, -- for_distribution_insertion_rec.period_name, bug#2906202
624 for_distribution_insertion_rec.set_of_books_id,
625 (-1)*for_pay_status_tds_rec.invoice_amount,
626 for_distribution_insertion_rec.match_status_flag,
627 for_distribution_insertion_rec.base_amount_to_post,
628 for_distribution_insertion_rec.prepay_amount_remaining,
629 for_distribution_insertion_rec.parent_invoice_id,
630 'ITEM',
631 cancelled_invoices_rec.last_updated_by,
632 cancelled_invoices_rec.last_update_date,
633 for_distribution_insertion_rec.org_id,
634 ap_invoice_distributions_s.NEXTVAL, -- Added on 15-sep-2000
635 for_distribution_insertion_rec.description,
636 'N'
637 );
638
639 End comment for bug#3536079 */
640
641
642 --GENERATE A STANDARD INVOICE FOR THE AMOUNT EQUAL TO THAT OF THE TDS INVOICE
643 lv_statement_no := '9';
644 -- start bug#3607133
645 open c_get_cm_status
646 (tds_invoices_rec.dm_invoice_num,
647 for_std_invoice_rec.vendor_id,
648 for_std_invoice_rec.vendor_site_id
649 );
650 fetch c_get_cm_status into v_cancelled_date_cm;
651 close c_get_cm_status;
652
653 if v_cancelled_date_cm is not null then
654 Fnd_File.put_line(Fnd_File.LOG,
655 'Not generating cancelled invoice as the supplier CM is already cancelled');
656 v_flag := 'N';
657
658 else
659 -- end bug#3607133
660 lv_invoice_num := 'CAN/'||SUBSTR(tds_invoices_rec.dm_invoice_num,1,47); --rchandan for bug#4428980
661 lv_description := 'Invoice Generated in lieu of Cancelled TDS Invoice'; --rchandan for bug#4428980
662 lv_lookup_type_code := 'STANDARD'; --rchandan for bug#4428980
663 lv_source := 'INDIA TDS'; --rchandan for bug#4428980
664
665
666 /* start additions by ssumaith - bug# 4448789 */
667 jai_cmn_utils_pkg.GET_LE_INFO(
668 P_API_VERSION => NULL ,
669 P_INIT_MSG_LIST => NULL ,
670 P_COMMIT => NULL ,
671 P_LEDGER_ID => NULL,
672 P_BSV => NULL,
673 P_ORG_ID => cancelled_invoices_rec.org_id,
674 X_RETURN_STATUS => lv_return_status ,
675 X_MSG_COUNT => ln_msg_count,
676 X_MSG_DATA => ln_msg_data,
677 X_LEGAL_ENTITY_ID => ln_legal_entity_id ,
678 X_LEGAL_ENTITY_NAME => lv_legal_entity_name
679 );
680 /* ends additions by ssumaith - bug# 4448789*/
681
682
683 INSERT INTO ap_invoices_interface
684 (
685 invoice_id,
686 invoice_num,
687 invoice_type_lookup_code,
688 invoice_date,
689 vendor_id,
690 vendor_site_id,
691 invoice_amount,
692 invoice_currency_code,
693 exchange_rate,
694 exchange_rate_type,
695 exchange_date,
696 terms_id,
697 description,
698 source,
699 --voucher_num, Harshita for Bug 4870243
700 payment_method_lookup_code,
701 pay_group_lookup_code,
702 org_id,
703 legal_entity_id ,
704 created_by,
705 creation_date,
706 last_updated_by,
707 last_update_date,
708 last_update_login,
709 goods_received_date,
710 invoice_received_date,
711 group_id /*Bug 4716884 bduvarag*/
712 )
713 VALUES
714 (
715 ap_invoices_interface_s.NEXTVAL,
716 lv_invoice_num,
717 lv_lookup_type_code,
718 v_open_gl_date, -- bug 3109138 TRUNC( SYSDATE ),
719 for_std_invoice_rec.vendor_id,
720 for_std_invoice_rec.vendor_site_id,
721 new_std_invoice_amount,
722 for_std_invoice_rec.invoice_currency_code,
723 for_std_invoice_rec.exchange_rate,
724 for_std_invoice_rec.exchange_rate_type,
725 for_std_invoice_rec.exchange_date,
726 for_std_invoice_rec.terms_id,
727 lv_description, --rchandan for bug#4428980
728 lv_source, /*--'TDS', --Ramanand for bug#4388958*/ --rchandan for bug#4428980
729 -- lv_invoice_num, --rchandan for bug#4428980 , Harshita for Bug 4870243
730 for_std_invoice_rec.payment_method_lookup_code,
731 for_std_invoice_rec.pay_group_lookup_code,
732 cancelled_invoices_rec.org_id,
733 ln_legal_entity_id ,
734 cancelled_invoices_rec.created_by,
735 cancelled_invoices_rec.creation_date,
736 cancelled_invoices_rec.last_updated_by,
737 cancelled_invoices_rec.last_update_date,
738 cancelled_invoices_rec.last_update_login,
739 for_std_invoice_rec.goods_received_date, --Added on 03-Dec-2001
740 for_std_invoice_rec.invoice_received_date,
741 to_char(p_invoice_id) /*ug 4716884 bduvarag*/
742 );
743
744 lv_statement_no := '10';
745 lv_lookup_type_code := 'ITEM'; --rchandan for bug#4428980
746 INSERT INTO ap_invoice_lines_interface
747 (
748 invoice_id,
749 invoice_line_id,
750 line_number,
751 line_type_lookup_code,
752 amount,
753 accounting_date,
754 description,
755 dist_code_combination_id,
756 created_by,
757 creation_date,
758 last_updated_by,
759 last_update_date,
760 last_update_login
761 )
762 VALUES
763 (
764 ap_invoices_interface_s.CURRVAL,
765 ap_invoice_lines_interface_s.NEXTVAL,
766 1, --THERE WILL ALWAYS BE ONLY ONE LINE.
767 lv_lookup_type_code, --rchandan for bug#4428980
768 new_std_invoice_amount,
769 v_open_gl_date, -- bug 3109138 TRUNC( SYSDATE ),
770 lv_description, --rchandan for bug#4428980
771 for_distribution_insertion_rec.dist_code_combination_id,
772 cancelled_invoices_rec.created_by,
773 cancelled_invoices_rec.creation_date,
774 cancelled_invoices_rec.last_updated_by,
775 cancelled_invoices_rec.last_update_date,
776 cancelled_invoices_rec.last_update_login
777 );
778
779
780 jai_ap_tds_generation_pkg.insert_tds_thhold_trxs -------4333449
781 (
782 p_invoice_id => p_invoice_id,
783 p_tds_event => 'OLD TDS INVOICE CANCEL',
784 p_tax_id => tds_invoices_rec.tds_tax_id,
785 p_tax_rate => tds_invoices_rec.tds_tax_rate,
786 p_taxable_amount => tds_invoices_rec.invoice_amount,
787 p_tax_amount => tds_invoices_rec.tds_amount,
788 p_vendor_id => for_std_invoice_rec.vendor_id,
789 p_vendor_site_id => for_std_invoice_rec.vendor_site_id,
790 p_invoice_vendor_num => 'CAN/'||SUBSTR(tds_invoices_rec.dm_invoice_num,1,47),
791 p_invoice_vendor_type => 'STANDARD',
792 p_invoice_vendor_curr => for_std_invoice_rec.invoice_currency_code,
793 p_invoice_vendor_amt => new_std_invoice_amount,
794 p_parent_inv_payment_priority => ln_prnt_pay_priority,
795 p_parent_inv_exchange_rate => ln_prnt_exchange_rate
796 );
797
798 v_flag := 'Y';
799
800 end if; -- bug#3607133 if condition.
801
802
803 END IF; --1
804
805 END LOOP;
806
807 CLOSE tds_invoices;
808
809
810 lv_statement_no := '10';
811
812 /* start Commented for TDS clean up, obsoleted table ja_in_ap_inv_cancel_temp
813
814 DELETE FROM ja_in_ap_inv_cancel_temp
815 WHERE invoice_id = p_invoice_id; --cbabu 08/07/02 Bug#2448040
816
817 lv_statement_no := '10a';
818 COMMIT; --cbabu 08/07/02 Bug#2448040
819 end Commented for TDS clean up, obsoleted table ja_in_ap_inv_cancel_temp */
820
821
822
823 IF v_flag = 'Y' THEN --2
824
825 result := Fnd_Request.set_mode(TRUE);
826 lv_statement_no := '11';
827
828 lv_batch_name := jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id); --Ramananda for Bug#4584221
829
830 req_id := Fnd_Request.submit_request
831 (
832 'SQLAP',
833 'APXIIMPT',
834 'Localization Payables Open Interface Import',
835 '',
836 FALSE,
837 /* Bug 4774647. Added by Lakshmi Gopalsami
838 Passed operating unit also as this parameter has been
839 added by base .
840 */
841 '',
842 'INDIA TDS', /*--'TDS', --Ramanand for bug#4388958*/
843 to_char(p_invoice_id), /*Bug 4716884 bduvarag*/
844 --'TDS'||TO_CHAR(TRUNC(SYSDATE)),
845 --commented the above and commented the below by Ramananda for Bug#4584221
846 lv_batch_name,
847 '',
848 '',
849 '',
850 'Y',
851 'N',
852 'N',
853 'N',
854 1000,
855 cancelled_invoices_rec.created_by,
856 cancelled_invoices_rec.last_update_login
857 );
858
859 lv_statement_no := '12';
860 /*
861 OPEN Fetch_App_Inv_Flag_Cur(for_std_invoice_rec.vendor_id,for_std_invoice_rec.vendor_site_id);
862 FETCH Fetch_App_Inv_Flag_Cur INTO v_approve_flag;
863 CLOSE Fetch_App_Inv_Flag_Cur;
864 IF NVL(v_approve_flag, 'N') = 'Y' THEN
865 result := Fnd_Request.set_mode(TRUE);
866 */
867
868 -- above block commented by Aparajita on 29/07/2002 for bug # 2475416, and the block below added.
869
870 v_Approved_Invoice_Flag := 'N';
871
872 BEGIN
873
874 SELECT NVL( Approved_Invoice_Flag, 'N' )
875 INTO v_Approved_Invoice_Flag
876 FROM JAI_CMN_VENDOR_SITES
877 WHERE Vendor_Id = for_std_invoice_rec.vendor_id
878 AND Vendor_Site_Id = for_std_invoice_rec.vendor_site_id;
879
880 EXCEPTION
881
882 WHEN NO_DATA_FOUND THEN
883
884 BEGIN
885
886 SELECT NVL( Approved_Invoice_Flag, 'N' )
887 INTO v_Approved_Invoice_Flag
888 FROM JAI_CMN_VENDOR_SITES
889 WHERE Vendor_Id = for_std_invoice_rec.vendor_id
890 AND Vendor_Site_Id = 0;
891
892 EXCEPTION
893 WHEN NO_DATA_FOUND THEN
894 NULL;
895 END;
896
897 END;
898
899
900 IF v_Approved_Invoice_Flag = 'Y' THEN
901
902 lv_statement_no := '13';
903
904 result := Fnd_Request.set_mode(TRUE);
905 req1_id := Fnd_Request.submit_request
906 (
907 'JA', 'JAINAPIN', 'Approval of TDS and CM',
908 SYSDATE, FALSE, req_id, for_std_invoice_rec.vendor_id,
909 for_std_invoice_rec.vendor_site_id, cancelled_invoices_rec.invoice_id,
910 NULL, 'CAN'
911 );
912 END IF;
913
914 -- end addition by Aparajita on 29/07/2002 for bug # 2475416
915
916 END IF; --2
917
918 --cbabu 08/07/02 Bug#2448040
919
920 EXCEPTION
921
922 WHEN OTHERS THEN
923
924 ROLLBACK;
925 lv_error_mesg := SQLERRM;
926
927 INSERT INTO JAI_CMN_ERRORS_T
928 (
929 APPLICATION_SOURCE, error_message, additional_error_mesg, creation_date, created_by,
930 last_updated_by, last_update_date -- added, Harshita for Bug 4866533
931 )
932 VALUES
933 (
934 lv_procedure_name, lv_error_mesg,
935 'EXCEPTION captured BY WHEN OTHERS IN the PROCEDURE. STATEMENT No:' || lv_statement_no,
936 SYSDATE, Fnd_Global.user_id,
937 fnd_global.user_id, sysdate -- added, Harshita for Bug 4866533
938 );
939
940 COMMIT; --commit for the above insert statement
941 --cbabu 08/07/02 Bug#2448040
942
943 END cancel_invoice;
944
945 /*
946 || Changed the procedure name from ja_in_ap_prepay_unapply_p to process_prepayment_unapply
947 */
948 PROCEDURE process_prepayment_unapply
949 (
950 errbuf OUT NOCOPY VARCHAR2,
951 retcode OUT NOCOPY VARCHAR2,
952 p_invoice_id IN NUMBER,
953 p_last_updated_by IN NUMBER,
954 p_last_update_date IN DATE,
955 p_created_by IN NUMBER,
956 p_creation_date IN DATE,
957 p_org_id IN NUMBER,
958 p_prepay_dist_id IN NUMBER,
959 p_inv_dist_id IN NUMBER,
960 p_attribute IN VARCHAR2
961 )
962 IS
963
964 /* CURSOR check_prep_amt_app(p_id NUMBER,inv_id NUMBER) IS
965 SELECT amount amt_app
966 FROM ap_invoice_distributions_all
967 WHERE prepay_distribution_id = p_id
968 AND invoice_id = inv_id
969 AND amount < 0 ;
970 */
971
972 -- above definition comented by Aparajita Reopen issue - 1 for bug #2461706 and replaced by code below
973
974 CURSOR check_prep_amt_app(p_id NUMBER,inv_id NUMBER) IS
975 SELECT -1 * amount amt_app
976 FROM ap_invoice_distributions_all
977 WHERE invoice_id = inv_id
978 AND invoice_distribution_id = p_inv_dist_id
979 AND prepay_distribution_id = p_id
980 -- AND amount < 0 ;
981 ;
982
983 CURSOR check_prep_amt_unapp(p_id NUMBER,inv_id NUMBER) IS
984 SELECT amount amt_unapp
985 FROM ap_invoice_distributions_all
986 WHERE prepay_distribution_id = p_id
987 AND invoice_id = inv_id
988 AND amount >0 ;
989
990 CURSOR check_for_tds_invoice_o(inv_id NUMBER, p_att VARCHAR2) IS
991 SELECT tds_invoice_num,invoice_id,invoice_amount,amt_reversed,
992 amt_applied,tds_tax_id,
993 tds_amount,
994 tds_tax_rate,
995 organization_id
996 FROM JAI_AP_TDS_INVOICES
997 WHERE invoice_id =inv_id
998 AND source_attribute=p_att;
999
1000
1001 CURSOR check_for_tds_invoice(prepay_dist_id NUMBER, p_att VARCHAR2) IS
1002 SELECT tds_invoice_num,invoice_id,invoice_amount,amt_reversed,
1003 amt_applied,tds_tax_id,
1004 tds_amount,
1005 tds_tax_rate,
1006 organization_id
1007 FROM JAI_AP_TDS_INVOICES
1008 WHERE source_attribute=p_att
1009 AND invoice_id = (SELECT invoice_id
1010 FROM ap_invoice_distributions_all
1011 WHERE invoice_distribution_id=prepay_dist_id);
1012
1013 /*CURSOR check_for_tds_invoice(inv_id NUMBER) IS
1014 SELECT tds_invoice_num,invoice_id,invoice_amount,amt_reversed,amt_applied,tds_tax_id,organization_id
1015 FROM JAI_AP_TDS_INVOICES
1016 WHERE invoice_id = inv_id
1017 AND source_attribute = p_attribute;*/
1018
1019 CURSOR for_vendor_id(t_id NUMBER) IS
1020 SELECT vendor_id
1021 FROM JAI_CMN_TAXES_ALL
1022 WHERE tax_id = t_id;
1023
1024 CURSOR for_payment_status(inv_num VARCHAR2,vend_id NUMBER,organization NUMBER) IS
1025 ----for information about tds invoice
1026 SELECT payment_status_flag,invoice_amount,invoice_id
1027 FROM ap_invoices_all
1028 WHERE invoice_num = inv_num
1029 AND vendor_id = vend_id
1030 AND NVL(org_id, 0) = NVL(organization, 0);
1031
1032 CURSOR for_distribution_insertion(inv_id NUMBER) IS
1033 SELECT distribution_line_number,accounting_date,accrual_posted_flag,reversal_flag,
1034 assets_addition_flag,assets_tracking_flag,cash_posted_flag,dist_code_combination_id,
1035 accts_pay_code_combination_id,
1036 period_name,set_of_books_id,
1037 amount,match_status_flag,base_amount_to_post,prepay_amount_remaining,
1038 parent_invoice_id,org_id,description
1039 FROM ap_invoice_distributions_all
1040 WHERE invoice_id = inv_id
1041 AND distribution_line_number = (SELECT MAX(distribution_line_number)
1042 FROM ap_invoice_distributions_all
1043 WHERE invoice_id = inv_id);
1044
1045 CURSOR for_std_invoice(inv_id NUMBER) IS
1046 SELECT invoice_type_lookup_code,vendor_id,vendor_site_id,invoice_currency_code,
1047 exchange_rate,exchange_rate_type,exchange_date,terms_id,payment_method_lookup_code,
1048 pay_group_lookup_code,invoice_num,
1049 invoice_received_date, -- added by Aparajita on 10/07/2002 for bug # 2439034
1050 goods_received_date -- added by Aparajita on 10/07/2002 for bug # 2439034
1051 FROM ap_invoices_all
1052 WHERE invoice_id = inv_id;
1053
1054 CURSOR for_payment_amount(inv_id NUMBER) IS
1055 SELECT amount
1056 FROM ap_invoice_distributions_all
1057 WHERE invoice_id = inv_id
1058 AND distribution_line_number = 1;
1059
1060 CURSOR for_approved_amount(inv_id NUMBER) IS
1061 SELECT approved_amount,amount_applicable_to_discount
1062 FROM ap_invoices_all
1063 WHERE invoice_id = inv_id;
1064
1065 CURSOR Fetch_Invoice_Num_Cur IS
1066 SELECT Invoice_Num
1067 FROM Ap_Invoices_All
1068 WHERE Invoice_Id = p_invoice_id;
1069
1070 /* CURSOR Fetch_App_Inv_Flag_Cur( p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER ) IS
1071 SELECT NVL( Approved_Invoice_Flag, 'N' ) Approved_Invoice_Flag
1072 FROM JAI_CMN_VENDOR_SITES
1073 WHERE Vendor_Id = p_vendor_id
1074 AND Vendor_Site_Id = p_vendor_site_id;
1075 */
1076 CURSOR Fetch_App_Inv_Flag_Cur( p_inv_id IN NUMBER) IS
1077 SELECT NVL( Approved_Invoice_Flag, 'N' ) Approved_Invoice_Flag
1078 FROM JAI_CMN_VENDOR_SITES
1079 WHERE (Vendor_Id, vendor_site_id) =
1080 (SELECT vendor_id, vendor_site_id FROM ap_invoices_all WHERE invoice_id = p_inv_id);
1081
1082
1083
1084 --Added by Abhay on 20-JAN-2000 to tackle unapplication of prepayment {
1085
1086 CURSOR fetch_amount(inv_id NUMBER) IS
1087 SELECT amount
1088 FROM ap_invoice_distributions_all
1089 WHERE invoice_id = inv_id
1090 AND distribution_line_number = 1;
1091
1092 --Added by Abhay on 20-JAN-2000 to tackle unapplication of prepayment }
1093
1094 /* Commented by Aparajita on 07/07/2002 for bug # 2439034 as it was not being used.
1095 CURSOR for_invoice_num(invoice_num VARCHAR2) IS
1096 SELECT 'RTN/'|| invoice_num || '/' ||TO_CHAR(JAI_AP_TDS_INVOICE_NUM_S.CURRVAL)
1097 FROM DUAL;
1098 */
1099
1100 Cursor get_prepay_dist_date is -- bug 3112711 kpvs
1101 select accounting_date
1102 from ap_invoice_distributions_all
1103 where invoice_distribution_id = p_inv_dist_id;
1104
1105 CURSOR cur_prnt_pay_priority -- Ravi
1106 IS
1107 SELECT payment_priority
1108 FROM ap_payment_schedules_all
1109 WHERE invoice_id = p_invoice_id;
1110
1111 CURSOR cur_prnt_exchange_rate -- Ravi
1112 IS
1113 SELECT exchange_rate
1114 FROM ap_invoices_all
1115 WHERE invoice_id = p_invoice_id;
1116
1117 lv_object_name VARCHAR2(61) ; --:= '<Package_name>.<procedure_name>'; /* Added by Ramananda for bug#4407165 */
1118
1119 check_tds_prepayment_rec check_for_tds_invoice%ROWTYPE;
1120 check_tds_original_rec check_for_tds_invoice_o%ROWTYPE;
1121 for_payment_status_prepay_rec for_payment_status%ROWTYPE;
1122 for_pay_status_original_rec for_payment_status%ROWTYPE;
1123 for_distribution_insertion_rec for_distribution_insertion%ROWTYPE;
1124 for_vendor_id_prepay_rec for_vendor_id%ROWTYPE;
1125 for_vendor_id_original_rec for_vendor_id%ROWTYPE;
1126 for_std_invoice_rec for_std_invoice%ROWTYPE;
1127 for_prepay_payment_amount_rec for_payment_amount%ROWTYPE;
1128 for_approved_amount_rec for_approved_amount%ROWTYPE;
1129 for_insertion_invoice_id NUMBER;
1130 insertion_amount NUMBER := 0;
1131 reversal_amount NUMBER := 0;
1132 updation_amount NUMBER := 0;
1133 updation_prepay_amount NUMBER := 0;
1134 reversal_prepay_amount NUMBER := 0;
1135 new_std_invoice_amount NUMBER := 0;
1136 if_part NUMBER := 0;
1137 else_part NUMBER := 0;
1138 req_id NUMBER;
1139 req1_id NUMBER;
1140 result BOOLEAN;
1141
1142 v_invoice_num VARCHAR2(100);
1143 fetch_amount_rec fetch_amount%ROWTYPE;
1144 v_unapply NUMBER := 0;
1145 v_rtn_amt NUMBER := 0;
1146 check_prep_amt_app_rec check_prep_amt_app%ROWTYPE;
1147 check_prep_amt_unapp_rec check_prep_amt_unapp%ROWTYPE;
1148 for_std_inv_tds_rec for_std_invoice%ROWTYPE;
1149 for_dist_tds_rec for_distribution_insertion%ROWTYPE;
1150 for_std_inv_rec for_std_invoice%ROWTYPE;
1151 for_dist_rec for_distribution_insertion%ROWTYPE;
1152 insert_amt_ap NUMBER;
1153 insert_inv_id NUMBER;
1154
1155 -- Start : following variables added by Aparajita on 07/07/2002 for bug # 2439034
1156 v_tds_inv_num ap_invoices_all.invoice_num%TYPE;
1157 v_sup_cm_num ap_invoices_all.invoice_num%TYPE;
1158
1159 v_tds_inv_run_num NUMBER;
1160 v_sup_cm_run_num NUMBER;
1161
1162 v_Approved_Invoice_Flag CHAR(1); -- := 'N'; -- added by Aparajita for bug # 2441683 on 23/07/2002 --Ramananda for File.Sql.35
1163 -- End : following variables added by Aparajita on 07/07/2002 for bug # 2439034
1164
1165 -- variables added by Aparajita on 08-jul-2002 for bug # 2461706
1166 v_tds_invoice_num ap_invoices_all.invoice_num%TYPE;
1167 v_invoice_id NUMBER;
1168 v_invoice_amount NUMBER;
1169 v_amt_reversed NUMBER;
1170 v_amt_applied NUMBER;
1171 v_tds_tax_id NUMBER;
1172 v_organization_id NUMBER;
1173 -- variables added by Aparajita on 08-jul-2002 for bug # 2461706
1174
1175 v_prepay_dist_date ap_invoice_distributions_all.accounting_date%TYPE; -- bug 3112711 kpvs
1176
1177 v_insertion_amount_tds_si NUMBER := 0; -- bug#3469847
1178 ln_prnt_pay_priority ap_payment_schedules_all.payment_priority%TYPE;
1179 ln_prnt_exchange_rate ap_invoices_all.exchange_rate%TYPE;
1180 ln_tax_rate JAI_AP_TDS_INVOICES.tds_tax_rate%type;
1181 ln_tax_amount JAI_AP_TDS_INVOICES.tds_amount%type;
1182 ln_taxable_amount JAI_AP_TDS_INVOICES.invoice_amount%type;
1183 lv_invoice_num ap_invoices_interface.invoice_num%type; --rchandan for bug#4428980
1184 lv_description ap_invoices_interface.description%type; --rchandan for bug#4428980
1185 lv_lookup_type_code ap_invoices_interface.invoice_type_lookup_code%TYPE; --rchandan for bug#4428980
1186 lv_source ap_invoices_interface.source%type; --rchandan for bug#4428980
1187 lv_voucher_num ap_invoices_interface.voucher_num%type;--rchandan for bug#4428980
1188 /* ramanand for SQL BIND Compliance */
1189 lv_rtn_tds_inv VARCHAR2(100);
1190 lv_rtn_tds_auth VARCHAR2(100);
1191
1192 /* start additions by ssumaith - bug# 4448789 */
1193 ln_legal_entity_id NUMBER;
1194 lv_legal_entity_name VARCHAR2(240);
1195 lv_return_status VARCHAR2(100);
1196 ln_msg_count NUMBER;
1197 ln_msg_data VARCHAR2(1000);
1198 /* ends additions by ssumaith - bug# 4448789*/
1199
1200 lv_batch_name ap_batches_all.batch_name%TYPE; --added by Ramananda for Bug#4584221
1201
1202 /* PrePayment to Standard */
1203
1204 /*---------------------------------------------------------------------------------------------------------------------
1205 FILENAME: jai_ap_tds_old_pkg.process_prepayment_unapply.sql
1206
1207 CHANGE HISTORY:
1208 Sl. No Date Author and Details
1209 1. 10/07/2002 Aparajita Das for bug # 2439034. RCS version 615.1
1210 Called the concurrent for TDS and CM approval with changed parameters.
1211 - hardcoded UNAPPLY to pass to approval program
1212 - the invoice numbers of the return TDS inv and supplier CM concatenated .
1213 - added code to populate the invoice received date and goods recd date to the interface table
1214 as otheriwise when terms is set to any of them, it was getting into error.
1215
1216 2. 29/07/2002 Aparajita for bug # 2475416. RCS version 615.2
1217 Approved TDS and CM setup was allowed at null site, so changed the code to look into null site
1218 setup if the site setup does not exist for the vendor for pre approved TDS and CM. This was done
1219 to find out if the approval of TDS an CM concurrent needs to be submitted.
1220
1221 3. 08/08/2002 Aparajita for bug # 2461706. RCS version 615.3
1222 When a prepayment is applied to an unapproved standard invoice, tds related invoices get
1223 generated. But when the prepayment is unapplied, tds invoices were not getting generated. Modified
1224 the code to generate the tds related invoices at unapplying.
1225
1226 4. 24/09/2002 Aparajita Reopen issue - 1 for bug #2461706. RCS version 615.4
1227 When a prepayment is applied, unapplied, applied, unapplied on the same invoice and the application
1228 amount is different between the first and the second time, the tds related invoices that were
1229 getting generated at the second time used to be of the value corresponding to the first time.
1230
1231 For this, the cursor definition check_prep_amt_app was changed to consider the application amount
1232 corresponding to the iteration, earlier it was always considering the first applied amount.
1233
1234 5. 23/09/2002 Aparajita for bug # 2503751.RCS version 615.5
1235 Populate the invoice id of the original invoice in attribute1 of the tds related invoice
1236 for context value 'India Original Invoice for TDS'.
1237
1238 6 15/01/2003 Aparajita for Bug # 2738340. RCS version 615.6
1239 Added the commit interval parameter in the request for APXIIMPT. This was missing.
1240 7 15/10/2003 kpvs for bug # 3112711, version 616.1
1241 Cursor get_prepay_dist_date incorporated to get the GL date of the distribution line
1242 created in ap_invoice_distributions_all .
1243
1244 This date is inserted as invoice_date into ap_invoices_interface
1245 and as accounting_date into ap_invoice_lines_interface.
1246 This would ensure that the invoice date and GL date of the TDS invoices
1247 are in sync with the date entered on Apply/Unapply prepayment screen if the
1248 user changes this date.
1249
1250 8. 31/10/2003 Aparajita for bug#3205948. Version#616.2
1251
1252 Applied round function to the amount of the invoices that is being generated to be in sync
1253 with apply.
1254
1255 9. 08/03/2004 Aparajita for bug#3469847. Version#619.1
1256 For Prepeyment having forex, the amount for return standard invoice for TDS authority
1257 was not getting converted though the inv is always generated in INR.
1258
1259 10. 29/04/2004 Aparajita for bug#3583708. Version#619.2
1260 The Credit memo that is generated was not picking up the right dist_code_combination_id. It has to be the same as used in case of the
1261 stadard invoice that is being generated for the TDS authority.
1262
1263 11. 02/05/2005 rchandan for bug#4333449. Version 116.1
1264 India Original Invoice for TDS DFF is eliminated. So attribute1 of ap_invoices_al
1265 is not populated whenevr an invoice is generated. Instead the Invoice details are
1266 populated into jai_ap_tds_thhold_trxs. So whenever data is inserted into interface
1267 tables the jai_ap_tds_thhold_trxs table is also populated.
1268
1269
1270 ----------------------------------------------------------------------------------------------------*/
1271 BEGIN
1272
1273 v_Approved_Invoice_Flag := 'N'; -- added by Aparajita for bug # 2441683 on 23/07/2002 --Ramananda for File.Sql.35
1274 lv_object_name := 'jai_ap_tds_old_pkg.process_prepayment_unapply'; /* Added by Ramananda for bug#4407165 */
1275
1276 OPEN check_prep_amt_app(p_prepay_dist_id,p_invoice_id);
1277 FETCH check_prep_amt_app INTO check_prep_amt_app_rec;
1278 CLOSE check_prep_amt_app;
1279
1280 OPEN check_prep_amt_unapp(p_prepay_dist_id,p_invoice_id);
1281 FETCH check_prep_amt_unapp INTO check_prep_amt_unapp_rec;
1282 CLOSE check_prep_amt_unapp;
1283
1284 OPEN cur_prnt_pay_priority; -- 4333449
1285 FETCH cur_prnt_pay_priority INTO ln_prnt_pay_priority;
1286 CLOSE cur_prnt_pay_priority;
1287
1288 OPEN cur_prnt_exchange_rate; -- 4333449
1289 FETCH cur_prnt_exchange_rate INTO ln_prnt_exchange_rate;
1290 CLOSE cur_prnt_exchange_rate;
1291
1292
1293
1294 IF ABS(check_prep_amt_app_rec.amt_app) < 0 THEN
1295 v_unapply := 1;
1296 END IF;
1297
1298 --CHECK IF TDS INVOICE FOR PREPAYMENT INVOICE EXISTS.
1299 OPEN check_for_tds_invoice(p_prepay_dist_id,p_attribute);
1300 FETCH check_for_tds_invoice INTO check_tds_prepayment_rec;
1301 CLOSE check_for_tds_invoice;
1302
1303
1304 IF check_tds_prepayment_rec.invoice_id IS NOT NULL THEN --1
1305
1306 --GET VENDOR ID BASED ON THE TAX-ID FOR THE PREPAYMENT INVOICE.
1307 OPEN for_vendor_id(check_tds_prepayment_rec.tds_tax_id);
1308 FETCH for_vendor_id INTO for_vendor_id_prepay_rec;
1309 CLOSE for_vendor_id;
1310
1311 --GET PAYMENT_STATUS, INVOICE_AMOUNT AND INVOICE_ID OF TDS INVOICE FOR PREPAYMENT INVOICE.
1312 OPEN for_payment_status(check_tds_prepayment_rec.tds_invoice_num,
1313 for_vendor_id_prepay_rec.vendor_id,NVL(check_tds_prepayment_rec.organization_id, 0));
1314 FETCH for_payment_status INTO for_payment_status_prepay_rec;
1315 CLOSE for_payment_status;
1316
1317 OPEN for_std_invoice(p_invoice_id); --added on 05-Feb-2002
1318 FETCH for_std_invoice INTO for_std_invoice_rec;
1319 CLOSE for_std_invoice;
1320
1321 --CHECK IF TDS INVOICE FOR ORIGINAL INVOICE EXISTS.
1322 OPEN check_for_tds_invoice_o(p_invoice_id,p_attribute);
1323 FETCH check_for_tds_invoice_o INTO check_tds_original_rec;
1324
1325
1326
1327 IF check_for_tds_invoice_o%FOUND THEN --2
1328 Fnd_File.put_line(Fnd_File.LOG,'Inside IF check_for_tds_invoice_o%FOUND');
1329 v_tds_invoice_num := check_tds_original_rec.tds_invoice_num;
1330 v_invoice_id := check_tds_original_rec.invoice_ID;
1331 v_invoice_amount := check_tds_original_rec.invoice_amount;
1332 v_amt_reversed := check_tds_original_rec.amt_reversed;
1333 v_amt_applied := check_tds_original_rec.amt_applied;
1334 v_tds_tax_id := check_tds_original_rec.tds_tax_id;
1335 v_organization_id := check_tds_original_rec.organization_id;
1336 ln_tax_rate := check_tds_original_rec.tds_tax_rate;
1337 ln_tax_amount := check_tds_original_rec.tds_amount;
1338 ln_taxable_amount := check_tds_original_rec.invoice_amount;
1339
1340 -- line below added by Aparajita for bug 2338345.
1341 v_tds_inv_num := check_tds_original_rec.tds_invoice_num;
1342
1343
1344 -- start the following else part is added on 08/08/2002 by Aparajita for bug # 2461706
1345 ELSIF check_for_tds_invoice_o%NOTFOUND THEN
1346
1347 Fnd_File.put_line(Fnd_File.LOG,'Inside ELSIF check_for_tds_invoice_o%NOTFOUND');
1348
1349 v_tds_invoice_num := check_tds_prepayment_rec.tds_invoice_num;
1350 v_invoice_id := check_tds_prepayment_rec.invoice_ID;
1351 v_invoice_amount := check_tds_prepayment_rec.invoice_amount;
1352 v_amt_reversed := check_tds_prepayment_rec.amt_reversed;
1353 v_amt_applied := check_tds_prepayment_rec.amt_applied;
1354 v_tds_tax_id := check_tds_prepayment_rec.tds_tax_id;
1355 v_organization_id := check_tds_prepayment_rec.organization_id;
1356 ln_tax_rate := check_tds_prepayment_rec.tds_tax_rate;
1357 ln_tax_amount := check_tds_prepayment_rec.tds_amount;
1358 ln_taxable_amount := check_tds_prepayment_rec.invoice_amount;
1359
1360 END IF; -- check_for_tds_invoice_o%FOUND THEN --2
1361
1362 -- following If added by Aparajita on 29th April for bug # 2338345
1363 IF v_tds_inv_num IS NULL THEN
1364 -- the TDS invoice corrosponding to the standard invoice is not found, this would be when
1365 -- the standard invoice is not approved yet.
1366 -- the CM for TDS authortiy should then should have the standard invoice number suffixed by TDS
1367 v_tds_inv_num := for_std_invoice_rec.invoice_num || 'TDS';
1368 END IF;
1369
1370 -- end the following else part is added on 08/08/2002 by Aparajita for bug # 2461706
1371
1372 --GET VENDOR ID BASED ON THE TAX-ID FOR THE ORIGINAL INVOICE.
1373 OPEN for_vendor_id(v_tds_tax_id);
1374 FETCH for_vendor_id INTO for_vendor_id_original_rec;
1375 CLOSE for_vendor_id;
1376
1377 --GET PAYMENT_STATUS, INVOICE_AMOUNT AND INVOICE_ID OF TDS INVOICE FOR ORIGINAL INVOICE.
1378 OPEN for_payment_status(v_tds_invoice_num,
1379 for_vendor_id_original_rec.vendor_id,
1380 NVL(v_organization_id, 0)
1381 );
1382
1383 FETCH for_payment_status INTO for_pay_status_original_rec;
1384 CLOSE for_payment_status;
1385
1386 --GET PREPAYMENT TDS INVOICE_AMOUNT, THIS IS THE AMOUNT OF INVOICE ORIGINALLY CREATED
1387 --AND NOT THE LATEST AMOUNT
1388 OPEN for_payment_amount(for_payment_status_prepay_rec.invoice_id);
1389 FETCH for_payment_amount INTO for_prepay_payment_amount_rec;
1390 CLOSE for_payment_amount;
1391
1392
1393 --CALCULATING THE PROPORTIONATE TAX TO BE APPLIED.
1394 IF (v_invoice_amount =
1395 v_amt_applied + ABS(check_prep_amt_app_rec.amt_app)) THEN --13
1396
1397 insertion_amount := (for_prepay_payment_amount_rec.amount - v_amt_reversed);
1398
1399 ELSE --13
1400
1401 insertion_amount :=FLOOR(ABS(check_prep_amt_app_rec.amt_app))
1402 * ( for_prepay_payment_amount_rec.amount / check_tds_prepayment_rec.invoice_amount);
1403
1404 END IF; --13
1405
1406 insertion_amount := round(insertion_amount); -- added by bug#3205948
1407
1408 -- Start added for bug#3469847
1409 if for_std_invoice_rec.invoice_currency_code <> 'INR' then
1410 v_insertion_amount_tds_si := insertion_amount * nvl(for_std_invoice_rec.exchange_rate, 1);
1411 else
1412 v_insertion_amount_tds_si := insertion_amount;
1413 end if;
1414 -- End added for bug#3469847
1415
1416 IF for_pay_status_original_rec.payment_status_flag = 'N'
1417 AND for_payment_status_prepay_rec.payment_status_flag = 'N'
1418 AND for_vendor_id_original_rec.vendor_id = for_vendor_id_prepay_rec.vendor_id THEN
1419
1420 -----------------------CASE 1
1421
1422 OPEN for_std_invoice(for_pay_status_original_rec.invoice_id);
1423 FETCH for_std_invoice INTO for_std_inv_tds_rec ;
1424 CLOSE for_std_invoice;
1425
1426 OPEN for_distribution_insertion(for_pay_status_original_rec.invoice_id);
1427 FETCH for_distribution_insertion INTO for_dist_tds_rec;
1428 CLOSE for_distribution_insertion;
1429
1430
1431 -- added by Aparajita to remove references to currval
1432 SELECT JAI_AP_TDS_THHOLD_TRXS_S1.nextval --JAI_AP_TDS_INVOICE_NUM_S.NEXTVAL changed by rchandan for bug#4487676
1433 INTO v_tds_inv_run_num
1434 FROM dual;
1435
1436 Open get_prepay_dist_date; -- bug 3112711 kpvs
1437 fetch get_prepay_dist_date into v_prepay_dist_date;
1438 close get_prepay_dist_date;
1439
1440
1441 /* Modified by Ramananda for bug# 4407184 , start */
1442
1443 lv_rtn_tds_inv := 'RTN/'|| v_tds_inv_num ||'/'||TO_CHAR(v_tds_inv_run_num);
1444 lv_rtn_tds_auth := 'RTN FOR TDS AUTHORITY FOR APPLIED AMOUNT OF TAX '||for_std_inv_tds_rec.invoice_num||' OR '||TO_CHAR(p_invoice_id) ;
1445
1446 /* start additions by ssumaith - bug# 4448789 */
1447 jai_cmn_utils_pkg.GET_LE_INFO(
1448 P_API_VERSION => NULL ,
1449 P_INIT_MSG_LIST => NULL ,
1450 P_COMMIT => NULL ,
1451 P_LEDGER_ID => NULL,
1452 P_BSV => NULL,
1453 P_ORG_ID => P_ORG_ID,
1454 X_RETURN_STATUS => lv_return_status ,
1455 X_MSG_COUNT => ln_msg_count,
1456 X_MSG_DATA => ln_msg_data,
1457 X_LEGAL_ENTITY_ID => ln_legal_entity_id ,
1458 X_LEGAL_ENTITY_NAME => lv_legal_entity_name
1459 );
1460 /* ends additions by ssumaith - bug# 4448789*/
1461
1462
1463 INSERT INTO ap_invoices_interface
1464 (
1465 invoice_id,
1466 invoice_num,
1467 invoice_type_lookup_code,
1468 invoice_date,
1469 vendor_id,
1470 vendor_site_id,
1471 invoice_amount,
1472 invoice_currency_code,
1473 exchange_rate,
1474 exchange_rate_type,
1475 exchange_date,
1476 terms_id,
1477 description,
1478 source,
1479 -- voucher_num, Harshita for Bug 4870243
1480 payment_method_lookup_code,
1481 pay_group_lookup_code,
1482 org_id,
1483 Legal_entity_id ,
1484 created_by,
1485 creation_date,
1486 last_updated_by,
1487 last_update_date,
1488 last_update_login,
1489 invoice_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
1490 goods_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
1491 group_id /*Bug 4716884*/
1492 )
1493 VALUES
1494 (
1495 ap_invoices_interface_s.NEXTVAL,
1496 lv_rtn_tds_inv, --'RTN/'|| v_tds_inv_num ||'/'||TO_CHAR(v_tds_inv_run_num),
1497 'STANDARD',
1498 v_prepay_dist_date, -- bug 3112711 kpvs TRUNC( SYSDATE ), --for_std_inv_tds_rec.invoice_date,
1499 for_std_inv_tds_rec.vendor_id,
1500 for_std_inv_tds_rec.vendor_site_id,
1501 v_insertion_amount_tds_si , -- insertion_amount, bug#3469847
1502 for_std_inv_tds_rec.invoice_currency_code,
1503 for_std_inv_tds_rec.exchange_rate,
1504 for_std_inv_tds_rec.exchange_rate_type,
1505 for_std_inv_tds_rec.exchange_date,
1506 for_std_inv_tds_rec.terms_id,
1507 lv_rtn_tds_auth, --'RTN FOR TDS AUTHORITY FOR APPLIED AMOUNT OF TAX '||for_std_inv_tds_rec.invoice_num||' OR '||TO_CHAR(p_invoice_id) ,
1508 'INDIA TDS', /*--'TDS', ----Ramanand for bug#4388958*/
1509 -- lv_rtn_tds_inv, --'RTN/'|| v_tds_inv_num ||'/'||TO_CHAR(v_tds_inv_run_num), Harshita for Bug 4870243
1510 for_std_inv_tds_rec.payment_method_lookup_code,
1511 for_std_inv_tds_rec.pay_group_lookup_code,
1512 p_org_id,
1513 ln_legal_entity_id ,
1514 p_created_by,
1515 p_creation_date,
1516 p_last_updated_by,
1517 p_last_update_date,
1518 p_last_updated_by,
1519 for_std_inv_tds_rec.invoice_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
1520 for_std_inv_tds_rec.goods_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
1521 to_char(p_invoice_id) /*Bug 4716884*/
1522 );
1523 /* Modified by Ramananda for bug# 4407184 , end */
1524
1525 -- following line added by Aparajita on 07/07/2002 for bug # 2439034
1526 v_tds_inv_num := 'RTN/'||v_tds_inv_num||'/'||TO_CHAR(v_tds_inv_run_num);
1527 lv_description := 'RTN FOR TDS TAX AUTHORITY FOR TAX APPLIED BY ';
1528 lv_lookup_type_code := 'ITEM';
1529 INSERT INTO ap_invoice_lines_interface
1530 (
1531 invoice_id,
1532 invoice_line_id,
1533 line_number,
1534 line_type_lookup_code,
1535 amount,
1536 accounting_date,
1537 description,
1538 dist_code_combination_id,
1539 created_by,
1540 creation_date,
1541 last_updated_by,
1542 last_update_date,
1543 last_update_login
1544 )
1545 VALUES
1546 (
1547 ap_invoices_interface_s.CURRVAL,
1548 ap_invoice_lines_interface_s.NEXTVAL,
1549 1, --THERE WILL ALWAYS BE ONLY ONE LINE.
1550 lv_lookup_type_code,
1551 v_insertion_amount_tds_si , -- insertion_amount, bug#3469847
1552 v_prepay_dist_date, -- bug 3112711 kpvs TRUNC( SYSDATE ),
1553 lv_description,
1554 for_dist_tds_rec.dist_code_combination_id,
1555 p_created_by,
1556 p_creation_date,
1557 p_last_updated_by,
1558 p_last_update_date,
1559 p_last_updated_by
1560 );
1561
1562
1563 OPEN for_std_invoice(p_invoice_id);--check_tds_original_rec.invoice_id);
1564 FETCH for_std_invoice INTO for_std_inv_rec ;
1565 CLOSE for_std_invoice;
1566
1567
1568 OPEN for_distribution_insertion(p_invoice_id); --check_tds_original_rec.invoice_id);
1569 FETCH for_distribution_insertion INTO for_dist_rec;
1570 CLOSE for_distribution_insertion;
1571
1572
1573 SELECT JAI_AP_TDS_THHOLD_TRXS_S1.nextval --JAI_AP_TDS_INVOICE_NUM_S.NEXTVAL changed by rchandan for bug#4487676
1574 INTO v_sup_cm_run_num
1575 FROM dual;
1576 lv_lookup_type_code:= 'CREDIT';
1577 lv_invoice_num := for_std_inv_rec.invoice_num||'CM/'||TO_CHAR(v_sup_cm_run_num); --rchandan for bug#4428980
1578 lv_description := 'CM FOR SUPPLIER FOR TDS DEDUCTED AFTER UNAPPLY '; --rchandan for bug#4428980
1579 lv_source := 'INDIA TDS'; --rchandan for bug#4428980
1580 lv_voucher_num := for_std_inv_rec.invoice_num || 'CM/' ||TO_CHAR(v_sup_cm_run_num); --rchandan for bug#4428980
1581
1582 /* start additions by ssumaith - bug# 4448789 */
1583 jai_cmn_utils_pkg.GET_LE_INFO(
1584 P_API_VERSION => NULL ,
1585 P_INIT_MSG_LIST => NULL ,
1586 P_COMMIT => NULL ,
1587 P_LEDGER_ID => NULL,
1588 P_BSV => NULL,
1589 P_ORG_ID => P_ORG_ID,
1590 X_RETURN_STATUS => lv_return_status ,
1591 X_MSG_COUNT => ln_msg_count,
1592 X_MSG_DATA => ln_msg_data,
1593 X_LEGAL_ENTITY_ID => ln_legal_entity_id ,
1594 X_LEGAL_ENTITY_NAME => lv_legal_entity_name
1595 );
1596 /* ends additions by ssumaith - bug# 4448789*/
1597
1598
1599
1600 INSERT INTO ap_invoices_interface
1601 (
1602 invoice_id,
1603 invoice_num,
1604 invoice_type_lookup_code,
1605 invoice_date,
1606 vendor_id,
1607 vendor_site_id,
1608 invoice_amount,
1609 invoice_currency_code,
1610 exchange_rate,
1611 exchange_rate_type,
1612 exchange_date,
1613 terms_id,
1614 description,
1615 source,
1616 -- voucher_num, Harshita for Bug 4870243
1617 payment_method_lookup_code,
1618 pay_group_lookup_code,
1619 org_id,
1620 legal_entity_id ,
1621 created_by,
1622 creation_date,
1623 last_updated_by,
1624 last_update_date,
1625 last_update_login,
1626 invoice_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
1627 goods_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
1628 group_id /*Bug 4716884*/
1629 )
1630 VALUES
1631 (
1632 ap_invoices_interface_s.NEXTVAL,
1633 lv_invoice_num, --rchandan for bug#4428980
1634 lv_lookup_type_code, --rchandan for bug#4428980
1635 v_prepay_dist_date, -- bug 3112711 kpvs TRUNC( SYSDATE ),
1636 for_std_inv_rec.vendor_id,
1637 for_std_inv_rec.vendor_site_id,
1638 (-1) * insertion_amount,
1639 for_std_inv_rec.invoice_currency_code,
1640 for_std_inv_rec.exchange_rate,
1641 for_std_inv_rec.exchange_rate_type,
1642 for_std_inv_rec.exchange_date,
1643 for_std_inv_rec.terms_id,
1644 lv_description, --rchandan for bug#4428980
1645 lv_source, /*--'TDS', --Ramanand for bug#4388958*/ --rchandan for bug#4428980
1646 -- lv_voucher_num, --rchandan for bug#4428980 Harshita for Bug 4870243
1647 for_std_inv_rec.payment_method_lookup_code,
1648 for_std_inv_rec.pay_group_lookup_code,
1649 p_org_id,
1650 ln_legal_entity_id,
1651 p_created_by,
1652 p_creation_date,
1653 p_last_updated_by,
1654 p_last_update_date,
1655 p_last_updated_by,
1656 for_std_inv_rec.invoice_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
1657 for_std_inv_rec.goods_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
1658 to_char(p_invoice_id) /*Bug 4716884*/
1659 );
1660
1661 -- following line added by Aparajita on 07/07/2002 for bug # 2439034
1662 v_sup_cm_num := for_std_inv_rec.invoice_num || 'CM/' ||TO_CHAR(v_sup_cm_run_num);
1663 lv_lookup_type_code := 'ITEM';
1664 lv_description := 'CM FOR SUPPLIER ON TDS AFTER UNAPPLY';
1665 INSERT INTO ap_invoice_lines_interface
1666 (
1667 invoice_id,
1668 invoice_line_id,
1669 line_number,
1670 line_type_lookup_code,
1671 amount,
1672 accounting_date,
1673 description,
1674 dist_code_combination_id,
1675 created_by,
1676 creation_date,
1677 last_updated_by,
1678 last_update_date,
1679 last_update_login
1680 )
1681 VALUES
1682 (
1683 ap_invoices_interface_s.CURRVAL,
1684 ap_invoice_lines_interface_s.NEXTVAL,
1685 1, --THERE WILL ALWAYS BE ONLY ONE LINE.
1686 lv_lookup_type_code, --rchandan for bug#4428980
1687 (-1) * insertion_amount,
1688 v_prepay_dist_date, -- bug 3112711 kpvs TRUNC( SYSDATE ),
1689 lv_description, --rchandan for bug#4428980
1690 for_dist_tds_rec.dist_code_combination_id,--bug#3583708 for_dist_rec.dist_code_combination_id,
1691 p_created_by,
1692 p_creation_date,
1693 p_last_updated_by,
1694 p_last_update_date,
1695 p_last_updated_by);
1696
1697
1698 jai_ap_tds_generation_pkg.insert_tds_thhold_trxs -------4333449
1699 (
1700 p_invoice_id => p_invoice_id,
1701 p_tds_event => 'OLD TDS PREPAY UNAPPLY',
1702 p_tax_id => v_tds_tax_id,
1703 p_tax_rate => ln_tax_rate,
1704 p_taxable_amount => ln_taxable_amount,
1705 p_tax_amount => ln_tax_amount,
1706 p_tds_authority_vendor_id => for_std_inv_tds_rec.vendor_id,
1707 p_tds_authority_vendor_site_id => for_std_inv_tds_rec.vendor_site_id,
1708 p_invoice_tds_authority_num => 'RTN/'|| v_tds_inv_num ||'/'||TO_CHAR(v_tds_inv_run_num),
1709 p_invoice_tds_authority_type => 'STANDARD',
1710 p_invoice_tds_authority_curr => for_std_inv_tds_rec.invoice_currency_code,
1711 p_invoice_tds_authority_amt => v_insertion_amount_tds_si,
1712 p_vendor_id => for_std_inv_rec.vendor_id,
1713 p_vendor_site_id => for_std_inv_rec.vendor_site_id,
1714 p_invoice_vendor_num => for_std_inv_rec.invoice_num||'CM/'||TO_CHAR(v_sup_cm_run_num),
1715 p_invoice_vendor_type => 'CREDIT',
1716 p_invoice_vendor_curr => for_std_inv_rec.invoice_currency_code,
1717 p_invoice_vendor_amt => (-1) * insertion_amount,
1718 p_parent_inv_payment_priority => ln_prnt_pay_priority,
1719 p_parent_inv_exchange_rate => ln_prnt_exchange_rate
1720 );
1721
1722 result := Fnd_Request.set_mode(TRUE);
1723
1724 lv_batch_name := jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id); --Ramananda for Bug#4584221
1725
1726 req_id := Fnd_Request.submit_request(
1727 'SQLAP',
1728 'APXIIMPT',
1729 'Localization Payables Open Interface Import',
1730 '',
1731 FALSE,
1732 /* Bug 4774647. Added by Lakshmi Gopalsami
1733 Passed operating unit also as this parameter has been
1734 added by base .
1735 */
1736 '',
1737 'INDIA TDS',/*--'TDS', --Ramanand for bug#4388958*/
1738 to_char(p_invoice_id), /*Bug 4716884*/
1739 --'TDS'||TO_CHAR(TRUNC(SYSDATE)),
1740 --commented the above and commented the below by Ramananda for Bug#4584221
1741 lv_batch_name,
1742 '',
1743 '',
1744 '',
1745 'Y',
1746 'N',
1747 'N',
1748 'N',
1749 1000, -- commit interval parameter added by Aparajita for bug # 2738340 on 15/01/2003
1750 p_created_by,
1751 p_last_updated_by);
1752
1753 insert_amt_ap := ABS(check_prep_amt_app_rec.amt_app);
1754 insert_inv_id := check_tds_prepayment_rec.invoice_id ;
1755
1756 UPDATE JAI_AP_TDS_INVOICES
1757 SET amt_reversed = NVL(amt_reversed,0) - insertion_amount,
1758 amt_applied = NVL(amt_applied,0) - insert_amt_ap
1759 WHERE invoice_id = insert_inv_id ;
1760
1761
1762 -- FOR App_Flag_Rec IN Fetch_App_Inv_Flag_Cur( for_std_invoice_rec.vendor_id,
1763 -- for_std_invoice_rec.vendor_site_id ) LOOP
1764 -- Above was commented by Aparajita on 07/07/2002 and replaced by the line below.
1765
1766 -- FOR App_Flag_Rec IN Fetch_App_Inv_Flag_Cur( p_invoice_id)
1767 -- LOOP
1768
1769 -- IF App_Flag_Rec.Approved_Invoice_Flag = 'Y' THEN
1770
1771 /* Commented by Aparajita for as this variables is not being used at all in the
1772 called unit
1773 OPEN for_invoice_num(for_std_invoice_rec.invoice_num);
1774 FETCH for_invoice_num INTO v_invoice_num;
1775 CLOSE for_invoice_num;
1776 */
1777 -- above block commented by Aparajita on 29/07/2002 for bug # 2475416, and the block below added.
1778 v_Approved_Invoice_Flag := 'N';
1779
1780 BEGIN
1781
1782 SELECT NVL( Approved_Invoice_Flag, 'N' )
1783 INTO v_Approved_Invoice_Flag
1784 FROM JAI_CMN_VENDOR_SITES
1785 WHERE Vendor_Id = for_std_inv_rec.vendor_id
1786 AND Vendor_Site_Id = for_std_inv_rec.vendor_site_id;
1787
1788 EXCEPTION
1789
1790 WHEN NO_DATA_FOUND THEN
1791
1792 BEGIN
1793
1794 SELECT NVL( Approved_Invoice_Flag, 'N' )
1795 INTO v_Approved_Invoice_Flag
1796 FROM JAI_CMN_VENDOR_SITES
1797 WHERE Vendor_Id = for_std_inv_rec.vendor_id
1798 AND Vendor_Site_Id = 0;
1799
1800 EXCEPTION
1801 WHEN NO_DATA_FOUND THEN
1802 NULL;
1803 END;
1804
1805 END;
1806
1807 -- end addition by Aparajita on 29/07/2002 for bug # 2475416
1808 IF v_Approved_Invoice_Flag = 'Y' THEN
1809
1810 result := Fnd_Request.set_mode(TRUE);
1811 req1_id := Fnd_Request.submit_request( 'JA', 'JAINAPIN', 'Approval OF TDS AND CM - Localization',
1812 SYSDATE, FALSE, req_id, for_std_invoice_rec.vendor_id,
1813 for_std_invoice_rec.vendor_site_id, p_invoice_Id ,
1814 v_tds_inv_num || ';' || v_sup_cm_num , 'UNAPPLY' );
1815
1816
1817
1818 END IF;
1819
1820 -- END LOOP;
1821
1822 END IF; -- IF for_pay_status_original_rec.payment_status_flag = 'N'
1823
1824 -- END IF ; -- IF check_for_tds_invoice_o%FOUND THEN --2
1825
1826 END IF; -- IF check_tds_prepayment_rec.invoice_id IS NOT NULL THEN --1
1827
1828 /* Added by Ramananda for bug#4407165 */
1829 EXCEPTION
1830 WHEN OTHERS THEN
1831 errbuf := null;
1832 retcode:= null;
1833 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1834 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1835 app_exception.raise_exception;
1836
1837 END process_prepayment_unapply;
1838
1839
1840 /*
1841 || Changed from ja_in_ap_prepay_invoice_p(11.5) to process_prepayment_apply(12.0)
1842 */
1843 PROCEDURE process_prepayment_apply(
1844 errbuf OUT NOCOPY VARCHAR2,
1845 retcode OUT NOCOPY VARCHAR2,
1846 p_invoice_id IN NUMBER,
1847 p_invoice_distribution_id IN NUMBER,
1848 p_amount IN NUMBER,
1849 p_last_updated_by IN NUMBER,
1850 p_last_update_date IN DATE,
1851 p_created_by IN NUMBER,
1852 p_creation_date IN DATE,
1853 p_org_id IN NUMBER,
1854 p_prepay_dist_id IN NUMBER,
1855 p_param IN VARCHAR2,
1856 p_attribute IN VARCHAR2
1857 )
1858 IS
1859
1860 CURSOR check_for_tds_invoice_o(inv_id NUMBER, p_att VARCHAR2) IS
1861 SELECT tds_invoice_num,invoice_id,
1862 invoice_amount,amt_reversed,
1863 amt_applied,
1864 tds_tax_id,
1865 tds_amount, -- 4333449
1866 tds_tax_rate, -- 4333449
1867 organization_id
1868 FROM JAI_AP_TDS_INVOICES
1869 WHERE invoice_id =inv_id
1870 AND source_attribute=p_att;
1871
1872 CURSOR check_for_tds_invoice(prepay_dist_id NUMBER, p_att VARCHAR2) IS
1873 SELECT tds_invoice_num,
1874 invoice_id,
1875 invoice_amount,
1876 amt_reversed,
1877 amt_applied,
1878 tds_tax_id,
1879 tds_amount, -- 4333449
1880 tds_tax_rate, -- 4333449
1881 organization_id
1882 FROM JAI_AP_TDS_INVOICES
1883 WHERE source_attribute=p_att
1884 AND invoice_id = (SELECT invoice_id
1885 FROM ap_invoice_distributions_all
1886 WHERE invoice_distribution_id=prepay_dist_id);
1887
1888 CURSOR for_vendor_id(t_id NUMBER) IS
1889 SELECT vendor_id
1890 FROM JAI_CMN_TAXES_ALL
1891 WHERE tax_id = t_id;
1892
1893 CURSOR for_payment_status(inv_num VARCHAR2,vend_id NUMBER,organization NUMBER) IS --for information about tds invoice
1894 SELECT payment_status_flag,
1895 invoice_amount,
1896 invoice_id
1897 FROM ap_invoices_all
1898 WHERE invoice_num = inv_num
1899 AND vendor_id = vend_id
1900 AND NVL(org_id, 0) = NVL(organization, 0);
1901
1902 CURSOR for_distribution_insertion(inv_id NUMBER) IS
1903 SELECT distribution_line_number,
1904 accounting_date,
1905 accrual_posted_flag,
1906 reversal_flag,
1907 assets_addition_flag,
1908 assets_tracking_flag,
1909 cash_posted_flag,
1910 dist_code_combination_id,
1911 period_name,
1912 set_of_books_id,
1913 accts_pay_code_combination_id,
1914 amount,
1915 match_status_flag,
1916 base_amount_to_post,
1917 prepay_amount_remaining,
1918 parent_invoice_id,
1919 org_id,
1920 description
1921 FROM ap_invoice_distributions_all
1922 WHERE invoice_id = inv_id
1923 AND distribution_line_number = (SELECT MAX(distribution_line_number)
1924 FROM ap_invoice_distributions_all
1925 WHERE invoice_id = inv_id);
1926
1927 CURSOR for_std_invoice(inv_id NUMBER) IS
1928 SELECT invoice_type_lookup_code,
1929 vendor_id,
1930 vendor_site_id,
1931 invoice_currency_code,
1932 exchange_rate,
1933 exchange_rate_type,
1934 exchange_date,
1935 terms_id,
1936 payment_method_lookup_code,
1937 pay_group_lookup_code,
1938 invoice_num,invoice_date,
1939 goods_received_date,
1940 invoice_received_date
1941 --Added the above line by pavan on 06-Jun-01 to populate the goods_received_date
1942 --column for the RTN invoice generated
1943 FROM ap_invoices_all
1944 WHERE invoice_id = inv_id;
1945
1946 CURSOR for_payment_amount(inv_id NUMBER) IS
1947 SELECT amount
1948 FROM ap_invoice_distributions_all
1949 WHERE invoice_id = inv_id
1950 AND distribution_line_number = 1;
1951
1952 CURSOR Fetch_Invoice_Num_Cur IS
1953 SELECT Invoice_Num
1954 FROM Ap_Invoices_All
1955 WHERE Invoice_Id = p_invoice_id;
1956
1957 CURSOR Fetch_App_Inv_Flag_Cur( p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER ) IS
1958 SELECT NVL( Approved_Invoice_Flag, 'N' ) Approved_Invoice_Flag
1959 FROM JAI_CMN_VENDOR_SITES
1960 WHERE Vendor_Id = p_vendor_id
1961 AND Vendor_Site_Id = p_vendor_site_id;
1962
1963 /* commented by Aparajita for bug 2364106 on 15-may-2002
1964 CURSOR for_invoice_num(invoice_num VARCHAR2) IS
1965 SELECT 'RTN/'|| invoice_num || '/' ||TO_CHAR(JAI_AP_TDS_INVOICE_NUM_S.CURRVAL)
1966 FROM DUAL;
1967 */
1968
1969
1970 CURSOR get_fun_det1(inv_id NUMBER) IS
1971 SELECT invoice_amount,
1972 payment_status_flag,
1973 invoice_type_lookup_code,
1974 org_id
1975 FROM ap_invoices_all
1976 WHERE invoice_id = inv_id;
1977
1978 for_dist_insertion_tds_rec for_distribution_insertion%ROWTYPE;
1979 check_tds_prepayment_rec check_for_tds_invoice%ROWTYPE;
1980 check_tds_original_rec check_for_tds_invoice_o%ROWTYPE;
1981 for_payment_status_prepay_rec for_payment_status%ROWTYPE;
1982 for_pay_status_original_rec for_payment_status%ROWTYPE;
1983 for_distribution_insertion_rec for_distribution_insertion%ROWTYPE;
1984 for_vendor_id_prepay_rec for_vendor_id%ROWTYPE;
1985 for_vendor_id_original_rec for_vendor_id%ROWTYPE;
1986 for_std_invoice_rec for_std_invoice%ROWTYPE;
1987 for_std_invoice_tds_rec for_std_invoice%ROWTYPE;
1988 for_prepay_payment_amount_rec for_payment_amount%ROWTYPE;
1989 for_insertion_invoice_id NUMBER;
1990 insertion_amount NUMBER := 0;
1991 reversal_amount NUMBER := 0;
1992 updation_amount NUMBER := 0;
1993 updation_prepay_amount NUMBER := 0;
1994 reversal_prepay_amount NUMBER := 0;
1995 new_std_invoice_amount NUMBER := 0;
1996 if_part NUMBER := 0;
1997 else_part NUMBER := 0;
1998 req_id NUMBER;
1999 req1_id NUMBER;
2000 result BOOLEAN;
2001 v_invoice_num VARCHAR2(100);
2002 lv_app_source VARCHAR2(100);--rchandan for bug#4428980
2003 lv_add_err_msg VARCHAR2(1000);--rchandan for bug#4428980
2004
2005 for_pre_invoice_tds_rec for_std_invoice%ROWTYPE;
2006 FOR_DIST_INSERTION_REC for_distribution_insertion %ROWTYPE ;
2007 for_dist_inst_rec for_distribution_insertion %ROWTYPE ;
2008 for_std_inv_rec_r for_std_invoice%ROWTYPE ;
2009 upd_amt_ap NUMBER;
2010 upd_inv_id NUMBER;
2011 get_fun_det_r1 get_fun_det1%ROWTYPE;
2012 X VARCHAR2(25);
2013 --03-Jan-2002
2014 -- added by Aparajita on 20 mar 2002
2015 debug_flag CHAR(1); -- := 'N' ; --Ramananda for File.Sql.35
2016 error_mesg VARCHAR2(255);
2017 -- end addition by Aparajita on 20 mar 2002
2018
2019 v_tds_cm_num VARCHAR2(60); -- Added by Aparajita for bug # 2338345 on 19th April'02
2020
2021 v_ja_ap_invoices_interface_no NUMBER; -- added by Aparajita on 14-may-2002 for bug # 2364106.
2022 v_ap_invoices_interface_no NUMBER; -- added by Aparajita on 14-may-2002 for bug # 2364106.
2023
2024 v_Approved_Invoice_Flag CHAR(1); -- := 'N';-- added by Aparajita on 29/07/2002 for bug # 2475416 --Ramananda for File.Sql.35
2025
2026
2027 CURSOR get_base_inv_id(v_inv_num VARCHAR2) IS
2028 SELECT invoice_id
2029 FROM JAI_AP_TDS_INVOICES
2030 WHERE tds_invoice_num = v_inv_num;
2031
2032 get_base_inv_id_rec get_base_inv_id%ROWTYPE;
2033
2034 CURSOR get_exchange_rate_base_inv(v_inv_id NUMBER) IS
2035 SELECT exchange_rate
2036 FROM ap_invoices_all
2037 WHERE invoice_id = v_inv_id;
2038
2039 Cursor get_prepay_dist_date is -- bug 3112711 kpvs
2040 select accounting_date
2041 from ap_invoice_distributions_all
2042 where invoice_distribution_id = p_invoice_distribution_id;
2043
2044 CURSOR cur_prnt_pay_priority -- 4333449
2045 IS
2046 SELECT payment_priority
2047 FROM ap_payment_schedules_all
2048 WHERE invoice_id = p_invoice_id;
2049
2050 CURSOR cur_prnt_exchange_rate -- 4333449
2051 IS
2052 SELECT exchange_rate
2053 FROM ap_invoices_all
2054 WHERE invoice_id = p_invoice_id;
2055
2056
2057 get_exchange_rate_base_inv_rec get_exchange_rate_base_inv%ROWTYPE;
2058
2059 --Below code added on 02-Feb-2002 for BUG#2205735
2060
2061 var_tds_invoice_num VARCHAR2(40);
2062 var_invoice_id NUMBER;
2063 var_tds_tax_id NUMBER;
2064 var_organization_id NUMBER;
2065 v_stage NUMBER(2):= 0; -- added by Aparajita on 23-may-2002 for tracking the stage in case of exception
2066
2067 ---end addition of 02-Feb-2002
2068 -- following added by Aparajita on 07/07/2002 for bug 2439034
2069 v_ap_tds_cm_num ap_invoices_all.invoice_num%TYPE;
2070 v_ap_sup_inv_num ap_invoices_all.invoice_num%TYPE;
2071 v_prepay_dist_date ap_invoice_distributions_all.accounting_date%TYPE; -- bug 3112711 kpvs
2072 ln_prnt_pay_priority ap_payment_schedules_all.payment_priority%TYPE; -- 4333449
2073 ln_prnt_exchange_rate ap_invoices_all.exchange_rate%TYPE; --4333449
2074 ln_tax_rate JAI_AP_TDS_INVOICES.tds_tax_rate%type; -- 4333449
2075 ln_tax_amount JAI_AP_TDS_INVOICES.tds_amount%type; -- 4333449
2076 ln_taxable_amount JAI_AP_TDS_INVOICES.invoice_amount%type; -- 4333449
2077
2078 /* Ramananda for BIND VARIABLES Compliance */
2079 lv_tds_cm_num VARCHAR2(100);
2080 lv_credit_note_tds VARCHAR2(100);
2081 lv_invoice_num_cm VARCHAR2(100);
2082 lv_rtn_invoice_num VARCHAR2(100);
2083 lv_standard_invoice_num VARCHAR2(100);
2084 lv_rtn_std_invoice_num VARCHAR2(100);
2085 lv_invoice_num VARCHAR2(100);
2086 lv_credit_tds_auth VARCHAR2(100);
2087 lv_standard_return_excess VARCHAR2(100);
2088
2089
2090 /* start additions by ssumaith - bug# 4448789 */
2091 ln_legal_entity_id NUMBER;
2092 lv_legal_entity_name VARCHAR2(240);
2093 lv_return_status VARCHAR2(100);
2094 ln_msg_count NUMBER;
2095 ln_msg_data VARCHAR2(1000);
2096 /* ends additions by ssumaith - bug# 4448789*/
2097
2098 lv_batch_name ap_batches_all.batch_name%TYPE; --added by Ramananda for Bug#4584221
2099
2100 -- start function added by bug#3218881
2101 function f_return_inv_amount
2102 (
2103 p_invoice_id in number,
2104 p_distribution_id in number,
2105 p_applied_prepay_amt in number,
2106 p_attribute in varchar2
2107 )
2108 return number
2109 is
2110
2111 type v_table_of_number is table of number(15) index by binary_integer;
2112 type v_table_of_number_decimal is table of number(15, 4) index by binary_integer;
2113
2114 v_distribution_amount_t v_table_of_number;
2115 v_prepaid_amount_t v_table_of_number;
2116 v_net_amount_t v_table_of_number;
2117 v_tax_rate_t v_table_of_number_decimal;
2118
2119 v_return_inv_amt number;
2120
2121 -- cursor to fetch the distribution lines having taxes which are not prepayment lines.
2122 -- reversed lines if any also should not be considered.
2123 Cursor c_inv_distribution_details(cp_line_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%type,
2124 cp_global_attribute_category ap_invoice_distributions_all.global_attribute_category%TYPE ) is--rchandan for bug#4428980
2125 select amount, prepay_distribution_id, global_attribute1, global_attribute2, global_attribute3 -- rchandan for bug#4333488
2126 from ap_invoice_distributions_all
2127 where invoice_id = p_invoice_id
2128 and nvl(reversal_flag, 'N') <> 'Y'
2129 and line_type_lookup_code <> cp_line_type_lookup_code--rchandan for bug#4428980
2130 and global_attribute_category = cp_global_attribute_category--rchandan for bug#4428980
2131 order by invoice_distribution_id;
2132
2133
2134 -- cursor to get the total of prepayment amoun having tds already applied
2135 Cursor c_inv_prepayments is
2136 select si.amount amount, pp.global_attribute1 global_attribute1, pp.global_attribute2 global_attribute2, pp.global_attribute3 global_attribute3 -- rchandan for bug#4333488
2137 from ap_invoice_distributions_all si ,
2138 ap_invoice_distributions_all pp
2139 where si.invoice_id= p_invoice_id
2140 and si.invoice_distribution_id <> p_distribution_id
2141 and si.prepay_distribution_id = pp.invoice_distribution_id;
2142
2143
2144 cursor c_get_tax_rate(p_tax_id number) is
2145 select tax_rate
2146 from JAI_CMN_TAXES_ALL
2147 where tax_id = p_tax_id;
2148
2149 v_tax_id JAI_CMN_TAXES_ALL.tax_id%type;
2150 v_tax_rate JAI_CMN_TAXES_ALL.tax_rate%type;
2151
2152 v_distribution_cnt number;
2153 v_loop_index number;
2154 v_prepayment_amount number;
2155 v_prepay_to_consider number;
2156 v_distribution_amt number;
2157
2158
2159 begin
2160
2161 fnd_file.put_line(fnd_file.log, 'Start of function f_return_inv_amount ');
2162
2163 v_distribution_cnt:=0;
2164
2165 for c_rec in c_inv_distribution_details('PREPAY','JA.IN.APXINWKB.DISTRIBUTIONS') --rchandan for bug#4428980
2166 loop
2167
2168 if p_attribute = 'ATTRIBUTE1' then
2169
2170 if c_rec.global_attribute1 is null then -- rchandan for bug#4333488
2171 goto continue_with_next;
2172 else
2173 v_tax_id := c_rec.global_attribute1; -- rchandan for bug#4333488
2174 end if;
2175
2176 elsif p_attribute = 'ATTRIBUTE2' then
2177
2178 if c_rec.global_attribute2 is null then -- rchandan for bug#4333488
2179 goto continue_with_next;
2180 else
2181 v_tax_id := c_rec.global_attribute2; -- rchandan for bug#4333488
2182 end if;
2183
2184
2185 elsif p_attribute = 'ATTRIBUTE3' then
2186
2187 if c_rec.global_attribute3 is null then -- rchandan for bug#4333488
2188 goto continue_with_next;
2189 else
2190 v_tax_id := c_rec.global_attribute3; -- rchandan for bug#4333488
2191 end if;
2192
2193 end if;
2194
2195 -- control comes here when the distribution line is qualified
2196 open c_get_tax_rate(v_tax_id);
2197 fetch c_get_tax_rate into v_tax_rate;
2198 close c_get_tax_rate;
2199
2200 v_distribution_cnt := v_distribution_cnt + 1;
2201
2202 v_distribution_amount_t(v_distribution_cnt) := c_rec.amount;
2203 v_tax_rate_t(v_distribution_cnt) := v_tax_rate;
2204
2205 <<continue_with_next>>
2206 null;
2207
2208 end loop; -- eligible distributions
2209
2210 -- check if any prepayment is there
2211 v_prepayment_amount := 0;
2212
2213 for c_rec in c_inv_prepayments loop
2214
2215 if p_attribute = 'ATTRIBUTE1' then
2216
2217 if c_rec.global_attribute1 is null then -- rchandan for bug#4333488
2218 goto continue_with_next_prepay;
2219 end if;
2220
2221 elsif p_attribute = 'ATTRIBUTE2' then
2222
2223 if c_rec.global_attribute2 is null then -- rchandan for bug#4333488
2224 goto continue_with_next_prepay;
2225 end if;
2226
2227 elsif p_attribute = 'ATTRIBUTE3' then
2228
2229 if c_rec.global_attribute3 is null then -- rchandan for bug#4333488
2230 goto continue_with_next_prepay;
2231 end if;
2232 end if;
2233
2234 -- control comes here when the prepayment line has tds.
2235 v_prepayment_amount := v_prepayment_amount + ( -1 * c_rec.amount) ;
2236
2237 << continue_with_next_prepay >>
2238 null;
2239
2240 end loop; -- prepayment
2241
2242 fnd_file.put_line(fnd_file.log, 'Prepayment amount : ' || v_prepayment_amount);
2243
2244 -- set the prepaid amount and net amount.
2245 for v_loop_index in 1 .. v_distribution_cnt loop
2246
2247 if v_prepayment_amount >= v_distribution_amount_t(v_loop_index) then
2248 v_prepaid_amount_t(v_loop_index) := v_distribution_amount_t(v_loop_index);
2249 else
2250 v_prepaid_amount_t(v_loop_index) := v_prepayment_amount;
2251 end if;
2252
2253 if v_prepayment_amount > 0 then
2254 v_prepayment_amount := v_prepayment_amount - v_prepaid_amount_t(v_loop_index);
2255 end if;
2256
2257 v_net_amount_t(v_loop_index) := v_distribution_amount_t(v_loop_index) - v_prepaid_amount_t(v_loop_index);
2258
2259 end loop;
2260
2261 -- calculate the tax amount and amount to be reversed based on the current prepayment amount applied;
2262 v_return_inv_amt:=0;
2263 v_prepay_to_consider := p_applied_prepay_amt;
2264
2265 for v_loop_index in 1 .. v_distribution_cnt loop
2266
2267 v_distribution_amt := v_net_amount_t(v_loop_index);
2268
2269 if v_distribution_amt > v_prepay_to_consider then
2270 v_distribution_amt := v_prepay_to_consider;
2271 end if;
2272
2273 v_return_inv_amt := v_return_inv_amt + ( v_distribution_amt * ( v_tax_rate_t(v_loop_index) / 100) );
2274 v_prepay_to_consider := v_prepay_to_consider - v_distribution_amt;
2275
2276 if v_prepay_to_consider <= 0 then
2277 exit;
2278 end if;
2279
2280 end loop;
2281
2282 fnd_file.put_line(fnd_file.log, 'End of function f_return_inv_amount. Return invoice amount :' || v_return_inv_amt);
2283 return v_return_inv_amt;
2284
2285 exception
2286 when others then
2287 fnd_file.put_line(fnd_file.log, 'Exception function f_return_inv_amount :' || sqlerrm);
2288 return 0;
2289 end f_return_inv_amount;
2290
2291 -- End function added by bug#3218881
2292
2293
2294
2295 BEGIN
2296
2297 -- DECODE(vendor_rec.exchange_rate_type,'USER',vendor_rec.exchange_rate,NULL),
2298 /*------------------------------------------------------------------------------------------
2299 FILENAME: jai_ap_tds_old_pkg.process_prepayment_apply.sql
2300
2301 CHANGE HISTORY:
2302 S.No Date Author Details
2303 -------------------------------------------------------------------------------------------------------------
2304 1 10-May-01 Ajay Sharma Procedure was prevented from firing once more (double
2305 RTN invoice and Credit Memos were getting generated)after
2306 application of prepayment if user approves the
2307 invoice (whose status becomes 'NEEDS REAPPROVAL'
2308 immediately after applying prepayment)immediately before
2309 completion of firing of related requests
2310
2311 2 06-Jun-01 Pavan Code modified to populate the goods_received_date field
2312 in interfaces so that the Credit memos are imported
2313 successfully.
2314
2315 3 09-Aug-01 Ajay Sharma Code modified to charge the RTN amount to tax account
2316
2317 4 11-Dec-01 RPK Code modified to fire this program when the payment is
2318 made/not made for the TDS Invoice of the Prepayment Invoice
2319
2320 5 08-Jan-02 RPK Credit memo invoice generation for TDS Authority,when prepayment
2321 in foreign currency is applied for foreign currency standard invoices,
2322 ie ,the credit memo should be generated with the functional currency.
2323
2324 6 05-FEB-02 RPK BUG # 2205735
2325 Code modified to generate the RTN invoice and the credit memos
2326 when the prepayment is applied to the unapproved standard invoices
2327
2328 7 20-Mar-2002 Aparajita Bug # 2272378
2329 Code modified to generate credit note and return invoice amount with correct tax
2330 when a prepayment is applied to a standard invoice.
2331
2332
2333 8 26-APR-02 Aparajita Bug # 2338345
2334 The credit memo for Tax authority was getting generated starting with
2335 the prepayment invoice number when an prepayment is applied to an unapproved
2336 invoice, Changed it to start with the standard invoice number as the program for
2337 approving the credit memo (jai_ap_tds_old_pkg.approve_invoice),expects the name to start
2338 with standard invoice number.
2339
2340 9 14-may-2002 Aparajita Bug # 2364106
2341 This procedure was giving ORA-08002:sequence
2342 JAI_AP_TDS_INVOICE_NUM_S.CURRVAL is not yet defined in this session. Changed
2343 All references to currval and also commented the cursor for_invoice_num as the
2344 cursor was referring JAI_AP_TDS_INVOICE_NUM_S.CURRVAL and passing the retrieved value
2345 to the request for approval, but that parameter was not getting used in the called object.
2346
2347 10 23-may-2002 Aparajita bug # 2385421
2348 RTN invoice and TDS CM not created on applying a prepayment to a tds invoice. The
2349 procedure was raising a no data found exception. Added Fnd_File.put_line(Fnd_File.LOG,'') statements to generate log for this concurrent.
2350
2351 11. 08-july-2002 Aparajita Bug # 2439034.
2352 Changes on the same line as that done for this bug in unapply prepayment, the
2353 object is jai_ap_tds_old_pkg.process_prepayment_unapply.
2354
2355 Change is to call the procedure for approval with code as 'APPLY' and the
2356 invoice and credit memo numbers concatenated.
2357
2358 12. 29-july-2002 Aparajita Bug # 2475416, RCS Version 615.2
2359 Approved TDS and CM setup was allowed at null site, so changed the code to look
2360 into null site setup if the site setup does not exist for the vendor for pre
2361 approved TDS and CM. This was done to find out if the approval of TDS an CM
2362 concurrent needs to be submitted.
2363
2364 12. 29-july-2002 Vijay Shankar Bug # 2508086, RCS Version 615.3
2365 During RTN invoice generation for foreign currency transaction, interface table is getting populated with exchange rate even if the exchange_rate_type
2366 is NOT USER. This is giving INCONSISTANT RATE as rejection code. This is fixed by populating NULL if the
2367 exchange_rate_type != 'USER'
2368
2369 13. 23-sep-2002 Aparajita bug # 2503751, RCS vesrion 615.4
2370 Populate the invoice id of the original invoice in attribute1 of the tds related invoice
2371 for context value 'India Original Invoice for TDS'.
2372
2373 14. 08-nov-2002 Aparajita bug # 2647361, RCS version 615.5
2374 Commented the formula at calculation of the amount for the RTN invoice and the credit note.
2375 There was no need of a if clause there and the if part was doing wrong
2376 caluclation when there were multiple distribution lines in the prepayment that is
2377 being applied.
2378
2379 15 15/01/2003 Aparajita Bug # 2738340, RCV version 615.6
2380 Added the commit interval parameter in the request for APXIIMPT. This was missing.
2381
2382 16. 10/02/2003 Aparajita Bug # 2779968. Version # 615.7.
2383 If TDS invoices does not exist for the prepayment invoice, no processing like
2384 return invoices generation is required.
2385
2386 17. 15/10/2003 kpvs bug # 3112711 kpvs, version 616.1
2387 Cursor get_prepay_dist_date incorporated to get the GL date of the negative
2388 distribution line created in ap_invoice_distributions_all when the prepay invoice
2389 is applied to a standard invoice.
2390
2391 This date is inserted as invoice_date into ap_invoices_interface and as
2392 accounting_date into ap_invoice_lines_interface. This would ensure that the CM for
2393 TDS authority and SI for the supplier that get created on cancellation of a vendor
2394 invoice, would bear proper invoice date and GL date instead of sysdate.
2395
2396 18. 22/12/2003 Aparajita Bug#3218881. Version#618.1
2397 Added inline function f_return_inv_amount to calculate the amount for the return
2398 invoices when the standard invoice on which the prepayment is being applied is
2399 approved.
2400
2401 As per the requirements, the amount to be reversed is to be based on the standard
2402 invoice if the same is approved at the time of application. Application is for a
2403 distribution line of the prepayment and with this changed approach all
2404 distribution lines of the SI with TDS attached is considered in the first line
2405 first basis to check the amount that should be reversed.
2406
2407 19. 09/02/2004 Aparajita Bug#3408429. Version#618.2
2408
2409 Whenever the standard invoice and prepeyment are in foreign currency, the exchange rate
2410 should be populated if the exchange rate type is 'User'. This is hard coded as 'USER',
2411 where as the lookup code used by base is User. Changed the code to convert the rate
2412 type to upper case and compare it with 'USER'
2413
2414 20. 02/05/2005 rchandan for bug#4333449. Version 116.1
2415 India Original Invoice for TDS DFF is eliminated. So attribute1 of ap_invoices_al
2416 is not populated whenevr an invoice is generated. Instead the Invoice details are
2417 populated into jai_ap_tds_thhold_trxs. So whenever data is inserted into interface
2418 tables the jai_ap_tds_thhold_trxs table is also populated.
2419
2420 21 11/05/2005 rchandan for bug#4333488. Version 116.2
2421 The Invoice Distribution DFF is eliminated and a new global DFF is used to
2422 maintain the functionality. From now the TDS tax, WCT tax and ESSI will not
2423 be populated in the attribute columns of ap_invoice_distributions_all table
2424 instead these will be populated in the global attribute columns. So the code changes are
2425 made accordingly.
2426 22. 14-Jul-2005 rchandan for bug#4487676.File version 117.2
2427 Sequnece jai_ap_tds_invoice_num_s is renamed to JAI_AP_TDS_THHOLD_TRXS_S1
2428
2429
2430 ----------------------------------------------------------------------------------------------------------------------*/
2431 /* Ramananda for File.Sql.35*/
2432 debug_flag := 'N' ;
2433 v_Approved_Invoice_Flag := 'N';-- added by Aparajita on 29/07/2002 for bug # 2475416
2434 /* Ramananda for File.Sql.35*/
2435
2436 Fnd_File.put_line(Fnd_File.LOG,'Starting OF PROCEDURE Ja_In_Ap_Prepay_Invoice_P');
2437 fnd_file.put_line(fnd_file.log,'invoice_id ='||p_invoice_id);
2438 fnd_file.put_line(fnd_file.log,'invoice_distribution_id ='||p_invoice_distribution_id);
2439 fnd_file.put_line(fnd_file.log,'prepay_invoice_distribution_id ='||p_prepay_dist_id);
2440
2441
2442 /*** --CHECK IF TDS INVOICE FOR PREPAYMENT INVOICE EXISTS.**************/
2443 OPEN check_for_tds_invoice(p_prepay_dist_id, p_attribute); -- replaced prepay_id with p_prepay_dist_id 25-09-00
2444 FETCH check_for_tds_invoice INTO check_tds_prepayment_rec;
2445 CLOSE check_for_tds_invoice;
2446
2447
2448 OPEN cur_prnt_pay_priority; -- 4333449
2449 FETCH cur_prnt_pay_priority INTO ln_prnt_pay_priority;
2450 CLOSE cur_prnt_pay_priority;
2451
2452 OPEN cur_prnt_exchange_rate; -- 4333449
2453 FETCH cur_prnt_exchange_rate INTO ln_prnt_exchange_rate;
2454 CLOSE cur_prnt_exchange_rate;
2455
2456
2457 -- Start added by Aparajita for bug # 2779968 on 10/02/2003
2458 if check_tds_prepayment_rec.invoice_id is null then
2459 Fnd_File.put_line(Fnd_File.LOG,
2460 'Returning as TDS does not exist for the Prepayment invoice. No further processing is required ');
2461
2462 Return;
2463 end if;
2464 -- End added by Aparajita for bug # 2779968 on 10/02/2003
2465
2466
2467
2468
2469 OPEN get_fun_det1(p_invoice_id);
2470 FETCH get_fun_det1 INTO get_fun_det_r1;
2471 CLOSE get_fun_det1;
2472
2473 X:=jai_ap_tds_old_pkg.get_invoice_status(
2474 p_invoice_id,
2475 get_fun_det_r1.invoice_amount,
2476 get_fun_det_r1.payment_status_flag,
2477 get_fun_det_r1.invoice_type_lookup_code,
2478 get_fun_det_r1.org_id);
2479
2480 v_stage:=1;
2481
2482 --IF (X = 'NEVER APPROVED' and p_param = 'I') --commented on 31-Jan-2002
2483 --OR p_param = 'U' THEN -- Condition added by Ajay Sharma on 10-May-01
2484
2485 IF p_param = 'U' THEN --added on 31-Jan-2002
2486 x:= NULL;
2487 Fnd_File.put_line(Fnd_File.LOG,'END OF PROCEDURE Ja_In_Ap_Prepay_Invoice_P AS param = U');
2488 RETURN;
2489 END IF;
2490
2491
2492
2493 IF check_tds_prepayment_rec.invoice_id IS NOT NULL THEN --1
2494
2495 Fnd_File.put_line(Fnd_File.LOG,'Inside IF check_tds_prepayment_rec.invoice_id IS NOT NULL');
2496 v_stage:=2;
2497
2498 --GET VENDOR ID BASED ON THE TAX-ID FOR THE PREPAYMENT INVOICE.
2499
2500 OPEN for_vendor_id(check_tds_prepayment_rec.tds_tax_id);
2501 FETCH for_vendor_id INTO for_vendor_id_prepay_rec;
2502 CLOSE for_vendor_id;
2503
2504 --GET PAYMENT_STATUS, INVOICE_AMOUNT AND INVOICE_ID OF TDS INVOICE FOR PREPAYMENT INVOICE.
2505
2506 OPEN for_payment_status(check_tds_prepayment_rec.tds_invoice_num,
2507 for_vendor_id_prepay_rec.vendor_id,
2508 NVL(check_tds_prepayment_rec.organization_id, 0)
2509 );
2510 FETCH for_payment_status INTO for_payment_status_prepay_rec;
2511 CLOSE for_payment_status;
2512
2513 OPEN for_std_invoice(p_invoice_id); --added on 05-Feb-2002
2514 FETCH for_std_invoice INTO for_std_invoice_rec;
2515 CLOSE for_std_invoice;
2516
2517 -- CHECK IF TDS INVOICE FOR ORIGINAL INVOICE EXISTS.
2518
2519 OPEN check_for_tds_invoice_o(p_invoice_id,p_attribute); -- *NEW.invoice_id
2520 FETCH check_for_tds_invoice_o INTO check_tds_original_rec; -- Commented on 02-Feb-2002
2521
2522
2523 IF check_for_tds_invoice_o%FOUND THEN --2
2524 --added on 05-Feb-2002
2525 Fnd_File.put_line(Fnd_File.LOG,'Inside IF check_for_tds_invoice_o%FOUND');
2526 v_stage:=3;
2527 var_tds_invoice_num := check_tds_original_rec.tds_invoice_num;
2528 var_invoice_id := check_tds_original_rec.invoice_ID;
2529 var_tds_tax_id := check_tds_original_rec.tds_tax_id;
2530 var_organization_id := check_tds_original_rec.organization_id;
2531 ln_tax_rate := check_tds_original_rec.tds_tax_rate;
2532 ln_tax_amount := check_tds_original_rec.tds_amount;
2533 ln_taxable_amount := check_tds_original_rec.invoice_amount;
2534
2535 -- line below added by Aparajita for bug 2338345.
2536 v_tds_cm_num := check_tds_original_rec.tds_invoice_num;
2537
2538 ELSIF check_for_tds_invoice_o%NOTFOUND THEN
2539
2540 Fnd_File.put_line(Fnd_File.LOG,'Inside ELSIF check_for_tds_invoice_o%NOTFOUND');
2541 v_stage:=4;
2542 var_tds_invoice_num := check_tds_prepayment_rec.tds_invoice_num;
2543 var_invoice_id := check_tds_prepayment_rec.invoice_ID;
2544 var_tds_tax_id := check_tds_prepayment_rec.tds_tax_id;
2545 var_organization_id := check_tds_prepayment_rec.organization_id;
2546 ln_tax_rate := check_tds_prepayment_rec.tds_tax_rate;
2547 ln_tax_amount := check_tds_prepayment_rec.tds_amount;
2548 ln_taxable_amount := check_tds_prepayment_rec.invoice_amount;
2549
2550
2551 END IF;
2552
2553 -- following If added by Aparajita on 29th April for bug # 2338345
2554 IF v_tds_cm_num IS NULL THEN
2555 -- the TDS invoice corrosponding to the standard invoice is not found, this would be when
2556 -- the standard invoice is not approved yet.
2557 -- the CM for TDS authortiy should then should have the standard invoice number suffixed by TDS
2558 v_tds_cm_num := for_std_invoice_rec.invoice_num || 'TDS';
2559 END IF;
2560
2561 --end addition of 05-Feb-2002
2562
2563 --GET VENDOR ID BASED ON THE TAX-ID FOR THE ORIGINAL INVOICE.
2564 OPEN for_vendor_id(var_tds_tax_id);
2565 FETCH for_vendor_id INTO for_vendor_id_original_rec;
2566 CLOSE for_vendor_id;
2567
2568 --GET PAYMENT_STATUS, INVOICE_AMOUNT AND INVOICE_ID OF TDS INVOICE FOR ORIGINAL INVOICE.
2569
2570 OPEN for_payment_status(var_tds_invoice_num,
2571 for_vendor_id_original_rec.vendor_id,
2572 NVL(var_organization_id, 0)
2573 );
2574
2575 FETCH for_payment_status INTO for_pay_status_original_rec;
2576 CLOSE for_payment_status;
2577
2578 --GET PREPAYMENT TDS INVOICE_AMOUNT, THIS IS THE AMOUNT OF INVOICE ORIGINALLY CREATED
2579 --AND NOT THE LATEST AMOUNT
2580
2581 OPEN for_payment_amount(for_payment_status_prepay_rec.invoice_id);
2582 FETCH for_payment_amount INTO for_prepay_payment_amount_rec;
2583 CLOSE for_payment_amount;
2584
2585 --CALCULATING THE PROPORTIONATE TAX TO BE APPLIED.
2586
2587 Fnd_File.put_line(Fnd_File.LOG,'calculating the proportionate tax TO be applied');
2588 Fnd_File.put_line(Fnd_File.LOG,'p_amount = ' || p_amount);
2589 Fnd_File.put_line(Fnd_File.LOG,'for_prepay_payment_amount_rec.amount = ' || for_prepay_payment_amount_rec.amount);
2590 Fnd_File.put_line(Fnd_File.LOG,'check_tds_prepayment_rec.invoice_amount = ' || check_tds_prepayment_rec.invoice_amount);
2591
2592 v_stage:=5;
2593
2594 -- the following if commented by Aparajita for bug # 2647361
2595 -- This was creating problem when prepayment has multiple line and partail amount is being applied.
2596 -- There is no need for this if
2597 /*
2598 IF var_invoice_amount = var_amt_applied + p_amount THEN --13
2599 insertion_amount := (for_prepay_payment_amount_rec.amount - var_amt_reversed);
2600 ELSE --13
2601 -- insertion_amount :=FLOOR(p_amount * ( for_prepay_payment_amount_rec.amount / var_invoice_amount));
2602
2603 -- The above line changed to the line below by Aparajita for correct calculation of tax on 20-mar-2002
2604 -- the function FLOOR was also changed to ROUND. Bug # 2272378
2605 end comment by Aparajita on 08/11/2002 for bug # 2647361
2606 */
2607
2608 -- start added for bug#3218881
2609 if check_for_tds_invoice_o%found then
2610 -- SI has been approved at the time of application and has TDS invoices generated.
2611 insertion_amount := round(f_return_inv_amount(p_invoice_id, p_invoice_distribution_id, p_amount, p_attribute));
2612 else
2613 -- SI has not been approved at the time of application and has no TDS invoices generated.
2614 insertion_amount :=round (p_amount * ( for_prepay_payment_amount_rec.amount / check_tds_prepayment_rec.invoice_amount));
2615 end if;
2616 -- End added for bug#3218881
2617
2618 -- END IF; --13
2619
2620
2621 IF for_pay_status_original_rec.payment_status_flag = 'N'
2622 -- AND for_payment_status_prepay_rec.payment_status_flag = 'N' --Commented on 11-Dec-01
2623 AND for_payment_status_prepay_rec.payment_status_flag IN ('Y','N') --Added on 11-Dec-01
2624 AND for_vendor_id_original_rec.vendor_id = for_vendor_id_prepay_rec.vendor_id THEN
2625
2626 ----------------CASE 1
2627 Fnd_File.put_line(Fnd_File.LOG,'inside case1 : payment_status_flag = N');
2628 v_stage:=6;
2629
2630 OPEN for_std_invoice(for_pay_status_original_rec.invoice_id);
2631 FETCH for_std_invoice INTO for_std_invoice_tds_rec;
2632 CLOSE for_std_invoice;
2633
2634 OPEN for_distribution_insertion(for_pay_status_original_rec.invoice_id);
2635 FETCH for_distribution_insertion INTO for_dist_insertion_tds_rec ;
2636 CLOSE for_distribution_insertion ;
2637
2638 --03-Jan-2002
2639 OPEN get_base_inv_id(for_std_invoice_tds_rec.invoice_num);
2640 FETCH get_base_inv_id INTO get_base_inv_id_rec;
2641 CLOSE get_base_inv_id;
2642
2643 OPEN Get_exchange_rate_base_inv(get_base_inv_id_rec.invoice_id);
2644 FETCH get_exchange_rate_base_inv INTO get_exchange_rate_base_inv_rec;
2645 CLOSE get_exchange_rate_base_inv;
2646 --end 03-Jan-2002
2647
2648 v_ja_ap_invoices_interface_no := NULL;
2649 SELECT JAI_AP_TDS_THHOLD_TRXS_S1.nextval--JAI_AP_TDS_INVOICE_NUM_S.NEXTVAL changed by rchandan for bug#4487676
2650 INTO v_ja_ap_invoices_interface_no
2651 FROM dual;
2652
2653 v_ap_invoices_interface_no := NULL;
2654 SELECT ap_invoices_interface_s.NEXTVAL
2655 INTO v_ap_invoices_interface_no
2656 FROM dual;
2657
2658 Open get_prepay_dist_date; -- bug 3112711 kpvs
2659 fetch get_prepay_dist_date into v_prepay_dist_date;
2660 close get_prepay_dist_date;
2661
2662 Fnd_File.put_line(Fnd_File.LOG,
2663 'BEFORE INSERTING INTO ap_invoices_interface 1 : CREDIT NOTE FOR TDS AUTHORITY FOR APPLIED AMOUNT OF TAX');
2664
2665 v_stage:=7;
2666
2667 /* Modified by Ramananda for bug# 4407184 ,start */
2668
2669
2670 lv_tds_cm_num := v_tds_cm_num ||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no);
2671 lv_credit_note_tds := 'CREDIT NOTE FOR TDS AUTHORITY FOR APPLIED AMOUNT OF TAX '||for_std_invoice_tds_rec.invoice_num||' OR '||TO_CHAR(p_invoice_id) ;
2672 lv_invoice_num_cm := for_std_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no) ;
2673
2674
2675 /* start additions by ssumaith - bug# 4448789 */
2676 jai_cmn_utils_pkg.GET_LE_INFO(
2677 P_API_VERSION => NULL ,
2678 P_INIT_MSG_LIST => NULL ,
2679 P_COMMIT => NULL ,
2680 P_LEDGER_ID => NULL,
2681 P_BSV => NULL,
2682 P_ORG_ID => P_ORG_ID,
2683 X_RETURN_STATUS => lv_return_status ,
2684 X_MSG_COUNT => ln_msg_count,
2685 X_MSG_DATA => ln_msg_data,
2686 X_LEGAL_ENTITY_ID => ln_legal_entity_id ,
2687 X_LEGAL_ENTITY_NAME => lv_legal_entity_name
2688 );
2689 /* ends additions by ssumaith - bug# 4448789*/
2690
2691
2692 INSERT INTO ap_invoices_interface (
2693 invoice_id,
2694 invoice_num,
2695 invoice_type_lookup_code,
2696 invoice_date,
2697 vendor_id,
2698 vendor_site_id,
2699 invoice_amount,
2700 invoice_currency_code,
2701 exchange_rate,
2702 exchange_rate_type,
2703 exchange_date,
2704 terms_id,
2705 description,
2706 source,
2707 -- voucher_num, Harshita for Bug 4870243
2708 payment_method_lookup_code,
2709 pay_group_lookup_code,
2710 org_id,
2711 legal_entity_id ,
2712 created_by,
2713 creation_date,
2714 last_updated_by,
2715 last_update_date,
2716 last_update_login,
2717 group_id /*Bug 4716884*/
2718 ) VALUES (
2719 v_ap_invoices_interface_no,
2720 --for_std_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.NEXTVAL),
2721 -- v_tds_cm_num ||'/'|| 'CM/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.NEXTVAL)
2722 lv_tds_cm_num , --v_tds_cm_num ||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
2723 -- the line below is being commented and replaced by line above by Aparajita on 29 apr 2002 for bug 2338345
2724 -- var_tds_invoice_num||'/'|| 'CM/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.NEXTVAL),
2725 'CREDIT',
2726 v_prepay_dist_date,-- bug 3112711 kpvs --TRUNC( SYSDATE ), --for_std_invoice_tds_rec.invoice_date,
2727 for_std_invoice_tds_rec.vendor_id,
2728 for_std_invoice_tds_rec.vendor_site_id,
2729 --(-1)*insertion_amount, --commented on 13-Dec-2001
2730 ((-1)*insertion_amount * NVL(get_exchange_rate_base_inv_rec.exchange_rate,1)), --added on 03-Jan-2002
2731 for_std_invoice_tds_rec.invoice_currency_code,
2732 for_std_invoice_tds_rec.exchange_rate,
2733 for_std_invoice_tds_rec.exchange_rate_type,
2734 for_std_invoice_tds_rec.exchange_date,
2735 for_std_invoice_tds_rec.terms_id,
2736 lv_credit_note_tds, --'CREDIT NOTE FOR TDS AUTHORITY FOR APPLIED AMOUNT OF TAX '||for_std_invoice_tds_rec.invoice_num||' OR '||TO_CHAR(p_invoice_id) ,
2737 'INDIA TDS', /* --'TDS',--Ramanand for bug#4388958 */
2738 -- for_std_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.CURRVAL),
2739 -- lv_invoice_num_cm, --for_std_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no), Harshita for Bug 4870243
2740 for_std_invoice_tds_rec.payment_method_lookup_code,
2741 for_std_invoice_tds_rec.pay_group_lookup_code,
2742 p_org_id,
2743 ln_legal_entity_id,
2744 p_created_by,
2745 p_creation_date,
2746 p_last_updated_by,
2747 p_last_update_date,
2748 p_last_updated_by,
2749 to_char(p_invoice_id) /*Bug 4716884*/
2750 );
2751
2752 /* Modified by Ramananda for bug# 4407184 , end */
2753
2754 -- following line added by Aparajita for bug # 2439034.
2755 v_ap_tds_cm_num := v_tds_cm_num ||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no);
2756
2757 Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoice_lines_interface 1 : CREDIT NOTE FOR TDS TAX AUTHORITY FOR TAX APPLIED');
2758 v_stage:=8;
2759
2760
2761 /* Modified by Ramananda for bug# 4407184 ,start */
2762
2763 lv_credit_note_tds := 'CREDIT NOTE FOR TDS TAX AUTHORITY FOR TAX APPLIED BY '||TO_CHAR(p_invoice_id)||' OR '||TO_CHAR(p_invoice_distribution_id) ;
2764
2765 INSERT INTO ap_invoice_lines_interface (
2766 invoice_id,
2767 invoice_line_id,
2768 line_number,
2769 line_type_lookup_code,
2770 amount,
2771 accounting_date,
2772 description,
2773 dist_code_combination_id,
2774 created_by,
2775 creation_date,
2776 last_updated_by,
2777 last_update_date,
2778 last_update_login
2779
2780 ) VALUES (
2781 v_ap_invoices_interface_no,
2782 ap_invoice_lines_interface_s.NEXTVAL,
2783 1, --THERE WILL ALWAYS BE ONLY ONE LINE.
2784 'ITEM',
2785 --(-1)*insertion_amount,--commented on 13-Dec-2001
2786 (-1)*insertion_amount * NVL(get_exchange_rate_base_inv_rec.exchange_rate,1), --added on 03-Jan-2002
2787 v_prepay_dist_date, -- bug 3112711 kpvsTRUNC( SYSDATE )
2788 lv_credit_note_tds, --'CREDIT NOTE FOR TDS TAX AUTHORITY FOR TAX APPLIED BY '||TO_CHAR(p_invoice_id)||' OR '||TO_CHAR(p_invoice_distribution_id),
2789 for_dist_insertion_tds_rec.dist_code_combination_id,
2790 p_created_by,
2791 p_creation_date,
2792 p_last_updated_by,
2793 p_last_update_date,
2794 p_last_updated_by
2795
2796 );
2797
2798 /* Modified by Ramananda for bug# 4407184 , end */
2799
2800 /*OPEN for_std_invoice(p_invoice_id);
2801 FETCH for_std_invoice INTO for_std_invoice_rec;
2802 CLOSE for_std_invoice;
2803 */ --commented on 05-Feb-2002
2804
2805 OPEN for_distribution_insertion(p_invoice_id);
2806 FETCH for_distribution_insertion INTO for_dist_insertion_rec;
2807 CLOSE for_distribution_insertion;
2808
2809 v_ja_ap_invoices_interface_no := NULL;
2810
2811 SELECT JAI_AP_TDS_THHOLD_TRXS_S1.nextval--JAI_AP_TDS_INVOICE_NUM_S.NEXTVAL changed by rchandan for bug#4487676
2812 INTO v_ja_ap_invoices_interface_no
2813 FROM dual;
2814
2815 v_ap_invoices_interface_no := NULL;
2816
2817 SELECT ap_invoices_interface_s.NEXTVAL
2818 INTO v_ap_invoices_interface_no
2819 FROM dual;
2820
2821 Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoices_interface 2 : STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED');
2822 v_stage:=9;
2823
2824 /* Modified by Ramananda for bug# 4407184 ,start */
2825
2826 lv_rtn_invoice_num := 'RTN/'||for_std_invoice_rec.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no);
2827 lv_standard_invoice_num := 'STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED ON '||for_std_invoice_rec.invoice_num||' OR '||TO_CHAR(p_invoice_id); -- NEW.prepay_id replaced with p_invoice_id
2828
2829
2830 /* start additions by ssumaith - bug# 4448789 */
2831 jai_cmn_utils_pkg.GET_LE_INFO(
2832 P_API_VERSION => NULL ,
2833 P_INIT_MSG_LIST => NULL ,
2834 P_COMMIT => NULL ,
2835 P_LEDGER_ID => NULL,
2836 P_BSV => NULL,
2837 P_ORG_ID => P_ORG_ID,
2838 X_RETURN_STATUS => lv_return_status ,
2839 X_MSG_COUNT => ln_msg_count,
2840 X_MSG_DATA => ln_msg_data,
2841 X_LEGAL_ENTITY_ID => ln_legal_entity_id ,
2842 X_LEGAL_ENTITY_NAME => lv_legal_entity_name
2843 );
2844 /* ends additions by ssumaith - bug# 4448789*/
2845
2846 INSERT INTO ap_invoices_interface (
2847 invoice_id,
2848 invoice_num,
2849 invoice_type_lookup_code,
2850 invoice_date,
2851 vendor_id,
2852 vendor_site_id,
2853 invoice_amount,
2854 invoice_currency_code,
2855 exchange_rate,
2856 exchange_rate_type,
2857 exchange_date,
2858 terms_id,
2859 description,
2860 source,
2861 -- voucher_num, Harshita for Bug 4870243
2862 payment_method_lookup_code,
2863 pay_group_lookup_code,
2864 org_id,
2865 legal_entity_id,
2866 created_by,
2867 creation_date,
2868 last_updated_by,
2869 last_update_date,
2870 last_update_login,
2871 goods_received_date,
2872 invoice_received_date, --Added by pavan on 06-Jun-01
2873 group_id /*Bug 4716884*/
2874 ) VALUES (
2875 v_ap_invoices_interface_no,
2876 -- 'RTN/'||for_std_invoice_rec.invoice_num|| '/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.NEXTVAL),
2877 lv_rtn_invoice_num, --'RTN/'||for_std_invoice_rec.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
2878 'STANDARD',
2879 v_prepay_dist_date, --bug 3112711 kpvs -- TRUNC(SYSDATE), --for_std_invoice_rec.invoice_date,
2880 for_std_invoice_rec.vendor_id,
2881 for_std_invoice_rec.vendor_site_id,
2882 insertion_amount,
2883 for_std_invoice_rec.invoice_currency_code,
2884 -- for_std_invoice_rec.exchange_rate, -- commented by cbabu for Bug#2508086
2885 DECODE( upper(for_std_invoice_rec.exchange_rate_type), 'USER', for_std_invoice_rec.exchange_rate, NULL),
2886 -- Bug#3408429, cbabu for Bug#2508086
2887 for_std_invoice_rec.exchange_rate_type,
2888 for_std_invoice_rec.exchange_date,
2889 for_std_invoice_rec.terms_id,
2890 lv_standard_invoice_num , --'STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED ON '||for_std_invoice_rec.invoice_num||' OR '||TO_CHAR(p_invoice_id), -- NEW.prepay_id replaced with p_invoice_id
2891 'INDIA TDS', /* --'TDS', --Ramanand for bug#4388958 */
2892 -- 'RTN/'||for_std_invoice_rec.invoice_num|| '/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.CURRVAL),
2893 -- lv_rtn_invoice_num , --'RTN/'||for_std_invoice_rec.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no), Harshita for Bug 4870243
2894 for_std_invoice_rec.payment_method_lookup_code,
2895 for_std_invoice_rec.pay_group_lookup_code,
2896 p_org_id,
2897 ln_legal_entity_id,
2898 p_created_by,
2899 p_creation_date,
2900 p_last_updated_by,
2901 p_last_update_date,
2902 p_last_updated_by,
2903 for_std_invoice_rec.goods_received_date,
2904 for_std_invoice_rec.invoice_received_date,
2905 to_char(p_invoice_id) /*Bug 4716884*/
2906 ); --Added by pavan on 06-Jun-01
2907
2908 /* Modified by Ramananda for bug# 4407184 , end */
2909
2910
2911 -- following line added by Aparajita on 07/07/2002 for bug # 2439034
2912 v_ap_sup_inv_num := 'RTN/'||for_std_invoice_rec.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no);
2913
2914 Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoice_lines_interface 2 : STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED');
2915 v_stage:=10;
2916
2917 /* Modified by Ramananda for bug# 4407184 , start */
2918 lv_standard_invoice_num := 'STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED ON '||TO_CHAR(p_invoice_id)||' OR '||TO_CHAR(p_invoice_distribution_id) ;
2919
2920 INSERT INTO ap_invoice_lines_interface
2921 (
2922 invoice_id,
2923 invoice_line_id,
2924 line_number,
2925 line_type_lookup_code,
2926 amount,
2927 accounting_date,
2928 description,
2929 dist_code_combination_id,
2930 created_by,
2931 creation_date,
2932 last_updated_by,
2933 last_update_date,
2934 last_update_login
2935 )
2936 VALUES
2937 (
2938 v_ap_invoices_interface_no,
2939 ap_invoice_lines_interface_s.NEXTVAL,
2940 1, --THERE WILL ALWAYS BE ONLY ONE LINE.
2941 'ITEM',
2942 insertion_amount,
2943 v_prepay_dist_date, --bug 3112711 kpvs TRUNC( SYSDATE ),
2944 lv_standard_invoice_num, --'STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED ON '||TO_CHAR(p_invoice_id)||' OR '||TO_CHAR(p_invoice_distribution_id) ,
2945 --for_dist_insertion_rec.dist_code_combination_id, --Commented by Ajay Sharma on 09-AUG-01
2946 for_dist_insertion_tds_rec.dist_code_combination_id, --Added by Ajay Sharma --on 09-AUG-01
2947 p_created_by,
2948 p_creation_date,
2949 p_last_updated_by,
2950 p_last_update_date,
2951 p_last_updated_by
2952 );
2953
2954 /* Modified by Ramananda for bug# 4407184 , end */
2955
2956 jai_ap_tds_generation_pkg.insert_tds_thhold_trxs -------4333449
2957 (
2958 p_invoice_id => p_invoice_id,
2959 p_tds_event => 'OLD TDS INVOICE PREPAY',
2960 p_tax_id => var_tds_tax_id,
2961 p_tax_rate => ln_tax_rate,
2962 p_taxable_amount => ln_taxable_amount,
2963 p_tax_amount => ln_tax_amount,
2964 p_tds_authority_vendor_id => for_std_invoice_tds_rec.vendor_id,
2965 p_tds_authority_vendor_site_id => for_std_invoice_tds_rec.vendor_site_id,
2966 p_invoice_tds_authority_num => v_tds_cm_num ||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
2967 p_invoice_tds_authority_type => 'CREDIT',
2968 p_invoice_tds_authority_curr => for_std_invoice_tds_rec.invoice_currency_code,
2969 p_invoice_tds_authority_amt => ((-1)*insertion_amount * NVL(get_exchange_rate_base_inv_rec.exchange_rate,1)),
2970 p_vendor_id => for_std_invoice_rec.vendor_id,
2971 p_vendor_site_id => for_std_invoice_rec.vendor_site_id,
2972 p_invoice_vendor_num => 'RTN/'||for_std_invoice_rec.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
2973 p_invoice_vendor_type => 'STANDARD',
2974 p_invoice_vendor_curr => for_std_invoice_rec.invoice_currency_code,
2975 p_invoice_vendor_amt => insertion_amount,
2976 p_parent_inv_payment_priority => ln_prnt_pay_priority,
2977 p_parent_inv_exchange_rate => ln_prnt_exchange_rate
2978 );
2979
2980
2981 Fnd_File.put_line(Fnd_File.LOG,'BEFORE submitting the Payables OPEN INTERFACE Import concurrent request 1');
2982 v_stage:=11;
2983 result := Fnd_Request.set_mode(TRUE);
2984
2985 lv_batch_name := jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id); --Ramananda for Bug#4584221
2986
2987 req_id := Fnd_Request.submit_request
2988 (
2989 'SQLAP',
2990 'APXIIMPT',
2991 'Localization Payables OPEN INTERFACE Import',
2992 '',
2993 FALSE,
2994 /* Bug 4774647. Added by Lakshmi Gopalsami
2995 Passed operating unit also as this parameter has been
2996 added by base .
2997 */
2998 '',
2999 'INDIA TDS', /* --'TDS', --Ramanand for bug#4388958*/
3000 to_char(p_invoice_id), /*Bug 4716884*/
3001 --'TDS'||TO_CHAR(TRUNC(SYSDATE)),
3002 --commented the above and added the below by Ramananda for bug#4584221
3003 lv_batch_name,
3004 '',
3005 '',
3006 '',
3007 'Y',
3008 'N',
3009 'N',
3010 'N',
3011 1000, -- commit interval parameter added by Aparajita for bug # 2738340 on 15/01/2003
3012 p_created_by,
3013 p_last_updated_by
3014 );
3015
3016 upd_inv_id := var_invoice_id ;
3017
3018 UPDATE JAI_AP_TDS_INVOICES
3019 SET amt_reversed = NVL(amt_reversed,0) + insertion_amount,
3020 amt_applied = NVL(amt_applied,0) + p_amount
3021 WHERE invoice_id = upd_inv_id ;
3022
3023
3024 END IF; -- CASE 1
3025
3026 /*FOR App_Flag_Rec IN Fetch_App_Inv_Flag_Cur( for_std_invoice_rec.vendor_id,for_std_invoice_rec.vendor_site_id ) LOOP
3027
3028 IF App_Flag_Rec.Approved_Invoice_Flag = 'Y' THEN
3029 */
3030 /* This has been commented by Aparajita as this variable v_invoice_num is no more used and
3031 this cursor was using currval for sequence JAI_AP_TDS_INVOICE_NUM_S,bug 2364106
3032
3033 OPEN for_invoice_num(for_std_invoice_rec.invoice_num);
3034 FETCH for_invoice_num INTO v_invoice_num;
3035 CLOSE for_invoice_num;
3036 end comment by Aparajita on 15th May for bug 2364106 */
3037
3038 v_Approved_Invoice_Flag := 'N';
3039
3040 BEGIN
3041
3042 SELECT NVL( Approved_Invoice_Flag, 'N' )
3043 INTO v_Approved_Invoice_Flag
3044 FROM JAI_CMN_VENDOR_SITES
3045 WHERE Vendor_Id = for_std_invoice_rec.vendor_id
3046 AND Vendor_Site_Id = for_std_invoice_rec.vendor_site_id;
3047
3048 EXCEPTION
3049
3050 WHEN NO_DATA_FOUND THEN
3051
3052 BEGIN
3053
3054 SELECT NVL( Approved_Invoice_Flag, 'N' )
3055 INTO v_Approved_Invoice_Flag
3056 FROM JAI_CMN_VENDOR_SITES
3057 WHERE Vendor_Id = for_std_invoice_rec.vendor_id
3058 AND Vendor_Site_Id = 0;
3059
3060 EXCEPTION
3061 WHEN NO_DATA_FOUND THEN
3062 NULL;
3063 END;
3064
3065 END;
3066
3067 IF v_Approved_Invoice_Flag = 'Y' THEN
3068
3069 Fnd_File.put_line(Fnd_File.LOG,'BEFORE submitting the Approval OF TDS AND CM concurrent request FOR invoice id '|| p_invoice_id);
3070 v_stage:=12;
3071 result := Fnd_Request.set_mode(TRUE);
3072 req1_id := Fnd_Request.submit_request
3073 (
3074 'JA',
3075 'JAINAPIN',
3076 'Approval OF TDS AND CM - Localization)',
3077 SYSDATE,
3078 FALSE,
3079 req_id,
3080 for_std_invoice_rec.vendor_id,
3081 for_std_invoice_rec.vendor_site_id,
3082 p_invoice_id, -- NEW.Invoice_Id replaced with p_invoice_id
3083 v_ap_tds_cm_num || ';' || v_ap_sup_inv_num, -- Changed by aparajita on 07/07/2002 for bug 2439034, earlier was null
3084 'APPLY'
3085 );
3086
3087 END IF;
3088
3089 -- END LOOP;
3090
3091
3092 IF for_pay_status_original_rec.payment_status_flag <> 'N'
3093 OR for_vendor_id_original_rec.vendor_id <> for_vendor_id_prepay_rec.vendor_id THEN
3094
3095 Fnd_File.put_line(Fnd_File.LOG,'Inside Case2 : payment_status_flag <> N ');
3096 v_stage:=13;
3097 -- CASE 2
3098
3099 OPEN for_std_invoice(for_payment_status_prepay_rec.invoice_id);
3100 FETCH for_std_invoice INTO for_pre_invoice_tds_rec;
3101 CLOSE for_std_invoice;
3102
3103 OPEN for_distribution_insertion(for_payment_status_prepay_rec.invoice_id);
3104 FETCH for_distribution_insertion INTO for_dist_insertion_tds_rec ;
3105 CLOSE for_distribution_insertion ;
3106
3107 v_ja_ap_invoices_interface_no := NULL;
3108
3109 SELECT JAI_AP_TDS_THHOLD_TRXS_S1.nextval--JAI_AP_TDS_INVOICE_NUM_S.NEXTVAL changed by rchandan for bug#4487676
3110 INTO v_ja_ap_invoices_interface_no
3111 FROM dual;
3112
3113 v_ap_invoices_interface_no := NULL;
3114 SELECT ap_invoices_interface_s.NEXTVAL
3115 INTO v_ap_invoices_interface_no
3116 FROM dual;
3117
3118 Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoices_interface 3:CREDIT NOTE FOR TDS AUTHORITY FOR APPLIED AMOUNT OF TAX');
3119 v_stage:=14;
3120
3121 /* Modified by Ramananda for bug# 4407184 ,start */
3122
3123 lv_invoice_num :=for_pre_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no);
3124 lv_credit_tds_auth :='CREDIT NOTE FOR TDS AUTHORITY FOR APPLIED AMOUNT OF TAX '||for_pre_invoice_tds_rec.invoice_num||' OR '||TO_CHAR(p_invoice_id) ;
3125
3126 /* start additions by ssumaith - bug# 4448789 */
3127 jai_cmn_utils_pkg.GET_LE_INFO(
3128 P_API_VERSION => NULL ,
3129 P_INIT_MSG_LIST => NULL ,
3130 P_COMMIT => NULL ,
3131 P_LEDGER_ID => NULL,
3132 P_BSV => NULL,
3133 P_ORG_ID => P_ORG_ID,
3134 X_RETURN_STATUS => lv_return_status ,
3135 X_MSG_COUNT => ln_msg_count,
3136 X_MSG_DATA => ln_msg_data,
3137 X_LEGAL_ENTITY_ID => ln_legal_entity_id ,
3138 X_LEGAL_ENTITY_NAME => lv_legal_entity_name
3139 );
3140 /* ends additions by ssumaith - bug# 4448789*/
3141
3142 INSERT INTO ap_invoices_interface (
3143 invoice_id,
3144 invoice_num,
3145 invoice_type_lookup_code,
3146 invoice_date,
3147 vendor_id,
3148 vendor_site_id,
3149 invoice_amount,
3150 invoice_currency_code,
3151 exchange_rate,
3152 exchange_rate_type,
3153 exchange_date,
3154 terms_id,
3155 description,
3156 source,
3157 -- voucher_num, Harshita for Bug 4870243
3158 payment_method_lookup_code,
3159 pay_group_lookup_code,
3160 org_id,
3161 legal_entity_id,
3162 created_by,
3163 creation_date,
3164 last_updated_by,
3165 last_update_date,
3166 last_update_login,
3167 group_id /*Bug 4716884*/
3168 ) VALUES (
3169 v_ap_invoices_interface_no,
3170 -- for_pre_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.NEXTVAL),
3171 lv_invoice_num, --for_pre_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
3172 'CREDIT',
3173 v_prepay_dist_date, --bug 3112711 kpvs TRUNC( SYSDATE ) --for_std_invoice_tds_rec.invoice_date,
3174 for_pre_invoice_tds_rec.vendor_id,
3175 for_pre_invoice_tds_rec.vendor_site_id,
3176 --(-1)*insertion_amount,
3177 (-1)*insertion_amount * NVL(get_exchange_rate_base_inv_rec.exchange_rate,1), --added on 03-Jan-2002
3178 for_pre_invoice_tds_rec.invoice_currency_code,
3179 for_pre_invoice_tds_rec.exchange_rate,
3180 for_pre_invoice_tds_rec.exchange_rate_type,
3181 for_pre_invoice_tds_rec.exchange_date,
3182 for_pre_invoice_tds_rec.terms_id,
3183 lv_credit_tds_auth, --'CREDIT NOTE FOR TDS AUTHORITY FOR APPLIED AMOUNT OF TAX '||for_pre_invoice_tds_rec.invoice_num||' OR '||TO_CHAR(p_invoice_id) ,
3184 'INDIA TDS', /* --'TDS',--Ramanand for bug#4388958*/
3185 -- for_pre_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.CURRVAL),
3186 -- lv_invoice_num, --for_pre_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no), Harshita for Bug 4870243
3187 for_pre_invoice_tds_rec.payment_method_lookup_code,
3188 for_pre_invoice_tds_rec.pay_group_lookup_code,
3189 p_org_id,
3190 ln_legal_entity_id,
3191 p_created_by,
3192 p_creation_date,
3193 p_last_updated_by,
3194 p_last_update_date,
3195 p_last_updated_by,
3196 to_char(p_invoice_id) /*Bug 4716884*/
3197 );
3198
3199 /* Modified by Ramananda for bug# 4407184 , end */
3200
3201 -- following line added by Aparajita for bug # 2439034.
3202 v_ap_tds_cm_num := for_pre_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no);
3203
3204 Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoice_lines_interface 3 :CREDIT NOTE FOR TDS TAX AUTHORITY FOR TAX APPLIED' );
3205 v_stage:=14;
3206
3207 /* Modified by Ramananda for bug# 4407184 , start */
3208 lv_credit_note_tds := 'CREDIT NOTE FOR TDS TAX AUTHORITY FOR TAX APPLIED BY '||TO_CHAR(p_invoice_id)||' OR '||TO_CHAR(p_invoice_distribution_id);
3209
3210 INSERT INTO ap_invoice_lines_interface (
3211 invoice_id,
3212 invoice_line_id,
3213 line_number,
3214 line_type_lookup_code,
3215 amount,
3216 accounting_date,
3217 description,
3218 dist_code_combination_id,
3219 created_by,
3220 creation_date,
3221 last_updated_by,
3222 last_update_date,
3223 last_update_login
3224 ) VALUES (
3225 v_ap_invoices_interface_no,
3226 ap_invoice_lines_interface_s.NEXTVAL,
3227 1, --THERE WILL ALWAYS BE ONLY ONE LINE.
3228 'ITEM',
3229 --(-1)*insertion_amount,--commented on 13-Dec-2001
3230 (-1)*insertion_amount * NVL(get_exchange_rate_base_inv_rec.exchange_rate,1), --added on 03-Jan-2002
3231 v_prepay_dist_date, --bug 3112711 kpvs TRUNC( SYSDATE )
3232 lv_credit_note_tds, --'CREDIT NOTE FOR TDS TAX AUTHORITY FOR TAX APPLIED BY '||TO_CHAR(p_invoice_id)||' OR '||TO_CHAR(p_invoice_distribution_id),
3233 for_dist_insertion_tds_rec.dist_code_combination_id,
3234 p_created_by,
3235 p_creation_date,
3236 p_last_updated_by,
3237 p_last_update_date,
3238 p_last_updated_by
3239 );
3240
3241 /* Modified by Ramananda for bug# 4407184 , end */
3242
3243 OPEN for_std_invoice(var_invoice_id);
3244 FETCH for_std_invoice INTO for_std_inv_rec_r;
3245 CLOSE for_std_invoice;
3246
3247 OPEN for_distribution_insertion(var_invoice_id);
3248 FETCH for_distribution_insertion INTO for_dist_inst_rec;
3249 CLOSE for_distribution_insertion;
3250
3251 v_ja_ap_invoices_interface_no := NULL;
3252
3253 SELECT JAI_AP_TDS_THHOLD_TRXS_S1.nextval--JAI_AP_TDS_INVOICE_NUM_S.NEXTVAL changed by rchandan for bug#4487676
3254 INTO v_ja_ap_invoices_interface_no
3255 FROM dual;
3256
3257 v_ap_invoices_interface_no := NULL;
3258
3259 SELECT ap_invoices_interface_s.NEXTVAL
3260 INTO v_ap_invoices_interface_no
3261 FROM dual;
3262
3263 Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoices_interface 4: STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED');
3264 v_stage:=15;
3265
3266 /* Modified by Ramananda for bug# 4407184 ,start */
3267
3268 lv_rtn_invoice_num := 'RTN/'||for_std_inv_rec_r.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no);
3269 lv_standard_return_excess := 'STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED ON '||for_std_inv_rec_r.invoice_num||' OR '||TO_CHAR(p_invoice_id);/**** NEW.prepay_id replaced with p_invoice_id*******/
3270
3271 /* start additions by ssumaith - bug# 4448789 */
3272 jai_cmn_utils_pkg.GET_LE_INFO(
3273 P_API_VERSION => NULL ,
3274 P_INIT_MSG_LIST => NULL ,
3275 P_COMMIT => NULL ,
3276 P_LEDGER_ID => NULL,
3277 P_BSV => NULL,
3278 P_ORG_ID => P_ORG_ID,
3279 X_RETURN_STATUS => lv_return_status ,
3280 X_MSG_COUNT => ln_msg_count,
3281 X_MSG_DATA => ln_msg_data,
3282 X_LEGAL_ENTITY_ID => ln_legal_entity_id ,
3283 X_LEGAL_ENTITY_NAME => lv_legal_entity_name
3284 );
3285 /* ends additions by ssumaith - bug# 4448789*/
3286
3287 INSERT INTO ap_invoices_interface (
3288 invoice_id,
3289 invoice_num,
3290 invoice_type_lookup_code,
3291 invoice_date,
3292 vendor_id,
3293 vendor_site_id,
3294 invoice_amount,
3295 invoice_currency_code,
3296 exchange_rate,
3297 exchange_rate_type,
3298 exchange_date,
3299 terms_id,
3300 description,
3301 source,
3302 -- voucher_num, Harshita for Bug 4870243
3303 payment_method_lookup_code,
3304 pay_group_lookup_code,
3305 org_id,
3306 legal_entity_id,
3307 created_by,
3308 creation_date,
3309 last_updated_by,
3310 last_update_date,
3311 last_update_login,
3312 goods_received_date,
3313 invoice_received_date, --Added by pavan on 06-Jun-01
3314 group_id /*Bug 4716884*/
3315 ) VALUES (
3316 v_ap_invoices_interface_no,
3317 -- 'RTN/'||for_std_inv_rec_r.invoice_num|| '/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.NEXTVAL),
3318 lv_rtn_invoice_num , --'RTN/'||for_std_inv_rec_r.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
3319 'STANDARD',
3320 v_prepay_dist_date, --bug 3112711 kpvs TRUNC(SYSDATE), --for_std_invoice_rec.invoice_date,
3321 for_std_inv_rec_r.vendor_id,
3322 for_std_inv_rec_r.vendor_site_id,
3323 insertion_amount,
3324 for_std_inv_rec_r.invoice_currency_code,
3325 -- for_std_inv_rec_r.exchange_rate, -- commented by cbabu for Bug#2508086
3326 DECODE( upper(for_std_inv_rec_r.exchange_rate_type), 'USER', for_std_inv_rec_r.exchange_rate, NULL),
3327 -- cbabu for Bug#2508086 Bug#3408429
3328 for_std_inv_rec_r.exchange_rate_type,
3329 for_std_inv_rec_r.exchange_date,
3330 for_std_inv_rec_r.terms_id,
3331 lv_standard_return_excess, --'STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED ON '||for_std_inv_rec_r.invoice_num||' OR '||TO_CHAR(p_invoice_id) ,/**** NEW.prepay_id replaced with p_invoice_id*******/
3332 'INDIA TDS', /*--'TDS', --Ramanand for bug#4388958*/
3333 -- 'RTN/'||for_std_inv_rec_r.invoice_num|| '/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.CURRVAL),
3334 --lv_rtn_invoice_num, --'RTN/'||for_std_inv_rec_r.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no), Harshita for Bug 4870243
3335 for_std_inv_rec_r.payment_method_lookup_code,
3336 for_std_inv_rec_r.pay_group_lookup_code,
3337 p_org_id,
3338 ln_legal_entity_id,
3339 p_created_by,
3340 p_creation_date,
3341 p_last_updated_by,
3342 p_last_update_date,
3343 p_last_updated_by,
3344 for_std_inv_rec_r.goods_received_date,
3345 for_std_inv_rec_r.invoice_received_date, --Added by RPK on 13-DEC-01
3346 to_char(p_invoice_id) /*Bug 4716884*/
3347 );
3348
3349 /* Modified by Ramananda for bug# 4407184 , end */
3350
3351 -- following line added by Aparajita on 07/07/2002 for bug # 2439034
3352 v_ap_sup_inv_num := 'RTN/'||for_std_inv_rec_r.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no);
3353
3354 Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoice_lines_interface 4: STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED');
3355 v_stage:=15;
3356
3357 /* Modified by Ramananda for bug# 4407184 , start */
3358 lv_standard_return_excess := 'STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED ON '||TO_CHAR(p_invoice_id)||' OR '||TO_CHAR(p_invoice_distribution_id) ;
3359
3360 INSERT INTO ap_invoice_lines_interface (
3361 invoice_id,
3362 invoice_line_id,
3363 line_number,
3364 line_type_lookup_code,
3365 amount,
3366 accounting_date,
3367 description,
3368 dist_code_combination_id,
3369 created_by,
3370 creation_date,
3371 last_updated_by,
3372 last_update_date,
3373 last_update_login
3374 ) VALUES (
3375 v_ap_invoices_interface_no,
3376 ap_invoice_lines_interface_s.NEXTVAL,
3377 1, --THERE WILL ALWAYS BE ONLY ONE LINE.
3378 'ITEM',
3379 insertion_amount,
3380 v_prepay_dist_date, --bug 3112711 kpvs TRUNC( SYSDATE )
3381 lv_standard_return_excess, --'STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED ON '||TO_CHAR(p_invoice_id)||' OR '||TO_CHAR(p_invoice_distribution_id) ,
3382 -- for_dist_inst_rec.dist_code_combination_id, --Commented by Ajay Sharma on 09-AUG-01
3383 for_dist_insertion_tds_rec.dist_code_combination_id, --Added by Ajay Sharma on 09-AUG-01
3384 p_created_by,
3385 p_creation_date,
3386 p_last_updated_by,
3387 p_last_update_date,
3388 p_last_updated_by
3389 );
3390
3391 /* Modified by Ramananda for bug# 4407184 , end */
3392
3393
3394 jai_ap_tds_generation_pkg.insert_tds_thhold_trxs -------4333449
3395 (
3396 p_invoice_id => p_invoice_id,
3397 p_tds_event => 'OLD TDS INVOICE PREPAY',
3398 p_tax_id => var_tds_tax_id,
3399 p_tax_rate => ln_tax_rate,
3400 p_taxable_amount => ln_taxable_amount,
3401 p_tax_amount => ln_tax_amount,
3402 p_tds_authority_vendor_id => for_pre_invoice_tds_rec.vendor_id,
3403 p_tds_authority_vendor_site_id => for_pre_invoice_tds_rec.vendor_site_id,
3404 p_invoice_tds_authority_num => for_pre_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
3405 p_invoice_tds_authority_type => 'CREDIT',
3406 p_invoice_tds_authority_curr => for_pre_invoice_tds_rec.invoice_currency_code,
3407 p_invoice_tds_authority_amt => (-1)*insertion_amount * NVL(get_exchange_rate_base_inv_rec.exchange_rate,1),
3408 p_vendor_id => for_std_inv_rec_r.vendor_id,
3409 p_vendor_site_id => for_std_inv_rec_r.vendor_site_id,
3410 p_invoice_vendor_num => 'RTN/'||for_std_inv_rec_r.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
3411 p_invoice_vendor_type => 'STANDARD',
3412 p_invoice_vendor_curr => for_std_inv_rec_r.invoice_currency_code,
3413 p_invoice_vendor_amt => insertion_amount,
3414 p_parent_inv_payment_priority => ln_prnt_pay_priority,
3415 p_parent_inv_exchange_rate => ln_prnt_exchange_rate
3416 );
3417
3418 Fnd_File.put_line(Fnd_File.LOG,'BEFORE submitting Payables OPEN INTERFACE Import concurrent request 2');
3419 result := Fnd_Request.set_mode(TRUE);
3420
3421 lv_batch_name := jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id); --Ramananda for Bug#4584221
3422
3423 req_id := Fnd_Request.submit_request
3424 (
3425 'SQLAP',
3426 'APXIIMPT',
3427 'Localization Payables OPEN INTERFACE Import',
3428 '',
3429 FALSE,
3430 /* Bug 4774647. Added by Lakshmi Gopalsami
3431 Passed operating unit also as this parameter has been
3432 added by base .
3433 */
3434 '',
3435 'INDIA TDS', /* --'TDS', --Ramanand for bug#4388958*/
3436 to_char(p_invoice_id), /*Bug 4716884 */
3437 --'TDS'||TO_CHAR(TRUNC(SYSDATE)),
3438 --commented the above and added the below by Ramananda for Bug#4584221
3439 lv_batch_name,
3440 '',
3441 '',
3442 '',
3443 'Y',
3444 'N',
3445 'N',
3446 'N',
3447 1000, -- commit interval parameter added by Aparajita for bug # 2738340 on 15/01/2003
3448 p_created_by,
3449 p_last_updated_by
3450 );
3451
3452 END IF; --CASE 2
3453
3454 /* FOR App_Flag_Rec IN
3455 Fetch_App_Inv_Flag_Cur( for_vendor_id_prepay_rec.vendor_id,for_std_invoice_rec.vendor_site_id ) LOOP
3456
3457
3458 IF App_Flag_Rec.Approved_Invoice_Flag = 'Y' THEN
3459 */
3460
3461 /* This has been commented by Aparajita as this variable v_invoice_num is no more used and
3462 this cursor was using currval for sequence JAI_AP_TDS_INVOICE_NUM_S,bug 2364106
3463
3464 OPEN for_invoice_num(for_std_invoice_rec.invoice_num);
3465 FETCH for_invoice_num INTO v_invoice_num;
3466 CLOSE for_invoice_num;
3467 end comment by Aparajita on 15th May for bug 2364106 */
3468
3469 -- above block commented by Aparajita on 29/07/2002 for bug # 2475416, and the block below added.
3470 v_Approved_Invoice_Flag := 'N';
3471
3472 BEGIN
3473
3474 SELECT NVL( Approved_Invoice_Flag, 'N' )
3475 INTO v_Approved_Invoice_Flag
3476 FROM JAI_CMN_VENDOR_SITES
3477 WHERE Vendor_Id = for_std_inv_rec_r.vendor_id
3478 AND Vendor_Site_Id = for_std_inv_rec_r.vendor_site_id;
3479
3480 EXCEPTION
3481
3482 WHEN NO_DATA_FOUND THEN
3483
3484 BEGIN
3485
3486 SELECT NVL( Approved_Invoice_Flag, 'N' )
3487 INTO v_Approved_Invoice_Flag
3488 FROM JAI_CMN_VENDOR_SITES
3489 WHERE Vendor_Id = for_std_inv_rec_r.vendor_id
3490 AND Vendor_Site_Id = 0;
3491
3492 EXCEPTION
3493 WHEN NO_DATA_FOUND THEN
3494 NULL;
3495 END;
3496
3497 END;
3498 -- end addition by Aparajita on 29/07/2002 for bug # 2475416
3499
3500 IF v_Approved_Invoice_Flag = 'Y' THEN
3501
3502
3503 Fnd_File.put_line(Fnd_File.LOG,'BEFORE submitting Approval OF TDS AND CM concurrent request FOR invoice id ' || p_invoice_id);
3504 v_stage:=16;
3505 result := Fnd_Request.set_mode(TRUE);
3506 req1_id := Fnd_Request.submit_request
3507 (
3508 'JA',
3509 'JAINAPIN',
3510 'Approval OF TDS AND CM - Localization',
3511 SYSDATE,
3512 FALSE,
3513 req_id,
3514 for_std_inv_rec_r.vendor_id,
3515 for_std_inv_rec_r.vendor_site_id,
3516 p_invoice_id,
3517 -- NULL, -- v_invoice_num, Changed to null by Aparajita on 15th may for bug 2364106.
3518 v_ap_tds_cm_num || ';' || v_ap_sup_inv_num, -- Changed by aparajita on 07/07/2002 for bug 2439034, earlier was null
3519 -- 'RTN'
3520 'APPLY'
3521 );
3522
3523 END IF; -- App_Flag_Rec.Approved_Invoice_Flag
3524
3525 -- END LOOP; -- App_Flag_Rec
3526
3527 END IF;
3528
3529 --END IF;
3530
3531 -- exception handler added by Aparajita on 22nd March 2002.
3532 Fnd_File.put_line(Fnd_File.LOG,'SUCCESSFUL END OF PROCEDURE Ja_In_Ap_Prepay_Invoice_P');
3533
3534 EXCEPTION
3535
3536 WHEN OTHERS THEN
3537
3538 error_mesg := SQLERRM;
3539 lv_app_source := 'jai_ap_tds_old_pkg.process_prepayment_apply'; --rchandan for bug#4428980
3540 lv_add_err_msg := 'EXCEPTION captured BY WHEN OTHERS IN the PROCEDURE. stage - ' || TO_CHAR(v_stage); --rchandan for bug#4428980
3541
3542 INSERT INTO JAI_CMN_ERRORS_T (
3543 APPLICATION_SOURCE,
3544 error_message,
3545 additional_error_mesg,
3546 creation_date,
3547 created_by,
3548 -- added, Harshita for Bug 4866533
3549 last_updated_by, last_update_date
3550 ) VALUES (
3551 lv_app_source, --rchandan for bug#4428980
3552 error_mesg,
3553 lv_add_err_msg, --rchandan for bug#4428980
3554 SYSDATE,
3555 fnd_global.user_id, -- USER, -- Harshita for Bug 4866533
3556 -- added, Harshita for Bug 4866533
3557 fnd_global.user_id, sysdate
3558 );
3559 Fnd_File.put_line(Fnd_File.LOG,'EXCEPTION END OF PROCEDURE Ja_In_Ap_Prepay_Invoice_P');
3560 Fnd_File.put_line(Fnd_File.LOG,'Error message : ' || error_mesg);
3561
3562 END process_prepayment_apply;
3563
3564 PROCEDURE approve_invoice
3565 (
3566 errbuf OUT NOCOPY VARCHAR2,
3567 retcode OUT NOCOPY VARCHAR2,
3568 p_parent_request_id IN NUMBER,
3569 p_vendor_id IN NUMBER,
3570 p_vendor_site_id IN NUMBER,
3571 p_invoice_id IN NUMBER,
3572 p_invoice_num IN VARCHAR2 DEFAULT NULL,
3573 p_inv_type IN VARCHAR2 DEFAULT NULL
3574 )
3575 IS
3576 v_request_id NUMBER;
3577 v_set_of_books_id NUMBER;
3578 v_approval_inv_flag VARCHAR2(1);
3579 v_vendor_id NUMBER;
3580 result BOOLEAN;
3581 req_status BOOLEAN := TRUE;
3582 req_id NUMBER;
3583 REQ1_ID NUMBER;
3584 v_phase VARCHAR2(100);
3585 v_status VARCHAR2(100);
3586 v_dev_phase VARCHAR2(100);
3587 v_dev_status VARCHAR2(100);
3588 v_message VARCHAR2(100);
3589 v_tds_inv_id NUMBER;
3590 v_cm_inv_id NUMBER;
3591 v_parent_request_id NUMBER;
3592 v_rtn_inv NUMBER;
3593 v_can_inv NUMBER;
3594 v_tds_cm_inv_id NUMBER; --Added by Ajay Sharma
3595 v_std_inv_num VARCHAR2(50); --Added by Ajay Sharma
3596
3597 CURSOR Fetch_Set_Of_Books_Id_Cur(inv_id NUMBER) IS
3598 SELECT Set_Of_Books_Id
3599 FROM Ap_Invoices_All
3600 WHERE Invoice_Id = inv_id;
3601
3602 /*CURSOR Fetch_App_Inv_Flag_Cur IS
3603 SELECT NVL( Approved_Invoice_Flag, 'N' ) Approved_Invoice_Flag
3604 FROM JAI_CMN_VENDOR_SITES
3605 WHERE Vendor_Id = p_vendor_id
3606 AND Vendor_Site_Id = p_vendor_site_id; */
3607
3608 CURSOR Fetch_Inv_Num_Cur IS
3609 SELECT Tds_Invoice_Num, Dm_Invoice_Num, Tds_Tax_Id
3610 FROM JAI_AP_TDS_INVOICES
3611 WHERE Invoice_Id = p_invoice_id;
3612
3613 CURSOR Fetch_Tds_Vendor_Dtls_Cur( v_tds_tax_id IN NUMBER ) IS
3614 SELECT Vendor_Id, Vendor_Site_Id
3615 FROM JAI_CMN_TAXES_ALL
3616 WHERE Tax_Id = v_tds_tax_id;
3617
3618 CURSOR Fetch_Inv_Id_Cur( v_inv_num IN VARCHAR2, v_vendor_id IN NUMBER, v_vendor_site_id IN NUMBER ) IS
3619 SELECT Invoice_Id
3620 FROM Ap_Invoices_All
3621 WHERE Invoice_Num = v_inv_num
3622 AND Vendor_Id = v_vendor_id
3623 AND Vendor_Site_Id = v_vendor_site_id;
3624
3625 CURSOR for_cancelled_invoices(inv_id NUMBER) IS
3626 SELECT dm_invoice_num
3627 FROM JAI_AP_TDS_INVOICES
3628 WHERE invoice_id = inv_id;
3629
3630 CURSOR cancelled_info(inv_id NUMBER) IS
3631 SELECT cancelled_date
3632 FROM ap_invoices_all
3633 WHERE invoice_id = inv_id;
3634 ----------------------------------------- PREPAY -------------------------------------
3635 CURSOR check_dist_type(inv NUMBER,cp_line_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%type) IS
3636 SELECT invoice_id, invoice_distribution_id, amount, org_id,prepay_distribution_id,line_type_lookup_code,
3637 last_updated_by,last_update_date,created_by,creation_date
3638 FROM ap_invoice_distributions_all
3639 WHERE invoice_id = inv
3640 and distribution_line_number in (select max(distribution_line_number)
3641 from ap_invoice_distributions_all
3642 where invoice_id = inv
3643 and line_type_lookup_code =cp_line_type_lookup_code ) ;
3644 -----------------------------------------------------------------------------------------------
3645 /* added by Ajay Sharma on 15-apr-01 */
3646
3647 CURSOR Fetch_Std_Inv_Num_Cur IS
3648 SELECT invoice_num
3649 FROM ap_invoices_all
3650 WHERE invoice_id = p_invoice_id;
3651
3652 CURSOR Fetch_Like_Inv_Id_Cur( v_inv_num IN VARCHAR2, v_vendor_id IN NUMBER, v_vendor_site_id IN NUMBER ) IS
3653 SELECT Invoice_Id
3654 FROM Ap_Invoices_All inv
3655 WHERE Invoice_Num LIKE v_inv_num
3656 AND Vendor_Id = v_vendor_id
3657 AND Vendor_Site_Id = v_vendor_site_id
3658 --following added by Aparajita to avoid approval of already approved invoice on 07/07/2002.
3659 AND NOT EXISTS (SELECT '1'
3660 FROM ap_invoice_distributions_all
3661 WHERE invoice_id = inv.invoice_id
3662 AND NVL(match_status_flag, 'T') = 'A')
3663 ;
3664
3665 /* end of code by Ajay Sharma on 15-apr-01 */
3666 ---------------------------------------------------------------------------------------------
3667 check_dist_type_r check_dist_type%ROWTYPE;
3668 for_cancelled_invoices_rec for_cancelled_invoices%ROWTYPE;
3669 v_cancelled_date DATE;
3670
3671 /* Start : Added by Aparajita Das for bug # 2439034 */
3672
3673 -- for unapplying prepayment.
3674 v_uap_tds_inv_num ap_invoices_all.invoice_num%TYPE;
3675 v_uap_sup_cm_num ap_invoices_all.invoice_num%TYPE;
3676 v_uap_inv_id NUMBER;
3677 v_uap_vendor_id NUMBER;
3678
3679 -- for applying prepayment.
3680 v_ap_tds_cm_num ap_invoices_all.invoice_num%TYPE;
3681 v_ap_sup_inv_num ap_invoices_all.invoice_num%TYPE;
3682 v_ap_inv_id NUMBER;
3683 v_ap_vendor_id NUMBER;
3684
3685 -- Common cursor for both applying and unapplying prepayment
3686 CURSOR c_get_invoice(p_inv_num VARCHAR2) IS
3687 SELECT invoice_id, vendor_id
3688 FROM ap_invoices_all
3689 WHERE invoice_num = p_inv_num;
3690
3691 /* End : Added by Aparajita Das for bug # 2439034 */
3692
3693 lv_object_name VARCHAR2(61); -- := '<Package_name>.<procedure_name>'; /* Added by Ramananda for bug#4407165 */
3694
3695 BEGIN
3696
3697 /*------------------------------------------------------------------------------------------
3698 FILENAME: jai_ap_tds_old_pkg.approve_invoice_p.sql
3699 CHANGE HISTORY:
3700 S.No Date Author and Details
3701
3702 1 15-Apr-01 Ajay Sharma
3703 Code modified to approve the credit memo on TDS AUTHORITY
3704 and RTN invoice for the vendor,if the pre-approve flag is
3705 checked at the vendor site
3706
3707 2. 07/07/2002 Aparajita Das for bug # 2439034
3708 Added the section (elsif) for APPLY and UNAPPLY.
3709
3710 APPLY caters to the situation where a prepayment is applied to the standard invoice. In this
3711 case the calling program (jai_ap_tds_old_pkg.process_prepayment_apply), that is the concurrent for apply sends the
3712 tds credit note and return invoice for vendor concatenated in the p_invoice_num parameter
3713 separated by a ';'.
3714
3715 UNAPPLY caters to the situation where an applied invoice is unapplied.
3716 In this case the calling program (jai_ap_tds_old_pkg.process_prepayment_unapply, that is the concurrent for
3717 unapply sends the concatenated invoice_numbers in the p_invoice_num parameter.
3718 The invoices numbers to be approved are always two, the tds invoice and the credit memo. These
3719 two invoices are separated by a ';'.
3720
3721 3. 27/08/2002 Aparajita for bug # 2518531
3722 Added where clause in cursor Fetch_Like_Inv_Id_Cur to avoid reapproval of approved invoice.
3723 added code for this concurrent to return with warning when the parent request ap payable
3724 open interface fails.
3725
3726 changed the Fnd_concurrent.get_request_status to Fnd_concurrent.wait_for_request to introduce
3727 wait period between polling for status of parent request.
3728
3729 4. 25/03/2005 Aparajita Forr TDS Clean up Bug #4088186. Version#115.2
3730 Removed the dependency on table JA_IN_AP_INV_PRE_TEMP.
3731
3732
3733 ----------------------------------------------------------------------------------------------------------------------*/
3734
3735 lv_object_name := 'jai_ap_tds_old_pkg.approve_invoice'; /* Added by Ramananda for bug#4407165 */
3736
3737 v_parent_request_id := p_parent_request_id;
3738 OPEN Fetch_Set_Of_Books_Id_Cur(p_invoice_id);
3739 FETCH Fetch_Set_Of_Books_Id_Cur INTO v_set_of_books_id;
3740 CLOSE Fetch_Set_Of_Books_Id_Cur;
3741
3742 -- LOOP
3743 -- req_status := Fnd_concurrent.get_request_status( v_parent_request_id,
3744 -- above line changed to the line below by Aparajita on 27/8/2002 for bug # 2518531.
3745 req_status := Fnd_concurrent.wait_for_request( v_parent_request_id,
3746 60, -- default value - sleep time in secs
3747 0, -- default value - max wait in secs
3748 v_phase,
3749 v_status,
3750 v_dev_phase,
3751 v_dev_status,
3752 v_message );
3753
3754 /* commented by Aparajita on 27/8/2002 for bug # 2518531.
3755 IF v_dev_phase = 'COMPLETE' THEN
3756 EXIT;
3757 END IF;
3758
3759 END LOOP;
3760 */
3761
3762 -- start added by Aparajita on 27/8/2002 for bug # 2518531.
3763 IF v_dev_phase = 'COMPLETE' THEN
3764
3765 IF v_dev_status <> 'NORMAL' THEN
3766 Fnd_File.put_line(Fnd_File.LOG, 'Exiting with warning as parent request not completed with normal status');
3767 Fnd_File.put_line(Fnd_File.LOG, 'Message from parent request :' || v_message);
3768 retcode := 1;
3769 errbuf := 'Exiting with warningr as parent request not completed with normal status';
3770 RETURN;
3771 END IF;
3772
3773 END IF;
3774
3775 -- end added by Aparajita on 27/8/2002 for bug # 2518531.
3776
3777
3778 IF v_dev_phase = 'COMPLETE' /*OR v_dev_phase = 'INACTIVE'*/ THEN
3779
3780 IF v_dev_status = 'NORMAL' THEN
3781
3782 OPEN check_dist_type(p_invoice_id, 'PREPAY');
3783 FETCH check_dist_type INTO check_dist_type_r;
3784
3785 IF check_dist_type%FOUND THEN
3786 result := Fnd_Request.set_mode(TRUE);
3787 req_id := Fnd_Request.submit_request('JA','JAINPREP','To Insert Prepayment Distributions',
3788 '',FALSE,check_dist_type_r.invoice_id,check_dist_type_r.invoice_distribution_id,
3789 ABS(check_dist_type_r.amount),check_dist_type_r.last_updated_by,check_dist_type_r.last_update_date,
3790 check_dist_type_r.created_by,check_dist_type_r.creation_date,check_dist_type_r.org_id,
3791 check_dist_type_r.prepay_distribution_id,'U','ATTRIBUTE1');
3792 -- Parameter ATTRIBUTE1 added by Ajay Sharma
3793 END IF ;
3794
3795 CLOSE check_dist_type;
3796
3797 -- DELETE JA_IN_AP_INV_PRE_TEMP ;
3798
3799 END IF; -- v_dev_status = 'NORMAL'
3800
3801 END IF; -- v_dev_phase = 'COMPLETE'
3802
3803 /* IF p_invoice_num IS NOT NULL AND p_inv_type = 'RTN' THEN --10
3804 OPEN Fetch_Inv_Id_Cur( p_invoice_num, p_vendor_id, p_vendor_site_id );
3805 FETCH Fetch_Inv_Id_Cur INTO v_rtn_inv;
3806 CLOSE Fetch_Inv_Id_Cur;
3807 result := Fnd_request.set_mode(TRUE);
3808 v_request_id := FND_REQUEST.SUBMIT_REQUEST ( 'SQLAP',
3809 'APPRVL',
3810 'Payables Approval Localization -- RTN Invoice',
3811 SYSDATE,
3812 FALSE,
3813 'All',
3814 UID,
3815 '',
3816 '',
3817 v_vendor_id,
3818 '',
3819 TO_CHAR( v_rtn_inv ),
3820 UID,
3821 TO_CHAR( v_set_of_books_id ),
3822 'N' );
3823 ELSE */ --10
3824
3825 IF p_inv_type = 'CAN' THEN --20
3826
3827 FOR i IN for_cancelled_invoices(p_invoice_id) LOOP
3828
3829 OPEN Fetch_Inv_Id_Cur('CAN/'||SUBSTR(i.dm_invoice_num, 1, 47), p_vendor_id, p_vendor_site_id);
3830 FETCH Fetch_Inv_Id_Cur INTO v_can_inv;
3831 CLOSE Fetch_Inv_Id_Cur;
3832
3833 result := Fnd_Request.set_mode(TRUE);
3834 /* Bug 5378544. Added by Lakshmi Gopalsami
3835 * Included org_id and commit size.
3836 */
3837 v_request_id := Fnd_Request.SUBMIT_REQUEST ( 'SQLAP',
3838 'APPRVL',
3839 'TDS Invoice (Cancellation)- Localization ',
3840 SYSDATE,
3841 FALSE,
3842 '', -- org_id
3843 'All',
3844 UID,
3845 '',
3846 '',
3847 v_vendor_id,
3848 '',
3849 TO_CHAR( v_can_inv ),
3850 UID,
3851 TO_CHAR( v_set_of_books_id ),
3852 'N',
3853 '');-- commit size
3854 END LOOP;
3855
3856
3857 -- Start addition by Aparajita for bug # 2439034 on 07/07/2002.
3858 ELSIF p_inv_type = 'UNAPPLY' THEN
3859
3860 -- unapply prepayment --> Return invoice for tax authority and credit note for supplier
3861
3862 v_uap_tds_inv_num := SUBSTR(p_invoice_num, 1, INSTR(p_invoice_num, ';') -1);
3863 v_uap_sup_cm_num := SUBSTR(p_invoice_num, INSTR(p_invoice_num, ';') + 1);
3864
3865 IF v_uap_tds_inv_num IS NOT NULL THEN
3866
3867 v_uap_inv_id := NULL;
3868 v_uap_vendor_id := NULL;
3869
3870 OPEN c_get_invoice(v_uap_tds_inv_num);
3871 FETCH c_get_invoice INTO v_uap_inv_id, v_uap_vendor_id;
3872 CLOSE c_get_invoice;
3873
3874 IF v_uap_inv_id IS NOT NULL THEN
3875 result := Fnd_Request.set_mode(TRUE);
3876 /* Bug 5378544. Added by Lakshmi Gopalsami
3877 * Included org_id and commit size.
3878 */
3879 v_request_id := Fnd_Request.SUBMIT_REQUEST ( 'SQLAP',
3880 'APPRVL',
3881 'Return TDS Invoice(Unapply Prepayment)- Localization ',
3882 SYSDATE,
3883 FALSE,
3884 '', -- org id
3885 'All',
3886 UID,
3887 '',
3888 '',
3889 v_uap_vendor_id,
3890 '',
3891 TO_CHAR( v_uap_inv_id ),
3892 UID,
3893 TO_CHAR( v_set_of_books_id ),
3894 'N',
3895 '' );-- commit size
3896
3897 END IF;
3898
3899 END IF; -- tds inv num not null
3900
3901 IF v_uap_sup_cm_num IS NOT NULL THEN
3902
3903 v_uap_inv_id := NULL;
3904 v_uap_vendor_id := NULL;
3905
3906 OPEN c_get_invoice(v_uap_sup_cm_num);
3907 FETCH c_get_invoice INTO v_uap_inv_id, v_uap_vendor_id;
3908 CLOSE c_get_invoice;
3909
3910 IF v_uap_inv_id IS NOT NULL THEN
3911 result := Fnd_Request.set_mode(TRUE);
3912 /* Bug 5378544. Added by Lakshmi Gopalsami
3913 * Included org_id and commit size.
3914 */
3915 v_request_id := Fnd_Request.SUBMIT_REQUEST (
3916 'SQLAP',
3917 'APPRVL',
3918 'Credit Note Supplier(Unapply Prepayment)- Localization ',
3919 SYSDATE,
3920 FALSE,
3921 '', -- org id
3922 'All',
3923 UID,
3924 '',
3925 '',
3926 v_uap_vendor_id,
3927 '',
3928 TO_CHAR( v_uap_inv_id ),
3929 UID,
3930 TO_CHAR( v_set_of_books_id ),
3931 'N',
3932 '');-- commit size
3933
3934 END IF;
3935
3936 END IF; -- sup cm num not null
3937
3938 -- end of unapply
3939
3940 ELSIF p_inv_type = 'APPLY' THEN
3941
3942 -- apply prepayment --> credit note for tax authority and retrun invoice for supplier
3943
3944 v_ap_tds_cm_num := SUBSTR(p_invoice_num, 1, INSTR(p_invoice_num, ';') -1);
3945 v_ap_sup_inv_num := SUBSTR(p_invoice_num, INSTR(p_invoice_num, ';') + 1);
3946
3947 IF v_ap_tds_cm_num IS NOT NULL THEN
3948
3949 v_ap_inv_id := NULL;
3950 v_ap_vendor_id := NULL;
3951
3952 OPEN c_get_invoice(v_ap_tds_cm_num);
3953 FETCH c_get_invoice INTO v_ap_inv_id, v_ap_vendor_id;
3954 CLOSE c_get_invoice;
3955
3956 IF v_ap_inv_id IS NOT NULL THEN
3957 result := Fnd_Request.set_mode(TRUE);
3958 /* Bug 5378544. Added by Lakshmi Gopalsami
3959 * Included org_id and commit size.
3960 */
3961 v_request_id := Fnd_Request.SUBMIT_REQUEST (
3962 'SQLAP',
3963 'APPRVL',
3964 'TDS Credit Note(Apply Prepayment)- Localization ',
3965 SYSDATE,
3966 FALSE,
3967 '', -- org id
3968 'All',
3969 UID,
3970 '',
3971 '',
3972 v_ap_vendor_id,
3973 '',
3974 TO_CHAR( v_ap_inv_id ),
3975 UID,
3976 TO_CHAR( v_set_of_books_id ),
3977 'N',
3978 ''); -- commit size
3979
3980 END IF;
3981
3982 END IF; -- tds inv num not null
3983
3984 IF v_ap_sup_inv_num IS NOT NULL THEN
3985
3986 v_ap_inv_id := NULL;
3987 v_ap_vendor_id := NULL;
3988
3989 OPEN c_get_invoice(v_ap_sup_inv_num);
3990 FETCH c_get_invoice INTO v_ap_inv_id, v_ap_vendor_id;
3991 CLOSE c_get_invoice;
3992
3993 IF v_ap_inv_id IS NOT NULL THEN
3994 result := Fnd_Request.set_mode(TRUE);
3995 /* Bug 5378544. Added by Lakshmi Gopalsami
3996 * Included org_id and commit size.
3997 */
3998 v_request_id := Fnd_Request.SUBMIT_REQUEST (
3999 'SQLAP',
4000 'APPRVL',
4001 'Return Invoice Supplier(Apply Prepayment)- Localization ',
4002 SYSDATE,
4003 FALSE,
4004 '', -- org id
4005 'All',
4006 UID,
4007 '',
4008 '',
4009 v_ap_vendor_id,
4010 '',
4011 TO_CHAR( v_ap_inv_id ),
4012 UID,
4013 TO_CHAR( v_set_of_books_id ),
4014 'N',
4015 ''); -- commit size
4016
4017 END IF;
4018
4019 END IF; -- sup cm num not null
4020
4021 -- end of apply
4022
4023 -- End addition by Aparajita for bug # 2439034 on 07/07/2002.
4024
4025 ELSE --20
4026
4027 OPEN cancelled_info(p_invoice_id);
4028 FETCH cancelled_info INTO v_cancelled_date;
4029 CLOSE cancelled_info;
4030
4031 IF v_cancelled_date IS NOT NULL THEN
4032 RETURN;
4033 END IF;
4034
4035 FOR Inv_Num_Rec IN Fetch_Inv_Num_Cur
4036 LOOP
4037
4038 FOR Vendor_Rec IN Fetch_Tds_Vendor_Dtls_Cur( Inv_Num_Rec.Tds_Tax_Id )
4039 LOOP
4040
4041 OPEN Fetch_Inv_Id_Cur( Inv_Num_Rec.Tds_Invoice_Num, Vendor_Rec.Vendor_Id, Vendor_Rec.Vendor_Site_Id );
4042 FETCH Fetch_Inv_Id_Cur INTO v_tds_inv_id;
4043 CLOSE Fetch_Inv_Id_Cur;
4044
4045 v_vendor_id := Vendor_Rec.Vendor_Id;
4046 --END LOOP;
4047
4048 OPEN Fetch_Inv_Id_Cur( Inv_Num_Rec.Dm_Invoice_Num, p_vendor_id, p_vendor_site_id );
4049 FETCH Fetch_Inv_Id_Cur INTO v_cm_inv_id;
4050 CLOSE Fetch_Inv_Id_Cur;
4051
4052 --END LOOP;
4053
4054 result := Fnd_Request.set_mode(TRUE);
4055 /* Bug 5378544. Added by Lakshmi Gopalsami
4056 * Included org_id and commit size.
4057 */
4058 v_request_id := Fnd_Request.SUBMIT_REQUEST (
4059 'SQLAP',
4060 'APPRVL',
4061 'TDS Invoice - Localization ',
4062 SYSDATE,
4063 FALSE,
4064 '', -- org_id
4065 'All',
4066 UID,
4067 '',
4068 '',
4069 v_vendor_id,
4070 '',
4071 TO_CHAR( v_tds_inv_id ),
4072 UID,
4073 TO_CHAR( v_set_of_books_id ),
4074 'N',
4075 ''); -- commit size
4076
4077 result := Fnd_Request.set_mode(TRUE);
4078 /* Bug 5378544. Added by Lakshmi Gopalsami
4079 * Included org_id and commit size.
4080 */
4081 v_request_id := Fnd_Request.SUBMIT_REQUEST (
4082 'SQLAP',
4083 'APPRVL',
4084 'Credit Memo - Localization',
4085 SYSDATE,
4086 FALSE,
4087 '', -- org_id
4088 'All',
4089 UID,
4090 '',
4091 '',
4092 p_vendor_id,
4093 '',
4094 TO_CHAR( v_cm_inv_id ),
4095 UID,
4096 TO_CHAR( v_set_of_books_id ),
4097 'N',
4098 ''); -- commit size
4099 ------------------------------------------------------------------------------------------------
4100 /* added by Ajay Sharma on 15-apr-01
4101 to approve RTN invoice for Vendor and CM for TDS authority*/
4102
4103 OPEN Fetch_Std_Inv_Num_Cur;
4104 FETCH Fetch_Std_Inv_Num_Cur INTO v_std_inv_num;
4105 CLOSE Fetch_Std_Inv_Num_Cur;
4106
4107 OPEN Fetch_Like_Inv_Id_Cur( v_std_inv_num||'TDS%CM%', Vendor_Rec.Vendor_Id, Vendor_Rec.Vendor_Site_Id );
4108 FETCH Fetch_Like_Inv_Id_Cur INTO v_tds_cm_inv_id;
4109 CLOSE Fetch_Like_Inv_Id_Cur;
4110
4111 v_vendor_id := Vendor_Rec.Vendor_id;
4112
4113 IF v_tds_cm_inv_id IS NOT NULL THEN
4114 result := Fnd_Request.set_mode(TRUE);
4115 /* Bug 5378544. Added by Lakshmi Gopalsami
4116 * Included org_id and commit size.
4117 */
4118 v_request_id := Fnd_Request.SUBMIT_REQUEST (
4119 'SQLAP',
4120 'APPRVL',
4121 'Credit Memo For Excess TDS - Localization' ,
4122 SYSDATE,
4123 FALSE,
4124 '', -- org_id
4125 'All',
4126 UID,
4127 '',
4128 '',
4129 v_vendor_id,
4130 '',
4131 TO_CHAR( v_tds_cm_inv_id ),
4132 UID,
4133 TO_CHAR( v_set_of_books_id ),
4134 'N',
4135 ''); -- commit size
4136 END IF;
4137
4138 OPEN Fetch_Like_Inv_Id_Cur('RTN/'||v_std_inv_num||'%',p_vendor_id,p_vendor_site_id);
4139 FETCH Fetch_Like_Inv_Id_Cur INTO v_rtn_inv;
4140 CLOSE Fetch_Like_Inv_Id_Cur;
4141
4142 IF v_rtn_inv IS NOT NULL THEN
4143 result := Fnd_Request.set_mode(TRUE);
4144 /* Bug 5378544. Added by Lakshmi Gopalsami
4145 * Included org_id and commit size.
4146 */
4147 v_request_id := Fnd_Request.SUBMIT_REQUEST (
4148 'SQLAP',
4149 'APPRVL',
4150 'RTN Invoice -- Localization ',
4151 SYSDATE,
4152 FALSE,
4153 '', -- org_id
4154 'All',
4155 UID,
4156 '',
4157 '',
4158 p_vendor_id,
4159 '',
4160 TO_CHAR( v_rtn_inv ),
4161 UID,
4162 TO_CHAR( v_set_of_books_id ),
4163 'N',
4164 ''); -- commit size
4165 END IF;
4166
4167 /* End of addition of code 15-apr-01*/
4168 --------------------------------------------------------------------------------------------------
4169 END LOOP;
4170
4171 END LOOP;
4172
4173 END IF; --20
4174 --10
4175 /* Added by Ramananda for bug#4407165 */
4176 EXCEPTION
4177 WHEN OTHERS THEN
4178 errbuf := null;
4179 retcode := null;
4180 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
4181 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
4182 app_exception.raise_exception;
4183
4184 END approve_invoice;
4185
4186 FUNCTION get_invoice_status (l_invoice_id IN NUMBER,
4187 l_invoice_amount IN NUMBER,
4188 l_payment_status_flag IN VARCHAR2,
4189 l_invoice_type_lookup_code IN VARCHAR2,
4190 I_org_id number )
4191 RETURN VARCHAR2
4192 IS
4193 invoice_approval_status VARCHAR2(25);
4194 invoice_approval_flag VARCHAR2(1);
4195 distribution_approval_flag VARCHAR2(1);
4196 encumbrance_flag VARCHAR2(1);
4197 invoice_holds NUMBER;
4198 cancelled_date DATE;
4199 ---------------------------------------------------------------------
4200 -- Declare cursor to establish the invoice-level approval flag
4201 --
4202 -- The first select simply looks at the match status flag for the
4203 -- distributions. The rest is to cover one specific case when some
4204 -- of the distributions are tested (T or A) and some are untested
4205 -- (NULL). The status should be needs reapproval (N).
4206 --
4207
4208 -----------------------------------------------------------------------------------
4209 CURSOR approval_cursor IS
4210 SELECT match_status_flag
4211 FROM ap_invoice_distributions_all
4212 WHERE invoice_id = l_invoice_id
4213 UNION
4214 SELECT 'N'
4215 FROM ap_invoice_distributions_all
4216 WHERE invoice_id = l_invoice_id
4217 AND match_status_flag IS NULL
4218 AND EXISTS
4219 (SELECT 'There are both untested and tested lines'
4220 FROM ap_invoice_distributions_all
4221 WHERE invoice_id = l_invoice_id
4222 AND match_status_flag IN ('T','A'));
4223 BEGIN
4224
4225 ---------------------------------------------------------------------
4226 -- Get the encumbrance flag
4227 --
4228 SELECT NVL(purch_encumbrance_flag,'N')
4229 INTO encumbrance_flag
4230 FROM financials_system_params_all
4231
4232
4233 -----------------------------------------------------------------------------------------------------------------------
4234 WHERE org_id = I_org_id ;
4235 ---------------------------------------------------------------------
4236 -- Get the number of holds for the invoice
4237 --
4238 SELECT count(*)
4239 INTO invoice_holds
4240 FROM ap_holds
4241 WHERE invoice_id = l_invoice_id
4242 AND release_lookup_code is NULL;
4243 ---------------------------------------------------------------------
4244 -- If invoice is cancelled, return 'CANCELLED'.
4245 --
4246 SELECT ai.cancelled_date
4247 INTO cancelled_date
4248 FROM ap_invoices_all ai
4249 WHERE ai.invoice_id = l_invoice_id;
4250 IF (cancelled_date IS NOT NULL) THEN
4251 RETURN('CANCELLED');
4252 END IF;
4253 ---------------------------------------------------------------------
4254 -- Establish the invoice-level approval flag
4255
4256
4257 -----------------------------------------------------------------------------------------------------------------------
4258 --
4259 -- Use the following ordering sequence to determine the invoice-level
4260 -- approval flag:
4261 -- 'N' - Needs Reapproval
4262 -- 'T' - Tested
4263 -- 'A' - Approved
4264 -- '' - Never Approved
4265 --
4266 -- Initialize invoice-level approval flag
4267 --
4268 invoice_approval_flag := '';
4269 OPEN approval_cursor;
4270 LOOP
4271 FETCH approval_cursor INTO distribution_approval_flag;
4272 EXIT WHEN approval_cursor%NOTFOUND;
4273 IF (distribution_approval_flag = 'N') THEN
4274 invoice_approval_flag := 'N';
4275 ELSIF (distribution_approval_flag = 'T' AND
4276 (invoice_approval_flag <> 'N'
4277 or invoice_approval_flag is null)) THEN
4278 invoice_approval_flag := 'T';
4279
4280
4281 -----------------------------------------------------------------------------------------------------------------------
4282 ELSIF (distribution_approval_flag = 'A' AND
4283 (invoice_approval_flag NOT IN ('N','T')
4284 or invoice_approval_flag is null)) THEN
4285 invoice_approval_flag := 'A';
4286 END IF;
4287 END LOOP;
4288 CLOSE approval_cursor;
4289 ---------------------------------------------------------------------
4290 -- Derive the translated approval status from the approval flag
4291 --
4292 IF (encumbrance_flag = 'Y') THEN
4293 IF (invoice_approval_flag = 'A' AND invoice_holds = 0) THEN
4294 invoice_approval_status := 'APPROVED';
4295 ELSIF ((nvl(invoice_approval_flag,'A') = 'A' AND invoice_holds > 0)
4296 OR (invoice_approval_flag IN ('T','N'))) THEN
4297 invoice_approval_status := 'NEEDS REAPPROVAL';
4298 ELSIF (invoice_approval_flag is null) THEN
4299 invoice_approval_status := 'NEVER APPROVED';
4300 END IF;
4301 ELSIF (encumbrance_flag = 'N') THEN
4302 IF (invoice_approval_flag IN ('A','T') AND invoice_holds = 0) THEN
4303
4304
4305 -----------------------------------------------------------------------------------------------------------------------
4306 invoice_approval_status := 'APPROVED';
4307 ELSIF ((nvl(invoice_approval_flag,'A') IN ('A','T') AND
4308 invoice_holds > 0) OR
4309 (invoice_approval_flag = 'N')) THEN
4310 invoice_approval_status := 'NEEDS REAPPROVAL';
4311 ELSIF (invoice_approval_flag is null) THEN
4312 invoice_approval_status := 'NEVER APPROVED';
4313 ELSIF (invoice_approval_flag is null and invoice_holds > 0 ) THEN
4314 invoice_approval_status := 'NEEDS REAPPROVAL';
4315 END IF;
4316 END IF;
4317 ---------------------------------------------------------------------
4318 -- If this a prepayment, find the appropriate prepayment status
4319 --
4320 if (l_invoice_type_lookup_code = 'PREPAYMENT') then
4321 if (invoice_approval_status = 'APPROVED') then
4322 if (l_payment_status_flag IN ('P','N')) then
4323 invoice_approval_status := 'UNPAID';
4324 else
4325 -- This prepayment is paid
4326 if (AP_INVOICES_UTILITY_PKG.get_prepay_amount_remaining(l_invoice_id) = 0) then
4327
4328
4329 -----------------------------------------------------------------------------------------------------------------------
4330 invoice_approval_status := 'FULL';
4331 elsif (AP_INVOICES_UTILITY_PKG.get_prepayment_type(l_invoice_id) = 'PERMANENT') THEN
4332 invoice_approval_status := 'PERMANENT';
4333 else
4334 invoice_approval_status := 'AVAILABLE';
4335 end if;
4336 end if;
4337 elsif (invoice_approval_status = 'NEVER APPROVED') then
4338 -- This prepayment in unapproved
4339 invoice_approval_status := 'UNAPPROVED';
4340 end if;
4341 end if;
4342 RETURN(invoice_approval_status);
4343 END get_invoice_status ;
4344
4345
4346 END jai_ap_tds_old_pkg;
[Home] [Help]