[Home] [Help]
PACKAGE BODY: APPS.PN_R12_UTIL_PKG
Source
4 --------------------------------------------------------------------------
1 PACKAGE BODY pn_r12_util_pkg AS
2 /* $Header: PNUTL12B.pls 120.11 2008/02/11 13:39:42 rthumma ship $ */
3
5 -- FUNCTION : get_tcc
6 -- RETURNS : gets tax classification code given a tax id
7 -- NOTES : for R12 release uptake
8 -- HISTORY
9 -- 09-MAY-05 ftanudja o created.
10 --------------------------------------------------------------------------
11 FUNCTION get_tcc (
12 p_tax_code_id pn_payment_terms.tax_code_id%TYPE,
13 p_lease_class_code pn_leases.lease_class_code%TYPE,
14 p_as_of_date pn_payment_terms.start_date%TYPE)
15 RETURN VARCHAR2
16 IS
17 CURSOR tcc_info (p_source VARCHAR2) IS
18 SELECT tax_classification_code
19 FROM zx_id_tcc_mapping
20 WHERE p_as_of_date BETWEEN nvl(effective_from, p_as_of_date)
21 AND nvl(effective_to, p_as_of_date)
22 AND tax_rate_code_id = p_tax_code_id
23 AND source = p_source;
24
25 l_source VARCHAR2(30);
26 l_tcc pn_payment_terms.tax_classification_code%TYPE;
27 l_desc VARCHAR2(100);
28
29 BEGIN
30
31 l_desc := 'pn_r12_util_pkg.get_tcc';
32
33 pnp_debug_pkg.log(l_desc ||' (+)');
34
35 IF p_lease_class_code IN ('DIRECT','PAY','PAYMENT') THEN
36 l_source := 'AP';
37 ELSIF p_lease_class_code IN ('THIRD_PARTY','SUBLEASE','REC','BILLING') THEN
38 l_source := 'AR';
39 END IF;
40
41 FOR fetch_info IN tcc_info(l_source) LOOP
42 l_tcc := fetch_info.tax_classification_code;
43 END LOOP;
44
45 pnp_debug_pkg.log(l_desc ||' (-)');
46
47 RETURN l_tcc;
48
49 END get_tcc;
50
51 --------------------------------------------------------------------------
52 -- FUNCTION : get_tcc_name
53 -- RETURNS : gets tax classification code name given the code
54 -- NOTES : for R12 release uptake
55 -- HISTORY
56 -- 09-MAY-05 ftanudja o created.
57 -- 03-MAY-06 sdmahesh o Bug 5192203
58 -- Rewrote the procedure to fetch TCC Billing/Payment
59 -- from E Tax Views
60 --------------------------------------------------------------------------
61 FUNCTION get_tcc_name (
62 p_tcc pn_payment_terms.tax_classification_code%TYPE,
63 p_lease_class_code pn_leases.lease_class_code%TYPE,
64 p_org_id pn_term_templates.org_id%TYPE)
65
66 RETURN VARCHAR2
67 IS
68
69 CURSOR csr_bill IS
70 SELECT meaning
71 FROM zx_output_classifications_v
72 WHERE enabled_flag = 'Y'
73 AND trunc(SYSDATE) BETWEEN TRUNC(nvl(start_date_active,SYSDATE)) AND TRUNC(nvl(end_date_active,SYSDATE))
74 AND org_id = p_org_id
75 AND lookup_code = p_tcc;
76
77 CURSOR csr_pay IS
78 SELECT meaning
79 FROM ZX_INPUT_CLASSIFICATIONS_V
80 WHERE lookup_type = 'ZX_INPUT_CLASSIFICATIONS'
81 AND enabled_flag = 'Y'
82 AND trunc(SYSDATE) BETWEEN TRUNC(nvl(start_date_active, SYSDATE)) AND TRUNC(nvl(end_date_active, SYSDATE))
83 AND org_id = p_org_id
84 AND lookup_code = p_tcc;
85
86 l_tcc_name VARCHAR2(80);
87 l_desc VARCHAR2(100);
88
89 BEGIN
90
91 l_desc := 'pn_r12_util_pkg.get_tcc_name';
92
93 pnp_debug_pkg.log(l_desc ||' (+)');
94
95 IF p_lease_class_code IN ('DIRECT','PAY') THEN
96
97 FOR rec IN csr_pay LOOP
98 l_tcc_name := rec.meaning;
99 END LOOP;
100
101 ELSIF p_lease_class_code IN ('THIRD_PARTY','SUBLEASE','REC') THEN
102
103 FOR rec IN csr_bill LOOP
104 l_tcc_name := rec.meaning;
105 END LOOP;
106
107 END IF;
108
109 pnp_debug_pkg.log(l_desc ||' (-)');
110
111 RETURN l_tcc_name;
112
113 END get_tcc_name;
114
115 --------------------------------------------------------------------------
116 -- FUNCTION : validate_term_template_tax
117 -- RETURNS : FALSE if tax classification code does not exist for a given
118 -- tax code or tax group
119 -- NOTES : for R12 release uptake
120 -- HISTORY
121 -- 09-MAY-05 ftanudja o created.
122 --------------------------------------------------------------------------
123 FUNCTION validate_term_template_tax(
124 p_term_temp_id IN NUMBER,
125 p_lease_cls_code IN VARCHAR2)
126 RETURN BOOLEAN IS
127
128 l_answer BOOLEAN := TRUE;
129 l_tcc pn_payment_terms.tax_classification_code%TYPE;
130 l_desc VARCHAR2(100);
131
132 CURSOR term_temp_cur(p_term_temp_id IN NUMBER) IS
133 SELECT tax_code_id, tax_group_id, tax_classification_code
134 FROM pn_term_templates
135 WHERE term_template_id = p_term_temp_id;
136
137 BEGIN
138
139 l_desc := 'pn_r12_util_pkg.validate_term_template_tax';
140
144
141 pnp_debug_pkg.log(l_desc ||' (+)');
142
143 FOR tp_rec IN term_temp_cur(p_term_temp_id) LOOP
145 IF (tp_rec.tax_code_id IS NOT NULL OR
146 tp_rec.tax_group_id IS NOT NULL)
147 AND
148 tp_rec.tax_classification_code IS NULL
149 THEN
150
151 l_tcc := pn_r12_util_pkg.get_tcc(
152 p_tax_code_id => nvl(tp_rec.tax_code_id, tp_rec.tax_group_id),
153 p_lease_class_code => p_lease_cls_code,
154 p_as_of_date => SYSDATE);
155
156 IF l_tcc IS NULL THEN
157 l_answer := FALSE;
158 END IF;
159 END IF;
160 END LOOP;
161
162 pnp_debug_pkg.log(l_desc ||' (-)');
163
164 RETURN l_answer;
165
166 END validate_term_template_tax;
167
168 ------------------------------------------------------------------------
169 -- FUNCTION : is_le_compatible
170 -- RETURNS : FALSE if Legal entity is different from LE associated with
171 -- existing account distributions.
172 -- LOGIC :
173 -- LE API CALL:
174 -- o Get LE given p_ccid1 => X
175 -- o Get LE given p_ccid2 => Y
176 --
177 -- IF X <> Y THEN RETURN FALSE; ELSE RETURN TRUE
178 -- HISTORY :
179 -- 26-MAY-05 ftanudja o created.
180 -- 28-JUL-05 ftanudja o split from main procedure
181 -- 25-AUG-05 ftanudja o Removed p_location_id from is_le_compatible.
182 -- 17-SEP-05 sdmahesh o Added parameter p_le_id_old to get the existing
183 -- legal_entity_id
184 ------------------------------------------------------------------------
185
186 FUNCTION is_le_compatible(
187 p_ccid1 IN pn_distributions.account_id%TYPE,
188 p_ccid2 IN pn_distributions.account_id%TYPE,
189 p_le_id_old IN NUMBER,
190 p_vendor_site_id IN pn_payment_terms.vendor_site_id%TYPE,
191 p_org_id IN pn_payment_terms.org_id%TYPE)
192
193 RETURN BOOLEAN IS
194
195 l_le_rec_new xle_businessinfo_grp.ptop_le_rec;
196 l_le_rec_old xle_businessinfo_grp.ptop_le_rec;
197 l_msg_data VARCHAR2(250);
198 l_ret_status VARCHAR2(1);
199 l_answer BOOLEAN := TRUE;
200 l_desc VARCHAR2(100);
201 l_le_id_old NUMBER(15) NULL;
202
203 BEGIN
204
205 l_desc := 'pn_r12_util_pkg.is_le_compatible - internal';
206
207 pnp_debug_pkg.log(l_desc ||' (+)');
208
209 /*
210 LE API CALL:
211 o Get LE given p_ccid1 => X
212 o Get LE given p_ccid2 => Y
213
214 IF X <> Y THEN RETURN FALSE; ELSE RETURN TRUE
215 */
216 l_le_id_old := p_le_id_old;
217 IF l_le_id_old IS NULL THEN
218 xle_businessinfo_grp.get_purchasetopay_info(
219 x_return_status => l_ret_status
220 ,x_msg_data => l_msg_data
221 ,p_code_combination_id => p_ccid1
222 ,p_registration_code => null
223 ,p_registration_number => null
224 ,p_location_id => p_vendor_site_id
225 ,p_operating_unit_id => p_org_id
226 ,x_ptop_le_info => l_le_rec_old
227 );
228 l_le_id_old := l_le_rec_old.legal_entity_id;
229 END IF;
230
231 xle_businessinfo_grp.get_purchasetopay_info(
232 x_return_status => l_ret_status
233 ,x_msg_data => l_msg_data
234 ,p_code_combination_id => p_ccid2
235 ,p_registration_code => null
236 ,p_registration_number => null
237 ,p_location_id => p_vendor_site_id
238 ,p_operating_unit_id => p_org_id
239 ,x_ptop_le_info => l_le_rec_new
240 );
241
242 IF NOT(l_le_rec_new.legal_entity_id = l_le_id_old OR
243 (l_le_rec_new.legal_entity_id IS NULL AND
244 l_le_id_old IS NULL))
245 THEN
246 l_answer := FALSE;
247 END IF;
248
249 pnp_debug_pkg.log(l_desc ||' (-)');
250
251 RETURN l_answer;
252
253 END is_le_compatible;
254
255 ------------------------------------------------------------------------
256 -- FUNCTION : is_le_compatible
257 -- RETURNS : FALSE if Legal entity is different from LE associated with
258 -- existing account distributions.
259 -- TRUE if no account distribution exists or if LE is the
260 -- same for all existing account distributions.
261 --
262 -- NOTES : for R12 release uptake
263 -- : This should be called for PAYABLES side only !!
264 -- HISTORY
265 -- 26-MAY-05 ftanudja o created.
266 -- 28-JUL-05 ftanudja o added p_distr_id, p_mode, p_term_templ_id.
267 -- 25-AUG-05 ftanudja o Removed p_location_id from is_le_compatible.
268 -- 16-OCT-05 sdmahesh o Modified cursors to fetch legal_entity_id
269 --------------------------------------------------------------------------
270 FUNCTION is_le_compatible(
271 p_ccid IN pn_distributions.account_id%TYPE,
272 p_payment_term_id IN pn_payment_terms.payment_term_id%TYPE,
273 p_term_template_id IN pn_payment_terms.term_template_id%TYPE,
274 p_vendor_site_id IN pn_payment_terms.vendor_site_id%TYPE,
275 p_org_id IN pn_payment_terms.org_id%TYPE,
276 p_distribution_id IN pn_distributions.distribution_id%TYPE,
277 p_mode IN VARCHAR2)
278 RETURN BOOLEAN IS
279
280 l_answer BOOLEAN := TRUE;
281 l_desc VARCHAR2(100);
282
283 -- cursor for checking distr in INSERT mode using term ID
284 CURSOR chk_other_dist_ins IS
288 pn_payment_terms_all ppt
285 SELECT ppt.legal_entity_id le_id,
286 dist.account_id cc_id
287 FROM pn_distributions dist,
289 WHERE dist.payment_term_id = p_payment_term_id
290 AND ppt.payment_term_id = p_payment_term_id
291 AND rownum < 2;
292
293 -- cursor for checking distr in INSERT mode using template ID
294 CURSOR chk_other_dist_templ_ins IS
295 SELECT dist.account_id cc_id,
296 NULL AS le_id
297 FROM pn_distributions dist
298 WHERE dist.term_template_id = p_term_template_id
299 AND rownum < 2;
300
301 -- cursor for checking distr in UPDATE mode using term ID
302 CURSOR chk_other_dist_upd IS
303 SELECT ppt.legal_entity_id le_id,
304 dist.account_id cc_id
305 FROM pn_distributions dist,
306 pn_payment_terms_all ppt
307 WHERE dist.payment_term_id = p_payment_term_id
308 AND ppt.payment_term_id = p_payment_term_id
309 AND distribution_id <> p_distribution_id
310 AND rownum < 2;
311
312 -- cursor for checking distr in UPDATE mode using template ID
313 CURSOR chk_other_dist_templ_upd IS
314 SELECT dist.account_id cc_id,
315 NULL AS le_id
316 FROM pn_distributions dist
317 WHERE dist.term_template_id = p_term_template_id
318 AND distribution_id <> p_distribution_id
319 AND rownum < 2;
320
321
322 BEGIN
323
324 l_desc := 'pn_r12_util_pkg.is_le_compatible';
325
326 pnp_debug_pkg.log(l_desc ||' (+)');
327
328 IF p_mode = 'INSERT' THEN
329 IF p_payment_term_id IS NOT NULL THEN
330 FOR validation_rec IN chk_other_dist_ins LOOP
331 l_answer := is_le_compatible(
332 p_le_id_old => validation_rec.le_id
333 ,p_ccid1 => validation_rec.cc_id
334 ,p_ccid2 => p_ccid
335 ,p_vendor_site_id => p_vendor_site_id
336 ,p_org_id => p_org_id);
337
338 END LOOP;
339 ELSIF p_term_template_id IS NOT NULL THEN
340 FOR validation_rec IN chk_other_dist_templ_ins LOOP
341 l_answer := is_le_compatible(
342 p_le_id_old => validation_rec.le_id
343 ,p_ccid1 => validation_rec.cc_id
344 ,p_ccid2 => p_ccid
345 ,p_vendor_site_id => p_vendor_site_id
346 ,p_org_id => p_org_id);
347
348 END LOOP;
349 END IF;
350 ELSIF p_mode = 'UPDATE' THEN
351 IF p_payment_term_id IS NOT NULL THEN
352 FOR validation_rec IN chk_other_dist_upd LOOP
353 l_answer := is_le_compatible(
354 p_le_id_old => validation_rec.le_id
355 ,p_ccid1 => validation_rec.cc_id
356 ,p_ccid2 => p_ccid
357 ,p_vendor_site_id => p_vendor_site_id
358 ,p_org_id => p_org_id);
359
360 END LOOP;
361 ELSIF p_term_template_id IS NOT NULL THEN
362 FOR validation_rec IN chk_other_dist_templ_upd LOOP
363 l_answer := is_le_compatible(
364 p_le_id_old => validation_rec.le_id
365 ,p_ccid1 => validation_rec.cc_id
366 ,p_ccid2 => p_ccid
367 ,p_vendor_site_id => p_vendor_site_id
368 ,p_org_id => p_org_id);
369
370 END LOOP;
371 END IF;
372
373 END IF;
374
375 pnp_debug_pkg.log(l_desc ||' (-)');
376
377 RETURN l_answer;
378
379 END is_le_compatible;
380
381
382 --------------------------------------------------------------------------
383 -- FUNCTION : is_r12
384 -- RETURNS : FALSE if release 11i, TRUE if release 12
385 -- HISTORY
386 -- 26-MAY-05 ftanudja o created.
387 --------------------------------------------------------------------------
388 FUNCTION is_r12
389 RETURN BOOLEAN IS
390 BEGIN
391 RETURN TRUE;
392 END is_r12;
393
394 --------------------------------------------------------------------------
395 -- FUNCTION : Wrapper function for LE API for AP
396 -- RETURNS : LE given the specified parameters
397 -- NOTES : for R12 release uptake
398 -- HISTORY
399 -- 11-JUL-05 ftanudja o created.
400 --------------------------------------------------------------------------
401 FUNCTION get_le_for_ap(
402 p_code_combination_id pn_distributions.account_id%TYPE
403 ,p_location_id pn_payment_terms.vendor_site_id%TYPE
404 ,p_org_id pn_payment_terms.org_id%TYPE)
405 RETURN NUMBER IS
406
407 l_le_rec_pay xle_businessinfo_grp.ptop_le_rec;
408 l_msg_data VARCHAR2(250);
409 l_ret_status VARCHAR2(1);
410 l_desc VARCHAR2(100);
411
412 BEGIN
413
414
415 l_desc := 'pn_r12_util_pkg.get_let_for_ap';
416
417 pnp_debug_pkg.log(l_desc ||' (+)');
418
419 xle_businessinfo_grp.get_purchasetopay_info(
420 x_return_status => l_ret_status
421 ,x_msg_data => l_msg_data
422 ,p_registration_code => null
423 ,p_registration_number => null
424 ,p_location_id => p_location_id
425 ,p_code_combination_id => p_code_combination_id
429
426 ,p_operating_unit_id => p_org_id
427 ,x_ptop_le_info => l_le_rec_pay
428 );
430 pnp_debug_pkg.log(l_desc ||' (-)');
431
432 RETURN l_le_rec_pay.legal_entity_id;
433
434 END get_le_for_ap;
435
436
437 --------------------------------------------------------------------------
438 -- FUNCTION : Wrapper function for LE API for AR
439 -- RETURNS : LE given the specified parameters
440 -- NOTES : for R12 release uptake
441 -- HISTORY
442 -- 11-JUL-05 ftanudja o created.
443 -- 05-AUG-05 ftanudja o added call to mo_global. #4526616,#4497295.
444 --------------------------------------------------------------------------
445 FUNCTION get_le_for_ar(
446 p_customer_id pn_payment_terms.customer_id%TYPE
447 ,p_transaction_type_id pn_payment_terms.cust_trx_type_id%TYPE
448 ,p_org_id pn_payment_terms.org_id%TYPE)
449 RETURN NUMBER IS
450
451 l_le_rec_rec xle_businessinfo_grp.otoc_le_rec;
452 l_msg_data VARCHAR2(250);
453 l_ret_status VARCHAR2(1);
454 l_desc VARCHAR2(100);
455
456 BEGIN
457
458 l_desc := 'pn_r12_util_pkg.get_let_for_ar';
459
460 pnp_debug_pkg.log(l_desc ||' (+)');
461
462 mo_global.set_org_access(p_org_id, null, 'AR');
463
464 xle_businessinfo_grp.get_ordertocash_info(
465 x_return_status => l_ret_status
466 ,x_msg_data => l_msg_data
467 ,p_customer_type => 'BILL_TO'
468 ,p_customer_id => p_customer_id
469 ,p_transaction_type_id => p_transaction_type_id
470 ,p_batch_source_id => 24
471 ,p_operating_unit_id => p_org_id
472 ,x_otoc_le_info => l_le_rec_rec
473 );
474
475 pnp_debug_pkg.log(l_desc ||' (-)');
476
477 RETURN l_le_rec_rec.legal_entity_id;
478
479 END get_le_for_ar;
480
481 --------------------------------------------------------------------------
482 -- FUNCTION : get_tax_flag
483 -- RETURNS : auto_tax_calc_flag
484 -- HISTORY
485 -- 05-SEP-05 SatyaDeep o created.Retreives auto_tax_calc_flag
486 -- in 11i
487 -- 05-APR-06 sdmahesh o Retreived INCLUSIVE_TAX_FLAG
488 -- in R12
489 -- 25-Sep-07 bnoorbha o Bug 6413109: Added cursor to retrieve
490 -- inclusive_tax_flag from supplier site also.
491 --------------------------------------------------------------------------
492 FUNCTION get_tax_flag (p_vendor_id IN NUMBER,
493 p_vendor_site_id IN NUMBER)
494 RETURN VARCHAR2 IS
495
496 CURSOR get_flag_value IS
497 SELECT DECODE( NVL(inclusive_tax_flag,'Y'),'Y','S','N') tax_flag
498 FROM zx_party_tax_profile zpt,
499 po_vendors pov
500 WHERE pov.vendor_id = p_vendor_id
501 AND zpt.party_id = pov.party_id
502 AND party_type_code = 'THIRD_PARTY';
503
504 -- Added for Bug #6413109
505 CURSOR get_site_flag_value IS
506 SELECT DECODE( NVL(inclusive_tax_flag,'Y'),'Y','S','N') tax_flag
507 FROM zx_party_tax_profile zpt,
508 po_vendor_sites_all pvsa
509 WHERE pvsa.VENDOR_SITE_ID = p_vendor_site_id
510 AND zpt.party_id = pvsa.party_site_id
511 AND party_type_code = 'THIRD_PARTY_SITE';
512
513 l_answer VARCHAR2(1);
514
515 BEGIN
516
517 pnp_debug_pkg.log('pn_r12_util_pkg.get_tax_flag (+)');
518
519 -- Added IF condition for Bug 6413109
520 IF p_vendor_site_id IS NOT NULL THEN
521 FOR info_rec IN get_site_flag_value LOOP
522
523 l_answer := info_rec.tax_flag;
524
525 END LOOP;
526 ELSE
527
528 FOR info_rec IN get_flag_value LOOP
529
530 l_answer := info_rec.tax_flag;
531
532 END LOOP;
533 END IF;
534
535 pnp_debug_pkg.log('pn_r12_util_pkg.get_tax_flag (-)');
536
537 RETURN l_answer;
538
539 END get_tax_flag;
540
541 --------------------------------------------------------------------------
542 -- FUNCTION : get_ap_tax_code_name
543 -- RETURNS : Tax Code Name
544 -- HISTORY
545 -- 14-MAR-06 Hareesha o Bug #4756588 Created.Stub for R12.
546 -- Retrieves Tax Code Name in 11i.
547 -- DO NOT USE IN R12
548 --------------------------------------------------------------------------
549 FUNCTION get_ap_tax_code_name(p_tax_id IN NUMBER)
550 RETURN VARCHAR2 IS
551 BEGIN
552 RETURN NULL;
553 END get_ap_tax_code_name;
554
555
556 --------------------------------------------------------------------------
557 -- FUNCTION : get_ar_tax_code_name
558 -- RETURNS : Tax Code Name
559 -- HISTORY
560 -- 14-MAR-06 Hareesha o Bug #4756588 Created.Stub for R12.
561 -- Retrieves Tax Code Name in 11i.
562 -- DO NOT USE IN R12
563 --------------------------------------------------------------------------
564 FUNCTION get_ar_tax_code_name (p_tax_id IN NUMBER)
565 RETURN VARCHAR2 IS
566 BEGIN
567 RETURN NULL;
568 END get_ar_tax_code_name;
569
570 --------------------------------------------------------------------------
571 -- FUNCTION : get_tax_group
572 -- RETURNS : Tax Group Name
573 -- HISTORY
574 -- 14-MAR-06 Hareesha o Bug #4756588 Created.Stub for R12.
575 -- Retrieves Tax Group Name in 11i.
576 -- DO NOT USE IN R12
577 --------------------------------------------------------------------------
578 FUNCTION get_tax_group (p_tax_group_id IN NUMBER)
579 RETURN VARCHAR2 IS
580 BEGIN
581 RETURN NULL;
582 END get_tax_group;
583
584
585 -------------------------------------------------------------------------------
586 -- FUNCTION : check_tax_upgrade
587 -- RETURNS : Tax-classification-code if exists, else ERROR-CODE
588 -- NOTES : for R12 release uptake
589 -- HISTORY
590 -- 09-JUN-06 Hareesha o Bug #5305903 Created.
591 -------------------------------------------------------------------------------
592 FUNCTION check_tax_upgrade (p_tax_code_id pn_payment_terms.tax_code_id%TYPE,
593 p_tax_group_id pn_payment_terms.tax_group_id%TYPE,
594 p_run_mode pn_leases.lease_class_code%TYPE)
595 RETURN VARCHAR2 IS
596
597 l_tcc pn_payment_terms.tax_classification_code%TYPE;
598
599 BEGIN
600 IF (p_tax_code_id IS NOT NULL OR p_tax_group_id IS NOT NULL)
601 THEN
602 l_tcc := pn_r12_util_pkg.get_tcc(
603 p_tax_code_id => nvl(p_tax_code_id, p_tax_group_id),
604 p_lease_class_code => p_run_mode,
605 p_as_of_date => SYSDATE);
606
607 IF l_tcc IS NULL THEN
608 RETURN 'PN_NO_TCC_FOUND';
609 ELSE
610 RETURN l_tcc;
611 END IF;
612 END IF;
613
614 END check_tax_upgrade;
615
616
617 -------------------------------------------------------------------------------
618 -- FUNCTION : check_tax_upgrade
619 -- RETURNS : 'PN_UPG_TCC' if tax-classification-code exists,
620 -- else PN_NO_TCC_FOUND
621 -- NOTES : for R12 release uptake
622 -- HISTORY
623 -- 09-JUN-06 Hareesha o Bug #5305903 Created.
624 -------------------------------------------------------------------------------
625 FUNCTION check_tax_upgrade (p_term_template_id pn_term_templates.term_template_id%TYPE)
626 RETURN VARCHAR2 IS
627
628 CURSOR get_term_temp_details IS
629 SELECT org_id,
630 tax_code_id,
631 tax_group_id,
632 tax_classification_code,
633 term_template_type
634 FROM pn_term_templates_all
635 WHERE term_template_id = p_term_template_id;
636
637 l_tcc pn_payment_terms.tax_classification_code%TYPE;
638
639 BEGIN
640
641 FOR rec IN get_term_temp_details LOOP
642
643 IF rec.tax_classification_code IS NULL AND
644 ( rec.tax_code_id IS NOT NULL OR rec.tax_group_id IS NOT NULL)
645 THEN
646
647 l_tcc := pn_r12_util_pkg.get_tcc(
648 p_tax_code_id => nvl(rec.tax_code_id, rec.tax_group_id),
649 p_lease_class_code => rec.term_template_type,
650 p_as_of_date => SYSDATE);
651
652 IF l_tcc IS NULL THEN
653 RETURN 'PN_NO_TCC_FOUND';
654 ELSE
655 UPDATE pn_term_templates_all
656 SET tax_classification_code = l_tcc,
657 tax_code_id = NULL,
658 tax_group_id = NULL
659 WHERE term_template_id = p_term_template_id;
660
661 COMMIT;
662 RETURN 'PN_UPG_TCC';
663 END IF;
664 END IF;
665 RETURN NULL;
666
667 END LOOP;
668 END check_tax_upgrade;
669
670
671 END pn_r12_util_pkg;
672