[Home] [Help]
PACKAGE BODY: APPS.JAI_AR_IMPORT_SYNC_PKG
Source
4 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1 PACKAGE BODY JAI_AR_IMPORT_SYNC_PKG AS
2 /* $Header: jai_ar_import_sync.plb 120.4.12020000.1 2013/03/12 07:15:13 appldev noship $ */
3
5 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
6 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
10 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_MODULE_NAME CONSTANT VARCHAR2(40) := 'JAI.PLSQL.JAI_AR_IMPORT_SYNC_PKG.';
12 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JAI_AR_IMPORT_SYNC_PKG';
13
14
15 /*
16 || foll function created by csahoo - for seperate vat invoice num for unreg dealers
17 */
18 FUNCTION check_reg_dealer
19 ( pn_customer_id NUMBER ,
20 pn_site_use_id NUMBER
21 ) return boolean
22
23 IS
24 ln_address_id NUMBER;
25 lv_regno JAI_CMN_CUS_ADDRESSES.vat_Reg_no%type;
26
27 CURSOR c_get_address is
28 SELECT hzcas.cust_acct_site_id
29 FROM hz_cust_site_uses_all hzcsu ,
30 hz_cust_acct_sites_all hzcas
31 WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
32 AND hzcsu.site_use_id = pn_site_use_id
33 AND hzcas.cust_account_id = pn_customer_id ;
34
35 CURSOR c_regno (pn_address_id NUMBER) IS
36 SELECT vat_Reg_no
37 FROM JAI_CMN_CUS_ADDRESSES
38 WHERE customer_id = pn_customer_id
39 AND address_id = pn_address_id;
40
41
42 BEGIN
43
44 open c_get_address;
45 fetch c_get_address into ln_address_id;
46 close c_get_address;
47
48 IF ln_address_id IS NOT NULL THEN
49
50 open c_regno (ln_address_id);
51 fetch c_regno into lv_regno;
52 close c_regno;
53 END IF;
54
55 IF lv_regno IS NULL THEN
56 return (false);
57 ELSE
58 return (true);
59 END IF;
60
61
62 END check_reg_dealer;
63
64
65
66 /*
67 REM +======================================================================+
68 REM NAME gen_vat_for_inv renamed from ARU_T1
69 REM
70 REM DESCRIPTION Called from trigger JAI_TAX_PROCESSING_PKG.PROCESS_COMPLETE
71 REM
72 REM NOTES Refers to old trigger JAI_AR_RCTA_TRIGGER_PKG.ARU_T1
73 REM
74 REM +======================================================================+
75 */
76 PROCEDURE gen_vat_for_inv( 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
77
78
79 v_vat_start_num JAI_CMN_INVENTORY_ORGS.current_number%Type;
80 v_vat_jump_by JAI_CMN_INVENTORY_ORGS.jump_by%type;
81 v_vat_prefix JAI_CMN_INVENTORY_ORGS.prefix%type;
82 v_vat_invoice_no JAI_AR_TRXS.tax_invoice_no%type;
83 v_vat_reg_no JAI_CMN_INVENTORY_ORGS.vat_reg_no%type;
84
85
86 v_organization_id Number;
87 v_loc_id Number;
88 v_vat_taxes_exist Number;
89 v_trans_type VARCHAR2(30);
90 v_loc_vat_inv_no JAI_AR_TRXS.tax_invoice_no%type;
91
92 CURSOR organization_cur IS
93 SELECT organization_id,location_id
94 FROM JAI_AR_TRXS
95 where customer_trx_id = pr_new.customer_trx_id;
96
97 CURSOR C_VAT_INVOICE_CUR IS
98 SELECT TAX_INVOICE_NO
99 FROM JAI_AR_TRXS
100 WHERE Customer_Trx_Id = pr_new.customer_trx_id;
101
102
103 cursor c_vat_taxes_exist
104 is
105 select 1
106 from JAI_AR_TRX_TAX_LINES
107 where link_to_cust_trx_line_id
108 in
109 (select customer_trx_line_id
110 from JAI_AR_TRX_LINES
111 where customer_trx_id = pr_new.customer_trx_id
112 )
113 and tax_id in
114 (select tax_id
115 from JAI_CMN_TAXES_ALL
116 where vat_flag = 'Y'
117 and org_id = pr_new.org_id
118 )
119 ;
120
121 CURSOR transaction_type_cur IS
122 SELECT TYPE
123 FROM RA_CUST_TRX_TYPES_ALL
124 WHERE cust_trx_type_id = pr_new.cust_trx_type_id
125 AND NVL(org_id,0) = NVL(pr_new.org_id,0);
126
127 l_api_name CONSTANT VARCHAR2(30) := 'GEN_VAT_FOR_INV()';
128
129 Procedure Generate_Tax_Invoice_no (p_organization_id Number , p_loc_id Number) is
130
131 Cursor c_get_vat_reg_no is
132 select vat_reg_no
133 from JAI_CMN_INVENTORY_ORGS
134 where organization_id = p_organization_id
135 and location_id = p_loc_id;
136
137 cursor c_get_vat_invoice_no is
138 select current_number , jump_by , prefix
139 from JAI_CMN_INVENTORY_ORGS
140 where organization_id = p_organization_id
141 and location_id = p_loc_id;
142
143 --Added by Zhiwei.hou for JAI Trigger elimination begin
144 ---------------------------------------------------------
145 cursor c_get_rec(cn_customer_trx_id number)
146 is
147 select *
148 from jai_ar_trxs
149 where customer_trx_id = cn_customer_trx_id;
150
151 t_rec_new jai_ar_trxs%rowtype;
152 t_rec_old jai_ar_trxs%rowtype;
153
154 lv_action VARCHAR2(20) ;
155 lv_return_message VARCHAR2(2000);
156 lv_return_code VARCHAR2(100) ;
157 le_error EXCEPTION ;
158 ---------------------------------------------------------
159 --Added by Zhiwei.hou for JAI Trigger elimination end
160
161
162
163 Begin
164
165 open c_get_vat_reg_no;
166 fetch c_get_vat_reg_no into v_vat_reg_no;
170 -- VAT reg number has not been defined for the org and loc.
167 close c_get_vat_reg_no;
168
169 if v_vat_reg_no is null then
171 return;
172 end if;
173
174
175 -- lock the records
176 update JAI_CMN_INVENTORY_ORGS
177 set last_update_date = last_update_date
178 where vat_reg_no = v_vat_reg_no;
179
180 Open c_get_vat_invoice_no;
181 Fetch c_get_vat_invoice_no into v_vat_start_num, v_vat_jump_by, v_vat_prefix;
182 close c_get_vat_invoice_no;
183
184 v_vat_start_num := NVL(v_vat_start_num,0) + NVL(v_vat_jump_by,1);
185
186 if v_vat_prefix is not null then
187 v_vat_invoice_no := v_vat_prefix || '/' || v_vat_start_num;
188 else
189 v_vat_invoice_no := v_vat_start_num;
190 end if;
191
192 --Added by Zhiwei for JAI Trigger elimination begin
193 ---------------------------------------------------------
194 open c_get_rec(pr_new.customer_trx_id);
195 fetch c_get_rec into t_rec_old;
196 close c_get_rec;
197
198 ---------------------------------------------------------
199 --Added by Zhiwei for JAI Trigger elimination end
200
201 update JAI_AR_TRXS
202 set tax_invoice_no = v_vat_invoice_no
203 where customer_trx_id = pr_new.customer_trx_id;
204
205 --Added by Zhiwei for JAI Trigger elimination begin
206 ---------------------------------------------------------
207 open c_get_rec(pr_new.customer_trx_id);
208 fetch c_get_rec into t_rec_new;
209 close c_get_rec;
210
211
212
213 if(t_rec_new.once_completed_flag = 'Y')then
214
215 lv_action := jai_constants.updating ;
216
217 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
218 pr_old => t_rec_old ,
219 pr_new => t_rec_new ,
220 pv_action => lv_action ,
221 pv_return_code => lv_return_code ,
222 pv_return_message => lv_return_message
223 );
224
225 IF lv_return_code <> jai_constants.successful then
226 RAISE le_error;
227 END IF;
228
229 end if;
230 ---------------------------------------------------------
231 --Added by Zhiwei for JAI Trigger elimination end
232
233 update JAI_CMN_INVENTORY_ORGS
234 set current_number = NVL(v_vat_start_num,0) ,
235 prefix = v_vat_prefix,
236 jump_by = v_vat_jump_by
237 where vat_Reg_no = v_vat_reg_no;
238
239 End;
240
241
242 BEGIN
243
244 if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
245 FND_LOG.STRING(G_LEVEL_PROCEDURE,
246 G_MODULE_NAME || l_api_name || '.BEGIN',
247 G_PKG_NAME || ': ' || l_api_name || '()+');
248 end if;
249
250
251 pv_return_code := jai_constants.successful ;
252 Open C_VAT_INVOICE_CUR;
253 Fetch C_VAT_INVOICE_CUR into v_loc_vat_inv_no;
254 close C_VAT_INVOICE_CUR;
255
256 if v_loc_vat_inv_no is not null then
257 return;
258 end if;
259
260 OPEN transaction_type_cur;
261 FETCH transaction_type_cur INTO v_trans_type;
262 CLOSE transaction_type_cur;
263
264 IF NVL(v_trans_type,'N') <> 'INV' THEN
265 -- VAT invoice number should be generated only for an Invoice and not for others like cm for RMA.
266 RETURN;
267 END IF;
268
269
270 OPEN organization_cur;
271 FETCH organization_cur INTO v_organization_id, v_loc_id;
272 CLOSE organization_cur;
273
274 Open c_vat_taxes_exist;
275 Fetch c_vat_taxes_exist into v_vat_taxes_exist;
276 Close c_vat_taxes_exist;
277
278 if v_vat_taxes_exist = 1 then
279 Generate_Tax_Invoice_no(v_organization_id,v_loc_id);
280 end if;
281
282 if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
283 FND_LOG.STRING(G_LEVEL_PROCEDURE,
284 G_MODULE_NAME || l_api_name || '.END',
285 G_PKG_NAME || ': ' || l_api_name || '()-');
286 end if;
287
288 EXCEPTION
289 WHEN OTHERS THEN
290 Pv_return_code := jai_constants.unexpected_error;
291 Pv_return_message := 'Encountered an error in JAI_AR_IMPORT_SYNC_PKG.GEN_VAT_FOR_INV ' || substr(sqlerrm,1,1900);
292 END gen_vat_for_inv ;
293
294 /*
295 REM +======================================================================+
296 REM NAME update_trx_number renamed from ARU_T2
297 REM
298 REM DESCRIPTION Called from trigger JAI_TAX_PROCESSING_PKG.PROCESS_COMPLETE
299 REM
300 REM NOTES Refers to old trigger JAI_AR_RCTA_TRIGGER_PKG.ARU_T2
301 REM
302 REM +======================================================================+
303 */
304 PROCEDURE update_trx_number ( pr_old t_jai_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
305 v_trans_type Varchar2(30);
306 v_trx_number varchar2(30);
307 v_ref_line_id varchar2(30);
308
309 Cursor transaction_type_cur IS
310 Select type
311 From RA_CUST_TRX_TYPES_ALL
312 Where cust_trx_type_id = pr_new.cust_trx_type_id
313 And NVL(org_id,0) = NVL(pr_new.org_id,0);
314
315 v_currency_code gl_sets_of_books.currency_code%type;
316 --Added by Qinglei for JAI Trigger Elimination 11-May_2012 begin
317 lv_action VARCHAR2(20);
318 lv_return_message VARCHAR2(2000);
319 lv_return_code VARCHAR2(100) ;
323
320 t_jai_line_rec_old JAI_AR_TRX_LINES%ROWTYPE;
321 t_jai_line_rec_new JAI_AR_TRX_LINES%ROWTYPE;
322 le_error EXCEPTION;
324 CURSOR c_jai_ar_trx_lines(pn_customer_trx_line_id NUMBER) IS
325 SELECT *
326 FROM
327 JAI_AR_TRX_LINES
328 WHERE customer_trx_line_id = pn_customer_trx_line_id;
329 --Added by Qinglei for JAI Trigger Elimination 11-May-2012 end
330
331
332 --Added by Zhiwei for JAI Trigger elimination begin
333 ---------------------------------------------------------
334 cursor c_get_rec(cn_customer_trx_id number)
335 is
336 select *
337 from jai_ar_trxs
338 where customer_trx_id = cn_customer_trx_id;
339
340 t_rec_new jai_ar_trxs%rowtype;
341 t_rec_old jai_ar_trxs%rowtype;
342 ---------------------------------------------------------
343 --Added by Zhiwei for JAI Trigger elimination end
344 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRX_NUMBER()';
345
346 BEGIN
347 pv_return_code := jai_constants.successful ;
348
349 if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
350 FND_LOG.STRING(G_LEVEL_PROCEDURE,
351 G_MODULE_NAME || l_api_name || '.BEGIN',
352 G_PKG_NAME || ': ' || l_api_name || '()+');
353 end if;
354
355 v_trx_number := pr_new.Trx_Number;
356 v_ref_line_id := pr_new.interface_header_attribute7;
357
358
359 IF pr_new.created_from = 'RAXTRX' THEN
360 IF pr_new.CUSTOMER_TRX_ID <> pr_old.CUSTOMER_TRX_ID
361 THEN
362
363 --Added by Zhiwei for JAI Trigger elimination begin
364 ---------------------------------------------------------
365 open c_get_rec(pr_old.customer_trx_id);
366 fetch c_get_rec into t_rec_old;
367 close c_get_rec;
368 ---------------------------------------------------------
369 --Added by Zhiwei for JAI Trigger elimination end
370
371 Update JAI_AR_TRXS
372 Set Customer_Trx_ID = pr_new.Customer_Trx_ID
373 Where Customer_Trx_ID = pr_old.Customer_Trx_ID;
374
375 --Added by Zhiwei for JAI Trigger elimination begin
376 ---------------------------------------------------------
377 open c_get_rec(pr_new.customer_trx_id);
378 fetch c_get_rec into t_rec_new;
379 close c_get_rec;
380
381 if(t_rec_new.once_completed_flag = 'Y')then
382
383 lv_action := jai_constants.updating ;
384
385 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
386 pr_old => t_rec_old ,
387 pr_new => t_rec_new ,
388 pv_action => lv_action ,
389 pv_return_code => lv_return_code ,
390 pv_return_message => lv_return_message
391 );
392
393 IF lv_return_code <> jai_constants.successful then
394 RAISE le_error;
395 END IF;
396
397 end if;
398 ---------------------------------------------------------
399 --Added by Zhiwei for JAI Trigger elimination end
400
401
402
403 --Added by Qinglei for JAI Trigger Elimination 11-May-012 begin
404 /*Update JAI_AR_TRX_LINES
405 Set Customer_Trx_Id = pr_new.Customer_Trx_ID
406 Where Customer_Trx_ID = pr_old.Customer_Trx_ID;*/
407 FOR c_jai_ar_trx_lines_rec IN (SELECT customer_trx_line_id
408 FROM JAI_AR_TRX_LINES
409 WHERE customer_trx_id = pr_old.customer_trx_id)
410 LOOP
411 OPEN c_jai_ar_trx_lines(c_jai_ar_trx_lines_rec.customer_trx_line_id);
412 FETCH c_jai_ar_trx_lines into t_jai_line_rec_old;
413 CLOSE c_jai_ar_trx_lines;
414
415 Update JAI_AR_TRX_LINES
416 Set Customer_Trx_Id = pr_new.Customer_Trx_ID
417 Where Customer_Trx_Line_ID = c_jai_ar_trx_lines_rec.customer_trx_line_id;
418
419 lv_action := jai_constants.UPDATING;
420
421 OPEN c_jai_ar_trx_lines(c_jai_ar_trx_lines_rec.customer_trx_line_id);
422 FETCH c_jai_ar_trx_lines into t_jai_line_rec_new;
423 CLOSE c_jai_ar_trx_lines;
424
425 IF ( ( ( t_jai_line_rec_new.AUTO_INVOICE_FLAG <> 'Y' AND
426 t_jai_line_rec_old.AUTO_INVOICE_FLAG <> 'Y'
427 ) AND
428 (t_jai_line_rec_new.Excise_Invoice_No IS NULL) AND
429 (t_jai_line_rec_new.payment_Register IS NULL) AND
430 (t_jai_line_rec_new.Excise_Invoice_Date IS NULL)
431 ) OR
432 (t_jai_line_rec_new.Customer_Trx_Id <> t_jai_line_rec_old.Customer_Trx_Id)
433 )
434 THEN
435 JAI_AR_TAX_LINES_PKG.POPULATE_TAX_LINES_WRAPPER (
436 pr_old => t_jai_line_rec_old ,
437 pr_new => t_jai_line_rec_new ,
438 pv_action => lv_action ,
439 pv_return_code => lv_return_code ,
440 pv_return_message => lv_return_message
441 );
442 IF lv_return_code <> jai_constants.successful THEN
443 RAISE le_error;
444 END IF;
445 END IF;
446 END LOOP;
447 --Added by Qinglei for JAI Trigger Elimination 11-May-2012 end
448 END IF;
449
450 --Added by Zhiwei for JAI Trigger elimination begin
451 ---------------------------------------------------------
452 open c_get_rec(pr_new.customer_trx_id);
453 fetch c_get_rec into t_rec_old;
454 close c_get_rec;
458 Update JAI_AR_TRXS
455 ---------------------------------------------------------
456 --Added by Zhiwei for JAI Trigger elimination end
457
459 Set Trx_Number = pr_new.Trx_Number
460 Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
461
462 --Added by Zhiwei for JAI Trigger elimination begin
463 ---------------------------------------------------------
464 open c_get_rec(pr_new.customer_trx_id);
465 fetch c_get_rec into t_rec_new;
466 close c_get_rec;
467
468
469
470 if(t_rec_new.once_completed_flag = 'Y')then
471
472 lv_action := jai_constants.updating ;
473
474 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
475 pr_old => t_rec_old ,
476 pr_new => t_rec_new ,
477 pv_action => lv_action ,
478 pv_return_code => lv_return_code ,
479 pv_return_message => lv_return_message
480 );
481
482 IF lv_return_code <> jai_constants.successful then
483 RAISE le_error;
484 END IF;
485
486 end if;
487 ---------------------------------------------------------
488 --Added by Zhiwei for JAI Trigger elimination end
489
490
491
492 END IF;
493
494 OPEN transaction_type_cur;
495 FETCH transaction_type_cur INTO v_trans_type;
496 CLOSE transaction_type_cur;
497
498 --Added by Zhiwei for JAI Trigger elimination begin
499 ---------------------------------------------------------
500 open c_get_rec(pr_new.customer_trx_id);
501 fetch c_get_rec into t_rec_old;
502 close c_get_rec;
503 ---------------------------------------------------------
504 --Added by Zhiwei for JAI Trigger elimination end
505
506
507 IF NVL(v_trans_type,'N') in ('CM','DM') THEN
508
509
510 IF pr_new.created_from = 'RAXTRX' THEN
511 Update JAI_AR_TRXS
512 Set Trx_Number = pr_new.Trx_Number
513 Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
514 ELSE
515
516
517 Update JAI_AR_TRXS
518 Set Trx_Number = pr_new.Trx_Number
519 , Once_Completed_Flag = NVL(pr_new.Complete_Flag,'N')
520 Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
521 END IF;
522
523 ELSIF NVL(v_trans_type,'N') = 'INV' THEN
524 Update JAI_AR_TRXS
525 Set Trx_Number = pr_new.Trx_Number
526 Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
527 END IF;
528
529 --Added by Zhiwei for JAI Trigger elimination begin
530 ---------------------------------------------------------
531 open c_get_rec(pr_new.customer_trx_id);
532 fetch c_get_rec into t_rec_new;
533 close c_get_rec;
534
535
536
537 if(t_rec_new.once_completed_flag = 'Y')then
538
539 lv_action := jai_constants.updating ;
540
541 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
542 pr_old => t_rec_old ,
543 pr_new => t_rec_new ,
544 pv_action => lv_action ,
545 pv_return_code => lv_return_code ,
546 pv_return_message => lv_return_message
547 );
548
549 IF lv_return_code <> jai_constants.successful then
550 RAISE le_error;
551 END IF;
552
553 end if;
554 ---------------------------------------------------------
555 --Added by Zhiwei for JAI Trigger elimination end
556 if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
557 FND_LOG.STRING(G_LEVEL_PROCEDURE,
558 G_MODULE_NAME || l_api_name || '.END',
559 G_PKG_NAME || ': ' || l_api_name || '()-');
560 end if;
561
562
563 EXCEPTION
564 WHEN OTHERS THEN
565 Pv_return_code := jai_constants.unexpected_error;
566 Pv_return_message := 'Encountered an error in JAI_AR_IMPORT_SYNC_PKG.UPDATE_TRX_NUMBER ' || substr(sqlerrm,1,1900);
567 END update_trx_number ;
568
569 /*
570 REM +======================================================================+
571 REM NAME update_complete_flag renamed from ARU_T4
572 REM
573 REM DESCRIPTION Called from trigger JAI_TAX_PROCESSING_PKG.PROCESS_COMPLETE
574 REM
575 REM NOTES Refers to old trigger JAI_AR_RCTA_TRIGGER_PKG.ARU_T4
576 REM
577 REM +======================================================================+
578 */
579 PROCEDURE update_complete_flag ( pr_old t_jai_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
580 v_org_id NUMBER;
581 v_loc_id NUMBER;
582 v_reg_code VARCHAR2(30);
583 v_update_rg VARCHAR2(1);
584 v_reg_type VARCHAR2(10);
585 v_excise_paid_register VARCHAR2(10);
586 v_rg23a_type VARCHAR2(10);
587 v_rg23c_type VARCHAR2(10);
588 v_complete_flag VARCHAR2(1);
589 v_rg_flag VARCHAR2(1);
590 v_update_rg_flag VARCHAR2(1);
591
592 v_tax_amount NUMBER := 0;
593 v_rg23a_tax_amount NUMBER := 0;
594 v_rg23c_tax_amount NUMBER := 0;
595 v_other_tax_amount NUMBER := 0;
596 v_basic_ed NUMBER := 0;
597 v_additional_ed NUMBER := 0;
598 v_other_ed NUMBER := 0;
599 v_item_class VARCHAR2(10);
600 v_excise_flag VARCHAR2(1);
601 v_fin_year NUMBER;
602 v_gp_1 NUMBER := 0;
603 v_gp_2 NUMBER := 0;
607 v_invoice_no VARCHAR2(200);
604 v_rg23a_bal NUMBER := 0;
605 v_rg23c_bal NUMBER := 0;
606 v_pla_bal NUMBER := 0;
608 v_other_invoice_no NUMBER ;
609 v_rg23a_invoice_no NUMBER ;
610 v_rg23c_invoice_no NUMBER ;
611 rg23a NUMBER :=0;
612 rg23c NUMBER :=0;
613 pla NUMBER :=0;
614
615 v_parent_trx_number VARCHAR2(20);
616 v_register_balance NUMBER := 0;
617 v_rg23d_register_balance NUMBER := 0;
618 v_customer_trx_id NUMBER;
619 v_converted_rate NUMBER := 1;
620 v_ssi_unit_flag VARCHAR2(1);
621 v_trans_type VARCHAR2(30);
622 v_last_update_date DATE;
623 v_last_updated_by NUMBER;
624 v_creation_date DATE;
625 v_created_by NUMBER;
626 v_last_update_login NUMBER;
627 v_bond_tax_amount NUMBER := 0;
628 V_rg23d_tax_amount NUMBER := 0;
629 v_modvat_tax_rate NUMBER;
630 v_exempt_bal NUMBER;
631 v_matched_qty NUMBER;
632 VSQLERRM VARCHAR2(240);
633 v_trans_type_up VARCHAR2(3);
634 v_order_invoice_type_up VARCHAR2(25);
635 v_register_code_up VARCHAR2(25);
636 v_errbuf VARCHAR2(250);
637
638 v_register_id JAI_OM_OE_BOND_REG_HDRS.register_id%type;
639 v_register_exp_date JAI_OM_OE_BOND_REG_HDRS.bond_expiry_date%type;
640 v_lou_flag JAI_OM_OE_BOND_REG_HDRS.lou_flag%type;
641
642 v_trading_flag JAI_CMN_INVENTORY_ORGS.TRADING%TYPE;
643 v_update_rg23d_flag JAI_AR_TRXS.UPDATE_RG23D_FLAG%TYPE;
644
645
646 CURSOR complete_cur
647 IS
648 SELECT
649 organization_id ,
650 location_id ,
651 once_completed_flag ,
652 decode(once_completed_flag,'A','RG23A','C','RG23C','P','PLA') register_type,
653 update_rg_flag,
654 nvl(update_rg23d_flag,'N')
655 FROM
656 JAI_AR_TRXS
657 WHERE
658 customer_trx_id = v_customer_trx_id;
659
660
661
662 CURSOR REG_BALANCE_CUR(p_org_id IN NUMBER,p_loc_id IN NUMBER) IS
663 SELECT NVL(rg23a_balance,0) rg23a_balance ,NVL(rg23c_balance,0) rg23c_balance,NVL(pla_balance,0) pla_balance
664 FROM JAI_CMN_RG_BALANCES
665 WHERE organization_id = p_org_id AND location_id = p_loc_id;
666
667 CURSOR register_code_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
668 SELECT register_code
669 FROM JAI_OM_OE_BOND_REG_HDRS
670 WHERE organization_id = p_org_id AND location_id = p_loc_id AND
671 register_id IN (SELECT register_id
672 FROM JAI_OM_OE_BOND_REG_DTLS
673 WHERE order_type_id = pr_new.batch_source_id AND order_flag= 'N');
674
675 CURSOR fin_year_cur(p_org_id IN NUMBER) IS
676 SELECT MAX(A.fin_year)
677 FROM JAI_CMN_FIN_YEARS A
678 WHERE organization_id = p_org_id AND fin_active_flag = 'Y';
679
680 CURSOR tax_amount_cur IS
681 SELECT NVL(tax_amount,0) tax_amount
682 FROM JAI_AR_TRXS
683 WHERE customer_trx_id = v_customer_trx_id;
684
685 CURSOR preference_reg_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
686 SELECT pref_rg23a , pref_rg23c , pref_pla
687 FROM JAI_CMN_INVENTORY_ORGS
688 WHERE organization_id = p_org_id AND
689 location_id = p_loc_id;
690
691 CURSOR item_class_cur(P_ORG_ID IN NUMBER, P_Item_id IN NUMBER) IS
692 SELECT item_class, excise_flag
693 FROM JAI_INV_ITM_SETUPS
694 WHERE inventory_item_id = P_Item_Id AND
695 ORGANIZATION_ID = P_ORG_ID;
696
697 CURSOR organization_cur IS
698 SELECT organization_id,location_id
699 FROM JAI_AR_TRXS
700 WHERE trx_number = v_parent_trx_number;
701
705 WHERE transaction_id = (SELECT MAX(A.transaction_id)
702 CURSOR register_balance_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
703 SELECT NVL(register_balance,0) register_balance
704 FROM JAI_OM_OE_BOND_TRXS
706 FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
707 WHERE A.register_id = B.register_id
708 AND B.organization_id = p_org_id AND B.location_id = p_loc_id);
709
710 CURSOR register_balance_cur1(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
711 SELECT NVL(rg23d_register_balance,0) rg23d_register_balance
712 FROM JAI_OM_OE_BOND_TRXS
713 WHERE transaction_id = (SELECT MAX(A.transaction_id)
714 FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
715 WHERE A.register_id = B.register_id
716 AND B.organization_id = p_org_id AND B.location_id = p_loc_id);
717
718 CURSOR line_cur IS
719 SELECT customer_trx_line_id, inventory_item_id, quantity,line_number,
720 excise_exempt_type, assessable_value
721 FROM JAI_AR_TRX_LINES
722 WHERE customer_trx_id = v_customer_trx_id
723 ORDER BY customer_trx_line_id;
724
725 CURSOR matched_qty_cur (p_customer_trx_line_id NUMBER) IS
726 SELECT SUM(quantity_applied)
727 FROM JAI_CMN_MATCH_RECEIPTS
728 WHERE ref_line_id = p_customer_trx_line_id;
729
730 CURSOR excise_cal_cur(p_line_id IN NUMBER, p_inventory_item_id IN NUMBER, p_org_id IN NUMBER) IS
731 SELECT
732 A.tax_id,
733 A.tax_rate t_rate,
734 A.tax_amount tax_amt,
735 A.func_tax_amount func_amt,
736 b.tax_type t_type,
737 b.stform_type,
738 A.tax_line_no
739 FROM JAI_AR_TRX_TAX_LINES A ,
740 JAI_CMN_TAXES_ALL B,
741 JAI_INV_ITM_SETUPS C
742 WHERE link_to_cust_trx_line_id = p_line_id
743 AND b.tax_type IN --('Excise','Addl. Excise','Other Excise')
744 (jai_constants.tax_type_excise,jai_constants.tax_type_exc_additional,jai_constants.tax_type_exc_other)
745 AND A.tax_id = b.tax_id
746 AND c.inventory_item_id = p_inventory_item_id
747 AND c.organization_id = p_org_id
748 --AND c.item_class IN ('RMIN','RMEX','CGEX','CGIN','CCEX','CCIN','FGIN','FGEX')
749 AND c.item_class IN ( jai_constants.item_class_rmin, jai_constants.item_class_rmex,
750 jai_constants.item_class_cgex, jai_constants.item_class_cgin,
751 jai_constants.item_class_ccex, jai_constants.item_class_ccin,
752 jai_constants.item_class_fgin, jai_constants.item_class_fgex
753 )
754 ORDER BY 1;
755
756 CURSOR ssi_unit_flag_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
757 SELECT ssi_unit_flag, nvl(trading,'N')
758 FROM JAI_CMN_INVENTORY_ORGS
759 WHERE organization_id = p_org_id AND
760 location_id = p_loc_id;
761
762 CURSOR transaction_type_cur IS
763 SELECT TYPE
764 FROM RA_CUST_TRX_TYPES_ALL
765 WHERE cust_trx_type_id = pr_new.cust_trx_type_id
766 AND (org_id = pr_new.org_id
767 OR
768 (org_id is null and pr_new.org_id is null)) ;
769
770
771
772 CURSOR Batch_Source_Name_Cur IS
773 SELECT name
774 FROM Ra_Batch_Sources_All
775 WHERE batch_source_id = pr_new.batch_source_id
776 AND (org_id = pr_new.org_id
777 OR
778 ( org_id is null AND pr_new.org_id is null));
779
780
781
785 FROM JAI_CMN_RG_EXC_INV_NOS
782 CURSOR Def_Excise_Invoice_Cur(p_organization_id IN NUMBER, p_location_id IN NUMBER, p_fin_year IN NUMBER,
783 p_batch_name IN VARCHAR2, p_register_code IN VARCHAR2) IS
784 SELECT start_number, end_number, jump_by, prefix
786 WHERE organization_id = p_organization_id
787 AND location_id = p_location_id
788 AND fin_year = p_fin_year
789 AND transaction_type IN ( 'I','DOM','EXP')
790 AND order_invoice_type = p_batch_name
791 AND register_code = p_register_code ;
792
793 CURSOR excise_invoice_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER, p_fin_year IN NUMBER) IS
794 SELECT NVL(MAX(GP1),0),NVL(MAX(GP2),0)
795 FROM JAI_CMN_RG_EXC_INV_NOS
796 WHERE organization_id = p_org_id
797 AND location_id = p_loc_id
798 AND fin_year = p_fin_year
799 AND transaction_type IS NULL
800 AND order_invoice_type IS NULL
801 AND register_code IS NULL;
802
803 CURSOR Register_Code_Meaning_Cur(p_register_code IN VARCHAR2,cp_register_type ja_lookups.lookup_type%type ) IS
804 SELECT meaning
805 FROM ja_lookups
806 WHERE lookup_code = p_register_code
807 AND lookup_type = cp_register_type; /*'JAI_REGISTER_TYPE'; Ramananda for removal of SQL LITERALs */
808
809
810 CURSOR for_modvat_percentage(v_org_id NUMBER, v_location_id NUMBER) IS
811 SELECT MODVAT_REVERSE_PERCENT
812 FROM JAI_CMN_INVENTORY_ORGS
813 WHERE organization_id = v_org_id
814 AND (location_id = v_location_id
815 OR
816 (location_id is NULL and v_location_id is NULL));
817
818 CURSOR for_modvat_tax_rate(p_cust_trx_line_id NUMBER) IS
819 SELECT A.tax_rate
820 FROM JAI_AR_TRX_TAX_LINES A, JAI_CMN_TAXES_ALL b
821 WHERE A.tax_id = b.tax_id
822 AND A.link_to_cust_trx_line_id = p_cust_trx_line_id
823 AND b.tax_type = jai_constants.tax_type_modvat_recovery ; --'Modvat Recovery';
824
825
826 v_start_number NUMBER;
827 v_end_number NUMBER;
828 v_jump_by NUMBER;
829 v_order_invoice_type VARCHAR2(50);
830 v_prefix VARCHAR2(50);
831 v_meaning VARCHAR2(80);
832 v_set_of_books_id NUMBER;
833
834 CURSOR ec_code_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
835 SELECT A.Organization_Id, A.Location_Id
836 FROM JAI_CMN_INVENTORY_ORGS A
837 WHERE A.Ec_Code IN (SELECT B.Ec_Code
838 FROM JAI_CMN_INVENTORY_ORGS B
839 WHERE B.Organization_Id = p_organization_id
840 AND B.Location_Id = p_location_id);
841
842
843 CURSOR c_total_Excise_amt IS
844 SELECT nvl(sum(jrtl.func_tax_amount),0)
845 FROM JAI_AR_TRXS jtrx,
846 JAI_AR_TRX_LINES jtl,
847 JAI_AR_TRX_TAX_LINES jrtl,
848 JAI_CMN_TAXES_ALL jtc ,
849 JAI_INV_ITM_SETUPS jmtl
850 WHERE jrtl.tax_id = jtc.tax_id
851 AND jtrx.customer_trx_id = jtl.customer_Trx_id
852 AND jrtl.link_to_cust_trx_line_id = jtl.customer_trx_line_id
853 AND jtl.inventory_item_id = jmtl.inventory_item_id
854 AND jtrx.organization_id = jmtl.organization_id
855 --AND jmtl.item_class in ('RMIN','RMEX','CGEX','CGIN','FGIN','FGEX','CCIN','CCEX')
856 AND jmtl.item_class IN ( jai_constants.item_class_rmin, jai_constants.item_class_rmex,
857 jai_constants.item_class_cgex, jai_constants.item_class_cgin,
858 jai_constants.item_class_ccex, jai_constants.item_class_ccin,
859 jai_constants.item_class_fgin, jai_constants.item_class_fgex
860 )
861 AND jtc.tax_type like '%Excise%'
862 AND jtl.customer_trx_id = pr_new.customer_trx_id
863 AND jtrx.customer_trx_id = pr_new.customer_trx_id;
864
865 v_total_excise_amt NUMBER :=0;
866
867 CURSOR c_cess_amount is
868 SELECT NVL(SUM(jrctl.func_tax_amount),0) tax_amount
869 FROM JAI_AR_TRX_TAX_LINES jrctl ,
870 JAI_CMN_TAXES_ALL jtc
871 WHERE jtc.tax_id = jrctl.tax_id
872 AND link_to_cust_trx_line_id IN
873 (SELECT customer_trx_line_id
874 FROM JAI_AR_TRX_LINES
875 WHERE customer_trx_id = pr_new.customer_trx_id
876 )
877 AND upper(jtc.tax_type) IN (upper(jai_constants.tax_type_cvd_edu_cess), upper(jai_constants.tax_type_exc_edu_cess));
878
879
880 /*
881 ||
882 || Check whether the JAI_AR_TRX_INS_LINES_T table still has the row corresponding to the current
883 || customer_trx_id
884 */
885 CURSOR cur_chk_temp_lines_exist( cp_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE )
886 IS
887 SELECT '1'
888 FROM Fnd_Concurrent_Requests FCR
889 WHERE FCR.argument1 = to_char(cp_customer_trx_id)
890 and fcr.status_code<> 'C'
891 and fcr.phase_code<>'C'
892 AND fcr.request_id IN (Select nvl(Max(Request_ID),-1)
893 From Fnd_Concurrent_Programs FCP,
894 Fnd_Application FA ,
895 Fnd_Concurrent_Requests FCR
896 Where FCR.Program_Application_ID = FA.Application_ID
897 AND FCR.Concurrent_Program_ID = FCP.Concurrent_Program_ID
898 AND FA.Application_ID = FCP.Application_ID
899 AND Concurrent_Program_Name = 'JAILINEGL'
900 AND FA.Application_Short_Name = 'JA'
901 AND FCR.argument1 = To_Char(cp_customer_trx_id) );
902
903
904
905 CURSOR c_vat_invoice_cur
906 IS
907 SELECT
908 vat_invoice_no
909 FROM JAI_AR_TRXS
913 IS
910 WHERE customer_trx_id = pr_new.customer_trx_id;
911
912 CURSOR cur_vat_taxes_exist
914 SELECT
915 regime_id ,
916 regime_code
917 FROM
918 JAI_AR_TRX_TAX_LINES jcttl,
919 JAI_AR_TRX_LINES jctl,
920 JAI_CMN_TAXES_ALL jtc ,
921 jai_regime_tax_types_v jrttv
922 WHERE
923 jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
924 jctl.customer_trx_id = pr_new.customer_trx_id AND
925 jcttl.tax_id = jtc.tax_id AND
926 jtc.tax_type = jrttv.tax_type AND
927 regime_code = jai_constants.vat_regime AND
928 jtc.org_id = pr_new.org_id ;
929
930 /*
931 ||
932 || Check if only 'VAT REVERSAL' tax type is present in ja_in_ra_cust_trx_tax_lines
933 */
934 CURSOR c_chk_vat_reversal (cp_tax_type jai_cmn_taxes_all.tax_type%TYPE )
935 IS
936 SELECT
937 1
938 FROM
939 JAI_AR_TRX_TAX_LINES jcttl,
940 JAI_AR_TRX_LINES jctl,
941 JAI_CMN_TAXES_ALL jtc
942 WHERE
943 jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
944 jctl.customer_trx_id = pr_new.customer_trx_id AND
945 jcttl.tax_id = jtc.tax_id AND
946 jtc.org_id = pr_new.org_id AND
947 jtc.tax_type = cp_tax_type ;
948
949 /*
950 || Retrieve the regime_id which is of regime code 'VAT'
951 */
952 CURSOR c_get_regime_id
953 IS
954 SELECT
955 regime_id
956 FROM
957 jai_regime_tax_types_v
958 WHERE
959 regime_code = jai_constants.vat_regime
960 AND rownum = 1 ;
961
962 ln_vat_reversal_exists NUMBER ;
963 lv_vat_reversal VARCHAR2(100);
964
965
966
967
968 CURSOR cur_get_same_inv_no ( cp_organization_id JAI_AR_TRXS.ORGANIZATION_ID%TYPE ,
969 cp_location_id JAI_AR_TRXS.LOCATION_ID%TYPE
970 )
971 IS
972 SELECT
973 nvl(attribute_value ,'N') attribute_value
974 FROM
975 JAI_RGM_ORG_REGNS_V
976 WHERE
977 regime_code = jai_constants.vat_regime AND
978 attribute_type_code = jai_constants.regn_type_others AND /*'OTHERS' AND */
979 attribute_code = jai_constants.attr_code_same_inv_no AND /*'SAME_INVOICE_NO' AND */
980 organization_id = cp_organization_id AND
981 location_id = cp_location_id;
982
983 CURSOR cur_get_exc_inv_no
984 IS
985 SELECT
986 excise_invoice_no
987 FROM
988 JAI_AR_TRX_LINES
989 WHERE
990 customer_trx_id = pr_new.customer_trx_id ;
991
992
993 CURSOR cur_get_gl_date(cp_account_class ra_cust_trx_line_gl_dist_all.account_class%type)
994 IS
995 SELECT
996 gl_date
997 FROM
998 ra_cust_trx_line_gl_dist_all
999 WHERE
1000 customer_trx_id = pr_new.customer_trx_id AND
1001 account_class = cp_account_class AND
1002 latest_rec_flag = 'Y';
1003
1004
1005
1006 ln_exists NUMBER ;
1007 ln_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;
1008 lv_process_flag VARCHAR2(2);
1009 lv_process_message VARCHAR2(1996);
1010 lv_register_type VARCHAR2(5);
1011 lv_rg23a_cess_avlbl VARCHAR2(10);
1012 lv_rg23c_cess_avlbl VARCHAR2(10);
1013 lv_pla_cess_avlbl VARCHAR2(10);
1014 lv_vat_invoice_number JAI_AR_TRXS.VAT_INVOICE_NO%TYPE;
1015 lv_vat_taxes_exist VARCHAR2(1);
1016 lv_vat_no_same_exc_no JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE;
1017 ld_gl_date RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE%TYPE;
1018 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE;
1019 ln_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
1020
1021
1022
1023
1024 lv_doc_type_class varchar2(2);
1025
1026 lv_enable_gst_flag VARCHAR2(1);
1027 ln_cgst_regime_id NUMBER;
1028 ln_sgst_regime_id NUMBER;
1029 lv_cgst_regime_code JAI_RGM_ORG_REGNS_V.REGIME_CODE%TYPE;
1030 lv_sgst_regime_code JAI_RGM_ORG_REGNS_V.REGIME_CODE%TYPE;
1031
1032
1033
1034 CURSOR cur_chk_gst_exists ( cp_regime_code JAI_RGM_ORG_REGNS_V.REGIME_CODE%TYPE)
1035 IS
1036 SELECT regime_id ,
1037 regime_code
1038 FROM
1039 JAI_AR_TRX_TAX_LINES jcttl,
1040 JAI_AR_TRX_LINES jctl,
1041 JAI_CMN_TAXES_ALL jtc ,
1042 jai_regime_tax_types_v jrttv
1043 WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
1044 AND jctl.customer_trx_id = pr_new.customer_trx_id
1045 AND jcttl.tax_id = jtc.tax_id
1046 AND jtc.tax_type = jrttv.tax_type
1047 AND regime_code = cp_regime_code
1048 AND jtc.org_id = pr_new.org_id ;
1049
1050 CURSOR cur_get_gst_regime_info (cp_organization_id JAI_RGM_ORG_REGNS_V.ORGANIZATION_ID%TYPE ,
1054 IS
1051 cp_location_id JAI_RGM_ORG_REGNS_V.LOCATION_ID%TYPE,
1052 cp_regime_code JAI_RGM_ORG_REGNS_V.REGIME_CODE%TYPE
1053 )
1055 SELECT regime_id,
1056 attribute_value
1057 FROM JAI_RGM_ORG_REGNS_V orrg
1058 WHERE orrg.organization_id = cp_organization_id
1059 AND orrg.location_id = cp_location_id
1060 AND attribute_type_code = jai_constants.rgm_attr_type_code_primary
1061 AND attribute_code = jai_constants.attr_code_regn_no
1062 AND regime_code = cp_regime_code;
1063
1064
1065
1066 --Added by Qinglei for JAI Trigger Elimination 11-May_2012 begin
1067 lv_action VARCHAR2(20);
1068 lv_return_message VARCHAR2(2000);
1069 lv_return_code VARCHAR2(100) ;
1070 t_jai_line_rec_old JAI_AR_TRX_LINES%ROWTYPE;
1071 t_jai_line_rec_new JAI_AR_TRX_LINES%ROWTYPE;
1072 le_error EXCEPTION;
1073
1074 CURSOR c_jai_ar_trx_lines(pn_customer_trx_line_id NUMBER) IS
1075 SELECT *
1076 FROM
1077 JAI_AR_TRX_LINES
1078 WHERE customer_trx_line_id = pn_customer_trx_line_id;
1079 --Added by Qinglei for JAI Trigger Elimination 11-May-2012 end
1080
1081 --Added by Zhiwei for JAI Trigger elimination begin
1082 ---------------------------------------------------------
1083 cursor c_get_rec(cn_customer_trx_id number)
1084 is
1085 select *
1086 from jai_ar_trxs
1087 where customer_trx_id = cn_customer_trx_id;
1088
1089 t_rec_new jai_ar_trxs%rowtype;
1090 t_rec_old jai_ar_trxs%rowtype;
1091
1092 ---------------------------------------------------------
1093 --Added by Zhiwei for JAI Trigger elimination end
1094
1095 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_COMPLETE_FLAG()';
1096
1097
1098
1099
1100 BEGIN
1101
1102 if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
1103 FND_LOG.STRING(G_LEVEL_PROCEDURE,
1104 G_MODULE_NAME || l_api_name || '.BEGIN',
1105 G_PKG_NAME || ': ' || l_api_name || '()+');
1106 end if;
1107
1108
1109
1110 pv_return_code := jai_constants.successful ;
1111
1112
1113 v_complete_flag := 'N';
1114 v_rg_flag := 'N';
1115 v_update_rg_flag := 'N';
1116 v_item_class := 'N';
1117 v_parent_trx_number := pr_new.recurred_from_trx_number;
1118 v_customer_trx_id := pr_old.customer_trx_id;
1119 v_last_update_date := pr_new.last_update_date;
1120 v_last_updated_by := pr_new.last_updated_by;
1121 v_creation_date := pr_new.creation_date;
1122 v_created_by := pr_new.created_by;
1123 v_last_update_login := pr_new.last_update_login;
1124 v_set_of_books_id := pr_new.set_of_books_id;
1125 lv_vat_no_same_exc_no := 'N';
1126
1127
1128
1129 OPEN transaction_type_cur;
1130 FETCH transaction_type_cur INTO v_trans_type;
1131 CLOSE transaction_type_cur;
1132
1133
1134 OPEN Complete_Cur;
1135 FETCH Complete_Cur INTO v_org_id, v_loc_id,v_complete_flag,v_reg_type, v_update_rg_flag,v_update_rg23d_flag;
1136 CLOSE Complete_Cur;
1137
1138 IF pr_new.COMPLETE_FLAG <> nvl(pr_old.COMPLETE_FLAG,'N') THEN
1139 --IF pr_new.COMPLETE_FLAG <> pr_old.COMPLETE_FLAG THEN
1140
1141
1142
1143 v_rg_flag := v_update_rg_flag;
1144
1145 IF NVL(v_complete_flag,'N') = 'Y' THEN
1146 RETURN;
1147 END IF;
1148
1149
1150 IF NVL(v_trans_type,'N') <> 'INV' THEN
1151
1152
1153 --Added by Zhiwei for JAI Trigger elimination begin
1154 ---------------------------------------------------------
1155 open c_get_rec(pr_new.customer_trx_id);
1156 fetch c_get_rec into t_rec_old;
1157 close c_get_rec;
1158 ---------------------------------------------------------
1159 --Added by Zhiwei for JAI Trigger elimination end
1160
1161
1162 UPDATE JAI_AR_TRXS
1163 SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
1164 ,complete_flag = pr_new.COMPLETE_FLAG --Added by Zhiwei for Trigger Elimination for Import AR
1165 WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
1166
1167 --Added by Zhiwei for JAI Trigger elimination begin
1168 ---------------------------------------------------------
1169 open c_get_rec(pr_new.customer_trx_id);
1170 fetch c_get_rec into t_rec_new;
1171 close c_get_rec;
1172
1173 if(t_rec_new.once_completed_flag = 'Y')then
1174
1175 lv_action := jai_constants.updating ;
1176
1177 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
1178 pr_old => t_rec_old ,
1179 pr_new => t_rec_new ,
1180 pv_action => lv_action ,
1181 pv_return_code => lv_return_code ,
1182 pv_return_message => lv_return_message
1183 );
1184
1185 IF lv_return_code <> jai_constants.successful then
1186 RAISE le_error;
1187 END IF;
1188
1189 end if;
1190 ---------------------------------------------------------
1191 --Added by Zhiwei for JAI Trigger elimination end
1192
1193
1194
1195 RETURN;
1196 END IF;
1197 IF pr_new.created_from = 'RAXTRX' THEN
1198
1199 --Added by Zhiwei for JAI Trigger elimination begin
1203 close c_get_rec;
1200 ---------------------------------------------------------
1201 open c_get_rec(pr_new.customer_trx_id);
1202 fetch c_get_rec into t_rec_old;
1204 ---------------------------------------------------------
1205 --Added by Zhiwei for JAI Trigger elimination end
1206
1207 UPDATE JAI_AR_TRXS
1208 SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
1209 ,complete_flag = pr_new.complete_flag --Added by Zhiwei for ZX integration
1210 --,TRX_DATE = pr_new.trx_date --Added by Zhiwei for ZX integration
1211 WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
1212
1213 --Added by Zhiwei for JAI Trigger elimination begin
1214 ---------------------------------------------------------
1215 open c_get_rec(pr_new.customer_trx_id);
1216 fetch c_get_rec into t_rec_new;
1217 close c_get_rec;
1218
1219 if(t_rec_new.once_completed_flag = 'Y')then
1220
1221 lv_action := jai_constants.updating ;
1222
1223 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
1224 pr_old => t_rec_old ,
1225 pr_new => t_rec_new ,
1226 pv_action => lv_action ,
1227 pv_return_code => lv_return_code ,
1228 pv_return_message => lv_return_message
1229 );
1230
1231 IF lv_return_code <> jai_constants.successful then
1232 RAISE le_error;
1233 END IF;
1234
1235 end if;
1236 ---------------------------------------------------------
1237 --Added by Zhiwei for JAI Trigger elimination end
1238
1239
1240 ELSE
1241
1242 IF NVL(v_org_id, 999999) = 999999 THEN
1243
1244 IF v_parent_trx_number IS NULL THEN
1245 RETURN;
1246 ELSE
1247 OPEN organization_cur;
1248 FETCH organization_cur INTO v_org_id, v_loc_id;
1249 CLOSE organization_cur;
1250 v_rg_flag := 'Y';
1251 END IF;
1252 END IF;
1253 IF NVL(v_org_id, 999999) = 999999 THEN
1254
1255 RETURN;
1256 END IF;
1257
1258 jai_cmn_bond_register_pkg.GET_REGISTER_ID (v_org_id,
1259 v_loc_id,
1260 NVL(pr_new.batch_source_id,0),
1261 'N',
1262 v_register_id ,
1263 v_reg_code
1264 );
1265
1266
1267 OPEN register_code_meaning_cur(v_reg_code, 'JAI_REGISTER_TYPE');
1268 FETCH register_code_meaning_cur INTO v_meaning;
1269 CLOSE register_code_meaning_cur;
1270 OPEN fin_year_cur(v_org_id);
1271 FETCH fin_year_cur INTO v_fin_year;
1272 CLOSE fin_year_cur;
1273 OPEN Batch_Source_Name_Cur;
1274 FETCH Batch_Source_Name_Cur INTO v_order_invoice_type;
1275 CLOSE Batch_Source_Name_Cur;
1276
1277
1278
1279
1280 IF v_reg_code IN ('DOMESTIC_EXCISE','EXPORT_EXCISE','DOM_WITHOUT_EXCISE','BOND_REG') THEN
1281 v_rg_flag := 'Y';
1282
1283 ELSIF upper(v_reg_code) IN ('23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE',
1284 '23D_DOM_WITHOUT_EXCISE','23D_EXPORT_WITHOUT_EXCISE')THEN
1285 v_rg_flag := 'N';
1286 END IF;
1287
1288 v_update_rg_flag := 'Y';
1289 -- setting the value to 'Y' because the update_rg_flag has to only impact
1290 -- amount registers and not quantity registers and excise invoice generation.
1291
1292 OPEN REG_BALANCE_CUR(v_org_id, v_loc_id);
1293 FETCH REG_BALANCE_CUR INTO v_rg23a_bal,v_rg23c_bal,v_pla_bal;
1294 CLOSE REG_BALANCE_CUR;
1295 OPEN ssi_unit_flag_cur(v_org_id, v_loc_id);
1296 FETCH ssi_unit_flag_cur INTO v_ssi_unit_flag, v_trading_flag;
1297 CLOSE ssi_unit_flag_cur;
1298
1299 /*
1300 ||
1301 ||
1302 ||The complete flag statuses should be 'A','P','C','N'
1303 */
1304 IF NVL(v_complete_flag,'N') IN ('N','A','C','P') AND
1305 (v_rg_flag = 'Y' OR v_update_rg_flag = 'Y') AND
1306 v_reg_code IS NOT NULL
1307 THEN
1308
1309 FOR Line_Rec IN Line_Cur LOOP
1310 FOR excise_cal_rec IN excise_cal_cur(Line_Rec.customer_trx_line_id, Line_Rec.Inventory_Item_ID, v_org_id) LOOP
1311 IF excise_cal_rec.t_type IN ('Excise') THEN
1312 v_basic_ed := NVL(v_basic_ed,0) + NVL(excise_cal_rec.func_amt,0);
1313 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
1314 v_additional_ed := NVL(v_additional_ed,0) + NVL(excise_cal_rec.func_amt,0);
1315 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
1316 v_other_ed := NVL(v_other_ed,0) + NVL(excise_cal_rec.func_amt,0);
1317 END IF;
1318 END LOOP;
1319 v_tax_amount := NVL(v_basic_ed,0) + NVL(v_additional_ed,0) + NVL(v_other_ed,0);
1320 IF v_reg_code IN ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
1321 OPEN item_class_cur(V_ORG_ID,Line_Rec.Inventory_Item_Id);
1322 FETCH item_class_cur INTO v_item_class, v_excise_flag;
1323 CLOSE item_class_cur;
1324
1325
1326 IF NVL(v_excise_flag,'N') = 'Y' THEN
1327 IF NVL(v_ssi_unit_flag,'N') = 'N'
1328 AND NVL(line_rec.excise_exempt_type, '@@@') NOT IN ('CT2', 'EXCISE_EXEMPT_CERT','CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH' )
1329 THEN
1333 v_rg23a_tax_amount := NVL(v_rg23a_tax_amount,0) + NVL(v_tax_amount,0);
1330 IF v_item_class IN ('CGEX','CGIN') THEN
1331 v_rg23c_tax_amount := NVL(v_rg23c_tax_amount,0) + NVL(v_tax_amount,0);
1332 ELSIF v_item_class IN ('RMIN','RMEX') THEN
1334 ELSIF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') THEN
1335 v_other_tax_amount := NVL(v_other_tax_amount,0) + NVL(v_tax_amount,0);
1336
1337 ELSIF NVL(v_ssi_unit_flag,'N') = 'N' AND
1338 NVL(line_rec.excise_exempt_type, '@@@') IN ('CT2', 'EXCISE_EXEMPT_CERT',
1339 'CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH' ) THEN
1340 IF v_item_class NOT IN ('OTIN', 'OTEX') THEN
1341 IF line_rec.excise_exempt_type IN ('CT2 - OTHERS', 'Excise Exempted OTHERS' ) THEN
1342 OPEN for_modvat_tax_rate(line_rec.customer_trx_line_id);
1343 FETCH for_modvat_tax_rate INTO v_modvat_tax_rate;
1344 CLOSE for_modvat_tax_rate;
1345 ELSE
1346 OPEN for_modvat_percentage(v_org_id, v_loc_id);
1347 FETCH for_modvat_percentage INTO v_modvat_tax_rate;
1348 CLOSE for_modvat_percentage;
1349 END IF;
1350 v_exempt_bal := (NVL(v_exempt_bal, 0) + line_rec.quantity * line_rec.assessable_value * NVL(v_modvat_tax_rate,0))/100;
1351 IF v_exempt_bal > v_rg23a_bal THEN
1352 pv_return_code := jai_constants.expected_error ;
1353 pv_return_message := 'Register RG23A PART II Balance -> '||
1354 TO_CHAR(v_rg23a_bal ) || ' IS less than the Modvat Amount ->' ||
1355 TO_CHAR(v_exempt_bal) ;
1356 return ;
1357 END IF;
1358 END IF;
1359
1360 END IF;
1361 END IF; -- SSI UNIT FLAG
1362 END IF; -- EXCISE INVOICE FLAG
1363 ELSIF v_reg_code IN ('BOND_REG')
1364 THEN
1365
1366 jai_cmn_bond_register_pkg.GET_REGISTER_DETAILS(v_register_id,
1367 v_register_balance,
1368 v_register_exp_date,
1369 v_lou_flag
1370 );
1371
1372 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (pr_new.set_of_books_id ,pr_new.invoice_currency_code ,
1373 pr_new.exchange_date ,pr_new.exchange_rate_type, pr_new.exchange_rate);
1374 v_bond_tax_amount := NVL(v_tax_amount,0) + NVL(v_bond_tax_amount,0);
1375
1376
1377 IF (v_register_balance < v_bond_tax_amount )
1378 AND
1379 ( NVL(v_lou_flag,'N') = 'N')
1380 THEN
1381 pv_return_code := jai_constants.expected_error ;
1382 pv_return_message := 'Bonded Register Has Balance -> ' || TO_CHAR(v_register_balance)
1383 || ' ,which IS less than Excisable Amount -> ' || TO_CHAR(v_bond_tax_amount) ;
1384 return ;
1385 END IF;
1386
1387 IF (nvl(v_register_exp_date,sysdate) < Sysdate ) THEN
1388 pv_return_code := jai_constants.expected_error ;
1389 pv_return_message := 'Validity Date of the Bond Register has expired' ;
1390 return ;
1391 END IF ;
1392 ELSIF v_reg_code IN ('23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE','23D_DOM_WITHOUT_EXCISE','23D_EXPORT_WITHOUT_EXCISE')
1393 THEN
1394 IF v_trading_flag = 'Y' AND v_update_rg23d_flag = 'Y'
1395 THEN
1396
1397 if line_rec.inventory_item_id is not null then
1398 OPEN matched_qty_cur(line_rec.customer_trx_line_id);
1399 FETCH matched_qty_cur INTO v_matched_qty;
1400 CLOSE matched_qty_cur;
1401 IF NVL(v_matched_qty,0)<> NVL(line_rec.quantity,0)
1402 THEN
1403 pv_return_code := jai_constants.expected_error ;
1404 pv_return_message := 'Matched Quantity -> ' || TO_CHAR(v_matched_qty)
1405 || ' , IS less than Invoiced Quantity -> ' || TO_CHAR(line_rec.quantity)
1406 || ' ,FOR line NUMBER -> ' || TO_CHAR(line_rec.line_number) ; return ;
1407 EXIT;
1408 END IF;
1409 END IF;
1410
1411
1412 IF v_reg_code = '23D_EXPORT_WITHOUT_EXCISE'
1413 THEN
1414 v_rg23d_tax_amount := NVL(v_tax_amount,0) + NVL(v_rg23d_tax_amount,0);
1415 IF NVL(v_rg23d_register_balance,0) < NVL(v_rg23d_tax_amount,0)
1416 and (NVL(v_lou_flag,'N') = 'N')
1417 THEN
1418 pv_return_code := jai_constants.expected_error ;
1419 pv_return_message := 'RG23D Bonded Register Has Balance -> ' || TO_CHAR(v_rg23d_register_balance)
1420 || ' ,which IS less than Excisable Amount -> ' || TO_CHAR(v_rg23d_tax_amount) ;
1421 return ;
1422 END IF;
1423
1424
1425 IF (v_register_exp_date > Sysdate ) THEN
1426 pv_return_code := jai_constants.expected_error ;
1427 pv_return_message := 'Validity Date of the Bond Register has expired' ;
1428 return ;
1429 END IF;
1430 END IF;
1431 END IF;
1432 END IF;
1436 v_other_ed := 0;
1433 END LOOP;
1434 v_basic_Ed := 0;
1435 v_additional_ed := 0;
1437 v_tax_amount := 0;
1438 v_other_tax_amount := 0;
1439 v_rg23a_tax_amount := 0;
1440 v_rg23c_tax_amount := 0;
1441 v_rg23d_tax_Amount := 0;
1442
1443 ------------------------------start of update loop------------------------
1444
1445 FOR Line_Rec IN Line_Cur LOOP
1446
1447
1448
1449
1450
1451 Open item_class_cur(v_org_id,line_rec.Inventory_item_id);
1452 fetch item_class_cur into v_item_class , v_excise_flag;
1453 close item_class_cur;
1454
1455 IF NVL(v_excise_flag,'N') = 'Y' THEN
1456 IF v_invoice_no is Null THEN
1457 jai_cmn_setup_pkg.generate_excise_invoice_no(v_org_id,v_loc_id,'I',pr_new.batch_source_id, v_fin_year, v_invoice_no , v_errbuf);
1458 END IF;
1459
1460 IF v_errbuf is not null THEN
1461
1462 pv_return_code := jai_constants.expected_error ;
1463 pv_return_message := 'Error During Excise Invoice Generation ! ' || v_errbuf ;
1464 return ;
1465 END IF;
1466
1467 IF NVL(v_item_class,'~') not in ('OTIN') THEN
1468 --Added by Qinglei for JAI Trigger Elimination 11-May-2012 begin
1469 OPEN c_jai_ar_trx_lines(LINE_REC.customer_trx_line_id);
1470 FETCH c_jai_ar_trx_lines INTO t_jai_line_rec_old;
1471 CLOSE c_jai_ar_trx_lines;
1472
1473 UPDATE JAI_AR_TRX_LINES
1474 SET EXCISE_INVOICE_NO = v_invoice_no ,
1475 EXCISE_INVOICE_DATE = SYSDATE
1476 WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
1477 INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
1478 CUSTOMER_TRX_ID = v_customer_trx_id;
1479
1480 lv_action := jai_constants.UPDATING;
1481
1482 OPEN c_jai_ar_trx_lines(LINE_REC.customer_trx_line_id);
1483 FETCH c_jai_ar_trx_lines INTO t_jai_line_rec_new;
1484 CLOSE c_jai_ar_trx_lines;
1485
1486 IF ( ( ( t_jai_line_rec_new.AUTO_INVOICE_FLAG <> 'Y' AND
1487 t_jai_line_rec_old.AUTO_INVOICE_FLAG <> 'Y'
1488 ) AND
1489 (t_jai_line_rec_new.Excise_Invoice_No IS NULL) AND
1490 (t_jai_line_rec_new.payment_Register IS NULL) AND
1491 (t_jai_line_rec_new.Excise_Invoice_Date IS NULL)
1492 ) OR
1493 (t_jai_line_rec_new.Customer_Trx_Id <> t_jai_line_rec_old.Customer_Trx_Id)
1494 )
1495 THEN
1496 JAI_AR_TAX_LINES_PKG.POPULATE_TAX_LINES_WRAPPER (
1497 pr_old => t_jai_line_rec_old,
1498 pr_new => t_jai_line_rec_new,
1499 pv_action => lv_action,
1500 pv_return_code => lv_return_code,
1501 pv_return_message => lv_return_message
1502 );
1503 IF lv_return_code <> jai_constants.successful then
1504 RAISE le_error;
1505 END IF;
1506 END IF;
1507 --Added by Qinglei for JAI Trigger Elimination 11-May-2012 end
1508 END IF;
1509 END IF;
1510 END LOOP;
1511 ----------------end of excise no update loop--------------------
1512
1513
1514 open c_total_Excise_amt;
1515 fetch c_total_Excise_amt into v_total_excise_amt;
1516 close c_total_Excise_amt;
1517
1518
1519
1520
1521 open c_cess_amount;
1522 fetch c_cess_amount into ln_Cess_amount;
1523 close c_cess_amount;
1524
1525 lv_register_type := 'RG23A';
1526 jai_cmn_rg_others_pkg.check_balances(
1527 p_organization_id => v_org_id ,
1528 p_location_id => v_loc_id ,
1529 p_register_type => lv_register_type ,
1530 p_trx_amount => ln_cess_amount ,
1531 p_process_flag => lv_process_flag ,
1532 p_process_message => lv_process_message
1533 );
1534
1535 if lv_process_flag <> jai_constants.successful then
1536 lv_rg23a_cess_avlbl := 'FALSE';
1537 else
1538 lv_rg23a_cess_avlbl := 'TRUE';
1539 end if;
1540
1541
1542 lv_register_type := 'RG23C';
1543 jai_cmn_rg_others_pkg.check_balances(
1544 p_organization_id => v_org_id ,
1545 p_location_id => v_loc_id ,
1546 p_register_type => lv_register_type ,
1547 p_trx_amount => ln_cess_amount ,
1548 p_process_flag => lv_process_flag ,
1549 p_process_message => lv_process_message
1550 );
1551
1552 if lv_process_flag <> jai_constants.successful then
1553 lv_rg23c_cess_avlbl := 'FALSE';
1554 else
1555 lv_rg23c_cess_avlbl := 'TRUE';
1556 end if;
1560 jai_cmn_rg_others_pkg.check_balances(
1557
1558
1559 lv_register_type := 'PLA';
1561 p_organization_id => v_org_id ,
1562 p_location_id => v_loc_id ,
1563 p_register_type => lv_register_type ,
1564 p_trx_amount => ln_cess_amount ,
1565 p_process_flag => lv_process_flag ,
1566 p_process_message => lv_process_message
1567 );
1568
1569 if lv_process_flag <> jai_constants.successful then
1570 lv_pla_cess_avlbl := 'FALSE';
1571 else
1572 lv_pla_cess_avlbl := 'TRUE';
1573 end if;
1574
1575
1576
1577 FOR Line_Rec IN Line_Cur LOOP
1578 OPEN item_class_cur(V_ORG_ID,Line_Rec.Inventory_Item_Id);
1579 FETCH item_class_cur INTO v_item_class , v_excise_flag;
1580 CLOSE item_class_cur;
1581 FOR excise_cal_rec IN excise_cal_cur(Line_Rec.customer_trx_line_id, Line_Rec.Inventory_Item_ID, v_org_id) LOOP
1582 IF excise_cal_rec.t_type IN ('Excise') THEN
1583 v_basic_ed := NVL(v_basic_ed,0) + NVL(excise_cal_rec.func_amt,0);
1584 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
1585 v_additional_ed := NVL(v_additional_ed,0) + NVL(excise_cal_rec.func_amt,0);
1586 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
1587 v_other_ed := NVL(v_other_ed,0) + NVL(excise_cal_rec.func_amt,0);
1588 END IF;
1589 END LOOP;
1590 v_tax_amount := NVL(v_basic_ed,0) + NVL(v_additional_ed,0) + NVL(v_other_ed,0);
1591 v_basic_Ed := 0;
1592 v_additional_ed := 0;
1593 v_other_ed := 0;
1594 IF v_item_class IN ('CGEX','CGIN') THEN
1595 v_rg23c_tax_amount := NVL(v_rg23c_tax_amount,0) + NVL(v_tax_amount,0);
1596 ELSIF v_item_class IN ('RMIN','RMEX') THEN
1597 v_rg23a_tax_amount := NVL(v_rg23a_tax_amount,0) + NVL(v_tax_amount,0);
1598 ELSIF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') THEN
1599 v_other_tax_amount := NVL(v_other_tax_amount,0) + NVL(v_tax_amount,0);
1600 END IF;
1601
1602 v_tax_amount:=v_total_excise_amt;
1603
1604 IF NVL(v_excise_flag,'N') = 'Y' THEN
1605 IF NVL(v_ssi_unit_flag,'N') = 'N' THEN
1606 /*
1607 ||
1608 || v_complete_flag should have the values as ('N','A','C','P')
1609 */
1610 IF v_complete_flag IN ('N','A','C','P') THEN
1611 IF v_rg_flag = 'Y' THEN
1612 IF v_reg_code IN ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
1613
1614
1615 -- in order to hit the register based on preferences.
1616 /*
1617 Added code in the following segment to check for cess balance also
1618 */
1619
1620 IF v_item_class IN ('FGIN','FGEX','CCIN','CCEX','CGIN','CGEX','RMIN','RMEX') THEN
1621 IF v_reg_type IS NULL THEN
1622 OPEN preference_reg_cur(v_org_id,v_loc_id);
1623 FETCH preference_reg_cur INTO rg23a,rg23c,pla;
1624 CLOSE preference_reg_cur;
1625
1626 FOR reg_balance IN reg_balance_cur(v_org_id,v_loc_id) LOOP
1627 IF rg23a = 1 THEN
1628 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1629 v_rg23a_tax_amount := v_tax_amount;
1630 v_reg_type := 'RG23A';
1631 ELSE
1632 IF rg23c = 2 THEN
1633 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1634 v_rg23c_tax_amount := v_tax_amount;
1635 v_reg_type := 'RG23C';
1636 ELSIF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
1637 v_reg_type := 'PLA';
1638 END IF;
1639 ELSIF pla = 2 THEN
1640 IF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
1641 v_reg_type := 'PLA';
1642 ELSIF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1643 v_rg23c_tax_amount := v_tax_amount;
1644 v_reg_type := 'RG23C';
1645 END IF;
1646 END IF;
1647 END IF;
1648 ELSIF rg23c = 1 THEN
1649 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1650 v_rg23c_tax_amount := v_tax_amount;
1651 v_reg_type := 'RG23C';
1652 ELSE
1653 IF rg23a = 2 THEN
1654 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1655 v_rg23a_tax_amount := v_tax_amount;
1656 v_reg_type := 'RG23A';
1657 ELSIF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
1658 v_reg_type := 'PLA';
1662 v_reg_type := 'PLA';
1659 END IF;
1660 ELSIF pla = 2 THEN
1661 IF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
1663 ELSIF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1664 v_rg23a_tax_amount := v_tax_amount;
1665 v_reg_type := 'RG23A';
1666 END IF;
1667 END IF;
1668 END IF;
1669 ELSIF pla = 1 THEN
1670 IF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
1671 v_reg_type := 'PLA';
1672 ELSE
1673 IF rg23c = 2 THEN
1674 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1675 v_rg23c_tax_amount := v_tax_amount;
1676 v_reg_type := 'RG23C';
1677 ELSIF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1678 v_rg23a_tax_amount := v_tax_amount;
1679 v_reg_type := 'RG23A';
1680 END IF;
1681 ELSIF rg23a = 2 THEN
1682 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1683 v_rg23a_tax_amount := v_tax_amount;
1684 v_reg_type := 'RG23A';
1685 ELSIF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1686 v_rg23c_tax_amount := v_tax_amount;
1687 v_reg_type := 'RG23C';
1688 END IF;
1689 END IF;
1690 END IF;
1691 END IF; -- pref 1 if condition's end if
1692
1693 IF v_reg_type is null THEN
1694 pv_return_code := jai_constants.expected_error ;
1695 pv_return_message := 'None of the registers have enough balance for the excise duty -> ' || v_tax_amount || ' Or Cess amount => ' || ln_Cess_amount ;
1696 return ;
1697 END IF;
1698 IF v_reg_type = 'PLA' and NVL(v_ssi_unit_flag,'N') <> 'Y' THEN
1699 IF v_tax_amount > reg_balance.pla_balance AND lv_pla_cess_avlbl = 'TRUE' THEN
1700 pv_return_code := jai_constants.expected_error ;
1701 pv_return_message := 'PLA Balance -> ' || reg_balance.pla_balance ||' is not enough for the excise duty -> ' || v_tax_amount ;
1702 return ;
1703 END IF;
1704 ELSIF v_reg_type = 'RG23A' THEN
1705 IF v_tax_amount > reg_balance.rg23a_balance AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1706 pv_return_code := jai_constants.expected_error ;
1707 pv_return_message := 'RG23A Balance -> ' || reg_balance.rg23a_balance ||' is not enough for the excise duty -> ' || v_tax_amount ;
1708 return ;
1709 END IF;
1710 ELSIF v_reg_type = 'RG23C' THEN
1711 IF v_tax_amount > reg_balance.rg23c_balance AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1712 pv_return_code := jai_constants.expected_error ;
1713 pv_return_message := 'RG23C Balance -> ' || reg_balance.rg23c_balance ||' is not enough for the excise duty -> ' || v_tax_amount ;
1714 return ;
1715 END IF;
1716 END IF;
1717 END LOOP;
1718 END IF; -- for v_reg_type is null
1719 v_excise_paid_register := v_reg_type;
1720 END IF; -- for v_item_class in ('FGIN','FGEX'.... )
1721 END IF; -- for v_reg_code in ('DOMESTIC_EXCISE')....
1722
1723
1724 Declare
1725 v_reg_type VARCHAR2(10);
1726 Begin
1727 SELECT once_completed_flag
1728 INTO v_reg_type
1729 FROM JAI_AR_TRXS
1730 WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
1731
1732 IF v_reg_type = 'P' THEN
1733 v_reg_type := 'PLA';
1734 ELSIF v_reg_type = 'A' THEN
1735 v_reg_type := 'RG23A';
1736 ELSIF v_reg_type = 'C' THEN
1737 v_reg_type := 'RG23C';
1738 END IF;
1739
1740 IF v_reg_type is not null and v_reg_type <> 'N' THEN
1741 v_excise_paid_register := v_reg_type;
1742 END IF;
1743
1744 Exception
1745 When Others Then
1746 pv_return_code := jai_constants.expected_error ;
1747 pv_return_message := SQLERRM ;
1748 return ;
1749 End ;
1750
1751
1752 --Added by Qinglei for JAI Trigger Elimination 11-May-2012 begin
1753 OPEN c_jai_ar_trx_lines(LINE_REC.customer_trx_line_id);
1754 FETCH c_jai_ar_trx_lines INTO t_jai_line_rec_new;
1758 SET PAYMENT_REGISTER = v_excise_paid_register
1755 CLOSE c_jai_ar_trx_lines;
1756
1757 UPDATE JAI_AR_TRX_LINES
1759 WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
1760 INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
1761 CUSTOMER_TRX_ID = v_customer_trx_id;
1762
1763 OPEN c_jai_ar_trx_lines(LINE_REC.customer_trx_line_id);
1764 FETCH c_jai_ar_trx_lines INTO t_jai_line_rec_new;
1765 CLOSE c_jai_ar_trx_lines;
1766
1767 lv_action := jai_constants.UPDATING;
1768
1769 IF ( ( ( t_jai_line_rec_new.AUTO_INVOICE_FLAG <> 'Y' AND
1770 t_jai_line_rec_old.AUTO_INVOICE_FLAG <> 'Y'
1771 ) AND
1772 (t_jai_line_rec_new.Excise_Invoice_No IS NULL) AND
1773 (t_jai_line_rec_new.payment_Register IS NULL) AND
1774 (t_jai_line_rec_new.Excise_Invoice_Date IS NULL)
1775 ) OR
1776 (t_jai_line_rec_new.Customer_Trx_Id <> t_jai_line_rec_old.Customer_Trx_Id)
1777 )
1778 THEN
1779 JAI_AR_TAX_LINES_PKG.POPULATE_TAX_LINES_WRAPPER (
1780 pr_old => t_jai_line_rec_old,
1781 pr_new => t_jai_line_rec_new,
1782 pv_action => lv_action,
1783 pv_return_code => lv_return_code,
1784 pv_return_message => lv_return_message
1785 );
1786 IF lv_return_code <> jai_constants.successful then
1787 RAISE le_error;
1788 END IF;
1789 END IF;
1790 --Added by Qinglei for JAI Trigger Elimination 11-May-2012 end
1791 END IF; -- for v_rg_flag = 'Y'
1792 v_excise_paid_register := '';
1793 END IF; -- for v_complete_flag = 'N'
1794 -- END IF;
1795 ELSIF NVL(v_ssi_unit_flag,'N') = 'Y' THEN
1796 IF v_item_class IN ('RMIN','RMEX','CGEX','CGIN','FGIN','FGEX','CCIN','CCEX') THEN
1797 /*
1798 ||
1799 || v_complete_flag should have the values as ('N','A','C','P')
1800 */
1801 IF v_complete_flag IN ('N','A','C','P') THEN
1802 IF v_rg_flag = 'Y' THEN
1803 IF v_reg_code IN ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
1804 IF v_reg_type IS NULL THEN
1805 OPEN preference_reg_cur(v_org_id,v_loc_id);
1806 FETCH preference_reg_cur INTO rg23a,rg23c,pla;
1807 CLOSE preference_reg_cur;
1808
1809 FOR reg_balance IN reg_balance_cur(v_org_id,v_loc_id) LOOP
1810 IF rg23a = 1 THEN
1811 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1812 v_rg23a_tax_amount := v_tax_amount;
1813 v_reg_type := 'RG23A';
1814 ELSE
1815 IF rg23c = 2 THEN
1816 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1817 v_rg23c_tax_amount := v_tax_amount;
1818 v_reg_type := 'RG23C';
1819 ELSE
1820 v_reg_type := 'PLA';
1821 END IF;
1822 ELSIF pla = 2 THEN
1823 v_reg_type := 'PLA';
1824 END IF;
1825 END IF;
1826 ELSIF rg23c = 1 THEN
1827 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1828 v_rg23c_tax_amount := v_tax_amount;
1829 v_reg_type := 'RG23C';
1830 ELSE
1831 IF rg23a = 2 THEN
1832 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1833 v_rg23a_tax_amount := v_tax_amount;
1834 v_reg_type := 'RG23A';
1835 ELSE
1836 v_reg_type := 'PLA';
1837 END IF;
1838 ELSIF pla = 2 THEN
1839 v_reg_type := 'PLA';
1840 END IF;
1841 END IF;
1842 ELSIF pla = 1 THEN
1843 v_reg_type := 'PLA';
1844 END IF;
1845
1846 IF v_reg_type = 'RG23A' THEN
1847 IF v_tax_amount > reg_balance.rg23a_balance AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1848 pv_return_code := jai_constants.expected_error ;
1849 pv_return_message := 'RG23A Balance -> ' || reg_balance.rg23a_balance ||' is not enough for the excise duty -> ' || v_tax_amount ;
1850 return ;
1851 END IF;
1852 ELSIF v_reg_type = 'RG23C' THEN
1856 return ;
1853 IF v_tax_amount > reg_balance.rg23c_balance AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1854 pv_return_code := jai_constants.expected_error ;
1855 pv_return_message := 'RG23C Balance -> ' || reg_balance.rg23c_balance ||' is not enough for the excise duty -> ' || v_tax_amount ;
1857 END IF;
1858 END IF;
1859
1860 END LOOP;
1861 END IF; -- for v_reg_type is null
1862
1863 v_excise_paid_register := v_reg_type;
1864 -- END IF; -- for v_item_clas in ('FGIN','FGEX'...)
1865 END IF; -- for if v_reg_code in ('DOMESTIC_EXCISE'....)
1866
1867
1868
1869 Declare
1870 v_reg_type1 VARCHAR2(10);
1871 Begin
1872 SELECT once_completed_flag
1873 INTO v_reg_type1
1874 FROM JAI_AR_TRXS
1875 WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
1876
1877 If v_reg_type1 = 'P' THEN
1878 v_reg_type1 := 'PLA';
1879 ELSIF v_reg_type1 = 'A' THEN
1880 v_reg_type1 := 'RG23A';
1881 ELSIF v_reg_type1 = 'C' THEN
1882 v_reg_type1 := 'RG23C';
1883 END IF;
1884
1885 if v_reg_type1 is not null and v_reg_type1 <> 'N' then
1886 v_excise_paid_register := v_reg_type1;
1887 end if;
1888
1889 Exception
1890 When Others Then
1891 pv_return_code := jai_constants.expected_error ;
1892 pv_return_message := SQLERRM ;
1893 return ;
1894 END;
1895
1896 --Added by Qinglei for JAI Trigger Elimination 11-May-2012 begin
1897 OPEN c_jai_ar_trx_lines(LINE_REC.customer_trx_line_id);
1898 FETCH c_jai_ar_trx_lines INTO t_jai_line_rec_old;
1899 CLOSE c_jai_ar_trx_lines;
1900
1901 UPDATE JAI_AR_TRX_LINES
1902 SET PAYMENT_REGISTER = v_excise_paid_register
1903 WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
1904 INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
1905 CUSTOMER_TRX_ID = v_customer_trx_id;
1906
1907 OPEN c_jai_ar_trx_lines(LINE_REC.customer_trx_line_id);
1908 FETCH c_jai_ar_trx_lines INTO t_jai_line_rec_new;
1909 CLOSE c_jai_ar_trx_lines;
1910
1911 lv_action := jai_constants.UPDATING;
1912
1913 IF ( ( ( t_jai_line_rec_new.AUTO_INVOICE_FLAG <> 'Y' AND
1914 t_jai_line_rec_old.AUTO_INVOICE_FLAG <> 'Y'
1915 ) AND
1916 (t_jai_line_rec_new.Excise_Invoice_No IS NULL) AND
1917 (t_jai_line_rec_new.payment_Register IS NULL) AND
1918 (t_jai_line_rec_new.Excise_Invoice_Date IS NULL)
1919 ) OR
1920 (t_jai_line_rec_new.Customer_Trx_Id <> t_jai_line_rec_old.Customer_Trx_Id)
1921 )
1922 THEN
1923 JAI_AR_TAX_LINES_PKG.POPULATE_TAX_LINES_WRAPPER (
1924 pr_old => t_jai_line_rec_old,
1925 pr_new => t_jai_line_rec_new,
1926 pv_action => lv_action,
1927 pv_return_code => lv_return_code,
1928 pv_return_message => lv_return_message
1929 );
1930 IF lv_return_code <> jai_constants.successful then
1931 RAISE le_error;
1932 END IF;
1933 END IF;
1934 --Added by Qinglei for JAI Trigger Elimination 11-May-2012 end
1935 END IF; -- for v_rg_flag = 'Y;
1936 v_excise_paid_register := '';
1937 END IF; -- for v_complete_flag = 'N'
1938 END IF; -- for v_item_class in ('...)
1939 END IF; -- for v_ssi_unit_flag ....
1940 END IF; -- v_excise_flag = 'Y'
1941
1942
1943 END LOOP;
1944 INSERT INTO JAI_AR_TRX_INS_HDRS_T
1945 (
1946 ORGANIZATION_ID,
1947 LOCATION_ID,
1948 CUSTOMER_TRX_ID ,
1949 SHIP_TO_CUSTOMER_ID,
1950 SHIP_TO_SITE_USE_ID,
1951 CUST_TRX_TYPE_ID,
1952 TRX_DATE,
1953 SOLD_TO_CUSTOMER_ID,
1954 BATCH_SOURCE_ID,
1955 BILL_TO_CUSTOMER_ID ,
1956 BILL_TO_SITE_USE_ID ,
1957 CREATED_BY ,
1958 CREATION_DATE,
1959 LAST_UPDATED_BY,
1960 LAST_UPDATE_DATE
1961 )
1962 VALUES
1963 (
1964 V_ORG_ID,
1965 V_LOC_ID,
1966 V_CUSTOMER_TRX_ID ,
1967 pr_new.SHIP_TO_CUSTOMER_ID,
1968 pr_new.SHIP_TO_SITE_USE_ID,
1969 pr_new.CUST_TRX_TYPE_ID,
1970 pr_new.TRX_DATE,
1974 pr_new.BILL_TO_SITE_USE_ID,
1971 pr_new.SOLD_TO_CUSTOMER_ID,
1972 pr_new.BATCH_SOURCE_ID,
1973 pr_new.BILL_TO_CUSTOMER_ID ,
1975 FND_GLOBAL.USER_ID ,
1976 SYSDATE ,
1977 FND_GLOBAL.USER_ID ,
1978 SYSDATE);
1979 END IF;
1980
1981 --Added by Zhiwei for JAI Trigger elimination begin
1982 ---------------------------------------------------------
1983 open c_get_rec(pr_new.customer_trx_id);
1984 fetch c_get_rec into t_rec_old;
1985 close c_get_rec;
1986 ---------------------------------------------------------
1987 --Added by Zhiwei for JAI Trigger elimination end
1988
1989 UPDATE JAI_AR_TRXS
1990 SET
1991 ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
1992 WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
1993
1994 --Added by Zhiwei for JAI Trigger elimination begin
1995 ---------------------------------------------------------
1996 open c_get_rec(pr_new.customer_trx_id);
1997 fetch c_get_rec into t_rec_new;
1998 close c_get_rec;
1999
2000 if(t_rec_new.once_completed_flag = 'Y')then
2001
2002 lv_action := jai_constants.updating ;
2003
2004 JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
2005 pr_old => t_rec_old ,
2006 pr_new => t_rec_new ,
2007 pv_action => lv_action ,
2008 pv_return_code => lv_return_code ,
2009 pv_return_message => lv_return_message
2010 );
2011
2012 IF lv_return_code <> jai_constants.successful then
2013 RAISE le_error;
2014 END IF;
2015
2016 end if;
2017 ---------------------------------------------------------
2018 --Added by Zhiwei for JAI Trigger elimination end
2019
2020
2021 END IF;
2022 END IF;
2023
2024 if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
2025 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2026 G_MODULE_NAME || l_api_name || '.END',
2027 G_PKG_NAME || ': ' || l_api_name || '()-');
2028 end if;
2029
2030 EXCEPTION
2031 WHEN OTHERS THEN
2032 Pv_return_code := jai_constants.unexpected_error;
2033 Pv_return_message := 'Encountered an error in JAI_AR_IMPORT_SYNC_PKG.UPDATE_COMPLETE_FLAG ' || substr(sqlerrm,1,1900);
2034
2035 END update_complete_flag ;
2036
2037
2038 /*
2039 REM +======================================================================+
2040 REM NAME process_tcs_tax renamed from ARU_T7
2041 REM
2042 REM DESCRIPTION Called from trigger JAI_TAX_PROCESSING_PKG.PROCESS_COMPLETE
2043 REM
2044 REM NOTES Refers to old trigger JAI_AR_RCTA_TRIGGER_PKG.ARU_T7
2045 REM
2046 REM +======================================================================+
2047 */
2048 PROCEDURE process_tcs_tax
2049 ( PR_OLD t_jai_rec%TYPE , PR_NEW T_REC%TYPE , PV_ACTION VARCHAR2 , PV_RETURN_CODE OUT NOCOPY VARCHAR2 , PV_RETURN_MESSAGE OUT NOCOPY VARCHAR2 )
2050 IS
2051 LV_DOCUMENT_TYPE VARCHAR2(40);
2052 LN_REG_ID NUMBER;
2053 LV_ONCE_COMPLETED_FLAG JAI_AR_TRXS.ONCE_COMPLETED_FLAG%TYPE;
2054 V_HEADER_ID NUMBER;
2055
2056
2057 CURSOR ONCE_COMPLETE_FLAG_CUR IS
2058 SELECT ONCE_COMPLETED_FLAG
2059 FROM JAI_AR_TRXS
2060 WHERE CUSTOMER_TRX_ID = V_HEADER_ID;
2061 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_TCS_TAX()';
2062 BEGIN
2063
2064 if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
2065 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2066 G_MODULE_NAME || l_api_name || '.BEGIN',
2067 G_PKG_NAME || ': ' || l_api_name || '()+');
2068 end if;
2069
2070 V_HEADER_ID := PR_NEW.CUSTOMER_TRX_ID;
2071 IF NVL(PR_NEW.COMPLETE_FLAG,JAI_CONSTANTS.NO) = JAI_CONSTANTS.YES THEN
2072 /** Invoice is getting COMPLETED */
2073 LV_DOCUMENT_TYPE := JAI_CONSTANTS.TRX_TYPE_INV_COMP;
2074 /*********
2075 || When the invoice is getting completed for the very first time (once_complete_flag is still null or 'N') then pass the
2076 || final TCS accounting for the TCS type of taxes belonging to the manual invoice only
2077 || This is not applicable for the imported invoices.
2078 *********/
2079
2080 OPEN ONCE_COMPLETE_FLAG_CUR;
2081 FETCH ONCE_COMPLETE_FLAG_CUR INTO LV_ONCE_COMPLETED_FLAG;
2082 CLOSE ONCE_COMPLETE_FLAG_CUR;
2083
2084 IF Pr_new.created_from <> 'RAXTRX' AND
2085 lv_once_completed_flag = jai_constants.yes
2086
2087 THEN
2088
2089 JAI_AR_TCS_REP_PKG.AR_ACCOUNTING ( P_RACT => PR_NEW ,
2090 P_PROCESS_FLAG => PV_RETURN_CODE ,
2091 P_PROCESS_MESSAGE => PV_RETURN_MESSAGE
2092 );
2093
2094 END IF;
2095
2096 ELSIF NVL(PR_NEW.COMPLETE_FLAG,JAI_CONSTANTS.NO) = JAI_CONSTANTS.NO THEN
2097 /** INVOICE IS GETTING INCOMPLETED */
2098 LV_DOCUMENT_TYPE := JAI_CONSTANTS.TRX_TYPE_INV_INCOMP;
2099 END IF;
2100
2101
2102 JAI_AR_TCS_REP_PKG.PROCESS_TRANSACTIONS
2103 ( P_RACT => PR_NEW
2104 , P_EVENT => JAI_CONSTANTS.TRX_EVENT_COMPLETION
2105 , P_PROCESS_FLAG => PV_RETURN_CODE
2106 , P_PROCESS_MESSAGE => PV_RETURN_MESSAGE
2107 );
2108
2112
2109 IF PV_RETURN_CODE <> JAI_CONSTANTS.SUCCESSFUL THEN
2110 RETURN;
2111 END IF;
2113 if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
2114 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2115 G_MODULE_NAME || l_api_name || '.END',
2116 G_PKG_NAME || ': ' || l_api_name || '()-');
2117 end if;
2118
2119
2120 END process_tcs_tax;
2121
2122 /*
2123 REM +======================================================================+
2124 REM NAME process_cm_account renamed from ARU_T8
2125 REM
2126 REM DESCRIPTION Called from trigger JAI_TAX_PROCESSING_PKG.PROCESS_COMPLETE
2127 REM
2128 REM NOTES Refers to old trigger JAI_AR_RCTA_TRIGGER_PKG.ARU_T8
2129 REM
2130 REM +======================================================================+
2131 */
2132 PROCEDURE process_cm_vat_repo ( pr_old t_jai_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
2133 v_organization_id NUMBER ;
2134 v_loc_id NUMBER ;
2135 v_trans_type RA_CUST_TRX_TYPES_ALL.TYPE%TYPE ;
2136 lv_vat_invoice_no JAI_AR_TRXS.VAT_INVOICE_NO%TYPE ;
2137 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
2138 ln_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE ;
2139 lv_process_flag VARCHAR2(10) ;
2140 lv_process_message VARCHAR2(4000) ;
2141 ld_gl_date RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE%TYPE ;
2142 ld_vat_invoice_date JAI_AR_TRXS.VAT_INVOICE_DATE%TYPE ;
2143
2144 ln_rma_flag NUMBER;
2145 ln_order_line_id NUMBER;
2146 ln_nonship_rma_flag NUMBER;
2147
2148 /*
2149 || Get the order line id for customer trx line
2150 */
2151 CURSOR get_order_and_item_id_cur
2152 IS
2153 SELECT interface_line_attribute6 order_line_id
2154 ,inventory_item_id
2155 ,customer_trx_line_id
2156 FROM ra_customer_trx_lines_all
2157 WHERE customer_trx_id = pr_new.customer_trx_id;
2158
2159 /*
2160 || Check the trasaction is from RMA credit only
2161 */
2162 CURSOR check_rma_credit_cur(pn_order_line_id NUMBER)
2163 IS
2164 SELECT count(1)
2165 FROM OE_ORDER_HEADERS_ALL oh,
2166 OE_ORDER_LINES_ALL ol,
2167 OE_TRANSACTION_TYPES_TL ot,
2168 oe_workflow_assignments owf
2169 WHERE oh.header_id = ol.header_id
2170 AND oh.order_type_id = ot.transaction_type_id
2171 AND oh.order_type_id = owf.order_type_id
2172 AND ol.line_type_id = owf.line_type_id
2173 AND oh.order_number = pr_new.interface_header_attribute1
2174 AND ot.language = userenv('LANG')
2175 AND ol.line_id = pn_order_line_id
2176 AND owf.process_name IN ('R_RMA_CREDIT_APP_HDR_INV',
2177 'R_RMA_CREDIT_WO_SHIP_APPROVE',
2178 'R_RMA_CREDIT_WO_SHIP_HDR_INV',
2179 'R_RMA_FOR_CREDIT_WO_SHIPMENT',
2180 'R_RMA_FOR_OTA_CREDIT');
2181
2182 /*
2183 || Check the item type shippable or non-shippable
2184 */
2185 CURSOR check_shippable_item_cur(pn_inventory_item_id NUMBER,pn_order_line_id NUMBER)
2186 IS
2187 SELECT COUNT(1)
2188 FROM MTL_SYSTEM_ITEMS msi,
2189 JAI_OM_OE_RMA_LINES l
2190 WHERE msi.inventory_item_id = pn_inventory_item_id
2191 AND msi.inventory_item_id = l.inventory_item_id
2192 AND l.rma_line_id = pn_order_line_id
2193 AND msi.shippable_item_flag = 'N' ;
2194
2195 /*
2196 || Get the organization, location, vat_invoice_no and vat_invoice_date from JAI_AR_TRXS
2197 */
2198 CURSOR organization_cur
2199 IS
2200 SELECT organization_id ,
2201 location_id ,
2202 vat_invoice_no ,
2203 vat_invoice_date
2204 FROM JAI_AR_TRXS
2205 WHERE customer_trx_id = pr_new.customer_trx_id;
2206
2207 /*
2208 || Get the transaction type of the document
2209 */
2210 CURSOR transaction_type_cur
2211 IS
2212 SELECT type
2213 FROM ra_cust_trx_types_all
2214 WHERE cust_trx_type_id = pr_new.cust_trx_type_id AND
2215 NVL(org_id,0) = NVL(pr_new.org_id,0);
2216
2217
2218 /*
2219 || Check whether vat types of taxes exist for the CM.
2220 || IF yes then get the regime id and regime code
2221 */
2222 CURSOR cur_vat_taxes_exist
2223 IS
2224 SELECT regime_id ,
2225 regime_code
2226 FROM
2227 JAI_AR_TRX_TAX_LINES jcttl,
2228 JAI_AR_TRX_LINES jctl,
2229 JAI_CMN_TAXES_ALL jtc ,
2230 jai_regime_tax_types_v jrttv
2231 WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
2232 AND jctl.customer_trx_id = pr_new.customer_trx_id
2233 AND jcttl.tax_id = jtc.tax_id
2234 AND jtc.tax_type = jrttv.tax_type
2235 AND regime_code = jai_constants.vat_regime
2236 AND jtc.org_id = pr_new.org_id ;
2237
2238
2239 CURSOR cur_get_gl_date(cp_acct_class ra_cust_trx_line_gl_dist_all.account_class%type)
2240 IS
2241 SELECT gl_date
2242 FROM ra_cust_trx_line_gl_dist_all
2243 WHERE customer_trx_id = pr_new.customer_trx_id
2244 AND account_class = cp_acct_class
2245 AND latest_rec_flag = 'Y';
2246
2247 CURSOR cur_get_in_vat_no
2248 IS
2249 SELECT vat_invoice_no
2250 FROM JAI_AR_TRXS
2251 WHERE customer_trx_id = pr_new.previous_customer_trx_id;
2255 */
2252
2253 /*
2254 || Check if only 'VAT REVERSAL' tax type is present in ja_in_ra_cust_trx_tax_lines
2256 CURSOR c_chk_vat_reversal (cp_tax_type jai_cmn_taxes_all.tax_type%TYPE )
2257 IS
2258 SELECT 1
2259 FROM JAI_AR_TRX_TAX_LINES jcttl,
2260 JAI_AR_TRX_LINES jctl,
2261 JAI_CMN_TAXES_ALL jtc
2262 WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
2263 AND jctl.customer_trx_id = pr_new.customer_trx_id
2264 AND jcttl.tax_id = jtc.tax_id
2265 AND jtc.org_id = pr_new.org_id
2266 AND jtc.tax_type = cp_tax_type ;
2267
2268 lv_vat_reversal VARCHAR2(30);
2269 ln_vat_reversal_exists NUMBER;
2270
2271 /*
2272 || Retrieve the regime_id which is of regime code 'VAT'
2273 */
2274 CURSOR c_get_regime_id
2275 IS
2276 SELECT regime_id
2277 FROM jai_regime_tax_types_v
2278 WHERE regime_code = jai_constants.vat_regime
2279 AND rownum = 1 ;
2280
2281
2282 CURSOR get_copy_vat_invoice_cur
2283 IS
2284 SELECT jwl.vat_invoice_no
2285 FROM OE_ORDER_HEADERS_ALL ohc,
2286 oe_transaction_types_tl ot,
2287 JAI_OM_WSH_LINES_ALL jwl
2288 WHERE ohc.order_type_id = ot.transaction_type_id
2289 AND ot.LANGUAGE = userenv('LANG')
2290 AND ohc.source_document_id = jwl.ORDER_HEADER_ID
2291 AND ohc.ORDER_NUMBER = pr_new.INTERFACE_HEADER_ATTRIBUTE1
2292 AND ot.NAME = pr_new.INTERFACE_HEADER_ATTRIBUTE2;
2293
2294
2295 CURSOR cur_get_man_ar_inv_taxes
2296 ( cp_source IN jai_rgm_trx_records.source%TYPE,
2297 cp_source_trx_type IN jai_rgm_trx_records.source_trx_type%TYPE,
2298 cp_source_table_name IN jai_rgm_trx_records.source_table_name%TYPE,
2299 cp_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE,
2300 cp_organization_id IN JAI_AR_TRXS.organization_id%TYPE,
2301 cp_loc_id IN JAI_AR_TRXS.location_id%TYPE
2302 )
2303 IS
2304 SELECT
2305 jctl.customer_trx_id ,
2306 jctl.vat_assessable_value ,
2307 nvl(jctl.vat_exemption_flag,'N') vat_exemption_flag ,
2308 jcttl.customer_trx_line_id ,
2309 jcttl.tax_id ,
2310 jcttl.link_to_cust_trx_line_id ,
2311 jcttl.func_tax_amount ,
2312 jcttl.creation_date ,
2313 jtc.tax_type ,
2314 jcttl.tax_rate
2315 FROM
2316 JAI_AR_TRX_LINES jctl ,
2317 JAI_AR_TRX_TAX_LINES jcttl ,
2318 JAI_CMN_TAXES_ALL jtc ,
2319 (
2320 SELECT jrttv1.tax_type tax_type
2321 FROM jai_regime_tax_types_v jrttv1
2322 WHERE jrttv1.regime_code = jai_constants.vat_regime
2323 UNION
2324 SELECT 'VAT REVERSAL' tax_type
2325 FROM DUAL
2326 ) jrttv
2327 WHERE
2328 jctl.customer_trx_id = pr_new.customer_trx_id AND
2329 jctl.customer_trx_line_id = jcttl.link_to_cust_trx_line_id AND
2330 jctl.customer_trx_line_id = cp_customer_trx_line_id AND
2331 jcttl.tax_id = jtc.tax_id AND
2332 jtc.tax_type = jrttv.tax_type AND
2333 NOT EXISTS ( SELECT
2334 1
2335 FROM
2336 jai_rgm_trx_records jrtr
2337 WHERE
2338 jrtr.source = cp_source AND
2339 jrtr.source_trx_type = cp_source_trx_type AND
2340 jrtr.organization_id = cp_organization_id AND
2341 jrtr.location_id = cp_loc_id AND
2342 jrtr.source_table_name = cp_source_table_name AND
2343 jrtr.trx_reference1 = pr_new.customer_trx_id AND
2344 jrtr.source_document_id = jcttl.customer_trx_line_id AND
2345 jrtr.reference_id = jcttl.tax_id
2346 ) ;
2347
2348 ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
2349 ln_liab_acct_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
2350 ln_intliab_acct_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
2351 ln_charge_ac_id GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
2352 ln_balancing_ac_id GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
2353 ln_debit_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
2354 ln_credit_amount JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE ;
2355 lc_account_name VARCHAR2(50);
2356
2357
2358 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_CM_VAT_REPO()';
2359
2360 BEGIN
2361
2362 if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
2363 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2364 G_MODULE_NAME || l_api_name || '.BEGIN',
2365 G_PKG_NAME || ': ' || l_api_name || '()+');
2366 end if;
2367
2368 pv_return_code := jai_constants.successful ;
2369
2370 /*
2371 || Get the Otransaction type of the document
2372 || Process only CM type of transaction's
2373 */
2374 OPEN transaction_type_cur;
2378 IF NVL(v_trans_type,'N') <> 'CM'
2375 FETCH transaction_type_cur INTO v_trans_type;
2376 CLOSE transaction_type_cur;
2377
2379 OR pr_new.created_from <> 'RAXTRX' THEN
2380 /*
2381 || In case of CM only VAT accouting should be done.
2382 */
2383 RETURN;
2384 END IF;
2385
2386 /*
2387 || Get the Organization and location info , vat_invoice_no, vat_invoice_date
2388 */
2389 OPEN organization_cur;
2390 FETCH organization_cur
2391 INTO v_organization_id
2392 ,v_loc_id
2393 ,lv_vat_invoice_no
2394 ,ld_vat_invoice_date ;
2395 CLOSE organization_cur;
2396
2397 IF lv_vat_invoice_no IS NOT NULL OR
2398 ld_vat_invoice_date IS NOT NULL
2399 THEN
2400 /*
2401 || IF vat_invoice_no or vat_invoice_date has already been populated into this record (indicating that it has already been run once)
2402 || then return.
2403 */
2404 RETURN;
2405 END IF;
2406
2407
2408 OPEN cur_vat_taxes_exist;
2409 FETCH cur_vat_taxes_exist into ln_regime_id,ln_regime_code;
2410 CLOSE cur_vat_taxes_exist;
2411
2412 IF upper(nvl(ln_regime_code,'####')) <> UPPER(jai_constants.vat_regime) THEN
2413 /*
2414 || only vat type of taxes should be processed
2415 */
2416 RETURN;
2417 END IF;
2418 /*
2419 || Check if only 'VAT REVERSAL' tax type is present in ja_in_ra_cust_trx_tax_lines
2420 */
2421 IF ln_regime_id IS NULL THEN
2422 lv_vat_reversal := 'VAT REVERSAL' ;
2423 OPEN c_chk_vat_reversal(lv_vat_reversal) ;
2424 FETCH c_chk_vat_reversal INTO ln_vat_reversal_exists;
2425 CLOSE c_chk_vat_reversal ;
2426
2427 /*
2428 || Retrieve the regime_id for 'VAT REVERSAL' tax type, which is of regime code 'VAT'
2429 */
2430 IF ln_vat_reversal_exists = 1 THEN
2431 OPEN c_get_regime_id ;
2432 FETCH c_get_regime_id
2433 INTO ln_regime_id ;
2434 CLOSE c_get_regime_id ;
2435
2436 IF ln_regime_id IS NOT NULL THEN
2437 ln_regime_code := jai_constants.vat_regime ;
2438 END IF ;
2439 END IF ;
2440 END IF ;
2441
2442 /*
2443 || Get the vat invoice number for the Credit Memo from the Source Invoice only if a CM has a source INvoice
2444 || IF it is from legacy then the vat invoice number would go as null
2445 */
2446 IF pr_new.previous_customer_trx_id is NOT NULL THEN
2447 OPEN cur_get_in_vat_no;
2448 FETCH cur_get_in_vat_no
2449 INTO lv_vat_invoice_no;
2450 CLOSE cur_get_in_vat_no ;
2451
2452 ELSE
2453 OPEN get_copy_vat_invoice_cur;
2454 FETCH get_copy_vat_invoice_cur
2455 INTO lv_vat_invoice_no;
2456 CLOSE get_copy_vat_invoice_cur ;
2457
2458 END IF;
2459
2460 /*
2461 || Get the gl_date from ra_cust_trx_lines_gl_dist_all
2462 */
2463 OPEN cur_get_gl_date('REC');
2464 FETCH cur_get_gl_date INTO ld_gl_date;
2465 CLOSE cur_get_gl_date;
2466
2467 FOR get_order_and_item_id_rec IN get_order_and_item_id_cur LOOP
2468
2469
2470 OPEN check_rma_credit_cur(get_order_and_item_id_rec.order_line_id);
2471 FETCH check_rma_credit_cur
2472 INTO ln_rma_flag;
2473 CLOSE check_rma_credit_cur;
2474
2475 OPEN check_shippable_item_cur( get_order_and_item_id_rec.inventory_item_id
2476 ,get_order_and_item_id_rec.order_line_id);
2477 FETCH check_shippable_item_cur
2478 INTO ln_nonship_rma_flag;
2479 CLOSE check_shippable_item_cur;
2480
2481 IF ln_rma_flag >0 OR ln_nonship_rma_flag >0
2482 THEN
2483
2484 FOR rec_cur_get_man_ar_inv_taxes IN cur_get_man_ar_inv_taxes(upper(jai_constants.source_ar)
2485 ,jai_constants.source_ttype_man_ar_inv
2486 ,jai_constants.tname_cus_trx_lines
2487 ,get_order_and_item_id_rec.customer_trx_line_id
2488 ,v_organization_id
2489 ,v_loc_id)
2490 LOOP
2491
2492 /*******************************
2493 ||Variable Initialization
2494 *******************************/
2495 ln_liab_acct_ccid := null;
2496 ln_intliab_acct_ccid := null;
2497 ln_charge_ac_id := null;
2498 ln_balancing_ac_id := null;
2499 ln_credit_amount := null;
2500 ln_debit_amount := null;
2501 lc_account_name := null;
2502
2503
2504 /*******************************
2505 ||Get the code combination id
2506 ||for the "LIABILITY ACCOUNT"
2507 *******************************/
2508 ln_liab_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
2509 p_regime_id => ln_regime_id ,
2510 p_organization_type => jai_constants.orgn_type_io ,
2511 p_organization_id => v_organization_id ,
2512 p_location_id => v_loc_id ,
2513 p_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type ,
2514 p_account_name => jai_constants.liability
2518 || Get the code combination id
2515 ) ;
2516
2517 /*******************************
2519 || for the "INTERIM LIABILITY ACCOUNT"
2520 *******************************/
2521 ln_intliab_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
2522 p_regime_id => ln_regime_id ,
2523 p_organization_type => jai_constants.orgn_type_io ,
2524 p_organization_id => v_organization_id ,
2525 p_location_id => v_loc_id ,
2526 p_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type ,
2527 p_account_name => jai_constants.liability_interim
2528 ) ;
2529
2530
2531 /*
2532 || Validate that if any one of the liability account or interim liability account is not defined then error our
2533 */
2534 IF ln_liab_acct_ccid IS NULL OR
2535 ln_intliab_acct_ccid IS NULL
2536 THEN
2537 pv_return_code := jai_constants.expected_error;
2538 pv_return_message := 'VAT receivable accouting entries cannot be passed.
2539 Please set up the Liability account and the Interim Liability account for the corresponding VAT regime';
2540 return;
2541 END IF;
2542
2543
2544
2545 ln_charge_ac_id := ln_intliab_acct_ccid ;
2546 ln_balancing_ac_id := ln_liab_acct_ccid ;
2547 ln_debit_amount := null ;
2548 ln_credit_amount := abs(rec_cur_get_man_ar_inv_taxes.func_tax_amount) ;
2549 lc_account_name := jai_constants.recovery ;
2550
2551
2552
2553
2554 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry (
2555 pn_repository_id => ln_repository_id ,
2556 pn_regime_id => ln_regime_id ,
2557 pv_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type ,
2558 pv_organization_type => jai_constants.orgn_type_io ,
2559 pn_organization_id => v_organization_id ,
2560 pn_location_id => v_loc_id ,
2561 pv_source => jai_constants.source_ar ,
2562 pv_source_trx_type => jai_constants.source_ttype_man_ar_inv ,
2563 pv_source_table_name => jai_constants.tname_cus_trx_lines ,
2564 pn_source_id => rec_cur_get_man_ar_inv_taxes.customer_trx_line_id ,
2565 pd_transaction_date => rec_cur_get_man_ar_inv_taxes.creation_date ,
2566 pv_account_name => lc_account_name , --Date 14/06/2007 by sacsethi for bug 6072461
2567 pn_charge_account_id => ln_charge_ac_id ,
2568 pn_balancing_account_id => ln_balancing_ac_id ,
2569 pn_credit_amount => ln_credit_amount ,
2570 pn_debit_amount => ln_debit_amount ,
2571 pn_assessable_value => rec_cur_get_man_ar_inv_taxes.vat_assessable_value ,
2572 pn_tax_rate => rec_cur_get_man_ar_inv_taxes.tax_rate ,
2573 pn_reference_id => rec_cur_get_man_ar_inv_taxes.tax_id ,
2574 pn_batch_id => NULL ,
2575 pn_inv_organization_id => v_organization_id ,
2576 pv_invoice_no => lv_vat_invoice_no ,
2577 pd_invoice_date => nvl(nvl(ld_gl_date,pr_new.trx_date),rec_cur_get_man_ar_inv_taxes.creation_date) ,
2578 pv_called_from => jai_constants.vat_repo_call_from_om_ar ,
2579 pv_process_flag => lv_process_flag ,
2580 pv_process_message => lv_process_message ,
2581 pv_trx_reference_context => jai_constants.contxt_manual_ar ,
2582 pv_trx_reference1 => rec_cur_get_man_ar_inv_taxes.customer_trx_id ,
2583 pv_trx_reference2 => rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id ,
2584 pv_trx_reference3 => NULL ,
2585 pv_trx_reference4 => NULL ,
2586 pv_trx_reference5 => NULL
2587 );
2588
2589
2590
2591 IF lv_process_flag = jai_constants.expected_error OR
2592 lv_process_flag = jai_constants.unexpected_error
2593 THEN
2594 pv_return_code := lv_process_flag ;
2595 pv_return_code := lv_process_message ;
2596 return;
2597 END IF;
2598
2599 END LOOP;
2600
2601 END IF;
2602 END LOOP;
2603
2604 if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
2605 FND_LOG.STRING(G_LEVEL_PROCEDURE,
2606 G_MODULE_NAME || l_api_name || '.END',
2607 G_PKG_NAME || ': ' || l_api_name || '()-');
2608 end if;
2609
2610 EXCEPTION
2611 WHEN OTHERS THEN
2612 Pv_return_code := jai_constants.unexpected_error;
2613 Pv_return_message := 'Encountered an error in JAI_AR_IMPORT_SYNC_PKG.PROCESS_CM_VAT_REPO ' || substr(sqlerrm,1,1900);
2614
2615 END process_cm_vat_repo ;
2616
2617
2618
2619
2620 END JAI_AR_IMPORT_SYNC_PKG ;