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