[Home] [Help]
PACKAGE BODY: APPS.PER_TAX_ADDRESS_PKG
Source
1 PACKAGE BODY per_tax_address_pkg AS
2 /* $Header: peaddovr.pkb 120.2 2006/04/13 11:27:04 saikrish noship $ */
3
4 PROCEDURE address_overide(p_person_id IN NUMBER,
5 p_date_from IN DATE,
6 p_overide_city OUT NOCOPY VARCHAR2,
7 p_overide_county OUT NOCOPY VARCHAR2,
8 p_overide_state OUT NOCOPY VARCHAR2,
9 p_overide_zip OUT NOCOPY VARCHAR2)
10 IS
11 l_date DATE;
12 l_last_date DATE;
13 l_city VARCHAR2(30);
14 l_county VARCHAR2(30);
15 l_state VARCHAR2(30);
16 l_zip VARCHAR2(30);
17 --
18 BEGIN
19 hr_utility.set_location(' per_tax_address_pkg.address_overide',10);
20 -- Get overide date and last input date.
21 ----------------------------------------
22 l_date := TO_DATE('01/01/' || (TO_CHAR(p_date_from, 'YYYY')),'DD/MM/YYYY');
23 SELECT MAX(date_from) INTO l_last_date
24 FROM per_addresses_v
25 WHERE person_id = p_person_id AND
26 primary_flag = 'Y' AND
27 date_from <= p_date_from;
28 --l_last_date := to_date('01-APR-2006','DD-MON-YYYY');
29 hr_utility.set_location(' l_last_date -> '||l_last_date,20);
30 hr_utility.set_location(' p_person_id -> '||p_person_id,30);
31 hr_utility.set_location(' p_date_from -> '||p_date_from,40);
32 hr_utility.set_location(' l_date -> '||l_date,50);
33 -- Get overide data
34 -------------------------
35 --l_last_date IS NOT NULL AND
36 IF l_date < l_last_date THEN -- WWBUG#2441642
37 hr_utility.set_location(' l_city -> '|| l_city, 45);
38 SELECT
39 NVL(add_information18,''),
40 NVL(add_information19,''),
41 NVL(add_information17,''),
42 NVL(add_information20,'')
43 INTO l_city, l_county, l_state, l_zip
44 FROM per_addresses_v
45 WHERE person_id = p_person_id AND
46 primary_flag = 'Y' AND
47 date_from = (SELECT max(date_from)
48 FROM per_addresses_v
49 WHERE date_from BETWEEN l_date AND p_date_from AND
50 person_id = p_person_id AND
51 primary_flag = 'Y');
52 hr_utility.set_location(' l_city -> '|| l_city, 60);
53 hr_utility.set_location(' l_county -> '|| l_county, 70);
54 hr_utility.set_location(' l_state -> '|| l_state, 80);
55 ELSE
56 hr_utility.set_location(' l_state -> '|| l_state, 85);
57 SELECT
58 town_or_city,
59 region_1,
60 region_2,
61 postal_code
62 INTO l_city, l_county, l_state, l_zip
63 FROM per_addresses_v
64 WHERE person_id = p_person_id AND
65 primary_flag = 'Y' AND
66 l_date BETWEEN date_from AND
67 NVL(date_to, TO_DATE('31/12/4712', 'DD/MM/YYYY'));
68
69 hr_utility.set_location(' l_city -> '|| l_city, 90);
70 hr_utility.set_location(' l_county -> '|| l_county, 100);
71 hr_utility.set_location(' l_state -> '|| l_state, 110);
72
73 END IF;
74 IF l_state <> 'IN' THEN
75 p_overide_city := '';
76 p_overide_county := '';
77 p_overide_state := '';
78 p_overide_zip := '';
79 ELSE
80 p_overide_city := l_city;
81 p_overide_county := l_county;
82 p_overide_state := l_state;
83 p_overide_zip := l_zip;
84 END IF;
85 --
86 EXCEPTION
87
88 WHEN NO_DATA_FOUND THEN
89 p_overide_city := 'NO DATA';
90 p_overide_county := 'NO DATA';
91 p_overide_state := 'NO DATA';
92 p_overide_zip := 'NO DATA';
93 hr_utility.set_location('exception l_state -> '|| l_state, 130);
94 END address_overide;
95 --
96 FUNCTION overide_tax_state(p_person_id NUMBER, p_date_from DATE)
97 RETURN VARCHAR2
98 IS
99 l_chk_date VARCHAR2(30);
100 l_date DATE;
101 l_state VARCHAR2(30);
102 CURSOR c_new_entry IS
103 SELECT 'N' FROM per_addresses_v
104 WHERE person_id = p_person_id AND
105 date_from IS NOT NULL;
106
107 CURSOR c_state(p_person_id IN NUMBER,p_date IN DATE) IS
108 SELECT region_2
109 FROM per_addresses_v
110 WHERE person_id = p_person_id
111 AND primary_flag = 'Y'
112 AND p_date BETWEEN date_from
113 AND NVL(date_to, TO_DATE('31/12/4712', 'DD/MM/YYYY'));
114
115 BEGIN
116 hr_utility.set_location('per_tax_address_pkg.overide_tax_state',10);
117 hr_utility.set_location('per_tax_address_pkg,p_date_from ->'|| p_date_from,11);
118 -- Check if the entry is new
119 -------------------------
120 OPEN c_new_entry;
121 FETCH c_new_entry INTO l_chk_date;
122 IF c_new_entry%NOTFOUND THEN
123 l_state := 'NEW ENTRY';
124 CLOSE c_new_entry;
125 hr_utility.set_location(' NEW ENTRY ',20);
126 ELSE
127 CLOSE c_new_entry;
128 -- Get overide date
129 -------------------------
130 l_date := TO_DATE('01/01/' || (TO_CHAR(p_date_from , 'YYYY')),
131 'DD/MM/YYYY');
132 -- Get overide data
133 -------------------------
134 OPEN c_state(p_person_id,l_date);
135 FETCH c_state INTO l_state;
136 IF c_state%NOTFOUND THEN
137
138 SELECT region_2
139 INTO l_state
140 FROM per_addresses_v
141 WHERE person_id = p_person_id AND
142 primary_flag = 'Y' AND
143 date_from = (SELECT max(date_from)
144 FROM per_addresses_v
145 WHERE date_from >= l_date
146 AND date_from < p_date_from AND
147 person_id = p_person_id AND
148 primary_flag = 'Y');
149
150 END IF;
151 CLOSE c_state;
152 hr_utility.set_location('per_tax_address_pkg.overide_tax_state,l_date ->'||l_date,20);
153 hr_utility.set_location('per_tax_address_pkg.overide_tax_stat,l_state -> '||l_state,30);
154 END IF;
155 hr_utility.set_location('per_tax_address_pkg.overide_tax_stat,l_state -> '||l_state,35);
156 RETURN l_state;
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 hr_utility.set_location('per_tax_address_pkg.overide_tax_stat,l_state -> '||l_state,40);
160 RETURN ('NO DATA');
161 END overide_tax_state;
162 END per_tax_address_pkg;