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