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