[Home] [Help]
PACKAGE BODY: APPS.AHL_LTP_RESRC_LEVL_PUB
Source
1 PACKAGE BODY AHL_LTP_RESRC_LEVL_PUB AS
2 /* $Header: AHLPRLGB.pls 115.14 2003/09/09 06:05:19 rroy noship $ */
3 G_PKG_NAME VARCHAR2(30) := 'AHL_LTP_RESRC_LEVL_PUB';
4 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
5 --
6 -----------------------------------------------------------
7 -- PACKAGE
8 -- AHL_LTP_RESRC_LEVL_PUB
9 --
10 -- PURPOSE
11 --
12 -- NOTES
13 --
14 --
15 -- HISTORY
16 -- 23-May-2002 ssurapan Created.
17
18 --------------------------------------------------------------------
19 -- PROCEDURE
20 -- Derive_Resource_Capacity
21 --
22 -- PURPOSE
23 -- Derive Required Resources Capacity
24 --
25 -- PARAMETERS
26 -- p_req_resources_rec : Record Representing Required Resources
27 -- x_aval_resources_tbl : Table Representing Available Resources Table
28 --
29 -- NOTES
30 --------------------------------------------------------------------
31 PROCEDURE Derive_Resource_Capacity (
32 p_api_version IN NUMBER,
33 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
34 p_commit IN VARCHAR2 := FND_API.g_false,
35 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
36 p_module_type IN VARCHAR2 := 'JSP',
37 p_req_resources IN Req_Resources_Rec,
38 x_aval_resources_tbl OUT NOCOPY Aval_Resources_Tbl,
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_count OUT NOCOPY NUMBER,
41 x_msg_data OUT NOCOPY VARCHAR2
42 )
43 IS
44 --
45 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_RESOURCE_CAPACITY';
46 l_api_version CONSTANT NUMBER := 1.0;
47 l_msg_count NUMBER;
48 l_return_status VARCHAR2(1);
49 l_msg_data VARCHAR2(2000);
50 l_aval_resources_tbl AHL_LTP_RESRC_LEVL_PUB.Aval_Resources_Tbl;
51 l_period_Rsrc_Req_Tbl AHL_LTP_RESRC_LEVL_PVT.Period_Rsrc_Req_Tbl_Type;
52 l_department_id NUMBER := null;
53 BEGIN
54
55 --------------------Initialize ----------------------------------
56 -- Standard Start of API savepoint
57 SAVEPOINT derive_resource_capacity;
58 -- Check if API is called in debug mode. If yes, enable debug.
59 IF G_DEBUG='Y' THEN
60 AHL_DEBUG_PUB.enable_debug;
61 END IF;
62 -- Debug info.
63 IF G_DEBUG='Y' THEN
64 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_resrc_levl_pub. derive resource capacity','+RESLG+');
65 END IF;
66 -- Standard call to check for call compatibility.
67 IF FND_API.to_boolean(p_init_msg_list)
68 THEN
69 FND_MSG_PUB.initialize;
70 END IF;
71 -- Initialize API return status to success
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73 -- Initialize message list if p_init_msg_list is set to TRUE.
74 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
75 p_api_version,
76 l_api_name,G_PKG_NAME)
77 THEN
78 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
79 END IF;
80 --------------------Start of API Body-----------------------------------
81 /*
82 IF (p_x_req_resources.org_name IS NOT NULL AND
83 p_x_req_resources.org_name <> FND_API.G_MISS_CHAR) THEN
84 --
85 IF (p_x_req_resources.dept_name IS NOT NULL AND
86 p_x_req_resources.dept_name <> FND_API.G_MISS_CHAR) THEN
87 --
88 SELECT department_id INTO l_department_id
89 FROM BOM_DEPARTMENTS A, HR_ALL_ORGANIZATION_UNITS B
90 WHERE A.ORGANIZATION_ID = B.ORGANIZATION_ID
91 AND B.NAME = p_x_req_resources.org_name
92 AND A.description = p_x_req_resources.dept_name;
93 --
94 IF l_department_id IS NULL THEN
95 Fnd_Message.Set_Name('AHL','AHL_LTP_DEPT_INVALID');
96 Fnd_Msg_Pub.ADD;
97 RAISE Fnd_Api.G_EXC_ERROR;
98 END IF;
99 ELSE
100 Fnd_Message.Set_Name('AHL','AHL_LTP_DEPT_NULL');
101 Fnd_Msg_Pub.ADD;
102 RAISE Fnd_Api.G_EXC_ERROR;
103 END IF;
104 --
105 */
106 AHL_LTP_RESRC_LEVL_PVT.Get_Rsrc_Req_By_Period
107 (
108 p_api_version => p_api_version,
109 p_init_msg_list => p_init_msg_list,
110 p_commit => p_commit,
111 p_validation_level => p_validation_level,
112 p_default => null,
113 p_module_type => p_module_type,
114 p_dept_id => p_req_resources.dept_id,
115 p_dept_name => p_req_resources.dept_name,
116 p_org_name => p_req_resources.org_name,
117 p_plan_id => p_req_resources.plan_id,
118 p_start_time => p_req_resources.start_date,
119 p_end_time => p_req_resources.end_date,
120 p_uom_code => p_req_resources.uom_code,
121 p_required_capacity => p_req_resources.required_capacity,
122 x_per_rsrc_tbl => l_period_Rsrc_Req_Tbl,
123 x_return_status => l_return_status,
124 x_msg_count => l_msg_count,
125 x_msg_data => l_msg_data);
126
127 -- END IF;
128 --
129 IF l_return_status = 'S' THEN
130 IF l_period_Rsrc_Req_Tbl.COUNT > 0 THEN
131 FOR i IN l_period_Rsrc_Req_Tbl.FIRST..l_period_Rsrc_Req_Tbl.LAST
132 LOOP
133 x_aval_resources_tbl(i).period_string := l_period_Rsrc_Req_Tbl(i).period_string;
134 x_aval_resources_tbl(i).required_capacity := l_period_Rsrc_Req_Tbl(i).capacity_units;
135 x_aval_resources_tbl(i).dept_name := l_period_Rsrc_Req_Tbl(i).dept_description;
136 x_aval_resources_tbl(i).period_start := l_period_Rsrc_Req_Tbl(i).period_start;
137 x_aval_resources_tbl(i).period_end := l_period_Rsrc_Req_Tbl(i).period_end;
138 x_aval_resources_tbl(i).resource_type_meaning := l_period_Rsrc_Req_Tbl(i).resource_type_meaning;
139 x_aval_resources_tbl(i).resource_name := l_period_Rsrc_Req_Tbl(i).resource_name;
140 x_aval_resources_tbl(i).resource_id := l_period_Rsrc_Req_Tbl(i).resource_id;
141 IF G_DEBUG='Y' THEN
142 --
143 AHL_DEBUG_PUB.debug( 'END PUB SDATE:'||x_aval_resources_tbl(i).period_start);
144 AHL_DEBUG_PUB.debug( 'END PUB EDATE:'||x_aval_resources_tbl(i).period_end);
145 AHL_DEBUG_PUB.debug( 'END PUB period string:'||x_aval_resources_tbl(i).period_string);
146 AHL_DEBUG_PUB.debug( 'END PUB RID:'||x_aval_resources_tbl(i).resource_id);
147 --
148 END IF;
149 END LOOP;
150 END IF;
151 END IF;
152
153 ------------------------End of Body---------------------------------------
154 --Standard check to count messages
155 l_msg_count := Fnd_Msg_Pub.count_msg;
156
157 IF l_msg_count > 0 THEN
158 X_msg_count := l_msg_count;
159 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
160 RAISE Fnd_Api.G_EXC_ERROR;
161 END IF;
162
163 --Standard check for commit
164 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
165 COMMIT;
166 END IF;
167 -- Debug info
168 IF G_DEBUG='Y' THEN
169 Ahl_Debug_Pub.debug( 'End of public api Derive Resource Capacity','+RSRLG+');
170 -- Check if API is called in debug mode. If yes, disable debug.
171 Ahl_Debug_Pub.disable_debug;
172 --
173 END IF;
174 EXCEPTION
175 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
176 ROLLBACK TO derive_resource_capacity;
177 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
179 p_count => x_msg_count,
180 p_data => x_msg_data);
181 IF G_DEBUG='Y' THEN
182
183 AHL_DEBUG_PUB.log_app_messages (
184 x_msg_count, x_msg_data, 'ERROR' );
185 AHL_DEBUG_PUB.debug( 'ahl_ltp_resrc_levl_pub. Derive Resource Capacity','+RSRLG+');
186 -- Check if API is called in debug mode. If yes, disable debug.
187 AHL_DEBUG_PUB.disable_debug;
188 END IF;
189 WHEN FND_API.G_EXC_ERROR THEN
190 ROLLBACK TO derive_resource_capacity;
191 X_return_status := FND_API.G_RET_STS_ERROR;
192 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
193 p_count => x_msg_count,
194 p_data => X_msg_data);
195 IF G_DEBUG='Y' THEN
196
197 -- Debug info.
198 AHL_DEBUG_PUB.log_app_messages (
199 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
200 AHL_DEBUG_PUB.debug( 'ahl_ltp_resrc_levl_pub. Derive Resource Capacity','+RSRLG+');
201 -- Check if API is called in debug mode. If yes, disable debug.
202 AHL_DEBUG_PUB.disable_debug;
203 END IF;
204 WHEN OTHERS THEN
205 ROLLBACK TO derive_resource_capacity;
206 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
207 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
208 THEN
209 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_RESRC_LEVL_PUB',
210 p_procedure_name => 'DERIVE_RESOURCE_CAPACITY',
211 p_error_text => SUBSTR(SQLERRM,1,240));
212 END IF;
213 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
214 p_count => x_msg_count,
215 p_data => X_msg_data);
216 IF G_DEBUG='Y' THEN
217
218 -- Debug info.
219 AHL_DEBUG_PUB.log_app_messages (
220 x_msg_count, x_msg_data, 'SQL ERROR' );
221 AHL_DEBUG_PUB.debug( 'ahl_ltp_resrc_levl_pub. Derive Resource Capacity','+RSRLG+');
222 -- Check if API is called in debug mode. If yes, disable debug.
223 AHL_DEBUG_PUB.disable_debug;
224 END IF;
225
226 END Derive_Resource_Capacity;
227
228 --------------------------------------------------------------------
229 -- PROCEDURE
230 -- Derive_Resource_Consum
231 --
232 -- PURPOSE
233 -- Derive Resource Consum
234 --
235 -- PARAMETERS
236 -- p_req_resources : Record Representing Resource Consumption For
237 -- x_resource_con_tbl : Table Representing Resource Consumption Table
238 --
239 -- NOTES
240 --------------------------------------------------------------------
241 PROCEDURE Derive_Resource_Consum (
242 p_api_version IN NUMBER,
243 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
244 p_commit IN VARCHAR2 := FND_API.g_false,
245 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
246 p_module_type IN VARCHAR2 := 'JSP',
247 p_req_resources IN Req_Resources_Rec,
248 x_resource_con_tbl OUT NOCOPY Resource_Con_Tbl,
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2
252 )
253 IS
254 l_api_name CONSTANT VARCHAR2(30) := 'DERIVE_RESOURCE_CONSUM';
255 l_api_version CONSTANT NUMBER := 1.0;
256 l_msg_count NUMBER;
257 l_return_status VARCHAR2(1);
258 l_msg_data VARCHAR2(2000);
259 l_task_req_tbl AHL_LTP_RESRC_LEVL_PVT.Task_Requirement_Tbl_Type;
260 l_resource_con_tbl AHL_LTP_RESRC_LEVL_PUB.Resource_Con_Tbl;
261 l_department_id NUMBER;
262 BEGIN
263
264 --------------------Initialize ----------------------------------
265 -- Standard Start of API savepoint
266 SAVEPOINT derive_resource_consum;
267 -- Check if API is called in debug mode. If yes, enable debug.
268 IF G_DEBUG='Y' THEN
269 AHL_DEBUG_PUB.enable_debug;
270 END IF;
271 -- Debug info.
272 IF G_DEBUG='Y' THEN
273 AHL_DEBUG_PUB.debug( 'enter ahl_ltp_resrc_levl_pub. derive resource consum','+RESLG+');
274 END IF;
275 -- Standard call to check for call compatibility.
276 IF FND_API.to_boolean(p_init_msg_list)
277 THEN
278 FND_MSG_PUB.initialize;
279 END IF;
280 -- Initialize API return status to success
281 x_return_status := FND_API.G_RET_STS_SUCCESS;
282 -- Initialize message list if p_init_msg_list is set to TRUE.
283 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
284 p_api_version,
285 l_api_name,G_PKG_NAME)
286 THEN
287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
288 END IF;
289 --------------------Start of API Body-----------------------------------
290
291 IF (p_req_resources.org_name IS NOT NULL AND
292 p_req_resources.org_name <> FND_API.G_MISS_CHAR) THEN
293 --
294 IF (p_req_resources.dept_name IS NOT NULL AND
295 p_req_resources.dept_name <> FND_API.G_MISS_CHAR) THEN
296 --
297 SELECT department_id INTO l_department_id
298 FROM BOM_DEPARTMENTS A, HR_ALL_ORGANIZATION_UNITS B
299 WHERE A.ORGANIZATION_ID = B.ORGANIZATION_ID
300 AND B.NAME = p_req_resources.org_name
301 AND A.description = p_req_resources.dept_name;
302 --
303 IF l_department_id IS NULL THEN
304 Fnd_Message.Set_Name('AHL','AHL_LTP_DEPT_INVALID');
305 Fnd_Msg_Pub.ADD;
306 RAISE Fnd_Api.G_EXC_ERROR;
307 END IF;
308 ELSE
309 Fnd_Message.Set_Name('AHL','AHL_LTP_DEPT_ID_NOT_EXIST');
310 Fnd_Msg_Pub.ADD;
311 RAISE Fnd_Api.G_EXC_ERROR;
312 END IF;
313 IF G_DEBUG='Y' THEN
314 AHL_DEBUG_PUB.debug( 'before public CallRID:'||p_req_resources.resource_id);
315 AHL_DEBUG_PUB.debug( 'before public CallRTY:'||p_req_resources.resource_type_meaning);
316 AHL_DEBUG_PUB.debug( 'before public CallDID:'||p_req_resources.dept_id);
317 AHL_DEBUG_PUB.debug( 'before public CallDNAME:'||p_req_resources.dept_name);
318 AHL_DEBUG_PUB.debug( 'before public CallDSTART:'||p_req_resources.display_start_date);
319 AHL_DEBUG_PUB.debug( 'before public CallDENDD:'||p_req_resources.display_end_date);
320 AHL_DEBUG_PUB.debug( 'before public CallSTART:'||p_req_resources.start_date);
321 AHL_DEBUG_PUB.debug( 'before public CallENDD:'||p_req_resources.end_date);
322 END IF;
323 AHL_LTP_RESRC_LEVL_PVT.Get_Task_Requirements
324 (
325 p_api_version => p_api_version,
326 p_init_msg_list => p_init_msg_list,
327 p_commit => p_commit,
328 p_validation_level => p_validation_level,
329 p_default => null,
330 p_module_type => p_module_type,
331 p_dept_id => l_department_id, --p_req_resources.dept_id,
332 p_dept_name => p_req_resources.dept_name,
333 p_org_name => p_req_resources.org_name,
334 p_plan_id => p_req_resources.plan_id,
335 p_start_time => trunc(p_req_resources.start_date),
336 p_end_time => trunc(p_req_resources.end_date),
337 p_dstart_time => trunc(p_req_resources.display_start_date),
338 p_dend_time => trunc(p_req_resources.display_end_date),
339 p_resource_id => p_req_resources.resource_id,
340 p_aso_bom_rsrc_type => p_req_resources.resource_type_meaning,
341 x_task_req_tbl => l_task_req_tbl,
342 x_return_status => l_return_status,
343 x_msg_count => l_msg_count,
344 x_msg_data => l_msg_data);
345 END IF;
346
347 IF l_return_status = 'S' THEN
348 IF l_task_req_tbl.COUNT > 0 THEN
349 FOR i IN l_task_req_tbl.FIRST..l_task_req_tbl.LAST
350 LOOP
351 x_resource_con_tbl(i).visit_id := l_task_req_tbl(i).visit_id;
352 x_resource_con_tbl(i).task_id := l_task_req_tbl(i).task_id;
353 x_resource_con_tbl(i).visit_name := l_task_req_tbl(i).visit_name;
354 x_resource_con_tbl(i).visit_task_name := l_task_req_tbl(i).visit_task_name;
355 x_resource_con_tbl(i).task_type_code := l_task_req_tbl(i).task_type_code;
356 x_resource_con_tbl(i).dept_name := l_task_req_tbl(i).dept_name;
357 x_resource_con_tbl(i).quantity := l_task_req_tbl(i).required_units;
358 x_resource_con_tbl(i).available_units := l_task_req_tbl(i).available_units;
359 IF G_DEBUG='Y' THEN
360 AHL_DEBUG_PUB.debug( 'End of public VID:'||x_resource_con_tbl(i).visit_id);
361 AHL_DEBUG_PUB.debug( 'End of public TID:'||x_resource_con_tbl(i).task_id);
362 AHL_DEBUG_PUB.debug( 'End of public VTNA:'||x_resource_con_tbl(i).visit_task_name);
363 AHL_DEBUG_PUB.debug( 'End of public VNAM:'||x_resource_con_tbl(i).visit_name);
364 AHL_DEBUG_PUB.debug( 'End of public RQTY:'||x_resource_con_tbl(i).required_units);
365 AHL_DEBUG_PUB.debug( 'End of public AQTY:'||x_resource_con_tbl(i).available_units);
366 END IF;
367
368 END LOOP;
369 END IF;
370 END IF;
371
372
373 ------------------------End of Body---------------------------------------
374 --Standard check to count messages
375 l_msg_count := Fnd_Msg_Pub.count_msg;
376
377 IF l_msg_count > 0 THEN
378 X_msg_count := l_msg_count;
379 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
380 RAISE Fnd_Api.G_EXC_ERROR;
381 END IF;
382
383 --Standard check for commit
384 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
385 COMMIT;
386 END IF;
387 -- Debug info
388 IF G_DEBUG='Y' THEN
389 Ahl_Debug_Pub.debug( 'End of public api Derive Resource Consum','+RSRLG+');
390 -- Check if API is called in debug mode. If yes, disable debug.
391 Ahl_Debug_Pub.disable_debug;
392 END IF;
393
394 EXCEPTION
395 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
396 ROLLBACK TO derive_resource_consum;
397 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
398 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
399 p_count => x_msg_count,
400 p_data => x_msg_data);
401 IF G_DEBUG='Y' THEN
402
403 AHL_DEBUG_PUB.log_app_messages (
404 x_msg_count, x_msg_data, 'ERROR' );
405 AHL_DEBUG_PUB.debug( 'ahl_ltp_resrc_levl_pub. Derive Resource Consum','+RSRLG+');
406 -- Check if API is called in debug mode. If yes, disable debug.
407 AHL_DEBUG_PUB.disable_debug;
408 END IF;
409
410 WHEN FND_API.G_EXC_ERROR THEN
411 ROLLBACK TO derive_resource_consum;
412 X_return_status := FND_API.G_RET_STS_ERROR;
413 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
414 p_count => x_msg_count,
415 p_data => X_msg_data);
416 IF G_DEBUG='Y' THEN
417
418 -- Debug info.
419 AHL_DEBUG_PUB.log_app_messages (
420 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
421 AHL_DEBUG_PUB.debug( 'ahl_ltp_resrc_levl_pub. Derive Resource Consum','+RSRLG+');
422 -- Check if API is called in debug mode. If yes, disable debug.
423 AHL_DEBUG_PUB.disable_debug;
424 END IF;
425
426 WHEN OTHERS THEN
427 ROLLBACK TO derive_resource_consum;
428 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
429 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
430 THEN
431 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_RESRC_LEVL_PUB',
432 p_procedure_name => 'DERIVE_RESOURCE_CAPACITY',
433 p_error_text => SUBSTR(SQLERRM,1,240));
434 END IF;
435 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
436 p_count => x_msg_count,
437 p_data => X_msg_data);
438 IF G_DEBUG='Y' THEN
439
440 -- Debug info.
441 AHL_DEBUG_PUB.log_app_messages (
442 x_msg_count, x_msg_data, 'SQL ERROR' );
443 AHL_DEBUG_PUB.debug( 'ahl_ltp_resrc_levl_pub. Derive Resource Consum','+RSRLG+');
444 -- Check if API is called in debug mode. If yes, disable debug.
445 AHL_DEBUG_PUB.disable_debug;
446 END IF;
447
448 END Derive_Resource_Consum;
449 --
450 END AHL_LTP_RESRC_LEVL_PUB;