1 PACKAGE BODY JAI_AR_RCTA_TRIGGER_PKG AS
2 /* $Header: jai_ar_rcta_t.plb 120.44.12020000.3 2013/04/08 03:29:16 vkaranam 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. 01-Sep-2010 Bo Li 10043656 12.1.3
947 REM -----------------------------------------------------------------------
948 REM Comments:- GST Enhancement
949 REM -----------------------------------------------------------------------
950 REM 3. 10-Feb-2011 Zhiwei For Open Interface ER bug#11683927
951 REM -----------------------------------------------------------------------
952 REM -----------------------------------------------------------------------
953 REM 4.
954 REM -----------------------------------------------------------------------
955 REM
956 REM 5.vkaranam for bug#11664009
957 REM RCA: THE AR ACCOUNTING PROGRAMME IS SHOWING WARNING
958 REM this issue occurs if the user completes themanual invoice without the AR tax
959 REM and freight defaultation concurrent gets completed.
960 REM FIX: modified cur_chk_temp_lines_exist cursor.
961 REM
962 REM -----------------------------------------------------------------------
963 REM 6. 07-Apr-2011 Xiao for bug#
964 REM -----------------------------------------------------------------------
965 REM Comments: Add nvl(ln_external_flag, 0) = 0 for bug#11936390.
966 REM The if condition will include Manual AR or Standard event.
967 REM -----------------------------------------------------------------------
968 REM+======================================================================+
969 */
970 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
971 v_org_id NUMBER;
972 v_loc_id NUMBER;
973 v_reg_code VARCHAR2(30);
974 v_update_rg VARCHAR2(1);
975 v_reg_type VARCHAR2(10);
976 v_excise_paid_register VARCHAR2(10);
977 v_rg23a_type VARCHAR2(10);
978 v_rg23c_type VARCHAR2(10);
979 v_complete_flag VARCHAR2(1); -- := 'N'; --Ramananda for File.Sql.35
980 v_rg_flag VARCHAR2(1); -- := 'N'; --Ramananda for File.Sql.35
981 v_update_rg_flag VARCHAR2(1); -- := 'N'; --Ramananda for File.Sql.35
982 -- v_update_rg23d_flag VARCHAR2(30); /*Bug 5040383*/
983 v_tax_amount NUMBER := 0;
984 v_rg23a_tax_amount NUMBER := 0;
985 v_rg23c_tax_amount NUMBER := 0;
986 v_other_tax_amount NUMBER := 0;
987 v_basic_ed NUMBER := 0;
988 v_additional_ed NUMBER := 0;
989 v_other_ed NUMBER := 0;
990 v_item_class VARCHAR2(10); -- := 'N'; --Ramananda for File.Sql.35
991 v_excise_flag VARCHAR2(1);
992 v_fin_year NUMBER;
993 v_gp_1 NUMBER := 0;
994 v_gp_2 NUMBER := 0;
995 v_rg23a_bal NUMBER := 0;
996 v_rg23c_bal NUMBER := 0;
997 v_pla_bal NUMBER := 0;
998 v_invoice_no VARCHAR2(200);
999 v_other_invoice_no NUMBER ;
1000 v_rg23a_invoice_no NUMBER ;
1001 v_rg23c_invoice_no NUMBER ;
1002 rg23a NUMBER :=0;
1003 rg23c NUMBER :=0;
1004 pla NUMBER :=0;
1005 --v_row_id ROWID; -- := pr_new.ROWID; --Ramananda for File.Sql.35
1006 v_parent_trx_number VARCHAR2(20); -- := pr_new.recurred_from_trx_number; --Ramananda for File.Sql.35
1007 v_register_balance NUMBER := 0;
1008 v_rg23d_register_balance NUMBER := 0;
1009 v_customer_trx_id NUMBER; -- := pr_old.customer_trx_id; --Ramananda for File.Sql.35
1010 v_converted_rate NUMBER := 1;
1011 v_ssi_unit_flag VARCHAR2(1);
1012 v_trans_type VARCHAR2(30);
1013 v_last_update_date DATE; -- := pr_new.last_update_date; --Ramananda for File.Sql.35
1014 v_last_updated_by NUMBER; -- := pr_new.last_updated_by; --Ramananda for File.Sql.35
1015 v_creation_date DATE; -- := pr_new.creation_date; --Ramananda for File.Sql.35
1016 v_created_by NUMBER; -- := pr_new.created_by; --Ramananda for File.Sql.35
1017 v_last_update_login NUMBER; -- := pr_new.last_update_login; --Ramananda for File.Sql.35
1018 v_bond_tax_amount NUMBER := 0;
1019 V_rg23d_tax_amount NUMBER := 0;
1020 v_modvat_tax_rate NUMBER;
1021 v_exempt_bal NUMBER;
1022 v_matched_qty NUMBER;
1023 VSQLERRM VARCHAR2(240);
1024 v_trans_type_up VARCHAR2(3);
1025 v_order_invoice_type_up VARCHAR2(25);---ashish 10june
1026 v_register_code_up VARCHAR2(25);---ashish 10june
1027 v_errbuf VARCHAR2(250);
1028 -- added by sriram - bug # 3021588
1029 v_register_id JAI_OM_OE_BOND_REG_HDRS.register_id%type;
1030 v_register_exp_date JAI_OM_OE_BOND_REG_HDRS.bond_expiry_date%type;
1031 v_lou_flag JAI_OM_OE_BOND_REG_HDRS.lou_flag%type;
1032 -- added by sriram - bug # 3021588
1033 v_trading_flag JAI_CMN_INVENTORY_ORGS.TRADING%TYPE;/*Bug#4601570 bduvarag*/
1034 v_update_rg23d_flag JAI_AR_TRXS.UPDATE_RG23D_FLAG%TYPE;/*Bug#4601570 bduvarag*/
1035
1036 /*
1037 || Start of bug 4101549
1038 || Cursor modified by aiyer
1039 */
1040 CURSOR complete_cur
1041 IS
1042 SELECT
1043 organization_id ,
1044 location_id ,
1045 once_completed_flag ,
1046 decode(once_completed_flag,'A','RG23A','C','RG23C','P','PLA') register_type,
1047 update_rg_flag, -- update_rg_flag added by sriram - bug# 3496577
1048 nvl(update_rg23d_flag,'N') /*Bug 5040383*/
1049 FROM
1050 JAI_AR_TRXS
1051 WHERE
1052 customer_trx_id = v_customer_trx_id;
1053
1054
1055 --2001/06/22 Anuradha Parthasarathy
1056 CURSOR REG_BALANCE_CUR(p_org_id IN NUMBER,p_loc_id IN NUMBER) IS
1057 SELECT NVL(rg23a_balance,0) rg23a_balance ,NVL(rg23c_balance,0) rg23c_balance,NVL(pla_balance,0) pla_balance
1058 FROM JAI_CMN_RG_BALANCES
1059 WHERE organization_id = p_org_id AND location_id = p_loc_id;
1060
1061 CURSOR register_code_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1062 SELECT register_code
1063 FROM JAI_OM_OE_BOND_REG_HDRS
1064 WHERE organization_id = p_org_id AND location_id = p_loc_id AND
1065 register_id IN (SELECT register_id
1066 FROM JAI_OM_OE_BOND_REG_DTLS
1067 WHERE order_type_id = pr_new.batch_source_id AND order_flag= 'N'); /* Modified by Ramananda for removal of SQL LITERALs */
1068
1069 CURSOR fin_year_cur(p_org_id IN NUMBER) IS
1070 SELECT MAX(A.fin_year)
1071 FROM JAI_CMN_FIN_YEARS A
1072 WHERE organization_id = p_org_id AND fin_active_flag = 'Y';
1073
1074 CURSOR tax_amount_cur IS
1075 SELECT NVL(tax_amount,0) tax_amount
1076 FROM JAI_AR_TRXS
1077 WHERE customer_trx_id = v_customer_trx_id;
1078
1079 CURSOR preference_reg_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1080 SELECT pref_rg23a , pref_rg23c , pref_pla
1081 FROM JAI_CMN_INVENTORY_ORGS
1082 WHERE organization_id = p_org_id AND
1083 location_id = p_loc_id;
1084
1085 CURSOR item_class_cur(P_ORG_ID IN NUMBER, P_Item_id IN NUMBER) IS
1086 SELECT item_class, excise_flag
1087 FROM JAI_INV_ITM_SETUPS
1088 WHERE inventory_item_id = P_Item_Id AND
1089 ORGANIZATION_ID = P_ORG_ID;
1090
1091 CURSOR organization_cur IS
1092 SELECT organization_id,location_id
1093 FROM JAI_AR_TRXS
1094 WHERE trx_number = v_parent_trx_number;
1095
1096 CURSOR register_balance_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1097 SELECT NVL(register_balance,0) register_balance
1098 FROM JAI_OM_OE_BOND_TRXS
1099 WHERE transaction_id = (SELECT MAX(A.transaction_id)
1100 FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
1101 WHERE A.register_id = B.register_id
1102 AND B.organization_id = p_org_id AND B.location_id = p_loc_id);
1103
1104 CURSOR register_balance_cur1(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1105 SELECT NVL(rg23d_register_balance,0) rg23d_register_balance
1106 FROM JAI_OM_OE_BOND_TRXS
1107 WHERE transaction_id = (SELECT MAX(A.transaction_id)
1108 FROM JAI_OM_OE_BOND_TRXS A, JAI_OM_OE_BOND_REG_HDRS B
1109 WHERE A.register_id = B.register_id
1110 AND B.organization_id = p_org_id AND B.location_id = p_loc_id);
1111
1112 CURSOR line_cur IS
1113 SELECT customer_trx_line_id, inventory_item_id, quantity,line_number,
1114 excise_exempt_type, assessable_value
1115 FROM JAI_AR_TRX_LINES
1116 WHERE customer_trx_id = v_customer_trx_id
1117 ORDER BY customer_trx_line_id;
1118
1119 CURSOR matched_qty_cur (p_customer_trx_line_id NUMBER) IS
1120 SELECT SUM(quantity_applied)
1121 FROM JAI_CMN_MATCH_RECEIPTS
1122 WHERE ref_line_id = p_customer_trx_line_id;
1123
1124 CURSOR excise_cal_cur(p_line_id IN NUMBER, p_inventory_item_id IN NUMBER, p_org_id IN NUMBER) IS
1125 SELECT
1126 A.tax_id,
1127 A.tax_rate t_rate,
1128 A.tax_amount tax_amt,
1129 A.func_tax_amount func_amt,
1130 b.tax_type t_type,
1131 b.stform_type,
1132 A.tax_line_no
1133 FROM JAI_AR_TRX_TAX_LINES A ,
1134 JAI_CMN_TAXES_ALL B,
1135 JAI_INV_ITM_SETUPS C
1136 WHERE link_to_cust_trx_line_id = p_line_id
1137 AND b.tax_type IN --('Excise','Addl. Excise','Other Excise') /* Modified by Ramananda for removal of SQL LITERALs */
1138 (jai_constants.tax_type_excise,jai_constants.tax_type_exc_additional,jai_constants.tax_type_exc_other)
1139 AND A.tax_id = b.tax_id
1140 AND c.inventory_item_id = p_inventory_item_id
1141 AND c.organization_id = p_org_id
1142 --AND c.item_class IN ('RMIN','RMEX','CGEX','CGIN','CCEX','CCIN','FGIN','FGEX') /* Modified by Ramananda for removal of SQL LITERALs */
1143 AND c.item_class IN ( jai_constants.item_class_rmin, jai_constants.item_class_rmex,
1144 jai_constants.item_class_cgex, jai_constants.item_class_cgin,
1145 jai_constants.item_class_ccex, jai_constants.item_class_ccin,
1146 jai_constants.item_class_fgin, jai_constants.item_class_fgex
1147 )
1148 ORDER BY 1;
1149
1150 CURSOR ssi_unit_flag_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER) IS
1151 SELECT ssi_unit_flag, nvl(trading,'N')/*Bug#4601570 bduvarag*/
1152 FROM JAI_CMN_INVENTORY_ORGS
1153 WHERE organization_id = p_org_id AND
1154 location_id = p_loc_id;
1155
1156 CURSOR transaction_type_cur IS
1157 SELECT TYPE
1158 FROM RA_CUST_TRX_TYPES_ALL
1159 WHERE cust_trx_type_id = pr_new.cust_trx_type_id
1160 AND (org_id = pr_new.org_id
1161 OR
1162 (org_id is null and pr_new.org_id is null)) ; /* Modified by Ramananda for removal of SQL LITERALs */
1163
1164
1165 /* Code Added For Generation of Excise Invoice Number */
1166 CURSOR Batch_Source_Name_Cur IS
1167 SELECT name
1168 FROM Ra_Batch_Sources_All
1169 WHERE batch_source_id = pr_new.batch_source_id
1170 AND (org_id = pr_new.org_id
1171 OR
1172 ( org_id is null AND pr_new.org_id is null)); /* Modified by Ramananda for removal of SQL LITERALs */
1173
1174 --------------chnages in cursor definition
1175
1176 CURSOR Def_Excise_Invoice_Cur(p_organization_id IN NUMBER, p_location_id IN NUMBER, p_fin_year IN NUMBER,
1177 p_batch_name IN VARCHAR2, p_register_code IN VARCHAR2) IS
1178 SELECT start_number, end_number, jump_by, prefix
1179 FROM JAI_CMN_RG_EXC_INV_NOS
1180 WHERE organization_id = p_organization_id
1181 AND location_id = p_location_id
1182 AND fin_year = p_fin_year
1183 AND transaction_type IN ( 'I','DOM','EXP') --ashish 20jun02
1184 AND order_invoice_type = p_batch_name
1185 AND register_code = p_register_code ; /* Modified by Ramananda for removal of SQL LITERALs */
1186
1187 CURSOR excise_invoice_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER, p_fin_year IN NUMBER) IS
1188 SELECT NVL(MAX(GP1),0),NVL(MAX(GP2),0)
1189 FROM JAI_CMN_RG_EXC_INV_NOS
1190 WHERE organization_id = p_org_id
1191 AND location_id = p_loc_id
1192 AND fin_year = p_fin_year
1193 AND transaction_type IS NULL
1194 AND order_invoice_type IS NULL
1195 AND register_code IS NULL;
1196
1197 CURSOR Register_Code_Meaning_Cur(p_register_code IN VARCHAR2,cp_register_type ja_lookups.lookup_type%type ) IS
1198 SELECT meaning
1199 FROM ja_lookups
1200 WHERE lookup_code = p_register_code
1201 AND lookup_type = cp_register_type; /*'JAI_REGISTER_TYPE'; Ramananda for removal of SQL LITERALs */
1202
1203 --added by GD
1204 CURSOR for_modvat_percentage(v_org_id NUMBER, v_location_id NUMBER) IS
1205 SELECT MODVAT_REVERSE_PERCENT
1206 FROM JAI_CMN_INVENTORY_ORGS
1207 WHERE organization_id = v_org_id
1208 AND (location_id = v_location_id
1209 OR
1210 (location_id is NULL and v_location_id is NULL)); /* Modified by Ramananda for removal of SQL LITERALs */
1211
1212 CURSOR for_modvat_tax_rate(p_cust_trx_line_id NUMBER) IS
1213 SELECT A.tax_rate
1214 FROM JAI_AR_TRX_TAX_LINES A, JAI_CMN_TAXES_ALL b
1215 WHERE A.tax_id = b.tax_id
1216 AND A.link_to_cust_trx_line_id = p_cust_trx_line_id
1217 AND b.tax_type = jai_constants.tax_type_modvat_recovery ; --'Modvat Recovery';
1218
1219 --added by GD
1220 v_start_number NUMBER;
1221 v_end_number NUMBER;
1222 v_jump_by NUMBER;
1223 v_order_invoice_type VARCHAR2(50);
1224 v_prefix VARCHAR2(50);
1225 v_meaning VARCHAR2(80);
1226 v_set_of_books_id NUMBER; -- := pr_new.set_of_books_id; --Ramananda for File.Sql.35
1227 /* Bug 5243532. Added by Lakshmi Gopalsami
1228 Removed the reference to set_of_books_cur
1229 which is selecting SOB from org_organization_definitions
1230 as the SOB will never by null in base table.
1231 */
1232 /* CODE ADDED TO INCORPORATE MASTER ORGANIZATION */
1233 CURSOR ec_code_cur(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
1234 SELECT A.Organization_Id, A.Location_Id
1235 FROM JAI_CMN_INVENTORY_ORGS A
1236 WHERE A.Ec_Code IN (SELECT B.Ec_Code
1237 FROM JAI_CMN_INVENTORY_ORGS B
1238 WHERE B.Organization_Id = p_organization_id
1239 AND B.Location_Id = p_location_id);
1240
1241 --3661746
1242 CURSOR c_total_Excise_amt IS
1243 SELECT nvl(sum(jrtl.func_tax_amount),0)
1244 FROM JAI_AR_TRXS jtrx,
1245 JAI_AR_TRX_LINES jtl,
1246 JAI_AR_TRX_TAX_LINES jrtl,
1247 JAI_CMN_TAXES_ALL jtc ,
1248 JAI_INV_ITM_SETUPS jmtl
1249 WHERE jrtl.tax_id = jtc.tax_id
1250 AND jtrx.customer_trx_id = jtl.customer_Trx_id
1251 AND jrtl.link_to_cust_trx_line_id = jtl.customer_trx_line_id
1252 AND jtl.inventory_item_id = jmtl.inventory_item_id
1253 AND jtrx.organization_id = jmtl.organization_id
1254 --AND jmtl.item_class in ('RMIN','RMEX','CGEX','CGIN','FGIN','FGEX','CCIN','CCEX') /* Modified by Ramananda for removal of SQL LITERALs */
1255 AND jmtl.item_class IN ( jai_constants.item_class_rmin, jai_constants.item_class_rmex,
1256 jai_constants.item_class_cgex, jai_constants.item_class_cgin,
1257 jai_constants.item_class_ccex, jai_constants.item_class_ccin,
1258 jai_constants.item_class_fgin, jai_constants.item_class_fgex
1259 )
1260 AND jtc.tax_type like '%Excise%'
1261 AND jtl.customer_trx_id = pr_new.customer_trx_id
1262 AND jtrx.customer_trx_id = pr_new.customer_trx_id;
1263
1264 v_total_excise_amt NUMBER :=0;
1265
1266 CURSOR c_cess_amount is
1267 SELECT NVL(SUM(jrctl.func_tax_amount),0) tax_amount
1268 FROM JAI_AR_TRX_TAX_LINES jrctl ,
1269 JAI_CMN_TAXES_ALL jtc
1270 WHERE jtc.tax_id = jrctl.tax_id
1271 AND link_to_cust_trx_line_id IN
1272 (SELECT customer_trx_line_id
1273 FROM JAI_AR_TRX_LINES
1274 WHERE customer_trx_id = pr_new.customer_trx_id
1275 )
1276 AND upper(jtc.tax_type) IN (upper(jai_constants.tax_type_cvd_edu_cess), upper(jai_constants.tax_type_exc_edu_cess));
1277
1278 -- Start of bug 4185033
1279 /*
1280 || Cursor added by aiyer for the bug 4185033
1281 || Check whether the JAI_AR_TRX_INS_LINES_T table still has the row corresponding to the current
1282 || customer_trx_id
1283 */
1284 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 */
1285 IS
1286 /* commented and added the below for bug#11664009 and added the below
1287 SELECT
1288 1
1289 FROM
1290 JAI_AR_TRX_INS_LINES_T
1291 WHERE
1292 customer_trx_id = cp_customer_trx_id ;
1293 */
1294 SELECT '1'
1295 FROM Fnd_Concurrent_Requests FCR
1296 WHERE FCR.argument1 = to_char(cp_customer_trx_id)
1297 and fcr.status_code<> 'C'
1298 and fcr.phase_code<>'C'
1299 AND fcr.request_id IN (Select nvl(Max(Request_ID),-1)
1300 From Fnd_Concurrent_Programs FCP,
1301 Fnd_Application FA ,
1302 Fnd_Concurrent_Requests FCR
1303 Where FCR.Program_Application_ID = FA.Application_ID
1304 AND FCR.Concurrent_Program_ID = FCP.Concurrent_Program_ID
1305 AND FA.Application_ID = FCP.Application_ID
1306 AND Concurrent_Program_Name = 'JAILINEGL'
1307 AND FA.Application_Short_Name = 'JA'
1308 AND FCR.argument1 = To_Char(cp_customer_trx_id) );
1309
1310 -- End of bug 4185033
1311
1312 CURSOR c_vat_invoice_cur
1313 IS
1314 SELECT
1315 vat_invoice_no
1316 FROM JAI_AR_TRXS
1317 WHERE customer_trx_id = pr_new.customer_trx_id;
1318
1319 CURSOR cur_vat_taxes_exist
1320 IS
1321 SELECT
1322 regime_id ,
1323 regime_code
1324 FROM
1325 JAI_AR_TRX_TAX_LINES jcttl,
1326 JAI_AR_TRX_LINES jctl,
1327 JAI_CMN_TAXES_ALL jtc ,
1328 jai_regime_tax_types_v jrttv
1329 WHERE
1330 jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
1331 jctl.customer_trx_id = pr_new.customer_trx_id AND
1332 jcttl.tax_id = jtc.tax_id AND
1333 jtc.tax_type = jrttv.tax_type AND
1334 regime_code = jai_constants.vat_regime AND
1335 jtc.org_id = pr_new.org_id ;
1336
1337 /*
1338 || Added by kunkumar for bug#5645003
1339 || Check if only 'VAT REVERSAL' tax type is present in ja_in_ra_cust_trx_tax_lines
1340 */
1341 CURSOR c_chk_vat_reversal (cp_tax_type jai_cmn_taxes_all.tax_type%TYPE )
1342 IS
1343 SELECT
1344 1
1345 FROM
1346 JAI_AR_TRX_TAX_LINES jcttl,
1347 JAI_AR_TRX_LINES jctl,
1348 JAI_CMN_TAXES_ALL jtc
1349 WHERE
1350 jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
1351 jctl.customer_trx_id = pr_new.customer_trx_id AND
1352 jcttl.tax_id = jtc.tax_id AND
1353 jtc.org_id = pr_new.org_id AND
1354 jtc.tax_type = cp_tax_type ;
1355
1356 /*
1357 || Retrieve the regime_id which is of regime code 'VAT'
1358 */
1359 CURSOR c_get_regime_id
1360 IS
1361 SELECT
1362 regime_id
1363 FROM
1364 jai_regime_tax_types_v
1365 WHERE
1366 regime_code = jai_constants.vat_regime
1367 AND rownum = 1 ;
1368
1369 ln_vat_reversal_exists NUMBER ;
1370 lv_vat_reversal VARCHAR2(100);
1371 --bug#5645003, ends
1372
1373
1374
1375 CURSOR cur_get_same_inv_no ( cp_organization_id JAI_AR_TRXS.ORGANIZATION_ID%TYPE ,
1376 cp_location_id JAI_AR_TRXS.LOCATION_ID%TYPE
1377 )
1378 IS
1379 SELECT
1380 nvl(attribute_value ,'N') attribute_value
1381 FROM
1382 JAI_RGM_ORG_REGNS_V
1383 WHERE
1384 regime_code = jai_constants.vat_regime AND
1385 attribute_type_code = jai_constants.regn_type_others AND /*'OTHERS' AND */
1386 attribute_code = jai_constants.attr_code_same_inv_no AND /*'SAME_INVOICE_NO' AND */
1387 organization_id = cp_organization_id AND
1388 location_id = cp_location_id;
1389
1390 CURSOR cur_get_exc_inv_no
1391 IS
1392 SELECT
1393 excise_invoice_no
1394 FROM
1395 JAI_AR_TRX_LINES
1396 WHERE
1397 customer_trx_id = pr_new.customer_trx_id ;
1398
1399
1400 CURSOR cur_get_gl_date(cp_account_class ra_cust_trx_line_gl_dist_all.account_class%type)
1401 IS
1402 SELECT
1403 gl_date
1404 FROM
1405 ra_cust_trx_line_gl_dist_all
1406 WHERE
1407 customer_trx_id = pr_new.customer_trx_id AND
1408 account_class = cp_account_class AND /* 'REC' AND -- Ramananda for removal of SQL LITERALs */
1409 latest_rec_flag = 'Y';
1410
1411
1412
1413 ln_exists NUMBER ;
1414 ln_cess_amount JAI_CMN_RG_OTHERS.DEBIT%TYPE;
1415 lv_process_flag VARCHAR2(2);
1416 lv_process_message VARCHAR2(1996);
1417 lv_register_type VARCHAR2(5);
1418 lv_rg23a_cess_avlbl VARCHAR2(10);
1419 lv_rg23c_cess_avlbl VARCHAR2(10);
1420 lv_pla_cess_avlbl VARCHAR2(10);
1421 lv_vat_invoice_number JAI_AR_TRXS.VAT_INVOICE_NO%TYPE;
1422 lv_vat_taxes_exist VARCHAR2(1);
1423 lv_vat_no_same_exc_no JAI_RGM_REGISTRATIONS.ATTRIBUTE_VALUE%TYPE; -- := 'N'; --Ramananda for File.Sql.35
1424 ld_gl_date RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE%TYPE;
1425 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE;
1426 ln_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE;
1427
1428
1429 --3661746
1430
1431 lv_doc_type_class varchar2(2); /* csahoo for seperate vat invoice num for unreg dealers - bug# 5233925*/
1432 lv_enable_gst_flag VARCHAR2(1);
1433 --Added by Bo Li for bug#10043656 Begin
1434 ------------------------------------------------------------
1435 /*
1436 lv_gst_invoice_no JAI_AR_TRX_LINES.gst_invoice_no%TYPE;
1437
1438 ln_cgst_regime_id NUMBER;
1439 ln_sgst_regime_id NUMBER;
1440 lv_cgst_regime_code JAI_RGM_ORG_REGNS_V.REGIME_CODE%TYPE;
1441 lv_sgst_regime_code JAI_RGM_ORG_REGNS_V.REGIME_CODE%TYPE;
1442
1443 CURSOR cur_get_gst_invoice
1444 IS
1445 SELECT gst_invoice_no
1446 FROM JAI_AR_TRXS
1447 WHERE customer_trx_id = pr_new.customer_trx_id ;
1448
1449 CURSOR cur_chk_gst_exists ( cp_regime_code JAI_RGM_ORG_REGNS_V.REGIME_CODE%TYPE)
1450 IS
1451 SELECT regime_id ,
1452 regime_code
1453 FROM
1454 JAI_AR_TRX_TAX_LINES jcttl,
1455 JAI_AR_TRX_LINES jctl,
1456 JAI_CMN_TAXES_ALL jtc ,
1457 jai_regime_tax_types_v jrttv
1458 WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
1459 AND jctl.customer_trx_id = pr_new.customer_trx_id
1460 AND jcttl.tax_id = jtc.tax_id
1461 AND jtc.tax_type = jrttv.tax_type
1462 AND regime_code = cp_regime_code
1463 AND jtc.org_id = pr_new.org_id ;
1464
1465 CURSOR cur_get_gst_regime_info (cp_organization_id JAI_RGM_ORG_REGNS_V.ORGANIZATION_ID%TYPE ,
1466 cp_location_id JAI_RGM_ORG_REGNS_V.LOCATION_ID%TYPE,
1467 cp_regime_code JAI_RGM_ORG_REGNS_V.REGIME_CODE%TYPE
1468 )
1469 IS
1470 SELECT regime_id,
1471 attribute_value
1472 FROM JAI_RGM_ORG_REGNS_V orrg
1473 WHERE orrg.organization_id = cp_organization_id
1474 AND orrg.location_id = cp_location_id
1475 AND attribute_type_code = jai_constants.rgm_attr_type_code_primary
1476 AND attribute_code = jai_constants.attr_code_regn_no
1477 AND regime_code = cp_regime_code;
1478 --------------------------------------------------------------------------------------
1479 --Added by Bo Li for bug#10043656 End
1480 */
1481 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
1482 ------------------------------------------------------------------------------
1483 CURSOR get_external_flag_cur IS
1484 SELECT COUNT(lines.customer_trx_id)
1485 FROM jai_ar_trx_lines lines,
1486 jai_interface_lines_all intfs
1487 WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
1488 AND lines.customer_trx_id = pr_new.customer_trx_id
1489 AND lines.interface_flag = 'Y'
1490 AND intfs.taxable_event = 'EXTERNAL';
1491
1492 CURSOR get_open_source_cur IS
1493 SELECT COUNT(interface_flag)
1494 FROM jai_ar_trx_lines
1495 WHERE customer_trx_id = pr_new.customer_trx_id
1496 AND interface_flag = 'Y';
1497
1498 CURSOR get_vat_invoice_no_cur IS
1499 SELECT DISTINCT vat_invoice_no
1500 FROM jai_interface_lines_all
1501 WHERE internal_trx_id = pr_new.customer_trx_id;
1502
1503
1504 ln_open_source NUMBER;
1505 ln_external_flag NUMBER;
1506 ------------------------------------------------------------------------------
1507 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
1508
1509 /*Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952 Begin*/
1510 CURSOR c_st_invoice_cur
1511 IS
1512 SELECT
1513 st_inv_number
1514 FROM JAI_AR_TRXS
1515 WHERE customer_trx_id = pr_new.customer_trx_id;
1516
1517 CURSOR cur_st_taxes_exist
1518 IS
1519 SELECT
1520 regime_id ,
1521 regime_code
1522 FROM
1523 JAI_AR_TRX_TAX_LINES jcttl,
1524 JAI_AR_TRX_LINES jctl,
1525 JAI_CMN_TAXES_ALL jtc ,
1526 jai_regime_tax_types_v jrttv
1527 WHERE
1528 jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
1529 jctl.customer_trx_id = pr_new.customer_trx_id AND
1530 jcttl.tax_id = jtc.tax_id AND
1531 jtc.tax_type = jrttv.tax_type AND
1532 regime_code = jai_constants.service_regime AND
1533 jtc.org_id = pr_new.org_id ;
1534 lv_st_inv_number jai_ar_trxs.st_inv_number%TYPE;
1535 /*Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952 End*/
1536 /*Added by Qinglei on 26-Dec-2011 for bug13531549 begin*/
1537 CURSOR cur_excise_taxes_exist(pn_customer_trx_line_id NUMBER)
1538 IS
1539 SELECT
1540 'Y'
1541 FROM dual
1542 WHERE EXISTS
1543 (SELECT 1
1544 FROM JAI_AR_TRX_TAX_LINES jcttl,
1545 JAI_AR_TRX_LINES jctl,
1546 JAI_CMN_TAXES_ALL jtc
1547 WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
1548 AND jctl.customer_trx_id = pr_new.customer_trx_id
1549 AND jcttl.tax_id = jtc.tax_id
1550 AND upper(jtc.tax_type) IN
1551 ('EXCISE', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS')
1552 AND jtc.org_id = pr_new.org_id
1553 AND jctl.customer_trx_line_id = pn_customer_trx_line_id
1554 );
1555 lv_excise_tax_flag VARCHAR2(2);
1556 /*Added by Qinglei on 26-Dec-2011 for bug13531549 end*/
1557 /* CODE ADDED TILL TO INCORPORATE MASTER ORGANIZATION */
1558 BEGIN
1559 pv_return_code := jai_constants.successful ;
1560 /*------------------------------------------------------------------------------------------
1561 FILENAME: JA_IN_LOC_AR_HDR_UPDATE_TRG.sql
1562 CHANGE HISTORY:
1563 S.No Date Author and Details
1564 1. 10-Aug-2005 Aiyer bug 4545146 version 120.1
1565 Issue:-
1566 Deadlock on tables due to multiple triggers on the same table (in different sql files)
1567 firing in the same phase.
1568 Fix:-
1569 Multiple triggers on the same table have been merged into a single file to resolve
1570 the problem
1571 The following files have been stubbed:-
1572 jai_ar_rcta_t1.sql
1573 jai_ar_rcta_t2.sql
1574 jai_ar_rcta_t3.sql
1575 jai_ar_rcta_t4.sql
1576 jai_ar_rcta_t6.sql
1577 jai_ar_rcta_t7.sql
1578 jai_ar_rcta_t8.sql
1579 jai_ar_rcta_t9.sql
1580 Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers in the above files
1581 2 09-Mar-2007 ssawant for the bug#5040383, File version 120.6
1582 Forward porting the changes done in bug#4998378
1583 bduvarag for the bug#5171573, File version 120.6
1584 Forward porting the changes done in bug#5057544
1585 3 17/05/2007 bduvarag for the bug#4601570, File version 120.14
1586 Forward porting the changes done in bug#4474270
1587 4 31-dec-2011 qinglei for bug#13537078
1588
1589 Future Dependencies For the release Of this Object:-
1590 (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/
1591 A datamodel change )
1592
1593 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
1594 Current Version Current Bug Dependent Files Version Author Date Remarks
1595 Of File On Bug/Patchset Dependent On
1596 ja_in_loc_ar_hdr_update_trg.sql
1597 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
1598
1599 ----------------------------------------------------------------------------------------------------------------------------------------------------
1600
1601 --------------------------------------------------------------------------------------------*/
1602 -----Once Complete Button is Pressed Following code tell you what will happen at what stage
1603
1604 /* --Ramananda for File.Sql.35, start */
1605 v_complete_flag := 'N';
1606 v_rg_flag := 'N';
1607 v_update_rg_flag := 'N';
1608 v_item_class := 'N';
1609 v_parent_trx_number := pr_new.recurred_from_trx_number;
1610 v_customer_trx_id := pr_old.customer_trx_id;
1611 v_last_update_date := pr_new.last_update_date;
1612 v_last_updated_by := pr_new.last_updated_by;
1613 v_creation_date := pr_new.creation_date;
1614 v_created_by := pr_new.created_by;
1615 v_last_update_login := pr_new.last_update_login;
1616 v_set_of_books_id := pr_new.set_of_books_id;
1617 lv_vat_no_same_exc_no := 'N';
1618 /* --Ramananda for File.Sql.35, end */
1619
1620 -- Start of bug 4185033
1621 /*
1622 || This code has been added by aiyer for the bug 4185033
1623 || Stop the processing before if the user tries to complete the Manual AR invoice before the Ar TAx and Fregiht DEfaultation is complete.
1624 || 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
1625 || ar_payment_schedule_all
1626 */
1627
1628 IF pr_new.created_from = 'ARXTWMAI'
1629 OR pr_new.created_from = 'RAXTRX' --Added 'OR' condition by Zhiwei for Open Interface ER bug#11683927
1630 THEN
1631 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, begin
1632 --------------------------------------------------------
1633 OPEN get_open_source_cur;
1634 FETCH get_open_source_cur INTO ln_open_source;
1635 CLOSE get_open_source_cur;
1636
1637 IF(pr_new.created_from = 'ARXTWMAI'
1638 --Added 'OR' condition by Zhiwei for Open Interface ER bug#11683927
1639 OR (pr_new.created_from = 'RAXTRX' AND ln_open_source > 0))
1640 THEN
1641 ---------------------------------------------------------
1642 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, end
1643
1644 OPEN cur_chk_temp_lines_exist( cp_customer_trx_id => v_customer_trx_id );
1645 FETCH cur_chk_temp_lines_exist INTO ln_exists;
1646 IF CUR_CHK_TEMP_LINES_EXIST%FOUND THEN
1647 CLOSE cur_chk_temp_lines_exist;
1648 /* raise_application_error(-20121,'IL Tax not applied - Please wait for AR Tax and Freight Defaultation Concurrent Request to complete');
1649 */ 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 ;
1650 END IF ;
1651 CLOSE cur_chk_temp_lines_exist;
1652 END IF;-- Added by Zhiwei for Open Interface ER bug#11683927.
1653 END IF;
1654 -- End of bug 4185033
1655 --Added the below for Bug 5040383
1656
1657 OPEN transaction_type_cur;
1658 FETCH transaction_type_cur INTO v_trans_type;
1659 CLOSE transaction_type_cur;
1660
1661 --Added the below for Bug 5040383
1662 OPEN Complete_Cur;
1663 FETCH Complete_Cur INTO v_org_id, v_loc_id,v_complete_flag,v_reg_type, v_update_rg_flag,v_update_rg23d_flag;
1664 CLOSE Complete_Cur;
1665
1666 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
1667 ---------------------------------------------------------------------------
1668 OPEN get_external_flag_cur;
1669 FETCH get_external_flag_cur INTO ln_external_flag;
1670 CLOSE get_external_flag_cur;
1671
1672 --For AR transaction for 'OFI TAX IMPORT', manually set complete flag as 'N',
1673 --so the logic can proceed for OFI Open Interface AR as manual AR does.
1674 IF ln_open_source > 0 THEN
1675 v_complete_flag := 'N';
1676 END IF;
1677 ---------------------------------------------------------------------
1678 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
1679
1680 IF pr_new.COMPLETE_FLAG <> pr_old.COMPLETE_FLAG
1681 OR ln_open_source > 0 --Added by Xiao for Open Interface ER bug#11683927.
1682 THEN
1683
1684 /* --commented for bug 5040383
1685 OPEN Complete_Cur;
1686 FETCH Complete_Cur INTO v_org_id, v_loc_id,v_complete_flag,v_reg_type, v_update_rg_flag;
1687 CLOSE Complete_Cur;
1688 */
1689
1690 v_rg_flag := v_update_rg_flag;
1691
1692 IF NVL(v_complete_flag,'N') = 'Y' THEN
1693 RETURN;
1694 END IF;
1695 /*
1696 --commented for bug 5040383
1697 OPEN transaction_type_cur;
1698 FETCH transaction_type_cur INTO v_trans_type;
1699 CLOSE transaction_type_cur;
1700 */
1701
1702 IF NVL(v_trans_type,'N') <> 'INV' THEN
1703 /*Bug 5171573 bduvarag start*/
1704 UPDATE JAI_AR_TRXS
1705 SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
1706 WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
1707 /*Bug 5171573 bduvarag End*/
1708 RETURN;
1709 END IF;
1710
1711 IF pr_new.created_from = 'RAXTRX'
1712 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011.
1713 --Add 'AND' condition, program will proceed while AR transaction is not from OFI Open Interface.
1714 AND ln_open_source = 0
1715 THEN
1716 UPDATE JAI_AR_TRXS
1717 SET ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
1718 WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
1719 ELSE
1720
1721 IF NVL(v_org_id, 999999) = 999999 THEN -- ssumaith --- changed 0 to 999999 because trigger was returning in case where
1722 -- setup business group is done. Bug # 2846277
1723 IF v_parent_trx_number IS NULL THEN
1724 RETURN;
1725 ELSE
1726 OPEN organization_cur;
1727 FETCH organization_cur INTO v_org_id, v_loc_id;
1728 CLOSE organization_cur;
1729 v_rg_flag := 'Y';
1730 END IF;
1731 END IF;
1732 IF NVL(v_org_id, 999999) = 999999 THEN -- ssumaith - -- changed 0 to 999999 because trigger was returning in case where
1733 -- setup business group is done. Bug # 2846277
1734 RETURN;
1735 END IF;
1736
1737 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, begin
1738 ----------------------------------------------------------------------------------
1739 IF( pr_new.created_from = 'RAXTRX' AND ln_open_source > 0 )
1740 THEN
1741 jai_cmn_bond_register_pkg.GET_REGISTER_ID (v_org_id,
1742 v_loc_id,
1743 -1, --Same as Manual AR
1744 'N',
1745 v_register_id ,
1746 v_reg_code
1747 );
1748 ELSE
1749 ----------------------------------------------------------------------------------
1750 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, end
1751
1752 -- above code segment commented by sriram - calling the procedure instead -- bug # 3021588
1753 jai_cmn_bond_register_pkg.GET_REGISTER_ID (v_org_id,
1754 v_loc_id,
1755 NVL(pr_new.batch_source_id,0),
1756 'N',
1757 v_register_id ,
1758 v_reg_code
1759 );
1760 END IF;--Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011.
1761 -- ends here code added by sriram - Bug # 3021588
1762
1763 OPEN register_code_meaning_cur(v_reg_code, 'JAI_REGISTER_TYPE'); /* Modified by Ramananda for removal of SQL LITERALs */
1764 FETCH register_code_meaning_cur INTO v_meaning;
1765 CLOSE register_code_meaning_cur;
1766 OPEN fin_year_cur(v_org_id);
1767 FETCH fin_year_cur INTO v_fin_year;
1768 CLOSE fin_year_cur;
1769 OPEN Batch_Source_Name_Cur;
1770 FETCH Batch_Source_Name_Cur INTO v_order_invoice_type;
1771 CLOSE Batch_Source_Name_Cur;
1772
1773
1774
1775
1776 IF v_reg_code IN ('DOMESTIC_EXCISE','EXPORT_EXCISE','DOM_WITHOUT_EXCISE','BOND_REG') THEN
1777 v_rg_flag := 'Y';
1778 -- following comparision values made into upper case by sriram -bug # 3179379
1779 ELSIF upper(v_reg_code) IN ('23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE',
1780 '23D_DOM_WITHOUT_EXCISE','23D_EXPORT_WITHOUT_EXCISE')THEN
1781 v_rg_flag := 'N';
1782 END IF;
1783
1784 v_update_rg_flag := 'Y';-- bug#3496577 -- setting the value to 'Y' because the update_rg_flag has to only impact
1785 -- amount registers and not quantity registers and excise invoice generation.
1786
1787 OPEN REG_BALANCE_CUR(v_org_id, v_loc_id);
1788 FETCH REG_BALANCE_CUR INTO v_rg23a_bal,v_rg23c_bal,v_pla_bal;
1789 CLOSE REG_BALANCE_CUR;
1790 OPEN ssi_unit_flag_cur(v_org_id, v_loc_id);
1791 FETCH ssi_unit_flag_cur INTO v_ssi_unit_flag, v_trading_flag;/*Bug#4601570 bduvarag*/
1792 CLOSE ssi_unit_flag_cur;
1793
1794 /*
1795 ||Start of bug 4101549
1796 || IF condition modified forthe bug 4101549
1797 ||The complete flag statuses should be 'A','P','C','N'
1798 */
1799 IF NVL(v_complete_flag,'N') IN ('N','A','C','P') AND
1800 (v_rg_flag = 'Y' OR v_update_rg_flag = 'Y') AND
1801 v_reg_code IS NOT NULL
1802 THEN
1803 /*
1804 ||End of bug 4101549
1805 */
1806 FOR Line_Rec IN Line_Cur LOOP
1807 FOR excise_cal_rec IN excise_cal_cur(Line_Rec.customer_trx_line_id, Line_Rec.Inventory_Item_ID, v_org_id) LOOP
1808 IF excise_cal_rec.t_type IN ('Excise') THEN
1809 v_basic_ed := NVL(v_basic_ed,0) + NVL(excise_cal_rec.func_amt,0);
1810 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
1811 v_additional_ed := NVL(v_additional_ed,0) + NVL(excise_cal_rec.func_amt,0);
1812 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
1813 v_other_ed := NVL(v_other_ed,0) + NVL(excise_cal_rec.func_amt,0);
1814 END IF;
1815 END LOOP;
1816 v_tax_amount := NVL(v_basic_ed,0) + NVL(v_additional_ed,0) + NVL(v_other_ed,0);
1817 IF v_reg_code IN ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
1818 OPEN item_class_cur(V_ORG_ID,Line_Rec.Inventory_Item_Id);
1819 FETCH item_class_cur INTO v_item_class, v_excise_flag;
1820 CLOSE item_class_cur;
1821
1822
1823 IF NVL(v_excise_flag,'N') = 'Y' THEN
1824 IF NVL(v_ssi_unit_flag,'N') = 'N'
1825 AND NVL(line_rec.excise_exempt_type, '@@@') NOT IN ('CT2', 'EXCISE_EXEMPT_CERT','CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH' )
1826 THEN
1827 IF v_item_class IN ('CGEX','CGIN') THEN
1828 v_rg23c_tax_amount := NVL(v_rg23c_tax_amount,0) + NVL(v_tax_amount,0);
1829 ELSIF v_item_class IN ('RMIN','RMEX') THEN
1830 v_rg23a_tax_amount := NVL(v_rg23a_tax_amount,0) + NVL(v_tax_amount,0);
1831 ELSIF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') THEN
1832 v_other_tax_amount := NVL(v_other_tax_amount,0) + NVL(v_tax_amount,0);
1833 -------------ADDED BY GD {
1834 ELSIF NVL(v_ssi_unit_flag,'N') = 'N' AND
1835 NVL(line_rec.excise_exempt_type, '@@@') IN ('CT2', 'EXCISE_EXEMPT_CERT',
1836 'CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH' ) THEN
1837 IF v_item_class NOT IN ('OTIN', 'OTEX') THEN
1838 IF line_rec.excise_exempt_type IN ('CT2 - OTHERS', 'Excise Exempted OTHERS' ) THEN
1839 OPEN for_modvat_tax_rate(line_rec.customer_trx_line_id);
1840 FETCH for_modvat_tax_rate INTO v_modvat_tax_rate;
1841 CLOSE for_modvat_tax_rate;
1842 ELSE
1843 OPEN for_modvat_percentage(v_org_id, v_loc_id);
1844 FETCH for_modvat_percentage INTO v_modvat_tax_rate;
1845 CLOSE for_modvat_percentage;
1846 END IF;
1847 v_exempt_bal := (NVL(v_exempt_bal, 0) + line_rec.quantity * line_rec.assessable_value * NVL(v_modvat_tax_rate,0))/100;
1848 IF v_exempt_bal > v_rg23a_bal THEN
1849 /* RAISE_APPLICATION_ERROR(-20120, 'Register RG23A PART II Balance -> '||
1850 TO_CHAR(v_rg23a_bal ) || ' IS less than the Modvat Amount ->' ||
1851 TO_CHAR(v_exempt_bal)); */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Register RG23A PART II Balance -> '||
1852 TO_CHAR(v_rg23a_bal ) || ' IS less than the Modvat Amount ->' ||
1853 TO_CHAR(v_exempt_bal) ; return ;
1854 END IF;
1855 END IF;
1856 -----------ADDED BY GD }
1857 END IF;
1858 END IF; -- SSI UNIT FLAG
1859 END IF; -- EXCISE INVOICE FLAG
1860 ELSIF v_reg_code IN ('BOND_REG')
1861 THEN
1862 -- added by sriram - bug # 3021588
1863 jai_cmn_bond_register_pkg.GET_REGISTER_DETAILS(v_register_id,
1864 v_register_balance,
1865 v_register_exp_date,
1866 v_lou_flag
1867 );
1868
1869 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (pr_new.set_of_books_id ,pr_new.invoice_currency_code ,
1870 pr_new.exchange_date ,pr_new.exchange_rate_type, pr_new.exchange_rate);
1871 v_bond_tax_amount := NVL(v_tax_amount,0) + NVL(v_bond_tax_amount,0);
1872
1873
1874 IF (v_register_balance < v_bond_tax_amount )
1875 AND -- added by sriram - bug # 3021588
1876 ( NVL(v_lou_flag,'N') = 'N') -- added by sriram - bug # 3021588
1877 THEN
1878 /* RAISE_APPLICATION_ERROR(-20120, 'Bonded Register Has Balance -> ' || TO_CHAR(v_register_balance)
1879 || ' ,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)
1880 || ' ,which IS less than Excisable Amount -> ' || TO_CHAR(v_bond_tax_amount) ; return ;
1881 END IF;
1882
1883 IF (nvl(v_register_exp_date,sysdate) < Sysdate ) THEN
1884 /* RAISE_APPLICATION_ERROR(-20121, 'Validity Date of the Bond Register has expired');
1885 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Validity Date of the Bond Register has expired' ; return ;
1886 END IF ;
1887 ELSIF v_reg_code IN ('23D_DOMESTIC_EXCISE','23D_EXPORT_EXCISE','23D_DOM_WITHOUT_EXCISE','23D_EXPORT_WITHOUT_EXCISE')
1888 THEN /*Bug#4601570 bduvarag start*/
1889 IF v_trading_flag = 'Y' AND
1890 v_update_rg23d_flag = 'Y'
1891 THEN
1892 /*Bug#4601570 bduvarag end*/
1893 if line_rec.inventory_item_id is not null then
1894 OPEN matched_qty_cur(line_rec.customer_trx_line_id);
1895 FETCH matched_qty_cur INTO v_matched_qty;
1896 CLOSE matched_qty_cur;
1897 IF NVL(v_matched_qty,0)<> NVL(line_rec.quantity,0)
1898 THEN
1899 /* RAISE_APPLICATION_ERROR(-20120, 'Matched Quantity -> ' || TO_CHAR(v_matched_qty)
1900 || ' , IS less than Invoiced Quantity -> ' || TO_CHAR(line_rec.quantity)
1901 || ' , 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)
1902 || ' , IS less than Invoiced Quantity -> ' || TO_CHAR(line_rec.quantity)
1903 || ' ,FOR line NUMBER -> ' || TO_CHAR(line_rec.line_number) ; return ;
1904 EXIT;
1905 END IF;
1906 END IF;
1907
1908 -- needs to start here
1909
1910 -- needs to end here
1911 IF v_reg_code = '23D_EXPORT_WITHOUT_EXCISE'
1912 THEN
1913 v_rg23d_tax_amount := NVL(v_tax_amount,0) + NVL(v_rg23d_tax_amount,0);
1914 IF NVL(v_rg23d_register_balance,0) < NVL(v_rg23d_tax_amount,0)
1915 and (NVL(v_lou_flag,'N') = 'N') -- added by sriram bug # 3021588
1916 THEN
1917 /* RAISE_APPLICATION_ERROR(-20120, 'RG23D Bonded Register Has Balance -> ' || TO_CHAR(v_rg23d_register_balance)
1918 || ' ,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)
1919 || ' ,which IS less than Excisable Amount -> ' || TO_CHAR(v_rg23d_tax_amount) ; return ;
1920 END IF;
1921
1922 -- added by sriram - bug # 3021588
1923 IF (v_register_exp_date > Sysdate ) THEN
1924 /* RAISE_APPLICATION_ERROR(-20121, 'Validity Date of the Bond Register has expired');
1925 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Validity Date of the Bond Register has expired' ; return ;
1926 -- ends here additions by sriram - bug # 3021588
1927 END IF;
1928 END IF;
1929 END IF;/*Bug#4601570 bduvarag*/
1930 END IF;
1931 END LOOP;
1932 v_basic_Ed := 0;
1933 v_additional_ed := 0;
1934 v_other_ed := 0;
1935 v_tax_amount := 0;
1936 v_other_tax_amount := 0;
1937 v_rg23a_tax_amount := 0;
1938 v_rg23c_tax_amount := 0;
1939 v_rg23d_tax_Amount := 0;
1940
1941 --Add nvl by Xiao for bug#11936390 on 7-Apr-2011.
1942 IF nvl(ln_external_flag, 0) = 0 THEN --Added by Xiao Lv for Open Interface ER bug#11683927 on 03-Mar-2011
1943
1944 ------------------------------start of update loop------------------------
1945
1946 FOR Line_Rec IN Line_Cur LOOP
1947 -- Excise invoice generation logic commented by sriram and
1948 -- making call to the procedure instead.
1949 -- Bug # 2663211
1950
1951 Open item_class_cur(v_org_id,line_rec.Inventory_item_id);
1952 fetch item_class_cur into v_item_class , v_excise_flag;
1953 close item_class_cur;
1954
1955 OPEN cur_excise_taxes_exist(line_rec.customer_trx_line_id);
1956 FETCH cur_excise_taxes_exist INTO lv_excise_tax_flag;
1957 CLOSE cur_excise_taxes_exist;
1958 /*Added by Qinglei on 26-Dec-2011 for bug13531549 */
1959
1960 IF NVL(v_excise_flag,'N') = 'Y' AND nvl(lv_excise_tax_flag,'N') = 'Y' THEN
1961 /*Added by Qinglei on 26-Dec-2011 for bug13531549 */
1962 IF v_invoice_no is Null THEN
1963 --Added IF condition by Zhiwei for Open Interface ER bug#11683927, begin
1964 --------------------------------------------------------------------------
1965 IF pr_new.created_from = 'RAXTRX' and NVL(ln_open_source,0)>0 THEN
1966 jai_cmn_setup_pkg.generate_excise_invoice_no(v_org_id,v_loc_id,'I',-1, v_fin_year, v_invoice_no , v_errbuf);
1967 --------------------------------------------------------------------------
1968 --Added IF condition by Zhiwei for Open Interface ER bug#11683927, end
1969 else
1970 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);
1971 end if;
1972 END IF;
1973
1974 IF v_errbuf is not null THEN
1975 -- to raise an error when the excise invoice returns a value.
1976 /* raise_application_error(-20107,'Error During Excise Invoice Generation ! ' || v_errbuf);
1977 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'Error During Excise Invoice Generation ! ' || v_errbuf ; return ;
1978 END IF;
1979
1980 IF NVL(v_item_class,'~') not in ('OTIN') THEN
1981
1982 UPDATE JAI_AR_TRX_LINES
1983 SET EXCISE_INVOICE_NO = v_invoice_no ,
1984 EXCISE_INVOICE_DATE = SYSDATE
1985 WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
1986 INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
1987 CUSTOMER_TRX_ID = v_customer_trx_id;
1988 END IF;
1989 END IF;
1990 END LOOP;
1991 ----------------end of excise no update loop--------------------
1992 END IF; --Added by Xiao Lv for Open Interface ER bug#11683927 on 03-Mar-2011
1993
1994 --3661746
1995 open c_total_Excise_amt;
1996 fetch c_total_Excise_amt into v_total_excise_amt;
1997 close c_total_Excise_amt;
1998 --3661746
1999
2000 /* start additions by ssumaith to check for negative cess - bug#4171272*/
2001
2002 open c_cess_amount;
2003 fetch c_cess_amount into ln_Cess_amount;
2004 close c_cess_amount;
2005
2006 lv_register_type := 'RG23A';
2007 jai_cmn_rg_others_pkg.check_balances(
2008 p_organization_id => v_org_id ,
2009 p_location_id => v_loc_id ,
2010 p_register_type => lv_register_type ,
2011 p_trx_amount => ln_cess_amount ,
2012 p_process_flag => lv_process_flag ,
2013 p_process_message => lv_process_message
2014 );
2015
2016 if lv_process_flag <> jai_constants.successful then
2017 lv_rg23a_cess_avlbl := 'FALSE';
2018 else
2019 lv_rg23a_cess_avlbl := 'TRUE';
2020 end if;
2021
2022
2023 lv_register_type := 'RG23C';
2024 jai_cmn_rg_others_pkg.check_balances(
2025 p_organization_id => v_org_id ,
2026 p_location_id => v_loc_id ,
2027 p_register_type => lv_register_type ,
2028 p_trx_amount => ln_cess_amount ,
2029 p_process_flag => lv_process_flag ,
2030 p_process_message => lv_process_message
2031 );
2032
2033 if lv_process_flag <> jai_constants.successful then
2034 lv_rg23c_cess_avlbl := 'FALSE';
2035 else
2036 lv_rg23c_cess_avlbl := 'TRUE';
2037 end if;
2038
2039
2040 lv_register_type := 'PLA';
2041 jai_cmn_rg_others_pkg.check_balances(
2042 p_organization_id => v_org_id ,
2043 p_location_id => v_loc_id ,
2044 p_register_type => lv_register_type ,
2045 p_trx_amount => ln_cess_amount ,
2046 p_process_flag => lv_process_flag ,
2047 p_process_message => lv_process_message
2048 );
2049
2050 if lv_process_flag <> jai_constants.successful then
2051 lv_pla_cess_avlbl := 'FALSE';
2052 else
2053 lv_pla_cess_avlbl := 'TRUE';
2054 end if;
2055
2056
2057 /* ends here additions by ssumaith to check for negative cess - bug# 4171272 */
2058
2059
2060
2061 FOR Line_Rec IN Line_Cur LOOP
2062 OPEN item_class_cur(V_ORG_ID,Line_Rec.Inventory_Item_Id);
2063 FETCH item_class_cur INTO v_item_class , v_excise_flag;
2064 CLOSE item_class_cur;
2065 FOR excise_cal_rec IN excise_cal_cur(Line_Rec.customer_trx_line_id, Line_Rec.Inventory_Item_ID, v_org_id) LOOP
2066 IF excise_cal_rec.t_type IN ('Excise') THEN
2067 v_basic_ed := NVL(v_basic_ed,0) + NVL(excise_cal_rec.func_amt,0);
2068 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
2069 v_additional_ed := NVL(v_additional_ed,0) + NVL(excise_cal_rec.func_amt,0);
2070 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
2071 v_other_ed := NVL(v_other_ed,0) + NVL(excise_cal_rec.func_amt,0);
2072 END IF;
2073 END LOOP;
2074 v_tax_amount := NVL(v_basic_ed,0) + NVL(v_additional_ed,0) + NVL(v_other_ed,0);
2075 v_basic_Ed := 0;
2076 v_additional_ed := 0;
2077 v_other_ed := 0;
2078 IF v_item_class IN ('CGEX','CGIN') THEN
2079 v_rg23c_tax_amount := NVL(v_rg23c_tax_amount,0) + NVL(v_tax_amount,0);
2080 ELSIF v_item_class IN ('RMIN','RMEX') THEN
2081 v_rg23a_tax_amount := NVL(v_rg23a_tax_amount,0) + NVL(v_tax_amount,0);
2082 ELSIF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') THEN
2083 v_other_tax_amount := NVL(v_other_tax_amount,0) + NVL(v_tax_amount,0);
2084 END IF;
2085
2086 v_tax_amount:=v_total_excise_amt;
2087
2088 IF NVL(v_excise_flag,'N') = 'Y' THEN
2089 IF NVL(v_ssi_unit_flag,'N') = 'N' THEN
2090 /*
2091 || code changed by aiyer for the bug 4101549
2092 || v_complete_flag should have the values as ('N','A','C','P')
2093 */
2094 IF v_complete_flag IN ('N','A','C','P') THEN
2095 IF v_rg_flag = 'Y' THEN
2096 IF v_reg_code IN ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
2097 --3661746
2098 -- following code modified by ssumaith - bug# --3661746
2099 -- in order to hit the register based on preferences.
2100 /*
2101 Added code in the following segment to check for cess balance also
2102 */
2103
2104 IF v_item_class IN ('FGIN','FGEX','CCIN','CCEX','CGIN','CGEX','RMIN','RMEX') THEN
2105 IF v_reg_type IS NULL THEN
2106 OPEN preference_reg_cur(v_org_id,v_loc_id);
2107 FETCH preference_reg_cur INTO rg23a,rg23c,pla;
2108 CLOSE preference_reg_cur;
2109
2110 FOR reg_balance IN reg_balance_cur(v_org_id,v_loc_id) LOOP
2111 IF rg23a = 1 THEN
2112 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
2113 v_rg23a_tax_amount := v_tax_amount;
2114 v_reg_type := 'RG23A';
2115 ELSE
2116 IF rg23c = 2 THEN
2117 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
2118 v_rg23c_tax_amount := v_tax_amount;
2119 v_reg_type := 'RG23C';
2120 ELSIF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
2121 v_reg_type := 'PLA';
2122 END IF;
2123 ELSIF pla = 2 THEN
2124 IF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
2125 v_reg_type := 'PLA';
2126 ELSIF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
2127 v_rg23c_tax_amount := v_tax_amount;
2128 v_reg_type := 'RG23C';
2129 END IF;
2130 END IF;
2131 END IF;
2132 ELSIF rg23c = 1 THEN
2133 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
2134 v_rg23c_tax_amount := v_tax_amount;
2135 v_reg_type := 'RG23C';
2136 ELSE
2137 IF rg23a = 2 THEN
2138 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
2139 v_rg23a_tax_amount := v_tax_amount;
2140 v_reg_type := 'RG23A';
2141 ELSIF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
2142 v_reg_type := 'PLA';
2143 END IF;
2144 ELSIF pla = 2 THEN
2145 IF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
2146 v_reg_type := 'PLA';
2147 ELSIF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
2148 v_rg23a_tax_amount := v_tax_amount;
2149 v_reg_type := 'RG23A';
2150 END IF;
2151 END IF;
2152 END IF;
2153 ELSIF pla = 1 THEN
2154 IF reg_balance.pla_balance >= v_tax_amount AND lv_pla_cess_avlbl = 'TRUE' THEN
2155 v_reg_type := 'PLA';
2156 ELSE
2157 IF rg23c = 2 THEN
2158 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
2159 v_rg23c_tax_amount := v_tax_amount;
2160 v_reg_type := 'RG23C';
2161 ELSIF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
2162 v_rg23a_tax_amount := v_tax_amount;
2163 v_reg_type := 'RG23A';
2164 END IF;
2165 ELSIF rg23a = 2 THEN
2166 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
2167 v_rg23a_tax_amount := v_tax_amount;
2168 v_reg_type := 'RG23A';
2169 ELSIF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
2170 v_rg23c_tax_amount := v_tax_amount;
2171 v_reg_type := 'RG23C';
2172 END IF;
2173 END IF;
2174 END IF;
2175 END IF; -- pref 1 if condition's end if
2176
2177 IF v_reg_type is null THEN
2178 /* raise_application_error(-20102,'None of the registers have enough balance for the excise duty -> ' || v_tax_amount || ' Or Cess amount => ' || ln_Cess_amount);
2179 */ 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 ;
2180 END IF;
2181 IF v_reg_type = 'PLA' and NVL(v_ssi_unit_flag,'N') <> 'Y' THEN
2182 IF v_tax_amount > reg_balance.pla_balance AND lv_pla_cess_avlbl = 'TRUE' THEN
2183 /* raise_application_error(-20102,'PLA Balance -> ' || reg_balance.pla_balance ||
2184 ' 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 ||
2185 ' is not enough for the excise duty -> ' || v_tax_amount ; return ;
2186 END IF;
2187 ELSIF v_reg_type = 'RG23A' THEN
2188 IF v_tax_amount > reg_balance.rg23a_balance AND lv_rg23a_cess_avlbl = 'TRUE' THEN
2189 /* raise_application_error(-20102,'RG23A Balance -> ' || reg_balance.rg23a_balance ||
2190 ' 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 ||
2191 ' is not enough for the excise duty -> ' || v_tax_amount ; return ;
2192 END IF;
2193 ELSIF v_reg_type = 'RG23C' THEN
2194 IF v_tax_amount > reg_balance.rg23c_balance AND lv_rg23c_cess_avlbl = 'TRUE' THEN
2195 /* raise_application_error(-20102,'RG23C Balance -> ' || reg_balance.rg23c_balance ||
2196 ' 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 ||
2197 ' is not enough for the excise duty -> ' || v_tax_amount ; return ;
2198 END IF;
2199 END IF;
2200 END LOOP;
2201 END IF; -- for v_reg_type is null
2202 v_excise_paid_register := v_reg_type;
2203 END IF; -- for v_item_class in ('FGIN','FGEX'.... )
2204 END IF; -- for v_reg_code in ('DOMESTIC_EXCISE')....
2205
2206 /*
2207 the following piece of code added by sriram bug # 2521387
2208 */
2209 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, begin
2210 ----------------------------------------------------------------------------
2211 --For the AR transaction that is from OFI Open Interface,
2212 --v_excise_paid_register don't need to re-update in following piece of code.
2213 IF(pr_new.created_from = 'RAXTRX' AND ln_open_source > 0)THEN
2214 NULL;
2215 ELSE
2216 ----------------------------------------------------------------------------
2217 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, end
2218 Declare
2219 v_reg_type VARCHAR2(10);
2220 Begin
2221 SELECT once_completed_flag
2222 INTO v_reg_type
2223 FROM JAI_AR_TRXS
2224 WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
2225
2226 IF v_reg_type = 'P' THEN
2227 v_reg_type := 'PLA';
2228 ELSIF v_reg_type = 'A' THEN
2229 v_reg_type := 'RG23A';
2230 ELSIF v_reg_type = 'C' THEN
2231 v_reg_type := 'RG23C';
2232 END IF;
2233
2234 IF v_reg_type is not null and v_reg_type <> 'N' THEN
2235 v_excise_paid_register := v_reg_type;
2236 END IF;
2237
2238 Exception
2239 When Others Then
2240 /* RAISE_APPLICATION_ERROR(-10101,SQLERRM);
2241 */ pv_return_code := jai_constants.expected_error ; pv_return_message := SQLERRM ; return ;
2242 End ;
2243 END IF;--Added by Zhiwei for Open Interface ER bug#11683927.
2244 /*
2245 Ends here
2246 */
2247
2248 UPDATE JAI_AR_TRX_LINES
2249 SET PAYMENT_REGISTER = v_excise_paid_register
2250 WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
2251 INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
2252 CUSTOMER_TRX_ID = v_customer_trx_id;
2253 END IF; -- for v_rg_flag = 'Y'
2254 v_excise_paid_register := '';
2255 END IF; -- for v_complete_flag = 'N'
2256 -- END IF; --3661746
2257 ELSIF NVL(v_ssi_unit_flag,'N') = 'Y' THEN
2258 IF v_item_class IN ('RMIN','RMEX','CGEX','CGIN','FGIN','FGEX','CCIN','CCEX') THEN
2259 /*
2260 || code changed by aiyer for the bug 4101549
2261 || v_complete_flag should have the values as ('N','A','C','P')
2262 */
2263 IF v_complete_flag IN ('N','A','C','P') THEN
2264 IF v_rg_flag = 'Y' THEN
2265 IF v_reg_code IN ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
2266 IF v_reg_type IS NULL THEN
2267 OPEN preference_reg_cur(v_org_id,v_loc_id);
2268 FETCH preference_reg_cur INTO rg23a,rg23c,pla;
2269 CLOSE preference_reg_cur;
2270 --======
2271 FOR reg_balance IN reg_balance_cur(v_org_id,v_loc_id) LOOP --3661746
2272 IF rg23a = 1 THEN
2273 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
2274 v_rg23a_tax_amount := v_tax_amount;
2275 v_reg_type := 'RG23A';
2276 ELSE
2277 IF rg23c = 2 THEN
2278 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
2279 v_rg23c_tax_amount := v_tax_amount;
2280 v_reg_type := 'RG23C';
2281 ELSE
2282 v_reg_type := 'PLA';
2283 END IF;
2284 ELSIF pla = 2 THEN
2285 v_reg_type := 'PLA';
2286 END IF;
2287 END IF;
2288 ELSIF rg23c = 1 THEN
2289 IF reg_balance.rg23c_balance >= v_tax_amount AND lv_rg23c_cess_avlbl = 'TRUE' THEN
2290 v_rg23c_tax_amount := v_tax_amount;
2291 v_reg_type := 'RG23C';
2292 ELSE
2293 IF rg23a = 2 THEN
2294 IF reg_balance.rg23a_balance >= v_tax_amount AND lv_rg23a_cess_avlbl = 'TRUE' THEN
2295 v_rg23a_tax_amount := v_tax_amount;
2296 v_reg_type := 'RG23A';
2297 ELSE
2298 v_reg_type := 'PLA';
2299 END IF;
2300 ELSIF pla = 2 THEN
2301 v_reg_type := 'PLA';
2302 END IF;
2303 END IF;
2304 ELSIF pla = 1 THEN
2305 v_reg_type := 'PLA';
2306 END IF;
2307 --3661746
2308 IF v_reg_type = 'RG23A' THEN
2309 IF v_tax_amount > reg_balance.rg23a_balance AND lv_rg23a_cess_avlbl = 'TRUE' THEN
2310 /* raise_application_error(-20102,'RG23A Balance -> ' || reg_balance.rg23a_balance ||
2311 ' 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 ||
2312 ' is not enough for the excise duty -> ' || v_tax_amount ; return ;
2313 END IF;
2314 ELSIF v_reg_type = 'RG23C' THEN
2315 IF v_tax_amount > reg_balance.rg23c_balance AND lv_rg23c_cess_avlbl = 'TRUE' THEN
2316 /* raise_application_error(-20102,'RG23C Balance -> ' || reg_balance.rg23c_balance ||
2317 ' 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 ||
2318 ' is not enough for the excise duty -> ' || v_tax_amount ; return ;
2319 END IF;
2320 END IF;
2321 --3661746
2322 END LOOP; --3661746
2323 END IF; -- for v_reg_type is null
2324
2325 v_excise_paid_register := v_reg_type;
2326 -- END IF; -- for v_item_clas in ('FGIN','FGEX'...)
2327 END IF; -- for if v_reg_code in ('DOMESTIC_EXCISE'....)
2328
2329 /*
2330 the following piece of code added by sriram bug # 2521387
2331 */
2332
2333 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, begin
2334 ----------------------------------------------------------------------------
2335 --For the AR transaction that is from OFI Open Interface,
2336 --v_excise_paid_register don't need to re-update in following piece of code.
2337 IF(pr_new.created_from = 'RAXTRX' AND ln_open_source > 0)THEN
2338 NULL;
2339 ELSE
2340 ----------------------------------------------------------------------------
2341 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, end
2342
2343 Declare
2344 v_reg_type1 VARCHAR2(10);
2345 Begin
2346 SELECT once_completed_flag
2347 INTO v_reg_type1
2348 FROM JAI_AR_TRXS
2349 WHERE CUSTOMER_TRX_ID = pr_new.Customer_trx_id;
2350
2351 If v_reg_type1 = 'P' THEN
2352 v_reg_type1 := 'PLA';
2353 ELSIF v_reg_type1 = 'A' THEN
2354 v_reg_type1 := 'RG23A';
2355 ELSIF v_reg_type1 = 'C' THEN
2356 v_reg_type1 := 'RG23C';
2357 END IF;
2358
2359 if v_reg_type1 is not null and v_reg_type1 <> 'N' then
2360 v_excise_paid_register := v_reg_type1;
2361 end if;
2362
2363 Exception
2364 When Others Then
2365 /* RAISE_APPLICATION_ERROR(-10101,SQLERRM);
2366 */ pv_return_code := jai_constants.expected_error ; pv_return_message := SQLERRM ; return ;
2367 END;
2368 END IF;--Added by Zhiwei for Open Interface ER bug#11683927.
2369 /*
2370 Ends here - Additions by Sriram
2371 */
2372 UPDATE JAI_AR_TRX_LINES
2373 SET PAYMENT_REGISTER = v_excise_paid_register
2374 WHERE CUSTOMER_TRX_LINE_ID = LINE_REC.customer_trx_line_id AND
2375 INVENTORY_ITEM_ID = LINE_REC.inventory_item_id AND
2376 CUSTOMER_TRX_ID = v_customer_trx_id;
2377 END IF; -- for v_rg_flag = 'Y;
2378 v_excise_paid_register := '';
2379 END IF; -- for v_complete_flag = 'N'
2380 END IF; -- for v_item_class in ('...)
2381 END IF; -- for v_ssi_unit_flag ....
2382 END IF; -- v_excise_flag = 'Y'
2383
2384
2385 END LOOP;
2386 INSERT INTO JAI_AR_TRX_INS_HDRS_T
2387 (
2388 ORGANIZATION_ID,
2389 LOCATION_ID,
2390 CUSTOMER_TRX_ID ,
2391 SHIP_TO_CUSTOMER_ID,
2392 SHIP_TO_SITE_USE_ID,
2393 CUST_TRX_TYPE_ID,
2394 TRX_DATE,
2395 SOLD_TO_CUSTOMER_ID,
2396 BATCH_SOURCE_ID,
2397 BILL_TO_CUSTOMER_ID , -- BILL_TO_CUSTOMER_ID column in insert added by sriram - 13/may-02
2398 BILL_TO_SITE_USE_ID ,
2399 CREATED_BY ,
2400 CREATION_DATE,
2401 LAST_UPDATED_BY,
2402 LAST_UPDATE_DATE
2403 ) -- BILL_TO_SITE_USE_ID column in insert added by sriram - 13/may-02
2404 VALUES
2405 (
2406 V_ORG_ID,
2407 V_LOC_ID,
2408 V_CUSTOMER_TRX_ID ,
2409 pr_new.SHIP_TO_CUSTOMER_ID,
2410 pr_new.SHIP_TO_SITE_USE_ID,
2411 pr_new.CUST_TRX_TYPE_ID,
2412 pr_new.TRX_DATE,
2413 pr_new.SOLD_TO_CUSTOMER_ID,
2414 pr_new.BATCH_SOURCE_ID,
2415 pr_new.BILL_TO_CUSTOMER_ID ,-- ADDED BY SRIRAM - 13-MAY-2002
2416 pr_new.BILL_TO_SITE_USE_ID,
2417 FND_GLOBAL.USER_ID , -- added standard who columns by brahtod for bug# 4558072
2418 SYSDATE ,
2419 FND_GLOBAL.USER_ID ,
2420 SYSDATE);
2421 END IF;
2422 UPDATE JAI_AR_TRXS
2423 SET
2424 ONCE_COMPLETED_FLAG = pr_new.COMPLETE_FLAG
2425 WHERE CUSTOMER_TRX_ID = V_CUSTOMER_TRX_ID;
2426 END IF;
2427 END IF;
2428
2429 /*
2430 ||Start of code changes for bug 4247989
2431 ||Modification for VAT enhancement, code added by aiyer
2432 */
2433 IF NVL(v_trans_type,'N') NOT IN ('INV','DM') THEN
2434 RETURN;
2435 END IF;
2436
2437 IF nvl(pr_new.created_from,'###') = 'ARXTWMAI'
2438 OR nvl(pr_new.created_from,'###') = 'RAXTRX' --Added 'OR' condition by Zhiwei for Open Interface ER bug#11683927.
2439 THEN
2440
2441 --Added by Bo Li for bug#10043656 Begin
2442 -------------------------------------------------------
2443 --lv_enable_gst_flag := JAI_GST_GENERAL_PKG.IS_GST_ENABLED;
2444
2445 IF nvl(lv_enable_gst_flag,'N') = 'N'
2446 THEN
2447 ----------------------------------------------------------
2448 --Added by Bo Li for bug#10043656 End
2449
2450 --Add nvl by Xiao for bug#11936390 on 7-Apr-2011.
2451 IF nvl(ln_external_flag, 0) = 0 THEN --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011
2452
2453 OPEN c_vat_invoice_cur;
2454 FETCH c_vat_invoice_cur INTO lv_vat_invoice_number;
2455 CLOSE c_vat_invoice_cur;
2456
2457 IF lv_vat_invoice_number IS NOT NULL THEN
2458 return;
2459 END IF;
2460 END IF ; --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011
2461 /*
2462 || check if VAT regime setup has been done
2463 || if yes then continue with the VAT processing
2464 */
2465 OPEN cur_vat_taxes_exist;
2466 FETCH cur_vat_taxes_exist into ln_regime_id,ln_regime_code;
2467
2468
2469 /*
2470 || Added by kunkumar for bug#5645003
2471 || Check if only 'VAT REVERSAL' tax type is present in ja_in_ra_cust_trx_tax_lines
2472 */
2473 IF ln_regime_id IS NULL THEN
2474 lv_vat_reversal := 'VAT REVERSAL' ;
2475 OPEN c_chk_vat_reversal(lv_vat_reversal) ;
2476 FETCH c_chk_vat_reversal INTO ln_vat_reversal_exists;
2477 CLOSE c_chk_vat_reversal ;
2478
2479 /*
2480 || Retrieve the regime_id for 'VAT REVERSAL' tax type, which is of regime code 'VAT'
2481 */
2482 IF ln_vat_reversal_exists = 1 THEN
2483 OPEN c_get_regime_id ;
2484 FETCH c_get_regime_id INTO ln_regime_id ;
2485 CLOSE c_get_regime_id ;
2486
2487 IF ln_regime_id IS NOT NULL THEN
2488 ln_regime_code := jai_constants.vat_regime ;
2489 END IF ;
2490 END IF ;
2491 END IF ;
2492 --bug#5645003, ends
2493
2494 IF UPPER(nvl(ln_regime_code,'####')) = jai_constants.vat_regime THEN
2495 --Add nvl by Xiao for bug#11936390 on 7-Apr-2011.
2496 IF nvl(ln_external_flag, 0) = 0 THEN --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011
2497 /*
2498 || Check the VAT Regime setup for vat invoice no being same as excise invoice no.
2499 || If the attribute value is 'N' or this attribute code does not exist the generate the vat invoice number
2500 */
2501 OPEN cur_get_same_inv_no ( cp_organization_id => v_org_id ,
2502 cp_location_id => v_loc_id
2503 ) ;
2504 FETCH cur_get_same_inv_no INTO lv_vat_no_same_exc_no;
2505 CLOSE cur_get_same_inv_no ;
2506
2507 IF nvl(lv_vat_no_same_exc_no,'N') = 'Y' THEN
2508 /*
2509 || vat invoice number should be same as excise invoice number
2510 */
2511
2512 OPEN cur_get_exc_inv_no ;
2513 FETCH cur_get_exc_inv_no INTO lv_vat_invoice_number;
2514 CLOSE cur_get_exc_inv_no;
2515
2516 END IF;
2517
2518
2519 IF lv_vat_invoice_number IS NULL THEN
2520 /*
2521 || Either the setup for excise invoice number has not been doe or the attribute_value was set to 'N'
2522 || In either of this cases generate VAT Invoice number
2523 */
2524
2525 /*
2526 || added csahoo - for seperate vat invoice num for unreg dealers - bug# 5233925
2527 */
2528 IF check_reg_dealer( NVL(pr_new.SHIP_TO_CUSTOMER_ID ,pr_new.BILL_TO_CUSTOMER_ID) ,
2529 NVL(pr_new.SHIP_TO_SITE_USE_ID, pr_new.BILL_TO_SITE_USE_ID)
2530 ) THEN
2531 lv_doc_type_class := 'I';
2532 ELSE
2533 lv_doc_type_class := 'UI';
2534 END IF;
2535
2536 /*
2537 || csahoo - for seperate vat invoice num for unreg dealers - bug# 5233925
2538 */
2539
2540 jai_cmn_rgm_setup_pkg.gen_invoice_number(
2541 p_regime_id => ln_regime_id ,
2542 p_organization_id => v_org_id ,
2543 p_location_id => v_loc_id ,
2544 p_date => pr_new.trx_date ,
2545 p_doc_class => lv_doc_type_class , --added for bug#7475924
2546 p_doc_type_id => pr_new.batch_source_id ,
2547 p_invoice_number => lv_vat_invoice_number ,
2548 p_process_flag => lv_process_flag ,
2549 p_process_msg => lv_process_message
2550 );
2551
2552 IF lv_process_flag = jai_constants.expected_error OR
2553 lv_process_flag = jai_constants.unexpected_error
2554 THEN
2555 CLOSE cur_vat_taxes_exist;
2556 /* raise_application_error(-20130,lv_process_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_process_message ; return ;
2557 /*
2558 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
2559 EXCEPTION_CODE => NULL ,
2560 EXCEPTION_TEXT => lv_process_message
2561 );
2562 */
2563 END IF;
2564 END IF; -- END IF of lv_excise_inv_no IS NULL
2565
2566 --Added by Xiao Lv for Open Interface ER bug#11683927 on 03-Mar-2011, begin
2567 ---------------------------------------------------------------------------------
2568 ELSE --For 'External' event, directly fetch vat invoice no.
2569 OPEN get_vat_invoice_no_cur;
2570 FETCH get_vat_invoice_no_cur INTO lv_vat_invoice_number;
2571 CLOSE get_vat_invoice_no_cur;
2572 ---------------------------------------------------------------------------------
2573 --Added by Xiao Lv for Open Interface ER bug#11683927 on 03-Mar-2011, end
2574 END IF;
2575
2576 /*
2577 || Get the gl_date from ra_cust_trx_lines_gl_dist_all
2578 */
2579 OPEN cur_get_gl_date('REC');
2580 FETCH cur_get_gl_date INTO ld_gl_date;
2581 CLOSE cur_get_gl_date;
2582
2583 --Added by zhiwei for Open Interface External not need to VAT accounting and Repository begin
2584 ---------------------------------------------------------------------------------------------
2585 IF ln_external_flag > 0 THEN
2586 null;
2587 else
2588 ---------------------------------------------------------------------------------------------
2589 --Added by zhiwei for Open Interface External not need to VAT accounting and Repository end
2590
2591
2592 /*
2593 || IF the VAT invoice Number has been successfully generated, then pass accounting entries
2594 */
2595 jai_cmn_rgm_vat_accnt_pkg.process_order_invoice (
2596 p_regime_id => ln_regime_id ,
2597 p_source => jai_constants.source_ar ,
2598 p_organization_id => v_org_id ,
2599 p_location_id => v_loc_id ,
2600 p_delivery_id => NULL ,
2601 p_customer_trx_id => pr_new.customer_trx_id ,
2602 p_transaction_type => v_trans_type ,
2603 p_vat_invoice_no => lv_vat_invoice_number ,
2604 p_default_invoice_date => nvl(ld_gl_date,pr_new.trx_date) ,
2605 p_batch_id => NULL ,
2606 p_called_from => jai_constants.vat_repo_call_inv_comp ,
2607 p_debug => jai_constants.no ,
2608 p_process_flag => lv_process_flag ,
2609 p_process_message => lv_process_message
2610 );
2611
2612 IF lv_process_flag = jai_constants.expected_error OR
2613 lv_process_flag = jai_constants.unexpected_error
2614 THEN
2615 CLOSE cur_vat_taxes_exist ;
2616 /* raise_application_error(-20130,lv_process_message); */ pv_return_code := jai_constants.expected_error ; pv_return_message := lv_process_message ; return ;
2617 /*
2618 app_exception.raise_exception( EXCEPTION_TYPE => 'APP',
2619 EXCEPTION_CODE => NULL ,
2620 EXCEPTION_TEXT => lv_process_message
2621 );
2622 */
2623
2624 END IF;
2625
2626
2627
2628 UPDATE
2629 JAI_AR_TRXS
2630 SET
2631 vat_invoice_no = lv_vat_invoice_number ,
2632 vat_invoice_date = nvl(ld_gl_date,pr_new.trx_date)
2633 WHERE
2634 customer_trx_id = pr_new.customer_trx_id ;
2635
2636 END IF; -- END IF of vat type of taxes found
2637
2638
2639 --Added by zhiwei for Open Interface External not need to VAT accounting and Repository begin
2640 ---------------------------------------------------------------------------------------------
2641 END IF;
2642 ---------------------------------------------------------------------------------------------
2643 --Added by zhiwei for Open Interface External not need to VAT accounting and Repository end
2644
2645 CLOSE cur_vat_taxes_exist;
2646
2647 /*Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952 Begin*/
2648 OPEN cur_st_taxes_exist;
2649 FETCH cur_st_taxes_exist INTO ln_regime_id,ln_regime_code;
2650 CLOSE cur_st_taxes_exist;
2651
2652 /*Added by Qignlei on 31-dec-2011 for bug#13537078 begin*/
2653 OPEN c_st_invoice_cur;
2654 FETCH c_st_invoice_cur INTO lv_st_inv_number;
2655 CLOSE c_st_invoice_cur;
2656 /*Added by Qignlei on 31-dec-2011 for bug#13537078 end*/
2657
2658 IF UPPER(nvl(ln_regime_code,'####')) = jai_constants.service_regime THEN
2659
2660 IF nvl(ln_external_flag,0) = 0 AND lv_st_inv_number IS NULL THEN
2661
2662 lv_doc_type_class := 'I';
2663
2664 jai_cmn_rgm_setup_pkg.gen_invoice_number(
2665 p_regime_id => ln_regime_id ,
2666 p_organization_id => v_org_id ,
2667 p_location_id => v_loc_id ,
2668 p_date => pr_new.trx_date ,
2669 p_doc_class => lv_doc_type_class ,
2670 p_doc_type_id => pr_new.batch_source_id ,
2671 p_invoice_number => lv_st_inv_number ,
2672 p_process_flag => lv_process_flag ,
2673 p_process_msg => lv_process_message
2674 );
2675
2676 IF lv_process_flag = jai_constants.expected_error OR
2677 lv_process_flag = jai_constants.unexpected_error
2678 THEN
2679 pv_return_code := jai_constants.expected_error ;
2680 pv_return_message := lv_process_message ;
2681 RETURN ;
2682
2683 END IF;
2684
2685 ELSE
2686 OPEN c_st_invoice_cur;
2687 FETCH c_st_invoice_cur INTO lv_st_inv_number;
2688 CLOSE c_st_invoice_cur;
2689
2690 END IF;
2691
2692
2693 UPDATE jai_ar_trxs
2694 SET st_inv_number = lv_st_inv_number
2695 WHERE customer_trx_id = pr_new.customer_trx_id ;
2696
2697 ELSE
2698 null;
2699 END IF;
2700
2701 /*Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952 End*/
2702
2703
2704
2705 END IF; -- If nvl(lv_enable_gst_flag,'N') = 'N'
2706 ----------------------------------------------------------------
2707 --Added by Bo Li for GST bug#10043656 End
2708 END IF ; --EBD IF of nvl(new.created_from,'###') ='ARXTWMAI'
2709
2710 /*
2711 ||End of code changes for bug 4247989
2712 */
2713 /* Added an exception block by Ramananda for bug#4570303 */
2714 EXCEPTION
2715 WHEN OTHERS THEN
2716 Pv_return_code := jai_constants.unexpected_error;
2717 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARU_T4 ' || substr(sqlerrm,1,1900);
2718 END ARU_T4 ;
2719
2720 /*
2721 REM +======================================================================+
2722 REM NAME ARU_T5
2723 REM
2724 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ARIUD_T1
2725 REM
2726 REM NOTES Refers to old trigger JAI_AR_RCTA_ARU_T6
2727 REM
2728 REM+=======================================================================+
2729 REM Change History
2730 REM slno Date Name BugNo File Version
2731 REM +=======================================================================+
2732 REM
2733 REM
2734 REM -----------------------------------------------------------------------
2735 REM 1. 04-Jul-2006 aiyer 5364288 120.3
2736 REM -----------------------------------------------------------------------
2737 REM Comments:-
2738 REM Removed references to ra_customer_trx_all and replaced it with jai_ar_trx.
2739 REM also removed the cursor org_cur which was trying to fetch the org_id from ra_customer_trx_all.
2740 REM This was not required as pr_new.org_id is already being passed to the procedure and has the
2741 REM value of org_id.
2742 REM -----------------------------------------------------------------------
2743 REM 2. 24-May-2011 Xiao for POT change, reg bug#12533434.
2744 REM Fixed: Fetch gl date in AR REV lines, and compare this date with pot
2745 REM effective date, to determine if it is accrual basis or cash basis. --Added by Chong.Lei for POT code port
2746 REM
2747 REM -----------------------------------------------------------------------
2748 REM -----------------------------------------------------------------------
2749 REM 3.
2750 REM -----------------------------------------------------------------------
2751 REM -----------------------------------------------------------------------
2752 REM 4.
2753 REM -----------------------------------------------------------------------
2754 REM
2755 REM
2756 REM+======================================================================+
2757 */
2758 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
2759 v_line_no NUMBER := 0;
2760 v_books_id NUMBER := 1;
2761 v_salesrep_id NUMBER;
2762 v_line_type VARCHAR2(30);
2763 v_vat_tax NUMBER;
2764 v_ccid NUMBER;
2765 v_cust_trx_line_id RA_CUSTOMER_TRX_LINES_ALL.customer_trx_line_id%TYPE;
2766 v_customer_trx_line_id NUMBER ;
2767 v_customer_trx_id NUMBER; -- := pr_new.customer_trx_id; --Ramananda for File.Sql.35
2768 v_created_from VARCHAR2(30);
2769 c_from_currency_code VARCHAR2(15);
2770 c_conversion_type VARCHAR2(30);
2771 c_conversion_date DATE;
2772 c_conversion_rate NUMBER := 0;
2773 v_converted_rate NUMBER := 1;
2774 req_id NUMBER;
2775 result BOOLEAN;
2776 v_organization_id NUMBER ;
2777 v_location_id NUMBER ;
2778 v_batch_source_id NUMBER ;
2779 v_register_code VARCHAR2(50);
2780 v_order_number VARCHAR2(30);
2781 v_org_id NUMBER(15);
2782 -- Bug 5207772. Added by Lakshmi Gopalsami
2783 v_order_type VARCHAR2(30);
2784
2785 lv_line_type_tax RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE%type ;
2786 lv_line_type_freight RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE%type ;
2787 lv_acct_class_tax RA_CUST_TRX_LINE_GL_DIST_ALL.ACCOUNT_CLASS%type ;
2788 lv_acct_class_freight RA_CUST_TRX_LINE_GL_DIST_ALL.ACCOUNT_CLASS%type ;
2789
2790 ld_st_accrual_date DATE; /*11821537*/
2791
2792 /*Bug 11821537 - Fetch the effective date on which Service Tax needs to accounted in accrual basis instead of cash*/
2793 --Commented by Chong.Lei for POT code port begin
2794 /*
2795 CURSOR c_get_st_accrual_date (p_regime_id NUMBER)
2796 IS
2797 SELECT to_date(attribute_value, 'DD/MM/YYYY')
2798 FROM jai_rgm_registrations
2799 WHERE regime_id = p_regime_id
2800 AND attribute_code = 'EFF_DATE_ST_PT'
2801 AND attribute_type_code = 'OTHERS'
2802 AND registration_type = 'OTHERS';
2803 */
2804 --Commented by Chong.Lei for POT code port end
2805 -- Added by Chong.Lei for POT code port begin
2806 ------------------------------------------------------------------------------------
2807 /*Bug 12805386 - Fetch Effective Date of Point of Taxation only if Organization Type is either 'INDIVIDUALS' or 'PARTNERSHIP FIRM'
2808 or 'PROPRIETARY FIRM' and if the Service Type is one mentioned in Rule 7 of Point of Taxation Rules 2011 i.e
2809 105-p, 105-q, 105-s, 105-t, 105-u, 105-za, 105-zzzzm*/
2810 CURSOR c_get_st_accrual_date(p_regime_id NUMBER, p_organization_id NUMBER, p_location_id NUMBER) IS
2811 select to_date(attribute_value, 'DD/MM/YYYY')
2812 from JAI_RGM_ORG_REGNS_V
2813 where regime_id = p_regime_id
2814 and organization_id = p_organization_id
2815 and location_id = p_location_id
2816 AND attribute_code = 'EFF_DATE_ST_PT'
2817 AND attribute_type_code = 'OTHERS'
2818 AND registration_type = 'OTHERS'
2819 AND (NOT EXISTS
2820 (select '1'
2821 from JAI_RGM_ORG_REGNS_V
2822 where regime_id = p_regime_id
2823 and attribute_code IN 'INV_ORG_CLASSIFICATION'
2824 and attribute_value <> 'ORGANIZATION'
2825 and organization_id = p_organization_id
2826 and location_id = p_location_id)
2827 OR
2828 NOT EXISTS
2829 (select '1'
2830 from JAI_RGM_ORG_REGNS_V
2831 where regime_id = p_regime_id
2832 and attribute_code IN 'SERVICE TYPE'
2833 and attribute_value <> 'OTHER'
2834 and organization_id = p_organization_id
2835 and location_id = p_location_id)
2836 );
2837
2838 --Xiao for POT changes, reg bug#12533434
2839 CURSOR c_get_trx_date(pn_customer_trx_line_id NUMBER) IS
2840 SELECT gl_date
2841 FROM ra_cust_trx_line_gl_dist_all
2842 WHERE customer_trx_id = pr_new.customer_trx_id
2843 AND customer_trx_line_id = pn_customer_trx_line_id
2844 AND account_class = 'REV';
2845
2846 ld_trx_gl_date DATE;
2847 --Xiao for POT changes, reg bug#12533434
2848 ------------------------------------------------------------------------------------
2849 -- Added by Chong.Lei for POT code port end
2850
2851 -- CURSOR ADDED BY SRIRAM - BUG # 2654567
2852
2853 CURSOR C_GET_TRX_DETAILS
2854 IS
2855 SELECT * FROM JAI_AR_TRX_LINES
2856 WHERE CUSTOMER_TRX_ID = pr_new.CUSTOMER_TRX_ID;
2857
2858
2859 CURSOR C_GET_TRX_COUNT
2860 IS
2861 SELECT COUNT(*)
2862 FROM RA_CUSTOMER_TRX_LINES_ALL
2863 WHERE CUSTOMER_TRX_ID = pr_new.CUSTOMER_TRX_ID
2864 AND LINE_TYPE in (lv_line_type_tax, lv_line_type_freight); /* Modified by Ramananda for removal of SQL LITERALs */
2865 --('TAX','FREIGHT');
2866
2867 CURSOR C_GET_GL_DIST_ALL_COUNT IS
2868 SELECT COUNT(*)
2869 FROM RA_CUST_TRX_LINE_GL_DIST_ALL
2870 WHERE CUSTOMER_TRX_ID = pr_new.CUSTOMER_TRX_ID
2871 AND ACCOUNT_CLASS IN (lv_acct_class_tax , lv_acct_class_freight ); /* Modified by Ramananda for removal of SQL LITERALs */
2872 --AND ACCOUNT_CLASS IN ('TAX','FREIGHT');
2873
2874 CURSOR TAX_TYPE_CUR(p_customer_trx_line_id Number) IS
2875 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
2876 FROM JAI_AR_TRX_TAX_LINES A , JAI_CMN_TAXES_ALL B
2877 WHERE link_to_cust_trx_line_id = p_customer_trx_line_id
2878 AND A.tax_id = B.tax_id
2879 ORDER BY 1;
2880
2881 lv_tax_regime_code zx_rates_b.tax_regime_code%type ;
2882 ln_party_tax_profile_id zx_party_tax_profile.party_tax_profile_id%type ;
2883 ln_tax_rate_id zx_rates_b.tax_rate_id%type ;
2884 /* Added by Ramananda for bug#4468353 , end */
2885
2886 CURSOR TAX_CCID_CUR(p_tax_id IN NUMBER) IS
2887 SELECT tax_account_id
2888 FROM JAI_CMN_TAXES_ALL B
2889 WHERE B.tax_id = p_tax_id ;
2890
2891
2892 CURSOR SO_AR_HDR_INFO IS
2893 SELECT organization_id, location_id, batch_source_id
2894 FROM JAI_AR_TRXS
2895 WHERE Customer_Trx_ID = v_customer_trx_id;
2896
2897 /*Bug 8625057 - Start*/
2898 CURSOR cur_chk_rgm ( cp_tax_type JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE )
2899 IS
2900 SELECT regime_id, regime_code
2901 FROM jai_regime_tax_types_v jrttv
2902 WHERE upper(jrttv.tax_type) = upper(cp_tax_type);
2903
2904 ln_regime_code VARCHAR2(30);
2905 ln_regime_id NUMBER;
2906 /*Bug 8625057 - End*/
2907
2908
2909 CURSOR register_code_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER,
2910 p_batch_source_id IN NUMBER) IS
2911 SELECT register_code
2912 FROM JAI_OM_OE_BOND_REG_HDRS
2913 WHERE organization_id = p_org_id AND location_id = p_loc_id AND
2914 register_id IN (SELECT register_id
2915 FROM JAI_OM_OE_BOND_REG_DTLS
2916 WHERE order_type_id = p_batch_source_id AND order_flag = 'N');
2917
2918 /* Bug5207772. Added by Lakshmi Gopalsami
2919 Fixed performance issue - SQL id - 17698796
2920 Removed the reference to so_headers_all and added oe_transaction_types_tl
2921 Changed the parameter to p_order_type instead of p_order_number
2922 */
2923 CURSOR register_code_cur1(p_organization_id NUMBER,
2924 p_location_id NUMBER,
2925 p_order_type VARCHAR2) IS
2926 SELECT A.register_code
2927 FROM JAI_OM_OE_BOND_REG_HDRS A,
2928 JAI_OM_OE_BOND_REG_DTLS b,
2929 oe_transaction_types_tl ott
2930 WHERE A.organization_id = p_organization_id
2931 AND A.location_id = p_location_id
2932 AND A.register_id = b.register_id
2933 AND b.order_flag = 'Y'
2934 AND b.order_type_id = ott.transaction_type_id
2935 AND ott.NAME = p_order_type;
2936
2937
2938
2939 /*
2940 || Added by kunkumar for bug#5645003
2941 || Check if only 'VAT REVERSAL' tax type is present in ja_in_ra_cust_trx_tax_lines
2942 */
2943 CURSOR c_chk_vat_reversal (cp_tax_type jai_cmn_taxes_all.tax_type%TYPE )
2944 IS
2945 SELECT
2946 1
2947 FROM
2948 JAI_AR_TRX_TAX_LINES jcttl,
2949 JAI_AR_TRX_LINES jctl,
2950 JAI_CMN_TAXES_ALL jtc
2951 WHERE
2952 jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id AND
2953 jctl.customer_trx_id = pr_new.customer_trx_id AND
2954 jcttl.tax_id = jtc.tax_id AND
2955 jtc.org_id = pr_new.org_id AND
2956 jtc.tax_type = cp_tax_type ;
2957
2958 /*
2959 || Retrieve the regime_id which is of regime code 'VAT'
2960 */
2961 CURSOR c_get_regime_id
2962 IS
2963 SELECT
2964 regime_id
2965 FROM
2966 jai_regime_tax_types_v
2967 WHERE
2968 regime_code = jai_constants.vat_regime
2969 AND rownum = 1 ;
2970
2971
2972
2973 v_err_mesg VARCHAR2(250);
2974
2975 /*
2976 || start of bug 5364288 - code modified by aiyer
2977 ||changed the variable definition from RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE to JAI_AR_TRXS.CUSTOMER_TRX_ID%TYPE
2978 */
2979 v_trx_num JAI_AR_TRXS.TRX_NUMBER%TYPE;
2980
2981 /* End of bug 5364288 */
2982
2983 v_TRX_TAX_COUNT Number;
2984 v_trx_gl_dist_COUNT Number;
2985 BEGIN
2986 pv_return_code := jai_constants.successful ;
2987 /*------------------------------------------------------------------------------------------
2988 FILENAME: JA_IN_APPS_AR_LINES_INSERT_TRG.sql
2989 CHANGE HISTORY:
2990 1. 10-Aug-2005 Aiyer bug 4545146 version 120.1
2991 Issue:-
2992 Deadlock on tables due to multiple triggers on the same table (in different sql files)
2993 firing in the same phase.
2994 Fix:-
2995 Multiple triggers on the same table have been merged into a single file to resolve
2996 the problem
2997 The following files have been stubbed:-
2998 jai_ar_rcta_t1.sql
2999 jai_ar_rcta_t2.sql
3000 jai_ar_rcta_t3.sql
3001 jai_ar_rcta_t4.sql
3002 jai_ar_rcta_t6.sql
3003 jai_ar_rcta_t7.sql
3004 jai_ar_rcta_t8.sql
3005 jai_ar_rcta_t9.sql
3006 Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers in the above files
3007
3008 Dependency:
3009 ----------
3010
3011 Sl No. Bug Dependent on
3012 Bug/Patch set Details
3013 -------------------------------------------------------------------------------------------------
3014
3015 --------------------------------------------------------------------------------------------*/
3016
3017 v_customer_trx_id := pr_new.customer_trx_id; --Ramananda for File.Sql.35
3018
3019 /* Added by Ramananda for removal of SQL LITERALs */
3020 lv_line_type_tax := 'TAX';
3021 lv_line_type_freight := 'FREIGHT' ;
3022 OPEN C_GET_TRX_COUNT ;
3023 FETCH C_GET_TRX_COUNT INTO v_TRX_TAX_COUNT;
3024 CLOSE C_GET_TRX_COUNT;
3025
3026 /* Added by Ramananda for removal of SQL LITERALs */
3027 lv_acct_class_tax := 'TAX';
3028 lv_acct_class_freight := 'FREIGHT' ;
3029 OPEN C_GET_GL_DIST_ALL_COUNT ;
3030 FETCH C_GET_GL_DIST_ALL_COUNT INTO v_trx_gl_dist_COUNT;
3031 CLOSE C_GET_GL_DIST_ALL_COUNT;
3032
3033
3034
3035 IF v_TRX_TAX_COUNT <> v_trx_gl_dist_COUNT THEN
3036 /* RAISE_APPLICATION_ERROR(-20102,'Taxes are not consistent in the RA_CUSTOMER_TRX_LINES_ALL AND RA_CUST_TRX_LINE_GL_DIST_ALL Tables');
3037 */ 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 ;
3038 END IF ;
3039
3040 FOR v_trx_rec in C_GET_TRX_DETAILS
3041
3042 LOOP
3043 v_customer_trx_line_id := v_trx_rec.customer_trx_line_id;
3044 v_trx_num := pr_new.trx_number;
3045 v_created_from := pr_new.created_from;
3046 v_order_number := pr_new.interface_header_attribute1;
3047 -- Bug 5207772. Added by Lakshmi Gopalsami
3048 v_order_type := pr_new.interface_header_attribute2;
3049
3050
3051
3052
3053 IF v_created_from IN ('ARXREC','ARXTWMAI') THEN
3054 RETURN;
3055 END IF;
3056
3057
3058 v_books_id := pr_new.set_of_books_id;
3059 v_salesrep_id := pr_new.primary_salesrep_id ;
3060 v_org_id := pr_new.org_id ;
3061 c_from_currency_code := pr_new.invoice_currency_code ;
3062 c_conversion_type := pr_new.exchange_rate_type;
3063 c_conversion_date := pr_new.exchange_date ;
3064 c_conversion_rate := pr_new.exchange_rate;
3065
3066 /*
3067 || 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
3068 || and causing mutation issue
3069 */
3070 v_org_id := pr_new.org_id;
3071 OPEN jai_ar_trx_pkg.c_tax_regime_code_cur(V_ORG_ID);
3072 FETCH jai_ar_trx_pkg.c_tax_regime_code_cur INTO lv_tax_regime_code;
3073 CLOSE jai_ar_trx_pkg.c_tax_regime_code_cur ;
3074
3075 OPEN jai_ar_trx_pkg.c_max_tax_rate_id_cur(lv_tax_regime_code);
3076 FETCH jai_ar_trx_pkg.c_max_tax_rate_id_cur INTO ln_tax_rate_id;
3077 CLOSE jai_ar_trx_pkg.c_max_tax_rate_id_cur ;
3078
3079
3080 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_books_id ,c_from_currency_code ,
3081 c_conversion_date ,c_conversion_type, c_conversion_rate);
3082
3083
3084
3085 OPEN SO_AR_HDR_INFO ;
3086 FETCH SO_AR_HDR_INFO INTO v_organization_id, v_location_id, v_batch_source_id;
3087 CLOSE SO_AR_HDR_INFO ;
3088
3089
3090
3091 IF v_created_from = 'RAXTRX' THEN
3092 -- Bug 5207772. Added by Lakshmi Gopalsami
3093 OPEN register_code_cur1(v_organization_id, v_location_id, v_order_type);
3094 FETCH register_code_cur1 INTO v_register_code;
3095 CLOSE register_code_cur1;
3096 END IF;
3097 BEGIN
3098 pv_return_code := jai_constants.successful ;
3099 FOR TAX_TYPE_REC IN TAX_TYPE_CUR(v_trx_rec.customer_trx_line_id)
3100 LOOP
3101
3102
3103 IF NVL(v_register_code,'N') IN ('23D_EXPORT_WITHOUT_EXCISE','23D_EXPORT_EXCISE',
3104 '23D_DOMESTIC_EXCISE','23D_DOM_WITHOUT_EXCISE','BOND_REG')
3105 THEN
3106
3107
3108 IF Tax_Type_Rec.T_Type IN ('Excise','Addl. Excise','Other Excise') THEN
3109 TAX_TYPE_REC.tax_amt := 0;
3110 END IF;
3111 END IF;
3112 IF TAX_TYPE_REC.t_type = 'Freight' THEN
3113 v_line_type := 'FREIGHT';
3114 ELSE
3115 v_line_type := 'TAX';
3116 END IF;
3117
3118 /*
3119 Bug 8625057 - Fetched the Interim Liability Code combination ID from Regime Setup
3120 Code Combination ID is fetched from the Tax Codes only if there is no setup at Regime Level
3121 */
3122
3123 ln_regime_id := 0;
3124 ln_regime_code := NULL;
3125
3126 OPEN cur_chk_rgm (cp_tax_type => TAX_TYPE_REC.t_type);
3127 FETCH cur_chk_rgm INTO ln_regime_id, ln_regime_code ;
3128 CLOSE cur_chk_rgm ;
3129
3130 IF UPPER(nvl(ln_regime_code,'####')) = jai_constants.service_regime THEN
3131
3132 /*ER 11821537 - Service Tax must hit Liability instead of Interim Liability due to change in point of taxation to accural from cash*/
3133 -- OPEN c_get_st_accrual_date(ln_regime_id); --Comment by Chong.Lei for POT code port
3134 -- Added by Chong.Lei for POT code port begin
3135 /*Bug 12805386 - Added parameters Inventory Organization ID and Location ID*/
3136 OPEN c_get_st_accrual_date(ln_regime_id, v_organization_id, v_location_id);
3137 -- Added by Chong.Lei for POT code port end
3138 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
3139 CLOSE c_get_st_accrual_date;
3140
3141 -- Added by Chong.Lei for POT code port begin
3142 --Add by Xiao for POT change, reg bug#12533434 on 10-May-2011, begin
3143 ----------------------------------------------------------------------------------------------
3144 OPEN c_get_trx_date(v_customer_trx_line_id);
3145 FETCH c_get_trx_date INTO ld_trx_gl_date;
3146 CLOSE c_get_trx_date;
3147 ----------------------------------------------------------------------------------------------
3148 --Add by Xiao for POT change, reg bug#12533434 on 10-May-2011, end
3149 -- Added by Chong.Lei for POT code port end
3150
3151 -- IF pr_new.trx_date >= ld_st_accrual_date THEN --Comment by Chong.Lei for POT code port
3152 IF ld_trx_gl_date >= ld_st_accrual_date THEN --Modified by Xiao for POT changes, reg bug#12533434 --Added by Chong.Lei for POT code port
3153 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
3154 p_regime_id => ln_regime_id ,
3155 p_organization_type => jai_constants.service_tax_orgn_type ,
3156 p_organization_id => v_organization_id ,
3157 p_location_id => v_location_id ,
3158 p_tax_type => TAX_TYPE_REC.t_type ,
3159 p_account_name => jai_constants.liability
3160 );
3161 ELSE
3162 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
3163 p_regime_id => ln_regime_id ,
3164 p_organization_type => jai_constants.service_tax_orgn_type ,
3165 p_organization_id => v_organization_id ,
3166 p_location_id => v_location_id ,
3167 p_tax_type => TAX_TYPE_REC.t_type ,
3168 p_account_name => jai_constants.liability_interim
3169 );
3170 END IF;
3171 /*ER 11821537 - End*/
3172
3173 IF v_ccid IS NULL THEN
3174 raise_application_error (-20150,'Regime Registration Incomplete. Please check the Service Tax - Tax Accounting Setup');
3175 END IF;
3176
3177
3178 ELSIF UPPER(nvl(ln_regime_code,'####')) = jai_constants.vat_regime THEN
3179
3180 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
3181 p_regime_id => ln_regime_id ,
3182 p_organization_type => jai_constants.orgn_type_io ,
3183 p_organization_id => v_organization_id ,
3184 p_location_id => v_location_id ,
3185 p_tax_type => TAX_TYPE_REC.t_type ,
3186 p_account_name => jai_constants.liability_interim
3187 );
3188 IF v_ccid IS NULL THEN
3189 raise_application_error (-20150,'Regime Registration Incomplete. Please check the VAT Tax - Tax Accounting Setup');
3190 END IF;
3191
3192
3193 ELSIF UPPER(nvl(ln_regime_code,'####')) = jai_constants.tcs_regime THEN
3194
3195 v_ccid := jai_cmn_rgm_recording_pkg.get_account (
3196 p_regime_id => ln_regime_id ,
3197 p_organization_type => jai_constants.orgn_type_io ,
3198 p_organization_id => v_organization_id ,
3199 p_location_id => v_location_id ,
3200 p_tax_type => TAX_TYPE_REC.t_type ,
3201 p_account_name => jai_constants.liability_interim
3202 );
3203 IF v_ccid IS NULL THEN
3204 raise_application_error (-20150,'Regime Registration Incomplete. Please check the TCS Tax - Tax Accounting Setup');
3205 END IF;
3206
3207 ELSE
3208 OPEN tax_ccid_cur(TAX_TYPE_REC.taxid); /*Modified the input parameter from t_type to taxid for Bug#11700083*/
3209 FETCH tax_ccid_cur INTO v_ccid;
3210 CLOSE tax_ccid_cur;
3211 END IF;
3212
3213 /*Bug 8625057 - End*/
3214
3215 IF TAX_TYPE_REC.t_type = 'TDS' THEN
3216 TAX_TYPE_REC.tax_amt := 0;
3217 END IF;
3218
3219
3220 INSERT INTO JAI_AR_TRX_INS_LINES_T ( paddr,
3221 extended_amount,
3222 customer_trx_line_id,
3223 customer_trx_id,
3224 set_of_books_id,
3225 link_to_cust_trx_line_id,
3226 line_type,
3227 uom_code,
3228 vat_tax_id,
3229 acctd_amount,
3230 amount,
3231 CODE_COMBINATION_ID,
3232 cust_trx_line_sales_rep_id,
3233 insert_update_flag,
3234 last_update_date,
3235 last_updated_by,
3236 creation_date,
3237 created_by,
3238 last_update_login,
3239 tax_rate,
3240 error_flag ,
3241 source ,
3242 org_id , -- bug# 3479348
3243 line_number) -- added by sriram bug# 3479348
3244 VALUES ( NULL, /* Previously passing v_paddr. Replaced with NULL by rallamse bug#4448789 */
3245 TAX_TYPE_REC.tax_amt,
3246 TAX_TYPE_REC.LINE_ID,
3247 v_customer_trx_id,
3248 v_books_id,
3249 v_customer_trx_line_id,
3250 v_line_type,
3251 TAX_TYPE_REC.uom,
3252 ln_tax_rate_id, --v_vat_tax, /* Modified by Ramananda for bug#4468353 due to ebtax uptake by AR */
3253 v_converted_rate * TAX_TYPE_REC.tax_amt,
3254 TAX_TYPE_REC.tax_amt,
3255 v_ccid,
3256 v_salesrep_id,
3257 'U',
3258 Sysdate,
3259 UID,
3260 Sysdate,
3261 UID,
3262 UID,
3263 TAX_TYPE_REC.tax_rate,
3264 'P',
3265 v_created_from,
3266 pr_new.org_id, -- added by sriram bug# 3479348
3267 TAX_TYPE_REC.tax_line_no); -- added by sriram bug# 3479348
3268
3269
3270 END LOOP;
3271
3272 EXCEPTION
3273 WHEN OTHERS THEN
3274 v_err_mesg := SUBSTR(SQLERRM,1,240);
3275
3276 /* RAISE_APPLICATION_ERROR(-20004,'error in processing the invoice ..' || v_trx_num || v_err_mesg);
3277 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'error in processing the invoice ..' || v_trx_num || v_err_mesg ; return ;
3278 END ;
3279
3280 END LOOP;
3281 EXCEPTION
3282 WHEN OTHERS THEN
3283 v_err_mesg := SUBSTR(SQLERRM,1,240);
3284
3285 --RAISE_APPLICATION_ERROR(-20003,'exception occured during processing invoice ..' || v_trx_num || v_err_mesg);
3286
3287 /* Added an exception block by Ramananda for bug#4570303 */
3288 Pv_return_code := jai_constants.unexpected_error;
3289 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARU_T4. ' ||
3290 'Exception occured during processing invoice ..' || v_trx_num || v_err_mesg ;
3291
3292 END ARU_T5 ;
3293
3294 /*
3295 REM +======================================================================+
3296 REM NAME ARU_T6
3297 REM
3298 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ARIUD_T1
3299 REM
3300 REM NOTES Refers to old trigger JAI_AR_RCTA_ARU_T9
3301 REM
3302 REM +======================================================================+
3303 */
3304 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
3305
3306 /* --Ramananda for File.Sql.35, start */
3307 v_customer_id Number; -- := pr_new.Ship_To_Customer_ID;
3308 v_org_id Number; -- := NVL(pr_new.Org_ID,0);
3309 v_header_id Number; -- := pr_new.customer_trx_id;
3310 v_ship_to_site_use_id Number; -- := NVL(pr_new.Ship_To_Site_Use_ID,0);
3311 v_created_from Varchar2(30); -- := pr_new.Created_From;
3312 --v_row_id rowid; -- := pr_new.rowid;
3313 v_last_update_date Date; -- := pr_new.last_update_date;
3314 v_last_updated_by Number; -- := pr_new.last_updated_by;
3315 v_creation_date Date; -- := pr_new.creation_date;
3316 v_created_by Number; -- := pr_new.created_by;
3317 v_last_update_login Number; -- := pr_new.last_update_login;
3318 c_from_currency_code Varchar2(15); -- := pr_new.invoice_currency_code;
3319 c_conversion_type Varchar2(30); -- := pr_new.exchange_rate_type;
3320 c_conversion_date Date; -- := NVL(pr_new.exchange_date, pr_new.trx_date);
3321 c_conversion_rate Number; -- := NVL(pr_new.exchange_rate, 0);
3322 v_books_id Number; -- := pr_new.set_of_books_id;
3323 /* --Ramananda for File.Sql.35, end */
3324
3325 v_inventory_item_id Number ;
3326 v_address_id Number ;
3327 v_once_completed_flag Varchar2(1);
3328 v_organization_id Number ;
3329 v_location_id NUMBER;--added by peng.zheng for bug 10043656
3330 lv_enable_gst_flag VARCHAR2(3);--added by peng.zheng for bug 10043656
3331 v_tax_category_id Number ;
3332 v_price_list Number := 0;
3333 v_price_list_uom_code Varchar2(10);
3334 v_conversion_rate Number ;
3335 v_price_list_val Number := 0;
3336 v_converted_rate Number ;
3337 v_line_tax_amount Number := 0;
3338 v_trx_date Date; -- := pr_new.trx_date; --Ramananda for File.Sql.35
3339 v_service_type VARCHAR2(30); --added by ssawant
3340
3341
3342 Cursor address_cur(p_ship_to_site_use_id IN Number) IS
3343 SELECT cust_acct_site_id address_id
3344 FROM hz_cust_site_uses_all A /*Removed ra_site_uses_all for Bug# 4434287*/
3345 WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs */
3346 --WHERE A.site_use_id = NVL(p_ship_to_site_use_id,0);
3347
3348
3349 CURSOR price_list_cur(p_customer_id IN Number,p_inventory_item_id IN Number,
3350 p_address_id IN Number DEFAULT 0, v_uom_code VARCHAR2, p_trx_date DATE) IS
3351 select list_price, unit_code
3352 from so_price_list_lines
3353 where price_list_id in (select price_list_id from JAI_CMN_CUS_ADDRESSES
3354 where customer_id = p_customer_id and
3355 address_id = p_address_id) and
3356 inventory_item_id = p_inventory_item_id
3357 and unit_code = v_uom_code
3358 AND NVL(end_date_active,SYSDATE) >= p_trx_date;
3359
3360 CURSOR ORG_CUR IS
3361 SELECT organization_id, location_id--added by peng.zheng for bug 10043656
3362 FROM JAI_AR_TRX_APPS_RELS_T ;/*altered by rchandan for bug#4479131*/
3363
3364 CURSOR organization_cur IS
3365 SELECT organization_id, location_id--added by peng.zheng for bug 10043656
3366 FROM JAI_AR_TRXS
3367 WHERE trx_number = pr_new.recurred_from_trx_number;
3368
3369 CURSOR ONCE_COMPLETE_FLAG_CUR IS
3370 SELECT once_completed_flag
3371 FROM JAI_AR_TRXS
3372 WHERE customer_trx_id = v_header_id;
3373
3374 v_trans_type Varchar2(30);
3375
3376 Cursor transaction_type_cur IS
3377 Select a.type
3378 From RA_CUST_TRX_TYPES_ALL a
3379 Where a.cust_trx_type_id = pr_new.cust_trx_type_id
3380 And a.org_id = v_org_id; /* Modified by Ramananda for removal of SQL LITERALs */
3381 -- And NVL(a.org_id,0) = v_org_id;
3382
3383 Cursor Ar_Line_Cur IS
3384 Select Customer_Trx_Line_ID, Inventory_Item_ID, Unit_Code, Line_Amount, Quantity,unit_selling_price
3385 From JAI_AR_TRX_LINES
3386 Where Customer_Trx_ID = v_header_id;
3387
3388 /* Bug 5243532. Added by Lakshmi Gopalsami
3389 Removed the reference to set_of_books_cur
3390 which is selecting SOB from org_organization_definitions
3391 as the SOB will never by null in base table.
3392 */
3393 ln_vat_assessable_value JAI_AR_TRX_LINES.VAT_ASSESSABLE_VALUE%TYPE;
3394
3395 ln_gst_assessable_value JAI_AR_TRX_LINES.GST_ASSESSABLE_VALUE%TYPE;
3396
3397 -- Added by sacsethi for bug 5631784 on 30-01-2007
3398 -- START 5631784
3399 LN_TCS_EXISTS NUMBER;
3400 LN_TCS_REGIME_ID JAI_RGM_DEFINITIONS.REGIME_ID%TYPE;
3401 LN_THRESHOLD_SLAB_ID JAI_AP_TDS_THHOLD_SLABS.THRESHOLD_SLAB_ID%TYPE;
3402 LN_THRESHOLD_TAX_CAT_ID JAI_AP_TDS_THHOLD_TAXES.TAX_CATEGORY_ID%TYPE;
3403
3404 CURSOR GC_CHK_RGM_TAX_EXISTS ( CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE
3405 , CP_RGM_TAX_TYPE JAI_CMN_TAXES_ALL.TAX_TYPE%TYPE
3406 , CP_TAX_CATEGORY_ID JAI_CMN_TAX_CTGS_ALL.TAX_CATEGORY_ID%TYPE
3407 )
3408 IS
3409 SELECT COUNT(1)
3410 FROM JAI_CMN_TAX_CTG_LINES CATL
3411 ,JAI_CMN_TAXES_ALL CODES
3412 ,JAI_REGIME_TAX_TYPES_V JRTTV
3413 WHERE CATL.TAX_CATEGORY_ID = CP_TAX_CATEGORY_ID
3414 AND CATL.TAX_ID = CODES.TAX_ID
3415 AND CODES.TAX_TYPE = JRTTV.TAX_TYPE
3416 AND JRTTV.REGIME_CODE = CP_REGIME_CODE;
3417
3418 CURSOR GC_GET_REGIME_ID (CP_REGIME_CODE JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE)
3419 IS
3420 SELECT REGIME_ID
3421 FROM JAI_RGM_DEFINITIONS
3422 WHERE REGIME_CODE = CP_REGIME_CODE;
3423
3424 LV_PROCESS_FLAG VARCHAR2 (2);
3425 LV_PROCESS_MESSAGE VARCHAR2 (1998);
3426
3427 --END 5631784
3428
3429 BEGIN
3430 pv_return_code := jai_constants.successful ;
3431 /*------------------------------------------------------------------------------------------
3432 FILENAME: JA_IN_AR_HDR_UPDATE_TRG.sql
3433
3434 CHANGE HISTORY:
3435 S.No Date Author and Details
3436 1. 10-Aug-2005 Aiyer bug 4545146 version 120.1
3437 Issue:-
3438 Deadlock on tables due to multiple triggers on the same table (in different sql files)
3439 firing in the same phase.
3440 Fix:-
3441 Multiple triggers on the same table have been merged into a single file to resolve
3442 the problem
3443 The following files have been stubbed:-
3444 jai_ar_rcta_t1.sql
3445 jai_ar_rcta_t2.sql
3446 jai_ar_rcta_t3.sql
3447 jai_ar_rcta_t4.sql
3448 jai_ar_rcta_t6.sql
3449 jai_ar_rcta_t7.sql
3450 jai_ar_rcta_t8.sql
3451 jai_ar_rcta_t9.sql
3452 Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers in the above files
3453
3454 2. 31-AUG-2006 SACSETHI FOR BUG 5631784 , 5228046 FILE VERSION 120.4
3455 FORWARD PORTING BUG FROM 11I BUG 4742259
3456 NEW ENH: TAX COLLECTION AT SOURCE IN RECEIVABLES
3457 Changes -
3458
3459 Object Type Object Name Change Description
3460 ---------------------------------------------------------------------------------------------
3461
3462 VARIABLE LN_TCS_EXISTS Add Variable Added
3463 VARIABLE LN_TCS_REGIME_ID Add Variable Added
3464 VARIABLE LN_THRESHOLD_SLAB_ID Add Variable Added
3465 VARIABLE LN_THRESHOLD_TAX_CAT_ID Add Variable Added
3466 CURSOR GC_CHK_RGM_TAX_EXISTS ADD CURSOR FOR GETTING COUNT(1) FROM TAXES
3467 CURSOR GC_GET_REGIME_ID ADD CURSOR FOR GETTING REGIME ID FOR TCS
3468 VARIABLE LV_PROCESS_FLAG ADD VARIABLE LV_PROCESS_FLAG IS PROCESS FLAG
3469 VARIABLE LV_PROCESS_MESSAGE ADD VARIABLE LV_PROCESS_MESSAGE IS PROCESS MESSAGE RETURN BY CALLING OBJECT IN RESPONSE
3470 CURSOR TAX_INFO_CUR MODIFY PRECEDENCE IS ADDED FROM 6 TO 10
3471 SQL STATEMENT JAI_AR_TRX_TAX_LINES MODIFY PRECEDENCE IS ADDED FROM 6 TO 10
3472 3. 27-Feb-2007 CSahoo for Bug 5390583, File Version 120.5 Forward Porting of 11i BUG 5357400
3473 When a change is done in the invoice currency code from the front end
3474 the change is being reflected in the JAI_AR_TRXS table.
3475 Added a IF clause for the same.
3476
3477
3478 4. 14-05-2007 ssawant for bug 5879769, File Version 120.6
3479 Objects was not compiling. so changes are done to make it compiling.
3480 5. 12-10-2007 ssumaith - bug#5597146 - file version 120.16
3481 when there is a change in currency at the invoice header , the excise av
3482 and vat av were calculated wrongly.
3483
3484 Future Dependencies For the release Of this Object:-
3485 (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/
3486 A datamodel change )
3487 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3488 Current Version Current Bug Dependent Files Version Author Date Remarks
3489 Of File On Bug/Patchset Dependent On
3490
3491 ja_in_ar_hdr_update_trg.sql
3492 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3493
3494 6. 21-Mar-2008 Jia for Bug#6859632
3495 Issue: TAX WILL BE ERROR IF SHIP-TO FILED OF AR TRANSACTION IS NOT ENTER AT FIRST.
3496 v_price_list_val didn't multiply quantity;
3497 Parameter is wrong when invoke jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes.
3498 Fixed: 1) v_price_list_val = v_price_list_val * quantity
3499 2) Add a default value for p_operation_flag parameter.
3500
3501 7. 10-Sep-2010 Jia for GST Bug#10043656.
3502 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
3503 /* --Ramananda for File.Sql.35 */
3504 v_customer_id := pr_new.Ship_To_Customer_ID;
3505 v_org_id := NVL(pr_new.Org_ID,0);
3506 v_header_id := pr_new.customer_trx_id;
3507 v_ship_to_site_use_id := NVL(pr_new.Ship_To_Site_Use_ID,0);
3508 v_created_from := pr_new.Created_From;
3509 --v_row_id := pr_new.rowid;
3510 v_last_update_date := pr_new.last_update_date;
3511 v_last_updated_by := pr_new.last_updated_by;
3512 v_creation_date := pr_new.creation_date;
3513 v_created_by := pr_new.created_by;
3514 v_last_update_login := pr_new.last_update_login;
3515 c_from_currency_code := pr_new.invoice_currency_code;
3516 c_conversion_type := pr_new.exchange_rate_type;
3517 c_conversion_date := NVL(pr_new.exchange_date, pr_new.trx_date);
3518 c_conversion_rate := NVL(pr_new.exchange_rate, 0);
3519 v_books_id := pr_new.set_of_books_id;
3520 v_trx_date := pr_new.trx_date;
3521
3522 /* --Ramananda for File.Sql.35 */
3523
3524 OPEN transaction_type_cur;
3525 FETCH transaction_type_cur INTO v_trans_type;
3526 CLOSE transaction_type_cur;
3527 IF NVL(v_trans_type,'N') <> 'INV' THEN
3528 Return;
3529 END IF;
3530
3531 OPEN ONCE_COMPLETE_FLAG_CUR;
3532 FETCH ONCE_COMPLETE_FLAG_CUR INTO v_once_completed_flag;
3533 CLOSE ONCE_COMPLETE_FLAG_CUR;
3534 IF NVL(v_once_completed_flag,'N') = 'Y' THEN
3535 RETURN;
3536 END IF;
3537 IF v_created_from in('RAXTRX','ARXREC') THEN
3538 RETURN;
3539 END IF;
3540 --Following If and update added by CSahoo - bug# 5390583
3541 IF pr_new.invoice_currency_code <> pr_old.invoice_currency_code THEN
3542
3543 UPDATE JAI_AR_TRXS
3544 SET invoice_currency_code = pr_new.invoice_currency_code ,
3545 exchange_rate_type = pr_new.exchange_rate_type ,
3546 exchange_date = pr_new.exchange_date ,
3547 exchange_rate = pr_new.exchange_rate
3548 WHERE customer_trx_id = pr_new.customer_trx_id;
3549
3550 END IF;
3551
3552 OPEN ORG_CUR;
3553 FETCH ORG_CUR INTO v_organization_id, v_location_id;--added by peng.zheng for bug 10043656
3554 CLOSE ORG_CUR;
3555 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
3556 -- which was causing code to return .- bug # 2846277
3557 OPEN organization_cur;
3558 FETCH organization_cur INTO v_organization_id, v_location_id;--added by peng.zheng for bug 10043656
3559 CLOSE organization_cur;
3560 END IF;
3561 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
3562 -- which was causing code to return .- bug # 2846277
3563 RETURN;
3564 END IF;
3565 OPEN address_cur(v_ship_to_site_use_id);
3566 FETCH address_cur INTO v_address_id;
3567 CLOSE address_cur;
3568
3569 FOR rec In Ar_Line_Cur
3570 LOOP
3571 v_tax_category_id := '';
3572 v_price_list := '';
3573 v_price_list_uom_code := '';
3574 v_conversion_rate := '';
3575 v_price_list_val := '';
3576 v_converted_rate := '';
3577 v_line_tax_amount := 0;
3578
3579 DELETE JAI_AR_TRX_TAX_LINES
3580 WHERE LINK_TO_CUST_TRX_LINE_ID = Rec.CUSTOMER_TRX_LINE_ID;
3581
3582 --added by peng.zheng for bug 10043656, begins
3583 -- lv_enable_gst_flag := JAI_GST_GENERAL_PKG.IS_GST_ENABLED;
3584 IF nvl(lv_enable_gst_flag,'N') = 'N' THEN
3585 --leave the original logic, by peng.zheng for bug 10043656
3586 IF v_customer_id IS NOT NULL AND v_address_id IS NOT NULL
3587 THEN
3588 jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes(v_organization_id , v_customer_id ,v_ship_to_site_use_id ,
3589 rec.inventory_item_id ,v_header_id , rec.customer_trx_line_id,
3590 v_tax_category_id );
3591 ELSE
3592 jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes(v_organization_id , rec.inventory_item_id , v_tax_category_id );
3593
3594 END IF;
3595
3596 /*ELSE
3597 --new gst logic, by peng.zheng for bug 10043656
3598 IF v_customer_id IS NOT NULL
3599 THEN
3600 jai_gst_tax_defaultation_pkg.jai_gst_cust_default_taxes(
3601 pn_organization_id => v_organization_id ,
3602 pn_location_id => v_location_id,
3603 pn_customer_id => v_customer_id,
3604 pn_ship_to_site_use_id => v_ship_to_site_use_id ,
3605 pn_inventory_item_id => rec.inventory_item_id ,
3606 pd_transaction_date => pr_new.trx_date,
3607 pn_tax_category_id => v_tax_category_id
3608 );
3609 END IF;
3610 */
3611 END IF;
3612 --added by peng.zheng for bug 10043656, ends
3613
3614 IF v_tax_category_id IS NOT NULL
3615 THEN
3616 OPEN price_list_cur(v_customer_id , rec.inventory_item_id, v_address_id,rec.unit_code, v_trx_date);
3617 FETCH price_list_cur INTO v_price_list, v_price_list_uom_code;
3618 CLOSE price_list_cur;
3619 IF v_price_list IS NULL
3620 THEN
3621 OPEN price_list_cur(v_customer_id ,rec.inventory_item_id, 0, rec.unit_code, v_trx_date);
3622 FETCH price_list_cur INTO v_price_list, v_price_list_uom_code;
3623 CLOSE price_list_cur;
3624 END IF;
3625 /*
3626 Added by ssumaith - 4245053
3627 */
3628 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value
3629 (
3630 p_party_id => v_customer_id ,
3631 p_party_site_id => v_ship_to_site_use_id ,
3632 p_inventory_item_id => rec.inventory_item_id ,
3633 p_uom_code => rec.unit_code ,
3634 p_default_price => nvl(rec.unit_selling_price,0) , /*ssumaith - bug#5597146 */
3635 p_ass_value_date => pr_new.trx_date ,
3636 p_party_type => 'C'
3637 );
3638
3639
3640 ln_vat_assessable_value := NVL(ln_vat_assessable_value,0) * rec.quantity;
3641
3642
3643 -- Added by Jia for GST Bug#10043656 on 2010/09/10, Begin
3644 -----------------------------------------------------------------------------
3645 /*
3646 ln_gst_assessable_value := jai_gst_general_pkg.get_gst_assessable_value
3647 (
3648 p_party_id => v_customer_id ,
3649 p_party_site_id => v_ship_to_site_use_id ,
3650 p_inventory_item_id => rec.inventory_item_id ,
3651 p_uom_code => rec.unit_code ,
3652 p_default_price => nvl(rec.unit_selling_price,0) ,
3653 p_ass_value_date => pr_new.trx_date ,
3654 p_party_type => 'C'
3655 );
3656
3657
3658 ln_gst_assessable_value := NVL(ln_gst_assessable_value,0) * rec.quantity;
3659 -----------------------------------------------------------------------------
3660 -- Added by Jia for GST Bug#10043656 on 2010/09/10, End
3661 */
3662 v_line_tax_amount := nvl(rec.line_amount,0);
3663 IF NVL(v_price_list,0) > 0 THEN
3664 IF v_price_list_uom_code IS NOT NULL THEN
3665 INV_CONVERT.inv_um_conversion(rec.unit_code, v_price_list_uom_code, rec.inventory_item_id,v_conversion_rate);
3666 IF nvl(v_conversion_rate, 0) <= 0 THEN
3667 INV_CONVERT.inv_um_conversion(rec.unit_code, v_price_list_uom_code, 0,v_conversion_rate);
3668 IF nvl(v_conversion_rate, 0) <= 0 THEN
3669 v_conversion_rate := 0;
3670 END IF;
3671 END IF;
3672 END IF;
3673 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_books_id ,c_from_currency_code ,
3674 c_conversion_date ,c_conversion_type, c_conversion_rate);
3675 v_price_list := NVL(1/v_converted_rate,0) * nvl(v_price_list,0) * v_conversion_rate;
3676 v_price_list_val := nvl(rec.quantity * v_price_list,0);
3677 ELSE
3678 v_price_list := rec.unit_selling_price; /*ssumaith - bug#5597146 */
3679 --v_price_list_val := rec.unit_selling_price; /*ssumaith - bug#5597146 */
3680 v_price_list_val := rec.unit_selling_price * rec.quantity ; -- Modified by Jia for Bug#6859632
3681 END IF;
3682
3683 /*
3684 ln_vat_assessable_value added by ssumaith - 4245053 in the following call.
3685 */
3686
3687 ---------------------------------------------------------------------------------------------------------
3688 /** sacseth, bug# 5631784 - TCS enhancement */
3689 /** Check if TCS type of taxes exists for v_tax_category_id */
3690
3691 OPEN GC_CHK_RGM_TAX_EXISTS
3692 ( CP_REGIME_CODE => JAI_CONSTANTS.TCS_REGIME
3693 , CP_RGM_TAX_TYPE => JAI_CONSTANTS.TAX_TYPE_TCS
3694 , CP_TAX_CATEGORY_ID => V_TAX_CATEGORY_ID
3695 );
3696 FETCH GC_CHK_RGM_TAX_EXISTS INTO LN_TCS_EXISTS;
3697 CLOSE GC_CHK_RGM_TAX_EXISTS;
3698
3699 IF LN_TCS_EXISTS IS NOT NULL THEN
3700 /** TCS type of tax(s) are present */
3701 OPEN GC_GET_REGIME_ID ( CP_REGIME_CODE => JAI_CONSTANTS.TCS_REGIME);
3702 FETCH GC_GET_REGIME_ID INTO LN_TCS_REGIME_ID;
3703 CLOSE GC_GET_REGIME_ID;
3704
3705 /** Check current threshold slab. The following procedure returns null threshold_slab_id if threshold is not yet reached */
3706 jai_rgm_thhold_proc_pkg.get_threshold_slab_id
3707 ( p_regime_id => ln_tcs_regime_id
3708 , p_organization_id => v_organization_id
3709 , p_party_type => jai_constants.party_type_customer
3710 , p_party_id => v_customer_id
3711 , p_org_id => v_org_id
3712 , p_source_trx_date => v_trx_date
3713 , p_threshold_slab_id => ln_threshold_slab_id
3714 , p_process_flag => lv_process_flag
3715 , p_process_message => lv_process_message
3716 );
3717 if lv_process_flag <> jai_constants.successful then
3718 app_exception.raise_exception
3719 (exception_type => 'APP'
3720 ,exception_code => -20275
3721 ,exception_text => lv_process_message
3722 );
3723 end if;
3724
3725 if ln_threshold_slab_id is not null then
3726 /**
3727 Threshold is high and slab is available. Hence get tax_category defined for the salb to default additional taxes
3728 */
3729 jai_rgm_thhold_proc_pkg.get_threshold_tax_cat_id
3730 (
3731 p_threshold_slab_id => ln_threshold_slab_id
3732 , p_org_id => v_org_id
3733 , p_threshold_tax_cat_id => ln_threshold_tax_cat_id
3734 , p_process_flag => lv_process_flag
3735 , p_process_message => lv_process_message
3736 );
3737 if lv_process_flag <> jai_constants.successful then
3738 app_exception.raise_exception
3739 (exception_type => 'APP'
3740 ,exception_code => -20275
3741 ,exception_text => lv_process_message
3742 );
3743 end if;
3744 end if; /** ln_threshold_slab_id is not null */
3745 end if; /** ln_tcs_exists is not null */
3746 ---------------------------------------------------------------------------------------------------------
3747 jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes('AR_LINES' , v_tax_category_id , v_header_id, rec.customer_trx_line_id,
3748 v_price_list_val , v_line_tax_amount ,rec.inventory_item_id , NVL(rec.quantity,0),
3749 rec.unit_code , NULL , NULL , v_converted_rate ,v_creation_date , v_created_by ,
3750 v_last_update_date , v_last_updated_by , v_last_update_login
3751 , null --Add a default value by Jia for Bug#6859632
3752 , ln_vat_assessable_value
3753 -- Bug 6109941, Added by brathod for fwd porting bug 4742259
3754 , p_thhold_cat_base_tax_typ => jai_constants.tax_type_tcs
3755 , p_threshold_tax_cat_id => ln_threshold_tax_cat_id
3756 , p_source_trx_type => null
3757 , p_source_table_name => null
3758 , p_action => jai_constants.default_taxes
3759 -- , pn_gst_assessable_value => ln_gst_assessable_value -- Added by Jia for GST Bug#10043656 on 2010/09/10
3760
3761 -- End 6109941
3762 );
3763
3764 END IF;
3765
3766 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
3767 UPDATE JAI_AR_TRX_LINES
3768 SET tax_category_id = v_tax_category_id,
3769 service_type_code = v_service_type, --added by csahoo for Bug#5879769
3770 assessable_value = nvl(v_price_list,0),
3771 vat_assessable_value = ln_vat_assessable_value,
3772 -- gst_assessable_value = ln_gst_assessable_value, -- Added by Jia for GST Bug#10043656 on 2010/09/10
3773 tax_amount = v_line_tax_amount,
3774 total_amount = nvl(rec.line_amount,0) + v_line_tax_amount,
3775 last_update_date = v_last_update_date,
3776 last_updated_by = v_last_updated_by,
3777 last_update_login = v_last_update_login
3778 WHERE Customer_Trx_Line_ID = rec.customer_trx_line_id;
3779
3780 END LOOP;
3781 /* Added an exception block by Ramananda for bug#4570303 */
3782 EXCEPTION
3783 WHEN OTHERS THEN
3784 Pv_return_code := jai_constants.unexpected_error;
3785 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARI_T7 ' || substr(sqlerrm,1,1900);
3786
3787 END ARU_T6 ;
3788 /*
3789 REM +======================================================================+
3790 REM NAME ARU_T7
3791 REM
3792 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ARIUD_T1
3793 REM
3794 REM NOTES Refers to old trigger JAI_AR_RCTA_ARU_T9
3795 REM
3796 REM
3797 REM CHANGE HISTORY:
3798 REM S.No Date Author and Details
3799 REM 1. 30/01/2007 SACSETHI FOR BUG 5631784
3800 REM PROCEDURE ARU_T7 IS NEWELY CREATED FOR PROVIDING TCS FUNCTIONALITY
3801 REM
3802 REM 2. 10/02/2011 Added by Zhiwei/Xiao Lv for Open Interface ER bug#11683927
3803 REM Add code to prevent process TCS Accounting for 'External' source for OFI Open Interface.
3804 REM +======================================================================+
3805 */
3806 PROCEDURE ARU_T7
3807 ( 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 )
3808 IS
3809 LV_DOCUMENT_TYPE VARCHAR2(40);
3810 LN_REG_ID NUMBER;
3811 LV_ONCE_COMPLETED_FLAG JAI_AR_TRXS.ONCE_COMPLETED_FLAG%TYPE;
3812 V_HEADER_ID NUMBER;
3813
3814
3815 CURSOR ONCE_COMPLETE_FLAG_CUR IS
3816 SELECT ONCE_COMPLETED_FLAG
3817 FROM JAI_AR_TRXS
3818 WHERE CUSTOMER_TRX_ID = V_HEADER_ID;
3819
3820
3821 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, begin
3822 ------------------------------------------------------------------------------
3823 CURSOR get_external_flag_cur IS
3824 SELECT COUNT(lines.customer_trx_id)
3825 FROM jai_ar_trx_lines lines,
3826 jai_interface_lines_all intfs
3827 WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
3828 AND lines.customer_trx_id = v_header_id
3829 AND lines.interface_flag = 'Y'
3830 AND intfs.taxable_event = 'EXTERNAL';
3831
3832 CURSOR get_open_source_cur IS
3833 SELECT COUNT(interface_flag)
3834 FROM jai_ar_trx_lines
3835 WHERE customer_trx_id = v_header_id
3836 AND interface_flag = 'Y';
3837
3838 ln_open_source NUMBER;
3839 ln_external_flag NUMBER;
3840 ------------------------------------------------------------------------------
3841 --Added by Xiao for Open Interface ER bug#11683927 on 10-Feb-2011, end
3842
3843 BEGIN
3844
3845 V_HEADER_ID := PR_NEW.CUSTOMER_TRX_ID;
3846 IF NVL(PR_NEW.COMPLETE_FLAG,JAI_CONSTANTS.NO) = JAI_CONSTANTS.YES THEN
3847 /** Invoice is getting COMPLETED */
3848 LV_DOCUMENT_TYPE := JAI_CONSTANTS.TRX_TYPE_INV_COMP;
3849 /*********
3850 || When the invoice is getting completed for the very first time (once_complete_flag is still null or 'N') then pass the
3851 || final TCS accounting for the TCS type of taxes belonging to the manual invoice only
3852 || This is not applicable for the imported invoices.
3853 *********/
3854
3855 OPEN ONCE_COMPLETE_FLAG_CUR;
3856 FETCH ONCE_COMPLETE_FLAG_CUR INTO LV_ONCE_COMPLETED_FLAG;
3857 CLOSE ONCE_COMPLETE_FLAG_CUR;
3858
3859 IF Pr_new.created_from <> 'RAXTRX' AND
3860 lv_once_completed_flag = jai_constants.yes
3861 /*Bug 8463839 - Accounting must be done to set off Interim Liability
3862 Account when Transaction is completed*/
3863 THEN
3864 -- jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Calling -> jai_ar_tcs_rep_pkg.ar_accounting ');
3865 JAI_AR_TCS_REP_PKG.AR_ACCOUNTING ( P_RACT => PR_NEW ,
3866 P_PROCESS_FLAG => PV_RETURN_CODE ,
3867 P_PROCESS_MESSAGE => PV_RETURN_MESSAGE
3868 );
3869 -- JAI_CMN_DEBUG_CONTEXTS_PKG.PRINT ( PN_REG_ID => LN_REG_ID ,
3870 -- PV_LOG_MSG => 'RETURNED FROM JAI_AR_TCS_REP_PKG.AR_ACCOUNTING ' || CHR(10)
3871 -- ||'P_PROCESS_FLAG=' ||PV_ERR_FLG
3872 -- );
3873 -- IF PV_ERR_FLG <> JAI_CONSTANTS.SUCCESSFUL THEN
3874 -- jai_cmn_debug_contexts_pkg.print ( pn_reg_id => ln_reg_id ,
3875 -- pv_log_msg => 'Error during processing of jai_ar_tcs_rep_pkg.ar_accounting '||chr(10)
3876 -- ||'p_process_flag=' ||pv_err_flg||chr(10)
3877 -- ||'p_process_message='||pv_err_msg
3878 -- );
3879 --
3880 -- return;
3881 -- END IF;
3882 END IF;
3883
3884
3885 --Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, begin
3886 ------------------------------------------------------------------------------------
3887 --Process TCS Accounting for OFI Open Interface.
3888
3889 IF Pr_new.created_from = 'RAXTRX' AND lv_once_completed_flag = jai_constants.yes
3890 THEN
3891 OPEN get_open_source_cur;
3892 FETCH get_open_source_cur INTO ln_open_source;
3893 CLOSE get_open_source_cur;
3894
3895 IF ln_open_source > 0 THEN
3896 --IF 1<>1 THEN
3897 OPEN get_external_flag_cur;
3898 FETCH get_external_flag_cur INTO ln_external_flag;
3899 CLOSE get_external_flag_cur;
3900
3901 IF ln_external_flag > 0 THEN
3902 NULL;
3903 ELSE
3904
3905 JAI_AR_TCS_REP_PKG.AR_ACCOUNTING(P_RACT => PR_NEW
3906 , P_PROCESS_FLAG => PV_RETURN_CODE
3907 , P_PROCESS_MESSAGE => PV_RETURN_MESSAGE);
3908 END IF;
3909 END IF;
3910 END IF;
3911
3912
3913 -----------------------------------------------------------------------------------
3914 -- Added by Zhiwei for Open Interface ER bug#11683927 on 10-Feb-2011, end
3915
3916 ELSIF NVL(PR_NEW.COMPLETE_FLAG,JAI_CONSTANTS.NO) = JAI_CONSTANTS.NO THEN
3917 /** INVOICE IS GETTING INCOMPLETED */
3918 LV_DOCUMENT_TYPE := JAI_CONSTANTS.TRX_TYPE_INV_INCOMP;
3919 END IF;
3920
3921 --Added by zhiwei for Open Interface ER bug#11683927 begin
3922 ----------------------------------------------------------
3923 OPEN get_external_flag_cur;
3924 FETCH get_external_flag_cur INTO ln_external_flag;
3925 CLOSE get_external_flag_cur;
3926
3927 IF ln_external_flag > 0 THEN
3928 NULL;
3929 ELSE
3930 --------------------------------------------------------------
3931 --Added by zhiwei for Open Interface ER bug#11683927 end
3932
3933 -- jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Calling -> JAI_AR_TCS_REP_PKG.PROCESS_TRANSACTIONS');
3934 JAI_AR_TCS_REP_PKG.PROCESS_TRANSACTIONS
3935 ( P_RACT => PR_NEW
3936 , P_EVENT => JAI_CONSTANTS.TRX_EVENT_COMPLETION
3937 , P_PROCESS_FLAG => PV_RETURN_CODE
3938 , P_PROCESS_MESSAGE => PV_RETURN_MESSAGE
3939 );
3940 END IF;--Added by zhiwei for Open Interface ER bug#11683927
3941 -- jai_cmn_debug_contexts_pkg.print (ln_reg_id
3942 -- , 'Process Result: ' || chr(10)
3943 -- ||'p_process_flag=' ||PV_RETURN_CODE||chr(10)
3944 -- ||'p_process_message='||PV_RETURN_MESSAGE||chr(10)
3945 -- );
3946 IF PV_RETURN_CODE <> JAI_CONSTANTS.SUCCESSFUL THEN
3947 RETURN;
3948 END IF;
3949
3950 END ARU_T7;
3951
3952 /*
3953 REM +======================================================================+
3954 REM NAME ARU_T8
3955 REM
3956 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ARIUD_T1 for deal with
3957 REM RMA credit only
3958 REM
3959 REM HISTORY Created by Bo Li for bug9666476
3960 REM
3961 REM 18-MAY-2010 Modified by Bo Li for Bug9706176
3962 REM Change the cursor logic of check_rma_credit_cur
3963 REM Added the new cursor get_order_and_item_id_cur and
3964 REM check_shippable_item_cur
3965 REM
3966 REM 03-JUN-2010 Modified by Bo Li for Bug9759668
3967 REM Get the VAT invoice number from the source SO and insert the source
3968 REM VAT invoice number to REPOSITORY table when pre_customer_trx_id is null.
3969 REM In functoinality,the issue happens when the Source SO and RMA SO
3970 REM are imported into AR together.
3971 REM
3972 REM 10-Jun-2010 Modified by Allen Yang for bug 9793678
3973 REM Commented code which populates VAT invoice number on
3974 REM JAI_AR_TRXS for non-shippable RMA.
3975 REM
3976 REM 09-Jul-2010 Modified by Bo Li for Bug 9765108
3977 REM Change the procedure ARU_T8, modified the logic of inserting
3978 REM repository table for nonshippable RMA.
3979 REM +======================================================================+
3980 */
3981 PROCEDURE ARU_T8 ( 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
3982 v_organization_id NUMBER ;
3983 v_loc_id NUMBER ;
3984 v_trans_type RA_CUST_TRX_TYPES_ALL.TYPE%TYPE ;
3985 lv_vat_invoice_no JAI_AR_TRXS.VAT_INVOICE_NO%TYPE ;
3986 ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
3987 ln_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE ;
3988 lv_process_flag VARCHAR2(10) ;
3989 lv_process_message VARCHAR2(4000) ;
3990 ld_gl_date RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE%TYPE ;
3991 ld_vat_invoice_date JAI_AR_TRXS.VAT_INVOICE_DATE%TYPE ;
3992
3993 ln_rma_flag NUMBER;
3994 ln_order_line_id NUMBER;
3995 ln_nonship_rma_flag NUMBER;
3996
3997 /*
3998 || Get the order line id for customer trx line
3999 */
4000 CURSOR get_order_and_item_id_cur
4001 IS
4002 SELECT interface_line_attribute6 order_line_id
4003 ,inventory_item_id
4004 ,customer_trx_line_id --Added By Bo Li for bug#9765108 on 09-JUL-2010
4005 FROM ra_customer_trx_lines_all
4006 WHERE customer_trx_id = pr_new.customer_trx_id;
4007
4008 /*
4009 || Check the trasaction is from RMA credit only
4010 */
4011 CURSOR check_rma_credit_cur(pn_order_line_id NUMBER)
4012 IS
4013 SELECT count(1)
4014 FROM OE_ORDER_HEADERS_ALL oh,
4015 OE_ORDER_LINES_ALL ol,
4016 OE_TRANSACTION_TYPES_TL ot,
4017 oe_workflow_assignments owf
4018 WHERE oh.header_id = ol.header_id
4019 AND oh.order_type_id = ot.transaction_type_id
4020 AND oh.order_type_id = owf.order_type_id
4021 AND ol.line_type_id = owf.line_type_id
4022 AND oh.order_number = pr_new.interface_header_attribute1
4023 AND ot.language = userenv('LANG')
4024 AND ol.line_id = pn_order_line_id
4025 AND owf.process_name IN ('R_RMA_CREDIT_APP_HDR_INV',
4026 'R_RMA_CREDIT_WO_SHIP_APPROVE',
4027 'R_RMA_CREDIT_WO_SHIP_HDR_INV',
4028 'R_RMA_FOR_CREDIT_WO_SHIPMENT',
4029 'R_RMA_FOR_OTA_CREDIT');
4030
4031 /*
4032 || Check the item type shippable or non-shippable
4033 */
4034 CURSOR check_shippable_item_cur(pn_inventory_item_id NUMBER,pn_order_line_id NUMBER)
4035 IS
4036 SELECT COUNT(1)
4037 FROM MTL_SYSTEM_ITEMS msi,
4038 JAI_OM_OE_RMA_LINES l
4039 WHERE msi.inventory_item_id = pn_inventory_item_id
4040 AND msi.inventory_item_id = l.inventory_item_id
4041 AND l.rma_line_id = pn_order_line_id
4042 AND msi.shippable_item_flag = 'N' ;
4043
4044 /*
4045 || Get the organization, location, vat_invoice_no and vat_invoice_date from JAI_AR_TRXS
4046 */
4047 CURSOR organization_cur
4048 IS
4049 SELECT organization_id ,
4050 location_id ,
4051 vat_invoice_no ,
4052 vat_invoice_date
4053 FROM JAI_AR_TRXS
4054 WHERE customer_trx_id = pr_new.customer_trx_id;
4055
4056 /*
4057 || Get the transaction type of the document
4058 */
4059 CURSOR transaction_type_cur
4060 IS
4061 SELECT type
4062 FROM ra_cust_trx_types_all
4063 WHERE cust_trx_type_id = pr_new.cust_trx_type_id AND
4064 NVL(org_id,0) = NVL(pr_new.org_id,0);
4065
4066
4067 /*
4068 || Check whether vat types of taxes exist for the CM.
4069 || IF yes then get the regime id and regime code
4070 */
4071 CURSOR cur_vat_taxes_exist
4072 IS
4073 SELECT regime_id ,
4074 regime_code
4075 FROM
4076 JAI_AR_TRX_TAX_LINES jcttl,
4077 JAI_AR_TRX_LINES jctl,
4078 JAI_CMN_TAXES_ALL jtc ,
4079 jai_regime_tax_types_v jrttv
4080 WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
4081 AND jctl.customer_trx_id = pr_new.customer_trx_id
4082 AND jcttl.tax_id = jtc.tax_id
4083 AND jtc.tax_type = jrttv.tax_type
4084 AND regime_code = jai_constants.vat_regime
4085 AND jtc.org_id = pr_new.org_id ;
4086
4087
4088 CURSOR cur_get_gl_date(cp_acct_class ra_cust_trx_line_gl_dist_all.account_class%type)
4089 IS
4090 SELECT gl_date
4091 FROM ra_cust_trx_line_gl_dist_all
4092 WHERE customer_trx_id = pr_new.customer_trx_id
4093 AND account_class = cp_acct_class
4094 AND latest_rec_flag = 'Y';
4095
4096 CURSOR cur_get_in_vat_no
4097 IS
4098 SELECT vat_invoice_no
4099 FROM JAI_AR_TRXS
4100 WHERE customer_trx_id = pr_new.previous_customer_trx_id;
4101
4102 /*
4103 || Check if only 'VAT REVERSAL' tax type is present in ja_in_ra_cust_trx_tax_lines
4104 */
4105 CURSOR c_chk_vat_reversal (cp_tax_type jai_cmn_taxes_all.tax_type%TYPE )
4106 IS
4107 SELECT 1
4108 FROM JAI_AR_TRX_TAX_LINES jcttl,
4109 JAI_AR_TRX_LINES jctl,
4110 JAI_CMN_TAXES_ALL jtc
4111 WHERE jcttl.link_to_cust_trx_line_id = jctl.customer_trx_line_id
4112 AND jctl.customer_trx_id = pr_new.customer_trx_id
4113 AND jcttl.tax_id = jtc.tax_id
4114 AND jtc.org_id = pr_new.org_id
4115 AND jtc.tax_type = cp_tax_type ;
4116
4117 lv_vat_reversal VARCHAR2(30);
4118 ln_vat_reversal_exists NUMBER;
4119
4120 /*
4121 || Retrieve the regime_id which is of regime code 'VAT'
4122 */
4123 CURSOR c_get_regime_id
4124 IS
4125 SELECT regime_id
4126 FROM jai_regime_tax_types_v
4127 WHERE regime_code = jai_constants.vat_regime
4128 AND rownum = 1 ;
4129
4130 --Added by Bo Li for Bug9759668 on 2010-6-2 Begin
4131 -------------------------------------------------------------
4132 CURSOR get_copy_vat_invoice_cur
4133 IS
4134 SELECT jwl.vat_invoice_no
4135 FROM OE_ORDER_HEADERS_ALL ohc,
4136 oe_transaction_types_tl ot,
4137 JAI_OM_WSH_LINES_ALL jwl
4138 WHERE ohc.order_type_id = ot.transaction_type_id
4139 AND ot.LANGUAGE = userenv('LANG')
4140 AND ohc.source_document_id = jwl.ORDER_HEADER_ID
4141 AND ohc.ORDER_NUMBER = pr_new.INTERFACE_HEADER_ATTRIBUTE1
4142 AND ot.NAME = pr_new.INTERFACE_HEADER_ATTRIBUTE2;
4143
4144 -------------------------------------------------------------
4145 --Added by Bo Li for Bug9759668 on 2010-6-2 End
4146
4147 --Added by Bo Li for Bug#9765108 on 09-JUL-2010 Begin
4148 -------------------------------------------------------------
4149 CURSOR cur_get_man_ar_inv_taxes
4150 ( cp_source IN jai_rgm_trx_records.source%TYPE,
4151 cp_source_trx_type IN jai_rgm_trx_records.source_trx_type%TYPE,
4152 cp_source_table_name IN jai_rgm_trx_records.source_table_name%TYPE,
4153 cp_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%TYPE,
4154 cp_organization_id IN JAI_AR_TRXS.organization_id%TYPE,
4155 cp_loc_id IN JAI_AR_TRXS.location_id%TYPE
4156 )
4157 IS
4158 SELECT
4159 jctl.customer_trx_id ,
4160 jctl.vat_assessable_value ,
4161 nvl(jctl.vat_exemption_flag,'N') vat_exemption_flag ,
4162 jcttl.customer_trx_line_id ,
4163 jcttl.tax_id ,
4164 jcttl.link_to_cust_trx_line_id ,
4165 jcttl.func_tax_amount ,
4166 jcttl.creation_date ,
4167 jtc.tax_type ,
4168 jcttl.tax_rate
4169 FROM
4170 JAI_AR_TRX_LINES jctl ,
4171 JAI_AR_TRX_TAX_LINES jcttl ,
4172 JAI_CMN_TAXES_ALL jtc ,
4173 (
4174 SELECT jrttv1.tax_type tax_type
4175 FROM jai_regime_tax_types_v jrttv1
4176 WHERE jrttv1.regime_code = jai_constants.vat_regime
4177 UNION
4178 SELECT 'VAT REVERSAL' tax_type
4179 FROM DUAL
4180 ) jrttv
4181 WHERE
4182 jctl.customer_trx_id = pr_new.customer_trx_id AND
4183 jctl.customer_trx_line_id = jcttl.link_to_cust_trx_line_id AND
4184 jctl.customer_trx_line_id = cp_customer_trx_line_id AND
4185 jcttl.tax_id = jtc.tax_id AND
4186 jtc.tax_type = jrttv.tax_type AND
4187 NOT EXISTS ( SELECT
4188 1
4189 FROM
4190 jai_rgm_trx_records jrtr
4191 WHERE
4192 jrtr.source = cp_source AND
4193 jrtr.source_trx_type = cp_source_trx_type AND
4194 jrtr.organization_id = cp_organization_id AND
4195 jrtr.location_id = cp_loc_id AND
4196 jrtr.source_table_name = cp_source_table_name AND
4197 jrtr.trx_reference1 = pr_new.customer_trx_id AND
4198 jrtr.source_document_id = jcttl.customer_trx_line_id AND
4199 jrtr.reference_id = jcttl.tax_id
4200 ) ;
4201
4202 ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
4203 ln_liab_acct_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
4204 ln_intliab_acct_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
4205 ln_charge_ac_id GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
4206 ln_balancing_ac_id GL_CODE_COMBINATIONS.code_combination_id%TYPE ;
4207 ln_debit_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
4208 ln_credit_amount JAI_RGM_TRX_RECORDS.CREDIT_AMOUNT%TYPE ;
4209 lc_account_name VARCHAR2(50);
4210
4211 ----------------------------------------------------------------------------------------------------
4212 --Added by Bo Li for Bug#9765108 on 09-JUL-2010 Begin
4213
4214
4215 BEGIN
4216 pv_return_code := jai_constants.successful ;
4217
4218 /*
4219 || Get the Otransaction type of the document
4220 || Process only CM type of transaction's
4221 */
4222 OPEN transaction_type_cur;
4223 FETCH transaction_type_cur INTO v_trans_type;
4224 CLOSE transaction_type_cur;
4225
4226 IF NVL(v_trans_type,'N') <> 'CM'
4227 OR pr_new.created_from <> 'RAXTRX' THEN
4228 /*
4229 || In case of CM only VAT accouting should be done.
4230 */
4231 RETURN;
4232 END IF;
4233
4234 /*
4235 || Get the Organization and location info , vat_invoice_no, vat_invoice_date
4236 */
4237 OPEN organization_cur;
4238 FETCH organization_cur
4239 INTO v_organization_id
4240 ,v_loc_id
4241 ,lv_vat_invoice_no
4242 ,ld_vat_invoice_date ;
4243 CLOSE organization_cur;
4244
4245 IF lv_vat_invoice_no IS NOT NULL OR
4246 ld_vat_invoice_date IS NOT NULL
4247 THEN
4248 /*
4249 || IF vat_invoice_no or vat_invoice_date has already been populated into this record (indicating that it has already been run once)
4250 || then return.
4251 */
4252 RETURN;
4253 END IF;
4254
4255
4256 OPEN cur_vat_taxes_exist;
4257 FETCH cur_vat_taxes_exist into ln_regime_id,ln_regime_code;
4258 CLOSE cur_vat_taxes_exist;
4259
4260 IF upper(nvl(ln_regime_code,'####')) <> UPPER(jai_constants.vat_regime) THEN
4261 /*
4262 || only vat type of taxes should be processed
4263 */
4264 RETURN;
4265 END IF;
4266 /*
4267 || Check if only 'VAT REVERSAL' tax type is present in ja_in_ra_cust_trx_tax_lines
4268 */
4269 IF ln_regime_id IS NULL THEN
4270 lv_vat_reversal := 'VAT REVERSAL' ;
4271 OPEN c_chk_vat_reversal(lv_vat_reversal) ;
4272 FETCH c_chk_vat_reversal INTO ln_vat_reversal_exists;
4273 CLOSE c_chk_vat_reversal ;
4274
4275 /*
4276 || Retrieve the regime_id for 'VAT REVERSAL' tax type, which is of regime code 'VAT'
4277 */
4278 IF ln_vat_reversal_exists = 1 THEN
4279 OPEN c_get_regime_id ;
4280 FETCH c_get_regime_id
4281 INTO ln_regime_id ;
4282 CLOSE c_get_regime_id ;
4283
4284 IF ln_regime_id IS NOT NULL THEN
4285 ln_regime_code := jai_constants.vat_regime ;
4286 END IF ;
4287 END IF ;
4288 END IF ;
4289
4290 /*
4291 || Get the vat invoice number for the Credit Memo from the Source Invoice only if a CM has a source INvoice
4292 || IF it is from legacy then the vat invoice number would go as null
4293 */
4294 IF pr_new.previous_customer_trx_id is NOT NULL THEN
4295 OPEN cur_get_in_vat_no;
4296 FETCH cur_get_in_vat_no
4297 INTO lv_vat_invoice_no;
4298 CLOSE cur_get_in_vat_no ;
4299 --Added by Bo Li for Bug9759668 on 2010-6-2 Begin
4300 ---------------------------------------------------
4301 ELSE
4302 OPEN get_copy_vat_invoice_cur;
4303 FETCH get_copy_vat_invoice_cur
4304 INTO lv_vat_invoice_no;
4305 CLOSE get_copy_vat_invoice_cur ;
4306 ---------------------------------------------------
4307 --Added by Bo Li for Bug9759668 on 2010-6-2 End
4308 END IF;
4309
4310 /*
4311 || Get the gl_date from ra_cust_trx_lines_gl_dist_all
4312 */
4313 OPEN cur_get_gl_date('REC'); /* Modified by Ramananda for removal of SQL LITERALs */
4314 FETCH cur_get_gl_date INTO ld_gl_date;
4315 CLOSE cur_get_gl_date;
4316
4317 FOR get_order_and_item_id_rec IN get_order_and_item_id_cur LOOP
4318
4319
4320 OPEN check_rma_credit_cur(get_order_and_item_id_rec.order_line_id);
4321 FETCH check_rma_credit_cur
4322 INTO ln_rma_flag;
4323 CLOSE check_rma_credit_cur;
4324
4325 OPEN check_shippable_item_cur( get_order_and_item_id_rec.inventory_item_id
4326 ,get_order_and_item_id_rec.order_line_id);
4327 FETCH check_shippable_item_cur
4328 INTO ln_nonship_rma_flag;
4329 CLOSE check_shippable_item_cur;
4330
4331 IF ln_rma_flag >0 OR ln_nonship_rma_flag >0
4332 THEN
4333 --Added by Bo Li for Bug#9765108 on 09-JUL-2010 Begin
4334 ---------------------------------------------------------
4335 FOR rec_cur_get_man_ar_inv_taxes IN cur_get_man_ar_inv_taxes(upper(jai_constants.source_ar)
4336 ,jai_constants.source_ttype_man_ar_inv
4337 ,jai_constants.tname_cus_trx_lines
4338 ,get_order_and_item_id_rec.customer_trx_line_id
4339 ,v_organization_id
4340 ,v_loc_id)
4341 LOOP
4342
4343 /*******************************
4344 ||Variable Initialization
4345 *******************************/
4346 ln_liab_acct_ccid := null;
4347 ln_intliab_acct_ccid := null;
4348 ln_charge_ac_id := null;
4349 ln_balancing_ac_id := null;
4350 ln_credit_amount := null;
4351 ln_debit_amount := null;
4352 lc_account_name := null;
4353
4354
4355 /*******************************
4356 ||Get the code combination id
4357 ||for the "LIABILITY ACCOUNT"
4358 *******************************/
4359 ln_liab_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
4360 p_regime_id => ln_regime_id ,
4361 p_organization_type => jai_constants.orgn_type_io ,
4362 p_organization_id => v_organization_id ,
4363 p_location_id => v_loc_id ,
4364 p_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type ,
4365 p_account_name => jai_constants.liability
4366 ) ;
4367
4368 /*******************************
4369 || Get the code combination id
4370 || for the "INTERIM LIABILITY ACCOUNT"
4371 *******************************/
4372 ln_intliab_acct_ccid := jai_cmn_rgm_recording_pkg.get_account(
4373 p_regime_id => ln_regime_id ,
4374 p_organization_type => jai_constants.orgn_type_io ,
4375 p_organization_id => v_organization_id ,
4376 p_location_id => v_loc_id ,
4377 p_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type ,
4378 p_account_name => jai_constants.liability_interim
4379 ) ;
4380
4381
4382 /*
4383 || Validate that if any one of the liability account or interim liability account is not defined then error our
4384 */
4385 IF ln_liab_acct_ccid IS NULL OR
4386 ln_intliab_acct_ccid IS NULL
4387 THEN
4388 pv_return_code := jai_constants.expected_error;
4389 pv_return_message := 'VAT receivable accouting entries cannot be passed.
4390 Please set up the Liability account and the Interim Liability account for the corresponding VAT regime';
4391 return;
4392 END IF;
4393
4394
4395
4396 ln_charge_ac_id := ln_intliab_acct_ccid ;
4397 ln_balancing_ac_id := ln_liab_acct_ccid ;
4398 ln_debit_amount := null ;
4399 ln_credit_amount := abs(rec_cur_get_man_ar_inv_taxes.func_tax_amount) ;
4400 lc_account_name := jai_constants.recovery ;
4401
4402
4403
4404
4405 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry (
4406 pn_repository_id => ln_repository_id ,
4407 pn_regime_id => ln_regime_id ,
4408 pv_tax_type => rec_cur_get_man_ar_inv_taxes.tax_type ,
4409 pv_organization_type => jai_constants.orgn_type_io ,
4410 pn_organization_id => v_organization_id ,
4411 pn_location_id => v_loc_id ,
4412 pv_source => jai_constants.source_ar ,
4413 pv_source_trx_type => jai_constants.source_ttype_man_ar_inv ,
4414 pv_source_table_name => jai_constants.tname_cus_trx_lines ,
4415 pn_source_id => rec_cur_get_man_ar_inv_taxes.customer_trx_line_id ,
4416 pd_transaction_date => rec_cur_get_man_ar_inv_taxes.creation_date ,
4417 pv_account_name => lc_account_name , --Date 14/06/2007 by sacsethi for bug 6072461
4418 pn_charge_account_id => ln_charge_ac_id ,
4419 pn_balancing_account_id => ln_balancing_ac_id ,
4420 pn_credit_amount => ln_credit_amount ,
4421 pn_debit_amount => ln_debit_amount ,
4422 pn_assessable_value => rec_cur_get_man_ar_inv_taxes.vat_assessable_value ,
4423 pn_tax_rate => rec_cur_get_man_ar_inv_taxes.tax_rate ,
4424 pn_reference_id => rec_cur_get_man_ar_inv_taxes.tax_id ,
4425 pn_batch_id => NULL ,
4426 pn_inv_organization_id => v_organization_id ,
4427 pv_invoice_no => lv_vat_invoice_no ,
4428 pd_invoice_date => nvl(nvl(ld_gl_date,pr_new.trx_date),rec_cur_get_man_ar_inv_taxes.creation_date) ,
4429 pv_called_from => jai_constants.vat_repo_call_from_om_ar ,
4430 pv_process_flag => lv_process_flag ,
4431 pv_process_message => lv_process_message ,
4432 pv_trx_reference_context => jai_constants.contxt_manual_ar ,
4433 pv_trx_reference1 => rec_cur_get_man_ar_inv_taxes.customer_trx_id ,
4434 pv_trx_reference2 => rec_cur_get_man_ar_inv_taxes.link_to_cust_trx_line_id ,
4435 pv_trx_reference3 => NULL ,
4436 pv_trx_reference4 => NULL ,
4437 pv_trx_reference5 => NULL
4438 );
4439
4440
4441
4442 IF lv_process_flag = jai_constants.expected_error OR
4443 lv_process_flag = jai_constants.unexpected_error
4444 THEN
4445 pv_return_code := lv_process_flag ;
4446 pv_return_code := lv_process_message ;
4447 return;
4448 END IF;
4449
4450 END LOOP;
4451 ------------------------------------------
4452 --Added by Bo Li for Bug#9765108 on 09-JUL-2010 End
4453 END IF;
4454 END LOOP;
4455
4456 /* Commented by Allen Yang 10-Jun-2010 for bug 9793678
4457 IF lv_vat_invoice_no IS NOT NULL THEN
4458 UPDATE JAI_AR_TRXS
4459 SET vat_invoice_no = lv_vat_invoice_no ,
4460 vat_invoice_date = nvl(ld_gl_date,pr_new.trx_date)
4461 WHERE customer_trx_id = pr_new.customer_trx_id ;
4462 END IF;
4463 */
4464
4465 EXCEPTION
4466 WHEN OTHERS THEN
4467 Pv_return_code := jai_constants.unexpected_error;
4468 Pv_return_message := 'Encountered an error in JAI_AR_RCTA_TRIGGER_PKG.ARU_T8 ' || substr(sqlerrm,1,1900);
4469
4470 END ARU_T8 ;
4471
4472
4473 /*
4474 REM +======================================================================+
4475 REM NAME ASI_T1
4476 REM
4477 REM DESCRIPTION Called from trigger JAI_AR_RCTA_ASI_T1
4478 REM
4479 REM NOTES Refers to old trigger JAI_AR_RCTA_ASI_T1
4480 REM
4481 REM +======================================================================+
4482 */
4483 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
4484 /*---------------------------------------------------------------------------
4485 HISTORY :
4486 1. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
4487 DB Entity as required for CASE COMPLAINCE. Version 116.1
4488
4489 2. 10-Jun-2005 File Version: 116.2
4490 Removal of SQL LITERALs is done
4491
4492 3. 10-Jun-2005 rallamse bug#4448789 116.3
4493 Added legal_entity_id for table JAI_AR_TRXS in insert statement
4494
4495 4. 26-07-2005 rallamse bug#4510143 120.2
4496 Modified legal_entity_id for table JAI_AR_TRXS to get from header_info_cur cursor
4497
4498 5. 10-Aug-2005 Aiyer bug 4545146 version 120.1
4499 Issue:-
4500 Deadlock on tables due to multiple triggers on the same table (in different sql files)
4501 firing in the same phase.
4502 Fix:-
4503 Multiple triggers on the same table have been merged into a single file to resolve
4504 the problem
4505 The following files have been stubbed:-
4506 jai_ar_rcta_t1.sql
4507 jai_ar_rcta_t2.sql
4508 jai_ar_rcta_t3.sql
4509 jai_ar_rcta_t4.sql
4510 jai_ar_rcta_t6.sql
4511 jai_ar_rcta_t7.sql
4512 jai_ar_rcta_t8.sql
4513 jai_ar_rcta_t9.sql
4514 Instead the new file jai_ar_rcta_t.sql has been created which contains all the triggers
4515
4516 ---------------------------------------------------------------------------------------------------*/
4517 v_created_from Varchar2(30);
4518 v_header_id Number;
4519 v_customer_trx_line_id Number;
4520 v_recurred_from_trx_number Varchar2(20);
4521 v_trx_number Varchar2(20);
4522 v_once_completed_flag Varchar2(1);
4523 x Number;
4524 v_batch_source_id Number := 0;
4525 v_parent_header_id Number;
4526 v_line_tax_amount Number := 0;
4527 v_header_tax_amount Number := 0;
4528 v_last_update_date Date;
4529 v_last_updated_by Number;
4530 v_creation_date Date;
4531 v_created_by Number;
4532 v_last_update_login Number;
4533 v_service_type VARCHAR2(30); --added by ssawant
4534
4535 CURSOR temp_fetch IS
4536 SELECT trx_number, customer_trx_id, recurred_from_trx_number, batch_source_id, created_from,
4537 creation_date, created_by, last_update_date, last_updated_by, last_update_login
4538 FROM JAI_AR_TRX_COPY_HDR_T
4539 ORDER BY customer_trx_id;
4540
4541 CURSOR ONCE_COMPLETE_FLAG_CUR(p_header_id IN NUMBER, p_batch_source_id IN Number) IS
4542 SELECT once_completed_flag, 1
4543 FROM JAI_AR_TRXS
4544 WHERE customer_trx_id = p_header_id
4545 AND NVL(batch_source_id,0) = p_batch_source_id;
4546
4547 CURSOR parent_header_id(p_recurred_from_trx_number IN Varchar2, p_batch_source_id IN Number) IS
4548 SELECT a.customer_trx_id
4549 FROM JAI_AR_TRXS a
4550 WHERE a.trx_number = p_recurred_from_trx_number
4551 AND NVL(batch_source_id,0) = p_batch_source_id;
4552
4553 CURSOR LINES_INFO_CUR(p_parent_header_id IN Number) IS
4554 SELECT customer_trx_line_id, line_number, description, inventory_item_id, unit_code, quantity, tax_category_id,
4555 auto_invoice_flag, unit_selling_price, line_amount, gl_date,
4556 tax_amount,total_amount,assessable_value
4557 FROM JAI_AR_TRX_LINES
4558 WHERE customer_trx_id = p_parent_header_id
4559 ORDER BY customer_trx_line_id;
4560
4561 CURSOR TAX_INFO_CUR(p_parent_line_id IN NUMBER) IS
4562 SELECT a.tax_line_no,
4563 a.precedence_1,a.precedence_2, a.precedence_3, a.precedence_4,a.precedence_5,
4564 a.precedence_6,a.precedence_7, a.precedence_8, a.precedence_9,a.precedence_10, -- Date 06/12/2006 Bug 5228046 added by SACSETHI
4565 a.tax_id, a.tax_rate, a.qty_rate, a.uom, a.tax_amount, a.base_tax_amount, a.func_tax_amount,
4566 b.end_date valid_date, b.tax_type
4567 FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
4568 WHERE a.link_to_cust_trx_line_id = p_parent_line_id
4569 AND a.tax_id = b.tax_id
4570 ORDER BY a.tax_line_no;
4571
4572
4573 CURSOR HEADER_INFO_CUR(p_recurred_from_trx_number IN Varchar2, p_batch_source_id IN Number) IS
4574 SELECT CUSTOMER_TRX_ID, ORGANIZATION_ID, LOCATION_ID, UPDATE_RG_FLAG, UPDATE_RG23D_FLAG,
4575 TAX_AMOUNT, LINE_AMOUNT, TOTAL_AMOUNT, BATCH_SOURCE_ID,legal_entity_id /* added rallamse bug#4448789 */
4576 FROM JAI_AR_TRXS
4577 WHERE trx_number = p_recurred_from_trx_number
4578 AND NVL(batch_source_id,0) = p_batch_source_id;
4579
4580 BEGIN
4581 pv_return_code := jai_constants.successful ;
4582
4583 OPEN temp_fetch;
4584 FETCH temp_fetch INTO v_trx_number, v_header_id, v_recurred_from_trx_number, v_batch_source_id,
4585 v_created_from, v_creation_date, v_created_by,
4586 v_last_update_date, v_last_updated_by, v_last_update_login;
4587 CLOSE temp_fetch;
4588
4589 DELETE JAI_AR_TRX_COPY_HDR_T
4590 WHERE customer_trx_id = v_header_id;
4591
4592 IF v_trx_number IS NULL THEN
4593 Return;
4594 END IF;
4595 IF v_created_from <>'ARXREC' THEN
4596 RETURN;
4597 END IF;
4598
4599 OPEN ONCE_COMPLETE_FLAG_CUR(v_header_id, v_batch_source_id);
4600 FETCH ONCE_COMPLETE_FLAG_CUR INTO v_once_completed_flag, x;
4601 CLOSE ONCE_COMPLETE_FLAG_CUR;
4602 IF NVL(v_once_completed_flag,'N') = 'Y' THEN
4603 RETURN;
4604 END IF;
4605
4606 OPEN parent_header_id(v_recurred_from_trx_number, v_batch_source_id);
4607 FETCH parent_header_id INTO v_parent_header_id;
4608 CLOSE parent_header_id;
4609
4610 IF NVL(x,0) <> 1 THEN
4611
4612 FOR hdr in HEADER_INFO_CUR(v_recurred_from_trx_number, v_batch_source_id)
4613 LOOP
4614 INSERT INTO JAI_AR_TRXS
4615 (customer_trx_id, organization_id, location_id, update_rg23d_flag,
4616 update_rg_flag, trx_number, once_completed_flag,
4617 line_amount, batch_source_id, created_from,
4618 creation_date, created_by,
4619 last_update_date,last_updated_by, last_update_login,
4620 legal_entity_id) /* added rallamse bug#4448789 */
4621 VALUES(v_header_id, hdr.organization_id, hdr.location_id, hdr.update_rg23d_flag,
4622 hdr.update_rg_flag, v_trx_number, 'N',
4623 hdr.line_amount, hdr.batch_source_id, v_created_from ,
4624 v_creation_date, v_created_by,
4625 v_last_update_date, v_last_updated_by, v_last_update_login,
4626 hdr.legal_entity_id); /* added rallamse bug#4448789 */
4627 END LOOP;
4628 END IF;
4629 --added by ssawant to replace r_new to pr_new
4630 v_service_type:=JAI_AR_RCTLA_TRIGGER_PKG.get_service_type( NVL(pr_new.SHIP_TO_CUSTOMER_ID ,pr_new.BILL_TO_CUSTOMER_ID) ,
4631 NVL(pr_new.SHIP_TO_SITE_USE_ID, pr_new.BILL_TO_SITE_USE_ID),'C'); -- added by csahoo for bug#5879769
4632
4633 FOR rec in LINES_INFO_CUR(v_parent_header_id)
4634 LOOP
4635
4636 -- SELECT ra_customer_trx_lines_s.nextval INTO v_customer_trx_line_id FROM Dual;
4637
4638 INSERT INTO JAI_AR_TRX_LINES
4639 (customer_trx_line_id, line_number,
4640 customer_trx_id, description,
4641 inventory_item_id, unit_code,
4642 quantity, tax_category_id,auto_invoice_flag ,
4643 unit_selling_price, line_amount, gl_date,
4644 assessable_value,
4645 creation_date, created_by,
4646 last_update_date,last_updated_by,
4647 last_update_login,
4648 service_type_code) --added by csahoo for Bug#5879769
4649 VALUES(ra_customer_trx_lines_s.nextval,
4650 --v_customer_trx_line_id, /* Commented by Ramananda as a part of removal of SQL LITERALs */
4651 rec.line_number,
4652 v_header_id,rec.description,
4653 rec.inventory_item_id, rec.unit_code,
4654 rec.quantity, rec.tax_category_id,rec.auto_invoice_flag,
4655 rec.unit_selling_price,rec.line_amount, rec.gl_date,
4656 rec.assessable_value,
4657 v_creation_date, v_created_by, v_last_update_date,
4658 v_last_updated_by, v_last_update_login,
4659 v_service_type) --added by csahoo for Bug#5879769
4660 returning customer_trx_line_id into v_customer_trx_line_id ;
4661
4662 FOR rec1 in TAX_INFO_CUR(rec.customer_trx_line_id)
4663 LOOP
4664 IF rec1.valid_date < sysdate THEN
4665 rec1.tax_amount := 0;
4666 rec1.base_tax_amount := 0;
4667 rec1.func_tax_amount := 0;
4668 END IF;
4669 INSERT INTO JAI_AR_TRX_TAX_LINES(customer_trx_line_id, link_to_cust_trx_line_id, tax_line_no,
4670 precedence_1,precedence_2, precedence_3, precedence_4,precedence_5,
4671 precedence_6,precedence_7, precedence_8, precedence_9,precedence_10, -- Date 06/12/2006 Bug 5228046 added by SACSETHI
4672 tax_id, tax_rate, qty_rate, uom,
4673 tax_amount, base_tax_amount, func_tax_amount,
4674 creation_date, created_by, last_update_date,
4675 last_updated_by, last_update_login)
4676 VALUES( ra_customer_trx_lines_s.nextval, v_customer_trx_line_id, rec1.tax_line_no,
4677 rec1.precedence_1, rec1.precedence_2, rec1.precedence_3, rec1.precedence_4, rec1.precedence_5,
4678 rec1.precedence_6, rec1.precedence_7, rec1.precedence_8, rec1.precedence_9, rec1.precedence_10, -- Date 06/12/2006 Bug 5228046 added by SACSETHI
4679 rec1.tax_id, rec1.tax_rate, rec1.qty_rate, rec1.uom,
4680 rec1.tax_amount, rec1.base_tax_amount, rec1.func_tax_amount,
4681 v_creation_date, v_created_by, v_last_update_date,
4682 v_last_updated_by, v_last_update_login);
4683
4684 IF rec1.tax_type <> 'TDS' THEN
4685 v_line_tax_amount := nvl(v_line_tax_amount,0) + nvl(rec1.tax_amount,0);
4686 END IF;
4687
4688 IF rec1.tax_type in ('Excise', 'Addl. Excise', 'Other Excise') THEN
4689 v_header_tax_amount := nvl(v_header_tax_amount,0) + nvl(rec1.tax_amount,0);
4690 END IF;
4691
4692 END LOOP;
4693 UPDATE JAI_AR_TRX_LINES
4694 SET tax_amount = v_line_tax_amount,
4695 total_amount = nvl(line_amount,0) + v_line_tax_amount
4696 WHERE customer_trx_line_id = v_customer_trx_line_id;
4697 v_line_tax_amount := 0;
4698 END LOOP;
4699
4700 UPDATE JAI_AR_TRXS
4701 SET tax_amount = v_header_tax_amount,
4702 total_amount = nvl(line_amount,0) + v_header_tax_amount
4703 WHERE customer_trx_id = v_header_id;
4704 v_header_tax_amount := 0;
4705
4706 END ASI_T1 ;
4707
4708 --added this procedure for bug#7450481
4709 PROCEDURE ARD_T1 ( pr_old t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
4710 BEGIN
4711 DELETE JAI_AR_TRXS
4712 WHERE customer_trx_id = pr_old.customer_trx_id ;
4713
4714 pv_return_message := '';
4715 pv_return_code := jai_constants.successful;
4716
4717 EXCEPTION
4718 when others then
4719 pv_return_message := substr (sqlerrm,1,1999);
4720 pv_return_code := jai_constants.unexpected_error;
4721 END ARD_T1;
4722
4723 END JAI_AR_RCTA_TRIGGER_PKG ;
[Home] [Help]