1 PACKAGE BODY jai_encum_prc AS
2 /* $Header: jai_encum_prc.plb 120.2 2008/01/21 11:26:26 ssumaith noship $ */
3 PROCEDURE fetch_nr_tax( p_dist_type_tbl IN po_tbl_varchar30,
4 p_dist_id_tbl IN po_tbl_number,
5 p_action IN VARCHAR2,
6 p_doc_type IN VARCHAR2,
7 p_nr_tax_tbl OUT NOCOPY po_tbl_number,
8 p_return_status OUT NOCOPY VARCHAR2
9 )
10 IS
11 ln_nr_tax NUMBER;--This will have the sum of nr tax fetched so far for the shipment of current distribution record
12 ln_nr_tot_tax NUMBER;--This will have the nr tax of the current shipment
13 ln_line_location_id NUMBER;--This will have the line_location_id of the current distribution record
14 ln_next_line_location_id NUMBER;--This will have the line_location_id of the next distribution record
15 ln_po_line_id NUMBER;--This will have the PO line_id
16 ln_req_line_id NUMBER;--This will have the requistion_line_id of the current distribution
17 ln_next_req_line_id NUMBER;--This will have the requistion_line_id of the next distribution
18 lv_last_record VARCHAR2(10);--This will indicate if this is the last distribution in the current shipment
19 ln_distribution_cnt NUMBER;--This will have the count of distributions processed so far in the current shipment
20 lv_doc_type VARCHAR2(30);--This will have the document type
21 lv_encum_exists VARCHAR2(30);--This indicates if the IL encumbrance table already has the record.
22 ln_doc_curr_tax_amt NUMBER;--This has the non-recoverable tax of the distribution in document currency
23 ln_conv_rate NUMBER;--This has the currency conversion rate
24 ln_doc_header_id NUMBER;--Document header id
25 ln_doc_line_id NUMBER;--Document Line Id
26 lv_doc_currency_code VARCHAR2(30);--Document Currency code
27 ln_func_curr_tax_amt NUMBER;
28 ln_il_encum_used NUMBER;
29
30 -- Added by Jia Li for Tax inclusive Computations on 2007/12/01, Begin
31 -- TD13-Changed Support for encumbrance accounting
32 ---------------------------------------------------------------------------------------
33 ln_rec_tot_inclu_tax NUMBER; -- This will have the inclusive recoverable amount
34 ln_rec_tax_inclu NUMBER; -- This will have the round inclusive recoverable amount of current distribution record
35 ---------------------------------------------------------------------------------------
36 -- Added by Jia Li for Tax inclusive Computations on 2007/12/01, End
37
38 /*The cursor is used to see if a record already exists for the distribution in the IL table*/
39 CURSOR cur_encum_exists( cp_document_type VARCHAR2, cp_distribution_id NUMBER )
40 IS
41 SELECT 'Encumbrance Exists'
42 FROM jai_encum_tax_details
43 WHERE document_type = cp_document_type
44 AND distribution_id = cp_distribution_id;
45
46 /*Cursor used to fetch the line_location_id using distribution_id*/
47 CURSOR cur_line_location_id(cp_distribution_id NUMBER)
48 IS
49 SELECT line_location_id
50 FROM po_encumbrance_gt
51 WHERE distribution_id = cp_distribution_id;
52
53 /*Cursor to fetch the po_line_id using line_location_id*/
54 CURSOR cur_po_line_id(cp_line_location_id NUMBER)
55 IS
56 SELECT po_line_id
57 FROM po_line_locations_all
58 WHERE line_location_id = cp_line_location_id;
59
60 /*Cursor to fetch currency, conversion rate and header id of the PO*/
61 CURSOR cur_po_hdr_dtls(cp_po_line_id NUMBER)
62 IS
63 SELECT poh.currency_code, poh.rate,poh.po_header_id
64 FROM po_headers_all poh, po_lines_all pol
65 WHERE poh.po_header_id = pol.po_header_id
66 AND pol.po_line_id = cp_po_line_id;
67
68 po_hdr_dtls_rec cur_po_hdr_dtls%ROWTYPE;
69
70 CURSOR cur_req_line_id(cp_distribution_id NUMBER)
71 IS
72 SELECT requisition_line_id
73 FROM po_req_distributions_all
74 WHERE distribution_id = cp_distribution_id;
75
76 /*Cursor to fetch currency, conversion rate and header id of the requistion*/
77 CURSOR cur_req_line_dtls(cp_req_line_id NUMBER)
78 IS
79 SELECT currency_code, rate,requisition_header_id
80 FROM po_requisition_lines_all
81 WHERE requisition_line_id = cp_req_line_id;
82
83 req_line_dtls_rec cur_req_line_dtls%ROWTYPE;
84
85 CURSOR cur_il_encumbrance(cp_document_type VARCHAR2, cp_distribution_id NUMBER )
86 IS
87 SELECT *
88 FROM jai_encum_tax_details
89 WHERE document_type = cp_document_type
90 AND distribution_id = cp_distribution_id;
91
92 il_encumbrance_rec cur_il_encumbrance%ROWTYPE;
93
94 BEGIN
95 p_nr_tax_tbl := po_tbl_number(0);
96 p_return_status := 'E';
97 lv_last_record := 'FALSE';
98 ln_distribution_cnt := 1;
99
100 FOR i in 1..p_dist_type_tbl.COUNT LOOP
101
102 ln_il_encum_used := 0;
103 il_encumbrance_rec := NULL;
104
105 IF p_dist_type_tbl(i) IN ( 'STANDARD','PLANNED', 'SCHEDULED','BLANKET', 'AGREEMENT') THEN
106
107 lv_doc_type := 'PO';
108
109 IF p_action <> PO_CONSTANTS_SV.RESERVE THEN
110
111 IF ln_line_location_id IS NULL THEN
112
113 /*These values might be used to populate the IL table*/
114
115 /*Fetch the line_location_id of the distribution*/
116 OPEN cur_line_location_id(p_dist_id_tbl(i));
117 FETCH cur_line_location_id INTO ln_line_location_id;
118 CLOSE cur_line_location_id;
119
120 /*Fetch the po_line_id of the shipment*/
121 OPEN cur_po_line_id(ln_line_location_id);
122 FETCH cur_po_line_id INTO ln_po_line_id;
123 CLOSE cur_po_line_id;
124
125 END IF;
126
127 ln_il_encum_used := 1;
128
129 OPEN cur_il_encumbrance( lv_doc_type,p_dist_id_tbl(i));
130 FETCH cur_il_encumbrance INTO il_encumbrance_rec;
131 CLOSE cur_il_encumbrance;
132
133 IF il_encumbrance_rec.distribution_id IS NULL THEN
134 /*This means the document was RESERVED before the patch was applied*/
135 p_nr_tax_tbl(i) := 0;
136 ln_rec_tax_inclu := 0; -- Added by Jia Li for Tax inclusive Computations on 2007/12/01
137
138 ELSE
139 /*This means the document was RESERVED after the patch was applied*/
140 p_nr_tax_tbl(i) := il_encumbrance_rec.func_nr_tax_amount;
141
142 END IF;
143
144 ELSE
145
146 IF ln_line_location_id IS NULL THEN/*If this is the first distribution in the shipment*/
147
148 /*Fetch the line_location_id of the distribution*/
149 OPEN cur_line_location_id(p_dist_id_tbl(i));
150 FETCH cur_line_location_id INTO ln_line_location_id;
151 CLOSE cur_line_location_id;
152
153 /*Fetch the po_line_id of the shipment*/
154 OPEN cur_po_line_id(ln_line_location_id);
155 FETCH cur_po_line_id INTO ln_po_line_id;
156 CLOSE cur_po_line_id;
157
158 /*Fetch the non-recoverable tax of the shipment*/
159 /*This should be in document currency*/
160 SELECT nvl(sum(po_tax.tax_amount * DECODE(tax_code.modifiable_flag,nvl(po_tax.modvat_flag,'Y')
161 ,((100-nvl(tax_code.mod_cr_percentage,0))/100)
162 ,1 )
163 * DECODE(po_tax.currency,'INR',1/nvl(poh.rate,1),1) ),0)
164 INTO ln_nr_tot_tax
165 FROM JAI_PO_TAXES PO_TAX,
166 JAI_CMN_TAXES_ALL TAX_CODE,
167 PO_HEADERS_ALL POH,
168 PO_LINE_LOCATIONS_ALL POLL
169 WHERE po_tax.line_location_id = poll.line_location_id
170 AND poll.po_header_id = poh.po_header_id
171 AND po_tax.tax_id = tax_code.tax_id
172 AND poll.line_location_id = ln_line_location_id
173 AND NVL(tax_code.inclusive_tax_flag, 'N') = 'N'; -- Added by Jia Li for Tax inclusive Computations on 2007/12/01
174
175 -- Added by Jia Li for Tax inclusive Computations on 2007/12/01, Begin
176 -- TD13-Changed Support for encumbrance accounting
177 -- Get inclusive recoverable tax amt
178 ---------------------------------------------------------------------------------------
179 SELECT NVL(SUM(po_tax.tax_amount * DECODE( tax_code.modifiable_flag
180 , NVL(po_tax.modvat_flag,'Y'), 1
181 , (NVL(tax_code.mod_cr_percentage,0)/100))
182 * DECODE( po_tax.currency
183 , 'INR', 1/NVL(poh.rate,1)
184 , 1)
185 ), 0)
186 INTO
187 ln_rec_tot_inclu_tax
188 FROM
189 jai_po_taxes po_tax
190 , jai_cmn_taxes_all tax_code
191 , po_headers_all poh
192 , po_line_locations_all poll
193 WHERE po_tax.line_location_id = poll.line_location_id
194 AND poll.po_header_id = poh.po_header_id
195 AND po_tax.tax_id = tax_code.tax_id
196 AND poll.line_location_id = ln_line_location_id
197 AND NVL(tax_code.inclusive_tax_flag, 'N') = 'Y';
198 ---------------------------------------------------------------------------------------
199 -- Added by Jia Li for Tax inclusive Computations on 2007/12/01, End
200
201 END IF;
202
203 IF i < p_dist_type_tbl.COUNT THEN /*If it is not the last record*/
204
205 IF p_dist_type_tbl(i) <> p_dist_type_tbl(i + 1) THEN
206
207 /*If the next distribution type is different then this is the last record in the shipment*/
208
209 lv_last_record := 'TRUE';
210
211 ELSE
212
213 /*fetch the line_location_id of the next distribution*/
214 OPEN cur_line_location_id(p_dist_id_tbl( i + 1));
215 FETCH cur_line_location_id INTO ln_next_line_location_id;
216 CLOSE cur_line_location_id;
217
218 IF ln_next_line_location_id <> ln_line_location_id THEN /*If this is the last distribution in the shipment*/
219
220 lv_last_record := 'TRUE';
221
222 ELSE
223
224 ln_distribution_cnt := ln_distribution_cnt + 1; /*Increment the count of the distributions*/
225
226 END IF;
227
228 END IF;
229
230 ELSE/*if it is the last record*/
231
232 lv_last_record := 'TRUE';
233
234 END IF;
235
236 /*Fetch the apportioned non-recoverable tax for the current distribution.Round it to 2 decimals*/
237 /*This should be in document currency*/
238 SELECT nvl(round(sum(po_tax.tax_amount * DECODE(tax_code.modifiable_flag,nvl(po_tax.modvat_flag,'Y')
239 ,((100-nvl(tax_code.mod_cr_percentage,0))/100)
240 ,1 )
241 * DECODE(po_tax.currency,'INR',1/nvl(poh.rate,1),1)
242 * ( dist.quantity_ordered / poll.quantity )),2),0)
243 INTO p_nr_tax_tbl(i)
244 FROM JAI_PO_TAXES PO_TAX,
245 JAI_CMN_TAXES_ALL TAX_CODE,
246 PO_HEADERS_ALL POH,
247 PO_DISTRIBUTIONS_ALL DIST,
248 PO_LINE_LOCATIONS_ALL POLL
249 WHERE po_tax.line_location_id = dist.line_location_id
250 AND dist.line_location_id = poll.line_location_id
251 AND dist.po_header_id = poh.po_header_id
252 AND po_tax.tax_id = tax_code.tax_id
253 AND dist.po_distribution_id = p_dist_id_tbl(i)
254 AND NVL(tax_code.inclusive_tax_flag, 'N') = 'N'; -- Added by Jia Li for Tax inclusive Computations on 2007/12/01
255
256 -- Added by Jia Li for Tax inclusive Computations on 2007/12/01, Begin
257 -- TD13-Changed Support for encumbrance accounting
258 ---------------------------------------------------------------------------------------
259 SELECT NVL(ROUND(SUM(po_tax.tax_amount * DECODE( tax_code.modifiable_flag
260 , NVL(po_tax.modvat_flag,'Y'), 1
261 , (NVL(tax_code.mod_cr_percentage,0)/100))
262 * DECODE( po_tax.currency
263 , 'INR',1/NVL(poh.rate,1)
264 , 1)
265 * (dist.quantity_ordered/poll.quantity)
266 ), 2), 0)
267 INTO
268 ln_rec_tax_inclu
269 FROM
270 jai_po_taxes po_tax
271 , jai_cmn_taxes_all tax_code
272 , po_headers_all poh
273 , po_distributions_all dist
274 , po_line_locations_all poll
275 WHERE po_tax.line_location_id = dist.line_location_id
276 AND dist.line_location_id = poll.line_location_id
277 AND dist.po_header_id = poh.po_header_id
278 AND po_tax.tax_id = tax_code.tax_id
279 AND dist.po_distribution_id = p_dist_id_tbl(i)
280 AND NVL(tax_code.inclusive_tax_flag, 'N') = 'Y';
281
282 p_nr_tax_tbl(i) := p_nr_tax_tbl(i) - ln_rec_tax_inclu;
283 ---------------------------------------------------------------------------------------
284 -- Added by Jia Li for Tax inclusive Computations on 2007/12/01, End
285
286 END IF;
287
288 ELSIF p_dist_type_tbl(i) IN ('REQUISITION') THEN
289
290 lv_doc_type := 'REQ';
291
292 IF ((p_doc_type = PO_CONSTANTS_SV.PO)
293 OR (p_action <> PO_CONSTANTS_SV.RESERVE AND p_doc_type = PO_CONSTANTS_SV.REQUISITION )) THEN
294
295 ln_il_encum_used := 1;
296
297 IF ln_req_line_id IS NULL THEN /*If this is the first distribution in the requisition line*/
298
299 /*fetch the requisition_line_id of the current distribution*/
300
301 OPEN cur_req_line_id(p_dist_id_tbl(i));
302 FETCH cur_req_line_id INTO ln_req_line_id;
303 CLOSE cur_req_line_id;
304
305 END IF;
306
307 OPEN cur_il_encumbrance( lv_doc_type,p_dist_id_tbl(i));
308 FETCH cur_il_encumbrance INTO il_encumbrance_rec;
309 CLOSE cur_il_encumbrance;
310
311 IF il_encumbrance_rec.distribution_id IS NULL THEN
312 /*This means the document was RESERVED before the patch was applied*/
313 p_nr_tax_tbl(i) := 0;
314 ln_rec_tax_inclu := 0; -- Added by Jia Li for Tax inclusive Computations on 2007/12/01
315
316 ELSE
317 /*This means the document was RESERVED after the patch was applied*/
318 p_nr_tax_tbl(i) := il_encumbrance_rec.func_nr_tax_amount;
319
320 END IF;
321
322 ELSE
323 IF ln_req_line_id IS NULL THEN /*If this is the first distribution in the requisition line*/
324
325 /*fetch the requisition_line_id of the current distribution*/
326
327 OPEN cur_req_line_id(p_dist_id_tbl(i));
328 FETCH cur_req_line_id INTO ln_req_line_id;
329 CLOSE cur_req_line_id;
330
331 /*Fetch the non-recoverable tax of the requistion line*/
332 /*This should be in functional currency*/
333 SELECT nvl(sum(req_tax.tax_amount * DECODE(tax_code.modifiable_flag,nvl(req_tax.modvat_flag,'Y')
334 ,((100-nvl(tax_code.mod_cr_percentage,0))/100)
335 ,1 )
336 * DECODE(req_tax.currency,'INR',1,lines.rate) ),0)
337 INTO ln_nr_tot_tax
338 FROM JAI_PO_REQ_LINE_TAXES Req_TAX,
339 JAI_CMN_TAXES_ALL TAX_CODE,
340 PO_REQUISITION_LINES_ALL lines
341 WHERE req_tax.requisition_line_id = lines.requisition_line_id
342 AND req_tax.requisition_header_id = lines.requisition_header_id
343 AND req_tax.tax_id = tax_code.tax_id
344 AND lines.requisition_line_id = ln_req_line_id
345 AND NVL(tax_code.inclusive_tax_flag, 'N') = 'N'; -- Added by Jia Li for Tax inclusive Computations on 2007/12/01
346
347 -- Added by Jia Li for Tax inclusive Computations on 2007/12/01, Begin
348 -- TD13-Changed Support for encumbrance accounting
349 -- Get inclusive recoverable tax amt
350 ---------------------------------------------------------------------------------------
351 SELECT NVL(SUM(req_tax.tax_amount * DECODE( tax_code.modifiable_flag
352 , NVL(req_tax.modvat_flag,'Y'), 1
353 , (NVL(tax_code.mod_cr_percentage,0)/100))
354 * DECODE( req_tax.currency
355 , 'INR', 1
356 , lines.rate)
357 ), 0)
358 INTO
359 ln_rec_tot_inclu_tax
360 FROM
361 jai_po_req_line_taxes req_tax
362 , jai_cmn_taxes_all tax_code
363 , po_requisition_lines_all lines
364 WHERE req_tax.requisition_line_id = lines.requisition_line_id
365 AND req_tax.requisition_header_id = lines.requisition_header_id
366 AND req_tax.tax_id = tax_code.tax_id
367 AND lines.requisition_line_id = ln_req_line_id
368 AND NVL(tax_code.inclusive_tax_flag, 'N') = 'Y';
369 ---------------------------------------------------------------------------------------
370 -- Added by Jia Li for Tax inclusive Computations on 2007/12/01, End
371
372 END IF;
373
374 IF i < p_dist_type_tbl.COUNT THEN /*If it is not the last record*/
375
376
377 IF p_dist_type_tbl(i) <> p_dist_type_tbl(i + 1) THEN
378 /*If the next distribution type is different then this is the last record in the shipment*/
379
380 lv_last_record := 'TRUE';
381
382 ELSE
383 /*Fetch the requistion line id of the next distribution*/
384 OPEN cur_req_line_id(p_dist_id_tbl(i + 1));
385 FETCH cur_req_line_id INTO ln_next_req_line_id;
386 CLOSE cur_req_line_id;
387
388 IF ln_next_req_line_id <> ln_req_line_id THEN /*If this is the last distribution in the requisition*/
389
390 lv_last_record := 'TRUE';
391
392 ELSE
393
394 ln_distribution_cnt := ln_distribution_cnt + 1; /*Increment the distribution count*/
395
396 END IF;
397
398 END IF;
399
400 ELSE/*if it is the last record*/
401
402 lv_last_record := 'TRUE';
403
404 END IF;
405
406 /*Fetch the apportioned tax amount for the current distribution.Round it to 2 decimals*/
407 /*This should be in functional currency*/
408 SELECT nvl(round(sum(req_tax.tax_amount * DECODE(tax_code.modifiable_flag,nvl(req_tax.modvat_flag,'Y')
409 ,((100-nvl(tax_code.mod_cr_percentage,0))/100)
410 ,1 )
411 * DECODE(req_tax.currency,'INR',1,lines.rate) * (dist.req_line_quantity/lines.quantity)),2),0)
412 INTO p_nr_tax_tbl(i)
413 FROM JAI_PO_REQ_LINE_TAXES Req_TAX,
414 JAI_CMN_TAXES_ALL TAX_CODE,
415 PO_REQ_DISTRIBUTIONS_ALL dist,
416 PO_REQUISITION_LINES_ALL lines
417 WHERE req_tax.requisition_line_id = lines.requisition_line_id
418 AND req_tax.requisition_header_id = lines.requisition_header_id
419 AND lines.requisition_line_id = dist.requisition_line_id
420 AND req_tax.tax_id = tax_code.tax_id
421 AND dist.distribution_id = p_dist_id_tbl(i)
422 AND NVL(tax_code.inclusive_tax_flag, 'N') = 'N'; -- Added by Jia Li for Tax inclusive Computations on 2007/12/01
423
424 -- Added by Jia Li for Tax inclusive Computations on 2007/12/01, Begin
425 -- TD13-Changed Support for encumbrance accounting
426 ---------------------------------------------------------------------------------------
427 SELECT NVL(ROUND(SUM(req_tax.tax_amount * DECODE( tax_code.modifiable_flag
428 , NVL(req_tax.modvat_flag,'Y'), 1
429 , (NVL(tax_code.mod_cr_percentage,0)/100))
430 * DECODE( req_tax.currency
431 , 'INR', 1
432 , lines.rate)
433 * (dist.req_line_quantity/lines.quantity)
434 ), 2), 0)
435 INTO
436 ln_rec_tax_inclu
437 FROM
438 jai_po_req_line_taxes req_tax
439 , jai_cmn_taxes_all tax_code
440 , po_req_distributions_all dist
441 , po_requisition_lines_all lines
442 WHERE req_tax.requisition_line_id = lines.requisition_line_id
443 AND req_tax.requisition_header_id = lines.requisition_header_id
444 AND lines.requisition_line_id = dist.requisition_line_id
445 AND req_tax.tax_id = tax_code.tax_id
446 AND dist.distribution_id = p_dist_id_tbl(i)
447 AND nvl(tax_code.inclusive_tax_flag, 'N') = 'Y';
448
449 p_nr_tax_tbl(i) := p_nr_tax_tbl(i) - ln_rec_tax_inclu;
450 ---------------------------------------------------------------------------------------
451 -- Added by Jia Li for Tax inclusive Computations on 2007/12/01, End
452
453 END IF;
454
455 END IF;
456
457 IF ln_il_encum_used = 0 THEN
458
459 IF lv_last_record = 'FALSE' THEN /*if this is not the last distribution */
460
461 ln_nr_tax := nvl(ln_nr_tax,0) + p_nr_tax_tbl(i); /*Add the current distribution's tax to ln_nr_tax*/
462
463 ELSIF ln_distribution_cnt > 1 THEN /*If this is the last distribution and there are more than one distributions*/
464
465 /*Calculate the tax amount by subtracting ln_nr_tax from ln_nr_tot_tax. This will eliminate any rounding differences*/
466 -- Modified by Jia Li for Tax inclusive Computations on 2007/12/01, Begin
467 --------------------------------------------------------------------------
468 --p_nr_tax_tbl(i) := nvl(ln_nr_tot_tax,0) - nvl(ln_nr_tax,0);
469 p_nr_tax_tbl(i) := nvl(ln_nr_tot_tax, 0) - NVL(ln_rec_tot_inclu_tax, 0) - nvl(ln_nr_tax, 0);
470 ---------------------------------------------------------------------------
471 -- Modified by Jia Li for Tax inclusive Computations on 2007/12/01, End
472
473 END IF;
474
475 /*If the distribution_cnt is 1 and lv_last_record is TRUE then p_nr_tax_tbl(i) would be the one fetched from query only.*/
476 END IF;
477
478 /*If the encumbrance action is any of the following then populate IL table*/
479 IF p_action IN ( PO_CONSTANTS_SV.RESERVE, PO_CONSTANTS_SV.CANCEL, 'FINAL CLOSE' ) THEN
480
481 IF lv_doc_type = 'PO' THEN
482 /*fetch the document details*/
483 OPEN cur_po_hdr_dtls(ln_po_line_id);
484 FETCH cur_po_hdr_dtls INTO po_hdr_dtls_rec;
485 CLOSE cur_po_hdr_dtls;
486
487 ln_doc_header_id := po_hdr_dtls_rec.po_header_id;
488 ln_doc_line_id := ln_po_line_id;
489 lv_doc_currency_code := po_hdr_dtls_rec.currency_code;
490
491 IF lv_doc_currency_code = 'INR' THEN
492
493 ln_conv_rate := 1;
494
495 ELSE
496
497 ln_conv_rate := po_hdr_dtls_rec.rate;
498
499 END IF;
500
501 ln_doc_curr_tax_amt := p_nr_tax_tbl(i) ;
502 ln_func_curr_tax_amt := p_nr_tax_tbl(i) * ln_conv_rate;
503
504 ELSIF lv_doc_type = 'REQ' THEN
505 /*fetch the line details*/
506 OPEN cur_req_line_dtls(ln_req_line_id);
507 FETCH cur_req_line_dtls INTO req_line_dtls_rec;
508 CLOSE cur_req_line_dtls;
509
510 ln_doc_header_id := req_line_dtls_rec.requisition_header_id;
511 ln_doc_line_id := ln_req_line_id;
512 lv_doc_currency_code := nvl(req_line_dtls_rec.currency_code,'INR');
513
514 IF lv_doc_currency_code = 'INR' THEN
515
516 ln_conv_rate := 1;
517
518 ELSE
519
520 ln_conv_rate := req_line_dtls_rec.rate;
521
522 END IF;
523
524 /*Covert the tax amount into document currency using conversion rate*/
525 ln_doc_curr_tax_amt := p_nr_tax_tbl(i) / ln_conv_rate;
526 ln_func_curr_tax_amt := p_nr_tax_tbl(i);
527
528 END IF;
529
530
531 lv_encum_exists := NULL;
532 /*Check if the IL encumbrance table has the record*/
533 OPEN cur_encum_exists(lv_doc_type,p_dist_id_tbl(i));
534 FETCH cur_encum_exists INTO lv_encum_exists;
535 CLOSE cur_encum_exists;
536
537 IF lv_encum_exists = 'Encumbrance Exists' THEN
538
539 /*If record exists then update it with the tax amount and status*/
540
541 UPDATE jai_encum_tax_details
542 SET nr_tax_amount = ln_doc_curr_tax_amt,
543 func_nr_tax_amount = p_nr_tax_tbl(i),
544 status = DECODE(p_action,PO_CONSTANTS_SV.RESERVE,'RESERVED',
545 PO_CONSTANTS_SV.CANCEL ,'CANCELLED',
546 'FINAL CLOSE' ,'CLOSED'),
547 last_update_date = sysdate,
548 last_updated_by = fnd_global.user_id,
549 last_update_login = fnd_global.login_id
550 WHERE document_type = lv_doc_type
551 AND distribution_id = p_dist_id_tbl(i);
552
553 ELSE
554
555 /*If the record does not exist create a new record*/
556
557 INSERT INTO
558 jai_encum_tax_details( DOCUMENT_TYPE ,
559 DOC_HEADER_ID ,
560 DOC_LINE_ID ,
561 LINE_LOCATION_ID ,
562 DISTRIBUTION_ID ,
563 CURRENCY_CODE ,
564 NR_TAX_AMOUNT ,
565 FUNC_NR_TAX_AMOUNT ,
566 STATUS ,
567 CREATION_DATE ,
568 CREATED_BY ,
569 LAST_UPDATE_DATE ,
570 LAST_UPDATED_BY ,
571 LAST_UPDATE_LOGIN ,
572 OBJECT_VERSION_NUMBER
573 )
574 VALUES( lv_doc_type ,
575 ln_doc_header_id ,
576 ln_doc_line_id ,
577 ln_line_location_id ,
578 p_dist_id_tbl(i) ,
579 lv_doc_currency_code ,
580 ln_doc_curr_tax_amt ,
581 ln_func_curr_tax_amt ,
582 DECODE(p_action,PO_CONSTANTS_SV.RESERVE,'RESERVED' ,
583 PO_CONSTANTS_SV.CANCEL ,'CANCELLED',
584 'FINAL CLOSE' ,'CLOSED') ,
585 sysdate ,
586 fnd_global.user_id ,
587 sysdate ,
588 fnd_global.user_id ,
589 fnd_global.login_id ,
590 NULL
591 );
592
593 END IF;
594
595
596 END IF;
597
598 IF p_nr_tax_tbl.COUNT < p_dist_id_tbl.COUNT THEN
599
600 p_nr_tax_tbl.extend;
601
602 END IF;
603
604 IF lv_last_record = 'TRUE' THEN
605
606 /*Assign the initial values to the variables used*/
607 ln_line_location_id := NULL;
608 ln_next_line_location_id := NULL;
609 ln_nr_tax := 0;
610 ln_nr_tot_tax := 0;
611 ln_rec_tot_inclu_tax := 0; -- Added by Jia Li for Tax inclusive Computations on 2007/12/01, TD13
612 lv_last_record := 'FALSE';
613 ln_distribution_cnt := 1;
614
615 END IF;
616
617 END LOOP;
618
619 p_return_status := 'S';
620
621 EXCEPTION
622
623 WHEN OTHERS THEN
624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
625
626 END fetch_nr_tax;
627
628 PROCEDURE fetch_encum_rev_amt( p_acct_txn_id IN NUMBER,
629 p_source_doc_type IN VARCHAR2,
630 p_source_doc_id IN NUMBER,
631 p_acct_source IN VARCHAR2,
632 p_nr_tax_amount OUT NOCOPY NUMBER,
633 p_rec_tax_amount OUT NOCOPY NUMBER,
634 p_err_num OUT NOCOPY NUMBER,
635 p_err_code OUT NOCOPY VARCHAR2,
636 p_err_msg OUT NOCOPY VARCHAR2
637 )
638 IS
639
640 /*Cursor fetches the non-recoverable tax amount of PO stored in IL encumbrance table*/
641 CURSOR cur_po_nr_tax_amt
642 IS
643 SELECT nr_tax_amount
644 FROM jai_encum_tax_details
645 WHERE document_type = p_source_doc_type
646 AND distribution_id = p_source_doc_id;
647
648 /*Cursor fetches the non-recoverable tax amount of REQUISITION stored in IL encumbrance table*/
649 CURSOR cur_req_nr_tax_amt
650 IS
651 SELECT nvl(sum(nr_tax_amount),0)
652 FROM jai_encum_tax_details
653 WHERE document_type = p_source_doc_type
654 AND doc_line_id = p_source_doc_id;
655
656
657 BEGIN
658
659 p_err_num := '';
660 p_err_code := '';
661 p_err_msg := '';
662 p_rec_tax_amount := 0; /*This will be zero as it is not used anywhere in encumbrance*/
663
664 IF p_source_doc_type = 'PO' THEN
665
666 /*Fetch the non-recoverable tax amount of the PO distribution*/
667 OPEN cur_po_nr_tax_amt;
668 FETCH cur_po_nr_tax_amt INTO p_nr_tax_amount;
669 CLOSE cur_po_nr_tax_amt;
670
671 ELSIF p_source_doc_type = 'REQ' THEN
672
673 /*Fetch the non-recoverable tax amount of the Requisition line*/
674 OPEN cur_req_nr_tax_amt;
675 FETCH cur_req_nr_tax_amt INTO p_nr_tax_amount;
676 CLOSE cur_req_nr_tax_amt;
677
678 END IF;
679
680 INSERT INTO jai_encum_tax_rvrsl_dtls( ACCT_TXN_ID ,
681 SOURCE_DOC_ID ,
682 ACCT_SOURCE ,
683 SOURCE_DOC_TYPE ,
684 DOC_NR_TAX ,
685 DOC_REC_TAX ,
686 LAST_UPDATE_DATE ,
687 LAST_UPDATED_BY ,
688 CREATION_DATE ,
689 CREATED_BY ,
690 LAST_UPDATE_LOGIN ,
691 OBJECT_VERSION_NUMBER )
692 VALUES ( p_acct_txn_id ,
693 p_source_doc_id ,
694 p_acct_source ,
695 p_source_doc_type ,
696 p_nr_tax_amount ,
697 0 ,
698 SYSDATE ,
699 fnd_global.user_id ,
700 SYSDATE ,
701 fnd_global.user_id ,
702 fnd_global.login_id ,
703 NULL
704 );
705
706 EXCEPTION
707
708 WHEN OTHERS THEN
709
710 p_err_num := SQLCODE;
711 p_err_msg := 'jai_encum_prc.fetch_encum_rev_amt:' || substrb(SQLERRM,1,150);
712
713 END fetch_encum_rev_amt;
714
715 END jai_encum_prc;
[Home] [Help]