[Home] [Help]
PACKAGE BODY: APPS.AHL_RA_NR_PROFILE_PVT
Source
1 PACKAGE BODY AHL_RA_NR_PROFILE_PVT AS
2 /* $Header: AHLVNRPB.pls 120.0.12020000.2 2012/12/07 00:53:55 sareepar noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_RA_NR_PROFILE_PVT';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6
7 G_DEBUG_LINE_NUM NUMBER;
8
9
10 -- Populates Nonroutine Profile Identification Material Details
11
12 PROCEDURE populate_prof_mat_req
13 (
14 p_mr_header_id IN NUMBER,
15 p_instance_id IN NUMBER,
16 p_ue_id IN NUMBER,
17 p_plan_id IN NUMBER,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 x_prof_mat_req_tbl OUT NOCOPY nrp_mat_req_tbl_type
22 )
23 IS
24
25 CURSOR get_ue_item_instance(c_ue_id NUMBER, c_instance_id NUMBER)
26 IS
27 SELECT fleet_header_id
28 FROM ahl_unit_effectivities_b
29 WHERE unit_effectivity_id = c_ue_id
30 AND csi_item_instance_id = c_instance_id ;
31
32 CURSOR get_ue_item_instance_sim(c_ue_id NUMBER, c_instance_id NUMBER)
33 IS
34 SELECT fleet_header_id
35 FROM ahl_ue_simulations
36 WHERE simulation_ue_id = c_ue_id
37 AND csi_item_instance_id = c_instance_id ;
38
39 CURSOR get_fleet_details(c_fleet_header_id NUMBER)
40 IS
41 SELECT fhb.operating_org_id, fhb.operations_type_code
42 FROM ahl_fleet_headers_b fhb
43 WHERE fhb.fleet_header_id = c_fleet_header_id
44 AND fhb.status_code = 'COMPLETE';
45
46 CURSOR get_profile_ident_seqs(c_mr_header_id NUMBER)
47 IS
48 SELECT nph.nr_profile_header_id, nph.ident_seq_third , nph.ident_seq_fourth
49 FROM ahl_mr_headers_b mrh, ahl_nr_profile_headers nph
50 WHERE mrh.title = nph.mr_title
51 AND mrh.mr_header_id = c_mr_header_id
52 AND nph.status_code = 'COMPLETE';
53
54 CURSOR get_material_details(c_nr_profile_header_id NUMBER)
55 IS
56 SELECT nr_profile_det_id, inventory_item_id, uom_code, required_qty, stage_type_code,
57 fleet_header_id, operating_org_id, operations_type_code
58 FROM ahl_nr_profile_details
59 WHERE nr_profile_header_id = c_nr_profile_header_id
60 AND inventory_item_id IS NOT NULL
61 AND excluded_date IS NULL
62 ORDER BY stage_type_code, inventory_item_id, fleet_header_id, operating_org_id, operations_type_code;
63
64 CURSOR get_mat_det_no_fleet(c_nr_profile_header_id NUMBER)
65 IS
66 SELECT nr_profile_det_id, inventory_item_id, uom_code, required_qty, stage_type_code
67 FROM ahl_nr_profile_details
68 WHERE nr_profile_header_id = c_nr_profile_header_id
69 AND inventory_item_id IS NOT NULL
70 AND excluded_date IS NULL
71 AND fleet_header_id IS NULL
72 AND operating_org_id IS NULL
73 AND operations_type_code IS NULL
74 ORDER BY stage_type_code, inventory_item_id;
75
76 CURSOR get_primary_plan_flag(c_plan_id NUMBER)
77 IS
78 SELECT primary_plan_flag
79 FROM ahl_simulation_plans_b
80 WHERE simulation_plan_id = c_plan_id
81 AND status_code = 'ACTIVE';
82
83
84 l_fleet_header_id NUMBER;
85 l_operating_org_id NUMBER;
86 l_operations_type_code VARCHAR2(30);
87
88 l_nr_profile_header_id NUMBER;
89 l_ident_seq_third VARCHAR2(30);
90 l_ident_seq_fourth VARCHAR2(30);
91
92
93 l_inventory_item_id NUMBER := -1;
94 l_stage_type_code VARCHAR2(30) := 'X';
95
96 l_primary_plan_flag VARCHAR2(1) := 'Y';
97
98 i NUMBER := 1;
99 l_prof_mat_req_tbl nrp_mat_req_tbl_type;
100
101 l_api_name VARCHAR2(80) := 'populate_prof_mat_req';
102
103 BEGIN
104
105 -- Add debug mesg.
106 IF G_DEBUG = 'Y' THEN
107 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Begin private API:' );
108 END IF;
109
110 SAVEPOINT sp_populate_prof_mat_req;
111
112 -- Initialize API return status to success
113 x_return_status := FND_API.G_RET_STS_SUCCESS;
114
115 -- Check Whether profile exist or not
116
117 OPEN get_profile_ident_seqs(p_mr_header_id);
118 FETCH get_profile_ident_seqs INTO l_nr_profile_header_id, l_ident_seq_third, l_ident_seq_fourth;
119 IF (get_profile_ident_seqs%NOTFOUND)
120 THEN
121 IF G_DEBUG = 'Y' THEN
122 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'Nonroutine MR Profile Not Exist' );
123 END IF;
124 CLOSE get_profile_ident_seqs;
125
126 GOTO end_of_mat_procedure;
127 END IF;
128 CLOSE get_profile_ident_seqs;
129
130 -- Get the primary plan flag, If it is primary derive fleet header id from ahl_unit_effectivities_b
131 -- Else derive fleet header id from ahl_ue_simulations
132
133 IF(p_plan_id IS NOT NULL)
134 THEN
135 OPEN get_primary_plan_flag(p_plan_id);
136 FETCH get_primary_plan_flag INTO l_primary_plan_flag;
137 IF (get_primary_plan_flag%NOTFOUND)
138 THEN
139 IF G_DEBUG = 'Y' THEN
140 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'Invalid plan_id' || p_plan_id );
141 END IF;
142 CLOSE get_primary_plan_flag;
143 GOTO end_of_mat_procedure;
144 END IF;
145 CLOSE get_primary_plan_flag;
146 END IF;
147
148 IF(l_primary_plan_flag = 'Y')
149 THEN
150 -- Check given UE _id and Instance_id or valid or not in unit effectivities table
151 OPEN get_ue_item_instance(p_ue_id,p_instance_id);
152 FETCH get_ue_item_instance INTO l_fleet_header_id;
153 IF (get_ue_item_instance%NOTFOUND)
154 THEN
155 IF G_DEBUG = 'Y' THEN
156 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'Invalid UE_ID and ITEM_INSTANCE_ID Combination for Unit Effectivities table' );
157 END IF;
158 CLOSE get_ue_item_instance;
159 GOTO end_of_mat_procedure;
160 END IF;
161 CLOSE get_ue_item_instance;
162 ELSE
163 -- Check given UE _id and Instance_id or valid or not in simulations table
164 OPEN get_ue_item_instance_sim(p_ue_id,p_instance_id);
165 FETCH get_ue_item_instance_sim INTO l_fleet_header_id;
166 IF (get_ue_item_instance_sim%NOTFOUND)
167 THEN
168 IF G_DEBUG = 'Y' THEN
169 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'Invalid UE_ID and ITEM_INSTANCE_ID Combination for Simulatin plan table');
170 END IF;
171 CLOSE get_ue_item_instance_sim;
172 GOTO end_of_mat_procedure;
173 END IF;
174 CLOSE get_ue_item_instance_sim;
175 END IF;
176
177
178 -- If UE is based on fleet Apply Profile Identification Sequence
179 IF (l_fleet_header_id IS NOT NULL)
180 THEN
181 -- Get Operating_org, Operations_type for a given fleet_header_id
182 OPEN get_fleet_details(l_fleet_header_id);
183 FETCH get_fleet_details INTO l_operating_org_id, l_operations_type_code;
184
185 IF (get_fleet_details%NOTFOUND)
186 THEN
187 CLOSE get_fleet_details;
188 IF G_DEBUG = 'Y' THEN
189 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'Invalid Fleet Header Id ::' || l_fleet_header_id);
190 END IF;
191 GOTO end_of_mat_procedure;
192 END IF;
193
194 IF G_DEBUG = 'Y' THEN
195 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'Fleet Details l_fleet_header_id:'||l_fleet_header_id||', l_operating_org_id:'||l_operating_org_id||', l_operations_type_code:'||l_operations_type_code);
196 END IF;
197 CLOSE get_fleet_details;
198
199 /*
200 Get all the items for a given nr_profile_header_id order by stage type , inventory item id
201 Check the conditions based on identification sequence Fleet, Operating org and Operations type, operating org and then operations type
202 */
203 FOR prof_mat_det_rec IN get_material_details(l_nr_profile_header_id)
204 LOOP
205
206 IF((l_stage_type_code = NVL(prof_mat_det_rec.stage_type_code,'X') AND l_inventory_item_id <> prof_mat_det_rec.inventory_item_id) OR l_stage_type_code <> NVL(prof_mat_det_rec.stage_type_code,'X'))
207 THEN
208
209 IF (prof_mat_det_rec.fleet_header_id IS NOT NULL AND prof_mat_det_rec.fleet_header_id = l_fleet_header_id )
210 THEN
211
212 l_prof_mat_req_tbl(i).nr_profile_det_id := prof_mat_det_rec.nr_profile_det_id ;
213 l_prof_mat_req_tbl(i).inventory_item_id := prof_mat_det_rec.inventory_item_id ;
214 l_prof_mat_req_tbl(i).uom_code := prof_mat_det_rec.uom_code ;
215 l_prof_mat_req_tbl(i).quantity := prof_mat_det_rec.required_qty ;
216 l_prof_mat_req_tbl(i).stage_type_code := prof_mat_det_rec.stage_type_code ;
217
218 l_inventory_item_id := prof_mat_det_rec.inventory_item_id ;
219 l_stage_type_code := NVL(prof_mat_det_rec.stage_type_code,'X') ;
220 i := i+1;
221
222 ELSIF (prof_mat_det_rec.fleet_header_id IS NULL AND prof_mat_det_rec.operating_org_id = l_operating_org_id AND prof_mat_det_rec.operations_type_code = l_operations_type_code)
223 THEN
224 l_prof_mat_req_tbl(i).nr_profile_det_id := prof_mat_det_rec.nr_profile_det_id ;
225 l_prof_mat_req_tbl(i).inventory_item_id := prof_mat_det_rec.inventory_item_id ;
226 l_prof_mat_req_tbl(i).uom_code := prof_mat_det_rec.uom_code ;
227 l_prof_mat_req_tbl(i).quantity := prof_mat_det_rec.required_qty ;
228 l_prof_mat_req_tbl(i).stage_type_code := prof_mat_det_rec.stage_type_code ;
229
230 l_inventory_item_id := prof_mat_det_rec.inventory_item_id ;
231 l_stage_type_code := NVL(prof_mat_det_rec.stage_type_code,'X') ;
232 i := i+1;
233
234 ELSIF (l_ident_seq_third IS NOT NULL AND l_ident_seq_third = 'ORGANIZATION' AND prof_mat_det_rec.fleet_header_id IS NULL AND prof_mat_det_rec.operating_org_id = l_operating_org_id AND prof_mat_det_rec.operations_type_code IS NULL)
235 THEN
236
237 l_prof_mat_req_tbl(i).nr_profile_det_id := prof_mat_det_rec.nr_profile_det_id ;
238 l_prof_mat_req_tbl(i).inventory_item_id := prof_mat_det_rec.inventory_item_id ;
239 l_prof_mat_req_tbl(i).uom_code := prof_mat_det_rec.uom_code ;
240 l_prof_mat_req_tbl(i).quantity := prof_mat_det_rec.required_qty ;
241 l_prof_mat_req_tbl(i).stage_type_code := prof_mat_det_rec.stage_type_code ;
242
243 l_inventory_item_id := prof_mat_det_rec.inventory_item_id ;
244 l_stage_type_code := NVL(prof_mat_det_rec.stage_type_code,'X') ;
245 i := i+1;
246
247 ELSIF (l_ident_seq_third IS NOT NULL AND l_ident_seq_third = 'OPERTYPE' AND prof_mat_det_rec.fleet_header_id IS NULL AND prof_mat_det_rec.operating_org_id IS NULL AND prof_mat_det_rec.operations_type_code = l_operations_type_code)
248 THEN
249
250 l_prof_mat_req_tbl(i).nr_profile_det_id := prof_mat_det_rec.nr_profile_det_id ;
251 l_prof_mat_req_tbl(i).inventory_item_id := prof_mat_det_rec.inventory_item_id ;
252 l_prof_mat_req_tbl(i).uom_code := prof_mat_det_rec.uom_code ;
253 l_prof_mat_req_tbl(i).quantity := prof_mat_det_rec.required_qty ;
254 l_prof_mat_req_tbl(i).stage_type_code := prof_mat_det_rec.stage_type_code ;
255
256 l_inventory_item_id := prof_mat_det_rec.inventory_item_id ;
257 l_stage_type_code := NVL(prof_mat_det_rec.stage_type_code,'X') ;
258 i := i+1;
259 ELSIF (l_ident_seq_third IS NOT NULL AND l_ident_seq_fourth = 'ORGANIZATION' AND prof_mat_det_rec.fleet_header_id IS NULL AND prof_mat_det_rec.operating_org_id = l_operating_org_id AND prof_mat_det_rec.operations_type_code IS NULL)
260 THEN
261
262 l_prof_mat_req_tbl(i).nr_profile_det_id := prof_mat_det_rec.nr_profile_det_id ;
263 l_prof_mat_req_tbl(i).inventory_item_id := prof_mat_det_rec.inventory_item_id ;
264 l_prof_mat_req_tbl(i).uom_code := prof_mat_det_rec.uom_code ;
265 l_prof_mat_req_tbl(i).quantity := prof_mat_det_rec.required_qty ;
266 l_prof_mat_req_tbl(i).stage_type_code := prof_mat_det_rec.stage_type_code ;
267
268 l_inventory_item_id := prof_mat_det_rec.inventory_item_id ;
269 l_stage_type_code := NVL(prof_mat_det_rec.stage_type_code,'X') ;
270 i := i+1;
271
272 ELSIF (l_ident_seq_third IS NOT NULL AND l_ident_seq_fourth = 'OPERTYPE' AND prof_mat_det_rec.fleet_header_id IS NULL AND prof_mat_det_rec.operating_org_id IS NULL AND prof_mat_det_rec.operations_type_code = l_operations_type_code)
273 THEN
274
275 l_prof_mat_req_tbl(i).nr_profile_det_id := prof_mat_det_rec.nr_profile_det_id ;
276 l_prof_mat_req_tbl(i).inventory_item_id := prof_mat_det_rec.inventory_item_id ;
277 l_prof_mat_req_tbl(i).uom_code := prof_mat_det_rec.uom_code ;
278 l_prof_mat_req_tbl(i).quantity := prof_mat_det_rec.required_qty ;
279 l_prof_mat_req_tbl(i).stage_type_code := prof_mat_det_rec.stage_type_code ;
280
281 l_inventory_item_id := prof_mat_det_rec.inventory_item_id ;
282 l_stage_type_code := NVL(prof_mat_det_rec.stage_type_code,'X') ;
283 i := i+1;
284 ELSIF(prof_mat_det_rec.fleet_header_id IS NULL AND prof_mat_det_rec.operating_org_id IS NULL AND prof_mat_det_rec.operations_type_code IS NULL)
285 THEN
286 l_prof_mat_req_tbl(i).nr_profile_det_id := prof_mat_det_rec.nr_profile_det_id ;
287 l_prof_mat_req_tbl(i).inventory_item_id := prof_mat_det_rec.inventory_item_id ;
288 l_prof_mat_req_tbl(i).uom_code := prof_mat_det_rec.uom_code ;
289 l_prof_mat_req_tbl(i).quantity := prof_mat_det_rec.required_qty ;
290 l_prof_mat_req_tbl(i).stage_type_code := prof_mat_det_rec.stage_type_code ;
291
292 l_inventory_item_id := prof_mat_det_rec.inventory_item_id ;
293 l_stage_type_code := NVL(prof_mat_det_rec.stage_type_code,'X') ;
294 i := i+1;
295
296 END IF;
297
298 END IF;
299
300 END LOOP;
301
302 ELSE
303
304 FOR prof_mat_det_rec IN get_mat_det_no_fleet(l_nr_profile_header_id)
305 LOOP
306 l_prof_mat_req_tbl(i).nr_profile_det_id := prof_mat_det_rec.nr_profile_det_id ;
307 l_prof_mat_req_tbl(i).inventory_item_id := prof_mat_det_rec.inventory_item_id ;
308 l_prof_mat_req_tbl(i).uom_code := prof_mat_det_rec.uom_code ;
309 l_prof_mat_req_tbl(i).quantity := prof_mat_det_rec.required_qty ;
310 l_prof_mat_req_tbl(i).stage_type_code := prof_mat_det_rec.stage_type_code ;
311
312 i := i+1;
313
314 END LOOP;
315
316 END IF;
317
318 <<end_of_mat_procedure>>
319
320 x_prof_mat_req_tbl := l_prof_mat_req_tbl;
321
322 -- End of API
323 IF G_DEBUG = 'Y' THEN
324 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::End private API:' );
325 END IF;
326
327 EXCEPTION
328 WHEN OTHERS THEN
329 ROLLBACK TO sp_populate_prof_mat_req;
330 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
331
332 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
333 THEN
334 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_RA_NR_PROFILE_PVT',
335 p_procedure_name => 'POPULATE_PROF_MAT_REQ',
336 p_error_text => SUBSTR(SQLERRM,1,240));
337 END IF;
338 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
339 p_count => x_msg_count,
340 p_data => X_msg_data);
341
342
343 END populate_prof_mat_req;
344
345
346 -- Populates Nonroutine Profile ResourceDetials for a given MR,UE and instance
347 -- Which will be called from workorder API
348 -- After push to production , every time when MR added.
349
350 PROCEDURE populate_prof_res_req
351 (
352 p_mr_header_id IN NUMBER,
353 p_instance_id IN NUMBER,
354 p_ue_id IN NUMBER,
355 p_plan_id IN NUMBER,
356 x_return_status OUT NOCOPY VARCHAR2,
357 x_msg_count OUT NOCOPY NUMBER,
358 x_msg_data OUT NOCOPY VARCHAR2,
359 x_prof_res_req_tbl OUT NOCOPY nrp_res_req_tbl_type
360 )
361 IS
362 CURSOR get_ue_item_instance(c_ue_id NUMBER,c_instance_id NUMBER)
363 IS
364 SELECT FLEET_HEADER_ID
365 FROM ahl_unit_effectivities_b
366 WHERE unit_effectivity_id = c_ue_id
367 AND csi_item_instance_id = c_instance_id;
368
369 CURSOR get_ue_item_instance_sim(c_ue_id NUMBER, c_instance_id NUMBER)
370 IS
371 SELECT fleet_header_id
372 FROM ahl_ue_simulations
373 WHERE simulation_ue_id = c_ue_id
374 AND csi_item_instance_id = c_instance_id ;
375
376 CURSOR get_fleet_details(c_fleet_header_id NUMBER)
377 IS
378 SELECT fhb.operating_org_id, fhb.operations_type_code
379 FROM ahl_fleet_headers_b fhb
380 WHERE fhb.fleet_header_id = c_fleet_header_id
381 AND fhb.status_code = 'COMPLETE';
382
383 CURSOR get_profile_ident_seqs(c_mr_header_id NUMBER)
384 IS
385 SELECT nph.nr_profile_header_id, nph.ident_seq_third , nph.ident_seq_fourth
386 FROM ahl_mr_headers_b mrh, ahl_nr_profile_headers nph
387 WHERE mrh.title = nph.mr_title
388 AND mrh.mr_header_id = c_mr_header_id
389 AND nph.status_code = 'COMPLETE';
390
391 CURSOR get_resource_details(c_nr_profile_header_id NUMBER)
392 IS
393 SELECT nr_profile_det_id, cmro_resource_id, required_qty, stage_type_code,
394 fleet_header_id, operating_org_id, operations_type_code
395 FROM ahl_nr_profile_details
396 WHERE nr_profile_header_id = c_nr_profile_header_id
397 AND excluded_date IS NULL
398 AND cmro_resource_id IS NOT NULL
399 ORDER BY stage_type_code, cmro_resource_id, fleet_header_id, operating_org_id, operations_type_code;
400
401 CURSOR get_res_det_no_fleet(c_nr_profile_header_id NUMBER)
402 IS
403 SELECT nr_profile_det_id, cmro_resource_id, required_qty, stage_type_code
404 FROM ahl_nr_profile_details
405 WHERE nr_profile_header_id = c_nr_profile_header_id
406 AND excluded_date IS NULL
407 AND cmro_resource_id IS NOT NULL
408 AND fleet_header_id IS NULL
409 AND operating_org_id IS NULL
410 AND operations_type_code IS NULL
411 ORDER BY stage_type_code, cmro_resource_id;
412
413 CURSOR get_primary_plan_flag(c_plan_id NUMBER)
414 IS
415 SELECT primary_plan_flag
416 FROM ahl_simulation_plans_b
417 WHERE simulation_plan_id = c_plan_id
418 AND status_code = 'ACTIVE';
419
420 l_fleet_header_id NUMBER;
421 l_operating_org_id NUMBER;
422 l_operations_type_code VARCHAR2(30);
423
424 l_nr_profile_header_id NUMBER;
425 l_ident_seq_third VARCHAR2(30);
426 l_ident_seq_fourth VARCHAR2(30);
427
428 l_cmro_resource_id NUMBER := -1;
429 l_stage_type_code VARCHAR2(30) := 'X';
430
431 l_primary_plan_flag VARCHAR2(1) := 'Y';
432
433 i NUMBER := 1;
434 l_prof_res_req_tbl nrp_res_req_tbl_type;
435
436 l_api_name VARCHAR2(80) := 'populate_prof_res_req';
437
438
439 BEGIN
440
441 -- Add debug mesg.
442 IF G_DEBUG = 'Y' THEN
443 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Begin private API:' );
444 END IF;
445
446 SAVEPOINT sp_populate_prof_res_req;
447
448 -- Initialize API return status to success
449 x_return_status := FND_API.G_RET_STS_SUCCESS;
450
451 -- Check Whether profile exist or not
452
453 OPEN get_profile_ident_seqs(p_mr_header_id);
454 FETCH get_profile_ident_seqs INTO l_nr_profile_header_id, l_ident_seq_third, l_ident_seq_fourth;
455 IF get_profile_ident_seqs%NOTFOUND
456 THEN
457 IF G_DEBUG = 'Y' THEN
458 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '.No NR Profile exist for a given Maintenance Requirement');
459 END IF;
460
461 CLOSE get_profile_ident_seqs;
462 GOTO end_of_res_procedure;
463 END IF;
464 CLOSE get_profile_ident_seqs;
465
466 -- Get the primary plan flag, If it is primary derive fleet header id from ahl_unit_effectivities_b
467 -- Else derive fleet header id from ahl_ue_simulations
468
469 IF(p_plan_id IS NOT NULL)
470 THEN
471 OPEN get_primary_plan_flag(p_plan_id);
472 FETCH get_primary_plan_flag INTO l_primary_plan_flag;
473 IF (get_primary_plan_flag%NOTFOUND)
474 THEN
475 IF G_DEBUG = 'Y' THEN
476 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'Invalid plan_id' || p_plan_id );
477 END IF;
478 CLOSE get_primary_plan_flag;
479 GOTO end_of_res_procedure;
480 END IF;
481 CLOSE get_primary_plan_flag;
482 END IF;
483
484 IF(l_primary_plan_flag = 'Y')
485 THEN
486 -- Check given UE _id and Instance_id or valid or not in unit effectivities table
487 OPEN get_ue_item_instance(p_ue_id,p_instance_id);
488 FETCH get_ue_item_instance INTO l_fleet_header_id;
489 IF (get_ue_item_instance%NOTFOUND)
490 THEN
491 IF G_DEBUG = 'Y' THEN
492 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'Invalid UE_ID and ITEM_INSTANCE_ID Combination for Unit Effectivities table' );
493 END IF;
494 CLOSE get_ue_item_instance;
495 GOTO end_of_res_procedure;
496 END IF;
497 CLOSE get_ue_item_instance;
498 ELSE
499 -- Check given UE _id and Instance_id or valid or not in simulations table
500 OPEN get_ue_item_instance_sim(p_ue_id,p_instance_id);
501 FETCH get_ue_item_instance_sim INTO l_fleet_header_id;
502 IF (get_ue_item_instance_sim%NOTFOUND)
503 THEN
504 IF G_DEBUG = 'Y' THEN
505 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'Invalid UE_ID and ITEM_INSTANCE_ID Combination for Simulatin plan table');
506 END IF;
507 CLOSE get_ue_item_instance_sim;
508 GOTO end_of_res_procedure;
509 END IF;
510 CLOSE get_ue_item_instance_sim;
511 END IF;
512
513 -- If UE is based on fleet Apply identification Sequence
514 IF (l_fleet_header_id IS NOT NULL)
515 THEN
516 -- Get Fleet ID,operating_org, Operations_type for a given unit config header id
517 OPEN get_fleet_details(l_fleet_header_id);
518 FETCH get_fleet_details INTO l_operating_org_id, l_operations_type_code;
519 IF get_fleet_details%NOTFOUND
520 THEN
521 CLOSE get_fleet_details;
522 IF G_DEBUG = 'Y' THEN
523 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'Invalid Fleet Id ::'|| l_fleet_header_id);
524 END IF;
525 GOTO end_of_res_procedure;
526 END IF;
527 IF G_DEBUG = 'Y' THEN
528 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'Fleet Details l_fleet_header_id:'||l_fleet_header_id||', l_operating_org_id:'||l_operating_org_id||', l_operations_type_code:'||l_operations_type_code);
529 END IF;
530 CLOSE get_fleet_details;
531
532 /* Get all the items for a given nr_profile_header_id order by stage type , cmro resource id
533 Check the conditions based on identification sequence Fleet, Operating org and Operations type, operating org and then operations type
534 */
535 FOR prof_res_det_rec IN get_resource_details(l_nr_profile_header_id)
536 LOOP
537
538 IF((l_stage_type_code = NVL(prof_res_det_rec.stage_type_code,'X') AND l_cmro_resource_id <> prof_res_det_rec.cmro_resource_id) OR l_stage_type_code <> NVL(prof_res_det_rec.stage_type_code,'X'))
539 THEN
540
541 IF (prof_res_det_rec.fleet_header_id IS NOT NULL AND prof_res_det_rec.fleet_header_id = l_fleet_header_id )
542 THEN
543
544 l_prof_res_req_tbl(i).nr_profile_det_id := prof_res_det_rec.nr_profile_det_id ;
545 l_prof_res_req_tbl(i).cmro_resource_id := prof_res_det_rec.cmro_resource_id;
546 l_prof_res_req_tbl(i).stage_type_code := prof_res_det_rec.stage_type_code ;
547 l_prof_res_req_tbl(i).duration := prof_res_det_rec.required_qty ;
548
549 l_cmro_resource_id := NVL(prof_res_det_rec.cmro_resource_id,-1) ;
550 l_stage_type_code := NVL(prof_res_det_rec.stage_type_code,'X') ;
551 i := i+1;
552
553 ELSIF (prof_res_det_rec.fleet_header_id IS NULL AND prof_res_det_rec.operating_org_id = l_operating_org_id AND prof_res_det_rec.operations_type_code = l_operations_type_code)
554 THEN
555 l_prof_res_req_tbl(i).nr_profile_det_id := prof_res_det_rec.nr_profile_det_id ;
556 l_prof_res_req_tbl(i).cmro_resource_id := prof_res_det_rec.cmro_resource_id;
557 l_prof_res_req_tbl(i).stage_type_code := prof_res_det_rec.stage_type_code ;
558 l_prof_res_req_tbl(i).duration := prof_res_det_rec.required_qty ;
559
560 l_cmro_resource_id := NVL(prof_res_det_rec.cmro_resource_id,-1) ;
561 l_stage_type_code := NVL(prof_res_det_rec.stage_type_code,'X') ;
562 i := i+1;
563
564 ELSIF (l_ident_seq_third IS NOT NULL AND l_ident_seq_third = 'ORGANIZATION' AND prof_res_det_rec.fleet_header_id IS NULL AND prof_res_det_rec.operating_org_id = l_operating_org_id AND prof_res_det_rec.operations_type_code IS NULL)
565 THEN
566
567 l_prof_res_req_tbl(i).nr_profile_det_id := prof_res_det_rec.nr_profile_det_id ;
568 l_prof_res_req_tbl(i).cmro_resource_id := prof_res_det_rec.cmro_resource_id;
569 l_prof_res_req_tbl(i).stage_type_code := prof_res_det_rec.stage_type_code ;
570 l_prof_res_req_tbl(i).duration := prof_res_det_rec.required_qty ;
571
572 l_cmro_resource_id := NVL(prof_res_det_rec.cmro_resource_id,-1) ;
573 l_stage_type_code := NVL(prof_res_det_rec.stage_type_code,'X') ;
574 i := i+1;
575
576 ELSIF (l_ident_seq_third IS NOT NULL AND l_ident_seq_third = 'OPERTYPE' AND prof_res_det_rec.fleet_header_id IS NULL AND prof_res_det_rec.operating_org_id IS NULL AND prof_res_det_rec.operations_type_code = l_operations_type_code)
577 THEN
578
579 l_prof_res_req_tbl(i).nr_profile_det_id := prof_res_det_rec.nr_profile_det_id ;
580 l_prof_res_req_tbl(i).cmro_resource_id := prof_res_det_rec.cmro_resource_id;
581 l_prof_res_req_tbl(i).stage_type_code := prof_res_det_rec.stage_type_code ;
582 l_prof_res_req_tbl(i).duration := prof_res_det_rec.required_qty ;
583
584 l_cmro_resource_id := NVL(prof_res_det_rec.cmro_resource_id,-1) ;
585 l_stage_type_code := NVL(prof_res_det_rec.stage_type_code,'X') ;
586 i := i+1;
587 ELSIF (l_ident_seq_third IS NOT NULL AND l_ident_seq_fourth = 'ORGANIZATION' AND prof_res_det_rec.fleet_header_id IS NULL AND prof_res_det_rec.operating_org_id = l_operating_org_id AND prof_res_det_rec.operations_type_code IS NULL)
588 THEN
589
590 l_prof_res_req_tbl(i).nr_profile_det_id := prof_res_det_rec.nr_profile_det_id ;
591 l_prof_res_req_tbl(i).cmro_resource_id := prof_res_det_rec.cmro_resource_id;
592 l_prof_res_req_tbl(i).stage_type_code := prof_res_det_rec.stage_type_code ;
593 l_prof_res_req_tbl(i).duration := prof_res_det_rec.required_qty ;
594
595 l_cmro_resource_id := NVL(prof_res_det_rec.cmro_resource_id,-1) ;
596 l_stage_type_code := NVL(prof_res_det_rec.stage_type_code,'X') ;
597 i := i+1;
598
599 ELSIF (l_ident_seq_third IS NOT NULL AND l_ident_seq_fourth = 'OPERTYPE' AND prof_res_det_rec.fleet_header_id IS NULL AND prof_res_det_rec.operating_org_id IS NULL AND prof_res_det_rec.operations_type_code = l_operations_type_code)
600 THEN
601
602 l_prof_res_req_tbl(i).nr_profile_det_id := prof_res_det_rec.nr_profile_det_id ;
603 l_prof_res_req_tbl(i).cmro_resource_id := prof_res_det_rec.cmro_resource_id;
604 l_prof_res_req_tbl(i).stage_type_code := prof_res_det_rec.stage_type_code ;
605 l_prof_res_req_tbl(i).duration := prof_res_det_rec.required_qty ;
606
607 l_cmro_resource_id := NVL(prof_res_det_rec.cmro_resource_id,-1) ;
608 l_stage_type_code := NVL(prof_res_det_rec.stage_type_code,'X') ;
609 i := i+1;
610 ELSIF(prof_res_det_rec.fleet_header_id IS NULL AND prof_res_det_rec.operating_org_id IS NULL AND prof_res_det_rec.operations_type_code IS NULL)
611 THEN
612 l_prof_res_req_tbl(i).nr_profile_det_id := prof_res_det_rec.nr_profile_det_id ;
613 l_prof_res_req_tbl(i).cmro_resource_id := prof_res_det_rec.cmro_resource_id;
614 l_prof_res_req_tbl(i).stage_type_code := prof_res_det_rec.stage_type_code ;
615 l_prof_res_req_tbl(i).duration := prof_res_det_rec.required_qty ;
616
617 l_cmro_resource_id := NVL(prof_res_det_rec.cmro_resource_id,-1) ;
618 l_stage_type_code := NVL(prof_res_det_rec.stage_type_code,'X') ;
619 i := i+1;
620
621 END IF;
622
623 END IF;
624
625 END LOOP;
626
627 ELSE
628
629 FOR prof_res_det_rec IN get_res_det_no_fleet(l_nr_profile_header_id)
630 LOOP
631 l_prof_res_req_tbl(i).nr_profile_det_id := prof_res_det_rec.nr_profile_det_id ;
632 l_prof_res_req_tbl(i).cmro_resource_id := prof_res_det_rec.cmro_resource_id ;
633 l_prof_res_req_tbl(i).duration := prof_res_det_rec.required_qty ;
634 l_prof_res_req_tbl(i).stage_type_code := prof_res_det_rec.stage_type_code ;
635
636 i := i+1;
637 END LOOP;
638
639 END IF;
640
641 << end_of_res_procedure >>
642
643 x_prof_res_req_tbl := l_prof_res_req_tbl;
644
645 -- End of API
646 IF G_DEBUG = 'Y' THEN
647 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::End private API:' );
648 END IF;
649
650
651 EXCEPTION
652 WHEN OTHERS THEN
653 ROLLBACK TO sp_populate_prof_res_req;
654 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
655
656 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
657 THEN
658 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_RA_NR_PROFILE_PVT',
659 p_procedure_name => 'POPULATE_PROF_RES_REQ',
660 p_error_text => SUBSTR(SQLERRM,1,240));
661 END IF;
662 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
663 p_count => x_msg_count,
664 p_data => X_msg_data);
665
666 END populate_prof_res_req;
667
668 -- Function to get the Record details when Error occured
669 FUNCTION get_record_identifier
670 (
671 p_nr_prof_est_rec IN nr_prof_est_rec_type
672 ) RETURN VARCHAR2
673 IS
674
675 l_record_identifier VARCHAR2(2000) := '';
676
677 BEGIN
678
679 IF ( p_nr_prof_est_rec.cmro_resource_type IS NOT NULL AND
680 p_nr_prof_est_rec.cmro_resource_type <> FND_API.G_MISS_NUM ) THEN
681 l_record_identifier := l_record_identifier || p_nr_prof_est_rec.cmro_resource_type;
682 l_record_identifier := l_record_identifier || ' - ';
683 END IF;
684
685 IF ( p_nr_prof_est_rec.cmro_resource_name IS NOT NULL AND
686 p_nr_prof_est_rec.cmro_resource_name <> FND_API.G_MISS_CHAR ) THEN
687 l_record_identifier := l_record_identifier || p_nr_prof_est_rec.cmro_resource_name;
688 l_record_identifier := l_record_identifier || ' - ';
689 END IF;
690
691 IF ( p_nr_prof_est_rec.inventory_item IS NOT NULL AND
692 p_nr_prof_est_rec.inventory_item <> FND_API.G_MISS_CHAR ) THEN
693 l_record_identifier := l_record_identifier || p_nr_prof_est_rec.inventory_item;
694 l_record_identifier := l_record_identifier || ' - ';
695 IF ( p_nr_prof_est_rec.uom IS NOT NULL AND
696 p_nr_prof_est_rec.uom <> FND_API.G_MISS_CHAR ) THEN
697 l_record_identifier := l_record_identifier || p_nr_prof_est_rec.uom;
698 END IF;
699 l_record_identifier := l_record_identifier || ' - ';
700 END IF;
701
702 /*IF ( p_nr_prof_est_rec.analysis_qty IS NOT NULL AND
703 p_nr_prof_est_rec.analysis_qty <> FND_API.G_MISS_NUM ) THEN
704 l_record_identifier := l_record_identifier || p_nr_prof_est_rec.analysis_qty;
705 END IF;
706
707 l_record_identifier := l_record_identifier || ' - ';
708
709 IF ( p_nr_prof_est_rec.required_qty IS NOT NULL AND
710 p_nr_prof_est_rec.required_qty <> FND_API.G_MISS_NUM ) THEN
711 l_record_identifier := l_record_identifier || p_nr_prof_est_rec.required_qty;
712 END IF;
713
714 l_record_identifier := l_record_identifier || ' - ';*/
715
716 IF ( p_nr_prof_est_rec.stage_type IS NOT NULL AND
717 p_nr_prof_est_rec.stage_type <> FND_API.G_MISS_CHAR ) THEN
718 l_record_identifier := l_record_identifier || p_nr_prof_est_rec.stage_type;
719 END IF;
720
721 l_record_identifier := l_record_identifier || ' - ';
722
723 IF ( p_nr_prof_est_rec.demantra_updated_date IS NOT NULL AND
724 p_nr_prof_est_rec.demantra_updated_date <> FND_API.G_MISS_DATE ) THEN
725 l_record_identifier := l_record_identifier || p_nr_prof_est_rec.demantra_updated_date;
726 END IF;
727
728 l_record_identifier := l_record_identifier || ' - ';
729
730 IF ( p_nr_prof_est_rec.fleet_name IS NOT NULL AND
731 p_nr_prof_est_rec.fleet_name <> FND_API.G_MISS_CHAR ) THEN
732 l_record_identifier := l_record_identifier || p_nr_prof_est_rec.fleet_name;
733 END IF;
734
735 l_record_identifier := l_record_identifier || ' - ';
736
737 IF ( p_nr_prof_est_rec.operating_org_name IS NOT NULL AND
738 p_nr_prof_est_rec.operating_org_name <> FND_API.G_MISS_CHAR ) THEN
739 l_record_identifier := l_record_identifier || p_nr_prof_est_rec.operating_org_name;
740 END IF;
741
742 l_record_identifier := l_record_identifier || ' - ';
743
744 IF ( p_nr_prof_est_rec.operations_type IS NOT NULL AND
745 p_nr_prof_est_rec.operations_type <> FND_API.G_MISS_CHAR ) THEN
746 l_record_identifier := l_record_identifier || p_nr_prof_est_rec.operations_type;
747 END IF;
748
749 RETURN l_record_identifier;
750
751 END get_record_identifier;
752
753 -- Validation Procedure for Nonroutine Profile Estimates
754 PROCEDURE Validate_prof_details
755 (
756 x_return_status OUT NOCOPY VARCHAR2,
757 x_msg_count OUT NOCOPY NUMBER,
758 x_msg_data OUT NOCOPY VARCHAR2,
759 p_x_nr_prof_est_tbl IN OUT NOCOPY nr_prof_est_tbl_type
760 )
761 AS
762
763 CURSOR get_resource_id(c_resource_name VARCHAR2)
764 IS
765 SELECT resource_id
766 FROM ahl_resources ar
767 WHERE ar.name = c_resource_name;
768
769 CURSOR check_resource_id(c_resource_id NUMBER)
770 IS
771 SELECT 'X'
772 FROM ahl_resources
773 WHERE resource_id = c_resource_id;
774
775 CURSOR get_inventory_item_id(c_item_name VARCHAR2)
776 IS
777 SELECT inventory_item_id
778 FROM mtl_system_items_kfv
779 WHERE concatenated_segments = c_item_name;
780
781 CURSOR check_inventory_item_id(c_iteme_id NUMBER)
782 IS
783 SELECT 'X'
784 FROM mtl_system_items_kfv
785 WHERE inventory_item_id = c_iteme_id;
786
787 CURSOR get_uom_code(c_uom VARCHAR2)
788 IS
789 SELECT DISTINCT uom_code
790 FROM ahl_item_class_uom_v
791 WHERE unit_of_measure = c_uom;
792
793 CURSOR check_uom_code(c_uom_code VARCHAR2)
794 IS
795 SELECT 'X'
796 FROM ahl_item_class_uom_v
797 WHERE uom_code = c_uom_code;
798
799 CURSOR get_fleet_id(c_fleet_name VARCHAR2)
800 IS
801 SELECT fleet_header_id
802 FROM ahl_fleet_headers_vl flt
803 WHERE flt.name = c_fleet_name
804 AND status_code = 'COMPLETE';
805
806 CURSOR check_fleet_id(c_fleet_header_id NUMBER)
807 IS
808 SELECT 'X'
809 FROM ahl_fleet_headers_b
810 WHERE fleet_header_id = c_fleet_header_id
811 AND status_code = 'COMPLETE';
812
813 CURSOR get_operating_org_id(c_operating_org_name VARCHAR2)
814 IS
815 SELECT organization_id
816 FROM hr_all_organization_units hr
817 WHERE hr.name = c_operating_org_name;
818
819 CURSOR check_operating_org_id(c_operating_org_id NUMBER)
820 IS
821 SELECT 'X'
822 FROM hr_all_organization_units
823 WHERE organization_id = c_operating_org_id;
824
825 CURSOR get_object_version_number(c_nr_profile_det_id NUMBER,c_object_version_number NUMBER)
826 IS
827 SELECT object_version_number
828 FROM ahl_nr_profile_details
829 WHERE nr_profile_det_id = c_nr_profile_det_id
830 AND object_version_number = c_object_version_number;
831
832
833
834 l_dummy Varchar2(1);
835 l_object_version_number NUMBER;
836 l_return_status Varchar2(240);
837 l_msg_data Varchar2(2000);
838 l_msg_count NUMBER;
839 l_api_name VARCHAR2(80) := 'validate_prof_details';
840
841 BEGIN
842
843 -- Add debug mesg.
844 IF G_DEBUG = 'Y' THEN
845 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Begin private API:' );
846 END IF;
847
848 -- Initialize API return status to success
849 x_return_status := FND_API.G_RET_STS_SUCCESS;
850
851 -- Standard Start of API savepoint
852 SAVEPOINT Validate_prof_details_sp;
853
854 IF p_x_nr_prof_est_tbl.count > 0
855 THEN
856 FOR i IN p_x_nr_prof_est_tbl.FIRST .. p_x_nr_prof_est_tbl.LAST
857 LOOP
858 IF (p_x_nr_prof_est_tbl(i).dml_operation = 'C')
859 THEN
860
861 -- Check Whether Profile is created or not
862 IF(p_x_nr_prof_est_tbl(i).nr_profile_header_id IS NULL)
863 THEN
864 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_MR_TIT_MIS');
865 FND_MSG_PUB.add;
866 END IF;
867
868 /* Resource Type is not validated because it is not stored in Table : AHL_NR_PROFILE_DETAILS
869 It is showing in the UI,for filtering purpose*/
870 -- If Exluded date is NULL then validate the records other wise skip the validation
871 IF (p_x_nr_prof_est_tbl(i).excluded_date IS NULL)
872 THEN
873 -- Resource Validation
874 -- Get resource id for a given resource Name
875 -- If Resource id is not null validate id againest CMRO resources
876 IF ( p_x_nr_prof_est_tbl(i).cmro_resource_id IS NULL AND p_x_nr_prof_est_tbl(i).cmro_resource_name IS NOT NULL)
877 THEN
878 OPEN get_resource_id(p_x_nr_prof_est_tbl(i).cmro_resource_name);
879 FETCH get_resource_id into p_x_nr_prof_est_tbl(i).cmro_resource_id;
880 IF(get_resource_id%NOTFOUND)
881 THEN
882 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_INVL_RES_NAME' );
883 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i) ));
884 FND_MSG_PUB.add;
885 END IF;
886 CLOSE get_resource_id;
887 ELSIF (p_x_nr_prof_est_tbl(i).cmro_resource_id IS NOT NULL AND p_x_nr_prof_est_tbl(i).cmro_resource_id <> FND_API.G_MISS_NUM AND p_x_nr_prof_est_tbl(i).cmro_resource_name IS NULL)
888 THEN
889 OPEN check_resource_id(p_x_nr_prof_est_tbl(i).cmro_resource_id);
890 FETCH check_resource_id into l_dummy;
891 IF(check_resource_id%NOTFOUND)
892 THEN
893 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_INVL_RES_ID' );
894 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
895 FND_MSG_PUB.add;
896 END IF;
897 CLOSE check_resource_id;
898 ELSIF ( (p_x_nr_prof_est_tbl(i).cmro_resource_id IS NULL OR p_x_nr_prof_est_tbl(i).cmro_resource_id = FND_API.G_MISS_NUM)
899 AND (p_x_nr_prof_est_tbl(i).cmro_resource_name IS NULL OR p_x_nr_prof_est_tbl(i).cmro_resource_name = FND_API.G_MISS_CHAR)
900 AND p_x_nr_prof_est_tbl(i).profile_detail_type = G_RESOURCE)
901 THEN
902 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_RES_MAND' );
903 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
904 FND_MSG_PUB.add;
905 END IF;
906
907 -- Item Validtion (Material Estimations)
908 -- Get Inventory item id for a given item Name
909 -- If Item id is not null validate id againest items in inventory
910 IF ( p_x_nr_prof_est_tbl(i).inventory_item_id IS NULL AND p_x_nr_prof_est_tbl(i).inventory_item IS NOT NULL)
911 THEN
912 OPEN get_inventory_item_id(p_x_nr_prof_est_tbl(i).inventory_item);
913 FETCH get_inventory_item_id into p_x_nr_prof_est_tbl(i).inventory_item_id;
914 IF(get_inventory_item_id%NOTFOUND)
915 THEN
916 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_INVL_INV_ITEM_NAME' );
917 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i) ));
918 FND_MSG_PUB.add;
919 END IF;
920 CLOSE get_inventory_item_id;
921 ELSIF ( p_x_nr_prof_est_tbl(i).inventory_item_id IS NOT NULL AND p_x_nr_prof_est_tbl(i).inventory_item_id <> FND_API.G_MISS_NUM AND p_x_nr_prof_est_tbl(i).inventory_item IS NULL)
922 THEN
923 OPEN check_inventory_item_id(p_x_nr_prof_est_tbl(i).inventory_item_id);
924 FETCH check_inventory_item_id into l_dummy;
925 IF(check_inventory_item_id%NOTFOUND)
926 THEN
927 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_INVL_INV_ITEM_ID' );
928 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
929 FND_MSG_PUB.add;
930 END IF;
931 CLOSE check_inventory_item_id;
932 ELSIF ( (p_x_nr_prof_est_tbl(i).inventory_item_id IS NULL OR p_x_nr_prof_est_tbl(i).inventory_item_id = FND_API.G_MISS_NUM)
933 AND (p_x_nr_prof_est_tbl(i).inventory_item IS NULL OR p_x_nr_prof_est_tbl(i).inventory_item = FND_API.G_MISS_CHAR)
934 AND p_x_nr_prof_est_tbl(i).profile_detail_type = G_ITEM)
935 THEN
936 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_INVL_ITEM_MAND' );
937 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
938 FND_MSG_PUB.add;
939 END IF;
940
941 -- UOM Validation
942 -- Get the UOM code for given UOM if UOM code is NULL
943 -- Validate the given UOM is valid or not
944 IF ( p_x_nr_prof_est_tbl(i).uom_code IS NULL AND p_x_nr_prof_est_tbl(i).uom IS NOT NULL)
945 THEN
946 OPEN get_uom_code(p_x_nr_prof_est_tbl(i).uom);
947 FETCH get_uom_code into p_x_nr_prof_est_tbl(i).uom_code;
948 IF(get_uom_code%NOTFOUND)
949 THEN
950 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_UOM' );
951 FND_MESSAGE.set_token('FIELD', p_x_nr_prof_est_tbl(i).uom);
952 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i) ));
953 FND_MSG_PUB.add;
954 END IF;
955 CLOSE get_uom_code;
956 ELSIF ( p_x_nr_prof_est_tbl(i).uom_code IS NOT NULL AND p_x_nr_prof_est_tbl(i).uom_code <> FND_API.G_MISS_CHAR AND p_x_nr_prof_est_tbl(i).uom IS NULL)
957 THEN
958 OPEN check_uom_code(p_x_nr_prof_est_tbl(i).uom_code);
959 FETCH check_uom_code into l_dummy;
960 IF(check_uom_code%NOTFOUND)
961 THEN
962 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_UOM' );
963 FND_MESSAGE.set_token('FIELD', p_x_nr_prof_est_tbl(i).uom_code);
964 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
965 FND_MSG_PUB.add;
966 END IF;
967 CLOSE check_uom_code;
968 ELSIF ( (p_x_nr_prof_est_tbl(i).uom_code IS NULL OR p_x_nr_prof_est_tbl(i).uom_code = FND_API.G_MISS_CHAR)
969 AND (p_x_nr_prof_est_tbl(i).uom IS NULL OR p_x_nr_prof_est_tbl(i).uom = FND_API.G_MISS_CHAR)
970 AND p_x_nr_prof_est_tbl(i).profile_detail_type = G_ITEM)
971 THEN
972 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_UOM_NULL' );
973 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
974 FND_MSG_PUB.add;
975 END IF;
976
977 -- Stage Type Validation
978 -- Get the Stage Type Code or Meaning and validate
979 IF ( ( p_x_nr_prof_est_tbl(i).stage_type_code IS NOT NULL AND
980 p_x_nr_prof_est_tbl(i).stage_type_code <> FND_API.G_MISS_CHAR ) OR
981 ( p_x_nr_prof_est_tbl(i).stage_type IS NOT NULL AND
982 p_x_nr_prof_est_tbl(i).stage_type <> FND_API.G_MISS_CHAR ) )
983 THEN
984 AHL_RM_ROUTE_UTIL.validate_lookup
985 (
986 x_return_status => l_return_status,
987 x_msg_data => l_msg_data,
988 p_lookup_type => 'AHL_VWP_STAGE_TYPE',
989 p_lookup_meaning => p_x_nr_prof_est_tbl(i).stage_type,
990 p_x_lookup_code => p_x_nr_prof_est_tbl(i).stage_type_code
991 );
992
993 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
994 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
995 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_DEP_TYPE' );
996 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
997 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_TOO_MANY_STG_TYP' );
998 ELSE
999 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1000 END IF;
1001
1002 IF ( p_x_nr_prof_est_tbl(i).stage_type IS NULL OR
1003 p_x_nr_prof_est_tbl(i).stage_type = FND_API.G_MISS_CHAR )
1004 THEN
1005 p_x_nr_prof_est_tbl(i).stage_type := p_x_nr_prof_est_tbl(i).stage_type_code;
1006 FND_MESSAGE.set_token( 'FIELD', p_x_nr_prof_est_tbl(i).stage_type );
1007 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1008 ELSE
1009 FND_MESSAGE.set_token( 'FIELD', p_x_nr_prof_est_tbl(i).stage_type );
1010 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1011 END IF;
1012 FND_MSG_PUB.add;
1013 END IF;
1014 END IF;
1015
1016 -- Fleet Id Validation
1017 -- Get the Fleet id for a given Fleet Name and
1018 -- Also validate the fleet id is exist or not if Fleet id id given
1019 IF ( (p_x_nr_prof_est_tbl(i).fleet_header_id IS NULL) AND p_x_nr_prof_est_tbl(i).fleet_name IS NOT NULL AND p_x_nr_prof_est_tbl(i).fleet_name <> FND_API.G_MISS_CHAR )
1020 THEN
1021 OPEN get_fleet_id(p_x_nr_prof_est_tbl(i).fleet_name);
1022 FETCH get_fleet_id into p_x_nr_prof_est_tbl(i).fleet_header_id;
1023 IF(get_fleet_id%NOTFOUND)
1024 THEN
1025 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_INVL_FLEET_NAME' );
1026 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i) ));
1027 FND_MSG_PUB.add;
1028 END IF;
1029 CLOSE get_fleet_id;
1030 ELSIF ( (p_x_nr_prof_est_tbl(i).fleet_header_id IS NOT NULL) AND p_x_nr_prof_est_tbl(i).fleet_header_id <> FND_API.G_MISS_NUM AND p_x_nr_prof_est_tbl(i).fleet_name IS NULL)
1031 THEN
1032 OPEN check_fleet_id(p_x_nr_prof_est_tbl(i).fleet_header_id);
1033 FETCH check_fleet_id into l_dummy;
1034 IF(check_fleet_id%NOTFOUND)
1035 THEN
1036 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_INVL_FLEET_ID' );
1037 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1038 FND_MSG_PUB.add;
1039 END IF;
1040 CLOSE check_fleet_id;
1041 END IF;
1042
1043 -- Operating Organization Validation
1044 -- Get the Operating org id for a given Operating org name
1045 -- And also validate If Operating org id is given it exist or not
1046 IF ( (p_x_nr_prof_est_tbl(i).operating_org_id IS NULL) AND p_x_nr_prof_est_tbl(i).operating_org_name IS NOT NULL AND p_x_nr_prof_est_tbl(i).operating_org_name <> FND_API.G_MISS_CHAR)
1047 THEN
1048 OPEN get_operating_org_id(p_x_nr_prof_est_tbl(i).operating_org_name);
1049 FETCH get_operating_org_id into p_x_nr_prof_est_tbl(i).operating_org_id;
1050 IF(get_operating_org_id%NOTFOUND)
1051 THEN
1052 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_INVL_ORG_NAME' );
1053 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i) ));
1054 FND_MSG_PUB.add;
1055 END IF;
1056 CLOSE get_operating_org_id;
1057 ELSIF ( (p_x_nr_prof_est_tbl(i).operating_org_id IS NOT NULL) AND p_x_nr_prof_est_tbl(i).operating_org_id <> FND_API.G_MISS_NUM AND p_x_nr_prof_est_tbl(i).operating_org_name IS NULL)
1058 THEN
1059 OPEN check_operating_org_id(p_x_nr_prof_est_tbl(i).operating_org_id);
1060 FETCH check_operating_org_id into l_dummy;
1061 IF(check_operating_org_id%NOTFOUND)
1062 THEN
1063 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_INVL_ORG_ID' );
1064 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1065 FND_MSG_PUB.add;
1066 END IF;
1067 CLOSE check_operating_org_id;
1068 END IF;
1069
1070 -- Operations Type Validation
1071 -- Get the operations Type Code or Meaning and validate
1072 IF ( ( p_x_nr_prof_est_tbl(i).operations_type_code IS NOT NULL AND
1073 p_x_nr_prof_est_tbl(i).operations_type_code <> FND_API.G_MISS_CHAR ) OR
1074 ( p_x_nr_prof_est_tbl(i).operations_type IS NOT NULL AND
1075 p_x_nr_prof_est_tbl(i).operations_type <> FND_API.G_MISS_CHAR ) )
1076 THEN
1077 AHL_RM_ROUTE_UTIL.validate_lookup
1078 (
1079 x_return_status => l_return_status,
1080 x_msg_data => l_msg_data,
1081 p_lookup_type => 'AHL_FLT_OPERATIONS_TYPE',
1082 p_lookup_meaning => p_x_nr_prof_est_tbl(i).operations_type,
1083 p_x_lookup_code => p_x_nr_prof_est_tbl(i).operations_type_code
1084 );
1085
1086 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1087 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
1088 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_DEP_TYPE' );
1089 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
1090 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_TOO_MANY_OPR_TYP' );
1091 ELSE
1092 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1093 END IF;
1094
1095 IF ( p_x_nr_prof_est_tbl(i).operations_type IS NULL OR
1096 p_x_nr_prof_est_tbl(i).operations_type = FND_API.G_MISS_CHAR )
1097 THEN
1098 p_x_nr_prof_est_tbl(i).operations_type := p_x_nr_prof_est_tbl(i).operations_type_code;
1099 FND_MESSAGE.set_token( 'FIELD', p_x_nr_prof_est_tbl(i).operations_type);
1100 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1101 ELSE
1102 FND_MESSAGE.set_token( 'FIELD', p_x_nr_prof_est_tbl(i).operations_type);
1103 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1104 END IF;
1105
1106 FND_MSG_PUB.add;
1107 END IF;
1108 END IF;
1109
1110 /* IF Fleet is given , Operating org and Operations Type are not allowed*/
1111 IF(p_x_nr_prof_est_tbl(i).fleet_header_id IS NOT NULL AND (p_x_nr_prof_est_tbl(i).operations_type_code IS NOT NULL OR p_x_nr_prof_est_tbl(i).operating_org_id IS NOT NULL))
1112 THEN
1113 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_FLT_NO_ORG_OPR_TYP' );
1114 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1115 FND_MSG_PUB.add;
1116 END IF;
1117
1118 END IF; -- Exluded date Close
1119
1120 END IF; -- End if for DML_OPERATION
1121
1122 -- Validation for Required Quantity
1123 -- Required Quanity is mandatory, if record is updated or created
1124 IF( p_x_nr_prof_est_tbl(i).DML_OPERATION = 'C' OR p_x_nr_prof_est_tbl(i).DML_OPERATION = 'U')
1125 THEN
1126 IF ( p_x_nr_prof_est_tbl(i).required_qty IS NULL OR p_x_nr_prof_est_tbl(i).required_qty = FND_API.G_MISS_NUM )
1127 THEN
1128 IF(p_x_nr_prof_est_tbl(i).profile_detail_type = G_RESOURCE)
1129 THEN
1130 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_REQ_HRS_MAND' );
1131 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1132 FND_MSG_PUB.add;
1133 ELSIF(p_x_nr_prof_est_tbl(i).profile_detail_type = G_ITEM)
1134 THEN
1135 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_REQ_QTY_MAND' );
1136 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1137 FND_MSG_PUB.add;
1138 END IF;
1139 ELSIF(p_x_nr_prof_est_tbl(i).required_qty < 0)
1140 THEN
1141 IF(p_x_nr_prof_est_tbl(i).profile_detail_type = G_RESOURCE)
1142 THEN
1143 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_REQ_HRS_LESS_ZERO' );
1144 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1145 FND_MSG_PUB.add;
1146 ELSIF(p_x_nr_prof_est_tbl(i).profile_detail_type = G_ITEM)
1147 THEN
1148 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_REQ_QTY_LESS_ZERO' );
1149 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1150 FND_MSG_PUB.add;
1151 END IF;
1152 ELSIF(p_x_nr_prof_est_tbl(i).analysis_qty < 0)
1153 THEN
1154 IF(p_x_nr_prof_est_tbl(i).profile_detail_type = G_RESOURCE)
1155 THEN
1156 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_ANL_HRS_LESS_ZERO' );
1157 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1158 FND_MSG_PUB.add;
1159 ELSIF(p_x_nr_prof_est_tbl(i).profile_detail_type = G_ITEM)
1160 THEN
1161 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_ANL_QTY_LESS_ZERO' );
1162 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1163 FND_MSG_PUB.add;
1164 END IF;
1165 END IF;
1166 END IF;
1167
1168 --Object version Number Validation
1169 IF(p_x_nr_prof_est_tbl(i).DML_OPERATION = 'U' OR p_x_nr_prof_est_tbl(i).DML_OPERATION = 'D')
1170 THEN
1171 OPEN get_object_version_number(p_x_nr_prof_est_tbl(i).nr_profile_det_id, p_x_nr_prof_est_tbl(i).object_version_number);
1172 FETCH get_object_version_number INTO l_object_version_number;
1173 IF(get_object_version_number%NOTFOUND)
1174 THEN
1175 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RECORD_CHANGED' );
1176 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1177 FND_MSG_PUB.add;
1178 END IF;
1179 CLOSE get_object_version_number;
1180 END IF;
1181
1182 END LOOP; -- Table Loop
1183
1184 END IF; --Table Count
1185
1186 l_msg_count := FND_MSG_PUB.count_msg;
1187 IF l_msg_count > 0 THEN
1188 x_msg_count := l_msg_count;
1189 RAISE FND_API.G_EXC_ERROR;
1190 END IF;
1191
1192 -- End of API
1193 IF G_DEBUG = 'Y' THEN
1194 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::End of private API:' );
1195 END IF;
1196
1197 EXCEPTION
1198 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1199 ROLLBACK TO Validate_prof_details_sp;
1200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1201 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1202 p_count => x_msg_count,
1203 p_data => x_msg_data);
1204
1205 WHEN FND_API.G_EXC_ERROR THEN
1206 ROLLBACK TO Validate_prof_details_sp;
1207 x_return_status := FND_API.G_RET_STS_ERROR;
1208 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1209 p_count => x_msg_count,
1210 p_data => X_msg_data);
1211 WHEN OTHERS THEN
1212 ROLLBACK TO Validate_prof_details_sp;
1213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1214
1215 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1216 THEN
1217 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_RA_NR_PROFILE_PVT',
1218 p_procedure_name => 'VALIDATE_PROF_DETAILS',
1219 p_error_text => SUBSTR(SQLERRM,1,240));
1220 END IF;
1221 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1222 p_count => x_msg_count,
1223 p_data => X_msg_data);
1224
1225 END Validate_prof_details;
1226
1227
1228
1229 PROCEDURE add_details_to_prof
1230 (
1231 p_api_version IN NUMBER,
1232 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1233 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1234 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1235 x_return_status OUT NOCOPY VARCHAR2,
1236 x_msg_count OUT NOCOPY NUMBER,
1237 x_msg_data OUT NOCOPY VARCHAR2,
1238 p_x_nr_prof_est_tbl IN OUT NOCOPY nr_prof_est_tbl_type
1239 )
1240 AS
1241
1242 CURSOR check_duplicate_record (c_nr_profile_header_id NUMBER, c_cmro_resource_id NUMBER,c_inventory_item_id NUMBER,c_stage_type_code VARCHAR2,c_fleet_header_id NUMBER, c_operating_org_id NUMBER, c_operations_type_code VARCHAR2,c_analysis_qty NUMBER)
1243 IS
1244 SELECT nr_profile_det_id,required_qty,excluded_date
1245 FROM ahl_nr_profile_details
1246 WHERE NR_PROFILE_HEADER_ID = c_nr_profile_header_id
1247 AND NVL(CMRO_RESOURCE_ID,-1) = c_cmro_resource_id
1248 AND NVL(INVENTORY_ITEM_ID ,-1) = c_inventory_item_id
1249 AND NVL(STAGE_TYPE_CODE,'X') = c_stage_type_code
1250 AND NVL(FLEET_HEADER_ID,-1) = c_fleet_header_id
1251 AND NVL(OPERATING_ORG_ID,-1) = c_operating_org_id
1252 AND NVL(OPERATIONS_TYPE_CODE,'X') = c_operations_type_code
1253 AND NVL2(ANALYSIS_QTY, -2,-1) = c_analysis_qty;
1254
1255 CURSOR check_exclude_record (c_nr_profile_header_id NUMBER, c_cmro_resource_id NUMBER,c_inventory_item_id NUMBER,c_stage_type_code VARCHAR2,c_fleet_header_id NUMBER, c_operating_org_id NUMBER, c_operations_type_code VARCHAR2,c_analysis_qty NUMBER)
1256 IS
1257 SELECT nr_profile_det_id,required_qty,excluded_date
1258 FROM ahl_nr_profile_details
1259 WHERE NR_PROFILE_HEADER_ID = c_nr_profile_header_id
1260 AND NVL(CMRO_RESOURCE_ID,-1) = c_cmro_resource_id
1261 AND NVL(INVENTORY_ITEM_ID ,-1) = c_inventory_item_id
1262 AND NVL(STAGE_TYPE_CODE,'X') = c_stage_type_code
1263 AND NVL(FLEET_HEADER_ID,-1) = c_fleet_header_id
1264 AND NVL(OPERATING_ORG_ID,-1) = c_operating_org_id
1265 AND NVL(OPERATIONS_TYPE_CODE,'X') = c_operations_type_code
1266 AND NVL2(ANALYSIS_QTY, -2,-1) = c_analysis_qty
1267 AND excluded_date IS NOT NULL;
1268
1269 CURSOR get_analysis(c_nr_profile_det_id NUMBER)
1270 IS
1271 SELECT analysis_qty ,excluded_date, cmro_resource_id, inventory_item_id, stage_type_code, fleet_header_id ,operating_org_id, operations_type_code
1272 FROM ahl_nr_profile_details
1273 WHERE nr_profile_det_id = c_nr_profile_det_id;
1274
1275 l_nr_profile_det_id NUMBER;
1276 l_analysis_qty NUMBER;
1277 l_required_qty NUMBER;
1278 l_excluded_date DATE;
1279 l_msg_count NUMBER;
1280 l_msg_data VARCHAR2(2000);
1281 l_return_status VARCHAR2(1);
1282 l_api_version CONSTANT NUMBER := 1.0;
1283 l_dummy VARCHAR2(1);
1284
1285 l_temp_nr_profile_det_id NUMBER;
1286 l_cmro_resource_id NUMBER := -1;
1287 l_inventory_item_id NUMBER := -1;
1288 l_stage_type_code VARCHAR2(30) := 'X';
1289 l_fleet_header_id NUMBER := -1;
1290 l_operating_org_id NUMBER := -1;
1291 l_operations_type_code VARCHAR2(30) := 'X';
1292
1293 l_api_name VARCHAR2(80) := 'add_details_to_prof';
1294
1295 BEGIN
1296
1297 IF G_DEBUG = 'Y' THEN
1298 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Begin private API:' );
1299 END IF;
1300
1301 -- Initialize API return status to success
1302 x_return_status := FND_API.G_RET_STS_SUCCESS;
1303
1304 -- Standard Start of API savepoint
1305 SAVEPOINT add_details_to_prof_sp;
1306
1307 -- Standard call to check for call compatibility.
1308 IF NOT FND_API.compatible_api_call
1309 (
1310 l_api_version,
1311 p_api_version,
1312 l_api_name,
1313 G_PKG_NAME
1314 )
1315 THEN
1316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1317 END IF;
1318
1319 -- Initialize message list if p_init_msg_list is set to TRUE.
1320 IF FND_API.to_boolean( p_init_msg_list ) THEN
1321 FND_MSG_PUB.initialize;
1322 END IF;
1323
1324 /* Call the Validate _profile details procedure for all validations*/
1325 Validate_prof_details
1326 (
1327 x_return_status => l_return_status,
1328 x_msg_count => l_msg_count,
1329 x_msg_data => l_msg_data ,
1330 p_x_nr_prof_est_tbl => p_x_nr_prof_est_tbl
1331 );
1332
1333 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1334 RAISE FND_API.G_EXC_ERROR;
1335 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1337 END IF;
1338
1339 IF p_x_nr_prof_est_tbl.count > 0
1340 THEN
1341 FOR i IN p_x_nr_prof_est_tbl.FIRST .. p_x_nr_prof_est_tbl.LAST
1342 LOOP
1343 IF (p_x_nr_prof_est_tbl(i).dml_operation = 'C')
1344 THEN
1345 IF (p_x_nr_prof_est_tbl(i).nr_profile_det_id IS NULL)
1346 THEN
1347 p_x_nr_prof_est_tbl(i).nr_profile_det_id := AHL_NR_PROFILE_DETAIL_S.NEXTVAL;
1348 END IF;
1349
1350 IF(p_x_nr_prof_est_tbl(i).object_version_number IS NULL)
1351 THEN
1352 p_x_nr_prof_est_tbl(i).object_version_number := 1;
1353 END IF;
1354
1355 IF(p_x_nr_prof_est_tbl(i).analysis_qty IS NULL)
1356 THEN
1357
1358 -- Check with existing record having Analysis quantity as Not Null
1359 OPEN check_duplicate_record(p_x_nr_prof_est_tbl(i).nr_profile_header_id,
1360 NVL(p_x_nr_prof_est_tbl(i).cmro_resource_id,-1),
1361 NVL(p_x_nr_prof_est_tbl(i).inventory_item_id,-1),
1362 NVL(p_x_nr_prof_est_tbl(i).stage_type_code,'X'),
1363 NVL(p_x_nr_prof_est_tbl(i).fleet_header_id,-1),
1364 NVL(p_x_nr_prof_est_tbl(i).operating_org_id,-1),
1365 NVL(p_x_nr_prof_est_tbl(i).operations_type_code,'X'),-2);
1366
1367 FETCH check_duplicate_record INTO l_nr_profile_det_id , l_required_qty, l_excluded_date ;
1368
1369 IF(check_duplicate_record%FOUND)
1370 THEN
1371 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_DUP_REC' );
1372 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1373 FND_MSG_PUB.add;
1374 END IF;
1375
1376 CLOSE check_duplicate_record;
1377
1378 -- Check with existing record having Analysis quantity as null
1379 OPEN check_duplicate_record(p_x_nr_prof_est_tbl(i).nr_profile_header_id,
1380 NVL(p_x_nr_prof_est_tbl(i).cmro_resource_id,-1),
1381 NVL(p_x_nr_prof_est_tbl(i).inventory_item_id,-1),
1382 NVL(p_x_nr_prof_est_tbl(i).stage_type_code,'X'),
1383 NVL(p_x_nr_prof_est_tbl(i).fleet_header_id,-1),
1384 NVL(p_x_nr_prof_est_tbl(i).operating_org_id,-1),
1385 NVL(p_x_nr_prof_est_tbl(i).operations_type_code,'X'),-1);
1386
1387 FETCH check_duplicate_record INTO l_nr_profile_det_id , l_required_qty, l_excluded_date ;
1388
1389 IF(check_duplicate_record%NOTFOUND)
1390 THEN
1391 INSERT INTO AHL_NR_PROFILE_DETAILS
1392 (
1393 NR_PROFILE_DET_ID,
1394 OBJECT_VERSION_NUMBER,
1395 LAST_UPDATE_DATE,
1396 LAST_UPDATED_BY,
1397 CREATION_DATE,
1398 CREATED_BY,
1399 LAST_UPDATE_LOGIN,
1400 NR_PROFILE_HEADER_ID,
1401 STAGE_TYPE_CODE,
1402 INVENTORY_ITEM_ID,
1403 UOM_CODE,
1404 CMRO_RESOURCE_ID,
1405 ANALYSIS_QTY,
1406 REQUIRED_QTY,
1407 FLEET_HEADER_ID,
1408 OPERATING_ORG_ID,
1409 OPERATIONS_TYPE_CODE,
1410 DEMANTRA_UPDATED_DATE,
1411 EXCLUDED_DATE,
1412 SECURITY_GROUP_ID
1413 )
1414 VALUES
1415 (
1416 p_x_nr_prof_est_tbl(i).nr_profile_det_id,
1417 p_x_nr_prof_est_tbl(i).object_version_number,
1418 SYSDATE,
1419 FND_GLOBAL.user_id,
1420 SYSDATE,
1421 FND_GLOBAL.user_id,
1422 FND_GLOBAL.login_id,
1423 p_x_nr_prof_est_tbl(i).nr_profile_header_id,
1424 p_x_nr_prof_est_tbl(i).stage_type_code,
1425 p_x_nr_prof_est_tbl(i).inventory_item_id,
1426 p_x_nr_prof_est_tbl(i).uom_code,
1427 p_x_nr_prof_est_tbl(i).cmro_resource_id,
1428 p_x_nr_prof_est_tbl(i).analysis_qty,
1429 p_x_nr_prof_est_tbl(i).required_qty,
1430 p_x_nr_prof_est_tbl(i).fleet_header_id,
1431 p_x_nr_prof_est_tbl(i).operating_org_id,
1432 p_x_nr_prof_est_tbl(i).operations_type_code,
1433 p_x_nr_prof_est_tbl(i).demantra_updated_date,
1434 NULL,
1435 p_x_nr_prof_est_tbl(i).security_group_id
1436 );
1437 ELSE
1438 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_DUP_REC' );
1439 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1440 FND_MSG_PUB.add;
1441 END IF;
1442
1443 CLOSE check_duplicate_record;
1444
1445 ELSIF(p_x_nr_prof_est_tbl(i).analysis_qty IS NOT NULL AND p_x_nr_prof_est_tbl(i).excluded_date IS NULL)
1446 THEN
1447
1448 OPEN check_duplicate_record(p_x_nr_prof_est_tbl(i).nr_profile_header_id,
1449 NVL(p_x_nr_prof_est_tbl(i).cmro_resource_id,-1),
1450 NVL(p_x_nr_prof_est_tbl(i).inventory_item_id,-1),
1451 NVL(p_x_nr_prof_est_tbl(i).stage_type_code,'X'),
1452 NVL(p_x_nr_prof_est_tbl(i).fleet_header_id,-1),
1453 NVL(p_x_nr_prof_est_tbl(i).operating_org_id,-1),
1454 NVL(p_x_nr_prof_est_tbl(i).operations_type_code,'X'),-2);
1455
1456 FETCH check_duplicate_record INTO l_nr_profile_det_id , l_required_qty, l_excluded_date ;
1457 IF(check_duplicate_record%FOUND)
1458 THEN
1459
1460 UPDATE AHL_NR_PROFILE_DETAILS
1461 SET analysis_qty = p_x_nr_prof_est_tbl(i).analysis_qty,
1462 required_qty = p_x_nr_prof_est_tbl(i).required_qty,
1463 object_version_number = object_version_number + 1,
1464 last_update_date = SYSDATE,
1465 last_updated_by = FND_GLOBAL.user_id,
1466 last_update_login = FND_GLOBAL.login_id,
1467 demantra_updated_date = p_x_nr_prof_est_tbl(i).demantra_updated_date,
1468 excluded_date = NULL
1469 WHERE NR_PROFILE_DET_ID = l_nr_profile_det_id ;
1470 -- If Update command is not executed properly throw error message
1471 IF (SQL%NOTFOUND)
1472 THEN
1473 RAISE NO_DATA_FOUND;
1474 END IF;
1475
1476 -- Delete Excluded record after updating existing record
1477 OPEN check_exclude_record(p_x_nr_prof_est_tbl(i).nr_profile_header_id,
1478 NVL(p_x_nr_prof_est_tbl(i).cmro_resource_id,-1),
1479 NVL(p_x_nr_prof_est_tbl(i).inventory_item_id,-1),
1480 NVL(p_x_nr_prof_est_tbl(i).stage_type_code,'X'),
1481 NVL(p_x_nr_prof_est_tbl(i).fleet_header_id,-1),
1482 NVL(p_x_nr_prof_est_tbl(i).operating_org_id,-1),
1483 NVL(p_x_nr_prof_est_tbl(i).operations_type_code,'X'),-2);
1484
1485 FETCH check_exclude_record INTO l_nr_profile_det_id , l_required_qty, l_excluded_date;
1486
1487 IF(check_exclude_record%FOUND)
1488 THEN
1489 DELETE FROM AHL_NR_PROFILE_DETAILS
1490 WHERE NR_PROFILE_DET_ID = l_nr_profile_det_id;
1491
1492 IF (SQL%NOTFOUND)
1493 THEN
1494 RAISE NO_DATA_FOUND;
1495 END IF;
1496 END IF;
1497
1498 CLOSE check_exclude_record;
1499
1500 ELSE
1501 INSERT INTO AHL_NR_PROFILE_DETAILS
1502 (
1503 NR_PROFILE_DET_ID,
1504 OBJECT_VERSION_NUMBER,
1505 LAST_UPDATE_DATE,
1506 LAST_UPDATED_BY,
1507 CREATION_DATE,
1508 CREATED_BY,
1509 LAST_UPDATE_LOGIN,
1510 NR_PROFILE_HEADER_ID,
1511 STAGE_TYPE_CODE,
1512 INVENTORY_ITEM_ID,
1513 UOM_CODE,
1514 CMRO_RESOURCE_ID,
1515 ANALYSIS_QTY,
1516 REQUIRED_QTY,
1517 FLEET_HEADER_ID,
1518 OPERATING_ORG_ID,
1519 OPERATIONS_TYPE_CODE,
1520 DEMANTRA_UPDATED_DATE,
1521 EXCLUDED_DATE,
1522 SECURITY_GROUP_ID
1523 )
1524 VALUES
1525 (
1526 p_x_nr_prof_est_tbl(i).nr_profile_det_id,
1527 p_x_nr_prof_est_tbl(i).object_version_number,
1528 SYSDATE,
1529 FND_GLOBAL.user_id,
1530 SYSDATE,
1531 FND_GLOBAL.user_id,
1532 FND_GLOBAL.login_id,
1533 p_x_nr_prof_est_tbl(i).nr_profile_header_id,
1534 p_x_nr_prof_est_tbl(i).stage_type_code,
1535 p_x_nr_prof_est_tbl(i).inventory_item_id,
1536 p_x_nr_prof_est_tbl(i).uom_code,
1537 p_x_nr_prof_est_tbl(i).cmro_resource_id,
1538 p_x_nr_prof_est_tbl(i).analysis_qty,
1539 p_x_nr_prof_est_tbl(i).required_qty,
1540 p_x_nr_prof_est_tbl(i).fleet_header_id,
1541 p_x_nr_prof_est_tbl(i).operating_org_id,
1542 p_x_nr_prof_est_tbl(i).operations_type_code,
1543 p_x_nr_prof_est_tbl(i).demantra_updated_date,
1544 NULL,
1545 p_x_nr_prof_est_tbl(i).security_group_id
1546 );
1547 END IF;
1548 CLOSE check_duplicate_record;
1549
1550 ELSIF(p_x_nr_prof_est_tbl(i).analysis_qty IS NOT NULL AND p_x_nr_prof_est_tbl(i).excluded_date IS NOT NULL)
1551 THEN
1552 OPEN check_exclude_record(p_x_nr_prof_est_tbl(i).nr_profile_header_id,
1553 NVL(p_x_nr_prof_est_tbl(i).cmro_resource_id,-1),
1554 NVL(p_x_nr_prof_est_tbl(i).inventory_item_id,-1),
1555 NVL(p_x_nr_prof_est_tbl(i).stage_type_code,'X'),
1556 NVL(p_x_nr_prof_est_tbl(i).fleet_header_id,-1),
1557 NVL(p_x_nr_prof_est_tbl(i).operating_org_id,-1),
1558 NVL(p_x_nr_prof_est_tbl(i).operations_type_code,'X'),-2);
1559
1560 FETCH check_exclude_record INTO l_nr_profile_det_id , l_required_qty, l_excluded_date;
1561
1562 IF(check_exclude_record%FOUND)
1563 THEN
1564 FND_MESSAGE.set_name( 'AHL', 'AHL_RA_NP_REC_EXLD' );
1565 FND_MESSAGE.set_token('RECORD', get_record_identifier( p_x_nr_prof_est_tbl(i)) );
1566 FND_MSG_PUB.add;
1567 ELSE
1568
1569 INSERT INTO AHL_NR_PROFILE_DETAILS
1570 (
1571 NR_PROFILE_DET_ID,
1572 OBJECT_VERSION_NUMBER,
1573 LAST_UPDATE_DATE,
1574 LAST_UPDATED_BY,
1575 CREATION_DATE,
1576 CREATED_BY,
1577 LAST_UPDATE_LOGIN,
1578 NR_PROFILE_HEADER_ID,
1579 STAGE_TYPE_CODE,
1580 INVENTORY_ITEM_ID,
1581 UOM_CODE,
1582 CMRO_RESOURCE_ID,
1583 ANALYSIS_QTY,
1584 REQUIRED_QTY,
1585 FLEET_HEADER_ID,
1586 OPERATING_ORG_ID,
1587 OPERATIONS_TYPE_CODE,
1588 DEMANTRA_UPDATED_DATE,
1589 EXCLUDED_DATE,
1590 SECURITY_GROUP_ID
1591 )
1592 VALUES
1593 (
1594 p_x_nr_prof_est_tbl(i).nr_profile_det_id,
1595 p_x_nr_prof_est_tbl(i).object_version_number,
1596 SYSDATE,
1597 FND_GLOBAL.user_id,
1598 SYSDATE,
1599 FND_GLOBAL.user_id,
1600 FND_GLOBAL.login_id,
1601 p_x_nr_prof_est_tbl(i).nr_profile_header_id,
1602 p_x_nr_prof_est_tbl(i).stage_type_code,
1603 p_x_nr_prof_est_tbl(i).inventory_item_id,
1604 p_x_nr_prof_est_tbl(i).uom_code,
1605 p_x_nr_prof_est_tbl(i).cmro_resource_id,
1606 p_x_nr_prof_est_tbl(i).analysis_qty,
1607 p_x_nr_prof_est_tbl(i).required_qty,
1608 p_x_nr_prof_est_tbl(i).fleet_header_id,
1609 p_x_nr_prof_est_tbl(i).operating_org_id,
1610 p_x_nr_prof_est_tbl(i).operations_type_code,
1611 p_x_nr_prof_est_tbl(i).demantra_updated_date,
1612 p_x_nr_prof_est_tbl(i).excluded_date,
1613 p_x_nr_prof_est_tbl(i).security_group_id
1614 );
1615
1616 END IF;
1617
1618 CLOSE check_exclude_record;
1619
1620 END IF;
1621
1622 ELSIF (p_x_nr_prof_est_tbl(i).dml_operation = 'U')
1623 THEN
1624 UPDATE AHL_NR_PROFILE_DETAILS
1625 SET object_version_number = object_version_number +1,
1626 last_update_date = SYSDATE,
1627 last_updated_by = FND_GLOBAL.user_id,
1628 last_update_login = FND_GLOBAL.login_id,
1629 required_qty = p_x_nr_prof_est_tbl(i).required_qty
1630 WHERE NR_PROFILE_DET_ID = p_x_nr_prof_est_tbl(i).nr_profile_det_id ;
1631
1632 IF (SQL%NOTFOUND)
1633 THEN
1634 RAISE NO_DATA_FOUND;
1635 END IF;
1636 ELSIF (p_x_nr_prof_est_tbl(i).dml_operation = 'D') -- IF DML_OPERATION is 'D'
1637 THEN
1638
1639 OPEN get_analysis(p_x_nr_prof_est_tbl(i).nr_profile_det_id);
1640
1641 FETCH get_analysis INTO l_analysis_qty,l_excluded_date, l_cmro_resource_id, l_inventory_item_id, l_stage_type_code, l_fleet_header_id ,l_operating_org_id, l_operations_type_code;
1642
1643 IF(get_analysis%FOUND)
1644 THEN
1645 IF(l_analysis_qty IS NULL )
1646 THEN
1647 DELETE FROM AHL_NR_PROFILE_DETAILS
1648 WHERE NR_PROFILE_DET_ID = p_x_nr_prof_est_tbl(i).nr_profile_det_id;
1649 ELSE
1650 OPEN check_exclude_record(p_x_nr_prof_est_tbl(i).nr_profile_header_id,
1651 NVL(l_cmro_resource_id,-1), NVL(l_inventory_item_id,-1),
1652 NVL(l_stage_type_code,'X'), NVL(l_fleet_header_id,-1),
1653 NVL(l_operating_org_id,-1), NVL(l_operations_type_code,'X'),-2);
1654
1655 FETCH check_exclude_record INTO l_temp_nr_profile_det_id , l_required_qty, l_excluded_date;
1656
1657 IF(check_exclude_record%FOUND)
1658 THEN
1659 DELETE FROM AHL_NR_PROFILE_DETAILS
1660 WHERE nr_profile_det_id = p_x_nr_prof_est_tbl(i).nr_profile_det_id;
1661 ELSE
1662 UPDATE AHL_NR_PROFILE_DETAILS
1663 SET object_version_number = object_version_number +1,
1664 last_update_date = SYSDATE,
1665 last_updated_by = FND_GLOBAL.user_id,
1666 last_update_login = FND_GLOBAL.login_id,
1667 excluded_date = SYSDATE
1668 WHERE nr_profile_det_id = p_x_nr_prof_est_tbl(i).nr_profile_det_id ;
1669 END IF;
1670
1671 CLOSE check_exclude_record;
1672 END IF;
1673 END IF;
1674
1675 IF (SQL%NOTFOUND)
1676 THEN
1677 RAISE NO_DATA_FOUND;
1678 END IF;
1679
1680 CLOSE get_analysis;
1681
1682 END IF;
1683
1684 -- Update the Profile lastupdate Date if New Record added or updated/deleted existing Records
1685 IF(p_x_nr_prof_est_tbl(i).excluded_date IS NULL)
1686 THEN
1687 UPDATE ahl_nr_profile_headers
1688 SET last_update_date = SYSDATE,
1689 last_updated_by = FND_GLOBAL.user_id,
1690 last_update_login = FND_GLOBAL.login_id
1691 WHERE nr_profile_header_id = p_x_nr_prof_est_tbl(i).nr_profile_header_id;
1692
1693 IF (SQL%NOTFOUND)
1694 THEN
1695 RAISE NO_DATA_FOUND;
1696 END IF;
1697 END IF;
1698
1699 -- Get all the error messages if any and raise the appropriate Exception
1700 l_msg_count := FND_MSG_PUB.count_msg;
1701 IF l_msg_count > 0 THEN
1702 x_msg_count := l_msg_count;
1703 RAISE FND_API.G_EXC_ERROR;
1704 END IF;
1705
1706 END LOOP; -- Table Loop
1707
1708 END IF; --Table Count
1709
1710 IF G_DEBUG='Y' THEN
1711 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || ':End of Private api ');
1712 END IF;
1713
1714
1715 EXCEPTION
1716 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1717 ROLLBACK TO add_details_to_prof_sp;
1718 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1719 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1720 p_count => x_msg_count,
1721 p_data => x_msg_data);
1722
1723 WHEN FND_API.G_EXC_ERROR THEN
1724 ROLLBACK TO add_details_to_prof_sp;
1725 X_return_status := FND_API.G_RET_STS_ERROR;
1726 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1727 p_count => x_msg_count,
1728 p_data => X_msg_data);
1729 WHEN OTHERS THEN
1730 ROLLBACK TO add_details_to_prof_sp;
1731 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1732
1733 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1734 THEN
1735 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_RA_NR_PROFILE_PVT',
1736 p_procedure_name => 'ADD_DETAILS_TO_PROF',
1737 p_error_text => SUBSTR(SQLERRM,1,240));
1738 END IF;
1739 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1740 p_count => x_msg_count,
1741 p_data => X_msg_data);
1742
1743 END add_details_to_prof;
1744
1745 -- Prcoess the Nonroutine Profile
1746 -- Create/Update/Delete
1747
1748 PROCEDURE process_nr_profile
1749 (
1750 p_api_version IN NUMBER,
1751 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1752 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1753 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1754 x_return_status OUT NOCOPY VARCHAR2,
1755 x_msg_count OUT NOCOPY NUMBER,
1756 x_msg_data OUT NOCOPY VARCHAR2,
1757 p_x_nr_profile_header_rec IN OUT NOCOPY nr_prof_header_rec_type
1758 )
1759 AS
1760
1761 CURSOR get_mr_title(c_mr_title VARCHAR2)
1762 IS
1763 SELECT DISTINCT mh.title
1764 FROM ahl_mr_headers_v mh
1765 WHERE SYSDATE BETWEEN mh.effective_from AND NVL(mh.effective_to,SYSDATE+1)
1766 AND mh.title NOT IN (SELECT mr_title FROM ahl_nr_profile_headers)
1767 AND mh.title = c_mr_title;
1768
1769 CURSOR get_valid_mr_title(c_mr_title VARCHAR2)
1770 IS
1771 SELECT DISTINCT mh.title
1772 FROM ahl_mr_headers_v mh
1773 WHERE SYSDATE BETWEEN mh.effective_from AND NVL(mh.effective_to,SYSDATE+1)
1774 AND mh.title = c_mr_title;
1775
1776 CURSOR check_duplicate(c_mr_title VARCHAR2)
1777 IS
1778 SELECT mr_title
1779 FROM ahl_nr_profile_headers
1780 WHERE mr_title = c_mr_title;
1781
1782 CURSOR get_profile_status(c_nr_profile_header_id NUMBER)
1783 IS
1784 SELECT status_code
1785 FROM ahl_nr_profile_headers
1786 WHERE nr_profile_header_id = c_nr_profile_header_id;
1787
1788 CURSOR get_nr_profile_id(c_mr_title VARCHAR2)
1789 IS
1790 SELECT nr_profile_header_id
1791 FROM ahl_nr_profile_headers
1792 WHERE mr_title = c_mr_title
1793 AND status_code <> 'DELETED';
1794
1795 CURSOR get_nr_profile_title(c_nr_profile_header_id NUMBER)
1796 IS
1797 SELECT mr_title
1798 FROM ahl_nr_profile_headers
1799 WHERE nr_profile_header_id = c_nr_profile_header_id
1800 AND status_code <> 'DELETED';
1801
1802 CURSOR get_object_version_number(c_nr_profile_header_id NUMBER,c_object_version_number NUMBER)
1803 IS
1804 SELECT object_version_number
1805 FROM ahl_nr_profile_headers
1806 WHERE nr_profile_header_id = c_nr_profile_header_id
1807 AND object_version_number = c_object_version_number;
1808
1809 CURSOR get_delete_mr_title(c_mr_title VARCHAR2)
1810 IS
1811 SELECT max(mh.version_number)
1812 FROM ahl_mr_headers_v mh
1813 WHERE mh.title = c_mr_title;
1814
1815 l_api_name CONSTANT VARCHAR2(30) := 'process_nr_profile';
1816
1817 l_nr_profile_header_id NUMBER;
1818 l_mr_title VARCHAR2(80);
1819 l_prof_status_code VARCHAR2(30);
1820 l_object_version_number NUMBER;
1821 l_return_status VARCHAR2(240);
1822 l_msg_count NUMBER;
1823 l_msg_data VARCHAR2(240);
1824 l_api_version NUMBER := 1.0;
1825 l_mr_version_number NUMBER;
1826
1827 BEGIN
1828 -- Initialize API return status to success
1829 x_return_status := FND_API.G_RET_STS_SUCCESS;
1830
1831 -- Standard Start of API savepoint
1832 SAVEPOINT process_nr_profile_sp;
1833
1834 -- Standard call to check for call compatibility.
1835 IF NOT FND_API.compatible_api_call
1836 (
1837 l_api_version,
1838 p_api_version,
1839 l_api_name,
1840 G_PKG_NAME
1841 )
1842 THEN
1843 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1844 END IF;
1845
1846 -- Initialize message list if p_init_msg_list is set to TRUE.
1847 IF FND_API.to_boolean( p_init_msg_list ) THEN
1848 FND_MSG_PUB.initialize;
1849 END IF;
1850
1851 IF G_DEBUG = 'Y' THEN
1852 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' ::Begin API' );
1853 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' :: NR_PROFILE_HEADER_ID::'||
1854 p_x_nr_profile_header_rec.nr_profile_header_id || ' : OBJECT_VERSION_NUMBER::'||
1855 p_x_nr_profile_header_rec.object_version_number || ' : MR_TITLE::'||
1856 p_x_nr_profile_header_rec.mr_title || ' : STATUS_CODE::'||
1857 p_x_nr_profile_header_rec.status_code || ' :DML_OPERATION::'||
1858 p_x_nr_profile_header_rec.dml_operation );
1859 END IF;
1860
1861 -- Profile Header ID Mandatory
1862 IF( p_x_nr_profile_header_rec.DML_OPERATION = 'U' AND p_x_nr_profile_header_rec.nr_profile_header_id IS NULL )
1863 THEN
1864 FND_MESSAGE.SET_NAME('AHL', 'AHL_RA_NP_HDR_ID_MAND');
1865 FND_MSG_PUB.ADD;
1866 END IF;
1867
1868 --Object version Number Validation
1869 IF( p_x_nr_profile_header_rec.DML_OPERATION = 'C' )
1870 THEN
1871 p_x_nr_profile_header_rec.object_version_number := 1;
1872 ELSIF(p_x_nr_profile_header_rec.DML_OPERATION = 'U' OR p_x_nr_profile_header_rec.DML_OPERATION = 'D')
1873 THEN
1874 OPEN get_object_version_number(p_x_nr_profile_header_rec.nr_profile_header_id, p_x_nr_profile_header_rec.object_version_number);
1875 FETCH get_object_version_number into l_object_version_number;
1876 IF(get_object_version_number%NOTFOUND)
1877 THEN
1878 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_RECORD_CHANGED' );
1879 FND_MSG_PUB.add;
1880 END IF;
1881 CLOSE get_object_version_number;
1882 END IF;
1883
1884 --Validation for identificatioin sequence
1885 IF (p_x_nr_profile_header_rec.dml_operation = 'C' OR p_x_nr_profile_header_rec.dml_operation = 'U' )
1886 THEN
1887 IF (p_x_nr_profile_header_rec.ident_seq_third IS NULL AND p_x_nr_profile_header_rec.ident_seq_fourth IS NOT NULL )
1888 THEN
1889 FND_MESSAGE.SET_NAME('AHL', 'AHL_RA_NP_IDENT_SEQ_THR_REQ');
1890 FND_MSG_PUB.ADD;
1891 END IF;
1892
1893 IF (p_x_nr_profile_header_rec.ident_seq_third IS NOT NULL AND p_x_nr_profile_header_rec.ident_seq_fourth IS NOT NULL AND p_x_nr_profile_header_rec.ident_seq_third = p_x_nr_profile_header_rec.ident_seq_fourth)
1894 THEN
1895 FND_MESSAGE.SET_NAME('AHL', 'AHL_RA_NP_IDENT_SEQ_SAME');
1896 FND_MSG_PUB.ADD;
1897 END IF;
1898
1899 --Status Code Validation
1900 IF(p_x_nr_profile_header_rec.status_code IS NULL)
1901 THEN
1902 FND_MESSAGE.SET_NAME('AHL', 'AHL_RA_NP_PROF_STAT_MAN');
1903 FND_MSG_PUB.ADD;
1904 END IF;
1905
1906
1907 END IF;
1908
1909 -- Validation for Wether MR is active or not
1910 IF (p_x_nr_profile_header_rec.dml_operation = 'C')
1911 THEN
1912 IF (p_x_nr_profile_header_rec.mr_title IS NOT NULL)
1913 THEN
1914 OPEN get_mr_title(p_x_nr_profile_header_rec.mr_title);
1915 FETCH get_mr_title INTO l_mr_title;
1916
1917 IF(get_mr_title%NOTFOUND)
1918 THEN
1919 FND_MESSAGE.SET_NAME('AHL', 'AHL_FMP_INVALID_MR');
1920 FND_MSG_PUB.ADD;
1921 END IF;
1922
1923 CLOSE get_mr_title;
1924
1925 OPEN check_duplicate(p_x_nr_profile_header_rec.mr_title);
1926 FETCH check_duplicate INTO l_mr_title;
1927
1928 IF(check_duplicate%FOUND)
1929 THEN
1930 FND_MESSAGE.SET_NAME('AHL', 'AHL_RA_NP_DUP_NR_PROFILE');
1931 FND_MSG_PUB.ADD;
1932 END IF;
1933
1934 CLOSE check_duplicate;
1935
1936 ELSE
1937 FND_MESSAGE.SET_NAME('AHL', 'AHL_RA_NP_MR_TIT_MIS');
1938 FND_MSG_PUB.ADD;
1939 END IF;
1940
1941 END IF;
1942
1943 -- Validation for Wether MR is active or not
1944 IF (p_x_nr_profile_header_rec.dml_operation = 'U')
1945 THEN
1946 OPEN get_profile_status(p_x_nr_profile_header_rec.nr_profile_header_id);
1947 FETCH get_profile_status INTO l_prof_status_code;
1948
1949 IF(get_profile_status%FOUND)
1950 THEN
1951 IF(l_prof_status_code = 'DELETED' AND (p_x_nr_profile_header_rec.status_code = 'DRAFT' OR p_x_nr_profile_header_rec.status_code = 'COMPLETE'))
1952 THEN
1953 OPEN get_valid_mr_title(p_x_nr_profile_header_rec.mr_title);
1954 FETCH get_valid_mr_title INTO l_mr_title;
1955
1956 IF(get_valid_mr_title%NOTFOUND)
1957 THEN
1958 FND_MESSAGE.SET_NAME('AHL', 'AHL_RA_NP_NO_ACTIVE_MR');
1959 FND_MESSAGE.set_token( 'FIELD2', p_x_nr_profile_header_rec.status_code );
1960 FND_MESSAGE.set_token( 'FIELD1', l_prof_status_code );
1961 FND_MSG_PUB.ADD;
1962 END IF;
1963 CLOSE get_valid_mr_title;
1964 END IF;
1965 END IF;
1966
1967 CLOSE get_profile_status;
1968 END IF;
1969
1970
1971 -- Get all the error messages if any and raise the appropriate Exception
1972 l_msg_count := FND_MSG_PUB.count_msg;
1973 IF l_msg_count > 0 THEN
1974 x_msg_count := l_msg_count;
1975 RAISE FND_API.G_EXC_ERROR;
1976 END IF;
1977
1978
1979 IF (p_x_nr_profile_header_rec.dml_operation = 'C')
1980 THEN
1981
1982 IF( p_x_nr_profile_header_rec.nr_profile_header_id IS NULL)
1983 THEN
1984 p_x_nr_profile_header_rec.nr_profile_header_id := AHL_NR_PROFILE_HEADERS_S.NEXTVAL;
1985 END IF;
1986
1987 IF(p_x_nr_profile_header_rec.object_version_number IS NULL)
1988 THEN
1989 p_x_nr_profile_header_rec.object_version_number := 1;
1990 END IF;
1991
1992 INSERT INTO AHL_NR_PROFILE_HEADERS
1993 (
1994 NR_PROFILE_HEADER_ID,
1995 OBJECT_VERSION_NUMBER,
1996 LAST_UPDATE_DATE,
1997 LAST_UPDATED_BY,
1998 CREATION_DATE,
1999 CREATED_BY,
2000 LAST_UPDATE_LOGIN,
2001 MR_TITLE,
2002 STATUS_CODE,
2003 IDENT_SEQ_THIRD ,
2004 IDENT_SEQ_FOURTH ,
2005 SECURITY_GROUP_ID,
2006 ATTRIBUTE_CATEGORY,
2007 ATTRIBUTE1,
2008 ATTRIBUTE2,
2009 ATTRIBUTE3,
2010 ATTRIBUTE4,
2011 ATTRIBUTE5,
2012 ATTRIBUTE6,
2013 ATTRIBUTE7,
2014 ATTRIBUTE8,
2015 ATTRIBUTE9,
2016 ATTRIBUTE10,
2017 ATTRIBUTE11,
2018 ATTRIBUTE12,
2019 ATTRIBUTE13,
2020 ATTRIBUTE14,
2021 ATTRIBUTE15
2022 )
2023 VALUES
2024 (
2025 p_x_nr_profile_header_rec.nr_profile_header_id,
2026 p_x_nr_profile_header_rec.object_version_number,
2027 SYSDATE,
2028 FND_GLOBAL.user_id,
2029 SYSDATE,
2030 FND_GLOBAL.user_id,
2031 FND_GLOBAL.login_id,
2032 p_x_nr_profile_header_rec.MR_TITLE,
2033 p_x_nr_profile_header_rec.STATUS_CODE,
2034 p_x_nr_profile_header_rec.IDENT_SEQ_THIRD,
2035 p_x_nr_profile_header_rec.IDENT_SEQ_FOURTH,
2036 p_x_nr_profile_header_rec.SECURITY_GROUP_ID,
2037 p_x_nr_profile_header_rec.ATTRIBUTE_CATEGORY,
2038 p_x_nr_profile_header_rec.ATTRIBUTE1,
2039 p_x_nr_profile_header_rec.ATTRIBUTE2,
2040 p_x_nr_profile_header_rec.ATTRIBUTE3,
2041 p_x_nr_profile_header_rec.ATTRIBUTE4,
2042 p_x_nr_profile_header_rec.ATTRIBUTE5,
2043 p_x_nr_profile_header_rec.ATTRIBUTE6,
2044 p_x_nr_profile_header_rec.ATTRIBUTE7,
2045 p_x_nr_profile_header_rec.ATTRIBUTE8,
2046 p_x_nr_profile_header_rec.ATTRIBUTE9,
2047 p_x_nr_profile_header_rec.ATTRIBUTE10,
2048 p_x_nr_profile_header_rec.ATTRIBUTE11,
2049 p_x_nr_profile_header_rec.ATTRIBUTE12,
2050 p_x_nr_profile_header_rec.ATTRIBUTE13,
2051 p_x_nr_profile_header_rec.ATTRIBUTE14,
2052 p_x_nr_profile_header_rec.ATTRIBUTE15
2053 ) RETURNING nr_profile_header_id INTO p_x_nr_profile_header_rec.nr_profile_header_id;
2054
2055 ELSIF (p_x_nr_profile_header_rec.dml_operation = 'U')
2056 THEN
2057 UPDATE ahl_nr_profile_headers
2058 SET object_version_number = object_version_number +1,
2059 last_update_date = SYSDATE,
2060 last_updated_by = FND_GLOBAL.user_id,
2061 last_update_login = FND_GLOBAL.login_id,
2062 status_code = p_x_nr_profile_header_rec.status_code,
2063 ident_seq_third = p_x_nr_profile_header_rec.ident_seq_third,
2064 ident_seq_fourth = p_x_nr_profile_header_rec.ident_seq_fourth,
2065 security_group_id = p_x_nr_profile_header_rec.security_group_id,
2066 attribute_category = p_x_nr_profile_header_rec.attribute_category,
2067 attribute1 = p_x_nr_profile_header_rec.attribute1,
2068 attribute2 = p_x_nr_profile_header_rec.attribute2,
2069 attribute3 = p_x_nr_profile_header_rec.attribute3,
2070 attribute4 = p_x_nr_profile_header_rec.attribute4,
2071 attribute5 = p_x_nr_profile_header_rec.attribute5,
2072 attribute6 = p_x_nr_profile_header_rec.attribute6,
2073 attribute7 = p_x_nr_profile_header_rec.attribute7,
2074 attribute8 = p_x_nr_profile_header_rec.attribute8,
2075 attribute9 = p_x_nr_profile_header_rec.attribute9,
2076 attribute10 = p_x_nr_profile_header_rec.attribute10,
2077 attribute11 = p_x_nr_profile_header_rec.attribute11,
2078 attribute12 = p_x_nr_profile_header_rec.attribute12,
2079 attribute13 = p_x_nr_profile_header_rec.attribute13,
2080 attribute14 = p_x_nr_profile_header_rec.attribute14,
2081 attribute15 = p_x_nr_profile_header_rec.attribute15
2082 WHERE nr_profile_header_id = p_x_nr_profile_header_rec.nr_profile_header_id;
2083
2084 IF (SQL%NOTFOUND)
2085 THEN
2086 RAISE NO_DATA_FOUND;
2087 END IF;
2088 ELSIF (p_x_nr_profile_header_rec.dml_operation = 'D')
2089 THEN
2090 IF(p_x_nr_profile_header_rec.nr_profile_header_id IS NOT NULL)
2091 THEN
2092 l_nr_profile_header_id := p_x_nr_profile_header_rec.nr_profile_header_id;
2093 -- Get the MR TITLE for Given Profile header ID
2094 OPEN get_nr_profile_title(l_nr_profile_header_id);
2095 FETCH get_nr_profile_title INTO l_mr_title;
2096 IF(get_nr_profile_title%NOTFOUND)
2097 THEN
2098 FND_MESSAGE.SET_NAME('AHL', 'AHL_RA_NP_HDR_IS_INVLD');
2099 FND_MSG_PUB.ADD;
2100 RAISE FND_API.G_EXC_ERROR;
2101 END IF;
2102 CLOSE get_nr_profile_title;
2103 ELSIF(p_x_nr_profile_header_rec.MR_TITLE IS NULL )
2104 THEN
2105 FND_MESSAGE.SET_NAME('AHL', 'AHL_RA_NP_MR_TITL_MAND');
2106 FND_MSG_PUB.ADD;
2107 RAISE FND_API.G_EXC_ERROR;
2108 ELSE
2109 l_mr_title := p_x_nr_profile_header_rec.MR_TITLE;
2110 -- Get the Profile header ID for Given MR TITLE
2111 OPEN get_nr_profile_id(l_mr_title);
2112 FETCH get_nr_profile_id INTO l_nr_profile_header_id;
2113 IF(get_nr_profile_id%NOTFOUND)
2114 THEN
2115 FND_MESSAGE.SET_NAME('AHL', 'AHL_RA_NP_MR_TITL_INVLD');
2116 FND_MSG_PUB.ADD;
2117 RAISE FND_API.G_EXC_ERROR;
2118 END IF;
2119 CLOSE get_nr_profile_id;
2120 END IF;
2121
2122 -- If Profile found for a given mr_title delete all the profile details
2123 OPEN get_delete_mr_title(l_mr_title);
2124 FETCH get_delete_mr_title INTO l_mr_version_number;
2125 -- If MR has version number 1 , do Hard Delete
2126 -- else change status to DELETED
2127 IF(get_delete_mr_title%FOUND)
2128 THEN
2129 IF(l_mr_version_number = 1)
2130 THEN
2131 -- Delete Profile details(Resources, Materials)
2132 DELETE FROM AHL_NR_PROFILE_DETAILS
2133 WHERE NR_PROFILE_HEADER_ID = l_nr_profile_header_id;
2134
2135 -- Delete Profile Header
2136 DELETE FROM AHL_NR_PROFILE_HEADERS
2137 WHERE NR_PROFILE_HEADER_ID = l_nr_profile_header_id;
2138
2139 ELSE
2140
2141 UPDATE AHL_NR_PROFILE_HEADERS
2142 SET object_version_number = object_version_number +1,
2143 last_update_date = SYSDATE,
2144 last_updated_by = FND_GLOBAL.user_id,
2145 last_update_login = FND_GLOBAL.login_id,
2146 status_code = 'DELETED'
2147 WHERE nr_profile_header_id = l_nr_profile_header_id;
2148
2149 END IF;
2150 END IF;
2151 CLOSE get_delete_mr_title;
2152 END IF; -- End DML Operation
2153
2154 IF FND_API.TO_BOOLEAN(p_commit) THEN
2155 COMMIT;
2156 END IF;
2157
2158 IF G_DEBUG='Y' THEN
2159 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : End API' );
2160 END IF;
2161
2162 EXCEPTION
2163 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2164 ROLLBACK TO process_nr_profile_sp;
2165 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2166 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2167 p_count => x_msg_count,
2168 p_data => x_msg_data);
2169
2170 WHEN FND_API.G_EXC_ERROR THEN
2171 ROLLBACK TO process_nr_profile_sp;
2172 X_return_status := FND_API.G_RET_STS_ERROR;
2173 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2174 p_count => x_msg_count,
2175 p_data => X_msg_data);
2176 WHEN OTHERS THEN
2177 ROLLBACK TO process_nr_profile_sp;
2178 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2179
2180 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2181 THEN
2182 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_RA_NR_PROFILE_PVT',
2183 p_procedure_name => 'PROCESS_NR_PROFILE',
2184 p_error_text => SUBSTR(SQLERRM,1,240));
2185 END IF;
2186 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
2187 p_count => x_msg_count,
2188 p_data => X_msg_data);
2189
2190
2191 END process_nr_profile;
2192
2193
2194 -- Returns Resource Type
2195 FUNCTION get_resource_type
2196 RETURN VARCHAR2
2197 IS
2198 BEGIN
2199 RETURN G_RESOURCE;
2200 END;
2201
2202 -- Returns Item Type
2203 FUNCTION get_item_type
2204 RETURN VARCHAR2
2205 IS
2206 BEGIN
2207 RETURN G_ITEM;
2208 END;
2209
2210 END AHL_RA_NR_PROFILE_PVT;