1 PACKAGE BODY JAI_JAR_TL_TRIGGER_PKG AS
2 /* $Header: jai_jar_tl_t.plb 120.44.12020000.5 2013/02/14 05:26:52 mmurtuza ship $ */
3 --Added by Wenqiong for Bug 14253668 Begin
4 ----------------------------------------------------------------------------
5 FUNCTION get_ar_trx_type(pn_customer_trx_line_id IN NUMBER)
6 RETURN VARCHAR2 IS
7 PRAGMA AUTONOMOUS_TRANSACTION;
8 CURSOR check_trx_type IS
9 SELECT trx_types.type
10 FROM
11 ra_customer_trx_lines_all trxl
12 , ra_customer_trx_all trx
13 , ra_cust_trx_types_all trx_types
14 WHERE trxl.customer_trx_id = trx.customer_trx_id
15 AND trx_types.cust_trx_type_id = trx.cust_trx_type_id
16 AND trxl.customer_trx_line_id = pn_customer_trx_line_id;
17
18 lv_trx_type ra_cust_trx_types_all.type%TYPE;
19 BEGIN
20 OPEN check_trx_type;
21 FETCH check_trx_type
22 INTO lv_trx_type;
23 CLOSE check_trx_type;
24 RETURN lv_trx_type;
25 END get_ar_trx_type;
26 ----------------------------------------------------------------------------
27 --Added by Wenqiong for Bug 14253668 End
28 /*
29 REM +======================================================================+
30 REM NAME ARI_T1
31 REM
32 REM DESCRIPTION Called from trigger JAI_JAR_TL_ARIUD_T1
33 REM
34 REM NOTES Refers to old trigger JAI_JAR_TL_ARI_T1
35 REM
36 REM +======================================================================+
37 */
38 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
39 v_line_no NUMBER := 0;
40 v_books_id NUMBER := 1;
41 v_salesrep_id NUMBER;
42 v_line_type VARCHAR2(30);
43 v_vat_tax NUMBER;
44 v_ccid NUMBER;
45 v_cust_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.customer_trx_line_id%TYPE;
46 /* commented by rallamse bug#4479131 PADDR Elimination
47 v_paddr v$session.paddr%TYPE;
48 */
49 v_customer_trx_line_id NUMBER; -- := pr_new.customer_trx_line_id; --Ramananda for File.Sql.35
50 v_customer_trx_id NUMBER; -- := pr_new.customer_trx_id; --Ramananda for File.Sql.35
51 v_created_from VARCHAR2(30);
52 c_from_currency_code VARCHAR2(15);
53 c_conversion_type VARCHAR2(30);
54 c_conversion_date DATE;
55 c_conversion_rate NUMBER := 0;
56 v_converted_rate NUMBER := 1;
57 req_id NUMBER;
58 result BOOLEAN;
59 v_organization_id NUMBER ;
60 v_location_id NUMBER ;
61 v_batch_source_id NUMBER ;
62 v_register_code VARCHAR2(50);
63 v_order_number ra_customer_trx_all.interface_header_attribute1%type; /*for bug 14089006. Earlier size was varchar2(30)*/
64 v_order_type ra_customer_trx_all.interface_header_attribute2%type;
65 v_org_id NUMBER(15); -- added by sriram because the orgid value is not going into temp_lines_insert table
66 v_tax_exists NUMBER :=0; --added by mmurtuza for bug 13042890
67
68 --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
69
70 ln_inv_curr_precision NUMBER; /* added by CSahoo - bug# 5364120*/
71 --commented by kunkumar for bug#6066813
72 lv_intf_hdr_ctx ra_customer_trx_all.interface_header_context%type; /* bug# 6012570 (5876390) */
73
74 /*Bug 11821537 - Fetch the effective date on which Service Tax needs to accounted in accrual basis instead of cash - Start*/
75 ld_st_accrual_date DATE;
76 ld_trx_date DATE;
77 --Commented by Chong.Lei for POT code port begin
78 /*
79 CURSOR c_get_st_accrual_date (p_regime_id NUMBER)
80 IS
81 SELECT to_date(attribute_value, 'DD/MM/YYYY')
82 FROM jai_rgm_registrations
83 WHERE regime_id = p_regime_id
84 AND attribute_code = 'EFF_DATE_ST_PT'
85 AND attribute_type_code = 'OTHERS'
86 AND registration_type = 'OTHERS';
87
88 CURSOR c_get_trx_date
89 IS
90 SELECT trx_date
91 FROM ra_customer_trx_all
92 WHERE customer_trx_id = v_customer_trx_id;
93 */
94 --Commented by Chong.Lei for POT code port end
95 --Added by Chong.Lei for POT code port begin
96 -------------------------------------------------------------------------------------------------------------------
97 /*Bug 12805386 - Fetch Effective Date of Point of Taxation only if Organization Type is either 'INDIVIDUALS' or 'PARTNERSHIP FIRM'
98 or 'PROPRIETARY FIRM' and if the Service Type is one mentioned in Rule 7 of Point of Taxation Rules 2011 i.e
99 105-p, 105-q, 105-s, 105-t, 105-u, 105-za, 105-zzzzm*/
100
101 CURSOR c_get_st_accrual_date(p_regime_id NUMBER, p_organization_id NUMBER, p_location_id NUMBER) IS
102 select to_date(attribute_value, 'DD/MM/YYYY')
103 from JAI_RGM_ORG_REGNS_V
104 where regime_id = p_regime_id
105 and organization_id = p_organization_id
106 and location_id = p_location_id
107 AND attribute_code = 'EFF_DATE_ST_PT'
108 AND attribute_type_code = 'OTHERS'
109 AND registration_type = 'OTHERS'
110 AND (NOT EXISTS
111 (select '1'
112 from JAI_RGM_ORG_REGNS_V
113 where regime_id = p_regime_id
114 and attribute_code IN 'INV_ORG_CLASSIFICATION'
115 and attribute_value <> 'ORGANIZATION'
116 and organization_id = p_organization_id
117 and location_id = p_location_id)
118 OR
119 NOT EXISTS
120 (select '1'
121 from JAI_RGM_ORG_REGNS_V
122 where regime_id = p_regime_id
123 and attribute_code IN 'SERVICE TYPE'
124 and attribute_value <> 'OTHER'
125 and organization_id = p_organization_id
126 and location_id = p_location_id)
127 );
128
129 CURSOR c_get_trx_date IS
130 SELECT gl_date --Xiao for POT changes, reg bug#12533434
131 FROM ra_cust_trx_line_gl_dist_all
132 WHERE customer_trx_id = v_customer_trx_id
133 AND customer_trx_line_id = v_customer_trx_line_id
134 AND account_class = 'REV';--Xiao for POT changes, reg bug#12533434
135 -------------------------------------------------------------------------------------------------------------------
136 --Added by Chong.Lei for POT code port end
137
138 /*Bug 11821537 - End*/
139
140 /* added by CSahoo - bug# 5364120*/
141 CURSOR c_inv_curr_precision(cp_currency_code varchar2)
142 IS
143 SELECT NVL(minimum_accountable_unit,NVL(precision,2)) curr_precision
144 FROM fnd_currencies
145 WHERE currency_code = cp_currency_code;
146
147
148
149
150 CURSOR tax_type_cur
151 IS
152 SELECT
153 a.tax_id taxid ,
154 a.tax_rate ,
155 a.uom uom ,
156 a.tax_amount tax_amt ,
157 b.tax_type t_type ,
158 a.customer_trx_line_id line_id ,
159 a.tax_line_no tax_line_no -- added by sriram - 10/4/2003 - bug # 2769439
160 FROM
161 JAI_AR_TRX_TAX_LINES a ,
162 JAI_CMN_TAXES_ALL b
163 WHERE
164 link_to_cust_trx_line_id = v_customer_trx_line_id AND
165 a.tax_id = b.tax_id
166 and nvl(b.reverse_charge_flag,'N') = 'N' --Added by Qiong for reverse charge bug#16001407
167 /*Bug 11936630 - Inclusive taxes are not interfaced in GL if Order contains only Inclusive Taxes*/
168 --AND NVL(b.inclusive_tax_flag,'N') = 'N' --Added by Jia Li for Tax inclusive Computations on 2007/11/22, TD11
169 ORDER BY
170 1;
171
172 /* Bug 4535701. Added by Lakshmi gopalsami
173 * Commented the following cursor as part of PADDR elimiation
174 CURSOR PADDR_CUR IS
175 SELECT A.paddr
176 FROM JAI_CMN_LOCATORS_T A , v$session s
177 WHERE A.PADDR = s.paddr
178 AND s.audsid = USERENV('SESSIONID');
179 */
180 CURSOR HEADER_INFO_CUR IS
181 SELECT set_of_books_id, primary_salesrep_id, org_id , invoice_currency_code, exchange_rate_type,
182 exchange_date, exchange_rate
183 --commented by kunkumar for Bug#6066813
184 ,interface_header_context /* 6012570 (5876390) */
185 FROM RA_CUSTOMER_TRX_ALL
186 WHERE customer_trx_id = v_customer_trx_id;
187
188 --Commented for bug#4468353
189 /*
190 CURSOR VAT_TAX_CUR(cp_tax_code AR_VAT_TAX_ALL.tax_code%type) IS
191 SELECT MAX(vat_tax_id)
192 FROM AR_VAT_TAX_ALL
193 WHERE tax_code = cp_tax_code ;
194 */
195
196 /* Added by Ramananda for bug#4468353 , start */
197 CURSOR ORG_CUR IS
198 SELECT ORG_ID
199 FROM RA_CUSTOMER_TRX_ALL
200 WHERE CUSTOMER_TRX_ID = pr_new.customer_trx_id;
201
202 lv_tax_regime_code zx_rates_b.tax_regime_code%type ;
203 ln_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%type ;
204 ln_tax_rate_id zx_rates_b.tax_rate_id%type ;
205 /* Added by Ramananda for bug#4468353 , end */
206
207
208 CURSOR TAX_CCID_CUR(p_tax_id IN NUMBER) IS
209 SELECT tax_account_id
210 FROM JAI_CMN_TAXES_ALL B
211 WHERE B.tax_id = p_tax_id ;
212
213 CURSOR CREATED_FROM_CUR IS
214 SELECT created_from, interface_header_attribute1,interface_header_attribute2
215 FROM ra_customer_trx_all
216 WHERE customer_trx_id = v_customer_trx_id;
217
218 CURSOR SO_AR_HDR_INFO IS
219 SELECT organization_id, location_id, batch_source_id /*uncommented for bug#8775345*/
220 -- SELECT batch_source_id
221 FROM JAI_AR_TRXS
222 WHERE Customer_Trx_ID = v_customer_trx_id;
223
224
225 --cursor added for bug 8775345
226 CURSOR get_batch_src_id IS
227 SELECT batch_source_id
228 FROM JAI_AR_TRXS
229 WHERE Customer_Trx_ID = v_customer_trx_id;
230
231
232 --start additions for bug#7661892
233 Cursor c_get_orgn
234 is
235 select warehouse_id,interface_line_attribute6
236 from ra_interfacE_lines_all
237 where interfacE_line_context= 'ORDER ENTRY'
238 and line_type='LINE'
239 and interface_line_id=v_customer_trx_line_id;
240
241 --added for bug#9151886
242 CURSOR c_get_orgn_project
243 IS
244 SELECT a.organization_id, a.location_id
245 FROM jai_pa_draft_invoices a, ra_interface_lines_all b, pa_projects_all c
246 WHERE b.interface_line_context = 'PROJECTS INVOICES'
247 AND b.line_type = 'LINE'
248 AND b.interface_line_attribute1 = c.segment1
249 AND c.project_id = a.project_id
250 AND a.draft_invoice_num = b.interface_line_attribute2
251 AND interface_line_id=v_customer_trx_line_id;
252
253 Cursor c_get_loc(cp_order_line_id varchar2)
254 is
255 select location_id
256 from jai_om_wsh_lines_all
257 where order_line_id=cp_order_line_id;
258
259 ln_order_line_id varchar2(30);
260 --end additions for bug#7661892
261
262
263 CURSOR register_code_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER,
264 p_batch_source_id IN NUMBER) IS
265 SELECT register_code
266 FROM JAI_OM_OE_BOND_REG_HDRS
267 WHERE organization_id = p_org_id AND location_id = p_loc_id AND
268 register_id IN (SELECT register_id
269 FROM JAI_OM_OE_BOND_REG_DTLS
270 WHERE order_type_id = p_batch_source_id AND order_flag = 'N');
271
272 /* Bug 4938350. Added by Lakshmi Gopalsami
273 Removed the sub-query and added oe_transaction_types_tl
274 Removed parameter p_order_number
275 */
276 CURSOR register_code_cur1(p_organization_id NUMBER,
277 p_location_id NUMBER,
278 p_order_type varchar2) IS
279 SELECT A.register_code
280 FROM JAI_OM_OE_BOND_REG_HDRS A,
281 JAI_OM_OE_BOND_REG_DTLS b,
282 oe_transaction_types_tl ott
283 WHERE A.organization_id = p_organization_id
284 AND A.location_id = p_location_id
285 AND A.register_id = b.register_id
286 AND b.order_flag = 'Y'
287 AND b.order_type_id = ott.transaction_type_id
288 AND ott.NAME = p_order_type;
289
290 /* Bug 5243532. Added by Lakshmi Gopalsami
291 * Removed cursor set_of_books_cur and c_opr_set_of_books_id
292 * and implemented caching logic.
293 */
294 CURSOR trx_num(v_cust_trx_id NUMBER) IS SELECT
295 trx_number FROM ra_customer_trx_all WHERE
296 customer_trx_id = v_cust_trx_id;
297
298 -- following cursor added by sriram -3266982
299
300 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 Begin
301 -- TD18-changed Trading AR Invoice
302 -------------------------------------------------------------------------
303 /*
304 cursor c_ORDER_PRICE_EXCISE_INCLUSIVE(p_organization_id number,p_location_id number) is
305 select ORDER_PRICE_EXCISE_INCLUSIVE
306 from JAI_CMN_INVENTORY_ORGS
307 where organization_id = p_organization_id
308 and location_id = p_location_id;
309 */
310 -------------------------------------------------------------------------
311 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 End
312
313 CURSOR cur_chk_rgm ( cp_tax_type JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE )
314 IS
315 SELECT
316 regime_id ,
317 regime_code
318 FROM
319 jai_regime_tax_types_v jrttv
320 WHERE
321 upper(jrttv.tax_type) = upper(cp_tax_type);
322
323
324 ln_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
325 lv_attr_value JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE;
326
327 -- Start of bug 4089440
328 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
329 lv_error_flag VARCHAR2(2) ;
330 lv_error_message VARCHAR2(4000) ;
331 -- End of bug 4089440
332
333 v_err_mesg VARCHAR2(250) ;
334 v_trx_num ra_customer_trx_all.trx_number%TYPE ;
335
336 /* Bug 5243532. Added by Lakshmi Gopalsami
337 * Defined variable for implementing caching logic
338 */
339 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
340
341 --Added by Bo Li for Bug 9705313 Begin
342 ----------------------------------------------------------------------------
343 CURSOR check_rma_credit_cur(-- pn_order_number NUMBER /*Commented order number parameter by mmurtuza for bug 14846750*/
344 pn_order_line_id NUMBER)
345 IS
346 SELECT count(1)
347 FROM OE_ORDER_HEADERS_ALL oh,
348 OE_ORDER_LINES_ALL ol,
349 OE_TRANSACTION_TYPES_TL ot,
350 oe_workflow_assignments owf
351 WHERE oh.header_id = ol.header_id
352 AND oh.order_type_id = ot.transaction_type_id
353 AND oh.order_type_id = owf.order_type_id
354 AND ol.line_type_id = owf.line_type_id
355 -- AND oh.order_number = pn_order_number /*Commented order number condition by mmurtuza for bug 14846750*/
356 AND ot.language = userenv('LANG')
357 AND ol.line_id = pn_order_line_id
358 AND owf.process_name IN ('R_RMA_CREDIT_APP_HDR_INV',
359 'R_RMA_CREDIT_WO_SHIP_APPROVE',
360 'R_RMA_CREDIT_WO_SHIP_HDR_INV',
361 'R_RMA_FOR_CREDIT_WO_SHIPMENT',
362 'R_RMA_FOR_OTA_CREDIT');
363
364 CURSOR check_shippable_item_cur(pn_order_line_id NUMBER)
365 IS
366 SELECT COUNT(1)
367 FROM MTL_SYSTEM_ITEMS msi,
368 JAI_OM_OE_RMA_LINES l
369 WHERE msi.inventory_item_id = pr_new.inventory_item_id
370 AND msi.inventory_item_id = l.inventory_item_id
371 AND l.rma_line_id = pn_order_line_id
372 AND msi.shippable_item_flag = 'N' ;
373
374 ln_rma_flag NUMBER;
375 ln_nonship_rma_flag NUMBER;
376 ----------------------------------------------------------------------------
377 --Added by Bo Li for Bug 9705313 End
378
379 -- Added by zhiwei for GST get ccid by regime setup BUG#10233402 20101027 BEGIN
380 ------------------------------------------------------------------
381 lv_enable_gst_flag VARCHAR2(1);
382 CURSOR get_gst_organization_location(cn_customer_trx_id IN NUMBER)--v_customer_trx_line_id
383 IS
384 SELECT ORGANIZATION_ID,LOCATION_ID
385 FROM JAI_AR_TRXS
386 WHERE CUSTOMER_TRX_ID = cn_customer_trx_id;
387 lv_log VARCHAR2(1000);
388 x number;
389 CURSOR GET_COUNT(cn_customer_trx_line_id IN NUMBER)is
390 SELECT
391 COUNT(1)
392 FROM
393 JAI_AR_TRX_TAX_LINES
394 WHERE
395 link_to_cust_trx_line_id = cn_customer_trx_line_id ;
396
397
398 ln_tax_type_code varchar2(50);
399 ------------------------------------------------------------------
400 -- Added by zhiwei for GST get ccid by regime setup BUG#10233402 20101027 END
401 lv_trx_type ra_cust_trx_types_all.type%TYPE; --Added by Wenqiong for Bug 14253668
402
403 BEGIN
404 pv_return_code := jai_constants.successful ;
405 /*------------------------------------------------------------------------------------------
406 FILENAME: JA_IN_APPS_AR_LINES_INSERT_TRG.sql
407 CHANGE HISTORY:
408 S.No Date Author and Details
409 1. 10-Jun-01 Jagdish / Subbu
410 To populate Tax_rate column in RA_CUSTOMER_TRX_LINES_ALL
411 table as receipts with discounts doesn't get saved.
412 2. 2001/06/22 Anuradha Parthasarathy
413 Commented and Added code for better performance.
414
415 3. 2002/03/22 RPK
416 added the column error_flag to the table 'JAI_AR_TRX_INS_LINES_T'
417 added the cursor trx_num to get the invoice num.
418
419 4 2002/04/22 RPK
420 BUG#2334972.
421 Code modified to allow the taxes to be inserted into
422 the table JAI_AR_TRX_INS_LINES_T.When an order line is having 2 tax amounts
423 one with a positive value and the other with a negative value(eg:100 and -100)
424 then these lines were not getting transferred to AR Tables and the same is
425 required to be transferred to have the proper accounting of the GL/AR.For this,
426 the when_clause of this trigger is commented to facilitate the execution of this
427 code.This is required to facilitate the functionality of having discounts in the
428 OM/AR tax flow.
429
430 5. 2002/05/04 Added the If Condition to ensure that the concurrent request to.
431 AR Tax and Freight Defaultation gets invoked only for manual invoices
432
433 6. 2002/05/06 Added the Source Column in the insert statement of Ja-In_temp_lines_insert
434 table.
435
436 8. 2003/04/07 SSUMAITH.Bug # 2779967
437 column Org id was not inserted in the JAI_AR_TRX_INS_LINES_T table. This needs to be
438 inserted in the table , because when processing the records using the 'India Local Concurrent'
439 only records that belong to the orgid of the current responsiblity needs to be picked up.
440
441 9. 2003/04/10 SSUMAITH - Bug # 2769439
442 Also inserting the tax line number in the JAI_AR_TRX_INS_LINES_T table.
443 This is necessary for creating links between OM and AR.
444
445 10. 2003/11/20 SSUMAITH - Bug # 3266982 File Version 617.1
446 The cursor which fetches order number was still pointing to So_headers_all table.
447 This caused the query to fetch no records, causing the code not to execute based on
448 register types.
449
450 This bug introduces dependency . Because as part of this bug , a new column ORDER_PRICE_EXCISE_INCLUSIVE
451 is added into the table - ja_in_hr_organzization_units.
452
453 If the flag value of ORDER_PRICE_EXCISE_INCLUSIVE becomes 'Y' then the excise tax will go as zero
454 else , the normal excise value will go to the base apps.
455
456 Also , a join between oe_order_headers_all and oe_transaction_types_tl has been added.
457 Without this join, if the same sales order number is associated to multiple order types,
458 there is scope for the wrong order number to be chosen and comparison done on that basis.
459
460 With this join condition, the value in the ra_customer_trx_all.interface_header_attribute2 is
461 compared to the 'Name' field in the oe_transaction_types_tl.
462
463 11. 2005/27/01 aiyer - Bug # 4089440 File Version 115.1
464 Issue:-
465 In case of service invoices having service/service_education type of taxes the code combination id should be picked
466 up from regime tax setup. This is being done as a part of the service tax enhancement
467
468 Solution:-
469 The check that, for service type of taxes ccid should be picked up from regime tax setup has been impletemented.
470 Called the procedure jai_ar_rgm_processing_pkg.get_regime_info is being called for regime setup validation.
471 Aslo called the function jai_cmn_rgm_recording_pkg.get_account to get the ccid in such cases.
472
473 Dependency introduced as a part of this bug:-
474 This file should be released on top of Bug 4146708.
475
476 12. 01-Mar-2005 aiyer - Bug # 4212816 File Version 115.2
477 Issue:-
478 In case of invoices having any of the taxes setup for VAT REGIME, the code combination should get picked up from the
479 VAT Regime setup. This is being done as a part of the VAT enhancement
480
481 Solution:-
482 The check that, for taxes belonging to the VAT regime, ccid should be picked up from VAT regime setup has been impletemented.
483 The account info is fetched using the function jai_cmn_rgm_recording_pkg.get_account.
484
485 Dependency introduced as a part of this bug:-
486 This file should be released on top of Bug 4245089.
487 Datamodel changes for VAT
488
489 13. 02-Apr-2005 aiyer - Bug # 4279702 File Version 115.3
490 Issue:-
491 VAT regime code does not get reinitialized for any tax type which does not fall in the VAT / Service TAx regime
492
493 Solution:-
494 Changed the triggers ja_in_apps_lines_insert_trg and ja_in_apps_ar_lines_update_trg to reinitialize the regime codes
495 every time processing happens for a tax type.
496
497 Dependency introduced as a part of this bug:-
498 This file should be released on top of Bug 4245089.
499
500 14 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
501 DB Entity as required for CASE COMPLAINCE. Version 116.1
502
503 15. 13-Jun-2005 File Version: 116.2
504 Ramananda for bug#4428980. Removal of SQL LITERALs is done
505
506 16 06-Jul-2005 rallamse for bug# PADDR Elimination
507 1. Commented use of v_paddr and cursor PADDR_CUR
508
509 17 04-Aug-2005 Bug4535701. Added by Lakshmi Gopalsami Version 120.2
510 Commented the cursor which is used for PADDR
511
512 18 25-Jan-2007 CSahoo for Bug#5631784, File Version 120.3
513 Forward Porting of BUG#4742259
514 Accounting information popluated for TCS regime
515
516 19 14-FEB-2007 CSahoo - bug# 5364120 - file version 120.4
517 Forward Porting Of 11i BUG 5346489.
518
519 The transaction amounts are rounded based on the currency precision of the invoice currency code
520 when the insert id done in the JAI_AR_TRX_INS_LINES_T.
521 Added the cursor c_inv_curr_precision.
522
523 Dependency due to this bug :- None
524
525
526 19. 15-Feb-2007 CSahoo Bug# 5390583, File Version 120.5
527 Forward Porting of 11i Bug 5357400
528 When taxes are added manually to the ar invoice from the transactions localised screen and the
529 batch source is set to bond register, the excise taxes are going to base AR. This should not go.
530
531 Fix :
532 The code to take care of this was in a trading loop and would never reach there.
533 Modified the code appropriately.
534
535 20 24/04/2007 cbabu for bug#6012570 (5876390), File Version 120.7 (115.14 )
536 FP: For project billing bond register functionality is not required hence added a check to see if
537 the invoice is created by projects then bond register related logic should not be executed.
538
539 21 25/05/2007 sacsethi for bug 6072461 file version 120.11
540
541 Problem - MANUAL AR TRANSACTION GIVES PROBLEM WITH VAT TAXES
542
543 when we creating transactions in receivalbles , at that time at time of saving , it was
544 giving error , vat setup is not defined but vat setup was defined
545
546 Solution - At time of get account of vat , we were passing wrong value of organzation id and location id .
547
548 22. 31/05/2007 csahoo for bug#6081806, File version 120.13
549 added the sh cess types.
550
551 23. 14/06/2007 sacsethi for bug 6072461 for file version - 120.15
552
553 This bug is used to fp 11i bug 5183031 for vat reveresal
554
555 Problem - Vat Reversal Enhancement not forward ported
556 Solution - Changes has been done to make it compatible for vat reversal functioanlity also.
557 24 06-jan-2009 vkaranam for bug# 7661892
558 Issue:
559 Auto Invoice is landing into error if the Order having the delivery from different warehouses
560 Fix:
561 Changed the query logic to get the organization ,location details.
562
563 25 08-sep-2009 vkaranam for bug#8775345
564 Issue: Manual AR invoice is giving an error "Organization Cannot be Null".
565 Fix:
566 Changed the query logic to get the organization ,location details.
567
568 26. 26-Nov-2009 CSahoo for bug#9151886
569 Issue: AUTO INVOICE IMPORT ERROR
570 Fix: Added the code to fetch the organization_id and location_id for project invoices.
571
572
573 27 11-May-2010 Bug 9705313 Added by Bo Li
574 For nonshippable RMA flow
575 Generate the account for the nonshippable item Tax
576
577 28 29-Oct-2010 abezgam for bug10176878
578 Issue: When attempting to run Autoinvoice, the following error occurs.
579 'Invalid Code combination ,please check the Service Tax - Tax Accounting Setup '
580 Fix: Added an if condition to check if the location_id going into ' jai_ar_rgm_processing_pkg.get_regime_info' is null.
581 If the location_id null, it is fetched using the SO_AR_HDR_INFO cursor.
582
583 29 04-Mar-2011 Enhancement 11821537
584 Description: Notification # 18/2011 proposes change in Point of Taxation for Service Tax.
585 Service Tax Liability arises on creation of Invoice and not on Receipt of Payment from Customer
586 i.e. Accounting changes to Accrual basis from Cash Basis
587 Fix: Service Tax Libility Account will be hit directly instead of Interim Liablity as there
588 will be no set off during Receipt/CM Application.
589
590 30 12-Apr-2011 Bug 11936630
591 Description: Data was inserted into JAI_AR_TRX_INS_LINES_T via tax_type_cur only if
592 there was an Exclusive Tax Line. Hence for Order Imported AR Invoice with only
593 Inclusive Taxes accounting was not happening.
594 Fix: Data to be inserted into JAI_AR_TRX_INS_LINES_T irrespective of Tax Types.
595
596 31 18-May-2011 Xiao for POT changes, reg bug#12533434.
597 Issue: Compare transaction date with POT date.
598 fixed: Compare Accounting Date with POT date. Cursor c_get_trx_date is modified. --Added by Chong.Lei for POT code port
599
600 32. 03-Oct-2011 mmurtuza for bug 13042890
601 Issue: INDIA-AR TAX AND FREIGHT DEFAULTATION IS FIRING WHEN TRANSACTION RAISED IN BASE AR Transaction witout IOL Taxes
602 Fix: Added a variable v_tax_exists and Added condition in the trigger to check if taxes exists and the concurrent should fire only if taxes exists.
603
604 33. 28-Mar-2012 mmurtuza for bug 13649994
605 Issue: AUTOINVOICE FAILING FOR VAT INVOICES INTERFACED FORM PROJECTS
606 Fix: Added if condition to skip the cursors check_rma_credit_cur and check_shippable_item_cur in procedure ARI_T1 if the invoices are
607 imported from projects.
608
609 34. 18-May-2012 mmurtuza for bug 14089006
610 Issue: ERROR ENCOUNTERED IN TRANSACTION WORKBENCH
611 Fix: Mdofied the definition of variable v_order_number from varchar2(30) to ra_customer_trx_all.interface_header_attribute1%type
612
613 35. 10-Dec-2012 mmurtuza for bug 14846750
614 Issue: INDIA LOCALIZATION ERROR IN AUTO INVOICE IMPORT FOR SUPPLEMENTARY INVOICES.
615 Fix: Removed parameter and condition of order_number in cursor check_rma_credit_cur
616
617 ==========================================================================================================================================================
618
619 Future Dependencies For the release Of this Object:-
620 (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/
621 A datamodel change )
622
623 ----------------------------------------------------------------------------------------------------------------------------------------------------
624 Current Version Current Bug Dependent Files Version Author Date Remarks
625 Of File On Bug/Patchset Dependent On
626 ja_in_apps_ar_lines_insert_trg.sql
627
628 617.1 3266982 IN60105D1 617.1 ssumaith 03/12/2003
629
630 115.1 4089440 IN60105D2 + Aiyer 27-Jan-2005 4146708 is the release bug for SERVICE/CESS
631 4146708 enhancement release
632 115.2 4247989 IN60106 + 4146708 + 4245089 Aiyer 01-Mar-2005 4245089 is the Base bug for VAT.
633 This has datamodel changes for VAT.
634 24. 22/11/2007 Added by Jia Li
635 for Tax inclusive Computations
636 --------------------------------------------------------------------------------------------------------------------------------------------------------*/
637
638 v_customer_trx_line_id := pr_new.customer_trx_line_id; --Ramananda for File.Sql.35
639 v_customer_trx_id := pr_new.customer_trx_id; --Ramananda for File.Sql.35
640
641 --added the following block to capture the trx_number on 22-Mar-2002
642
643 OPEN trx_num(pr_new.Customer_trx_id);
644 FETCH trx_num INTO v_trx_num;
645 CLOSE trx_num;
646 --end addition 22-Mar-2002
647 OPEN CREATED_FROM_CUR;
648 FETCH CREATED_FROM_CUR INTO v_created_from , v_order_number, v_order_type;
649 CLOSE CREATED_FROM_CUR;
650 IF v_created_from IN ('ARXREC') THEN
651 RETURN;
652 END IF;
653
654 /* Commented rallamse bug#4479131 PADDR Elimination
655 OPEN PADDR_CUR;
656 FETCH PADDR_CUR INTO v_paddr;
657 CLOSE PADDR_CUR;
658 */
659
660 OPEN HEADER_INFO_CUR;
661 FETCH HEADER_INFO_CUR INTO v_books_id, v_salesrep_id,v_org_id , c_from_currency_code,
662 c_conversion_type, c_conversion_date, c_conversion_rate, lv_intf_hdr_ctx;
663 CLOSE HEADER_INFO_CUR;
664
665 /* start additions by CSahoo - bug#5364120 */
666
667 IF c_from_currency_code IS NOT NULL THEN
668 OPEN c_inv_Curr_precision(c_from_currency_code);
669 FETCH c_inv_curr_precision INTO ln_inv_curr_precision;
670 CLOSE c_inv_curr_precision;
671 END IF;
672
673 IF ln_inv_curr_precision is NULL THEN
674 ln_inv_curr_precision := 0;
675 END IF;
676
677 /* end additions by CSahoo - bug#5364120 */
678
679 --Commented for bug#4468353
680 /*
681 OPEN VAT_TAX_CUR('Localization');
682 FETCH VAT_TAX_CUR INTO v_vat_tax;
683 CLOSE VAT_TAX_CUR;
684 */
685
686 /* Added by Ramananda for bug# due to ebtax uptake by AR, start */
687 OPEN ORG_CUR;
688 FETCH ORG_CUR INTO V_ORG_ID;
689 CLOSE ORG_CUR;
690
691 OPEN jai_ar_trx_pkg.c_tax_regime_code_cur(V_ORG_ID);
692 FETCH jai_ar_trx_pkg.c_tax_regime_code_cur INTO lv_tax_regime_code;
693 CLOSE jai_ar_trx_pkg.c_tax_regime_code_cur ;
694
695 OPEN jai_ar_trx_pkg.c_max_tax_rate_id_cur(lv_tax_regime_code);
696 FETCH jai_ar_trx_pkg.c_max_tax_rate_id_cur INTO ln_tax_rate_id;
697 CLOSE jai_ar_trx_pkg.c_max_tax_rate_id_cur ;
698 /* Added by Ramananda for bug# due to ebtax uptake by AR, end */
699
700 IF v_books_id IS NULL THEN
701 /* Bug 5243532. Added by Lakshmi Gopalsami
702 * Removed the cursor set_of_books_cur and implemented using caching logic.
703 */
704 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
705 (p_org_id => v_org_id );
706 v_books_id := l_func_curr_det.ledger_id;
707 END IF;
708
709 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_books_id ,c_from_currency_code ,
710 c_conversion_date ,c_conversion_type, c_conversion_rate);
711 /*
712 OPEN SO_AR_HDR_INFO ;
713 --FETCH SO_AR_HDR_INFO INTO v_organization_id, v_location_id, v_batch_source_id; commented for bug#7661892
714 FETCH SO_AR_HDR_INFO INTO v_batch_source_id;
715 CLOSE SO_AR_HDR_INFO ;
716 *//*commented for bug#8775345*/
717
718 --start additions for bug#8775345
719 open get_batch_src_id;
720 fetch get_batch_src_id into v_batch_source_id;
721 close get_batch_src_id;
722 --end additions for bug#8775345
723
724
725 --start additions for bug#7661892
726 open c_get_orgn;
727 fetch c_get_orgn into v_organization_id,ln_order_line_id;
728 close c_get_orgn;
729
730 --start additions for bug#8775345
731 if v_organization_id is null
732 then
733 OPEN SO_AR_HDR_INFO ;
734 FETCH SO_AR_HDR_INFO INTO v_organization_id, v_location_id, v_batch_source_id;
735 CLOSE SO_AR_HDR_INFO ;
736 end if;
737 --end additions for bug#8775345
738
739
740
741 open c_get_loc(ln_order_line_id);
742 fetch c_get_loc into v_location_id;
743 close c_get_loc;
744
745 --end additions for bug#7661892
746
747 /*Commented by kunkumar for Bug#6066813 Start, Bug 6012570 (5876390) */
748 if JAI_AR_RCTLA_TRIGGER_PKG.is_this_projects_context (lv_intf_hdr_ctx) then
749 /* For project invoices, there is no bond register functionality, so no need to do any processing for bond registers
750 If line context is not PROJECT INVOICE then continue the normal processing flow */
751 --null;
752 --added for bug#9151886, start
753 open c_get_orgn_project;
754 fetch c_get_orgn_project into v_organization_id,v_location_id;
755 close c_get_orgn_project;
756 -- bug#9151886,end
757 else
758 -- End commented by kunkumar for bug#6066813
759 -- End 6012570 (5876390) */
760
761 fnd_file.put_line(FND_FILE.LOG,'organization id is '|| v_organization_id);
762 fnd_file.put_line(FND_FILE.LOG,'location id is '|| v_location_id);
763
764 /*added by abezgam for bug10176878
765 *Added the following to get the location_id incase it is Null
766 */
767 --start additions for the bug10176878
768 if v_location_id is null
769 then
770 OPEN SO_AR_HDR_INFO ;
771 FETCH SO_AR_HDR_INFO INTO v_organization_id, v_location_id, v_batch_source_id;
772 CLOSE SO_AR_HDR_INFO ;
773 end if;
774 --end additions for the bug10176878
775 fnd_file.put_line(FND_FILE.LOG,'organization id is '|| v_organization_id);
776 fnd_file.put_line(FND_FILE.LOG,'location id is '|| v_location_id);
777
778 IF v_created_from = 'RAXTRX' THEN
779 /* Bug 4938350. Added by Lakshmi Gopalsami
780 Removed the parameter v_order_number
781 */
782 OPEN register_code_cur1(v_organization_id, v_location_id,v_order_type);
783 FETCH register_code_cur1 INTO v_register_code;
784 CLOSE register_code_cur1;
785 ELSIF v_created_from = 'ARXTWMAI' THEN
786 OPEN register_code_cur(v_organization_id, v_location_id, v_batch_source_id);
787 FETCH register_code_cur INTO v_register_code;
788 CLOSE register_code_cur;
789 END IF;
790
791 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 Begin
792 -- TD18-changed Trading AR Invoice
793 -------------------------------------------------------------------------
794 /*
795 open c_ORDER_PRICE_EXCISE_INCLUSIVE(v_organization_id, v_location_id);
796 fetch c_ORDER_PRICE_EXCISE_INCLUSIVE into v_ORDER_PRICE_EXCISE_INCLUSIVE;
797 close c_ORDER_PRICE_EXCISE_INCLUSIVE;
798 */
799 -------------------------------------------------------------------------
800 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 End
801
802 --commented by kunkumar for bug#6066813
803 end if;
804 /* Bug 6012570 (5876390) */
805
806 BEGIN --19-MAR-2002
807 FOR TAX_TYPE_REC IN TAX_TYPE_CUR
808 LOOP /* following if condition added by sriram - bug# 3266982*/
809
810 --Added by zhiwei for GST get ccid by regime setup BUG#10233402 20101027 BEGIN
811 ----------------------------------------------------------------------
812 lv_enable_gst_flag := JAI_GST_GENERAL_PKG.IS_GST_ENABLED;
813 OPEN get_gst_organization_location(v_customer_trx_id);
814 FETCH get_gst_organization_location INTO v_organization_id,v_location_id;
815 CLOSE get_gst_organization_location;
816
817 IF tax_type_rec.t_type = 'Freight' THEN
818 v_line_type := 'FREIGHT';
819 ELSE
820 v_line_type := 'TAX';
821 END IF;
822
823 ln_regime_id := null;
824 ln_regime_code := null;
825
826
827 OPEN cur_chk_rgm ( cp_tax_type => tax_type_rec.t_type);
828 FETCH cur_chk_rgm INTO ln_regime_id,ln_regime_code ;
829 CLOSE cur_chk_rgm ;
830
831 IF nvl(lv_enable_gst_flag,'N') = 'Y'
832 THEN
833
834
835 v_ccid := JAI_GST_GENERAL_PKG.get_gst_accounting(
836 pv_account_source => 'AR' ,
837 pv_trx_type => 'MANUAL' ,
838 pn_tax_regim_id => ln_regime_id ,
839 pv_tax_type_code => tax_type_rec.t_type ,
840 pn_tax_id => tax_type_rec.Taxid ,
841 pn_organization_id => v_organization_id ,
842 pn_location_id => v_location_id ) ;
843
844
845 IF v_ccid IS NULL THEN
846 /**********************************************************************************************************
847 || Code Combination id has been returned as null from the function jai_rgm_trx_recording_pkg.get_account
848 || This is an error condition and the current processing has to be stopped
849 **********************************************************************************************************/
850 raise_application_error (-20130,'Invalid Code combination ,please check the GST Tax - Tax Accounting Setup' || tax_type_rec.t_type || '&&' || tax_type_rec.Taxid);
851
852 END IF;
853
854 lv_log := 'get ccid:' || v_ccid;
855
856 ELSE
857 ----------------------------------------------------------------------
858 --Added by zhiwei for GST get ccid by regime setup BUG#10233402 20101027 END
859
860
861
862 -- IF nvl(v_ORDER_PRICE_EXCISE_INCLUSIVE,'N') = 'Y' then -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 ,TD18
863 /*commented for bug#10048317 as the functionality has been obsoleted as part of inclusive tax ER*
864 IF NVL(v_register_code,'N') IN ('23D_EXPORT_WITHOUT_EXCISE','23D_EXPORT_EXCISE',
865 '23D_DOMESTIC_EXCISE','23D_DOM_WITHOUT_EXCISE','BOND_REG')
866 THEN
867 -- jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess added by csahoo for bug#6081806
868 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
869 TAX_TYPE_REC.tax_amt := 0;
870 END IF;
871 END IF;
872 */
873 -- END IF; -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 ,TD18
874
875 /*
876 Date 14-jun-2007 by sacsethi for bug 6072461
877 */
878
879 IF ( upper(Tax_Type_Rec.T_Type) = 'VAT REVERSAL' )
880 THEN
881 TAX_TYPE_REC.tax_amt := 0;
882 END IF;
883
884
885 /*
886 || added by CSahoo - bug# 5390583 In case of bond reg , the excise taxes should not go to base AR tables.
887 */
888 IF NVL(v_register_code,'N') = 'BOND_REG' THEN
889 -- jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess added by csahoo for bug#6081806
890 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
891 TAX_TYPE_REC.tax_amt := 0;
892 END IF;
893 END IF;
894
895 /*
896 || Ends additions by CSahoo - bug# 5390583
897 */
898
899 IF tax_type_rec.t_type = 'Freight' THEN
900 v_line_type := 'FREIGHT';
901 ELSE
902 v_line_type := 'TAX';
903 END IF;
904 /*
905 || Code modified by aiyer for the bug 4279702.
906 || Initializing the regime variables
907 */
908 ln_regime_id := null;
909 ln_regime_code := null;
910
911 /*
912 || Get the regime attached to a transaction tax type
913 */
914 OPEN cur_chk_rgm ( cp_tax_type => tax_type_rec.t_type);
915 FETCH cur_chk_rgm INTO ln_regime_id,ln_regime_code ;
916 CLOSE cur_chk_rgm ;
917
918 -- Start of bug 4089440
919 /*
920 || The following code has been added by aiyer for the bug 4089440
921 || IF tax type is SERVICE or SERVICE-CESS then get the account info from regime setup
922 || IF no setup is found then raise an error and stop the transaction.
923 */
924 IF upper(tax_type_rec.t_type) = upper(jai_constants.tax_type_service) OR
925 upper(tax_type_rec.t_type) = upper(jai_constants.tax_type_service_edu_cess)
926 OR upper(tax_type_rec.t_type)= upper(jai_constants.tax_type_sh_service_edu_cess) --added by csahoo for bug#6081806
927 THEN -- Start of A1
928
929 /**********************************************************************************************************
930 || Get the regime id and also validate the Regime/Regime Registratiom Setup Information
931 ***********************************************************************************************************/
932
933 jai_ar_rgm_processing_pkg.get_regime_info ( p_regime_code => jai_constants.service_regime ,
934 p_tax_type_code => tax_type_rec.t_type ,
935 p_regime_id => ln_regime_id ,
936 p_error_flag => lv_error_flag ,
937 p_error_message => lv_error_message
938 );
939
940 IF lv_error_flag <> jai_constants.successful THEN
941 /*
942 || Encountered an error from the call to jai_ar_rgm_processing_pkg.get_regime_info
943 || Stop processing and thorw an error
944 */
945 /* raise_application_error (-20130,lv_error_message);
946 */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_error_message ; return ;
947 END IF ;
948 /**********************************************************************************************************
949 || Get Tax Account Info from the Regime Organization/Regime Registration setup
950 **********************************************************************************************************/
951
952 /*
953 || Get the code combination id from the Organization/Regime Registration setup
954 || by calling the function jai_cmn_rgm_recording_pkg.get_account
955 */
956
957 /*ER 11821537 - Service Tax must hit Liability instead of Interim Liability due to change in point of taxation to accural from cash*/
958 -- OPEN c_get_st_accrual_date(ln_regime_id); --Comment by Chong.Lei for POT code port
959 -- Added by Chong.Lei for POT code port begin
960 /*Bug 12805386 - Added parameters Inventory Organization ID and Location ID*/
961 OPEN c_get_st_accrual_date(ln_regime_id, v_organization_id, v_location_id);
962 -- Added by Chong.Lei for POT code port end
963 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
964 CLOSE c_get_st_accrual_date;
965
966 OPEN c_get_trx_date;
967 FETCH c_get_trx_date INTO ld_trx_date;
968 CLOSE c_get_trx_date;
969
970 --Added by Wenqiong for Bug 14253668 begin
971 -------------------------------------------
972 lv_trx_type := get_ar_trx_type(v_customer_trx_line_id);
973 -------------------------------------------
974 --Added by Wenqiong for Bug 14253668 end
975
976 IF ld_trx_date >= ld_st_accrual_date THEN
977 IF nvl(lv_trx_type,'#') <> jai_constants.ar_invoice_type_cm THEN--Added by Wenqiong for Bug 14253668
978 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
979 p_regime_id => ln_regime_id ,
980 p_organization_type => jai_constants.service_tax_orgn_type ,
981 p_organization_id => v_organization_id ,
982 p_location_id => v_location_id ,
983 p_tax_type => tax_type_rec.t_type ,
984 p_account_name => jai_constants.liability
985 );
986 --Added by Wenqiong for Bug 14253668 begin
987 ELSIF nvl(lv_trx_type,'#') = jai_constants.ar_invoice_type_cm THEN
988 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
989 p_regime_id => ln_regime_id ,
990 p_organization_type => jai_constants.service_tax_orgn_type ,
991 p_organization_id => v_organization_id ,
992 p_location_id => v_location_id ,
993 p_tax_type => tax_type_rec.t_type ,
994 p_account_name => jai_constants.recovery
995 );
996 END IF;
997 --Added by Wenqiong for Bug 14253668 end
998 ELSE
999 IF nvl(lv_trx_type,'#') <> jai_constants.ar_invoice_type_cm THEN --Added by Wenqiong for Bug 14253668
1000 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
1001 p_regime_id => ln_regime_id ,
1002 p_organization_type => jai_constants.service_tax_orgn_type ,
1003 p_organization_id => v_organization_id ,
1004 p_location_id => v_location_id ,
1005 p_tax_type => tax_type_rec.t_type ,
1006 p_account_name => jai_constants.liability_interim
1007 );
1008 --Added by Wenqiong for Bug 14253668 begin
1009 ELSIF nvl(lv_trx_type,'#') = jai_constants.ar_invoice_type_cm THEN
1010 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
1011 p_regime_id => ln_regime_id ,
1012 p_organization_type => jai_constants.service_tax_orgn_type ,
1013 p_organization_id => v_organization_id ,
1014 p_location_id => v_location_id ,
1015 p_tax_type => tax_type_rec.t_type ,
1016 p_account_name => jai_constants.recovery_interim
1017 );
1018 END IF;
1019 --Added by Wenqiong for Bug 14253668 end
1020 END IF;
1021 /*ER 11821537 - End*/
1022
1023 IF v_ccid IS NULL THEN
1024 /*
1025 || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
1026 || This is an error condition and the current processing has to be stopped
1027 */
1028 /* raise_application_error (-20130,'Invalid Code combination ,please check the Service Tax - Tax Accounting Setup');
1029 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Invalid Code combination ,please check the Service Tax - Tax Accounting Setup' ; return ;
1030 END IF;
1031 /*
1032 || Start of bug 4212816
1033 || Code modified by aiyer for the VAT Enhancement
1034 || Get the Tax accounting information from the vat regime setup when the taxes are as mentioned below taxes
1035 */
1036
1037 ELSIF UPPER(nvl(ln_regime_code,'####')) = jai_constants.vat_regime THEN
1038
1039 /*********************************************************************************************************
1040 || Validate whether the item attached is vatable or not
1041 *********************************************************************************************************/
1042 if pr_new.inventory_item_id is not null then /*ssumaith - bug#6104491 */
1043 jai_inv_items_pkg.jai_get_attrib (
1044 p_regime_code => ln_regime_code ,
1045 p_organization_id => v_organization_id ,
1046 p_inventory_item_id => pr_new.inventory_item_id ,
1047 p_attribute_code => jai_constants.rgm_attr_item_applicable ,
1048 p_attribute_value => lv_attr_value ,
1049 p_process_flag => lv_error_flag ,
1050 p_process_msg => lv_error_message
1051 ) ;
1052 IF lv_error_flag <> jai_constants.successful THEN
1053 /*
1054 || Encountered an error from the call to jai_ar_rgm_processing_pkg.get_regime_info
1055 || Stop processing and thorw an error
1056 */
1057 /* raise_application_error (-20130,lv_error_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_error_message ; return ;
1058 /*
1059 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
1060 EXCEPTION_CODE => NULL ,
1061 EXCEPTION_TEXT => lv_error_message
1062 );
1063 */
1064
1065
1066 END IF;
1067 end if; /*ssumaith - bug# 6104491 */
1068 /*********************************************************************************************************
1069 || Raise an error if item is not vatable
1070 *********************************************************************************************************/
1071 if pr_new.inventory_item_id is not null and nvl(lv_attr_value,'N') = 'N' THEN
1072 /* above if condition before the if added by ssumaith - bug# 6104491 */
1073 /*
1074 || Item is not vatable . Stop processing and throw an error
1075 */
1076 /* raise_application_error (-20130,'ITEM not vatable'); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'ITEM not vatable' ; return ;
1077 /* DO not delete this code, enable this code while doing the messageing project
1078 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
1079 EXCEPTION_CODE => NULL ,
1080 EXCEPTION_TEXT => 'Cannot attach VAT type of taxes to non vatable items.'
1081 );
1082 */
1083
1084
1085
1086 END IF;
1087 /*********************************************************************************************************
1088 || Get the code combination id from the Organization/Regime Registration setup
1089 || by calling the function jai_cmn_rgm_recording_pkg.get_account
1090 *********************************************************************************************************/
1091
1092 -- 25/05/2007 by sacsethi for bug 6072461
1093 -- Previously Organziation id was going wrong and location id was wrong
1094
1095 --Added by Bo Li for Bug 9705313 Begin
1096 -----------------------------------------------------------------------
1097
1098 if NOT (JAI_AR_RCTLA_TRIGGER_PKG.is_this_projects_context (lv_intf_hdr_ctx)) then --by mmurtuza for bug 13649994
1099
1100 /*OPEN check_rma_credit_cur(v_order_number,ln_order_line_id);*/ /*Commected and added below by mmurtuza for bug 14846750*/
1101 OPEN check_rma_credit_cur(ln_order_line_id);
1102 FETCH check_rma_credit_cur
1103 INTO ln_rma_flag ;
1104 CLOSE check_rma_credit_cur;
1105
1106
1107 OPEN check_shippable_item_cur(ln_order_line_id);
1108 FETCH check_shippable_item_cur
1109 INTO ln_nonship_rma_flag ;
1110 CLOSE check_shippable_item_cur;
1111
1112 end if; -- bug 13649994
1113
1114 IF ln_rma_flag > 0 OR ln_nonship_rma_flag > 0
1115 THEN
1116 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
1117 p_regime_id => ln_regime_id ,
1118 p_organization_type => jai_constants.orgn_type_io ,
1119 p_organization_id => v_organization_id ,
1120 p_location_id => v_location_id ,
1121 p_tax_type => tax_type_rec.t_type ,
1122 p_account_name => jai_constants.recovery
1123 );
1124 ELSE
1125 ----------------------------------------------------------------------
1126 --Added by Bo Li for Bug 9705313 End
1127 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
1128 p_regime_id => ln_regime_id ,
1129 p_organization_type => jai_constants.orgn_type_io ,
1130 p_organization_id => v_organization_id ,
1131 p_location_id => v_location_id ,
1132 p_tax_type => tax_type_rec.t_type ,
1133 p_account_name => jai_constants.liability_interim
1134 );
1135
1136 End IF; -- Added by Bo Li for Bug 9705313
1137 IF v_ccid IS NULL THEN
1138 /**********************************************************************************************************
1139 || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
1140 || This is an error condition and the current processing has to be stopped
1141 **********************************************************************************************************/
1142 /* raise_application_error (-20130,'Invalid Code combination ,please check the VAT Tax - Tax Accounting Setup'); */
1143 pv_return_code := jai_constants.expected_error ;
1144 pv_return_message := 'Invalid Code combination ,please check the VAT Tax - Tax Accounting Setup ' ;
1145 return ;
1146 /*
1147 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
1148 EXCEPTION_CODE => NULL ,
1149 EXCEPTION_TEXT => 'Invalid Code combination ,please check the VAT Tax - Tax Accounting Setup'
1150 );
1151 */
1152 END IF;
1153
1154 /*
1155 || End of bug 4212816
1156 */
1157
1158 /* Added by CSahoo for the TCS enhancements Bug# 5631784 */
1159 ELSIF UPPER(nvl(ln_regime_code,'####')) = jai_constants.tcs_regime THEN -- Start of A1
1160
1161 /*********************************************************************************************************
1162 || Get the code combination id from the Organization/Regime Registration setup
1163 || by calling the function jai_rgm_trx_recording_pkg.get_account
1164 *********************************************************************************************************/
1165
1166
1167 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
1168 p_regime_id => ln_regime_id ,
1169 p_organization_type => jai_constants.orgn_type_io ,
1170 p_organization_id => v_organization_id ,
1171 p_location_id => v_location_id ,
1172 p_tax_type => tax_type_rec.t_type ,
1173 p_account_name => jai_constants.liability_interim
1174 );
1175 IF v_ccid IS NULL THEN
1176 /**********************************************************************************************************
1177 || Code Combination id has been returned as null from the function jai_rgm_trx_recording_pkg.get_account
1178 || This is an error condition and the current processing has to be stopped
1179 **********************************************************************************************************/
1180 raise_application_error (-20130,'Invalid Code combination ,please check the TCS Tax - Tax Accounting Setup');
1181
1182 END IF;
1183
1184 /*End of bug 5631784 */
1185
1186 ELSE -- ELSE of A1
1187 /*
1188 || As tax type is not SERVICE hence
1189 || get code combination from tax definition setup
1190 */
1191 OPEN TAX_CCID_CUR(TAX_TYPE_REC.taxid);
1192 FETCH TAX_CCID_CUR INTO v_ccid;
1193 CLOSE TAX_CCID_CUR;
1194
1195 END IF; --End if of A1
1196 -- End of bug 4089440
1197
1198 IF TAX_TYPE_REC.t_type = 'TDS' THEN
1199 TAX_TYPE_REC.tax_amt := 0;
1200 END IF;
1201 END IF;--Added by zhiwei for GST get ccid by regime setup BUG#10233402 20101027
1202
1203 INSERT INTO JAI_AR_TRX_INS_LINES_T ( -- paddr,
1204 -- 6842749
1205 extended_amount,
1206 customer_trx_line_id,
1207 customer_trx_id,
1208 set_of_books_id,
1209 link_to_cust_trx_line_id,
1210 line_type,
1211 uom_code,
1212 vat_tax_id,
1213 acctd_amount,
1214 amount,
1215 CODE_COMBINATION_ID,
1216 cust_trx_line_sales_rep_id,
1217 insert_update_flag,
1218 last_update_date,
1219 last_updated_by,
1220 creation_date,
1221 created_by,
1222 last_update_login,
1223 tax_rate, --Tax_rate column added by Jagdish/Subbu 10-Jun-01
1224 error_flag ,--added on 22-Mar-2002 by RPK to store the error_flag.Initially it --will be NULL
1225 source, -- column added by sriram on 6th May 2002.
1226 org_id, -- Added by sriram bug # 2779967
1227 line_number ) -- Added by sriram Bug # 2769439
1228
1229 VALUES ( -- NULL, /* Previously passing v_paddr. Replaced with NULL by rallamse bug#4448789 */
1230 round( TAX_TYPE_REC.tax_amt, ln_inv_curr_precision ), /* rounding based on inv currency precision - bug# 5364120*/
1231 TAX_TYPE_REC.LINE_ID,
1232 v_customer_trx_id,
1233 v_books_id,
1234 v_customer_trx_line_id,
1235 v_line_type,
1236 TAX_TYPE_REC.uom,
1237 ln_tax_rate_id, --v_vat_tax, /* Modified by Ramananda for bug#4468353 due to ebtax uptake by AR */
1238 v_converted_rate * TAX_TYPE_REC.tax_amt,
1239 round(TAX_TYPE_REC.tax_amt, ln_inv_curr_precision ) , /* rounding based on inv currency precision - bug# 5364120*/
1240 v_ccid,
1241 v_salesrep_id,
1242 'U',
1243 pr_new.last_update_date,
1244 pr_new.last_updated_by,
1245 pr_new.creation_date,
1246 pr_new.created_by,
1247 pr_new.last_update_login,
1248 TAX_TYPE_REC.tax_rate, --Tax_rate column added by Jagdish/Subbu 10-Jun-01
1249 'P',
1250 v_created_from,
1251 v_org_id , -- added by sriram bug # 2779967
1252 tax_type_rec.tax_line_no ); -- added by sriram - bug # 2769439
1253 --END; --19-MAR-2002
1254 v_tax_exists := 1; --added by mmurtuza for bug 13042890
1255 END LOOP;
1256 EXCEPTION
1257 WHEN OTHERS THEN
1258 v_err_mesg := SQLERRM;
1259 /* RAISE_APPLICATION_ERROR(-20003,'error in processing the invoice ..' || v_trx_num || v_err_mesg);
1260 */ 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
1261 END;
1262 -- the following if added by Sriram / Pavan on 06-May-2002
1263 IF v_created_from = 'ARXTWMAI' AND v_tax_exists = 1 THEN --added condition of v_tax_exists by mmurtuza for bug 13042890
1264 result := fnd_request.set_mode(TRUE);
1265 req_id := fnd_request.submit_request( 'JA', 'JAILINEGL', 'AR Tax and Freight Defaultation',
1266 SYSDATE, FALSE,v_customer_trx_id, v_customer_trx_line_id );
1267 END IF;
1268 END ARI_T1 ;
1269
1270 /*
1271 REM +======================================================================+
1272 REM NAME ARU_T1
1273 REM
1274 REM DESCRIPTION Called from trigger JAI_JAR_TL_ARIUD_T1
1275 REM
1276 REM NOTES Refers to old trigger JAI_JAR_TL_ARU_T2
1277 REM
1278 REM +======================================================================+
1279 */
1280 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
1281 v_books_id NUMBER;
1282 v_salesrep_id NUMBER;
1283 v_line_type VARCHAR2(30);
1284 v_vat_tax NUMBER;
1285 v_ccid NUMBER;
1286 v_cust_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.customer_trx_line_id%TYPE;
1287 -- v_paddr v$session.paddr%TYPE;
1288 -- bug#6842749
1289 v_counter NUMBER;
1290 v_customer_trx_id NUMBER; -- := pr_old.customer_trx_id; --rpokkula for File.Sql.35
1291 c_from_currency_code VARCHAR2(15);
1292 c_conversion_type VARCHAR2(30);
1293 c_conversion_date DATE;
1294 c_conversion_rate NUMBER := 0;
1295 v_converted_rate NUMBER := 1;
1296 req_id NUMBER;
1297 result BOOLEAN;
1298 v_created_from VARCHAR2(30);
1299 v_insert_update_flag VARCHAR2(1) ;
1300 v_organization_id NUMBER ;
1301 v_location_id NUMBER ;
1302 v_batch_source_id NUMBER ;
1303 v_register_code VARCHAR2(50);
1304 -- added by sriram - bug # 2779967
1305 v_org_id ra_customer_trx_all.org_id%type;
1306 /*Bug 8371741 - Start*/
1307 v_line_amount NUMBER := 0;
1308 v_quantity NUMBER;
1309 v_trans_type VARCHAR2(30);
1310 v_line_tax_amount NUMBER := 0;
1311 l_tcs_line_num NUMBER := 0;
1312 l_tcs_sur_line_num NUMBER := 0;
1313 ln_tcs_regime_id JAI_RGM_DEFINITIONS.regime_id%type;
1314 l_org_id NUMBER;
1315 l_bill_to_customer_id NUMBER;
1316 l_bill_to_site_use_id NUMBER;
1317 ln_organization_id NUMBER;
1318 ln_trx_date DATE;
1319 ln_threshold_slab_id jai_ap_tds_thhold_slabs.threshold_slab_id%type;
1320 ln_threshold_tax_cat_id jai_ap_tds_thhold_taxes.tax_category_id%type;
1321 lv_process_flag VARCHAR2(2);
1322 lv_process_message VARCHAR2(1996);
1323 ln_tax_amount NUMBER;
1324 l_tot_tax_lines NUMBER;
1325 l_max_tax_line_no NUMBER;
1326 /*Bug 8371741 - End*/
1327
1328 /*Bug 11821537 - Fetch the effective date on which Service Tax needs to accounted in accrual basis instead of cash - Start*/
1329 ld_st_accrual_date DATE;
1330 ld_trx_date DATE;
1331 v_tax_exists NUMBER :=0; --added by mmurtuza for bug 13042890
1332 --Commented by Chong.Lei for POT code port begin
1333 /*
1334 CURSOR c_get_st_accrual_date (p_regime_id NUMBER)
1335 IS
1336 SELECT to_date(attribute_value, 'DD/MM/YYYY')
1337 FROM jai_rgm_registrations
1338 WHERE regime_id = p_regime_id
1339 AND attribute_code = 'EFF_DATE_ST_PT'
1340 AND attribute_type_code = 'OTHERS'
1341 AND registration_type = 'OTHERS';
1342
1343 CURSOR c_get_trx_date
1344 IS
1345 SELECT trx_date
1346 FROM ra_customer_trx_all
1347 WHERE customer_trx_id = v_customer_trx_id;
1348 */
1349 --Commented by Chong.Lei for POT code port end
1350 -- Added by Chong.Lei for POT code port begin
1351 ---------------------------------------------------------------------------------------------------------
1352 /*Bug 12805386 - Fetch Effective Date of Point of Taxation only if Organization Type is either 'INDIVIDUALS' or 'PARTNERSHIP FIRM'
1353 or 'PROPRIETARY FIRM' and if the Service Type is one mentioned in Rule 7 of Point of Taxation Rules 2011 i.e
1354 105-p, 105-q, 105-s, 105-t, 105-u, 105-za, 105-zzzzm*/
1355
1356 CURSOR c_get_st_accrual_date(p_regime_id NUMBER, p_organization_id NUMBER, p_location_id NUMBER) IS
1357 select to_date(attribute_value, 'DD/MM/YYYY')
1358 from JAI_RGM_ORG_REGNS_V
1359 where regime_id = p_regime_id
1360 and organization_id = p_organization_id
1361 and location_id = p_location_id
1362 AND attribute_code = 'EFF_DATE_ST_PT'
1363 AND attribute_type_code = 'OTHERS'
1364 AND registration_type = 'OTHERS'
1365 AND (NOT EXISTS
1366 (select '1'
1367 from JAI_RGM_ORG_REGNS_V
1368 where regime_id = p_regime_id
1369 and attribute_code IN 'INV_ORG_CLASSIFICATION'
1370 and attribute_value <> 'ORGANIZATION'
1371 and organization_id = p_organization_id
1372 and location_id = p_location_id)
1373 OR
1374 NOT EXISTS
1375 (select '1'
1376 from JAI_RGM_ORG_REGNS_V
1377 where regime_id = p_regime_id
1378 and attribute_code IN 'SERVICE TYPE'
1379 and attribute_value <> 'OTHER'
1380 and organization_id = p_organization_id
1381 and location_id = p_location_id)
1382 );
1383
1384 CURSOR c_get_trx_date
1385 IS
1386 SELECT gl_date --Xiao for POT changes, reg bug#12533434
1387 FROM ra_cust_trx_line_gl_dist_all
1388 WHERE customer_trx_id = v_customer_trx_id
1389 AND customer_trx_line_id = pr_new.customer_trx_line_id
1390 AND account_class = 'REV';--Xiao for POT changes, reg bug#12533434
1391 ---------------------------------------------------------------------------------------------------------
1392 -- Added by Chong.Lei for POT code port end
1393 /*Bug 11821537 - End*/
1394
1395 -- added by sriram - bug # 2779967
1396
1397 /*Bug 8371741 - Start*/
1398 CURSOR GC_GET_REGIME_ID (CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE)
1399 IS
1400 SELECT REGIME_ID
1401 FROM JAI_RGM_DEFINITIONS
1402 WHERE REGIME_CODE = CP_REGIME_CODE;
1403
1404 Cursor transaction_type_cur IS
1405 Select a.type
1406 From RA_CUST_TRX_TYPES_ALL a, RA_CUSTOMER_TRX_ALL b
1407 Where a.cust_trx_type_id = b.cust_trx_type_id
1408 And b.customer_trx_id = v_customer_trx_id
1409 And NVL(a.org_id,0) = NVL(b.org_id,0);
1410
1411 CURSOR bind_cur IS
1412 SELECT RCTA.org_id,
1413 RCTA.bill_to_customer_id,
1414 NVL(RCTA.bill_to_site_use_id,0),
1415 RCTA.trx_date
1416 FROM RA_CUSTOMER_TRX_ALL RCTA
1417 WHERE RCTA.customer_trx_id = v_customer_trx_id;
1418
1419 /*Bug 8371741 - End*/
1420 /*Commented by kunkumar for bug#6066813 start Bug 6012570 (5876390) */
1421 /*cursor c_get_hdr_ctx
1422 is
1423 select interface_header_context
1424 from ra_customer_trx_all
1425 where customer_trx_id = pr_new.customer_trx_id;*/
1426 /*commented the above cursor and added the following for bug#5597146*/
1427 /*commented the cursor c_get_hdr_ctx for bug#8310220
1428 cursor c_get_hdr_ctx
1429 is
1430 select distinct interface_line_context
1431 from ra_customer_trx_lines_all
1432 where customer_trx_id = pr_new.customer_trx_id
1433 and customer_trx_line_id = pr_new.customer_trx_line_id --added for bug#5597146
1434 and interface_line_context is not null
1435 and rownum = 1;*/
1436
1437
1438 lv_intf_hdr_ctx ra_customer_trx_all.interface_header_context%type ;
1439 /* End commented by kunkumar for bug#6066813 */
1440 /*end Bug 6012570 (5876390) */
1441
1442 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 Begin
1443 -- TD18-changed Trading AR Invoice
1444 -------------------------------------------------------------------------
1445 /*
1446 v_ORDER_PRICE_EXCISE_INCLUSIVE JAI_CMN_INVENTORY_ORGS.ORDER_PRICE_EXCISE_INCLUSIVE%type; -- date 15/06/2007 sacsethi for bug 6131957
1447
1448 -- date 15/06/2007 sacsethi for bug 6131957
1449
1450 cursor c_ORDER_PRICE_EXCISE_INCLUSIVE(p_organization_id number,p_location_id number) is
1451 select ORDER_PRICE_EXCISE_INCLUSIVE
1452 from JAI_CMN_INVENTORY_ORGS
1453 where organization_id = p_organization_id
1454 and location_id = p_location_id;
1455 */
1456 -------------------------------------------------------------------------
1457 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 End
1458
1459 -- Added by zhiwei for GST get ccid by regime setup BUG#10233402 20101027 BEGIN
1460 ------------------------------------------------------------------
1461 lv_enable_gst_flag VARCHAR2(1);
1462 CURSOR get_gst_organization_location(cn_customer_trx_id IN NUMBER)--v_customer_trx_line_id
1463 IS
1464 SELECT ORGANIZATION_ID,LOCATION_ID
1465 FROM JAI_AR_TRXS
1466 WHERE CUSTOMER_TRX_ID = cn_customer_trx_id;
1467
1468 lv_log VARCHAR2(1000);
1469 x number;
1470 CURSOR GET_COUNT(cn_customer_trx_line_id IN NUMBER)is
1471 SELECT
1472 COUNT(1)
1473 FROM
1474 JAI_AR_TRX_TAX_LINES
1475 WHERE
1476 link_to_cust_trx_line_id = cn_customer_trx_line_id ;
1477 ------------------------------------------------------------------
1478 -- Added by zhiwei for GST get ccid by regime setup BUG#10233402 20101027 END
1479
1480
1481
1482 CURSOR tax_type_cur IS
1483 SELECT
1484 a.tax_id taxid ,
1485 a.tax_rate ,
1486 a.uom uom ,
1487 a.tax_amount tax_amt ,
1488 b.tax_type t_type ,
1489 a.customer_trx_line_id line_id ,
1490 a.tax_line_no tax_line_no
1491 FROM
1492 JAI_AR_TRX_TAX_LINES a ,
1493 JAI_CMN_TAXES_ALL b
1494 WHERE
1495 link_to_cust_trx_line_id = pr_old.customer_trx_line_id and
1496 a.tax_id = b.tax_id
1497 and nvl(b.reverse_charge_flag,'N') = 'N' --Added by Qiong for reverse charge bug#16001407
1498 /*Bug 12895321 - Inclusive taxes are not interfaced in GL if Order contains only Inclusive Taxes*/
1499 --AND NVL(b.inclusive_tax_flag,'N') = 'N' --Added by Jia Li for Tax inclusive Computations on 2007/11/22
1500 ORDER BY
1501 1;
1502
1503 -- bug#6842749
1504 /*
1505 CURSOR PADDR_CUR IS
1506 SELECT paddr
1507 FROM v$session
1508 WHERE audsid = USERENV('SESSIONID');
1509 */
1510
1511 CURSOR BOOKS_TRX_CUR IS
1512 SELECT set_of_books_id, primary_salesrep_id, invoice_currency_code, exchange_rate_type, exchange_date, exchange_rate
1513 FROM JAI_AR_TRXS
1514 WHERE customer_trx_id = v_customer_trx_id;
1515
1516
1517 --Commented for bug#4468353
1518 /*
1519 CURSOR VAT_TAX_CUR(cp_tax_code AR_VAT_TAX_ALL.tax_code%type) IS
1520 SELECT DISTINCT vat_tax_id
1521 FROM AR_VAT_TAX_ALL
1522 WHERE UPPER(tax_code) = cp_tax_code ;
1523 */
1524
1525
1526 /* Added by Ramananda for bug#4468353 , start
1527 CURSOR ORG_CUR IS
1528 SELECT ORG_ID
1529 FROM RA_CUSTOMER_TRX_ALL
1530 WHERE CUSTOMER_TRX_ID = pr_new.customer_trx_id;*/
1531
1532 --commented the above org_cur cursor and added the following org_cur cursor for bug#5597146
1533 CURSOR ORG_CUR IS
1534 SELECT ORG_ID
1535 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
1536 WHERE CUSTOMER_TRX_ID = pr_new.customer_trx_id
1537 AND account_class ='REC'
1538 AND latest_rec_flag ='Y';
1539
1540 lv_tax_regime_code zx_rates_b.tax_regime_code%type ;
1541 ln_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%type ;
1542 ln_tax_rate_id zx_rates_b.tax_rate_id%type ;
1543 /* Added by Ramananda for bug#4468353 , end */
1544
1545 CURSOR TAX_CCID_CUR(p_tax_id IN NUMBER) IS
1546 SELECT tax_account_id
1547 FROM JAI_CMN_TAXES_ALL B
1548 WHERE B.tax_id = p_tax_id ;
1549
1550 CURSOR GL_DATE_CUR IS
1551 SELECT gl_date
1552 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
1553 WHERE CUSTOMER_TRX_LINE_ID = pr_old.customer_trx_line_id;
1554
1555 CURSOR CREATED_FROM_CUR IS
1556 SELECT created_from
1557 FROM JAI_AR_TRXS -- table reference was previously RA_CUSTOMER_TRX_ALL - using JA_IN_RA_CUSTOMER_TRX instead - bug# 2728636
1558 WHERE customer_trx_id = v_customer_trx_id;
1559
1560 CURSOR Insert_Update_Cur(p_customer_trx_line_id IN NUMBER) IS
1561 SELECT INSERT_UPDATE_FLAG
1562 FROM JAI_AR_TRX_INS_LINES_T
1563 WHERE customer_trx_id = V_CUSTOMER_TRX_ID AND
1564 Customer_trx_line_id = p_customer_trx_line_id
1565 ORDER BY CUSTOMER_TRX_LINE_ID;
1566
1567 CURSOR SO_AR_HDR_INFO IS
1568 SELECT organization_id, location_id, batch_source_id
1569 FROM JAI_AR_TRXS
1570 WHERE Customer_Trx_ID = v_customer_trx_id;
1571
1572 CURSOR register_code_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER, p_batch_source_id IN NUMBER) IS
1573 SELECT register_code
1574 FROM JAI_OM_OE_BOND_REG_HDRS
1575 WHERE organization_id = p_org_id AND location_id = p_loc_id AND
1576 register_id IN (SELECT register_id
1577 FROM JAI_OM_OE_BOND_REG_DTLS
1578 WHERE order_type_id = p_batch_source_id AND order_flag = 'N');
1579
1580 /* Bug 5243532. Added by Lakshmi Gopalsami
1581 * Removed cursor set_of_books_cur and c_opr_set_of_books_id
1582 * and implemented caching logic.
1583 */
1584 CURSOR cur_chk_rgm ( cp_tax_type JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE )
1585 IS
1586 SELECT
1587 regime_id ,
1588 regime_code
1589 FROM
1590 jai_regime_tax_types_v jrttv
1591 WHERE
1592 upper(jrttv.tax_type) = upper(cp_tax_type);
1593
1594
1595 ln_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
1596 lv_attr_value JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE;
1597
1598 -- Start of bug 4089440
1599 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
1600 lv_error_flag VARCHAR2(2) ;
1601 lv_error_message VARCHAR2(4000) ;
1602 -- End of bug 4089440
1603
1604 /* Bug 5243532. Added by Lakshmi Gopalsami
1605 * Removed cursor set_of_books_cur
1606 * and implemented caching logic.
1607 */
1608 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1609
1610 ln_inv_curr_precision NUMBER;
1611
1612 /* added by CSahoo - bug# 5364120*/
1613 CURSOR c_inv_curr_precision(cp_currency_code varchar2) IS
1614 SELECT NVL(minimum_accountable_unit,NVL(precision,2)) curr_precision
1615 FROM fnd_currencies
1616 WHERE currency_code = cp_currency_code;
1617
1618 lv_trx_type ra_cust_trx_types_all.type%TYPE; --Added by Wenqiong for Bug 14253668
1619 --added the function for bug#8310220
1620 FUNCTION get_hdr_ctx (p_customer_trx_id IN NUMBER,
1621 p_customer_trx_line_id IN NUMBER)
1622 RETURN VARCHAR2
1623 IS
1624 PRAGMA AUTONOMOUS_TRANSACTION;
1625 cursor c_get_hdr_ctx
1626 is
1627 select distinct interface_line_context
1628 from ra_customer_trx_lines_all
1629 where customer_trx_id = p_customer_trx_id
1630 and customer_trx_line_id = p_customer_trx_line_id --added for bug#5597146
1631 and interface_line_context is not null
1632 and rownum = 1;
1633 lv_hdr_ctx ra_customer_trx_lines_all.INTERFACE_LINE_CONTEXT%type;
1634 BEGIN
1635 OPEN c_get_hdr_ctx;
1636 FETCH c_get_hdr_ctx into lv_hdr_ctx;
1637 CLOSE c_get_hdr_ctx;
1638
1639 return lv_hdr_ctx;
1640 END get_hdr_ctx;
1641
1642 BEGIN
1643 pv_return_code := jai_constants.successful ;
1644 /*------------------------------------------------------------------------------------------
1645 FILENAME: JA_IN_APPS_AR_LINES_UPDATE_TRG.sql
1646 CHANGE HISTORY:
1647 S.No Date Author and Details
1648 1. 2001/04/09 Changed the Cases for Register Codes
1649 2. 2001/06/11 Jagdish
1650 Added tax_rate for Receipts Discounts Issue
1651 3. 2001/06/22 Anuradha Parthasarathy
1652 Code commented and added to improve performance.
1653 4. 2001/12/13 Anuradha Parthasarathy
1654 Code commented because if the updated tax amount is zero the tax lines
1655 need to be corrected in the Base Tables as well.
1656 5. 2002/05/09 Sriram
1657 Added the Source Column in the Column list in the insert statement
1658 This Column was added in the JAI_AR_TRX_INS_LINES_T table because the
1659 AR Tax and Freight Defaultation Concurrent - was split into 2 concurrents
1660 doing the same functionality - one being called from the AR side for manual invoice
1661 from this trigger and another from OM side which is scheduled to run on a periodid basis
1662 6. 2002/05/09 Sriram
1663 Added the if condition at the bottom of the trigger to conditionally call the concurrent
1664 only if is a manual invoice.
1665 7. 2003/01/02 Ssumaith Bug # 2728636 File Version 615.2
1666 Reference to table RA_CUSTOMER_TRX_ALL was present in the cursor Created_from_cur
1667 Which was causing a mutating error and causing the transaction to error out
1668 with unhandled exception.This has been changed to point to JAI_AR_TRXS
1669 table instead which takes care of the issue.
1670 8. 2003/04/07 SSUMAITH.Bug # 2779967
1671 column Org id was not inserted in the JAI_AR_TRX_INS_LINES_T table. This needs to be
1672 inserted in the table , because when processing the records using the 'India Local Concurrent'
1673 only records that belong to the orgid of the current responsiblity needs to be picked up.
1674 9. 2003/04/10 SSUMAITH - Bug # 2769439
1675 Also inserting the tax line number in the JAI_AR_TRX_INS_LINES_T table.
1676 This is necessary for creating links between OM and AR.
1677
1678
1679 10. 2005/27/01 aiyer - Bug # 4089440 File Version 115.1
1680 Issue:-
1681 In case of service invoices having service/service_education type of taxes the code combination id should be picked
1682 up from regime tax setup. This is being done as a part of the service tax enhancement
1683
1684 Solution:-
1685 The check that, for service type of taxes ccid should be picked up from regime tax setup has been impletemented.
1686 Called the procedure jai_ar_rgm_processing_pkg.get_regime_info is being called for regime setup validation.
1687 Aslo called the function jai_cmn_rgm_recording_pkg.get_account to get the ccid in such cases.
1688
1689 Dependency introduced as a part of this bug:-
1690 This file should be released on top of Bug 4146708.
1691
1692 11. 01-Mar-2005 aiyer - Bug # 4212816 File Version 115.2
1693 Issue:-
1694 In case of invoices having any of the taxes setup for VAT REGIME, the code combination should get picked up from the
1695 VAT Regime setup. This is being done as a part of the VAT enhancement
1696
1697 Solution:-
1698 The check that, for taxes belonging to the VAT regime, ccid should be picked up from VAT regime setup has been impletemented.
1699 The account info is fetched using the function jai_cmn_rgm_recording_pkg.get_account.
1700
1701
1702 Dependency introduced as a part of this bug:-
1703 This file should be released on top of Bug 4245089.
1704 Datamodel changes for VAT
1705
1706
1707 12. 02-Apr-2005 aiyer - Bug # 4279702 File Version 115.3
1708 Issue:-
1709 VAT regime code does not get reinitialized for any tax type which does not fall in the VAT / Service TAx regime
1710
1711 Solution:-
1712 Changed the triggers ja_in_apps_ar_lines_insert_trg and ja_in_apps_ar_lines_update_trg to reinitialize the regime codes
1713 every time processing happens for a tax type.
1714
1715 Dependency introduced as a part of this bug:-
1716 This file should be released on top of Bug 4245089.
1717
1718 13. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
1719 DB Entity as required for CASE COMPLAINCE. Version 116.1
1720
1721 14. 13-Jun-2005 File Version: 116.2
1722 Ramananda for bug#4428980. Removal of SQL LITERALs is done
1723
1724 15. 25-Jan-2007 CSahoo for Bug#5631784, File Version 120.3
1725 Forward Porting of BUG#4742259
1726 Accounting information popluated for TCS regime
1727
1728 15. 14-Feb-2007 CSahoo BUG#5364120, File Version - 120.4
1729 Forward Porting of 11i BUG#5346489
1730 The transaction amounts are rounded based on the currency precision of the invoice currency code
1731 when the insert is done in the JAI_AR_TRX_INS_LINES_T.
1732 Added the cursor c_inv_curr_precision.
1733
1734 Dependency due to this bug :- None
1735
1736 16. 15-Feb-2007 CSahoo Bug#5390583, File Version - 120.5
1737 Forward Porting of 11i bug#5357400
1738 When taxes are added manually to the ar invoice from the transactions localised screen and the
1739 batch source is set to bond register, the excise taxes are going to base AR. This should not go.
1740
1741 Fix :
1742 The code to take care of this was in a trading loop and would never reach there.
1743 17. 05-APR-2007 bduvarag for bug#5671400,File version 120.6
1744 Forward porting the changes done in 11i bug#4648231
1745
1746 Modified the code appropriately.
1747
1748 18 24/04/2007 cbabu for bug#5876390, File Version 120.7 (115.17 )
1749 FP: For project billing bond register functionality is not required hence added a check to see if
1750 the invoice is created by projects then bond register related logic should not be executed.
1751 19. 31/05/2007 CSahoo for bug#6081806, File Version 120.13
1752 added the sh cess tax types.
1753
1754
1755 20. 14/06/2007 sacsethi for bug 6072461 for file version - 120.15
1756
1757 This bug is used to fp 11i bug 5183031 for vat reveresal
1758
1759 Problem - Vat Reversal Enhancement not forward ported
1760 Solution - Changes has been done to make it compatible for vat reversal functioanlity also.
1761
1762 21. 15/06/2007 sacsethi for bug 6131957 for file version - 120.16
1763
1764 R12RUP03-ST1: TRADING TAKES EXCISE PRICE INCLUSIVE EVEN WHEN FLAG IS UNCHECKED
1765
1766 Variable v_order_price_excise_inclusive and cursor c_order_price_excise_inclusive is defined
1767
1768 22. 10/07/2007 CSahoo for bug#5597146, FileVersion 120.17
1769 modified the cursor ORG_CUR.
1770 23. 10/10/2007 CSahoo for bug#5597146, FileVersion 120.19
1771 modified the cursor c_get_hdr_ctx to remove the mutating trigger error.
1772 24. 11/10/2007 CSahoo for bug#5597146, File Version 120.20
1773 Added the following AND condition in the cursor c_get_hdr_ctx
1774 "and customer_trx_line_id = pr_new.customer_trx_line_id "
1775 25. 23/04/2009 CSahoo for bug#8310220, File Version 120.22.12010000.4,120.22.12010000.5
1776 Commented the cursor c_get_hdr_ctx. added the function get_hdr_ctx
1777
1778 26. 30-aug-2010 vkaranam for bug#10048317
1779 Issue:EXCISE TAXES NOT TRANSFERRING FROM SALES ORDER TO RECEIVABLE for trading organization.
1780 issue is in jai_jar_tl_t.plb:
1781
1782 IF NVL(v_register_code,'N') IN
1783 ('23D_EXPORT_WITHOUT_EXCISE','23D_EXPORT_EXCISE',
1784
1785 '23D_DOMESTIC_EXCISE','23D_DOM_WITHOUT_EXCISE','BOND_REG') THEN
1786
1787 --jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_ces
1788 s added by csahoo for bug#6081806
1789 IF Tax_Type_Rec.T_Type IN ('Excise','Addl. Excise','Other
1790 Excise','EXCISE_EDUCATION_CESS','CVD_EDUCATION_CESS',jai_constants.tax_type_sh
1791 _exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess) THEN
1792 TAX_TYPE_REC.tax_amt := 0;
1793 END IF;
1794 END IF;
1795
1796 Eventhough the order_price_excise_inclusive functionality was obsoleted the
1797 tax amount has been populated as "0" in jai_ar_trx_ins_lines_t.
1798
1799 Hence the issue
1800
1801 Fix:
1802 commeneted the code which will make the tax_type_rec.tax_amt to 0.
1803
1804 27 04-Mar-2011 Enhancement 11821537
1805 Description: Notification # 18/2011 proposes change in Point of Taxation for Service Tax.
1806 Service Tax Liability arises on creation of Invoice and not on Receipt of Payment from Customer
1807 i.e. Accounting changes to Accrual basis from Cash Basis
1808 Fix: Service Tax Libility Account will be hit directly instead of Interim Liablity as there
1809 will be no set off during Receipt/CM Application.
1810
1811 28. 10/02/2011 Added by Zhiwei for Open Interface ER bug#11683927.
1812 When update the AR transaction that are imported by Open Interface, procedure
1813 JAI_JAR_TL_TRIGGER_PKG.ARU_T1 will not be invoked, and the same logic is implemented
1814 in Open Interface Procedure.
1815
1816 29 12-Apr-2011 Bug 11936630
1817 Description: Data was inserted into JAI_AR_TRX_INS_LINES_T via tax_type_cur only if
1818 there was an Exclusive Tax Line. Hence for Order Imported AR Invoice with only
1819 Inclusive Taxes accounting was not happening.
1820 Fix: Data to be inserted into JAI_AR_TRX_INS_LINES_T irrespective of Tax Types.
1821
1822 29 18-May-2011 Xiao for POT changes, reg bug#12533434.
1823 Issue: Compare transaction date with POT date.
1824 fixed: Compare Accounting Date with POT date. Cursor c_get_trx_date is modified. --Added by Chong.Lei for POT code port
1825
1826 30. 03-Oct-2011 mmurtuza for bug 13042890
1827 Issue: INDIA-AR TAX AND FREIGHT DEFAULTATION IS FIRING WHEN TRANSACTION RAISED IN BASE AR Transaction witout IOL Taxes
1828 Fix: Added a variable v_tax_exists and Added condition in the trigger to check if taxes exists and the concurrent should fire only if taxes exists.
1829
1830 31. 27-Dec-2011 mmurtuza for bug 13532483
1831 Issue: TAXES ARE NOT REFLECT IN THE AR DISTRIBUTIONS when sum of taxes is zero
1832 Fix: Commented the part of code which returns the control when the old and new tax amoutns are same.
1833
1834 32. 30-Dec-2011 mmurtuza for bug 13532483
1835 Fix: reverted the cahnegs done for bug 13532483
1836
1837 33. 27-Aug-2012 mmurtuza for bug 14525160
1838 Issue: TAXES ARE NOT REFLECT IN THE AR DISTRIBUTIONS when sum of taxes is zero
1839 Fix: Commented the part of code which returns the control when the old and new tax amoutns are same.
1840
1841 33. 14-Feb-2013 mmurtuza for bug 16052256
1842 Issue: UNABLE TO APPLY TCS TAXES ON AR INVOICES
1843 Description: Added paramter p_called_from while calling jai_rgm_thhold_proc_pkg.get_threshold_slab_id
1844
1845 ==========================================================================================================================================================
1846
1847 Future Dependencies For the release Of this Object:-
1848 (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/
1849 A datamodel change )
1850
1851 ----------------------------------------------------------------------------------------------------------------------------------------------------
1852 Current Version Current Bug Dependent Files Version Author Date Remarks
1853 Of File On Bug/Patchset Dependent On
1854 ja_in_apps_ar_lines_update_trg.sql
1855
1856 115.1 4089440 IN60105D2 + Aiyer 27-Jan-2005 4146708 is the release bug for SERVICE/CESS
1857 4146708 enhancement release
1858
1859 115.2 4247989 IN60106 + 4146708 + 4245089 Aiyer 01-Mar-2005 4245089 is the Base bug for VAT.
1860 This has datamodel changes for VAT.
1861
1862 25. 22/11/2007 Added by Jia Li
1863 for Tax inclusive Computations
1864
1865 -------------------------------------------------------------------------------------------------------------------------------------------------------------*/
1866
1867 -- added by sriram - bug # 2779967
1868 v_org_id := FND_PROFILE.VALUE('ORG_ID');
1869 -- added by sriram - bug # 2779967
1870
1871 v_customer_trx_id := pr_old.customer_trx_id; --rpokkula for File.Sql.35
1872
1873 OPEN CREATED_FROM_CUR;
1874 FETCH CREATED_FROM_CUR INTO v_created_from;
1875 CLOSE CREATED_FROM_CUR;
1876 IF v_created_from IN ('ARXREC','ARXTWCMI') THEN /* Added on 19/9/99*/
1877 RETURN;
1878 END IF;
1879
1880 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, begin
1881 --When AR transction is imported by OFI Open Interface,
1882 --program returns, and the same logic is implemented in OFI Open Interface procedure.
1883 -------------------------------------------------------------------------------------
1884 IF(NVL(pr_new.interface_flag,'####')='Y')THEN
1885 RETURN;
1886 END IF;
1887 -------------------------------------------------------------------------------------
1888 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, end
1889
1890 /*Start commenting by mmurtuza for bug 13532483 */
1891 /*Uncommented by mmurtuza to revert changes done for bug 13532843*/
1892
1893 /*Start commenting by mmurtuza for bug 14525160*/
1894
1895 /*IF (NVL(pr_old.TAX_AMOUNT,0) = NVL(pr_new.TAX_AMOUNT,1)) AND (pr_new.Customer_Trx_Id = pr_old.Customer_Trx_Id)
1896 THEN
1897 RETURN;
1898 END IF;*/
1899
1900 /*End commenting by mmurtuza for bug 14525160*/
1901
1902 /*End commenting by mmurtuza for bug 13532483 */
1903 -- 6842749
1904 /* OPEN PADDR_CUR;
1905 FETCH PADDR_CUR INTO v_paddr;
1906 CLOSE PADDR_CUR;
1907 */
1908 OPEN BOOKS_TRX_CUR;
1909 FETCH BOOKS_TRX_CUR INTO v_books_id, v_salesrep_id, c_from_currency_code, c_conversion_type, c_conversion_date, c_conversion_rate ;
1910 CLOSE BOOKS_TRX_CUR;
1911
1912 /* start additions by CSahoo - bug# 5364120*/
1913
1914 IF c_from_currency_code IS NOT NULL THEN
1915 OPEN c_inv_Curr_precision(c_from_currency_code);
1916 FETCH c_inv_curr_precision INTO ln_inv_curr_precision;
1917 CLOSE c_inv_curr_precision;
1918 END IF;
1919
1920
1921 IF ln_inv_curr_precision IS NULL THEN
1922 ln_inv_curr_precision := 0;
1923 END IF;
1924
1925 /* end additions by CSahoo - bug#5364120 */
1926
1927 --- This Gives More then One Row, Still To solve This Issue
1928
1929 --Commented for bug#4468353
1930 /*
1931 OPEN VAT_TAX_CUR('LOCALIZATION');
1932 FETCH VAT_TAX_CUR INTO v_vat_tax;
1933 CLOSE VAT_TAX_CUR;
1934 */
1935
1936 /* Added by Ramananda for bug# due to ebtax uptake by AR, start */
1937 OPEN ORG_CUR;
1938 FETCH ORG_CUR INTO V_ORG_ID;
1939 CLOSE ORG_CUR;
1940
1941 OPEN jai_ar_trx_pkg.c_tax_regime_code_cur(V_ORG_ID);
1942 FETCH jai_ar_trx_pkg.c_tax_regime_code_cur INTO lv_tax_regime_code;
1943 CLOSE jai_ar_trx_pkg.c_tax_regime_code_cur ;
1944
1945 OPEN jai_ar_trx_pkg.c_max_tax_rate_id_cur(lv_tax_regime_code);
1946 FETCH jai_ar_trx_pkg.c_max_tax_rate_id_cur INTO ln_tax_rate_id;
1947 CLOSE jai_ar_trx_pkg.c_max_tax_rate_id_cur ;
1948 /* Added by Ramananda for bug# due to ebtax uptake by AR, end */
1949
1950 OPEN SO_AR_HDR_INFO ;
1951 FETCH SO_AR_HDR_INFO INTO v_organization_id, v_location_id, v_batch_source_id;
1952 CLOSE SO_AR_HDR_INFO ;
1953
1954 --Commented by kunkumar for bug#6066813 Start, Bug 6012570 (5876390)
1955 /*commented the following for bug#8310220
1956 open c_get_hdr_ctx;
1957 fetch c_get_hdr_ctx into lv_intf_hdr_ctx;
1958 close c_get_hdr_ctx;*/
1959 --added the following for bug#8310220
1960 lv_intf_hdr_ctx := get_hdr_ctx(pr_new.customer_trx_id,pr_new.customer_trx_line_id);
1961
1962 if JAI_AR_RCTLA_TRIGGER_PKG.is_this_projects_context (lv_intf_hdr_ctx) then
1963 /* For project invoices, there is no bond register functionality, so no need to do any processing for bond registers
1964 If line context is not PROJECT INVOICE then continue the normal processing flow */
1965 null;
1966
1967 else
1968 -- End commented by kunkumar for bug#6066813 End 6012570 (5876390) */
1969
1970 OPEN register_code_cur(v_organization_id, v_location_id, v_batch_source_id);
1971 FETCH register_code_cur INTO v_register_code;
1972 CLOSE register_code_cur;
1973
1974 --commented by kunkumar for bug#6066813
1975 end if;
1976 /* 6012570 (5876390) */
1977
1978 IF v_books_id IS NULL
1979 THEN
1980 /* Bug 5243532. Added by Lakshmi Gopalsami
1981 * Removed the cursor set_of_books_cur and implemented using caching logic.
1982 */
1983 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1984 (p_org_id => v_organization_id );
1985 v_books_id := l_func_curr_det.ledger_id;
1986 END IF;
1987 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_books_id ,c_from_currency_code ,
1988 c_conversion_date ,c_conversion_type, c_conversion_rate);
1989
1990
1991 -- date 15/06/2007 sacsethi for bug 6131957
1992 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 Begin
1993 -- TD18-changed Trading AR Invoice
1994 -------------------------------------------------------------------------
1995 /*
1996 open c_ORDER_PRICE_EXCISE_INCLUSIVE(v_organization_id, v_location_id);
1997 fetch c_ORDER_PRICE_EXCISE_INCLUSIVE into v_ORDER_PRICE_EXCISE_INCLUSIVE;
1998 close c_ORDER_PRICE_EXCISE_INCLUSIVE;
1999 */
2000 -------------------------------------------------------------------------
2001 -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22 End
2002
2003 FOR TAX_TYPE_REC IN TAX_TYPE_CUR
2004 LOOP
2005 SELECT COUNT(*) INTO v_counter
2006 FROM JAI_AR_TRX_INS_LINES_T b
2007 WHERE b.LINK_TO_CUST_TRX_LINE_ID = pr_new.Customer_Trx_Line_Id
2008 AND b.customer_trx_line_id = Tax_Type_Rec.LINE_ID;
2009
2010 --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
2011 /*commented for bug#10048317 as the functionality has been obsoleted as part of inclusive tax ER*
2012 IF NVL(v_register_code,'N') IN ('23D_EXPORT_WITHOUT_EXCISE','23D_EXPORT_EXCISE',
2013 '23D_DOMESTIC_EXCISE','23D_DOM_WITHOUT_EXCISE','BOND_REG') THEN
2014 --jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess added by csahoo for bug#6081806
2015 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
2016 TAX_TYPE_REC.tax_amt := 0;
2017 END IF;
2018 END IF;
2019 */
2020 --end if ; -- Deleted by Jia Li for Tax inclusive Computations on 2007/11/22, TD18
2021
2022 /*
2023 Date 14-jun-2007 by sacsethi for bug 6072461
2024 */
2025
2026 IF ( upper(Tax_Type_Rec.T_Type) = 'VAT REVERSAL' )
2027 THEN
2028 TAX_TYPE_REC.tax_amt := 0;
2029 END IF;
2030
2031 /*
2032 || added by CSahoo - bug# 5390583 In case of bond reg , the excise taxes should not go to base AR tables.
2033 */
2034 IF NVL(v_register_code,'N') = 'BOND_REG' THEN
2035 -- jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess added by csahoo for bug#6081806
2036 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
2037 TAX_TYPE_REC.tax_amt := 0;
2038 END IF;
2039 END IF;
2040
2041 /*
2042 || Ends additions by CSahoo - bug# 5390583
2043 */
2044
2045 OPEN Insert_Update_Cur(TAX_TYPE_REC.line_id);
2046 FETCH Insert_Update_Cur INTO v_insert_update_flag;
2047 CLOSE Insert_Update_Cur;
2048 IF NVL(v_insert_update_flag,'I') <> 'X' THEN
2049 IF TAX_TYPE_REC.t_type = 'Freight' THEN
2050 v_line_type := 'FREIGHT';
2051 ELSE
2052 v_line_type := 'TAX';
2053 END IF;
2054
2055 IF tax_type_rec.t_type = 'TDS' THEN
2056 tax_type_rec.tax_amt := 0;
2057 END IF;
2058
2059 /*
2060 || Code modified by aiyer for the bug 4279702.
2061 || Initializing the regime variables
2062 */
2063 ln_regime_id := null;
2064 ln_regime_code := null;
2065
2066 /*
2067 || Get the regime attached to a transaction tax type
2068 */
2069 OPEN cur_chk_rgm ( cp_tax_type => tax_type_rec.t_type);
2070 FETCH cur_chk_rgm INTO ln_regime_id,ln_regime_code ;
2071 CLOSE cur_chk_rgm ;
2072
2073
2074 -- Added by zhiwei for GST get ccid by regime setup BUG#10233402 20101027 BEGIN
2075 ------------------------------------------------------------------
2076 lv_enable_gst_flag := JAI_GST_GENERAL_PKG.IS_GST_ENABLED;
2077
2078
2079 OPEN get_gst_organization_location(pr_new.Customer_Trx_Id);
2080 FETCH get_gst_organization_location INTO v_organization_id,v_location_id;
2081 CLOSE get_gst_organization_location;
2082
2083 IF nvl(lv_enable_gst_flag,'N') = 'Y'
2084 THEN
2085 v_ccid := JAI_GST_GENERAL_PKG.get_gst_accounting(
2086 pv_account_source => 'AR' ,
2087 pv_trx_type => 'MANUAL' ,
2088 pn_tax_regim_id => ln_regime_id ,
2089 pv_tax_type_code => tax_type_rec.t_type ,
2090 pn_tax_id => tax_type_rec.Taxid ,
2091 pn_organization_id => v_organization_id ,
2092 pn_location_id => v_location_id ) ;
2093 IF v_ccid IS NULL THEN
2094 /**********************************************************************************************************
2095 || Code Combination id has been returned as null from the function jai_rgm_trx_recording_pkg.get_account
2096 || This is an error condition and the current processing has to be stopped
2097 **********************************************************************************************************/
2098
2099 raise_application_error (-20130,'Invalid Code combination ,please check the GST Tax - Tax Accounting Setup' || tax_type_rec.t_type || '&&' || tax_type_rec.Taxid);
2100
2101 END IF;
2102 ELSE
2103 -- ORGINAL TAX LOGIC FOR VAT/EXCISE/TDS/TCS/CESS/SHCESS/RMA
2104 ------------------------------------------------------------------
2105 -- Added by zhiwei for GST get ccid by regime setup BUG#10233402 20101027 END
2106
2107
2108 -- Start of bug 4089440
2109 /*
2110 || The following code has been added by aiyer for the bug 4089440
2111 || IF tax type is SERVICE or SERVICE-CESS then get the account info from regime setup
2112 || IF no setup is found then raise an error and stop the transaction.
2113 */
2114 IF upper(tax_type_rec.t_type) = upper(jai_constants.tax_type_service) OR
2115 upper(tax_type_rec.t_type) = upper(jai_constants.tax_type_service_edu_cess)
2116 OR upper(tax_type_rec.t_type)= upper(jai_constants.tax_type_sh_service_edu_cess) -- added by csahoo for bug#6081806
2117 THEN -- Start of A1
2118
2119 /*################################################################################################################
2120 || Get the regime id and also validate the Regime/Regime Registratiom Setup Information
2121 ################################################################################################################*/
2122
2123 jai_ar_rgm_processing_pkg.get_regime_info ( p_regime_code => jai_constants.service_regime ,
2124 p_tax_type_code => tax_type_rec.t_type ,
2125 p_regime_id => ln_regime_id ,
2126 p_error_flag => lv_error_flag ,
2127 p_error_message => lv_error_message
2128 );
2129
2130 IF lv_error_flag <> jai_constants.successful THEN
2131 /*
2132 || Encountered an error from the call to jai_ar_rgm_processing_pkg.get_regime_info
2133 || Stop processing and thorw an error
2134 */
2135 /* raise_application_error (-20130,lv_error_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_error_message ; return ;
2136 /*
2137 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
2138 EXCEPTION_CODE => NULL ,
2139 EXCEPTION_TEXT => lv_error_message
2140 );
2141 */
2142
2143 END IF;
2144
2145 /*################################################################################################################
2146 || Get Tax Account Info from the Regime Organization/Regime Registration setup
2147 ################################################################################################################*/
2148
2149 /*
2150 || Get the code combination id from the Organization/Regime Registration setup
2151 || by calling the function jai_cmn_rgm_recording_pkg.get_account
2152 */
2153
2154 /*ER 11821537 - Service Tax must hit Liability instead of Interim Liability due to change in point of taxation to accural from cash*/
2155 -- OPEN c_get_st_accrual_date(ln_regime_id); --Comment by Chong.Lei for POT code port
2156 -- Added by Chong.Lei for POT code port begin
2157 /*Bug 12805386 - Added parameters Inventory Organization ID and Location ID*/
2158 OPEN c_get_st_accrual_date(ln_regime_id, v_organization_id, v_location_id);
2159 -- Added by Chong.Lei for POT code port end
2160 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
2161 CLOSE c_get_st_accrual_date;
2162
2163 OPEN c_get_trx_date;
2164 FETCH c_get_trx_date INTO ld_trx_date;
2165 CLOSE c_get_trx_date;
2166
2167 --Added by Wenqiong for Bug 14253668 begin
2168 -------------------------------------------
2169 lv_trx_type := get_ar_trx_type(pr_old.Customer_Trx_Line_Id);
2170 -------------------------------------------
2171 --Added by Wenqiong for Bug 14253668 end
2172
2173 IF ld_trx_date >= ld_st_accrual_date THEN
2174 IF nvl(lv_trx_type,'#') <> jai_constants.ar_invoice_type_cm THEN --Added by Wenqiong for Bug 14253668
2175 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
2176 p_regime_id => ln_regime_id ,
2177 p_organization_type => jai_constants.service_tax_orgn_type ,
2178 p_organization_id => v_organization_id ,
2179 p_location_id => v_location_id ,
2180 p_tax_type => tax_type_rec.t_type ,
2181 p_account_name => jai_constants.liability
2182 );
2183 --Added by Wenqiong for Bug 14253668 begin
2184 ELSIF nvl(lv_trx_type,'#') = jai_constants.ar_invoice_type_cm THEN
2185 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
2186 p_regime_id => ln_regime_id ,
2187 p_organization_type => jai_constants.service_tax_orgn_type ,
2188 p_organization_id => v_organization_id ,
2189 p_location_id => v_location_id ,
2190 p_tax_type => tax_type_rec.t_type ,
2191 p_account_name => jai_constants.recovery
2192 );
2193
2194
2195 END IF;
2196 --Added by Wenqiong for Bug 14253668 end
2197 ELSE
2198 IF nvl(lv_trx_type,'#') <> jai_constants.ar_invoice_type_cm THEN --Added by Wenqiong for Bug 14253668
2199 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
2200 p_regime_id => ln_regime_id ,
2201 p_organization_type => jai_constants.service_tax_orgn_type ,
2202 p_organization_id => v_organization_id ,
2203 p_location_id => v_location_id ,
2204 p_tax_type => tax_type_rec.t_type ,
2205 p_account_name => jai_constants.liability_interim
2206 );
2207 --Added by Wenqiong for Bug 14253668 begin
2208 ELSIF nvl(lv_trx_type,'#') = jai_constants.ar_invoice_type_cm THEN
2209 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
2210 p_regime_id => ln_regime_id ,
2211 p_organization_type => jai_constants.service_tax_orgn_type ,
2212 p_organization_id => v_organization_id ,
2213 p_location_id => v_location_id ,
2214 p_tax_type => tax_type_rec.t_type ,
2215 p_account_name => jai_constants.recovery_interim
2216 );
2217 END IF;
2218 --Added by Wenqiong for Bug 14253668 end
2219 END IF;
2220 /*ER 11821537 - End*/
2221
2222 IF v_ccid IS NULL THEN
2223 /*
2224 || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
2225 || This is an error condition and the current processing has to be stopped
2226 */
2227 /* raise_application_error (-20130,'Invalid Code combination, please check the Service Tax - Tax Accounting Setup'); */
2228 pv_return_code := jai_constants.expected_error ; pv_return_message := 'Invalid Code combination,please check the Service Tax - Tax Accounting Setup' ; return ;
2229 /*
2230 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
2231 EXCEPTION_CODE => NULL ,
2232 EXCEPTION_TEXT => 'Invalid Code combination, please check the Service Tax - Tax Type Accounting Setup'
2233 );
2234 */
2235
2236 END IF;
2237
2238
2239 /*
2240 || Start of bug 4212816
2241 || Code modified by aiyer for the VAT Enhancement
2242 || Get the Tax accounting information from the vat regime setup when the taxes are as mentioned below taxes
2243 */
2244 ELSIF UPPER(nvl(ln_regime_code,'####')) = jai_constants.vat_regime THEN
2245
2246 /*********************************************************************************************************
2247 || Validate whether the item attached is vatable or not
2248 *********************************************************************************************************/
2249 if pr_new.inventory_item_id is not null then /*Bug 5671400 bduvarag*/
2250 jai_inv_items_pkg.jai_get_attrib (
2251 p_regime_code => ln_regime_code ,
2252 p_organization_id => v_organization_id ,
2253 p_inventory_item_id => pr_new.inventory_item_id ,
2254 p_attribute_code => jai_constants.rgm_attr_item_applicable ,
2255 p_attribute_value => lv_attr_value ,
2256 p_process_flag => lv_error_flag ,
2257 p_process_msg => lv_error_message
2258 ) ;
2259
2260 IF lv_error_flag <> jai_constants.successful THEN
2261 /*
2262 || Encountered an error from the call to jai_ar_rgm_processing_pkg.get_regime_info
2263 || Stop processing and thorw an error
2264 */
2265 /* raise_application_error (-20130,lv_error_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_error_message ; return ;
2266 /*
2267 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
2268 EXCEPTION_CODE => NULL ,
2269 EXCEPTION_TEXT => lv_error_message
2270 );
2271 */
2272
2273 END IF;
2274 END IF; /*Bug 5671400 bduvarag*/
2275 /*********************************************************************************************************
2276 || Raise an error if item is not vatable
2277 *********************************************************************************************************/
2278 IF pr_new.inventory_item_id is not null
2279 and nvl(lv_attr_value,'N') = 'N' THEN /*Bug 5671400 bduvarag */
2280
2281 /*
2282 || Item is not vatable . Stop processing and throw an error
2283 */
2284 /* raise_application_error (-20130,'ITEM not vatable'); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'ITEM not vatable' ; return ;
2285 /* DO not delete this code, enable this code while doing the messageing project
2286 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
2287 EXCEPTION_CODE => NULL ,
2288 EXCEPTION_TEXT => 'Cannot attach VAT type of taxes to non vatable items.'
2289 );
2290 */
2291
2292
2293 END IF;
2294
2295 /*################################################################################################################
2296 || Get Tax Account Info from the Regime Organization/Regime Registration setup
2297 ################################################################################################################*/
2298
2299 /*
2300 || Get the code combination id from the Organization/Regime Registration setup
2301 || by calling the function jai_cmn_rgm_recording_pkg.get_account
2302 */
2303
2304 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
2305 p_regime_id => ln_regime_id ,
2306 p_organization_type => jai_constants.orgn_type_io ,
2307 p_organization_id => v_organization_id ,
2308 p_location_id => v_location_id ,
2309 p_tax_type => tax_type_rec.t_type ,
2310 p_account_name => jai_constants.liability_interim
2311 );
2312 IF v_ccid IS NULL THEN
2313 /*
2314 || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
2315 || This is an error condition and the current processing has to be stopped
2316 */
2317 /* raise_application_error (-20130,'Invalid Code combination ,please check the VAT Tax - Tax Accounting Setup'); */
2318 pv_return_code := jai_constants.expected_error ; pv_return_message := 'Invalid Code combination ,please check the VAT Tax - Tax Accounting Setup' ; return ;
2319 /*
2320 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
2321 EXCEPTION_CODE => NULL ,
2322 EXCEPTION_TEXT => 'Invalid Code combination ,please check the VAT Tax - Tax Type Accounting Setup'
2323 );
2324 */
2325 END IF;
2326
2327 /*
2328 || End of bug 4212816
2329 */
2330
2331
2332 /* Added by CSahoo for the TCS enhancements Bug# 5631784 */
2333 ELSIF UPPER(nvl(ln_regime_code,'####')) = jai_constants.tcs_regime THEN -- Start of A1
2334
2335 /*********************************************************************************************************
2336 || Get the code combination id from the Organization/Regime Registration setup
2337 || by calling the function jai_rgm_trx_recording_pkg.get_account
2338 *********************************************************************************************************/
2339
2340
2341 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
2342 p_regime_id => ln_regime_id ,
2343 p_organization_type => jai_constants.orgn_type_io ,
2344 p_organization_id => v_organization_id ,
2345 p_location_id => v_location_id ,
2346 p_tax_type => tax_type_rec.t_type ,
2347 p_account_name => jai_constants.liability_interim
2348 );
2349 IF v_ccid IS NULL THEN
2350 /**********************************************************************************************************
2351 || Code Combination id has been returned as null from the function jai_rgm_trx_recording_pkg.get_account
2352 || This is an error condition and the current processing has to be stopped
2353 **********************************************************************************************************/
2354 raise_application_error (-20130,'Invalid Code combination ,please check the TCS Tax - Tax Accounting Setup');
2355
2356 END IF;
2357
2358 /*End of bug 5631784 */
2359
2360 ELSE -- ELSE of A1
2361 /*
2362 || As tax type is not SERVICE hence
2363 || get code combination from tax definition setup
2364 */
2365 OPEN TAX_CCID_CUR(TAX_TYPE_REC.taxid);
2366 FETCH TAX_CCID_CUR INTO v_ccid;
2367 CLOSE TAX_CCID_CUR;
2368 END IF;
2369 END IF ;---- Added by zhiwei for GST get ccid by regime setup BUG#10233402 20101027
2370 IF NVL(v_counter,0) = 0 THEN
2371 INSERT INTO JAI_AR_TRX_INS_LINES_T (
2372 -- paddr,
2373 -- 6842749
2374 extended_amount,
2375 customer_trx_line_id,
2376 customer_trx_id,
2377 set_of_books_id,
2378 link_to_cust_trx_line_id,
2379 line_type,
2380 uom_code,
2381 vat_tax_id,
2382 acctd_amount,
2383 amount,
2384 CODE_COMBINATION_ID,
2385 cust_trx_line_sales_rep_id,
2386 insert_update_flag,
2387 last_update_date,
2388 last_updated_by,
2389 creation_date,
2390 created_by,
2391 last_update_login,
2392 tax_rate, -- Tax_rate column added by Jagdish/Subbu 10-Jun-01
2393 Source, -- Source Column added by Sriram / Pavan
2394 org_id , -- org_id column added by sriram - bug # 2779967
2395 line_number ) -- added by sriram - bug # 2769439
2396 VALUES(
2397 -- v_paddr,
2398 -- 6842749
2399 round(TAX_TYPE_REC.tax_amt,ln_inv_curr_precision), /* rounding based on inv currency precision - bug# 5364120*/
2400 TAX_TYPE_REC.line_id,
2401 v_customer_trx_id,
2402 v_books_id,
2403 pr_new.customer_trx_line_id,
2404 v_line_type,
2405 TAX_TYPE_REC.uom,
2406 ln_tax_rate_id, --v_vat_tax, /* Modified by Ramananda for bug#4468353 due to ebtax uptake by AR */
2407 v_converted_rate * TAX_TYPE_REC.tax_amt,
2408 round(TAX_TYPE_REC.tax_amt,ln_inv_curr_precision), /* rounding based on inv currency precision - bug# 5364120*/
2409 v_ccid,
2410 v_salesrep_id,
2411 'U',
2412 pr_new.last_update_date,
2413 pr_new.last_updated_by,
2414 pr_new.creation_date,
2415 pr_new.created_by,
2416 pr_new.last_update_login,
2417 TAX_TYPE_REC.tax_rate, --- Tax_rate column added by Jagdish/Subbu 10-Jun-01
2418 v_Created_from, -- v_created_from column added by Sriram - 09-MAY-2002
2419 v_org_id , -- added by sriram bug # 2779967
2420 tax_type_rec.tax_line_no) ; -- added by sriram - bug # 2769439
2421 ELSE
2422 UPDATE JAI_AR_TRX_INS_LINES_T
2423 SET extended_amount = TAX_TYPE_REC.tax_amt,
2424 set_of_books_id = v_books_id,
2425 line_type = v_line_type,
2426 uom_code = TAX_TYPE_REC.uom,
2427 acctd_amount = v_converted_rate * TAX_TYPE_REC.tax_amt,
2428 amount = TAX_TYPE_REC.tax_amt,
2429 insert_update_flag = 'U',
2430 tax_rate=TAX_TYPE_REC.tax_rate -- Tax_rate column added by Jagdish/Subbu 10-Jun-01
2431 WHERE customer_trx_id = v_customer_trx_id
2432 AND customer_trx_line_id = TAX_TYPE_REC.line_id;
2433 END IF;
2434 v_tax_exists := 1; --added by mmurtuza for bug 13042890
2435 END IF;
2436 END LOOP;
2437 -- the following if condition added by sriram - 09-MAY-2002
2438 -- this is added because the AR Tax and Freight Defaultation should be called only
2439 -- if it is a manual invoice.
2440 IF v_created_from = 'ARXTWMAI' AND v_tax_exists = 1 THEN --added condition of v_tax_exists by mmurtuza for bug 13042890
2441 result := fnd_request.set_mode(TRUE);
2442 req_id := fnd_request.submit_request('JA', 'JAILINEGL', 'AR Tax and Freight Defaultation', SYSDATE, FALSE,
2443 v_customer_trx_id, pr_old.customer_trx_line_id);
2444 /*Bug 8371741 - Start*/
2445 BEGIN
2446 SELECT max(jattl.tax_line_no) INTO l_tcs_sur_line_num
2447 FROM JAI_AR_TRX_TAX_LINES jattl, jai_cmn_taxes_all jcta
2448 WHERE jattl.link_to_cust_trx_line_id = pr_new.customer_trx_line_id
2449 AND jattl.tax_id = jcta.tax_id
2450 AND jcta.tax_type = jai_constants.tax_type_tcs_surcharge
2451 GROUP BY jcta.tax_type;
2452 EXCEPTION
2453 WHEN NO_DATA_FOUND THEN
2454 l_tcs_sur_line_num := 0;
2455 END;
2456
2457 BEGIN
2458 SELECT count(jattl.tax_line_no) INTO l_tot_tax_lines
2459 FROM JAI_AR_TRX_TAX_LINES jattl
2460 WHERE jattl.link_to_cust_trx_line_id = pr_new.customer_trx_line_id;
2461 EXCEPTION
2462 WHEN NO_DATA_FOUND THEN
2463 RETURN;
2464 END;
2465
2466 if (pr_new.tax_category_id is NULL and l_tcs_sur_line_num = 0) then
2467 if (c_conversion_date is NULL) then
2468 SELECT trx_date INTO c_conversion_date
2469 FROM ra_customer_trx_all
2470 WHERE customer_trx_id = v_customer_trx_id;
2471 end if;
2472
2473 OPEN transaction_type_cur;
2474 FETCH transaction_type_cur INTO v_trans_type;
2475 CLOSE transaction_type_cur;
2476
2477 IF NVL(v_trans_type,'N') Not in ( 'INV','CM') THEN -- 'CM' added ssumaith - bug# 3957682
2478 Return;
2479 END IF;
2480
2481 v_line_amount := nvl(pr_new.quantity * pr_new.unit_selling_price,0);
2482 v_quantity := pr_new.quantity;
2483
2484 v_line_tax_amount := nvl(v_line_amount,0);
2485
2486 BEGIN
2487 SELECT max(jattl.tax_line_no) INTO l_tcs_line_num
2488 FROM JAI_AR_TRX_TAX_LINES jattl, jai_cmn_taxes_all jcta
2489 WHERE jattl.link_to_cust_trx_line_id = pr_new.customer_trx_line_id
2490 AND jattl.tax_id = jcta.tax_id
2491 AND jcta.tax_type = jai_constants.tax_type_tcs
2492 GROUP BY jcta.tax_type;
2493 EXCEPTION
2494 WHEN NO_DATA_FOUND THEN
2495 RETURN;
2496 END;
2497
2498 IF (l_tcs_line_num > 0) then
2499 /** TCS type of tax(s) are present */
2500 open gc_get_regime_id ( cp_regime_code => jai_constants.tcs_regime);
2501 fetch gc_get_regime_id into ln_tcs_regime_id;
2502 close gc_get_regime_id;
2503
2504 open bind_cur;
2505 fetch bind_cur into l_org_id, l_bill_to_customer_id, l_bill_to_site_use_id, ln_trx_date;
2506 close bind_cur;
2507
2508 SELECT organization_id into ln_organization_id
2509 FROM JAI_AR_TRXS
2510 WHERE customer_trx_id = pr_new.customer_trx_id;
2511
2512 jai_rgm_thhold_proc_pkg.get_threshold_slab_id
2513 ( p_regime_id => ln_tcs_regime_id
2514 , p_organization_id => ln_organization_id
2515 , p_party_type => jai_constants.party_type_customer
2516 , p_party_id => l_bill_to_customer_id
2517 , p_org_id => l_org_id
2518 , p_source_trx_date => ln_trx_date /* ssumaith - bug# 6109941*/
2519 , p_threshold_slab_id => ln_threshold_slab_id
2520 , p_called_from => jai_constants.tcs_event_surcharge --Added by mmurtuza for bug#16052256
2521 , p_process_flag => lv_process_flag
2522 , p_process_message => lv_process_message
2523 );
2524
2525 if lv_process_flag <> jai_constants.successful then
2526 app_exception.raise_exception
2527 (exception_type => 'APP'
2528 ,exception_code => -20275
2529 ,exception_text => lv_process_message
2530 );
2531 end if;
2532 if ln_threshold_slab_id is not null then
2533 /**
2534 Threshold is high and slab is available. Hence get tax_category defined for the salb to default additional taxes
2535 */
2536 jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id
2537 (
2538 p_threshold_slab_id => ln_threshold_slab_id
2539 , p_org_id => l_org_id
2540 , p_threshold_tax_cat_id => ln_threshold_tax_cat_id
2541 , p_process_flag => lv_process_flag
2542 , p_process_message => lv_process_message
2543 );
2544 if lv_process_flag <> jai_constants.successful then
2545 app_exception.raise_exception
2546 (exception_type => 'APP'
2547 ,exception_code => -20275
2548 ,exception_text => lv_process_message
2549 );
2550 end if;
2551 end if; /** ln_threshold_slab_id is not null */
2552
2553 select max(jattl.tax_line_no) into l_max_tax_line_no
2554 from JAI_AR_TRX_TAX_LINES jattl
2555 where jattl.link_to_cust_trx_line_id = pr_new.customer_trx_line_id;
2556
2557 jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes
2558 (
2559 transaction_name => 'AR_LINES',
2560 p_tax_category_id => -1,
2561 p_header_id => pr_new.customer_trx_id,
2562 p_line_id => pr_new.customer_trx_line_id,
2563 p_assessable_value => pr_new.assessable_value * pr_new.quantity,
2564 p_tax_amount => ln_tax_amount,
2565 p_inventory_item_id => pr_new.inventory_item_id,
2566 p_line_quantity => pr_new.quantity,
2567 p_uom_code => pr_new.unit_code,
2568 p_vendor_id => NULL,
2569 p_currency => NULL,
2570 p_currency_conv_factor => NVL(v_converted_rate, 1),
2571 p_creation_date => sysdate,
2572 p_created_by => FND_GLOBAL.user_id,
2573 p_last_update_date => sysdate,
2574 p_last_updated_by => FND_GLOBAL.user_id,
2575 p_last_update_login => FND_GLOBAL.login_id,
2576 p_operation_flag => NULL,
2577 p_vat_assessable_value => pr_new.vat_assessable_value,
2578 p_thhold_cat_base_tax_typ => 'TCS' ,
2579 p_threshold_tax_cat_id => ln_threshold_tax_cat_id,
2580 p_source_trx_type => null,
2581 p_source_table_name => null,
2582 p_action => 'DEFAULT_TAXES',
2583 p_max_tax_line => l_max_tax_line_no ,
2584 p_max_rgm_tax_line => l_tcs_line_num
2585 );
2586 end if; /*IF (l_tcs_line_num > 0) then*/
2587
2588 end if; /*if (pr_new.tax_category_id is NULL) then*/
2589 /*Bug 8371741 - End*/
2590
2591 END IF;
2592 /* Added an exception block by Ramananda for bug#4570303 */
2593 EXCEPTION
2594 WHEN OTHERS THEN
2595 Pv_return_code := jai_constants.unexpected_error;
2596 Pv_return_message := 'Encountered an error in JAI_JAR_TL_TRIGGER_PKG.ARU_T1 ' || substr(sqlerrm,1,1900);
2597
2598 END ARU_T1 ;
2599
2600 END JAI_JAR_TL_TRIGGER_PKG ;
[Home] [Help]