[Home] [Help]
PACKAGE BODY: APPS.HZ_GEO_STRUCTURE_LEVELS_PKG
Source
1 PACKAGE BODY HZ_GEO_STRUCTURE_LEVELS_PKG AS
2 /*$Header: ARHGSTTB.pls 120.1 2005/07/28 02:05:31 baianand noship $ */
3
4 PROCEDURE Insert_Row (
5 x_rowid IN OUT NOCOPY VARCHAR2,
6 x_geography_id IN NUMBER,
7 x_geography_type IN VARCHAR2,
8 x_parent_geography_type IN VARCHAR2,
9 x_object_version_number IN NUMBER,
10 x_relationship_type_id IN NUMBER,
11 x_country_code IN VARCHAR2,
12 x_geography_element_column IN VARCHAR2,
13 x_created_by_module IN VARCHAR2,
14 x_application_id IN NUMBER,
15 x_program_login_id IN NUMBER,
16 x_addr_val_level IN VARCHAR2
17 ) IS
18
19
20 BEGIN
21
22 --dbms_output.put_line.PUT_LINE('relationship_type_id is '||to_char(x_relationship_type_id));
23
24 INSERT INTO HZ_GEO_STRUCTURE_LEVELS (
25 geography_id,
26 geography_type,
27 parent_geography_type,
28 object_version_number,
29 relationship_type_id,
30 country_code,
31 geography_element_column,
32 created_by_module,
33 last_updated_by,
34 creation_date,
35 created_by,
36 last_update_date,
37 last_update_login,
38 application_id,
39 program_id,
40 program_login_id,
41 program_application_id,
42 request_id,
43 addr_val_level
44 )
45 VALUES (
46 DECODE(x_geography_id,
47 FND_API.G_MISS_NUM, NULL,
48 x_geography_id),
49 DECODE(x_geography_type,
50 FND_API.G_MISS_CHAR, NULL,
51 x_geography_type),
52 DECODE(x_parent_geography_type,
53 FND_API.G_MISS_CHAR, NULL,
54 x_parent_geography_type),
55 DECODE(x_object_version_number,
56 FND_API.G_MISS_NUM, NULL,
57 x_object_version_number),
58 DECODE(x_relationship_type_id,
59 FND_API.G_MISS_NUM, NULL,
60 x_relationship_type_id),
61 DECODE(x_country_code,
62 FND_API.G_MISS_CHAR, NULL,
63 x_country_code),
64 DECODE(x_geography_element_column,
65 FND_API.G_MISS_CHAR, NULL,
66 x_geography_element_column),
67 DECODE(x_created_by_module,
68 FND_API.G_MISS_CHAR, NULL,
69 x_created_by_module),
70 hz_utility_v2pub.last_updated_by,
71 hz_utility_v2pub.creation_date,
72 hz_utility_v2pub.created_by,
73 hz_utility_v2pub.last_update_date,
74 hz_utility_v2pub.last_update_login,
75 DECODE(x_application_id,
76 FND_API.G_MISS_NUM, NULL,
77 x_application_id),
78 hz_utility_v2pub.program_id,
79 DECODE(x_program_login_id,
80 FND_API.G_MISS_NUM, NULL,
81 x_program_login_id),
82 hz_utility_v2pub.program_application_id,
83 hz_utility_v2pub.request_id,
84 DECODE(x_addr_val_level,
85 FND_API.G_MISS_CHAR, NULL,
86 x_addr_val_level)
87 ) RETURNING
88 rowid
89 INTO
90 x_rowid;
91
92 END Insert_Row;
93
94 PROCEDURE Update_Row (
95 x_rowid IN OUT NOCOPY VARCHAR2,
96 x_geography_id IN NUMBER,
97 x_geography_type IN VARCHAR2,
98 x_parent_geography_type IN VARCHAR2,
99 x_object_version_number IN NUMBER,
100 x_relationship_type_id IN NUMBER,
101 x_country_code IN VARCHAR2,
102 x_geography_element_column IN VARCHAR2,
103 x_created_by_module IN VARCHAR2,
104 x_application_id IN NUMBER,
105 x_program_login_id IN NUMBER
106 ) IS
107 BEGIN
108
109 UPDATE HZ_GEO_STRUCTURE_LEVELS
110 SET
111 geography_id =
112 DECODE(x_geography_id,
113 NULL, geography_id,
114 FND_API.G_MISS_NUM, NULL,
115 x_geography_id),
116 geography_type =
117 DECODE(x_geography_type,
118 NULL, geography_type,
119 FND_API.G_MISS_CHAR, NULL,
120 x_geography_type),
121 parent_geography_type =
122 DECODE(x_parent_geography_type,
123 NULL, parent_geography_type,
124 FND_API.G_MISS_CHAR, NULL,
125 x_parent_geography_type),
126 object_version_number =
127 DECODE(x_object_version_number,
128 NULL, object_version_number,
129 FND_API.G_MISS_NUM, NULL,
130 x_object_version_number),
131 relationship_type_id =
132 DECODE(x_relationship_type_id,
133 NULL, relationship_type_id,
134 FND_API.G_MISS_NUM, NULL,
135 x_relationship_type_id),
136 country_code =
137 DECODE(x_country_code,
138 NULL, country_code,
139 FND_API.G_MISS_CHAR, NULL,
140 x_country_code),
141 geography_element_column =
142 DECODE(x_geography_element_column,
143 NULL, geography_element_column,
144 FND_API.G_MISS_CHAR, NULL,
145 x_geography_element_column),
146 created_by_module =
147 DECODE(x_created_by_module,
148 NULL, created_by_module,
149 FND_API.G_MISS_CHAR, NULL,
150 x_created_by_module),
151 last_updated_by = hz_utility_v2pub.last_updated_by,
152 creation_date = creation_date,
153 created_by = created_by,
154 last_update_date = hz_utility_v2pub.last_update_date,
155 last_update_login = hz_utility_v2pub.last_update_login,
156 application_id =
157 DECODE(x_application_id,
158 NULL, application_id,
159 FND_API.G_MISS_NUM, NULL,
160 x_application_id),
161 program_id = hz_utility_v2pub.program_id,
162 program_login_id =
163 DECODE(x_program_login_id,
164 NULL, program_login_id,
165 FND_API.G_MISS_NUM, NULL,
166 x_program_login_id),
167 program_application_id = hz_utility_v2pub.program_application_id,
168 request_id = hz_utility_v2pub.request_id
169 WHERE rowid = x_rowid;
170
171 IF ( SQL%NOTFOUND ) THEN
172 RAISE NO_DATA_FOUND;
173 END IF;
174
175 END Update_Row;
176
177 PROCEDURE Lock_Row (
178 x_rowid IN OUT NOCOPY VARCHAR2,
179 x_geography_id IN NUMBER,
180 x_geography_type IN VARCHAR2,
181 x_parent_geography_type IN VARCHAR2,
182 x_object_version_number IN NUMBER,
183 x_relationship_type_id IN NUMBER,
184 x_country_code IN VARCHAR2,
185 x_geography_element_column IN VARCHAR2,
186 x_created_by_module IN VARCHAR2,
187 x_last_updated_by IN NUMBER,
188 x_creation_date IN DATE,
189 x_created_by IN NUMBER,
190 x_last_update_date IN DATE,
191 x_last_update_login IN NUMBER,
192 x_application_id IN NUMBER,
193 x_program_id IN NUMBER,
194 x_program_login_id IN NUMBER,
195 x_program_application_id IN NUMBER,
196 x_request_id IN NUMBER
197 ) IS
198
199 CURSOR c IS
200 SELECT * FROM hz_geo_structure_levels
201 WHERE rowid = x_rowid
202 FOR UPDATE NOWAIT;
203 Recinfo c%ROWTYPE;
204
205 BEGIN
206
207 OPEN c;
208 FETCH c INTO Recinfo;
209 IF ( c%NOTFOUND ) THEN
210 CLOSE c;
211 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
212 APP_EXCEPTION.RAISE_EXCEPTION;
213 END IF;
214 CLOSE C;
215
216 IF (
217 ( ( Recinfo.geography_id = x_geography_id )
218 OR ( ( Recinfo.geography_id IS NULL )
219 AND ( x_geography_id IS NULL ) ) )
220 AND ( ( Recinfo.geography_type = x_geography_type )
221 OR ( ( Recinfo.geography_type IS NULL )
222 AND ( x_geography_type IS NULL ) ) )
223 AND ( ( Recinfo.parent_geography_type = x_parent_geography_type )
224 OR ( ( Recinfo.parent_geography_type IS NULL )
225 AND ( x_parent_geography_type IS NULL ) ) )
226 AND ( ( Recinfo.object_version_number = x_object_version_number )
227 OR ( ( Recinfo.object_version_number IS NULL )
228 AND ( x_object_version_number IS NULL ) ) )
229 AND ( ( Recinfo.relationship_type_id = x_relationship_type_id )
230 OR ( ( Recinfo.relationship_type_id IS NULL )
231 AND ( x_relationship_type_id IS NULL ) ) )
232 AND ( ( Recinfo.country_code = x_country_code )
233 OR ( ( Recinfo.country_code IS NULL )
234 AND ( x_country_code IS NULL ) ) )
235 AND ( ( Recinfo.geography_element_column = x_geography_element_column )
236 OR ( ( Recinfo.geography_element_column IS NULL )
237 AND ( x_geography_element_column IS NULL ) ) )
238 AND ( ( Recinfo.created_by_module = x_created_by_module )
239 OR ( ( Recinfo.created_by_module IS NULL )
240 AND ( x_created_by_module IS NULL ) ) )
241 AND ( ( Recinfo.last_updated_by = x_last_updated_by )
242 OR ( ( Recinfo.last_updated_by IS NULL )
243 AND ( x_last_updated_by IS NULL ) ) )
244 AND ( ( Recinfo.creation_date = x_creation_date )
245 OR ( ( Recinfo.creation_date IS NULL )
246 AND ( x_creation_date IS NULL ) ) )
247 AND ( ( Recinfo.created_by = x_created_by )
248 OR ( ( Recinfo.created_by IS NULL )
249 AND ( x_created_by IS NULL ) ) )
250 AND ( ( Recinfo.last_update_date = x_last_update_date )
251 OR ( ( Recinfo.last_update_date IS NULL )
252 AND ( x_last_update_date IS NULL ) ) )
253 AND ( ( Recinfo.last_update_login = x_last_update_login )
254 OR ( ( Recinfo.last_update_login IS NULL )
255 AND ( x_last_update_login IS NULL ) ) )
256 AND ( ( Recinfo.application_id = x_application_id )
257 OR ( ( Recinfo.application_id IS NULL )
258 AND ( x_application_id IS NULL ) ) )
259 AND ( ( Recinfo.program_id = x_program_id )
260 OR ( ( Recinfo.program_id IS NULL )
261 AND ( x_program_id IS NULL ) ) )
262 AND ( ( Recinfo.program_login_id = x_program_login_id )
263 OR ( ( Recinfo.program_login_id IS NULL )
264 AND ( x_program_login_id IS NULL ) ) )
265 AND ( ( Recinfo.program_application_id = x_program_application_id )
266 OR ( ( Recinfo.program_application_id IS NULL )
267 AND ( x_program_application_id IS NULL ) ) )
268 AND ( ( Recinfo.request_id = x_request_id )
269 OR ( ( Recinfo.request_id IS NULL )
270 AND ( x_request_id IS NULL ) ) )
271 ) THEN
272 RETURN;
273 ELSE
274 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
275 APP_EXCEPTION.RAISE_EXCEPTION;
276 END IF;
277
278 END Lock_Row;
279
280 PROCEDURE Select_Row (
281 x_geography_id IN OUT NOCOPY NUMBER,
282 x_geography_type IN OUT NOCOPY VARCHAR2,
283 x_parent_geography_type IN OUT NOCOPY VARCHAR2,
284 x_object_version_number OUT NOCOPY NUMBER,
285 x_relationship_type_id OUT NOCOPY NUMBER,
286 x_country_code OUT NOCOPY VARCHAR2,
287 x_geography_element_column OUT NOCOPY VARCHAR2,
288 x_created_by_module OUT NOCOPY VARCHAR2,
289 x_application_id OUT NOCOPY NUMBER,
290 x_program_login_id OUT NOCOPY NUMBER
291 ) IS
292 BEGIN
293
294 SELECT
295 NVL(geography_id, FND_API.G_MISS_NUM),
296 NVL(geography_type, FND_API.G_MISS_CHAR),
297 NVL(parent_geography_type, FND_API.G_MISS_CHAR),
298 NVL(relationship_type_id, FND_API.G_MISS_NUM),
299 NVL(country_code, FND_API.G_MISS_CHAR),
300 NVL(geography_element_column, FND_API.G_MISS_CHAR),
301 NVL(created_by_module, FND_API.G_MISS_CHAR),
302 NVL(application_id, FND_API.G_MISS_NUM),
303 NVL(program_login_id, FND_API.G_MISS_NUM)
304 INTO
305 x_geography_id,
306 x_geography_type,
307 x_parent_geography_type,
308 x_relationship_type_id,
309 x_country_code,
310 x_geography_element_column,
311 x_created_by_module,
312 x_application_id,
313 x_program_login_id
314 FROM HZ_GEO_STRUCTURE_LEVELS
315 WHERE geography_id = x_geography_id
316 AND geography_type = x_geography_type
320 WHEN NO_DATA_FOUND THEN
317 AND parent_geography_type = x_parent_geography_type;
318
319 EXCEPTION
321 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
322 FND_MESSAGE.SET_TOKEN('RECORD', 'geo_structure_levels_rec');
323 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(x_geography_id));
324 FND_MSG_PUB.ADD;
325 RAISE FND_API.G_EXC_ERROR;
326
327 END Select_Row;
328
329 PROCEDURE Delete_Row (
330 x_geography_id IN NUMBER,
331 x_geography_type IN VARCHAR2,
332 x_parent_geography_type IN VARCHAR2
333
334 ) IS
335 BEGIN
336
337 DELETE FROM HZ_GEO_STRUCTURE_LEVELS
338 WHERE geography_id = x_geography_id
339 AND geography_type = x_geography_type
340 AND parent_geography_type = x_parent_geography_type;
341
342 IF ( SQL%NOTFOUND ) THEN
343 RAISE NO_DATA_FOUND;
344 END IF;
345
346 END Delete_Row;
347
348 END HZ_GEO_STRUCTURE_LEVELS_PKG;