1 PACKAGE BODY JAI_JAR_TL_TRIGGER_PKG AS
2 /* $Header: jai_jar_tl_t.plb 120.22 2008/02/25 04:32:14 ssumaith ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME ARI_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_JAR_TL_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_JAR_TL_ARI_T1
11 REM
12 REM +======================================================================+
13 */
14 PROCEDURE ARI_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 ) IS
15 v_line_no NUMBER := 0;
16 v_books_id NUMBER := 1;
17 v_salesrep_id NUMBER;
18 v_line_type VARCHAR2(30);
19 v_vat_tax NUMBER;
20 v_ccid NUMBER;
21 v_cust_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.customer_trx_line_id%TYPE;
22 /* commented by rallamse bug#4479131 PADDR Elimination
23 v_paddr v$session.paddr%TYPE;
24 */
25 v_customer_trx_line_id NUMBER; -- := pr_new.customer_trx_line_id; --Ramananda for File.Sql.35
26 v_customer_trx_id NUMBER; -- := pr_new.customer_trx_id; --Ramananda for File.Sql.35
27 v_created_from VARCHAR2(30);
28 c_from_currency_code VARCHAR2(15);
29 c_conversion_type VARCHAR2(30);
30 c_conversion_date DATE;
31 c_conversion_rate NUMBER := 0;
32 v_converted_rate NUMBER := 1;
33 req_id NUMBER;
34 result BOOLEAN;
35 v_organization_id NUMBER ;
36 v_location_id NUMBER ;
37 v_batch_source_id NUMBER ;
38 v_register_code VARCHAR2(50);
39 v_order_number VARCHAR2(30);
40 v_order_type ra_customer_trx_all.interface_header_attribute2%type;
41 v_org_id NUMBER(15); -- added by sriram because the orgid value is not going into temp_lines_insert table
42
43 --v_ORDER_PRICE_EXCISE_INCLUSIVE JAI_CMN_INVENTORY_ORGS.ORDER_PRICE_EXCISE_INCLUSIVE%type; ---- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22, TD18
44
45 ln_inv_curr_precision NUMBER; /* added by CSahoo - bug# 5364120*/
46 --commented by kunkumar for bug#6066813
47 lv_intf_hdr_ctx ra_customer_trx_all.interface_header_context%type; /* bug# 6012570 (5876390) */
48
49 /* added by CSahoo - bug# 5364120*/
50 CURSOR c_inv_curr_precision(cp_currency_code varchar2)
51 IS
52 SELECT NVL(minimum_accountable_unit,NVL(precision,2)) curr_precision
53 FROM fnd_currencies
54 WHERE currency_code = cp_currency_code;
55
56
57
58
59 CURSOR tax_type_cur
60 IS
61 SELECT
62 a.tax_id taxid ,
63 a.tax_rate ,
64 a.uom uom ,
65 a.tax_amount tax_amt ,
66 b.tax_type t_type ,
67 a.customer_trx_line_id line_id ,
68 a.tax_line_no tax_line_no -- added by sriram - 10/4/2003 - bug # 2769439
69 FROM
70 JAI_AR_TRX_TAX_LINES a ,
71 JAI_CMN_TAXES_ALL b
72 WHERE
73 link_to_cust_trx_line_id = v_customer_trx_line_id AND
74 a.tax_id = b.tax_id
75 AND NVL(b.inclusive_tax_flag,'N') = 'N' --Added by Jia Li for Tax inclusive Computations on 2007/11/22, TD11
76 ORDER BY
77 1;
78
79 /* Bug 4535701. Added by Lakshmi gopalsami
80 * Commented the following cursor as part of PADDR elimiation
81 CURSOR PADDR_CUR IS
82 SELECT A.paddr
83 FROM JAI_CMN_LOCATORS_T A , v$session s
84 WHERE A.PADDR = s.paddr
85 AND s.audsid = USERENV('SESSIONID');
86 */
87 CURSOR HEADER_INFO_CUR IS
88 SELECT set_of_books_id, primary_salesrep_id, org_id , invoice_currency_code, exchange_rate_type,
89 exchange_date, exchange_rate
90 --commented by kunkumar for Bug#6066813
91 ,interface_header_context /* 6012570 (5876390) */
92 FROM RA_CUSTOMER_TRX_ALL
93 WHERE customer_trx_id = v_customer_trx_id;
94
95 --Commented for bug#4468353
96 /*
97 CURSOR VAT_TAX_CUR(cp_tax_code AR_VAT_TAX_ALL.tax_code%type) IS
98 SELECT MAX(vat_tax_id)
99 FROM AR_VAT_TAX_ALL
100 WHERE tax_code = cp_tax_code ;
101 */
102
103 /* Added by Ramananda for bug#4468353 , start */
104 CURSOR ORG_CUR IS
105 SELECT ORG_ID
106 FROM RA_CUSTOMER_TRX_ALL
107 WHERE CUSTOMER_TRX_ID = pr_new.customer_trx_id;
108
109 lv_tax_regime_code zx_rates_b.tax_regime_code%type ;
110 ln_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%type ;
111 ln_tax_rate_id zx_rates_b.tax_rate_id%type ;
112 /* Added by Ramananda for bug#4468353 , end */
113
114
115 CURSOR TAX_CCID_CUR(p_tax_id IN NUMBER) IS
116 SELECT tax_account_id
117 FROM JAI_CMN_TAXES_ALL B
118 WHERE B.tax_id = p_tax_id ;
119
120 CURSOR CREATED_FROM_CUR IS
121 SELECT created_from, interface_header_attribute1,interface_header_attribute2
122 FROM ra_customer_trx_all
123 WHERE customer_trx_id = v_customer_trx_id;
124 CURSOR SO_AR_HDR_INFO IS
125 SELECT organization_id, location_id, batch_source_id
126 FROM JAI_AR_TRXS
127 WHERE Customer_Trx_ID = v_customer_trx_id;
128 CURSOR register_code_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER,
129 p_batch_source_id IN NUMBER) IS
130 SELECT register_code
131 FROM JAI_OM_OE_BOND_REG_HDRS
132 WHERE organization_id = p_org_id AND location_id = p_loc_id AND
133 register_id IN (SELECT register_id
134 FROM JAI_OM_OE_BOND_REG_DTLS
135 WHERE order_type_id = p_batch_source_id AND order_flag = 'N');
136
137 /* Bug 4938350. Added by Lakshmi Gopalsami
138 Removed the sub-query and added oe_transaction_types_tl
139 Removed parameter p_order_number
140 */
141 CURSOR register_code_cur1(p_organization_id NUMBER,
142 p_location_id NUMBER,
143 p_order_type varchar2) IS
144 SELECT A.register_code
145 FROM JAI_OM_OE_BOND_REG_HDRS A,
146 JAI_OM_OE_BOND_REG_DTLS b,
147 oe_transaction_types_tl ott
148 WHERE A.organization_id = p_organization_id
149 AND A.location_id = p_location_id
150 AND A.register_id = b.register_id
151 AND b.order_flag = 'Y'
152 AND b.order_type_id = ott.transaction_type_id
153 AND ott.NAME = p_order_type;
154
155 /* Bug 5243532. Added by Lakshmi Gopalsami
156 * Removed cursor set_of_books_cur and c_opr_set_of_books_id
157 * and implemented caching logic.
158 */
159 CURSOR trx_num(v_cust_trx_id NUMBER) IS SELECT
160 trx_number FROM ra_customer_trx_all WHERE
161 customer_trx_id = v_cust_trx_id;
162
163 -- following cursor added by sriram -3266982
164
165 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 Begin
166 -- TD18-changed Trading AR Invoice
167 -------------------------------------------------------------------------
168 /*
169 cursor c_ORDER_PRICE_EXCISE_INCLUSIVE(p_organization_id number,p_location_id number) is
170 select ORDER_PRICE_EXCISE_INCLUSIVE
171 from JAI_CMN_INVENTORY_ORGS
172 where organization_id = p_organization_id
173 and location_id = p_location_id;
174 */
175 -------------------------------------------------------------------------
176 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 End
177
178 CURSOR cur_chk_rgm ( cp_tax_type JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE )
179 IS
180 SELECT
181 regime_id ,
182 regime_code
183 FROM
184 jai_regime_tax_types_v jrttv
185 WHERE
186 upper(jrttv.tax_type) = upper(cp_tax_type);
187
188
189 ln_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
190 lv_attr_value JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE;
191
192 -- Start of bug 4089440
193 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
194 lv_error_flag VARCHAR2(2) ;
195 lv_error_message VARCHAR2(4000) ;
196 -- End of bug 4089440
197
198 v_err_mesg VARCHAR2(250) ;
199 v_trx_num ra_customer_trx_all.trx_number%TYPE ;
200
201 /* Bug 5243532. Added by Lakshmi Gopalsami
202 * Defined variable for implementing caching logic
203 */
204 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
205
206 BEGIN
207 pv_return_code := jai_constants.successful ;
208 /*------------------------------------------------------------------------------------------
209 FILENAME: JA_IN_APPS_AR_LINES_INSERT_TRG.sql
210 CHANGE HISTORY:
211 S.No Date Author and Details
212 1. 10-Jun-01 Jagdish / Subbu
213 To populate Tax_rate column in RA_CUSTOMER_TRX_LINES_ALL
214 table as receipts with discounts doesn't get saved.
215 2. 2001/06/22 Anuradha Parthasarathy
216 Commented and Added code for better performance.
217
218 3. 2002/03/22 RPK
219 added the column error_flag to the table 'JAI_AR_TRX_INS_LINES_T'
220 added the cursor trx_num to get the invoice num.
221
222 4 2002/04/22 RPK
223 BUG#2334972.
224 Code modified to allow the taxes to be inserted into
225 the table JAI_AR_TRX_INS_LINES_T.When an order line is having 2 tax amounts
226 one with a positive value and the other with a negative value(eg:100 and -100)
227 then these lines were not getting transferred to AR Tables and the same is
228 required to be transferred to have the proper accounting of the GL/AR.For this,
229 the when_clause of this trigger is commented to facilitate the execution of this
230 code.This is required to facilitate the functionality of having discounts in the
231 OM/AR tax flow.
232
233 5. 2002/05/04 Added the If Condition to ensure that the concurrent request to.
234 AR Tax and Freight Defaultation gets invoked only for manual invoices
235
236 6. 2002/05/06 Added the Source Column in the insert statement of Ja-In_temp_lines_insert
237 table.
238
239 8. 2003/04/07 SSUMAITH.Bug # 2779967
240 column Org id was not inserted in the JAI_AR_TRX_INS_LINES_T table. This needs to be
241 inserted in the table , because when processing the records using the 'India Local Concurrent'
242 only records that belong to the orgid of the current responsiblity needs to be picked up.
243
244 9. 2003/04/10 SSUMAITH - Bug # 2769439
245 Also inserting the tax line number in the JAI_AR_TRX_INS_LINES_T table.
246 This is necessary for creating links between OM and AR.
247
248 10. 2003/11/20 SSUMAITH - Bug # 3266982 File Version 617.1
249 The cursor which fetches order number was still pointing to So_headers_all table.
250 This caused the query to fetch no records, causing the code not to execute based on
251 register types.
252
253 This bug introduces dependency . Because as part of this bug , a new column ORDER_PRICE_EXCISE_INCLUSIVE
254 is added into the table - ja_in_hr_organzization_units.
255
256 If the flag value of ORDER_PRICE_EXCISE_INCLUSIVE becomes 'Y' then the excise tax will go as zero
257 else , the normal excise value will go to the base apps.
258
259 Also , a join between oe_order_headers_all and oe_transaction_types_tl has been added.
260 Without this join, if the same sales order number is associated to multiple order types,
261 there is scope for the wrong order number to be chosen and comparison done on that basis.
262
263 With this join condition, the value in the ra_customer_trx_all.interface_header_attribute2 is
264 compared to the 'Name' field in the oe_transaction_types_tl.
265
266 11. 2005/27/01 aiyer - Bug # 4089440 File Version 115.1
267 Issue:-
268 In case of service invoices having service/service_education type of taxes the code combination id should be picked
269 up from regime tax setup. This is being done as a part of the service tax enhancement
270
271 Solution:-
272 The check that, for service type of taxes ccid should be picked up from regime tax setup has been impletemented.
273 Called the procedure jai_ar_rgm_processing_pkg.get_regime_info is being called for regime setup validation.
274 Aslo called the function jai_cmn_rgm_recording_pkg.get_account to get the ccid in such cases.
275
276 Dependency introduced as a part of this bug:-
277 This file should be released on top of Bug 4146708.
278
279 12. 01-Mar-2005 aiyer - Bug # 4212816 File Version 115.2
280 Issue:-
281 In case of invoices having any of the taxes setup for VAT REGIME, the code combination should get picked up from the
282 VAT Regime setup. This is being done as a part of the VAT enhancement
283
284 Solution:-
285 The check that, for taxes belonging to the VAT regime, ccid should be picked up from VAT regime setup has been impletemented.
286 The account info is fetched using the function jai_cmn_rgm_recording_pkg.get_account.
287
288 Dependency introduced as a part of this bug:-
289 This file should be released on top of Bug 4245089.
290 Datamodel changes for VAT
291
292 13. 02-Apr-2005 aiyer - Bug # 4279702 File Version 115.3
293 Issue:-
294 VAT regime code does not get reinitialized for any tax type which does not fall in the VAT / Service TAx regime
295
296 Solution:-
297 Changed the triggers ja_in_apps_lines_insert_trg and ja_in_apps_ar_lines_update_trg to reinitialize the regime codes
298 every time processing happens for a tax type.
299
300 Dependency introduced as a part of this bug:-
301 This file should be released on top of Bug 4245089.
302
303 14 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
304 DB Entity as required for CASE COMPLAINCE. Version 116.1
305
306 15. 13-Jun-2005 File Version: 116.2
307 Ramananda for bug#4428980. Removal of SQL LITERALs is done
308
309 16 06-Jul-2005 rallamse for bug# PADDR Elimination
310 1. Commented use of v_paddr and cursor PADDR_CUR
311
312 17 04-Aug-2005 Bug4535701. Added by Lakshmi Gopalsami Version 120.2
313 Commented the cursor which is used for PADDR
314
315 18 25-Jan-2007 CSahoo for Bug#5631784, File Version 120.3
316 Forward Porting of BUG#4742259
317 Accounting information popluated for TCS regime
318
319 19 14-FEB-2007 CSahoo - bug# 5364120 - file version 120.4
320 Forward Porting Of 11i BUG 5346489.
321
322 The transaction amounts are rounded based on the currency precision of the invoice currency code
323 when the insert id done in the JAI_AR_TRX_INS_LINES_T.
324 Added the cursor c_inv_curr_precision.
325
326 Dependency due to this bug :- None
327
328
329 19. 15-Feb-2007 CSahoo Bug# 5390583, File Version 120.5
330 Forward Porting of 11i Bug 5357400
331 When taxes are added manually to the ar invoice from the transactions localised screen and the
332 batch source is set to bond register, the excise taxes are going to base AR. This should not go.
333
334 Fix :
335 The code to take care of this was in a trading loop and would never reach there.
336 Modified the code appropriately.
337
338 20 24/04/2007 cbabu for bug#6012570 (5876390), File Version 120.7 (115.14 )
339 FP: For project billing bond register functionality is not required hence added a check to see if
340 the invoice is created by projects then bond register related logic should not be executed.
341
342 21 25/05/2007 sacsethi for bug 6072461 file version 120.11
343
344 Problem - MANUAL AR TRANSACTION GIVES PROBLEM WITH VAT TAXES
345
346 when we creating transactions in receivalbles , at that time at time of saving , it was
347 giving error , vat setup is not defined but vat setup was defined
348
349 Solution - At time of get account of vat , we were passing wrong value of organzation id and location id .
350
351 22. 31/05/2007 csahoo for bug#6081806, File version 120.13
352 added the sh cess types.
353
354 23. 14/06/2007 sacsethi for bug 6072461 for file version - 120.15
355
356 This bug is used to fp 11i bug 5183031 for vat reveresal
357
358 Problem - Vat Reversal Enhancement not forward ported
359 Solution - Changes has been done to make it compatible for vat reversal functioanlity also.
360 ==========================================================================================================================================================
361
362 Future Dependencies For the release Of this Object:-
363 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
364 A datamodel change )
365
366 ----------------------------------------------------------------------------------------------------------------------------------------------------
367 Current Version Current Bug Dependent Files Version Author Date Remarks
368 Of File On Bug/Patchset Dependent On
369 ja_in_apps_ar_lines_insert_trg.sql
370
371 617.1 3266982 IN60105D1 617.1 ssumaith 03/12/2003
372
373 115.1 4089440 IN60105D2 + Aiyer 27-Jan-2005 4146708 is the release bug for SERVICE/CESS
374 4146708 enhancement release
375 115.2 4247989 IN60106 + 4146708 + 4245089 Aiyer 01-Mar-2005 4245089 is the Base bug for VAT.
376 This has datamodel changes for VAT.
377 24. 22/11/2007 Added by Jia Li
378 for Tax inclusive Computations
379 --------------------------------------------------------------------------------------------------------------------------------------------------------*/
380
381 v_customer_trx_line_id := pr_new.customer_trx_line_id; --Ramananda for File.Sql.35
382 v_customer_trx_id := pr_new.customer_trx_id; --Ramananda for File.Sql.35
383
384 --added the following block to capture the trx_number on 22-Mar-2002
385
386 OPEN trx_num(pr_new.Customer_trx_id);
387 FETCH trx_num INTO v_trx_num;
388 CLOSE trx_num;
389 --end addition 22-Mar-2002
390 OPEN CREATED_FROM_CUR;
391 FETCH CREATED_FROM_CUR INTO v_created_from , v_order_number, v_order_type;
392 CLOSE CREATED_FROM_CUR;
393 IF v_created_from IN ('ARXREC') THEN
394 RETURN;
395 END IF;
396
397 /* Commented rallamse bug#4479131 PADDR Elimination
398 OPEN PADDR_CUR;
399 FETCH PADDR_CUR INTO v_paddr;
400 CLOSE PADDR_CUR;
401 */
402
403 OPEN HEADER_INFO_CUR;
404 FETCH HEADER_INFO_CUR INTO v_books_id, v_salesrep_id,v_org_id , c_from_currency_code,
405 c_conversion_type, c_conversion_date, c_conversion_rate, lv_intf_hdr_ctx;
406 CLOSE HEADER_INFO_CUR;
407
408 /* start additions by CSahoo - bug#5364120 */
409
410 IF c_from_currency_code IS NOT NULL THEN
411 OPEN c_inv_Curr_precision(c_from_currency_code);
412 FETCH c_inv_curr_precision INTO ln_inv_curr_precision;
413 CLOSE c_inv_curr_precision;
414 END IF;
415
416 IF ln_inv_curr_precision is NULL THEN
417 ln_inv_curr_precision := 0;
418 END IF;
419
420 /* end additions by CSahoo - bug#5364120 */
421
422 --Commented for bug#4468353
423 /*
424 OPEN VAT_TAX_CUR('Localization');
425 FETCH VAT_TAX_CUR INTO v_vat_tax;
426 CLOSE VAT_TAX_CUR;
427 */
428
429 /* Added by Ramananda for bug# due to ebtax uptake by AR, start */
430 OPEN ORG_CUR;
431 FETCH ORG_CUR INTO V_ORG_ID;
432 CLOSE ORG_CUR;
433
434 OPEN jai_ar_trx_pkg.c_tax_regime_code_cur(V_ORG_ID);
435 FETCH jai_ar_trx_pkg.c_tax_regime_code_cur INTO lv_tax_regime_code;
436 CLOSE jai_ar_trx_pkg.c_tax_regime_code_cur ;
437
438 OPEN jai_ar_trx_pkg.c_max_tax_rate_id_cur(lv_tax_regime_code);
439 FETCH jai_ar_trx_pkg.c_max_tax_rate_id_cur INTO ln_tax_rate_id;
440 CLOSE jai_ar_trx_pkg.c_max_tax_rate_id_cur ;
441 /* Added by Ramananda for bug# due to ebtax uptake by AR, end */
442
443 IF v_books_id IS NULL THEN
444 /* Bug 5243532. Added by Lakshmi Gopalsami
445 * Removed the cursor set_of_books_cur and implemented using caching logic.
446 */
447 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
448 (p_org_id => v_org_id );
449 v_books_id := l_func_curr_det.ledger_id;
450 END IF;
451
452 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_books_id ,c_from_currency_code ,
453 c_conversion_date ,c_conversion_type, c_conversion_rate);
454 OPEN SO_AR_HDR_INFO ;
455 FETCH SO_AR_HDR_INFO INTO v_organization_id, v_location_id, v_batch_source_id;
456 CLOSE SO_AR_HDR_INFO ;
457
458 /*Commented by kunkumar for Bug#6066813 Start, Bug 6012570 (5876390) */
459 if JAI_AR_RCTLA_TRIGGER_PKG.is_this_projects_context (lv_intf_hdr_ctx) then
460 /* For project invoices, there is no bond register functionality, so no need to do any processing for bond registers
461 If line context is not PROJECT INVOICE then continue the normal processing flow */
462 null;
463 else
464 -- End commented by kunkumar for bug#6066813
465 -- End 6012570 (5876390) */
466
467 IF v_created_from = 'RAXTRX' THEN
468 /* Bug 4938350. Added by Lakshmi Gopalsami
469 Removed the parameter v_order_number
470 */
471 OPEN register_code_cur1(v_organization_id, v_location_id,v_order_type);
472 FETCH register_code_cur1 INTO v_register_code;
473 CLOSE register_code_cur1;
474 ELSIF v_created_from = 'ARXTWMAI' THEN
475 OPEN register_code_cur(v_organization_id, v_location_id, v_batch_source_id);
476 FETCH register_code_cur INTO v_register_code;
477 CLOSE register_code_cur;
478 END IF;
479
480 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 Begin
481 -- TD18-changed Trading AR Invoice
482 -------------------------------------------------------------------------
483 /*
484 open c_ORDER_PRICE_EXCISE_INCLUSIVE(v_organization_id, v_location_id);
485 fetch c_ORDER_PRICE_EXCISE_INCLUSIVE into v_ORDER_PRICE_EXCISE_INCLUSIVE;
486 close c_ORDER_PRICE_EXCISE_INCLUSIVE;
487 */
488 -------------------------------------------------------------------------
489 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 End
490
491 --commented by kunkumar for bug#6066813
492 end if;
493 /* Bug 6012570 (5876390) */
494
495 BEGIN --19-MAR-2002
496 FOR TAX_TYPE_REC IN TAX_TYPE_CUR
497 LOOP /* following if condition added by sriram - bug# 3266982*/
498
499 -- IF nvl(v_ORDER_PRICE_EXCISE_INCLUSIVE,'N') = 'Y' then -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 ,TD18
500
501 IF NVL(v_register_code,'N') IN ('23D_EXPORT_WITHOUT_EXCISE','23D_EXPORT_EXCISE',
502 '23D_DOMESTIC_EXCISE','23D_DOM_WITHOUT_EXCISE','BOND_REG')
503 THEN
504 -- jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess added by csahoo for bug#6081806
505 IF Tax_Type_Rec.T_Type IN ('Excise','Addl. Excise','Other Excise','EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS',jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess) THEN
506 TAX_TYPE_REC.tax_amt := 0;
507 END IF;
508 END IF;
509 -- END IF; -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 ,TD18
510
511 /*
512 Date 14-jun-2007 by sacsethi for bug 6072461
513 */
514
515 IF ( upper(Tax_Type_Rec.T_Type) = 'VAT REVERSAL' )
516 THEN
517 TAX_TYPE_REC.tax_amt := 0;
518 END IF;
519
520
521 /*
522 || added by CSahoo - bug# 5390583 In case of bond reg , the excise taxes should not go to base AR tables.
523 */
524 IF NVL(v_register_code,'N') = 'BOND_REG' THEN
525 -- jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess added by csahoo for bug#6081806
526 IF upper(Tax_Type_Rec.T_Type) IN ('EXCISE','ADDL. EXCISE','OTHER EXCISE','EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS',jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess) THEN
527 TAX_TYPE_REC.tax_amt := 0;
528 END IF;
529 END IF;
530
531 /*
532 || Ends additions by CSahoo - bug# 5390583
533 */
534
535 IF tax_type_rec.t_type = 'Freight' THEN
536 v_line_type := 'FREIGHT';
537 ELSE
538 v_line_type := 'TAX';
539 END IF;
540 /*
541 || Code modified by aiyer for the bug 4279702.
542 || Initializing the regime variables
543 */
544 ln_regime_id := null;
545 ln_regime_code := null;
546
547 /*
548 || Get the regime attached to a transaction tax type
549 */
550 OPEN cur_chk_rgm ( cp_tax_type => tax_type_rec.t_type);
551 FETCH cur_chk_rgm INTO ln_regime_id,ln_regime_code ;
552 CLOSE cur_chk_rgm ;
553
554 -- Start of bug 4089440
555 /*
556 || The following code has been added by aiyer for the bug 4089440
557 || IF tax type is SERVICE or SERVICE-CESS then get the account info from regime setup
558 || IF no setup is found then raise an error and stop the transaction.
559 */
560 IF upper(tax_type_rec.t_type) = upper(jai_constants.tax_type_service) OR
561 upper(tax_type_rec.t_type) = upper(jai_constants.tax_type_service_edu_cess)
562 OR upper(tax_type_rec.t_type)= upper(jai_constants.tax_type_sh_service_edu_cess) --added by csahoo for bug#6081806
563 THEN -- Start of A1
564
565 /**********************************************************************************************************
566 || Get the regime id and also validate the Regime/Regime Registratiom Setup Information
567 ***********************************************************************************************************/
568
569 jai_ar_rgm_processing_pkg.get_regime_info ( p_regime_code => jai_constants.service_regime ,
570 p_tax_type_code => tax_type_rec.t_type ,
571 p_regime_id => ln_regime_id ,
572 p_error_flag => lv_error_flag ,
573 p_error_message => lv_error_message
574 );
575
576 IF lv_error_flag <> jai_constants.successful THEN
577 /*
578 || Encountered an error from the call to jai_ar_rgm_processing_pkg.get_regime_info
579 || Stop processing and thorw an error
580 */
581 /* raise_application_error (-20130,lv_error_message);
582 */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_error_message ; return ;
583 END IF ;
584 /**********************************************************************************************************
585 || Get Tax Account Info from the Regime Organization/Regime Registration setup
586 **********************************************************************************************************/
587
588 /*
589 || Get the code combination id from the Organization/Regime Registration setup
590 || by calling the function jai_cmn_rgm_recording_pkg.get_account
591 */
592
593 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
594 p_regime_id => ln_regime_id ,
595 p_organization_type => jai_constants.service_tax_orgn_type ,
596 p_organization_id => v_organization_id ,
597 p_location_id => v_location_id ,
598 p_tax_type => tax_type_rec.t_type ,
599 p_account_name => jai_constants.liability_interim
600 );
601 IF v_ccid IS NULL THEN
602 /*
603 || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
604 || This is an error condition and the current processing has to be stopped
605 */
606 /* raise_application_error (-20130,'Invalid Code combination ,please check the Service Tax - Tax Accounting Setup');
607 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Invalid Code combination ,please check the Service Tax - Tax Accounting Setup' ; return ;
608 END IF;
609 /*
610 || Start of bug 4212816
611 || Code modified by aiyer for the VAT Enhancement
612 || Get the Tax accounting information from the vat regime setup when the taxes are as mentioned below taxes
613 */
614
615 ELSIF UPPER(nvl(ln_regime_code,'####')) = jai_constants.vat_regime THEN
616
617 /*********************************************************************************************************
618 || Validate whether the item attached is vatable or not
619 *********************************************************************************************************/
620 if pr_new.inventory_item_id is not null then /*ssumaith - bug#6104491 */
621 jai_inv_items_pkg.jai_get_attrib (
622 p_regime_code => ln_regime_code ,
623 p_organization_id => v_organization_id ,
624 p_inventory_item_id => pr_new.inventory_item_id ,
625 p_attribute_code => jai_constants.rgm_attr_item_applicable ,
626 p_attribute_value => lv_attr_value ,
627 p_process_flag => lv_error_flag ,
628 p_process_msg => lv_error_message
629 ) ;
630 IF lv_error_flag <> jai_constants.successful THEN
631 /*
632 || Encountered an error from the call to jai_ar_rgm_processing_pkg.get_regime_info
633 || Stop processing and thorw an error
634 */
635 /* raise_application_error (-20130,lv_error_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_error_message ; return ;
636 /*
637 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
638 EXCEPTION_CODE => NULL ,
639 EXCEPTION_TEXT => lv_error_message
640 );
641 */
642
643
644 END IF;
645 end if; /*ssumaith - bug# 6104491 */
646 /*********************************************************************************************************
647 || Raise an error if item is not vatable
648 *********************************************************************************************************/
649 if pr_new.inventory_item_id is not null and nvl(lv_attr_value,'N') = 'N' THEN
650 /* above if condition before the if added by ssumaith - bug# 6104491 */
651 /*
652 || Item is not vatable . Stop processing and throw an error
653 */
654 /* raise_application_error (-20130,'ITEM not vatable'); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'ITEM not vatable' ; return ;
655 /* DO not delete this code, enable this code while doing the messageing project
656 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
657 EXCEPTION_CODE => NULL ,
658 EXCEPTION_TEXT => 'Cannot attach VAT type of taxes to non vatable items.'
659 );
660 */
661
662
663
664 END IF;
665 /*********************************************************************************************************
666 || Get the code combination id from the Organization/Regime Registration setup
667 || by calling the function jai_cmn_rgm_recording_pkg.get_account
668 *********************************************************************************************************/
669
670 -- 25/05/2007 by sacsethi for bug 6072461
671 -- Previously Organziation id was going wrong and location id was wrong
672
673
674 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
675 p_regime_id => ln_regime_id ,
676 p_organization_type => jai_constants.orgn_type_io ,
677 p_organization_id => v_organization_id ,
678 p_location_id => v_location_id ,
679 p_tax_type => tax_type_rec.t_type ,
680 p_account_name => jai_constants.liability_interim
681 );
682 IF v_ccid IS NULL THEN
683 /**********************************************************************************************************
684 || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
685 || This is an error condition and the current processing has to be stopped
686 **********************************************************************************************************/
687 /* raise_application_error (-20130,'Invalid Code combination ,please check the VAT Tax - Tax Accounting Setup'); */
688 pv_return_code := jai_constants.expected_error ;
689 pv_return_message := 'Invalid Code combination ,please check the VAT Tax - Tax Accounting Setup ' ;
690 return ;
691 /*
692 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
693 EXCEPTION_CODE => NULL ,
694 EXCEPTION_TEXT => 'Invalid Code combination ,please check the VAT Tax - Tax Accounting Setup'
695 );
696 */
697 END IF;
698
699 /*
700 || End of bug 4212816
701 */
702
703 /* Added by CSahoo for the TCS enhancements Bug# 5631784 */
704 ELSIF UPPER(nvl(ln_regime_code,'####')) = jai_constants.tcs_regime THEN -- Start of A1
705
706 /*********************************************************************************************************
707 || Get the code combination id from the Organization/Regime Registration setup
708 || by calling the function jai_rgm_trx_recording_pkg.get_account
709 *********************************************************************************************************/
710
711
712 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
713 p_regime_id => ln_regime_id ,
714 p_organization_type => jai_constants.orgn_type_io ,
715 p_organization_id => v_organization_id ,
716 p_location_id => v_location_id ,
717 p_tax_type => tax_type_rec.t_type ,
718 p_account_name => jai_constants.liability_interim
719 );
720 IF v_ccid IS NULL THEN
721 /**********************************************************************************************************
722 || Code Combination id has been returned as null from the function jai_rgm_trx_recording_pkg.get_account
723 || This is an error condition and the current processing has to be stopped
724 **********************************************************************************************************/
725 raise_application_error (-20130,'Invalid Code combination ,please check the TCS Tax - Tax Accounting Setup');
726
727 END IF;
728
729 /*End of bug 5631784 */
730
731 ELSE -- ELSE of A1
732 /*
733 || As tax type is not SERVICE hence
734 || get code combination from tax definition setup
735 */
736 OPEN TAX_CCID_CUR(TAX_TYPE_REC.taxid);
737 FETCH TAX_CCID_CUR INTO v_ccid;
738 CLOSE TAX_CCID_CUR;
739
740 END IF; --End if of A1
741 -- End of bug 4089440
742
743 IF TAX_TYPE_REC.t_type = 'TDS' THEN
744 TAX_TYPE_REC.tax_amt := 0;
745 END IF;
746
747 INSERT INTO JAI_AR_TRX_INS_LINES_T ( -- paddr,
-- 6842749
748 extended_amount,
749 customer_trx_line_id,
750 customer_trx_id,
751 set_of_books_id,
752 link_to_cust_trx_line_id,
753 line_type,
754 uom_code,
755 vat_tax_id,
756 acctd_amount,
757 amount,
758 CODE_COMBINATION_ID,
759 cust_trx_line_sales_rep_id,
760 insert_update_flag,
761 last_update_date,
762 last_updated_by,
763 creation_date,
764 created_by,
765 last_update_login,
766 tax_rate, --Tax_rate column added by Jagdish/Subbu 10-Jun-01
767 error_flag ,--added on 22-Mar-2002 by RPK to store the error_flag.Initially it --will be NULL
768 source, -- column added by sriram on 6th May 2002.
769 org_id, -- Added by sriram bug # 2779967
770 line_number ) -- Added by sriram Bug # 2769439
771
772 VALUES ( -- NULL, /* Previously passing v_paddr. Replaced with NULL by rallamse bug#4448789 */
773 round( TAX_TYPE_REC.tax_amt, ln_inv_curr_precision ), /* rounding based on inv currency precision - bug# 5364120*/
774 TAX_TYPE_REC.LINE_ID,
775 v_customer_trx_id,
776 v_books_id,
777 v_customer_trx_line_id,
778 v_line_type,
779 TAX_TYPE_REC.uom,
780 ln_tax_rate_id, --v_vat_tax, /* Modified by Ramananda for bug#4468353 due to ebtax uptake by AR */
781 v_converted_rate * TAX_TYPE_REC.tax_amt,
782 round(TAX_TYPE_REC.tax_amt, ln_inv_curr_precision ) , /* rounding based on inv currency precision - bug# 5364120*/
783 v_ccid,
784 v_salesrep_id,
785 'U',
786 pr_new.last_update_date,
787 pr_new.last_updated_by,
788 pr_new.creation_date,
789 pr_new.created_by,
790 pr_new.last_update_login,
791 TAX_TYPE_REC.tax_rate, --Tax_rate column added by Jagdish/Subbu 10-Jun-01
792 'P',
793 v_created_from,
794 v_org_id , -- added by sriram bug # 2779967
795 tax_type_rec.tax_line_no ); -- added by sriram - bug # 2769439
796 --END; --19-MAR-2002
797 END LOOP;
798 EXCEPTION
799 WHEN OTHERS THEN
800 v_err_mesg := SQLERRM;
801 /* RAISE_APPLICATION_ERROR(-20003,'error in processing the invoice ..' || v_trx_num || v_err_mesg);
802 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'error in processing the invoice ..' || v_trx_num || v_err_mesg ; return ; --19-MAR-2002
803 END;
804 -- the following if added by Sriram / Pavan on 06-May-2002
805 IF v_created_from = 'ARXTWMAI' THEN
806 result := fnd_request.set_mode(TRUE);
807 req_id := fnd_request.submit_request( 'JA', 'JAILINEGL', 'AR Tax and Freight Defaultation',
808 SYSDATE, FALSE,v_customer_trx_id, v_customer_trx_line_id );
809 END IF;
810 END ARI_T1 ;
811
812 /*
813 REM +======================================================================+
814 REM NAME ARU_T1
815 REM
816 REM DESCRIPTION Called from trigger JAI_JAR_TL_ARIUD_T1
817 REM
818 REM NOTES Refers to old trigger JAI_JAR_TL_ARU_T2
819 REM
820 REM +======================================================================+
821 */
822 PROCEDURE ARU_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 ) IS
823 v_books_id NUMBER;
824 v_salesrep_id NUMBER;
825 v_line_type VARCHAR2(30);
826 v_vat_tax NUMBER;
827 v_ccid NUMBER;
828 v_cust_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.customer_trx_line_id%TYPE;
829 -- v_paddr v$session.paddr%TYPE;
-- bug#6842749
830 v_counter NUMBER;
831 v_customer_trx_id NUMBER; -- := pr_old.customer_trx_id; --rpokkula for File.Sql.35
832 c_from_currency_code VARCHAR2(15);
833 c_conversion_type VARCHAR2(30);
834 c_conversion_date DATE;
835 c_conversion_rate NUMBER := 0;
836 v_converted_rate NUMBER := 1;
837 req_id NUMBER;
838 result BOOLEAN;
839 v_created_from VARCHAR2(30);
840 v_insert_update_flag VARCHAR2(1) ;
841 v_organization_id NUMBER ;
842 v_location_id NUMBER ;
843 v_batch_source_id NUMBER ;
844 v_register_code VARCHAR2(50);
845 -- added by sriram - bug # 2779967
846 v_org_id ra_customer_trx_all.org_id%type;
847 -- added by sriram - bug # 2779967
848
849 /*Commented by kunkumar for bug#6066813 start Bug 6012570 (5876390) */
850 /*cursor c_get_hdr_ctx
851 is
852 select interface_header_context
853 from ra_customer_trx_all
854 where customer_trx_id = pr_new.customer_trx_id;*/
855 /*commented the above cursor and added the following for bug#5597146*/
856 cursor c_get_hdr_ctx
857 is
858 select distinct interface_line_context
859 from ra_customer_trx_lines_all
860 where customer_trx_id = pr_new.customer_trx_id
861 and customer_trx_line_id = pr_new.customer_trx_line_id --added for bug#5597146
862 and interface_line_context is not null
863 and rownum = 1;
864
865 lv_intf_hdr_ctx ra_customer_trx_all.interface_header_context%type ;
866 /* End commented by kunkumar for bug#6066813 */
867 /*end Bug 6012570 (5876390) */
868
869 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 Begin
870 -- TD18-changed Trading AR Invoice
871 -------------------------------------------------------------------------
872 /*
873 v_ORDER_PRICE_EXCISE_INCLUSIVE JAI_CMN_INVENTORY_ORGS.ORDER_PRICE_EXCISE_INCLUSIVE%type; -- date 15/06/2007 sacsethi for bug 6131957
874
875 -- date 15/06/2007 sacsethi for bug 6131957
876
877 cursor c_ORDER_PRICE_EXCISE_INCLUSIVE(p_organization_id number,p_location_id number) is
878 select ORDER_PRICE_EXCISE_INCLUSIVE
879 from JAI_CMN_INVENTORY_ORGS
880 where organization_id = p_organization_id
881 and location_id = p_location_id;
882 */
883 -------------------------------------------------------------------------
884 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 End
885
886
887 CURSOR tax_type_cur IS
888 SELECT
889 a.tax_id taxid ,
890 a.tax_rate ,
891 a.uom uom ,
892 a.tax_amount tax_amt ,
893 b.tax_type t_type ,
894 a.customer_trx_line_id line_id ,
895 a.tax_line_no tax_line_no
896 FROM
897 JAI_AR_TRX_TAX_LINES a ,
898 JAI_CMN_TAXES_ALL b
899 WHERE
900 link_to_cust_trx_line_id = pr_old.customer_trx_line_id and
901 a.tax_id = b.tax_id
902 AND NVL(b.inclusive_tax_flag,'N') = 'N' --Added by Jia Li for Tax inclusive Computations on 2007/11/22
903 ORDER BY
904 1;
905
906 -- bug#6842749
907 /*
908 CURSOR PADDR_CUR IS
909 SELECT paddr
910 FROM v$session
911 WHERE audsid = USERENV('SESSIONID');
912 */
913
914 CURSOR BOOKS_TRX_CUR IS
915 SELECT set_of_books_id, primary_salesrep_id, invoice_currency_code, exchange_rate_type, exchange_date, exchange_rate
916 FROM JAI_AR_TRXS
917 WHERE customer_trx_id = v_customer_trx_id;
918
919
920 --Commented for bug#4468353
921 /*
922 CURSOR VAT_TAX_CUR(cp_tax_code AR_VAT_TAX_ALL.tax_code%type) IS
923 SELECT DISTINCT vat_tax_id
924 FROM AR_VAT_TAX_ALL
925 WHERE UPPER(tax_code) = cp_tax_code ;
926 */
927
928
929 /* Added by Ramananda for bug#4468353 , start
930 CURSOR ORG_CUR IS
931 SELECT ORG_ID
932 FROM RA_CUSTOMER_TRX_ALL
933 WHERE CUSTOMER_TRX_ID = pr_new.customer_trx_id;*/
934
935 --commented the above org_cur cursor and added the following org_cur cursor for bug#5597146
936 CURSOR ORG_CUR IS
937 SELECT ORG_ID
938 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
939 WHERE CUSTOMER_TRX_ID = pr_new.customer_trx_id
940 AND account_class ='REC'
941 AND latest_rec_flag ='Y';
942
943 lv_tax_regime_code zx_rates_b.tax_regime_code%type ;
944 ln_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%type ;
945 ln_tax_rate_id zx_rates_b.tax_rate_id%type ;
946 /* Added by Ramananda for bug#4468353 , end */
947
948 CURSOR TAX_CCID_CUR(p_tax_id IN NUMBER) IS
949 SELECT tax_account_id
950 FROM JAI_CMN_TAXES_ALL B
951 WHERE B.tax_id = p_tax_id ;
952
953 CURSOR GL_DATE_CUR IS
954 SELECT gl_date
955 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
956 WHERE CUSTOMER_TRX_LINE_ID = pr_old.customer_trx_line_id;
957
958 CURSOR CREATED_FROM_CUR IS
959 SELECT created_from
960 FROM JAI_AR_TRXS -- table reference was previously RA_CUSTOMER_TRX_ALL - using JA_IN_RA_CUSTOMER_TRX instead - bug# 2728636
961 WHERE customer_trx_id = v_customer_trx_id;
962
963 CURSOR Insert_Update_Cur(p_customer_trx_line_id IN NUMBER) IS
964 SELECT INSERT_UPDATE_FLAG
965 FROM JAI_AR_TRX_INS_LINES_T
966 WHERE customer_trx_id = V_CUSTOMER_TRX_ID AND
967 Customer_trx_line_id = p_customer_trx_line_id
968 ORDER BY CUSTOMER_TRX_LINE_ID;
969
970 CURSOR SO_AR_HDR_INFO IS
971 SELECT organization_id, location_id, batch_source_id
972 FROM JAI_AR_TRXS
973 WHERE Customer_Trx_ID = v_customer_trx_id;
974
975 CURSOR register_code_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER, p_batch_source_id IN NUMBER) IS
976 SELECT register_code
977 FROM JAI_OM_OE_BOND_REG_HDRS
978 WHERE organization_id = p_org_id AND location_id = p_loc_id AND
979 register_id IN (SELECT register_id
980 FROM JAI_OM_OE_BOND_REG_DTLS
981 WHERE order_type_id = p_batch_source_id AND order_flag = 'N');
982
983 /* Bug 5243532. Added by Lakshmi Gopalsami
984 * Removed cursor set_of_books_cur and c_opr_set_of_books_id
985 * and implemented caching logic.
986 */
987 CURSOR cur_chk_rgm ( cp_tax_type JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE )
988 IS
989 SELECT
990 regime_id ,
991 regime_code
992 FROM
993 jai_regime_tax_types_v jrttv
994 WHERE
995 upper(jrttv.tax_type) = upper(cp_tax_type);
996
997
998 ln_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
999 lv_attr_value JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE;
1000
1001 -- Start of bug 4089440
1002 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
1003 lv_error_flag VARCHAR2(2) ;
1004 lv_error_message VARCHAR2(4000) ;
1005 -- End of bug 4089440
1006
1007 /* Bug 5243532. Added by Lakshmi Gopalsami
1008 * Removed cursor set_of_books_cur
1009 * and implemented caching logic.
1010 */
1011 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1012
1013 ln_inv_curr_precision NUMBER;
1014
1015 /* added by CSahoo - bug# 5364120*/
1016 CURSOR c_inv_curr_precision(cp_currency_code varchar2) IS
1017 SELECT NVL(minimum_accountable_unit,NVL(precision,2)) curr_precision
1018 FROM fnd_currencies
1019 WHERE currency_code = cp_currency_code;
1020
1021 BEGIN
1022 pv_return_code := jai_constants.successful ;
1023 /*------------------------------------------------------------------------------------------
1024 FILENAME: JA_IN_APPS_AR_LINES_UPDATE_TRG.sql
1025 CHANGE HISTORY:
1026 S.No Date Author and Details
1027 1. 2001/04/09 Changed the Cases for Register Codes
1028 2. 2001/06/11 Jagdish
1029 Added tax_rate for Receipts Discounts Issue
1030 3. 2001/06/22 Anuradha Parthasarathy
1031 Code commented and added to improve performance.
1032 4. 2001/12/13 Anuradha Parthasarathy
1033 Code commented because if the updated tax amount is zero the tax lines
1034 need to be corrected in the Base Tables as well.
1035 5. 2002/05/09 Sriram
1036 Added the Source Column in the Column list in the insert statement
1037 This Column was added in the JAI_AR_TRX_INS_LINES_T table because the
1038 AR Tax and Freight Defaultation Concurrent - was split into 2 concurrents
1039 doing the same functionality - one being called from the AR side for manual invoice
1040 from this trigger and another from OM side which is scheduled to run on a periodid basis
1041 6. 2002/05/09 Sriram
1042 Added the if condition at the bottom of the trigger to conditionally call the concurrent
1043 only if is a manual invoice.
1044 7. 2003/01/02 Ssumaith Bug # 2728636 File Version 615.2
1045 Reference to table RA_CUSTOMER_TRX_ALL was present in the cursor Created_from_cur
1046 Which was causing a mutating error and causing the transaction to error out
1047 with unhandled exception.This has been changed to point to JAI_AR_TRXS
1048 table instead which takes care of the issue.
1049 8. 2003/04/07 SSUMAITH.Bug # 2779967
1050 column Org id was not inserted in the JAI_AR_TRX_INS_LINES_T table. This needs to be
1051 inserted in the table , because when processing the records using the 'India Local Concurrent'
1052 only records that belong to the orgid of the current responsiblity needs to be picked up.
1053 9. 2003/04/10 SSUMAITH - Bug # 2769439
1054 Also inserting the tax line number in the JAI_AR_TRX_INS_LINES_T table.
1055 This is necessary for creating links between OM and AR.
1056
1057
1058 10. 2005/27/01 aiyer - Bug # 4089440 File Version 115.1
1059 Issue:-
1060 In case of service invoices having service/service_education type of taxes the code combination id should be picked
1061 up from regime tax setup. This is being done as a part of the service tax enhancement
1062
1063 Solution:-
1064 The check that, for service type of taxes ccid should be picked up from regime tax setup has been impletemented.
1065 Called the procedure jai_ar_rgm_processing_pkg.get_regime_info is being called for regime setup validation.
1066 Aslo called the function jai_cmn_rgm_recording_pkg.get_account to get the ccid in such cases.
1067
1068 Dependency introduced as a part of this bug:-
1069 This file should be released on top of Bug 4146708.
1070
1071 11. 01-Mar-2005 aiyer - Bug # 4212816 File Version 115.2
1072 Issue:-
1073 In case of invoices having any of the taxes setup for VAT REGIME, the code combination should get picked up from the
1074 VAT Regime setup. This is being done as a part of the VAT enhancement
1075
1076 Solution:-
1077 The check that, for taxes belonging to the VAT regime, ccid should be picked up from VAT regime setup has been impletemented.
1078 The account info is fetched using the function jai_cmn_rgm_recording_pkg.get_account.
1079
1080
1081 Dependency introduced as a part of this bug:-
1082 This file should be released on top of Bug 4245089.
1083 Datamodel changes for VAT
1084
1085
1086 12. 02-Apr-2005 aiyer - Bug # 4279702 File Version 115.3
1087 Issue:-
1088 VAT regime code does not get reinitialized for any tax type which does not fall in the VAT / Service TAx regime
1089
1090 Solution:-
1091 Changed the triggers ja_in_apps_ar_lines_insert_trg and ja_in_apps_ar_lines_update_trg to reinitialize the regime codes
1092 every time processing happens for a tax type.
1093
1094 Dependency introduced as a part of this bug:-
1095 This file should be released on top of Bug 4245089.
1096
1097 13. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
1098 DB Entity as required for CASE COMPLAINCE. Version 116.1
1099
1100 14. 13-Jun-2005 File Version: 116.2
1101 Ramananda for bug#4428980. Removal of SQL LITERALs is done
1102
1103 15. 25-Jan-2007 CSahoo for Bug#5631784, File Version 120.3
1104 Forward Porting of BUG#4742259
1105 Accounting information popluated for TCS regime
1106
1107 15. 14-Feb-2007 CSahoo BUG#5364120, File Version - 120.4
1108 Forward Porting of 11i BUG#5346489
1109 The transaction amounts are rounded based on the currency precision of the invoice currency code
1110 when the insert is done in the JAI_AR_TRX_INS_LINES_T.
1111 Added the cursor c_inv_curr_precision.
1112
1113 Dependency due to this bug :- None
1114
1115 16. 15-Feb-2007 CSahoo Bug#5390583, File Version - 120.5
1116 Forward Porting of 11i bug#5357400
1117 When taxes are added manually to the ar invoice from the transactions localised screen and the
1118 batch source is set to bond register, the excise taxes are going to base AR. This should not go.
1119
1120 Fix :
1121 The code to take care of this was in a trading loop and would never reach there.
1122 17. 05-APR-2007 bduvarag for bug#5671400,File version 120.6
1123 Forward porting the changes done in 11i bug#4648231
1124
1125 Modified the code appropriately.
1126
1127 18 24/04/2007 cbabu for bug#5876390, File Version 120.7 (115.17 )
1128 FP: For project billing bond register functionality is not required hence added a check to see if
1129 the invoice is created by projects then bond register related logic should not be executed.
1130 19. 31/05/2007 CSahoo for bug#6081806, File Version 120.13
1131 added the sh cess tax types.
1132
1133
1134 20. 14/06/2007 sacsethi for bug 6072461 for file version - 120.15
1135
1136 This bug is used to fp 11i bug 5183031 for vat reveresal
1137
1138 Problem - Vat Reversal Enhancement not forward ported
1139 Solution - Changes has been done to make it compatible for vat reversal functioanlity also.
1140
1141 21. 15/06/2007 sacsethi for bug 6131957 for file version - 120.16
1142
1143 R12RUP03-ST1: TRADING TAKES EXCISE PRICE INCLUSIVE EVEN WHEN FLAG IS UNCHECKED
1144
1145 Variable v_order_price_excise_inclusive and cursor c_order_price_excise_inclusive is defined
1146
1147 22. 10/07/2007 CSahoo for bug#5597146, FileVersion 120.17
1148 modified the cursor ORG_CUR.
1149 23. 10/10/2007 CSahoo for bug#5597146, FileVersion 120.19
1150 modified the cursor c_get_hdr_ctx to remove the mutating trigger error.
1151 24. 11/10/2007 CSahoo for bug#5597146, File Version 120.20
1152 Added the following AND condition in the cursor c_get_hdr_ctx
1153 "and customer_trx_line_id = pr_new.customer_trx_line_id "
1154
1155 ==========================================================================================================================================================
1156
1157 Future Dependencies For the release Of this Object:-
1158 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
1159 A datamodel change )
1160
1161 ----------------------------------------------------------------------------------------------------------------------------------------------------
1162 Current Version Current Bug Dependent Files Version Author Date Remarks
1163 Of File On Bug/Patchset Dependent On
1164 ja_in_apps_ar_lines_update_trg.sql
1165
1166 115.1 4089440 IN60105D2 + Aiyer 27-Jan-2005 4146708 is the release bug for SERVICE/CESS
1167 4146708 enhancement release
1168
1169 115.2 4247989 IN60106 + 4146708 + 4245089 Aiyer 01-Mar-2005 4245089 is the Base bug for VAT.
1170 This has datamodel changes for VAT.
1171
1172 25. 22/11/2007 Added by Jia Li
1173 for Tax inclusive Computations
1174
1175 -------------------------------------------------------------------------------------------------------------------------------------------------------------*/
1176
1177 -- added by sriram - bug # 2779967
1178 v_org_id := FND_PROFILE.VALUE('ORG_ID');
1179 -- added by sriram - bug # 2779967
1180
1181 v_customer_trx_id := pr_old.customer_trx_id; --rpokkula for File.Sql.35
1182
1183 OPEN CREATED_FROM_CUR;
1184 FETCH CREATED_FROM_CUR INTO v_created_from;
1185 CLOSE CREATED_FROM_CUR;
1186 IF v_created_from IN ('ARXREC','ARXTWCMI') THEN /* Added on 19/9/99*/
1187 RETURN;
1188 END IF;
1189
1190 IF (NVL(pr_old.TAX_AMOUNT,0) = NVL(pr_new.TAX_AMOUNT,1)) AND (pr_new.Customer_Trx_Id = pr_old.Customer_Trx_Id)
1191 THEN
1192 RETURN;
1193 END IF;
1194 -- 6842749
1195 /* OPEN PADDR_CUR;
1196 FETCH PADDR_CUR INTO v_paddr;
1197 CLOSE PADDR_CUR;
1198 */
1199 OPEN BOOKS_TRX_CUR;
1200 FETCH BOOKS_TRX_CUR INTO v_books_id, v_salesrep_id, c_from_currency_code, c_conversion_type, c_conversion_date, c_conversion_rate ;
1201 CLOSE BOOKS_TRX_CUR;
1202
1203 /* start additions by CSahoo - bug# 5364120*/
1204
1205 IF c_from_currency_code IS NOT NULL THEN
1206 OPEN c_inv_Curr_precision(c_from_currency_code);
1207 FETCH c_inv_curr_precision INTO ln_inv_curr_precision;
1208 CLOSE c_inv_curr_precision;
1209 END IF;
1210
1211
1212 IF ln_inv_curr_precision IS NULL THEN
1213 ln_inv_curr_precision := 0;
1214 END IF;
1215
1216 /* end additions by CSahoo - bug#5364120 */
1217
1218 --- This Gives More then One Row, Still To solve This Issue
1219
1220 --Commented for bug#4468353
1221 /*
1222 OPEN VAT_TAX_CUR('LOCALIZATION');
1223 FETCH VAT_TAX_CUR INTO v_vat_tax;
1224 CLOSE VAT_TAX_CUR;
1225 */
1226
1227 /* Added by Ramananda for bug# due to ebtax uptake by AR, start */
1228 OPEN ORG_CUR;
1229 FETCH ORG_CUR INTO V_ORG_ID;
1230 CLOSE ORG_CUR;
1231
1232 OPEN jai_ar_trx_pkg.c_tax_regime_code_cur(V_ORG_ID);
1233 FETCH jai_ar_trx_pkg.c_tax_regime_code_cur INTO lv_tax_regime_code;
1234 CLOSE jai_ar_trx_pkg.c_tax_regime_code_cur ;
1235
1236 OPEN jai_ar_trx_pkg.c_max_tax_rate_id_cur(lv_tax_regime_code);
1237 FETCH jai_ar_trx_pkg.c_max_tax_rate_id_cur INTO ln_tax_rate_id;
1238 CLOSE jai_ar_trx_pkg.c_max_tax_rate_id_cur ;
1239 /* Added by Ramananda for bug# due to ebtax uptake by AR, end */
1240
1241 OPEN SO_AR_HDR_INFO ;
1242 FETCH SO_AR_HDR_INFO INTO v_organization_id, v_location_id, v_batch_source_id;
1243 CLOSE SO_AR_HDR_INFO ;
1244
1245 --Commented by kunkumar for bug#6066813 Start, Bug 6012570 (5876390)
1246 open c_get_hdr_ctx;
1247 fetch c_get_hdr_ctx into lv_intf_hdr_ctx;
1248 close c_get_hdr_ctx;
1249
1250 if JAI_AR_RCTLA_TRIGGER_PKG.is_this_projects_context (lv_intf_hdr_ctx) then
1251 /* For project invoices, there is no bond register functionality, so no need to do any processing for bond registers
1252 If line context is not PROJECT INVOICE then continue the normal processing flow */
1253 null;
1254
1255 else
1256 -- End commented by kunkumar for bug#6066813 End 6012570 (5876390) */
1257
1258 OPEN register_code_cur(v_organization_id, v_location_id, v_batch_source_id);
1259 FETCH register_code_cur INTO v_register_code;
1260 CLOSE register_code_cur;
1261
1262 --commented by kunkumar for bug#6066813
1263 end if;
1264 /* 6012570 (5876390) */
1265
1266 IF v_books_id IS NULL
1267 THEN
1268 /* Bug 5243532. Added by Lakshmi Gopalsami
1269 * Removed the cursor set_of_books_cur and implemented using caching logic.
1270 */
1271 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1272 (p_org_id => v_organization_id );
1273 v_books_id := l_func_curr_det.ledger_id;
1274 END IF;
1275 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_books_id ,c_from_currency_code ,
1276 c_conversion_date ,c_conversion_type, c_conversion_rate);
1277
1278
1279 -- date 15/06/2007 sacsethi for bug 6131957
1280 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 Begin
1281 -- TD18-changed Trading AR Invoice
1282 -------------------------------------------------------------------------
1283 /*
1284 open c_ORDER_PRICE_EXCISE_INCLUSIVE(v_organization_id, v_location_id);
1285 fetch c_ORDER_PRICE_EXCISE_INCLUSIVE into v_ORDER_PRICE_EXCISE_INCLUSIVE;
1286 close c_ORDER_PRICE_EXCISE_INCLUSIVE;
1287 */
1288 -------------------------------------------------------------------------
1289 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 End
1290
1291 FOR TAX_TYPE_REC IN TAX_TYPE_CUR
1292 LOOP
1293 SELECT COUNT(*) INTO v_counter
1294 FROM JAI_AR_TRX_INS_LINES_T b
1295 WHERE b.LINK_TO_CUST_TRX_LINE_ID = pr_new.Customer_Trx_Line_Id
1296 AND b.customer_trx_line_id = Tax_Type_Rec.LINE_ID;
1297
1298 --IF nvl(v_ORDER_PRICE_EXCISE_INCLUSIVE,'N') = 'Y' then -- date 15/06/2007 sacsethi for bug 6131957 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22, TD18
1299
1300 IF NVL(v_register_code,'N') IN ('23D_EXPORT_WITHOUT_EXCISE','23D_EXPORT_EXCISE',
1301 '23D_DOMESTIC_EXCISE','23D_DOM_WITHOUT_EXCISE','BOND_REG') THEN
1302 --jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess added by csahoo for bug#6081806
1303 IF Tax_Type_Rec.T_Type IN ('Excise','Addl. Excise','Other Excise','EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS',jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess) THEN
1304 TAX_TYPE_REC.tax_amt := 0;
1305 END IF;
1306 END IF;
1307
1308 --end if ; -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22, TD18
1309
1310 /*
1311 Date 14-jun-2007 by sacsethi for bug 6072461
1312 */
1313
1314 IF ( upper(Tax_Type_Rec.T_Type) = 'VAT REVERSAL' )
1315 THEN
1316 TAX_TYPE_REC.tax_amt := 0;
1317 END IF;
1318
1319 /*
1320 || added by CSahoo - bug# 5390583 In case of bond reg , the excise taxes should not go to base AR tables.
1321 */
1322 IF NVL(v_register_code,'N') = 'BOND_REG' THEN
1323 -- jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess added by csahoo for bug#6081806
1324 IF upper(Tax_Type_Rec.T_Type) IN ('EXCISE','ADDL. EXCISE','OTHER EXCISE','EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS',jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess) THEN
1325 TAX_TYPE_REC.tax_amt := 0;
1326 END IF;
1327 END IF;
1328
1329 /*
1330 || Ends additions by CSahoo - bug# 5390583
1331 */
1332
1333 OPEN Insert_Update_Cur(TAX_TYPE_REC.line_id);
1334 FETCH Insert_Update_Cur INTO v_insert_update_flag;
1335 CLOSE Insert_Update_Cur;
1336 IF NVL(v_insert_update_flag,'I') <> 'X' THEN
1337 IF TAX_TYPE_REC.t_type = 'Freight' THEN
1338 v_line_type := 'FREIGHT';
1339 ELSE
1340 v_line_type := 'TAX';
1341 END IF;
1342
1343 IF tax_type_rec.t_type = 'TDS' THEN
1344 tax_type_rec.tax_amt := 0;
1345 END IF;
1346
1347 /*
1348 || Code modified by aiyer for the bug 4279702.
1349 || Initializing the regime variables
1350 */
1351 ln_regime_id := null;
1352 ln_regime_code := null;
1353
1354 /*
1355 || Get the regime attached to a transaction tax type
1356 */
1357 OPEN cur_chk_rgm ( cp_tax_type => tax_type_rec.t_type);
1358 FETCH cur_chk_rgm INTO ln_regime_id,ln_regime_code ;
1359 CLOSE cur_chk_rgm ;
1360
1361 -- Start of bug 4089440
1362 /*
1363 || The following code has been added by aiyer for the bug 4089440
1364 || IF tax type is SERVICE or SERVICE-CESS then get the account info from regime setup
1365 || IF no setup is found then raise an error and stop the transaction.
1366 */
1367 IF upper(tax_type_rec.t_type) = upper(jai_constants.tax_type_service) OR
1368 upper(tax_type_rec.t_type) = upper(jai_constants.tax_type_service_edu_cess)
1369 OR upper(tax_type_rec.t_type)= upper(jai_constants.tax_type_sh_service_edu_cess) -- added by csahoo for bug#6081806
1370 THEN -- Start of A1
1371
1372 /*################################################################################################################
1373 || Get the regime id and also validate the Regime/Regime Registratiom Setup Information
1374 ################################################################################################################*/
1375
1376 jai_ar_rgm_processing_pkg.get_regime_info ( p_regime_code => jai_constants.service_regime ,
1377 p_tax_type_code => tax_type_rec.t_type ,
1378 p_regime_id => ln_regime_id ,
1379 p_error_flag => lv_error_flag ,
1380 p_error_message => lv_error_message
1381 );
1382
1383 IF lv_error_flag <> jai_constants.successful THEN
1384 /*
1385 || Encountered an error from the call to jai_ar_rgm_processing_pkg.get_regime_info
1386 || Stop processing and thorw an error
1387 */
1388 /* raise_application_error (-20130,lv_error_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_error_message ; return ;
1389 /*
1390 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
1391 EXCEPTION_CODE => NULL ,
1392 EXCEPTION_TEXT => lv_error_message
1393 );
1394 */
1395
1396 END IF;
1397
1398 /*################################################################################################################
1399 || Get Tax Account Info from the Regime Organization/Regime Registration setup
1400 ################################################################################################################*/
1401
1402 /*
1403 || Get the code combination id from the Organization/Regime Registration setup
1404 || by calling the function jai_cmn_rgm_recording_pkg.get_account
1405 */
1406
1407 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
1408 p_regime_id => ln_regime_id ,
1409 p_organization_type => jai_constants.service_tax_orgn_type ,
1410 p_organization_id => v_organization_id ,
1411 p_location_id => v_location_id ,
1412 p_tax_type => tax_type_rec.t_type ,
1413 p_account_name => jai_constants.liability_interim
1414 );
1415
1416 IF v_ccid IS NULL THEN
1417 /*
1418 || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
1419 || This is an error condition and the current processing has to be stopped
1420 */
1421 /* raise_application_error (-20130,'Invalid Code combination, please check the Service Tax - Tax Accounting Setup'); */
1422 pv_return_code := jai_constants.expected_error ; pv_return_message := 'Invalid Code combination,please check the Service Tax - Tax Accounting Setup' ; return ;
1423 /*
1424 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
1425 EXCEPTION_CODE => NULL ,
1426 EXCEPTION_TEXT => 'Invalid Code combination, please check the Service Tax - Tax Type Accounting Setup'
1427 );
1428 */
1429
1430 END IF;
1431
1432
1433 /*
1434 || Start of bug 4212816
1435 || Code modified by aiyer for the VAT Enhancement
1436 || Get the Tax accounting information from the vat regime setup when the taxes are as mentioned below taxes
1437 */
1438 ELSIF UPPER(nvl(ln_regime_code,'####')) = jai_constants.vat_regime THEN
1439
1440 /*********************************************************************************************************
1441 || Validate whether the item attached is vatable or not
1442 *********************************************************************************************************/
1443 if pr_new.inventory_item_id is not null then /*Bug 5671400 bduvarag*/
1444 jai_inv_items_pkg.jai_get_attrib (
1445 p_regime_code => ln_regime_code ,
1446 p_organization_id => v_organization_id ,
1447 p_inventory_item_id => pr_new.inventory_item_id ,
1448 p_attribute_code => jai_constants.rgm_attr_item_applicable ,
1449 p_attribute_value => lv_attr_value ,
1450 p_process_flag => lv_error_flag ,
1451 p_process_msg => lv_error_message
1452 ) ;
1453
1454 IF lv_error_flag <> jai_constants.successful THEN
1455 /*
1456 || Encountered an error from the call to jai_ar_rgm_processing_pkg.get_regime_info
1457 || Stop processing and thorw an error
1458 */
1459 /* raise_application_error (-20130,lv_error_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_error_message ; return ;
1460 /*
1461 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
1462 EXCEPTION_CODE => NULL ,
1463 EXCEPTION_TEXT => lv_error_message
1464 );
1465 */
1466
1467 END IF;
1468 END IF; /*Bug 5671400 bduvarag*/
1469 /*********************************************************************************************************
1470 || Raise an error if item is not vatable
1471 *********************************************************************************************************/
1472 IF pr_new.inventory_item_id is not null
1473 and nvl(lv_attr_value,'N') = 'N' THEN /*Bug 5671400 bduvarag */
1474
1475 /*
1476 || Item is not vatable . Stop processing and throw an error
1477 */
1478 /* raise_application_error (-20130,'ITEM not vatable'); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'ITEM not vatable' ; return ;
1479 /* DO not delete this code, enable this code while doing the messageing project
1480 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
1481 EXCEPTION_CODE => NULL ,
1482 EXCEPTION_TEXT => 'Cannot attach VAT type of taxes to non vatable items.'
1483 );
1484 */
1485
1486
1487 END IF;
1488
1489 /*################################################################################################################
1490 || Get Tax Account Info from the Regime Organization/Regime Registration setup
1491 ################################################################################################################*/
1492
1493 /*
1494 || Get the code combination id from the Organization/Regime Registration setup
1495 || by calling the function jai_cmn_rgm_recording_pkg.get_account
1496 */
1497
1498 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
1499 p_regime_id => ln_regime_id ,
1500 p_organization_type => jai_constants.orgn_type_io ,
1501 p_organization_id => v_organization_id ,
1502 p_location_id => v_location_id ,
1503 p_tax_type => tax_type_rec.t_type ,
1504 p_account_name => jai_constants.liability_interim
1505 );
1506 IF v_ccid IS NULL THEN
1507 /*
1508 || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
1509 || This is an error condition and the current processing has to be stopped
1510 */
1511 /* raise_application_error (-20130,'Invalid Code combination ,please check the VAT Tax - Tax Accounting Setup'); */
1512 pv_return_code := jai_constants.expected_error ; pv_return_message := 'Invalid Code combination ,please check the VAT Tax - Tax Accounting Setup' ; return ;
1513 /*
1514 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
1515 EXCEPTION_CODE => NULL ,
1516 EXCEPTION_TEXT => 'Invalid Code combination ,please check the VAT Tax - Tax Type Accounting Setup'
1517 );
1518 */
1519 END IF;
1520
1521 /*
1522 || End of bug 4212816
1523 */
1524
1525
1526 /* Added by CSahoo for the TCS enhancements Bug# 5631784 */
1527 ELSIF UPPER(nvl(ln_regime_code,'####')) = jai_constants.tcs_regime THEN -- Start of A1
1528
1529 /*********************************************************************************************************
1530 || Get the code combination id from the Organization/Regime Registration setup
1531 || by calling the function jai_rgm_trx_recording_pkg.get_account
1532 *********************************************************************************************************/
1533
1534
1535 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
1536 p_regime_id => ln_regime_id ,
1537 p_organization_type => jai_constants.orgn_type_io ,
1538 p_organization_id => v_organization_id ,
1539 p_location_id => v_location_id ,
1540 p_tax_type => tax_type_rec.t_type ,
1541 p_account_name => jai_constants.liability_interim
1542 );
1543 IF v_ccid IS NULL THEN
1544 /**********************************************************************************************************
1545 || Code Combination id has been returned as null from the function jai_rgm_trx_recording_pkg.get_account
1546 || This is an error condition and the current processing has to be stopped
1547 **********************************************************************************************************/
1548 raise_application_error (-20130,'Invalid Code combination ,please check the TCS Tax - Tax Accounting Setup');
1549
1550 END IF;
1551
1552 /*End of bug 5631784 */
1553
1554 ELSE -- ELSE of A1
1555 /*
1556 || As tax type is not SERVICE hence
1557 || get code combination from tax definition setup
1558 */
1559 OPEN TAX_CCID_CUR(TAX_TYPE_REC.taxid);
1560 FETCH TAX_CCID_CUR INTO v_ccid;
1561 CLOSE TAX_CCID_CUR;
1562 END IF;
1563
1564 IF NVL(v_counter,0) = 0 THEN
1565 INSERT INTO JAI_AR_TRX_INS_LINES_T (
1566 -- paddr,
-- 6842749
1567 extended_amount,
1568 customer_trx_line_id,
1569 customer_trx_id,
1570 set_of_books_id,
1571 link_to_cust_trx_line_id,
1572 line_type,
1573 uom_code,
1574 vat_tax_id,
1575 acctd_amount,
1576 amount,
1577 CODE_COMBINATION_ID,
1578 cust_trx_line_sales_rep_id,
1579 insert_update_flag,
1580 last_update_date,
1581 last_updated_by,
1582 creation_date,
1583 created_by,
1584 last_update_login,
1585 tax_rate, -- Tax_rate column added by Jagdish/Subbu 10-Jun-01
1586 Source, -- Source Column added by Sriram / Pavan
1587 org_id , -- org_id column added by sriram - bug # 2779967
1588 line_number ) -- added by sriram - bug # 2769439
1589 VALUES(
1590 -- v_paddr,
-- 6842749
1591 round(TAX_TYPE_REC.tax_amt,ln_inv_curr_precision), /* rounding based on inv currency precision - bug# 5364120*/
1592 TAX_TYPE_REC.line_id,
1593 v_customer_trx_id,
1594 v_books_id,
1595 pr_new.customer_trx_line_id,
1596 v_line_type,
1597 TAX_TYPE_REC.uom,
1598 ln_tax_rate_id, --v_vat_tax, /* Modified by Ramananda for bug#4468353 due to ebtax uptake by AR */
1599 v_converted_rate * TAX_TYPE_REC.tax_amt,
1600 round(TAX_TYPE_REC.tax_amt,ln_inv_curr_precision), /* rounding based on inv currency precision - bug# 5364120*/
1601 v_ccid,
1602 v_salesrep_id,
1603 'U',
1604 pr_new.last_update_date,
1605 pr_new.last_updated_by,
1606 pr_new.creation_date,
1607 pr_new.created_by,
1608 pr_new.last_update_login,
1609 TAX_TYPE_REC.tax_rate, --- Tax_rate column added by Jagdish/Subbu 10-Jun-01
1610 v_Created_from, -- v_created_from column added by Sriram - 09-MAY-2002
1611 v_org_id , -- added by sriram bug # 2779967
1612 tax_type_rec.tax_line_no) ; -- added by sriram - bug # 2769439
1613 ELSE
1614 UPDATE JAI_AR_TRX_INS_LINES_T
1615 SET extended_amount = TAX_TYPE_REC.tax_amt,
1616 set_of_books_id = v_books_id,
1617 line_type = v_line_type,
1618 uom_code = TAX_TYPE_REC.uom,
1619 acctd_amount = v_converted_rate * TAX_TYPE_REC.tax_amt,
1620 amount = TAX_TYPE_REC.tax_amt,
1621 insert_update_flag = 'U',
1622 tax_rate=TAX_TYPE_REC.tax_rate -- Tax_rate column added by Jagdish/Subbu 10-Jun-01
1623 WHERE customer_trx_id = v_customer_trx_id
1624 AND customer_trx_line_id = TAX_TYPE_REC.line_id;
1625 END IF;
1626 END IF;
1627 END LOOP;
1628 -- the following if condition added by sriram - 09-MAY-2002
1629 -- this is added because the AR Tax and Freight Defaultation should be called only
1630 -- if it is a manual invoice.
1631 IF v_created_from = 'ARXTWMAI' THEN
1632 result := fnd_request.set_mode(TRUE);
1633 req_id := fnd_request.submit_request('JA', 'JAILINEGL', 'AR Tax and Freight Defaultation', SYSDATE, FALSE,
1634 v_customer_trx_id, pr_old.customer_trx_line_id);
1635 END IF;
1636 /* Added an exception block by Ramananda for bug#4570303 */
1637 EXCEPTION
1638 WHEN OTHERS THEN
1639 Pv_return_code := jai_constants.unexpected_error;
1640 Pv_return_message := 'Encountered an error in JAI_JAR_TL_TRIGGER_PKG.ARU_T1 ' || substr(sqlerrm,1,1900);
1641
1642 END ARU_T1 ;
1643
1644 END JAI_JAR_TL_TRIGGER_PKG ;
[Home] [Help]