1 PACKAGE BODY JAI_AR_RCTA_TRIGGER_PKG AS
2 /* $Header: jai_ar_rcta_t.plb 120.17.12010000.3 2008/11/10 06:56:00 csahoo ship $ */
3
4 /*
5 || foll function created by csahoo - for seperate vat invoice num for unreg dealers - bug# 5233925
6 */
7
8 FUNCTION check_reg_dealer
9 ( pn_customer_id NUMBER ,
10 pn_site_use_id NUMBER
11 ) return boolean
12
13 IS
14 ln_address_id NUMBER;
15 lv_regno JAI_CMN_CUS_ADDRESSES.vat_Reg_no%type;
16
17 CURSOR c_get_address is
18 SELECT hzcas.cust_acct_site_id
19 FROM hz_cust_site_uses_all hzcsu ,
20 hz_cust_acct_sites_all hzcas
21 WHERE hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
22 AND hzcsu.site_use_id = pn_site_use_id
23 AND hzcas.cust_account_id = pn_customer_id ;
24
25 CURSOR c_regno (pn_address_id NUMBER) IS
26 SELECT vat_Reg_no
27 FROM JAI_CMN_CUS_ADDRESSES
28 WHERE customer_id = pn_customer_id
29 AND address_id = pn_address_id;
30
31
32 BEGIN
33
34 open c_get_address;
35 fetch c_get_address into ln_address_id;
36 close c_get_address;
37
38 IF ln_address_id IS NOT NULL THEN
39
40 open c_regno (ln_address_id);
41 fetch c_regno into lv_regno;
42 close c_regno;
43 END IF;
44
45 IF lv_regno IS NULL THEN
46 return (false);
47 ELSE
48 return (true);
49 END IF;
50
51
52 END check_reg_dealer;
53
54 /*
55 || csahoo - for seperate vat invoice num for unreg dealers - bug# 5233925
56 */
57
58 /*
59 REM +======================================================================+
60 REM NAME ARI_T1
61 REM
62 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ARIUD_T1
63 REM
64 REM NOTES Refers to old trigger JAI_AR_RCTA_ARI_T8
65 REM
66 REM +======================================================================+
67 */
68 PROCEDURE ARI_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
69 v_org_id Number; --File.Sql.35 Cbabu := -1;
70 v_loc_id Number ;
71 v_reg_code Varchar2(30);
72 v_excise_invoice_no Number;
73 v_update_rg Varchar2(1); -- := 'Y'; --Ramananda for File.Sql.35
74 v_update_rg23d_flag Varchar2(1); -- := 'Y'; --Ramananda for File.Sql.35
75 v_reg_type Varchar2(10);
76 v_complete_flag Varchar2(1);
77 --v_row_id rowid; --File.Sql.35 Cbabu := pr_new.rowid;
78 v_parent_trx_number Varchar2(20); --File.Sql.35 Cbabu := pr_new.recurred_from_trx_number;
79 v_trans_type Varchar2(30);
80
81 cursor loc_app_cur IS
82 SELECT organization_id, location_id
83 FROM JAI_AR_TRX_APPS_RELS_T ; /* Modified cursor by rallamse bug#4479131 PADDR Elimination */
84
85
86 Cursor register_code_cur(p_org_id IN Number, p_loc_id IN Number) IS
87 SELECT register_code
88 FROM JAI_OM_OE_BOND_REG_HDRS
89 WHERE organization_id = p_org_id AND location_id = p_loc_id AND
90 register_id in (SELECT register_id
91 FROM JAI_OM_OE_BOND_REG_DTLS
92 WHERE order_type_id = pr_new.batch_source_id and order_flag = 'N');
93
94 Cursor organization_cur IS
95 SELECT organization_id,location_id
96 FROM JAI_AR_TRXS
97 WHERE trx_number = v_parent_trx_number;
98
99 Cursor transaction_type_cur IS
100 Select type
101 From RA_CUST_TRX_TYPES_ALL
102 Where cust_trx_type_id = pr_new.cust_trx_type_id
103 And NVL(org_id,0) = NVL(pr_new.org_id,0);
104
105 Cursor localization_header_info IS
106 Select organization_id, location_id, update_rg_flag
107 From JAI_AR_TRXS
108 Where customer_trx_id= pr_new.previous_customer_trx_id;
109
110 v_currency_code gl_sets_of_books.currency_code%type;
111 CURSOR curr(c_sob NUMBER) IS
112 SELECT currency_code
113 FROM gl_sets_of_books
114 WHERE set_of_books_id = c_sob;
115 V_CURR CURR%ROWTYPE; --2002/03/11 Vijay
116 BEGIN
117 pv_return_code := jai_constants.successful ;
118 /*------------------------------------------------------------------------------------------
119 FILENAME: JA_IN_LOC_INFO_AR_HDR_TRG.sql
120
121 CHANGE HISTORY:
122 S.No Date Author and Details
123 ========================================
124
125 1. 10-Aug-2005 Aiyer bug 4545146 version 120.1
126 Issue:-
127 Deadlock on tables due to multiple triggers on the same table (in different sql files)
128 firing in the same phase.
129 Fix:-
130 Multiple triggers on the same table have been merged into a single file to resolve
131 the problem
132 The following files have been stubbed:-
133 jai_ar_rcta_t1.sql
134 jai_ar_rcta_t2.sql
135 jai_ar_rcta_t3.sql
136 jai_ar_rcta_t4.sql
137 jai_ar_rcta_t6.sql
138 jai_ar_rcta_t7.sql
139 jai_ar_rcta_t8.sql
140 jai_ar_rcta_t9.sql
141 Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers in the above files
142
143
144 Dependency Due to this Bug:-
145 The current trigger becomes dependent on the function jai_cmn_utils_pkg.check_jai_exists version 115.0.
146
147
148 Future Dependencies For the release Of this Object:-
149 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
150 A datamodel change )
151 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
152 Current Version Current Bug Dependent Files Version Author Date Remarks
153 Of File On Bug/Patchset Dependent On
154
155 ja_in_loc_info_ar_hdr_trg.sql
156 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
157
158 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
159
160 v_update_rg := 'Y'; --Ramananda for File.Sql.35
161 v_update_rg23d_flag := 'Y'; --Ramananda for File.Sql.35
162 v_org_id := -1;
163 v_parent_trx_number := pr_new.recurred_from_trx_number;
164
165 OPEN curr(pr_new.set_of_books_id);
166 FETCH curr into v_curr;
167 CLOSE curr;
168
169 OPEN transaction_type_cur;
170 FETCH transaction_type_cur INTO v_trans_type;
171 CLOSE transaction_type_cur;
172 IF NVL(v_trans_type,'N') NOT IN('CM','INV','DM') THEN
173 Return;
174 END IF;
175 IF pr_new.created_from = 'ARXREC' THEN
176 Insert Into JAI_AR_TRX_COPY_HDR_T
177 (TRX_NUMBER, CUSTOMER_TRX_ID, RECURRED_FROM_TRX_NUMBER, BATCH_SOURCE_ID,
178 CREATED_FROM, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
179 LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
180 VALUES
181 (pr_new.trx_number, pr_new.CUSTOMER_TRX_ID, v_parent_trx_number, pr_new.BATCH_SOURCE_ID,
182 pr_new.CREATED_FROM, pr_new.CREATION_DATE, pr_new.CREATED_BY, pr_new.LAST_UPDATE_DATE,
183 pr_new.LAST_UPDATED_BY, pr_new.LAST_UPDATE_LOGIN);
184 ELSE
185 IF pr_new.created_from = 'ARXTWCMI' THEN
186 IF pr_new.previous_customer_trx_id IS Not Null Then
187 Open localization_header_info;
188 Fetch localization_header_info Into v_org_id, v_loc_id, v_update_rg;
189 Close localization_header_info;
190 ELSE
191 Return;
192 END IF;
193 ELSE
194 OPEN loc_app_cur;
195 FETCH loc_app_cur INTO v_org_id,v_loc_id;
196 CLOSE loc_app_cur;
197
198 IF NVL(v_org_id, 999999) = 999999 THEN -- changed 0 to 999999 because trigger was returning in case where
199 -- setup business group is done. Bug # 2846277
200 IF v_parent_trx_number IS NULL THEN
201 RETURN;
202 ELSE
203 OPEN organization_cur;
204 FETCH organization_cur INTO v_org_id, v_loc_id;
205 CLOSE organization_cur;
206 END IF;
207 END IF;
208 IF NVL(v_org_id, 999999) = 999999 THEN -- changed 0 to 999999 because trigger was returning in case where
209 -- setup business group is done. Bug # 2846277
210 RETURN;
211 END IF;
212
213 OPEN register_code_cur(v_org_id,v_loc_id);
214 FETCH register_code_cur INTO v_reg_code;
215 CLOSE register_code_cur;
216
217 /*
218 in the following if .. elsif block comparison to the register codes was done in lower case , which was in R11 , in R11i ,
219 it is in upper case. - bug# 3496577
220 */
221 IF v_reg_code IS NULL THEN
222 v_update_rg := 'N';
223 v_update_rg23d_flag := 'N';
224 ELSIF v_reg_code IN ('23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE','23D_EXPORT_WITHOUT_EXCISE','23D_DOM_WITHOUT_EXCISE') THEN
225 v_update_rg23d_flag := 'Y';
226 v_update_rg := 'N';
227 ELSIF v_reg_code IN ('DOMESTIC_EXCISE','EXPORT_EXCISE','BOND_REG','DOM_WITHOUT_EXCISE') THEN
228 v_update_rg := 'Y';
229 v_update_rg23d_flag := 'N';
230 END IF;
231 END IF;
232
233 -------
234 INSERT INTO JAI_AR_TRXS
235 (
236 CUSTOMER_TRX_ID ,
237 ORGANIZATION_ID ,
238 LOCATION_ID ,
239 TRX_NUMBER ,
240 UPDATE_RG_FLAG ,
241 UPDATE_RG23d_FLAG,
242 ONCE_COMPLETED_FLAG,
243 BATCH_SOURCE_ID,
244 SET_OF_BOOKS_ID,
245 PRIMARY_SALESREP_ID,
246 INVOICE_CURRENCY_CODE,
247 EXCHANGE_RATE_TYPE,
248 EXCHANGE_DATE,
249 EXCHANGE_RATE,
250 CREATED_FROM,
251 CREATION_DATE ,
252 CREATED_BY ,
253 LAST_UPDATE_DATE ,
254 LAST_UPDATE_LOGIN ,
255 LAST_UPDATED_BY,
256 LEGAL_ENTITY_ID /* added rallamse bug#4448789 */
257 )
258 VALUES
259 (
260 pr_new.CUSTOMER_TRX_ID,
261 V_ORG_ID,
262 V_LOC_ID,
263 pr_new.TRX_NUMBER,
264 V_UPDATE_RG,
265 v_update_rg23d_flag,
266 pr_new.COMPLETE_FLAG,
267 pr_new.BATCH_SOURCE_ID,
268 pr_new.SET_OF_BOOKS_ID,
269 pr_new.PRIMARY_SALESREP_ID,
270 pr_new.INVOICE_CURRENCY_CODE,
271 pr_new.EXCHANGE_RATE_TYPE,
272 pr_new.EXCHANGE_DATE,
273 pr_new.EXCHANGE_RATE,
274 pr_new.CREATED_FROM,
275 pr_new.CREATION_DATE,
276 pr_new.CREATED_BY,
277 pr_new.LAST_UPDATE_DATE,
278 pr_new.LAST_UPDATE_LOGIN,
279 pr_new.LAST_UPDATED_BY,
280 pr_new.LEGAL_ENTITY_ID /* added rallamse bug#4448789 */
281 );
282 END IF;
283 /* Added an exception block by Ramananda for bug#4570303 */
284 EXCEPTION
285 WHEN OTHERS THEN
286 Pv_return_code := jai_constants.unexpected_error;
287 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARI_T1 ' || substr(sqlerrm,1,1900);
288 END ARI_T1 ;
289
290 /*
291 REM +======================================================================+
292 REM NAME ARU_T1
293 REM
294 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ARIUD_T1
295 REM
296 REM NOTES Refers to old trigger JAI_AR_RCTA_ARU_T2
297 REM
298 REM +======================================================================+
299 */
300 PROCEDURE ARU_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
301 /*
302 start additions by sriram for VAT
303 */
304
305 v_vat_start_num JAI_CMN_INVENTORY_ORGS.current_number%Type;
306 v_vat_jump_by JAI_CMN_INVENTORY_ORGS.jump_by%type;
307 v_vat_prefix JAI_CMN_INVENTORY_ORGS.prefix%type;
308 v_vat_invoice_no JAI_AR_TRXS.tax_invoice_no%type;
309 v_vat_reg_no JAI_CMN_INVENTORY_ORGS.vat_reg_no%type;
310
311
312 v_organization_id Number;
313 v_loc_id Number;
314 v_vat_taxes_exist Number;
315 v_trans_type VARCHAR2(30);
316 v_loc_vat_inv_no JAI_AR_TRXS.tax_invoice_no%type;
317
318 CURSOR organization_cur IS
319 SELECT organization_id,location_id
320 FROM JAI_AR_TRXS
321 where customer_trx_id = pr_new.customer_trx_id;
322
323 CURSOR C_VAT_INVOICE_CUR IS
324 SELECT TAX_INVOICE_NO
325 FROM JAI_AR_TRXS
326 WHERE Customer_Trx_Id = pr_new.customer_trx_id;
327
328
329 cursor c_vat_taxes_exist
330 is
331 select 1
332 from JAI_AR_TRX_TAX_LINES
333 where link_to_cust_trx_line_id
334 in
335 (select customer_trx_line_id
336 from JAI_AR_TRX_LINES
337 where customer_trx_id = pr_new.customer_trx_id
338 )
339 and tax_id in
340 (select tax_id
341 from JAI_CMN_TAXES_ALL
342 where vat_flag = 'Y'
343 and org_id = pr_new.org_id
344 )
345 ;
346
347 CURSOR transaction_type_cur IS
348 SELECT TYPE
349 FROM RA_CUST_TRX_TYPES_ALL
350 WHERE cust_trx_type_id = pr_new.cust_trx_type_id
351 AND NVL(org_id,0) = NVL(pr_new.org_id,0);
352
353
354 Procedure Generate_Tax_Invoice_no (p_organization_id Number , p_loc_id Number) is
355
356 Cursor c_get_vat_reg_no is
357 select vat_reg_no
358 from JAI_CMN_INVENTORY_ORGS
359 where organization_id = p_organization_id
360 and location_id = p_loc_id;
361
362 cursor c_get_vat_invoice_no is
363 select current_number , jump_by , prefix
364 from JAI_CMN_INVENTORY_ORGS
365 where organization_id = p_organization_id
366 and location_id = p_loc_id;
367
368
369
370 Begin
371
372 open c_get_vat_reg_no;
373 fetch c_get_vat_reg_no into v_vat_reg_no;
374 close c_get_vat_reg_no;
375
376 if v_vat_reg_no is null then
377 -- VAT reg number has not been defined for the org and loc.
378 return;
379 end if;
380
381
382 -- lock the records
383 update JAI_CMN_INVENTORY_ORGS
384 set last_update_date = last_update_date
385 where vat_reg_no = v_vat_reg_no;
386
387 Open c_get_vat_invoice_no;
388 Fetch c_get_vat_invoice_no into v_vat_start_num, v_vat_jump_by, v_vat_prefix;
389 close c_get_vat_invoice_no;
390
391 v_vat_start_num := NVL(v_vat_start_num,0) + NVL(v_vat_jump_by,1);
392
393 if v_vat_prefix is not null then
394 v_vat_invoice_no := v_vat_prefix || '/' || v_vat_start_num;
395 else
396 v_vat_invoice_no := v_vat_start_num;
397 end if;
398
399 update JAI_AR_TRXS
400 set tax_invoice_no = v_vat_invoice_no
401 where customer_trx_id = pr_new.customer_trx_id;
402
403 update JAI_CMN_INVENTORY_ORGS
404 set current_number = NVL(v_vat_start_num,0) ,
405 prefix = v_vat_prefix,
406 jump_by = v_vat_jump_by
407 where vat_Reg_no = v_vat_reg_no;
408
409 End;
410
411
412 /*
413 end additions by sriram for VAT
414 */
415 /******************************************************************************************************************
416 File name : jai_ar_gen_tax_inv_upd_trg.sql
417 Created By Aiyer
418
419 Created Date 31-Mar-2005
420
421 Bug 4276502
422
423 Purpose : Support the old vat Functionality . The ja_in_loc_ar_hdr_upd_trg_vat 115.2 now supports the new vat functionality for Credit Memo.
424 This trigger supports the same for the Invoice .The code in this trigger is the same as that which existed in the trigger
425 ja_in_loc_ar_hdr_upd_trg_vat 115.1.
426 Tax invoice number to be generated when an Auto_invoiced invoice is imported or when a manual invoice is completed.
427
428 1. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
429 DB Entity as required for CASE COMPLAINCE. Version 116.1
430
431 Dependency Due to this Bug:-
432 IN60106 + 4245089 (VAT Enhancement)
433
434 2. 13-Jun-2005 File Version: 116.2
435 Ramananda for bug#4428980. Removal of SQL LITERALs is done
436
437
438
439 3. 10-Aug-2005 Aiyer bug 4545146 version 120.1
440 Issue:-
441 Deadlock on tables due to multiple triggers on the same table (in different sql files)
442 firing in the same phase.
443 Fix:-
444 Multiple triggers on the same table have been merged into a single file to resolve
445 the problem
446 The following files have been stubbed:-
447 jai_ar_rcta_t1.sql
448 jai_ar_rcta_t2.sql
449 jai_ar_rcta_t3.sql
450 jai_ar_rcta_t4.sql
451 jai_ar_rcta_t6.sql
452 jai_ar_rcta_t7.sql
453 jai_ar_rcta_t8.sql
454 jai_ar_rcta_t9.sql
455 Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers
456
457 Future Dependencies For the release Of this Object:-
458 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
459 A datamodel change )
460 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
461 Current Version Current Bug Dependent Files Version Author Date Remarks
462 Of File On Bug/Patchset Dependent On
463
464 jai_ar_gen_tax_inv_upd_trg.sql
465 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
466 4. 02-MAR-2007 SSAWANT , File version 120.6
467 Forward porting the change in 11.5 bug 4998378 to R12 bug no 5040383.
468 1) Moved the opening/fetching/closing of the cursors - transaction_type_cur, Complete_Cur
469 Future Dependency due to this Bug
470 --------------------------
471 None
472 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
473
474 BEGIN
475 pv_return_code := jai_constants.successful ;
476 Open C_VAT_INVOICE_CUR;
477 Fetch C_VAT_INVOICE_CUR into v_loc_vat_inv_no;
478 close C_VAT_INVOICE_CUR;
479
480 if v_loc_vat_inv_no is not null then
481 return;
482 end if;
483
484 OPEN transaction_type_cur;
485 FETCH transaction_type_cur INTO v_trans_type;
486 CLOSE transaction_type_cur;
487
488 IF NVL(v_trans_type,'N') <> 'INV' THEN
489 -- VAT invoice number should be generated only for an Invoice and not for others like cm for RMA.
490 RETURN;
491 END IF;
492
493
494 OPEN organization_cur;
495 FETCH organization_cur INTO v_organization_id, v_loc_id;
496 CLOSE organization_cur;
497
498 Open c_vat_taxes_exist;
499 Fetch c_vat_taxes_exist into v_vat_taxes_exist;
500 Close c_vat_taxes_exist;
501
502 if v_vat_taxes_exist = 1 then
503 Generate_Tax_Invoice_no(v_organization_id,v_loc_id);
504 end if;
505 /* Added an exception block by Ramananda for bug#4570303 */
506 EXCEPTION
507 WHEN OTHERS THEN
508 Pv_return_code := jai_constants.unexpected_error;
509 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARU_T1 ' || substr(sqlerrm,1,1900);
510 END ARU_T1 ;
511
512 /*
513 REM +======================================================================+
514 REM NAME ARU_T2
515 REM
516 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ARIUD_T1
517 REM
518 REM NOTES Refers to old trigger JAI_AR_RCTA_ARU_T3
519 REM
520 REM +======================================================================+
521 */
522 PROCEDURE ARU_T2 ( 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
523 v_trans_type Varchar2(30);
524 v_trx_number varchar2(30); -- := pr_new.Trx_Number; --Ramananda for File.Sql.35
525 v_ref_line_id varchar2(30); -- := pr_new.interface_header_attribute7; --Ramananda for File.Sql.35
526
527 Cursor transaction_type_cur IS
528 Select type
529 From RA_CUST_TRX_TYPES_ALL
530 Where cust_trx_type_id = pr_new.cust_trx_type_id
531 And NVL(org_id,0) = NVL(pr_new.org_id,0);
532
533 v_currency_code gl_sets_of_books.currency_code%type;
534 BEGIN
535 pv_return_code := jai_constants.successful ;
536 /*------------------------------------------------------------------------------------------
537 FILENAME:JA_IN_TRX_HDR_UPDATE_TRG.sql
538
539 CHANGE HISTORY:
540 S.No Date Author and Details
541 ------------------------------------------------------------------------------------------
542 1. 10-Aug-2005 Aiyer bug 4545146 version 120.1
543 Issue:-
544 Deadlock on tables due to multiple triggers on the same table (in different sql files)
545 firing in the same phase.
546 Fix:-
547 Multiple triggers on the same table have been merged into a single file to resolve
548 the problem
549 The following files have been stubbed:-
550 jai_ar_rcta_t1.sql
551 jai_ar_rcta_t2.sql
552 jai_ar_rcta_t3.sql
553 jai_ar_rcta_t4.sql
554 jai_ar_rcta_t6.sql
555 jai_ar_rcta_t7.sql
556 jai_ar_rcta_t8.sql
557 jai_ar_rcta_t9.sql
558 Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers
559
560 Future Dependencies For the release Of this Object:-
561 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
562 A datamodel change )
563 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
564 Current Version Current Bug Dependent Files Version Author Date Remarks
565 Of File On Bug/Patchset Dependent On
566
567 ja_in_trx_hdr_update_trg.sql
568 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
569
570 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
571
572 v_trx_number := pr_new.Trx_Number; --Ramananda for File.Sql.35
573 v_ref_line_id := pr_new.interface_header_attribute7; --Ramananda for File.Sql.35
574
575
576 IF pr_new.created_from = 'RAXTRX' THEN
577 IF pr_new.CUSTOMER_TRX_ID <> pr_old.CUSTOMER_TRX_ID
578 THEN
579 Update JAI_AR_TRXS
580 Set Customer_Trx_ID = pr_new.Customer_Trx_ID
581 Where Customer_Trx_ID = pr_old.Customer_Trx_ID;
582 Update JAI_AR_TRX_LINES
583 Set Customer_Trx_Id = pr_new.Customer_Trx_ID
584 Where Customer_Trx_ID = pr_old.Customer_Trx_ID;
585 END IF;
586
587
588 Update JAI_AR_TRXS
589 Set Trx_Number = pr_new.Trx_Number
590 Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
591 END IF;
592
593 OPEN transaction_type_cur;
594 FETCH transaction_type_cur INTO v_trans_type;
595 CLOSE transaction_type_cur;
596 IF NVL(v_trans_type,'N') in ('CM','DM') THEN
597
598 -- Start, Vijay Shankar for bug # 3181921
599 IF pr_new.created_from = 'RAXTRX' THEN
600 Update JAI_AR_TRXS
601 Set Trx_Number = pr_new.Trx_Number
602 Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
603 ELSE
604 -- End, Vijay Shankar for bug # 3181921
605
606 Update JAI_AR_TRXS
607 Set Trx_Number = pr_new.Trx_Number
608 , Once_Completed_Flag = NVL(pr_new.Complete_Flag,'N')
609 Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
610 END IF;
611
612 ELSIF NVL(v_trans_type,'N') = 'INV' THEN
613 Update JAI_AR_TRXS
614 Set Trx_Number = pr_new.Trx_Number
615 Where Customer_Trx_ID = pr_new.Customer_Trx_ID;
616 END IF;
617 /* Added an exception block by Ramananda for bug#4570303 */
618 EXCEPTION
619 WHEN OTHERS THEN
620 Pv_return_code := jai_constants.unexpected_error;
621 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARU_T2 ' || substr(sqlerrm,1,1900);
622 END ARU_T2 ;
623
624 /*
625 REM +======================================================================+
626 REM NAME ARU_T3
627 REM
628 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ARIUD_T1
629 REM
630 REM NOTES Refers to old trigger JAI_AR_RCTA_ARU_T4
631 REM
632 REM +======================================================================+
633 */
634 PROCEDURE ARU_T3 ( 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
635 v_organization_id NUMBER ;
636 v_loc_id NUMBER ;
637 v_trans_type RA_CUST_TRX_TYPES_ALL.TYPE%TYPE ;
638 lv_vat_invoice_no JAI_AR_TRXS.VAT_INVOICE_NO%TYPE ;
639 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
640 ln_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE ;
641 lv_process_flag VARCHAR2(10) ;
642 lv_process_message VARCHAR2(4000) ;
643 ld_gl_date RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE%TYPE ;
644 ld_vat_invoice_date JAI_AR_TRXS.VAT_INVOICE_DATE%TYPE ;
645
646
647 /*
648 || Get the organization, location, vat_invoice_no and vat_invoice_date from JAI_AR_TRXS
649 */
650 CURSOR organization_cur
651 IS
652 SELECT
653 organization_id ,
654 location_id ,
655 vat_invoice_no ,
656 vat_invoice_date
657 FROM
658 JAI_AR_TRXS
659 WHERE
660 customer_trx_id = pr_new.customer_trx_id;
661
662 /*
663 || Get the transaction type of the document
664 */
665 CURSOR transaction_type_cur
666 IS
667 SELECT
668 type
669 FROM
670 ra_cust_trx_types_all
671 WHERE
672 cust_trx_type_id = pr_new.cust_trx_type_id AND
673 NVL(org_id,0) = NVL(pr_new.org_id,0);
674
675
676 /*
677 || Check whether vat types of taxes exist for the CM.
678 || IF yes then get the regime id and regime code
679 */
680 CURSOR cur_vat_taxes_exist
681 IS
682 SELECT
683 regime_id ,
684 regime_code
685 FROM
686 JAI_AR_TRX_TAX_LINES jcttl,
687 JAI_AR_TRX_LINES jctl,
688 JAI_CMN_TAXES_ALL jtc ,
689 jai_regime_tax_types_v jrttv
690 WHERE
691 jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
692 jctl.customer_trx_id = pr_new.customer_trx_id AND
693 jcttl.tax_id = jtc.tax_id AND
694 jtc.tax_type = jrttv.tax_type AND
695 regime_code = jai_constants.vat_regime AND
696 jtc.org_id = pr_new.org_id ;
697
698
699 CURSOR cur_get_gl_date(cp_acct_class ra_cust_trx_line_gl_dist_all.account_class%type)
700 IS
701 SELECT
702 gl_date
703 FROM
704 ra_cust_trx_line_gl_dist_all
705 WHERE
706 customer_trx_id = pr_new.customer_trx_id AND
707 account_class = cp_acct_class AND /*--'REC' AND*/
708 latest_rec_flag = 'Y';
709
710 CURSOR cur_get_in_vat_no
711 IS
712 SELECT
713 vat_invoice_no
714 FROM
715 JAI_AR_TRXS
716 WHERE
717 customer_trx_id = pr_new.previous_customer_trx_id;
718
719 /*
720 || Added by kunkumar for bug#5645003
721 || Check if only 'VAT REVERSAL' tax type is present in ja_in_ra_cust_trx_tax_lines
722 */
723 CURSOR c_chk_vat_reversal (cp_tax_type jai_cmn_taxes_all.tax_type%TYPE )
724 IS
725 SELECT
726 1
727 FROM
728 JAI_AR_TRX_TAX_LINES jcttl,
729 JAI_AR_TRX_LINES jctl,
730 JAI_CMN_TAXES_ALL jtc
731 WHERE
732 jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
733 jctl.customer_trx_id = pr_new.customer_trx_id AND
734 jcttl.tax_id = jtc.tax_id AND
735 jtc.org_id = pr_new.org_id AND
736 jtc.tax_type = cp_tax_type ;
737
738 lv_vat_reversal VARCHAR2(30);
739 ln_vat_reversal_exists NUMBER;
740
741 /*
742 || Retrieve the regime_id which is of regime code 'VAT'
743 */
744 CURSOR c_get_regime_id
745 IS
746 SELECT
747 regime_id
748 FROM
749 jai_regime_tax_types_v
750 WHERE
751 regime_code = jai_constants.vat_regime
752 AND rownum = 1 ;
753 /******************************************************************************************************************
754 File name : ja_in_loc_ar_hdr_upd_trg_vat.sql
755 Change History :
756
757 1. 10-Aug-2005 Aiyer bug 4545146 version 120.1
758 Issue:-
759 Deadlock on tables due to multiple triggers on the same table (in different sql files)
760 firing in the same phase.
761 Fix:-
762 Multiple triggers on the same table have been merged into a single file to resolve
763 the problem
764 The following files have been stubbed:-
765 jai_ar_rcta_t1.sql
766 jai_ar_rcta_t2.sql
767 jai_ar_rcta_t3.sql
768 jai_ar_rcta_t4.sql
769 jai_ar_rcta_t6.sql
770 jai_ar_rcta_t7.sql
771 jai_ar_rcta_t8.sql
772 jai_ar_rcta_t9.sql
773 Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers
774
775 Future Dependencies For the release Of this Object:-
776 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
777 A datamodel change )
778 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
779 Current Version Current Bug Dependent Files Version Author Date Remarks
780 Of File On Bug/Patchset Dependent On
781
782 ja_in_loc_ar_hdr_upd_trg_vat.sql
783 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
784
785 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
786
787 BEGIN
788 pv_return_code := jai_constants.successful ;
789
790 /*
791 || Get the Organization and location info , vat_invoice_no, vat_invoice_date
792 */
793 OPEN organization_cur;
794 FETCH organization_cur INTO v_organization_id, v_loc_id,lv_vat_invoice_no,ld_vat_invoice_date ;
795 CLOSE organization_cur;
796 IF lv_vat_invoice_no IS NOT NULL OR
797 ld_vat_invoice_date IS NOT NULL
798 THEN
799 /*
800 || IF vat_invoice_no or vat_invoice_date has already been populated into this record (indicating that it has already been run once)
801 || then return.
802 */
803 return;
804 END IF;
805
806 /*
807 || Get the Otransaction type of the document
808 || Process only CM type of transaction's
809 */
810 OPEN transaction_type_cur;
811 FETCH transaction_type_cur INTO v_trans_type;
812 CLOSE transaction_type_cur;
813
814 IF NVL(v_trans_type,'N') <> 'CM' THEN
815 /*
816 || In case of CM only VAT accouting should be done.
817 */
818 return;
819 END IF;
820
821 OPEN cur_vat_taxes_exist;
822 FETCH cur_vat_taxes_exist into ln_regime_id,ln_regime_code;
823 CLOSE cur_vat_taxes_exist;
824
825 IF UPPER(nvl(ln_regime_code,'####')) <> UPPER(jai_constants.vat_regime) THEN
826 /*
827 || only vat type of taxes should be processed
828 */
829 return;
830 END IF;
831 /*
832 || Added by kunkumar for bug#5645003
833 || Check if only 'VAT REVERSAL' tax type is present in ja_in_ra_cust_trx_tax_lines
834 */
835 IF ln_regime_id IS NULL THEN
836 lv_vat_reversal := 'VAT REVERSAL' ;
837 OPEN c_chk_vat_reversal(lv_vat_reversal) ;
838 FETCH c_chk_vat_reversal INTO ln_vat_reversal_exists;
839 CLOSE c_chk_vat_reversal ;
840
841 /*
842 || Retrieve the regime_id for 'VAT REVERSAL' tax type, which is of regime code 'VAT'
843 */
844 IF ln_vat_reversal_exists = 1 THEN
845 OPEN c_get_regime_id ;
846 FETCH c_get_regime_id INTO ln_regime_id ;
847 CLOSE c_get_regime_id ;
848
849 IF ln_regime_id IS NOT NULL THEN
850 ln_regime_code := jai_constants.vat_regime ;
851 END IF ;
852 END IF ;
853 END IF ;
854 --bug#5645003, ends
855
856
857
858
859 /*
860 || Get the vat invoice number for the Credit Memo from the Source Invoice only if a CM has a source INvoice
861 || IF it is from legacy then the vat invoice number would go as null
862 */
863 IF pr_new.previous_customer_trx_id is NOT NULL THEN
864 OPEN cur_get_in_vat_no;
865 FETCH cur_get_in_vat_no INTO lv_vat_invoice_no;
866 CLOSE cur_get_in_vat_no ;
867 END IF;
868
869 /*
870 || Get the gl_date from ra_cust_trx_lines_gl_dist_all
871 */
872 OPEN cur_get_gl_date('REC'); /* Modified by Ramananda for removal of SQL LITERALs */
873 FETCH cur_get_gl_date INTO ld_gl_date;
874 CLOSE cur_get_gl_date;
875
876 /*
877 || IF the VAT invoice Number has been successfully generated, then pass accounting entries
878 */
879 jai_cmn_rgm_vat_accnt_pkg.process_order_invoice (
880 p_regime_id => ln_regime_id ,
881 p_source => jai_constants.source_ar ,
882 p_organization_id => v_organization_id ,
883 p_location_id => v_loc_id ,
884 p_delivery_id => NULL ,
885 p_customer_trx_id => pr_new.customer_trx_id ,
886 p_transaction_type => v_trans_type ,
887 p_vat_invoice_no => lv_vat_invoice_no ,
888 p_default_invoice_date => nvl(ld_gl_date,pr_new.trx_date) ,
889 p_batch_id => NULL ,
890 p_called_from => 'JA_IN_LOC_AR_HDR_UPD_TRG_VAT' , /* The string 'JA_IN_LOC_AR_HDR_UPD_TRG_VAT' is also being used in jai_cmn_rgm_vat_accnt_pkg.process_order_invoice*/
891 p_debug => jai_constants.no ,
892 p_process_flag => lv_process_flag ,
893 p_process_message => lv_process_message
894 );
895
896 IF lv_process_flag = jai_constants.expected_error OR
897 lv_process_flag = jai_constants.unexpected_error
898 THEN
899
900 /* raise_application_error(-20130,lv_process_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_process_message ; return ;
901 /*
902 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
903 EXCEPTION_CODE => NULL ,
904 EXCEPTION_TEXT => lv_process_message
905 );
906 */
907
908 END IF;
909
910 UPDATE
911 JAI_AR_TRXS
912 SET
913 vat_invoice_no = lv_vat_invoice_no ,
914 vat_invoice_date = nvl(ld_gl_date,pr_new.trx_date)
915 WHERE
916 customer_trx_id = pr_new.customer_trx_id ;
917
918 /* Added an exception block by Ramananda for bug#4570303 */
919 EXCEPTION
920 WHEN OTHERS THEN
921 Pv_return_code := jai_constants.unexpected_error;
922 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARU_T3 ' || substr(sqlerrm,1,1900);
923
924 END ARU_T3 ;
925
926 /*
927 REM +======================================================================+
928 REM NAME ARU_T4
929 REM
930 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ARIUD_T1
931 REM
932 REM NOTES Refers to old trigger JAI_AR_RCTA_ARU_T6
933 REM
934 REM+=======================================================================+
935 REM Change History
936 REM slno Date Name BugNo File Version
937 REM +=======================================================================+
938 REM
939 REM
940 REM -----------------------------------------------------------------------
941 REM 1. 04-Jul-2006 aiyer 5364288 120.3
942 REM -----------------------------------------------------------------------
943 REM Comments:-
944 REM Removed references to ra_customer_trx_all and replaced it with jai_ar_trx.
945 REM -----------------------------------------------------------------------
946 REM 2.
947 REM -----------------------------------------------------------------------
948 REM -----------------------------------------------------------------------
949 REM 3.
950 REM -----------------------------------------------------------------------
951 REM -----------------------------------------------------------------------
952 REM 4.
953 REM -----------------------------------------------------------------------
954 REM
955 REM
956 REM+======================================================================+
957 */
958 PROCEDURE ARU_T4 ( 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
959 v_org_id NUMBER;
960 v_loc_id NUMBER;
961 v_reg_code VARCHAR2(30);
962 v_update_rg VARCHAR2(1);
963 v_reg_type VARCHAR2(10);
964 v_excise_paid_register VARCHAR2(10);
965 v_rg23a_type VARCHAR2(10);
966 v_rg23c_type VARCHAR2(10);
967 v_complete_flag VARCHAR2(1); -- := 'N'; --Ramananda for File.Sql.35
968 v_rg_flag VARCHAR2(1); -- := 'N'; --Ramananda for File.Sql.35
969 v_update_rg_flag VARCHAR2(1); -- := 'N'; --Ramananda for File.Sql.35
970 -- v_update_rg23d_flag VARCHAR2(30); /*Bug 5040383*/
971 v_tax_amount NUMBER := 0;
972 v_rg23a_tax_amount NUMBER := 0;
973 v_rg23c_tax_amount NUMBER := 0;
974 v_other_tax_amount NUMBER := 0;
975 v_basic_ed NUMBER := 0;
976 v_additional_ed NUMBER := 0;
977 v_other_ed NUMBER := 0;
978 v_item_class VARCHAR2(10); -- := 'N'; --Ramananda for File.Sql.35
979 v_excise_flag VARCHAR2(1);
980 v_fin_year NUMBER;
981 v_gp_1 NUMBER := 0;
982 v_gp_2 NUMBER := 0;
983 v_rg23a_bal NUMBER := 0;
984 v_rg23c_bal NUMBER := 0;
985 v_pla_bal NUMBER := 0;
986 v_invoice_no VARCHAR2(200);
987 v_other_invoice_no NUMBER ;
988 v_rg23a_invoice_no NUMBER ;
989 v_rg23c_invoice_no NUMBER ;
990 rg23a NUMBER :=0;
991 rg23c NUMBER :=0;
992 pla NUMBER :=0;
993 --v_row_id ROWID; -- := pr_new.ROWID; --Ramananda for File.Sql.35
994 v_parent_trx_number VARCHAR2(20); -- := pr_new.recurred_from_trx_number; --Ramananda for File.Sql.35
995 v_register_balance NUMBER := 0;
996 v_rg23d_register_balance NUMBER := 0;
997 v_customer_trx_id NUMBER; -- := pr_old.customer_trx_id; --Ramananda for File.Sql.35
998 v_converted_rate NUMBER := 1;
999 v_ssi_unit_flag VARCHAR2(1);
1000 v_trans_type VARCHAR2(30);
1001 v_last_update_date DATE; -- := pr_new.last_update_date; --Ramananda for File.Sql.35
1002 v_last_updated_by NUMBER; -- := pr_new.last_updated_by; --Ramananda for File.Sql.35
1003 v_creation_date DATE; -- := pr_new.creation_date; --Ramananda for File.Sql.35
1004 v_created_by NUMBER; -- := pr_new.created_by; --Ramananda for File.Sql.35
1005 v_last_update_login NUMBER; -- := pr_new.last_update_login; --Ramananda for File.Sql.35
1006 v_bond_tax_amount NUMBER := 0;
1007 V_rg23d_tax_amount NUMBER := 0;
1008 v_modvat_tax_rate NUMBER;
1009 v_exempt_bal NUMBER;
1010 v_matched_qty NUMBER;
1011 VSQLERRM VARCHAR2(240);
1012 v_trans_type_up VARCHAR2(3);
1013 v_order_invoice_type_up VARCHAR2(25);---ashish 10june
1014 v_register_code_up VARCHAR2(25);---ashish 10june
1015 v_errbuf VARCHAR2(250);
1016 -- added by sriram - bug # 3021588
1017 v_register_id JAI_OM_OE_BOND_REG_HDRS.register_id%type;
1018 v_register_exp_date JAI_OM_OE_BOND_REG_HDRS.bond_expiry_date%type;
1019 v_lou_flag JAI_OM_OE_BOND_REG_HDRS.lou_flag%type;
1020 -- added by sriram - bug # 3021588
1021 v_trading_flag JAI_CMN_INVENTORY_ORGS.TRADING%TYPE;/*Bug#4601570 bduvarag*/
1022 v_update_rg23d_flag JAI_AR_TRXS.UPDATE_RG23D_FLAG%TYPE;/*Bug#4601570 bduvarag*/
1023
1024 /*
1025 || Start of bug 4101549
1026 || Cursor modified by aiyer
1027 */
1028 CURSOR complete_cur
1029 IS
1030 SELECT
1031 organization_id ,
1032 location_id ,
1033 once_completed_flag ,
1034 decode(once_completed_flag,'A','RG23A','C','RG23C','P','PLA') register_type,
1035 update_rg_flag, -- update_rg_flag added by sriram - bug# 3496577
1036 nvl(update_rg23d_flag,'N') /*Bug 5040383*/
1037 FROM
1038 JAI_AR_TRXS
1039 WHERE
1040 customer_trx_id = v_customer_trx_id;
1041
1042
1043 --2001/06/22 Anuradha Parthasarathy
1044 CURSOR REG_BALANCE_CUR(p_org_id IN NUMBER,p_loc_id IN NUMBER) IS
1045 SELECT NVL(rg23a_balance,0) rg23a_balance ,NVL(rg23c_balance,0) rg23c_balance,NVL(pla_balance,0) pla_balance
1046 FROM JAI_CMN_RG_BALANCES
1047 WHERE organization_id = p_org_id AND location_id = p_loc_id;
1048
1049 CURSOR register_code_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1050 SELECT register_code
1051 FROM JAI_OM_OE_BOND_REG_HDRS
1052 WHERE organization_id = p_org_id AND location_id = p_loc_id AND
1053 register_id IN (SELECT register_id
1054 FROM JAI_OM_OE_BOND_REG_DTLS
1055 WHERE order_type_id = pr_new.batch_source_id AND order_flag= 'N'); /* Modified by Ramananda for removal of SQL LITERALs */
1056
1057 CURSOR fin_year_cur(p_org_id IN NUMBER) IS
1058 SELECT MAX(A.fin_year)
1059 FROM JAI_CMN_FIN_YEARS A
1060 WHERE organization_id = p_org_id AND fin_active_flag = 'Y';
1061
1062 CURSOR tax_amount_cur IS
1063 SELECT NVL(tax_amount,0) tax_amount
1064 FROM JAI_AR_TRXS
1065 WHERE customer_trx_id = v_customer_trx_id;
1066
1067 CURSOR preference_reg_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1068 SELECT pref_rg23a , pref_rg23c , pref_pla
1069 FROM JAI_CMN_INVENTORY_ORGS
1070 WHERE organization_id = p_org_id AND
1071 location_id = p_loc_id;
1072
1073 CURSOR item_class_cur(P_ORG_ID IN NUMBER, P_Item_id IN NUMBER) IS
1074 SELECT item_class, excise_flag
1075 FROM JAI_INV_ITM_SETUPS
1076 WHERE inventory_item_id = P_Item_Id AND
1077 ORGANIZATION_ID = P_ORG_ID;
1078
1079 CURSOR organization_cur IS
1080 SELECT organization_id,location_id
1081 FROM JAI_AR_TRXS
1082 WHERE trx_number = v_parent_trx_number;
1083
1084 CURSOR register_balance_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1085 SELECT NVL(register_balance,0) register_balance
1086 FROM JAI_OM_OE_BOND_TRXS
1087 WHERE transaction_id = (SELECT MAX(A.transaction_id)
1088 FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
1089 WHERE A.register_id = B.register_id
1090 AND B.organization_id = p_org_id AND B.location_id = p_loc_id);
1091
1092 CURSOR register_balance_cur1(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1093 SELECT NVL(rg23d_register_balance,0) rg23d_register_balance
1094 FROM JAI_OM_OE_BOND_TRXS
1095 WHERE transaction_id = (SELECT MAX(A.transaction_id)
1096 FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
1097 WHERE A.register_id = B.register_id
1098 AND B.organization_id = p_org_id AND B.location_id = p_loc_id);
1099
1100 CURSOR line_cur IS
1101 SELECT customer_trx_line_id, inventory_item_id, quantity,line_number,
1102 excise_exempt_type, assessable_value
1103 FROM JAI_AR_TRX_LINES
1104 WHERE customer_trx_id = v_customer_trx_id
1105 ORDER BY customer_trx_line_id;
1106
1107 CURSOR matched_qty_cur (p_customer_trx_line_id NUMBER) IS
1108 SELECT SUM(quantity_applied)
1109 FROM JAI_CMN_MATCH_RECEIPTS
1110 WHERE ref_line_id = p_customer_trx_line_id;
1111
1112 CURSOR excise_cal_cur(p_line_id IN NUMBER, p_inventory_item_id IN NUMBER, p_org_id IN NUMBER) IS
1113 SELECT
1114 A.tax_id,
1115 A.tax_rate t_rate,
1116 A.tax_amount tax_amt,
1117 A.func_tax_amount func_amt,
1118 b.tax_type t_type,
1119 b.stform_type,
1120 A.tax_line_no
1121 FROM JAI_AR_TRX_TAX_LINES A ,
1122 JAI_CMN_TAXES_ALL B,
1123 JAI_INV_ITM_SETUPS C
1124 WHERE link_to_cust_trx_line_id = p_line_id
1125 AND b.tax_type IN --('Excise','Addl. Excise','Other Excise') /* Modified by Ramananda for removal of SQL LITERALs */
1126 (jai_constants.tax_type_excise,jai_constants.tax_type_exc_additional,jai_constants.tax_type_exc_other)
1127 AND A.tax_id = b.tax_id
1128 AND c.inventory_item_id = p_inventory_item_id
1129 AND c.organization_id = p_org_id
1130 --AND c.item_class IN ('RMIN','RMEX','CGEX','CGIN','CCEX','CCIN','FGIN','FGEX') /* Modified by Ramananda for removal of SQL LITERALs */
1131 AND c.item_class IN ( jai_constants.item_class_rmin, jai_constants.item_class_rmex,
1132 jai_constants.item_class_cgex, jai_constants.item_class_cgin,
1133 jai_constants.item_class_ccex, jai_constants.item_class_ccin,
1134 jai_constants.item_class_fgin, jai_constants.item_class_fgex
1135 )
1136 ORDER BY 1;
1137
1138 CURSOR ssi_unit_flag_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1139 SELECT ssi_unit_flag, nvl(trading,'N')/*Bug#4601570 bduvarag*/
1140 FROM JAI_CMN_INVENTORY_ORGS
1141 WHERE organization_id = p_org_id AND
1142 location_id = p_loc_id;
1143
1144 CURSOR transaction_type_cur IS
1145 SELECT TYPE
1146 FROM RA_CUST_TRX_TYPES_ALL
1147 WHERE cust_trx_type_id = pr_new.cust_trx_type_id
1148 AND (org_id = pr_new.org_id
1149 OR
1150 (org_id is null and pr_new.org_id is null)) ; /* Modified by Ramananda for removal of SQL LITERALs */
1151
1152
1153 /* Code Added For Generation of Excise Invoice Number */
1154 CURSOR Batch_Source_Name_Cur IS
1155 SELECT name
1156 FROM Ra_Batch_Sources_All
1157 WHERE batch_source_id = pr_new.batch_source_id
1158 AND (org_id = pr_new.org_id
1159 OR
1160 ( org_id is null AND pr_new.org_id is null)); /* Modified by Ramananda for removal of SQL LITERALs */
1161
1162 --------------chnages in cursor definition
1163
1164 CURSOR Def_Excise_Invoice_Cur(p_organization_id IN NUMBER, p_location_id IN NUMBER, p_fin_year IN NUMBER,
1165 p_batch_name IN VARCHAR2, p_register_code IN VARCHAR2) IS
1166 SELECT start_number, end_number, jump_by, prefix
1167 FROM JAI_CMN_RG_EXC_INV_NOS
1168 WHERE organization_id = p_organization_id
1169 AND location_id = p_location_id
1170 AND fin_year = p_fin_year
1171 AND transaction_type IN ( 'I','DOM','EXP') --ashish 20jun02
1172 AND order_invoice_type = p_batch_name
1173 AND register_code = p_register_code ; /* Modified by Ramananda for removal of SQL LITERALs */
1174
1175 CURSOR excise_invoice_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER, p_fin_year IN NUMBER) IS
1176 SELECT NVL(MAX(GP1),0),NVL(MAX(GP2),0)
1177 FROM JAI_CMN_RG_EXC_INV_NOS
1178 WHERE organization_id = p_org_id
1179 AND location_id = p_loc_id
1180 AND fin_year = p_fin_year
1181 AND transaction_type IS NULL
1182 AND order_invoice_type IS NULL
1183 AND register_code IS NULL;
1184
1185 CURSOR Register_Code_Meaning_Cur(p_register_code IN VARCHAR2,cp_register_type ja_lookups.lookup_type%type ) IS
1186 SELECT meaning
1187 FROM ja_lookups
1188 WHERE lookup_code = p_register_code
1189 AND lookup_type = cp_register_type; /*'JAI_REGISTER_TYPE'; Ramananda for removal of SQL LITERALs */
1190
1191 --added by GD
1192 CURSOR for_modvat_percentage(v_org_id NUMBER, v_location_id NUMBER) IS
1193 SELECT MODVAT_REVERSE_PERCENT
1194 FROM JAI_CMN_INVENTORY_ORGS
1195 WHERE organization_id = v_org_id
1196 AND (location_id = v_location_id
1197 OR
1198 (location_id is NULL and v_location_id is NULL)); /* Modified by Ramananda for removal of SQL LITERALs */
1199
1200 CURSOR for_modvat_tax_rate(p_cust_trx_line_id NUMBER) IS
1201 SELECT A.tax_rate
1202 FROM JAI_AR_TRX_TAX_LINES A, JAI_CMN_TAXES_ALL b
1203 WHERE A.tax_id = b.tax_id
1204 AND A.link_to_cust_trx_line_id = p_cust_trx_line_id
1205 AND b.tax_type = jai_constants.tax_type_modvat_recovery ; --'Modvat Recovery';
1206
1207 --added by GD
1208 v_start_number NUMBER;
1209 v_end_number NUMBER;
1210 v_jump_by NUMBER;
1211 v_order_invoice_type VARCHAR2(50);
1212 v_prefix VARCHAR2(50);
1213 v_meaning VARCHAR2(80);
1214 v_set_of_books_id NUMBER; -- := pr_new.set_of_books_id; --Ramananda for File.Sql.35
1215 /* Bug 5243532. Added by Lakshmi Gopalsami
1216 Removed the reference to set_of_books_cur
1217 which is selecting SOB from org_organization_definitions
1218 as the SOB will never by null in base table.
1219 */
1220 /* CODE ADDED TO INCORPORATE MASTER ORGANIZATION */
1221 CURSOR ec_code_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
1222 SELECT A.Organization_Id, A.Location_Id
1223 FROM JAI_CMN_INVENTORY_ORGS A
1224 WHERE A.Ec_Code IN (SELECT B.Ec_Code
1225 FROM JAI_CMN_INVENTORY_ORGS B
1226 WHERE B.Organization_Id = p_organization_id
1227 AND B.Location_Id = p_location_id);
1228
1229 --3661746
1230 CURSOR c_total_Excise_amt IS
1231 SELECT nvl(sum(jrtl.func_tax_amount),0)
1232 FROM JAI_AR_TRXS jtrx,
1233 JAI_AR_TRX_LINES jtl,
1234 JAI_AR_TRX_TAX_LINES jrtl,
1235 JAI_CMN_TAXES_ALL jtc ,
1236 JAI_INV_ITM_SETUPS jmtl
1237 WHERE jrtl.tax_id = jtc.tax_id
1238 AND jtrx.customer_trx_id = jtl.customer_Trx_id
1239 AND jrtl.link_to_cust_trx_line_id = jtl.customer_trx_line_id
1240 AND jtl.inventory_item_id = jmtl.inventory_item_id
1241 AND jtrx.organization_id = jmtl.organization_id
1242 --AND jmtl.item_class in ('RMIN','RMEX','CGEX','CGIN','FGIN','FGEX','CCIN','CCEX') /* Modified by Ramananda for removal of SQL LITERALs */
1243 AND jmtl.item_class IN ( jai_constants.item_class_rmin, jai_constants.item_class_rmex,
1244 jai_constants.item_class_cgex, jai_constants.item_class_cgin,
1245 jai_constants.item_class_ccex, jai_constants.item_class_ccin,
1246 jai_constants.item_class_fgin, jai_constants.item_class_fgex
1247 )
1248 AND jtc.tax_type like '%Excise%'
1249 AND jtl.customer_trx_id = pr_new.customer_trx_id
1250 AND jtrx.customer_trx_id = pr_new.customer_trx_id;
1251
1252 v_total_excise_amt NUMBER :=0;
1253
1254 CURSOR c_cess_amount is
1255 SELECT NVL(SUM(jrctl.func_tax_amount),0) tax_amount
1256 FROM JAI_AR_TRX_TAX_LINES jrctl ,
1257 JAI_CMN_TAXES_ALL jtc
1258 WHERE jtc.tax_id = jrctl.tax_id
1259 AND link_to_cust_trx_line_id IN
1260 (SELECT customer_trx_line_id
1261 FROM JAI_AR_TRX_LINES
1262 WHERE customer_trx_id = pr_new.customer_trx_id
1263 )
1264 AND upper(jtc.tax_type) IN (upper(jai_constants.tax_type_cvd_edu_cess), upper(jai_constants.tax_type_exc_edu_cess));
1265
1266 -- Start of bug 4185033
1267 /*
1268 || Cursor added by aiyer for the bug 4185033
1269 || Check whether the JAI_AR_TRX_INS_LINES_T table still has the row corresponding to the current
1270 || customer_trx_id
1271 */
1272 CURSOR cur_chk_temp_lines_exist( cp_customer_trx_id JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE ) /* changed the RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE to JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE for the bug 5364288 */
1273 IS
1274 SELECT
1275 1
1276 FROM
1277 JAI_AR_TRX_INS_LINES_T
1278 WHERE
1279 customer_trx_id = cp_customer_trx_id ;
1280
1281 -- End of bug 4185033
1282
1283 CURSOR c_vat_invoice_cur
1284 IS
1285 SELECT
1286 vat_invoice_no
1287 FROM JAI_AR_TRXS
1288 WHERE customer_trx_id = pr_new.customer_trx_id;
1289
1290 CURSOR cur_vat_taxes_exist
1291 IS
1292 SELECT
1293 regime_id ,
1294 regime_code
1295 FROM
1296 JAI_AR_TRX_TAX_LINES jcttl,
1297 JAI_AR_TRX_LINES jctl,
1298 JAI_CMN_TAXES_ALL jtc ,
1299 jai_regime_tax_types_v jrttv
1300 WHERE
1301 jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
1302 jctl.customer_trx_id = pr_new.customer_trx_id AND
1303 jcttl.tax_id = jtc.tax_id AND
1304 jtc.tax_type = jrttv.tax_type AND
1305 regime_code = jai_constants.vat_regime AND
1306 jtc.org_id = pr_new.org_id ;
1307
1308 /*
1309 || Added by kunkumar for bug#5645003
1310 || Check if only 'VAT REVERSAL' tax type is present in ja_in_ra_cust_trx_tax_lines
1311 */
1312 CURSOR c_chk_vat_reversal (cp_tax_type jai_cmn_taxes_all.tax_type%TYPE )
1313 IS
1314 SELECT
1315 1
1316 FROM
1317 JAI_AR_TRX_TAX_LINES jcttl,
1318 JAI_AR_TRX_LINES jctl,
1319 JAI_CMN_TAXES_ALL jtc
1320 WHERE
1321 jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
1322 jctl.customer_trx_id = pr_new.customer_trx_id AND
1323 jcttl.tax_id = jtc.tax_id AND
1324 jtc.org_id = pr_new.org_id AND
1325 jtc.tax_type = cp_tax_type ;
1326
1327 /*
1328 || Retrieve the regime_id which is of regime code 'VAT'
1329 */
1330 CURSOR c_get_regime_id
1331 IS
1332 SELECT
1333 regime_id
1334 FROM
1335 jai_regime_tax_types_v
1336 WHERE
1337 regime_code = jai_constants.vat_regime
1338 AND rownum = 1 ;
1339
1340 ln_vat_reversal_exists NUMBER ;
1341 lv_vat_reversal VARCHAR2(100);
1342 --bug#5645003, ends
1343
1344
1345
1346 CURSOR cur_get_same_inv_no ( cp_organization_id JAI_AR_TRXS.ORGANIZATION_ID%TYPE ,
1347 cp_location_id JAI_AR_TRXS.LOCATION_ID%TYPE
1348 )
1349 IS
1350 SELECT
1351 nvl(attribute_value ,'N') attribute_value
1352 FROM
1353 JAI_RGM_ORG_REGNS_V
1354 WHERE
1355 regime_code = jai_constants.vat_regime AND
1356 attribute_type_code = jai_constants.regn_type_others AND /*'OTHERS' AND */
1357 attribute_code = jai_constants.attr_code_same_inv_no AND /*'SAME_INVOICE_NO' AND */
1358 organization_id = cp_organization_id AND
1359 location_id = cp_location_id;
1360
1361 CURSOR cur_get_exc_inv_no
1362 IS
1363 SELECT
1364 excise_invoice_no
1365 FROM
1366 JAI_AR_TRX_LINES
1367 WHERE
1368 customer_trx_id = pr_new.customer_trx_id ;
1369
1370
1371 CURSOR cur_get_gl_date(cp_account_class ra_cust_trx_line_gl_dist_all.account_class%type)
1372 IS
1373 SELECT
1374 gl_date
1375 FROM
1376 ra_cust_trx_line_gl_dist_all
1377 WHERE
1378 customer_trx_id = pr_new.customer_trx_id AND
1379 account_class = cp_account_class AND /* 'REC' AND -- Ramananda for removal of SQL LITERALs */
1380 latest_rec_flag = 'Y';
1381
1382
1383
1384 ln_exists NUMBER ;
1385 ln_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;
1386 lv_process_flag VARCHAR2(2);
1387 lv_process_message VARCHAR2(1996);
1388 lv_register_type VARCHAR2(5);
1389 lv_rg23a_cess_avlbl VARCHAR2(10);
1390 lv_rg23c_cess_avlbl VARCHAR2(10);
1391 lv_pla_cess_avlbl VARCHAR2(10);
1392 lv_vat_invoice_number JAI_AR_TRXS.VAT_INVOICE_NO%TYPE;
1393 lv_vat_taxes_exist VARCHAR2(1);
1394 lv_vat_no_same_exc_no JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE; -- := 'N'; --Ramananda for File.Sql.35
1395 ld_gl_date RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE%TYPE;
1396 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE;
1397 ln_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
1398
1399
1400 --3661746
1401
1402 lv_doc_type_class varchar2(2); /* csahoo for seperate vat invoice num for unreg dealers - bug# 5233925*/
1403
1404 /* CODE ADDED TILL TO INCORPORATE MASTER ORGANIZATION */
1405 BEGIN
1406 pv_return_code := jai_constants.successful ;
1407 /*------------------------------------------------------------------------------------------
1408 FILENAME: JA_IN_LOC_AR_HDR_UPDATE_TRG.sql
1409 CHANGE HISTORY:
1410 S.No Date Author and Details
1411 1. 10-Aug-2005 Aiyer bug 4545146 version 120.1
1412 Issue:-
1413 Deadlock on tables due to multiple triggers on the same table (in different sql files)
1414 firing in the same phase.
1415 Fix:-
1416 Multiple triggers on the same table have been merged into a single file to resolve
1417 the problem
1418 The following files have been stubbed:-
1419 jai_ar_rcta_t1.sql
1420 jai_ar_rcta_t2.sql
1421 jai_ar_rcta_t3.sql
1422 jai_ar_rcta_t4.sql
1423 jai_ar_rcta_t6.sql
1424 jai_ar_rcta_t7.sql
1425 jai_ar_rcta_t8.sql
1426 jai_ar_rcta_t9.sql
1427 Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers in the above files
1428 2 09-Mar-2007 ssawant for the bug#5040383, File version 120.6
1429 Forward porting the changes done in bug#4998378
1430 bduvarag for the bug#5171573, File version 120.6
1431 Forward porting the changes done in bug#5057544
1432 3 17/05/2007 bduvarag for the bug#4601570, File version 120.14
1433 Forward porting the changes done in bug#4474270
1434 Future Dependencies For the release Of this Object:-
1435 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
1436 A datamodel change )
1437
1438 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
1439 Current Version Current Bug Dependent Files Version Author Date Remarks
1440 Of File On Bug/Patchset Dependent On
1441 ja_in_loc_ar_hdr_update_trg.sql
1442 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
1443
1444 ----------------------------------------------------------------------------------------------------------------------------------------------------
1445
1446 --------------------------------------------------------------------------------------------*/
1447 -----Once Complete Button is Pressed Following code tell you what will happen at what stage
1448
1449 /* --Ramananda for File.Sql.35, start */
1450 v_complete_flag := 'N';
1451 v_rg_flag := 'N';
1452 v_update_rg_flag := 'N';
1453 v_item_class := 'N';
1454 v_parent_trx_number := pr_new.recurred_from_trx_number;
1455 v_customer_trx_id := pr_old.customer_trx_id;
1456 v_last_update_date := pr_new.last_update_date;
1457 v_last_updated_by := pr_new.last_updated_by;
1458 v_creation_date := pr_new.creation_date;
1459 v_created_by := pr_new.created_by;
1460 v_last_update_login := pr_new.last_update_login;
1461 v_set_of_books_id := pr_new.set_of_books_id;
1462 lv_vat_no_same_exc_no := 'N';
1463 /* --Ramananda for File.Sql.35, end */
1464
1465 -- Start of bug 4185033
1466 /*
1467 || This code has been added by aiyer for the bug 4185033
1468 || Stop the processing before if the user tries to complete the Manual AR invoice before the Ar TAx and Fregiht DEfaultation is complete.
1469 || This is essential as otherwise it would lead to data corruption. i.e ra_cust_trx_lines_gl_dist_all would be out of sync with
1470 || ar_payment_schedule_all
1471 */
1472 IF pr_new.created_from = 'ARXTWMAI' THEN
1473 OPEN cur_chk_temp_lines_exist( cp_customer_trx_id => v_customer_trx_id );
1474 FETCH cur_chk_temp_lines_exist INTO ln_exists;
1475 IF CUR_CHK_TEMP_LINES_EXIST%FOUND THEN
1476 CLOSE cur_chk_temp_lines_exist;
1477 /* raise_application_error(-20121,'IL Tax not applied - Please wait for AR Tax and Freight Defaultation Concurrent Request to complete');
1478 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'IL Tax not applied - Please wait for AR Tax and Freight Defaultation Concurrent Request to complete' ; return ;
1479 END IF ;
1480 CLOSE cur_chk_temp_lines_exist;
1481 END IF;
1482 -- End of bug 4185033
1483 --Added the below for Bug 5040383
1484
1485 OPEN transaction_type_cur;
1486 FETCH transaction_type_cur INTO v_trans_type;
1487 CLOSE transaction_type_cur;
1488
1489 --Added the below for Bug 5040383
1490 OPEN Complete_Cur;
1491 FETCH Complete_Cur INTO v_org_id, v_loc_id,v_complete_flag,v_reg_type, v_update_rg_flag,v_update_rg23d_flag;
1492 CLOSE Complete_Cur;
1493
1494 IF pr_new.COMPLETE_FLAG <> pr_old.COMPLETE_FLAG THEN
1495
1496 /* --commented for bug 5040383
1497 OPEN Complete_Cur;
1498 FETCH Complete_Cur INTO v_org_id, v_loc_id,v_complete_flag,v_reg_type, v_update_rg_flag;
1499 CLOSE Complete_Cur;
1500 */
1501
1502 v_rg_flag := v_update_rg_flag;
1503
1504 IF NVL(v_complete_flag,'N') = 'Y' THEN
1505 RETURN;
1506 END IF;
1507 /*
1508 --commented for bug 5040383
1509 OPEN transaction_type_cur;
1510 FETCH transaction_type_cur INTO v_trans_type;
1511 CLOSE transaction_type_cur;
1512 */
1513
1514 IF NVL(v_trans_type,'N') <> 'INV' THEN
1515 /*Bug 5171573 bduvarag start*/
1516 UPDATE JAI_AR_TRXS
1517 SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
1518 WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
1519 /*Bug 5171573 bduvarag End*/
1520 RETURN;
1521 END IF;
1522 IF pr_new.created_from = 'RAXTRX' THEN
1523 UPDATE JAI_AR_TRXS
1524 SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
1525 WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
1526 ELSE
1527
1528 IF NVL(v_org_id, 999999) = 999999 THEN -- ssumaith --- changed 0 to 999999 because trigger was returning in case where
1529 -- setup business group is done. Bug # 2846277
1530 IF v_parent_trx_number IS NULL THEN
1531 RETURN;
1532 ELSE
1533 OPEN organization_cur;
1534 FETCH organization_cur INTO v_org_id, v_loc_id;
1535 CLOSE organization_cur;
1536 v_rg_flag := 'Y';
1537 END IF;
1538 END IF;
1539 IF NVL(v_org_id, 999999) = 999999 THEN -- ssumaith - -- changed 0 to 999999 because trigger was returning in case where
1540 -- setup business group is done. Bug # 2846277
1541 RETURN;
1542 END IF;
1543 -- above code segment commented by sriram - calling the procedure instead -- bug # 3021588
1544 jai_cmn_bond_register_pkg.GET_REGISTER_ID (v_org_id,
1545 v_loc_id,
1546 NVL(pr_new.batch_source_id,0),
1547 'N',
1548 v_register_id ,
1549 v_reg_code
1550 );
1551 -- ends here code added by sriram - Bug # 3021588
1552
1553 OPEN register_code_meaning_cur(v_reg_code, 'JAI_REGISTER_TYPE'); /* Modified by Ramananda for removal of SQL LITERALs */
1554 FETCH register_code_meaning_cur INTO v_meaning;
1555 CLOSE register_code_meaning_cur;
1556 OPEN fin_year_cur(v_org_id);
1557 FETCH fin_year_cur INTO v_fin_year;
1558 CLOSE fin_year_cur;
1559 OPEN Batch_Source_Name_Cur;
1560 FETCH Batch_Source_Name_Cur INTO v_order_invoice_type;
1561 CLOSE Batch_Source_Name_Cur;
1562
1563
1564
1565
1566 IF v_reg_code IN ('DOMESTIC_EXCISE','EXPORT_EXCISE','DOM_WITHOUT_EXCISE','BOND_REG') THEN
1567 v_rg_flag := 'Y';
1568 -- following comparision values made into upper case by sriram -bug # 3179379
1569 ELSIF upper(v_reg_code) IN ('23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE',
1570 '23D_DOM_WITHOUT_EXCISE','23D_EXPORT_WITHOUT_EXCISE')THEN
1571 v_rg_flag := 'N';
1572 END IF;
1573
1574 v_update_rg_flag := 'Y';-- bug#3496577 -- setting the value to 'Y' because the update_rg_flag has to only impact
1575 -- amount registers and not quantity registers and excise invoice generation.
1576
1577 OPEN REG_BALANCE_CUR(v_org_id, v_loc_id);
1578 FETCH REG_BALANCE_CUR INTO v_rg23a_bal,v_rg23c_bal,v_pla_bal;
1579 CLOSE REG_BALANCE_CUR;
1580 OPEN ssi_unit_flag_cur(v_org_id, v_loc_id);
1581 FETCH ssi_unit_flag_cur INTO v_ssi_unit_flag, v_trading_flag;/*Bug#4601570 bduvarag*/
1582 CLOSE ssi_unit_flag_cur;
1583
1584 /*
1585 ||Start of bug 4101549
1586 || IF condition modified forthe bug 4101549
1587 ||The complete flag statuses should be 'A','P','C','N'
1588 */
1589 IF NVL(v_complete_flag,'N') IN ('N','A','C','P') AND
1590 (v_rg_flag = 'Y' OR v_update_rg_flag = 'Y') AND
1591 v_reg_code IS NOT NULL
1592 THEN
1593 /*
1594 ||End of bug 4101549
1595 */
1596 FOR Line_Rec IN Line_Cur LOOP
1597 FOR excise_cal_rec IN excise_cal_cur(Line_Rec.customer_trx_line_id, Line_Rec.Inventory_Item_ID, v_org_id) LOOP
1598 IF excise_cal_rec.t_type IN ('Excise') THEN
1599 v_basic_ed := NVL(v_basic_ed,0) + NVL(excise_cal_rec.func_amt,0);
1600 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
1601 v_additional_ed := NVL(v_additional_ed,0) + NVL(excise_cal_rec.func_amt,0);
1602 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
1603 v_other_ed := NVL(v_other_ed,0) + NVL(excise_cal_rec.func_amt,0);
1604 END IF;
1605 END LOOP;
1606 v_tax_amount := NVL(v_basic_ed,0) + NVL(v_additional_ed,0) + NVL(v_other_ed,0);
1607 IF v_reg_code IN ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
1608 OPEN item_class_cur(V_ORG_ID,Line_Rec.Inventory_Item_Id);
1609 FETCH item_class_cur INTO v_item_class, v_excise_flag;
1610 CLOSE item_class_cur;
1611
1612
1613 IF NVL(v_excise_flag,'N') = 'Y' THEN
1614 IF NVL(v_ssi_unit_flag,'N') = 'N'
1615 AND NVL(line_rec.excise_exempt_type, '@@@') NOT IN ('CT2', 'EXCISE_EXEMPT_CERT','CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH' )
1616 THEN
1617 IF v_item_class IN ('CGEX','CGIN') THEN
1618 v_rg23c_tax_amount := NVL(v_rg23c_tax_amount,0) + NVL(v_tax_amount,0);
1619 ELSIF v_item_class IN ('RMIN','RMEX') THEN
1620 v_rg23a_tax_amount := NVL(v_rg23a_tax_amount,0) + NVL(v_tax_amount,0);
1621 ELSIF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') THEN
1622 v_other_tax_amount := NVL(v_other_tax_amount,0) + NVL(v_tax_amount,0);
1623 -------------ADDED BY GD {
1624 ELSIF NVL(v_ssi_unit_flag,'N') = 'N' AND
1625 NVL(line_rec.excise_exempt_type, '@@@') IN ('CT2', 'EXCISE_EXEMPT_CERT',
1626 'CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH' ) THEN
1627 IF v_item_class NOT IN ('OTIN', 'OTEX') THEN
1628 IF line_rec.excise_exempt_type IN ('CT2 - OTHERS', 'Excise Exempted OTHERS' ) THEN
1629 OPEN for_modvat_tax_rate(line_rec.customer_trx_line_id);
1630 FETCH for_modvat_tax_rate INTO v_modvat_tax_rate;
1631 CLOSE for_modvat_tax_rate;
1632 ELSE
1633 OPEN for_modvat_percentage(v_org_id, v_loc_id);
1634 FETCH for_modvat_percentage INTO v_modvat_tax_rate;
1635 CLOSE for_modvat_percentage;
1636 END IF;
1637 v_exempt_bal := (NVL(v_exempt_bal, 0) + line_rec.quantity * line_rec.assessable_value * NVL(v_modvat_tax_rate,0))/100;
1638 IF v_exempt_bal > v_rg23a_bal THEN
1639 /* RAISE_APPLICATION_ERROR(-20120, 'Register RG23A PART II Balance -> '||
1640 TO_CHAR(v_rg23a_bal ) || ' IS less than the Modvat Amount ->' ||
1641 TO_CHAR(v_exempt_bal)); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Register RG23A PART II Balance -> '||
1642 TO_CHAR(v_rg23a_bal ) || ' IS less than the Modvat Amount ->' ||
1643 TO_CHAR(v_exempt_bal) ; return ;
1644 END IF;
1645 END IF;
1646 -----------ADDED BY GD }
1647 END IF;
1648 END IF; -- SSI UNIT FLAG
1649 END IF; -- EXCISE INVOICE FLAG
1650 ELSIF v_reg_code IN ('BOND_REG')
1651 THEN
1652 -- added by sriram - bug # 3021588
1653 jai_cmn_bond_register_pkg.GET_REGISTER_DETAILS(v_register_id,
1654 v_register_balance,
1655 v_register_exp_date,
1656 v_lou_flag
1657 );
1658
1659 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (pr_new.set_of_books_id ,pr_new.invoice_currency_code ,
1660 pr_new.exchange_date ,pr_new.exchange_rate_type, pr_new.exchange_rate);
1661 v_bond_tax_amount := NVL(v_tax_amount,0) + NVL(v_bond_tax_amount,0);
1662
1663
1664 IF (v_register_balance < v_bond_tax_amount )
1665 AND -- added by sriram - bug # 3021588
1666 ( NVL(v_lou_flag,'N') = 'N') -- added by sriram - bug # 3021588
1667 THEN
1668 /* RAISE_APPLICATION_ERROR(-20120, 'Bonded Register Has Balance -> ' || TO_CHAR(v_register_balance)
1669 || ' ,which IS less than Excisable Amount -> ' || TO_CHAR(v_bond_tax_amount)); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Bonded Register Has Balance -> ' || TO_CHAR(v_register_balance)
1670 || ' ,which IS less than Excisable Amount -> ' || TO_CHAR(v_bond_tax_amount) ; return ;
1671 END IF;
1672
1673 IF (nvl(v_register_exp_date,sysdate) < Sysdate ) THEN
1674 /* RAISE_APPLICATION_ERROR(-20121, 'Validity Date of the Bond Register has expired');
1675 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Validity Date of the Bond Register has expired' ; return ;
1676 END IF ;
1677 ELSIF v_reg_code IN ('23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE','23D_DOM_WITHOUT_EXCISE','23D_EXPORT_WITHOUT_EXCISE')
1678 THEN /*Bug#4601570 bduvarag start*/
1679 IF v_trading_flag = 'Y' AND
1680 v_update_rg23d_flag = 'Y'
1681 THEN
1682 /*Bug#4601570 bduvarag end*/
1683 if line_rec.inventory_item_id is not null then
1684 OPEN matched_qty_cur(line_rec.customer_trx_line_id);
1685 FETCH matched_qty_cur INTO v_matched_qty;
1686 CLOSE matched_qty_cur;
1687 IF NVL(v_matched_qty,0)<> NVL(line_rec.quantity,0)
1688 THEN
1689 /* RAISE_APPLICATION_ERROR(-20120, 'Matched Quantity -> ' || TO_CHAR(v_matched_qty)
1690 || ' , IS less than Invoiced Quantity -> ' || TO_CHAR(line_rec.quantity)
1691 || ' , FOR line NUMBER -> ' || TO_CHAR(line_rec.line_number)); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Matched Quantity -> ' || TO_CHAR(v_matched_qty)
1692 || ' , IS less than Invoiced Quantity -> ' || TO_CHAR(line_rec.quantity)
1693 || ' ,FOR line NUMBER -> ' || TO_CHAR(line_rec.line_number) ; return ;
1694 EXIT;
1695 END IF;
1696 END IF;
1697
1698 -- needs to start here
1699
1700 -- needs to end here
1701 IF v_reg_code = '23D_EXPORT_WITHOUT_EXCISE'
1702 THEN
1703 v_rg23d_tax_amount := NVL(v_tax_amount,0) + NVL(v_rg23d_tax_amount,0);
1704 IF NVL(v_rg23d_register_balance,0) < NVL(v_rg23d_tax_amount,0)
1705 and (NVL(v_lou_flag,'N') = 'N') -- added by sriram bug # 3021588
1706 THEN
1707 /* RAISE_APPLICATION_ERROR(-20120, 'RG23D Bonded Register Has Balance -> ' || TO_CHAR(v_rg23d_register_balance)
1708 || ' ,which IS less than Excisable Amount -> ' || TO_CHAR(v_rg23d_tax_amount)); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'RG23D Bonded Register Has Balance -> ' || TO_CHAR(v_rg23d_register_balance)
1709 || ' ,which IS less than Excisable Amount -> ' || TO_CHAR(v_rg23d_tax_amount) ; return ;
1710 END IF;
1711
1712 -- added by sriram - bug # 3021588
1713 IF (v_register_exp_date > Sysdate ) THEN
1714 /* RAISE_APPLICATION_ERROR(-20121, 'Validity Date of the Bond Register has expired');
1715 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Validity Date of the Bond Register has expired' ; return ;
1716 -- ends here additions by sriram - bug # 3021588
1717 END IF;
1718 END IF;
1719 END IF;/*Bug#4601570 bduvarag*/
1720 END IF;
1721 END LOOP;
1722 v_basic_Ed := 0;
1723 v_additional_ed := 0;
1724 v_other_ed := 0;
1725 v_tax_amount := 0;
1726 v_other_tax_amount := 0;
1727 v_rg23a_tax_amount := 0;
1728 v_rg23c_tax_amount := 0;
1729 v_rg23d_tax_Amount := 0;
1730
1731 ------------------------------start of update loop------------------------
1732
1733 FOR Line_Rec IN Line_Cur LOOP
1734 -- Excise invoice generation logic commented by sriram and
1735 -- making call to the procedure instead.
1736 -- Bug # 2663211
1737
1738 Open item_class_cur(v_org_id,line_rec.Inventory_item_id);
1739 fetch item_class_cur into v_item_class , v_excise_flag;
1740 close item_class_cur;
1741
1742 IF NVL(v_excise_flag,'N') = 'Y' THEN
1743 IF v_invoice_no is Null THEN
1744 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);
1745 END IF;
1746
1747 IF v_errbuf is not null THEN
1748 -- to raise an error when the excise invoice returns a value.
1749 /* raise_application_error(-20107,'Error During Excise Invoice Generation ! ' || v_errbuf);
1750 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Error During Excise Invoice Generation ! ' || v_errbuf ; return ;
1751 END IF;
1752
1753 IF NVL(v_item_class,'~') not in ('OTIN') THEN
1754
1755 UPDATE JAI_AR_TRX_LINES
1756 SET EXCISE_INVOICE_NO = v_invoice_no ,
1757 EXCISE_INVOICE_DATE = SYSDATE
1758 WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
1759 INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
1760 CUSTOMER_TRX_ID = v_customer_trx_id;
1761 END IF;
1762 END IF;
1763 END LOOP;
1764 ----------------end of excise no update loop--------------------
1765
1766 --3661746
1767 open c_total_Excise_amt;
1768 fetch c_total_Excise_amt into v_total_excise_amt;
1769 close c_total_Excise_amt;
1770 --3661746
1771
1772 /* start additions by ssumaith to check for negative cess - bug#4171272*/
1773
1774 open c_cess_amount;
1775 fetch c_cess_amount into ln_Cess_amount;
1776 close c_cess_amount;
1777
1778 lv_register_type := 'RG23A';
1779 jai_cmn_rg_others_pkg.check_balances(
1780 p_organization_id => v_org_id ,
1781 p_location_id => v_loc_id ,
1782 p_register_type => lv_register_type ,
1783 p_trx_amount => ln_cess_amount ,
1784 p_process_flag => lv_process_flag ,
1785 p_process_message => lv_process_message
1786 );
1787
1788 if lv_process_flag <> jai_constants.successful then
1789 lv_rg23a_cess_avlbl := 'FALSE';
1790 else
1791 lv_rg23a_cess_avlbl := 'TRUE';
1792 end if;
1793
1794
1795 lv_register_type := 'RG23C';
1796 jai_cmn_rg_others_pkg.check_balances(
1797 p_organization_id => v_org_id ,
1798 p_location_id => v_loc_id ,
1799 p_register_type => lv_register_type ,
1800 p_trx_amount => ln_cess_amount ,
1801 p_process_flag => lv_process_flag ,
1802 p_process_message => lv_process_message
1803 );
1804
1805 if lv_process_flag <> jai_constants.successful then
1806 lv_rg23c_cess_avlbl := 'FALSE';
1807 else
1808 lv_rg23c_cess_avlbl := 'TRUE';
1809 end if;
1810
1811
1812 lv_register_type := 'PLA';
1813 jai_cmn_rg_others_pkg.check_balances(
1814 p_organization_id => v_org_id ,
1815 p_location_id => v_loc_id ,
1816 p_register_type => lv_register_type ,
1817 p_trx_amount => ln_cess_amount ,
1818 p_process_flag => lv_process_flag ,
1819 p_process_message => lv_process_message
1820 );
1821
1822 if lv_process_flag <> jai_constants.successful then
1823 lv_pla_cess_avlbl := 'FALSE';
1824 else
1825 lv_pla_cess_avlbl := 'TRUE';
1826 end if;
1827
1828
1829 /* ends here additions by ssumaith to check for negative cess - bug# 4171272 */
1830
1831
1832
1833 FOR Line_Rec IN Line_Cur LOOP
1834 OPEN item_class_cur(V_ORG_ID,Line_Rec.Inventory_Item_Id);
1835 FETCH item_class_cur INTO v_item_class , v_excise_flag;
1836 CLOSE item_class_cur;
1837 FOR excise_cal_rec IN excise_cal_cur(Line_Rec.customer_trx_line_id, Line_Rec.Inventory_Item_ID, v_org_id) LOOP
1838 IF excise_cal_rec.t_type IN ('Excise') THEN
1839 v_basic_ed := NVL(v_basic_ed,0) + NVL(excise_cal_rec.func_amt,0);
1840 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
1841 v_additional_ed := NVL(v_additional_ed,0) + NVL(excise_cal_rec.func_amt,0);
1842 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
1843 v_other_ed := NVL(v_other_ed,0) + NVL(excise_cal_rec.func_amt,0);
1844 END IF;
1845 END LOOP;
1846 v_tax_amount := NVL(v_basic_ed,0) + NVL(v_additional_ed,0) + NVL(v_other_ed,0);
1847 v_basic_Ed := 0;
1848 v_additional_ed := 0;
1849 v_other_ed := 0;
1850 IF v_item_class IN ('CGEX','CGIN') THEN
1851 v_rg23c_tax_amount := NVL(v_rg23c_tax_amount,0) + NVL(v_tax_amount,0);
1852 ELSIF v_item_class IN ('RMIN','RMEX') THEN
1853 v_rg23a_tax_amount := NVL(v_rg23a_tax_amount,0) + NVL(v_tax_amount,0);
1854 ELSIF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') THEN
1855 v_other_tax_amount := NVL(v_other_tax_amount,0) + NVL(v_tax_amount,0);
1856 END IF;
1857
1858 v_tax_amount:=v_total_excise_amt;
1859
1860 IF NVL(v_excise_flag,'N') = 'Y' THEN
1861 IF NVL(v_ssi_unit_flag,'N') = 'N' THEN
1862 /*
1863 || code changed by aiyer for the bug 4101549
1864 || v_complete_flag should have the values as ('N','A','C','P')
1865 */
1866 IF v_complete_flag IN ('N','A','C','P') THEN
1867 IF v_rg_flag = 'Y' THEN
1868 IF v_reg_code IN ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
1869 --3661746
1870 -- following code modified by ssumaith - bug# --3661746
1871 -- in order to hit the register based on preferences.
1872 /*
1873 Added code in the following segment to check for cess balance also
1874 */
1875
1876 IF v_item_class IN ('FGIN','FGEX','CCIN','CCEX','CGIN','CGEX','RMIN','RMEX') THEN
1877 IF v_reg_type IS NULL THEN
1878 OPEN preference_reg_cur(v_org_id,v_loc_id);
1879 FETCH preference_reg_cur INTO rg23a,rg23c,pla;
1880 CLOSE preference_reg_cur;
1881
1882 FOR reg_balance IN reg_balance_cur(v_org_id,v_loc_id) LOOP
1883 IF rg23a = 1 THEN
1884 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1885 v_rg23a_tax_amount := v_tax_amount;
1886 v_reg_type := 'RG23A';
1887 ELSE
1888 IF rg23c = 2 THEN
1889 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1890 v_rg23c_tax_amount := v_tax_amount;
1891 v_reg_type := 'RG23C';
1892 ELSIF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
1893 v_reg_type := 'PLA';
1894 END IF;
1895 ELSIF pla = 2 THEN
1896 IF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
1897 v_reg_type := 'PLA';
1898 ELSIF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1899 v_rg23c_tax_amount := v_tax_amount;
1900 v_reg_type := 'RG23C';
1901 END IF;
1902 END IF;
1903 END IF;
1904 ELSIF rg23c = 1 THEN
1905 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1906 v_rg23c_tax_amount := v_tax_amount;
1907 v_reg_type := 'RG23C';
1908 ELSE
1909 IF rg23a = 2 THEN
1910 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1911 v_rg23a_tax_amount := v_tax_amount;
1912 v_reg_type := 'RG23A';
1913 ELSIF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
1914 v_reg_type := 'PLA';
1915 END IF;
1916 ELSIF pla = 2 THEN
1917 IF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
1918 v_reg_type := 'PLA';
1919 ELSIF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1920 v_rg23a_tax_amount := v_tax_amount;
1921 v_reg_type := 'RG23A';
1922 END IF;
1923 END IF;
1924 END IF;
1925 ELSIF pla = 1 THEN
1926 IF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
1927 v_reg_type := 'PLA';
1928 ELSE
1929 IF rg23c = 2 THEN
1930 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1931 v_rg23c_tax_amount := v_tax_amount;
1932 v_reg_type := 'RG23C';
1933 ELSIF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1934 v_rg23a_tax_amount := v_tax_amount;
1935 v_reg_type := 'RG23A';
1936 END IF;
1937 ELSIF rg23a = 2 THEN
1938 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1939 v_rg23a_tax_amount := v_tax_amount;
1940 v_reg_type := 'RG23A';
1941 ELSIF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1942 v_rg23c_tax_amount := v_tax_amount;
1943 v_reg_type := 'RG23C';
1944 END IF;
1945 END IF;
1946 END IF;
1947 END IF; -- pref 1 if condition's end if
1948
1949 IF v_reg_type is null THEN
1950 /* raise_application_error(-20102,'None of the registers have enough balance for the excise duty -> ' || v_tax_amount || ' Or Cess amount => ' || ln_Cess_amount);
1951 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'None of the registers have enough balance for the excise duty -> ' || v_tax_amount || ' Or Cess amount => ' || ln_Cess_amount ; return ;
1952 END IF;
1953 IF v_reg_type = 'PLA' and NVL(v_ssi_unit_flag,'N') <> 'Y' THEN
1954 IF v_tax_amount > reg_balance.pla_balance AND lv_pla_cess_avlbl = 'TRUE' THEN
1955 /* raise_application_error(-20102,'PLA Balance -> ' || reg_balance.pla_balance ||
1956 ' is not enough for the excise duty -> ' || v_tax_amount ); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'PLA Balance -> ' || reg_balance.pla_balance ||
1957 ' is not enough for the excise duty -> ' || v_tax_amount ; return ;
1958 END IF;
1959 ELSIF v_reg_type = 'RG23A' THEN
1960 IF v_tax_amount > reg_balance.rg23a_balance AND lv_rg23a_cess_avlbl = 'TRUE' THEN
1961 /* raise_application_error(-20102,'RG23A Balance -> ' || reg_balance.rg23a_balance ||
1962 ' is not enough for the excise duty -> ' || v_tax_amount ); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'RG23A Balance -> ' || reg_balance.rg23a_balance ||
1963 ' is not enough for the excise duty -> ' || v_tax_amount ; return ;
1964 END IF;
1965 ELSIF v_reg_type = 'RG23C' THEN
1966 IF v_tax_amount > reg_balance.rg23c_balance AND lv_rg23c_cess_avlbl = 'TRUE' THEN
1967 /* raise_application_error(-20102,'RG23C Balance -> ' || reg_balance.rg23c_balance ||
1968 ' is not enough for the excise duty -> ' || v_tax_amount ); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'RG23C Balance -> ' || reg_balance.rg23c_balance ||
1969 ' is not enough for the excise duty -> ' || v_tax_amount ; return ;
1970 END IF;
1971 END IF;
1972 END LOOP;
1973 END IF; -- for v_reg_type is null
1974 v_excise_paid_register := v_reg_type;
1975 END IF; -- for v_item_class in ('FGIN','FGEX'.... )
1976 END IF; -- for v_reg_code in ('DOMESTIC_EXCISE')....
1977
1978 /*
1979 the following piece of code added by sriram bug # 2521387
1980 */
1981 Declare
1982 v_reg_type VARCHAR2(10);
1983 Begin
1984 SELECT once_completed_flag
1985 INTO v_reg_type
1986 FROM JAI_AR_TRXS
1987 WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
1988
1989 IF v_reg_type = 'P' THEN
1990 v_reg_type := 'PLA';
1991 ELSIF v_reg_type = 'A' THEN
1992 v_reg_type := 'RG23A';
1993 ELSIF v_reg_type = 'C' THEN
1994 v_reg_type := 'RG23C';
1995 END IF;
1996
1997 IF v_reg_type is not null and v_reg_type <> 'N' THEN
1998 v_excise_paid_register := v_reg_type;
1999 END IF;
2000
2001 Exception
2002 When Others Then
2003 /* RAISE_APPLICATION_ERROR(-10101,SQLERRM);
2004 */ pv_return_code := jai_constants.expected_error ; pv_return_message := SQLERRM ; return ;
2005 End ;
2006
2007 /*
2008 Ends here
2009 */
2010
2011 UPDATE JAI_AR_TRX_LINES
2012 SET PAYMENT_REGISTER = v_excise_paid_register
2013 WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
2014 INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
2015 CUSTOMER_TRX_ID = v_customer_trx_id;
2016 END IF; -- for v_rg_flag = 'Y'
2017 v_excise_paid_register := '';
2018 END IF; -- for v_complete_flag = 'N'
2019 -- END IF; --3661746
2020 ELSIF NVL(v_ssi_unit_flag,'N') = 'Y' THEN
2021 IF v_item_class IN ('RMIN','RMEX','CGEX','CGIN','FGIN','FGEX','CCIN','CCEX') THEN
2022 /*
2023 || code changed by aiyer for the bug 4101549
2024 || v_complete_flag should have the values as ('N','A','C','P')
2025 */
2026 IF v_complete_flag IN ('N','A','C','P') THEN
2027 IF v_rg_flag = 'Y' THEN
2028 IF v_reg_code IN ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
2029 IF v_reg_type IS NULL THEN
2030 OPEN preference_reg_cur(v_org_id,v_loc_id);
2031 FETCH preference_reg_cur INTO rg23a,rg23c,pla;
2032 CLOSE preference_reg_cur;
2033 --======
2034 FOR reg_balance IN reg_balance_cur(v_org_id,v_loc_id) LOOP --3661746
2035 IF rg23a = 1 THEN
2036 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
2037 v_rg23a_tax_amount := v_tax_amount;
2038 v_reg_type := 'RG23A';
2039 ELSE
2040 IF rg23c = 2 THEN
2041 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
2042 v_rg23c_tax_amount := v_tax_amount;
2043 v_reg_type := 'RG23C';
2044 ELSE
2045 v_reg_type := 'PLA';
2046 END IF;
2047 ELSIF pla = 2 THEN
2048 v_reg_type := 'PLA';
2049 END IF;
2050 END IF;
2051 ELSIF rg23c = 1 THEN
2052 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
2053 v_rg23c_tax_amount := v_tax_amount;
2054 v_reg_type := 'RG23C';
2055 ELSE
2056 IF rg23a = 2 THEN
2057 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
2058 v_rg23a_tax_amount := v_tax_amount;
2059 v_reg_type := 'RG23A';
2060 ELSE
2061 v_reg_type := 'PLA';
2062 END IF;
2063 ELSIF pla = 2 THEN
2064 v_reg_type := 'PLA';
2065 END IF;
2066 END IF;
2067 ELSIF pla = 1 THEN
2068 v_reg_type := 'PLA';
2069 END IF;
2070 --3661746
2071 IF v_reg_type = 'RG23A' THEN
2072 IF v_tax_amount > reg_balance.rg23a_balance AND lv_rg23a_cess_avlbl = 'TRUE' THEN
2073 /* raise_application_error(-20102,'RG23A Balance -> ' || reg_balance.rg23a_balance ||
2074 ' is not enough for the excise duty -> ' || v_tax_amount ); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'RG23A Balance -> ' || reg_balance.rg23a_balance ||
2075 ' is not enough for the excise duty -> ' || v_tax_amount ; return ;
2076 END IF;
2077 ELSIF v_reg_type = 'RG23C' THEN
2078 IF v_tax_amount > reg_balance.rg23c_balance AND lv_rg23c_cess_avlbl = 'TRUE' THEN
2079 /* raise_application_error(-20102,'RG23C Balance -> ' || reg_balance.rg23c_balance ||
2080 ' is not enough for the excise duty -> ' || v_tax_amount ); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'RG23C Balance -> ' || reg_balance.rg23c_balance ||
2081 ' is not enough for the excise duty -> ' || v_tax_amount ; return ;
2082 END IF;
2083 END IF;
2084 --3661746
2085 END LOOP; --3661746
2086 END IF; -- for v_reg_type is null
2087
2088 v_excise_paid_register := v_reg_type;
2089 -- END IF; -- for v_item_clas in ('FGIN','FGEX'...)
2090 END IF; -- for if v_reg_code in ('DOMESTIC_EXCISE'....)
2091
2092 /*
2093 the following piece of code added by sriram bug # 2521387
2094 */
2095
2096 Declare
2097 v_reg_type1 VARCHAR2(10);
2098 Begin
2099 SELECT once_completed_flag
2100 INTO v_reg_type1
2101 FROM JAI_AR_TRXS
2102 WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
2103
2104 If v_reg_type1 = 'P' THEN
2105 v_reg_type1 := 'PLA';
2106 ELSIF v_reg_type1 = 'A' THEN
2107 v_reg_type1 := 'RG23A';
2108 ELSIF v_reg_type1 = 'C' THEN
2109 v_reg_type1 := 'RG23C';
2110 END IF;
2111
2112 if v_reg_type1 is not null and v_reg_type1 <> 'N' then
2113 v_excise_paid_register := v_reg_type1;
2114 end if;
2115
2116 Exception
2117 When Others Then
2118 /* RAISE_APPLICATION_ERROR(-10101,SQLERRM);
2119 */ pv_return_code := jai_constants.expected_error ; pv_return_message := SQLERRM ; return ;
2120 END;
2121 /*
2122 Ends here - Additions by Sriram
2123 */
2124 UPDATE JAI_AR_TRX_LINES
2125 SET PAYMENT_REGISTER = v_excise_paid_register
2126 WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
2127 INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
2128 CUSTOMER_TRX_ID = v_customer_trx_id;
2129 END IF; -- for v_rg_flag = 'Y;
2130 v_excise_paid_register := '';
2131 END IF; -- for v_complete_flag = 'N'
2132 END IF; -- for v_item_class in ('...)
2133 END IF; -- for v_ssi_unit_flag ....
2134 END IF; -- v_excise_flag = 'Y'
2135
2136
2137 END LOOP;
2138 INSERT INTO JAI_AR_TRX_INS_HDRS_T
2139 (
2140 ORGANIZATION_ID,
2141 LOCATION_ID,
2142 CUSTOMER_TRX_ID ,
2143 SHIP_TO_CUSTOMER_ID,
2144 SHIP_TO_SITE_USE_ID,
2145 CUST_TRX_TYPE_ID,
2146 TRX_DATE,
2147 SOLD_TO_CUSTOMER_ID,
2148 BATCH_SOURCE_ID,
2149 BILL_TO_CUSTOMER_ID , -- BILL_TO_CUSTOMER_ID column in insert added by sriram - 13/may-02
2150 BILL_TO_SITE_USE_ID ,
2151 CREATED_BY ,
2152 CREATION_DATE,
2153 LAST_UPDATED_BY,
2154 LAST_UPDATE_DATE
2155 ) -- BILL_TO_SITE_USE_ID column in insert added by sriram - 13/may-02
2156 VALUES
2157 (
2158 V_ORG_ID,
2159 V_LOC_ID,
2160 V_CUSTOMER_TRX_ID ,
2161 pr_new.SHIP_TO_CUSTOMER_ID,
2162 pr_new.SHIP_TO_SITE_USE_ID,
2163 pr_new.CUST_TRX_TYPE_ID,
2164 pr_new.TRX_DATE,
2165 pr_new.SOLD_TO_CUSTOMER_ID,
2166 pr_new.BATCH_SOURCE_ID,
2167 pr_new.BILL_TO_CUSTOMER_ID ,-- ADDED BY SRIRAM - 13-MAY-2002
2168 pr_new.BILL_TO_SITE_USE_ID,
2169 FND_GLOBAL.USER_ID , -- added standard who columns by brahtod for bug# 4558072
2170 SYSDATE ,
2171 FND_GLOBAL.USER_ID ,
2172 SYSDATE);
2173 END IF;
2174 UPDATE JAI_AR_TRXS
2175 SET
2176 ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
2177 WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
2178 END IF;
2179 END IF;
2180
2181 /*
2182 ||Start of code changes for bug 4247989
2183 ||Modification for VAT enhancement, code added by aiyer
2184 */
2185 IF NVL(v_trans_type,'N') NOT IN ('INV','DM') THEN
2186 RETURN;
2187 END IF;
2188
2189 IF nvl(pr_new.created_from,'###') = 'ARXTWMAI' THEN
2190 OPEN c_vat_invoice_cur;
2191 FETCH c_vat_invoice_cur INTO lv_vat_invoice_number;
2192 CLOSE c_vat_invoice_cur;
2193
2194 IF lv_vat_invoice_number IS NOT NULL THEN
2195 return;
2196 END IF;
2197
2198 /*
2199 || check if VAT regime setup has been done
2200 || if yes then continue with the VAT processing
2201 */
2202 OPEN cur_vat_taxes_exist;
2203 FETCH cur_vat_taxes_exist into ln_regime_id,ln_regime_code;
2204
2205
2206 /*
2207 || Added by kunkumar for bug#5645003
2208 || Check if only 'VAT REVERSAL' tax type is present in ja_in_ra_cust_trx_tax_lines
2209 */
2210 IF ln_regime_id IS NULL THEN
2211 lv_vat_reversal := 'VAT REVERSAL' ;
2212 OPEN c_chk_vat_reversal(lv_vat_reversal) ;
2213 FETCH c_chk_vat_reversal INTO ln_vat_reversal_exists;
2214 CLOSE c_chk_vat_reversal ;
2215
2216 /*
2217 || Retrieve the regime_id for 'VAT REVERSAL' tax type, which is of regime code 'VAT'
2218 */
2219 IF ln_vat_reversal_exists = 1 THEN
2220 OPEN c_get_regime_id ;
2221 FETCH c_get_regime_id INTO ln_regime_id ;
2222 CLOSE c_get_regime_id ;
2223
2224 IF ln_regime_id IS NOT NULL THEN
2225 ln_regime_code := jai_constants.vat_regime ;
2226 END IF ;
2227 END IF ;
2228 END IF ;
2229 --bug#5645003, ends
2230
2231 IF UPPER(nvl(ln_regime_code,'####')) = jai_constants.vat_regime THEN
2232 /*
2233 || Check the VAT Regime setup for vat invoice no being same as excise invoice no.
2234 || If the attribute value is 'N' or this attribute code does not exist the generate the vat invoice number
2235 */
2236 OPEN cur_get_same_inv_no ( cp_organization_id => v_org_id ,
2237 cp_location_id => v_loc_id
2238 ) ;
2239 FETCH cur_get_same_inv_no INTO lv_vat_no_same_exc_no;
2240 CLOSE cur_get_same_inv_no ;
2241
2242 IF nvl(lv_vat_no_same_exc_no,'N') = 'Y' THEN
2243 /*
2244 || vat invoice number should be same as excise invoice number
2245 */
2246 OPEN cur_get_exc_inv_no ;
2247 FETCH cur_get_exc_inv_no INTO lv_vat_invoice_number;
2248 CLOSE cur_get_exc_inv_no;
2249 END IF;
2250
2251
2252 IF lv_vat_invoice_number IS NULL THEN
2253 /*
2254 || Either the setup for excise invoice number has not been doe or the attribute_value was set to 'N'
2255 || In either of this cases generate VAT Invoice number
2256 */
2257
2258 /*
2259 || added csahoo - for seperate vat invoice num for unreg dealers - bug# 5233925
2260 */
2261 IF check_reg_dealer( NVL(pr_new.SHIP_TO_CUSTOMER_ID ,pr_new.BILL_TO_CUSTOMER_ID) ,
2262 NVL(pr_new.SHIP_TO_SITE_USE_ID, pr_new.BILL_TO_SITE_USE_ID)
2263 ) THEN
2264 lv_doc_type_class := 'I';
2265 ELSE
2266 lv_doc_type_class := 'UI';
2267 END IF;
2268
2269 /*
2270 || csahoo - for seperate vat invoice num for unreg dealers - bug# 5233925
2271 */
2272
2273 jai_cmn_rgm_setup_pkg.gen_invoice_number(
2274 p_regime_id => ln_regime_id ,
2275 p_organization_id => v_org_id ,
2276 p_location_id => v_loc_id ,
2277 p_date => pr_new.trx_date ,
2278 p_doc_class => lv_doc_type_class , --added for bug#7475924
2279 p_doc_type_id => pr_new.batch_source_id ,
2280 p_invoice_number => lv_vat_invoice_number ,
2281 p_process_flag => lv_process_flag ,
2282 p_process_msg => lv_process_message
2283 );
2284
2285 IF lv_process_flag = jai_constants.expected_error OR
2286 lv_process_flag = jai_constants.unexpected_error
2287 THEN
2288 CLOSE cur_vat_taxes_exist;
2289 /* raise_application_error(-20130,lv_process_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_process_message ; return ;
2290 /*
2291 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
2292 EXCEPTION_CODE => NULL ,
2293 EXCEPTION_TEXT => lv_process_message
2294 );
2295 */
2296 END IF;
2297 END IF; -- END IF of lv_excise_inv_no IS NULL
2298
2299 /*
2300 || Get the gl_date from ra_cust_trx_lines_gl_dist_all
2301 */
2302 OPEN cur_get_gl_date('REC');
2303 FETCH cur_get_gl_date INTO ld_gl_date;
2304 CLOSE cur_get_gl_date;
2305
2306 /*
2307 || IF the VAT invoice Number has been successfully generated, then pass accounting entries
2308 */
2309 jai_cmn_rgm_vat_accnt_pkg.process_order_invoice (
2310 p_regime_id => ln_regime_id ,
2311 p_source => jai_constants.source_ar ,
2312 p_organization_id => v_org_id ,
2313 p_location_id => v_loc_id ,
2314 p_delivery_id => NULL ,
2315 p_customer_trx_id => pr_new.customer_trx_id ,
2316 p_transaction_type => v_trans_type ,
2317 p_vat_invoice_no => lv_vat_invoice_number ,
2318 p_default_invoice_date => nvl(ld_gl_date,pr_new.trx_date) ,
2319 p_batch_id => NULL ,
2320 p_called_from => jai_constants.vat_repo_call_inv_comp ,
2321 p_debug => jai_constants.no ,
2322 p_process_flag => lv_process_flag ,
2323 p_process_message => lv_process_message
2324 );
2325
2326 IF lv_process_flag = jai_constants.expected_error OR
2327 lv_process_flag = jai_constants.unexpected_error
2328 THEN
2329 CLOSE cur_vat_taxes_exist ;
2330 /* raise_application_error(-20130,lv_process_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_process_message ; return ;
2331 /*
2332 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
2333 EXCEPTION_CODE => NULL ,
2334 EXCEPTION_TEXT => lv_process_message
2335 );
2336 */
2337
2338 END IF;
2339
2340 UPDATE
2341 JAI_AR_TRXS
2342 SET
2343 vat_invoice_no = lv_vat_invoice_number ,
2344 vat_invoice_date = nvl(ld_gl_date,pr_new.trx_date)
2345 WHERE
2346 customer_trx_id = pr_new.customer_trx_id ;
2347
2348 END IF; -- END IF of vat type of taxes found
2349
2350 CLOSE cur_vat_taxes_exist;
2351
2352 END IF ; --EBD IF of nvl(new.created_from,'###') ='ARXTWMAI'
2353
2354 /*
2355 ||End of code changes for bug 4247989
2356 */
2357 /* Added an exception block by Ramananda for bug#4570303 */
2358 EXCEPTION
2359 WHEN OTHERS THEN
2360 Pv_return_code := jai_constants.unexpected_error;
2361 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARU_T4 ' || substr(sqlerrm,1,1900);
2362
2363 END ARU_T4 ;
2364
2365 /*
2366 REM +======================================================================+
2367 REM NAME ARU_T5
2368 REM
2369 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ARIUD_T1
2370 REM
2371 REM NOTES Refers to old trigger JAI_AR_RCTA_ARU_T6
2372 REM
2373 REM+=======================================================================+
2374 REM Change History
2375 REM slno Date Name BugNo File Version
2376 REM +=======================================================================+
2377 REM
2378 REM
2379 REM -----------------------------------------------------------------------
2380 REM 1. 04-Jul-2006 aiyer 5364288 120.3
2381 REM -----------------------------------------------------------------------
2382 REM Comments:-
2383 REM Removed references to ra_customer_trx_all and replaced it with jai_ar_trx.
2384 REM also removed the cursor org_cur which was trying to fetch the org_id from ra_customer_trx_all.
2385 REM This was not required as pr_new.org_id is already being passed to the procedure and has the
2386 REM value of org_id.
2387 REM -----------------------------------------------------------------------
2388 REM 2.
2389 REM -----------------------------------------------------------------------
2390 REM -----------------------------------------------------------------------
2391 REM 3.
2392 REM -----------------------------------------------------------------------
2393 REM -----------------------------------------------------------------------
2394 REM 4.
2395 REM -----------------------------------------------------------------------
2396 REM
2397 REM
2398 REM+======================================================================+
2399 */
2400 PROCEDURE ARU_T5 ( 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
2401 v_line_no NUMBER := 0;
2402 v_books_id NUMBER := 1;
2403 v_salesrep_id NUMBER;
2404 v_line_type VARCHAR2(30);
2405 v_vat_tax NUMBER;
2406 v_ccid NUMBER;
2407 v_cust_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.customer_trx_line_id%TYPE;
2408 v_customer_trx_line_id NUMBER ;
2409 v_customer_trx_id NUMBER; -- := pr_new.customer_trx_id; --Ramananda for File.Sql.35
2410 v_created_from VARCHAR2(30);
2411 c_from_currency_code VARCHAR2(15);
2412 c_conversion_type VARCHAR2(30);
2413 c_conversion_date DATE;
2414 c_conversion_rate NUMBER := 0;
2415 v_converted_rate NUMBER := 1;
2416 req_id NUMBER;
2417 result BOOLEAN;
2418 v_organization_id NUMBER ;
2419 v_location_id NUMBER ;
2420 v_batch_source_id NUMBER ;
2421 v_register_code VARCHAR2(50);
2422 v_order_number VARCHAR2(30);
2423 v_org_id NUMBER(15);
2424 -- Bug 5207772. Added by Lakshmi Gopalsami
2425 v_order_type VARCHAR2(30);
2426
2427 lv_line_type_tax RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE%type ;
2428 lv_line_type_freight RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE%type ;
2429 lv_acct_class_tax RA_CUST_TRX_LINE_GL_DIST_ALL.ACCOUNT_CLASS%type ;
2430 lv_acct_class_freight RA_CUST_TRX_LINE_GL_DIST_ALL.ACCOUNT_CLASS%type ;
2431
2432 -- CURSOR ADDED BY SRIRAM - BUG # 2654567
2433
2434 CURSOR C_GET_TRX_DETAILS
2435 IS
2436 SELECT * FROM JAI_AR_TRX_LINES
2437 WHERE CUSTOMER_TRX_ID = pr_new.CUSTOMER_TRX_ID;
2438
2439
2440 CURSOR C_GET_TRX_COUNT
2441 IS
2442 SELECT COUNT(*)
2443 FROM RA_CUSTOMER_TRX_LINES_ALL
2444 WHERE CUSTOMER_TRX_ID = pr_new.CUSTOMER_TRX_ID
2445 AND LINE_TYPE in (lv_line_type_tax, lv_line_type_freight); /* Modified by Ramananda for removal of SQL LITERALs */
2446 --('TAX','FREIGHT');
2447
2448 CURSOR C_GET_GL_DIST_ALL_COUNT IS
2449 SELECT COUNT(*)
2450 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
2451 WHERE CUSTOMER_TRX_ID = pr_new.CUSTOMER_TRX_ID
2452 AND ACCOUNT_CLASS IN (lv_acct_class_tax , lv_acct_class_freight ); /* Modified by Ramananda for removal of SQL LITERALs */
2453 --AND ACCOUNT_CLASS IN ('TAX','FREIGHT');
2454
2455 CURSOR TAX_TYPE_CUR(p_customer_trx_line_id Number) IS
2456 SELECT A.tax_id taxid, A.tax_rate, A.uom uom,A.tax_amount tax_amt,b.tax_type t_type,A.customer_trx_line_id line_id , a.tax_line_no
2457 FROM JAI_AR_TRX_TAX_LINES A , JAI_CMN_TAXES_ALL B
2458 WHERE link_to_cust_trx_line_id = p_customer_trx_line_id
2459 AND A.tax_id = B.tax_id
2460 ORDER BY 1;
2461
2462 lv_tax_regime_code zx_rates_b.tax_regime_code%type ;
2463 ln_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%type ;
2464 ln_tax_rate_id zx_rates_b.tax_rate_id%type ;
2465 /* Added by Ramananda for bug#4468353 , end */
2466
2467 CURSOR TAX_CCID_CUR(p_tax_id IN NUMBER) IS
2468 SELECT tax_account_id
2469 FROM JAI_CMN_TAXES_ALL B
2470 WHERE B.tax_id = p_tax_id ;
2471
2472
2473 CURSOR SO_AR_HDR_INFO IS
2474 SELECT organization_id, location_id, batch_source_id
2475 FROM JAI_AR_TRXS
2476 WHERE Customer_Trx_ID = v_customer_trx_id;
2477
2478
2479 CURSOR register_code_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER,
2480 p_batch_source_id IN NUMBER) IS
2481 SELECT register_code
2482 FROM JAI_OM_OE_BOND_REG_HDRS
2483 WHERE organization_id = p_org_id AND location_id = p_loc_id AND
2484 register_id IN (SELECT register_id
2485 FROM JAI_OM_OE_BOND_REG_DTLS
2486 WHERE order_type_id = p_batch_source_id AND order_flag = 'N');
2487
2488 /* Bug5207772. Added by Lakshmi Gopalsami
2489 Fixed performance issue - SQL id - 17698796
2490 Removed the reference to so_headers_all and added oe_transaction_types_tl
2491 Changed the parameter to p_order_type instead of p_order_number
2492 */
2493 CURSOR register_code_cur1(p_organization_id NUMBER,
2494 p_location_id NUMBER,
2495 p_order_type VARCHAR2) IS
2496 SELECT A.register_code
2497 FROM JAI_OM_OE_BOND_REG_HDRS A,
2498 JAI_OM_OE_BOND_REG_DTLS b,
2499 oe_transaction_types_tl ott
2500 WHERE A.organization_id = p_organization_id
2501 AND A.location_id = p_location_id
2502 AND A.register_id = b.register_id
2503 AND b.order_flag = 'Y'
2504 AND b.order_type_id = ott.transaction_type_id
2505 AND ott.NAME = p_order_type;
2506
2507
2508
2509 /*
2510 || Added by kunkumar for bug#5645003
2511 || Check if only 'VAT REVERSAL' tax type is present in ja_in_ra_cust_trx_tax_lines
2512 */
2513 CURSOR c_chk_vat_reversal (cp_tax_type jai_cmn_taxes_all.tax_type%TYPE )
2514 IS
2515 SELECT
2516 1
2517 FROM
2518 JAI_AR_TRX_TAX_LINES jcttl,
2519 JAI_AR_TRX_LINES jctl,
2520 JAI_CMN_TAXES_ALL jtc
2521 WHERE
2522 jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
2523 jctl.customer_trx_id = pr_new.customer_trx_id AND
2524 jcttl.tax_id = jtc.tax_id AND
2525 jtc.org_id = pr_new.org_id AND
2526 jtc.tax_type = cp_tax_type ;
2527
2528 /*
2529 || Retrieve the regime_id which is of regime code 'VAT'
2530 */
2531 CURSOR c_get_regime_id
2532 IS
2533 SELECT
2534 regime_id
2535 FROM
2536 jai_regime_tax_types_v
2537 WHERE
2538 regime_code = jai_constants.vat_regime
2539 AND rownum = 1 ;
2540
2541
2542
2543 v_err_mesg VARCHAR2(250);
2544
2545 /*
2546 || start of bug 5364288 - code modified by aiyer
2547 ||changed the variable definition from RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE to JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE
2548 */
2549 v_trx_num JAI_AR_TRXS.TRX_NUMBER%TYPE;
2550
2551 /* End of bug 5364288 */
2552
2553 v_TRX_TAX_COUNT Number;
2554 v_trx_gl_dist_COUNT Number;
2555 BEGIN
2556 pv_return_code := jai_constants.successful ;
2557 /*------------------------------------------------------------------------------------------
2558 FILENAME: JA_IN_APPS_AR_LINES_INSERT_TRG.sql
2559 CHANGE HISTORY:
2560 1. 10-Aug-2005 Aiyer bug 4545146 version 120.1
2561 Issue:-
2562 Deadlock on tables due to multiple triggers on the same table (in different sql files)
2563 firing in the same phase.
2564 Fix:-
2565 Multiple triggers on the same table have been merged into a single file to resolve
2566 the problem
2567 The following files have been stubbed:-
2568 jai_ar_rcta_t1.sql
2569 jai_ar_rcta_t2.sql
2570 jai_ar_rcta_t3.sql
2571 jai_ar_rcta_t4.sql
2572 jai_ar_rcta_t6.sql
2573 jai_ar_rcta_t7.sql
2574 jai_ar_rcta_t8.sql
2575 jai_ar_rcta_t9.sql
2576 Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers in the above files
2577
2578 Dependency:
2579 ----------
2580
2581 Sl No. Bug Dependent on
2582 Bug/Patch set Details
2583 -------------------------------------------------------------------------------------------------
2584
2585 --------------------------------------------------------------------------------------------*/
2586
2587 v_customer_trx_id := pr_new.customer_trx_id; --Ramananda for File.Sql.35
2588
2589 /* Added by Ramananda for removal of SQL LITERALs */
2590 lv_line_type_tax := 'TAX';
2591 lv_line_type_freight := 'FREIGHT' ;
2592 OPEN C_GET_TRX_COUNT ;
2593 FETCH C_GET_TRX_COUNT INTO v_TRX_TAX_COUNT;
2594 CLOSE C_GET_TRX_COUNT;
2595
2596 /* Added by Ramananda for removal of SQL LITERALs */
2597 lv_acct_class_tax := 'TAX';
2598 lv_acct_class_freight := 'FREIGHT' ;
2599 OPEN C_GET_GL_DIST_ALL_COUNT ;
2600 FETCH C_GET_GL_DIST_ALL_COUNT INTO v_trx_gl_dist_COUNT;
2601 CLOSE C_GET_GL_DIST_ALL_COUNT;
2602
2603
2604
2605 IF v_TRX_TAX_COUNT <> v_trx_gl_dist_COUNT THEN
2606 /* RAISE_APPLICATION_ERROR(-20102,'Taxes are not consistent in the RA_CUSTOMER_TRX_LINES_ALL AND RA_CUST_TRX_LINE_GL_DIST_ALL Tables');
2607 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Taxes are not consistent in the RA_CUSTOMER_TRX_LINES_ALL AND RA_CUST_TRX_LINE_GL_DIST_ALL Tables' ; return ;
2608 END IF ;
2609
2610 FOR v_trx_rec in C_GET_TRX_DETAILS
2611
2612 LOOP
2613 v_customer_trx_line_id := v_trx_rec.customer_trx_line_id;
2614 v_trx_num := pr_new.trx_number;
2615 v_created_from := pr_new.created_from;
2616 v_order_number := pr_new.interface_header_attribute1;
2617 -- Bug 5207772. Added by Lakshmi Gopalsami
2618 v_order_type := pr_new.interface_header_attribute2;
2619
2620
2621
2622
2623 IF v_created_from IN ('ARXREC','ARXTWMAI') THEN
2624 RETURN;
2625 END IF;
2626
2627
2628 v_books_id := pr_new.set_of_books_id;
2629 v_salesrep_id := pr_new.primary_salesrep_id ;
2630 v_org_id := pr_new.org_id ;
2631 c_from_currency_code := pr_new.invoice_currency_code ;
2632 c_conversion_type := pr_new.exchange_rate_type;
2633 c_conversion_date := pr_new.exchange_date ;
2634 c_conversion_rate := pr_new.exchange_rate;
2635
2636 /*
2637 || Assigned the value of pr_new.org_id to v_org and instead removed the cursor org_cur which was picking org_id from ra_customer_trx_all
2638 || and causing mutation issue
2639 */
2640 v_org_id := pr_new.org_id;
2641 OPEN jai_ar_trx_pkg.c_tax_regime_code_cur(V_ORG_ID);
2642 FETCH jai_ar_trx_pkg.c_tax_regime_code_cur INTO lv_tax_regime_code;
2643 CLOSE jai_ar_trx_pkg.c_tax_regime_code_cur ;
2644
2645 OPEN jai_ar_trx_pkg.c_max_tax_rate_id_cur(lv_tax_regime_code);
2646 FETCH jai_ar_trx_pkg.c_max_tax_rate_id_cur INTO ln_tax_rate_id;
2647 CLOSE jai_ar_trx_pkg.c_max_tax_rate_id_cur ;
2648
2649
2650 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_books_id ,c_from_currency_code ,
2651 c_conversion_date ,c_conversion_type, c_conversion_rate);
2652
2653
2654
2655 OPEN SO_AR_HDR_INFO ;
2656 FETCH SO_AR_HDR_INFO INTO v_organization_id, v_location_id, v_batch_source_id;
2657 CLOSE SO_AR_HDR_INFO ;
2658
2659
2660
2661 IF v_created_from = 'RAXTRX' THEN
2662 -- Bug 5207772. Added by Lakshmi Gopalsami
2663 OPEN register_code_cur1(v_organization_id, v_location_id, v_order_type);
2664 FETCH register_code_cur1 INTO v_register_code;
2665 CLOSE register_code_cur1;
2666 END IF;
2667 BEGIN
2668 pv_return_code := jai_constants.successful ;
2669 FOR TAX_TYPE_REC IN TAX_TYPE_CUR(v_trx_rec.customer_trx_line_id)
2670 LOOP
2671
2672
2673 IF NVL(v_register_code,'N') IN ('23D_EXPORT_WITHOUT_EXCISE','23D_EXPORT_EXCISE',
2674 '23D_DOMESTIC_EXCISE','23D_DOM_WITHOUT_EXCISE','BOND_REG')
2675 THEN
2676
2677
2678 IF Tax_Type_Rec.T_Type IN ('Excise','Addl. Excise','Other Excise') THEN
2679 TAX_TYPE_REC.tax_amt := 0;
2680 END IF;
2681 END IF;
2682 IF TAX_TYPE_REC.t_type = 'Freight' THEN
2683 v_line_type := 'FREIGHT';
2684 ELSE
2685 v_line_type := 'TAX';
2686 END IF;
2687 OPEN TAX_CCID_CUR(TAX_TYPE_REC.taxid);
2688 FETCH TAX_CCID_CUR INTO v_ccid;
2689 CLOSE TAX_CCID_CUR;
2690 IF TAX_TYPE_REC.t_type = 'TDS' THEN
2691 TAX_TYPE_REC.tax_amt := 0;
2692 END IF;
2693
2694
2695 INSERT INTO JAI_AR_TRX_INS_LINES_T ( paddr,
2696 extended_amount,
2697 customer_trx_line_id,
2698 customer_trx_id,
2699 set_of_books_id,
2700 link_to_cust_trx_line_id,
2701 line_type,
2702 uom_code,
2703 vat_tax_id,
2704 acctd_amount,
2705 amount,
2706 CODE_COMBINATION_ID,
2707 cust_trx_line_sales_rep_id,
2708 insert_update_flag,
2709 last_update_date,
2710 last_updated_by,
2711 creation_date,
2712 created_by,
2713 last_update_login,
2714 tax_rate,
2715 error_flag ,
2716 source ,
2717 org_id , -- bug# 3479348
2718 line_number) -- added by sriram bug# 3479348
2719 VALUES ( NULL, /* Previously passing v_paddr. Replaced with NULL by rallamse bug#4448789 */
2720 TAX_TYPE_REC.tax_amt,
2721 TAX_TYPE_REC.LINE_ID,
2722 v_customer_trx_id,
2723 v_books_id,
2724 v_customer_trx_line_id,
2725 v_line_type,
2726 TAX_TYPE_REC.uom,
2727 ln_tax_rate_id, --v_vat_tax, /* Modified by Ramananda for bug#4468353 due to ebtax uptake by AR */
2728 v_converted_rate * TAX_TYPE_REC.tax_amt,
2729 TAX_TYPE_REC.tax_amt,
2730 v_ccid,
2731 v_salesrep_id,
2732 'U',
2733 Sysdate,
2734 UID,
2735 Sysdate,
2736 UID,
2737 UID,
2738 TAX_TYPE_REC.tax_rate,
2739 'P',
2740 v_created_from,
2741 pr_new.org_id, -- added by sriram bug# 3479348
2742 TAX_TYPE_REC.tax_line_no); -- added by sriram bug# 3479348
2743
2744
2745 END LOOP;
2746
2747 EXCEPTION
2748 WHEN OTHERS THEN
2749 v_err_mesg := SUBSTR(SQLERRM,1,240);
2750
2751 /* RAISE_APPLICATION_ERROR(-20004,'error in processing the invoice ..' || v_trx_num || v_err_mesg);
2752 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'error in processing the invoice ..' || v_trx_num || v_err_mesg ; return ;
2753 END ;
2754
2755 END LOOP;
2756 EXCEPTION
2757 WHEN OTHERS THEN
2758 v_err_mesg := SUBSTR(SQLERRM,1,240);
2759
2760 --RAISE_APPLICATION_ERROR(-20003,'exception occured during processing invoice ..' || v_trx_num || v_err_mesg);
2761
2762 /* Added an exception block by Ramananda for bug#4570303 */
2763 Pv_return_code := jai_constants.unexpected_error;
2764 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARU_T4. ' ||
2765 'Exception occured during processing invoice ..' || v_trx_num || v_err_mesg ;
2766
2767 END ARU_T5 ;
2768
2769 /*
2770 REM +======================================================================+
2771 REM NAME ARU_T6
2772 REM
2773 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ARIUD_T1
2774 REM
2775 REM NOTES Refers to old trigger JAI_AR_RCTA_ARU_T9
2776 REM
2777 REM +======================================================================+
2778 */
2779 PROCEDURE ARU_T6 ( 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
2780
2781 /* --Ramananda for File.Sql.35, start */
2782 v_customer_id Number; -- := pr_new.Ship_To_Customer_ID;
2783 v_org_id Number; -- := NVL(pr_new.Org_ID,0);
2784 v_header_id Number; -- := pr_new.customer_trx_id;
2785 v_ship_to_site_use_id Number; -- := NVL(pr_new.Ship_To_Site_Use_ID,0);
2786 v_created_from Varchar2(30); -- := pr_new.Created_From;
2787 --v_row_id rowid; -- := pr_new.rowid;
2788 v_last_update_date Date; -- := pr_new.last_update_date;
2789 v_last_updated_by Number; -- := pr_new.last_updated_by;
2790 v_creation_date Date; -- := pr_new.creation_date;
2791 v_created_by Number; -- := pr_new.created_by;
2792 v_last_update_login Number; -- := pr_new.last_update_login;
2793 c_from_currency_code Varchar2(15); -- := pr_new.invoice_currency_code;
2794 c_conversion_type Varchar2(30); -- := pr_new.exchange_rate_type;
2795 c_conversion_date Date; -- := NVL(pr_new.exchange_date, pr_new.trx_date);
2796 c_conversion_rate Number; -- := NVL(pr_new.exchange_rate, 0);
2797 v_books_id Number; -- := pr_new.set_of_books_id;
2798 /* --Ramananda for File.Sql.35, end */
2799
2800 v_inventory_item_id Number ;
2801 v_address_id Number ;
2802 v_once_completed_flag Varchar2(1);
2803 v_organization_id Number ;
2804 v_tax_category_id Number ;
2805 v_price_list Number := 0;
2806 v_price_list_uom_code Varchar2(10);
2807 v_conversion_rate Number ;
2808 v_price_list_val Number := 0;
2809 v_converted_rate Number ;
2810 v_line_tax_amount Number := 0;
2811 v_trx_date Date; -- := pr_new.trx_date; --Ramananda for File.Sql.35
2812 v_service_type VARCHAR2(30); --added by ssawant
2813
2814
2815 Cursor address_cur(p_ship_to_site_use_id IN Number) IS
2816 SELECT cust_acct_site_id address_id
2817 FROM hz_cust_site_uses_all A /*Removed ra_site_uses_all for Bug# 4434287*/
2818 WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs */
2819 --WHERE A.site_use_id = NVL(p_ship_to_site_use_id,0);
2820
2821
2822 CURSOR price_list_cur(p_customer_id IN Number,p_inventory_item_id IN Number,
2823 p_address_id IN Number DEFAULT 0, v_uom_code VARCHAR2, p_trx_date DATE) IS
2824 select list_price, unit_code
2825 from so_price_list_lines
2826 where price_list_id in (select price_list_id from JAI_CMN_CUS_ADDRESSES
2827 where customer_id = p_customer_id and
2828 address_id = p_address_id) and
2829 inventory_item_id = p_inventory_item_id
2830 and unit_code = v_uom_code
2831 AND NVL(end_date_active,SYSDATE) >= p_trx_date;
2832
2833 CURSOR ORG_CUR IS
2834 SELECT organization_id
2835 FROM JAI_AR_TRX_APPS_RELS_T ;/*altered by rchandan for bug#4479131*/
2836
2837 CURSOR organization_cur IS
2838 SELECT organization_id
2839 FROM JAI_AR_TRXS
2840 WHERE trx_number = pr_new.recurred_from_trx_number;
2841
2842 CURSOR ONCE_COMPLETE_FLAG_CUR IS
2843 SELECT once_completed_flag
2844 FROM JAI_AR_TRXS
2845 WHERE customer_trx_id = v_header_id;
2846
2847 v_trans_type Varchar2(30);
2848
2849 Cursor transaction_type_cur IS
2850 Select a.type
2851 From RA_CUST_TRX_TYPES_ALL a
2852 Where a.cust_trx_type_id = pr_new.cust_trx_type_id
2853 And a.org_id = v_org_id; /* Modified by Ramananda for removal of SQL LITERALs */
2854 -- And NVL(a.org_id,0) = v_org_id;
2855
2856 Cursor Ar_Line_Cur IS
2857 Select Customer_Trx_Line_ID, Inventory_Item_ID, Unit_Code, Line_Amount, Quantity,unit_selling_price
2858 From JAI_AR_TRX_LINES
2859 Where Customer_Trx_ID = v_header_id;
2860
2861 /* Bug 5243532. Added by Lakshmi Gopalsami
2862 Removed the reference to set_of_books_cur
2863 which is selecting SOB from org_organization_definitions
2864 as the SOB will never by null in base table.
2865 */
2866 ln_vat_assessable_value JAI_AR_TRX_LINES.VAT_ASSESSABLE_VALUE%TYPE;
2867
2868 -- Added by sacsethi for bug 5631784 on 30-01-2007
2869 -- START 5631784
2870 LN_TCS_EXISTS NUMBER;
2871 LN_TCS_REGIME_ID JAI_RGM_DEFINITIONS.REGIME_ID%TYPE;
2872 LN_THRESHOLD_SLAB_ID JAI_AP_TDS_THHOLD_SLABS.THRESHOLD_SLAB_ID%TYPE;
2873 LN_THRESHOLD_TAX_CAT_ID JAI_AP_TDS_THHOLD_TAXES.TAX_CATEGORY_ID%TYPE;
2874
2875 CURSOR GC_CHK_RGM_TAX_EXISTS ( CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE
2876 , CP_RGM_TAX_TYPE JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE
2877 , CP_TAX_CATEGORY_ID JAI_CMN_TAX_CTGS_ALL.TAX_CATEGORY_ID%TYPE
2878 )
2879 IS
2880 SELECT COUNT(1)
2881 FROM JAI_CMN_TAX_CTG_LINES CATL
2882 ,JAI_CMN_TAXES_ALL CODES
2883 ,JAI_REGIME_TAX_TYPES_V JRTTV
2884 WHERE CATL.TAX_CATEGORY_ID = CP_TAX_CATEGORY_ID
2885 AND CATL.TAX_ID = CODES.TAX_ID
2886 AND CODES.TAX_TYPE = JRTTV.TAX_TYPE
2887 AND JRTTV.REGIME_CODE = CP_REGIME_CODE;
2888
2889 CURSOR GC_GET_REGIME_ID (CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE)
2890 IS
2891 SELECT REGIME_ID
2892 FROM JAI_RGM_DEFINITIONS
2893 WHERE REGIME_CODE = CP_REGIME_CODE;
2894
2895 LV_PROCESS_FLAG VARCHAR2 (2);
2896 LV_PROCESS_MESSAGE VARCHAR2 (1998);
2897
2898 --END 5631784
2899
2900 BEGIN
2901 pv_return_code := jai_constants.successful ;
2902 /*------------------------------------------------------------------------------------------
2903 FILENAME: JA_IN_AR_HDR_UPDATE_TRG.sql
2904
2905 CHANGE HISTORY:
2906 S.No Date Author and Details
2907 1. 10-Aug-2005 Aiyer bug 4545146 version 120.1
2908 Issue:-
2909 Deadlock on tables due to multiple triggers on the same table (in different sql files)
2910 firing in the same phase.
2911 Fix:-
2912 Multiple triggers on the same table have been merged into a single file to resolve
2913 the problem
2914 The following files have been stubbed:-
2915 jai_ar_rcta_t1.sql
2916 jai_ar_rcta_t2.sql
2917 jai_ar_rcta_t3.sql
2918 jai_ar_rcta_t4.sql
2919 jai_ar_rcta_t6.sql
2920 jai_ar_rcta_t7.sql
2921 jai_ar_rcta_t8.sql
2922 jai_ar_rcta_t9.sql
2923 Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers in the above files
2924
2925 2. 31-AUG-2006 SACSETHI FOR BUG 5631784 , 5228046 FILE VERSION 120.4
2926 FORWARD PORTING BUG FROM 11I BUG 4742259
2927 NEW ENH: TAX COLLECTION AT SOURCE IN RECEIVABLES
2928 Changes -
2929
2930 Object Type Object Name Change Description
2931 ---------------------------------------------------------------------------------------------
2932
2933 VARIABLE LN_TCS_EXISTS Add Variable Added
2934 VARIABLE LN_TCS_REGIME_ID Add Variable Added
2935 VARIABLE LN_THRESHOLD_SLAB_ID Add Variable Added
2936 VARIABLE LN_THRESHOLD_TAX_CAT_ID Add Variable Added
2937 CURSOR GC_CHK_RGM_TAX_EXISTS ADD CURSOR FOR GETTING COUNT(1) FROM TAXES
2938 CURSOR GC_GET_REGIME_ID ADD CURSOR FOR GETTING REGIME ID FOR TCS
2939 VARIABLE LV_PROCESS_FLAG ADD VARIABLE LV_PROCESS_FLAG IS PROCESS FLAG
2940 VARIABLE LV_PROCESS_MESSAGE ADD VARIABLE LV_PROCESS_MESSAGE IS PROCESS MESSAGE RETURN BY CALLING OBJECT IN RESPONSE
2941 CURSOR TAX_INFO_CUR MODIFY PRECEDENCE IS ADDED FROM 6 TO 10
2942 SQL STATEMENT JAI_AR_TRX_TAX_LINES MODIFY PRECEDENCE IS ADDED FROM 6 TO 10
2943 3. 27-Feb-2007 CSahoo for Bug 5390583, File Version 120.5 Forward Porting of 11i BUG 5357400
2944 When a change is done in the invoice currency code from the front end
2945 the change is being reflected in the JAI_AR_TRXS table.
2946 Added a IF clause for the same.
2947
2948
2949 4. 14-05-2007 ssawant for bug 5879769, File Version 120.6
2950 Objects was not compiling. so changes are done to make it compiling.
2951 5. 12-10-2007 ssumaith - bug#5597146 - file version 120.16
2952 when there is a change in currency at the invoice header , the excise av
2953 and vat av were calculated wrongly.
2954
2955 Future Dependencies For the release Of this Object:-
2956 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
2957 A datamodel change )
2958 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2959 Current Version Current Bug Dependent Files Version Author Date Remarks
2960 Of File On Bug/Patchset Dependent On
2961
2962 ja_in_ar_hdr_update_trg.sql
2963 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2964
2965 6. 21-Mar-2008 Jia for Bug#6859632
2966 Issue: TAX WILL BE ERROR IF SHIP-TO FILED OF AR TRANSACTION IS NOT ENTER AT FIRST.
2967 v_price_list_val didn't multiply quantity;
2968 Parameter is wrong when invoke jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes.
2969 Fixed: 1) v_price_list_val = v_price_list_val * quantity
2970 2) Add a default value for p_operation_flag parameter.
2971
2972 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
2973 /* --Ramananda for File.Sql.35 */
2974 v_customer_id := pr_new.Ship_To_Customer_ID;
2975 v_org_id := NVL(pr_new.Org_ID,0);
2976 v_header_id := pr_new.customer_trx_id;
2977 v_ship_to_site_use_id := NVL(pr_new.Ship_To_Site_Use_ID,0);
2978 v_created_from := pr_new.Created_From;
2979 --v_row_id := pr_new.rowid;
2980 v_last_update_date := pr_new.last_update_date;
2981 v_last_updated_by := pr_new.last_updated_by;
2982 v_creation_date := pr_new.creation_date;
2983 v_created_by := pr_new.created_by;
2984 v_last_update_login := pr_new.last_update_login;
2985 c_from_currency_code := pr_new.invoice_currency_code;
2986 c_conversion_type := pr_new.exchange_rate_type;
2987 c_conversion_date := NVL(pr_new.exchange_date, pr_new.trx_date);
2988 c_conversion_rate := NVL(pr_new.exchange_rate, 0);
2989 v_books_id := pr_new.set_of_books_id;
2990 v_trx_date := pr_new.trx_date;
2991
2992 /* --Ramananda for File.Sql.35 */
2993
2994 OPEN transaction_type_cur;
2995 FETCH transaction_type_cur INTO v_trans_type;
2996 CLOSE transaction_type_cur;
2997 IF NVL(v_trans_type,'N') <> 'INV' THEN
2998 Return;
2999 END IF;
3000
3001 OPEN ONCE_COMPLETE_FLAG_CUR;
3002 FETCH ONCE_COMPLETE_FLAG_CUR INTO v_once_completed_flag;
3003 CLOSE ONCE_COMPLETE_FLAG_CUR;
3004 IF NVL(v_once_completed_flag,'N') = 'Y' THEN
3005 RETURN;
3006 END IF;
3007 IF v_created_from in('RAXTRX','ARXREC') THEN
3008 RETURN;
3009 END IF;
3010 --Following If and update added by CSahoo - bug# 5390583
3011 IF pr_new.invoice_currency_code <> pr_old.invoice_currency_code THEN
3012
3013 UPDATE JAI_AR_TRXS
3014 SET invoice_currency_code = pr_new.invoice_currency_code ,
3015 exchange_rate_type = pr_new.exchange_rate_type ,
3016 exchange_date = pr_new.exchange_date ,
3017 exchange_rate = pr_new.exchange_rate
3018 WHERE customer_trx_id = pr_new.customer_trx_id;
3019
3020 END IF;
3021
3022 OPEN ORG_CUR;
3023 FETCH ORG_CUR INTO v_organization_id;
3024 CLOSE ORG_CUR;
3025 IF NVL(v_organization_id,999999) = 999999 THEN -- made 0 to 999999 because in case of setup business group setup , inventory organization value is 0
3026 -- which was causing code to return .- bug # 2846277
3027 OPEN organization_cur;
3028 FETCH organization_cur INTO v_organization_id;
3029 CLOSE organization_cur;
3030 END IF;
3031 IF NVL(v_organization_id,999999) = 999999 THEN -- made 0 to 999999 because in case of setup business group setup , inventory organization value is 0
3032 -- which was causing code to return .- bug # 2846277
3033 RETURN;
3034 END IF;
3035 OPEN address_cur(v_ship_to_site_use_id);
3036 FETCH address_cur INTO v_address_id;
3037 CLOSE address_cur;
3038
3039 FOR rec In Ar_Line_Cur
3040 LOOP
3041 v_tax_category_id := '';
3042 v_price_list := '';
3043 v_price_list_uom_code := '';
3044 v_conversion_rate := '';
3045 v_price_list_val := '';
3046 v_converted_rate := '';
3047 v_line_tax_amount := 0;
3048
3049 DELETE JAI_AR_TRX_TAX_LINES
3050 WHERE LINK_TO_CUST_TRX_LINE_ID = Rec.CUSTOMER_TRX_LINE_ID;
3051
3052 IF v_customer_id IS NOT NULL AND v_address_id IS NOT NULL
3053 THEN
3054 jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes(v_organization_id , v_customer_id ,v_ship_to_site_use_id ,
3055 rec.inventory_item_id ,v_header_id , rec.customer_trx_line_id,
3056 v_tax_category_id );
3057 ELSE
3058 jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes(v_organization_id , rec.inventory_item_id , v_tax_category_id );
3059 END IF;
3060 IF v_tax_category_id IS NOT NULL
3061 THEN
3062 OPEN price_list_cur(v_customer_id , rec.inventory_item_id, v_address_id,rec.unit_code, v_trx_date);
3063 FETCH price_list_cur INTO v_price_list, v_price_list_uom_code;
3064 CLOSE price_list_cur;
3065 IF v_price_list IS NULL
3066 THEN
3067 OPEN price_list_cur(v_customer_id ,rec.inventory_item_id, 0, rec.unit_code, v_trx_date);
3068 FETCH price_list_cur INTO v_price_list, v_price_list_uom_code;
3069 CLOSE price_list_cur;
3070 END IF;
3071 /*
3072 Added by ssumaith - 4245053
3073 */
3074 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value
3075 (
3076 p_party_id => v_customer_id ,
3077 p_party_site_id => v_ship_to_site_use_id ,
3078 p_inventory_item_id => rec.inventory_item_id ,
3079 p_uom_code => rec.unit_code ,
3080 p_default_price => nvl(rec.unit_selling_price,0) , /*ssumaith - bug#5597146 */
3081 p_ass_value_date => pr_new.trx_date ,
3082 p_party_type => 'C'
3083 );
3084
3085
3086 ln_vat_assessable_value := NVL(ln_vat_assessable_value,0) * rec.quantity;
3087
3088 v_line_tax_amount := nvl(rec.line_amount,0);
3089 IF NVL(v_price_list,0) > 0 THEN
3090 IF v_price_list_uom_code IS NOT NULL THEN
3091 INV_CONVERT.inv_um_conversion(rec.unit_code, v_price_list_uom_code, rec.inventory_item_id,v_conversion_rate);
3092 IF nvl(v_conversion_rate, 0) <= 0 THEN
3093 INV_CONVERT.inv_um_conversion(rec.unit_code, v_price_list_uom_code, 0,v_conversion_rate);
3094 IF nvl(v_conversion_rate, 0) <= 0 THEN
3095 v_conversion_rate := 0;
3096 END IF;
3097 END IF;
3098 END IF;
3099 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_books_id ,c_from_currency_code ,
3100 c_conversion_date ,c_conversion_type, c_conversion_rate);
3101 v_price_list := NVL(1/v_converted_rate,0) * nvl(v_price_list,0) * v_conversion_rate;
3102 v_price_list_val := nvl(rec.quantity * v_price_list,0);
3103 ELSE
3104 v_price_list := rec.unit_selling_price; /*ssumaith - bug#5597146 */
3105 --v_price_list_val := rec.unit_selling_price; /*ssumaith - bug#5597146 */
3106 v_price_list_val := rec.unit_selling_price * rec.quantity ; -- Modified by Jia for Bug#6859632
3107 END IF;
3108
3109 /*
3110 ln_vat_assessable_value added by ssumaith - 4245053 in the following call.
3111 */
3112
3113 ---------------------------------------------------------------------------------------------------------
3114 /** sacseth, bug# 5631784 - TCS enhancement */
3115 /** Check if TCS type of taxes exists for v_tax_category_id */
3116
3117 OPEN GC_CHK_RGM_TAX_EXISTS
3118 ( CP_REGIME_CODE => JAI_CONSTANTS.TCS_REGIME
3119 , CP_RGM_TAX_TYPE => JAI_CONSTANTS.TAX_TYPE_TCS
3120 , CP_TAX_CATEGORY_ID => V_TAX_CATEGORY_ID
3121 );
3122 FETCH GC_CHK_RGM_TAX_EXISTS INTO LN_TCS_EXISTS;
3123 CLOSE GC_CHK_RGM_TAX_EXISTS;
3124
3125 IF LN_TCS_EXISTS IS NOT NULL THEN
3126 /** TCS type of tax(s) are present */
3127 OPEN GC_GET_REGIME_ID ( CP_REGIME_CODE => JAI_CONSTANTS.TCS_REGIME);
3128 FETCH GC_GET_REGIME_ID INTO LN_TCS_REGIME_ID;
3129 CLOSE GC_GET_REGIME_ID;
3130
3131 /** Check current threshold slab. The following procedure returns null threshold_slab_id if threshold is not yet reached */
3132 jai_rgm_thhold_proc_pkg.get_threshold_slab_id
3133 ( p_regime_id => ln_tcs_regime_id
3134 , p_organization_id => v_organization_id
3135 , p_party_type => jai_constants.party_type_customer
3136 , p_party_id => v_customer_id
3137 , p_org_id => v_org_id
3138 , p_source_trx_date => v_trx_date
3139 , p_threshold_slab_id => ln_threshold_slab_id
3140 , p_process_flag => lv_process_flag
3141 , p_process_message => lv_process_message
3142 );
3143 if lv_process_flag <> jai_constants.successful then
3144 app_exception.raise_exception
3145 (exception_type => 'APP'
3146 ,exception_code => -20275
3147 ,exception_text => lv_process_message
3148 );
3149 end if;
3150
3151 if ln_threshold_slab_id is not null then
3152 /**
3153 Threshold is high and slab is available. Hence get tax_category defined for the salb to default additional taxes
3154 */
3155 jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id
3156 (
3157 p_threshold_slab_id => ln_threshold_slab_id
3158 , p_org_id => v_org_id
3159 , p_threshold_tax_cat_id => ln_threshold_tax_cat_id
3160 , p_process_flag => lv_process_flag
3161 , p_process_message => lv_process_message
3162 );
3163 if lv_process_flag <> jai_constants.successful then
3164 app_exception.raise_exception
3165 (exception_type => 'APP'
3166 ,exception_code => -20275
3167 ,exception_text => lv_process_message
3168 );
3169 end if;
3170 end if; /** ln_threshold_slab_id is not null */
3171 end if; /** ln_tcs_exists is not null */
3172 ---------------------------------------------------------------------------------------------------------
3173 jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes('AR_LINES' , v_tax_category_id , v_header_id, rec.customer_trx_line_id,
3174 v_price_list_val , v_line_tax_amount ,rec.inventory_item_id , NVL(rec.quantity,0),
3175 rec.unit_code , NULL , NULL , v_converted_rate ,v_creation_date , v_created_by ,
3176 v_last_update_date , v_last_updated_by , v_last_update_login
3177 , null --Add a default value by Jia for Bug#6859632
3178 , ln_vat_assessable_value
3179 -- Bug 6109941, Added by brathod for fwd porting bug 4742259
3180 , p_thhold_cat_base_tax_typ => jai_constants.tax_type_tcs
3181 , p_threshold_tax_cat_id => ln_threshold_tax_cat_id
3182 , p_source_trx_type => null
3183 , p_source_table_name => null
3184 , p_action => jai_constants.default_taxes
3185
3186 -- End 6109941
3187 );
3188
3189 END IF;
3190
3191 v_service_type:=JAI_AR_RCTLA_TRIGGER_PKG.get_service_type( v_customer_id,v_ship_to_site_use_id ,'C'); --added by csahoo for Bug#5879769
3192 UPDATE JAI_AR_TRX_LINES
3193 SET tax_category_id = v_tax_category_id,
3194 service_type_code = v_service_type, --added by csahoo for Bug#5879769
3195 assessable_value = nvl(v_price_list,0),
3196 vat_assessable_value = ln_vat_assessable_value,
3197 tax_amount = v_line_tax_amount,
3198 total_amount = nvl(rec.line_amount,0) + v_line_tax_amount,
3199 last_update_date = v_last_update_date,
3200 last_updated_by = v_last_updated_by,
3201 last_update_login = v_last_update_login
3202 WHERE Customer_Trx_Line_ID = rec.customer_trx_line_id;
3203
3204 END LOOP;
3205 /* Added an exception block by Ramananda for bug#4570303 */
3206 EXCEPTION
3207 WHEN OTHERS THEN
3208 Pv_return_code := jai_constants.unexpected_error;
3209 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARI_T7 ' || substr(sqlerrm,1,1900);
3210
3211 END ARU_T6 ;
3212 /*
3213 REM +======================================================================+
3214 REM NAME ARU_T7
3215 REM
3216 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ARIUD_T1
3217 REM
3218 REM NOTES Refers to old trigger JAI_AR_RCTA_ARU_T9
3219 REM
3220 REM
3221 REM CHANGE HISTORY:
3222 REM S.No Date Author and Details
3223 REM 1. 30/01/2007 SACSETHI FOR BUG 5631784
3224 REM PROCEDURE ARU_T7 IS NEWELY CREATED FOR PROVIDING TCS FUNCTIONALITY
3225 REM +======================================================================+
3226 */
3227 PROCEDURE ARU_T7
3228 ( 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 )
3229 IS
3230 LV_DOCUMENT_TYPE VARCHAR2(40);
3231 LN_REG_ID NUMBER;
3232 LV_ONCE_COMPLETED_FLAG JAI_AR_TRXS.ONCE_COMPLETED_FLAG%TYPE;
3233 V_HEADER_ID NUMBER;
3234
3235
3236 CURSOR ONCE_COMPLETE_FLAG_CUR IS
3237 SELECT ONCE_COMPLETED_FLAG
3238 FROM JAI_AR_TRXS
3239 WHERE CUSTOMER_TRX_ID = V_HEADER_ID;
3240
3241 BEGIN
3242
3243 V_HEADER_ID := PR_NEW.CUSTOMER_TRX_ID;
3244 IF NVL(PR_NEW.COMPLETE_FLAG,JAI_CONSTANTS.NO) = JAI_CONSTANTS.YES THEN
3245 /** Invoice is getting COMPLETED */
3246 LV_DOCUMENT_TYPE := JAI_CONSTANTS.TRX_TYPE_INV_COMP;
3247 /*********
3248 || When the invoice is getting completed for the very first time (once_complete_flag is still null or 'N') then pass the
3249 || final TCS accounting for the TCS type of taxes belonging to the manual invoice only
3250 || This is not applicable for the imported invoices.
3251 *********/
3252
3253 OPEN ONCE_COMPLETE_FLAG_CUR;
3254 FETCH ONCE_COMPLETE_FLAG_CUR INTO LV_ONCE_COMPLETED_FLAG;
3255 CLOSE ONCE_COMPLETE_FLAG_CUR;
3256
3257 IF Pr_new.created_from <> 'RAXTRX' AND
3258 lv_once_completed_flag = jai_constants.no
3259 THEN
3260 -- jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Calling -> jai_ar_tcs_rep_pkg.ar_accounting ');
3261 JAI_AR_TCS_REP_PKG.AR_ACCOUNTING ( P_RACT => PR_NEW ,
3262 P_PROCESS_FLAG => PV_RETURN_CODE ,
3263 P_PROCESS_MESSAGE => PV_RETURN_MESSAGE
3264 );
3265 -- JAI_CMN_DEBUG_CONTEXTS_PKG.PRINT ( PN_REG_ID => LN_REG_ID ,
3266 -- PV_LOG_MSG => 'RETURNED FROM JAI_AR_TCS_REP_PKG.AR_ACCOUNTING ' || CHR(10)
3267 -- ||'P_PROCESS_FLAG=' ||PV_ERR_FLG
3268 -- );
3269 -- IF PV_ERR_FLG <> JAI_CONSTANTS.SUCCESSFUL THEN
3270 -- jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3271 -- pv_log_msg => 'Error during processing of jai_ar_tcs_rep_pkg.ar_accounting '||chr(10)
3272 -- ||'p_process_flag=' ||pv_err_flg||chr(10)
3273 -- ||'p_process_message='||pv_err_msg
3274 -- );
3275 --
3276 -- return;
3277 -- END IF;
3278 END IF;
3279
3280 ELSIF NVL(PR_NEW.COMPLETE_FLAG,JAI_CONSTANTS.NO) = JAI_CONSTANTS.NO THEN
3281 /** INVOICE IS GETTING INCOMPLETED */
3282 LV_DOCUMENT_TYPE := JAI_CONSTANTS.TRX_TYPE_INV_INCOMP;
3283 END IF;
3284
3285 -- jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Calling -> JAI_AR_TCS_REP_PKG.PROCESS_TRANSACTIONS');
3286 JAI_AR_TCS_REP_PKG.PROCESS_TRANSACTIONS
3287 ( P_RACT => PR_NEW
3288 , P_EVENT => JAI_CONSTANTS.TRX_EVENT_COMPLETION
3289 , P_PROCESS_FLAG => PV_RETURN_CODE
3290 , P_PROCESS_MESSAGE => PV_RETURN_MESSAGE
3291 );
3292 -- jai_cmn_debug_contexts_pkg.print (ln_reg_id
3293 -- , 'Process Result: ' || chr(10)
3294 -- ||'p_process_flag=' ||PV_RETURN_CODE||chr(10)
3295 -- ||'p_process_message='||PV_RETURN_MESSAGE||chr(10)
3296 -- );
3297 IF PV_RETURN_CODE <> JAI_CONSTANTS.SUCCESSFUL THEN
3298 RETURN;
3299 END IF;
3300
3301 END ARU_T7;
3302
3303
3304 /*
3305 REM +======================================================================+
3306 REM NAME ASI_T1
3307 REM
3308 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ASI_T1
3309 REM
3310 REM NOTES Refers to old trigger JAI_AR_RCTA_ASI_T1
3311 REM
3312 REM +======================================================================+
3313 */
3314 PROCEDURE ASI_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
3315 /*---------------------------------------------------------------------------
3316 HISTORY :
3317 1. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
3318 DB Entity as required for CASE COMPLAINCE. Version 116.1
3319
3320 2. 10-Jun-2005 File Version: 116.2
3321 Removal of SQL LITERALs is done
3322
3323 3. 10-Jun-2005 rallamse bug#4448789 116.3
3324 Added legal_entity_id for table JAI_AR_TRXS in insert statement
3325
3326 4. 26-07-2005 rallamse bug#4510143 120.2
3327 Modified legal_entity_id for table JAI_AR_TRXS to get from header_info_cur cursor
3328
3329 5. 10-Aug-2005 Aiyer bug 4545146 version 120.1
3330 Issue:-
3331 Deadlock on tables due to multiple triggers on the same table (in different sql files)
3332 firing in the same phase.
3333 Fix:-
3334 Multiple triggers on the same table have been merged into a single file to resolve
3335 the problem
3336 The following files have been stubbed:-
3337 jai_ar_rcta_t1.sql
3338 jai_ar_rcta_t2.sql
3339 jai_ar_rcta_t3.sql
3340 jai_ar_rcta_t4.sql
3341 jai_ar_rcta_t6.sql
3342 jai_ar_rcta_t7.sql
3343 jai_ar_rcta_t8.sql
3344 jai_ar_rcta_t9.sql
3345 Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers
3346
3347 ---------------------------------------------------------------------------------------------------*/
3348 v_created_from Varchar2(30);
3349 v_header_id Number;
3350 v_customer_trx_line_id Number;
3351 v_recurred_from_trx_number Varchar2(20);
3352 v_trx_number Varchar2(20);
3353 v_once_completed_flag Varchar2(1);
3354 x Number;
3355 v_batch_source_id Number := 0;
3356 v_parent_header_id Number;
3357 v_line_tax_amount Number := 0;
3358 v_header_tax_amount Number := 0;
3359 v_last_update_date Date;
3360 v_last_updated_by Number;
3361 v_creation_date Date;
3362 v_created_by Number;
3363 v_last_update_login Number;
3364 v_service_type VARCHAR2(30); --added by ssawant
3365
3366 CURSOR temp_fetch IS
3367 SELECT trx_number, customer_trx_id, recurred_from_trx_number, batch_source_id, created_from,
3368 creation_date, created_by, last_update_date, last_updated_by, last_update_login
3369 FROM JAI_AR_TRX_COPY_HDR_T
3370 ORDER BY customer_trx_id;
3371
3372 CURSOR ONCE_COMPLETE_FLAG_CUR(p_header_id IN NUMBER, p_batch_source_id IN Number) IS
3373 SELECT once_completed_flag, 1
3374 FROM JAI_AR_TRXS
3375 WHERE customer_trx_id = p_header_id
3376 AND NVL(batch_source_id,0) = p_batch_source_id;
3377
3378 CURSOR parent_header_id(p_recurred_from_trx_number IN Varchar2, p_batch_source_id IN Number) IS
3379 SELECT a.customer_trx_id
3380 FROM JAI_AR_TRXS a
3381 WHERE a.trx_number = p_recurred_from_trx_number
3382 AND NVL(batch_source_id,0) = p_batch_source_id;
3383
3384 CURSOR LINES_INFO_CUR(p_parent_header_id IN Number) IS
3385 SELECT customer_trx_line_id, line_number, description, inventory_item_id, unit_code, quantity, tax_category_id,
3386 auto_invoice_flag, unit_selling_price, line_amount, gl_date,
3387 tax_amount,total_amount,assessable_value
3388 FROM JAI_AR_TRX_LINES
3389 WHERE customer_trx_id = p_parent_header_id
3390 ORDER BY customer_trx_line_id;
3391
3392 CURSOR TAX_INFO_CUR(p_parent_line_id IN NUMBER) IS
3393 SELECT a.tax_line_no,
3394 a.precedence_1,a.precedence_2, a.precedence_3, a.precedence_4,a.precedence_5,
3395 a.precedence_6,a.precedence_7, a.precedence_8, a.precedence_9,a.precedence_10, -- Date 06/12/2006 Bug 5228046 added by SACSETHI
3396 a.tax_id, a.tax_rate, a.qty_rate, a.uom, a.tax_amount, a.base_tax_amount, a.func_tax_amount,
3397 b.end_date valid_date, b.tax_type
3398 FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
3399 WHERE a.link_to_cust_trx_line_id = p_parent_line_id
3400 AND a.tax_id = b.tax_id
3401 ORDER BY a.tax_line_no;
3402
3403
3404 CURSOR HEADER_INFO_CUR(p_recurred_from_trx_number IN Varchar2, p_batch_source_id IN Number) IS
3405 SELECT CUSTOMER_TRX_ID, ORGANIZATION_ID, LOCATION_ID, UPDATE_RG_FLAG, UPDATE_RG23D_FLAG,
3406 TAX_AMOUNT, LINE_AMOUNT, TOTAL_AMOUNT, BATCH_SOURCE_ID,legal_entity_id /* added rallamse bug#4448789 */
3407 FROM JAI_AR_TRXS
3408 WHERE trx_number = p_recurred_from_trx_number
3409 AND NVL(batch_source_id,0) = p_batch_source_id;
3410
3411 BEGIN
3412 pv_return_code := jai_constants.successful ;
3413
3414 OPEN temp_fetch;
3415 FETCH temp_fetch INTO v_trx_number, v_header_id, v_recurred_from_trx_number, v_batch_source_id,
3416 v_created_from, v_creation_date, v_created_by,
3417 v_last_update_date, v_last_updated_by, v_last_update_login;
3418 CLOSE temp_fetch;
3419
3420 DELETE JAI_AR_TRX_COPY_HDR_T
3421 WHERE customer_trx_id = v_header_id;
3422
3423 IF v_trx_number IS NULL THEN
3424 Return;
3425 END IF;
3426 IF v_created_from <>'ARXREC' THEN
3427 RETURN;
3428 END IF;
3429
3430 OPEN ONCE_COMPLETE_FLAG_CUR(v_header_id, v_batch_source_id);
3431 FETCH ONCE_COMPLETE_FLAG_CUR INTO v_once_completed_flag, x;
3432 CLOSE ONCE_COMPLETE_FLAG_CUR;
3433 IF NVL(v_once_completed_flag,'N') = 'Y' THEN
3434 RETURN;
3435 END IF;
3436
3437 OPEN parent_header_id(v_recurred_from_trx_number, v_batch_source_id);
3438 FETCH parent_header_id INTO v_parent_header_id;
3439 CLOSE parent_header_id;
3440
3441 IF NVL(x,0) <> 1 THEN
3442
3443 FOR hdr in HEADER_INFO_CUR(v_recurred_from_trx_number, v_batch_source_id)
3444 LOOP
3445 INSERT INTO JAI_AR_TRXS
3446 (customer_trx_id, organization_id, location_id, update_rg23d_flag,
3447 update_rg_flag, trx_number, once_completed_flag,
3448 line_amount, batch_source_id, created_from,
3449 creation_date, created_by,
3450 last_update_date,last_updated_by, last_update_login,
3451 legal_entity_id) /* added rallamse bug#4448789 */
3452 VALUES(v_header_id, hdr.organization_id, hdr.location_id, hdr.update_rg23d_flag,
3453 hdr.update_rg_flag, v_trx_number, 'N',
3454 hdr.line_amount, hdr.batch_source_id, v_created_from ,
3455 v_creation_date, v_created_by,
3456 v_last_update_date, v_last_updated_by, v_last_update_login,
3457 hdr.legal_entity_id); /* added rallamse bug#4448789 */
3458 END LOOP;
3459 END IF;
3460 --added by ssawant to replace r_new to pr_new
3461 v_service_type:=JAI_AR_RCTLA_TRIGGER_PKG.get_service_type( NVL(pr_new.SHIP_TO_CUSTOMER_ID ,pr_new.BILL_TO_CUSTOMER_ID) ,
3462 NVL(pr_new.SHIP_TO_SITE_USE_ID, pr_new.BILL_TO_SITE_USE_ID),'C'); -- added by csahoo for bug#5879769
3463
3464 FOR rec in LINES_INFO_CUR(v_parent_header_id)
3465 LOOP
3466
3467 -- SELECT ra_customer_trx_lines_s.nextval INTO v_customer_trx_line_id FROM Dual;
3468
3469 INSERT INTO JAI_AR_TRX_LINES
3470 (customer_trx_line_id, line_number,
3471 customer_trx_id, description,
3472 inventory_item_id, unit_code,
3473 quantity, tax_category_id,auto_invoice_flag ,
3474 unit_selling_price, line_amount, gl_date,
3475 assessable_value,
3476 creation_date, created_by,
3477 last_update_date,last_updated_by,
3478 last_update_login,
3479 service_type_code) --added by csahoo for Bug#5879769
3480 VALUES(ra_customer_trx_lines_s.nextval,
3481 --v_customer_trx_line_id, /* Commented by Ramananda as a part of removal of SQL LITERALs */
3482 rec.line_number,
3483 v_header_id,rec.description,
3484 rec.inventory_item_id, rec.unit_code,
3485 rec.quantity, rec.tax_category_id,rec.auto_invoice_flag,
3486 rec.unit_selling_price,rec.line_amount, rec.gl_date,
3487 rec.assessable_value,
3488 v_creation_date, v_created_by, v_last_update_date,
3489 v_last_updated_by, v_last_update_login,
3490 v_service_type) --added by csahoo for Bug#5879769
3491 returning customer_trx_line_id into v_customer_trx_line_id ;
3492
3493 FOR rec1 in TAX_INFO_CUR(rec.customer_trx_line_id)
3494 LOOP
3495 IF rec1.valid_date < sysdate THEN
3496 rec1.tax_amount := 0;
3497 rec1.base_tax_amount := 0;
3498 rec1.func_tax_amount := 0;
3499 END IF;
3500 INSERT INTO JAI_AR_TRX_TAX_LINES(customer_trx_line_id, link_to_cust_trx_line_id, tax_line_no,
3501 precedence_1,precedence_2, precedence_3, precedence_4,precedence_5,
3502 precedence_6,precedence_7, precedence_8, precedence_9,precedence_10, -- Date 06/12/2006 Bug 5228046 added by SACSETHI
3503 tax_id, tax_rate, qty_rate, uom,
3504 tax_amount, base_tax_amount, func_tax_amount,
3505 creation_date, created_by, last_update_date,
3506 last_updated_by, last_update_login)
3507 VALUES( ra_customer_trx_lines_s.nextval, v_customer_trx_line_id, rec1.tax_line_no,
3508 rec1.precedence_1, rec1.precedence_2, rec1.precedence_3, rec1.precedence_4, rec1.precedence_5,
3509 rec1.precedence_6, rec1.precedence_7, rec1.precedence_8, rec1.precedence_9, rec1.precedence_10, -- Date 06/12/2006 Bug 5228046 added by SACSETHI
3510 rec1.tax_id, rec1.tax_rate, rec1.qty_rate, rec1.uom,
3511 rec1.tax_amount, rec1.base_tax_amount, rec1.func_tax_amount,
3512 v_creation_date, v_created_by, v_last_update_date,
3513 v_last_updated_by, v_last_update_login);
3514
3515 IF rec1.tax_type <> 'TDS' THEN
3516 v_line_tax_amount := nvl(v_line_tax_amount,0) + nvl(rec1.tax_amount,0);
3517 END IF;
3518
3519 IF rec1.tax_type in ('Excise', 'Addl. Excise', 'Other Excise') THEN
3520 v_header_tax_amount := nvl(v_header_tax_amount,0) + nvl(rec1.tax_amount,0);
3521 END IF;
3522
3523 END LOOP;
3524 UPDATE JAI_AR_TRX_LINES
3525 SET tax_amount = v_line_tax_amount,
3526 total_amount = nvl(line_amount,0) + v_line_tax_amount
3527 WHERE customer_trx_line_id = v_customer_trx_line_id;
3528 v_line_tax_amount := 0;
3529 END LOOP;
3530
3531 UPDATE JAI_AR_TRXS
3532 SET tax_amount = v_header_tax_amount,
3533 total_amount = nvl(line_amount,0) + v_header_tax_amount
3534 WHERE customer_trx_id = v_header_id;
3535 v_header_tax_amount := 0;
3536
3537 END ASI_T1 ;
3538
3539 --added this procedure for bug#7450481
3540 PROCEDURE ARD_T1 ( pr_old t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
3541 BEGIN
3542 DELETE JAI_AR_TRXS
3543 WHERE customer_trx_id = pr_old.customer_trx_id ;
3544
3545 pv_return_message := '';
3546 pv_return_code := jai_constants.successful;
3547
3548 EXCEPTION
3549 when others then
3550 pv_return_message := substr (sqlerrm,1,1999);
3551 pv_return_code := jai_constants.unexpected_error;
3552 END ARD_T1;
3553
3554 END JAI_AR_RCTA_TRIGGER_PKG ;
[Home] [Help]