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