1 PACKAGE BODY JAI_AP_IDA_TRIGGER_DTC_PKG AS
2 /* $Header: jai_ap_ida_dtc_t.plb 120.0.12020000.2 2013/03/19 00:21:58 vkaranam noship $ */
3 /*
4 REM +======================================================================+
5 REM NAME BRIUD_DTC_T1
6 REM
7 REM DESCRIPTION Called from trigger JAI_AP_IDA_BRIUD_DTC_T1
8 REM
9 REM NOTES Refers to old trigger JAI_AP_IDA_BRIUD_T1
10 REM
11 REM +======================================================================+
12 */
13 PROCEDURE BRIUD_DTC_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
14 lv_process_flag varchar2(20);
15 lv_process_message varchar2(200);
16 ln_final_tds_tax_id JAI_CMN_TAXES_ALL.tax_id%type;
17
18 cursor c_ap_invoices_all is
19 select ai.vendor_id,
20 ai.vendor_site_id,
21 ai.invoice_currency_code,
22 ai.exchange_rate,
23 ai.set_of_books_id,
24 ai.source,
25 ai.cancelled_date,
26 -- Bug#5131075(4683207). Added by Lakshmi Gopalsami
27 ai.invoice_type_lookup_code,
28 ai.invoice_num, /*added for bug 6493858 ref-6318997*/
29 pv.vendor_type_lookup_code /* Bug 8330522. Added by Lakshmi Gopalsami */
30 from ap_invoices_all ai, po_vendors pv
31 where ai.invoice_id = pr_new.invoice_id
32 and ai.vendor_id = pv.vendor_id;
33
34 --Added by Sanjikum for Bug#5131075(4722011)
35 CURSOR c_check_prepayment_apply IS
36 SELECT '1'
37 FROM jai_ap_tds_prepayments
38 WHERE invoice_distribution_id_prepay = pr_new.invoice_distribution_id;
39
40 CURSOR c_check_prepayment_unapply IS
41 SELECT '1'
42 FROM jai_ap_tds_prepayments
43 WHERE invoice_distribution_id_prepay = pr_new.parent_reversal_id
44 AND unapply_flag = 'Y';
45
46 lv_prepay_flag VARCHAR2(1);
47 --End addition by Sanjikum for Bug#5131075(4722011)
48
49 c_rec_ap_invoices_all c_ap_invoices_all%rowtype;
50 lv_codepath VARCHAR2(1996);
51 lv_is_invoice_validated varchar2(1);
52 lv_new_transaction_si varchar2(1);
53 lv_new_transaction_pp varchar2(1);
54 ln_org_id ap_invoices_all.org_id%type;
55 ln_set_of_books_id ap_invoices_all.set_of_books_id%type;
56 /* bug 5640993 FP of 5553489. Added by JMEENA
57 * Created variable which decides whether the prepayment
58 * created prior to upgrade(TDS-Threshold) has to be processed
59 */
60 lv_process_old_trxn VARCHAR2(1);
61
62 /*Bug 5989740 bduvarag start*/
63 /* Commented for bug# 6459941
64 CURSOR c_get_rnd_factor (p_org_id IN NUMBER ) IS
65 SELECT NVL(tds_rounding_factor,0) , tds_rounding_start_date
66 FROM JAI_AP_TDS_YEARS
67 WHERE legal_entity_id IN (SELECT legal_entity_id
68 FROM hr_operating_units
69 where organization_id = p_org_id
70 )
71 AND trunc (sysdate) between start_date and end_date; --added by ssawant for bug 6074957
72 Bug 5989740 bduvarag end */
73
74 /* Added for bug# 6459941 */
75 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
76 SELECT
77 nvl(tds_rounding_factor,0) ,
78 tds_rounding_start_date
79 FROM
80 jai_ap_tds_years
81 WHERE
82 legal_entity_id = p_org_id
83 AND trunc (p_inv_date) between start_date and end_date ; -- Modified by Jia for FP Bug#8656402, change sysdate to p_inv_date
84 /*START, by amandali for Bug#10430662*/
85 cursor c_receipt_wct_tax(p_shipment_line_id number) is
86 select a.tax_id
87 FROM JAI_RCV_LINE_TAXES a, JAI_CMN_TAXES_ALL b
88 where a.tax_id = b.tax_id
89 and a.shipment_line_id = p_shipment_line_id
90 and a.tax_type = 'TDS'
91 and b.section_type= 'WCT_SECTION';
92
93 CURSOR c_line_location_wct_taxes(focus_id NUMBER) IS
94 SELECT a.tax_id
95 FROM JAI_PO_TAXES a, JAI_CMN_TAXES_ALL b
96 WHERE line_focus_id = focus_id
97 and a.tax_id = b.tax_id
98 and b.section_type= 'WCT_SECTION'
99 AND a. tax_type = 'TDS';
100
101 cursor c_get_shipment_line_id (p_rcv_transaction_id number) is
102 select shipment_line_id
103 from rcv_transactions
104 where transaction_id = p_rcv_transaction_id;
105
106 CURSOR from_po_distributions(po_dist_id NUMBER) IS
107 SELECT line_location_id, po_line_id
108 FROM po_distributions_all
109 WHERE po_distribution_id = po_dist_id;
110
111 CURSOR from_line_locations(loc_id NUMBER, line_id NUMBER) IS
112 SELECT line_focus_id
113 FROM JAI_PO_LINE_LOCATIONS
114 WHERE line_location_id = loc_id
115 AND po_line_id = line_id;
116
117 from_po_distributions_rec from_po_distributions%ROWTYPE;
118 v_shipment_line_id number;
119 v_receipt_wct_tax_id number;
120 from_line_locations_rec from_line_locations%ROWTYPE;
121 C_LINE_LOCATION_WCT_TAXES_REC c_line_location_wct_taxes%rowtype;
122 ln_processed VARCHAR2(1); /*Bug 12630283*/
123 /*END, by amandali for Bug#10430662*/
124
125 /*start changes for bug 6493858 - logic for 5662741 moved from jai_ap_ia_t.plb*/
126 CURSOR c_tds_invoice_id(cp_invoice_id NUMBER)
127 IS
128 SELECT invoice_to_tds_authority_id invoice_id,
129 invoice_to_tds_authority_num invoice_num
130 FROM jai_ap_tds_thhold_trxs
131 WHERE invoice_id = cp_invoice_id;
132
133 /* Bug 8330522. Added by Lakshmi Gopalsami */
134
135 CURSOR c_tds_invoice_id1(cp_invoice_to_vendor_id NUMBER)
136 IS
137 SELECT invoice_to_tds_authority_id invoice_id,
138 invoice_to_tds_authority_num invoice_num
139 FROM jai_ap_tds_thhold_trxs
140 WHERE invoice_to_vendor_id = cp_invoice_to_vendor_id;
141 -- end for bug 83305222.
142
143 r_tds_invoice_id c_tds_invoice_id%ROWTYPE;
144
145 lv_invoice_payment_status ap_invoices_all.payment_status_flag%TYPE;
146
147 FUNCTION get_invoice_payment_status(p_invoice_id IN NUMBER)
148 RETURN VARCHAR2
149 IS
150 PRAGMA AUTONOMOUS_TRANSACTION;
151
152 BEGIN
153 RETURN (ap_invoices_utility_pkg.get_payment_status(p_invoice_id));
154 END get_invoice_payment_status;
155
156 /*end changes for bug 6493858*/
157
158
159 --Inline procedure added by Sanjikum for Bug#5131075(4722011)
160 PROCEDURE process_prepayment(cp_event varchar2) --Added parameter cp_event for Bug 8431516
161 IS
162 BEGIN
163 /* Check if the prepayment transaction should be processed by the code before
164 TDS Clean up or after TDS clean up.
165
166 if SI is created in the new regime and also the Prepay is created in the new regime,
167 then code should invoke the new regime or else
168 old concurrent shd be invoked */
169
170
171 --Check for SI
172 jai_ap_tds_tax_defaultation.check_old_transaction
173 (
174 p_invoice_id => pr_new.invoice_id,
175 p_new_transaction => lv_new_transaction_si
176 );
177
178 --Check for Pprepayment
179 jai_ap_tds_tax_defaultation.check_old_transaction
180 (
181 p_invoice_distribution_id => pr_new.prepay_distribution_id,
182 p_new_transaction => lv_new_transaction_pp
183 );
184
185 if lv_new_transaction_si = 'Y' and lv_new_transaction_pp = 'Y' then
186
187 lv_codepath := null;
188
189 --Updated by Zhiwei Hou for DTC #13359892 begin
190 ----------------------------------------------------------------------
191 --jai_ap_tds_prepayments_pkg.process_prepayment
192 jai_ap_dtc_prepayments_pkg.process_prepayment
193 ----------------------------------------------------------------------
194 --Updated by Zhiwei Hou for DTC #13359892 end
195 (
196 p_event => cp_event, --Added parameter cp_event for Bug 8431516
197 p_invoice_id => pr_new.invoice_id,
198 p_invoice_distribution_id => pr_new.invoice_distribution_id,
199 p_prepay_distribution_id => pr_new.prepay_distribution_id,
200 p_parent_reversal_id => pr_new.parent_reversal_id,
201 p_prepay_amount => pr_new.amount,
202 p_vendor_id => c_rec_ap_invoices_all.vendor_id,
203 p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
204 p_accounting_date => pr_new.accounting_date,
205 p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
206 p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
207 p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
208 p_org_id => pr_new.org_id,
209 p_creation_date => pr_new.creation_date, /*Bug 5989740 bduvarag*/
210 p_process_flag => lv_process_flag,
211 p_process_message => lv_process_message,
212 p_codepath => lv_codepath
213 );
214
215 if nvl(lv_process_flag, 'N') = 'E' then
216 raise_application_error(-20007,
217 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
218 end if;
219
220 else
221 /* Bug 5640993 FP of 5553489. Added by JMEENA
222 * Invoking the processing of prepayments only during
223 * prepayment application and not during validation
224 */
225 IF lv_process_old_trxn = 'Y' THEN
226
227 --Invoke the old regime functionality
228 --Updated by Zhiwei Hou for DTC #13359892 begin
229 ----------------------------------------------------------------------
230 --jai_ap_tds_prepayments_pkg.process_old_transaction
231 jai_ap_dtc_prepayments_pkg.process_old_transaction
232 ----------------------------------------------------------------------
233 --Updated by Zhiwei Hou for DTC #13359892 end
234 (
235 p_invoice_id => pr_new.invoice_id,
236 p_invoice_distribution_id => pr_new.invoice_distribution_id,
237 p_prepay_distribution_id => pr_new.prepay_distribution_id,
238 p_amount => pr_new.amount,
239 p_last_updated_by => pr_new.last_updated_by,
240 p_last_update_date => pr_new.last_update_date,
241 p_created_by => pr_new.created_by,
242 p_creation_date => pr_new.creation_date,
243 p_org_id => pr_new.org_id,
244 p_process_flag => lv_process_flag,
245 p_process_message => lv_process_message
246 );
247
248 if nvl(lv_process_flag, 'N') = 'E' then
249 raise_application_error(-20008,
250 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
251 end if;
252 END IF ; --End of bug#5640993
253
254 end if; --Transactions in new regime
255 END process_prepayment;
256
257 --Inline procedure added by vkaranam for bug#13422310
258 PROCEDURE process_IPV
259 IS
260 PRAGMA AUTONOMOUS_TRANSACTION;
261 /* Cursor to get the localization taxes attached to the item line*/
262 Cursor get_loc_tax(cp_invoice_id number,cp_inv_distid number) is
263 select jm.*
264 from JAI_AP_MATCH_INV_TAXES jm,jai_cmn_taxes_all jct
265 where jm.tax_id=jct.tax_id
266 and invoice_id = cp_invoice_id
267 and parent_invoice_distribution_id=cp_inv_distid
268 and line_type_lookup_code='MISCELLANEOUS'
269 --adhoc taxes which are different from transaction currency shall not be considered for IPV/ERV ,16dec
270 and (nvl(jct.adhoc_flag,'Q')='N'
271 OR
272 (nvl(jct.adhoc_flag,'Q')='Y' and exists (select '1' from jai_po_taxes jpt
273 where jpt.line_location_id=jm.line_location_id
274 and jpt.currency=jm.currency_code
275 AND jpt.tax_id=jm.tax_id)
276 )
277 OR
278 (nvl(jct.adhoc_flag,'Q')='Y' and exists (select '1' from jai_rcv_line_taxes jpt
279 where jpt.shipment_header_id=jm.shipment_header_id
280 and jpt.shipment_line_id=jm.shipment_line_id
281 and jpt.currency=jm.currency_code
282 AND jpt.tax_id=jm.tax_id)
283 )
284 );
285
286
287
288
289 Cursor get_line_amt(cp_invoice_id number,cp_inv_lineno number) is
290 select nvl(amount,0)
291 from ap_invoice_lines_all
292 where invoice_id = cp_invoice_id
293 and line_number=cp_inv_lineno;
294
295
296 CURSOR c_get_invoice_distribution is
297 select ap_invoice_distributions_s.nextval
298 from dual;
299
300 cursor get_dist_no(cp_invoice_id number,cp_inv_lineno number)
301 is
302 select max(nvl(distribution_line_number,0))
303 from ap_invoice_distributions_all
304 where invoice_id = cp_invoice_id
305 and invoice_line_number=cp_inv_lineno;
306
307 cursor get_tax_name(cp_tax_id in number)
308 is
309 select tax_name
310 from jai_cmn_taxes_all
311 where tax_id=cp_tax_id;
312
313
314 cursor get_invoice_rate(cp_inv_id in number) is
315 SELECT exchange_rate
316 FROM ap_invoices AI
317 WHERE AI.invoice_id = cp_inv_id;
318
319 cursor get_PO_rate(cp_header_id in number) is
320 SELECT rate
321 FROM po_headers ph
322 WHERE ph.po_header_id=cp_header_id;
323
324 --start additions by vkaranam for bug#13618731
325 cursor get_rcv_rate(cp_rcv_transaction_id in number) is
326 SELECT currency_conversion_rate
327 FROM rcv_transactions
328 WHERE transaction_id=cp_rcv_transaction_id;
329
330
331 cursor c_get_accrual_acct(cp_invoice_id number,cp_inv_distid number) is
332 select dist_code_combination_id
333 FROM ap_invoice_distributions_all
334 where invoice_id =cp_invoice_id
335 and invoice_distribution_id=cp_inv_distid;
336
337 ln_accrual_acct number;
338
339
340 lv_exists varchar2(1) := 'N' ;
341 ln_user_id NUMBER;
342 ln_login_id NUMBER;
343 ln_tax_variance_amt NUMBER;
344 ln_invline_amt number;
345 ln_base_variance_amt NUMBER;
346
347 ln_tax_excvariance_amt NUMBER;
348 ln_invoice_rate number;
349 ln_po_exchrate number;
350 ln_precision fnd_currencies.precision%type;
351 ln_invoice_distribution_id NUMBER;
352 ln_distribution_lineno number;
353 lv_tax_name VARCHAR2(100);
354
355 --start changes 13422310 ,14dec
356
357
358 Cursor get_exch_accnts(cp_invoice_id number)
359 is
360 SELECT
361 nvl(sp.rate_var_gain_ccid, -1),
362 nvl(sp.rate_var_loss_ccid, -1)
363 FROM ap_system_parameters sp,
364 gl_sets_of_books gls,
365 ap_invoices ai
366 WHERE sp.set_of_books_id = gls.set_of_books_id
367 AND sp.set_of_books_id = ai.set_of_books_id
368 AND ai.invoice_id = cp_invoice_id;
369
370 ln_xrate_gain_ccid NUMBER;
371 ln_xrate_loss_ccid NUMBER;
372 ln_erv_ccid NUMBER;
373 v_apportn_factor_for_item_line NUMBER;
374 ln_po_amount NUMBER;
375 --end changes 13422310 ,14dec
376 --start additions for bug#13422310 ,29dec
377 /*modified by vkaranam for bug#13618731
378 cursor get_rcv_tax_amt (cp_shipment_line_id in number ,cp_tax_id in number)
379 is
380 select tax_amount
381 from jai_rcv_line_taxes
382 where shipment_line_id=cp_shipment_line_id
383 and tax_id=cp_tax_id;
384 */
385 cursor get_rcv_tax_amt (cp_rcv_transaction_id in number ,cp_tax_id in number)
386 is
387 select jt.tax_amount
388 from jai_rcv_line_taxes jt ,jai_rcv_transactions jrt
389 where
390 jrt.shipment_header_id=jt.shipment_headeR_id
391 and jrt.shipment_line_id=jt.shipment_line_id
392 and jrt.transaction_id=cp_rcv_transaction_id
393 and tax_id=cp_tax_id;
394
395
396 cursor get_po_tax_amt (cp_line_location_id in number ,cp_tax_id in number)
397 is
398 select tax_amount
399 from jai_po_taxes
400 where line_location_id=cp_line_location_id
401 and tax_id=cp_tax_id;
402
403 ln_src_tax_amt NUMBER;
404
405 --end additions for bug#13422310
406
407
408 /*
409 Cursor get_mis_dists
410 is
411 select *
412 from ap_invoice_distributions_all
413 where invoice_id=cp_invoice_id
414 and line_type_lookup_code='MISCELLANEOUS'
415 and invoice_distribut
416 */
417
418 /*
419 RELATED_ID NUMBER (15)
420 Identifier linking related distributions. Used for linking related IPV, ERV, ITEM, or ACCRUAL.
421 Populated from INVOICE_DISTRIBUTION_ID of ITEM or ACCRUAL distribution if any, IPV if no ITEM or ACCRUAL or ERV otherwise
422 */
423
424
425 BEGIN
426 ln_user_id := fnd_global.user_id;
427 ln_login_id := fnd_global.login_id;
428 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','inside call to process_ipv');
429 if pr_new.line_type_lookup_code ='IPV'
430 then
431 for jai_rec in get_loc_tax(pr_new.invoice_id,pr_new.related_id)
432 loop
433
434 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_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 );
435 ln_invline_amt :=0;
436 ln_tax_variance_amt:=0;
437 ln_base_variance_amt := nvl(pr_new.amount,0);
438 ln_distribution_lineno :=0;
439 OPEN get_invoice_rate(pr_new.invoice_id);
440 fetch get_invoice_rate into ln_invoice_rate;
441 close get_invoice_rate;
442
443 OPEN get_line_amt(pr_new.invoice_id,pr_new.invoice_line_number);
444 FETCH get_line_amt INTO ln_invline_amt;
445 CLOSE get_line_amt;
446
447
448 --start additions for bug#13422310 ,29dec
449 v_apportn_factor_for_item_line:=null;
450 ln_src_tax_amt:=null;
451 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','inside ipv SHIPMENT line id '||jai_rec.shipment_line_id||' line_location_id '||jai_rec.line_location_id);
452 --if jai_rec.shipment_line_id is not null 13618731
453 if jai_rec.rcv_transaction_id is not null
454 then
455 open get_rcv_tax_amt(jai_rec.rcv_transaction_id ,jai_rec.tax_id);--13618731
456 fetch get_rcv_tax_amt into ln_src_tax_amt;
457 close get_rcv_tax_amt;
458 elsif jai_rec.line_location_id is not null then
459 open get_po_tax_amt(jai_rec.line_location_id ,jai_rec.tax_id);
460 fetch get_po_tax_amt into ln_src_tax_amt;
461 close get_po_tax_amt;
462 end if;
463 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_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);
464 v_apportn_factor_for_item_line := jai_ap_utils_pkg.get_apportion_factor(pr_new.invoice_id,pr_new.invoice_line_number,'QUANTITY');
465 ln_src_tax_amt := nvl(ln_src_tax_amt,0)*v_apportn_factor_for_item_line; --30dec
466 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','inside ipv ln_src_tax_amt after apportiong '||ln_src_tax_amt);
467 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_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);
468 --calculate the tax variance amount
469 --ipv_tax_variance_amt := invoice amount-po_amount ;
470
471 --ln_tax_variance_amt :=ln_base_variance_amt *nvl(jai_rec.tax_amount,0)/ln_invline_amt ; commented on 29dec for bug#13422310
472 -- ln_tax_variance_amt := ln_src_tax_amt-nvl(jai_rec.tax_amount,0); 30 dec
473 ln_tax_variance_amt := nvl(jai_rec.tax_amount,0)-ln_src_tax_amt; --13422310 ,30dec
474
475 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','inside ipv ln_tax_variance_amt '||ln_tax_variance_amt);
476 --end additions for bug#13422310 ,29dec
477 if nvl(ln_tax_variance_amt,0)<>0
478 then
479
480 open c_get_invoice_distribution;
481 fetch c_get_invoice_distribution into ln_invoice_distribution_id;
482 close c_get_invoice_distribution;
483 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','inside ipv before get_dist_no ');
484 open get_dist_no(pr_new.invoice_id,jai_rec.invoice_line_number);
485 fetch get_dist_no into ln_distribution_lineno;
486 close get_dist_no;
487 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','inside ipv ln_distribution_lineno '||ln_distribution_lineno);
488 open get_tax_name(jai_rec.tax_id);
489 fetch get_tax_name into lv_tax_name;
490 close get_tax_name;
491
492 ln_distribution_lineno :=nvl(ln_distribution_lineno,0)+1;
493
494 --insert the variance amount with line_type_lookup_code as MISCELLANEOUS
495 INSERT INTO ap_invoice_distributions_all
496 (
497 accounting_date,
498 accrual_posted_flag,
499 assets_addition_flag,
500 assets_tracking_flag,
501 cash_posted_flag,
502 distribution_line_number,
503 dist_code_combination_id,
504 invoice_id,
505 last_updated_by,
506 last_update_date,
507 line_type_lookup_code,
508 period_name,
509 set_of_books_id ,
510 amount,
511 base_amount,
512 batch_id,
513 created_by,
514 creation_date,
515 description,
516 exchange_rate_variance,
517 last_update_login,
518 match_status_flag,
519 posted_flag,
520 rate_var_code_combination_id ,
521 reversal_flag ,
522 program_application_id,
523 program_id,
524 program_update_date,
525 accts_pay_code_combination_id,
526 invoice_distribution_id,
527 quantity_invoiced,
528 po_distribution_id ,
529 rcv_transaction_id,
530 --price_var_code_combination_id,/*no longer used in R12*/
531 --invoice_price_variance,/*no longer used in R12*/
532 -- base_invoice_price_variance,/*no longer used in R12*/
533 matched_uom_lookup_code
534 ,invoice_line_number
535 ,org_id
536 ,charge_applicable_to_dist_id
537 , project_id
538 , task_id
539 , expenditure_type
540 , expenditure_item_date
541 , expenditure_organization_id
542 , project_accounting_context
543 , pa_addition_flag
544 ,distribution_class
545 ,related_id /*13422310*/
546 )
547 VALUES
548 (
549 pr_new.accounting_date,
550 pr_new.accrual_posted_flag,
551 pr_new.assets_addition_flag,
552 'N',/*need to check the importance of assets_tracking_flag*/
553 'N',
554 ln_distribution_lineno,
555 pr_new.dist_code_combination_id,
556 pr_new.invoice_id,
557 ln_user_id,
558 sysdate,
559 'MISCELLANEOUS',
560 pr_new.period_name,
561 pr_new.set_of_books_id ,
562 ln_tax_variance_amt ,
563 -- ln_tax_variance_amt * NVL(pr_new.exchange_rate,1), 14dec
564 ln_tax_variance_amt * NVL(ln_invoice_rate,1), --14dec
565 pr_new.batch_id,
566 ln_user_id,
567 sysdate,
568 lv_tax_name,
569 null,
570 ln_login_id,
571 pr_new.match_status_flag ,
572 'N',
573 NULL,
574 pr_new.reversal_flag,
575 pr_new.program_application_id,
576 pr_new.program_id,
577 pr_new.program_update_date,
578 pr_new.accts_pay_code_combination_id,
579 ln_invoice_distribution_id,
580 null,
581 pr_new.po_distribution_id ,
582 pr_new.rcv_transaction_id,
583 -- v_price_var_accnt,
584 -- v_tax_variance_inv_cur,
585 -- v_tax_variance_fun_cur,
586 pr_new.matched_uom_lookup_code,
587 jai_rec.invoice_line_number,
588 pr_new.org_id,
589 pr_new.charge_applicable_to_dist_id/*need to check this,Invoice distribution to which 100% of current charge is applied */
590 , pr_new.project_id
591 , pr_new.task_id
592 , pr_new.expenditure_type
593 , pr_new.expenditure_item_date
594 , pr_new.expenditure_organization_id
595 , pr_new.project_accounting_context
596 , pr_new.pa_addition_flag
597 ,pr_new.distribution_class
598 ,jai_rec.invoice_distribution_id
599 );
600
601
602 --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
603 ln_invoice_distribution_id :=null;
604
605 open c_get_invoice_distribution;
606 fetch c_get_invoice_distribution into ln_invoice_distribution_id;
607 close c_get_invoice_distribution;
608 --jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ipv before get_dist_no ');
609
610
611
612 open c_get_accrual_acct (pr_new.invoice_id,jai_rec.parent_invoice_distribution_id);
613 fetch c_get_accrual_acct into ln_accrual_acct;
614 close c_get_accrual_acct;
615
616 ln_distribution_lineno :=nvl(ln_distribution_lineno,0)+1;
617
618 --insert the variance amount with line_type_lookup_code as MISCELLANEOUS
619 INSERT INTO ap_invoice_distributions_all
620 (
621 accounting_date,
622 accrual_posted_flag,
623 assets_addition_flag,
624 assets_tracking_flag,
625 cash_posted_flag,
626 distribution_line_number,
627 dist_code_combination_id,
628 invoice_id,
629 last_updated_by,
630 last_update_date,
631 line_type_lookup_code,
632 period_name,
633 set_of_books_id ,
634 amount,
635 base_amount,
636 batch_id,
637 created_by,
638 creation_date,
639 description,
640 exchange_rate_variance,
641 last_update_login,
642 match_status_flag,
643 posted_flag,
644 rate_var_code_combination_id ,
645 reversal_flag ,
646 program_application_id,
647 program_id,
648 program_update_date,
649 accts_pay_code_combination_id,
650 invoice_distribution_id,
651 quantity_invoiced,
652 po_distribution_id ,
653 rcv_transaction_id,
654 --price_var_code_combination_id,/*no longer used in R12*/
655 --invoice_price_variance,/*no longer used in R12*/
656 -- base_invoice_price_variance,/*no longer used in R12*/
657 matched_uom_lookup_code
658 ,invoice_line_number
659 ,org_id
660 ,charge_applicable_to_dist_id
661 , project_id
662 , task_id
663 , expenditure_type
664 , expenditure_item_date
665 , expenditure_organization_id
666 , project_accounting_context
667 , pa_addition_flag
668 ,distribution_class
669 ,related_id /*13422310*/
670 )
671 VALUES
672 (
673 pr_new.accounting_date,
674 pr_new.accrual_posted_flag,
675 pr_new.assets_addition_flag,
676 'N',/*need to check the importance of assets_tracking_flag*/
677 'N',
678 ln_distribution_lineno,
679 ln_accrual_acct,
680 pr_new.invoice_id,
681 ln_user_id,
682 sysdate,
683 'MISCELLANEOUS',
684 pr_new.period_name,
685 pr_new.set_of_books_id ,
686 -ln_tax_variance_amt ,
687 -- -ln_tax_variance_amt * NVL(pr_new.exchange_rate,1), 14dec
688 -ln_tax_variance_amt * NVL(ln_invoice_rate,1), --14dec
689 pr_new.batch_id,
690 ln_user_id,
691 sysdate,
692 lv_tax_name,
693 null,
694 ln_login_id,
695 pr_new.match_status_flag ,
696 'N',
697 NULL,
698 pr_new.reversal_flag,
699 pr_new.program_application_id,
700 pr_new.program_id,
701 pr_new.program_update_date,
702 pr_new.accts_pay_code_combination_id,
703 ln_invoice_distribution_id,
704 null,
705 pr_new.po_distribution_id ,
706 pr_new.rcv_transaction_id,
707 -- v_price_var_accnt,
708 -- v_tax_variance_inv_cur,
709 -- v_tax_variance_fun_cur,
710 pr_new.matched_uom_lookup_code,
711 jai_rec.invoice_line_number,
712 pr_new.org_id,
713 pr_new.charge_applicable_to_dist_id/*need to check this,Invoice distribution to which 100% of current charge is applied */
714 , pr_new.project_id
715 , pr_new.task_id
716 , pr_new.expenditure_type
717 , pr_new.expenditure_item_date
718 , pr_new.expenditure_organization_id
719 , pr_new.project_accounting_context
720 , pr_new.pa_addition_flag
721 ,pr_new.distribution_class
722 ,jai_rec.invoice_distribution_id
723 );
724
725
726
727 commit;
728 end if;--if nvl(ln_tax_variance_amt,0)<>0
729
730 end loop;
731
732 elsif pr_new.line_type_lookup_code ='ERV'
733 then
734 for jai_rec in get_loc_tax(pr_new.invoice_id,pr_new.related_id)
735 loop
736
737 ln_invline_amt :=0;
738 ln_tax_excvariance_amt:=0;
739 ln_po_exchrate := 0;
740 ln_invoice_rate :=0;
741 ln_distribution_lineno :=0;
742
743 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_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 );
744 OPEN get_invoice_rate(pr_new.invoice_id);
745 fetch get_invoice_rate into ln_invoice_rate;
746 close get_invoice_rate;
747 /*moved the call inside the if rcv_transaction_id is null condition*
748 OPEN get_po_rate(jai_rec.po_header_id);
749 fetch get_po_rate into ln_po_exchrate;
750 close get_po_rate;*/
751 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_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);
752
753 --calculate the tax exchange variance amount
754 --exchange_rate_variance_amount := taxamount_in_usd *(Invoice_exchange_rate-po_exchange_rate);
755 /*issue mentioned by the ct:
756
757 --po : 250 usd ,10K
758 --invoice : 250 USD,11k
759
760 --erv 1k
761 --due to price variance amount is changes from 250 usd to 300 usd.
762
763 --po :250 ,10k
764 --invoice : 300 usd ,13200
765 -- ipv : 50 usd , 2200
766 --ipv : -50 usd ,2200
767 --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
768 --erv : 0 ,-1200 -- correct entry should be : -1000 (250*4)
769 --logic to get the invoice amount is :
770 -- po_amount*apportion_factor_for_item_line
771 --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)
772 */
773
774 --start additions for bug#13422310 ,30dec
775 ln_src_tax_amt:=null;
776 ln_po_amount:=null;
777 --if jai_rec.shipment_line_id is not null 13618731
778 if jai_rec.rcv_transaction_id is not null
779 then
780 open get_rcv_tax_amt(jai_rec.rcv_transaction_id ,jai_rec.tax_id);--13618731
781 fetch get_rcv_tax_amt into ln_src_tax_amt;
782 close get_rcv_tax_amt;
783
784 --added for bug#13618731
785 OPEN get_rcv_rate(jai_rec.rcv_transaction_id);
786 fetch get_rcv_rate into ln_po_exchrate;
787 close get_rcv_rate;
788 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','receipt exchange rate '||ln_po_exchrate||' receipt tax amount '||ln_src_tax_amt);--13618731
789
790 elsif jai_rec.line_location_id is not null then
791 open get_po_tax_amt(jai_rec.line_location_id ,jai_rec.tax_id);
792 fetch get_po_tax_amt into ln_src_tax_amt;
793 close get_po_tax_amt;
794
795 OPEN get_po_rate(jai_rec.po_header_id);
796 fetch get_po_rate into ln_po_exchrate;
797 close get_po_rate;
798 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','po exchange rate '||ln_po_exchrate||' po tax amount '||ln_src_tax_amt);--13618731
799 end if;
800 --end bug#13422310 ,30dec
801 --Start additions for bug#13422310 ,18dec
802 v_apportn_factor_for_item_line := jai_ap_utils_pkg.get_apportion_factor(pr_new.invoice_id,pr_new.invoice_line_number, 'QUANTITY');
803 -- ln_po_amount:= nvl(jai_rec.tax_amount,0)/v_apportn_factor_for_item_line; 30dec
804 ln_po_amount := ln_src_tax_amt *v_apportn_factor_for_item_line;
805
806 --ln_tax_excvariance_amt :=nvl(jai_rec.tax_amount,0) *(nvl(ln_invoice_rate,0)-nvl(ln_po_exchrate,0)); 18dec
807 ln_tax_excvariance_amt :=nvl(ln_po_amount,0) *(nvl(ln_invoice_rate,0)-nvl(ln_po_exchrate,0));--18dec
808 --start changes for 13422310 --14dec
809 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','inside ERV nvl(jai_rec.tax_amount,0) '||nvl(jai_rec.tax_amount,0)||' ln_tax_excvariance_amt '||ln_tax_excvariance_amt);
810 if nvl(ln_tax_excvariance_amt,0)<>0
811 then
812
813 ln_xrate_gain_ccid :=null;
814 ln_xrate_loss_ccid :=null;
815
816 open get_exch_accnts(pr_new.invoice_id);
817 fetch get_exch_accnts into ln_xrate_gain_ccid,ln_xrate_loss_ccid;
818 close get_exch_accnts;
819
820 if sign(nvl(ln_invoice_rate,0)-nvl(ln_po_exchrate,0))=1
821 then
822 ln_erv_ccid := ln_xrate_gain_ccid;
823 else
824 ln_erv_ccid := ln_xrate_loss_ccid;
825 end if;
826
827 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','inside ERV exchange gain ccid '||ln_xrate_gain_ccid);
828 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','inside ERV exchange loss ccid '||ln_xrate_loss_ccid);
829 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','inside ERV CCID Derived is '||ln_erv_ccid);
830 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','inside ERV exchange_rate_variance_amount '||ln_tax_excvariance_amt);
831
832 --end changes for 13422310 --14dec
833 open c_get_invoice_distribution;
834 fetch c_get_invoice_distribution into ln_invoice_distribution_id;
835 close c_get_invoice_distribution;
836 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','inside ERV BEFORE get_dist_no ');
837 open get_dist_no(pr_new.invoice_id,jai_rec.invoice_line_number);
838 fetch get_dist_no into ln_distribution_lineno;
839 close get_dist_no;
840
841 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','inside ERV ln_distribution_lineno ' ||ln_distribution_lineno);
842
843 open get_tax_name(jai_rec.tax_id);
844 fetch get_tax_name into lv_tax_name;
845 close get_tax_name;
846
847 ln_distribution_lineno :=nvl(ln_distribution_lineno,0)+1;
848
849 --insert the variance amount with line_type_lookup_code as MISCELLANEOUS
850 INSERT INTO ap_invoice_distributions_all
851 (
852 accounting_date,
853 accrual_posted_flag,
854 assets_addition_flag,
855 assets_tracking_flag,
856 cash_posted_flag,
857 distribution_line_number,
858 dist_code_combination_id,
859 invoice_id,
860 last_updated_by,
861 last_update_date,
862 line_type_lookup_code,
863 period_name,
864 set_of_books_id ,
865 amount,
866 base_amount,
867 batch_id,
868 created_by,
869 creation_date,
870 description,
871 exchange_rate_variance,
872 last_update_login,
873 match_status_flag,
874 posted_flag,
875 rate_var_code_combination_id ,
876 reversal_flag ,
877 program_application_id,
878 program_id,
879 program_update_date,
880 accts_pay_code_combination_id,
881 invoice_distribution_id,
882 quantity_invoiced,
883 po_distribution_id ,
884 rcv_transaction_id,
885 --price_var_code_combination_id,/*no longer used in R12*/
886 --invoice_price_variance,/*no longer used in R12*/
887 -- base_invoice_price_variance,/*no longer used in R12*/
888 matched_uom_lookup_code
889 ,invoice_line_number
890 ,org_id
891 ,charge_applicable_to_dist_id
892 , project_id
893 , task_id
894 , expenditure_type
895 , expenditure_item_date
896 , expenditure_organization_id
897 , project_accounting_context
898 , pa_addition_flag
899 ,distribution_class
900 ,related_id /*13422310*/
901 )
902 VALUES
903 (
904 pr_new.accounting_date,
905 pr_new.accrual_posted_flag,
906 pr_new.assets_addition_flag,
907 'N',/*need to check the importance of assets_tracking_flag*/
908 'N',
909 ln_distribution_lineno,
910 -- pr_new.dist_code_combination_id,
911 ln_erv_ccid ,
912 pr_new.invoice_id,
913 ln_user_id,
914 sysdate,
915 'MISCELLANEOUS',
916 pr_new.period_name,
917 pr_new.set_of_books_id ,
918 0 ,
919 ln_tax_excvariance_amt,
920 pr_new.batch_id,
921 ln_user_id,
922 sysdate,
923 lv_tax_name,
924 null,
925 ln_login_id,
926 pr_new.match_status_flag ,
927 'N',
928 NULL,
929 pr_new.reversal_flag,
930 pr_new.program_application_id,
931 pr_new.program_id,
932 pr_new.program_update_date,
933 pr_new.accts_pay_code_combination_id,
934 ln_invoice_distribution_id,
935 null,
936 pr_new.po_distribution_id ,
937 pr_new.rcv_transaction_id,
938 -- v_price_var_accnt,
939 -- v_tax_variance_inv_cur,
940 -- v_tax_variance_fun_cur,
941 pr_new.matched_uom_lookup_code,
942 jai_rec.invoice_line_number,
943 pr_new.org_id,
944 pr_new.charge_applicable_to_dist_id/*need to check this,Invoice distribution to which 100% of current charge is applied */
945 , pr_new.project_id
946 , pr_new.task_id
947 , pr_new.expenditure_type
948 , pr_new.expenditure_item_date
949 , pr_new.expenditure_organization_id
950 , pr_new.project_accounting_context
951 , pr_new.pa_addition_flag
952 ,pr_new.distribution_class
953 ,jai_rec.invoice_distribution_id
954 );
955
956
957 --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
958 ln_invoice_distribution_id :=null;
959
960 open c_get_invoice_distribution;
961 fetch c_get_invoice_distribution into ln_invoice_distribution_id;
962 close c_get_invoice_distribution;
963 --jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ipv before get_dist_no ');
964
965
966
967 open c_get_accrual_acct (pr_new.invoice_id,jai_rec.parent_invoice_distribution_id);
968 fetch c_get_accrual_acct into ln_accrual_acct;
969 close c_get_accrual_acct;
970
971 ln_distribution_lineno :=nvl(ln_distribution_lineno,0)+1;
972
973 --insert the variance amount with line_type_lookup_code as MISCELLANEOUS
974 INSERT INTO ap_invoice_distributions_all
975 (
976 accounting_date,
977 accrual_posted_flag,
978 assets_addition_flag,
979 assets_tracking_flag,
980 cash_posted_flag,
981 distribution_line_number,
982 dist_code_combination_id,
983 invoice_id,
984 last_updated_by,
985 last_update_date,
986 line_type_lookup_code,
987 period_name,
988 set_of_books_id ,
989 amount,
990 base_amount,
991 batch_id,
992 created_by,
993 creation_date,
994 description,
995 exchange_rate_variance,
996 last_update_login,
997 match_status_flag,
998 posted_flag,
999 rate_var_code_combination_id ,
1000 reversal_flag ,
1001 program_application_id,
1002 program_id,
1003 program_update_date,
1004 accts_pay_code_combination_id,
1005 invoice_distribution_id,
1006 quantity_invoiced,
1007 po_distribution_id ,
1008 rcv_transaction_id,
1009 --price_var_code_combination_id,/*no longer used in R12*/
1010 --invoice_price_variance,/*no longer used in R12*/
1011 -- base_invoice_price_variance,/*no longer used in R12*/
1012 matched_uom_lookup_code
1013 ,invoice_line_number
1014 ,org_id
1015 ,charge_applicable_to_dist_id
1016 , project_id
1017 , task_id
1018 , expenditure_type
1019 , expenditure_item_date
1020 , expenditure_organization_id
1021 , project_accounting_context
1022 , pa_addition_flag
1023 ,distribution_class
1024 ,related_id /*13422310*/
1025 )
1026 VALUES
1027 (
1028 pr_new.accounting_date,
1029 pr_new.accrual_posted_flag,
1030 pr_new.assets_addition_flag,
1031 'N',/*need to check the importance of assets_tracking_flag*/
1032 'N',
1033 ln_distribution_lineno,
1034 ln_accrual_acct,
1035 pr_new.invoice_id,
1036 ln_user_id,
1037 sysdate,
1038 'MISCELLANEOUS',
1039 pr_new.period_name,
1040 pr_new.set_of_books_id ,
1041 0 ,
1042 -ln_tax_excvariance_amt,
1043 pr_new.batch_id,
1044 ln_user_id,
1045 sysdate,
1046 lv_tax_name,
1047 null,
1048 ln_login_id,
1049 pr_new.match_status_flag ,
1050 'N',
1051 NULL,
1052 pr_new.reversal_flag,
1053 pr_new.program_application_id,
1054 pr_new.program_id,
1055 pr_new.program_update_date,
1056 pr_new.accts_pay_code_combination_id,
1057 ln_invoice_distribution_id,
1058 null,
1059 pr_new.po_distribution_id ,
1060 pr_new.rcv_transaction_id,
1061 -- v_price_var_accnt,
1062 -- v_tax_variance_inv_cur,
1063 -- v_tax_variance_fun_cur,
1064 pr_new.matched_uom_lookup_code,
1065 jai_rec.invoice_line_number,
1066 pr_new.org_id,
1067 pr_new.charge_applicable_to_dist_id/*need to check this,Invoice distribution to which 100% of current charge is applied */
1068 , pr_new.project_id
1069 , pr_new.task_id
1070 , pr_new.expenditure_type
1071 , pr_new.expenditure_item_date
1072 , pr_new.expenditure_organization_id
1073 , pr_new.project_accounting_context
1074 , pr_new.pa_addition_flag
1075 ,pr_new.distribution_class
1076 ,jai_rec.invoice_distribution_id
1077 );
1078
1079
1080
1081 commit;
1082
1083 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_pkg.briud','inside ERV after update');
1084
1085 end if;--if nvl(ln_tax_variance_amt,0)<>0
1086
1087 end loop;
1088
1089
1090
1091
1092 end if;--if pr_new.line_type_lookup_code ='IPV'
1093
1094
1095 end process_ipv;
1096
1097
1098 BEGIN
1099 pv_return_code := jai_constants.successful ;
1100 /* ----------------------------------------------------------------------------
1101 FILENAME : jai_ap_ida_dtc_t.plb
1102
1103 Created By : Cholei
1104
1105 Created Date : 29-MAY-2012
1106
1107 Bug :
1108
1109 Purpose : Code port from JAI_AP_IDA_TRIGGER_PKG.BIUD_T1 to implement DTC logic after ZX integratmion.
1110
1111 CHANGE HISTORY:
1112 -------------------------------------------------------------------------------
1113 S.No Date Author and Details
1114
1115 1. 25/03/2004 Aparajita. Bug # 4088186. TDS Clean up. Version#115.0
1116
1117 This is the only trigger introduced for all the before event
1118 on the table on which this is based.
1119
1120 2. 11/05/2005 rchandan for bug#4333488. Version 116.1
1121 The Invoice Distribution DFF is eliminated and a new global DFF is used to
1122 maintain the functionality. From now the TDS tax, WCT tax and ESSI will not
1123 be populated in the attribute columns of ap_invoice_distributions_all table
1124 instead these will be populated in the global attribute columns. So the code changes are
1125 made accordingly.
1126
1127 3. 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
1128 Changed AP Lookup code from 'TDS' to 'INDIA TDS'
1129
1130 4. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
1131 DB Entity as required for CASE COMPLAINCE. Version 116.2
1132
1133 5 13-Jun-2005 File Version: 116.3
1134 Ramananda for bug#4428980. Removal of SQL LITERALs is done
1135
1136 6. 06-jul-2005 AP lines change.
1137 7. 18/04/2007 bduvarag for the Bug#5989740, file version 120.2
1138 Forward porting the changes done in 11i bug#5907436
1139 8. 21/10/2008 Bug 5640993 FP of 5553489. Added by JMEENA
1140 Invoked processing of old prepayment application using variable lv_process_old_trxn. Processing of TDS prepayment application for transactions created
1141 prior to upgrade to TDS Threshold will be done only at the time of application and not during validation.
1142
1143 9. 18-Jul-2009 Bug 8641199
1144 Need to update match_status_flag in jai_ap_tds_inv_taxes with the match_status_flag of
1145 ap_invoice_distributions_all
1146
1147 8. 11-Jan-2010 Xiao Lv for bug#7347508, related 11i bug#6417285
1148 Added new conditions to check if either TDS, WCT or ESSI taxes are getting modified or inserted
1149 after the invoice has been validated. In such cases an error message is thrown stating that once an
1150 invoice is validated, there should not be any modifications made to these three taxes.
1151
1152 Added the new condition 'nvl(pr_new.global_attribute_category, 'JA.IN.APXINWKB.DISTRIBUTIONS' ) =
1153 'JA.IN.APXINWKB.DISTRIBUTIONS'' to make sure the checks are made only when the context is
1154 'JA.IN.APXINWKB.DISTRIBUTIONS'
1155
1156 9. 14-Jan-2010 Xiao Lv for bug#7154864, related 11i bug#6767347
1157 Commented two if condition section code.
1158
1159 Dependency:
1160 ----------
1161
1162 Sl No. Bug Dependent on
1163 Bug/Patch set Details
1164 -------------------------------------------------------------------------------------------------
1165 1 3924692 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
1166 4033992.
1167 ja_in_util_pkg_s.sql 115.0
1168 ja_in_util_pkg_b.sql 115.0
1169
1170 2. 4088186 4088186 Call to Package jai_ap_tds_tax_defaultation.
1171 -------------------------------------------------------------------------------------------------
1172
1173 8. 17/Sep/2007 Bug 5911913. Added by vkantamn version 120.6
1174 Added two parameters
1175 (1) p_old_input_dff_value_wct
1176 (2) p_old_input_dff_value_essi
1177 in the call to procedure process_invoice.
1178
1179 Dependencies:
1180 -------------
1181 jai_ap_tds_dflt.pls 120.1
1182 jai_ap_tds_dflt.plb 120.3
1183 jai_ap_ida_t.plb 120.5
1184
1185
1186 9. 18-Oct-07 Bug 6493858, File version 120.8
1187 Moved the validation done for invoice cancellation process from jai_ap_ia_t.plb.
1188 Through this, changes done for bug 6318997 have been forward ported to R12 code.
1189
1190 10. 21-Dec-2007 Sanjikum for Bug#6708042, Version 120.10
1191 Obsoleted the changes done for verion 120.6
1192
1193 11. 05-Dec-2008 Bgowrava for Bug#7433241, file Version 120.10.12010000.4
1194 moved the end if condition in the code of intercepting the validate event. this enables that only the call to the
1195 process_tds_at_inv_validate procedure is dependent on the value of variable lv_is_invoice_validated. Thus enabling
1196 the code for prepayment to execute when a prepayment application or unapplication to execute when the prepayment is
1197 applied before validation of the std invoice.
1198
1199 12. 01-Apr-2010 Bgowrava for bug#9457695, file version 120.10.12010000.15
1200 Added the code to populate global_attribute1 and global attribute context in ap_invoice_distributions table
1201 at the place after the validation procedure.
1202
1203 13. 24-12-2010 amandali for Bug 10430662
1204 Forward ported the changes made in bug 7328147,7328147
1205 +Added code for defaulting WCT taxes from a PO or Receipt when a PO matched invoice or Receipt
1206 matched invoice is created.
1207 +Need to update WCT Taxes to the Invoices when PO/Receipt matched invoice is created
1208 This must be done while inserting and updating
1209
1210 14. 3-feb-2011 amandali for bug 11709107
1211 Forward ported the changes made in bug 9951744
1212 Description: Default Tax Codes were not shown in the GDF after saving the distribution form
1213 Fix: GLOBAL_ATTRIBUTE1 and GLOBAL_ATTRIBUTE_CATEGORY are saved in Before Insert Trigger
1214 To prevent usability issues for customers already using the previous solution, added to Client
1215 Extension to restrict the above behavior based on Customer preference
1216 15. 01-dec-2011 vkaranam for bug 13422310
1217 Issue: IPV and ERV has not been calculated for JAI taxes.
1218 fix :
1219 --IPV or ERV amount for jai taxes will be inserted into AID with "MISCELLANEOUS" line.
1220 added the inline procedure process_ipv
1221 2.ERV shall not be generated for adhoc taxes with curreny different from the document currency.
1222 16. 29-dec-2011 vkaranam for bug 13422310
1223 Issue: IPV is not calculated correctly for JAI taxes
1224 fix :
1225 IPV tax amount shall be base_po_tax_amt-invoice_tax_amt.
1226 17. 29-jan-2012 vkaranam for bug 13618731
1227 Issue: ERV is not calculated for JAI taxes for the receipt matched invoice.
1228 This issue will particularly occurs if the PO doesnot have the taxes
1229 attached.
1230
1231 Technical details:
1232 jai_ap_match_inv_taxes.shipment_line_id is used to get the receipt tax
1233 amount.
1234 But jai_ap_match_inv_taxes.shipment_line_id will be always null.
1235 Hence the receipt tax amount retrieved is null ,due to which ERV tax amount
1236 is null and the accounting didnt happen for the ERV line
1237
1238 fix :
1239 Modified the jai_ap_ida_trigger_pkg.process_ipv (cursor get_rcv_tax_amt).
1240 also for receipt exchange rate is fetched from rcv_transactions instead
1241 of PO document.
1242 18. 04/05/2012 Chong for ZX integration
1243 Comment out code concerning global_attribute1, for DFF nolonger used in DTC.
1244 Comment pr_old.match_status_flag which used in validation, for validation will be invoke in ZX.
1245
1246 ------------------------------------------------------------------------------------------ */
1247
1248 /*CHANGE HISTORY:
1249 S.No Bug Date Author and Details
1250 1 6493858 4-DEC-2007 Added by Nitin Prashar, for cancelation of Base Invoice*/
1251
1252
1253 if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
1254 ln_org_id := pr_new.org_id;
1255 ln_set_of_books_id := pr_new.set_of_books_id;
1256 elsif pv_action = jai_constants.deleting then
1257 ln_org_id := pr_old.org_id;
1258 ln_set_of_books_id := pr_old.set_of_books_id;
1259 end if;
1260
1261
1262 --START additions by vkaranam for bug#13422310
1263 /*
1264 MATCH_STATUS_FLAG VARCHAR2 (1)
1265 Validation status for the distribution.
1266 This flag is used to derive Invoice Status.
1267 Possible values for this flag: Null or N for invoice distributions that are not tested and validated,
1268 T for distributions that have been tested,
1269 A for distributions that have been tested and validated,
1270 S for distributions that have been selected for validation
1271 */
1272
1273 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_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'));
1274 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','pr_new.line_type_lookup_code '||pr_new.line_type_lookup_code);
1275 if pv_action = jai_constants.inserting
1276 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'))
1277 AND ( pr_new.line_type_lookup_code ='IPV' or pr_new.line_type_lookup_code ='ERV' )
1278 then
1279 jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_ida_trigger_dtc_pkg.briud','before call to process_ipv');
1280 process_ipv;
1281 end if;
1282
1283 --end additions for bug#13422310
1284
1285 /* TDS Tax Defaultation Functionality Bug # 4088186 */
1286 open c_ap_invoices_all;
1287 fetch c_ap_invoices_all into c_rec_ap_invoices_all;
1288 close c_ap_invoices_all;
1289
1290 /* Bug 5640993 FP of 5553489. Added by JMEENA
1291 * Initialized the variable lv_process_old_trxn to 'Y' so that
1292 * prepayments will be processed during insert event.
1293 */
1294 lv_process_old_trxn := 'Y';
1295
1296 /*Bug 5989740 bduvarag start*/
1297 -- Rmoved by Jia for FP Bug#8656402, Begin
1298 ------------------------------------------------------------------------
1299 /*
1300 IF JAI_AP_TDS_GENERATION_pkg.gn_tds_rounding_factor IS NULL
1301 OR JAI_AP_TDS_GENERATION_pkg.gd_tds_rounding_effective_date IS NULL
1302 THEN
1303 */
1304 ------------------------------------------------------------------------
1305 -- Rmoved by Jia for FP Bug#8656402, End
1306 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
1307 FETCH c_get_rnd_factor into
1308 JAI_AP_TDS_GENERATION_pkg.gn_tds_rounding_factor,
1309 JAI_AP_TDS_GENERATION_pkg.gd_tds_rounding_effective_date;
1310 CLOSE c_get_rnd_factor ;
1311 --END IF; -- Rmoved by Jia for FP Bug#8656402
1312
1313
1314 /*Bug 5989740 bduvarag end*/
1315
1316 --Comment out by Chong.Lei for ZX integration test 20120411 start
1317 /*
1318 --Added by Xiao Lv for bug#7347508 on 11-Jan-2010, begin
1319 if pv_action = jai_constants.updating
1320 then
1321 IF nvl(pr_new.global_attribute_category, 'JA.IN.APXINWKB.DISTRIBUTIONS' ) = 'JA.IN.APXINWKB.DISTRIBUTIONS'
1322 AND ( nvl(pr_old.global_attribute1, 0) <> nvl(pr_new.global_attribute1, 0)
1323 OR nvl(pr_old.global_attribute2, 0) <> nvl(pr_new.global_attribute2, 0)
1324 OR nvl(pr_old.global_attribute3, 0) <> nvl(pr_new.global_attribute3, 0)
1325 )
1326 AND pr_old.match_status_flag = 'A'
1327 THEN
1328 raise_application_error(-20036,
1329 'Error - Cannot Modify or Insert the values for TDS, WCT or ESSI tax id once Invoice is validated ');
1330 end if;
1331 end if;
1332 --Added by Xiao Lv for bug#7347508 on 11-Jan-2010, end
1333 */
1334 --Comment out by Chong.Lei for ZX integration test 20120411 end
1335
1336 if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
1337
1338 if nvl(pr_new.global_attribute_category, 'JA.IN.APXINWKB.DISTRIBUTIONS' ) = 'JA.IN.APXINWKB.DISTRIBUTIONS' and -- rchandan for bug#4333488
1339 pr_new.line_type_lookup_code <> 'PREPAY' and
1340 c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
1341 c_rec_ap_invoices_all.cancelled_date is null
1342 then
1343 -- comment for debug DTC by xin on 28-Dec-2011
1344 /*jai_ap_tds_tax_defaultation.process_invoice
1345 (
1346 p_invoice_id => pr_new.invoice_id,
1347 p_invoice_line_number => pr_new.invoice_line_number , \* AP Lines*\
1348 p_invoice_distribution_id => pr_new.invoice_distribution_id,
1349 p_line_type_lookup_code => pr_new.line_type_lookup_code,
1350 p_distribution_line_number => pr_new.distribution_line_number,
1351 p_parent_reversal_id => pr_new.parent_reversal_id,
1352 p_reversal_flag => pr_new.reversal_flag,
1353 p_amount => pr_new.amount,
1354 p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1355 p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1356 p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1357 p_po_distribution_id => pr_new.po_distribution_id,
1358 p_rcv_transaction_id => pr_new.rcv_transaction_id,
1359 p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1360 p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1361 p_input_dff_value_tds => pr_new.global_attribute1, -- rchandan for bug#4333488
1362 p_input_dff_value_wct => pr_new.global_attribute2, -- rchandan for bug#4333488
1363 p_old_input_dff_value_wct => pr_old.global_attribute2, -- Added by Bgowrava for Bug 5911913
1364 p_input_dff_value_essi => pr_new.global_attribute3, -- rchandan for bug#4333488
1365 p_old_input_dff_value_essi => pr_old.global_attribute3, -- Added by Bgowrava for Bug 5911913
1366 p_org_id => pr_new.org_id,
1367 p_accounting_date => pr_new.accounting_date,
1368 p_call_from => 'ja_in_ap_aida_after_trg',
1369 p_final_tds_tax_id => ln_final_tds_tax_id,
1370 p_process_flag => lv_process_flag,
1371 p_process_message => lv_process_message,
1372 p_codepath => lv_codepath
1373 );*/
1374
1375 if nvl(lv_process_flag, 'N') = 'E' then
1376 /* raise_application_error(-20004,
1377 '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 :=
1378 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_codepath ; return ;
1379 end if;
1380
1381 /*Bug 8641199 - Start*/
1382 jai_ap_tds_generation_pkg.status_update_chk_validate
1383 (
1384 p_invoice_id => pr_new.invoice_id,
1385 p_invoice_distribution_id => pr_new.invoice_distribution_id,
1386 p_match_status_flag => pr_new.match_status_flag,
1387 p_is_invoice_validated => lv_is_invoice_validated,
1388 p_process_flag => lv_process_flag,
1389 p_process_message => lv_process_message,
1390 p_codepath => lv_codepath
1391 );
1392 /*Bug 8641199 - End*/
1393 /*Default Tax Codes to be shown on Save(in GDF) only if the Client Extension in jai_populate_attribute package is set to Y*/
1394 IF nvl(jai_populate_attribute.default_value_check, 'N') = 'Y' THEN
1395 /*Uncommenting the following code for 11709107 by amandali*/
1396 if pr_new.global_attribute1 is null and ln_final_tds_tax_id is not null then --rchandan for bug#4333488
1397 pr_new.global_attribute1 := to_char(ln_final_tds_tax_id);--rchandan for bug#4333488
1398 end if;
1399
1400 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
1401 pr_new.global_attribute_category := 'JA.IN.APXINWKB.DISTRIBUTIONS'; --rchandan for bug#4333488
1402 end if; --Commented by xiao for bug#7154864
1403 END IF;
1404 /*End -Bug 11709107*/
1405 end if; /* Additional conditions */
1406
1407 end if; /*inserting or updating */
1408
1409 /* TDS Tax Defaultation Functionality Bug # 4088186 */
1410 /*Bug 12630283 - Start*/
1411 /*Data is inserted into JAI_AP_TDS_PREPAYMENTS before Validation. This results in incorrect TDS getting calculated
1412 On validation of Invoice the match status flag of PREPAY line is set to 'A' first resulting in JAI_AP_TDS_PREPAYMENTS
1413 getting inserted before the Item Line's match status flag is set to A. Only if all the distraibutions match status flag
1414 is set to A or T process_tds_at_inv_validate shall be called. If process_tds_at_inv_validate is called after insertion
1415 of data in JAI_AP_TDS_PREPAYMENTS it results in incorrect TDS deduction. Hence moved the code to insert JAI_AP_TDS_PREPAYMENTS
1416 after validation of Invoice.*/
1417 --To handle the condition, if there are PP applications/Unapplications, before the SI is validated
1418 if pv_action = jai_constants.updating then
1419 IF pr_new.line_type_lookup_code = 'PREPAY' THEN
1420 lv_prepay_flag := NULL;
1421 --Apply Scenario
1422 IF NVL(pr_new.amount,0) < 0 THEN
1423
1424 OPEN c_check_prepayment_apply;
1425 FETCH c_check_prepayment_apply INTO lv_prepay_flag;
1426 CLOSE c_check_prepayment_apply;
1427
1428 --Unapply Scenario
1429 ELSIF NVL(pr_new.amount,0) > 0 THEN
1430
1431 OPEN c_check_prepayment_unapply;
1432 FETCH c_check_prepayment_unapply INTO lv_prepay_flag;
1433 CLOSE c_check_prepayment_unapply;
1434
1435 END IF;
1436 /*Verify if Invoice is processed by process_tds_at_inv_validate before processing Prepayments*/
1437 SELECT max(process_status) INTO ln_processed
1438 FROM jai_ap_tds_inv_taxes
1439 WHERE invoice_id = pr_new.invoice_id;
1440 --should be run, only if prepayment application/unapplication is not already processed
1441 IF lv_prepay_flag IS NULL AND ln_processed = 'P' THEN
1442 lv_process_old_trxn := 'N';
1443 process_prepayment(cp_event => 'UPDATE');
1444 END IF;
1445
1446 END IF;
1447 end if;
1448 /*Bug 12630283 - End*/
1449 /* Intercepting Validate event */
1450 if pv_action = jai_constants.updating then
1451
1452 --Update by Chong.Lei for ZX integration test 20120411 start
1453 -- if nvl(pr_old.match_status_flag, 'Q') <> nvl(pr_new.match_status_flag, 'Q') and
1454 -- pr_new.match_status_flag = 'A' and
1455 if
1456 --Update by Chong.Lei for ZX integration test 20120411 end
1457 c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
1458 c_rec_ap_invoices_all.cancelled_date is null
1459 then
1460
1461 /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
1462 Don't proceed for TDS invoice creation if the invoice type
1463 is either 'CREDIT' or 'DEBIT'
1464 */
1465
1466 If c_rec_ap_invoices_all.invoice_type_lookup_code
1467 IN ('CREDIT', 'DEBIT')
1468 Then
1469 return;
1470 End if;
1471
1472 lv_codepath := null;
1473
1474 --Added by Junjian for ZX intergration on 19-Oct-2012 begin
1475 jai_ap_match_tax_proc_pkg.tax_check_at_inv_validate
1476 (
1477 p_invoice_id => pr_new.invoice_id,
1478 p_invoice_line_number => pr_new.invoice_line_number,
1479 p_process_flag => lv_process_flag,
1480 p_process_message => lv_process_message,
1481 p_codepath => lv_codepath
1482 );
1483 if nvl(lv_process_flag, 'N') = 'E' then
1484 pv_return_code := jai_constants.expected_error ;
1485 pv_return_message :=
1486 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ;
1487 return ;
1488 end if;
1489
1490 lv_codepath := null;
1491 --Added by Junjian for ZX intergration on 19-Oct-2012 end
1492
1493 jai_ap_tds_generation_pkg.status_update_chk_validate
1494 (
1495 p_invoice_id => pr_new.invoice_id,
1496 p_invoice_line_number => pr_new.invoice_line_number, /* AP Lines*/
1497 p_invoice_distribution_id => pr_new.invoice_distribution_id,
1498 p_match_status_flag => pr_new.match_status_flag,
1499 p_is_invoice_validated => lv_is_invoice_validated,
1500 p_process_flag => lv_process_flag,
1501 p_process_message => lv_process_message,
1502 p_codepath => lv_codepath
1503 );
1504
1505 if nvl(lv_process_flag, 'N') = 'E' then
1506 /* raise_application_error(-20005,
1507 '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 :=
1508 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ; return ;
1509 end if;
1510
1511 --if lv_is_invoice_validated = 'Y' then --Comment by Zhiwei Hou for bug#13359892 DTC on 20120103
1512
1513 lv_codepath := null;
1514 --Updated by zhiwei for DTC bug#13359892 on 20111219 begin
1515 ------------------------------------------------------------------
1516 jai_ap_dtc_generation_pkg.process_dtc_at_inv_validate
1517 --jai_ap_tds_generation_pkg.process_tds_at_inv_validate
1518 ------------------------------------------------------------------
1519 --Updated by zhiwei for DTC bug#13359892 on 20111219 end
1520 (
1521 p_invoice_id => pr_new.invoice_id,
1522 p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1523 p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1524 p_accounting_date => pr_new.accounting_date,
1525 p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1526 p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1527 p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1528 p_org_id => pr_new.org_id,
1529 p_call_from => 'ja_in_ap_aida_before_trg',
1530 p_creation_date => pr_new.creation_date,/*Bug 5989740 bduvarag*/
1531 p_process_flag => lv_process_flag,
1532 p_process_message => lv_process_message,
1533 p_codepath => lv_codepath
1534 );
1535
1536 if nvl(lv_process_flag, 'N') = 'E' then
1537 /* raise_application_error(-20006,
1538 '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 :=
1539 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ; return ;
1540 end if;
1541
1542 --end if; --moved this from below for Bug#7433241--Comment by Zhiwei Hou for bug#13359892 DTC on 20120103
1543
1544 -- end if; --commented by bgowrava for Bug#7433241, moved this end if to a different place at the top.
1545 /*START, Added by Bgowrava for Bug#9457695*/
1546 if pr_new.global_attribute1 is null and ln_final_tds_tax_id is not null then
1547 pr_new.global_attribute1 := to_char(ln_final_tds_tax_id);
1548 end if;
1549
1550 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
1551 pr_new.global_attribute_category := 'JA.IN.APXINWKB.DISTRIBUTIONS';
1552 end if;
1553 /*END, Added by Bgowrava for Bug#9457695*/
1554
1555 end if; /* pr_old.match_status_flag <> pr_new.match_status_flag */
1556 end if; /* updating */
1557 /* Intercepting Validate event */
1558
1559
1560 if pv_action = jai_constants.inserting or pv_action = jai_constants.updating then
1561
1562 /*START, by amandali for Bug#10430662*/
1563 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
1564 pr_new.global_attribute_category := 'JA.IN.APXINWKB.DISTRIBUTIONS';
1565 end if;
1566 if (pr_new.po_distribution_id is not null and pr_new.global_attribute2 is null ) then
1567
1568 open c_get_shipment_line_id(pr_new.rcv_transaction_id);
1569 fetch c_get_shipment_line_id into v_shipment_line_id;
1570 close c_get_shipment_line_id;
1571
1572 if v_shipment_line_id is not null then
1573
1574 open c_receipt_wct_tax(v_shipment_line_id);
1575 fetch c_receipt_wct_tax into v_receipt_wct_tax_id;
1576 close c_receipt_wct_tax;
1577 end if;
1578
1579 if v_receipt_wct_tax_id is not null then
1580 pr_new.global_attribute2 := v_receipt_wct_tax_id;
1581 pr_new.global_attribute_category := 'JA.IN.APXINWKB.DISTRIBUTIONS';
1582 end if;
1583
1584 if pr_new.global_attribute2 is null then
1585 open from_po_distributions(pr_new.po_distribution_id);
1586 fetch from_po_distributions into from_po_distributions_rec;
1587 close from_po_distributions;
1588
1589 open from_line_locations(from_po_distributions_rec.line_location_id, from_po_distributions_rec.po_line_id );
1590 fetch from_line_locations into from_line_locations_rec;
1591 close from_line_locations;
1592
1593 open c_line_location_wct_taxes(from_line_locations_rec.line_focus_id);
1594 fetch c_line_location_wct_taxes into c_line_location_wct_taxes_rec;
1595 close c_line_location_wct_taxes;
1596
1597 pr_new.global_attribute2 := c_line_location_wct_taxes_rec.tax_id ;
1598 pr_new.global_attribute_category := 'JA.IN.APXINWKB.DISTRIBUTIONS';
1599 end if;
1600
1601 end if;
1602 end if;
1603 /*END, by amandali for Bug#10430662*/
1604 /* Prepayment functionality */
1605 if pv_action = jai_constants.inserting then
1606 if pr_new.line_type_lookup_code = 'PREPAY' and
1607 c_rec_ap_invoices_all.source <> 'INDIA TDS' and /*'TDS' and --Ramanand for bug#4388958 */
1608 c_rec_ap_invoices_all.cancelled_date is null
1609 then
1610
1611 /* Bug#5131075(4683207). Added by Lakshmi Gopalsami
1612 Don't proceed for TDS invoice creation if the invoice type
1613 is either 'CREDIT' or 'DEBIT'
1614 */
1615
1616 If c_rec_ap_invoices_all.invoice_type_lookup_code
1617 IN ('CREDIT', 'DEBIT')
1618 Then
1619 return;
1620 End if;
1621
1622
1623 --Start Added by Sanjikum for Bug#5131075(4722011)
1624 --If SI is not validated at this stage, then return
1625
1626 lv_codepath := null;
1627
1628 jai_ap_tds_generation_pkg.status_update_chk_validate
1629 (
1630 p_invoice_id => pr_new.invoice_id,
1631 /* p_invoice_line_id => null, Future use AP Lines
1632 /*p_invoice_distribution_id => null,*/
1633 p_match_status_flag => pr_new.match_status_flag, --Changed by Sanjikum for Bug#5131075(4722011)
1634 p_is_invoice_validated => lv_is_invoice_validated,
1635 p_process_flag => lv_process_flag,
1636 p_process_message => lv_process_message,
1637 p_codepath => lv_codepath
1638 );
1639
1640 if nvl(lv_process_flag, 'N') = 'E' then
1641 raise_application_error(-20005,
1642 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
1643 end if;
1644
1645 IF lv_is_invoice_validated = 'N' THEN
1646 RETURN;
1647 END IF;
1648
1649 process_prepayment(cp_event => 'INSERT'); --Added parameter cp_event for Bug 8431516
1650
1651 --Removed the existing code for Handling PP and moved it into inline procedure process_prepayment
1652 --End Added by Sanjikum for Bug#5131075(4722011)
1653
1654
1655 /*--Check if the prepayment transaction should be processed by the code before
1656 --TDS Clean up or after TDS clean up.
1657
1658 --if SI is created in the new regime and also the Prepay is created in the new regime,
1659 --then code should invoke the new regime or else
1660 --old concurrent shd be invoked
1661
1662
1663 --Check for SI
1664 jai_ap_tds_tax_defaultation.check_old_transaction
1665 (
1666 p_invoice_id => pr_new.invoice_id,
1667 p_new_transaction => lv_new_transaction_si
1668 );
1669
1670 --Check for Prepayment
1671 jai_ap_tds_tax_defaultation.check_old_transaction
1672 (
1673 p_invoice_distribution_id => pr_new.prepay_distribution_id,
1674 p_new_transaction => lv_new_transaction_pp
1675 );
1676
1677 if lv_new_transaction_si = 'Y' and lv_new_transaction_pp = 'Y' then
1678
1679 lv_codepath := null;
1680
1681 jai_ap_tds_prepayments_pkg.process_prepayment
1682 (
1683 p_invoice_id => pr_new.invoice_id,
1684 p_invoice_distribution_id => pr_new.invoice_distribution_id,
1685 p_prepay_distribution_id => pr_new.prepay_distribution_id,
1686 p_parent_reversal_id => pr_new.parent_reversal_id,
1687 p_prepay_amount => pr_new.amount,
1688 p_vendor_id => c_rec_ap_invoices_all.vendor_id,
1689 p_vendor_site_id => c_rec_ap_invoices_all.vendor_site_id,
1690 p_accounting_date => pr_new.accounting_date,
1691 p_invoice_currency_code => c_rec_ap_invoices_all.invoice_currency_code,
1692 p_exchange_rate => c_rec_ap_invoices_all.exchange_rate,
1693 p_set_of_books_id => c_rec_ap_invoices_all.set_of_books_id,
1694 p_org_id => pr_new.org_id,
1695 p_process_flag => lv_process_flag,
1696 p_process_message => lv_process_message,
1697 p_codepath => lv_codepath
1698 );
1699
1700 if nvl(lv_process_flag, 'N') = 'E' then
1701 -- raise_application_error(-20007,
1702 --'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
1703 pv_return_code := jai_constants.expected_error ;
1704 pv_return_message := 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ;
1705 return ;
1706 end if;
1707
1708 else
1709 --
1710 --|| Invoke the old regime functionality
1711 --
1712 jai_ap_tds_prepayments_pkg.process_old_transaction
1713 (
1714 p_invoice_id => pr_new.invoice_id,
1715 p_invoice_distribution_id => pr_new.invoice_distribution_id,
1716 p_prepay_distribution_id => pr_new.prepay_distribution_id,
1717 p_amount => pr_new.amount,
1718 p_last_updated_by => pr_new.last_updated_by,
1719 p_last_update_date => pr_new.last_update_date,
1720 p_created_by => pr_new.created_by,
1721 p_creation_date => pr_new.creation_date,
1722 p_org_id => pr_new.org_id,
1723 p_process_flag => lv_process_flag,
1724 p_process_message => lv_process_message
1725 );
1726
1727 if nvl(lv_process_flag, 'N') = 'E' then
1728 --raise_application_error(-20008,
1729 --'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message);
1730 pv_return_code := jai_constants.expected_error ;
1731 pv_return_message := 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ;
1732 return ;
1733 end if;
1734
1735
1736 end if; --Transactions in new regime*/
1737
1738
1739 end if; /* Prepayment functionality */
1740
1741 /*Code Started start changes for bug 6493858 - cancellation functionality*/
1742 IF pr_new.cancellation_flag = 'Y' AND c_rec_ap_invoices_all.source <> 'INDIA TDS'
1743 THEN
1744 --commented the following for bug#9052839
1745 /*OPEN c_tds_invoice_id(pr_new.invoice_id);
1746 FETCH c_tds_invoice_id INTO r_tds_invoice_id;
1747 CLOSE c_tds_invoice_id;*/
1748
1749 --added the FOR loop for bug#9052839
1750 FOR r_tds_invoice_id IN c_tds_invoice_id (pr_new.invoice_id)
1751 LOOP
1752 IF r_tds_invoice_id.invoice_id IS NOT NULL THEN
1753 lv_invoice_payment_status := get_invoice_payment_status(r_tds_invoice_id.invoice_id);
1754
1755 IF NVL(lv_invoice_payment_status,'N') <> 'N' THEN
1756 raise_application_error(-20011,
1757 'Invoice to TDS Authority - '||r_tds_invoice_id.invoice_num||' is already paid. Current invoice can''t be cancelled' );
1758 END IF;
1759 END IF;
1760 END LOOP;
1761 END IF;
1762 /*end changes for bug 6493858 -Code Ended */
1763
1764 /* Bug 8330522. Added by Lakshmi Gopalsami */
1765 IF pr_new.cancellation_flag = 'Y' AND c_rec_ap_invoices_all.source = 'INDIA TDS'
1766 AND c_rec_ap_invoices_all.vendor_type_lookup_code <> 'INDIA TDS AUTHORITY'
1767 THEN
1768 --commented the following for bug#9052839
1769 /*OPEN c_tds_invoice_id1(pr_new.invoice_id);
1770 FETCH c_tds_invoice_id1 INTO r_tds_invoice_id;
1771 CLOSE c_tds_invoice_id1;*/
1772
1773 --added the FOR loop for bug#9052839
1774 FOR r_tds_invoice_id IN c_tds_invoice_id1 (pr_new.invoice_id)
1775 LOOP
1776 IF r_tds_invoice_id.invoice_id IS NOT NULL THEN
1777 lv_invoice_payment_status := get_invoice_payment_status(
1778 r_tds_invoice_id.invoice_id);
1779
1780 IF NVL(lv_invoice_payment_status,'N') <> 'N' THEN
1781 raise_application_error(-20011,
1782 'Invoice to TDS Authority - '||r_tds_invoice_id.invoice_num||' is already paid. TDS Credit memo to supplier invoice can''t be cancelled' );
1783 END IF;
1784 END IF;
1785 END LOOP;
1786 END IF;
1787 /*End for bug 8330522*/
1788
1789 end if; /* inserting */
1790
1791 if pv_action = jai_constants.deleting then
1792
1793 jai_ap_tds_tax_defaultation.process_delete
1794 (
1795 p_invoice_id => pr_old.invoice_id,
1796 p_invoice_line_number => pr_new.invoice_line_number, /* AP Lines*/
1797 p_invoice_distribution_id => pr_old.invoice_distribution_id,
1798 p_process_flag => lv_process_flag,
1799 P_process_message => lv_process_message
1800 );
1801
1802 if nvl(lv_process_flag, 'N') = 'E' then
1803 /* raise_application_error(-20009,
1804 '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 :=
1805 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || lv_process_message ; return ;
1806 end if;
1807
1808 end if; /* Deleting */
1809
1810
1811 exception
1812 when others then
1813 --raise_application_error(-20010, 'Error - trigger ja_in_ap_aida_before_trg on ap_invoice_distributions_all : ' || sqlerrm);
1814 Pv_return_code := jai_constants.unexpected_error;
1815 Pv_return_message := 'Encountered an error in JAI_AP_IDA_TRIGGER_DTC_PKG.BRIUD_DTC_T1 ' ||
1816 'Error on ap_invoice_distributions_all : ' || substr(sqlerrm,1,1900);
1817
1818 END BRIUD_DTC_T1 ;
1819
1820 END JAI_AP_IDA_TRIGGER_DTC_PKG ;
[Home] [Help]