[Home] [Help]
PACKAGE BODY: APPS.JAI_RCV_RND_PKG
Source
1 PACKAGE BODY jai_rcv_rnd_pkg AS
2 /* $Header: jai_rcv_rnd.plb 120.12 2012/05/23 07:39:27 zxin ship $ */
3
4
5
6 PROCEDURE do_rounding(
7 p_err_buf OUT NOCOPY VARCHAR2,
8 p_ret_code OUT NOCOPY NUMBER,
9 P_ORGANIZATION_ID IN NUMBER,
10 P_TRANSACTION_TYPE IN VARCHAR2, -- AT PRESENT THIS CAN CONTAIN ONLY ONE VALUE 'RECEIVE'. IN FUTURE THIS MAY CONTAIN MANY VALUES
11 P_REGISTER_TYPE IN VARCHAR2, -- CAN BE EITHER A OR C
12 PV_EX_INVOICE_FROM_DATE IN VARCHAR2, /* rallamse bug#4336482 changed to VARCHAR2 from DATE */
13 PV_EX_INVOICE_TO_DATE IN VARCHAR2 /* rallamse bug#4336482 changed to VARCHAR2 from DATE */
14 ) IS
15
16 /* Added by Ramananda for bug#4407165 */
17 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_rnd_pkg.do_rounding';
18
19 /* rallamse bug#4336482 */
20 P_EX_INVOICE_FROM_DATE DATE ; --File.Sql.35 Cbabu DEFAULT fnd_date.canonical_to_date(PV_EX_INVOICE_FROM_DATE);
21 P_EX_INVOICE_TO_DATE DATE ; --File.Sql.35 Cbabu DEFAULT fnd_date.canonical_to_date(PV_EX_INVOICE_TO_DATE);
22 /* End of Bug# 4336482 */
23
24 --Added by Sanjikum for Bug #4049363
25 TYPE amount_record IS RECORD( basic NUMBER,
26 additional NUMBER,
27 additional_cvd NUMBER, /* 5228046 change by sacsethi */
28 other NUMBER,
29 excise_edu_cess NUMBER,
30 cvd_edu_cess NUMBER,
31 sh_excise_edu_cess NUMBER, -- Date 16/04/2007 by
32 sh_cvd_edu_cess NUMBER, -- sacsethi for Bug#5989740
33 total NUMBER);
34
35 v_zero_record AMOUNT_RECORD;
36
37 --Added the below by Sanjikum for Bug #4049363
38 v_tot_amount AMOUNT_RECORD;
39 v_tot_rounded_amount AMOUNT_RECORD;
40 v_rounded_amount AMOUNT_RECORD;
41 v_rounded_amount_abs NUMBER;
42 v_rounded_amount_rg23 NUMBER;
43
44 /*
45 v_rounded_cr_amount AMOUNT_RECORD;
46 v_rounded_dr_amount AMOUNT_RECORD;
47 */
48 v_rounded_cr_amount NUMBER;
49 v_rounded_dr_amount NUMBER;
50
51 v_rounded_cr_rg23_amount AMOUNT_RECORD;
52 v_rounded_dr_rg23_amount AMOUNT_RECORD;
53 v_rounded_cr_oth_amount AMOUNT_RECORD;
54 v_rounded_dr_oth_amount AMOUNT_RECORD;
55
56 v_rounding_entry_type VARCHAR2(2);
57
58 v_commit_interval NUMBER(5) ; --File.Sql.35 Cbabu := 50;
59 v_rounding_precision NUMBER(2) ; --File.Sql.35 Cbabu := 0;
60 v_acct_type VARCHAR2(20) ; --File.Sql.35 Cbabu := 'REGULAR';
61 v_acct_nature VARCHAR2(20) ; --File.Sql.35 Cbabu := 'CENVAT-ROUNDING';
62 v_source_name VARCHAR2(20) ; --File.Sql.35 Cbabu := 'Purchasing India';
63 v_category_name VARCHAR2(20) ; --File.Sql.35 Cbabu := 'Receiving India';
64 v_statement_no VARCHAR2(4) ; --File.Sql.35 Cbabu := '0';
65 v_err_message VARCHAR2(100) ; --File.Sql.35 Cbabu := '';
66
67 v_rounding_entries_made NUMBER ; --File.Sql.35 Cbabu := 0;
68 v_tot_errored_entries NUMBER ; --File.Sql.35 Cbabu := 0;
69 v_zero_round_found NUMBER ; --File.Sql.35 Cbabu := 0;
70 v_tot_processed_invoices NUMBER ; --File.Sql.35 Cbabu := 0;
71 v_no_of_invoices_posted NUMBER ; --File.Sql.35 Cbabu := 0;
72 v_save_point_set BOOLEAN ; --File.Sql.35 Cbabu := FALSE;
73
74 v_fin_year NUMBER(4);
75
76 v_vendor_id NUMBER;
77 v_vendor_site_id NUMBER;
78 v_rg23_balance NUMBER;
79
80 v_modvat_rm_account_id NUMBER(15);
81 v_modvat_cg_account_id NUMBER(15);
82 v_rg_rounding_account_id NUMBER(15);
83
84 v_rg_account_id NUMBER(15);
85
86 v_register_id_part_ii NUMBER;
87 v_rounding_id NUMBER;
88
89 v_created_by NUMBER ; --File.Sql.35 Cbabu := nvl(FND_GLOBAL.USER_ID, -1);
90 v_last_update_login NUMBER ; --File.Sql.35 Cbabu := nvl(FND_GLOBAL.LOGIN_ID,- 1);
91 v_today DATE ; --File.Sql.35 Cbabu := trunc(SYSDATE);
92
93 v_shipment_header_id NUMBER;
94 v_excise_invoice_no JAI_CMN_RG_23AC_II_TRXS.excise_invoice_no%TYPE;
95 v_excise_invoice_date JAI_CMN_RG_23AC_II_TRXS.excise_invoice_date%TYPE;
96 v_register_type JAI_CMN_RG_23AC_II_TRXS.register_type%TYPE;
97 v_line_type_a_cnt NUMBER ; --File.Sql.35 Cbabu := 0;
98 v_line_type_c_cnt NUMBER ; --File.Sql.35 Cbabu := 0;
99 v_tot_lines_cnt NUMBER ; --File.Sql.35 Cbabu := 0;
100
101 -- Following values are used for the below variable
102 -- 0 => RMIN Items, 1 => CGIN Items, 2 => Both RMIN and CGIN items
103 v_rounding_type NUMBER(1);
104
105 v_enable_trace FND_CONCURRENT_PROGRAMS.enable_trace%TYPE;
106 v_sid v$session.sid%type;
107 v_serial v$session.serial#%type;
108 v_spid v$process.spid%type;
109 v_name1 v$database.name%type;
110
111 CURSOR c_enable_trace(cp_conc_pname fnd_concurrent_programs.concurrent_program_name%type) IS
112 SELECT enable_trace
113 FROM fnd_concurrent_programs
114 WHERE concurrent_program_name = cp_conc_pname ; --'JAINRGRND';
115
116 CURSOR get_audsid IS
117 SELECT a.sid, a.serial#, b.spid
118 FROM v$session a, v$process b
119 WHERE audsid = userenv('SESSIONID')
120 AND a.paddr = b.addr;
121
122 CURSOR get_dbname IS SELECT name FROM v$database;
123
124 CURSOR c_vendor(p_shipment_header_id IN NUMBER) IS
125 SELECT vendor_id, vendor_site_id, receipt_num
126 FROM rcv_shipment_headers
127 WHERE shipment_header_id = p_shipment_header_id;
128
129 v_slno NUMBER;
130 v_balance NUMBER;
131
132 CURSOR c_slno_balance(p_organization_id IN NUMBER, p_location_id IN NUMBER,
133 p_fin_year IN NUMBER, p_register_type IN VARCHAR2) IS
134 SELECT slno, closing_balance
135 FROM JAI_CMN_RG_23AC_II_TRXS
136 WHERE organization_id = p_organization_id
137 AND location_id = p_location_id
138 AND fin_year = p_fin_year
139 AND register_type = p_register_type
140 AND slno = (SELECT max(slno) slno
141 FROM JAI_CMN_RG_23AC_II_TRXS
142 WHERE organization_id = p_organization_id
143 AND location_id = p_location_id
144 AND fin_year = p_fin_year
145 AND register_type = p_register_type);
146
147 --Added by Sanjikum for Bug #4049363
148 /*Bug 5141459 bduvarag start*/
149 /* CURSOR c_rg23_cess_balance( p_organization_id IN NUMBER,
150 p_location_id IN NUMBER,
151 p_register_type IN VARCHAR2,
152 p_tax_type IN VARCHAR2) is
153 Select nvl(a.closing_balance,0) closing_balance
154 from JAI_CMN_RG_OTHERS a
155 Where a.source_type = 1 --1 is for JAI_CMN_RG_23AC_II_TRXS
156 AND a.source_register = DECODE(p_register_type,'A',jai_constants.reg_rg23a_2, 'C', jai_constants.reg_rg23c_2) --'RG23A_P2','C','RG23C_P2')
157 and a.tax_type = p_tax_type
158 AND abs(a.source_register_id) IN (Select max(abs(c.source_register_id))
159 from JAI_CMN_RG_23AC_II_TRXS b,
160 JAI_CMN_RG_OTHERS c
161 Where c.source_type = 1 --1 is for JAI_CMN_RG_23AC_II_TRXS
162 AND c.source_register = DECODE(p_register_type,'A',jai_constants.reg_rg23a_2, 'C', jai_constants.reg_rg23c_2) --'RG23A_P2','C','RG23C_P2')
163 AND b.register_id = c.source_register_id
164 AND b.organization_id = p_organization_id
165 and b.location_id = p_location_id
166 and c.tax_type = p_tax_type
167 and b.register_type = P_register_type);*/
168 /*Bug 5141459 bduvarag end*/
169
170 CURSOR c_active_fin_year(p_organization_id IN NUMBER) IS
171 SELECT max(fin_year)
172 FROM JAI_CMN_FIN_YEARS
173 WHERE organization_id = p_organization_id
174 AND fin_active_flag = 'Y';
175
176 CURSOR c_rg_rounding_account(p_organization_id IN NUMBER) IS
177 SELECT rg_rounding_account_id
178 FROM JAI_CMN_INVENTORY_ORGS
179 WHERE organization_id = p_organization_id
180 AND ( location_id IS NULL OR location_id = 0);
181
182 CURSOR c_rg_modvat_account(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
183 SELECT modvat_rm_account_id, modvat_cg_account_id
184 FROM JAI_CMN_INVENTORY_ORGS
185 WHERE organization_id = p_organization_id
186 AND location_id = p_location_id;
187
188 ln_receive_qty NUMBER;
189 CURSOR c_ja_in_receive_qty(cp_shipment_line_id IN NUMBER) IS
190 SELECT qty_received
191 FROM JAI_RCV_LINES
192 WHERE shipment_line_id = cp_shipment_line_id;
193
194 --Added the below 2 by Sanjikum for Bug #4049363
195 ln_cenvat_amount AMOUNT_RECORD;
196 ln_receive_amount AMOUNT_RECORD;
197
198 CURSOR c_receipt_tax_amount(cp_shipment_line_id IN NUMBER) IS /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
199 SELECT --Added the 6 columns by Sanjikum for Bug #4049363
200 sum(decode(b.tax_type, 'Excise', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) excise_amt,
201 sum(decode(b.tax_type, 'Addl. Excise', b.tax_amount * nvl(c.mod_cr_percentage, 0), 'CVD', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) additional_excise_amt,
202 sum(decode(b.tax_type, jai_constants.tax_type_add_cvd, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) additional_cvd , /*Changed by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
203 sum(decode(b.tax_type, 'Other Excise', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) other_excise_amt,
204 sum(decode(b.tax_type, jai_constants.tax_type_exc_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) excise_edu_cess_amt,
205 sum(decode(b.tax_type, jai_constants.tax_type_cvd_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) cvd_edu_cess_amt ,
206 sum(decode(b.tax_type, jai_constants.tax_type_sh_exc_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) sh_excise_edu_cess_amt, -- Date 16/04/2007 by
207 sum(decode(b.tax_type, jai_constants.tax_type_sh_cvd_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) sh_cvd_edu_cess_amt -- sacsethi for Bug#5989740
208 FROM JAI_RCV_LINE_TAXES b, JAI_CMN_TAXES_ALL c
209 WHERE b.shipment_line_id = cp_shipment_line_id
210 AND b.tax_id = c.tax_id
211 AND b.tax_type IN (jai_constants.tax_type_excise, jai_constants.tax_type_exc_additional, jai_constants.tax_type_exc_other,
212 jai_constants.tax_type_cvd, jai_constants.tax_type_exc_edu_cess, jai_constants.tax_type_cvd_edu_cess ,
213 jai_constants.tax_type_sh_exc_edu_cess, jai_constants.tax_type_sh_cvd_edu_cess -- Date 16/04/2007 by sacsethi for Bug#5989740
214 )
215 --AND b.tax_type IN ('Excise', 'Addl. Excise', 'Other Excise', 'CVD', jai_constants.tax_type_exc_edu_cess, jai_constants.tax_type_cvd_edu_cess)
216 AND b.modvat_flag = 'Y';
217
218 v_already_rounded_chk NUMBER;
219 v_excise_inv_rnd_cnt NUMBER;
220
221 CURSOR c_already_rounded_chk(p_source_header_id IN NUMBER, p_excise_invoice_no IN VARCHAR2,
222 p_excise_invoice_date IN DATE, p_transaction_type IN VARCHAR2) IS
223 SELECT max(rounding_id) rounding_id,
224 --Added the below 6 by Sanjikum for Bug #4049363
225 sum(basic_ed - rounded_basic_ed) rounded_basic_amt,
226 sum(additional_ed - rounded_additional_ed) rounded_addl_amt,
227 sum(additional_cvd - rounded_additional_cvd) rounded_additional_cvd ,/*Changed by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
228 sum(other_ed - rounded_other_ed) rounded_other_amt,
229 sum(excise_edu_cess - rounded_excise_edu_cess) rounded_excise_edu_cess,
230 sum(cvd_edu_cess - rounded_cvd_edu_cess) rounded_cvd_edu_cess,
231 sum(sh_excise_edu_cess - rounded_sh_excise_edu_cess) rounded_sh_excise_edu_cess,
232 sum(sh_cvd_edu_cess - rounded_sh_cvd_edu_cess) rounded_sh_cvd_edu_cess,
233 count(1)
234 FROM JAI_CMN_RG_ROUND_HDRS
235 WHERE source_header_id = p_source_header_id
236 AND excise_invoice_no = p_excise_invoice_no
237 AND excise_invoice_date = p_excise_invoice_date
238 AND src_transaction_type = p_transaction_type
239 GROUP BY rounding_id, excise_invoice_no, excise_invoice_date;
240
241 --Added the 2 variables below by Sanjikum for Bug #4049363
242 v_temp_amount AMOUNT_RECORD;
243 v_1st_claim_rnd_amount AMOUNT_RECORD;
244
245 v_1st_claim_cgin_cnt NUMBER;
246 v_1st_claim_tot_cnt NUMBER;
247
248 CURSOR c_1st_claim_cgin_cnt(p_rounding_id IN NUMBER) IS
249 SELECT count(1) total_cnt, sum( decode(item_class, 'CGIN', 1, 'CGEX', 1, 0) ) cgin_cnt
250 FROM JAI_CMN_RG_ROUND_LINES
251 WHERE rounding_id = p_rounding_id
252 GROUP BY rounding_id;
253
254 r_1st_claim_cgin_cnt c_1st_claim_cgin_cnt%ROWTYPE;
255
256 r_cgin_chk_for_2nd_claim c_cgin_chk_for_2nd_claim%ROWTYPE;
257 r_full_cgin_chk c_full_cgin_chk%ROWTYPE;
258
259
260 v_no_of_periods_updated NUMBER(15);
261 v_period_balance_id NUMBER(15);
262 v_full_cgin_case VARCHAR(1) ; --File.Sql.35 Cbabu := 'N';
263 v_proceed_for_2nd_claim VARCHAR(1) ; --File.Sql.35 Cbabu := 'N';
264
265 v_exc_inv_rnd_counter NUMBER; --File.Sql.35 Cbabu := 0;
266 v_rnd_entries_to_be_passed NUMBER;--File.Sql.35 Cbabu := 0;
267 v_receipt_num rcv_shipment_headers.receipt_num%type;
268 v_transaction_type rcv_transactions.transaction_type%type;
269
270 lv_ttype_correct rcv_transactions.transaction_type%type ;
271 BEGIN
272
273 /*--------------------------------------------------------------------------------------------------------------------------------
274 Change History for Filename - ja_in_rg_rounding_p.sql
275 S.No dd/mm/yyyy Author and Details
276 ----------------------------------------------------------------------------------------------------------------------------------
277 1 08/01/2004 Vijay Shankar for Bug# 3213826 Version : 619.1
278 Created the Package to handle Receipts related RG Rounding.
279 DO_ROUNDING Procedue is called from concurrent program JAINRGRND. This procedure posts excise amount Rounding differences to
280 - RG_PART_II, GL Accouting, Update RG balances
281 - Insert into JAI_CMN_RG_ROUND_HDRS, JAI_CMN_RG_ROUND_LINES tables
282 - Makes a call to jai_cmn_rg_period_bals_pkg.adjust_rounding to adjust this rounding amount in PERIOD Balance if
283 the min(ROUNDING_LINE_ID,register_id) of excise invoice is already consolidated in another period
284
285 Only for Excise Invoice having all CGIN Items is considered as a case where in two rounding entries were passed.
286 all the other scenarios only one rounding entry is passed
287
288 2 31/08/2004 Vijay Shankar for Bug# 3496408 Version : 115.1
289 Modified the code to consider CORRECTions of RECEIVE to round the CENVAT amount. Related Selects and Updates
290 are modified to select JAI_CMN_RG_ROUND_LINES_S.nextval, the all the required data and punch Rounding_id in JAI_CMN_RG_23AC_II_TRXS in CORRECT RG records also
291
292 3 21/01/2005 Sanjikum for Bug #4049363 Version 116.0 (115.2)
293 Modified the Code to consider the rounding for each tax type instead of total excise amount
294 Changed a lot of code. Can be searched with Bug#4049363
295
296 Dependency -
297 New columns are added in the Tables - JAI_CMN_RG_ROUND_HDRS, JAI_CMN_RG_ROUND_LINES
298
299 4 31/08/2004 Vijay Shankar for Bug# 4103161 Version : 116.1
300 added a new procedure DO_RTV_ROUNDING to take care of RTV Rounding also.
301 And existing procedure do_rounding is modified to make a call to do_rtv_rounding
302
303 * Dependancy for later version of the object *
304
305 5. 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.2
306 Code is modified due to the Impact of Receiving Transactions DFF Elimination
307
308 * High Dependancy for future Versions of this object *
309
310 6. 19/04/2005 rallamse for Bug#4336482, Version 116.3
311 For SEED there is a change in concurrent "JAINRGRND" to use FND_STANDARD_DATE with STANDARD_DATE format
312 Procedure ja_in_rg_rounding_pkg.do_rounding signature modified by converting P_EX_INVOICE_TO_DATE, P_EX_INVOICE_TO_DATE
313 of DATE datatype to PV_EX_INVOICE_FROM_DATE, PV_EX_INVOICE_TO_DATE of varchar2 datatype.
314 The varchar2 values are converted to DATE fromat using fnd_date.canonical_to_date function.
315
316 7. 08-Jun-2005 Version 116.2 jai_rcv_rnd -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
317 as required for CASE COMPLAINCE.
318
319 8. 13-Jun-2005 File Version: 116.3
320 Ramananda for bug#4428980. Removal of SQL LITERALs is done
321
322 9. 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
323 GL Sources and GL Categories got changed. Refer bug for the details
324
325 10. 31-Jan-2006 Bug 4930048. Added by Lakshmi Gopalsami version 120.2
326 (1) Changed transaction_source_num to transaction_id in
327 update of JAI_CMN_RG_23AC_II_TRXS and subquery checking
328 for the existence in jai_rcv_transactions
329 (2) Changed transaction_source_num to transaction_id in
330 update of JAI_CMN_RG_PLA_TRXS and subquery checking
331 for the existence in jai_rcv_transactions
332 (3) Changed transaction_source_num to transaction_id in
333 update of JAI_CMN_RG_23AC_II_TRXS and subquery checking
334 for the existence in rcv_transactions
335 (4) Added proper alias names and changed
336 transaction_source_num to transaction_id while
337 checking data in rcv_transactions in update to
338 JAI_CMN_RG_23AC_II_TRXS. This is done in 2 places.
339
340
341 DEPENDENCY
342 ----------
343 IN60106 + 4146708 + 4103161 + 4346453
344
345
346 11. 20/11/2006 Aiyer for bug#5228046 , File Version 120.3
347 Issue :- Enhancement to support new tax type called ADDITIONAL_CVD
348 Fix :- Added the code similar to that of additional_ed. The procedures do_rounding and do_rtv_rounding
349 have been modified.
350 Dependencies Due to this Bug:-
351 There are Datamodel and spec changes done for this bug. So this bug has both
352
353 12. 26-FEB-2007 SSAWANT , File version 120.7
354 Forward porting the change in 11.5 bug 5053992 to R12 bug no 5054176.
355
356 Issue:
357
358 The rounding entries for CESS are hitting wrong register.
359
360 Fix:
361
362 While inserting rounding for excise into JAI_CMN_RG_23AC_II_TRXS table the varibale v_register_type was
363 used for register_type column. But for jai_rg_others while inserting the corresponding CESS the
364 parameter p_register_type is used for source_register and so the discrepancy. Replaced p_register_type with
365 v_register_type while inserting into jai_rg_others.
366
367 13. 19-Apr-2007 Sacsethi for forward porting Bug#5989740, 11i bug#5907436, file version 120.8
368
369 14. 19-Apr-2007 bgowrava for forward porting bug#5674376. File Version 120.9
370 Issue : Rounding entries are not generated correctly.
371 Fix : Whenever we fetch parent register id for a rounding entry we use excise_invoice_no and
372 excise_invoice_date. But these two can be same for different vendors. So added a check to include vendor_id
373 and vendor_site_id wherever applicable. The cursor to fetch parent register id is also modified to include
374 shipment header id.
375
376 The vendor_id and vendor_site_id for rounding_entries are populated from rcv_shipment_headers
377 This is now changed to be populated from the parent entry in ja_in_rg23_part_ii.
378
379 These changes were made in bug#5478107 at PRE Addl. CVD enh level. The same are
380 forward ported to the latest code line.
381
382 15. 19-Apr-2007 bgowrava for forward porting bug#5674376. File Version 120.9
383 Issue : Rounding entries are not generated correctly.
384 Fix : The concurrent was erroring out as the group by clause did not have vendor_id and vendor_site_id. These
385 were included in the select clause as part of previous fix. Now these are added in group by clause also.
386 16. 02/05/2007 bduvarag for the Bug#5141459, file version 120.10
387 Forward porting the changes done in 11i bug#4548378
388
389
390 --------------------------------------------------------------------------------------------------------------------------------*/
391
392 P_EX_INVOICE_FROM_DATE := fnd_date.canonical_to_date(PV_EX_INVOICE_FROM_DATE);
393 P_EX_INVOICE_TO_DATE := fnd_date.canonical_to_date(PV_EX_INVOICE_TO_DATE);
394 v_commit_interval := 50;
395 v_rounding_precision := 0;
396 v_acct_type := 'REGULAR';
397 v_acct_nature := 'CENVAT-ROUNDING';
398 v_source_name := 'Purchasing India';
399 v_category_name := 'Receiving India';
400 v_statement_no := '0';
401 v_err_message := '';
402 v_rounding_entries_made := 0;
403 v_tot_errored_entries := 0;
404 v_zero_round_found := 0;
405 v_tot_processed_invoices := 0;
406 v_no_of_invoices_posted := 0;
407 v_save_point_set := FALSE;
408 v_created_by := nvl(FND_GLOBAL.USER_ID, -1);
409 v_last_update_login := nvl(FND_GLOBAL.LOGIN_ID,- 1);
410 v_today := trunc(SYSDATE);
411 v_line_type_a_cnt := 0;
412 v_line_type_c_cnt := 0;
413 v_tot_lines_cnt := 0;
414 v_full_cgin_case := 'N';
415 v_proceed_for_2nd_claim := 'N';
416 v_exc_inv_rnd_counter := 0;
417 v_rnd_entries_to_be_passed := 0;
418
419
420 OPEN c_enable_trace('JAINRGRND'); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
421 FETCH c_enable_trace INTO v_enable_trace;
422 CLOSE c_enable_trace;
423
424 FND_FILE.PUT_LINE( FND_FILE.log, 'File Version: 115.2 and LastUpdateDate:08/01/2004, Inputs parameters: p_organization_id->'
425 ||p_organization_id
426 ||', p_ex_invoice_from_date->'||p_ex_invoice_from_date
427 ||', p_ex_invoice_to_date->'||p_ex_invoice_to_date
428 ||', v_enable_trace->'||v_enable_trace
429 );
430
431 IF v_enable_trace = 'Y' THEN
432
433 OPEN get_audsid;
434 FETCH get_audsid INTO v_sid, v_serial, v_spid;
435 CLOSE get_audsid;
436
437 OPEN get_dbname;
438 FETCH get_dbname INTO v_name1;
439 CLOSE get_dbname;
440
441 FND_FILE.PUT_LINE( FND_FILE.log, 'TraceFile Name = '||lower(v_name1)||'_ora_'||v_spid||'.trc');
442
443 EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
444
445 END IF;
446
447 /* Vijay Shankar for Bug#4103161 */
448 if p_transaction_type IN ('ALL', 'RETURN TO VENDOR') then
449 v_transaction_type := 'RETURN TO VENDOR';
450 do_rtv_rounding(
451 p_organization_id => p_organization_id,
452 p_transaction_type => v_transaction_type,
453 p_register_type => p_register_type,
454 p_ex_invoice_from_date => p_ex_invoice_from_date,
455 p_ex_invoice_to_date => p_ex_invoice_to_date
456 );
457
458
459 /* as rounding is complete we can return from execution after commit*/
460 if p_transaction_type = 'RETURN TO VENDOR' then
461 goto do_commit;
462 else
463 /* we need to do the rounding of RECEIVE transactions also */
464 null;
465 end if;
466
467 end if;
468
469 v_statement_no := '0.1';
470 if p_transaction_type = 'ALL' then
471 v_transaction_type := 'RECEIVE';
472 else
473 v_transaction_type := p_transaction_type;
474 end if;
475
476 OPEN c_rg_rounding_account(p_organization_id);
477 FETCH c_rg_rounding_account INTO v_rg_rounding_account_id;
478 CLOSE c_rg_rounding_account;
479
480 v_statement_no := '0.2';
481
482 IF v_rg_rounding_account_id IS NULL THEN
483 FND_FILE.PUT_LINE( FND_FILE.log, 'ERROR: Rounding Account is not specified at Organization Level');
484 RAISE_APPLICATION_ERROR( -20099, 'Rounding Account is not specified at Organization Level');
485 END IF;
486
487 v_statement_no := '0.3';
488
489 OPEN c_active_fin_year(p_organization_id);
490 FETCH c_active_fin_year INTO v_fin_year;
491 CLOSE c_active_fin_year;
492
493 --Added by Sanjikum for Bug #4049363
494 v_zero_record.basic := 0;
495 v_zero_record.additional := 0;
496 v_zero_record.additional_cvd := 0; /*added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
497 v_zero_record.other := 0;
498 v_zero_record.excise_edu_cess := 0;
499 v_zero_record.cvd_edu_cess := 0;
500 v_zero_record.total := 0;
501
502 -- Date 16/04/2007 by sacsethi for Bug#5989740
503
504 v_zero_record.sh_excise_edu_cess := 0;
505 v_zero_record.sh_cvd_edu_cess := 0;
506
507
508 v_tot_amount := v_zero_record;
509
510 -- PART II A, C rounding entries can be posted into one of the Registers A or C
511 FOR r IN (
512 select b.shipment_header_id, a.excise_invoice_no, a.excise_invoice_date, b.transaction_type,
513 a.vendor_id,a.vendor_site_id,/*bgowrava for forward porting bug#5674376*/
514 min(a.location_id) location_id,
515 sum(a.cr_basic_ed) cr_basic_ed,
516 sum(a.cr_additional_ed) cr_additional_ed,
517 sum(a.cr_additional_cvd) cr_additional_cvd,/*5228046 Additional cvd Enhancement*/
518 sum(a.cr_other_ed) cr_other_ed,
519 sum(a.dr_basic_ed) dr_basic_ed,
520 sum(a.dr_additional_ed) dr_additional_ed,
521 sum(a.dr_additional_cvd) dr_additional_cvd, /*Column added by SACSETHI for the bug 5228046 - Additional CVD Enhancement */
522 sum(a.dr_other_ed) dr_other_ed
523 from JAI_CMN_RG_23AC_II_TRXS a, rcv_transactions b
524 where a.RECEIPT_REF = b.transaction_id
525 AND a.organization_id = p_organization_id
526 AND (
527 (p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NULL)
528 OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NULL
529 AND a.excise_invoice_date >= p_ex_invoice_from_date)
530 OR (p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NOT NULL
531 AND a.excise_invoice_date <= p_ex_invoice_to_date)
532 OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NOT NULL
533 AND a.excise_invoice_date BETWEEN p_ex_invoice_from_date AND p_ex_invoice_to_date)
534 )
535 AND a.rounding_id IS NULL
536 AND a.TRANSACTION_SOURCE_NUM = 18
537 AND b.transaction_type = v_transaction_type
538 GROUP BY b.shipment_header_id, b.transaction_type, a.excise_invoice_no, a.excise_invoice_date,
539 a.vendor_id,a.vendor_site_id /*bgowrava for forward porting bug#5674376*/
540 )
541 LOOP
542
543 v_exc_inv_rnd_counter := 0;
544 v_rnd_entries_to_be_passed := 1;
545
546 LOOP
547
548 EXIT WHEN v_exc_inv_rnd_counter >= v_rnd_entries_to_be_passed;
549
550 BEGIN
551
552 v_statement_no := '1';
553 v_shipment_header_id := r.shipment_header_id;
554 v_excise_invoice_no := r.excise_invoice_no;
555 v_excise_invoice_date := r.excise_invoice_date;
556 r_full_cgin_chk := null;
557
558 v_excise_inv_rnd_cnt := 0;
559 --Added below 1 statements by Sanjikum for Bug #4049363
560 v_temp_amount := v_zero_record;
561
562 v_already_rounded_chk := null;
563 r_1st_claim_cgin_cnt := null;
564 r_cgin_chk_for_2nd_claim := null;
565 v_period_balance_id := null;
566 v_no_of_periods_updated := null;
567 v_proceed_for_2nd_claim := 'N';
568 v_full_cgin_case := 'N';
569
570 --Added below 1 statements by Sanjikum for Bug #4049363
571 v_1st_claim_rnd_amount := v_zero_record;
572
573 v_statement_no := '1.1';
574 v_already_rounded_chk := NULL;
575 OPEN c_already_rounded_chk(v_shipment_header_id, v_excise_invoice_no, v_excise_invoice_date, r.transaction_type);
576 FETCH c_already_rounded_chk INTO
577 v_already_rounded_chk,
578 --Added the below 6 variables by Sanjikum for Bug #4049363
579 v_1st_claim_rnd_amount.basic,
580 v_1st_claim_rnd_amount.additional,
581 v_1st_claim_rnd_amount.additional_cvd,/*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
582 v_1st_claim_rnd_amount.other,
583 v_1st_claim_rnd_amount.excise_edu_cess,
584 v_1st_claim_rnd_amount.cvd_edu_cess,
585 v_1st_claim_rnd_amount.sh_excise_edu_cess, -- Date 16/04/2007
586 v_1st_claim_rnd_amount.sh_cvd_edu_cess, -- by sacsethi for Bug#5989740
587 v_excise_inv_rnd_cnt;
588 CLOSE c_already_rounded_chk;
589
590 -- Chk for full CGIN case
591 OPEN c_full_cgin_chk(v_shipment_header_id, v_excise_invoice_no, v_excise_invoice_date,r.vendor_id,r.vendor_site_id);/*bgowrava for forward porting bug#5674376*/
592 FETCH c_full_cgin_chk INTO r_full_cgin_chk;
593 CLOSE c_full_cgin_chk;
594
595 IF r_full_cgin_chk.total_cnt = r_full_cgin_chk.cgin_cnt THEN
596 v_full_cgin_case := 'Y';
597
598 OPEN c_cgin_chk_for_2nd_claim(v_shipment_header_id, v_excise_invoice_no, v_excise_invoice_date,r.vendor_id,r.vendor_site_id);/*bgowrava for forward porting bug#5674376*/
599 FETCH c_cgin_chk_for_2nd_claim INTO r_cgin_chk_for_2nd_claim;
600 CLOSE c_cgin_chk_for_2nd_claim;
601
602 END IF;
603
604 IF v_full_cgin_case = 'Y' AND r_cgin_chk_for_2nd_claim.cent_percent_cnt > 0
605 AND v_excise_inv_rnd_cnt=0
606 THEN
607 v_rnd_entries_to_be_passed := 2;
608 END IF;
609
610 FND_FILE.PUT_LINE( FND_FILE.log, 'v_already_rounded_chk->'||v_already_rounded_chk
611 ||', v_1st_claim_rnd_amt->'||r_cgin_chk_for_2nd_claim.cent_percent_cnt
612 );
613
614 -- If the following if is satisfied, then cgin chk for 2nd claim has to be done
615 IF v_already_rounded_chk IS NOT NULL THEN
616
617 -- if the following condition is satisfied, then it means Excise Invoice has All CGIN items
618 IF v_full_cgin_case = 'Y' THEN
619
620 -- 2nd Claim is done and 2nd rounding has also happened so no more rounding
621 IF v_excise_inv_rnd_cnt = 2 THEN
622 v_proceed_for_2nd_claim := 'N';
623
624 -- proceed for 2nd rounding entry as 1st rounding happened and 2nd claim is done
625 ELSIF v_excise_inv_rnd_cnt = 1 AND r_cgin_chk_for_2nd_claim.cent_percent_cnt > 0 THEN
626 v_proceed_for_2nd_claim := 'Y';
627
628 -- 1st claim is done but 2nd claim was not done and 1st rounding has already happened so new rounding entry
629 ELSE
630 v_proceed_for_2nd_claim := 'N';
631
632 END IF;
633
634 -- Other Than Full CGIN Case
635 ELSE
636 v_proceed_for_2nd_claim := 'N';
637
638 END IF;
639
640 v_statement_no := '1.3';
641 SAVEPOINT previous_savepoint;
642 v_save_point_set := TRUE;
643
644 IF v_proceed_for_2nd_claim = 'N' THEN
645 -- punching the rounding id found above in these transactions, so that they will not be considered again
646 v_statement_no := '1.2';
647 UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
648 SET jcrg23ac.rounding_id = v_already_rounded_chk
649 WHERE jcrg23ac.excise_invoice_no = v_excise_invoice_no
650 AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
651 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
652 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
653 AND jcrg23ac.organization_id = p_organization_id
654 AND jcrg23ac.rounding_id IS NULL
655 AND jcrg23ac.transaction_source_num = 18
656 AND exists (select 1 from rcv_transactions rt
657 /* Bug 4930048. Added by Lakshmi Gopalsami
658 Changed transaction_source_num to transaction_id
659 */
660 where rt.transaction_id = jcrg23ac.receipt_ref
661 and rt.transaction_type = v_transaction_type);
662
663 FND_FILE.PUT_LINE( FND_FILE.log, '+++++ Ex. Invoice Already rounded +++++');
664 GOTO next_exc_inv;
665 END IF;
666
667 -- Rounding was not yet done for this excise invoice
668 ELSE
669
670 IF v_full_cgin_case = 'Y' THEN
671 NULL;
672 END IF;
673
674 END IF;
675
676 FND_FILE.PUT_LINE( FND_FILE.log, '2nd_claim.cent ->'||r_cgin_chk_for_2nd_claim.cent_percent_cnt
677 ||', fifty->'||r_cgin_chk_for_2nd_claim.fifty_percent_cnt
678 ||', zero->'||r_cgin_chk_for_2nd_claim.zero_percent_cnt
679 ||', v_proceed_for_2nd_claim->'||v_proceed_for_2nd_claim
680 );
681
682 -- This indicates whether the processing has to ROLLBACK or not
683 -- incase error occured due to code between v_save_point_set = FALSE and v_save_point_set = TRUE
684 v_tot_processed_invoices := v_tot_processed_invoices + 1;
685
686 v_statement_no := '1.4';
687 SELECT JAI_CMN_RG_ROUND_HDRS_S.nextval INTO v_rounding_id FROM dual;
688
689 v_line_type_a_cnt := 0;
690 v_line_type_c_cnt := 0;
691 v_tot_lines_cnt := 0;
692 --Added below 1 statements by Sanjikum for Bug #4049363
693 v_tot_amount := v_zero_record;
694
695 v_statement_no := '1.4';
696 -- this is to get RECEIVE type of transactions
697 FOR line IN ( SELECT a.shipment_line_id, a.transaction_id, d.item_class
698 FROM JAI_RCV_LINES a,JAI_RCV_CENVAT_CLAIMS b,RCV_TRANSACTIONS c, JAI_INV_ITM_SETUPS d
699 WHERE a.organization_id = d.organization_id
700 AND b.transaction_id = a.transaction_id /*bgowrava for forward porting bug#5674376*/
701 AND a.transaction_id = c.transaction_id --Added by nprashar for bug # 13367420
702 AND a.inventory_item_id = d.inventory_item_id
703 AND a.shipment_header_id = v_shipment_header_id
704 AND a.excise_invoice_no = v_excise_invoice_no
705 AND a.excise_invoice_date = v_excise_invoice_date
706 AND ( (nvl(b.vendor_id,-999) = nvl(r.vendor_id,-999)
707 AND nvl(b.vendor_site_id,-999) = nvl(r.vendor_site_id,-999)
708 AND b.vendor_changed_flag = 'Y' )
709 OR
710 (nvl(c.vendor_id,-999) = nvl(r.vendor_id,-999)
711 AND nvl(c.vendor_site_id,-999) = nvl(r.vendor_site_id,-999)
712 AND b.vendor_changed_flag = 'N' )
713 ) /*bgowrava for forward porting bug#5674376*/
714 GROUP BY a.shipment_line_id, a.transaction_id, d.item_class
715 )
716 LOOP
717 --Added below 1 statements by Sanjikum for Bug #4049363
718 ln_receive_amount := v_zero_record;
719
720 ln_receive_qty := 0;
721
722 OPEN c_ja_in_receive_qty(line.shipment_line_id);
723 FETCH c_ja_in_receive_qty INTO ln_receive_qty;
724 CLOSE c_ja_in_receive_qty;
725
726 OPEN c_receipt_tax_amount(line.shipment_line_id);
727 --Added the 6 columns by Sanjikum for Bug #4049363
728 FETCH c_receipt_tax_amount INTO
729 ln_receive_amount.basic,
730 ln_receive_amount.additional,
731 ln_receive_amount.additional_cvd, /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
732 ln_receive_amount.other,
733 ln_receive_amount.excise_edu_cess,
734 ln_receive_amount.cvd_edu_cess ,
735 ln_receive_amount.sh_excise_edu_cess,
736 ln_receive_amount.sh_cvd_edu_cess;
737
738 CLOSE c_receipt_tax_amount;
739
740 FND_FILE.PUT_LINE( FND_FILE.log, 'TrxId:'||line.transaction_id ||', RecvQty->'||ln_receive_qty);
741
742 -- this is to loop through all the RECEIVE and related CORRECT transactions
743 lv_ttype_correct := 'CORRECT' ; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
744 FOR trx IN (select transaction_id, quantity
745 from rcv_transactions
746 where shipment_line_id = line.shipment_line_id
747 and (
748 (transaction_type = lv_ttype_correct and parent_transaction_id = line.transaction_id)
749 OR transaction_id = line.transaction_id
750 )
751 )
752 LOOP
753
754 --Added below the 7 statements by Sanjikum for Bug #4049363
755 ln_cenvat_amount.basic := ln_receive_amount.basic * trx.quantity/ln_receive_qty;
756 ln_cenvat_amount.additional := ln_receive_amount.additional * trx.quantity/ln_receive_qty;
757 ln_cenvat_amount.other := ln_receive_amount.other * trx.quantity/ln_receive_qty;
758 ln_cenvat_amount.excise_edu_cess := ln_receive_amount.excise_edu_cess * trx.quantity/ln_receive_qty;
759 ln_cenvat_amount.cvd_edu_cess := ln_receive_amount.cvd_edu_cess * trx.quantity/ln_receive_qty;
760 ln_cenvat_amount.sh_excise_edu_cess := ln_receive_amount.sh_excise_edu_cess * trx.quantity/ln_receive_qty; -- Date 16/04/2007 by sacsethi for Bug#5989740
761 ln_cenvat_amount.sh_cvd_edu_cess := ln_receive_amount.cvd_edu_cess * trx.quantity/ln_receive_qty; -- Date 16/04/2007 by sacsethi for Bug#5989740
762 ln_cenvat_amount.total := NVL(ln_cenvat_amount.basic,0) + NVL(ln_cenvat_amount.additional,0) + NVL(ln_cenvat_amount.other,0) + NVL(ln_cenvat_amount.excise_edu_cess,0) + NVL(ln_cenvat_amount.cvd_edu_cess,0);
763 v_statement_no := '1.5';
764
765 -- populate item class
766 INSERT INTO JAI_CMN_RG_ROUND_LINES (ROUNDING_LINE_ID,
767 ROUNDING_ID, SRC_LINE_ID, SRC_TRANSACTION_ID,
768 EXCISE_AMT,
769 BASIC_ED, ADDITIONAL_ED, OTHER_ED, --Added by Sanjikum for Bug #4049363
770 EXCISE_EDU_CESS, CVD_EDU_CESS,--Added by Sanjikum for Bug #4049363
771 sh_excise_edu_cess , -- Date 16/04/2007 by sacsethi for Bug#5989740
772 sh_cvd_edu_cess ,
773 ITEM_CLASS, CREATION_DATE, CREATED_BY,
774 program_application_id, program_id, program_login_id, request_id
775 )
776 VALUES ( JAI_CMN_RG_ROUND_LINES_S.nextval,
777 v_rounding_id,
778 line.shipment_line_id,
779 trx.transaction_id,
780 ln_cenvat_amount.total, --Added by Sanjikum for Bug #4049363
781 ln_cenvat_amount.basic,
782 ln_cenvat_amount.additional,
783 ln_cenvat_amount.other, --Columns Added by Sanjikum for Bug #4049363
784 ln_cenvat_amount.excise_edu_cess,
785 ln_cenvat_amount.cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
786 ln_cenvat_amount.sh_excise_edu_cess,
787 ln_cenvat_amount.sh_cvd_edu_cess, --Columns Added by Sanjikum for Bug #4049363
788 line.item_class, SYSDATE, v_created_by,
789 fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'), fnd_profile.value('CONC_REQUEST_ID')
790 );
791
792 --Added the 6 statements by Sanjikum for Bug #4049363
793 v_tot_amount.basic := v_tot_amount.basic + ln_cenvat_amount.basic;
794 v_tot_amount.additional := v_tot_amount.additional + ln_cenvat_amount.additional;
795 v_tot_amount.other := v_tot_amount.other + ln_cenvat_amount.other;
796 v_tot_amount.excise_edu_cess := v_tot_amount.excise_edu_cess + ln_cenvat_amount.excise_edu_cess;
797 v_tot_amount.cvd_edu_cess := v_tot_amount.cvd_edu_cess + ln_cenvat_amount.cvd_edu_cess;
798 -- Date 16/04/2007 by sacsethi for Bug#5989740
799 -- start 5989740
800 v_tot_amount.sh_excise_edu_cess := v_tot_amount.sh_excise_edu_cess + ln_cenvat_amount.sh_excise_edu_cess;
801 v_tot_amount.sh_cvd_edu_cess := v_tot_amount.sh_cvd_edu_cess + ln_cenvat_amount.sh_cvd_edu_cess;
802 -- end 5989740
803 -----------------------------------------------
804 v_tot_amount.total := NVL(v_tot_amount.basic,0) +
805 NVL(v_tot_amount.additional,0) +
806 NVL(v_tot_amount.other,0) +
807 NVL(v_tot_amount.excise_edu_cess,0) +
808 NVL(v_tot_amount.cvd_edu_cess,0) +
809 NVL(v_tot_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
810 NVL(v_tot_amount.sh_cvd_edu_cess,0);
811
812 END LOOP;
813
814 IF line.item_class IN ('RMIN', 'RMEX') THEN
815 v_line_type_a_cnt := v_line_type_a_cnt + 1;
816 ELSIF line.item_class IN ('CGIN', 'CGEX') THEN
817 v_line_type_c_cnt := v_line_type_c_cnt + 1;
818 END IF;
819 v_tot_lines_cnt := v_tot_lines_cnt + 1;
820
821 END LOOP;
822
823 v_statement_no := '1.6';
824 IF v_line_type_c_cnt = v_tot_lines_cnt THEN
825 v_register_type := 'C';
826 v_rounding_type := 1;
827 ELSIF v_line_type_c_cnt = 0 THEN
828 v_register_type := 'A';
829 v_rounding_type := 0;
830 ELSE
831 v_register_type := p_register_type;
832 v_rounding_type := 2;
833 END IF;
834
835 FND_FILE.PUT_LINE( FND_FILE.log, 'Started Processing shipment_header_id->'||v_shipment_header_id
836 ||', excise_invoice_no->'||v_excise_invoice_no
837 ||', excise_invoice_date->'||v_excise_invoice_date
838 );
839
840 -- find the rounding amount for the receipt
841 -- CGIN case
842 IF v_line_type_c_cnt = v_tot_lines_cnt THEN
843
844 --7 statements added by Sanjikum for Bug #4049363
845 v_tot_amount.basic := v_tot_amount.basic/2;
846 v_tot_amount.additional := v_tot_amount.additional/2;
847 v_tot_amount.additional_cvd := v_tot_amount.additional_cvd/2;/*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
848 v_tot_amount.other := v_tot_amount.other/2;
849 v_tot_amount.excise_edu_cess := v_tot_amount.excise_edu_cess/2;
850 v_tot_amount.cvd_edu_cess := v_tot_amount.cvd_edu_cess/2;
851 -- Date 16/04/2007 by sacsethi for Bug#5989740
852 -- start 5989740
853 v_tot_amount.sh_excise_edu_cess := v_tot_amount.sh_excise_edu_cess/2;
854 v_tot_amount.sh_cvd_edu_cess := v_tot_amount.sh_cvd_edu_cess/2;
855 -- end 5989740
856 v_tot_amount.total := NVL(v_tot_amount.basic,0) +
857 NVL(v_tot_amount.additional,0) +
858 NVL(v_tot_amount.additional_cvd,0) + /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
859 NVL(v_tot_amount.other,0) +
860 NVL(v_tot_amount.excise_edu_cess,0) +
861 NVL(v_tot_amount.cvd_edu_cess,0) +
862 NVL(v_tot_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
863 NVL(v_tot_amount.sh_cvd_edu_cess,0);
864
865
866 UPDATE JAI_CMN_RG_ROUND_LINES
867 SET --7 columns added by Sanjikum for Bug #4049363
868 excise_amt = NVL(basic_ed/2,0) +
869 NVL(additional_ed/2,0) +
870 NVL(additional_cvd/2,0) + /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
871 NVL(other_ed/2,0) +
872 NVL(excise_edu_cess/2,0) +
873 NVL(cvd_edu_cess/2,0),
874 basic_ed = basic_ed/2,
875 additional_ed = additional_ed/2,
876 additional_cvd = additional_cvd/2,/*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
877 other_ed = other_ed/2,
878 excise_edu_cess = excise_edu_cess/2,
879 cvd_edu_cess = cvd_edu_cess/2 ,
880 sh_excise_edu_cess = sh_excise_edu_cess/2,
881 sh_cvd_edu_cess = sh_cvd_edu_cess/2
882 WHERE rounding_id = v_rounding_id;
883
884 -- 1st Claim
885 IF v_proceed_for_2nd_claim = 'N' THEN
886 v_statement_no := '2';
887
888 --6 statements added by Sanjikum for Bug #4049363
889 v_tot_rounded_amount.basic := FLOOR(v_tot_amount.basic); -- i.e floor(25.7) = 25
890 v_tot_rounded_amount.additional := FLOOR(v_tot_amount.additional);
891 v_tot_rounded_amount.additional_cvd := FLOOR(v_tot_amount.additional_cvd); /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
892 v_tot_rounded_amount.other := FLOOR(v_tot_amount.other);
893 v_tot_rounded_amount.excise_edu_cess := FLOOR(v_tot_amount.excise_edu_cess);
894 v_tot_rounded_amount.cvd_edu_cess := FLOOR(v_tot_amount.cvd_edu_cess);
895
896 -- Date 16/04/2007 by sacsethi for Bug#5989740
897 v_tot_rounded_amount.sh_excise_edu_cess := FLOOR(v_tot_amount.sh_excise_edu_cess);
898 v_tot_rounded_amount.sh_cvd_edu_cess := FLOOR(v_tot_amount.sh_cvd_edu_cess);
899 -- end 5989740
900
901
902 --6 statements added by Sanjikum for Bug #4049363
903 v_rounded_amount.basic := v_tot_rounded_amount.basic - v_tot_amount.basic;
904 v_rounded_amount.additional := v_tot_rounded_amount.additional - v_tot_amount.additional;
905 v_rounded_amount.additional_cvd := v_tot_rounded_amount.additional_cvd - v_tot_amount.additional_cvd;/*Added by SACSETHI for the bug 5228046
906 -Additional CVD Enhancement*/
907 v_rounded_amount.other := v_tot_rounded_amount.other - v_tot_amount.other;
908 v_rounded_amount.excise_edu_cess := v_tot_rounded_amount.excise_edu_cess - v_tot_amount.excise_edu_cess;
909 v_rounded_amount.cvd_edu_cess := v_tot_rounded_amount.cvd_edu_cess - v_tot_amount.cvd_edu_cess;
910
911 -- Date 16/04/2007 by sacsethi for Bug#5989740
912 v_rounded_amount.sh_excise_edu_cess := v_tot_rounded_amount.sh_excise_edu_cess - v_tot_amount.sh_excise_edu_cess;
913 v_rounded_amount.sh_cvd_edu_cess := v_tot_rounded_amount.sh_cvd_edu_cess - v_tot_amount.sh_cvd_edu_cess;
914 -- end 5989740
915
916 -- 2nd Claim
917 ELSIF v_proceed_for_2nd_claim = 'Y' THEN
918 --statements added by Sanjikum for Bug #4049363
919
920 v_temp_amount.basic := v_tot_amount.basic + v_1st_claim_rnd_amount.basic; -- 25.7 + 0.7 = 26.4
921 v_tot_rounded_amount.basic := ROUND(v_temp_amount.basic, v_rounding_precision); -- round(26.4, 0)= 26
922 v_rounded_amount.basic := (v_tot_rounded_amount.basic - v_temp_amount.basic) + v_1st_claim_rnd_amount.basic;
923
924 v_temp_amount.additional := v_tot_amount.additional + v_1st_claim_rnd_amount.additional;
925 v_tot_rounded_amount.additional := ROUND(v_temp_amount.additional, v_rounding_precision);
926 v_rounded_amount.additional := (v_tot_rounded_amount.additional - v_temp_amount.additional) + v_1st_claim_rnd_amount.additional;
927
928 /*
929 || Start of bug 5228046 -Additional CVD Enhancement
930 || Added by SACSETHI
931 */
932 v_temp_amount.additional_cvd := v_tot_amount.additional_cvd + v_1st_claim_rnd_amount.additional_cvd;
933 v_tot_rounded_amount.additional_cvd := ROUND(v_temp_amount.additional_cvd, v_rounding_precision);
934 v_rounded_amount.additional_cvd := (v_tot_rounded_amount.additional_cvd - v_temp_amount.additional_cvd) + v_1st_claim_rnd_amount.additional_cvd;
935
936 /* End of bug 5228046 */
937
938 v_temp_amount.other := v_tot_amount.other + v_1st_claim_rnd_amount.other;
939 v_tot_rounded_amount.other := ROUND(v_temp_amount.other, v_rounding_precision);
940 v_rounded_amount.other := (v_tot_rounded_amount.other - v_temp_amount.other) + v_1st_claim_rnd_amount.other;
941
942 v_temp_amount.excise_edu_cess := v_tot_amount.excise_edu_cess + v_1st_claim_rnd_amount.excise_edu_cess;
943 v_tot_rounded_amount.excise_edu_cess := ROUND(v_temp_amount.excise_edu_cess, v_rounding_precision);
944 v_rounded_amount.excise_edu_cess := (v_tot_rounded_amount.excise_edu_cess - v_temp_amount.excise_edu_cess) + v_1st_claim_rnd_amount.excise_edu_cess;
945
946 v_temp_amount.cvd_edu_cess := v_tot_amount.cvd_edu_cess + v_1st_claim_rnd_amount.cvd_edu_cess;
947 v_tot_rounded_amount.cvd_edu_cess := ROUND(v_temp_amount.cvd_edu_cess, v_rounding_precision);
948 v_rounded_amount.cvd_edu_cess := (v_tot_rounded_amount.cvd_edu_cess - v_temp_amount.cvd_edu_cess) + v_1st_claim_rnd_amount.cvd_edu_cess;
949
950 -- Date 16/04/2007 by sacsethi for Bug#5989740
951 -- start 5989740
952
953 v_temp_amount.sh_excise_edu_cess := v_tot_amount.sh_excise_edu_cess + v_1st_claim_rnd_amount.sh_excise_edu_cess;
954 v_tot_rounded_amount.sh_excise_edu_cess := ROUND(v_temp_amount.sh_excise_edu_cess, v_rounding_precision);
955 v_rounded_amount.sh_excise_edu_cess := (v_tot_rounded_amount.sh_excise_edu_cess - v_temp_amount.sh_excise_edu_cess) + v_1st_claim_rnd_amount.sh_excise_edu_cess;
956
957 v_temp_amount.sh_cvd_edu_cess := v_tot_amount.sh_cvd_edu_cess + v_1st_claim_rnd_amount.sh_cvd_edu_cess;
958 v_tot_rounded_amount.sh_cvd_edu_cess := ROUND(v_temp_amount.sh_cvd_edu_cess, v_rounding_precision);
959 v_rounded_amount.sh_cvd_edu_cess := (v_tot_rounded_amount.sh_cvd_edu_cess - v_temp_amount.sh_cvd_edu_cess) + v_1st_claim_rnd_amount.sh_cvd_edu_cess;
960
961 -- end 5989740
962
963 ELSE
964 FND_FILE.PUT_LINE( FND_FILE.log, 'Some Problem in CGIN Claiming');
965 END IF;
966
967 -- Other than CGIN cases
968 ELSE
969 v_statement_no := '2';
970
971 --added by Sanjikum for Bug #4049363
972 v_tot_rounded_amount.basic := ROUND(v_tot_amount.basic, v_rounding_precision);
973 v_rounded_amount.basic := v_tot_rounded_amount.basic - v_tot_amount.basic;
974
975 v_tot_rounded_amount.additional := ROUND(v_tot_amount.additional, v_rounding_precision);
976 v_rounded_amount.additional := v_tot_rounded_amount.additional - v_tot_amount.additional;
977
978 /*
979 || Start of bug 5228046 -Additional CVD Enhancement
980 || Added by SACSETHI
981 */
982
983 v_tot_rounded_amount.additional_cvd := ROUND(v_tot_amount.additional_cvd, v_rounding_precision);
984 v_rounded_amount.additional_cvd := v_tot_rounded_amount.additional_cvd - v_tot_amount.additional_cvd;
985
986 /* End of bug 5228046 */
987
988 v_tot_rounded_amount.other := ROUND(v_tot_amount.other, v_rounding_precision);
989 v_rounded_amount.other := v_tot_rounded_amount.other - v_tot_amount.other;
990
991 v_tot_rounded_amount.excise_edu_cess := ROUND(v_tot_amount.excise_edu_cess, v_rounding_precision);
992 v_rounded_amount.excise_edu_cess := v_tot_rounded_amount.excise_edu_cess - v_tot_amount.excise_edu_cess;
993
994 v_tot_rounded_amount.cvd_edu_cess := ROUND(v_tot_amount.cvd_edu_cess, v_rounding_precision);
995 v_rounded_amount.cvd_edu_cess := v_tot_rounded_amount.cvd_edu_cess - v_tot_amount.cvd_edu_cess;
996
997 -- Date 16/04/2007 by sacsethi for Bug#5989740
998 -- start 5989740
999
1000 v_tot_rounded_amount.sh_excise_edu_cess := ROUND(v_tot_amount.sh_excise_edu_cess, v_rounding_precision);
1001 v_rounded_amount.sh_excise_edu_cess := v_tot_rounded_amount.sh_excise_edu_cess - v_tot_amount.sh_excise_edu_cess;
1002
1003 v_tot_rounded_amount.sh_cvd_edu_cess := ROUND(v_tot_amount.sh_cvd_edu_cess, v_rounding_precision);
1004 v_rounded_amount.sh_cvd_edu_cess := v_tot_rounded_amount.sh_cvd_edu_cess - v_tot_amount.sh_cvd_edu_cess;
1005
1006 -- end 5989740
1007
1008
1009 END IF;
1010
1011 --These are common for all the If elses above and should be sum of different tax types
1012
1013 v_tot_rounded_amount.total := NVL(v_tot_rounded_amount.basic,0) +
1014 NVL(v_tot_rounded_amount.additional,0) +
1015 NVL(v_tot_rounded_amount.additional_cvd,0) /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/ +
1016 NVL(v_tot_rounded_amount.other,0) +
1017 NVL(v_tot_rounded_amount.excise_edu_cess,0) +
1018 NVL(v_tot_rounded_amount.cvd_edu_cess,0) +
1019 NVL(v_tot_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1020 NVL(v_tot_rounded_amount.sh_cvd_edu_cess,0);
1021
1022 v_rounded_amount.total := NVL(v_rounded_amount.basic,0) +
1023 NVL(v_rounded_amount.additional,0) +
1024 NVL(v_rounded_amount.additional_cvd,0) /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/ +
1025 NVL(v_rounded_amount.other,0) +
1026 NVL(v_rounded_amount.excise_edu_cess,0) +
1027 NVL(v_rounded_amount.cvd_edu_cess,0) +
1028 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1029 NVL(v_rounded_amount.sh_cvd_edu_cess,0);
1030
1031 v_rounded_amount_rg23 := NVL(v_rounded_amount.basic,0) +
1032 NVL(v_rounded_amount.additional,0) +
1033 NVL(v_rounded_amount.additional_cvd,0) + /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/ +
1034 NVL(v_rounded_amount.other,0);
1035 v_rounded_amount_abs := ABS(NVL(v_rounded_amount.basic,0)) +
1036 ABS(NVL(v_rounded_amount.additional,0)) +
1037 ABS(NVL(v_rounded_amount.additional_cvd,0)) /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/ +
1038 ABS(NVL(v_rounded_amount.other,0)) +
1039 ABS(NVL(v_rounded_amount.excise_edu_cess,0)) +
1040 ABS(NVL(v_rounded_amount.cvd_edu_cess,0)) +
1041 ABS(NVL(v_rounded_amount.sh_excise_edu_cess,0)) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1042 ABS(NVL(v_rounded_amount.sh_cvd_edu_cess,0));
1043
1044 -- Punching Rounding_Id as 0 for RG transactions where in no Rounding is Required
1045 v_statement_no := '3';
1046
1047 --To check the amount with adding after taking absolute value
1048 IF v_rounded_amount_abs = 0 THEN
1049 v_zero_round_found := v_zero_round_found + 1;
1050
1051 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1052 lv_ttype_correct := 'CORRECT' ;
1053 /* Bug 4930048. Added by Lakshmi Gopalsami
1054 Added proper alias names and changed transaction_source_num
1055 to transaction_id while checking data in rcv_transactions.
1056 */
1057 UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
1058 SET jcrg23ac.rounding_id = 0
1059 WHERE jcrg23ac.organization_id = p_organization_id
1060 AND jcrg23ac.excise_invoice_no = v_excise_invoice_no
1061 AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
1062 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1063 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
1064 AND jcrg23ac.rounding_id IS NULL
1065 AND jcrg23ac.TRANSACTION_SOURCE_NUM = 18
1066 AND EXISTS (
1067 SELECT 1 FROM rcv_transactions rt
1068 WHERE rt.shipment_header_id = v_shipment_header_id
1069 AND rt.transaction_id = jcrg23ac.receipt_ref
1070 AND ( rt.transaction_type = r.transaction_type
1071 OR ( rt.transaction_type = lv_ttype_correct AND exists
1072 (select 1 from rcv_transactions rt1
1073 where rt1.transaction_id = rt.parent_transaction_id
1074 and rt1.transaction_type = r.transaction_type)
1075 )
1076 )
1077 );
1078
1079 DELETE FROM JAI_CMN_RG_ROUND_LINES WHERE rounding_id = v_rounding_id;
1080
1081 FND_FILE.PUT_LINE( FND_FILE.log, '**** Zero Rounded ****');
1082 GOTO next_exc_inv;
1083 ELSIF v_rounded_amount.total > 0 THEN
1084 v_rounding_entry_type := 'CR';
1085
1086 --Added by Sanjikum for Bug #4049363
1087 v_rounded_cr_amount := ABS(NVL(v_rounded_amount.basic,0) +
1088 NVL(v_rounded_amount.additional,0) +
1089 NVL(v_rounded_amount.additional_cvd,0) /*Changed by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/ +
1090 NVL(v_rounded_amount.other,0) +
1091 NVL(v_rounded_amount.excise_edu_cess,0) +
1092 NVL(v_rounded_amount.cvd_edu_cess,0) +
1093 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1094 NVL(v_rounded_amount.sh_cvd_edu_cess,0));
1095
1096 ELSIF v_rounded_amount.total < 0 THEN
1097 v_rounding_entry_type := 'DR';
1098
1099 --Added by Sanjikum for Bug #4049363
1100 v_rounded_dr_amount := ABS(NVL(v_rounded_amount.basic,0) +
1101 NVL(v_rounded_amount.additional,0) +
1102 NVL(v_rounded_amount.additional_cvd,0) + /* 5228046 -Additional CVD Enhancement*/
1103 NVL(v_rounded_amount.other,0) +
1104 NVL(v_rounded_amount.excise_edu_cess,0) +
1105 NVL(v_rounded_amount.cvd_edu_cess,0) +
1106 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1107 NVL(v_rounded_amount.sh_cvd_edu_cess,0));
1108
1109 END IF;
1110
1111 IF NVL(v_rounded_amount.basic,0) < 0 THEN
1112 v_rounded_dr_rg23_amount.basic := ABS(v_rounded_amount.basic);
1113 ELSE
1114 v_rounded_cr_rg23_amount.basic := ABS(v_rounded_amount.basic);
1115 END IF;
1116
1117 IF NVL(v_rounded_amount.additional,0) < 0 THEN
1118 v_rounded_dr_rg23_amount.additional := ABS(v_rounded_amount.additional);
1119 ELSE
1120 v_rounded_cr_rg23_amount.additional := ABS(v_rounded_amount.additional);
1121 END IF;
1122
1123 /*
1124 ||Start of bug 5228046 - Additional CVD Enhancement
1125 */
1126 IF NVL(v_rounded_amount.additional_cvd,0) < 0 THEN
1127 v_rounded_dr_rg23_amount.additional_cvd := ABS(v_rounded_amount.additional_cvd);
1128 ELSE
1129 v_rounded_cr_rg23_amount.additional_cvd := ABS(v_rounded_amount.additional_cvd);
1130 END IF;
1131
1132 /*End of bug 5228046 */
1133
1134
1135 IF NVL(v_rounded_amount.other,0) < 0 THEN
1136 v_rounded_dr_rg23_amount.other := ABS(v_rounded_amount.other);
1137 ELSE
1138 v_rounded_cr_rg23_amount.other := ABS(v_rounded_amount.other);
1139 END IF;
1140
1141 IF (NVL(v_rounded_amount.excise_edu_cess,0) +
1142 NVL(v_rounded_amount.cvd_edu_cess,0) +
1143 NVL(v_rounded_amount.sh_excise_edu_cess,0) +
1144 NVL(v_rounded_amount.sh_cvd_edu_cess,0)) < 0
1145 THEN
1146 v_rounded_dr_oth_amount.total := ABS(NVL(v_rounded_amount.excise_edu_cess,0) +
1147 NVL(v_rounded_amount.cvd_edu_cess,0) +
1148 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1149 NVL(v_rounded_amount.sh_cvd_edu_cess,0)
1150 );
1151 ELSE
1152 v_rounded_cr_oth_amount.total := ABS(NVL(v_rounded_amount.excise_edu_cess,0) +
1153 NVL(v_rounded_amount.cvd_edu_cess,0) +
1154 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1155 NVL(v_rounded_amount.sh_cvd_edu_cess,0));
1156 END IF;
1157
1158 IF NVL(v_rounded_amount.excise_edu_cess,0) < 0 THEN
1159 v_rounded_dr_oth_amount.excise_edu_cess := ABS(v_rounded_amount.excise_edu_cess);
1160 ELSE
1161 v_rounded_cr_oth_amount.excise_edu_cess := ABS(v_rounded_amount.excise_edu_cess);
1162 END IF;
1163
1164 IF NVL(v_rounded_amount.cvd_edu_cess,0) < 0 THEN
1165 v_rounded_dr_oth_amount.cvd_edu_cess := ABS(v_rounded_amount.cvd_edu_cess);
1166 ELSE
1167 v_rounded_cr_oth_amount.cvd_edu_cess := ABS(v_rounded_amount.cvd_edu_cess);
1168 END IF;
1169
1170 -- Date 16/04/2007 by sacsethi for Bug#5989740
1171 -- start 5989740
1172
1173
1174 IF NVL(v_rounded_amount.sh_excise_edu_cess,0) < 0 THEN
1175 v_rounded_dr_oth_amount.sh_excise_edu_cess := ABS(v_rounded_amount.sh_excise_edu_cess);
1176 ELSE
1177 v_rounded_cr_oth_amount.sh_excise_edu_cess := ABS(v_rounded_amount.sh_excise_edu_cess);
1178 END IF;
1179
1180 IF NVL(v_rounded_amount.sh_cvd_edu_cess,0) < 0 THEN
1181 v_rounded_dr_oth_amount.sh_cvd_edu_cess := ABS(v_rounded_amount.sh_cvd_edu_cess);
1182 ELSE
1183 v_rounded_cr_oth_amount.sh_cvd_edu_cess := ABS(v_rounded_amount.sh_cvd_edu_cess);
1184 END IF;
1185
1186 -- end 5989740
1187
1188
1189
1190 -- check the item classes
1191 -- if there is a combination of cgin and rmin find which register to hit
1192
1193 v_statement_no := '4';
1194
1195 OPEN c_rg_modvat_account(p_organization_id, r.location_id);
1196 FETCH c_rg_modvat_account INTO v_modvat_rm_account_id, v_modvat_cg_account_id;
1197 CLOSE c_rg_modvat_account;
1198
1199 v_statement_no := '4.1';
1200 IF v_register_type = 'A' THEN
1201 v_rg_account_id := v_modvat_rm_account_id;
1202 ELSIF v_register_type = 'C' THEN
1203 v_rg_account_id := v_modvat_cg_account_id;
1204 END IF;
1205
1206 -- Required Accounts are not specified at the location where RG23 PART II entry will be posted. So, through out an error
1207 IF v_rg_account_id IS NULL THEN
1208 IF v_register_type = 'A' THEN
1209 v_err_message := 'ERROR: Cenvat RMIN Account is not specified for Location:'||r.location_id;
1210 ELSE
1211 v_err_message := 'ERROR: Cenvat CGIN Account is not specified for Location:'||r.location_id;
1212 END IF;
1213
1214 FND_FILE.PUT_LINE( FND_FILE.log, v_err_message);
1215 RAISE_APPLICATION_ERROR( -20099, v_err_message);
1216 END IF;
1217
1218 v_statement_no := '5';
1219
1220 OPEN c_vendor(v_shipment_header_id);
1221 FETCH c_vendor INTO v_vendor_id, v_vendor_site_id, v_receipt_num;
1222 CLOSE c_vendor;
1223
1224 v_statement_no := '7';
1225
1226 OPEN c_slno_balance(p_organization_id, r.location_id, v_fin_year, v_register_type);
1227 FETCH c_slno_balance INTO v_slno, v_balance;
1228 CLOSE c_slno_balance;
1229 v_rg23_balance := nvl(v_balance,0);
1230
1231 v_statement_no := '8';
1232
1233 IF v_slno IS NULL or v_slno = 0 THEN
1234 v_slno := 1;
1235 ELSE
1236 v_slno := v_slno + 1;
1237 END IF;
1238
1239 v_statement_no := '8.1';
1240
1241 INSERT INTO JAI_CMN_RG_23AC_II_TRXS(
1242 REGISTER_ID, ORGANIZATION_ID, LOCATION_ID, FIN_YEAR, INVENTORY_ITEM_ID, SLNO,
1243 CR_BASIC_ED, DR_BASIC_ED,
1244 CR_ADDITIONAL_ED, DR_ADDITIONAL_ED, --Added by Sanjikum for Bug #4049363
1245 CR_ADDITIONAL_CVD,DR_ADDITIONAL_CVD, /* ADDED THE COLUMNS CR_ADDITIONAL_CVD,DR_ADDITIONAL_CVD FOR THE ENHANCEMENT 5228046 */
1246 CR_OTHER_ED, DR_OTHER_ED, --Added by Sanjikum for Bug #4049363
1247 ROUNDING_ID, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
1248 TRANSACTION_SOURCE_NUM, RECEIPT_REF, REGISTER_TYPE, REMARKS,
1249 VENDOR_ID, VENDOR_SITE_ID, CUSTOMER_ID, CUSTOMER_SITE_ID,
1250 OPENING_BALANCE, CLOSING_BALANCE, CHARGE_ACCOUNT_ID,
1251 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
1252 , TRANSACTION_DATE, OTHER_TAX_CREDIT, OTHER_TAX_DEBIT--Added by Sanjikum
1253 ) VALUES (
1254 JAI_CMN_RG_23AC_II_TRXS_S.nextval, p_organization_id, r.location_id , v_fin_year, 0, v_slno,
1255 --Added by Sanjikum for Bug #4049363
1256 v_rounded_cr_rg23_amount.basic, v_rounded_dr_rg23_amount.basic,
1257 v_rounded_cr_rg23_amount.additional, v_rounded_dr_rg23_amount.additional,
1258 v_rounded_cr_rg23_amount.additional_cvd, v_rounded_dr_rg23_amount.additional_cvd,/* added for the enhancement 5228046 */
1259 v_rounded_cr_rg23_amount.other, v_rounded_dr_rg23_amount.other,
1260 -1, r.excise_invoice_no, r.excise_invoice_date,
1261 18, r.shipment_header_id, v_register_type, 'Rounding Entry for Receipt No:'||v_receipt_num,
1262 v_vendor_id, v_vendor_site_id, NULL, NULL,
1263 v_rg23_balance, v_rg23_balance + v_rounded_amount_rg23, v_rg_account_id,
1264 SYSDATE, v_created_by, SYSDATE, v_created_by, v_last_update_login
1265 , v_today, v_rounded_cr_oth_amount.total, v_rounded_dr_oth_amount.total
1266
1267 ) RETURNING register_id INTO v_register_id_part_ii;
1268
1269 DECLARE
1270 v_tax_type JAI_CMN_RG_OTHERS.tax_type%TYPE;
1271 v_dr_amt JAI_CMN_RG_OTHERS.debit%TYPE;
1272 v_cr_amt JAI_CMN_RG_OTHERS.credit%TYPE;
1273 BEGIN
1274
1275 FOR I in 1..4 LOOP -- Date 16/04/2007 by sacsethi for Bug#5989740
1276
1277 /* Vijay Shankar for Bug#4103161
1278 SELECT DECODE(i, 1, jai_constants.tax_type_exc_edu_cess, 2, jai_constants.tax_type_cvd_edu_cess) tax_type,
1279 DECODE(i, 1, v_rounded_dr_oth_amount.excise_edu_cess, 2, v_rounded_dr_oth_amount.cvd_edu_cess) dr_cess_amount,
1280 DECODE(i, 1, v_rounded_cr_oth_amount.excise_edu_cess, 2, v_rounded_cr_oth_amount.cvd_edu_cess) cr_cess_amount
1281 INTO v_tax_type, v_dr_amt, v_cr_amt
1282 FROM dual;
1283 */
1284
1285 if i = 1 then
1286 v_tax_type := jai_constants.tax_type_exc_edu_cess;
1287 v_dr_amt := v_rounded_dr_oth_amount.excise_edu_cess;
1288 v_cr_amt := v_rounded_cr_oth_amount.excise_edu_cess;
1289 elsif i = 2 then
1290 v_tax_type := jai_constants.tax_type_cvd_edu_cess;
1291 v_dr_amt := v_rounded_dr_oth_amount.cvd_edu_cess;
1292 v_cr_amt := v_rounded_cr_oth_amount.cvd_edu_cess;
1293 -- Date 16/04/2007 by sacsethi for Bug#5989740
1294 elsif i = 3 then
1295 v_tax_type := jai_constants.tax_type_sh_exc_edu_cess;
1296 v_dr_amt := v_rounded_dr_oth_amount.sh_excise_edu_cess;
1297 v_cr_amt := v_rounded_cr_oth_amount.sh_excise_edu_cess;
1298 elsif i = 4 then
1299 v_tax_type := jai_constants.tax_type_sh_cvd_edu_cess;
1300 v_dr_amt := v_rounded_dr_oth_amount.sh_cvd_edu_cess;
1301 v_cr_amt := v_rounded_cr_oth_amount.sh_cvd_edu_cess;
1302 -- end 5989740
1303 end if;
1304
1305 IF NVL(v_dr_amt,0) <> 0 OR NVL(v_cr_amt,0) <> 0 THEN
1306 INSERT INTO JAI_CMN_RG_OTHERS
1307 (RG_OTHER_ID,
1308 SOURCE_TYPE,
1309 SOURCE_REGISTER,
1310 SOURCE_REGISTER_ID,
1311 TAX_TYPE,
1312 DEBIT,
1313 CREDIT,
1314 OPENING_BALANCE,
1315 CLOSING_BALANCE,
1316 CREATED_BY,
1317 CREATION_DATE,
1318 LAST_UPDATED_BY,
1319 LAST_UPDATE_DATE,
1320 LAST_UPDATE_LOGIN)
1321 VALUES
1322 (JAI_CMN_RG_OTHERS_S.nextval,
1323 1,
1324 DECODE(v_register_type,'A','RG23A_P2','C','RG23C_P2'),/*for bug 5054176*/
1325 v_register_id_part_ii,
1326 v_tax_type,
1327 v_dr_amt,
1328 v_cr_amt,
1329 NULL,
1330 NULL,
1331 v_created_by,
1332 sysdate,
1333 v_created_by,
1334 sysdate,
1335 v_last_update_login);
1336 END IF;
1337 END LOOP;
1338 END;
1339
1340 v_statement_no := '10a';
1341 -- this call will update Rounding RG23 PartII entry with Period_balance_id and updates all records of JAI_CMN_RG_PERIOD_BALS
1342 -- that come after the period in which parent Excise invoice has hit RG
1343 jai_cmn_rg_period_bals_pkg.adjust_rounding(v_register_id_part_ii, v_period_balance_id, v_no_of_periods_updated);
1344
1345 v_statement_no := '11';
1346 INSERT INTO JAI_CMN_RG_ROUND_HDRS(
1347 ROUNDING_ID, SOURCE_HEADER_ID, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
1348 EXCISE_AMT_BEFORE_ROUNDING, EXCISE_AMT_AFTER_ROUNDING,
1349 --Added the below 3 by Sanjikum for Bug #4049363
1350 BASIC_ED, ROUNDED_BASIC_ED,
1351 ADDITIONAL_ED, ROUNDED_ADDITIONAL_ED,
1352 ADDITIONAL_CVD, ROUNDED_ADDITIONAL_CVD, /* ADDED FOR THE BUG 5228046 -ADDITIONAL CVD ENHANCEMENT*/
1353 OTHER_ED, ROUNDED_OTHER_ED,
1354 EXCISE_EDU_CESS, ROUNDED_EXCISE_EDU_CESS,
1355 sh_excise_edu_cess, rounded_sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
1356 CVD_EDU_CESS, ROUNDED_CVD_EDU_CESS,
1357 sh_cvd_edu_cess, rounded_sh_cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
1358 REGISTER_ID, SOURCE, SRC_TRANSACTION_TYPE,
1359 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
1360 , register, -- Vijay Shankar for Bug#4103161
1361 program_application_id, program_id, program_login_id, request_id
1362
1363 ) VALUES (
1364 v_rounding_id, v_shipment_header_id, v_excise_invoice_no, v_excise_invoice_date,
1365 v_tot_amount.total, v_tot_rounded_amount.total,
1366 --Added the below 6 by Sanjikum for Bug #4049363
1367 v_tot_amount.basic, v_tot_rounded_amount.basic,
1368 v_tot_amount.additional, v_tot_rounded_amount.additional,
1369 v_tot_amount.additional_cvd, v_tot_rounded_amount.additional_cvd,/* added for the bug 5228046 -additional cvd enhancement*/
1370 v_tot_amount.other, v_tot_rounded_amount.other,
1371 v_tot_amount.excise_edu_cess, v_tot_rounded_amount.excise_edu_cess,
1372 v_tot_amount.sh_excise_edu_cess, v_tot_rounded_amount.sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
1373 v_tot_amount.cvd_edu_cess, v_tot_rounded_amount.cvd_edu_cess,
1374 v_tot_amount.sh_cvd_edu_cess, v_tot_rounded_amount.sh_cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
1375 v_register_id_part_ii, 'PO', v_transaction_type,
1376 SYSDATE, v_created_by, SYSDATE, v_created_by, v_last_update_login
1377 , LV_RG23_REGISTER, -- Vijay Shankar for Bug#4103161
1378 fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'), fnd_profile.value('CONC_REQUEST_ID')
1379 );
1380
1381 v_statement_no := '12';
1382
1383 -- 2 (CR, DR) GL Interface related calls has to be coded
1384 -- Modvat Account entries
1385 pass_accounting(
1386 p_organization_id => p_organization_id,
1387 p_transaction_id => v_rounding_id,
1388 p_transaction_date => v_today,
1389 p_shipment_line_id => -1,
1390 p_acct_type => v_acct_type,
1391 p_acct_nature => v_acct_nature,
1392 p_source => v_source_name,
1393 p_category => v_category_name,
1394 p_code_combination_id => v_rg_account_id,
1395 p_entered_dr => v_rounded_cr_amount,
1396 p_entered_cr => v_rounded_dr_amount,
1397 p_created_by => v_created_by,
1398 p_currency_code => 'INR',
1399 p_currency_conversion_type => NULL,
1400 p_currency_conversion_date => NULL,
1401 p_currency_conversion_rate => NULL,
1402 p_receipt_num => v_receipt_num
1403 );
1404
1405 v_statement_no := '13';
1406
1407 jai_cmn_gl_pkg.create_gl_entry(
1408 p_organization_id => p_organization_id,
1409 p_currency_code => 'INR',
1410 p_credit_amount => v_rounded_dr_amount,
1411 p_debit_amount => v_rounded_cr_amount,
1412 p_cc_id => v_rg_account_id,
1413 p_je_source_name => v_source_name,
1414 p_je_category_name => v_category_name,
1415 p_created_by => v_created_by,
1416 p_accounting_date => v_today,
1417 p_reference_10 => 'India Local Rounding Entry for Shipment_Header_id:'||r.shipment_header_id
1418 ||', excise_invoice_no:'||r.excise_invoice_no||', exc_inv_date:'||r.excise_invoice_date,
1419 p_reference_23 => 'ja_in_rg_rounding_p',
1420 p_reference_24 => 'JAI_CMN_RG_ROUND_HDRS',
1421 p_reference_25 => 'ROUNDING_ID',
1422 p_reference_26 => v_rounding_id
1423 );
1424
1425 v_statement_no := '14';
1426
1427 -- Rounding Account entries
1428 pass_accounting(
1429 p_organization_id => p_organization_id,
1430 p_transaction_id => v_rounding_id,
1431 p_transaction_date => v_today,
1432 p_shipment_line_id => -1,
1433 p_acct_type => v_acct_type,
1434 p_acct_nature => v_acct_nature,
1435 p_source => v_source_name,
1436 p_category => v_category_name,
1437 p_code_combination_id => v_rg_rounding_account_id,
1438 p_entered_dr => v_rounded_dr_amount,
1439 p_entered_cr => v_rounded_cr_amount,
1440 p_created_by => v_created_by,
1441 p_currency_code => 'INR',
1442 p_currency_conversion_type => NULL,
1443 p_currency_conversion_date => NULL,
1444 p_currency_conversion_rate => NULL,
1445 p_receipt_num => v_receipt_num
1446 );
1447
1448 v_statement_no := '15';
1449
1450 jai_cmn_gl_pkg.create_gl_entry(
1451 p_organization_id => p_organization_id,
1452 p_currency_code => 'INR',
1453 p_credit_amount => v_rounded_cr_amount,
1454 p_debit_amount => v_rounded_dr_amount,
1455 p_cc_id => v_rg_rounding_account_id,
1456 p_je_source_name => v_source_name,
1457 p_je_category_name => v_category_name,
1458 p_created_by => v_created_by,
1459 p_accounting_date => v_today,
1460 p_reference_10 => 'India Local Rounding Entry for Shipment_Header_id:'||r.shipment_header_id
1461 ||', excise_invoice_no:'||r.excise_invoice_no||', exc_inv_date:'||r.excise_invoice_date,
1462 p_reference_23 => 'ja_in_rg_rounding_p',
1463 p_reference_24 => 'JAI_CMN_RG_ROUND_HDRS',
1464 p_reference_25 => 'ROUNDING_ID',
1465 p_reference_26 => v_rounding_id
1466 );
1467
1468 v_statement_no := '16';
1469
1470 IF v_register_type = 'A' THEN
1471 UPDATE JAI_CMN_RG_BALANCES
1472 SET rg23a_balance = nvl(rg23a_balance, 0) + v_rounded_amount_rg23
1473 WHERE organization_id = p_organization_id
1474 AND location_id = r.location_id;
1475
1476 ELSIF v_register_type = 'C' THEN
1477 UPDATE JAI_CMN_RG_BALANCES
1478 SET rg23c_balance = nvl(rg23c_balance, 0) + v_rounded_amount_rg23
1479 WHERE organization_id = p_organization_id
1480 AND location_id = r.location_id;
1481
1482 END IF;
1483
1484 v_statement_no := '16.1';
1485
1486 FND_FILE.put_line( FND_FILE.log, 'v_full_cgin_case->'||v_full_cgin_case
1487 ||', cent_cnt->'||r_cgin_chk_for_2nd_claim.cent_percent_cnt
1488 ||', exc_rnd_cnt->'||v_excise_inv_rnd_cnt
1489 );
1490
1491 -- Updating the RG lines of Receipt with Rounding_ID
1492 IF v_full_cgin_case = 'Y' AND r_cgin_chk_for_2nd_claim.cent_percent_cnt > 0
1493 AND v_excise_inv_rnd_cnt=0
1494 THEN
1495
1496 FND_FILE.put_line( FND_FILE.log, '1st Rounding of CGIN in Case of 100% CGIN claim');
1497 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1498 lv_ttype_correct := 'CORRECT' ;
1499 /* Bug 4930048. Added by Lakshmi Gopalsami
1500 Added proper aliases and changed transaction_source_num
1501 to transaction_id
1502 */
1503 /* Bug 5207827. Added by Lakshmi Gopalsami
1504 Fixed performance issue for sql id - 17699668
1505 Removed EXISTS and added IN clause
1506 */
1507 UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
1508 SET jcrg23ac.rounding_id = v_rounding_id
1509 WHERE jcrg23ac.organization_id = p_organization_id
1510 AND jcrg23ac.excise_invoice_no = v_excise_invoice_no
1511 AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
1512 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1513 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
1514 AND jcrg23ac.rounding_id IS NULL
1515 AND jcrg23ac.TRANSACTION_SOURCE_NUM = 18
1516 AND jcrg23ac.receipt_ref IN (
1517 SELECT rt.transaction_id
1518 FROM rcv_transactions rt
1519 WHERE rt.shipment_header_id = v_shipment_header_id
1520 AND ( rt.transaction_type = r.transaction_type
1521 OR ( rt.transaction_type = lv_ttype_correct
1522 AND exists (select 1
1523 from rcv_transactions rt1
1524 where rt1.transaction_id = rt.parent_transaction_id
1525 and rt1.transaction_type = r.transaction_type)
1526 )
1527 )
1528 )
1529 AND register_id IN (
1530 select min(register_id) from JAI_CMN_RG_23AC_II_TRXS
1531 WHERE organization_id = p_organization_id
1532 AND excise_invoice_no = v_excise_invoice_no
1533 AND excise_invoice_date = v_excise_invoice_date
1534 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1535 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
1536 AND rounding_id IS NULL
1537 AND TRANSACTION_SOURCE_NUM = 18
1538 group by RECEIPT_REF);
1539
1540 ELSE
1541
1542 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1543
1544 lv_ttype_correct := 'CORRECT' ;
1545
1546 /* Bug 4930048. Added by Lakshmi Gopalsami
1547 Added proper aliases and changed transaction_source_num
1548 to transaction_id
1549 */
1550
1551 UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
1552 SET jcrg23ac.rounding_id = v_rounding_id
1553 WHERE jcrg23ac.organization_id = p_organization_id
1554 AND jcrg23ac.excise_invoice_no = v_excise_invoice_no
1555 AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
1556 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1557 AND nvl(vendor_site_id,-999)= nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
1558 AND jcrg23ac.rounding_id IS NULL
1559 AND jcrg23ac.TRANSACTION_SOURCE_NUM = 18
1560 AND EXISTS (
1561 SELECT rt.transaction_id
1562 FROM rcv_transactions rt
1563 WHERE rt.shipment_header_id = v_shipment_header_id
1564 AND rt.transaction_id = jcrg23ac.receipt_ref
1565 AND ( rt.transaction_type = r.transaction_type
1566 OR ( rt.transaction_type = lv_ttype_correct
1567 AND exists --'CORRECT'
1568 (select 1
1569 from rcv_transactions rt1
1570 where rt1.transaction_id= rt.parent_transaction_id
1571 and rt1.transaction_type = r.transaction_type)
1572 )
1573 )
1574 );
1575
1576 END IF;
1577
1578
1579 v_rounding_entries_made := v_rounding_entries_made + 1;
1580
1581 v_statement_no := '17';
1582 IF v_no_of_invoices_posted >= v_commit_interval THEN
1583 v_no_of_invoices_posted := 0;
1584 COMMIT;
1585 ELSE
1586 v_no_of_invoices_posted := v_no_of_invoices_posted + 1;
1587 END IF;
1588
1589 <<next_exc_inv>>
1590 v_save_point_set := false;
1591
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 v_tot_errored_entries := v_tot_errored_entries + 1;
1595 FND_FILE.PUT_LINE( FND_FILE.log, 'Error at statement_no->'|| v_statement_no
1596 ||', shipment_header_id->'||v_shipment_header_id
1597 ||', excise_invoice_no->'||v_excise_invoice_no
1598 ||', excise_invoice_date->'||v_excise_invoice_date||' '
1599 );
1600 FND_FILE.PUT_LINE( FND_FILE.log, 'ErrMess->'|| SQLERRM);
1601
1602 IF v_save_point_set THEN
1603 -- This has to rollback only if SAVEPOINT is set for the exc invoice being processed
1604 ROLLBACK TO previous_savepoint;
1605 v_save_point_set := false;
1606 END IF;
1607
1608 END;
1609
1610 --Added the below 5 by Sanjikum for Bug #4049363
1611 v_tot_amount := NULL;
1612 v_tot_rounded_amount := NULL;
1613 v_rounded_amount := NULL;
1614 v_rounded_amount_abs := NULL;
1615 v_rounded_amount_rg23 := NULL;
1616 v_rounded_cr_amount := NULL;
1617 v_rounded_dr_amount := NULL;
1618 v_rounded_cr_rg23_amount := NULL;
1619 v_rounded_dr_rg23_amount := NULL;
1620 v_rounded_cr_oth_amount := NULL;
1621 v_rounded_dr_oth_amount := NULL;
1622
1623 v_rounding_entry_type := null;
1624
1625 v_vendor_id := null;
1626 v_vendor_site_id := null;
1627 v_modvat_rm_account_id := null;
1628 v_modvat_cg_account_id := null;
1629 v_rg_account_id := null;
1630 v_rg23_balance := null;
1631 v_balance := null;
1632 v_slno := null;
1633
1634 v_rounding_id := null;
1635 v_register_id_part_ii := null;
1636
1637 v_shipment_header_id := null;
1638 v_excise_invoice_no := null;
1639 v_excise_invoice_date := null;
1640 v_rounding_type := null;
1641 v_register_type := null;
1642
1643 v_exc_inv_rnd_counter := v_exc_inv_rnd_counter + 1;
1644
1645 END LOOP;
1646
1647 END LOOP;
1648
1649 FND_FILE.PUT_LINE( FND_FILE.log, 'Completed. Total Rounding entries made -> '|| v_rounding_entries_made
1650 ||', errored entries->'||v_tot_errored_entries
1651 ||', Zero Exc. Amt. Invoices found->'||v_zero_round_found
1652 ||', total Ex. Invoices processed->'||v_tot_processed_invoices
1653 );
1654
1655 <<do_commit>>
1656 COMMIT;
1657
1658
1659 /* Added by Ramananda for bug#4407165 */
1660 EXCEPTION
1661 WHEN OTHERS THEN
1662 p_err_buf := null;
1663 p_ret_code := null;
1664 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1665 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1666 app_exception.raise_exception;
1667
1668 END do_rounding;
1669
1670 FUNCTION get_parent_register_id
1671 (
1672 p_register_id IN NUMBER
1673 ) RETURN NUMBER IS
1674
1675 /* Added by Ramananda for bug#4407165 */
1676 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_rnd_pkg.get_parent_register_id';
1677
1678 CURSOR c_part_ii_record(cp_register_id IN NUMBER) IS
1679 SELECT
1680 register_type, organization_id, location_id, excise_invoice_no, excise_invoice_date,
1681 receipt_ref shipment_header_id,
1682 vendor_id,vendor_site_id/*bgowrava for forward porting bug#5674376*/
1683 FROM JAI_CMN_RG_23AC_II_TRXS
1684 WHERE register_id = cp_register_id;
1685 r_part_ii_rec c_part_ii_record%ROWTYPE;
1686
1687 -- this cursor excludes rounding entries
1688 CURSOR c_parent_part_ii_rec( cp_register_type IN VARCHAR2, cp_organization_id IN NUMBER, cp_location_id IN NUMBER,
1689 cp_excise_invoice_no IN VARCHAR2, cp_excise_invoice_date IN DATE,
1690 cp_vendor_id NUMBER,cp_vendor_site_id NUMBER,cp_shipment_header_id NUMBER) IS
1691 SELECT min(register_id) register_id
1692 FROM JAI_CMN_RG_23AC_II_TRXS
1693 WHERE organization_id = cp_organization_id
1694 AND location_id = cp_location_id
1695 AND register_type = cp_register_type
1696 AND excise_invoice_no = cp_excise_invoice_no
1697 AND excise_invoice_date = cp_excise_invoice_date
1698 AND inventory_item_id <> 0;
1699
1700 CURSOR c_2nd_claim_register_id(cp_organization_id IN NUMBER, cp_location_id IN NUMBER,
1701 cp_excise_invoice_no IN VARCHAR2, cp_excise_invoice_date IN DATE,
1702 cp_vendor_id NUMBER,cp_vendor_site_id NUMBER) IS /*bgowrava for forward porting bug#5674376*/
1703 select min(register_id)
1704 from JAI_CMN_RG_23AC_II_TRXS a
1705 where organization_id = cp_organization_id
1706 and location_id = cp_location_id
1707 and excise_invoice_no = cp_excise_invoice_no
1708 and excise_invoice_date = cp_excise_invoice_date
1709 AND nvl(vendor_id,-999) = nvl(cp_vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1710 AND nvl(vendor_site_id,-999) = nvl(cp_vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
1711 and inventory_item_id <> 0
1712 and exists (select '1'
1713 from JAI_CMN_RG_23AC_II_TRXS
1714 where organization_id = a.organization_id
1715 and location_id = a.location_id
1716 and excise_invoice_no = a.excise_invoice_no
1717 and excise_invoice_date = a.excise_invoice_date
1718 AND nvl(vendor_id,-999) = nvl(a.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1719 AND nvl(vendor_site_id,-999) = nvl(a.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
1720 and inventory_item_id = a.inventory_item_id
1721 and receipt_ref = a.RECEIPT_REF
1722 and register_id < a.register_id
1723 );
1724
1725 cursor c_get_all_rounding_ids
1726 (cp_excise_invoice_no IN VARCHAR2, cp_excise_invoice_date IN DATE,
1727 cp_vendor_id NUMBER, cp_vendor_site_id NUMBER) is
1728 select min(register_id) minimum_rounding_id, max(register_id) maximum_rounding_id
1729 from JAI_CMN_RG_23AC_II_TRXS
1730 where inventory_item_id = 0
1731 and excise_invoice_no = cp_excise_invoice_no
1732 and excise_invoice_date = cp_excise_invoice_date
1733 and nvl(vendor_id,-999) = nvl(cp_vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1734 and nvl(vendor_site_id,-999) = nvl(cp_vendor_site_id,-999);/*bgowrava for forward porting bug#5674376*/
1735
1736
1737 r_full_cgin_chk c_full_cgin_chk%ROWTYPE;
1738 r_parent_part_ii_rec c_parent_part_ii_rec%ROWTYPE;
1739 r_cgin_chk c_cgin_chk_for_2nd_claim%ROWTYPE;
1740 r_get_all_rounding_ids c_get_all_rounding_ids%rowtype;
1741
1742 v_return_register_id NUMBER;
1743 BEGIN
1744
1745 /* Get the details of the rounding entry */
1746 OPEN c_part_ii_record(p_register_id);
1747 FETCH c_part_ii_record INTO r_part_ii_rec;
1748 CLOSE c_part_ii_record;
1749
1750 /* Check if rounding is against an excise invoice having only CGIN items */
1751 OPEN c_full_cgin_chk(r_part_ii_rec.shipment_header_id, r_part_ii_rec.excise_invoice_no,
1752 r_part_ii_rec.excise_invoice_date,r_part_ii_rec.vendor_id,r_part_ii_rec.vendor_site_id); /*bgowrava for forward porting bug#5674376*/
1753 FETCH c_full_cgin_chk INTO r_full_cgin_chk;
1754 CLOSE c_full_cgin_chk;
1755
1756 /* check if the CGIN invoice has been claimed 100 % */
1757 OPEN c_cgin_chk_for_2nd_claim(r_part_ii_rec.shipment_header_id, r_part_ii_rec.excise_invoice_no,
1758 r_part_ii_rec.excise_invoice_date,r_part_ii_rec.vendor_id,r_part_ii_rec.vendor_site_id); /*bgowrava for forward porting bug#5674376*/
1759 FETCH c_cgin_chk_for_2nd_claim INTO r_cgin_chk;
1760 CLOSE c_cgin_chk_for_2nd_claim;
1761
1762 -- Condition to test whether excise invoice is of full CGIN items and 2nd Claim is done for some/all lines
1763 IF r_full_cgin_chk.total_cnt = r_full_cgin_chk.cgin_cnt AND
1764 r_cgin_chk.cent_percent_cnt > 0
1765 THEN
1766
1767 /* Check if two rounding entry has been passed for the given excise invoice */
1768 open c_get_all_rounding_ids(r_part_ii_rec.excise_invoice_no, r_part_ii_rec.excise_invoice_date,r_part_ii_rec.vendor_id,r_part_ii_rec.vendor_site_id);/*bgowrava for forward porting bug#5674376*/
1769 fetch c_get_all_rounding_ids into r_get_all_rounding_ids;
1770 close c_get_all_rounding_ids;
1771
1772 if r_get_all_rounding_ids.minimum_rounding_id <> r_get_all_rounding_ids.maximum_rounding_id then
1773
1774 if r_get_all_rounding_ids.maximum_rounding_id = p_register_id then
1775
1776 FND_FILE.put_line(fnd_file.log, '2nd Claim Rounding Register_id is Selected');
1777
1778 OPEN c_2nd_claim_register_id
1779 (r_part_ii_rec.organization_id, r_part_ii_rec.location_id,
1780 r_part_ii_rec.excise_invoice_no, r_part_ii_rec.excise_invoice_date
1781 , r_part_ii_rec.vendor_id, r_part_ii_rec.vendor_site_id);/*bgowrava for forward porting bug#5674376*/
1782 FETCH c_2nd_claim_register_id INTO v_return_register_id;
1783 CLOSE c_2nd_claim_register_id;
1784
1785 goto exit_from_function;
1786
1787 else
1788
1789 FND_FILE.put_line(fnd_file.log, '1st Claim Rounding Register_id is Selected');
1790
1791 end if; /* 2nd claim rounding id is selected */
1792
1793 end if;
1794
1795 end if; /* excise invoice is of full CGIN items and 2nd Claim is done for some/all lines */
1796
1797 FND_FILE.put_line(fnd_file.log, 'Minimum Register_id is Selected');
1798 OPEN c_parent_part_ii_rec
1799 (r_part_ii_rec.register_type, r_part_ii_rec.organization_id,
1800 r_part_ii_rec.location_id, r_part_ii_rec.excise_invoice_no, r_part_ii_rec.excise_invoice_date,
1801 r_part_ii_rec.vendor_id,r_part_ii_rec.vendor_site_id,r_part_ii_rec.shipment_header_id);/*bgowrava for forward porting bug#5674376*/
1802 FETCH c_parent_part_ii_rec INTO v_return_register_id;
1803 CLOSE c_parent_part_ii_rec;
1804
1805 -- v_return_register_id := r_parent_part_ii_rec.register_id;
1806
1807 << exit_from_function >>
1808 return v_return_register_id;
1809
1810 /* Added by Ramananda for bug#4407165 */
1811 EXCEPTION
1812 WHEN OTHERS THEN
1813 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1814 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1815 app_exception.raise_exception;
1816
1817 END get_parent_register_id;
1818
1819 PROCEDURE pass_accounting(
1820 p_organization_id number,
1821 p_transaction_id number, -- this is the rounding_id passed in JAI_CMN_RG_ROUND_HDRS table
1822 p_transaction_date date,
1823 p_shipment_line_id number,
1824 p_acct_type varchar2,
1825 p_acct_nature varchar2,
1826 p_source varchar2,
1827 p_category varchar2,
1828 p_code_combination_id number,
1829 p_entered_dr number,
1830 p_entered_cr number,
1831 p_created_by number,
1832 p_currency_code varchar2,
1833 p_currency_conversion_type varchar2,
1834 p_currency_conversion_date varchar2,
1835 p_currency_conversion_rate varchar2,
1836 p_receipt_num varchar2
1837 ) IS
1838
1839 v_organization_code ORG_ORGANIZATION_DEFINITIONS.organization_code%TYPE;
1840 --v_receipt_num RCV_SHIPMENT_HEADERS.receipt_num%TYPE;
1841 v_period_name GL_PERIODS.period_name%TYPE;
1842
1843 v_transaction_type VARCHAR2(20);
1844 --v_shipment_header_id NUMBER;
1845
1846 CURSOR c_shipment_header_id(cp_rounding_id IN NUMBER) IS
1847 SELECt source_header_id shipment_header_id
1848 FROM JAI_CMN_RG_ROUND_HDRS
1849 WHERE rounding_id = cp_rounding_id;
1850
1851 CURSOR c_receipt_num(cp_shipment_header_id IN NUMBER) IS
1852 SELECt receipt_num
1853 FROM rcv_shipment_headers
1854 WHERE shipment_header_id = cp_shipment_header_id;
1855
1856 /* Bug 5243532. Added by Lakshmi Gopalsami
1857 * Removed org_organization_definitions from the
1858 * cursor c_orgn_code_n_period_name
1859 * and passed set_of_books_id to the cursor. Also removed
1860 * gl_sets_of_books and included gl_ledgers.
1861 */
1862
1863 CURSOR c_orgn_code_n_period_name(cp_set_of_books_id IN NUMBER) IS
1864 SELECT gd.period_name
1865 FROM gl_ledgers gle,gl_periods gd
1866 WHERE gle.ledger_id = cp_set_of_books_id
1867 AND gd.period_set_name = gle.period_set_name
1868 AND trunc(p_transaction_date) BETWEEN gd.start_date and gd.end_date
1869 AND adjustment_period_flag='N';
1870
1871 /* Added by Ramananda for bug#4407165 */
1872 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_rnd_pkg.pass_accounting';
1873
1874 /* Bug 5243532. Added by Lakshmi Gopalsami
1875 * Defined variable for implementing caching logic.
1876 */
1877 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1878 ln_set_of_books_id NUMBER;
1879
1880 BEGIN
1881
1882 v_transaction_type := 'HEADER';
1883 /* Bug 5243532. Added by Lakshmi Gopalsami
1884 * Implemented caching logic for getting organization_code
1885 */
1886 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1887 (p_org_id => p_organization_id);
1888 v_organization_code := l_func_curr_det.organization_code;
1889 ln_set_of_books_id := l_func_curr_det.ledger_id;
1890
1891 /* Bug 5243532. Added by Lakshmi Gopalsami
1892 * Passes ln_set_of_books_id instead of p_transaction_id
1893 */
1894
1895 OPEN c_orgn_code_n_period_name(ln_set_of_books_id);
1896 FETCH c_orgn_code_n_period_name INTO v_period_name;
1897 CLOSE c_orgn_code_n_period_name;
1898
1899 /*
1900 OPEN c_shipment_header_id(p_transaction_id);
1901 FETCH c_shipment_header_id INTO v_shipment_header_id;
1902 CLOSE c_shipment_header_id;
1903
1904 OPEN c_receipt_num(v_shipment_header_id);
1905 FETCH c_receipt_num INTO v_receipt_num;
1906 CLOSE c_receipt_num;
1907 */
1908 INSERT INTO JAI_RCV_JOURNAL_ENTRIES (JOURNAL_ENTRY_ID,
1909 organization_code, receipt_num, transaction_id, creation_date, transaction_date,
1910 shipment_line_id, acct_type, acct_nature, source_name, category_name,
1911 code_combination_id, entered_dr, entered_cr, transaction_type, period_name,
1912 created_by, currency_code, currency_conversion_type, currency_conversion_date,
1913 currency_conversion_rate
1914 ) VALUES ( JAI_RCV_JOURNAL_ENTRIES_S.nextval,
1915 v_organization_code, p_receipt_num, p_transaction_id, sysdate, p_transaction_date,
1916 p_shipment_line_id, p_acct_type, p_acct_nature, p_source, p_category,
1917 p_code_combination_id, p_entered_dr, p_entered_cr, v_transaction_type, v_period_name,
1918 p_created_by, p_currency_code, p_currency_conversion_type, p_currency_conversion_date,
1919 p_currency_conversion_rate
1920 );
1921
1922
1923 /* Added by Ramananda for bug#4407165 */
1924 EXCEPTION
1925 WHEN OTHERS THEN
1926 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1927 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1928 app_exception.raise_exception;
1929 END pass_accounting;
1930
1931
1932 /* Following Procedure added as part of RTV Rounding Resolution. Bug#4103161 */
1933 PROCEDURE do_rtv_rounding(
1934 P_ORGANIZATION_ID IN NUMBER,
1935 P_TRANSACTION_TYPE IN VARCHAR2,
1936 P_REGISTER_TYPE IN VARCHAR2,
1937 P_EX_INVOICE_FROM_DATE IN DATE,
1938 P_EX_INVOICE_TO_DATE IN DATE
1939 ) IS
1940
1941 /* Added by Ramananda for bug#4407165 */
1942 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_rnd_pkg.do_rtv_rounding';
1943
1944 TYPE amount_record IS RECORD( basic NUMBER,
1945 additional NUMBER,
1946 additional_cvd NUMBER, /* 5228046 change by sacsethi */
1947 other NUMBER,
1948 excise_edu_cess NUMBER,
1949 cvd_edu_cess NUMBER,
1950 sh_excise_edu_cess NUMBER, -- Date 16/04/2007 by
1951 sh_cvd_edu_cess NUMBER, -- sacsethi for Bug#5989740
1952 total NUMBER);
1953
1954
1955 v_zero_record AMOUNT_RECORD;
1956
1957 v_tot_amount AMOUNT_RECORD;
1958 v_tot_rounded_amount AMOUNT_RECORD;
1959 v_rounded_amount AMOUNT_RECORD;
1960
1961 v_rounded_cr_rg23_amount AMOUNT_RECORD;
1962 v_rounded_dr_rg23_amount AMOUNT_RECORD;
1963 v_rounded_cr_oth_amount AMOUNT_RECORD;
1964 v_rounded_dr_oth_amount AMOUNT_RECORD;
1965
1966 v_rounded_amount_abs NUMBER;
1967 v_rounded_amount_rg23 NUMBER;
1968 v_rounded_cr_amount NUMBER;
1969 v_rounded_dr_amount NUMBER;
1970
1971 v_rounding_entry_type VARCHAR2(2);
1972
1973 v_commit_interval NUMBER(5) ;--File.Sql.35 Cbabu := 50;
1974 v_rounding_precision NUMBER(2) ;--File.Sql.35 Cbabu := 0;
1975 v_acct_type VARCHAR2(20) ;--File.Sql.35 Cbabu := 'REGULAR';
1976 v_acct_nature VARCHAR2(20) ;--File.Sql.35 Cbabu := 'CENVAT-ROUNDING';
1977 v_source_name VARCHAR2(20) ;--File.Sql.35 Cbabu := 'Purchasing India';
1978 v_category_name VARCHAR2(20) ;--File.Sql.35 Cbabu := 'Receiving India';
1979 v_statement_no VARCHAR2(4) ;--File.Sql.35 Cbabu := '0';
1980 v_err_message VARCHAR2(100) ;--File.Sql.35 Cbabu := '';
1981
1982 v_rounding_entries_made NUMBER ;--File.Sql.35 Cbabu := 0;
1983 v_tot_errored_entries NUMBER ;--File.Sql.35 Cbabu := 0;
1984 v_zero_round_found NUMBER ;--File.Sql.35 Cbabu := 0;
1985 v_tot_processed_invoices NUMBER ;--File.Sql.35 Cbabu := 0;
1986 v_no_of_invoices_posted NUMBER ;--File.Sql.35 Cbabu := 0;
1987 v_save_point_set BOOLEAN ;--File.Sql.35 Cbabu := FALSE;
1988
1989 v_fin_year NUMBER(9);
1990
1991 v_vendor_id NUMBER;
1992 v_vendor_site_id NUMBER;
1993 v_rg23_balance NUMBER;
1994
1995 v_modvat_rm_account_id NUMBER(15);
1996 v_modvat_cg_account_id NUMBER(15);
1997 v_modvat_pla_account_id NUMBER(15);
1998 v_rg_rounding_account_id NUMBER(15);
1999
2000 v_rg_account_id NUMBER(15);
2001
2002 v_register_id_part_ii NUMBER;
2003 v_rounding_id NUMBER;
2004
2005 v_created_by NUMBER ;--File.Sql.35 Cbabu := nvl(FND_GLOBAL.USER_ID, -1);
2006 v_last_update_login NUMBER ;--File.Sql.35 Cbabu := nvl(FND_GLOBAL.LOGIN_ID,- 1);
2007 v_today DATE ;--File.Sql.35 Cbabu := trunc(SYSDATE);
2008
2009 v_excise_invoice_no JAI_CMN_RG_23AC_II_TRXS.excise_invoice_no%TYPE;
2010 v_excise_invoice_date JAI_CMN_RG_23AC_II_TRXS.excise_invoice_date%TYPE;
2011 v_register_type JAI_CMN_RG_23AC_II_TRXS.register_type%TYPE;
2012 v_line_type_c_cnt NUMBER ;--File.Sql.35 Cbabu := 0;
2013 v_tot_lines_cnt NUMBER ;--File.Sql.35 Cbabu := 0;
2014
2015 CURSOR c_vendor(p_shipment_header_id IN NUMBER) IS
2016 SELECT vendor_id, vendor_site_id, receipt_num
2017 FROM rcv_shipment_headers
2018 WHERE shipment_header_id = p_shipment_header_id;
2019
2020 v_slno NUMBER;
2021 v_balance NUMBER;
2022
2023 CURSOR c_slno_balance_rg23p2(p_organization_id IN NUMBER, p_location_id IN NUMBER,
2024 p_fin_year IN NUMBER, p_register_type IN VARCHAR2) IS
2025 SELECT slno, closing_balance
2026 FROM JAI_CMN_RG_23AC_II_TRXS
2027 WHERE organization_id = p_organization_id
2028 AND location_id = p_location_id
2029 AND fin_year = p_fin_year
2030 AND register_type = p_register_type
2031 AND slno = (SELECT max(slno) slno
2032 FROM JAI_CMN_RG_23AC_II_TRXS
2033 WHERE organization_id = p_organization_id
2034 AND location_id = p_location_id
2035 AND fin_year = p_fin_year
2036 AND register_type = p_register_type);
2037
2038
2039 CURSOR c_active_fin_year(p_organization_id IN NUMBER) IS
2040 SELECT max(fin_year)
2041 FROM JAI_CMN_FIN_YEARS
2042 WHERE organization_id = p_organization_id
2043 AND fin_active_flag = 'Y';
2044
2045 CURSOR c_rg_rounding_account(p_organization_id IN NUMBER) IS
2046 SELECT rg_rounding_account_id
2047 FROM JAI_CMN_INVENTORY_ORGS
2048 WHERE organization_id = p_organization_id
2049 AND ( location_id IS NULL OR location_id = 0);
2050
2051 CURSOR c_rg_modvat_account(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
2052 SELECT modvat_rm_account_id, modvat_cg_account_id, modvat_pla_account_id
2053 FROM JAI_CMN_INVENTORY_ORGS
2054 WHERE organization_id = p_organization_id
2055 AND location_id = p_location_id;
2056
2057 ln_receive_qty NUMBER;
2058 CURSOR c_ja_in_receive_qty(cp_shipment_line_id IN NUMBER) IS
2059 SELECT qty_received
2060 FROM JAI_RCV_LINES
2061 WHERE shipment_line_id = cp_shipment_line_id;
2062
2063 --Added the below 2 by Sanjikum for Bug #4049363
2064 ln_cenvat_amount AMOUNT_RECORD;
2065 ln_receive_amount AMOUNT_RECORD;
2066
2067 CURSOR c_receipt_tax_amount(cp_shipment_line_id IN NUMBER) IS
2068 SELECT --Added the 6 columns by Sanjikum for Bug #4049363
2069 sum(decode(upper(b.tax_type), 'EXCISE', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) excise_amt,
2070 sum(decode(upper(b.tax_type), 'ADDL. EXCISE', b.tax_amount * nvl(c.mod_cr_percentage, 0),
2071 'CVD', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) additional_excise_amt,
2072 sum(decode(upper(b.tax_type), jai_constants.tax_type_add_cvd, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) additional_cvd,/*5228046 Addtional cvd Enhancement*/
2073 sum(decode(upper(b.tax_type), 'OTHER EXCISE', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) other_excise_amt,
2074 sum(decode(b.tax_type, jai_constants.tax_type_exc_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) excise_edu_cess_amt,
2075 sum(decode(b.tax_type, jai_constants.tax_type_cvd_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) cvd_edu_cess_amt ,
2076 sum(decode(b.tax_type, jai_constants.tax_type_sh_exc_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) sh_excise_edu_cess_amt, -- Date 16/04/2007 by sacsethi for Bug#5989740
2077 sum(decode(b.tax_type, jai_constants.tax_type_sh_cvd_edu_cess, b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) sh_cvd_edu_cess_amt
2078 FROM JAI_RCV_LINE_TAXES b, JAI_CMN_TAXES_ALL c
2079 WHERE b.shipment_line_id = cp_shipment_line_id
2080 AND b.tax_id = c.tax_id
2081 AND upper(b.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',JAI_CONSTANTS.tax_type_add_cvd,
2082 jai_constants.tax_type_exc_edu_cess, jai_constants.tax_type_cvd_edu_cess,
2083 jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess -- Date 16/04/2007 by sacsethi for Bug#5989740
2084 )
2085 AND b.modvat_flag = 'Y';
2086
2087 v_already_rounded_chk NUMBER;
2088 v_excise_inv_rnd_cnt NUMBER;
2089
2090 CURSOR c_already_rounded_chk(p_source_header_id IN NUMBER, p_excise_invoice_no IN VARCHAR2,
2091 p_excise_invoice_date IN DATE, p_transaction_type IN VARCHAR2) IS
2092 SELECT max(rounding_id) rounding_id,
2093 count(1)
2094 FROM JAI_CMN_RG_ROUND_HDRS
2095 WHERE source_header_id = p_source_header_id
2096 AND excise_invoice_no = p_excise_invoice_no
2097 AND excise_invoice_date = p_excise_invoice_date
2098 AND src_transaction_type = p_transaction_type
2099 GROUP BY rounding_id, excise_invoice_no, excise_invoice_date;
2100
2101 /*Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
2102 ln_rtv_excise_batch_group_id jai_rcv_rtv_batch_trxs.excise_batch_group_id%TYPE;
2103 CURSOR c_excise_batch_group_id(cpn_transaction_id number) IS
2104 select excise_batch_group_id
2105 from jai_rcv_rtv_batch_trxs
2106 where transaction_id = cpn_transaction_id;
2107
2108 v_no_of_periods_updated NUMBER(15);
2109 v_period_balance_id NUMBER(15);
2110
2111 v_exc_inv_rnd_counter NUMBER ;--File.Sql.35 Cbabu := 0;
2112
2113 lv_process_status VARCHAR2(3);
2114 lv_process_message VARCHAR2(1996);
2115 v_receipt_num rcv_shipment_headers.receipt_num%TYPE;
2116 v_pla_register_id NUMBER;
2117
2118 lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type ;
2119
2120 BEGIN
2121
2122 v_statement_no := '0.1';
2123 v_commit_interval := 50;
2124 v_rounding_precision := 0;
2125 v_acct_type := 'REGULAR';
2126 v_acct_nature := 'CENVAT-ROUNDING';
2127 v_source_name := 'Purchasing India';
2128 v_category_name := 'Receiving India';
2129 v_statement_no := '0';
2130 v_err_message := '';
2131 v_rounding_entries_made := 0;
2132 v_tot_errored_entries := 0;
2133 v_zero_round_found := 0;
2134 v_tot_processed_invoices := 0;
2135 v_no_of_invoices_posted := 0;
2136 v_save_point_set := FALSE;
2137 v_created_by := nvl(FND_GLOBAL.USER_ID, -1);
2138 v_last_update_login := nvl(FND_GLOBAL.LOGIN_ID,- 1);
2139 v_today := trunc(SYSDATE);
2140 v_line_type_c_cnt := 0;
2141 v_tot_lines_cnt := 0;
2142 v_exc_inv_rnd_counter := 0;
2143
2144 if gb_debug then
2145 fnd_file.put_line(fnd_file.log, '-1-Start of Procedure');
2146 end if;
2147
2148 OPEN c_rg_rounding_account(p_organization_id);
2149 FETCH c_rg_rounding_account INTO v_rg_rounding_account_id;
2150 CLOSE c_rg_rounding_account;
2151
2152 v_statement_no := '0.2';
2153
2154 IF v_rg_rounding_account_id IS NULL THEN
2155 fnd_file.put_line(fnd_file.log, 'ERROR: Rounding Account is not specified at Organization Level');
2156 RAISE_APPLICATION_ERROR( -20099, 'Rounding Account is not specified at Organization Level');
2157 END IF;
2158
2159 v_statement_no := '0.3';
2160
2161 OPEN c_active_fin_year(p_organization_id);
2162 FETCH c_active_fin_year INTO v_fin_year;
2163 CLOSE c_active_fin_year;
2164
2165 --Added by Sanjikum for Bug #4049363
2166 v_zero_record.basic := 0;
2167 v_zero_record.additional := 0;
2168 v_zero_record.additional_cvd := 0;/*5228046 Addtional cvd Enhancement*/
2169 v_zero_record.other := 0;
2170 v_zero_record.excise_edu_cess := 0;
2171 v_zero_record.cvd_edu_cess := 0;
2172 v_zero_record.total := 0;
2173
2174 v_tot_amount := v_zero_record;
2175
2176 if gb_debug then
2177 fnd_file.put_line(fnd_file.log, '0-Before MAIN LOOP');
2178 end if;
2179
2180 -- PART II A, C rounding entries can be posted into one of the Registers A or C
2181 lv_ttype_correct := 'CORRECT' ;
2182 FOR r IN (
2183 select
2184 LV_RG23_REGISTER register,
2185 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. b.shipment_header_id shipment_header_id,
2186 min(b.shipment_header_id) min_shipment_header_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2187 count( distinct shipment_header_id) cnt_shipment_header_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2188 a.excise_invoice_no excise_invoice_no,
2189 a.excise_invoice_date excise_invoice_date,
2190 a.vendor_id Vendor_id,/*bgowrava for forward porting bug#5674376*/
2191 a.vendor_site_id vendor_site_id,/*bgowrava for forward porting bug#5674376*/
2192 p_transaction_type transaction_type,
2193 max(to_number(a.RECEIPT_REF)) rcv_transaction_id,
2194 min(a.location_id) location_id,
2195 nvl(sum(a.cr_basic_ed), 0) cr_basic_ed,
2196 nvl(sum(a.cr_additional_ed), 0) cr_additional_ed,
2197 nvl(sum(a.cr_additional_cvd),0) cr_additional_cvd,/*5228046 Addtional cvd Enhancement*/
2198 nvl(sum(a.cr_other_ed), 0) cr_other_ed,
2199 nvl(sum(a.dr_basic_ed), 0) dr_basic_ed,
2200 nvl(sum(a.dr_additional_ed), 0) dr_additional_ed,
2201 nvl(sum(a.dr_additional_cvd),0) dr_additional_cvd,/*5228046 Addtional cvd Enhancement*/
2202 nvl(sum(a.dr_other_ed), 0) dr_other_ed,
2203 nvl(sum( decode(c.tax_type, 'EXCISE_EDUCATION_CESS', c.credit, 0)), 0) cr_exc_edu_cess,
2204 nvl(sum( decode(c.tax_type, 'CVD_EDUCATION_CESS', c.credit, 0)), 0) cr_cvd_edu_cess,
2205 nvl(sum( decode(c.tax_type, jai_constants.tax_type_sh_exc_edu_cess, c.credit, 0)), 0) cr_sh_exc_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
2206 nvl(sum( decode(c.tax_type, jai_constants.tax_type_sh_cvd_edu_cess, c.credit, 0)), 0) cr_sh_cvd_edu_cess,
2207 nvl(sum( decode(c.tax_type, 'EXCISE_EDUCATION_CESS', c.debit, 0)), 0) dr_exc_edu_cess,
2208 nvl(sum( decode(c.tax_type, 'CVD_EDUCATION_CESS', c.debit, 0)), 0) dr_cvd_edu_cess,
2209 nvl(sum( decode(c.tax_type, jai_constants.tax_type_sh_exc_edu_cess, c.debit, 0)), 0) dr_sh_exc_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
2210 nvl(sum( decode(c.tax_type, jai_constants.tax_type_sh_cvd_edu_cess, c.debit, 0)), 0) dr_sh_cvd_edu_cess,
2211 null transaction_date
2212 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. from JAI_CMN_RG_23AC_II_TRXS a, rcv_transactions b, JAI_CMN_RG_OTHERS c
2213 from JAI_CMN_RG_23AC_II_TRXS a, JAI_RCV_TRANSACTIONS b, JAI_CMN_RG_OTHERS c
2214 where a.RECEIPT_REF = b.transaction_id
2215 AND a.organization_id = p_organization_id
2216 AND c.source_type(+) = 1 -- this means register is JAI_CMN_RG_23AC_II_TRXS
2217 AND a.register_id = c.source_register_id(+)
2218 AND (
2219 (p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NULL)
2220 OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NULL
2221 AND a.excise_invoice_date >= p_ex_invoice_from_date)
2222 OR (p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NOT NULL
2223 AND a.excise_invoice_date <= p_ex_invoice_to_date)
2224 OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NOT NULL
2225 AND a.excise_invoice_date BETWEEN p_ex_invoice_from_date AND p_ex_invoice_to_date)
2226 )
2227 AND a.rounding_id IS NULL
2228 AND a.TRANSACTION_SOURCE_NUM = 18
2229 AND ( (b.transaction_type = p_transaction_type)
2230 or (b.transaction_type = lv_ttype_correct and b.parent_transaction_type = p_transaction_type --'CORRECT'
2231 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. exists (select 1 from JAI_RCV_TRANSACTIONS
2232 where transaction_id = b.parent_transaction_id
2233 and transaction_type = p_transaction_type)*/
2234 )
2235 )
2236 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. GROUP BY b.shipment_header_id, a.excise_invoice_no, a.excise_invoice_date
2237 GROUP BY a.excise_invoice_no, a.excise_invoice_date,
2238 a.vendor_id , /*bgowrava for forward porting bug#5674376*/
2239 a.vendor_site_id /*bgowrava for forward porting bug#5674376*/
2240 UNION ALL
2241 select
2242 LV_PLA_REGISTER register,
2243 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. b.shipment_header_id shipment_header_id,
2244 min(b.shipment_header_id) min_shipment_header_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2245 count( distinct shipment_header_id) cnt_shipment_header_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2246 a.DR_INVOICE_NO excise_invoice_no,
2247 a.dr_invoice_date excise_invoice_date,
2248 a.vendor_id Vendor_id,/*bgowrava for forward porting bug#5674376*/
2249 a.vendor_site_id vendor_site_id,/*bgowrava for forward porting bug#5674376*/
2250 p_transaction_type transaction_type,
2251 max(to_number(ref_document_id)) rcv_transaction_id,
2252 min(a.location_id) location_id,
2253 nvl(sum(a.cr_basic_ed),0) cr_basic_ed,
2254 nvl(sum(a.cr_additional_ed), 0) cr_additional_ed,
2255 to_number(null) cr_additional_cvd, /*5228046 Addtional cvd Enhancement*/
2256 nvl(sum(a.cr_other_ed), 0) cr_other_ed,
2257 nvl(sum(a.dr_basic_ed), 0) dr_basic_ed,
2258 nvl(sum(a.dr_additional_ed), 0) dr_additional_ed,
2259 to_number(null) dr_additional_cvd, /*5228046 Addtional cvd Enhancement*/
2260 nvl(sum(a.dr_other_ed), 0) dr_other_ed,
2261 nvl(sum( decode(c.tax_type, 'EXCISE_EDUCATION_CESS', c.credit, 0)), 0) cr_exc_edu_cess,
2262 nvl(sum( decode(c.tax_type, 'CVD_EDUCATION_CESS', c.credit, 0)), 0) cr_cvd_edu_cess,
2263 -- Date 16/04/2007 by sacsethi for Bug#5989740
2264 nvl(sum( decode(c.tax_type, 'EXCISE_SH_EDU_CESS', c.credit, 0)), 0) cr_sh_exc_edu_cess,
2265 nvl(sum( decode(c.tax_type, 'CVD_SH_EDU_CESS', c.credit, 0)), 0) cr_sh_cvd_edu_cess,
2266 -- end
2267 nvl(sum( decode(c.tax_type, 'EXCISE_EDUCATION_CESS', c.debit, 0)), 0) dr_exc_edu_cess,
2268 nvl(sum( decode(c.tax_type, 'CVD_EDUCATION_CESS', c.debit, 0)), 0) dr_cvd_edu_cess,
2269 -- Date 16/04/2007 by sacsethi for Bug#5989740
2270 nvl(sum( decode(c.tax_type, 'EXCISE_SH_EDU_CESS', c.debit, 0)), 0) dr_sh_exc_edu_cess,
2271 nvl(sum( decode(c.tax_type, 'CVD_SH_EDU_CESS', c.debit, 0)), 0) dr_sh_cvd_edu_cess,
2272 -- end
2273 max(a.transaction_date) transaction_date
2274 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. from JAI_CMN_RG_PLA_TRXS a, rcv_transactions b, JAI_CMN_RG_OTHERS c
2275 from JAI_CMN_RG_PLA_TRXS a, JAI_RCV_TRANSACTIONS b, JAI_CMN_RG_OTHERS c
2276 where a.ref_document_id = b.transaction_id
2277 AND a.organization_id = p_organization_id
2278 AND c.source_type(+) = 2 -- this means register is JAI_CMN_RG_PLA_TRXS
2279 AND a.register_id = c.source_register_id(+)
2280 AND (
2281 (p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NULL)
2282 OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NULL
2283 AND a.dr_invoice_date >= p_ex_invoice_from_date)
2284 OR (p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NOT NULL
2285 AND a.dr_invoice_date <= p_ex_invoice_to_date)
2286 OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NOT NULL
2287 AND a.dr_invoice_date BETWEEN p_ex_invoice_from_date AND p_ex_invoice_to_date)
2288 )
2289 AND a.rounding_id IS NULL
2290 AND a.TRANSACTION_SOURCE_NUM = 19
2291 AND ( (b.transaction_type = p_transaction_type)
2292 or (b.transaction_type = 'CORRECT' and b.parent_transaction_type = p_transaction_type
2293 /*Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. exists (select 1 from JAI_RCV_TRANSACTIONS
2294 where transaction_id = b.parent_transaction_id
2295 and transaction_type = p_transaction_type)*/
2296 )
2297 )
2298 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. GROUP BY b.shipment_header_id, a.DR_INVOICE_NO, a.dr_invoice_date
2299 GROUP BY a.DR_INVOICE_NO, a.dr_invoice_date,
2300 a.vendor_id , /*bgowrava for forward porting bug#5674376*/
2301 a.vendor_site_id /*bgowrava for forward porting bug#5674376*/
2302 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. ORDER BY shipment_header_id
2303 ORDER BY rcv_transaction_id
2304 )
2305 LOOP
2306
2307 BEGIN
2308
2309 if gb_debug then
2310 fnd_file.put_line(fnd_file.log, '0-Amts: crBas:'||r.cr_basic_ed
2311 ||', crAdd:'||r.cr_additional_ed||', crOth:'||r.cr_other_ed
2312 ||', crExcEdu:'||r.cr_exc_edu_cess||', crCvdEdu:'||r.cr_cvd_edu_cess
2313 ||', drBas:'||r.dr_basic_ed
2314 ||', drAdd:'||r.dr_additional_ed||', drOth:'||r.dr_other_ed
2315 ||', drExcEdu:'||r.dr_exc_edu_cess||', drCvdEdu:'||r.dr_cvd_edu_cess
2316 );
2317 end if;
2318
2319 v_statement_no := '1';
2320 v_excise_invoice_no := r.excise_invoice_no;
2321 v_excise_invoice_date := r.excise_invoice_date;
2322
2323 v_excise_inv_rnd_cnt := 0;
2324
2325 v_already_rounded_chk := null;
2326 v_period_balance_id := null;
2327 v_no_of_periods_updated := null;
2328
2329 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
2330 ln_rtv_excise_batch_group_id := null;
2331 open c_excise_batch_group_id(r.rcv_transaction_id);
2332 fetch c_excise_batch_group_id into ln_rtv_excise_batch_group_id;
2333 close c_excise_batch_group_id;
2334
2335 v_statement_no := '1.1';
2336 v_already_rounded_chk := NULL;
2337 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. OPEN c_already_rounded_chk(v_shipment_header_id, v_excise_invoice_no, v_excise_invoice_date, r.transaction_type);
2338 OPEN c_already_rounded_chk(ln_rtv_excise_batch_group_id, v_excise_invoice_no,
2339 v_excise_invoice_date, r.transaction_type);
2340 FETCH c_already_rounded_chk INTO
2341 v_already_rounded_chk,
2342 v_excise_inv_rnd_cnt;
2343 CLOSE c_already_rounded_chk;
2344
2345 IF v_already_rounded_chk IS NOT NULL THEN
2346
2347 v_statement_no := '1.3';
2348 SAVEPOINT previous_savepoint;
2349 v_save_point_set := TRUE;
2350
2351 if gb_debug then
2352 fnd_file.put_line(fnd_file.log, '1-Before Update of Register');
2353 end if;
2354
2355 if r.register = LV_RG23_REGISTER then
2356 v_statement_no := '1.2';
2357 UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
2358 SET jcrg23ac.rounding_id = v_already_rounded_chk
2359 WHERE jcrg23ac.excise_invoice_no = v_excise_invoice_no
2360 AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
2361 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
2362 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
2363 AND jcrg23ac.organization_id = p_organization_id
2364 AND jcrg23ac.rounding_id IS NULL
2365 AND jcrg23ac.transaction_source_num = 18
2366 AND exists (select 1 from JAI_RCV_TRANSACTIONS jrt
2367 /* Bug 4930048. Added by Lakshmi Gopalsami
2368 Changed transaction_source_num to transaction_id
2369 */
2370 where jrt.transaction_id = jcrg23ac.receipt_ref
2371 and (jrt.transaction_type = p_transaction_type or
2372 jrt.parent_transaction_type = p_transaction_type) );
2373
2374 elsif r.register = LV_PLA_REGISTER then
2375
2376 v_statement_no := '1.2';
2377 UPDATE JAI_CMN_RG_PLA_TRXS jcpla
2378 SET jcpla.rounding_id = v_already_rounded_chk
2379 WHERE jcpla.DR_INVOICE_NO = v_excise_invoice_no
2380 AND jcpla.dr_invoice_date = v_excise_invoice_date
2381 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
2382 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
2383 AND jcpla.organization_id = p_organization_id
2384 AND jcpla.rounding_id IS NULL
2385 AND jcpla.TRANSACTION_SOURCE_NUM = 19
2386 AND exists (select 1 from JAI_RCV_TRANSACTIONS jrt
2387 /* Bug 4930048. Added by Lakshmi Gopalsami
2388 Changed transaction_source_num to transaction_id
2389 */
2390 where jrt.transaction_id = jcpla.ref_document_id
2391 and (jrt.transaction_type = p_transaction_type or
2392 jrt.parent_transaction_type = p_transaction_type) );
2393 end if;
2394
2395 fnd_file.put_line(fnd_file.log, '+++++ Ex. Invoice Already rounded +++++');
2396 GOTO next_exc_inv;
2397
2398 END IF;
2399
2400 v_tot_processed_invoices := v_tot_processed_invoices + 1;
2401
2402 v_statement_no := '1.4';
2403 SELECT JAI_CMN_RG_ROUND_HDRS_S.nextval INTO v_rounding_id FROM dual;
2404
2405 v_line_type_c_cnt := 0;
2406 v_tot_lines_cnt := 0;
2407 v_tot_amount := v_zero_record;
2408
2409 v_statement_no := '1.4';
2410 FOR line IN ( SELECT a.shipment_line_id, a.transaction_id, a.item_class,
2411 a.transaction_type, a.parent_transaction_type, a.quantity
2412 FROM JAI_RCV_TRANSACTIONS a,
2413 JAI_RCV_CENVAT_CLAIMS b,/*bgowrava for forward porting bug#5674376*/
2414 RCV_TRANSACTIONS c,/*bgowrava for forward porting bug#5674376*/
2415 jai_rcv_rtv_batch_trxs d
2416 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE a.shipment_header_id = v_shipment_header_id
2417 WHERE a.transaction_id = d.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2418 and a.transaction_id = c.transaction_id --Added by nprashar for bug # 13367420
2419 and b.shipment_line_id = a.shipment_line_id --Added by nprashar for bug # 13367420
2420 and d.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2421 AND (a.transaction_type = p_transaction_type
2422 or (a.transaction_type = 'CORRECT' and a.parent_transaction_type = p_transaction_type))
2423 AND (
2424 (nvl(b.vendor_id,-999) = nvl(r.vendor_id,-999)
2425 AND nvl(b.vendor_site_id,-999) = nvl(r.vendor_site_id,-999)
2426 AND b.vendor_changed_flag = 'Y' )
2427 OR
2428 (nvl(c.vendor_id,-999) = nvl(r.vendor_id,-999)
2429 AND nvl(c.vendor_site_id,-999) = nvl(r.vendor_site_id,-999)
2430 AND b.vendor_changed_flag = 'N' )
2431 ) /*bgowrava for forward porting bug#5674376*/
2432 AND a.excise_invoice_no = v_excise_invoice_no
2433 AND a.excise_invoice_date = v_excise_invoice_date
2434 )
2435 LOOP
2436
2437 ln_receive_amount := v_zero_record;
2438
2439 ln_receive_qty := 0;
2440
2441 OPEN c_ja_in_receive_qty(line.shipment_line_id);
2442 FETCH c_ja_in_receive_qty INTO ln_receive_qty;
2443 CLOSE c_ja_in_receive_qty;
2444
2445 OPEN c_receipt_tax_amount(line.shipment_line_id);
2446 FETCH c_receipt_tax_amount INTO
2447 ln_receive_amount.basic,
2448 ln_receive_amount.additional,
2449 ln_receive_amount.additional_cvd,/*5228046 Addtional cvd Enhancement*/
2450 ln_receive_amount.other,
2451 ln_receive_amount.excise_edu_cess,
2452 ln_receive_amount.cvd_edu_cess,
2453 ln_receive_amount.sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
2454 ln_receive_amount.sh_cvd_edu_cess ;
2455 CLOSE c_receipt_tax_amount;
2456
2457 fnd_file.put_line(fnd_file.log, 'TrxId:'||line.transaction_id ||', RecvQty->'||ln_receive_qty);
2458
2459 ln_cenvat_amount.basic := ln_receive_amount.basic * line.quantity/ln_receive_qty;
2460 ln_cenvat_amount.additional := ln_receive_amount.additional * line.quantity/ln_receive_qty;
2461 ln_cenvat_amount.additional_cvd := nvl(ln_receive_amount.additional_cvd,0) * line.quantity/ln_receive_qty;/*5228046 Addtional cvd Enhancement*/
2462 ln_cenvat_amount.other := ln_receive_amount.other * line.quantity/ln_receive_qty;
2463 ln_cenvat_amount.excise_edu_cess := ln_receive_amount.excise_edu_cess * line.quantity/ln_receive_qty;
2464 ln_cenvat_amount.cvd_edu_cess := ln_receive_amount.cvd_edu_cess * line.quantity/ln_receive_qty;
2465 -- Date 16/04/2007 by sacsethi for Bug#5989740
2466 ln_cenvat_amount.sh_excise_edu_cess := nvl(ln_receive_amount.sh_excise_edu_cess,0) * line.quantity/ln_receive_qty;
2467 ln_cenvat_amount.sh_cvd_edu_cess := nvl(ln_receive_amount.sh_cvd_edu_cess,0) * line.quantity/ln_receive_qty;
2468 -- end 5989740
2469
2470 ln_cenvat_amount.total :=
2471 NVL(ln_cenvat_amount.basic,0)
2472 + NVL(ln_cenvat_amount.additional,0)
2473 + NVL(ln_cenvat_amount.additional_cvd,0)/*5228046 Addtional cvd Enhancement*/
2474 + NVL(ln_cenvat_amount.other,0)
2475 + NVL(ln_cenvat_amount.excise_edu_cess,0)
2476 + NVL(ln_cenvat_amount.cvd_edu_cess,0)
2477 + NVL(ln_cenvat_amount.sh_excise_edu_cess,0)-- Date 16/04/2007 by sacsethi for Bug#5989740
2478 + NVL(ln_cenvat_amount.sh_cvd_edu_cess,0) ;
2479
2480 v_statement_no := '1.5';
2481
2482 if gb_debug then
2483 fnd_file.put_line(fnd_file.log, '2-Before Insert into Entry Lines');
2484 end if;
2485
2486 -- populate item class
2487 INSERT INTO JAI_CMN_RG_ROUND_LINES (ROUNDING_LINE_ID,
2488 ROUNDING_ID, SRC_LINE_ID, SRC_TRANSACTION_ID,
2489 EXCISE_AMT,
2490 BASIC_ED, ADDITIONAL_ED,ADDITIONAL_CVD , OTHER_ED,
2491 EXCISE_EDU_CESS, CVD_EDU_CESS,
2492 sh_excise_edu_cess, sh_cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
2493 ITEM_CLASS, CREATION_DATE, CREATED_BY,
2494 program_application_id, program_id, program_login_id, request_id
2495 ) VALUES ( JAI_CMN_RG_ROUND_LINES_S.nextval,
2496 v_rounding_id, line.shipment_line_id, line.transaction_id,
2497 ln_cenvat_amount.total,
2498 ln_cenvat_amount.basic,
2499 ln_cenvat_amount.additional,
2500 ln_cenvat_amount.additional_cvd,/*5228046 Addtional cvd Enhancement*/
2501 ln_cenvat_amount.other,
2502 ln_cenvat_amount.excise_edu_cess, ln_cenvat_amount.cvd_edu_cess,
2503 ln_cenvat_amount.sh_excise_edu_cess, ln_cenvat_amount.sh_cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
2504 line.item_class, SYSDATE, v_created_by,
2505 fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'), fnd_profile.value('CONC_REQUEST_ID')
2506 );
2507
2508 v_tot_amount.basic := v_tot_amount.basic + ln_cenvat_amount.basic;
2509 v_tot_amount.additional := v_tot_amount.additional + ln_cenvat_amount.additional;
2510 v_tot_amount.additional_cvd := v_tot_amount.additional_cvd + nvl(ln_cenvat_amount.additional_cvd,0);/*Added by SACSETHI for the bug 5228046
2511 -Additional CVD Enhancement*/
2512
2513 v_tot_amount.other := v_tot_amount.other + ln_cenvat_amount.other;
2514 v_tot_amount.excise_edu_cess := v_tot_amount.excise_edu_cess + ln_cenvat_amount.excise_edu_cess;
2515 v_tot_amount.cvd_edu_cess := v_tot_amount.cvd_edu_cess + ln_cenvat_amount.cvd_edu_cess;
2516
2517 -- Date 16/04/2007 by sacsethi for Bug#5989740
2518 v_tot_amount.sh_excise_edu_cess := v_tot_amount.sh_excise_edu_cess + ln_cenvat_amount.sh_excise_edu_cess;
2519 v_tot_amount.sh_cvd_edu_cess := v_tot_amount.sh_cvd_edu_cess + ln_cenvat_amount.sh_cvd_edu_cess;
2520 -- end 5989740
2521
2522 v_tot_amount.total := NVL(v_tot_amount.basic,0) +
2523 NVL(v_tot_amount.additional,0) +
2524 NVL(v_tot_amount.additional_cvd,0) + /*5228046 Additional cvd Enhancement*/
2525 NVL(v_tot_amount.other,0) +
2526 NVL(v_tot_amount.excise_edu_cess,0) +
2527 NVL(v_tot_amount.cvd_edu_cess,0) +
2528 NVL(v_tot_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2529 NVL(v_tot_amount.sh_cvd_edu_cess,0);
2530
2531
2532 if line.transaction_type = p_transaction_type or line.parent_transaction_type = p_transaction_type then
2533 IF line.item_class IN ('CGIN', 'CGEX') THEN
2534 v_line_type_c_cnt := v_line_type_c_cnt + 1;
2535 END IF;
2536 v_tot_lines_cnt := v_tot_lines_cnt + 1;
2537 end if;
2538
2539 END LOOP;
2540
2541 v_statement_no := '1.6';
2542 IF v_line_type_c_cnt = v_tot_lines_cnt THEN
2543 v_register_type := 'C';
2544 ELSIF v_line_type_c_cnt = 0 THEN
2545 v_register_type := 'A';
2546 ELSE
2547 v_register_type := p_register_type;
2548 END IF;
2549
2550 v_tot_amount.basic := -r.cr_basic_ed + r.dr_basic_ed;
2551 v_tot_amount.additional := -r.cr_additional_ed + r.dr_additional_ed;
2552 v_tot_amount.additional_cvd := -r.cr_additional_cvd + r.dr_additional_cvd;/*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
2553 v_tot_amount.other := -r.cr_other_ed + r.dr_other_ed;
2554 v_tot_amount.excise_edu_cess := -r.cr_exc_edu_cess + r.dr_exc_edu_cess;
2555 v_tot_amount.cvd_edu_cess := -r.cr_cvd_edu_cess + r.dr_cvd_edu_cess;
2556
2557 -- Date 16/04/2007 by sacsethi for Bug#5989740
2558 -- start
2559 v_tot_amount.sh_excise_edu_cess := -r.cr_sh_exc_edu_cess + r.dr_sh_exc_edu_cess;
2560 v_tot_amount.sh_cvd_edu_cess := -r.cr_sh_cvd_edu_cess + r.dr_sh_cvd_edu_cess;
2561 -- end
2562
2563 v_tot_amount.total := NVL(v_tot_amount.basic,0) +
2564 NVL(v_tot_amount.additional,0) +
2565 NVL(v_tot_amount.additional_cvd,0) + /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
2566 NVL(v_tot_amount.other,0) +
2567 NVL(v_tot_amount.excise_edu_cess,0) +
2568 NVL(v_tot_amount.cvd_edu_cess,0)+
2569 NVL(v_tot_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2570 NVL(v_tot_amount.sh_cvd_edu_cess,0);
2571 if gb_debug then
2572 fnd_file.put_line(fnd_file.log, '2.1-Amts: tBas:'||v_tot_amount.basic
2573 ||', tAdd:'||v_tot_amount.additional||', tOth:'||v_tot_amount.other
2574 ||', tAdditional_cvd:'||v_tot_amount.additional_cvd /*5228046 Addtional cvd Enhancement*/
2575 ||', tExcEdu:'||v_tot_amount.excise_edu_cess||', tCvdEdu:'||v_tot_amount.cvd_edu_cess
2576 ||', tAmt:'||v_tot_amount.total
2577 );
2578 end if;
2579
2580 fnd_file.put_line(fnd_file.log, 'Started Processing rtvExcBtchGrpId->'||ln_rtv_excise_batch_group_id
2581 ||', excise_invoice_no->'||v_excise_invoice_no
2582 ||', excise_invoice_date->'||v_excise_invoice_date
2583 );
2584
2585 v_statement_no := '2';
2586
2587 v_tot_rounded_amount.basic := ROUND(v_tot_amount.basic, v_rounding_precision);
2588 v_rounded_amount.basic := v_tot_rounded_amount.basic - v_tot_amount.basic;
2589
2590 v_tot_rounded_amount.additional := ROUND(v_tot_amount.additional, v_rounding_precision);
2591 v_rounded_amount.additional := v_tot_rounded_amount.additional - v_tot_amount.additional;
2592
2593 /*5228046 Addtional cvd Enhancement*/
2594 v_tot_rounded_amount.additional_cvd := ROUND(v_tot_amount.additional_cvd, v_rounding_precision);
2595 v_rounded_amount.additional_cvd := v_tot_rounded_amount.additional_cvd - v_tot_amount.additional_cvd;
2596
2597
2598 v_tot_rounded_amount.other := ROUND(v_tot_amount.other, v_rounding_precision);
2599 v_rounded_amount.other := v_tot_rounded_amount.other - v_tot_amount.other;
2600
2601 v_tot_rounded_amount.excise_edu_cess := ROUND(v_tot_amount.excise_edu_cess, v_rounding_precision);
2602 v_rounded_amount.excise_edu_cess := v_tot_rounded_amount.excise_edu_cess - v_tot_amount.excise_edu_cess;
2603
2604 v_tot_rounded_amount.cvd_edu_cess := ROUND(v_tot_amount.cvd_edu_cess, v_rounding_precision);
2605 v_rounded_amount.cvd_edu_cess := v_tot_rounded_amount.cvd_edu_cess - v_tot_amount.cvd_edu_cess;
2606
2607 -- Date 16/04/2007 by sacsethi for Bug#5989740
2608 -- start 5989740
2609
2610 v_tot_rounded_amount.sh_excise_edu_cess := ROUND(v_tot_amount.sh_excise_edu_cess, v_rounding_precision);
2611 v_rounded_amount.sh_excise_edu_cess := v_tot_rounded_amount.sh_excise_edu_cess - v_tot_amount.sh_excise_edu_cess;
2612
2613 v_tot_rounded_amount.sh_cvd_edu_cess := ROUND(v_tot_amount.sh_cvd_edu_cess, v_rounding_precision);
2614 v_rounded_amount.sh_cvd_edu_cess := v_tot_rounded_amount.sh_cvd_edu_cess - v_tot_amount.sh_cvd_edu_cess;
2615
2616 -- end 5989740
2617
2618 v_tot_rounded_amount.total :=
2619 NVL(v_tot_rounded_amount.basic,0)
2620 + NVL(v_tot_rounded_amount.additional,0)
2621 + NVL(v_tot_rounded_amount.additional_cvd,0) /*5228046 Additional cvd Enhancement*/
2622 + NVL(v_tot_rounded_amount.other,0)
2623 + NVL(v_tot_rounded_amount.excise_edu_cess,0)
2624 + NVL(v_tot_rounded_amount.cvd_edu_cess,0)
2625 + NVL(v_tot_rounded_amount.sh_excise_edu_cess,0) -- Date 16/04/2007 by sacsethi for Bug#5989740
2626 + NVL(v_tot_rounded_amount.sh_cvd_edu_cess,0);
2627
2628 v_rounded_amount.total :=
2629 NVL(v_rounded_amount.basic,0)
2630 + NVL(v_rounded_amount.additional,0)
2631 + NVL(v_rounded_amount.additional_cvd,0) /*5228046 Additional cvd Enhancement*/
2632 + NVL(v_rounded_amount.other,0)
2633 + NVL(v_rounded_amount.excise_edu_cess,0)
2634 + NVL(v_rounded_amount.cvd_edu_cess,0)
2635 + NVL(v_rounded_amount.sh_excise_edu_cess,0) -- Date 16/04/2007 by sacsethi for Bug#5989740
2636 + NVL(v_rounded_amount.sh_cvd_edu_cess,0);
2637
2638 v_rounded_amount_rg23 :=
2639 NVL(v_rounded_amount.basic,0)
2640 + NVL(v_rounded_amount.additional,0)
2641 + NVL(v_rounded_amount.additional_cvd,0)/*5228046 Additional cvd Enhancement*/
2642 + NVL(v_rounded_amount.other,0);
2643
2644 if gb_debug then
2645 fnd_file.put_line(fnd_file.log, '2.2-Amts: rBas:'||v_rounded_amount.basic
2646 ||', rAdd:'||v_rounded_amount.additional||', rOth:'||v_rounded_amount.other
2647 ||', rAdd_cvd:'||v_rounded_amount.additional_cvd /*5228046 Additional cvd Enhancement*/
2648 ||', rExcEdu:'||v_rounded_amount.excise_edu_cess||', rCvdEdu:'||v_rounded_amount.cvd_edu_cess
2649 );
2650 end if;
2651
2652 v_rounded_amount_abs :=
2653 ABS(NVL(v_rounded_amount.basic,0)) +
2654 ABS(NVL(v_rounded_amount.additional,0)) +
2655 ABS(NVL(v_rounded_amount.additional_cvd,0)) + /*5228046 Additional cvd Enhancement*/
2656 ABS(NVL(v_rounded_amount.other,0)) +
2657 ABS(NVL(v_rounded_amount.excise_edu_cess,0)) +
2658 ABS(NVL(v_rounded_amount.cvd_edu_cess,0)) +
2659 ABS(NVL(v_rounded_amount.sh_excise_edu_cess,0)) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2660 ABS(NVL(v_rounded_amount.sh_cvd_edu_cess,0));
2661
2662 v_statement_no := '3';
2663
2664 /* Punching Rounding_Id as 0 for RG transactions where in no Rounding is Required */
2665 IF v_rounded_amount_abs = 0 THEN
2666 v_zero_round_found := v_zero_round_found + 1;
2667
2668 if gb_debug then
2669 fnd_file.put_line(fnd_file.log, '3-Before Update of Register with 0 Rounding');
2670 end if;
2671
2672 if r.register = LV_RG23_REGISTER then
2673 UPDATE JAI_CMN_RG_23AC_II_TRXS aa
2674 SET rounding_id = 0
2675 WHERE organization_id = p_organization_id
2676 AND excise_invoice_no = v_excise_invoice_no
2677 AND excise_invoice_date = v_excise_invoice_date
2678 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
2679 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
2680 AND rounding_id IS NULL
2681 AND TRANSACTION_SOURCE_NUM = 18
2682 AND EXISTS (
2683 SELECT 1 FROM JAI_RCV_TRANSACTIONS bb, jai_rcv_rtv_batch_trxs b1
2684 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE BB.shipment_header_id = v_shipment_header_id
2685 WHERE bb.transaction_id = b1.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2686 and b1.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2687 AND bb.transaction_id = aa.receipt_ref
2688 AND ( bb.transaction_type = r.transaction_type or bb.parent_transaction_type = r.transaction_type)
2689 );
2690
2691 elsif r.register = LV_PLA_REGISTER then
2692
2693 UPDATE JAI_CMN_RG_PLA_TRXS aa
2694 SET rounding_id = 0
2695 WHERE organization_id = p_organization_id
2696 AND DR_INVOICE_NO = v_excise_invoice_no
2697 AND dr_invoice_date = v_excise_invoice_date
2698 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
2699 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
2700 AND rounding_id IS NULL
2701 AND TRANSACTION_SOURCE_NUM = 19
2702 AND EXISTS (
2703 SELECT 1 FROM JAI_RCV_TRANSACTIONS bb, jai_rcv_rtv_batch_trxs b1
2704 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE BB.shipment_header_id = v_shipment_header_id
2705 WHERE bb.transaction_id = b1.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2706 and b1.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2707 AND bb.transaction_id = aa.ref_document_id
2708 AND ( bb.transaction_type = r.transaction_type or bb.parent_transaction_type = r.transaction_type)
2709 );
2710
2711 end if;
2712
2713 DELETE FROM JAI_CMN_RG_ROUND_LINES WHERE rounding_id = v_rounding_id;
2714
2715 fnd_file.put_line(fnd_file.log, '**** Zero Rounded ****');
2716 GOTO next_exc_inv;
2717
2718 elsif v_rounded_amount.total < 0 then
2719 v_rounding_entry_type := 'CR';
2720 v_rounded_cr_amount := ABS(NVL(v_rounded_amount.basic,0) +
2721 NVL(v_rounded_amount.additional,0) +
2722 NVL(v_rounded_amount.additional_cvd,0) + /*5228046 Additional cvd Enhancement*/
2723 NVL(v_rounded_amount.other,0) +
2724 NVL(v_rounded_amount.excise_edu_cess,0) +
2725 NVL(v_rounded_amount.cvd_edu_cess,0) +
2726 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2727 NVL(v_rounded_amount.sh_cvd_edu_cess,0));
2728
2729 ELSIF v_rounded_amount.total > 0 THEN
2730 v_rounding_entry_type := 'DR';
2731 v_rounded_dr_amount := ABS(NVL(v_rounded_amount.basic,0) +
2732 NVL(v_rounded_amount.additional,0) +
2733 NVL(v_rounded_amount.additional_cvd,0) +/*5228046 Additional cvd Enhancement*/
2734 NVL(v_rounded_amount.other,0) +
2735 NVL(v_rounded_amount.excise_edu_cess,0) +
2736 NVL(v_rounded_amount.cvd_edu_cess,0) +
2737 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2738 NVL(v_rounded_amount.sh_cvd_edu_cess,0));
2739 END IF;
2740
2741 IF NVL(v_rounded_amount.basic,0) > 0 THEN
2742 v_rounded_dr_rg23_amount.basic := ABS(v_rounded_amount.basic);
2743 ELSE
2744 v_rounded_cr_rg23_amount.basic := ABS(v_rounded_amount.basic);
2745 END IF;
2746
2747 IF NVL(v_rounded_amount.additional,0) > 0 THEN
2748 v_rounded_dr_rg23_amount.additional := ABS(v_rounded_amount.additional);
2749 ELSE
2750 v_rounded_cr_rg23_amount.additional := ABS(v_rounded_amount.additional);
2751 END IF;
2752
2753 /*5228046 Additional cvd Enhancement*/
2754 IF NVL(v_rounded_amount.additional_cvd,0) > 0 THEN
2755 v_rounded_dr_rg23_amount.additional_cvd := ABS(v_rounded_amount.additional_cvd);
2756 ELSE
2757 v_rounded_cr_rg23_amount.additional_cvd := ABS(v_rounded_amount.additional_cvd);
2758 END IF;
2759
2760
2761 IF NVL(v_rounded_amount.other,0) > 0 THEN
2762 v_rounded_dr_rg23_amount.other := ABS(v_rounded_amount.other);
2763 ELSE
2764 v_rounded_cr_rg23_amount.other := ABS(v_rounded_amount.other);
2765 END IF;
2766
2767 IF (NVL(v_rounded_amount.excise_edu_cess,0) +
2768 NVL(v_rounded_amount.cvd_edu_cess,0)+
2769 NVL(v_rounded_amount.sh_excise_edu_cess,0) +
2770 NVL(v_rounded_amount.sh_cvd_edu_cess,0)) > 0
2771 THEN
2772 v_rounded_dr_oth_amount.total := ABS(NVL(v_rounded_amount.excise_edu_cess,0) +
2773 NVL(v_rounded_amount.cvd_edu_cess,0) +
2774 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2775 NVL(v_rounded_amount.sh_cvd_edu_cess,0)
2776 );
2777
2778 ELSE
2779 v_rounded_cr_oth_amount.total := ABS(NVL(v_rounded_amount.excise_edu_cess,0) +
2780 NVL(v_rounded_amount.cvd_edu_cess,0) +
2781 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2782 NVL(v_rounded_amount.sh_cvd_edu_cess,0)
2783 );
2784 END IF;
2785
2786 IF NVL(v_rounded_amount.excise_edu_cess,0) > 0 THEN
2787 v_rounded_dr_oth_amount.excise_edu_cess := ABS(v_rounded_amount.excise_edu_cess);
2788 ELSE
2789 v_rounded_cr_oth_amount.excise_edu_cess := ABS(v_rounded_amount.excise_edu_cess);
2790 END IF;
2791
2792 IF NVL(v_rounded_amount.cvd_edu_cess,0) > 0 THEN
2793 v_rounded_dr_oth_amount.cvd_edu_cess := ABS(v_rounded_amount.cvd_edu_cess);
2794 ELSE
2795 v_rounded_cr_oth_amount.cvd_edu_cess := ABS(v_rounded_amount.cvd_edu_cess);
2796 END IF;
2797
2798 -- Date 16/04/2007 by sacsethi for Bug#5989740
2799 -- start 5989740
2800 IF NVL(v_rounded_amount.sh_excise_edu_cess,0) > 0 THEN
2801 v_rounded_dr_oth_amount.sh_excise_edu_cess := ABS(v_rounded_amount.sh_excise_edu_cess);
2802 ELSE
2803 v_rounded_cr_oth_amount.sh_excise_edu_cess := ABS(v_rounded_amount.sh_excise_edu_cess);
2804 END IF;
2805
2806 IF NVL(v_rounded_amount.sh_cvd_edu_cess,0) > 0 THEN
2807 v_rounded_dr_oth_amount.sh_cvd_edu_cess := ABS(v_rounded_amount.sh_cvd_edu_cess);
2808 ELSE
2809 v_rounded_cr_oth_amount.sh_cvd_edu_cess := ABS(v_rounded_amount.sh_cvd_edu_cess);
2810 END IF;
2811 -- end 5989740
2812
2813 v_statement_no := '4';
2814
2815 OPEN c_rg_modvat_account(p_organization_id, r.location_id);
2816 FETCH c_rg_modvat_account INTO v_modvat_rm_account_id, v_modvat_cg_account_id, v_modvat_pla_account_id;
2817 CLOSE c_rg_modvat_account;
2818
2819 v_statement_no := '4.1';
2820 if r.register = LV_PLA_REGISTER then
2821 v_rg_account_id := v_modvat_pla_account_id;
2822
2823 elsif v_register_type = 'A' THEN
2824 v_rg_account_id := v_modvat_rm_account_id;
2825
2826 elsif v_register_type = 'C' THEN
2827 v_rg_account_id := v_modvat_cg_account_id;
2828 end if;
2829
2830 -- Required Accounts are not specified at the location where RG23 PART II entry will be posted. So, through out an error
2831 if v_rg_account_id IS NULL then
2832 if r.register = LV_PLA_REGISTER then
2833 v_err_message := 'ERROR: PLA Account is not specified for Location:'||r.location_id;
2834 elsif v_register_type = 'A' THEN
2835 v_err_message := 'ERROR: Cenvat RMIN Account is not specified for Location:'||r.location_id;
2836 else
2837 v_err_message := 'ERROR: Cenvat CGIN Account is not specified for Location:'||r.location_id;
2838 end if;
2839
2840 fnd_file.put_line(fnd_file.log, v_err_message);
2841 RAISE_APPLICATION_ERROR( -20099, v_err_message);
2842 end if;
2843
2844 v_statement_no := '5';
2845
2846 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. OPEN c_vendor(v_shipment_header_id);
2847 OPEN c_vendor(r.min_shipment_header_id);
2848 FETCH c_vendor INTO v_vendor_id, v_vendor_site_id, v_receipt_num;
2849 CLOSE c_vendor;
2850
2851 /*Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.*/
2852 if r.cnt_shipment_header_id > 1 then
2853 v_receipt_num := null;
2854 end if;
2855
2856 v_statement_no := '7';
2857
2858 if r.register = LV_RG23_REGISTER then
2859 OPEN c_slno_balance_rg23p2(p_organization_id, r.location_id, v_fin_year, v_register_type);
2860 FETCH c_slno_balance_rg23p2 INTO v_slno, v_balance;
2861 CLOSE c_slno_balance_rg23p2;
2862 v_rg23_balance := nvl(v_balance,0);
2863
2864 v_statement_no := '8';
2865
2866 IF v_slno IS NULL or v_slno = 0 THEN
2867 v_slno := 1;
2868 ELSE
2869 v_slno := v_slno + 1;
2870 END IF;
2871 end if;
2872
2873 v_statement_no := '8.1';
2874
2875 if gb_debug then
2876 fnd_file.put_line(fnd_file.log, '4-Before Insert of Rounding Entry into Register:'||r.register );
2877 end if;
2878
2879 if r.register = LV_PLA_REGISTER then
2880
2881 jai_cmn_rg_pla_trxs_pkg.insert_row(
2882 p_register_id => v_register_id_part_ii,
2883 p_tr6_challan_no => NULL,
2884 p_tr6_challan_date => NULL,
2885 p_cr_basic_ed => v_rounded_cr_rg23_amount.basic,
2886 p_cr_additional_ed => v_rounded_cr_rg23_amount.additional,
2887 p_cr_other_ed => v_rounded_cr_rg23_amount.other,
2888 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. p_ref_document_id => r.shipment_header_id,
2889 p_ref_document_id => ln_rtv_excise_batch_group_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2890 p_ref_document_date => trunc(sysdate), -- CHK
2891 p_dr_invoice_id => r.excise_invoice_no,
2892 p_dr_invoice_date => r.excise_invoice_date,
2893 p_dr_basic_ed => v_rounded_dr_rg23_amount.basic,
2894 p_dr_additional_ed => v_rounded_dr_rg23_amount.additional,
2895 p_dr_other_ed => v_rounded_dr_rg23_amount.other,
2896 p_organization_id => p_organization_id,
2897 p_location_id => r.location_id,
2898 p_bank_branch_id => NULL,
2899 p_entry_date => NULL,
2900 p_inventory_item_id => 0,
2901 p_vendor_cust_flag => 'V',
2902 p_vendor_id => v_vendor_id,
2903 p_vendor_site_id => v_vendor_site_id,
2904 p_excise_invoice_no => r.excise_invoice_no,
2905 p_remarks => 'Rounding Entry for Receipt No:'||v_receipt_num,
2906 p_transaction_date => r.transaction_date,
2907 p_charge_account_id => NULL,
2908 p_other_tax_credit => v_rounded_cr_oth_amount.total,
2909 p_other_tax_debit => v_rounded_dr_oth_amount.total,
2910 p_transaction_type => 'RETURN TO VENDOR',
2911 p_transaction_source => null,
2912 p_called_from => 'rg_rounding_pkg.do_rtv_rounding',
2913 p_simulate_flag => 'N',
2914 p_process_status => lv_process_status,
2915 p_process_message => lv_process_message,
2916 p_rounding_id => -1
2917 );
2918
2919 if lv_process_status = 'E' then
2920 raise_application_error( -20010, lv_process_message, true);
2921 end if;
2922
2923 elsif r.register = LV_RG23_REGISTER then
2924 INSERT INTO JAI_CMN_RG_23AC_II_TRXS(
2925 REGISTER_ID, ORGANIZATION_ID, LOCATION_ID, FIN_YEAR, INVENTORY_ITEM_ID, SLNO,
2926 CR_BASIC_ED, DR_BASIC_ED,
2927 CR_ADDITIONAL_ED, DR_ADDITIONAL_ED,
2928 CR_ADDITIONAL_CVD, DR_ADDITIONAL_CVD,/*5228046 ADDITIONAL CVD ENHANCEMENT*/
2929 CR_OTHER_ED, DR_OTHER_ED,
2930 ROUNDING_ID, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
2931 TRANSACTION_SOURCE_NUM, RECEIPT_REF, REGISTER_TYPE, REMARKS,
2932 VENDOR_ID, VENDOR_SITE_ID, CUSTOMER_ID, CUSTOMER_SITE_ID,
2933 OPENING_BALANCE, CLOSING_BALANCE, CHARGE_ACCOUNT_ID,
2934 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
2935 , TRANSACTION_DATE, OTHER_TAX_CREDIT, OTHER_TAX_DEBIT
2936 ) VALUES (
2937 JAI_CMN_RG_23AC_II_TRXS_S.nextval, p_organization_id, r.location_id , v_fin_year, 0, v_slno,
2938 v_rounded_cr_rg23_amount.basic, v_rounded_dr_rg23_amount.basic,
2939 v_rounded_cr_rg23_amount.additional, v_rounded_dr_rg23_amount.additional,
2940 v_rounded_cr_rg23_amount.additional_cvd, v_rounded_dr_rg23_amount.additional_cvd,/*5228046 Additional cvd Enhancement*/
2941 v_rounded_cr_rg23_amount.other, v_rounded_dr_rg23_amount.other,
2942 -1, r.excise_invoice_no, r.excise_invoice_date,
2943 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. 18, r.shipment_header_id, v_register_type, 'Rounding Entry for Receipt No:'||v_receipt_num,
2944 18, ln_rtv_excise_batch_group_id, v_register_type, 'Rounding Entry for Receipt No:'||v_receipt_num,
2945 v_vendor_id, v_vendor_site_id, NULL, NULL,
2946 v_rg23_balance, v_rg23_balance + v_rounded_amount_rg23, v_rg_account_id,
2947 SYSDATE, v_created_by, SYSDATE, v_created_by, v_last_update_login
2948 , r.transaction_date, v_rounded_cr_oth_amount.total, v_rounded_dr_oth_amount.total
2949
2950 ) RETURNING register_id INTO v_register_id_part_ii;
2951 end if;
2952
2953 DECLARE
2954 v_tax_type JAI_CMN_RG_OTHERS.tax_type%TYPE;
2955 v_dr_amt JAI_CMN_RG_OTHERS.debit%TYPE;
2956 v_cr_amt JAI_CMN_RG_OTHERS.credit%TYPE;
2957 BEGIN
2958 FOR I in 1..4 LOOP -- Date 16/04/2007 by sacsethi for Bug#5989740 changed the loop counter from 2 to 4
2959
2960 if i = 1 then
2961 v_tax_type := jai_constants.tax_type_exc_edu_cess;
2962 v_dr_amt := v_rounded_dr_oth_amount.excise_edu_cess;
2963 v_cr_amt := v_rounded_cr_oth_amount.excise_edu_cess;
2964 elsif i = 2 then
2965 v_tax_type := jai_constants.tax_type_cvd_edu_cess;
2966 v_dr_amt := v_rounded_dr_oth_amount.cvd_edu_cess;
2967 v_cr_amt := v_rounded_cr_oth_amount.cvd_edu_cess;
2968 -- Date 16/04/2007 by sacsethi for Bug#5989740
2969 -- start 5989740
2970 elsif i = 3 then
2971 v_tax_type := jai_constants.tax_type_sh_exc_edu_cess;
2972 v_dr_amt := v_rounded_dr_oth_amount.sh_excise_edu_cess;
2973 v_cr_amt := v_rounded_cr_oth_amount.sh_excise_edu_cess;
2974 elsif i = 4 then
2975 v_tax_type := jai_constants.tax_type_sh_cvd_edu_cess;
2976 v_dr_amt := v_rounded_dr_oth_amount.sh_cvd_edu_cess;
2977 v_cr_amt := v_rounded_cr_oth_amount.sh_cvd_edu_cess;
2978 -- end 5989740
2979 end if;
2980
2981 if gb_debug then
2982 fnd_file.put_line(fnd_file.log, '5-Before Insert of RG Others.Amts:dr-'||v_dr_amt||',cr-'||v_cr_amt );
2983 end if;
2984
2985 IF NVL(v_dr_amt,0) <> 0 OR NVL(v_cr_amt,0) <> 0 THEN
2986 INSERT INTO JAI_CMN_RG_OTHERS (
2987 RG_OTHER_ID,
2988 SOURCE_TYPE,
2989 SOURCE_REGISTER,
2990 SOURCE_REGISTER_ID,
2991 TAX_TYPE,
2992 DEBIT,
2993 CREDIT,
2994 OPENING_BALANCE,
2995 CLOSING_BALANCE,
2996 CREATED_BY,
2997 CREATION_DATE,
2998 LAST_UPDATED_BY,
2999 LAST_UPDATE_DATE,
3000 LAST_UPDATE_LOGIN
3001 ) VALUES (
3002 JAI_CMN_RG_OTHERS_S.nextval,
3003 DECODE(r.register, LV_PLA_REGISTER, 2, 1),
3004 DECODE(r.register, LV_PLA_REGISTER, 'PLA', decode(v_register_type,'A','RG23A_P2','C','RG23C_P2')),/*for bug 5054176*/
3005 v_register_id_part_ii,
3006 v_tax_type,
3007 v_dr_amt,
3008 v_cr_amt,
3009 NULL,
3010 NULL,
3011 v_created_by,
3012 sysdate,
3013 v_created_by,
3014 sysdate,
3015 v_last_update_login
3016 );
3017 END IF;
3018 END LOOP;
3019 END;
3020
3021 v_statement_no := '10a';
3022 -- this call will update Rounding RG23 PartII entry with Period_balance_id and updates all records of JAI_CMN_RG_PERIOD_BALS
3023 -- that come after the period in which parent Excise invoice has hit RG
3024 if r.register = LV_RG23_REGISTER then
3025 jai_cmn_rg_period_bals_pkg.adjust_rounding(v_register_id_part_ii, v_period_balance_id, v_no_of_periods_updated);
3026 end if;
3027
3028 if gb_debug then
3029 fnd_file.put_line(fnd_file.log, '6-Before Insert into Rounding Entries. Id:'||v_rounding_id);
3030 end if;
3031
3032 v_statement_no := '11';
3033 INSERT INTO JAI_CMN_RG_ROUND_HDRS(
3034 ROUNDING_ID, SOURCE_HEADER_ID, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
3035 EXCISE_AMT_BEFORE_ROUNDING, EXCISE_AMT_AFTER_ROUNDING,
3036 BASIC_ED, ROUNDED_BASIC_ED,
3037 ADDITIONAL_ED, ROUNDED_ADDITIONAL_ED,
3038 ADDITIONAL_CVD, ROUNDED_ADDITIONAL_CVD,/*5228046 ADDITIONAL CVD ENHANCEMENT*/
3039 OTHER_ED, ROUNDED_OTHER_ED,
3040 EXCISE_EDU_CESS, ROUNDED_EXCISE_EDU_CESS,
3041 CVD_EDU_CESS, ROUNDED_CVD_EDU_CESS,
3042 sh_excise_edu_cess, rounded_sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
3043 sh_cvd_edu_cess, rounded_sh_cvd_edu_cess, -- SH Cess column is added
3044 register, REGISTER_ID, SOURCE, SRC_TRANSACTION_TYPE,
3045 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
3046 program_application_id, program_id, program_login_id, request_id
3047 ) VALUES (
3048 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. v_rounding_id, v_shipment_header_id, v_excise_invoice_no, v_excise_invoice_date,
3049 v_rounding_id, ln_rtv_excise_batch_group_id, v_excise_invoice_no, v_excise_invoice_date,
3050 v_tot_amount.total, v_tot_rounded_amount.total,
3051 v_tot_amount.basic, v_tot_rounded_amount.basic,
3052 v_tot_amount.additional, v_tot_rounded_amount.additional,
3053 V_TOT_AMOUNT.ADDITIONAL_CVD, V_TOT_ROUNDED_AMOUNT.ADDITIONAL_CVD, /*5228046 ADDITIONAL CVD ENHANCEMENT*/
3054 v_tot_amount.other, v_tot_rounded_amount.other,
3055 v_tot_amount.excise_edu_cess, v_tot_rounded_amount.excise_edu_cess,
3056 v_tot_amount.cvd_edu_cess, v_tot_rounded_amount.cvd_edu_cess,
3057 v_tot_amount.sh_excise_edu_cess, v_tot_rounded_amount.sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
3058 v_tot_amount.sh_cvd_edu_cess, v_tot_rounded_amount.sh_cvd_edu_cess,
3059 r.register, v_register_id_part_ii, 'PO', p_transaction_type,
3060 SYSDATE, v_created_by, SYSDATE, v_created_by, v_last_update_login,
3061 fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'), fnd_profile.value('CONC_REQUEST_ID')
3062 );
3063
3064 v_statement_no := '12';
3065
3066 if gb_debug then
3067 fnd_file.put_line(fnd_file.log, '7-Pass RG Accnting: Cr-'||v_rounded_cr_amount||', Dr-'||v_rounded_dr_amount);
3068 end if;
3069
3070 -- 2 (CR, DR) GL Interface related calls has to be coded
3071 -- Modvat Account entries
3072 jai_rcv_rnd_pkg.pass_accounting(
3073 p_organization_id => p_organization_id,
3074 p_transaction_id => r.rcv_transaction_id,
3075 p_transaction_date => v_today,
3076 p_shipment_line_id => -1,
3077 p_acct_type => v_acct_type,
3078 p_acct_nature => v_acct_nature,
3079 p_source => v_source_name,
3080 p_category => v_category_name,
3081 p_code_combination_id => v_rg_account_id,
3082 p_entered_dr => v_rounded_cr_amount,
3083 p_entered_cr => v_rounded_dr_amount,
3084 p_created_by => v_created_by,
3085 p_currency_code => 'INR',
3086 p_currency_conversion_type => NULL,
3087 p_currency_conversion_date => NULL,
3088 p_currency_conversion_rate => NULL,
3089 p_receipt_num => v_receipt_num
3090 );
3091
3092 v_statement_no := '13';
3093
3094 jai_cmn_gl_pkg.create_gl_entry(
3095 p_organization_id => p_organization_id,
3096 p_currency_code => 'INR',
3097 p_credit_amount => v_rounded_dr_amount,
3098 p_debit_amount => v_rounded_cr_amount,
3099 p_cc_id => v_rg_account_id,
3100 p_je_source_name => v_source_name,
3101 p_je_category_name => v_category_name,
3102 p_created_by => v_created_by,
3103 p_accounting_date => v_today,
3104 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. p_reference_10 => 'India Local Rounding Entry for Shipment_Header_id:'||r.shipment_header_id
3105 p_reference_10 => 'India Local Rounding Entry for RTV batch_group_id:'||ln_rtv_excise_batch_group_id
3106 ||', excise_invoice_no:'||r.excise_invoice_no||', exc_inv_date:'||r.excise_invoice_date,
3107 p_reference_23 => 'ja_in_rg_rounding_p',
3108 p_reference_24 => 'JAI_CMN_RG_ROUND_HDRS',
3109 p_reference_25 => 'ROUNDING_ID',
3110 p_reference_26 => v_rounding_id
3111 );
3112
3113 v_statement_no := '14';
3114
3115 -- Rounding Account entries
3116 jai_rcv_rnd_pkg.pass_accounting(
3117 p_organization_id => p_organization_id,
3118 p_transaction_id => r.rcv_transaction_id,
3119 p_transaction_date => v_today,
3120 p_shipment_line_id => -1,
3121 p_acct_type => v_acct_type,
3122 p_acct_nature => v_acct_nature,
3123 p_source => v_source_name,
3124 p_category => v_category_name,
3125 p_code_combination_id => v_rg_rounding_account_id,
3126 p_entered_dr => v_rounded_dr_amount,
3127 p_entered_cr => v_rounded_cr_amount,
3128 p_created_by => v_created_by,
3129 p_currency_code => 'INR',
3130 p_currency_conversion_type => NULL,
3131 p_currency_conversion_date => NULL,
3132 p_currency_conversion_rate => NULL,
3133 p_receipt_num => v_receipt_num
3134 );
3135
3136 v_statement_no := '15';
3137
3138 jai_cmn_gl_pkg.create_gl_entry(
3139 p_organization_id => p_organization_id,
3140 p_currency_code => 'INR',
3141 p_credit_amount => v_rounded_cr_amount,
3142 p_debit_amount => v_rounded_dr_amount,
3143 p_cc_id => v_rg_rounding_account_id,
3144 p_je_source_name => v_source_name,
3145 p_je_category_name => v_category_name,
3146 p_created_by => v_created_by,
3147 p_accounting_date => v_today,
3148 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. p_reference_10 => 'India Local Rounding Entry for Shipment_Header_id:'||r.shipment_header_id
3149 p_reference_10 => 'India Local Rounding Entry for RTV batch_group_id:'||ln_rtv_excise_batch_group_id
3150 ||', excise_invoice_no:'||r.excise_invoice_no||', exc_inv_date:'||r.excise_invoice_date,
3151 p_reference_23 => 'ja_in_rg_rounding_p',
3152 p_reference_24 => 'JAI_CMN_RG_ROUND_HDRS',
3153 p_reference_25 => 'ROUNDING_ID',
3154 p_reference_26 => v_rounding_id
3155 );
3156
3157 v_statement_no := '16';
3158
3159 if v_register_type = 'A' then
3160 UPDATE JAI_CMN_RG_BALANCES
3161 SET rg23a_balance = nvl(rg23a_balance, 0) + v_rounded_amount_rg23
3162 WHERE organization_id = p_organization_id
3163 AND location_id = r.location_id;
3164
3165 elsif v_register_type = 'C' then
3166 UPDATE JAI_CMN_RG_BALANCES
3167 SET rg23c_balance = nvl(rg23c_balance, 0) + v_rounded_amount_rg23
3168 WHERE organization_id = p_organization_id
3169 AND location_id = r.location_id;
3170
3171 elsif r.register = LV_PLA_REGISTER then
3172 /* this update is already taken in jai_cmn_rg_pla_trxs_pkg.insert_row call*/
3173 NULL;
3174
3175 end if;
3176
3177 v_statement_no := '16.1';
3178
3179 fnd_file.put_line(fnd_file.log,' 101 exc_rnd_cnt->'||v_excise_inv_rnd_cnt);
3180
3181 if gb_debug then
3182 fnd_file.put_line(fnd_file.log, '8-Punching RoundingId in Register');
3183 end if;
3184
3185 if r.register = LV_RG23_REGISTER then
3186 lv_ttype_correct := 'CORRECT'; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3187 UPDATE JAI_CMN_RG_23AC_II_TRXS aa
3188 SET rounding_id = v_rounding_id
3189 WHERE organization_id = p_organization_id
3190 AND excise_invoice_no = v_excise_invoice_no
3191 AND excise_invoice_date = v_excise_invoice_date
3192 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
3193 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
3194 AND rounding_id IS NULL
3195 AND TRANSACTION_SOURCE_NUM = 18
3196 AND EXISTS (
3197 SELECT BB.transaction_id
3198 FROM JAI_RCV_TRANSACTIONS bb, jai_rcv_rtv_batch_trxs b1
3199 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE bb.shipment_header_id = v_shipment_header_id
3200 WHERE bb.transaction_id = b1.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
3201 and b1.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
3202 AND bb.transaction_id = aa.receipt_ref
3203 AND ( bb.transaction_type = r.transaction_type
3204 OR (bb.transaction_type = lv_ttype_correct and bb.parent_transaction_type = r.transaction_type) --'CORRECT'
3205 )
3206 );
3207
3208 elsif r.register = LV_PLA_REGISTER then
3209 lv_ttype_correct := 'CORRECT'; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3210 UPDATE JAI_CMN_RG_PLA_TRXS aa
3211 SET rounding_id = v_rounding_id
3212 WHERE organization_id = p_organization_id
3213 AND DR_INVOICE_NO = v_excise_invoice_no
3214 AND dr_invoice_date = v_excise_invoice_date
3215 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
3216 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
3217 AND rounding_id IS NULL
3218 AND TRANSACTION_SOURCE_NUM = 19
3219 AND exists (
3220 SELECT BB.transaction_id
3221 FROM JAI_RCV_TRANSACTIONS bb, jai_rcv_rtv_batch_trxs b1
3222 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE bb.shipment_header_id = v_shipment_header_id
3223 WHERE bb.transaction_id = b1.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
3224 and b1.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
3225 AND bb.transaction_id = aa.ref_document_id
3226 AND ( bb.transaction_type = r.transaction_type
3227 OR (bb.transaction_type = lv_ttype_correct and bb.parent_transaction_type = r.transaction_type)
3228 )
3229 );
3230
3231 end if;
3232
3233 v_rounding_entries_made := v_rounding_entries_made + 1;
3234
3235 v_statement_no := '17';
3236 IF v_no_of_invoices_posted >= v_commit_interval THEN
3237 v_no_of_invoices_posted := 0;
3238 COMMIT;
3239 if gb_debug then
3240 fnd_file.put_line(fnd_file.log, '9-After Commit of '||v_commit_interval||' entries');
3241 end if;
3242
3243 ELSE
3244 v_no_of_invoices_posted := v_no_of_invoices_posted + 1;
3245 END IF;
3246
3247 <<next_exc_inv>>
3248 v_save_point_set := false;
3249
3250 EXCEPTION
3251 WHEN OTHERS THEN
3252 v_tot_errored_entries := v_tot_errored_entries + 1;
3253 fnd_file.put_line(fnd_file.log, 'Error at statement_no->'|| v_statement_no
3254 ||', rtvExcBtchGrpId->'||ln_rtv_excise_batch_group_id
3255 ||', excise_invoice_no->'||v_excise_invoice_no
3256 ||', excise_invoice_date->'||v_excise_invoice_date||' '
3257 );
3258 fnd_file.put_line(fnd_file.log, 'ErrMess->'|| SQLERRM);
3259
3260 IF v_save_point_set THEN
3261 -- This has to rollback only if SAVEPOINT is set for the exc invoice being processed
3262 ROLLBACK TO previous_savepoint;
3263 v_save_point_set := false;
3264 END IF;
3265
3266 END;
3267
3268 v_tot_amount := NULL;
3269 v_tot_rounded_amount := NULL;
3270 v_rounded_amount := NULL;
3271 v_rounded_amount_abs := NULL;
3272 v_rounded_amount_rg23 := NULL;
3273 v_rounded_cr_amount := NULL;
3274 v_rounded_dr_amount := NULL;
3275 v_rounded_cr_rg23_amount := NULL;
3276 v_rounded_dr_rg23_amount := NULL;
3277 v_rounded_cr_oth_amount := NULL;
3278 v_rounded_dr_oth_amount := NULL;
3279
3280 v_rounding_entry_type := null;
3281
3282 v_vendor_id := null;
3283 v_vendor_site_id := null;
3284 v_modvat_rm_account_id := null;
3285 v_modvat_cg_account_id := null;
3286 v_rg_account_id := null;
3287 v_rg23_balance := null;
3288 v_balance := null;
3289 v_slno := null;
3290
3291 v_rounding_id := null;
3292 v_register_id_part_ii := null;
3293
3294 v_excise_invoice_no := null;
3295 v_excise_invoice_date := null;
3296 v_register_type := null;
3297 v_receipt_num := null;
3298 v_pla_register_id := null;
3299 lv_process_message := null;
3300 lv_process_status := null;
3301
3302 v_exc_inv_rnd_counter := v_exc_inv_rnd_counter + 1;
3303
3304 END LOOP;
3305
3306 fnd_file.put_line(fnd_file.log, 'Completed. Total Rounding entries made -> '|| v_rounding_entries_made
3307 ||', errored entries->'||v_tot_errored_entries
3308 ||', Zero Exc. Amt. Invoices found->'||v_zero_round_found
3309 ||', total Ex. Invoices processed->'||v_tot_processed_invoices
3310 );
3311
3312
3313 /* Added by Ramananda for bug#4407165 */
3314 EXCEPTION
3315 WHEN OTHERS THEN
3316 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
3317 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
3318 app_exception.raise_exception;
3319
3320 END do_rtv_rounding;
3321
3322 END jai_rcv_rnd_pkg;