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