1 PACKAGE BODY JAI_AP_IDA_TRIGGER_PKG AS
2 /* $Header: jai_ap_ida_t.plb 120.48.12020000.9 2013/03/08 11:13:31 vkaranam 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 --Add by Wenqiong for POT, reg bug#12397015 on 26-Apr-2011, begin
41 ---------------------------------------------------------------------------
42 CURSOR get_invoice_type_cur IS
43 SELECT invoice_type_lookup_code
44 FROM ap_invoices_all
45 WHERE invoice_id = pr_new.invoice_id;
46
47 CURSOR get_tax_type_cur IS
48 SELECT jcta.tax_type
49 FROM jai_cmn_document_taxes jcdt
50 , jai_cmn_taxes_all jcta
51 , jai_ap_invoice_lines jail
52 , jai_rgm_registrations jrr
53 , jai_rgm_definitions jrd
54 WHERE jcdt.source_doc_id = pr_new.invoice_id
55 AND jail.invoice_id = pr_new.invoice_id
56 AND jail.invoice_line_number = jcdt.source_doc_line_id
57 AND jail.invoice_line_number = pr_new.invoice_line_number
58 AND jcdt.modvat_flag = 'Y' --Xiao for POT bug#12598010.
59 AND jcdt.tax_id = jcta.tax_id
60 AND jcta.tax_type = jrr.attribute_code
61 AND jrr.regime_id = jrd.regime_id
62 AND jrr.registration_type = jai_constants.regn_type_tax_types
66 SELECT jcta.tax_type
63 AND jrd.regime_code = jai_constants.service_regime;
64
65 CURSOR get_matched_tax_cur IS
67 FROM jai_ap_match_inv_taxes jamt
68 , jai_cmn_taxes_all jcta
69 , jai_rgm_registrations jrr
70 , jai_rgm_definitions jrd
71 WHERE jamt.invoice_id = pr_new.invoice_id
72 AND jamt.invoice_line_number = pr_new.invoice_line_number
73 AND jamt.tax_id=jcta.tax_id
74 and jamt.invoice_distribution_id=nvl(pr_new.parent_reversal_id,pr_new.invoice_distribution_id) /* Added for bug 16393213 */
75 AND jamt.recoverable_flag = 'Y'
76 AND jcta.tax_type = jrr.attribute_code
77 AND jrr.regime_id = jrd.regime_id
78 AND jrr.registration_type = jai_constants.regn_type_tax_types
79 AND jrd.regime_code = jai_constants.service_regime;
80
81 /* Modified the cursor get_match_item_cur for bug 14507573 */
82 CURSOR get_match_item_cur IS
83
84 SELECT line.match_type,line.line_number
85 FROM ap_invoice_lines_all line, jai_ap_match_inv_taxes jamt
86 WHERE line.invoice_id = pr_new.invoice_id
87 and jamt.invoice_id=line.invoice_id
88 and jamt.invoice_distribution_id=nvl(pr_new.parent_reversal_id,pr_new.invoice_distribution_id) /*Modified for bug 16393213 */
89 and jamt.po_distribution_id=pr_new.po_distribution_id
90 and line.line_number=jamt.parent_invoice_line_number
91 -- AND line.po_distribution_id = line.po_distribution_id /*Avanija */
92 AND line.line_type_lookup_code ='ITEM'
93 AND line.match_type IS NOT NULL;
94
95 CURSOR get_curr_dtls_cur IS
96 SELECT exchange_date
97 , exchange_rate
98 , exchange_rate_type
99 , invoice_date
100 -- remove lg_date by zhiwei.xin on 7-FEB-2012 for bug 13540555
101 --, gl_date
102 --Add by qiong for bug12934221 2011.09.07 begin
103 -----------------------------------------------
104 , invoice_currency_code
105 -----------------------------------------------
106 --Add by qiong for bug12934221 2011.09.07 end
107 FROM ap_invoices_all
108 WHERE invoice_id = pr_new.invoice_id ;
109
110 CURSOR get_regime_id_cur IS
111 SELECT regime_id
112 FROM jai_rgm_definitions
113 WHERE regime_code = jai_constants.service_regime;
114
115 CURSOR get_org_cur IS
116 SELECT organization_id,location_id
117 FROM jai_ap_invoice_lines
118 WHERE invoice_id = pr_new.invoice_id
119 AND invoice_line_number = (SELECT parent_invoice_line_number
120 FROM jai_ap_invoice_lines jail
121 WHERE jail.invoice_line_number = pr_new.invoice_line_number
122 AND jail.invoice_id = pr_new.invoice_id)
123 AND parent_invoice_line_number IS NULL;
124
125 CURSOR get_po_matched_org_cur(pn_line_number NUMBER) IS
126 SELECT po.ship_to_organization_id, po.ship_to_location_id
127 FROM po_line_locations_all po,ap_invoice_lines_all ap
128 WHERE po.line_location_id = ap.po_line_location_id
129 AND ap.invoice_id = pr_new.invoice_id
130 AND ap.line_number = pn_line_number;
131
132 CURSOR get_rcpt_matched_org_cur(pn_line_number NUMBER) IS
133 SELECT rcv.organization_id, rcv.location_id
134 FROM jai_rcv_transactions rcv, ap_invoice_lines_all ap
135 WHERE ap.rcv_transaction_id = rcv.transaction_id
136 AND ap.invoice_id = pr_new.invoice_id
137 AND ap.line_number = pn_line_number;
138
139 /*Bug 12805386 - Fetch Effective Date of Point of Taxation only if Organization Type is either 'INDIVIDUALS' or 'PARTNERSHIP FIRM'
140 or 'PROPRIETARY FIRM' and if the Service Type is one mentioned in Rule 7 of Point of Taxation Rules 2011 i.e
141 105-p, 105-q, 105-s, 105-t, 105-u, 105-za, 105-zzzzm*/
142 CURSOR c_get_st_accrual_date(p_regime_id NUMBER, p_organization_id NUMBER, p_location_id NUMBER) IS
143 select to_date(attribute_value, 'DD/MM/YYYY')
144 from JAI_RGM_ORG_REGNS_V
145 where regime_id = p_regime_id
146 and organization_id = p_organization_id
147 and location_id = p_location_id
148 AND attribute_code = 'EFF_DATE_ST_PT'
149 AND attribute_type_code = 'OTHERS'
150 AND registration_type = 'OTHERS'
151 AND (NOT EXISTS
152 (select '1'
153 from JAI_RGM_ORG_REGNS_V
154 where regime_id = p_regime_id
155 and attribute_code IN 'INV_ORG_CLASSIFICATION'
156 and attribute_value <> 'ORGANIZATION'
157 and organization_id = p_organization_id
158 and location_id = p_location_id)
159 OR
160 NOT EXISTS
161 (select '1'
162 from JAI_RGM_ORG_REGNS_V
163 where regime_id = p_regime_id
164 and attribute_code IN 'SERVICE TYPE'
165 and attribute_value <> 'OTHER'
166 and organization_id = p_organization_id
167 and location_id = p_location_id)
168 );
169
170 CURSOR get_cancell_date_cur IS
171 SELECT cancelled_date
172 FROM ap_invoices_all
173 WHERE invoice_id = pr_new.invoice_id;
174 --start additions for bug#16241506
175 cursor get_acct_exists(CP_INVOICE_DISTIRBUTION_ID in number)
176 is
177 SELECT 'Y' FROM jai_cmn_journal_entries
178 WHERE regime_code='SERVICE'
179 AND source='AP'
180 AND source_table_name='AP_INVOICE_DISTRIBUTIONS_ALL'
181 AND SOURCE_TRX_ID=CP_INVOICE_DISTIRBUTION_ID;
182
183 lv_accting_exists VARCHAR2(1);
184 --end additions for bug#16241506
185 -- comment out by zhiwei.xin for bug 13540555 on 6-FEB-2012 begin
186 -- remove same period check cursor into function check_same_period_func
187 /*CURSOR check_same_period_cur IS
188
189 SELECT 'Y'
190 FROM gl_period_statuses gps
194 AND SYSDATE BETWEEN gps.start_date AND gps.end_date;*/
191 WHERE pr_new.period_name = gps.period_name
192 AND gps.set_of_books_id = pr_new.set_of_books_id
193 AND gps.application_id = 200
195 -- comment out by zhiwei.xin for bug 13540555 on 6-FEB-2012 end
196
197 lv_same_period_flag VARCHAR2(1);
198
199 lv_process_message VARCHAR2(200);
200 ln_regime_id NUMBER;
201 lv_regime_code VARCHAR2(30) := jai_constants.service_regime;
202 lv_tax_type VARCHAR2(30);
203 ln_organization_id NUMBER;
204 ln_location_id NUMBER;
205 ln_charge_account_id NUMBER;
206 ld_transaction_date DATE;
207 ld_gl_date DATE;
208 ld_accrual_date DATE;
209 lv_called_from VARCHAR2(80) ;
210 lv_currency_code VARCHAR2(30) := 'INR';
211 ld_curr_conv_date DATE;
212 lv_curr_conv_type VARCHAR2(30);
213 ln_curr_conv_rate NUMBER;
214 ln_entered_amt NUMBER;
215 ln_accounted_amt NUMBER;
216 lv_invoice_type VARCHAR2(30);
217 ln_dist_line_amount NUMBER := pr_new.amount;
218 ln_invoice_id NUMBER := pr_new.invoice_id;
219 ln_invoice_dist_id NUMBER := pr_new.invoice_distribution_id;
220 lv_match_type VARCHAR2(50);
221 ln_item_line_num NUMBER;
222 lv_event VARCHAR2(10);
223 lv_account_name VARCHAR2(30);
224 lv_source_trx_type VARCHAR2(30);
225 ld_canceled_date DATE;
226 ln_round_accounted_amt NUMBER;
227 ln_round_ent_amt NUMBER;
228
229 ---------------------------------------------------------------------------
230 --Add by Wenqiong for POT, reg bug#12397015 on 26-Apr-2011, end
231
232 lv_interface_flag jai_ap_invoice_lines.interface_flag%type; --Added by zhiwei for POT change Bug#13023443 on 20110930
233 lv_interface_event jai_ap_invoice_lines.interface_event%type; --Added by zhiwei for POT change Bug#13023443 on 20110930
234
235 --The cursor get_jai_interface_info is added by Eric Ma for bug 13405997
236 CURSOR get_jai_interface_info
237 IS
238 SELECT interface_flag,interface_event
239 FROM jai_ap_invoice_lines jail_parent,
240 (SELECT invoice_id, parent_invoice_line_number
241 FROM jai_ap_invoice_lines
242 WHERE invoice_line_number = pr_new.invoice_line_number
243 AND invoice_id = pr_new.invoice_id) jail_child
244 WHERE jail_parent.invoice_id = jail_child.invoice_id
245 AND jail_parent.invoice_line_number = jail_child.parent_invoice_line_number;
246
247 -- added by zhiwei.xin for bug 13540555 on 6-FEB-2012 begin
248 FUNCTION check_same_period_func
249 RETURN VARCHAR2
250 IS
251 PRAGMA AUTONOMOUS_TRANSACTION;
252
253 CURSOR check_same_period_cur IS
254 SELECT 'Y'
255 FROM ap_invoice_distributions_all
256 WHERE invoice_id = pr_new.invoice_id
257 AND invoice_distribution_id = pr_new.parent_reversal_id
258 AND period_name = pr_new.period_name;
259
260 lv_same_period varchar2(1);
261
262 BEGIN
263
264 open check_same_period_cur;
265 fetch check_same_period_cur into lv_same_period;
266 close check_same_period_cur;
267
268 return lv_same_period;
269
270 END check_same_period_func;
271 -- added by zhiwei.xin for bug 13540555 on 6-FEB-2012 end.
272
273 BEGIN
274 pv_return_code := jai_constants.successful ;
275
276 /*------------------------------------------------------------------------------------------
277 FILENAME: jai_ap_ida_t.plb
278
279 CHANGE HISTORY:
280 S.No Date Author and Details
281
282 1. 22/11/2004 Aparajita, created for bug # 3924692. Version # 115.0
283
284 This is the common after row level trigger for all events, that is
285 insert, update and delete.
286
287 Introduced the call to centralized packaged procedure,
288 jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
289
290 2. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
291 DB Entity as required for CASE COMPLAINCE. Version 116.1
292
293 5. 13-Jun-2005 File Version: 116.3
294 Ramananda for bug#4428980. Removal of SQL LITERALs is done
295
296 6. 03/11/2006 Sanjikum for Bug#5131075, File Version 120.1
297 1) Changes are done for forward porting of bugs - 4722011, 4683207
298
299 Dependency Due to this Bug
300 --------------------------
301 Yes, as Package spec is changed and there are multiple files changed as part of current
302
303 7 13-JUNE-2007 ssawant for bug 6074957
304 Modified cursor c_get_rnd_factor to check whether the current date is between start and end date.
305
306 8. 10/Jul/2009 Bgowrava for Bug 5911913 . File Version 120.0.12000000.12
307 Added two parameters
308 (1) p_old_input_dff_value_wct
309 (2) p_old_input_dff_value_essi
310 in procedure processs_invoice.
311
312 9. 28/Jan/2010 Modified by Jia for FP Bug#8656402
313 Issue: TDS amount is not rounded as per setup.
314 This was a forward port issue of the R11i Bug#8597476.
315 Fix: Modified cursor c_get_rnd_factor in procedure BRIUD_T1 to include a filter
316 on invoice (accounting) date. Also, the rounding setup will be fetched for each invoice.
317 10. 25/04/2011 Wenqiong, created for POT bug#12397015.
321 Fixed: Add source trx type, replace invoice date with GL date of distribution.
318 Inserting accounting to GL interface, when posting the accouting.
319
320 11. 24-May-2011 Xiao for POT change, reg bug#12533434.
322
323 12. 29-May-2011 Xiao for POT change, reg bug#12533434.
324 Fixed: Add period check on cancellation event.
325
326 13. 29-May-2011 Xiao for POT change, reg bug#12598010.
327 Fixed: Accounting should be generated only for Recoverable taxes.
328 Add condition in cursor get_tax_type_cur.
329
330 14. 06-Jul-2011 Xiao for POT change, reg bug#12722515.
331 Fixed: Fix the Accounting issues, so that A/c that are generated by final post,
332 and cancellation for AP invoice, DM/CM can be correct.
333
334 15. 10-AUG-2011 Bug 12793930
335 Description: Accounting Entries are passed with Source Transaction Type
336 as Invoice Accounting even during Cancelation which is resulting in
337 double accounting
338 Fix: Added checks on Canceled date, parent reversal ID and reversal flag
339 to ensure Accounting is not passed with Transaction Type as Invoice Accounting
340 in those cases
341 16. 07-SEP-2011 Qiong fix Bug 12934221
342 POT:PH III - journal import is running into error
343 Fix: Change currency code(lv_currency_code) from hardcode to getting value from table.
344
345 17. 29-jan-2012 vkaranam for bug 13618731
346 Issue: ERV is not calculated for JAI taxes for the receipt matched invoice.
347 This issue will particularly occurs if the PO doesnot have the taxes
348 attached.
349
350 Technical details:
351 jai_ap_match_inv_taxes.shipment_line_id is used to get the receipt tax
352 amount.
353 But jai_ap_match_inv_taxes.shipment_line_id will be always null.
354 Hence the receipt tax amount retrieved is null ,due to which ERV tax amount
355 is null and the accounting didnt happen for the ERV line
356
357 fix :
358 Modified the jai_ap_ida_trigger_pkg.process_ipv (cursor get_rcv_tax_amt).
359 also for receipt exchange rate is fetched from rcv_transactions instead
360 of PO document.
361
362 18. 6-FEB-2012 Xin Modified for Bug 13540555
363 Issue : INCORRECT ACCOUNT WHEN CANCEL INVOICE
364 Fixed : Modified the same period check for cancellation.
365
366 19. 16-Feb-2012 Qinglei modified for bug#13725705
367 Issue: AMOUNT FOR INVOICE CANCELLATION JOURNAL IS NEGATIVE
368 Fixed: Use positive amount insert into gl interface for invoice cancellation
369 20. 20-DEC-2012 Qiong for reverse charge bug#16001407.
370
371 20. 31-Aug-2012 amandali for bug 14507573
372 Description:Service Tax accounting not happening for PO matched invoice having multiple distributions
373 Fix:Modified the cursor get_match_item_cur as the po_distribution_id would be null in ap_invoice_lines_all for a PO line having multiple distributions.
374
375 21. 18-Feb-2013 amandali for bug 16241506.
376 issue: STandard line accounting is not happening for a cancelled invoice when the accounting
377 is run after cancellation only.
378 fix:commented the ld_cancell_date is null and reversal_flag='N'
379
380 22. 05-March-2013 amandali for bug 16393213
381 Issue:Accounting entries not generated for cancelled lines for a PO/Receipt matched invoices
382 Fix: Added parent_Reversal_id condition to invoice_distribution_id in cursor get_match_item_cur
383 and also added parent the same condition in cursor get_matched_tax_cur
384 24. 07-mar-2013 vkaranam for bug#16314805
385 Issue: accounting for a -ve line in a Standard invoice is wrong.
386 Fix:
387 changes are done in aruid_t1 to pick the correct accounts based on the sign of the distribution amount.
388 Dependency:
389 ----------
390
391 Sl No. Bug Dependent on
392 Bug/Patch set Details
393 -------------------------------------------------------------------------------------------------
394 1 3924692 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
395 4033992.
396 ja_in_util_pkg_s.sql 115.0
397 ja_in_util_pkg_b.sql 115.0
398 ------------------------------------------------------------------------------------------ */
399 --if
400 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_AP_AIDA_AFTER_TRG',
401 -- p_org_id => pr_new.org_id,
402 -- p_set_of_books_id => pr_new.set_of_books_id )
403 -- =
404 -- FALSE
405 --then
406 /* India Localization funtionality is not required */
407 -- return;
408 --end if;
409
410 -- Bug 7114863. Added by Lakshmi Gopalsami
411 -- Removed the reference to jai_ap_tolerance_pkg.check_tolerance_hold
412
413 /* Bug 4406963. Added by LGOPALSA */
414
415 /* Proceed only when Match_Status_Flag is changed to 'A'.
416 Implies invoice is in validated status */
417
418
419
420 If lv_debug = 'Y' Then
421 Begin
422 Select DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL,
423 Value,SUBSTR (value,1,INSTR(value,',') -1))
424 INTO lv_utl_location
425 from v$parameter
426 where name = 'utl_file_dir';
427
428 lv_myfilehandle := UTL_FILE.FOPEN(lv_utl_location, lv_log_file_name ,'A');
429 UTL_FILE.PUT_LINE(lv_myfilehandle, '********* Start ja_in_ap_aida_after_trg('||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS') ||') *********');
430
431 EXCEPTION
435 End if; /* lv_debug ='Y' */
432 WHEN OTHERS THEN
433 lv_debug := 'N';
434 END;
436
437 If lv_debug = 'Y' Then -- added, Harshita for Bug 5219176
438 UTL_FILE.PUT_LINE(lv_myfilehandle, ' inside update ');
439 UTL_FILE.PUT_LINE(lv_myfilehandle, ' line type lookup code '|| pr_new.line_type_lookup_code);
440 UTL_FILE.PUT_LINE(lv_myfilehandle, ' reversal flag '|| pr_new.reversal_flag);
441 UTL_FILE.PUT_LINE(lv_myfilehandle, ' old match status flag '|| pr_old.match_status_flag);
442 UTL_FILE.PUT_LINE(lv_myfilehandle, ' match status flag '|| pr_new.match_status_flag);
443 UTL_FILE.PUT_LINE(lv_myfilehandle, ' old price variance '|| pr_old.invoice_price_variance);
444 UTL_FILE.PUT_LINE(lv_myfilehandle, ' new price variance '|| pr_new.invoice_price_variance);
445 UTL_FILE.PUT_LINE(lv_myfilehandle, ' old exc. vari '|| pr_old.exchange_rate_variance);
446 UTL_FILE.PUT_LINE(lv_myfilehandle, ' exc. vari '|| pr_new.exchange_rate_variance);
447 UTL_FILE.PUT_LINE(lv_myfilehandle, ' invoice id '|| pr_new.invoice_id);
448 UTL_FILE.PUT_LINE(lv_myfilehandle, ' invoice dist id '|| pr_new.invoice_distribution_id);
449 end if ;
450
451 If updating Then
452
453 If ( ( pr_new.line_type_lookup_code = 'ITEM') AND
454 ( ( nvl(pr_old.invoice_price_variance,0) <> nvl(pr_new.invoice_price_variance,0) ) OR
455 ( nvl(pr_old.base_invoice_price_variance,0) <> nvl(pr_new.base_invoice_price_variance,0) ) OR
456 ( nvl(pr_old.exchange_rate_variance,0) <> nvl(pr_new.exchange_rate_variance,0) )
457 )
458
459 ) Then
460
461 If lv_debug = 'Y' Then -- added, Harshita for Bug 5219176
462
463 UTL_FILE.PUT_LINE(lv_myfilehandle, ' inside all condition chec k ');
464
465 UTL_FILE.PUT_LINE(lv_myfilehandle, ' inside match status flag ');
466
467 End If ;
468
469 /* Check whether MISC (Tax lines) exists for this invoice
470 Then proceed. Else return.
471 */
472 Open check_loc_tax(pr_new.invoice_id);
473 Fetch check_loc_tax into lv_exists;
474 Close check_loc_tax;
475
476 If nvl(lv_exists , 'N') = 'Y' Then
477
478 /* Proceed only when the variances are calculated and old variance
479 is not equal to the new variances */
480
481 IF lv_debug = 'Y' THEN
482 UTL_FILE.PUT_LINE(lv_myfilehandle, ' exists flag ');
483 UTL_FILE.PUT_LINE(lv_myfilehandle,
484 'invoice_id => '|| pr_new.invoice_id
485 || ', po_distribution_id => ' ||pr_new.po_distribution_id
486 || ', line_type_lookup_code => ' ||pr_new.line_type_lookup_code
487 || ', amount => ' ||pr_new.amount
488 || ', Dist Line number => ' || pr_new.distribution_line_number
489 );
490 UTL_FILE.PUT_LINE(lv_myfilehandle,
491 ', amount => ' ||pr_new.amount
492 || ', base_amount => ' ||pr_new.base_amount
493 || ', Old Price Variance => ' || pr_old.invoice_price_variance
494 || ', New Price Variance => ' || pr_new.invoice_price_variance
495 || ', Base Old Price Variance => ' || pr_old.base_invoice_price_variance
496 || ', Base New Price Variance => ' || pr_new.base_invoice_price_variance
497 || ', Var CCID => '||pr_new.price_var_code_combination_id
498 || ', org_id => ' ||pr_new.org_id
499 );
500 END IF;
501
502 -- Call the CP for calculating the IPV
503
504 lb_result := Fnd_Request.set_mode(TRUE);
505 ln_request_id := Fnd_Request.submit_request
506 ('JA', -- Changed to JA from SQLAP, 4579729
507 'JAIAPIPV',
508 'India - Create Variances for Payables Tax line',
509 '',
510 FALSE,
511 pr_new.invoice_id,
512 pr_new.po_distribution_id,
513 pr_new.invoice_distribution_id,
514 pr_new.amount,
515 pr_new.base_amount,
516 pr_new.rcv_transaction_id,
517 pr_new.invoice_price_variance,
518 pr_new.base_invoice_price_variance,
519 pr_new.price_var_code_combination_id,
520 pr_new.Exchange_rate_variance,
521 pr_new.rate_var_code_combination_id
522 );
523 IF lv_debug = 'Y' THEN
524 UTL_FILE.fclose(lv_myfilehandle);
525 END IF;
526
527 End if; /* lv_exists ='Y' */
528
529 End if; /* For Variances */
530 --Add by Wenqiong for POT, reg bug#12397015 on 26-Apr-2011, begin
531 ---------------------------------------------------------------------------
532 /*12793930 - Pass Accounting entries only if reversal id is not present*/
533 ld_canceled_date := NULL;
534 OPEN get_cancell_date_cur;
535 FETCH get_cancell_date_cur INTO ld_canceled_date;
536 CLOSE get_cancell_date_cur;
537 IF pr_old.posted_flag IN('S')
538 AND pr_new.posted_flag = 'Y' AND pr_new.parent_reversal_id is NULL
539 THEN
540 /*commented the below condition for bug#16142506*
541 AND ld_canceled_date is NULL AND nvl(pr_new.reversal_flag, 'N') = 'N' THEN*/
542 --Start additions for bug#16241506
543 lv_accting_exists :='N';
544 open get_acct_exists(pr_new.invoice_distribution_id);
545 fetch get_acct_exists into lv_accting_exists;
546 close get_acct_exists;
547
548 if nvl(lv_accting_Exists,'N') ='N' then
549
550 lv_event := 'ACCOUNTING';
551 lv_called_from := 'AP_ACCOUNTING';
555 --If the standard line accounting exists then return.
552 lv_source_trx_type := 'INVOICE_ACCOUNTING';
553 ld_gl_date := pr_new.accounting_date;
554 else
556 return;
557 end if;
558 --end additions for bug#16241506
559 -- comment out by zhiwei.xin on 6-FEB-2012 for bug 13540555 begin
560 --ELSIF nvl(pr_old.reversal_flag, 'N') = 'N' AND pr_new.reversal_flag = 'Y' AND pr_old.posted_flag = 'Y' THEN
561 -- comment out by zhiwei.xin on 6-FEB-2012 for bug 13540555 end.
562
563 -- added by zhiwei.xin on 6-FEB-2012 for bug 13540555 begin
564 -- switch trigger to cancelled distribution line
565 ELSIF nvl(pr_new.cancellation_flag, 'N') = 'Y' AND pr_new.parent_reversal_id is not NULL AND pr_old.posted_flag IN('S') AND pr_new.posted_flag = 'Y' THEN
566 -- added by zhiwei.xin on 6-FEB-2012 for bug 13540555 end.
567 --Add by Xiao for AP Cancellation
568 lv_event := 'CANCEL';
569 lv_called_from := 'AP_CANCELLATION';
570 lv_source_trx_type := 'INVOICE_CANCELLATION';
571 -- comment out by zhiwei.xin on 7-FEB-2012 for bug 13540555 begin
572 /*OPEN get_cancell_date_cur;
573 FETCH get_cancell_date_cur INTO ld_gl_date;
574 CLOSE get_cancell_date_cur;*/
575 -- comment out by zhiwei.xin on 7-FEB-2012 for bug 13540555 end.
576 -- added by zhiwei.xin on 7-FEB-2012 for bug 13540555 begin
577 ld_gl_date := pr_new.accounting_date;
578 -- added by zhiwei.xin on 7-FEB-2012 for bug 13540555 end.
579 ELSE
580 RETURN;
581 END IF;
582
583 IF pr_new.line_type_lookup_code = 'MISCELLANEOUS' AND ln_dist_line_amount <> 0 THEN
584 --Add by Qiong for reverse charge bug#16001407 begin
585 ---------------------------------------------------------------------------
586 IF jai_cmn_reverse_charge_pkg.is_reverse_charge_dist(pr_new.invoice_distribution_id) = 'Y' THEN
587 RETURN;
588 END IF;
589 ---------------------------------------------------------------------------
590 --Add by Qiong for reverse charge bug#16001407 end
591
592 OPEN get_invoice_type_cur;
593 FETCH get_invoice_type_cur INTO lv_invoice_type;
594 CLOSE get_invoice_type_cur;
595
596 IF lv_invoice_type IN ('STANDARD', 'CREDIT', 'DEBIT', 'QUICKDEFAULT', 'QUICKMATCH', 'MIXED') THEN
597 OPEN get_regime_id_cur;
598 FETCH get_regime_id_cur INTO ln_regime_id;
599 CLOSE get_regime_id_cur;
600
601 -- remove 'ld_gl_date' by zhiwei.xin on 7-FEB-2012 for bug 13540555
602 OPEN get_curr_dtls_cur;
603 FETCH get_curr_dtls_cur INTO ld_curr_conv_date, ln_curr_conv_rate, lv_curr_conv_type,
604 ld_transaction_date,/*ld_gl_date,*/lv_currency_code;--Add lv_currency_code for bug12934221 2011.09.07 Qiong marked
605 CLOSE get_curr_dtls_cur;
606
607 IF pr_new.po_distribution_id IS NOT NULL THEN
608
609 OPEN get_match_item_cur;
610 FETCH get_match_item_cur INTO lv_match_type, ln_item_line_num;
611 CLOSE get_match_item_cur;
612
613
614 IF lv_match_type IN('ITEM_TO_PO','ITEM_TO_RECEIPT') THEN
615 OPEN get_matched_tax_cur;
616 -- FETCH get_matched_tax_cur INTO ln_dist_line_amount,lv_tax_type;
617 FETCH get_matched_tax_cur INTO lv_tax_type; /* Avanija -16314805 */
618 CLOSE get_matched_tax_cur;
619
620 IF lv_match_type = 'ITEM_TO_PO' THEN
621 OPEN get_po_matched_org_cur(ln_item_line_num);
622 FETCH get_po_matched_org_cur INTO ln_organization_id,ln_location_id;
623 CLOSE get_po_matched_org_cur;
624 ELSE
625 OPEN get_rcpt_matched_org_cur(ln_item_line_num);
626 FETCH get_rcpt_matched_org_cur INTO ln_organization_id,ln_location_id;
627 CLOSE get_rcpt_matched_org_cur;
628 IF ln_organization_id IS NULL OR ln_location_id IS NULL THEN
629 OPEN get_po_matched_org_cur(ln_item_line_num);
630 FETCH get_po_matched_org_cur INTO ln_organization_id,ln_location_id;
631 CLOSE get_po_matched_org_cur;
632 END IF;/*ln_organization_id IS NULL OR ln_location_id IS NULL */
633 END IF;/*lv_match_type = 'ITEM_TO_PO' */
634 END IF;/*lv_match_type IN('ITEM_TO_PO','ITEM_TO_RECEIPT')*/
635 ELSE
636 OPEN get_tax_type_cur;
637 FETCH get_tax_type_cur INTO lv_tax_type;
638 CLOSE get_tax_type_cur;
639
640 OPEN get_org_cur;
641 FETCH get_org_cur INTO ln_organization_id,ln_location_id;
642 CLOSE get_org_cur;
643
644 END IF;/*pr_new.po_distribution_id IS NOT NULL */
645
646 /*Bug 12805386 - Moved the cursor to fetch Accrual Date after fetching Organization ID and Location ID
647 as POT is modifiable at Inventory Organization level*/
648 OPEN c_get_st_accrual_date(ln_regime_id, ln_organization_id, ln_location_id);
649 FETCH c_get_st_accrual_date INTO ld_accrual_date;
650 CLOSE c_get_st_accrual_date;
651
652 /*Bug 12805386 - Moved the code to derive Organization ID and Location ID outside the loop*/
653 IF pr_new.accounting_date >= ld_accrual_date THEN
654
655 ln_entered_amt := round(ln_dist_line_amount, jai_constants.service_rgm_rnd_factor);
656 ln_accounted_amt := ln_entered_amt * nvl(ln_curr_conv_rate, 1);
657
658 IF lv_tax_type IS NOT NULL THEN --Service Tax.
659
660
664 --For External , if Service tax and GL > POT then ignore .
661 --Added by zhiwei for POT change Bug#13023443 begin
662 --Updated by Eric Ma on Nov-28-2011 for bug#13405997 , begin
663 -------------------------------------------------------------
665 /* Move the select into to declar section as a cursor
666 SELECT interface_flag,interface_event
667 INTO lv_interface_flag,lv_interface_event
668 FROM jai_ap_invoice_lines jail_parent,
669 (SELECT invoice_id, parent_invoice_line_number
670 FROM jai_ap_invoice_lines
671 WHERE invoice_line_number = pr_new.invoice_line_number
672 AND invoice_id = pr_new.invoice_id) jail_child
673 WHERE jail_parent.invoice_id = jail_child.invoice_id
674 AND jail_parent.invoice_line_number = jail_child.parent_invoice_line_number;
675
676 */
677
678 OPEN get_jai_interface_info;
679 FETCH get_jai_interface_info INTO lv_interface_flag,lv_interface_event;
680 CLOSE get_jai_interface_info;
681
682 if nvl(lv_interface_flag,'#') = 'Y' and nvl(lv_interface_event,'###') = 'EXTERNAL' then
683
684 null;
685 else
686 ------------------------------------------------------------
687 --Updated by Eric Ma on Nov-28-2011 for bug#13405997 , end
688 --Added by zhiwei for POT change Bug#13023443 end
689
690
691 IF lv_invoice_type IN ('CREDIT', 'DEBIT') THEN
692 --DR: Service Tax interim liability Account
693 IF lv_event = 'ACCOUNTING' THEN
694 lv_account_name := jai_constants.liability_interim;
695 ELSE --'CANCELLATION'
696 lv_account_name := jai_constants.liability;
697 END IF;
698 ln_entered_amt := abs(ln_entered_amt);
699 ln_accounted_amt := abs(ln_accounted_amt);
700 ELSE
701 --DR: Service Tax Recovery Account
702 --DR: Service Tax Recovery Account
703 --start modifications for bug16314085
704 /*--> +ve line accounting:
705 Dr Recovery
706 --> -ve line ,cancelled line with -ve figure accounting :
707 Dr Interim Recovery
708 --> cancelled line with +ve figure accounting:
709 Dr Recovery/Liability based on period check
710 */
711 IF lv_event = 'ACCOUNTING' and sign(ln_dist_line_amount)=1 THEN /* Avanija 16314805 */
712 lv_account_name := jai_constants.recovery;
713 ELSIF ((lv_event= 'ACCOUNTING' and sign(ln_dist_line_amount)=-1 ) or (lv_event= 'CANCELLATION' and sign(ln_dist_line_amount)=-1))THEN--'CANCELLATION'
714 lv_account_name := jai_constants.recovery_interim;
715 ELSIF (lv_event='CANCELLATION' and sign(ln_dist_line_amount)=1 )
716 then
717 lv_same_period_flag := check_same_period_func; --added for bug#13934819
718 IF nvl(lv_same_period_flag, 'N') = 'N' THEN
719 lv_account_name := jai_constants.liability;
720 ELSE
721 lv_account_name := jai_constants.recovery;
722 END IF;
723 else
724 lv_account_name := jai_constants.recovery_interim;
725 END IF;
726
727 /*Added by Qinglei on 16-Feb-2012 for bug#13725705 begin*/
728 ln_entered_amt := abs(ln_entered_amt);
729 ln_accounted_amt := abs(ln_accounted_amt);
730 /*Added by Qinglei on 16-Feb-2012 for bug#13725705 end*/
731 END IF;
732 ln_charge_account_id := jai_cmn_rgm_recording_pkg.get_account(
733 p_regime_id => ln_regime_id
734 , p_organization_type => jai_constants.service_tax_orgn_type
735 , p_organization_id => ln_organization_id
736 , p_location_id => ln_location_id
737 , p_tax_type => lv_tax_type
738 , p_account_name => lv_account_name);
739
740 IF ln_charge_account_id IS NULL THEN
741
742 lv_process_message := 'Recovery/Liability Accounts are not defined at the regime registration. Review Invoice ID: ' || pr_new.invoice_id;
743 RAISE_APPLICATION_ERROR(-20007, 'Error - trigger ja_in_ap_aida_after_trg on ap_invoice_distributions_all: '
744 ||lv_process_message);
745 END IF;
746
747 jai_cmn_rgm_recording_pkg.post_accounting(
748 p_regime_code => lv_regime_code,
749 p_tax_type => lv_tax_type,
750 p_organization_type => jai_constants.service_tax_orgn_type,
751 p_organization_id => ln_organization_id,
752 p_source => jai_constants.source_ap,
753 p_source_trx_type => lv_source_trx_type,
754 p_source_table_name => 'AP_INVOICE_DISTRIBUTIONS_ALL',
755 p_source_document_id => ln_invoice_dist_id,
759 p_accounted_cr => NULL,
756 p_code_combination_id => ln_charge_account_id,
757 p_entered_cr => NULL,
758 p_entered_dr => ln_entered_amt,
760 p_accounted_dr => ln_accounted_amt,
761 p_accounting_date => ld_gl_date,
762 p_transaction_date => ld_transaction_date,
763 p_calling_object => lv_called_from,
764 p_repository_name => 'invoice_distribution_id',
765 p_repository_id => NULL,
766 p_reference_name => 'AP_INVOICE_DISTRIBUTIONS_ALL',
767 p_reference_id => ln_invoice_dist_id,
768 p_currency_code => lv_currency_code,
769 p_curr_conv_date => ld_curr_conv_date,
770 p_curr_conv_type => lv_curr_conv_type,
771 p_curr_conv_rate => ln_curr_conv_rate
772 );
773
774 IF lv_invoice_type IN ('CREDIT', 'DEBIT') THEN
775 --CR: Service Tax liability Account
776 IF lv_event = 'ACCOUNTING' THEN
777 lv_account_name := jai_constants.liability;
778 ELSE --'CANCELLATION'
779 lv_account_name := jai_constants.liability_interim;
780 END IF;
781 ELSE
782 --CR: Service Tax Recovery Interim Account
783
784 --start modifications for bug16314085
785 /*--> +ve line accounting(existing):
786 Cr Interim Recovery
787 --> -ve line accounting (added):
788 Cr Recovery
789 -->cancelled line with -ve figure accounting (existing):
790 Cr Recovery/Liability based on period check
791 --> cancelled line with +ve figure accounting(added) :
792 Cr Interim recovery
793 */
794 IF lv_event = 'ACCOUNTING' and sign(ln_dist_line_amount)=1 or (lv_event = 'CANCELLATION' and sign(ln_dist_line_amount)=1)THEN /* Avanija 16314805 */
795 lv_account_name := jai_constants.recovery_interim;
796 ELSIF (lv_event= 'ACCOUNTING' and sign(ln_dist_line_amount)=-1 ) then
797 lv_account_name := jai_constants.recovery;
798 elsif (lv_event = 'CANCELLATION' and sign(ln_dist_line_amount)=-1) then --'CANCELLATION'
799 IF lv_invoice_type = 'STANDARD' THEN
800 /*commented for bug#13934819
801 OPEN check_same_period_cur;
802 FETCH check_same_period_cur INTO lv_same_period_flag;
803 CLOSE check_same_period_cur;
804 */
805 lv_same_period_flag := check_same_period_func; --added for bug#13934819
806 IF nvl(lv_same_period_flag, 'N') = 'N' THEN
807 lv_account_name := jai_constants.liability;
808 ELSE
809 lv_account_name := jai_constants.recovery;
810 END IF;
811 ELSE
812 lv_account_name := jai_constants.recovery;
813 END IF;
814 END IF;
815 END IF;
816
817 ln_charge_account_id := jai_cmn_rgm_recording_pkg.get_account(
818 p_regime_id => ln_regime_id
819 , p_organization_type => jai_constants.service_tax_orgn_type
820 , p_organization_id => ln_organization_id
821 , p_location_id => ln_location_id
822 , p_tax_type => lv_tax_type
823 , p_account_name => lv_account_name);
824 IF ln_charge_account_id IS NULL THEN
825
826 lv_process_message := 'Recovery/Liability Accounts are not defined at the regime registration. Review Invoice ID: ' || pr_new.invoice_id;
827 RAISE_APPLICATION_ERROR(-20007, 'Error - trigger ja_in_ap_aida_after_trg on ap_invoice_distributions_all: '
828 ||lv_process_message);
829 END IF;
830
831 jai_cmn_rgm_recording_pkg.post_accounting(
832 p_regime_code => lv_regime_code,
833 p_tax_type => lv_tax_type,
834 p_organization_type => jai_constants.service_tax_orgn_type,
835 p_organization_id => ln_organization_id,
836 p_source => jai_constants.source_ap,
837 p_source_trx_type => lv_source_trx_type,
838 p_source_table_name => 'AP_INVOICE_DISTRIBUTIONS_ALL',
839 p_source_document_id => ln_invoice_dist_id,
840 p_code_combination_id => ln_charge_account_id,
841 p_entered_cr => ln_entered_amt,
842 p_entered_dr => NULL,
843 p_accounted_cr => ln_accounted_amt,
844 p_accounted_dr => NULL,
845 p_accounting_date => ld_gl_date,
846 p_transaction_date => ld_transaction_date,
850 p_reference_name => 'AP_INVOICE_DISTRIBUTIONS_ALL',
847 p_calling_object => lv_called_from,
848 p_repository_name => 'invoice_distribution_id',
849 p_repository_id => NULL,
851 p_reference_id => ln_invoice_dist_id,
852 p_currency_code => lv_currency_code,
853 p_curr_conv_date => ld_curr_conv_date,
854 p_curr_conv_type => lv_curr_conv_type,
855 p_curr_conv_rate => ln_curr_conv_rate
856 );
857 end if;--Added by zhiwei for open interface POT change bug#13023443 on 20110930
858 END IF;/*tax_type is null*/
859 END IF;/*ld_transaction_date >= ld_accrual_date*/
860 END IF;/* lv_invoice_type IN ('STANDARD', 'CREDIT',....)*/
861 END IF; /*pr_new.line_type_lookup_code = 'MISCELLANEOUS' AND ln_dist_line_amount > 0 */
862 ---------------------------------------------------------------------------
863 --Add by Wenqiong for POT, reg bug#12397015 on 26-Apr-2011, end
864 End if; /* updating */
865
866 IF lv_debug = 'Y' THEN
867 UTL_FILE.fclose(lv_myfilehandle);
868 END IF;
869
870 /* Added an exception block by Ramananda for bug#4570303 */
871 EXCEPTION
872 WHEN OTHERS THEN
873 IF lv_debug ='Y' Then
874 UTL_FILE.PUT_LINE(lv_myfilehandle, 'Error in trigger , errm -> '||SQLERRM);
875 UTL_FILE.fclose(lv_myfilehandle);
876 End if;
877 RAISE;
878 END ARUID_T1 ;
879 /*
880 REM +======================================================================+
881 REM NAME BRIUD_T1
882 REM
883 REM DESCRIPTION Called from trigger JAI_AP_IDA_BRIUD_T1
884 REM
885 REM NOTES Refers to old trigger JAI_AP_IDA_BRIUD_T1
886 REM
887 REM +======================================================================+
888 */
889 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
890 lv_final_dff_value_tds varchar2(150);
891 lv_process_flag varchar2(20);
892 lv_process_message varchar2(200);
893 ln_final_tds_tax_id JAI_CMN_TAXES_ALL.tax_id%type;
894
895 cursor c_ap_invoices_all is
896 select ai.vendor_id,
897 ai.vendor_site_id,
898 ai.invoice_currency_code,
899 ai.exchange_rate,
900 ai.set_of_books_id,
901 ai.source,
902 ai.cancelled_date,
903 -- Bug#5131075(4683207). Added by Lakshmi Gopalsami
904 ai.invoice_type_lookup_code,
905 ai.invoice_num, /*added for bug 6493858 ref-6318997*/
906 pv.vendor_type_lookup_code /* Bug 8330522. Added by Lakshmi Gopalsami */
907 from ap_invoices_all ai, po_vendors pv
908 where ai.invoice_id = pr_new.invoice_id
909 and ai.vendor_id = pv.vendor_id;
910
911 --Added by Sanjikum for Bug#5131075(4722011)
912 CURSOR c_check_prepayment_apply IS
913 SELECT '1'
914 FROM jai_ap_tds_prepayments
915 WHERE invoice_distribution_id_prepay = pr_new.invoice_distribution_id;
916
917 CURSOR c_check_prepayment_unapply IS
918 SELECT '1'
919 FROM jai_ap_tds_prepayments
920 WHERE invoice_distribution_id_prepay = pr_new.parent_reversal_id
921 AND unapply_flag = 'Y';
922
923 lv_prepay_flag VARCHAR2(1);
924 --End addition by Sanjikum for Bug#5131075(4722011)
925
926 c_rec_ap_invoices_all c_ap_invoices_all%rowtype;
927 lv_codepath VARCHAR2(1996);
928 lv_is_invoice_validated varchar2(1);
929 lv_new_transaction_si varchar2(1);
930 lv_new_transaction_pp varchar2(1);
931 ln_org_id ap_invoices_all.org_id%type;
932 ln_set_of_books_id ap_invoices_all.set_of_books_id%type;
933 /* bug 5640993 FP of 5553489. Added by JMEENA
934 * Created variable which decides whether the prepayment
935 * created prior to upgrade(TDS-Threshold) has to be processed
936 */
937 lv_process_old_trxn VARCHAR2(1);
938
939 /*Bug 5989740 bduvarag start*/
940 /* Commented for bug# 6459941
941 CURSOR c_get_rnd_factor (p_org_id IN NUMBER ) IS
942 SELECT NVL(tds_rounding_factor,0) , tds_rounding_start_date
943 FROM JAI_AP_TDS_YEARS
944 WHERE legal_entity_id IN (SELECT legal_entity_id
945 FROM hr_operating_units
946 where organization_id = p_org_id
947 )
948 AND trunc (sysdate) between start_date and end_date; --added by ssawant for bug 6074957
949 Bug 5989740 bduvarag end */
950
951 /* Added for bug# 6459941 */
952 CURSOR c_get_rnd_factor (p_org_id IN NUMBER, p_inv_date IN DATE ) IS -- Added a parameter p_inv_date by Jia for FP Bug#8656402
953 SELECT
954 nvl(tds_rounding_factor,0) ,
955 tds_rounding_start_date
956 FROM
957 jai_ap_tds_years
958 WHERE
959 legal_entity_id = p_org_id
960 AND trunc (p_inv_date) between start_date and end_date ; -- Modified by Jia for FP Bug#8656402, change sysdate to p_inv_date
961 /*START, by amandali for Bug#10430662*/
962 cursor c_receipt_wct_tax(p_shipment_line_id number) is
963 select a.tax_id
964 FROM JAI_RCV_LINE_TAXES a, JAI_CMN_TAXES_ALL b
965 where a.tax_id = b.tax_id
966 and a.shipment_line_id = p_shipment_line_id
967 and a.tax_type = 'TDS'
968 and b.section_type= 'WCT_SECTION';
972 FROM JAI_PO_TAXES a, JAI_CMN_TAXES_ALL b
969
970 CURSOR c_line_location_wct_taxes(focus_id NUMBER) IS
971 SELECT a.tax_id
973 WHERE line_focus_id = focus_id
974 and a.tax_id = b.tax_id
975 and b.section_type= 'WCT_SECTION'
976 AND a. tax_type = 'TDS';
977
978 cursor c_get_shipment_line_id (p_rcv_transaction_id number) is
979 select shipment_line_id
980 from rcv_transactions
981 where transaction_id = p_rcv_transaction_id;
982
983 CURSOR from_po_distributions(po_dist_id NUMBER) IS
984 SELECT line_location_id, po_line_id
985 FROM po_distributions_all
986 WHERE po_distribution_id = po_dist_id;
987
988 CURSOR from_line_locations(loc_id NUMBER, line_id NUMBER) IS
989 SELECT line_focus_id
990 FROM JAI_PO_LINE_LOCATIONS
991 WHERE line_location_id = loc_id
992 AND po_line_id = line_id;
993
994 from_po_distributions_rec from_po_distributions%ROWTYPE;
995 v_shipment_line_id number;
996 v_receipt_wct_tax_id number;
997 from_line_locations_rec from_line_locations%ROWTYPE;
998 C_LINE_LOCATION_WCT_TAXES_REC c_line_location_wct_taxes%rowtype;
999 ln_processed VARCHAR2(1); /*Bug 12630283*/
1000 /*END, by amandali for Bug#10430662*/
1001
1002 /*start changes for bug 6493858 - logic for 5662741 moved from jai_ap_ia_t.plb*/
1003 CURSOR c_tds_invoice_id(cp_invoice_id NUMBER)
1004 IS
1005 SELECT invoice_to_tds_authority_id invoice_id,
1006 invoice_to_tds_authority_num invoice_num
1007 FROM jai_ap_tds_thhold_trxs
1008 WHERE invoice_id = cp_invoice_id;
1009
1010 /* Bug 8330522. Added by Lakshmi Gopalsami */
1011
1012 CURSOR c_tds_invoice_id1(cp_invoice_to_vendor_id NUMBER)
1013 IS
1014 SELECT invoice_to_tds_authority_id invoice_id,
1015 invoice_to_tds_authority_num invoice_num
1016 FROM jai_ap_tds_thhold_trxs
1017 WHERE invoice_to_vendor_id = cp_invoice_to_vendor_id;
1018 -- end for bug 83305222.
1019
1020 r_tds_invoice_id c_tds_invoice_id%ROWTYPE;
1021
1022 lv_invoice_payment_status ap_invoices_all.payment_status_flag%TYPE;
1023
1024 FUNCTION get_invoice_payment_status(p_invoice_id IN NUMBER)
1025 RETURN VARCHAR2
1026 IS
1027 PRAGMA AUTONOMOUS_TRANSACTION;
1028
1029 BEGIN
1030 RETURN (ap_invoices_utility_pkg.get_payment_status(p_invoice_id));
1031 END get_invoice_payment_status;
1032
1033 /*end changes for bug 6493858*/
1034
1035
1036 --Inline procedure added by Sanjikum for Bug#5131075(4722011)
1037 PROCEDURE process_prepayment(cp_event varchar2) --Added parameter cp_event for Bug 8431516
1038 IS
1039 BEGIN
1040 /* Check if the prepayment transaction should be processed by the code before
1041 TDS Clean up or after TDS clean up.
1042
1043 if SI is created in the new regime and also the Prepay is created in the new regime,
1044 then code should invoke the new regime or else
1045 old concurrent shd be invoked */
1046
1047
1048 --Check for SI
1049 jai_ap_tds_tax_defaultation.check_old_transaction
1050 (
1051 p_invoice_id => pr_new.invoice_id,
1052 p_new_transaction => lv_new_transaction_si
1053 );
1054
1055 --Check for Pprepayment
1056 jai_ap_tds_tax_defaultation.check_old_transaction
1057 (
1058 p_invoice_distribution_id => pr_new.prepay_distribution_id,
1059 p_new_transaction => lv_new_transaction_pp
1060 );
1061
1062 if lv_new_transaction_si = 'Y' and lv_new_transaction_pp = 'Y' then
1063
1064 lv_codepath := null;
1065
1066 jai_ap_tds_prepayments_pkg.process_prepayment
1067 (
1068 p_event => cp_event, --Added parameter cp_event for Bug 8431516
1069 p_invoice_id => pr_new.invoice_id,
1070 p_invoice_distribution_id => pr_new.invoice_distribution_id,
1071 p_prepay_distribution_id => pr_new.prepay_distribution_id,
1072 p_parent_reversal_id => pr_new.parent_reversal_id,
1073 p_prepay_amount => pr_new.amount,
1074 p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1075 p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1076 p_accounting_date => pr_new.accounting_date,
1077 p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1078 p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1079 p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1080 p_org_id => pr_new.org_id,
1081 p_creation_date => pr_new.creation_date, /*Bug 5989740 bduvarag*/
1082 p_process_flag => lv_process_flag,
1083 p_process_message => lv_process_message,
1084 p_codepath => lv_codepath
1085 );
1086
1087 if nvl(lv_process_flag, 'N') = 'E' then
1088 raise_application_error(-20007,
1089 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
1090 end if;
1091
1092 else
1093 /* Bug 5640993 FP of 5553489. Added by JMEENA
1094 * Invoking the processing of prepayments only during
1095 * prepayment application and not during validation
1096 */
1097 IF lv_process_old_trxn = 'Y' THEN
1098
1099 --Invoke the old regime functionality
1103 p_invoice_distribution_id => pr_new.invoice_distribution_id,
1100 jai_ap_tds_prepayments_pkg.process_old_transaction
1101 (
1102 p_invoice_id => pr_new.invoice_id,
1104 p_prepay_distribution_id => pr_new.prepay_distribution_id,
1105 p_amount => pr_new.amount,
1106 p_last_updated_by => pr_new.last_updated_by,
1107 p_last_update_date => pr_new.last_update_date,
1108 p_created_by => pr_new.created_by,
1109 p_creation_date => pr_new.creation_date,
1110 p_org_id => pr_new.org_id,
1111 p_process_flag => lv_process_flag,
1112 p_process_message => lv_process_message
1113 );
1114
1115 if nvl(lv_process_flag, 'N') = 'E' then
1116 raise_application_error(-20008,
1117 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
1118 end if;
1119 END IF ; --End of bug#5640993
1120
1121 end if; --Transactions in new regime
1122 END process_prepayment;
1123
1124 --Inline procedure added by vkaranam for bug#13422310
1125 PROCEDURE process_IPV
1126 IS
1127 PRAGMA AUTONOMOUS_TRANSACTION;
1128 /* Cursor to get the localization taxes attached to the item line*/
1129 Cursor get_loc_tax(cp_invoice_id number,cp_inv_distid number) is
1130 select jm.*
1131 from JAI_AP_MATCH_INV_TAXES jm,jai_cmn_taxes_all jct
1132 where jm.tax_id=jct.tax_id
1133 and invoice_id = cp_invoice_id
1134 and parent_invoice_distribution_id=cp_inv_distid
1135 and line_type_lookup_code='MISCELLANEOUS'
1136 --adhoc taxes which are different from transaction currency shall not be considered for IPV/ERV ,16dec
1137 and (nvl(jct.adhoc_flag,'Q')='N'
1138 OR
1139 (nvl(jct.adhoc_flag,'Q')='Y' and exists (select '1' from jai_po_taxes jpt
1140 where jpt.line_location_id=jm.line_location_id
1141 and jpt.currency=jm.currency_code
1142 AND jpt.tax_id=jm.tax_id)
1143 )
1144 OR
1145 (nvl(jct.adhoc_flag,'Q')='Y' and exists (select '1' from jai_rcv_line_taxes jpt
1146 where jpt.shipment_header_id=jm.shipment_header_id
1147 and jpt.shipment_line_id=jm.shipment_line_id
1148 and jpt.currency=jm.currency_code
1149 AND jpt.tax_id=jm.tax_id)
1150 )
1151 );
1152
1153
1154
1155
1156 Cursor get_line_amt(cp_invoice_id number,cp_inv_lineno number) is
1157 select nvl(amount,0)
1158 from ap_invoice_lines_all
1159 where invoice_id = cp_invoice_id
1160 and line_number=cp_inv_lineno;
1161
1162
1163 CURSOR c_get_invoice_distribution is
1164 select ap_invoice_distributions_s.nextval
1165 from dual;
1166
1167 cursor get_dist_no(cp_invoice_id number,cp_inv_lineno number)
1168 is
1169 select max(nvl(distribution_line_number,0))
1170 from ap_invoice_distributions_all
1171 where invoice_id = cp_invoice_id
1172 and invoice_line_number=cp_inv_lineno;
1173
1174 cursor get_tax_name(cp_tax_id in number)
1175 is
1176 select tax_name
1177 from jai_cmn_taxes_all
1178 where tax_id=cp_tax_id;
1179
1180
1181 cursor get_invoice_rate(cp_inv_id in number) is
1182 SELECT exchange_rate
1183 FROM ap_invoices AI
1184 WHERE AI.invoice_id = cp_inv_id;
1185
1186 cursor get_PO_rate(cp_header_id in number) is
1187 SELECT rate
1188 FROM po_headers ph
1189 WHERE ph.po_header_id=cp_header_id;
1190
1191 --start additions by vkaranam for bug#13618731
1192 cursor get_rcv_rate(cp_rcv_transaction_id in number) is
1193 SELECT currency_conversion_rate
1194 FROM rcv_transactions
1195 WHERE transaction_id=cp_rcv_transaction_id;
1196
1197 cursor c_get_accrual_acct(cp_invoice_id number,cp_inv_distid number) is
1198 select dist_code_combination_id
1199 FROM ap_invoice_distributions_all
1200 where invoice_id =cp_invoice_id
1201 and invoice_distribution_id=cp_inv_distid;
1202
1203 ln_accrual_acct number;
1204
1205
1206 lv_exists varchar2(1) := 'N' ;
1207 ln_user_id NUMBER;
1208 ln_login_id NUMBER;
1209 ln_tax_variance_amt NUMBER;
1210 ln_invline_amt number;
1211 ln_base_variance_amt NUMBER;
1212
1213 ln_tax_excvariance_amt NUMBER;
1214 ln_invoice_rate number;
1215 ln_po_exchrate number;
1216 ln_precision fnd_currencies.precision%type;
1217 ln_invoice_distribution_id NUMBER;
1218 ln_distribution_lineno number;
1219 lv_tax_name VARCHAR2(100);
1220
1221 --start changes 13422310 ,14dec
1222
1223
1224 Cursor get_exch_accnts(cp_invoice_id number)
1225 is
1226 SELECT
1227 nvl(sp.rate_var_gain_ccid, -1),
1228 nvl(sp.rate_var_loss_ccid, -1)
1232 WHERE sp.set_of_books_id = gls.set_of_books_id
1229 FROM ap_system_parameters sp,
1230 gl_sets_of_books gls,
1231 ap_invoices ai
1233 AND sp.set_of_books_id = ai.set_of_books_id
1234 AND ai.invoice_id = cp_invoice_id;
1235
1236 ln_xrate_gain_ccid NUMBER;
1237 ln_xrate_loss_ccid NUMBER;
1238 ln_erv_ccid NUMBER;
1239 v_apportn_factor_for_item_line NUMBER;
1240 ln_po_amount NUMBER;
1241 --end changes 13422310 ,14dec
1242 /*modified by vkaranam for bug#13618731
1243 cursor get_rcv_tax_amt (cp_shipment_line_id in number ,cp_tax_id in number)
1244 is
1245 select tax_amount
1246 from jai_rcv_line_taxes
1247 where shipment_line_id=cp_shipment_line_id
1248 and tax_id=cp_tax_id;
1249 */
1250 cursor get_rcv_tax_amt (cp_rcv_transaction_id in number ,cp_tax_id in number)
1251 is
1252 select jt.tax_amount
1253 from jai_rcv_line_taxes jt ,jai_rcv_transactions jrt
1254 where
1255 jrt.shipment_header_id=jt.shipment_headeR_id
1256 and jrt.shipment_line_id=jt.shipment_line_id
1257 and jrt.transaction_id=cp_rcv_transaction_id
1258 and tax_id=cp_tax_id;
1259 cursor get_po_tax_amt (cp_line_location_id in number ,cp_tax_id in number)
1260 is
1261 select tax_amount
1262 from jai_po_taxes
1263 where line_location_id=cp_line_location_id
1264 and tax_id=cp_tax_id;
1265
1266 ln_src_tax_amt NUMBER;
1267
1268 --end additions for bug#13422310
1269
1270
1271 /*
1272 Cursor get_mis_dists
1273 is
1274 select *
1275 from ap_invoice_distributions_all
1276 where invoice_id=cp_invoice_id
1277 and line_type_lookup_code='MISCELLANEOUS'
1278 and invoice_distribut
1279 */
1280
1281 /*
1282 RELATED_ID NUMBER (15)
1283 Identifier linking related distributions. Used for linking related IPV, ERV, ITEM, or ACCRUAL.
1284 Populated from INVOICE_DISTRIBUTION_ID of ITEM or ACCRUAL distribution if any, IPV if no ITEM or ACCRUAL or ERV otherwise
1285 */
1286
1287
1288 BEGIN
1289 ln_user_id := fnd_global.user_id;
1290 ln_login_id := fnd_global.login_id;
1291 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside call to process_ipv');
1292 if pr_new.line_type_lookup_code ='IPV'
1293 then
1294 for jai_rec in get_loc_tax(pr_new.invoice_id,pr_new.related_id)
1295 loop
1296
1297 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ipv pr_new.invoice_id '||pr_new.invoice_id||'pr_new.related_id '||pr_new.related_id||'pr_new.invoice_line_number '||pr_new.invoice_line_number );
1298 ln_invline_amt :=0;
1299 ln_tax_variance_amt:=0;
1300 ln_base_variance_amt := nvl(pr_new.amount,0);
1301 ln_distribution_lineno :=0;
1302 OPEN get_invoice_rate(pr_new.invoice_id);
1303 fetch get_invoice_rate into ln_invoice_rate;
1304 close get_invoice_rate;
1305
1306 OPEN get_line_amt(pr_new.invoice_id,pr_new.invoice_line_number);
1307 FETCH get_line_amt INTO ln_invline_amt;
1308 CLOSE get_line_amt;
1309
1310
1311 --start additions for bug#13422310 ,29dec
1312 v_apportn_factor_for_item_line:=null;
1313 ln_src_tax_amt:=null;
1314 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ipv SHIPMENT line id '||jai_rec.shipment_line_id||' line_location_id '||jai_rec.line_location_id);
1315 --if jai_rec.shipment_line_id is not null 13618731
1316 if jai_rec.rcv_transaction_id is not null
1317 then
1318 open get_rcv_tax_amt(jai_rec.rcv_transaction_id ,jai_rec.tax_id);--13618731
1319 fetch get_rcv_tax_amt into ln_src_tax_amt;
1320 close get_rcv_tax_amt;
1321 elsif jai_rec.line_location_id is not null then
1322 open get_po_tax_amt(jai_rec.line_location_id ,jai_rec.tax_id);
1323 fetch get_po_tax_amt into ln_src_tax_amt;
1324 close get_po_tax_amt;
1325 end if;
1326 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ipv ln_src_tax_amt before apportiong '||ln_src_tax_amt|| ' v_apportn_factor_for_item_line '||v_apportn_factor_for_item_line);
1327 v_apportn_factor_for_item_line := jai_ap_utils_pkg.get_apportion_factor(pr_new.invoice_id,pr_new.invoice_line_number,'QUANTITY');
1328 ln_src_tax_amt := nvl(ln_src_tax_amt,0)*v_apportn_factor_for_item_line; --30dec
1329 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ipv ln_src_tax_amt after apportiong '||ln_src_tax_amt);
1330 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ipv ln_base_variance_amt '||ln_base_variance_amt||' nvl(jai_rec.tax_amount,0) '||nvl(jai_rec.tax_amount,0)||'ln_invline_amt '||ln_invline_amt);
1331 --calculate the tax variance amount
1332 --ipv_tax_variance_amt := invoice amount-po_amount ;
1333
1334 --ln_tax_variance_amt :=ln_base_variance_amt *nvl(jai_rec.tax_amount,0)/ln_invline_amt ; commented on 29dec for bug#13422310
1335 -- ln_tax_variance_amt := ln_src_tax_amt-nvl(jai_rec.tax_amount,0); 30 dec
1336 ln_tax_variance_amt := nvl(jai_rec.tax_amount,0)-ln_src_tax_amt; --13422310 ,30dec
1337
1338 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ipv ln_tax_variance_amt '||ln_tax_variance_amt);
1339 --end additions for bug#13422310 ,29dec
1340 if nvl(ln_tax_variance_amt,0)<>0
1341 then
1342
1343 open c_get_invoice_distribution;
1344 fetch c_get_invoice_distribution into ln_invoice_distribution_id;
1345 close c_get_invoice_distribution;
1349 close get_dist_no;
1346 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ipv before get_dist_no ');
1347 open get_dist_no(pr_new.invoice_id,jai_rec.invoice_line_number);
1348 fetch get_dist_no into ln_distribution_lineno;
1350 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ipv ln_distribution_lineno '||ln_distribution_lineno);
1351 open get_tax_name(jai_rec.tax_id);
1352 fetch get_tax_name into lv_tax_name;
1353 close get_tax_name;
1354
1355 ln_distribution_lineno :=nvl(ln_distribution_lineno,0)+1;
1356
1357 --insert the variance amount with line_type_lookup_code as MISCELLANEOUS
1358 INSERT INTO ap_invoice_distributions_all
1359 (
1360 accounting_date,
1361 accrual_posted_flag,
1362 assets_addition_flag,
1363 assets_tracking_flag,
1364 cash_posted_flag,
1365 distribution_line_number,
1366 dist_code_combination_id,
1367 invoice_id,
1368 last_updated_by,
1369 last_update_date,
1370 line_type_lookup_code,
1371 period_name,
1372 set_of_books_id ,
1373 amount,
1374 base_amount,
1375 batch_id,
1376 created_by,
1377 creation_date,
1378 description,
1379 exchange_rate_variance,
1380 last_update_login,
1381 match_status_flag,
1382 posted_flag,
1383 rate_var_code_combination_id ,
1384 reversal_flag ,
1385 program_application_id,
1386 program_id,
1387 program_update_date,
1388 accts_pay_code_combination_id,
1389 invoice_distribution_id,
1390 quantity_invoiced,
1391 po_distribution_id ,
1392 rcv_transaction_id,
1393 --price_var_code_combination_id,/*no longer used in R12*/
1394 --invoice_price_variance,/*no longer used in R12*/
1395 -- base_invoice_price_variance,/*no longer used in R12*/
1396 matched_uom_lookup_code
1397 ,invoice_line_number
1398 ,org_id
1399 ,charge_applicable_to_dist_id
1400 , project_id
1401 , task_id
1402 , expenditure_type
1403 , expenditure_item_date
1404 , expenditure_organization_id
1405 , project_accounting_context
1406 , pa_addition_flag
1407 ,distribution_class
1408 ,related_id /*13422310*/
1409 )
1410 VALUES
1411 (
1412 pr_new.accounting_date,
1413 pr_new.accrual_posted_flag,
1414 pr_new.assets_addition_flag,
1415 'N',/*need to check the importance of assets_tracking_flag*/
1416 'N',
1417 ln_distribution_lineno,
1418 pr_new.dist_code_combination_id,
1419 pr_new.invoice_id,
1420 ln_user_id,
1421 sysdate,
1422 'MISCELLANEOUS',
1423 pr_new.period_name,
1424 pr_new.set_of_books_id ,
1425 ln_tax_variance_amt ,
1426 -- ln_tax_variance_amt * NVL(pr_new.exchange_rate,1), 14dec
1427 ln_tax_variance_amt * NVL(ln_invoice_rate,1), --14dec
1428 pr_new.batch_id,
1429 ln_user_id,
1430 sysdate,
1431 lv_tax_name,
1432 null,
1433 ln_login_id,
1434 pr_new.match_status_flag ,
1435 'N',
1436 NULL,
1437 pr_new.reversal_flag,
1438 pr_new.program_application_id,
1439 pr_new.program_id,
1440 pr_new.program_update_date,
1441 pr_new.accts_pay_code_combination_id,
1442 ln_invoice_distribution_id,
1443 null,
1444 pr_new.po_distribution_id ,
1445 pr_new.rcv_transaction_id,
1446 -- v_price_var_accnt,
1447 -- v_tax_variance_inv_cur,
1448 -- v_tax_variance_fun_cur,
1449 pr_new.matched_uom_lookup_code,
1450 jai_rec.invoice_line_number,
1451 pr_new.org_id,
1452 pr_new.charge_applicable_to_dist_id/*need to check this,Invoice distribution to which 100% of current charge is applied */
1453 , pr_new.project_id
1454 , pr_new.task_id
1455 , pr_new.expenditure_type
1456 , pr_new.expenditure_item_date
1457 , pr_new.expenditure_organization_id
1458 , pr_new.project_accounting_context
1459 , pr_new.pa_addition_flag
1460 ,pr_new.distribution_class
1461 ,jai_rec.invoice_distribution_id
1462 );
1463
1464
1465 --INSERT THE -VE VARIANCE AMOUNT TO ACCRUAL ACCOUNT ,SUCH THAT THE ACCRUAL amount will be of the PO tax amt and is in sync with base
1466 ln_invoice_distribution_id :=null;
1467
1468 open c_get_invoice_distribution;
1469 fetch c_get_invoice_distribution into ln_invoice_distribution_id;
1470 close c_get_invoice_distribution;
1471 --jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ipv before get_dist_no ');
1472
1473
1474
1475 open c_get_accrual_acct (pr_new.invoice_id,jai_rec.parent_invoice_distribution_id);
1476 fetch c_get_accrual_acct into ln_accrual_acct;
1477 close c_get_accrual_acct;
1478
1479 ln_distribution_lineno :=nvl(ln_distribution_lineno,0)+1;
1480
1481 --insert the variance amount with line_type_lookup_code as MISCELLANEOUS
1482 INSERT INTO ap_invoice_distributions_all
1483 (
1484 accounting_date,
1485 accrual_posted_flag,
1486 assets_addition_flag,
1487 assets_tracking_flag,
1488 cash_posted_flag,
1492 last_updated_by,
1489 distribution_line_number,
1490 dist_code_combination_id,
1491 invoice_id,
1493 last_update_date,
1494 line_type_lookup_code,
1495 period_name,
1496 set_of_books_id ,
1497 amount,
1498 base_amount,
1499 batch_id,
1500 created_by,
1501 creation_date,
1502 description,
1503 exchange_rate_variance,
1504 last_update_login,
1505 match_status_flag,
1506 posted_flag,
1507 rate_var_code_combination_id ,
1508 reversal_flag ,
1509 program_application_id,
1510 program_id,
1511 program_update_date,
1512 accts_pay_code_combination_id,
1513 invoice_distribution_id,
1514 quantity_invoiced,
1515 po_distribution_id ,
1516 rcv_transaction_id,
1517 --price_var_code_combination_id,/*no longer used in R12*/
1518 --invoice_price_variance,/*no longer used in R12*/
1519 -- base_invoice_price_variance,/*no longer used in R12*/
1520 matched_uom_lookup_code
1521 ,invoice_line_number
1522 ,org_id
1523 ,charge_applicable_to_dist_id
1524 , project_id
1525 , task_id
1526 , expenditure_type
1527 , expenditure_item_date
1528 , expenditure_organization_id
1529 , project_accounting_context
1530 , pa_addition_flag
1531 ,distribution_class
1532 ,related_id /*13422310*/
1533 )
1534 VALUES
1535 (
1536 pr_new.accounting_date,
1537 pr_new.accrual_posted_flag,
1538 pr_new.assets_addition_flag,
1539 'N',/*need to check the importance of assets_tracking_flag*/
1540 'N',
1541 ln_distribution_lineno,
1542 ln_accrual_acct,
1543 pr_new.invoice_id,
1544 ln_user_id,
1545 sysdate,
1546 'MISCELLANEOUS',
1547 pr_new.period_name,
1548 pr_new.set_of_books_id ,
1549 -ln_tax_variance_amt ,
1550 -- -ln_tax_variance_amt * NVL(pr_new.exchange_rate,1), 14dec
1551 -ln_tax_variance_amt * NVL(ln_invoice_rate,1), --14dec
1552 pr_new.batch_id,
1553 ln_user_id,
1554 sysdate,
1555 lv_tax_name,
1556 null,
1557 ln_login_id,
1558 pr_new.match_status_flag ,
1559 'N',
1560 NULL,
1561 pr_new.reversal_flag,
1562 pr_new.program_application_id,
1563 pr_new.program_id,
1564 pr_new.program_update_date,
1565 pr_new.accts_pay_code_combination_id,
1566 ln_invoice_distribution_id,
1567 null,
1568 pr_new.po_distribution_id ,
1569 pr_new.rcv_transaction_id,
1570 -- v_price_var_accnt,
1571 -- v_tax_variance_inv_cur,
1572 -- v_tax_variance_fun_cur,
1573 pr_new.matched_uom_lookup_code,
1574 jai_rec.invoice_line_number,
1575 pr_new.org_id,
1576 pr_new.charge_applicable_to_dist_id/*need to check this,Invoice distribution to which 100% of current charge is applied */
1577 , pr_new.project_id
1578 , pr_new.task_id
1579 , pr_new.expenditure_type
1580 , pr_new.expenditure_item_date
1581 , pr_new.expenditure_organization_id
1582 , pr_new.project_accounting_context
1583 , pr_new.pa_addition_flag
1584 ,pr_new.distribution_class
1585 ,jai_rec.invoice_distribution_id
1586 );
1587
1588
1589
1590 commit;
1591 end if;--if nvl(ln_tax_variance_amt,0)<>0
1592
1593 end loop;
1594
1595 elsif pr_new.line_type_lookup_code ='ERV'
1596 then
1597 for jai_rec in get_loc_tax(pr_new.invoice_id,pr_new.related_id)
1598 loop
1599
1600 ln_invline_amt :=0;
1601 ln_tax_excvariance_amt:=0;
1602 ln_po_exchrate := 0;
1603 ln_invoice_rate :=0;
1604 ln_distribution_lineno :=0;
1605
1606 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ERV pr_new.invoice_id '||pr_new.invoice_id||'pr_new.related_id '||pr_new.related_id||'pr_new.invoice_line_number '||pr_new.invoice_line_number );
1607 OPEN get_invoice_rate(pr_new.invoice_id);
1608 fetch get_invoice_rate into ln_invoice_rate;
1609 close get_invoice_rate;
1610
1611 OPEN get_po_rate(jai_rec.po_header_id);
1612 fetch get_po_rate into ln_po_exchrate;
1613 close get_po_rate;
1614 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ERV ln_invoice_rate '||ln_invoice_rate||' jai_rec.po_header_id '||jai_rec.po_header_id||' ln_po_exchrate '||ln_po_exchrate);
1615
1616 --calculate the tax exchange variance amount
1617 --exchange_rate_variance_amount := taxamount_in_usd *(Invoice_exchange_rate-po_exchange_rate);
1618 /*issue mentioned by the ct:
1619
1620 --po : 250 usd ,10K
1621 --invoice : 250 USD,11k
1622
1623 --erv 1k
1624 --due to price variance amount is changes from 250 usd to 300 usd.
1625
1626 --po :250 ,10k
1627 --invoice : 300 usd ,13200
1628 -- ipv : 50 usd , 2200
1629 --ipv : -50 usd ,2200
1630 --erv : 0 , 1200 (30*4) --correct entry : should be 1000 (250*4) i.e exchange rate shall be caclulated on PO/rcpt tax amount ,instead of the invoice amount
1631 --erv : 0 ,-1200 -- correct entry should be : -1000 (250*4)
1632 --logic to get the invoice amount is :
1633 -- po_amount*apportion_factor_for_item_line
1637 --start additions for bug#13422310 ,30dec
1634 --po_amount:=invoice_amount /apportion_Factor_for_item_line (not fetched the PO amount directly as there can be cased of invoice matched against partial PO)
1635 */
1636
1638 ln_src_tax_amt:=null;
1639 ln_po_amount:=null;
1640 --if jai_rec.shipment_line_id is not null 13618731
1641 if jai_rec.rcv_transaction_id is not null
1642 then
1643 open get_rcv_tax_amt(jai_rec.rcv_transaction_id ,jai_rec.tax_id);--13618731
1644 fetch get_rcv_tax_amt into ln_src_tax_amt;
1645 close get_rcv_tax_amt;
1646
1647 --added for bug#13618731
1648 OPEN get_rcv_rate(jai_rec.rcv_transaction_id);
1649 fetch get_rcv_rate into ln_po_exchrate;
1650 close get_rcv_rate;
1651 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','receipt exchange rate '||ln_po_exchrate||' receipt tax amount '||ln_src_tax_amt);--13618731
1652
1653 elsif jai_rec.line_location_id is not null then
1654 open get_po_tax_amt(jai_rec.line_location_id ,jai_rec.tax_id);
1655 fetch get_po_tax_amt into ln_src_tax_amt;
1656 close get_po_tax_amt;
1657
1658 OPEN get_po_rate(jai_rec.po_header_id);
1659 fetch get_po_rate into ln_po_exchrate;
1660 close get_po_rate;
1661 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','po exchange rate '||ln_po_exchrate||' po tax amount '||ln_src_tax_amt);--13618731
1662 end if;
1663 --end bug#13422310 ,30dec
1664 --Start additions for bug#13422310 ,18dec
1665 v_apportn_factor_for_item_line := jai_ap_utils_pkg.get_apportion_factor(pr_new.invoice_id,pr_new.invoice_line_number, 'QUANTITY');
1666 -- ln_po_amount:= nvl(jai_rec.tax_amount,0)/v_apportn_factor_for_item_line; 30dec
1667 ln_po_amount := ln_src_tax_amt *v_apportn_factor_for_item_line;
1668
1669 --ln_tax_excvariance_amt :=nvl(jai_rec.tax_amount,0) *(nvl(ln_invoice_rate,0)-nvl(ln_po_exchrate,0)); 18dec
1670 ln_tax_excvariance_amt :=nvl(ln_po_amount,0) *(nvl(ln_invoice_rate,0)-nvl(ln_po_exchrate,0));--18dec
1671 --start changes for 13422310 --14dec
1672 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ERV nvl(jai_rec.tax_amount,0) '||nvl(jai_rec.tax_amount,0)||' ln_tax_excvariance_amt '||ln_tax_excvariance_amt);
1673 if nvl(ln_tax_excvariance_amt,0)<>0
1674 then
1675
1676 ln_xrate_gain_ccid :=null;
1677 ln_xrate_loss_ccid :=null;
1678
1679 open get_exch_accnts(pr_new.invoice_id);
1680 fetch get_exch_accnts into ln_xrate_gain_ccid,ln_xrate_loss_ccid;
1681 close get_exch_accnts;
1682
1683 if sign(nvl(ln_invoice_rate,0)-nvl(ln_po_exchrate,0))=1
1684 then
1685 ln_erv_ccid := ln_xrate_gain_ccid;
1686 else
1687 ln_erv_ccid := ln_xrate_loss_ccid;
1688 end if;
1689
1690 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ERV exchange gain ccid '||ln_xrate_gain_ccid);
1691 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ERV exchange loss ccid '||ln_xrate_loss_ccid);
1692 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ERV CCID Derived is '||ln_erv_ccid);
1693 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ERV exchange_rate_variance_amount '||ln_tax_excvariance_amt);
1694
1695 --end changes for 13422310 --14dec
1696 open c_get_invoice_distribution;
1697 fetch c_get_invoice_distribution into ln_invoice_distribution_id;
1698 close c_get_invoice_distribution;
1699 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ERV BEFORE get_dist_no ');
1700 open get_dist_no(pr_new.invoice_id,jai_rec.invoice_line_number);
1701 fetch get_dist_no into ln_distribution_lineno;
1702 close get_dist_no;
1703
1704 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ERV ln_distribution_lineno ' ||ln_distribution_lineno);
1705
1706 open get_tax_name(jai_rec.tax_id);
1707 fetch get_tax_name into lv_tax_name;
1708 close get_tax_name;
1709
1710 ln_distribution_lineno :=nvl(ln_distribution_lineno,0)+1;
1711
1712 --insert the variance amount with line_type_lookup_code as MISCELLANEOUS
1713 INSERT INTO ap_invoice_distributions_all
1714 (
1715 accounting_date,
1716 accrual_posted_flag,
1717 assets_addition_flag,
1718 assets_tracking_flag,
1719 cash_posted_flag,
1720 distribution_line_number,
1721 dist_code_combination_id,
1722 invoice_id,
1723 last_updated_by,
1724 last_update_date,
1725 line_type_lookup_code,
1726 period_name,
1727 set_of_books_id ,
1728 amount,
1729 base_amount,
1730 batch_id,
1731 created_by,
1732 creation_date,
1733 description,
1734 exchange_rate_variance,
1735 last_update_login,
1736 match_status_flag,
1737 posted_flag,
1738 rate_var_code_combination_id ,
1739 reversal_flag ,
1740 program_application_id,
1741 program_id,
1742 program_update_date,
1743 accts_pay_code_combination_id,
1744 invoice_distribution_id,
1745 quantity_invoiced,
1746 po_distribution_id ,
1747 rcv_transaction_id,
1748 --price_var_code_combination_id,/*no longer used in R12*/
1749 --invoice_price_variance,/*no longer used in R12*/
1750 -- base_invoice_price_variance,/*no longer used in R12*/
1754 ,charge_applicable_to_dist_id
1751 matched_uom_lookup_code
1752 ,invoice_line_number
1753 ,org_id
1755 , project_id
1756 , task_id
1757 , expenditure_type
1758 , expenditure_item_date
1759 , expenditure_organization_id
1760 , project_accounting_context
1761 , pa_addition_flag
1762 ,distribution_class
1763 ,related_id /*13422310*/
1764 )
1765 VALUES
1766 (
1767 pr_new.accounting_date,
1768 pr_new.accrual_posted_flag,
1769 pr_new.assets_addition_flag,
1770 'N',/*need to check the importance of assets_tracking_flag*/
1771 'N',
1772 ln_distribution_lineno,
1773 -- pr_new.dist_code_combination_id,
1774 ln_erv_ccid ,
1775 pr_new.invoice_id,
1776 ln_user_id,
1777 sysdate,
1778 'MISCELLANEOUS',
1779 pr_new.period_name,
1780 pr_new.set_of_books_id ,
1781 0 ,
1782 ln_tax_excvariance_amt,
1783 pr_new.batch_id,
1784 ln_user_id,
1785 sysdate,
1786 lv_tax_name,
1787 null,
1788 ln_login_id,
1789 pr_new.match_status_flag ,
1790 'N',
1791 NULL,
1792 pr_new.reversal_flag,
1793 pr_new.program_application_id,
1794 pr_new.program_id,
1795 pr_new.program_update_date,
1796 pr_new.accts_pay_code_combination_id,
1797 ln_invoice_distribution_id,
1798 null,
1799 pr_new.po_distribution_id ,
1800 pr_new.rcv_transaction_id,
1801 -- v_price_var_accnt,
1802 -- v_tax_variance_inv_cur,
1803 -- v_tax_variance_fun_cur,
1804 pr_new.matched_uom_lookup_code,
1805 jai_rec.invoice_line_number,
1806 pr_new.org_id,
1807 pr_new.charge_applicable_to_dist_id/*need to check this,Invoice distribution to which 100% of current charge is applied */
1808 , pr_new.project_id
1809 , pr_new.task_id
1810 , pr_new.expenditure_type
1811 , pr_new.expenditure_item_date
1812 , pr_new.expenditure_organization_id
1813 , pr_new.project_accounting_context
1814 , pr_new.pa_addition_flag
1815 ,pr_new.distribution_class
1816 ,jai_rec.invoice_distribution_id
1817 );
1818
1819
1820 --INSERT THE -VE VARIANCE AMOUNT TO ACCRUAL ACCOUNT ,SUCH THAT THE ACCRUAL amount will be of the PO tax amt and is in sync with base
1821 ln_invoice_distribution_id :=null;
1822
1823 open c_get_invoice_distribution;
1824 fetch c_get_invoice_distribution into ln_invoice_distribution_id;
1825 close c_get_invoice_distribution;
1826 --jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ipv before get_dist_no ');
1827
1828
1829
1830 open c_get_accrual_acct (pr_new.invoice_id,jai_rec.parent_invoice_distribution_id);
1831 fetch c_get_accrual_acct into ln_accrual_acct;
1832 close c_get_accrual_acct;
1833
1834 ln_distribution_lineno :=nvl(ln_distribution_lineno,0)+1;
1835
1836 --insert the variance amount with line_type_lookup_code as MISCELLANEOUS
1837 INSERT INTO ap_invoice_distributions_all
1838 (
1839 accounting_date,
1840 accrual_posted_flag,
1841 assets_addition_flag,
1842 assets_tracking_flag,
1843 cash_posted_flag,
1844 distribution_line_number,
1845 dist_code_combination_id,
1846 invoice_id,
1847 last_updated_by,
1848 last_update_date,
1849 line_type_lookup_code,
1850 period_name,
1851 set_of_books_id ,
1852 amount,
1853 base_amount,
1854 batch_id,
1855 created_by,
1856 creation_date,
1857 description,
1858 exchange_rate_variance,
1859 last_update_login,
1860 match_status_flag,
1861 posted_flag,
1862 rate_var_code_combination_id ,
1863 reversal_flag ,
1864 program_application_id,
1865 program_id,
1866 program_update_date,
1867 accts_pay_code_combination_id,
1868 invoice_distribution_id,
1869 quantity_invoiced,
1870 po_distribution_id ,
1871 rcv_transaction_id,
1872 --price_var_code_combination_id,/*no longer used in R12*/
1873 --invoice_price_variance,/*no longer used in R12*/
1874 -- base_invoice_price_variance,/*no longer used in R12*/
1875 matched_uom_lookup_code
1876 ,invoice_line_number
1877 ,org_id
1878 ,charge_applicable_to_dist_id
1879 , project_id
1880 , task_id
1881 , expenditure_type
1882 , expenditure_item_date
1883 , expenditure_organization_id
1884 , project_accounting_context
1885 , pa_addition_flag
1886 ,distribution_class
1887 ,related_id /*13422310*/
1888 )
1889 VALUES
1890 (
1891 pr_new.accounting_date,
1892 pr_new.accrual_posted_flag,
1893 pr_new.assets_addition_flag,
1894 'N',/*need to check the importance of assets_tracking_flag*/
1895 'N',
1896 ln_distribution_lineno,
1897 ln_accrual_acct,
1898 pr_new.invoice_id,
1899 ln_user_id,
1900 sysdate,
1901 'MISCELLANEOUS',
1902 pr_new.period_name,
1903 pr_new.set_of_books_id ,
1904 0 ,
1908 sysdate,
1905 -ln_tax_excvariance_amt,
1906 pr_new.batch_id,
1907 ln_user_id,
1909 lv_tax_name,
1910 null,
1911 ln_login_id,
1912 pr_new.match_status_flag ,
1913 'N',
1914 NULL,
1915 pr_new.reversal_flag,
1916 pr_new.program_application_id,
1917 pr_new.program_id,
1918 pr_new.program_update_date,
1919 pr_new.accts_pay_code_combination_id,
1920 ln_invoice_distribution_id,
1921 null,
1922 pr_new.po_distribution_id ,
1923 pr_new.rcv_transaction_id,
1924 -- v_price_var_accnt,
1925 -- v_tax_variance_inv_cur,
1926 -- v_tax_variance_fun_cur,
1927 pr_new.matched_uom_lookup_code,
1928 jai_rec.invoice_line_number,
1929 pr_new.org_id,
1930 pr_new.charge_applicable_to_dist_id/*need to check this,Invoice distribution to which 100% of current charge is applied */
1931 , pr_new.project_id
1932 , pr_new.task_id
1933 , pr_new.expenditure_type
1934 , pr_new.expenditure_item_date
1935 , pr_new.expenditure_organization_id
1936 , pr_new.project_accounting_context
1937 , pr_new.pa_addition_flag
1938 ,pr_new.distribution_class
1939 ,jai_rec.invoice_distribution_id
1940 );
1941
1942
1943
1944 commit;
1945
1946 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ERV after update');
1947
1948 end if;--if nvl(ln_tax_variance_amt,0)<>0
1949
1950 end loop;
1951
1952
1953
1954
1955 end if;--if pr_new.line_type_lookup_code ='IPV'
1956
1957
1958 end process_ipv;
1959
1960
1961
1962
1963
1964 BEGIN
1965 pv_return_code := jai_constants.successful ;
1966 /*------------------------------------------------------------------------------------------
1967 FILENAME: ja_in_ap_aida_before_trg.sql
1968
1969 CHANGE HISTORY:
1970 S.No Date Author and Details
1971
1972 1. 25/03/2004 Aparajita. Bug # 4088186. TDS Clean up. Version#115.0
1973
1974 This is the only trigger introduced for all the before event
1975 on the table on which this is based.
1976
1977 2. 11/05/2005 rchandan for bug#4333488. Version 116.1
1978 The Invoice Distribution DFF is eliminated and a new global DFF is used to
1979 maintain the functionality. From now the TDS tax, WCT tax and ESSI will not
1980 be populated in the attribute columns of ap_invoice_distributions_all table
1981 instead these will be populated in the global attribute columns. So the code changes are
1982 made accordingly.
1983
1984 3. 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
1985 Changed AP Lookup code from 'TDS' to 'INDIA TDS'
1986
1987 4. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
1988 DB Entity as required for CASE COMPLAINCE. Version 116.2
1989
1990 5 13-Jun-2005 File Version: 116.3
1991 Ramananda for bug#4428980. Removal of SQL LITERALs is done
1992
1993 6. 06-jul-2005 AP lines change.
1994 7. 18/04/2007 bduvarag for the Bug#5989740, file version 120.2
1995 Forward porting the changes done in 11i bug#5907436
1996 8. 21/10/2008 Bug 5640993 FP of 5553489. Added by JMEENA
1997 Invoked processing of old prepayment application using variable lv_process_old_trxn. Processing of TDS prepayment application for transactions created
1998 prior to upgrade to TDS Threshold will be done only at the time of application and not during validation.
1999
2000 9. 18-Jul-2009 Bug 8641199
2001 Need to update match_status_flag in jai_ap_tds_inv_taxes with the match_status_flag of
2002 ap_invoice_distributions_all
2003
2004 8. 11-Jan-2010 Xiao Lv for bug#7347508, related 11i bug#6417285
2005 Added new conditions to check if either TDS, WCT or ESSI taxes are getting modified or inserted
2006 after the invoice has been validated. In such cases an error message is thrown stating that once an
2007 invoice is validated, there should not be any modifications made to these three taxes.
2008
2009 Added the new condition 'nvl(pr_new.global_attribute_category, 'JA.IN.APXINWKB.DISTRIBUTIONS' ) =
2010 'JA.IN.APXINWKB.DISTRIBUTIONS'' to make sure the checks are made only when the context is
2011 'JA.IN.APXINWKB.DISTRIBUTIONS'
2012
2013 9. 14-Jan-2010 Xiao Lv for bug#7154864, related 11i bug#6767347
2014 Commented two if condition section code.
2015
2016 Dependency:
2017 ----------
2018
2019 Sl No. Bug Dependent on
2020 Bug/Patch set Details
2021 -------------------------------------------------------------------------------------------------
2022 1 3924692 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
2023 4033992.
2024 ja_in_util_pkg_s.sql 115.0
2025 ja_in_util_pkg_b.sql 115.0
2026
2027 2. 4088186 4088186 Call to Package jai_ap_tds_tax_defaultation.
2028 -------------------------------------------------------------------------------------------------
2029
2030 8. 17/Sep/2007 Bug 5911913. Added by vkantamn version 120.6
2031 Added two parameters
2032 (1) p_old_input_dff_value_wct
2033 (2) p_old_input_dff_value_essi
2034 in the call to procedure process_invoice.
2035
2039 jai_ap_tds_dflt.plb 120.3
2036 Dependencies:
2037 -------------
2038 jai_ap_tds_dflt.pls 120.1
2040 jai_ap_ida_t.plb 120.5
2041
2042
2043 9. 18-Oct-07 Bug 6493858, File version 120.8
2044 Moved the validation done for invoice cancellation process from jai_ap_ia_t.plb.
2045 Through this, changes done for bug 6318997 have been forward ported to R12 code.
2046
2047 10. 21-Dec-2007 Sanjikum for Bug#6708042, Version 120.10
2048 Obsoleted the changes done for verion 120.6
2049
2050 11. 05-Dec-2008 Bgowrava for Bug#7433241, file Version 120.10.12010000.4
2051 moved the end if condition in the code of intercepting the validate event. this enables that only the call to the
2052 process_tds_at_inv_validate procedure is dependent on the value of variable lv_is_invoice_validated. Thus enabling
2053 the code for prepayment to execute when a prepayment application or unapplication to execute when the prepayment is
2054 applied before validation of the std invoice.
2055
2056 12. 01-Apr-2010 Bgowrava for bug#9457695, file version 120.10.12010000.15
2057 Added the code to populate global_attribute1 and global attribute context in ap_invoice_distributions table
2058 at the place after the validation procedure.
2059
2060 13. 24-12-2010 amandali for Bug 10430662
2061 Forward ported the changes made in bug 7328147,7328147
2062 +Added code for defaulting WCT taxes from a PO or Receipt when a PO matched invoice or Receipt
2063 matched invoice is created.
2064 +Need to update WCT Taxes to the Invoices when PO/Receipt matched invoice is created
2065 This must be done while inserting and updating
2066
2067 14. 3-feb-2011 amandali for bug 11709107
2068 Forward ported the changes made in bug 9951744
2069 Description: Default Tax Codes were not shown in the GDF after saving the distribution form
2070 Fix: GLOBAL_ATTRIBUTE1 and GLOBAL_ATTRIBUTE_CATEGORY are saved in Before Insert Trigger
2071 To prevent usability issues for customers already using the previous solution, added to Client
2072 Extension to restrict the above behavior based on Customer preference
2073
2074 ------------------------------------------------------------------------------------------ */
2075
2076 /*CHANGE HISTORY:
2077 S.No Bug Date Author and Details
2078 1 6493858 4-DEC-2007 Added by Nitin Prashar, for cancelation of Base Invoice*/
2079
2080
2081 if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
2082 ln_org_id := pr_new.org_id;
2083 ln_set_of_books_id := pr_new.set_of_books_id;
2084 elsif pv_action = jai_constants.deleting then
2085 ln_org_id := pr_old.org_id;
2086 ln_set_of_books_id := pr_old.set_of_books_id;
2087 end if;
2088
2089 --START additions by vkaranam for bug#13422310
2090 /*
2091 MATCH_STATUS_FLAG VARCHAR2 (1)
2092 Validation status for the distribution.
2093 This flag is used to derive Invoice Status.
2094 Possible values for this flag: Null or N for invoice distributions that are not tested and validated,
2095 T for distributions that have been tested,
2096 A for distributions that have been tested and validated,
2097 S for distributions that have been selected for validation
2098 */
2099
2100 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud',' pv_action '||pv_action||' nvl(pr_old.match_status_flag,Q) '||nvl(pr_old.match_status_flag,'Q')||' nvl(pr_new.match_status_flag,Q) '||nvl(pr_new.match_status_flag,'Q'));
2101 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','pr_new.line_type_lookup_code '||pr_new.line_type_lookup_code);
2102 if pv_action = jai_constants.inserting
2103 AND ( nvl(pr_old.match_status_flag,'Q')<> nvl(pr_new.match_status_flag,'Q') and nvl(pr_new.match_status_flag,'Q') IN ('N'))
2104 AND ( pr_new.line_type_lookup_code ='IPV' or pr_new.line_type_lookup_code ='ERV' )
2105 then
2106 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','before call to process_ipv');
2107 process_ipv;
2108 end if;
2109
2110 --end additions for bug#13422310
2111
2112 /* TDS Tax Defaultation Functionality Bug # 4088186 */
2113 open c_ap_invoices_all;
2114 fetch c_ap_invoices_all into c_rec_ap_invoices_all;
2115 close c_ap_invoices_all;
2116
2117 /* Bug 5640993 FP of 5553489. Added by JMEENA
2118 * Initialized the variable lv_process_old_trxn to 'Y' so that
2119 * prepayments will be processed during insert event.
2120 */
2121 lv_process_old_trxn := 'Y';
2122
2123 /*Bug 5989740 bduvarag start*/
2124 -- Rmoved by Jia for FP Bug#8656402, Begin
2125 ------------------------------------------------------------------------
2126 /*
2127 IF JAI_AP_TDS_GENERATION_pkg.gn_tds_rounding_factor IS NULL
2128 OR JAI_AP_TDS_GENERATION_pkg.gd_tds_rounding_effective_date IS NULL
2129 THEN
2130 */
2131 ------------------------------------------------------------------------
2132 -- Rmoved by Jia for FP Bug#8656402, End
2133 OPEN c_get_rnd_factor (pr_new.org_id, pr_new.accounting_date); -- Added a parameter pr_new.accounting_date for cursor by Jia for FP Bug#8656402
2134 FETCH c_get_rnd_factor into
2135 JAI_AP_TDS_GENERATION_pkg.gn_tds_rounding_factor,
2136 JAI_AP_TDS_GENERATION_pkg.gd_tds_rounding_effective_date;
2137 CLOSE c_get_rnd_factor ;
2138 --END IF; -- Rmoved by Jia for FP Bug#8656402
2139
2140
2141 /*Bug 5989740 bduvarag end*/
2142
2143 --Added by Xiao Lv for bug#7347508 on 11-Jan-2010, begin
2144 if pv_action = jai_constants.updating
2145 then
2146 IF nvl(pr_new.global_attribute_category, 'JA.IN.APXINWKB.DISTRIBUTIONS' ) = 'JA.IN.APXINWKB.DISTRIBUTIONS'
2147 AND ( nvl(pr_old.global_attribute1, 0) <> nvl(pr_new.global_attribute1, 0)
2151 AND pr_old.match_status_flag = 'A'
2148 OR nvl(pr_old.global_attribute2, 0) <> nvl(pr_new.global_attribute2, 0)
2149 OR nvl(pr_old.global_attribute3, 0) <> nvl(pr_new.global_attribute3, 0)
2150 )
2152 THEN
2153 raise_application_error(-20036,
2154 'Error - Cannot Modify or Insert the values for TDS, WCT or ESSI tax id once Invoice is validated ');
2155 end if;
2156 end if;
2157 --Added by Xiao Lv for bug#7347508 on 11-Jan-2010, end
2158
2159
2160 if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
2161
2162 if nvl(pr_new.global_attribute_category, 'JA.IN.APXINWKB.DISTRIBUTIONS' ) = 'JA.IN.APXINWKB.DISTRIBUTIONS' and -- rchandan for bug#4333488
2163 pr_new.line_type_lookup_code <> 'PREPAY' and
2164 c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
2165 c_rec_ap_invoices_all.cancelled_date is null
2166 then
2167
2168 jai_ap_tds_tax_defaultation.process_invoice
2169 (
2170 p_invoice_id => pr_new.invoice_id,
2171 p_invoice_line_number => pr_new.invoice_line_number , /* AP Lines*/
2172 p_invoice_distribution_id => pr_new.invoice_distribution_id,
2173 p_line_type_lookup_code => pr_new.line_type_lookup_code,
2174 p_distribution_line_number => pr_new.distribution_line_number,
2175 p_parent_reversal_id => pr_new.parent_reversal_id,
2176 p_reversal_flag => pr_new.reversal_flag,
2177 p_amount => pr_new.amount,
2178 p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2179 p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2180 p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2181 p_po_distribution_id => pr_new.po_distribution_id,
2182 p_rcv_transaction_id => pr_new.rcv_transaction_id,
2183 p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2184 p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2185 p_input_dff_value_tds => pr_new.global_attribute1, -- rchandan for bug#4333488
2186 p_input_dff_value_wct => pr_new.global_attribute2, -- rchandan for bug#4333488
2187 p_old_input_dff_value_wct => pr_old.global_attribute2, -- Added by Bgowrava for Bug 5911913
2188 p_input_dff_value_essi => pr_new.global_attribute3, -- rchandan for bug#4333488
2189 p_old_input_dff_value_essi => pr_old.global_attribute3, -- Added by Bgowrava for Bug 5911913
2190 p_org_id => pr_new.org_id,
2191 p_accounting_date => pr_new.accounting_date,
2192 p_call_from => 'ja_in_ap_aida_after_trg',
2193 p_final_tds_tax_id => ln_final_tds_tax_id,
2194 p_process_flag => lv_process_flag,
2195 p_process_message => lv_process_message,
2196 p_codepath => lv_codepath
2197 );
2198
2199 if nvl(lv_process_flag, 'N') = 'E' then
2200 /* raise_application_error(-20004,
2201 '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 :=
2202 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_codepath ; return ;
2203 end if;
2204
2205 /*Bug 8641199 - Start*/
2206 jai_ap_tds_generation_pkg.status_update_chk_validate
2207 (
2208 p_invoice_id => pr_new.invoice_id,
2209 p_invoice_distribution_id => pr_new.invoice_distribution_id,
2210 p_match_status_flag => pr_new.match_status_flag,
2211 p_is_invoice_validated => lv_is_invoice_validated,
2212 p_process_flag => lv_process_flag,
2213 p_process_message => lv_process_message,
2214 p_codepath => lv_codepath
2215 );
2216 /*Bug 8641199 - End*/
2217 /*Default Tax Codes to be shown on Save(in GDF) only if the Client Extension in jai_populate_attribute package is set to Y*/
2218 IF nvl(jai_populate_attribute.default_value_check, 'N') = 'Y' THEN
2219 /*Uncommenting the following code for 11709107 by amandali*/
2220 if pr_new.global_attribute1 is null and ln_final_tds_tax_id is not null then --rchandan for bug#4333488
2221 pr_new.global_attribute1 := to_char(ln_final_tds_tax_id);--rchandan for bug#4333488
2222 end if;
2223
2224 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
2225 pr_new.global_attribute_category := 'JA.IN.APXINWKB.DISTRIBUTIONS'; --rchandan for bug#4333488
2226 end if; --Commented by xiao for bug#7154864
2227 END IF;
2228 /*End -Bug 11709107*/
2229 end if; /* Additional conditions */
2230
2231 end if; /*inserting or updating */
2232
2233 /* TDS Tax Defaultation Functionality Bug # 4088186 */
2234 /*Bug 12630283 - Start*/
2235 /*Data is inserted into JAI_AP_TDS_PREPAYMENTS before Validation. This results in incorrect TDS getting calculated
2236 On validation of Invoice the match status flag of PREPAY line is set to 'A' first resulting in JAI_AP_TDS_PREPAYMENTS
2237 getting inserted before the Item Line's match status flag is set to A. Only if all the distraibutions match status flag
2238 is set to A or T process_tds_at_inv_validate shall be called. If process_tds_at_inv_validate is called after insertion
2239 of data in JAI_AP_TDS_PREPAYMENTS it results in incorrect TDS deduction. Hence moved the code to insert JAI_AP_TDS_PREPAYMENTS
2243 IF pr_new.line_type_lookup_code = 'PREPAY' and
2240 after validation of Invoice.*/
2241 --To handle the condition, if there are PP applications/Unapplications, before the SI is validated
2242 if pv_action = jai_constants.updating then
2244 nvl(pr_old.match_status_flag, 'Q') <> nvl(pr_new.match_status_flag, 'Q') and pr_new.match_status_flag = 'A' /* Added for Bug #16028459 */
2245 THEN
2246 lv_prepay_flag := NULL;
2247 --Apply Scenario
2248 IF NVL(pr_new.amount,0) < 0 THEN
2249
2250 OPEN c_check_prepayment_apply;
2251 FETCH c_check_prepayment_apply INTO lv_prepay_flag;
2252 CLOSE c_check_prepayment_apply;
2253
2254 --Unapply Scenario
2255 ELSIF NVL(pr_new.amount,0) > 0 THEN
2256
2257 OPEN c_check_prepayment_unapply;
2258 FETCH c_check_prepayment_unapply INTO lv_prepay_flag;
2259 CLOSE c_check_prepayment_unapply;
2260
2261 END IF;
2262 /*Verify if Invoice is processed by process_tds_at_inv_validate before processing Prepayments*/
2263 SELECT max(process_status) INTO ln_processed
2264 FROM jai_ap_tds_inv_taxes
2265 WHERE invoice_id = pr_new.invoice_id;
2266 --should be run, only if prepayment application/unapplication is not already processed
2267 IF lv_prepay_flag IS NULL AND ln_processed = 'P' THEN
2268 lv_process_old_trxn := 'N';
2269 process_prepayment(cp_event => 'UPDATE');
2270 END IF;
2271
2272 END IF;
2273 end if;
2274 /*Bug 12630283 - End*/
2275 /* Intercepting Validate event */
2276 if pv_action = jai_constants.updating then
2277
2278 if nvl(pr_old.match_status_flag, 'Q') <> nvl(pr_new.match_status_flag, 'Q') and
2279 pr_new.match_status_flag = 'A' and
2280 c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
2281 c_rec_ap_invoices_all.cancelled_date is null
2282 then
2283
2284 /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
2285 Don't proceed for TDS invoice creation if the invoice type
2286 is either 'CREDIT' or 'DEBIT'
2287 */
2288
2289 If c_rec_ap_invoices_all.invoice_type_lookup_code
2290 IN ('CREDIT', 'DEBIT')
2291 Then
2292 return;
2293 End if;
2294
2295 lv_codepath := null;
2296
2297 jai_ap_tds_generation_pkg.status_update_chk_validate
2298 (
2299 p_invoice_id => pr_new.invoice_id,
2300 p_invoice_line_number => pr_new.invoice_line_number, /* AP Lines*/
2301 p_invoice_distribution_id => pr_new.invoice_distribution_id,
2302 p_match_status_flag => pr_new.match_status_flag,
2303 p_is_invoice_validated => lv_is_invoice_validated,
2304 p_process_flag => lv_process_flag,
2305 p_process_message => lv_process_message,
2306 p_codepath => lv_codepath
2307 );
2308
2309 if nvl(lv_process_flag, 'N') = 'E' then
2310 /* raise_application_error(-20005,
2311 '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 :=
2312 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ; return ;
2313 end if;
2314
2315 if lv_is_invoice_validated = 'Y' then
2316
2317 lv_codepath := null;
2318 jai_ap_tds_generation_pkg.process_tds_at_inv_validate
2319 (
2320 p_invoice_id => pr_new.invoice_id,
2321 p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2322 p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2323 p_accounting_date => pr_new.accounting_date,
2324 p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2325 p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2326 p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2327 p_org_id => pr_new.org_id,
2328 p_call_from => 'ja_in_ap_aida_before_trg',
2329 p_creation_date => pr_new.creation_date,/*Bug 5989740 bduvarag*/
2330 p_process_flag => lv_process_flag,
2331 p_process_message => lv_process_message,
2332 p_codepath => lv_codepath
2333 );
2334
2335 if nvl(lv_process_flag, 'N') = 'E' then
2336 /* raise_application_error(-20006,
2337 '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 :=
2338 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ; return ;
2339 end if;
2340
2341 end if; --moved this from below for Bug#7433241
2342
2343 -- end if; --commented by bgowrava for Bug#7433241, moved this end if to a different place at the top.
2344 /*START, Added by Bgowrava for Bug#9457695*/
2345 if pr_new.global_attribute1 is null and ln_final_tds_tax_id is not null then
2346 pr_new.global_attribute1 := to_char(ln_final_tds_tax_id);
2347 end if;
2348
2349 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
2350 pr_new.global_attribute_category := 'JA.IN.APXINWKB.DISTRIBUTIONS';
2351 end if;
2352 /*END, Added by Bgowrava for Bug#9457695*/
2353
2354 end if; /* pr_old.match_status_flag <> pr_new.match_status_flag */
2355 end if; /* updating */
2356 /* Intercepting Validate event */
2357
2358
2362 if (pr_new.global_attribute1 is not null or pr_new.global_attribute2 is not null or pr_new.global_attribute3 is not null) and (pr_new.global_attribute_category is null) then
2359 if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
2360
2361 /*START, by amandali for Bug#10430662*/
2363 pr_new.global_attribute_category := 'JA.IN.APXINWKB.DISTRIBUTIONS';
2364 end if;
2365 if (pr_new.po_distribution_id is not null and pr_new.global_attribute2 is null ) then
2366
2367 open c_get_shipment_line_id(pr_new.rcv_transaction_id);
2368 fetch c_get_shipment_line_id into v_shipment_line_id;
2369 close c_get_shipment_line_id;
2370
2371 if v_shipment_line_id is not null then
2372
2373 open c_receipt_wct_tax(v_shipment_line_id);
2374 fetch c_receipt_wct_tax into v_receipt_wct_tax_id;
2375 close c_receipt_wct_tax;
2376 end if;
2377
2378 if v_receipt_wct_tax_id is not null then
2379 pr_new.global_attribute2 := v_receipt_wct_tax_id;
2380 pr_new.global_attribute_category := 'JA.IN.APXINWKB.DISTRIBUTIONS';
2381 end if;
2382
2383 if pr_new.global_attribute2 is null then
2384 open from_po_distributions(pr_new.po_distribution_id);
2385 fetch from_po_distributions into from_po_distributions_rec;
2386 close from_po_distributions;
2387
2388 open from_line_locations(from_po_distributions_rec.line_location_id, from_po_distributions_rec.po_line_id );
2389 fetch from_line_locations into from_line_locations_rec;
2390 close from_line_locations;
2391
2392 open c_line_location_wct_taxes(from_line_locations_rec.line_focus_id);
2393 fetch c_line_location_wct_taxes into c_line_location_wct_taxes_rec;
2394 close c_line_location_wct_taxes;
2395
2396 pr_new.global_attribute2 := c_line_location_wct_taxes_rec.tax_id ;
2397 pr_new.global_attribute_category := 'JA.IN.APXINWKB.DISTRIBUTIONS';
2398 end if;
2399
2400 end if;
2401 end if;
2402 /*END, by amandali for Bug#10430662*/
2403 /* Prepayment functionality */
2404 if pv_action = jai_constants.inserting then
2405 if pr_new.line_type_lookup_code = 'PREPAY' and
2406 c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
2407 c_rec_ap_invoices_all.cancelled_date is null
2408 then
2409
2410 /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
2411 Don't proceed for TDS invoice creation if the invoice type
2412 is either 'CREDIT' or 'DEBIT'
2413 */
2414
2415 If c_rec_ap_invoices_all.invoice_type_lookup_code
2416 IN ('CREDIT', 'DEBIT')
2417 Then
2418 return;
2419 End if;
2420
2421
2422 --Start Added by Sanjikum for Bug#5131075(4722011)
2423 --If SI is not validated at this stage, then return
2424
2425 lv_codepath := null;
2426
2427 jai_ap_tds_generation_pkg.status_update_chk_validate
2428 (
2429 p_invoice_id => pr_new.invoice_id,
2430 /* p_invoice_line_id => null, Future use AP Lines
2431 /*p_invoice_distribution_id => null,*/
2432 p_match_status_flag => pr_new.match_status_flag, --Changed by Sanjikum for Bug#5131075(4722011)
2433 p_is_invoice_validated => lv_is_invoice_validated,
2434 p_process_flag => lv_process_flag,
2435 p_process_message => lv_process_message,
2436 p_codepath => lv_codepath
2437 );
2438
2439 if nvl(lv_process_flag, 'N') = 'E' then
2440 raise_application_error(-20005,
2441 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
2442 end if;
2443
2444 IF lv_is_invoice_validated = 'N' THEN
2445 RETURN;
2446 END IF;
2447
2448 process_prepayment(cp_event => 'INSERT'); --Added parameter cp_event for Bug 8431516
2449
2450 --Removed the existing code for Handling PP and moved it into inline procedure process_prepayment
2451 --End Added by Sanjikum for Bug#5131075(4722011)
2452
2453
2454 /*--Check if the prepayment transaction should be processed by the code before
2455 --TDS Clean up or after TDS clean up.
2456
2457 --if SI is created in the new regime and also the Prepay is created in the new regime,
2458 --then code should invoke the new regime or else
2459 --old concurrent shd be invoked
2460
2461
2462 --Check for SI
2463 jai_ap_tds_tax_defaultation.check_old_transaction
2464 (
2465 p_invoice_id => pr_new.invoice_id,
2466 p_new_transaction => lv_new_transaction_si
2467 );
2468
2469 --Check for Prepayment
2470 jai_ap_tds_tax_defaultation.check_old_transaction
2471 (
2472 p_invoice_distribution_id => pr_new.prepay_distribution_id,
2473 p_new_transaction => lv_new_transaction_pp
2474 );
2475
2476 if lv_new_transaction_si = 'Y' and lv_new_transaction_pp = 'Y' then
2477
2478 lv_codepath := null;
2479
2480 jai_ap_tds_prepayments_pkg.process_prepayment
2481 (
2482 p_invoice_id => pr_new.invoice_id,
2483 p_invoice_distribution_id => pr_new.invoice_distribution_id,
2484 p_prepay_distribution_id => pr_new.prepay_distribution_id,
2485 p_parent_reversal_id => pr_new.parent_reversal_id,
2486 p_prepay_amount => pr_new.amount,
2487 p_vendor_id => c_rec_ap_invoices_all.vendor_id,
2488 p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
2489 p_accounting_date => pr_new.accounting_date,
2490 p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
2491 p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
2492 p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
2493 p_org_id => pr_new.org_id,
2494 p_process_flag => lv_process_flag,
2495 p_process_message => lv_process_message,
2496 p_codepath => lv_codepath
2497 );
2498
2499 if nvl(lv_process_flag, 'N') = 'E' then
2500 -- raise_application_error(-20007,
2501 --'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
2502 pv_return_code := jai_constants.expected_error ;
2503 pv_return_message := 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ;
2504 return ;
2505 end if;
2506
2507 else
2508 --
2509 --|| Invoke the old regime functionality
2510 --
2511 jai_ap_tds_prepayments_pkg.process_old_transaction
2512 (
2513 p_invoice_id => pr_new.invoice_id,
2514 p_invoice_distribution_id => pr_new.invoice_distribution_id,
2515 p_prepay_distribution_id => pr_new.prepay_distribution_id,
2516 p_amount => pr_new.amount,
2517 p_last_updated_by => pr_new.last_updated_by,
2518 p_last_update_date => pr_new.last_update_date,
2519 p_created_by => pr_new.created_by,
2520 p_creation_date => pr_new.creation_date,
2521 p_org_id => pr_new.org_id,
2522 p_process_flag => lv_process_flag,
2523 p_process_message => lv_process_message
2524 );
2525
2526 if nvl(lv_process_flag, 'N') = 'E' then
2527 --raise_application_error(-20008,
2528 --'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
2529 pv_return_code := jai_constants.expected_error ;
2530 pv_return_message := 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ;
2531 return ;
2532 end if;
2533
2534
2535 end if; --Transactions in new regime*/
2536
2537
2538 end if; /* Prepayment functionality */
2539
2540 /*Code Started start changes for bug 6493858 - cancellation functionality*/
2541 IF pr_new.cancellation_flag = 'Y' AND c_rec_ap_invoices_all.source <> 'INDIA TDS'
2542 THEN
2543 --commented the following for bug#9052839
2544 /*OPEN c_tds_invoice_id(pr_new.invoice_id);
2545 FETCH c_tds_invoice_id INTO r_tds_invoice_id;
2546 CLOSE c_tds_invoice_id;*/
2547
2548 --added the FOR loop for bug#9052839
2549 FOR r_tds_invoice_id IN c_tds_invoice_id (pr_new.invoice_id)
2550 LOOP
2551 IF r_tds_invoice_id.invoice_id IS NOT NULL THEN
2552 lv_invoice_payment_status := get_invoice_payment_status(r_tds_invoice_id.invoice_id);
2553
2554 IF NVL(lv_invoice_payment_status,'N') <> 'N' THEN
2555 raise_application_error(-20011,
2556 'Invoice to TDS Authority - '||r_tds_invoice_id.invoice_num||' is already paid. Current invoice can''t be cancelled' );
2557 END IF;
2558 END IF;
2559 END LOOP;
2560 END IF;
2561 /*end changes for bug 6493858 -Code Ended */
2562
2563 /* Bug 8330522. Added by Lakshmi Gopalsami */
2564 IF pr_new.cancellation_flag = 'Y' AND c_rec_ap_invoices_all.source = 'INDIA TDS'
2565 AND c_rec_ap_invoices_all.vendor_type_lookup_code <> 'INDIA TDS AUTHORITY'
2566 THEN
2567 --commented the following for bug#9052839
2568 /*OPEN c_tds_invoice_id1(pr_new.invoice_id);
2569 FETCH c_tds_invoice_id1 INTO r_tds_invoice_id;
2570 CLOSE c_tds_invoice_id1;*/
2571
2572 --added the FOR loop for bug#9052839
2573 FOR r_tds_invoice_id IN c_tds_invoice_id1 (pr_new.invoice_id)
2574 LOOP
2575 IF r_tds_invoice_id.invoice_id IS NOT NULL THEN
2576 lv_invoice_payment_status := get_invoice_payment_status(
2577 r_tds_invoice_id.invoice_id);
2578
2579 IF NVL(lv_invoice_payment_status,'N') <> 'N' THEN
2580 raise_application_error(-20011,
2581 'Invoice to TDS Authority - '||r_tds_invoice_id.invoice_num||' is already paid. TDS Credit memo to supplier invoice can''t be cancelled' );
2582 END IF;
2583 END IF;
2584 END LOOP;
2585 END IF;
2586 /*End for bug 8330522*/
2587
2588 end if; /* inserting */
2589
2590 if pv_action = jai_constants.deleting then
2591
2592 jai_ap_tds_tax_defaultation.process_delete
2593 (
2594 p_invoice_id => pr_old.invoice_id,
2595 p_invoice_line_number => pr_new.invoice_line_number, /* AP Lines*/
2596 p_invoice_distribution_id => pr_old.invoice_distribution_id,
2597 p_process_flag => lv_process_flag,
2598 P_process_message => lv_process_message
2599 );
2600
2601 if nvl(lv_process_flag, 'N') = 'E' then
2602 /* raise_application_error(-20009,
2603 '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 :=
2604 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ; return ;
2605 end if;
2606
2607 end if; /* Deleting */
2608
2609
2610 exception
2611 when others then
2612 --raise_application_error(-20010, 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || sqlerrm);
2613 Pv_return_code := jai_constants.unexpected_error;
2614 Pv_return_message := 'Encountered an error in JAI_AP_IDA_TRIGGER_PKG.BRIUD_T1 ' ||
2615 'Error on ap_invoice_distributions_all : ' || substr(sqlerrm,1,1900);
2616
2617 END BRIUD_T1 ;
2618
2619 END JAI_AP_IDA_TRIGGER_PKG ;
[Home] [Help]