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