[Home] [Help]
PACKAGE BODY: APPS.JAI_AR_TRXS_PKG
Source
1 PACKAGE BODY JAI_AR_TRXS_PKG AS
2 /* $Header: jai_ar_trxs.plb 120.9.12020000.1 2013/03/12 07:17:16 appldev noship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME update_excise_registers
7 REM
8 REM DESCRIPTION Copied from JAI_JAR_TRXS_TRIGGER_PKG CALLED FROM
9 REM trigger JAI_JAR_T_ARIUD_T1
10 REM
11 REM NOTES Refers to old trigger JAI_JAR_T_ARU_T1
12 REM
13 REM +======================================================================+
14 */
15 PROCEDURE update_excise_registers ( 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
16 v_org_id Number;
17 v_loc_id Number := 0;
18 v_receipt_id Number;
19 v_reg_code Varchar2(30);
20 v_reg_type Varchar2(10);
21 v_complete_flag Varchar2(1);
22 v_fin_year Number;
23 v_register_id number;
24 v_payment_register Varchar2(30) ;
25 v_basic_ed Number := 0;
26 v_additional_ed Number := 0;
27 v_other_ed Number := 0;
28 v_rg_flag Varchar2(1);
29 v_tax_amount Number := 0;
30 v_charge_account number;
31 v_tax_rate Number := 0;
32 v_rg_register_id Number := 0;
33
34 v_rg23a_bal number := 0;
35 v_rg23c_bal number := 0;
36 v_pla_bal number := 0;
37 v_sold_cust_id Number;
38 v_ship_cust_id Number;
39 v_ship_to_site_use_id Number;
40 v_cust_trx_type_id Number;
41 v_trx_date Date;
42 v_batch_source_id Number := 0;
43 v_account_id Number;
44 v_counter Number := 0;
45 v_average_duty Number := 0;
46 v_rg23_part_ii_no Number;
47 v_rg23_part_i_no Number;
48 v_excise_flag Varchar2(1);
49 v_pla_register_no Number;
50 v_part_i_register_id Number ;
51
52
53 v_item_class Varchar2(10); -- := 'N';
54 v_customer_trx_id Number; -- := pr_old.customer_trx_id;
55 v_last_update_date Date ; -- := pr_new.last_update_date;
56 v_last_updated_by Number; -- := pr_new.last_updated_by;
57 v_creation_date Date; -- := SYSDATE;
58 v_created_by Number; -- := pr_new.created_by;
59 v_last_update_login Number; -- := pr_new.last_update_login;
60 v_set_of_books_id Number; -- := pr_new.set_of_books_id;
61 v_currency_code Varchar2(30); -- := pr_new.invoice_currency_code;
62 v_conv_date Date ; -- := NVL(pr_new.exchange_date, pr_new.creation_date);
63 v_conv_type_code Varchar2(30); -- := pr_new.Exchange_Rate_Type;
64 v_conv_rate Number ; -- := pr_new.Exchange_rate;
65 v_trx_number varchar2(20); -- := pr_new.Trx_Number;
66 v_source_name Varchar2(100); -- := 'Receivables India';
67 v_category_name Varchar2(100); -- := 'RG Register Data Entry';
68
69
70 v_converted_rate Number;
71 v_assessable_value Number;
72
73
74 v_tax_rate_counter Number := 0;
75 v_match_tax_rate Number := 0;
76 v_rg23d_receipt_id Number;
77 v_oth_receipt_id Number;
78 v_duty_amount number;
79 uom_code varchar2(3);
80 V_item_trading_flag Varchar2(1);
81 v_modvat_tax_rate Number;
82 v_remarks Varchar2(60);
83 v_rounding_factor Number;
84 v_opt_unit Number;
85 v_bill_to_customer_id Number;
86 v_bill_to_site_use_id Number;
87
88 lv_excise_invoice_no jai_ar_trx_lines.excise_invoice_no%TYPE;
89 req_id NUMBER;
90 result BOOLEAN;
91
92 --added by Zhiwei for bug#14640257 begin
93 -------------------------------------------------------
94 cursor get_gl_date is
95 select gl_date
96 from RA_CUST_TRX_LINE_GL_DIST_ALL
97 where customer_trx_id = pr_new.customer_trx_id
98 and account_class = 'REV';
99
100 cursor get_created_from is
101 select created_from
102 from ra_customer_trx_all
103 where customer_trx_id = pr_new.customer_trx_id;
104
105 ld_gl_date date;
106 lv_created_from ra_customer_trx_all.created_from%type;
107 ----------------------------------------------------------
108 --added by Zhiwei for bug#14640257 end
109
110 Cursor complete_info_cur IS
111 Select SHIP_TO_CUSTOMER_ID,SHIP_TO_SITE_USE_ID,CUST_TRX_TYPE_ID,
112 TRX_DATE,SOLD_TO_CUSTOMER_ID,
113 BATCH_SOURCE_ID,
114 BILL_TO_CUSTOMER_ID, BILL_TO_SITE_USE_ID
118
115 From JAI_AR_TRX_INS_HDRS_T
116 Where customer_trx_id = v_customer_trx_id;
117
119
120 Cursor org_loc_cur IS
121 SELECT organization_id, location_id, register_type, rg_update_flag, once_completed_flag
122 FROM JAI_AR_TRX_APPS_RELS_T r;
123
124
125 Cursor line_cur IS
126 SELECT customer_trx_line_id line_id, payment_register, inventory_item_id,
127 quantity quantity_invoiced,unit_selling_price,unit_code,
128 excise_invoice_no, excise_invoice_date, assessable_value,
129 customer_trx_line_id, excise_exempt_type
130 FROM JAI_AR_TRX_LINES
131 WHERE customer_trx_id = v_customer_trx_id;
132 CURSOR REG_BALANCE_CUR(p_org_id IN Number,
133 p_loc_id IN Number) IS
134 SELECT nvl(rg23a_balance,0) rg23a_balance ,nvl(rg23c_balance,0) rg23c_balance,
135 nvl(pla_balance,0) pla_balance
136 FROM JAI_CMN_RG_BALANCES
137 WHERE organization_id = p_org_id AND
138 location_id = p_loc_id;
139 Cursor register_code_cur(p_org_id IN Number, p_loc_id IN Number, p_batch_source_id IN NUMBER) IS
140 SELECT register_code
141 FROM JAI_OM_OE_BOND_REG_HDRS
142 WHERE organization_id = p_org_id AND
143 location_id = p_loc_id AND
144 register_id in (SELECT register_id FROM JAI_OM_OE_BOND_REG_DTLS
145 WHERE order_type_id = p_batch_source_id and order_flag ='N');
146 CURSOR excise_cal_cur(p_line_id IN NUMBER, p_inventory_item_id IN NUMBER,
147 p_org_id IN NUMBER) IS
148 select A.tax_id,
149 A.tax_rate t_rate,
150 A.tax_amount tax_amt,
151 A.func_tax_amount func_amt,
152 (a.func_tax_amount*100)/decode(a.tax_rate,0,0.01) taxable_amt,
153 A.BASE_TAX_AMOUNT BASE_TAX_AMT,
154 b.tax_type t_type,
155 b.stform_type,
156 a.tax_line_no
157 from JAI_AR_TRX_TAX_LINES A , JAI_CMN_TAXES_ALL B,
158 JAI_INV_ITM_SETUPS C
159 where link_to_cust_trx_line_id = p_line_id
160 and a.tax_id = b.tax_id
161 and c.inventory_item_id = p_inventory_item_id
162 and c.organization_id = p_org_id
163 AND c.item_class in ('RMIN','RMEX','CGEX','CGIN','CCEX','CCIN','FGIN','FGEX')
164 order by 1;
165 cursor item_class_cur(P_ORG_ID IN NUMBER,P_INVENTORY_ITEM_ID IN NUMBER) IS
166 select item_class, excise_flag,item_trading_flag
167 from JAI_INV_ITM_SETUPS
168 where inventory_item_id = p_inventory_item_id AND
169 ORGANIZATION_ID = P_ORG_ID;
170 cursor organization_cur IS
171 select organization_id,location_id
172 FROM JAI_AR_TRX_INS_HDRS_T
173 WHERE customer_trx_id = v_customer_trx_id;
174 CURSOR fin_year_cur(p_org_id IN NUMBER) IS
175 SELECT MAX(a.fin_year)
176 FROM JAI_CMN_FIN_YEARS a
177 WHERE organization_id = p_org_id and fin_active_flag = 'Y';
178
179 CURSOR matched_receipt_cur(p_customer_trx_line_id IN NUMBER) IS
180 SELECT a.receipt_id, a.quantity_applied, b.transaction_type,b.qty_to_adjust,
181 b.rate_per_unit,b.excise_duty_rate
182 FROM JAI_CMN_MATCH_RECEIPTS a, JAI_CMN_RG_23D_TRXS b
183 WHERE a.ref_line_id = p_customer_trx_line_id
184 AND a.receipt_id = b.register_id
185 AND a.quantity_applied > 0 ;
186 CURSOR tax_rate_cur(p_customer_trx_line_id IN NUMBER, p_receipt_id IN NUMBER)
187 IS
188 SELECT tax_rate
189 FROM JAI_CMN_MATCH_TAXES
190 WHERE ref_line_id = p_customer_trx_line_id
191 AND receipt_id = p_receipt_id;
192 -- AND nvl(receipt_id,0) = p_receipt_id;
193
194
195 CURSOR for_modvat_percentage(v_org_id NUMBER, v_location_id NUMBER) IS
196 SELECT MODVAT_REVERSE_PERCENT
197 FROM JAI_CMN_INVENTORY_ORGS
198 WHERE organization_id = v_org_id
199 AND (location_id = v_location_id
200 OR
201 (location_id is NULL AND v_location_id is NULL));
202 --AND NVL(location_id,0) = NVL(v_location_id,0);
203
204 CURSOR for_modvat_tax_rate(p_cust_trx_line_id NUMBER) IS
205 SELECT a.tax_rate, b.rounding_factor
206 FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
207 WHERE a.tax_id = b.tax_id
208 AND a.link_to_cust_trx_line_id = p_cust_trx_line_id
209 AND b.tax_type = jai_constants.tax_type_modvat_recovery ;
210
211 CURSOR get_opt_unit is SELECT Operating_unit
212 FROM org_organization_definitions
213 WHERE organization_id = nvl(v_org_id,0);
214
215
216
217
218 v_ref_10 gl_interface.reference10%type; -- := 'India Localization Entry for Invoice # '; -- will hold a standard text such as 'India Localization Entry for sales order'
219 v_ref_23 gl_interface.reference23%type; -- := 'ja_in_ar_hdr_complete_trg'; -- holds the object name -- 'ja_in_ar_hdr_complete_trg'
220 v_ref_24 gl_interface.reference24%type; -- := 'ra_customer_trx_lines_all'; -- holds the table name -- ' ra_customer_trx_all'
221 v_ref_25 gl_interface.reference25%type; -- := 'customer_trx_line_id'; -- holds the column name -- 'customer_trx_id'
222 v_ref_26 gl_interface.reference26%type; -- := v_customer_trx_id; -- holds the column value -- eg -- 13645
223
224
225
226 VSQLERRM varchar2(250);
227 vsqlstmt varchar2(20);
228
229 lv_ship_status JAI_CMN_MATCH_RECEIPTS.ship_status%type ;
230
231
232 CURSOR cur_get_trx_cess_amt (cp_trx_line_id in number)
233 IS
234 SELECT
235 sum(jrcttl.func_tax_amount) cess_amount
236 FROM
237 jai_ar_trx_lines jrctl ,
238 jai_ar_trx_tax_lines jrcttl ,
239 jai_cmn_taxes_all jtc
240 WHERE
241 jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id AND
245 jrctl.customer_trx_line_id = cp_trx_line_id;
242 jrcttl.tax_id = jtc.tax_id AND
243
244 upper(jtc.tax_type) IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess) AND /* added by ssawant for bug 5989740 */
246
247
248 CURSOR cur_get_trx_sh_cess_amt (cp_trx_line_id in number)
249 IS
250 SELECT
251 sum(jrcttl.func_tax_amount) sh_cess_amount
252 FROM
253 jai_ar_trx_lines jrctl,
254 jai_ar_trx_tax_lines jrcttl,
255 jai_cmn_taxes_all jtc
256 WHERE
257 jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id AND
258 jrcttl.tax_id = jtc.tax_id AND
259 upper(jtc.tax_type) IN (jai_constants.tax_type_sh_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess) AND
260 jrctl.customer_trx_line_id = cp_trx_line_id;
261
262 ln_trx_totcess_amt JAI_CMN_RG_I_TRXS.CESS_AMT%TYPE;
263 ln_trx_totshcess_amt JAI_CMN_RG_I_TRXS.SH_CESS_AMT%TYPE;
264
265
266
267 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
268 BEGIN
269 pv_return_code := jai_constants.successful ;
270
271 --added by Zhiwei for bug#14640257 begin
272 ----------------------------------------------------------
273 open get_created_from;
274 fetch get_created_from into lv_created_from;
275 close get_created_from;
276
277 if(nvl(lv_created_from,'###')= 'ARXTWMAI')then
278
279 open get_gl_date;
280 fetch get_gl_date into ld_gl_date;
281 close get_gl_date;
282
283 else
284 ld_gl_date := SYSDATE;
285 end if;
286
287 jai_om_rg_pkg.gl_accounting_date := ld_gl_date;
288 -----------------------------------------------------------
289 --added by Zhiwei for bug#14640257 end
290
291 v_item_class := 'N'; --Ramananda for File.Sql.35
292 v_customer_trx_id := pr_old.customer_trx_id; --Ramananda for File.Sql.35
293 v_last_update_date := pr_new.last_update_date; --Ramananda for File.Sql.35
294 v_last_updated_by := pr_new.last_updated_by; --Ramananda for File.Sql.35
295 v_creation_date := SYSDATE; --Ramananda for File.Sql.35
296 v_created_by := pr_new.created_by;--Ramananda for File.Sql.35
297 v_last_update_login := pr_new.last_update_login; --Ramananda for File.Sql.35
298 v_set_of_books_id := pr_new.set_of_books_id; --Ramananda for File.Sql.35
299 v_currency_code := pr_new.invoice_currency_code; --Ramananda for File.Sql.35
300 v_conv_date := NVL(pr_new.exchange_date, pr_new.creation_date); --Ramananda for File.Sql.35
301 v_conv_type_code := pr_new.Exchange_Rate_Type; --Ramananda for File.Sql.35
302 v_conv_rate := pr_new.Exchange_rate; --Ramananda for File.Sql.35
303 --2001/05/04 Vijay,Subbu.
304 v_trx_number := pr_new.Trx_Number; --Ramananda for File.Sql.35
305 v_source_name := 'Receivables India'; --Ramananda for File.Sql.35
306 v_category_name := jai_constants.je_category_rg_entry ; -- modified by csahoo for bug#6155839 --'RG Register Data Entry'; --Ramananda for File.Sql.35
307 v_ref_10 := 'India Localization Entry for Invoice # '; -- will hold a standard text such as 'India Localization Entry for sales order'
308 v_ref_23 := 'ja_in_ar_hdr_complete_trg'; -- holds the object name -- 'ja_in_ar_hdr_complete_trg'
309 v_ref_24 := 'ra_customer_trx_lines_all'; -- holds the table name -- ' ra_customer_trx_all'
310 v_ref_25 := 'customer_trx_line_id'; -- holds the column name -- 'customer_trx_id'
311 v_ref_26 := v_customer_trx_id; -- holds the column value -- eg -- 13645
312
313 OPEN complete_info_cur;
314 FETCH complete_info_cur INTO v_ship_cust_id, v_ship_to_site_use_id,
315 v_cust_trx_type_id,
316 v_trx_date,v_sold_cust_id,v_batch_source_id,
317 v_bill_to_customer_id, v_bill_to_site_use_id;
318 CLOSE complete_info_cur;
319 IF v_trx_date IS NULL THEN
320 RETURN;
321 END IF;
322
323 OPEN org_loc_cur;
324 FETCH org_loc_cur INTO v_org_id, v_loc_id, v_reg_type, v_rg_flag,
325 v_complete_flag ;
326 CLOSE org_loc_cur;
327 IF NVL(v_org_id,999999) = 999999 THEN -- made 0 to 999999 because in case of setup business group setup , inventory organization value is 0
328 -- which was causing code to return .
329 OPEN organization_cur;
330 FETCH organization_cur INTO v_org_id, v_loc_id;
331 CLOSE organization_cur;
332 END IF;
333 IF NVL(v_org_id,999999) = 999999 THEN -- made 0 to 999999 because in case of setup business group setup , inventory organization value is 0
334 -- which was causing code to return .
335 RETURN;
336 END IF;
337
338
339
340 v_ref_10 := v_ref_10 || v_trx_number ;
341 vsqlstmt := '1';
342
343 IF pr_new.ONCE_COMPLETED_FLAG <> pr_old.ONCE_COMPLETED_FLAG THEN
344 IF v_set_of_books_id IS NULL
345 THEN
346
347 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
348 (p_org_id => v_org_id );
349 v_set_of_books_id := l_func_curr_det.ledger_id;
350 END IF;
351 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_set_of_books_id ,v_currency_code ,
352 v_conv_date ,v_conv_type_code, v_conv_rate);
353
354 vsqlstmt := '2';
355
356 jai_cmn_bond_register_pkg.GET_REGISTER_ID(v_org_id,
357 v_loc_id,
358 v_batch_source_id,
359 'N',
360 v_register_id,
361 v_reg_code
362 );
363 vsqlstmt := '3';
367 OPEN REG_BALANCE_CUR(v_org_id, v_loc_id);
364 OPEN fin_year_cur(v_org_id);
365 FETCH fin_year_cur INTO v_fin_year;
366 CLOSE fin_year_cur;
368 FETCH REG_BALANCE_CUR INTO v_rg23a_bal,v_rg23c_bal,v_pla_bal;
369 CLOSE REG_BALANCE_CUR;
370 vsqlstmt := '4';
371
372 OPEN get_opt_unit;
373 FETCH get_opt_unit INTO v_opt_unit;
374 CLOSE get_opt_unit;
375
376 vsqlstmt := '5';
377 FOR l_rec in line_cur LOOP
378
379 v_ref_26 := l_rec.customer_trx_line_id;
380
381 OPEN item_class_cur(v_org_id,l_rec.inventory_item_id);
382 FETCH item_class_cur INTO v_item_class , v_excise_flag,V_item_trading_flag;
383 CLOSE item_class_cur;
384
385
386 vsqlstmt := '5';
387 IF NVL(v_excise_flag,'N') = 'Y' THEN
388 FOR excise_cal_rec in excise_cal_cur(l_rec.line_id, l_rec.inventory_item_id,
389 v_org_id)
390 LOOP
391 IF v_reg_code in ('BOND_REG') THEN
392
393 vsqlstmt := '6';
394 IF excise_cal_rec.t_type IN ('Excise') THEN
395 v_basic_ed := NVL(v_basic_ed,0) +
396 NVL(excise_cal_rec.BASE_TAX_AMT * (excise_cal_rec.t_rate)/100 ,0);
397 v_tax_rate := NVL(v_tax_rate,0) + NVL(excise_cal_rec.t_rate,0);
398 vsqlstmt := '7';
399 IF NVL(excise_cal_rec.t_rate,0) > 0 THEN
400 v_counter := v_counter + 1;
401 END IF;
402 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
403 vsqlstmt := '8';
404 v_additional_ed := NVL(v_additional_ed,0) +
405 NVL(excise_cal_rec.BASE_TAX_AMT * (excise_cal_rec.t_rate)/100 ,0);
406 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
407 vsqlstmt := '9';
408 v_other_ed := NVL(v_other_ed,0) +
409 NVL(excise_cal_rec.BASE_TAX_AMT * (excise_cal_rec.t_rate)/100 ,0);
410 END IF;
411 ELSE
412 IF excise_cal_rec.t_type IN ('Excise') THEN
413 vsqlstmt := '10';
414 v_basic_ed := NVL(v_basic_ed,0) + NVL(excise_cal_rec.func_amt,0);
415 v_tax_rate := NVL(v_tax_rate,0) + NVL(excise_cal_rec.t_rate,0);
416 IF NVL(excise_cal_rec.t_rate,0) > 0 THEN
417 vsqlstmt := '11';
418 v_counter := v_counter + 1;
419 END IF;
420 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
421 vsqlstmt := '12';
422 v_additional_ed := NVL(v_additional_ed,0) +NVL(excise_cal_rec.func_amt,0);
423 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
424 vsqlstmt := '13';
425 v_other_ed := NVL(v_other_ed,0) + NVL(excise_cal_rec.func_amt,0);
426 END IF;
427 END IF;
428
429 END LOOP;
430 IF NVL(v_counter,0) = 0 THEN
431 v_counter := 1;
432 END IF;
433 vsqlstmt := '14';
434 v_average_duty := NVL(v_tax_rate,0)/v_counter;
435 vsqlstmt := '15';
436 v_average_duty := TRUNC(v_average_duty,2);
437 v_tax_amount := NVL(v_basic_ed,0) + NVL(v_additional_ed,0) +
438 NVL(v_other_ed,0);
439
440 IF v_item_class IN ('RMIN','RMEX','CGEX','CGIN','FGIN','FGEX','CCIN','CCEX')THEN
441
442 vsqlstmt := '16';
443
444 v_assessable_value := NVL(v_converted_rate,0) * nvl(l_rec.assessable_value,0);
445 IF v_reg_code in ('DOMESTIC_EXCISE','EXPORT_EXCISE',
446 'DOMESTIC_WITHOUT_EXCISE', 'BOND_REG') THEN
447 vsqlstmt := '17';
448
449 ln_trx_totcess_amt := null;
450 ln_trx_totshcess_amt := null;
451
452
453 OPEN cur_get_trx_cess_amt(l_rec.line_id) ;
454 FETCH cur_get_trx_cess_amt INTO ln_trx_totcess_amt;
455 CLOSE cur_get_trx_cess_amt ;
456
457
458
459 OPEN cur_get_trx_sh_cess_amt(l_rec.line_id) ;
460 FETCH cur_get_trx_sh_cess_amt INTO ln_trx_totshcess_amt;
461 CLOSE cur_get_trx_sh_cess_amt ;
462
463
464
465 IF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') THEN
466 vsqlstmt := '18';
467 IF l_rec.payment_register = 'RG23A' THEN
468 v_reg_type := 'A';
469 v_payment_register := 'RG23A';
470 ELSIF l_rec.payment_register = 'RG23C' THEN
471 v_reg_type := 'C';
472 v_payment_register := 'RG23A';
473 ELSIF l_rec.payment_register = 'PLA' THEN
474 v_payment_register := 'PLA';
475 END IF;
476 vsqlstmt := '19';
477 jai_om_rg_pkg.ja_in_rg_I_entry(
478
479 p_fin_year => v_fin_year ,
480 p_org_id => v_org_id ,
481 p_location_id => v_loc_id ,
482 p_inventory_item_id => l_rec.inventory_item_id ,
483 p_transaction_id => 33 ,
484 --p_transaction_date => SYSDATE ,--commented by Zhiwei for bug#14640257
485 p_transaction_date => ld_gl_date, --Added by Zhiwei for bug#14640257
486 p_transaction_type => 'I' ,
487 p_header_id => v_customer_trx_id ,
488 p_excise_quantity => l_rec.quantity_invoiced ,
489 p_excise_amount => v_tax_amount ,
490 p_uom_code => l_rec.unit_code ,
494 p_basic_ed => v_basic_ed ,
491 p_excise_invoice_no => l_rec.excise_invoice_no ,
492 p_excise_invoice_date => l_rec.excise_invoice_date ,
493 p_payment_register => v_payment_register ,
495 p_additional_ed => v_additional_ed ,
496 p_other_ed => v_other_ed ,
497 p_excise_duty_rate => v_average_duty ,
498 p_customer_id => v_ship_cust_id ,
499 p_customer_site_id => v_ship_to_site_use_id ,
500 p_register_code => v_reg_code ,
501 p_creation_date => v_creation_date ,
502 p_created_by => v_created_by ,
503 p_last_update_date => v_last_update_date ,
504 p_last_updated_by => v_last_updated_by ,
505 p_last_update_login => v_last_update_login ,
506 p_assessable_value => v_assessable_value ,
507 p_cess_amt => ln_trx_totcess_amt ,
508 p_sh_cess_amt => ln_trx_totshcess_amt ,
509 p_source => jai_constants.source_ar
510 );
511
512
513 vsqlstmt := '20';
514 SELECT JAI_CMN_RG_I_TRXS_S.CURRVAL INTO v_part_i_register_id from dual;
515 ELSIF v_item_class IN ('CGEX','CGIN') THEN
516 v_reg_type := 'C';
517 ELSIF v_item_class IN ('RMIN','RMEX') THEN
518 v_reg_type := 'A';
519 END IF;
520 IF v_item_class IN ('RMIN','RMEX','CGIN','CGEX') THEN
521 vsqlstmt := '21';
522 jai_om_rg_pkg.ja_in_rg23_part_I_entry(v_reg_type, v_fin_year, v_org_id,
523 v_loc_id,
524 --l_rec.inventory_item_id, 33, SYSDATE, 'I', --commented by Zhiwei for bug#14640257
525 l_rec.inventory_item_id, 33, ld_gl_date, 'I',--added by Zhiwei for bug#14640257
526 l_rec.quantity_invoiced, l_rec.unit_code, l_rec.excise_invoice_no,
527 l_rec.excise_invoice_date, v_basic_ed, v_additional_ed,
528 v_other_ed, v_ship_cust_id, v_ship_to_site_use_id,
529 v_customer_trx_id, SYSDATE, v_reg_code,
530 v_creation_date, v_created_by,v_last_update_date,
531 v_last_updated_by, v_last_update_login );
532 vsqlstmt := '22';
533 SELECT JAI_CMN_RG_23AC_I_TRXS_S.CURRVAL INTO v_part_i_register_id from dual;
534 END IF;
535 END IF;
536 IF v_reg_code in ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
537
538
539
540
541
542 if pr_new.update_rg_flag = 'Y' or pr_old.update_rg_flag = 'Y' then -- 3496577
543
544
545 IF l_rec.payment_register IN( 'RG23A','RG23C') THEN
546 IF l_rec.payment_register = 'RG23A' THEN
547 v_reg_type := 'A';
548 ELSIF l_rec.payment_register = 'RG23C' THEN
549 v_reg_type := 'C';
550 END IF;
551 IF NVL(l_rec.EXCISE_EXEMPT_TYPE,'@@@') IN
552 ('CT2', 'EXCISE_EXEMPT_CERT' )
553 AND v_item_class NOT IN ('OTIN', 'OTEX')
554 THEN
555 v_reg_type := 'A';
556 OPEN for_modvat_percentage(v_org_id, v_loc_id);
557 FETCH for_modvat_percentage INTO v_modvat_tax_rate;
558 CLOSE for_modvat_percentage;
559 v_basic_ed := ROUND((l_rec.quantity_invoiced * l_rec.unit_selling_price
560 * v_modvat_tax_rate)/100);
561 v_remarks := 'Against Modvat Recovery'||'-'||l_rec.EXCISE_EXEMPT_TYPE;
562 ELSIF NVL(l_rec.EXCISE_EXEMPT_TYPE,'@@@') IN
563 ('CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH')
564 AND v_item_class NOT IN ('OTIN', 'OTEX')
565 THEN
566 OPEN for_modvat_tax_rate(l_rec.line_id);
567 FETCH for_modvat_tax_rate INTO v_modvat_tax_rate, v_rounding_factor;
568 CLOSE for_modvat_tax_rate;
569 v_basic_ed := (l_rec.quantity_invoiced * l_rec.unit_selling_price
570 * v_modvat_tax_rate)/100;
571 IF v_rounding_factor IS NOT NULL THEN
572 v_basic_ed := ROUND(v_basic_ed, v_rounding_factor);
573 ELSE
574 v_basic_ed := ROUND(v_basic_ed);
575 END IF;
576 v_remarks := 'Against Modvat Recovery'||'-'||l_rec.EXCISE_EXEMPT_TYPE;
577 END IF;
578
579 vsqlstmt := '23';
580 jai_om_rg_pkg.ja_in_rg23_part_II_entry(v_reg_code,v_reg_type, v_fin_year, v_org_id,
581 v_loc_id,
582 --l_rec.inventory_item_id, 33, SYSDATE, v_part_i_register_id,--commented by Zhiwei for bug#14640257
583 l_rec.inventory_item_id, 33, ld_gl_date, v_part_i_register_id,--added by Zhiwei for bug#14640257
584 l_rec.excise_invoice_no, l_rec.excise_invoice_date ,
585 round(v_basic_ed),
586 round(v_additional_ed),
587 round(v_other_ed),
588 v_ship_cust_id, v_ship_to_site_use_id,
589 v_source_name, v_category_name,
590 v_creation_date, v_created_by,v_last_update_date,
591 v_last_updated_by, v_last_update_login, l_rec.line_id,
595 v_ref_24,
592 l_rec.excise_exempt_type, v_remarks ,
593 v_ref_10,
594 v_ref_23,
596 v_ref_25,
597 v_ref_26
598 );
599 vsqlstmt := '24';
600 ELSIF l_rec.payment_register IN ('PLA') THEN
601 vsqlstmt := '25';
602 jai_om_rg_pkg.ja_in_pla_entry(v_org_id,
603 v_loc_id,
604 l_rec.inventory_item_id,
605 v_fin_year,
606 33,
607 l_rec.line_id,
608 --SYSDATE,--commented by Zhiwei for bug#14640257
609 ld_gl_date,--added by Zhiwei for bug#14640257
610 l_rec.excise_invoice_no ,
611 l_rec.excise_invoice_date ,
612 round(v_basic_ed),
613 round(v_additional_ed),
614 round(v_other_ed),
615 v_ship_cust_id,
616 v_ship_to_site_use_id,
617 v_source_name,
618 v_category_name,
619 v_creation_date,
620 v_created_by,
621 v_last_update_date,
622 v_last_updated_by,
623 v_last_update_login ,
624 v_ref_10,
625 v_ref_23,
626 v_ref_24,
627 v_ref_25,
628 v_ref_26
629 );
630 vsqlstmt := '26';
631 END IF;
632 IF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') THEN
633 SELECT JAI_CMN_RG_I_TRXS_S.CURRVAL INTO v_rg23_part_i_no from dual;
634 IF l_rec.payment_register IN( 'RG23A','RG23C') THEN
635 SELECT JAI_CMN_RG_23AC_II_TRXS_S.CURRVAL INTO v_rg23_part_ii_no from dual;
636 UPDATE JAI_CMN_RG_I_TRXS
637 SET register_id_part_ii = v_rg23_part_ii_no,
638 CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_23AC_II_TRXS
639 WHERE register_id = v_rg23_part_ii_no)
640 WHERE register_id = v_rg23_part_i_no;
641 ELSIF l_rec.payment_register IN( 'PLA') THEN
642 SELECT JAI_CMN_RG_PLA_TRXS_S1.CURRVAL INTO v_pla_register_no from dual;
643 UPDATE JAI_CMN_RG_I_TRXS
644 SET register_id_part_ii = v_pla_register_no,
645 CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_PLA_TRXS
646 WHERE register_id = v_pla_register_no)
647 WHERE register_id = v_rg23_part_i_no;
648 END IF;
649 ELSIF v_item_class IN ('RMIN','RMEX','CGIN','CGEX') THEN
650 SELECT JAI_CMN_RG_23AC_I_TRXS_S.CURRVAL INTO v_rg23_part_i_no from dual;
651 IF l_rec.payment_register IN( 'RG23A','RG23C') THEN
652 SELECT JAI_CMN_RG_23AC_II_TRXS_S.CURRVAL INTO v_rg23_part_ii_no from dual;
653 UPDATE JAI_CMN_RG_23AC_I_TRXS
654 SET REGISTER_ID_PART_II = v_rg23_part_ii_no,
655 CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_23AC_II_TRXS
656 WHERE register_id = v_rg23_part_ii_no)
657 WHERE register_id = v_rg23_part_i_no;
658 ELSIF l_rec.payment_register IN( 'PLA') THEN
659 SELECT JAI_CMN_RG_PLA_TRXS_S1.CURRVAL INTO v_pla_register_no from dual;
660 UPDATE JAI_CMN_RG_23AC_I_TRXS
661 SET REGISTER_ID_PART_II = v_pla_register_no,
662 CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_PLA_TRXS
663 WHERE register_id = v_pla_register_no)
664 WHERE register_id = v_rg23_part_i_no;
665 END IF;
666 END IF;
667 END IF;
668
669 end if;
670 IF v_reg_code IN ('BOND_REG','23D_EXPORT_WITHOUT_EXCISE') THEN
671 vsqlstmt := '27';
672 jai_om_rg_pkg.ja_in_register_txn_entry(
673 v_org_id,
674 v_loc_id,
675 l_rec.excise_invoice_no,
676 'BOND SALES',
677 'N',
678 l_rec.line_id,--v_customer_trx_id,
679 round(v_tax_amount * NVL(pr_new.exchange_rate ,1),2) ,
680 v_reg_code,
681 v_creation_date,
682 v_created_by,
683 v_last_update_date,
684 v_last_updated_by,
685 v_last_update_login ,
686 pr_new.Batch_source_id,
687 NVL(pr_new.exchange_Rate,1)
688 );
689 vsqlstmt := '28';
690 END IF;
691 IF v_reg_code IN('23D_EXPORT_WITHOUT_EXCISE','23D_EXPORT_EXCISE',
692 '23D_DOMESTIC_EXCISE','23D_DOM_WITHOUT_EXCISE')
693 and
694 nvl(pr_new.update_rg23d_flag,'N') = 'Y'
695 then
696 if nvl(v_item_trading_flag,'N') = 'Y' then
697 select sum(func_tax_amount) into v_duty_amount
698 from JAI_AR_TRX_TAX_LINES
699 where link_to_cust_trx_line_id=l_rec.customer_trx_line_id;
700 FOR match_rec IN matched_receipt_cur(l_rec.customer_trx_line_id)
701 LOOP
702 FOR rate_rec IN tax_rate_cur(l_rec.customer_trx_line_id,
703 match_rec.receipt_id)
704 LOOP
705 IF nvl(rate_rec.tax_rate,0) > 0 THEN
706 v_tax_rate_counter := v_tax_rate_counter + 1;
707 v_match_tax_rate := nvl(v_match_tax_rate,0)
708 + nvl(rate_rec.tax_rate,0);
709 END IF;
710
711 IF v_counter > 0 and v_tax_rate_counter > 0 THEN
712 v_match_tax_rate := v_match_tax_rate/v_tax_rate_counter;
713 END IF;
714 END LOOP;
715
716 Select JAI_CMN_RG_23D_TRXS_S.NEXTVAL into v_register_id From Dual;
717 IF match_rec.transaction_type = 'R'
718 THEN
719 v_rg23d_receipt_id := match_rec.receipt_id;
720 ELSIF match_rec.transaction_type = 'CR'
721 THEN
722 v_oth_receipt_id := match_rec.receipt_id;
723 END IF;
724 vsqlstmt := '29';
725 jai_om_rg_pkg.ja_in_rg23d_entry(
726 v_register_id,
727 v_org_id, v_loc_id,
728 v_fin_year, 'I', l_rec.inventory_item_id,
729 l_rec.customer_trx_line_id, l_rec.unit_code, l_rec.unit_code,
730 v_ship_cust_id, v_sold_cust_id, v_ship_to_site_use_id,
731 match_rec.quantity_applied,
732 v_reg_code, match_rec.rate_per_unit,
733 match_rec.excise_duty_rate,v_tax_amount ,null,
734 v_source_name, v_category_name, null,null,
735 v_creation_date,v_created_by,v_last_update_date,
736 v_last_update_login,
737 v_last_updated_by, null, null, null,
738 l_rec.excise_invoice_no,
739 v_trx_date,
740 v_ref_10,v_ref_23,v_ref_24,v_ref_25,v_ref_26);
741 vsqlstmt := '30';
742
743 jai_cmn_rg_23d_trxs_pkg.upd_receipt_qty_matched(match_rec.receipt_id,match_rec.quantity_applied,
744 match_rec.qty_to_adjust
745 );
746
747 lv_ship_status := 'CLOSED' ;
748
749 UPDATE JAI_CMN_MATCH_RECEIPTS
750 set ship_status = lv_ship_status --'CLOSED'
751 where ref_line_id = l_rec.customer_trx_line_id;
752 END LOOP;
753 END IF;
754 END IF;
755 ELSE
756 null;
757 END IF;
758 v_item_class := '';
759 v_tax_amount := 0;
760 v_basic_ed := 0;
761 v_additional_ed := 0;
762 v_other_ed := 0;
763 v_average_duty := 0;
764 v_counter := 0;
765 v_tax_rate := 0;
766 v_reg_type := '';
767 END IF;
768
769
770 IF l_rec.excise_invoice_no IS NOT NULL AND lv_excise_invoice_no IS NULL THEN
771 lv_excise_invoice_no := l_rec.excise_invoice_no;
772
773 END IF;
774
775 END LOOP;
776 DELETE JAI_AR_TRX_INS_HDRS_T
777 WHERE CUSTOMER_TRX_ID = v_customer_trx_id;
778
779 END IF;
780
781
782 EXCEPTION
783 WHEN OTHERS THEN
784 Pv_return_code := jai_constants.unexpected_error;
785 Pv_return_message := 'Encountered an error in JAI_JAR_TRXS_TRIGGER_PKG.ARU_T1 ' || substr(sqlerrm,1,1900);
786
787 END update_excise_registers ;
788
789 END JAI_AR_TRXS_PKG ;