1 PACKAGE BODY AHL_PRD_DISPOSITION_PVT AS
2 /* $Header: AHLVDISB.pls 120.15.12010000.2 2008/12/09 01:42:51 jaramana ship $ */
3
4
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_PRD_DISPOSITION_PVT';
7 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
8 G_LOG_PREFIX CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.';
9
10 g_module_type VARCHAR2(30) := NULL;
11
12 G_PART_CHANGE_INSTALL CONSTANT VARCHAR2(1) := 'I';
13 G_PART_CHANGE_REMOVE CONSTANT VARCHAR2(1) := 'R';
14 G_PART_CHANGE_SWAP CONSTANT VARCHAR2(1) := 'S';
15
16 G_WO_RELEASED_STATUS CONSTANT VARCHAR2(1) := '3';
17
18 ------------------------
19 -- Declare Local Functions --
20 ------------------------
21 --FUNCTION isPositionEmpty(p_path_position_id IN NUMBER)
22 --RETURN BOOLEAN;
23
24 FUNCTION get_unit_instance_id(p_workorder_id IN NUMBER) RETURN NUMBER;
25 FUNCTION workorder_Editable(p_workorder_id IN NUMBER) RETURN BOOLEAN;
26 FUNCTION get_root_instance_id(p_instance_id IN NUMBER) RETURN NUMBER;
27 FUNCTION get_issued_quantity(p_disposition_id IN NUMBER) RETURN NUMBER;
28 -- Added function by rbhavsar on 09/27/2007 for Bug 6411059
29 FUNCTION root_node_in_uc_headers(p_instance_id IN NUMBER) RETURN BOOLEAN;
30
31 ------------------------
32 -- Declare Procedures --
33 ------------------------
34 PROCEDURE create_disposition(
35 p_api_version IN NUMBER := 1.0,
36 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
37 p_commit IN VARCHAR2 := FND_API.G_FALSE,
38 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
39 p_module_type IN VARCHAR2 := NULL,
40 p_x_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type,
41 -- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
42 p_mr_asso_tbl IN AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type,
43 x_return_status OUT NOCOPY VARCHAR2,
44 x_msg_count OUT NOCOPY NUMBER,
45 x_msg_data OUT NOCOPY VARCHAR2
46 );
47
48 PROCEDURE update_disposition(
49 p_api_version IN NUMBER := 1.0,
50 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
51 p_commit IN VARCHAR2 := FND_API.G_FALSE,
52 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
53 p_module_type IN VARCHAR2 := NULL,
54 p_x_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type,
55 -- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
56 p_mr_asso_tbl IN AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type,
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 x_msg_data OUT NOCOPY VARCHAR2
60 );
61
62 PROCEDURE CREATE_SR(
63 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
64 p_disposition_rec IN AHL_PRD_DISPOSITION_PVT.disposition_rec_type,
65 -- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
66 p_mr_asso_tbl IN AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type,
67 x_primary_sr_id OUT NOCOPY NUMBER,
68 x_non_routine_workorder_id OUT NOCOPY NUMBER,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY VARCHAR2
72 );
73
74
75 PROCEDURE Validate_Disposition_Types (
76 x_return_status OUT NOCOPY VARCHAR2,
77 x_msg_count OUT NOCOPY NUMBER,
78 x_msg_data OUT NOCOPY VARCHAR2,
79 p_disposition_rec IN AHL_PRD_DISPOSITION_PVT.disposition_rec_type);
80
81 PROCEDURE Calculate_Status (
82 p_disposition_Rec IN AHL_PRD_DISPOSITION_PVT.disposition_rec_type,
83 x_status_code OUT NOCOPY VARCHAR2);
84
85 PROCEDURE convert_values_to_ids(p_x_prd_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type);
86
87 PROCEDURE validate_for_create(p_disposition_rec IN AHL_PRD_DISPOSITION_PVT.disposition_rec_type);
88
89 PROCEDURE derive_columns(p_x_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type);
90
91 PROCEDURE validate_workorder(p_workorder_id IN NUMBER);
92
93 PROCEDURE validate_path_position(p_path_position_id IN NUMBER);
94
95 PROCEDURE validate_collection_id(p_collection_id IN NUMBER);
96
97 PROCEDURE validate_item(p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER, p_workorder_id IN NUMBER);
98
99 PROCEDURE validate_instance(p_instance_id IN NUMBER, p_workorder_id IN NUMBER, p_path_position_id IN NUMBER,
100 p_part_change_id IN NUMBER);
101
102 PROCEDURE validate_wo_operation(p_workorder_id IN NUMBER, p_wo_operation_id IN NUMBER);
103
104 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
105 -- Modified the API to take disposition quantity as an additional IN parameter.
106 PROCEDURE validate_part_change(p_part_change_id IN NUMBER, p_disp_instance_id IN NUMBER, p_disp_quantity IN NUMBER);
107
108 PROCEDURE validate_Item_Control(p_item_id IN NUMBER, p_org_id IN NUMBER,
109 p_serial_number IN VARCHAR2,
110 p_item_rev_number IN VARCHAR2,
111 p_lot_number IN VARCHAR2);
112
113 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
114 -- The API update_item_location and its use has been commented out. Its functionality will
115 -- now be handled in the API AHL_PRD_NONROUTINE_PVT.process_nonroutine_job.
116 /*
117 -- Following Procedure and Function added by jaramana on October 8, 2007 for ER 5903256
118 PROCEDURE update_item_location(p_workorder_id IN NUMBER,
119 p_instance_id IN NUMBER,
120 x_return_status OUT NOCOPY VARCHAR2);
121 */
122
123 FUNCTION Get_NonMWO_WIP_Entity_Id(p_workorder_id IN NUMBER) RETURN NUMBER;
124
125
126 ------------------------------------------------------------------
127 ----------- BEGIN DEFINITION OF PROCEDURES AND FUNCTIONS ---------
128 ------------------------------------------------------------------
129 -- Define procedure create_job_dispositions
130 -- This API is used to get all default dispositions for a job from its related route
131 -- and then put them into the dispostion entity.
132 PROCEDURE create_job_dispositions(
133 p_api_version IN NUMBER := 1.0,
134 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
135 p_commit IN VARCHAR2 := FND_API.G_FALSE,
136 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
137 x_return_status OUT NOCOPY VARCHAR2,
138 x_msg_count OUT NOCOPY NUMBER,
139 x_msg_data OUT NOCOPY VARCHAR2,
140 p_workorder_id IN NUMBER)
141 IS
142 l_api_name CONSTANT VARCHAR2(30) := 'create_job_dispositions';
143 l_api_version CONSTANT NUMBER := 1.0;
144 l_return_status VARCHAR2(1);
145 l_msg_count NUMBER;
146 l_msg_data VARCHAR2(2000);
147 l_relationship_id NUMBER;
148 l_children_no NUMBER;
149 l_route_mtl_req_tbl ahl_ltp_mtl_req_pvt.route_mtl_req_tbl_type;
150 l_unit_instance_id NUMBER;
151 l_installed_inst_id NUMBER;
152 l_serial_number csi_item_instances.serial_number%TYPE;
153 l_lot_number csi_item_instances.lot_number%TYPE;
154 l_inv_item_id NUMBER;
155 l_master_org_id NUMBER;
156 l_last_vld_org_id NUMBER;
157 l_disp_org_id NUMBER;
158 l_lowest_unit_inst_id NUMBER;
159 l_mapping_status VARCHAR2(30);
160 l_disposition_id NUMBER;
161 l_disposition_h_id NUMBER;
162 l_dummy_char VARCHAR2(1);
163 l_dummy_rowid VARCHAR2(100);
164 l_dummy_num NUMBER;
165 i NUMBER;
166 CURSOR get_job_attrs IS
167 /*
168 SELECT route_id,
169 item_instance_id,
170 scheduled_start_date,
171 job_status_code,
172 job_number,
173 organization_id
174 FROM ahl_workorders_v
175 WHERE workorder_id = p_workorder_id;
176 */
177 --AnRaj: Changed query, Perf Bug#4908609,Issue#1
178 select WO.route_id route_id,
179 NVL(VTS.INSTANCE_ID, VST.ITEM_INSTANCE_ID) item_instance_id,
180 WDJ.SCHEDULED_START_DATE scheduled_start_date,
181 WO.status_code job_status_code,
182 WO.workorder_name job_number,
183 VST.ORGANIZATION_ID organization_id
184 from AHL_WORKORDERS WO,
185 WIP_DISCRETE_JOBS WDJ,
186 AHL_VISITS_VL VST,
187 AHL_VISIT_TASKS_VL VTS
188 where WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID and
189 WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID and
190 VST.VISIT_ID = VTS.VISIT_ID and
191 WO.WORKORDER_ID = p_workorder_id;
192 l_job_attrs get_job_attrs%ROWTYPE;
193
194 CURSOR get_mtl_req_flags(c_rt_oper_material_id NUMBER) IS
195 SELECT --'N' include_flag, Once the column include_flag is added, then replace 'N' with it.
196 --Refer enhancement bug 3502592
197 exclude_flag
198 FROM ahl_rt_oper_materials
199 WHERE rt_oper_material_id = c_rt_oper_material_id;
200 l_mtl_req_flags get_mtl_req_flags%ROWTYPE;
201 CURSOR check_item_org(c_inventory_item_id NUMBER, c_organization_id NUMBER) IS
202 SELECT 'X'
203 FROM mtl_system_items_kfv
204 WHERE inventory_item_id = c_inventory_item_id
205 AND organization_id = c_organization_id;
206 CURSOR check_unit_instance(c_instance_id NUMBER) IS
207 SELECT csi_item_instance_id
208 FROM ahl_unit_config_headers
209 WHERE csi_item_instance_id = c_instance_id
210 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
211 CURSOR get_sub_unit_instance(c_instance_id NUMBER) IS
212 SELECT object_id
213 FROM csi_ii_relationships
214 WHERE object_id IN (SELECT csi_item_instance_id
215 FROM ahl_unit_config_headers
216 WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
217 START WITH subject_id = c_instance_id
218 AND relationship_type_code = 'COMPONENT-OF'
219 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
220 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
221 CONNECT BY subject_id = PRIOR object_id
222 AND relationship_type_code = 'COMPONENT-OF'
223 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
224 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
225 CURSOR get_instance_attrs(c_instance_id NUMBER) IS
226 SELECT serial_number,
227 lot_number,
228 inventory_item_id,
229 last_vld_organization_id,
230 inv_master_organization_id
231 FROM csi_item_instances
232 WHERE instance_id = c_instance_id;
233
234 BEGIN
235 --Initialize API return status to success
236 x_return_status := FND_API.G_RET_STS_SUCCESS;
237
238 --Standard Start of API savepoint
239 SAVEPOINT create_job_dispositions;
240
241 --Standard call to check for call compatibility.
242 IF NOT FND_API.compatible_api_call(
243 l_api_version,
244 p_api_version,
245 l_api_name,
246 G_PKG_NAME)
247 THEN
248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
249 END IF;
250
251 --Initialize message list if p_init_msg_list is set to TRUE.
252 IF FND_API.to_boolean( p_init_msg_list ) THEN
253 FND_MSG_PUB.initialize;
254 END IF;
255
256 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
257 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
258 G_LOG_PREFIX||l_api_name||': Begin API',
259 'At the start of the procedure and p_workorder_id ='||p_workorder_id);
260 END IF;
261
262 --Validate the input parameter
263 OPEN get_job_attrs;
264 FETCH get_job_attrs INTO l_job_attrs;
265 IF get_job_attrs%NOTFOUND THEN
266 --Comment out this check because the p_workoder_id passed should always be valid
267 /*
268 FND_MESSAGE.set_name('AHL', 'AHL_PRD_DISP_JOB_ID_INVALID');
269 FND_MESSAGE.set_token('JOBID', p_workorder_id);
270 FND_MSG_PUB.add;
271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
272 CLOSE get_job_attrs;
273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
274 */
275 CLOSE get_job_attrs;
276 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
277 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
278 G_LOG_PREFIX||l_api_name||': After normal execution',
279 'Returned but nothing done because the job is not in the view and it might be a master workorder!');
280 END IF;
281 RETURN;
282 ELSIF l_job_attrs.job_status_code IN (4, 12) THEN
283 --Complete(4) and Closed(12)
284 FND_MESSAGE.set_name('AHL', 'AHL_PRD_DISP_JOB_STS_INVALID');
285 FND_MESSAGE.set_token('JOB', l_job_attrs.job_number);
286 FND_MSG_PUB.add;
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 CLOSE get_job_attrs;
289 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
290 ELSIF l_job_attrs.item_instance_id IS NULL THEN
291 FND_MESSAGE.set_name('AHL', 'AHL_PRD_DISP_JOB_INST_NULL');
292 FND_MESSAGE.set_token('JOB', l_job_attrs.job_number);
293 FND_MSG_PUB.add;
294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
295 CLOSE get_job_attrs;
296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
297 ELSIF l_job_attrs.route_id IS NULL THEN
298 CLOSE get_job_attrs;
299 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
300 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
301 G_LOG_PREFIX||l_api_name||': After normal execution',
302 'Returned but nothing done because there is no route associated to the job');
303 END IF;
304 RETURN;
305 ELSE
306 CLOSE get_job_attrs;
307 END IF;
308
309 --Call ahl_ltp_mtl_req_pvt.get_route_mtl_req to get the default dispositions for the job
310
311 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
312 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
313 G_LOG_PREFIX||l_api_name||': In normal execution',
314 'Just before calling ahl_ltp_mtl_req_pvt.get_route_mtl_req and p_route_id='||
315 l_job_attrs.route_id||' p_item_instance_id='||l_job_attrs.item_instance_id||
316 ' p_requirement_date='||l_job_attrs.scheduled_start_date);
317 END IF;
318 ahl_ltp_mtl_req_pvt.get_route_mtl_req(
319 p_api_version => 1.0,
320 p_init_msg_list => FND_API.G_FALSE,
321 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
322 x_return_status => l_return_status,
323 x_msg_count => l_msg_count,
324 x_msg_data => l_msg_data,
325 p_route_id => l_job_attrs.route_id,
326 p_mr_route_id => NULL,
327 p_item_instance_id => l_job_attrs.item_instance_id,
328 p_requirement_date => l_job_attrs.scheduled_start_date,
329 p_request_type => 'PLANNED',
330 x_route_mtl_req_tbl => l_route_mtl_req_tbl);
331
332 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
333 RAISE FND_API.G_EXC_ERROR;
334 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
336 END IF;
337 ----dbms_output.put_line('After calling ltp API, the count='||l_route_mtl_req_tbl.COUNT);
338 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
339 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
340 G_LOG_PREFIX||l_api_name||': In normal execution',
341 'After calling LTP API and x_return_status='||l_return_status||
342 ' The count of the returned records = '||l_route_mtl_req_tbl.COUNT);
343 END IF;
344
345 IF l_route_mtl_req_tbl.COUNT > 0 THEN
346 FOR i IN l_route_mtl_req_tbl.FIRST..l_route_mtl_req_tbl.LAST LOOP
347 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
348 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
349 G_LOG_PREFIX||l_api_name||': In normal execution',
350 'In mtl_req_tbl loop and i='||i||' and count = '||l_route_mtl_req_tbl.COUNT);
351 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
352 G_LOG_PREFIX||l_api_name,
353 'For index ' || i || ', rt_oper_material_id = '||
354 l_route_mtl_req_tbl(i).rt_oper_material_id||', position_path_id = '||
355 l_route_mtl_req_tbl(i).position_path_id||', item_group_id = '||
356 l_route_mtl_req_tbl(i).item_group_id||', inventory_item_id = '||
357 l_route_mtl_req_tbl(i).inventory_item_id);
358 END IF;
359 OPEN get_mtl_req_flags(l_route_mtl_req_tbl(i).rt_oper_material_id);
360 FETCH get_mtl_req_flags INTO l_mtl_req_flags;
361 ----dbms_output.put_line('In loop '|| i||' and rt_oper_material_id='||l_route_mtl_req_tbl(i).rt_oper_material_id);
362 IF get_mtl_req_flags%NOTFOUND THEN
363 FND_MESSAGE.set_name('AHL', 'AHL_PRD_DISP_MTL_REQ_ID_INV');
364 FND_MESSAGE.set_token('REQID', l_route_mtl_req_tbl(i).rt_oper_material_id);
365 FND_MSG_PUB.add;
366 CLOSE get_mtl_req_flags;
367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
368 END IF;
369 CLOSE get_mtl_req_flags;
370 --dbms_output.put_line('Before check flag');
371 --Filter out the record which should not be included
372 IF (l_mtl_req_flags.exclude_flag = 'Y') THEN
373 l_route_mtl_req_tbl.DELETE(i);
374 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
375 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
376 G_LOG_PREFIX||l_api_name||': In normal execution',
377 'The disposition record is deleted and i='||i||' because the exclude_flag=Y');
378 END IF;
379 ----dbms_output.put_line('Yes it is deleted '||i);
380 END IF;
381 --This default logic is per Jay's requirement
382 ----dbms_output.put_line('Before apply Jay''s logic');
383 IF l_route_mtl_req_tbl.EXISTS(i) THEN
384 l_inv_item_id := l_route_mtl_req_tbl(i).inventory_item_id;
385 IF l_route_mtl_req_tbl(i).position_path_id IS NOT NULL THEN
386 l_route_mtl_req_tbl(i).item_group_id := NULL;
387 l_inv_item_id := NULL;
388 ELSIF l_route_mtl_req_tbl(i).item_group_id IS NOT NULL THEN
389 l_inv_item_id := NULL;
390 -- Added by jaramana on April 26, 2005 to fix the issue
391 -- where we are unable to update the Disposition for a Item Group with
392 -- a Revision Controlled Item when created from Push To Production
393 l_disp_org_id := l_job_attrs.organization_id;
394 ELSIF l_route_mtl_req_tbl(i).inventory_item_id IS NOT NULL THEN
395 OPEN check_item_org(l_route_mtl_req_tbl(i).inventory_item_id,
396 l_job_attrs.organization_id);
397 FETCH check_item_org INTO l_dummy_char;
398 IF check_item_org%NOTFOUND THEN
399 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
400 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
401 G_LOG_PREFIX||l_api_name||': In normal execution',
402 'inventory_item_id = '||l_route_mtl_req_tbl(i).inventory_item_id||
403 'job_organization_id = '||l_job_attrs.organization_id||
404 'The item does not exist in the organization of the job and this disposition record will be ignored.');
405 END IF;
406 l_route_mtl_req_tbl.DELETE(i);
407 ELSE
408 --In this case, the column organization_id in dispositions table will be the same as
409 --the job organization_id
410 l_disp_org_id := l_job_attrs.organization_id;
411 END IF;
412 CLOSE check_item_org;
413 END IF;
414 END IF;
415 --When position_path_id is not null, we should derive the instance attributes if
416 --the position is not empty
417 ----dbms_output.put_line('position_path_id='||l_route_mtl_req_tbl(i).position_path_id);
418 IF (l_route_mtl_req_tbl.EXISTS(i) AND
419 l_route_mtl_req_tbl(i).position_path_id IS NOT NULL) THEN
420 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
421 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
422 G_LOG_PREFIX||l_api_name||': In normal execution',
423 'Exists and position_path_id is not null');
424 END IF;
425 ----dbms_output.put_line('Yes exists');
426 --Check the job instance is a unit instance?
427 OPEN check_unit_instance(l_job_attrs.item_instance_id);
428 FETCH check_unit_instance INTO l_unit_instance_id;
429 IF check_unit_instance%NOTFOUND THEN
430 CLOSE check_unit_instance;
431 --The job instance is not a unit instance but a component instance, then get its
432 --lowest unit instance. Assuming the result of the hierarchy query is what we expected
433 --that the lowest sub unit instance_id will be the first one to be displayed.
434 OPEN get_sub_unit_instance(l_job_attrs.item_instance_id);
435 FETCH get_sub_unit_instance INTO l_unit_instance_id;
436 IF get_sub_unit_instance%NOTFOUND THEN
437 FND_MESSAGE.set_name('AHL', 'AHL_PRD_DISP_JOB_INST_INVALID');
438 FND_MESSAGE.set_token('INSTANCE', l_job_attrs.item_instance_id);
439 FND_MESSAGE.set_token('JOB', l_job_attrs.job_number);
440 FND_MSG_PUB.add;
441 CLOSE get_sub_unit_instance;
442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443 ELSE
444 CLOSE get_sub_unit_instance;
445 END IF;
446 ELSE
447 CLOSE check_unit_instance;
448 END IF;
449 --Call Path Position API to get the installed instance if the given unit has a
450 --matching path_position_id
451 ----dbms_output.put_line('Before calling path API');
452 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
453 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
454 G_LOG_PREFIX||l_api_name||': in normal execution',
455 'Before calling mc path position API and p_position_id ='||
456 l_route_mtl_req_tbl(i).position_path_id||' p_csi_item_instance_id='||
457 l_unit_instance_id);
458 END IF;
459 AHL_MC_PATH_POSITION_PVT.get_pos_instance(
460 p_api_version => 1.0,
461 p_init_msg_list => FND_API.G_FALSE,
462 p_commit => FND_API.G_FALSE,
463 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
464 x_return_status => l_return_status,
465 x_msg_count => l_msg_count,
466 x_msg_data => l_msg_data,
467 p_position_id => l_route_mtl_req_tbl(i).position_path_id,
468 p_csi_item_instance_id => l_unit_instance_id,
469 x_item_instance_id => l_installed_inst_id,
470 x_lowest_uc_csi_id => l_lowest_unit_inst_id,
471 x_mapping_status => l_mapping_status);
472
473 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
474 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
475 G_LOG_PREFIX||l_api_name||': Within the procedure ',
476 'Returned from call to AHL_MC_PATH_POSITION_PVT.get_pos_instance:' ||
477 ' x_return_status = ' || l_return_status ||
478 ', x_mapping_status = ' || l_mapping_status ||
479 ', x_item_instance_id = ' || l_installed_inst_id ||
480 ', x_lowest_uc_csi_id = ' || l_lowest_unit_inst_id);
481 END IF;
482
483 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
485 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
486 RAISE FND_API.G_EXC_ERROR;
487 END IF;
488
489 IF (l_mapping_status = 'NA') THEN
490 FND_MESSAGE.set_name('AHL', 'AHL_PRD_DISP_PATH_POS_INV');
491 FND_MESSAGE.set_token('POSITION', l_route_mtl_req_tbl(i).position_path_id);
492 FND_MESSAGE.set_token('INSTANCE', l_unit_instance_id);
493 FND_MSG_PUB.add;
494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
495 -- Position does not apply to current unit
496 ELSIF (l_mapping_status = 'EMPTY') THEN
497 l_installed_inst_id := NULL;
498 l_serial_number := NULL;
499 l_lot_number := NULL;
500 l_inv_item_id := NULL;
501 l_last_vld_org_id := NULL;
502 l_master_org_id := NULL;
503 l_disp_org_id := NULL;
504 ELSIF (l_mapping_status = 'MATCH') THEN
505 OPEN get_instance_attrs(l_installed_inst_id);
506 FETCH get_instance_attrs INTO
507 l_serial_number,
508 l_lot_number,
509 l_inv_item_id,
510 l_last_vld_org_id,
511 l_master_org_id;
512 CLOSE get_instance_attrs;
513 --Check to see whether the item of the instance exists in the job's organization,
514 --if not, then ignore this record
515 OPEN check_item_org(l_inv_item_id, l_job_attrs.organization_id);
516 FETCH check_item_org INTO l_dummy_char;
517 IF check_item_org%NOTFOUND THEN
518 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
519 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
520 G_LOG_PREFIX||l_api_name||': In normal execution',
521 'inventory_item_id = '||l_route_mtl_req_tbl(i).inventory_item_id||
522 'instance_id = '||l_installed_inst_id||
523 'job_organization_id = '||l_job_attrs.organization_id||
524 'The item of the instance does not exsit in the organization of the job and this disposition record will be ignored');
525 END IF;
526 l_route_mtl_req_tbl.DELETE(i);
527 ELSE
528 --In this case we need to ensure that the organization_id in
529 --dispositions table should be derived from the instance's organization
530 l_disp_org_id := nvl(l_last_vld_org_id, l_master_org_id);
531 END IF;
532 CLOSE check_item_org;
533 END IF;
534 END IF;
535 --dbms_output.put_line('Before calling table handler API');
536 --Insert the record into the disposition entity table
537 IF (l_route_mtl_req_tbl.EXISTS(i)) THEN
538 BEGIN
539 SELECT ahl_prd_dispositions_b_s.NEXTVAL
540 INTO l_disposition_id
541 FROM dual;
542 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
543 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
544 G_LOG_PREFIX||l_api_name||': Before calling table handler',
545 'disposition_id = '||l_disposition_id);
546 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
547 G_LOG_PREFIX||l_api_name||': Before calling table handler',
548 'workorder_id = '||p_workorder_id);
549 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
550 G_LOG_PREFIX||l_api_name||': Before calling table handler',
551 'path_position_id = '||l_route_mtl_req_tbl(i).position_path_id);
552 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
553 G_LOG_PREFIX||l_api_name||': Before calling table handler',
554 'inventory_item_id = '||l_route_mtl_req_tbl(i).inventory_item_id);
555 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
556 G_LOG_PREFIX||l_api_name||': Before calling table handler',
557 'inv_master_org_id = '||l_route_mtl_req_tbl(i).inv_master_org_id);
558 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
559 G_LOG_PREFIX||l_api_name||': Before calling table handler',
560 'item_group_id= '||l_route_mtl_req_tbl(i).item_group_id);
561 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
562 G_LOG_PREFIX||l_api_name||': Before calling table handler',
563 'l_installed_inst_id='||l_installed_inst_id);
564 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
565 G_LOG_PREFIX||l_api_name||': Before calling table handler',
566 'l_serial_number='||l_serial_number);
567 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
568 G_LOG_PREFIX||l_api_name||': Before calling table handler',
569 'l_lot_number='||l_lot_number);
570 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
571 G_LOG_PREFIX||l_api_name||': Before calling table handler',
572 'quantity='||l_route_mtl_req_tbl(i).quantity);
573 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
574 G_LOG_PREFIX||l_api_name||': Before calling table handler',
575 'uom_code='||l_route_mtl_req_tbl(i).uom_code);
576 END IF;
577
578 AHL_PRD_DISPOSITIONS_PKG.INSERT_ROW(
579 X_ROWID => l_dummy_rowid,
580 X_DISPOSITION_ID => l_disposition_id,
581 X_OBJECT_VERSION_NUMBER => 1.0,
582 X_WORKORDER_ID => p_workorder_id,
583 X_PART_CHANGE_ID => NULL,
584 X_PATH_POSITION_ID => l_route_mtl_req_tbl(i).position_path_id,
585 X_INVENTORY_ITEM_ID => l_inv_item_id,
586 X_ORGANIZATION_ID => l_disp_org_id,
587 X_ITEM_GROUP_ID => l_route_mtl_req_tbl(i).item_group_id,
588 X_CONDITION_ID => NULL,
589 X_INSTANCE_ID => l_installed_inst_id,
590 X_SERIAL_NUMBER => l_serial_number,
591 X_LOT_NUMBER => l_lot_number,
592 X_IMMEDIATE_DISPOSITION_CODE => NULL,
593 X_SECONDARY_DISPOSITION_CODE => NULL,
594 X_STATUS_CODE => NULL,
595 X_QUANTITY => l_route_mtl_req_tbl(i).quantity,
596 X_UOM => l_route_mtl_req_tbl(i).uom_code,
597 X_COLLECTION_ID => NULL,
598 X_PRIMARY_SERVICE_REQUEST_ID => NULL,
599 X_NON_ROUTINE_WORKORDER_ID => NULL,
600 X_WO_OPERATION_ID => NULL,
601 X_ITEM_REVISION => NULL,
602 --We may need to get the item_revision from ahl_rt_oper_materials later
603 X_ATTRIBUTE_CATEGORY => NULL,
604 X_ATTRIBUTE1 => NULL,
605 X_ATTRIBUTE2 => NULL,
606 X_ATTRIBUTE3 => NULL,
607 X_ATTRIBUTE4 => NULL,
608 X_ATTRIBUTE5 => NULL,
609 X_ATTRIBUTE6 => NULL,
610 X_ATTRIBUTE7 => NULL,
611 X_ATTRIBUTE8 => NULL,
612 X_ATTRIBUTE9 => NULL,
613 X_ATTRIBUTE10 => NULL,
614 X_ATTRIBUTE11 => NULL,
615 X_ATTRIBUTE12 => NULL,
616 X_ATTRIBUTE13 => NULL,
617 X_ATTRIBUTE14 => NULL,
618 X_ATTRIBUTE15 => NULL,
619 X_COMMENTS => NULL,
620 X_CREATION_DATE => SYSDATE,
621 X_CREATED_BY => FND_GLOBAL.user_id,
622 X_LAST_UPDATE_DATE => SYSDATE,
623 X_LAST_UPDATED_BY => FND_GLOBAL.user_id,
624 X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id);
625
626 --Insert the same record into the Disposition History table as well
627 SELECT AHL_PRD_DISPOSITIONS_B_H_S.NEXTVAL
628 INTO l_disposition_h_id
629 FROM dual;
630 AHL_PRD_DISPOSITIONS_B_H_PKG.INSERT_ROW(
631 X_ROWID => l_dummy_rowid,
632 X_DISPOSITION_H_ID => l_disposition_h_id,
633 X_DISPOSITION_ID => l_disposition_id,
634 X_OBJECT_VERSION_NUMBER => 1.0,
635 X_WORKORDER_ID => p_workorder_id,
636 X_PART_CHANGE_ID => NULL,
637 X_PATH_POSITION_ID => l_route_mtl_req_tbl(i).position_path_id,
638 X_INVENTORY_ITEM_ID => l_inv_item_id,
639 X_ORGANIZATION_ID => l_disp_org_id,
640 X_ITEM_GROUP_ID => l_route_mtl_req_tbl(i).item_group_id,
641 X_CONDITION_ID => NULL,
642 X_INSTANCE_ID => l_installed_inst_id,
643 X_SERIAL_NUMBER => l_serial_number,
644 X_LOT_NUMBER => l_lot_number,
645 X_IMMEDIATE_DISPOSITION_CODE => NULL,
646 X_SECONDARY_DISPOSITION_CODE => NULL,
647 X_STATUS_CODE => NULL,
648 X_QUANTITY => l_route_mtl_req_tbl(i).quantity,
649 X_UOM => l_route_mtl_req_tbl(i).uom_code,
650 X_COLLECTION_ID => NULL,
651 X_PRIMARY_SERVICE_REQUEST_ID => NULL,
652 X_NON_ROUTINE_WORKORDER_ID => NULL,
653 X_WO_OPERATION_ID => NULL,
654 X_ITEM_REVISION => NULL,
655 --We may need to get the item_revision from ahl_rt_oper_materials later
656 X_ATTRIBUTE_CATEGORY => NULL,
657 X_ATTRIBUTE1 => NULL,
658 X_ATTRIBUTE2 => NULL,
659 X_ATTRIBUTE3 => NULL,
660 X_ATTRIBUTE4 => NULL,
661 X_ATTRIBUTE5 => NULL,
662 X_ATTRIBUTE6 => NULL,
663 X_ATTRIBUTE7 => NULL,
664 X_ATTRIBUTE8 => NULL,
665 X_ATTRIBUTE9 => NULL,
666 X_ATTRIBUTE10 => NULL,
667 X_ATTRIBUTE11 => NULL,
668 X_ATTRIBUTE12 => NULL,
669 X_ATTRIBUTE13 => NULL,
670 X_ATTRIBUTE14 => NULL,
671 X_ATTRIBUTE15 => NULL,
672 X_COMMENTS => NULL,
673 X_CREATION_DATE => SYSDATE,
674 X_CREATED_BY => FND_GLOBAL.user_id,
675 X_LAST_UPDATE_DATE => SYSDATE,
676 X_LAST_UPDATED_BY => FND_GLOBAL.user_id,
677 X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id);
678 EXCEPTION
679 WHEN NO_DATA_FOUND THEN --This +100 is raised explicitly in INSERT_ROW
680 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INSERT_FAILED' );
681 FND_MSG_PUB.add;
682 WHEN OTHERS THEN
683 IF ( SQLCODE = -1 ) THEN --DUP_VAL_ON_INDEX
684 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_DUPLICATE_RECORD' );
685 FND_MSG_PUB.add;
686 ELSE
687 RAISE;
688 END IF;
689 END;
690 END IF;
691 --To clear the local variable, otherwise it carries the previous one if the current one
692 --should be null
693 l_installed_inst_id := NULL;
694 l_serial_number := NULL;
695 l_lot_number := NULL;
696 l_master_org_id := NULL;
697 l_last_vld_org_id := NULL;
698 l_disp_org_id := NULL;
699 l_inv_item_id := NULL;
700 END LOOP;
701 END IF;
702
703 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
704 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
705 G_LOG_PREFIX||l_api_name||': After normal execution',
706 'At the end of the procedure');
707 END IF;
708
709 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
710 l_msg_count := FND_MSG_PUB.count_msg;
711 IF l_msg_count > 0 THEN
712 x_msg_count := l_msg_count;
713 RAISE FND_API.G_EXC_ERROR;
714 END IF;
715
716 -- Perform the Commit (if requested)
717 IF FND_API.to_boolean(p_commit) THEN
718 COMMIT;
719 END IF;
720
721 -- Count and Get messages (optional)
722 FND_MSG_PUB.count_and_get(
723 p_encoded => FND_API.G_FALSE,
724 p_count => x_msg_count,
725 p_data => x_msg_data);
726
727 EXCEPTION
728 WHEN FND_API.G_EXC_ERROR THEN
729 ROLLBACK TO create_job_dispositions;
730 x_return_status := FND_API.G_RET_STS_ERROR ;
731 FND_MSG_PUB.count_and_get(
732 p_encoded => FND_API.G_FALSE,
733 p_count => x_msg_count,
734 p_data => x_msg_data);
735
736 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
737 ROLLBACK TO create_job_dispositions;
738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
739 FND_MSG_PUB.count_and_get(
740 p_encoded => FND_API.G_FALSE,
741 p_count => x_msg_count,
742 p_data => x_msg_data);
743
744 WHEN OTHERS THEN
745 ROLLBACK TO create_job_dispositions;
746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
747 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
748 THEN
749 FND_MSG_PUB.add_exc_msg(
750 p_pkg_name => G_PKG_NAME,
751 p_procedure_name => l_api_name,
752 p_error_text => SUBSTRB(SQLERRM,1,240));
753 END IF;
754 FND_MSG_PUB.count_and_get(
755 p_encoded => FND_API.G_FALSE,
756 p_count => x_msg_count,
757 p_data => x_msg_data);
758
759 END create_job_dispositions;
760
761 ------------------------------------------------------------------
762 -- Start of Comments --
763 -- Procedure name : process_disposition
764 -- Type : Private
765 -- Function : create or update a disposition based on the input from disposition record.
766 -- Pre-reqs :
767 -- Parameters :
768 --
769 -- Standard IN Parameters :
770 -- p_api_version IN NUMBER Default 1.0
771 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
772 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
773 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
774 -- Standard OUT Parameters :
775 -- x_return_status OUT VARCHAR2 Required
776 -- x_msg_count OUT NUMBER Required
777 -- x_msg_data OUT VARCHAR2 Required
778 --
779 -- p_module_type IN VARCHAR2 Required.
780 --
781 -- This parameter indicates the front-end form interface. The default value is 'JSP'. If the value
782 -- is JSP, then this API clears out all id columns and validations are done using the values based
783 -- on which the Id's are populated.
784 --
785 -- process_disposition Parameters:
786 --
787 -- p_x_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type Required
788 -- Disposition record
789 -- p_mr_asso_tbl IN AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type Required
790 -- Table of MRs associated to the Disposition's Primary NR
791 -- (Parameter added by jaramana on Oct 9, 2007 for ER 5883257)
792 --
793 -- Version :
794 -- Initial Version 1.0
795 --
796 -- End of Comments.
797 --------------------------------------------------------------------------------------------------------------
798
799 PROCEDURE process_disposition(
800 p_api_version IN NUMBER := 1.0,
801 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
802 p_commit IN VARCHAR2 := FND_API.G_FALSE,
803 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
804 p_module_type IN VARCHAR2 := NULL,
805 p_x_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type,
806 -- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
807 p_mr_asso_tbl IN AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type,
808 x_return_status OUT NOCOPY VARCHAR2,
809 x_msg_count OUT NOCOPY NUMBER,
810 x_msg_data OUT NOCOPY VARCHAR2) IS
811
812
813 l_api_version CONSTANT NUMBER := 1.0;
814 l_api_name CONSTANT VARCHAR2(30) := 'process_disposition';
815 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
816 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
817 l_commit VARCHAR2(1) := FND_API.G_FALSE;
818 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || 'process_disposition';
819 l_prev_err_count NUMBER;
820
821 BEGIN
822
823 -- Standard start of API savepoint
824 SAVEPOINT process_disposition_pvt;
825 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
826 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
827 END IF;
828
829 --dbms_output.put_line(SubStr('Begin Process_Disposition', 1, 255));
830 -- Standard call to check for call compatibility
831 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME )
832 THEN
833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
834 END IF;
835
836 -- Initialize message list if p_init_msg_list is set to TRUE
837 IF FND_API.To_Boolean( p_init_msg_list)
838 THEN
839 FND_MSG_PUB.Initialize;
840 --dbms_output.put_line(SubStr('Current MSG Count: ' || TO_CHAR(FND_MSG_PUB.count_msg), 1, 255));
841 END IF;
842 /* Begin Fix for 4071599 on Dec 22, 2004 by JR */
843 l_prev_err_count := NVL(FND_MSG_PUB.count_msg,0);
844 /* End Fix for 4071599 on Dec 22, 2004 by JR */
845
846 -- Initialize API return status to success
847 x_return_status := FND_API.G_RET_STS_SUCCESS;
848 g_module_type := p_module_type;
849
850 -- Begin Processing HERE
851
852 IF p_x_disposition_rec.operation_flag = G_OP_CREATE THEN
853 create_disposition( p_api_version,
854 l_init_msg_list,
855 l_commit,
856 p_validation_level,
857 p_module_type ,
858 p_x_disposition_rec,
859 p_mr_asso_tbl, -- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
860 x_return_status ,
861 x_msg_count,
862 x_msg_data);
863
864 ELSIF p_x_disposition_rec.operation_flag = G_OP_UPDATE THEN
865 update_disposition( p_api_version,
866 l_init_msg_list,
867 l_commit,
868 p_validation_level,
869 p_module_type ,
870 p_x_disposition_rec,
871 p_mr_asso_tbl, -- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
872 x_return_status ,
873 x_msg_count,
874 x_msg_data);
875
876 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
877 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'After calling update_dispositon- x_msg_data: ' || x_msg_data
878 || ' x_msg_count: ' || x_msg_count);
879 END IF;
880 END IF;
881
882 -- Check Error Message stack.
883 x_msg_count := FND_MSG_PUB.count_msg;
884
885
886 IF x_msg_count - l_prev_err_count > 0 THEN
887 RAISE FND_API.G_EXC_ERROR;
888 END IF;
889
890 -- Standard check of p_commit
891 IF FND_API.TO_BOOLEAN(p_commit) THEN
892 COMMIT WORK;
893 END IF;
894
895 -- Standard call to get message count and if count is 1, get message info
896 FND_MSG_PUB.Count_And_Get
897 ( p_count => x_msg_count,
898 p_data => x_msg_data,
899 p_encoded => fnd_api.g_false
900 );
901
902 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
903 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'End Procedure');
904 END IF;
905 --dbms_output.put_line(SubStr('End Process_Disposition', 1, 255));
906
907 EXCEPTION
908 WHEN FND_API.G_EXC_ERROR THEN
909 Rollback to process_disposition_pvt;
910 x_return_status := FND_API.G_RET_STS_ERROR;
911 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
912 p_data => x_msg_data,
913 p_encoded => fnd_api.g_false);
914 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
915 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, L_DEBUG_KEY, 'Execution Exception: ' || x_msg_data);
916 END IF;
917 --dbms_output.put_line(SubStr('Execution Exception', 1, 255));
918
919
920 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
921 Rollback to process_disposition_pvt;
922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
923 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
924 p_data => x_msg_data,
925 p_encoded => fnd_api.g_false);
926
927 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
928 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Unexpected Exception: ' || x_msg_data);
929 END IF;
930 --dbms_output.put_line(SubStr('Unexpected Exception', 1, 255));
931
932 WHEN OTHERS THEN
933 Rollback to process_disposition_pvt;
934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
935 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
936 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
937 p_procedure_name => l_api_name,
938 p_error_text => SUBSTR(SQLERRM,1,500));
939 END IF;
940 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
941 p_data => x_msg_data,
942 p_encoded => fnd_api.g_false);
943
944 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
945 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, L_DEBUG_KEY, 'Other Exception: ' || x_msg_data);
946 END IF;
947
948
949 --dbms_output.put_line(SubStr('Other Exception', 1, 255));
950 END process_disposition;
951
952
953 --------------CREATE_DISPOSITION---------------------------------------------------
954
955 PROCEDURE create_disposition(
956 p_api_version IN NUMBER := 1.0,
957 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
958 p_commit IN VARCHAR2 := FND_API.G_FALSE,
959 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
960 p_module_type IN VARCHAR2 := NULL,
961 p_x_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type,
962 -- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
963 p_mr_asso_tbl IN AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type,
964 x_return_status OUT NOCOPY VARCHAR2,
965 x_msg_count OUT NOCOPY NUMBER,
966 x_msg_data OUT NOCOPY VARCHAR2
967 ) IS
968
969 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
970 -- Cursor to check whether the disposition item is serialized or not.
971 CURSOR chk_non_serialized_csr(p_inventory_item_id NUMBER, p_item_org_id NUMBER) IS
972 SELECT 'X'
973 FROM mtl_system_items_b
974 WHERE inventory_item_id = p_inventory_item_id
975 AND organization_id = p_item_org_id
976 AND serial_number_control_code = 1;
977
978 -- SATHAPLI::FP OGMA Issue# 86 - Automatic Material Return, 27-Dec-2007
979 -- Cursor to fetch the part change details.
980 CURSOR part_change_dtls_csr(p_part_change_id IN NUMBER) IS
981 SELECT removed_instance_id, part_change_type
982 FROM ahl_part_changes
983 WHERE part_change_id = p_part_change_id;
984
985 l_disposition_h_id NUMBER;
986 l_dummy_char VARCHAR(30);
987 l_primary_service_request_id NUMBER;
988 l_non_routine_workorder_id NUMBER;
989 l_calculated_status VARCHAR(30);
990 l_return_status VARCHAR(30);
991
992 l_removed_instance_id NUMBER;
993 l_part_change_type VARCHAR2(1);
994 l_ahl_mtltxn_rec AHL_PRD_MTLTXN_PVT.Ahl_Mtltxn_Rec_Type;
995 l_dummy VARCHAR2(1);
996
997 l_msg_count NUMBER;
998 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || 'create_disposition';
999
1000 BEGIN
1001
1002
1003
1004 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1005 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1006 END IF;
1007 --dbms_output.put_line(SubStr('Begin Create_Disposition', 1, 255));
1008
1009 IF (p_module_type = 'JSP') THEN
1010 IF (p_x_disposition_rec.WORKORDER_ID = FND_API.G_MISS_NUM) THEN
1011 p_x_disposition_rec.WORKORDER_ID := null;
1012 END IF;
1013 IF (p_x_disposition_rec.PART_CHANGE_ID = FND_API.G_MISS_NUM) THEN
1014 p_x_disposition_rec.PART_CHANGE_ID := null;
1015 END IF;
1016 IF (p_x_disposition_rec.PATH_POSITION_ID = FND_API.G_MISS_NUM) THEN
1017 p_x_disposition_rec.PATH_POSITION_ID := null;
1018 END IF;
1019 IF (p_x_disposition_rec.INVENTORY_ITEM_ID = FND_API.G_MISS_NUM) THEN
1020 p_x_disposition_rec.INVENTORY_ITEM_ID := null;
1021 END IF;
1022 IF (p_x_disposition_rec.ITEM_GROUP_ID = FND_API.G_MISS_NUM) THEN
1023 p_x_disposition_rec.ITEM_GROUP_ID := null;
1024 END IF;
1025 IF (p_x_disposition_rec.CONDITION_ID = FND_API.G_MISS_NUM) THEN
1026 p_x_disposition_rec.CONDITION_ID := null;
1027 END IF;
1028 IF (p_x_disposition_rec.INSTANCE_ID = FND_API.G_MISS_NUM) THEN
1029 p_x_disposition_rec.INSTANCE_ID := null;
1030 END IF;
1031 IF (p_x_disposition_rec.SERIAL_NUMBER = FND_API.G_MISS_CHAR) THEN
1032 p_x_disposition_rec.SERIAL_NUMBER := null;
1033 END IF;
1034 IF (p_x_disposition_rec.LOT_NUMBER = FND_API.G_MISS_CHAR) THEN
1035 p_x_disposition_rec.LOT_NUMBER := null;
1036 END IF;
1037 IF (p_x_disposition_rec.IMMEDIATE_DISPOSITION_CODE = FND_API.G_MISS_CHAR) THEN
1038 p_x_disposition_rec.IMMEDIATE_DISPOSITION_CODE := null;
1039 END IF;
1040 IF (p_x_disposition_rec.SECONDARY_DISPOSITION_CODE = FND_API.G_MISS_CHAR) THEN
1041 p_x_disposition_rec.SECONDARY_DISPOSITION_CODE := null;
1042 END IF;
1043 IF (p_x_disposition_rec.STATUS_CODE = FND_API.G_MISS_CHAR) THEN
1044 p_x_disposition_rec.STATUS_CODE := null;
1045 END IF;
1046 IF (p_x_disposition_rec.QUANTITY = FND_API.G_MISS_NUM) THEN
1047 p_x_disposition_rec.QUANTITY := null;
1048 END IF;
1049 IF (p_x_disposition_rec.UOM = FND_API.G_MISS_CHAR) THEN
1050 p_x_disposition_rec.UOM := null;
1051 END IF;
1052 IF (p_x_disposition_rec.COLLECTION_ID = FND_API.G_MISS_NUM) THEN
1053 p_x_disposition_rec.COLLECTION_ID := null;
1054 END IF;
1055 IF (p_x_disposition_rec.PRIMARY_SERVICE_REQUEST_ID = FND_API.G_MISS_NUM) THEN
1056 p_x_disposition_rec.PRIMARY_SERVICE_REQUEST_ID := null;
1057 END IF;
1058 IF (p_x_disposition_rec.NON_ROUTINE_WORKORDER_ID = FND_API.G_MISS_NUM) THEN
1059 p_x_disposition_rec.NON_ROUTINE_WORKORDER_ID := null;
1060 END IF;
1061 IF (p_x_disposition_rec.WO_OPERATION_ID = FND_API.G_MISS_NUM) THEN
1062 p_x_disposition_rec.WO_OPERATION_ID := null;
1063 END IF;
1064 IF (p_x_disposition_rec.ITEM_REVISION = FND_API.G_MISS_CHAR) THEN
1065 p_x_disposition_rec.ITEM_REVISION := null;
1066 END IF;
1067 END IF;
1068
1069
1070 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
1071 convert_values_to_ids(p_x_disposition_rec);
1072 END IF;
1073 -- Derive Columns from other know columns
1074
1075 derive_columns(p_x_disposition_rec);
1076
1077 validate_for_create(p_x_disposition_rec);
1078
1079 --Validate Disposition Types
1080 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1081 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before validate disposition_type');
1082 END IF;
1083 --dbms_output.put_line(SubStr('Before validate disposition type ', 1, 255));
1084 Validate_Disposition_Types (
1085 -- p_api_version => p_api_version,
1086 -- p_init_msg_list => p_init_msg_list,
1087 -- p_commit => p_commit,
1088 -- p_validation_level => p_validation_level,
1089 x_return_status => x_return_status,
1090 x_msg_count => x_msg_count,
1091 x_msg_data => x_msg_data ,
1092 p_disposition_rec => p_x_disposition_rec);
1093
1094 --Calculate Status
1095 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1096 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before calculate_status');
1097 END IF;
1098 --dbms_output.put_line(SubStr('Before calculate status', 1, 255));
1099 Calculate_Status (
1100 p_disposition_Rec => p_x_disposition_Rec,
1101 x_status_code => l_calculated_status);
1102
1103 p_x_disposition_Rec.status_code := l_calculated_status;
1104
1105 --prepare for insert
1106 Select AHL_PRD_DISPOSITIONS_B_S.NEXTVAL into p_x_disposition_rec.disposition_id from dual;
1107 --setting object version number for create
1108 p_x_disposition_rec.object_version_number := 1;
1109 --setting up user/create/update information
1110 p_x_disposition_rec.created_by := fnd_global.user_id;
1111 p_x_disposition_rec.creation_date := SYSDATE;
1112 p_x_disposition_rec.last_updated_by := fnd_global.user_id;
1113 p_x_disposition_rec.last_update_date := SYSDATE;
1114 p_x_disposition_rec.last_update_login := fnd_global.login_id ;
1115
1116 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1117 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before Insert_Row');
1118 END IF;
1119 --dbms_output.put_line(SubStr('Create_Disposition - Before Insert_Row', 1, 255));
1120 AHL_PRD_DISPOSITIONS_PKG.INSERT_ROW(
1121 x_rowid => l_dummy_char,
1122 x_disposition_id => p_x_disposition_rec.disposition_id,
1123 x_object_version_number => p_x_disposition_rec.object_version_number,
1124 x_workorder_id => p_x_disposition_rec.workorder_id ,
1125 x_part_change_id => p_x_disposition_rec.part_change_id,
1126 x_path_position_id => p_x_disposition_rec.path_position_id,
1127 x_inventory_item_id => p_x_disposition_rec.inventory_item_id,
1128 x_organization_id => p_x_disposition_rec.item_org_id,
1129 x_item_group_id => p_x_disposition_rec.item_group_id,
1130 x_condition_id => p_x_disposition_rec.condition_id,
1131 x_instance_id => p_x_disposition_rec.instance_id,
1132 x_serial_number => p_x_disposition_rec.serial_number,
1133 x_lot_number => p_x_disposition_rec.lot_number,
1134 x_immediate_disposition_code => p_x_disposition_rec.immediate_disposition_code ,
1135 x_secondary_disposition_code => p_x_disposition_rec.secondary_disposition_code,
1136 x_status_code => p_x_disposition_rec.status_code,
1137 x_quantity => p_x_disposition_rec.quantity,
1138 x_uom => p_x_disposition_rec.uom,
1139 x_collection_id => p_x_disposition_rec.collection_id,
1140 x_primary_service_request_id => p_x_disposition_rec.primary_service_request_id ,
1141 x_non_routine_workorder_id => p_x_disposition_rec.non_routine_workorder_id,
1142 x_wo_operation_id => p_x_disposition_rec.wo_operation_id,
1143 x_item_revision => p_x_disposition_rec.item_revision,
1144 x_attribute_category => p_x_disposition_rec.attribute_category,
1145 x_attribute1 => p_x_disposition_rec.attribute1,
1146 x_attribute2 => p_x_disposition_rec.attribute2,
1147 x_attribute3 => p_x_disposition_rec.attribute3,
1148 x_attribute4 => p_x_disposition_rec.attribute4,
1149 x_attribute5 => p_x_disposition_rec.attribute5,
1150 x_attribute6 => p_x_disposition_rec.attribute6,
1151 x_attribute7 => p_x_disposition_rec.attribute7,
1152 x_attribute8 => p_x_disposition_rec.attribute8,
1153 x_attribute9 => p_x_disposition_rec.attribute9,
1154 x_attribute10 => p_x_disposition_rec.attribute10,
1155 x_attribute11 => p_x_disposition_rec.attribute11,
1156 x_attribute12 => p_x_disposition_rec.attribute12,
1157 x_attribute13 => p_x_disposition_rec.attribute13,
1158 x_attribute14 => p_x_disposition_rec.attribute14,
1159 x_attribute15 => p_x_disposition_rec.attribute15,
1160 x_comments => p_x_disposition_rec.comments,
1161 x_creation_date => p_x_disposition_rec.creation_date ,
1162 x_created_by => p_x_disposition_rec.created_by,
1163 x_last_update_date => p_x_disposition_rec.last_update_date,
1164 x_last_updated_by => p_x_disposition_rec.last_updated_by,
1165 x_last_update_login => p_x_disposition_rec.last_update_login
1166 );
1167 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1168 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'AfterInsert_Row');
1169 END IF;
1170
1171 -- create service request and non-routine job
1172 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
1173 -- Add a check for the create_work_order_option attribute too.
1174 IF p_x_disposition_rec.primary_service_request_id IS NULL AND p_x_disposition_rec.instance_id IS NOT NULL -- ITEM is tracked
1175 AND(p_x_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE') OR
1176 p_x_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_MRB'))
1177 AND(p_x_disposition_rec.create_work_order_option <> 'CREATE_SR_NO') THEN
1178
1179 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
1180 -- The option CREATE_WO_NO is not valid for non-serialized items. If chosen, throw an error.
1181 IF (p_x_disposition_rec.create_work_order_option = 'CREATE_WO_NO') THEN
1182 OPEN chk_non_serialized_csr(p_x_disposition_rec.inventory_item_id, p_x_disposition_rec.item_org_id);
1183 FETCH chk_non_serialized_csr INTO l_dummy;
1184 IF (chk_non_serialized_csr%FOUND) THEN
1185 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DIS_NON_SRL_SR');
1186 FND_MSG_PUB.ADD;
1187 END IF;
1188 CLOSE chk_non_serialized_csr;
1189 END IF;
1190
1191 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1192 l_msg_count := FND_MSG_PUB.count_msg;
1193 IF l_msg_count > 0 THEN
1194 x_msg_count := l_msg_count;
1195 RAISE FND_API.G_EXC_ERROR;
1196 END IF;
1197 Create_SR (p_init_msg_list => FND_API.G_FALSE,
1198 p_disposition_rec => p_x_disposition_rec,
1199 -- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
1200 p_mr_asso_tbl => p_mr_asso_tbl,
1201 x_primary_sr_id => l_primary_service_request_id,
1202 x_non_routine_workorder_id => l_non_routine_workorder_id,
1203 x_return_status => x_return_status,
1204 x_msg_count => x_msg_count,
1205 x_msg_data => x_msg_data);
1206 --reinitialize message stack and ignore any warning message
1207 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1208 FND_MSG_PUB.Initialize;
1209 END IF;
1210
1211 p_x_disposition_rec.primary_service_request_id := l_primary_service_request_id;
1212 p_x_disposition_rec.non_routine_workorder_id := l_non_routine_workorder_id;
1213
1214 --update the just modified field without changing object version number.
1215 UPDATE AHL_PRD_DISPOSITIONS_B SET primary_service_request_id = p_x_disposition_rec.primary_service_request_id,
1216 non_routine_workorder_id = p_x_disposition_rec.non_routine_workorder_id,
1217 status_code = p_x_disposition_rec.status_code
1218 WHERE disposition_id = p_x_disposition_rec.disposition_id;
1219 END IF;
1220
1221 Select AHL_PRD_DISPOSITIONS_B_H_S.NEXTVAL into l_disposition_h_id from dual;
1222 AHL_PRD_DISPOSITIONS_B_H_PKG.INSERT_ROW(
1223 x_rowid => l_dummy_char,
1224 x_disposition_h_id => l_disposition_h_id,
1225 x_disposition_id => p_x_disposition_rec.disposition_id,
1226 x_object_version_number => p_x_disposition_rec.object_version_number,
1227 x_workorder_id => p_x_disposition_rec.workorder_id ,
1228 x_part_change_id => p_x_disposition_rec.part_change_id,
1229 x_path_position_id => p_x_disposition_rec.path_position_id,
1230 x_inventory_item_id => p_x_disposition_rec.inventory_item_id,
1231 x_organization_id => p_x_disposition_rec.item_org_id,
1232 x_item_group_id => p_x_disposition_rec.item_group_id,
1233 x_condition_id => p_x_disposition_rec.condition_id,
1234 x_instance_id => p_x_disposition_rec.instance_id,
1235 x_serial_number => p_x_disposition_rec.serial_number,
1236 x_lot_number => p_x_disposition_rec.lot_number,
1237 x_immediate_disposition_code => p_x_disposition_rec.immediate_disposition_code ,
1238 x_secondary_disposition_code => p_x_disposition_rec.secondary_disposition_code,
1239 x_status_code => p_x_disposition_rec.status_code,
1240 x_quantity => p_x_disposition_rec.quantity,
1241 x_uom => p_x_disposition_rec.uom,
1242 x_collection_id => p_x_disposition_rec.collection_id,
1243 x_primary_service_request_id => p_x_disposition_rec.primary_service_request_id ,
1244 x_non_routine_workorder_id => p_x_disposition_rec.non_routine_workorder_id,
1245 x_wo_operation_id => p_x_disposition_rec.wo_operation_id,
1246 x_item_revision => p_x_disposition_rec.item_revision,
1247 x_attribute_category => p_x_disposition_rec.attribute_category,
1248 x_attribute1 => p_x_disposition_rec.attribute1,
1249 x_attribute2 => p_x_disposition_rec.attribute2,
1250 x_attribute3 => p_x_disposition_rec.attribute3,
1251 x_attribute4 => p_x_disposition_rec.attribute4,
1252 x_attribute5 => p_x_disposition_rec.attribute5,
1253 x_attribute6 => p_x_disposition_rec.attribute6,
1254 x_attribute7 => p_x_disposition_rec.attribute7,
1255 x_attribute8 => p_x_disposition_rec.attribute8,
1256 x_attribute9 => p_x_disposition_rec.attribute9,
1257 x_attribute10 => p_x_disposition_rec.attribute10,
1258 x_attribute11 => p_x_disposition_rec.attribute11,
1259 x_attribute12 => p_x_disposition_rec.attribute12,
1260 x_attribute13 => p_x_disposition_rec.attribute13,
1261 x_attribute14 => p_x_disposition_rec.attribute14,
1262 x_attribute15 => p_x_disposition_rec.attribute15,
1263 x_comments => p_x_disposition_rec.comments,
1264
1265 x_creation_date => p_x_disposition_rec.creation_date ,
1266 x_created_by => p_x_disposition_rec.created_by,
1267 x_last_update_date => p_x_disposition_rec.last_update_date,
1268 x_last_updated_by => p_x_disposition_rec.last_updated_by,
1269 x_last_update_login => p_x_disposition_rec.last_update_login
1270 );
1271
1272 -- SATHAPLI::FP OGMA Issue# 86 - Automatic Material Return, 27-Dec-2007
1273 -- If the instance was just removed in Serviceable condition return the part to the Visit Locator.
1274 -- Note that the ReturnTo_Workorder_Locator will return only if the locator is set at the Visit level.
1275 -- For FP OGMA Issue# 105 - Non-Serialized Item Maintenance, if the instance was removed in 'Inspection'
1276 -- condition, then it should not be returned to the locator.
1277 IF (NVL(x_return_status, FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS AND
1278 p_x_disposition_rec.part_change_id IS NOT NULL AND
1279 p_x_disposition_rec.condition_id <> NVL(fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE'), -1) AND
1280 p_x_disposition_rec.condition_id <> NVL(fnd_profile.value('AHL_MTL_MAT_STATUS_MRB'), -1) AND
1281 p_x_disposition_rec.condition_id <> NVL(fnd_profile.value('AHL_MTL_MAT_STATUS_INSPECTION'), -1)) THEN
1282 OPEN part_change_dtls_csr(p_x_disposition_rec.part_change_id);
1283 FETCH part_change_dtls_csr INTO l_removed_instance_id, l_part_change_type;
1284 CLOSE part_change_dtls_csr;
1285 IF (l_removed_instance_id = p_x_disposition_rec.instance_id AND -- Removed instance is the Disposition instance
1286 NVL(l_part_change_type, 'X') IN ('R', 'S')) THEN -- Removal or Swap
1287 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1288 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call AHL_PRD_PARTS_CHANGE_PVT.ReturnTo_Workorder_Locator with ' ||
1289 ' part change id = ' || p_x_disposition_rec.part_change_id ||
1290 ' and disposition_id = ' || p_x_disposition_rec.disposition_id);
1291 END IF;
1292 AHL_PRD_PARTS_CHANGE_PVT.ReturnTo_Workorder_Locator(p_part_change_id => p_x_disposition_rec.part_change_id,
1293 p_disposition_id => p_x_disposition_rec.disposition_id,
1294 x_return_status => x_return_status,
1295 x_msg_data => x_msg_data,
1296 x_msg_count => x_msg_count,
1297 x_ahl_mtltxn_rec => l_ahl_mtltxn_rec);
1298
1299 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1300 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from AHL_PRD_PARTS_CHANGE_PVT.ReturnTo_Workorder_Locator: ' ||
1301 ' x_return_status = ' || x_return_status);
1302 END IF;
1303 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1304 RAISE FND_API.G_EXC_ERROR;
1305 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1307 END IF;
1308 END IF;
1309 END IF;
1310
1311 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
1312 -- If the part is removed in 'Inspection' condition, then move the disposition to Complete status.
1313 IF (p_x_disposition_rec.condition_id = NVL(fnd_profile.value('AHL_MTL_MAT_STATUS_INSPECTION'), -1) AND
1314 p_x_disposition_rec.part_change_id IS NOT NULL) THEN
1315 UPDATE AHL_PRD_DISPOSITIONS_B
1316 SET status_code = 'COMPLETE'
1317 WHERE disposition_id = p_x_disposition_rec.disposition_id;
1318 END IF;
1319
1320 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1321 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'end create_disposition disposition_id:' ||p_x_disposition_rec.disposition_id);
1322 END IF;
1323 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1324 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'End Procedure');
1325 END IF;
1326 --dbms_output.put_line(SubStr('End Create_Disposition', 1, 255));
1327 END create_disposition;
1328
1329
1330 --------------UPDATE_DISPOSITION---------------------------------------------------
1331
1332 PROCEDURE update_disposition(
1333 p_api_version IN NUMBER := 1.0,
1334 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1335 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1336 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1337 p_module_type IN VARCHAR2 := NULL,
1338 p_x_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type,
1339 -- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
1340 p_mr_asso_tbl IN AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type,
1341 x_return_status OUT NOCOPY VARCHAR2,
1342 x_msg_count OUT NOCOPY NUMBER,
1343 x_msg_data OUT NOCOPY VARCHAR2
1344 ) IS
1345
1346 CURSOR disposition_csr(p_disposition_id IN NUMBER) IS
1347 SELECT *
1348 -- AnRaj: Changed query, Perf Bug#4908609,Issue#4
1349 -- FROM ahl_prd_dispositions_v
1350 FROM ahl_prd_dispositions_vl
1351 WHERE disposition_id = p_disposition_id ;
1352
1353
1354 CURSOR get_organization_csr(p_workorder_id IN NUMBER) IS
1355 SELECT vi.organization_id from ahl_workorders wo, ahl_visits_b vi
1356 WHERE wo.workorder_id = p_workorder_id
1357 AND wo.visit_id = vi.visit_id;
1358
1359 CURSOR val_lot_number_csr(p_lot_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
1360 SELECT 'x' FROM mtl_lot_numbers
1361 WHERE lot_number = p_lot_number
1362 AND inventory_item_id = p_inventory_item_id
1363 UNION
1364 SELECT 'x' FROM csi_item_instances csi
1365 WHERE lot_number = p_lot_number
1366 AND inventory_item_id = p_inventory_item_id;
1367
1368 CURSOR val_serial_number_csr(p_serial_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
1369 SELECT 'x' FROM mtl_serial_numbers
1370 WHERE serial_number = p_serial_number
1371 AND inventory_item_id = p_inventory_item_id
1372 UNION
1373 SELECT 'x' FROM csi_item_instances csi
1374 WHERE serial_number = p_serial_number
1375 AND inventory_item_id = p_inventory_item_id;
1376
1377 CURSOR item_revisions_csr (p_revision IN VARCHAR2, p_item_id IN NUMBER, p_organization_id IN NUMBER) IS
1378 SELECT 'x' FROM mtl_item_revisions
1379 WHERE inventory_item_id = p_item_id
1380 AND organization_id = p_organization_id
1381 AND revision = p_revision;
1382
1383 -- Added by jaramana on October 8, 2007 for ER 5903256
1384 CURSOR check_nr_wo_status_csr(p_nr_workorder_id IN NUMBER) IS
1385 SELECT 'Y'
1386 FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK, AHL_UNIT_EFFECTIVITIES_B UE
1387 WHERE WO.workorder_id = NVL(p_nr_workorder_id, -1)
1388 AND TSK.VISIT_TASK_ID = WO.VISIT_TASK_ID
1389 AND UE.UNIT_EFFECTIVITY_ID = TSK.UNIT_EFFECTIVITY_ID
1390 AND UE.STATUS_CODE IS NULL;
1391
1392 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
1393 -- Cursor to check whether the disposition item is serialized or not.
1394 CURSOR chk_non_serialized_csr(p_inventory_item_id NUMBER, p_item_org_id NUMBER) IS
1395 SELECT 'X'
1396 FROM mtl_system_items_b
1397 WHERE inventory_item_id = p_inventory_item_id
1398 AND organization_id = p_item_org_id
1399 AND serial_number_control_code = 1;
1400
1401 -- SATHAPLI::FP OGMA Issue# 86 - Automatic Material Return, 27-Dec-2007
1402 -- Cursor to fetch the part change details.
1403 CURSOR part_change_dtls_csr(p_part_change_id IN NUMBER) IS
1404 SELECT removed_instance_id, part_change_type
1405 FROM ahl_part_changes
1406 WHERE part_change_id = p_part_change_id;
1407
1408 l_exist VARCHAR(1);
1409
1410 -- SATHAPLI::Bug 7111116, 21-May-2008, fix start
1411 -- Cursor to get the first released non-master workorder id for a given NR summary workorder.
1412 CURSOR get_rel_nonmaster_wo_id_csr(c_nr_wo_id NUMBER) IS
1413 SELECT workorder_id
1414 FROM ahl_workorders
1415 WHERE master_workorder_flag = 'N'
1416 AND wip_entity_id IN
1417 (SELECT child_object_id
1418 FROM wip_sched_relationships
1419 START WITH parent_object_id = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = c_nr_wo_id)
1420 CONNECT BY parent_object_id = PRIOR child_object_id
1421 AND parent_object_type_id = PRIOR child_object_type_id
1422 AND relationship_type = 1
1423 )
1424 AND status_code = G_WO_RELEASED_STATUS
1425 ORDER BY workorder_id;
1426
1427 -- Cursor to get the removed instance id for a given part change id.
1428 CURSOR get_rem_inst_id_csr(c_part_change_id NUMBER) IS
1429 SELECT removed_instance_id
1430 FROM ahl_part_changes
1431 WHERE part_change_id = c_part_change_id;
1432 -- SATHAPLI::Bug 7111116, 21-May-2008, fix end
1433
1434 l_disposition_rec disposition_csr%ROWTYPE;
1435 -- l_disposition_rec AHL_PRD_DISPOSITION_PVT.disposition_rec_type;
1436 l_primary_service_request_id NUMBER;
1437 l_non_routine_workorder_id NUMBER;
1438 l_calculated_status VARCHAR(30);
1439 l_disposition_h_id NUMBER;
1440 l_dummy_char VARCHAR2(30);
1441 l_return_status VARCHAR2(30);
1442
1443 l_pos_empty BOOLEAN;
1444 l_assoc_quantity NUMBER;
1445
1446 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || 'update_disposition';
1447 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
1448 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1449 l_msg_count NUMBER;
1450 l_plan_id NUMBER;
1451 l_msg_data VARCHAR2(2000);
1452
1453 l_removed_instance_id NUMBER;
1454 l_part_change_type VARCHAR2(1);
1455 l_ahl_mtltxn_rec AHL_PRD_MTLTXN_PVT.Ahl_Mtltxn_Rec_Type;
1456 l_dummy VARCHAR(1);
1457
1458 -- SATHAPLI::Bug 7111116, 21-May-2008
1459 l_move_item_ins_tbl AHL_PRD_PARTS_CHANGE_PVT.move_item_instance_tbl_type;
1460 l_rel_nm_wo_id NUMBER;
1461 l_primary_sr_created BOOLEAN := FALSE;
1462
1463 BEGIN
1464
1465 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1466 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1467 END IF;
1468
1469
1470 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1471 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '', 'update Disposition_id' || p_x_disposition_rec.disposition_id );
1472 END IF;
1473
1474 --dbms_output.put_line(SubStr('Begin Update_Disposition', 1, 255));
1475
1476
1477 OPEN disposition_csr(p_x_disposition_rec.disposition_id);
1478 FETCH disposition_csr INTO l_disposition_rec;
1479
1480 IF (disposition_csr%NOTFOUND) THEN
1481 CLOSE disposition_csr; --close cursor before raising exeption
1482 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_NOT_FOUND');
1483 FND_MSG_PUB.ADD;
1484 RAISE FND_API.G_EXC_ERROR;
1485 END IF;
1486 CLOSE disposition_csr;
1487
1488 IF(p_x_disposition_rec.OBJECT_VERSION_NUMBER <> l_disposition_rec.OBJECT_VERSION_NUMBER) THEN
1489 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_RECORD_CHANGED');
1490 FND_MSG_PUB.ADD;
1491 RAISE FND_API.G_EXC_ERROR;
1492 END IF;
1493
1494 IF l_disposition_rec.status_code = 'TERMINATED' THEN
1495 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_DIS_UPDATE_TERMINATE'); --Cannot update a terminated disposition.
1496 FND_MSG_PUB.ADD;
1497 RAISE FND_API.G_EXC_ERROR;
1498 END IF;
1499
1500 --Handle JSP module
1501 --Handle GMiss and merge the updating record with the one from database
1502
1503 IF (p_module_type = 'JSP') THEN
1504 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1505 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Merging data');
1506 END IF;
1507 --dbms_output.put_line(SubStr('Update_disp:Merging data', 1, 255));
1508 IF p_x_disposition_rec.workorder_id IS NULL THEN
1509 p_x_disposition_rec.workorder_id := l_disposition_rec.workorder_id;
1510 ELSIF (p_x_disposition_rec.workorder_id = FND_API.G_MISS_NUM) THEN
1511 p_x_disposition_rec.workorder_id := null;
1512 END IF;
1513 IF p_x_disposition_rec.part_change_id IS NULL THEN
1514 p_x_disposition_rec.part_change_id := l_disposition_rec.part_change_id;
1515 ELSIF (p_x_disposition_rec.part_change_id = FND_API.G_MISS_NUM) THEN
1516 p_x_disposition_rec.part_change_id := null;
1517 END IF;
1518 IF p_x_disposition_rec.path_position_id IS NULL THEN
1519 p_x_disposition_rec.path_position_id := l_disposition_rec.path_position_id;
1520 ELSIF (p_x_disposition_rec.path_position_id = FND_API.G_MISS_NUM) THEN
1521 p_x_disposition_rec.path_position_id := null;
1522 END IF;
1523 IF p_x_disposition_rec.item_org_id IS NULL THEN
1524 p_x_disposition_rec.item_org_id := l_disposition_rec.organization_id;
1525 ELSIF (p_x_disposition_rec.item_org_id = FND_API.G_MISS_NUM) THEN
1526 p_x_disposition_rec.item_org_id := null;
1527 END IF;
1528 IF p_x_disposition_rec.inventory_item_id IS NULL THEN
1529 p_x_disposition_rec.inventory_item_id := l_disposition_rec.inventory_item_id;
1530 ELSIF (p_x_disposition_rec.inventory_item_id = FND_API.G_MISS_NUM) THEN
1531 p_x_disposition_rec.inventory_item_id := null;
1532 END IF;
1533
1534 IF p_x_disposition_rec.item_group_id IS NULL THEN
1535 p_x_disposition_rec.item_group_id := l_disposition_rec.item_group_id;
1536 ELSIF (p_x_disposition_rec.item_group_id = FND_API.G_MISS_NUM) THEN
1537 p_x_disposition_rec.item_group_id := null;
1538 END IF;
1539 IF p_x_disposition_rec.condition_id IS NULL THEN
1540 p_x_disposition_rec.condition_id := l_disposition_rec.condition_id;
1541 ELSIF (p_x_disposition_rec.condition_id = FND_API.G_MISS_NUM) THEN
1542 p_x_disposition_rec.condition_id := null;
1543 END IF;
1544 IF p_x_disposition_rec.instance_id IS NULL THEN
1545 p_x_disposition_rec.instance_id := l_disposition_rec.instance_id;
1546 ELSIF (p_x_disposition_rec.instance_id = FND_API.G_MISS_NUM) THEN
1547 p_x_disposition_rec.instance_id := null;
1548 END IF;
1549 IF p_x_disposition_rec.serial_number IS NULL THEN
1550 p_x_disposition_rec.serial_number := l_disposition_rec.serial_number;
1551 ELSIF (p_x_disposition_rec.serial_number = FND_API.G_MISS_CHAR) THEN
1552 p_x_disposition_rec.serial_number := null;
1553 END IF;
1554 IF p_x_disposition_rec.lot_number IS NULL THEN
1555 p_x_disposition_rec.lot_number := l_disposition_rec.lot_number;
1556 ELSIF (p_x_disposition_rec.lot_number = FND_API.G_MISS_CHAR) THEN
1557 p_x_disposition_rec.lot_number := null;
1558 END IF;
1559 IF p_x_disposition_rec.immediate_disposition_code IS NULL THEN
1560 p_x_disposition_rec.immediate_disposition_code := l_disposition_rec.immediate_disposition_code;
1561 ELSIF (p_x_disposition_rec.immediate_disposition_code = FND_API.G_MISS_CHAR) THEN
1562 p_x_disposition_rec.immediate_disposition_code := null;
1563 END IF;
1564 IF p_x_disposition_rec.secondary_disposition_code IS NULL THEN
1565 p_x_disposition_rec.secondary_disposition_code := l_disposition_rec.secondary_disposition_code;
1566 ELSIF (p_x_disposition_rec.secondary_disposition_code = FND_API.G_MISS_CHAR) THEN
1567 p_x_disposition_rec.secondary_disposition_code := null;
1568 END IF;
1569 IF p_x_disposition_rec.status_code IS NULL THEN
1570 p_x_disposition_rec.status_code := l_disposition_rec.status_code;
1571 ELSIF (p_x_disposition_rec.status_code = FND_API.G_MISS_CHAR) THEN
1572 p_x_disposition_rec.status_code := null;
1573 END IF;
1574 IF p_x_disposition_rec.quantity IS NULL THEN
1575 p_x_disposition_rec.quantity := l_disposition_rec.quantity;
1576 ELSIF (p_x_disposition_rec.quantity = FND_API.G_MISS_NUM) THEN
1577 p_x_disposition_rec.quantity := null;
1578 END IF;
1579 IF p_x_disposition_rec.uom IS NULL THEN
1580 p_x_disposition_rec.uom := l_disposition_rec.uom;
1581 ELSIF (p_x_disposition_rec.uom = FND_API.G_MISS_CHAR) THEN
1582 p_x_disposition_rec.uom := null;
1583 END IF;
1584 IF p_x_disposition_rec.comments IS NULL THEN
1585 p_x_disposition_rec.comments := l_disposition_rec.comments;
1586 ELSIF (p_x_disposition_rec.comments = FND_API.G_MISS_CHAR) THEN
1587 p_x_disposition_rec.comments := null;
1588 END IF;
1589 IF p_x_disposition_rec.collection_id IS NULL THEN
1590 p_x_disposition_rec.collection_id := l_disposition_rec.collection_id;
1591 ELSIF (p_x_disposition_rec.collection_id = FND_API.G_MISS_NUM) THEN
1592 p_x_disposition_rec.collection_id := null;
1593 END IF;
1594 IF p_x_disposition_rec.primary_service_request_id IS NULL THEN
1595 p_x_disposition_rec.primary_service_request_id := l_disposition_rec.primary_service_request_id;
1596 ELSIF (p_x_disposition_rec.primary_service_request_id = FND_API.G_MISS_NUM) THEN
1597 p_x_disposition_rec.primary_service_request_id := null;
1598 END IF;
1599 IF p_x_disposition_rec.non_routine_workorder_id IS NULL THEN
1600 p_x_disposition_rec.non_routine_workorder_id := l_disposition_rec.non_routine_workorder_id;
1601 ELSIF (p_x_disposition_rec.non_routine_workorder_id = FND_API.G_MISS_NUM) THEN
1602 p_x_disposition_rec.non_routine_workorder_id := null;
1603 END IF;
1604 IF p_x_disposition_rec.wo_operation_id IS NULL THEN
1605 p_x_disposition_rec.wo_operation_id := l_disposition_rec.wo_operation_id;
1606 ELSIF (p_x_disposition_rec.wo_operation_id = FND_API.G_MISS_NUM) THEN
1607 p_x_disposition_rec.wo_operation_id := null;
1608 END IF;
1609 IF p_x_disposition_rec.item_revision IS NULL THEN
1610 p_x_disposition_rec.item_revision := l_disposition_rec.item_revision;
1611 ELSIF (p_x_disposition_rec.item_revision = FND_API.G_MISS_CHAR) THEN
1612 p_x_disposition_rec.item_revision := null;
1613 END IF;
1614
1615 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1616 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'After Merging data');
1617 END IF;
1618 --dbms_output.put_line(SubStr('Update_disp:End Merging data', 1, 255));
1619 END IF;
1620 --END MERGING DATA
1621
1622 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
1623 convert_values_to_ids(p_x_disposition_rec);
1624 END IF;
1625
1626 --VALIDATE THAT THESE ATTRIBUTE SHOULD NOT BE CHANGED
1627 --dbms_output.put_line('Start validate for changes..............');
1628 IF nvl(p_x_disposition_rec.workorder_id, -1) <> nvl(l_disposition_rec.workorder_id, -1) THEN
1629 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_WO_ID_CHNG'); --Workorder id Cannot be change
1630 FND_MSG_PUB.ADD;
1631 END IF;
1632 IF l_disposition_rec.part_change_id IS NOT NULL
1633 AND nvl (p_x_disposition_rec.part_change_id, -1) <> l_disposition_rec.part_change_id THEN
1634 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_PARTCHG_ID_CHNG'); --Part Change Cannot be change
1635 FND_MSG_PUB.ADD;
1636 END IF;
1637
1638 IF l_disposition_rec.inventory_item_id IS NOT NULL
1639 AND nvl(p_x_disposition_rec.inventory_item_id, -1) <> l_disposition_rec.inventory_item_id THEN
1640 --dbms_output.put_line('In Error message AHL_PRD_DIS_ITEM_REV_CHNG');
1641 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_ITEM_CHG'); --Item cannot be changed
1642 FND_MSG_PUB.ADD;
1643 END IF;
1644
1645 /* IF nvl (p_x_disposition_rec.item_revision, ' ') <> nvl(l_disposition_rec.item_revision, ' ') THEN
1646 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_ITEM_REV_CHNG'); --ITem Revision Cannot be change
1647 FND_MSG_PUB.ADD;
1648 END IF;
1649 */
1650 IF nvl(p_x_disposition_rec.item_group_id, -1) <> nvl(l_disposition_rec.item_group_id, -1) THEN
1651 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_ITEM_GRP_CHNG'); --Item Group Cannot be change
1652 FND_MSG_PUB.ADD;
1653 END IF;
1654 --IF nvl(p_x_disposition_rec.instance_id, -1) <> nvl(l_disposition_rec.instance_id, -1) THEN
1655 --Instance can only be changed once (originally it is not changeable at all)
1656 --Updated by Jerry on 01/26/2005 for fixing bug 4089750
1657 IF (l_disposition_rec.instance_id IS NOT NULL AND (p_x_disposition_rec.instance_id IS NULL OR
1658 p_x_disposition_rec.instance_id <> l_disposition_rec.instance_id)) THEN
1659 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_INST_CHNG');
1660 FND_MSG_PUB.ADD;
1661 ELSIF ((l_disposition_rec.instance_id IS NULL AND
1662 p_x_disposition_rec.instance_id IS NULL AND
1663 -- AnRaj: Changed query, Perf Bug#4908609,Issue#4
1664 -- l_disposition_rec.trackable_flag = 'Y' AND
1665 (l_disposition_rec.instance_id is not null or l_disposition_rec.path_position_id is not null) AND
1666 p_x_disposition_rec.status_code <> 'TERMINATED' AND --Added on 03/02/05 when verifying
1667 l_disposition_rec.part_change_id IS NULL AND --bug fix 4093642 on SCMTSB2
1668 p_x_disposition_rec.part_change_id IS NULL) AND -- Added by rbhavsar on Aug 07, 2007 for FP bug 6318339, base bug 6058419
1669 (l_disposition_rec.immediate_disposition_code is NULL)) THEN
1670 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_INSTANCE_VALUE_REQ');
1671 FND_MSG_PUB.ADD;
1672 ELSIF (l_disposition_rec.instance_id IS NULL AND p_x_disposition_rec.instance_id IS NOT NULL) THEN
1673 BEGIN
1674 SELECT 'X' INTO l_dummy_char
1675 FROM csi_item_instances
1676 WHERE instance_id = p_x_disposition_rec.instance_id
1677 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
1678 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1679 EXCEPTION
1680 WHEN NO_DATA_FOUND THEN
1681 FND_MESSAGE.set_name(G_APP_NAME,'AHL_PRD_INV_INST_NUM');
1682 FND_MESSAGE.set_token('INTANCE_NUM', p_x_disposition_rec.instance_number);
1683 FND_MSG_PUB.ADD;
1684 END;
1685 END IF;
1686 /* Commented out on 02/02/2005 by Jerry for fixing bug 4089750
1687 IF nvl(p_x_disposition_rec.instance_id, -1) <> nvl(l_disposition_rec.instance_id, -1) THEN
1688 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_INST_CHNG'); --Instance Cannot be change
1689 FND_MSG_PUB.ADD;
1690 END IF;
1691 */
1692 /* Begin Fix for 4075758 on Dec 21. 2004 */
1693 -- For non-tracked, serial or lot controlled items, the serial
1694 -- and/or and lot number may be provided during update mode also
1695 /*****
1696 IF nvl(p_x_disposition_rec.serial_number, ' ') <> nvl(l_disposition_rec.serial_number, ' ') THEN
1697 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_SERIAL_NUM_CHNG'); --Serial Number Cannot be change
1698 FND_MSG_PUB.ADD;
1699 END IF;
1700
1701 IF nvl(p_x_disposition_rec.lot_number, ' ') <> nvl(l_disposition_rec.lot_number, ' ') THEN
1702 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_LOT_NUM_CHNG'); --Lot Number Cannot be change
1703 FND_MSG_PUB.ADD;
1704 END IF;
1705 ******/
1706 IF nvl(p_x_disposition_rec.serial_number, ' ') <> nvl(l_disposition_rec.serial_number, nvl(p_x_disposition_rec.serial_number, ' ')) THEN
1707 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_SERIAL_NUM_CHNG'); --Serial Number Can be set once
1708 FND_MSG_PUB.ADD;
1709 END IF;
1710 IF nvl(p_x_disposition_rec.serial_number, ' ') <> nvl(l_disposition_rec.serial_number, ' ') THEN
1711 -- Serial Number has been set: Validate
1712 OPEN val_serial_number_csr(p_x_disposition_rec.serial_number, p_x_disposition_rec.inventory_item_id);
1713 FETCH val_serial_number_csr INTO l_exist;
1714 IF (val_serial_number_csr%NOTFOUND) THEN
1715 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_INV_SERIAL'); -- Invalid serial number and item combination
1716 FND_MSG_PUB.ADD;
1717 END IF;
1718 CLOSE val_serial_number_csr;
1719 END IF;
1720
1721 IF nvl(p_x_disposition_rec.lot_number, ' ') <> nvl(l_disposition_rec.lot_number, nvl(p_x_disposition_rec.lot_number, ' ')) THEN
1722 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_DIS_LOT_NUM_CHNG'); --Lot Number Can be set once
1723 FND_MSG_PUB.ADD;
1724 END IF;
1725 IF nvl(p_x_disposition_rec.lot_number, ' ') <> nvl(l_disposition_rec.lot_number, ' ') THEN
1726 -- Lot Number has been set: Validate
1727 OPEN val_lot_number_csr(p_x_disposition_rec.lot_number, p_x_disposition_rec.inventory_item_id);
1728 FETCH val_lot_number_csr INTO l_exist;
1729 IF (val_lot_number_csr%NOTFOUND) THEN
1730 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_DIS_INV_LOT'); -- Invalid Lot number and item combination
1731 FND_MSG_PUB.ADD;
1732 END IF;
1733 CLOSE val_lot_number_csr;
1734 END IF;
1735
1736 --Item Revision
1737 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1738 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Item Revision old: ' || l_disposition_rec.item_revision || ' new: ' || p_x_disposition_rec.item_revision);
1739 END IF;
1740 IF nvl(p_x_disposition_rec.item_revision, ' ') <> nvl(l_disposition_rec.item_revision, nvl(p_x_disposition_rec.item_revision, ' ')) THEN
1741 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_ITEM_REV_CHNG'); --item revision Number Can be set once
1742 FND_MSG_PUB.ADD;
1743 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1744 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Item Revision Change old: ' || l_disposition_rec.item_revision || ' new: ' || p_x_disposition_rec.item_revision);
1745 END IF;
1746 END IF;
1747 IF nvl(p_x_disposition_rec.item_revision, ' ') <> nvl(l_disposition_rec.item_revision, ' ') THEN
1748 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1749 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Validate item revision: ' || p_x_disposition_rec.item_revision);
1750 END IF;
1751 -- item revision Number has been set: Validate
1752 OPEN item_revisions_csr (p_x_disposition_rec.item_revision, p_x_disposition_rec.inventory_item_id, p_x_disposition_rec.item_org_id);
1753 FETCH item_revisions_csr INTO l_exist;
1754 IF (item_revisions_csr%NOTFOUND) THEN
1755 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_INV_ITEM_REV'); -- Invalid serial number and item combination
1756 FND_MSG_PUB.ADD;
1757 END IF;
1758 CLOSE item_revisions_csr;
1759 END IF;
1760
1761 /* End Fix for 4075758 on Dec 21. 2004 */
1762
1763 IF l_disposition_rec.Collection_Id IS NOT NULL
1764 AND nvl(p_x_disposition_rec.Collection_id, -1) <> nvl(l_disposition_rec.Collection_Id, -1) THEN
1765 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_COLLECT_ID_CHNG'); --Workorder Cannot be changed
1766 FND_MSG_PUB.ADD;
1767 END IF;
1768 IF nvl(p_x_disposition_rec.Primary_Service_Request_id , -1) <> nvl(l_disposition_rec.Primary_Service_Request_id , -1) THEN
1769 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_SR_ID_CHNG'); --Primary Service Request Cannot be changed
1770 FND_MSG_PUB.ADD;
1771 END IF;
1772 IF nvl(p_x_disposition_rec.Non_Routine_Workorder_id , -1) <> nvl(l_disposition_rec.Non_Routine_Workorder_id , -1) THEN
1773 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_NONRTN_WO_CHNG'); --Non Routine Workorder Cannot be changed
1774 FND_MSG_PUB.ADD;
1775 END IF;
1776
1777 IF (l_disposition_rec.UOM IS NOT NULL AND (nvl(p_x_disposition_rec.UOM, 'null') <> l_disposition_rec.UOM)) THEN
1778 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_UOM_CHNG'); --UOM Cannot be changed
1779 FND_MSG_PUB.ADD;
1780 END IF;
1781
1782
1783 IF(workorder_editable(p_x_disposition_rec.workorder_id) = FALSE) THEN
1784 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_WO_NOT_EDITABLE'); --Cannot Update Disposition Because Workorder is not editable.
1785 FND_MSG_PUB.ADD;
1786 END IF;
1787
1788 --Disposition Status can only be changed to Terminated by the user.
1789 IF nvl(p_x_disposition_rec.status_code, 'dummy') <> 'TERMINATED' AND nvl(p_x_disposition_rec.status_code, 'dummy') <> nvl(l_disposition_rec.status_code, 'dummy') THEN
1790 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_STATUS_CHNG'); --Disposition Status cannot be changed excepte changed
1791 FND_MSG_PUB.ADD;
1792 END IF;
1793
1794 --if existing item is null and item group is not null then item need to be enter
1795 IF l_disposition_rec.inventory_item_id IS NULL AND l_disposition_rec.item_group_id IS NOT NULL THEN
1796 IF p_x_disposition_rec.inventory_item_id IS NOT NULL THEN
1797 --derive organization
1798 OPEN get_organization_csr(p_x_disposition_rec.workorder_id);
1799 FETCH get_organization_csr INTO p_x_disposition_rec.item_org_id;
1800 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1801 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Derived Org: ' || p_x_disposition_rec.item_org_id);
1802 END IF;
1803 --dbms_output.put_line(SubStr('Derived Org: ' || p_x_disposition_rec.item_org_id, 1, 255));
1804 CLOSE get_organization_csr;
1805
1806 --dbms_output.put_line(SubStr('Update_disp:allow change item', 1, 255));
1807
1808
1809 validate_item(p_x_disposition_rec.inventory_item_id, p_x_disposition_rec.item_org_id, p_x_disposition_rec.workorder_id);
1810
1811 --start fix Bug#4075758 Item is non-tracked
1812 validate_Item_Control(p_x_disposition_rec.inventory_item_id , p_x_disposition_rec.item_org_id,
1813 p_x_disposition_rec.serial_number,
1814 p_x_disposition_rec.item_revision,
1815 p_x_disposition_rec.lot_number);
1816 --end Bug#4075758
1817 ELSE
1818 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_ITEM_REQ'); --Item is required
1819 FND_MSG_PUB.ADD;
1820 END IF;
1821 ELSE --ignore the new organization id change just use the existing one
1822 p_x_disposition_rec.item_org_id := l_disposition_rec.organization_id;
1823 END IF;
1824
1825 --Validate Workorder operation change
1826 IF(nvl(p_x_disposition_rec.wo_operation_id, -1) <> nvl(l_disposition_rec.wo_operation_id, -1)) THEN
1827 validate_wo_operation(p_x_disposition_rec.workorder_id, p_x_disposition_rec.wo_operation_id);
1828
1829 END IF;
1830
1831 IF p_x_disposition_rec.path_position_id IS NOT NULL AND p_x_disposition_rec.instance_id IS NULL THEN
1832 l_pos_empty := TRUE;
1833 END IF;
1834
1835 --Validate Quantity Change
1836 IF p_x_disposition_rec.quantity IS NULL AND l_pos_empty <> TRUE THEN
1837 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_QTY_REQ'); --Quantity is required
1838 FND_MSG_PUB.ADD;
1839 ELSIF p_x_disposition_rec.quantity <= 0 THEN
1840 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_QTY_LESS_ZERO'); --Invalid Quantity. Quantity must be greater than zero
1841 FND_MSG_PUB.ADD;
1842 ELSIF p_x_disposition_rec.quantity <> l_disposition_rec.quantity THEN
1843 IF nvl(p_x_disposition_rec.status_code, ' ') = 'COMPLETE' THEN
1844 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_DIS_QTY_CHNG'); -- Quantity cannot be changed
1845 FND_MSG_PUB.ADD;
1846 END IF;
1847
1848 /* Commented out on 02/02/2005 by Jerry for fixing bug 4089750
1849 IF p_x_disposition_rec.instance_id IS NOT NULL THEN
1850 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_DIS_QTY_INST_QTY'); -- Quantity cannot be different from instance's quantity
1851 FND_MSG_PUB.ADD;
1852 END IF;
1853 */
1854
1855 l_assoc_quantity := AHL_PRD_DISP_MTL_TXN_PVT.Calculate_Txned_Qty(p_x_disposition_rec.disposition_id);
1856 IF (p_x_disposition_rec.quantity < l_assoc_quantity) THEN
1857 FND_MESSAGE.set_name(G_APP_NAME, 'AHL_PRD_DIS_LESS_THAN_ASSC_QTY'); -- Quantity cannot be less then material transaction associated quantity
1858 FND_MESSAGE.Set_Token('QUANTITY', p_x_disposition_rec.quantity );
1859 FND_MESSAGE.Set_Token('ASSC_QTY', l_assoc_quantity );
1860 FND_MSG_PUB.ADD;
1861 END IF;
1862
1863 END IF;
1864 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1865 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'end validate changes');
1866 END IF;
1867 --dbms_output.put_line(SubStr('Update_disp:End Validate Change', 1, 255));
1868
1869 -- Validate part_change
1870 IF(p_x_disposition_rec.part_change_id IS NOT NULL AND l_disposition_rec.part_change_id IS NULL) THEN
1871 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
1872 -- Modified the API to take disposition quantity as an additional IN parameter.
1873 validate_part_change(p_x_disposition_rec.part_change_id, p_x_disposition_rec.instance_id, p_x_disposition_rec.quantity);
1874 END IF;
1875
1876 --COLLECTION ID
1877 -- Added by jaramana on March 25, 2005 to fix bug 4243200
1878 -- First check if a QA PLan is defined in the workorder Org.
1879 AHL_QA_RESULTS_PVT.get_qa_plan( p_api_version => 1.0,
1880 p_init_msg_list => FND_API.G_False,
1881 p_commit => FND_API.G_FALSE,
1882 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1883 p_default => FND_API.G_FALSE,
1884 p_organization_id => p_x_disposition_rec.item_org_id,
1885 p_transaction_number => 2004, -- MRB_TRANSACTION_NUMBER
1886 p_col_trigger_value => fnd_profile.value('AHL_MRB_DISP_PLAN_TYPE'),
1887 x_return_status => l_return_status,
1888 x_msg_count => l_msg_count,
1889 x_msg_data => l_msg_data,
1890 x_plan_id => l_plan_id);
1891 /**
1892 IF p_x_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_MRB') AND p_x_disposition_rec.instance_id IS NOT NULL THEN -- status is MRB and tracked item
1893 **/
1894 IF p_x_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_MRB') AND
1895 p_x_disposition_rec.instance_id IS NOT NULL AND
1896 l_plan_id IS NOT NULL THEN -- status is MRB and tracked item and QA plan is defined in Org
1897 IF p_x_disposition_rec.collection_id IS NULL THEN
1898 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_QA_RESULT_REQ'); -- QA Result Required
1899 FND_MSG_PUB.ADD;
1900 ELSE
1901 validate_collection_id(p_x_disposition_rec.collection_id);
1902 END IF;
1903 END IF;
1904 -- End fix for bug 4243200
1905
1906
1907 -- SERVICE REQUEST
1908 IF ((p_x_disposition_rec.instance_id IS NULL
1909 OR
1910 (nvl(p_x_disposition_rec.condition_id, -1) <> fnd_profile.value('AHL_MTL_MAT_STATUS_MRB')
1911 AND nvl(p_x_disposition_rec.condition_id, -1) <> fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE')
1912 )
1913 )
1914 AND
1915 ( p_x_disposition_rec.summary IS NOT NULL OR p_x_disposition_rec.problem_code IS NOT NULL
1916 OR p_x_disposition_rec.severity_id IS NOT NULL
1917 )) THEN
1918 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_SR_NOT_REQ'); --Non Conformance (SR) information is not required
1919 FND_MSG_PUB.ADD;
1920 END IF;
1921
1922 -- create service request and non-routine job
1923 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
1924 -- Add a check for the create_work_order_option attribute too.
1925 IF p_x_disposition_rec.primary_service_request_id IS NULL AND p_x_disposition_rec.instance_id IS NOT NULL -- AND ITEM is tracked
1926 AND(p_x_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE') OR
1927 p_x_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_MRB'))
1928 AND(p_x_disposition_rec.create_work_order_option <> 'CREATE_SR_NO') THEN
1929 --dbms_output.put_line(SubStr('Update_Disp Before Create_SR', 1, 255));
1930
1931 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
1932 -- The option CREATE_WO_NO is not valid for non-serialized items. If chosen, throw an error.
1933 IF (p_x_disposition_rec.create_work_order_option = 'CREATE_WO_NO') THEN
1934 OPEN chk_non_serialized_csr(p_x_disposition_rec.inventory_item_id, p_x_disposition_rec.item_org_id);
1935 FETCH chk_non_serialized_csr INTO l_dummy;
1936 IF (chk_non_serialized_csr%FOUND) THEN
1937 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DIS_NON_SRL_SR');
1938 FND_MSG_PUB.ADD;
1939 END IF;
1940 CLOSE chk_non_serialized_csr;
1941 END IF;
1942
1943 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1944 l_msg_count := FND_MSG_PUB.count_msg;
1945 IF l_msg_count > 0 THEN
1946 x_msg_count := l_msg_count;
1947 RAISE FND_API.G_EXC_ERROR;
1948 END IF;
1949 Create_SR (p_init_msg_list => FND_API.G_FALSE,
1950 p_disposition_rec => p_x_disposition_rec,
1951 -- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
1952 p_mr_asso_tbl => p_mr_asso_tbl,
1953 x_primary_sr_id => l_primary_service_request_id,
1954 x_non_routine_workorder_id => l_non_routine_workorder_id,
1955 x_return_status => x_return_status,
1956 x_msg_count => x_msg_count,
1957 x_msg_data => x_msg_data);
1958 --reinitialize message stack and ignore any warning message
1959 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1960 FND_MSG_PUB.Initialize;
1961
1962 -- SATHAPLI::Bug 7111116, 21-May-2008
1963 -- set the l_primary_sr_created flag to TRUE
1964 l_primary_sr_created := TRUE;
1965 END IF;
1966 p_x_disposition_rec.primary_service_request_id := l_primary_service_request_id;
1967 p_x_disposition_rec.non_routine_workorder_id := l_non_routine_workorder_id;
1968 END IF; --end create SR
1969
1970
1971 --Validate disposition type
1972 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1973 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before validate_disposition_types');
1974 END IF;
1975 --dbms_output.put_line(SubStr('Before validate disposition type', 1, 255));
1976 Validate_Disposition_Types (
1977 -- p_api_version => p_api_version,
1978 -- p_init_msg_list => l_init_msg_list,
1979 -- p_commit => l_commit,
1980 -- p_validation_level => p_validation_level,
1981 x_return_status => x_return_status,
1982 x_msg_count => x_msg_count,
1983 x_msg_data => x_msg_data ,
1984 p_disposition_rec => p_x_disposition_rec);
1985
1986 -- Calculate disposition status
1987 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1988 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before calculate_status');
1989 END IF;
1990 --dbms_output.put_line(SubStr('Before calculate status'|| p_x_disposition_Rec.status_code, 1, 255));
1991 Calculate_Status (
1992 p_disposition_Rec => p_x_disposition_Rec,
1993 x_status_code => l_calculated_status);
1994
1995 p_x_disposition_Rec.status_code := l_calculated_status;
1996 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1997 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'After calculate status' || p_x_disposition_Rec.status_code);
1998 END IF;
1999 --dbms_output.put_line(SubStr('After calculate status' ||p_x_disposition_Rec.status_code, 1, 255));
2000 -- setting up object version number
2001 p_x_disposition_rec.object_version_number := p_x_disposition_rec.object_version_number + 1;
2002 --setting up user/create/update information
2003 p_x_disposition_rec.last_updated_by := fnd_global.user_id;
2004 p_x_disposition_rec.last_update_date := SYSDATE;
2005 p_x_disposition_rec.last_update_login := fnd_global.login_id;
2006
2007 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
2008 -- If the part is removed in 'Inspection' condition, then move the disposition to Complete status.
2009 IF (p_x_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_INSPECTION') AND
2010 p_x_disposition_rec.part_change_id IS NOT NULL ) THEN
2011 p_x_disposition_rec.status_code := 'COMPLETE';
2012 END IF;
2013
2014 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2015 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before update_row');
2016 END IF;
2017 --dbms_output.put_line(SubStr('Update_disp:Before call Update_Row', 1, 255));
2018 AHL_PRD_DISPOSITIONS_PKG.UPDATE_ROW(
2019 x_disposition_id => p_x_disposition_rec.disposition_id,
2020 x_object_version_number => p_x_disposition_rec.object_version_number,
2021 x_workorder_id => p_x_disposition_rec.workorder_id ,
2022 x_part_change_id => p_x_disposition_rec.part_change_id,
2023 x_path_position_id => p_x_disposition_rec.path_position_id,
2024 x_inventory_item_id => p_x_disposition_rec.inventory_item_id,
2025 x_organization_id => p_x_disposition_rec.item_org_id,
2026 x_item_group_id => p_x_disposition_rec.item_group_id,
2027 x_condition_id => p_x_disposition_rec.condition_id,
2028 x_instance_id => p_x_disposition_rec.instance_id,
2029 x_serial_number => p_x_disposition_rec.serial_number,
2030 x_lot_number => p_x_disposition_rec.lot_number,
2031 x_immediate_disposition_code => p_x_disposition_rec.immediate_disposition_code ,
2032 x_secondary_disposition_code => p_x_disposition_rec.secondary_disposition_code,
2033 x_status_code => p_x_disposition_rec.status_code,
2034 x_quantity => p_x_disposition_rec.quantity,
2035 x_uom => p_x_disposition_rec.uom,
2036 x_collection_id => p_x_disposition_rec.collection_id,
2037 x_primary_service_request_id => p_x_disposition_rec.primary_service_request_id ,
2038 x_non_routine_workorder_id => p_x_disposition_rec.non_routine_workorder_id,
2039 x_wo_operation_id => p_x_disposition_rec.wo_operation_id,
2040 x_item_revision => p_x_disposition_rec.item_revision,
2041 x_attribute_category => p_x_disposition_rec.attribute_category,
2042 x_attribute1 => p_x_disposition_rec.attribute1,
2043 x_attribute2 => p_x_disposition_rec.attribute2,
2044 x_attribute3 => p_x_disposition_rec.attribute3,
2045 x_attribute4 => p_x_disposition_rec.attribute4,
2046 x_attribute5 => p_x_disposition_rec.attribute5,
2047 x_attribute6 => p_x_disposition_rec.attribute6,
2048 x_attribute7 => p_x_disposition_rec.attribute7,
2049 x_attribute8 => p_x_disposition_rec.attribute8,
2050 x_attribute9 => p_x_disposition_rec.attribute9,
2051 x_attribute10 => p_x_disposition_rec.attribute10,
2052 x_attribute11 => p_x_disposition_rec.attribute11,
2053 x_attribute12 => p_x_disposition_rec.attribute12,
2054 x_attribute13 => p_x_disposition_rec.attribute13,
2055 x_attribute14 => p_x_disposition_rec.attribute14,
2056 x_attribute15 => p_x_disposition_rec.attribute15,
2057 x_comments => p_x_disposition_rec.comments,
2058 x_last_update_date => p_x_disposition_rec.last_update_date,
2059 x_last_updated_by => p_x_disposition_rec.last_updated_by,
2060 x_last_update_login => p_x_disposition_rec.last_update_login
2061 );
2062
2063 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2064 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'After update_row');
2065 END IF;
2066 --dbms_output.put_line(SubStr('Update_disp:After call Update_Row', 1, 255));
2067 --dbms_output.put_line(SubStr('Update_disp: l_disposition_rec.creation_date ' || l_disposition_rec.creation_date, 1, 255));
2068 --dbms_output.put_line(SubStr('Update_disp: l_disposition_rec.created_by ' || l_disposition_rec.created_by, 1, 255));
2069
2070
2071 Select AHL_PRD_DISPOSITIONS_B_H_S.NEXTVAL into l_disposition_h_id from dual;
2072 --dbms_output.put_line(SubStr('Update_disp:Before insert into history table', 1, 255));
2073
2074 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2075 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before insert into history table');
2076 END IF;
2077 AHL_PRD_DISPOSITIONS_B_H_PKG.INSERT_ROW(
2078 x_rowid => l_dummy_char,
2079 x_disposition_h_id => l_disposition_h_id,
2080 x_disposition_id => p_x_disposition_rec.disposition_id,
2081 x_object_version_number => p_x_disposition_rec.object_version_number,
2082 x_workorder_id => p_x_disposition_rec.workorder_id ,
2083 x_part_change_id => p_x_disposition_rec.part_change_id,
2084 x_path_position_id => p_x_disposition_rec.path_position_id,
2085 x_inventory_item_id => p_x_disposition_rec.inventory_item_id,
2086 x_organization_id => p_x_disposition_rec.item_org_id,
2087 x_item_group_id => p_x_disposition_rec.item_group_id,
2088 x_condition_id => p_x_disposition_rec.condition_id,
2089 x_instance_id => p_x_disposition_rec.instance_id,
2090 x_serial_number => p_x_disposition_rec.serial_number,
2091 x_lot_number => p_x_disposition_rec.lot_number,
2092 x_immediate_disposition_code => p_x_disposition_rec.immediate_disposition_code ,
2093 x_secondary_disposition_code => p_x_disposition_rec.secondary_disposition_code,
2094 x_status_code => p_x_disposition_rec.status_code,
2095 x_quantity => p_x_disposition_rec.quantity,
2096 x_uom => p_x_disposition_rec.uom,
2097 x_collection_id => p_x_disposition_rec.collection_id,
2098 x_primary_service_request_id => p_x_disposition_rec.primary_service_request_id ,
2099 x_non_routine_workorder_id => p_x_disposition_rec.non_routine_workorder_id,
2100 x_wo_operation_id => p_x_disposition_rec.wo_operation_id,
2101 x_item_revision => p_x_disposition_rec.item_revision,
2102 x_attribute_category => p_x_disposition_rec.attribute_category,
2103 x_attribute1 => p_x_disposition_rec.attribute1,
2104 x_attribute2 => p_x_disposition_rec.attribute2,
2105 x_attribute3 => p_x_disposition_rec.attribute3,
2106 x_attribute4 => p_x_disposition_rec.attribute4,
2107 x_attribute5 => p_x_disposition_rec.attribute5,
2108 x_attribute6 => p_x_disposition_rec.attribute6,
2109 x_attribute7 => p_x_disposition_rec.attribute7,
2110 x_attribute8 => p_x_disposition_rec.attribute8,
2111 x_attribute9 => p_x_disposition_rec.attribute9,
2112 x_attribute10 => p_x_disposition_rec.attribute10,
2113 x_attribute11 => p_x_disposition_rec.attribute11,
2114 x_attribute12 => p_x_disposition_rec.attribute12,
2115 x_attribute13 => p_x_disposition_rec.attribute13,
2116 x_attribute14 => p_x_disposition_rec.attribute14,
2117 x_attribute15 => p_x_disposition_rec.attribute15,
2118 x_comments => p_x_disposition_rec.comments,
2119 x_creation_date => l_disposition_rec.creation_date ,
2120 x_created_by => l_disposition_rec.created_by,
2121 x_last_update_date => p_x_disposition_rec.last_update_date,
2122 x_last_updated_by => p_x_disposition_rec.last_updated_by,
2123 x_last_update_login => p_x_disposition_rec.last_update_login
2124 );
2125
2126 -- Added by jaramana on October 8, 2007 for ER 5903256
2127 -- If the instance was just removed and a NR WO was already created
2128 -- Change the location of the instance to the NR WO.
2129 l_exist := NULL;
2130 OPEN check_nr_wo_status_csr(p_x_disposition_rec.non_routine_workorder_id);
2131 FETCH check_nr_wo_status_csr INTO l_exist;
2132 CLOSE check_nr_wo_status_csr;
2133 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2134 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to check if instance location neesd to be changed. ' ||
2135 ' p_x_disposition_rec.non_routine_workorder_id = ' || p_x_disposition_rec.non_routine_workorder_id ||
2136 ', l_disposition_rec.part_change_id = ' || l_disposition_rec.part_change_id ||
2137 ', p_x_disposition_rec.part_change_id = ' || p_x_disposition_rec.part_change_id ||
2138 ', NR UE with null status exists: ' || NVL(l_exist, 'N'));
2139 END IF;
2140
2141 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
2142 -- The API update_item_location and its use has been commented out. Its functionality will
2143 -- now be handled in the API AHL_PRD_NONROUTINE_PVT.process_nonroutine_job.
2144 /*
2145 IF (NVL(x_return_status, FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS AND
2146 p_x_disposition_rec.non_routine_workorder_id IS NOT NULL AND
2147 l_disposition_rec.part_change_id IS NULL AND
2148 p_x_disposition_rec.part_change_id IS NOT NULL AND
2149 l_exist IS NOT NULL) THEN
2150 update_item_location(p_workorder_id => p_x_disposition_rec.non_routine_workorder_id,
2151 p_instance_id => p_x_disposition_rec.instance_id,
2152 x_return_status => x_return_status);
2153 END IF;
2154 -- End addition by jaramana on February 21, 2007 for ER 5854667
2155 */
2156
2157 -- SATHAPLI::Bug 7111116, 21-May-2008, fix start
2158 -- If the following two conditions are met, i.e.:
2159 -- 1) unserviceable removal has happened
2160 -- 2) there exists a primary NR with released workorder for this disposition
2161 -- then the removed part should be assigned to the released NR workorder.
2162 -- This should be done only if the primary SR was not created along with removal in this cycle itself.
2163 IF (l_disposition_rec.part_change_id IS NULL AND p_x_disposition_rec.part_change_id IS NOT NULL
2164 AND
2165 (p_x_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE')
2166 OR
2167 p_x_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_MRB')
2168 )
2169 AND
2170 p_x_disposition_rec.non_routine_workorder_id IS NOT NULL
2171 AND
2172 NOT l_primary_sr_created)
2173 THEN
2174 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2175 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key,
2176 'About to check if the removed instance location needs to be changed.');
2177 END IF;
2178
2179 -- get the first released non-master workorder id for the NR summary workorder
2180 OPEN get_rel_nonmaster_wo_id_csr(p_x_disposition_rec.non_routine_workorder_id);
2181 FETCH get_rel_nonmaster_wo_id_csr INTO l_rel_nm_wo_id;
2182 IF (get_rel_nonmaster_wo_id_csr%FOUND) THEN
2183 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2184 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key,
2185 'First released non-master workorder id => '||l_rel_nm_wo_id);
2186 END IF;
2187
2188 -- get the removed instance id, which can be different from the disposition instance id for non-serialized items
2189 OPEN get_rem_inst_id_csr(p_x_disposition_rec.part_change_id);
2190 FETCH get_rem_inst_id_csr INTO l_removed_instance_id;
2191 IF (get_rem_inst_id_csr%FOUND) THEN
2192 -- move the removed part to the released NR workorder
2193 l_move_item_ins_tbl(1).instance_id := l_removed_instance_id;
2194 l_move_item_ins_tbl(1).quantity := p_x_disposition_rec.quantity;
2195 l_move_item_ins_tbl(1).from_workorder_id := p_x_disposition_rec.workorder_id;
2196 l_move_item_ins_tbl(1).to_workorder_id := l_rel_nm_wo_id;
2197
2198 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2199 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key,
2200 'About to call the API AHL_PRD_PARTS_CHANGE_PVT.move_instance_location with the parameters:'||
2201 ' instance_id => '||l_removed_instance_id||
2202 ' ,quantity => '||p_x_disposition_rec.quantity||
2203 ' ,to_workorder_id => '||l_rel_nm_wo_id);
2204 END IF;
2205
2206 -- call the required API
2207 AHL_PRD_PARTS_CHANGE_PVT.move_instance_location(
2208 p_api_version => 1.0,
2209 p_init_msg_list => FND_API.G_FALSE,
2210 p_commit => FND_API.G_FALSE,
2211 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2212 p_module_type => NULL,
2213 p_default => FND_API.G_TRUE,
2214 p_move_item_instance_tbl => l_move_item_ins_tbl,
2215 x_return_status => x_return_status,
2216 x_msg_count => x_msg_count,
2217 x_msg_data => x_msg_data
2218 );
2219
2220 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2221 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_debug_key,
2222 'Call to the API AHL_PRD_PARTS_CHANGE_PVT.move_instance_location returned with status => '||x_return_status);
2223 END IF;
2224
2225 -- check the API call return status
2226 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2227 CLOSE get_rem_inst_id_csr;
2228 CLOSE get_rel_nonmaster_wo_id_csr;
2229 RAISE FND_API.G_EXC_ERROR;
2230 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2231 CLOSE get_rem_inst_id_csr;
2232 CLOSE get_rel_nonmaster_wo_id_csr;
2233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2234 END IF;
2235 END IF;
2236 CLOSE get_rem_inst_id_csr;
2237 END IF;
2238 CLOSE get_rel_nonmaster_wo_id_csr;
2239 END IF;
2240 -- SATHAPLI::Bug 7111116, 21-May-2008, fix end
2241
2242 -- SATHAPLI::FP OGMA Issue# 86 - Automatic Material Return, 27-Dec-2007
2243 -- If the instance was just removed in Serviceable condition return the part to the Visit Locator.
2244 -- Note that the ReturnTo_Workorder_Locator will return only if the locator is set at the Visit level.
2245 -- For FP OGMA Issue# 105 - Non-Serialized Item Maintenance, if the instance was removed in 'Inspection'
2246 -- condition, then it should not be returned to the locator.
2247 IF (NVL(x_return_status, FND_API.G_RET_STS_SUCCESS) = FND_API.G_RET_STS_SUCCESS AND
2248 l_disposition_rec.part_change_id IS NULL AND
2249 p_x_disposition_rec.part_change_id IS NOT NULL AND
2250 p_x_disposition_rec.condition_id <> NVL(fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE'), -1) AND
2251 p_x_disposition_rec.condition_id <> NVL(fnd_profile.value('AHL_MTL_MAT_STATUS_MRB'), -1) AND
2252 p_x_disposition_rec.condition_id <> NVL(fnd_profile.value('AHL_MTL_MAT_STATUS_INSPECTION'), -1)) THEN
2253 OPEN part_change_dtls_csr(p_x_disposition_rec.part_change_id);
2254 FETCH part_change_dtls_csr INTO l_removed_instance_id, l_part_change_type;
2255 CLOSE part_change_dtls_csr;
2256 IF (l_removed_instance_id = p_x_disposition_rec.instance_id AND -- Removed instance is the Disposition instance
2257 NVL(l_part_change_type, 'X') IN ('R', 'S')) THEN -- Removal or Swap
2258 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2259 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call AHL_PRD_PARTS_CHANGE_PVT.ReturnTo_Workorder_Locator with ' ||
2260 ' part change id = ' || p_x_disposition_rec.part_change_id ||
2261 ' and disposition_id = ' || p_x_disposition_rec.disposition_id);
2262 END IF;
2263 AHL_PRD_PARTS_CHANGE_PVT.ReturnTo_Workorder_Locator(p_part_change_id => p_x_disposition_rec.part_change_id,
2264 p_disposition_id => p_x_disposition_rec.disposition_id,
2265 x_return_status => x_return_status,
2266 x_msg_data => x_msg_data,
2267 x_msg_count => x_msg_count,
2268 x_ahl_mtltxn_rec => l_ahl_mtltxn_rec);
2269 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2270 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from AHL_PRD_PARTS_CHANGE_PVT.ReturnTo_Workorder_Locator: ' ||
2271 ' x_return_status = ' || x_return_status);
2272 END IF;
2273 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2274 RAISE FND_API.G_EXC_ERROR;
2275 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2277 END IF;
2278 END IF;
2279 END IF;
2280
2281 --dbms_output.put_line(SubStr('Update_disp:After insert history', 1, 255));
2282 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2283 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'End Procedure');
2284 END IF;
2285
2286 END UPDATE_DISPOSITION;
2287
2288 ------------------------------------------------------------------
2289
2290 PROCEDURE CREATE_SR(
2291 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2292 p_disposition_rec IN AHL_PRD_DISPOSITION_PVT.disposition_rec_type,
2293 -- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
2294 p_mr_asso_tbl IN AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type,
2295 x_primary_sr_id OUT NOCOPY NUMBER,
2296 x_non_routine_workorder_id OUT NOCOPY NUMBER,
2297 x_return_status OUT NOCOPY VARCHAR2,
2298 x_msg_count OUT NOCOPY NUMBER,
2299 x_msg_data OUT NOCOPY VARCHAR2
2300 ) IS
2301
2302 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 13-Dec-2007
2303 CURSOR get_rem_inst_id_csr (p_part_change_id NUMBER) IS
2304 SELECT removed_instance_id
2305 FROM ahl_part_changes
2306 WHERE part_change_id = p_part_change_id;
2307
2308 -- Cursor to check whether removed instance is in job or not.
2309 CURSOR chk_disp_inst_job_csr (p_instance_id NUMBER, p_workorder_id NUMBER) IS
2310 SELECT 'Y'
2311 FROM ahl_workorders awo, csi_item_instances csi
2312 WHERE awo.wip_entity_id = csi.wip_job_id
2313 AND awo.workorder_id = p_workorder_id
2314 AND csi.instance_id = p_instance_id;
2315
2316 l_sr_task_tbl AHL_PRD_NONROUTINE_PVT.sr_task_tbl_type;
2317 l_visit_id NUMBER;
2318
2319 -- Variable added by jaramana on Oct 9, 2007 for ER 5883257
2320 l_mr_asso_tbl AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type := p_mr_asso_tbl;
2321
2322
2323 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || 'Create_SR';
2324 l_msg_index_out NUMBER;
2325
2326 l_inst_in_job_flag VARCHAR2(1) := 'N';
2327 l_removed_instance_id NUMBER;
2328 BEGIN
2329
2330 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2331 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2332 END IF;
2333
2334 -- Initialize message list if p_init_msg_list is set to TRUE
2335 IF FND_API.To_Boolean( p_init_msg_list)
2336 THEN
2337 FND_MSG_PUB.Initialize;
2338 END IF;
2339 --dbms_output.put_line('Begin Create SR -----------------------------');
2340 --dbms_output.put_line('Begin Create SR ');
2341 -- Populate sr_task_tbl
2342 l_sr_task_tbl(0).Request_date:= sysdate;
2343 l_sr_task_tbl(0).Summary := p_disposition_rec.summary;
2344
2345 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 13-Dec-2007
2346 -- NR record instance id should be set conditionally, as given below: -
2347 -- i.e. if part change occurred, then set it with the removed instance as it can be different for non-serialized items
2348 -- else, set it with disposition instance
2349 IF (p_disposition_rec.part_change_id IS NOT NULL) THEN
2350 OPEN get_rem_inst_id_csr(p_disposition_rec.part_change_id);
2351 FETCH get_rem_inst_id_csr INTO l_removed_instance_id;
2352 CLOSE get_rem_inst_id_csr;
2353 l_sr_task_tbl(0).Instance_id := l_removed_instance_id;
2354 ELSE
2355 l_sr_task_tbl(0).Instance_id := p_disposition_rec.instance_id;
2356 END IF;
2357
2358 l_sr_task_tbl(0).Problem_code := p_disposition_rec.problem_code;
2359 l_sr_task_tbl(0).duration := p_disposition_rec.duration;
2360
2361 SELECT visit_id INTO l_visit_id FROM AHL_WORKORDERS WHERE workorder_id = p_disposition_rec.workorder_id;
2362
2363 l_sr_task_tbl(0).Visit_id:= l_visit_id;
2364 l_sr_task_tbl(0).Originating_wo_id:= p_disposition_rec.workorder_id;
2365 l_sr_task_tbl(0).Operation_type := 'CREATE' ;
2366 l_sr_task_tbl(0).Severity_id := p_disposition_rec.severity_id;
2367 l_sr_task_tbl(0).source_program_code := 'AHL_NONROUTINE';
2368
2369 --data provided for service request to add record to the cs_incident_links table
2370 l_sr_task_tbl(0).object_id := p_disposition_rec.disposition_id;
2371 l_sr_task_tbl(0).Object_type := 'AHL_PRD_DISP';
2372 l_sr_task_tbl(0).link_id := 6;
2373
2374
2375 --dbms_output.put_line('l_sr_task_tbl(0).Request_date: '|| l_sr_task_tbl(0).Request_date);
2376 --dbms_output.put_line('l_sr_task_tbl(0).Summary: '|| l_sr_task_tbl(0).Summary);
2377 --dbms_output.put_line('l_sr_task_tbl(0).Instance_id: '|| l_sr_task_tbl(0).Instance_id);
2378 --dbms_output.put_line('l_sr_task_tbl(0).Problem_code: '|| l_sr_task_tbl(0).Problem_code);
2379 --dbms_output.put_line('l_sr_task_tbl(0).Visit_id: '|| l_sr_task_tbl(0).Visit_id);
2380 --dbms_output.put_line('l_sr_task_tbl(0).Originating_wo_id: '|| l_sr_task_tbl(0).Originating_wo_id);
2381 --dbms_output.put_line('l_sr_task_tbl(0).Operation_type: '|| l_sr_task_tbl(0).Operation_type);
2382 --dbms_output.put_line('l_sr_task_tbl(0).Severity_id: '|| l_sr_task_tbl(0).Severity_id);
2383 --dbms_output.put_line('l_sr_task_tbl(0).source_program_code: '|| l_sr_task_tbl(0).source_program_code);
2384
2385 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2386 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2387 '--Request_date: '|| l_sr_task_tbl(0).Request_date
2388 || '--Summary: '|| l_sr_task_tbl(0).Summary
2389 || '--Instance_id: '|| l_sr_task_tbl(0).Instance_id
2390 || '--Problem_code: '|| l_sr_task_tbl(0).Problem_code
2391 || '--Visit_id: '|| l_sr_task_tbl(0).Visit_id
2392 || '--Originating_wo_id: '|| l_sr_task_tbl(0).Originating_wo_id
2393 || '--Operation_type: '|| l_sr_task_tbl(0).Operation_type
2394 || '--Severity_id: '|| l_sr_task_tbl(0).Severity_id
2395 || '--source_program_code: '|| l_sr_task_tbl(0).source_program_code);
2396
2397 END IF;
2398 -- dbms_output.put_line('l_sr_task_tbl(0).type_id: '|| l_sr_task_tbl(0).type_id);
2399 -- dbms_output.put_line('l_sr_task_tbl(0).type_name: '|| l_sr_task_tbl(0).type_name);
2400 --dbms_output.put_line('Before Call process_nonroutine_job --return_status' || x_return_status);
2401
2402 -- Added by jaramana on October 9, 2007 for ER 5903318
2403 IF (p_disposition_rec.CREATE_WORK_ORDER_OPTION = 'CREATE_RELEASE_WO') THEN
2404 l_sr_task_tbl(0).wo_create_flag := 'Y';
2405 l_sr_task_tbl(0).wo_release_flag := 'Y';
2406 ELSIF (p_disposition_rec.CREATE_WORK_ORDER_OPTION = 'CREATE_WO') THEN
2407 l_sr_task_tbl(0).wo_create_flag := 'Y';
2408 l_sr_task_tbl(0).wo_release_flag := 'N';
2409 ELSIF (p_disposition_rec.CREATE_WORK_ORDER_OPTION = 'CREATE_WO_NO') THEN
2410 l_sr_task_tbl(0).wo_create_flag := 'N';
2411 l_sr_task_tbl(0).wo_release_flag := 'N';
2412 END IF;
2413
2414 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 13-Dec-2007
2415 -- Check whether the disposition instance/removed instance is issued to the job or not.
2416 -- NOTE: the l_sr_task_tbl(0).Instance_id set above can be used here as it is set with all the required checks
2417 OPEN chk_disp_inst_job_csr (l_sr_task_tbl(0).Instance_id, p_disposition_rec.workorder_id);
2418 FETCH chk_disp_inst_job_csr INTO l_inst_in_job_flag;
2419 CLOSE chk_disp_inst_job_csr;
2420
2421 -- Set the move_qty_to_nr_workorder flag to 'Y' conditionally.
2422 IF(p_disposition_rec.CREATE_WORK_ORDER_OPTION = 'CREATE_RELEASE_WO' AND l_inst_in_job_flag = 'Y') THEN
2423 l_sr_task_tbl(0).move_qty_to_nr_workorder := 'Y';
2424 ELSE
2425 l_sr_task_tbl(0).move_qty_to_nr_workorder := 'N';
2426 END IF;
2427
2428 -- set the NR instance quantity
2429 l_sr_task_tbl(0).instance_quantity := p_disposition_rec.quantity;
2430
2431 -- Following two attributes added by jaramana on 18-NOV-2008 for bug 7566597
2432 l_sr_task_tbl(0).resolution_code := p_disposition_rec.resolution_code;
2433 l_sr_task_tbl(0).resolution_meaning := p_disposition_rec.resolution_meaning;
2434
2435 --Calling Service Request API--
2436 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2437 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before process_nonroutine_job');
2438 END IF;
2439 AHL_PRD_NONROUTINE_PVT.process_nonroutine_job (
2440 p_api_version => 1.0,
2441 p_commit => Fnd_Api.g_false,
2442 p_module_type => NULL,
2443 x_return_status => x_return_status,
2444 x_msg_count => x_msg_count,
2445 x_msg_data => x_msg_data,
2446 p_x_sr_task_tbl => l_sr_task_tbl,
2447 -- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
2448 p_x_mr_asso_tbl => l_mr_asso_tbl
2449 );
2450
2451 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2452 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'After process non_nonroutine_job'
2453 || '-- x_return_status = '||x_return_status || '| x_msg_count = '||TO_CHAR(x_msg_count));
2454 END IF;
2455
2456
2457 --debuging codes
2458 --dbms_output.put_line('After Call process_nonroutine_job');
2459 --dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
2460 --dbms_output.put_line(SubStr('x_msg_count = '||TO_CHAR(x_msg_count), 1, 255));
2461 --dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));
2462
2463 FOR i IN 1..x_msg_count LOOP
2464 FND_MSG_PUB.get (
2465 p_msg_index => i,
2466 p_encoded => FND_API.G_FALSE,
2467 p_data => x_msg_data,
2468 p_msg_index_out => l_msg_index_out );
2469 --dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));
2470 END LOOP;
2471
2472 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2473 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Incident id: ' ||l_sr_task_tbl(0).incident_id);
2474 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Nonroutine_wo_id: ' ||l_sr_task_tbl(0).Nonroutine_wo_id);
2475 END IF;
2476 --dbms_output.put_line('Incident_id: ' || l_sr_task_tbl(0).incident_id);
2477 --dbms_output.put_line('Nonroutine_wo_id: ' || l_sr_task_tbl(0).Nonroutine_wo_id);
2478
2479
2480 -- Check return status.
2481 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2482 RAISE FND_API.G_EXC_ERROR;
2483 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2485 END IF;
2486
2487
2488 x_primary_sr_id := l_sr_task_tbl(0).incident_id;
2489 x_non_routine_workorder_id := l_sr_task_tbl(0).Nonroutine_wo_id;
2490
2491 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
2492 -- The API update_item_location and its use has been commented out. Its functionality will
2493 -- now be handled in the API AHL_PRD_NONROUTINE_PVT.process_nonroutine_job.
2494 /*
2495 -- Added by jaramana on October 8, 2007 for ER 5903256
2496 -- Automatically change the location of the removed unserviceable instance to the Non Routine Work Order
2497 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2498 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_sr_task_tbl(0).Nonroutine_wo_id: ' || l_sr_task_tbl(0).Nonroutine_wo_id);
2499 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_disposition_rec.part_change_id: ' || p_disposition_rec.part_change_id);
2500 END IF;
2501 IF (l_sr_task_tbl(0).Nonroutine_wo_id IS NOT NULL AND
2502 p_disposition_rec.part_change_id IS NOT NULL) THEN
2503 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2504 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call update_item_location.');
2505 END IF;
2506 update_item_location(p_workorder_id => l_sr_task_tbl(0).Nonroutine_wo_id,
2507 p_instance_id => p_disposition_rec.instance_id,
2508 x_return_status => x_return_status);
2509 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2510 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from update_item_location. x_return_status = ' || x_return_status);
2511 END IF;
2512 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2513 RAISE FND_API.G_EXC_ERROR;
2514 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2516 END IF;
2517 END IF;
2518 -- End changes for ER 5903256
2519 */
2520
2521 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2522 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2523 END IF;
2524 --dbms_output.put_line('End Create SR -----------------------------');
2525 END Create_SR;
2526
2527
2528 --------------DERIVE_COLUMNS---------------------------------------------------
2529 PROCEDURE derive_columns(p_x_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type)
2530 IS
2531
2532 CURSOR get_organization_csr(p_workorder_id IN NUMBER) IS
2533 SELECT vi.organization_id from ahl_workorders wo, ahl_visits_b vi
2534 WHERE wo.workorder_id = p_workorder_id
2535 AND wo.visit_id = vi.visit_id;
2536
2537 CURSOR get_instance_from_serial(p_item_id IN NUMBER, p_serial_num IN VARCHAR2) IS
2538 SELECT instance_id FROM CSI_ITEM_INSTANCES
2539 WHERE inventory_item_id = p_item_id AND serial_number = p_serial_num;
2540
2541 CURSOR get_instance_from_lot(p_item_id IN NUMBER, p_lot_num IN VARCHAR2) IS
2542 SELECT instance_id FROM CSI_ITEM_INSTANCES
2543 WHERE inventory_item_id = p_item_id AND lot_number = p_lot_num;
2544
2545 CURSOR instance_csr(p_instance_id IN NUMBER) IS
2546 SELECT inventory_item_id,
2547 quantity,
2548 unit_of_measure,
2549 last_vld_organization_id,
2550 inv_master_organization_id,
2551 serial_number,
2552 lot_number,
2553 inventory_revision
2554 from csi_item_instances
2555 WHERE instance_id = p_instance_id;
2556
2557
2558 instance_rec instance_csr%ROWTYPE;
2559
2560 l_unit_instance_id NUMBER; --instance id of the Unit
2561 l_count NUMBER;
2562
2563 l_position_instance_id NUMBER;
2564 l_derived_path_pos_id NUMBER;
2565 l_derived_org_id NUMBER;
2566 l_dummy_lowest_uc_id NUMBER;
2567 l_dummy_status VARCHAR(30);
2568
2569 x_return_status VARCHAR2(30);
2570 x_msg_count VARCHAR2(30);
2571 x_msg_data VARCHAR(2000);
2572
2573
2574 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || 'derive_columns';
2575
2576 BEGIN
2577
2578 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2579 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2580 END IF;
2581 --dbms_output.put_line(SubStr('Begin Derive Column', 1, 255));
2582
2583
2584 /* JR: 25-SEP-2004
2585 When creating a disposition from the Part Change UI,
2586 for a unit, path_position_id will not be null and
2587 p_disposition_rec.instance_id will contain the following values:
2588 Removal: The removed instance id
2589 Swap: The swapped out (removed) instance id
2590 Install: Null
2591 and for an IB Tree, path_position_id will be null and
2592 p_disposition_rec.instance_id will contain the following values:
2593 Removal: The removed instance id
2594 Swap: The swapped out (removed) instance id
2595 Install: The installed instance id
2596 */
2597 IF (p_x_disposition_rec.path_position_id IS NOT NULL) THEN
2598 -- derive instance from path position.
2599 -- Get the instance in the position only if instance has not been
2600 -- passed and if this API has NOT been called from Part Change
2601 IF (p_x_disposition_rec.instance_id IS NULL AND p_x_disposition_rec.part_change_id IS NULL) THEN
2602 l_unit_instance_id := get_unit_instance_id(p_x_disposition_rec.workorder_id);
2603 IF l_unit_instance_id IS NOT NULL THEN
2604 AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance (
2605 p_api_version => 1.0,
2606 x_return_status => x_return_status,
2607 x_msg_count => x_msg_count,
2608 x_msg_data => x_msg_data,
2609 p_position_id => p_x_disposition_rec.path_position_id,
2610 p_csi_item_instance_id => l_unit_instance_id,
2611 x_item_instance_id => l_position_instance_id,
2612 x_lowest_uc_csi_id => l_dummy_lowest_uc_id,
2613 x_mapping_status => l_dummy_status
2614 );
2615 p_x_disposition_rec.instance_id := l_position_instance_id;
2616 -- Check return status.
2617 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2618 RAISE FND_API.G_EXC_ERROR;
2619 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2621 END IF;
2622
2623 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2624 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,'derived Position Instance_id: ' || p_x_disposition_rec.instance_id);
2625 END IF;
2626 --dbms_output.put_line(SubStr('derived Position Instance_id: ' || p_x_disposition_rec.instance_id, 1, 255));
2627 END IF;
2628 -- Added by jaramana on June 23, 2006 to fix Bug 5205851
2629 ELSE
2630 IF (p_x_disposition_rec.instance_id IS NULL AND p_x_disposition_rec.part_change_id IS NOT NULL) THEN
2631 -- Disposition being created from Part Change UI for installing to an Empty position
2632 -- Default the condition of the Disposition to Serviceable if not already set
2633 IF (p_x_disposition_rec.condition_id IS NULL) THEN
2634 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2635 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Defaulting Condition to ' || fnd_profile.value('AHL_MTL_MAT_STATUS_SERVICABLE'));
2636 END IF;
2637 p_x_disposition_rec.condition_id := fnd_profile.value('AHL_MTL_MAT_STATUS_SERVICABLE');
2638 IF (p_x_disposition_rec.condition_id IS NULL) THEN
2639 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2640 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Profile AHL_MTL_MAT_STATUS_SERVICABLE not set. Unable to derive Default Condition.');
2641 END IF;
2642 -- Raise an Exception
2643 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_SVC_COND_PRF_NOT_SET');
2644 FND_MSG_PUB.ADD;
2645 RAISE FND_API.G_EXC_ERROR;
2646 END IF;
2647 END IF;
2648 END IF;
2649 END IF;
2650 -- Following added by jaramana on August 07, 2007 for the bug 6328554 (FP of 5948917)
2651 ELSE
2652 -- IB Tree
2653 IF (p_x_disposition_rec.instance_id IS NOT NULL AND
2654 p_x_disposition_rec.part_change_id IS NOT NULL AND
2655 p_x_disposition_rec.condition_id IS NULL) THEN
2656 -- Install of an Instance to an IB Tree from the Part Change UI with creation of
2657 -- a new Disposition: Default the condition to Serviceable
2658 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2659 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Defaulting Condition to ' || fnd_profile.value('AHL_MTL_MAT_STATUS_SERVICABLE'));
2660 END IF;
2661 p_x_disposition_rec.condition_id := fnd_profile.value('AHL_MTL_MAT_STATUS_SERVICABLE');
2662 IF (p_x_disposition_rec.condition_id IS NULL) THEN
2663 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2664 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Profile AHL_MTL_MAT_STATUS_SERVICABLE not set. Unable to derive Default Condition.');
2665 END IF;
2666 -- Raise an Exception
2667 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_SVC_COND_PRF_NOT_SET');
2668 FND_MSG_PUB.ADD;
2669 RAISE FND_API.G_EXC_ERROR;
2670 END IF;
2671 END IF;
2672 -- End addition by jaramana on August 07, 2007 for the bug 6328554 (FP of 5948917)
2673 END IF;
2674
2675 --derive instance_id
2676 IF p_x_disposition_rec.instance_id IS NULL THEN
2677 --derive instance_id from item and serial_number
2678 IF p_x_disposition_rec.inventory_item_id IS NOT NULL AND p_x_disposition_rec.serial_number IS NOT NULL THEN
2679 OPEN get_instance_from_serial(p_x_disposition_rec.inventory_item_id,
2680 p_x_disposition_rec.serial_number);
2681 FETCH get_instance_from_serial INTO p_x_disposition_rec.instance_id;
2682 CLOSE get_instance_from_serial;
2683 END IF;
2684 --from item and lot_number derive instance_id
2685 IF p_x_disposition_rec.inventory_item_id IS NOT NULL AND p_x_disposition_rec.lot_number IS NOT NULL THEN
2686 OPEN get_instance_from_lot(p_x_disposition_rec.inventory_item_id,
2687 p_x_disposition_rec.lot_number);
2688 FETCH get_instance_from_lot INTO p_x_disposition_rec.instance_id;
2689 CLOSE get_instance_from_lot;
2690 END IF;
2691 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2692 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,'derived instance: ' || p_x_disposition_rec.instance_id);
2693 END IF;
2694 --dbms_output.put_line(SubStr('derived instance: ' || p_x_disposition_rec.instance_id, 1, 255));
2695 END IF;
2696
2697 -- from INSTANCE derive organization, item, quantity and uom
2698 IF (p_x_disposition_rec.instance_id IS NOT NULL) THEN
2699 OPEN instance_csr(p_x_disposition_rec.instance_id);
2700 FETCH instance_csr INTO instance_rec;
2701 CLOSE instance_csr;
2702
2703 l_derived_org_id := nvl(instance_rec.last_vld_organization_id, instance_rec.inv_master_organization_id); --derive organization from instance
2704 IF p_x_disposition_rec.inventory_item_id IS NULL THEN
2705 p_x_disposition_rec.inventory_item_id := instance_rec.inventory_item_id;
2706
2707 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2708 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,'derived item_id ' || instance_rec.inventory_item_id);
2709 END IF;
2710 --dbms_output.put_line(SubStr('derived item_id ' || instance_rec.inventory_item_id, 1, 255));
2711 END IF;
2712 IF p_x_disposition_rec.quantity IS NULL THEN
2713 p_x_disposition_rec.quantity := instance_rec.quantity;
2714 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2715 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'derived quantity ' || instance_rec.quantity);
2716 END IF;
2717 --dbms_output.put_line(SubStr('derived quantity ' || instance_rec.quantity, 1, 255));
2718 END IF;
2719 IF p_x_disposition_rec.uom IS NULL THEN
2720 p_x_disposition_rec.uom := instance_rec.unit_of_measure;
2721 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2722 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'derived uom ' || instance_rec.unit_of_measure);
2723 END IF;
2724 --dbms_output.put_line(SubStr('derived uom ' || instance_rec.unit_of_measure, 1, 255));
2725 END IF;
2726
2727 --Jerry added on 10/04/05 for fixing an internal bug found by Shailaja/Vadim
2728 IF p_x_disposition_rec.serial_number IS NULL THEN
2729 p_x_disposition_rec.serial_number:= instance_rec.serial_number;
2730 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2731 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'derived serial_number ' || instance_rec.serial_number);
2732 END IF;
2733 END IF;
2734
2735 IF p_x_disposition_rec.lot_number IS NULL THEN
2736 p_x_disposition_rec.lot_number:= instance_rec.lot_number;
2737 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2738 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'derived lot_number ' || instance_rec.lot_number);
2739 END IF;
2740 END IF;
2741
2742 IF p_x_disposition_rec.item_revision IS NULL THEN
2743 p_x_disposition_rec.item_revision := instance_rec.inventory_revision;
2744 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2745 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'derived item_revision ' || instance_rec.inventory_revision);
2746 END IF;
2747 END IF;
2748 --Jerry's change finishes here
2749
2750 --Derive path_position_id
2751 IF p_x_disposition_rec.path_position_id IS NULL THEN
2752
2753 -- Updated by rbhavsar on 09/27/2007 for Bug 6411059
2754 -- START: Added IF statement to check if root node of the instance is in uc headers table
2755 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2756 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,' Before calling Map_Instance_To_Pos_ID ' || p_x_disposition_rec.path_position_id ||
2757 ' Instance id ' || p_x_disposition_rec.instance_id );
2758 END IF;
2759
2760 IF (root_node_in_uc_headers(p_x_disposition_rec.instance_id) = TRUE) THEN
2761 AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Pos_ID (
2762 p_api_version => 1.0,
2763 x_return_status => x_return_status,
2764 x_msg_count => x_msg_count,
2765 x_msg_data => x_msg_data,
2766 p_csi_item_instance_id => p_x_disposition_rec.instance_id,
2767 x_path_position_id => l_derived_path_pos_id);
2768
2769 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2770 RAISE FND_API.G_EXC_ERROR;
2771 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2773 END IF;
2774
2775 p_x_disposition_rec.path_position_id := l_derived_path_pos_id;
2776 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2777 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'derived path_position_id ' || l_derived_path_pos_id);
2778 END IF;
2779
2780 END IF; -- END: Updated by rbhavsar Bug 6411059
2781
2782 --dbms_output.put_line(SubStr('derived path_position_id ' || l_derived_path_pos_id, 1, 255));
2783 END IF; -- path_position_id is null
2784 END IF; -- instance_id is not null
2785
2786 --derive organizationid for item from workorder only if instance is null
2787 IF(p_x_disposition_rec.inventory_item_id IS NOT NULL AND p_x_disposition_rec.instance_id IS NULL) THEN
2788 OPEN get_organization_csr(p_x_disposition_rec.workorder_id);
2789 FETCH get_organization_csr INTO l_derived_org_id;
2790 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2791 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Derived Org: ' || p_x_disposition_rec.item_org_id);
2792 END IF;
2793 --dbms_output.put_line(SubStr('Derived Org: ' || p_x_disposition_rec.item_org_id, 1, 255));
2794 CLOSE get_organization_csr;
2795 END IF;
2796
2797 --assign derived organization id to disposition record's item_org_id
2798 --ignore organization id from user's input
2799 p_x_disposition_rec.item_org_id := l_derived_org_id;
2800
2801 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2802 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
2803 END IF;
2804 --dbms_output.put_line(SubStr('End Derive Column', 1, 255));
2805
2806 END derive_columns;
2807
2808
2809
2810 ---------------------------LOCAL VALIDATION PROCEDURES------------------------------------
2811
2812 PROCEDURE validate_for_create(p_disposition_rec IN AHL_PRD_DISPOSITION_PVT.disposition_rec_type)
2813 IS
2814
2815 CURSOR exist_position_csr(p_path_position_id IN NUMBER, p_workorder_id IN NUMBER) IS
2816 SELECT 'X' FROM ahl_mc_path_positions pos
2817 WHERE pos.path_position_id = p_path_position_id
2818 AND EXISTS
2819 (SELECT pos2.path_pos_common_id FROM ahl_prd_dispositions_b dis, ahl_mc_path_positions pos2
2820 WHERE dis.path_position_id IS NOT NULL
2821 AND dis.path_position_id = pos2.path_position_id
2822 AND dis.workorder_id = p_workorder_id
2823 AND nvl(dis.status_code, 'dummy') NOT IN ('COMPLETE', 'TERMINATED')
2824 AND pos.path_pos_common_id = pos2.path_pos_common_id);
2825
2826 --Begin Performance Tuning
2827 CURSOR get_wo_instance_id(p_workorder_id IN NUMBER) IS
2828 SELECT nvl(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)
2829 FROM ahl_workorders wo, AHL_VISITS_VL VST, AHL_VISIT_TASKS_VL VTS
2830 WHERE workorder_id = p_workorder_id
2831 and WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
2832 AND VST.VISIT_ID=VTS.VISIT_ID;
2833 --End performance Tunning
2834
2835 CURSOR item_in_itemgrp_csr(p_inventory_item_id IN NUMBER, p_item_group_id IN NUMBER) IS
2836 SELECT 'x' FROM ahl_item_associations_b
2837 WHERE inventory_item_id = p_Inventory_item_id
2838 AND item_group_id = p_item_group_id;
2839
2840 -- cursor changed by anraj
2841 -- backend validation to check that while create the Item Group given is of type NON-TRACKED
2842 CURSOR val_item_group_csr(p_item_group_id IN NUMBER) IS
2843 SELECT 'x' FROM ahl_item_groups_b
2844 WHERE item_group_id = p_item_group_id
2845 AND status_code = 'COMPLETE' AND type_code = 'NON-TRACKED';
2846
2847
2848 /* Begin Fix for 4075758 on Dec 21. 2004 by JR */
2849 -- For non-tracked items, serial or lot numbers
2850 -- need to be validated only against MTL tables.
2851 /******
2852 CURSOR val_lot_number_csr(p_lot_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
2853 SELECT 'x' FROM csi_item_instances csi
2854 WHERE lot_number = p_lot_number
2855 AND inventory_item_id = p_inventory_item_id;
2856
2857 CURSOR val_serial_number_csr(p_serial_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
2858 SELECT 'x' FROM csi_item_instances csi
2859 WHERE serial_number = p_serial_number
2860 AND inventory_item_id = p_inventory_item_id;
2861 ******/
2862 CURSOR val_lot_number_csr(p_lot_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
2863 SELECT 'x' FROM mtl_lot_numbers
2864 WHERE lot_number = p_lot_number
2865 AND inventory_item_id = p_inventory_item_id
2866 UNION
2867 SELECT 'x' FROM csi_item_instances csi
2868 WHERE lot_number = p_lot_number
2869 AND inventory_item_id = p_inventory_item_id;
2870
2871 CURSOR val_serial_number_csr(p_serial_number IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
2872 SELECT 'x' FROM mtl_serial_numbers
2873 WHERE serial_number = p_serial_number
2874 AND inventory_item_id = p_inventory_item_id
2875 UNION
2876 SELECT 'x' FROM csi_item_instances csi
2877 WHERE serial_number = p_serial_number
2878 AND inventory_item_id = p_inventory_item_id;
2879 /* End Fix for 4075758 on Dec 21. 2004 */
2880
2881 CURSOR instance_quantity_csr(p_instance_id IN NUMBER) IS
2882 SELECT quantity from csi_item_instances WHERE instance_id = p_instance_id;
2883
2884 CURSOR instance_uom_csr(p_instance_id IN NUMBER) IS
2885 SELECT unit_of_measure from csi_item_instances WHERE instance_id = p_instance_id;
2886
2887 CURSOR item_class_uom_csr(p_uom_code IN VARCHAR2, p_inventory_item_id NUMBER) IS
2888 /*
2889 SELECT 'X' FROM ahl_item_class_uom_v
2890 WHERE uom_code = p_uom_code AND inventory_item_id = p_inventory_item_id;
2891 */
2892 --AnRaj: Changed query, Perf Bug#4908609,Issue#2
2893 SELECT 'X'
2894 from MTL_UNITS_OF_MEASURE_TL
2895 where uom_class = (
2896 select distinct uom.uom_class
2897 from MTL_UNITS_OF_MEASURE_TL uom
2898 where uom.uom_code = ( select distinct primary_uom_code
2899 from mtl_system_items
2900 where inventory_item_id = p_inventory_item_id
2901 )
2902 )
2903 and uom_code = p_uom_code;
2904
2905 CURSOR val_Collection_id_csr(p_collection_id IN NUMBER) IS
2906 SELECT 'x' FROM qa_results WHERE collection_id = p_collection_id;
2907
2908 CURSOR get_item_id_csr(p_instance_id IN NUMBER) IS
2909 SELECT inventory_item_id from csi_item_instances WHERE instance_id = p_instance_id;
2910
2911 CURSOR val_uom_csr(p_uom IN VARCHAR2) IS
2912 SELECT 'x' FROM mtl_units_of_measure_vl
2913 WHERE uom_code = p_uom;
2914
2915 CURSOR item_revisions_csr (p_revision IN VARCHAR2, p_item_id IN NUMBER, p_organization_id IN NUMBER) IS
2916 SELECT 'x' FROM mtl_item_revisions
2917 WHERE inventory_item_id = p_item_id
2918 AND organization_id = p_organization_id
2919 AND revision = p_revision;
2920
2921 CURSOR part_change_csr(c_part_change_id IN NUMBER) IS
2922 SELECT REMOVED_INSTANCE_ID, INSTALLED_INSTANCE_ID, PART_CHANGE_TYPE
2923 FROM AHL_PART_CHANGES
2924 WHERE PART_CHANGE_ID = c_part_change_id;
2925
2926 -- Added the following for fixing Bug#:4059944
2927 -- To find whether the Item is IB Trackable
2928 CURSOR item_is_ib_trackable(p_inventory_item_id IN NUMBER) IS
2929 SELECT NVL(MTL.comms_nl_trackable_flag, 'N') trackable_flag
2930 FROM MTL_SYSTEM_ITEMS_KFV MTL
2931 WHERE MTL.INVENTORY_ITEM_ID= p_inventory_item_id;
2932
2933 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 13-Dec-2007
2934 -- Cursor to check whether the disposition item is serialized or not.
2935 CURSOR chk_non_serialized_csr(p_inventory_item_id NUMBER, p_item_org_id NUMBER) IS
2936 SELECT 'X'
2937 FROM mtl_system_items_b
2938 WHERE inventory_item_id = p_inventory_item_id
2939 AND organization_id = p_item_org_id
2940 AND serial_number_control_code = 1;
2941
2942 item_is_trackable VARCHAR2(1);
2943
2944 l_serial_control NUMBER;
2945 l_qty_revision_control NUMBER;
2946 l_lot_control NUMBER;
2947
2948
2949
2950 l_exist VARCHAR(1);
2951 l_quantity NUMBER;
2952 l_uom VARCHAR(3);
2953
2954 l_wo_organization_id NUMBER;
2955
2956 l_unit_instance_id NUMBER;
2957 l_pos_instance_id NUMBER;
2958 l_parent_instance_id NUMBER;
2959 l_dummy_rel_id NUMBER;
2960 l_dummy_lowest_uc_id NUMBER;
2961 l_mapping_status VARCHAR(30);
2962 l_return_status VARCHAR(30);
2963 l_msg_count NUMBER;
2964 l_msg_data VARCHAR(200);
2965
2966 l_wo_instance_id NUMBER;
2967 l_wo_root_instance_id NUMBER; --Root Instance of workorder instance
2968 l_dis_root_instance_id NUMBER; --Root Instance of disposition instance.
2969 l_item_id NUMBER;
2970
2971 l_position_empty BOOLEAN := FALSE;
2972
2973 l_pc_rem_instance_id NUMBER;
2974 l_pc_inst_instance_id NUMBER;
2975 l_pc_type VARCHAR2(1);
2976
2977 l_plan_id NUMBER;
2978
2979
2980 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || 'validate_for_create';
2981 l_dummy VARCHAR2(1);
2982 l_srl_flag VARCHAR2(1) := 'Y';
2983
2984 BEGIN
2985
2986 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2987 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
2988 END IF;
2989 --dbms_output.put_line(SubStr('Begin Validate_For_Create', 1, 255));
2990
2991 --WORKORDER
2992 IF (p_disposition_rec.workorder_id IS NULL) THEN
2993 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_WO_ID_REQ'); -- Workorder is required to create disposition.
2994 FND_MSG_PUB.ADD;
2995 RAISE FND_API.G_EXC_ERROR;
2996 ELSE
2997 validate_workorder(p_disposition_rec.workorder_id);
2998 IF(workorder_editable(p_disposition_rec.workorder_id) = FALSE) THEN
2999 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_DIS_WO_NOT_EDITABLE'); --Cannot Create Disposition Because Workorder is not editable.
3000 FND_MSG_PUB.ADD;
3001 RAISE FND_API.G_EXC_ERROR;
3002 END IF;
3003 END IF;
3004
3005 IF (p_disposition_rec.path_position_id IS NULL AND p_disposition_rec.inventory_item_id IS NULL) THEN
3006 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_DIS_POS_OR_ITEM_REQ'); --Path Position or Item is required to create a disposition
3007 FND_MSG_PUB.ADD;
3008 RAISE FND_API.G_EXC_ERROR;
3009 END IF;
3010
3011 IF p_disposition_rec.item_org_id IS NOT NULL AND p_disposition_rec.inventory_item_id IS NULL THEN
3012 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_DIS_CANNOT_ENTER_ORG'); --Cannot enter organization id.
3013 FND_MSG_PUB.ADD;
3014 END IF;
3015
3016 -- Validate Part Change and get details
3017 IF(p_disposition_rec.part_change_id IS NOT NULL) THEN
3018 OPEN part_change_csr(p_disposition_rec.part_change_id);
3019 FETCH part_change_csr INTO l_pc_rem_instance_id,
3020 l_pc_inst_instance_id,
3021 l_pc_type;
3022 IF (part_change_csr%NOTFOUND) THEN
3023 CLOSE part_change_csr;
3024 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DIS_INV_PART_CHG_ID'); -- Invalid part change id
3025 FND_MESSAGE.SET_TOKEN('PART_CHNG_ID', p_disposition_rec.part_change_id);
3026 FND_MSG_PUB.ADD;
3027 RAISE FND_API.G_EXC_ERROR;
3028 END IF;
3029 CLOSE part_change_csr;
3030 END IF;
3031
3032 --PATH POSITION VALIDATION ---------------------------------
3033 IF (p_disposition_rec.path_position_id IS NOT NULL) THEN
3034 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3035 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Check Path Position');
3036 END IF;
3037 --dbms_output.put_line(SubStr('In Validate_For_Create -- Check Path Position ', 1, 255));
3038 --Check for valid path_position_id
3039 validate_path_position(p_disposition_rec.path_position_id);
3040
3041 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 13-Dec-2007
3042 -- Multiple dispositions can be created for non-serialized item/position. So relax the only-one-disposition
3043 -- restriction for non-serilized item/position.
3044 OPEN chk_non_serialized_csr(p_disposition_rec.inventory_item_id, p_disposition_rec.item_org_id);
3045 FETCH chk_non_serialized_csr INTO l_dummy;
3046 IF (chk_non_serialized_csr%NOTFOUND) THEN
3047 -- The chk_non_serialized_csr will not fetch results if item id is NULL.
3048 -- If item id is NULL, then this disposition is being created for empty position.
3049 -- Check for non-serialized position by calling the API AHL_MC_PATH_POSITION_PVT.Is_Position_Serial_Controlled
3050 IF (p_disposition_rec.inventory_item_id IS NULL) THEN
3051 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3052 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
3053 'Before API AHL_MC_PATH_POSITION_PVT.Is_Position_Serial_Controlled call l_srl_flag => '||l_srl_flag);
3054 END IF;
3055
3056 l_srl_flag := AHL_MC_PATH_POSITION_PVT.Is_Position_Serial_Controlled(
3057 NULL,
3058 p_disposition_rec.path_position_id
3059 );
3060
3061 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3062 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
3063 'After API AHL_MC_PATH_POSITION_PVT.Is_Position_Serial_Controlled call l_srl_flag => '||l_srl_flag);
3064 END IF;
3065 END IF;
3066
3067 --If item/position is serialized, then validate that path position does not exist in other dispositions
3068 IF (l_srl_flag = 'Y') THEN
3069 OPEN exist_position_csr(p_disposition_rec.path_position_id, p_disposition_rec.workorder_id);
3070 FETCH exist_position_csr INTO l_exist;
3071 IF(exist_position_csr%FOUND) THEN
3072 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_POS_OTHER');
3073 FND_MSG_PUB.ADD;
3074 END IF;
3075 CLOSE exist_position_csr;
3076 END IF; -- if l_srl_flag = 'Y'
3077 END IF; -- if chk_non_serialized_csr%NOTFOUND
3078 CLOSE chk_non_serialized_csr;
3079
3080 --Check if workorder's instance is in UC tree
3081 l_unit_instance_id := get_unit_instance_id(p_disposition_rec.workorder_id);
3082 IF(l_unit_instance_id IS NULL) THEN
3083 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_NOT_UC_NO_POS'); --Workorder's instance does not belong to UC hence path position is not allowed
3084 FND_MSG_PUB.ADD;
3085 RAISE FND_API.G_EXC_ERROR;
3086 END IF;
3087
3088 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3089 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'About to call AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance with ' ||
3090 ' p_position_id = ' || p_disposition_rec.path_position_id ||
3091 ', p_csi_item_instance_id = ' || l_unit_instance_id);
3092 END IF;
3093
3094 AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance (
3095 p_api_version => 1.0,
3096 x_return_status => l_return_status,
3097 x_msg_count => l_msg_count,
3098 x_msg_data => l_msg_data,
3099 p_position_id => p_disposition_rec.path_position_id,
3100 p_csi_item_instance_id => l_unit_instance_id,
3101 x_item_instance_id => l_pos_instance_id,
3102 x_parent_instance_id => l_parent_instance_id,
3103 x_relationship_id => l_dummy_rel_id,
3104 x_lowest_uc_csi_id => l_dummy_lowest_uc_id,
3105 x_mapping_status => l_mapping_status
3106 );
3107
3108 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3109 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, L_DEBUG_KEY, 'Returned from call to AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance: ' ||
3110 ' x_return_status ' || l_return_status ||
3111 ', x_parent_instance_id ' || l_parent_instance_id ||
3112 ', x_mapping_status = ' || l_mapping_status);
3113 END IF;
3114 -- Check return status.
3115 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3116 RAISE FND_API.G_EXC_ERROR;
3117 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3119 END IF;
3120
3121 -- SATHAPLI:: Bug 6836572, 19-Mar-2008
3122 -- If the workorder instance is root instance of an installed sub UC, and the sub UC has been removed during parts change, then
3123 -- the API get_unit_instance_id would return the sub UC's root instance, instead of the top most UC's root instance. This
3124 -- would result in the API AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance returning x_mapping_status as 'NA'.
3125 -- To bypass this, we can just check for the part change to have taken place or not. If yes, then we can go ahead with the
3126 -- Disposition creation.
3127
3128 IF (l_mapping_status = 'NA') THEN
3129 -- Check if removal was done or not.
3130 IF(p_disposition_rec.part_change_id IS NOT NULL) THEN
3131 -- Ensure that the disposition instance is the same as the removed instance.
3132 IF (NVL(p_disposition_rec.instance_id, -1) <> l_pc_rem_instance_id) THEN
3133 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_INV_INSTANT_ID');
3134 FND_MESSAGE.SET_TOKEN('INSTANT_ID', p_disposition_rec.instance_id);
3135 FND_MSG_PUB.ADD;
3136 END IF;
3137 ELSE
3138 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_POS_NOT_WO_UC'); --Position is not in the same Unit Configuration as that of workorder instance.
3139 FND_MSG_PUB.ADD;
3140 END IF; -- part change check
3141 ELSIF (l_mapping_status = 'PARENT_EMPTY') THEN
3142 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_PARENT_EMPTY'); --Cannot select a position whose parent is empty
3143 FND_MSG_PUB.ADD;
3144 ELSIF (l_mapping_status = 'EMPTY') THEN
3145 -- Check if this create_disposition is called from Part Change
3146 IF(p_disposition_rec.part_change_id IS NOT NULL) THEN
3147 IF (l_pc_type = G_PART_CHANGE_REMOVE) THEN
3148 -- Disposition is for the removed instance
3149 -- Ensure that the disposition instance is the same as the removed instance
3150 IF (NVL(p_disposition_rec.instance_id, -1) <> l_pc_rem_instance_id) THEN
3151 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_INV_INSTANT_ID');
3152 FND_MESSAGE.SET_TOKEN('INSTANT_ID', p_disposition_rec.instance_id);
3153 FND_MSG_PUB.ADD;
3154 END IF;
3155 ELSE
3156 -- G_PART_CHANGE_INSTALL and G_PART_CHANGE_SWAP are not possible since the position is empty
3157 -- May have to throw an exception if control come here
3158 NULL;
3159 END IF;
3160 ELSE
3161 l_position_empty := TRUE;
3162 IF (p_disposition_rec.instance_id IS NOT NULL) THEN
3163 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_DIS_POS_EMPTY_NO_INST'); --Position is empty cannot enter instance number.
3164 FND_MSG_PUB.ADD;
3165 END IF;
3166 --Check if parent instance of this empty position is in the same tree as workorder instance
3167 OPEN get_wo_instance_id(p_disposition_rec.workorder_id);
3168 FETCH get_wo_instance_id INTO l_wo_instance_id;
3169 CLOSE get_wo_instance_id;
3170 IF l_parent_instance_id <> l_wo_instance_id THEN --only need to check when instance is not the same as workorder instance
3171 --get root instance for workorder instance
3172 l_wo_root_instance_id := get_root_instance_id(l_wo_instance_id);
3173
3174 --get root instance for the empty position parent instance
3175 l_dis_root_instance_id := get_root_instance_id(l_parent_instance_id);
3176
3177 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3178 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_wo_instance_id = ' || l_wo_instance_id ||
3179 ', l_wo_root_instance_id = ' || l_wo_root_instance_id ||
3180 ', l_parent_instance_id = ' || l_parent_instance_id ||
3181 ', l_dis_root_instance_id = ' || l_dis_root_instance_id);
3182 END IF;
3183
3184 IF nvl(l_wo_root_instance_id, -1) <> nvl(l_dis_root_instance_id, -1) THEN
3185 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DIS_POS_NOT_WO_UC'); -- Instance is not in the same unit as workorder instance
3186 FND_MSG_PUB.ADD;
3187 RAISE FND_API.G_EXC_ERROR;
3188 END IF;
3189 END IF;
3190 END IF; -- Part Change
3191 ELSIF (l_mapping_status = 'MATCH') THEN
3192 -- Check if this create_disposition is called from Part Change
3193 IF(p_disposition_rec.part_change_id IS NOT NULL) THEN
3194 -- Position is not empty and Part Change has already happened
3195 IF (l_pc_type = G_PART_CHANGE_INSTALL) THEN
3196 -- Disposition is for the empty position (before the install)
3197 IF (p_disposition_rec.instance_id IS NOT NULL) THEN
3198 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_INV_INSTANT_ID');
3199 FND_MESSAGE.SET_TOKEN('INSTANT_ID', p_disposition_rec.instance_id);
3200 FND_MSG_PUB.ADD;
3201 RAISE FND_API.G_EXC_ERROR;
3202 END IF;
3203 l_position_empty := TRUE;
3204 ELSIF (l_pc_type = G_PART_CHANGE_SWAP) THEN
3205 -- Disposition is for the instance that was swapped out
3206 -- Ensure that the disposition instance is the same as the removed instance
3207 IF (NVL(p_disposition_rec.instance_id, l_pc_rem_instance_id) <> l_pc_rem_instance_id) THEN
3208 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_INV_INSTANT_ID');
3209 FND_MESSAGE.SET_TOKEN('INSTANT_ID', p_disposition_rec.instance_id);
3210 FND_MSG_PUB.ADD;
3211 END IF;
3212 ELSE
3213 -- G_PART_CHANGE_REMOVE is not possible since the position is not empty
3214 -- May have to throw an exception if control come here
3215 NULL;
3216 END IF;
3217 ELSE
3218 IF(nvl(l_pos_instance_id, -1) <> nvl(p_disposition_rec.instance_id, -1)) THEN
3219 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_INST_NO_MATCH'); -- Position's instance and disposition instance do not match
3220 FND_MESSAGE.Set_Token('POS_INSTANT_ID', l_pos_instance_id);
3221 FND_MESSAGE.Set_Token('DIS_INSTANT_ID', p_disposition_rec.instance_id);
3222 FND_MSG_PUB.ADD;
3223 END IF;
3224 END IF; -- Part Change
3225 END IF; -- l_mapping_status
3226 END IF; -- end path_position
3227
3228 --ITEM ---------------------------------
3229
3230 IF(p_disposition_rec.inventory_item_id IS NOT NULL) THEN
3231 --dbms_output.put_line(SubStr('In Validate_For_Create -- Check ITem ', 1, 255));
3232 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3233 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Check Check Item');
3234 END IF;
3235 IF (l_position_empty = TRUE) THEN
3236 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_DIS_POS_EMPTY_NO_ITM'); --Item does not belong to the item group
3237 FND_MSG_PUB.ADD;
3238 END IF;
3239
3240 validate_item(p_disposition_rec.inventory_item_id, p_disposition_rec.item_org_id, p_disposition_rec.workorder_id);
3241
3242 --item group exist requires item to exist
3243 IF(p_disposition_rec.item_group_id IS NOT NULL) THEN
3244 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3245 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Check ITem and item group relation');
3246 END IF;
3247 --dbms_output.put_line(SubStr('In Validate_For_Create -- Check ITem and item group relation ', 1, 255));
3248 OPEN item_in_itemgrp_csr( p_disposition_rec.inventory_item_id, p_disposition_rec.item_group_id);
3249 FETCH item_in_itemgrp_csr INTO l_exist;
3250 IF item_in_itemgrp_csr%NOTFOUND THEN
3251 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_DIS_NOT_IN_ITMGRP'); --Item does not belong to the item group
3252 FND_MSG_PUB.ADD;
3253 END IF;
3254 CLOSE item_in_itemgrp_csr;
3255 END IF; -- item_group_id is not null
3256
3257 -- Bug#:4059944
3258 -- Forcing the user to select an instance if the item is Trackable
3259 OPEN item_is_ib_trackable (p_disposition_rec.inventory_item_id);
3260 FETCH item_is_ib_trackable INTO item_is_trackable;
3261 CLOSE item_is_ib_trackable;
3262 IF( item_is_trackable ='Y') THEN
3263 IF (p_disposition_rec.instance_id IS NULL) THEN
3264 -- validation to force the user to pick an instance if Disposition Item is Trackable in the IB
3265 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_TR_ITEM_INST_MAND'); -- Invalid item group
3266 FND_MESSAGE.Set_Token('ITEM_NAME',p_disposition_rec.item_number);
3267 FND_MSG_PUB.ADD;
3268 END IF;
3269 ELSE --start fix Bug#4075758 Item is non-tracked
3270 validate_Item_Control(p_disposition_rec.inventory_item_id , p_disposition_rec.item_org_id,
3271 p_disposition_rec.serial_number,
3272 p_disposition_rec.item_revision,
3273 p_disposition_rec.lot_number);
3274 --end Bug#4075758
3275
3276 END IF;
3277 END IF; -- item id is not null
3278
3279 --ITEM GROUP-----------------------------
3280 IF(p_disposition_rec.item_group_id IS NOT NULL) THEN
3281 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3282 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Check ITem group ');
3283 END IF;
3284 --dbms_output.put_line(SubStr('In Validate_For_Create -- Check ITem Group ', 1, 255));
3285 OPEN val_item_group_csr(p_disposition_rec.item_group_id);
3286 FETCH val_item_group_csr INTO l_exist;
3287 IF (val_item_group_csr%NOTFOUND) THEN
3288 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_INV_ITEM_GRP'); -- Invalid item group
3289 FND_MSG_PUB.ADD;
3290 END IF;
3291 CLOSE val_item_group_csr;
3292
3293 IF(p_disposition_rec.inventory_item_Id IS NULL) THEN
3294 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_ITMGRP_REQ_ITM'); --Since item group is present, item number needs to be entered.
3295 FND_MSG_PUB.ADD;
3296 END IF;
3297 --path position id exist then item group must be null
3298 IF(p_disposition_rec.path_position_id IS NOT NULL)THEN
3299 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_POS_ITMGRP'); -- Can only enter either position or item group but not both.
3300 FND_MSG_PUB.ADD;
3301 END IF;
3302 END IF; -- item_group_id is not null
3303
3304 --INSTANCE-----------------------------
3305 IF(p_disposition_rec.instance_id IS NOT NULL) THEN
3306 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3307 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Check Instance_id');
3308 END IF;
3309 --dbms_output.put_line(SubStr('In Validate_For_Create -- Check Instance Number', 1, 255));
3310 validate_instance(p_disposition_rec.instance_id,
3311 p_disposition_rec.workorder_id,
3312 p_disposition_rec.path_position_id,
3313 p_disposition_rec.part_change_id);
3314
3315 IF(p_disposition_rec.inventory_item_id IS NULL) THEN
3316 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_ITEM_REQ'); -- Item number is required
3317 FND_MSG_PUB.ADD;
3318 ELSE
3319 OPEN get_item_id_csr(p_disposition_rec.instance_id);
3320 FETCH get_item_id_csr INTO l_item_id;
3321 IF l_item_id <> p_disposition_rec.inventory_item_id THEN
3322 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_ITEM_NO_MATCH'); -- Item does not match with instance's item.
3323 FND_MSG_PUB.ADD;
3324 END IF;
3325 CLOSE get_item_id_csr;
3326 END IF;
3327 END IF;
3328
3329 --LOT NUMBER----------------------------------
3330 IF(p_disposition_rec.lot_number IS NOT NULL) THEN
3331 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3332 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Check Lot_number');
3333 END IF;
3334 --dbms_output.put_line(SubStr('In Validate_For_Create -- Check Lot Number', 1, 255));
3335 IF(p_disposition_rec.inventory_item_id IS NULL) THEN
3336 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_ITEM_REQ'); -- Item number is required
3337 FND_MSG_PUB.ADD;
3338 ELSE
3339 OPEN val_lot_number_csr(p_disposition_rec.lot_number, p_disposition_rec.inventory_item_id);
3340 FETCH val_lot_number_csr INTO l_exist;
3341 IF (val_lot_number_csr%NOTFOUND) THEN
3342 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_INV_LOT'); -- Invalid lot_number and item combination
3343 FND_MSG_PUB.ADD;
3344 END IF;
3345 CLOSE val_lot_number_csr;
3346 END IF;
3347 END IF;
3348
3349 --SERIAL NUMBER -----------------------------------
3350 IF(p_disposition_rec.serial_number IS NOT NULL) THEN
3351 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3352 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Check Serial_Number');
3353 END IF;
3354 --dbms_output.put_line(SubStr('In Validate_For_Create -- Check Serial Number', 1, 255));
3355 IF(p_disposition_rec.inventory_item_id IS NULL) THEN
3356 --dbms_output.put_line(SubStr('In Validate_For_Create -- Check Serial Number--check item is null', 1, 255));
3357 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_ITEM_REQ'); -- Item is required
3358 FND_MSG_PUB.ADD;
3359 ELSE
3360 --dbms_output.put_line(SubStr('In Validate_For_Create -- before open val_serial_number_csr', 1, 255));
3361 OPEN val_serial_number_csr(p_disposition_rec.serial_number, p_disposition_rec.inventory_item_id);
3362 FETCH val_serial_number_csr INTO l_exist;
3363 IF (val_serial_number_csr%NOTFOUND) THEN
3364 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_INV_SERIAL'); -- Invalid serial number and item combination
3365 FND_MSG_PUB.ADD;
3366 END IF;
3367 CLOSE val_serial_number_csr;
3368 END IF;
3369 --dbms_output.put_line(SubStr('In Validate_For_Create --end check serial Number', 1, 255));
3370 END IF;
3371
3372 --QUANTITY -----------------------------------
3373 IF (p_disposition_rec.quantity IS NULL AND l_position_empty = FALSE) THEN
3374 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_QTY_REQ'); -- Quantity cannot be null
3375 FND_MSG_PUB.ADD;
3376 ELSIF (p_disposition_rec.quantity IS NOT NULL) THEN
3377 IF p_disposition_rec.quantity <= 0 THEN
3378 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_QTY_LESS_ZERO'); -- Quantity must be greater than zero
3379 FND_MSG_PUB.ADD;
3380 END IF;
3381 IF p_disposition_rec.instance_id IS NOT NULL THEN
3382 OPEN instance_quantity_csr(p_disposition_rec.instance_id);
3383 FETCH instance_quantity_csr into l_quantity;
3384 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 13-Dec-2007
3385 -- The quantity check should be modified from '<>' to '>'. Subsequently, the error has been modified to
3386 -- 'Quantity should not be greater than instance quantity'.
3387 IF (p_disposition_rec.quantity > l_quantity) THEN
3388 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_QTY_INST_QTY'); -- Quantity cannot be different from instance's quantity
3389 FND_MSG_PUB.ADD;
3390 END IF;
3391 CLOSE instance_quantity_csr;
3392 END IF;
3393 END IF;
3394
3395 --UOM-------------------------------------------------------------
3396 IF( p_disposition_rec.uom IS NULL AND l_position_empty = FALSE )THEN
3397 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_UOM_REQ'); -- UOM cannot be null
3398 FND_MSG_PUB.ADD;
3399 ELSIF ( p_disposition_rec.uom IS NOT NULL) THEN
3400 OPEN val_uom_csr(p_disposition_rec.uom);
3401 FETCH val_uom_csr INTO l_exist;
3402 IF val_uom_csr%NOTFOUND THEN
3403 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_INV_UOM'); -- Invalid UOM.
3404 FND_MSG_PUB.ADD;
3405 END IF;
3406 CLOSE val_uom_csr;
3407
3408 IF p_disposition_rec.instance_id IS NOT NULL THEN
3409 OPEN instance_uom_csr(p_disposition_rec.instance_id);
3410 FETCH instance_uom_csr INTO l_uom;
3411 IF (p_disposition_rec.uom <> l_uom) THEN
3412 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_INV_INST_UOM'); -- Invalid UOM. UOM must be the same as instance's UOM.
3413 FND_MSG_PUB.ADD;
3414 END IF;
3415 CLOSE instance_uom_csr;
3416 END IF;
3417
3418 IF p_disposition_rec.inventory_item_id IS NOT NULL THEN
3419 OPEN item_class_uom_csr(p_disposition_rec.uom, p_disposition_rec.inventory_item_id);
3420 FETCH item_class_uom_csr into l_exist;
3421 IF item_class_uom_csr%NOTFOUND THEN
3422 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_INV_ITEM_UOM'); -- Invalid UOM. UOM must belong to the item's primary uom
3423 FND_MSG_PUB.ADD;
3424 END IF;
3425 CLOSE item_class_uom_csr;
3426 END IF;
3427 END IF;
3428
3429 --COLLECTION ID-------------------------------------------------------------
3430 -- Added by jaramana on March 25, 2005 to fix bug 4243200
3431 -- First check if a QA Plan is defined in the workorder Org.
3432 AHL_QA_RESULTS_PVT.get_qa_plan( p_api_version => 1.0,
3433 p_init_msg_list => FND_API.G_False,
3434 p_commit => FND_API.G_FALSE,
3435 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3436 p_default => FND_API.G_FALSE,
3437 p_organization_id => p_disposition_rec.item_org_id,
3438 p_transaction_number => 2004, -- MRB_TRANSACTION_NUMBER
3439 p_col_trigger_value => fnd_profile.value('AHL_MRB_DISP_PLAN_TYPE'),
3440 x_return_status => l_return_status,
3441 x_msg_count => l_msg_count,
3442 x_msg_data => l_msg_data,
3443 x_plan_id => l_plan_id);
3444 --IF p_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_MRB') AND p_disposition_rec.instance_id IS NOT NULL THEN
3445 IF p_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_MRB') AND
3446 p_disposition_rec.instance_id IS NOT NULL AND
3447 l_plan_id IS NOT NULL THEN
3448 IF p_disposition_rec.collection_id IS NULL THEN
3449 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_QA_RESULT_REQ'); -- QA Result Required
3450 FND_MSG_PUB.ADD;
3451 ELSE
3452 validate_collection_id(p_disposition_rec.collection_id);
3453 END IF;
3454 END IF;
3455 -- End fix for bug 4243200
3456
3457 IF ((p_disposition_rec.instance_id IS NULL OR
3458 (nvl(p_disposition_rec.condition_id, -1) <> fnd_profile.value('AHL_MTL_MAT_STATUS_MRB')
3459 AND nvl(p_disposition_rec.condition_id, -1) <> fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE')
3460 )
3461 )AND
3462 ( p_disposition_rec.summary IS NOT NULL OR p_disposition_rec.problem_code IS NOT NULL
3463 OR p_disposition_rec.severity_id IS NOT NULL
3464 )) THEN
3465 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_SR_NOT_REQ'); --Non Conformance (SR) information is not required
3466 FND_MSG_PUB.ADD;
3467 END IF;
3468
3469 --OPERATION_ID--------------------------------------------------------
3470 IF (p_disposition_rec.wo_operation_id IS NOT NULL) THEN
3471 validate_wo_operation(p_disposition_rec.workorder_id, p_disposition_rec.wo_operation_id );
3472 END IF;
3473
3474 --validate item revision--------------------------------------------------------
3475 IF (p_disposition_rec.item_revision IS NOT NULL) THEN
3476 IF p_disposition_rec.inventory_item_id IS NULL THEN
3477 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_DIS_ITM_REV_REQ_ITM'); --Cannot enter an item revision without entering item
3478 FND_MSG_PUB.ADD;
3479 ELSE
3480 OPEN item_revisions_csr(p_disposition_rec.item_revision, p_disposition_rec.inventory_item_id, p_disposition_rec.item_org_id);
3481 FETCH item_revisions_csr INTO l_exist;
3482 IF item_revisions_csr%NOTFOUND THEN
3483 FND_MESSAGE.Set_Name(G_APP_NAME, 'AHL_PRD_DIS_INV_ITEM_REV'); --Invalid item revision
3484 FND_MSG_PUB.ADD;
3485 END IF;
3486 CLOSE item_revisions_csr;
3487 END IF;
3488 END IF;
3489
3490 IF FND_MSG_PUB.count_msg > 0 THEN
3491 RAISE FND_API.G_EXC_ERROR;
3492 END IF;
3493
3494 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3495 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
3496 END IF;
3497
3498 --dbms_output.put_line(SubStr('End Validate_For_Create', 1, 255));
3499 END validate_for_create;
3500
3501 --------------------------------------------------------------------------------
3502 PROCEDURE convert_values_to_ids
3503 (
3504 p_x_prd_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type
3505 ) IS
3506
3507 CURSOR instance_id_csr(p_instance_number IN VARCHAR2) IS
3508 SELECT instance_id from csi_item_instances
3509 WHERE instance_number = p_instance_number;
3510
3511 CURSOR item_group_id_csr(p_item_group_name IN VARCHAR2) IS
3512 SELECT item_group_id from ahl_item_groups_b
3513 WHERE name = p_item_group_name AND status_code = 'COMPLETE';
3514
3515 CURSOR item_id_csr(p_item_number IN VARCHAR2) IS
3516 SELECT inventory_item_id FROM MTL_SYSTEM_ITEMS_KFV
3517 WHERE concatenated_segments = p_item_number;
3518
3519 CURSOR condition_id_csr(p_condition_meaning IN VARCHAR2) IS
3520 SELECT status_id FROM mtl_material_statuses_vl
3521 WHERE status_code = p_condition_meaning;
3522
3523
3524 CURSOR wo_operation_id_csr(p_workorder_id IN NUMBER, p_operation_seq IN NUMBER) IS
3525 SELECT workorder_operation_id from ahl_workorder_operations
3526 WHERE workorder_id = p_workorder_id AND operation_sequence_num = p_operation_seq;
3527
3528 CURSOR severity_id_csr(p_severity_name IN VARCHAR2) IS
3529 SELECT incident_severity_id from cs_incident_severities_vl
3530 WHERE name = p_severity_name
3531 AND incident_subtype = 'INC'
3532 AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate)) and trunc(nvl(end_date_active,sysdate));
3533
3534 CURSOR problem_code_csr(p_problem_meaning IN VARCHAR2) IS
3535 /*
3536 SELECT fl.lookup_code FROM fnd_lookup_values_vl FL
3537 WHERE fl.meaning = p_problem_meaning
3538 AND lookup_type = 'REQUEST_PROBLEM_CODE'
3539 AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active,sysdate)) AND trunc(nvl(end_date_active,sysdate))
3540 AND (( NOT EXISTS (SELECT 1 FROM CS_SR_PROB_CODE_MAPPING_V WHERE INCIDENT_TYPE_ID = FND_PROFILE.Value('AHL_PRD_SR_TYPE')
3541 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))) )
3542 OR ( EXISTS (SELECT 1 FROM CS_SR_PROB_CODE_MAPPING_V MAP WHERE MAP.INCIDENT_TYPE_ID = FND_PROFILE.Value('AHL_PRD_SR_TYPE')
3543 AND MAP.INVENTORY_ITEM_ID IS NULL AND MAP.PROBLEM_CODE = FL.LOOKUP_CODE
3544 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(MAP.START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(MAP.END_DATE_ACTIVE,SYSDATE)))));
3545 */
3546 -- AnRaj: Changed query, Perf Bug#4908609,Issue#6
3547 SELECT fl.lookup_code
3548 FROM fnd_lookup_values_vl FL
3549 WHERE fl.meaning = p_problem_meaning
3550 AND lookup_type = 'REQUEST_PROBLEM_CODE'
3551 AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active,sysdate))
3552 AND trunc(nvl(end_date_active,sysdate)) ;
3553
3554 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || 'convert_values_to_ids';
3555 l_instance_id NUMBER;
3556 l_item_group_id NUMBER;
3557 BEGIN
3558
3559 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3560 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
3561 END IF;
3562 --dbms_output.put_line(SubStr('Begin Convert_val_to_id', 1, 255));
3563 IF (p_x_prd_disposition_rec.instance_number IS NOT NULL AND p_x_prd_disposition_rec.instance_number <> FND_API.G_MISS_CHAR) THEN
3564 OPEN instance_id_csr(p_x_prd_disposition_rec.instance_number);
3565 FETCH instance_id_csr INTO p_x_prd_disposition_rec.instance_id;
3566 IF(instance_id_csr%NOTFOUND) THEN
3567 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_INV_INST_NUM');
3568 FND_MESSAGE.Set_Token('INTANCE_NUM', p_x_prd_disposition_rec.instance_number);
3569 FND_MSG_PUB.ADD;
3570 END IF;
3571 CLOSE instance_id_csr;
3572 ELSIF(p_x_prd_disposition_rec.instance_number = FND_API.G_MISS_CHAR) THEN
3573 p_x_prd_disposition_rec.instance_id := NULL;
3574 END IF;
3575
3576 IF (p_x_prd_disposition_rec.item_group_name IS NOT NULL AND p_x_prd_disposition_rec.item_group_name <> FND_API.G_MISS_CHAR) THEN
3577 OPEN item_group_id_csr(p_x_prd_disposition_rec.item_group_name);
3578 FETCH item_group_id_csr INTO p_x_prd_disposition_rec.item_group_id;
3579 IF(item_group_id_csr%NOTFOUND) THEN
3580 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_INV_ITM_GRPNAME');
3581 FND_MESSAGE.Set_Token('ITEM_GROUP', p_x_prd_disposition_rec.item_group_name);
3582 FND_MSG_PUB.ADD;
3583 END IF;
3584 CLOSE item_group_id_csr;
3585 ELSIF(p_x_prd_disposition_rec.item_group_name = FND_API.G_MISS_CHAR) THEN
3586 p_x_prd_disposition_rec.item_group_id := NULL;
3587 END IF;
3588
3589 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3590 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before convert Item Number: ' ||p_x_prd_disposition_rec.item_number);
3591 END IF;
3592 --dbms_output.put_line(SubStr('convert Item Number', 1, 255));
3593 IF (p_x_prd_disposition_rec.item_number IS NOT NULL AND p_x_prd_disposition_rec.item_number <> FND_API.G_MISS_CHAR) THEN
3594 OPEN item_id_csr(p_x_prd_disposition_rec.item_number);
3595 FETCH item_id_csr INTO p_x_prd_disposition_rec.inventory_item_id;
3596 IF(item_id_csr%NOTFOUND) THEN
3597 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_INV_ITEM_NUM');
3598 FND_MESSAGE.Set_Token('ITEM_NUM', p_x_prd_disposition_rec.item_number);
3599 FND_MSG_PUB.ADD;
3600 END IF;
3601 CLOSE item_id_csr;
3602 ELSIF(p_x_prd_disposition_rec.item_number = FND_API.G_MISS_CHAR) THEN
3603 p_x_prd_disposition_rec.inventory_item_id := NULL;
3604 END IF;
3605
3606 IF (p_x_prd_disposition_rec.operation_sequence IS NOT NULL AND p_x_prd_disposition_rec.operation_sequence <> FND_API.G_MISS_NUM) THEN
3607 OPEN wo_operation_id_csr(p_x_prd_disposition_rec.workorder_id, p_x_prd_disposition_rec.operation_sequence);
3608 FETCH wo_operation_id_csr INTO p_x_prd_disposition_rec.wo_operation_id;
3609 IF(wo_operation_id_csr%NOTFOUND) THEN
3610 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_INV_OPER_SEQ');
3611 FND_MESSAGE.Set_Token('OPER_SEQ', p_x_prd_disposition_rec.operation_sequence);
3612 FND_MSG_PUB.ADD;
3613 END IF;
3614 CLOSE wo_operation_id_csr;
3615 ELSIF(p_x_prd_disposition_rec.operation_sequence = FND_API.G_MISS_NUM) THEN
3616 p_x_prd_disposition_rec.wo_operation_id := NULL;
3617 END IF;
3618
3619 IF (p_x_prd_disposition_rec.condition_meaning IS NOT NULL AND p_x_prd_disposition_rec.condition_meaning <> FND_API.G_MISS_CHAR) THEN
3620 OPEN condition_id_csr(p_x_prd_disposition_rec.condition_meaning);
3621 FETCH condition_id_csr INTO p_x_prd_disposition_rec.condition_id;
3622 IF(condition_id_csr%NOTFOUND) THEN
3623 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_INV_CONDITION');
3624 FND_MESSAGE.Set_Token('CONDITION', p_x_prd_disposition_rec.condition_meaning);
3625 FND_MSG_PUB.ADD;
3626 END IF;
3627 CLOSE condition_id_csr;
3628 ELSIF(p_x_prd_disposition_rec.condition_meaning = FND_API.G_MISS_CHAR) THEN
3629 p_x_prd_disposition_rec.condition_id := NULL;
3630 END IF;
3631
3632 IF (p_x_prd_disposition_rec.severity_name IS NOT NULL AND p_x_prd_disposition_rec.severity_name <> FND_API.G_MISS_CHAR) THEN
3633 OPEN severity_id_csr( p_x_prd_disposition_rec.severity_name);
3634 FETCH severity_id_csr INTO p_x_prd_disposition_rec.severity_id;
3635 IF(severity_id_csr%NOTFOUND) THEN
3636 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_INV_SEVERITY_NAME');
3637 FND_MESSAGE.Set_Token('NAME', p_x_prd_disposition_rec.severity_name);
3638 FND_MSG_PUB.ADD;
3639 END IF;
3640 CLOSE severity_id_csr;
3641 ELSIF(p_x_prd_disposition_rec.severity_name = FND_API.G_MISS_CHAR) THEN
3642 p_x_prd_disposition_rec.severity_id := NULL;
3643 END IF;
3644
3645 IF (p_x_prd_disposition_rec.problem_meaning IS NOT NULL AND p_x_prd_disposition_rec.problem_meaning <> FND_API.G_MISS_CHAR) THEN
3646 OPEN problem_code_csr( p_x_prd_disposition_rec.problem_meaning);
3647 FETCH problem_code_csr INTO p_x_prd_disposition_rec.problem_code;
3648 IF(problem_code_csr%NOTFOUND) THEN
3649 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_INV_PROBLEM');
3650 FND_MESSAGE.Set_Token('PROBLEM', p_x_prd_disposition_rec.problem_meaning);
3651 FND_MSG_PUB.ADD;
3652 END IF;
3653 CLOSE problem_code_csr;
3654 ELSIF(p_x_prd_disposition_rec.problem_meaning = FND_API.G_MISS_CHAR) THEN
3655 p_x_prd_disposition_rec.problem_code := NULL;
3656 END IF;
3657
3658 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3659 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
3660 END IF;
3661
3662
3663 --dbms_output.put_line(SubStr('End Convert_val_to_id', 1, 255));
3664
3665 END convert_values_to_ids;
3666
3667
3668
3669
3670
3671
3672 --------------------------------------------------------------------------------
3673 PROCEDURE validate_collection_id(p_collection_id IN NUMBER) IS
3674 CURSOR val_Collection_id_csr(p_collection_id IN NUMBER) IS
3675 SELECT 'x' FROM qa_results WHERE collection_id = p_collection_id;
3676
3677 l_exist VARCHAR(1);
3678
3679 BEGIN
3680 IF p_collection_id IS NOT NULL THEN
3681 OPEN val_collection_id_csr(p_collection_id);
3682 FETCH val_collection_id_csr into l_exist;
3683 IF val_collection_id_csr%NOTFOUND THEN
3684 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_INV_COLLECTION_ID'); -- Invalid collection Id
3685 FND_MESSAGE.Set_Token('COLL_ID', p_collection_id);
3686 FND_MSG_PUB.ADD;
3687 END IF;
3688 END IF;
3689 END validate_collection_id;
3690
3691
3692 --Validate_workorder----------------------------------------------------
3693 PROCEDURE validate_workorder(p_workorder_id IN NUMBER) IS
3694 CURSOR workorder_csr(p_workorder_id IN NUMBER) IS
3695 SELECT 'x' FROM AHL_WORKORDERS
3696 WHERE workorder_id = p_workorder_id;
3697
3698 l_exist VARCHAR(1);
3699
3700 BEGIN
3701 OPEN workorder_csr(p_workorder_id);
3702 FETCH workorder_csr INTO l_exist;
3703 IF workorder_csr%NOTFOUND THEN
3704 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_INV_WORKORDER_ID'); -- Invalid Workorder Id
3705 FND_MESSAGE.set_token('WORKORDER_ID', p_workorder_id);
3706 FND_MSG_PUB.ADD;
3707 CLOSE workorder_csr;
3708 RAISE FND_API.G_EXC_ERROR;
3709 ELSE
3710 CLOSE workorder_csr;
3711 END IF;
3712
3713 END validate_workorder;
3714
3715
3716 --Validate_Path_Position---------------------------------------------
3717 PROCEDURE validate_path_position(p_path_position_id IN NUMBER) IS
3718 CURSOR path_position_csr(p_path_position_id IN NUMBER) IS
3719 SELECT 'x' FROM ahl_mc_path_positions
3720 WHERE path_position_id = p_path_position_id;
3721
3722 l_exist VARCHAR(1);
3723
3724 BEGIN
3725 OPEN path_position_csr(p_path_position_id);
3726 FETCH path_position_csr INTO l_exist;
3727 IF path_position_csr%NOTFOUND THEN
3728 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_INV_PATH_POSITION');
3729 FND_MESSAGE.SET_TOKEN('PATH_POS_ID', p_path_position_id);
3730 FND_MSG_PUB.ADD;
3731 CLOSE path_position_csr;
3732 RAISE FND_API.G_EXC_ERROR;
3733 ELSE
3734 CLOSE path_position_csr;
3735 END IF;
3736 END validate_path_position;
3737
3738 --Validate_item---------------------------------------------
3739 PROCEDURE validate_item(p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER, p_workorder_id IN NUMBER) IS
3740
3741 CURSOR get_wo_organization_csr(p_workorder_id NUMBER) IS
3742 SELECT vi.organization_id from ahl_workorders wo, ahl_visits_b vi
3743 WHERE wo.workorder_id = p_workorder_id
3744 AND wo.visit_id = vi.visit_id;
3745
3746 CURSOR val_item_csr(p_item_id NUMBER, p_organization_id NUMBER) IS
3747 SELECT 'x' FROM mtl_system_items_kfv
3748 WHERE inventory_item_id = p_inventory_item_id
3749 AND organization_id = p_organization_id;
3750
3751
3752 l_exist VARCHAR(1);
3753 l_wo_organization_id NUMBER;
3754
3755 BEGIN
3756
3757
3758 OPEN val_item_csr(p_inventory_item_id, p_organization_id);
3759 FETCH val_item_csr INTO l_exist;
3760 IF val_item_csr%NOTFOUND THEN
3761 CLOSE val_item_csr; --Close cursor before raising exception
3762 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_INV_ITEM_ID');
3763 FND_MESSAGE.SET_TOKEN('ITEM_ID', p_inventory_item_id);
3764 FND_MSG_PUB.ADD;
3765 RAISE FND_API.G_EXC_ERROR;
3766 END IF;
3767 CLOSE val_item_csr;
3768
3769
3770 OPEN get_wo_organization_csr(p_workorder_id);
3771 FETCH get_wo_organization_csr INTO l_wo_organization_id;
3772 CLOSE get_wo_organization_csr;
3773
3774 IF l_wo_organization_id <> p_organization_id THEN
3775 -- need to check whether if item is defined in workorder organization
3776 OPEN val_item_csr(p_inventory_item_id, l_wo_organization_id);
3777 FETCH val_item_csr INTO l_exist;
3778 IF val_item_csr%NOTFOUND THEN
3779 CLOSE val_item_csr; --close cursor before raise an exception
3780 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_ITEM_NOT_IN_WO_ORG'); --Item is not defined in workorder's organization
3781 FND_MESSAGE.SET_TOKEN('ITEM_ID', p_inventory_item_id);
3782 FND_MSG_PUB.ADD;
3783 RAISE FND_API.G_EXC_ERROR;
3784 END IF;
3785 CLOSE val_item_csr;
3786 END IF;
3787
3788 END validate_item;
3789
3790 --validate_Item_Control--------------------------------------
3791 --Check if items is serial control then require serial_number
3792 -- if item is lot control then requires lot_number
3793 -- if item is quantity revision control then requires revision number
3794 PROCEDURE validate_Item_Control(p_item_id IN NUMBER, p_org_id IN NUMBER,
3795 p_serial_number IN VARCHAR2,
3796 p_item_rev_number IN VARCHAR2,
3797 p_lot_number IN VARCHAR2) IS
3798
3799
3800 CURSOR item_control_csr(p_item_id IN NUMBER, p_org_id IN NUMBER) IS
3801 SELECT serial_number_control_code, revision_qty_control_code, lot_control_code
3802 FROM MTL_SYSTEM_ITEMS_KFV
3803 WHERE inventory_item_id = p_item_id
3804 AND organization_id = p_org_id;
3805
3806 l_item_crl_rec item_control_csr%ROWTYPE;
3807 l_serial_flag VARCHAR2(1);
3808 l_rev_qty_flag VARCHAR2(1);
3809 l_lot_flag VARCHAR2(1);
3810
3811 BEGIN
3812
3813 OPEN item_control_csr(p_item_id, p_org_id);
3814 FETCH item_control_csr INTO l_item_crl_rec;
3815 IF item_control_csr%FOUND THEN
3816 IF l_item_crl_rec.lot_control_code = 2 THEN
3817 l_lot_flag:='Y';
3818 ELSE
3819 l_lot_flag:='N';
3820 END IF;
3821
3822 IF l_item_crl_rec.revision_qty_control_code = 2 THEN
3823 l_rev_qty_flag:='Y';
3824 ELSE
3825 l_rev_qty_flag:='N';
3826 END IF;
3827
3828 IF l_item_crl_rec.serial_number_control_code = 1 THEN
3829 l_serial_flag:='N';
3830 ELSE
3831 l_serial_flag:='Y';
3832 END IF;
3833 END IF;
3834 CLOSE item_control_csr;
3835
3836 IF l_serial_flag = 'Y' AND p_serial_number IS NULL THEN
3837 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DIS_SERIAL_REQ'); --Serial Number is required
3838 FND_MSG_PUB.ADD;
3839 END IF;
3840
3841 IF l_rev_qty_flag = 'Y' AND p_item_rev_number IS NULL THEN
3842 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DIS_ITEM_REV_REQ'); --Item Revision Number is required
3843 FND_MSG_PUB.ADD;
3844 END IF;
3845
3846 IF l_lot_flag = 'Y' AND p_lot_number IS NULL THEN
3847 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DIS_LOT_NUM_REQ'); --Lot Number is required
3848 FND_MSG_PUB.ADD;
3849 END IF;
3850
3851 END validate_Item_Control;
3852
3853
3854
3855
3856 --Validate Instance------------------------------------------------------------
3857
3858 PROCEDURE validate_instance(p_instance_id IN NUMBER, p_workorder_id IN NUMBER, p_path_position_id IN NUMBER, p_part_change_id IN NUMBER) IS
3859
3860 CURSOR instance_csr(p_instance_id IN NUMBER) IS
3861 SELECT 'x' from csi_item_instances
3862 where instance_id = p_instance_id;
3863
3864 CURSOR instance_in_wip_csr(p_instance_id IN NUMBER, p_workorder_id IN NUMBER) IS
3865 SELECT 'x' from csi_item_instances csi, ahl_workorders wo
3866 WHERE instance_id = p_instance_id
3867 and wo.wip_entity_id = csi.wip_job_id
3868 and csi.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
3869 and trunc(sysdate) between trunc(nvl(csi.active_start_date, sysdate)) and trunc(nvl(csi.active_end_date, sysdate));
3870
3871 CURSOR instance_in_disp_csr(p_instance_id IN NUMBER, p_workorder_id IN NUMBER) IS
3872 SELECT 'x' from ahl_prd_dispositions_b WHERE
3873 workorder_id = p_workorder_id
3874 and instance_id = p_instance_id
3875 and nvl(status_code, ' ') NOT IN ('COMPLETE', 'TERMINATED');
3876
3877 --Begin Performance Tuning
3878 CURSOR get_wo_instance_id(p_workorder_id IN NUMBER) IS
3879 SELECT nvl(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)
3880 FROM ahl_workorders wo, AHL_VISITS_VL VST, AHL_VISIT_TASKS_VL VTS
3881 WHERE workorder_id = p_workorder_id
3882 and WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
3883 AND VST.VISIT_ID=VTS.VISIT_ID;
3884 --End Performance Tuning
3885
3886 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 13-Dec-2007
3887 -- Cursor to check whether the disposition item is serialized or not.
3888 CURSOR chk_non_serialized_csr(p_instance_id NUMBER) IS
3889 SELECT 'X'
3890 FROM mtl_system_items_b mtl, csi_item_instances csi
3891 WHERE csi.instance_id = p_instance_id
3892 AND csi.inventory_item_id = mtl.inventory_item_id
3893 AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
3894 AND mtl.serial_number_control_code = 1;
3895
3896 l_wo_instance_id NUMBER;
3897 l_wo_root_instance_id NUMBER; --Root Instance of workorder instance
3898 l_dis_root_instance_id NUMBER; --Root Instance of disposition instance.
3899 l_exist VARCHAR(1);
3900 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || 'validate_instance' ;
3901
3902 -- Added by jaramana on August 7, 2007 to support IB Trees
3903 l_unit_config_id NUMBER;
3904 l_unit_config_name ahl_unit_config_headers.name%TYPE;
3905 l_return_status VARCHAR2(1);
3906 l_dummy VARCHAR2(1);
3907
3908 BEGIN
3909
3910 OPEN instance_csr(p_instance_id);
3911 FETCH instance_csr INTO l_exist;
3912 IF instance_csr%NOTFOUND THEN
3913 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_INV_INSTANT_ID');
3914 FND_MESSAGE.SET_TOKEN('INSTANT_ID', p_instance_id);
3915 FND_MSG_PUB.ADD;
3916 END IF;
3917 CLOSE instance_csr;
3918
3919 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 13-Dec-2007
3920 -- Multiple dispositions for the same instance/position are allowed for non-serialized items.
3921 OPEN chk_non_serialized_csr(p_instance_id);
3922 FETCH chk_non_serialized_csr INTO l_dummy;
3923 IF (chk_non_serialized_csr%NOTFOUND) THEN
3924 OPEN instance_in_disp_csr(p_instance_id, p_workorder_id);
3925 FETCH instance_in_disp_csr INTO l_exist;
3926 IF instance_in_disp_csr%FOUND THEN
3927 -- Added by jaramana on August 7, 2007 to support IB Trees
3928 -- Allow duplicate instances for IB Trees when created from Part Change
3929 AHL_PRD_PARTS_CHANGE_PVT.Get_Unit_Config_Information(p_item_instance_id => NULL,
3930 p_workorder_id => p_workorder_id,
3931 x_unit_config_id => l_unit_config_id,
3932 x_unit_config_name => l_unit_config_name,
3933 x_return_status => l_return_status);
3934 IF (l_unit_config_name IS NULL AND p_part_change_id IS NOT NULL) THEN
3935 -- IB Tree, Disposition created from Part Change UI: Allow duplicates
3936 NULL;
3937 ELSE
3938 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DIS_INST_IN_DISP'); --Instance already exist in another not-completed or not-terminated disposition.
3939 FND_MSG_PUB.ADD;
3940 END IF;
3941 -- End changes by jaramana on August 7, 2007 to support IB Trees
3942 END IF;
3943 CLOSE instance_in_disp_csr;
3944 END IF;
3945 CLOSE chk_non_serialized_csr;
3946
3947 IF p_path_position_id IS NOT NULL THEN
3948 IF p_part_change_id IS NOT NULL THEN
3949 -- Called from Part Change UI
3950 -- Part Change (Removal or Swap) has already occurred
3951 -- Cannot verify if the disposition instance is in the same unit as the workorder
3952 -- since the disposition instance has already been removed
3953 NULL;
3954 ELSE
3955 --path position exists then need to check if the instance is in the same tree as workorder instance
3956 --Get Workorder Instance Id
3957 OPEN get_wo_instance_id(p_workorder_id);
3958 FETCH get_wo_instance_id INTO l_wo_instance_id;
3959 CLOSE get_wo_instance_id;
3960
3961 IF l_wo_instance_id <> p_instance_id THEN
3962 --get root instance for workorder instance
3963 l_wo_root_instance_id := get_root_instance_id(l_wo_instance_id);
3964
3965 --get root instance for disposition instance
3966 l_dis_root_instance_id := get_root_instance_id(p_instance_id);
3967
3968 IF nvl(l_wo_root_instance_id, -1) <> nvl(l_dis_root_instance_id, -1) THEN
3969 --dbms_output.put_line(' before throw the error root instance are not the same');
3970 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3971 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_wo_instance_id:' || l_wo_instance_id);
3972 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_wo_root_instance_id:' || l_wo_root_instance_id);
3973 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_dis_root_instance_id:' || l_dis_root_instance_id);
3974 END IF;
3975 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DIS_INST_IN_UC'); -- Instance is not in the same unit as workorder instance
3976 FND_MSG_PUB.ADD;
3977 RAISE FND_API.G_EXC_ERROR;
3978 END IF; -- Root Instances are not the same
3979 END IF; -- l_wo_instance_id <> p_instance_id
3980 END IF; -- p_part_change_id is null
3981 ELSE --then it is a stand alone instance need to check if instance is issued to the job
3982 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3983 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Stand alone instance');
3984 END IF;
3985 -- Following lines commented out by jaramana on August 8, 2007
3986 -- In the case of IB Tree, it does not make sense to force the instance to be in the job.
3987 -- The instance may be currently installed on the IB Tree and creation of Disposition should be
3988 -- be allowed for it (1. From Part Change, the Installation Disposition is created after
3989 -- the instance is installed on the IB Tree. 2. While creating a disposition for removing,
3990 -- the instance is still on the IB Tree which may not have been issued to the job)
3991 /***
3992 OPEN instance_in_wip_csr(p_instance_id, p_workorder_id);
3993 FETCH instance_in_wip_csr INTO l_exist;
3994 IF instance_in_wip_csr%NOTFOUND THEN
3995 CLOSE instance_in_wip_csr;
3996 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_INST_NOT_ISSUED'); -- Instance is not issued to the job
3997 FND_MSG_PUB.ADD;
3998 RAISE FND_API.G_EXC_ERROR;
3999 END IF;
4000 CLOSE instance_in_wip_csr;
4001 ***/
4002 END IF;
4003
4004 --dbms_output.put_line(SubStr('end validate_instance ', 1, 255));
4005 END validate_instance;
4006
4007 -----validate workorder operation--------------------------------------
4008
4009 PROCEDURE validate_wo_operation(p_workorder_id IN NUMBER, p_wo_operation_id IN NUMBER) IS
4010
4011 CURSOR val_wo_operation_csr(p_workorder_id IN NUMBER, p_wo_operation_id IN NUMBER) IS
4012 SELECT 'x' FROM ahl_workorder_operations
4013 WHERE workorder_operation_id = p_wo_operation_id
4014 AND workorder_id = p_workorder_id;
4015
4016 l_exist VARCHAR(1);
4017
4018 BEGIN
4019 OPEN val_wo_operation_csr(p_workorder_id, p_wo_operation_id);
4020 FETCH val_wo_operation_csr INTO l_exist;
4021 IF val_wo_operation_csr%NOTFOUND THEN
4022 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_INV_OPERATION'); --Invalid operation id.
4023 FND_MESSAGE.SET_TOKEN('OPER_ID', p_wo_operation_id);
4024 FND_MSG_PUB.ADD;
4025 END IF;
4026 CLOSE val_wo_operation_csr;
4027
4028 END validate_wo_operation;
4029
4030 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 13-Dec-2007
4031 -- Modified the API to accept the instance quantity, and modified the validations to accommodate: -
4032 -- 1) non-serialized items
4033 -- 2) disposition for empty positions
4034 PROCEDURE validate_part_change(p_part_change_id IN NUMBER, p_disp_instance_id IN NUMBER, p_disp_quantity IN NUMBER) IS
4035
4036 -- Begin Changes made by jaramana on August 07, 2007 for the bug 6328554 (FP of 5948917)
4037 CURSOR part_change_csr IS
4038 SELECT removed_instance_id, installed_instance_id, NVL(part_change_type, 'X'), quantity
4039 FROM ahl_part_changes
4040 WHERE part_change_id = p_part_change_id;
4041
4042 -- Cursor to get the disposition instance details.
4043 CURSOR get_inst_dtls_csr(c_instance_id IN NUMBER) IS
4044 SELECT SERIAL_NUMBER, INVENTORY_ITEM_ID, QUANTITY
4045 FROM CSI_ITEM_INSTANCES
4046 WHERE INSTANCE_ID = c_instance_id;
4047
4048 l_removed_instance_id NUMBER;
4049 l_installed_instance_id NUMBER;
4050 l_part_change_type VARCHAR2(1);
4051 l_disp_inst_dtls get_inst_dtls_csr%ROWTYPE;
4052 l_rem_inst_dtls get_inst_dtls_csr%ROWTYPE;
4053 l_part_change_quantity NUMBER;
4054 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || 'validate_part_change';
4055
4056 BEGIN
4057 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4058 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4059 L_DEBUG_KEY || '.begin ',
4060 'At the start of the procedure.' ||
4061 ' ,p_part_change_id = ' || p_part_change_id ||
4062 ' ,p_disp_instance_id = ' || p_disp_instance_id ||
4063 ' ,p_disp_quantity = ' || p_disp_quantity );
4064 END IF;
4065
4066 -- If the disposition is not for an empty position, then get the disposition instance details.
4067 IF p_disp_instance_id IS NOT NULL THEN
4068 OPEN get_inst_dtls_csr(p_disp_instance_id);
4069 FETCH get_inst_dtls_csr INTO l_disp_inst_dtls;
4070 CLOSE get_inst_dtls_csr;
4071 END IF;
4072
4073 OPEN part_change_csr;
4074 FETCH part_change_csr INTO l_removed_instance_id, l_installed_instance_id, l_part_change_type, l_part_change_quantity;
4075 IF part_change_csr%NOTFOUND THEN
4076 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DIS_INV_PART_CHG_ID'); -- Invalid part change id
4077 FND_MESSAGE.SET_TOKEN('PART_CHNG_ID', p_part_change_id);
4078 FND_MSG_PUB.ADD;
4079 ELSIF (l_part_change_type IN ('R', 'S')) THEN -- Remove or Swap
4080 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 13-Dec-2007
4081 -- Compare instances only if it is serialized.
4082 IF (l_disp_inst_dtls.SERIAL_NUMBER IS NOT NULL) THEN
4083 IF (NVL(p_disp_instance_id, -1) <> nvl(l_removed_instance_id, -1)) THEN
4084 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DIS_INV_REMOVE_INST'); -- Removed instance in part change is not the same as disposition instance
4085 FND_MESSAGE.SET_TOKEN('REMOVED_INST', l_removed_instance_id);
4086 FND_MESSAGE.SET_TOKEN('DISP_INST', p_disp_instance_id);
4087 FND_MSG_PUB.ADD;
4088 END IF;
4089 ELSE
4090 -- Non-serialized: Removed instance can be different from the Disposition Instance.
4091 -- Compare only item and quantity.
4092 IF (NVL(p_disp_instance_id, -1) <> nvl(l_removed_instance_id, -1)) THEN
4093 -- Get the removed instance details.
4094 OPEN get_inst_dtls_csr(l_removed_instance_id);
4095 FETCH get_inst_dtls_csr INTO l_rem_inst_dtls;
4096 CLOSE get_inst_dtls_csr;
4097
4098 IF (l_disp_inst_dtls.inventory_item_id <> l_rem_inst_dtls.inventory_item_id OR
4099 nvl(p_disp_quantity,l_part_change_quantity) <> l_part_change_quantity) THEN
4100 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4101 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'validate part change non serial item' ||
4102 ', l_disp_inst_dtls.inventory_item_i ' || l_disp_inst_dtls.inventory_item_id ||
4103 ', l_rem_inst_dtls.inventory_item_id ' || l_rem_inst_dtls.inventory_item_id ||
4104 ', p_disp_instance_id ' || p_disp_instance_id ||
4105 ', p_removed_instance_id ' || l_removed_instance_id ||
4106 ', p_disp_quantity ' || p_disp_quantity ||
4107 ', l_part_change_quantity ' || l_part_change_quantity);
4108 END IF;
4109
4110 -- The Message AHL_PRD_DIS_INV_REMOVE_INST is not accurate in this case, but is probably ok
4111 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DIS_INV_REMOVE_INST'); -- Removed instance in part change is not the same as disposition instance
4112 FND_MESSAGE.SET_TOKEN('REMOVED_INST', l_removed_instance_id);
4113 FND_MESSAGE.SET_TOKEN('DISP_INST', p_disp_instance_id);
4114 FND_MSG_PUB.ADD;
4115 END IF; -- Item and Quantity Mismatch
4116 END IF; -- Instances are different
4117 END IF; -- Disp Instance's Serial Number is null or not
4118 ELSE
4119 -- Changed since the disposition instance will always
4120 -- (even after installation) be null for a disposition created against an empty position
4121 -- IF (NVL(p_disp_instance_id, nvl(l_installed_instance_id, -1)) <> nvl(l_installed_instance_id, -1)) THEN
4122 IF p_disp_instance_id IS NOT NULL AND l_installed_instance_id IS NOT NULL AND p_disp_instance_id <> l_installed_instance_id THEN
4123 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_PRD_DIS_INV_INSTALL_INST'); -- Installed instance in part change is not the same as disposition instance
4124 FND_MESSAGE.SET_TOKEN('INSTALLED_INST', l_installed_instance_id);
4125 FND_MESSAGE.SET_TOKEN('DISP_INST', p_disp_instance_id);
4126 FND_MSG_PUB.ADD;
4127 END IF; -- Disposition Instance is not the same as the Installation Instance
4128 END IF; -- Part Change Found, and Part Change Type Check
4129 CLOSE part_change_csr;
4130 -- End Changes made by jaramana on August 07, 2007 for the bug 6328554 (FP of 5948917)
4131
4132 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4133 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4134 L_DEBUG_KEY || '.end',
4135 'At the end of the procedure ' );
4136 END IF;
4137 END validate_part_change;
4138
4139 ------------------------------------------------------------------
4140 -- Start of Comments --
4141 -- Procedure name : Calculate_Status
4142 -- Type : Private
4143 -- Function : Derives the status of the disposition
4144 -- Pre-reqs :
4145 -- Parameters :
4146 --
4147 -- Calculate_Status Parameters:
4148 -- p_disp_rec IN the final disposition record
4149 -- x_status_code OUT NOCOPY the output of the disposition record
4150 --
4151 -- End of Comments.
4152 PROCEDURE Calculate_Status (
4153 p_disposition_Rec IN AHL_PRD_DISPOSITION_PVT.disposition_rec_type,
4154 x_status_code OUT NOCOPY VARCHAR2)
4155 IS
4156 --
4157 CURSOR get_disposition_csr (p_disposition_id IN NUMBER) IS
4158 /*
4159 SELECT disp.status_code, disp.trackable_flag,
4160 pc.return_mtl_txn_id mtl_txn_id,
4161 pc.installed_part_change_id install_pc_id,
4162 AHL_PRD_DISP_MTL_TXN_PVT.calculate_txned_qty(disp.disposition_id) mtl_txn_qty
4163 FROM AHL_PRD_DISPOSITIONS_V disp, AHL_PART_CHANGES_V pc
4164 WHERE disp.disposition_id = p_disposition_id
4165 AND disp.part_change_id = pc.part_change_id (+);
4166 */
4167 -- AnRaj: Changed query, Perf Bug#4908609,Issue#5
4168 SELECT disp.status_code,
4169 decode(disp.instance_id, null, decode(disp.path_position_id, null, 'N', 'Y'), 'Y') trackable_flag ,
4170 pc.return_mtl_txn_id mtl_txn_id,
4171 pc.installed_part_change_id install_pc_id,
4172 pc.part_change_type,
4173 AHL_PRD_DISP_MTL_TXN_PVT.calculate_txned_qty(disp.disposition_id) mtl_txn_qty
4174 FROM AHL_PRD_DISPOSITIONS_B disp,
4175 AHL_PART_CHANGES_V pc
4176 WHERE disp.disposition_id = p_disposition_id
4177 AND disp.part_change_id = pc.part_change_id (+);
4178
4179 --
4180 CURSOR get_trackable_csr (p_inv_item_id IN NUMBER,
4181 p_inv_org_id IN NUMBER) IS
4182 SELECT NVL(MTL.comms_nl_trackable_flag, 'N') trackable_flag
4183 FROM MTL_SYSTEM_ITEMS_KFV MTL
4184 WHERE MTL.INVENTORY_ITEM_ID= p_inv_item_id
4185 AND MTL.organization_id =p_inv_org_id;
4186 --
4187 CURSOR get_part_change_type_csr (p_part_change_id IN NUMBER) IS
4188 SELECT part_change_type
4189 FROM AHL_PART_CHANGES
4190 WHERE part_change_id = p_part_change_id;
4191 --
4192 CURSOR get_pos_mandatory_csr(p_instance_id IN NUMBER) IS
4193 SELECT rel.position_necessity_code
4194 FROM AHL_MC_RELATIONSHIPS rel, CSI_II_RELATIONSHIPS CSI
4195 WHERE csi.subject_id = p_instance_id
4196 AND rel.relationship_id = TO_NUMBER(CSI.position_reference)
4197 AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
4198 AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate-1)) < TRUNC(sysdate)
4199 AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
4200
4201 --For fixing bug 4085156, added by Jerry on 12/27/2004
4202 CURSOR get_pos_mandatory(c_path_position_id NUMBER) IS
4203 SELECT B.position_necessity_code
4204 FROM ahl_mc_path_position_nodes A,
4205 ahl_mc_relationships B,
4206 ahl_mc_headers_b C
4207 WHERE A.path_position_id = c_path_position_id
4208 and A.sequence = (select max(D.sequence)
4209 from ahl_mc_path_position_nodes D
4210 group by D.path_position_id
4211 having D.path_position_id = c_path_position_id)
4212 and A.mc_id = C.mc_id
4213 and A.version_number = C.version_number
4214 and C.mc_header_id = B.mc_header_id
4215 and A.position_key = B.position_key;
4216
4217 --
4218 l_disp_dtl_rec get_disposition_csr%ROWTYPE;
4219 l_pos_mand_flag VARCHAR2(30);
4220 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || 'Calculate_Status';
4221
4222 --
4223 BEGIN
4224
4225 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4226 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4227 L_DEBUG_KEY || '.begin',
4228 'At the start of the procedure. p_disposition_rec.disposition_id = ' || p_disposition_rec.disposition_id);
4229 END IF;
4230
4231 --Fetch existing parameters.
4232 IF (p_disposition_rec.disposition_id IS NOT NULL) THEN
4233 OPEN get_disposition_csr(p_disposition_rec.disposition_id);
4234 FETCH get_disposition_csr into l_disp_dtl_rec;
4235 CLOSE get_disposition_csr;
4236 ELSE
4237 OPEN get_trackable_csr (p_disposition_rec.inventory_item_id,
4238 p_disposition_rec.item_org_id);
4239 FETCH get_trackable_csr into l_disp_dtl_rec.trackable_flag;
4240 CLOSE get_trackable_csr;
4241 END IF;
4242
4243 -- Added by jaramana on August 7, 2007
4244 IF (l_disp_dtl_rec.part_change_type IS NULL AND p_disposition_rec.part_change_id IS NOT NULL) THEN
4245 OPEN get_part_change_type_csr(p_disposition_rec.part_change_id);
4246 FETCH get_part_change_type_csr INTO l_disp_dtl_rec.part_change_type;
4247 CLOSE get_part_change_type_csr;
4248 END IF;
4249
4250 --determine if instance position is mandatory.
4251 IF (p_disposition_rec.instance_id IS NOT NULL) THEN
4252 OPEN get_pos_mandatory_csr (p_disposition_rec.instance_id);
4253 FETCH get_pos_mandatory_csr INTO l_pos_mand_flag;
4254 CLOSE get_pos_mandatory_csr;
4255 --For fixing bug 4085156, this ELSIF section was added by Jerry on 12/27/2004
4256 ELSIF (p_disposition_rec.path_position_id IS NOT NULL) THEN
4257 OPEN get_pos_mandatory(p_disposition_rec.path_position_id);
4258 FETCH get_pos_mandatory INTO l_pos_mand_flag;
4259 CLOSE get_pos_mandatory;
4260 END IF;
4261
4262 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4263 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_disposition_rec.immediate_disposition_code = ' || p_disposition_rec.immediate_disposition_code ||
4264 ', p_disposition_rec.instance_id = ' || p_disposition_rec.instance_id ||
4265 ', p_disposition_rec.part_change_id = ' || p_disposition_rec.part_change_id ||
4266 ', l_disp_dtl_rec.part_change_type = ' || l_disp_dtl_rec.part_change_type ||
4267 ', l_disp_dtl_rec.trackable_flag = ' || l_disp_dtl_rec.trackable_flag);
4268 END IF;
4269
4270 ----dbms_output.put_line( l_disp_dtl_rec.trackable_flag);
4271 --dbms_output.put_line( p_disposition_rec.quantity||'<>'||l_disp_dtl_rec.mtl_txn_qty);
4272
4273 --If terminated, can not change status.
4274 IF (p_disposition_rec.status_code = 'TERMINATED'
4275 OR l_disp_dtl_rec.status_code = 'TERMINATED') THEN
4276 x_status_code := 'TERMINATED';
4277
4278 --secondary disposition required
4279 ELSIF ((p_disposition_rec.immediate_disposition_code = 'BFS' OR
4280 p_disposition_rec.immediate_disposition_code = 'NON_CONF') AND
4281 p_disposition_rec.secondary_disposition_code IS NULL) THEN
4282 x_status_code := 'SECONDARY_REQD';
4283
4284 --Added by Jerry Li on 01/18/2005 for fixing bug 4095487 issue 1.
4285 ELSIF (p_disposition_rec.immediate_disposition_code IN ('NA','NOT_RECEIVED','NOT_REMOVED')
4286 AND p_disposition_rec.path_position_id IS NOT NULL
4287 AND l_disp_dtl_rec.trackable_flag = 'Y'
4288 AND p_disposition_rec.instance_id IS NOT NULL) THEN
4289 x_status_code := NULL;
4290
4291 -- Added by jaramana on August 7, 2007 to set the Dispositions created only for
4292 -- the sake of installation to status COMPLETE as soon as the installation is done.
4293 ELSIF (p_disposition_rec.immediate_disposition_code NOT IN ('NA','NOT_REMOVED')
4294 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 13-Dec-2007
4295 -- Check for path_position_id as well, so as to cater to disposition for empty position.
4296 AND (p_disposition_rec.instance_id IS NOT NULL OR p_disposition_rec.path_position_id IS NOT NULL)
4297 AND p_disposition_rec.part_change_id IS NOT NULL
4298 AND NVL(l_disp_dtl_rec.part_change_type, 'X') = 'I') THEN
4299 x_status_code := 'COMPLETE';
4300
4301 ----Added by Jerry Li on 01/18/2005 for fixing bug 4095487 issue 2a.
4302 ELSIF (p_disposition_rec.immediate_disposition_code = 'NOT_RECEIVED'
4303 AND p_disposition_rec.path_position_id IS NULL
4304 AND l_disp_dtl_rec.trackable_flag <> 'Y'
4305 AND p_disposition_rec.quantity > NVL(l_disp_dtl_rec.mtl_txn_qty, 0)) THEN
4306 x_status_code := NULL;
4307
4308 --Part Removal required, if path position is given and no part change has occurred
4309 ELSIF (p_disposition_rec.immediate_disposition_code NOT IN ('NA','NOT_RECEIVED','NOT_REMOVED')
4310 AND NVL(p_disposition_rec.secondary_disposition_code, 'NULL') <> 'REWORK_NR'
4311 AND p_disposition_rec.path_position_id IS NOT NULL
4312 AND p_disposition_rec.instance_id IS NOT NULL
4313 AND p_disposition_rec.part_change_id IS NULL) THEN
4314 x_status_code := 'PART_CHANGE_REQD';
4315
4316 -- Non-conformance request required. When tracked instance is in non-serviceable condition
4317 ELSIF (p_disposition_rec.immediate_disposition_code NOT IN ('NA','NOT_RECEIVED','NOT_REMOVED')
4318 AND l_disp_dtl_rec.trackable_flag = 'Y'
4319 AND (p_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE')
4320 OR p_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_MRB'))
4321 AND p_disposition_rec.primary_service_request_id IS NULL) THEN
4322 x_status_code := 'NON_CONF_REQD';
4323
4324 --Mtl_txn required
4325 ELSIF (p_disposition_rec.immediate_disposition_code NOT IN ('NA','NOT_REMOVED')
4326 AND nvl(p_disposition_rec.secondary_disposition_code,'NULL') <> 'HOLD'
4327 AND (( l_disp_dtl_rec.trackable_flag = 'Y'
4328 AND p_disposition_rec.part_change_id IS NOT NULL
4329 AND l_disp_dtl_rec.mtl_txn_id IS NULL)
4330 OR (l_disp_dtl_rec.trackable_flag = 'Y'
4331 AND p_disposition_rec.part_change_id IS NULL
4332 AND p_disposition_rec.quantity > NVL(l_disp_dtl_rec.mtl_txn_qty, 0))
4333 OR (l_disp_dtl_rec.trackable_flag <> 'Y'
4334 AND p_disposition_rec.quantity > NVL(l_disp_dtl_rec.mtl_txn_qty, 0)))) THEN
4335 x_status_code := 'MTL_TXN_REQD';
4336
4337 --Removal Complete
4338 ELSIF (p_disposition_rec.immediate_disposition_code NOT IN ('NA','NOT_RECEIVED','NOT_REMOVED')
4339 AND l_disp_dtl_rec.trackable_flag = 'Y'
4340 AND p_disposition_rec.path_position_id IS NOT NULL
4341 AND p_disposition_rec.part_change_id IS NOT NULL
4342 AND l_disp_dtl_rec.install_pc_id IS NULL) THEN
4343 x_status_code := 'REMOVAL_COMP';
4344 --QA collection ID
4345 ELSIF (p_disposition_rec.immediate_disposition_code NOT IN ('NA','NOT_RECEIVED','NOT_REMOVED')
4346 AND p_disposition_rec.instance_id IS NOT NULL
4347 AND p_disposition_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_MRB')
4348 AND p_disposition_rec.collection_id IS NULL)THEN
4349 x_status_code := 'PENDING_MRB';
4350
4351 --Install required
4352 ELSIF (--p_disposition_rec.instance_id IS NOT NULL and
4353 --For fixing bug 4085156, the above condition was removed by Jerry on 12/27/2004
4354 p_disposition_rec.path_position_id IS NOT NULL and
4355 l_pos_mand_flag = 'MANDATORY' AND
4356 l_disp_dtl_rec.install_pc_id IS NULL) THEN
4357 x_status_code := 'INSTALL_REQD';
4358
4359 --Complete status
4360 ELSIF (p_disposition_rec.immediate_disposition_code IN ('NA','NOT_REMOVED') OR
4361 (l_disp_dtl_rec.trackable_flag ='Y'--tracked
4362 AND ( p_disposition_rec.path_position_id IS NOT NULL --part change
4363 AND l_disp_dtl_rec.install_pc_id IS NOT NULL
4364 AND (l_disp_dtl_rec.mtl_txn_id IS NOT NULL
4365 OR p_disposition_rec.secondary_disposition_code = 'HOLD')
4366 OR (p_disposition_rec.path_position_id IS NULL --no part change
4367 AND NVL(l_disp_dtl_rec.mtl_txn_qty, 0) >= p_disposition_rec.quantity)))
4368 OR (l_disp_dtl_rec.trackable_flag <>'Y' -- non-tracked
4369 AND NVL(l_disp_dtl_rec.mtl_txn_qty, 0) >= p_disposition_rec.quantity
4370 AND get_issued_quantity(p_disposition_rec.disposition_id) >= p_disposition_rec.quantity --add to fix bug 4077106
4371 )) THEN
4372 x_status_code := 'COMPLETE';
4373
4374 ELSE
4375 x_status_code := null;
4376 END IF;
4377
4378 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4379 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4380 L_DEBUG_KEY || '.end',
4381 'At the end of the procedure, x_status_code = ' || x_status_code);
4382 END IF;
4383
4384 END Calculate_Status;
4385
4386
4387 ------------------------------------------------------------------
4388 -- Function name : Validate_Disposition_Types
4389 -- Type : Private
4390 -- Function : Validate the disposition type of the disposition record
4391 -- Pre-reqs :
4392 -- Parameters :
4393 --
4394 -- Validate_Disposition_Types parameters:
4395 -- p_disp_rec IN the final disposition record
4396 -- End of Comments.
4397
4398 PROCEDURE Validate_Disposition_Types (
4399 x_return_status OUT NOCOPY VARCHAR2,
4400 x_msg_count OUT NOCOPY NUMBER,
4401 x_msg_data OUT NOCOPY VARCHAR2,
4402 p_disposition_rec IN AHL_PRD_DISPOSITION_PVT.disposition_rec_type)
4403 IS
4404 --
4405 --
4406 CURSOR check_immed_disp_types_csr (p_disp_code IN VARCHAR2)IS
4407 SELECT 'X'
4408 FROM fnd_lookups
4409 WHERE lookup_type = 'AHL_IMMED_DISP_TYPE'
4410 AND lookup_code = p_disp_code
4411 AND lookup_code <> 'NULL';
4412 --
4413 CURSOR check_second_disp_types_csr (p_disp_code IN VARCHAR2)IS
4414 SELECT 'X'
4415 FROM fnd_lookups
4416 WHERE lookup_type = 'AHL_SECND_DISP_TYPE'
4417 AND lookup_code = p_disp_code
4418 AND lookup_code <> 'NULL';
4419 --
4420 CURSOR check_condition_csr (p_condition_id IN NUMBER) IS
4421 SELECT 'X'
4422 FROM mtl_material_statuses
4423 WHERE status_id = p_condition_id
4424 AND enabled_flag = 1;
4425 --
4426 CURSOR get_disp_rec_csr (p_disp_id IN NUMBER) IS
4427 /*
4428 SELECT *
4429 FROM AHL_PRD_DISPOSITIONS_V
4430 WHERE DISPOSITION_ID = p_disp_id;
4431 */
4432 --AnRaj: Changed query, Perf Bug#4908609,Issue#3
4433 select B.condition_id,
4434 COND.STATUS_CODE CONDITION_CODE,
4435 B.immediate_disposition_code,
4436 FND1.MEANING IMMEDIATE_TYPE,
4437 B.secondary_disposition_code,
4438 FND2.MEANING SECONDARY_TYPE,
4439 B.part_change_id,
4440 decode(B.instance_id, null, decode(B.path_position_id, null, 'N', 'Y'), 'Y') TRACKABLE_FLAG
4441 from AHL_PRD_DISPOSITIONS_B B,
4442 FND_LOOKUPS FND1,
4443 FND_LOOKUPS FND2,
4444 MTL_MATERIAL_STATUSES_VL COND
4445 where FND1.LOOKUP_TYPE (+) = 'AHL_IMMED_DISP_TYPE'
4446 AND B.immediate_disposition_code = FND1.LOOKUP_CODE (+)
4447 AND FND2.LOOKUP_TYPE (+) = 'AHL_SECND_DISP_TYPE'
4448 AND B.SECONDARY_DISPOSITION_CODE = FND2.LOOKUP_CODE (+)
4449 AND B.condition_id = COND.status_id (+)
4450 AND B.disposition_id = p_disp_id;
4451
4452 --
4453 l_dummy VARCHAR2(1);
4454 --l_api_version CONSTANT NUMBER := 1.0;
4455 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Disp_Types';
4456 l_imm_disp_type_tbl AHL_PRD_DISP_UTIL_PVT.Disp_Type_Tbl_Type;
4457 l_sec_disp_type_tbl AHL_PRD_DISP_UTIL_PVT.Disp_Type_Tbl_Type;
4458 l_disp_rec Get_Disp_Rec_Csr%ROWTYPE;
4459 l_match_flag boolean;
4460 l_trackable_flag VARCHAR2(1);
4461 --
4462 BEGIN
4463 -- Standard start of API savepoint
4464 SAVEPOINT Validate_Disp_Types_Pvt;
4465
4466 -- Initialize Procedure return status to success
4467 x_return_status := FND_API.G_RET_STS_SUCCESS;
4468
4469 --Validate that the immediate disposition type is valid
4470 OPEN check_immed_disp_types_csr (p_disposition_rec.immediate_disposition_code);
4471 FETCH check_immed_disp_types_csr into l_dummy;
4472 IF (check_immed_disp_types_csr%NOTFOUND) THEN
4473 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_IMMED_TYPE_INV');
4474 FND_MSG_PUB.ADD;
4475 END IF;
4476 CLOSE check_immed_disp_types_csr;
4477
4478 --Validate that the secondary disposition type is valid
4479 IF (p_disposition_rec.secondary_disposition_code IS NOT NULL) THEN
4480 OPEN check_second_disp_types_csr (p_disposition_rec.secondary_disposition_code);
4481 FETCH check_second_disp_types_csr into l_dummy;
4482 IF (check_second_disp_types_csr%NOTFOUND) THEN
4483 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_SECND_TYPE_INV');
4484 FND_MSG_PUB.ADD;
4485 END IF;
4486 CLOSE check_second_disp_types_csr;
4487 END IF;
4488
4489 OPEN check_condition_csr (p_disposition_rec.condition_id);
4490 FETCH check_condition_csr into l_dummy;
4491 IF (check_condition_csr%NOTFOUND) THEN
4492 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_CONDITION_INV');
4493 FND_MSG_PUB.ADD;
4494 END IF;
4495 CLOSE check_condition_csr;
4496
4497
4498
4499 --Check that secondary disposition is null for all immediate except BFS,
4500 -- NonConf dispositions
4501 IF (p_disposition_rec.immediate_disposition_code = 'BFS' OR
4502 p_disposition_rec.immediate_disposition_code ='NON_CONF') THEN
4503
4504 --If RTV/RTC/HOLD/REWORK_RR, condition has to be unserviceable or MRB
4505 IF (p_disposition_rec.secondary_disposition_code <> 'REWORK_NR' AND
4506 p_disposition_rec.secondary_disposition_code <> 'SCRAP' AND
4507 p_disposition_rec.condition_id <> fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE') AND
4508 p_disposition_rec.condition_id <> fnd_profile.value('AHL_MTL_MAT_STATUS_MRB')) THEN
4509 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_TYPE_COND_INV');
4510 FND_MSG_PUB.ADD;
4511 --If SCRAP, condition has to be MRB
4512 ELSIF (p_disposition_rec.secondary_disposition_code = 'SCRAP' AND
4513 p_disposition_rec.condition_id <> fnd_profile.value('AHL_MTL_MAT_STATUS_MRB')) THEN
4514 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_TYPE_COND_INV');
4515 FND_MSG_PUB.ADD;
4516 END IF;
4517
4518 ELSE
4519 --Use_as_is means condition must be serviceable
4520 IF (p_disposition_rec.immediate_disposition_code = 'USE_AS_IS' AND
4521 (p_disposition_rec.condition_id=fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE') OR
4522 p_disposition_rec.condition_id=fnd_profile.value('AHL_MTL_MAT_STATUS_MRB'))) THEN
4523 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_TYPE_COND_INV');
4524 FND_MSG_PUB.ADD;
4525 END IF;
4526 --SCRAP means condition is MRB
4527 IF (p_disposition_rec.immediate_disposition_code = 'SCRAP' AND
4528 p_disposition_rec.condition_id <> fnd_profile.value('AHL_MTL_MAT_STATUS_MRB')) THEN
4529 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_TYPE_COND_INV');
4530 FND_MSG_PUB.ADD;
4531 END IF;
4532 --not BFS or NON_CONF, secondary must be null
4533 IF(p_disposition_rec.secondary_disposition_code IS NOT NULL) THEN
4534 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_SECND_TYPE_INV');
4535 FND_MSG_PUB.ADD;
4536 END IF;
4537 END IF;
4538
4539 --Jerry added the following validation on 02/17/2005 for fixing bug 4189553
4540 --To determine whether the disposition is for tracked item or non-tracked item
4541 IF p_disposition_rec.path_position_id IS NOT NULL THEN
4542 l_trackable_flag := 'Y';
4543 ELSIF p_disposition_rec.inventory_item_id IS NOT NULL THEN
4544 SELECT nvl(comms_nl_trackable_flag, 'N') INTO l_trackable_flag
4545 FROM mtl_system_items_kfv
4546 WHERE inventory_item_id = p_disposition_rec.inventory_item_id
4547 AND organization_id = p_disposition_rec.item_org_id;
4548 ELSIF p_disposition_rec.item_group_id IS NOT NULL THEN
4549 SELECT decode(type_code, 'TRACKED', 'Y', 'N') INTO l_trackable_flag
4550 FROM ahl_item_groups_b
4551 WHERE item_group_id = p_disposition_rec.item_group_id;
4552 ELSE
4553 l_trackable_flag := 'N';
4554 END IF;
4555
4556 IF (p_disposition_rec.secondary_disposition_code IN ('REWORK_RR', 'REWORK_NR') AND
4557 l_trackable_flag = 'N') THEN
4558 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_SECND_TYPE_INV');
4559 FND_MSG_PUB.ADD;
4560 END IF;
4561
4562 --Validate against existing state in update case.
4563 IF (p_disposition_rec.operation_flag = G_OP_UPDATE AND
4564 p_disposition_rec.disposition_id IS NOT NULL) THEN
4565
4566 --2a) Fetch the existing state.
4567 OPEN get_disp_rec_csr (p_disposition_rec.disposition_id);
4568 FETCH get_disp_rec_csr INTO l_disp_rec;
4569 IF (get_disp_rec_csr%NOTFOUND) THEN
4570 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_ID_INV');
4571 FND_MESSAGE.Set_Token('DISPOSITION_ID', p_disposition_rec.disposition_id);
4572 FND_MSG_PUB.ADD;
4573 RAISE FND_API.G_EXC_ERROR;
4574 END IF;
4575 CLOSE get_disp_rec_csr;
4576
4577 --Validate that condition id flows only 1 way.
4578 --'MRB' must stay as MRB
4579 IF (l_disp_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_MRB')
4580 AND p_disposition_rec.condition_id <> fnd_profile.value('AHL_MTL_MAT_STATUS_MRB')) THEN
4581 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_COND_CHANGE_INV');
4582 FND_MESSAGE.Set_Token('OLD_CONDITION', l_disp_rec.condition_code);
4583 FND_MESSAGE.Set_Token('NEW_CONDITION', p_disposition_rec.condition_meaning);
4584 FND_MSG_PUB.ADD;
4585 --Unserviceable cannot become serviceable
4586 ELSIF (l_disp_rec.condition_id = fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE') AND
4587 p_disposition_rec.condition_id<>fnd_profile.value('AHL_MTL_MAT_STATUS_UNSERVICABLE') AND
4588 p_disposition_rec.condition_id<>fnd_profile.value('AHL_MTL_MAT_STATUS_MRB')) THEN
4589 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_COND_CHANGE_INV');
4590 FND_MESSAGE.Set_Token('OLD_CONDITION', l_disp_rec.condition_code);
4591 FND_MESSAGE.Set_Token('NEW_CONDITION', p_disposition_rec.condition_meaning);
4592 FND_MSG_PUB.ADD;
4593 END IF;
4594
4595 --Validate that Disposition Types are defined correctly
4596 --1) Check that Not Removed and NA are not mapped to Not Received
4597 IF (l_disp_rec.immediate_disposition_code IS NOT NULL) THEN
4598 IF ( l_disp_rec.immediate_disposition_code in ('NOT_REMOVED','NA')
4599 AND p_disposition_rec.immediate_disposition_code = 'NOT_RECEIVED') THEN
4600 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_IMMED_CHANGE_ILL');
4601 FND_MESSAGE.Set_Token('OLD_DISP_TYPE', l_disp_rec.immediate_type);
4602 FND_MESSAGE.Set_Token('NEW_DISP_TYPE', p_disposition_rec.immediate_disposition);
4603 FND_MSG_PUB.ADD;
4604 END IF;
4605
4606 --2) Check that Not Received, BFS, Non_Conf, SCRAP are not changed
4607 --IF ( l_disp_rec.immediate_disposition_code in ('NOT_RECEIVED','BFS','NON_CONF','SCRAP')
4608 --Jerry removed 'NOT_RECEIVED' on 01/17/2005 for fixing bug 4094927
4609 IF ( l_disp_rec.immediate_disposition_code in ('BFS','NON_CONF','SCRAP')
4610 AND p_disposition_rec.immediate_disposition_code <> l_disp_rec.immediate_disposition_code) THEN
4611 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_IMMED_CHANGE_ILL');
4612 FND_MESSAGE.Set_Token('OLD_DISP_TYPE', l_disp_rec.immediate_type);
4613 FND_MESSAGE.Set_Token('NEW_DISP_TYPE', p_disposition_rec.immediate_disposition);
4614 FND_MSG_PUB.ADD;
4615 END IF;
4616
4617 --3) Check that USE_AS_IS, RTV, RTC can not change to NOT_REMOVED,NA, NOT_RECEIVED
4618 IF ( l_disp_rec.immediate_disposition_code in ('USE_AS_IS','RTV','RTC')
4619 AND p_disposition_rec.immediate_disposition_code in ('NOT_REMOVED','NA','NOT_RECEIVED')) THEN
4620 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_IMMED_CHANGE_ILL');
4621 FND_MESSAGE.Set_Token('OLD_DISP_TYPE', l_disp_rec.immediate_type);
4622 FND_MESSAGE.Set_Token('NEW_DISP_TYPE', p_disposition_rec.immediate_disposition);
4623 FND_MSG_PUB.ADD;
4624 END IF;
4625 END IF;
4626
4627 --Validate secondary dispositions
4628 --4) Check if secondary is SCRAP then must stay SCRAP
4629 IF ( l_disp_rec.secondary_disposition_code = 'SCRAP'
4630 AND p_disposition_rec.secondary_disposition_code <> 'SCRAP') THEN
4631 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_SECND_CHANGE_ILL');
4632 FND_MESSAGE.Set_Token('OLD_DISP_TYPE', l_disp_rec.secondary_type);
4633 FND_MESSAGE.Set_Token('NEW_DISP_TYPE', p_disposition_rec.secondary_disposition);
4634 FND_MSG_PUB.ADD;
4635 END IF;
4636
4637
4638 --5) Check REWORK_NR means no part change has or is taking place.
4639 IF ( p_disposition_rec.secondary_disposition_code = 'REWORK_NR'
4640 and (l_disp_rec.part_change_id IS NOT NULL
4641 or p_disposition_rec.part_change_id IS NOT NULL)) THEN
4642 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_SECND_CHANGE_ILL');
4643 FND_MESSAGE.Set_Token('OLD_DISP_TYPE', l_disp_rec.secondary_type);
4644 FND_MESSAGE.Set_Token('NEW_DISP_TYPE', p_disposition_rec.secondary_disposition);
4645 FND_MSG_PUB.ADD;
4646 END IF;
4647
4648 -- Jerry added the following validation on 02/17/2005 for fixing bug 4189553
4649 IF ( p_disposition_rec.secondary_disposition_code IN ('REWORK_NR','REWORK_RR')
4650 AND l_disp_rec.trackable_flag = 'N') THEN
4651 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DISP_SECND_CHANGE_ILL');
4652 FND_MESSAGE.Set_Token('OLD_DISP_TYPE', l_disp_rec.secondary_type);
4653 FND_MESSAGE.Set_Token('NEW_DISP_TYPE', p_disposition_rec.secondary_disposition);
4654 FND_MSG_PUB.ADD;
4655 END IF;
4656
4657 END IF; --G_UPDATE
4658
4659 IF FND_MSG_PUB.count_msg > 0 THEN
4660 RAISE FND_API.G_EXC_ERROR;
4661 END IF;
4662
4663 EXCEPTION
4664 WHEN FND_API.G_EXC_ERROR THEN
4665 Rollback to Validate_Disp_Types_Pvt;
4666 x_return_status := FND_API.G_RET_STS_ERROR;
4667 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4668 p_data => x_msg_data,
4669 p_encoded => fnd_api.g_false);
4670 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4671 Rollback to Validate_Disp_Types_Pvt;
4672 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4673 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4674 p_data => x_msg_data,
4675 p_encoded => fnd_api.g_false);
4676 WHEN OTHERS THEN
4677 Rollback to Validate_Disp_Types_Pvt;
4678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4679 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
4680 p_procedure_name => l_api_name,
4681 p_error_text => SQLERRM);
4682 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4683 p_data => x_msg_data,
4684 p_encoded => fnd_api.g_false);
4685
4686 END Validate_Disposition_Types;
4687
4688 -------------------------------------------------------------------------
4689
4690
4691
4692 FUNCTION workorder_Editable(p_workorder_id IN NUMBER) RETURN BOOLEAN
4693 IS
4694
4695 CURSOR workorder_status_csr(p_workorder_id IN NUMBER) IS
4696 SELECT status_code from ahl_workorders where workorder_id = p_workorder_id;
4697
4698 l_status_code VARCHAR(30);
4699 BEGIN
4700 OPEN workorder_status_csr(p_workorder_id);
4701 FETCH workorder_status_csr INTO l_status_code;
4702 CLOSE workorder_status_csr;
4703 -- Change made by jaramana on August 8, 2007 for bug 6326065 (FP of 6061600)
4704 -- Need to allow Disp Txn association for Complete workorders (code 4)
4705 -- update_disposition is called by AHL_PRD_DISP_MTL_TXN_PVT.Process_Disp_Mtl_Txn
4706 -- when the Transaction Qty is updated. So allow the Complete status as updateable.
4707 -- But note that from the Disposition UI, it will still not be possible to create or
4708 -- update the Disposition when the work order is in status Complete
4709 -- IF (l_status_code IN ('12', '4', '5', '7'))THEN --CLOSED, COMPLETE, COMPLETE NO CHARGE, CANCELLED
4710 IF (l_status_code IN ('12', '5', '7')) THEN --CLOSED, COMPLETE NO CHARGE, CANCELLED
4711 RETURN FALSE;
4712 END IF;
4713
4714 RETURN TRUE;
4715 END workorder_editable;
4716
4717 ----------------function get_unit_instance_id-------------------------
4718 -- retrieve the instance id of the unit for the job.
4719 ----------------------------------------------------------------------
4720 FUNCTION get_unit_instance_id(p_workorder_id IN NUMBER) RETURN NUMBER
4721 IS
4722 CURSOR task_instance_csr IS
4723 SELECT VTS.INSTANCE_ID, VTS.VISIT_ID
4724 FROM AHL_VISIT_TASKS_B VTS, AHL_WORKORDERS WO
4725 WHERE WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID AND
4726 WO.WORKORDER_ID = p_workorder_id;
4727
4728 CURSOR visit_instance_csr (c_visit_id IN NUMBER) IS
4729 SELECT VST.ITEM_INSTANCE_ID
4730 FROM AHL_VISITS_B VST
4731 WHERE VST.VISIT_ID = c_visit_id;
4732
4733 CURSOR uc_header_instance_csr(p_uc_header_id IN NUMBER) IS
4734 SELECT csi_item_instance_id FROM ahl_unit_config_headers
4735 WHERE unit_config_header_id = p_uc_header_id;
4736
4737 l_task_instance_id NUMBER;
4738 l_visit_id NUMBER;
4739 l_visit_instance_id NUMBER;
4740 l_wo_instance_id NUMBER;
4741 l_uc_header_id NUMBER;
4742 l_unit_instance_id NUMBER;
4743
4744 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || 'get_unit_instance_id';
4745
4746
4747 BEGIN
4748 OPEN task_instance_csr;
4749 FETCH task_instance_csr into l_task_instance_id, l_visit_id;
4750 CLOSE task_instance_csr;
4751
4752 OPEN visit_instance_csr(l_visit_id);
4753 FETCH visit_instance_csr into l_visit_instance_id;
4754 CLOSE visit_instance_csr;
4755
4756 IF l_task_instance_id IS NULL THEN
4757 l_wo_instance_id := l_visit_instance_id;
4758 ELSE
4759 l_wo_instance_id := l_task_instance_id;
4760 END IF;
4761
4762 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4763 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'For work order id ' || p_workorder_id || ', wo_instance_id = ' || l_wo_instance_id);
4764 END IF;
4765
4766 -- Get the top most unit containing the wo instance
4767 l_uc_header_id := AHL_UTIL_UC_PKG.get_uc_header_id(l_wo_instance_id);
4768 IF (l_uc_header_id IS NULL) THEN
4769 -- The task instance may have been removed from the Unit already.
4770 -- So, try to get the UC from the visit instance
4771 IF (l_task_instance_id IS NOT NULL AND l_visit_instance_id IS NOT NULL) THEN
4772 -- WO instance is the task instance. So try with the visit instance
4773 l_uc_header_id := AHL_UTIL_UC_PKG.get_uc_header_id(l_visit_instance_id);
4774 ELSE
4775 -- WO instance is already the visit instance
4776 NULL;
4777 END IF;
4778 END IF;
4779
4780 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4781 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'For work order id ' || p_workorder_id || ', uc_header_id = ' || l_uc_header_id);
4782 END IF;
4783
4784 IF (l_uc_header_id IS NOT NULL) THEN
4785 OPEN uc_header_instance_csr(l_uc_header_id);
4786 FETCH uc_header_instance_csr INTO l_unit_instance_id;
4787 CLOSE uc_header_instance_csr;
4788 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4789 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'For work order id ' || p_workorder_id || ', unit_instance_id = ' || l_unit_instance_id);
4790 END IF;
4791 RETURN l_unit_instance_id;
4792 ELSE
4793 -- IB Tree
4794 RETURN NULL;
4795 END IF;
4796
4797 END get_unit_instance_id;
4798
4799 --------------------------------------------------------------------
4800
4801 --Retrieve root instance Id of an instance
4802 FUNCTION get_root_instance_id(p_instance_id IN NUMBER) RETURN NUMBER
4803 IS
4804
4805 CURSOR get_root_instance_csr(p_instance_id IN NUMBER) IS
4806 SELECT object_id
4807 FROM csi_ii_relationships
4808 START WITH subject_id = p_instance_id
4809 AND relationship_type_code = 'COMPONENT-OF'
4810 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
4811 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
4812 CONNECT BY subject_id = PRIOR object_id
4813 AND relationship_type_code = 'COMPONENT-OF'
4814 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
4815 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
4816
4817 CURSOR is_non_root_csr(p_instance_id NUMBER) IS
4818 SELECT 'x' FROM csi_ii_relationships
4819 WHERE subject_id = p_instance_id
4820 AND relationship_type_code = 'COMPONENT-OF'
4821 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
4822 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
4823
4824 l_exist VARCHAR2(1);
4825 l_root_instance_id NUMBER;
4826 BEGIN
4827 OPEN is_non_root_csr(p_instance_id);
4828 FETCH is_non_root_csr INTO l_exist;
4829 IF is_non_root_csr%NOTFOUND THEN -- then it is a root instance
4830 l_root_instance_id := p_instance_id;
4831 ELSE
4832 OPEN get_root_instance_csr(p_instance_id);
4833 LOOP
4834 FETCH get_root_instance_csr INTO l_root_instance_id;
4835 EXIT when get_root_instance_csr%NOTFOUND;
4836 END LOOP;
4837 CLOSE get_root_instance_csr;
4838 END IF;
4839 CLOSE is_non_root_csr;
4840
4841 RETURN l_root_instance_id;
4842
4843 END get_root_instance_id;
4844 --------------------------------------------------------------------
4845
4846 FUNCTION get_issued_quantity(p_disposition_id IN NUMBER) RETURN NUMBER
4847 IS
4848 Cursor get_issued_quantity_csr(p_disp_id IN NUMBER) IS
4849 SELECT sum (assoc.quantity)
4850 FROM AHL_PRD_DISP_MTL_TXNS assoc,
4851 AHL_WORKORDER_MTL_TXNS mtxn
4852 WHERE assoc.disposition_id = p_disp_id
4853 AND assoc.workorder_mtl_txn_id = mtxn.workorder_mtl_txn_id
4854 AND mtxn.transaction_type_id = WIP_CONSTANTS.ISSCOMP_TYPE
4855 GROUP BY assoc.disposition_id;
4856
4857 l_quantity NUMBER;
4858 BEGIN
4859 OPEN get_issued_quantity_csr(p_disposition_id);
4860 FETCH get_issued_quantity_csr INTO l_quantity;
4861 IF(get_issued_quantity_csr%NOTFOUND) THEN
4862 l_quantity := 0;
4863 END IF;
4864 CLOSE get_issued_quantity_csr;
4865
4866 RETURN l_quantity;
4867
4868 END get_issued_quantity;
4869
4870 ------------------------------------------------------------------------
4871 -- Added function by rbhavsar on 09/27/2007 for Bug 6411059
4872 FUNCTION root_node_in_uc_headers(p_instance_id IN NUMBER) RETURN BOOLEAN
4873 IS
4874 CURSOR get_root_node(c_instance_id NUMBER) IS
4875 SELECT object_id
4876 FROM csi_ii_relationships
4877 START WITH subject_id = c_instance_id
4878 AND relationship_type_code = 'COMPONENT-OF'
4879 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
4880 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
4881
4882 CONNECT BY subject_id = prior object_id
4883 AND relationship_type_code = 'COMPONENT-OF'
4884 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
4885 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
4886
4887 CURSOR check_in_headers(c_instance_id NUMBER) IS
4888 SELECT csi_item_instance_id
4889 FROM ahl_unit_config_headers
4890 WHERE csi_item_instance_id = c_instance_id
4891 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
4892
4893 TYPE objectid_tbl IS TABLE OF csi_ii_relationships.object_id%TYPE;
4894 l_object_id_tbl objectid_tbl;
4895 l_instance_id NUMBER;
4896 l_unit_instance_id NUMBER;
4897 l_api_name CONSTANT VARCHAR2(30) := 'root_node_in_uc_headers';
4898
4899 BEGIN
4900 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4901 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4902 G_LOG_PREFIX||l_api_name||'.begin',
4903 'At the start of the function: instance_id ='||p_instance_id);
4904 END IF;
4905
4906 -- Get the root node instance for the given instance id
4907 OPEN get_root_node(p_instance_id);
4908 FETCH get_root_node BULK COLLECT INTO l_object_id_tbl;
4909 CLOSE get_root_node;
4910
4911 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4912 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4913 G_LOG_PREFIX||l_api_name,
4914 'Parent hierachy count ' || l_object_id_tbl.count);
4915 END IF;
4916
4917 IF (l_object_id_tbl.count > 0) then
4918 l_instance_id := l_object_id_tbl(l_object_id_tbl.count);
4919 ELSE
4920 l_instance_id := p_instance_id;
4921 END IF;
4922
4923 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4924 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4925 G_LOG_PREFIX||l_api_name,
4926 'Root Instance is ' || l_instance_id);
4927 END IF;
4928
4929 -- Check if the Root instance is in unit config headers
4930 OPEN check_in_headers(l_instance_id);
4931 FETCH check_in_headers INTO l_unit_instance_id;
4932 IF check_in_headers%NOTFOUND THEN
4933 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4934 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4935 G_LOG_PREFIX||l_api_name,
4936 'Root Instance ' || l_instance_id || ' is not in unit config headers ');
4937 END IF;
4938 CLOSE check_in_headers;
4939 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4940 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4941 G_LOG_PREFIX||l_api_name||'.end',
4942 'At the end of the procedure returning FALSE');
4943 END IF;
4944 RETURN FALSE;
4945 ELSE
4946 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4947 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4948 G_LOG_PREFIX||l_api_name,
4949 'Root Instance ' || l_instance_id || ' is in unit config headers ');
4950 END IF;
4951 CLOSE check_in_headers;
4952 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4953 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4954 G_LOG_PREFIX||l_api_name||'.end',
4955 'At the end of the procedure returning TRUE');
4956 END IF;
4957 RETURN TRUE;
4958 END IF;
4959
4960 END root_node_in_uc_headers;
4961
4962 --------------------------------------------------------------
4963
4964 -- Procedure added by jaramana on October 8, 2007 for ER 5854667
4965 -- This procedure gets the WIP Entity Id of the Non-Master Work that is a child of the passed
4966 -- in Work order (Summary WO of the Non Routine)
4967
4968 FUNCTION Get_NonMWO_WIP_Entity_Id(p_workorder_id IN NUMBER) RETURN NUMBER
4969 IS
4970 Cursor get_child_entities IS
4971 SELECT CHILD_OBJECT_ID
4972 FROM EAM_WO_RELATIONSHIPS
4973 WHERE PARENT_OBJECT_ID = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = p_workorder_id)
4974 AND PARENT_RELATIONSHIP_TYPE = 1
4975 ORDER BY CHILD_OBJECT_ID;
4976
4977 Cursor get_matching_wo_dtls(c_wip_entity_id IN NUMBER) IS
4978 SELECT WO.WORKORDER_ID, WO.WIP_ENTITY_ID, WO.VISIT_TASK_ID
4979 FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK
4980 WHERE WIP_ENTITY_ID = c_wip_entity_id AND
4981 WO.VISIT_TASK_ID = TSK.VISIT_TASK_ID AND
4982 TSK.TASK_TYPE_CODE <> 'SUMMARY';
4983
4984 Cursor get_non_summary_entity(c_wip_entity_id IN NUMBER) IS
4985 SELECT EAM.CHILD_OBJECT_ID
4986 FROM EAM_WO_RELATIONSHIPS EAM, AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK
4987 WHERE EAM.CHILD_OBJECT_ID = WO.WIP_ENTITY_ID AND
4988 TSK.VISIT_TASK_ID = WO.VISIT_TASK_ID AND
4989 TSK.TASK_TYPE_CODE <> 'SUMMARY'
4990 START WITH EAM.CHILD_OBJECT_ID = c_wip_entity_id
4991 CONNECT BY PRIOR CHILD_OBJECT_ID = PARENT_OBJECT_ID AND
4992 PARENT_RELATIONSHIP_TYPE = 1
4993 ORDER BY LEVEL, EAM.CHILD_OBJECT_ID;
4994
4995 l_child_entity_id NUMBER;
4996 l_first_child_id NUMBER := null;
4997 l_wo_dtls_rec get_matching_wo_dtls%ROWTYPE;
4998 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||'Get_NonMWO_WIP_Entity_Id';
4999
5000 BEGIN
5001 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5002 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_full_name, 'Entering Procedure. p_workorder_id = ' || p_workorder_id);
5003 END IF;
5004 OPEN get_child_entities;
5005 LOOP
5006 FETCH get_child_entities INTO l_child_entity_id;
5007 EXIT when get_child_entities%NOTFOUND;
5008 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5009 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'l_child_entity_id = ' || l_child_entity_id);
5010 END IF;
5011 OPEN get_matching_wo_dtls(l_child_entity_id);
5012 FETCH get_matching_wo_dtls INTO l_wo_dtls_rec;
5013 CLOSE get_matching_wo_dtls;
5014 IF (l_wo_dtls_rec.WIP_ENTITY_ID IS NOT NULL) THEN
5015 CLOSE get_child_entities;
5016 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5017 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'About to return ' || l_wo_dtls_rec.WIP_ENTITY_ID);
5018 END IF;
5019 RETURN l_wo_dtls_rec.WIP_ENTITY_ID;
5020 END IF;
5021 IF (l_first_child_id IS NULL) THEN
5022 l_first_child_id := l_child_entity_id;
5023 END IF;
5024 END LOOP;
5025 CLOSE get_child_entities;
5026 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5027 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'l_first_child_id = ' || l_first_child_id);
5028 END IF;
5029 IF (l_first_child_id IS NOT NULL) THEN
5030 OPEN get_non_summary_entity(l_first_child_id);
5031 FETCH get_non_summary_entity INTO l_child_entity_id;
5032 CLOSE get_non_summary_entity;
5033 END IF;
5034 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5035 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_full_name, 'Exiting Procedure. About to return ' || l_child_entity_id);
5036 END IF;
5037 RETURN l_child_entity_id;
5038
5039 END Get_NonMWO_WIP_Entity_Id;
5040
5041 --------------------------------------------------------------
5042
5043 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 12-Dec-2007
5044 -- The API update_item_location and its use has been commented out. Its functionality will
5045 -- now be handled in the API AHL_PRD_NONROUTINE_PVT.process_nonroutine_job.
5046 /*
5047 -- Procedure added by jaramana on February 14, 2007 for ER 5854667
5048 -- This procedure sets the instannce's location as the WIP Job passed in as parameter.
5049 PROCEDURE update_item_location(p_workorder_id IN NUMBER,
5050 p_instance_id IN NUMBER,
5051 x_return_status OUT NOCOPY Varchar2)
5052 IS
5053
5054 l_wip_entity_id NUMBER;
5055 l_instance_rec csi_datastructures_pub.instance_rec;
5056 l_csi_transaction_rec CSI_DATASTRUCTURES_PUB.transaction_rec;
5057 l_extend_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
5058 l_party_tbl csi_datastructures_pub.party_tbl;
5059 l_account_tbl csi_datastructures_pub.party_account_tbl;
5060 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
5061 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
5062 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
5063 l_instance_id_lst csi_datastructures_pub.id_tbl;
5064 l_msg_count NUMBER;
5065 l_msg_data VARCHAR2(2000);
5066
5067 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||'Update_item_location';
5068 l_transaction_type_id NUMBER;
5069 l_return_val BOOLEAN;
5070 l_wo_status_code AHL_WORKORDERS.STATUS_CODE%TYPE;
5071 l_temp_wo_id NUMBER;
5072
5073 -- For getting the status of the workorder from the wip_entity_id
5074 CURSOR ahl_wo_status_csr(c_wip_entity_id IN NUMBER) IS
5075 select workorder_id, status_code
5076 FROM ahl_workorders
5077 WHERE wip_entity_id = c_wip_entity_id;
5078
5079 -- For getting the the updated object_version number from csi_item_isntances
5080 CURSOR ahl_obj_ver_csr IS
5081 select object_version_number
5082 from csi_item_instances
5083 where instance_id = p_instance_id;
5084
5085 -- For getting the wip_location_id to populate csi_transaction record
5086 CURSOR ahl_wip_location_csr IS
5087 select wip_location_id
5088 from csi_install_parameters ;
5089
5090 BEGIN
5091
5092 --Initialize API return status to success
5093 x_return_status := FND_API.G_RET_STS_SUCCESS;
5094
5095 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5096 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_full_name, 'Entering Procedure. p_workorder_id = ' || p_workorder_id || ', p_instance_id = ' || p_instance_id);
5097 END IF;
5098
5099 -- Get the Non-Master Workorder's wip_entity_id
5100 l_wip_entity_id := Get_NonMWO_WIP_Entity_Id(p_workorder_id);
5101 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5102 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Get_NonMWO_WIP_Entity_Id Returned ' || l_wip_entity_id);
5103 END IF;
5104 IF (l_wip_entity_id IS NULL) THEN
5105 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5106 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Throwing Unexpected Error since Get_NonMWO_WIP_Entity_Id returned null');
5107 END IF;
5108 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_WIP_ENTITY_MISSING');
5109 FND_MESSAGE.Set_Token('WOID', p_workorder_id);
5110 FND_MSG_PUB.ADD;
5111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5112 ELSE
5113 -- Additional check added by jaramana on February 23, 2007 for ER 5854667
5114 -- Update the instance location only if the work order is in released status
5115 OPEN ahl_wo_status_csr(l_wip_entity_id);
5116 FETCH ahl_wo_status_csr INTO l_temp_wo_id, l_wo_status_code;
5117 CLOSE ahl_wo_status_csr;
5118 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5119 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Status of Work order with id ' || l_temp_wo_id || ' is ' || l_wo_status_code);
5120 END IF;
5121 IF (l_wo_status_code <> G_WO_RELEASED_STATUS) THEN
5122 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5123 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Not changing the location of the instance to the NR WO because the work order is not released.');
5124 END IF;
5125 ELSE
5126 -- Get the object_version number from csi_item_instances
5127 OPEN ahl_obj_ver_csr;
5128 FETCH ahl_obj_ver_csr INTO l_instance_rec.object_version_number;
5129 IF (ahl_obj_ver_csr%NOTFOUND) THEN
5130 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_REMOVED_INSTANCE_INVALID');
5131 FND_MESSAGE.Set_Token('INST', p_instance_id);
5132 FND_MSG_PUB.ADD;
5133 CLOSE ahl_obj_ver_csr;
5134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5135 ELSE
5136 CLOSE ahl_obj_ver_csr;
5137 END IF;
5138 -- Populate l_instance_rec
5139 l_instance_rec.INSTANCE_ID := p_instance_id;
5140 l_instance_rec.LOCATION_TYPE_CODE := 'WIP';
5141 l_instance_rec.WIP_JOB_ID := l_wip_entity_id;
5142 l_instance_rec.instance_usage_code := 'IN_WIP';
5143
5144 -- Get location id
5145 OPEN ahl_wip_location_csr;
5146 FETCH ahl_wip_location_csr INTO l_instance_rec.LOCATION_ID ;
5147 CLOSE ahl_wip_location_csr;
5148
5149 -- get transaction_type_id .
5150 AHL_Util_UC_Pkg.GetCSI_Transaction_ID('UC_UPDATE', l_transaction_type_id, l_return_val);
5151 IF NOT(l_return_val) THEN
5152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5153 END IF;
5154 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
5155 l_csi_transaction_rec.source_transaction_date := sysdate;
5156
5157 -- Call the CSI API to actually do the update
5158 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5159 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'About to call CSI_ITEM_INSTANCE_PUB.UPDATE_ITEM_INSTANCE');
5160 END IF;
5161
5162 CSI_ITEM_INSTANCE_PUB.UPDATE_ITEM_INSTANCE(p_api_version => 1.0
5163 ,p_commit => fnd_api.g_false
5164 ,p_init_msg_list => fnd_api.g_false
5165 ,p_validation_level => fnd_api.g_valid_level_full
5166 ,p_instance_rec => l_instance_rec
5167 ,p_ext_attrib_values_tbl => l_extend_attrib_values_tbl
5168 ,p_party_tbl => l_party_tbl
5169 ,p_account_tbl => l_account_tbl
5170 ,p_pricing_attrib_tbl => l_pricing_attrib_tbl
5171 ,p_org_assignments_tbl => l_org_assignments_tbl
5172 ,p_asset_assignment_tbl => l_asset_assignment_tbl
5173 ,p_txn_rec => l_csi_transaction_rec
5174 ,x_instance_id_lst => l_instance_id_lst
5175 ,x_return_status => x_return_status
5176 ,x_msg_count => l_msg_count
5177 ,x_msg_data => l_msg_data);
5178
5179 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5180 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Returned from CSI_ITEM_INSTANCE_PUB.UPDATE_ITEM_INSTANCE. x_return_status = ' || x_return_status);
5181 END IF;
5182 END IF; -- Status is Released or not
5183 END IF; -- WIP Entity Id is null or not
5184
5185 -- Updated by jaramana on October 15, 2007 since the CSI API seems to nullify return params
5186 IF (x_return_status IS NULL AND NVL(l_msg_count, 0) = 0) THEN
5187 x_return_status := FND_API.G_RET_STS_SUCCESS;
5188 END IF;
5189
5190 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5191 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_full_name, 'Exiting Procedure. x_return_status = ' || x_return_status);
5192 END IF;
5193
5194 END update_item_location;
5195 */
5196
5197 END AHL_PRD_DISPOSITION_PVT;
[Home] [Help]