[Home] [Help]
PACKAGE BODY: APPS.AHL_PRD_WO_LOGIN_PVT
Source
1 PACKAGE BODY AHL_PRD_WO_LOGIN_PVT AS
2 /* $Header: AHLVLGNB.pls 120.8 2008/02/29 01:58:57 sracha ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_PRD_WO_Login_PVT';
5
6 G_DEBUG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7 G_DEBUG_PROC CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
8 G_DEBUG_STMT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
9
10 ------------------------------
11 -- Declare Local Procedures --
12 ------------------------------
13
14 -- Convert WO Values to IDs.
15 -- Used by both login and logout procedures.
16 PROCEDURE ConvertWO_Value_to_IDs (x_return_status OUT NOCOPY VARCHAR2,
17 p_employee_num IN VARCHAR2,
18 p_workorder_name IN VARCHAR2,
19 p_org_code IN VARCHAR2,
20 p_x_employee_id IN OUT NOCOPY NUMBER,
21 p_x_workorder_id IN OUT NOCOPY NUMBER,
22 p_x_operation_seq_num IN OUT NOCOPY NUMBER,
23 p_x_resource_seq_num IN OUT NOCOPY NUMBER,
24 p_x_resource_id IN OUT NOCOPY NUMBER);
25
26 -- Procedure to validate and login user into a workorder.
27 PROCEDURE Process_WO_Login (x_return_status OUT NOCOPY VARCHAR2,
28 p_employee_id IN NUMBER,
29 p_workorder_id IN NUMBER,
30 p_workorder_name IN VARCHAR2,
31 p_user_role IN VARCHAR2);
32
33
34 -- Procedure to validate and login user into a operation.
35 PROCEDURE Process_OP_Login (x_return_status OUT NOCOPY VARCHAR2,
36 p_employee_id IN NUMBER,
37 p_workorder_id IN NUMBER,
38 p_workorder_name IN VARCHAR2,
39 p_operation_seq_num IN NUMBER,
40 p_user_role IN VARCHAR2);
41
42 -- Procedure to validate and login user into a operation-resource.
43 PROCEDURE Process_RES_Login (x_return_status OUT NOCOPY VARCHAR2,
44 p_employee_id IN NUMBER,
45 p_workorder_id IN NUMBER,
46 p_workorder_name IN VARCHAR2,
47 p_operation_seq_num IN NUMBER,
48 p_resource_seq_num IN NUMBER,
49 p_resource_id IN NUMBER,
50 p_user_role IN VARCHAR2);
51
52
53 -- Procedure to validate and logout user from a workorder, operation, operation-resource.
54 PROCEDURE Process_WO_Logout (x_return_status OUT NOCOPY VARCHAR2,
55 p_employee_id IN NUMBER,
56 p_workorder_id IN NUMBER,
57 p_operation_seq_num IN NUMBER,
58 p_resource_seq_num IN NUMBER,
59 p_resource_id IN NUMBER,
60 p_user_role IN VARCHAR2);
61
62
63
64 ------------------------------
65 -- Definition of Procedures --
66 ------------------------------
67
68 -- Start of Comments --
69 -- Procedure name : Workorder_Login
70 --
71 -- Parameters :
72 --
73 --
74 -- Description : This API logs a technician onto a workorder or operation. If the
75 -- operation sequence number passed to the API is null, then the login
76 -- is done at the workorder level; if the resource sequence or resource ID is not
77 -- passed but the workorder and operation is passed, then the login is at operation level.
78 -- If resource details are passed, then login is at the operation and resource level.
79 --
80 PROCEDURE Workorder_Login(p_api_version IN NUMBER,
81 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
82 p_commit IN VARCHAR2 := FND_API.G_FALSE,
83 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
84 p_module_type IN VARCHAR2 := NULL,
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_msg_count OUT NOCOPY NUMBER,
87 x_msg_data OUT NOCOPY VARCHAR2,
88 p_employee_num IN NUMBER := NULL,
89 p_employee_id IN NUMBER := NULL,
90 p_workorder_name IN VARCHAR2 := NULL,
91 p_workorder_id IN NUMBER := NULL,
92 p_org_code IN VARCHAR2 := NULL,
93 p_operation_seq_num IN NUMBER := NULL,
94 p_resource_seq_num IN NUMBER := NULL,
95 p_resource_id IN NUMBER := NULL)
96
97 IS
98
99
100 -- get WO name.
101 CURSOR c_wo_name (p_workorder_id IN NUMBER) IS
102 SELECT AW.workorder_name
103 FROM AHL_WORKORDERS AW
104 WHERE AW.WORKORDER_ID = p_workorder_id;
105
106 l_api_version CONSTANT NUMBER := 1.0;
107 l_api_name CONSTANT VARCHAR2(30) := 'Workorder_Login';
108
109
110 l_employee_num NUMBER;
111 l_employee_id NUMBER;
112 l_workorder_name ahl_workorders.workorder_name%TYPE;
113 l_workorder_id NUMBER;
114 l_org_code mtl_parameters.organization_id%TYPE;
115 l_operation_seq_num NUMBER;
116 l_resource_seq_num NUMBER;
117 l_resource_id NUMBER;
118
119 l_user_role VARCHAR2(80);
120
121 BEGIN
122
123 -- log debug message.
124 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
125 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Login.begin',
126 'At the start of PLSQL procedure' );
127 END IF;
128
129 -- Standard call to check for api compatibility
130 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
131 G_PKG_NAME) THEN
132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
133 END IF;
134
135 -- Standard start of API savepoint
136 SAVEPOINT Workorder_Login_Pvt;
137
138 -- Initialize message list if p_init_msg_list is set to TRUE
139 IF FND_API.To_Boolean(p_init_msg_list) THEN
140 FND_MSG_PUB.Initialize;
141 END IF;
142
143 -- Initialize Procedure return status to success
144 x_return_status := FND_API.G_RET_STS_SUCCESS;
145
146
147 -- Dump Input parameters.
148 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
149 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.Dump',
150 'p_api_version: ' || p_api_version );
151
152 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.Dump',
153 'p_init_msg_list:' || p_init_msg_list );
154
155 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.Dump',
156 'p_commit:' || p_commit );
157
158 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.Dump',
159 'p_validation_level:' || p_validation_level);
160
161 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.Dump',
162 'p_module_type:' || p_module_type );
163
164 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.Dump',
165 'p_employee_num:' || p_employee_num);
166
167 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.Dump',
168 'p_employee_id:' || p_employee_id);
169
170 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.Dump',
171 'p_workorder_name:' || p_workorder_name);
172
173 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.Dump',
174 'p_workorder_id:' || p_workorder_id);
175
176 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.Dump',
177 'p_org_code:' || p_org_code);
178
179 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.Dump',
180 'p_operation_seq_num:' || p_operation_seq_num);
181
182 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.Dump',
183 'p_resource_seq_num:' || p_resource_seq_num);
184
185 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.Dump',
186 'p_resource_id:' || p_resource_id);
187 END IF;
188
189 -- Check if login/logout enabled.
190 l_user_role := get_user_role();
191
192 IF (NVL(FND_PROFILE.value('AHL_MANUAL_RES_TXN'),'N') = 'Y') OR
193 (l_user_role = AHL_PRD_UTIL_PKG.G_DATA_CLERK) THEN
194 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_AUTOTXN_DSBLD');
195 FND_MSG_PUB.ADD;
196 RAISE FND_API.G_EXC_ERROR;
197 END IF;
198
199 -- Initialize local variables.
200 l_employee_num := p_employee_num;
201 l_employee_id := p_employee_id;
202 l_workorder_name := p_workorder_name;
203 l_workorder_id := p_workorder_id;
204 l_org_code := p_org_code;
205 l_operation_seq_num := p_operation_seq_num;
206 l_resource_seq_num := p_resource_seq_num;
207 l_resource_id := p_resource_id;
208
209 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
210 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Login',
211 'Before call to ConvertWO_Value_to_IDs' );
212 END IF;
213
214 -- Convert Values to IDs.
215 ConvertWO_Value_to_IDs (p_employee_num => l_employee_num,
216 p_x_employee_id => l_employee_id,
217 p_workorder_name => l_workorder_name,
218 p_x_workorder_id => l_workorder_id,
219 p_org_code => l_org_code,
220 p_x_operation_seq_num => l_operation_seq_num,
221 p_x_resource_seq_num => l_resource_seq_num,
222 p_x_resource_id => l_resource_id,
223 x_return_status => x_return_status);
224
225 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
226 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Login',
227 'After call to ConvertWO_Value_to_IDs: return status' || x_return_status );
228 END IF;
229
230 -- Raise errors if exceptions occur
231 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
233 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
234 RAISE FND_API.G_EXC_ERROR;
235 END IF;
236
237 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
238 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Login',
239 'After call to ConvertWO_Value_to_IDs: return status' || x_return_status );
240 END IF;
241
242
243 -- Validate workorder.
244 IF (l_workorder_id IS NULL) THEN
245 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_WO_NULL');
246 --FND_MESSAGE.set_token('WO_ID',l_workorder_id);
247 FND_MSG_PUB.ADD;
248 RAISE FND_API.G_EXC_ERROR;
249 ELSE
250 -- get WO number to display error message.
251 OPEN c_wo_name(p_workorder_id);
252 FETCH c_wo_name INTO l_workorder_name;
253 IF (c_wo_name%NOTFOUND) THEN
254 CLOSE c_wo_name;
255 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_WO_INVALID');
256 FND_MESSAGE.set_token('WO_ID',l_workorder_id);
257 FND_MSG_PUB.ADD;
258 RAISE FND_API.G_EXC_ERROR;
259
260 END IF;
261
262 CLOSE c_wo_name;
263
264 END IF;
265
266
267 -- If employee ID is NULL then default logged in user.
268 IF (p_employee_id IS NULL) THEN
269 l_employee_id := AHL_PRD_WO_LOGIN_PVT.Get_Employee_ID();
270 ELSE
271 l_employee_id := p_employee_id;
272 END IF;
273
274 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
275 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login',
276 'Check Emp and Role:' || l_employee_id || ':' || l_user_role);
277 END IF;
278
279 -- process based on input parameters.
280 IF (l_resource_id IS NOT NULL OR l_resource_seq_num IS NOT NULL) THEN
281
282 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
283 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Login',
284 'Processing for Resource login');
285 END IF;
286 -- Process resource Login
287 Process_RES_Login (p_employee_id => l_employee_id,
288 p_workorder_id => l_workorder_id,
289 p_workorder_name => l_workorder_name,
290 p_operation_seq_num => l_operation_seq_num,
291 p_resource_seq_num => l_resource_seq_num,
292 p_resource_id => l_resource_id,
293 p_user_role => l_user_role,
294 x_return_status => x_return_status);
295
296 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
297 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Login',
298 'After call to Process_RES_Login: return status' || x_return_status );
299
300 END IF;
301
302 ELSIF (l_operation_seq_num IS NOT NULL) THEN
303
304 -- Process for operation login.
305
306 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
307 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Login',
308 'Processing for Operation login');
309 END IF;
310
311 Process_OP_Login (p_employee_id => l_employee_id,
312 p_workorder_id => l_workorder_id,
313 p_workorder_name => l_workorder_name,
314 p_operation_seq_num => l_operation_seq_num,
315 p_user_role => l_user_role,
316 x_return_status => x_return_status);
317
318 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
319 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Login',
320 'After call to Process_OP_Login: return status' || x_return_status );
321 END IF;
322
323
324 ELSE
325 -- Process for workorder login.
326
327 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
328 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Login',
329 'Processing for Workorder login');
330 END IF;
331
332 Process_WO_Login (p_employee_id => l_employee_id,
333 p_workorder_id => l_workorder_id,
334 p_workorder_name => l_workorder_name,
335 p_user_role => l_user_role,
336 x_return_status => x_return_status);
337
338 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
339 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Login',
340 'After call to Process_WO_Login: return status' || x_return_status );
341 END IF;
342
343
344 END IF; --l_resource_id IS NOT NULL
345
346
347 -- Raise errors if exceptions occur
348 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
350 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
351 RAISE FND_API.G_EXC_ERROR;
352 END IF;
353
354 -- Standard check of p_commit
355 IF FND_API.TO_BOOLEAN(p_commit) THEN
356 COMMIT WORK;
357 END IF;
358
359 -- Standard call to get message count and if count is 1, get message info
360 FND_MSG_PUB.Count_And_Get
361 ( p_count => x_msg_count,
362 p_data => x_msg_data,
363 p_encoded => fnd_api.g_false
364 );
365
366 -- log debug message.
367 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
368 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Login.End',
369 'Exiting Procedure' );
370 END IF;
371
372 --
373 EXCEPTION
374 WHEN FND_API.G_EXC_ERROR THEN
375 x_return_status := FND_API.G_RET_STS_ERROR;
376 Rollback to Workorder_Login_PVT;
377 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
378 p_data => x_msg_data,
379 p_encoded => fnd_api.g_false);
380
381
382 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
384 Rollback to Workorder_Login_PVT;
385 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
386 p_data => x_msg_data,
387 p_encoded => fnd_api.g_false);
388
389 WHEN OTHERS THEN
390
391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
392 Rollback to Workorder_Login_PVT;
393 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
394 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
395 p_procedure_name => 'Workorder_Login',
396 p_error_text => SUBSTR(SQLERRM,1,240));
397 END IF;
398 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
399 p_data => x_msg_data,
400 p_encoded => fnd_api.g_false);
401
402
403 END Workorder_Login;
404 ---------------------------------------------------------------------------------------------
405
406 -- Convert WO Values to IDs.
407 -- Used by both login and logout procedures.
408 PROCEDURE ConvertWO_Value_to_IDs (x_return_status OUT NOCOPY VARCHAR2,
409 p_employee_num IN VARCHAR2,
410 p_workorder_name IN VARCHAR2,
411 p_org_code IN VARCHAR2,
412 p_x_employee_id IN OUT NOCOPY NUMBER,
413 p_x_workorder_id IN OUT NOCOPY NUMBER,
414 p_x_operation_seq_num IN OUT NOCOPY NUMBER,
415 p_x_resource_seq_num IN OUT NOCOPY NUMBER,
416 p_x_resource_id IN OUT NOCOPY NUMBER)
417
418 IS
419
420 CURSOR c_get_wo_id (p_workorder_name VARCHAR2,
421 p_org_code VARCHAR2) IS
422 SELECT workorder_id
423 FROM AHL_WORKORDERS AWOS, WIP_DISCRETE_JOBS WIP,
424 ORG_ORGANIZATION_DEFINITIONS ORG
425 WHERE AWOS.WIP_ENTITY_ID = WIP.WIP_ENTITY_ID
426 AND AWOS.WORKORDER_NAME = p_workorder_name
427 AND WIP.ORGANIZATION_ID = ORG.ORGANIZATION_ID
428 AND ORG.ORGANIZATION_CODE = p_org_code;
429
430
431 l_workorder_id NUMBER;
432
433 BEGIN
434
435 -- Initialize Procedure return status to success
436 x_return_status := FND_API.G_RET_STS_SUCCESS;
437
438 -- Employee num/ID
439 IF (p_x_employee_id IS NULL AND p_employee_num IS NOT NULL) THEN
440 p_x_employee_id := AHL_PRD_WO_LOGIN_PVT.Get_Employee_ID(p_employee_num);
441 END IF; -- p_x_employee_id IS NULL
442
443 -- Workorder Num/ID
444 IF (p_x_workorder_id IS NULL) THEN
445 IF (p_workorder_name IS NULL OR p_org_code IS NULL) THEN
446 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_WO_NULL');
447 FND_MSG_PUB.ADD;
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 ELSE
450 OPEN c_get_wo_id(p_workorder_name, p_org_code);
451 FETCH c_get_wo_id INTO l_workorder_id;
452 IF (c_get_wo_id%NOTFOUND) THEN
453 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_WO_INVALID');
454 FND_MESSAGE.set_token('WO_NUM', p_workorder_name);
455 FND_MESSAGE.set_token('ORG_CODE', p_org_code);
456 FND_MSG_PUB.ADD;
457 x_return_status := FND_API.G_RET_STS_ERROR;
458 ELSE
459 p_x_workorder_id := l_workorder_id;
460 END IF;
461 CLOSE c_get_wo_id;
462 END IF; -- p_workorder_name IS NULL
463
464 END IF; -- p_x_workorder_id IS NULL
465
466 END ConvertWO_Value_to_IDs;
467 ---------------------------------------------------------------------------------------------
468 -- Procedure to validate and login user into a operation-resource.
469 PROCEDURE Process_RES_Login (x_return_status OUT NOCOPY VARCHAR2,
470 p_employee_id IN NUMBER,
471 p_workorder_id IN NUMBER,
472 p_workorder_name IN VARCHAR2,
473 p_operation_seq_num IN NUMBER,
474 p_resource_seq_num IN NUMBER,
475 p_resource_id IN NUMBER,
476 p_user_role IN VARCHAR2)
477
478 IS
479
480 -- Lock specific WO-operation.
481 CURSOR c_lock_wo_oper (p_workorder_id IN NUMBER,
482 p_operation_seq_num IN NUMBER) IS
483 SELECT AWO.STATUS_CODE
484 FROM AHL_WORKORDER_OPERATIONS AWO
485 WHERE AWO.WORKORDER_ID = p_workorder_id
486 AND AWO.operation_sequence_num = p_operation_seq_num
487 FOR UPDATE OF AWO.object_version_number;
488
489 -- Query to check if an employee is qualified for a resource reqd
490 -- for a given operation.
491 -- Support for borrowed resources. Bug# 6748783.
492 CURSOR c_qualified_req(p_employee_id IN NUMBER,
493 p_workorder_id IN NUMBER,
494 p_operation_seq_num IN NUMBER,
495 p_resource_id IN NUMBER,
496 p_resource_seq_num IN NUMBER) IS
497
498 SELECT AOR.OPERATION_RESOURCE_ID, WOR.start_date, WOR.completion_date,
499 (select wo1.department_id from wip_operations wo1
500 where wo1.wip_entity_id = aw.wip_entity_id
501 and wo1.operation_seq_num = p_operation_seq_num) department_id,
502 WOR.resource_seq_num
503 FROM WIP_OPERATION_RESOURCES WOR,
504 AHL_OPERATION_RESOURCES AOR, AHL_WORKORDER_OPERATIONS AWO, AHL_WORKORDERS AW,
505 BOM_RESOURCES BRS
506 WHERE AW.workorder_id = AWO.workorder_id
507 AND WOR.wip_entity_id = AW.wip_entity_id
508 AND WOR.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
509 AND WOR.OPERATION_SEQ_NUM = AWO.OPERATION_SEQUENCE_NUM
510 AND AWO.operation_sequence_num = p_operation_seq_num
511 AND AWO.workorder_operation_id = AOR.workorder_operation_id
512 AND WOR.organization_id = BRS.organization_id
513 AND WOR.resource_id = AOR.resource_id
514 AND WOR.resource_id = BRS.resource_id
515 --AND WOR.operation_seq_num = p_operation_seq_num
516 AND BRS.resource_type = 2 -- person.
517 AND AW.workorder_id = p_workorder_id
518 AND WOR.resource_id = nvl(p_resource_id, WOR.resource_id)
519 AND WOR.resource_seq_num = nvl(p_resource_seq_num, WOR.resource_seq_num)
520 -- qualified.
521 AND EXISTS (SELECT 'x'
522 FROM mtl_employees_current_view pf,
523 bom_resource_employees bre,
524 bom_dept_res_instances bdri,
525 wip_operations wo,
526 bom_department_resources bdr
527 WHERE WO.wip_entity_id = AW.wip_entity_id
528 AND WO.operation_seq_num = AWO.operation_sequence_num
529 -- AND WO.department_id = bdri.department_id
530 AND nvl(bdr.share_from_dept_id,WO.department_id) = bdri.department_id
531 AND bdr.department_id = wo.department_id
532 AND bdr.resource_id = WOR.RESOURCE_ID
533 AND WOR.RESOURCE_ID= bdri.resource_id
534 AND bre.instance_id = bdri.instance_id
535 AND bre.resource_id = bdri.resource_id
536 AND bre.organization_id = WOR.organization_id
537 AND bre.person_id = pf.employee_id
538 AND pf.organization_id = bre.organization_id
539 AND bre.person_id = p_employee_id);
540
541 -- Check if assignment exists.
542 CURSOR c_assignment_details (p_operation_resource_id IN NUMBER,
543 p_employee_id IN NUMBER) IS
544 SELECT AWAS.Assignment_id, AWAS.object_version_number
545 FROM AHL_WORK_ASSIGNMENTS AWAS
546 WHERE AWAS.operation_resource_id = p_operation_resource_id
547 AND AWAS.employee_id = p_employee_id;
548
549 -- parameters to call Assignment API.
550 l_resrc_assign_cre_tbl AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Tbl_Type;
551 l_assignment_rec AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Rec_Type;
552 l_initial_assign_rec AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Rec_Type;
553
554 l_object_ver_num NUMBER;
555 l_assignment_id NUMBER;
556
557 l_oper_status AHL_WORKORDER_OPERATIONS.status_code%TYPE;
558
559 l_operation_resource_id NUMBER;
560 l_sysdate DATE;
561 l_duration NUMBER; -- resource reqd duration.
562
563 l_login_allowed_flag VARCHAR2(1);
564 l_msg_count NUMBER;
565 l_msg_data VARCHAR2(2000);
566
567 l_start_date DATE;
568 l_completion_date DATE;
569 l_dept_id NUMBER;
570 l_resource_seq_num NUMBER;
571
572 i NUMBER;
573 l_junk VARCHAR2(1);
574
575 BEGIN
576
577 -- log debug message.
578 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
579 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login.Start',
580 'At the Start of procedure AHL_PRD_WO_LOGIN_PVT.Process_RES_Login');
581 END IF;
582
583 -- Dump of input parameters.
584 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
585 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login.Input_Dump',
586 'p_employee_id:' || p_employee_id);
587 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login.Input_Dump',
588 'p_workorder_id:' || p_workorder_id);
589 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login.Input_Dump',
590 'p_operation_seq_num:' || p_operation_seq_num);
591 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login.Input_Dump',
592 'p_resource_seq_num:' || p_resource_seq_num);
593 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login.Input_Dump',
594 'p_resource_id:' || p_resource_id);
595 END IF;
596
597 -- Initialize Procedure return status to success
598 x_return_status := FND_API.G_RET_STS_SUCCESS;
599
600 -- Based on input, lock WO and Operation records.
601 IF (p_operation_seq_num IS NOT NULL) THEN
602 OPEN c_lock_wo_oper (p_workorder_id, p_operation_seq_num);
603 FETCH c_lock_wo_oper INTO l_oper_status;
604 IF (c_lock_wo_oper%NOTFOUND) THEN
605
606 CLOSE c_lock_wo_oper;
607
608 -- add error to stack.
609 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_OP_INVALID');
610 FND_MESSAGE.SET_TOKEN('OP_NUM', p_operation_seq_num);
611 FND_MESSAGE.set_token('WO_NUM' , p_workorder_name);
612 FND_MSG_PUB.ADD;
613 RAISE FND_API.G_EXC_ERROR;
614 END IF;
615 CLOSE c_lock_wo_oper;
616
617 -- check operation status.
618 IF (l_oper_status <> '2') THEN
619 -- add error to stack.
620 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_OP_INVALID');
621 FND_MESSAGE.SET_TOKEN('OP_NUM', p_operation_seq_num);
622 FND_MESSAGE.set_token('WO_NUM' , p_workorder_name);
623 FND_MSG_PUB.ADD;
624 RAISE FND_API.G_EXC_ERROR;
625 END IF;
626
627 END IF; -- p_operation_seq_num IS NOT NULL
628
629 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
630 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login',
631 'After locking rows');
632 END IF;
633
634 -- Call procedure to check if login allowed or not.
635 l_login_allowed_flag := Is_Login_Allowed(p_employee_id => p_employee_id,
636 p_workorder_id => p_workorder_id,
637 p_operation_seq_num => p_operation_seq_num,
638 p_resource_seq_num => p_resource_seq_num,
639 p_resource_id => p_resource_id,
640 p_fnd_function_name => p_user_role);
641
642 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
643 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login',
644 'After call to Is_Login_Allowed procedure:l_login_allowed_flag:' || l_login_allowed_flag);
645 END IF;
646
647 -- Error out based on login allowed flag.
648 IF (l_login_allowed_flag = FND_API.G_FALSE) THEN
649 RAISE FND_API.G_EXC_ERROR;
650 END IF;
651
652 -- Initialize resource assignment table index.
653 i := 1;
654
655 l_initial_assign_rec.login_date := sysdate;
656
657 IF (p_resource_id IS NULL AND p_resource_seq_num IS NULL) THEN
658 -- add error to stack.
659 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_RES_NULL');
660 FND_MESSAGE.SET_TOKEN('OP_NUM', p_operation_seq_num);
661 FND_MESSAGE.set_token('WO_NUM' , p_workorder_name);
662 FND_MSG_PUB.ADD;
663 RAISE FND_API.G_EXC_ERROR;
664 END IF;
665
666
667 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
668 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login',
669 'Start login processing for Operation-Resource');
670 END IF;
671
672 -- process for technician and transit tech roles.
673 -- Commented out user_role condn to fix bug 5015149.
674 --
675 --IF (p_user_role = ahl_prd_util_pkg.G_TECH_MYWO) THEN
676
677 -- Create assignment and login record.
678 -- login into a specific resource.
679 OPEN c_qualified_req(p_employee_id => p_employee_id,
680 p_workorder_id => p_workorder_id,
681 p_operation_seq_num => p_operation_seq_num,
682 p_resource_seq_num => p_resource_seq_num,
683 p_resource_id => p_resource_id );
684 FETCH c_qualified_req INTO l_operation_resource_id, l_start_date, l_completion_date,
685 l_dept_id, l_resource_seq_num;
686 IF (c_qualified_req%FOUND) THEN
687
688 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
689 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login',
690 'Processing for..resource:operation:resource_seq:' || p_resource_id || ':' || p_operation_seq_num
691 || ':' || p_resource_seq_num);
692 END IF;
693
694 -- check if assignment exists.
695 OPEN c_assignment_details(l_operation_resource_id, p_employee_id);
696 FETCH c_assignment_details INTO l_assignment_id, l_object_ver_num;
697 IF (c_assignment_details%NOTFOUND) THEN
698
699 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
700 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login',
701 'Assignment not found ..');
702 END IF;
703
704 -- Create assignment record.
705 l_sysdate := sysdate;
706 l_assignment_rec.workorder_id := p_workorder_id;
707 l_assignment_rec.operation_seq_number := p_operation_seq_num;
708 l_assignment_rec.resource_seq_number := p_resource_seq_num;
709 l_assignment_rec.OPER_RESOURCE_ID := l_operation_resource_id;
710 l_assignment_rec.department_id := l_dept_id;
711 l_assignment_rec.employee_id := p_employee_id;
712 l_assignment_rec.assign_start_date := trunc(l_start_date);
713 l_assignment_rec.assign_start_hour := to_number(to_char(l_start_date, 'HH24'));
714 l_assignment_rec.assign_start_min := to_number(to_char(l_start_date, 'MI'));
715 l_assignment_rec.assign_end_date := trunc(l_completion_date);
716 l_assignment_rec.assign_end_hour := to_number(to_char(l_completion_date, 'HH24'));
717 l_assignment_rec.assign_end_min := to_number(to_char(l_completion_date,'MI'));
718
719 --l_assignment_rec.login_date := sysdate;
720 l_assignment_rec.self_assigned_flag := 'Y';
721 l_assignment_rec.operation_flag := 'C';
722
723 l_resrc_assign_cre_tbl(i) := l_assignment_rec;
724
725 ELSE --c_assignment_details%NOTFOUND
726
727 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
728 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login',
729 'Assignment found ..');
730 END IF;
731
732 END IF; -- c_assignment_details%NOTFOUND
733 CLOSE c_assignment_details;
734
735 ELSE -- c_qualified_req%FOUND
736
737 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
738 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login',
739 'Error - Resource is not found');
740 END IF;
741
742 CLOSE c_qualified_req;
743 -- add error to stack.
744 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_RES_INVALID');
745 FND_MESSAGE.SET_TOKEN('OP_NUM', p_operation_seq_num);
746 FND_MESSAGE.set_token('WO_NUM' , p_workorder_name);
747 FND_MSG_PUB.ADD;
748 RAISE FND_API.G_EXC_ERROR;
749
750 END IF; -- c_qualified_req%FOUND
751 CLOSE c_qualified_req;
752
753 -- Call Assignment Create API.
754 IF (l_resrc_assign_cre_tbl.COUNT > 0) THEN
755
756 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
757 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login',
758 'Before calling Create AHL_PP_RESRC_ASSIGN_PVT.Process_Resrc_Assign:tbl count:' || l_resrc_assign_cre_tbl.count);
759 END IF;
760
761 AHL_PP_RESRC_ASSIGN_PVT.Process_Resrc_Assign (
762 p_api_version => 1.0,
763 p_commit => Fnd_Api.G_FALSE,
764 p_operation_flag => 'C',
765 p_x_resrc_assign_tbl => l_resrc_assign_cre_tbl,
766 x_return_status => x_return_status,
767 x_msg_count => l_msg_count,
768 x_msg_data => l_msg_data);
769
770
771
772 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
773 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login',
774 'After calling Create AHL_PP_RESRC_ASSIGN_PVT.Process_Resrc_Assign:x_return_status:' || x_return_status);
775 END IF;
776
777 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
779 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
780 RAISE FND_API.G_EXC_ERROR;
781 END IF;
782
783 END IF; -- l_resrc_assign_cre_tbl.COUNT > 0
784
785 -- END IF; -- techinician user role check.
786
787 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
788 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login',
789 'Inserting into ahl_work_login_times.');
790 END IF;
791
792 -- insert login date and time.
793 insert into ahl_work_login_times(
794 work_login_time_id,
795 workorder_id,
796 operation_seq_num,
797 resource_seq_num,
798 operation_resource_id,
799 employee_id,
800 login_date,
801 object_version_number,
802 login_level,
803 LAST_UPDATE_DATE,
804 LAST_UPDATED_BY,
805 CREATION_DATE,
806 CREATED_BY,
807 LAST_UPDATE_LOGIN)
808 values (
809 ahl_work_login_times_s.nextval,
810 p_workorder_id,
811 p_operation_seq_num,
812 p_resource_seq_num,
813 l_operation_resource_id,
814 p_employee_id,
815 sysdate,
816 1,
817 'R',
818 sysdate,
819 fnd_global.user_id,
820 sysdate,
821 fnd_global.user_id,
822 fnd_global.login_id
823 );
824
825 -- log debug message.
826 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
827 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_RES_Login.End',
828 'At the End of procedure AHL_PRD_WO_LOGIN_PVT.Process_RES_Login');
829 END IF;
830
831
832 END Process_RES_Login;
833 ----------------------------------------------------------------------------------------
834 -- Procedure to validate and login user into a operation.
835 PROCEDURE Process_OP_Login (x_return_status OUT NOCOPY VARCHAR2,
836 p_employee_id IN NUMBER,
837 p_workorder_id IN NUMBER,
838 p_workorder_name IN VARCHAR2,
839 p_operation_seq_num IN NUMBER,
840 p_user_role IN VARCHAR2 )
841
842 IS
843
844
845 -- Lock specific WO-operation.
846 CURSOR c_lock_wo_oper (p_workorder_id IN NUMBER,
847 p_operation_seq_num IN NUMBER) IS
848 SELECT AWO.STATUS_CODE
849 FROM AHL_WORKORDER_OPERATIONS AWO
850 WHERE AWO.WORKORDER_ID = p_workorder_id
851 AND AWO.operation_sequence_num = p_operation_seq_num
852 FOR UPDATE OF AWO.object_version_number;
853
854 -- Query to get all qualified resource reqd for an Operation and employee
855 -- Fixed query to support borrowed resources.
856 CURSOR c_qualified_req_oper(p_employee_id IN NUMBER,
857 p_workorder_id IN NUMBER,
858 p_operation_seq_num IN NUMBER) IS
859
860 SELECT AOR.OPERATION_RESOURCE_ID, WOR.resource_seq_num, WOR.resource_id,
861 WOR.start_date, WOR.completion_date,
862 (select wo1.department_id from wip_operations wo1
863 where wo1.wip_entity_id = aw.wip_entity_id
864 and wo1.operation_seq_num = p_operation_seq_num) department_id
865 FROM WIP_OPERATION_RESOURCES WOR,
866 AHL_OPERATION_RESOURCES AOR, AHL_WORKORDER_OPERATIONS AWO, AHL_WORKORDERS AW,
867 BOM_RESOURCES BRS
868 WHERE AW.workorder_id = AWO.workorder_id
869 AND AWO.operation_sequence_num = p_operation_seq_num
870 AND WOR.wip_entity_id = AW.wip_entity_id
871 AND WOR.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
872 AND WOR.OPERATION_SEQ_NUM = AWO.OPERATION_SEQUENCE_NUM
873 AND AWO.workorder_operation_id = AOR.workorder_operation_id
874 AND WOR.organization_id = BRS.organization_id
875 AND WOR.resource_id = AOR.resource_id
876 AND WOR.resource_id = BRS.resource_id
877 --AND WOR.operation_seq_num = p_operation_seq_num
878 AND BRS.resource_type = 2 -- person.
879 AND AW.workorder_id = p_workorder_id
880 -- qualified.
881 AND EXISTS (SELECT 'x'
882 FROM mtl_employees_current_view pf,
883 bom_resource_employees bre,
884 bom_dept_res_instances bdri,
885 wip_operations wo,
886 bom_department_resources bdr
887 WHERE WO.wip_entity_id = AW.wip_entity_id
888 AND WO.operation_seq_num = AWO.operation_sequence_num
889 --AND WO.department_id = bdri.department_id
890 AND nvl(bdr.share_from_dept_id,WO.department_id) = bdri.department_id
891 AND bdr.department_id = wo.department_id
892 AND bdr.resource_id = WOR.RESOURCE_ID
893 AND WOR.RESOURCE_ID= bdri.resource_id
894 AND bre.instance_id = bdri.instance_id
895 AND bre.resource_id = bdri.resource_id
896 AND bre.organization_id = WOR.organization_id
897 AND bre.person_id = pf.employee_id
898 AND pf.organization_id = bre.organization_id
899 AND bre.person_id = p_employee_id);
900
901 -- Check if assignment exists.
902 CURSOR c_assignment_details (p_operation_resource_id IN NUMBER,
903 p_employee_id IN NUMBER) IS
904 SELECT AWAS.Assignment_id, AWAS.object_version_number
905 FROM AHL_WORK_ASSIGNMENTS AWAS
906 WHERE AWAS.operation_resource_id = p_operation_resource_id
907 AND AWAS.employee_id = p_employee_id;
908
909
910 -- parameters to call Assignment API.
911 l_resrc_assign_cre_tbl AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Tbl_Type;
912 l_assignment_rec AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Rec_Type;
913 l_initial_assign_rec AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Rec_Type;
914
915 l_object_ver_num NUMBER;
916 l_assignment_id NUMBER;
917
918 l_oper_status AHL_WORKORDER_OPERATIONS.status_code%TYPE;
919 l_wo_status AHL_WORKORDERS.status_code%TYPE;
920
921 l_operation_resource_id NUMBER;
922 l_sysdate DATE;
923 l_duration NUMBER; -- resource reqd duration.
924
925 l_login_allowed_flag VARCHAR2(1);
926 l_msg_count NUMBER;
927 l_msg_data VARCHAR2(2000);
928
929 l_start_date DATE;
930 l_completion_date DATE;
931 l_dept_id NUMBER;
932 l_resource_seq_num NUMBER;
933
934 i NUMBER;
935
936 BEGIN
937
938 -- log debug message.
939 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
940 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_OP_Login.Start',
941 'At the Start of procedure AHL_PRD_WO_LOGIN_PVT.Process_OP_Login');
942 END IF;
943
944 -- Dump of input parameters.
945 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
946 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_OP_Login.Input_Dump',
947 'p_employee_id:' || p_employee_id);
948 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_OP_Login.Input_Dump',
949 'p_workorder_id:' || p_workorder_id);
950 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_OP_Login.Input_Dump',
951 'p_operation_seq_num:' || p_operation_seq_num);
952 END IF;
953
954 -- Initialize Procedure return status to success
955 x_return_status := FND_API.G_RET_STS_SUCCESS;
956
957 -- Based on input, lock WO and Operation records.
958 IF (p_operation_seq_num IS NOT NULL) THEN
959 OPEN c_lock_wo_oper (p_workorder_id, p_operation_seq_num);
960 FETCH c_lock_wo_oper INTO l_oper_status;
961 IF (c_lock_wo_oper%NOTFOUND) THEN
962 CLOSE c_lock_wo_oper;
963
964 -- add error to stack.
965 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_OP_INVALID');
966 FND_MESSAGE.SET_TOKEN('OP_NUM', p_operation_seq_num);
967 FND_MESSAGE.set_token('WO_NUM' , p_workorder_name);
968 FND_MSG_PUB.ADD;
969 RAISE FND_API.G_EXC_ERROR;
970 ELSIF (l_oper_status <> 2) THEN
971 CLOSE c_lock_wo_oper;
972
973 -- add error to stack.
974 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_OP_INVALID');
975 FND_MESSAGE.SET_TOKEN('OP_NUM', p_operation_seq_num);
976 FND_MESSAGE.set_token('WO_NUM' , p_workorder_name);
977 FND_MSG_PUB.ADD;
978 RAISE FND_API.G_EXC_ERROR;
979 END IF;
980 CLOSE c_lock_wo_oper;
981
982 ELSE
983
984 -- add error to stack.
985 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_OP_NULL');
986 FND_MESSAGE.set_token('WO_NUM' , p_workorder_name);
987 FND_MSG_PUB.ADD;
988 RAISE FND_API.G_EXC_ERROR;
989 END IF; -- p_operation_seq_num IS NOT NULL
990
991 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
992 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_OP_Login',
993 'After locking rows');
994 END IF;
995
996 -- Call procedure to check if login allowed or not.
997 l_login_allowed_flag := Is_Login_Allowed(p_employee_id => p_employee_id,
998 p_workorder_id => p_workorder_id,
999 p_operation_seq_num => p_operation_seq_num,
1000 p_fnd_function_name => p_user_role);
1001
1002 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1003 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_OP_login',
1004 'After call to Is_Login_Allowed procedure:l_login_allowed_flag:' || l_login_allowed_flag);
1005 END IF;
1006
1007 -- Error out based on login allowed flag.
1008 IF (l_login_allowed_flag = FND_API.G_FALSE) THEN
1009 RAISE FND_API.G_EXC_ERROR;
1010 END IF;
1011
1012 -- Commented out user_role condn to fix bug 5015149.
1013 --IF (p_user_role = ahl_prd_util_pkg.G_TECH_MYWO) THEN
1014
1015 -- For creating assignment user needs to be qualified.
1016 -- Initialize resource assignment table index.
1017 i := 1;
1018
1019 l_initial_assign_rec.login_date := sysdate;
1020
1021 -- initialize login
1022 -- Create assignments and login records based on login type.
1023 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1024 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_OP_Login',
1025 'Processing for Operation:' || p_operation_seq_num);
1026 END IF;
1027
1028 -- Loop through all resources for an operation.
1029 FOR resrc_oper_rec IN c_qualified_req_oper(p_employee_id, p_workorder_id,
1030 p_operation_seq_num)
1031 LOOP
1032 -- Check if assignment exists.
1033 OPEN c_assignment_details(resrc_oper_rec.operation_resource_id, p_employee_id);
1034 FETCH c_assignment_details INTO l_assignment_id, l_object_ver_num;
1035 IF (c_assignment_details%NOTFOUND) THEN
1036
1037 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1038 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_OP_Login',
1039 'Assignment found ..');
1040 END IF;
1041 -- Create assignment record.
1042 l_assignment_rec := l_initial_assign_rec;
1043
1044 l_assignment_rec.workorder_id := p_workorder_id;
1045 l_assignment_rec.operation_seq_number := p_operation_seq_num;
1046 l_assignment_rec.resource_seq_number := resrc_oper_rec.resource_seq_num;
1047 l_assignment_rec.oper_resource_id := resrc_oper_rec.operation_resource_id;
1048 l_assignment_rec.department_id := resrc_oper_rec.department_id;
1049 l_assignment_rec.employee_id := p_employee_id;
1050 l_assignment_rec.assign_start_date := trunc(resrc_oper_rec.start_date);
1051 l_assignment_rec.assign_start_hour := to_number(to_char(resrc_oper_rec.start_date, 'HH24'));
1052 l_assignment_rec.assign_start_min := to_number(to_char(resrc_oper_rec.start_date, 'MI'));
1053 l_assignment_rec.assign_end_date := trunc(resrc_oper_rec.completion_date);
1054 l_assignment_rec.assign_end_hour := to_number(to_char(resrc_oper_rec.completion_date, 'HH24'));
1055 l_assignment_rec.assign_end_min := to_number(to_char(resrc_oper_rec.completion_date,'MI'));
1056
1057 l_assignment_rec.self_assigned_flag := 'Y';
1058 l_assignment_rec.operation_flag := 'C';
1059
1060 l_resrc_assign_cre_tbl(i) := l_assignment_rec;
1061
1062 i := i + 1;
1063
1064 ELSE -- (c_assignment_details%NOTFOUND)
1065
1066 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1067 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_OP_Login',
1068 'Assignment found..');
1069 END IF;
1070
1071 END IF; -- c_assignment_details%NOTFOUND
1072 CLOSE c_assignment_details;
1073 END LOOP; -- resrc_oper_rec.
1074
1075 -- Call Assignment Create API.
1076 IF (l_resrc_assign_cre_tbl.COUNT > 0) THEN
1077
1078 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1079 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_OP_Login',
1080 'Before calling Create AHL_PP_RESRC_ASSIGN_PVT.Process_Resrc_Assign:tbl count:' || l_resrc_assign_cre_tbl.count);
1081 END IF;
1082
1083 AHL_PP_RESRC_ASSIGN_PVT.Process_Resrc_Assign (
1084 p_api_version => 1.0,
1085 p_commit => Fnd_Api.G_FALSE,
1086 p_operation_flag => 'C',
1087 p_x_resrc_assign_tbl => l_resrc_assign_cre_tbl,
1088 x_return_status => x_return_status,
1089 x_msg_count => l_msg_count,
1090 x_msg_data => l_msg_data);
1091
1092
1093
1094 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1095 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_OP_Login',
1096 'After calling Create AHL_PP_RESRC_ASSIGN_PVT.Process_Resrc_Assign:x_return_status:' || x_return_status);
1097 END IF;
1098
1099 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1101 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1102 RAISE FND_API.G_EXC_ERROR;
1103 END IF;
1104
1105 END IF;
1106
1107 -- END IF; -- p_user_role.
1108
1109 -- insert login date and time.
1110 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1111 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_OP_Login',
1112 'Before inserting into ahl_work_login_times');
1113 END IF;
1114 insert into ahl_work_login_times(
1115 work_login_time_id,
1116 workorder_id,
1117 operation_seq_num,
1118 resource_seq_num,
1119 operation_resource_id,
1120 employee_id,
1121 login_date,
1122 login_level,
1123 object_version_number,
1124 LAST_UPDATE_DATE,
1125 LAST_UPDATED_BY,
1126 CREATION_DATE,
1127 CREATED_BY,
1128 LAST_UPDATE_LOGIN)
1129 values (
1130 ahl_work_login_times_s.nextval,
1131 p_workorder_id,
1132 p_operation_seq_num,
1133 null,
1134 null,
1135 p_employee_id,
1136 sysdate,
1137 1,
1138 'O',
1139 sysdate,
1140 fnd_global.user_id,
1141 sysdate,
1142 fnd_global.user_id,
1143 fnd_global.login_id);
1144
1145 -- log debug message.
1146 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
1147 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_OP_Login.End',
1148 'At the End of procedure AHL_PRD_WO_LOGIN_PVT.Process_OP_Login');
1149 END IF;
1150
1151
1152 END Process_OP_Login;
1153 ----------------------------------------------------------------------------------------
1154 -- Procedure to validate and login user into a workorder.
1155 PROCEDURE Process_WO_Login (x_return_status OUT NOCOPY VARCHAR2,
1156 p_employee_id IN NUMBER,
1157 p_workorder_id IN NUMBER,
1158 p_workorder_name IN VARCHAR2,
1159 p_user_role IN VARCHAR2)
1160 IS
1161
1162
1163 -- lock workorder.
1164 CURSOR c_lock_wo (p_workorder_id IN NUMBER) IS
1165 SELECT AW.STATUS_CODE
1166 FROM AHL_WORKORDERS AW
1167 WHERE AW.WORKORDER_ID = p_workorder_id
1168 FOR UPDATE OF AW.object_version_number;
1169
1170 -- Lock all operation for a WO.
1171 CURSOR c_lock_wo_all_ops (p_workorder_id IN NUMBER) IS
1172 SELECT AWO.STATUS_CODE
1173 FROM AHL_WORKORDER_OPERATIONS AWO
1174 WHERE AWO.WORKORDER_ID = p_workorder_id
1175 FOR UPDATE OF AWO.object_version_number;
1176
1177 -- Query to get all qualified resources reqd for a WO and employee
1178 -- across all operations.
1179 -- Fixed query to support borrowed resources.
1180 CURSOR c_qualified_req_WO(p_employee_id IN NUMBER,
1181 p_workorder_id IN NUMBER) IS
1182
1183 SELECT AOR.OPERATION_RESOURCE_ID, WOR.operation_seq_num, WOR.resource_id,
1184 WOR.resource_seq_num, WOR.start_date, WOR.completion_date,
1185 (select wo1.department_id from wip_operations wo1
1186 where wo1.wip_entity_id = aw.wip_entity_id
1187 and wo1.operation_seq_num = AWO.operation_sequence_num) department_id
1188 FROM WIP_OPERATION_RESOURCES WOR,
1189 AHL_OPERATION_RESOURCES AOR, AHL_WORKORDER_OPERATIONS AWO, AHL_WORKORDERS AW,
1190 BOM_RESOURCES BRS
1191 WHERE AW.workorder_id = AWO.workorder_id
1192 AND WOR.wip_entity_id = AW.wip_entity_id
1193 AND WOR.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
1194 AND WOR.OPERATION_SEQ_NUM = AWO.OPERATION_SEQUENCE_NUM
1195 AND AWO.workorder_operation_id = AOR.workorder_operation_id
1196 AND WOR.resource_id = AOR.resource_id
1197 AND WOR.organization_id = BRS.organization_id
1198 AND WOR.resource_id = BRS.resource_id
1199 AND BRS.resource_type = 2 -- person.
1200 AND AW.workorder_id = p_workorder_id
1201 -- qualified.
1202 AND EXISTS (SELECT 'x'
1203 FROM mtl_employees_current_view pf,
1204 bom_resource_employees bre,
1205 bom_dept_res_instances bdri,
1206 wip_operations wo,
1207 bom_department_resources bdr
1208 WHERE WO.wip_entity_id = AW.wip_entity_id
1209 AND WO.operation_seq_num = AWO.operation_sequence_num
1210 --AND WO.department_id = bdri.department_id
1211 AND nvl(bdr.share_from_dept_id,WO.department_id) = bdri.department_id
1212 AND bdr.department_id = wo.department_id
1213 AND bdr.resource_id = WOR.RESOURCE_ID
1214 AND WOR.RESOURCE_ID= bdri.resource_id
1215 AND bre.instance_id = bdri.instance_id
1216 AND bre.resource_id = bdri.resource_id
1217 AND bre.organization_id = WOR.organization_id
1218 AND bre.person_id = pf.employee_id
1219 AND pf.organization_id = bre.organization_id
1220 AND bre.person_id = p_employee_id);
1221
1222 -- Check if assignment exists.
1223 CURSOR c_assignment_details (p_operation_resource_id IN NUMBER,
1224 p_employee_id IN NUMBER) IS
1225 SELECT AWAS.Assignment_id, AWAS.object_version_number
1226 FROM AHL_WORK_ASSIGNMENTS AWAS
1227 WHERE AWAS.operation_resource_id = p_operation_resource_id
1228 AND AWAS.employee_id = p_employee_id;
1229
1230
1231 -- parameters to call Assignment API.
1232 l_resrc_assign_cre_tbl AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Tbl_Type;
1233 l_assignment_rec AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Rec_Type;
1234 l_initial_assign_rec AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Rec_Type;
1235
1236 l_object_ver_num NUMBER;
1237 l_assignment_id NUMBER;
1238
1239 l_wo_status AHL_WORKORDERS.status_code%TYPE;
1240 l_oper_status AHL_WORKORDER_OPERATIONS.status_code%TYPE;
1241
1242 l_sysdate DATE;
1243 l_duration NUMBER; -- resource reqd duration.
1244
1245 l_login_allowed_flag VARCHAR2(1);
1246 l_msg_count NUMBER;
1247 l_msg_data VARCHAR2(2000);
1248
1249 i NUMBER;
1250
1251 BEGIN
1252
1253 -- log debug message.
1254 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
1255 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Login.Start',
1256 'At the Start of procedure AHL_PRD_WO_LOGIN_PVT.Process_WO_Login');
1257 END IF;
1258
1259 -- Dump of input parameters.
1260 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1261 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Login.Input_Dump',
1262 'p_employee_id:' || p_employee_id);
1263 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Login.Input_Dump',
1264 'p_workorder_id:' || p_workorder_id);
1265 END IF;
1266
1267 -- Initialize Procedure return status to success
1268 x_return_status := FND_API.G_RET_STS_SUCCESS;
1269
1270 -- Lock WO record.
1271 OPEN c_lock_wo (p_workorder_id);
1272 FETCH c_lock_wo INTO l_wo_status;
1273 IF (c_lock_wo%NOTFOUND) THEN
1274 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_WO_INVALID');
1275 FND_MSG_PUB.ADD;
1276 END IF;
1277 CLOSE c_lock_wo;
1278
1279 -- Lock all operation records.
1280 OPEN c_lock_wo_all_ops(p_workorder_id);
1281 FETCH c_lock_wo_all_ops INTO l_oper_status;
1282 CLOSE c_lock_wo_all_ops;
1283
1284 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1285 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Login',
1286 'After locking rows');
1287 END IF;
1288
1289 -- Call procedure to check if login allowed or not.
1290 l_login_allowed_flag := Is_Login_Allowed(p_employee_id => p_employee_id,
1291 p_workorder_id => p_workorder_id,
1292 p_fnd_function_name => p_user_role);
1293
1294 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1295 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Login',
1296 'After call to Is_Login_Allowed procedure:l_login_allowed_flag:' || l_login_allowed_flag);
1297 END IF;
1298
1299 -- Error out based on login allowed flag.
1300 IF (l_login_allowed_flag = FND_API.G_FALSE) THEN
1301 RAISE FND_API.G_EXC_ERROR;
1302 END IF;
1303
1304 -- Commented out user_role condn to fix bug 5015149.
1305 --IF (p_user_role = ahl_prd_util_pkg.G_TECH_MYWO) THEN
1306
1307 -- to create assignment, user needs to be qualified.
1308 -- Initialize resource assignment table index.
1309 i := 1;
1310
1311 l_initial_assign_rec.login_date := sysdate;
1312
1313 -- Create assignments and login records.
1314 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1315 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Login',
1316 'Processing for workorderID:' || p_workorder_id);
1317 END IF;
1318
1319 -- login into workorder.
1320 -- Loop for all operations and all resources for a workorder.
1321 FOR wo_oper_rec IN c_qualified_req_WO(p_employee_id, p_workorder_id) LOOP
1322 -- Check if assignment exists.
1323 OPEN c_assignment_details(wo_oper_rec.operation_resource_id, p_employee_id);
1324 FETCH c_assignment_details INTO l_assignment_id, l_object_ver_num;
1325 IF (c_assignment_details%NOTFOUND) THEN
1326
1327 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1328 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Login',
1329 'Assignment not found ..');
1330 END IF;
1331 -- Create assignment record.
1332 l_assignment_rec := l_initial_assign_rec;
1333
1334 l_assignment_rec.workorder_id := p_workorder_id;
1335 l_assignment_rec.operation_seq_number := wo_oper_rec.operation_seq_num;
1336 l_assignment_rec.resource_seq_number := wo_oper_rec.resource_seq_num;
1337 l_assignment_rec.oper_resource_id := wo_oper_rec.operation_resource_id;
1338 l_assignment_rec.department_id := wo_oper_rec.department_id;
1339 l_assignment_rec.employee_id := p_employee_id;
1340 l_assignment_rec.assign_start_date := trunc(wo_oper_rec.start_date);
1341 l_assignment_rec.assign_start_hour := to_number(to_char(wo_oper_rec.start_date, 'HH24'));
1342 l_assignment_rec.assign_start_min := to_number(to_char(wo_oper_rec.start_date, 'MI'));
1343 l_assignment_rec.assign_end_date := trunc(wo_oper_rec.completion_date);
1344 l_assignment_rec.assign_end_hour := to_number(to_char(wo_oper_rec.completion_date, 'HH24'));
1345 l_assignment_rec.assign_end_min := to_number(to_char(wo_oper_rec.completion_date,'MI'));
1346
1347 l_assignment_rec.self_assigned_flag := 'Y';
1348 l_assignment_rec.operation_flag := 'C';
1349
1350 l_resrc_assign_cre_tbl(i) := l_assignment_rec;
1351
1352 i := i + 1;
1353
1354 ELSE
1355
1356 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1357 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Login',
1358 'Assignment found ..');
1359 END IF;
1360
1361 END IF; -- c_assignment_details%NOTFOUND
1362 CLOSE c_assignment_details;
1363
1364 END LOOP; -- wo_oper_rec
1365
1366 -- Call Assignment Create API.
1367 IF (l_resrc_assign_cre_tbl.COUNT > 0) THEN
1368
1369 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1370 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Login',
1371 'Before calling Create AHL_PP_RESRC_ASSIGN_PVT.Process_Resrc_Assign:tbl count:' || l_resrc_assign_cre_tbl.count);
1372 END IF;
1373
1374 AHL_PP_RESRC_ASSIGN_PVT.Process_Resrc_Assign (
1375 p_api_version => 1.0,
1376 p_commit => Fnd_Api.G_FALSE,
1377 p_operation_flag => 'C',
1378 p_x_resrc_assign_tbl => l_resrc_assign_cre_tbl,
1379 x_return_status => x_return_status,
1380 x_msg_count => l_msg_count,
1381 x_msg_data => l_msg_data);
1382
1383
1384
1385 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1386 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Login',
1387 'After calling Create AHL_PP_RESRC_ASSIGN_PVT.Process_Resrc_Assign:x_return_status:' || x_return_status);
1388 END IF;
1389
1390 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1391 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1392 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1393 RAISE FND_API.G_EXC_ERROR;
1394 END IF;
1395
1396 END IF; -- l_resrc_assign_cre_tbl.COUNT
1397
1398 -- END IF; -- p_user_role.
1399
1400 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1401 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Login',
1402 'Before inserting into ahl_work_login_times');
1403 END IF;
1404
1405 -- insert login date and time.
1406 insert into ahl_work_login_times(
1407 work_login_time_id,
1408 workorder_id,
1409 operation_seq_num,
1410 resource_seq_num,
1411 operation_resource_id,
1412 employee_id,
1413 login_date,
1414 object_version_number,
1415 login_level,
1416 LAST_UPDATE_DATE,
1417 LAST_UPDATED_BY,
1418 CREATION_DATE,
1419 CREATED_BY,
1420 LAST_UPDATE_LOGIN)
1421 values (
1422 ahl_work_login_times_s.nextval,
1423 p_workorder_id,
1424 null,
1425 null,
1426 null,
1427 p_employee_id,
1428 sysdate,
1429 1,
1430 'W',
1431 sysdate,
1432 fnd_global.user_id,
1433 sysdate,
1434 fnd_global.user_id,
1435 fnd_global.login_id);
1436
1437 -- log debug message.
1438 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
1439 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Login.End',
1440 'At the End of procedure AHL_PRD_WO_LOGIN_PVT.Process_WO_Login');
1441 END IF;
1442
1443
1444 END Process_WO_Login;
1445 ---------------------------------------------------------------------------------------------
1446
1447 -- Start of Comments --
1448 -- Procedure name : Workorder_Logout
1449 --
1450 -- Parameters :
1451 -- p_employee_number Input Employee Number.
1452 --
1453 -- Description :
1454 --
1455 --
1456 --
1457 --
1458 PROCEDURE Workorder_Logout( p_api_version IN NUMBER,
1459 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1460 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1461 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1462 p_module_type IN VARCHAR2 := NULL,
1463 x_return_status OUT NOCOPY VARCHAR2,
1464 x_msg_count OUT NOCOPY NUMBER,
1465 x_msg_data OUT NOCOPY VARCHAR2,
1466 p_employee_num IN NUMBER := NULL,
1467 p_employee_id IN NUMBER := NULL,
1468 p_workorder_name IN VARCHAR2 := NULL,
1469 p_workorder_id IN NUMBER := NULL,
1470 p_org_code IN VARCHAR2 := NULL,
1471 p_operation_seq_num IN NUMBER := NULL,
1472 p_resource_seq_num IN NUMBER := NULL,
1473 p_resource_id IN NUMBER := NULL)
1474 IS
1475
1476 l_api_version CONSTANT NUMBER := 1.0;
1477 l_api_name CONSTANT VARCHAR2(30) := 'Workorder_Logout';
1478
1479
1480 l_employee_num NUMBER;
1481 l_employee_id NUMBER;
1482 l_workorder_name wip_entities.wip_entity_name%TYPE;
1483 l_workorder_id NUMBER;
1484 l_org_code mtl_parameters.organization_code%TYPE;
1485 l_operation_seq_num wip_operations.operation_seq_num%TYPE;
1486
1487 l_resource_seq_num wip_operation_resources.resource_seq_num%TYPE;
1488
1489 l_resource_id NUMBER;
1490 l_user_role VARCHAR2(80);
1491
1492 BEGIN
1493
1494 -- log debug message.
1495 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
1496 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout.begin',
1497 'At the start of PLSQL procedure' );
1498 END IF;
1499
1500
1501 -- Standard call to check for call compatibility
1502 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1503 G_PKG_NAME) THEN
1504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1505 END IF;
1506
1507 -- Standard start of API savepoint
1508 SAVEPOINT Workorder_Logout_Pvt;
1509
1510 -- Initialize message list if p_init_msg_list is set to TRUE
1511 IF FND_API.To_Boolean(p_init_msg_list) THEN
1512 FND_MSG_PUB.Initialize;
1513 END IF;
1514
1515 -- Initialize Procedure return status to success
1516 x_return_status := FND_API.G_RET_STS_SUCCESS;
1517
1518
1519 -- Dump Input parameters.
1520 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1521 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Logout.Dump',
1522 'p_api_version: ' || p_api_version );
1523
1524 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Logout.Dump',
1525 'p_init_msg_list:' || p_init_msg_list );
1526
1527 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Logout.Dump',
1528 'p_commit:' || p_commit );
1529
1530 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Logout.Dump',
1531 'p_validation_level:' || p_validation_level);
1532
1533 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Logout.Dump',
1534 'p_module_type:' || p_module_type );
1535
1536 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Logout.Dump',
1537 'p_employee_num:' || p_employee_num);
1538
1539 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Logout.Dump',
1540 'p_employee_id:' || p_employee_id);
1541
1542 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Logout.Dump',
1543 'p_workorder_name:' || p_workorder_name);
1544
1545 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Logout.Dump',
1546 'p_workorder_id:' || p_workorder_id);
1547
1548 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Logout.Dump',
1549 'p_org_code:' || p_org_code);
1550
1551 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Logout.Dump',
1552 'p_operation_seq_num:' || p_operation_seq_num);
1553
1554 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Logout.Dump',
1555 'p_resource_seq_num:' || p_resource_seq_num);
1556
1557 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_Workorder_Logout.Dump',
1558 'p_resource_id:' || p_resource_id);
1559 END IF;
1560
1561
1562 -- Check if login/logout enabled.
1563 l_user_role := get_user_role();
1564
1565 /* THis validation is not needed. Data Clerk can complete WO in which case all users
1566 should be logged out
1567 IF (NVL(FND_PROFILE.value('AHL_MANUAL_RES_TXN'),'N') = 'Y') OR
1568 (l_user_role = AHL_PRD_UTIL_PKG.G_DATA_CLERK) THEN
1569 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_AUTOTXN_DSBLD');
1570 FND_MSG_PUB.ADD;
1571 RAISE FND_API.G_EXC_ERROR;
1572 END IF; */
1573
1574 -- Initialize local variables.
1575 l_employee_num := p_employee_num;
1576 l_employee_id := p_employee_id;
1577 l_workorder_name := p_workorder_name;
1578 l_workorder_id := p_workorder_id;
1579 l_org_code := p_org_code;
1580 l_operation_seq_num := p_operation_seq_num;
1581 l_resource_seq_num := p_resource_seq_num;
1582 l_resource_id := p_resource_id;
1583
1584 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1585 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
1586 'Before call to ConvertWO_Value_to_IDs' );
1587 END IF;
1588
1589 -- Convert Values to IDs.
1590 ConvertWO_Value_to_IDs (p_employee_num => l_employee_num,
1591 p_x_employee_id => l_employee_id,
1592 p_workorder_name => l_workorder_name,
1593 p_x_workorder_id => l_workorder_id,
1594 p_org_code => l_org_code,
1595 p_x_operation_seq_num => l_operation_seq_num,
1596 p_x_resource_seq_num => l_resource_seq_num,
1597 p_x_resource_id => l_resource_id,
1598 x_return_status => x_return_status);
1599
1600 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1601 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
1602 'After call to ConvertWO_Value_to_IDs: return status' || x_return_status );
1603 END IF;
1604
1605 -- Raise error if exception occurs
1606 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1608 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1609 RAISE FND_API.G_EXC_ERROR;
1610 END IF;
1611
1612 -- If employee ID and emp num are NULL then default logged in user.
1613 IF (l_employee_id IS NULL AND l_employee_num IS NULL) THEN
1614 l_employee_id := AHL_PRD_WO_LOGIN_PVT.Get_Employee_ID();
1615 END IF;
1616
1617 -- Check required parameters.
1618 IF (l_workorder_id IS NULL) THEN
1619 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_WO_NULL');
1620 FND_MSG_PUB.ADD;
1621 RAISE FND_API.G_EXC_ERROR;
1622 END IF;
1623
1624 IF (l_employee_id IS NULL) THEN
1625 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_EMPID_NULL');
1626 FND_MSG_PUB.ADD;
1627 RAISE FND_API.G_EXC_ERROR;
1628 END IF;
1629
1630 -- debug checkpoint.
1631 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1632 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Logout',
1633 'Check Emp and Role:' || l_employee_id || ':' || l_user_role);
1634 END IF;
1635
1636 -- Process WO Logout
1637 Process_WO_Logout (p_employee_id => l_employee_id,
1638 p_workorder_id => l_workorder_id,
1639 p_operation_seq_num => l_operation_seq_num,
1640 p_resource_seq_num => l_resource_seq_num,
1641 p_resource_id => l_resource_id,
1642 p_user_role => l_user_role,
1643 x_return_status => x_return_status);
1644
1645 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1646 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
1647 'After call to Process_WO_Logout: return status' || x_return_status );
1648 END IF;
1649
1650
1651 -- Raise errors if exceptions occur
1652 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1653 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1654 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1655 RAISE FND_API.G_EXC_ERROR;
1656 END IF;
1657
1658 -- Standard check of p_commit
1659 IF FND_API.TO_BOOLEAN(p_commit) THEN
1660 COMMIT WORK;
1661 END IF;
1662
1663 -- Standard call to get message count and if count is 1, get message info
1664 FND_MSG_PUB.Count_And_Get
1665 ( p_count => x_msg_count,
1666 p_data => x_msg_data,
1667 p_encoded => fnd_api.g_false
1668 );
1669
1670 -- log debug message.
1671 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
1672 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout.End',
1673 'Exiting Procedure' );
1674 END IF;
1675
1676 --
1677 EXCEPTION
1678 WHEN FND_API.G_EXC_ERROR THEN
1679 x_return_status := FND_API.G_RET_STS_ERROR;
1680 Rollback to Workorder_Logout_PVT;
1681 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1682 p_data => x_msg_data,
1683 p_encoded => fnd_api.g_false);
1684
1685
1686 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1688 Rollback to Workorder_Logout_PVT;
1689 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1690 p_data => x_msg_data,
1691 p_encoded => fnd_api.g_false);
1692
1693 WHEN OTHERS THEN
1694
1695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1696 Rollback to Workorder_Logout_PVT;
1697 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1698 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1699 p_procedure_name => 'Workorder_Logout',
1700 p_error_text => SUBSTR(SQLERRM,1,240));
1701 END IF;
1702 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1703 p_data => x_msg_data,
1704 p_encoded => fnd_api.g_false);
1705
1706
1707 END Workorder_Logout ;
1708 --------------------------------------------------------------------------------------------
1709 -- Procedure to validate and logout user into a workorder, operation, operation-resource.
1710 PROCEDURE Process_WO_Logout (x_return_status OUT NOCOPY VARCHAR2,
1711 p_employee_id IN NUMBER,
1712 p_workorder_id IN NUMBER,
1713 p_operation_seq_num IN NUMBER,
1714 p_resource_seq_num IN NUMBER,
1715 p_resource_id IN NUMBER,
1716 p_user_role IN VARCHAR2 )
1717
1718 IS
1719
1720
1721 -- get login details.
1722 CURSOR c_emp_login_details(c_employee_id IN NUMBER,
1723 c_workorder_id IN NUMBER) IS
1724 SELECT WLGN.workorder_id,
1725 AW.workorder_name,
1726 AW.wip_entity_id,
1727 WLGN.operation_seq_num,
1728 AOR.resource_id,
1729 WLGN.resource_seq_num,
1730 WLGN.login_level,
1731 WLGN.login_date,
1732 WLGN.work_login_time_id,
1733 WLGN.object_version_number
1734 FROM AHL_Operation_Resources AOR, AHL_WORKORDERS AW,
1735 AHL_WORK_LOGIN_TIMES WLGN
1736 WHERE WLGN.workorder_id(+) = AW.workorder_id
1737 AND WLGN.operation_resource_id = AOR.operation_resource_id(+)
1738 AND WLGN.employee_id(+) = c_employee_id
1739 AND WLGN.login_date(+) IS NOT NULL
1740 AND WLGN.logout_date(+) IS NULL -- employee logged in.
1741 AND AW.workorder_id = c_workorder_id
1742 FOR UPDATE OF WLGN.logout_date NOWAIT;
1743
1744 -- lock workorder.
1745 CURSOR c_lock_wo (p_workorder_id IN NUMBER) IS
1746 SELECT AW.STATUS_CODE, AW.workorder_name
1747 FROM AHL_WORKORDERS AW
1748 WHERE AW.WORKORDER_ID = p_workorder_id
1749 FOR UPDATE OF AW.object_version_number;
1750
1751 -- Lock all operations for a WO.
1752 CURSOR c_lock_wo_all_ops (p_workorder_id IN NUMBER) IS
1753 SELECT AWO.STATUS_CODE
1754 FROM AHL_WORKORDER_OPERATIONS AWO
1755 WHERE AWO.WORKORDER_ID = p_workorder_id
1756 FOR UPDATE OF AWO.object_version_number;
1757
1758 -- Lock specific WO-operation.
1759 CURSOR c_lock_wo_oper (p_workorder_id IN NUMBER,
1760 p_operation_seq_num IN NUMBER) IS
1761 SELECT AWO.STATUS_CODE
1762 FROM AHL_WORKORDER_OPERATIONS AWO
1763 WHERE AWO.WORKORDER_ID = p_workorder_id
1764 AND AWO.operation_sequence_num = p_operation_seq_num
1765 FOR UPDATE OF AWO.object_version_number;
1766
1767 -- Query to get all qualified resources reqd for a WO and employee
1768 -- across all operations.
1769 -- Adithya added organization_id and department_id to fix bug# 6452479
1770 -- Support for borrowed resources
1771 CURSOR c_qualified_req_WO(p_employee_id IN NUMBER,
1772 p_wip_entity_id IN NUMBER) IS
1773 SELECT WOR.operation_seq_num, WOR.resource_id,
1774 WOR.resource_seq_num, BRS.unit_of_measure uom_code,
1775 WO.organization_id, WO.department_id
1776 FROM wip_operations wo,
1777 wip_operation_resources WOR,
1778 bom_resources BRS
1779 WHERE wo.wip_entity_id = WOR.wip_entity_id
1780 AND wo.operation_seq_num = WOR.operation_seq_num
1781 AND WOR.resource_id = BRS.resource_id
1782 AND BRS.resource_type = 2 -- person.
1783 AND wo.wip_entity_id = p_wip_entity_id
1784 AND exists ( SELECT 'x'
1785 FROM bom_resource_employees bre,
1786 bom_dept_res_instances bdri,
1787 bom_department_resources bdr
1788 WHERE --WO.department_id = bdri.department_id
1789 nvl(bdr.share_from_dept_id, WO.department_id) = bdri.department_id
1790 AND bdr.department_id = WO.department_id
1791 AND bdr.resource_id = WOR.RESOURCE_ID
1792 AND bre.resource_id = WOR.RESOURCE_ID
1793 AND bre.instance_id = bdri.instance_id
1794 AND bre.resource_id = bdri.resource_id
1795 AND bre.person_id = p_employee_id);
1796
1797 -- Query to get all qualified resources req for an operation and employee
1798 --Adithya added organization_id and department_id to fix bug# 6452479
1799 -- Support for borrowed resources. Bug# 6748783.
1800 CURSOR c_qualified_req_OP(p_employee_id IN NUMBER,
1801 p_wip_entity_id IN NUMBER,
1802 p_operation_seq_num IN NUMBER) IS
1803 SELECT WOR.operation_seq_num, WOR.resource_id,
1804 WOR.resource_seq_num, BRS.unit_of_measure uom_code,
1805 WO.organization_id, WO.department_id
1806 FROM wip_operations wo,
1807 wip_operation_resources WOR,
1808 bom_resources BRS
1809 WHERE wo.wip_entity_id = WOR.wip_entity_id
1810 AND wo.operation_seq_num = WOR.operation_seq_num
1811 AND WOR.resource_id = BRS.resource_id
1812 AND BRS.resource_type = 2 -- person.
1813 AND wo.wip_entity_id = p_wip_entity_id
1814 AND wo.operation_seq_num = p_operation_seq_num
1815 AND exists ( SELECT 'x'
1816 FROM bom_resource_employees bre,
1817 bom_dept_res_instances bdri,
1818 bom_department_resources bdr
1819 WHERE --WO.department_id = bdri.department_id
1820 nvl(bdr.share_from_dept_id, WO.department_id) = bdri.department_id
1821 AND bdr.department_id = WO.department_id
1822 AND bdr.resource_id = WOR.RESOURCE_ID
1823 AND bre.resource_id = WOR.RESOURCE_ID
1824 AND bre.instance_id = bdri.instance_id
1825 AND bre.resource_id = bdri.resource_id
1826 AND bre.person_id = p_employee_id);
1827
1828 -- query to get qualified resource req. details for a operation-resrc.
1829 --Adithya added organization_id and department_id to fix bug# 6452479
1830 -- Support for borrowed resources. Bug# 6748783.
1831 CURSOR c_qualified_req_RES(p_employee_id IN NUMBER,
1832 p_wip_entity_id IN NUMBER,
1833 p_operation_seq_num IN NUMBER,
1834 p_resource_seq_num IN NUMBER) IS
1835 SELECT WOR.operation_seq_num, WOR.resource_id,
1836 WOR.resource_seq_num, BRS.unit_of_measure uom_code,
1837 WO.organization_id, WO.department_id
1838 FROM wip_operations wo,
1839 wip_operation_resources WOR,
1840 bom_resources BRS
1841 WHERE wo.wip_entity_id = WOR.wip_entity_id
1842 AND wo.operation_seq_num = WOR.operation_seq_num
1843 AND WOR.resource_id = BRS.resource_id
1844 AND BRS.resource_type = 2 -- person.
1845 AND wo.wip_entity_id = p_wip_entity_id
1846 AND wo.operation_seq_num = p_operation_seq_num
1847 AND WOR.resource_seq_num = p_resource_seq_num
1848 AND exists ( SELECT 'x'
1849 FROM bom_resource_employees bre,
1850 bom_dept_res_instances bdri,
1851 bom_department_resources bdr
1852 WHERE --WO.department_id = bdri.department_id
1853 nvl(bdr.share_from_dept_id, WO.department_id) = bdri.department_id
1854 AND bdr.department_id = WO.department_id
1855 AND bdr.resource_id = WOR.RESOURCE_ID
1856 AND bre.resource_id = WOR.RESOURCE_ID
1857 AND bre.instance_id = bdri.instance_id
1858 AND bre.resource_id = bdri.resource_id
1859 AND bre.person_id = p_employee_id);
1860
1861 -- Query to get all person resources reqd for a WO
1862 -- across all operations. (transit tech case).
1863 CURSOR c_person_req_WO(p_wip_entity_id IN NUMBER) IS
1864 SELECT WOR.operation_seq_num, WOR.resource_id,
1865 WOR.resource_seq_num, BRS.unit_of_measure uom_code
1866 FROM wip_operations wo,
1867 wip_operation_resources WOR,
1868 bom_resources BRS
1869 WHERE wo.wip_entity_id = WOR.wip_entity_id
1870 AND wo.operation_seq_num = WOR.operation_seq_num
1871 AND WOR.resource_id = BRS.resource_id
1872 AND BRS.resource_type = 2 -- person.
1873 AND wo.wip_entity_id = p_wip_entity_id;
1874
1875 -- Query to get all person resources req for an operation
1876 CURSOR c_person_req_OP(p_wip_entity_id IN NUMBER,
1877 p_operation_seq_num IN NUMBER) IS
1878 SELECT WOR.operation_seq_num, WOR.resource_id,
1879 WOR.resource_seq_num, BRS.unit_of_measure uom_code
1880 FROM wip_operations wo,
1881 wip_operation_resources WOR,
1882 bom_resources BRS
1883 WHERE wo.wip_entity_id = WOR.wip_entity_id
1884 AND wo.operation_seq_num = WOR.operation_seq_num
1885 AND WOR.resource_id = BRS.resource_id
1886 AND BRS.resource_type = 2 -- person.
1887 AND wo.wip_entity_id = p_wip_entity_id
1888 AND wo.operation_seq_num = p_operation_seq_num;
1889
1890 -- query to get person resource req. details for a operation-resrc.
1891 CURSOR c_person_req_RES(p_wip_entity_id IN NUMBER,
1892 p_operation_seq_num IN NUMBER,
1893 p_resource_seq_num IN NUMBER) IS
1894 SELECT WOR.operation_seq_num, WOR.resource_id,
1895 WOR.resource_seq_num, BRS.unit_of_measure uom_code
1896 FROM wip_operations wo,
1897 wip_operation_resources WOR,
1898 bom_resources BRS
1899 WHERE wo.wip_entity_id = WOR.wip_entity_id
1900 AND wo.operation_seq_num = WOR.operation_seq_num
1901 AND WOR.resource_id = BRS.resource_id
1902 AND BRS.resource_type = 2 -- person.
1903 AND wo.wip_entity_id = p_wip_entity_id
1904 AND wo.operation_seq_num = p_operation_seq_num
1905 AND WOR.resource_seq_num = p_resource_seq_num;
1906
1907 -- get resource seq number given resource id.
1908 CURSOR c_get_resrc_seq(p_wip_entity_id IN NUMBER,
1909 p_operation_seq_num IN NUMBER,
1910 p_resource_id IN NUMBER) IS
1911 SELECT WOR.resource_seq_num
1912 FROM WIP_OPERATION_RESOURCES WOR
1913 WHERE WOR.wip_entity_id = p_wip_entity_id
1914 AND WOR.operation_seq_num = p_operation_seq_num
1915 AND WOR.resource_id = p_resource_id;
1916
1917
1918 l_wip_entity_id NUMBER;
1919 l_login_workorder_id NUMBER;
1920 l_resource_id NUMBER;
1921 l_operation_seq_num NUMBER;
1922 l_resource_seq_num NUMBER;
1923 l_workorder_name ahl_workorders.workorder_name%TYPE;
1924 l_login_date DATE;
1925 l_login_level ahl_work_login_times.login_level%TYPE;
1926 l_work_login_time_id NUMBER;
1927
1928 i NUMBER;
1929 l_prd_res_txn_tbl AHL_PRD_RESOURCE_TRANX_PVT.PRD_RESOURCE_TXNS_TBL;
1930 l_qty NUMBER;
1931
1932 l_return_status VARCHAR2(1);
1933 l_msg_data VARCHAR2(2000);
1934 l_msg_count NUMBER;
1935 l_object_version_number NUMBER;
1936
1937
1938 BEGIN
1939
1940 -- log debug message.
1941 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
1942 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Logout.Start',
1943 'At the Start of procedure AHL_PRD_WO_LOGIN_PVT.Process_WO_Logout');
1944 END IF;
1945
1946 -- Dump of input parameters.
1947 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1948 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Logout.Input_Dump',
1949 'p_employee_id:' || p_employee_id);
1950 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Logout.Input_Dump',
1951 'p_workorder_id:' || p_workorder_id);
1952 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Logout.Input_Dump',
1953 'p_operation_seq_num:' || p_operation_seq_num);
1954 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Logout.Input_Dump',
1955 'p_resource_seq_num:' || p_resource_seq_num);
1956 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Process_WO_Logout.Input_Dump',
1957 'p_resource_id:' || p_resource_id);
1958 END IF;
1959
1960 -- Initialize Procedure return status to success
1961 x_return_status := FND_API.G_RET_STS_SUCCESS;
1962
1963
1964 -- get workorder login details.
1965 OPEN c_emp_login_details (p_employee_id, p_workorder_id);
1966 FETCH c_emp_login_details INTO l_login_workorder_id,
1967 l_workorder_name,
1968 l_wip_entity_id,
1969 l_operation_seq_num,
1970 l_resource_id,
1971 l_resource_seq_num,
1972 l_login_level,
1973 l_login_date,
1974 l_work_login_time_id,
1975 l_object_version_number;
1976
1977 IF (c_emp_login_details%NOTFOUND) THEN
1978 CLOSE c_emp_login_details;
1979 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGNOUT_NOTLOGGEDIN');
1980 FND_MSG_PUB.ADD;
1981 RAISE FND_API.G_EXC_ERROR;
1982 END IF;
1983
1984 -- Check login WO matches input WO.
1985 IF (l_login_workorder_id IS NULL) THEN
1986 -- user is not logged in any workorder.
1987 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGNOUT_NOTLOGGEDIN');
1988 FND_MSG_PUB.ADD;
1989 RAISE FND_API.G_EXC_ERROR;
1990 ELSIF (l_login_workorder_id <> p_workorder_id) THEN
1991 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGNOUT_WO_INVALID');
1992 FND_MESSAGE.set_token('WO_NAME', l_workorder_name);
1993 FND_MSG_PUB.ADD;
1994 RAISE FND_API.G_EXC_ERROR;
1995 END IF;
1996
1997 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
1998 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
1999 'After check for employee login');
2000 END IF;
2001
2002 -- For 'R' and 'O' logins, operation_seq_num is mandatory.
2003 IF (l_login_level <> 'W' AND p_operation_seq_num IS NULL) THEN
2004 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGNOUT_OP_NULL');
2005 FND_MSG_PUB.ADD;
2006 RAISE FND_API.G_EXC_ERROR;
2007 ELSIF (p_operation_seq_num <> l_operation_seq_num) THEN
2008 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGNOUT_OP_INVALID');
2009 FND_MESSAGE.set_token('OP_NUM', p_operation_seq_num);
2010 FND_MSG_PUB.ADD;
2011 RAISE FND_API.G_EXC_ERROR;
2012 END IF;
2013
2014 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2015 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
2016 'After check for operation login');
2017 END IF;
2018
2019 -- For 'R' login, resource is mandatory.
2020 IF (l_login_level = 'R' AND
2021 p_resource_seq_num IS NULL AND p_resource_id IS NULL) THEN
2022 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGNOUT_RES_NULL');
2023 FND_MSG_PUB.ADD;
2024 RAISE FND_API.G_EXC_ERROR;
2025 ELSIF (p_resource_seq_num IS NOT NULL) AND
2026 (p_resource_seq_num <> nvl(l_resource_seq_num, -1) ) THEN
2027 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGNOUT_RES_INVALID');
2028 FND_MESSAGE.set_token('RES_NUM', p_resource_seq_num);
2029 FND_MSG_PUB.ADD;
2030 RAISE FND_API.G_EXC_ERROR;
2031 ELSIF (p_resource_id IS NOT NULL) THEN
2032 IF (p_resource_id <> nvl(l_resource_id,-1)) THEN
2033 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGNOUT_RESID_INVALID');
2034 FND_MESSAGE.set_token('RES_ID', p_resource_id);
2035 FND_MSG_PUB.ADD;
2036 RAISE FND_API.G_EXC_ERROR;
2037 ELSE
2038 -- get resource seq num.
2039 OPEN c_get_resrc_seq(l_wip_entity_id, l_operation_seq_num, l_resource_id);
2040 FETCH c_get_resrc_seq INTO l_resource_seq_num;
2041 IF (c_get_resrc_seq%NOTFOUND) THEN
2042 CLOSE c_get_resrc_seq;
2043 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGNOUT_RES_INVALID');
2044 FND_MESSAGE.set_token('RES_ID', p_resource_id);
2045 FND_MSG_PUB.ADD;
2046 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2047 END IF; -- c_get_resrc_seq
2048 CLOSE c_get_resrc_seq;
2049 END IF; -- p_resource_id <> ..
2050 END IF; -- l_login_level = 'R' ..
2051
2052 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2053 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
2054 'After check for resource login');
2055 END IF;
2056
2057 -- Check Unit locked.
2058 IF AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => p_workorder_id,
2059 p_ue_id => null,
2060 p_visit_id => null,
2061 p_item_instance_id => null) = FND_API.g_true THEN
2062 -- Unit is locked, therefore cannot perform resource transactions
2063 -- and cannot login to the workorder
2064 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_UNTLCKD');
2065 FND_MESSAGE.set_token('WO_NUM' , l_workorder_name);
2066 FND_MSG_PUB.ADD;
2067 RAISE FND_API.G_EXC_ERROR;
2068 END IF;
2069
2070 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2071 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
2072 'After Unit locked check');
2073 END IF;
2074
2075 -- Process for posting resource txns.
2076
2077 -- initialize variables.
2078 i := 0;
2079
2080 -- Read user qualified resource requirements to record automatic resource txns.
2081 -- Operation-resource login case.
2082 IF (l_login_level = 'R') THEN
2083
2084 -- debug check point.
2085 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2086 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
2087 'processing resource txns for RES login:Emp:WE:OP:RES' || p_employee_id || ':'|| l_wip_entity_id || ':' || l_operation_seq_num || ':' || l_resource_seq_num);
2088 END IF;
2089
2090 -- Commented out user_role condn to fix bug 5015149.
2091 --IF (p_user_role = ahl_prd_util_pkg.G_TECH_MYWO) THEN
2092 FOR qual_res_rec IN c_qualified_req_RES(p_employee_id, l_wip_entity_id,
2093 l_operation_seq_num, l_resource_seq_num)
2094 LOOP
2095 i := i + 1;
2096
2097 l_prd_res_txn_tbl(i).workorder_id := p_workorder_id;
2098 l_prd_res_txn_tbl(i).operation_sequence_num := l_operation_seq_num;
2099 l_prd_res_txn_tbl(i).resource_sequence_num := l_resource_seq_num;
2100 l_prd_res_txn_tbl(i).person_id := p_employee_id;
2101 l_prd_res_txn_tbl(i).qty := 0;
2102 l_prd_res_txn_tbl(i).uom_code := qual_res_rec.uom_code;
2103 l_prd_res_txn_tbl(i).resource_id := qual_res_rec.resource_id;
2104 --l_prd_res_txn_tbl(i).transaction_date := sysdate;
2105 l_prd_res_txn_tbl(i).end_date := sysdate;
2106 l_prd_res_txn_tbl(i).DML_operation := 'C';
2107 --Adithya added to fix bug# 6452479
2108 l_prd_res_txn_tbl(i).department_id := qual_res_rec.department_id;
2109 l_prd_res_txn_tbl(i).organization_id := qual_res_rec.organization_id;
2110
2111 END LOOP; -- qual_res_rec
2112
2113 /* Commented out to fix bug 5015149.
2114 ELSE -- transit tech: apply no qualification.
2115 FOR person_res_rec IN c_person_req_RES(l_wip_entity_id,
2116 l_operation_seq_num, l_resource_seq_num)
2117 LOOP
2118 i := i + 1;
2119
2120 l_prd_res_txn_tbl(i).workorder_id := p_workorder_id;
2121 l_prd_res_txn_tbl(i).operation_sequence_num := l_operation_seq_num;
2122 l_prd_res_txn_tbl(i).resource_sequence_num := l_resource_seq_num;
2123 l_prd_res_txn_tbl(i).person_id := p_employee_id;
2124 l_prd_res_txn_tbl(i).qty := 0;
2125 l_prd_res_txn_tbl(i).uom_code := person_res_rec.uom_code;
2126 l_prd_res_txn_tbl(i).resource_id := person_res_rec.resource_id;
2127 l_prd_res_txn_tbl(i).transaction_date := sysdate;
2128 l_prd_res_txn_tbl(i).DML_operation := 'C';
2129
2130 END LOOP; -- person_res_rec
2131
2132 END IF; -- l_user_role. */
2133
2134 END IF; -- l_login_level = 'R'
2135
2136 -- Operation login case.
2137 IF (l_login_level = 'O') THEN
2138
2139 -- debug check point.
2140 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2141 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
2142 'processing resource txns for OP login:Emp:WE:OP:' || p_employee_id || ':'|| l_wip_entity_id || ':' || l_operation_seq_num);
2143 END IF;
2144
2145 -- Commented out user_role condn to fix bug 5015149.
2146 --IF (p_user_role = ahl_prd_util_pkg.G_TECH_MYWO) THEN
2147 FOR qual_res_rec IN c_qualified_req_OP(p_employee_id, l_wip_entity_id,
2148 l_operation_seq_num)
2149 LOOP
2150 i := i + 1;
2151
2152 l_prd_res_txn_tbl(i).workorder_id := p_workorder_id;
2153 l_prd_res_txn_tbl(i).operation_sequence_num := l_operation_seq_num;
2154 l_prd_res_txn_tbl(i).resource_sequence_num := qual_res_rec.resource_seq_num;
2155 l_prd_res_txn_tbl(i).person_id := p_employee_id;
2156 l_prd_res_txn_tbl(i).qty := 0;
2157 l_prd_res_txn_tbl(i).uom_code := qual_res_rec.uom_code;
2158 l_prd_res_txn_tbl(i).resource_id := qual_res_rec.resource_id;
2159 --l_prd_res_txn_tbl(i).transaction_date := sysdate;
2160 l_prd_res_txn_tbl(i).end_date := sysdate;
2161 l_prd_res_txn_tbl(i).DML_operation := 'C';
2162 --Adithya added to fix bug# 6452479
2163 l_prd_res_txn_tbl(i).department_id := qual_res_rec.department_id;
2164 l_prd_res_txn_tbl(i).organization_id := qual_res_rec.organization_id;
2165
2166 END LOOP; -- qual_res_rec
2167
2168 /* commented out to fix bug 5015149.
2169 ELSE -- apply no qual for transit tech.
2170 FOR person_res_rec IN c_person_req_OP(l_wip_entity_id, l_operation_seq_num)
2171 LOOP
2172 i := i + 1;
2173
2174 l_prd_res_txn_tbl(i).workorder_id := p_workorder_id;
2175 l_prd_res_txn_tbl(i).operation_sequence_num := l_operation_seq_num;
2176 l_prd_res_txn_tbl(i).resource_sequence_num := person_res_rec.resource_seq_num;
2177 l_prd_res_txn_tbl(i).person_id := p_employee_id;
2178 l_prd_res_txn_tbl(i).qty := 0;
2179 l_prd_res_txn_tbl(i).uom_code := person_res_rec.uom_code;
2180 l_prd_res_txn_tbl(i).resource_id := person_res_rec.resource_id;
2181 l_prd_res_txn_tbl(i).transaction_date := sysdate;
2182 l_prd_res_txn_tbl(i).DML_operation := 'C';
2183
2184 END LOOP; -- person_res_rec
2185
2186 END IF; -- l_user_role. */
2187
2188 END IF; -- l_login_level = 'O'
2189
2190 -- Workorder login case.
2191 IF (l_login_level = 'W') THEN
2192
2193 -- debug check point.
2194 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2195 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
2196 'processing resource txns for WO login:Emp:WE:' || p_employee_id || ':'|| l_wip_entity_id );
2197 END IF;
2198
2199 -- Commented out user_role condn to fix bug 5015149.
2200 --IF (p_user_role = ahl_prd_util_pkg.G_TECH_MYWO) THEN
2201 FOR qual_res_rec IN c_qualified_req_WO(p_employee_id, l_wip_entity_id)
2202 LOOP
2203 i := i + 1;
2204
2205 l_prd_res_txn_tbl(i).workorder_id := p_workorder_id;
2206 l_prd_res_txn_tbl(i).operation_sequence_num := qual_res_rec.operation_seq_num;
2207 l_prd_res_txn_tbl(i).resource_sequence_num := qual_res_rec.resource_seq_num;
2208 l_prd_res_txn_tbl(i).person_id := p_employee_id;
2209 l_prd_res_txn_tbl(i).qty := 0;
2210 l_prd_res_txn_tbl(i).uom_code := qual_res_rec.uom_code;
2211 l_prd_res_txn_tbl(i).resource_id := qual_res_rec.resource_id;
2212 --l_prd_res_txn_tbl(i).transaction_date := sysdate;
2213 l_prd_res_txn_tbl(i).end_date := sysdate;
2214 l_prd_res_txn_tbl(i).DML_operation := 'C';
2215 --Adithya added to fix bug# 6452479
2216 l_prd_res_txn_tbl(i).department_id := qual_res_rec.department_id;
2217 l_prd_res_txn_tbl(i).organization_id := qual_res_rec.organization_id;
2218
2219 END LOOP; -- qual_res_rec
2220
2221 /* commented out to fix bug 5015149.
2222 ELSE -- apply no qualification to transit tech.
2223
2224 FOR person_res_rec IN c_person_req_WO(l_wip_entity_id)
2225 LOOP
2226 i := i + 1;
2227
2228 l_prd_res_txn_tbl(i).workorder_id := p_workorder_id;
2229 l_prd_res_txn_tbl(i).operation_sequence_num := person_res_rec.operation_seq_num;
2230 l_prd_res_txn_tbl(i).resource_sequence_num := person_res_rec.resource_seq_num;
2231 l_prd_res_txn_tbl(i).person_id := p_employee_id;
2232 l_prd_res_txn_tbl(i).qty := 0;
2233 l_prd_res_txn_tbl(i).uom_code := person_res_rec.uom_code;
2234 l_prd_res_txn_tbl(i).resource_id := person_res_rec.resource_id;
2235 l_prd_res_txn_tbl(i).transaction_date := sysdate;
2236 l_prd_res_txn_tbl(i).DML_operation := 'C';
2237
2238 END LOOP; -- person_res_rec
2239
2240 END IF; -- l_user_role. */
2241
2242 END IF; -- l_login_level = 'W'
2243
2244 -- debug check point.
2245 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2246 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
2247 'Count on resource txns table:' || l_prd_res_txn_tbl.count);
2248 END IF;
2249
2250 -- Call resource txns api.
2251 IF (l_prd_res_txn_tbl.COUNT > 0) THEN
2252 -- Post resource time equally accross all qualifications.
2253 l_qty := ROUND(((sysdate - l_login_date) * 24) / l_prd_res_txn_tbl.COUNT, 3);
2254
2255 -- debug check point.
2256 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2257 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
2258 'Hours per resource:' || l_qty);
2259 END IF;
2260
2261 FOR i IN l_prd_res_txn_tbl.FIRST..l_prd_res_txn_tbl.LAST LOOP
2262 l_prd_res_txn_tbl(i).qty := l_qty;
2263
2264 -- debug check point.
2265 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2266 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
2267 'Hours per resource:' || l_qty);
2268 END IF;
2269
2270 END LOOP;
2271
2272 AHL_PRD_RESOURCE_TRANX_PVT.Process_Resource_Txns(p_api_version => 1.0,
2273 p_init_msg_list => FND_API.G_TRUE,
2274 p_commit => FND_API.G_FALSE,
2275 x_return_status => l_return_status,
2276 x_msg_count => l_msg_count,
2277 x_msg_data => l_msg_data,
2278 p_x_prd_resrc_txn_tbl => l_prd_res_txn_tbl);
2279
2280 -- debug check point.
2281 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2282 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Workorder_Logout',
2283 'After call to resource txns: return status:' || l_return_status);
2284 END IF;
2285
2286 -- Raise errors if exceptions occur
2287 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2289 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2290 RAISE FND_API.G_EXC_ERROR;
2291 END IF;
2292
2293 END IF; -- l_prd_res_txn_tbl.COUNT > 0
2294
2295 -- update logout time.
2296 UPDATE ahl_work_login_times
2297 SET logout_date = sysdate,
2298 object_version_number = l_object_version_number + 1
2299 WHERE work_login_time_id = l_work_login_time_id;
2300
2301
2302 END Process_WO_Logout;
2303
2304
2305
2306
2307 -- Start of Comments --
2308 -- Function name : Get_User_Role
2309 --
2310 -- Parameters :
2311 -- p_fnd_function_name Input FND function name.
2312 --
2313 -- Description : This function is used to retrieve the role associated with the current
2314 -- user - it could be a Production Tech, Production Data Clerk or
2315 -- Production Transit Tech.
2316 --
2317
2318 FUNCTION Get_User_Role
2319 RETURN VARCHAR2
2320
2321 IS
2322 l_user_role VARCHAR2(30);
2323
2324 BEGIN
2325
2326 -- log debug message.
2327 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
2328 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Get_User_Role.Start',
2329 'At Start of procedure AHL_PRD_WO_LOGIN_PVT.Get_User_Role');
2330 END IF;
2331
2332 IF (FND_FUNCTION.TEST(AHL_PRD_UTIL_PKG.G_TECH_MYWO)) THEN
2333 -- Technician Role.
2334 l_user_role := AHL_PRD_UTIL_PKG.G_TECH_MYWO;
2335 ELSIF (FND_FUNCTION.TEST(AHL_PRD_UTIL_PKG.G_DATA_CLERK)) THEN
2336 -- Data Clerk Role.
2337 l_user_role := AHL_PRD_UTIL_PKG.G_DATA_CLERK;
2338 ELSIF (FND_FUNCTION.TEST(AHL_PRD_UTIL_PKG.G_LINE_TECH)) THEN
2339 -- Transit Check Role.
2340 l_user_role := AHL_PRD_UTIL_PKG.G_LINE_TECH;
2341 END IF;
2342
2343 -- log debug message.
2344 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
2345 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Get_User_Role.End',
2346 'At End of procedure AHL_PRD_WO_LOGIN_PVT.Get_User_Role');
2347 END IF;
2348
2349 RETURN l_user_role;
2350
2351 EXCEPTION
2352 WHEN OTHERS THEN
2353 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2354 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2355 p_procedure_name => 'Get_User_Role',
2356 p_error_text => SUBSTR(SQLERRM,1,240));
2357 END IF;
2358 RETURN NULL;
2359
2360
2361 END Get_User_Role;
2362
2363 --------------------------------------------------------------------------------------------
2364
2365 -- Start of Comments --
2366 -- Function name : Get_Employee_ID
2367 --
2368 -- Parameters :
2369 -- p_employee_number Input Employee Number.
2370 --
2371 -- Description : This function is used to retrieve the employee ID given an employee number.
2372 -- If employee number is not passed in, then the logged in user's employee ID
2373 -- is returned. This function is a helper function for other APIs.
2374 --
2375 --
2376
2377 FUNCTION Get_Employee_ID (p_Employee_Number IN VARCHAR2 := NULL)
2378 RETURN VARCHAR2
2379
2380 IS
2381
2382 -- To get the current logged in user's employee ID.
2383 CURSOR c_get_current_employee_id (p_user_id IN NUMBER) IS
2384 SELECT employee_id
2385 FROM FND_USER
2386 WHERE USER_ID = p_user_id;
2387
2388 -- To get the employee ID based on an employee number.
2389 CURSOR c_get_employee_id (p_employee_number IN VARCHAR2) IS
2390 SELECT employee_id
2391 FROM MTL_EMPLOYEES_CURRENT_VIEW
2392 WHERE employee_num = p_employee_number
2393 AND rownum < 2;
2394
2395 l_employee_ID NUMBER;
2396
2397 BEGIN
2398 -- Check for NULL value.
2399 IF (p_employee_number IS NULL) THEN
2400 OPEN c_get_current_employee_id(FND_GLOBAL.USER_ID);
2401 FETCH c_get_current_employee_id INTO l_employee_id;
2402 IF (c_get_current_employee_id%NOTFOUND) THEN
2403 l_employee_id := NULL;
2404 END IF;
2405 CLOSE c_get_current_employee_id;
2406 ELSE
2407 -- read employee table to get the ID.
2408 OPEN c_get_employee_id (p_employee_number);
2409 FETCH c_get_employee_id INTO l_employee_id;
2410 IF (c_get_employee_id%NOTFOUND) THEN
2411 l_employee_id := NULL;
2412 END IF;
2413 CLOSE c_get_employee_id;
2414 END IF;
2415
2416 RETURN l_employee_id;
2417
2418 EXCEPTION
2419 WHEN OTHERS THEN
2420 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2421 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2422 p_procedure_name => 'Get_Employee_ID',
2423 p_error_text => SUBSTR(SQLERRM,1,240));
2424 END IF;
2425 RETURN NULL;
2426
2427
2428 END Get_Employee_ID;
2429 --------------------------------------------------------------------------------------------
2430
2431 -- Start of Comments --
2432 -- Procedure name : Get_Current_Emp_Login
2433 --
2434 -- Parameters :
2435 -- p_employee_id - Optional Input Employee Id.
2436 -- x_return_status -- Procedure return status.
2437 -- x_workorder_id -- Workorder ID employee is logged into.
2438 -- only valid id Employee logged into workorder.
2439 -- x_workorder_number -- Workorder Name.
2440 -- x_operation_seq_num -- Operation Seq Number
2441 -- -- Only valid if Employee logged into an Operation-Resource.
2442 -- x_resource_id -- Resource sequence employee is logged into.
2443 -- x_resource_seq_num -- Resource Sequence number.
2444 --
2445 -- Description : This procedure returns the workorder or operation the input employee ID
2446 -- is currently logged into. If input employee ID is null, then the values are
2447 -- retrieved for the currently logged in employee.
2448 --
2449
2450
2451 PROCEDURE Get_Current_Emp_Login (x_return_status OUT NOCOPY VARCHAR2,
2452 x_msg_data OUT NOCOPY VARCHAR2,
2453 x_msg_count OUT NOCOPY NUMBER,
2454 p_employee_id IN NUMBER := NULL,
2455 x_employee_name OUT NOCOPY VARCHAR2,
2456 x_workorder_id OUT NOCOPY NUMBER,
2457 x_workorder_number OUT NOCOPY VARCHAR2,
2458 x_operation_seq_num OUT NOCOPY NUMBER,
2459 x_resource_id OUT NOCOPY NUMBER,
2460 x_resource_seq_num OUT NOCOPY NUMBER)
2461
2462 IS
2463
2464 -- Cursor to check current assignments.
2465 CURSOR c_emp_login_details(c_employee_id IN NUMBER) IS
2466 SELECT WLGN.workorder_id,
2467 AW.workorder_name,
2468 WLGN.operation_seq_num,
2469 AOR.resource_id,
2470 WLGN.resource_seq_num
2471 FROM AHL_Operation_Resources AOR, AHL_WORKORDERS AW,
2472 AHL_WORK_LOGIN_TIMES WLGN
2473 WHERE WLGN.workorder_id = AW.workorder_id
2474 AND WLGN.operation_resource_id = AOR.operation_resource_id(+)
2475 AND WLGN.employee_id = c_employee_id
2476 AND WLGN.logout_date IS NULL; -- employee logged in.
2477
2478 -- To get employee name.
2479 CURSOR get_emp_name (p_employee_id IN NUMBER) IS
2480 SELECT full_name
2481 FROM MTL_EMPLOYEES_CURRENT_VIEW
2482 WHERE employee_id = p_employee_id
2483 AND rownum < 2;
2484
2485
2486 l_employee_id NUMBER;
2487 l_workorder_id NUMBER;
2488 l_operation_seq_num NUMBER;
2489 l_resource_id NUMBER;
2490 l_count NUMBER;
2491
2492 l_resource_seq_num NUMBER;
2493 l_workorder_number ahl_workorders.workorder_name%TYPE;
2494
2495 BEGIN
2496
2497 -- log debug message.
2498 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
2499 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Get_Current_Emp_Login.Start',
2500 'At Start of procedure AHL_PRD_WO_LOGIN_PVT.Get_Current_Emp_Login');
2501 END IF;
2502
2503 -- Dump Input parameters.
2504 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2505 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Get_Current_Emp_Login.Dump',
2506 'p_employee_id: ' || p_employee_id );
2507
2508 END IF;
2509
2510 -- Initialize Procedure return status to success
2511 x_return_status := FND_API.G_RET_STS_SUCCESS;
2512
2513 -- Check input parameter for NULL.
2514 IF (p_employee_id IS NULL) THEN
2515 l_employee_id := Get_Employee_ID();
2516 ELSE
2517 l_employee_id := p_employee_id;
2518 END IF;
2519
2520 -- Return error if employee id not found.
2521 IF (l_employee_id IS NULL) THEN
2522 FND_MESSAGE.Set_Name('AHL','AHL_PRD_LGN_EMP_NULL');
2523 FND_MSG_PUB.ADD;
2524 RAISE FND_API.G_EXC_ERROR;
2525 END IF;
2526
2527 -- Get login info.
2528 OPEN c_emp_login_details(l_employee_id);
2529
2530 FETCH c_emp_login_details INTO x_workorder_id, x_workorder_number,
2531 x_operation_seq_num, x_resource_id,
2532 x_resource_seq_num;
2533 IF (c_emp_login_details%NOTFOUND) THEN
2534 -- employee not logged in.
2535 x_workorder_id := NULL;
2536 x_workorder_number := NULL;
2537 x_operation_seq_num := NULL;
2538 x_resource_id := NULL;
2539 x_resource_seq_num := NULL;
2540 END IF; -- c_emp_login_details%NOTFOUND
2541
2542 CLOSE c_emp_login_details;
2543
2544 -- get employee name.
2545 OPEN get_emp_name(l_employee_id);
2546 FETCH get_emp_name INTO x_employee_name;
2547 CLOSE get_emp_name;
2548
2549 -- Dump output parameters.
2550 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2551 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Get_Current_Emp_Login.Output_Dump',
2552 'x_workorder_id:' || x_workorder_id);
2553 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Get_Current_Emp_Login.Output_Dump',
2554 'x_workorder_number:' || x_workorder_number);
2555 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Get_Current_Emp_Login.Output_Dump',
2556 'x_operation_seq_num:' || x_operation_seq_num);
2557 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Get_Current_Emp_Login.Output_Dump',
2558 'x_resource_id:' || x_resource_id);
2559 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Get_Current_Emp_Login.Output_Dump',
2560 'x_resource_seq_num:' || x_resource_seq_num);
2561 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Get_Current_Emp_Login.Output_Dump',
2562 'x_employee_name:' || x_employee_name);
2563 END IF;
2564
2565 -- log debug message.
2566 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
2567 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Get_Current_Emp_Login.End',
2568 'At End of procedure AHL_PRD_WO_LOGIN_PVT.Get_Current_Emp_Login');
2569 END IF;
2570
2571 EXCEPTION
2572 WHEN FND_API.G_EXC_ERROR THEN
2573 x_return_status := FND_API.G_RET_STS_ERROR;
2574 FND_MSG_PUB.count_and_get
2575 (
2576 p_count => x_msg_count,
2577 p_data => x_msg_data,
2578 p_encoded => fnd_api.g_false
2579 );
2580
2581
2582 WHEN OTHERS THEN
2583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2584 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2585 fnd_msg_pub.add_exc_msg
2586 (
2587 p_pkg_name => G_PKG_NAME,
2588 p_procedure_name => 'Get_Current_Emp_Login',
2589 p_error_text => SUBSTR(SQLERRM,1,240)
2590 );
2591 END IF;
2592 FND_MSG_PUB.count_and_get
2593 (
2594 p_count => x_msg_count,
2595 p_data => x_msg_data,
2596 p_encoded => fnd_api.g_false
2597 );
2598
2599 END Get_Current_Emp_Login;
2600
2601 --------------------------------------------------------------------------------------------
2602
2603
2604 -- Start of Comments --
2605 -- Function name : Is_Login_Allowed
2606 --
2607 -- Parameters :
2608 -- p_employee_id -- Optional Input Employee Id.
2609 -- p_wip_entity_id -- Mandatory Workorder ID.
2610 -- p_operation_seq_num -- Mandatory Operation Seq Number
2611 -- p_resource_seq_num -- Mandatory Resource ID.
2612 -- p_fnd_function_name -- Mandatory fnd_function to identify user role.
2613 --
2614 -- Description : This function is used to determine if a given technician is
2615 -- allowed to login to a particular workorder/operation/oper-resource.
2616 -- User is allowed to login only if value of the profile
2617 -- 'Technician Role: Enable Manual Resource Transactions Mode'
2618 -- is set to Yes. Login is not allowed for Data Clerk role.
2619 --
2620 -- This function returns fnd_api.g_false if login is not allowed
2621 -- and return fnd_api.g_true if login is allowed. Error
2622 -- messages are added to the message stack.
2623 --
2624 --
2625
2626 FUNCTION Is_Login_Allowed(p_employee_id IN NUMBER := NULL,
2627 p_workorder_id IN NUMBER,
2628 p_operation_seq_num IN NUMBER := NULL,
2629 p_resource_seq_num IN NUMBER := NULL,
2630 p_resource_id IN NUMBER := NULL,
2631 p_fnd_function_name IN VARCHAR2)
2632 RETURN VARCHAR2 IS
2633
2634 -- get WO status.
2635 CURSOR c_is_wo_valid(p_workorder_id IN NUMBER) IS
2636 SELECT wo.STATUS_CODE, wo.workorder_name, wo.wip_entity_id, we.organization_id
2637 FROM AHL_WORKORDERS WO, WIP_ENTITIES WE
2638 WHERE WO.wip_entity_id = we.wip_entity_id
2639 AND WORKORDER_ID = p_workorder_id;
2640
2641 -- validate employee.
2642 CURSOR c_is_emp_valid (p_employee_id IN NUMBER,
2643 p_org_id IN NUMBER) IS
2644 SELECT pf.employee_id
2645 FROM mtl_employees_current_view pf
2646 WHERE pf.employee_id = p_employee_id
2647 AND pf.organization_id = p_org_id;
2648
2649 /*
2650 -- Query to check if for a WO and its operations, any requirements exist with no qualification for given employee.
2651 CURSOR c_unqualified_for_all_ops(p_employee_id IN NUMBER,
2652 p_wip_entity_id IN NUMBER) IS
2653 SELECT 'x'
2654 FROM wip_operations wo
2655 WHERE wo.wip_entity_id = p_wip_entity_id
2656 AND not exists ( SELECT 'x'
2657 FROM wip_operation_resources WOR,
2658 bom_resources BRS,
2659 bom_resource_employees bre,
2660 bom_dept_res_instances bdri
2661 WHERE wo.operation_seq_num = WOR.operation_seq_num
2662 AND WOR.resource_id = BRS.resource_id
2663 AND BRS.resource_type = 2 -- person.
2664 AND WO.department_id = bdri.department_id
2665 AND WOR.RESOURCE_ID= bdri.resource_id
2666 AND bre.instance_id = bdri.instance_id
2667 AND bre.resource_id = bdri.resource_id
2668 AND bre.person_id = p_employee_id);
2669
2670 */
2671 -- Query to check if all operations of WO have at least one person resource requirement.
2672 CURSOR c_check_res_reqd (p_wip_entity_id IN NUMBER) IS
2673 SELECT 'x'
2674 FROM WIP_OPERATIONS WO
2675 WHERE wip_entity_id = p_wip_entity_id
2676 AND exists ( SELECT 'x'
2677 FROM WIP_OPERATION_RESOURCES WOR, BOM_RESOURCES BRS
2678 WHERE WOR.wip_entity_id = WO.wip_entity_id
2679 AND WOR.operation_seq_num = WO.operation_seq_num
2680 AND WOR.resource_id = BRS.resource_id
2681 AND BRS.resource_type = 2 -- person.
2682 );
2683
2684 /*
2685 -- Query to check if employee qualifies for multiple resource requirements within an operation at a WO level.
2686 CURSOR c_check_res_multiple_wo(p_employee_id IN NUMBER,
2687 p_wip_entity_id IN NUMBER) IS
2688 SELECT 'x'
2689 FROM wip_operations wo,
2690 wip_operation_resources WOR,
2691 bom_resources BRS
2692 WHERE wo.wip_entity_id = WOR.wip_entity_id
2693 AND wo.operation_seq_num = WOR.operation_seq_num
2694 AND WOR.resource_id = BRS.resource_id
2695 AND BRS.resource_type = 2 -- person.
2696 AND wo.wip_entity_id = p_wip_entity_id
2697 AND exists ( SELECT 'x'
2698 FROM bom_resource_employees bre,
2699 bom_dept_res_instances bdri
2700 WHERE WO.department_id = bdri.department_id
2701 AND WOR.RESOURCE_ID= bdri.resource_id
2702 AND bre.instance_id = bdri.instance_id
2703 AND bre.resource_id = bdri.resource_id
2704 AND bre.person_id = p_employee_id)
2705 GROUP BY WO.wip_entity_id, WO.Operation_seq_num
2706 HAVING count(WOR.resource_seq_num) > 1;
2707
2708 -- Query to check if employee qualifies for multiple resource requirements within an operation.
2709 CURSOR c_check_res_multiple_op(p_employee_id IN NUMBER,
2710 p_wip_entity_id IN NUMBER,
2711 p_operation_seq_num IN NUMBER) IS
2712 SELECT 'x'
2713 FROM wip_operations wo,
2714 wip_operation_resources WOR,
2715 bom_resources BRS
2716 WHERE wo.wip_entity_id = WOR.wip_entity_id
2717 AND wo.operation_seq_num = WOR.operation_seq_num
2718 AND WOR.resource_id = BRS.resource_id
2719 AND BRS.resource_type = 2 -- person.
2720 AND wo.wip_entity_id = p_wip_entity_id
2721 AND wo.operation_seq_num = p_operation_seq_num
2722 AND exists ( SELECT 'x'
2723 FROM bom_resource_employees bre,
2724 bom_dept_res_instances bdri
2725 WHERE WO.department_id = bdri.department_id
2726 AND WOR.RESOURCE_ID= bdri.resource_id
2727 AND bre.instance_id = bdri.instance_id
2728 AND bre.resource_id = bdri.resource_id
2729 AND bre.person_id = p_employee_id)
2730 GROUP BY WO.wip_entity_id, WO.Operation_seq_num
2731 HAVING count(WOR.resource_seq_num) > 1;
2732
2733 */
2734
2735 -- cursor to validate operation seq.
2736 -- At least one person resource should exist.
2737 CURSOR c_is_op_seq_valid(p_workorder_id IN NUMBER,
2738 p_op_seq_num IN NUMBER,
2739 p_wip_entity_id IN NUMBER) IS
2740 SELECT 'x' -- status_code
2741 FROM AHL_WORKORDER_OPERATIONS
2742 WHERE WORKORDER_ID = p_workorder_id
2743 AND OPERATION_SEQUENCE_NUM = p_op_seq_num
2744 AND status_code = 2 -- uncomplete
2745 AND exists ( SELECT 'x'
2746 FROM wip_operation_resources WOR, bom_resources BRS
2747 WHERE WOR.wip_entity_id = p_wip_entity_id
2748 AND WOR.operation_seq_num = p_op_seq_num
2749 AND WOR.resource_id = BRS.resource_id
2750 AND BRS.resource_type = 2 -- person.
2751 );
2752
2753 -- Query to check if for an operation, given employee is qualified for at least one resource reqd.
2754 -- Fix for bug# 6748783. Support for borrowed resources.
2755 CURSOR c_qualified_for_one_res(p_employee_id IN NUMBER,
2756 p_wip_entity_id IN NUMBER,
2757 p_operation_seq_num IN NUMBER) IS
2758 SELECT 'x'
2759 FROM wip_operations wo
2760 WHERE wo.wip_entity_id = p_wip_entity_id
2761 and wo.operation_seq_num = p_operation_seq_num
2762 AND exists ( SELECT 'x'
2763 FROM wip_operation_resources WOR,
2764 bom_resources BRS,
2765 bom_resource_employees bre,
2766 bom_dept_res_instances bdri,
2767 bom_department_resources bdr
2768 WHERE WOR.operation_seq_num = wo.operation_seq_num
2769 AND WOR.wip_entity_id = wo.wip_entity_id
2770 AND WOR.resource_id = BRS.resource_id
2771 AND wor.organization_id = brs.organization_id
2772 AND BRS.resource_type = 2 -- person.
2773 AND brs.resource_id = bre.resource_id
2774 AND brs.organization_id = bre.organization_id
2775 --AND WO.department_id = bdri.department_id
2776 AND nvl(bdr.share_from_dept_id, WO.department_id) = bdri.department_id
2777 AND bdr.department_id = WO.department_id
2778 AND bdr.resource_id = WOR.RESOURCE_ID
2779 AND WOR.RESOURCE_ID= bdri.resource_id
2780 AND bre.instance_id = bdri.instance_id
2781 AND bre.resource_id = bdri.resource_id
2782 AND bre.person_id = p_employee_id);
2783
2784 -- Query to check if employee does not qualify for a operation-resource req.
2785 -- Support for borrowed resources. Fix for bug# 6748783
2786 CURSOR c_qualify_res(p_employee_id IN NUMBER,
2787 p_wip_entity_id IN NUMBER,
2788 p_operation_seq_num IN NUMBER,
2789 p_resource_id IN NUMBER) IS
2790
2791 SELECT 'x'
2792 FROM wip_operations wo,
2793 wip_operation_resources WOR
2794 WHERE wo.wip_entity_id = WOR.wip_entity_id
2795 AND wo.operation_seq_num = WOR.operation_seq_num
2796 AND wo.wip_entity_id = p_wip_entity_id
2797 AND WOR.operation_seq_num = p_operation_seq_num
2798 AND WOR.resource_id = p_resource_id
2799 AND not exists ( SELECT 'x'
2800 FROM bom_resource_employees bre,
2801 bom_dept_res_instances bdri,
2802 bom_department_resources bdr
2803 WHERE bre.resource_id = wor.resource_id
2804 AND bre.organization_id = wor.organization_id
2805 --AND WO.department_id = bdri.department_id
2806 AND nvl(bdr.share_from_dept_id, WO.department_id) = bdri.department_id
2807 AND bdr.department_id = WO.department_id
2808 AND bdr.resource_id = WOR.resource_id
2809 AND WOR.RESOURCE_ID= bdri.resource_id
2810 AND bre.instance_id = bdri.instance_id
2811 AND bre.resource_id = bdri.resource_id
2812 AND bre.person_id = p_employee_id);
2813
2814 -- query to get resource ID id given resource seq num input.
2815 CURSOR c_get_resrc_id(p_wip_entity_id IN NUMBER,
2816 p_operation_seq_num IN NUMBER,
2817 p_resource_seq_num IN NUMBER) IS
2818 SELECT resource_id
2819 FROM WIP_OPERATION_RESOURCES WOR
2820 WHERE WOR.wip_entity_id = p_wip_entity_id
2821 AND WOR.operation_seq_num = p_operation_seq_num
2822 AND WOR.resource_seq_num = p_resource_seq_num;
2823
2824
2825 -- query to validate resource_id. Resource must be of type 'person'.
2826 CURSOR c_is_resource_valid(p_resource_id IN NUMBER,
2827 p_org_id IN NUMBER) IS
2828 SELECT resource_code
2829 FROM BOM_RESOURCES
2830 WHERE RESOURCE_ID = p_resource_id
2831 AND organization_id = p_org_id
2832 AND resource_type = 2;
2833
2834 -- query to check if all operations of a WO are uncomplete.
2835 CURSOR c_workorder_oper(p_workorder_id IN NUMBER) IS
2836 SELECT 'x'
2837 FROM AHL_WORKORDER_OPERATIONS
2838 WHERE workorder_id = p_workorder_id
2839 AND STATUS_CODE = '1' -- complete.
2840 AND rownum < 2;
2841
2842 -- query to get all operations for a workorder.
2843 CURSOR c_get_workorder_oper (p_wip_entity_id IN NUMBER) IS
2844 SELECT operation_seq_num
2845 FROM WIP_OPERATIONS
2846 WHERE wip_entity_id = p_wip_entity_id;
2847
2848 l_employee_id NUMBER;
2849 l_wo_status AHL_WORKORDERS.status_code%TYPE;
2850 l_wo_name AHL_WORKORDERS.workorder_name%TYPE;
2851 l_wip_entity_id NUMBER;
2852 l_org_id NUMBER;
2853
2854 --l_oper_status AHL_WORKORDER_OPERATIONS.status_code%TYPE;
2855 l_resource_code BOM_RESOURCES.resource_code%TYPE;
2856
2857 l_junk VARCHAR2(1);
2858 l_return_status VARCHAR2(1);
2859 l_msg_count NUMBER;
2860 l_msg_data VARCHAR2(2000);
2861
2862 l_login_workorder_id NUMBER;
2863 l_login_op_seq_num NUMBER;
2864 l_login_resource_id NUMBER;
2865 l_login_resrc_seq_num NUMBER;
2866 l_login_wo_name AHL_WORKORDERS.workorder_name%TYPE;
2867 l_resource_id NUMBER;
2868 l_employee_name per_people_f.full_name%TYPE;
2869
2870 l_fnd_function_name VARCHAR2(100);
2871
2872 BEGIN
2873
2874 -- log debug message.
2875 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
2876 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed.Start',
2877 'At Start of procedure AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed');
2878 END IF;
2879
2880 -- Dump Input parameters.
2881 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2882 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed.Input_Dump',
2883 'p_employee_id:' || p_employee_id);
2884 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed.Input_Dump',
2885 'p_workorder_id:' || p_workorder_id);
2886 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed.Input_Dump',
2887 'p_operation_seq_num:' || p_operation_seq_num);
2888 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed.Input_Dump',
2889 'p_resource_seq_num:' || p_resource_seq_num);
2890 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed.Input_Dump',
2891 'p_resource_id:' || p_resource_id);
2892 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed.Input_Dump',
2893 'p_fnd_function_name:' || p_fnd_function_name);
2894
2895 END IF;
2896
2897 -- Check if login enabled.
2898 IF (NVL(FND_PROFILE.value('AHL_PRD_MANUAL_RES_TXN'),'N') = 'Y') OR
2899 (l_fnd_function_name = AHL_PRD_UTIL_PKG.G_DATA_CLERK) THEN
2900 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_AUTOTXN_DSBLD');
2901 FND_MSG_PUB.ADD;
2902 Return FND_API.G_FALSE;
2903 END IF;
2904
2905 -- Debug Checkpoint.
2906 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2907 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed',
2908 'After Login enabled check');
2909 END IF;
2910
2911 -- Check required parameters.
2912 IF p_workorder_id IS NULL THEN
2913 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_WO_NULL');
2914 FND_MSG_PUB.ADD;
2915 Return FND_API.G_FALSE;
2916 END IF;
2917
2918 -- Debug Checkpoint.
2919 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2920 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed',
2921 'After required parameters check');
2922 END IF;
2923
2924 -- Validate Workorder.
2925 OPEN c_is_wo_valid (p_workorder_id);
2926 FETCH c_is_wo_valid INTO l_wo_status, l_wo_name, l_wip_entity_id, l_org_id;
2927 IF c_is_wo_valid%NOTFOUND THEN
2928 CLOSE c_is_wo_valid;
2929 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_WO_INVALID');
2930 FND_MSG_PUB.ADD;
2931 RETURN FND_API.G_FALSE;
2932 END IF;
2933 CLOSE c_is_wo_valid;
2934
2935 -- Validate WO status.
2936 IF l_wo_status in ('1','6','7','22','12','4','5') THEN
2937 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_WOSTS_INVLD');
2938 FND_MESSAGE.set_token('WO_NUM' , l_wo_name);
2939 FND_MSG_PUB.ADD;
2940 RETURN FND_API.G_FALSE;
2941 END IF;
2942
2943 -- Debug Checkpoint.
2944 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2945 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed',
2946 'After validate workorder');
2947 END IF;
2948
2949 -- Default employee if input is null.
2950 IF (p_employee_id IS NULL) THEN
2951 l_employee_id := Get_Employee_ID();
2952 ELSE
2953 -- validate employee_id.
2954 OPEN c_is_emp_valid(p_employee_id, l_org_id);
2955 FETCH c_is_emp_valid INTO l_employee_id;
2956 IF (c_is_emp_valid%NOTFOUND) THEN
2957 CLOSE c_is_emp_valid;
2958 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_EMPID_INVALID');
2959 FND_MESSAGE.set_token('EMP_ID' , p_employee_id);
2960 FND_MSG_PUB.ADD;
2961 RETURN FND_API.G_FALSE;
2962 END IF;
2963 CLOSE c_is_emp_valid;
2964 END IF;
2965
2966 -- Debug Checkpoint.
2967 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2968 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed',
2969 'After validating workorder and employee');
2970 END IF;
2971
2972 -- Check if user already logged in.
2973 AHL_PRD_WO_LOGIN_PVT.Get_Current_Emp_Login (
2974 p_employee_id => l_employee_id,
2975 x_return_status => l_return_status,
2976 x_msg_count => l_msg_count,
2977 x_msg_data => l_msg_data,
2978 x_workorder_id => l_login_workorder_id,
2979 x_workorder_number => l_login_wo_name,
2980 x_operation_seq_num => l_login_op_seq_num,
2981 x_resource_id => l_login_resource_id,
2982 x_resource_seq_num => l_login_resrc_seq_num,
2983 x_employee_name => l_employee_name);
2984
2985 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2986 RAISE FND_API.G_EXC_ERROR;
2987 END IF;
2988
2989 -- Check for login WO. If user already logged in, then do not allow login into another WO.
2990 IF (l_login_workorder_id IS NOT NULL) THEN
2991 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_USER_LOGGED_IN');
2992 FND_MSG_PUB.ADD;
2993 RETURN FND_API.G_FALSE;
2994 END IF;
2995
2996 -- Debug Checkpoint.
2997 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
2998 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed',
2999 'After login check successful');
3000 END IF;
3001
3002 -- Check Unit locked.
3003 IF AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => p_workorder_id,
3004 p_ue_id => null,
3005 p_visit_id => null,
3006 p_item_instance_id => null) = FND_API.g_true THEN
3007 -- Unit is locked, therefore cannot perform resource transactions
3008 -- and hence cannot login to the workorder
3009 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_UNTLCKD');
3010 FND_MESSAGE.set_token('WO_NUM' , l_wo_name);
3011 FND_MSG_PUB.ADD;
3012 RETURN FND_API.G_FALSE;
3013 END IF;
3014
3015 IF (p_fnd_function_name IS NULL) THEN
3016 l_fnd_function_name := get_user_role();
3017 ELSE
3018 l_fnd_function_name := p_fnd_function_name;
3019 END IF;
3020
3021 IF (l_fnd_function_name IS NULL) THEN
3022 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_FUNC_NULL');
3023 FND_MSG_PUB.ADD;
3024 Return FND_API.G_FALSE;
3025 END IF;
3026
3027 -- Debug Checkpoint.
3028 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3029 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed',
3030 'After unit lock check, user role check.. starting qualification checks for function:' ||
3031 l_fnd_function_name);
3032 END IF;
3033
3034 -- validate login into workorder/operation/operation-resource.
3035 IF (p_operation_seq_num IS NULL) THEN
3036
3037 -- Debug Checkpoint.
3038 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3039 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed',
3040 'Workorder Login validations');
3041 END IF;
3042
3043 -- validate if any operation is complete.
3044 OPEN c_workorder_oper(p_workorder_id);
3045 FETCH c_workorder_oper INTO l_junk;
3046 IF (c_workorder_oper%FOUND) THEN
3047 CLOSE c_workorder_oper;
3048 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_WOOPS_COMPLETE');
3049 FND_MESSAGE.set_token('WO_NUM' , l_wo_name);
3050 FND_MSG_PUB.ADD;
3051 RETURN FND_API.G_FALSE;
3052 END IF;
3053 CLOSE c_workorder_oper;
3054
3055 -- validate WO has at least one 'person' resource reqmt.
3056 OPEN c_check_res_reqd(l_wip_entity_id);
3057 FETCH c_check_res_reqd INTO l_junk;
3058 IF c_check_res_reqd%NOTFOUND THEN
3059 -- Login at the workorder level will be disabled.
3060 CLOSE c_check_res_reqd;
3061 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_WO_NO_RESREQ');
3062 FND_MESSAGE.set_token('WO_NUM' , l_wo_name);
3063 FND_MSG_PUB.ADD;
3064 RETURN FND_API.G_FALSE;
3065 END IF; -- c_check_res_reqd.
3066 CLOSE c_check_res_reqd;
3067
3068 -- Validate qualification to login into a WO in Technician case.
3069 -- Commented l_fnd_function_name validation to fix bug# 5015149.
3070 -- IF (l_fnd_function_name = AHL_PRD_UTIL_PKG.G_TECH_MYWO) THEN
3071 -- validate user qualifies for at least one resource requirement within each operation.
3072 FOR operation_rec IN c_get_workorder_oper(l_wip_entity_id) LOOP
3073 OPEN c_qualified_for_one_res(l_employee_id, l_wip_entity_id, operation_rec.operation_seq_num);
3074 FETCH c_qualified_for_one_res INTO l_junk;
3075 IF c_qualified_for_one_res%NOTFOUND THEN
3076 CLOSE c_qualified_for_one_res;
3077 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_WOOPS_NOTQUAL');
3078 FND_MESSAGE.set_token('WO_NUM' , l_wo_name);
3079 FND_MSG_PUB.ADD;
3080 RETURN FND_API.G_FALSE;
3081 END IF; -- c_qualified_for_one_ops
3082 CLOSE c_qualified_for_one_res;
3083 END LOOP; -- operation_rec
3084 -- END IF; -- l_fnd_function_name = AHL_PRD_UTIL_PKG.G_TECH_MYWO
3085
3086 /*
3087 -- If user qualifies for multiple resource requirements within the same operation and
3088 -- profile 'AHL_ALLOW_MULTI_RESRC_LOGIN' = 'Y then do not allow WO login.
3089 IF nvl(fnd_profile.value('AHL_ALLOW_MULTI_RESRC_LOGIN'), 'N') = 'Y' THEN
3090 OPEN c_check_res_multiple_wo(l_employee_id, l_wip_entity_id);
3091 FETCH c_check_res_multiple_wo INTO l_junk;
3092 IF (c_check_res_multiple_wo%FOUND) THEN
3093 -- disable login at WO level.
3094 CLOSE c_check_res_multiple_wo;
3095 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_MULTI_RES_WO');
3096 FND_MESSAGE.set_token('WO_NUM' , l_wo_name);
3097 FND_MSG_PUB.ADD;
3098 RETURN FND_API.G_FALSE;
3099 END IF;
3100 CLOSE c_check_res_multiple_wo;
3101 END IF;
3102 */
3103
3104 END IF; -- operation_seq_num IS NULL
3105
3106 IF (p_operation_seq_num IS NOT NULL) THEN
3107
3108 -- Debug Checkpoint.
3109 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3110 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed',
3111 'Starting Workorder-Operation status validations');
3112 END IF;
3113
3114 -- Validate operation seq num.
3115 -- If operation has no person resource requirements, user cannot login into operation.
3116 -- operation status should be uncomplete.
3117 OPEN c_is_op_seq_valid (p_workorder_id, p_operation_seq_num, l_wip_entity_id);
3118 FETCH c_is_op_seq_valid INTO l_junk;
3119 IF c_is_op_seq_valid%NOTFOUND THEN
3120 CLOSE c_is_op_seq_valid;
3121 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_OP_INVALID');
3122 FND_MESSAGE.SET_TOKEN('OP_NUM', p_operation_seq_num);
3123 FND_MESSAGE.set_token('WO_NUM' , l_wo_name);
3124 FND_MSG_PUB.ADD;
3125 RETURN FND_API.G_FALSE;
3126 END IF; -- c_is_op_seq_valid.
3127 CLOSE c_is_op_seq_valid;
3128
3129 -- Check login into Operation or Operation+resource.
3130 IF (p_resource_id IS NULL AND p_resource_seq_num IS NULL) THEN
3131
3132 -- Login into operation only.
3133
3134 -- Debug Checkpoint.
3135 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3136 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed',
3137 'Workorder-Operation Login qualification validations');
3138 END IF;
3139
3140
3141 -- Validate qualification to login into a Operation in Technician case.
3142 -- Commented l_fnd_function_name validation to fix bug# 5015149.
3143 -- IF (l_fnd_function_name = AHL_PRD_UTIL_PKG.G_TECH_MYWO) THEN
3144 -- validate user qualifies for at least one resource requirement for the operation.
3145 OPEN c_qualified_for_one_res(l_employee_id, l_wip_entity_id, p_operation_seq_num);
3146 FETCH c_qualified_for_one_res INTO l_junk;
3147 IF (c_qualified_for_one_res%NOTFOUND) THEN
3148 CLOSE c_qualified_for_one_res;
3149 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_OP_NOT_QUAL');
3150 FND_MESSAGE.SET_TOKEN('OP_NUM', p_operation_seq_num);
3151 FND_MESSAGE.set_token('WO_NUM' , l_wo_name);
3152 FND_MSG_PUB.ADD;
3153 RETURN FND_API.G_FALSE;
3154 END IF; -- c_qualified_for_one_res
3155 CLOSE c_qualified_for_one_res;
3156 -- END IF; -- l_fnd_function_name = AHL_PRD_UTIL_PKG.G_TECH_MYWO
3157
3158 /*
3159 -- If user qualifies for multiple resource requirements within the same operation and
3160 -- profile 'AHL_ALLOW_MULTI_RESRC_LOGIN' = 'Y then do not allow WO-OP login.
3161 IF nvl(fnd_profile.value('AHL_ALLOW_MULTI_RESRC_LOGIN'), 'N') = 'Y' THEN
3162 OPEN c_check_res_multiple_op(l_employee_id, l_wip_entity_id, p_operation_seq_num);
3163 FETCH c_check_res_multiple_op INTO l_junk;
3164 IF (c_check_res_multiple_op%FOUND) THEN
3165 -- disable login at WO level.
3166 CLOSE c_check_res_multiple_op;
3167 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_MULTI_RES_OP');
3168 FND_MESSAGE.set_token('OP_NUM' , p_operation_seq_num);
3169 FND_MSG_PUB.ADD;
3170 RETURN FND_API.G_FALSE;
3171 END IF;
3172 CLOSE c_check_res_multiple_op;
3173 END IF;
3174 */
3175
3176 ELSE -- login into operation + resource.
3177
3178 -- Debug Checkpoint.
3179 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3180 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed',
3181 'Starting Workorder-Operation-Resource validations');
3182 END IF;
3183
3184 -- Get resource ID if resource seq num is input and resource_id is NULL.
3185 IF (p_resource_id IS NULL AND p_resource_seq_num IS NOT NULL) THEN
3186 OPEN c_get_resrc_id(l_wip_entity_id, p_operation_seq_num, p_resource_seq_num);
3187 FETCH c_get_resrc_id INTO l_resource_id;
3188 IF (c_get_resrc_id%NOTFOUND) THEN
3189 CLOSE c_get_resrc_id;
3190 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_RESSEQ_INVALID');
3191 FND_MESSAGE.SET_TOKEN('OP_NUM', p_operation_seq_num);
3192 FND_MESSAGE.set_token('WO_NUM' , l_wo_name);
3193 FND_MESSAGE.set_token('RES_NAME' ,p_resource_seq_num);
3194 FND_MSG_PUB.ADD;
3195 RETURN FND_API.G_FALSE;
3196 END IF;
3197 CLOSE c_get_resrc_id;
3198 ELSE
3199 l_resource_id := p_resource_id;
3200 END IF; -- p_resource_id IS NULL AND ...
3201
3202 -- Validate Resource ID.
3203 OPEN c_is_resource_valid(l_resource_id, l_org_id);
3204 FETCH c_is_resource_valid INTO l_resource_code;
3205 IF (c_is_resource_valid%NOTFOUND) THEN
3206 CLOSE c_is_resource_valid;
3207 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_RESID_INVALID');
3208 FND_MESSAGE.SET_TOKEN('OP_NUM', p_operation_seq_num);
3209 FND_MESSAGE.set_token('WO_NUM' , l_wo_name);
3210 FND_MESSAGE.set_token('RES_ID' , l_resource_id);
3211 FND_MSG_PUB.ADD;
3212 RETURN FND_API.G_FALSE;
3213 END IF; -- c_is_resource_valid
3214 CLOSE c_is_resource_valid;
3215
3216 -- Validate qualification in case of technician.
3217 -- Commented l_fnd_function_name validation to fix bug# 5015149.
3218 -- IF (l_fnd_function_name = AHL_PRD_UTIL_PKG.G_TECH_MYWO) THEN
3219 OPEN c_qualify_res(l_employee_id, l_wip_entity_id, p_operation_seq_num,
3220 l_resource_id);
3221 FETCH c_qualify_res INTO l_junk;
3222 IF (c_qualify_res%FOUND) THEN
3223 CLOSE c_qualify_res;
3224 FND_MESSAGE.set_name('AHL', 'AHL_PRD_LGN_OP_NOTQUAL');
3225 FND_MESSAGE.SET_TOKEN('OP_NUM', p_operation_seq_num);
3226 FND_MESSAGE.set_token('WO_NUM' , l_wo_name);
3227 FND_MESSAGE.set_token('RES_NUM', l_resource_code);
3228 FND_MSG_PUB.ADD;
3229 RETURN FND_API.G_FALSE;
3230 END IF;
3231 CLOSE c_qualify_res;
3232 -- END IF; -- l_fnd_function_name = AHL_PRD_UTIL_PKG.G_TECH_MYWO
3233
3234 END IF; -- p_resource_id IS NULL
3235
3236 END IF; -- operation_seq_num is not null.
3237
3238 -- Debug Checkpoint.
3239 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
3240 fnd_log.string(G_DEBUG_STMT, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed',
3241 'Successfully completed all qualification validations ...');
3242 END IF;
3243
3244 -- log debug message.
3245 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
3246 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed.End',
3247 'At End of procedure AHL_PRD_WO_LOGIN_PVT.Is_Login_Allowed');
3248 END IF;
3249
3250 -- Set login allowed flag.
3251 RETURN FND_API.G_TRUE;
3252
3253 EXCEPTION
3254 WHEN FND_API.G_EXC_ERROR THEN
3255 --x_return_status := FND_API.G_RET_STS_ERROR;
3256
3257 RETURN FND_API.G_FALSE;
3258
3259 WHEN OTHERS THEN
3260 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3261 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3262 fnd_msg_pub.add_exc_msg
3263 (
3264 p_pkg_name => G_PKG_NAME,
3265 p_procedure_name => 'Is_Login_Allowed',
3266 p_error_text => SUBSTR(SQLERRM,1,240)
3267 );
3268 END IF;
3269 RETURN FND_API.G_FALSE;
3270
3271 END Is_Login_Allowed;
3272
3273
3274 ----------------------------------------------------------------------------------------------------
3275 --Wrapper procedure used by Technician workbench, Transit Technician and Data Clerk Search Wo UIs
3276 --This procedure returns whether login is allowed for all workorder_ids passed.
3277 ----------------------------------------------------------------------------------------------------
3278 PROCEDURE get_wo_login_info(p_function_name IN VARCHAR2,
3279 p_employee_id IN NUMBER,
3280 p_x_wos IN OUT NOCOPY WO_TBL_TYPE)
3281 IS
3282
3283 BEGIN
3284
3285
3286 -- log debug message.
3287 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
3288 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.get_wo_login_info.Start',
3289 'At Start of procedure AHL_PRD_WO_LOGIN_PVT.get_wo_login_info');
3290 END IF;
3291
3292 IF p_x_wos.COUNT > 0 THEN
3293 FOR i IN p_x_wos.FIRST..p_x_wos.LAST
3294 LOOP
3295 p_x_wos(i).is_login_allowed := Is_Login_Allowed(
3296 p_employee_id => p_employee_id,
3297 p_workorder_id => p_x_wos(i).workorder_id,
3298 p_fnd_function_name => p_function_name
3299 );
3300 END LOOP;
3301 END IF;
3302
3303 -- log debug message.
3304 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
3305 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.get_wo_login_info.End',
3306 'At End of procedure AHL_PRD_WO_LOGIN_PVT.get_wo_login_info');
3307 END IF;
3308
3309 END get_wo_login_info;
3310
3311 ----------------------------------------------------------------------------------------------------
3312 --Wrapper procedure used by Technician workbench, Transit Technician and Data Clerk Search Wo UIs
3313 --This procedure returns whether login is allowed for all all operations of a workorder passed.
3314 ----------------------------------------------------------------------------------------------------
3315 PROCEDURE get_op_res_login_info(p_workorder_id IN NUMBER,
3316 p_employee_id IN NUMBER,
3317 p_function_name IN VARCHAR2,
3318 p_x_op_res IN OUT NOCOPY OP_RES_TBL_TYPE)
3319 IS
3320 BEGIN
3321
3322 -- log debug message.
3323 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
3324 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.get_op_res_login_info.Start',
3325 'At Start of procedure AHL_PRD_WO_LOGIN_PVT.get_op_res_login_info');
3326 END IF;
3327
3328 IF p_x_op_res.COUNT > 0 AND p_workorder_id IS NOT NULL
3329 THEN
3330 FOR i IN p_x_op_res.FIRST..p_x_op_res.LAST
3331 LOOP
3332 p_x_op_res(i).is_login_allowed := Is_Login_Allowed(
3333 p_employee_id => p_employee_id,
3334 p_workorder_id => p_workorder_id,
3335 p_operation_seq_num => p_x_op_res(i).operation_seq_num,
3336 p_resource_id => p_x_op_res(i).resource_id,
3337 p_fnd_function_name => p_function_name
3338 );
3339 END LOOP;
3340 END IF;
3341
3342 -- log debug message.
3343 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL) THEN
3344 fnd_log.string(G_DEBUG_PROC, 'ahl.plsql.AHL_PRD_WO_LOGIN_PVT.get_op_res_login_info.End',
3345 'At End of procedure AHL_PRD_WO_LOGIN_PVT.get_op_res_login_info');
3346 END IF;
3347
3348 END get_op_res_login_info;
3349
3350
3351
3352 END AHL_PRD_WO_LOGIN_PVT;