1 PACKAGE BODY AHL_PRD_NONROUTINE_PUB AS
2 /* $Header: AHLPPNRB.pls 120.0.12010000.1 2008/11/30 21:06:57 sikumar noship $ */
3 ------------------------------------
4 -- Common constants and variables --
5 ------------------------------------
6 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
7 l_log_statement NUMBER := fnd_log.level_statement;
8 l_log_procedure NUMBER := fnd_log.level_procedure;
9 l_log_error NUMBER := fnd_log.level_error;
10 l_log_unexpected NUMBER := fnd_log.level_unexpected;
11 G_DEBUG VARCHAR2(1) := NVL(AHL_DEBUG_PUB.is_log_enabled,'N');
12
13 G_BPEL_USER_ROLE_KEY VARCHAR2(240) := FND_PROFILE.VALUE('AHL_BPEL_USER_ROLE');
14
15 FUNCTION init_user_and_role(p_user_id IN VARCHAR2) RETURN VARCHAR2 IS
16
17 CURSOR get_user_id_csr(p_user_id IN VARCHAR2) IS
18 select user_id from fnd_user where user_name = p_user_id;
19
20 l_user_id NUMBER;
21 l_resp_id NUMBER;
22
23 CURSOR get_resp_id_csr IS
24 select responsibility_id from fnd_responsibility_vl where responsibility_key = G_BPEL_USER_ROLE_KEY;
25
26 BEGIN
27 OPEN get_user_id_csr(p_user_id);
28 FETCH get_user_id_csr INTO l_user_id;
29 IF get_user_id_csr%NOTFOUND THEN
30 FND_MESSAGE.set_name('AHL','AHL_PRD_INV_BPEL_USR');
31 FND_MESSAGE.SET_TOKEN('USER_NAME',p_user_id);
32 FND_MSG_PUB.ADD;
33 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
34 END IF;
35 CLOSE get_user_id_csr;
36
37 OPEN get_resp_id_csr;
38 FETCH get_resp_id_csr INTO l_resp_id;
39 CLOSE get_resp_id_csr;
40
41 FND_GLOBAL.apps_initialize(l_user_id,l_resp_id,867);
42 mo_global.init('AHL');
43 return Fnd_Api.G_RET_STS_SUCCESS;
44
45 END init_user_and_role;
46
47 PROCEDURE POPULATE_CREATE_SR_INPUT_REC( p_create_nr_input_rec IN NON_ROUTINE_REC_TYPE,
48 x_sr_task_rec OUT NOCOPY AHL_PRD_NONROUTINE_PVT.SR_TASK_REC_TYPE);
49
50 PROCEDURE POPULATE_CREATE_SR_OUTPUT_REC( x_create_nr_output_rec OUT NOCOPY NON_ROUTINE_REC_TYPE,
51 p_sr_task_rec IN AHL_PRD_NONROUTINE_PVT.SR_TASK_REC_TYPE);
52
53 PROCEDURE POPULATE_CREATE_MTRL_INPUT_REC( p_matrl_reqrs_for_nr_tbl IN MATERIAL_REQUIREMENTS_TBL,
54 p_workorder_id IN NUMBER,
55 x_req_material_tbl OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type);
56
57 PROCEDURE POPULATE_CREATE_NR_INPUT_REC( p_create_nr_input_rec IN NON_ROUTINE_REC_TYPE,
58 x_nr_task_rec OUT NOCOPY AHL_UMP_NONROUTINES_PVT.NonRoutine_Rec_Type);
59
60 PROCEDURE POPULATE_CREATE_NR_OUTPUT_REC( x_create_nr_output_rec OUT NOCOPY NON_ROUTINE_REC_TYPE,
61 p_nr_task_rec IN AHL_UMP_NONROUTINES_PVT.NonRoutine_Rec_Type);
62
63
64 ------------------------------------------------------------------------------------------------------------------
65 -- Start of Comments --
66 -- Procedure name : CREATE_NON_ROUTINE
67 -- Type : Public
68 -- Function : Creates a SR either in the context of WO and adds material reqrs or creates a SR independently
69 -- Pre-reqs :
70 -- PROCESS Parameters:
71 -- p_create_non_routine_input_rec : Parameters needed for the creation of the NR
72 -- p_matrl_reqrs_for_nr_tbl : Material requirements for the NR
73 -- x_create_non_routine_output_rec : Parameters returned after the creation of the NR
74 -- End of Comments.
75 ------------------------------------------------------------------------------------------------------------------
76 PROCEDURE CREATE_NON_ROUTINE
77 (
78 p_api_version IN NUMBER := 1.0,
79 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
80 p_commit IN VARCHAR2 := FND_API.G_FALSE,
81 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
82 p_module_type IN VARCHAR2,
83 p_user_id IN VARCHAR2:=NULL,
84 p_create_nr_input_rec IN NON_ROUTINE_REC_TYPE,
85 p_matrl_reqrs_for_nr_tbl IN MATERIAL_REQUIREMENTS_TBL,
86 x_create_nr_output_rec OUT NOCOPY NON_ROUTINE_REC_TYPE,
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2
90 )
91 IS
92 -- Declare local variables
93 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_NON_ROUTINE';
94 l_api_version CONSTANT NUMBER := 1.0;
95 l_debug_module CONSTANT VARCHAR2(100) := 'AHL.PLSQL.'||'AHL_NON_ROUTINE_PUB'||'.'||'CREATE_NON_ROUTINE';
96
97
98 p_x_sr_task_tbl ahl_prd_nonroutine_pvt.sr_task_tbl_type;
99 p_sr_task_rec ahl_prd_nonroutine_pvt.sr_task_rec_type;
100 x_sr_task_rec ahl_prd_nonroutine_pvt.sr_task_rec_type;
101 x_req_material_tbl ahl_pp_materials_pvt.req_material_tbl_type;
102 x_nr_task_rec ahl_ump_nonroutines_pvt.nonroutine_rec_type;
103
104 p_material_req_rec material_requirement_rec_type;
105
106 x_job_return_status VARCHAR2(1);
107 l_mr_asso_tbl AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type;
108 BEGIN
109 -- Standard start of API savepoint
110 SAVEPOINT CREATE_NON_ROUTINE_SP;
111
112 -- Initialize return status to success before any code logic/validation
113 x_return_status:= FND_API.G_RET_STS_SUCCESS;
114
115 -- Standard call to check for call compatibility
116 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
117 THEN
118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
119 END IF;
120
121 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
122 IF FND_API.TO_BOOLEAN(p_init_msg_list)
123 THEN
124 FND_MSG_PUB.INITIALIZE;
125 END IF;
126
127 --fnd_global.APPS_INITIALIZE (1003259,62211, 867);
128 --mo_global.SET_POLICY_CONTEXT('S',4698);
129 x_return_status := init_user_and_role(p_user_id);
130
131 -- Log API entry point
132 IF (l_log_procedure >= l_log_current_level) THEN
133 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.begin','At the start of PL SQL procedure ');
134 END IF;
135
136 -- If the originator wo id is present
137 IF( (p_create_nr_input_rec.ORIGINATOR_WORKORDER_ID IS NOT NULL AND p_create_nr_input_rec.ORIGINATOR_WORKORDER_ID <> FND_API.G_MISS_NUM)
138 OR
139 (p_create_nr_input_rec.ORIGINATOR_WORKORDER_NUMBER IS NOT NULL AND p_create_nr_input_rec.ORIGINATOR_WORKORDER_NUMBER <> FND_API.G_MISS_CHAR)
140 )
141 THEN
142 -- populate the record to be passed to AHL_PRD_NONROUTINE_PVT
143 POPULATE_CREATE_SR_INPUT_REC(p_create_nr_input_rec,x_sr_task_rec);
144 p_x_sr_task_tbl(1) := x_sr_task_rec;
145
146 IF (l_log_statement >= l_log_current_level) THEN
147 fnd_log.string(fnd_log.level_statement,l_debug_module,'Calling AHL_PRD_NONROUTINE_PVT.Process_nonroutine_job');
148 END IF;
149 -- Call the API to create the SR and workorder
150 AHL_PRD_NONROUTINE_PVT.Process_nonroutine_job (
151 1.0,
152 FND_API.G_TRUE,
153 FND_API.G_FALSE,
154 FND_API.G_VALID_LEVEL_FULL,
155 'JSP',
156 x_return_status,
157 x_msg_count,
158 x_msg_data,
159 p_x_sr_task_tbl,
160 l_mr_asso_tbl
161 );
162
163 IF (l_log_statement >= l_log_current_level) THEN
164 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_PRD_NONROUTINE_PVT.Process_nonroutine_job');
165 END IF;
166
167 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
168 IF (l_log_statement >= l_log_current_level) THEN
169 fnd_log.string(fnd_log.level_statement,l_debug_module,'Call to AHL_PRD_NONROUTINE_PVT.Process_nonroutine_job, Not Success');
170 END IF;
171 RAISE FND_API.G_EXC_ERROR;
172 END IF;
173
174 -- Get the params for output
175 p_sr_task_rec := p_x_sr_task_tbl(1);
176 -- p_sr_task_rec.originating_wo_id := x_sr_task_rec.originating_wo_id;
177 POPULATE_CREATE_SR_OUTPUT_REC(x_create_nr_output_rec,p_sr_task_rec);
178 /* CREATE NON ROUTINE, CREATE A JOB , RELEASE THE JOB : END */
179
180 IF (p_matrl_reqrs_for_nr_tbl.count > 0) THEN
181 p_material_req_rec := p_matrl_reqrs_for_nr_tbl(p_matrl_reqrs_for_nr_tbl.FIRST);
182 -- Material Requirements are considered only atleast one of id or name is present
183 IF ( (p_material_req_rec.INVENTORY_ITEM_ID IS NOT NULL AND p_material_req_rec.INVENTORY_ITEM_ID <> FND_API.G_MISS_NUM )
184 OR
185 (p_material_req_rec.ITEM_NUMBER IS NOT NULL AND p_material_req_rec.ITEM_NUMBER <> FND_API.G_MISS_CHAR )
186 )
187 THEN
188 IF (l_log_statement >= l_log_current_level) THEN
189 fnd_log.string(fnd_log.level_statement,l_debug_module,'Calling POPULATE_CREATE_MTRL_INPUT_REC');
190 END IF;
191 POPULATE_CREATE_MTRL_INPUT_REC(p_matrl_reqrs_for_nr_tbl,p_sr_task_rec.Nonroutine_wo_id,x_req_material_tbl);
192 IF (l_log_statement >= l_log_current_level) THEN
193 fnd_log.string(fnd_log.level_statement,l_debug_module,'After POPULATE_CREATE_MTRL_INPUT_REC');
194 END IF;
195 IF (l_log_statement >= l_log_current_level) THEN
196 fnd_log.string(fnd_log.level_statement,l_debug_module,'Calling AHL_PP_MATERIALS_PVT.Create_Material_Reqst');
197 END IF;
198
199 SAVEPOINT CREATE_NON_ROUTINE_MTRLS_SP;
200
201 AHL_PP_MATERIALS_PVT.Create_Material_Reqst (
202 1.0,
203 FND_API.G_TRUE,
204 Fnd_Api.G_FALSE,
205 FND_API.G_VALID_LEVEL_FULL,
206 'Y',
207 x_req_material_tbl,
208 x_job_return_status,
209 x_return_status,
210 x_msg_count,
211 x_msg_data
212 );
213
214 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
215 IF (l_log_statement >= l_log_current_level) THEN
216 fnd_log.string(fnd_log.level_statement,l_debug_module,'Error after AHL_PP_MATERIALS_PVT.Create_Material_Reqst');
217 ROLLBACK TO CREATE_NON_ROUTINE_MTRLS_SP;
218 FND_MESSAGE.SET_NAME ('AHL','AHL_NR_SUCC_MATRL_FAIL');
219 FND_MSG_PUB.ADD;
220 END IF;
221 -- RAISE FND_API.G_EXC_ERROR;
222 END IF;
223
224
225 ELSE
226 IF (l_log_statement >= l_log_current_level) THEN
227 fnd_log.string(fnd_log.level_statement,l_debug_module,'No Materials Requirements to be added');
228 END IF;
229 END IF;
230 END IF;
231 /* ADD MATERIAL REQUIREMENTS TO THE JOB CREATED : END */
232 ELSE
233
234 POPULATE_CREATE_NR_INPUT_REC(p_create_nr_input_rec,x_nr_task_rec);
235 AHL_UMP_NONROUTINES_PVT.Create_SR
236 (
237 1.0,
238 FND_API.G_TRUE,
239 FND_API.G_FALSE,
240 FND_API.G_VALID_LEVEL_FULL,
241 FND_API.G_FALSE,
242 'JSP',
243 x_return_status,
244 x_msg_count,
245 x_msg_data,
246 x_nr_task_rec
247 );
248 IF (l_log_statement >= l_log_current_level) THEN
249 fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_UMP_NONROUTINES_PVT.Create_SR');
250 END IF;
251
252 IF ( upper(x_return_status) <> FND_API.G_RET_STS_SUCCESS ) THEN
253 IF (l_log_statement >= l_log_current_level) THEN
254 fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_UMP_NONROUTINES_PVT.Create_SR');
255 END IF;
256 RAISE FND_API.G_EXC_ERROR;
257 END IF;
258 POPULATE_CREATE_NR_OUTPUT_REC(x_create_nr_output_rec,x_nr_task_rec);
259
260 -- Added
261 IF FND_API.TO_BOOLEAN(p_commit) THEN
262 COMMIT WORK;
263 END IF;
264 END IF;
265
266 -- Standard check of p_commit
267 IF FND_API.TO_BOOLEAN(p_commit) THEN
268 COMMIT WORK;
269 END IF;
270
271 IF (G_DEBUG = 'Y') THEN
272 AHL_DEBUG_PUB.debug('END - Successfully completion of '||G_PKG_NAME||'.'||l_api_name||' API ');
273 END IF;
274
275 -- Count and Get messages
276 FND_MSG_PUB.count_and_get
277 ( p_encoded => fnd_api.g_false,
278 p_count => x_msg_count,
279 p_data => x_msg_data
280 );
281
282 -- Disable debug (if enabled)
283 IF (G_DEBUG = 'Y') THEN
284 AHL_DEBUG_PUB.disable_debug;
285 END IF;
286
287 EXCEPTION
288 WHEN FND_API.G_EXC_ERROR THEN
289 x_return_status := FND_API.G_RET_STS_ERROR;
290 ROLLBACK TO CREATE_NON_ROUTINE_SP;
291 x_msg_count := FND_MSG_PUB.Count_Msg;
292 x_msg_data := AHL_PRD_WO_PUB.GET_MSG_DATA(x_msg_count);
293 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
294
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
296 ROLLBACK TO CREATE_NON_ROUTINE_SP;
297 x_msg_count := FND_MSG_PUB.Count_Msg;
298 x_msg_data := AHL_PRD_WO_PUB.GET_MSG_DATA(x_msg_count);
299
300 WHEN OTHERS THEN
301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
302 ROLLBACK TO CREATE_NON_ROUTINE_SP;
303 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
304 THEN
305 FND_MSG_PUB.add_exc_msg
306 (
307 p_pkg_name => G_PKG_NAME,
308 p_procedure_name => l_debug_module,
309 p_error_text => SUBSTR(SQLERRM,1,240)
310 );
311 END IF;
312 x_msg_count := FND_MSG_PUB.Count_Msg;
313 x_msg_data := AHL_PRD_WO_PUB.GET_MSG_DATA(x_msg_count);
314 END CREATE_NON_ROUTINE;
315
316 ------------------------------------------------------------------------------------------------------------------
317 -- Start of Comments --
318 -- Procedure name : POPULATE_CREATE_SR_INPUT_REC
319 -- Type : Private
320 -- Function : Populates the SR record for creation, from the params that are available
321 -- Pre-reqs :
322 -- PROCESS Parameters:
323 -- p_create_nr_input_rec : Parameters available to CMRO
324 -- x_service_request_rec : Record which contains params that SR api needs for Creation
325 -- End of Comments.
326
327 ------------------------------------------------------------------------------------------------------------------
328 PROCEDURE POPULATE_CREATE_SR_INPUT_REC( p_create_nr_input_rec IN NON_ROUTINE_REC_TYPE,
329 x_sr_task_rec OUT NOCOPY ahl_prd_nonroutine_pvt.sr_task_rec_type
330 )
331 IS
332 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||'ahl_non_routine_pub'||'.'||'populate_create_sr_input_rec';
333
334 BEGIN
335 -- Log API entry point
336 IF (l_log_procedure >= l_log_current_level) THEN
337 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.begin','At the start of PL SQL procedure ');
338 END IF;
339
340 -- If Workorder ID as well Workorder number are present, honour the id
341 IF( (p_create_nr_input_rec.ORIGINATOR_WORKORDER_ID IS NOT NULL AND p_create_nr_input_rec.ORIGINATOR_WORKORDER_ID <> FND_API.G_MISS_NUM)
342 and
343 (p_create_nr_input_rec.ORIGINATOR_WORKORDER_NUMBER IS NOT NULL AND p_create_nr_input_rec.ORIGINATOR_WORKORDER_NUMBER <> FND_API.G_MISS_CHAR)
344 )
345 THEN
346 x_sr_task_rec.Originating_wo_id := p_create_nr_input_rec.ORIGINATOR_WORKORDER_ID;
347 BEGIN
348 SELECT csi.instance_number,vst.visit_number
349 INTO x_sr_task_rec.instance_number,x_sr_task_rec.visit_number
350 FROM ahl_workorders wo,
351 ahl_visits_b vst,
352 ahl_visit_tasks_b tsk,
353 csi_item_instances csi
354 WHERE wo.visit_task_id = tsk.visit_task_id
355 AND vst.visit_id = tsk.visit_id
356 AND NVL(tsk.instance_id,vst.item_instance_id)=csi.instance_id
357 AND wo.workorder_id = p_create_nr_input_rec.ORIGINATOR_WORKORDER_ID;
358 EXCEPTION
359 WHEN NO_DATA_FOUND THEN
360 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_INVLD_WO');
361 FND_MSG_PUB.ADD;
362 RAISE FND_API.G_EXC_ERROR;
363 WHEN TOO_MANY_ROWS THEN
364 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_INVLD_WO');
365 FND_MSG_PUB.ADD;
366 RAISE FND_API.G_EXC_ERROR;
367 END;
368 ELSIF
369 ( (p_create_nr_input_rec.ORIGINATOR_WORKORDER_ID IS NULL OR p_create_nr_input_rec.ORIGINATOR_WORKORDER_ID = FND_API.G_MISS_NUM )
370 and
371 (p_create_nr_input_rec.ORIGINATOR_WORKORDER_NUMBER IS NOT NULL AND p_create_nr_input_rec.ORIGINATOR_WORKORDER_NUMBER <> FND_API.G_MISS_CHAR)
372 )
373 THEN
374 BEGIN
375 SELECT CSI.INSTANCE_NUMBER,VST.visit_number,WO.workorder_id
376 INTO x_sr_task_rec.instance_number,x_sr_task_rec.visit_number,x_sr_task_rec.Originating_wo_id
377 FROM AHL_WORKORDERS WO,
378 AHL_VISITS_B VST,
379 AHL_VISIT_TASKS_B TSK,
380 CSI_ITEM_INSTANCES CSI
381 WHERE WO.VISIT_TASK_ID = TSK.VISIT_TASK_ID
382 AND VST.VISIT_ID = TSK.VISIT_ID
383 AND NVL(TSK.INSTANCE_ID,VST.ITEM_INSTANCE_ID)=CSI.INSTANCE_ID
384 AND WO.WORKORDER_NAME = p_create_nr_input_rec.ORIGINATOR_WORKORDER_NUMBER;
385 EXCEPTION
386 WHEN NO_DATA_FOUND THEN
387 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_INVLD_WO');
388 FND_MSG_PUB.ADD;
389 RAISE FND_API.G_EXC_ERROR;
390 WHEN TOO_MANY_ROWS THEN
391 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_INVLD_WO');
392 FND_MSG_PUB.ADD;
393 RAISE FND_API.G_EXC_ERROR;
394 END;
395 ELSE
396 FND_MESSAGE.SET_NAME ('AHL','AHL_PRD_INVLD_WO');
397 FND_MSG_PUB.ADD;
398 RAISE FND_API.G_EXC_ERROR;
399 END IF;
400
401 -- populate the request date
402 x_sr_task_rec.request_date := p_create_nr_input_rec.service_request_date;
403
404 -- populate the summary
405 x_sr_task_rec.summary := p_create_nr_input_rec.summary;
406
407 -- populate the operation type
408 x_sr_task_rec.operation_type := 'CREATE';
409
410 IF p_create_nr_input_rec.service_request_type <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.service_request_type <> NULL THEN
411 x_sr_task_rec.Type_name := p_create_nr_input_rec.service_request_type;
412 END IF;
413
414 IF p_create_nr_input_rec.service_request_status <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.service_request_type <> NULL THEN
415 x_sr_task_rec.Status_name := p_create_nr_input_rec.service_request_status;
416 END IF;
417
418 IF p_create_nr_input_rec.problem_code <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.problem_code <> NULL THEN
419 x_sr_task_rec.Problem_code := p_create_nr_input_rec.problem_code;
420 END IF;
421
422 IF p_create_nr_input_rec.severity_name <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.severity_name <> NULL THEN
423 x_sr_task_rec.Severity_name := p_create_nr_input_rec.severity_name;
424 END IF;
425
426 IF p_create_nr_input_rec.contact_type <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.contact_type <> NULL THEN
427 x_sr_task_rec.Contact_type := p_create_nr_input_rec.contact_type;
428 END IF;
429
430 IF p_create_nr_input_rec.contact_name <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.contact_name <> NULL THEN
431 x_sr_task_rec.contact_name := p_create_nr_input_rec.contact_name;
432 END IF;
433
434 IF p_create_nr_input_rec.resolution_code_meaning <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.resolution_code_meaning <> NULL THEN
435 x_sr_task_rec.resolution_meaning := p_create_nr_input_rec.resolution_code_meaning;
436 END IF;
437
438 IF p_create_nr_input_rec.estimated_duration <> fnd_api.g_miss_num AND p_create_nr_input_rec.estimated_duration <> NULL THEN
439 x_sr_task_rec.duration := p_create_nr_input_rec.estimated_duration;
440 END IF;
441
442
443 -- Log API exit point
444 IF (l_log_procedure >= l_log_current_level) THEN
445 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.end','At the start of PL SQL procedure ');
446 END IF;
447 END POPULATE_CREATE_SR_INPUT_REC;
448
449 -----------------------------------------------------------------------------------------------------------------
450 -- Start of Comments --
451 -- Procedure name : POPULATE_CREATE_MTRL_INPUT_REC
452 -- Type : Private
453 -- Function : Populates the material requirements
454 -- Pre-reqs :
455 -- PROCESS Parameters:
456 -- p_matrl_reqrs_for_nr_tbl : Material reqrmts to be created
457 -- p_workorder_id : ID of the just created WO
458 -- x_req_material_tbl : Parameters to be passed to the caller
459 -- End of Comments.
460 ------------------------------------------------------------------------------------------------------------------
461 PROCEDURE POPULATE_CREATE_MTRL_INPUT_REC( p_matrl_reqrs_for_nr_tbl IN MATERIAL_REQUIREMENTS_TBL,
462 p_workorder_id IN NUMBER,
463 x_req_material_tbl OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type)
464 IS
465 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||'ahl_non_routine_pub'||'.'||'populate_create_mtrl_input_rec';
466 BEGIN
467 -- Log API entry point
468 IF (l_log_procedure >= l_log_current_level) THEN
469 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.Begin','At the start of PL SQL procedure ');
470 END IF;
471 IF p_matrl_reqrs_for_nr_tbl.COUNT > 0 THEN
472 FOR i IN p_matrl_reqrs_for_nr_tbl.FIRST..p_matrl_reqrs_for_nr_tbl.LAST
473 LOOP
474 x_req_material_tbl(i).WORKORDER_ID := p_workorder_id;
475 x_req_material_tbl(i).OPERATION_SEQUENCE := 10;
476 x_req_material_tbl(i).INVENTORY_ITEM_ID := NULL;
477 x_req_material_tbl(i).CONCATENATED_SEGMENTS := p_matrl_reqrs_for_nr_tbl(i).ITEM_NUMBER;
478 x_req_material_tbl(i).ITEM_DESCRIPTION := p_matrl_reqrs_for_nr_tbl(i).ITEM_DESCRIPTION;
479 x_req_material_tbl(i).REQUESTED_QUANTITY := p_matrl_reqrs_for_nr_tbl(i).REQUIRED_QUANTITY;
480 x_req_material_tbl(i).UOM_CODE := p_matrl_reqrs_for_nr_tbl(i).PART_UOM;
481 x_req_material_tbl(i).REQUESTED_DATE := p_matrl_reqrs_for_nr_tbl(i).REQUIRED_DATE;
482 END LOOP;
483 END IF;
484 -- Log API exit point
485 IF (l_log_procedure >= l_log_current_level) THEN
486 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.End','At the end of PL SQL procedure ');
487 END IF;
488 END POPULATE_CREATE_MTRL_INPUT_REC;
489
490 ------------------------------------------------------------------------------------------------------------------
491 -- Start of Comments --
492 -- Procedure name : POPULATE_CREATE_NR_INPUT_REC
493 -- Type : Private
494 -- Function : Populates the params for Creating a NR
495 -- Pre-reqs :
496 -- PROCESS Parameters:
497 -- p_create_nr_input_rec : Parameters for the creation of NR obtained from the caller
498 -- x_nr_task_rec : Parameters to be passed for the creation of the NR
499 -- End of Comments.
500 ------------------------------------------------------------------------------------------------------------------
501 PROCEDURE POPULATE_CREATE_NR_INPUT_REC( p_create_nr_input_rec IN NON_ROUTINE_REC_TYPE,
502 x_nr_task_rec OUT NOCOPY AHL_UMP_NONROUTINES_PVT.NonRoutine_Rec_Type
503 )
504 IS
505 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||'ahl_non_routine_pub'||'.'||'POPULATE_CREATE_NR_INPUT_REC';
506 l_unit_name VARCHAR2(80);
507
508 CURSOR get_instance_details(p_unit_name VARCHAR2)
509 IS
510 SELECT instance_number,
511 serial_number
512 FROM csi_item_instances csi,
513 ahl_unit_config_headers uch
514 WHERE uch.name = p_unit_name
515 AND uch.csi_item_instance_id = csi.instance_id;
516 BEGIN
517 -- Log API entry point
518 IF (l_log_procedure >= l_log_current_level) THEN
519 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.begin','At the start of PL SQL procedure ');
520 END IF;
521 -- populate the request date
522 x_nr_task_rec.INCIDENT_DATE := p_create_nr_input_rec.service_request_date;
523 -- populate the summary
524 x_nr_task_rec.PROBLEM_SUMMARY := p_create_nr_input_rec.summary;
525 -- populate the instance number
526 OPEN get_instance_details(p_create_nr_input_rec.unit_name);
527 FETCH get_instance_details INTO x_nr_task_rec.instance_number,x_nr_task_rec.serial_number;
528 IF get_instance_details%NOTFOUND THEN
529 CLOSE get_instance_details;
530 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_UNIT_INVALID');
531 FND_MESSAGE.Set_Token('NAME',p_create_nr_input_rec.unit_name);
532 FND_MSG_PUB.ADD;
533 RAISE FND_API.G_EXC_ERROR;
534 END IF;
535 CLOSE get_instance_details;
536
537 IF p_create_nr_input_rec.service_request_type <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.service_request_type <> NULL THEN
538 x_nr_task_rec.TYPE_NAME := p_create_nr_input_rec.service_request_type;
539 END IF;
540
541 IF p_create_nr_input_rec.service_request_status <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.service_request_type <> NULL THEN
542 x_nr_task_rec.status_name := p_create_nr_input_rec.service_request_status;
543 END IF;
544
545 IF p_create_nr_input_rec.problem_code <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.problem_code <> NULL THEN
546 x_nr_task_rec.problem_code := p_create_nr_input_rec.problem_code;
547 END IF;
548
549 IF p_create_nr_input_rec.severity_name <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.severity_name <> NULL THEN
550 x_nr_task_rec.severity_name := p_create_nr_input_rec.severity_name;
551 END IF;
552
553 IF p_create_nr_input_rec.contact_type <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.contact_type <> NULL THEN
554 x_nr_task_rec.contact_type := p_create_nr_input_rec.contact_type;
555 END IF;
556
557 IF p_create_nr_input_rec.contact_name <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.contact_name <> NULL THEN
558 x_nr_task_rec.contact_name := p_create_nr_input_rec.contact_name;
559 END IF;
560
561 IF p_create_nr_input_rec.resolution_code_meaning <> FND_API.G_MISS_CHAR AND p_create_nr_input_rec.resolution_code_meaning <> NULL THEN
562 x_nr_task_rec.resolution_meaning := p_create_nr_input_rec.resolution_code_meaning;
563 END IF;
564
565 -- Log API exit point
566 IF (l_log_procedure >= l_log_current_level) THEN
567 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.end','At the end of PL SQL procedure ');
568 END IF;
569 END POPULATE_CREATE_NR_INPUT_REC;
570
571
572 ------------------------------------------------------------------------------------------------------------------
573 -- Start of Comments --
574 -- Procedure name : POPULATE_CREATE_NR_OUTPUT_REC
575 -- Type : Private
576 -- Function : Populates the NR output record
577 -- Pre-reqs :
578 -- PROCESS Parameters:
579 -- x_create_nr_output_rec : Parameters to be passed to the caller
580 -- p_nr_task_rec : Record which contains params obtained after SR creation
581 -- End of Comments.
582
583 ------------------------------------------------------------------------------------------------------------------
584 PROCEDURE POPULATE_CREATE_NR_OUTPUT_REC( x_create_nr_output_rec OUT NOCOPY NON_ROUTINE_REC_TYPE,
585 p_nr_task_rec IN AHL_UMP_NONROUTINES_PVT.NonRoutine_Rec_Type)
586 IS
587 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||'ahl_non_routine_pub'||'.'||'populate_create_nr_output_rec';
588 BEGIN
589 -- Log API entry point
590 IF (l_log_procedure >= l_log_current_level) THEN
591 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.begin','At the start of PL SQL procedure ');
592 END IF;
593
594 -- Populate the OUT params
595 x_create_nr_output_rec.SERVICE_REQUEST_ID := p_nr_task_rec.INCIDENT_ID;
596 x_create_nr_output_rec.SUMMARY := p_nr_task_rec.PROBLEM_SUMMARY;
597 x_create_nr_output_rec.SERVICE_REQUEST_DATE := p_nr_task_rec.INCIDENT_DATE;
598
599 x_create_nr_output_rec.CONTACT_TYPE := p_nr_task_rec.CONTACT_TYPE;
600 x_create_nr_output_rec.CONTACT_ID := p_nr_task_rec.CONTACT_ID;
601 x_create_nr_output_rec.CONTACT_NAME := p_nr_task_rec.CONTACT_NAME;
602 x_create_nr_output_rec.SERVICE_REQUEST_STATUS := p_nr_task_rec.STATUS_NAME;
603 x_create_nr_output_rec.SEVERITY_ID := p_nr_task_rec.SEVERITY_ID;
604 x_create_nr_output_rec.SEVERITY_NAME := p_nr_task_rec.SEVERITY_NAME;
605 x_create_nr_output_rec.URGENCY_ID := p_nr_task_rec.URGENCY_ID;
606 x_create_nr_output_rec.URGENCY_NAME := p_nr_task_rec.URGENCY_NAME;
607 x_create_nr_output_rec.ATA_CODE := p_nr_task_rec.ATA_CODE;
608 x_create_nr_output_rec.UNIT_NAME := p_nr_task_rec.UNIT_NAME;
609 x_create_nr_output_rec.ITEM_NUMBER := p_nr_task_rec.ITEM_NUMBER;
610 x_create_nr_output_rec.SERIAL_NUMBER := p_nr_task_rec.SERIAL_NUMBER;
611 x_create_nr_output_rec.INSTANCE_NUMBER := p_nr_task_rec.INSTANCE_NUMBER;
612 x_create_nr_output_rec.PARTY_ID := p_nr_task_rec.CUSTOMER_ID;
613 x_create_nr_output_rec.PARTY_NAME := p_nr_task_rec.CUSTOMER_NAME;
614 x_create_nr_output_rec.CONTACT_TYPE_CODE := p_nr_task_rec.CONTACT_TYPE;
615
616 x_create_nr_output_rec.POSITION := NULL;
617 x_create_nr_output_rec.POSITION_ID := NULL;
618 x_create_nr_output_rec.LOT_NUMBER := NULL;
619
620 x_create_nr_output_rec.ESTIMATED_DURATION := NULL;
621 x_create_nr_output_rec.ESTIMATED_DURATION_UOM := NULL;
622 x_create_nr_output_rec.REPORT_BY_TYPE := NULL;
623 x_create_nr_output_rec.REPORT_TYPE_CODE := NULL;
624 x_create_nr_output_rec.REPORT_TYPE := NULL;
625
626
627 -- Populate the problem,resolution codes and meanings
628 BEGIN
629 SELECT CS.PROBLEM_CODE,FLVT.MEANING,CS.RESOLUTION_CODE ,FLVT1.MEANING
630 INTO x_create_nr_output_rec.problem_code,
631 x_create_nr_output_rec.problem_code_meaning,
632 x_create_nr_output_rec.resolution_code,
633 x_create_nr_output_rec.resolution_code_meaning
634 FROM cs_incidents_all_b CS,
635 FND_LOOKUP_VALUES FLVT,
636 FND_LOOKUP_VALUES FLVT1
637 WHERE FLVT.LOOKUP_TYPE(+) = 'REQUEST_PROBLEM_CODE'
638 AND FLVT.LOOKUP_CODE(+) = CS.PROBLEM_CODE
639 AND FLVT1.LOOKUP_TYPE(+) = 'REQUEST_RESOLUTION_CODE'
640 AND FLVT1.LOOKUP_CODE(+) = CS.RESOLUTION_CODE
641 AND FLVT.LANGUAGE(+) = userenv('LANG')
642 AND FLVT1.LANGUAGE(+) = userenv('LANG')
643 AND incident_id = p_nr_task_rec.INCIDENT_ID;
644 EXCEPTION
645 WHEN OTHERS THEN
646 NULL;
647 END;
648
649 BEGIN
650 SELECT sts.status_code,sts.name
651 INTO x_create_nr_output_rec.service_request_status_code,x_create_nr_output_rec.service_request_status
652 FROM cs_incidents_all_b CS,CS_INCIDENT_STATUSES_B sts
653 WHERE cs.incident_id = p_nr_task_rec.INCIDENT_ID
654 AND sts.INCIDENT_STATUS_ID = cs.incident_Status_id;
655 EXCEPTION
656 WHEN OTHERS THEN
657 NULL;
658 END;
659
660 BEGIN
661 SELECT name,incident_subtype
662 INTO x_create_nr_output_rec.SERVICE_REQUEST_TYPE,x_create_nr_output_rec.SERVICE_REQUEST_TYPE_CODE
663 FROM cs_incidents_all_b CS,cs_incident_types_vl
664 WHERE cs.incident_id = p_nr_task_rec.INCIDENT_ID
665 AND cs.incident_type_id = cs_incident_types_vl.incident_type_id;
666 EXCEPTION
667 WHEN OTHERS THEN
668 NULL;
669 END;
670
671 BEGIN
672 SELECT mtl.concatenated_segments,csi.serial_number
673 INTO x_create_nr_output_rec.item_number,x_create_nr_output_rec.serial_number
674 FROm csi_item_instances csi, mtl_system_items_kfv mtl,cs_incidents_all_b cs
675 WHERE csi.instance_id = cs.customer_product_id
676 and csi.inventory_item_id = mtl.inventory_item_id
677 and mtl.organization_id = csi.inv_master_organization_id
678 AND cs.incident_id = p_nr_task_rec.incident_id;
679 EXCEPTION
680 WHEN OTHERS THEN
681 NULL;
682 END;
683
684 BEGIN
685 SELECT ahl_util_uc_pkg.get_unit_name(cs.customer_product_id)
686 INTO x_create_nr_output_rec.unit_name
687 FROM cs_incidents_all_b cs
688 WHERE cs.incident_id = p_nr_task_rec.incident_id;
689 EXCEPTION
690 WHEN OTHERS THEN
691 NULL;
692 END;
693
694 BEGIN
695 SELECT name
696 INTO x_create_nr_output_rec.SEVERITY_NAME
697 FROM cs_incident_severities_vl
698 WHERE incident_severity_id = p_nr_task_rec.severity_id;
699 EXCEPTION
700 WHEN OTHERS THEN
701 NULL;
702 END;
703 -- Log API exit point
704 IF (l_log_procedure >= l_log_current_level) THEN
705 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.end','At the end of PL SQL procedure ');
706 END IF;
707 END POPULATE_CREATE_NR_OUTPUT_REC;
708
709 ------------------------------------------------------------------------------------------------------------------
710 -- Start of Comments --
711 -- Procedure name : POPULATE_CREATE_SR_OUTPUT_REC
712 -- Type : Private
713 -- Function : Populates the SR output record
714 -- Pre-reqs :
715 -- PROCESS Parameters:
716 -- x_create_nr_output_rec : Parameters to be passed to the caller
717 -- p_sr_task_rec : Record which contains params after SR creation
718 -- End of Comments.
719
720 ------------------------------------------------------------------------------------------------------------------
721 PROCEDURE POPULATE_CREATE_SR_OUTPUT_REC( x_create_nr_output_rec OUT NOCOPY NON_ROUTINE_REC_TYPE,
722 p_sr_task_rec IN AHL_PRD_NONROUTINE_PVT.SR_TASK_REC_TYPE)
723 IS
724 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||'ahl_non_routine_pub'||'.'||'populate_create_sr_output_rec';
725 BEGIN
726 -- Log API start point
727 IF (l_log_procedure >= l_log_current_level) THEN
728 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.begin','At the start of PL SQL procedure ');
729 END IF;
730
731 x_create_nr_output_rec.SERVICE_REQUEST_ID := p_sr_task_rec.Incident_id;
732 -- Populate the OUT params
733 x_create_nr_output_rec.SERVICE_REQUEST_TYPE := p_sr_task_rec.type_name;
734 x_create_nr_output_rec.SUMMARY := p_sr_task_rec.summary;
735 x_create_nr_output_rec.CONTACT_TYPE := p_sr_task_rec.contact_type;
736 x_create_nr_output_rec.CONTACT_ID := p_sr_task_rec.contact_id;
737 x_create_nr_output_rec.CONTACT_NAME := p_sr_task_rec.contact_name;
738 x_create_nr_output_rec.SERVICE_REQUEST_DATE := p_sr_task_rec.request_date;
739 x_create_nr_output_rec.SERVICE_REQUEST_STATUS := p_sr_task_rec.status_name;
740 x_create_nr_output_rec.SEVERITY_ID := p_sr_task_rec.severity_id;
741 x_create_nr_output_rec.URGENCY_ID := p_sr_task_rec.urgency_id;
742 x_create_nr_output_rec.URGENCY_NAME := p_sr_task_rec.urgency_name;
743 x_create_nr_output_rec.ATA_CODE := NULL;
744 x_create_nr_output_rec.INSTANCE_NUMBER := p_sr_task_rec.instance_number;
745 x_create_nr_output_rec.PARTY_ID := p_sr_task_rec.customer_id;
746 x_create_nr_output_rec.PARTY_NAME := p_sr_task_rec.customer_name;
747 x_create_nr_output_rec.CONTACT_TYPE_CODE := p_sr_task_rec.contact_type;
748
749 x_create_nr_output_rec.POSITION := NULL;
750 x_create_nr_output_rec.POSITION_ID := NULL;
751 x_create_nr_output_rec.LOT_NUMBER := NULL;
752
753 x_create_nr_output_rec.ESTIMATED_DURATION := p_sr_task_rec.Duration;
754 x_create_nr_output_rec.ESTIMATED_DURATION_UOM := NULL;
755 x_create_nr_output_rec.REPORT_BY_TYPE := NULL;
756 x_create_nr_output_rec.REPORT_TYPE_CODE := NULL;
757 x_create_nr_output_rec.REPORT_TYPE := NULL;
758
759 x_create_nr_output_rec.WORKORDER_ID := p_sr_task_rec.Nonroutine_wo_id;
760 x_create_nr_output_rec.VISIT_ID := p_sr_task_rec.visit_id;
761 x_create_nr_output_rec.VISIT_NUMBER := p_sr_task_rec.visit_number;
762 x_create_nr_output_rec.RELEASE_NON_ROUTINE_WORKORDER := p_sr_task_rec.wo_release_flag;
763
764 x_create_nr_output_rec.originator_workorder_id := p_sr_task_rec.ORIGINATING_WO_ID;
765
766 IF (l_log_statement >= l_log_current_level) THEN
767 fnd_log.string(fnd_log.level_statement,l_debug_module,'Populating workorder_number, p_sr_task_rec.Nonroutine_wo_id:' || p_sr_task_rec.Nonroutine_wo_id);
768 END IF;
769 BEGIN
770 SELECT workorder_name
771 INTO x_create_nr_output_rec.WORKORDER_NUMBER
772 FROM ahl_workorders
773 WHERE workorder_id = p_sr_task_rec.Nonroutine_wo_id;
774 EXCEPTION
775 WHEN OTHERS THEN
776 NULL;
777 END;
778
779 IF (l_log_statement >= l_log_current_level) THEN
780 fnd_log.string(fnd_log.level_statement,l_debug_module,'Populating Originator Details, WO id :' || p_sr_task_rec.ORIGINATING_WO_ID );
781 END IF;
782 BEGIN
783 SELECT workorder_name,vst.visit_id,visit_number,visit_task_id
784 INTO x_create_nr_output_rec.ORIGINATOR_WORKORDER_NUMBER,
785 x_create_nr_output_rec.ORIGINATOR_VISIT_ID,
786 x_create_nr_output_rec.ORIGINATOR_VISIT_NUMBER,
787 x_create_nr_output_rec.ORIGINATOR_TASK
788 FROM ahl_workorders,
789 ahl_visits_b vst
790 WHERE workorder_id = p_sr_task_rec.ORIGINATING_WO_ID
791 AND vst.visit_id = ahl_workorders.visit_id(+);
792 EXCEPTION
793 WHEN OTHERS THEN
794 NULL;
795 END;
796
797 IF (l_log_statement >= l_log_current_level) THEN
798 fnd_log.string(fnd_log.level_statement,l_debug_module,'Populating Prob, Res Codes, SERVICE_REQUEST_ID:' || x_create_nr_output_rec.service_request_id );
799 END IF;
800 -- Populate the problem,resolution codes and meanings
801 BEGIN
802 SELECT CS.PROBLEM_CODE,FLVT.MEANING,CS.RESOLUTION_CODE ,FLVT1.MEANING
803 INTO x_create_nr_output_rec.problem_code,
804 x_create_nr_output_rec.problem_code_meaning,
805 x_create_nr_output_rec.resolution_code,
806 x_create_nr_output_rec.resolution_code_meaning
807 FROM cs_incidents_all_b CS,
808 FND_LOOKUP_VALUES FLVT,
809 FND_LOOKUP_VALUES FLVT1
810 WHERE FLVT.LOOKUP_TYPE(+) = 'REQUEST_PROBLEM_CODE'
811 AND FLVT.LOOKUP_CODE(+) = CS.PROBLEM_CODE
812 AND FLVT1.LOOKUP_TYPE(+) = 'REQUEST_RESOLUTION_CODE'
813 AND FLVT1.LOOKUP_CODE(+) = CS.RESOLUTION_CODE
814 AND FLVT.LANGUAGE(+) = userenv('LANG')
815 AND FLVT1.LANGUAGE(+) = userenv('LANG')
816 AND incident_id = x_create_nr_output_rec.SERVICE_REQUEST_ID;
817 EXCEPTION
818 WHEN OTHERS THEN
819 NULL;
820 END;
821
822 IF (l_log_statement >= l_log_current_level) THEN
823 fnd_log.string(fnd_log.level_statement,l_debug_module,'Populating status_code, Status_id:' || p_sr_task_rec.Status_id);
824 END IF;
825 BEGIN
826 SELECT status_code
827 INTO x_create_nr_output_rec.SERVICE_REQUEST_STATUS_CODE
828 FROM CS_INCIDENT_STATUSES_B
829 WHERE INCIDENT_STATUS_ID = p_sr_task_rec.Status_id;
830 EXCEPTION
831 WHEN OTHERS THEN
832 NULL;
833 END;
834
835 IF (l_log_statement >= l_log_current_level) THEN
836 fnd_log.string(fnd_log.level_statement,l_debug_module,'Populating status_code, Status_id:' || p_sr_task_rec.Status_id);
837 END IF;
838 BEGIN
839 SELECT incident_subtype
840 INTO x_create_nr_output_rec.SERVICE_REQUEST_TYPE_CODE
841 FROM cs_incident_types_vl
842 WHERE incident_Type_id = p_sr_task_rec.type_id;
843 EXCEPTION
844 WHEN OTHERS THEN
845 NULL;
846 END;
847
848 IF (l_log_statement >= l_log_current_level) THEN
849 fnd_log.string(fnd_log.level_statement,l_debug_module,'Populating ITEM_NUMBER,SERIAL_NUMBER instance_id:' || p_sr_task_rec.instance_id);
850 END IF;
851 BEGIN
852 SELECT mtl.concatenated_segments,csi.serial_number
853 INTO x_create_nr_output_rec.ITEM_NUMBER,x_create_nr_output_rec.SERIAL_NUMBER
854 FROm csi_item_instances csi, mtl_system_items_kfv mtl
855 WHERE csi.instance_id = p_sr_task_rec.instance_id
856 and csi.inventory_item_id = mtl.inventory_item_id
857 and mtl.organization_id = csi.INV_MASTER_ORGANIZATION_ID;
858 EXCEPTION
859 WHEN OTHERS THEN
860 NULL;
861 END;
862
863 IF (l_log_statement >= l_log_current_level) THEN
864 fnd_log.string(fnd_log.level_statement,l_debug_module,'Populating unit_name, instance_id:' || p_sr_task_rec.instance_id);
865 END IF;
866 BEGIN
867 SELECT AHL_UTIL_UC_PKG.get_unit_name(p_sr_task_rec.instance_id)
868 INTO x_create_nr_output_rec.UNIT_NAME
869 FROM DUAL;
870 EXCEPTION
871 WHEN OTHERS THEN
872 NULL;
873 END;
874
875 IF (l_log_statement >= l_log_current_level) THEN
876 fnd_log.string(fnd_log.level_statement,l_debug_module,'Populating severity_name, severity_id:' || p_sr_task_rec.severity_id);
877 END IF;
878 BEGIN
879 SELECT name
880 INTO x_create_nr_output_rec.SEVERITY_NAME
881 FROM cs_incident_severities_vl
882 WHERE incident_severity_id = p_sr_task_rec.severity_id;
883 EXCEPTION
884 WHEN OTHERS THEN
885 NULL;
886 END;
887
888 IF (l_log_procedure >= l_log_current_level) THEN
889 fnd_log.string(fnd_log.level_procedure,l_debug_module,'After populating.......');
890 END IF;
891
892 -- Log API exit point
893 IF (l_log_procedure >= l_log_current_level) THEN
894 fnd_log.string(fnd_log.level_procedure,l_debug_module||'.end','At the end of PL SQL procedure ');
895 END IF;
896 END POPULATE_CREATE_SR_OUTPUT_REC;
897
898 END AHL_PRD_NONROUTINE_PUB;
899
[Home] [Help]