[Home] [Help]
PACKAGE BODY: APPS.AHL_RM_ROUTE_PVT
Source
1 PACKAGE BODY AHL_RM_ROUTE_PVT AS
2 /* $Header: AHLVROMB.pls 120.4.12010000.4 2008/12/29 14:20:43 bachandr ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_RM_ROUTE_PVT';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6
7 -- constants for WHO Columns
8 -- Added by balaji as a part of Public API cleanup
9 G_LAST_UPDATE_DATE DATE := SYSDATE;
10 G_LAST_UPDATED_BY NUMBER(15) := FND_GLOBAL.user_id;
11 G_LAST_UPDATE_LOGIN NUMBER(15) := FND_GLOBAL.login_id;
12 G_CREATION_DATE DATE := SYSDATE;
13 G_CREATED_BY NUMBER(15) := FND_GLOBAL.user_id;
14
15 -- Procedure to validate the Inputs of the API
16 PROCEDURE validate_api_inputs
17 (
18 p_route_rec IN route_rec_type,
19 x_return_status OUT NOCOPY VARCHAR2
20 )
21 IS
22
23 l_return_status VARCHAR2(1);
24 l_msg_data VARCHAR2(2000);
25
26 BEGIN
27 x_return_status := FND_API.G_RET_STS_SUCCESS;
28
29 -- Validate DML Operation
30 IF ( p_route_rec.dml_operation IS NULL OR
31 (
32 p_route_rec.dml_operation <> 'U' AND
33 p_route_rec.dml_operation <> 'C'
34 )
35 )
36 THEN
37 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML_REC' );
38 FND_MESSAGE.set_token( 'FIELD', p_route_rec.dml_operation );
39 FND_MSG_PUB.add;
40 x_return_status := FND_API.G_RET_STS_ERROR;
41 RETURN;
42 END IF;
43
44 END validate_api_inputs;
45
46 -- Procedure to Default NULL / G_MISS Values for LOV attributes
47 PROCEDURE clear_lov_attribute_ids
48 (
49 p_x_route_rec IN OUT NOCOPY route_rec_type
50 )
51 IS
52
53 BEGIN
54 IF ( p_x_route_rec.route_type IS NULL ) THEN
55 p_x_route_rec.route_type_code := NULL;
56 ELSIF ( p_x_route_rec.route_type = FND_API.G_MISS_CHAR ) THEN
57 p_x_route_rec.route_type_code := FND_API.G_MISS_CHAR;
58 END IF;
59
60 --bachandr Enigma Phase I changes -- start
61 IF ( p_x_route_rec.model_meaning IS NULL ) THEN
62 p_x_route_rec.model_code := NULL;
63 ELSIF ( p_x_route_rec.model_meaning = FND_API.G_MISS_CHAR ) THEN
64 p_x_route_rec.model_code := FND_API.G_MISS_CHAR;
65 END IF;
66 --bachandr Enigma Phase I changes -- end
67
68 IF ( p_x_route_rec.process IS NULL ) THEN
69 p_x_route_rec.process_code := NULL;
70 ELSIF ( p_x_route_rec.process = FND_API.G_MISS_CHAR ) THEN
71 p_x_route_rec.process_code := FND_API.G_MISS_CHAR;
72 END IF;
73
74 IF ( p_x_route_rec.product_type IS NULL ) THEN
75 p_x_route_rec.product_type_code := NULL;
76 ELSIF ( p_x_route_rec.product_type = FND_API.G_MISS_CHAR ) THEN
77 p_x_route_rec.product_type_code := FND_API.G_MISS_CHAR;
78 END IF;
79
80 IF ( p_x_route_rec.operator_name IS NULL ) THEN
81 p_x_route_rec.operator_party_id := NULL;
82 ELSIF ( p_x_route_rec.operator_name = FND_API.G_MISS_CHAR ) THEN
83 p_x_route_rec.operator_party_id := FND_API.G_MISS_NUM;
84 END IF;
85
86 IF ( p_x_route_rec.zone IS NULL ) THEN
87 p_x_route_rec.zone_code := NULL;
88 ELSIF ( p_x_route_rec.zone = FND_API.G_MISS_CHAR ) THEN
89 p_x_route_rec.zone_code := FND_API.G_MISS_CHAR;
90 END IF;
91
92 IF ( p_x_route_rec.sub_zone IS NULL ) THEN
93 p_x_route_rec.sub_zone_code := NULL;
94 ELSIF ( p_x_route_rec.sub_zone = FND_API.G_MISS_CHAR ) THEN
95 p_x_route_rec.sub_zone_code := FND_API.G_MISS_CHAR;
96 END IF;
97
98 IF ( p_x_route_rec.service_item_number IS NULL ) THEN
99 p_x_route_rec.service_item_id := NULL;
100 p_x_route_rec.service_item_org_id := NULL;
101 ELSIF ( p_x_route_rec.service_item_number = FND_API.G_MISS_CHAR ) THEN
102 p_x_route_rec.service_item_id := FND_API.G_MISS_NUM;
103 p_x_route_rec.service_item_org_id := FND_API.G_MISS_NUM;
104 END IF;
105
106 IF ( p_x_route_rec.accounting_class IS NULL ) THEN
107 p_x_route_rec.accounting_class_code := NULL;
108 p_x_route_rec.accounting_class_org_id := NULL;
109 ELSIF ( p_x_route_rec.accounting_class = FND_API.G_MISS_CHAR ) THEN
110 p_x_route_rec.accounting_class_code := FND_API.G_MISS_CHAR;
111 p_x_route_rec.accounting_class_org_id := FND_API.G_MISS_NUM;
112 END IF;
113
114 IF ( p_x_route_rec.task_template_group IS NULL ) THEN
115 p_x_route_rec.task_template_group_id := NULL;
116 ELSIF ( p_x_route_rec.task_template_group = FND_API.G_MISS_CHAR ) THEN
117 p_x_route_rec.task_template_group_id := FND_API.G_MISS_NUM;
118 END IF;
119
120 IF ( p_x_route_rec.qa_inspection_type_desc IS NULL ) THEN
121 p_x_route_rec.qa_inspection_type := NULL;
122 ELSIF ( p_x_route_rec.qa_inspection_type_desc = FND_API.G_MISS_CHAR ) THEN
123 p_x_route_rec.qa_inspection_type := FND_API.G_MISS_CHAR;
124 END IF;
125
126 IF ( p_x_route_rec.revision_status IS NULL ) THEN
127 p_x_route_rec.revision_status_code := NULL;
128 ELSIF ( p_x_route_rec.revision_status = FND_API.G_MISS_CHAR ) THEN
129 p_x_route_rec.revision_status_code := FND_API.G_MISS_CHAR;
130 END IF;
131
132 END clear_lov_attribute_ids;
133
134 -- Procedure to perform Value to ID conversion for appropriate attributes
135 PROCEDURE convert_values_to_ids
136 (
137 p_x_route_rec IN OUT NOCOPY route_rec_type,
138 x_return_status OUT NOCOPY VARCHAR2
139 )
140 IS
141
142 l_return_status VARCHAR2(1);
143 l_msg_data VARCHAR2(2000);
144
145 BEGIN
146 x_return_status := FND_API.G_RET_STS_SUCCESS;
147
148 -- Convert / Validate Route Type
149 IF ( ( p_x_route_rec.route_type_code IS NOT NULL AND
150 p_x_route_rec.route_type_code <> FND_API.G_MISS_CHAR ) OR
151 ( p_x_route_rec.route_type IS NOT NULL AND
152 p_x_route_rec.route_type <> FND_API.G_MISS_CHAR ) ) THEN
153
154 AHL_RM_ROUTE_UTIL.validate_lookup
155 (
156 x_return_status => l_return_status,
157 x_msg_data => l_msg_data,
158 p_lookup_type => 'AHL_ROUTE_TYPE',
159 p_lookup_meaning => p_x_route_rec.route_type,
160 p_x_lookup_code => p_x_route_rec.route_type_code
161 );
162
163 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
164 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
165 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_ROUTE_TYPE' );
166 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
167 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_ROUTE_TYPES' );
168 ELSE
169 FND_MESSAGE.set_name( 'AHL', l_msg_data );
170 END IF;
171
172 IF ( p_x_route_rec.route_type IS NULL OR
173 p_x_route_rec.route_type = FND_API.G_MISS_CHAR ) THEN
174 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.route_type_code );
175 ELSE
176 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.route_type );
177 END IF;
178
179 FND_MSG_PUB.add;
180 END IF;
181
182 END IF;
183
184 --bachandr Enigma Phase I changes -- start
185 -- Convert / Validate Model
186 IF ( ( p_x_route_rec.model_code IS NOT NULL AND
187 p_x_route_rec.model_code <> FND_API.G_MISS_CHAR ) OR
188 ( p_x_route_rec.model_meaning IS NOT NULL AND
189 p_x_route_rec.model_meaning <> FND_API.G_MISS_CHAR ) ) THEN
190
191 AHL_RM_ROUTE_UTIL.validate_lookup
192 (
193 x_return_status => l_return_status,
194 x_msg_data => l_msg_data,
195 p_lookup_type => 'AHL_ENIGMA_MODEL_CODE',
196 p_lookup_meaning => p_x_route_rec.model_meaning,
197 p_x_lookup_code => p_x_route_rec.model_code
198 );
199
200 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
201 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
202 FND_MESSAGE.set_name( 'AHL', 'AHL_CM_INVALID_MODEL' );
203 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
204 FND_MESSAGE.set_name( 'AHL', 'AHL_CM_TOO_MANY_MODELS' );
205 ELSE
206 FND_MESSAGE.set_name( 'AHL', l_msg_data );
207 END IF;
208
209 IF ( p_x_route_rec.model_meaning IS NULL OR
210 p_x_route_rec.model_meaning = FND_API.G_MISS_CHAR ) THEN
211 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.model_code );
212 ELSE
213 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.model_meaning );
214 END IF;
215
216 FND_MSG_PUB.add;
217 END IF;
218
219 END IF;
220 --bachandr Enigma Phase I changes -- end
221
222 -- Convert / Validate Process
223 IF ( ( p_x_route_rec.process_code IS NOT NULL AND
224 p_x_route_rec.process_code <> FND_API.G_MISS_CHAR ) OR
225 ( p_x_route_rec.process IS NOT NULL AND
226 p_x_route_rec.process <> FND_API.G_MISS_CHAR ) ) THEN
227
228 AHL_RM_ROUTE_UTIL.validate_lookup
229 (
230 x_return_status => l_return_status,
231 x_msg_data => l_msg_data,
232 p_lookup_type => 'AHL_PROCESS_CODE',
233 p_lookup_meaning => p_x_route_rec.process,
234 p_x_lookup_code => p_x_route_rec.process_code
235 );
236
237 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
238 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
239 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_PROCESS' );
240 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
241 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_PROCESSES' );
242 ELSE
243 FND_MESSAGE.set_name( 'AHL', l_msg_data );
244 END IF;
245
246 IF ( p_x_route_rec.process IS NULL OR
247 p_x_route_rec.process = FND_API.G_MISS_CHAR ) THEN
248 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.process_code );
249 ELSE
250 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.process );
251 END IF;
252
253 FND_MSG_PUB.add;
254 END IF;
255
256 END IF;
257
258 -- Convert / Validate Product Type
259 IF ( ( p_x_route_rec.product_type_code IS NOT NULL AND
260 p_x_route_rec.product_type_code <> FND_API.G_MISS_CHAR ) OR
261 ( p_x_route_rec.product_type IS NOT NULL AND
262 p_x_route_rec.product_type <> FND_API.G_MISS_CHAR ) ) THEN
263
264 AHL_RM_ROUTE_UTIL.validate_lookup
265 (
266 x_return_status => l_return_status,
267 x_msg_data => l_msg_data,
268 p_lookup_type => 'ITEM_TYPE',
269 p_lookup_meaning => p_x_route_rec.product_type,
270 p_x_lookup_code => p_x_route_rec.product_type_code
271 );
272
273 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
274 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
275 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_PRODUCT_TYPE' );
276 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
277 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_PRODUCT_TYPES' );
278 ELSE
279 FND_MESSAGE.set_name( 'AHL', l_msg_data );
280 END IF;
281
282 IF ( p_x_route_rec.product_type IS NULL OR
283 p_x_route_rec.product_type = FND_API.G_MISS_CHAR ) THEN
284 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.product_type_code );
285 ELSE
286 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.product_type );
287 END IF;
288
289 FND_MSG_PUB.add;
290 END IF;
291
292 END IF;
293
294 -- Convert / Validate Operator
295 IF ( ( p_x_route_rec.operator_name IS NOT NULL AND
296 p_x_route_rec.operator_name <> FND_API.G_MISS_CHAR ) OR
297 ( p_x_route_rec.operator_party_id IS NOT NULL AND
298 p_x_route_rec.operator_party_id <> FND_API.G_MISS_NUM ) ) THEN
299
300 AHL_RM_ROUTE_UTIL.validate_operator
301 (
302 x_return_status => l_return_status,
303 x_msg_data => l_msg_data,
304 p_operator_name => p_x_route_rec.operator_name,
305 p_x_operator_party_id => p_x_route_rec.operator_party_id
306 );
307
308 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
309 FND_MESSAGE.set_name( 'AHL', l_msg_data );
310
311 IF ( p_x_route_rec.operator_name IS NULL OR
312 p_x_route_rec.operator_name = FND_API.G_MISS_CHAR ) THEN
313 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_route_rec.operator_party_id ) );
314 ELSE
315 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.operator_name );
316 END IF;
317
318 FND_MSG_PUB.add;
319 END IF;
320
321 END IF;
322
323 -- Convert / Validate Zone
324 IF ( ( p_x_route_rec.zone_code IS NOT NULL AND
325 p_x_route_rec.zone_code <> FND_API.G_MISS_CHAR ) OR
326 ( p_x_route_rec.zone IS NOT NULL AND
327 p_x_route_rec.zone <> FND_API.G_MISS_CHAR ) ) THEN
328
329 AHL_RM_ROUTE_UTIL.validate_lookup
330 (
331 x_return_status => l_return_status,
332 x_msg_data => l_msg_data,
333 p_lookup_type => 'AHL_ZONE',
334 p_lookup_meaning => p_x_route_rec.zone,
335 p_x_lookup_code => p_x_route_rec.zone_code
336 );
337
338 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
339 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
340 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_ZONE' );
341 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
342 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_ZONES' );
343 ELSE
344 FND_MESSAGE.set_name( 'AHL', l_msg_data );
345 END IF;
346
347 IF ( p_x_route_rec.zone IS NULL OR
348 p_x_route_rec.zone = FND_API.G_MISS_CHAR ) THEN
349 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.zone_code );
350 ELSE
351 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.zone );
352 END IF;
353
354 FND_MSG_PUB.add;
355 END IF;
356
357 END IF;
358
359 -- Convert / Validate Sub Zone
360 IF ( ( p_x_route_rec.sub_zone_code IS NOT NULL AND
361 p_x_route_rec.sub_zone_code <> FND_API.G_MISS_CHAR ) OR
362 ( p_x_route_rec.sub_zone IS NOT NULL AND
363 p_x_route_rec.sub_zone <> FND_API.G_MISS_CHAR ) ) THEN
364
365 AHL_RM_ROUTE_UTIL.validate_lookup
366 (
367 x_return_status => l_return_status,
368 x_msg_data => l_msg_data,
369 p_lookup_type => 'AHL_SUB_ZONE',
370 p_lookup_meaning => p_x_route_rec.sub_zone,
371 p_x_lookup_code => p_x_route_rec.sub_zone_code
372 );
373
374 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
375 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
376 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_SUB_ZONE' );
377 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
378 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_SUB_ZONES' );
379 ELSE
380 FND_MESSAGE.set_name( 'AHL', l_msg_data );
381 END IF;
382
383 IF ( p_x_route_rec.sub_zone IS NULL OR
384 p_x_route_rec.sub_zone = FND_API.G_MISS_CHAR ) THEN
385 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.sub_zone_code );
386 ELSE
387 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.sub_zone );
388 END IF;
389
390 FND_MSG_PUB.add;
391 END IF;
392
393 END IF;
394
395 -- Convert / Validate Service Item
396 IF ( ( p_x_route_rec.service_item_number IS NOT NULL AND
397 p_x_route_rec.service_item_number <> FND_API.G_MISS_CHAR ) OR
398 ( p_x_route_rec.service_item_id IS NOT NULL AND
399 p_x_route_rec.service_item_id <> FND_API.G_MISS_NUM AND
400 p_x_route_rec.service_item_org_id IS NOT NULL AND
401 p_x_route_rec.service_item_org_id <> FND_API.G_MISS_NUM ) ) THEN
402
403 AHL_RM_ROUTE_UTIL.validate_service_item
404 (
405 x_return_status => l_return_status,
406 x_msg_data => l_msg_data,
407 p_item_number => p_x_route_rec.service_item_number,
408 p_x_inventory_item_id => p_x_route_rec.service_item_id,
409 p_x_inventory_org_id => p_x_route_rec.service_item_org_id
410 );
411
412 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
413 FND_MESSAGE.set_name( 'AHL', l_msg_data );
414
415 IF ( p_x_route_rec.service_item_number IS NULL OR
416 p_x_route_rec.service_item_number = FND_API.G_MISS_CHAR ) THEN
417 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_route_rec.service_item_id ) || TO_CHAR( p_x_route_rec.service_item_org_id ) );
418 ELSE
419 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.service_item_number );
420 END IF;
421
422 FND_MSG_PUB.add;
423 END IF;
424
425 END IF;
426
427 -- Convert / Validate Accounting Class
428 IF ( ( p_x_route_rec.accounting_class_code IS NOT NULL AND
429 p_x_route_rec.accounting_class_code <> FND_API.G_MISS_CHAR AND
430 p_x_route_rec.accounting_class_org_id IS NOT NULL AND
431 p_x_route_rec.accounting_class_org_id <> FND_API.G_MISS_NUM ) OR
432 ( p_x_route_rec.accounting_class IS NOT NULL AND
433 p_x_route_rec.accounting_class <> FND_API.G_MISS_CHAR ) ) THEN
434
435 AHL_RM_ROUTE_UTIL.validate_accounting_class
436 (
437 x_return_status => l_return_status,
438 x_msg_data => l_msg_data,
439 p_accounting_class => p_x_route_rec.accounting_class,
440 p_x_accounting_class_code => p_x_route_rec.accounting_class_code,
441 p_x_accounting_class_org_id => p_x_route_rec.accounting_class_org_id
442 );
443
444 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
445 FND_MESSAGE.set_name( 'AHL', l_msg_data );
446
447 IF ( p_x_route_rec.accounting_class IS NULL OR
448 p_x_route_rec.accounting_class = FND_API.G_MISS_CHAR ) THEN
449 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.accounting_class_code || '-' || TO_CHAR( p_x_route_rec.accounting_class_org_id ) );
450 ELSE
451 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.accounting_class );
452 END IF;
453
454 FND_MSG_PUB.add;
455 END IF;
456
457 END IF;
458
459 -- Convert / Validate Task Template Group
460 IF ( ( p_x_route_rec.task_template_group IS NOT NULL AND
461 p_x_route_rec.task_template_group <> FND_API.G_MISS_CHAR ) OR
462 ( p_x_route_rec.task_template_group_id IS NOT NULL AND
463 p_x_route_rec.task_template_group_id <> FND_API.G_MISS_NUM ) ) THEN
464
465 AHL_RM_ROUTE_UTIL.validate_task_template_group
466 (
467 x_return_status => l_return_status,
468 x_msg_data => l_msg_data,
469 p_task_template_group => p_x_route_rec.task_template_group,
470 p_x_task_template_group_id => p_x_route_rec.task_template_group_id
471 );
472
473 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
474 FND_MESSAGE.set_name( 'AHL', l_msg_data );
475
476 IF ( p_x_route_rec.task_template_group IS NULL OR
477 p_x_route_rec.task_template_group = FND_API.G_MISS_CHAR ) THEN
478 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_route_rec.task_template_group_id ) );
479 ELSE
480 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.task_template_group );
481 END IF;
482
483 FND_MSG_PUB.add;
484 END IF;
485
486 END IF;
487
488 -- Convert / Validate QA Plan
489 IF ( ( p_x_route_rec.qa_inspection_type_desc IS NOT NULL AND
490 p_x_route_rec.qa_inspection_type_desc <> FND_API.G_MISS_CHAR ) OR
491 ( p_x_route_rec.qa_inspection_type IS NOT NULL AND
492 p_x_route_rec.qa_inspection_type <> FND_API.G_MISS_CHAR ) ) THEN
493
494 AHL_RM_ROUTE_UTIL.validate_qa_inspection_type
495 (
496 x_return_status => l_return_status,
497 x_msg_data => l_msg_data,
498 p_qa_inspection_type_desc => p_x_route_rec.qa_inspection_type_desc,
499 p_x_qa_inspection_type => p_x_route_rec.qa_inspection_type
500 );
501
502 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
503 FND_MESSAGE.set_name( 'AHL', l_msg_data );
504
505 IF ( p_x_route_rec.qa_inspection_type_desc IS NULL OR
506 p_x_route_rec.qa_inspection_type_desc = FND_API.G_MISS_CHAR ) THEN
507 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.qa_inspection_type );
508 ELSE
509 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.qa_inspection_type_desc );
510 END IF;
511
512 FND_MSG_PUB.add;
513 END IF;
514
515 END IF;
516
517 -- Convert / Validate Revision Status
518 IF ( ( p_x_route_rec.revision_status_code IS NOT NULL AND
519 p_x_route_rec.revision_status_code <> FND_API.G_MISS_CHAR ) OR
520 ( p_x_route_rec.revision_status IS NOT NULL AND
521 p_x_route_rec.revision_status <> FND_API.G_MISS_CHAR ) ) THEN
522
523 AHL_RM_ROUTE_UTIL.validate_lookup
524 (
525 x_return_status => l_return_status,
526 x_msg_data => l_msg_data,
527 p_lookup_type => 'AHL_REVISION_STATUS',
528 p_lookup_meaning => p_x_route_rec.revision_status,
529 p_x_lookup_code => p_x_route_rec.revision_status_code
530 );
531
532 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
533 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
534 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_STATUS' );
535 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
536 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_STATUSES' );
537 ELSE
538 FND_MESSAGE.set_name( 'AHL', l_msg_data );
539 END IF;
540
541 IF ( p_x_route_rec.revision_status IS NULL OR
542 p_x_route_rec.revision_status = FND_API.G_MISS_CHAR ) THEN
543 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.revision_status_code );
544 ELSE
545 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.revision_status );
546 END IF;
547
548 FND_MSG_PUB.add;
549 END IF;
550
551 END IF;
552
553 -- pdoki added for Bug 6504159
554 -- Convert / Validate unit receipt update
555 IF ( ( p_x_route_rec.unit_receipt_update_flag IS NOT NULL AND
556 p_x_route_rec.unit_receipt_update_flag <> FND_API.G_MISS_CHAR ) OR
557 ( p_x_route_rec.unit_receipt_update IS NOT NULL AND
558 p_x_route_rec.unit_receipt_update <> FND_API.G_MISS_CHAR ) ) THEN
559
560 AHL_RM_ROUTE_UTIL.validate_lookup
561 (
562 x_return_status => l_return_status,
563 x_msg_data => l_msg_data,
564 p_lookup_type => 'AHL_YES_NO_TYPE',
565 p_lookup_meaning => p_x_route_rec.unit_receipt_update,
566 p_x_lookup_code => p_x_route_rec.unit_receipt_update_flag
567 );
568
569 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
570 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
571 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_UNIT_RECEIPT' );
572 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
573 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_UNIT_RECEIPTS' );
574 ELSE
575 FND_MESSAGE.set_name( 'AHL', l_msg_data );
576 END IF;
577
578 IF ( p_x_route_rec.unit_receipt_update IS NULL OR
579 p_x_route_rec.unit_receipt_update = FND_API.G_MISS_CHAR ) THEN
580 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.unit_receipt_update_flag );
581 ELSE
582 FND_MESSAGE.set_token( 'FIELD', p_x_route_rec.unit_receipt_update );
583 END IF;
584 FND_MSG_PUB.add;
585 END IF;
586
587 END IF;
588 END convert_values_to_ids;
589
590 -- Procedure to add Default values for route attributes
591 -- Balaji removed it as a part of public API cleanup as this defaulting logic should not be bound by p_default value. Instead the logic is moved to DML.
592 /*
593 PROCEDURE default_attributes
594 (
595 p_x_route_rec IN OUT NOCOPY route_rec_type
596 )
597 IS
598
599 BEGIN
600
601 p_x_route_rec.last_update_date := SYSDATE;
602 p_x_route_rec.last_updated_by := FND_GLOBAL.user_id;
603 p_x_route_rec.last_update_login := FND_GLOBAL.login_id;
604
605 IF ( p_x_route_rec.dml_operation = 'C' ) THEN
606 p_x_route_rec.revision_status_code := 'DRAFT';
607 p_x_route_rec.object_version_number := 1;
608 p_x_route_rec.revision_number := 1;
609 p_x_route_rec.creation_date := SYSDATE;
610 p_x_route_rec.created_by := FND_GLOBAL.user_id;
611 END IF;
612
613 END default_attributes;
614 */
615 -- Procedure to add Default values for missing attributes (CREATE)
616 PROCEDURE default_missing_attributes
617 (
618 p_x_route_rec IN OUT NOCOPY route_rec_type
619 )
620 IS
621
622 BEGIN
623
624 -- Convert G_MISS values to NULL
625 IF ( p_x_route_rec.route_type_code = FND_API.G_MISS_CHAR ) THEN
626 p_x_route_rec.route_type_code := null;
627 END IF;
628
629 IF ( p_x_route_rec.route_type = FND_API.G_MISS_CHAR ) THEN
630 p_x_route_rec.route_type := null;
631 END IF;
632
633 IF ( p_x_route_rec.process_code = FND_API.G_MISS_CHAR ) THEN
634 p_x_route_rec.process_code := null;
635 END IF;
636
637 IF ( p_x_route_rec.process = FND_API.G_MISS_CHAR ) THEN
638 p_x_route_rec.process := null;
639 END IF;
640
641 IF ( p_x_route_rec.product_type_code = FND_API.G_MISS_CHAR ) THEN
642 p_x_route_rec.product_type_code := null;
643 END IF;
644
645 IF ( p_x_route_rec.product_type = FND_API.G_MISS_CHAR ) THEN
646 p_x_route_rec.product_type := null;
647 END IF;
648
649 --bachandr Enigma Phase I changes -- start
650 -- Default the model code and meaning to null only
651 -- when the route in a non-Enigma route.
652
653 IF ( p_x_route_rec.enigma_doc_id IS NULL OR p_x_route_rec.enigma_doc_id = FND_API.G_MISS_CHAR ) THEN
654 IF ( p_x_route_rec.model_code = FND_API.G_MISS_CHAR ) THEN
655 p_x_route_rec.model_code := null;
656 END IF;
657
658 IF ( p_x_route_rec.model_meaning = FND_API.G_MISS_CHAR ) THEN
659 p_x_route_rec.model_meaning := null;
660 END IF;
661 END IF;
662
663 IF ( p_x_route_rec.time_span = FND_API.G_MISS_NUM ) THEN
664 p_x_route_rec.time_span := null;
665 END IF;
666
667 --bachandr Enigma Phase I changes -- end
668
669 IF ( p_x_route_rec.operator_party_id = FND_API.G_MISS_NUM ) THEN
670 p_x_route_rec.operator_party_id := null;
671 END IF;
672
673 IF ( p_x_route_rec.operator_name = FND_API.G_MISS_CHAR ) THEN
674 p_x_route_rec.operator_name := null;
675 END IF;
676
677 IF ( p_x_route_rec.zone_code = FND_API.G_MISS_CHAR ) THEN
678 p_x_route_rec.zone_code := null;
679 END IF;
680
681 IF ( p_x_route_rec.zone = FND_API.G_MISS_CHAR ) THEN
682 p_x_route_rec.zone := null;
683 END IF;
684
685 IF ( p_x_route_rec.sub_zone_code = FND_API.G_MISS_CHAR ) THEN
686 p_x_route_rec.sub_zone_code := null;
687 END IF;
688
689 IF ( p_x_route_rec.sub_zone = FND_API.G_MISS_CHAR ) THEN
690 p_x_route_rec.sub_zone := null;
691 END IF;
692
693 IF ( p_x_route_rec.service_item_id = FND_API.G_MISS_NUM ) THEN
694 p_x_route_rec.service_item_id := null;
695 END IF;
696
697 IF ( p_x_route_rec.service_item_org_id = FND_API.G_MISS_NUM ) THEN
698 p_x_route_rec.service_item_org_id := null;
699 END IF;
700
701 IF ( p_x_route_rec.service_item_number = FND_API.G_MISS_CHAR ) THEN
702 p_x_route_rec.service_item_number := null;
703 END IF;
704
705 IF ( p_x_route_rec.accounting_class_code = FND_API.G_MISS_CHAR ) THEN
706 p_x_route_rec.accounting_class_code := null;
707 END IF;
708
709 IF ( p_x_route_rec.accounting_class_org_id = FND_API.G_MISS_NUM ) THEN
710 p_x_route_rec.accounting_class_org_id := null;
711 END IF;
712
713 IF ( p_x_route_rec.accounting_class = FND_API.G_MISS_CHAR ) THEN
714 p_x_route_rec.accounting_class := null;
715 END IF;
716
717 IF ( p_x_route_rec.task_template_group_id = FND_API.G_MISS_NUM ) THEN
718 p_x_route_rec.task_template_group_id := null;
719 END IF;
720
721 IF ( p_x_route_rec.task_template_group = FND_API.G_MISS_CHAR ) THEN
722 p_x_route_rec.task_template_group := null;
723 END IF;
724
725 IF ( p_x_route_rec.qa_inspection_type = FND_API.G_MISS_CHAR ) THEN
726 p_x_route_rec.qa_inspection_type := null;
727 END IF;
728
729 IF ( p_x_route_rec.qa_inspection_type_desc = FND_API.G_MISS_CHAR ) THEN
730 p_x_route_rec.qa_inspection_type_desc := null;
731 END IF;
732
733 IF ( p_x_route_rec.remarks = FND_API.G_MISS_CHAR ) THEN
734 p_x_route_rec.remarks := null;
735 END IF;
736
737 IF ( p_x_route_rec.revision_notes = FND_API.G_MISS_CHAR ) THEN
738 p_x_route_rec.revision_notes := null;
739 END IF;
740
741 IF ( p_x_route_rec.segment1 = FND_API.G_MISS_CHAR ) THEN
742 p_x_route_rec.segment1 := null;
743 END IF;
744
745 IF ( p_x_route_rec.segment2 = FND_API.G_MISS_CHAR ) THEN
746 p_x_route_rec.segment2 := null;
747 END IF;
748
749 IF ( p_x_route_rec.segment3 = FND_API.G_MISS_CHAR ) THEN
750 p_x_route_rec.segment3 := null;
751 END IF;
752
753 IF ( p_x_route_rec.segment4 = FND_API.G_MISS_CHAR ) THEN
754 p_x_route_rec.segment4 := null;
755 END IF;
756
757 IF ( p_x_route_rec.segment5 = FND_API.G_MISS_CHAR ) THEN
758 p_x_route_rec.segment5 := null;
759 END IF;
760
761 IF ( p_x_route_rec.segment6 = FND_API.G_MISS_CHAR ) THEN
762 p_x_route_rec.segment6 := null;
763 END IF;
764
765 IF ( p_x_route_rec.segment7 = FND_API.G_MISS_CHAR ) THEN
766 p_x_route_rec.segment7 := null;
767 END IF;
768
769 IF ( p_x_route_rec.segment8 = FND_API.G_MISS_CHAR ) THEN
770 p_x_route_rec.segment8 := null;
771 END IF;
772
773 IF ( p_x_route_rec.segment9 = FND_API.G_MISS_CHAR ) THEN
774 p_x_route_rec.segment9 := null;
775 END IF;
776
777 IF ( p_x_route_rec.segment10 = FND_API.G_MISS_CHAR ) THEN
778 p_x_route_rec.segment10 := null;
779 END IF;
780
781 IF ( p_x_route_rec.segment11 = FND_API.G_MISS_CHAR ) THEN
782 p_x_route_rec.segment11 := null;
783 END IF;
784
785 IF ( p_x_route_rec.segment12 = FND_API.G_MISS_CHAR ) THEN
786 p_x_route_rec.segment12 := null;
787 END IF;
788
789 IF ( p_x_route_rec.segment13 = FND_API.G_MISS_CHAR ) THEN
790 p_x_route_rec.segment13 := null;
791 END IF;
792
793 IF ( p_x_route_rec.segment14 = FND_API.G_MISS_CHAR ) THEN
794 p_x_route_rec.segment14 := null;
795 END IF;
796
797 IF ( p_x_route_rec.segment15 = FND_API.G_MISS_CHAR ) THEN
798 p_x_route_rec.segment15 := null;
799 END IF;
800
801 IF ( p_x_route_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
802 p_x_route_rec.attribute_category := null;
803 END IF;
804
805 IF ( p_x_route_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
806 p_x_route_rec.attribute1 := null;
807 END IF;
808
809 IF ( p_x_route_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
810 p_x_route_rec.attribute2 := null;
811 END IF;
812
813 IF ( p_x_route_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
814 p_x_route_rec.attribute3 := null;
815 END IF;
816
817 IF ( p_x_route_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
818 p_x_route_rec.attribute4 := null;
819 END IF;
820
821 IF ( p_x_route_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
822 p_x_route_rec.attribute5 := null;
823 END IF;
824
825 IF ( p_x_route_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
826 p_x_route_rec.attribute6 := null;
827 END IF;
828
829 IF ( p_x_route_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
830 p_x_route_rec.attribute7 := null;
831 END IF;
832
833 IF ( p_x_route_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
834 p_x_route_rec.attribute8 := null;
835 END IF;
836
837 IF ( p_x_route_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
838 p_x_route_rec.attribute9 := null;
839 END IF;
840
841 IF ( p_x_route_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
842 p_x_route_rec.attribute10 := null;
843 END IF;
844
845 IF ( p_x_route_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
846 p_x_route_rec.attribute11 := null;
847 END IF;
848
849 IF ( p_x_route_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
850 p_x_route_rec.attribute12 := null;
851 END IF;
852
853 IF ( p_x_route_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
854 p_x_route_rec.attribute13 := null;
855 END IF;
856
857 IF ( p_x_route_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
858 p_x_route_rec.attribute14 := null;
859 END IF;
860
861 IF ( p_x_route_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
862 p_x_route_rec.attribute15 := null;
863 END IF;
864
865 --pdoki added for Bug 6504159
866 IF ( p_x_route_rec.unit_receipt_update_flag = FND_API.G_MISS_CHAR ) THEN
867 p_x_route_rec.unit_receipt_update_flag := 'N';
868 END IF;
869 END default_missing_attributes;
870
871 -- Procedure to get the Route Record for a given route_id
872 PROCEDURE get_route_record
873 (
874 x_return_status OUT NOCOPY VARCHAR2,
875 x_msg_data OUT NOCOPY VARCHAR2,
876 p_route_id IN NUMBER,
877 p_object_version_number IN NUMBER,
878 p_x_route_rec IN OUT NOCOPY route_rec_type
879 )
880 IS
881
882 CURSOR get_old_rec ( c_route_id NUMBER )
883 IS
884 SELECT route_no,
885 title,
886 route_type_code,
887 route_type,
888 process_code,
889 process,
890 product_type_code,
891 product_type,
892 --bachandr Enigma Phase I changes -- start
893 model_code,
894 model_meaning,
895 enigma_doc_id,
896 enigma_route_id,
897 enigma_publish_date,
898 file_id,
899 --bachandr Enigma Phase I changes -- end
900 operator_party_id,
901 operator_name,
902 zone_code,
903 zone,
904 sub_zone_code,
905 sub_zone,
906 service_item_id,
907 service_item_org_id,
908 service_item_number,
909 accounting_class_code,
910 accounting_class_org_id,
911 accounting_class,
912 task_template_group_id,
913 task_template_group,
914 qa_inspection_type,
915 qa_inspection_type_desc,
916 time_span,
917 start_date_active,
918 end_date_active,
919 revision_number,
920 revision_status_code,
921 revision_status,
922 unit_receipt_update_flag, --pdoki Bug 6504159.
923 unit_receipt_update, --pdoki Bug 6504159.
924 remarks,
925 segment1,
926 segment2,
927 segment3,
928 segment4,
929 segment5,
930 segment6,
931 segment7,
932 segment8,
933 segment9,
934 segment10,
935 segment11,
936 segment12,
937 segment13,
938 segment14,
939 segment15,
940 attribute_category,
941 attribute1,
942 attribute2,
943 attribute3,
944 attribute4,
945 attribute5,
946 attribute6,
947 attribute7,
948 attribute8,
949 attribute9,
950 attribute10,
951 attribute11,
952 attribute12,
953 attribute13,
954 attribute14,
955 attribute15
956 FROM AHL_ROUTES_V
957 WHERE route_id = c_route_id;
958
959 BEGIN
960 x_return_status := FND_API.G_RET_STS_SUCCESS;
961
962 -- Get the old record from AHL_ROUTES_V.
963 OPEN get_old_rec( p_route_id );
964
965 FETCH get_old_rec INTO
966 p_x_route_rec.route_no,
967 p_x_route_rec.title,
968 p_x_route_rec.route_type_code,
969 p_x_route_rec.route_type,
970 p_x_route_rec.process_code,
971 p_x_route_rec.process,
972 p_x_route_rec.product_type_code,
973 p_x_route_rec.product_type,
974 --bachandr Enigma Phase I changes -- start
975 p_x_route_rec.model_code,
976 p_x_route_rec.model_meaning,
977 p_x_route_rec.enigma_doc_id,
978 p_x_route_rec.enigma_route_id,
979 p_x_route_rec.enigma_publish_date,
980 p_x_route_rec.file_id,
981 --bachandr Enigma Phase I changes -- end
982 p_x_route_rec.operator_party_id,
983 p_x_route_rec.operator_name,
984 p_x_route_rec.zone_code,
985 p_x_route_rec.zone,
986 p_x_route_rec.sub_zone_code,
987 p_x_route_rec.sub_zone,
988 p_x_route_rec.service_item_id,
989 p_x_route_rec.service_item_org_id,
990 p_x_route_rec.service_item_number,
991 p_x_route_rec.accounting_class_code,
992 p_x_route_rec.accounting_class_org_id,
993 p_x_route_rec.accounting_class,
994 p_x_route_rec.task_template_group_id,
995 p_x_route_rec.task_template_group,
996 p_x_route_rec.qa_inspection_type,
997 p_x_route_rec.qa_inspection_type_desc,
998 p_x_route_rec.time_span,
999 p_x_route_rec.active_start_date,
1000 p_x_route_rec.active_end_date,
1001 p_x_route_rec.revision_number,
1002 p_x_route_rec.revision_status_code,
1003 p_x_route_rec.revision_status,
1004 p_x_route_rec.unit_receipt_update_flag, --pdoki Bug 6504159.
1005 p_x_route_rec.unit_receipt_update, --pdoki Bug 6504159.
1006 p_x_route_rec.remarks,
1007 p_x_route_rec.segment1,
1008 p_x_route_rec.segment2,
1009 p_x_route_rec.segment3,
1010 p_x_route_rec.segment4,
1011 p_x_route_rec.segment5,
1012 p_x_route_rec.segment6,
1013 p_x_route_rec.segment7,
1014 p_x_route_rec.segment8,
1015 p_x_route_rec.segment9,
1016 p_x_route_rec.segment10,
1017 p_x_route_rec.segment11,
1018 p_x_route_rec.segment12,
1019 p_x_route_rec.segment13,
1020 p_x_route_rec.segment14,
1021 p_x_route_rec.segment15,
1022 p_x_route_rec.attribute_category,
1023 p_x_route_rec.attribute1,
1024 p_x_route_rec.attribute2,
1025 p_x_route_rec.attribute3,
1026 p_x_route_rec.attribute4,
1027 p_x_route_rec.attribute5,
1028 p_x_route_rec.attribute6,
1029 p_x_route_rec.attribute7,
1030 p_x_route_rec.attribute8,
1031 p_x_route_rec.attribute9,
1032 p_x_route_rec.attribute10,
1033 p_x_route_rec.attribute11,
1034 p_x_route_rec.attribute12,
1035 p_x_route_rec.attribute13,
1036 p_x_route_rec.attribute14,
1037 p_x_route_rec.attribute15;
1038
1039 IF ( get_old_rec%NOTFOUND ) THEN
1040 x_msg_data := 'AHL_RM_INVALID_ROUTE';
1041 x_return_status := FND_API.G_RET_STS_ERROR;
1042 END IF;
1043
1044 IF ( p_x_route_rec.object_version_number <> p_object_version_number ) THEN
1045 x_msg_data := 'AHL_COM_RECORD_CHANGED';
1046 x_return_status := FND_API.G_RET_STS_ERROR;
1047 END IF;
1048
1049 CLOSE get_old_rec;
1050
1051 END get_route_record;
1052
1053 -- Procedure to add Default values for unchanged attributes (UPDATE)
1054 PROCEDURE default_unchanged_attributes
1055 (
1056 p_x_route_rec IN OUT NOCOPY route_rec_type
1057 )
1058 IS
1059
1060 l_old_route_rec route_rec_type;
1061 l_read_only_flag VARCHAR2(1);
1062 l_msg_data VARCHAR2(2000);
1063 l_return_status VARCHAR2(1);
1064
1065 BEGIN
1066
1067 get_route_record
1068 (
1069 x_return_status => l_return_status,
1070 x_msg_data => l_msg_data,
1071 p_route_id => p_x_route_rec.route_id,
1072 p_object_version_number => p_x_route_rec.object_version_number,
1073 p_x_route_rec => l_old_route_rec
1074 );
1075
1076 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1077 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1078 FND_MSG_PUB.add;
1079 RAISE FND_API.G_EXC_ERROR;
1080 END IF;
1081
1082 -- Convert G_MISS values to NULL and NULL values to Old values
1083 IF ( p_x_route_rec.revision_status_code IS NULL ) THEN
1084 p_x_route_rec.revision_status_code := l_old_route_rec.revision_status_code;
1085
1086 IF ( p_x_route_rec.revision_status_code = 'APPROVAL_REJECTED' ) THEN
1087 p_x_route_rec.revision_status_code := 'DRAFT';
1088 END IF;
1089 -- Condition added in 11.5.10.
1090 ELSIF p_x_route_rec.revision_status_code <> l_old_route_rec.revision_status_code THEN
1091 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_STATUS_RO' );
1092 FND_MSG_PUB.add;
1093 END IF;
1094
1095 IF ( p_x_route_rec.revision_status IS NULL ) THEN
1096 p_x_route_rec.revision_status := l_old_route_rec.revision_status;
1097 ELSIF p_x_route_rec.revision_status <> l_old_route_rec.revision_status THEN
1098 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_STATUS_RO' );
1099 FND_MSG_PUB.add;
1100 END IF;
1101
1102 --bachandr Enigma Phase I changes -- start
1103 -- Do the following only when the route is created from Enigma
1104 -- Check if the model code is null and if it is then default it to the DB value
1105 -- If it is not null and it does not match with the value in the DB then throw an error
1106 IF ( l_old_route_rec.enigma_doc_id IS NOT NULL AND p_x_route_rec.model_code IS NULL ) THEN
1107 p_x_route_rec.model_code := l_old_route_rec.model_code;
1108 ELSIF ( l_old_route_rec.enigma_doc_id IS NOT NULL AND p_x_route_rec.model_code <> l_old_route_rec.model_code ) THEN
1109 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_MODEL_RO' );
1110 FND_MSG_PUB.add;
1111 END IF;
1112
1113 -- Do the following only when the route is created from Enigma
1114 -- Check if the model meaning is null and if it is then default it to the DB value
1115 -- If it is not null / Enigma Route it does not match with the value in the DB then throw an error
1116 IF ( l_old_route_rec.enigma_doc_id IS NOT NULL AND p_x_route_rec.model_meaning IS NULL ) THEN
1117 p_x_route_rec.model_meaning := l_old_route_rec.model_meaning;
1118 ELSIF ( l_old_route_rec.enigma_doc_id IS NOT NULL AND p_x_route_rec.model_meaning <> l_old_route_rec.model_meaning ) THEN
1119 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_MODEL_RO' );
1120 FND_MSG_PUB.add;
1121 END IF;
1122
1123
1124 IF ( l_old_route_rec.enigma_doc_id IS NOT NULL AND p_x_route_rec.file_id IS NULL ) THEN
1125 p_x_route_rec.file_id := l_old_route_rec.file_id;
1126 ELSIF ( p_x_route_rec.file_id = FND_API.G_MISS_NUM) THEN
1127 p_x_route_rec.file_id := null;
1128 END IF;
1129 --bachandr Enigma Phase I changes -- end
1130
1131 IF ( p_x_route_rec.revision_number IS NULL ) THEN
1132 p_x_route_rec.revision_number := l_old_route_rec.revision_number;
1133 ELSIF p_x_route_rec.revision_number <> l_old_route_rec.revision_number THEN
1134 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_REVISION_RO' );
1135 FND_MSG_PUB.add;
1136 END IF;
1137
1138 IF ( p_x_route_rec.route_no IS NULL ) THEN
1139 p_x_route_rec.route_no := l_old_route_rec.route_no;
1140 ELSIF p_x_route_rec.route_no <> l_old_route_rec.route_no THEN
1141 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_NO_RO' );
1142 FND_MSG_PUB.add;
1143 END IF;
1144
1145 IF ( p_x_route_rec.revision_status_code = 'DRAFT' ) THEN
1146 l_read_only_flag := 'N';
1147 ELSE
1148 l_read_only_flag := 'Y';
1149 END IF;
1150
1151 IF ( p_x_route_rec.title IS NULL ) THEN
1152 p_x_route_rec.title := l_old_route_rec.title;
1153 ELSE
1154 IF ( l_read_only_flag = 'Y' ) THEN
1155 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_TITLE_RO' );
1156 FND_MSG_PUB.add;
1157 END IF;
1158 END IF;
1159
1160 IF ( p_x_route_rec.active_start_date IS NULL ) THEN
1161 p_x_route_rec.active_start_date := l_old_route_rec.active_start_date;
1162 ELSE
1163 IF ( l_read_only_flag = 'Y' ) THEN
1164 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ST_DATE_RO' );
1165 FND_MSG_PUB.add;
1166 ELSIF ( p_x_route_rec.active_start_date = FND_API.G_MISS_DATE ) THEN
1167 p_x_route_rec.active_start_date := NULL;
1168 END IF;
1169 END IF;
1170
1171 IF ( p_x_route_rec.active_end_date IS NULL ) THEN
1172 p_x_route_rec.active_end_date := l_old_route_rec.active_end_date;
1173 ELSE
1174 IF ( l_read_only_flag = 'Y' ) THEN
1175 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_END_DATE_RO' );
1176 FND_MSG_PUB.add;
1177 END IF;
1178 END IF;
1179
1180 IF ( p_x_route_rec.route_type_code IS NULL ) THEN
1181 p_x_route_rec.route_type_code := l_old_route_rec.route_type_code;
1182 ELSIF ( p_x_route_rec.route_type_code = FND_API.G_MISS_CHAR ) THEN
1183 p_x_route_rec.route_type_code := null;
1184 END IF;
1185
1186 IF ( p_x_route_rec.route_type IS NULL ) THEN
1187 p_x_route_rec.route_type := l_old_route_rec.route_type;
1188 ELSIF ( p_x_route_rec.route_type = FND_API.G_MISS_CHAR ) THEN
1189 p_x_route_rec.route_type := null;
1190 END IF;
1191 --pdoki added for Bug 6504159
1192 IF ( p_x_route_rec.unit_receipt_update_flag IS NULL ) THEN
1193 p_x_route_rec.unit_receipt_update_flag := l_old_route_rec.unit_receipt_update_flag;
1194 ELSIF ( p_x_route_rec.unit_receipt_update_flag = FND_API.G_MISS_CHAR ) THEN
1195 p_x_route_rec.unit_receipt_update_flag := 'N';
1196 END IF;
1197
1198 --bachandr Enigma Phase I changes -- start
1199 -- default the missing attributes only when it is a non-Enigma Route
1200
1201 IF ( l_old_route_rec.enigma_doc_id IS NULL ) THEN
1202 IF ( p_x_route_rec.model_code IS NULL ) THEN
1203 p_x_route_rec.model_code := l_old_route_rec.model_code;
1204 ELSIF ( p_x_route_rec.model_code = FND_API.G_MISS_CHAR ) THEN
1205 p_x_route_rec.model_code := null;
1206 END IF;
1207
1208 IF ( p_x_route_rec.model_meaning IS NULL ) THEN
1209 p_x_route_rec.model_meaning := l_old_route_rec.model_meaning;
1210 ELSIF ( p_x_route_rec.model_meaning = FND_API.G_MISS_CHAR ) THEN
1211 p_x_route_rec.model_meaning := null;
1212 END IF;
1213 END IF;
1214 --bachandr Enigma Phase I changes -- end
1215
1216 IF ( p_x_route_rec.process_code IS NULL ) THEN
1217 p_x_route_rec.process_code := l_old_route_rec.process_code;
1218 ELSIF ( p_x_route_rec.process_code = FND_API.G_MISS_CHAR ) THEN
1219 p_x_route_rec.process_code := null;
1220 END IF;
1221
1222 IF ( p_x_route_rec.process IS NULL ) THEN
1223 p_x_route_rec.process := l_old_route_rec.process;
1224 ELSIF ( p_x_route_rec.process = FND_API.G_MISS_CHAR ) THEN
1225 p_x_route_rec.process := null;
1226 END IF;
1227
1228 IF ( p_x_route_rec.product_type_code IS NULL ) THEN
1229 p_x_route_rec.product_type_code := l_old_route_rec.product_type_code;
1230 ELSIF ( p_x_route_rec.product_type_code = FND_API.G_MISS_CHAR ) THEN
1231 p_x_route_rec.product_type_code := null;
1232 END IF;
1233
1234 IF ( p_x_route_rec.product_type IS NULL ) THEN
1235 p_x_route_rec.product_type := l_old_route_rec.product_type;
1236 ELSIF ( p_x_route_rec.product_type = FND_API.G_MISS_CHAR ) THEN
1237 p_x_route_rec.product_type := null;
1238 END IF;
1239
1240 IF ( p_x_route_rec.operator_party_id IS NULL ) THEN
1241 p_x_route_rec.operator_party_id := l_old_route_rec.operator_party_id;
1242 ELSE
1243 IF ( l_read_only_flag = 'Y' ) THEN
1244 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_OPERATOR_RO' );
1245 FND_MSG_PUB.add;
1246 ELSE
1247 IF ( p_x_route_rec.operator_party_id = FND_API.G_MISS_NUM ) THEN
1248 p_x_route_rec.operator_party_id := null;
1249 END IF;
1250 END IF;
1251 END IF;
1252
1253 IF ( p_x_route_rec.operator_name IS NULL ) THEN
1254 p_x_route_rec.operator_name := l_old_route_rec.operator_name;
1255 ELSE
1256 IF ( l_read_only_flag = 'Y' ) THEN
1257 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_OPERATOR_RO' );
1258 FND_MSG_PUB.add;
1259 ELSE
1260 IF ( p_x_route_rec.operator_name = FND_API.G_MISS_CHAR ) THEN
1261 p_x_route_rec.operator_name := null;
1262 END IF;
1263 END IF;
1264 END IF;
1265
1266 IF ( p_x_route_rec.zone_code IS NULL ) THEN
1267 p_x_route_rec.zone_code := l_old_route_rec.zone_code;
1268 ELSIF ( p_x_route_rec.zone_code = FND_API.G_MISS_CHAR ) THEN
1269 p_x_route_rec.zone_code := null;
1270 END IF;
1271
1272 IF ( p_x_route_rec.zone IS NULL ) THEN
1273 p_x_route_rec.zone := l_old_route_rec.zone;
1274 ELSIF ( p_x_route_rec.zone = FND_API.G_MISS_CHAR ) THEN
1275 p_x_route_rec.zone := null;
1276 END IF;
1277
1278 IF ( p_x_route_rec.sub_zone_code IS NULL ) THEN
1279 p_x_route_rec.sub_zone_code := l_old_route_rec.sub_zone_code;
1280 ELSIF ( p_x_route_rec.sub_zone_code = FND_API.G_MISS_CHAR ) THEN
1281 p_x_route_rec.sub_zone_code := null;
1282 END IF;
1283
1284 IF ( p_x_route_rec.sub_zone IS NULL ) THEN
1285 p_x_route_rec.sub_zone := l_old_route_rec.sub_zone;
1286 ELSIF ( p_x_route_rec.sub_zone = FND_API.G_MISS_CHAR ) THEN
1287 p_x_route_rec.sub_zone := null;
1288 END IF;
1289
1290 IF ( p_x_route_rec.service_item_id IS NULL ) THEN
1291 p_x_route_rec.service_item_id := l_old_route_rec.service_item_id;
1292 ELSE
1293 IF ( l_read_only_flag = 'Y' ) THEN
1294 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_SVC_ITEM_RO' );
1295 FND_MSG_PUB.add;
1296 ELSE
1297 IF ( p_x_route_rec.service_item_id = FND_API.G_MISS_NUM ) THEN
1298 p_x_route_rec.service_item_id := null;
1299 END IF;
1300 END IF;
1301 END IF;
1302
1303 IF ( p_x_route_rec.service_item_org_id IS NULL ) THEN
1304 p_x_route_rec.service_item_org_id := l_old_route_rec.service_item_org_id;
1305 ELSE
1306 IF ( l_read_only_flag = 'Y' ) THEN
1307 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_SVC_ITEM_RO' );
1308 FND_MSG_PUB.add;
1309 ELSE
1310 IF ( p_x_route_rec.service_item_org_id = FND_API.G_MISS_NUM ) THEN
1311 p_x_route_rec.service_item_org_id := null;
1312 END IF;
1313 END IF;
1314 END IF;
1315
1316 IF ( p_x_route_rec.service_item_number IS NULL ) THEN
1317 p_x_route_rec.service_item_number := l_old_route_rec.service_item_number;
1318 ELSE
1319 IF ( l_read_only_flag = 'Y' ) THEN
1320 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_SVC_ITEM_RO' );
1321 FND_MSG_PUB.add;
1322 ELSE
1323 IF ( p_x_route_rec.service_item_number = FND_API.G_MISS_CHAR ) THEN
1324 p_x_route_rec.service_item_number := null;
1325 END IF;
1326 END IF;
1327 END IF;
1328
1329 IF ( p_x_route_rec.accounting_class_code IS NULL ) THEN
1330 p_x_route_rec.accounting_class_code := l_old_route_rec.accounting_class_code;
1331 ELSIF ( p_x_route_rec.accounting_class_code = FND_API.G_MISS_CHAR ) THEN
1332 p_x_route_rec.accounting_class_code := null;
1333 END IF;
1334
1335 IF ( p_x_route_rec.accounting_class_org_id IS NULL ) THEN
1336 p_x_route_rec.accounting_class_org_id := l_old_route_rec.accounting_class_org_id;
1337 ELSIF ( p_x_route_rec.accounting_class_org_id = FND_API.G_MISS_NUM ) THEN
1338 p_x_route_rec.accounting_class_org_id := null;
1339 END IF;
1340
1341 IF ( p_x_route_rec.accounting_class IS NULL ) THEN
1342 p_x_route_rec.accounting_class := l_old_route_rec.accounting_class;
1343 ELSIF ( p_x_route_rec.accounting_class = FND_API.G_MISS_CHAR ) THEN
1344 p_x_route_rec.accounting_class := null;
1345 END IF;
1346
1347 IF ( p_x_route_rec.task_template_group_id IS NULL ) THEN
1348 p_x_route_rec.task_template_group_id := l_old_route_rec.task_template_group_id;
1349 ELSE
1350 IF ( l_read_only_flag = 'Y' ) THEN
1351 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_TASK_TEMP_RO' );
1352 FND_MSG_PUB.add;
1353 ELSE
1354 IF ( p_x_route_rec.task_template_group_id = FND_API.G_MISS_NUM ) THEN
1355 p_x_route_rec.task_template_group_id := null;
1356 END IF;
1357 END IF;
1358 END IF;
1359
1360 IF ( p_x_route_rec.task_template_group IS NULL ) THEN
1361 p_x_route_rec.task_template_group := l_old_route_rec.task_template_group;
1362 ELSE
1363 IF ( l_read_only_flag = 'Y' ) THEN
1364 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_TASK_TEMP_RO' );
1365 FND_MSG_PUB.add;
1366 ELSE
1367 IF ( p_x_route_rec.task_template_group = FND_API.G_MISS_CHAR ) THEN
1368 p_x_route_rec.task_template_group := null;
1369 END IF;
1370 END IF;
1371 END IF;
1372
1373 IF ( p_x_route_rec.qa_inspection_type IS NULL ) THEN
1374 p_x_route_rec.qa_inspection_type := l_old_route_rec.qa_inspection_type;
1375 ELSE
1376 IF ( l_read_only_flag = 'Y' ) THEN
1377 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_QA_INSP_TYPE_RO' );
1378 FND_MSG_PUB.add;
1379 ELSE
1380 IF ( p_x_route_rec.qa_inspection_type = FND_API.G_MISS_CHAR ) THEN
1381 p_x_route_rec.qa_inspection_type := null;
1382 END IF;
1383 END IF;
1384 END IF;
1385
1386 IF ( p_x_route_rec.qa_inspection_type_desc IS NULL ) THEN
1387 p_x_route_rec.qa_inspection_type_desc := l_old_route_rec.qa_inspection_type_desc;
1388 ELSE
1389 IF ( l_read_only_flag = 'Y' ) THEN
1390 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_QA_INSP_TYPE_RO' );
1391 FND_MSG_PUB.add;
1392 ELSE
1393 IF ( p_x_route_rec.qa_inspection_type_desc = FND_API.G_MISS_CHAR ) THEN
1394 p_x_route_rec.qa_inspection_type_desc := null;
1395 END IF;
1396 END IF;
1397 END IF;
1398
1399 IF ( p_x_route_rec.time_span IS NULL ) THEN
1400 p_x_route_rec.time_span := l_old_route_rec.time_span;
1401 ELSIF ( p_x_route_rec.time_span = FND_API.G_MISS_NUM ) THEN
1402 p_x_route_rec.time_span := null;
1403 END IF;
1404
1405 IF ( p_x_route_rec.remarks IS NULL ) THEN
1406 p_x_route_rec.remarks := l_old_route_rec.remarks;
1407 ELSIF ( p_x_route_rec.remarks = FND_API.G_MISS_CHAR ) THEN
1408 p_x_route_rec.remarks := null;
1409 END IF;
1410
1411 IF ( p_x_route_rec.revision_notes IS NULL ) THEN
1412 p_x_route_rec.revision_notes := l_old_route_rec.revision_notes;
1413 ELSIF ( p_x_route_rec.revision_notes = FND_API.G_MISS_CHAR ) THEN
1414 p_x_route_rec.revision_notes := null;
1415 END IF;
1416
1417 IF ( p_x_route_rec.segment1 IS NULL ) THEN
1418 p_x_route_rec.segment1 := l_old_route_rec.segment1;
1419 ELSIF ( p_x_route_rec.segment1 = FND_API.G_MISS_CHAR ) THEN
1420 p_x_route_rec.segment1 := null;
1421 END IF;
1422
1423 IF ( p_x_route_rec.segment2 IS NULL ) THEN
1424 p_x_route_rec.segment2 := l_old_route_rec.segment2;
1425 ELSIF ( p_x_route_rec.segment2 = FND_API.G_MISS_CHAR ) THEN
1426 p_x_route_rec.segment2 := null;
1427 END IF;
1428
1429 IF ( p_x_route_rec.segment3 IS NULL ) THEN
1430 p_x_route_rec.segment3 := l_old_route_rec.segment3;
1431 ELSIF ( p_x_route_rec.segment3 = FND_API.G_MISS_CHAR ) THEN
1432 p_x_route_rec.segment3 := null;
1433 END IF;
1434
1435 IF ( p_x_route_rec.segment4 IS NULL ) THEN
1436 p_x_route_rec.segment4 := l_old_route_rec.segment4;
1437 ELSIF ( p_x_route_rec.segment4 = FND_API.G_MISS_CHAR ) THEN
1438 p_x_route_rec.segment4 := null;
1439 END IF;
1440
1441 IF ( p_x_route_rec.segment5 IS NULL ) THEN
1442 p_x_route_rec.segment5 := l_old_route_rec.segment5;
1443 ELSIF ( p_x_route_rec.segment5 = FND_API.G_MISS_CHAR ) THEN
1444 p_x_route_rec.segment5 := null;
1445 END IF;
1446
1447 IF ( p_x_route_rec.segment6 IS NULL ) THEN
1448 p_x_route_rec.segment6 := l_old_route_rec.segment6;
1449 ELSIF ( p_x_route_rec.segment6 = FND_API.G_MISS_CHAR ) THEN
1450 p_x_route_rec.segment6 := null;
1451 END IF;
1452
1453 IF ( p_x_route_rec.segment7 IS NULL ) THEN
1454 p_x_route_rec.segment7 := l_old_route_rec.segment7;
1455 ELSIF ( p_x_route_rec.segment7 = FND_API.G_MISS_CHAR ) THEN
1456 p_x_route_rec.segment7 := null;
1457 END IF;
1458
1459 IF ( p_x_route_rec.segment8 IS NULL ) THEN
1460 p_x_route_rec.segment8 := l_old_route_rec.segment8;
1461 ELSIF ( p_x_route_rec.segment8 = FND_API.G_MISS_CHAR ) THEN
1462 p_x_route_rec.segment8 := null;
1463 END IF;
1464
1465 IF ( p_x_route_rec.segment9 IS NULL ) THEN
1466 p_x_route_rec.segment9 := l_old_route_rec.segment9;
1467 ELSIF ( p_x_route_rec.segment9 = FND_API.G_MISS_CHAR ) THEN
1468 p_x_route_rec.segment9 := null;
1469 END IF;
1470
1471 IF ( p_x_route_rec.segment10 IS NULL ) THEN
1472 p_x_route_rec.segment10 := l_old_route_rec.segment10;
1473 ELSIF ( p_x_route_rec.segment10 = FND_API.G_MISS_CHAR ) THEN
1474 p_x_route_rec.segment10 := null;
1475 END IF;
1476
1477 IF ( p_x_route_rec.segment11 IS NULL ) THEN
1478 p_x_route_rec.segment11 := l_old_route_rec.segment11;
1479 ELSIF ( p_x_route_rec.segment11 = FND_API.G_MISS_CHAR ) THEN
1480 p_x_route_rec.segment11 := null;
1481 END IF;
1482
1483 IF ( p_x_route_rec.segment12 IS NULL ) THEN
1484 p_x_route_rec.segment12 := l_old_route_rec.segment12;
1485 ELSIF ( p_x_route_rec.segment12 = FND_API.G_MISS_CHAR ) THEN
1486 p_x_route_rec.segment12 := null;
1487 END IF;
1488
1489 IF ( p_x_route_rec.segment13 IS NULL ) THEN
1490 p_x_route_rec.segment13 := l_old_route_rec.segment13;
1491 ELSIF ( p_x_route_rec.segment13 = FND_API.G_MISS_CHAR ) THEN
1492 p_x_route_rec.segment13 := null;
1493 END IF;
1494
1495 IF ( p_x_route_rec.segment14 IS NULL ) THEN
1496 p_x_route_rec.segment14 := l_old_route_rec.segment14;
1497 ELSIF ( p_x_route_rec.segment14 = FND_API.G_MISS_CHAR ) THEN
1498 p_x_route_rec.segment14 := null;
1499 END IF;
1500
1501 IF ( p_x_route_rec.segment15 IS NULL ) THEN
1502 p_x_route_rec.segment15 := l_old_route_rec.segment15;
1503 ELSIF ( p_x_route_rec.segment15 = FND_API.G_MISS_CHAR ) THEN
1504 p_x_route_rec.segment15 := null;
1505 END IF;
1506
1507 IF ( p_x_route_rec.attribute_category IS NULL ) THEN
1508 p_x_route_rec.attribute_category := l_old_route_rec.attribute_category;
1509 ELSIF ( p_x_route_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
1510 p_x_route_rec.attribute_category := null;
1511 END IF;
1512
1513 IF ( p_x_route_rec.attribute1 IS NULL ) THEN
1514 p_x_route_rec.attribute1 := l_old_route_rec.attribute1;
1515 ELSIF ( p_x_route_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
1516 p_x_route_rec.attribute1 := null;
1517 END IF;
1518
1519 IF ( p_x_route_rec.attribute2 IS NULL ) THEN
1520 p_x_route_rec.attribute2 := l_old_route_rec.attribute2;
1521 ELSIF ( p_x_route_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
1522 p_x_route_rec.attribute2 := null;
1523 END IF;
1524
1525 IF ( p_x_route_rec.attribute3 IS NULL ) THEN
1526 p_x_route_rec.attribute3 := l_old_route_rec.attribute3;
1527 ELSIF ( p_x_route_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
1528 p_x_route_rec.attribute3 := null;
1529 END IF;
1530
1531 IF ( p_x_route_rec.attribute4 IS NULL ) THEN
1532 p_x_route_rec.attribute4 := l_old_route_rec.attribute4;
1533 ELSIF ( p_x_route_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
1534 p_x_route_rec.attribute4 := null;
1535 END IF;
1536
1537 IF ( p_x_route_rec.attribute5 IS NULL ) THEN
1538 p_x_route_rec.attribute5 := l_old_route_rec.attribute5;
1539 ELSIF ( p_x_route_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
1540 p_x_route_rec.attribute5 := null;
1541 END IF;
1542
1543 IF ( p_x_route_rec.attribute6 IS NULL ) THEN
1544 p_x_route_rec.attribute6 := l_old_route_rec.attribute6;
1545 ELSIF ( p_x_route_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
1546 p_x_route_rec.attribute6 := null;
1547 END IF;
1548
1549 IF ( p_x_route_rec.attribute7 IS NULL ) THEN
1550 p_x_route_rec.attribute7 := l_old_route_rec.attribute7;
1551 ELSIF ( p_x_route_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
1552 p_x_route_rec.attribute7 := null;
1553 END IF;
1554
1555 IF ( p_x_route_rec.attribute8 IS NULL ) THEN
1556 p_x_route_rec.attribute8 := l_old_route_rec.attribute8;
1557 ELSIF ( p_x_route_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
1558 p_x_route_rec.attribute8 := null;
1559 END IF;
1560
1561 IF ( p_x_route_rec.attribute9 IS NULL ) THEN
1562 p_x_route_rec.attribute9 := l_old_route_rec.attribute9;
1563 ELSIF ( p_x_route_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
1564 p_x_route_rec.attribute9 := null;
1565 END IF;
1566
1567 IF ( p_x_route_rec.attribute10 IS NULL ) THEN
1568 p_x_route_rec.attribute10 := l_old_route_rec.attribute10;
1569 ELSIF ( p_x_route_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
1570 p_x_route_rec.attribute10 := null;
1571 END IF;
1572
1573 IF ( p_x_route_rec.attribute11 IS NULL ) THEN
1574 p_x_route_rec.attribute11 := l_old_route_rec.attribute11;
1575 ELSIF ( p_x_route_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
1576 p_x_route_rec.attribute11 := null;
1577 END IF;
1578
1579 IF ( p_x_route_rec.attribute12 IS NULL ) THEN
1580 p_x_route_rec.attribute12 := l_old_route_rec.attribute12;
1581 ELSIF ( p_x_route_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
1582 p_x_route_rec.attribute12 := null;
1583 END IF;
1584
1585 IF ( p_x_route_rec.attribute13 IS NULL ) THEN
1586 p_x_route_rec.attribute13 := l_old_route_rec.attribute13;
1587 ELSIF ( p_x_route_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
1588 p_x_route_rec.attribute13 := null;
1589 END IF;
1590
1591 IF ( p_x_route_rec.attribute14 IS NULL ) THEN
1592 p_x_route_rec.attribute14 := l_old_route_rec.attribute14;
1593 ELSIF ( p_x_route_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
1594 p_x_route_rec.attribute14 := null;
1595 END IF;
1596
1597 IF ( p_x_route_rec.attribute15 IS NULL ) THEN
1598 p_x_route_rec.attribute15 := l_old_route_rec.attribute15;
1599 ELSIF ( p_x_route_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
1600 p_x_route_rec.attribute15 := null;
1601 END IF;
1602
1603 END default_unchanged_attributes;
1604
1605 -- Procedure to validate individual route attributes
1606 PROCEDURE validate_attributes
1607 (
1608 p_route_rec IN route_rec_type,
1609 x_return_status OUT NOCOPY VARCHAR2
1610 )
1611 IS
1612
1613 l_return_status VARCHAR2(1);
1614 l_msg_data VARCHAR2(2000);
1615 l_res_max_duration NUMBER;
1616
1617 cursor validate_route_ovn
1618 is
1619 select 'x'
1620 from ahl_routes_app_v
1621 where route_id = p_route_rec.route_id and
1622 object_version_number = p_route_rec.object_version_number;
1623
1624 l_dummy VARCHAR2(1);
1625 --bachandr Enigma Phase I changes -- start
1626 l_enigma_avail varchar2(80);
1627 --bachandr Enigma Phase I changes -- end
1628
1629 BEGIN
1630 x_return_status := FND_API.G_RET_STS_SUCCESS;
1631
1632 -- Check if the Revision Status code does not column contains a null value.
1633 /*
1634 * Removing this as revision status for a newly created route is always DRAFT and need not be passed from
1635 * by the caller.
1636 * Changes made by balaji as a part of public API cleanup in 11510+
1637 */
1638 /*
1639 IF ( ( p_route_rec.dml_operation = 'C' AND
1640 p_route_rec.revision_status_code IS NULL ) OR
1641 p_route_rec.revision_status_code = FND_API.G_MISS_CHAR ) THEN
1642 FND_MESSAGE.set_name( 'AHL','AHL_RM_STATUS_NULL' );
1643 FND_MSG_PUB.add;
1644 END IF;
1645 */
1646
1647 -- Check if the Route Number does not column contains a null value.
1648 IF ( ( p_route_rec.dml_operation = 'C' AND
1649 p_route_rec.route_no IS NULL ) OR
1650 p_route_rec.route_no = FND_API.G_MISS_CHAR ) THEN
1651 FND_MESSAGE.set_name( 'AHL','AHL_RM_ROUTE_NO_NULL' );
1652 FND_MSG_PUB.add;
1653 END IF;
1654
1655 -- Check if the Route Title does not column contains a null value.
1656 IF ( ( p_route_rec.dml_operation = 'C' AND
1657 p_route_rec.title IS NULL ) OR
1658 p_route_rec.title = FND_API.G_MISS_CHAR ) THEN
1659 FND_MESSAGE.set_name( 'AHL','AHL_RM_ROUTE_TITLE_NULL' );
1660 FND_MSG_PUB.add;
1661 END IF;
1662
1663 -- Check if the Route Start Date does not column contains a null value.
1664 IF ( ( p_route_rec.dml_operation = 'C' AND
1665 p_route_rec.active_start_date IS NULL ) OR
1666 p_route_rec.active_start_date = FND_API.G_MISS_DATE ) THEN
1667 FND_MESSAGE.set_name( 'AHL','AHL_RM_ST_DATE_NULL' );
1668 FND_MSG_PUB.add;
1669 END IF;
1670
1671 --bachandr Enigma Phase I changes -- start
1672 -- Time Span is no longer a mandatory field during creat/update of Routes from Enigma
1673 -- Moving the validation to approval flow when it is a Enigma Route
1674 -- Check if the Time Span does not column contains a null value.
1675 SELECT trim(fnd_profile.value('AHL_ENIGMA_3C_URL')) INTO l_enigma_avail FROM dual;
1676 IF l_enigma_avail = 'N' THEN
1677 IF ( ( p_route_rec.dml_operation = 'C' AND
1678 p_route_rec.time_span IS NULL ) OR
1679 p_route_rec.time_span = FND_API.G_MISS_NUM ) THEN
1680 FND_MESSAGE.set_name( 'AHL','AHL_RM_TIME_SPAN_NULL' );
1681 FND_MSG_PUB.add;
1682 END IF;
1683 END IF;
1684
1685 -- Check if the model code or meaning is not null for create
1686 -- when the route is created from Enigma
1687 IF ( p_route_rec.dml_operation = 'C' AND p_route_rec.enigma_doc_id IS NOT NULL
1688 AND p_route_rec.enigma_doc_id <> FND_API.G_MISS_CHAR) THEN
1689 IF (( p_route_rec.model_code IS NULL OR p_route_rec.model_code = FND_API.G_MISS_CHAR ) AND
1690 ( p_route_rec.model_meaning IS NULL OR p_route_rec.model_meaning = FND_API.G_MISS_CHAR ) )THEN
1691 FND_MESSAGE.set_name( 'AHL','AHL_RM_MODEL_CODE_NULL' );
1692 FND_MSG_PUB.add;
1693 END IF;
1694 END IF;
1695 --bachandr Enigma Phase I changes -- end
1696
1697 -- Check if Time Span is not less than or equal to zero
1698 IF ( p_route_rec.time_span IS NOT NULL AND
1699 p_route_rec.time_span <> FND_API.G_MISS_NUM AND
1700 p_route_rec.time_span <= 0 ) THEN
1701 FND_MESSAGE.set_name( 'AHL','AHL_RM_INVALID_TIME_SPAN' );
1702 FND_MESSAGE.set_token( 'FIELD', p_route_rec.time_span );
1703 FND_MSG_PUB.add;
1704 END IF;
1705
1706 IF ( p_route_rec.dml_operation = 'C' ) THEN
1707 RETURN;
1708 END IF;
1709
1710 -- Check if the mandatory Route ID column contains a null value.
1711 IF ( p_route_rec.route_id IS NULL OR
1712 p_route_rec.route_id = FND_API.G_MISS_NUM ) THEN
1713 FND_MESSAGE.set_name( 'AHL','AHL_RM_ROUTE_ID_NULL' );
1714 FND_MSG_PUB.add;
1715 RETURN;
1716 END IF;
1717
1718 -- Check if the mandatory Object Version Number column contains a null value.
1719 IF ( p_route_rec.object_version_number IS NULL OR
1720 p_route_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
1721 FND_MESSAGE.set_name( 'AHL','AHL_RM_OBJ_VERSION_NULL' );
1722 FND_MSG_PUB.add;
1723 RETURN;
1724 END IF;
1725
1726 -- Validate whether the Time Span of the Route is Greater than the Longest Resource Duration for the Same Route and all the Associated Operations
1727 IF ( p_route_rec.time_span IS NOT NULL AND
1728 p_route_rec.time_span <> FND_API.G_MISS_NUM AND
1729 p_route_rec.time_span > 0 ) THEN
1730
1731 AHL_RM_ROUTE_UTIL.validate_route_time_span
1732 (
1733 x_return_status => l_return_status,
1734 x_msg_data => l_msg_data,
1735 p_route_id => p_route_rec.route_id,
1736 p_time_span => p_route_rec.time_span,
1737 x_res_max_duration => l_res_max_duration
1738 );
1739
1740 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1741 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1742 FND_MESSAGE.set_token( 'FIELD1', l_res_max_duration );
1743 FND_MESSAGE.set_token( 'FIELD2', p_route_rec.time_span );
1744 FND_MSG_PUB.add;
1745 END IF;
1746 END IF;
1747
1748 -- Added by Tamal for Bug #3854052
1749 IF (p_route_rec.dml_operation = 'U' AND p_route_rec.revision_status_code IN ('COMPLETE', 'APPROVAL_PENDING', 'TERMINATION_PENDING', 'TERMINATED'))
1750 THEN
1751 FND_MESSAGE.set_name( 'AHL','AHL_RM_RT_STS_NO_UPD' );
1752 FND_MSG_PUB.add;
1753 x_return_status := FND_API.G_RET_STS_ERROR;
1754 END IF;
1755 -- Added by Tamal for Bug #3854052
1756
1757 IF (p_route_rec.dml_operation IN ('U','D'))
1758 THEN
1759 OPEN validate_route_ovn;
1760 FETCH validate_route_ovn INTO l_dummy;
1761 IF (validate_route_ovn%NOTFOUND)
1762 THEN
1763 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
1764 FND_MSG_PUB.add;
1765 END IF;
1766 END IF;
1767
1768 END validate_attributes;
1769
1770 -- Procedure to Perform cross attribute validation and missing attribute checks and duplicate checks
1771 PROCEDURE validate_record
1772 (
1773 p_route_rec IN route_rec_type,
1774 x_return_status OUT NOCOPY VARCHAR2
1775 )
1776 IS
1777
1778 l_return_status VARCHAR2(1);
1779 l_msg_data VARCHAR2(2000);
1780 l_route_id NUMBER;
1781 l_start_date DATE;
1782
1783
1784 CURSOR get_dup_rec( c_route_no VARCHAR2 , c_revision_number NUMBER )
1785 IS
1786 SELECT route_id
1787 FROM AHL_ROUTES_APP_V
1788 WHERE UPPER(TRIM(route_no)) = UPPER(TRIM(c_route_no))
1789 AND revision_number = nvl(c_revision_number,1);
1790
1791 BEGIN
1792 --x_return_status := FND_API.G_RET_STS_SUCCESS;
1793
1794 -- Check if Duplicate Route Number exists
1795 OPEN get_dup_rec( p_route_rec.route_no , p_route_rec.revision_number );
1796
1797 FETCH get_dup_rec INTO l_route_id;
1798
1799 IF ( p_route_rec.dml_operation = 'C' )
1800 THEN
1801 -- if its create then p_route_rec.route_id = null and any duplicate record should make you throw an err.
1802 IF ( get_dup_rec%FOUND ) THEN
1803 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_NO_DUP' );
1804 FND_MSG_PUB.add;
1805 x_return_status := FND_API.G_RET_STS_ERROR;
1806 END IF;
1807 ELSIF ( p_route_rec.dml_operation = 'U' )
1808 THEN
1809 IF ( l_route_id <> p_route_rec.route_id ) THEN
1810 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_NO_DUP' );
1811 FND_MSG_PUB.add;
1812 x_return_status := FND_API.G_RET_STS_ERROR;
1813 END IF;
1814 END IF;
1815
1816 CLOSE get_dup_rec;
1817
1818 -- Check if the Route Start Date is not less than today's date
1819 /*
1820 IF ( ( p_route_rec.revision_status_code = 'DRAFT' OR
1821 p_route_rec.revision_status_code = 'APPROVAL_REJECTED' ) AND
1822 p_route_rec.active_start_date IS NOT NULL AND
1823 p_route_rec.active_start_date <> FND_API.G_MISS_DATE AND
1824 TRUNC( p_route_rec.active_start_date ) < TRUNC( SYSDATE ) ) THEN
1825 FND_MESSAGE.set_name( 'AHL','AHL_RM_INVALID_ST_DATE' );
1826 FND_MESSAGE.set_token( 'FIELD', SYSDATE );
1827 FND_MSG_PUB.add;
1828 END IF;
1829 */
1830 -- Check if Active start date is less than today's date and the start
1831 -- date of the latest revision for DRAFT and APPROVAL_REJECTED Routes
1832 IF ( ( p_route_rec.revision_status_code = 'DRAFT' OR
1833 p_route_rec.revision_status_code = 'APPROVAL_REJECTED' ) AND
1834 p_route_rec.route_id IS NOT NULL AND
1835 p_route_rec.active_start_date IS NOT NULL ) THEN
1836
1837 AHL_RM_ROUTE_UTIL.validate_rt_oper_start_date
1838 (
1839 x_return_status => l_return_status,
1840 x_msg_data => l_msg_data,
1841 p_association_type => 'ROUTE',
1842 p_object_id => p_route_rec.route_id,
1843 p_start_date => p_route_rec.active_start_date,
1844 x_start_date => l_start_date
1845 );
1846
1847 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1848 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1849 FND_MESSAGE.set_token( 'FIELD', l_start_date );
1850 FND_MSG_PUB.add;
1851 x_return_status := FND_API.G_RET_STS_ERROR;
1852 END IF;
1853 END IF;
1854
1855 -- Check if Zone contains a value but, the Product Type is NULL
1856 IF ( ( p_route_rec.zone IS NOT NULL OR
1857 p_route_rec.zone_code IS NOT NULL ) AND
1858 ( p_route_rec.product_type IS NULL AND
1859 p_route_rec.product_type_code IS NULL ) ) THEN
1860 FND_MESSAGE.set_name( 'AHL','AHL_RM_PT_NULL_ZONE_NOTNULL' );
1861 FND_MSG_PUB.add;
1862 x_return_status := FND_API.G_RET_STS_ERROR;
1863 END IF;
1864
1865 -- Check if Sub Zone contains a value but, the Product Type or Zone are NULL
1866 IF ( ( p_route_rec.sub_zone IS NOT NULL OR
1867 p_route_rec.sub_zone_code IS NOT NULL ) AND
1868 ( ( p_route_rec.product_type IS NULL AND
1869 p_route_rec.product_type_code IS NULL ) OR
1870 ( p_route_rec.zone IS NULL AND
1871 p_route_rec.zone_code IS NULL ) ) ) THEN
1872 FND_MESSAGE.set_name( 'AHL','AHL_RM_PT_NULL_SUBZONE_NOTNULL' );
1873 FND_MSG_PUB.add;
1874 x_return_status := FND_API.G_RET_STS_ERROR;
1875 END IF;
1876
1877 -- Check if the Zone is valid for the Product Type
1878 IF ( p_route_rec.product_type_code IS NOT NULL AND
1879 p_route_rec.zone_code IS NOT NULL ) THEN
1880
1881 AHL_RM_ROUTE_UTIL.validate_pt_zone
1882 (
1883 x_return_status => l_return_status,
1884 x_msg_data => l_msg_data,
1885 p_product_type_code => p_route_rec.product_type_code,
1886 p_zone_code => p_route_rec.zone_code
1887 );
1888
1889 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1890 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1891 IF ( p_route_rec.zone IS NULL ) THEN
1892 FND_MESSAGE.set_token( 'FIELD1', p_route_rec.zone_code );
1893 ELSE
1894 FND_MESSAGE.set_token( 'FIELD1', p_route_rec.zone );
1895 END IF;
1896
1897 IF ( p_route_rec.product_type IS NULL ) THEN
1898 FND_MESSAGE.set_token( 'FIELD2', p_route_rec.product_type_code );
1899 ELSE
1900 FND_MESSAGE.set_token( 'FIELD2', p_route_rec.product_type );
1901 END IF;
1902
1903 FND_MSG_PUB.add;
1904 x_return_status := FND_API.G_RET_STS_ERROR;
1905 END IF;
1906 END IF;
1907
1908 -- Check if the Sub Zone is valid for the Product Type and Zone
1909 IF ( p_route_rec.product_type_code IS NOT NULL AND
1910 p_route_rec.zone_code IS NOT NULL AND
1911 p_route_rec.sub_zone_code IS NOT NULL ) THEN
1912
1913 AHL_RM_ROUTE_UTIL.validate_pt_zone_subzone
1914 (
1915 x_return_status => l_return_status,
1916 x_msg_data => l_msg_data,
1917 p_product_type_code => p_route_rec.product_type_code,
1918 p_zone_code => p_route_rec.zone_code,
1919 p_sub_zone_code => p_route_rec.sub_zone_code
1920 );
1921
1922 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1923 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1924 IF ( p_route_rec.sub_zone IS NULL ) THEN
1925 FND_MESSAGE.set_token( 'FIELD1', p_route_rec.sub_zone_code );
1926 ELSE
1927 FND_MESSAGE.set_token( 'FIELD1', p_route_rec.sub_zone );
1928 END IF;
1929
1930 IF ( p_route_rec.zone IS NULL ) THEN
1931 FND_MESSAGE.set_token( 'FIELD2', p_route_rec.zone_code );
1932 ELSE
1933 FND_MESSAGE.set_token( 'FIELD2', p_route_rec.zone );
1934 END IF;
1935
1936 IF ( p_route_rec.product_type IS NULL ) THEN
1937 FND_MESSAGE.set_token( 'FIELD3', p_route_rec.product_type_code );
1938 ELSE
1939 FND_MESSAGE.set_token( 'FIELD3', p_route_rec.product_type );
1940 END IF;
1941
1942 FND_MSG_PUB.add;
1943 x_return_status := FND_API.G_RET_STS_ERROR;
1944 END IF;
1945 END IF;
1946
1947 END validate_record;
1948
1949 PROCEDURE process_route
1950 (
1951 p_api_version IN NUMBER := '1.0',
1952 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1953 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1954 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1955 p_default IN VARCHAR2 := FND_API.G_FALSE,
1956 p_module_type IN VARCHAR2 := NULL,
1957 x_return_status OUT NOCOPY VARCHAR2,
1958 x_msg_count OUT NOCOPY NUMBER,
1959 x_msg_data OUT NOCOPY VARCHAR2,
1960 p_x_route_rec IN OUT NOCOPY route_rec_type
1961 )
1962 IS
1963 l_api_name CONSTANT VARCHAR2(30) := 'process_route';
1964 l_api_version CONSTANT NUMBER := 1.0;
1965 l_return_status VARCHAR2(1);
1966 l_msg_count NUMBER;
1967 l_rowid VARCHAR2(30) := NULL;
1968 l_kfv_flag VARCHAR2(1) := NULL; --amsriniv. Bug 6695219
1969 l_concat_segs VARCHAR2(500) := NULL; --amsriniv. Bug 6695219
1970 --bachandr Enigma Phase I changes -- start
1971 concat VARCHAR2(1) := ':';
1972 --bachandr Enigma Phase I changes -- end
1973 --amsriniv. Bug 6695219 Begin.
1974
1975 CURSOR get_concat_segs(c_route_id NUMBER)
1976 IS
1977 SELECT CONCATENATED_SEGMENTS
1978 FROM AHL_ROUTES_B_KFV
1979 WHERE ROUTE_ID = p_x_route_rec.route_id
1980 AND REPLACE(CONCATENATED_SEGMENTS, FND_FLEX_EXT.GET_DELIMITER('AHL', 'AHLR', 101), NULL) IS NOT NULL;
1981
1982
1983 --validate system KFV to ensure that no two routes share the same System KFV.
1984 CURSOR validate_system_kfv(c_route_no VARCHAR2, c_concat_segs VARCHAR2)
1985 IS
1986 SELECT 'X'
1987 FROM AHL_ROUTES_B_KFV
1988 WHERE REPLACE(CONCATENATED_SEGMENTS, FND_FLEX_EXT.GET_DELIMITER('AHL', 'AHLR', 101), NULL) IS NOT NULL
1989 AND CONCATENATED_SEGMENTS = c_concat_segs
1990 AND ROUTE_NO <> c_route_no;
1991 --amsriniv. End
1992
1993 BEGIN
1994 -- Initialize API return status to success
1995 x_return_status := FND_API.G_RET_STS_SUCCESS;
1996
1997 -- Standard Start of API savepoint
1998 SAVEPOINT process_route_PVT;
1999
2000 -- Standard call to check for call compatibility.
2001 IF NOT FND_API.compatible_api_call
2002 (
2003 l_api_version,
2004 p_api_version,
2005 l_api_name,
2006 G_PKG_NAME
2007 )
2008 THEN
2009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2010 END IF;
2011
2012 -- Initialize message list if p_init_msg_list is set to TRUE.
2013 IF FND_API.to_boolean( p_init_msg_list ) THEN
2014 FND_MSG_PUB.initialize;
2015 END IF;
2016
2017 -- Enable Debug (optional)
2018 IF ( G_DEBUG = 'Y' ) THEN
2019 AHL_DEBUG_PUB.enable_debug;
2020 END IF;
2021
2022 IF G_DEBUG = 'Y' THEN
2023 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : Begin API' );
2024 END IF;
2025
2026 -- Generate route_id from route_number and revision_number if it is not provided.
2027 IF (p_x_route_rec.dml_operation <> 'C' AND p_x_route_rec.dml_operation <> 'c') AND
2028 (p_x_route_rec.route_id IS NULL OR p_x_route_rec.route_id = FND_API.G_MISS_NUM)
2029 THEN
2030 -- Function to convert route_number, route_revision to id
2031 AHL_RM_ROUTE_UTIL.Route_Number_To_Id(
2032 p_route_number => p_x_route_rec.route_no,
2033 p_route_revision => p_x_route_rec.revision_number,
2034 x_route_id => p_x_route_rec.route_id,
2035 x_return_status => x_return_status
2036 );
2037
2038 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2039 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2040 fnd_log.string
2041 (
2042 fnd_log.level_statement,
2043 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
2044 'Error in converting Route Number, Route Revision to ID'
2045 );
2046 END IF;
2047 RAISE FND_API.G_EXC_ERROR;
2048 END IF;
2049 END IF;
2050
2051 --This is to be added before calling validate_api_inputs
2052 IF ( p_x_route_rec.dml_operation = 'U' )
2053 THEN
2054 -- Validate Application Usage
2055 AHL_RM_ROUTE_UTIL.validate_ApplnUsage
2056 (
2057 p_object_id => p_x_route_rec.route_id,
2058 p_association_type => 'ROUTE',
2059 x_return_status => x_return_status,
2060 x_msg_data => x_msg_data
2061 );
2062
2063 -- If any severe error occurs, then, abort API.
2064 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2065 RAISE FND_API.G_EXC_ERROR;
2066 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2067 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2068 END IF;
2069
2070 END IF ;
2071
2072 -- Validate all the inputs of the API
2073 validate_api_inputs
2074 (
2075 p_x_route_rec, -- IN
2076 l_return_status -- OUT
2077 );
2078
2079 -- If any severe error occurs, then, abort API.
2080 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2081 RAISE FND_API.G_EXC_ERROR;
2082 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2083 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2084 END IF;
2085
2086 -- If the module type is JSP, then default values for ID columns of LOV attributes
2087 IF ( p_module_type = 'JSP' ) THEN
2088 clear_lov_attribute_ids
2089 (
2090 p_x_route_rec -- IN OUT Record with Values and Ids
2091 );
2092 END IF;
2093
2094 -- Convert Values into Ids.
2095 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
2096 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
2097 convert_values_to_ids
2098 (
2099 p_x_route_rec , -- IN OUT Record with Values and Ids
2100 l_return_status -- OUT
2101 );
2102
2103 -- If any severe error occurs, then, abort API.
2104 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2105 RAISE FND_API.G_EXC_ERROR;
2106 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2108 END IF;
2109 --END IF;
2110
2111 IF G_DEBUG = 'Y' THEN
2112 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after convert_values_to_ids' );
2113 END IF;
2114
2115 /* Balaji removed it as a part of public API cleanup as this defaulting logic should not be bound by p_default value. Instead the logic is moved to DML.
2116 -- Default route attributes.
2117 IF FND_API.to_boolean( p_default ) THEN
2118 default_attributes
2119 (
2120 p_x_route_rec -- IN OUT
2121 );
2122 END IF;
2123 */
2124
2125 IF G_DEBUG = 'Y' THEN
2126 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after default_attributes' );
2127 END IF;
2128
2129 -- Default missing and unchanged attributes.
2130 IF ( p_x_route_rec.dml_operation = 'U' ) THEN
2131 default_unchanged_attributes
2132 (
2133 p_x_route_rec -- IN OUT
2134 );
2135 ELSIF ( p_x_route_rec.dml_operation = 'C' ) THEN
2136 default_missing_attributes
2137 (
2138 p_x_route_rec -- IN OUT
2139 );
2140 END IF;
2141
2142 IF G_DEBUG = 'Y' THEN
2143 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after default_unchanged_attributes / default_missing_attributes' );
2144 END IF;
2145
2146 -- Validate all attributes (Item level validation)
2147 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
2148 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
2149 validate_attributes
2150 (
2151 p_x_route_rec, -- IN
2152 l_return_status -- OUT
2153 );
2154
2155 -- If any severe error occurs, then, abort API.
2156 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2157 RAISE FND_API.G_EXC_ERROR;
2158 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2160 END IF;
2161 --END IF;
2162
2163 IF G_DEBUG = 'Y' THEN
2164 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after validate_attributes' );
2165 END IF;
2166
2167 -- Perform cross attribute validation and missing attribute checks (Record level validation)
2168 -- Balaji removed p_validation_level check in 11510+ as a part of public api cleanup.
2169 --IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
2170 validate_record
2171 (
2172 p_x_route_rec, -- IN
2173 l_return_status -- OUT
2174 );
2175
2176 -- If any severe error occurs, then, abort API.
2177 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2178 RAISE FND_API.G_EXC_ERROR;
2179 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2181 END IF;
2182 --END IF;
2183
2184 IF G_DEBUG = 'Y' THEN
2185 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after validate_record' );
2186 END IF;
2187
2188 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2189 l_msg_count := FND_MSG_PUB.count_msg;
2190 IF l_msg_count > 0 THEN
2191 x_msg_count := l_msg_count;
2192 RAISE FND_API.G_EXC_ERROR;
2193 END IF;
2194
2195 -- Perform the DML by invoking the Table Handler.
2196 IF ( p_x_route_rec.dml_operation = 'C' ) THEN
2197
2198 BEGIN
2199
2200 -- Get the Route ID from the Sequence
2201 SELECT AHL_ROUTES_B_S.NEXTVAL
2202 INTO p_x_route_rec.route_id
2203 FROM DUAL;
2204
2205 --bachandr Enigma Phase I changes -- start
2206 -- Append the model code if any to the route No
2207 IF ( (p_x_route_rec.model_code IS NOT NULL ) AND (p_x_route_rec.model_code <> FND_API.G_MISS_CHAR ) ) THEN
2208 --AND (p_x_route_rec.model_meaning IS NOT NULL ) AND (p_x_route_rec.model_meaning <> FND_API.G_MISS_CHAR )) THEN
2209 p_x_route_rec.route_no := p_x_route_rec.model_code || concat || p_x_route_rec.route_no ;
2210
2211 --dbms_output.put_line('Inside If');
2212 END IF;
2213
2214 --dbms_output.put_line('p_x_route_rec.route_no is '||p_x_route_rec.route_no);
2215 --bachandr Enigma Phase I changes -- end
2216
2217 -- Insert the record
2218 AHL_ROUTES_PKG.insert_row
2219 (
2220 X_ROWID => l_rowid ,
2221 X_ROUTE_ID => p_x_route_rec.route_id ,
2222 X_OBJECT_VERSION_NUMBER => 1 ,
2223 X_ROUTE_NO => p_x_route_rec.route_no ,
2224 X_APPLICATION_USG_CODE => rtrim(ltrim(FND_PROFILE.value( 'AHL_APPLN_USAGE' ))),
2225 X_REVISION_NUMBER => 1 ,
2226 X_REVISION_STATUS_CODE => 'DRAFT' ,
2227 X_UNIT_RECEIPT_UPDATE_FLAG => p_x_route_rec.unit_receipt_update_flag , --pdoki Bug 6504159.
2228 X_START_DATE_ACTIVE => p_x_route_rec.active_start_date ,
2229 X_END_DATE_ACTIVE => p_x_route_rec.active_end_date ,
2230 X_OPERATOR_PARTY_ID => p_x_route_rec.operator_party_id ,
2231 X_QA_INSPECTION_TYPE => p_x_route_rec.qa_inspection_type ,
2232 X_SERVICE_ITEM_ID => p_x_route_rec.service_item_id ,
2233 X_SERVICE_ITEM_ORG_ID => p_x_route_rec.service_item_org_id ,
2234 X_TASK_TEMPLATE_GROUP_ID => p_x_route_rec.task_template_group_id ,
2235 X_ACCOUNTING_CLASS_CODE => p_x_route_rec.accounting_class_code ,
2236 X_ACCOUNTING_CLASS_ORG_ID => p_x_route_rec.accounting_class_org_id ,
2237 X_ROUTE_TYPE_CODE => p_x_route_rec.route_type_code ,
2238 X_PRODUCT_TYPE_CODE => p_x_route_rec.product_type_code ,
2239 --bachandr Enigma Phase I changes -- start
2240 X_MODEL_CODE => p_x_route_rec.model_code ,
2241 X_ENIGMA_PUBLISH_DATE => p_x_route_rec.enigma_publish_date,
2242 X_ENIGMA_DOC_ID => p_x_route_rec.enigma_doc_id,
2243 X_ENIGMA_ROUTE_ID => p_x_route_rec.enigma_route_id,
2244 X_FILE_ID => p_x_route_rec.file_id,
2245 --bachandr Enigma Phase I changes -- end
2246 X_ZONE_CODE => p_x_route_rec.zone_code ,
2247 X_SUB_ZONE_CODE => p_x_route_rec.sub_zone_code ,
2248 X_PROCESS_CODE => p_x_route_rec.process_code ,
2249 X_TIME_SPAN => p_x_route_rec.time_span ,
2250 X_SEGMENT1 => p_x_route_rec.segment1 ,
2251 X_SEGMENT2 => p_x_route_rec.segment2 ,
2252 X_SEGMENT3 => p_x_route_rec.segment3 ,
2253 X_SEGMENT4 => p_x_route_rec.segment4 ,
2254 X_SEGMENT5 => p_x_route_rec.segment5 ,
2255 X_SEGMENT6 => p_x_route_rec.segment6 ,
2256 X_SEGMENT7 => p_x_route_rec.segment7 ,
2257 X_SEGMENT8 => p_x_route_rec.segment8 ,
2258 X_SEGMENT9 => p_x_route_rec.segment9 ,
2259 X_SEGMENT10 => p_x_route_rec.segment10 ,
2260 X_SEGMENT11 => p_x_route_rec.segment11 ,
2261 X_SEGMENT12 => p_x_route_rec.segment12 ,
2262 X_SEGMENT13 => p_x_route_rec.segment13 ,
2263 X_SEGMENT14 => p_x_route_rec.segment14 ,
2264 X_SEGMENT15 => p_x_route_rec.segment15 ,
2265 X_ATTRIBUTE_CATEGORY => p_x_route_rec.attribute_category ,
2266 X_ATTRIBUTE1 => p_x_route_rec.attribute1 ,
2267 X_ATTRIBUTE2 => p_x_route_rec.attribute2 ,
2268 X_ATTRIBUTE3 => p_x_route_rec.attribute3 ,
2269 X_ATTRIBUTE4 => p_x_route_rec.attribute4 ,
2270 X_ATTRIBUTE5 => p_x_route_rec.attribute5 ,
2271 X_ATTRIBUTE6 => p_x_route_rec.attribute6 ,
2272 X_ATTRIBUTE7 => p_x_route_rec.attribute7 ,
2273 X_ATTRIBUTE8 => p_x_route_rec.attribute8 ,
2274 X_ATTRIBUTE9 => p_x_route_rec.attribute9 ,
2275 X_ATTRIBUTE10 => p_x_route_rec.attribute10 ,
2276 X_ATTRIBUTE11 => p_x_route_rec.attribute11 ,
2277 X_ATTRIBUTE12 => p_x_route_rec.attribute12 ,
2278 X_ATTRIBUTE13 => p_x_route_rec.attribute13 ,
2279 X_ATTRIBUTE14 => p_x_route_rec.attribute14 ,
2280 X_ATTRIBUTE15 => p_x_route_rec.attribute15 ,
2281 X_TITLE => p_x_route_rec.title ,
2282 X_REMARKS => p_x_route_rec.remarks ,
2283 X_REVISION_NOTES => p_x_route_rec.revision_notes ,
2284 X_CREATION_DATE => G_CREATION_DATE ,
2285 X_CREATED_BY => G_CREATED_BY ,
2286 X_LAST_UPDATE_DATE => G_LAST_UPDATE_DATE ,
2287 X_LAST_UPDATED_BY => G_LAST_UPDATED_BY ,
2288 X_LAST_UPDATE_LOGIN => G_LAST_UPDATE_LOGIN,
2289 X_ENABLED_FLAG => 'Y',
2290 X_SUMMARY_FLAG => 'N'
2291 );
2292 EXCEPTION
2293 WHEN NO_DATA_FOUND THEN
2294 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_RECORD_CHANGED' );
2295 FND_MSG_PUB.add;
2296 WHEN OTHERS THEN
2297 IF ( SQLCODE = -1 ) THEN
2298 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_NO_DUP' );
2299 FND_MSG_PUB.add;
2300 ELSE
2301 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
2302 fnd_log.string
2303 (
2304 fnd_log.level_unexpected,
2305 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2306 'AHL_ROUTES_PKG.insert_row error = ['||SQLERRM||']'
2307 );
2308 END IF;
2309 END IF;
2310 END;
2311
2312 ELSIF ( p_x_route_rec.dml_operation = 'U' ) THEN
2313
2314 BEGIN
2315 -- Update the record
2316
2317 p_x_route_rec.object_version_number := p_x_route_rec.object_version_number + 1;
2318
2319 AHL_ROUTES_PKG.update_row
2320 (
2321 X_ROUTE_ID => p_x_route_rec.route_id ,
2322 X_OBJECT_VERSION_NUMBER => p_x_route_rec.object_version_number ,
2323 X_ROUTE_NO => p_x_route_rec.route_no ,
2324 X_REVISION_NUMBER => p_x_route_rec.revision_number ,
2325 X_REVISION_STATUS_CODE => p_x_route_rec.revision_status_code ,
2326 X_UNIT_RECEIPT_UPDATE_FLAG => p_x_route_rec.unit_receipt_update_flag ,--pdoki Bug 6504159.
2327 X_START_DATE_ACTIVE => p_x_route_rec.active_start_date ,
2328 X_END_DATE_ACTIVE => p_x_route_rec.active_end_date ,
2329 X_OPERATOR_PARTY_ID => p_x_route_rec.operator_party_id ,
2330 X_QA_INSPECTION_TYPE => p_x_route_rec.qa_inspection_type ,
2331 X_SERVICE_ITEM_ID => p_x_route_rec.service_item_id ,
2332 X_SERVICE_ITEM_ORG_ID => p_x_route_rec.service_item_org_id ,
2333 X_TASK_TEMPLATE_GROUP_ID => p_x_route_rec.task_template_group_id ,
2334 X_ACCOUNTING_CLASS_CODE => p_x_route_rec.accounting_class_code ,
2335 X_ACCOUNTING_CLASS_ORG_ID => p_x_route_rec.accounting_class_org_id,
2336 X_ROUTE_TYPE_CODE => p_x_route_rec.route_type_code ,
2337 X_PRODUCT_TYPE_CODE => p_x_route_rec.product_type_code ,
2338 --bachandr Enigma Phase I changes -- start
2339 X_MODEL_CODE => p_x_route_rec.model_code ,
2340 X_FILE_ID => p_x_route_rec.file_id,
2341 --bachandr Enigma Phase I changes -- end
2342 X_ZONE_CODE => p_x_route_rec.zone_code ,
2343 X_SUB_ZONE_CODE => p_x_route_rec.sub_zone_code ,
2344 X_PROCESS_CODE => p_x_route_rec.process_code ,
2345 X_TIME_SPAN => p_x_route_rec.time_span ,
2346 X_SEGMENT1 => p_x_route_rec.segment1 ,
2347 X_SEGMENT2 => p_x_route_rec.segment2 ,
2348 X_SEGMENT3 => p_x_route_rec.segment3 ,
2349 X_SEGMENT4 => p_x_route_rec.segment4 ,
2350 X_SEGMENT5 => p_x_route_rec.segment5 ,
2351 X_SEGMENT6 => p_x_route_rec.segment6 ,
2352 X_SEGMENT7 => p_x_route_rec.segment7 ,
2353 X_SEGMENT8 => p_x_route_rec.segment8 ,
2354 X_SEGMENT9 => p_x_route_rec.segment9 ,
2355 X_SEGMENT10 => p_x_route_rec.segment10 ,
2356 X_SEGMENT11 => p_x_route_rec.segment11 ,
2357 X_SEGMENT12 => p_x_route_rec.segment12 ,
2358 X_SEGMENT13 => p_x_route_rec.segment13 ,
2359 X_SEGMENT14 => p_x_route_rec.segment14 ,
2360 X_SEGMENT15 => p_x_route_rec.segment15 ,
2361 X_ATTRIBUTE_CATEGORY => p_x_route_rec.attribute_category ,
2362 X_ATTRIBUTE1 => p_x_route_rec.attribute1 ,
2363 X_ATTRIBUTE2 => p_x_route_rec.attribute2 ,
2364 X_ATTRIBUTE3 => p_x_route_rec.attribute3 ,
2365 X_ATTRIBUTE4 => p_x_route_rec.attribute4 ,
2366 X_ATTRIBUTE5 => p_x_route_rec.attribute5 ,
2367 X_ATTRIBUTE6 => p_x_route_rec.attribute6 ,
2368 X_ATTRIBUTE7 => p_x_route_rec.attribute7 ,
2369 X_ATTRIBUTE8 => p_x_route_rec.attribute8 ,
2370 X_ATTRIBUTE9 => p_x_route_rec.attribute9 ,
2371 X_ATTRIBUTE10 => p_x_route_rec.attribute10 ,
2372 X_ATTRIBUTE11 => p_x_route_rec.attribute11 ,
2373 X_ATTRIBUTE12 => p_x_route_rec.attribute12 ,
2374 X_ATTRIBUTE13 => p_x_route_rec.attribute13 ,
2375 X_ATTRIBUTE14 => p_x_route_rec.attribute14 ,
2376 X_ATTRIBUTE15 => p_x_route_rec.attribute15 ,
2377 X_TITLE => p_x_route_rec.title ,
2378 X_REMARKS => p_x_route_rec.remarks ,
2379 X_REVISION_NOTES => p_x_route_rec.revision_notes ,
2380 X_LAST_UPDATE_DATE => G_LAST_UPDATE_DATE ,
2381 X_LAST_UPDATED_BY => G_LAST_UPDATED_BY ,
2382 X_LAST_UPDATE_LOGIN => G_LAST_UPDATE_LOGIN,
2383 X_ENABLED_FLAG => 'Y',
2384 X_SUMMARY_FLAG => 'N'
2385 );
2386
2387 EXCEPTION
2388 WHEN NO_DATA_FOUND THEN
2389 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_RECORD_CHANGED' );
2390 FND_MSG_PUB.add;
2391 WHEN OTHERS THEN
2392 IF ( SQLCODE = -1 ) THEN
2393 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_NO_DUP' );
2394 FND_MSG_PUB.add;
2395 ELSE
2396 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
2397 fnd_log.string
2398 (
2399 fnd_log.level_unexpected,
2400 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
2401 'AHL_ROUTES_PKG.update_row error = ['||SQLERRM||']'
2402 );
2403 END IF;
2404 END IF;
2405 END;
2406
2407 END IF;
2408
2409 --amsriniv. Begin. Bug 6695219
2410 OPEN get_concat_segs(p_x_route_rec.route_id);
2411 FETCH get_concat_segs into l_concat_segs;
2412 IF(get_concat_segs%FOUND)
2413 THEN
2414 OPEN validate_system_kfv(p_x_route_rec.route_no, l_concat_segs);
2415 FETCH validate_system_kfv INTO l_kfv_flag;
2416 IF (validate_system_kfv%FOUND)
2417 THEN
2418 FND_MESSAGE.set_name('AHL', 'AHL_RM_INV_SYS_KFV');
2419 FND_MSG_PUB.add;
2420 END IF;
2421 CLOSE validate_system_kfv;
2422 END IF;
2423 CLOSE get_concat_segs;
2424 --amsriniv. End
2425
2426 IF G_DEBUG = 'Y' THEN
2427 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after DML operation' );
2428 END IF;
2429
2430 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2431 l_msg_count := FND_MSG_PUB.count_msg;
2432 IF l_msg_count > 0 THEN
2433 x_msg_count := l_msg_count;
2434 RAISE FND_API.G_EXC_ERROR;
2435 END IF;
2436
2437 -- Perform the Commit (if requested)
2438 IF FND_API.to_boolean( p_commit ) THEN
2439 COMMIT WORK;
2440 END IF;
2441
2442 -- Count and Get messages (optional)
2443 FND_MSG_PUB.count_and_get
2444 (
2445 p_encoded => FND_API.G_FALSE,
2446 p_count => x_msg_count,
2447 p_data => x_msg_data
2448 );
2449
2450 -- Disable debug (if enabled)
2451 IF ( G_DEBUG = 'Y' ) THEN
2452 AHL_DEBUG_PUB.disable_debug;
2453 END IF;
2454
2455 EXCEPTION
2456
2457 WHEN FND_API.G_EXC_ERROR THEN
2458 ROLLBACK TO process_route_PVT;
2459 x_return_status := FND_API.G_RET_STS_ERROR ;
2460 FND_MSG_PUB.count_and_get
2461 (
2462 p_encoded => FND_API.G_FALSE,
2463 p_count => x_msg_count,
2464 p_data => x_msg_data
2465 );
2466
2467 -- Disable debug (if enabled)
2468 IF ( G_DEBUG = 'Y' ) THEN
2469 AHL_DEBUG_PUB.disable_debug;
2470 END IF;
2471
2472 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2473 ROLLBACK TO process_route_PVT;
2474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2475 FND_MSG_PUB.count_and_get
2476 (
2477 p_encoded => FND_API.G_FALSE,
2478 p_count => x_msg_count,
2479 p_data => x_msg_data
2480 );
2481
2482 -- Disable debug (if enabled)
2483 IF ( G_DEBUG = 'Y' ) THEN
2484 AHL_DEBUG_PUB.disable_debug;
2485 END IF;
2486
2487 WHEN OTHERS THEN
2488 ROLLBACK TO process_route_PVT;
2489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2490 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2491 THEN
2492 FND_MSG_PUB.add_exc_msg
2493 (
2494 p_pkg_name => G_PKG_NAME,
2495 p_procedure_name => l_api_name,
2496 p_error_text => SUBSTRB(SQLERRM,1,240)
2497 );
2498 END IF;
2499 FND_MSG_PUB.count_and_get
2500 (
2501 p_encoded => FND_API.G_FALSE,
2502 p_count => x_msg_count,
2503 p_data => x_msg_data
2504 );
2505
2506 -- Disable debug (if enabled)
2507 IF ( G_DEBUG = 'Y' ) THEN
2508 AHL_DEBUG_PUB.disable_debug;
2509 END IF;
2510
2511 END process_route;
2512
2513 PROCEDURE delete_route
2514 (
2515 p_api_version IN NUMBER := '1.0',
2516 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2517 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2518 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2519 p_default IN VARCHAR2 := FND_API.G_FALSE,
2520 p_module_type IN VARCHAR2 := NULL,
2521 x_return_status OUT NOCOPY VARCHAR2,
2522 x_msg_count OUT NOCOPY NUMBER,
2523 x_msg_data OUT NOCOPY VARCHAR2,
2524 p_route_id IN NUMBER,
2525 p_object_version_number IN NUMBER
2526 )
2527 IS
2528
2529 l_api_name CONSTANT VARCHAR2(30) := 'delete_route';
2530 l_api_version CONSTANT NUMBER := 1.0;
2531 l_return_status VARCHAR2(1);
2532 l_msg_data VARCHAR2(2000);
2533 --bachandr Enigma Phase I changes -- start
2534 l_doc_id VARCHAR2(80);
2535 --bachandr Enigma Phase I changes -- end
2536
2537 CURSOR get_doc_associations( c_route_id NUMBER )
2538 IS
2539 SELECT doc_title_asso_id
2540 FROM ahl_doc_title_assos_b
2541 WHERE aso_object_id = c_route_id
2542 AND aso_object_type_code = 'ROUTE';
2543
2544 cursor validate_route_ovn
2545 is
2546 select 'x'
2547 from ahl_routes_app_v
2548 where route_id = p_route_id and
2549 object_version_number = p_object_version_number;
2550
2551 --bachandr Enigma Phase I changes -- start
2552 -- Cursor to get the document_id
2553 cursor get_doc_id
2554 is
2555 select enigma_doc_id
2556 from ahl_routes_b
2557 where route_id = p_route_id;
2558 --bachandr Enigma Phase I changes -- end
2559
2560 l_dummy VARCHAR2(1);
2561
2562 BEGIN
2563
2564 -- Initialize API return status to success
2565 x_return_status := FND_API.G_RET_STS_SUCCESS;
2566
2567 -- Standard Start of API savepoint
2568 SAVEPOINT delete_route_PVT;
2569
2570 -- Standard call to check for call compatibility.
2571 IF NOT FND_API.compatible_api_call
2572 (
2573 l_api_version,
2574 p_api_version,
2575 l_api_name,
2576 G_PKG_NAME
2577 )
2578 THEN
2579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2580 END IF;
2581
2582 -- Initialize message list if p_init_msg_list is set to TRUE.
2583 IF FND_API.to_boolean( p_init_msg_list ) THEN
2584 FND_MSG_PUB.initialize;
2585 END IF;
2586
2587 -- Enable Debug (optional)
2588 IF ( G_DEBUG = 'Y' ) THEN
2589 AHL_DEBUG_PUB.enable_debug;
2590 END IF;
2591
2592 IF G_DEBUG = 'Y' THEN
2593 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : Begin API' );
2594 END IF;
2595
2596 --This is to be added before calling AHL_RM_ROUTE_UTIL.validate_route_status
2597 -- Validate Application Usage
2598 AHL_RM_ROUTE_UTIL .validate_ApplnUsage
2599 (
2600 p_object_id => p_route_id,
2601 p_association_type => 'ROUTE',
2602 x_return_status => x_return_status,
2603 x_msg_data => x_msg_data
2604 );
2605
2606 -- If any severe error occurs, then, abort API.
2607 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2608 RAISE FND_API.G_EXC_ERROR;
2609 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2611 END IF;
2612
2613 IF ( p_route_id IS NULL OR
2614 p_route_id = FND_API.G_MISS_NUM OR
2615 p_object_version_number IS NULL OR
2616 p_object_version_number = FND_API.G_MISS_NUM ) THEN
2617 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
2618 FND_MESSAGE.set_token( 'PROCEDURE', l_api_name );
2619 FND_MSG_PUB.add;
2620 RAISE FND_API.G_EXC_ERROR;
2621 END IF;
2622
2623 OPEN validate_route_ovn;
2624 FETCH validate_route_ovn INTO l_dummy;
2625 IF (validate_route_ovn%NOTFOUND)
2626 THEN
2627 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
2628 FND_MSG_PUB.add;
2629 x_return_status := FND_API.G_RET_STS_ERROR;
2630 RAISE FND_API.G_EXC_ERROR;
2631 END IF;
2632
2633 --bachandr Enigma Phase I changes -- start
2634 -- Fetch the doc_id and if the doc_id is not null( ie an Enigma Route) then
2635 -- deletion is not allowed
2636 -- Kick the validation only if the call is from the CMRO end.
2637 IF (p_module_type <> 'ENIGMA' ) THEN
2638 OPEN get_doc_id;
2639 FETCH get_doc_id INTO l_doc_id;
2640 IF (get_doc_id%FOUND AND l_doc_id IS NOT NULL)
2641 THEN
2642 FND_MESSAGE.set_name('AHL', 'AHL_RM_ROUTE_ENIG_DEL');
2643 FND_MSG_PUB.add;
2644 x_return_status := FND_API.G_RET_STS_ERROR;
2645 RAISE FND_API.G_EXC_ERROR;
2646 END IF;
2647 END IF;
2648 --bachandr Enigma Phase I changes -- end
2649
2650 AHL_RM_ROUTE_UTIL.validate_route_status
2651 (
2652 p_route_id => p_route_id,
2653 x_msg_data => l_msg_data,
2654 x_return_status => l_return_status
2655 );
2656
2657 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
2658 IF ( l_msg_data = 'AHL_RM_INVALID_ROUTE_STATUS' ) THEN
2659 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RT_STATUS_NOT_DRAFT' );
2660 ELSE
2661 FND_MESSAGE.set_name( 'AHL', l_msg_data );
2662 END IF;
2663
2664 FND_MSG_PUB.add;
2665 RAISE FND_API.G_EXC_ERROR;
2666 END IF;
2667
2668
2669 IF G_DEBUG = 'Y' THEN
2670 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after Deleting AHL_ROUTES_B and AHL_ROUTES_TL' );
2671 END IF;
2672
2673 -- Delete all the associations
2674
2675 -- 0.Delete Effectivities
2676 DELETE ahl_route_effectivities
2677 WHERE ROUTE_ID = p_route_id;
2678
2679 -- If no records exist, then, Continue.
2680 IF ( SQL%ROWCOUNT = 0 ) THEN
2681 -- Ignore the Exception
2682 NULL;
2683 END IF;
2684
2685 IF G_DEBUG = 'Y' THEN
2686 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after Deleting Effectivities' );
2687 END IF;
2688
2689 -- 1.Delete Material Requirements
2690 DELETE AHL_RT_OPER_MATERIALS
2691 WHERE OBJECT_ID = p_route_id
2692 AND ASSOCIATION_TYPE_CODE = 'ROUTE';
2693
2694 -- If no records exist, then, Continue.
2695 IF ( SQL%ROWCOUNT = 0 ) THEN
2696 -- Ignore the Exception
2697 NULL;
2698 END IF;
2699
2700 IF G_DEBUG = 'Y' THEN
2701 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after Deleting Material Requirements' );
2702 END IF;
2703
2704 -- 2.Delete Resource Requirements
2705 DELETE AHL_RT_OPER_RESOURCES
2706 WHERE OBJECT_ID = p_route_id
2707 AND ASSOCIATION_TYPE_CODE = 'ROUTE';
2708
2709 -- If no records exist, then, Continue.
2710 IF ( SQL%ROWCOUNT = 0 ) THEN
2711 -- Ignore the Exception
2712 NULL;
2713 END IF;
2714
2715 IF G_DEBUG = 'Y' THEN
2716 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after Deleting Resource Requirements' );
2717 END IF;
2718
2719 -- 3.Delete Reference Documents
2720 FOR I in get_doc_associations( p_route_id ) LOOP
2721 ahl_doc_title_assos_pkg.delete_row
2722 (
2723 X_DOC_TITLE_ASSO_ID => I.doc_title_asso_id
2724 );
2725 END LOOP;
2726
2727 IF G_DEBUG = 'Y' THEN
2728 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after Deleting Reference Documents' );
2729 END IF;
2730
2731 -- 4.Delete Associated Operations
2732 DELETE AHL_ROUTE_OPERATIONS
2733 WHERE ROUTE_ID = p_route_id;
2734
2735 -- If no records exist, then, Continue.
2736 IF ( SQL%ROWCOUNT = 0 ) THEN
2737 -- Ignore the Exception
2738 NULL;
2739 END IF;
2740
2741 -- 5.Delete Access Panel associations
2742 DELETE AHL_RT_OPER_ACCESS_PANELS
2743 WHERE OBJECT_ID = p_route_id
2744 AND ASSOCIATION_TYPE_CODE = 'ROUTE';
2745
2746 -- If no records exist, then, Continue.
2747 IF ( SQL%ROWCOUNT = 0 ) THEN
2748 -- Ignore the Exception
2749 NULL;
2750 END IF;
2751
2752 IF G_DEBUG = 'Y' THEN
2753 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after Deleting Access Panels' );
2754 END IF;
2755
2756 BEGIN
2757 -- Delete the record in AHL_ROUTES_B and AHL_ROUTES_TL
2758 AHL_ROUTES_PKG.delete_row
2759 (
2760 X_ROUTE_ID => p_route_id
2761 );
2762
2763 EXCEPTION
2764 WHEN NO_DATA_FOUND THEN
2765 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_RECORD_CHANGED' );
2766 FND_MSG_PUB.add;
2767 RAISE FND_API.G_EXC_ERROR;
2768 WHEN OTHERS THEN
2769 RAISE;
2770 END;
2771
2772 IF G_DEBUG = 'Y' THEN
2773 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after Deleting Associated Operations' );
2774 END IF;
2775
2776 -- Perform the Commit (if requested)
2777 IF FND_API.to_boolean( p_commit ) THEN
2778 COMMIT WORK;
2779 END IF;
2780
2781 -- Count and Get messages (optional)
2782 FND_MSG_PUB.count_and_get
2783 (
2784 p_encoded => FND_API.G_FALSE,
2785 p_count => x_msg_count,
2786 p_data => x_msg_data
2787 );
2788
2789 -- Disable debug (if enabled)
2790 IF ( G_DEBUG = 'Y' ) THEN
2791 AHL_DEBUG_PUB.disable_debug;
2792 END IF;
2793
2794 EXCEPTION
2795
2796 WHEN FND_API.G_EXC_ERROR THEN
2797 ROLLBACK TO delete_route_PVT;
2798 x_return_status := FND_API.G_RET_STS_ERROR ;
2799 FND_MSG_PUB.count_and_get
2800 (
2801 p_encoded => FND_API.G_FALSE,
2802 p_count => x_msg_count,
2803 p_data => x_msg_data
2804 );
2805
2806 -- Disable debug (if enabled)
2807 IF ( G_DEBUG = 'Y' ) THEN
2808 AHL_DEBUG_PUB.disable_debug;
2809 END IF;
2810
2811 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2812 ROLLBACK TO delete_route_PVT;
2813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2814 FND_MSG_PUB.count_and_get
2815 (
2816 p_encoded => FND_API.G_FALSE,
2817 p_count => x_msg_count,
2818 p_data => x_msg_data
2819 );
2820
2821 -- Disable debug (if enabled)
2822 IF ( G_DEBUG = 'Y' ) THEN
2823 AHL_DEBUG_PUB.disable_debug;
2824 END IF;
2825
2826 WHEN OTHERS THEN
2827 ROLLBACK TO delete_route_PVT;
2828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2829 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2830 THEN
2831 FND_MSG_PUB.add_exc_msg
2832 (
2833 p_pkg_name => G_PKG_NAME,
2834 p_procedure_name => l_api_name,
2835 p_error_text => SUBSTRB(SQLERRM,1,240)
2836 );
2837 END IF;
2838 FND_MSG_PUB.count_and_get
2839 (
2840 p_encoded => FND_API.G_FALSE,
2841 p_count => x_msg_count,
2842 p_data => x_msg_data
2843 );
2844
2845 -- Disable debug (if enabled)
2846 IF ( G_DEBUG = 'Y' ) THEN
2847 AHL_DEBUG_PUB.disable_debug;
2848 END IF;
2849
2850 END delete_route;
2851
2852 PROCEDURE create_route_revision
2853 (
2854 p_api_version IN NUMBER := '1.0',
2855 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2856 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2857 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2858 p_default IN VARCHAR2 := FND_API.G_FALSE,
2859 p_module_type IN VARCHAR2 := NULL,
2860 x_return_status OUT NOCOPY VARCHAR2,
2861 x_msg_count OUT NOCOPY NUMBER,
2862 x_msg_data OUT NOCOPY VARCHAR2,
2863 p_route_id IN NUMBER,
2864 p_object_version_number IN NUMBER,
2865 x_route_id OUT NOCOPY NUMBER
2866 )
2867 IS
2868
2869 l_api_name CONSTANT VARCHAR2(30) := 'create_route_revision';
2870 l_api_version CONSTANT NUMBER := 1.0;
2871 l_return_status VARCHAR2(1);
2872 l_msg_data VARCHAR2(2000);
2873 l_old_route_rec route_rec_type;
2874 l_dummy VARCHAR2(1);
2875 l_revision_number NUMBER;
2876 l_route_id NUMBER;
2877 l_doc_title_assos_id NUMBER;
2878 l_rowid VARCHAR2(30) := NULL;
2879 l_rt_oper_resource_id NUMBER;
2880 l_route_effectivity_id NUMBER;
2881
2882 CURSOR get_latest_revision( c_route_no VARCHAR2 )
2883 IS
2884 SELECT MAX( revision_number )
2885 FROM AHL_ROUTES_APP_V
2886 WHERE UPPER(TRIM(route_no)) = UPPER(TRIM(c_route_no));
2887
2888 CURSOR get_doc_associations( c_route_id NUMBER )
2889 IS
2890 SELECT doc_title_asso_id,
2891 doc_revision_id,
2892 document_id,
2893 use_latest_rev_flag,
2894 serial_no,
2895 source_ref_code,
2896 chapter,
2897 section,
2898 subject,
2899 page,
2900 figure,
2901 note,
2902 attribute_category,
2903 attribute1,
2904 attribute2,
2905 attribute3,
2906 attribute4,
2907 attribute5,
2908 attribute6,
2909 attribute7,
2910 attribute8,
2911 attribute9,
2912 attribute10,
2913 attribute11,
2914 attribute12,
2915 attribute13,
2916 attribute14,
2917 attribute15
2918 FROM AHL_DOC_TITLE_ASSOS_VL
2919 WHERE aso_object_id = c_route_id
2920 AND aso_object_type_code = 'ROUTE';
2921
2922 CURSOR get_rt_oper_resources (c_route_id NUMBER) IS
2923 SELECT
2924 RT_OPER_RESOURCE_ID,
2925 OBJECT_ID,
2926 ASSOCIATION_TYPE_CODE,
2927 ASO_RESOURCE_ID,
2928 QUANTITY,
2929 DURATION,
2930 ACTIVITY_ID,
2931 COST_BASIS_ID,
2932 SCHEDULED_TYPE_ID,
2933 AUTOCHARGE_TYPE_ID,
2934 STANDARD_RATE_FLAG,
2935 ATTRIBUTE_CATEGORY,
2936 ATTRIBUTE1,
2937 ATTRIBUTE2,
2938 ATTRIBUTE3,
2939 ATTRIBUTE4,
2940 ATTRIBUTE5,
2941 ATTRIBUTE6,
2942 ATTRIBUTE7,
2943 ATTRIBUTE8,
2944 ATTRIBUTE9,
2945 ATTRIBUTE10,
2946 ATTRIBUTE11,
2947 ATTRIBUTE12,
2948 ATTRIBUTE13,
2949 ATTRIBUTE14,
2950 ATTRIBUTE15,
2951 -- Bug # 7644260 (FP for ER # 6998882) -- start
2952 SCHEDULE_SEQ
2953 -- Bug # 7644260 (FP for ER # 6998882) -- end
2954 FROM ahl_rt_oper_resources
2955 WHERE object_id = c_route_id
2956 AND association_type_code = 'ROUTE';
2957
2958 CURSOR get_route_efcts (c_route_id NUMBER) IS
2959 SELECT
2960 route_effectivity_id
2961 , route_id
2962 , inventory_item_id
2963 , inventory_master_org_id
2964 , mc_id
2965 , mc_header_id ,
2966 OBJECT_VERSION_NUMBER,
2967 LAST_UPDATE_DATE,
2968 LAST_UPDATED_BY,
2969 CREATION_DATE,
2970 CREATED_BY,
2971 LAST_UPDATE_LOGIN,
2972 security_group_id,
2973 ATTRIBUTE_CATEGORY,
2974 ATTRIBUTE1,
2975 ATTRIBUTE2,
2976 ATTRIBUTE3,
2977 ATTRIBUTE4,
2978 ATTRIBUTE5,
2979 ATTRIBUTE6,
2980 ATTRIBUTE7,
2981 ATTRIBUTE8,
2982 ATTRIBUTE9,
2983 ATTRIBUTE10,
2984 ATTRIBUTE11,
2985 ATTRIBUTE12,
2986 ATTRIBUTE13,
2987 ATTRIBUTE14,
2988 ATTRIBUTE15
2989 FROM ahl_route_effectivities
2990 WHERE route_id = c_route_id
2991 ;
2992
2993 BEGIN
2994 -- Initialize API return status to success
2995 x_return_status := FND_API.G_RET_STS_SUCCESS;
2996
2997 -- Standard Start of API savepoint
2998 SAVEPOINT create_route_revision_PVT;
2999
3000 -- Standard call to check for call compatibility.
3001 IF NOT FND_API.compatible_api_call
3002 (
3003 l_api_version,
3004 p_api_version,
3005 l_api_name,
3006 G_PKG_NAME
3007 )
3008 THEN
3009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3010 END IF;
3011
3012 -- Initialize message list if p_init_msg_list is set to TRUE.
3013 IF FND_API.to_boolean( p_init_msg_list ) THEN
3014 FND_MSG_PUB.initialize;
3015 END IF;
3016
3017 -- Enable Debug (optional)
3018 IF ( G_DEBUG = 'Y' ) THEN
3019 AHL_DEBUG_PUB.enable_debug;
3020 END IF;
3021
3022 IF ( G_DEBUG = 'Y' ) THEN
3023 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : Begin API' );
3024 END IF;
3025
3026 --This is to be added before calling get_route_record()
3027 -- Validate Application Usage
3028 AHL_RM_ROUTE_UTIL .validate_ApplnUsage
3029 (
3030 p_object_id => p_route_id,
3031 p_association_type => 'ROUTE',
3032 x_return_status => x_return_status,
3033 x_msg_data => x_msg_data
3034 );
3035
3036 -- If any severe error occurs, then, abort API.
3037 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3038 RAISE FND_API.G_EXC_ERROR;
3039 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3040 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3041 END IF;
3042
3043
3044 IF ( p_route_id IS NULL OR
3045 p_route_id = FND_API.G_MISS_NUM OR
3046 p_object_version_number IS NULL OR
3047 p_object_version_number = FND_API.G_MISS_NUM ) THEN
3048 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
3049 FND_MSG_PUB.add;
3050 RAISE FND_API.G_EXC_ERROR;
3051 END IF;
3052
3053 get_route_record
3054 (
3055 x_return_status => l_return_status,
3056 x_msg_data => l_msg_data,
3057 p_route_id => p_route_id,
3058 p_object_version_number => p_object_version_number,
3059 p_x_route_rec => l_old_route_rec
3060 );
3061
3062 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
3063 FND_MESSAGE.set_name( 'AHL', l_msg_data );
3064 FND_MSG_PUB.add;
3065 RAISE FND_API.G_EXC_ERROR;
3066 END IF;
3067
3068 -- Check if the Status is COMPLETE
3069 IF ( l_old_route_rec.revision_status_code <> 'COMPLETE' ) THEN
3070 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RT_STATUS_NOT_COMPLETE' );
3071 FND_MESSAGE.set_token( 'RECORD', l_old_route_rec.route_no );
3072 FND_MSG_PUB.add;
3073 RAISE FND_API.G_EXC_ERROR;
3074 END IF;
3075
3076 -- Check if this revision is not Terminated
3077 IF ( l_old_route_rec.active_end_date IS NOT NULL ) THEN
3078 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_END_DATE_NOT_NULL' );
3079 FND_MESSAGE.set_token( 'RECORD', l_old_route_rec.route_no );
3080 FND_MSG_PUB.add;
3081 RAISE FND_API.G_EXC_ERROR;
3082 END IF;
3083
3084 -- Check if this revision is the latest complete revision of this Route
3085 OPEN get_latest_revision( l_old_route_rec.route_no );
3086
3087 FETCH get_latest_revision INTO
3088 l_revision_number;
3089
3090 IF ( l_revision_number <> l_old_route_rec.revision_number ) THEN
3091 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RT_REVISION_NOT_LATEST' );
3092 FND_MESSAGE.set_token( 'RECORD', l_old_route_rec.route_no );
3093 FND_MSG_PUB.add;
3094 CLOSE get_latest_revision;
3095 RAISE FND_API.G_EXC_ERROR;
3096 END IF;
3097
3098 CLOSE get_latest_revision;
3099
3100 -- Default the Active Start Date
3101 IF ( TRUNC( l_old_route_rec.active_start_date ) < TRUNC( SYSDATE ) ) THEN
3102 l_old_route_rec.active_start_date := SYSDATE;
3103 END IF;
3104
3105 -- Create copy of the route in AHL_ROUTES_B and AHL_ROUTES_TL
3106 BEGIN
3107
3108 l_revision_number := l_revision_number + 1;
3109
3110 -- Get the Route ID from the Sequence
3111 SELECT AHL_ROUTES_B_S.NEXTVAL
3112 INTO l_route_id
3113 FROM DUAL;
3114
3115 -- Insert the record
3116 AHL_ROUTES_PKG.insert_row
3117 (
3118 X_ROWID => l_rowid ,
3119 X_ROUTE_ID => l_route_id ,
3120 X_OBJECT_VERSION_NUMBER => 1 ,
3121 X_ROUTE_NO => l_old_route_rec.route_no ,
3122 X_APPLICATION_USG_CODE => rtrim(ltrim(FND_PROFILE.value( 'AHL_APPLN_USAGE' ))),
3123 X_REVISION_NUMBER => l_revision_number ,
3124 X_REVISION_STATUS_CODE => 'DRAFT' ,
3125 X_UNIT_RECEIPT_UPDATE_FLAG => l_old_route_rec.unit_receipt_update_flag , --pdoki Bug 6504159.
3126 X_START_DATE_ACTIVE => l_old_route_rec.active_start_date ,
3127 X_END_DATE_ACTIVE => NULL ,
3128 X_OPERATOR_PARTY_ID => l_old_route_rec.operator_party_id ,
3129 X_QA_INSPECTION_TYPE => l_old_route_rec.qa_inspection_type ,
3130 X_SERVICE_ITEM_ID => l_old_route_rec.service_item_id ,
3131 X_SERVICE_ITEM_ORG_ID => l_old_route_rec.service_item_org_id ,
3132 X_TASK_TEMPLATE_GROUP_ID => l_old_route_rec.task_template_group_id ,
3133 X_ACCOUNTING_CLASS_CODE => l_old_route_rec.accounting_class_code ,
3134 X_ACCOUNTING_CLASS_ORG_ID => l_old_route_rec.accounting_class_org_id ,
3135 X_ROUTE_TYPE_CODE => l_old_route_rec.route_type_code ,
3136 X_PRODUCT_TYPE_CODE => l_old_route_rec.product_type_code ,
3137 --bachandr Enigma Phase I changes -- start
3138 X_MODEL_CODE => l_old_route_rec.model_code ,
3139 X_ENIGMA_PUBLISH_DATE => l_old_route_rec.enigma_publish_date,
3140 X_ENIGMA_DOC_ID => l_old_route_rec.enigma_doc_id ,
3141 X_ENIGMA_ROUTE_ID => l_old_route_rec.enigma_route_id ,
3142 X_FILE_ID => l_old_route_rec.file_id,
3143 --bachandr Enigma Phase I changes -- end
3144 X_ZONE_CODE => l_old_route_rec.zone_code ,
3145 X_SUB_ZONE_CODE => l_old_route_rec.sub_zone_code ,
3146 X_PROCESS_CODE => l_old_route_rec.process_code ,
3147 X_TIME_SPAN => l_old_route_rec.time_span ,
3148 X_SEGMENT1 => l_old_route_rec.segment1 ,
3149 X_SEGMENT2 => l_old_route_rec.segment2 ,
3150 X_SEGMENT3 => l_old_route_rec.segment3 ,
3151 X_SEGMENT4 => l_old_route_rec.segment4 ,
3152 X_SEGMENT5 => l_old_route_rec.segment5 ,
3153 X_SEGMENT6 => l_old_route_rec.segment6 ,
3154 X_SEGMENT7 => l_old_route_rec.segment7 ,
3155 X_SEGMENT8 => l_old_route_rec.segment8 ,
3156 X_SEGMENT9 => l_old_route_rec.segment9 ,
3157 X_SEGMENT10 => l_old_route_rec.segment10 ,
3158 X_SEGMENT11 => l_old_route_rec.segment11 ,
3159 X_SEGMENT12 => l_old_route_rec.segment12 ,
3160 X_SEGMENT13 => l_old_route_rec.segment13 ,
3161 X_SEGMENT14 => l_old_route_rec.segment14 ,
3162 X_SEGMENT15 => l_old_route_rec.segment15 ,
3163 X_ATTRIBUTE_CATEGORY => l_old_route_rec.attribute_category ,
3164 X_ATTRIBUTE1 => l_old_route_rec.attribute1 ,
3165 X_ATTRIBUTE2 => l_old_route_rec.attribute2 ,
3166 X_ATTRIBUTE3 => l_old_route_rec.attribute3 ,
3167 X_ATTRIBUTE4 => l_old_route_rec.attribute4 ,
3168 X_ATTRIBUTE5 => l_old_route_rec.attribute5 ,
3169 X_ATTRIBUTE6 => l_old_route_rec.attribute6 ,
3170 X_ATTRIBUTE7 => l_old_route_rec.attribute7 ,
3171 X_ATTRIBUTE8 => l_old_route_rec.attribute8 ,
3172 X_ATTRIBUTE9 => l_old_route_rec.attribute9 ,
3173 X_ATTRIBUTE10 => l_old_route_rec.attribute10 ,
3174 X_ATTRIBUTE11 => l_old_route_rec.attribute11 ,
3175 X_ATTRIBUTE12 => l_old_route_rec.attribute12 ,
3176 X_ATTRIBUTE13 => l_old_route_rec.attribute13 ,
3177 X_ATTRIBUTE14 => l_old_route_rec.attribute14 ,
3178 X_ATTRIBUTE15 => l_old_route_rec.attribute15 ,
3179 X_TITLE => l_old_route_rec.title ,
3180 X_REMARKS => l_old_route_rec.remarks ,
3181 X_REVISION_NOTES => NULL ,
3182 X_CREATION_DATE => SYSDATE ,
3183 X_CREATED_BY => FND_GLOBAL.user_id ,
3184 X_LAST_UPDATE_DATE => SYSDATE ,
3185 X_LAST_UPDATED_BY => FND_GLOBAL.user_id ,
3186 X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id,
3187 X_ENABLED_FLAG => 'Y',
3188 X_SUMMARY_FLAG => 'N'
3189 );
3190
3191 EXCEPTION
3192 WHEN NO_DATA_FOUND THEN
3193 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_RECORD_CHANGED' );
3194 FND_MSG_PUB.add;
3195 WHEN OTHERS THEN
3196 IF ( SQLCODE = -1 ) THEN
3197 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_NO_DUP' );
3198 FND_MSG_PUB.add;
3199 ELSE
3200 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)THEN
3201 fnd_log.string
3202 (
3203 fnd_log.level_unexpected,
3204 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
3205 'AHL_ROUTES_PKG.insert_row error = ['||SQLERRM||']'
3206 );
3207 END IF;
3208 END IF;
3209 END;
3210
3211 -- Create copies of the route associations
3212 -- 0.Copy Route Effectivities
3213 FOR l_get_route_efcts IN get_route_efcts(p_route_id) LOOP
3214 SELECT ahl_route_effectivities_s.nextval into l_route_effectivity_id
3215 FROM dual;
3216 INSERT INTO ahl_route_effectivities
3217 (
3218 route_effectivity_id
3219 , route_id
3220 , inventory_item_id
3221 , inventory_master_org_id
3222 , mc_id
3223 , mc_header_id ,
3224 OBJECT_VERSION_NUMBER,
3225 LAST_UPDATE_DATE,
3226 LAST_UPDATED_BY,
3227 CREATION_DATE,
3228 CREATED_BY,
3229 LAST_UPDATE_LOGIN,
3230 security_group_id,
3231 ATTRIBUTE_CATEGORY,
3232 ATTRIBUTE1,
3233 ATTRIBUTE2,
3234 ATTRIBUTE3,
3235 ATTRIBUTE4,
3236 ATTRIBUTE5,
3237 ATTRIBUTE6,
3238 ATTRIBUTE7,
3239 ATTRIBUTE8,
3240 ATTRIBUTE9,
3241 ATTRIBUTE10,
3242 ATTRIBUTE11,
3243 ATTRIBUTE12,
3244 ATTRIBUTE13,
3245 ATTRIBUTE14,
3246 ATTRIBUTE15
3247 )
3248 VALUES
3249 (
3250 l_route_effectivity_id,
3251 l_route_id,
3252 l_get_route_efcts.inventory_item_id,
3253 l_get_route_efcts.inventory_master_org_id,
3254 l_get_route_efcts.mc_id,
3255 l_get_route_efcts.mc_header_id,
3256 1,
3257 SYSDATE,
3258 FND_GLOBAL.user_id,
3259 SYSDATE,
3260 FND_GLOBAL.user_id,
3261 FND_GLOBAL.user_id,
3262 l_get_route_efcts.security_group_id,
3263 l_get_route_efcts.ATTRIBUTE_CATEGORY,
3264 l_get_route_efcts.ATTRIBUTE1,
3265 l_get_route_efcts.ATTRIBUTE2,
3266 l_get_route_efcts.ATTRIBUTE3,
3267 l_get_route_efcts.ATTRIBUTE4,
3268 l_get_route_efcts.ATTRIBUTE5,
3269 l_get_route_efcts.ATTRIBUTE6,
3270 l_get_route_efcts.ATTRIBUTE7,
3271 l_get_route_efcts.ATTRIBUTE8,
3272 l_get_route_efcts.ATTRIBUTE9,
3273 l_get_route_efcts.ATTRIBUTE10,
3274 l_get_route_efcts.ATTRIBUTE11,
3275 l_get_route_efcts.ATTRIBUTE12,
3276 l_get_route_efcts.ATTRIBUTE13,
3277 l_get_route_efcts.ATTRIBUTE14,
3278 l_get_route_efcts.ATTRIBUTE15
3279 );
3280
3281 INSERT INTO AHL_RT_OPER_MATERIALS
3282 (
3283 RT_OPER_MATERIAL_ID,
3284 OBJECT_VERSION_NUMBER,
3285 OBJECT_ID,
3286 ASSOCIATION_TYPE_CODE,
3287 POSITION_PATH_ID,
3288 ITEM_GROUP_ID,
3289 INVENTORY_ITEM_ID,
3290 INVENTORY_ORG_ID,
3291 UOM_CODE,
3292 QUANTITY,
3293 ITEM_COMP_DETAIL_ID,
3294 EXCLUDE_FLAG,
3295 REWORK_PERCENT,
3296 REPLACE_PERCENT,
3297 ATTRIBUTE_CATEGORY,
3298 ATTRIBUTE1,
3299 ATTRIBUTE2,
3300 ATTRIBUTE3,
3301 ATTRIBUTE4,
3302 ATTRIBUTE5,
3303 ATTRIBUTE6,
3304 ATTRIBUTE7,
3305 ATTRIBUTE8,
3306 ATTRIBUTE9,
3307 ATTRIBUTE10,
3308 ATTRIBUTE11,
3309 ATTRIBUTE12,
3310 ATTRIBUTE13,
3311 ATTRIBUTE14,
3312 ATTRIBUTE15,
3313 LAST_UPDATE_DATE,
3314 LAST_UPDATED_BY,
3315 CREATION_DATE,
3316 CREATED_BY,
3317 LAST_UPDATE_LOGIN,
3318 IN_SERVICE --pdoki added for OGMA 105 issue
3319 ) SELECT
3320 AHL_RT_OPER_MATERIALS_S.NEXTVAL,
3321 1,
3322 l_route_effectivity_id,
3323 'DISPOSITION',
3324 position_path_id ,
3325 item_group_id,
3326 inventory_item_id,
3327 inventory_org_id,
3328 uom_code,
3329 quantity,
3330 item_comp_detail_id,
3331 exclude_flag,
3332 rework_percent,
3333 replace_percent,
3334 attribute_category,
3335 attribute1,
3336 attribute2,
3337 attribute3,
3338 attribute4,
3339 attribute5,
3340 attribute6,
3341 attribute7,
3342 attribute8,
3343 attribute9,
3344 attribute10,
3345 attribute11,
3346 attribute12,
3347 attribute13,
3348 attribute14,
3349 attribute15,
3350 last_update_date,
3351 last_updated_by,
3352 creation_date,
3353 created_by,
3354 last_update_login,
3355 in_service --pdoki added for OGMA 105 issue
3356 FROM AHL_RT_OPER_MATERIALS
3357 WHERE OBJECT_ID = l_get_route_efcts.route_effectivity_id
3358 ;
3359
3360 END LOOP;
3361
3362 -- 1.Copy Material Requirements
3363 INSERT INTO AHL_RT_OPER_MATERIALS
3364 (
3365 RT_OPER_MATERIAL_ID,
3366 OBJECT_VERSION_NUMBER,
3367 OBJECT_ID,
3368 ASSOCIATION_TYPE_CODE,
3369 ITEM_GROUP_ID,
3370 INVENTORY_ITEM_ID,
3371 INVENTORY_ORG_ID,
3372 UOM_CODE,
3373 QUANTITY,
3374 ATTRIBUTE_CATEGORY,
3375 ATTRIBUTE1,
3376 ATTRIBUTE2,
3377 ATTRIBUTE3,
3378 ATTRIBUTE4,
3379 ATTRIBUTE5,
3380 ATTRIBUTE6,
3381 ATTRIBUTE7,
3382 ATTRIBUTE8,
3383 ATTRIBUTE9,
3384 ATTRIBUTE10,
3385 ATTRIBUTE11,
3386 ATTRIBUTE12,
3387 ATTRIBUTE13,
3388 ATTRIBUTE14,
3389 ATTRIBUTE15,
3390 EXCLUDE_FLAG,
3391 LAST_UPDATE_DATE,
3392 LAST_UPDATED_BY,
3393 CREATION_DATE,
3394 CREATED_BY,
3395 LAST_UPDATE_LOGIN,
3396 IN_SERVICE --pdoki added for OGMA 105 issue
3397 )
3398 SELECT
3399 AHL_RT_OPER_MATERIALS_S.NEXTVAL,
3400 1,
3401 l_route_id,
3402 ASSOCIATION_TYPE_CODE,
3403 ITEM_GROUP_ID,
3404 INVENTORY_ITEM_ID,
3405 INVENTORY_ORG_ID,
3406 UOM_CODE,
3407 QUANTITY,
3408 ATTRIBUTE_CATEGORY,
3409 ATTRIBUTE1,
3410 ATTRIBUTE2,
3411 ATTRIBUTE3,
3412 ATTRIBUTE4,
3413 ATTRIBUTE5,
3414 ATTRIBUTE6,
3415 ATTRIBUTE7,
3416 ATTRIBUTE8,
3417 ATTRIBUTE9,
3418 ATTRIBUTE10,
3419 ATTRIBUTE11,
3420 ATTRIBUTE12,
3421 ATTRIBUTE13,
3422 ATTRIBUTE14,
3423 ATTRIBUTE15,
3424 EXCLUDE_FLAG,
3425 SYSDATE,
3426 FND_GLOBAL.user_id,
3427 SYSDATE,
3428 FND_GLOBAL.user_id,
3429 FND_GLOBAL.login_id,
3430 IN_SERVICE --pdoki added for OGMA 105 issue
3431 FROM AHL_RT_OPER_MATERIALS
3432 WHERE object_id = p_route_id
3433 AND association_type_code = 'ROUTE';
3434
3435 -- 2.Copy Resource Requirements and Alternate Resources
3436 FOR l_get_rt_oper_resources IN get_rt_oper_resources(p_route_id) LOOP
3437 SELECT ahl_rt_oper_resources_s.nextval into l_rt_oper_resource_id
3438 FROM dual;
3439 INSERT INTO AHL_RT_OPER_RESOURCES
3440 (
3441 RT_OPER_RESOURCE_ID,
3442 OBJECT_VERSION_NUMBER,
3443 OBJECT_ID,
3444 ASSOCIATION_TYPE_CODE,
3445 ASO_RESOURCE_ID,
3446 QUANTITY,
3447 DURATION,
3448 ACTIVITY_ID,
3449 COST_BASIS_ID,
3450 SCHEDULED_TYPE_ID,
3451 AUTOCHARGE_TYPE_ID,
3452 STANDARD_RATE_FLAG,
3453 ATTRIBUTE_CATEGORY,
3454 ATTRIBUTE1,
3455 ATTRIBUTE2,
3456 ATTRIBUTE3,
3457 ATTRIBUTE4,
3458 ATTRIBUTE5,
3459 ATTRIBUTE6,
3460 ATTRIBUTE7,
3461 ATTRIBUTE8,
3462 ATTRIBUTE9,
3463 ATTRIBUTE10,
3464 ATTRIBUTE11,
3465 ATTRIBUTE12,
3466 ATTRIBUTE13,
3467 ATTRIBUTE14,
3468 ATTRIBUTE15,
3469 LAST_UPDATE_DATE,
3470 LAST_UPDATED_BY,
3471 CREATION_DATE,
3472 CREATED_BY,
3473 LAST_UPDATE_LOGIN,
3474 -- Bug # 7644260 (FP for ER # 6998882) -- start
3475 SCHEDULE_SEQ
3476 -- Bug # 7644260 (FP for ER # 6998882) -- end
3477 )
3478 VALUES
3479 (
3480 l_rt_oper_resource_id,
3481 1,
3482 l_route_id,
3483 l_get_rt_oper_resources.ASSOCIATION_TYPE_CODE,
3484 l_get_rt_oper_resources.ASO_RESOURCE_ID,
3485 l_get_rt_oper_resources.QUANTITY,
3486 l_get_rt_oper_resources.DURATION,
3487 l_get_rt_oper_resources.ACTIVITY_ID,
3488 l_get_rt_oper_resources.COST_BASIS_ID,
3489 l_get_rt_oper_resources.SCHEDULED_TYPE_ID,
3490 l_get_rt_oper_resources.AUTOCHARGE_TYPE_ID,
3491 l_get_rt_oper_resources.STANDARD_RATE_FLAG,
3492 l_get_rt_oper_resources.ATTRIBUTE_CATEGORY,
3493 l_get_rt_oper_resources.ATTRIBUTE1,
3494 l_get_rt_oper_resources.ATTRIBUTE2,
3495 l_get_rt_oper_resources.ATTRIBUTE3,
3496 l_get_rt_oper_resources.ATTRIBUTE4,
3497 l_get_rt_oper_resources.ATTRIBUTE5,
3498 l_get_rt_oper_resources.ATTRIBUTE6,
3499 l_get_rt_oper_resources.ATTRIBUTE7,
3500 l_get_rt_oper_resources.ATTRIBUTE8,
3501 l_get_rt_oper_resources.ATTRIBUTE9,
3502 l_get_rt_oper_resources.ATTRIBUTE10,
3503 l_get_rt_oper_resources.ATTRIBUTE11,
3504 l_get_rt_oper_resources.ATTRIBUTE12,
3505 l_get_rt_oper_resources.ATTRIBUTE13,
3506 l_get_rt_oper_resources.ATTRIBUTE14,
3507 l_get_rt_oper_resources.ATTRIBUTE15,
3508 SYSDATE,
3509 FND_GLOBAL.user_id,
3510 SYSDATE,
3511 FND_GLOBAL.user_id,
3512 FND_GLOBAL.login_id,
3513 -- Bug # 7644260 (FP for ER # 6998882) -- start
3514 l_get_rt_oper_resources.SCHEDULE_SEQ
3515 -- Bug # 7644260 (FP for ER # 6998882) -- end
3516 );
3517
3518 INSERT INTO AHL_ALTERNATE_RESOURCES
3519 (
3520 ALTERNATE_RESOURCE_ID,
3521 OBJECT_VERSION_NUMBER,
3522 LAST_UPDATE_DATE,
3523 LAST_UPDATED_BY,
3524 CREATION_DATE,
3525 CREATED_BY,
3526 LAST_UPDATE_LOGIN,
3527 RT_OPER_RESOURCE_ID,
3528 ASO_RESOURCE_ID,
3529 PRIORITY,
3530 ATTRIBUTE_CATEGORY,
3531 ATTRIBUTE1,
3532 ATTRIBUTE2,
3533 ATTRIBUTE3,
3534 ATTRIBUTE4,
3535 ATTRIBUTE5,
3536 ATTRIBUTE6,
3537 ATTRIBUTE7,
3538 ATTRIBUTE8,
3539 ATTRIBUTE9,
3540 ATTRIBUTE10,
3541 ATTRIBUTE11,
3542 ATTRIBUTE12,
3543 ATTRIBUTE13,
3544 ATTRIBUTE14,
3545 ATTRIBUTE15
3546 )
3547 SELECT
3548 AHL_ALTERNATE_RESOURCES_S.NEXTVAL,
3549 1,
3550 SYSDATE,
3551 FND_GLOBAL.user_id,
3552 SYSDATE,
3553 FND_GLOBAL.user_id,
3554 FND_GLOBAL.login_id,
3555 l_rt_oper_resource_id,
3556 aso_resource_id,
3557 priority,
3558 ATTRIBUTE_CATEGORY,
3559 ATTRIBUTE1,
3560 ATTRIBUTE2,
3561 ATTRIBUTE3,
3562 ATTRIBUTE4,
3563 ATTRIBUTE5,
3564 ATTRIBUTE6,
3565 ATTRIBUTE7,
3566 ATTRIBUTE8,
3567 ATTRIBUTE9,
3568 ATTRIBUTE10,
3569 ATTRIBUTE11,
3570 ATTRIBUTE12,
3571 ATTRIBUTE13,
3572 ATTRIBUTE14,
3573 ATTRIBUTE15
3574 FROM AHL_ALTERNATE_RESOURCES
3575 WHERE rt_oper_resource_id = l_get_rt_oper_resources.rt_oper_resource_id;
3576 END LOOP;
3577
3578 -- 3.Copy Reference Documents
3579 FOR I in get_doc_associations( p_route_id ) LOOP
3580 SELECT AHL_DOC_TITLE_ASSOS_B_S.NEXTVAL
3581 INTO l_doc_title_assos_id
3582 FROM DUAL;
3583
3584 AHL_DOC_TITLE_ASSOS_PKG.insert_row
3585 (
3586 X_ROWID => l_rowid,
3587 X_DOC_TITLE_ASSO_ID => l_doc_title_assos_id,
3588 X_SERIAL_NO => I.serial_no,
3589 X_ATTRIBUTE_CATEGORY => I.attribute_category,
3590 X_ATTRIBUTE1 => I.attribute1,
3591 X_ATTRIBUTE2 => I.attribute1,
3592 X_ATTRIBUTE3 => I.attribute1,
3593 X_ATTRIBUTE4 => I.attribute1,
3594 X_ATTRIBUTE5 => I.attribute1,
3595 X_ATTRIBUTE6 => I.attribute1,
3596 X_ATTRIBUTE7 => I.attribute1,
3597 X_ATTRIBUTE8 => I.attribute1,
3598 X_ATTRIBUTE9 => I.attribute1,
3599 X_ATTRIBUTE10 => I.attribute1,
3600 X_ATTRIBUTE11 => I.attribute1,
3601 X_ATTRIBUTE12 => I.attribute1,
3602 X_ATTRIBUTE13 => I.attribute1,
3603 X_ATTRIBUTE14 => I.attribute1,
3604 X_ATTRIBUTE15 => I.attribute1,
3605 X_ASO_OBJECT_TYPE_CODE => 'ROUTE',
3606 X_SOURCE_REF_CODE => I.source_ref_code,
3607 X_ASO_OBJECT_ID => l_route_id,
3608 X_DOCUMENT_ID => I.document_id,
3609 X_USE_LATEST_REV_FLAG => I.use_latest_rev_flag,
3610 X_DOC_REVISION_ID => I.doc_revision_id,
3611 X_OBJECT_VERSION_NUMBER => 1,
3612 X_CHAPTER => I.chapter,
3613 X_SECTION => I.section,
3614 X_SUBJECT => I.subject,
3615 X_FIGURE => I.figure,
3616 X_PAGE => I.page,
3617 X_NOTE => I.note,
3618 X_CREATION_DATE => SYSDATE,
3619 X_CREATED_BY => fnd_global.user_id ,
3620 X_LAST_UPDATE_DATE => SYSDATE,
3621 X_LAST_UPDATED_BY => fnd_global.user_id ,
3622 X_LAST_UPDATE_LOGIN => fnd_global.login_id
3623 );
3624 END LOOP;
3625 -- 4.Copy Associated Operations
3626 INSERT INTO AHL_ROUTE_OPERATIONS
3627 (
3628 ROUTE_OPERATION_ID,
3629 OBJECT_VERSION_NUMBER,
3630 ROUTE_ID,
3631 OPERATION_ID,
3632 STEP,
3633 CHECK_POINT_FLAG,
3634 ATTRIBUTE_CATEGORY,
3635 ATTRIBUTE1,
3636 ATTRIBUTE2,
3637 ATTRIBUTE3,
3638 ATTRIBUTE4,
3639 ATTRIBUTE5,
3640 ATTRIBUTE6,
3641 ATTRIBUTE7,
3642 ATTRIBUTE8,
3643 ATTRIBUTE9,
3644 ATTRIBUTE10,
3645 ATTRIBUTE11,
3646 ATTRIBUTE12,
3647 ATTRIBUTE13,
3648 ATTRIBUTE14,
3649 ATTRIBUTE15,
3650 LAST_UPDATE_DATE,
3651 LAST_UPDATED_BY,
3652 CREATION_DATE,
3653 CREATED_BY,
3654 LAST_UPDATE_LOGIN
3655 )
3656 SELECT
3657 AHL_ROUTE_OPERATIONS_S.NEXTVAL,
3658 1,
3659 l_route_id,
3660 OPERATION_ID,
3661 STEP,
3662 CHECK_POINT_FLAG,
3663 ATTRIBUTE_CATEGORY,
3664 ATTRIBUTE1,
3665 ATTRIBUTE2,
3666 ATTRIBUTE3,
3667 ATTRIBUTE4,
3668 ATTRIBUTE5,
3669 ATTRIBUTE6,
3670 ATTRIBUTE7,
3671 ATTRIBUTE8,
3672 ATTRIBUTE9,
3673 ATTRIBUTE10,
3674 ATTRIBUTE11,
3675 ATTRIBUTE12,
3676 ATTRIBUTE13,
3677 ATTRIBUTE14,
3678 ATTRIBUTE15,
3679 SYSDATE,
3680 FND_GLOBAL.user_id,
3681 SYSDATE,
3682 FND_GLOBAL.user_id,
3683 FND_GLOBAL.login_id
3684 FROM AHL_ROUTE_OPERATIONS
3685 WHERE route_id = p_route_id;
3686
3687 -- Adithya added to fix bug# 6525763
3688 -- 5.Copy Access Panels
3689 INSERT INTO AHL_RT_OPER_ACCESS_PANELS
3690 (
3691 RT_OPER_PANEL_ID,
3692 OBJECT_VERSION_NUMBER,
3693 OBJECT_ID,
3694 ASSOCIATION_TYPE_CODE,
3695 PANEL_TYPE_ID,
3696 ATTRIBUTE_CATEGORY,
3697 ATTRIBUTE1,
3698 ATTRIBUTE2,
3699 ATTRIBUTE3,
3700 ATTRIBUTE4,
3701 ATTRIBUTE5,
3702 ATTRIBUTE6,
3703 ATTRIBUTE7,
3704 ATTRIBUTE8,
3705 ATTRIBUTE9,
3706 ATTRIBUTE10,
3707 ATTRIBUTE11,
3708 ATTRIBUTE12,
3709 ATTRIBUTE13,
3710 ATTRIBUTE14,
3711 ATTRIBUTE15,
3712 LAST_UPDATE_DATE,
3713 LAST_UPDATED_BY,
3714 CREATION_DATE,
3715 CREATED_BY,
3716 LAST_UPDATE_LOGIN
3717 )
3718 SELECT
3719 AHL_RT_OPER_ACCESS_PANELS_S.NEXTVAL,
3720 1,
3721 l_route_id,
3722 ASSOCIATION_TYPE_CODE,
3723 PANEL_TYPE_ID,
3724 ATTRIBUTE_CATEGORY,
3725 ATTRIBUTE1,
3726 ATTRIBUTE2,
3727 ATTRIBUTE3,
3728 ATTRIBUTE4,
3729 ATTRIBUTE5,
3730 ATTRIBUTE6,
3731 ATTRIBUTE7,
3732 ATTRIBUTE8,
3733 ATTRIBUTE9,
3734 ATTRIBUTE10,
3735 ATTRIBUTE11,
3736 ATTRIBUTE12,
3737 ATTRIBUTE13,
3738 ATTRIBUTE14,
3739 ATTRIBUTE15,
3740 SYSDATE,
3741 FND_GLOBAL.user_id,
3742 SYSDATE,
3743 FND_GLOBAL.user_id,
3744 FND_GLOBAL.login_id
3745 FROM AHL_RT_OPER_ACCESS_PANELS
3746 WHERE object_id = p_route_id
3747 AND association_type_code = 'ROUTE';
3748
3749 -- Set the OUT values.
3750 x_route_id := l_route_id;
3751
3752 -- Perform the Commit (if requested)
3753 IF FND_API.to_boolean( p_commit ) THEN
3754 COMMIT WORK;
3755 END IF;
3756
3757 -- Count and Get messages (optional)
3758 FND_MSG_PUB.count_and_get
3759 (
3760 p_encoded => FND_API.G_FALSE,
3761 p_count => x_msg_count,
3762 p_data => x_msg_data
3763 );
3764
3765 -- Disable debug (if enabled)
3766 IF ( G_DEBUG = 'Y' ) THEN
3767 AHL_DEBUG_PUB.disable_debug;
3768 END IF;
3769
3770 EXCEPTION
3771
3772 WHEN FND_API.G_EXC_ERROR THEN
3773 ROLLBACK TO create_route_revision_PVT;
3774 x_return_status := FND_API.G_RET_STS_ERROR ;
3775 FND_MSG_PUB.count_and_get
3776 (
3777 p_encoded => FND_API.G_FALSE,
3778 p_count => x_msg_count,
3779 p_data => x_msg_data
3780 );
3781
3782 -- Disable debug (if enabled)
3783 IF ( G_DEBUG = 'Y' ) THEN
3784 AHL_DEBUG_PUB.disable_debug;
3785 END IF;
3786
3787 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3788 ROLLBACK TO create_route_revision_PVT;
3789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3790 FND_MSG_PUB.count_and_get
3791 (
3792 p_encoded => FND_API.G_FALSE,
3793 p_count => x_msg_count,
3794 p_data => x_msg_data
3795 );
3796
3797 -- Disable debug (if enabled)
3798 IF ( G_DEBUG = 'Y' ) THEN
3799 AHL_DEBUG_PUB.disable_debug;
3800 END IF;
3801
3802 WHEN OTHERS THEN
3803 ROLLBACK TO create_route_revision_PVT;
3804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3805 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
3806 THEN
3807 FND_MSG_PUB.add_exc_msg
3808 (
3809 p_pkg_name => G_PKG_NAME,
3810 p_procedure_name => l_api_name,
3811 p_error_text => SUBSTRB(SQLERRM,1,240)
3812 );
3813 END IF;
3814 FND_MSG_PUB.count_and_get
3815 (
3816 p_encoded => FND_API.G_FALSE,
3817 p_count => x_msg_count,
3818 p_data => x_msg_data
3819 );
3820
3821 -- Disable debug (if enabled)
3822 IF ( G_DEBUG = 'Y' ) THEN
3823 AHL_DEBUG_PUB.disable_debug;
3824 END IF;
3825
3826 END create_route_revision;
3827 END AHL_RM_ROUTE_PVT;