[Home] [Help]
PACKAGE BODY: APPS.JAI_RETRO_PRC_PKG
Source
1 PACKAGE BODY JAI_RETRO_PRC_PKG AS
2 --$Header: jai_retro_prc.plb 120.9.12020000.2 2012/12/30 10:31:54 anupgupt ship $
3 --|+======================================================================+
4 --| Copyright (c) 2007 Oracle Corporation Redwood Shores, California, USA |
5 --| All rights reserved. |
6 --+=======================================================================+
7 --| FILENAME |
8 --| JAI_RETRO_PRC_PKG.plb |
9 --| |
10 --| DESCRIPTION |
11 --| This package offer funcitons to process the retro receipt |
12 --| |
13 --| |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Process_Retroactive_Update |
17 --| PROCEDURE Do_Cenvat_Claim |
18 --| PROCEDURE Do_Vat_Claim |
19 --| PROCEDURE Do_Unclaim |
20 --| PROCEDURE Get_Tax_Amount_Breakup |
21 --| PROCEDURE Get_Vat_CenVat_Amount |
22 --| PROCEDURE Print_Shipment_Detail |
23 --| PROCEDURE Get_Tot_NonRe_Tax_Amount |
24 --| |
25 --| FUNCTION Get_Recoverable_Amount |
26 --| FUNCTION Get_NonRecoverable_Amount |
27 --| |
28 --| HISTORY |
29 --| 2008/01/08 Eric Ma Created |
30 --| 2008/02/01 Eric Ma Add log,change Do_Uncalim and Process_Retroactive_Update for bug #6788048
31 --| 2008/03/28 Eric Ma reversal the debit/credit and correct the cenvat
32 --| tax amount for the bug 6918495 and bug 6914567
33 --|
34 --| 2008/04/08 Eric Ma fail to fully fix the bug 6918495 as the amount is still in half as expected
35 --| open an new bug 6955045 and the modification is for the bug
36 --|
37 --| 2008/04/10 Eric Ma changed the code for bug#6957519/6958938/6968839 ,incorrect accouting entries generated in DELIVERY
38 --|
39 --| 2008/04/15 Eric Ma changed the code for bug#6968733 on Apr 15,2008,Vat tax claim is not correct after retro
40 --|
41 --| 2008/04/21 Jia Li changed the code for bug#6988208 on Apr 21,2008,
42 --| Recoverable amt on Vat tax claim header is not correct after retro .
43 --|
44 --| 2010/04/14 Bo Li For bug9305067
45 --| Change the parameters for the procedure insert_vat_repository_entry .
46 --| 2012/12/30 anupgupt for bug 16013918
47 --| removed GST changes
48 --| |
49 --+======================================================================*/
50 --==========================================================================
51 -- PROCEDURE NAME:
52 --
53 -- Get_Recoverable_Amount Private
54 --
55 -- DESCRIPTION:
56 --
57 -- This procedure is used to get the recoverable amount for a given tax id
58 --
59 --
60 -- PARAMETERS:
61 -- In: pn_tax_id NUMBER tax identifier
62 -- pn_tax_amount NUMBER tax amount
63 -- pn_conver_rate NUMBER DEFAULT 1 converstion rate between different currency
64 -- pn_rounding_factor NUMBER DEFAULT NULL rounding factor
65 --
66 -- DESIGN REFERENCES:
67 -- JAI_Retroprice_TDD.doc
68 --
69 -- CHANGE HISTORY:
70 --
71 -- 14-JAN-2008 Eric Ma created
72 --==========================================================================
73 FUNCTION Get_Recoverable_Amount
74 ( pn_tax_id NUMBER
75 , pn_tax_amount NUMBER
76 , pn_conver_rate NUMBER DEFAULT 1
77 , pn_rounding_factor NUMBER DEFAULT NULL
78 )
79 RETURN NUMBER
80 IS
81
82 CURSOR get_jai_cmn_taxes_all_cur
83 IS
84 SELECT
85 mod_cr_percentage
86 , rounding_factor
87 FROM
88 jai_cmn_taxes_all
89 WHERE tax_id = pn_tax_id;
90
91 ln_re_tax_amount NUMBER;
92 ln_mod_cr_percent jai_cmn_taxes_all.MOD_CR_PERCENTAGE%TYPE;
93 ln_rounding_factor jai_cmn_taxes_all.rounding_factor%TYPE;
94 lv_procedure_name VARCHAR2(40):='Get_Recoverable_Amount';
95 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
96 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
97 BEGIN
98 --logging for debug
99 IF (ln_proc_level >= ln_dbg_level)
100 THEN
101 FND_LOG.STRING( ln_proc_level
102 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
103 , 'Enter procedure'
104 );
105 END IF; --l_proc_level>=l_dbg_level
106
107 OPEN get_jai_cmn_taxes_all_cur;
108 FETCH get_jai_cmn_taxes_all_cur
109 INTO
110 ln_mod_cr_percent
111 , ln_rounding_factor;
112 CLOSE get_jai_cmn_taxes_all_cur;
113
114 ln_mod_cr_percent := NVL(ln_mod_cr_percent,0)/100;
115 ln_rounding_factor := NVL(NVL(pn_rounding_factor,ln_rounding_factor),0);
116 ln_re_tax_amount := pn_tax_amount * ln_mod_cr_percent *pn_conver_rate;
117 ln_re_tax_amount := ROUND(ln_re_tax_amount,ln_rounding_factor);
118
119
120 IF (ln_proc_level >= ln_dbg_level)
121 THEN
122
123 FND_LOG.STRING( ln_proc_level
124 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.pn_tax_amount'
125 , 'pn_tax_amount :' || pn_tax_amount
126 );
127
128 FND_LOG.STRING( ln_proc_level
129 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.ln_mod_cr_percent'
130 , 'ln_mod_cr_percent :' || ln_mod_cr_percent
131 );
132
133 FND_LOG.STRING( ln_proc_level
134 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.pn_conver_rate'
135 , 'pn_conver_rate :' || pn_conver_rate
136 );
137
138 FND_LOG.STRING( ln_proc_level
139 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.parameter'
140 , 'ln_re_tax_amount' || ln_re_tax_amount
141 );
142
143 FND_LOG.STRING( ln_proc_level
144 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
145 , 'Exit procedure'
146 );
147 END IF; --l_proc_level>=l_dbg_level
148
149
150 RETURN NVL(ln_re_tax_amount,0) ;
151 EXCEPTION
152 WHEN OTHERS THEN
153 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
154 THEN
155 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
156 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
157 , Sqlcode||Sqlerrm);
158 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
159 RAISE;
160 END Get_Recoverable_Amount;
161
162 --==========================================================================
163 -- FUNCTION NAME:
164 --
165 -- Get_NonRecoverable_Amount Private
166 --
167 -- DESCRIPTION:
168 --
169 -- This procedure is used to get the non recoverable tax amount for a given tax id
170 --
171 --
172 -- PARAMETERS:
173 -- In: pn_tax_id NUMBER tax identifier
174 -- pn_tax_amount NUMBER tax amount
175 -- pn_conver_rate NUMBER DEFAULT 1 converstion rate between different currency
176 -- pn_rounding_factor NUMBER DEFAULT NULL rounding factor
177 --
178 -- DESIGN REFERENCES:
179 -- JAI_Retroprice_TDD.doc
180 --
181 -- CHANGE HISTORY:
182 --
183 -- 14-JAN-2008 Eric Ma created
184 --==========================================================================
185
186 FUNCTION Get_NonRecoverable_Amount
187 ( pn_tax_id NUMBER
188 , pn_tax_amount NUMBER
189 , pn_conver_rate NUMBER DEFAULT 1
190 , pn_rounding_factor NUMBER DEFAULT NULL
191 )
192 RETURN NUMBER
193 IS
194
195 CURSOR get_jai_cmn_taxes_all_cur
196 IS
197 SELECT
198 mod_cr_percentage
199 , rounding_factor
200 FROM
201 jai_cmn_taxes_all
202 WHERE tax_id = pn_tax_id;
203
204 ln_nr_tax_amount NUMBER;
205 ln_nr_mod_cr_percent jai_cmn_taxes_all.MOD_CR_PERCENTAGE%TYPE;
206 ln_mod_cr_percent jai_cmn_taxes_all.MOD_CR_PERCENTAGE%TYPE;
207 ln_rounding_factor jai_cmn_taxes_all.rounding_factor%TYPE;
208 lv_procedure_name VARCHAR2(40):='Get_NonRecoverable_Amount';
209 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
210 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
211 BEGIN
212 --logging for debug
213 IF (ln_proc_level >= ln_dbg_level)
214 THEN
215 FND_LOG.STRING( ln_proc_level
216 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
217 , 'Enter procedure'
218 );
219 END IF; --l_proc_level>=l_dbg_level
220
221 OPEN get_jai_cmn_taxes_all_cur;
222 FETCH get_jai_cmn_taxes_all_cur
223 INTO
224 ln_mod_cr_percent
225 , ln_rounding_factor;
226 CLOSE get_jai_cmn_taxes_all_cur;
227
228 ln_nr_mod_cr_percent := (100-NVL(ln_mod_cr_percent,0))/100;
229 ln_rounding_factor := NVL(NVL(pn_rounding_factor,ln_rounding_factor),0);
230 ln_nr_tax_amount := pn_tax_amount * ln_nr_mod_cr_percent *pn_conver_rate;
231 ln_nr_tax_amount := ROUND(ln_nr_tax_amount,ln_rounding_factor);
232
233 --logging for debug
234 IF (ln_proc_level >= ln_dbg_level)
235 THEN
236 FND_LOG.STRING( ln_proc_level
237 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
238 , 'Exit procedure'
239 );
240 END IF; -- (ln_proc_level>=ln_dbg_level)
241
242 RETURN NVL(ln_nr_tax_amount,0) ;
243 EXCEPTION
244 WHEN OTHERS THEN
245 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
246 THEN
247 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
248 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
249 , Sqlcode||Sqlerrm);
250 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
251 RAISE;
252 END Get_NonRecoverable_Amount;
253
254 --==========================================================================
255 -- PROCEDURE NAME:
256 --
257 -- Get_Tot_NonRe_Tax_Amount Private
258 --
259 -- DESCRIPTION:
260 --
261 -- This procedure is used to get the total origianl/modified/difference of
262 -- non recoverable tax amount for a given line_change_id
263 --
264 --
265 -- PARAMETERS:
266 -- In: pn_line_change_id NUMBER tax identifier
267 --
268 -- DESIGN REFERENCES:
269 -- JAI_Retroprice_TDD.doc
270 --
271 -- CHANGE HISTORY:
272 --
273 -- 14-JAN-2008 Eric Ma created
274 -- 10-Apr-2008 Eric Ma Updated the procedure for the bug 6957519/6958938/6968839
275 --==========================================================================
276
277 PROCEDURE Get_Tot_NonRe_Tax_Amount
278 ( pn_line_change_id IN NUMBER
279 , xn_org_nonre_tax_amount OUT NOCOPY NUMBER
280 , xn_modif_nonre_tax_amount OUT NOCOPY NUMBER
281 , xn_diff_nonre_tax_amount OUT NOCOPY NUMBER
282 )
283 IS
284 CURSOR get_tax_info_cur
285 IS
286 SELECT
287 original_tax_amount --eric added for bug 6957519/6958938/6968839
288 , modified_tax_amount
289 , tax_id
290 FROM
291 jai_retro_tax_changes
292 WHERE line_change_id = pn_line_change_id;
293
294 ln_tot_org_nr_tax_amt NUMBER :=0; --eric added for bug 6957519/6958938/6968839 on Apr 10,2008
295 ln_tot_modif_nr_tax_amt NUMBER :=0; --eric added for bug 6957519/6958938/6968839 on Apr 10,2008
296 ln_tot_diff_nr_tax_amt NUMBER :=0; --eric added for bug 6957519/6958938/6968839 on Apr 10,2008
297
298 lv_procedure_name VARCHAR2(40):='Get_Tot_NonRe_Tax_Amount';
299 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
300 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
301 BEGIN
302 --logging for debug
303 IF (ln_proc_level >= ln_dbg_level)
304 THEN
305 FND_LOG.STRING( ln_proc_level
306 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
307 , 'Enter procedure'
308 );
309 FND_LOG.STRING( ln_proc_level
310 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.parameter'
311 , 'pn_line_change_id :'|| pn_line_change_id
312 );
313 END IF; --l_proc_level>=l_dbg_level
314
315 --eric modified for bug 6957519/6958938/6968839 on Apr 10,2008, beign
316 -----------------------------------------------------------------------------------------------------
317 FOR tax_info_rec IN get_tax_info_cur
318 LOOP
319 ln_tot_modif_nr_tax_amt := ln_tot_modif_nr_tax_amt +
320 Get_NonRecoverable_Amount( pn_tax_id => tax_info_rec.tax_id
321 , pn_tax_amount => tax_info_rec.modified_tax_amount
322 );
323
324 ln_tot_org_nr_tax_amt := ln_tot_org_nr_tax_amt +
325 Get_NonRecoverable_Amount( pn_tax_id => tax_info_rec.tax_id
326 , pn_tax_amount => tax_info_rec.original_tax_amount
327 );
328 -- FND_FILE.PUT_LINE(fnd_file.log,' Tax id is : '|| tax_info_rec.tax_id||'Accumulated Tax is: '||ln_tot_nr_tax_amt);
329 END LOOP;--tax_info_rec IN get_tax_info_cur
330
331 ln_tot_diff_nr_tax_amt := ln_tot_modif_nr_tax_amt - ln_tot_org_nr_tax_amt;
332
333 xn_org_nonre_tax_amount := ln_tot_org_nr_tax_amt ;
334 xn_modif_nonre_tax_amount := ln_tot_modif_nr_tax_amt ;
335 xn_diff_nonre_tax_amount := ln_tot_diff_nr_tax_amt ;
336
337 -----------------------------------------------------------------------------------------------------
338 --eric modified for bug 6957519/6958938/6968839 on Apr 10,2008, end
339
340 --logging for debug
341 IF (ln_proc_level >= ln_dbg_level)
342 THEN
343 FND_LOG.STRING( ln_proc_level
344 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.parameter'
345 , 'ln_tot_modif_nr_tax_amt :'|| ln_tot_modif_nr_tax_amt
346 );
347
348 FND_LOG.STRING( ln_proc_level
349 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.parameter'
350 , 'ln_tot_org_nr_tax_amt :'|| ln_tot_org_nr_tax_amt
351 );
352
353 FND_LOG.STRING( ln_proc_level
354 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.parameter'
355 , 'ln_tot_diff_nr_tax_amt :'|| ln_tot_diff_nr_tax_amt
356 );
357
358 FND_LOG.STRING( ln_proc_level
359 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
360 , 'Exit procedure'
361 );
362 END IF; -- (ln_proc_level>=ln_dbg_level)
363
364 --eric deleted for bug 6957519/6958938/6968839 on Apr 10,2008,begin
365 ---------------------------------------------
366 --RETURN ln_tot_nr_tax_amt;
367 ---------------------------------------------
368 --eric deleted for bug 6957519/6958938/6968839 on Apr 10,2008,end
369 EXCEPTION
370 WHEN OTHERS THEN
371 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
372 THEN
373 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
374 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
375 , Sqlcode||Sqlerrm);
376 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
377 RAISE;
378 END Get_Tot_NonRe_Tax_Amount;
379
380 --==========================================================================
381 -- PROCEDURE NAME:
382 --
383 -- Get_Vat_CenVat_Amount Private
384 --
385 -- DESCRIPTION:
386 --
387 -- This procedure is used to get total recoverable modified/original amount of vat/cenvat tax
388 -- for a given line_change_id. The difference between modified and original tax amount will also
389 -- be returned by the procedure
390 --
391 -- PARAMETERS:
392 -- In: pn_line_change_id NUMBER jai retro line change identifier
393 --
394 -- OUT:
395 -- xn_re_vat_amount NUMBER original recoverable vat tax amount
396 -- xn_modif_re_vat_amount NUMBER modified recoverable vat tax amount
397 -- xn_diff_re_vat_amount NUMBER difference between original/ modified recoverable vat tax amount
398 -- xn_re_cenvat_amount NUMBER original recoverable excise tax amount
399 -- xn_modif_re_cenvat_amount NUMBER modified recoverable excise tax amount
400 -- xn_diff_re_cenvat_amount NUMBER difference between original/ modified recoverable vat tax amount
401 -- DESIGN REFERENCES:
402 -- JAI_Retroprice_TDD.doc
403 --
404 -- CHANGE HISTORY:
405 --
406 -- 14-JAN-2008 Eric Ma created
407 --==========================================================================
408 PROCEDURE Get_Vat_CenVat_Amount
409 ( pn_line_change_id IN NUMBER
410 , xn_re_vat_amount OUT NOCOPY NUMBER
411 , xn_modif_re_vat_amount OUT NOCOPY NUMBER
412 , xn_diff_re_vat_amount OUT NOCOPY NUMBER
413 , xn_re_cenvat_amount OUT NOCOPY NUMBER
414 , xn_modif_re_cenvat_amount OUT NOCOPY NUMBER
415 , xn_diff_re_cenvat_amount OUT NOCOPY NUMBER
416 )
417 IS
418
419 CURSOR get_cenvat_tax_info_cur
420 IS
421 SELECT
422 original_tax_amount
423 , modified_tax_amount
424 , tax_id
425 FROM
426 jai_retro_tax_changes
427 WHERE line_change_id = pn_line_change_id
428 AND tax_type IN ( JAI_CONSTANTS.tax_type_excise
429 , JAI_CONSTANTS.tax_type_exc_additional
430 , JAI_CONSTANTS.tax_type_exc_other
431 , JAI_CONSTANTS.tax_type_exc_edu_cess
432 , JAI_CONSTANTS.tax_type_sh_exc_edu_cess
433 )
434 AND recoverable_flag ='Y';
435
436 CURSOR get_vat_tax_info_cur
437 IS
438 SELECT
439 original_tax_amount
440 , modified_tax_amount
441 , tax_id
442 FROM
443 jai_retro_tax_changes jrtc
444 WHERE EXISTS( SELECT
445 'X'
446 FROM
447 JAI_RGM_DEFINITIONS jr
448 , JAI_RGM_REGISTRATIONS jrr
449 WHERE jr.regime_id = jrr.regime_id
450 AND jr.regime_code = jai_constants.vat_regime
451 AND jrr.registration_type = jai_constants.regn_type_tax_types
452 AND jrtc.tax_type = jrr.attribute_code
453 )
454 AND recoverable_flag ='Y'
455 AND line_change_id = pn_line_change_id;
456
457 ln_tot_re_cenvat_amt NUMBER :=0;
458 ln_modif_tot_re_cenvat_amt NUMBER :=0;
459 ln_tot_re_vat_amt NUMBER :=0;
460 ln_modif_tot_re_vat_amt NUMBER :=0;
461
462 lv_procedure_name VARCHAR2(40):='Get_Vat_CenVat_Amount';
463 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
464 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
465
466
467 BEGIN
468
469 --logging for debug
470 IF (ln_proc_level >= ln_dbg_level)
471 THEN
472 FND_LOG.STRING( ln_proc_level
473 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
474 , 'Enter procedure'
475 );
476 END IF; --l_proc_level>=l_dbg_level
477
478 FOR cenvat_tax_info_rec IN get_cenvat_tax_info_cur
479 LOOP
480 ln_tot_re_cenvat_amt := ln_tot_re_cenvat_amt +
481 Get_Recoverable_Amount ( pn_tax_id =>cenvat_tax_info_rec.tax_id
482 , pn_tax_amount =>cenvat_tax_info_rec.original_tax_amount
483 );
484
485 ln_modif_tot_re_cenvat_amt := ln_modif_tot_re_cenvat_amt +
486 Get_Recoverable_Amount ( pn_tax_id =>cenvat_tax_info_rec.tax_id
487 , pn_tax_amount =>cenvat_tax_info_rec.modified_tax_amount
488 );
489 END LOOP;
490 xn_re_cenvat_amount := ln_tot_re_cenvat_amt ;
491 xn_modif_re_cenvat_amount := ln_modif_tot_re_cenvat_amt;
492 xn_diff_re_cenvat_amount := ln_modif_tot_re_cenvat_amt - ln_tot_re_cenvat_amt;
493
494 FOR vat_tax_info_rec IN get_vat_tax_info_cur
495 LOOP
496 ln_tot_re_vat_amt := ln_tot_re_vat_amt +
497 Get_Recoverable_Amount ( pn_tax_id =>vat_tax_info_rec.tax_id
498 , pn_tax_amount =>vat_tax_info_rec.original_tax_amount
499 );
500
501 ln_modif_tot_re_vat_amt := ln_modif_tot_re_vat_amt +
502 Get_Recoverable_Amount ( pn_tax_id =>vat_tax_info_rec.tax_id
503 , pn_tax_amount =>vat_tax_info_rec.modified_tax_amount
504 );
505 END LOOP;
506 xn_re_vat_amount := ln_tot_re_vat_amt ;
507 xn_modif_re_vat_amount := ln_modif_tot_re_vat_amt;
508 xn_diff_re_vat_amount := ln_modif_tot_re_vat_amt - ln_tot_re_vat_amt;
509
510 EXCEPTION
511 WHEN OTHERS THEN
512 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
513 THEN
514 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
515 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
516 , Sqlcode||Sqlerrm);
517 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
518 RAISE;
519 END Get_Vat_CenVat_Amount;
520
521 --==========================================================================
522 -- PROCEDURE NAME:
523 --
524 -- Get_Tax_Amount_Breakup Private
525 --
526 -- DESCRIPTION:
527 --
528 -- This procedure is used to get breakup the excise tax amount for different tax types
529 --
530 --
531 -- PARAMETERS:
532 -- In: pn_shipment_line_id NUMBER tax identifier
533 -- pn_transaction_id NUMBER transaction identifier
534 -- pn_line_change_id NUMBER identifier of jai_retro_line_changes
535 -- DESIGN REFERENCES:
536 -- JAI_Retroprice_TDD.doc
537 --
538 -- CHANGE HISTORY:
539 --
540 -- 14-JAN-2008 Eric Ma created
541 --==========================================================================
542
543 PROCEDURE Get_Tax_Amount_Breakup
544 ( pn_shipment_line_id IN NUMBER
545 , pn_transaction_id IN NUMBER
546 , pn_curr_conv_rate IN NUMBER
547 , pr_tax OUT NOCOPY JAI_RCV_EXCISE_PROCESSING_PKG.tax_breakup
548 , pv_breakup_type IN VARCHAR2
549 , pn_line_change_id IN NUMBER
550 )
551 IS
552 ln_curr_conv NUMBER;
553 ln_mod_problem_amt NUMBER;
554 ln_nonmod_problem_amt NUMBER;
555 ln_apportion_factor NUMBER;
556
557 lv_procedure_name VARCHAR2(40):='Get_Tax_Amount_Breakup';
558 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
559 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
560 BEGIN
561 --This procedure returns excise amounts as per transaction quantity
562 --If p_breakup_type is RG23D, then total tax amount should be added to excise amount instead of taking
563 --mod_cr_percentage into consideration
564 ln_mod_problem_amt := 0;
565 ln_nonmod_problem_amt := 0;
566 ln_apportion_factor := jai_rcv_trx_processing_pkg.get_apportion_factor(pn_transaction_id);
567 FOR tax_rec IN (SELECT
568 jrtc.tax_type
569 , (jrtc.modified_tax_amount - jrtc.original_tax_amount) tax_amount
570 , nvl(jrtc.recoverable_flag, 'N') modvat_flag
571 , nvl(jrtc.currency_code, jai_rcv_trx_processing_pkg.gv_func_curr) currency
572 , nvl(decode(pv_breakup_type, 'RG23D', 100, jtc.mod_cr_percentage), 0) mod_cr_percentage
573 , nvl(jtc.rounding_factor, 0) rnd
574 FROM
575 jai_retro_tax_changes jrtc
576 , jai_cmn_taxes_all jtc
577 , jai_retro_line_changes jrlc
578 WHERE jrlc.doc_line_id = pn_shipment_line_id
579 AND jrtc.line_change_id = jrlc.line_change_id
580 AND jrlc.doc_type = 'RECEIPT'
581 AND jtc.tax_id = jrtc.tax_id
582 AND jrlc.line_change_id = pn_line_change_id
583 )
584 LOOP
585 IF tax_rec.currency <> jai_rcv_trx_processing_pkg.gv_func_curr
586 THEN
587 ln_curr_conv := NVL(pn_curr_conv_rate, 1);
588 ELSE
589 ln_curr_conv := 1;
590 END IF;
591
592 IF pv_breakup_type = 'RG23D'
593 THEN -- trading case
594 IF upper(tax_rec.tax_type) = JAI_CONSTANTS.excise_regime
595 THEN
596 pr_tax.basic_excise := pr_tax.basic_excise + round(tax_rec.tax_amount * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
597 ELSIF upper(tax_rec.tax_type) = JAI_CONSTANTS.tax_type_exc_additional
598 THEN
599 pr_tax.addl_excise := pr_tax.addl_excise + round(tax_rec.tax_amount * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
600 ELSIF upper(tax_rec.tax_type) = JAI_CONSTANTS.tax_type_exc_other
601 THEN
602 pr_tax.other_excise := pr_tax.other_excise + round(tax_rec.tax_amount * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
603 ELSIF tax_rec.tax_type = JAI_CONSTANTS.tax_type_cvd
604 THEN
605 pr_tax.cvd := pr_tax.cvd + round(tax_rec.tax_amount * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
606 ELSIF tax_rec.tax_type = jai_constants.tax_type_exc_edu_cess
607 THEN
608 pr_tax.excise_edu_cess := pr_tax.excise_edu_cess + round(tax_rec.tax_amount * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
609 ELSIF tax_rec.tax_type = jai_constants.tax_type_cvd_edu_cess
610 THEN
611 pr_tax.cvd_edu_cess := pr_tax.cvd_edu_cess + round(tax_rec.tax_amount * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
612 ELSIF tax_rec.tax_type = jai_constants.tax_type_sh_cvd_edu_cess
613 THEN
614 pr_tax.sh_cvd_edu_cess := nvl(pr_tax.sh_cvd_edu_cess,0) + round(tax_rec.tax_amount * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
615 ELSIF tax_rec.tax_type = jai_constants.tax_type_sh_exc_edu_cess
616 THEN
617 pr_tax.sh_exc_edu_cess := nvl(pr_tax.sh_exc_edu_cess,0) + round(tax_rec.tax_amount * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
618 ELSIF tax_rec.tax_type = jai_constants.tax_type_add_cvd
619 THEN
620 pr_tax.addl_cvd := pr_tax.addl_cvd + round(tax_rec.tax_amount * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
621 ELSE
622 pr_tax.non_cenvat := pr_tax.non_cenvat + round(tax_rec.tax_amount * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
623 END IF;
624 ELSE -- manufacturing case
625 IF tax_rec.modvat_flag = 'Y' AND
626 upper(tax_rec.tax_type) IN (JAI_CONSTANTS.excise_regime, JAI_CONSTANTS.tax_type_exc_additional,
627 JAI_CONSTANTS.tax_type_exc_other, JAI_CONSTANTS.tax_type_cvd,
628 JAI_CONSTANTS.tax_type_add_cvd,
629 jai_constants.tax_type_exc_edu_cess,
630 jai_constants.tax_type_cvd_edu_cess,
631 jai_constants.tax_type_sh_cvd_edu_cess,
632 jai_constants.tax_type_sh_exc_edu_cess)
633 THEN
634 IF upper(tax_rec.tax_type) = JAI_CONSTANTS.excise_regime
635 THEN
636 pr_tax.basic_excise := pr_tax.basic_excise
637 + round(tax_rec.tax_amount * (tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
638 pr_tax.non_cenvat := pr_tax.non_cenvat
639 + round(tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
640 ELSIF upper(tax_rec.tax_type) = JAI_CONSTANTS.tax_type_exc_additional
641 THEN
642 pr_tax.addl_excise := pr_tax.addl_excise
643 + round(tax_rec.tax_amount * (tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
644 pr_tax.non_cenvat := pr_tax.non_cenvat
645 + round(tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
646 ELSIF upper(tax_rec.tax_type) = JAI_CONSTANTS.tax_type_exc_other
647 THEN
648 pr_tax.other_excise := pr_tax.other_excise
649 + round(tax_rec.tax_amount * (tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
650 pr_tax.non_cenvat := pr_tax.non_cenvat
651 + round(tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
652 ELSIF tax_rec.tax_type IN (JAI_CONSTANTS.tax_type_cvd)
653 THEN
654 pr_tax.cvd := pr_tax.cvd
655 + round(tax_rec.tax_amount * (tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
656 pr_tax.non_cenvat := pr_tax.non_cenvat
657 + round(tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
658 ELSIF tax_rec.tax_type IN ( jai_constants.tax_type_add_cvd)
659 THEN
660 pr_tax.addl_cvd := pr_tax.addl_cvd
661 + round(tax_rec.tax_amount * (tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
662 pr_tax.non_cenvat := pr_tax.non_cenvat
663 + round(tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
664 ELSIF tax_rec.tax_type = jai_constants.tax_type_exc_edu_cess
665 THEN
666 pr_tax.excise_edu_cess := pr_tax.excise_edu_cess +
667 + round(tax_rec.tax_amount * (tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
668 pr_tax.non_cenvat := pr_tax.non_cenvat
669 + round(tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
670 ELSIF tax_rec.tax_type = jai_constants.tax_type_cvd_edu_cess
671 THEN
672 pr_tax.cvd_edu_cess := pr_tax.cvd_edu_cess
673 + round(tax_rec.tax_amount * (tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
674 pr_tax.non_cenvat := pr_tax.non_cenvat
675 + round(tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
676 ELSIF tax_rec.tax_type = jai_constants.tax_type_sh_exc_edu_cess
677 THEN
678 pr_tax.sh_exc_edu_cess := nvl(pr_tax.sh_exc_edu_cess,0)+
679 + round(tax_rec.tax_amount * (tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
680 pr_tax.non_cenvat := pr_tax.non_cenvat
681 + round(tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
682 ELSIF tax_rec.tax_type = jai_constants.tax_type_sh_cvd_edu_cess
683 THEN
684 pr_tax.sh_cvd_edu_cess := nvl(pr_tax.sh_cvd_edu_cess,0)+
685 + round(tax_rec.tax_amount * (tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
686 pr_tax.non_cenvat := pr_tax.non_cenvat
687 + round(tax_rec.tax_amount * (1 - tax_rec.mod_cr_percentage/100) * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
688 ELSE
689 ln_mod_problem_amt := ln_mod_problem_amt
690 + round(tax_rec.tax_amount * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
691 END IF;
692 ELSIF tax_rec.modvat_flag = 'N' and tax_rec.tax_type NOT IN (JAI_CONSTANTS.tax_type_tds, JAI_CONSTANTS.tax_type_modvat_recovery)
693 THEN
694 pr_tax.non_cenvat := pr_tax.non_cenvat
695 + round(tax_rec.tax_amount * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
696 ELSE
697 ln_nonmod_problem_amt := ln_nonmod_problem_amt
698 + round(tax_rec.tax_amount * ln_curr_conv * ln_apportion_factor, tax_rec.rnd);
699 END IF;
700 END IF;
701 END LOOP;
702 --logging for debug
703 IF (ln_proc_level >= ln_dbg_level)
704 THEN
705 FND_LOG.STRING( ln_proc_level
706 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
707 , 'Exit procedure'
708 );
709 END IF; -- (ln_proc_level>=ln_dbg_level)
710 EXCEPTION
711 WHEN OTHERS THEN
712 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
713 THEN
714 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
715 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
716 , Sqlcode||Sqlerrm);
717 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
718 RAISE;
719 END Get_Tax_Amount_Breakup;
720
721 --==========================================================================
722 -- Function NAME:
723 --
724 -- Get_Jai_Rcv_Trans_Record Private
725 --
726 -- DESCRIPTION:
727 --
728 -- This procedure is used to get the jai transaction record for a
729 -- given transaction id
730 --
731 --
732 -- PARAMETERS:
733 -- In: pn_transaction_id NUMBER transaction identifier
734 --
735 -- DESIGN REFERENCES:
736 -- JAI_Retroprice_TDD.doc
737 --
738 -- CHANGE HISTORY:
739 --
740 -- 14-JAN-2008 Eric Ma created
741 --==========================================================================
742 Function Get_Jai_Rcv_Trans_Record
743 ( pn_transaction_id jai_rcv_transactions.transaction_id%TYPE
744 )
745 RETURN jai_rcv_transactions%ROWTYPE
746 IS
747
748 CURSOR get_jai_rcv_transactions_cur
749 IS
750 SELECT
751 *
752 FROM
753 jai_rcv_transactions
754 WHERE transaction_id = pn_transaction_id;
755
756
757 jai_rcv_transactions_rec jai_rcv_transactions%ROWTYPE;
758
759 lv_procedure_name VARCHAR2(40):='Get_Jai_Rcv_Trans_Record';
760 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
761 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
762
763 BEGIN
764 --logging for debug
765 IF (ln_proc_level >= ln_dbg_level)
766 THEN
767 FND_LOG.STRING( ln_proc_level
768 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
769 , 'Enter procedure'
770 );
771 END IF; --l_proc_level>=l_dbg_level
772
773 OPEN get_jai_rcv_transactions_cur;
774 FETCH get_jai_rcv_transactions_cur
775 INTO jai_rcv_transactions_rec;
776 CLOSE get_jai_rcv_transactions_cur;
777
778 --logging for debug
779 IF (ln_proc_level >= ln_dbg_level)
780 THEN
781 FND_LOG.STRING( ln_proc_level
782 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
783 , 'Exit procedure'
784 );
785 END IF; -- (ln_proc_level>=ln_dbg_level)
786 RETURN jai_rcv_transactions_rec;
787 EXCEPTION
788 WHEN OTHERS THEN
789 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
790 THEN
791 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
792 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
793 , Sqlcode||Sqlerrm);
794 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
795 RAISE;
796 END Get_Jai_Rcv_Trans_Record;
797
798 --==========================================================================
799 -- Function NAME:
800 --
801 -- Get_Jai_Rcv_Lines_Record Private
802 --
803 -- DESCRIPTION:
804 --
805 -- This procedure is used to get the jai_rcv_lines record for a
806 -- given transaction id
807 --
808 --
809 -- PARAMETERS:
810 -- In: pn_transaction_id NUMBER transaction identifier
811 --
812 -- DESIGN REFERENCES:
813 -- JAI_Retroprice_TDD.doc
814 --
815 -- CHANGE HISTORY:
816 --
817 -- 14-JAN-2008 Eric Ma created
818 --==========================================================================
819 Function Get_Jai_Rcv_Lines_Record
820 ( pn_transaction_id jai_rcv_transactions.transaction_id%TYPE
821 )
822 RETURN jai_rcv_lines%ROWTYPE
823 IS
824
825 CURSOR get_jai_rcv_lines_cur
826 IS
827 SELECT
828 *
829 FROM
830 jai_rcv_lines
831 WHERE transaction_id = pn_transaction_id;
832
833 jai_rcv_lines_rec jai_rcv_lines%ROWTYPE;
834
835 lv_procedure_name VARCHAR2(40):='Get_Jai_Rcv_Trans_Record';
836 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
837 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
838 BEGIN
839 --logging for debug
840 IF (ln_proc_level >= ln_dbg_level)
841 THEN
842 FND_LOG.STRING( ln_proc_level
843 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
844 , 'Enter procedure'
845 );
846 END IF; --l_proc_level>=l_dbg_level
847
848 OPEN get_jai_rcv_lines_cur;
849 FETCH get_jai_rcv_lines_cur
850 INTO jai_rcv_lines_rec;
851 CLOSE get_jai_rcv_lines_cur;
852
853 --logging for debug
854 IF (ln_proc_level >= ln_dbg_level)
855 THEN
856 FND_LOG.STRING( ln_proc_level
857 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
858 , 'Exit procedure'
859 );
860 END IF; -- (ln_proc_level>=ln_dbg_level)
861
862 RETURN jai_rcv_lines_rec;
863 EXCEPTION
864 WHEN OTHERS
865 THEN
866 RAISE;
867 END Get_Jai_Rcv_Lines_Record;
868
869 --==========================================================================
870 -- PROCEDURE NAME:
871 --
872 -- Do_Unclaim Private
873 --
874 -- DESCRIPTION:
875 --
876 -- This procedure is used to unclaim the tax on receipt or processing the
877 -- costing
878 --
879 --
880 -- PARAMETERS:
881 -- In:pn_organization_id NUMBER inventory organization identifier
882 -- pn_transaction_id NUMBER transaction identifier
883 -- pn_amount NUMBER non recoverable amount
884 -- pn_version_number NUMBER receipt version number
885 -- DESIGN REFERENCES:
886 -- JAI_Retroprice_TDD.doc
887 --
888 -- CHANGE HISTORY:
889 --
890 -- 14-JAN-2008 Eric Ma created
891 -- 01-Feb-2008 Eric Ma Add log and change code for bug #6788048
892 --==========================================================================
893
894 PROCEDURE Do_Unclaim
895 ( pn_organization_id IN NUMBER
896 , pn_transaction_id IN NUMBER
897 , pn_amount IN NUMBER
898 , pn_version_number IN NUMBER
899 )
900 IS
901 CURSOR rcv_transactions_cur IS
902 SELECT
903 destination_type_code
904 , shipment_line_id
905 FROM rcv_transactions
906 WHERE transaction_id = pn_transaction_id;
907
908
909 CURSOR get_mtl_parameters_cur IS
910 SELECT
911 primary_cost_method
912 , expense_account
913 , purchase_price_var_account
914 , organization_code
915 FROM
916 mtl_parameters
917 WHERE
918 organization_id = pn_organization_id;
919
920 CURSOR get_rcv_parameters_cur IS
921 SELECT
922 retroprice_adj_account_id
923 , receiving_account_id
924 FROM
925 rcv_parameters
926 WHERE
927 organization_id = pn_organization_id;
928
929 lv_primary_cost_method mtl_parameters.primary_cost_method%TYPE;
930 lv_destination_type_code jai_rcv_transactions.destination_type_code%TYPE;
931 ln_shipment_line_id jai_rcv_transactions.shipment_line_id%TYPE;
932 lv_receipt_num jai_rcv_transactions.receipt_num%TYPE;
933 ln_expense_account mtl_parameters.expense_account%TYPE;
934 lv_process_message VARCHAR2(500);
935 lv_process_status VARCHAR2(500);
936 lv_code_path VARCHAR2(500);
937 ln_purchase_price_var_account mtl_parameters.purchase_price_var_account%TYPE;
938 ln_retroprice_adj_account_id rcv_parameters.retroprice_adj_account_id%TYPE;
939 func_curr_det_rec jai_plsql_cache_pkg.func_curr_details;
940 lv_period_name gl_periods.period_name%TYPE;
941 ln_receiving_account_id rcv_parameters.receiving_account_id%TYPE;
942 ln_user_id NUMBER := fnd_global.user_id;
943 lv_procedure_name VARCHAR2(40):='Do_Unclaim';
944 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
945 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
946 lv_organization_code mtl_parameters.organization_code%TYPE;/*added by rchandan*/
947 BEGIN
948 --logging for debug
949 IF (ln_proc_level >= ln_dbg_level)
950 THEN
951 FND_LOG.STRING( ln_proc_level
952 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
953 , 'Enter procedure'
954 );
955 END IF; --l_proc_level>=l_dbg_level
956
957 OPEN get_mtl_parameters_cur;
958 FETCH get_mtl_parameters_cur
959 INTO
960 lv_primary_cost_method
961 , ln_expense_account
962 , ln_purchase_price_var_account
963 , lv_organization_code;
964 CLOSE get_mtl_parameters_cur;
965
966 IF (ln_proc_level >= ln_dbg_level)
967 THEN
968 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 1');
969 FND_FILE.PUT_LINE(fnd_file.log,'lv_primary_cost_method '|| lv_primary_cost_method);
970 FND_FILE.PUT_LINE(fnd_file.log,'ln_expense_account '|| ln_expense_account);
971 FND_FILE.PUT_LINE(fnd_file.log,'ln_purchase_price_var_account '|| ln_purchase_price_var_account);
972 FND_FILE.PUT_LINE(fnd_file.log,'lv_organization_code '|| lv_organization_code);
973 END IF; --l_proc_level>=l_dbg_level
974
975 OPEN get_rcv_parameters_cur;
976 FETCH get_rcv_parameters_cur
977 INTO
978 ln_retroprice_adj_account_id,
979 ln_receiving_account_id;
980 CLOSE get_rcv_parameters_cur;
981
982 IF (ln_proc_level >= ln_dbg_level)
983 THEN
984 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 2');
985 FND_FILE.PUT_LINE(fnd_file.log,'ln_retroprice_adj_account_id '|| ln_retroprice_adj_account_id);
986 FND_FILE.PUT_LINE(fnd_file.log,'ln_receiving_account_id '|| ln_receiving_account_id);
987 END IF; --l_proc_level>=l_dbg_level
988
989 OPEN rcv_transactions_cur;
990 FETCH rcv_transactions_cur
991 INTO
992 lv_destination_type_code
993 , ln_shipment_line_id;
994 CLOSE rcv_transactions_cur;
995
996 IF (ln_proc_level >= ln_dbg_level)
997 THEN
998 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 3');
999 FND_FILE.PUT_LINE(fnd_file.log,'lv_destination_type_code '|| lv_destination_type_code);
1000 FND_FILE.PUT_LINE(fnd_file.log,'ln_shipment_line_id '|| ln_shipment_line_id);
1001 END IF; --l_proc_level>=l_dbg_level
1002
1003 IF (lv_destination_type_code = 'EXPENSE')
1004 THEN -- if the destination_type_code in DELIVER transaction is EXPENSE
1005
1006 -- Debit the account calling the following procedure
1007 jai_rcv_accounting_pkg.process_transaction
1008 ( p_transaction_id => pn_transaction_id
1009 , p_acct_type => 'REGULAR'
1010 , p_acct_nature => 'Expense Accounting'
1011 , p_source_name => 'Purchasing India'
1012 , p_category_name => 'Receiving India'
1013 , p_code_combination_id => ln_expense_account
1014 , p_entered_dr => pn_amount
1015 , p_entered_cr => NULL
1016 , p_currency_code => 'INR'
1017 , p_accounting_date => SYSDATE
1018 , p_reference_10 => NULL
1019 , p_reference_23 => 'jai_retro_prc_pkg.do_accounting'
1020 , p_reference_24 => 'rcv_transactions'
1021 , p_reference_25 => 'transaction_id'
1022 , p_reference_26 => to_char(pn_transaction_id)
1023 , p_destination => 'G'
1024 , p_simulate_flag => 'N'
1025 , p_codepath => lv_code_path
1026 , p_process_message => lv_process_message
1027 , p_process_status => lv_process_status
1028 , p_reference_name => 'RETRO CENVAT CLAIMS ' || pn_version_number
1029 , p_reference_id => 1
1030 );
1031
1032 IF (lv_process_status IN ('X','E'))
1033 THEN
1034 raise_application_error(-20120,'Unclaim accounting returned with error for Expense : ' || lv_process_message);
1035 END IF; -- (lv_process_status IN ('X','E'))
1036 --logging for debug
1037 IF (ln_proc_level >= ln_dbg_level)
1038 THEN
1039 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 4');
1040 END IF; --l_proc_level>=l_dbg_level
1041
1042 -- Credit the receiving_account_id by calling the following procedure
1043
1044 jai_rcv_accounting_pkg.process_transaction
1045 ( p_transaction_id => pn_transaction_id
1046 , p_acct_type => 'REGULAR'
1047 , p_acct_nature => 'Expense Accounting'
1048 , p_source_name => 'Purchasing India'
1049 , p_category_name => 'Receiving India'
1050 , p_code_combination_id => ln_receiving_account_id
1051 , p_entered_dr => NULL
1052 , p_entered_cr => pn_amount
1053 , p_currency_code => 'INR'
1054 , p_accounting_date => SYSDATE
1055 , p_reference_10 => NULL
1056 , p_reference_23 => 'jai_retro_prc_pkg.do_accounting'
1057 , p_reference_24 => 'rcv_transactions'
1058 , p_reference_25 => 'transaction_id'
1059 , p_reference_26 => to_char(pn_transaction_id)
1060 , p_destination => 'G'
1061 , p_simulate_flag => 'N'
1062 , p_codepath => lv_code_path
1063 , p_process_message => lv_process_message
1064 , p_process_status => lv_process_status
1065 , p_reference_name => 'RETRO CENVAT CLAIMS ' || pn_version_number
1066 , p_reference_id => 2
1067 );
1068
1069 IF (lv_process_status IN ('X','E'))
1070 THEN
1071 raise_application_error(-20120,'Unclaim accounting returned with error for Expense : ' || lv_process_message);
1072 END IF; -- (lv_process_status IN ('X','E'))
1073
1074 IF (ln_proc_level >= ln_dbg_level)
1075 THEN
1076 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 5');
1077 END IF; --l_proc_level>=l_dbg_level
1078
1079 ELSIF (lv_primary_cost_method = 1)
1080 THEN -- if its Standard Costing
1081
1082 IF (ln_proc_level >= ln_dbg_level)
1083 THEN
1084 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 6');
1085 END IF; --l_proc_level>=l_dbg_level
1086
1087 -- Debit the account by calling
1088 jai_rcv_accounting_pkg.process_transaction
1089 ( p_transaction_id => pn_transaction_id
1090 , p_acct_type => 'REGULAR'
1091 , p_acct_nature => 'Standard Costing'
1092 , p_source_name => 'Inventory India'
1093 , p_category_name => 'MTL'
1094 , p_code_combination_id => ln_purchase_price_var_account
1095 , p_entered_dr => pn_amount
1096 , p_entered_cr => NULL
1097 , p_currency_code => 'INR'
1098 , p_accounting_date => SYSDATE
1099 , p_reference_10 => NULL
1100 , p_reference_23 => 'jai_retro_prc_pkg.do_accounting'
1101 , p_reference_24 => 'rcv_transactions'
1102 , p_reference_25 => 'transaction_id'
1103 , p_reference_26 => to_char(pn_transaction_id)
1104 , p_destination => 'S'
1105 , p_simulate_flag => 'N'
1106 , p_codepath => lv_code_path
1107 , p_process_message => lv_process_message
1108 , p_process_status => lv_process_status
1109 , p_reference_name => 'RETRO CENVAT CLAIMS ' || pn_version_number
1110 , p_reference_id => 1
1111 );
1112
1113 IF (lv_process_status IN ('X','E'))
1114 THEN
1115 raise_application_error(-20120,'Unclaim accounting returned with error for Standard Costing : ' || lv_process_message);
1116 END IF; -- (lv_process_status IN ('X','E'))
1117
1118 IF (ln_proc_level >= ln_dbg_level)
1119 THEN
1120 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 7');
1121 END IF; --l_proc_level>=l_dbg_level
1122
1123 -- Credit the receiving account by calling
1124 jai_rcv_accounting_pkg.process_transaction
1125 ( p_transaction_id => pn_transaction_id
1126 , p_acct_type => 'REGULAR'
1127 , p_acct_nature => 'Standard Costing'
1128 , p_source_name => 'Inventory India'
1129 , p_category_name => 'MTL'
1130 , p_code_combination_id => ln_receiving_account_id
1131 , p_entered_dr => NULL
1132 , p_entered_cr => pn_amount
1133 , p_currency_code => 'INR'
1134 , p_accounting_date => SYSDATE
1135 , p_reference_10 => NULL
1136 , p_reference_23 => 'jai_retro_prc_pkg.do_accounting'
1137 , p_reference_24 => 'rcv_transactions'
1138 , p_reference_25 => 'transaction_id'
1139 , p_reference_26 => to_char(pn_transaction_id)
1140 , p_destination => 'S'
1141 , p_simulate_flag => 'N'
1142 , p_codepath => lv_code_path
1143 , p_process_message => lv_process_message
1144 , p_process_status => lv_process_status
1145 , p_reference_name => 'RETRO CENVAT CLAIMS ' || pn_version_number
1146 , p_reference_id => 2
1147 );
1148
1149 IF (lv_process_status IN ('X','E'))
1150 THEN
1151 raise_application_error(-20120,'Unclaim accounting returned with error for Standard Costing : ' || lv_process_message);
1152 END IF; -- (lv_process_status IN ('X','E'))
1153
1154 IF (ln_proc_level >= ln_dbg_level)
1155 THEN
1156 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 8');
1157 END IF; --l_proc_level>=l_dbg_level
1158
1159 ELSIF (lv_primary_cost_method = 2)
1160 THEN -- if its Average costing
1161
1162 IF (ln_proc_level >= ln_dbg_level)
1163 THEN
1164 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 9');
1165 END IF; --l_proc_level>=l_dbg_level
1166
1167 -- Get the organization details by calling the following function
1168 func_curr_det_rec := jai_plsql_cache_pkg.return_sob_curr(p_org_id => pn_organization_id);
1169
1170
1171 IF (ln_proc_level >= ln_dbg_level)
1172 THEN
1173 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 10');
1174 END IF; --l_proc_level>=l_dbg_level
1175
1176 -- Get the period name using the following query
1177 SELECT gd.period_name
1178 INTO lv_period_name
1179 FROM
1180 gl_ledgers gle
1181 , gl_periods gd
1182 WHERE gle.ledger_id = func_curr_det_rec.ledger_id
1183 AND gd.period_set_name = gle.period_set_name
1184 -- AND SYSDATE BETWEEN gd.start_date AND gd.end_date bug #6788048
1185 --eric changed on Feb 1, 2008 for bug #6788048 begin
1186 ---------------------------------------------
1187 AND SYSDATE >=TRUNC(gd.start_date)
1188 AND SYSDATE < TRUNC(gd.end_date+1)
1189 ---------------------------------------------
1190 --eric changed on Feb 1, 2008 for bug #6788048 end
1191 AND gd.adjustment_period_flag = 'N';
1192
1193 IF (ln_proc_level >= ln_dbg_level)
1194 THEN
1195 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 11');
1196 FND_FILE.PUT_LINE(fnd_file.log,'lv_period_name '|| lv_period_name);
1197 END IF; --l_proc_level>=l_dbg_level
1198
1199 -- Get the receipt_num
1200 SELECT receipt_num
1201 INTO lv_receipt_num
1202 FROM jai_rcv_lines
1203 WHERE shipment_line_id = ln_shipment_line_id;
1204
1205 IF (ln_proc_level >= ln_dbg_level)
1206 THEN
1207 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 12');
1208 FND_FILE.PUT_LINE(fnd_file.log,'lv_receipt_num '|| lv_receipt_num);
1209 END IF; --l_proc_level>=l_dbg_level
1210
1211 -- Debit the Retroprice account by calling
1212 jai_rcv_accounting_pkg.gl_entry
1213 ( p_organization_id => pn_organization_id
1214 , p_organization_code => func_curr_det_rec.organization_code
1215 , p_set_of_books_id => func_curr_det_rec.ledger_id
1216 , p_credit_amount => NULL
1217 , p_debit_amount => pn_amount
1218 , p_cc_id => ln_retroprice_adj_account_id
1219 , p_je_source_name => 'Inventory India'
1220 , p_je_category_name => 'MTL'
1221 , p_created_by => ln_user_id
1222 , p_accounting_date => SYSDATE
1223 , p_currency_code => 'INR'
1224 , p_currency_conversion_date => NULL
1225 , p_currency_conversion_type => NULL
1226 , p_currency_conversion_rate => NULL
1227 , p_reference_10 => 'JAI Retropricing Unclaim Entry for the Receipt Number '||lv_receipt_num ||' for the Organization code '||lv_organization_code
1228 , p_reference_23 => 'JAI_RETRO_PRC_PKG.Do_Accounting'
1229 , p_reference_24 => 'rcv_transactions'
1230 , p_reference_25 => 'transaction_id'
1231 , p_reference_26 => to_char(pn_transaction_id)
1232 , p_process_message => lv_process_message
1233 , p_process_status => lv_process_status
1234 , p_codepath => lv_code_path
1235 );
1236
1237 --FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 9');
1238
1239 IF (lv_process_status IN ('X','E'))
1240 THEN
1241 raise_application_error(-20120,'Unclaim GL Entry returned with error for Average Costing : '|| lv_process_message);
1242 END IF; -- (lv_process_status IN ('X','E'))
1243
1244 IF (ln_proc_level >= ln_dbg_level)
1245 THEN
1246 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 13');
1247 END IF; --l_proc_level>=l_dbg_level
1248
1249 jai_rcv_journal_pkg.insert_row
1250 ( p_organization_id => pn_organization_id
1251 , p_organization_code => func_curr_det_rec.organization_code
1252 , p_receipt_num => lv_receipt_num
1253 , p_transaction_id => pn_transaction_id
1254 , p_transaction_date => SYSDATE
1255 , p_shipment_line_id => ln_shipment_line_id
1256 , p_acct_type => 'REGULAR'
1257 , p_acct_nature => 'Average Costing'
1258 , p_source_name => 'Inventory India'
1259 , p_category_name => 'MTL'
1260 , p_code_combination_id => ln_retroprice_adj_account_id
1261 , p_entered_dr => pn_amount
1262 , p_entered_cr => NULL
1263 , p_transaction_type => 'DELIVER'
1264 , p_period_name => lv_period_name
1265 , p_currency_code => 'INR'
1266 , p_currency_conversion_type => NULL
1267 , p_currency_conversion_date => NULL
1268 , p_currency_conversion_rate => NULL
1269 , p_simulate_flag => 'N'
1270 , p_process_status => lv_process_status
1271 , p_process_message => lv_process_message
1272 , p_reference_name => 'RETRO CENVAT CLAIMS ' || pn_version_number
1273 , p_reference_id => 1
1274 );
1275
1276 IF (lv_process_status IN ('X','E'))
1277 THEN
1278 raise_application_error(-20120,'Unclaim Journal Entry returned with error for Average Costing : ' || lv_process_message);
1279 END IF; -- (lv_process_status IN ('X','E'))
1280
1281 IF (ln_proc_level >= ln_dbg_level)
1282 THEN
1283 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 14');
1284 END IF; --l_proc_level>=l_dbg_level
1285
1286 -- Credit the receiving account by calling
1287
1288 jai_rcv_accounting_pkg.gl_entry
1289 ( p_organization_id => pn_organization_id
1290 , p_organization_code => func_curr_det_rec.organization_code
1291 , p_set_of_books_id => func_curr_det_rec.ledger_id
1292 , p_credit_amount => pn_amount
1293 , p_debit_amount => NULL
1294 , p_cc_id => ln_receiving_account_id
1295 , p_je_source_name => 'Inventory India'
1296 , p_je_category_name => 'MTL'
1297 , p_created_by => ln_user_id
1298 , p_accounting_date => SYSDATE
1299 , p_currency_code => 'INR'
1300 , p_currency_conversion_date => NULL
1301 , p_currency_conversion_type => NULL
1302 , p_currency_conversion_rate => NULL
1303 , p_reference_10 => 'India Localization.....'
1304 , p_reference_23 => 'jai_retro_prc_pkg.do_accounting'
1305 , p_reference_24 => 'rcv_transactions'
1306 , p_reference_25 => 'transaction_id'
1307 , p_reference_26 => to_char(pn_transaction_id)
1308 , p_process_message => lv_process_message
1309 , p_process_status => lv_process_status
1310 , p_codepath => lv_code_path
1311 );
1312
1313 IF (lv_process_status IN ('X','E'))
1314 THEN
1315 raise_application_error(-20120,'Unclaim GL Entry returned with error for Average Costing : ' || lv_process_message);
1316 END IF; -- (lv_process_status IN ('X','E'))
1317
1318 IF (ln_proc_level >= ln_dbg_level)
1319 THEN
1320 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 15');
1321 END IF; --l_proc_level>=l_dbg_level
1322
1323 jai_rcv_journal_pkg.insert_row
1324 ( p_organization_id => pn_organization_id
1325 , p_organization_code => func_curr_det_rec.organization_code
1326 , p_receipt_num => lv_receipt_num
1327 , p_transaction_id => pn_transaction_id
1328 , p_transaction_date => SYSDATE
1329 , p_shipment_line_id => ln_shipment_line_id
1330 , p_acct_type => 'REGULAR'
1331 , p_acct_nature => 'Average Costing'
1332 , p_source_name => 'Inventory India'
1333 , p_category_name => 'MTL'
1334 , p_code_combination_id => ln_receiving_account_id
1335 , p_entered_dr => NULL
1336 , p_entered_cr => pn_amount
1337 , p_transaction_type => 'DELIVER'
1338 , p_period_name => lv_period_name
1339 , p_currency_code => 'INR'
1340 , p_currency_conversion_type => NULL
1341 , p_currency_conversion_date => NULL
1342 , p_currency_conversion_rate => NULL
1343 , p_simulate_flag => 'N'
1344 , p_process_status => lv_process_status
1345 , p_process_message => lv_process_message
1346 , p_reference_name => 'RETRO CENVAT CLAIMS ' || pn_version_number
1347 , p_reference_id => 2
1348 );
1349
1350 --FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 12');
1351 IF (lv_process_status IN ('X','E'))
1352 THEN
1353 raise_application_error(-20120,'Unclaim Journal Entry returned with error for Average Costing : ' || lv_process_message);
1354 END IF; -- (lv_process_status IN ('X','E'))
1355
1356 IF (ln_proc_level >= ln_dbg_level)
1357 THEN
1358 FND_FILE.PUT_LINE(fnd_file.log,'Do_Unclaim 16');
1359 END IF; --l_proc_level>=l_dbg_level
1360 END IF; -- (lv_destination_type_code = 'EXPENSE')
1361
1362 --logging for debug
1363 IF (ln_proc_level >= ln_dbg_level)
1364 THEN
1365 FND_LOG.STRING( ln_proc_level
1366 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
1367 , 'Exit procedure'
1368 );
1369 END IF; -- (ln_proc_level>=ln_dbg_level)
1370
1371 EXCEPTION
1372 WHEN OTHERS THEN
1373 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1374 THEN
1375 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1376 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
1377 , Sqlcode||Sqlerrm);
1378 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1379 RAISE;
1380 END Do_Unclaim;
1381
1382
1383
1384 --==========================================================================
1385 -- PROCEDURE NAME:
1386 --
1387 -- Do_Accounting Private
1388 --
1389 -- DESCRIPTION:
1390 --
1391 -- This procedure is used to processing the accounting related logic
1392 --
1393 --
1394 -- PARAMETERS:
1395 -- In:pn_transaction_id NUMBER transaction identifier
1396 -- pn_shipment_line_id NUMBER shipemnt line identifier,
1397 -- pn_vat_amount NUMBER recoverable vat tax amount
1398 -- xv_vat_action NUMBER vat action 'CLAIM' or 'UNCLAIM'
1399 -- pn_cenvat_amount NUMBER recoverable vat tax amount
1400 -- xv_cenvat_action NUMBER vat action 'CLAIM' or 'UNCLAIM'
1401 -- pn_version_number NUMBER receipt version number
1402 -- pn_line_change_id NUMBER identifier of jai_retro_line_changes
1403 -- DESIGN REFERENCES:
1404 -- JAI_Retroprice_TDD.doc
1405 --
1406 -- CHANGE HISTORY:
1407 --
1408 -- 14-JAN-2008 Eric Ma created
1409 --==========================================================================
1410 PROCEDURE Do_Accounting
1411 ( pn_shipment_line_id IN NUMBER
1412 , pn_transaction_id IN NUMBER
1413 , pn_cenvat_amount IN NUMBER
1414 , xv_cenvat_action IN OUT NOCOPY VARCHAR2
1415 , pn_vat_amount IN NUMBER
1416 , xv_vat_action IN OUT NOCOPY VARCHAR2
1417 , pn_non_rec_amount IN NUMBER
1418 , pn_version_number IN NUMBER
1419 , pn_line_change_id IN NUMBER
1420 )
1421 IS
1422
1423 CURSOR Rcv_Trx_Cur
1424 IS
1425 SELECT
1426 transaction_id
1427 , organization_id
1428 FROM
1429 Rcv_Transactions
1430 WHERE shipment_line_id = pn_shipment_line_id
1431 AND transaction_type = 'DELIVER';
1432
1433 ln_tax_diff_tot NUMBER;
1434 lv_currency Jai_Retro_Tax_Changes.Currency_Code%TYPE;
1435 ln_curr_conv_rate Rcv_Transactions.Currency_Conversion_Rate%TYPE;
1436 ln_organization_id Rcv_Transactions.Organization_Id%TYPE;
1437 ln_recv_acct_id Rcv_Parameters.Receiving_Account_Id%TYPE;
1438 ln_ap_accrual_acc Mtl_Parameters.Ap_Accrual_Account%TYPE;
1439 ln_non_rec_amount NUMBER;
1440 lv_include_cenvat_in_costing VARCHAR2(10);
1441
1442 lv_codepath VARCHAR2(4000);
1443 lv_process_message VARCHAR2(4000);
1444 lv_process_status VARCHAR2(4000);
1445
1446 lv_procedure_name VARCHAR2(40):='Do_Accounting';
1447 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1448 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
1449
1450
1451 BEGIN
1452
1453 --logging for debug
1454 IF (ln_proc_level >= ln_dbg_level)
1455 THEN
1456 FND_LOG.STRING( ln_proc_level
1457 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
1458 , 'Enter procedure'
1459 );
1460 END IF; --l_proc_level>=l_dbg_level
1461
1462 SELECT
1463 NVL(currency_conversion_rate,1)
1464 , organization_id
1465 INTO
1466 ln_curr_conv_rate
1467 , ln_organization_id
1468 FROM
1469 Rcv_Transactions
1470 WHERE shipment_line_id = pn_shipment_line_id
1471 AND transaction_type = 'RECEIVE';
1472
1473 IF (ln_proc_level >= ln_dbg_level)
1474 THEN
1475 FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 1');
1476 FND_FILE.PUT_LINE(fnd_file.log,'ln_curr_conv_rate '|| ln_curr_conv_rate);
1477 FND_FILE.PUT_LINE(fnd_file.log,'ln_organization_id '|| ln_organization_id);
1478 END IF; --l_proc_level>=l_dbg_level
1479
1480 --Get the total tax difference from jai_retro_tax_changes for the shipment_line_id
1481 -- Get the difference in INR. Multiply with currency_conversion_rate of rcv_transactions
1482 -- if the tax is in Non INR currency
1483 SELECT
1484 SUM((modified_tax_amount - original_tax_amount) * DECODE(currency_code,'INR',1, ln_curr_conv_rate)) tax_diff_tot
1485 INTO
1486 ln_tax_diff_tot
1487 FROM
1488 Jai_Retro_Tax_Changes
1489 WHERE line_change_id = pn_line_change_id ;/*rchandan. removed sub query and replaced with pn_line_change_id*/
1490
1491 IF (ln_proc_level >= ln_dbg_level)
1492 THEN
1493 FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 2');
1494 FND_FILE.PUT_LINE(fnd_file.log,'ln_tax_diff_tot '|| ln_tax_diff_tot);
1495 END IF; --l_proc_level>=l_dbg_level
1496
1497
1498 /* eric deleted for a bug on Jan 22,2008
1499 IF lv_currency <> 'INR'
1500 THEN
1501 ln_tax_diff_tot := ln_tax_diff_tot * ln_curr_conv_rate;
1502 END IF;
1503 */
1504
1505 --FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 2');
1506
1507 --IF the total difference is NOT EQUAL to ZERO THEN
1508 IF ln_tax_diff_tot <> 0
1509 THEN
1510 --Get the receiving_account_id from rcv_parameters for the current organization id
1511 SELECT
1512 receiving_account_id
1513 INTO
1514 ln_recv_acct_id
1515 FROM
1516 Rcv_Parameters
1517 WHERE organization_id = ln_organization_id;
1518
1519 IF (ln_proc_level >= ln_dbg_level)
1520 THEN
1521 FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 3');
1522 FND_FILE.PUT_LINE(fnd_file.log,'ln_recv_acct_id '|| ln_recv_acct_id);
1523 END IF; --l_proc_level>=l_dbg_level
1524
1525 --Debit the account by calling the following procedure
1526 Jai_Rcv_Accounting_Pkg.Process_Transaction
1527 ( p_transaction_id => pn_transaction_id
1528 , p_acct_type => 'REGULAR'
1529 , p_acct_nature => 'Receiving'
1530 , p_source_name => 'Purchasing India'
1531 , p_category_name => 'Receiving India'
1532 , p_code_combination_id => ln_recv_acct_id--receiving_account_id
1533 , p_entered_dr => ln_tax_diff_tot --ln_amount --Total tax difference in INR
1534 , p_entered_cr => NULL
1535 , p_currency_code => 'INR'
1536 , p_accounting_date => SYSDATE
1537 , p_reference_10 => NULL
1538 , p_reference_23 => 'jai_retro_prc_pkg.do_accounting'
1539 , p_reference_24 => 'rcv_transactions'
1540 , p_reference_25 => 'transaction_id'
1541 , p_reference_26 => to_char(pn_transaction_id)
1542 , p_destination => 'G' --G indicates GL Interface Entries,
1543 , p_simulate_flag => 'N'
1544 , p_codepath => lv_codepath
1545 , p_process_message => lv_process_message -- OUT parameter
1546 , p_process_status => lv_process_status -- OUT parameter
1547 , p_reference_name => 'RETRO CENVAT CLAIMS ' ||pn_version_number
1548 , p_reference_id => 1
1549 );
1550 --FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 3');
1551
1552 IF lv_process_status IN ('X', 'E')
1553 THEN
1554 raise_application_error(-20120,'Receive Accounting Entry retruned with error : '||lv_process_message);
1555 END IF;
1556
1557 IF (ln_proc_level >= ln_dbg_level)
1558 THEN
1559 FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 4');
1560 END IF; --l_proc_level>=l_dbg_level
1561
1562 --Get the ap_accrual_account from mtl_parameters for the current organization id
1563 SELECT
1564 Ap_Accrual_Account
1565 INTO
1566 ln_ap_accrual_acc
1567 FROM
1568 Mtl_Parameters
1569 WHERE organization_id = ln_organization_id;
1570
1571 IF (ln_proc_level >= ln_dbg_level)
1572 THEN
1573 FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 5');
1574 FND_FILE.PUT_LINE(fnd_file.log,'ln_ap_accrual_acc '|| ln_ap_accrual_acc);
1575 END IF; --l_proc_level>=l_dbg_level
1576
1577 --Credit the account by calling the following procedure
1578 Jai_Rcv_Accounting_Pkg.Process_Transaction
1579 ( p_transaction_id => pn_transaction_id
1580 , p_acct_type => 'REGULAR'
1581 , p_acct_nature => 'Receiving'
1582 , p_source_name => 'Purchasing India'
1583 , p_category_name => 'Receiving India'
1584 , p_code_combination_id => ln_ap_accrual_acc --ap_accrual_account
1585 , p_entered_dr => NULL
1586 , p_entered_cr => ln_tax_diff_tot --ln_amount --Total tax difference in INR
1587 , p_currency_code => 'INR'
1588 , p_accounting_date => SYSDATE
1589 , p_reference_10 => NULL
1590 , p_reference_23 => 'jai_retro_prc_pkg.do_accounting'
1591 , p_reference_24 => 'rcv_transactions'
1592 , p_reference_25 => 'transaction_id'
1593 , p_reference_26 => to_char(pn_transaction_id)
1594 , p_destination => 'G' --GL Interface Entries
1595 , p_simulate_flag => 'N'
1596 , p_codepath => lv_codepath
1597 , p_process_message => lv_process_message -- OUT parameter
1598 , p_process_status => lv_process_status -- OUT parameter
1599 , p_reference_name => 'RETRO CENVAT CLAIMS ' ||pn_version_number
1600 , p_reference_id => 2
1601 );
1602
1603 -- FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 4');
1604 IF lv_process_status IN ('X', 'E')
1605 THEN
1606 raise_application_error(-20120,'Receive Accounting Entry retruned with error : '||lv_process_message);
1607 END IF;
1608
1609 IF (ln_proc_level >= ln_dbg_level)
1610 THEN
1611 FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 6');
1612 END IF; --l_proc_level>=l_dbg_level
1613
1614 ln_non_rec_amount := pn_non_rec_amount;
1615
1616 --LOOP through the DELIVER transactions for the shipment_line_id from rcv_transactions
1617 FOR trx_rec IN Rcv_Trx_Cur
1618 LOOP
1619 IF pn_cenvat_amount <> 0 --if the recoverable cenvat amount is not zero
1620 THEN
1621 --Call the following function to decide if cenvat needs to be included in costing
1622 lv_include_cenvat_in_costing := Jai_Rcv_Deliver_Rtr_Pkg.Include_Cenvat_In_Costing( p_transaction_id => trx_rec.transaction_id --DELIEVR transaction id
1623 , p_process_message => lv_process_message
1624 , p_process_status => lv_process_status
1625 , p_codepath => lv_codepath
1626 );
1627
1628 IF lv_include_cenvat_in_costing = 'Y' OR xv_cenvat_action = 'UNCLAIM' THEN
1629 FND_FILE.PUT_LINE(fnd_file.log,' lv_include_cenvat_in_costing : '|| lv_include_cenvat_in_costing);
1630 xv_cenvat_action := 'UNCLAIMED'; -- CENVAT is included in Unclaim. do_cenvat_claim is no more called
1631 ln_non_rec_amount := ln_non_rec_amount + pn_cenvat_amount; -- include recoverable cenvat in Non recoverable amount
1632 END IF;
1633
1634 END IF;
1635
1636 IF (ln_proc_level >= ln_dbg_level)
1637 THEN
1638 FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 7');
1639 END IF; --l_proc_level>=l_dbg_level
1640
1641 IF xv_vat_action = 'UNCLAIM' AND pn_vat_amount <> 0 THEN --If VAT amount exists and VAT action is UNCLAIM
1642 xv_vat_action := 'UNCLAIMED'; -- VAT is included in Unclaim. do_vat_claim is no more called
1643 ln_non_rec_amount := ln_non_rec_amount + pn_vat_amount; -- include recoverable VAT in Non recoverable amount
1644 END IF;
1645
1646 IF (ln_proc_level >= ln_dbg_level)
1647 THEN
1648 FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 8');
1649 END IF; --l_proc_level>=l_dbg_level
1650
1651 IF (ln_non_rec_amount <> 0)
1652 THEN
1653 --Call the following procedure to do unclaim
1654 Do_Unclaim( pn_organization_id => trx_rec.organization_id -- current organization from rcv_transactions or jai_rcv_lines
1655 , pn_transaction_id => trx_rec.transaction_id -- DELIVER transaction_id
1656 , pn_amount => ln_non_rec_amount -- Amount to be unclaimed
1657 , pn_version_number => pn_version_number
1658 );
1659
1660 FND_FILE.PUT_LINE(fnd_file.log,' Do_Unclaim() Invoked');
1661 ELSE
1662 FND_FILE.PUT_LINE(fnd_file.log,' Do_Unclaim() is not Invoked');
1663 END IF;--(ln_non_rec_amount <> 0)
1664
1665 IF (ln_proc_level >= ln_dbg_level)
1666 THEN
1667 FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 9');
1668 END IF; --l_proc_level>=l_dbg_level
1669
1670 IF xv_cenvat_action = 'UNCLAIMED'
1671 THEN
1672 --update jai_retro_line_changes to modify excise_action to 'UNCLAIM'
1673 UPDATE
1674 Jai_Retro_Line_Changes
1675 SET
1676 Excise_Action = 'UNCLAIM'
1677 WHERE line_change_id =pn_line_change_id;
1678 END IF;
1679
1680 IF (ln_proc_level >= ln_dbg_level)
1681 THEN
1682 FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 10');
1683 END IF; --l_proc_level>=l_dbg_level
1684
1685 IF xv_vat_action = 'UNCLAIMED'
1686 THEN
1687 --update jai_retro_line_changes to modify vat_action to 'UNCLAIM'
1688 UPDATE
1689 Jai_Retro_Line_Changes
1690 SET
1691 Vat_Action = 'UNCLAIM'
1692 WHERE line_change_id = pn_line_change_id;
1693 END IF;
1694
1695 IF (ln_proc_level >= ln_dbg_level)
1696 THEN
1697 FND_FILE.PUT_LINE(fnd_file.log,'Do_Accounting 11');
1698 END IF; --l_proc_level>=l_dbg_level
1699 END LOOP; --FOR trx_rec IN rcv_trx_cur --DELIVER transactions
1700 END IF;
1701 --logging for debug
1702 IF (ln_proc_level >= ln_dbg_level)
1703 THEN
1704 FND_LOG.STRING( ln_proc_level
1705 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
1706 , 'Exit procedure'
1707 );
1708 END IF; -- (ln_proc_level>=ln_dbg_level)
1709 EXCEPTION
1710 WHEN OTHERS THEN
1711 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1712 THEN
1713 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1714 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
1715 , Sqlcode||Sqlerrm);
1716 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1717 RAISE;
1718 END Do_Accounting;
1719
1720 --==========================================================================
1721 -- PROCEDURE NAME:
1722 --
1723 -- Do_Vat_Claim Private
1724 --
1725 -- DESCRIPTION:
1726 --
1727 -- This procedure is used to claim the vat tax on receipt
1728 --
1729 --
1730 -- PARAMETERS:
1731 -- In:pn_transaction_id NUMBER transaction identifier
1732 -- pn_shipment_line_id NUMBER shipemnt line identifier,
1733 -- pn_vat_amount NUMBER recoverable vat tax amount
1734 -- pv_supp_vat_inv_no VARCHAR2 supplementary invoice number
1735 -- pd_supp_vat_inv_date DATE supplementary invoice date
1736 -- pn_version_number NUMBER receipt version number
1737 -- pn_line_change_id NUMBER identifier of jai_retro_line_changes
1738 -- DESIGN REFERENCES:
1739 -- JAI_Retroprice_TDD.doc
1740 --
1741 -- CHANGE HISTORY:
1742 --
1743 -- 14-JAN-2008 Eric Ma created
1744 --==========================================================================
1745
1746 PROCEDURE Do_Vat_Claim
1747 ( pn_transaction_id IN NUMBER
1748 , pn_shipment_line_id IN NUMBER
1749 , pn_vat_amount IN NUMBER
1750 , pv_supp_vat_inv_no IN VARCHAR2 DEFAULT NULL
1751 , pd_supp_vat_inv_date IN DATE DEFAULT NULL
1752 , pn_version_number IN NUMBER
1753 , pn_line_change_id IN NUMBER
1754 )
1755 IS
1756 --This procedure is used to CLAIm or UNCLAIM VAT to the extent it got modified
1757 --It does the corresponding accounting as well
1758
1759 jai_rcv_rgm_lines_rec jai_rcv_rgm_lines%ROWTYPE;
1760 rcv_transactions_rec rcv_transactions%ROWTYPE;
1761 ln_tot_clm_instl_amt NUMBER;
1762 ln_tot_instl_amt NUMBER;
1763 ln_installment_cnt NUMBER;
1764 ln_new_re_tax_amt NUMBER;
1765 ln_orig_re_tax_amt NUMBER;
1766 ln_diff_re_tax_amt NUMBER;
1767 ln_instl_diff NUMBER;
1768 ln_tax_instl_claimed_cnt NUMBER;
1769 ln_tax_claimed_diff_amount NUMBER;
1770 ln_organization_id NUMBER;
1771 ln_location_id NUMBER;
1772 ln_receipt_num NUMBER;
1773 ln_regime_id NUMBER;
1774 ln_interim_recovery_account NUMBER;
1775 ln_code_combination_id NUMBER;
1776 ln_repository_id NUMBER;
1777 lv_reference_10 VARCHAR2 (4000);
1778 lv_reference_23 VARCHAR2 (4000);
1779 lv_reference_24 VARCHAR2 (4000);
1780 lv_reference_25 VARCHAR2 (4000);
1781 lv_reference_26 VARCHAR2 (4000);
1782 lv_process_status VARCHAR2 (4000);
1783 lv_process_message VARCHAR2 (4000);
1784 lv_receipt_number jai_rcv_lines.receipt_num%TYPE;
1785 lv_code_path VARCHAR2 (4000); --TO BE DONE
1786 --lv_receipt_num jai_rcv_lines.receipt_num%TYPE;/*rchandan*/
1787
1788 CURSOR get_rcv_transactions_cur
1789 IS
1790 SELECT
1791 *
1792 FROM
1793 rcv_transactions
1794 WHERE
1795 transaction_id = pn_transaction_id;
1796
1797 CURSOR jai_rcv_rgm_lines_cur (pn_shipment_line_id NUMBER)
1798 IS
1799 SELECT
1800 *
1801 FROM
1802 jai_rcv_rgm_lines
1803 WHERE shipment_line_id = pn_shipment_line_id ;
1804
1805 CURSOR jai_rcv_rgm_instl_count_cur
1806 ( pn_rcv_rgm_line_id NUMBER
1807 , pn_tax_id NUMBER DEFAULT NULL --added by eric for bug#6968733 on Apr 15,2008
1808 )
1809 IS
1810 SELECT
1811 COUNT(*)
1812 FROM
1813 jai_rcv_rgm_claims
1814 WHERE rcv_rgm_line_id = pn_rcv_rgm_line_id
1815 AND tax_id = NVL(pn_tax_id,tax_id); --added by eric for bug#6968733 on Apr 15,2008
1816
1817
1818 CURSOR jai_rcv_rgm_claimed_count_cur
1819 ( pn_rcv_rgm_line_id NUMBER,
1820 pn_tax_id NUMBER DEFAULT NULL
1821 )
1822 IS
1823 SELECT
1824 COUNT(*)
1825 FROM
1826 jai_rcv_rgm_claims
1827 WHERE rcv_rgm_line_id = pn_rcv_rgm_line_id
1828 AND tax_id = NVL(pn_tax_id,tax_id)
1829 AND claimed_amount IS NOT NULL;
1830
1831 CURSOR jai_retro_tax_changes_cur
1832 IS
1833 SELECT
1834 *
1835 FROM
1836 jai_retro_tax_changes jrtc
1837 WHERE EXISTS ( SELECT
1838 'X'
1839 FROM
1840 JAI_RGM_DEFINITIONS jr
1841 , JAI_RGM_REGISTRATIONS jrr
1842 WHERE jr.regime_id = jrr.regime_id
1843 AND jr.regime_code = jai_constants.vat_regime
1844 AND jrr.registration_type = jai_constants.regn_type_tax_types
1845 AND jrtc.tax_type =jrr.attribute_code
1846 )
1847 AND jrtc.recoverable_flag ='Y'
1848 AND jrtc.line_change_id = pn_line_change_id;
1849
1850
1851 CURSOR get_claim_schedule_cur ( pn_rcv_rgm_line_id NUMBER)
1852 IS
1853 SELECT
1854 SUM( a.installment_amount - a.claimed_amount ) claim_amount
1855 , tax_type
1856 , MIN(claim_schedule_id) claim_schedule_id
1857 FROM
1858 jai_rcv_rgm_claims A
1859 WHERE rcv_rgm_line_id = pn_rcv_rgm_line_id
1860 AND claimed_amount IS NOT NULL
1861 GROUP BY a.tax_type;
1862
1863 CURSOR get_parameters_cur ( pn_rcv_rgm_line_id NUMBER)
1864 IS
1865 SELECT
1866 jrrl.organization_id
1867 , jrrl.location_id
1868 , jrl.receipt_num
1869 , jrd.regime_id
1870 FROM
1871 jai_rcv_rgm_lines jrrl
1872 , jai_rgm_definitions jrd
1873 , jai_rcv_lines jrl
1874 WHERE jrrl.rcv_rgm_line_id = pn_rcv_rgm_line_id
1875 AND jrrl.shipment_line_id = jrl.shipment_line_id
1876 AND jrrl.regime_code = jrd.regime_code;
1877
1878 lv_procedure_name VARCHAR2(40):='Do_Vat_Claim';
1879 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1880 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
1881
1882 BEGIN
1883 --logging for debug
1884 IF (ln_proc_level >= ln_dbg_level)
1885 THEN
1886 FND_LOG.STRING( ln_proc_level
1887 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
1888 , 'Enter procedure'
1889 );
1890 END IF; --l_proc_level>=l_dbg_level
1891 --Get the primary_cost_method from mtl_parameters for the organization
1892
1893 OPEN get_rcv_transactions_cur;
1894 FETCH get_rcv_transactions_cur
1895 INTO
1896 rcv_transactions_rec ;
1897 CLOSE get_rcv_transactions_cur;
1898
1899 -- Get the receipt_num
1900 SELECT receipt_num
1901 INTO lv_receipt_number
1902 FROM jai_rcv_lines
1903 WHERE shipment_line_id = pn_shipment_line_id;
1904
1905 --Get the record from jai_rcv_rgm_lines for the current shipment_line_id ( ln_rcv_rgm_line_id )
1906 OPEN jai_rcv_rgm_lines_cur (pn_shipment_line_id => pn_shipment_line_id);
1907 FETCH jai_rcv_rgm_lines_cur
1908 INTO jai_rcv_rgm_lines_rec;
1909 CLOSE jai_rcv_rgm_lines_cur;
1910
1911
1912 --deleted by eric for bug#6968733 on Apr 15,2008,begin
1913 ----------------------------------------------------------------------
1914 /*
1915 --Get the count of installments from jai_rcv_rgm_claims for the rcv_rgm_line_id from the above record(ln_installment_cnt)
1916 OPEN jai_rcv_rgm_instl_count_cur
1917 ( pn_rcv_rgm_line_id =>jai_rcv_rgm_lines_rec.rcv_rgm_line_id)
1918 FETCH jai_rcv_rgm_instl_count_cur
1919 INTO ln_installment_cnt;
1920 CLOSE jai_rcv_rgm_instl_count_cur;
1921 */
1922
1923 ----------------------------------------------------------------------
1924 --deleted by eric for bug#6968733 on Apr 15,2008,end
1925
1926
1927 --Initialise ln_tot_clm_instl_amt to zero. This is used to calculate the total claimed
1928 --Initialise ln_tot_instl_amt to zero. This is used to calculate the total installment amount difference
1929 ln_tot_clm_instl_amt :=0 ;
1930 ln_tot_instl_amt :=0 ;
1931
1932 --LOOP through jai_retro_tax_changes where recoverable_flag is 'Y' and tax_type is one of tax types attached to VAT regime
1933 FOR jai_retor_tax_changes_rec IN jai_retro_tax_changes_cur
1934 LOOP
1935 -- calculate the recoverable portion of the difference in modified and original tax_amount
1936 -- Use mod_cr_percentage from jai_cmn_taxes_all
1937 ln_new_re_tax_amt := Get_Recoverable_Amount ( pn_tax_id => jai_retor_tax_changes_rec.tax_id
1938 , pn_tax_amount => jai_retor_tax_changes_rec.modified_tax_amount
1939 );
1940 ln_orig_re_tax_amt := Get_Recoverable_Amount ( pn_tax_id => jai_retor_tax_changes_rec.tax_id
1941 , pn_tax_amount => jai_retor_tax_changes_rec.original_tax_amount
1942 );
1943 ln_diff_re_tax_amt := ln_new_re_tax_amt - ln_orig_re_tax_amt;
1944
1945 --added by eric for bug#6968733 on Apr 15,2008,begin
1946 ----------------------------------------------------------------------
1947 --Get the count of installments from jai_rcv_rgm_claims for the rcv_rgm_line_id from the above record(ln_installment_cnt)
1948 OPEN jai_rcv_rgm_instl_count_cur
1949 ( pn_rcv_rgm_line_id =>jai_rcv_rgm_lines_rec.rcv_rgm_line_id
1950 , pn_tax_id => jai_retor_tax_changes_rec.tax_id
1951 );
1952 FETCH jai_rcv_rgm_instl_count_cur
1953 INTO ln_installment_cnt;
1954 CLOSE jai_rcv_rgm_instl_count_cur;
1955 ----------------------------------------------------------------------
1956 --added by eric for bug#6968733 on Apr 15,2008,end
1957
1958
1959 -- calculate the installment difference amount by doing ln_diff_re_tax_amt / ln_installment_cnt( ln_instl_diff )
1960 ln_instl_diff := ln_diff_re_tax_amt/ln_installment_cnt;
1961
1962 --Update jai_rcv_rgm_claims to increment installment amount
1963 UPDATE jai_rcv_rgm_claims
1964 SET installment_amount = installment_amount + ln_instl_diff
1965 WHERE rcv_rgm_line_id = jai_rcv_rgm_lines_rec.rcv_rgm_line_id
1966 AND tax_id = jai_retor_tax_changes_rec.tax_id;
1967
1968
1969 --get no of installments which are claimed for this tax_id by counting the records for which
1970 --claimed_amount is populated (ln_instl_claimed_cnt)
1971 OPEN jai_rcv_rgm_claimed_count_cur
1972 ( pn_rcv_rgm_line_id => jai_rcv_rgm_lines_rec.rcv_rgm_line_id
1973 , pn_tax_id => jai_retor_tax_changes_rec.tax_id
1974 );
1975 FETCH jai_rcv_rgm_claimed_count_cur
1976 INTO ln_tax_instl_claimed_cnt;
1977 CLOSE jai_rcv_rgm_claimed_count_cur;
1978
1979 --Get the total amount to be claimed for this tax id by using ln_claim_diff_amount := ln_tax_instl_claimed_cnt * ln_instl_diff
1980
1981 --changed by eric for bug#6968733 on Apr 15,2008,begin
1982 ----------------------------------------------------------------------
1983 -- ln_tax_claimed_diff_amount := ln_tax_inst_claimed_cnt * ln_instl_diff ;
1984
1985 ln_tax_claimed_diff_amount := ln_tax_instl_claimed_cnt * ln_instl_diff ;
1986 ----------------------------------------------------------------------
1987 --changed by eric for bug#6968733 on Apr 15,2008,end
1988
1989 ln_tot_clm_instl_amt := ln_tot_clm_instl_amt + ln_tax_claimed_diff_amount; -- Total change amount claimed for this receipt
1990
1991 ln_tot_instl_amt := ln_tot_instl_amt + ln_diff_re_tax_amt; -- Total change in installment amounts
1992 END LOOP; -- (jai_retor_tax_changes_rec IN jai_retro_tax_changes_cur)
1993
1994 IF ln_tot_instl_amt <> 0
1995 THEN
1996 --Update jai_rcv_rgm_lines to increment recoverable_amount
1997
1998 UPDATE jai_rcv_rgm_lines
1999 SET recoverable_amount = recoverable_amount + ln_tot_instl_amt
2000 --WHERE rcv_rgm_line_id = jai_rcv_rgm_lines.rcv_rgm_line_id;
2001 WHERE rcv_rgm_line_id = jai_rcv_rgm_lines_rec.rcv_rgm_line_id; -- Modified by Jia for bug#6988208, on Apr 21, 2008.
2002
2003 END IF;-- (ln_tot_instl_amt <> 0 )
2004
2005
2006 --IF any amount is claimed THEN
2007 IF ln_tot_clm_instl_amt <>0
2008 THEN
2009 --UPDATE jai_rcv_rgm_lines to increment the recovered_amount by the amount claimed
2010 UPDATE jai_rcv_rgm_lines
2011 SET recovered_amount = recovered_amount + ln_tot_clm_instl_amt
2012 --WHERE rcv_rgm_line_id = jai_rcv_rgm_lines.rcv_rgm_line_id;
2013 WHERE rcv_rgm_line_id = jai_rcv_rgm_lines_rec.rcv_rgm_line_id; -- Modified by Jia for bug#6988208, on Apr 21, 2008.
2014
2015 END IF; -- (ln_tot_clm_instl_amt <>0)
2016
2017 FOR claim_schedule_rec IN get_claim_schedule_cur
2018 (pn_rcv_rgm_line_id =>jai_rcv_rgm_lines_rec.rcv_rgm_line_id)
2019 LOOP
2020 OPEN get_parameters_cur (jai_rcv_rgm_lines_rec.rcv_rgm_line_id);
2021 FETCH get_parameters_cur
2022 INTO
2023 ln_organization_id
2024 , ln_location_id
2025 , ln_receipt_num
2026 , ln_regime_id ;
2027 CLOSE get_parameters_cur;
2028
2029 --Get the Interim recovery account by calling the following function
2030 ln_interim_recovery_account :=
2031 jai_cmn_rgm_recording_pkg.get_account
2032 ( p_regime_id => ln_regime_id -- fetched above
2033 , p_organization_type => jai_constants.orgn_type_io
2034 , p_organization_id => ln_organization_id -- fetched above
2035 , p_location_id => ln_location_id -- fetched above
2036 , p_tax_type => claim_schedule_rec.tax_type -- current tax type in the LOOP
2037 , p_account_name => jai_constants.recovery_interim
2038 );
2039 IF ln_interim_recovery_account IS NULL THEN
2040 raise_application_error(-20110,'Recovery Account not defined in VAT Setup');
2041 END IF;
2042
2043 --Get the recovery account by calling the following funcation
2044 ln_code_combination_id :=
2045 jai_cmn_rgm_recording_pkg.get_account
2046 ( p_regime_id => ln_regime_id -- fetched above
2047 , p_organization_type => jai_constants.orgn_type_io
2048 , p_organization_id => ln_organization_id -- fetched above
2049 , p_location_id => ln_location_id -- fetched above
2050 , p_tax_type => claim_schedule_rec.tax_type -- current tax type in the LOOP
2051 , p_account_name => jai_constants.recovery
2052 );
2053
2054 IF ln_code_combination_id IS NULL THEN
2055 raise_application_error(-20110,'Recovery Account not defined in VAT Setup');
2056 END IF;
2057
2058 --Call the following procedure to make an entry in VAT repository to the extent it is claimed
2059
2060 jai_cmn_rgm_recording_pkg.insert_vat_repository_entry
2061 (
2062 pn_repository_id => ln_repository_id, -- OUT parameter
2063 pn_regime_id => ln_regime_id, -- fetched above
2064 pv_tax_type => claim_schedule_rec.tax_type, -- current tax type in the LOOP
2065 pv_organization_type => jai_constants.orgn_type_io,
2066 pn_organization_id => ln_organization_id, -- fetched above
2067 pn_location_id => ln_location_id, -- fetched above
2068 pv_source => jai_constants.source_rcv,
2069 pv_source_trx_type => 'RETROACTIVE VAT CLAIM:'||to_char(pn_version_number),
2070 pv_source_table_name => 'RCV_TRANSACTIONS',
2071 pn_source_id => pn_transaction_id,
2072 pd_transaction_date => trunc(sysdate),
2073 pv_account_name => jai_constants.recovery,
2074 pn_charge_account_id => ln_code_combination_id,
2075 pn_balancing_account_id => ln_interim_recovery_account,
2076 pn_credit_amount => claim_schedule_rec.claim_amount, -- current claim amount in the LOOP
2077 pn_debit_amount => claim_schedule_rec.claim_amount,
2078 pn_assessable_value => NULL,
2079 pn_tax_rate => NULL,
2080 pn_reference_id => claim_schedule_rec.claim_schedule_id,-- Current claim_schedule_id in LOOP
2081 pn_batch_id => NULL,
2082 pn_inv_organization_id => ln_organization_id, -- fetched above
2083 pv_invoice_no => pv_supp_vat_inv_no,
2084 pd_invoice_date => pd_supp_vat_inv_date,
2085 pv_called_from => 'JAI_RETRO_PRC_PKG.DO_VAT_CLAIM',
2086 pv_process_flag => lv_process_status,
2087 pv_process_message => lv_process_message,
2088 --Added by Bo Li for bug9305067 2010-4-14 BEGIN
2089 --------------------------------------------------
2090 pv_trx_reference_context => NULL,
2091 pv_trx_reference1 => NULL,
2092 pv_trx_reference2 => NULL,
2093 pv_trx_reference3 => NULL,
2094 pv_trx_reference4 => NULL,
2095 pv_trx_reference5 => NULL
2096 ----------------------------------------------
2097 --Added by Bo Li for bug9305067 2010-4-14 BEGIN
2098 );
2099
2100 IF lv_process_status <> jai_constants.successful
2101 THEN
2102 raise_application_error(-20120,'VAT repository Entry retruned with error : '||lv_process_message);
2103 END IF;
2104
2105 lv_reference_10 := 'India Local Retroactive VAT Claim Entries For Receipt:'||lv_receipt_number;
2106 lv_reference_23 := 'JAI_RETRO_PRC_PKG.DO_VAT_CLAIM';
2107 lv_reference_24 := 'JAI_RETRO_TAX_CHANGES';
2108 lv_reference_25 := 'transaction_id';
2109 lv_reference_26 := pn_transaction_id;
2110
2111 --Call the following procedure to debit the recovery account
2112
2113 jai_rcv_accounting_pkg.process_transaction
2114 ( p_transaction_id => pn_transaction_id,
2115 p_acct_type => 'REGULAR',
2116 p_acct_nature => 'VAT CLAIM',
2117 p_source_name => 'Purchasing India',
2118 p_category_name => 'Receiving India',
2119 p_code_combination_id => ln_code_combination_id,
2120 p_entered_dr => claim_schedule_rec.claim_amount,
2121 p_entered_cr => NULL,
2122 p_currency_code => rcv_transactions_rec.currency_code,
2123 p_accounting_date => SYSDATE,
2124 p_reference_10 => lv_reference_10,
2125 p_reference_23 => lv_reference_23,
2126 p_reference_24 => lv_reference_24,
2127 p_reference_25 => ln_repository_id,
2128 p_reference_26 => lv_reference_26,
2129 p_destination => 'G',
2130 p_simulate_flag => 'N',
2131 p_codepath => lv_code_path,
2132 p_process_message => lv_process_message,
2133 p_process_status => lv_process_status,
2134 p_reference_name => 'RETROACTIVE VAT CLAIM:'||to_char(pn_version_number),
2135 p_reference_id => claim_schedule_rec.claim_schedule_id
2136 );
2137
2138 IF lv_process_status <> jai_constants.successful THEN
2139 raise_application_error(-20120,'VAT Claim accounting retruned with error : '||lv_process_message);
2140 END IF;
2141
2142 --Call the following procedure to credit the recovery account
2143 jai_rcv_accounting_pkg.process_transaction
2144 ( p_transaction_id => pn_transaction_id,
2145 p_acct_type => 'REGULAR',
2146 p_acct_nature => 'VAT CLAIM',
2147 p_source_name => 'Purchasing India',
2148 p_category_name => 'Receiving India',
2149 p_code_combination_id => ln_interim_recovery_account,
2150 p_entered_dr => NULL,
2151 p_entered_cr => claim_schedule_rec.claim_amount,
2152 p_currency_code => 'INR',
2153 p_accounting_date => SYSDATE,
2154 p_reference_10 => lv_reference_10,
2155 p_reference_23 => lv_reference_23,
2156 p_reference_24 => lv_reference_24,
2157 p_reference_25 => ln_repository_id,
2158 p_reference_26 => lv_reference_26,
2159 p_destination => 'G',
2160 p_simulate_flag => 'N',
2161 p_codepath => lv_code_path,
2162 p_process_message => lv_process_message,
2163 p_process_status => lv_process_status,
2164 p_reference_name => 'RETROACTIVE VAT CLAIM:'||to_char(pn_version_number),
2165 p_reference_id => claim_schedule_rec.claim_schedule_id
2166 );
2167
2168 IF lv_process_status <> jai_constants.successful THEN
2169 raise_application_error(-20120,'VAT Claim accounting retruned with error : '||lv_process_message);
2170 END IF;
2171
2172 --Update jai_rcv_rgm_claims to set the claimed amount equal to installment amount as claim is made
2173
2174 UPDATE jai_rcv_rgm_claims
2175 SET claimed_amount = installment_amount
2176 WHERE rcv_rgm_line_id = jai_rcv_rgm_lines_rec.rcv_rgm_line_id
2177 AND tax_type = claim_schedule_rec.tax_type -- Current tax type in the loop
2178 AND claimed_amount IS NOT NULL;
2179
2180 END LOOP; -- claimed records
2181
2182 -- UPDATE jai_retro_line_changes to modify vat_action as 'CLAIM' for the current receipt line
2183 UPDATE
2184 jai_retro_line_changes
2185 SET
2186 vat_action = 'CLAIM'
2187 WHERE line_change_id = pn_line_change_id;
2188
2189 --logging for debug
2190 IF (ln_proc_level >= ln_dbg_level)
2191 THEN
2192 FND_LOG.STRING( ln_proc_level
2193 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
2194 , 'Exit procedure'
2195 );
2196 END IF; -- (ln_proc_level>=ln_dbg_level) --logging for debug
2197 EXCEPTION
2198 WHEN OTHERS
2199 THEN
2200 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2201 THEN
2202 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
2203 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
2204 , Sqlcode||Sqlerrm);
2205 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2206 RAISE;
2207 END Do_Vat_Claim;
2208
2209 --==========================================================================
2210 -- PROCEDURE NAME:
2211 --
2212 -- Do_Cenvat_Claim Private
2213 --
2214 -- DESCRIPTION:
2215 --
2216 -- This procedure is used to claim the cenvat tax on receipt
2217 --
2218 --
2219 -- PARAMETERS:
2220 -- In:pn_transaction_id NUMBER transaction identifier
2221 -- pn_shipment_line_id NUMBER shipemnt line identifier,
2222 -- pv_supp_vat_inv_no VARCHAR2 supplementary invoice number
2223 -- pd_supp_vat_inv_date DATE supplementary invoice date
2224 -- pn_version_number NUMBER receipt version number
2225 -- pn_line_change_id NUMBER identifier of jai_retro_line_changes
2226 -- DESIGN REFERENCES:
2227 -- JAI_Retroprice_TDD.doc
2228 --
2229 -- CHANGE HISTORY:
2230 --
2231 -- 14-JAN-2008 Eric Ma created
2232 --==========================================================================
2233 PROCEDURE Do_Cenvat_Claim
2234 ( pn_transaction_id IN NUMBER,
2235 pn_shipment_line_id IN NUMBER,
2236 pv_supp_exc_inv_no IN VARCHAR2 DEFAULT NULL,
2237 pd_supp_exc_inv_date IN DATE DEFAULT NULL,
2238 pn_version_number IN NUMBER,
2239 pn_line_change_id IN NUMBER
2240 )
2241 IS
2242 --This procedure is used to CLAIM or UNCLAIM the CENVAT to the extent it got modified
2243 --It does the corresponding accounting as well
2244 lv_process_status VARCHAR2(4000);
2245 lv_process_message VARCHAR2(4000);
2246 lv_code_path VARCHAR2(4000);
2247 lv_cgin_code VARCHAR2(4000);
2248 xt_tax_breakup_rec jai_rcv_excise_processing_pkg.tax_breakup;
2249 lt_tax_breakup_rec jai_rcv_excise_processing_pkg.tax_breakup;
2250 ln_charge_account_id NUMBER;
2251 xv_register_id NUMBER;
2252 xv_process_status VARCHAR2(4000);
2253 xv_process_message VARCHAR2(4000);
2254 xv_code_path VARCHAR2(4000);
2255 lv_tax_breakup_type VARCHAR2(4000);
2256 lv_register_type VARCHAR2(4000);
2257
2258 CURSOR get_jai_transaction_cur
2259 IS
2260 SELECT
2261 *
2262 FROM
2263 jai_rcv_transactions
2264 WHERE
2265 transaction_id = pn_transaction_id;
2266
2267 CURSOR get_rcv_cenvat_claim_cur
2268 IS
2269 SELECT
2270 cenvat_claimed_ptg
2271 FROM
2272 jai_rcv_cenvat_claims
2273 WHERE transaction_id = pn_transaction_id;
2274
2275
2276
2277 jai_transaction_rec get_jai_transaction_cur%ROWTYPE;
2278
2279 lv_procedure_name VARCHAR2(40):='Do_Cenvat_Claim';
2280 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2281 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
2282 lv_amount_register VARCHAR2(15);/*added by rchandan*/
2283 ln_cenvat_claimed_ptg NUMBER;/*added by rchandan*/
2284
2285 --added by eric for bug 6918495 and bug 6914567 on Mar 28, 2008,begin
2286 -------------------------------------------------------
2287 CENVAT_CREDIT CONSTANT VARCHAR2(2) := 'Cr';
2288 CENVAT_DEBIT CONSTANT VARCHAR2(2) := 'Dr';
2289 -------------------------------------------------------
2290 --added by eric for bug 6918495 and bug 6914567 on Mar 28, 2008,end
2291
2292 BEGIN
2293 --logging for debug
2294 IF (ln_proc_level >= ln_dbg_level)
2295 THEN
2296 FND_LOG.STRING( ln_proc_level
2297 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
2298 , 'Enter procedure'
2299 );
2300 END IF; --l_proc_level>=l_dbg_level
2301 --Get the primary_cost_method from mtl_parameters for the organization
2302
2303 --Call the following procedure to decide if Claim is valid for this transaction
2304 JAI_RCV_EXCISE_PROCESSING_PKG.Validate_Transaction
2305 ( p_transaction_id => pn_transaction_id
2306 , p_validation_type => 'COMMON'
2307 , p_process_status => lv_process_status -- OUT parameter
2308 , p_process_message => lv_process_message-- OUT parameter
2309 , p_simulate_flag => 'N'
2310 , p_codepath => lv_code_path -- OUT parameter
2311 );
2312
2313 IF lv_process_status = 'E' THEN
2314 raise_application_error(-20120,'Validaiton of the receipt for Cenvat processing returned with error : '||lv_process_message);
2315 END IF;
2316
2317 IF lv_process_status = 'X' THEN
2318 --Print that Claim is not valid for this and print lv_process_message
2319 FND_FILE.PUT_LINE(fnd_file.log, 'Total receipt fail to pass cenvat prossing validation,for the reason: '||lv_process_message);
2320
2321
2322 UPDATE
2323 jai_retro_line_changes
2324 SET
2325 excise_action ='UNCLAIM'
2326 WHERE
2327 line_change_id = pn_line_change_id;
2328
2329 RETURN;
2330 END IF;
2331
2332
2333 --Get the record from jai_rcv_transactions for the current transaction_id
2334 OPEN get_jai_transaction_cur;
2335 FETCH get_jai_transaction_cur
2336 INTO jai_transaction_rec;
2337 CLOSE get_jai_transaction_cur;
2338
2339 --Get cenvat_claimed_ptg from jai_rcv_cenvat_claims for the current transaction_id
2340 OPEN get_rcv_cenvat_claim_cur;
2341 FETCH get_rcv_cenvat_claim_cur
2342 INTO
2343 ln_cenvat_claimed_ptg;
2344 CLOSE get_rcv_cenvat_claim_cur;
2345
2346 IF (jai_transaction_rec.organization_type = 'T')
2347 THEN --Trading
2348 lv_tax_breakup_type := 'RG23D';
2349 ELSE -- manufacturing and others
2350 lv_tax_breakup_type := 'MODVAT';
2351 END IF;--(jai_rcv_transactions.organization_type = 'T')
2352
2353 --Call the following procedure to get the tax amounts by tax types
2354 Get_Tax_Amount_Breakup
2355 ( pn_shipment_line_id => pn_shipment_line_id
2356 , pn_transaction_id => pn_transaction_id
2357 , pn_curr_conv_rate => jai_transaction_rec.currency_conversion_rate -- from rcv_transactions
2358 , pr_tax => xt_tax_breakup_rec -- OUT parameter
2359 , pv_breakup_type => lv_tax_breakup_type
2360 , pn_line_change_id => pn_line_change_id
2361 );
2362
2363 lv_register_type := JAI_GENERAL_PKG.Get_Rg_Register_Type(jai_transaction_rec.item_class);
2364 lv_cgin_code := NULL;
2365
2366 lt_tax_breakup_rec := xt_tax_breakup_rec;
2367
2368 IF lv_register_type = 'C'
2369 THEN
2370 lv_amount_register := 'RG23C'; /*added by rchandan*/
2371 IF ln_cenvat_claimed_ptg = 50
2372 THEN
2373 lv_cgin_code := 'REGULAR-HALF';
2374
2375 --deleted by eric for bug 6918495 on Mar 28, 2008,begin
2376 ------------------------------------------------------------------------------
2377 /*
2378 --calculate lt_tax_breakup_rec to be half the xt_tax_breakup_rec
2379 lt_tax_breakup_rec.basic_excise :=0.5 * xt_tax_breakup_rec.basic_excise ;
2380 lt_tax_breakup_rec.addl_excise :=0.5 * xt_tax_breakup_rec.addl_excise ;
2381 lt_tax_breakup_rec.other_excise :=0.5 * xt_tax_breakup_rec.other_excise ;
2382 lt_tax_breakup_rec.cvd :=0.5 * xt_tax_breakup_rec.cvd ;
2383 lt_tax_breakup_rec.non_cenvat :=0.5 * xt_tax_breakup_rec.non_cenvat ;
2384 lt_tax_breakup_rec.excise_edu_cess :=0.5 * xt_tax_breakup_rec.excise_edu_cess;
2385 lt_tax_breakup_rec.cvd_edu_cess :=0.5 * xt_tax_breakup_rec.cvd_edu_cess ;
2386 lt_tax_breakup_rec.addl_cvd :=0.5 * xt_tax_breakup_rec.addl_cvd ;
2387 lt_tax_breakup_rec.sh_exc_edu_cess :=0.5 * xt_tax_breakup_rec.sh_exc_edu_cess;
2388 lt_tax_breakup_rec.sh_cvd_edu_cess :=0.5 * xt_tax_breakup_rec.sh_cvd_edu_cess;
2389 */
2390 ------------------------------------------------------------------------------
2391 --deleted by eric for bug 6918495 on Mar 28, 2008,end
2392
2393 --modified by eric for bug 6955045 on Mar 28, 2008,begin
2394 /*
2395 patch 6918495 failed to fix the bug 6918495, the bug of amount expected to double the
2396 current amount in the accounting type CENVAT-REG-50%.
2397
2398 The tax amount calculated here is used by both accounting entries and tax repository.
2399 So we invoke the funcions Accounting_entries and Rg23_Part_Ii_Entry of package JAI_RCV_EXCISE_PROCESSING_PKG
2400 to process it. In the function jai_rcv_excise_processing_pkg.accounting_entries, it has the logic to process
2401 lv_cgin_code := 'REGULAR-HALF' but the logic in Rg23_Part_Ii_Entry is not ignored.
2402
2403 So we need to prepare the data
2404 lt_tax_breakup_rec := -xt_tax_breakup_rec for Accounting_entries
2405 and
2406 xt_tax_breakup_rec := 0.5*xt_tax_breakup_rec for Rg23_Part_Ii_Entry
2407
2408
2409
2410
2411 The orignal code is as:
2412 xt_tax_breakup_rec := 0.5*xt_tax_breakup_rec
2413 lt_tax_breakup_rec := -xt_tax_breakup_rec
2414 So the first symptom of bug 6955045 is fixed but the secod is still there.
2415
2416
2417 Now opening a new bug 6955045 and changing the code as
2418
2419 lt_tax_breakup_rec := -xt_tax_breakup_rec
2420 xt_tax_breakup_rec := 0.5*xt_tax_breakup_rec
2421 */
2422
2423
2424
2425
2426 --Added by eric for bug 6918495 on Mar 28, 2008,begin
2427 ------------------------------------------------------------------------------
2428 lt_tax_breakup_rec.basic_excise := -xt_tax_breakup_rec.basic_excise ;
2429 lt_tax_breakup_rec.addl_excise := -xt_tax_breakup_rec.addl_excise ;
2430 lt_tax_breakup_rec.other_excise := -xt_tax_breakup_rec.other_excise ;
2431 lt_tax_breakup_rec.cvd := -xt_tax_breakup_rec.cvd ;
2432 lt_tax_breakup_rec.non_cenvat := -xt_tax_breakup_rec.non_cenvat ;
2433 lt_tax_breakup_rec.excise_edu_cess := -xt_tax_breakup_rec.excise_edu_cess;
2434 lt_tax_breakup_rec.cvd_edu_cess := -xt_tax_breakup_rec.cvd_edu_cess ;
2435 lt_tax_breakup_rec.addl_cvd := -xt_tax_breakup_rec.addl_cvd ;
2436 lt_tax_breakup_rec.sh_exc_edu_cess := -xt_tax_breakup_rec.sh_exc_edu_cess;
2437 lt_tax_breakup_rec.sh_cvd_edu_cess := -xt_tax_breakup_rec.sh_cvd_edu_cess;
2438
2439 --calculate lt_tax_breakup_rec to be half the xt_tax_breakup_rec
2440 xt_tax_breakup_rec.basic_excise :=0.5 * xt_tax_breakup_rec.basic_excise ;
2441 xt_tax_breakup_rec.addl_excise :=0.5 * xt_tax_breakup_rec.addl_excise ;
2442 xt_tax_breakup_rec.other_excise :=0.5 * xt_tax_breakup_rec.other_excise ;
2443 xt_tax_breakup_rec.cvd :=0.5 * xt_tax_breakup_rec.cvd ;
2444 xt_tax_breakup_rec.non_cenvat :=0.5 * xt_tax_breakup_rec.non_cenvat ;
2445 xt_tax_breakup_rec.excise_edu_cess :=0.5 * xt_tax_breakup_rec.excise_edu_cess;
2446 xt_tax_breakup_rec.cvd_edu_cess :=0.5 * xt_tax_breakup_rec.cvd_edu_cess ;
2447 xt_tax_breakup_rec.addl_cvd :=0.5 * xt_tax_breakup_rec.addl_cvd ;
2448 xt_tax_breakup_rec.sh_exc_edu_cess :=0.5 * xt_tax_breakup_rec.sh_exc_edu_cess;
2449 xt_tax_breakup_rec.sh_cvd_edu_cess :=0.5 * xt_tax_breakup_rec.sh_cvd_edu_cess;
2450 ------------------------------------------------------------------------------
2451 --Added by eric for bug 6918495 on Mar 28, 2008,end
2452 --modified by eric for bug 6955045 on Mar 28, 2008,end
2453
2454 ELSIF ln_cenvat_claimed_ptg = 100
2455 THEN
2456 lv_cgin_code := 'REGULAR-FULL-RETRO';
2457 END IF; -- ( ln_cenvat_claimed_ptg = 50)
2458 ELSIF lv_register_type = 'A' THEN
2459
2460 lv_amount_register := 'RG23A'; /*added by rchandan*/
2461
2462 END IF; -- (lv_register_type = 'C')
2463
2464
2465
2466 JAI_RCV_EXCISE_PROCESSING_PKG.Rg23_Part_Ii_Entry
2467 ( p_transaction_id => pn_transaction_id
2468
2469 --modified by eric for bug 6918495 on Mar 28, 2008,begin
2470 ------------------------------------------------------------------------------
2471 , pr_tax => xt_tax_breakup_rec --lt_tax_breakup_rec
2472 ------------------------------------------------------------------------------
2473 --modified by eric for bug 6918495 on Mar 28, 2008,end
2474 , p_part_i_register_id => NULL
2475 , p_register_entry_type => CENVAT_CREDIT
2476 , p_reference_num => 'RETRO CENVAT CLAIMS '||pn_version_number
2477 , p_register_id => xv_register_id
2478 , p_process_status => xv_process_status
2479 , p_process_message => xv_process_message
2480 , p_simulate_flag => 'N'
2481 , p_codepath => xv_code_path
2482 );
2483
2484 IF lv_process_status IN ('X','E')
2485 THEN
2486 raise_application_error(-20120,'RG23 part II Entry retruned with error : '||lv_process_message);
2487 END IF;
2488
2489 --UPDATE JAI_CMN_RG_23AC_II_TRXS and modify excise_invoice_no and excise_invoice_date
2490 --with pv_supp_exc_inv_no and pd_supp_exc_inv_date respectively
2491 UPDATE
2492 JAI_CMN_RG_23AC_II_TRXS
2493 SET
2494 excise_invoice_no = pv_supp_exc_inv_no
2495 , excise_invoice_date = pd_supp_exc_inv_date
2496 WHERE register_id = xv_register_id;
2497
2498 --Call the following procedure to do cenvat accounting
2499 jai_rcv_excise_processing_pkg.accounting_entries
2500 ( p_transaction_id => pn_transaction_id
2501 , pr_tax => lt_tax_breakup_rec
2502 , p_cgin_code => lv_cgin_code
2503 --modified by eric for bug 6918495 and bug 6914567 on Mar 28, 2008,begin
2504 -------------------------------------------------------
2505 --, p_cenvat_accounting_type => 'CENVAT_DEBIT'
2506 , p_cenvat_accounting_type => CENVAT_DEBIT
2507 -------------------------------------------------------
2508 --modified by eric for bug 6918495 and bug 6914567 on Mar 28, 2008,end
2509 , p_amount_register => lv_amount_register/*rchandan. replaced xv_register_id with lv_amount_register*/
2510 , p_cenvat_account_id => ln_charge_account_id -- OUT parameter
2511 , p_process_status => lv_process_status
2512 , p_process_message => lv_process_message
2513 , p_simulate_flag => 'N'
2514 , p_codepath => lv_code_path
2515 , pv_retro_reference => 'RETRO CENVAT CLAIMS '||pn_version_number
2516 );
2517
2518 IF lv_process_status IN ('X','E')
2519 THEN
2520 raise_application_error(-20120,'CENVAT Claim accounting retruned with error : '||lv_process_message);
2521 END IF;
2522
2523 --UPDATE jai_retro_line_changes to modify excise_action as 'CLAIM'
2524 UPDATE
2525 jai_retro_line_changes
2526 SET
2527 excise_action = 'CLAIM'
2528 WHERE line_change_id = pn_line_change_id;
2529
2530
2531 --logging for debug
2532 IF (ln_proc_level >= ln_dbg_level)
2533 THEN
2534 FND_LOG.STRING( ln_proc_level
2535 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
2536 , 'Exit procedure'
2537 );
2538 END IF; -- (ln_proc_level>=ln_dbg_level) --logging for debug
2539 EXCEPTION
2540 WHEN OTHERS THEN
2541 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2542 THEN
2543 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
2544 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
2545 , Sqlcode||Sqlerrm);
2546 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2547 RAISE;
2548 END Do_Cenvat_Claim;
2549 --==========================================================================
2550 -- PROCEDURE NAME:
2551 --
2552 -- Process_Retroactive_Update Publice
2553 --
2554 -- DESCRIPTION:
2555 --
2556 -- This procedure is used to claim the cenvat tax on receipt
2557 --
2558 --
2559 -- PARAMETERS:
2560 -- In:errbuf NUMBER error buffer the cocurent
2561 -- retcode NUMBER return the cocurent
2562 -- pn_vendor_id NUMBER vendor identifier
2563 -- pn_vendor_site_id NUMBER vendor site identifier
2564 -- pn_po_header_id NUMBER aggrement identifer
2565 -- pv_from_eff_date VARCHAR2 effective from date
2566 -- pv_vat_action NUMBER vat action 'CLAIM' or 'UNCLAIM'
2567 -- pv_supp_exc_inv_no VARCHAR2 supplementary invoice number
2568 -- pv_supp_exc_inv_date DATE supplementary invoice date
2569 -- pv_cenvat_action NUMBER cenvat action 'CLAIM' or 'UNCLAIM'
2570 -- pv_supp_vat_inv_no VARCHAR2 supplementary invoice number
2571 -- pv_process_downward DATE supplementary invoice date
2572 -- pn_version_number NUMBER receipt version number
2573 -- pv_process_downward VARCHAR2 down revision processing flag
2574 -- DESIGN REFERENCES:
2575 -- JAI_Retroprice_TDD.doc
2576 --
2577 -- CHANGE HISTORY:
2578 --
2579 -- 14-JAN-2008 Eric Ma created
2580 -- 01-Feb-2008 Eric Ma add logs and change code in exception for bug #6788048
2581 -- 10-Sep-2010 Jia Li for GST Bug#10043656.
2582 --==========================================================================
2583 PROCEDURE Process_Retroactive_Update
2584 ( errbuf OUT NOCOPY VARCHAR2
2585 , retcode OUT NOCOPY VARCHAR2
2586 , pn_vendor_id IN NUMBER
2587 , pn_vendor_site_id IN NUMBER DEFAULT NULL
2588 , pn_po_header_id IN NUMBER DEFAULT NULL
2589 , pv_from_eff_date IN VARCHAR2 DEFAULT NULL
2590 , pv_cenvat_action IN VARCHAR2 DEFAULT NULL
2591 , pv_supp_exc_inv_no IN VARCHAR2 DEFAULT NULL
2592 , pv_supp_exc_inv_date IN VARCHAR2 DEFAULT NULL
2593 , pv_vat_action IN VARCHAR2 DEFAULT NULL
2594 , pv_supp_vat_inv_no IN VARCHAR2 DEFAULT NULL
2595 , pv_supp_vat_inv_date IN VARCHAR2 DEFAULT NULL
2596 , pv_process_downward IN VARCHAR2 DEFAULT NULL
2597 )
2598 IS
2599 jai_rcv_transactions_rec jai_rcv_transactions%ROWTYPE;
2600 jai_rcv_lines_rec jai_rcv_lines%ROWTYPE;
2601
2602 ln_skip_rcpt_cnt NUMBER := NULL;
2603 lv_profile_setting VARCHAR2(255);
2604
2605 ln_recv_line_amount NUMBER;
2606 ln_recv_tax_amount NUMBER;
2607 ln_vat_assess_value NUMBER;
2608 ln_assessable_value NUMBER;
2609 /* for bug 16013918 by anupgupt
2610 ln_gst_assessable_value NUMBER; -- Added by Jia for GST Bug#10043656 on 2010/09/10
2611 */
2612 ln_retro_line_changes_id NUMBER;
2613 lv_cenvat_action VARCHAR2(4000);
2614 lv_vat_action VARCHAR2(4000);
2615 ln_re_vat_amount NUMBER;
2616 ln_re_cenvat_amount NUMBER;
2617 ln_modif_re_vat_amount NUMBER;
2618 ln_modif_re_cenvat_amount NUMBER;
2619 ln_diff_re_vat_amount NUMBER;
2620 ln_diff_re_cenvat_amount NUMBER;
2621 ln_non_rec_amount NUMBER;
2622 ln_retro_line_changes_version NUMBER;
2623
2624 ld_from_eff_date DATE := TO_DATE(pv_from_eff_date, 'YYYY/MM/DD hh24:mi:ss');
2625 ld_supp_exc_inv_date DATE := TO_DATE(pv_supp_exc_inv_date, 'YYYY/MM/DD hh24:mi:ss');
2626 ld_supp_vat_inv_date DATE := TO_DATE(pv_supp_vat_inv_date, 'YYYY/MM/DD hh24:mi:ss');
2627 ln_receipt_processed_no NUMBER;
2628 ln_tot_receipt_processed_no NUMBER :=0;
2629 ln_tot_receipt_no NUMBER;
2630
2631
2632 ln_org_nonre_tax_amount NUMBER; --added by eric on Apr 10,2008 for bug 6957519/6958938/6968839
2633 ln_modif_nonre_tax_amount NUMBER; --added by eric on Apr 10,2008 for bug 6957519/6958938/6968839
2634 ln_diff_nonre_tax_amount NUMBER; --added by eric on Apr 10,2008 for bug 6957519/6958938/6968839
2635
2636 CURSOR get_rcv_transactions_cur
2637 ( pn_line_location_id rcv_transactions.po_line_location_id%TYPE
2638 , pn_line_change_id jai_retro_line_changes.line_change_id%TYPE
2639 )
2640 IS
2641 SELECT
2642 *
2643 FROM
2644 rcv_transactions rt
2645 WHERE rt.transaction_type = 'RECEIVE'
2646 AND rt.po_line_location_id =pn_line_location_id
2647 AND creation_date >= NVL(ld_from_eff_date,creation_date)-- eric changed according to review comment #36
2648 AND NOT EXISTS ( SELECT
2649 'X'
2650 FROM
2651 jai_retro_line_changes jrlc
2652 WHERE jrlc.doc_line_id = rt.shipment_line_id
2653 AND jrlc.source_line_change_id = pn_line_change_id
2654 AND jrlc.doc_type = 'RECEIPT'
2655 );
2656
2657 CURSOR get_jai_rcv_line_taxes_cur
2658 (pn_transaction_id jai_rcv_line_taxes.transaction_id%TYPE )
2659 IS
2660 SELECT
2661 jrlt.*,
2662 jcta.adhoc_flag
2663 FROM
2664 jai_rcv_line_taxes jrlt
2665 , jai_cmn_taxes_all jcta
2666 WHERE jrlt.transaction_id = pn_transaction_id
2667 AND jrlt.tax_id = jcta.tax_id;
2668
2669 CURSOR get_jai_retro_line_change_cur
2670 IS
2671 -- doc_type is RELEASE
2672 SELECT
2673 line_change_id
2674 , doc_type
2675 , doc_header_id
2676 , doc_line_id
2677 , line_location_id
2678 , from_header_id
2679 , from_line_id
2680 , doc_version_number
2681 , source_line_change_id
2682 , price_change_date
2683 , inventory_item_id
2684 , organization_id
2685 , original_unit_price
2686 , modified_unit_price
2687 , receipt_processed_flag
2688 , excise_action
2689 , vat_action
2690 , excise_invoice_no
2691 , excise_invoice_date
2692 , vat_invoice_no
2693 , vat_invoice_date
2694 , retro_request_id
2695 , doc_number
2696 , vendor_id
2697 , vendor_site_id
2698 FROM
2699 jai_retro_line_changes jrlc
2700 WHERE vendor_id = NVL(pn_vendor_id,vendor_id)
2701 AND vendor_site_id = NVL(pn_vendor_site_id,vendor_site_id)
2702 AND ( (doc_type = 'RELEASE' AND doc_header_id = NVL(pn_po_header_id ,doc_header_id))
2703 OR (doc_type = 'STANDARD PO' AND from_header_id = NVL(pn_po_header_id ,from_header_id))
2704 )
2705 AND NVL(receipt_processed_flag,'N') <>jai_constants.yes
2706 --AND creation_date <= NVL(ld_from_eff_date,creation_date) , eric remomved according to review comment #36
2707 AND (original_unit_price < modified_unit_price OR pv_process_downward = jai_constants.yes )
2708 AND doc_version_number =( SELECT MAX(doc_version_number)
2709 FROM jai_retro_line_changes a
2710 WHERE a.line_location_id = jrlc.line_location_id
2711 ) ;
2712 lv_procedure_name VARCHAR2(40):='Process_Retroactive_Update';
2713 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2714 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
2715
2716 BEGIN
2717 --logging for debug
2718 IF (ln_proc_level >= ln_dbg_level)
2719 THEN
2720 FND_LOG.STRING( ln_proc_level
2721 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
2722 , 'Enter procedure'
2723 );
2724 END IF; --l_proc_level>=l_dbg_level
2725
2726 BEGIN
2727 FND_FILE.PUT_LINE(fnd_file.log, 'Concurrent Input Parameter Is :');
2728 FND_FILE.PUT_LINE(fnd_file.log, '-------------------------------------------------------------- ');
2729 FND_FILE.PUT_LINE(fnd_file.log, 'VENDOR ID :'|| pn_vendor_id );
2730 FND_FILE.PUT_LINE(fnd_file.log, 'VENDOR SITE ID :'|| pn_vendor_site_id );
2731 FND_FILE.PUT_LINE(fnd_file.log, 'AGGREMENT ID :'|| pn_po_header_id );
2732 FND_FILE.PUT_LINE(fnd_file.log, 'EFFECTIVE DATE :'|| pv_from_eff_date );
2733 FND_FILE.PUT_LINE(fnd_file.log, 'Execise Action :'|| pv_cenvat_action );
2734 FND_FILE.PUT_LINE(fnd_file.log, 'Supp Execise Invoice No. :'|| pv_supp_exc_inv_no );
2735 FND_FILE.PUT_LINE(fnd_file.log, 'Supp Execise Invoice Date :'|| pv_supp_exc_inv_date );
2736 FND_FILE.PUT_LINE(fnd_file.log, 'Vat Action :'|| pv_vat_action );
2737 FND_FILE.PUT_LINE(fnd_file.log, 'Supp VAT Invoice No. :'|| pv_supp_vat_inv_no );
2738 FND_FILE.PUT_LINE(fnd_file.log, 'Supp VAT Invoice Date :'|| pv_supp_vat_inv_date );
2739 FND_FILE.PUT_LINE(fnd_file.log, 'Down Revision Process :'|| pv_process_downward );
2740 FND_FILE.PUT_LINE(fnd_file.log, '--------------------------------------------------------- ');
2741 END;
2742
2743 retcode := 0;
2744 errbuf := NULL;
2745
2746 IF (pv_cenvat_action ='CLAIM' AND (pv_supp_exc_inv_no IS NULL OR pv_supp_exc_inv_date IS NULL))
2747 THEN
2748 FND_FILE.PUT_LINE(fnd_file.log, ' Both Supplementary Excise Invoice NO and Supplementary Excise Invoice date should not be null when Excise Action is CLAIM' );
2749 retcode :=2;
2750 RETURN;
2751 END IF;
2752
2753
2754 IF (pv_vat_action ='CLAIM' AND (pv_supp_vat_inv_no IS NULL OR pv_supp_vat_inv_date IS NULL))
2755 THEN
2756 FND_FILE.PUT_LINE(fnd_file.log, ' Both Supplementary Vat Invoice NO and Supplementary Vat Invoice date should not be null when Excise Action is CLAIM' );
2757 retcode :=2;
2758 RETURN;
2759 END IF;
2760
2761 --Get the value for the profile: "PO: Allow Retroactive Pricing of POs"
2762 lv_profile_setting := FND_PROFILE.VALUE('PO_ALLOW_RETROPRICING_OF_PO');
2763
2764 IF (lv_profile_setting <>'ALL_RELEASES')
2765 THEN
2766 FND_FILE.PUT_LINE(fnd_file.log, 'PO: Allow Retroactive Pricing of POs = '||lv_profile_setting );
2767 FND_FILE.PUT_LINE(fnd_file.log, 'This Concurrent Program is not executed as the profile (PO: Allow Retroactive Pricing of POs) is not set to ''ALL RELEASES''');
2768 retcode :=0;
2769 RETURN;
2770 END IF;
2771
2772 FOR jai_retro_line_change_rec IN get_jai_retro_line_change_cur
2773 LOOP
2774 -- Print the shipment line information
2775 BEGIN
2776 FND_FILE.PUT_LINE(fnd_file.log, ' ');
2777 FND_FILE.PUT_LINE(fnd_file.log, '++++');
2778 FND_FILE.PUT_LINE(fnd_file.log, 'Loop for the shipment line, line_change_id ='||jai_retro_line_change_rec.line_change_id );
2779 FND_FILE.PUT_LINE(fnd_file.log, 'doc_type = '||jai_retro_line_change_rec.doc_type );
2780 FND_FILE.PUT_LINE(fnd_file.log, 'doc_header_id = '||jai_retro_line_change_rec.doc_header_id );
2781 FND_FILE.PUT_LINE(fnd_file.log, 'doc_line_id = '||jai_retro_line_change_rec.doc_line_id );
2782 FND_FILE.PUT_LINE(fnd_file.log, 'line_location_id = '||jai_retro_line_change_rec.line_location_id );
2783 FND_FILE.PUT_LINE(fnd_file.log, 'from_header_id = '||jai_retro_line_change_rec.from_header_id );
2784 FND_FILE.PUT_LINE(fnd_file.log, 'from_line_id = '||jai_retro_line_change_rec.from_line_id );
2785 FND_FILE.PUT_LINE(fnd_file.log, 'doc_version_number = '||jai_retro_line_change_rec.doc_version_number );
2786 FND_FILE.PUT_LINE(fnd_file.log, 'inventory_item_id = '||jai_retro_line_change_rec.inventory_item_id );
2787 FND_FILE.PUT_LINE(fnd_file.log, 'organization_id = '||jai_retro_line_change_rec.organization_id );
2788 FND_FILE.PUT_LINE(fnd_file.log, 'original_unit_price = '||jai_retro_line_change_rec.original_unit_price );
2789 FND_FILE.PUT_LINE(fnd_file.log, 'modified_unit_price = '||jai_retro_line_change_rec.modified_unit_price );
2790 FND_FILE.PUT_LINE(fnd_file.log, 'receipt_processed_flag = '||jai_retro_line_change_rec.receipt_processed_flag);
2791 FND_FILE.PUT_LINE(fnd_file.log, 'excise_action = '||jai_retro_line_change_rec.excise_action );
2792 FND_FILE.PUT_LINE(fnd_file.log, 'vat_action = '||jai_retro_line_change_rec.vat_action );
2793 FND_FILE.PUT_LINE(fnd_file.log, 'excise_invoice_no = '||jai_retro_line_change_rec.excise_invoice_no );
2794 FND_FILE.PUT_LINE(fnd_file.log, 'excise_invoice_date = '||jai_retro_line_change_rec.excise_invoice_date );
2795 FND_FILE.PUT_LINE(fnd_file.log, 'vat_invoice_no = '||jai_retro_line_change_rec.vat_invoice_no );
2796 FND_FILE.PUT_LINE(fnd_file.log, 'vat_invoice_date = '||jai_retro_line_change_rec.vat_invoice_date );
2797 FND_FILE.PUT_LINE(fnd_file.log, 'retro_request_id = '||jai_retro_line_change_rec.retro_request_id );
2798 FND_FILE.PUT_LINE(fnd_file.log, 'doc_number = '||jai_retro_line_change_rec.doc_number );
2799 FND_FILE.PUT_LINE(fnd_file.log, 'vendor_id = '||jai_retro_line_change_rec.vendor_id );
2800 FND_FILE.PUT_LINE(fnd_file.log, 'vendor_site_id = '||jai_retro_line_change_rec.vendor_site_id );
2801 END;
2802
2803 ln_skip_rcpt_cnt := 0; --This is to count the number of receipts which are not processed for this Shipment
2804 ln_receipt_processed_no :=0 ;--Initial vaiable for counting receipt processed number for a shipment
2805 ln_tot_receipt_no :=0; --Initial vaiable for counting total receipts number for a shipment
2806
2807 --LOOP through the RECEIVE transactions performed against the current Shipment
2808 FOR rcv_transactions_rec IN get_rcv_transactions_cur ( pn_line_location_id => jai_retro_line_change_rec.line_location_id
2809 , pn_line_change_id => jai_retro_line_change_rec.line_change_id
2810 )
2811 LOOP
2812 ln_tot_receipt_no := ln_tot_receipt_no +1;
2813 jai_rcv_transactions_rec := Get_Jai_Rcv_Trans_Record(rcv_transactions_rec.transaction_id);
2814 jai_rcv_lines_rec := Get_Jai_Rcv_Lines_Record(rcv_transactions_rec.transaction_id);
2815
2816 BEGIN
2817 FND_FILE.PUT_LINE(fnd_file.log, ' ');
2818 FND_FILE.PUT_LINE(fnd_file.log, ' ++');
2819 FND_FILE.PUT_LINE(fnd_file.log, ' LOOP through the RECEIVE transactions,Transaction id is : ' ||rcv_transactions_rec.transaction_id);
2820 FND_FILE.PUT_LINE(fnd_file.log, ' Shipment Header id is : ' ||jai_rcv_lines_rec.shipment_header_id);
2821 FND_FILE.PUT_LINE(fnd_file.log, ' Shipment Line id is : ' ||jai_rcv_lines_rec.shipment_line_id);
2822 FND_FILE.PUT_LINE(fnd_file.log, ' Receipt Number is : ' ||jai_rcv_lines_rec.receipt_num );
2823 FND_FILE.PUT_LINE(fnd_file.log, ' Receipt Vat status is : ' ||jai_rcv_transactions_rec.process_vat_status);
2824 FND_FILE.PUT_LINE(fnd_file.log, ' Receipt CenVat status is : ' ||jai_rcv_transactions_rec.cenvat_rg_status);
2825 FND_FILE.PUT_LINE(fnd_file.log, '');
2826 END;
2827 IF ( jai_rcv_transactions_rec.process_vat_status IS NULL
2828 OR jai_rcv_transactions_rec.process_vat_status = 'P'
2829 OR jai_rcv_transactions_rec.process_vat_status = 'N'
2830 OR jai_rcv_transactions_rec.process_vat_status = 'EE'
2831 OR jai_rcv_transactions_rec.cenvat_rg_status IS NULL
2832 OR jai_rcv_transactions_rec.cenvat_rg_status = 'P'
2833 OR jai_rcv_transactions_rec.cenvat_rg_status = 'N'
2834 OR jai_rcv_transactions_rec.cenvat_rg_status = 'EE'
2835 )
2836 THEN
2837 -- Print a message stating that the Receipt is not claimed and it should be claimed before
2838 -- JAI Retroactive price Update is Run
2839 IF( jai_rcv_transactions_rec.process_vat_status IS NULL
2840 OR jai_rcv_transactions_rec.process_vat_status = 'P'
2841 OR jai_rcv_transactions_rec.process_vat_status = 'N'
2842 OR jai_rcv_transactions_rec.process_vat_status = 'EE'
2843 )
2844 THEN
2845 FND_FILE.PUT_LINE(fnd_file.log, ' Vat tax on the receipt :'||jai_rcv_lines_rec.receipt_num||' is not claimed.');
2846 END IF;
2847
2848 IF( jai_rcv_transactions_rec.cenvat_rg_status IS NULL
2849 OR jai_rcv_transactions_rec.cenvat_rg_status = 'P'
2850 OR jai_rcv_transactions_rec.cenvat_rg_status = 'N'
2851 OR jai_rcv_transactions_rec.cenvat_rg_status = 'EE'
2852 )
2853 THEN
2854 FND_FILE.PUT_LINE(fnd_file.log, ' Cenvat tax on the receipt :'||jai_rcv_lines_rec.receipt_num||' is not claimed.');
2855 END IF;
2856
2857 FND_FILE.PUT_LINE(fnd_file.log, ' Please Claim it before running the concurrent JAI Retroactive price Update');
2858 ln_skip_rcpt_cnt := ln_skip_rcpt_cnt + 1;
2859 ELSE
2860 --get the retro line change id the receipt from a sequence
2861 SELECT
2862 jai_retro_line_changes_s.nextval
2863 INTO
2864 ln_retro_line_changes_id
2865 FROM
2866 DUAL;
2867
2868 IF (ln_proc_level >= ln_dbg_level)
2869 THEN
2870 FND_FILE.PUT_LINE(fnd_file.log, ' ln_retro_line_changes_id : '|| ln_retro_line_changes_id);
2871 END IF; --l_proc_level>=l_dbg_level
2872
2873 --get the version number of retro tax line for the receipt
2874 --starting from 0
2875 SELECT
2876 NVL(MAX(doc_version_number),0) + 1 /*rchandan. Moved +1 outside NVL*/
2877 INTO
2878 ln_retro_line_changes_version
2879 FROM
2880 jai_retro_line_changes
2881 WHERE doc_header_id = jai_rcv_lines_rec.shipment_header_id
2882 AND doc_line_id = jai_rcv_lines_rec.shipment_line_id
2883 AND doc_type = 'RECEIPT';
2884
2885 IF (ln_proc_level >= ln_dbg_level)
2886 THEN
2887 FND_FILE.PUT_LINE(fnd_file.log, ' ln_retro_line_changes_version : '|| ln_retro_line_changes_version);
2888 END IF; --l_proc_level>=l_dbg_level
2889
2890 --insert data into jai_retro_line_changes table
2891 INSERT INTO jai_retro_line_changes
2892 ( line_change_id
2893 , doc_type
2894 , doc_header_id
2895 , doc_line_id
2896 , line_location_id
2897 , doc_version_number
2898 , source_line_change_id -- added for indicating receipt processed or not
2899 , price_change_date
2900 , inventory_item_id
2901 , organization_id
2902 , original_unit_price
2903 , modified_unit_price
2904 , receipt_processed_flag
2905 , excise_action
2906 , excise_invoice_no
2907 , excise_invoice_date
2908 , vat_action
2909 , vat_invoice_no
2910 , vat_invoice_date
2911 , retro_request_id
2912 , doc_number
2913 , vendor_id
2914 , vendor_site_id
2915 , creation_date
2916 , created_by
2917 , last_update_date
2918 , last_update_login
2919 , last_updated_by
2920 , object_version_number
2921 )
2922 VALUES
2923 ( ln_retro_line_changes_id --=> jai_retro_line_changes_s.nextval
2924 , 'RECEIPT' --=> 'RECEIPT'
2925 , jai_rcv_lines_rec.shipment_header_id --=> shipment_header_id from jai_rcv_lines
2926 , jai_rcv_lines_rec.shipment_line_id --=> shipment_Line_Id from jai_rcv_lines
2927 , NULL --=> NULL
2928 , ln_retro_line_changes_version --=> Increment previous version number of this receipt,issue??
2929 , jai_retro_line_change_rec.line_change_id --=> source_line_change_id ,The line_change_id of latest shipment
2930 , SYSDATE --=> Sysdate
2931 , jai_rcv_lines_rec.inventory_item_id --=> inventory_item_id from jai_rcv_lines
2932 , jai_rcv_lines_rec.organization_id --=> Organization_id from jai_rcv_lines
2933 , jai_retro_line_change_rec.original_unit_price --=> original_unit_price from jai_retro_line_changes of the current Release
2934 , jai_retro_line_change_rec.modified_unit_price --=> Modified_unit_price from jai_retro_line_changes of the current Release
2935 , 'Y' --=> receipt_processed_flag :'Y'
2936 , pv_cenvat_action --=> pv_cenvat_action
2937 , pv_supp_exc_inv_no --=> pv_supp_exc_inv_no
2938 , ld_supp_exc_inv_date --=> pd_supp_exc_inv_date
2939 , pv_vat_action --=> pv_vat_action
2940 , pv_supp_vat_inv_no --=> pv_supp_vat_inv_no
2941 , ld_supp_vat_inv_date --=> pd_supp_vat_inv_date
2942 , fnd_global.conc_request_id --=> fnd_global.conc_request_id
2943 , jai_rcv_lines_rec.receipt_num --=> receipt_number from jai_rcv_lines
2944 , rcv_transactions_rec.vendor_id --=> vendor_id from rcv_transactions
2945 , rcv_transactions_rec.vendor_site_id --=> vendor_site_id from rcv_transactions
2946 , SYSDATE --=> sysdate
2947 , FND_GLOBAL.USER_ID --=> fnd_global.user_id
2948 , SYSDATE --=> sysdate
2949 , FND_GLOBAL.LOGIN_ID --=> fnd_global.login_id
2950 , FND_GLOBAL.USER_ID --=> fnd_global.user_id
2951 , NULL --=> NULL
2952 );
2953
2954 IF (ln_proc_level >= ln_dbg_level)
2955 THEN
2956 FND_FILE.PUT_LINE(fnd_file.log, ' Table jai_retro_line_changes inserted ');
2957 END IF; --l_proc_level>=l_dbg_level
2958
2959 FOR jai_rcv_line_taxes_rec IN get_jai_rcv_line_taxes_cur (rcv_transactions_rec.transaction_id)
2960 LOOP
2961 -- Insert into jai_retro_tax_changes
2962 INSERT INTO jai_retro_tax_changes
2963 ( tax_change_id
2964 , line_change_id
2965 , tax_line_no
2966 , tax_id
2967 , tax_name
2968 , tax_type
2969 , currency_code
2970 , original_tax_amount
2971 , modified_tax_amount
2972 , Recoverable_flag
2973 , adhoc_flag
2974 , third_party_flag
2975 , creation_date
2976 , created_by
2977 , last_update_date
2978 , last_update_login
2979 , last_updated_by
2980 , object_version_number
2981 )
2982 VALUES
2983 ( jai_retro_tax_changes_s.nextval --=> jai_retro_tax_changes_s.nextval
2984 , ln_retro_line_changes_id --=> from jai_retro_line_changes
2985 , jai_rcv_line_taxes_rec.tax_line_no --=> from jai_rcv_line_taxes
2986 , jai_rcv_line_taxes_rec.tax_id --=> from jai_rcv_line_taxes
2987 , jai_rcv_line_taxes_rec.tax_name --=> from jai_rcv_line_taxes
2988 , jai_rcv_line_taxes_rec.tax_type --=> from jai_rcv_line_taxes
2989 , jai_rcv_line_taxes_rec.currency --=> from jai_rcv_line_taxes
2990 , jai_rcv_line_taxes_rec.tax_amount --=> tax_amount from jai_rcv_line_taxes
2991 , NULL --=> NULL ,modified_tax_amount
2992 , jai_rcv_line_taxes_rec.modvat_flag --=> modvat_flag from jai_rcv_line_taxes
2993 , jai_rcv_line_taxes_rec.adhoc_flag --=> adhoc_flag from jai_cmn_taxes_all. Join using tax_id
2994 , jai_rcv_line_taxes_rec.third_party_flag --=> from jai_rcv_line_taxes
2995 , SYSDATE --=> sysdate
2996 , fnd_global.user_id --=> fnd_global.user_id
2997 , SYSDATE --=> sysdate
2998 , fnd_global.login_id --=> fnd_global.login_id
2999 , fnd_global.user_id --=> fnd_global.user_id
3000 , NULL --=> NULL
3001 ) ;
3002 END LOOP;-- (jai_rcv_line_taxes_rec IN get_jai_rcv_line_taxes_cur)
3003
3004 IF (ln_proc_level >= ln_dbg_level)
3005 THEN
3006 FND_FILE.PUT_LINE(fnd_file.log, ' Table jai_retro_tax_changes inserted ');
3007 END IF; --l_proc_level>=l_dbg_level
3008
3009 --calc_new_line_amount
3010 ln_recv_line_amount := jai_retro_line_change_rec.modified_unit_price
3011 * rcv_transactions_rec.quantity;
3012
3013 ln_recv_tax_amount := ln_recv_line_amount;
3014
3015 IF (ln_proc_level >= ln_dbg_level)
3016 THEN
3017 FND_FILE.PUT_LINE(fnd_file.log, ' ln_recv_line_amount : '|| ln_recv_line_amount);
3018 FND_FILE.PUT_LINE(fnd_file.log, ' ln_recv_tax_amount : '|| ln_recv_tax_amount);
3019 END IF; --l_proc_level>=l_dbg_level
3020
3021 --get_assessable_value
3022 ln_assessable_value := jai_cmn_setup_pkg.get_po_assessable_value
3023 ( p_vendor_id => rcv_transactions_rec.vendor_id
3024 , p_vendor_site_id => rcv_transactions_rec.vendor_site_id
3025 , p_inv_item_id => jai_rcv_lines_rec.inventory_item_id
3026 , p_line_uom => jai_rcv_transactions_rec.uom_code
3027 );
3028
3029 IF (ln_proc_level >= ln_dbg_level)
3030 THEN
3031 FND_FILE.PUT_LINE(fnd_file.log, ' ln_assessable_value : '|| ln_assessable_value);
3032 END IF; --l_proc_level>=l_dbg_level
3033
3034 IF NVL( ln_assessable_value, 0 ) <= 0
3035 THEN
3036 ln_assessable_value := ln_recv_line_amount;
3037 ELSE
3038 ln_assessable_value := ln_assessable_value * rcv_transactions_rec.quantity;
3039 END IF;
3040
3041 IF (ln_proc_level >= ln_dbg_level)
3042 THEN
3043 FND_FILE.PUT_LINE(fnd_file.log, ' ln_assessable_value :=ln_assessable_value*quantity : '|| ln_assessable_value);
3044 END IF; --l_proc_level>=l_dbg_level
3045
3046 --get_vat_assessable_value
3047 ln_vat_assess_value := jai_general_pkg.ja_in_vat_assessable_value
3048 ( p_party_id => rcv_transactions_rec.vendor_id
3049 , p_party_site_id => rcv_transactions_rec.vendor_site_id
3050 , p_inventory_item_id => jai_rcv_lines_rec.inventory_item_id
3051 , p_uom_code => jai_rcv_transactions_rec.uom_code
3052 , p_default_price => jai_retro_line_change_rec.modified_unit_price
3053 , p_ass_value_date => trunc(sysdate)
3054 , p_party_type => 'V'
3055 );
3056
3057 IF (ln_proc_level >= ln_dbg_level)
3058 THEN
3059 FND_FILE.PUT_LINE(fnd_file.log, ' ln_vat_assess_value : '|| ln_vat_assess_value);
3060 END IF; --l_proc_level>=l_dbg_level
3061
3062
3063 ln_vat_assess_value := ln_vat_assess_value * rcv_transactions_rec.quantity;
3064
3065 IF (ln_proc_level >= ln_dbg_level)
3066 THEN
3067 FND_FILE.PUT_LINE(fnd_file.log, ' ln_vat_assess_value :=ln_vat_assess_value*quantity : '|| ln_vat_assess_value);
3068 END IF; --l_proc_level>=l_dbg_level
3069
3070 -- Added by Jia for GST Bug#10043656 on 2010/09/10, Begin
3071 -------------------------------------------------------------------------------------------
3072 /* for bug 16013918 by anupgupt
3073 ln_gst_assessable_value := jai_gst_general_pkg.get_gst_assessable_value
3074 ( p_party_id => rcv_transactions_rec.vendor_id
3075 , p_party_site_id => rcv_transactions_rec.vendor_site_id
3076 , p_inventory_item_id => jai_rcv_lines_rec.inventory_item_id
3077 , p_uom_code => jai_rcv_transactions_rec.uom_code
3078 , p_default_price => jai_retro_line_change_rec.modified_unit_price
3079 , p_ass_value_date => trunc(sysdate)
3080 , p_party_type => 'V'
3081 );
3082
3083 IF (ln_proc_level >= ln_dbg_level)
3084 THEN
3085 FND_FILE.PUT_LINE(fnd_file.log, ' ln_gst_assessable_value : '|| ln_gst_assessable_value);
3086 END IF; --l_proc_level>=l_dbg_level
3087
3088 ln_gst_assessable_value := ln_gst_assessable_value * rcv_transactions_rec.quantity;
3089
3090 IF (ln_proc_level >= ln_dbg_level)
3091 THEN
3092 FND_FILE.PUT_LINE(fnd_file.log, ' ln_gst_assessable_value :=ln_gst_assessable_value*quantity : '|| ln_gst_assessable_value);
3093 END IF; --l_proc_level>=l_dbg_level
3094 */
3095 -------------------------------------------------------------------------------------------
3096 -- Added by Jia for GST Bug#10043656 on 2010/09/10, End
3097
3098
3099 JAI_PO_TAX_PKG.Calculate_Tax
3100 ( p_type => 'RECEIPTS'
3101 , p_header_id => jai_rcv_lines_rec.shipment_header_id -- Receipt Shipment header Id
3102 , P_line_id => jai_rcv_lines_rec.shipment_line_id -- Receipt Shipment line Id
3103 , p_line_loc_id => -999
3104 , p_line_quantity => rcv_transactions_rec.quantity -- receipt Quantity
3105 , p_price => ln_recv_line_amount -- Receipt line Amount
3106 , p_line_uom_code => jai_rcv_transactions_rec.uom_code -- Receipt UOM code
3107 , p_tax_amount => ln_recv_tax_amount -- Receipt line Amount ( IN Out parameter that gives the total tax amount and takes line amount as Input)
3108 , p_assessable_value => ln_assessable_value -- Excise Assesable value
3109 , p_vat_assess_value => ln_vat_assess_value -- VAT Assessable value
3110 , p_item_id => jai_rcv_lines_rec.inventory_item_id -- Inventory item id
3111 , p_conv_rate => rcv_transactions_rec.currency_conversion_rate -- currency conversion rate
3112 , pv_retroprice_changed => 'Y' --CHANGED NEW
3113 , pv_called_from => 'RETROACTIVE' -- New parameter
3114 /* for bug 16013918 by anupgupt
3115 , pn_gst_assessable_value => ln_gst_assessable_value -- Added by Jia for GST Bug#10043656
3116 */
3117 );
3118
3119 -- print the new total tax amount of the receipt
3120 Fnd_File.Put_Line(FND_FILE.LOG,' New total tax amount of the receipt '||jai_rcv_lines_rec.receipt_num||' IS :' ||ln_recv_tax_amount);
3121
3122 -- After recalculating the tax ,updat the modified_tax_amount of jai_retro_tax_changes table
3123 FOR jai_rcv_line_taxes_rec IN get_jai_rcv_line_taxes_cur (rcv_transactions_rec.transaction_id)
3124 LOOP
3125 -- Update the new tax amount in modified_tax_amount column of jai_retro_tax_changes
3126 UPDATE
3127 jai_retro_tax_changes jrtc
3128 SET
3129 modified_tax_amount = ( SELECT tax_amount
3130 FROM jai_rcv_line_taxes jrlt
3131 WHERE jrlt.shipment_header_id = jai_rcv_lines_rec.shipment_header_id
3132 AND jrlt.shipment_line_id = jai_rcv_lines_rec.shipment_line_id
3133 AND jrlt.tax_id = jrtc.tax_id
3134 )
3135 WHERE line_change_id = ln_retro_line_changes_id
3136 AND tax_line_no = jai_rcv_line_taxes_rec.tax_line_no
3137 AND tax_id = jai_rcv_line_taxes_rec.tax_id ;
3138 END LOOP; --jai_rcv_line_taxes_rec IN get_jai_rcv_line_taxes_cur
3139
3140 Fnd_File.Put_Line(FND_FILE.LOG,' New tax has been updated to the table jai_retro_tax_changes ');
3141
3142 Get_Vat_CenVat_Amount
3143 ( pn_line_change_id => ln_retro_line_changes_id
3144 , xn_re_vat_amount => ln_re_vat_amount
3145 , xn_modif_re_vat_amount => ln_modif_re_vat_amount
3146 , xn_diff_re_vat_amount => ln_diff_re_vat_amount
3147 , xn_re_cenvat_amount => ln_re_cenvat_amount
3148 , xn_modif_re_cenvat_amount => ln_modif_re_cenvat_amount
3149 , xn_diff_re_cenvat_amount => ln_diff_re_cenvat_amount
3150 );
3151 --eric deleted for bug 6957519/6958938/6968839 on Apr 10,2008,begin
3152 ----------------------------------------------------------------------------------------------
3153 --ln_non_rec_amount := Get_Tot_NonRe_Tax_Amount(pn_line_change_id => ln_retro_line_changes_id);
3154 ----------------------------------------------------------------------------------------------
3155 --eric deleted for bug 6957519/6958938/6968839 on Apr 10,2008,end
3156
3157
3158 --eric added for bug 6957519/6958938/6968839 on Apr 10,2008,begin
3159 ----------------------------------------------------------------------------------------------
3160 Get_Tot_NonRe_Tax_Amount
3161 ( pn_line_change_id => ln_retro_line_changes_id
3162 , xn_org_nonre_tax_amount => ln_org_nonre_tax_amount
3163 , xn_modif_nonre_tax_amount => ln_modif_nonre_tax_amount
3164 , xn_diff_nonre_tax_amount => ln_diff_nonre_tax_amount
3165 );
3166 ----------------------------------------------------------------------------------------------
3167 --eric added for bug 6957519/6958938/6968839 on Apr 10,2008,end
3168
3169 Fnd_File.Put_Line(FND_FILE.LOG,' New VAT tax amount is : '|| ln_modif_re_vat_amount|| ', the difference from old vat tax is :'|| ln_diff_re_vat_amount);
3170 Fnd_File.Put_Line(FND_FILE.LOG,' New CENVAT tax amount is : '|| ln_modif_re_cenvat_amount|| ', the difference from old cenvat tax is :'|| ln_diff_re_cenvat_amount);
3171 Fnd_File.Put_Line(FND_FILE.LOG,' New NonRecoverable Tax is : '|| ln_modif_nonre_tax_amount|| ', the difference from old NonRecoverable tax is :'|| ln_diff_nonre_tax_amount);
3172
3173 lv_cenvat_action := pv_cenvat_action ;
3174 lv_vat_action := pv_vat_action ;
3175
3176 -- Call the procedure to do accounting for both RECEIVE and DELIVER transactions
3177
3178 -- xv_cenvat_action/xv_vat_action are IN OUT parameters. Even if the user decides
3179 -- to claim cenvat, depending on set up we may chose to unclaim. This parameter returns the action taken
3180 -- and takes the pv_cenvat_action as input
3181 Do_Accounting
3182 ( pn_shipment_line_id => rcv_transactions_rec.shipment_line_id -- from rcv_transactions
3183 , pn_transaction_id => rcv_transactions_rec.transaction_id -- from rcv_transactions for the current RECEIVE transaction,
3184 , pn_cenvat_amount => ln_diff_re_cenvat_amount -- Cenvat amount difference from above
3185 , xv_cenvat_action => lv_cenvat_action
3186 , pn_vat_amount => ln_diff_re_vat_amount -- recoverable VAT difference from above
3187 , xv_vat_action => lv_vat_action
3188 --eric modified for bug 6957519/6958938/6968839 on Apr 10,2008,begin
3189 -------------------------------------------------------------------------
3190 --, pn_non_rec_amount => ln_non_rec_amount -- Non recoverable tax amount from above
3191 , pn_non_rec_amount => ln_diff_nonre_tax_amount -- difference of Non recoverable tax amount between org and modifed amount
3192 -------------------------------------------------------------------------
3193 --eric modified for bug 6957519/6958938/6968839 on Apr 10,2008,end
3194 , pn_version_number => ln_retro_line_changes_version -- current version of the receipt i.e previous version plus one
3195 , pn_line_change_id => ln_retro_line_changes_id
3196 );
3197
3198 Fnd_File.Put_Line(FND_FILE.LOG,' ');
3199 Fnd_File.Put_Line(FND_FILE.LOG,' Do_Accounting() Invoked. ');
3200 Fnd_File.Put_Line(FND_FILE.LOG,' ');
3201 -- print a message stating that accounting is done
3202 -- Call the procedure to Claim or Unclaim Cenvat do its accounting
3203
3204 -- if its UNCLAIMED then amount is already uncalimed during DELIVER in do_accounting
3205 IF (NVL(lv_cenvat_action,'$') <> 'UNCLAIMED' AND ln_diff_re_cenvat_amount <> 0 )
3206 THEN
3207 Do_Cenvat_Claim
3208 ( pn_transaction_id => rcv_transactions_rec.transaction_id -- from rcv_transactions
3209 , pn_shipment_line_id => rcv_transactions_rec.shipment_line_id -- from rcv_transactions
3210 --, pn_cenvat_amount => ln_diff_re_cenvat_amount ,delete the useless parameter by eric on Jan 24,2008
3211 , pv_supp_exc_inv_no => pv_supp_exc_inv_no
3212 , pd_supp_exc_inv_date => ld_supp_exc_inv_date
3213 , pn_version_number => ln_retro_line_changes_version -- current version of the receipt
3214 , pn_line_change_id => ln_retro_line_changes_id
3215 );
3216 Fnd_File.Put_Line(FND_FILE.LOG,' ');
3217 Fnd_File.Put_Line(FND_FILE.LOG,' Do_Cenvat_Claim() Invoked. ');
3218 Fnd_File.Put_Line(FND_FILE.LOG,' ');
3219
3220 --Print a message stating that the receipt cenvat is claimed or unclaimed depending on value of lv_cenvat_action
3221 ELSE
3222 Fnd_File.Put_Line(FND_FILE.LOG,' Cenvat_action = '|| lv_cenvat_action);
3223 Fnd_File.Put_Line(FND_FILE.LOG,' Diff Cenvat_Amount = '|| ln_diff_re_cenvat_amount);
3224 Fnd_File.Put_Line(FND_FILE.LOG,' Did not invoke DO_CENVAT_CLAIM() subroutine');
3225 END IF;--(lv_cenvat_action <> 'UNCLAIMED' AND ln_cenvat_amount <> 0 )
3226
3227 -- Call the procedure to Claim or Unclaim VAT and do its accounting
3228
3229 IF (NVL(lv_vat_action,'$') <> 'UNCLAIMED' AND ln_diff_re_vat_amount <> 0)
3230 THEN --if VAT is unclaimed in do_accounting then we should not claim or unclaim
3231 Do_Vat_Claim
3232 ( pn_transaction_id => rcv_transactions_rec.transaction_id, -- from rcv_transactions
3233 pn_shipment_line_id => rcv_transactions_rec.shipment_line_id, -- from rcv_transactions
3234 pn_vat_amount => ln_diff_re_vat_amount,
3235 pv_supp_vat_inv_no => pv_supp_vat_inv_no,
3236 pd_supp_vat_inv_date => ld_supp_vat_inv_date,
3237 pn_version_number => ln_retro_line_changes_version -- current version of the receipt
3238 , pn_line_change_id => ln_retro_line_changes_id
3239 );
3240
3241 Fnd_File.Put_Line(FND_FILE.LOG,' ');
3242 Fnd_File.Put_Line(FND_FILE.LOG,' Do_Vat_Claim() Invoked. ');
3243 Fnd_File.Put_Line(FND_FILE.LOG,' ');
3244
3245 --Print a message stating that the receipt vat is claimed or unclaimed depending on value of lv_vat_action
3246 ELSE
3247 Fnd_File.Put_Line(FND_FILE.LOG,' lv_vat_action = '|| lv_vat_action);
3248 Fnd_File.Put_Line(FND_FILE.LOG,' Diff Vat Amount = '|| ln_diff_re_vat_amount);
3249 Fnd_File.Put_Line(FND_FILE.LOG,' Did not invoke DO_VAT_CLAIM() subroutine');
3250 END IF; --(lv_vat_action <> 'UNCLAIMED' AND ln_vat_amount <> 0)
3251
3252 --Increaset the total processed receipt number;
3253 ln_receipt_processed_no :=ln_receipt_processed_no+1;
3254 END IF; -- IF ( jai_rcv_transactions_rec.process_vat_status IS NULL )
3255
3256 FND_FILE.PUT_LINE(fnd_file.log, ' The Receipt :'||jai_rcv_lines_rec.receipt_num||' processing is done.');
3257 FND_FILE.PUT_LINE(fnd_file.log, ' End loop through the RECEIVE transactions,Transaction id is : ' ||rcv_transactions_rec.transaction_id);
3258 FND_FILE.PUT_LINE(fnd_file.log, ' ++');
3259 END LOOP; --rcv_transactions_rec IN get_rcv_transactions_cur
3260
3261 IF (ln_skip_rcpt_cnt =0)
3262 THEN
3263 UPDATE
3264 jai_retro_line_changes
3265 SET
3266 receipt_processed_flag = 'Y'
3267 WHERE
3268 line_change_id = jai_retro_line_change_rec.line_change_id;
3269 END IF; -- (ln_skip_rcpt_cnt =0)
3270
3271 --increase the number of total receipt processed
3272 ln_tot_receipt_processed_no := ln_tot_receipt_processed_no + ln_receipt_processed_no ;
3273
3274 FND_FILE.PUT_LINE(fnd_file.log, 'Total receipt number for current shipment is: '||ln_tot_receipt_no);
3275 FND_FILE.PUT_LINE(fnd_file.log, 'Total processed receipt for current shipment number is: '||ln_receipt_processed_no);
3276 FND_FILE.PUT_LINE(fnd_file.log, 'End loop for the shipment line, line_change_id ='||jai_retro_line_change_rec.line_change_id );
3277 FND_FILE.PUT_LINE(fnd_file.log, '++++');
3278 END LOOP;--jai_retro_line_change_rec IN get_jai_retro_line_change_cur
3279
3280 IF ( ln_skip_rcpt_cnt IS NULL OR ln_tot_receipt_processed_no = 0)
3281 THEN
3282 Fnd_File.Put_Line(FND_FILE.LOG,' ');
3283 Fnd_File.Put_Line(FND_FILE.LOG,' ');
3284 Fnd_File.Put_Line(FND_FILE.LOG,'No valid data found for processing');
3285 Fnd_File.Put_Line(FND_FILE.LOG,' ');
3286 Fnd_File.Put_Line(FND_FILE.LOG,' ');
3287 END IF ;
3288 --logging for debug
3289 IF (ln_proc_level >= ln_dbg_level)
3290 THEN
3291 FND_LOG.STRING( ln_proc_level
3292 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
3293 , 'Exit procedure'
3294 );
3295 END IF; -- (ln_proc_level>=ln_dbg_level) --logging for debug
3296 EXCEPTION
3297 WHEN OTHERS THEN
3298 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3299 THEN
3300 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
3301 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
3302 , Sqlcode||Sqlerrm);
3303 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3304 retcode := 2;
3305 errbuf := Sqlerrm;
3306 --RAISE; BUG #6788048
3307 RETURN;
3308 END Process_Retroactive_Update;
3309
3310 --==========================================================================
3311 -- PROCEDURE NAME:
3312 --
3313 -- Insert_Price_Changes Public
3314 --
3315 -- DESCRIPTION:
3316 --
3317 -- This procedure is used to insert location line history changes
3318 -- when doing retroactive price update.
3319 --
3320 -- PARAMETERS:
3321 -- In: pr_old lc_rec%TYPE old line record
3322 -- pr_new lc_rec%TYPE new line record
3323 -- Out: pv_process_flag VARCHAR2 return flag
3324 -- pv_process_message VARCHAR2 return message
3325 --
3326 --
3327 -- DESIGN REFERENCES:
3328 -- JAI_Retroprice_TDD.doc
3329 --
3330 -- CHANGE HISTORY:
3331 --
3332 -- 14-JAN-2008 Kevin Cheng Created
3333 --==========================================================================
3334 PROCEDURE Insert_Price_Changes
3335 ( pr_old IN lc_rec%TYPE
3336 , pr_new IN lc_rec%TYPE
3337 , pv_process_flag OUT NOCOPY VARCHAR2
3338 , pv_process_message OUT NOCOPY VARCHAR2
3339 )
3340 IS
3341 CURSOR cur_loc_line_taxes
3342 IS
3343 SELECT
3344 jpt.tax_line_no
3345 , jpt.tax_id
3346 , jcta.tax_name
3347 , jcta.tax_type
3348 , jpt.currency
3349 , jpt.tax_amount
3350 , jcta.adhoc_flag
3351 , jpt.modvat_flag
3352 , jpt.vendor_id
3353 FROM
3354 jai_po_taxes jpt
3355 , jai_cmn_taxes_all jcta
3356 WHERE jpt.tax_id = jcta.tax_id
3357 AND jpt.line_location_id = pr_old.line_location_id;
3358
3359 lv_doc_number po_headers_all.segment1%TYPE;
3360 ln_vendor_id po_headers_all.vendor_id%TYPE;
3361 ln_vendor_site_id po_headers_all.vendor_site_id%TYPE;
3362 ln_item_id mtl_system_items_b.inventory_item_id%TYPE;
3363 ln_revision_num po_headers_all.revision_num%TYPE;
3364 ln_retro_line_changes_id NUMBER;
3365 ln_retro_tax_changes_id NUMBER;
3366 lv_third_party_flag VARCHAR2(1);
3367
3368 BEGIN
3369 pv_process_flag := 'SS';
3370 pv_process_message := '';
3371
3372 BEGIN
3373 SELECT
3374 JAI_RETRO_LINE_CHANGES_S.NEXTVAL
3375 INTO
3376 ln_retro_line_changes_id
3377 FROM dual;
3378 EXCEPTION
3379 WHEN no_data_found THEN
3380 pv_process_flag := 'UE';
3381 pv_process_message := 'When getting line sequence.'||SQLERRM;
3382 RETURN;
3383 WHEN OTHERS THEN
3384 pv_process_flag := 'UE';
3385 pv_process_message := 'When getting line sequence.'||SQLERRM;
3386 RETURN;
3387 END;
3388
3389 --Get revision number
3390 BEGIN
3391 SELECT
3392 NVL(max(doc_version_number), 0) + 1 /*added max and replaced -1 with 0*/
3393 INTO
3394 ln_revision_num
3395 FROM
3396 JAI_RETRO_LINE_CHANGES jrlc
3397 WHERE jrlc.doc_header_id = pr_old.po_header_id
3398 AND jrlc.doc_line_id = pr_old.po_line_id
3399 AND jrlc.line_location_id = pr_old.line_location_id /*added by rchandan*/
3400 AND jrlc.doc_type IN ('RELEASE', 'STANDARD PO');
3401
3402 EXCEPTION
3403 WHEN no_data_found THEN
3404 pv_process_flag := 'UE';
3405 pv_process_message := 'When getting revision number.'||SQLERRM;
3406 RETURN;
3407 WHEN too_many_rows THEN
3408 pv_process_flag := 'UE';
3409 pv_process_message := 'When getting revision number.'||SQLERRM;
3410 RETURN;
3411 WHEN OTHERS THEN
3412 pv_process_flag := 'UE';
3413 pv_process_message := 'When getting revision number.'||SQLERRM;
3414 RETURN;
3415 END;
3416
3417 BEGIN
3418 SELECT
3419 item_id
3420 INTO
3421 ln_item_id
3422 FROM
3423 po_lines_all
3424 WHERE po_line_id = pr_old.po_line_id;
3425 EXCEPTION
3426 WHEN no_data_found THEN
3427 pv_process_flag := 'UE';
3428 pv_process_message := 'When getting line item id.'||SQLERRM;
3429 RETURN;
3430 WHEN too_many_rows THEN
3431 pv_process_flag := 'UE';
3432 pv_process_message := 'When getting line item id.'||SQLERRM;
3433 RETURN;
3434 WHEN OTHERS THEN
3435 pv_process_flag := 'UE';
3436 pv_process_message := 'When getting line item id.'||SQLERRM;
3437 RETURN;
3438 END;
3439
3440 BEGIN
3441 IF pr_old.shipment_type = 'STANDARD'
3442 THEN
3443 SELECT
3444 segment1
3445 , vendor_id
3446 , vendor_site_id
3447 INTO
3448 lv_doc_number
3449 , ln_vendor_id
3450 , ln_vendor_site_id
3451 FROM
3452 po_headers_all
3453 WHERE po_header_id = pr_old.from_header_id;
3454
3455 ELSIF pr_old.shipment_type = 'BLANKET'
3456 THEN
3457 SELECT
3458 segment1
3459 , vendor_id
3460 , vendor_site_id
3461 INTO
3462 lv_doc_number
3463 , ln_vendor_id
3464 , ln_vendor_site_id
3465 FROM
3466 po_headers_all
3467 WHERE po_header_id = pr_old.po_header_id;
3468
3469 END IF;
3470 EXCEPTION
3471 WHEN no_data_found THEN
3472 pv_process_flag := 'UE';
3473 pv_process_message := 'When getting agreement relate information.'||SQLERRM;
3474 RETURN;
3475 WHEN too_many_rows THEN
3476 pv_process_flag := 'UE';
3477 pv_process_message := 'When getting agreement relate information.'||SQLERRM;
3478 RETURN;
3479 WHEN OTHERS THEN
3480 pv_process_flag := 'UE';
3481 pv_process_message := 'When getting agreement relate information.'||SQLERRM;
3482 RETURN;
3483 END;
3484
3485 BEGIN
3486 IF pr_old.shipment_type = 'STANDARD'
3487 THEN
3488 INSERT INTO JAI_RETRO_LINE_CHANGES
3489 ( LINE_CHANGE_ID
3490 , DOC_TYPE
3491 , DOC_HEADER_ID
3492 , DOC_LINE_ID
3493 , LINE_LOCATION_ID
3494 , FROM_HEADER_ID
3495 , FROM_LINE_ID
3496 , DOC_VERSION_NUMBER
3497 , PRICE_CHANGE_DATE
3498 , INVENTORY_ITEM_ID
3499 , ORGANIZATION_ID
3500 , ORIGINAL_UNIT_PRICE
3501 , MODIFIED_UNIT_PRICE
3502 , RECEIPT_PROCESSED_FLAG
3503 , EXCISE_ACTION
3504 , VAT_ACTION
3505 , EXCISE_INVOICE_NO
3506 , EXCISE_INVOICE_DATE
3507 , VAT_INVOICE_NO
3508 , VAT_INVOICE_DATE
3509 , RETRO_REQUEST_ID
3510 , DOC_NUMBER
3511 , VENDOR_ID
3512 , VENDOR_SITE_ID
3513 , CREATION_DATE
3514 , LAST_UPDATE_DATE
3515 , LAST_UPDATE_LOGIN
3516 , LAST_UPDATED_BY
3517 , CREATED_BY
3518 , OBJECT_VERSION_NUMBER
3519 )
3520 VALUES
3521 ( ln_retro_line_changes_id
3522 , 'STANDARD PO'
3523 , pr_old.po_header_id
3524 , pr_old.po_line_id
3525 , pr_old.line_location_id
3526 , pr_old.from_header_id
3527 , pr_old.from_line_id
3528 , ln_revision_num
3529 , pr_new.retroactive_date
3530 , ln_item_id
3531 , pr_old.ship_to_organization_id
3532 , pr_old.price_override
3533 , pr_new.price_override
3534 , 'N'
3535 , NULL
3536 , NULL
3537 , NULL
3538 , NULL
3539 , NULL
3540 , NULL
3541 , NULL
3542 , lv_doc_number
3543 , ln_vendor_id
3544 , ln_vendor_site_id
3545 , SYSDATE
3546 , SYSDATE
3547 , fnd_global.login_id
3548 , fnd_global.user_id
3549 , fnd_global.user_id
3550 , NULL
3551 );
3552 ELSIF pr_old.shipment_type = 'BLANKET'
3553 THEN
3554 INSERT INTO JAI_RETRO_LINE_CHANGES
3555 ( LINE_CHANGE_ID
3556 , DOC_TYPE
3557 , DOC_HEADER_ID
3558 , DOC_LINE_ID
3559 , LINE_LOCATION_ID
3560 , FROM_HEADER_ID
3561 , FROM_LINE_ID
3562 , DOC_VERSION_NUMBER
3563 , PRICE_CHANGE_DATE
3564 , INVENTORY_ITEM_ID
3565 , ORGANIZATION_ID
3566 , ORIGINAL_UNIT_PRICE
3567 , MODIFIED_UNIT_PRICE
3568 , RECEIPT_PROCESSED_FLAG
3569 , EXCISE_ACTION
3570 , VAT_ACTION
3571 , EXCISE_INVOICE_NO
3572 , EXCISE_INVOICE_DATE
3573 , VAT_INVOICE_NO
3574 , VAT_INVOICE_DATE
3575 , RETRO_REQUEST_ID
3576 , DOC_NUMBER
3577 , VENDOR_ID
3578 , VENDOR_SITE_ID
3579 , CREATION_DATE
3580 , LAST_UPDATE_DATE
3581 , LAST_UPDATE_LOGIN
3582 , LAST_UPDATED_BY
3583 , CREATED_BY
3584 , OBJECT_VERSION_NUMBER
3585 )
3586 VALUES
3587 ( ln_retro_line_changes_id
3588 , 'RELEASE'
3589 , pr_old.po_header_id
3590 , pr_old.po_line_id
3591 , pr_old.line_location_id
3592 , pr_old.po_header_id
3593 , pr_old.po_line_id
3594 , ln_revision_num
3595 , pr_new.retroactive_date
3596 , ln_item_id
3597 , pr_old.ship_to_organization_id
3598 , pr_old.price_override
3599 , pr_new.price_override
3600 , 'N'
3601 , NULL
3602 , NULL
3603 , NULL
3604 , NULL
3605 , NULL
3606 , NULL
3607 , NULL
3608 , lv_doc_number
3609 , ln_vendor_id
3610 , ln_vendor_site_id
3611 , SYSDATE
3612 , SYSDATE
3613 , fnd_global.login_id
3614 , fnd_global.user_id
3615 , fnd_global.user_id
3616 , NULL
3617 );
3618 END IF;
3619 EXCEPTION
3620 WHEN OTHERS THEN
3621 pv_process_flag := 'UE';
3622 pv_process_message := 'When inserting line change history.'||SQLERRM;
3623 RETURN;
3624 END;
3625
3626 FOR tax_rec IN cur_loc_line_taxes
3627 LOOP
3628 BEGIN
3629 SELECT
3630 JAI_RETRO_TAX_CHANGES_S.NEXTVAL
3631 INTO
3632 ln_retro_tax_changes_id
3633 FROM dual;
3634 EXCEPTION
3635 WHEN no_data_found THEN
3636 pv_process_flag := 'UE';
3637 pv_process_message := 'When getting tax line sequence.'||SQLERRM;
3638 RETURN;
3639 WHEN OTHERS THEN
3640 pv_process_flag := 'UE';
3641 pv_process_message := 'When getting tax line sequence.'||SQLERRM;
3642 RETURN;
3643 END;
3644
3645 IF tax_rec.vendor_id = ln_vendor_id
3646 THEN
3647 lv_third_party_flag := 'N';
3648 ELSE
3649 lv_third_party_flag := 'Y';
3650 END IF;
3651
3652 BEGIN
3653 INSERT INTO JAI_RETRO_TAX_CHANGES
3654 ( TAX_CHANGE_ID
3655 , LINE_CHANGE_ID
3656 , TAX_LINE_NO
3657 , TAX_ID
3658 , TAX_NAME
3659 , TAX_TYPE
3660 , CURRENCY_CODE
3661 , ORIGINAL_TAX_AMOUNT
3662 , MODIFIED_TAX_AMOUNT
3663 , RECOVERABLE_FLAG
3664 , ADHOC_FLAG
3665 , THIRD_PARTY_FLAG
3666 , CREATION_DATE
3667 , CREATED_BY
3668 , LAST_UPDATE_DATE
3669 , LAST_UPDATE_LOGIN
3670 , LAST_UPDATED_BY
3671 , OBJECT_VERSION_NUMBER
3672 )
3673 VALUES
3674 ( ln_retro_tax_changes_id
3675 , ln_retro_line_changes_id
3676 , tax_rec.tax_line_no
3677 , tax_rec.tax_id
3678 , tax_rec.tax_name
3679 , tax_rec.tax_type
3680 , tax_rec.currency
3681 , tax_rec.tax_amount
3682 , -99999
3683 , tax_rec.modvat_flag
3684 , tax_rec.adhoc_flag
3685 , lv_third_party_flag
3686 , SYSDATE
3687 , fnd_global.user_id
3688 , SYSDATE
3689 , fnd_global.login_id
3690 , fnd_global.user_id
3691 , NULL
3692 );
3693 EXCEPTION
3694 WHEN OTHERS THEN
3695 pv_process_flag := 'UE';
3696 pv_process_message := 'When inserting tax line change history.'||SQLERRM;
3697 RETURN;
3698 END;
3699 END LOOP;
3700 END Insert_Price_Changes;
3701
3702 --==========================================================================
3703 -- PROCEDURE NAME:
3704 --
3705 -- Update_Price_Changes Public
3706 --
3707 -- DESCRIPTION:
3708 --
3709 -- This procedure is used to update tax amount in tax line changes table
3710 -- when doing retroactive price update.
3711 --
3712 -- PARAMETERS:
3713 -- In: pn_tax_amt NUMBER updated tax amount
3714 -- pn_line_no NUMBER tax line number
3715 -- pn_line_loc_id NUMBER line location id
3716 -- Out: pv_process_flag VARCHAR2 return flag
3717 -- pv_process_message VARCHAR2 return message
3718 --
3719 --
3720 -- DESIGN REFERENCES:
3721 -- JAI_Retroprice_TDD.doc
3722 --
3723 -- CHANGE HISTORY:
3724 --
3725 -- 14-JAN-2008 Kevin Cheng Created
3726 --==========================================================================
3727 PROCEDURE Update_Price_Changes
3728 ( pn_tax_amt IN NUMBER
3729 , pn_line_no IN NUMBER
3730 , pn_line_loc_id IN NUMBER
3731 , pv_process_flag OUT NOCOPY VARCHAR2
3732 , pv_process_message OUT NOCOPY VARCHAR2
3733 )
3734 IS
3735 BEGIN
3736 pv_process_flag := 'SS';
3737 pv_process_message := '';
3738
3739 UPDATE
3740 JAI_RETRO_TAX_CHANGES
3741 SET
3742 modified_tax_amount = pn_tax_amt
3743 , last_update_date = SYSDATE
3744 , last_updated_by = fnd_global.user_id
3745 , last_update_login = fnd_global.login_id
3746 WHERE tax_line_no = pn_line_no
3747 AND line_change_id = (SELECT
3748 line_change_id
3749 FROM
3750 JAI_RETRO_LINE_CHANGES jrpc
3751 WHERE jrpc.Line_location_id = pn_line_loc_id
3752 AND jrpc.Doc_Type IN ('RELEASE','RECEIPT','STANDARD PO')
3753 AND jrpc.Doc_version_number = (SELECT
3754 MAX(Doc_version_number)
3755 FROM
3756 JAI_RETRO_LINE_CHANGES jrpc1
3757 WHERE jrpc1.Line_location_id = pn_line_loc_id
3758 AND jrpc1.Doc_Type IN ('RELEASE','RECEIPT','STANDARD PO')
3759 )
3760 );
3761
3762 EXCEPTION
3763 WHEN OTHERS THEN
3764 pv_process_flag := 'UE';
3765 pv_process_message := 'When updating tax line change history.'||SQLERRM;
3766 END Update_Price_Changes;
3767
3768 END JAI_RETRO_PRC_PKG;