1 PACKAGE BODY JAI_JAR_TRXS_TRIGGER_PKG AS
2 /* $Header: jai_jar_t.plb 120.7.12010000.2 2009/01/05 12:46:00 csahoo 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 ln_trx_totcess_amt JAI_CMN_RG_I_TRXS.CESS_AMT%TYPE; /* End of bug 4346220 */
265 ln_trx_totshcess_amt JAI_CMN_RG_I_TRXS.SH_CESS_AMT%TYPE; /* added by ssawant for bug 5989740 */
266
267
268 /* End of bug 4566054 */
269
270 /* Bug 5243532. Added by Lakshmi Gopalsami
271 * Defined variable for implementing caching logic.
272 */
273 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
274 BEGIN
275 pv_return_code := jai_constants.successful ;
276 /*------------------------------------------------------------------------------------------
277 FILENAME: JA_IN_AR_HDR_COMPLETE_TRG.sql
278
279 CHANGE HISTORY:
280 S.No Date Author and Details
281 1. 2001/03/30 Jagdish
282 In case of Bond register transactions to calculate the excise_duty,modification
283 done to excise_cal_rec cursor.Search for Jagdish for more modifications
284
285 2. 2001/05/04 Vijay,Subbu.
286 Code commented and added for ST Form Tracking
287
288 3. 2001/06/22 Anuradha Parthasarathy
289 Code commented and added to improve performance.
290
291 4. 2003/03/14 Sriram - Bug # 2846277 -- File Version 615.1
292 Trigger was returning when organization id value is 0 . Organization id
293 value can be 0 in case of setup business group setup is done . Hence making it into
294 a large number (999999) instead of 0.
295
296 5. 2003/08/22 Sriram Bug # 3021588 (Bond Register Enhancement) Version 616.2
297
298 For Multiple Bond Register Enhancement,
299 Instead of using the cursors for fetching the register associated with the invoice type , a call has been made to the procedures
300 of the jai_cmn_bond_register_pkg package. There enhancement has created dependency because of the
301 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.
302
303 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
304
305 Provision for letter of undertaking has been incorporated. In the case of the letter of undetaking , its also a type of bond register
306 but without validation for the balances.
307 This has been done by checking if the LOU_FLAG is 'Y' in the JAI_OM_OE_BOND_REG_HDRS table for the
308 associated register id , if yes , then validation is only based on bond expiry date .
309
310 This bug has introduced huge dependency. All future bugs on this object should have this bug as a prereq
311
312 6. 2003/10/10 Ssumaith - bug # 3179653 File Version 616.3
313
314 When RG23D register is getting hit, instead of populating the excise invoice number in the commercial invoice no field (comm_invoice_no)
315 trx_number of the invoice is getting populated.This is wrong ans has been corrected in this fix.
316
317 7. 2003/11/11 ssumaith - bug # 3138194 File Version 616.4
318
319 ST forms population functionality has been removed from this trigger and instead moved to a new
320 concurrent program which does the exclusive job of population of ST form records into the tables.
321
322 8. 11-Nov-2003 Aiyer - Bug #3249375 File Version 617.1
323 References to JA_IN_OE_ST_FORMS_HDR table, which has been obsolete post IN60105D1 patchset, was found
324 in this file in some cursors.
325 As these tables do not exists in the database any more post application the above mentioned patchset
326 hence deleting the cursors.
327
328 Dependency Due to This Bug:-
329 Can be applied only post application of IN60105D1.
330
331 9. 21-Nov-03 Ssumaith Bug # 3273545
332
333 When only Adhoc Excise tax is attached to the invoice line and matched against a receipt and the invoice
334 completed from the base apps screen is causing error - Divide by Zero ORA-1476.
335
336 10. 2004/04/20 ssumaith - bug# 3496577
337
338 Made code changes such that payment register does not get hit when update_rg_flag is set to No. Only
339 quantity register gets hit.
340
341 11. 2005/01/28 ssumaith - bug#4136981
342
343 IN call to the ja_in_register_txn_entry procedure , passing the customer_Trx_line_id instead of customer_trx_id
344 Because , this procedure gets called from individual line in the JAI_AR_TRX_LINES table
345 when 'COMPLETE' action is done.
346
347 12. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
348 DB Entity as required for CASE COMPLAINCE. Version 116.2
349
350 13. 13-Jun-2005 Ramananda for bug#4428980. File Version: 116.2
351 Removal of SQL LITERALs is done
352
353 14. 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
354 GL Sources and GL Categories got changed. Refer bug for the details
355
356 15. 23-Aug-2005 Aiyer - Bug 4566054 (Forward porting for the 11.5 bug 4346220 ),Version 120.1
357 Issue :-
358 Rg does not show correct cess value in case of Shipment transactions.
359
360 Fix:-
361 Two fields cess_amt and source have been added in JAI_CMN_RG_I_TRXS table.
362 The cess amt and source would be populated from jai_jar_t_aru_t1 (Source -> 'AR' ) and
363 as 'WSH' from jai_om_wsh.plb procedure Shipment.
364 Corresponding changes have been done in the form JAINIRGI.fmb and JAFRMRG1.rdf .
365 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
366 exactly matches the jai_om_wsh_lines_all.delivery_detail_id and jai_ar_trxs.customer_trx_id the tracking of the source
367 becomes very difficult hence to have a clear demarcation between WSh and AR sources hence the source field has been added.
368
369 Added 2 new parametes p_cess_amt and p_source to jai_om_rg_pkg.ja_in_rg_i_entry package.
370 This has been populated from this and jai_om_wsh_pkg.process_delivery procedure.
371
372 A migration script has been provided to migrate the value for cess and source.
373
374 Dependency due to this bug:-
375 1. Datamodel change in table JAI_CMN_RG_I_TRXS, added the cess_amt and source fields
376 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
377 3. Modified the trigger jai_jar_t_aru_t1
378 4. Procedure jai_om_wsh_pkg.process_delivery
379 5. Report JAICMNRG1.rdf
380 6. Created a migration script to populate cess_amt and source for Shipment and Receivable transactions.
381 Both functional and technical dependencies exists
382
383
384 16. 23-Aug-2005 Aiyer - Bug# 4541303 (Forward porting for the 11.5 bug 4538315) 120.1
385
386 For a manual AR invoice with more than one line, the cess amount was being hit for the whole of the
387 invoice amount for each of the lines.
388
389 Code changes are done in the package jai_om_rg_pkg as well this trigger.
390
391 Code changes done in the package include calculating the cess amount for the current customer trx line id.
392
393 Code changes done in the trigger include sending the customer trx line id when pla is hit . This is inline
394 with the way JAI_CMN_RG_23AC_II_TRXS works.
395
396
397 Dependency Due to thus bug:-
398 jai_om_rg.plb (120.4)
399
400 17. 15-Feb-2007 CSahoo - BUG# 5390583, File Version 120.2
401 Forward Porting of 11i BUG 5357400
402 Issue : Excise amount not hitting bond register in functional currency.
403 Fix : Excise and cess amounts would hit bond register in functional currency.
404 Changes are done in three objects.
405
406 1. Package jai_om_rg_pkg. - Added a parameter to the ja_in_register_txn_entry called p_currency_rate
407 It holds the currency conversion rate which would be multiplied by the transaction amts to
408 get the functional amounts.
409
410 2. Package jai_jar_t.plb - In the call to the ja_in_register_txn_entry procedure
411 added the parameter called p_currency_code.
412
413 3. Package - jai_ract_trg_pkg - When a change is done in the invoice currency code from the front end
414 the change is being reflected in the JAI_AR_TRXS table.
415
416 Future Dependency due to this Bug
417 ------------------------
418 YES - A new parameter is added to the procedure - ja_in_register_txn_entry in the package jai_om_rg_pkg.
419 It has a technical dependency on jai_om_rg_pkg and Package jai_jar_t.plb.
420 It has functional dependency on jai_ract_trg.plb
421
422
423 18. 16-April-2007 ssawant for bug 5989740 ,File version 120.3
424 Forward porting Budget07-08 changes of handling secondary and
425 Higher Secondary Education Cess from 11.5( bug no 5907436) to R12 (bug no 5989740).
426
427 19. 28-Jun-2007 CSahoo for bug#6155839 , File Version 120.6
428 replaced RG Register Data Entry by jai_constants.je_category_rg_entry
429
430 20. 17-Sep-2007 Anujsax for bug#5636544 ,File Version 120.7
431 Forward porting for R11 bug 5629319 into R12 bug 5636544
432 Issue : excise_invoice_number need to be updated in the ra_customer_trx_all.ct_reference table.
433 Fix : 1) Stored the excise_invoice_no into a variable
434 2) Submitted the concurrent - JAICMNCP to update the excise invoice number
435 21. 05-Jan-2009 CSahoo for bug#7685000, File Version 120.7.12010000.2
436 ISSUE: TST1211.XB1 : CONCURRENT RUNS IN ERROR
437 FIX: commented the call to the conc request JAICMNCP : India - Concurrent for
438 updating the excise invoice no.
439
440
441 Future Dependencies For the release Of this Object:-
442 (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/
443 A datamodel change )
444
445 ----------------------------------------------------------------------------------------------------------------------------------------------------
446 Current Version Current Bug Dependent Files Version Author Date Remarks
447 Of File On Bug/Patchset Dependent On
448 ja_in_ar_hdr_complete_trg.sql
449 ----------------------------------------------------------------------------------------------------------------------------------------------------
450 616.1 3021588 IN60104D1 + ssumaith 22/08/2003 Bond Register Enhancement
451 2801751 +
452 2769440
453
454 617.1 3249375 IN60105D1 Aiyer 11/Nov/2003 Can be applied only after IN60105D1 patchset
455 has been applied.
456 12.0 4566054 jai_om_rg.pls 120.3 Aiyer 24-Aug-2005
457 jai_om_rg.plb 120.4
458 jai_om_wsh.plb (jai_om_wsh_pkg.process_delivery) 120.4
459 JAINIRGI.fmb 120.2
460 jain14.odf 120.3
461 jain14reg.ldt 120.3
462 New migration script to port data into new tables 120.0
463 JAICMNRG1.rdf 120.3
464 jai_jai_t.sql (trigger jai_jar_t_aru_t1) 120.1
465 17/5/2007 bduvarag for the bug#4601570, File version 120.4
466 Forward porting the changes done in the 11i bug#4474270
467
468 ----------------------------------------------------------------------------------------------------------------------------------------------------
469 --------------------------------------------------------------------------------------------*/
470 v_item_class := 'N'; --Ramananda for File.Sql.35
471 v_customer_trx_id := pr_old.customer_trx_id; --Ramananda for File.Sql.35
472 v_last_update_date := pr_new.last_update_date; --Ramananda for File.Sql.35
473 v_last_updated_by := pr_new.last_updated_by; --Ramananda for File.Sql.35
474 v_creation_date := SYSDATE; --Ramananda for File.Sql.35
475 v_created_by := pr_new.created_by;--Ramananda for File.Sql.35
476 v_last_update_login := pr_new.last_update_login; --Ramananda for File.Sql.35
477 v_set_of_books_id := pr_new.set_of_books_id; --Ramananda for File.Sql.35
478 v_currency_code := pr_new.invoice_currency_code; --Ramananda for File.Sql.35
479 v_conv_date := NVL(pr_new.exchange_date, pr_new.creation_date); --Ramananda for File.Sql.35
480 v_conv_type_code := pr_new.Exchange_Rate_Type; --Ramananda for File.Sql.35
481 v_conv_rate := pr_new.Exchange_rate; --Ramananda for File.Sql.35
482 --2001/05/04 Vijay,Subbu.
483 v_trx_number := pr_new.Trx_Number; --Ramananda for File.Sql.35
484 v_source_name := 'Receivables India'; --Ramananda for File.Sql.35
485 v_category_name := jai_constants.je_category_rg_entry ; -- modified by csahoo for bug#6155839 --'RG Register Data Entry'; --Ramananda for File.Sql.35
486 v_ref_10 := 'India Localization Entry for Invoice # '; -- will hold a standard text such as 'India Localization Entry for sales order'
487 v_ref_23 := 'ja_in_ar_hdr_complete_trg'; -- holds the object name -- 'ja_in_ar_hdr_complete_trg'
488 v_ref_24 := 'ra_customer_trx_lines_all'; -- holds the table name -- ' ra_customer_trx_all'
489 v_ref_25 := 'customer_trx_line_id'; -- holds the column name -- 'customer_trx_id'
490 v_ref_26 := v_customer_trx_id; -- holds the column value -- eg -- 13645
491
492 OPEN complete_info_cur;
493 FETCH complete_info_cur INTO v_ship_cust_id, v_ship_to_site_use_id,
494 v_cust_trx_type_id,
495 v_trx_date,v_sold_cust_id,v_batch_source_id,
496 v_bill_to_customer_id, v_bill_to_site_use_id; --2001/05/04 Vijay,Subbu.
497 CLOSE complete_info_cur;
498 IF v_trx_date IS NULL THEN
499 RETURN;
500 END IF;
501
502 OPEN org_loc_cur;
503 FETCH org_loc_cur INTO v_org_id, v_loc_id, v_reg_type, v_rg_flag,
504 v_complete_flag ;
505 CLOSE org_loc_cur;
506 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
507 -- which was causing code to return .- bug # 2846277
508 OPEN organization_cur;
509 FETCH organization_cur INTO v_org_id, v_loc_id;
510 CLOSE organization_cur;
511 END IF;
512 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
513 -- which was causing code to return .- bug # 2846277
514 RETURN;
515 END IF;
516
517
518
519 v_ref_10 := v_ref_10 || v_trx_number ;
520 vsqlstmt := '1';
521 -----Once Complete Button is Pressed Following code WILL tell you what will happ
522 --en at what stage
523 IF pr_new.ONCE_COMPLETED_FLAG <> pr_old.ONCE_COMPLETED_FLAG THEN
524 IF v_set_of_books_id IS NULL
525 THEN
526 /* Bug 5243532. Added by Lakshmi Gopalsami
527 * Removed the cursor set_of_books_cur and implemented using caching logic.
528 */
529 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
530 (p_org_id => v_org_id );
531 v_set_of_books_id := l_func_curr_det.ledger_id;
532 END IF;
533 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_set_of_books_id ,v_currency_code ,
534 v_conv_date ,v_conv_type_code, v_conv_rate);
535
536 vsqlstmt := '2';
537 /*
538
539 OPEN register_code_cur(v_org_id, v_loc_id,v_batch_source_id);
540 FETCH register_code_cur INTO v_reg_code;
541 CLOSE register_code_cur;
542 */
543
544 -- above code commented by sriram - bug # 3021588 and instead making a call to the jai_cmn_bond_register_pkg Package instead.
545
546 jai_cmn_bond_register_pkg.GET_REGISTER_ID(v_org_id,
547 v_loc_id,
548 v_batch_source_id,
549 'N',
550 v_register_id,
551 v_reg_code
552 );
553 vsqlstmt := '3';
554 OPEN fin_year_cur(v_org_id);
555 FETCH fin_year_cur INTO v_fin_year;
556 CLOSE fin_year_cur;
557 OPEN REG_BALANCE_CUR(v_org_id, v_loc_id);
558 FETCH REG_BALANCE_CUR INTO v_rg23a_bal,v_rg23c_bal,v_pla_bal;
559 CLOSE REG_BALANCE_CUR;
560 vsqlstmt := '4';
561 --2001/05/04 Vijay,Subbu.
562 OPEN get_opt_unit;
563 FETCH get_opt_unit INTO v_opt_unit;
564 CLOSE get_opt_unit;
565 --2001/05/04 Vijay,Subbu.
566 vsqlstmt := '5';
567 FOR l_rec in line_cur LOOP
568
569 v_ref_26 := l_rec.customer_trx_line_id; -- sriram - bug # 2967440
570
571 OPEN item_class_cur(v_org_id,l_rec.inventory_item_id);
572 FETCH item_class_cur INTO v_item_class , v_excise_flag,V_item_trading_flag;
573 CLOSE item_class_cur;
574
575 /* Changed by Sjha on 25/10/99 */
576 vsqlstmt := '5';
577 IF NVL(v_excise_flag,'N') = 'Y' THEN
578 FOR excise_cal_rec in excise_cal_cur(l_rec.line_id, l_rec.inventory_item_id,
579 v_org_id)
580 LOOP
581 IF v_reg_code in ('BOND_REG') THEN
582 -- 2001/03/30 Jagdish
583 vsqlstmt := '6';
584 IF excise_cal_rec.t_type IN ('Excise') THEN
585 v_basic_ed := NVL(v_basic_ed,0) +
586 NVL(excise_cal_rec.BASE_TAX_AMT * (excise_cal_rec.t_rate)/100 ,0);
587 v_tax_rate := NVL(v_tax_rate,0) + NVL(excise_cal_rec.t_rate,0);
588 vsqlstmt := '7';
589 IF NVL(excise_cal_rec.t_rate,0) > 0 THEN
590 v_counter := v_counter + 1;
591 END IF;
592 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
593 vsqlstmt := '8';
594 v_additional_ed := NVL(v_additional_ed,0) +
595 NVL(excise_cal_rec.BASE_TAX_AMT * (excise_cal_rec.t_rate)/100 ,0);
596 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
597 vsqlstmt := '9';
598 v_other_ed := NVL(v_other_ed,0) +
599 NVL(excise_cal_rec.BASE_TAX_AMT * (excise_cal_rec.t_rate)/100 ,0);
600 END IF;
601 ELSE
602 IF excise_cal_rec.t_type IN ('Excise') THEN
603 vsqlstmt := '10';
604 v_basic_ed := NVL(v_basic_ed,0) + NVL(excise_cal_rec.func_amt,0);
605 v_tax_rate := NVL(v_tax_rate,0) + NVL(excise_cal_rec.t_rate,0);
606 IF NVL(excise_cal_rec.t_rate,0) > 0 THEN
607 vsqlstmt := '11';
608 v_counter := v_counter + 1;
609 END IF;
610 ELSIF excise_cal_rec.t_type IN ('Addl. Excise') THEN
611 vsqlstmt := '12';
612 v_additional_ed := NVL(v_additional_ed,0) +NVL(excise_cal_rec.func_amt,0);
613 ELSIF excise_cal_rec.t_type IN ('Other Excise') THEN
614 vsqlstmt := '13';
615 v_other_ed := NVL(v_other_ed,0) + NVL(excise_cal_rec.func_amt,0);
616 END IF;
617 END IF;
618 ---ghankot cmmented on 25-oct-99
619 ---ghankot commented on 25-oct-99
620 -- END IF;
621 END LOOP;
622 IF NVL(v_counter,0) = 0 THEN
623 v_counter := 1;
624 END IF;
625 vsqlstmt := '14';
626 v_average_duty := NVL(v_tax_rate,0)/v_counter;
627 vsqlstmt := '15';
628 v_average_duty := TRUNC(v_average_duty,2);
629 v_tax_amount := NVL(v_basic_ed,0) + NVL(v_additional_ed,0) +
630 NVL(v_other_ed,0);
631 IF v_item_class IN ('RMIN','RMEX','CGEX','CGIN','FGIN','FGEX','CCIN','CCEX')
632 THEN
633 vsqlstmt := '16';
634
635 v_assessable_value := NVL(v_converted_rate,0) * nvl(l_rec.assessable_value,0);
636 IF v_reg_code in ('DOMESTIC_EXCISE','EXPORT_EXCISE',
637 'DOMESTIC_WITHOUT_EXCISE', 'BOND_REG') THEN
638 vsqlstmt := '17';
639 /*
640 || Code added for the bug 4566054
641 || Initialize the ln_trx_totcess_amt variable to null;
642 */
643
644 ln_trx_totcess_amt := null;
645 ln_trx_totshcess_amt := null; /* added by ssawant for bug 5989740 */
646
647 /*
648 || Start of bug 4566054
649 ||Code added by aiyer for the bug 4566054
650 ||The cess amount is also being maintained in jai_cmn_rg_i_trxs table at a delivery_detail_level
651 ||hence calculate the cess and pass it to the procedure jai_om_rg_pkg.ja_in_rg_i_entry with source as 'WSH'
652 */
653
654 OPEN cur_get_trx_cess_amt(l_rec.line_id) ;
655 FETCH cur_get_trx_cess_amt INTO ln_trx_totcess_amt;
656 CLOSE cur_get_trx_cess_amt ;
657 /* End of bug 4566054 */
658
659 /* added by ssawant for bug 5989740 */
660 OPEN cur_get_trx_sh_cess_amt(l_rec.line_id) ;
661 FETCH cur_get_trx_sh_cess_amt INTO ln_trx_totshcess_amt;
662 CLOSE cur_get_trx_sh_cess_amt ;
663
664
665
666 IF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') THEN
667 vsqlstmt := '18';
668 IF l_rec.payment_register = 'RG23A' THEN
669 v_reg_type := 'A';
670 v_payment_register := 'RG23A';
671 ELSIF l_rec.payment_register = 'RG23C' THEN
672 v_reg_type := 'C';
673 v_payment_register := 'RG23A';
674 ELSIF l_rec.payment_register = 'PLA' THEN
675 v_payment_register := 'PLA';
676 END IF;
677 vsqlstmt := '19';
678 jai_om_rg_pkg.ja_in_rg_I_entry(
679
680 p_fin_year => v_fin_year ,
681 p_org_id => v_org_id ,
682 p_location_id => v_loc_id ,
683 p_inventory_item_id => l_rec.inventory_item_id ,
684 p_transaction_id => 33 ,
685 p_transaction_date => SYSDATE ,
686 p_transaction_type => 'I' ,
687 p_header_id => v_customer_trx_id ,
688 p_excise_quantity => l_rec.quantity_invoiced ,
689 p_excise_amount => v_tax_amount ,
690 p_uom_code => l_rec.unit_code ,
691 p_excise_invoice_no => l_rec.excise_invoice_no ,
692 p_excise_invoice_date => l_rec.excise_invoice_date ,
693 p_payment_register => v_payment_register ,
694 p_basic_ed => v_basic_ed ,
695 p_additional_ed => v_additional_ed ,
696 p_other_ed => v_other_ed ,
697 p_excise_duty_rate => v_average_duty ,
698 p_customer_id => v_ship_cust_id ,
699 p_customer_site_id => v_ship_to_site_use_id ,
700 p_register_code => v_reg_code ,
701 p_creation_date => v_creation_date ,
702 p_created_by => v_created_by ,
703 p_last_update_date => v_last_update_date ,
704 p_last_updated_by => v_last_updated_by ,
705 p_last_update_login => v_last_update_login ,
706 p_assessable_value => v_assessable_value ,
707 p_cess_amt => ln_trx_totcess_amt , /*Parameters p_cess_amt and p_source added by aiyer for the bug 4566054 */
708 p_sh_cess_amt => ln_trx_totshcess_amt , /* added by ssawant for bug 5989740 */
709 p_source => jai_constants.source_ar
710 );
711
712
713 vsqlstmt := '20';
714 SELECT JAI_CMN_RG_I_TRXS_S.CURRVAL INTO v_part_i_register_id from dual;
715 ELSIF v_item_class IN ('CGEX','CGIN') THEN
716 v_reg_type := 'C';
717 ELSIF v_item_class IN ('RMIN','RMEX') THEN
718 v_reg_type := 'A';
719 END IF;
720 IF v_item_class IN ('RMIN','RMEX','CGIN','CGEX') THEN
721 vsqlstmt := '21';
722 jai_om_rg_pkg.ja_in_rg23_part_I_entry(v_reg_type, v_fin_year, v_org_id,
723 v_loc_id,
724 l_rec.inventory_item_id, 33, SYSDATE, 'I',
725 l_rec.quantity_invoiced, l_rec.unit_code, l_rec.excise_invoice_no,
726 l_rec.excise_invoice_date, v_basic_ed, v_additional_ed,
727 v_other_ed, v_ship_cust_id, v_ship_to_site_use_id,
728 v_customer_trx_id, SYSDATE, v_reg_code,
729 v_creation_date, v_created_by,v_last_update_date,
730 v_last_updated_by, v_last_update_login );
731 vsqlstmt := '22';
732 SELECT JAI_CMN_RG_23AC_I_TRXS_S.CURRVAL INTO v_part_i_register_id from dual;
733 END IF;
734 END IF;
735 IF v_reg_code in ('DOMESTIC_EXCISE','EXPORT_EXCISE') THEN
736
737
738
739 -- to code such that payment register does not get hit when update_rg_flag is set to No
740
741 if pr_new.update_rg_flag = 'Y' or pr_old.update_rg_flag = 'Y' then -- 3496577
742
743
744 IF l_rec.payment_register IN( 'RG23A','RG23C') THEN
745 IF l_rec.payment_register = 'RG23A' THEN
746 v_reg_type := 'A';
747 ELSIF l_rec.payment_register = 'RG23C' THEN
748 v_reg_type := 'C';
749 END IF;
750 IF NVL(l_rec.EXCISE_EXEMPT_TYPE,'@@@') IN
751 ('CT2', 'EXCISE_EXEMPT_CERT' )
752 AND v_item_class NOT IN ('OTIN', 'OTEX')
753 THEN
754 v_reg_type := 'A';
755 OPEN for_modvat_percentage(v_org_id, v_loc_id);
756 FETCH for_modvat_percentage INTO v_modvat_tax_rate;
757 CLOSE for_modvat_percentage;
758 v_basic_ed := ROUND((l_rec.quantity_invoiced * l_rec.unit_selling_price
759 * v_modvat_tax_rate)/100);
760 v_remarks := 'Against Modvat Recovery'||'-'||l_rec.EXCISE_EXEMPT_TYPE;
761 ELSIF NVL(l_rec.EXCISE_EXEMPT_TYPE,'@@@') IN
762 ('CT2_OTH', 'EXCISE_EXEMPT_CERT_OTH')
763 AND v_item_class NOT IN ('OTIN', 'OTEX')
764 THEN
765 OPEN for_modvat_tax_rate(l_rec.line_id);
766 FETCH for_modvat_tax_rate INTO v_modvat_tax_rate, v_rounding_factor;
767 CLOSE for_modvat_tax_rate;
768 v_basic_ed := (l_rec.quantity_invoiced * l_rec.unit_selling_price
769 * v_modvat_tax_rate)/100;
770 IF v_rounding_factor IS NOT NULL THEN
771 v_basic_ed := ROUND(v_basic_ed, v_rounding_factor);
772 ELSE
773 v_basic_ed := ROUND(v_basic_ed);
774 END IF;
775 v_remarks := 'Against Modvat Recovery'||'-'||l_rec.EXCISE_EXEMPT_TYPE;
776 END IF;
777 --2001/03/26 Manohar Mishra , added new parameter V_REG_CODE as below.
778 vsqlstmt := '23';
779 jai_om_rg_pkg.ja_in_rg23_part_II_entry(v_reg_code,v_reg_type, v_fin_year, v_org_id,
780 v_loc_id,
781 l_rec.inventory_item_id, 33, SYSDATE, v_part_i_register_id,
782 l_rec.excise_invoice_no, l_rec.excise_invoice_date ,
783 round(v_basic_ed), -- Vijay 2001/10/19
784 round(v_additional_ed), -- Vijay 2001/10/19
785 round(v_other_ed), -- Vijay 2001/10/19
786 v_ship_cust_id, v_ship_to_site_use_id,
787 v_source_name, v_category_name,
788 v_creation_date, v_created_by,v_last_update_date,
789 v_last_updated_by, v_last_update_login, l_rec.line_id,
790 l_rec.excise_exempt_type, v_remarks ,
791 v_ref_10,
792 v_ref_23,
793 v_ref_24,
794 v_ref_25,
795 v_ref_26
796 );
797 vsqlstmt := '24';
798 ELSIF l_rec.payment_register IN ('PLA') THEN
799 vsqlstmt := '25';
800 jai_om_rg_pkg.ja_in_pla_entry(v_org_id,
801 v_loc_id,
802 l_rec.inventory_item_id,
803 v_fin_year,
804 33,
805 l_rec.line_id, -- modified by, aiyer for Bug #4541303
806 SYSDATE,
807 l_rec.excise_invoice_no ,
808 l_rec.excise_invoice_date ,
809 round(v_basic_ed), -- Vijay 2001/10/19
810 round(v_additional_ed),-- Vijay 2001/10/19
811 round(v_other_ed), -- Vijay 2001/10/19
812 v_ship_cust_id,
813 v_ship_to_site_use_id,
814 v_source_name,
815 v_category_name,
816 v_creation_date,
817 v_created_by,
818 v_last_update_date,
819 v_last_updated_by,
820 v_last_update_login ,
821 v_ref_10,
822 v_ref_23,
823 v_ref_24,
824 v_ref_25,
825 v_ref_26
826 );
827 vsqlstmt := '26';
828 END IF;
829 IF v_item_class IN ('FGIN','FGEX','CCIN','CCEX') THEN
830 SELECT JAI_CMN_RG_I_TRXS_S.CURRVAL INTO v_rg23_part_i_no from dual;
831 IF l_rec.payment_register IN( 'RG23A','RG23C') THEN
832 SELECT JAI_CMN_RG_23AC_II_TRXS_S.CURRVAL INTO v_rg23_part_ii_no from dual;
833 UPDATE JAI_CMN_RG_I_TRXS
834 SET register_id_part_ii = v_rg23_part_ii_no,
835 CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_23AC_II_TRXS
836 WHERE register_id = v_rg23_part_ii_no)
837 WHERE register_id = v_rg23_part_i_no;
838 ELSIF l_rec.payment_register IN( 'PLA') THEN
839 SELECT JAI_CMN_RG_PLA_TRXS_S1.CURRVAL INTO v_pla_register_no from dual;
840 UPDATE JAI_CMN_RG_I_TRXS
841 SET register_id_part_ii = v_pla_register_no,
842 CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_PLA_TRXS
843 WHERE register_id = v_pla_register_no)
844 WHERE register_id = v_rg23_part_i_no;
845 END IF;
846 ELSIF v_item_class IN ('RMIN','RMEX','CGIN','CGEX') THEN
847 SELECT JAI_CMN_RG_23AC_I_TRXS_S.CURRVAL INTO v_rg23_part_i_no from dual;
848 IF l_rec.payment_register IN( 'RG23A','RG23C') THEN
849 SELECT JAI_CMN_RG_23AC_II_TRXS_S.CURRVAL INTO v_rg23_part_ii_no from dual;
850 UPDATE JAI_CMN_RG_23AC_I_TRXS
851 SET REGISTER_ID_PART_II = v_rg23_part_ii_no,
852 CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_23AC_II_TRXS
853 WHERE register_id = v_rg23_part_ii_no)
854 WHERE register_id = v_rg23_part_i_no;
855 ELSIF l_rec.payment_register IN( 'PLA') THEN
856 SELECT JAI_CMN_RG_PLA_TRXS_S1.CURRVAL INTO v_pla_register_no from dual;
857 UPDATE JAI_CMN_RG_23AC_I_TRXS
858 SET REGISTER_ID_PART_II = v_pla_register_no,
859 CHARGE_ACCOUNT_ID = (SELECT CHARGE_ACCOUNT_ID FROM JAI_CMN_RG_PLA_TRXS
860 WHERE register_id = v_pla_register_no)
861 WHERE register_id = v_rg23_part_i_no;
862 END IF;
863 END IF;
864 END IF;
865
866 end if; -- 3496577
867 IF v_reg_code IN ('BOND_REG','23D_EXPORT_WITHOUT_EXCISE') THEN
868 vsqlstmt := '27';
869 jai_om_rg_pkg.ja_in_register_txn_entry(
870 v_org_id,
871 v_loc_id,
872 l_rec.excise_invoice_no,
873 'BOND SALES',
874 'N',
875 l_rec.line_id,--v_customer_trx_id,
876 round(v_tax_amount * NVL(pr_new.exchange_rate ,1),2) , /* added by CSahoo - bug# 5390583 */
877 v_reg_code,
878 v_creation_date,
879 v_created_by,
880 v_last_update_date,
881 v_last_updated_by,
882 v_last_update_login ,
883 pr_new.Batch_source_id,
884 NVL(pr_new.exchange_Rate,1) /* added by CSahoo - bug# 5390583 */
885 );
886 vsqlstmt := '28';
887 END IF;
888 IF v_reg_code IN('23D_EXPORT_WITHOUT_EXCISE','23D_EXPORT_EXCISE',
889 '23D_DOMESTIC_EXCISE','23D_DOM_WITHOUT_EXCISE')
890 and
891 nvl(pr_new.update_rg23d_flag,'N') = 'Y' /*bduvarag for the bug4601570*/
892 then
893 if nvl(v_item_trading_flag,'N') = 'Y' then
894 select sum(func_tax_amount) into v_duty_amount
895 from JAI_AR_TRX_TAX_LINES
896 where link_to_cust_trx_line_id=l_rec.customer_trx_line_id;
897 FOR match_rec IN matched_receipt_cur(l_rec.customer_trx_line_id)
898 LOOP
899 FOR rate_rec IN tax_rate_cur(l_rec.customer_trx_line_id,
900 match_rec.receipt_id)
901 LOOP
902 IF nvl(rate_rec.tax_rate,0) > 0 THEN
903 v_tax_rate_counter := v_tax_rate_counter + 1;
904 v_match_tax_rate := nvl(v_match_tax_rate,0)
905 + nvl(rate_rec.tax_rate,0);
906 END IF;
907 /*
908 and v_tax_rate_counter > 0 added by sriram for patchset bug
909 because it was causing a divide by zero exception. Bug # 3273545
910
911 The variable v_match_tax_rate is not used anywhere further below the
912 following if condition.However, I have not removed the code snippet
913 to be on the safer side.
914 */
915 IF v_counter > 0 and v_tax_rate_counter > 0 THEN
916 v_match_tax_rate := v_match_tax_rate/v_tax_rate_counter;
917 END IF;
918 END LOOP;
919
920 Select JAI_CMN_RG_23D_TRXS_S.NEXTVAL into v_register_id From Dual;
921 IF match_rec.transaction_type = 'R'
922 THEN
923 v_rg23d_receipt_id := match_rec.receipt_id;
924 ELSIF match_rec.transaction_type = 'CR'
925 THEN
926 v_oth_receipt_id := match_rec.receipt_id;
927 END IF;
928 vsqlstmt := '29';
929 jai_om_rg_pkg.ja_in_rg23d_entry(
930 v_register_id,
931 v_org_id, v_loc_id,
932 v_fin_year, 'I', l_rec.inventory_item_id,
933 l_rec.customer_trx_line_id, l_rec.unit_code, l_rec.unit_code,
934 v_ship_cust_id, v_sold_cust_id, v_ship_to_site_use_id,
935 match_rec.quantity_applied,
936 v_reg_code, match_rec.rate_per_unit,
937 match_rec.excise_duty_rate,v_tax_amount ,null,
938 v_source_name, v_category_name, null,null,
939 v_creation_date,v_created_by,v_last_update_date,
940 v_last_update_login,
941 v_last_updated_by, null, null, null,
942 l_rec.excise_invoice_no,--v_trx_number Bug # 3179653 passing excise invoice no instead of trx number,
943 v_trx_date,
944 v_ref_10,v_ref_23,v_ref_24,v_ref_25,v_ref_26);
945 vsqlstmt := '30';
946
947 jai_cmn_rg_23d_trxs_pkg.upd_receipt_qty_matched(match_rec.receipt_id,match_rec.quantity_applied,
948 match_rec.qty_to_adjust
949 );
950
951 lv_ship_status := 'CLOSED' ;
952
953 UPDATE JAI_CMN_MATCH_RECEIPTS
954 set ship_status = lv_ship_status --'CLOSED' /* Modified by Ramananda for removal of SQL LITERALs */
955 where ref_line_id = l_rec.customer_trx_line_id;
956 END LOOP;
957 END IF;
958 END IF;
959 ELSE
960 null;
961 END IF;
962 v_item_class := '';
963 v_tax_amount := 0;
964 v_basic_ed := 0;
965 v_additional_ed := 0;
966 v_other_ed := 0;
967 v_average_duty := 0;
968 v_counter := 0;
969 v_tax_rate := 0;
970 v_reg_type := '';
971 END IF;
972
973 --Added the below by Anujsax for Bug#5636544
974 IF l_rec.excise_invoice_no IS NOT NULL AND lv_excise_invoice_no IS NULL THEN
975 lv_excise_invoice_no := l_rec.excise_invoice_no;
976 --Ended by Anujsax for Bug#5636544
977 END IF;
978
979 END LOOP;
980 DELETE JAI_AR_TRX_INS_HDRS_T
981 WHERE CUSTOMER_TRX_ID = v_customer_trx_id;
982 --Start addition by Anujsax for Bug#5636544
983 /* commented this code for bug#7685000, csahoo
984 IF lv_excise_invoice_no IS NOT NULL THEN
985 result := fnd_request.set_mode(TRUE);
986 req_id := fnd_request.submit_request( 'JA',
987 'JAICMNCP',
988 'India - Concurrent for updating the excise invoice no',
989 SYSDATE,
990 FALSE,
991 'UPDATE EXCISE INVOICE NO',
992 NULL,
993 'Y',
994 v_customer_trx_id,
995 lv_excise_invoice_no);
996 END IF;
997 --End Addition by Anujsax for Bug#5636544
998 bug#7685000, csahoo, end*/
999
1000 END IF;
1001
1002 /*EXCEPTION
1003 WHEN OTHERS THEN
1004 VSQLERRM := SUBSTR(SQLERRM,1,240);
1005 INSERT INTO JAI_CMN_ERRORS_T
1006 (
1007 APPLICATION_SOURCE,
1008 ERROR_MESSAGE ,
1009 ADDITIONAL_ERROR_MESG,
1010 CREATION_DATE,
1011 CREATED_BY
1012 )
1013 VALUES
1014 ('JA_IN_AR_HDR_COMPLETE_TRG',
1015 'EXCEPTION OCCURED AT SQLSTMT' || vsqlstmt ,
1016 VSQLERRM,
1017 SYSDATE,
1018 USER
1019 );
1020 */
1021 /* Added an exception block by Ramananda for bug#4570303 */
1022 EXCEPTION
1023 WHEN OTHERS THEN
1024 Pv_return_code := jai_constants.unexpected_error;
1025 Pv_return_message := 'Encountered an error in JAI_JAR_TRXS_TRIGGER_PKG.ARU_T1 ' || substr(sqlerrm,1,1900);
1026
1027 END ARU_T1 ;
1028
1029 END JAI_JAR_TRXS_TRIGGER_PKG ;
[Home] [Help]