[Home] [Help]
PACKAGE BODY: APPS.AHL_PRD_WO_PUB
Source
1 PACKAGE BODY AHL_PRD_WO_PUB AS
2 /* $Header: AHLVWOSB.pls 120.2.12020000.2 2012/12/11 05:43:04 prakkum ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_PRD_WO_PUB';
5
6 G_BPEL_USER_ROLE_KEY VARCHAR2(240);
7
8 FUNCTION init_user_and_role(p_user_id IN VARCHAR2) RETURN VARCHAR2 IS
9 PRAGMA AUTONOMOUS_TRANSACTION;
10
11 CURSOR get_user_id_csr(p_user_id IN VARCHAR2) IS
12 select user_id from fnd_user where user_name = p_user_id;
13
14 l_user_id NUMBER;
15 l_resp_id NUMBER;
16
17 CURSOR get_resp_id_csr IS
18 select responsibility_id from fnd_responsibility_vl where responsibility_key = G_BPEL_USER_ROLE_KEY;
19 BEGIN
20 IF(p_user_id IS NOT NULL) THEN
21 OPEN get_user_id_csr(p_user_id);
22 FETCH get_user_id_csr INTO l_user_id;
23 IF get_user_id_csr%NOTFOUND THEN
24 FND_MESSAGE.set_name('AHL','AHL_PRD_INV_BPEL_USR');
25 FND_MESSAGE.SET_TOKEN('USER_NAME',p_user_id);
26 FND_MSG_PUB.ADD;
27 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
28 END IF;
29 CLOSE get_user_id_csr;
30
31 FND_GLOBAL.apps_initialize(l_user_id,null,867);
32
33 G_BPEL_USER_ROLE_KEY := FND_PROFILE.VALUE('AHL_BPEL_USER_ROLE');
34
35 OPEN get_resp_id_csr;
36 FETCH get_resp_id_csr INTO l_resp_id;
37 CLOSE get_resp_id_csr;
38
39 FND_GLOBAL.apps_initialize(l_user_id,l_resp_id,867);
40 mo_global.init('AHL');
41
42 END IF;
43 COMMIT;
44 return Fnd_Api.G_RET_STS_SUCCESS;
45
46 EXCEPTION
47 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
48 ROLLBACK;
49 return FND_API.G_RET_STS_UNEXP_ERROR;
50 WHEN OTHERS THEN
51 ROLLBACK;
52 return FND_API.G_RET_STS_UNEXP_ERROR;
53
54 END init_user_and_role;
55
56 FUNCTION get_workorder_id(p_WorkorderNumber IN VARCHAR2) RETURN NUMBER;
57
58 FUNCTION get_workorder_operation_id(p_WorkorderId IN NUMBER,p_operation_sequence IN NUMBER) RETURN NUMBER;
59
60 FUNCTION get_qa_sql_str(p_plan_id IN NUMBER) RETURN VARCHAR2;
61
62
63
64 PROCEDURE EXTRACT_SERIAL_NUMBER(p_reference IN OUT NOCOPY VARCHAR2,
65 x_serial_number OUT NOCOPY VARCHAR2);
66 FUNCTION IS_VALID_RESULT_ATTRIBUTE(p_CharId IN NUMBER, p_QA_PLAN IN QA_PLAN_REC_TYPE) RETURN VARCHAR2;
67
68 PROCEDURE get_workorder_details
69 (
70 p_api_version IN NUMBER := 1.0,
71 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
72 p_commit IN VARCHAR2 := FND_API.G_FALSE,
73 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
74 p_default IN VARCHAR2 := FND_API.G_FALSE,
75 p_module_type IN VARCHAR2,
76 p_userid IN VARCHAR2 := NULL,
77 p_WorkorderId IN NUMBER,
78 p_WorkorderNumber IN VARCHAR2,
79 x_return_status OUT NOCOPY VARCHAR2,
80 x_msg_count OUT NOCOPY NUMBER,
81 x_msg_data OUT NOCOPY VARCHAR2,
82 x_WO_DETAILS_REC OUT NOCOPY WO_DETAILS_REC_TYPE
83 ) IS
84
85 l_api_version CONSTANT NUMBER := 1.0;
86 l_api_name CONSTANT VARCHAR2(30) := 'get_workorder_details';
87
88 CURSOR get_wo_details_csr(p_WorkorderId IN NUMBER)
89 IS SELECT WO.WORKORDER_ID,
90 WO.OBJECT_VERSION_NUMBER,
91 WO.JOB_NUMBER,
92 WO.JOB_DESCRIPTION,
93 WO.ORGANIZATION_NAME,
94 WO.DEPARTMENT_NAME,
95 WO.JOB_STATUS_CODE,
96 WO.JOB_STATUS_MEANING,
97 WO.PRIORITY_MEANING,
98 WO.SCHEDULED_START_DATE,
99 WO.SCHEDULED_END_DATE,
100 WO.ACTUAL_START_DATE,
101 WO.ACTUAL_END_DATE,
102 WO.UNIT_NAME,
103 WO.WO_PART_NUMBER,
104 WO.SERIAL_NUMBER,
105 WO.VISIT_ID,
106 WO.VISIT_NUMBER,
107 WO.VISIT_NAME,
108 WO.VISIT_TASK_ID,
109 WO.VISIT_STATUS_CODE,
110 AMH.MR_HEADER_ID,
111 WO.VISIT_TASK_NUMBER,
112 AMH.TITLE MR_TITLE,
113 WO.MR_ROUTE_ID,
114 WO.ROUTE_ID,
115 AR.ROUTE_NO ROUTE_TITLE,
116 AR.ROUTE_NO ROUTE_NUMBER,
117 PAA.NAME PROJECT_NAME,
118 PAT.TASK_NAME PROJECT_TASK_NAME,
119 WO.UNIT_EFFECTIVITY_ID,
120 WO.LOT_NUMBER,
121 WO.UC_HEADER_ID,
122 WO.UNIT_QUARANTINE_FLAG,
123 WO.ORGANIZATION_ID,
124 WO.DEPARTMENT_ID,
125 WOS.PLAN_ID,
126 VST.start_date_time,
127 VST.close_date_time,
128 VTS.service_request_id,
129 VTS.service_request_id service_request_number,
130 WO.HOLD_REASON_CODE,
131 WO.HOLD_REASON
132 FROM AHL_WORKORDER_TASKS_V WO, PA_PROJECTS_ALL PAA, PA_TASKS PAT,
133 AHL_MR_ROUTES AMR, AHL_MR_HEADERS_B AMH, AHL_ROUTES_B AR, AHL_VISIT_TASKS_B VTS,
134 AHL_VISITS_B VST,AHL_WORKORDERS WOS
135 WHERE WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
136 AND WO.VISIT_ID=VST.VISIT_ID
137 AND WO.MR_ROUTE_ID=AMR.MR_ROUTE_ID (+)
138 AND AMR.MR_HEADER_ID=AMH.MR_HEADER_ID(+)
139 AND WO.ROUTE_ID=AR.ROUTE_ID (+)
140 AND VST.PROJECT_ID=PAA.PROJECT_ID (+)
141 AND VTS.PROJECT_TASK_ID=PAT.TASK_ID (+)
142 AND WO.WORKORDER_ID = WOS.workorder_id
143 AND WO.WORKORDER_ID = p_WorkorderId;
144
145 l_wo_details get_wo_details_csr%ROWTYPE;
146 l_workorder_id NUMBER;
147 l_model VARCHAR2(30) := 'Model' ;
148 l_ata_code VARCHAR2(30) := 'ATA';
149 l_tail_number VARCHAR2(30);
150 l_user_name VARCHAR2(40);
151 l_user_lang VARCHAR2(40);
152 l_doc_id VARCHAR2(80) :='docid';
153
154
155 BEGIN
156 x_return_status := init_user_and_role(p_userid);
157 IF(x_return_status <> Fnd_Api.G_RET_STS_SUCCESS)THEN
158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159 END IF;
160
161 -- Standard call to check for call compatibility
162 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
164 END IF;
165
166 IF FND_API.To_Boolean(p_init_msg_list) THEN
167 FND_MSG_PUB.Initialize;
168 END IF;
169
170
171
172
173 IF(p_WorkorderId IS NULL AND p_WorkorderNumber IS NULL)THEN
174 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
175 FND_MSG_PUB.ADD;
176 RAISE FND_API.G_EXC_ERROR;
177 ELSIF (p_WorkorderId IS NULL AND p_WorkorderNumber IS NOT NULL)THEN
178 l_workorder_id := get_workorder_id(p_WorkorderNumber);
179 ELSE
180 l_workorder_id := p_WorkorderId;
181 END IF;
182
183
184 OPEN get_wo_details_csr(l_workorder_id);
185 FETCH get_wo_details_csr INTO l_wo_details;
186 IF(get_wo_details_csr%NOTFOUND)THEN
187 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
188 FND_MSG_PUB.ADD;
189 CLOSE get_wo_details_csr;
190 RAISE FND_API.G_EXC_ERROR;
191 ELSE
192 x_WO_DETAILS_REC.WorkorderId := l_wo_details.WORKORDER_ID;
193 x_WO_DETAILS_REC.ObjectVersionNumber := l_wo_details.OBJECT_VERSION_NUMBER;
194 x_WO_DETAILS_REC.WorkorderNumber := l_wo_details.JOB_NUMBER;
195 x_WO_DETAILS_REC.Description := l_wo_details.JOB_DESCRIPTION;
196 x_WO_DETAILS_REC.StatusCode := l_wo_details.JOB_STATUS_CODE;
197 x_WO_DETAILS_REC.Status := l_wo_details.JOB_STATUS_MEANING;
198 x_WO_DETAILS_REC.Priority := l_wo_details.PRIORITY_MEANING;
199 x_WO_DETAILS_REC.OrganizationId := l_wo_details.ORGANIZATION_ID;
200 x_WO_DETAILS_REC.OrganizationName := l_wo_details.ORGANIZATION_NAME;
201 x_WO_DETAILS_REC.DepartmentId := l_wo_details.DEPARTMENT_ID;
202 x_WO_DETAILS_REC.DepartmentName := l_wo_details.DEPARTMENT_NAME;
203 x_WO_DETAILS_REC.ScheduledStartDate := l_wo_details.SCHEDULED_START_DATE;
204 x_WO_DETAILS_REC.ScheduledEndDate := l_wo_details.SCHEDULED_END_DATE;
205 x_WO_DETAILS_REC.ActualStartDate := l_wo_details.ACTUAL_START_DATE;
206 x_WO_DETAILS_REC.ActualEndDate := l_wo_details.ACTUAL_END_DATE;
207 x_WO_DETAILS_REC.UnitHeaderId := l_wo_details.UC_HEADER_ID;
208 x_WO_DETAILS_REC.UnitName := l_wo_details.UNIT_NAME;
209 x_WO_DETAILS_REC.WorkorderItemNumber := l_wo_details.WO_PART_NUMBER;
210 x_WO_DETAILS_REC.SerialNumber := l_wo_details.SERIAL_NUMBER;
211 x_WO_DETAILS_REC.LotNumber := l_wo_details.LOT_NUMBER;
212 x_WO_DETAILS_REC.VisitId := l_wo_details.VISIT_ID;
213 x_WO_DETAILS_REC.VisitNumber := l_wo_details.VISIT_NUMBER;
214 x_WO_DETAILS_REC.VisitTaskId := l_wo_details.VISIT_TASK_ID;
215 x_WO_DETAILS_REC.VisitTaskNumber := l_wo_details.VISIT_TASK_NUMBER;
216 x_WO_DETAILS_REC.VisitStatusCode := l_wo_details.VISIT_STATUS_CODE;
217 x_WO_DETAILS_REC.VisitStartDate := l_wo_details.start_date_time;
218 x_WO_DETAILS_REC.VisitEndDate := l_wo_details.close_date_time;
219 AHL_ENIGMA_UTIL_PKG.get_enigma_url_params
220 (
221 p_object_type => 'WO',
222 p_primary_object_id => l_wo_details.WORKORDER_ID,
223 p_secondary_object_id => l_wo_details.WORKORDER_ID,
224 x_model => l_model,
225 x_ata_code => l_ata_code,
226 x_tail_number => l_tail_number,
227 x_user_name => l_user_name,
228 x_user_lang => l_user_lang,
229 x_doc_id => l_doc_id
230 );
231 x_WO_DETAILS_REC.EnigmaDocumentID := l_doc_id;
232 x_WO_DETAILS_REC.EnigmaDocumentTitle := l_tail_number;
233 x_WO_DETAILS_REC.ATACode := l_ata_code;
234 x_WO_DETAILS_REC.Model := l_model;
235 x_WO_DETAILS_REC.RoutePublishingDate := SYSDATE;
236 x_WO_DETAILS_REC.MrHeaderId := l_wo_details.MR_HEADER_ID;
237 x_WO_DETAILS_REC.MrTitle := l_wo_details.MR_TITLE;
238 x_WO_DETAILS_REC.MrRouteId := l_wo_details.MR_ROUTE_ID;
239 x_WO_DETAILS_REC.RouteId := l_wo_details.ROUTE_ID;
240 x_WO_DETAILS_REC.RouteTitle := l_wo_details.ROUTE_TITLE;
241 x_WO_DETAILS_REC.RouteNumber := l_wo_details.ROUTE_NUMBER;
242 x_WO_DETAILS_REC.ProjectName := l_wo_details.PROJECT_NAME;
243 x_WO_DETAILS_REC.ProjectTaskName := l_wo_details.PROJECT_TASK_NAME;
244 x_WO_DETAILS_REC.UnitEffectivityId := l_wo_details.UNIT_EFFECTIVITY_ID;
245 x_WO_DETAILS_REC.NonRoutineId := l_wo_details.service_request_id;
246 x_WO_DETAILS_REC.NonRoutineNumber := to_char(l_wo_details.service_request_number);
247 x_WO_DETAILS_REC.HoldReasonCode := l_wo_details.HOLD_REASON_CODE;
248 x_WO_DETAILS_REC.HoldReason := l_wo_details.HOLD_REASON;
249 x_WO_DETAILS_REC.IsUnitQuarantined := l_wo_details.UNIT_QUARANTINE_FLAG;
250 x_WO_DETAILS_REC.IsCompleteEnabled := AHL_COMPLETIONS_PVT.Is_Complete_Enabled(l_wo_details.WORKORDER_ID, NULL, NULL, 'T');
251 FND_MSG_PUB.Initialize;
252 x_WO_DETAILS_REC.IsPartsChangeEnabled := AHL_PRD_UTIL_PKG.Is_PartChange_Enabled(l_wo_details.WORKORDER_ID,'T');
253 FND_MSG_PUB.Initialize;
254
255 IF(x_WO_DETAILS_REC.StatusCode NOT IN ('22','7','12','1','7','4','5') AND x_WO_DETAILS_REC.IsUnitQuarantined = 'F')THEN
256 x_WO_DETAILS_REC.IsNonRoutineCreationEnabled := 'T';
257 ELSE
258 x_WO_DETAILS_REC.IsNonRoutineCreationEnabled := 'F';
259 END IF;
260 IF(x_WO_DETAILS_REC.IsUnitQuarantined = 'T' OR x_WO_DETAILS_REC.StatusCode IN ('22','7','12','1','7'))THEN
261 x_WO_DETAILS_REC.IsUpdateEnabled := 'F';
262 ELSE
263 x_WO_DETAILS_REC.IsUpdateEnabled := 'T';
264 END IF;
265 x_WO_DETAILS_REC.IsResTxnEnabled := AHL_PRD_UTIL_PKG.Is_ResTxn_Allowed(l_wo_details.WORKORDER_ID,'T');
266 FND_MSG_PUB.Initialize;
267 IF(l_wo_details.PLAN_ID IS NULL)THEN
268 x_WO_DETAILS_REC.IsQualityEnabled := 'N';
269 /*ELSIF(x_WO_DETAILS_REC.StatusCode IN ('22','7','12','1','7','4','5') OR x_WO_DETAILS_REC.IsUnitQuarantined = 'T')THEN
270 x_WO_DETAILS_REC.IsQualityEnabled := 'F';*/
271 ELSE
272 x_WO_DETAILS_REC.IsQualityEnabled := 'T';
273 END IF;
274 END IF;
275 CLOSE get_wo_details_csr;
276
277 EXCEPTION
278 WHEN FND_API.G_EXC_ERROR THEN
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 x_msg_count := FND_MSG_PUB.count_msg;
281
282
283 x_msg_data := GET_MSG_DATA(x_msg_count);
284 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
286 x_msg_count := FND_MSG_PUB.count_msg;
287 x_msg_data := GET_MSG_DATA(x_msg_count);
288 WHEN OTHERS THEN
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
290
291 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
292 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
293 p_procedure_name => l_api_name,
294 p_error_text => SUBSTR(SQLERRM,1,500));
295 END IF;
296 x_msg_count := FND_MSG_PUB.count_msg;
297 x_msg_data := GET_MSG_DATA(x_msg_count);
298 END get_workorder_details;
299
300 PROCEDURE get_wo_operations_details
301 (
302 p_api_version IN NUMBER := 1.0,
303 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
304 p_commit IN VARCHAR2 := FND_API.G_FALSE,
305 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
306 p_default IN VARCHAR2 := FND_API.G_FALSE,
307 p_module_type IN VARCHAR2,
308 p_userid IN VARCHAR2 := NULL,
309 p_WorkorderId IN NUMBER,
310 p_WorkorderNumber IN VARCHAR2,
311 p_WoOperationId IN NUMBER,
312 p_OperationSequence IN NUMBER,
313 x_return_status OUT NOCOPY VARCHAR2,
314 x_msg_count OUT NOCOPY NUMBER,
315 x_msg_data OUT NOCOPY VARCHAR2,
316 x_Operations OUT NOCOPY OP_TBL_TYPE
317 ) IS
318
319 l_api_version CONSTANT NUMBER := 1.0;
320 l_api_name CONSTANT VARCHAR2(30) := 'get_wo_operations_details';
321
322 CURSOR get_operations_details_csr(p_WorkorderId IN NUMBER, p_WoOperationId IN NUMBER) IS
323 SELECT WORKORDER_OPERATION_ID,OBJECT_VERSION_NUMBER, OPERATION_SEQUENCE_NUM,DESCRIPTION,
324 WORKORDER_ID, DEPARTMENT_ID,
325 DEPARTMENT_NAME, OPERATION_ID, OPERATION_CODE, OPERATION_TYPE_CODE,
326 OPERATION_TYPE, STATUS_CODE, STATUS,
327 SCHEDULED_START_DATE, SCHEDULED_END_DATE,
328 ACTUAL_START_DATE, ACTUAL_END_DATE, PLAN_ID
329 FROM AHL_WORKORDER_OPERATIONS_V
330 WHERE WORKORDER_ID = p_WorkorderId
331 AND WORKORDER_OPERATION_ID = NVL(p_WoOperationId, WORKORDER_OPERATION_ID)
332 ORDER BY OPERATION_SEQUENCE_NUM;
333
334 l_workorder_id NUMBER;
335 l_workorder_operation_id NUMBER;
336 l_op_index NUMBER;
337
338 BEGIN
339
340 x_return_status := init_user_and_role(p_userid);
341 IF(x_return_status <> Fnd_Api.G_RET_STS_SUCCESS)THEN
342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
343 END IF;
344
345 x_Operations(0) := NULL;
346 -- Standard call to check for call compatibility
347 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
349 END IF;
350
351 IF FND_API.To_Boolean(p_init_msg_list) THEN
352 FND_MSG_PUB.Initialize;
353 END IF;
354 -- Check Error Message stack.
355 IF(p_WorkorderId IS NULL AND p_WorkorderNumber IS NULL)THEN
356 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
357 FND_MSG_PUB.ADD;
358 RAISE FND_API.G_EXC_ERROR;
359 ELSIF (p_WorkorderId IS NULL AND p_WorkorderNumber IS NOT NULL)THEN
360 l_workorder_id := get_workorder_id(p_WorkorderNumber);
361 ELSE
362 l_workorder_id := p_WorkorderId;
363 END IF;
364
365 x_msg_count := FND_MSG_PUB.count_msg;
366 IF x_msg_count > 0 THEN
367 RAISE FND_API.G_EXC_ERROR;
368 END IF;
369
370 IF(p_WoOperationId IS NULL AND p_OperationSequence IS NULL)THEN
371 NULL;
372 ELSIF (p_WoOperationId IS NULL AND p_OperationSequence IS NOT NULL)THEN
373 l_workorder_operation_id := get_workorder_operation_id(l_workorder_id, p_OperationSequence);
374 ELSE
375 l_workorder_operation_id := p_WoOperationId;
376 END IF;
377
378 x_msg_count := FND_MSG_PUB.count_msg;
379 IF x_msg_count > 0 THEN
380 RAISE FND_API.G_EXC_ERROR;
381 END IF;
382
383 l_op_index :=0;
384 FOR op_details IN get_operations_details_csr(l_workorder_id,l_workorder_operation_id) LOOP
385 x_Operations(l_op_index).WorkorderOperationId := op_details.WORKORDER_OPERATION_ID;
386 x_Operations(l_op_index).ObjectVersionNumber := op_details.OBJECT_VERSION_NUMBER;
387 x_Operations(l_op_index).OperationSequenceNumber := op_details.OPERATION_SEQUENCE_NUM;
388 x_Operations(l_op_index).WorkorderId := op_details.WORKORDER_ID;
389 x_Operations(l_op_index).OperationCode := op_details.OPERATION_CODE;
390 x_Operations(l_op_index).Description := op_details.DESCRIPTION;
391 x_Operations(l_op_index).StatusCode := op_details.STATUS_CODE;
392 x_Operations(l_op_index).Status := op_details.STATUS;
393 x_Operations(l_op_index).OperationTypeCode := op_details.OPERATION_TYPE_CODE;
394 x_Operations(l_op_index).OperationType := op_details.OPERATION_TYPE;
395 x_Operations(l_op_index).DepartmentId := op_details.DEPARTMENT_ID;
396 x_Operations(l_op_index).DepartmentName := op_details.DEPARTMENT_NAME;
397 x_Operations(l_op_index).ScheduledStartDate := op_details.SCHEDULED_START_DATE;
398 x_Operations(l_op_index).ScheduledEndDate := op_details.SCHEDULED_END_DATE;
399 x_Operations(l_op_index).ActualStartDate := op_details.ACTUAL_START_DATE;
400 x_Operations(l_op_index).ActualEndDate := op_details.ACTUAL_END_DATE;
401 x_Operations(l_op_index).IsUpdateEnabled := AHL_PRD_UTIL_PKG.Is_Op_Updatable(op_details.WORKORDER_ID, op_details.OPERATION_SEQUENCE_NUM);
402
403 x_Operations(l_op_index).IsQualityEnabled := 'N' ;
404 IF(op_details.PLAN_ID IS NULL ) THEN
405 x_Operations(l_op_index).IsQualityEnabled := 'N' ;
406 ELSIF(op_details.PLAN_ID IS NOT NULL AND x_Operations(l_op_index).IsUpdateEnabled = 'T')THEN
407 x_Operations(l_op_index).IsQualityEnabled := 'T' ;
408 ELSIF(op_details.PLAN_ID IS NOT NULL AND x_Operations(l_op_index).IsUpdateEnabled = 'F')THEN
409 x_Operations(l_op_index).IsQualityEnabled := 'F' ;
410 END IF;
411 l_op_index := l_op_index + 1;
412 END LOOP;
413
414 x_msg_count := FND_MSG_PUB.count_msg;
415 IF x_msg_count > 0 THEN
416 RAISE FND_API.G_EXC_ERROR;
417 END IF;
418
419 EXCEPTION
420 WHEN FND_API.G_EXC_ERROR THEN
421 x_return_status := FND_API.G_RET_STS_ERROR;
422 x_msg_count := FND_MSG_PUB.count_msg;
423
424
425 x_msg_data := GET_MSG_DATA(x_msg_count);
426 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428 x_msg_count := FND_MSG_PUB.count_msg;
429 x_msg_data := GET_MSG_DATA(x_msg_count);
430 WHEN OTHERS THEN
431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
432
433 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
434 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
435 p_procedure_name => l_api_name,
436 p_error_text => SUBSTR(SQLERRM,1,500));
437 END IF;
438 x_msg_count := FND_MSG_PUB.count_msg;
439 x_msg_data := GET_MSG_DATA(x_msg_count);
440 END get_wo_operations_details;
441
442 PROCEDURE get_wo_mtl_reqmts
443 (
444 p_api_version IN NUMBER := 1.0,
445 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
446 p_commit IN VARCHAR2 := FND_API.G_FALSE,
447 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
448 p_default IN VARCHAR2 := FND_API.G_FALSE,
449 p_module_type IN VARCHAR2,
450 p_userid IN VARCHAR2 := NULL,
451 p_WorkorderId IN NUMBER,
452 p_WorkorderNumber IN VARCHAR2,
453 p_WoOperationId IN NUMBER,
454 p_OperationSequence IN NUMBER,
455 x_return_status OUT NOCOPY VARCHAR2,
456 x_msg_count OUT NOCOPY NUMBER,
457 x_msg_data OUT NOCOPY VARCHAR2,
458 x_MaterialRequirementDetails OUT NOCOPY MTL_REQMTS_TBL_TYPE
459 ) IS
460
461 l_api_version CONSTANT NUMBER := 1.0;
462 l_api_name CONSTANT VARCHAR2(30) := 'get_wo_mtl_reqmts';
463
464 CURSOR get_wo_mtl_reqmts_csr(p_WorkorderId IN NUMBER, p_WoOperationId IN NUMBER) IS
465 SELECT ASML.operation_sequence, MSIK.concatenated_segments part_number,MSIK.description Part_Desc, MSIK.primary_unit_of_measure part_uom ,
466 WIRO.REQUIRED_QUANTITY required_quantity , WIRO.DATE_REQUIRED required_date,
467 asml.scheduled_quantity schedule_quantity,asml.scheduled_date schedule_date,
468 nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0) issued_quantity,
469 AWOS.workorder_id,
470 asml.inventory_item_id,
471 ASML.organization_id,
472 ASML.scheduled_material_id
473 from ahl_workorders AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK
474 WHERE AWOS.visit_task_id = ASML.visit_task_id
475 and ASML.inventory_item_id = MSIK.inventory_item_id
476 and ASML.organization_id = MSIK.organization_id
477 AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
478 AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
479 AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
480 AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID and asml.status IN ( 'ACTIVE', 'IN-SERVICE', 'HISTORY')
481 AND AWOS.WORKORDER_ID = p_WorkorderId
482 AND ASML.WORKORDER_OPERATION_ID = NVL(p_WoOperationId,ASML.WORKORDER_OPERATION_ID);
483
484 l_workorder_id NUMBER;
485 l_workorder_operation_id NUMBER;
486 l_mtl_index NUMBER;
487
488 BEGIN
489
490 x_return_status := init_user_and_role(p_userid);
491 IF(x_return_status <> Fnd_Api.G_RET_STS_SUCCESS)THEN
492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
493 END IF;
494
495 x_MaterialRequirementDetails(0) := NULL;
496
497 -- Standard call to check for call compatibility
498 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
499 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
500 END IF;
501
502 IF FND_API.To_Boolean(p_init_msg_list) THEN
503 FND_MSG_PUB.Initialize;
504 END IF;
505
506 -- Check Error Message stack.
507 IF(p_WorkorderId IS NULL AND p_WorkorderNumber IS NULL)THEN
508 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
509 FND_MSG_PUB.ADD;
510 RAISE FND_API.G_EXC_ERROR;
511 ELSIF (p_WorkorderId IS NULL AND p_WorkorderNumber IS NOT NULL)THEN
512 l_workorder_id := get_workorder_id(p_WorkorderNumber);
513 ELSE
514 l_workorder_id := p_WorkorderId;
515 END IF;
516
517 IF(p_WoOperationId IS NULL AND p_OperationSequence IS NULL)THEN
518 NULL;
519 ELSIF (p_WoOperationId IS NULL AND p_OperationSequence IS NOT NULL)THEN
520 l_workorder_operation_id := get_workorder_operation_id(l_workorder_id, p_OperationSequence);
521 ELSE
522 l_workorder_operation_id := p_WoOperationId;
523 END IF;
524
525 x_msg_count := FND_MSG_PUB.count_msg;
526 IF x_msg_count > 0 THEN
527 RAISE FND_API.G_EXC_ERROR;
528 END IF;
529
530 l_mtl_index :=0;
531 FOR mtl_details IN get_wo_mtl_reqmts_csr(l_workorder_id,l_workorder_operation_id) LOOP
532 x_MaterialRequirementDetails(l_mtl_index).ScheduledMaterialId := mtl_details.scheduled_material_id;
533 x_MaterialRequirementDetails(l_mtl_index).WorkorderId := mtl_details.workorder_id;
534 x_MaterialRequirementDetails(l_mtl_index).OperationSequenceNumber := mtl_details.operation_sequence;
535 x_MaterialRequirementDetails(l_mtl_index).InventoryItemId := mtl_details.inventory_item_id;
536 x_MaterialRequirementDetails(l_mtl_index).ItemNumber := mtl_details.part_number;
537 x_MaterialRequirementDetails(l_mtl_index).ItemDescription := mtl_details.Part_Desc;
538 x_MaterialRequirementDetails(l_mtl_index).RequiredQuantity := mtl_details.required_quantity;--mtl_details.schedule_quantity;
539 x_MaterialRequirementDetails(l_mtl_index).PartUOM := mtl_details.part_uom;
540 x_MaterialRequirementDetails(l_mtl_index).RequiredDate := mtl_details.required_date;
541 x_MaterialRequirementDetails(l_mtl_index).ScheduledQuantity := mtl_details.required_quantity;
542 x_MaterialRequirementDetails(l_mtl_index).ScheduledDate := mtl_details.schedule_date;
543 x_MaterialRequirementDetails(l_mtl_index).IssuedQuantity := mtl_details.issued_quantity;
544 l_mtl_index := l_mtl_index + 1;
545 END LOOP;
546
547 -- Check Error Message stack.
548 x_msg_count := FND_MSG_PUB.count_msg;
549 IF x_msg_count > 0 THEN
550 RAISE FND_API.G_EXC_ERROR;
551 END IF;
552
553 EXCEPTION
554 WHEN FND_API.G_EXC_ERROR THEN
555 x_return_status := FND_API.G_RET_STS_ERROR;
556 x_msg_count := FND_MSG_PUB.count_msg;
557
558
559 x_msg_data := GET_MSG_DATA(x_msg_count);
560 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
562 x_msg_count := FND_MSG_PUB.count_msg;
563 x_msg_data := GET_MSG_DATA(x_msg_count);
564 WHEN OTHERS THEN
565 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
566
567 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
568 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
569 p_procedure_name => l_api_name,
570 p_error_text => SUBSTR(SQLERRM,1,500));
571 END IF;
572 x_msg_count := FND_MSG_PUB.count_msg;
573 x_msg_data := GET_MSG_DATA(x_msg_count);
574 END get_wo_mtl_reqmts;
575
576 PROCEDURE get_wo_assoc_documents
577 (
578 p_api_version IN NUMBER := 1.0,
579 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
580 p_commit IN VARCHAR2 := FND_API.G_FALSE,
581 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
582 p_default IN VARCHAR2 := FND_API.G_FALSE,
583 p_module_type IN VARCHAR2,
584 p_userid IN VARCHAR2 := NULL,
585 p_WorkorderId IN NUMBER,
586 p_WorkorderNumber IN VARCHAR2,
587 x_return_status OUT NOCOPY VARCHAR2,
588 x_msg_count OUT NOCOPY NUMBER,
589 x_msg_data OUT NOCOPY VARCHAR2,
590 x_AssociatedDocuments OUT NOCOPY ASSOC_DOCS_TBL_TYPE
591 ) IS
592
593 l_api_version CONSTANT NUMBER := 1.0;
594 l_api_name CONSTANT VARCHAR2(30) := 'get_wo_assoc_documents';
595 l_workorder_id NUMBER;
596 l_doc_index NUMBER;
597
598 CURSOR get_wo_assoc_documents_csr(p_WorkorderId IN NUMBER)IS
599 SELECT DISTINCT
600 DOC.DOCUMENT_NO, DOC.DOCUMENT_TITLE, DOC.ASO_OBJECT_TYPE_DESC, DOC.REVISION_NO,
601 DOC.CHAPTER, DOC.SECTION, DOC.SUBJECT, DOC.PAGE, DOC.FIGURE, DOC.NOTE
602 FROM
603 AHL_WORKORDERS WO, AHL_DOCUMENT_ASSOS_V DOC
604 WHERE
605 WO.ROUTE_ID = DOC.ASO_OBJECT_ID
606 AND WO.WORKORDER_ID = p_WorkorderId
607 AND DOC.ASO_OBJECT_TYPE_CODE = 'ROUTE'
608 UNION ALL
609 -- OPERATION DOC ASSOCIATIONS
610 SELECT DISTINCT
611 DOC.DOCUMENT_NO, DOC.DOCUMENT_TITLE, DOC.ASO_OBJECT_TYPE_DESC, DOC.REVISION_NO,
612 DOC.CHAPTER, DOC.SECTION, DOC.SUBJECT, DOC.PAGE, DOC.FIGURE, DOC.NOTE
613 FROM
614 AHL_WORKORDER_OPERATIONS WOP, AHL_DOCUMENT_ASSOS_V DOC
615 WHERE
616 WOP.OPERATION_ID = DOC.ASO_OBJECT_ID
617 AND WOP.WORKORDER_ID = p_WorkorderId
618 AND DOC.ASO_OBJECT_TYPE_CODE = 'OPERATION'
619 UNION ALL
620 -- MR DOCUMENT ASSOCIATIONS
621 SELECT DISTINCT
622 DOC.DOCUMENT_NO, DOC.DOCUMENT_TITLE, DOC.ASO_OBJECT_TYPE_DESC, DOC.REVISION_NO,
623 DOC.CHAPTER, DOC.SECTION, DOC.SUBJECT, DOC.PAGE, DOC.FIGURE, DOC.NOTE
624 FROM
625 AHL_WORKORDERS WO, AHL_VISIT_TASKS_B VST, AHL_DOCUMENT_ASSOS_V DOC
626 WHERE
627 WO.VISIT_TASK_ID = VST.VISIT_TASK_ID
628 AND WO.WORKORDER_ID = p_WorkorderId
629 AND VST.MR_ID = DOC.ASO_OBJECT_ID
630 AND DOC.ASO_OBJECT_TYPE_CODE = 'MR'
631 UNION ALL
632 -- MC DOCUMENT ASSOCIATIONS
633 SELECT DISTINCT
634 DOC.DOCUMENT_NO, DOC.DOCUMENT_TITLE, DOC.ASO_OBJECT_TYPE_DESC, DOC.REVISION_NO,
635 DOC.CHAPTER, DOC.SECTION, DOC.SUBJECT, DOC.PAGE, DOC.FIGURE, DOC.NOTE
636 FROM
637 AHL_WORKORDERS WO, CSI_II_RELATIONSHIPS CSI, AHL_VISIT_TASKS_B VTS,
638 AHL_DOCUMENT_ASSOS_V DOC
639 WHERE
640 WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID
641 AND WO.WORKORDER_ID = p_WorkorderId
642 AND VTS.INSTANCE_ID = CSI.SUBJECT_ID
643 AND VTS.INSTANCE_ID = CSI.SUBJECT_ID
644 AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
645 AND (SYSDATE BETWEEN NVL(CSI.ACTIVE_START_DATE, SYSDATE) AND NVL(CSI.ACTIVE_END_DATE, SYSDATE))
646 AND TO_NUMBER(CSI.POSITION_REFERENCE) = DOC.ASO_OBJECT_ID
647 AND DOC.ASO_OBJECT_TYPE_CODE = 'MC'
648 UNION ALL
649 -- PC DOC ASSOCIATIONS
650 SELECT DISTINCT
651 DOC.DOCUMENT_NO, DOC.DOCUMENT_TITLE, DOC.ASO_OBJECT_TYPE_DESC, DOC.REVISION_NO,
652 DOC.CHAPTER, DOC.SECTION, DOC.SUBJECT, DOC.PAGE, DOC.FIGURE, DOC.NOTE
653 FROM
654 AHL_WORKORDERS WO, AHL_PC_ASSOCIATIONS PCA, AHL_VISIT_TASKS_B VTS,
655 AHL_DOCUMENT_ASSOS_V DOC
656 WHERE
657 WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
658 AND WO.WORKORDER_ID = p_WorkorderId
659 AND AHL_UTIL_UC_PKG.GET_UC_HEADER_ID(VTS.INSTANCE_ID) = PCA.UNIT_ITEM_ID
660 and pca.pc_node_id = doc.aso_object_id
661 AND DOC.ASO_OBJECT_TYPE_CODE = 'PC';
662
663 BEGIN
664
665 x_return_status := init_user_and_role(p_userid);
666 IF(x_return_status <> Fnd_Api.G_RET_STS_SUCCESS)THEN
667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
668 END IF;
669
670 x_AssociatedDocuments(0) := NULL;
671
672 -- Standard call to check for call compatibility
673 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
675 END IF;
676
677 IF FND_API.To_Boolean(p_init_msg_list) THEN
678 FND_MSG_PUB.Initialize;
679 END IF;
680
681 -- Check Error Message stack.
682 IF(p_WorkorderId IS NULL AND p_WorkorderNumber IS NULL)THEN
683 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
684 FND_MSG_PUB.ADD;
685 RAISE FND_API.G_EXC_ERROR;
686 ELSIF (p_WorkorderId IS NULL AND p_WorkorderNumber IS NOT NULL)THEN
687 l_workorder_id := get_workorder_id(p_WorkorderNumber);
688 ELSE
689 l_workorder_id := p_WorkorderId;
690 END IF;
691
692 x_msg_count := FND_MSG_PUB.count_msg;
693 IF x_msg_count > 0 THEN
694 RAISE FND_API.G_EXC_ERROR;
695 END IF;
696
697 l_doc_index := 0;
698 FOR assoc_docs IN get_wo_assoc_documents_csr(l_workorder_id ) LOOP
699 x_AssociatedDocuments(l_doc_index).DocumentNumber := assoc_docs.DOCUMENT_NO;
700 x_AssociatedDocuments(l_doc_index).DocumentTitle := assoc_docs.DOCUMENT_TITLE;
701 x_AssociatedDocuments(l_doc_index).AsoObjectTypeDesc := assoc_docs.ASO_OBJECT_TYPE_DESC;
702 x_AssociatedDocuments(l_doc_index).RevisionNumber := assoc_docs.REVISION_NO;
703 x_AssociatedDocuments(l_doc_index).Chapter := assoc_docs.CHAPTER;
704 x_AssociatedDocuments(l_doc_index).Section := assoc_docs.SECTION;
705 x_AssociatedDocuments(l_doc_index).Subject := assoc_docs.SUBJECT;
706 x_AssociatedDocuments(l_doc_index).Page := assoc_docs.PAGE;
707 x_AssociatedDocuments(l_doc_index).Figure := assoc_docs.FIGURE;
708 x_AssociatedDocuments(l_doc_index).Note := assoc_docs.NOTE;
709 l_doc_index := l_doc_index +1;
710 END LOOP;
711
712 -- Check Error Message stack.
713 x_msg_count := FND_MSG_PUB.count_msg;
714 IF x_msg_count > 0 THEN
715 RAISE FND_API.G_EXC_ERROR;
716 END IF;
717
718 EXCEPTION
719 WHEN FND_API.G_EXC_ERROR THEN
720 x_return_status := FND_API.G_RET_STS_ERROR;
721 x_msg_count := FND_MSG_PUB.count_msg;
722
723
724 x_msg_data := GET_MSG_DATA(x_msg_count);
725 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
726 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
727 x_msg_count := FND_MSG_PUB.count_msg;
728 x_msg_data := GET_MSG_DATA(x_msg_count);
729 WHEN OTHERS THEN
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
731
732 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
733 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
734 p_procedure_name => l_api_name,
735 p_error_text => SUBSTR(SQLERRM,1,500));
736 END IF;
737 x_msg_count := FND_MSG_PUB.count_msg;
738 x_msg_data := GET_MSG_DATA(x_msg_count);
739 END get_wo_assoc_documents;
740
741 PROCEDURE get_wo_turnover_notes
742 (
743 p_api_version IN NUMBER := 1.0,
744 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
745 p_commit IN VARCHAR2 := FND_API.G_FALSE,
746 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
747 p_default IN VARCHAR2 := FND_API.G_FALSE,
748 p_module_type IN VARCHAR2,
749 p_userid IN VARCHAR2 := NULL,
750 p_WorkorderId IN NUMBER,
751 p_WorkorderNumber IN VARCHAR2,
752 x_return_status OUT NOCOPY VARCHAR2,
753 x_msg_count OUT NOCOPY NUMBER,
754 x_msg_data OUT NOCOPY VARCHAR2,
755 x_TurnoverNotes OUT NOCOPY TURNOVER_NOTES_TBL_TYPE
756 ) IS
757
758 l_api_version CONSTANT NUMBER := 1.0;
759 l_api_name CONSTANT VARCHAR2(30) := 'get_wo_turnover_notes';
760 l_notes_index NUMBER;
761 l_workorder_id NUMBER;
762
763 CURSOR get_wo_turnover_notes_csr(p_WorkorderId IN NUMBER) IS
764 Select jtf_note_id,
765 source_object_id,
766 source_object_code,
767 entered_date,
768 fu.employee_id entered_by,
769 (Select DISTINCT PF.full_name from mtl_employees_current_view PF where pf.employee_id =fu.employee_id) entered_by_name,
770 notes note
771 from jtf_notes_vl JTF,fnd_user fu
772 where source_object_code = 'AHL_WO_TURNOVER_NOTES'
773 and fu.user_id = JTF.entered_by
774 and source_object_id = p_WorkorderId ORDER BY entered_date DESC;
775
776 BEGIN
777
778 x_return_status := init_user_and_role(p_userid);
779 IF(x_return_status <> Fnd_Api.G_RET_STS_SUCCESS)THEN
780 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
781 END IF;
782
783 x_TurnoverNotes(0) := NULL;
784
785 -- Standard call to check for call compatibility
786 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
788 END IF;
789
790 IF FND_API.To_Boolean(p_init_msg_list) THEN
791 FND_MSG_PUB.Initialize;
792 END IF;
793
794 -- Check Error Message stack.
795 IF(p_WorkorderId IS NULL AND p_WorkorderNumber IS NULL)THEN
796 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
797 FND_MSG_PUB.ADD;
798 RAISE FND_API.G_EXC_ERROR;
799 ELSIF (p_WorkorderId IS NULL AND p_WorkorderNumber IS NOT NULL)THEN
800 l_workorder_id := get_workorder_id(p_WorkorderNumber);
801 ELSE
802 l_workorder_id := p_WorkorderId;
803 END IF;
804
805 x_msg_count := FND_MSG_PUB.count_msg;
806 IF x_msg_count > 0 THEN
807 RAISE FND_API.G_EXC_ERROR;
808 END IF;
809
810 l_notes_index := 0;
811 FOR notes IN get_wo_turnover_notes_csr(l_workorder_id ) LOOP
812 x_TurnoverNotes(l_notes_index).JtfNoteId := notes.jtf_note_id ;
813 x_TurnoverNotes(l_notes_index).SourceObjectId := notes.source_object_id ;
814 x_TurnoverNotes(l_notes_index).SourceObjectCode := notes.source_object_code ;
815 x_TurnoverNotes(l_notes_index).EnteredDate := notes.entered_date ;
816 x_TurnoverNotes(l_notes_index).EnteredBy := notes.entered_by ;
817 x_TurnoverNotes(l_notes_index).EnteredByName := notes.entered_by_name ;
818 x_TurnoverNotes(l_notes_index).Notes := notes.note ;
819 l_notes_index := l_notes_index +1;
820 END LOOP;
821
822 -- Check Error Message stack.
823 x_msg_count := FND_MSG_PUB.count_msg;
824 IF x_msg_count > 0 THEN
825 RAISE FND_API.G_EXC_ERROR;
826 END IF;
827 EXCEPTION
828 WHEN FND_API.G_EXC_ERROR THEN
829 x_return_status := FND_API.G_RET_STS_ERROR;
830 x_msg_count := FND_MSG_PUB.count_msg;
831
832
833 x_msg_data := GET_MSG_DATA(x_msg_count);
834 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
836 x_msg_count := FND_MSG_PUB.count_msg;
837 x_msg_data := GET_MSG_DATA(x_msg_count);
838 WHEN OTHERS THEN
839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
840
841 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
842 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
843 p_procedure_name => l_api_name,
844 p_error_text => SUBSTR(SQLERRM,1,500));
845 END IF;
846 x_msg_count := FND_MSG_PUB.count_msg;
847 x_msg_data := GET_MSG_DATA(x_msg_count);
848 END get_wo_turnover_notes;
849
850 PROCEDURE get_wo_res_txns
851 (
852 p_api_version IN NUMBER := 1.0,
853 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
854 p_commit IN VARCHAR2 := FND_API.G_FALSE,
855 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
856 p_default IN VARCHAR2 := FND_API.G_FALSE,
857 p_module_type IN VARCHAR2,
858 p_userid IN VARCHAR2 := NULL,
859 p_WorkorderId IN NUMBER,
860 p_WorkorderNumber IN VARCHAR2,
861 p_WoOperationId IN NUMBER,
862 p_OperationSequence IN NUMBER,
863 x_return_status OUT NOCOPY VARCHAR2,
864 x_msg_count OUT NOCOPY NUMBER,
865 x_msg_data OUT NOCOPY VARCHAR2,
866 x_DefaultResourceTransactions OUT NOCOPY RES_TXNS_TBL_TYPE,
867 x_ResourceTransactions OUT NOCOPY RES_TXNS_TBL_TYPE
868 ) IS
869 l_api_version CONSTANT NUMBER := 1.0;
870 l_api_name CONSTANT VARCHAR2(30) := 'get_wo_res_txns';
871 l_restxns_index NUMBER;
872 l_workorder_id NUMBER;
873 l_operation_sequence NUMBER;
874
875 CURSOR get_wo_res_txns_csr(p_WorkorderId IN NUMBER, p_OperationSequence IN NUMBER) IS
876 SELECT WIP.TRANSACTION_ID, WIP.WORKORDER_ID, WIP.JOB_NUMBER,
877 WIP.OPERATION_SEQ_NUM, WIP.RESOURCE_SEQ_NUM, WIP.EMPLOYEE_ID, WIP.EMPLOYEE_NUMBER,
878 WIP.FULL_NAME, WIP.RESOURCE_CODE, WIP.DESCRIPTION, WIP.RESOURCE_ID, WIP.DEPARTMENT_ID,
879 WIP.DEPT_DESCRIPTION, WIP.QUANTITY, WIP.USAGE_RATE_OR_AMOUNT, WIP.PRIMARY_UOM,
880 WIP.UOM_MEANING, WIP.ACTIVITY_ID, WIP.ACTIVITY, WIP.REASON_ID, WIP.REASON_NAME,
881 WIP.REFERENCE, WIP.TRANSACTION_DATE,
882 WIP.TRANSACTION_STATUS, MFGL.MEANING RESOURCE_TYPE,
883 BR.RESOURCE_TYPE RESOURCE_TYPE_CODE, NULL SERIAL_NUMBER
884 FROM AHL_WIP_RESOURCE_TXNS_V WIP,BOM_RESOURCES BR, MFG_LOOKUPS MFGL
885 WHERE WORKORDER_ID = p_WorkorderId
886 AND OPERATION_SEQ_NUM = NVL(p_OperationSequence,OPERATION_SEQ_NUM )
887 AND BR.resource_id = WIP.resource_id
888 AND MFGL.LOOKUP_TYPE(+) = 'BOM_RESOURCE_TYPE'
889 AND MFGL.LOOKUP_CODE(+) = BR.RESOURCE_TYPE;
890
891 CURSOR get_operation_sequence_csr(p_WoOperationId IN NUMBER)IS
892 SELECT OPERATION_SEQUENCE_NUM FROM ahl_workorder_operations
893 WHERE WORKORDER_OPERATION_ID = p_WoOperationId;
894
895 CURSOR get_operation_sequences_csr(p_WorkorderId IN NUMBER, p_OperationSequence IN NUMBER)
896 IS
897 SELECT OPERATION_SEQUENCE_NUM FROM ahl_workorder_operations
898 WHERE WORKORDER_ID = p_WorkorderId
899 AND OPERATION_SEQUENCE_NUM = NVL(p_OperationSequence, OPERATION_SEQUENCE_NUM);
900
901 l_PRD_RESOURCE_TXNS_TBL AHL_PRD_RESOURCE_TRANX_PVT.PRD_RESOURCE_TXNS_TBL;
902 BEGIN
903 x_return_status := init_user_and_role(p_userid);
904 IF(x_return_status <> Fnd_Api.G_RET_STS_SUCCESS)THEN
905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
906 END IF;
907
908 x_ResourceTransactions(0) := NULL;
909 x_DefaultResourceTransactions(0) := NULL;
910
911
912 -- Standard call to check for call compatibility
913 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
915 END IF;
916
917 IF FND_API.To_Boolean(p_init_msg_list) THEN
918 FND_MSG_PUB.Initialize;
919 END IF;
920
921 -- Check Error Message stack.
922 IF(p_WorkorderId IS NULL AND p_WorkorderNumber IS NULL)THEN
923 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
924 FND_MSG_PUB.ADD;
925 RAISE FND_API.G_EXC_ERROR;
926 ELSIF (p_WorkorderId IS NULL AND p_WorkorderNumber IS NOT NULL)THEN
927 l_workorder_id := get_workorder_id(p_WorkorderNumber);
928 ELSE
929 l_workorder_id := p_WorkorderId;
930 END IF;
931
932 x_msg_count := FND_MSG_PUB.count_msg;
933 IF x_msg_count > 0 THEN
934 RAISE FND_API.G_EXC_ERROR;
935 END IF;
936
937 IF(p_WoOperationId IS NOT NULL)THEN
938 OPEN get_operation_sequence_csr(p_WoOperationId);
939 FETCH get_operation_sequence_csr INTO l_operation_sequence;
940 IF(get_operation_sequence_csr%NOTFOUND)THEN
941 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
942 FND_MSG_PUB.ADD;
943 RAISE FND_API.G_EXC_ERROR;
944 END IF;
945 CLOSE get_operation_sequence_csr;
946 ELSIF (p_WoOperationId IS NULL AND p_OperationSequence IS NOT NULL)THEN
947 l_operation_sequence := p_OperationSequence;
948 END IF;
949 -- Check Error Message stack.
950 x_msg_count := FND_MSG_PUB.count_msg;
951 IF x_msg_count > 0 THEN
952 RAISE FND_API.G_EXC_ERROR;
953 END IF;
954
955
956 l_restxns_index := 0;
957 FOR res_txns IN get_wo_res_txns_csr(l_workorder_id, l_operation_sequence)LOOP
958 x_ResourceTransactions(l_restxns_index).TransactionId := res_txns.TRANSACTION_ID ;
959 x_ResourceTransactions(l_restxns_index).WorkorderId := res_txns.WORKORDER_ID ;
960 x_ResourceTransactions(l_restxns_index).OperationSequenceNumber := res_txns.OPERATION_SEQ_NUM ;
961 x_ResourceTransactions(l_restxns_index).ResourceSequenceNumber := res_txns.RESOURCE_SEQ_NUM ;
962 x_ResourceTransactions(l_restxns_index).ResourceId := res_txns.RESOURCE_ID ;
963 x_ResourceTransactions(l_restxns_index).ResourceCode := res_txns.RESOURCE_CODE ;
964 x_ResourceTransactions(l_restxns_index).ResourceDescription := res_txns.DESCRIPTION ;
965 x_ResourceTransactions(l_restxns_index).ResourceType := res_txns.RESOURCE_TYPE ;
966 x_ResourceTransactions(l_restxns_index).ResourceTypeCode := res_txns.RESOURCE_TYPE_CODE ;
967 x_ResourceTransactions(l_restxns_index).EmployeeId := res_txns.EMPLOYEE_ID ;
968 x_ResourceTransactions(l_restxns_index).EmployeeNumber := res_txns.EMPLOYEE_NUMBER ;
969 x_ResourceTransactions(l_restxns_index).EmployeeName := res_txns.FULL_NAME ;
970 x_ResourceTransactions(l_restxns_index).SerialNumber := res_txns.SERIAL_NUMBER ;
971 x_ResourceTransactions(l_restxns_index).StartTime := res_txns.TRANSACTION_DATE ;
972 x_ResourceTransactions(l_restxns_index).EndTime := NULL ;
973 x_ResourceTransactions(l_restxns_index).Quantity := res_txns.QUANTITY ;
974 x_ResourceTransactions(l_restxns_index).UOMCode := res_txns.PRIMARY_UOM ;
975 x_ResourceTransactions(l_restxns_index).UOM := res_txns.UOM_MEANING ;
976 x_ResourceTransactions(l_restxns_index).UsageRateOrAmount := res_txns.USAGE_RATE_OR_AMOUNT ;
977 x_ResourceTransactions(l_restxns_index).ActivityId := res_txns.ACTIVITY_ID ;
978 x_ResourceTransactions(l_restxns_index).Activity := res_txns.ACTIVITY ;
979 x_ResourceTransactions(l_restxns_index).ReasonId := res_txns.REASON_ID ;
980 x_ResourceTransactions(l_restxns_index).Reason := res_txns.REASON_NAME ;
981 x_ResourceTransactions(l_restxns_index).Reference := res_txns.REFERENCE ;
982 x_ResourceTransactions(l_restxns_index).TransactionDate := res_txns.TRANSACTION_DATE ;
983 x_ResourceTransactions(l_restxns_index).TransactionStatus := res_txns.TRANSACTION_STATUS ;
984 IF (x_ResourceTransactions(l_restxns_index).Reference IS NOT NULL AND
985 x_ResourceTransactions(l_restxns_index).ResourceTypeCode <> 2) THEN
986 EXTRACT_SERIAL_NUMBER(
987 p_reference => x_ResourceTransactions(l_restxns_index).Reference,
988 x_serial_number => x_ResourceTransactions(l_restxns_index).SerialNumber);
989 END IF;
990 l_restxns_index := l_restxns_index + 1;
991 END LOOP;
992
993 l_restxns_index := 0;
994 FOR res_txns_defaults IN get_operation_sequences_csr(l_workorder_id, l_operation_sequence)LOOP
995 AHL_PRD_RESOURCE_TRANX_PVT.Get_Resource_Txn_Defaults
996 (
997 p_api_version => 1.0,
998 p_init_msg_list => FND_API.G_TRUE,
999 p_module_type => NULL,
1000 x_return_status => x_return_status,
1001 x_msg_count => x_msg_count,
1002 x_msg_data => x_msg_data,
1003 p_employee_id => FND_GLOBAL.employee_id,
1004 p_workorder_id => l_workorder_id,
1005 p_operation_seq_num => res_txns_defaults.OPERATION_SEQUENCE_NUM,
1006 p_function_name => 'AHL_PRD_TECH_MYWO',
1007 x_resource_txn_tbl => l_PRD_RESOURCE_TXNS_TBL
1008 );
1009 IF(l_PRD_RESOURCE_TXNS_TBL IS NOT NULL AND l_PRD_RESOURCE_TXNS_TBL.COUNT > 0) THEN
1010 FOR i IN l_PRD_RESOURCE_TXNS_TBL.FIRST..l_PRD_RESOURCE_TXNS_TBL.LAST LOOP
1011 x_DefaultResourceTransactions(l_restxns_index).WorkorderId := l_workorder_id ;
1012 x_DefaultResourceTransactions(l_restxns_index).OperationSequenceNumber := res_txns_defaults.OPERATION_SEQUENCE_NUM ;
1013 x_DefaultResourceTransactions(l_restxns_index).ResourceId := l_PRD_RESOURCE_TXNS_TBL(i).RESOURCE_ID ;
1014 x_DefaultResourceTransactions(l_restxns_index).ResourceCode := l_PRD_RESOURCE_TXNS_TBL(i).resource_name ;
1015 x_DefaultResourceTransactions(l_restxns_index).ResourceType := l_PRD_RESOURCE_TXNS_TBL(i).RESOURCE_TYPE_NAME ;
1016 x_DefaultResourceTransactions(l_restxns_index).ResourceTypeCode := l_PRD_RESOURCE_TXNS_TBL(i).RESOURCE_TYPE_CODE ;
1017 x_DefaultResourceTransactions(l_restxns_index).EmployeeId := l_PRD_RESOURCE_TXNS_TBL(i).person_id ;
1018 x_DefaultResourceTransactions(l_restxns_index).EmployeeNumber := l_PRD_RESOURCE_TXNS_TBL(i).employee_num ;
1019 x_DefaultResourceTransactions(l_restxns_index).EmployeeName := l_PRD_RESOURCE_TXNS_TBL(i).employee_name ;
1020 x_DefaultResourceTransactions(l_restxns_index).SerialNumber := l_PRD_RESOURCE_TXNS_TBL(i).SERIAL_NUMBER ;
1021 x_DefaultResourceTransactions(l_restxns_index).EndTime := SYSDATE ;
1022 x_DefaultResourceTransactions(l_restxns_index).Quantity := l_PRD_RESOURCE_TXNS_TBL(i).QTY ;
1023 x_DefaultResourceTransactions(l_restxns_index).UOMCode := l_PRD_RESOURCE_TXNS_TBL(i).UOM_CODE ;
1024 x_DefaultResourceTransactions(l_restxns_index).UOM := l_PRD_RESOURCE_TXNS_TBL(i).UOM_MEANING ;
1025 l_restxns_index := l_restxns_index + 1;
1026 END LOOP;
1027 END IF;
1028 END LOOP;
1029
1030 -- Check Error Message stack.
1031 x_msg_count := FND_MSG_PUB.count_msg;
1032 IF x_msg_count > 0 THEN
1033 RAISE FND_API.G_EXC_ERROR;
1034 END IF;
1035 x_return_status := FND_API.G_RET_STS_SUCCESS;
1036
1037 EXCEPTION
1038 WHEN FND_API.G_EXC_ERROR THEN
1039 x_return_status := FND_API.G_RET_STS_ERROR;
1040 x_msg_count := FND_MSG_PUB.count_msg;
1041
1042
1043 x_msg_data := GET_MSG_DATA(x_msg_count);
1044 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1046 x_msg_count := FND_MSG_PUB.count_msg;
1047 x_msg_data := GET_MSG_DATA(x_msg_count);
1048 WHEN OTHERS THEN
1049 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1050
1051 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1052 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1053 p_procedure_name => l_api_name,
1054 p_error_text => SUBSTR(SQLERRM,1,500));
1055 END IF;
1056 x_msg_count := FND_MSG_PUB.count_msg;
1057 x_msg_data := GET_MSG_DATA(x_msg_count);
1058 END get_wo_res_txns;
1059
1060 PROCEDURE get_qa_plan_results
1061 (
1062 p_api_version IN NUMBER := 1.0,
1063 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1064 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1065 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1066 p_default IN VARCHAR2 := FND_API.G_FALSE,
1067 p_module_type IN VARCHAR2,
1068 p_userid IN VARCHAR2 := NULL,
1069 p_WorkorderId IN NUMBER,
1070 p_WorkorderNumber IN VARCHAR2,
1071 p_WoOperationId IN NUMBER,
1072 p_OperationSequence IN NUMBER,
1073 x_return_status OUT NOCOPY VARCHAR2,
1074 x_msg_count OUT NOCOPY NUMBER,
1075 x_msg_data OUT NOCOPY VARCHAR2,
1076 x_QaPlan OUT NOCOPY QA_PLAN_REC_TYPE,
1077 x_QaResults OUT NOCOPY QA_RESULTS_REC_TYPE
1078 ) IS
1079
1080 l_api_version CONSTANT NUMBER := 1.0;
1081 l_api_name CONSTANT VARCHAR2(30) := 'get_qa_plan_results';
1082 l_workorder_id NUMBER;
1083 l_workorder_operation_id NUMBER;
1084
1085 CURSOR get_wo_plan_csr(p_WorkorderId IN NUMBER)IS
1086 Select plan_id,collection_id from ahl_search_workorders_v
1087 WHERE workorder_id = p_WorkorderId;
1088
1089 CURSOR get_op_plan_csr(p_WoOperationId IN NUMBER)IS
1090 Select plan_id,collection_id from ahl_workorder_operations_v
1091 WHERE workorder_operation_id = p_WoOperationId;
1092
1093
1094
1095 l_plan_id NUMBER;
1096 l_collection_id NUMBER;
1097 l_attribute_index NUMBER;
1098
1099 CURSOR get_plan_attributes_csr(p_plan_id IN NUMBER) IS
1100 SELECT char_id , prompt_sequence
1101 , prompt, enabled_flag , default_value
1102 , default_value_id , result_column_name
1103 , DECODE( NVL( sql_string_flag, 'N' ), 'N', DECODE( hardcoded_column, NULL, 'N', DECODE( QA_SS_LOV_API.get_lov_sql( plan_id, char_id, organization_id, null, null, null, null, null ), NULL, 'N', 'Y' ) ), sql_string_flag ) sql_string_flag
1104 , values_exist_flag, displayed_flag
1105 , char_name , datatype , display_length, hardcoded_column
1106 , developer_name , mandatory_flag
1107 FROM QA_PLAN_CHARS_V WHERE plan_id = p_plan_id ORDER BY prompt_sequence;
1108
1109 CURSOR get_plan_csr(p_plan_id IN NUMBER) IS
1110 SELECT plan_id , organization_id, name
1111 , description FROM QA_PLANS_V
1112 WHERE plan_id = p_plan_id;
1113
1114 CURSOR get_op_default_values_csr(p_WoOperationId IN NUMBER)IS
1115 SELECT WO.JOB_NUMBER,
1116 AMH.TITLE MR_TITLE,
1117 WOP.OPERATION_SEQUENCE_NUM,
1118 WOP.STATUS_CODE,
1119 WO.JOB_STATUS_CODE,
1120 WO.WO_PART_NUMBER,
1121 WO.ITEM_INSTANCE_NUMBER,
1122 WO.LOT_NUMBER,
1123 WO.SERIAL_NUMBER
1124 FROM AHL_WORKORDER_TASKS_V WO, AHL_WORKORDER_OPERATIONS WOP,
1125 AHL_MR_ROUTES AMR, AHL_MR_HEADERS_B AMH
1126 WHERE WO.MR_ROUTE_ID=AMR.MR_ROUTE_ID (+)
1127 AND AMR.MR_HEADER_ID=AMH.MR_HEADER_ID(+)
1128 AND WO.WORKORDER_ID = WOP.WORKORDER_ID
1129 AND WOP.workorder_operation_id = p_WoOperationId;
1130
1131 op_defaults get_op_default_values_csr%ROWTYPE;
1132
1133 CURSOR get_wo_default_values_csr(p_WorkorderId IN NUMBER)IS
1134 SELECT WO.JOB_NUMBER,
1135 AMH.TITLE MR_TITLE,
1136 WO.JOB_STATUS_CODE,
1137 WO.WO_PART_NUMBER,
1138 WO.ITEM_INSTANCE_NUMBER,
1139 WO.LOT_NUMBER,
1140 WO.SERIAL_NUMBER
1141 FROM AHL_WORKORDER_TASKS_V WO,
1142 AHL_MR_ROUTES AMR, AHL_MR_HEADERS_B AMH
1143 WHERE WO.MR_ROUTE_ID=AMR.MR_ROUTE_ID (+)
1144 AND AMR.MR_HEADER_ID=AMH.MR_HEADER_ID(+)
1145 AND WO.WORKORDER_ID = p_WorkorderId;
1146
1147 wo_defaults get_wo_default_values_csr%ROWTYPE;
1148
1149
1150
1151 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
1152 -- dynamic cursor
1153 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
1154 l_results_sql_str VARCHAR2(6000);
1155 source_cursor INTEGER;
1156 dummy INTEGER;
1157 l_occurence NUMBER;
1158 l_temp VARCHAR2(4000);
1159 l_result_row_index NUMBER;
1160 l_result_column_index NUMBER;
1161
1162
1163 BEGIN
1164 -- Initialize API return status to success
1165
1166 x_return_status := init_user_and_role(p_userid);
1167 IF(x_return_status <> Fnd_Api.G_RET_STS_SUCCESS)THEN
1168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1169 END IF;
1170
1171
1172 x_QaPlan.QA_PLAN_ATR_TBL(0) := NULL;
1173 x_QaResults.QA_RESULT_TBL(0).QA_PLAN_ATRVAL_TBL(0) := NULL;
1174
1175
1176 -- Standard call to check for call compatibility
1177 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
1178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1179 END IF;
1180
1181 IF FND_API.To_Boolean(p_init_msg_list) THEN
1182 FND_MSG_PUB.Initialize;
1183 END IF;
1184
1185 -- Check Error Message stack.
1186 IF(p_WorkorderId IS NULL AND p_WorkorderNumber IS NULL)THEN
1187 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
1188 FND_MSG_PUB.ADD;
1189 RAISE FND_API.G_EXC_ERROR;
1190 ELSIF (p_WorkorderId IS NULL AND p_WorkorderNumber IS NOT NULL)THEN
1191 l_workorder_id := get_workorder_id(p_WorkorderNumber);
1192 ELSE
1193 l_workorder_id := p_WorkorderId;
1194 END IF;
1195
1196 x_msg_count := FND_MSG_PUB.count_msg;
1197 IF x_msg_count > 0 THEN
1198 RAISE FND_API.G_EXC_ERROR;
1199 END IF;
1200
1201 IF(p_WoOperationId IS NULL AND p_OperationSequence IS NULL)THEN
1202 l_workorder_operation_id := NULL;
1203 ELSIF (p_WoOperationId IS NULL AND p_OperationSequence IS NOT NULL)THEN
1204 l_workorder_operation_id := get_workorder_operation_id(l_workorder_id, p_OperationSequence);
1205 ELSE
1206 l_workorder_operation_id := p_WoOperationId;
1207 END IF;
1208 --l_workorder_operation_id := NULL;
1209 x_msg_count := FND_MSG_PUB.count_msg;
1210 IF x_msg_count > 0 THEN
1211 RAISE FND_API.G_EXC_ERROR;
1212 END IF;
1213
1214 IF(l_workorder_operation_id IS NOT NULL)THEN
1215 OPEN get_op_plan_csr(l_workorder_operation_id);
1216 FETCH get_op_plan_csr INTO l_plan_id, l_collection_id;
1217 CLOSE get_op_plan_csr;
1218 OPEN get_op_default_values_csr(l_workorder_operation_id);
1219 FETCH get_op_default_values_csr INTO op_defaults;
1220 CLOSE get_op_default_values_csr;
1221 ELSIF(l_workorder_id IS NOT NULL)THEN
1222 OPEN get_wo_plan_csr(l_workorder_id);
1223 FETCH get_wo_plan_csr INTO l_plan_id, l_collection_id;
1224 CLOSE get_wo_plan_csr;
1225 OPEN get_wo_default_values_csr(l_workorder_id);
1226 FETCH get_wo_default_values_csr INTO wo_defaults;
1227 CLOSE get_wo_default_values_csr;
1228 END IF;
1229 --fetch plan
1230 IF(l_plan_id IS NOT NULL)THEN
1231 OPEN get_plan_csr(l_plan_id);
1232 FETCH get_plan_csr INTO x_QaPlan.PlanId,x_QaPlan.OrganizationId ,
1233 x_QaPlan.PlanName, x_QaPlan.PlanDescription;
1234 CLOSE get_plan_csr;
1235 l_attribute_index :=0;
1236 FOR qa_plan_attributes IN get_plan_attributes_csr(l_plan_id)LOOP
1237 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId := qa_plan_attributes.char_id;
1238 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).PromptSequence := qa_plan_attributes.prompt_sequence;
1239 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Prompt := qa_plan_attributes.prompt;
1240 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := qa_plan_attributes.default_value;
1241 IF(qa_plan_attributes.displayed_flag = 1) THEN
1242 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).IsDisplayed := 'T';
1243 ELSE
1244 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).IsDisplayed := 'F';
1245 END IF;
1246 IF(qa_plan_attributes.mandatory_flag = 1) THEN
1247 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).IsMandatory := 'T';
1248 ELSE
1249 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).IsMandatory := 'F';
1250 END IF;
1251 IF(qa_plan_attributes.enabled_flag = 1) THEN
1252 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).IsReadOnly := 'F';
1253 ELSE
1254 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).IsReadOnly := 'T';
1255 END IF;
1256 IF(qa_plan_attributes.sql_string_flag = 'Y'
1257 AND x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).IsReadOnly = 'F'
1258 AND x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).IsDisplayed = 'T')THEN
1259 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).IsListOfValue := 'T';
1260 ELSE
1261 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).IsListOfValue := 'F';
1262 END IF;
1263 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DisplayLength := qa_plan_attributes.display_length;
1264 IF(qa_plan_attributes.datatype = 2)THEN
1265 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DataType := 'integer';
1266 ELSIF(qa_plan_attributes.datatype = 3)THEN
1267 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DataType := 'date';
1268 ELSIF(qa_plan_attributes.datatype = 6)THEN
1269 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DataType := 'dateTime';
1270 ELSE
1271 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DataType := 'string';
1272 END IF;
1273
1274 IF(l_workorder_operation_id IS NOT NULL)THEN
1275 IF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 165)THEN
1276 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := op_defaults.JOB_NUMBER;
1277 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 44)THEN
1278 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := op_defaults.MR_TITLE;
1279 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 199)THEN
1280 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := op_defaults.OPERATION_SEQUENCE_NUM;
1281 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 98)THEN
1282 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := op_defaults.JOB_STATUS_CODE;
1283 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 125)THEN
1284 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := op_defaults.STATUS_CODE;
1285 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 10)THEN
1286 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := op_defaults.WO_PART_NUMBER;
1287 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 30)THEN
1288 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := op_defaults.ITEM_INSTANCE_NUMBER;
1289 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 84)THEN
1290 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := op_defaults.LOT_NUMBER;
1291 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 147)THEN
1292 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := op_defaults.SERIAL_NUMBER;
1293 END IF;
1294 ELSE
1295 IF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 165)THEN
1296 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := wo_defaults.JOB_NUMBER;
1297 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 44)THEN
1298 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := wo_defaults.MR_TITLE;
1299 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 98)THEN
1300 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := wo_defaults.JOB_STATUS_CODE;
1301 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 10)THEN
1302 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := wo_defaults.WO_PART_NUMBER;
1303 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 30)THEN
1304 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := wo_defaults.ITEM_INSTANCE_NUMBER;
1305 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 84)THEN
1306 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := wo_defaults.LOT_NUMBER;
1307 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).CharId = 147)THEN
1308 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DefaultValue := wo_defaults.SERIAL_NUMBER;
1309 END IF;
1310 END IF;
1311 l_attribute_index := l_attribute_index+1;
1312 END LOOP;
1313 END IF;
1314 --fetch results
1315 IF(l_plan_id IS NOT NULL AND l_collection_id IS NOT NULL)THEN
1316 x_QaResults.PlanId := l_plan_id;
1317 x_QaResults.CollectionId := l_collection_id;
1318 l_results_sql_str := get_qa_sql_str(l_plan_id);
1319 source_cursor := DBMS_sql.open_cursor;
1320 DBMS_SQL.parse(source_cursor,l_results_sql_str,DBMS_SQL.native);
1321 DBMS_SQL.BIND_VARIABLE(source_cursor, ':1', l_collection_id);
1322 DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, l_occurence);
1323
1324 FOR i IN x_QaPlan.QA_PLAN_ATR_TBL.FIRST..x_QaPlan.QA_PLAN_ATR_TBL.LAST LOOP
1325 DBMS_SQL.DEFINE_COLUMN(source_cursor, i+2, l_temp,4000);
1326 END LOOP;
1327 dummy := DBMS_SQL.EXECUTE(source_cursor);
1328 l_result_row_index := 0;
1329 LOOP
1330 IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
1331
1332 DBMS_SQL.COLUMN_VALUE(source_cursor, 1, l_occurence);
1333 x_QaResults.QA_RESULT_TBL(l_result_row_index).Occurence := l_occurence;
1334
1335 FOR i IN x_QaPlan.QA_PLAN_ATR_TBL.FIRST..x_QaPlan.QA_PLAN_ATR_TBL.LAST LOOP
1336 DBMS_SQL.COLUMN_VALUE(source_cursor, i+2, l_temp);
1337 x_QaResults.QA_RESULT_TBL(l_result_row_index).QA_PLAN_ATRVAL_TBL(i).CharId
1338 := x_QaPlan.QA_PLAN_ATR_TBL(i).CharId;
1339 x_QaResults.QA_RESULT_TBL(l_result_row_index).QA_PLAN_ATRVAL_TBL(i).AttributeValue
1340 := l_temp;
1341
1342 END LOOP;
1343 ELSE
1344 EXIT;
1345 END IF;
1346 l_result_row_index := l_result_row_index + 1;
1347 END LOOP;
1348 END IF;
1349 -- Check Error Message stack.
1350 x_msg_count := FND_MSG_PUB.count_msg;
1351
1352 EXCEPTION
1353 WHEN FND_API.G_EXC_ERROR THEN
1354 x_return_status := FND_API.G_RET_STS_ERROR;
1355 x_msg_count := FND_MSG_PUB.count_msg;
1356
1357
1358 x_msg_data := GET_MSG_DATA(x_msg_count);
1359 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1361 x_msg_count := FND_MSG_PUB.count_msg;
1362 x_msg_data := GET_MSG_DATA(x_msg_count);
1363 WHEN OTHERS THEN
1364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1365
1366 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1367 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1368 p_procedure_name => l_api_name,
1369 p_error_text => SUBSTR(SQLERRM,1,500));
1370 END IF;
1371 x_msg_count := FND_MSG_PUB.count_msg;
1372 x_msg_data := GET_MSG_DATA(x_msg_count);
1373 END get_qa_plan_results;
1374
1375 FUNCTION get_workorder_id(p_WorkorderNumber IN VARCHAR2) RETURN NUMBER
1376 IS
1377
1378 l_workorder_id NUMBER;
1379
1380 CURSOR get_workorder_id_csr(p_workorder_number IN VARCHAR2) IS
1381 Select WORKORDER_ID FROM AHL_WORKORDERS
1382 WHERE WORKORDER_NAME = p_workorder_number;
1383
1384 BEGIN
1385 OPEN get_workorder_id_csr(p_WorkorderNumber);
1386 FETCH get_workorder_id_csr INTO l_workorder_id;
1387 IF(get_workorder_id_csr%NOTFOUND)THEN
1388 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
1389 FND_MSG_PUB.ADD;
1390 CLOSE get_workorder_id_csr;
1391 RAISE FND_API.G_EXC_ERROR;
1392 END IF;
1393 CLOSE get_workorder_id_csr;
1394 RETURN l_workorder_id;
1395 END get_workorder_id;
1396
1397 FUNCTION get_workorder_operation_id(p_WorkorderId IN NUMBER,p_operation_sequence IN NUMBER) RETURN NUMBER
1398 IS
1399
1400 l_workorder_operation_id NUMBER;
1401
1402 CURSOR get_workorder_operation_id_csr(p_WorkorderId IN NUMBER,p_operation_sequence IN NUMBER) IS
1403 Select WORKORDER_OPERATION_ID FROM AHL_WORKORDER_OPERATIONS
1404 WHERE WORKORDER_ID = p_WorkorderId AND
1405 OPERATION_SEQUENCE_NUM = p_operation_sequence;
1406
1407 BEGIN
1408 OPEN get_workorder_operation_id_csr(p_WorkorderId, p_operation_sequence);
1409 FETCH get_workorder_operation_id_csr INTO l_workorder_operation_id;
1410 IF(get_workorder_operation_id_csr%NOTFOUND)THEN
1411 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
1412 FND_MSG_PUB.ADD;
1413 CLOSE get_workorder_operation_id_csr;
1414 RAISE FND_API.G_EXC_ERROR;
1415 END IF;
1416 CLOSE get_workorder_operation_id_csr;
1417 RETURN l_workorder_operation_id;
1418 END get_workorder_operation_id;
1419
1420 PROCEDURE EXTRACT_SERIAL_NUMBER(p_reference IN OUT NOCOPY VARCHAR2,
1421 x_serial_number OUT NOCOPY VARCHAR2) IS
1422 BEGIN
1423 NULL;
1424 END EXTRACT_SERIAL_NUMBER;
1425
1426 FUNCTION get_qa_sql_str(p_plan_id IN NUMBER) RETURN VARCHAR2 IS
1427
1428 CURSOR get_plan_attributes_csr(p_plan_id IN NUMBER) IS
1429 SELECT char_id , prompt_sequence
1430 , prompt, enabled_flag , default_value
1431 , default_value_id , result_column_name
1432 , DECODE( NVL( sql_string_flag, 'N' ), 'N', DECODE( hardcoded_column, NULL, 'N', DECODE( QA_SS_LOV_API.get_lov_sql( plan_id, char_id, organization_id, null, null, null, null, null ), NULL, 'N', 'Y' ) ), sql_string_flag ) sql_string_flag
1433 , values_exist_flag, displayed_flag
1434 , char_name , datatype , display_length, hardcoded_column
1435 , developer_name , mandatory_flag
1436 FROM QA_PLAN_CHARS_V WHERE plan_id = p_plan_id ORDER BY prompt_sequence;
1437
1438 l_sql_string VARCHAR2(6000);
1439 l_result_column VARCHAR2(240);
1440 l_result_column_name VARCHAR2(240);
1441
1442
1443 BEGIN
1444 l_sql_string := 'SELECT RESULTS.OCCURRENCE ';
1445 FOR qa_plan_attributes IN get_plan_attributes_csr(p_plan_id)LOOP
1446 IF(qa_plan_attributes.char_id = 10)THEN -- ITEM NUMBER
1447 l_result_column := 'CONCATENATED_SEGMENTS';
1448 ELSIF (qa_plan_attributes.char_id = 15)THEN -- LOCATOR
1449 l_result_column := 'CONCATENATED_SEGMENTS';
1450 ELSE
1451 IF ( qa_plan_attributes.hardcoded_column IS NOT NULL ) THEN
1452 l_result_column := qa_plan_attributes.developer_name;
1453 ELSE
1454 l_result_column := qa_plan_attributes.result_column_name;
1455 END IF;
1456 END IF;
1457
1458 IF ( qa_plan_attributes.char_id = 10 )THEN
1459 l_result_column_name := 'ITEM.' || l_result_column || ' ';
1460 l_result_column_name := '(Select ' ||l_result_column_name || ' FROM MTL_SYSTEM_ITEMS_KFV ITEM '
1461 || ' WHERE ITEM.inventory_item_id = RESULTS.'|| qa_plan_attributes.hardcoded_column
1462 || ' AND ITEM.organization_id = RESULTS.organization_id ) ' || l_result_column;
1463
1464 ELSIF ( qa_plan_attributes.char_id = 15) THEN
1465 l_result_column_name := 'LOCATOR.' || l_result_column || ' ';
1466
1467 l_result_column_name := '(Select ' || l_result_column_name ||' FROM MTL_ITEM_LOCATIONS_KFV LOCATOR '
1468 || ' WHERE LOCATOR.inventory_location_id = RESULTS.' || qa_plan_attributes.hardcoded_column
1469 + ' AND LOCATOR.organization_id = RESULTS.organization_id ) ' || l_result_column;
1470
1471 ELSE
1472
1473 l_result_column_name := 'to_char(RESULTS.' || l_result_column || ')';
1474 END IF;
1475 l_sql_string := l_sql_string || ' , ' || l_result_column_name;
1476 END LOOP;
1477
1478 l_sql_string := l_sql_string || ' FROM QA_RESULTS_V RESULTS WHERE COLLECTION_ID = :1 ORDER BY RESULTS.OCCURRENCE DESC';
1479 RETURN l_sql_string;
1480 END get_qa_sql_str;
1481
1482 PROCEDURE process_turnover_notes(
1483 p_WO_DETAILS_REC IN WO_DETAILS_REC_TYPE,
1484 p_TurnoverNotes IN TURNOVER_NOTES_TBL_TYPE,
1485 x_return_status OUT NOCOPY VARCHAR2,
1486 x_msg_count OUT NOCOPY NUMBER,
1487 x_msg_data OUT NOCOPY VARCHAR2
1488 )IS
1489 l_trunover_notes_tbl AHL_PRD_WORKORDER_PVT.turnover_notes_tbl_type;
1490 j INTEGER;
1491 BEGIN
1492 x_return_status := FND_API.G_RET_STS_SUCCESS;
1493 IF(p_TurnoverNotes IS NULL OR p_TurnoverNotes.COUNT < 1)THEN
1494 RETURN;
1495 END IF;
1496 j :=0;
1497 FOR i IN p_TurnoverNotes.FIRST..p_TurnoverNotes.LAST LOOP
1498 IF(p_TurnoverNotes(i).Notes IS NOT NULL AND p_TurnoverNotes(i).JtfNoteId IS NULL)THEN
1499 l_trunover_notes_tbl(j).Notes := p_TurnoverNotes(i).Notes;
1500 l_trunover_notes_tbl(j).source_object_id := p_WO_DETAILS_REC.WorkorderId;
1501 l_trunover_notes_tbl(j).source_object_code := 'AHL_WO_TURNOVER_NOTES';
1502 l_trunover_notes_tbl(j).Entered_Date := p_TurnoverNotes(i).EnteredDate;
1503 --l_trunover_notes_tbl(j).employee_id := p_TurnoverNotes(i).EnteredBy;
1504 l_trunover_notes_tbl(j).employee_name := p_TurnoverNotes(i).EnteredByName;
1505 l_trunover_notes_tbl(j).org_id := p_WO_DETAILS_REC.OrganizationId;
1506 j := j+1;
1507 END IF;
1508 END LOOP;
1509
1510 IF(l_trunover_notes_tbl IS NOT NULL AND l_trunover_notes_tbl.COUNT > 0)THEN
1511 IF(p_WO_DETAILS_REC.IsUpdateEnabled <> 'T')THEN
1512 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_TRNNTREC_NALWD');
1513 FND_MSG_PUB.ADD;
1514 RAISE FND_API.G_EXC_ERROR;
1515 END IF;
1516
1517 AHL_PRD_WORKORDER_PVT.INSERT_TURNOVER_NOTES
1518 (
1519 p_api_version => 1.0 ,
1520 p_init_msg_list => FND_API.G_TRUE,
1521 p_commit => FND_API.G_FALSE,
1522 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1523 p_default => FND_API.G_TRUE,
1524 p_module_type => 'OAF',
1525 x_return_status => x_return_status,
1526 x_msg_count => x_msg_count,
1527 x_msg_data => x_msg_data,
1528 p_trunover_notes_tbl => l_trunover_notes_tbl
1529 );
1530
1531 END IF;
1532
1533
1534 END process_turnover_notes;
1535
1536 PROCEDURE process_wo_details
1537 (
1538 p_WO_DETAILS_REC IN WO_DETAILS_REC_TYPE,
1539 p_CURR_WO_DETAILS_REC IN WO_DETAILS_REC_TYPE,
1540 x_return_status OUT NOCOPY VARCHAR2,
1541 x_msg_count OUT NOCOPY NUMBER,
1542 x_msg_data OUT NOCOPY VARCHAR2
1543 )IS
1544
1545 l_prd_workorder_rec AHL_PRD_WORKORDER_PVT.prd_workorder_rec;
1546 l_prd_workoper_tbl AHL_PRD_WORKORDER_PVT.prd_workoper_tbl;
1547
1548 CURSOR get_status_csr(status VARCHAR)
1549 IS
1550 SELECT lookup_code
1551 FROM fnd_Lookups
1552 WHERE lookup_type = 'AHL_JOB_STATUS'
1553 AND meaning = status;
1554
1555 CURSOR validate_status_csr(c_status_new VARCHAR, c_status_old VARCHAR)
1556 IS
1557 SELECT 1
1558 FROM AHL_STATUS_ORDER_RULES
1559 WHERE system_status_type = 'AHL_JOB_STATUS'
1560 AND CURRENT_STATUS_CODE = c_status_old
1561 AND NEXT_STATUS_CODE = c_status_new;
1562
1563 l_status_code VARCHAR2(30);
1564 l_dummy_ctr NUMBER;
1565
1566 BEGIN
1567 x_return_status := FND_API.G_RET_STS_SUCCESS;
1568
1569 IF(p_CURR_WO_DETAILS_REC.IsUpdateEnabled <> 'T')THEN
1570 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_UPD_NALWD');
1571 FND_MSG_PUB.ADD;
1572 RAISE FND_API.G_EXC_ERROR;
1573 END IF;
1574
1575 l_status_code := p_WO_DETAILS_REC.StatusCode;
1576
1577 IF p_WO_DETAILS_REC.StatusCode IS NULL AND p_WO_DETAILS_REC.Status IS NOT NULL THEN
1578 OPEN get_status_csr(p_WO_DETAILS_REC.Status);
1579 FETCH get_status_csr INTO l_status_code;
1580 IF get_status_csr%NOTFOUND THEN
1581 FND_MESSAGE.SET_NAME('AHL','AHL_PP_JOB_INV_STATUS_JSP');
1582 FND_MSG_PUB.ADD;
1583 CLOSE get_status_csr;
1584 RAISE FND_API.G_EXC_ERROR;
1585 ELSE
1586 CLOSE get_status_csr;
1587 END IF;
1588 END IF;
1589
1590
1591 IF l_status_code IS NOT NULL AND l_status_code <> '4' THEN
1592 OPEN validate_status_csr(l_status_code,p_CURR_WO_DETAILS_REC.StatusCode);
1593 FETCH validate_status_csr INTO l_dummy_ctr;
1594 IF validate_status_csr%NOTFOUND THEN
1595 FND_MESSAGE.SET_NAME('AHL','AHL_UMP_INVALID_STTS_CHNG');
1596 FND_MESSAGE.SET_TOKEN('FROM_STATUS',p_CURR_WO_DETAILS_REC.StatusCode);
1597 FND_MESSAGE.SET_TOKEN('TO_STATUS', l_status_code);
1598 FND_MSG_PUB.ADD;
1599 CLOSE validate_status_csr;
1600 RAISE FND_API.G_EXC_ERROR;
1601 ELSE
1602 CLOSE validate_status_csr;
1603 END IF;
1604 END IF;
1605
1606 IF(l_status_code = '3' AND p_CURR_WO_DETAILS_REC.StatusCode = '1')THEN
1607 --release Job
1608 AHL_PRD_WORKORDER_PVT.release_visit_jobs
1609 (
1610 p_api_version => 1.0 ,
1611 p_init_msg_list => FND_API.G_TRUE,
1612 p_commit => FND_API.G_FALSE,
1613 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1614 p_default => FND_API.G_TRUE,
1615 p_module_type => 'OAF',
1616 x_return_status => x_return_status,
1617 x_msg_count => x_msg_count,
1618 x_msg_data => x_msg_data,
1619 p_visit_id => NULL,
1620 p_unit_effectivity_id => NULL,
1621 p_workorder_id => p_CURR_WO_DETAILS_REC.WorkorderId
1622 );
1623 ELSIF(l_status_code = '7')THEN
1624 --cancel job
1625 AHL_PRD_WORKORDER_PVT.cancel_visit_jobs
1626 (
1627 p_api_version => 1.0 ,
1628 p_init_msg_list => FND_API.G_TRUE,
1629 p_commit => FND_API.G_FALSE,
1630 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1631 p_default => FND_API.G_TRUE,
1632 p_module_type => 'OAF',
1633 x_return_status => x_return_status,
1634 x_msg_count => x_msg_count,
1635 x_msg_data => x_msg_data,
1636 p_visit_id => NULL,
1637 p_unit_effectivity_id => NULL,
1638 p_workorder_id => p_CURR_WO_DETAILS_REC.WorkorderId
1639 );
1640 ELSIF(l_status_code = '4')THEN
1641 IF(p_CURR_WO_DETAILS_REC.IsCompleteEnabled <> 'T')THEN
1642 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_COMP_NALWD');
1643 FND_MSG_PUB.ADD;
1644 RAISE FND_API.G_EXC_ERROR;
1645 END IF;
1646 -- complete job
1647 AHL_COMPLETIONS_PVT.complete_workorder
1648 (
1649 p_api_version => 1.0 ,
1650 p_init_msg_list => FND_API.G_TRUE,
1651 p_commit => FND_API.G_FALSE,
1652 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1653 p_default => FND_API.G_TRUE,
1654 p_module_type => 'OAF',
1655 x_return_status => x_return_status,
1656 x_msg_count => x_msg_count,
1657 x_msg_data => x_msg_data,
1658 p_workorder_id => p_CURR_WO_DETAILS_REC.WorkorderId,
1659 p_object_version_no => p_CURR_WO_DETAILS_REC.ObjectVersionNumber
1660 );
1661
1662 ELSE
1663 l_prd_workorder_rec.WORKORDER_ID := p_CURR_WO_DETAILS_REC.WorkorderId;
1664 l_prd_workorder_rec.OBJECT_VERSION_NUMBER := p_CURR_WO_DETAILS_REC.ObjectVersionNumber;
1665 l_prd_workorder_rec.dml_operation := 'U';
1666 l_prd_workorder_rec.STATUS_CODE := l_status_code;
1667 l_prd_workorder_rec.STATUS_MEANING := p_WO_DETAILS_REC.Status;
1668 l_prd_workorder_rec.HOLD_REASON_CODE := p_WO_DETAILS_REC.HoldReasonCode;
1669 l_prd_workorder_rec.hold_reason := p_wo_details_rec.holdreason;
1670 l_prd_workorder_rec.DEPARTMENT_ID := p_WO_DETAILS_REC.DepartmentId;
1671 l_prd_workorder_rec.DEPARTMENT_NAME := p_WO_DETAILS_REC.DepartmentName;
1672 l_prd_workorder_rec.SCHEDULED_START_DATE := p_WO_DETAILS_REC.ScheduledStartDate;
1673 l_prd_workorder_rec.SCHEDULED_END_DATE := p_WO_DETAILS_REC.ScheduledEndDate;
1674 l_prd_workorder_rec.ACTUAL_START_DATE := p_WO_DETAILS_REC.ActualStartDate;
1675 l_prd_workorder_rec.ACTUAL_END_DATE := p_WO_DETAILS_REC.ActualEndDate;
1676 --AHL_DEBUG_PUB.debug( 'l_prd_workorder_rec.ACTUAL_START_DATE : '||l_prd_workorder_rec.ACTUAL_START_DATE);
1677 --AHL_DEBUG_PUB.debug( 'l_prd_workorder_rec.ACTUAL_END_DATE : '||l_prd_workorder_rec.ACTUAL_END_DATE);
1678 AHL_PRD_WORKORDER_PVT.update_job
1679 (
1680 p_api_version => 1.0 ,
1681 p_init_msg_list => FND_API.G_TRUE,
1682 p_commit => FND_API.G_FALSE,
1683 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1684 p_default => FND_API.G_TRUE,
1685 p_module_type => 'OAF',
1686 x_return_status => x_return_status,
1687 x_msg_count => x_msg_count,
1688 x_msg_data => x_msg_data,
1689 p_wip_load_flag => 'Y',
1690 p_x_prd_workorder_rec => l_prd_workorder_rec,
1691 p_x_prd_workoper_tbl => l_prd_workoper_tbl
1692 );
1693 -- update job
1694 END IF;
1695
1696
1697
1698 END process_wo_details;
1699
1700 PROCEDURE process_op_quality
1701 (
1702 p_OP_DETAILS_REC IN OP_DETAILS_REC_TYPE,
1703 p_OP_QaResults IN QA_RESULTS_REC_TYPE,
1704 x_return_status OUT NOCOPY VARCHAR2,
1705 x_msg_count OUT NOCOPY NUMBER,
1706 x_msg_data OUT NOCOPY VARCHAR2
1707 ) IS
1708
1709 l_results_tbl AHL_QA_RESULTS_PVT.qa_results_tbl_type;
1710 l_hidden_results_tbl AHL_QA_RESULTS_PVT.qa_results_tbl_type;
1711 l_context_tbl AHL_QA_RESULTS_PVT.qa_context_tbl_type;
1712 l_occurrence_tbl AHL_QA_RESULTS_PVT.occurrence_tbl_type;
1713
1714 l_QA_PLAN QA_PLAN_REC_TYPE;
1715 l_QA_RESULTS QA_RESULTS_REC_TYPE;
1716 results_tbl_index integer;
1717 occurrence_tbl_index integer;
1718 l_rowElementCount integer;
1719
1720 BEGIN
1721 x_return_status := FND_API.G_RET_STS_SUCCESS;
1722 IF(p_OP_QaResults.QA_RESULT_TBL IS NULL OR p_OP_QaResults.QA_RESULT_TBL.COUNT < 1)THEN
1723 RETURN;
1724 END IF;
1725
1726 IF(p_OP_DETAILS_REC.IsQualityEnabled <> 'T')THEN
1727 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OPQASUB_NALWD');
1728 FND_MSG_PUB.ADD;
1729 RAISE FND_API.G_EXC_ERROR;
1730 END IF;
1731
1732 get_qa_plan_results
1733 (
1734 p_module_type => 'BPEL',
1735 p_WorkorderId => p_OP_DETAILS_REC.WorkorderId,
1736 p_WorkorderNumber => NULL,
1737 p_WoOperationId => NULL,--p_OP_DETAILS_REC.WorkorderOperationId,
1738 p_OperationSequence => p_OP_DETAILS_REC.OperationSequenceNumber,
1739 x_return_status => x_return_status,
1740 x_msg_count => x_msg_count,
1741 x_msg_data => x_msg_data,
1742 x_QaPlan => l_QA_PLAN,
1743 x_QaResults => l_QA_RESULTS
1744 );
1745 --AHL_DEBUG_PUB.debug( 'p_OP_DETAILS_REC.WorkorderOperationId : '||p_OP_DETAILS_REC.WorkorderOperationId);
1746 --AHL_DEBUG_PUB.debug( 'l_QA_PLAN.PlanId : '||l_QA_PLAN.PlanId);
1747
1748 l_context_tbl(1).Name := 'operation_id';
1749 l_context_tbl(1).Value := to_char(p_OP_DETAILS_REC.WorkorderOperationId);
1750
1751 l_context_tbl(2).Name := 'object_version_no';
1752 l_context_tbl(2).Value := to_char(p_OP_DETAILS_REC.ObjectVersionNumber);
1753
1754 results_tbl_index := 1;
1755 occurrence_tbl_index :=1;
1756 FOR i IN p_OP_QaResults.QA_RESULT_TBL.FIRST..p_OP_QaResults.QA_RESULT_TBL.LAST LOOP
1757 l_rowElementCount := 0;
1758 FOR j IN p_OP_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL.FIRST..p_OP_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL.LAST LOOP
1759 IF('T' = IS_VALID_RESULT_ATTRIBUTE(
1760 p_OP_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL(j).CharId,
1761 l_QA_PLAN))THEN
1762 l_results_tbl(results_tbl_index).char_id := p_OP_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL(j).CharId;
1763 l_results_tbl(results_tbl_index).result_value := p_OP_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL(j).AttributeValue;
1764 results_tbl_index := results_tbl_index+1;
1765 l_rowElementCount := l_rowElementCount+1;
1766 END IF;
1767 END LOOP;
1768 IF(l_rowElementCount > 0)THEN
1769 l_occurrence_tbl(occurrence_tbl_index).element_count := l_rowElementCount;
1770 occurrence_tbl_index := occurrence_tbl_index + 1;
1771 END IF;
1772 END LOOP;
1773 IF(results_tbl_index = 1)THEN
1774 RETURN; -- No attributes passed.
1775 END IF;
1776
1777 AHL_QA_RESULTS_PVT.submit_qa_results
1778 (
1779 p_api_version => 1.0,
1780 p_init_msg_list => FND_API.G_TRUE,
1781 p_commit => FND_API.G_FALSE,
1782 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1783 p_default => FND_API.G_FALSE,
1784 p_module_type => 'OAF',
1785 x_return_status => x_return_status,
1786 x_msg_count => x_msg_count,
1787 x_msg_data => x_msg_data,
1788 p_plan_id => l_QA_PLAN.PlanId,
1789 p_organization_id => l_QA_PLAN.OrganizationId,
1790 p_transaction_no => 2002,
1791 p_specification_id => NULL,
1792 p_results_tbl => l_results_tbl,
1793 p_hidden_results_tbl => l_hidden_results_tbl,
1794 p_context_tbl => l_context_tbl,
1795 p_result_commit_flag => 0,
1796 p_id_or_value => 'VALUE',
1797 p_x_collection_id => l_QA_RESULTS.CollectionId,
1798 p_x_occurrence_tbl => l_occurrence_tbl
1799 );
1800 END process_op_quality;
1801
1802 PROCEDURE process_op_details
1803 (
1804 p_WO_DETAILS_REC IN WO_DETAILS_REC_TYPE,
1805 p_Operations IN OP_ALL_DETAILS_TBL,
1806 x_return_status OUT NOCOPY VARCHAR2,
1807 x_msg_count OUT NOCOPY NUMBER,
1808 x_msg_data OUT NOCOPY VARCHAR2
1809 )IS
1810
1811
1812 CURSOR get_current_obj_ver_csr(p_WoOperationId IN NUMBER)IS
1813 SELECT object_version_number FROM AHL_WORKORDER_OPERATIONS
1814 WHERE WORKORDER_OPERATION_ID = p_WoOperationId;
1815
1816 l_prd_operation_tbl AHL_PRD_OPERATIONS_PVT.PRD_OPERATION_TBL;
1817 l_Operations OP_TBL_TYPE;
1818 j INTEGER;
1819 x_msg_index_out NUMBER;
1820 l_prd_comp_operation_tbl AHL_COMPLETIONS_PVT.operation_tbl_type;
1821
1822 Cursor get_op_status_code(p_status VARCHAR2) IS
1823 select lookup_code status_code
1824 from fnd_lookup_values_vl
1825 where lookup_type='AHL_OPERATION_STATUS'
1826 and sysdate between nvl(start_date_active,sysdate)
1827 and nvl(end_date_active,sysdate)
1828 and meaning like p_status;
1829
1830
1831 BEGIN
1832 x_return_status := FND_API.G_RET_STS_SUCCESS;
1833 IF(p_Operations IS NULL OR p_Operations.COUNT < 1)THEN
1834 RETURN;
1835 END IF;
1836 j :=1;--keep this as 1 as thats how called package recognize things..
1837 FOR i IN p_Operations.FIRST..p_Operations.LAST LOOP
1838 IF(p_Operations(i).WorkorderOperationId IS NULL AND p_Operations(i).OperationSequenceNumber IS NULL)THEN
1839 EXIT;--empty record not entertained.
1840 END IF;
1841 --AHL_DEBUG_PUB.debug( 'p_Operations : '||i);
1842 --AHL_DEBUG_PUB.debug( 'p_Operations : '||i || ' : ' ||p_Operations(i).OperationSequenceNumber);
1843 get_wo_operations_details
1844 (
1845 p_module_type => 'BPEL',
1846 p_WorkorderId => p_WO_DETAILS_REC.WorkorderId,
1847 p_WorkorderNumber => NULL,
1848 p_WoOperationId => p_Operations(i).WorkorderOperationId,
1849 p_OperationSequence => p_Operations(i).OperationSequenceNumber,
1850 x_return_status => x_return_status,
1851 x_msg_count => x_msg_count,
1852 x_msg_data => x_msg_count,
1853 x_Operations => l_Operations
1854 );
1855 --DBMS_OUTPUT.put_line('x_return_status :i: ' || x_return_status);
1856 IF(p_Operations(i).ObjectVersionNumber IS NULL OR
1857 p_Operations(i).ObjectVersionNumber <> l_Operations(0).ObjectVersionNumber)THEN
1858 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1859 FND_MSG_PUB.ADD;
1860 RAISE FND_API.G_EXC_ERROR;
1861 END IF;
1862 IF(l_Operations(0).IsUpdateEnabled <> 'T')THEN
1863 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OPUPD_NALWD');
1864 FND_MSG_PUB.ADD;
1865 RAISE FND_API.G_EXC_ERROR;
1866 END IF;
1867 process_op_quality
1868 (
1869 p_OP_DETAILS_REC => l_Operations(0),
1870 p_OP_QaResults => p_Operations(i).QAResults,
1871 x_return_status => x_return_status,
1872 x_msg_count => x_msg_count,
1873 x_msg_data => x_msg_data
1874 );
1875 --DBMS_OUTPUT.put_line('x_return_status :i: ' || x_return_status);
1876 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1877 RAISE FND_API.G_EXC_ERROR;
1878 ELSE
1879 OPEN get_current_obj_ver_csr(l_Operations(0).WorkorderOperationId);
1880 FETCH get_current_obj_ver_csr INTO l_Operations(0).ObjectVersionNumber;
1881 CLOSE get_current_obj_ver_csr;
1882 END IF;
1883
1884 IF(p_Operations(i).StatusCode IS NOT NULL OR p_Operations(i).Status IS NOT NULL
1885 OR p_Operations(i).ActualStartDate IS NOT NULL
1886 or p_operations(i).actualenddate is not null)then
1887 IF(p_Operations(i).StatusCode IS NOT NULL AND p_Operations(i).StatusCode NOT IN ('1','2'))THEN
1888 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OPUPD_NALWD');
1889 FND_MSG_PUB.ADD;
1890 RAISE FND_API.G_EXC_ERROR;
1891 end if;
1892 l_prd_operation_tbl(j).status_code := p_operations(i).statuscode;
1893 l_prd_operation_tbl(j).status_meaning := p_Operations(i).Status;
1894 if(p_operations(i).status is not null)then
1895 open get_op_status_code(p_operations(i).status);
1896 fetch get_op_status_code into l_prd_operation_tbl(j).status_code;
1897 if get_op_status_code%notfound then
1898 fnd_message.set_name('AHL','AHL_PRD_OPUPD_NALWD');
1899 fnd_msg_pub.add;
1900 close get_op_status_code;
1901 raise fnd_api.g_exc_error;
1902 end if;
1903 close get_op_status_code;
1904 end if;
1905 ----DBMS_OUTPUT.put_line('op :i:statusCode: ' || p_Operations(i).StatusCode);
1906 l_prd_operation_tbl(j).operation_sequence_num := l_Operations(0).OperationSequenceNumber;
1907 l_prd_operation_tbl(j).workorder_id := l_Operations(0).WorkorderId;
1908 l_prd_operation_tbl(j).workorder_operation_id := l_Operations(0).WorkorderOperationId;
1909 l_prd_operation_tbl(j).department_id := l_Operations(0).DepartmentId;
1910 l_prd_operation_tbl(j).department_name := l_Operations(0).DepartmentName;
1911 l_prd_operation_tbl(j).object_version_number := l_Operations(0).ObjectVersionNumber;
1912 l_prd_operation_tbl(j).scheduled_start_date := l_Operations(0).ScheduledStartDate;
1913 l_prd_operation_tbl(j).scheduled_end_date := l_Operations(0).ScheduledEndDate;
1914 l_prd_operation_tbl(j).dml_operation := 'U';
1915 l_prd_operation_tbl(j).actual_start_date := p_Operations(i).ActualStartDate;
1916 l_prd_operation_tbl(j).actual_end_date := p_Operations(i).ActualEndDate;
1917 IF(p_Operations(i).StatusCode = '1')THEN
1918 IF(p_Operations(i).ActualStartDate IS NULL AND p_Operations(i).ActualEndDate IS NULL)THEN
1919 l_prd_operation_tbl(j).actual_start_date := l_Operations(0).ActualStartDate;
1920 l_prd_operation_tbl(j).actual_end_date := l_Operations(0).ActualEndDate;
1921 IF(l_prd_operation_tbl(j).actual_start_date IS NULL AND l_prd_operation_tbl(j).actual_end_date IS NULL)THEN
1922 l_prd_comp_operation_tbl(0).workorder_id := l_Operations(0).WorkorderId;
1923 l_prd_comp_operation_tbl(0).operation_sequence_num := l_Operations(0).OperationSequenceNumber;
1924 AHL_COMPLETIONS_PVT.Get_Default_Op_Actual_Dates
1925 (
1926 x_return_status => x_return_status,
1927 x_msg_count => x_msg_count,
1928 x_msg_data => x_msg_data,
1929 P_x_operation_tbl => l_prd_comp_operation_tbl
1930 );
1931 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1932 RAISE FND_API.G_EXC_ERROR;
1933 END IF;
1934 l_prd_operation_tbl(j).actual_start_date := l_prd_comp_operation_tbl(0).Actual_Start_Date;
1935 l_prd_operation_tbl(j).actual_end_date := l_prd_comp_operation_tbl(0).Actual_End_Date;
1936 END IF;
1937 END IF;
1938 END IF;
1939 j := j+1;
1940 END IF;
1941 END LOOP;
1942 IF(l_prd_operation_tbl IS NOT NULL AND l_prd_operation_tbl.COUNT > 0)THEN
1943 ----DBMS_OUTPUT.put_line('op :i:statusCode: processing operations : ' || l_prd_operation_tbl.COUNT);
1944 AHL_PRD_OPERATIONS_PVT.PROCESS_OPERATIONS
1945 (
1946 p_api_version => 1.0 ,
1947 p_init_msg_list => FND_API.G_TRUE,
1948 p_commit => FND_API.G_FALSE,
1949 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1950 p_default => FND_API.G_TRUE,
1951 p_module_type => 'OAF',
1952 p_wip_mass_load_flag => 'Y',
1953 x_return_status => x_return_status,
1954 x_msg_count => x_msg_count,
1955 x_msg_data => x_msg_data,
1956 p_x_prd_operation_tbl => l_prd_operation_tbl
1957 );
1958 END IF;
1959
1960 END process_op_details;
1961
1962 PROCEDURE process_mtl_requirements
1963 (
1964 p_WO_DETAILS_REC IN WO_DETAILS_REC_TYPE,
1965 p_MaterialRequirementDetails IN MTL_REQMTS_TBL_TYPE,
1966 x_return_status OUT NOCOPY VARCHAR2,
1967 x_msg_count OUT NOCOPY NUMBER,
1968 x_msg_data OUT NOCOPY VARCHAR2
1969 ) IS
1970
1971 l_req_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
1972 j INTEGER;
1973
1974 BEGIN
1975 x_return_status := FND_API.G_RET_STS_SUCCESS;
1976 IF(p_MaterialRequirementDetails IS NULL OR p_MaterialRequirementDetails.COUNT < 1)THEN
1977 RETURN;
1978 END IF;
1979 j := 1;
1980 FOR i IN p_MaterialRequirementDetails.FIRST..p_MaterialRequirementDetails.LAST LOOP
1981 IF(p_MaterialRequirementDetails(i).ItemNumber IS NOT NULL OR p_MaterialRequirementDetails(i).InventoryItemId IS NOT NULL
1982 OR p_MaterialRequirementDetails(i).ScheduledMaterialId IS NOT NULL)THEN
1983 l_req_material_tbl(j).SCHEDULE_MATERIAL_ID := p_MaterialRequirementDetails(i).ScheduledMaterialId;
1984 --l_req_material_tbl(j).OBJECT_VERSION_NUMBER := p_MaterialRequirementDetails(i).ObjectVersionNumber;
1985 l_req_material_tbl(j).inventory_item_id := p_materialrequirementdetails(i).inventoryitemid;
1986 --l_req_material_tbl(j).SCHEDULED_DATE := p_MaterialRequirementDetails(i).ScheduledDate;
1987 l_req_material_tbl(j).CONCATENATED_SEGMENTS := p_MaterialRequirementDetails(i).ItemNumber;
1988 l_req_material_tbl(j).ITEM_DESCRIPTION := p_MaterialRequirementDetails(i).ItemDescription;
1989 l_req_material_tbl(j).requested_quantity := p_materialrequirementdetails(i).requiredquantity;
1990
1991 l_req_material_tbl(j).requested_date := p_materialrequirementdetails(i).requireddate;
1992 --l_req_material_tbl(j).SCHEDULED_DATE := p_MaterialRequirementDetails(i).RequiredDate;
1993 l_req_material_tbl(j).uom_meaning := p_materialrequirementdetails(i).partuom;
1994 --l_req_material_tbl(j).SCHEDULED_QUANTITY := p_MaterialRequirementDetails(i).ScheduledQuantity;
1995 l_req_material_tbl(j).JOB_NUMBER := p_WO_DETAILS_REC.WorkorderNumber;
1996 l_req_material_tbl(j).WORKORDER_ID := p_WO_DETAILS_REC.WorkorderId;
1997 --l_req_material_tbl(j).WIP_ENTITY_ID := p_WO_DETAILS_REC.WipEntityId;
1998 l_req_material_tbl(j).OPERATION_SEQUENCE := p_MaterialRequirementDetails(i).OperationSequenceNumber;
1999 IF(p_MaterialRequirementDetails(i).ScheduledMaterialId IS NOT NULL) THEN
2000 l_req_material_tbl(j).OPERATION_FLAG := 'U';
2001 ELSE
2002 l_req_material_tbl(j).OPERATION_FLAG := 'C';
2003 END IF;
2004 j := j+1;
2005 END IF;
2006 END LOOP;
2007 IF(j > 1) THEN
2008 AHL_PP_MATERIALS_PVT.Process_Material_Request (
2009 p_api_version => 1.0 ,
2010 p_init_msg_list => FND_API.G_TRUE,
2011 p_commit => FND_API.G_FALSE,
2012 p_validation_level => fnd_api.g_valid_level_full,
2013 p_module_type => 'JSP',
2014 p_x_req_material_tbl => l_req_material_tbl,
2015 x_return_status => x_return_status,
2016 x_msg_count => x_msg_count,
2017 x_msg_data => x_msg_data
2018 );
2019 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2020 RAISE FND_API.G_EXC_ERROR;
2021 END IF;
2022 END IF;
2023 END process_mtl_requirements;
2024
2025 PROCEDURE process_wo_quality
2026 (
2027 p_WO_DETAILS_REC IN WO_DETAILS_REC_TYPE,
2028 p_WO_QaResults IN QA_RESULTS_REC_TYPE,
2029 x_return_status OUT NOCOPY VARCHAR2,
2030 x_msg_count OUT NOCOPY NUMBER,
2031 x_msg_data OUT NOCOPY VARCHAR2
2032 ) IS
2033
2034 l_results_tbl AHL_QA_RESULTS_PVT.qa_results_tbl_type;
2035 l_hidden_results_tbl AHL_QA_RESULTS_PVT.qa_results_tbl_type;
2036 l_context_tbl AHL_QA_RESULTS_PVT.qa_context_tbl_type;
2037 l_occurrence_tbl AHL_QA_RESULTS_PVT.occurrence_tbl_type;
2038
2039 l_QA_PLAN QA_PLAN_REC_TYPE;
2040 l_QA_RESULTS QA_RESULTS_REC_TYPE;
2041 results_tbl_index integer;
2042 occurrence_tbl_index integer;
2043 l_rowElementCount integer;
2044
2045
2046 BEGIN
2047 x_return_status := FND_API.G_RET_STS_SUCCESS;
2048 IF(p_WO_QaResults.QA_RESULT_TBL IS NULL OR p_WO_QaResults.QA_RESULT_TBL.COUNT < 1)THEN
2049 RETURN;
2050 END IF;
2051 IF(p_WO_DETAILS_REC.IsQualityEnabled <> 'T')THEN
2052 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WOQASUB_NALWD');
2053 FND_MSG_PUB.ADD;
2054 RAISE FND_API.G_EXC_ERROR;
2055 END IF;
2056
2057 get_qa_plan_results
2058 (
2059 p_module_type => 'BPEL',
2060 p_WorkorderId => p_WO_DETAILS_REC.WorkorderId,
2061 p_WorkorderNumber => NULL,
2062 p_WoOperationId => NULL,
2063 p_OperationSequence => NULL,
2064 x_return_status => x_return_status,
2065 x_msg_count => x_msg_count,
2066 x_msg_data => x_msg_data,
2067 x_QaPlan => l_QA_PLAN,
2068 x_QaResults => l_QA_RESULTS
2069 );
2070
2071 l_context_tbl(1).Name := 'workorder_id';
2072 l_context_tbl(1).Value := to_char(p_WO_DETAILS_REC.WorkorderId);
2073
2074 l_context_tbl(2).Name := 'object_version_no';
2075 l_context_tbl(2).Value := to_char(p_WO_DETAILS_REC.ObjectVersionNumber);
2076
2077 results_tbl_index := 1;
2078 occurrence_tbl_index :=1;
2079 FOR i IN p_WO_QaResults.QA_RESULT_TBL.FIRST..p_WO_QaResults.QA_RESULT_TBL.LAST LOOP
2080 l_rowElementCount := 0;
2081 FOR j IN p_WO_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL.FIRST..p_WO_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL.LAST LOOP
2082 IF('T' = IS_VALID_RESULT_ATTRIBUTE(
2083 p_WO_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL(j).CharId,
2084 l_QA_PLAN))THEN
2085 l_results_tbl(results_tbl_index).char_id := p_WO_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL(j).CharId;
2086 l_results_tbl(results_tbl_index).result_value := p_WO_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL(j).AttributeValue;
2087 results_tbl_index := results_tbl_index+1;
2088 l_rowElementCount := l_rowElementCount+1;
2089 END IF;
2090 END LOOP;
2091 IF(l_rowElementCount > 0)THEN
2092 l_occurrence_tbl(occurrence_tbl_index).element_count := l_rowElementCount;
2093 occurrence_tbl_index := occurrence_tbl_index + 1;
2094 END IF;
2095 END LOOP;
2096 IF(results_tbl_index = 1)THEN
2097 RETURN; -- No attributes passed.
2098 END IF;
2099
2100 AHL_QA_RESULTS_PVT.submit_qa_results
2101 (
2102 p_api_version => 1.0,
2103 p_init_msg_list => FND_API.G_TRUE,
2104 p_commit => FND_API.G_FALSE,
2105 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2106 p_default => FND_API.G_FALSE,
2107 p_module_type => 'OAF',
2108 x_return_status => x_return_status,
2109 x_msg_count => x_msg_count,
2110 x_msg_data => x_msg_data,
2111 p_plan_id => l_QA_PLAN.PlanId,
2112 p_organization_id => l_QA_PLAN.OrganizationId,
2113 p_transaction_no => 2001,
2114 p_specification_id => NULL,
2115 p_results_tbl => l_results_tbl,
2116 p_hidden_results_tbl => l_hidden_results_tbl,
2117 p_context_tbl => l_context_tbl,
2118 p_result_commit_flag => 0,
2119 p_id_or_value => 'VALUE',
2120 p_x_collection_id => l_QA_RESULTS.CollectionId,
2121 p_x_occurrence_tbl => l_occurrence_tbl
2122 );
2123 END process_wo_quality;
2124
2125
2126
2127 PROCEDURE process_res_txns
2128 (
2129 p_WO_DETAILS_REC IN WO_DETAILS_REC_TYPE,
2130 p_ResourceTransactions IN RES_TXNS_TBL_TYPE,
2131 x_return_status OUT NOCOPY VARCHAR2,
2132 x_msg_count OUT NOCOPY NUMBER,
2133 x_msg_data OUT NOCOPY VARCHAR2
2134 ) IS
2135 l_res_txns_tbl AHL_PRD_RESOURCE_TRANX_PVT.PRD_RESOURCE_TXNS_TBL;
2136 j INTEGER;
2137 BEGIN
2138 x_return_status := FND_API.G_RET_STS_SUCCESS;
2139 IF(p_ResourceTransactions IS NULL OR p_ResourceTransactions.COUNT < 1)THEN
2140 RETURN;
2141 END IF;
2142 x_return_status := FND_API.G_RET_STS_SUCCESS;
2143 j :=0;
2144 FOR i IN p_ResourceTransactions.FIRST..p_ResourceTransactions.LAST LOOP
2145 IF((p_ResourceTransactions(i).StartTime IS NOT NULL OR
2146 p_ResourceTransactions(i).EndTime IS NOT NULL OR
2147 p_ResourceTransactions(i).Quantity IS NOT NULL) AND
2148 (p_ResourceTransactions(i).ResourceId IS NOT NULL OR
2149 p_ResourceTransactions(i).ResourceCode IS NOT NULL))THEN
2150 l_res_txns_tbl(j).WORKORDER_ID := p_WO_DETAILS_REC.WorkorderId;
2151 l_res_txns_tbl(j).OPERATION_SEQUENCE_NUM := p_ResourceTransactions(i).OperationSequenceNumber;
2152 l_res_txns_tbl(j).RESOURCE_ID := p_ResourceTransactions(i).ResourceId;
2153 l_res_txns_tbl(j).RESOURCE_NAME := p_ResourceTransactions(i).ResourceCode;
2154 l_res_txns_tbl(j).Qty := p_ResourceTransactions(i).Quantity;
2155 l_res_txns_tbl(j).TRANSACTION_DATE := p_ResourceTransactions(i).StartTime ;
2156 l_res_txns_tbl(j).END_DATE := p_ResourceTransactions(i).EndTime;
2157 l_res_txns_tbl(j).DML_OPERATION := 'C';
2158 l_res_txns_tbl(j).employee_name := p_ResourceTransactions(i).EmployeeName;
2159 l_res_txns_tbl(j).employee_num := p_ResourceTransactions(i).EmployeeNumber;
2160 l_res_txns_tbl(j).person_id := p_ResourceTransactions(i).EmployeeId;
2161 l_res_txns_tbl(j).serial_number := p_ResourceTransactions(i).SerialNumber;
2162 l_res_txns_tbl(j).activity_id := p_ResourceTransactions(i).ActivityId;
2163 l_res_txns_tbl(j).activity_meaning := p_ResourceTransactions(i).Activity;
2164 l_res_txns_tbl(j).reason_id := p_ResourceTransactions(i).ReasonId;
2165 l_res_txns_tbl(j).reason := p_ResourceTransactions(i).Reason;
2166 l_res_txns_tbl(j).reference := p_ResourceTransactions(i).Reference;
2167 j := j+1;
2168 END IF;
2169 END LOOP;
2170
2171 IF(l_res_txns_tbl IS NOT NULL AND l_res_txns_tbl.COUNT > 0)THEN
2172 IF(p_WO_DETAILS_REC.IsUpdateEnabled <> 'T')THEN
2173 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_RES_TXN_NALWD');
2174 FND_MSG_PUB.ADD;
2175 RAISE FND_API.G_EXC_ERROR;
2176 END IF;
2177 AHL_PRD_RESOURCE_TRANX_PVT.process_resource_txns
2178 (
2179 p_api_version => 1.0 ,
2180 p_init_msg_list => FND_API.G_TRUE,
2181 p_commit => FND_API.G_FALSE,
2182 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2183 p_default => FND_API.G_TRUE,
2184 p_module_type => 'BPEL',
2185 x_return_status => x_return_status,
2186 x_msg_count => x_msg_count,
2187 x_msg_data => x_msg_data,
2188 p_x_prd_resrc_txn_tbl => l_res_txns_tbl
2189 );
2190 END IF;
2191 END process_res_txns;
2192
2193 PROCEDURE process_workorder
2194 (
2195 p_api_version IN NUMBER := 1.0,
2196 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2197 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2198 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2199 p_default IN VARCHAR2 := FND_API.G_FALSE,
2200 p_module_type IN VARCHAR2,
2201 p_userid IN VARCHAR2 := NULL,
2202 p_WO_DETAILS_REC IN WO_DETAILS_REC_TYPE,
2203 p_Operations IN OP_ALL_DETAILS_TBL,
2204 p_TurnoverNotes IN TURNOVER_NOTES_TBL_TYPE,
2205 p_MaterialRequirementDetails IN MTL_REQMTS_TBL_TYPE,
2206 p_WO_QaResults IN QA_RESULTS_REC_TYPE,
2207 p_ResourceTransactions IN RES_TXNS_TBL_TYPE,
2208 x_return_status OUT NOCOPY VARCHAR2,
2209 x_msg_count OUT NOCOPY NUMBER,
2210 x_msg_data OUT NOCOPY VARCHAR2
2211 ) IS
2212 l_api_version CONSTANT NUMBER := 1.0;
2213 l_api_name CONSTANT VARCHAR2(30) := 'process_workorder';
2214 l_WO_DETAILS_REC WO_DETAILS_REC_TYPE;
2215
2216 CURSOR get_current_obj_ver_csr(p_WotkorderId IN NUMBER)IS
2217 SELECT object_version_number FROM AHL_WORKORDERS
2218 WHERE WORKORDER_ID = p_WotkorderId;
2219
2220 BEGIN
2221
2222 SAVEPOINT PROCESS_WORKORDER;
2223
2224 IF(p_module_type = 'BPEL') THEN
2225 x_return_status := init_user_and_role(p_userid);
2226 IF(x_return_status <> Fnd_Api.G_RET_STS_SUCCESS)THEN
2227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2228 END IF;
2229 END IF;
2230
2231
2232 -- Standard call to check for call compatibility
2233 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
2234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2235 END IF;
2236
2237 IF FND_API.To_Boolean(p_init_msg_list) THEN
2238 FND_MSG_PUB.Initialize;
2239 END IF;
2240 -- Initialize API return status to success
2241
2242 --AHL_DEBUG_PUB.debug( 'p_WO_DETAILS_REC.WorkorderId : '||p_WO_DETAILS_REC.WorkorderId);
2243 --AHL_DEBUG_PUB.debug( 'p_WO_DETAILS_REC.WorkorderNumber : '||p_WO_DETAILS_REC.WorkorderNumber);
2244 get_workorder_details
2245 (
2246 p_module_type => p_module_type,
2247 p_WorkorderId => p_WO_DETAILS_REC.WorkorderId,
2248 p_WorkorderNumber => p_WO_DETAILS_REC.WorkorderNumber,
2249 x_return_status => x_return_status,
2250 x_msg_count => x_msg_count,
2251 x_msg_data => x_msg_data,
2252 x_WO_DETAILS_REC => l_WO_DETAILS_REC
2253 );
2254 --AHL_DEBUG_PUB.debug( 'l_WO_DETAILS_REC.ObjectVersionNumber : '||l_WO_DETAILS_REC.ObjectVersionNumber);
2255 --AHL_DEBUG_PUB.debug( 'p_WO_DETAILS_REC.ObjectVersionNumber : '||p_WO_DETAILS_REC.ObjectVersionNumber);
2256 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2257 RAISE FND_API.G_EXC_ERROR;
2258 END IF;
2259 IF(p_WO_DETAILS_REC.ObjectVersionNumber IS NULL OR
2260 p_WO_DETAILS_REC.ObjectVersionNumber <> l_WO_DETAILS_REC.ObjectVersionNumber)THEN
2261 --AHL_DEBUG_PUB.debug( 'Object Version Numbers are not same');
2262 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
2263 FND_MSG_PUB.ADD;
2264 RAISE FND_API.G_EXC_ERROR;
2265 END IF;
2266 process_turnover_notes(
2267 p_WO_DETAILS_REC => l_WO_DETAILS_REC,
2268 p_TurnoverNotes => p_TurnoverNotes,
2269 x_return_status => x_return_status,
2270 x_msg_count => x_msg_count,
2271 x_msg_data => x_msg_data
2272 );
2273
2274 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2275 RAISE FND_API.G_EXC_ERROR;
2276 END IF;
2277
2278 process_mtl_requirements
2279 (
2280 p_WO_DETAILS_REC => l_WO_DETAILS_REC,
2281 p_MaterialRequirementDetails => p_MaterialRequirementDetails,
2282 x_return_status => x_return_status,
2283 x_msg_count => x_msg_count,
2284 x_msg_data => x_msg_data
2285 );
2286 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2287 RAISE FND_API.G_EXC_ERROR;
2288 END IF;
2289
2290 process_res_txns(
2291 p_WO_DETAILS_REC => l_WO_DETAILS_REC,
2292 p_ResourceTransactions => p_ResourceTransactions,
2293 x_return_status => x_return_status,
2294 x_msg_count => x_msg_count,
2295 x_msg_data => x_msg_data
2296 );
2297 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2298 RAISE FND_API.G_EXC_ERROR;
2299 END IF;
2300
2301 process_op_details(
2302 p_WO_DETAILS_REC => l_WO_DETAILS_REC,
2303 p_Operations => p_Operations,
2304 x_return_status => x_return_status,
2305 x_msg_count => x_msg_count,
2306 x_msg_data => x_msg_data
2307 );
2308 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2309 RAISE FND_API.G_EXC_ERROR;
2310 END IF;
2311
2312 process_wo_quality(
2313 p_WO_DETAILS_REC => l_WO_DETAILS_REC,
2314 p_WO_QaResults => p_WO_QaResults,
2315 x_return_status => x_return_status,
2316 x_msg_count => x_msg_count,
2317 x_msg_data => x_msg_data
2318 );
2319 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2320 RAISE FND_API.G_EXC_ERROR;
2321 ELSE
2322 OPEN get_current_obj_ver_csr(l_WO_DETAILS_REC.WorkorderId);
2323 FETCH get_current_obj_ver_csr INTO l_WO_DETAILS_REC.ObjectVersionNumber;
2324 CLOSE get_current_obj_ver_csr;
2325 END IF;
2326
2327
2328
2329 process_wo_details(
2330 p_WO_DETAILS_REC => p_WO_DETAILS_REC,
2331 p_CURR_WO_DETAILS_REC => l_WO_DETAILS_REC,
2332 x_return_status => x_return_status,
2333 x_msg_count => x_msg_count,
2334 x_msg_data => x_msg_data
2335 );
2336 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2337 RAISE FND_API.G_EXC_ERROR;
2338 END IF;
2339
2340 IF FND_API.to_boolean(p_commit) THEN
2341 COMMIT;
2342 END IF;
2343
2344 EXCEPTION
2345 WHEN FND_API.G_EXC_ERROR THEN
2346 ROLLBACK TO PROCESS_WORKORDER;
2347 x_return_status := FND_API.G_RET_STS_ERROR;
2348 x_msg_count := FND_MSG_PUB.count_msg;
2349
2350
2351 x_msg_data := GET_MSG_DATA(x_msg_count);
2352 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2353 ROLLBACK TO PROCESS_WORKORDER;
2354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2355 x_msg_count := FND_MSG_PUB.count_msg;
2356 x_msg_data := GET_MSG_DATA(x_msg_count);
2357 WHEN OTHERS THEN
2358 ROLLBACK TO PROCESS_WORKORDER;
2359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2360
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 => l_api_name,
2364 p_error_text => SUBSTR(SQLERRM,1,500));
2365 END IF;
2366 x_msg_count := FND_MSG_PUB.count_msg;
2367 x_msg_data := GET_MSG_DATA(x_msg_count);
2368 END process_workorder;
2369
2370
2371 /*PROCEDURE process_workorder_autotxns
2372 (
2373 p_api_version IN NUMBER := 1.0,
2374 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2375 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2376 p_default IN VARCHAR2 := FND_API.G_FALSE,
2377 p_module_type IN VARCHAR2,
2378 p_userid IN VARCHAR2 := NULL,
2379 p_WO_DETAILS_REC IN WO_DETAILS_REC_TYPE,
2380 p_Operations IN OP_ALL_DETAILS_TBL,
2381 p_TurnoverNotes IN TURNOVER_NOTES_TBL_TYPE,
2382 p_MaterialRequirementDetails IN MTL_REQMTS_TBL_TYPE,
2383 p_WO_QaResults IN QA_RESULTS_REC_TYPE,
2384 p_ResourceTransactions IN RES_TXNS_TBL_TYPE,
2385 x_return_status OUT NOCOPY VARCHAR2,
2386 x_msg_count OUT NOCOPY NUMBER,
2387 x_msg_data OUT NOCOPY VARCHAR2
2388 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
2389
2390 l_api_version CONSTANT NUMBER := 1.0;
2391 l_api_name CONSTANT VARCHAR2(30) := 'process_workorder_autotxns';
2392
2393
2394 BEGIN
2395
2396 -- Standard call to check for call compatibility
2397 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
2398 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2399 END IF;
2400
2401 process_workorder_nonautotxns
2402 (
2403 p_api_version => p_api_version,
2404 p_init_msg_list => p_init_msg_list,
2405 p_commit => FND_API.G_FALSE,
2406 p_validation_level => p_validation_level,
2407 p_default => p_default,
2408 p_module_type => p_module_type,
2409 p_userid => p_userid,
2410 p_WO_DETAILS_REC => p_WO_DETAILS_REC,
2411 p_Operations => p_Operations,
2412 p_TurnoverNotes => p_TurnoverNotes,
2413 p_MaterialRequirementDetails => p_MaterialRequirementDetails,
2414 p_WO_QaResults => p_WO_QaResults,
2415 p_ResourceTransactions => p_ResourceTransactions,
2416 x_return_status => x_return_status,
2417 x_msg_count => x_msg_count,
2418 x_msg_data => x_msg_data
2419 );
2420
2421 IF(x_return_status = Fnd_Api.G_RET_STS_SUCCESS)THEN
2422 COMMIT;
2423 ELSE
2424 ROLLBACK;
2425 END IF;
2426
2427
2428
2429 EXCEPTION
2430 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2431 ROLLBACK;
2432 WHEN OTHERS THEN
2433 ROLLBACK;
2434 END process_workorder_autotxns;*/
2435
2436 /*PROCEDURE process_workorder
2437 (
2438 p_api_version IN NUMBER := 1.0,
2439 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2440 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2441 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2442 p_default IN VARCHAR2 := FND_API.G_FALSE,
2443 p_module_type IN VARCHAR2,
2444 p_userid IN VARCHAR2 := NULL,
2445 p_WO_DETAILS_REC IN WO_DETAILS_REC_TYPE,
2446 p_Operations IN OP_ALL_DETAILS_TBL,
2447 p_TurnoverNotes IN TURNOVER_NOTES_TBL_TYPE,
2448 p_MaterialRequirementDetails IN MTL_REQMTS_TBL_TYPE,
2449 p_WO_QaResults IN QA_RESULTS_REC_TYPE,
2450 p_ResourceTransactions IN RES_TXNS_TBL_TYPE,
2451 x_return_status OUT NOCOPY VARCHAR2,
2452 x_msg_count OUT NOCOPY NUMBER,
2453 x_msg_data OUT NOCOPY VARCHAR2
2454 ) IS
2455 l_api_version CONSTANT NUMBER := 1.0;
2456 l_api_name CONSTANT VARCHAR2(30) := 'process_workorder';
2457
2458 BEGIN
2459
2460 IF(p_module_type = 'BPEL' AND p_commit = FND_API.G_TRUE)THEN
2461
2462 process_workorder_autotxns
2463 (
2464 p_api_version => p_api_version,
2465 p_init_msg_list => p_init_msg_list,
2466 p_validation_level => p_validation_level,
2467 p_default => p_default,
2468 p_module_type => p_module_type,
2469 p_userid => p_userid,
2470 p_WO_DETAILS_REC => p_WO_DETAILS_REC,
2471 p_Operations => p_Operations,
2472 p_TurnoverNotes => p_TurnoverNotes,
2473 p_MaterialRequirementDetails => p_MaterialRequirementDetails,
2474 p_WO_QaResults => p_WO_QaResults,
2475 p_ResourceTransactions => p_ResourceTransactions,
2476 x_return_status => x_return_status,
2477 x_msg_count => x_msg_count,
2478 x_msg_data => x_msg_data
2479 );
2480
2481 ELSE
2482
2483 process_workorder_nonautotxns
2484 (
2485 p_api_version => p_api_version,
2486 p_init_msg_list => p_init_msg_list,
2487 p_commit => p_commit,
2488 p_validation_level => p_validation_level,
2489 p_default => p_default,
2490 p_module_type => p_module_type,
2491 p_userid => p_userid,
2492 p_WO_DETAILS_REC => p_WO_DETAILS_REC,
2493 p_Operations => p_Operations,
2494 p_TurnoverNotes => p_TurnoverNotes,
2495 p_MaterialRequirementDetails => p_MaterialRequirementDetails,
2496 p_WO_QaResults => p_WO_QaResults,
2497 p_ResourceTransactions => p_ResourceTransactions,
2498 x_return_status => x_return_status,
2499 x_msg_count => x_msg_count,
2500 x_msg_data => x_msg_data
2501 );
2502 END IF;
2503
2504
2505 END process_workorder;*/
2506
2507
2508 FUNCTION GET_MSG_DATA(p_msg_count IN NUMBER) RETURN VARCHAR2 IS
2509 l_msg_data VARCHAR2(4000);
2510 l_temp_msg_data VARCHAR2(2000);
2511 l_msg_index_out NUMBER;
2512 l_msg_count NUMBER;
2513
2514 BEGIN
2515 l_msg_count := p_msg_count;
2516 IF (p_msg_count IS NULL)THEN
2517 RETURN NULL;
2518 END IF;
2519 IF (p_msg_count = 1) then
2520 FND_MSG_PUB.count_and_get( p_count => l_msg_count,
2521 p_data => l_temp_msg_data,
2522 p_encoded => fnd_api.g_false);
2523 l_msg_data := '(' || 1 || ')' || l_temp_msg_data;
2524 ELSE
2525 IF (l_msg_count > 0) THEN
2526 FOR i IN 1..l_msg_count LOOP
2527
2528 FND_MSG_PUB.get(
2529 p_encoded => 'F',
2530 p_data => l_temp_msg_data,
2531 p_msg_index_out => l_msg_index_out);
2532 IF(i = 1)THEN
2533 l_msg_data := '(' || i || ')' ||l_msg_data || l_temp_msg_data;
2534 ELSE
2535 l_msg_data := l_msg_data || '(' || i || ')' || l_temp_msg_data;
2536 END IF;
2537 END LOOP;
2538 END IF;
2539 END IF;
2540 RETURN l_msg_data;
2541 END GET_MSG_DATA;
2542
2543 FUNCTION is_valid_result_attribute(p_CharId IN NUMBER, p_QA_PLAN IN QA_PLAN_REC_TYPE) RETURN VARCHAR2
2544 IS
2545 l_addAttribute VARCHAR2(1);
2546 l_enabled_flag VARCHAR2(1);
2547
2548 BEGIN
2549 l_addAttribute := 'F';
2550 FOR i IN p_QA_PLAN.QA_PLAN_ATR_TBL.FIRST..p_QA_PLAN.QA_PLAN_ATR_TBL.LAST LOOP
2551 IF(p_CharId = p_QA_PLAN.QA_PLAN_ATR_TBL(i).CharId) THEN
2552 IF (p_QA_PLAN.QA_PLAN_ATR_TBL(i).IsReadOnly = 'F')THEN
2553 l_addAttribute := 'T';
2554 EXIT;
2555 END IF;
2556 END IF;
2557 END LOOP;
2558 RETURN l_addAttribute;
2559 END IS_VALID_RESULT_ATTRIBUTE;
2560
2561 END AHL_PRD_WO_PUB;