[Home] [Help]
PACKAGE BODY: APPS.AHL_RM_PROD_ZONE_AS_PVT
Source
1 PACKAGE BODY AHL_RM_PROD_ZONE_AS_PVT AS
2 /* $Header: AHLVAPMB.pls 115.31 2004/05/19 16:16:50 bachandr noship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_RM_PROD_ZONE_AS_PVT';
5 G_API_NAME VARCHAR2(30) := 'PROCESS_PROD_ZONE_AS';
6 G_DEBUG VARCHAR2(1) := NVL(AHL_DEBUG_PUB.is_log_enabled,'N');
7
8 -- Function to get the Record Identifier for Error Messages
9 FUNCTION get_record_identifier
10 (
11 p_prod_zone_as_rec IN prod_zone_as_rec_type
12 ) RETURN VARCHAR2
13 IS
14
15 l_record_identifier VARCHAR2(2000) := '';
16
17 BEGIN
18
19 IF ( p_prod_zone_as_rec.product_type IS NOT NULL AND
20 p_prod_zone_as_rec.product_type <> FND_API.G_MISS_CHAR ) THEN
21 l_record_identifier := l_record_identifier || p_prod_zone_as_rec.product_type;
22 END IF;
23
24 l_record_identifier := l_record_identifier || ' - ';
25
26 IF ( p_prod_zone_as_rec.zone IS NOT NULL AND
27 p_prod_zone_as_rec.zone <> FND_API.G_MISS_CHAR ) THEN
28 l_record_identifier := l_record_identifier || p_prod_zone_as_rec.zone;
29 END IF;
30
31 l_record_identifier := l_record_identifier || ' - ';
32
33 IF ( p_prod_zone_as_rec.sub_zone IS NOT NULL AND
34 p_prod_zone_as_rec.sub_zone <> FND_API.G_MISS_CHAR ) THEN
35 l_record_identifier := l_record_identifier || p_prod_zone_as_rec.sub_zone;
36 END IF;
37
38 RETURN l_record_identifier;
39
40 END get_record_identifier;
41
42 -- Procedure to validate the all the inputs except the table structure of the API
43 PROCEDURE validate_api_inputs
44 (
45 p_prod_zone_as_tbl IN prod_zone_as_tbl_type,
46 p_associate_flag IN VARCHAR2,
47 x_return_status OUT NOCOPY VARCHAR2
48 )
49 IS
50
51 l_return_status VARCHAR2(1);
52 l_msg_data VARCHAR2(2000);
53
54 BEGIN
55 x_return_status := FND_API.G_RET_STS_SUCCESS;
56
57 -- Check whether the associate type is 'M' or 'S'
58 IF ( p_associate_flag <> 'M' AND p_associate_flag <> 'S') THEn
59 FND_MESSAGE.set_name('AHL', 'AHL_RM_INVALID_ASSOCIATE_TYPE');
60 FND_MESSAGE.set_token('FIELD', p_associate_flag );
61 FND_MSG_PUB.add;
62 x_return_status := FND_API.G_RET_STS_ERROR;
63 RETURN;
64 END IF;
65
66 -- Check if at least one record is passed in p_prod_zone_as_tbl
67 IF ( p_prod_zone_as_tbl.count < 1 ) THEN
68 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
69 FND_MESSAGE.set_token( 'PROCEDURE', G_PKG_NAME || '.' || G_API_NAME );
70 FND_MSG_PUB.add;
71 x_return_status := FND_API.G_RET_STS_ERROR;
72 RETURN;
73 END IF;
74
75 -- Validate DML Operation
76 FOR i IN 1..p_prod_zone_as_tbl.count LOOP
77 IF ( p_prod_zone_as_tbl(i).dml_operation <> 'C' AND
78 p_prod_zone_as_tbl(i).dml_operation <> 'U' AND
79 p_prod_zone_as_tbl(i).dml_operation <> 'D' ) THEN
80 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML' );
81 FND_MESSAGE.set_token( 'FIELD', p_prod_zone_as_tbl(i).dml_operation );
82 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_prod_zone_as_tbl(i) ) );
83 FND_MSG_PUB.add;
84 x_return_status := FND_API.G_RET_STS_ERROR;
85 RETURN;
86 END IF;
87 END LOOP;
88
89 END validate_api_inputs;
90
91 -- Procedure to Default NULL / G_MISS Values for LOV attributes
92 PROCEDURE clear_lov_attribute_ids
93 (
94 p_x_prod_zone_as_rec IN OUT NOCOPY prod_zone_as_rec_type
95 )
96 IS
97
98 BEGIN
99 IF ( p_x_prod_zone_as_rec.product_type IS NULL ) THEN
100 p_x_prod_zone_as_rec.product_type_code := NULL;
101 ELSIF ( p_x_prod_zone_as_rec.product_type = FND_API.G_MISS_CHAR ) THEN
102 p_x_prod_zone_as_rec.product_type_code := FND_API.G_MISS_CHAR;
103 END IF;
104
105 IF ( p_x_prod_zone_as_rec.zone IS NULL ) THEN
106 p_x_prod_zone_as_rec.zone_code := NULL;
107 ELSIF ( p_x_prod_zone_as_rec.zone = FND_API.G_MISS_CHAR ) THEN
108 p_x_prod_zone_as_rec.zone_code := FND_API.G_MISS_CHAR;
109 END IF;
110
111 IF ( p_x_prod_zone_as_rec.sub_zone IS NULL ) THEN
112 p_x_prod_zone_as_rec.sub_zone_code := NULL;
113 ELSIF ( p_x_prod_zone_as_rec.sub_zone = FND_API.G_MISS_CHAR ) THEN
114 p_x_prod_zone_as_rec.sub_zone_code := FND_API.G_MISS_CHAR;
115 END IF;
116
117 END clear_lov_attribute_ids;
118
119 -- Procedure to perform Value to ID conversion and validation for LOV attributes
120 PROCEDURE convert_values_to_ids
121 (
122 p_x_prod_zone_as_rec IN OUT NOCOPY prod_zone_as_rec_type,
123 x_return_status OUT NOCOPY VARCHAR2
124 )
125 IS
126
127 l_return_status VARCHAR2(1);
128 l_msg_data VARCHAR2(2000);
129
130 BEGIN
131 x_return_status := FND_API.G_RET_STS_SUCCESS;
132
133 -- Convert / Validate product type
134 IF ( ( p_x_prod_zone_as_rec.product_type_code IS NOT NULL AND
135 p_x_prod_zone_as_rec.product_type_code <> FND_API.G_MISS_CHAR ) OR
136 ( p_x_prod_zone_as_rec.product_type IS NOT NULL AND
137 p_x_prod_zone_as_rec.product_type <> FND_API.G_MISS_CHAR ) ) THEN
138
139 AHL_RM_ROUTE_UTIL.validate_lookup
140 (
141 p_lookup_type => 'ITEM_TYPE',
142 p_lookup_meaning => p_x_prod_zone_as_rec.product_type,
143 p_x_lookup_code => p_x_prod_zone_as_rec.product_type_code,
144 x_msg_data => l_msg_data,
145 x_return_status => l_return_status
146 );
147
148 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
149 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
150 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_PRODUCT_TYPE' );
151 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
152 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_PRODUCT_TYPES' );
153 ELSE
154 FND_MESSAGE.set_name( 'AHL', l_msg_data );
155 END IF;
156
157 IF ( p_x_prod_zone_as_rec.product_type IS NULL OR
158 p_x_prod_zone_as_rec.product_type = FND_API.G_MISS_CHAR ) THEN
159 FND_MESSAGE.set_token( 'FIELD', p_x_prod_zone_as_rec.product_type_code );
160 ELSE
161 FND_MESSAGE.set_token( 'FIELD', p_x_prod_zone_as_rec.product_type );
162 END IF;
163
164 FND_MSG_PUB.add;
165 x_return_status := l_return_status;
166 END IF;
167
168 END IF;
169
170 -- Convert / Validate zone
171 IF ( ( p_x_prod_zone_as_rec.zone_code IS NOT NULL AND
172 p_x_prod_zone_as_rec.zone_code <> FND_API.G_MISS_CHAR ) OR
173 ( p_x_prod_zone_as_rec.zone IS NOT NULL AND
174 p_x_prod_zone_as_rec.zone <> FND_API.G_MISS_CHAR ) )
175 THEN
176
177 AHL_RM_ROUTE_UTIL.validate_lookup
178 (
179 p_lookup_type =>'AHL_ZONE',
180 p_lookup_meaning => p_x_prod_zone_as_rec.zone,
181 p_x_lookup_code => p_x_prod_zone_as_rec.zone_code,
182 x_msg_data => l_msg_data,
183 x_return_status => l_return_status
184 );
185
186 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
187 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
188 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_ZONE_REC' );
189 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
190 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_ZONES_REC' );
191 ELSE
192 FND_MESSAGE.set_name( 'AHL', l_msg_data );
193 END IF;
194
195 IF ( p_x_prod_zone_as_rec.zone IS NULL OR
196 p_x_prod_zone_as_rec.zone = FND_API.G_MISS_CHAR ) THEN
197 FND_MESSAGE.set_token( 'FIELD', p_x_prod_zone_as_rec.zone_code );
198 ELSE
199 FND_MESSAGE.set_token( 'FIELD', p_x_prod_zone_as_rec.zone );
200 END IF;
201
202 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_prod_zone_as_rec ) );
203 FND_MSG_PUB.add;
204 x_return_status := l_return_status;
205 END IF;
206
207 END IF;
208
209 -- Convert / Validate sub zone
210 IF ( ( p_x_prod_zone_as_rec.sub_zone_code IS NOT NULL AND
211 p_x_prod_zone_as_rec.sub_zone_code <> FND_API.G_MISS_CHAR ) OR
212 ( p_x_prod_zone_as_rec.sub_zone IS NOT NULL AND
213 p_x_prod_zone_as_rec.sub_zone <> FND_API.G_MISS_CHAR ) )
214 THEN
215
216 AHL_RM_ROUTE_UTIL.validate_lookup
217 (
218 p_lookup_type => 'AHL_SUB_ZONE',
219 p_lookup_meaning => p_x_prod_zone_as_rec.sub_zone,
220 p_x_lookup_code => p_x_prod_zone_as_rec.sub_zone_code,
221 x_msg_data => l_msg_data,
222 x_return_status => l_return_status
223 );
224
225 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
226 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
227 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_SUB_ZONE_REC' );
228 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
229 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_SUB_ZONES_REC' );
230 ELSE
231 FND_MESSAGE.set_name( 'AHL', l_msg_data );
232 END IF;
233
234 IF ( p_x_prod_zone_as_rec.sub_zone IS NULL OR
235 p_x_prod_zone_as_rec.sub_zone = FND_API.G_MISS_CHAR ) THEN
236 FND_MESSAGE.set_token( 'FIELD', p_x_prod_zone_as_rec.sub_zone_code );
237 ELSE
238 FND_MESSAGE.set_token( 'FIELD', p_x_prod_zone_as_rec.sub_zone );
239 END IF;
240
241 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_prod_zone_as_rec ) );
242 FND_MSG_PUB.add;
243 x_return_status := l_return_status;
244 END IF;
245
246 END IF;
247
248 END convert_values_to_ids;
249
250 -- Procedure to add Default values for prod_zone_as attributes
251 PROCEDURE default_attributes
252 (
253 p_x_prod_zone_as_rec IN OUT NOCOPY prod_zone_as_rec_type
254 )
255 IS
256
257 BEGIN
258
259 p_x_prod_zone_as_rec.last_update_date := SYSDATE;
260 p_x_prod_zone_as_rec.last_updated_by := FND_GLOBAL.user_id;
261 p_x_prod_zone_as_rec.last_update_login := FND_GLOBAL.login_id;
262
263 IF ( p_x_prod_zone_as_rec.dml_operation = 'C' ) THEN
264 p_x_prod_zone_as_rec.object_version_number := 1;
265 p_x_prod_zone_as_rec.creation_date := SYSDATE;
266 p_x_prod_zone_as_rec.created_by := FND_GLOBAL.user_id;
267 END IF;
268
269 END default_attributes;
270
271 -- Procedure to add Default values for missing attributes (CREATE)
272 PROCEDURE default_missing_attributes
273 (
274 p_x_prod_zone_as_rec IN OUT NOCOPY prod_zone_as_rec_type
275 )
276 IS
277
278 BEGIN
279
280 -- Convert G_MISS values to NULL
281 IF ( p_x_prod_zone_as_rec.product_type_code = FND_API.G_MISS_CHAR ) THEN
282 p_x_prod_zone_as_rec.product_type_code := null;
283 END IF;
284
285 IF ( p_x_prod_zone_as_rec.product_type = FND_API.G_MISS_CHAR ) THEN
286 p_x_prod_zone_as_rec.product_type := null;
287 END IF;
288
289 IF ( p_x_prod_zone_as_rec.zone_code = FND_API.G_MISS_CHAR ) THEN
290 p_x_prod_zone_as_rec.zone_code := null;
291 END IF;
292
293 IF ( p_x_prod_zone_as_rec.zone = FND_API.G_MISS_CHAR ) THEN
294 p_x_prod_zone_as_rec.zone := null;
295 END IF;
296
297 IF ( p_x_prod_zone_as_rec.sub_zone_code = FND_API.G_MISS_CHAR ) THEN
298 p_x_prod_zone_as_rec.sub_zone_code := null;
299 END IF;
300
301 IF ( p_x_prod_zone_as_rec.sub_zone = FND_API.G_MISS_CHAR ) THEN
302 p_x_prod_zone_as_rec.sub_zone := null;
303 END IF;
304
305 IF ( p_x_prod_zone_as_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
306 p_x_prod_zone_as_rec.attribute_category := null;
307 END IF;
308
309 IF ( p_x_prod_zone_as_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
310 p_x_prod_zone_as_rec.attribute1 := null;
311 END IF;
312
313 IF ( p_x_prod_zone_as_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
314 p_x_prod_zone_as_rec.attribute2 := null;
315 END IF;
316
317 IF ( p_x_prod_zone_as_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
318 p_x_prod_zone_as_rec.attribute3 := null;
319 END IF;
320
321 IF ( p_x_prod_zone_as_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
322 p_x_prod_zone_as_rec.attribute4 := null;
323 END IF;
324
325 IF ( p_x_prod_zone_as_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
326 p_x_prod_zone_as_rec.attribute5 := null;
327 END IF;
328
329 IF ( p_x_prod_zone_as_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
330 p_x_prod_zone_as_rec.attribute6 := null;
331 END IF;
332
333 IF ( p_x_prod_zone_as_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
334 p_x_prod_zone_as_rec.attribute7 := null;
335 END IF;
336
337 IF ( p_x_prod_zone_as_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
338 p_x_prod_zone_as_rec.attribute8 := null;
339 END IF;
340
341 IF ( p_x_prod_zone_as_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
342 p_x_prod_zone_as_rec.attribute9 := null;
343 END IF;
344
345 IF ( p_x_prod_zone_as_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
346 p_x_prod_zone_as_rec.attribute10 := null;
347 END IF;
348
349 IF ( p_x_prod_zone_as_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
350 p_x_prod_zone_as_rec.attribute11 := null;
351 END IF;
352
353 IF ( p_x_prod_zone_as_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
354 p_x_prod_zone_as_rec.attribute12 := null;
355 END IF;
356
357 IF ( p_x_prod_zone_as_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
358 p_x_prod_zone_as_rec.attribute13 := null;
359 END IF;
360
361 IF ( p_x_prod_zone_as_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
362 p_x_prod_zone_as_rec.attribute14 := null;
363 END IF;
364
365 IF ( p_x_prod_zone_as_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
366 p_x_prod_zone_as_rec.attribute15 := null;
367 END IF;
368
369 END default_missing_attributes;
370
371 -- Procedure to add Default values for unchanged attributes (UPDATE)
372 PROCEDURE default_unchanged_attributes
373 (
374 p_x_prod_zone_as_rec IN OUT NOCOPY prod_zone_as_rec_type
375 )
376 IS
377
378 l_old_prod_zone_as_rec prod_zone_as_rec_type;
379
380 CURSOR get_old_rec ( c_prodtype_zone_id NUMBER )
381 IS
382 SELECT product_type_code,
383 zone_code,
384 sub_zone_code,
385 attribute_category,
386 attribute1,
387 attribute2,
388 attribute3,
389 attribute4,
390 attribute5,
391 attribute6,
392 attribute7,
393 attribute8,
394 attribute9,
395 attribute10,
396 attribute11,
397 attribute12,
398 attribute13,
399 attribute14,
400 attribute15
401 FROM AHL_PRODTYPE_ZONES
402 WHERE prodtype_zone_id = c_prodtype_zone_id;
403
404 BEGIN
405
406 -- Get the old record from AHL_RT_OPER_RESOURCES.
407 OPEN get_old_rec( p_x_prod_zone_as_rec.prodtype_zone_id );
408
409 FETCH get_old_rec INTO
410 l_old_prod_zone_as_rec.product_type_code,
411 l_old_prod_zone_as_rec.zone_code,
412 l_old_prod_zone_as_rec.sub_zone_code,
413 l_old_prod_zone_as_rec.attribute_category,
414 l_old_prod_zone_as_rec.attribute1,
415 l_old_prod_zone_as_rec.attribute2,
416 l_old_prod_zone_as_rec.attribute3,
417 l_old_prod_zone_as_rec.attribute4,
418 l_old_prod_zone_as_rec.attribute5,
419 l_old_prod_zone_as_rec.attribute6,
420 l_old_prod_zone_as_rec.attribute7,
421 l_old_prod_zone_as_rec.attribute8,
422 l_old_prod_zone_as_rec.attribute9,
423 l_old_prod_zone_as_rec.attribute10,
424 l_old_prod_zone_as_rec.attribute11,
425 l_old_prod_zone_as_rec.attribute12,
426 l_old_prod_zone_as_rec.attribute13,
427 l_old_prod_zone_as_rec.attribute14,
428 l_old_prod_zone_as_rec.attribute15;
429
430 IF get_old_rec%NOTFOUND THEN
431 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_PROD_ZONE_REC' );
432 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_prod_zone_as_rec ) );
433 FND_MSG_PUB.add;
434 CLOSE get_old_rec;
435 RAISE FND_API.G_EXC_ERROR;
436 END IF;
437
438 CLOSE get_old_rec;
439
440 -- Convert G_MISS values to NULL and NULL values to Old values
441 IF ( p_x_prod_zone_as_rec.product_type_code = FND_API.G_MISS_CHAR ) THEN
442 p_x_prod_zone_as_rec.product_type_code := null;
443 ELSIF ( p_x_prod_zone_as_rec.product_type_code IS NULL ) THEN
444 p_x_prod_zone_as_rec.product_type_code := l_old_prod_zone_as_rec.product_type_code;
445 END IF;
446
447 IF ( p_x_prod_zone_as_rec.product_type = FND_API.G_MISS_CHAR ) THEN
448 p_x_prod_zone_as_rec.product_type := null;
449 ELSIF ( p_x_prod_zone_as_rec.product_type IS NULL ) THEN
450 p_x_prod_zone_as_rec.product_type := l_old_prod_zone_as_rec.product_type;
451 END IF;
452
453 IF ( p_x_prod_zone_as_rec.zone_code = FND_API.G_MISS_CHAR ) THEN
454 p_x_prod_zone_as_rec.zone_code := null;
455 ELSIF ( p_x_prod_zone_as_rec.zone_code IS NULL ) THEN
456 p_x_prod_zone_as_rec.zone_code := l_old_prod_zone_as_rec.zone_code;
457 END IF;
458
459 IF ( p_x_prod_zone_as_rec.zone = FND_API.G_MISS_CHAR ) THEN
460 p_x_prod_zone_as_rec.zone := null;
461 ELSIF ( p_x_prod_zone_as_rec.zone IS NULL ) THEN
462 p_x_prod_zone_as_rec.zone := l_old_prod_zone_as_rec.zone;
463 END IF;
464
465 IF ( p_x_prod_zone_as_rec.sub_zone_code = FND_API.G_MISS_CHAR ) THEN
466 p_x_prod_zone_as_rec.sub_zone_code := null;
467 ELSIF ( p_x_prod_zone_as_rec.sub_zone_code IS NULL ) THEN
468 p_x_prod_zone_as_rec.sub_zone_code := l_old_prod_zone_as_rec.sub_zone_code;
469 END IF;
470
471 IF ( p_x_prod_zone_as_rec.sub_zone = FND_API.G_MISS_CHAR ) THEN
472 p_x_prod_zone_as_rec.sub_zone := null;
473 ELSIF ( p_x_prod_zone_as_rec.sub_zone IS NULL ) THEN
474 p_x_prod_zone_as_rec.sub_zone := l_old_prod_zone_as_rec.sub_zone;
475 END IF;
476
477 IF ( p_x_prod_zone_as_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
478 p_x_prod_zone_as_rec.attribute_category := null;
479 ELSIF ( p_x_prod_zone_as_rec.attribute_category IS NULL ) THEN
480 p_x_prod_zone_as_rec.attribute_category := l_old_prod_zone_as_rec.attribute_category;
481 END IF;
482
483 IF ( p_x_prod_zone_as_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
484 p_x_prod_zone_as_rec.attribute1 := null;
485 ELSIF ( p_x_prod_zone_as_rec.attribute1 IS NULL ) THEN
486 p_x_prod_zone_as_rec.attribute1 := l_old_prod_zone_as_rec.attribute1;
487 END IF;
488
489 IF ( p_x_prod_zone_as_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
490 p_x_prod_zone_as_rec.attribute2 := null;
491 ELSIF ( p_x_prod_zone_as_rec.attribute2 IS NULL ) THEN
492 p_x_prod_zone_as_rec.attribute2 := l_old_prod_zone_as_rec.attribute2;
493 END IF;
494
495 IF ( p_x_prod_zone_as_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
496 p_x_prod_zone_as_rec.attribute3 := null;
497 ELSIF ( p_x_prod_zone_as_rec.attribute3 IS NULL ) THEN
498 p_x_prod_zone_as_rec.attribute3 := l_old_prod_zone_as_rec.attribute3;
499 END IF;
500
501 IF ( p_x_prod_zone_as_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
502 p_x_prod_zone_as_rec.attribute4 := null;
503 ELSIF ( p_x_prod_zone_as_rec.attribute4 IS NULL ) THEN
504 p_x_prod_zone_as_rec.attribute4 := l_old_prod_zone_as_rec.attribute4;
505 END IF;
506
507 IF ( p_x_prod_zone_as_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
508 p_x_prod_zone_as_rec.attribute5 := null;
509 ELSIF ( p_x_prod_zone_as_rec.attribute5 IS NULL ) THEN
510 p_x_prod_zone_as_rec.attribute5 := l_old_prod_zone_as_rec.attribute5;
511 END IF;
512
513 IF ( p_x_prod_zone_as_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
514 p_x_prod_zone_as_rec.attribute6 := null;
515 ELSIF ( p_x_prod_zone_as_rec.attribute6 IS NULL ) THEN
516 p_x_prod_zone_as_rec.attribute6 := l_old_prod_zone_as_rec.attribute6;
517 END IF;
518
519 IF ( p_x_prod_zone_as_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
520 p_x_prod_zone_as_rec.attribute7 := null;
521 ELSIF ( p_x_prod_zone_as_rec.attribute7 IS NULL ) THEN
522 p_x_prod_zone_as_rec.attribute7 := l_old_prod_zone_as_rec.attribute7;
523 END IF;
524
525 IF ( p_x_prod_zone_as_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
526 p_x_prod_zone_as_rec.attribute8 := null;
527 ELSIF ( p_x_prod_zone_as_rec.attribute8 IS NULL ) THEN
528 p_x_prod_zone_as_rec.attribute8 := l_old_prod_zone_as_rec.attribute8;
529 END IF;
530
531 IF ( p_x_prod_zone_as_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
532 p_x_prod_zone_as_rec.attribute9 := null;
533 ELSIF ( p_x_prod_zone_as_rec.attribute9 IS NULL ) THEN
534 p_x_prod_zone_as_rec.attribute9 := l_old_prod_zone_as_rec.attribute9;
535 END IF;
536
537 IF ( p_x_prod_zone_as_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
538 p_x_prod_zone_as_rec.attribute10 := null;
539 ELSIF ( p_x_prod_zone_as_rec.attribute10 IS NULL ) THEN
540 p_x_prod_zone_as_rec.attribute10 := l_old_prod_zone_as_rec.attribute10;
541 END IF;
542
543 IF ( p_x_prod_zone_as_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
544 p_x_prod_zone_as_rec.attribute11 := null;
545 ELSIF ( p_x_prod_zone_as_rec.attribute11 IS NULL ) THEN
546 p_x_prod_zone_as_rec.attribute11 := l_old_prod_zone_as_rec.attribute11;
547 END IF;
548
549 IF ( p_x_prod_zone_as_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
550 p_x_prod_zone_as_rec.attribute12 := null;
551 ELSIF ( p_x_prod_zone_as_rec.attribute12 IS NULL ) THEN
552 p_x_prod_zone_as_rec.attribute12 := l_old_prod_zone_as_rec.attribute12;
553 END IF;
554
555 IF ( p_x_prod_zone_as_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
556 p_x_prod_zone_as_rec.attribute13 := null;
557 ELSIF ( p_x_prod_zone_as_rec.attribute13 IS NULL ) THEN
558 p_x_prod_zone_as_rec.attribute13 := l_old_prod_zone_as_rec.attribute13;
559 END IF;
560
561 IF ( p_x_prod_zone_as_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
562 p_x_prod_zone_as_rec.attribute14 := null;
563 ELSIF ( p_x_prod_zone_as_rec.attribute14 IS NULL ) THEN
564 p_x_prod_zone_as_rec.attribute14 := l_old_prod_zone_as_rec.attribute14;
565 END IF;
566
567 IF ( p_x_prod_zone_as_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
568 p_x_prod_zone_as_rec.attribute15 := null;
569 ELSIF ( p_x_prod_zone_as_rec.attribute15 IS NULL ) THEN
570 p_x_prod_zone_as_rec.attribute15 := l_old_prod_zone_as_rec.attribute15;
571 END IF;
572
573 END default_unchanged_attributes;
574
575 -- Procedure to validate individual prod_zone_as attributes
576 PROCEDURE validate_attributes
577 (
578 p_prod_zone_as_rec IN prod_zone_as_rec_type,
579 p_associate_flag IN VARCHAR2,
580 x_return_status OUT NOCOPY VARCHAR2
581 )
582 IS
583
584 BEGIN
585 x_return_status := FND_API.G_RET_STS_SUCCESS;
586
587 IF ( p_prod_zone_as_rec.product_type_code IS NULL OR
588 p_prod_zone_as_rec.product_type_code = FND_API.G_MISS_CHAR ) THEN
589 FND_MESSAGE.set_name( 'AHL','AHL_RM_PRODUCT_TYPE_NULL' );
590 FND_MSG_PUB.add;
591 x_return_status := FND_API.G_RET_STS_ERROR;
592 RETURN;
593 END IF;
594
595 IF ( p_prod_zone_as_rec.zone_code IS NULL OR
596 p_prod_zone_as_rec.zone_code = FND_API.G_MISS_CHAR ) THEN
597 FND_MESSAGE.set_name( 'AHL','AHL_RM_ZONE_NULL' );
598 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_prod_zone_as_rec ) );
599 FND_MSG_PUB.add;
600 x_return_status := FND_API.G_RET_STS_ERROR;
601 RETURN;
602 END IF;
603
604 IF ( p_associate_flag = 'S' AND ( p_prod_zone_as_rec.sub_zone_code IS NULL OR
605 p_prod_zone_as_rec.sub_zone_code = FND_API.G_MISS_CHAR )) THEN
606 FND_MESSAGE.set_name( 'AHL','AHL_RM_SUB_ZONE_NULL' );
607 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_prod_zone_as_rec ) );
608 FND_MSG_PUB.add;
609 x_return_status := FND_API.G_RET_STS_ERROR;
610 RETURN;
611 END IF;
612
613 IF ( p_prod_zone_as_rec.dml_operation = 'U') THEN
614 -- Check if the mandatory Object Version Number column contains a null value.
615 IF ( p_prod_zone_as_rec.object_version_number IS NULL OR
616 p_prod_zone_as_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
617 FND_MESSAGE.set_name( 'AHL','AHL_RM_PTZ_OBJ_VERSION_NULL' );
618 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_prod_zone_as_rec ) );
619 FND_MSG_PUB.add;
620 x_return_status := FND_API.G_RET_STS_ERROR;
621 RETURN;
622 END IF;
623
624 -- Check if the mandatory prod_zone_as ID column contains a null value.
625 IF ( p_prod_zone_as_rec.prodtype_zone_id IS NULL OR
626 p_prod_zone_as_rec.prodtype_zone_id = FND_API.G_MISS_NUM ) THEN
627 FND_MESSAGE.set_name( 'AHL','AHL_RM_PRODTYPE_ZONE_ID_NULL' );
628 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_prod_zone_as_rec ) );
629 FND_MSG_PUB.add;
630 x_return_status := FND_API.G_RET_STS_ERROR;
631 RETURN;
632 END IF;
633
634 END IF;
635
636 END validate_attributes;
637
638 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
639 PROCEDURE validate_record
640 (
641 p_prod_zone_as_rec IN prod_zone_as_rec_type,
642 p_associate_flag IN VARCHAR2,
643 x_return_status OUT NOCOPY VARCHAR2
644 )
645 IS
646
647 l_return_status VARCHAR2(1);
648 l_msg_data VARCHAR2(2000);
649 CURSOR get_zone_assoc (c_product_type_code VARCHAR2, c_zone_code VARCHAR2) IS
650 SELECT product_type_code, zone_code
651 FROM ahl_prodtype_zones
652 WHERE product_type_code = c_product_type_code
653 AND zone_code = c_zone_code;
654 l_zone_assoc get_zone_assoc%ROWTYPE;
655
656 BEGIN
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658 IF (p_associate_flag = 'S') THEN
659 OPEN get_zone_assoc(p_prod_zone_as_rec.product_type_code, p_prod_zone_as_rec.zone_code);
660 FETCH get_zone_assoc INTO l_zone_assoc;
661 IF get_zone_assoc%NOTFOUND THEN
662 FND_MESSAGE.set_name( 'AHL','AHL_RM_NO_PRODTYPE_ZONE_AS' );
663 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_prod_zone_as_rec ) );
664 FND_MSG_PUB.add;
665 x_return_status := FND_API.G_RET_STS_ERROR;
666 END IF;
667 CLOSE get_zone_assoc;
668 END IF;
669
670 END validate_record;
671
672 -- Procedure to Perform cross records validation and duplicate checks
673 PROCEDURE validate_records
674 (
675 p_product_type_code IN VARCHAR2,
676 x_return_status OUT NOCOPY VARCHAR2
677 )
678 IS
679
680 CURSOR get_dup_rec ( c_product_type_code VARCHAR2 )
681 IS
682 SELECT zone_code,
683 zone,
684 sub_zone_code,
685 sub_zone
686 FROM AHL_PRODTYPE_ZONES_V
687 WHERE product_type_code = c_product_type_code
688 GROUP BY zone_code,
689 zone,
690 sub_zone_code,
691 sub_zone
692 HAVING count(*) > 1;
693
694 l_prod_zone_as_rec prod_zone_as_rec_type;
695
696 BEGIN
697
698 x_return_status := FND_API.G_RET_STS_SUCCESS;
699 -- Check whether any duplicate prod_zone_as records for the given object_ID
700 OPEN get_dup_rec( p_product_type_code );
701
702 LOOP
703 FETCH get_dup_rec INTO
704 l_prod_zone_as_rec.zone_code,
705 l_prod_zone_as_rec.zone,
706 l_prod_zone_as_rec.sub_zone_code,
707 l_prod_zone_as_rec.sub_zone;
708 EXIT WHEN get_dup_rec%NOTFOUND;
709 END LOOP;
710
711 IF ( get_dup_rec%ROWCOUNT > 0 ) THEN
712 CLOSE get_dup_rec;
713 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_PRODTYPE_ZONE_DUP' );
714 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( l_prod_zone_as_rec ) );
715 FND_MSG_PUB.add;
716 x_return_status := FND_API.G_RET_STS_ERROR;
717 RETURN;
718 END IF;
719
720 CLOSE get_dup_rec;
721
722 END validate_records;
723
724 PROCEDURE process_prod_zone_as
725 (
726 p_api_version IN NUMBER := 1.0,
727 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
728 p_commit IN VARCHAR2 := FND_API.G_FALSE,
729 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
730 p_default IN VARCHAR2 := FND_API.G_FALSE,
731 p_module_type IN VARCHAR2 := NULL,
732 x_return_status OUT NOCOPY VARCHAR2,
733 x_msg_count OUT NOCOPY NUMBER,
734 x_msg_data OUT NOCOPY VARCHAR2,
735 p_associate_flag IN VARCHAR2,
736 p_x_prod_zone_as_tbl IN OUT NOCOPY prod_zone_as_tbl_type
737 )
738 IS
739 l_api_version CONSTANT NUMBER := 1.0;
740 l_return_status VARCHAR2(1);
741 l_msg_count NUMBER;
742 l_prodtype_zone_id NUMBER;
743
744 CURSOR get_prod_zone_rec(c_prodtype_zone_id NUMBER) IS
745 SELECT product_type_code, zone_code, sub_zone_code
746 FROM ahl_prodtype_zones
747 WHERE prodtype_zone_id = c_prodtype_zone_id;
748
749 CURSOR get_route_zone_rec(c_product_type_code VARCHAR2, c_zone_code VARCHAR2) IS
750 SELECT route_id
751 FROM ahl_routes_app_v
752 WHERE product_type_code = c_product_type_code
753 AND zone_code = c_zone_code;
754
755 CURSOR get_route_sub_rec(c_product_type_code VARCHAR2, c_zone_code VARCHAR2,
756 c_sub_zone_code VARCHAR2 ) IS
757 SELECT route_id
758 FROM ahl_routes_app_v
759 WHERE product_type_code = c_product_type_code
760 AND zone_code = c_zone_code
761 AND sub_zone_code = c_sub_zone_code;
762
763 l_get_prod_zone_rec get_prod_zone_rec%ROWTYPE;
764
765 BEGIN
766 -- Initialize API return status to success
767 x_return_status := FND_API.G_RET_STS_SUCCESS;
768
769 -- Standard Start of API savepoint
770 SAVEPOINT process_prod_zone_as_pvt;
771
772 -- Standard call to check for call compatibility.
773 IF NOT FND_API.compatible_api_call
774 (
775 l_api_version,
776 p_api_version,
777 G_API_NAME,
778 G_PKG_NAME
779 )
780 THEN
781 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
782 END IF;
783
784 -- Initialize message list if p_init_msg_list is set to TRUE.
785 IF FND_API.to_boolean( p_init_msg_list ) THEN
786 FND_MSG_PUB.initialize;
787 END IF;
788
789 IF G_DEBUG = 'Y' THEN
790 AHL_DEBUG_PUB.enable_debug;
791 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Begin API' );
792 END IF;
793
794 -- Validate all the inputs of the API
795 validate_api_inputs
796 (
797 p_x_prod_zone_as_tbl,
798 p_associate_flag,
799 l_return_status
800 );
801
802 -- If any severe error occurs, then, abort API.
803 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
804 RAISE FND_API.G_EXC_ERROR;
805 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807 END IF;
808
809 -- If the module type is JSP, then default values for ID columns of LOV attributes
810 IF ( p_module_type = 'JSP' ) THEN
811 FOR i IN 1..p_x_prod_zone_as_tbl.count LOOP
812 IF ( p_x_prod_zone_as_tbl(i).dml_operation <> 'D' ) THEN
813 clear_lov_attribute_ids
814 (
815 p_x_prod_zone_as_tbl(i) -- IN OUT Record with Values and Ids
816 );
817 END IF;
818 END LOOP;
819 END IF;
820
821 -- Convert Values into Ids.
822 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
823 FOR i IN 1..p_x_prod_zone_as_tbl.count LOOP
824 IF ( p_x_prod_zone_as_tbl(i).dml_operation <> 'D' ) THEN
825 convert_values_to_ids
826 (
827 p_x_prod_zone_as_tbl(i) , -- IN OUT Record with Values and Ids
828 l_return_status -- OUT
829 );
830
831 -- If any severe error occurs, then, abort API.
832 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
833 RAISE FND_API.G_EXC_ERROR;
834 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
836 END IF;
837 END IF;
838 END LOOP;
839 END IF;
840
841 IF G_DEBUG = 'Y' THEN
842 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after convert_values_to_ids' );
843 END IF;
844
845 -- Default prod_zone_as attributes.
846 IF FND_API.to_boolean( p_default ) THEN
847 FOR i IN 1..p_x_prod_zone_as_tbl.count LOOP
848 IF ( p_x_prod_zone_as_tbl(i).dml_operation <> 'D' ) THEN
849 default_attributes
850 (
851 p_x_prod_zone_as_tbl(i) -- IN OUT
852 );
853 END IF;
854 END LOOP;
855 END IF;
856
857 IF G_DEBUG = 'Y' THEN
858 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_attributes' );
859 END IF;
860
861 -- Validate all attributes (Item level validation)
862 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
863 FOR i IN 1..p_x_prod_zone_as_tbl.count LOOP
864 IF p_x_prod_zone_as_tbl(i).dml_operation <> 'D' THEN
865 validate_attributes
866 (
867 p_x_prod_zone_as_tbl(i), -- IN
868 p_associate_flag,
869 l_return_status -- OUT
870 );
871 END IF;
872 -- If any severe error occurs, then, abort API.
873 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
874 RAISE FND_API.G_EXC_ERROR;
875 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
876 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
877 END IF;
878 END LOOP;
879 END IF;
880
881 IF G_DEBUG = 'Y' THEN
882 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_attributes' );
883 END IF;
884
885 -- Default missing and unchanged attributes.
886 FOR i IN 1..p_x_prod_zone_as_tbl.count LOOP
887 IF ( p_x_prod_zone_as_tbl(i).dml_operation = 'U' ) THEN
888 default_unchanged_attributes
889 (
890 p_x_prod_zone_as_tbl(i) -- IN OUT
891 );
892 ELSIF ( p_x_prod_zone_as_tbl(i).dml_operation = 'C' ) THEN
893 default_missing_attributes
894 (
895 p_x_prod_zone_as_tbl(i) -- IN OUT
896 );
897 END IF;
898 END LOOP;
899
900 IF G_DEBUG = 'Y' THEN
901 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_unchanged_attributes / default_missing_attributes' );
902 END IF;
903
904 -- Perform cross attribute validation and missing attribute checks (Record level validation)
905
906 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
907 FOR i IN 1..p_x_prod_zone_as_tbl.count LOOP
908 IF ( p_x_prod_zone_as_tbl(i).dml_operation <> 'D' ) THEN
909 validate_record
910 (
911 p_x_prod_zone_as_tbl(i), -- IN
912 p_associate_flag, -- IN
913 l_return_status -- OUT
914 );
915
916 -- If any severe error occurs, then, abort API.
917 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
918 RAISE FND_API.G_EXC_ERROR;
919 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
921 END IF;
922 END IF;
923 END LOOP;
924 END IF;
925
926 IF G_DEBUG = 'Y' THEN
927 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_record' );
928 END IF;
929
930 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
931 l_msg_count := FND_MSG_PUB.count_msg;
932 IF l_msg_count > 0 THEN
933 x_msg_count := l_msg_count;
934 RAISE FND_API.G_EXC_ERROR;
935 END IF;
936
937 -- Perform the DML statement directly.
938 FOR i IN 1..p_x_prod_zone_as_tbl.count LOOP
939 IF ( p_x_prod_zone_as_tbl(i).dml_operation = 'C' ) THEN
940 BEGIN
941 -- Insert the record
942 INSERT INTO AHL_PRODTYPE_ZONES
943 (
944 PRODTYPE_ZONE_ID,
945 OBJECT_VERSION_NUMBER,
946 LAST_UPDATE_DATE,
947 LAST_UPDATED_BY,
948 CREATION_DATE,
949 CREATED_BY,
950 LAST_UPDATE_LOGIN,
951 PRODUCT_TYPE_CODE,
952 ZONE_CODE,
953 SUB_ZONE_CODE,
954 ATTRIBUTE_CATEGORY,
955 ATTRIBUTE1,
956 ATTRIBUTE2,
957 ATTRIBUTE3,
958 ATTRIBUTE4,
959 ATTRIBUTE5,
960 ATTRIBUTE6,
961 ATTRIBUTE7,
962 ATTRIBUTE8,
963 ATTRIBUTE9,
964 ATTRIBUTE10,
965 ATTRIBUTE11,
966 ATTRIBUTE12,
967 ATTRIBUTE13,
968 ATTRIBUTE14,
969 ATTRIBUTE15
970 ) VALUES
971 (
972 AHL_PRODTYPE_ZONES_S.NEXTVAL,
973 p_x_prod_zone_as_tbl(i).object_version_number,
974 p_x_prod_zone_as_tbl(i).last_update_date,
975 p_x_prod_zone_as_tbl(i).last_updated_by,
976 p_x_prod_zone_as_tbl(i).creation_date,
977 p_x_prod_zone_as_tbl(i).created_by,
978 p_x_prod_zone_as_tbl(i).last_update_login,
979 p_x_prod_zone_as_tbl(i).product_type_code,
980 p_x_prod_zone_as_tbl(i).zone_code,
981 p_x_prod_zone_as_tbl(i).sub_zone_code,
982 p_x_prod_zone_as_tbl(i).attribute_category,
983 p_x_prod_zone_as_tbl(i).attribute1,
984 p_x_prod_zone_as_tbl(i).attribute2,
985 p_x_prod_zone_as_tbl(i).attribute3,
986 p_x_prod_zone_as_tbl(i).attribute4,
987 p_x_prod_zone_as_tbl(i).attribute5,
988 p_x_prod_zone_as_tbl(i).attribute6,
989 p_x_prod_zone_as_tbl(i).attribute7,
990 p_x_prod_zone_as_tbl(i).attribute8,
991 p_x_prod_zone_as_tbl(i).attribute9,
992 p_x_prod_zone_as_tbl(i).attribute10,
993 p_x_prod_zone_as_tbl(i).attribute11,
994 p_x_prod_zone_as_tbl(i).attribute12,
995 p_x_prod_zone_as_tbl(i).attribute13,
996 p_x_prod_zone_as_tbl(i).attribute14,
997 p_x_prod_zone_as_tbl(i).attribute15
998 ) RETURNING prodtype_zone_id INTO l_prodtype_zone_id;
999
1000 -- Set OUT values
1001 p_x_prod_zone_as_tbl(i).prodtype_zone_id := l_prodtype_zone_id;
1002
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 IF ( SQLCODE = -1 ) THEN
1006 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_PRODTYPE_ZONE_DUP' );
1007 FND_MESSAGE.set_token( 'RECORD', get_record_identifier(p_x_prod_zone_as_tbl(i) ) );
1008 FND_MSG_PUB.add;
1009 END IF;
1010 END;
1011
1012 ELSIF ( p_x_prod_zone_as_tbl(i).dml_operation = 'U' ) THEN
1013
1014 BEGIN
1015 -- Update the record
1016 UPDATE AHL_PRODTYPE_ZONES SET
1017 object_version_number = object_version_number + 1,
1018 last_update_date = p_x_prod_zone_as_tbl(i).last_update_date,
1019 last_updated_by = p_x_prod_zone_as_tbl(i).last_updated_by,
1020 last_update_login = p_x_prod_zone_as_tbl(i).last_update_login,
1021 product_type_code = p_x_prod_zone_as_tbl(i).product_type_code,
1022 zone_code = p_x_prod_zone_as_tbl(i).zone_code,
1023 sub_zone_code = p_x_prod_zone_as_tbl(i).sub_zone_code,
1024 attribute_category = p_x_prod_zone_as_tbl(i).attribute_category,
1025 attribute1 = p_x_prod_zone_as_tbl(i).attribute1,
1026 attribute2 = p_x_prod_zone_as_tbl(i).attribute2,
1027 attribute3 = p_x_prod_zone_as_tbl(i).attribute3,
1028 attribute4 = p_x_prod_zone_as_tbl(i).attribute4,
1029 attribute5 = p_x_prod_zone_as_tbl(i).attribute5,
1030 attribute6 = p_x_prod_zone_as_tbl(i).attribute6,
1031 attribute7 = p_x_prod_zone_as_tbl(i).attribute7,
1032 attribute8 = p_x_prod_zone_as_tbl(i).attribute8,
1033 attribute9 = p_x_prod_zone_as_tbl(i).attribute9,
1034 attribute10 = p_x_prod_zone_as_tbl(i).attribute10,
1035 attribute11 = p_x_prod_zone_as_tbl(i).attribute11,
1036 attribute12 = p_x_prod_zone_as_tbl(i).attribute12,
1037 attribute13 = p_x_prod_zone_as_tbl(i).attribute13,
1038 attribute14 = p_x_prod_zone_as_tbl(i).attribute14,
1039 attribute15 = p_x_prod_zone_as_tbl(i).attribute15
1040 WHERE prodtype_zone_id = p_x_prod_zone_as_tbl(i).prodtype_zone_id
1041 AND object_version_number = p_x_prod_zone_as_tbl(i).object_version_number;
1042
1043 -- If the record does not exist, then, abort API.
1044 IF ( SQL%ROWCOUNT = 0 ) THEN
1045 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1046 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_prod_zone_as_tbl(i) ) );
1047 FND_MSG_PUB.add;
1048 END IF;
1049
1050 -- Set OUT values
1051 p_x_prod_zone_as_tbl(i).object_version_number := p_x_prod_zone_as_tbl(i).object_version_number + 1;
1052
1053 EXCEPTION
1054 WHEN OTHERS THEN
1055 IF ( SQLCODE = -1 ) THEN
1056 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_PRODTYPE_ZONE_DUP' );
1057 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_prod_zone_as_tbl(i) ) );
1058 FND_MSG_PUB.add;
1059 END IF;
1060 END;
1061
1062 ELSIF ( p_x_prod_zone_as_tbl(i).dml_operation = 'D' ) THEN
1063
1064 OPEN get_prod_zone_rec(p_x_prod_zone_as_tbl(i).prodtype_zone_id);
1065 FETCH get_prod_zone_rec INTO l_get_prod_zone_rec;
1066 IF get_prod_zone_rec%NOTFOUND THEN
1067 FND_MESSAGE.set_name('AHL','AHL_RM_INVALID_PROD_ZONE_REC');
1068 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
1069 FND_MSG_PUB.add;
1070 CLOSE get_prod_zone_rec;
1071 RAISE FND_API.G_EXC_ERROR;
1072 END IF;
1073 CLOSE get_prod_zone_rec;
1074
1075 -- Delete the record
1076 DELETE FROM AHL_PRODTYPE_ZONES
1077 WHERE prodtype_zone_id = p_x_prod_zone_as_tbl(i).prodtype_zone_id
1078 AND object_version_number = p_x_prod_zone_as_tbl(i).object_version_number;
1079
1080 -- If the record does not exist, then, abort API.
1081 IF ( SQL%ROWCOUNT = 0 ) THEN
1082 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1083 FND_MESSAGE.set_token( 'RECORD', TO_CHAR( i ) );
1084 FND_MSG_PUB.add;
1085 ELSE
1086 IF l_get_prod_zone_rec.sub_zone_code IS NULL THEN
1087 DELETE FROM AHL_PRODTYPE_ZONES
1088 WHERE product_type_code = l_get_prod_zone_rec.product_type_code
1089 AND zone_code = l_get_prod_zone_rec.zone_code;
1090 FOR I IN get_route_zone_rec(l_get_prod_zone_rec.product_type_code,
1091 l_get_prod_zone_rec.zone_code) LOOP
1092 UPDATE AHL_ROUTES_B
1093 SET zone_code = NULL,
1094 sub_zone_code = NULL
1095 WHERE route_id = I.route_id;
1096 END LOOP;
1097 ELSE
1098 FOR I IN get_route_sub_rec(l_get_prod_zone_rec.product_type_code,
1099 l_get_prod_zone_rec.zone_code, l_get_prod_zone_rec.sub_zone_code ) LOOP
1100 UPDATE AHL_ROUTES_B
1101 SET sub_zone_code = NULL
1102 WHERE route_id= I.route_id;
1103 END LOOP;
1104 END IF;
1105 END IF;
1106
1107 -- Delete
1108 END IF;
1109 END LOOP;
1110
1111 IF G_DEBUG = 'Y' THEN
1112 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after DML operation' );
1113 END IF;
1114
1115 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1116 l_msg_count := FND_MSG_PUB.count_msg;
1117 IF l_msg_count > 0 THEN
1118 x_msg_count := l_msg_count;
1119 RAISE FND_API.G_EXC_ERROR;
1120 END IF;
1121
1122 -- Perform cross records validations and duplicate records check
1123 validate_records
1124 (
1125 p_x_prod_zone_as_tbl(p_x_prod_zone_as_tbl.FIRST).product_type_code,
1126 l_return_status -- OUT
1127 );
1128
1129 -- If any severe error occurs, then, abort API.
1130 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1131 RAISE FND_API.G_EXC_ERROR;
1132 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1134 END IF;
1135
1136 IF G_DEBUG = 'Y' THEN
1137 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_records' );
1138 END IF;
1139
1140 -- Perform the Commit (if requested)
1141 IF FND_API.to_boolean( p_commit ) THEN
1142 COMMIT WORK;
1143 END IF;
1144
1145 -- Count and Get messages (optional)
1146 FND_MSG_PUB.count_and_get
1147 (
1148 p_encoded => FND_API.G_FALSE,
1149 p_count => x_msg_count,
1150 p_data => x_msg_data
1151 );
1152
1153 -- Disable debug (if enabled)
1154 IF G_DEBUG = 'Y' THEN
1155 AHL_DEBUG_PUB.disable_debug;
1156 END IF;
1157 EXCEPTION
1158 WHEN FND_API.G_EXC_ERROR THEN
1159 ROLLBACK TO process_prod_zone_as_pvt;
1160 x_return_status := FND_API.G_RET_STS_ERROR ;
1161 FND_MSG_PUB.count_and_get
1162 (
1163 p_encoded => FND_API.G_FALSE,
1164 p_count => x_msg_count,
1165 p_data => x_msg_data
1166 );
1167
1168 -- Disable debug (if enabled)
1169 IF G_DEBUG = 'Y' THEN
1170 AHL_DEBUG_PUB.disable_debug;
1171 END IF;
1172
1173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1174 ROLLBACK TO process_prod_zone_as_pvt;
1175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1176 FND_MSG_PUB.count_and_get
1177 (
1178 p_encoded => FND_API.G_FALSE,
1179 p_count => x_msg_count,
1180 p_data => x_msg_data
1181 );
1182
1183 -- Disable debug (if enabled)
1184 IF G_DEBUG = 'Y' THEN
1185 AHL_DEBUG_PUB.disable_debug;
1186 END IF;
1187
1188 WHEN OTHERS THEN
1189 ROLLBACK TO process_prod_zone_as_pvt;
1190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1191 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1192 THEN
1193 FND_MSG_PUB.add_exc_msg
1194 (
1195 p_pkg_name => G_PKG_NAME,
1196 p_procedure_name => G_API_NAME,
1197 p_error_text => SUBSTRB(SQLERRM,1,240)
1198 );
1199 END IF;
1200 FND_MSG_PUB.count_and_get
1201 (
1202 p_encoded => FND_API.G_FALSE,
1203 p_count => x_msg_count,
1204 p_data => x_msg_data
1205 );
1206
1207 -- Disable debug (if enabled)
1208 IF G_DEBUG = 'Y' THEN
1209 AHL_DEBUG_PUB.disable_debug;
1210 END IF;
1211
1212 END process_prod_zone_as;
1213
1214 END AHL_RM_PROD_ZONE_AS_PVT;