1 PACKAGE BODY JAI_JAR_TRXS_TRIGGER_PKG AS
2 /* $Header: jai_jar_t.plb 120.12 2011/05/10 09:32:07 zhhou ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME ARU_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_JAR_T_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_JAR_T_ARU_T1
11 REM
12 REM +======================================================================+
13 */
14 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
15 v_org_id Number;
16 v_loc_id Number := 0;
17 v_receipt_id Number;
18 v_reg_code Varchar2(30);
19 v_reg_type Varchar2(10);
20 v_complete_flag Varchar2(1);
21 v_fin_year Number;
22 v_register_id number;
23 v_payment_register Varchar2(30) ;
24 v_basic_ed Number := 0;
25 v_additional_ed Number := 0;
26 v_other_ed Number := 0;
27 v_rg_flag Varchar2(1);
28 v_tax_amount Number := 0;
29 v_charge_account number;
30 v_tax_rate Number := 0;
31 v_rg_register_id Number := 0;
32
33 v_rg23a_bal number := 0;
34 v_rg23c_bal number := 0;
35 v_pla_bal number := 0;
36 v_sold_cust_id Number;
37 v_ship_cust_id Number;
38 v_ship_to_site_use_id Number;
39 v_cust_trx_type_id Number;
40 v_trx_date Date;
41 v_batch_source_id Number := 0;
42 v_account_id Number;
43 v_counter Number := 0;
44 v_average_duty Number := 0;
45 v_rg23_part_ii_no Number;
46 v_rg23_part_i_no Number;
47 v_excise_flag Varchar2(1);
48 v_pla_register_no Number;
49 v_part_i_register_id Number ;
50
51 /* --Ramananda for File.Sql.35 */
52 v_item_class Varchar2(10); -- := 'N';
53 v_customer_trx_id Number; -- := pr_old.customer_trx_id;
54 v_last_update_date Date ; -- := pr_new.last_update_date;
55 v_last_updated_by Number; -- := pr_new.last_updated_by;
56 v_creation_date Date; -- := SYSDATE;
57 v_created_by Number; -- := pr_new.created_by;
58 v_last_update_login Number; -- := pr_new.last_update_login;
59 v_set_of_books_id Number; -- := pr_new.set_of_books_id;
60 v_currency_code Varchar2(30); -- := pr_new.invoice_currency_code;
61 v_conv_date Date ; -- := NVL(pr_new.exchange_date, pr_new.creation_date);
62 v_conv_type_code Varchar2(30); -- := pr_new.Exchange_Rate_Type;
63 v_conv_rate Number ; -- := pr_new.Exchange_rate;
64 v_trx_number varchar2(20); -- := pr_new.Trx_Number;
65 v_source_name Varchar2(100); -- := 'Receivables India';
66 v_category_name Varchar2(100); -- := 'RG Register Data Entry';
67 /* --Ramananda for File.Sql.35 */
68
69 v_converted_rate Number;
70 v_assessable_value Number;
71 /* v_bill_to_site_id number;
72 v_bill_to_site_use_id number; */
73 --2001/05/04 Vijay,Subbu.
74
75 v_tax_rate_counter Number := 0;
76 v_match_tax_rate Number := 0;
77 v_rg23d_receipt_id Number;
78 v_oth_receipt_id Number;
79 v_duty_amount number;
80 uom_code varchar2(3);
81 V_item_trading_flag Varchar2(1);
82 v_modvat_tax_rate Number;
83 v_remarks Varchar2(60);
84 v_rounding_factor Number;
85 v_opt_unit Number; --2001/05/04 Vijay,Subbu.
86 v_bill_to_customer_id Number; --2001/05/04 Vijay,Subbu.
87 v_bill_to_site_use_id Number; --2001/05/04 Vijay,Subbu.
88 --Added the below 3 variables by Anujsax for Bug#5636544
89 lv_excise_invoice_no jai_ar_trx_lines.excise_invoice_no%TYPE;
90 req_id NUMBER;
91 result BOOLEAN;
92 ---ended by Anujsax for Bug#5636544
93 Cursor complete_info_cur IS
94 Select SHIP_TO_CUSTOMER_ID,SHIP_TO_SITE_USE_ID,CUST_TRX_TYPE_ID,
95 TRX_DATE,SOLD_TO_CUSTOMER_ID,
96 BATCH_SOURCE_ID,
97 BILL_TO_CUSTOMER_ID, BILL_TO_SITE_USE_ID --2001/05/04 Vijay,Subbu.
98 From JAI_AR_TRX_INS_HDRS_T
99 Where customer_trx_id = v_customer_trx_id;
100
101 --2001/06/22 Anuradha Parthasarathy
102 /*Cursor org_loc_cur IS
103 SELECT organization_id,location_id,register_type,rg_update_flag,once_completed_flag
104 FROM JAI_AR_TRX_APPS_RELS_T
105 WHERE paddr = (SELECT paddr FROM v$session WHERE sid =
106 (SELECT sid FROM v$mystat WHERE rownum = 1));*/
107
108 Cursor org_loc_cur IS
109 SELECT organization_id, location_id, register_type, rg_update_flag, once_completed_flag
110 FROM JAI_AR_TRX_APPS_RELS_T r;/*, v$session s
111 WHERE r.paddr = s.paddr
112 and s.audsid=userenv('SESSIONID');*/ --commented by rchandan for CASE120
113 --2001/06/22 Anuradha Parthasarathy
114 --assessable value added
115 Cursor line_cur IS
116 SELECT customer_trx_line_id line_id, payment_register, inventory_item_id,
117 quantity quantity_invoiced,unit_selling_price,unit_code,
118 excise_invoice_no, excise_invoice_date, assessable_value,
119 customer_trx_line_id, excise_exempt_type
120 FROM JAI_AR_TRX_LINES
121 WHERE customer_trx_id = v_customer_trx_id;
122 CURSOR REG_BALANCE_CUR(p_org_id IN Number,
123 p_loc_id IN Number) IS
124 SELECT nvl(rg23a_balance,0) rg23a_balance ,nvl(rg23c_balance,0) rg23c_balance,
125 nvl(pla_balance,0) pla_balance
126 FROM JAI_CMN_RG_BALANCES
127 WHERE organization_id = p_org_id AND
128 location_id = p_loc_id;
129 Cursor register_code_cur(p_org_id IN Number, p_loc_id IN Number, p_batch_source_id IN NUMBER) IS
130 SELECT register_code
131 FROM JAI_OM_OE_BOND_REG_HDRS
132 WHERE organization_id = p_org_id AND
133 location_id = p_loc_id AND
134 register_id in (SELECT register_id FROM JAI_OM_OE_BOND_REG_DTLS
135 WHERE order_type_id = p_batch_source_id and order_flag ='N');
136 CURSOR excise_cal_cur(p_line_id IN NUMBER, p_inventory_item_id IN NUMBER,
137 p_org_id IN NUMBER) IS
138 select A.tax_id,
139 A.tax_rate t_rate,
140 A.tax_amount tax_amt,
141 A.func_tax_amount func_amt,
142 (a.func_tax_amount*100)/decode(a.tax_rate,0,0.01) taxable_amt, --2001/03/30 Jagdish
143 A.BASE_TAX_AMOUNT BASE_TAX_AMT, --2001/03/30 Jagdish
144 b.tax_type t_type,
145 b.stform_type,
146 a.tax_line_no
147 from JAI_AR_TRX_TAX_LINES A , JAI_CMN_TAXES_ALL B,
148 JAI_INV_ITM_SETUPS C
149 where link_to_cust_trx_line_id = p_line_id
150 and a.tax_id = b.tax_id
151 and c.inventory_item_id = p_inventory_item_id
152 and c.organization_id = p_org_id
153 AND c.item_class in ('RMIN','RMEX','CGEX','CGIN','CCEX','CCIN','FGIN','FGEX')
154 order by 1;
155 cursor item_class_cur(P_ORG_ID IN NUMBER,P_INVENTORY_ITEM_ID IN NUMBER) IS
156 select item_class, excise_flag,item_trading_flag
157 from JAI_INV_ITM_SETUPS
158 where inventory_item_id = p_inventory_item_id AND
159 ORGANIZATION_ID = P_ORG_ID;
160 cursor organization_cur IS
161 select organization_id,location_id
162 FROM JAI_AR_TRX_INS_HDRS_T
163 WHERE customer_trx_id = v_customer_trx_id;
164 CURSOR fin_year_cur(p_org_id IN NUMBER) IS
165 SELECT MAX(a.fin_year)
166 FROM JAI_CMN_FIN_YEARS a
167 WHERE organization_id = p_org_id and fin_active_flag = 'Y';
168
169 CURSOR matched_receipt_cur(p_customer_trx_line_id IN NUMBER) IS
170 SELECT a.receipt_id, a.quantity_applied, b.transaction_type,b.qty_to_adjust,
171 b.rate_per_unit,b.excise_duty_rate
172 FROM JAI_CMN_MATCH_RECEIPTS a, JAI_CMN_RG_23D_TRXS b
173 WHERE a.ref_line_id = p_customer_trx_line_id
174 AND a.receipt_id = b.register_id
175 AND a.quantity_applied > 0 ;
176 CURSOR tax_rate_cur(p_customer_trx_line_id IN NUMBER, p_receipt_id IN NUMBER)
177 IS
178 SELECT tax_rate
179 FROM JAI_CMN_MATCH_TAXES
180 WHERE ref_line_id = p_customer_trx_line_id
181 AND receipt_id = p_receipt_id; /* Modified by Ramananda for removal of SQL LITERALs */
182 -- AND nvl(receipt_id,0) = p_receipt_id;
183
184 --added by GD
185 CURSOR for_modvat_percentage(v_org_id NUMBER, v_location_id NUMBER) IS
186 SELECT MODVAT_REVERSE_PERCENT
187 FROM JAI_CMN_INVENTORY_ORGS
188 WHERE organization_id = v_org_id
189 AND (location_id = v_location_id
190 OR
191 (location_id is NULL AND v_location_id is NULL));
192 --AND NVL(location_id,0) = NVL(v_location_id,0);
193
194 CURSOR for_modvat_tax_rate(p_cust_trx_line_id NUMBER) IS
195 SELECT a.tax_rate, b.rounding_factor
196 FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
197 WHERE a.tax_id = b.tax_id
198 AND a.link_to_cust_trx_line_id = p_cust_trx_line_id
199 AND b.tax_type = jai_constants.tax_type_modvat_recovery ; /*'Modvat Recovery'; Ramananda for removal of SQL LITERALs */
200 --added by GD
201
202 /* Bug 5243532. Added by Lakshmi Gopalsami
203 * Removed the cursor set_of_books_cur and implemented using caching logic.
204 */
205
206 --2001/05/04 Vijay,Subbu.
207 CURSOR get_opt_unit is SELECT Operating_unit
208 FROM org_organization_definitions
209 WHERE organization_id = nvl(v_org_id,0);
210
211
212 -- added by sriram -- bug # 2769440
213
214 v_ref_10 gl_interface.reference10%type; -- := 'India Localization Entry for Invoice # '; -- will hold a standard text such as 'India Localization Entry for sales order'
215 v_ref_23 gl_interface.reference23%type; -- := 'ja_in_ar_hdr_complete_trg'; -- holds the object name -- 'ja_in_ar_hdr_complete_trg'
216 v_ref_24 gl_interface.reference24%type; -- := 'ra_customer_trx_lines_all'; -- holds the table name -- ' ra_customer_trx_all'
217 v_ref_25 gl_interface.reference25%type; -- := 'customer_trx_line_id'; -- holds the column name -- 'customer_trx_id'
218 v_ref_26 gl_interface.reference26%type; -- := v_customer_trx_id; -- holds the column value -- eg -- 13645
219
220 -- ends here additions by sriram - Bug # 2769440
221
222 VSQLERRM varchar2(250);
223 vsqlstmt varchar2(20);
224
225 lv_ship_status JAI_CMN_MATCH_RECEIPTS.ship_status%type ;
226
227 /*
228 || Start of bug 4566054
229 ||Code added by aiyer for the bug 4566054
230 ||Get the total cess amount at the invoice level
231 ||hence calculate the cess and pass it to the procedure ja_in_Rg_pkg.ja_in_rg_i_entry with source as 'AR'
232 */
233 CURSOR cur_get_trx_cess_amt (cp_trx_line_id in number) /* added by ssawant for bug 5989740 */
234 IS
235 SELECT
236 sum(jrcttl.func_tax_amount) cess_amount
237 FROM
238 jai_ar_trx_lines jrctl ,
239 jai_ar_trx_tax_lines jrcttl ,
240 jai_cmn_taxes_all jtc
241 WHERE
242 jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id AND
243 jrcttl.tax_id = jtc.tax_id AND
244 -- commented by ssawant
245 --jrctl.customer_trx_id = :old.customer_trx_id;
246 upper(jtc.tax_type) IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess) AND /* added by ssawant for bug 5989740 */
247 jrctl.customer_trx_line_id = cp_trx_line_id; /* added by ssawant for bug 5989740 */
248
249 -- This cursor is added by ssawant to account for the total sh cess amount for bug 5989740
250 CURSOR cur_get_trx_sh_cess_amt (cp_trx_line_id in number)
251 IS
252 SELECT
253 sum(jrcttl.func_tax_amount) sh_cess_amount
254 FROM
255 jai_ar_trx_lines jrctl,
256 jai_ar_trx_tax_lines jrcttl,
257 jai_cmn_taxes_all jtc
258 WHERE
259 jrctl.customer_trx_line_id = jrcttl.link_to_cust_trx_line_id AND
260 jrcttl.tax_id = jtc.tax_id AND
261 upper(jtc.tax_type) IN (jai_constants.tax_type_sh_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess) AND
262 jrctl.customer_trx_line_id = cp_trx_line_id;
263
264 --Added by Xiao for Open Interface ER bug#11683927 on 16-Feb-2011, begin
265 --------------------------------------------------------------------------------------
266 CURSOR get_external_flag_cur IS
267 SELECT COUNT(lines.customer_trx_id)
268 FROM jai_ar_trx_lines lines,
269 jai_interface_lines_all intfs
270 WHERE lines.customer_trx_line_id = intfs.internal_trx_line_id
271 AND lines.customer_trx_id = v_customer_trx_id
272 AND lines.interface_flag = 'Y'
273 AND intfs.taxable_event = 'EXTERNAL';
274
275 CURSOR get_trx_date_cur IS
276 SELECT trx_date
277 FROM jai_ar_trx_ins_hdrs_t
278 WHERE customer_trx_id = v_customer_trx_id;
279
280 CURSOR get_open_source_cur IS
281 SELECT COUNT(interface_flag)
282 FROM jai_ar_trx_lines
283 WHERE customer_trx_id = v_customer_trx_id
284 AND interface_flag = 'Y';
285
286 ln_external_flag NUMBER;
287 ln_open_source NUMBER;
288 --------------------------------------------------------------------------------------
289 --Added by Xiao for Open Interface ER bug#11683927 on 16-Feb-2011, end
290 ln_trx_totcess_amt JAI_CMN_RG_I_TRXS.CESS_AMT%TYPE; /* End of bug 4346220 */
291 ln_trx_totshcess_amt JAI_CMN_RG_I_TRXS.SH_CESS_AMT%TYPE; /* added by ssawant for bug 5989740 */
292
293
294 /* End of bug 4566054 */
295
296 /* Bug 5243532. Added by Lakshmi Gopalsami
297 * Defined variable for implementing caching logic.
298 */
299 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
300 BEGIN
301 pv_return_code := jai_constants.successful ;
302 /*------------------------------------------------------------------------------------------
303 FILENAME: JA_IN_AR_HDR_COMPLETE_TRG.sql
304
305 CHANGE HISTORY:
306 S.No Date Author and Details
307 1. 2001/03/30 Jagdish
308 In case of Bond register transactions to calculate the excise_duty,modification
309 done to excise_cal_rec cursor.Search for Jagdish for more modifications
310
311 2. 2001/05/04 Vijay,Subbu.
312 Code commented and added for ST Form Tracking
313
314 3. 2001/06/22 Anuradha Parthasarathy
315 Code commented and added to improve performance.
316
317 4. 2003/03/14 Sriram - Bug # 2846277 -- File Version 615.1
318 Trigger was returning when organization id value is 0 . Organization id
319 value can be 0 in case of setup business group setup is done . Hence making it into
320 a large number (999999) instead of 0.
321
322 5. 2003/08/22 Sriram Bug # 3021588 (Bond Register Enhancement) Version 616.2
323
324 For Multiple Bond Register Enhancement,
325 Instead of using the cursors for fetching the register associated with the invoice type , a call has been made to the procedures
326 of the jai_cmn_bond_register_pkg package. There enhancement has created dependency because of the
327 introduction of 3 new columns in the JAI_OM_OE_BOND_REG_HDRS table and also call to the new package jai_cmn_bond_register_pkg.
328
329 New Validations for checking the bond expiry date and to check the balance based on the call to the jai_cmn_bond_register_pkg has been added
330
331 Provision for letter of undertaking has been incorporated. In the case of the letter of undetaking , its also a type of bond register
332 but without validation for the balances.
333 This has been done by checking if the LOU_FLAG is 'Y' in the JAI_OM_OE_BOND_REG_HDRS table for the
334 associated register id , if yes , then validation is only based on bond expiry date .
335
336 This bug has introduced huge dependency. All future bugs on this object should have this bug as a prereq
337
338 6. 2003/10/10 Ssumaith - bug # 3179653 File Version 616.3
339
340 When RG23D register is getting hit, instead of populating the excise invoice number in the commercial invoice no field (comm_invoice_no)
341 trx_number of the invoice is getting populated.This is wrong ans has been corrected in this fix.
342
343 7. 2003/11/11 ssumaith - bug # 3138194 File Version 616.4
344
345 ST forms population functionality has been removed from this trigger and instead moved to a new
346 concurrent program which does the exclusive job of population of ST form records into the tables.
347
348 8. 11-Nov-2003 Aiyer - Bug #3249375 File Version 617.1
349 References to JA_IN_OE_ST_FORMS_HDR table, which has been obsolete post IN60105D1 patchset, was found
350 in this file in some cursors.
351 As these tables do not exists in the database any more post application the above mentioned patchset
352 hence deleting the cursors.
353
354 Dependency Due to This Bug:-
355 Can be applied only post application of IN60105D1.
356
357 9. 21-Nov-03 Ssumaith Bug # 3273545
358
359 When only Adhoc Excise tax is attached to the invoice line and matched against a receipt and the invoice
360 completed from the base apps screen is causing error - Divide by Zero ORA-1476.
361
362 10. 2004/04/20 ssumaith - bug# 3496577
363
364 Made code changes such that payment register does not get hit when update_rg_flag is set to No. Only
365 quantity register gets hit.
366
367 11. 2005/01/28 ssumaith - bug#4136981
368
369 IN call to the ja_in_register_txn_entry procedure , passing the customer_Trx_line_id instead of customer_trx_id
370 Because , this procedure gets called from individual line in the JAI_AR_TRX_LINES table
371 when 'COMPLETE' action is done.
372
373 12. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
374 DB Entity as required for CASE COMPLAINCE. Version 116.2
375
376 13. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.2
377 Removal of SQL LITERALs is done
378
379 14. 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
380 GL Sources and GL Categories got changed. Refer bug for the details
381
382 15. 23-Aug-2005 Aiyer - Bug 4566054 (Forward porting for the 11.5 bug 4346220 ),Version 120.1
383 Issue :-
384 Rg does not show correct cess value in case of Shipment transactions.
385
386 Fix:-
387 Two fields cess_amt and source have been added in JAI_CMN_RG_I_TRXS table.
388 The cess amt and source would be populated from jai_jar_t_aru_t1 (Source -> 'AR' ) and
389 as 'WSH' from jai_om_wsh.plb procedure Shipment.
390 Corresponding changes have been done in the form JAINIRGI.fmb and JAFRMRG1.rdf .
391 For shipment and Ar receivable transaction currently the transaction_id is 33 and in some cases where the jai_cmn_rg_i_trxs.ref_doc_id
392 exactly matches the jai_om_wsh_lines_all.delivery_detail_id and jai_ar_trxs.customer_trx_id the tracking of the source
393 becomes very difficult hence to have a clear demarcation between WSh and AR sources hence the source field has been added.
394
395 Added 2 new parametes p_cess_amt and p_source to jai_om_rg_pkg.ja_in_rg_i_entry package.
396 This has been populated from this and jai_om_wsh_pkg.process_delivery procedure.
397
398 A migration script has been provided to migrate the value for cess and source.
399
400 Dependency due to this bug:-
401 1. Datamodel change in table JAI_CMN_RG_I_TRXS, added the cess_amt and source fields
402 2. Added two new parameters in jai_om_rg_pkg.ja_in_rg_i_entry procedure to insert data into JAI_CMN_RG_I_TRXS table
403 3. Modified the trigger jai_jar_t_aru_t1
404 4. Procedure jai_om_wsh_pkg.process_delivery
405 5. Report JAICMNRG1.rdf
406 6. Created a migration script to populate cess_amt and source for Shipment and Receivable transactions.
407 Both functional and technical dependencies exists
408
409
410 16. 23-Aug-2005 Aiyer - Bug# 4541303 (Forward porting for the 11.5 bug 4538315) 120.1
411
412 For a manual AR invoice with more than one line, the cess amount was being hit for the whole of the
413 invoice amount for each of the lines.
414
415 Code changes are done in the package jai_om_rg_pkg as well this trigger.
416
417 Code changes done in the package include calculating the cess amount for the current customer trx line id.
418
419 Code changes done in the trigger include sending the customer trx line id when pla is hit . This is inline
420 with the way JAI_CMN_RG_23AC_II_TRXS works.
421
422
423 Dependency Due to thus bug:-
424 jai_om_rg.plb (120.4)
425
426 17. 15-Feb-2007 CSahoo - BUG# 5390583, File Version 120.2
427 Forward Porting of 11i BUG 5357400
428 Issue : Excise amount not hitting bond register in functional currency.
429 Fix : Excise and cess amounts would hit bond register in functional currency.
430 Changes are done in three objects.
431
432 1. Package jai_om_rg_pkg. - Added a parameter to the ja_in_register_txn_entry called p_currency_rate
433 It holds the currency conversion rate which would be multiplied by the transaction amts to
434 get the functional amounts.
435
436 2. Package jai_jar_t.plb - In the call to the ja_in_register_txn_entry procedure
437 added the parameter called p_currency_code.
438
439 3. Package - jai_ract_trg_pkg - When a change is done in the invoice currency code from the front end
440 the change is being reflected in the JAI_AR_TRXS table.
441
442 Future Dependency due to this Bug
443 ------------------------
444 YES - A new parameter is added to the procedure - ja_in_register_txn_entry in the package jai_om_rg_pkg.
445 It has a technical dependency on jai_om_rg_pkg and Package jai_jar_t.plb.
446 It has functional dependency on jai_ract_trg.plb
447
448
449 18. 16-April-2007 ssawant for bug 5989740 ,File version 120.3
450 Forward porting Budget07-08 changes of handling secondary and
451 Higher Secondary Education Cess from 11.5( bug no 5907436) to R12 (bug no 5989740).
452
453 19. 28-Jun-2007 CSahoo for bug#6155839 , File Version 120.6
454 replaced RG Register Data Entry by jai_constants.je_category_rg_entry
455
456
457
458 20. 17-Sep-2007 Anujsax for bug#5636544 ,File Version 120.7
459 Forward porting for R11 bug 5629319 into R12 bug 5636544
460 Issue : excise_invoice_number need to be updated in the ra_customer_trx_all.ct_reference table.
461 Fix : 1) Stored the excise_invoice_no into a variable
462 2) Submitted the concurrent - JAICMNCP to update the excise invoice number
463
464 21 19-mar-2008 ssumaith - bug# 6901521
465
466 removed the reference of JAICMNCP .
467
468 22. 10-Feb-2011 Xiao Lv for Open Interface ER bug#11683927 on 10-Feb-2011.
469 Issue: For the Open Interface ER, imported Excise taxes by OFI tax importing to AR,
470 didn't update the RG register, nor genreate accounting.
471 Fixed: For 'Standard' source, Excise Taxes should also update the RG register and
472 generate accounting, while for 'External' source, should not.
473 Add check condition to make sure the programs will work for AR transaction
474 from 'Standard' source.
475
476 23. 08-Apr-2011 Xiao for bug#11936390
477 Fixed: Add nvl(ln_external_flag, 0) condition.
478
479 Future Dependencies For the release Of this Object:-
480 (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/
481 A datamodel change )
482
483 ----------------------------------------------------------------------------------------------------------------------------------------------------
484 Current Version Current Bug Dependent Files Version Author Date Remarks
485 Of File On Bug/Patchset Dependent On
486 ja_in_ar_hdr_complete_trg.sql
487 ----------------------------------------------------------------------------------------------------------------------------------------------------
488 616.1 3021588 IN60104D1 + ssumaith 22/08/2003 Bond Register Enhancement
489 2801751 +
490 2769440
491
492 617.1 3249375 IN60105D1 Aiyer 11/Nov/2003 Can be applied only after IN60105D1 patchset
493 has been applied.
494 12.0 4566054 jai_om_rg.pls 120.3 Aiyer 24-Aug-2005
495 jai_om_rg.plb 120.4
496 jai_om_wsh.plb (jai_om_wsh_pkg.process_delivery) 120.4
497 JAINIRGI.fmb 120.2
498 jain14.odf 120.3
499 jain14reg.ldt 120.3
500 New migration script to port data into new tables 120.0
501 JAICMNRG1.rdf 120.3
502 jai_jai_t.sql (trigger jai_jar_t_aru_t1) 120.1
503 17/5/2007 bduvarag for the bug#4601570, File version 120.4
504 Forward porting the changes done in the 11i bug#4474270
505
506 ----------------------------------------------------------------------------------------------------------------------------------------------------
507 --------------------------------------------------------------------------------------------*/
508 v_item_class := 'N'; --Ramananda for File.Sql.35
509 v_customer_trx_id := pr_old.customer_trx_id; --Ramananda for File.Sql.35
510 v_last_update_date := pr_new.last_update_date; --Ramananda for File.Sql.35
511 v_last_updated_by := pr_new.last_updated_by; --Ramananda for File.Sql.35
512 v_creation_date := SYSDATE; --Ramananda for File.Sql.35
513 v_created_by := pr_new.created_by;--Ramananda for File.Sql.35
514 v_last_update_login := pr_new.last_update_login; --Ramananda for File.Sql.35
515 v_set_of_books_id := pr_new.set_of_books_id; --Ramananda for File.Sql.35
516 v_currency_code := pr_new.invoice_currency_code; --Ramananda for File.Sql.35
517 v_conv_date := NVL(pr_new.exchange_date, pr_new.creation_date); --Ramananda for File.Sql.35
518 v_conv_type_code := pr_new.Exchange_Rate_Type; --Ramananda for File.Sql.35
519 v_conv_rate := pr_new.Exchange_rate; --Ramananda for File.Sql.35
520 --2001/05/04 Vijay,Subbu.
521 v_trx_number := pr_new.Trx_Number; --Ramananda for File.Sql.35
522 v_source_name := 'Receivables India'; --Ramananda for File.Sql.35
523 v_category_name := jai_constants.je_category_rg_entry ; -- modified by csahoo for bug#6155839 --'RG Register Data Entry'; --Ramananda for File.Sql.35
524 v_ref_10 := 'India Localization Entry for Invoice # '; -- will hold a standard text such as 'India Localization Entry for sales order'
525 v_ref_23 := 'ja_in_ar_hdr_complete_trg'; -- holds the object name -- 'ja_in_ar_hdr_complete_trg'
526 v_ref_24 := 'ra_customer_trx_lines_all'; -- holds the table name -- ' ra_customer_trx_all'
527 v_ref_25 := 'customer_trx_line_id'; -- holds the column name -- 'customer_trx_id'
528 v_ref_26 := v_customer_trx_id; -- holds the column value -- eg -- 13645
529
530 OPEN complete_info_cur;
531 FETCH complete_info_cur INTO v_ship_cust_id, v_ship_to_site_use_id,
532 v_cust_trx_type_id,
533 v_trx_date,v_sold_cust_id,v_batch_source_id,
534 v_bill_to_customer_id, v_bill_to_site_use_id; --2001/05/04 Vijay,Subbu.
535 CLOSE complete_info_cur;
536 --Add by Xiao for Open Interface ER bug#11683927 on 16-Feb-2011, begin
537 ----------------------------------------------------------------------------
538 OPEN get_external_flag_cur;
539 FETCH get_external_flag_cur INTO ln_external_flag;
540 CLOSE get_external_flag_cur;
541
542 OPEN get_open_source_cur;
543 FETCH get_open_source_cur INTO ln_open_source;
544 CLOSE get_open_source_cur;
545
546 IF ln_open_source > 0 THEN
547 --For 'Standard' source, manually set v_trx_date to avoid directly return.
548 --RG register will not be udpated, but will be generated.
549 IF (ln_external_flag = 0)THEN
550 OPEN get_trx_date_cur;
551 FETCH get_trx_date_cur INTO v_trx_date;
552 CLOSE get_trx_date_cur;
553 -- '-1' is passed to v_batch_source_id, the same as Manual AR.
554 v_batch_source_id := -1;
555 END IF;
556 END IF;
557 ----------------------------------------------------------------------------
558 --Add by Xiao for Open Interface ER bug#11683927 on 16-Feb-2011, end
559 IF v_trx_date IS NULL THEN
560 RETURN;
561 END IF;
562
563 OPEN org_loc_cur;
564 FETCH org_loc_cur INTO v_org_id, v_loc_id, v_reg_type, v_rg_flag,
565 v_complete_flag ;
566 CLOSE org_loc_cur;
567
568 --Add by Xiao for Open Interface ER bug#11683927 on 16-Feb-2011, begin
569 ----------------------------------------------------------------------------
570 --For 'Standard' source, manually set v_org_id/v_loc_id to avoid directly return.
571 IF ln_open_source > 0 THEN
572 v_org_id := pr_new.organization_id;
573 v_loc_id := pr_new.location_id;
574 END IF;
575 ----------------------------------------------------------------------------
576 --Add by Xiao for Open Interface ER bug#11683927 on 16-Feb-2011, end
577
578 IF NVL(v_org_id,999999) = 999999 THEN -- made 0 to 999999 because in case of setup business group setup , inventory organization value is 0
579 -- which was causing code to return .- bug # 2846277
580 OPEN organization_cur;
581 FETCH organization_cur INTO v_org_id, v_loc_id;
582 CLOSE organization_cur;
583 END IF;
584 IF NVL(v_org_id,999999) = 999999 THEN -- made 0 to 999999 because in case of setup business group setup , inventory organization value is 0
585 -- which was causing code to return .- bug # 2846277
586 RETURN;
587 END IF;
588
589
590
591 v_ref_10 := v_ref_10 || v_trx_number ;
592 vsqlstmt := '1';
593 -----Once Complete Button is Pressed Following code WILL tell you what will happ
594 --en at what stage
595
596 IF pr_new.ONCE_COMPLETED_FLAG <> pr_old.ONCE_COMPLETED_FLAG
597 OR ln_open_source > 0 --Add by Xiao for Open Interface ER bug#11683927 on 16-Feb-2011
598 THEN
599 IF v_set_of_books_id IS NULL
600 THEN
601 /* Bug 5243532. Added by Lakshmi Gopalsami
602 * Removed the cursor set_of_books_cur and implemented using caching logic.
603 */
604 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
605 (p_org_id => v_org_id );
606 v_set_of_books_id := l_func_curr_det.ledger_id;
607 END IF;
608 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_set_of_books_id ,v_currency_code ,
609 v_conv_date ,v_conv_type_code, v_conv_rate);
610
611 vsqlstmt := '2';
612 /*
613
614 OPEN register_code_cur(v_org_id, v_loc_id,v_batch_source_id);
615 FETCH register_code_cur INTO v_reg_code;
616 CLOSE register_code_cur;
617 */
618
619 -- above code commented by sriram - bug # 3021588 and instead making a call to the jai_cmn_bond_register_pkg Package instead.
620
621 jai_cmn_bond_register_pkg.GET_REGISTER_ID(v_org_id,
622 v_loc_id,
623 v_batch_source_id,
624 'N',
625 v_register_id,
626 v_reg_code
627 );
628 vsqlstmt := '3';
629 OPEN fin_year_cur(v_org_id);
630 FETCH fin_year_cur INTO v_fin_year;
631 CLOSE fin_year_cur;
632 OPEN REG_BALANCE_CUR(v_org_id, v_loc_id);
633 FETCH REG_BALANCE_CUR INTO v_rg23a_bal,v_rg23c_bal,v_pla_bal;
634 CLOSE REG_BALANCE_CUR;
635 vsqlstmt := '4';
636 --2001/05/04 Vijay,Subbu.
637 OPEN get_opt_unit;
638 FETCH get_opt_unit INTO v_opt_unit;
639 CLOSE get_opt_unit;
640 --2001/05/04 Vijay,Subbu.
641 vsqlstmt := '5';
642 FOR l_rec in line_cur LOOP
643
644 v_ref_26 := l_rec.customer_trx_line_id; -- sriram - bug # 2967440
645
646 OPEN item_class_cur(v_org_id,l_rec.inventory_item_id);
647 FETCH item_class_cur INTO v_item_class , v_excise_flag,V_item_trading_flag;
648 CLOSE item_class_cur;
649
650 /* Changed by Sjha on 25/10/99 */
651 vsqlstmt := '5';
652 IF NVL(v_excise_flag,'N') = 'Y' THEN
653 FOR excise_cal_rec in excise_cal_cur(l_rec.line_id, l_rec.inventory_item_id,
654 v_org_id)
655 LOOP
656 IF v_reg_code in ('BOND_REG') THEN
657 -- 2001/03/30 Jagdish
658 vsqlstmt := '6';
659 IF excise_cal_rec.t_type IN ('Excise') THEN
660 v_basic_ed := NVL(v_basic_ed,0) +
661 NVL(excise_cal_rec.BASE_TAX_AMT * (excise_cal_rec.t_rate)/100 ,0);
662 v_tax_rate := NVL(v_tax_rate,0) + NVL(excise_cal_rec.t_rate,0);
663 vsqlstmt := '7';
664 IF NVL(excise_cal_rec.t_rate,0) > 0 THEN
665 v_counter := v_counter + 1;
666 END IF;
667 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
668 vsqlstmt := '8';
669 v_additional_ed := NVL(v_additional_ed,0) +
670 NVL(excise_cal_rec.BASE_TAX_AMT * (excise_cal_rec.t_rate)/100 ,0);
671 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
672 vsqlstmt := '9';
673 v_other_ed := NVL(v_other_ed,0) +
674 NVL(excise_cal_rec.BASE_TAX_AMT * (excise_cal_rec.t_rate)/100 ,0);
675 END IF;
676 ELSE
677 IF excise_cal_rec.t_type IN ('Excise') THEN
678 vsqlstmt := '10';
679 v_basic_ed := NVL(v_basic_ed,0) + NVL(excise_cal_rec.func_amt,0);
680 v_tax_rate := NVL(v_tax_rate,0) + NVL(excise_cal_rec.t_rate,0);
681 IF NVL(excise_cal_rec.t_rate,0) > 0 THEN
682 vsqlstmt := '11';
683 v_counter := v_counter + 1;
684 END IF;
685 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
686 vsqlstmt := '12';
687 v_additional_ed := NVL(v_additional_ed,0) +NVL(excise_cal_rec.func_amt,0);
688 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
689 vsqlstmt := '13';
690 v_other_ed := NVL(v_other_ed,0) + NVL(excise_cal_rec.func_amt,0);
691 END IF;
692 END IF;
693 ---ghankot cmmented on 25-oct-99
694 ---ghankot commented on 25-oct-99
695 -- END IF;
696 END LOOP;
697 IF NVL(v_counter,0) = 0 THEN
698 v_counter := 1;
699 END IF;
700 vsqlstmt := '14';
701 v_average_duty := NVL(v_tax_rate,0)/v_counter;
702 vsqlstmt := '15';
703 v_average_duty := TRUNC(v_average_duty,2);
704 v_tax_amount := NVL(v_basic_ed,0) + NVL(v_additional_ed,0) +
705 NVL(v_other_ed,0);
706 IF v_item_class IN ('RMIN','RMEX','CGEX','CGIN','FGIN','FGEX','CCIN','CCEX')
707 THEN
708 vsqlstmt := '16';
709
710 v_assessable_value := NVL(v_converted_rate,0) * nvl(l_rec.assessable_value,0);
711 IF v_reg_code in ('DOMESTIC_EXCISE','EXPORT_EXCISE',
712 'DOMESTIC_WITHOUT_EXCISE', 'BOND_REG') THEN
713 vsqlstmt := '17';
714 /*
715 || Code added for the bug 4566054
716 || Initialize the ln_trx_totcess_amt variable to null;
717 */
718
719 ln_trx_totcess_amt := null;
720 ln_trx_totshcess_amt := null; /* added by ssawant for bug 5989740 */
721
722 /*
723 || Start of bug 4566054
724 ||Code added by aiyer for the bug 4566054
725 ||The cess amount is also being maintained in jai_cmn_rg_i_trxs table at a delivery_detail_level
726 ||hence calculate the cess and pass it to the procedure jai_om_rg_pkg.ja_in_rg_i_entry with source as 'WSH'
727 */
728
729 OPEN cur_get_trx_cess_amt(l_rec.line_id) ;
730 FETCH cur_get_trx_cess_amt INTO ln_trx_totcess_amt;
731 CLOSE cur_get_trx_cess_amt ;
732 /* End of bug 4566054 */
733
734 /* added by ssawant for bug 5989740 */
735 OPEN cur_get_trx_sh_cess_amt(l_rec.line_id) ;
736 FETCH cur_get_trx_sh_cess_amt INTO ln_trx_totshcess_amt;
737 CLOSE cur_get_trx_sh_cess_amt ;
738
739
740
741 IF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') THEN
742 vsqlstmt := '18';
743 IF l_rec.payment_register = 'RG23A' THEN
744 v_reg_type := 'A';
745 v_payment_register := 'RG23A';
746 ELSIF l_rec.payment_register = 'RG23C' THEN
747 v_reg_type := 'C';
748 v_payment_register := 'RG23A';
749 ELSIF l_rec.payment_register = 'PLA' THEN
750 v_payment_register := 'PLA';
751 END IF;
752 vsqlstmt := '19';
753 --Added by Xiao for Open Interface ER bug#11683927.
754 --Only for 'External' event, register should not be updated.
755 IF nvl(ln_external_flag, 0) = 0 THEN --Add nvl by Xiao for bug#11936390
756
757 jai_om_rg_pkg.ja_in_rg_I_entry(
758
759 p_fin_year => v_fin_year ,
760 p_org_id => v_org_id ,
761 p_location_id => v_loc_id ,
762 p_inventory_item_id => l_rec.inventory_item_id ,
763 p_transaction_id => 33 ,
764 p_transaction_date => SYSDATE ,
765 p_transaction_type => 'I' ,
766 p_header_id => v_customer_trx_id ,
767 p_excise_quantity => l_rec.quantity_invoiced ,
768 p_excise_amount => v_tax_amount ,
769 p_uom_code => l_rec.unit_code ,
770 p_excise_invoice_no => l_rec.excise_invoice_no ,
771 p_excise_invoice_date => l_rec.excise_invoice_date ,
772 p_payment_register => v_payment_register ,
773 p_basic_ed => v_basic_ed ,
774 p_additional_ed => v_additional_ed ,
775 p_other_ed => v_other_ed ,
776 p_excise_duty_rate => v_average_duty ,
777 p_customer_id => v_ship_cust_id ,
778 p_customer_site_id => v_ship_to_site_use_id ,
779 p_register_code => v_reg_code ,
780 p_creation_date => v_creation_date ,
781 p_created_by => v_created_by ,
782 p_last_update_date => v_last_update_date ,
783 p_last_updated_by => v_last_updated_by ,
784 p_last_update_login => v_last_update_login ,
785 p_assessable_value => v_assessable_value ,
786 p_cess_amt => ln_trx_totcess_amt , /*Parameters p_cess_amt and p_source added by aiyer for the bug 4566054 */
787 p_sh_cess_amt => ln_trx_totshcess_amt , /* added by ssawant for bug 5989740 */
788 p_source => jai_constants.source_ar
789 );
790
791
792 vsqlstmt := '20';
793 SELECT JAI_CMN_RG_I_TRXS_S.CURRVAL INTO v_part_i_register_id from dual;
794 END IF ; --Added by Xiao for Open Interface ER bug#11683927.
795 ELSIF v_item_class IN ('CGEX','CGIN') THEN
796 v_reg_type := 'C';
797 ELSIF v_item_class IN ('RMIN','RMEX') THEN
798 v_reg_type := 'A';
799 END IF;
800 IF v_item_class IN ('RMIN','RMEX','CGIN','CGEX') THEN
801 vsqlstmt := '21';
802
803 --Added IF condition by Xiao for Open Interface ER bug#11683927.
804 --Only for 'External' event, register should not be updated.
805 IF nvl(ln_external_flag, 0)= 0 THEN --Add nvl by Xiao for bug#11936390
806 jai_om_rg_pkg.ja_in_rg23_part_I_entry(v_reg_type, v_fin_year, v_org_id,
807 v_loc_id,
808 l_rec.inventory_item_id, 33, SYSDATE, 'I',
809 l_rec.quantity_invoiced, l_rec.unit_code, l_rec.excise_invoice_no,
810 l_rec.excise_invoice_date, v_basic_ed, v_additional_ed,
811 v_other_ed, v_ship_cust_id, v_ship_to_site_use_id,
812 v_customer_trx_id, SYSDATE, v_reg_code,
813 v_creation_date, v_created_by,v_last_update_date,
814 v_last_updated_by, v_last_update_login );
815 vsqlstmt := '22';
816 SELECT JAI_CMN_RG_23AC_I_TRXS_S.CURRVAL INTO v_part_i_register_id from dual;
817 END IF ;--Added by Xiao for Open Interface ER bug#11683927.
818 END IF;
819 END IF;
820 IF v_reg_code in ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
821
822
823
824 -- to code such that payment register does not get hit when update_rg_flag is set to No
825
826 if pr_new.update_rg_flag = 'Y' or pr_old.update_rg_flag = 'Y' then -- 3496577
827
828
829 IF l_rec.payment_register IN( 'RG23A','RG23C') THEN
830 IF l_rec.payment_register = 'RG23A' THEN
831 v_reg_type := 'A';
832 ELSIF l_rec.payment_register = 'RG23C' THEN
833 v_reg_type := 'C';
834 END IF;
835 IF NVL(l_rec.EXCISE_EXEMPT_TYPE,'@@@') IN
836 ('CT2', 'EXCISE_EXEMPT_CERT' )
837 AND v_item_class NOT IN ('OTIN', 'OTEX')
838 THEN
839 v_reg_type := 'A';
840 OPEN for_modvat_percentage(v_org_id, v_loc_id);
841 FETCH for_modvat_percentage INTO v_modvat_tax_rate;
842 CLOSE for_modvat_percentage;
843 v_basic_ed := ROUND((l_rec.quantity_invoiced * l_rec.unit_selling_price
844 * v_modvat_tax_rate)/100);
845 v_remarks := 'Against Modvat Recovery'||'-'||l_rec.EXCISE_EXEMPT_TYPE;
846 ELSIF NVL(l_rec.EXCISE_EXEMPT_TYPE,'@@@') IN
847 ('CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH')
848 AND v_item_class NOT IN ('OTIN', 'OTEX')
849 THEN
850 OPEN for_modvat_tax_rate(l_rec.line_id);
851 FETCH for_modvat_tax_rate INTO v_modvat_tax_rate, v_rounding_factor;
852 CLOSE for_modvat_tax_rate;
853 v_basic_ed := (l_rec.quantity_invoiced * l_rec.unit_selling_price
854 * v_modvat_tax_rate)/100;
855 IF v_rounding_factor IS NOT NULL THEN
856 v_basic_ed := ROUND(v_basic_ed, v_rounding_factor);
857 ELSE
858 v_basic_ed := ROUND(v_basic_ed);
859 END IF;
860 v_remarks := 'Against Modvat Recovery'||'-'||l_rec.EXCISE_EXEMPT_TYPE;
861 END IF;
862 --2001/03/26 Manohar Mishra , added new parameter V_REG_CODE as below.
863 vsqlstmt := '23';
864 IF nvl(ln_external_flag, 0)= 0 THEN --Added by zhiwei for Bug#12537533 on 20110510
865 jai_om_rg_pkg.ja_in_rg23_part_II_entry(v_reg_code,v_reg_type, v_fin_year, v_org_id,
866 v_loc_id,
867 l_rec.inventory_item_id, 33, SYSDATE, v_part_i_register_id,
868 l_rec.excise_invoice_no, l_rec.excise_invoice_date ,
869 round(v_basic_ed), -- Vijay 2001/10/19
870 round(v_additional_ed), -- Vijay 2001/10/19
871 round(v_other_ed), -- Vijay 2001/10/19
872 v_ship_cust_id, v_ship_to_site_use_id,
873 v_source_name, v_category_name,
874 v_creation_date, v_created_by,v_last_update_date,
875 v_last_updated_by, v_last_update_login, l_rec.line_id,
876 l_rec.excise_exempt_type, v_remarks ,
877 v_ref_10,
878 v_ref_23,
879 v_ref_24,
880 v_ref_25,
881 v_ref_26
882 );
883 end if;--Added by zhiwei for Bug#12537533 on 20110510
884 vsqlstmt := '24';
885 ELSIF l_rec.payment_register IN ('PLA') THEN
886 vsqlstmt := '25';
887 IF nvl(ln_external_flag, 0)= 0 THEN --Added by zhiwei for Bug#12537533 on 20110510
888 jai_om_rg_pkg.ja_in_pla_entry(v_org_id,
889 v_loc_id,
890 l_rec.inventory_item_id,
891 v_fin_year,
892 33,
893 l_rec.line_id, -- modified by, aiyer for Bug #4541303
894 SYSDATE,
895 l_rec.excise_invoice_no ,
896 l_rec.excise_invoice_date ,
897 round(v_basic_ed), -- Vijay 2001/10/19
898 round(v_additional_ed),-- Vijay 2001/10/19
899 round(v_other_ed), -- Vijay 2001/10/19
900 v_ship_cust_id,
901 v_ship_to_site_use_id,
902 v_source_name,
903 v_category_name,
904 v_creation_date,
905 v_created_by,
906 v_last_update_date,
907 v_last_updated_by,
908 v_last_update_login ,
909 v_ref_10,
910 v_ref_23,
911 v_ref_24,
912 v_ref_25,
913 v_ref_26
914 );
915 end if;--Added by zhiwei for Bug#12537533 on 20110510
916 vsqlstmt := '26';
917 END IF;
918
919 --Added by Xiao for Open Interface ER bug#11683927 on 03-Mar-2011, begin
920 ------------------------------------------------------------------------------------
921 --For only for 'External' event, the program should only generate accounting.
922 --Accounting generation is invoked only in porcedure jai_om_rg_pkg.ja_in_pla_entry, and procedure
923 --jai_om_rg_pkg.ja_in_rg23_part_II_entry, so after the call for these two procedures,
924 --program returns, and RG register will not be updated.
925 IF ln_external_flag > 0 THEN
926 lv_ship_status := 'CLOSED' ;
927 UPDATE JAI_CMN_MATCH_RECEIPTS
928 SET ship_status = lv_ship_status
929 WHERE ref_line_id = l_rec.customer_trx_line_id;
930 RETURN;
931 END IF;
932 ------------------------------------------------------------------------------------
933 --Added by Xiao for Open Interface ER bug#11683927 on 03-Mar-2011, begin
934
935 IF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') THEN
936 SELECT JAI_CMN_RG_I_TRXS_S.CURRVAL INTO v_rg23_part_i_no from dual;
937 IF l_rec.payment_register IN( 'RG23A','RG23C') THEN
938 SELECT JAI_CMN_RG_23AC_II_TRXS_S.CURRVAL INTO v_rg23_part_ii_no from dual;
939 UPDATE JAI_CMN_RG_I_TRXS
940 SET register_id_part_ii = v_rg23_part_ii_no,
941 CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_23AC_II_TRXS
942 WHERE register_id = v_rg23_part_ii_no)
943 WHERE register_id = v_rg23_part_i_no;
944 ELSIF l_rec.payment_register IN( 'PLA') THEN
945 SELECT JAI_CMN_RG_PLA_TRXS_S1.CURRVAL INTO v_pla_register_no from dual;
946 UPDATE JAI_CMN_RG_I_TRXS
947 SET register_id_part_ii = v_pla_register_no,
948 CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_PLA_TRXS
949 WHERE register_id = v_pla_register_no)
950 WHERE register_id = v_rg23_part_i_no;
951 END IF;
952 ELSIF v_item_class IN ('RMIN','RMEX','CGIN','CGEX') THEN
953 SELECT JAI_CMN_RG_23AC_I_TRXS_S.CURRVAL INTO v_rg23_part_i_no from dual;
954 IF l_rec.payment_register IN( 'RG23A','RG23C') THEN
955 SELECT JAI_CMN_RG_23AC_II_TRXS_S.CURRVAL INTO v_rg23_part_ii_no from dual;
956 UPDATE JAI_CMN_RG_23AC_I_TRXS
957 SET REGISTER_ID_PART_II = v_rg23_part_ii_no,
958 CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_23AC_II_TRXS
959 WHERE register_id = v_rg23_part_ii_no)
960 WHERE register_id = v_rg23_part_i_no;
961 ELSIF l_rec.payment_register IN( 'PLA') THEN
962 SELECT JAI_CMN_RG_PLA_TRXS_S1.CURRVAL INTO v_pla_register_no from dual;
963 UPDATE JAI_CMN_RG_23AC_I_TRXS
964 SET REGISTER_ID_PART_II = v_pla_register_no,
965 CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_PLA_TRXS
966 WHERE register_id = v_pla_register_no)
967 WHERE register_id = v_rg23_part_i_no;
968 END IF;
969 END IF;
970 END IF;
971
972 end if; -- 3496577
973 IF v_reg_code IN ('BOND_REG','23D_EXPORT_WITHOUT_EXCISE') THEN
974 vsqlstmt := '27';
975 jai_om_rg_pkg.ja_in_register_txn_entry(
976 v_org_id,
977 v_loc_id,
978 l_rec.excise_invoice_no,
979 'BOND SALES',
980 'N',
981 l_rec.line_id,--v_customer_trx_id,
982 round(v_tax_amount * NVL(pr_new.exchange_rate ,1),2) , /* added by CSahoo - bug# 5390583 */
983 v_reg_code,
984 v_creation_date,
985 v_created_by,
986 v_last_update_date,
987 v_last_updated_by,
988 v_last_update_login ,
989 pr_new.Batch_source_id,
990 NVL(pr_new.exchange_Rate,1) /* added by CSahoo - bug# 5390583 */
991 );
992 vsqlstmt := '28';
993 END IF;
994 IF v_reg_code IN('23D_EXPORT_WITHOUT_EXCISE','23D_EXPORT_EXCISE',
995 '23D_DOMESTIC_EXCISE','23D_DOM_WITHOUT_EXCISE')
996 and
997 nvl(pr_new.update_rg23d_flag,'N') = 'Y' /*bduvarag for the bug4601570*/
998 then
999 if nvl(v_item_trading_flag,'N') = 'Y' then
1000 select sum(func_tax_amount) into v_duty_amount
1001 from JAI_AR_TRX_TAX_LINES
1002 where link_to_cust_trx_line_id=l_rec.customer_trx_line_id;
1003 FOR match_rec IN matched_receipt_cur(l_rec.customer_trx_line_id)
1004 LOOP
1005 FOR rate_rec IN tax_rate_cur(l_rec.customer_trx_line_id,
1006 match_rec.receipt_id)
1007 LOOP
1008 IF nvl(rate_rec.tax_rate,0) > 0 THEN
1009 v_tax_rate_counter := v_tax_rate_counter + 1;
1010 v_match_tax_rate := nvl(v_match_tax_rate,0)
1011 + nvl(rate_rec.tax_rate,0);
1012 END IF;
1013 /*
1014 and v_tax_rate_counter > 0 added by sriram for patchset bug
1015 because it was causing a divide by zero exception. Bug # 3273545
1016
1017 The variable v_match_tax_rate is not used anywhere further below the
1018 following if condition.However, I have not removed the code snippet
1019 to be on the safer side.
1020 */
1021 IF v_counter > 0 and v_tax_rate_counter > 0 THEN
1022 v_match_tax_rate := v_match_tax_rate/v_tax_rate_counter;
1023 END IF;
1024 END LOOP;
1025
1026 Select JAI_CMN_RG_23D_TRXS_S.NEXTVAL into v_register_id From Dual;
1027 IF match_rec.transaction_type = 'R'
1028 THEN
1029 v_rg23d_receipt_id := match_rec.receipt_id;
1030 ELSIF match_rec.transaction_type = 'CR'
1031 THEN
1032 v_oth_receipt_id := match_rec.receipt_id;
1033 END IF;
1034 vsqlstmt := '29';
1035 jai_om_rg_pkg.ja_in_rg23d_entry(
1036 v_register_id,
1037 v_org_id, v_loc_id,
1038 v_fin_year, 'I', l_rec.inventory_item_id,
1039 l_rec.customer_trx_line_id, l_rec.unit_code, l_rec.unit_code,
1040 v_ship_cust_id, v_sold_cust_id, v_ship_to_site_use_id,
1041 match_rec.quantity_applied,
1042 v_reg_code, match_rec.rate_per_unit,
1043 match_rec.excise_duty_rate,v_tax_amount ,null,
1044 v_source_name, v_category_name, null,null,
1045 v_creation_date,v_created_by,v_last_update_date,
1046 v_last_update_login,
1047 v_last_updated_by, null, null, null,
1048 l_rec.excise_invoice_no,--v_trx_number Bug # 3179653 passing excise invoice no instead of trx number,
1049 v_trx_date,
1050 v_ref_10,v_ref_23,v_ref_24,v_ref_25,v_ref_26);
1051 vsqlstmt := '30';
1052
1053 jai_cmn_rg_23d_trxs_pkg.upd_receipt_qty_matched(match_rec.receipt_id,match_rec.quantity_applied,
1054 match_rec.qty_to_adjust
1055 );
1056
1057 lv_ship_status := 'CLOSED' ;
1058
1059 UPDATE JAI_CMN_MATCH_RECEIPTS
1060 set ship_status = lv_ship_status --'CLOSED' /* Modified by Ramananda for removal of SQL LITERALs */
1061 where ref_line_id = l_rec.customer_trx_line_id;
1062 END LOOP;
1063 END IF;
1064 END IF;
1065 ELSE
1066 null;
1067 END IF;
1068 v_item_class := '';
1069 v_tax_amount := 0;
1070 v_basic_ed := 0;
1071 v_additional_ed := 0;
1072 v_other_ed := 0;
1073 v_average_duty := 0;
1074 v_counter := 0;
1075 v_tax_rate := 0;
1076 v_reg_type := '';
1077 END IF;
1078
1079 --Added the below by Anujsax for Bug#5636544
1080 IF l_rec.excise_invoice_no IS NOT NULL AND lv_excise_invoice_no IS NULL THEN
1081 lv_excise_invoice_no := l_rec.excise_invoice_no;
1082 --Ended by Anujsax for Bug#5636544
1083 END IF;
1084
1085 END LOOP;
1086 DELETE JAI_AR_TRX_INS_HDRS_T
1087 WHERE CUSTOMER_TRX_ID = v_customer_trx_id;
1088 --Start addition by Anujsax for Bug#5636544
1089 /* ssumaith - bug# 6901521
1090 IF lv_excise_invoice_no IS NOT NULL THEN
1091 result := fnd_request.set_mode(TRUE);
1092 req_id := fnd_request.submit_request( 'JA',
1093 'JAICMNCP',
1094 'India - Concurrent for updating the excise invoice no',
1095 SYSDATE,
1096 FALSE,
1097 'UPDATE EXCISE INVOICE NO',
1098 NULL,
1099 'Y',
1100 v_customer_trx_id,
1101 lv_excise_invoice_no);
1102 END IF;
1103 --End Addition by Anujsax for Bug#5636544
1104 ends by ssumaith - bug# 6601521 */
1105 END IF;
1106
1107 /*EXCEPTION
1108 WHEN OTHERS THEN
1109 VSQLERRM := SUBSTR(SQLERRM,1,240);
1110 INSERT INTO JAI_CMN_ERRORS_T
1111 (
1112 APPLICATION_SOURCE,
1113 ERROR_MESSAGE ,
1114 ADDITIONAL_ERROR_MESG,
1115 CREATION_DATE,
1116 CREATED_BY
1117 )
1118 VALUES
1119 ('JA_IN_AR_HDR_COMPLETE_TRG',
1120 'EXCEPTION OCCURED AT SQLSTMT' || vsqlstmt ,
1121 VSQLERRM,
1122 SYSDATE,
1123 USER
1124 );
1125 */
1126 /* Added an exception block by Ramananda for bug#4570303 */
1127 EXCEPTION
1128 WHEN OTHERS THEN
1129 Pv_return_code := jai_constants.unexpected_error;
1130 Pv_return_message := 'Encountered an error in JAI_JAR_TRXS_TRIGGER_PKG.ARU_T1 ' || substr(sqlerrm,1,1900);
1131
1132 END ARU_T1 ;
1133
1134 END JAI_JAR_TRXS_TRIGGER_PKG ;
[Home] [Help]