[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.10 2007/05/02 15:31:02 bduvarag 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.inventory_item_id = d.inventory_item_id
702 AND a.shipment_header_id = v_shipment_header_id
703 AND a.excise_invoice_no = v_excise_invoice_no
704 AND a.excise_invoice_date = v_excise_invoice_date
705 AND ( (nvl(b.vendor_id,-999) = nvl(r.vendor_id,-999)
706 AND nvl(b.vendor_site_id,-999) = nvl(r.vendor_site_id,-999)
707 AND b.vendor_changed_flag = 'Y' )
708 OR
709 (nvl(c.vendor_id,-999) = nvl(r.vendor_id,-999)
710 AND nvl(c.vendor_site_id,-999) = nvl(r.vendor_site_id,-999)
711 AND b.vendor_changed_flag = 'N' )
712 ) /*bgowrava for forward porting bug#5674376*/
713 GROUP BY a.shipment_line_id, a.transaction_id, d.item_class
714 )
715 LOOP
716 --Added below 1 statements by Sanjikum for Bug #4049363
717 ln_receive_amount := v_zero_record;
718
719 ln_receive_qty := 0;
720
721 OPEN c_ja_in_receive_qty(line.shipment_line_id);
722 FETCH c_ja_in_receive_qty INTO ln_receive_qty;
723 CLOSE c_ja_in_receive_qty;
724
725 OPEN c_receipt_tax_amount(line.shipment_line_id);
726 --Added the 6 columns by Sanjikum for Bug #4049363
727 FETCH c_receipt_tax_amount INTO
728 ln_receive_amount.basic,
729 ln_receive_amount.additional,
730 ln_receive_amount.additional_cvd, /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
731 ln_receive_amount.other,
732 ln_receive_amount.excise_edu_cess,
733 ln_receive_amount.cvd_edu_cess ,
734 ln_receive_amount.sh_excise_edu_cess,
735 ln_receive_amount.sh_cvd_edu_cess;
736
737 CLOSE c_receipt_tax_amount;
738
739 FND_FILE.PUT_LINE( FND_FILE.log, 'TrxId:'||line.transaction_id ||', RecvQty->'||ln_receive_qty);
740
741 -- this is to loop through all the RECEIVE and related CORRECT transactions
742 lv_ttype_correct := 'CORRECT' ; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
743 FOR trx IN (select transaction_id, quantity
744 from rcv_transactions
745 where shipment_line_id = line.shipment_line_id
746 and (
747 (transaction_type = lv_ttype_correct and parent_transaction_id = line.transaction_id)
748 OR transaction_id = line.transaction_id
749 )
750 )
751 LOOP
752
753 --Added below the 7 statements by Sanjikum for Bug #4049363
754 ln_cenvat_amount.basic := ln_receive_amount.basic * trx.quantity/ln_receive_qty;
755 ln_cenvat_amount.additional := ln_receive_amount.additional * trx.quantity/ln_receive_qty;
756 ln_cenvat_amount.other := ln_receive_amount.other * trx.quantity/ln_receive_qty;
757 ln_cenvat_amount.excise_edu_cess := ln_receive_amount.excise_edu_cess * trx.quantity/ln_receive_qty;
758 ln_cenvat_amount.cvd_edu_cess := ln_receive_amount.cvd_edu_cess * trx.quantity/ln_receive_qty;
759 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
760 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
761 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);
762 v_statement_no := '1.5';
763
764 -- populate item class
765 INSERT INTO JAI_CMN_RG_ROUND_LINES (ROUNDING_LINE_ID,
766 ROUNDING_ID, SRC_LINE_ID, SRC_TRANSACTION_ID,
767 EXCISE_AMT,
768 BASIC_ED, ADDITIONAL_ED, OTHER_ED, --Added by Sanjikum for Bug #4049363
769 EXCISE_EDU_CESS, CVD_EDU_CESS,--Added by Sanjikum for Bug #4049363
770 sh_excise_edu_cess , -- Date 16/04/2007 by sacsethi for Bug#5989740
771 sh_cvd_edu_cess ,
772 ITEM_CLASS, CREATION_DATE, CREATED_BY,
773 program_application_id, program_id, program_login_id, request_id
774 )
775 VALUES ( JAI_CMN_RG_ROUND_LINES_S.nextval,
776 v_rounding_id,
777 line.shipment_line_id,
778 trx.transaction_id,
779 ln_cenvat_amount.total, --Added by Sanjikum for Bug #4049363
780 ln_cenvat_amount.basic,
781 ln_cenvat_amount.additional,
782 ln_cenvat_amount.other, --Columns Added by Sanjikum for Bug #4049363
783 ln_cenvat_amount.excise_edu_cess,
784 ln_cenvat_amount.cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
785 ln_cenvat_amount.sh_excise_edu_cess,
786 ln_cenvat_amount.sh_cvd_edu_cess, --Columns Added by Sanjikum for Bug #4049363
787 line.item_class, SYSDATE, v_created_by,
788 fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'), fnd_profile.value('CONC_REQUEST_ID')
789 );
790
791 --Added the 6 statements by Sanjikum for Bug #4049363
792 v_tot_amount.basic := v_tot_amount.basic + ln_cenvat_amount.basic;
793 v_tot_amount.additional := v_tot_amount.additional + ln_cenvat_amount.additional;
794 v_tot_amount.other := v_tot_amount.other + ln_cenvat_amount.other;
795 v_tot_amount.excise_edu_cess := v_tot_amount.excise_edu_cess + ln_cenvat_amount.excise_edu_cess;
796 v_tot_amount.cvd_edu_cess := v_tot_amount.cvd_edu_cess + ln_cenvat_amount.cvd_edu_cess;
797 -- Date 16/04/2007 by sacsethi for Bug#5989740
798 -- start 5989740
799 v_tot_amount.sh_excise_edu_cess := v_tot_amount.sh_excise_edu_cess + ln_cenvat_amount.sh_excise_edu_cess;
800 v_tot_amount.sh_cvd_edu_cess := v_tot_amount.sh_cvd_edu_cess + ln_cenvat_amount.sh_cvd_edu_cess;
801 -- end 5989740
802 -----------------------------------------------
803 v_tot_amount.total := NVL(v_tot_amount.basic,0) +
804 NVL(v_tot_amount.additional,0) +
805 NVL(v_tot_amount.other,0) +
806 NVL(v_tot_amount.excise_edu_cess,0) +
807 NVL(v_tot_amount.cvd_edu_cess,0) +
808 NVL(v_tot_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
809 NVL(v_tot_amount.sh_cvd_edu_cess,0);
810
811 END LOOP;
812
813 IF line.item_class IN ('RMIN', 'RMEX') THEN
814 v_line_type_a_cnt := v_line_type_a_cnt + 1;
815 ELSIF line.item_class IN ('CGIN', 'CGEX') THEN
816 v_line_type_c_cnt := v_line_type_c_cnt + 1;
817 END IF;
818 v_tot_lines_cnt := v_tot_lines_cnt + 1;
819
820 END LOOP;
821
822 v_statement_no := '1.6';
823 IF v_line_type_c_cnt = v_tot_lines_cnt THEN
824 v_register_type := 'C';
825 v_rounding_type := 1;
826 ELSIF v_line_type_c_cnt = 0 THEN
827 v_register_type := 'A';
828 v_rounding_type := 0;
829 ELSE
830 v_register_type := p_register_type;
831 v_rounding_type := 2;
832 END IF;
833
834 FND_FILE.PUT_LINE( FND_FILE.log, 'Started Processing shipment_header_id->'||v_shipment_header_id
835 ||', excise_invoice_no->'||v_excise_invoice_no
836 ||', excise_invoice_date->'||v_excise_invoice_date
837 );
838
839 -- find the rounding amount for the receipt
840 -- CGIN case
841 IF v_line_type_c_cnt = v_tot_lines_cnt THEN
842
843 --7 statements added by Sanjikum for Bug #4049363
844 v_tot_amount.basic := v_tot_amount.basic/2;
845 v_tot_amount.additional := v_tot_amount.additional/2;
846 v_tot_amount.additional_cvd := v_tot_amount.additional_cvd/2;/*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
847 v_tot_amount.other := v_tot_amount.other/2;
848 v_tot_amount.excise_edu_cess := v_tot_amount.excise_edu_cess/2;
849 v_tot_amount.cvd_edu_cess := v_tot_amount.cvd_edu_cess/2;
850 -- Date 16/04/2007 by sacsethi for Bug#5989740
851 -- start 5989740
852 v_tot_amount.sh_excise_edu_cess := v_tot_amount.sh_excise_edu_cess/2;
853 v_tot_amount.sh_cvd_edu_cess := v_tot_amount.sh_cvd_edu_cess/2;
854 -- end 5989740
855 v_tot_amount.total := NVL(v_tot_amount.basic,0) +
856 NVL(v_tot_amount.additional,0) +
857 NVL(v_tot_amount.additional_cvd,0) + /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
858 NVL(v_tot_amount.other,0) +
859 NVL(v_tot_amount.excise_edu_cess,0) +
860 NVL(v_tot_amount.cvd_edu_cess,0) +
861 NVL(v_tot_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
862 NVL(v_tot_amount.sh_cvd_edu_cess,0);
863
864
865 UPDATE JAI_CMN_RG_ROUND_LINES
866 SET --7 columns added by Sanjikum for Bug #4049363
867 excise_amt = NVL(basic_ed/2,0) +
868 NVL(additional_ed/2,0) +
869 NVL(additional_cvd/2,0) + /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
870 NVL(other_ed/2,0) +
871 NVL(excise_edu_cess/2,0) +
872 NVL(cvd_edu_cess/2,0),
873 basic_ed = basic_ed/2,
874 additional_ed = additional_ed/2,
875 additional_cvd = additional_cvd/2,/*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
876 other_ed = other_ed/2,
877 excise_edu_cess = excise_edu_cess/2,
878 cvd_edu_cess = cvd_edu_cess/2 ,
879 sh_excise_edu_cess = sh_excise_edu_cess/2,
880 sh_cvd_edu_cess = sh_cvd_edu_cess/2
881 WHERE rounding_id = v_rounding_id;
882
883 -- 1st Claim
884 IF v_proceed_for_2nd_claim = 'N' THEN
885 v_statement_no := '2';
886
887 --6 statements added by Sanjikum for Bug #4049363
888 v_tot_rounded_amount.basic := FLOOR(v_tot_amount.basic); -- i.e floor(25.7) = 25
889 v_tot_rounded_amount.additional := FLOOR(v_tot_amount.additional);
890 v_tot_rounded_amount.additional_cvd := FLOOR(v_tot_amount.additional_cvd); /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
891 v_tot_rounded_amount.other := FLOOR(v_tot_amount.other);
892 v_tot_rounded_amount.excise_edu_cess := FLOOR(v_tot_amount.excise_edu_cess);
893 v_tot_rounded_amount.cvd_edu_cess := FLOOR(v_tot_amount.cvd_edu_cess);
894
895 -- Date 16/04/2007 by sacsethi for Bug#5989740
896 v_tot_rounded_amount.sh_excise_edu_cess := FLOOR(v_tot_amount.sh_excise_edu_cess);
897 v_tot_rounded_amount.sh_cvd_edu_cess := FLOOR(v_tot_amount.sh_cvd_edu_cess);
898 -- end 5989740
899
900
901 --6 statements added by Sanjikum for Bug #4049363
902 v_rounded_amount.basic := v_tot_rounded_amount.basic - v_tot_amount.basic;
903 v_rounded_amount.additional := v_tot_rounded_amount.additional - v_tot_amount.additional;
904 v_rounded_amount.additional_cvd := v_tot_rounded_amount.additional_cvd - v_tot_amount.additional_cvd;/*Added by SACSETHI for the bug 5228046
905 -Additional CVD Enhancement*/
906 v_rounded_amount.other := v_tot_rounded_amount.other - v_tot_amount.other;
907 v_rounded_amount.excise_edu_cess := v_tot_rounded_amount.excise_edu_cess - v_tot_amount.excise_edu_cess;
908 v_rounded_amount.cvd_edu_cess := v_tot_rounded_amount.cvd_edu_cess - v_tot_amount.cvd_edu_cess;
909
910 -- Date 16/04/2007 by sacsethi for Bug#5989740
911 v_rounded_amount.sh_excise_edu_cess := v_tot_rounded_amount.sh_excise_edu_cess - v_tot_amount.sh_excise_edu_cess;
912 v_rounded_amount.sh_cvd_edu_cess := v_tot_rounded_amount.sh_cvd_edu_cess - v_tot_amount.sh_cvd_edu_cess;
913 -- end 5989740
914
915 -- 2nd Claim
916 ELSIF v_proceed_for_2nd_claim = 'Y' THEN
917 --statements added by Sanjikum for Bug #4049363
918
919 v_temp_amount.basic := v_tot_amount.basic + v_1st_claim_rnd_amount.basic; -- 25.7 + 0.7 = 26.4
920 v_tot_rounded_amount.basic := ROUND(v_temp_amount.basic, v_rounding_precision); -- round(26.4, 0)= 26
921 v_rounded_amount.basic := (v_tot_rounded_amount.basic - v_temp_amount.basic) + v_1st_claim_rnd_amount.basic;
922
923 v_temp_amount.additional := v_tot_amount.additional + v_1st_claim_rnd_amount.additional;
924 v_tot_rounded_amount.additional := ROUND(v_temp_amount.additional, v_rounding_precision);
925 v_rounded_amount.additional := (v_tot_rounded_amount.additional - v_temp_amount.additional) + v_1st_claim_rnd_amount.additional;
926
927 /*
928 || Start of bug 5228046 -Additional CVD Enhancement
929 || Added by SACSETHI
930 */
931 v_temp_amount.additional_cvd := v_tot_amount.additional_cvd + v_1st_claim_rnd_amount.additional_cvd;
932 v_tot_rounded_amount.additional_cvd := ROUND(v_temp_amount.additional_cvd, v_rounding_precision);
933 v_rounded_amount.additional_cvd := (v_tot_rounded_amount.additional_cvd - v_temp_amount.additional_cvd) + v_1st_claim_rnd_amount.additional_cvd;
934
935 /* End of bug 5228046 */
936
937 v_temp_amount.other := v_tot_amount.other + v_1st_claim_rnd_amount.other;
938 v_tot_rounded_amount.other := ROUND(v_temp_amount.other, v_rounding_precision);
939 v_rounded_amount.other := (v_tot_rounded_amount.other - v_temp_amount.other) + v_1st_claim_rnd_amount.other;
940
941 v_temp_amount.excise_edu_cess := v_tot_amount.excise_edu_cess + v_1st_claim_rnd_amount.excise_edu_cess;
942 v_tot_rounded_amount.excise_edu_cess := ROUND(v_temp_amount.excise_edu_cess, v_rounding_precision);
943 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;
944
945 v_temp_amount.cvd_edu_cess := v_tot_amount.cvd_edu_cess + v_1st_claim_rnd_amount.cvd_edu_cess;
946 v_tot_rounded_amount.cvd_edu_cess := ROUND(v_temp_amount.cvd_edu_cess, v_rounding_precision);
947 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;
948
949 -- Date 16/04/2007 by sacsethi for Bug#5989740
950 -- start 5989740
951
952 v_temp_amount.sh_excise_edu_cess := v_tot_amount.sh_excise_edu_cess + v_1st_claim_rnd_amount.sh_excise_edu_cess;
953 v_tot_rounded_amount.sh_excise_edu_cess := ROUND(v_temp_amount.sh_excise_edu_cess, v_rounding_precision);
954 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;
955
956 v_temp_amount.sh_cvd_edu_cess := v_tot_amount.sh_cvd_edu_cess + v_1st_claim_rnd_amount.sh_cvd_edu_cess;
957 v_tot_rounded_amount.sh_cvd_edu_cess := ROUND(v_temp_amount.sh_cvd_edu_cess, v_rounding_precision);
958 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;
959
960 -- end 5989740
961
962 ELSE
963 FND_FILE.PUT_LINE( FND_FILE.log, 'Some Problem in CGIN Claiming');
964 END IF;
965
966 -- Other than CGIN cases
967 ELSE
968 v_statement_no := '2';
969
970 --added by Sanjikum for Bug #4049363
971 v_tot_rounded_amount.basic := ROUND(v_tot_amount.basic, v_rounding_precision);
972 v_rounded_amount.basic := v_tot_rounded_amount.basic - v_tot_amount.basic;
973
974 v_tot_rounded_amount.additional := ROUND(v_tot_amount.additional, v_rounding_precision);
975 v_rounded_amount.additional := v_tot_rounded_amount.additional - v_tot_amount.additional;
976
977 /*
978 || Start of bug 5228046 -Additional CVD Enhancement
979 || Added by SACSETHI
980 */
981
982 v_tot_rounded_amount.additional_cvd := ROUND(v_tot_amount.additional_cvd, v_rounding_precision);
983 v_rounded_amount.additional_cvd := v_tot_rounded_amount.additional_cvd - v_tot_amount.additional_cvd;
984
985 /* End of bug 5228046 */
986
987 v_tot_rounded_amount.other := ROUND(v_tot_amount.other, v_rounding_precision);
988 v_rounded_amount.other := v_tot_rounded_amount.other - v_tot_amount.other;
989
990 v_tot_rounded_amount.excise_edu_cess := ROUND(v_tot_amount.excise_edu_cess, v_rounding_precision);
991 v_rounded_amount.excise_edu_cess := v_tot_rounded_amount.excise_edu_cess - v_tot_amount.excise_edu_cess;
992
993 v_tot_rounded_amount.cvd_edu_cess := ROUND(v_tot_amount.cvd_edu_cess, v_rounding_precision);
994 v_rounded_amount.cvd_edu_cess := v_tot_rounded_amount.cvd_edu_cess - v_tot_amount.cvd_edu_cess;
995
996 -- Date 16/04/2007 by sacsethi for Bug#5989740
997 -- start 5989740
998
999 v_tot_rounded_amount.sh_excise_edu_cess := ROUND(v_tot_amount.sh_excise_edu_cess, v_rounding_precision);
1000 v_rounded_amount.sh_excise_edu_cess := v_tot_rounded_amount.sh_excise_edu_cess - v_tot_amount.sh_excise_edu_cess;
1001
1002 v_tot_rounded_amount.sh_cvd_edu_cess := ROUND(v_tot_amount.sh_cvd_edu_cess, v_rounding_precision);
1003 v_rounded_amount.sh_cvd_edu_cess := v_tot_rounded_amount.sh_cvd_edu_cess - v_tot_amount.sh_cvd_edu_cess;
1004
1005 -- end 5989740
1006
1007
1008 END IF;
1009
1010 --These are common for all the If elses above and should be sum of different tax types
1011
1012 v_tot_rounded_amount.total := NVL(v_tot_rounded_amount.basic,0) +
1013 NVL(v_tot_rounded_amount.additional,0) +
1014 NVL(v_tot_rounded_amount.additional_cvd,0) /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/ +
1015 NVL(v_tot_rounded_amount.other,0) +
1016 NVL(v_tot_rounded_amount.excise_edu_cess,0) +
1017 NVL(v_tot_rounded_amount.cvd_edu_cess,0) +
1018 NVL(v_tot_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1019 NVL(v_tot_rounded_amount.sh_cvd_edu_cess,0);
1020
1021 v_rounded_amount.total := NVL(v_rounded_amount.basic,0) +
1022 NVL(v_rounded_amount.additional,0) +
1023 NVL(v_rounded_amount.additional_cvd,0) /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/ +
1024 NVL(v_rounded_amount.other,0) +
1025 NVL(v_rounded_amount.excise_edu_cess,0) +
1026 NVL(v_rounded_amount.cvd_edu_cess,0) +
1027 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1028 NVL(v_rounded_amount.sh_cvd_edu_cess,0);
1029
1030 v_rounded_amount_rg23 := NVL(v_rounded_amount.basic,0) +
1031 NVL(v_rounded_amount.additional,0) +
1032 NVL(v_rounded_amount.additional_cvd,0) + /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/ +
1033 NVL(v_rounded_amount.other,0);
1034 v_rounded_amount_abs := ABS(NVL(v_rounded_amount.basic,0)) +
1035 ABS(NVL(v_rounded_amount.additional,0)) +
1036 ABS(NVL(v_rounded_amount.additional_cvd,0)) /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/ +
1037 ABS(NVL(v_rounded_amount.other,0)) +
1038 ABS(NVL(v_rounded_amount.excise_edu_cess,0)) +
1039 ABS(NVL(v_rounded_amount.cvd_edu_cess,0)) +
1040 ABS(NVL(v_rounded_amount.sh_excise_edu_cess,0)) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1041 ABS(NVL(v_rounded_amount.sh_cvd_edu_cess,0));
1042
1043 -- Punching Rounding_Id as 0 for RG transactions where in no Rounding is Required
1044 v_statement_no := '3';
1045
1046 --To check the amount with adding after taking absolute value
1047 IF v_rounded_amount_abs = 0 THEN
1048 v_zero_round_found := v_zero_round_found + 1;
1049
1050 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1051 lv_ttype_correct := 'CORRECT' ;
1052 /* Bug 4930048. Added by Lakshmi Gopalsami
1053 Added proper alias names and changed transaction_source_num
1054 to transaction_id while checking data in rcv_transactions.
1055 */
1056 UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
1057 SET jcrg23ac.rounding_id = 0
1058 WHERE jcrg23ac.organization_id = p_organization_id
1059 AND jcrg23ac.excise_invoice_no = v_excise_invoice_no
1060 AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
1061 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1062 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
1063 AND jcrg23ac.rounding_id IS NULL
1064 AND jcrg23ac.TRANSACTION_SOURCE_NUM = 18
1065 AND EXISTS (
1066 SELECT 1 FROM rcv_transactions rt
1067 WHERE rt.shipment_header_id = v_shipment_header_id
1068 AND rt.transaction_id = jcrg23ac.receipt_ref
1069 AND ( rt.transaction_type = r.transaction_type
1070 OR ( rt.transaction_type = lv_ttype_correct AND exists
1071 (select 1 from rcv_transactions rt1
1072 where rt1.transaction_id = rt.parent_transaction_id
1073 and rt1.transaction_type = r.transaction_type)
1074 )
1075 )
1076 );
1077
1078 DELETE FROM JAI_CMN_RG_ROUND_LINES WHERE rounding_id = v_rounding_id;
1079
1080 FND_FILE.PUT_LINE( FND_FILE.log, '**** Zero Rounded ****');
1081 GOTO next_exc_inv;
1082 ELSIF v_rounded_amount.total > 0 THEN
1083 v_rounding_entry_type := 'CR';
1084
1085 --Added by Sanjikum for Bug #4049363
1086 v_rounded_cr_amount := ABS(NVL(v_rounded_amount.basic,0) +
1087 NVL(v_rounded_amount.additional,0) +
1088 NVL(v_rounded_amount.additional_cvd,0) /*Changed by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/ +
1089 NVL(v_rounded_amount.other,0) +
1090 NVL(v_rounded_amount.excise_edu_cess,0) +
1091 NVL(v_rounded_amount.cvd_edu_cess,0) +
1092 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1093 NVL(v_rounded_amount.sh_cvd_edu_cess,0));
1094
1095 ELSIF v_rounded_amount.total < 0 THEN
1096 v_rounding_entry_type := 'DR';
1097
1098 --Added by Sanjikum for Bug #4049363
1099 v_rounded_dr_amount := ABS(NVL(v_rounded_amount.basic,0) +
1100 NVL(v_rounded_amount.additional,0) +
1101 NVL(v_rounded_amount.additional_cvd,0) + /* 5228046 -Additional CVD Enhancement*/
1102 NVL(v_rounded_amount.other,0) +
1103 NVL(v_rounded_amount.excise_edu_cess,0) +
1104 NVL(v_rounded_amount.cvd_edu_cess,0) +
1105 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1106 NVL(v_rounded_amount.sh_cvd_edu_cess,0));
1107
1108 END IF;
1109
1110 IF NVL(v_rounded_amount.basic,0) < 0 THEN
1111 v_rounded_dr_rg23_amount.basic := ABS(v_rounded_amount.basic);
1112 ELSE
1113 v_rounded_cr_rg23_amount.basic := ABS(v_rounded_amount.basic);
1114 END IF;
1115
1116 IF NVL(v_rounded_amount.additional,0) < 0 THEN
1117 v_rounded_dr_rg23_amount.additional := ABS(v_rounded_amount.additional);
1118 ELSE
1119 v_rounded_cr_rg23_amount.additional := ABS(v_rounded_amount.additional);
1120 END IF;
1121
1122 /*
1123 ||Start of bug 5228046 - Additional CVD Enhancement
1124 */
1125 IF NVL(v_rounded_amount.additional_cvd,0) < 0 THEN
1126 v_rounded_dr_rg23_amount.additional_cvd := ABS(v_rounded_amount.additional_cvd);
1127 ELSE
1128 v_rounded_cr_rg23_amount.additional_cvd := ABS(v_rounded_amount.additional_cvd);
1129 END IF;
1130
1131 /*End of bug 5228046 */
1132
1133
1134 IF NVL(v_rounded_amount.other,0) < 0 THEN
1135 v_rounded_dr_rg23_amount.other := ABS(v_rounded_amount.other);
1136 ELSE
1137 v_rounded_cr_rg23_amount.other := ABS(v_rounded_amount.other);
1138 END IF;
1139
1140 IF (NVL(v_rounded_amount.excise_edu_cess,0) +
1141 NVL(v_rounded_amount.cvd_edu_cess,0) +
1142 NVL(v_rounded_amount.sh_excise_edu_cess,0) +
1143 NVL(v_rounded_amount.sh_cvd_edu_cess,0)) < 0
1144 THEN
1145 v_rounded_dr_oth_amount.total := ABS(NVL(v_rounded_amount.excise_edu_cess,0) +
1146 NVL(v_rounded_amount.cvd_edu_cess,0) +
1147 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1148 NVL(v_rounded_amount.sh_cvd_edu_cess,0)
1149 );
1150 ELSE
1151 v_rounded_cr_oth_amount.total := ABS(NVL(v_rounded_amount.excise_edu_cess,0) +
1152 NVL(v_rounded_amount.cvd_edu_cess,0) +
1153 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
1154 NVL(v_rounded_amount.sh_cvd_edu_cess,0));
1155 END IF;
1156
1157 IF NVL(v_rounded_amount.excise_edu_cess,0) < 0 THEN
1158 v_rounded_dr_oth_amount.excise_edu_cess := ABS(v_rounded_amount.excise_edu_cess);
1159 ELSE
1160 v_rounded_cr_oth_amount.excise_edu_cess := ABS(v_rounded_amount.excise_edu_cess);
1161 END IF;
1162
1163 IF NVL(v_rounded_amount.cvd_edu_cess,0) < 0 THEN
1164 v_rounded_dr_oth_amount.cvd_edu_cess := ABS(v_rounded_amount.cvd_edu_cess);
1165 ELSE
1166 v_rounded_cr_oth_amount.cvd_edu_cess := ABS(v_rounded_amount.cvd_edu_cess);
1167 END IF;
1168
1169 -- Date 16/04/2007 by sacsethi for Bug#5989740
1170 -- start 5989740
1171
1172
1173 IF NVL(v_rounded_amount.sh_excise_edu_cess,0) < 0 THEN
1174 v_rounded_dr_oth_amount.sh_excise_edu_cess := ABS(v_rounded_amount.sh_excise_edu_cess);
1175 ELSE
1176 v_rounded_cr_oth_amount.sh_excise_edu_cess := ABS(v_rounded_amount.sh_excise_edu_cess);
1177 END IF;
1178
1179 IF NVL(v_rounded_amount.sh_cvd_edu_cess,0) < 0 THEN
1180 v_rounded_dr_oth_amount.sh_cvd_edu_cess := ABS(v_rounded_amount.sh_cvd_edu_cess);
1181 ELSE
1182 v_rounded_cr_oth_amount.sh_cvd_edu_cess := ABS(v_rounded_amount.sh_cvd_edu_cess);
1183 END IF;
1184
1185 -- end 5989740
1186
1187
1188
1189 -- check the item classes
1190 -- if there is a combination of cgin and rmin find which register to hit
1191
1192 v_statement_no := '4';
1193
1194 OPEN c_rg_modvat_account(p_organization_id, r.location_id);
1195 FETCH c_rg_modvat_account INTO v_modvat_rm_account_id, v_modvat_cg_account_id;
1196 CLOSE c_rg_modvat_account;
1197
1198 v_statement_no := '4.1';
1199 IF v_register_type = 'A' THEN
1200 v_rg_account_id := v_modvat_rm_account_id;
1201 ELSIF v_register_type = 'C' THEN
1202 v_rg_account_id := v_modvat_cg_account_id;
1203 END IF;
1204
1205 -- Required Accounts are not specified at the location where RG23 PART II entry will be posted. So, through out an error
1206 IF v_rg_account_id IS NULL THEN
1207 IF v_register_type = 'A' THEN
1208 v_err_message := 'ERROR: Cenvat RMIN Account is not specified for Location:'||r.location_id;
1209 ELSE
1210 v_err_message := 'ERROR: Cenvat CGIN Account is not specified for Location:'||r.location_id;
1211 END IF;
1212
1213 FND_FILE.PUT_LINE( FND_FILE.log, v_err_message);
1214 RAISE_APPLICATION_ERROR( -20099, v_err_message);
1215 END IF;
1216
1217 v_statement_no := '5';
1218
1219 OPEN c_vendor(v_shipment_header_id);
1220 FETCH c_vendor INTO v_vendor_id, v_vendor_site_id, v_receipt_num;
1221 CLOSE c_vendor;
1222
1223 v_statement_no := '7';
1224
1225 OPEN c_slno_balance(p_organization_id, r.location_id, v_fin_year, v_register_type);
1226 FETCH c_slno_balance INTO v_slno, v_balance;
1227 CLOSE c_slno_balance;
1228 v_rg23_balance := nvl(v_balance,0);
1229
1230 v_statement_no := '8';
1231
1232 IF v_slno IS NULL or v_slno = 0 THEN
1233 v_slno := 1;
1234 ELSE
1235 v_slno := v_slno + 1;
1236 END IF;
1237
1238 v_statement_no := '8.1';
1239
1240 INSERT INTO JAI_CMN_RG_23AC_II_TRXS(
1241 REGISTER_ID, ORGANIZATION_ID, LOCATION_ID, FIN_YEAR, INVENTORY_ITEM_ID, SLNO,
1242 CR_BASIC_ED, DR_BASIC_ED,
1243 CR_ADDITIONAL_ED, DR_ADDITIONAL_ED, --Added by Sanjikum for Bug #4049363
1244 CR_ADDITIONAL_CVD,DR_ADDITIONAL_CVD, /* ADDED THE COLUMNS CR_ADDITIONAL_CVD,DR_ADDITIONAL_CVD FOR THE ENHANCEMENT 5228046 */
1245 CR_OTHER_ED, DR_OTHER_ED, --Added by Sanjikum for Bug #4049363
1246 ROUNDING_ID, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
1247 TRANSACTION_SOURCE_NUM, RECEIPT_REF, REGISTER_TYPE, REMARKS,
1248 VENDOR_ID, VENDOR_SITE_ID, CUSTOMER_ID, CUSTOMER_SITE_ID,
1249 OPENING_BALANCE, CLOSING_BALANCE, CHARGE_ACCOUNT_ID,
1250 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
1251 , TRANSACTION_DATE, OTHER_TAX_CREDIT, OTHER_TAX_DEBIT--Added by Sanjikum
1252 ) VALUES (
1253 JAI_CMN_RG_23AC_II_TRXS_S.nextval, p_organization_id, r.location_id , v_fin_year, 0, v_slno,
1254 --Added by Sanjikum for Bug #4049363
1255 v_rounded_cr_rg23_amount.basic, v_rounded_dr_rg23_amount.basic,
1256 v_rounded_cr_rg23_amount.additional, v_rounded_dr_rg23_amount.additional,
1257 v_rounded_cr_rg23_amount.additional_cvd, v_rounded_dr_rg23_amount.additional_cvd,/* added for the enhancement 5228046 */
1258 v_rounded_cr_rg23_amount.other, v_rounded_dr_rg23_amount.other,
1259 -1, r.excise_invoice_no, r.excise_invoice_date,
1260 18, r.shipment_header_id, v_register_type, 'Rounding Entry for Receipt No:'||v_receipt_num,
1261 v_vendor_id, v_vendor_site_id, NULL, NULL,
1262 v_rg23_balance, v_rg23_balance + v_rounded_amount_rg23, v_rg_account_id,
1263 SYSDATE, v_created_by, SYSDATE, v_created_by, v_last_update_login
1264 , v_today, v_rounded_cr_oth_amount.total, v_rounded_dr_oth_amount.total
1265
1266 ) RETURNING register_id INTO v_register_id_part_ii;
1267
1268 DECLARE
1269 v_tax_type JAI_CMN_RG_OTHERS.tax_type%TYPE;
1270 v_dr_amt JAI_CMN_RG_OTHERS.debit%TYPE;
1271 v_cr_amt JAI_CMN_RG_OTHERS.credit%TYPE;
1272 BEGIN
1273
1274 FOR I in 1..4 LOOP -- Date 16/04/2007 by sacsethi for Bug#5989740
1275
1276 /* Vijay Shankar for Bug#4103161
1277 SELECT DECODE(i, 1, jai_constants.tax_type_exc_edu_cess, 2, jai_constants.tax_type_cvd_edu_cess) tax_type,
1278 DECODE(i, 1, v_rounded_dr_oth_amount.excise_edu_cess, 2, v_rounded_dr_oth_amount.cvd_edu_cess) dr_cess_amount,
1279 DECODE(i, 1, v_rounded_cr_oth_amount.excise_edu_cess, 2, v_rounded_cr_oth_amount.cvd_edu_cess) cr_cess_amount
1280 INTO v_tax_type, v_dr_amt, v_cr_amt
1281 FROM dual;
1282 */
1283
1284 if i = 1 then
1285 v_tax_type := jai_constants.tax_type_exc_edu_cess;
1286 v_dr_amt := v_rounded_dr_oth_amount.excise_edu_cess;
1287 v_cr_amt := v_rounded_cr_oth_amount.excise_edu_cess;
1288 elsif i = 2 then
1289 v_tax_type := jai_constants.tax_type_cvd_edu_cess;
1290 v_dr_amt := v_rounded_dr_oth_amount.cvd_edu_cess;
1291 v_cr_amt := v_rounded_cr_oth_amount.cvd_edu_cess;
1292 -- Date 16/04/2007 by sacsethi for Bug#5989740
1293 elsif i = 3 then
1294 v_tax_type := jai_constants.tax_type_sh_exc_edu_cess;
1295 v_dr_amt := v_rounded_dr_oth_amount.sh_excise_edu_cess;
1296 v_cr_amt := v_rounded_cr_oth_amount.sh_excise_edu_cess;
1297 elsif i = 4 then
1298 v_tax_type := jai_constants.tax_type_sh_cvd_edu_cess;
1299 v_dr_amt := v_rounded_dr_oth_amount.sh_cvd_edu_cess;
1300 v_cr_amt := v_rounded_cr_oth_amount.sh_cvd_edu_cess;
1301 -- end 5989740
1302 end if;
1303
1304 IF NVL(v_dr_amt,0) <> 0 OR NVL(v_cr_amt,0) <> 0 THEN
1305 INSERT INTO JAI_CMN_RG_OTHERS
1306 (RG_OTHER_ID,
1307 SOURCE_TYPE,
1308 SOURCE_REGISTER,
1309 SOURCE_REGISTER_ID,
1310 TAX_TYPE,
1311 DEBIT,
1312 CREDIT,
1313 OPENING_BALANCE,
1314 CLOSING_BALANCE,
1315 CREATED_BY,
1316 CREATION_DATE,
1317 LAST_UPDATED_BY,
1318 LAST_UPDATE_DATE,
1319 LAST_UPDATE_LOGIN)
1320 VALUES
1321 (JAI_CMN_RG_OTHERS_S.nextval,
1322 1,
1323 DECODE(v_register_type,'A','RG23A_P2','C','RG23C_P2'),/*for bug 5054176*/
1324 v_register_id_part_ii,
1325 v_tax_type,
1326 v_dr_amt,
1327 v_cr_amt,
1328 NULL,
1329 NULL,
1330 v_created_by,
1331 sysdate,
1332 v_created_by,
1333 sysdate,
1334 v_last_update_login);
1335 END IF;
1336 END LOOP;
1337 END;
1338
1339 v_statement_no := '10a';
1340 -- this call will update Rounding RG23 PartII entry with Period_balance_id and updates all records of JAI_CMN_RG_PERIOD_BALS
1341 -- that come after the period in which parent Excise invoice has hit RG
1342 jai_cmn_rg_period_bals_pkg.adjust_rounding(v_register_id_part_ii, v_period_balance_id, v_no_of_periods_updated);
1343
1344 v_statement_no := '11';
1345 INSERT INTO JAI_CMN_RG_ROUND_HDRS(
1346 ROUNDING_ID, SOURCE_HEADER_ID, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
1347 EXCISE_AMT_BEFORE_ROUNDING, EXCISE_AMT_AFTER_ROUNDING,
1348 --Added the below 3 by Sanjikum for Bug #4049363
1349 BASIC_ED, ROUNDED_BASIC_ED,
1350 ADDITIONAL_ED, ROUNDED_ADDITIONAL_ED,
1351 ADDITIONAL_CVD, ROUNDED_ADDITIONAL_CVD, /* ADDED FOR THE BUG 5228046 -ADDITIONAL CVD ENHANCEMENT*/
1352 OTHER_ED, ROUNDED_OTHER_ED,
1353 EXCISE_EDU_CESS, ROUNDED_EXCISE_EDU_CESS,
1354 sh_excise_edu_cess, rounded_sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
1355 CVD_EDU_CESS, ROUNDED_CVD_EDU_CESS,
1356 sh_cvd_edu_cess, rounded_sh_cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
1357 REGISTER_ID, SOURCE, SRC_TRANSACTION_TYPE,
1358 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
1359 , register, -- Vijay Shankar for Bug#4103161
1360 program_application_id, program_id, program_login_id, request_id
1361
1362 ) VALUES (
1363 v_rounding_id, v_shipment_header_id, v_excise_invoice_no, v_excise_invoice_date,
1364 v_tot_amount.total, v_tot_rounded_amount.total,
1365 --Added the below 6 by Sanjikum for Bug #4049363
1366 v_tot_amount.basic, v_tot_rounded_amount.basic,
1367 v_tot_amount.additional, v_tot_rounded_amount.additional,
1368 v_tot_amount.additional_cvd, v_tot_rounded_amount.additional_cvd,/* added for the bug 5228046 -additional cvd enhancement*/
1369 v_tot_amount.other, v_tot_rounded_amount.other,
1370 v_tot_amount.excise_edu_cess, v_tot_rounded_amount.excise_edu_cess,
1371 v_tot_amount.sh_excise_edu_cess, v_tot_rounded_amount.sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
1372 v_tot_amount.cvd_edu_cess, v_tot_rounded_amount.cvd_edu_cess,
1373 v_tot_amount.sh_cvd_edu_cess, v_tot_rounded_amount.sh_cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
1374 v_register_id_part_ii, 'PO', v_transaction_type,
1375 SYSDATE, v_created_by, SYSDATE, v_created_by, v_last_update_login
1376 , LV_RG23_REGISTER, -- Vijay Shankar for Bug#4103161
1377 fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'), fnd_profile.value('CONC_REQUEST_ID')
1378 );
1379
1380 v_statement_no := '12';
1381
1382 -- 2 (CR, DR) GL Interface related calls has to be coded
1383 -- Modvat Account entries
1384 pass_accounting(
1385 p_organization_id => p_organization_id,
1386 p_transaction_id => v_rounding_id,
1387 p_transaction_date => v_today,
1388 p_shipment_line_id => -1,
1389 p_acct_type => v_acct_type,
1390 p_acct_nature => v_acct_nature,
1391 p_source => v_source_name,
1392 p_category => v_category_name,
1393 p_code_combination_id => v_rg_account_id,
1394 p_entered_dr => v_rounded_cr_amount,
1395 p_entered_cr => v_rounded_dr_amount,
1396 p_created_by => v_created_by,
1397 p_currency_code => 'INR',
1398 p_currency_conversion_type => NULL,
1399 p_currency_conversion_date => NULL,
1400 p_currency_conversion_rate => NULL,
1401 p_receipt_num => v_receipt_num
1402 );
1403
1404 v_statement_no := '13';
1405
1406 jai_cmn_gl_pkg.create_gl_entry(
1407 p_organization_id => p_organization_id,
1408 p_currency_code => 'INR',
1409 p_credit_amount => v_rounded_dr_amount,
1410 p_debit_amount => v_rounded_cr_amount,
1411 p_cc_id => v_rg_account_id,
1412 p_je_source_name => v_source_name,
1413 p_je_category_name => v_category_name,
1414 p_created_by => v_created_by,
1415 p_accounting_date => v_today,
1416 p_reference_10 => 'India Local Rounding Entry for Shipment_Header_id:'||r.shipment_header_id
1417 ||', excise_invoice_no:'||r.excise_invoice_no||', exc_inv_date:'||r.excise_invoice_date,
1418 p_reference_23 => 'ja_in_rg_rounding_p',
1419 p_reference_24 => 'JAI_CMN_RG_ROUND_HDRS',
1420 p_reference_25 => 'ROUNDING_ID',
1421 p_reference_26 => v_rounding_id
1422 );
1423
1424 v_statement_no := '14';
1425
1426 -- Rounding Account entries
1427 pass_accounting(
1428 p_organization_id => p_organization_id,
1429 p_transaction_id => v_rounding_id,
1430 p_transaction_date => v_today,
1431 p_shipment_line_id => -1,
1432 p_acct_type => v_acct_type,
1433 p_acct_nature => v_acct_nature,
1434 p_source => v_source_name,
1435 p_category => v_category_name,
1436 p_code_combination_id => v_rg_rounding_account_id,
1437 p_entered_dr => v_rounded_dr_amount,
1438 p_entered_cr => v_rounded_cr_amount,
1439 p_created_by => v_created_by,
1440 p_currency_code => 'INR',
1441 p_currency_conversion_type => NULL,
1442 p_currency_conversion_date => NULL,
1443 p_currency_conversion_rate => NULL,
1444 p_receipt_num => v_receipt_num
1445 );
1446
1447 v_statement_no := '15';
1448
1449 jai_cmn_gl_pkg.create_gl_entry(
1450 p_organization_id => p_organization_id,
1451 p_currency_code => 'INR',
1452 p_credit_amount => v_rounded_cr_amount,
1453 p_debit_amount => v_rounded_dr_amount,
1454 p_cc_id => v_rg_rounding_account_id,
1455 p_je_source_name => v_source_name,
1456 p_je_category_name => v_category_name,
1457 p_created_by => v_created_by,
1458 p_accounting_date => v_today,
1459 p_reference_10 => 'India Local Rounding Entry for Shipment_Header_id:'||r.shipment_header_id
1460 ||', excise_invoice_no:'||r.excise_invoice_no||', exc_inv_date:'||r.excise_invoice_date,
1461 p_reference_23 => 'ja_in_rg_rounding_p',
1462 p_reference_24 => 'JAI_CMN_RG_ROUND_HDRS',
1463 p_reference_25 => 'ROUNDING_ID',
1464 p_reference_26 => v_rounding_id
1465 );
1466
1467 v_statement_no := '16';
1468
1469 IF v_register_type = 'A' THEN
1470 UPDATE JAI_CMN_RG_BALANCES
1471 SET rg23a_balance = nvl(rg23a_balance, 0) + v_rounded_amount_rg23
1472 WHERE organization_id = p_organization_id
1473 AND location_id = r.location_id;
1474
1475 ELSIF v_register_type = 'C' THEN
1476 UPDATE JAI_CMN_RG_BALANCES
1477 SET rg23c_balance = nvl(rg23c_balance, 0) + v_rounded_amount_rg23
1478 WHERE organization_id = p_organization_id
1479 AND location_id = r.location_id;
1480
1481 END IF;
1482
1483 v_statement_no := '16.1';
1484
1485 FND_FILE.put_line( FND_FILE.log, 'v_full_cgin_case->'||v_full_cgin_case
1486 ||', cent_cnt->'||r_cgin_chk_for_2nd_claim.cent_percent_cnt
1487 ||', exc_rnd_cnt->'||v_excise_inv_rnd_cnt
1488 );
1489
1490 -- Updating the RG lines of Receipt with Rounding_ID
1491 IF v_full_cgin_case = 'Y' AND r_cgin_chk_for_2nd_claim.cent_percent_cnt > 0
1492 AND v_excise_inv_rnd_cnt=0
1493 THEN
1494
1495 FND_FILE.put_line( FND_FILE.log, '1st Rounding of CGIN in Case of 100% CGIN claim');
1496 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1497 lv_ttype_correct := 'CORRECT' ;
1498 /* Bug 4930048. Added by Lakshmi Gopalsami
1499 Added proper aliases and changed transaction_source_num
1500 to transaction_id
1501 */
1502 /* Bug 5207827. Added by Lakshmi Gopalsami
1503 Fixed performance issue for sql id - 17699668
1504 Removed EXISTS and added IN clause
1505 */
1506 UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
1507 SET jcrg23ac.rounding_id = v_rounding_id
1508 WHERE jcrg23ac.organization_id = p_organization_id
1509 AND jcrg23ac.excise_invoice_no = v_excise_invoice_no
1510 AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
1511 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1512 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
1513 AND jcrg23ac.rounding_id IS NULL
1514 AND jcrg23ac.TRANSACTION_SOURCE_NUM = 18
1515 AND jcrg23ac.receipt_ref IN (
1516 SELECT rt.transaction_id
1517 FROM rcv_transactions rt
1518 WHERE rt.shipment_header_id = v_shipment_header_id
1519 AND ( rt.transaction_type = r.transaction_type
1520 OR ( rt.transaction_type = lv_ttype_correct
1521 AND exists (select 1
1522 from rcv_transactions rt1
1523 where rt1.transaction_id = rt.parent_transaction_id
1524 and rt1.transaction_type = r.transaction_type)
1525 )
1526 )
1527 )
1528 AND register_id IN (
1529 select min(register_id) from JAI_CMN_RG_23AC_II_TRXS
1530 WHERE organization_id = p_organization_id
1531 AND excise_invoice_no = v_excise_invoice_no
1532 AND excise_invoice_date = v_excise_invoice_date
1533 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1534 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
1535 AND rounding_id IS NULL
1536 AND TRANSACTION_SOURCE_NUM = 18
1537 group by RECEIPT_REF);
1538
1539 ELSE
1540
1541 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1542
1543 lv_ttype_correct := 'CORRECT' ;
1544
1545 /* Bug 4930048. Added by Lakshmi Gopalsami
1546 Added proper aliases and changed transaction_source_num
1547 to transaction_id
1548 */
1549
1550 UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
1551 SET jcrg23ac.rounding_id = v_rounding_id
1552 WHERE jcrg23ac.organization_id = p_organization_id
1553 AND jcrg23ac.excise_invoice_no = v_excise_invoice_no
1554 AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
1555 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1556 AND nvl(vendor_site_id,-999)= nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
1557 AND jcrg23ac.rounding_id IS NULL
1558 AND jcrg23ac.TRANSACTION_SOURCE_NUM = 18
1559 AND EXISTS (
1560 SELECT rt.transaction_id
1561 FROM rcv_transactions rt
1562 WHERE rt.shipment_header_id = v_shipment_header_id
1563 AND rt.transaction_id = jcrg23ac.receipt_ref
1564 AND ( rt.transaction_type = r.transaction_type
1565 OR ( rt.transaction_type = lv_ttype_correct
1566 AND exists --'CORRECT'
1567 (select 1
1568 from rcv_transactions rt1
1569 where rt1.transaction_id= rt.parent_transaction_id
1570 and rt1.transaction_type = r.transaction_type)
1571 )
1572 )
1573 );
1574
1575 END IF;
1576
1577
1578 v_rounding_entries_made := v_rounding_entries_made + 1;
1579
1580 v_statement_no := '17';
1581 IF v_no_of_invoices_posted >= v_commit_interval THEN
1582 v_no_of_invoices_posted := 0;
1583 COMMIT;
1584 ELSE
1585 v_no_of_invoices_posted := v_no_of_invoices_posted + 1;
1586 END IF;
1587
1588 <<next_exc_inv>>
1589 v_save_point_set := false;
1590
1591 EXCEPTION
1592 WHEN OTHERS THEN
1593 v_tot_errored_entries := v_tot_errored_entries + 1;
1594 FND_FILE.PUT_LINE( FND_FILE.log, 'Error at statement_no->'|| v_statement_no
1595 ||', shipment_header_id->'||v_shipment_header_id
1596 ||', excise_invoice_no->'||v_excise_invoice_no
1597 ||', excise_invoice_date->'||v_excise_invoice_date||' '
1598 );
1599 FND_FILE.PUT_LINE( FND_FILE.log, 'ErrMess->'|| SQLERRM);
1600
1601 IF v_save_point_set THEN
1602 -- This has to rollback only if SAVEPOINT is set for the exc invoice being processed
1603 ROLLBACK TO previous_savepoint;
1604 v_save_point_set := false;
1605 END IF;
1606
1607 END;
1608
1609 --Added the below 5 by Sanjikum for Bug #4049363
1610 v_tot_amount := NULL;
1611 v_tot_rounded_amount := NULL;
1612 v_rounded_amount := NULL;
1613 v_rounded_amount_abs := NULL;
1614 v_rounded_amount_rg23 := NULL;
1615 v_rounded_cr_amount := NULL;
1616 v_rounded_dr_amount := NULL;
1617 v_rounded_cr_rg23_amount := NULL;
1618 v_rounded_dr_rg23_amount := NULL;
1619 v_rounded_cr_oth_amount := NULL;
1620 v_rounded_dr_oth_amount := NULL;
1621
1622 v_rounding_entry_type := null;
1623
1624 v_vendor_id := null;
1625 v_vendor_site_id := null;
1626 v_modvat_rm_account_id := null;
1627 v_modvat_cg_account_id := null;
1628 v_rg_account_id := null;
1629 v_rg23_balance := null;
1630 v_balance := null;
1631 v_slno := null;
1632
1633 v_rounding_id := null;
1634 v_register_id_part_ii := null;
1635
1636 v_shipment_header_id := null;
1637 v_excise_invoice_no := null;
1638 v_excise_invoice_date := null;
1639 v_rounding_type := null;
1640 v_register_type := null;
1641
1642 v_exc_inv_rnd_counter := v_exc_inv_rnd_counter + 1;
1643
1644 END LOOP;
1645
1646 END LOOP;
1647
1648 FND_FILE.PUT_LINE( FND_FILE.log, 'Completed. Total Rounding entries made -> '|| v_rounding_entries_made
1649 ||', errored entries->'||v_tot_errored_entries
1650 ||', Zero Exc. Amt. Invoices found->'||v_zero_round_found
1651 ||', total Ex. Invoices processed->'||v_tot_processed_invoices
1652 );
1653
1654 <<do_commit>>
1655 COMMIT;
1656
1657
1658 /* Added by Ramananda for bug#4407165 */
1659 EXCEPTION
1660 WHEN OTHERS THEN
1661 p_err_buf := null;
1662 p_ret_code := null;
1663 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1664 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1665 app_exception.raise_exception;
1666
1667 END do_rounding;
1668
1669 FUNCTION get_parent_register_id
1670 (
1671 p_register_id IN NUMBER
1672 ) RETURN NUMBER IS
1673
1674 /* Added by Ramananda for bug#4407165 */
1675 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_rnd_pkg.get_parent_register_id';
1676
1677 CURSOR c_part_ii_record(cp_register_id IN NUMBER) IS
1678 SELECT
1679 register_type, organization_id, location_id, excise_invoice_no, excise_invoice_date,
1680 receipt_ref shipment_header_id,
1681 vendor_id,vendor_site_id/*bgowrava for forward porting bug#5674376*/
1682 FROM JAI_CMN_RG_23AC_II_TRXS
1683 WHERE register_id = cp_register_id;
1684 r_part_ii_rec c_part_ii_record%ROWTYPE;
1685
1686 -- this cursor excludes rounding entries
1687 CURSOR c_parent_part_ii_rec( cp_register_type IN VARCHAR2, cp_organization_id IN NUMBER, cp_location_id IN NUMBER,
1688 cp_excise_invoice_no IN VARCHAR2, cp_excise_invoice_date IN DATE,
1689 cp_vendor_id NUMBER,cp_vendor_site_id NUMBER,cp_shipment_header_id NUMBER) IS
1690 SELECT min(register_id) register_id
1691 FROM JAI_CMN_RG_23AC_II_TRXS
1692 WHERE organization_id = cp_organization_id
1693 AND location_id = cp_location_id
1694 AND register_type = cp_register_type
1695 AND excise_invoice_no = cp_excise_invoice_no
1696 AND excise_invoice_date = cp_excise_invoice_date
1697 AND inventory_item_id <> 0;
1698
1699 CURSOR c_2nd_claim_register_id(cp_organization_id IN NUMBER, cp_location_id IN NUMBER,
1700 cp_excise_invoice_no IN VARCHAR2, cp_excise_invoice_date IN DATE,
1701 cp_vendor_id NUMBER,cp_vendor_site_id NUMBER) IS /*bgowrava for forward porting bug#5674376*/
1702 select min(register_id)
1703 from JAI_CMN_RG_23AC_II_TRXS a
1704 where organization_id = cp_organization_id
1705 and location_id = cp_location_id
1706 and excise_invoice_no = cp_excise_invoice_no
1707 and excise_invoice_date = cp_excise_invoice_date
1708 AND nvl(vendor_id,-999) = nvl(cp_vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1709 AND nvl(vendor_site_id,-999) = nvl(cp_vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
1710 and inventory_item_id <> 0
1711 and exists (select '1'
1712 from JAI_CMN_RG_23AC_II_TRXS
1713 where organization_id = a.organization_id
1714 and location_id = a.location_id
1715 and excise_invoice_no = a.excise_invoice_no
1716 and excise_invoice_date = a.excise_invoice_date
1717 AND nvl(vendor_id,-999) = nvl(a.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1718 AND nvl(vendor_site_id,-999) = nvl(a.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
1719 and inventory_item_id = a.inventory_item_id
1720 and receipt_ref = a.RECEIPT_REF
1721 and register_id < a.register_id
1722 );
1723
1724 cursor c_get_all_rounding_ids
1725 (cp_excise_invoice_no IN VARCHAR2, cp_excise_invoice_date IN DATE,
1726 cp_vendor_id NUMBER, cp_vendor_site_id NUMBER) is
1727 select min(register_id) minimum_rounding_id, max(register_id) maximum_rounding_id
1728 from JAI_CMN_RG_23AC_II_TRXS
1729 where inventory_item_id = 0
1730 and excise_invoice_no = cp_excise_invoice_no
1731 and excise_invoice_date = cp_excise_invoice_date
1732 and nvl(vendor_id,-999) = nvl(cp_vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
1733 and nvl(vendor_site_id,-999) = nvl(cp_vendor_site_id,-999);/*bgowrava for forward porting bug#5674376*/
1734
1735
1736 r_full_cgin_chk c_full_cgin_chk%ROWTYPE;
1737 r_parent_part_ii_rec c_parent_part_ii_rec%ROWTYPE;
1738 r_cgin_chk c_cgin_chk_for_2nd_claim%ROWTYPE;
1739 r_get_all_rounding_ids c_get_all_rounding_ids%rowtype;
1740
1741 v_return_register_id NUMBER;
1742 BEGIN
1743
1744 /* Get the details of the rounding entry */
1745 OPEN c_part_ii_record(p_register_id);
1746 FETCH c_part_ii_record INTO r_part_ii_rec;
1747 CLOSE c_part_ii_record;
1748
1749 /* Check if rounding is against an excise invoice having only CGIN items */
1750 OPEN c_full_cgin_chk(r_part_ii_rec.shipment_header_id, r_part_ii_rec.excise_invoice_no,
1751 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*/
1752 FETCH c_full_cgin_chk INTO r_full_cgin_chk;
1753 CLOSE c_full_cgin_chk;
1754
1755 /* check if the CGIN invoice has been claimed 100 % */
1756 OPEN c_cgin_chk_for_2nd_claim(r_part_ii_rec.shipment_header_id, r_part_ii_rec.excise_invoice_no,
1757 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*/
1758 FETCH c_cgin_chk_for_2nd_claim INTO r_cgin_chk;
1759 CLOSE c_cgin_chk_for_2nd_claim;
1760
1761 -- Condition to test whether excise invoice is of full CGIN items and 2nd Claim is done for some/all lines
1762 IF r_full_cgin_chk.total_cnt = r_full_cgin_chk.cgin_cnt AND
1763 r_cgin_chk.cent_percent_cnt > 0
1764 THEN
1765
1766 /* Check if two rounding entry has been passed for the given excise invoice */
1767 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*/
1768 fetch c_get_all_rounding_ids into r_get_all_rounding_ids;
1769 close c_get_all_rounding_ids;
1770
1771 if r_get_all_rounding_ids.minimum_rounding_id <> r_get_all_rounding_ids.maximum_rounding_id then
1772
1773 if r_get_all_rounding_ids.maximum_rounding_id = p_register_id then
1774
1775 FND_FILE.put_line(fnd_file.log, '2nd Claim Rounding Register_id is Selected');
1776
1777 OPEN c_2nd_claim_register_id
1778 (r_part_ii_rec.organization_id, r_part_ii_rec.location_id,
1779 r_part_ii_rec.excise_invoice_no, r_part_ii_rec.excise_invoice_date
1780 , r_part_ii_rec.vendor_id, r_part_ii_rec.vendor_site_id);/*bgowrava for forward porting bug#5674376*/
1781 FETCH c_2nd_claim_register_id INTO v_return_register_id;
1782 CLOSE c_2nd_claim_register_id;
1783
1784 goto exit_from_function;
1785
1786 else
1787
1788 FND_FILE.put_line(fnd_file.log, '1st Claim Rounding Register_id is Selected');
1789
1790 end if; /* 2nd claim rounding id is selected */
1791
1792 end if;
1793
1794 end if; /* excise invoice is of full CGIN items and 2nd Claim is done for some/all lines */
1795
1796 FND_FILE.put_line(fnd_file.log, 'Minimum Register_id is Selected');
1797 OPEN c_parent_part_ii_rec
1798 (r_part_ii_rec.register_type, r_part_ii_rec.organization_id,
1799 r_part_ii_rec.location_id, r_part_ii_rec.excise_invoice_no, r_part_ii_rec.excise_invoice_date,
1800 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*/
1801 FETCH c_parent_part_ii_rec INTO v_return_register_id;
1802 CLOSE c_parent_part_ii_rec;
1803
1804 -- v_return_register_id := r_parent_part_ii_rec.register_id;
1805
1806 << exit_from_function >>
1807 return v_return_register_id;
1808
1809 /* Added by Ramananda for bug#4407165 */
1810 EXCEPTION
1811 WHEN OTHERS THEN
1812 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1813 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1814 app_exception.raise_exception;
1815
1816 END get_parent_register_id;
1817
1818 PROCEDURE pass_accounting(
1819 p_organization_id number,
1820 p_transaction_id number, -- this is the rounding_id passed in JAI_CMN_RG_ROUND_HDRS table
1821 p_transaction_date date,
1822 p_shipment_line_id number,
1823 p_acct_type varchar2,
1824 p_acct_nature varchar2,
1825 p_source varchar2,
1826 p_category varchar2,
1827 p_code_combination_id number,
1828 p_entered_dr number,
1829 p_entered_cr number,
1830 p_created_by number,
1831 p_currency_code varchar2,
1832 p_currency_conversion_type varchar2,
1833 p_currency_conversion_date varchar2,
1834 p_currency_conversion_rate varchar2,
1835 p_receipt_num varchar2
1836 ) IS
1837
1838 v_organization_code ORG_ORGANIZATION_DEFINITIONS.organization_code%TYPE;
1839 --v_receipt_num RCV_SHIPMENT_HEADERS.receipt_num%TYPE;
1840 v_period_name GL_PERIODS.period_name%TYPE;
1841
1842 v_transaction_type VARCHAR2(20);
1843 --v_shipment_header_id NUMBER;
1844
1845 CURSOR c_shipment_header_id(cp_rounding_id IN NUMBER) IS
1846 SELECt source_header_id shipment_header_id
1847 FROM JAI_CMN_RG_ROUND_HDRS
1848 WHERE rounding_id = cp_rounding_id;
1849
1850 CURSOR c_receipt_num(cp_shipment_header_id IN NUMBER) IS
1851 SELECt receipt_num
1852 FROM rcv_shipment_headers
1853 WHERE shipment_header_id = cp_shipment_header_id;
1854
1855 /* Bug 5243532. Added by Lakshmi Gopalsami
1856 * Removed org_organization_definitions from the
1857 * cursor c_orgn_code_n_period_name
1858 * and passed set_of_books_id to the cursor. Also removed
1859 * gl_sets_of_books and included gl_ledgers.
1860 */
1861
1862 CURSOR c_orgn_code_n_period_name(cp_set_of_books_id IN NUMBER) IS
1863 SELECT gd.period_name
1864 FROM gl_ledgers gle,gl_periods gd
1865 WHERE gle.ledger_id = cp_set_of_books_id
1866 AND gd.period_set_name = gle.period_set_name
1867 AND trunc(p_transaction_date) BETWEEN gd.start_date and gd.end_date
1868 AND adjustment_period_flag='N';
1869
1870 /* Added by Ramananda for bug#4407165 */
1871 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_rnd_pkg.pass_accounting';
1872
1873 /* Bug 5243532. Added by Lakshmi Gopalsami
1874 * Defined variable for implementing caching logic.
1875 */
1876 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
1877 ln_set_of_books_id NUMBER;
1878
1879 BEGIN
1880
1881 v_transaction_type := 'HEADER';
1882 /* Bug 5243532. Added by Lakshmi Gopalsami
1883 * Implemented caching logic for getting organization_code
1884 */
1885 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
1886 (p_org_id => p_organization_id);
1887 v_organization_code := l_func_curr_det.organization_code;
1888 ln_set_of_books_id := l_func_curr_det.ledger_id;
1889
1890 /* Bug 5243532. Added by Lakshmi Gopalsami
1891 * Passes ln_set_of_books_id instead of p_transaction_id
1892 */
1893
1894 OPEN c_orgn_code_n_period_name(ln_set_of_books_id);
1895 FETCH c_orgn_code_n_period_name INTO v_period_name;
1896 CLOSE c_orgn_code_n_period_name;
1897
1898 /*
1899 OPEN c_shipment_header_id(p_transaction_id);
1900 FETCH c_shipment_header_id INTO v_shipment_header_id;
1901 CLOSE c_shipment_header_id;
1902
1903 OPEN c_receipt_num(v_shipment_header_id);
1904 FETCH c_receipt_num INTO v_receipt_num;
1905 CLOSE c_receipt_num;
1906 */
1907 INSERT INTO JAI_RCV_JOURNAL_ENTRIES (JOURNAL_ENTRY_ID,
1908 organization_code, receipt_num, transaction_id, creation_date, transaction_date,
1909 shipment_line_id, acct_type, acct_nature, source_name, category_name,
1910 code_combination_id, entered_dr, entered_cr, transaction_type, period_name,
1911 created_by, currency_code, currency_conversion_type, currency_conversion_date,
1912 currency_conversion_rate
1913 ) VALUES ( JAI_RCV_JOURNAL_ENTRIES_S.nextval,
1914 v_organization_code, p_receipt_num, p_transaction_id, sysdate, p_transaction_date,
1915 p_shipment_line_id, p_acct_type, p_acct_nature, p_source, p_category,
1916 p_code_combination_id, p_entered_dr, p_entered_cr, v_transaction_type, v_period_name,
1917 p_created_by, p_currency_code, p_currency_conversion_type, p_currency_conversion_date,
1918 p_currency_conversion_rate
1919 );
1920
1921
1922 /* Added by Ramananda for bug#4407165 */
1923 EXCEPTION
1924 WHEN OTHERS THEN
1925 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1926 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1927 app_exception.raise_exception;
1928 END pass_accounting;
1929
1930
1931 /* Following Procedure added as part of RTV Rounding Resolution. Bug#4103161 */
1932 PROCEDURE do_rtv_rounding(
1933 P_ORGANIZATION_ID IN NUMBER,
1934 P_TRANSACTION_TYPE IN VARCHAR2,
1935 P_REGISTER_TYPE IN VARCHAR2,
1936 P_EX_INVOICE_FROM_DATE IN DATE,
1937 P_EX_INVOICE_TO_DATE IN DATE
1938 ) IS
1939
1940 /* Added by Ramananda for bug#4407165 */
1941 lv_object_name CONSTANT VARCHAR2(61) := 'jai_rcv_rnd_pkg.do_rtv_rounding';
1942
1943 TYPE amount_record IS RECORD( basic NUMBER,
1944 additional NUMBER,
1945 additional_cvd NUMBER, /* 5228046 change by sacsethi */
1946 other NUMBER,
1947 excise_edu_cess NUMBER,
1948 cvd_edu_cess NUMBER,
1949 sh_excise_edu_cess NUMBER, -- Date 16/04/2007 by
1950 sh_cvd_edu_cess NUMBER, -- sacsethi for Bug#5989740
1951 total NUMBER);
1952
1953
1954 v_zero_record AMOUNT_RECORD;
1955
1956 v_tot_amount AMOUNT_RECORD;
1957 v_tot_rounded_amount AMOUNT_RECORD;
1958 v_rounded_amount AMOUNT_RECORD;
1959
1960 v_rounded_cr_rg23_amount AMOUNT_RECORD;
1961 v_rounded_dr_rg23_amount AMOUNT_RECORD;
1962 v_rounded_cr_oth_amount AMOUNT_RECORD;
1963 v_rounded_dr_oth_amount AMOUNT_RECORD;
1964
1965 v_rounded_amount_abs NUMBER;
1966 v_rounded_amount_rg23 NUMBER;
1967 v_rounded_cr_amount NUMBER;
1968 v_rounded_dr_amount NUMBER;
1969
1970 v_rounding_entry_type VARCHAR2(2);
1971
1972 v_commit_interval NUMBER(5) ;--File.Sql.35 Cbabu := 50;
1973 v_rounding_precision NUMBER(2) ;--File.Sql.35 Cbabu := 0;
1974 v_acct_type VARCHAR2(20) ;--File.Sql.35 Cbabu := 'REGULAR';
1975 v_acct_nature VARCHAR2(20) ;--File.Sql.35 Cbabu := 'CENVAT-ROUNDING';
1976 v_source_name VARCHAR2(20) ;--File.Sql.35 Cbabu := 'Purchasing India';
1977 v_category_name VARCHAR2(20) ;--File.Sql.35 Cbabu := 'Receiving India';
1978 v_statement_no VARCHAR2(4) ;--File.Sql.35 Cbabu := '0';
1979 v_err_message VARCHAR2(100) ;--File.Sql.35 Cbabu := '';
1980
1981 v_rounding_entries_made NUMBER ;--File.Sql.35 Cbabu := 0;
1982 v_tot_errored_entries NUMBER ;--File.Sql.35 Cbabu := 0;
1983 v_zero_round_found NUMBER ;--File.Sql.35 Cbabu := 0;
1984 v_tot_processed_invoices NUMBER ;--File.Sql.35 Cbabu := 0;
1985 v_no_of_invoices_posted NUMBER ;--File.Sql.35 Cbabu := 0;
1986 v_save_point_set BOOLEAN ;--File.Sql.35 Cbabu := FALSE;
1987
1988 v_fin_year NUMBER(9);
1989
1990 v_vendor_id NUMBER;
1991 v_vendor_site_id NUMBER;
1992 v_rg23_balance NUMBER;
1993
1994 v_modvat_rm_account_id NUMBER(15);
1995 v_modvat_cg_account_id NUMBER(15);
1996 v_modvat_pla_account_id NUMBER(15);
1997 v_rg_rounding_account_id NUMBER(15);
1998
1999 v_rg_account_id NUMBER(15);
2000
2001 v_register_id_part_ii NUMBER;
2002 v_rounding_id NUMBER;
2003
2004 v_created_by NUMBER ;--File.Sql.35 Cbabu := nvl(FND_GLOBAL.USER_ID, -1);
2005 v_last_update_login NUMBER ;--File.Sql.35 Cbabu := nvl(FND_GLOBAL.LOGIN_ID,- 1);
2006 v_today DATE ;--File.Sql.35 Cbabu := trunc(SYSDATE);
2007
2008 v_excise_invoice_no JAI_CMN_RG_23AC_II_TRXS.excise_invoice_no%TYPE;
2009 v_excise_invoice_date JAI_CMN_RG_23AC_II_TRXS.excise_invoice_date%TYPE;
2010 v_register_type JAI_CMN_RG_23AC_II_TRXS.register_type%TYPE;
2011 v_line_type_c_cnt NUMBER ;--File.Sql.35 Cbabu := 0;
2012 v_tot_lines_cnt NUMBER ;--File.Sql.35 Cbabu := 0;
2013
2014 CURSOR c_vendor(p_shipment_header_id IN NUMBER) IS
2015 SELECT vendor_id, vendor_site_id, receipt_num
2016 FROM rcv_shipment_headers
2017 WHERE shipment_header_id = p_shipment_header_id;
2018
2019 v_slno NUMBER;
2020 v_balance NUMBER;
2021
2022 CURSOR c_slno_balance_rg23p2(p_organization_id IN NUMBER, p_location_id IN NUMBER,
2023 p_fin_year IN NUMBER, p_register_type IN VARCHAR2) IS
2024 SELECT slno, closing_balance
2025 FROM JAI_CMN_RG_23AC_II_TRXS
2026 WHERE organization_id = p_organization_id
2027 AND location_id = p_location_id
2028 AND fin_year = p_fin_year
2029 AND register_type = p_register_type
2030 AND slno = (SELECT max(slno) slno
2031 FROM JAI_CMN_RG_23AC_II_TRXS
2032 WHERE organization_id = p_organization_id
2033 AND location_id = p_location_id
2034 AND fin_year = p_fin_year
2035 AND register_type = p_register_type);
2036
2037
2038 CURSOR c_active_fin_year(p_organization_id IN NUMBER) IS
2039 SELECT max(fin_year)
2040 FROM JAI_CMN_FIN_YEARS
2041 WHERE organization_id = p_organization_id
2042 AND fin_active_flag = 'Y';
2043
2044 CURSOR c_rg_rounding_account(p_organization_id IN NUMBER) IS
2045 SELECT rg_rounding_account_id
2046 FROM JAI_CMN_INVENTORY_ORGS
2047 WHERE organization_id = p_organization_id
2048 AND ( location_id IS NULL OR location_id = 0);
2049
2050 CURSOR c_rg_modvat_account(p_organization_id IN NUMBER, p_location_id IN NUMBER) IS
2051 SELECT modvat_rm_account_id, modvat_cg_account_id, modvat_pla_account_id
2052 FROM JAI_CMN_INVENTORY_ORGS
2053 WHERE organization_id = p_organization_id
2054 AND location_id = p_location_id;
2055
2056 ln_receive_qty NUMBER;
2057 CURSOR c_ja_in_receive_qty(cp_shipment_line_id IN NUMBER) IS
2058 SELECT qty_received
2059 FROM JAI_RCV_LINES
2060 WHERE shipment_line_id = cp_shipment_line_id;
2061
2062 --Added the below 2 by Sanjikum for Bug #4049363
2063 ln_cenvat_amount AMOUNT_RECORD;
2064 ln_receive_amount AMOUNT_RECORD;
2065
2066 CURSOR c_receipt_tax_amount(cp_shipment_line_id IN NUMBER) IS
2067 SELECT --Added the 6 columns by Sanjikum for Bug #4049363
2068 sum(decode(upper(b.tax_type), 'EXCISE', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) excise_amt,
2069 sum(decode(upper(b.tax_type), 'ADDL. EXCISE', b.tax_amount * nvl(c.mod_cr_percentage, 0),
2070 'CVD', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) additional_excise_amt,
2071 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*/
2072 sum(decode(upper(b.tax_type), 'OTHER EXCISE', b.tax_amount * nvl(c.mod_cr_percentage, 0), 0) /100) other_excise_amt,
2073 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,
2074 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 ,
2075 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
2076 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
2077 FROM JAI_RCV_LINE_TAXES b, JAI_CMN_TAXES_ALL c
2078 WHERE b.shipment_line_id = cp_shipment_line_id
2079 AND b.tax_id = c.tax_id
2080 AND upper(b.tax_type) IN ('EXCISE', 'ADDL. EXCISE', 'OTHER EXCISE', 'CVD',JAI_CONSTANTS.tax_type_add_cvd,
2081 jai_constants.tax_type_exc_edu_cess, jai_constants.tax_type_cvd_edu_cess,
2082 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
2083 )
2084 AND b.modvat_flag = 'Y';
2085
2086 v_already_rounded_chk NUMBER;
2087 v_excise_inv_rnd_cnt NUMBER;
2088
2089 CURSOR c_already_rounded_chk(p_source_header_id IN NUMBER, p_excise_invoice_no IN VARCHAR2,
2090 p_excise_invoice_date IN DATE, p_transaction_type IN VARCHAR2) IS
2091 SELECT max(rounding_id) rounding_id,
2092 count(1)
2093 FROM JAI_CMN_RG_ROUND_HDRS
2094 WHERE source_header_id = p_source_header_id
2095 AND excise_invoice_no = p_excise_invoice_no
2096 AND excise_invoice_date = p_excise_invoice_date
2097 AND src_transaction_type = p_transaction_type
2098 GROUP BY rounding_id, excise_invoice_no, excise_invoice_date;
2099
2100 /*Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
2101 ln_rtv_excise_batch_group_id jai_rcv_rtv_batch_trxs.excise_batch_group_id%TYPE;
2102 CURSOR c_excise_batch_group_id(cpn_transaction_id number) IS
2103 select excise_batch_group_id
2104 from jai_rcv_rtv_batch_trxs
2105 where transaction_id = cpn_transaction_id;
2106
2107 v_no_of_periods_updated NUMBER(15);
2108 v_period_balance_id NUMBER(15);
2109
2110 v_exc_inv_rnd_counter NUMBER ;--File.Sql.35 Cbabu := 0;
2111
2112 lv_process_status VARCHAR2(3);
2113 lv_process_message VARCHAR2(1996);
2114 v_receipt_num rcv_shipment_headers.receipt_num%TYPE;
2115 v_pla_register_id NUMBER;
2116
2117 lv_ttype_correct JAI_RCV_TRANSACTIONS.transaction_type%type ;
2118
2119 BEGIN
2120
2121 v_statement_no := '0.1';
2122 v_commit_interval := 50;
2123 v_rounding_precision := 0;
2124 v_acct_type := 'REGULAR';
2125 v_acct_nature := 'CENVAT-ROUNDING';
2126 v_source_name := 'Purchasing India';
2127 v_category_name := 'Receiving India';
2128 v_statement_no := '0';
2129 v_err_message := '';
2130 v_rounding_entries_made := 0;
2131 v_tot_errored_entries := 0;
2132 v_zero_round_found := 0;
2133 v_tot_processed_invoices := 0;
2134 v_no_of_invoices_posted := 0;
2135 v_save_point_set := FALSE;
2136 v_created_by := nvl(FND_GLOBAL.USER_ID, -1);
2137 v_last_update_login := nvl(FND_GLOBAL.LOGIN_ID,- 1);
2138 v_today := trunc(SYSDATE);
2139 v_line_type_c_cnt := 0;
2140 v_tot_lines_cnt := 0;
2141 v_exc_inv_rnd_counter := 0;
2142
2143 if gb_debug then
2144 fnd_file.put_line(fnd_file.log, '-1-Start of Procedure');
2145 end if;
2146
2147 OPEN c_rg_rounding_account(p_organization_id);
2148 FETCH c_rg_rounding_account INTO v_rg_rounding_account_id;
2149 CLOSE c_rg_rounding_account;
2150
2151 v_statement_no := '0.2';
2152
2153 IF v_rg_rounding_account_id IS NULL THEN
2154 fnd_file.put_line(fnd_file.log, 'ERROR: Rounding Account is not specified at Organization Level');
2155 RAISE_APPLICATION_ERROR( -20099, 'Rounding Account is not specified at Organization Level');
2156 END IF;
2157
2158 v_statement_no := '0.3';
2159
2160 OPEN c_active_fin_year(p_organization_id);
2161 FETCH c_active_fin_year INTO v_fin_year;
2162 CLOSE c_active_fin_year;
2163
2164 --Added by Sanjikum for Bug #4049363
2165 v_zero_record.basic := 0;
2166 v_zero_record.additional := 0;
2167 v_zero_record.additional_cvd := 0;/*5228046 Addtional cvd Enhancement*/
2168 v_zero_record.other := 0;
2169 v_zero_record.excise_edu_cess := 0;
2170 v_zero_record.cvd_edu_cess := 0;
2171 v_zero_record.total := 0;
2172
2173 v_tot_amount := v_zero_record;
2174
2175 if gb_debug then
2176 fnd_file.put_line(fnd_file.log, '0-Before MAIN LOOP');
2177 end if;
2178
2179 -- PART II A, C rounding entries can be posted into one of the Registers A or C
2180 lv_ttype_correct := 'CORRECT' ;
2181 FOR r IN (
2182 select
2183 LV_RG23_REGISTER register,
2184 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. b.shipment_header_id shipment_header_id,
2185 min(b.shipment_header_id) min_shipment_header_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2186 count( distinct shipment_header_id) cnt_shipment_header_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2187 a.excise_invoice_no excise_invoice_no,
2188 a.excise_invoice_date excise_invoice_date,
2189 a.vendor_id Vendor_id,/*bgowrava for forward porting bug#5674376*/
2190 a.vendor_site_id vendor_site_id,/*bgowrava for forward porting bug#5674376*/
2191 p_transaction_type transaction_type,
2192 max(to_number(a.RECEIPT_REF)) rcv_transaction_id,
2193 min(a.location_id) location_id,
2194 nvl(sum(a.cr_basic_ed), 0) cr_basic_ed,
2195 nvl(sum(a.cr_additional_ed), 0) cr_additional_ed,
2196 nvl(sum(a.cr_additional_cvd),0) cr_additional_cvd,/*5228046 Addtional cvd Enhancement*/
2197 nvl(sum(a.cr_other_ed), 0) cr_other_ed,
2198 nvl(sum(a.dr_basic_ed), 0) dr_basic_ed,
2199 nvl(sum(a.dr_additional_ed), 0) dr_additional_ed,
2200 nvl(sum(a.dr_additional_cvd),0) dr_additional_cvd,/*5228046 Addtional cvd Enhancement*/
2201 nvl(sum(a.dr_other_ed), 0) dr_other_ed,
2202 nvl(sum( decode(c.tax_type, 'EXCISE_EDUCATION_CESS', c.credit, 0)), 0) cr_exc_edu_cess,
2203 nvl(sum( decode(c.tax_type, 'CVD_EDUCATION_CESS', c.credit, 0)), 0) cr_cvd_edu_cess,
2204 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
2205 nvl(sum( decode(c.tax_type, jai_constants.tax_type_sh_cvd_edu_cess, c.credit, 0)), 0) cr_sh_cvd_edu_cess,
2206 nvl(sum( decode(c.tax_type, 'EXCISE_EDUCATION_CESS', c.debit, 0)), 0) dr_exc_edu_cess,
2207 nvl(sum( decode(c.tax_type, 'CVD_EDUCATION_CESS', c.debit, 0)), 0) dr_cvd_edu_cess,
2208 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
2209 nvl(sum( decode(c.tax_type, jai_constants.tax_type_sh_cvd_edu_cess, c.debit, 0)), 0) dr_sh_cvd_edu_cess,
2210 null transaction_date
2211 -- 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
2212 from JAI_CMN_RG_23AC_II_TRXS a, JAI_RCV_TRANSACTIONS b, JAI_CMN_RG_OTHERS c
2213 where a.RECEIPT_REF = b.transaction_id
2214 AND a.organization_id = p_organization_id
2215 AND c.source_type(+) = 1 -- this means register is JAI_CMN_RG_23AC_II_TRXS
2216 AND a.register_id = c.source_register_id(+)
2217 AND (
2218 (p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NULL)
2219 OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NULL
2220 AND a.excise_invoice_date >= p_ex_invoice_from_date)
2221 OR (p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NOT NULL
2222 AND a.excise_invoice_date <= p_ex_invoice_to_date)
2223 OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NOT NULL
2224 AND a.excise_invoice_date BETWEEN p_ex_invoice_from_date AND p_ex_invoice_to_date)
2225 )
2226 AND a.rounding_id IS NULL
2227 AND a.TRANSACTION_SOURCE_NUM = 18
2228 AND ( (b.transaction_type = p_transaction_type)
2229 or (b.transaction_type = lv_ttype_correct and b.parent_transaction_type = p_transaction_type --'CORRECT'
2230 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. exists (select 1 from JAI_RCV_TRANSACTIONS
2231 where transaction_id = b.parent_transaction_id
2232 and transaction_type = p_transaction_type)*/
2233 )
2234 )
2235 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. GROUP BY b.shipment_header_id, a.excise_invoice_no, a.excise_invoice_date
2236 GROUP BY a.excise_invoice_no, a.excise_invoice_date,
2237 a.vendor_id , /*bgowrava for forward porting bug#5674376*/
2238 a.vendor_site_id /*bgowrava for forward porting bug#5674376*/
2239 UNION ALL
2240 select
2241 LV_PLA_REGISTER register,
2242 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. b.shipment_header_id shipment_header_id,
2243 min(b.shipment_header_id) min_shipment_header_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2244 count( distinct shipment_header_id) cnt_shipment_header_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2245 a.DR_INVOICE_NO excise_invoice_no,
2246 a.dr_invoice_date excise_invoice_date,
2247 a.vendor_id Vendor_id,/*bgowrava for forward porting bug#5674376*/
2248 a.vendor_site_id vendor_site_id,/*bgowrava for forward porting bug#5674376*/
2249 p_transaction_type transaction_type,
2250 max(to_number(ref_document_id)) rcv_transaction_id,
2251 min(a.location_id) location_id,
2252 nvl(sum(a.cr_basic_ed),0) cr_basic_ed,
2253 nvl(sum(a.cr_additional_ed), 0) cr_additional_ed,
2254 to_number(null) cr_additional_cvd, /*5228046 Addtional cvd Enhancement*/
2255 nvl(sum(a.cr_other_ed), 0) cr_other_ed,
2256 nvl(sum(a.dr_basic_ed), 0) dr_basic_ed,
2257 nvl(sum(a.dr_additional_ed), 0) dr_additional_ed,
2258 to_number(null) dr_additional_cvd, /*5228046 Addtional cvd Enhancement*/
2259 nvl(sum(a.dr_other_ed), 0) dr_other_ed,
2260 nvl(sum( decode(c.tax_type, 'EXCISE_EDUCATION_CESS', c.credit, 0)), 0) cr_exc_edu_cess,
2261 nvl(sum( decode(c.tax_type, 'CVD_EDUCATION_CESS', c.credit, 0)), 0) cr_cvd_edu_cess,
2262 -- Date 16/04/2007 by sacsethi for Bug#5989740
2263 nvl(sum( decode(c.tax_type, 'EXCISE_SH_EDU_CESS', c.credit, 0)), 0) cr_sh_exc_edu_cess,
2264 nvl(sum( decode(c.tax_type, 'CVD_SH_EDU_CESS', c.credit, 0)), 0) cr_sh_cvd_edu_cess,
2265 -- end
2266 nvl(sum( decode(c.tax_type, 'EXCISE_EDUCATION_CESS', c.debit, 0)), 0) dr_exc_edu_cess,
2267 nvl(sum( decode(c.tax_type, 'CVD_EDUCATION_CESS', c.debit, 0)), 0) dr_cvd_edu_cess,
2268 -- Date 16/04/2007 by sacsethi for Bug#5989740
2269 nvl(sum( decode(c.tax_type, 'EXCISE_SH_EDU_CESS', c.debit, 0)), 0) dr_sh_exc_edu_cess,
2270 nvl(sum( decode(c.tax_type, 'CVD_SH_EDU_CESS', c.debit, 0)), 0) dr_sh_cvd_edu_cess,
2271 -- end
2272 max(a.transaction_date) transaction_date
2273 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. from JAI_CMN_RG_PLA_TRXS a, rcv_transactions b, JAI_CMN_RG_OTHERS c
2274 from JAI_CMN_RG_PLA_TRXS a, JAI_RCV_TRANSACTIONS b, JAI_CMN_RG_OTHERS c
2275 where a.ref_document_id = b.transaction_id
2276 AND a.organization_id = p_organization_id
2277 AND c.source_type(+) = 2 -- this means register is JAI_CMN_RG_PLA_TRXS
2278 AND a.register_id = c.source_register_id(+)
2279 AND (
2280 (p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NULL)
2281 OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NULL
2282 AND a.dr_invoice_date >= p_ex_invoice_from_date)
2283 OR (p_ex_invoice_from_date IS NULL AND p_ex_invoice_to_date IS NOT NULL
2284 AND a.dr_invoice_date <= p_ex_invoice_to_date)
2285 OR (p_ex_invoice_from_date IS NOT NULL AND p_ex_invoice_to_date IS NOT NULL
2286 AND a.dr_invoice_date BETWEEN p_ex_invoice_from_date AND p_ex_invoice_to_date)
2287 )
2288 AND a.rounding_id IS NULL
2289 AND a.TRANSACTION_SOURCE_NUM = 19
2290 AND ( (b.transaction_type = p_transaction_type)
2291 or (b.transaction_type = 'CORRECT' and b.parent_transaction_type = p_transaction_type
2292 /*Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. exists (select 1 from JAI_RCV_TRANSACTIONS
2293 where transaction_id = b.parent_transaction_id
2294 and transaction_type = p_transaction_type)*/
2295 )
2296 )
2297 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. GROUP BY b.shipment_header_id, a.DR_INVOICE_NO, a.dr_invoice_date
2298 GROUP BY a.DR_INVOICE_NO, a.dr_invoice_date,
2299 a.vendor_id , /*bgowrava for forward porting bug#5674376*/
2300 a.vendor_site_id /*bgowrava for forward porting bug#5674376*/
2301 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. ORDER BY shipment_header_id
2302 ORDER BY rcv_transaction_id
2303 )
2304 LOOP
2305
2306 BEGIN
2307
2308 if gb_debug then
2309 fnd_file.put_line(fnd_file.log, '0-Amts: crBas:'||r.cr_basic_ed
2310 ||', crAdd:'||r.cr_additional_ed||', crOth:'||r.cr_other_ed
2311 ||', crExcEdu:'||r.cr_exc_edu_cess||', crCvdEdu:'||r.cr_cvd_edu_cess
2312 ||', drBas:'||r.dr_basic_ed
2313 ||', drAdd:'||r.dr_additional_ed||', drOth:'||r.dr_other_ed
2314 ||', drExcEdu:'||r.dr_exc_edu_cess||', drCvdEdu:'||r.dr_cvd_edu_cess
2315 );
2316 end if;
2317
2318 v_statement_no := '1';
2319 v_excise_invoice_no := r.excise_invoice_no;
2320 v_excise_invoice_date := r.excise_invoice_date;
2321
2322 v_excise_inv_rnd_cnt := 0;
2323
2324 v_already_rounded_chk := null;
2325 v_period_balance_id := null;
2326 v_no_of_periods_updated := null;
2327
2328 /* Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
2329 ln_rtv_excise_batch_group_id := null;
2330 open c_excise_batch_group_id(r.rcv_transaction_id);
2331 fetch c_excise_batch_group_id into ln_rtv_excise_batch_group_id;
2332 close c_excise_batch_group_id;
2333
2334 v_statement_no := '1.1';
2335 v_already_rounded_chk := NULL;
2336 -- 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);
2337 OPEN c_already_rounded_chk(ln_rtv_excise_batch_group_id, v_excise_invoice_no,
2338 v_excise_invoice_date, r.transaction_type);
2339 FETCH c_already_rounded_chk INTO
2340 v_already_rounded_chk,
2341 v_excise_inv_rnd_cnt;
2342 CLOSE c_already_rounded_chk;
2343
2344 IF v_already_rounded_chk IS NOT NULL THEN
2345
2346 v_statement_no := '1.3';
2347 SAVEPOINT previous_savepoint;
2348 v_save_point_set := TRUE;
2349
2350 if gb_debug then
2351 fnd_file.put_line(fnd_file.log, '1-Before Update of Register');
2352 end if;
2353
2354 if r.register = LV_RG23_REGISTER then
2355 v_statement_no := '1.2';
2356 UPDATE JAI_CMN_RG_23AC_II_TRXS jcrg23ac
2357 SET jcrg23ac.rounding_id = v_already_rounded_chk
2358 WHERE jcrg23ac.excise_invoice_no = v_excise_invoice_no
2359 AND jcrg23ac.excise_invoice_date = v_excise_invoice_date
2360 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
2361 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
2362 AND jcrg23ac.organization_id = p_organization_id
2363 AND jcrg23ac.rounding_id IS NULL
2364 AND jcrg23ac.transaction_source_num = 18
2365 AND exists (select 1 from JAI_RCV_TRANSACTIONS jrt
2366 /* Bug 4930048. Added by Lakshmi Gopalsami
2367 Changed transaction_source_num to transaction_id
2368 */
2369 where jrt.transaction_id = jcrg23ac.receipt_ref
2370 and (jrt.transaction_type = p_transaction_type or
2371 jrt.parent_transaction_type = p_transaction_type) );
2372
2373 elsif r.register = LV_PLA_REGISTER then
2374
2375 v_statement_no := '1.2';
2376 UPDATE JAI_CMN_RG_PLA_TRXS jcpla
2377 SET jcpla.rounding_id = v_already_rounded_chk
2378 WHERE jcpla.DR_INVOICE_NO = v_excise_invoice_no
2379 AND jcpla.dr_invoice_date = v_excise_invoice_date
2380 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
2381 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
2382 AND jcpla.organization_id = p_organization_id
2383 AND jcpla.rounding_id IS NULL
2384 AND jcpla.TRANSACTION_SOURCE_NUM = 19
2385 AND exists (select 1 from JAI_RCV_TRANSACTIONS jrt
2386 /* Bug 4930048. Added by Lakshmi Gopalsami
2387 Changed transaction_source_num to transaction_id
2388 */
2389 where jrt.transaction_id = jcpla.ref_document_id
2390 and (jrt.transaction_type = p_transaction_type or
2391 jrt.parent_transaction_type = p_transaction_type) );
2392 end if;
2393
2394 fnd_file.put_line(fnd_file.log, '+++++ Ex. Invoice Already rounded +++++');
2395 GOTO next_exc_inv;
2396
2397 END IF;
2398
2399 v_tot_processed_invoices := v_tot_processed_invoices + 1;
2400
2401 v_statement_no := '1.4';
2402 SELECT JAI_CMN_RG_ROUND_HDRS_S.nextval INTO v_rounding_id FROM dual;
2403
2404 v_line_type_c_cnt := 0;
2405 v_tot_lines_cnt := 0;
2406 v_tot_amount := v_zero_record;
2407
2408 v_statement_no := '1.4';
2409 FOR line IN ( SELECT a.shipment_line_id, a.transaction_id, a.item_class,
2410 a.transaction_type, a.parent_transaction_type, a.quantity
2411 FROM JAI_RCV_TRANSACTIONS a,
2412 JAI_RCV_CENVAT_CLAIMS b,/*bgowrava for forward porting bug#5674376*/
2413 RCV_TRANSACTIONS c,/*bgowrava for forward porting bug#5674376*/
2414 jai_rcv_rtv_batch_trxs d
2415 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE a.shipment_header_id = v_shipment_header_id
2416 WHERE a.transaction_id = d.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2417 and d.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2418 AND (a.transaction_type = p_transaction_type
2419 or (a.transaction_type = 'CORRECT' and a.parent_transaction_type = p_transaction_type))
2420 AND (
2421 (nvl(b.vendor_id,-999) = nvl(r.vendor_id,-999)
2422 AND nvl(b.vendor_site_id,-999) = nvl(r.vendor_site_id,-999)
2423 AND b.vendor_changed_flag = 'Y' )
2424 OR
2425 (nvl(c.vendor_id,-999) = nvl(r.vendor_id,-999)
2426 AND nvl(c.vendor_site_id,-999) = nvl(r.vendor_site_id,-999)
2427 AND b.vendor_changed_flag = 'N' )
2428 ) /*bgowrava for forward porting bug#5674376*/
2429 AND a.excise_invoice_no = v_excise_invoice_no
2430 AND a.excise_invoice_date = v_excise_invoice_date
2431 )
2432 LOOP
2433
2434 ln_receive_amount := v_zero_record;
2435
2436 ln_receive_qty := 0;
2437
2438 OPEN c_ja_in_receive_qty(line.shipment_line_id);
2439 FETCH c_ja_in_receive_qty INTO ln_receive_qty;
2440 CLOSE c_ja_in_receive_qty;
2441
2442 OPEN c_receipt_tax_amount(line.shipment_line_id);
2443 FETCH c_receipt_tax_amount INTO
2444 ln_receive_amount.basic,
2445 ln_receive_amount.additional,
2446 ln_receive_amount.additional_cvd,/*5228046 Addtional cvd Enhancement*/
2447 ln_receive_amount.other,
2448 ln_receive_amount.excise_edu_cess,
2449 ln_receive_amount.cvd_edu_cess,
2450 ln_receive_amount.sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
2451 ln_receive_amount.sh_cvd_edu_cess ;
2452 CLOSE c_receipt_tax_amount;
2453
2454 fnd_file.put_line(fnd_file.log, 'TrxId:'||line.transaction_id ||', RecvQty->'||ln_receive_qty);
2455
2456 ln_cenvat_amount.basic := ln_receive_amount.basic * line.quantity/ln_receive_qty;
2457 ln_cenvat_amount.additional := ln_receive_amount.additional * line.quantity/ln_receive_qty;
2458 ln_cenvat_amount.additional_cvd := nvl(ln_receive_amount.additional_cvd,0) * line.quantity/ln_receive_qty;/*5228046 Addtional cvd Enhancement*/
2459 ln_cenvat_amount.other := ln_receive_amount.other * line.quantity/ln_receive_qty;
2460 ln_cenvat_amount.excise_edu_cess := ln_receive_amount.excise_edu_cess * line.quantity/ln_receive_qty;
2461 ln_cenvat_amount.cvd_edu_cess := ln_receive_amount.cvd_edu_cess * line.quantity/ln_receive_qty;
2462 -- Date 16/04/2007 by sacsethi for Bug#5989740
2463 ln_cenvat_amount.sh_excise_edu_cess := nvl(ln_receive_amount.sh_excise_edu_cess,0) * line.quantity/ln_receive_qty;
2464 ln_cenvat_amount.sh_cvd_edu_cess := nvl(ln_receive_amount.sh_cvd_edu_cess,0) * line.quantity/ln_receive_qty;
2465 -- end 5989740
2466
2467 ln_cenvat_amount.total :=
2468 NVL(ln_cenvat_amount.basic,0)
2469 + NVL(ln_cenvat_amount.additional,0)
2470 + NVL(ln_cenvat_amount.additional_cvd,0)/*5228046 Addtional cvd Enhancement*/
2471 + NVL(ln_cenvat_amount.other,0)
2472 + NVL(ln_cenvat_amount.excise_edu_cess,0)
2473 + NVL(ln_cenvat_amount.cvd_edu_cess,0)
2474 + NVL(ln_cenvat_amount.sh_excise_edu_cess,0)-- Date 16/04/2007 by sacsethi for Bug#5989740
2475 + NVL(ln_cenvat_amount.sh_cvd_edu_cess,0) ;
2476
2477 v_statement_no := '1.5';
2478
2479 if gb_debug then
2480 fnd_file.put_line(fnd_file.log, '2-Before Insert into Entry Lines');
2481 end if;
2482
2483 -- populate item class
2484 INSERT INTO JAI_CMN_RG_ROUND_LINES (ROUNDING_LINE_ID,
2485 ROUNDING_ID, SRC_LINE_ID, SRC_TRANSACTION_ID,
2486 EXCISE_AMT,
2487 BASIC_ED, ADDITIONAL_ED,ADDITIONAL_CVD , OTHER_ED,
2488 EXCISE_EDU_CESS, CVD_EDU_CESS,
2489 sh_excise_edu_cess, sh_cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
2490 ITEM_CLASS, CREATION_DATE, CREATED_BY,
2491 program_application_id, program_id, program_login_id, request_id
2492 ) VALUES ( JAI_CMN_RG_ROUND_LINES_S.nextval,
2493 v_rounding_id, line.shipment_line_id, line.transaction_id,
2494 ln_cenvat_amount.total,
2495 ln_cenvat_amount.basic,
2496 ln_cenvat_amount.additional,
2497 ln_cenvat_amount.additional_cvd,/*5228046 Addtional cvd Enhancement*/
2498 ln_cenvat_amount.other,
2499 ln_cenvat_amount.excise_edu_cess, ln_cenvat_amount.cvd_edu_cess,
2500 ln_cenvat_amount.sh_excise_edu_cess, ln_cenvat_amount.sh_cvd_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
2501 line.item_class, SYSDATE, v_created_by,
2502 fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'), fnd_profile.value('CONC_REQUEST_ID')
2503 );
2504
2505 v_tot_amount.basic := v_tot_amount.basic + ln_cenvat_amount.basic;
2506 v_tot_amount.additional := v_tot_amount.additional + ln_cenvat_amount.additional;
2507 v_tot_amount.additional_cvd := v_tot_amount.additional_cvd + nvl(ln_cenvat_amount.additional_cvd,0);/*Added by SACSETHI for the bug 5228046
2508 -Additional CVD Enhancement*/
2509
2510 v_tot_amount.other := v_tot_amount.other + ln_cenvat_amount.other;
2511 v_tot_amount.excise_edu_cess := v_tot_amount.excise_edu_cess + ln_cenvat_amount.excise_edu_cess;
2512 v_tot_amount.cvd_edu_cess := v_tot_amount.cvd_edu_cess + ln_cenvat_amount.cvd_edu_cess;
2513
2514 -- Date 16/04/2007 by sacsethi for Bug#5989740
2515 v_tot_amount.sh_excise_edu_cess := v_tot_amount.sh_excise_edu_cess + ln_cenvat_amount.sh_excise_edu_cess;
2516 v_tot_amount.sh_cvd_edu_cess := v_tot_amount.sh_cvd_edu_cess + ln_cenvat_amount.sh_cvd_edu_cess;
2517 -- end 5989740
2518
2519 v_tot_amount.total := NVL(v_tot_amount.basic,0) +
2520 NVL(v_tot_amount.additional,0) +
2521 NVL(v_tot_amount.additional_cvd,0) + /*5228046 Additional cvd Enhancement*/
2522 NVL(v_tot_amount.other,0) +
2523 NVL(v_tot_amount.excise_edu_cess,0) +
2524 NVL(v_tot_amount.cvd_edu_cess,0) +
2525 NVL(v_tot_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2526 NVL(v_tot_amount.sh_cvd_edu_cess,0);
2527
2528
2529 if line.transaction_type = p_transaction_type or line.parent_transaction_type = p_transaction_type then
2530 IF line.item_class IN ('CGIN', 'CGEX') THEN
2531 v_line_type_c_cnt := v_line_type_c_cnt + 1;
2532 END IF;
2533 v_tot_lines_cnt := v_tot_lines_cnt + 1;
2534 end if;
2535
2536 END LOOP;
2537
2538 v_statement_no := '1.6';
2539 IF v_line_type_c_cnt = v_tot_lines_cnt THEN
2540 v_register_type := 'C';
2541 ELSIF v_line_type_c_cnt = 0 THEN
2542 v_register_type := 'A';
2543 ELSE
2544 v_register_type := p_register_type;
2545 END IF;
2546
2547 v_tot_amount.basic := -r.cr_basic_ed + r.dr_basic_ed;
2548 v_tot_amount.additional := -r.cr_additional_ed + r.dr_additional_ed;
2549 v_tot_amount.additional_cvd := -r.cr_additional_cvd + r.dr_additional_cvd;/*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
2550 v_tot_amount.other := -r.cr_other_ed + r.dr_other_ed;
2551 v_tot_amount.excise_edu_cess := -r.cr_exc_edu_cess + r.dr_exc_edu_cess;
2552 v_tot_amount.cvd_edu_cess := -r.cr_cvd_edu_cess + r.dr_cvd_edu_cess;
2553
2554 -- Date 16/04/2007 by sacsethi for Bug#5989740
2555 -- start
2556 v_tot_amount.sh_excise_edu_cess := -r.cr_sh_exc_edu_cess + r.dr_sh_exc_edu_cess;
2557 v_tot_amount.sh_cvd_edu_cess := -r.cr_sh_cvd_edu_cess + r.dr_sh_cvd_edu_cess;
2558 -- end
2559
2560 v_tot_amount.total := NVL(v_tot_amount.basic,0) +
2561 NVL(v_tot_amount.additional,0) +
2562 NVL(v_tot_amount.additional_cvd,0) + /*Added by SACSETHI for the bug 5228046 -Additional CVD Enhancement*/
2563 NVL(v_tot_amount.other,0) +
2564 NVL(v_tot_amount.excise_edu_cess,0) +
2565 NVL(v_tot_amount.cvd_edu_cess,0)+
2566 NVL(v_tot_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2567 NVL(v_tot_amount.sh_cvd_edu_cess,0);
2568 if gb_debug then
2569 fnd_file.put_line(fnd_file.log, '2.1-Amts: tBas:'||v_tot_amount.basic
2570 ||', tAdd:'||v_tot_amount.additional||', tOth:'||v_tot_amount.other
2571 ||', tAdditional_cvd:'||v_tot_amount.additional_cvd /*5228046 Addtional cvd Enhancement*/
2572 ||', tExcEdu:'||v_tot_amount.excise_edu_cess||', tCvdEdu:'||v_tot_amount.cvd_edu_cess
2573 ||', tAmt:'||v_tot_amount.total
2574 );
2575 end if;
2576
2577 fnd_file.put_line(fnd_file.log, 'Started Processing rtvExcBtchGrpId->'||ln_rtv_excise_batch_group_id
2578 ||', excise_invoice_no->'||v_excise_invoice_no
2579 ||', excise_invoice_date->'||v_excise_invoice_date
2580 );
2581
2582 v_statement_no := '2';
2583
2584 v_tot_rounded_amount.basic := ROUND(v_tot_amount.basic, v_rounding_precision);
2585 v_rounded_amount.basic := v_tot_rounded_amount.basic - v_tot_amount.basic;
2586
2587 v_tot_rounded_amount.additional := ROUND(v_tot_amount.additional, v_rounding_precision);
2588 v_rounded_amount.additional := v_tot_rounded_amount.additional - v_tot_amount.additional;
2589
2590 /*5228046 Addtional cvd Enhancement*/
2591 v_tot_rounded_amount.additional_cvd := ROUND(v_tot_amount.additional_cvd, v_rounding_precision);
2592 v_rounded_amount.additional_cvd := v_tot_rounded_amount.additional_cvd - v_tot_amount.additional_cvd;
2593
2594
2595 v_tot_rounded_amount.other := ROUND(v_tot_amount.other, v_rounding_precision);
2596 v_rounded_amount.other := v_tot_rounded_amount.other - v_tot_amount.other;
2597
2598 v_tot_rounded_amount.excise_edu_cess := ROUND(v_tot_amount.excise_edu_cess, v_rounding_precision);
2599 v_rounded_amount.excise_edu_cess := v_tot_rounded_amount.excise_edu_cess - v_tot_amount.excise_edu_cess;
2600
2601 v_tot_rounded_amount.cvd_edu_cess := ROUND(v_tot_amount.cvd_edu_cess, v_rounding_precision);
2602 v_rounded_amount.cvd_edu_cess := v_tot_rounded_amount.cvd_edu_cess - v_tot_amount.cvd_edu_cess;
2603
2604 -- Date 16/04/2007 by sacsethi for Bug#5989740
2605 -- start 5989740
2606
2607 v_tot_rounded_amount.sh_excise_edu_cess := ROUND(v_tot_amount.sh_excise_edu_cess, v_rounding_precision);
2608 v_rounded_amount.sh_excise_edu_cess := v_tot_rounded_amount.sh_excise_edu_cess - v_tot_amount.sh_excise_edu_cess;
2609
2610 v_tot_rounded_amount.sh_cvd_edu_cess := ROUND(v_tot_amount.sh_cvd_edu_cess, v_rounding_precision);
2611 v_rounded_amount.sh_cvd_edu_cess := v_tot_rounded_amount.sh_cvd_edu_cess - v_tot_amount.sh_cvd_edu_cess;
2612
2613 -- end 5989740
2614
2615 v_tot_rounded_amount.total :=
2616 NVL(v_tot_rounded_amount.basic,0)
2617 + NVL(v_tot_rounded_amount.additional,0)
2618 + NVL(v_tot_rounded_amount.additional_cvd,0) /*5228046 Additional cvd Enhancement*/
2619 + NVL(v_tot_rounded_amount.other,0)
2620 + NVL(v_tot_rounded_amount.excise_edu_cess,0)
2621 + NVL(v_tot_rounded_amount.cvd_edu_cess,0)
2622 + NVL(v_tot_rounded_amount.sh_excise_edu_cess,0) -- Date 16/04/2007 by sacsethi for Bug#5989740
2623 + NVL(v_tot_rounded_amount.sh_cvd_edu_cess,0);
2624
2625 v_rounded_amount.total :=
2626 NVL(v_rounded_amount.basic,0)
2627 + NVL(v_rounded_amount.additional,0)
2628 + NVL(v_rounded_amount.additional_cvd,0) /*5228046 Additional cvd Enhancement*/
2629 + NVL(v_rounded_amount.other,0)
2630 + NVL(v_rounded_amount.excise_edu_cess,0)
2631 + NVL(v_rounded_amount.cvd_edu_cess,0)
2632 + NVL(v_rounded_amount.sh_excise_edu_cess,0) -- Date 16/04/2007 by sacsethi for Bug#5989740
2633 + NVL(v_rounded_amount.sh_cvd_edu_cess,0);
2634
2635 v_rounded_amount_rg23 :=
2636 NVL(v_rounded_amount.basic,0)
2637 + NVL(v_rounded_amount.additional,0)
2638 + NVL(v_rounded_amount.additional_cvd,0)/*5228046 Additional cvd Enhancement*/
2639 + NVL(v_rounded_amount.other,0);
2640
2641 if gb_debug then
2642 fnd_file.put_line(fnd_file.log, '2.2-Amts: rBas:'||v_rounded_amount.basic
2643 ||', rAdd:'||v_rounded_amount.additional||', rOth:'||v_rounded_amount.other
2644 ||', rAdd_cvd:'||v_rounded_amount.additional_cvd /*5228046 Additional cvd Enhancement*/
2645 ||', rExcEdu:'||v_rounded_amount.excise_edu_cess||', rCvdEdu:'||v_rounded_amount.cvd_edu_cess
2646 );
2647 end if;
2648
2649 v_rounded_amount_abs :=
2650 ABS(NVL(v_rounded_amount.basic,0)) +
2651 ABS(NVL(v_rounded_amount.additional,0)) +
2652 ABS(NVL(v_rounded_amount.additional_cvd,0)) + /*5228046 Additional cvd Enhancement*/
2653 ABS(NVL(v_rounded_amount.other,0)) +
2654 ABS(NVL(v_rounded_amount.excise_edu_cess,0)) +
2655 ABS(NVL(v_rounded_amount.cvd_edu_cess,0)) +
2656 ABS(NVL(v_rounded_amount.sh_excise_edu_cess,0)) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2657 ABS(NVL(v_rounded_amount.sh_cvd_edu_cess,0));
2658
2659 v_statement_no := '3';
2660
2661 /* Punching Rounding_Id as 0 for RG transactions where in no Rounding is Required */
2662 IF v_rounded_amount_abs = 0 THEN
2663 v_zero_round_found := v_zero_round_found + 1;
2664
2665 if gb_debug then
2666 fnd_file.put_line(fnd_file.log, '3-Before Update of Register with 0 Rounding');
2667 end if;
2668
2669 if r.register = LV_RG23_REGISTER then
2670 UPDATE JAI_CMN_RG_23AC_II_TRXS aa
2671 SET rounding_id = 0
2672 WHERE organization_id = p_organization_id
2673 AND excise_invoice_no = v_excise_invoice_no
2674 AND excise_invoice_date = v_excise_invoice_date
2675 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
2676 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
2677 AND rounding_id IS NULL
2678 AND TRANSACTION_SOURCE_NUM = 18
2679 AND EXISTS (
2680 SELECT 1 FROM JAI_RCV_TRANSACTIONS bb, jai_rcv_rtv_batch_trxs b1
2681 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE BB.shipment_header_id = v_shipment_header_id
2682 WHERE bb.transaction_id = b1.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2683 and b1.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2684 AND bb.transaction_id = aa.receipt_ref
2685 AND ( bb.transaction_type = r.transaction_type or bb.parent_transaction_type = r.transaction_type)
2686 );
2687
2688 elsif r.register = LV_PLA_REGISTER then
2689
2690 UPDATE JAI_CMN_RG_PLA_TRXS aa
2691 SET rounding_id = 0
2692 WHERE organization_id = p_organization_id
2693 AND DR_INVOICE_NO = v_excise_invoice_no
2694 AND dr_invoice_date = v_excise_invoice_date
2695 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
2696 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
2697 AND rounding_id IS NULL
2698 AND TRANSACTION_SOURCE_NUM = 19
2699 AND EXISTS (
2700 SELECT 1 FROM JAI_RCV_TRANSACTIONS bb, jai_rcv_rtv_batch_trxs b1
2701 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE BB.shipment_header_id = v_shipment_header_id
2702 WHERE bb.transaction_id = b1.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2703 and b1.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2704 AND bb.transaction_id = aa.ref_document_id
2705 AND ( bb.transaction_type = r.transaction_type or bb.parent_transaction_type = r.transaction_type)
2706 );
2707
2708 end if;
2709
2710 DELETE FROM JAI_CMN_RG_ROUND_LINES WHERE rounding_id = v_rounding_id;
2711
2712 fnd_file.put_line(fnd_file.log, '**** Zero Rounded ****');
2713 GOTO next_exc_inv;
2714
2715 elsif v_rounded_amount.total < 0 then
2716 v_rounding_entry_type := 'CR';
2717 v_rounded_cr_amount := ABS(NVL(v_rounded_amount.basic,0) +
2718 NVL(v_rounded_amount.additional,0) +
2719 NVL(v_rounded_amount.additional_cvd,0) + /*5228046 Additional cvd Enhancement*/
2720 NVL(v_rounded_amount.other,0) +
2721 NVL(v_rounded_amount.excise_edu_cess,0) +
2722 NVL(v_rounded_amount.cvd_edu_cess,0) +
2723 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2724 NVL(v_rounded_amount.sh_cvd_edu_cess,0));
2725
2726 ELSIF v_rounded_amount.total > 0 THEN
2727 v_rounding_entry_type := 'DR';
2728 v_rounded_dr_amount := ABS(NVL(v_rounded_amount.basic,0) +
2729 NVL(v_rounded_amount.additional,0) +
2730 NVL(v_rounded_amount.additional_cvd,0) +/*5228046 Additional cvd Enhancement*/
2731 NVL(v_rounded_amount.other,0) +
2732 NVL(v_rounded_amount.excise_edu_cess,0) +
2733 NVL(v_rounded_amount.cvd_edu_cess,0) +
2734 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2735 NVL(v_rounded_amount.sh_cvd_edu_cess,0));
2736 END IF;
2737
2738 IF NVL(v_rounded_amount.basic,0) > 0 THEN
2739 v_rounded_dr_rg23_amount.basic := ABS(v_rounded_amount.basic);
2740 ELSE
2741 v_rounded_cr_rg23_amount.basic := ABS(v_rounded_amount.basic);
2742 END IF;
2743
2744 IF NVL(v_rounded_amount.additional,0) > 0 THEN
2745 v_rounded_dr_rg23_amount.additional := ABS(v_rounded_amount.additional);
2746 ELSE
2747 v_rounded_cr_rg23_amount.additional := ABS(v_rounded_amount.additional);
2748 END IF;
2749
2750 /*5228046 Additional cvd Enhancement*/
2751 IF NVL(v_rounded_amount.additional_cvd,0) > 0 THEN
2752 v_rounded_dr_rg23_amount.additional_cvd := ABS(v_rounded_amount.additional_cvd);
2753 ELSE
2754 v_rounded_cr_rg23_amount.additional_cvd := ABS(v_rounded_amount.additional_cvd);
2755 END IF;
2756
2757
2758 IF NVL(v_rounded_amount.other,0) > 0 THEN
2759 v_rounded_dr_rg23_amount.other := ABS(v_rounded_amount.other);
2760 ELSE
2761 v_rounded_cr_rg23_amount.other := ABS(v_rounded_amount.other);
2762 END IF;
2763
2764 IF (NVL(v_rounded_amount.excise_edu_cess,0) +
2765 NVL(v_rounded_amount.cvd_edu_cess,0)+
2766 NVL(v_rounded_amount.sh_excise_edu_cess,0) +
2767 NVL(v_rounded_amount.sh_cvd_edu_cess,0)) > 0
2768 THEN
2769 v_rounded_dr_oth_amount.total := ABS(NVL(v_rounded_amount.excise_edu_cess,0) +
2770 NVL(v_rounded_amount.cvd_edu_cess,0) +
2771 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2772 NVL(v_rounded_amount.sh_cvd_edu_cess,0)
2773 );
2774
2775 ELSE
2776 v_rounded_cr_oth_amount.total := ABS(NVL(v_rounded_amount.excise_edu_cess,0) +
2777 NVL(v_rounded_amount.cvd_edu_cess,0) +
2778 NVL(v_rounded_amount.sh_excise_edu_cess,0) + -- Date 16/04/2007 by sacsethi for Bug#5989740
2779 NVL(v_rounded_amount.sh_cvd_edu_cess,0)
2780 );
2781 END IF;
2782
2783 IF NVL(v_rounded_amount.excise_edu_cess,0) > 0 THEN
2784 v_rounded_dr_oth_amount.excise_edu_cess := ABS(v_rounded_amount.excise_edu_cess);
2785 ELSE
2786 v_rounded_cr_oth_amount.excise_edu_cess := ABS(v_rounded_amount.excise_edu_cess);
2787 END IF;
2788
2789 IF NVL(v_rounded_amount.cvd_edu_cess,0) > 0 THEN
2790 v_rounded_dr_oth_amount.cvd_edu_cess := ABS(v_rounded_amount.cvd_edu_cess);
2791 ELSE
2792 v_rounded_cr_oth_amount.cvd_edu_cess := ABS(v_rounded_amount.cvd_edu_cess);
2793 END IF;
2794
2795 -- Date 16/04/2007 by sacsethi for Bug#5989740
2796 -- start 5989740
2797 IF NVL(v_rounded_amount.sh_excise_edu_cess,0) > 0 THEN
2798 v_rounded_dr_oth_amount.sh_excise_edu_cess := ABS(v_rounded_amount.sh_excise_edu_cess);
2799 ELSE
2800 v_rounded_cr_oth_amount.sh_excise_edu_cess := ABS(v_rounded_amount.sh_excise_edu_cess);
2801 END IF;
2802
2803 IF NVL(v_rounded_amount.sh_cvd_edu_cess,0) > 0 THEN
2804 v_rounded_dr_oth_amount.sh_cvd_edu_cess := ABS(v_rounded_amount.sh_cvd_edu_cess);
2805 ELSE
2806 v_rounded_cr_oth_amount.sh_cvd_edu_cess := ABS(v_rounded_amount.sh_cvd_edu_cess);
2807 END IF;
2808 -- end 5989740
2809
2810 v_statement_no := '4';
2811
2812 OPEN c_rg_modvat_account(p_organization_id, r.location_id);
2813 FETCH c_rg_modvat_account INTO v_modvat_rm_account_id, v_modvat_cg_account_id, v_modvat_pla_account_id;
2814 CLOSE c_rg_modvat_account;
2815
2816 v_statement_no := '4.1';
2817 if r.register = LV_PLA_REGISTER then
2818 v_rg_account_id := v_modvat_pla_account_id;
2819
2820 elsif v_register_type = 'A' THEN
2821 v_rg_account_id := v_modvat_rm_account_id;
2822
2823 elsif v_register_type = 'C' THEN
2824 v_rg_account_id := v_modvat_cg_account_id;
2825 end if;
2826
2827 -- Required Accounts are not specified at the location where RG23 PART II entry will be posted. So, through out an error
2828 if v_rg_account_id IS NULL then
2829 if r.register = LV_PLA_REGISTER then
2830 v_err_message := 'ERROR: PLA Account is not specified for Location:'||r.location_id;
2831 elsif v_register_type = 'A' THEN
2832 v_err_message := 'ERROR: Cenvat RMIN Account is not specified for Location:'||r.location_id;
2833 else
2834 v_err_message := 'ERROR: Cenvat CGIN Account is not specified for Location:'||r.location_id;
2835 end if;
2836
2837 fnd_file.put_line(fnd_file.log, v_err_message);
2838 RAISE_APPLICATION_ERROR( -20099, v_err_message);
2839 end if;
2840
2841 v_statement_no := '5';
2842
2843 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. OPEN c_vendor(v_shipment_header_id);
2844 OPEN c_vendor(r.min_shipment_header_id);
2845 FETCH c_vendor INTO v_vendor_id, v_vendor_site_id, v_receipt_num;
2846 CLOSE c_vendor;
2847
2848 /*Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.*/
2849 if r.cnt_shipment_header_id > 1 then
2850 v_receipt_num := null;
2851 end if;
2852
2853 v_statement_no := '7';
2854
2855 if r.register = LV_RG23_REGISTER then
2856 OPEN c_slno_balance_rg23p2(p_organization_id, r.location_id, v_fin_year, v_register_type);
2857 FETCH c_slno_balance_rg23p2 INTO v_slno, v_balance;
2858 CLOSE c_slno_balance_rg23p2;
2859 v_rg23_balance := nvl(v_balance,0);
2860
2861 v_statement_no := '8';
2862
2863 IF v_slno IS NULL or v_slno = 0 THEN
2864 v_slno := 1;
2865 ELSE
2866 v_slno := v_slno + 1;
2867 END IF;
2868 end if;
2869
2870 v_statement_no := '8.1';
2871
2872 if gb_debug then
2873 fnd_file.put_line(fnd_file.log, '4-Before Insert of Rounding Entry into Register:'||r.register );
2874 end if;
2875
2876 if r.register = LV_PLA_REGISTER then
2877
2878 jai_cmn_rg_pla_trxs_pkg.insert_row(
2879 p_register_id => v_register_id_part_ii,
2880 p_tr6_challan_no => NULL,
2881 p_tr6_challan_date => NULL,
2882 p_cr_basic_ed => v_rounded_cr_rg23_amount.basic,
2883 p_cr_additional_ed => v_rounded_cr_rg23_amount.additional,
2884 p_cr_other_ed => v_rounded_cr_rg23_amount.other,
2885 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. p_ref_document_id => r.shipment_header_id,
2886 p_ref_document_id => ln_rtv_excise_batch_group_id, -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
2887 p_ref_document_date => trunc(sysdate), -- CHK
2888 p_dr_invoice_id => r.excise_invoice_no,
2889 p_dr_invoice_date => r.excise_invoice_date,
2890 p_dr_basic_ed => v_rounded_dr_rg23_amount.basic,
2891 p_dr_additional_ed => v_rounded_dr_rg23_amount.additional,
2892 p_dr_other_ed => v_rounded_dr_rg23_amount.other,
2893 p_organization_id => p_organization_id,
2894 p_location_id => r.location_id,
2895 p_bank_branch_id => NULL,
2896 p_entry_date => NULL,
2897 p_inventory_item_id => 0,
2898 p_vendor_cust_flag => 'V',
2899 p_vendor_id => v_vendor_id,
2900 p_vendor_site_id => v_vendor_site_id,
2901 p_excise_invoice_no => r.excise_invoice_no,
2902 p_remarks => 'Rounding Entry for Receipt No:'||v_receipt_num,
2903 p_transaction_date => r.transaction_date,
2904 p_charge_account_id => NULL,
2905 p_other_tax_credit => v_rounded_cr_oth_amount.total,
2906 p_other_tax_debit => v_rounded_dr_oth_amount.total,
2907 p_transaction_type => 'RETURN TO VENDOR',
2908 p_transaction_source => null,
2909 p_called_from => 'rg_rounding_pkg.do_rtv_rounding',
2910 p_simulate_flag => 'N',
2911 p_process_status => lv_process_status,
2912 p_process_message => lv_process_message,
2913 p_rounding_id => -1
2914 );
2915
2916 if lv_process_status = 'E' then
2917 raise_application_error( -20010, lv_process_message, true);
2918 end if;
2919
2920 elsif r.register = LV_RG23_REGISTER then
2921 INSERT INTO JAI_CMN_RG_23AC_II_TRXS(
2922 REGISTER_ID, ORGANIZATION_ID, LOCATION_ID, FIN_YEAR, INVENTORY_ITEM_ID, SLNO,
2923 CR_BASIC_ED, DR_BASIC_ED,
2924 CR_ADDITIONAL_ED, DR_ADDITIONAL_ED,
2925 CR_ADDITIONAL_CVD, DR_ADDITIONAL_CVD,/*5228046 ADDITIONAL CVD ENHANCEMENT*/
2926 CR_OTHER_ED, DR_OTHER_ED,
2927 ROUNDING_ID, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
2928 TRANSACTION_SOURCE_NUM, RECEIPT_REF, REGISTER_TYPE, REMARKS,
2929 VENDOR_ID, VENDOR_SITE_ID, CUSTOMER_ID, CUSTOMER_SITE_ID,
2930 OPENING_BALANCE, CLOSING_BALANCE, CHARGE_ACCOUNT_ID,
2931 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
2932 , TRANSACTION_DATE, OTHER_TAX_CREDIT, OTHER_TAX_DEBIT
2933 ) VALUES (
2934 JAI_CMN_RG_23AC_II_TRXS_S.nextval, p_organization_id, r.location_id , v_fin_year, 0, v_slno,
2935 v_rounded_cr_rg23_amount.basic, v_rounded_dr_rg23_amount.basic,
2936 v_rounded_cr_rg23_amount.additional, v_rounded_dr_rg23_amount.additional,
2937 v_rounded_cr_rg23_amount.additional_cvd, v_rounded_dr_rg23_amount.additional_cvd,/*5228046 Additional cvd Enhancement*/
2938 v_rounded_cr_rg23_amount.other, v_rounded_dr_rg23_amount.other,
2939 -1, r.excise_invoice_no, r.excise_invoice_date,
2940 -- 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,
2941 18, ln_rtv_excise_batch_group_id, v_register_type, 'Rounding Entry for Receipt No:'||v_receipt_num,
2942 v_vendor_id, v_vendor_site_id, NULL, NULL,
2943 v_rg23_balance, v_rg23_balance + v_rounded_amount_rg23, v_rg_account_id,
2944 SYSDATE, v_created_by, SYSDATE, v_created_by, v_last_update_login
2945 , r.transaction_date, v_rounded_cr_oth_amount.total, v_rounded_dr_oth_amount.total
2946
2947 ) RETURNING register_id INTO v_register_id_part_ii;
2948 end if;
2949
2950 DECLARE
2951 v_tax_type JAI_CMN_RG_OTHERS.tax_type%TYPE;
2952 v_dr_amt JAI_CMN_RG_OTHERS.debit%TYPE;
2953 v_cr_amt JAI_CMN_RG_OTHERS.credit%TYPE;
2954 BEGIN
2955 FOR I in 1..4 LOOP -- Date 16/04/2007 by sacsethi for Bug#5989740 changed the loop counter from 2 to 4
2956
2957 if i = 1 then
2958 v_tax_type := jai_constants.tax_type_exc_edu_cess;
2959 v_dr_amt := v_rounded_dr_oth_amount.excise_edu_cess;
2960 v_cr_amt := v_rounded_cr_oth_amount.excise_edu_cess;
2961 elsif i = 2 then
2962 v_tax_type := jai_constants.tax_type_cvd_edu_cess;
2963 v_dr_amt := v_rounded_dr_oth_amount.cvd_edu_cess;
2964 v_cr_amt := v_rounded_cr_oth_amount.cvd_edu_cess;
2965 -- Date 16/04/2007 by sacsethi for Bug#5989740
2966 -- start 5989740
2967 elsif i = 3 then
2968 v_tax_type := jai_constants.tax_type_sh_exc_edu_cess;
2969 v_dr_amt := v_rounded_dr_oth_amount.sh_excise_edu_cess;
2970 v_cr_amt := v_rounded_cr_oth_amount.sh_excise_edu_cess;
2971 elsif i = 4 then
2972 v_tax_type := jai_constants.tax_type_sh_cvd_edu_cess;
2973 v_dr_amt := v_rounded_dr_oth_amount.sh_cvd_edu_cess;
2974 v_cr_amt := v_rounded_cr_oth_amount.sh_cvd_edu_cess;
2975 -- end 5989740
2976 end if;
2977
2978 if gb_debug then
2979 fnd_file.put_line(fnd_file.log, '5-Before Insert of RG Others.Amts:dr-'||v_dr_amt||',cr-'||v_cr_amt );
2980 end if;
2981
2982 IF NVL(v_dr_amt,0) <> 0 OR NVL(v_cr_amt,0) <> 0 THEN
2983 INSERT INTO JAI_CMN_RG_OTHERS (
2984 RG_OTHER_ID,
2985 SOURCE_TYPE,
2986 SOURCE_REGISTER,
2987 SOURCE_REGISTER_ID,
2988 TAX_TYPE,
2989 DEBIT,
2990 CREDIT,
2991 OPENING_BALANCE,
2992 CLOSING_BALANCE,
2993 CREATED_BY,
2994 CREATION_DATE,
2995 LAST_UPDATED_BY,
2996 LAST_UPDATE_DATE,
2997 LAST_UPDATE_LOGIN
2998 ) VALUES (
2999 JAI_CMN_RG_OTHERS_S.nextval,
3000 DECODE(r.register, LV_PLA_REGISTER, 2, 1),
3001 DECODE(r.register, LV_PLA_REGISTER, 'PLA', decode(v_register_type,'A','RG23A_P2','C','RG23C_P2')),/*for bug 5054176*/
3002 v_register_id_part_ii,
3003 v_tax_type,
3004 v_dr_amt,
3005 v_cr_amt,
3006 NULL,
3007 NULL,
3008 v_created_by,
3009 sysdate,
3010 v_created_by,
3011 sysdate,
3012 v_last_update_login
3013 );
3014 END IF;
3015 END LOOP;
3016 END;
3017
3018 v_statement_no := '10a';
3019 -- this call will update Rounding RG23 PartII entry with Period_balance_id and updates all records of JAI_CMN_RG_PERIOD_BALS
3020 -- that come after the period in which parent Excise invoice has hit RG
3021 if r.register = LV_RG23_REGISTER then
3022 jai_cmn_rg_period_bals_pkg.adjust_rounding(v_register_id_part_ii, v_period_balance_id, v_no_of_periods_updated);
3023 end if;
3024
3025 if gb_debug then
3026 fnd_file.put_line(fnd_file.log, '6-Before Insert into Rounding Entries. Id:'||v_rounding_id);
3027 end if;
3028
3029 v_statement_no := '11';
3030 INSERT INTO JAI_CMN_RG_ROUND_HDRS(
3031 ROUNDING_ID, SOURCE_HEADER_ID, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
3032 EXCISE_AMT_BEFORE_ROUNDING, EXCISE_AMT_AFTER_ROUNDING,
3033 BASIC_ED, ROUNDED_BASIC_ED,
3034 ADDITIONAL_ED, ROUNDED_ADDITIONAL_ED,
3035 ADDITIONAL_CVD, ROUNDED_ADDITIONAL_CVD,/*5228046 ADDITIONAL CVD ENHANCEMENT*/
3036 OTHER_ED, ROUNDED_OTHER_ED,
3037 EXCISE_EDU_CESS, ROUNDED_EXCISE_EDU_CESS,
3038 CVD_EDU_CESS, ROUNDED_CVD_EDU_CESS,
3039 sh_excise_edu_cess, rounded_sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
3040 sh_cvd_edu_cess, rounded_sh_cvd_edu_cess, -- SH Cess column is added
3041 register, REGISTER_ID, SOURCE, SRC_TRANSACTION_TYPE,
3042 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
3043 program_application_id, program_id, program_login_id, request_id
3044 ) VALUES (
3045 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. v_rounding_id, v_shipment_header_id, v_excise_invoice_no, v_excise_invoice_date,
3046 v_rounding_id, ln_rtv_excise_batch_group_id, v_excise_invoice_no, v_excise_invoice_date,
3047 v_tot_amount.total, v_tot_rounded_amount.total,
3048 v_tot_amount.basic, v_tot_rounded_amount.basic,
3049 v_tot_amount.additional, v_tot_rounded_amount.additional,
3050 V_TOT_AMOUNT.ADDITIONAL_CVD, V_TOT_ROUNDED_AMOUNT.ADDITIONAL_CVD, /*5228046 ADDITIONAL CVD ENHANCEMENT*/
3051 v_tot_amount.other, v_tot_rounded_amount.other,
3052 v_tot_amount.excise_edu_cess, v_tot_rounded_amount.excise_edu_cess,
3053 v_tot_amount.cvd_edu_cess, v_tot_rounded_amount.cvd_edu_cess,
3054 v_tot_amount.sh_excise_edu_cess, v_tot_rounded_amount.sh_excise_edu_cess, -- Date 16/04/2007 by sacsethi for Bug#5989740
3055 v_tot_amount.sh_cvd_edu_cess, v_tot_rounded_amount.sh_cvd_edu_cess,
3056 r.register, v_register_id_part_ii, 'PO', p_transaction_type,
3057 SYSDATE, v_created_by, SYSDATE, v_created_by, v_last_update_login,
3058 fnd_profile.value('PROG_APPL_ID'), fnd_profile.value('CONC_PROGRAM_ID'), fnd_profile.value('CONC_LOGIN_ID'), fnd_profile.value('CONC_REQUEST_ID')
3059 );
3060
3061 v_statement_no := '12';
3062
3063 if gb_debug then
3064 fnd_file.put_line(fnd_file.log, '7-Pass RG Accnting: Cr-'||v_rounded_cr_amount||', Dr-'||v_rounded_dr_amount);
3065 end if;
3066
3067 -- 2 (CR, DR) GL Interface related calls has to be coded
3068 -- Modvat Account entries
3069 jai_rcv_rnd_pkg.pass_accounting(
3070 p_organization_id => p_organization_id,
3071 p_transaction_id => r.rcv_transaction_id,
3072 p_transaction_date => v_today,
3073 p_shipment_line_id => -1,
3074 p_acct_type => v_acct_type,
3075 p_acct_nature => v_acct_nature,
3076 p_source => v_source_name,
3077 p_category => v_category_name,
3078 p_code_combination_id => v_rg_account_id,
3079 p_entered_dr => v_rounded_cr_amount,
3080 p_entered_cr => v_rounded_dr_amount,
3081 p_created_by => v_created_by,
3082 p_currency_code => 'INR',
3083 p_currency_conversion_type => NULL,
3084 p_currency_conversion_date => NULL,
3085 p_currency_conversion_rate => NULL,
3086 p_receipt_num => v_receipt_num
3087 );
3088
3089 v_statement_no := '13';
3090
3091 jai_cmn_gl_pkg.create_gl_entry(
3092 p_organization_id => p_organization_id,
3093 p_currency_code => 'INR',
3094 p_credit_amount => v_rounded_dr_amount,
3095 p_debit_amount => v_rounded_cr_amount,
3096 p_cc_id => v_rg_account_id,
3097 p_je_source_name => v_source_name,
3098 p_je_category_name => v_category_name,
3099 p_created_by => v_created_by,
3100 p_accounting_date => v_today,
3101 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. p_reference_10 => 'India Local Rounding Entry for Shipment_Header_id:'||r.shipment_header_id
3102 p_reference_10 => 'India Local Rounding Entry for RTV batch_group_id:'||ln_rtv_excise_batch_group_id
3103 ||', excise_invoice_no:'||r.excise_invoice_no||', exc_inv_date:'||r.excise_invoice_date,
3104 p_reference_23 => 'ja_in_rg_rounding_p',
3105 p_reference_24 => 'JAI_CMN_RG_ROUND_HDRS',
3106 p_reference_25 => 'ROUNDING_ID',
3107 p_reference_26 => v_rounding_id
3108 );
3109
3110 v_statement_no := '14';
3111
3112 -- Rounding Account entries
3113 jai_rcv_rnd_pkg.pass_accounting(
3114 p_organization_id => p_organization_id,
3115 p_transaction_id => r.rcv_transaction_id,
3116 p_transaction_date => v_today,
3117 p_shipment_line_id => -1,
3118 p_acct_type => v_acct_type,
3119 p_acct_nature => v_acct_nature,
3120 p_source => v_source_name,
3121 p_category => v_category_name,
3122 p_code_combination_id => v_rg_rounding_account_id,
3123 p_entered_dr => v_rounded_dr_amount,
3124 p_entered_cr => v_rounded_cr_amount,
3125 p_created_by => v_created_by,
3126 p_currency_code => 'INR',
3127 p_currency_conversion_type => NULL,
3128 p_currency_conversion_date => NULL,
3129 p_currency_conversion_rate => NULL,
3130 p_receipt_num => v_receipt_num
3131 );
3132
3133 v_statement_no := '15';
3134
3135 jai_cmn_gl_pkg.create_gl_entry(
3136 p_organization_id => p_organization_id,
3137 p_currency_code => 'INR',
3138 p_credit_amount => v_rounded_cr_amount,
3139 p_debit_amount => v_rounded_dr_amount,
3140 p_cc_id => v_rg_rounding_account_id,
3141 p_je_source_name => v_source_name,
3142 p_je_category_name => v_category_name,
3143 p_created_by => v_created_by,
3144 p_accounting_date => v_today,
3145 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. p_reference_10 => 'India Local Rounding Entry for Shipment_Header_id:'||r.shipment_header_id
3146 p_reference_10 => 'India Local Rounding Entry for RTV batch_group_id:'||ln_rtv_excise_batch_group_id
3147 ||', excise_invoice_no:'||r.excise_invoice_no||', exc_inv_date:'||r.excise_invoice_date,
3148 p_reference_23 => 'ja_in_rg_rounding_p',
3149 p_reference_24 => 'JAI_CMN_RG_ROUND_HDRS',
3150 p_reference_25 => 'ROUNDING_ID',
3151 p_reference_26 => v_rounding_id
3152 );
3153
3154 v_statement_no := '16';
3155
3156 if v_register_type = 'A' then
3157 UPDATE JAI_CMN_RG_BALANCES
3158 SET rg23a_balance = nvl(rg23a_balance, 0) + v_rounded_amount_rg23
3159 WHERE organization_id = p_organization_id
3160 AND location_id = r.location_id;
3161
3162 elsif v_register_type = 'C' then
3163 UPDATE JAI_CMN_RG_BALANCES
3164 SET rg23c_balance = nvl(rg23c_balance, 0) + v_rounded_amount_rg23
3165 WHERE organization_id = p_organization_id
3166 AND location_id = r.location_id;
3167
3168 elsif r.register = LV_PLA_REGISTER then
3169 /* this update is already taken in jai_cmn_rg_pla_trxs_pkg.insert_row call*/
3170 NULL;
3171
3172 end if;
3173
3174 v_statement_no := '16.1';
3175
3176 fnd_file.put_line(fnd_file.log,' 101 exc_rnd_cnt->'||v_excise_inv_rnd_cnt);
3177
3178 if gb_debug then
3179 fnd_file.put_line(fnd_file.log, '8-Punching RoundingId in Register');
3180 end if;
3181
3182 if r.register = LV_RG23_REGISTER then
3183 lv_ttype_correct := 'CORRECT'; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3184 UPDATE JAI_CMN_RG_23AC_II_TRXS aa
3185 SET rounding_id = v_rounding_id
3186 WHERE organization_id = p_organization_id
3187 AND excise_invoice_no = v_excise_invoice_no
3188 AND excise_invoice_date = v_excise_invoice_date
3189 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
3190 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
3191 AND rounding_id IS NULL
3192 AND TRANSACTION_SOURCE_NUM = 18
3193 AND EXISTS (
3194 SELECT BB.transaction_id
3195 FROM JAI_RCV_TRANSACTIONS bb, jai_rcv_rtv_batch_trxs b1
3196 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE bb.shipment_header_id = v_shipment_header_id
3197 WHERE bb.transaction_id = b1.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
3198 and b1.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
3199 AND bb.transaction_id = aa.receipt_ref
3200 AND ( bb.transaction_type = r.transaction_type
3201 OR (bb.transaction_type = lv_ttype_correct and bb.parent_transaction_type = r.transaction_type) --'CORRECT'
3202 )
3203 );
3204
3205 elsif r.register = LV_PLA_REGISTER then
3206 lv_ttype_correct := 'CORRECT'; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3207 UPDATE JAI_CMN_RG_PLA_TRXS aa
3208 SET rounding_id = v_rounding_id
3209 WHERE organization_id = p_organization_id
3210 AND DR_INVOICE_NO = v_excise_invoice_no
3211 AND dr_invoice_date = v_excise_invoice_date
3212 AND nvl(vendor_id,-999) = nvl(r.vendor_id,-999)/*bgowrava for forward porting bug#5674376*/
3213 AND nvl(vendor_site_id,-999) = nvl(r.vendor_site_id,-999)/*bgowrava for forward porting bug#5674376*/
3214 AND rounding_id IS NULL
3215 AND TRANSACTION_SOURCE_NUM = 19
3216 AND exists (
3217 SELECT BB.transaction_id
3218 FROM JAI_RCV_TRANSACTIONS bb, jai_rcv_rtv_batch_trxs b1
3219 -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. WHERE bb.shipment_header_id = v_shipment_header_id
3220 WHERE bb.transaction_id = b1.transaction_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
3221 and b1.excise_batch_group_id = ln_rtv_excise_batch_group_id -- Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh.
3222 AND bb.transaction_id = aa.ref_document_id
3223 AND ( bb.transaction_type = r.transaction_type
3224 OR (bb.transaction_type = lv_ttype_correct and bb.parent_transaction_type = r.transaction_type)
3225 )
3226 );
3227
3228 end if;
3229
3230 v_rounding_entries_made := v_rounding_entries_made + 1;
3231
3232 v_statement_no := '17';
3233 IF v_no_of_invoices_posted >= v_commit_interval THEN
3234 v_no_of_invoices_posted := 0;
3235 COMMIT;
3236 if gb_debug then
3237 fnd_file.put_line(fnd_file.log, '9-After Commit of '||v_commit_interval||' entries');
3238 end if;
3239
3240 ELSE
3241 v_no_of_invoices_posted := v_no_of_invoices_posted + 1;
3242 END IF;
3243
3244 <<next_exc_inv>>
3245 v_save_point_set := false;
3246
3247 EXCEPTION
3248 WHEN OTHERS THEN
3249 v_tot_errored_entries := v_tot_errored_entries + 1;
3250 fnd_file.put_line(fnd_file.log, 'Error at statement_no->'|| v_statement_no
3251 ||', rtvExcBtchGrpId->'||ln_rtv_excise_batch_group_id
3252 ||', excise_invoice_no->'||v_excise_invoice_no
3253 ||', excise_invoice_date->'||v_excise_invoice_date||' '
3254 );
3255 fnd_file.put_line(fnd_file.log, 'ErrMess->'|| SQLERRM);
3256
3257 IF v_save_point_set THEN
3258 -- This has to rollback only if SAVEPOINT is set for the exc invoice being processed
3259 ROLLBACK TO previous_savepoint;
3260 v_save_point_set := false;
3261 END IF;
3262
3263 END;
3264
3265 v_tot_amount := NULL;
3266 v_tot_rounded_amount := NULL;
3267 v_rounded_amount := NULL;
3268 v_rounded_amount_abs := NULL;
3269 v_rounded_amount_rg23 := NULL;
3270 v_rounded_cr_amount := NULL;
3271 v_rounded_dr_amount := NULL;
3272 v_rounded_cr_rg23_amount := NULL;
3273 v_rounded_dr_rg23_amount := NULL;
3274 v_rounded_cr_oth_amount := NULL;
3275 v_rounded_dr_oth_amount := NULL;
3276
3277 v_rounding_entry_type := null;
3278
3279 v_vendor_id := null;
3280 v_vendor_site_id := null;
3281 v_modvat_rm_account_id := null;
3282 v_modvat_cg_account_id := null;
3283 v_rg_account_id := null;
3284 v_rg23_balance := null;
3285 v_balance := null;
3286 v_slno := null;
3287
3288 v_rounding_id := null;
3289 v_register_id_part_ii := null;
3290
3291 v_excise_invoice_no := null;
3292 v_excise_invoice_date := null;
3293 v_register_type := null;
3294 v_receipt_num := null;
3295 v_pla_register_id := null;
3296 lv_process_message := null;
3297 lv_process_status := null;
3298
3299 v_exc_inv_rnd_counter := v_exc_inv_rnd_counter + 1;
3300
3301 END LOOP;
3302
3303 fnd_file.put_line(fnd_file.log, 'Completed. Total Rounding entries made -> '|| v_rounding_entries_made
3304 ||', errored entries->'||v_tot_errored_entries
3305 ||', Zero Exc. Amt. Invoices found->'||v_zero_round_found
3306 ||', total Ex. Invoices processed->'||v_tot_processed_invoices
3307 );
3308
3309
3310 /* Added by Ramananda for bug#4407165 */
3311 EXCEPTION
3312 WHEN OTHERS THEN
3313 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
3314 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
3315 app_exception.raise_exception;
3316
3317 END do_rtv_rounding;
3318
3319 END jai_rcv_rnd_pkg;