[Home] [Help]
PACKAGE BODY: APPS.JAI_AR_TRX_LINES_PKG
Source
1 PACKAGE BODY JAI_AR_TRX_LINES_PKG AS
2 /* $Header: jai_ar_trx_lines.plb 120.0.12020000.2 2013/03/19 00:29:08 vkaranam noship $ */
3 /*------------------------------------------------------------------------------------------------------------
4 CHANGE HISTORY
5 ------------------------------------------------------------------------------------------------------------
6 Sl.No. Date Developer BugNo Version Remarks
7 ------------------------------------------------------------------------------------------------------------
8 1. 13-June-2012 qinglei 14040855 115.1 Created the initial version
9
10 --------------------------------------------------------------------------------------------------------------*/
11
12 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
13 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
14 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
15 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
16 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
17 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
18 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
19 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'JAI.PLSQL.JAI_AR_TRX_LINES_PKG.';
20 G_PACKAGE_NAME CONSTANT VARCHAR2(100) := 'JAI_AR_TRX_LINES_PKG';
21
22 PROCEDURE POPULATE_JAI_AR_LINES(pr_new t_rec%TYPE,
23 pv_action VARCHAR2,
24 pv_return_code OUT NOCOPY VARCHAR2,
25 pv_return_message OUT NOCOPY VARCHAR2) IS
26 lv_api_name CONSTANT VARCHAR2(100) := 'POPULATE_JAI_AR_LINES';
27 lv_debug_info VARCHAR2(4000);
28 t_jai_line_rec_old JAI_AR_TRX_LINES%ROWTYPE;
29 t_jai_line_rec_new JAI_AR_TRX_LINES%ROWTYPE;
30 CURSOR c_jai_ar_trx_lines(pn_customer_trx_line_id NUMBER) IS
31 SELECT *
32 FROM jai_ar_trx_lines
33 WHERE customer_trx_line_id = pn_customer_trx_line_id;
34 lv_action VARCHAR2(20);
35 lv_return_message VARCHAR2(2000);
36 lv_return_code VARCHAR2(100);
37 le_error EXCEPTION;
38
39 v_trans_type Varchar2(30);
40 v_header_id Number;
41 v_inventory_item_id Number;
42 v_line_amount number;
43 v_customer_trx_line_id Number;
44 v_line_type Varchar2(10);
45 v_prev_customer_trx_line_id Number;
46 v_link_to_cust_id Number;
47
48 v_gl_date Date;
49 v_tax_category_id Number;
50 v_price_list Number;
51 c_from_currency_code Varchar2(15);
52 c_conversion_type Varchar2(30);
53 c_conversion_date Date;
54 c_conversion_rate Number := 0;
55 v_converted_rate Number := 1;
56 v_books_id Number;
57
58 v_last_update_date Date;
59 v_last_updated_by Number;
60 v_creation_date Date;
61 v_created_by Number;
62 v_last_update_login Number;
63 v_operating_id number;
64 v_created_from Varchar2(30);
65 v_organization_id Number;
66
67 v_gl_set_of_bks_id gl_sets_of_books.set_of_books_id%type;
68 v_currency_code gl_sets_of_books.currency_code%type;
69
70 lv_service_type_code VARCHAR2(30);
71
72 CURSOR transaction_type_cur IS
73 SELECT a.type
74 FROM RA_CUST_TRX_TYPES_ALL a, RA_CUSTOMER_TRX_ALL b
75 WHERE a.cust_trx_type_id = b.cust_trx_type_id
76 AND b.customer_trx_id = v_header_id
77 AND NVL(a.org_id, 0) = NVL(pr_new.org_id, 0);
78
79 CURSOR gl_date_cur IS
80 SELECT DISTINCT gl_date
81 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
82 WHERE CUSTOMER_TRX_LINE_ID = v_prev_customer_trx_line_id;
83
84 CURSOR localization_line_info IS
85 SELECT assessable_value, tax_category_id, service_type_code
86 FROM JAI_AR_TRX_LINES
87 WHERE customer_trx_line_id = v_prev_customer_trx_line_id;
88
89 CURSOR localization_tax_info IS
90 SELECT a.tax_id,
91 a.tax_line_no lno,
92 a.precedence_1 p_1,
93 a.precedence_2 p_2,
94 a.precedence_3 p_3,
95 a.precedence_4 p_4,
96 a.precedence_5 p_5,
97 a.precedence_6 p_6,
98 a.precedence_7 p_7,
99 a.precedence_8 p_8,
100 a.precedence_9 p_9,
101 a.precedence_10 p_10,
102 a.tax_rate,
103 a.tax_amount,
104 a.uom uom_code,
105 a.qty_rate,
106 decode(upper(b.tax_type),
107 'EXCISE',
108 1,
109 'ADDL. EXCISE',
110 1,
111 'OTHER EXCISE',
112 1,
113 'CVD',
114 1,
115 'TDS',
116 2,
117 0) tax_type_val,
118 b.tax_type
119 FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
120 WHERE a.customer_trx_line_id = v_prev_customer_trx_line_id
121 AND a.tax_id = b.tax_id;
122
123 CURSOR header_info_cur IS
124 SELECT created_from,
125 set_of_books_id,
126 invoice_currency_code,
127 exchange_rate_type,
128 nvl(exchange_date, trx_date),
129 exchange_rate
130 FROM RA_CUSTOMER_TRX_ALL
131 WHERE customer_trx_id = v_header_id;
132
133 CURSOR localization_header_info_cur IS
134 SELECT organization_id
135 FROM JAI_AR_TRXS
136 WHERE customer_trx_id = v_header_id;
137 BEGIN
138 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
139 FND_LOG.STRING(G_LEVEL_PROCEDURE,
140 G_MODULE_NAME || lv_api_name,
141 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
142 END IF;
143
144 pv_return_code := jai_constants.successful;
145 v_header_id := pr_new.customer_trx_id;
146 v_inventory_item_id := pr_new.inventory_item_id;
147 v_line_amount := nvl(NVL(pr_new.quantity_credited,
148 pr_new.quantity_invoiced) *
149 pr_new.unit_selling_price,
150 nvl(pr_new.extended_amount, 0));
151 v_customer_trx_line_id := pr_new.customer_trx_line_id;
152 v_line_type := pr_new.line_type;
153 v_prev_customer_trx_line_id := pr_new.previous_customer_trx_line_id;
154 v_link_to_cust_id := pr_new.link_to_cust_trx_line_id;
155 v_last_update_date := pr_new.last_update_date;
156 v_last_updated_by := pr_new.last_updated_by;
157 v_creation_date := pr_new.creation_date;
158 v_created_by := pr_new.created_by;
159 v_last_update_login := pr_new.last_update_login;
160 v_operating_id := pr_new.ORG_ID;
161 v_gl_set_of_bks_id := pr_new.set_of_books_id;
162
163 OPEN transaction_type_cur;
164 FETCH transaction_type_cur
165 INTO v_trans_type;
166 CLOSE transaction_type_cur;
167
168 IF NVL(v_trans_type, 'N') NOT IN ('DM', 'CM') THEN
169 Return;
170 END IF;
171
172 OPEN HEADER_INFO_CUR;
173 FETCH HEADER_INFO_CUR
174 INTO v_created_from,
175 v_books_id,
176 c_from_currency_code,
177 c_conversion_type,
178 c_conversion_date,
179 c_conversion_rate;
180 CLOSE HEADER_INFO_CUR;
181 IF v_created_from NOT IN ('ARXTWCMI', 'ARXTWMAI') THEN
182 Return;
183 END IF;
184
185 if v_created_from = 'ARXTWMAI' and v_trans_type = 'CM' then
186 return;
187 end if;
188
189 IF v_books_id IS NULL THEN
190 OPEN localization_header_info_cur;
191 FETCH localization_header_info_cur
192 INTO v_organization_id;
193 CLOSE localization_header_info_cur;
194
195 END IF;
196
197 v_converted_rate := jai_cmn_utils_pkg.currency_conversion(v_books_id,
198 c_from_currency_code,
199 c_conversion_date,
200 c_conversion_type,
201 c_conversion_rate);
202
203 IF v_line_type = 'LINE' THEN
204 Open Gl_Date_Cur;
205 Fetch Gl_Date_Cur
206 Into v_gl_date;
207 Close Gl_Date_Cur;
208
209 Open localization_line_info;
210 Fetch localization_line_info
211 into v_tax_category_id, v_price_list, lv_service_type_code;
212 Close localization_line_info;
213
214 INSERT INTO JAI_AR_TRX_LINES
215 (customer_trx_line_id,
216 line_number,
217 customer_trx_id,
218 description,
219 inventory_item_id,
220 unit_code,
221 quantity,
222 tax_category_id,
223 auto_invoice_flag,
224 unit_selling_price,
225 line_amount,
226 gl_date,
227 tax_amount,
228 total_amount,
229 assessable_value,
230 creation_date,
231 created_by,
232 last_update_date,
233 last_updated_by,
234 last_update_login,
235 service_type_code)
236 VALUES
237 (v_customer_trx_line_id,
238 pr_new.line_number,
239 v_header_id,
240 pr_new.description,
241 v_inventory_item_id,
242 pr_new.uom_code,
243 NVL(NVL(pr_new.quantity_credited, pr_new.quantity_invoiced), 0),
244 v_tax_category_id,
245 'N',
246 pr_new.unit_selling_price,
247 v_line_amount,
248 v_gl_date,
249 0,
250 v_line_amount,
251 v_price_list,
252 v_creation_date,
253 v_created_by,
254 v_last_update_date,
255 v_last_updated_by,
256 v_last_update_login,
257 lv_service_type_code);
258
259 OPEN c_jai_ar_trx_lines(v_customer_trx_line_id);
260 FETCH c_jai_ar_trx_lines
261 INTO t_jai_line_rec_new;
262 CLOSE c_jai_ar_trx_lines;
263
264 lv_action := JAI_CONSTANTS.INSERTING;
265 JAI_AR_TAX_LINES_PKG.POPULATE_TAX_LINES_WRAPPER(pr_old => t_jai_line_rec_old,
266 pr_new => t_jai_line_rec_new,
267 pv_action => lv_action,
268 pv_return_code => lv_return_code,
269 pv_return_message => lv_return_message);
270 IF PV_RETURN_CODE <> JAI_CONSTANTS.successful THEN
271 RAISE le_error;
272 END IF;
273
274 Update JAI_AR_TRXS
275 Set line_amount = nvl(line_amount, 0) + nvl(v_line_amount, 0)
276 Where Customer_Trx_Id = v_header_id;
277
278 ELSIF v_line_type in ('FREIGHT', 'TAX') THEN
279 FOR rec in localization_tax_info LOOP
280 INSERT INTO JAI_AR_TRX_TAX_LINES
281 (customer_trx_line_id,
282 link_to_cust_trx_line_id,
283 tax_line_no,
284 precedence_1,
285 precedence_2,
286 precedence_3,
287 precedence_4,
288 precedence_5,
289 precedence_6,
290 precedence_7,
291 precedence_8,
292 precedence_9,
293 precedence_10,
294 tax_id,
295 tax_rate,
296 qty_rate,
297 uom,
298 tax_amount,
299 base_tax_amount,
300 func_tax_amount,
301 creation_date,
302 created_by,
303 last_update_date,
304 last_updated_by,
305 last_update_login)
306 VALUES
307 (v_customer_trx_line_id,
308 v_link_to_cust_id,
309 rec.lno,
310 rec.p_1,
311 rec.p_2,
312 rec.p_3,
313 rec.p_4,
314 rec.p_5,
315 rec.p_6,
316 rec.p_7,
317 rec.p_8,
318 rec.p_9,
319 rec.p_10,
320 rec.tax_id,
321 rec.tax_rate,
322 rec.qty_rate,
323 rec.uom_code,
324 pr_new.extended_amount,
325 pr_new.extended_amount,
326 pr_new.extended_amount * v_converted_rate,
327 v_creation_date,
328 v_created_by,
329 v_last_update_date,
330 v_last_updated_by,
331 v_last_update_login);
332
333 IF rec.tax_type in ('Excise', 'Addl. Excise', 'Other Excise') THEN
334 Update JAI_AR_TRXS
335 Set total_amount = nvl(total_amount, 0) +
336 nvl(pr_new.extended_amount, 0),
337 tax_amount = nvl(tax_amount, 0) +
338 nvl(pr_new.extended_amount, 0)
339 Where Customer_Trx_Id = v_header_id;
340 END IF;
341
342 OPEN c_jai_ar_trx_lines(v_link_to_cust_id);
343 FETCH c_jai_ar_trx_lines
344 INTO t_jai_line_rec_old;
345 CLOSE c_jai_ar_trx_lines;
346
347 Update JAI_AR_TRX_LINES
348 Set total_amount = nvl(total_amount, 0) +
349 nvl(pr_new.extended_amount, 0),
350 tax_amount = nvl(tax_amount, 0) +
351 nvl(pr_new.extended_amount, 0)
352 Where Customer_Trx_Id = v_header_id
353 and Customer_Trx_Line_Id = v_link_to_cust_id;
354
355 OPEN c_jai_ar_trx_lines(v_link_to_cust_id);
356 FETCH c_jai_ar_trx_lines
357 INTO t_jai_line_rec_new;
358 CLOSE c_jai_ar_trx_lines;
359 lv_action := JAI_CONSTANTS.UPDATING;
360 IF (((t_jai_line_rec_new.AUTO_INVOICE_FLAG <> 'Y' AND
361 t_jai_line_rec_old.AUTO_INVOICE_FLAG <> 'Y') AND
362 (t_jai_line_rec_new.Excise_Invoice_No IS NULL) AND
363 (t_jai_line_rec_new.payment_Register IS NULL) AND
364 (t_jai_line_rec_new.Excise_Invoice_Date IS NULL)) OR
365 (t_jai_line_rec_new.Customer_Trx_Id <>
366 t_jai_line_rec_old.Customer_Trx_Id)) THEN
367 JAI_AR_TAX_LINES_PKG.POPULATE_TAX_LINES_WRAPPER(pr_old => t_jai_line_rec_old,
368 pr_new => t_jai_line_rec_new,
369 pv_action => lv_action,
370 pv_return_code => lv_return_code,
371 pv_return_message => lv_return_message);
372
373 IF lv_return_code <> jai_constants.successful then
374 RAISE le_error;
375 END IF;
376 END IF;
377 END LOOP;
378 END IF;
379 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
380 FND_LOG.STRING(G_LEVEL_PROCEDURE,
381 G_MODULE_NAME || lv_api_name,
382 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
383 END IF;
384 EXCEPTION
385 WHEN OTHERS THEN
386 Pv_return_code := jai_constants.unexpected_error;
387 Pv_return_message := 'Encountered an error in ' || G_PACKAGE_NAME || '.' ||
388 lv_api_name || ' ' || substr(sqlerrm, 1, 1900);
389 IF G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL THEN
390 FND_LOG.STRING(G_LEVEL_UNEXPECTED,
391 G_MODULE_NAME || lv_api_name,
392 SUBSTR('Encountered an error in ' || G_PACKAGE_NAME || '.' ||
393 lv_api_name || ' ' || sqlerrm,
394 1,
395 4000));
396 END IF;
397 END POPULATE_JAI_AR_LINES;
398
399 PROCEDURE DEFAULT_TAX(pr_old t_jai_rec%TYPE,
400 pr_new t_rec%TYPE,
401 pv_action VARCHAR2,
402 pv_return_code OUT NOCOPY VARCHAR2,
403 pv_return_message OUT NOCOPY VARCHAR2) IS
404 lv_api_name CONSTANT VARCHAR2(100) := 'DEFAULT_TAX';
405 lv_debug_info VARCHAR2(4000);
406 t_jai_line_rec_old JAI_AR_TRX_LINES%ROWTYPE;
407 t_jai_line_rec_new JAI_AR_TRX_LINES%ROWTYPE;
408 CURSOR c_jai_ar_trx_lines(pn_customer_trx_line_id NUMBER) IS
409 SELECT *
410 FROM jai_ar_trx_lines
411 WHERE customer_trx_line_id = pn_customer_trx_line_id;
412 lv_action VARCHAR2(20);
413 lv_return_message VARCHAR2(2000);
414 lv_return_code VARCHAR2(100);
415 le_error EXCEPTION;
416 v_customer_id Number;
417 v_customer_id_bill Number;
418 v_customer_id_ship Number;
419 v_address_id Number;
420 v_org_id Number := 0;
421 v_header_id Number;
422 v_bill_to_site_use_id Number := 0;
423 v_ship_to_site_use_id Number := 0;
424 v_site_use_id Number := 0;
425 v_inventory_item_id Number;
426 v_tax_category_list varchar2(30);
427 v_tax_category_id Number;
428 v_item_class varchar2(30);
429 v_line_amount number := 0;
430 v_line_no Number;
431 v_tax_id Number;
432 v_tax_rate Number;
433 v_tax_amount Number;
434 v_line_tax_amount Number := 0;
435 v_uom_code varchar2(3);
436 v_tax_tot Number := 0;
437 v_tot_amt Number := 0;
438 v_excise Number := 0;
439 v_additional Number := 0;
440 v_other Number := 0;
441 v_hdr_tax_amount Number := 0;
442 v_hdr_total_amount Number := 0;
443 v_price_list Number := 0;
444 v_price_list_val Number := 0;
445 v_price_list_uom_code Varchar2(3);
446 v_conversion_rate Number := 0;
447 v_organization_id Number := -1;
448 v_location_id NUMBER;
449 v_row_id Rowid;
450 v_customer_trx_line_id Number;
451 v_once_completed_flag Varchar2(1);
452 v_old_line_amount Number := 0;
453 v_old_tax_tot Number := 0;
454 v_created_from Varchar2(30);
455 c_from_currency_code Varchar2(15);
456 c_conversion_type Varchar2(30);
457 c_conversion_date Date;
458 c_conversion_rate Number := 0;
459 v_converted_rate Number := 1;
460 v_books_id Number;
461 v_gl_date Date;
462 ln_tcs_exists number;
463 ln_tcs_regime_id JAI_RGM_DEFINITIONS.regime_id%type;
464 ln_threshold_slab_id JAI_AP_TDS_THHOLD_SLABS.threshold_slab_id%type;
465 ln_threshold_tax_cat_id JAI_AP_TDS_THHOLD_TAXES.tax_category_id%type;
466 ld_gl_dist_date date;
467 v_service_type VARCHAR2(30);
468 v_num_check Number;
469
470 cursor c_gl_dist_date(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%type) is
471 select gl_date
472 from ra_cust_trx_line_gl_dist_all
473 where customer_trx_id = cp_customer_trx_id
474 and account_class = jai_constants.account_class_rec
475 and latest_rec_flag = jai_constants.yes;
476
477 CURSOR GC_GET_REGIME_ID(CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE) IS
478 SELECT REGIME_ID
479 FROM JAI_RGM_DEFINITIONS
480 WHERE REGIME_CODE = CP_REGIME_CODE;
481
482 CURSOR GC_CHK_RGM_TAX_EXISTS(CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE,
483 CP_RGM_TAX_TYPE JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE,
484 CP_TAX_CATEGORY_ID JAI_CMN_TAX_CTGS_ALL.TAX_CATEGORY_ID%TYPE) IS
485 SELECT COUNT(1)
486 FROM JAI_CMN_TAX_CTG_LINES CATL,
487 JAI_CMN_TAXES_ALL CODES,
488 JAI_REGIME_TAX_TYPES_V JRTTV
489 WHERE CATL.TAX_CATEGORY_ID = CP_TAX_CATEGORY_ID
490 AND CATL.TAX_ID = CODES.TAX_ID
491 AND CODES.TAX_TYPE = JRTTV.TAX_TYPE
492 AND JRTTV.REGIME_CODE = CP_REGIME_CODE;
493
494 v_last_update_date Date;
495 v_last_updated_by Number;
496 v_creation_date Date;
497 v_created_by Number;
498 v_last_update_login Number;
499 v_trx_date Date;
500 v_total_tax_amount Number := 0;
501 v_line_type varchar2(20);
502 V_DEBUG_VAR VARCHAR2(1996);
503 VAR_REC_CTR NUMBER := 0;
504 VAR_SQLERRM VARCHAR2(240);
505 v_trans_type Varchar2(30);
506 v_quantity number;
507 lv_process_flag VARCHAR2(2);
508 lv_process_message VARCHAR2(1996);
509
510 Cursor bind_cur IS
511
512 SELECT A.org_id,
513 A.ship_to_customer_id,
514 NVL(A.ship_to_site_use_id, 0),
515 A.bill_to_customer_id,
516 NVL(A.bill_to_site_use_id, 0)
517 FROM RA_CUSTOMER_TRX_ALL A
518 WHERE customer_trx_id = v_header_id;
519
520 Cursor header_info_cur IS
521 SELECT set_of_books_id,
522 invoice_currency_code,
523 exchange_rate_type,
524 exchange_date,
525 exchange_rate,
526 trx_date
527 FROM RA_CUSTOMER_TRX_ALL
528 WHERE customer_trx_id = v_header_id;
529
530 Cursor address_cur(p_ship_to_site_use_id IN Number) IS
531 SELECT cust_acct_site_id address_id
532 FROM HZ_CUST_SITE_USES_ALL A
533 WHERE A.site_use_id = NVL(p_ship_to_site_use_id, 0);
534
535 CURSOR excise_cal_cur IS
536 SELECT A.tax_id, A.tax_rate, A.tax_amount tax_amt, b.tax_type t_type
537 FROM JAI_AR_TRX_TAX_LINES A, JAI_CMN_TAXES_ALL B
538 WHERE link_to_cust_trx_line_id = v_customer_trx_line_id
539 AND A.tax_id = B.tax_id
540 ORDER BY 1;
541
542 CURSOR ORG_CUR IS
543 SELECT organization_id, location_id FROM JAI_AR_TRX_APPS_RELS_T;
544
545 CURSOR organization_cur IS
546 SELECT A.organization_id, location_id
547 FROM JAI_AR_TRXS A, RA_CUSTOMER_TRX_ALL B
548 WHERE A.trx_number = B.recurred_from_trx_number
549 AND B.customer_trx_id = v_header_id;
550
551 CURSOR CREATED_FROM_CUR IS
552 SELECT created_from, trx_date
553 FROM ra_customer_trx_all
554 WHERE customer_trx_id = v_header_id;
555
556 ld_trx_date ra_customer_trx_all.trx_Date%type;
557
558 CURSOR ONCE_COMPLETE_FLAG_CUR IS
559 SELECT once_completed_flag
560 FROM JAI_AR_TRXS
561 WHERE customer_trx_id = v_header_id;
562
563 CURSOR ROW_ID_CUR IS
564 SELECT rowid
565 FROM RA_CUSTOMER_TRX_ALL
566 WHERE customer_trx_id = v_header_id;
567
568 CURSOR old_line_amount_cur IS
569 SELECT line_amount
570 FROM JAI_AR_TRX_LINES
571 WHERE CUSTOMER_TRX_ID = pr_old.CUSTOMER_TRX_ID
572 AND CUSTOMER_TRX_LINE_ID = pr_old.CUSTOMER_TRX_LINE_ID;
573
574 CURSOR gl_date_cur IS
575 SELECT DISTINCT gl_date
576 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
577 WHERE CUSTOMER_TRX_LINE_ID = v_customer_trx_line_id;
578
579 Cursor transaction_type_cur IS
580 Select a.type
581 From RA_CUST_TRX_TYPES_ALL a, RA_CUSTOMER_TRX_ALL b
582 Where a.cust_trx_type_id = b.cust_trx_type_id
583 And b.customer_trx_id = v_header_id
584 And NVL(a.org_id, 0) = NVL(pr_new.org_id, 0);
585
586 ln_vat_assessable_value JAI_AR_TRX_LINES.VAT_ASSESSABLE_VALUE%TYPE;
587 lv_appl_src JAI_CMN_ERRORS_T.APPLICATION_SOURCE%type;
588 lv_add_err JAI_CMN_ERRORS_T.additional_error_mesg%type;
589 BEGIN
590 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
591 FND_LOG.STRING(G_LEVEL_PROCEDURE,
592 G_MODULE_NAME || lv_api_name,
593 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
594 END IF;
595
596 pv_return_code := jai_constants.successful;
597 v_header_id := pr_new.customer_trx_id;
598 v_inventory_item_id := pr_new.inventory_item_id;
599 v_item_class := 'N';
600 v_uom_code := pr_new.uom_code;
601 v_customer_trx_line_id := pr_new.customer_trx_line_id;
602 v_last_update_date := pr_new.last_update_date;
603 v_last_updated_by := pr_new.last_updated_by;
604 v_creation_date := pr_new.creation_date;
605 v_created_by := pr_new.created_by;
606 v_last_update_login := pr_new.last_update_login;
607 v_line_type := pr_new.LINE_TYPE;
608
609 OPEN transaction_type_cur;
610 FETCH transaction_type_cur
611 INTO v_trans_type;
612 CLOSE transaction_type_cur;
613
614 IF NVL(v_trans_type, 'N') Not in ('INV', 'CM') THEN
615 Return;
616 END IF;
617
618 IF NVL(v_trans_type, 'N') = 'INV' then
619 v_line_amount := nvl(pr_new.quantity_invoiced *
620 pr_new.unit_selling_price,
621 nvl(pr_new.extended_amount, 0));
622 v_quantity := pr_new.quantity_invoiced;
623 elsif NVL(v_trans_type, 'N') = 'CM' then
624 v_line_amount := nvl(pr_new.quantity_credited *
625 pr_new.unit_selling_price,
626 nvl(pr_new.extended_amount, 0));
627 v_quantity := pr_new.quantity_credited;
628 end if;
629
630 OPEN ONCE_COMPLETE_FLAG_CUR;
631 FETCH ONCE_COMPLETE_FLAG_CUR
632 INTO v_once_completed_flag;
633 CLOSE ONCE_COMPLETE_FLAG_CUR;
634 IF NVL(v_once_completed_flag, 'N') = 'Y' THEN
635 RETURN;
636 END IF;
637
638 OPEN CREATED_FROM_CUR;
639 FETCH CREATED_FROM_CUR
640 INTO v_created_from, ld_trx_date;
641 CLOSE CREATED_FROM_CUR;
642 IF v_created_from in ('ARXREC', 'RAXTRX') THEN
643 RETURN;
644 END IF;
645 IF v_created_from = 'ARXTWMAI' THEN
646 OPEN ORG_CUR;
647 FETCH ORG_CUR
648 INTO v_organization_id, v_location_id;
649 CLOSE ORG_CUR;
650 IF NVL(v_organization_id, 999999) = 999999 THEN
651 OPEN organization_cur;
652 FETCH organization_cur
653 INTO v_organization_id, v_location_id;
654 CLOSE organization_cur;
655 END IF;
656 IF NVL(v_organization_id, 999999) = 999999 THEN
657 RETURN;
658 END IF;
659 OPEN bind_cur;
660 FETCH bind_cur
661 INTO v_org_id,
662 v_customer_id_ship,
663 v_ship_to_site_use_id,
664 v_customer_id_bill,
665 v_bill_to_site_use_id;
666 CLOSE bind_cur;
667 IF pr_new.inventory_item_id <> pr_old.inventory_item_id THEN
668 FOR excise_cal_rec in excise_cal_cur LOOP
669 IF excise_cal_rec.t_type IN ('Excise') THEN
670 v_excise := nvl(v_excise, 0) + nvl(excise_cal_rec.tax_amt, 0);
671 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
672 v_additional := nvl(v_additional, 0) +
673 nvl(excise_cal_rec.tax_amt, 0);
674 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
675 v_other := nvl(v_other, 0) + nvl(excise_cal_rec.tax_amt, 0);
676 END IF;
677 END LOOP;
678 v_old_tax_tot := nvl(v_excise, 0) + nvl(v_other, 0) +
679 nvl(v_additional, 0);
680 OPEN old_line_amount_cur;
681 FETCH old_line_amount_cur
682 INTO v_old_line_amount;
683 CLOSE old_line_amount_cur;
684 UPDATE JAI_AR_TRXS
685 SET line_amount = nvl(line_amount, 0) -
686 nvl(v_old_line_amount, 0),
687 tax_amount = nvl(tax_amount, 0) - nvl(v_old_tax_tot, 0),
688 total_amount = nvl(total_amount, 0) -
689 (nvl(v_old_line_amount, 0) +
690 nvl(v_old_tax_tot, 0))
691 WHERE customer_trx_id = pr_old.CUSTOMER_TRX_ID;
692
693 DELETE JAI_AR_TRX_TAX_LINES
694 WHERE LINK_TO_CUST_TRX_LINE_ID = pr_old.CUSTOMER_TRX_LINE_ID;
695
696 DELETE JAI_AR_TRX_LINES
697 WHERE CUSTOMER_TRX_ID = pr_old.CUSTOMER_TRX_ID
698 AND CUSTOMER_TRX_LINE_ID = pr_old.CUSTOMER_TRX_LINE_ID;
699 END IF;
700
701 OPEN HEADER_INFO_CUR;
702 FETCH HEADER_INFO_CUR
703 INTO v_books_id,
704 c_from_currency_code,
705 c_conversion_type,
706 c_conversion_date,
707 c_conversion_rate,
708 v_trx_date;
709 CLOSE HEADER_INFO_CUR;
710
711 v_site_use_id := v_bill_to_site_use_id;
712 v_customer_id := v_customer_id_bill;
713
714 For check_corr_tax_category_id in 1 .. 2 Loop
715 OPEN address_cur(v_site_use_id);
716 FETCH address_cur
717 INTO v_address_id;
718 CLOSE address_cur;
719
720 IF v_customer_id IS NOT NULL AND v_address_id IS NOT NULL THEN
721 jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes(v_organization_id,
722 v_customer_id,
723 v_site_use_id,
724 v_inventory_item_id,
725 v_header_id,
726 v_customer_trx_line_id,
727 v_tax_category_id);
728 ELSE
729 jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes(v_organization_id,
730 v_inventory_item_id,
731 v_tax_category_id);
732 END IF;
733
734 open gc_chk_rgm_tax_exists(cp_regime_code => jai_constants.tcs_regime,
735 cp_rgm_tax_type => jai_constants.tax_type_tcs,
736 cp_tax_category_id => v_tax_category_id);
737 fetch gc_chk_rgm_tax_exists
738 into ln_tcs_exists;
739 close gc_chk_rgm_tax_exists;
740
741 If ln_tcs_exists > 0 then
742 v_num_check := 1;
743 Exit;
744 End If;
745 v_num_check := 2;
746 v_site_use_id := v_ship_to_site_use_id;
747 v_customer_id := v_customer_id_ship;
748 End Loop;
749
750 If v_num_check = 1 then
751 v_site_use_id := v_bill_to_site_use_id;
752 v_customer_id := v_customer_id_bill;
753 Elsif v_num_check = 2 then
754 v_site_use_id := v_ship_to_site_use_id;
755 v_customer_id := v_customer_id_ship;
756 End If;
757
758 v_price_list := jai_om_utils_pkg.get_oe_assessable_value(p_customer_id => v_customer_id,
759 p_ship_to_site_use_id => v_site_use_id,
760 p_inventory_item_id => v_inventory_item_id,
761 p_uom_code => v_uom_code,
762 p_default_price => pr_new.unit_selling_price,
763 p_ass_value_date => ld_trx_date,
764 p_sob_id => v_books_id,
765 p_curr_conv_code => c_conversion_type,
766 p_conv_rate => c_conversion_rate);
767 v_price_list_val := v_quantity * NVL(v_price_list, 0);
768
769 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value(p_party_id => v_customer_id,
770 p_party_site_id => v_site_use_id, /*v_bill_to_site_use_id , Bug 8371741*/
771 p_inventory_item_id => v_inventory_item_id,
772 p_uom_code => v_uom_code,
773 p_default_price => pr_new.unit_selling_price,
774 p_ass_value_date => ld_trx_date,
775 p_party_type => 'C');
776 ln_vat_assessable_value := nvl(ln_vat_assessable_value, 0) *
777 v_quantity;
778
779 IF v_tax_category_id IS NOT NULL THEN
780
781 IF c_conversion_date is NULL THEN
782 c_conversion_date := v_trx_date;
783 END IF;
784
785 v_converted_rate := jai_cmn_utils_pkg.currency_conversion(v_books_id,
786 c_from_currency_code,
787 c_conversion_date,
788 c_conversion_type,
789 c_conversion_rate);
790 v_line_tax_amount := nvl(v_line_amount, 0);
791
792 If v_num_check = 1 then
793 open gc_get_regime_id(cp_regime_code => jai_constants.tcs_regime);
794 fetch gc_get_regime_id
795 into ln_tcs_regime_id;
796 close gc_get_regime_id;
797
798 jai_rgm_thhold_proc_pkg.get_threshold_slab_id(p_regime_id => ln_tcs_regime_id,
799 p_organization_id => v_organization_id,
800 p_party_type => jai_constants.party_type_customer,
801 p_party_id => v_customer_id,
802 p_org_id => v_org_id,
803 p_source_trx_date => ld_trx_date,
804 p_threshold_slab_id => ln_threshold_slab_id,
805 p_process_flag => lv_process_flag,
806 p_process_message => lv_process_message);
807
808 if lv_process_flag <> jai_constants.successful then
809 app_exception.raise_exception(exception_type => 'APP',
810 exception_code => -20275,
811 exception_text => lv_process_message);
812 end if;
813 if ln_threshold_slab_id is not null then
814
815 jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id(p_threshold_slab_id => ln_threshold_slab_id,
816 p_org_id => v_org_id,
817 p_threshold_tax_cat_id => ln_threshold_tax_cat_id,
818 p_process_flag => lv_process_flag,
819 p_process_message => lv_process_message);
820 if lv_process_flag <> jai_constants.successful then
821 app_exception.raise_exception(exception_type => 'APP',
822 exception_code => -20275,
823 exception_text => lv_process_message);
824 end if;
825 end if;
826 end if;
827 jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes('AR_LINES',
828 v_tax_category_id,
829 v_header_id,
830 v_customer_trx_line_id,
831 v_price_list_val,
832 v_line_tax_amount,
833 v_inventory_item_id,
834 nvl(v_quantity,
835 0),
836 pr_new.uom_code,
837 NULL,
838 NULL,
839 v_converted_rate,
840 v_creation_date,
841 v_created_by,
842 v_last_update_date,
843 v_last_updated_by,
844 v_last_update_login,
845 NULL,
846 ln_vat_assessable_value,
847 p_thhold_cat_base_tax_typ => jai_constants.tax_type_tcs,
848 p_threshold_tax_cat_id => ln_threshold_tax_cat_id,
849 p_source_trx_type => null,
850 p_source_table_name => null,
851 p_action => jai_constants.default_taxes);
852 END IF;
853 v_excise := 0;
854 v_additional := 0;
855 v_other := 0;
856 v_total_tax_amount := 0;
857 FOR excise_cal_rec in excise_cal_cur LOOP
858 IF excise_cal_rec.t_type IN ('Excise') THEN
859 v_excise := nvl(v_excise, 0) + excise_cal_rec.tax_amt;
860 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
861 v_additional := nvl(v_additional, 0) + excise_cal_rec.tax_amt;
862 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
863 v_other := nvl(v_other, 0) + excise_cal_rec.tax_amt;
864 END IF;
865 v_total_tax_amount := nvl(v_total_tax_amount, 0) +
866 nvl(excise_cal_rec.tax_amt, 0);
867 END LOOP;
868 v_tax_tot := nvl(v_excise, 0) + nvl(v_other, 0) +
869 nvl(v_additional, 0);
870 v_tot_amt := nvl(v_line_amount, 0) + nvl(v_total_tax_amount, 0);
871
872 UPDATE JAI_AR_TRXS
873 SET line_amount = NVL(line_amount, 0) + NVL(v_line_amount, 0),
874 total_amount = NVL(total_amount, 0) + NVL(v_tot_amt, 0),
875 tax_amount = NVL(tax_amount, 0) + NVL(v_total_tax_amount, 0)
876 WHERE JAI_AR_TRXS.customer_trx_id = v_header_id;
877
878 OPEN c_get_address_details(v_header_id);
879 FETCH c_get_address_details
880 into r_add;
881 CLOSE c_get_address_details;
882
883 v_service_type := get_service_type(NVL(r_add.SHIP_TO_CUSTOMER_ID,
884 r_add.BILL_TO_CUSTOMER_ID),
885 NVL(r_add.SHIP_TO_SITE_USE_ID,
886 r_add.BILL_TO_SITE_USE_ID),
887 'C');
888
889 Open Gl_Date_Cur;
890 Fetch Gl_Date_Cur
891 Into v_gl_date;
892 Close Gl_Date_Cur;
893 INSERT INTO JAI_AR_TRX_LINES
894 (customer_trx_line_id,
895 line_number,
896 customer_trx_id,
897 description,
898 inventory_item_id,
899 unit_code,
900 quantity,
901 tax_category_id,
902 auto_invoice_flag,
903 unit_selling_price,
904 line_amount,
905 gl_date,
906 tax_amount,
907 total_amount,
908 assessable_value,
909 creation_date,
910 created_by,
911 last_update_date,
912 last_updated_by,
913 last_update_login,
914 vat_assessable_value,
915 service_type_code)
916 VALUES
917 (v_customer_trx_line_id,
918 pr_new.line_number,
919 v_header_id,
920 pr_new.description,
921 pr_new.inventory_item_id,
922 pr_new.uom_code,
923 nvl(v_quantity, 0),
924 v_tax_category_id,
925 'N',
926 pr_new.unit_selling_price,
927 v_line_amount,
928 v_gl_date,
929 v_line_tax_amount,
930 (v_line_amount + v_line_tax_amount),
931 v_price_list,
932 v_creation_date,
933 v_created_by,
934 v_last_update_date,
935 v_last_updated_by,
936 v_last_update_login,
937 ln_vat_assessable_value,
938 v_service_type);
939
940 OPEN c_jai_ar_trx_lines(v_customer_trx_line_id);
941 FETCH c_jai_ar_trx_lines
942 INTO t_jai_line_rec_new;
943 CLOSE c_jai_ar_trx_lines;
944
945 lv_action := JAI_CONSTANTS.INSERTING;
946 JAI_AR_TAX_LINES_PKG.POPULATE_TAX_LINES_WRAPPER(pr_old => t_jai_line_rec_old,
947 pr_new => t_jai_line_rec_new,
948 pv_action => lv_action,
949 pv_return_code => lv_return_code,
950 pv_return_message => lv_return_message);
951 IF PV_RETURN_CODE <> JAI_CONSTANTS.successful THEN
952 RAISE le_error;
953 END IF;
954 END IF;
955
956 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
957 FND_LOG.STRING(G_LEVEL_PROCEDURE,
958 G_MODULE_NAME || lv_api_name,
959 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
960 END IF;
961 EXCEPTION
962 WHEN OTHERS THEN
963 Pv_return_code := jai_constants.unexpected_error;
964 Pv_return_message := SUBSTR('Encountered an error in ' ||
965 G_PACKAGE_NAME || '.' || lv_api_name || ' ' ||
966 sqlerrm,
967 1,
968 1996);
969 IF G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL THEN
970 FND_LOG.STRING(G_LEVEL_UNEXPECTED,
971 G_MODULE_NAME || lv_api_name,
972 SUBSTR('Encountered an error in ' || G_PACKAGE_NAME || '.' ||
973 lv_api_name || ' ' || sqlerrm,
974 1,
975 4000));
976 END IF;
977 END DEFAULT_TAX;
978
979 FUNCTION GET_SERVICE_TYPE(pn_party_id NUMBER,
980 pn_party_site_id NUMBER,
981 pv_party_type VARCHAR2) return VARCHAR2 IS
982 v_service_type VARCHAR2(30);
983 ln_address_id NUMBER;
984
985 CURSOR c_get_address IS
986 SELECT hzcas.cust_acct_site_id
987 FROM hz_cust_site_uses_all hzcsu, hz_cust_acct_sites_all hzcas
988 WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
989 AND hzcsu.site_use_id = pn_party_site_id
990 AND hzcas.cust_account_id = pn_party_id;
991
992 CURSOR cur_get_ser_type_customer(pn_cust_id NUMBER,
993 pn_address_id NUMBER) IS
994 SELECT service_type_code
995 FROM JAI_CMN_CUS_ADDRESSES
996 WHERE customer_id = pn_cust_id
997 AND address_id = pn_address_id;
998
999 CURSOR cur_get_ser_type_vendor(cp_vendor_id IN Po_Headers_All.vendor_id%type,
1000 cp_vendor_site_id IN Po_Headers_All.vendor_site_id%type) IS
1001 SELECT service_type_code
1002 FROM JAI_CMN_VENDOR_SITES
1003 WHERE vendor_id = cp_vendor_id
1004 AND vendor_site_id = cp_vendor_site_id;
1005
1006 /*
1007 ||This function is used to retreive the service type based on the customer id and address id
1008 ||passed as parameters.
1009 */
1010
1011 BEGIN
1012 IF pv_party_type = jai_constants.party_type_customer THEN
1013
1014 OPEN c_get_address;
1015 FETCH c_get_address
1016 INTO ln_address_id;
1017 CLOSE c_get_address;
1018
1019 OPEN cur_get_ser_type_customer(pn_party_id, ln_address_id);
1020 FETCH cur_get_ser_type_customer
1021 INTO v_service_type;
1022 CLOSE cur_get_ser_type_customer;
1023
1024 IF v_service_type is null THEN
1025
1026 OPEN cur_get_ser_type_customer(pn_party_id, 0);
1027 FETCH cur_get_ser_type_customer
1028 INTO v_service_type;
1029 CLOSE cur_get_ser_type_customer;
1030
1031 END IF;
1032
1033 ELSIF pv_party_type = jai_constants.party_type_vendor THEN
1034
1035 OPEN cur_get_ser_type_vendor(pn_party_id, pn_party_site_id);
1036 FETCH cur_get_ser_type_vendor
1037 INTO v_service_type;
1038 CLOSE cur_get_ser_type_vendor;
1039
1040 IF v_service_type IS NULL THEN
1041 OPEN cur_get_ser_type_vendor(pn_party_id, 0);
1042 FETCH cur_get_ser_type_vendor
1043 INTO v_service_type;
1044 CLOSE cur_get_ser_type_vendor;
1045 END IF;
1046
1047 END IF;
1048
1049 RETURN v_service_type;
1050
1051 END GET_SERVICE_TYPE;
1052
1053 PROCEDURE RECALCULATE_TAX_FOR_DM(pr_old t_jai_rec%type,
1054 pr_new t_rec%type,
1055 pv_action varchar2,
1056 pv_return_code out NOCOPY varchar2,
1057 pv_return_message out NOCOPY varchar2) IS
1058 lv_api_name CONSTANT VARCHAR2(100) := 'RECALCULATE_TAX_FOR_DM';
1059 lv_debug_info VARCHAR2(4000);
1060 t_jai_line_rec_old JAI_AR_TRX_LINES%ROWTYPE;
1061 t_jai_line_rec_new JAI_AR_TRX_LINES%ROWTYPE;
1062 CURSOR c_jai_ar_trx_lines(pn_customer_trx_line_id NUMBER) IS
1063 SELECT *
1064 FROM jai_ar_trx_lines
1065 WHERE customer_trx_line_id = pn_customer_trx_line_id;
1066 lv_action VARCHAR2(20);
1067 lv_return_message VARCHAR2(2000);
1068 lv_return_code VARCHAR2(100);
1069 le_error EXCEPTION;
1070 v_customer_id Number;
1071 v_address_id Number;
1072 v_org_id Number := 0;
1073 v_bill_to_site_use_id Number := 0;
1074 v_tax_category_list varchar2(30);
1075 v_tax_category_id Number;
1076 v_line_no Number;
1077 v_tax_id Number;
1078 v_tax_rate Number;
1079 v_tax_amount Number;
1080 v_line_tax_amount Number := 0;
1081 v_tax_tot Number := 0;
1082 v_tot_amt Number := 0;
1083 v_excise Number := 0;
1084 v_additional Number := 0;
1085 v_other Number := 0;
1086 v_price_list Number := 0;
1087 v_price_list_val Number := 0;
1088 v_price_list_uom_code Varchar2(3);
1089 v_conversion_rate Number := 0;
1090 v_old_tax_tot Number := 0;
1091 v_organization_id Number := -1;
1092 v_created_from Varchar2(30);
1093 v_once_completed_flag Varchar2(1);
1094 c_from_currency_code Varchar2(15);
1095 c_conversion_type Varchar2(30);
1096 c_conversion_date Date;
1097 c_conversion_rate Number := 0;
1098 v_converted_rate Number := 1;--Modified by Qinglei for bug#14627416
1099 v_books_id Number;
1100 v_row_id Rowid;
1101 v_uom_code varchar2(3);
1102 v_line_amount number;
1103 v_old_amount Number;
1104 v_item_class varchar2(30);
1105 v_inventory_item_id Number;
1106 v_header_id Number;
1107 v_customer_trx_line_id Number;
1108 v_last_update_date Date;
1109 v_last_updated_by Number;
1110 v_creation_date Date;
1111 v_created_by Number;
1112 v_last_update_login Number;
1113 v_trx_date Date;
1114 v_trans_type Varchar2(30);
1115
1116 Cursor bind_cur IS
1117 SELECT A.org_id, A.bill_to_customer_id, NVL(A.bill_to_site_use_id, 0)
1118 FROM RA_CUSTOMER_TRX_ALL A
1119 WHERE customer_trx_id = v_header_id;
1120
1121 Cursor header_info_cur IS
1122 SELECT set_of_books_id,
1123 invoice_currency_code,
1124 exchange_rate_type,
1125 exchange_date,
1126 exchange_rate,
1127 trx_date
1128 FROM RA_CUSTOMER_TRX_ALL
1129 WHERE customer_trx_id = v_header_id;
1130
1131 CURSOR excise_cal_cur IS
1132 select A.tax_id, A.tax_rate, A.tax_amount tax_amt, b.tax_type t_type
1133 from JAI_AR_TRX_TAX_LINES A, JAI_CMN_TAXES_ALL B
1134 where link_to_cust_trx_line_id = pr_old.customer_trx_line_id
1135 and A.tax_id = B.tax_id
1136 order by 1;
1137
1138 CURSOR ORG_CUR IS
1139 SELECT organization_id FROM JAI_AR_TRX_APPS_RELS_T;
1140
1141 CURSOR organization_cur IS
1142 SELECT organization_id
1143 FROM JAI_AR_TRXS
1144 WHERE trx_number =
1145 (SELECT recurred_from_trx_number
1146 FROM RA_CUSTOMER_TRX_ALL
1147 WHERE customer_trx_id = v_header_id);
1148
1149 CURSOR CREATED_FROM_CUR IS
1150 SELECT created_from
1151 FROM ra_customer_trx_all
1152 WHERE customer_trx_id = v_header_id;
1153
1154 Cursor transaction_type_cur IS
1155 Select a.type
1156 From RA_CUST_TRX_TYPES_ALL a, RA_CUSTOMER_TRX_ALL b
1157 Where a.cust_trx_type_id = b.cust_trx_type_id
1158 And b.customer_trx_id = v_header_id
1159 And (a.org_id = pr_new.org_id OR
1160 (a.org_id is null and pr_new.org_id is null));
1161 BEGIN
1162
1163 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1164 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1165 G_MODULE_NAME || lv_api_name,
1166 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
1167 END IF;
1168
1169 pv_return_code := jai_constants.successful;
1170
1171 v_header_id := pr_old.customer_trx_id;
1172 v_inventory_item_id := pr_new.inventory_item_id;
1173 v_item_class := 'N';
1174 v_uom_code := pr_new.uom_code;
1175 v_line_amount := nvl(nvl(pr_new.quantity_credited,
1176 pr_new.quantity_invoiced) *
1177 pr_new.unit_selling_price,
1178 nvl(pr_new.extended_amount, 0));
1179 /* v_old_amount := nvl(nvl(pr_old.quantity_credited,
1180 pr_new.quantity_invoiced) *
1181 pr_old.unit_selling_price,
1182 nvl(pr_old.extended_amount, 0));*/
1183 v_old_amount := pr_old.line_amount;
1184 v_customer_trx_line_id := pr_old.customer_trx_line_id;
1185 v_last_update_date := pr_new.last_update_date;
1186 v_last_updated_by := pr_new.last_updated_by;
1187 v_creation_date := pr_new.creation_date;
1188 v_created_by := pr_new.created_by;
1189 v_last_update_login := pr_new.last_update_login;
1190
1191 OPEN transaction_type_cur;
1192 FETCH transaction_type_cur
1193 INTO v_trans_type;
1194 CLOSE transaction_type_cur;
1195
1196 IF NVL(v_trans_type, 'N') not in ('CM', 'DM') THEN
1197 Return;
1198 END IF;
1199
1200 OPEN CREATED_FROM_CUR;
1201 FETCH CREATED_FROM_CUR
1202 INTO v_created_from;
1203 CLOSE CREATED_FROM_CUR;
1204
1205 if v_created_from = 'ARXTWMAI' and NVL(v_trans_type, 'N') = 'CM' then
1206 return;
1207 end if;
1208
1209 IF v_created_from in ('RAXTRX', 'ARXREC') THEN
1210 RETURN;
1211 END IF;
1212
1213 OPEN ORG_CUR;
1214 FETCH ORG_CUR
1215 INTO v_organization_id;
1216 CLOSE ORG_CUR;
1217 IF NVL(v_organization_id, 999999) = 999999 THEN
1218 OPEN organization_cur;
1219 FETCH organization_cur
1220 INTO v_organization_id;
1221 CLOSE organization_cur;
1222 END IF;
1223 IF NVL(v_organization_id, 999999) = 999999 THEN
1224
1225 RETURN;
1226 END IF;
1227 FOR excise_cal_rec in excise_cal_cur LOOP
1228 IF excise_cal_rec.t_type IN ('Excise') THEN
1229 v_excise := nvl(v_excise, 0) + nvl(excise_cal_rec.tax_amt, 0);
1230 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
1231 v_additional := nvl(v_additional, 0) +
1232 nvl(excise_cal_rec.tax_amt, 0);
1233 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
1234 v_other := nvl(v_other, 0) + nvl(excise_cal_rec.tax_amt, 0);
1235 END IF;
1236 END LOOP;
1237
1238 v_old_tax_tot := nvl(v_excise, 0) + nvl(v_other, 0) +
1239 nvl(v_additional, 0);
1240
1241 OPEN bind_cur;
1242 FETCH bind_cur
1243 INTO v_org_id, v_customer_id, v_bill_to_site_use_id;
1244 CLOSE bind_cur;
1245
1246 OPEN HEADER_INFO_CUR;
1247 FETCH HEADER_INFO_CUR
1248 INTO v_books_id,
1249 c_from_currency_code,
1250 c_conversion_type,
1251 c_conversion_date,
1252 c_conversion_rate,
1253 v_trx_date;
1254 CLOSE HEADER_INFO_CUR;
1255
1256 v_line_tax_amount := nvl(v_line_amount, 0);
1257
1258 IF c_conversion_date is NULL THEN
1259 c_conversion_date := v_trx_date;
1260 END IF;
1261
1262 v_converted_rate := jai_cmn_utils_pkg.currency_conversion(v_books_id,
1263 c_from_currency_code,
1264 c_conversion_date,
1265 c_conversion_type,
1266 c_conversion_rate);
1267 v_price_list_val := v_line_tax_amount;
1268
1269 jai_ar_utils_pkg.recalculate_tax('AR_LINES_UPDATE',
1270 null,
1271 v_header_id,
1272 v_customer_trx_line_id,
1273 v_price_list_val,
1274 v_line_tax_amount,
1275 v_converted_rate,
1276 v_inventory_item_id,
1277 NVL(pr_new.quantity_credited, 0),
1278 pr_new.uom_code,
1279 NULL,
1280 NULL,
1281 v_creation_date,
1282 v_created_by,
1283 v_last_update_date,
1284 v_last_updated_by,
1285 v_last_update_login);
1286 v_excise := 0;
1287 v_additional := 0;
1288 v_other := 0;
1289
1290 FOR excise_cal_rec in excise_cal_cur LOOP
1291 IF excise_cal_rec.t_type IN ('Excise') THEN
1292 v_excise := nvl(v_excise, 0) + excise_cal_rec.tax_amt;
1293 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
1294 v_additional := nvl(v_additional, 0) + excise_cal_rec.tax_amt;
1295 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
1296 v_other := nvl(v_other, 0) + excise_cal_rec.tax_amt;
1297 END IF;
1298 END LOOP;
1299 v_tax_tot := v_excise + v_other + v_additional;
1300 v_tot_amt := v_line_amount + v_tax_tot;
1301
1302 UPDATE JAI_AR_TRXS
1303 SET line_amount = NVL(line_amount, 0) + NVL(v_line_amount, 0) -
1304 NVL(v_old_amount, 0),
1305 total_amount = NVL(total_amount, 0) + NVL(v_tot_amt, 0) -
1306 nvl(v_old_amount, 0) - NVL(v_old_tax_tot, 0),
1307 tax_amount = NVL(tax_amount, 0) + NVL(v_tax_tot, 0) -
1308 NVL(v_old_tax_tot, 0),
1309 creation_date = v_creation_date,
1310 created_by = v_created_by,
1311 last_update_date = v_last_update_date,
1312 last_updated_by = v_last_updated_by,
1313 last_update_login = v_last_update_login
1314 WHERE customer_trx_id = v_header_id;
1315
1316 OPEN c_jai_ar_trx_lines(pr_old.customer_trx_line_id);
1317 FETCH c_jai_ar_trx_lines
1318 INTO t_jai_line_rec_old;
1319 CLOSE c_jai_ar_trx_lines;
1320
1321 UPDATE JAI_AR_TRX_LINES
1322 SET description = pr_new.description,
1323 inventory_item_id = pr_new.inventory_item_id,
1324 unit_code = pr_new.uom_code,
1325 quantity = pr_new.quantity_credited,
1326 auto_invoice_flag = 'N',
1327 tax_category_id = v_tax_category_id,
1328 unit_selling_price = pr_new.unit_selling_price,
1329 line_amount = v_line_amount,
1330 tax_amount = v_line_tax_amount,
1331 total_amount = v_line_amount + v_line_tax_amount,
1332 creation_date = v_creation_date,
1333 created_by = v_created_by,
1334 last_update_date = v_last_update_date,
1335 last_updated_by = v_last_updated_by,
1336 last_update_login = v_last_update_login
1337 WHERE customer_trx_line_id = pr_old.customer_trx_line_id
1338 AND customer_trx_id = v_header_id;
1339
1340 OPEN c_jai_ar_trx_lines(pr_old.customer_trx_line_id);
1341 FETCH c_jai_ar_trx_lines
1342 INTO t_jai_line_rec_new;
1343 CLOSE c_jai_ar_trx_lines;
1344 lv_action := JAI_CONSTANTS.UPDATING;
1345 IF (((t_jai_line_rec_new.AUTO_INVOICE_FLAG <> 'Y' AND
1346 t_jai_line_rec_old.AUTO_INVOICE_FLAG <> 'Y') AND
1347 (t_jai_line_rec_new.Excise_Invoice_No IS NULL) AND
1348 (t_jai_line_rec_new.payment_Register IS NULL) AND
1349 (t_jai_line_rec_new.Excise_Invoice_Date IS NULL)) OR
1350 (t_jai_line_rec_new.Customer_Trx_Id <>
1351 t_jai_line_rec_old.Customer_Trx_Id)) THEN
1352 JAI_AR_TAX_LINES_PKG.POPULATE_TAX_LINES_WRAPPER(pr_old => t_jai_line_rec_old,
1353 pr_new => t_jai_line_rec_new,
1354 pv_action => lv_action,
1355 pv_return_code => lv_return_code,
1356 pv_return_message => lv_return_message);
1357
1358 IF lv_return_code <> jai_constants.successful then
1359 RAISE le_error;
1360 END IF;
1361 END IF;
1362 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1363 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1364 G_MODULE_NAME || lv_api_name,
1365 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
1366 END IF;
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 Pv_return_code := jai_constants.unexpected_error;
1370 Pv_return_message := 'Encountered an error in ' || G_MODULE_NAME || '.' ||
1371 lv_api_name || substr(sqlerrm, 1, 1900);
1372 END RECALCULATE_TAX_FOR_DM;
1373
1374 PROCEDURE RECALCULATE_TAX_FOR_INV_CM(pr_old t_jai_rec%type,
1375 pr_new t_rec%type,
1376 pv_action varchar2,
1377 pv_return_code out NOCOPY varchar2,
1378 pv_return_message out NOCOPY varchar2) IS
1379 lv_api_name CONSTANT VARCHAR2(100) := 'RECALCULATE_TAX_FOR_INV_CM';
1380 lv_debug_info VARCHAR2(4000);
1381 t_jai_line_rec_old JAI_AR_TRX_LINES%ROWTYPE;
1382 t_jai_line_rec_new JAI_AR_TRX_LINES%ROWTYPE;
1383 CURSOR c_jai_ar_trx_lines(pn_customer_trx_line_id NUMBER) IS
1384 SELECT *
1385 FROM jai_ar_trx_lines
1386 WHERE customer_trx_line_id = pn_customer_trx_line_id;
1387 lv_action VARCHAR2(20);
1388 lv_return_message VARCHAR2(2000);
1389 lv_return_code VARCHAR2(100);
1390 le_error EXCEPTION;
1391 --Added by Zhiwei for JAI Trigger elimination begin
1392 ---------------------------------------------------------
1393 cursor c_get_rec(cn_customer_trx_id number) is
1394 select * from jai_ar_trxs where customer_trx_id = cn_customer_trx_id;
1395
1396 t_rec_new jai_ar_trxs%rowtype;
1397 t_rec_old jai_ar_trxs%rowtype;
1398
1399 ---------------------------------------------------------
1400 --Added by Zhiwei for JAI Trigger elimination end
1401
1402 v_customer_id Number;
1403 v_address_id Number;
1404 v_org_id Number := 0;
1405 v_bill_to_site_use_id Number := 0;
1406 v_tax_category_list varchar2(30);
1407 v_tax_category_id Number;
1408 v_line_no Number;
1409 v_tax_id Number;
1410 v_tax_rate Number;
1411 v_tax_amount Number;
1412 v_line_tax_amount Number := 0;
1413 v_new_quantity number := 0;
1414 v_old_quantity number := 0;
1415 v_line_amount number := 0;
1416 v_old_amount Number := 0;
1417 v_tax_tot Number := 0;
1418 v_tot_amt Number := 0;
1419 v_excise Number := 0;
1420 v_additional Number := 0;
1421 v_other Number := 0;
1422 v_price_list Number := 0;
1423 v_price_list_val Number := 0;
1424 v_price_list_uom_code Varchar2(3);
1425 v_conversion_rate Number := 0;
1426 v_old_tax_tot Number := 0;
1427 v_organization_id Number := -1;
1428 v_location_id NUMBER;
1429 v_created_from Varchar2(30);
1430 v_once_completed_flag Varchar2(1);
1431 c_from_currency_code Varchar2(15);
1432 c_conversion_type Varchar2(30);
1433 c_conversion_date Date;
1434 c_conversion_rate Number := 0;
1435 v_converted_rate Number := 1;--Modified by Qinglei for bug#14627416
1436 v_books_id Number;
1437 v_row_id Rowid;
1438 v_trx_date Date;
1439 v_trans_type Varchar2(30);
1440
1441 lv_enable_gst_flag VARCHAR2(3);
1442 v_uom_code varchar2(3);
1443 v_item_class varchar2(30);
1444 v_inventory_item_id Number;
1445 v_header_id Number;
1446 v_customer_trx_line_id Number;
1447 v_last_update_date Date;
1448 v_last_updated_by Number;
1449 v_creation_date Date;
1450 v_created_by Number;
1451 v_last_update_login Number;
1452
1453 Cursor bind_cur IS
1454 SELECT A.org_id, A.bill_to_customer_id, NVL(A.bill_to_site_use_id, 0)
1455 FROM RA_CUSTOMER_TRX_ALL A
1456 WHERE customer_trx_id = v_header_id;
1457
1458 Cursor header_info_cur IS
1459 SELECT set_of_books_id,
1460 invoice_currency_code,
1461 exchange_rate_type,
1462 exchange_date,
1463 exchange_rate,
1464 trx_date
1465 FROM RA_CUSTOMER_TRX_ALL
1466 WHERE customer_trx_id = v_header_id;
1467
1468 Cursor tax_id_cur(p_tax_category_id IN Number) IS
1469 SELECT line_no
1470 FROM JAI_CMN_TAX_CTG_LINES A
1471 WHERE A.tax_category_id = p_tax_category_id
1472 ORDER BY line_no;
1473
1474 Cursor address_cur(p_ship_to_site_use_id IN Number) IS
1475 SELECT cust_acct_site_id address_id
1476 FROM hz_cust_site_uses_all A
1477 WHERE A.site_use_id = p_ship_to_site_use_id;
1478
1479 CURSOR excise_cal_cur IS
1480 select A.tax_id, A.tax_rate, A.tax_amount tax_amt, b.tax_type t_type
1481 from JAI_AR_TRX_TAX_LINES A, JAI_CMN_TAXES_ALL B
1482 where link_to_cust_trx_line_id = pr_old.customer_trx_line_id
1483 and A.tax_id = B.tax_id
1484 order by 1;
1485
1486 CURSOR ORG_CUR IS
1487 SELECT organization_id, location_id FROM JAI_AR_TRX_APPS_RELS_T;
1488
1489 CURSOR organization_cur IS
1490 SELECT organization_id, location_id
1491 FROM JAI_AR_TRXS
1492 WHERE trx_number =
1493 (SELECT recurred_from_trx_number
1494 FROM RA_CUSTOMER_TRX_ALL
1495 WHERE customer_trx_id = v_header_id);
1496
1497 CURSOR CREATED_FROM_CUR IS
1498 SELECT created_from, trx_date
1499 FROM ra_customer_trx_all
1500 WHERE customer_trx_id = v_header_id;
1501
1502 CURSOR ONCE_COMPLETE_FLAG_CUR IS
1503 SELECT once_completed_flag
1504 FROM JAI_AR_TRXS
1505 WHERE customer_trx_id = v_header_id;
1506
1507 Cursor transaction_type_cur IS
1508 Select a.type
1509 From RA_CUST_TRX_TYPES_ALL a, RA_CUSTOMER_TRX_ALL b
1510 Where a.cust_trx_type_id = b.cust_trx_type_id
1511 And b.customer_trx_id = v_header_id
1512 And (a.org_id = pr_new.org_id OR
1513 (a.org_id is NULL AND pr_new.org_id is NULL));
1514
1515 ln_vat_assessable_value JAI_AR_TRX_LINES.VAT_ASSESSABLE_VALUE%TYPE;
1516
1517 ld_trx_date DATE;
1518 BEGIN
1519
1520 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1521 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1522 G_MODULE_NAME || lv_api_name,
1523 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
1524 END IF;
1525
1526 pv_return_code := jai_constants.successful;
1527 v_header_id := pr_old.customer_trx_id;
1528 v_inventory_item_id := pr_new.inventory_item_id;
1529 v_item_class := 'N';
1530 v_uom_code := pr_new.uom_code;
1531 v_customer_trx_line_id := pr_old.customer_trx_line_id;
1532 v_last_update_date := pr_new.last_update_date;
1533 v_last_updated_by := pr_new.last_updated_by;
1534 v_creation_date := pr_new.creation_date;
1535 v_created_by := pr_new.created_by;
1536 v_last_update_login := pr_new.last_update_login;
1537
1538 OPEN transaction_type_cur;
1539 FETCH transaction_type_cur
1540 INTO v_trans_type;
1541 CLOSE transaction_type_cur;
1542 IF NVL(v_trans_type, 'N') not in ('INV', 'CM') THEN
1543 Return;
1544 END IF;
1545
1546 if NVL(v_trans_type, 'N') = 'INV' then
1547 v_new_quantity := pr_new.quantity_invoiced;
1548 --v_old_quantity := pr_old.quantity_invoiced;
1549 elsif NVL(v_trans_type, 'N') = 'CM' then
1550 v_new_quantity := pr_new.quantity_credited;
1551 --v_old_quantity := pr_old.quantity_credited;
1552 end if;
1553 v_line_amount := nvl(v_new_quantity * pr_new.unit_selling_price,
1554 nvl(pr_new.extended_amount, 0));
1555 /*v_old_amount := nvl(v_old_quantity * pr_old.unit_selling_price,
1556 nvl(pr_old.extended_amount, 0));*/
1557 v_old_amount := pr_old.line_amount;
1558
1559 OPEN ONCE_COMPLETE_FLAG_CUR;
1560 FETCH ONCE_COMPLETE_FLAG_CUR
1561 INTO v_once_completed_flag;
1562 CLOSE ONCE_COMPLETE_FLAG_CUR;
1563 IF NVL(v_once_completed_flag, 'N') = 'Y' THEN
1564 RETURN;
1565 END IF;
1566
1567 OPEN CREATED_FROM_CUR;
1568 FETCH CREATED_FROM_CUR
1569 INTO v_created_from, ld_trx_date;
1570 CLOSE CREATED_FROM_CUR;
1571 IF v_created_from in ('RAXTRX', 'ARXREC') THEN
1572 RETURN;
1573 END IF;
1574
1575 OPEN ORG_CUR;
1576 FETCH ORG_CUR
1577 INTO v_organization_id, v_location_id;
1578 CLOSE ORG_CUR;
1579 IF NVL(v_organization_id, 999999) = 999999 THEN
1580 OPEN organization_cur;
1581 FETCH organization_cur
1582 INTO v_organization_id, v_location_id;
1583 CLOSE organization_cur;
1584 END IF;
1585 IF NVL(v_organization_id, 999999) = 999999 THEN
1586 RETURN;
1587 END IF;
1588 FOR excise_cal_rec in excise_cal_cur LOOP
1589 IF excise_cal_rec.t_type IN ('Excise') THEN
1590 v_excise := nvl(v_excise, 0) + nvl(excise_cal_rec.tax_amt, 0);
1591 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
1592 v_additional := nvl(v_additional, 0) +
1593 nvl(excise_cal_rec.tax_amt, 0);
1594 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
1595 v_other := nvl(v_other, 0) + nvl(excise_cal_rec.tax_amt, 0);
1596 END IF;
1597 END LOOP;
1598
1599 v_old_tax_tot := nvl(v_excise, 0) + nvl(v_other, 0) +
1600 nvl(v_additional, 0);
1601
1602 OPEN bind_cur;
1603 FETCH bind_cur
1604 INTO v_org_id, v_customer_id, v_bill_to_site_use_id;
1605 CLOSE bind_cur;
1606
1607 OPEN HEADER_INFO_CUR;
1608 FETCH HEADER_INFO_CUR
1609 INTO v_books_id,
1610 c_from_currency_code,
1611 c_conversion_type,
1612 c_conversion_date,
1613 c_conversion_rate,
1614 v_trx_date;
1615 CLOSE HEADER_INFO_CUR;
1616
1617 OPEN address_cur(v_bill_to_site_use_id);
1618 FETCH address_cur
1619 INTO v_address_id;
1620 CLOSE address_cur;
1621
1622 IF v_customer_id IS NOT NULL AND v_address_id IS NOT NULL THEN
1623
1624 jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes(v_organization_id,
1625 v_customer_id,
1626 v_bill_to_site_use_id,
1627 v_inventory_item_id,
1628 v_header_id,
1629 v_customer_trx_line_id,
1630 v_tax_category_id);
1631 ELSE
1632 jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes(v_organization_id,
1633 v_inventory_item_id,
1634 v_tax_category_id);
1635 END IF;
1636
1637 v_price_list := jai_om_utils_pkg.get_oe_assessable_value(p_customer_id => v_customer_id,
1638 p_ship_to_site_use_id => v_bill_to_site_use_id,
1639 p_inventory_item_id => v_inventory_item_id,
1640 p_uom_code => v_uom_code,
1641 p_default_price => pr_new.unit_selling_price,
1642 p_ass_value_date => ld_trx_date,
1643
1644 p_sob_id => v_books_id,
1645 p_curr_conv_code => c_conversion_type,
1646 p_conv_rate => c_conversion_rate);
1647 v_price_list_val := v_new_quantity * NVL(v_price_list, 0);
1648 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value(p_party_id => v_customer_id,
1649 p_party_site_id => v_bill_to_site_use_id,
1650 p_inventory_item_id => v_inventory_item_id,
1651 p_uom_code => v_uom_code,
1652 p_default_price => pr_new.unit_selling_price,
1653 p_ass_value_date => ld_trx_date,
1654 p_party_type => 'C');
1655
1656 ln_vat_assessable_value := nvl(ln_vat_assessable_value, 0) *
1657 v_new_quantity;
1658
1659 v_line_tax_amount := nvl(v_line_amount, 0);
1660
1661 jai_ar_utils_pkg.recalculate_tax('AR_LINES_UPDATE',
1662 v_tax_category_id,
1663 v_header_id,
1664 v_customer_trx_line_id,
1665 v_price_list_val,
1666 v_line_tax_amount,
1667 v_converted_rate,
1668 v_inventory_item_id,
1669 NVL(v_new_quantity, 0),
1670 pr_new.uom_code,
1671 NULL,
1672 NULL,
1673 v_creation_date,
1674 v_created_by,
1675 v_last_update_date,
1676 v_last_updated_by,
1677 v_last_update_login,
1678 ln_vat_assessable_value);
1679 v_excise := 0;
1680 v_additional := 0;
1681 v_other := 0;
1682
1683 FOR excise_cal_rec in excise_cal_cur LOOP
1684 IF excise_cal_rec.t_type IN ('Excise') THEN
1685 v_excise := nvl(v_excise, 0) + excise_cal_rec.tax_amt;
1686 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
1687 v_additional := nvl(v_additional, 0) + excise_cal_rec.tax_amt;
1688 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
1689 v_other := nvl(v_other, 0) + excise_cal_rec.tax_amt;
1690 END IF;
1691 END LOOP;
1692
1693 v_tax_tot := v_excise + v_other + v_additional;
1694 v_tot_amt := v_line_amount + v_tax_tot;
1695
1696 UPDATE JAI_AR_TRXS
1697 SET line_amount = NVL(line_amount, 0) + NVL(v_line_amount, 0) -
1698 NVL(v_old_amount, 0),
1699 total_amount = NVL(total_amount, 0) + NVL(v_tot_amt, 0) -
1700 nvl(v_old_amount, 0) - NVL(v_old_tax_tot, 0),
1701 tax_amount = NVL(tax_amount, 0) + NVL(v_tax_tot, 0) -
1702 NVL(v_old_tax_tot, 0),
1703 creation_date = v_creation_date,
1704 created_by = v_created_by,
1705 last_update_date = v_last_update_date,
1706 last_updated_by = v_last_updated_by,
1707 last_update_login = v_last_update_login
1708 WHERE customer_trx_id = v_header_id;
1709
1710 OPEN c_jai_ar_trx_lines(pr_old.customer_trx_line_id);
1711 FETCH c_jai_ar_trx_lines
1712 INTO t_jai_line_rec_old;
1713 CLOSE c_jai_ar_trx_lines;
1714
1715
1716 UPDATE JAI_AR_TRX_LINES
1717 SET description = pr_new.description,
1718 inventory_item_id = pr_new.inventory_item_id,
1719 unit_code = pr_new.uom_code,
1720 quantity = v_new_quantity,
1721 auto_invoice_flag = 'N',
1722 tax_category_id = v_tax_category_id,
1723 unit_selling_price = pr_new.unit_selling_price,
1724 line_amount = v_line_amount,
1725 tax_amount = v_line_tax_amount,
1726 total_amount = v_line_amount + v_line_tax_amount,
1727 creation_date = v_creation_date,
1728 created_by = v_created_by,
1729 last_update_date = v_last_update_date,
1730 last_updated_by = v_last_updated_by,
1731 last_update_login = v_last_update_login,
1732 assessable_value = v_price_list,
1733 vat_assessable_value = ln_vat_assessable_value
1734 WHERE customer_trx_line_id = pr_old.customer_trx_line_id
1735 AND customer_trx_id = v_header_id;
1736
1737 OPEN c_jai_ar_trx_lines(pr_old.customer_trx_line_id);
1738 FETCH c_jai_ar_trx_lines
1739 INTO t_jai_line_rec_new;
1740 CLOSE c_jai_ar_trx_lines;
1741 lv_action := JAI_CONSTANTS.UPDATING;
1742 IF (((t_jai_line_rec_new.AUTO_INVOICE_FLAG <> 'Y' AND
1743 t_jai_line_rec_old.AUTO_INVOICE_FLAG <> 'Y') AND
1744 (t_jai_line_rec_new.Excise_Invoice_No IS NULL) AND
1745 (t_jai_line_rec_new.payment_Register IS NULL) AND
1746 (t_jai_line_rec_new.Excise_Invoice_Date IS NULL)) OR
1747 (t_jai_line_rec_new.Customer_Trx_Id <>
1748 t_jai_line_rec_old.Customer_Trx_Id)) THEN
1749
1750 JAI_AR_TAX_LINES_PKG.POPULATE_TAX_LINES_WRAPPER(pr_old => t_jai_line_rec_old,
1751 pr_new => t_jai_line_rec_new,
1752 pv_action => lv_action,
1753 pv_return_code => lv_return_code,
1754 pv_return_message => lv_return_message);
1755
1756 IF lv_return_code <> jai_constants.successful then
1757 RAISE le_error;
1758 END IF;
1759 END IF;
1760 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1761 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1762 G_MODULE_NAME || lv_api_name,
1763 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
1764 END IF;
1765 EXCEPTION
1766 WHEN OTHERS THEN
1767 Pv_return_code := jai_constants.unexpected_error;
1768 Pv_return_message := 'Encountered an error in ' || G_MODULE_NAME || '.' ||
1769 lv_api_name || substr(sqlerrm, 1, 1900);
1770 END RECALCULATE_TAX_FOR_INV_CM;
1771
1772 PROCEDURE DELETE_AR_TRX_LINES(pr_old t_jai_rec%type,
1773 pr_new t_rec%type,
1774 pv_action varchar2,
1775 pv_return_code out NOCOPY varchar2,
1776 pv_return_message out NOCOPY varchar2) IS
1777 lv_api_name CONSTANT VARCHAR2(100) := 'DELETE_AR_TRX_LINES';
1778 lv_debug_info VARCHAR2(4000);
1779 v_excise Number := 0;
1780 v_additional Number := 0;
1781 v_other Number := 0;
1782 v_old_tax_tot Number := 0;
1783 v_old_line_amount Number := 0;
1784 v_customer_trx_line_id Number;
1785 v_customer_trx_id Number;
1786 v_once_completed_flag Varchar2(1);
1787
1788 CURSOR excise_cal_cur IS
1789 SELECT A.tax_id, A.tax_rate, A.tax_amount tax_amt, b.tax_type t_type
1790 FROM JAI_AR_TRX_TAX_LINES A, JAI_CMN_TAXES_ALL B
1791 WHERE link_to_cust_trx_line_id = v_customer_trx_line_id
1792 and A.tax_id = B.tax_id
1793 order by 1;
1794
1795 CURSOR old_line_amount_cur IS
1796 SELECT line_amount, tax_amount
1797 FROM JAI_AR_TRX_LINES
1798 WHERE CUSTOMER_TRX_ID = v_customer_trx_id
1799 AND CUSTOMER_TRX_LINE_ID = v_customer_trx_line_id;
1800
1801 CURSOR ONCE_COMPLETE_FLAG_CUR IS
1802 SELECT once_completed_flag
1803 FROM JAI_AR_TRXS
1804 WHERE customer_trx_id = v_customer_trx_id;
1805
1806 v_trans_type Varchar2(30);
1807
1808 v_so_line_check Number := 0;
1809 v_rma_line_check Number := 0;
1810
1811 BEGIN
1812
1813 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1814 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1815 G_MODULE_NAME || lv_api_name,
1816 G_PACKAGE_NAME || ':' || lv_api_name || '.BEGIN()+');
1817 END IF;
1818 pv_return_code := jai_constants.successful;
1819 v_customer_trx_line_id := pr_old.CUSTOMER_TRX_LINE_ID;
1820 v_customer_trx_id := pr_old.CUSTOMER_TRX_ID;
1821
1822 FOR excise_cal_rec in excise_cal_cur LOOP
1823 IF excise_cal_rec.t_type IN ('Excise') THEN
1824 v_excise := nvl(v_excise, 0) + nvl(excise_cal_rec.tax_amt, 0);
1825 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
1826 v_additional := nvl(v_additional, 0) +
1827 nvl(excise_cal_rec.tax_amt, 0);
1828 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
1829 v_other := nvl(v_other, 0) + nvl(excise_cal_rec.tax_amt, 0);
1830 END IF;
1831 END LOOP;
1832 v_old_tax_tot := nvl(v_excise, 0) + nvl(v_other, 0) +
1833 nvl(v_additional, 0);
1834
1835 OPEN old_line_amount_cur;
1836 FETCH old_line_amount_cur
1837 INTO v_old_line_amount, v_old_tax_tot;
1838 CLOSE old_line_amount_cur;
1839
1840 UPDATE JAI_AR_TRXS
1841 SET line_amount = nvl(line_amount, 0) - nvl(v_old_line_amount, 0),
1842 tax_amount = nvl(tax_amount, 0) - nvl(v_old_tax_tot, 0),
1843 total_amount = nvl(total_amount, 0) -
1844 (nvl(v_old_line_amount, 0) + nvl(v_old_tax_tot, 0))
1845 WHERE customer_trx_id = v_customer_trx_id;
1846
1847 DELETE FROM JAI_AR_TRXS trx
1848 WHERE customer_trx_id = v_customer_trx_id
1849 AND EXISTS (SELECT 1
1850 FROM ra_interface_lines_all il
1851 WHERE il.customer_trx_id = v_customer_trx_id
1852 AND NVL(il.interface_status, '~') <> 'P');
1853
1854 DELETE JAI_AR_TRX_TAX_LINES
1855 WHERE LINK_TO_CUST_TRX_LINE_ID = v_customer_trx_line_id;
1856
1857 DELETE JAI_AR_TRX_INS_LINES_T
1858 WHERE CUSTOMER_TRX_ID = v_customer_trx_id
1859 AND LINK_TO_CUST_TRX_LINE_ID = v_customer_trx_line_id;
1860
1861 DELETE JAI_AR_TRX_LINES
1862 WHERE CUSTOMER_TRX_ID = v_customer_trx_id
1863 AND CUSTOMER_TRX_LINE_ID = v_customer_trx_line_id;
1864 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1865 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1866 G_MODULE_NAME || lv_api_name,
1867 G_PACKAGE_NAME || ':' || lv_api_name || '.END()-');
1868 END IF;
1869 EXCEPTION
1870 WHEN OTHERS THEN
1871 Pv_return_code := jai_constants.unexpected_error;
1872 Pv_return_message := 'Encountered an error in ' || G_MODULE_NAME || '.' ||
1873 lv_api_name || ':' || substr(sqlerrm, 1, 1900);
1874
1875 END DELETE_AR_TRX_LINES;
1876 END JAI_AR_TRX_LINES_PKG;