1 PACKAGE BODY ZX_MERGE_LOC_CHECK_PKG AS
2 /* $Header: zxcmergegnrchkb.pls 120.3.12010000.2 2008/10/20 18:19:08 tsen ship $ */
3
4 G_CURRENT_RUNTIME_LEVEL NUMBER;
5 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
6 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
11 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'ZX.ZX_MERGE_LOC_CHECK_PKG';
12 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
13 l_procedure_name CONSTANT VARCHAR2(30) := 'CHECK_GNR';
14
15 PROCEDURE CHECK_GNR(p_from_location_id IN NUMBER,
16 p_to_location_id IN NUMBER,
17 p_init_msg_list IN VARCHAR2,
18 x_merge_yn OUT NOCOPY VARCHAR2,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2) AS
22 TYPE number_tbl_type IS TABLE OF NUMBER;
23 TYPE varchar2_tbl_type IS TABLE OF VARCHAR2(30);
24 l_from_geography_id_tbl number_tbl_type;
25 l_from_geography_type_tbl varchar2_tbl_type;
26 l_to_geography_id_tbl number_tbl_type;
27 l_to_geography_type_tbl varchar2_tbl_type;
28 l_from_country_code VARCHAR2(30);
29 l_to_country_code VARCHAR2(30);
30 l_from_tax_usage_exists VARCHAR2(6);
31 l_to_tax_usage_exists VARCHAR2(6);
32 l_from_geography_id_count NUMBER;
33 l_to_geography_id_count NUMBER;
34 BEGIN
35 x_return_status := FND_API.G_RET_STS_SUCCESS;
36 x_merge_yn := 'N';
37 BEGIN
38 SELECT 'TRUE'
39 INTO l_from_tax_usage_exists
40 FROM hz_geo_struct_map map, hz_locations loc, hz_address_usages usage
41 WHERE map.country_code = loc.country
42 AND map.loc_tbl_name = 'HZ_LOCATIONS'
43 AND nvl(map.address_style,'1') = nvl(loc.address_style,'1')
44 AND map.map_id = usage.map_id
45 AND usage.usage_code = 'TAX'
46 AND loc.location_id = p_from_location_id;
47 EXCEPTION WHEN NO_DATA_FOUND THEN
48 l_from_tax_usage_exists := 'FALSE';
49 END;
50
51 BEGIN
52 SELECT 'TRUE'
53 INTO l_to_tax_usage_exists
54 FROM hz_geo_struct_map map, hz_locations loc, hz_address_usages usage
55 WHERE map.country_code = loc.country
56 AND map.loc_tbl_name = 'HZ_LOCATIONS'
57 AND nvl(map.address_style,'1') = nvl(loc.address_style,'1')
58 AND map.map_id = usage.map_id
59 AND usage.usage_code = 'TAX'
60 AND loc.location_id = p_to_location_id;
61 EXCEPTION WHEN NO_DATA_FOUND THEN
62 l_to_tax_usage_exists := 'FALSE';
63 END;
64
65 IF l_from_tax_usage_exists = 'TRUE' and l_to_tax_usage_exists = 'TRUE' THEN
66 SELECT gnr.geography_id, gnr.geography_type
67 BULK COLLECT INTO l_from_geography_id_tbl, l_from_geography_type_tbl
68 FROM hz_geo_name_references gnr,
69 hz_geo_name_reference_log log,
70 hz_geo_struct_map map,
71 hz_locations loc,
72 hz_address_usages usage,
73 hz_address_usage_dtls dtl
74 WHERE gnr.location_id = p_from_location_id
75 AND gnr.location_id = log.location_id
76 AND log.map_status = 'S'
77 AND log.usage_code = 'TAX'
78 AND loc.location_id = gnr.location_id
79 AND map.country_code = loc.country
80 AND map.loc_tbl_name = 'HZ_LOCATIONS'
81 AND nvl(map.address_style,'1') = nvl(loc.address_style,'1')
82 AND map.map_id = usage.map_id
83 AND usage.usage_code = 'TAX'
84 AND dtl.usage_id = usage.usage_id
85 AND dtl.geography_type = gnr.geography_type
86 ORDER BY gnr.geography_id;
87
88 SELECT gnr.geography_id, gnr.geography_type
89 BULK COLLECT INTO l_to_geography_id_tbl, l_to_geography_type_tbl
90 FROM hz_geo_name_references gnr,
91 hz_geo_name_reference_log log,
92 hz_geo_struct_map map,
93 hz_locations loc,
94 hz_address_usages usage,
95 hz_address_usage_dtls dtl
96 WHERE gnr.location_id = p_to_location_id
97 AND gnr.location_id = log.location_id
98 AND log.map_status = 'S'
99 AND log.usage_code = 'TAX'
100 AND loc.location_id = gnr.location_id
101 AND map.country_code = loc.country
102 AND map.loc_tbl_name = 'HZ_LOCATIONS'
103 AND nvl(map.address_style,'1') = nvl(loc.address_style,'1')
104 AND map.map_id = usage.map_id
105 AND usage.usage_code = 'TAX'
106 AND dtl.usage_id = usage.usage_id
107 AND dtl.geography_type = gnr.geography_type
108 ORDER BY gnr.geography_id;
109
110 l_from_geography_id_count := l_from_geography_id_tbl.count;
111 l_to_geography_id_count := l_to_geography_id_tbl.count;
112
113
114 IF l_from_geography_id_count = 0 OR l_to_geography_id_count = 0 THEN
115 x_merge_yn := 'N';
116 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
117 l_log_msg := 'B: Tax Usage exists for from and to locations but no GNR has been created';
118 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
119 END IF;
120 return;
121 END IF;
122 IF l_from_geography_id_count <> l_to_geography_id_count THEN
123 x_merge_yn := 'N';
124 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
125 l_log_msg := 'B: Tax Usage exists for from and to locations but GNR rows are different';
126 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
127 END IF;
128 ELSE
129 FOR i IN l_from_geography_id_tbl.first..l_from_geography_id_tbl.last LOOP
130 IF l_from_geography_id_tbl(i) = l_to_geography_id_tbl(i) THEN
131 x_merge_yn := 'Y';
132 ELSE
133 x_merge_yn := 'N';
134 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
135 l_log_msg := 'B: Tax Usage exists for from and to locations and GNR rows are not identical';
136 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
137 END IF;
138 EXIT;
139 END IF ;
140 END LOOP;
141 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
142 l_log_msg := 'B: After Loop Tax Usage for from and to locations and GNR rows are identical';
143 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
144 END IF;
145
146 END IF;
147
148 ELSIF l_from_tax_usage_exists = 'FALSE' and l_to_tax_usage_exists = 'FALSE' THEN
149 SELECT country
150 INTO l_from_country_code
151 FROM hz_locations loc
152 WHERE loc.location_id = p_from_location_id;
153
154 SELECT country
155 INTO l_to_country_code
156 FROM hz_locations loc
157 WHERE loc.location_id = p_to_location_id;
158
159 IF l_from_country_code <> l_to_country_code THEN
160 x_merge_yn := 'N';
161 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
162 l_log_msg := 'B: Tax Usage does not exist for from and to locations and country codes are different';
163 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
164 END IF;
165 ELSE
166 x_merge_yn := 'Y';
167 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
168 l_log_msg := 'B: Tax Usage does not exist for from and to locations and country codes are identical';
169 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
170 END IF;
171 END IF;
172
173 ELSIF l_from_tax_usage_exists = 'TRUE' and l_to_tax_usage_exists = 'FALSE' THEN
174 x_merge_yn := 'N';
175 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
176 l_log_msg := 'B: Tax Usage exists for from location but not for to location';
177 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
178 END IF;
179
180 ELSIF l_from_tax_usage_exists = 'FALSE' and l_to_tax_usage_exists = 'TRUE' THEN
181 x_merge_yn := 'N';
182 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
183 l_log_msg := 'B: Tax Usage does not exist for from location but exists for to location';
184 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
185 END IF;
186 END IF;
187 EXCEPTION WHEN OTHERS THEN
188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
189 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
190 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
191 FND_MSG_PUB.ADD;
192 FND_MSG_PUB.Count_And_Get(
193 p_encoded => FND_API.G_FALSE,
194 p_count => x_msg_count,
195 p_data => x_msg_data);
196
197 -- Logging Infra: Statement level
198 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
199 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'E: EXC: OTHERS: '||SQLCODE||': '||SQLERRM);
200 END IF;
201
202
203 END;
204
205 END;
[Home] [Help]