1 PACKAGE BODY JAI_AP_IDA_TRIGGER_PKG AS
2 /* $Header: jai_ap_ida_t.plb 120.10.12010000.4 2008/12/05 07:23:48 bgowrava ship $ */
3 /*
4 REM +======================================================================+
5 REM NAME ARUID_T1
6 REM
7 REM DESCRIPTION Called from trigger JAI_AP_IDA_ARIUD_T1
8 REM
9 REM NOTES Refers to old trigger JAI_AP_IDA_ARIUD_T2
10 REM
11 REM +======================================================================+
12 */
13 PROCEDURE ARUID_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 )
14 IS
15
16 /* Cursor to check whether Tax lines exists for
17 this invoice in localization tables */
18
19 Cursor check_loc_tax(ln_invoice_id number) is
20 select 'Y'
21 from JAI_AP_MATCH_INV_TAXES
22 where invoice_id = ln_invoice_id ;
23
24 /* Check for localization tax existence */
25
26 lv_exists varchar2(1) := 'N' ;
27
28 -- Log file Generation
29 lv_log_file_name VARCHAR2(50) := 'ja_in_ap_aida_after_trg.log';
30 lv_utl_location VARCHAR2(512);
31 lv_myfilehandle UTL_FILE.FILE_TYPE;
32 lv_debug VARCHAR2(1) := 'N'; -- Harshita for Bug 5219176
33
34 /* Variables related to CP */
35
36 lb_result BOOLEAN;
37 ln_request_id Number;
38
39 /* Emd for bug4406963 */
40
41 BEGIN
42 pv_return_code := jai_constants.successful ;
43
44 /*------------------------------------------------------------------------------------------
45 FILENAME: jai_ap_ida_t.plb
46
47 CHANGE HISTORY:
48 S.No Date Author and Details
49
50 1. 22/11/2004 Aparajita, created for bug # 3924692. Version # 115.0
51
52 This is the common after row level trigger for all events, that is
53 insert, update and delete.
54
55 Introduced the call to centralized packaged procedure,
56 jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
57
58 2. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
59 DB Entity as required for CASE COMPLAINCE. Version 116.1
60
61 5. 13-Jun-2005 File Version: 116.3
62 Ramananda for bug#4428980. Removal of SQL LITERALs is done
63
64 6. 03/11/2006 Sanjikum for Bug#5131075, File Version 120.1
65 1) Changes are done for forward porting of bugs - 4722011, 4683207
66
67 Dependency Due to this Bug
68 --------------------------
69 Yes, as Package spec is changed and there are multiple files changed as part of current
70
71 7 13-JUNE-2007 ssawant for bug 6074957
72 Modified cursor c_get_rnd_factor to check whether the current date is between start and end date.
73
74
75 Dependency:
76 ----------
77
78 Sl No. Bug Dependent on
79 Bug/Patch set Details
80 -------------------------------------------------------------------------------------------------
81 1 3924692 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
82 4033992.
83 ja_in_util_pkg_s.sql 115.0
84 ja_in_util_pkg_b.sql 115.0
85 ------------------------------------------------------------------------------------------ */
86 --if
87 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_AP_AIDA_AFTER_TRG',
88 -- p_org_id => pr_new.org_id,
89 -- p_set_of_books_id => pr_new.set_of_books_id )
90 -- =
91 -- FALSE
92 --then
93 /* India Localization funtionality is not required */
94 -- return;
95 --end if;
96
97 -- Bug 7114863. Added by Lakshmi Gopalsami
98 -- Removed the reference to jai_ap_tolerance_pkg.check_tolerance_hold
99
100 /* Bug 4406963. Added by LGOPALSA */
101
102 /* Proceed only when Match_Status_Flag is changed to 'A'.
103 Implies invoice is in validated status */
104
105
106
107 If lv_debug = 'Y' Then
108 Begin
109 Select DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
110 Value,SUBSTR (value,1,INSTR(value,',') -1))
111 INTO lv_utl_location
112 from v$parameter
113 where name = 'utl_file_dir';
114
115 lv_myfilehandle := UTL_FILE.FOPEN(lv_utl_location, lv_log_file_name ,'A');
116 UTL_FILE.PUT_LINE(lv_myfilehandle, '********* Start ja_in_ap_aida_after_trg('||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS') ||') *********');
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 lv_debug := 'N';
121 END;
122 End if; /* lv_debug ='Y' */
123
124 If lv_debug = 'Y' Then -- added, Harshita for Bug 5219176
125 UTL_FILE.PUT_LINE(lv_myfilehandle, ' inside update ');
126 UTL_FILE.PUT_LINE(lv_myfilehandle, ' line type lookup code '|| pr_new.line_type_lookup_code);
127 UTL_FILE.PUT_LINE(lv_myfilehandle, ' reversal flag '|| pr_new.reversal_flag);
128 UTL_FILE.PUT_LINE(lv_myfilehandle, ' old match status flag '|| pr_old.match_status_flag);
129 UTL_FILE.PUT_LINE(lv_myfilehandle, ' match status flag '|| pr_new.match_status_flag);
130 UTL_FILE.PUT_LINE(lv_myfilehandle, ' old price variance '|| pr_old.invoice_price_variance);
131 UTL_FILE.PUT_LINE(lv_myfilehandle, ' new price variance '|| pr_new.invoice_price_variance);
132 UTL_FILE.PUT_LINE(lv_myfilehandle, ' old exc. vari '|| pr_old.exchange_rate_variance);
133 UTL_FILE.PUT_LINE(lv_myfilehandle, ' exc. vari '|| pr_new.exchange_rate_variance);
134 UTL_FILE.PUT_LINE(lv_myfilehandle, ' invoice id '|| pr_new.invoice_id);
135 UTL_FILE.PUT_LINE(lv_myfilehandle, ' invoice dist id '|| pr_new.invoice_distribution_id);
136 end if ;
137
138 If updating Then
139
140 If ( ( pr_new.line_type_lookup_code = 'ITEM') AND
141 ( ( nvl(pr_old.invoice_price_variance,0) <> nvl(pr_new.invoice_price_variance,0) ) OR
142 ( nvl(pr_old.base_invoice_price_variance,0) <> nvl(pr_new.base_invoice_price_variance,0) ) OR
143 ( nvl(pr_old.exchange_rate_variance,0) <> nvl(pr_new.exchange_rate_variance,0) )
144 )
145
146 ) Then
147
148 If lv_debug = 'Y' Then -- added, Harshita for Bug 5219176
149
150 UTL_FILE.PUT_LINE(lv_myfilehandle, ' inside all condition chec k ');
151
152 UTL_FILE.PUT_LINE(lv_myfilehandle, ' inside match status flag ');
153
154 End If ;
155
156 /* Check whether MISC (Tax lines) exists for this invoice
157 Then proceed. Else return.
158 */
159 Open check_loc_tax(pr_new.invoice_id);
160 Fetch check_loc_tax into lv_exists;
161 Close check_loc_tax;
162
163 If nvl(lv_exists , 'N') = 'Y' Then
164
165 /* Proceed only when the variances are calculated and old variance
166 is not equal to the new variances */
167
168 IF lv_debug = 'Y' THEN
169 UTL_FILE.PUT_LINE(lv_myfilehandle, ' exists flag ');
170 UTL_FILE.PUT_LINE(lv_myfilehandle,
171 'invoice_id => '|| pr_new.invoice_id
172 || ', po_distribution_id => ' ||pr_new.po_distribution_id
173 || ', line_type_lookup_code => ' ||pr_new.line_type_lookup_code
174 || ', amount => ' ||pr_new.amount
175 || ', Dist Line number => ' || pr_new.distribution_line_number
176 );
177 UTL_FILE.PUT_LINE(lv_myfilehandle,
178 ', amount => ' ||pr_new.amount
179 || ', base_amount => ' ||pr_new.base_amount
180 || ', Old Price Variance => ' || pr_old.invoice_price_variance
181 || ', New Price Variance => ' || pr_new.invoice_price_variance
182 || ', Base Old Price Variance => ' || pr_old.base_invoice_price_variance
183 || ', Base New Price Variance => ' || pr_new.base_invoice_price_variance
184 || ', Var CCID => '||pr_new.price_var_code_combination_id
185 || ', org_id => ' ||pr_new.org_id
186 );
187 END IF;
188
189 -- Call the CP for calculating the IPV
190
191 lb_result := Fnd_Request.set_mode(TRUE);
192 ln_request_id := Fnd_Request.submit_request
193 ('JA', -- Changed to JA from SQLAP, 4579729
194 'JAIAPIPV',
195 'India - Create Variances for Payables Tax line',
196 '',
197 FALSE,
198 pr_new.invoice_id,
199 pr_new.po_distribution_id,
200 pr_new.invoice_distribution_id,
201 pr_new.amount,
202 pr_new.base_amount,
203 pr_new.rcv_transaction_id,
204 pr_new.invoice_price_variance,
205 pr_new.base_invoice_price_variance,
206 pr_new.price_var_code_combination_id,
207 pr_new.Exchange_rate_variance,
208 pr_new.rate_var_code_combination_id
209 );
210 IF lv_debug = 'Y' THEN
211 UTL_FILE.fclose(lv_myfilehandle);
212 END IF;
213
214 End if; /* lv_exists ='Y' */
215
216 End if; /* For Variances */
217
218 End if; /* updating */
219
220 IF lv_debug = 'Y' THEN
221 UTL_FILE.fclose(lv_myfilehandle);
222 END IF;
223
224 /* Added an exception block by Ramananda for bug#4570303 */
225 EXCEPTION
226 WHEN OTHERS THEN
227 IF lv_debug ='Y' Then
228 UTL_FILE.PUT_LINE(lv_myfilehandle, 'Error in trigger , errm -> '||SQLERRM);
229 UTL_FILE.fclose(lv_myfilehandle);
230 End if;
231 RAISE;
232 END ARUID_T1 ;
233 /*
234 REM +======================================================================+
235 REM NAME BRIUD_T1
236 REM
237 REM DESCRIPTION Called from trigger JAI_AP_IDA_BRIUD_T1
238 REM
239 REM NOTES Refers to old trigger JAI_AP_IDA_BRIUD_T1
240 REM
241 REM +======================================================================+
242 */
243 PROCEDURE BRIUD_T1 ( pr_old t_rec%type , pr_new in out t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
244 lv_final_dff_value_tds varchar2(150);
245 lv_process_flag varchar2(20);
246 lv_process_message varchar2(200);
247 ln_final_tds_tax_id JAI_CMN_TAXES_ALL.tax_id%type;
248
249 cursor c_ap_invoices_all is
250 select vendor_id,
251 vendor_site_id,
252 invoice_currency_code,
253 exchange_rate,
254 set_of_books_id,
255 source,
256 cancelled_date,
257 -- Bug#5131075(4683207). Added by Lakshmi Gopalsami
258 invoice_type_lookup_code,
259 invoice_num /*added for bug 6493858 ref-6318997*/
260 from ap_invoices_all
261 where invoice_id = pr_new.invoice_id;
262
263 --Added by Sanjikum for Bug#5131075(4722011)
264 CURSOR c_check_prepayment_apply IS
265 SELECT '1'
266 FROM jai_ap_tds_prepayments
267 WHERE invoice_distribution_id_prepay = pr_new.invoice_distribution_id;
268
269 CURSOR c_check_prepayment_unapply IS
270 SELECT '1'
271 FROM jai_ap_tds_prepayments
272 WHERE invoice_distribution_id_prepay = pr_new.parent_reversal_id
273 AND unapply_flag = 'Y';
274
275 lv_prepay_flag VARCHAR2(1);
276 --End addition by Sanjikum for Bug#5131075(4722011)
277
278 c_rec_ap_invoices_all c_ap_invoices_all%rowtype;
279 lv_codepath VARCHAR2(1996);
280 lv_is_invoice_validated varchar2(1);
281 lv_new_transaction_si varchar2(1);
282 lv_new_transaction_pp varchar2(1);
283 ln_org_id ap_invoices_all.org_id%type;
284 ln_set_of_books_id ap_invoices_all.set_of_books_id%type;
285 /* bug 5640993 FP of 5553489. Added by JMEENA
286 * Created variable which decides whether the prepayment
287 * created prior to upgrade(TDS-Threshold) has to be processed
288 */
289 lv_process_old_trxn VARCHAR2(1);
290
291 /*Bug 5989740 bduvarag start*/
292 /* Commented for bug# 6459941
293 CURSOR c_get_rnd_factor (p_org_id IN NUMBER ) IS
294 SELECT NVL(tds_rounding_factor,0) , tds_rounding_start_date
295 FROM JAI_AP_TDS_YEARS
296 WHERE legal_entity_id IN (SELECT legal_entity_id
297 FROM hr_operating_units
298 where organization_id = p_org_id
299 )
300 AND trunc (sysdate) between start_date and end_date; --added by ssawant for bug 6074957
301 Bug 5989740 bduvarag end */
302
303 /* Added for bug# 6459941 */
304 CURSOR c_get_rnd_factor (p_org_id IN NUMBER ) IS
305 SELECT
306 nvl(tds_rounding_factor,0) ,
307 tds_rounding_start_date
308 FROM
309 jai_ap_tds_years
310 WHERE
311 legal_entity_id = p_org_id
312 AND trunc (sysdate) between start_date and end_date ;
313
314 /*start changes for bug 6493858 - logic for 5662741 moved from jai_ap_ia_t.plb*/
315 CURSOR c_tds_invoice_id(cp_invoice_id NUMBER)
316 IS
317 SELECT invoice_to_tds_authority_id invoice_id,
318 invoice_to_tds_authority_num invoice_num
319 FROM jai_ap_tds_thhold_trxs
320 WHERE invoice_id = cp_invoice_id;
321
322 r_tds_invoice_id c_tds_invoice_id%ROWTYPE;
323
324 lv_invoice_payment_status ap_invoices_all.payment_status_flag%TYPE;
325
326 FUNCTION get_invoice_payment_status(p_invoice_id IN NUMBER)
327 RETURN VARCHAR2
328 IS
329 PRAGMA AUTONOMOUS_TRANSACTION;
330
331 CURSOR c_payment_status(cp_invoice_id NUMBER)
332 IS
333 SELECT payment_status_flag
334 FROM ap_invoices_all
335 WHERE invoice_id = cp_invoice_id;
336
337 r_payment_status c_payment_status%ROWTYPE;
338 BEGIN
339 OPEN c_payment_status(p_invoice_id);
340 FETCH c_payment_status INTO r_payment_status;
341 CLOSE c_payment_status;
342
343 RETURN r_payment_status.payment_status_flag;
344 END get_invoice_payment_status;
345
346 /*end changes for bug 6493858*/
347
348
349 --Inline procedure added by Sanjikum for Bug#5131075(4722011)
350 PROCEDURE process_prepayment
351 IS
352 BEGIN
353 /* Check if the prepayment transaction should be processed by the code before
354 TDS Clean up or after TDS clean up.
355
356 if SI is created in the new regime and also the Prepay is created in the new regime,
357 then code should invoke the new regime or else
358 old concurrent shd be invoked */
359
360
361 --Check for SI
362 jai_ap_tds_tax_defaultation.check_old_transaction
363 (
364 p_invoice_id => pr_new.invoice_id,
365 p_new_transaction => lv_new_transaction_si
366 );
367
368 --Check for Pprepayment
369 jai_ap_tds_tax_defaultation.check_old_transaction
370 (
371 p_invoice_distribution_id => pr_new.prepay_distribution_id,
372 p_new_transaction => lv_new_transaction_pp
373 );
374
375 if lv_new_transaction_si = 'Y' and lv_new_transaction_pp = 'Y' then
376
377 lv_codepath := null;
378
379 jai_ap_tds_prepayments_pkg.process_prepayment
380 (
381 p_invoice_id => pr_new.invoice_id,
382 p_invoice_distribution_id => pr_new.invoice_distribution_id,
383 p_prepay_distribution_id => pr_new.prepay_distribution_id,
384 p_parent_reversal_id => pr_new.parent_reversal_id,
385 p_prepay_amount => pr_new.amount,
386 p_vendor_id => c_rec_ap_invoices_all.vendor_id,
387 p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
391 p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
388 p_accounting_date => pr_new.accounting_date,
389 p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
390 p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
392 p_org_id => pr_new.org_id,
393 p_creation_date => pr_new.creation_date, /*Bug 5989740 bduvarag*/
394 p_process_flag => lv_process_flag,
395 p_process_message => lv_process_message,
396 p_codepath => lv_codepath
397 );
398
399 if nvl(lv_process_flag, 'N') = 'E' then
400 raise_application_error(-20007,
401 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
402 end if;
403
404 else
405 /* Bug 5640993 FP of 5553489. Added by JMEENA
406 * Invoking the processing of prepayments only during
407 * prepayment application and not during validation
408 */
409 IF lv_process_old_trxn = 'Y' THEN
410
411 --Invoke the old regime functionality
412 jai_ap_tds_prepayments_pkg.process_old_transaction
413 (
414 p_invoice_id => pr_new.invoice_id,
415 p_invoice_distribution_id => pr_new.invoice_distribution_id,
416 p_prepay_distribution_id => pr_new.prepay_distribution_id,
417 p_amount => pr_new.amount,
418 p_last_updated_by => pr_new.last_updated_by,
419 p_last_update_date => pr_new.last_update_date,
420 p_created_by => pr_new.created_by,
421 p_creation_date => pr_new.creation_date,
422 p_org_id => pr_new.org_id,
423 p_process_flag => lv_process_flag,
424 p_process_message => lv_process_message
425 );
426
427 if nvl(lv_process_flag, 'N') = 'E' then
428 raise_application_error(-20008,
429 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
430 end if;
431 END IF ; --End of bug#5640993
432
433 end if; --Transactions in new regime
434 END process_prepayment;
435
436
437 BEGIN
438 pv_return_code := jai_constants.successful ;
439 /*------------------------------------------------------------------------------------------
440 FILENAME: ja_in_ap_aida_before_trg.sql
441
442 CHANGE HISTORY:
443 S.No Date Author and Details
444
445 1. 25/03/2004 Aparajita. Bug # 4088186. TDS Clean up. Version#115.0
446
447 This is the only trigger introduced for all the before event
448 on the table on which this is based.
449
450 2. 11/05/2005 rchandan for bug#4333488. Version 116.1
451 The Invoice Distribution DFF is eliminated and a new global DFF is used to
452 maintain the functionality. From now the TDS tax, WCT tax and ESSI will not
453 be populated in the attribute columns of ap_invoice_distributions_all table
454 instead these will be populated in the global attribute columns. So the code changes are
455 made accordingly.
456
457 3. 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
458 Changed AP Lookup code from 'TDS' to 'INDIA TDS'
459
460 4. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
461 DB Entity as required for CASE COMPLAINCE. Version 116.2
462
463 5 13-Jun-2005 File Version: 116.3
464 Ramananda for bug#4428980. Removal of SQL LITERALs is done
465
466 6. 06-jul-2005 AP lines change.
467 7. 18/04/2007 bduvarag for the Bug#5989740, file version 120.2
468 Forward porting the changes done in 11i bug#5907436
469 8. 21/10/2008 Bug 5640993 FP of 5553489. Added by JMEENA
470 Invoked processing of old prepayment application using variable lv_process_old_trxn. Processing of TDS prepayment application for transactions created
471 prior to upgrade to TDS Threshold will be done only at the time of application and not during validation.
472
473
474
475
476 Dependency:
477 ----------
478
479 Sl No. Bug Dependent on
480 Bug/Patch set Details
481 -------------------------------------------------------------------------------------------------
482 1 3924692 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
483 4033992.
484 ja_in_util_pkg_s.sql 115.0
485 ja_in_util_pkg_b.sql 115.0
486
487 2. 4088186 4088186 Call to Package jai_ap_tds_tax_defaultation.
488 -------------------------------------------------------------------------------------------------
489
490 8. 17/Sep/2007 Bug 5911913. Added by vkantamn version 120.6
491 Added two parameters
492 (1) p_old_input_dff_value_wct
493 (2) p_old_input_dff_value_essi
494 in the call to procedure process_invoice.
498 jai_ap_tds_dflt.pls 120.1
495
496 Dependencies:
497 -------------
499 jai_ap_tds_dflt.plb 120.3
500 jai_ap_ida_t.plb 120.5
501
502
503 9. 18-Oct-07 Bug 6493858, File version 120.8
504 Moved the validation done for invoice cancellation process from jai_ap_ia_t.plb.
505 Through this, changes done for bug 6318997 have been forward ported to R12 code.
506
507 10. 21-Dec-2007 Sanjikum for Bug#6708042, Version 120.10
508 Obsoleted the changes done for verion 120.6
509
510 11. 05-Dec-2008 Bgowrava for Bug#7433241, file Version 120.10.12010000.4
511 moved the end if condition in the code of intercepting the validate event. this enables that only the call to the
512 process_tds_at_inv_validate procedure is dependent on the value of variable lv_is_invoice_validated. Thus enabling
513 the code for prepayment to execute when a prepayment application or unapplication to execute when the prepayment is
514 applied before validation of the std invoice.
515
516 ------------------------------------------------------------------------------------------ */
517
518 /*CHANGE HISTORY:
519 S.No Bug Date Author and Details
520 1 6493858 4-DEC-2007 Added by Nitin Prashar, for cancelation of Base Invoice*/
521
522
523 if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
524 ln_org_id := pr_new.org_id;
525 ln_set_of_books_id := pr_new.set_of_books_id;
526 elsif pv_action = jai_constants.deleting then
527 ln_org_id := pr_old.org_id;
528 ln_set_of_books_id := pr_old.set_of_books_id;
529 end if;
530
531 --if
532 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_AP_AIDA_BEFORE_TRG',
533 -- p_org_id => ln_org_id,
534 -- p_set_of_books_id => ln_set_of_books_id )
535 -- =
536 -- FALSE
537 --then
538 /* India Localization funtionality is not required */
539 -- return;
540 --end if;
541
542
543 /* TDS Tax Defaultation Functionality Bug # 4088186 */
544 open c_ap_invoices_all;
545 fetch c_ap_invoices_all into c_rec_ap_invoices_all;
546 close c_ap_invoices_all;
547
548 /* Bug 5640993 FP of 5553489. Added by JMEENA
549 * Initialized the variable lv_process_old_trxn to 'Y' so that
550 * prepayments will be processed during insert event.
551 */
552 lv_process_old_trxn := 'Y';
553
554 /*Bug 5989740 bduvarag start*/
555 IF JAI_AP_TDS_GENERATION_pkg.gn_tds_rounding_factor IS NULL
556 OR JAI_AP_TDS_GENERATION_pkg.gd_tds_rounding_effective_date IS NULL
557 THEN
558 OPEN c_get_rnd_factor (pr_new.org_id);
559 FETCH c_get_rnd_factor into
560 JAI_AP_TDS_GENERATION_pkg.gn_tds_rounding_factor,
561 JAI_AP_TDS_GENERATION_pkg.gd_tds_rounding_effective_date;
562 CLOSE c_get_rnd_factor ;
563 END IF;
564
565
566 /*Bug 5989740 bduvarag end*/
567
568 if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
569
570 if nvl(pr_new.global_attribute_category, 'JA.IN.APXINWKB.DISTRIBUTIONS' ) = 'JA.IN.APXINWKB.DISTRIBUTIONS' and -- rchandan for bug#4333488
571 pr_new.line_type_lookup_code <> 'PREPAY' and
572 c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
573 c_rec_ap_invoices_all.cancelled_date is null
574 then
575
576 jai_ap_tds_tax_defaultation.process_invoice
577 (
578 p_invoice_id => pr_new.invoice_id,
579 p_invoice_line_number => pr_new.invoice_line_number , /* AP Lines*/
580 p_invoice_distribution_id => pr_new.invoice_distribution_id,
581 p_line_type_lookup_code => pr_new.line_type_lookup_code,
582 p_distribution_line_number => pr_new.distribution_line_number,
583 p_parent_reversal_id => pr_new.parent_reversal_id,
584 p_reversal_flag => pr_new.reversal_flag,
585 p_amount => pr_new.amount,
586 p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
587 p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
588 p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
589 p_po_distribution_id => pr_new.po_distribution_id,
590 p_rcv_transaction_id => pr_new.rcv_transaction_id,
591 p_vendor_id => c_rec_ap_invoices_all.vendor_id,
592 p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
593 p_input_dff_value_tds => pr_new.global_attribute1, -- rchandan for bug#4333488
594 p_input_dff_value_wct => pr_new.global_attribute2, -- rchandan for bug#4333488
595 --p_old_input_dff_value_wct => pr_old.global_attribute2,-- Bug 5911913 Added by vkantamn --commented by Sanjikum for Bug#6708042
596 p_input_dff_value_essi => pr_new.global_attribute3, -- rchandan for bug#4333488
597 --p_old_input_dff_value_essi => pr_old.global_attribute3, -- Bug 5911913 Added by vkantamn --commented by Sanjikum for Bug#6708042
598 p_org_id => pr_new.org_id,
599 p_accounting_date => pr_new.accounting_date,
600 p_call_from => 'ja_in_ap_aida_after_trg',
601 p_final_tds_tax_id => ln_final_tds_tax_id,
605 );
602 p_process_flag => lv_process_flag,
603 p_process_message => lv_process_message,
604 p_codepath => lv_codepath
606
607 if nvl(lv_process_flag, 'N') = 'E' then
608 /* raise_application_error(-20004,
609 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_codepath); */ pv_return_code := jai_constants.expected_error ; pv_return_message :=
610 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_codepath ; return ;
611 end if;
612
613
614 if pr_new.global_attribute1 is null and ln_final_tds_tax_id is not null then /*rchandan for bug#4333488*/
615 pr_new.global_attribute1 := to_char(ln_final_tds_tax_id);/*rchandan for bug#4333488*/
616 end if;
617
618 if pr_new.global_attribute1 is not null or pr_new.global_attribute2 is not null or pr_new.global_attribute3 is not null then/*rchandan for bug#4333488*/
619 pr_new.global_attribute_category := 'JA.IN.APXINWKB.DISTRIBUTIONS'; /*rchandan for bug#4333488*/
620 end if;
621
622 end if; /* Additional conditions */
623
624 end if; /*inserting or updating */
625
626 /* TDS Tax Defaultation Functionality Bug # 4088186 */
627
628 /* Intercepting Validate event */
629 if pv_action = jai_constants.updating then
630
631 if nvl(pr_old.match_status_flag, 'Q') <> nvl(pr_new.match_status_flag, 'Q') and
632 pr_new.match_status_flag = 'A' and
633 c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
634 c_rec_ap_invoices_all.cancelled_date is null
635 then
636
637 /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
638 Don't proceed for TDS invoice creation if the invoice type
639 is either 'CREDIT' or 'DEBIT'
640 */
641
642 If c_rec_ap_invoices_all.invoice_type_lookup_code
643 IN ('CREDIT', 'DEBIT')
644 Then
645 return;
646 End if;
647
648 lv_codepath := null;
649
650 jai_ap_tds_generation_pkg.status_update_chk_validate
651 (
652 p_invoice_id => pr_new.invoice_id,
653 p_invoice_line_number => pr_new.invoice_line_number, /* AP Lines*/
654 p_invoice_distribution_id => pr_new.invoice_distribution_id,
655 p_match_status_flag => pr_new.match_status_flag,
656 p_is_invoice_validated => lv_is_invoice_validated,
657 p_process_flag => lv_process_flag,
658 p_process_message => lv_process_message,
659 p_codepath => lv_codepath
660 );
661
662 if nvl(lv_process_flag, 'N') = 'E' then
663 /* raise_application_error(-20005,
664 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message :=
665 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ; return ;
666 end if;
667
668 if lv_is_invoice_validated = 'Y' then
669
670 lv_codepath := null;
671 jai_ap_tds_generation_pkg.process_tds_at_inv_validate
672 (
673 p_invoice_id => pr_new.invoice_id,
674 p_vendor_id => c_rec_ap_invoices_all.vendor_id,
675 p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
676 p_accounting_date => pr_new.accounting_date,
677 p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
678 p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
679 p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
680 p_org_id => pr_new.org_id,
681 p_call_from => 'ja_in_ap_aida_before_trg',
682 p_creation_date => pr_new.creation_date,/*Bug 5989740 bduvarag*/
683 p_process_flag => lv_process_flag,
684 p_process_message => lv_process_message,
685 p_codepath => lv_codepath
686 );
687
688 if nvl(lv_process_flag, 'N') = 'E' then
689 /* raise_application_error(-20006,
690 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message :=
691 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ; return ;
692 end if;
693
694 end if; --moved this from below for Bug#7433241
695
696 --Start added by Sanjikum for Bug#5131075(4722011)
697 --To handle the condition, if there are PP applications/Unapplications, before the SI is validated
698 IF pr_new.line_type_lookup_code = 'PREPAY' THEN
699
700
701 lv_prepay_flag := NULL;
702
703 --Apply Scenario
704 IF NVL(pr_new.amount,0) < 0 THEN
705
706 OPEN c_check_prepayment_apply;
707 FETCH c_check_prepayment_apply INTO lv_prepay_flag;
708 CLOSE c_check_prepayment_apply;
709
713 OPEN c_check_prepayment_unapply;
710 --Unapply Scenario
711 ELSIF NVL(pr_new.amount,0) > 0 THEN
712
714 FETCH c_check_prepayment_unapply INTO lv_prepay_flag;
715 CLOSE c_check_prepayment_unapply;
716
717 END IF;
718
719 --should be run, only if prepayment application/unapplication is not already processed
720 IF lv_prepay_flag IS NULL THEN
721 /* Bug 5640993 FP of 5553489. Added by JMEENA
722 * Set the variable lv_process_old_trxn so that old prepayments will not be processed during validation. */
723 lv_process_old_trxn := 'N';
724 process_prepayment;
725 END IF;
726
727 END IF;
728 --End added by Sanjikum for Bug#5131075(4722011)
729
730 -- end if; --commented by bgowrava for Bug#7433241, moved this end if to a different place at the top.
731
732
733 end if; /* pr_old.match_status_flag <> pr_new.match_status_flag */
734 end if; /* updating */
735 /* Intercepting Validate event */
736
737
738 /* Prepayment functionality */
739 if pv_action = jai_constants.inserting then
740
741 if pr_new.line_type_lookup_code = 'PREPAY' and
742 c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
743 c_rec_ap_invoices_all.cancelled_date is null
744 then
745
746 /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
747 Don't proceed for TDS invoice creation if the invoice type
748 is either 'CREDIT' or 'DEBIT'
749 */
750
751 If c_rec_ap_invoices_all.invoice_type_lookup_code
752 IN ('CREDIT', 'DEBIT')
753 Then
754 return;
755 End if;
756
757
758 --Start Added by Sanjikum for Bug#5131075(4722011)
759 --If SI is not validated at this stage, then return
760
761 lv_codepath := null;
762
763 jai_ap_tds_generation_pkg.status_update_chk_validate
764 (
765 p_invoice_id => pr_new.invoice_id,
766 /* p_invoice_line_id => null, Future use AP Lines
767 /*p_invoice_distribution_id => null,*/
768 p_match_status_flag => pr_new.match_status_flag, --Changed by Sanjikum for Bug#5131075(4722011)
769 p_is_invoice_validated => lv_is_invoice_validated,
770 p_process_flag => lv_process_flag,
771 p_process_message => lv_process_message,
772 p_codepath => lv_codepath
773 );
774
775 if nvl(lv_process_flag, 'N') = 'E' then
776 raise_application_error(-20005,
777 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
778 end if;
779
780 IF lv_is_invoice_validated = 'N' THEN
781 RETURN;
782 END IF;
783
784 process_prepayment;
785
786 --Removed the existing code for Handling PP and moved it into inline procedure process_prepayment
787 --End Added by Sanjikum for Bug#5131075(4722011)
788
789
790 /*--Check if the prepayment transaction should be processed by the code before
791 --TDS Clean up or after TDS clean up.
792
793 --if SI is created in the new regime and also the Prepay is created in the new regime,
794 --then code should invoke the new regime or else
795 --old concurrent shd be invoked
796
797
798 --Check for SI
799 jai_ap_tds_tax_defaultation.check_old_transaction
800 (
801 p_invoice_id => pr_new.invoice_id,
802 p_new_transaction => lv_new_transaction_si
803 );
804
805 --Check for Prepayment
806 jai_ap_tds_tax_defaultation.check_old_transaction
807 (
808 p_invoice_distribution_id => pr_new.prepay_distribution_id,
809 p_new_transaction => lv_new_transaction_pp
810 );
811
812 if lv_new_transaction_si = 'Y' and lv_new_transaction_pp = 'Y' then
813
814 lv_codepath := null;
815
816 jai_ap_tds_prepayments_pkg.process_prepayment
817 (
818 p_invoice_id => pr_new.invoice_id,
819 p_invoice_distribution_id => pr_new.invoice_distribution_id,
820 p_prepay_distribution_id => pr_new.prepay_distribution_id,
821 p_parent_reversal_id => pr_new.parent_reversal_id,
822 p_prepay_amount => pr_new.amount,
823 p_vendor_id => c_rec_ap_invoices_all.vendor_id,
824 p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
825 p_accounting_date => pr_new.accounting_date,
826 p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
827 p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
828 p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
829 p_org_id => pr_new.org_id,
830 p_process_flag => lv_process_flag,
831 p_process_message => lv_process_message,
832 p_codepath => lv_codepath
833 );
834
835 if nvl(lv_process_flag, 'N') = 'E' then
836 -- raise_application_error(-20007,
837 --'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
838 pv_return_code := jai_constants.expected_error ;
839 pv_return_message := 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ;
840 return ;
841 end if;
842
843 else
844 --
845 --|| Invoke the old regime functionality
846 --
847 jai_ap_tds_prepayments_pkg.process_old_transaction
848 (
849 p_invoice_id => pr_new.invoice_id,
850 p_invoice_distribution_id => pr_new.invoice_distribution_id,
851 p_prepay_distribution_id => pr_new.prepay_distribution_id,
852 p_amount => pr_new.amount,
853 p_last_updated_by => pr_new.last_updated_by,
854 p_last_update_date => pr_new.last_update_date,
855 p_created_by => pr_new.created_by,
856 p_creation_date => pr_new.creation_date,
857 p_org_id => pr_new.org_id,
858 p_process_flag => lv_process_flag,
859 p_process_message => lv_process_message
860 );
861
862 if nvl(lv_process_flag, 'N') = 'E' then
863 --raise_application_error(-20008,
864 --'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
865 pv_return_code := jai_constants.expected_error ;
866 pv_return_message := 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ;
867 return ;
868 end if;
869
870
871 end if; --Transactions in new regime*/
872
873
874 end if; /* Prepayment functionality */
875
876 /*Code Started start changes for bug 6493858 - cancellation functionality*/
877 IF pr_new.cancellation_flag = 'Y' AND c_rec_ap_invoices_all.source <> 'INDIA TDS'
878 THEN
879 OPEN c_tds_invoice_id(pr_new.invoice_id);
880 FETCH c_tds_invoice_id INTO r_tds_invoice_id;
881 CLOSE c_tds_invoice_id;
882
883 IF r_tds_invoice_id.invoice_id IS NOT NULL THEN
884 lv_invoice_payment_status := get_invoice_payment_status(r_tds_invoice_id.invoice_id);
885
886 IF NVL(lv_invoice_payment_status,'N') <> 'N' THEN
887 raise_application_error(-20011,
888 'Invoice to TDS Authority - '||r_tds_invoice_id.invoice_num||' is already paid. Current invoice can''t be cancelled' );
889 END IF;
890 END IF;
891 END IF;
892 /*end changes for bug 6493858 -Code Ended */
893
894 end if; /* inserting */
895
896 if pv_action = jai_constants.deleting then
897
898 jai_ap_tds_tax_defaultation.process_delete
899 (
900 p_invoice_id => pr_old.invoice_id,
901 p_invoice_line_number => pr_new.invoice_line_number, /* AP Lines*/
902 p_invoice_distribution_id => pr_old.invoice_distribution_id,
903 p_process_flag => lv_process_flag,
904 P_process_message => lv_process_message
905 );
906
907 if nvl(lv_process_flag, 'N') = 'E' then
908 /* raise_application_error(-20009,
909 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message :=
910 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ; return ;
911 end if;
912
913 end if; /* Deleting */
914
915
916 exception
917 when others then
918 --raise_application_error(-20010, 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || sqlerrm);
919 Pv_return_code := jai_constants.unexpected_error;
920 Pv_return_message := 'Encountered an error in JAI_AP_IDA_TRIGGER_PKG.BRIUD_T1 ' ||
921 'Error on ap_invoice_distributions_all : ' || substr(sqlerrm,1,1900);
922
923 END BRIUD_T1 ;
924
925 END JAI_AP_IDA_TRIGGER_PKG ;
[Home] [Help]