eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.JAI_PO_WF_UTIL_PUB

Source


1 PACKAGE BODY JAI_PO_WF_UTIL_PUB AS
2 /* $Header: jainpowfut.plb 120.7 2010/12/28 18:17:36 erma noship $ */
3   --+=======================================================================+
4   --|               Copyright (c) 1998 Oracle Corporation                   |
5   --|                       Redwood Shores, CA, USA                         |
6   --|                         All rights reserved.                          |
7   --+=======================================================================+
8   --| FILENAME                                                              |
9   --|     jainpowfut.pls                                                    |
10   --|                                                                       |
11   --| DESCRIPTION                                                           |
12   --|     This is the utility package for IL po notification.               |
13   --|                                                                       |
14   --|                                                                       |
15   --| PROCEDURE LIST                                                        |
16   --|      PROCEDURE Get_Req_Curr_Conv_Rate                                 |
17   --|      PROCEDURE Get_Currency_Convertion_Rate                           |
18   --|      PROCEDURE Get_Jai_Tax_Amount                                     |
19   --|      PROCEDURE Get_Jai_New_Tax_Amount                                 |
20   --|      PROCEDURE Populate_Session_GT                                    |
21   --|      FUNCTION  Get_Tax_Region                                         |
22   --|      FUNCTION  Get_Poreq_Tax                                          |
23   --|      FUNCTION  Get_Jai_Req_Tax_Disp                                   |
24   --|      FUNCTION  Get_Jai_Tax_Disp                                       |
25   --|      FUNCTION  Get_Jai_Open_Form_Command                              |
26   --|                                                                       |
27   --| HISTORY                                                               |
28   --|     2009-Feb-11 Eric Ma   Created
29   --|     2009-Aug-02 Eric Ma   Code change in the procedure of  Get_Jai_Open_Form_Command
30   --|                           removing all "" from the code for the bug 8744317
31   --|
32   --|     2009-Aug-03 Eric Ma   Remove all logic in the procedures  and
33   --|                           return NULL for all functions for bug 8757047 and 8757049
34   --|
35   --|     2009-Aug-11 Eric Ma   Restore all business logic for bug 8785506
36   --|
37   --|     2010-Dec-28 Eric Ma   Fix tax calc error for bug 10426971
38   --+======================================================================*/
39 
40   --==========================================================================
41   --    PROCEDURE   NAME:
42   --
43   --    Get_Req_Curr_Conv_Rate                     Public
44   --
45   --  DESCRIPTION:
46   --
47   --    This procedure is used to get the conversion rate for a Requsition line
48   --
49   --  PARAMETERS:
50   --      In: pn_req_header_id      IN   NUMBER               req header id
51   --          pn_req_line_id        IN   NUMBER               req line id
52   --          pv_tax_currency       IN   VARCHAR2             tax currency code
53   --          xn_conversion_rate    OUT  NUMBER               conversion  rate
54   --  DESIGN REFERENCES:
55   --
56   --
57   --  CHANGE HISTORY:
58   --
59   --           15-APR-2009   Eric Ma  created
60   --==========================================================================
61 
62   PROCEDURE Get_Req_Curr_Conv_Rate
63   ( pn_req_header_id     IN NUMBER
64   , pn_req_line_id       IN NUMBER
65   , pv_tax_currency      IN VARCHAR2
66   , xn_conversion_rate   OUT NOCOPY NUMBER
67   )
68   IS
69   ln_denominator_rate       NUMBER;
70   ln_numerator_rate         NUMBER;
71   ln_currency_rate          NUMBER;
72 
73   lv_base_currency           GL_SETS_OF_BOOKS.currency_code%TYPE;
74   lv_req_conv_curr_rate_type  po_requisition_lines_all.rate_type%TYPE;
75   ld_req_conv_curr_rate_date  po_requisition_lines_all.rate_date%TYPE;
76   ln_req_conv_curr_rate       po_requisition_lines_all.rate%TYPE;
77   lv_req_conv_curr_code       po_requisition_lines_all.currency_code%TYPE;
78 
79   lv_procedure_name     VARCHAR2(40):='Get_Req_Curr_Conv_Rate';
80   ln_dbg_level          NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
81   ln_proc_level         NUMBER:=FND_LOG.LEVEL_PROCEDURE;
82 
83   CURSOR Get_Curr_Conv_Rate_Cur
84   IS
85   SELECT
86     currency_code
87   , rate
88   FROM
89     PO_REQUISITION_LINES_ALL
90   WHERE  requisition_header_id = pn_req_header_id
91     AND  requisition_line_id   = pn_req_line_id ;
92 
93   BEGIN
94     --logging for debug
95     IF (ln_proc_level >= ln_dbg_level)
96     THEN
97       FND_LOG.STRING( ln_proc_level
98                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
99                     , 'Enter procedure'
100                     );
101     END IF; --l_proc_level>=l_dbg_level
102 
103     lv_base_currency := PO_CORE_S2.get_base_currency;
104 
105      OPEN  Get_Curr_Conv_Rate_Cur;
106     FETCH  Get_Curr_Conv_Rate_Cur
107      INTO  lv_req_conv_curr_code,ln_req_conv_curr_rate;
108     CLOSE  Get_Curr_Conv_Rate_Cur;
109 
110 
111     --dbms_output.put_line ( '-------Get_Req_Curr_Conv_Rate-------');
112     --dbms_output.put_line ( 'pn_req_header_id             ='||pn_req_header_id);
113     --dbms_output.put_line ( 'pn_req_line_id         ='||pn_req_line_id);
114     --dbms_output.put_line ( 'pv_tax_currency        ='||pv_tax_currency);
115     --dbms_output.put_line ( 'ln_req_conv_curr_rate   ='||ln_req_conv_curr_rate);
116     --dbms_output.put_line ( 'lv_base_currency        ='||lv_base_currency);
117 
118     --When the line currency is same as base currency, no conversion rate can be retrived
119     --from the req table. So assign the value 1 to the rate
120     IF (lv_req_conv_curr_code = lv_base_currency)
121     THEN
122       ln_req_conv_curr_rate :=1;
123     END IF;
124 
125     --When no foreign currency code in the line level,
126     --the line currency is same as the currency in header level
127     --and the document currency of requesition is always the base currency
128     --So converstion rate is 1
129     IF lv_req_conv_curr_code IS NULL
130     THEN
131       lv_req_conv_curr_code :=lv_base_currency;
132       ln_req_conv_curr_rate :=1;
133     END IF;
134 
135     --For all of the belwo case,convert the tax from foreign
136     -- currency to the base currency by the  ln_currency_rate
137 
138     IF (pv_tax_currency = lv_req_conv_curr_code)
139     THEN
140       -- If the currency in the current tax line is same as the foreign
141       -- currency code defined in the Req Line, use the REQ line convertion rate
142       ln_currency_rate :=  ln_req_conv_curr_rate;
143 
144       --dbms_output.put_line ( 'if 1');
145     ELSIF (pv_tax_currency = lv_base_currency)
146     THEN
147       -- If the tax currency equals to the base currency,
148       -- then the converstion_rate  is 1
149       ln_currency_rate := 1;
150 
151       --dbms_output.put_line ( 'if 2');
152     ELSE
153      -- In other cases, coverting the currency by the type and date
154      -- defined in the REQ line level
155       gl_currency_api.get_closest_triangulation_rate
156       ( x_from_currency    => pv_tax_currency
157       , x_to_currency      => lv_base_currency
158       , x_conversion_date  => ld_req_conv_curr_rate_date
159       , x_conversion_type  => lv_req_conv_curr_rate_type
160       , x_max_roll_days    => 5
161       , x_denominator      => ln_denominator_rate
162       , x_numerator        => ln_numerator_rate
163       , x_rate             => ln_currency_rate
164       );
165       --dbms_output.put_line ( 'if 3');
166     END IF; --(pv_tax_currency = ln_req_conv_curr_code)
167 
168     xn_conversion_rate := ln_currency_rate;
169 
170     --logging for debug
171     IF (ln_proc_level >= ln_dbg_level)
172     THEN
173       FND_LOG.STRING( ln_proc_level
174                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
175                     , 'Exit procedure'
176                     );
177     END IF; -- (ln_proc_level>=ln_dbg_level)
178   EXCEPTION
179     WHEN OTHERS
180     THEN
181       IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
182       THEN
183         FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
184                       , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
185                       , Sqlcode||Sqlerrm);
186       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
187   END Get_Req_Curr_Conv_Rate;
188 
189   --==========================================================================
190   --    PROCEDURE   NAME:
191   --
192   --    Get_Currency_Convertion_Rate                     Public
193   --
194   --  DESCRIPTION:
195   --
196   --    This procedure is used to get the conversion rate for PO/PA
197   --
198   --  PARAMETERS:
199   --      In: pn_document_id      IN   NUMBER               PO/PA header id
200   --          pv_tax_currency     IN   VARCHAR2             tax currency code
201   --          xn_conversion_rate  OUT  NUMBER               conversion  rate
202   --  DESIGN REFERENCES:
203   --
204   --
205   --  CHANGE HISTORY:
206   --
207   --           15-APR-2009   Eric Ma  created
208   --==========================================================================
209   PROCEDURE Get_Currency_Convertion_Rate
210   ( pn_document_id   IN NUMBER
211   , pv_tax_currency  IN VARCHAR2
212   , xn_conversion_rate OUT NOCOPY NUMBER
213   )
214   IS
215   ln_denominator_rate       NUMBER;
216   ln_numerator_rate         NUMBER;
217   ln_currency_rate          NUMBER;
218 
219   lv_currency_code          PO_HEADERS_ALL.currency_code%TYPE;
220   lv_po_currency_rate_type  PO_HEADERS_ALL.rate_type%TYPE;
221   ld_po_currency_rate_date  PO_HEADERS_ALL.rate_date%TYPE;
222   ln_po_currency_rate       PO_HEADERS_ALL.rate%TYPE;
223   lv_base_currency          GL_SETS_OF_BOOKS.currency_code%TYPE;
224   lv_po_currency            PO_HEADERS_ALL.currency_code%TYPE;
225 
226   lv_procedure_name     VARCHAR2(40):='Get_Currency_Convertion_Rate';
227   ln_dbg_level          NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
228   ln_proc_level         NUMBER:=FND_LOG.LEVEL_PROCEDURE;
229   BEGIN
230     --logging for debug
231     IF (ln_proc_level >= ln_dbg_level)
232     THEN
233       FND_LOG.STRING( ln_proc_level
234                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
235                     , 'Enter procedure'
236                     );
237     END IF; --l_proc_level>=l_dbg_level
238 
239     -- get the currency convert information from po/pa header level
240     PO_CORE_S2.GET_PO_CURRENCY_INFO (p_po_header_id      => pn_document_id,
241                                      x_currency_code     => lv_currency_code,
242                                      x_curr_rate_type    => lv_po_currency_rate_type,
243                                      x_curr_rate_date    => ld_po_currency_rate_date,
244                                      x_currency_rate     => ln_po_currency_rate);
245 
246      -- get the PO/PA currency and base currency
247      PO_CORE_S2.GET_PO_CURRENCY (x_object_id      =>pn_document_id,
248                                  x_base_currency  =>lv_base_currency ,
249                                  x_po_currency    =>lv_po_currency);
250 
251 
252     IF (pv_tax_currency <> lv_po_currency        --TAX CURRENCY <> PO/PA CURRENCY
253         AND pv_tax_currency = lv_base_currency   --TAX CURRENCY =  PO/PA CURRENCY
254         AND lv_po_currency  = lv_currency_code   --PO/PA CURRENCY  = THE CURRENCY DEFINED IN CONVERSION FORM
255        )
256     THEN
257       -- the tax currency is different from PO/PA currency
258       -- so change the tax currency to the PO/PA currency
259       -- and also the converstion rate is defined in the po_header level
260       ln_currency_rate :=1/ln_po_currency_rate;
261     ELSIF (pv_tax_currency = lv_po_currency )
262     THEN
263       -- the tax currency eaquals to PO/PA currency
264       -- then the converation rate is 1
265       ln_currency_rate :=1;
266     ELSE
267       -- the converation rate is not defined
268       -- user the convertion type / converstion date defined in the po header
269       -- change the tax currency to the PO/PA currency
270       gl_currency_api.get_closest_triangulation_rate
271       ( x_from_currency    => pv_tax_currency
272       , x_to_currency      => lv_po_currency
273       , x_conversion_date  => ld_po_currency_rate_date
274       , x_conversion_type  => lv_po_currency_rate_type
275       , x_max_roll_days    => 5
276       , x_denominator      => ln_denominator_rate
277       , x_numerator        => ln_numerator_rate
278       , x_rate             => ln_currency_rate
279       );
280     END IF;
281 
282     xn_conversion_rate := ln_currency_rate;
283 
284     --logging for debug
285     IF (ln_proc_level >= ln_dbg_level)
286     THEN
287       FND_LOG.STRING( ln_proc_level
288                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
289                     , 'Exit procedure'
290                     );
291     END IF; -- (ln_proc_level>=ln_dbg_level)
292   EXCEPTION
293     WHEN OTHERS
294     THEN
295       IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
296       THEN
297         FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
298                       , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
299                       , Sqlcode||Sqlerrm);
300       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
301   END Get_Currency_Convertion_Rate;
302 
303   --==========================================================================
304   --    PROCEDURE   NAME:
305   --
306   --    Get_Tax_Amount_Info                     Public
307   --
308   --  DESCRIPTION:
309   --
310   --    This procedure is used to get the exclusive tax amount and non recoverable exclusive tax
311   --    amount for a given tax id and tax amount
312   --
313   --  PARAMETERS:
314   --      In: pn_tax_id            IN   NUMBER               tax identifier
315   --          pn_tax_amount        IN   NUMBER               tax amount
316   --          pn_conver_rate       IN   NUMBER DEFAULT 1     converstion rate between different currency
317   --          pn_rounding_factor   IN   NUMBER DEFAULT NULL  rounding factor
318   --          xn_excl_tax_amount    OUT  NUMBER               exclusive tax amount
319   --          xn_excl_nr_tax_amount OUT  NUMBER               exclusive non recoverable tax amount
320   --          pn_trx_rec_flag      IN   VARCHAR2             The modvat flat in tax transaction level
321   --          xv_tax_type          OUT  VARCHAR2             Tax Type
322   --  DESIGN REFERENCES:
323   --
324   --
325   --  CHANGE HISTORY:
326   --
327   --           10-FEB-2009   Eric Ma  created
328   --==========================================================================
329 
330   PROCEDURE  Get_Tax_Amount_Info
331   ( pn_tax_id             IN NUMBER
332   , pn_tax_amount         IN NUMBER
333   , pn_conver_rate        IN NUMBER DEFAULT 1
334   , pn_rounding_factor    IN NUMBER DEFAULT NULL
335   , xn_excl_tax_amount    OUT NOCOPY NUMBER
336   , xn_excl_nr_tax_amount OUT NOCOPY NUMBER
337   , pn_trx_rec_flag        IN VARCHAR2 DEFAULT 'N'  -- add by Xiao Lv for MADVAT flag
338   , xv_tax_type           OUT NOCOPY VARCHAR2       -- added by Eric Ma for bug10426971 on 28-Dec-2010
339   )
340   IS
341 
342   CURSOR get_jai_cmn_taxes_all_cur
343   IS
344   SELECT
345     NVL(inclusive_tax_flag,'N')
346   , NVL(mod_cr_percentage,0)
347   , NVL(rounding_factor,0)
348   , tax_type
349   FROM
350     jai_cmn_taxes_all
351   WHERE tax_id = pn_tax_id;
352 
353 
354   ln_nr_tax_amount      NUMBER;
355   lv_incl_tax_flag      jai_cmn_taxes_all.inclusive_tax_flag%TYPE;
356   ln_nr_mod_cr_percent  jai_cmn_taxes_all.MOD_CR_PERCENTAGE%TYPE;
357   ln_mod_cr_percent     jai_cmn_taxes_all.MOD_CR_PERCENTAGE%TYPE;
358   ln_rounding_factor    jai_cmn_taxes_all.rounding_factor%TYPE;
359   lv_tax_type           jai_cmn_taxes_all.tax_type%TYPE;       -- added by Eric Ma for bug10426971 on 28-Dec-2010
360 
361   lv_procedure_name     VARCHAR2(40):='Get_Tax_Amount_Info';
362   ln_dbg_level          NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
363   ln_proc_level         NUMBER:=FND_LOG.LEVEL_PROCEDURE;
364   BEGIN
365     --logging for debug
366     IF (ln_proc_level >= ln_dbg_level)
367     THEN
368       FND_LOG.STRING( ln_proc_level
369                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
370                     , 'Enter procedure'
371                     );
372     END IF; --l_proc_level>=l_dbg_level
373 
374     OPEN  get_jai_cmn_taxes_all_cur;
375     FETCH get_jai_cmn_taxes_all_cur
376     INTO
377       lv_incl_tax_flag
378     , ln_mod_cr_percent
379     , ln_rounding_factor
380     , lv_tax_type;
381     CLOSE get_jai_cmn_taxes_all_cur;
382 
383     --dbms_output.put_line ( '-------Get_Tax_Amount_Info-------');
384     --dbms_output.put_line ( 'lv_incl_tax_flag    ='||lv_incl_tax_flag);
385     --dbms_output.put_line ( 'ln_mod_cr_percent ='||ln_mod_cr_percent);
386     --dbms_output.put_line ( 'ln_rounding_factor    ='||ln_rounding_factor);
387 
388 
389     --dbms_output.put_line ( 'pn_tax_id          ='||pn_tax_id);
390     --dbms_output.put_line ( 'pn_tax_amount      ='||pn_tax_amount);
391     --dbms_output.put_line ( 'pn_conver_rate     ='||pn_conver_rate);
392     --dbms_output.put_line ( 'pn_rounding_factor ='||pn_rounding_factor);
393     --dbms_output.put_line ( 'pn_trx_rec_flag    ='||pn_trx_rec_flag);
394 
395     IF (lv_incl_tax_flag ='Y')--inclusive tax
396     THEN
397       xn_excl_tax_amount    := 0;
398       xn_excl_nr_tax_amount := 0;
399     ELSE --exclusive tax
400       IF (pn_trx_rec_flag = 'Y')
401       THEN
402         ln_nr_mod_cr_percent  := (100-ln_mod_cr_percent)/100;
403       ELSE
404         ln_nr_mod_cr_percent  := 1;
405       END IF; -- (pn_trx_rec_flag)
406 
407       ln_rounding_factor    := NVL(pn_rounding_factor,ln_rounding_factor);
408       ln_nr_tax_amount      := pn_tax_amount * ln_nr_mod_cr_percent * pn_conver_rate;
409       ln_nr_tax_amount      := ROUND(ln_nr_tax_amount,ln_rounding_factor);
410     --dbms_output.put_line ( 'ln_nr_tax_amount    ='||ln_nr_tax_amount);
411       xn_excl_nr_tax_amount  := ln_nr_tax_amount;
412       xn_excl_tax_amount     := ROUND(pn_tax_amount* pn_conver_rate,ln_rounding_factor);
413     END IF;
414 
415     xv_tax_type := lv_tax_type; -- added by Eric Ma for bug10426971 on 28-Dec-2010
416 
417     --dbms_output.put_line ( 'xn_excl_nr_tax_amount ='||xn_excl_nr_tax_amount);
418     --dbms_output.put_line ( 'xn_excl_tax_amount    ='||xn_excl_tax_amount);
419     --dbms_output.put_line ( 'xv_tax_type           ='||xv_tax_type);
420 
421     --logging for debug
422     IF (ln_proc_level >= ln_dbg_level)
423     THEN
424       FND_LOG.STRING( ln_proc_level
425                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
426                     , 'Exit procedure'
427                     );
428     END IF; -- (ln_proc_level>=ln_dbg_level)
429   EXCEPTION
430     WHEN OTHERS
431     THEN
432       IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
433       THEN
434         FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
435                       , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
436                       , Sqlcode||Sqlerrm);
437       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
438       RAISE;
439   END Get_Tax_Amount_Info;
440 
441   --==========================================================================
442   --    PROCEDURE   NAME:
443   --
444   --    Get_Jai_Tax_Amount                     Public
445   --
446   --  DESCRIPTION:
447   --
448   --    This procedure is used to get the exclusive tax amount and non recoverable exclusive tax
449   --    amount for a PO,PR or a RELEASE
450   --
451   --  PARAMETERS:
452   --      In: pv_document_type         IN VARCHAR2,                       document type  : requisition,po,release
453   --          pn_document_id           IN NUMBER,                         document_id    : req header id,po header id
454   --          pn_release_num           IN NUMBER DEFAULT NULL,            release nmuber : for release,it receive release number
455   --          xn_excl_tax_amount      OUT NOCOPY NUMBER,                  exclusive tax amount for the document
456   --          xn_excl_nr_tax_amount   OUT NOCOPY NUMBER                   exclusive non recoverable tax amount for the document
457   --  DESIGN REFERENCES:
458   --
459   --
460   --  CHANGE HISTORY:
461   --
462   --           10-FEB-2009   Eric Ma  created
463   --           25-MAR-2009   Xiao Lv  modified
464   --==========================================================================
465 
466 
467   PROCEDURE Get_Jai_Tax_Amount
468   ( pv_document_type       IN VARCHAR2,
469     pn_document_id         IN NUMBER,
470     pn_requisition_line_id IN NUMBER DEFAULT NULL, --add by Xiao Lv
471     xn_excl_tax_amount    OUT NOCOPY NUMBER,
472     xn_excl_nr_tax_amount OUT NOCOPY NUMBER
473   )
474   IS
475   ln_tax_id                 NUMBER;
476   ln_tax_amount             NUMBER;
477   ln_excl_tax_amount        NUMBER :=0;
478   ln_excl_nr_tax_amount     NUMBER :=0;
479   ln_total_tax_amount       NUMBER :=0;
480   ln_total_nr_tax_amount    NUMBER :=0;
481   ln_currency_rate          NUMBER;
482   ln_req_line_id            JAI_PO_REQ_LINE_TAXES.requisition_line_id%TYPE;
483   lv_req_tax_currency       JAI_PO_REQ_LINE_TAXES.currency%TYPE;
484   lv_po_tax_currency        JAI_PO_TAXES.currency%TYPE;
485   lv_rel_tax_currency       JAI_PO_TAXES.currency%TYPE;
486   lv_modvat_flag            VARCHAR2(1); --add by Xiao Lv for IL po notification on Mar-25-2009
487   ln_po_header_id           PO_RELEASES_ALL.PO_HEADER_ID%TYPE;
488   lv_tax_type               jai_cmn_taxes_all.tax_type%TYPE;       -- added by Eric Ma for bug10426971 on 28-Dec-2010
489 
490   CURSOR Get_Req_tax_Cur
491   IS
492   SELECT
493     tax_id
494   , NVL(tax_amount,0)
495   , currency
496   , requisition_line_id
497   , NVL(modvat_flag,'N') --add by Xiao Lv for IL po notification on Mar-25-2009
498   FROM
499     JAI_PO_REQ_LINE_TAXES
500   WHERE REQUISITION_HEADER_ID = pn_document_id
501     AND REQUISITION_LINE_ID   = NVL(pn_requisition_line_id, REQUISITION_LINE_ID); --add by Xiao
502 
503 
504   CURSOR Get_Po_tax_Cur
505   IS
506   SELECT
507     tax_id
508   , NVL(tax_amount, 0)
509   , currency
510   , NVL(modvat_flag,'N') --add by Xiao Lv for IL po notification on Mar-25-2009
511   FROM
512     JAI_PO_TAXES
513   WHERE PO_HEADER_ID = pn_document_id;
514 
515 
516   CURSOR Get_Rel_tax_Cur
517   IS
518   SELECT
519     JPT.tax_id
520   , NVL(JPT.tax_amount,0)
521   , JPT.currency
522   , NVL(JPT.modvat_flag,'N') --add by Xiao Lv for IL po notification on Mar-25-2009
523   FROM
524     PO_LINE_LOCATIONS_ALL  PLLA
525   , JAI_PO_TAXES JPT
526   WHERE PLLA.LINE_LOCATION_ID = JPT.LINE_LOCATION_ID
527     AND PLLA.PO_RELEASE_ID   = pn_document_id;
528 
529 
530   CURSOR Get_Po_Header_Id_Cur
531   IS
532   SELECT
533     POA.PO_HEADER_ID
534   FROM
535     PO_RELEASES_ALL POA
536   WHERE POA.PO_RELEASE_ID = pn_document_id;
537 
538 
539   lv_procedure_name     VARCHAR2(40) := 'Get_Jai_Tax_Amount';
540   ln_dbg_level          NUMBER       := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
541   ln_proc_level         NUMBER       := FND_LOG.LEVEL_PROCEDURE;
542 
543   BEGIN
544     --logging for debug
545     IF (ln_proc_level >= ln_dbg_level)
546     THEN
547       FND_LOG.STRING( ln_proc_level
548                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
549                     , 'Enter procedure'
550                     );
551     END IF; --l_proc_level>=l_dbg_level
552 
553     IF pv_document_type= JAI_PO_WF_UTIL_PUB.G_REQ_DOC_TYPE
554     THEN
555       --calculate requsition tax
556       OPEN Get_Req_tax_Cur;
557       LOOP
558         FETCH Get_Req_tax_Cur
559          INTO ln_tax_id
560             , ln_tax_amount
561             , lv_req_tax_currency
562             , ln_req_line_id
563             , lv_modvat_flag;
564         EXIT WHEN Get_Req_tax_Cur%NOTFOUND;
565 
566        --dbms_output.put_line ( '-------Get_JAI_Tax_Amount-------');
567        --dbms_output.put_line ( 'ln_tax_id             ='||ln_tax_id);
568        --dbms_output.put_line ( 'ln_tax_amount         ='||ln_tax_amount);
569        --dbms_output.put_line ( 'lv_req_tax_currency   ='||lv_req_tax_currency);
570        --dbms_output.put_line ( 'ln_req_line_id        ='||ln_req_line_id);
571        --dbms_output.put_line ( 'lv_modvat_flag        ='||lv_modvat_flag);
572 
573         Get_Req_Curr_Conv_Rate ( pn_req_header_id    => pn_document_id
574                                , pn_req_line_id      => ln_req_line_id
575                                , pv_tax_currency     => lv_req_tax_currency
576                                , xn_conversion_rate  => ln_currency_rate
577                                );
578        --dbms_output.put_line ( 'ln_currency_rate        ='||ln_currency_rate);
579 
580         Get_Tax_Amount_Info ( pn_tax_id             =>ln_tax_id
581                             , pn_tax_amount         =>ln_tax_amount
582                             , pn_conver_rate        =>ln_currency_rate
583                             , xn_excl_tax_amount    =>ln_excl_tax_amount
584                             , xn_excl_nr_tax_amount =>ln_excl_nr_tax_amount
585                             , pn_trx_rec_flag       =>lv_modvat_flag -- add by Xiao Lv on Mar-25-2009
586                             , xv_tax_type           =>lv_tax_type    -- added by Eric Ma for bug10426971 on 28-Dec-2010
587                             );
588 
589          -- If condition is added for bug10426971
590          -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,Begin
591          ------------------------------------------------------------------------
592          IF lv_tax_type <>'TDS'
593          THEN
594            ln_total_tax_amount    :=  ln_total_tax_amount    + ln_excl_tax_amount;
595            ln_total_nr_tax_amount :=  ln_total_nr_tax_amount + ln_excl_nr_tax_amount;
596          END IF;
597          ------------------------------------------------------------------------
598          -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,End
599 
600        --dbms_output.put_line ( 'ln_total_tax_amount       ='||ln_total_tax_amount);
601        --dbms_output.put_line ( 'ln_total_nr_tax_amount    ='||ln_total_nr_tax_amount);
602       END LOOP;
603 
604       CLOSE Get_Req_tax_Cur;
605     ELSIF pv_document_type= JAI_PO_WF_UTIL_PUB.G_PO_DOC_TYPE
606     THEN
607       --calculate PO tax
608       OPEN Get_Po_tax_Cur;
609       LOOP
610         FETCH Get_Po_tax_Cur
611 
612         INTO
613           ln_tax_id
614         , ln_tax_amount
615         , lv_po_tax_currency
616         , lv_modvat_flag;
617         EXIT WHEN Get_Po_tax_Cur%NOTFOUND;
618 
619         Get_Currency_Convertion_Rate ( pn_document_id     => pn_document_id
620                                      , pv_tax_currency    => lv_po_tax_currency
621                                      , xn_conversion_rate => ln_currency_rate
622                                      );
623 
624         Get_Tax_Amount_Info ( pn_tax_id             =>ln_tax_id
625                             , pn_tax_amount         =>ln_tax_amount
626                             , pn_conver_rate        =>ln_currency_rate
627                             , xn_excl_tax_amount    =>ln_excl_tax_amount
628                             , xn_excl_nr_tax_amount =>ln_excl_nr_tax_amount
629                             , pn_trx_rec_flag       =>lv_modvat_flag -- add by Xiao Lv on Mar-25-2009
630                             , xv_tax_type           =>lv_tax_type    -- added by Eric Ma for bug10426971 on 28-Dec-2010
631                             );
632 
633          -- If condition is added for bug10426971
634          -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,Begin
635          ------------------------------------------------------------------------
636          IF lv_tax_type <>'TDS'
637          THEN
638            ln_total_tax_amount    :=  ln_total_tax_amount    + ln_excl_tax_amount;
639            ln_total_nr_tax_amount :=  ln_total_nr_tax_amount + ln_excl_nr_tax_amount;
640          END IF;
641          ------------------------------------------------------------------------
642          -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,End
643 
644        --dbms_output.put_line ( 'ln_total_tax_amount       ='||ln_total_tax_amount);
645        --dbms_output.put_line ( 'ln_total_nr_tax_amount    ='||ln_total_nr_tax_amount);
646       END LOOP;
647 
648       CLOSE Get_Po_tax_Cur;
649     ELSIF pv_document_type= JAI_PO_WF_UTIL_PUB.G_REL_DOC_TYPE
650     THEN
651       --Get po header id
652       OPEN  Get_Po_Header_Id_Cur;
653       FETCH Get_Po_Header_Id_Cur
654        INTO ln_po_header_id;
655       CLOSE Get_Po_Header_Id_Cur;
656 
657       --calculate Release tax
658       OPEN Get_Rel_tax_Cur;
659       LOOP
660         FETCH Get_Rel_tax_Cur
661         INTO
662           ln_tax_id
663         , ln_tax_amount
664         , lv_rel_tax_currency
665         , lv_modvat_flag;
666         EXIT  WHEN Get_Rel_tax_Cur%NOTFOUND;
667 
668          Get_Currency_Convertion_Rate ( pn_document_id     => ln_po_header_id
669                                       , pv_tax_currency    => lv_rel_tax_currency
670                                       , xn_conversion_rate => ln_currency_rate
671                                       );
672 
673 
674          Get_Tax_Amount_Info ( pn_tax_id             =>ln_tax_id
675                              , pn_tax_amount         =>ln_tax_amount
676                              , pn_conver_rate        =>ln_currency_rate
677                              , xn_excl_tax_amount    =>ln_excl_tax_amount
678                              , xn_excl_nr_tax_amount =>ln_excl_nr_tax_amount
679                              , pn_trx_rec_flag       =>lv_modvat_flag -- add by Xiao Lv on Mar-25-2009
680                              , xv_tax_type           =>lv_tax_type    -- added by Eric Ma for bug10426971 on 28-Dec-2010
681                              );
682 
683          -- If condition is added for bug10426971
684          -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,Begin
685          ------------------------------------------------------------------------
686          IF lv_tax_type <>'TDS'
687          THEN
688            ln_total_tax_amount    :=  ln_total_tax_amount    + ln_excl_tax_amount;
689            ln_total_nr_tax_amount :=  ln_total_nr_tax_amount + ln_excl_nr_tax_amount;
690          END IF;
691          ------------------------------------------------------------------------
692          -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,End
693 
694        --dbms_output.put_line ( 'ln_total_tax_amount       ='||ln_total_tax_amount);
695        --dbms_output.put_line ( 'ln_total_nr_tax_amount    ='||ln_total_nr_tax_amount);
696       END LOOP;
697 
698       CLOSE Get_Rel_tax_Cur;
699     END IF;--(p_document_type=JAI_PO_WF_UTIL_PUB.G_REQ_DOC_TYPE)
700 
701     --set the values to output parameters
702     xn_excl_tax_amount    := NVL(ln_total_tax_amount,0);
703     xn_excl_nr_tax_amount := NVL(ln_total_nr_tax_amount,0);
704 
705 
706     --logging for debug
707     IF (ln_proc_level >= ln_dbg_level)
708     THEN
709       FND_LOG.STRING( ln_proc_level
710                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
711                     , 'Exit procedure'
712                     );
713     END IF; -- (ln_proc_level>=ln_dbg_level)
714 
715   EXCEPTION
716     WHEN OTHERS
717     THEN
718       IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
719       THEN
720         FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
721                       , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
722                       , Sqlcode||Sqlerrm);
723       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
724       RAISE;
725   END Get_Jai_Tax_Amount;
726 
727   --==========================================================================
728   --    PROCEDURE   NAME:
729   --
730   --    Get_Tax_Region                     Public
731   --
732   --  DESCRIPTION:
733   --
734   --    This procedure is used to return the tax region code
735   --
736   --  PARAMETERS:
737   --      In: pv_document_type      IN   VARCHAR2     document type
738   --          pn_document_id        IN   NUMBER       document header id
739   --          pn_org_id             IN   NUMBER       organization id
740   --  DESIGN REFERENCES:
741   --
742   --
743   --  CHANGE HISTORY:
744   --
745   --           15-APR-2009   Eric Ma  created
746   --==========================================================================
747 
748   FUNCTION Get_Tax_Region
749   ( pv_document_type      IN VARCHAR2   DEFAULT NULL
750   , pn_document_id        IN NUMBER     DEFAULT NULL
751   , pn_org_id             IN NUMBER     DEFAULT NULL
752   ) RETURN VARCHAR2
753   IS
754   ln_org_id             NUMBER := pn_org_id;
755   lv_procedure_name     VARCHAR2(40):='Get_Tax_Region';
756   ln_dbg_level          NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
757   ln_proc_level         NUMBER:=FND_LOG.LEVEL_PROCEDURE;
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     --if org id is not availabe, get the org id by the parameter of document type and
769     -- document header id
770     IF ( ln_org_id IS NULL
771          AND (pv_document_type IS NOT NULL
772               AND pn_document_id  IS NOT NULL
773              )
774        )
775     THEN
776       PO_REQAPPROVAL_INIT1.get_multiorg_context
777       ( document_type => pv_document_type
778       , document_id   => pn_document_id
779       , x_orgid       => ln_org_id
780       );
781     END IF;
782 
783     --Check if indian localization is enabled or not by the org id
784     IF (jai_cmn_utils_pkg.check_jai_exists
785          ( p_calling_object     => GV_MODULE_PREFIX ||'.' || lv_procedure_name
786          , p_org_id             => ln_org_id
787          )
788        )
789     THEN
790       --logging for debug
791       IF (ln_proc_level >= ln_dbg_level)
792       THEN
793         FND_LOG.STRING( ln_proc_level
794                       , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
795                       , 'Exit procedure'
796                       );
797       END IF; -- (ln_proc_level>=ln_dbg_level)
798 
799       RETURN 'JAI';
800     ELSE
801       --logging for debug
802       IF (ln_proc_level >= ln_dbg_level)
803       THEN
804         FND_LOG.STRING( ln_proc_level
805                       , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
806                       , 'Exit procedure'
807                       );
808       END IF; -- (ln_proc_level>=ln_dbg_level)
809 
810       RETURN NULL;
811     END IF;--(jai_cmn_utils_pkg.check_jai_exists)
812   EXCEPTION
813   WHEN OTHERS
814   THEN
815     IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
816     THEN
817       FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
818                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
819                     , Sqlcode||Sqlerrm);
820     END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
821     RAISE;
822   END Get_Tax_Region;
823 
824 
825   --==========================================================================
826   --    PROCEDURE   NAME:
827   --
828   --    Get_Jai_New_Tax_Amount                     Public
829   --
830   --  DESCRIPTION:
831   --
832   --    This procedure is used to get the exclusive tax amount and non recoverable exclusive tax
833   --    amount for a PO,PR or a RELEASE
834   --
835   --  PARAMETERS:
836   --      In: pv_document_type         IN VARCHAR2,                       document type  : requisition,po,release
837   --          pn_document_id           IN NUMBER,                         document_id    : req header id,po header id
838   --          pn_release_num           IN NUMBER DEFAULT NULL,            release nmuber : for release,it receive release number
839   --          xn_excl_tax_amount      OUT NOCOPY NUMBER,                  exclusive tax amount for the document
840   --          xn_excl_nr_tax_amount   OUT NOCOPY NUMBER                   exclusive non recoverable tax amount for the document
841   --  DESIGN REFERENCES:
842   --
843   --
844   --  CHANGE HISTORY:
845   --
846   --           7-Apr-2009   Xiao Lv  created
847   --==========================================================================
848 
849 
850   PROCEDURE Get_Jai_New_Tax_Amount
851   ( pv_document_type         IN VARCHAR2,
852     pn_document_id           IN NUMBER,
853     pn_chg_request_group_id  IN NUMBER,
854     xn_excl_tax_amount       OUT NOCOPY NUMBER,
855     xn_excl_nr_tax_amount    OUT NOCOPY NUMBER
856   )
857   IS
858   ln_tax_id                   NUMBER;
859   ln_tax_amount               NUMBER;
860   ln_excl_tax_amount          NUMBER :=0; --exclusive tax amount for a tax line
861   ln_excl_nr_tax_amount       NUMBER :=0; --exclusive nr tax amount for a tax line
862   ln_total_tax_amount         NUMBER :=0; --tax lines amount summary  for a req line
863   ln_total_nr_tax_amount      NUMBER :=0; --tax lines amount summary of nr tax for a req line
864 
865   ln_new_tax_amount           NUMBER :=0; --new tax amount for a req line
866   ln_new_nr_tax_amount        NUMBER :=0; --new nr tax amount for a req line
867   ln_new_total_tax_amount     NUMBER :=0; --total new tax amount for a req
868   ln_new_total_nr_tax_amount  NUMBER :=0; --total new nr tax amount for a req
869 
870   ln_currency_rate            NUMBER;
871   ln_req_line_id              JAI_PO_REQ_LINE_TAXES.requisition_line_id%TYPE;
872   lv_req_tax_currency         JAI_PO_REQ_LINE_TAXES.currency%TYPE;
873   lv_modvat_flag              VARCHAR2(1); --add by Xiao Lv for IL po notification on Mar-25-2009
874   lv_adhoc_flag               VARCHAR2(1); --add by Xiao Lv for adhoc tax flag.
875   ln_total_adhoc_tax_amount   NUMBER :=0;
876   ln_old_quantity             NUMBER;
877   ln_new_quantity             NUMBER;
878   lv_tax_type                 jai_cmn_taxes_all.tax_type%TYPE;       -- added by Eric Ma for bug10426971 on 28-Dec-2010
879 
880   CURSOR Get_Req_tax_Cur
881   IS
882   SELECT
883     jprlt.tax_id
884   , jprlt.tax_amount
885   , jprlt.currency
886   , NVL(jprlt.modvat_flag,'N')
887   , NVL(jcta.adhoc_flag, 'N')
888   FROM
889     JAI_PO_REQ_LINE_TAXES jprlt
890   , JAI_CMN_TAXES_ALL     jcta
891   WHERE jcta.tax_id = jprlt.tax_id
892     AND jprlt.requisition_line_id   = ln_req_line_id
893     AND jprlt.REQUISITION_HEADER_ID = pn_document_id;
894 
895   CURSOR Get_New_Old_Quantity_Cur
896   IS
897   SELECT
898     document_line_id
899   , NVL(old_quantity,1)
900   , NVL(new_quantity,0)
901   FROM
902     po_change_requests
903   WHERE document_header_id = pn_document_id
904     and request_level='LINE'
905     and change_request_group_id =pn_chg_request_group_id;
906 
907   lv_procedure_name     VARCHAR2(40) := 'Get_Jai_New_Tax_Amount';
908   ln_dbg_level          NUMBER       := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
909   ln_proc_level         NUMBER       := FND_LOG.LEVEL_PROCEDURE;
910 
911   BEGIN
912     --logging for debug
913     IF (ln_proc_level >= ln_dbg_level)
914     THEN
915       FND_LOG.STRING( ln_proc_level
916                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
917                     , 'Enter procedure'
918                     );
919     END IF; --l_proc_level>=l_dbg_level
920 
921     IF pv_document_type= JAI_PO_WF_UTIL_PUB.G_REQ_DOC_TYPE
922     THEN
923       OPEN Get_New_Old_Quantity_Cur;
924       LOOP
925         FETCH Get_New_Old_Quantity_Cur
926          INTO ln_req_line_id
927             , ln_old_quantity
928             , ln_new_quantity;
929         EXIT WHEN Get_New_Old_Quantity_Cur%NOTFOUND;
930 
931         --dbms_output.put_line('ln_req_line_id: '||ln_req_line_id);
932         --dbms_output.put_line('ln_old_quantity: '||ln_old_quantity);
933         --dbms_output.put_line('ln_new_quantity: '||ln_new_quantity);
934 
935         ln_total_tax_amount    :=0;
936         ln_total_nr_tax_amount :=0;
937         ln_total_adhoc_tax_amount :=0;
938 
939         OPEN Get_Req_tax_Cur;
940         LOOP
941           FETCH Get_Req_tax_Cur
942            INTO ln_tax_id
943               , ln_tax_amount
944               , lv_req_tax_currency
945               , lv_modvat_flag
946               , lv_adhoc_flag;
947 
948              --dbms_output.put_line('ln_tax_id: '||ln_tax_id);
949              --dbms_output.put_line('ln_tax_amount: '||ln_tax_amount);
950              --dbms_output.put_line('lv_req_tax_currency: '||lv_req_tax_currency);
951              --dbms_output.put_line('lv_modvat_flag: '||lv_modvat_flag);
952              --dbms_output.put_line('lv_adhoc_flag: '||lv_adhoc_flag);
953           EXIT WHEN Get_Req_tax_Cur%NOTFOUND;
954 
955           Get_Req_Curr_Conv_Rate ( pn_req_header_id    => pn_document_id
956                                  , pn_req_line_id      => ln_req_line_id
957                                  , pv_tax_currency     => lv_req_tax_currency
958                                  , xn_conversion_rate  => ln_currency_rate
959                                  );
960 
961              --dbms_output.put_line('ln_currency_rate: '||ln_currency_rate);
962 
963           Get_Tax_Amount_Info ( pn_tax_id             =>ln_tax_id
964                               , pn_tax_amount         =>ln_tax_amount
965                               , pn_conver_rate        =>ln_currency_rate
966                               , xn_excl_tax_amount    =>ln_excl_tax_amount
967                               , xn_excl_nr_tax_amount =>ln_excl_nr_tax_amount
968                               , pn_trx_rec_flag       =>lv_modvat_flag
969                               , xv_tax_type           =>lv_tax_type    -- added by Eric Ma for bug10426971 on 28-Dec-2010
970                               );
971 
972 --dbms_output.put_line('ln_excl_tax_amount: '||ln_excl_tax_amount);
973 --dbms_output.put_line('ln_excl_nr_tax_amount: '||ln_excl_nr_tax_amount);
974 
975            -- If condition is added for bug10426971
976            -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,Begin
977            ------------------------------------------------------------------------
978            IF lv_tax_type <>'TDS'
979            THEN
980              ln_total_tax_amount    :=  ln_total_tax_amount    + ln_excl_tax_amount;
981              ln_total_nr_tax_amount :=  ln_total_nr_tax_amount + ln_excl_nr_tax_amount;
982            END IF;
983            ------------------------------------------------------------------------
984            -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,End
985 
986            IF( lv_adhoc_flag = 'Y') THEN
987              ln_total_adhoc_tax_amount := ln_total_adhoc_tax_amount + ln_tax_amount;
988            END IF;
989 
990         END LOOP;
991 
992         CLOSE Get_Req_tax_Cur;
993 
994 
995     --dbms_output.put_line('ln_total_tax_amount: '||ln_total_tax_amount);
996     --dbms_output.put_line('ln_total_nr_tax_amount: '||ln_total_nr_tax_amount);
997     --dbms_output.put_line('ln_total_adhoc_tax_amount: '||ln_total_adhoc_tax_amount);
998 
999 
1000 
1001         --calculate new tax
1002         ln_new_tax_amount := (ln_total_tax_amount - ln_total_adhoc_tax_amount)
1003                          * ln_new_quantity/ln_old_quantity + ln_total_adhoc_tax_amount;
1004 
1005 
1006         ln_new_nr_tax_amount := (ln_total_nr_tax_amount- ln_total_adhoc_tax_amount)
1007                          * ln_new_quantity/ln_old_quantity + ln_total_adhoc_tax_amount;
1008 
1009     --dbms_output.put_line('ln_new_tax_amount: '||ln_new_tax_amount);
1010     --dbms_output.put_line('ln_new_nr_tax_amount: '||ln_new_nr_tax_amount);
1011 
1012         --calculate new tax total
1013         ln_new_total_tax_amount    := ln_new_tax_amount    + ln_new_total_tax_amount;
1014         ln_new_total_nr_tax_amount := ln_new_nr_tax_amount + ln_new_total_nr_tax_amount;
1015       END LOOP;
1016 
1017       CLOSE Get_New_Old_Quantity_Cur;
1018     END IF;--(p_document_type=JAI_PO_WF_UTIL_PUB.G_REQ_DOC_TYPE)
1019 
1020     --set the values to output parameters
1021     xn_excl_tax_amount    := NVL(ln_new_total_tax_amount,0);
1022     xn_excl_nr_tax_amount := NVL(ln_new_total_nr_tax_amount,0);
1023 
1024 
1025     --logging for debug
1026     IF (ln_proc_level >= ln_dbg_level)
1027     THEN
1028       FND_LOG.STRING( ln_proc_level
1029                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
1030                     , 'Exit procedure'
1031                     );
1032     END IF; -- (ln_proc_level>=ln_dbg_level)
1033 
1034   EXCEPTION
1035     WHEN OTHERS
1036     THEN
1037       IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1038       THEN
1039         FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1040                       , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
1041                       , Sqlcode||Sqlerrm);
1042       END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1043       RAISE;
1044   END Get_Jai_New_Tax_Amount;
1045 
1046  --==========================================================================
1047   --    FUNCTION   NAME:
1048   --
1049   --    Get_Jai_Req_Tax_Disp                     Public
1050   --
1051   --  DESCRIPTION:
1052   --    Return the formatted non-recoverable tax for display
1053   --
1054   --  PARAMETERS:
1055   --      In: pn_jai_excl_nr_tax      IN   NUMBER        non recoverable tax amount
1056   --          pv_total_tax_dsp        IN   VARCHAR2      total tax amount for display
1057   --          pv_currency_code        IN   VARCHAR2      currency code used for formating
1058   --          pv_currency_mask        IN   VARCHAR       formatted mask used by fnd_currency function
1059   --  DESIGN REFERENCES:
1060   --
1061   --
1062   --  CHANGE HISTORY:
1063   --
1064   --           8-Apr-2009   Eric Ma  created
1065   --==========================================================================
1066   FUNCTION Get_Jai_Req_Tax_Disp
1067   ( pn_jai_excl_nr_tax IN NUMBER
1068   , pv_total_tax_dsp   IN VARCHAR2
1069   , pv_currency_code   IN VARCHAR2
1070   , pv_currency_mask   IN VARCHAR2
1071   ) RETURN VARCHAR2
1072   IS
1073   lv_jai_excl_nr_tax_disp  VARCHAR2(32000);
1074   lv_amount_for_tax_disp   VARCHAR2(32000);
1075   lv_procedure_name     VARCHAR2(40):='Get_Jai_Req_Tax_Disp';
1076   ln_dbg_level          NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1077   ln_proc_level         NUMBER:=FND_LOG.LEVEL_PROCEDURE;
1078   BEGIN
1079     --logging for debug
1080     IF (ln_proc_level >= ln_dbg_level)
1081     THEN
1082       FND_LOG.STRING( ln_proc_level
1083                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
1084                     , 'Enter procedure'
1085                     );
1086     END IF; --l_proc_level>=l_dbg_level
1087 
1088     lv_jai_excl_nr_tax_disp := TO_CHAR(pn_jai_excl_nr_tax,FND_CURRENCY.GET_FORMAT_MASK(pv_currency_code, pv_currency_mask));
1089     lv_amount_for_tax_disp := lv_jai_excl_nr_tax_disp ||' '|| pv_currency_code ||' (Total Tax: ' || pv_total_tax_dsp ||')';
1090 
1091     --logging for debug
1092     IF (ln_proc_level >= ln_dbg_level)
1093     THEN
1094       FND_LOG.STRING( ln_proc_level
1095                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
1096                     , 'Exit procedure'
1097                     );
1098     END IF; --l_proc_level>=l_dbg_level
1099     RETURN lv_amount_for_tax_disp;
1100   EXCEPTION
1101   WHEN OTHERS
1102   THEN
1103     IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1104     THEN
1105     FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1106                   , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
1107                   , Sqlcode||Sqlerrm);
1108     END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1109     RAISE;
1110   END Get_Jai_Req_Tax_Disp;
1111 
1112  --==========================================================================
1113   --    FUNCTION   NAME:
1114   --
1115   --    Get_Jai_Tax_Disp                     Public
1116   --
1117   --  DESCRIPTION:
1118   --    Return the formatted tax amount for display
1119   --
1120   --  PARAMETERS:
1121   --      In: pn_tax_amount           IN   NUMBER        tax amount
1122   --          pv_currency_code        IN   VARCHAR2      currency code used for formating
1123   --          pv_currency_mask        IN   VARCHAR       formatted mask used by fnd_currency function
1124   --  DESIGN REFERENCES:
1125   --
1126   --
1127   --  CHANGE HISTORY:
1128   --
1129   --           8-Apr-2009   Eric Ma  created
1130   --==========================================================================
1131   FUNCTION Get_Jai_Tax_Disp
1132   ( pn_tax_amount IN NUMBER
1133   , pv_currency_code   IN VARCHAR2
1134   , pv_currency_mask   IN VARCHAR2
1135   ) RETURN VARCHAR2
1136   IS
1137   lv_amount_for_tax_disp   VARCHAR2(32000);
1138   lv_procedure_name     VARCHAR2(40):='Get_Jai_Tax_Disp';
1139   ln_dbg_level          NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1140   ln_proc_level         NUMBER:=FND_LOG.LEVEL_PROCEDURE;
1141   BEGIN
1142     --logging for debug
1143     IF (ln_proc_level >= ln_dbg_level)
1144     THEN
1145       FND_LOG.STRING( ln_proc_level
1146                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
1147                     , 'Enter procedure'
1148                     );
1149     END IF; --l_proc_level>=l_dbg_level
1150     lv_amount_for_tax_disp := TO_CHAR(pn_tax_amount,FND_CURRENCY.GET_FORMAT_MASK(pv_currency_code,pv_currency_mask));
1151 
1152     --logging for debug
1153     IF (ln_proc_level >= ln_dbg_level)
1154     THEN
1155       FND_LOG.STRING( ln_proc_level
1156                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
1157                     , 'Exit procedure'
1158                     );
1159     END IF; --l_proc_level>=l_dbg_level
1160     RETURN lv_amount_for_tax_disp;
1161   EXCEPTION
1162   WHEN OTHERS
1163   THEN
1164     IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1165     THEN
1166     FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1167                   , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
1168                   , Sqlcode||Sqlerrm);
1169     END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1170     RAISE;
1171   END Get_Jai_Tax_Disp;
1172 
1173 --==========================================================================
1174   --    FUNCTION   NAME:
1175   --
1176   --    Get_Jai_Open_Form_command                     Public
1177   --
1178   --  DESCRIPTION:
1179   --    Return the open form command for each document type
1180   --
1181   --  PARAMETERS:
1182   --      In: pv_document_type        IN   VARCHAR2      document type
1183   --  DESIGN REFERENCES:
1184   --
1185   --  CHANGE HISTORY:
1186   --
1187   --           13-Apr-2009   Eric Ma  created
1188   --==========================================================================
1189 
1190   Function Get_Jai_Open_Form_Command( pv_document_type VARCHAR2 )
1191   RETURN VARCHAR2
1192   IS
1193   lv_open_form VARCHAR2 (32000);
1194   lv_procedure_name     VARCHAR2(40):='Get_Jai_Open_Form_command';
1195   ln_dbg_level          NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1196   ln_proc_level         NUMBER:=FND_LOG.LEVEL_PROCEDURE;
1197   BEGIN
1198     --logging for debug
1199     IF (ln_proc_level >= ln_dbg_level)
1200     THEN
1201       FND_LOG.STRING( ln_proc_level
1202                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
1203                     , 'Enter procedure'
1204                     );
1205     END IF; --l_proc_level>=l_dbg_level
1206 
1207     IF (pv_document_type= JAI_PO_WF_UTIL_PUB.G_REQ_DOC_TYPE)
1208     THEN
1209       --Requistion
1210 
1211       --Modified by Eric on 2009-Aug-02 for bug 8744317,begin
1212       ------------------------------------------------------------------------------
1213       lv_open_form := 'JAINREQN_FUN:REQUISITION_HEADER_ID=' || '&' ||'DOCUMENT_ID'||
1214                       ' P_MODE=MODIFY' ||                      -- replace "MODIFY" with MODIFY
1215                       ' JAINREQN_CALLING_FORM=POXSTNOT';       -- replace "POXSTNOT" with POXSTNOT
1216       ------------------------------------------------------------------------------
1217       --Modified by Eric on 2009-Aug-02 for bug 8744317,end
1218 
1219 
1220     ELSIF (pv_document_type= JAI_PO_WF_UTIL_PUB.G_PO_DOC_TYPE)
1221     THEN
1222       --PO,PA
1223 
1224       --Modified by Eric on 2009-Aug-02 for bug 8744317,begin
1225       ------------------------------------------------------------------------------
1226       lv_open_form := 'JAINPO_FUN:PO_HEADER_ID=' || '&' || 'DOCUMENT_ID' ||
1227                       ' ACCESS_LEVEL_CODE=MODIFY' ||           -- replace "MODIFY" with MODIFY
1228                       ' JAINPO_CALLING_FORM=POXSTNOT';         -- replace "POXSTNOT" with POXSTNOT
1229       ------------------------------------------------------------------------------
1230       --Modified by Eric on 2009-Aug-02 for bug 8744317,end
1231 
1232     ELSIF (pv_document_type= JAI_PO_WF_UTIL_PUB.G_REL_DOC_TYPE)
1233     THEN
1234       --Release
1235 
1236       --Modified by Eric on 2009-Aug-02 for bug 8744317,begin
1237       ------------------------------------------------------------------------------
1238       lv_open_form := 'JAINPORL_FUN:PO_RELEASE_ID=' || '&' || 'DOCUMENT_ID' ||
1239                       ' ACCESS_LEVEL_CODE=MODIFY' ||           -- replace "MODIFY" with MODIFY
1240                       ' JAINPORL_CALLING_FORM=POXSTNOT';       -- replace "POXSTNOT" with POXSTNOT
1241       ------------------------------------------------------------------------------
1242       --Modified by Eric on 2009-Aug-02 for bug 8744317,end
1243     END IF;
1244 
1245     --logging for debug
1246     IF (ln_proc_level >= ln_dbg_level)
1247     THEN
1248       FND_LOG.STRING( ln_proc_level
1249                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
1250                     , 'Exit procedure'
1251                     );
1252     END IF; --l_proc_level>=l_dbg_level
1253 
1254     RETURN lv_open_form;
1255   EXCEPTION
1256   WHEN OTHERS
1257   THEN
1258     IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1259     THEN
1260       FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1261                     , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
1262                     , Sqlcode||Sqlerrm);
1263     END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1264     RAISE;
1265   END Get_Jai_Open_Form_Command;
1266 
1267 
1268 
1269  --==========================================================================
1270   --    FUNCTION   NAME:
1271   --
1272   --    Get_Poreq_Tax                     Public
1273   --
1274   --  DESCRIPTION:
1275   --    get po requisition tax
1276   --
1277   --  PARAMETERS:
1278   --      In: pv_document_type          IN   VARCHAR2      po type
1279   --          pn_document_id            IN   NUMBER        req header id,po header id,po release id
1280   --          pn_release_num            IN   NUMBER        release num
1281   --          pn_line_id                IN   NUMBER        po line id
1282   --          pn_line_location_id       IN   NUMBER        po line location id
1283   --  DESIGN REFERENCES:
1284   --
1285   --
1286   --  CHANGE HISTORY:
1287   --
1288   --           13-Apr-2009   Xiao Lv  created
1289   --==========================================================================
1290 
1291   FUNCTION Get_Poreq_Tax
1292   ( pv_document_type       IN VARCHAR2
1293   , pn_document_id         IN NUMBER
1294   , pn_release_num         IN NUMBER DEFAULT NULL
1295   , pn_line_id             IN NUMBER DEFAULT NULL
1296   , pn_line_location_id    IN NUMBER DEFAULT NULL
1297   ) RETURN NUMBER
1298   IS
1299     ln_tax_id                 NUMBER;
1300     ln_tax_amount             NUMBER;
1301     ln_excl_tax_amount        NUMBER;
1302     ln_excl_nr_tax_amount     NUMBER;
1303     ln_total_tax_amount       NUMBER :=0;
1304     ln_total_nr_tax_amount    NUMBER :=0;
1305     ln_currency_rate          NUMBER;
1306     ln_req_line_id            JAI_PO_REQ_LINE_TAXES.requisition_line_id%TYPE;
1307     lv_req_tax_currency       JAI_PO_REQ_LINE_TAXES.currency%TYPE;
1308     lv_po_tax_currency        JAI_PO_TAXES.currency%TYPE;
1309     lv_rel_tax_currency       JAI_PO_TAXES.currency%TYPE;
1310     lv_modvat_flag    VARCHAR2(1);
1311     lv_tax_type               jai_cmn_taxes_all.tax_type%TYPE;       -- added by Eric Ma for bug10426971 on 28-Dec-2010
1312 
1313     CURSOR Get_Req_tax_Cur
1314     IS
1315     SELECT
1316       tax_id
1317     , tax_amount
1318     , currency
1319     , requisition_line_id
1320     , modvat_flag
1321     FROM
1322       JAI_PO_REQ_LINE_TAXES
1323     WHERE REQUISITION_HEADER_ID = pn_document_id
1324   AND requisition_line_id = NVL(pn_line_id, requisition_line_id);
1325 
1326 
1327     CURSOR Get_Po_tax_Cur
1328     IS
1329     SELECT
1330       tax_id
1331     , tax_amount
1332     , currency
1333     , modvat_flag
1334     FROM
1335       JAI_PO_TAXES
1336     WHERE PO_HEADER_ID = pn_document_id
1337   AND po_line_id = NVL(pn_line_id, po_line_id)
1338   And line_location_id = NVL(pn_line_location_id, line_location_id);
1339 
1340 
1341     CURSOR Get_Rel_tax_Cur
1342     IS
1343     SELECT
1344       JPT.tax_id
1345     , JPT.tax_amount
1346     , JPT.currency
1347     , JPT.modvat_flag
1348     FROM
1349       PO_RELEASES_ALL POA
1350     , PO_LINE_LOCATIONS_ALL  PLLA
1351     , JAI_PO_TAXES JPT
1352     WHERE   PLLA.LINE_LOCATION_ID = JPT.LINE_LOCATION_ID
1353       AND   POA.PO_HEADER_ID  = PLLA.PO_HEADER_ID
1354       AND   POA.PO_RELEASE_ID = PLLA.PO_RELEASE_ID
1355       AND   POA.RELEASE_NUM   = pn_release_num
1356       AND   POA.PO_HEADER_ID  = pn_document_id
1357       AND   PLLA.LINE_LOCATION_ID = pn_line_location_id ;
1358 
1359     lv_procedure_name     VARCHAR2(40):='Get_Poreq_Tax';
1360     ln_dbg_level          NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1361     ln_proc_level         NUMBER:=FND_LOG.LEVEL_PROCEDURE;
1362 
1363     BEGIN
1364       --logging for debug
1365       IF (ln_proc_level >= ln_dbg_level)
1366       THEN
1367         FND_LOG.STRING( ln_proc_level
1368                       , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
1369                       , 'Enter procedure'
1370                       );
1371       END IF; --l_proc_level>=l_dbg_level
1372 
1373 
1374 
1375       IF pv_document_type= JAI_PO_WF_UTIL_PUB.G_REQ_DOC_TYPE
1376       THEN
1377         OPEN Get_Req_tax_Cur;
1378         LOOP
1379           FETCH Get_Req_tax_Cur
1380            INTO ln_tax_id,ln_tax_amount,lv_req_tax_currency,ln_req_line_id, lv_modvat_flag;
1381           EXIT  WHEN Get_Req_tax_Cur%NOTFOUND;
1382 
1383           Get_Req_Curr_Conv_Rate ( pn_req_header_id    => pn_document_id
1384                                            , pn_req_line_id      => ln_req_line_id
1385                                            , pv_tax_currency     =>lv_req_tax_currency
1386                                            , xn_conversion_rate  => ln_currency_rate
1387                                            );
1388 --dbms_output.put_line('***********************');
1389 --dbms_output.put_line('ln_curreny_rate: ' || ln_currency_rate);
1390 
1391 
1392           Get_Tax_Amount_Info ( pn_tax_id             =>ln_tax_id
1393                               , pn_tax_amount         =>ln_tax_amount
1394                               , pn_conver_rate        =>ln_currency_rate
1395                               , xn_excl_tax_amount    =>ln_excl_tax_amount
1396                               , xn_excl_nr_tax_amount =>ln_excl_nr_tax_amount
1397                               , pn_trx_rec_flag       => lv_modvat_flag
1398                               , xv_tax_type           =>lv_tax_type    -- added by Eric Ma for bug10426971 on 28-Dec-2010
1399                               );
1400 --dbms_output.put_line('ln_tax_id: ' || ln_tax_id);
1401 --dbms_output.put_line('ln_tax_amount: ' || ln_tax_amount);
1402 --dbms_output.put_line('ln_total_tax_amount: ' || ln_total_tax_amount);
1403 
1404            -- If condition is added for bug10426971
1405            -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,Begin
1406            ------------------------------------------------------------------------
1407            IF lv_tax_type <>'TDS'
1408            THEN
1409              ln_total_tax_amount    :=  ln_total_tax_amount    + ln_excl_tax_amount;
1410              ln_total_nr_tax_amount :=  ln_total_nr_tax_amount + ln_excl_nr_tax_amount;
1411            END IF;
1412            ------------------------------------------------------------------------
1413            -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,End
1414 
1415         END LOOP;
1416 
1417         CLOSE Get_Req_tax_Cur;
1418       ELSIF pv_document_type= JAI_PO_WF_UTIL_PUB.G_PO_DOC_TYPE
1419       THEN
1420 
1421 
1422         OPEN Get_Po_tax_Cur;
1423         LOOP
1424           FETCH Get_Po_tax_Cur
1425           INTO  ln_tax_id,ln_tax_amount,lv_po_tax_currency, lv_modvat_flag;
1426           EXIT WHEN Get_Po_tax_Cur%NOTFOUND;
1427 
1428           Get_Currency_Convertion_Rate ( pn_document_id     => pn_document_id
1429                                        , pv_tax_currency    => lv_po_tax_currency
1430                                        , xn_conversion_rate => ln_currency_rate
1431                                        );
1432 
1433           Get_Tax_Amount_Info ( pn_tax_id             =>ln_tax_id
1434                               , pn_tax_amount         =>ln_tax_amount
1435                               , pn_conver_rate        =>ln_currency_rate
1436                               , xn_excl_tax_amount    =>ln_excl_tax_amount
1437                               , xn_excl_nr_tax_amount =>ln_excl_nr_tax_amount
1438                               , pn_trx_rec_flag       =>lv_modvat_flag
1439                               , xv_tax_type           =>lv_tax_type    -- added by Eric Ma for bug10426971 on 28-Dec-2010
1440                               );
1441 
1442            -- If condition is added for bug10426971
1443            -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,Begin
1444            ------------------------------------------------------------------------
1445            IF lv_tax_type <>'TDS'
1446            THEN
1447              ln_total_tax_amount    :=  ln_total_tax_amount    + ln_excl_tax_amount;
1448              ln_total_nr_tax_amount :=  ln_total_nr_tax_amount + ln_excl_nr_tax_amount;
1449            END IF;
1450            ------------------------------------------------------------------------
1451            -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,End
1452 
1453          --dbms_output.put_line ( 'ln_total_tax_amount       ='||ln_total_tax_amount);
1454          --dbms_output.put_line ( 'ln_total_nr_tax_amount    ='||ln_total_nr_tax_amount);
1455         END LOOP;
1456 
1457         CLOSE Get_Po_tax_Cur;
1458       ELSIF pv_document_type= JAI_PO_WF_UTIL_PUB.G_REL_DOC_TYPE
1459       THEN
1460         OPEN Get_Rel_tax_Cur;
1461         LOOP
1462           FETCH Get_Rel_tax_Cur
1463            INTO ln_tax_id,ln_tax_amount,lv_rel_tax_currency, lv_modvat_flag;
1464           EXIT  WHEN Get_Rel_tax_Cur%NOTFOUND;
1465 
1466            Get_Currency_Convertion_Rate ( pn_document_id     => pn_document_id
1467                                         , pv_tax_currency    => lv_rel_tax_currency
1468                                         , xn_conversion_rate => ln_currency_rate
1469                                         );
1470 
1471            Get_Tax_Amount_Info ( pn_tax_id             =>ln_tax_id
1472                                , pn_tax_amount         =>ln_tax_amount
1473                                , pn_conver_rate        =>ln_currency_rate
1474                                , xn_excl_tax_amount    =>ln_excl_tax_amount
1475                                , xn_excl_nr_tax_amount =>ln_excl_nr_tax_amount
1476                                , pn_trx_rec_flag       =>lv_modvat_flag
1477                                , xv_tax_type           =>lv_tax_type    -- added by Eric Ma for bug10426971 on 28-Dec-2010
1478                                );
1479 
1480            -- If condition is added for bug10426971
1481            -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,Begin
1482            ------------------------------------------------------------------------
1483            IF lv_tax_type <>'TDS'
1484            THEN
1485              ln_total_tax_amount    :=  ln_total_tax_amount    + ln_excl_tax_amount;
1486              ln_total_nr_tax_amount :=  ln_total_nr_tax_amount + ln_excl_nr_tax_amount;
1487            END IF;
1488            ------------------------------------------------------------------------
1489            -- Modified by Eric Ma for bug10426971 on 28-Dec-2010,End
1490 
1491         END LOOP;
1492         CLOSE Get_Rel_tax_Cur;
1493       END IF;--(p_document_type=JAI_PO_WF_UTIL_PUB.G_REQ_DOC_TYPE)
1494 
1495       --logging for debug
1496       IF (ln_proc_level >= ln_dbg_level)
1497       THEN
1498         FND_LOG.STRING( ln_proc_level
1499                       , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
1500                       , 'Exit procedure'
1501                       );
1502       END IF; -- (ln_proc_level>=ln_dbg_level)
1503 
1504         RETURN  ln_total_tax_amount;
1505 
1506     EXCEPTION
1507       WHEN OTHERS
1508       THEN
1509         IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1510         THEN
1511           FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1512                         , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
1513                         , Sqlcode||Sqlerrm);
1514         END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1515         RAISE;
1516     END Get_Poreq_Tax;
1517 
1518 
1519 
1520  --==========================================================================
1521   --    PROCEDURE   NAME:
1522   --
1523   --    Populate_Session_GT                     Public
1524   --
1525   --  DESCRIPTION:
1526   --    Populate_session_gt will insert IL tax amount into session table
1527   --
1528   --  PARAMETERS:
1529   --      In: p_document_id          IN   NUMBER        req header id,po header id,po release id
1530   --          p_document_type        IN   VARCHAR2      po type
1531   --          p_document_subtype     IN   VARCHAR2
1532   --          x_session_gt_key       IN   NUMBER
1533   --  DESIGN REFERENCES:
1534   --
1535   --
1536   --  CHANGE HISTORY:
1537   --
1538   --           13-Apr-2009   Xiao Lv  created
1539   --==========================================================================
1540 
1541   PROCEDURE Populate_Session_GT(
1542      p_document_id         IN     NUMBER
1543   ,  p_document_type       IN     VARCHAR2
1544   ,  p_document_subtype    IN     VARCHAR2
1545   ,  x_session_gt_key      IN     NUMBER
1546   )
1547   IS
1548      d_progress VARCHAR2(30);
1549 
1550   BEGIN
1551 
1552   IF ( p_document_type = JAI_PO_WF_UTIL_PUB.G_PO_DOC_TYPE )
1553   THEN
1554     INSERT
1555       INTO PO_SESSION_GT(
1556            key
1557          , num1
1558          , num2
1559          , num3
1560          , num4
1561          , num5
1562          , num6
1563          , char1
1564          , char2
1565          )
1566     SELECT x_session_gt_key
1567          , GET_POREQ_TAX( JAI_PO_WF_UTIL_PUB.G_PO_DOC_TYPE
1568                         , poh.po_header_id
1569                         , NULL
1570                         , pol.po_line_id
1571                         , poll.line_location_id)
1572             * nvl(pod.rate,1)
1573 
1574          , NULL
1575          , pod.code_combination_id
1576          , poll.ship_to_location_id + 0
1577          , pol.item_id
1578          , pol.category_id
1579          , DECODE(nvl(pol.cancel_flag, 'N')
1580               , 'N'
1581               , NVL(poll.cancel_flag, 'N')
1582               , pol.cancel_flag)
1583          , DECODE(nvl(pol.closed_code, 'OPEN')
1584               , 'OPEN'
1585               , NVL(poll.closed_code, 'OPEN')
1586               , pol.closed_code)
1587       FROM po_headers        poh
1588          , po_lines          pol
1589          , po_line_locations poll
1590          , po_distributions  pod
1591      WHERE poh.po_header_id = p_document_id
1592        AND pol.po_header_id = poh.po_header_id
1593        AND poll.po_line_id = pol.po_line_id
1594        AND poll.shipment_type <> 'PREPAYMENT' -- <Complex Work R12>
1595        AND pod.line_location_id = poll.line_location_id
1596        AND ((poh.type_lookup_code <> 'PLANNED') OR
1597           ((poh.type_lookup_code = 'PLANNED') AND
1598            (poll.shipment_type = 'PLANNED')))
1599        AND pod.distribution_num=1;
1600 
1601   ELSIF (p_document_type = JAI_PO_WF_UTIL_PUB.G_REQ_DOC_TYPE)
1602    THEN
1603       d_progress := 30;
1604 
1605       INSERT
1606         INTO PO_SESSION_GT(
1607              key
1608            , num1
1609            , num2
1610            , num3
1611            , num4
1612            , num5
1613            , num6
1614            , char1
1615            , char2
1616            )
1617       SELECT x_session_gt_key
1618            , GET_POREQ_TAX( JAI_PO_WF_UTIL_PUB.G_REQ_DOC_TYPE
1619                        , porl.requisition_header_id
1620                        , NULL
1621                        , porl.requisition_line_id
1622                        , NULL )
1623            , NULL
1624            , pord.code_combination_id
1625            , porl.deliver_to_location_id
1626            , porl.item_id
1627            , porl.category_id
1628            , 'N'
1629            , 'OPEN'                      -- Bug 4610058
1630         FROM po_req_distributions pord
1631            , po_requisition_lines porl
1632        WHERE porl.requisition_header_id = p_document_id
1633          AND porl.requisition_line_id = pord.requisition_line_id
1634          AND NVL(porl.cancel_flag, 'N') = 'N'
1635          AND NVL(porl.modified_by_agent_flag, 'N') = 'N'
1636          AND pord.distribution_num=1;
1637 
1638     ELSIF (p_document_type = JAI_PO_WF_UTIL_PUB.G_REL_DOC_TYPE)
1639     THEN
1640       d_progress := 40;
1641       INSERT
1642         INTO PO_SESSION_GT(
1643              key
1644            , num1
1645            , num2
1646             , num3
1647            , num4
1648            , num5
1649            , num6
1650            , char1
1651            , char2
1652            )
1653       SELECT x_session_gt_key
1654            , GET_POREQ_TAX( JAI_PO_WF_UTIL_PUB.G_REL_DOC_TYPE
1655                          , pod.po_header_id
1656                          , poa.release_num
1657                          , pol.po_line_id
1658                          , poll.line_location_id
1659                          )
1660               * NVL(pod.rate,1)
1661            , NULL
1662            , pod.code_combination_id
1663            , poll.ship_to_location_id
1664            , pol.item_id
1665            , pol.category_id
1666            , DECODE(nvl(pol.cancel_flag, 'N'), 'N', NVL(poll.cancel_flag, 'N'), pol.cancel_flag)
1667            , DECODE(nvl(pol.closed_code, 'OPEN'), 'OPEN', NVL(poll.closed_code, 'OPEN'), pol.closed_code)
1668         FROM po_distributions pod
1669            , po_line_locations poll
1670            , po_lines pol
1671            , po_releases_all poa
1672        WHERE poa.po_release_id =  p_document_id
1673          AND poll.po_release_id = p_document_id
1674          AND poll.po_line_id = pol.po_line_id
1675          AND pod.line_location_id = poll.line_location_id
1676          AND pod.distribution_num = 1;
1677     END IF;
1678 
1679  END Populate_Session_GT;
1680 
1681 END JAI_PO_WF_UTIL_PUB;