[Home] [Help]
PACKAGE BODY: APPS.AHL_WIP_JOB_PVT
Source
1 PACKAGE BODY ahl_wip_job_pvt AS
2 /* $Header: AHLVWIPB.pls 120.1.12000000.2 2007/08/09 10:42:13 adivenka ship $ */
3
4 -- Define Global Type --
5 TYPE num_array_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6
7 -- Define Global Variable --
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_WIP_JOB_PVT';
9 G_DEBUG VARCHAR2(1) := NVL(AHL_DEBUG_PUB.is_log_enabled,'N');
10
11 -- Define Global Cursors --
12 CURSOR job_header_exists(c_group_id NUMBER, c_parent_header_id NUMBER) IS
13 SELECT 'X'
14 FROM wip_job_schedule_interface
15 WHERE group_id = c_group_id
16 AND header_id = c_parent_header_id;
17
18 CURSOR get_interface_ids(c_group_id NUMBER, c_header_id NUMBER) IS
19 SELECT interface_id
20 FROM wip_job_dtls_interface
21 WHERE group_id = c_group_id
22 AND parent_header_id = c_header_id
23 AND interface_id IS NOT NULL;
24
25 CURSOR get_error_msg(c_interface_id NUMBER) IS
26 SELECT error
27 FROM wip_interface_errors
28 WHERE error_type = 1
29 AND interface_id = c_interface_id;
30
31 CURSOR get_wip_entity(c_wip_entity_name VARCHAR2, c_organization_id NUMBER) IS
32 SELECT wip_entity_id
33 FROM wip_entities
34 WHERE wip_entity_name = c_wip_entity_name
35 AND organization_id = c_organization_id;
36
37 -- Declare and define local procedure insert_job_header --
38 PROCEDURE insert_job_header(
39 p_ahl_wo_rec IN AHL_WO_REC_TYPE,
40 p_group_id IN NUMBER,
41 p_header_id IN NUMBER,
42 x_group_id OUT NOCOPY NUMBER,
43 x_header_id OUT NOCOPY NUMBER,
44 x_return_status OUT NOCOPY VARCHAR2
45 ) IS
46 l_wip_job_rec wip_job_schedule_interface%ROWTYPE;
47 l_group_id NUMBER;
48 l_header_id NUMBER;
49
50 BEGIN
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52
53 -- Ensure the DML operation for the Job header is only Insert or Update
54 IF (p_ahl_wo_rec.dml_type <> 'I' AND p_ahl_wo_rec.dml_type <> 'U') THEN
55 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_JOB_DML_TYPE');
56 FND_MSG_PUB.ADD;
57 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
58 RETURN;
59 END IF;
60
61 IF (p_group_id IS NULL AND p_header_id IS NOT NULL) OR
62 (p_group_id IS NOT NULL AND p_header_id IS NULL) THEN
63 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_GROUP_HEADER_MISMATCH');
64 FND_MSG_PUB.ADD;
65 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
66 RETURN;
67 END IF;
68
69 IF p_group_id IS NULL THEN
70 SELECT wip_job_schedule_interface_s.NEXTVAL INTO l_group_id FROM dual;
71 l_header_id := l_group_id;
72 ELSE
73 l_group_id := p_group_id;
74 l_header_id := p_header_id + 1;
75 END IF;
76
77 l_wip_job_rec.process_phase := 2; --validation
78 l_wip_job_rec.process_status := 1; --pending
79 l_wip_job_rec.group_id := l_group_id;
80 l_wip_job_rec.header_id := l_header_id;
81 l_wip_job_rec.source_code := 'Service';
82 l_wip_job_rec.maintenance_object_source := 2; -- for 'AHL'
83 l_wip_job_rec.maintenance_object_type := 3; -- for 'CII'
84 l_wip_job_rec.creation_date := SYSDATE;
85 l_wip_job_rec.last_update_date := SYSDATE;
86 l_wip_job_rec.created_by := fnd_global.user_id;
87 l_wip_job_rec.last_updated_by := fnd_global.user_id;
88 l_wip_job_rec.last_update_login := fnd_global.login_id;
89 --l_wip_job_rec.last_updated_login := fnd_global.conc_login_id;
90 l_wip_job_rec.request_id := fnd_global.conc_request_id;
91 l_wip_job_rec.program_id := fnd_global.conc_program_id;
92 l_wip_job_rec.program_application_id := fnd_global.prog_appl_id;
93
94 IF p_ahl_wo_rec.dml_type = 'I' THEN
95 l_wip_job_rec.load_type := 7; --create job
96 ELSIF p_ahl_wo_rec.dml_type = 'U' THEN
97 l_wip_job_rec.load_type := 8; --update job
98 END IF;
99
100 l_wip_job_rec.job_name := p_ahl_wo_rec.wo_name;
101 l_wip_job_rec.organization_id := p_ahl_wo_rec.organization_id;
102 l_wip_job_rec.status_type := p_ahl_wo_rec.status;
103 l_wip_job_rec.first_unit_start_date := p_ahl_wo_rec.scheduled_start;
104 l_wip_job_rec.last_unit_start_date := p_ahl_wo_rec.scheduled_start;
105 l_wip_job_rec.first_unit_completion_date := p_ahl_wo_rec.scheduled_end;
106 l_wip_job_rec.last_unit_completion_date := p_ahl_wo_rec.scheduled_end;
107 l_wip_job_rec.completion_subinventory := p_ahl_wo_rec.completion_subinventory;
108 l_wip_job_rec.completion_locator_id := p_ahl_wo_rec.completion_locator_id;
109 l_wip_job_rec.wip_supply_type := p_ahl_wo_rec.wip_supply_type;
110 l_wip_job_rec.firm_planned_flag := p_ahl_wo_rec.firm_planned_flag;
111 l_wip_job_rec.project_id := p_ahl_wo_rec.project_id;
112 l_wip_job_rec.task_id := p_ahl_wo_rec.prj_task_id;
113
114 IF p_ahl_wo_rec.dml_type = 'I' THEN
115 l_wip_job_rec.start_quantity := 1;
116 ELSIF p_ahl_wo_rec.dml_type = 'U' THEN
117 l_wip_job_rec.start_quantity := NULL;
118 END IF;
119
120 IF p_ahl_wo_rec.dml_type = 'I' THEN
121 l_wip_job_rec.net_quantity := 1;
122 ELSIF p_ahl_wo_rec.dml_type = 'U' THEN
123 l_wip_job_rec.net_quantity := 0;
124 END IF;
125
126 IF ( p_ahl_wo_rec.dml_type = 'U' AND
127 ( p_ahl_wo_rec.inventory_item_id IS NULL OR
128 p_ahl_wo_rec.item_instance_id IS NULL OR
129 p_ahl_wo_rec.class_code IS NULL )
130 ) THEN
131
132 SELECT rebuild_item_id,
133 maintenance_object_id,
134 class_code
135 INTO l_wip_job_rec.rebuild_item_id,
136 l_wip_job_rec.maintenance_object_id,
137 l_wip_job_rec.class_code
138 FROM wip_discrete_jobs
139 WHERE wip_entity_id =
140 ( SELECT wip_entity_id
141 FROM wip_entities
142 WHERE wip_entity_name = p_ahl_wo_rec.wo_name );
143
144 ELSE
145 l_wip_job_rec.rebuild_item_id := p_ahl_wo_rec.inventory_item_id;
146 l_wip_job_rec.maintenance_object_id := p_ahl_wo_rec.item_instance_id;
147 l_wip_job_rec.class_code := p_ahl_wo_rec.class_code;
148 END IF;
149
150 l_wip_job_rec.priority := p_ahl_wo_rec.priority;
151 l_wip_job_rec.owning_department := p_ahl_wo_rec.department_id;
152 l_wip_job_rec.manual_rebuild_flag := p_ahl_wo_rec.manual_rebuild_flag;
153 l_wip_job_rec.rebuild_serial_number := p_ahl_wo_rec.rebuild_serial_number;
154 l_wip_job_rec.description:= p_ahl_wo_rec.description;
155 l_wip_job_rec.allow_explosion := 'N';
156 l_wip_job_rec.scheduling_method := 3; --default to 'Manual'
157
158 --insert into table WJSI
159 BEGIN
160 INSERT INTO wip_job_schedule_interface
161 ( last_update_date,
162 last_updated_by,
163 creation_date,
164 created_by,
165 last_update_login,
166 request_id,
167 program_id,
168 program_application_id,
169 program_update_date,
170 load_type,
171 process_phase,
172 process_status,
173 group_id,
174 header_id,
175 source_code,
176 allow_explosion,
177 maintenance_object_source,
178 maintenance_object_type,
179 job_name,
180 organization_id,
181 status_type,
182 first_unit_start_date,
183 last_unit_completion_date,
184 rebuild_item_id,
185 maintenance_object_id,
186 rebuild_serial_number,
187 manual_rebuild_flag,
188 completion_subinventory,
189 completion_locator_id,
190 start_quantity,
191 net_quantity,
192 wip_supply_type,
193 firm_planned_flag,
194 project_id,
195 task_id,
196 class_code,
197 priority,
198 owning_department,
199 scheduling_method,
200 description)
201 VALUES
202 ( l_wip_job_rec.last_update_date,
203 l_wip_job_rec.last_updated_by,
204 l_wip_job_rec.creation_date,
205 l_wip_job_rec.created_by,
206 l_wip_job_rec.last_update_login,
207 l_wip_job_rec.request_id,
208 l_wip_job_rec.program_id,
209 l_wip_job_rec.program_application_id,
210 l_wip_job_rec.program_update_date,
211 l_wip_job_rec.load_type,
212 l_wip_job_rec.process_phase,
213 l_wip_job_rec.process_status,
214 l_wip_job_rec.group_id,
215 l_wip_job_rec.header_id,
216 l_wip_job_rec.source_code,
217 l_wip_job_rec.allow_explosion,
218 l_wip_job_rec.maintenance_object_source,
219 l_wip_job_rec.maintenance_object_type,
220 l_wip_job_rec.job_name,
221 l_wip_job_rec.organization_id,
222 l_wip_job_rec.status_type,
223 l_wip_job_rec.first_unit_start_date,
224 l_wip_job_rec.last_unit_completion_date,
225 l_wip_job_rec.rebuild_item_id,
226 l_wip_job_rec.maintenance_object_id,
227 l_wip_job_rec.rebuild_serial_number,
228 l_wip_job_rec.manual_rebuild_flag,
229 l_wip_job_rec.completion_subinventory,
230 l_wip_job_rec.completion_locator_id,
231 l_wip_job_rec.start_quantity,
232 l_wip_job_rec.net_quantity,
233 l_wip_job_rec.wip_supply_type,
234 l_wip_job_rec.firm_planned_flag,
235 l_wip_job_rec.project_id,
236 l_wip_job_rec.task_id,
237 l_wip_job_rec.class_code,
238 l_wip_job_rec.priority,
239 l_wip_job_rec.owning_department,
240 l_wip_job_rec.scheduling_method,
241 l_wip_job_rec.description);
242 EXCEPTION
243 WHEN OTHERS THEN
244 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_JOB_HEADER_INSERT_ERR');
245 FND_MSG_PUB.ADD;
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247 RAISE; -- Reraise the exception and its calling procedure's exception handler
248 -- will handle it in its OTHERS exception part
249 END;
250 x_group_id := l_wip_job_rec.group_id;
251 x_header_id := l_wip_job_rec.header_id;
252 IF G_DEBUG='Y' THEN
253 AHL_DEBUG_PUB.debug('Finish: insert_job_header, x_return_status='||x_return_status);
254 --dbms_output.put_line('Finish: insert_job_header, x_return_status='||x_return_status);
255 END IF;
256 END insert_job_header;
257
258 -- Declare and define local procedure insert_job_operation --
259 PROCEDURE insert_job_operation(
260 p_ahl_wo_op_rec IN AHL_WO_OP_REC_TYPE,
261 p_group_id IN NUMBER,
262 p_parent_header_id IN NUMBER,
263 x_return_status OUT NOCOPY VARCHAR2
264 ) IS
265 l_wip_job_op_rec wip_job_dtls_interface%ROWTYPE;
266 l_dummy VARCHAR2(1);
267
268 BEGIN
269 x_return_status := FND_API.G_RET_STS_SUCCESS;
270
271 -- Ensure the DML operation for the Job operation is only Insert or Update
272 IF (p_ahl_wo_op_rec.dml_type <> 'I' AND p_ahl_wo_op_rec.dml_type <> 'U') THEN
273 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_OPER_DML_TYPE');
274 FND_MSG_PUB.ADD;
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276 RETURN;
277 END IF;
278
279 -- Validate p_group_id and p_parent_header_id
280 OPEN job_header_exists(p_group_id, p_parent_header_id);
281 FETCH job_header_exists INTO l_dummy;
282 IF job_header_exists%NOTFOUND THEN
283 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_JOB_HEADER');
284 FND_MSG_PUB.ADD;
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
286 CLOSE job_header_exists;
287 RETURN;
288 ELSE
289 CLOSE job_header_exists;
290 END IF;
291
292 -- Validate p_ahl_wo_op_rec.operation_seq_num, because operation_seq_num is
293 -- a NOT NULL column in table wip_job_dtls_interface
294 IF p_ahl_wo_op_rec.operation_seq_num IS NULL THEN
295 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OPERATION_SEQ_NULL');
296 FND_MSG_PUB.ADD;
297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298 RETURN;
299 END IF;
300
301 l_wip_job_op_rec.load_type := 3; --loading operation
302 l_wip_job_op_rec.process_phase := 2;
303 l_wip_job_op_rec.process_status := 1;
304 l_wip_job_op_rec.group_id := p_group_id;
305 l_wip_job_op_rec.parent_header_id := p_parent_header_id;
306 l_wip_job_op_rec.creation_date := SYSDATE;
307 l_wip_job_op_rec.last_update_date := SYSDATE;
308 l_wip_job_op_rec.created_by := fnd_global.user_id;
309 l_wip_job_op_rec.last_updated_by := fnd_global.user_id;
310 l_wip_job_op_rec.last_update_login := fnd_global.login_id;
311 --l_wip_job_op_rec.last_updated_login := fnd_global.conc_login_id;
312 l_wip_job_op_rec.request_id := fnd_global.conc_request_id;
313 l_wip_job_op_rec.program_id := fnd_global.conc_program_id;
314 l_wip_job_op_rec.program_application_id := fnd_global.prog_appl_id;
315
316 IF p_ahl_wo_op_rec.dml_type = 'I' THEN
317 l_wip_job_op_rec.substitution_type := 2; --add operation
318 ELSIF p_ahl_wo_op_rec.dml_type = 'U' THEN
319 l_wip_job_op_rec.substitution_type := 3; --update operation
320 END IF;
321 l_wip_job_op_rec.organization_id := p_ahl_wo_op_rec.organization_id;
322 l_wip_job_op_rec.operation_seq_num := p_ahl_wo_op_rec.operation_seq_num;
323 l_wip_job_op_rec.department_id := p_ahl_wo_op_rec.department_id;
324 l_wip_job_op_rec.first_unit_start_date := p_ahl_wo_op_rec.scheduled_start;
325 l_wip_job_op_rec.first_unit_completion_date := p_ahl_wo_op_rec.scheduled_end;
326 l_wip_job_op_rec.last_unit_start_date := p_ahl_wo_op_rec.scheduled_start;
327 l_wip_job_op_rec.last_unit_completion_date := p_ahl_wo_op_rec.scheduled_end;
328 --l_wip_job_op_rec.standard_operation_id := l_ahl_wo_op_rec.standard_operation_id;
329 l_wip_job_op_rec.description := p_ahl_wo_op_rec.description;
330 IF (p_ahl_wo_op_rec.dml_type = 'I' AND p_ahl_wo_op_rec.minimum_transfer_quantity IS NULL) THEN
331 l_wip_job_op_rec.minimum_transfer_quantity := 1; --default to 1 during creation
332 ELSE
333 l_wip_job_op_rec.minimum_transfer_quantity := p_ahl_wo_op_rec. minimum_transfer_quantity;
334 END IF;
335 IF (p_ahl_wo_op_rec.dml_type = 'I' AND p_ahl_wo_op_rec.count_point_type IS NULL) THEN
336 l_wip_job_op_rec.count_point_type := 2; --default to 2 'No -- Autocharge' during creation
337 ELSE
338 l_wip_job_op_rec.count_point_type := p_ahl_wo_op_rec.count_point_type;
339 END IF;
340 IF (p_ahl_wo_op_rec.dml_type = 'I' AND p_ahl_wo_op_rec.backflush_flag IS NULL) THEN
341 l_wip_job_op_rec.backflush_flag := 2; --default to 2 'No' during creation
342 ELSE
343 l_wip_job_op_rec.backflush_flag := p_ahl_wo_op_rec.backflush_flag;
344 END IF;
345 --Insert into table WJDI
346 BEGIN
347 INSERT INTO wip_job_dtls_interface
348 ( last_update_date,
349 last_updated_by,
350 creation_date,
351 created_by,
352 last_update_login,
353 request_id,
354 program_id,
355 program_application_id,
356 program_update_date,
357 load_type,
358 process_phase,
359 process_status,
360 group_id,
361 parent_header_id,
362 substitution_type,
363 organization_id,
364 operation_seq_num,
365 department_id,
366 description,
367 minimum_transfer_quantity,
368 count_point_type,
369 first_unit_start_date,
370 first_unit_completion_date,
371 last_unit_start_date,
372 last_unit_completion_date,
373 backflush_flag)
374 VALUES
375 ( l_wip_job_op_rec.last_update_date,
376 l_wip_job_op_rec.last_updated_by,
377 l_wip_job_op_rec.creation_date,
378 l_wip_job_op_rec.created_by,
379 l_wip_job_op_rec.last_update_login,
380 l_wip_job_op_rec.request_id,
381 l_wip_job_op_rec.program_id,
382 l_wip_job_op_rec.program_application_id,
383 l_wip_job_op_rec.program_update_date,
384 l_wip_job_op_rec.load_type,
385 l_wip_job_op_rec.process_phase,
386 l_wip_job_op_rec.process_status,
387 l_wip_job_op_rec.group_id,
388 l_wip_job_op_rec.parent_header_id,
389 l_wip_job_op_rec.substitution_type,
390 l_wip_job_op_rec.organization_id,
391 l_wip_job_op_rec.operation_seq_num,
392 l_wip_job_op_rec.department_id,
393 l_wip_job_op_rec.description,
394 l_wip_job_op_rec.minimum_transfer_quantity,
395 l_wip_job_op_rec.count_point_type,
399 l_wip_job_op_rec.last_unit_completion_date,
396 l_wip_job_op_rec.first_unit_start_date,
397 l_wip_job_op_rec.first_unit_completion_date,
398 l_wip_job_op_rec.last_unit_start_date,
400 l_wip_job_op_rec.backflush_flag);
401 EXCEPTION
402 WHEN OTHERS THEN
403 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_JOB_OPER_INSERT_ERR');
404 FND_MSG_PUB.ADD;
405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
406 RAISE;
407 END;
408 IF G_DEBUG='Y' THEN
409 AHL_DEBUG_PUB.debug('Finish: insert_job_operation, x_return_status='||x_return_status);
410 --dbms_output.put_line('Finish: insert_job_operation, x_return_status='||x_return_status);
411 END IF;
412 END insert_job_operation;
413
414 -- Declare and define local procedure insert_job_resource --
415 PROCEDURE insert_job_resource(
416 p_ahl_wo_res_rec IN AHL_WO_RES_REC_TYPE,
417 p_group_id IN NUMBER,
418 p_parent_header_id IN NUMBER,
419 x_return_status OUT NOCOPY VARCHAR2
420 ) IS
421 l_wip_job_res_rec wip_job_dtls_interface%ROWTYPE;
422 l_dummy VARCHAR2(1);
423
424 BEGIN
425 x_return_status := FND_API.G_RET_STS_SUCCESS;
426
427 -- Ensure the DML operation for the Job resource is Insert, Update or Delete
428 IF (p_ahl_wo_res_rec.dml_type <> 'I' AND p_ahl_wo_res_rec.dml_type <> 'U'
429 AND p_ahl_wo_res_rec.dml_type <> 'D') THEN
430 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_RES_DML_TYPE');
431 FND_MSG_PUB.ADD;
432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
433 RETURN;
434 END IF;
435
436 -- validate p_group_id and p_parent_header_id
437 OPEN job_header_exists(p_group_id, p_parent_header_id);
438 FETCH job_header_exists INTO l_dummy;
439 IF job_header_exists%NOTFOUND THEN
440 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_JOB_HEADER');
441 FND_MSG_PUB.ADD;
442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
443 CLOSE job_header_exists;
444 RETURN;
445 ELSE
446 CLOSE job_header_exists;
447 END IF;
448
449 l_wip_job_res_rec.load_type := 1; --loading resource
450 l_wip_job_res_rec.process_phase := 2;
451 l_wip_job_res_rec.process_status := 1;
452 l_wip_job_res_rec.group_id := p_group_id;
453 l_wip_job_res_rec.parent_header_id := p_parent_header_id;
454 l_wip_job_res_rec.creation_date := SYSDATE;
455 l_wip_job_res_rec.last_update_date := SYSDATE;
456 l_wip_job_res_rec.created_by := fnd_global.user_id;
457 l_wip_job_res_rec.last_updated_by := fnd_global.user_id;
458 l_wip_job_res_rec.last_update_login := fnd_global.login_id;
459 --l_wip_job_res_rec.last_updated_login := fnd_global.conc_login_id;
460 l_wip_job_res_rec.request_id := fnd_global.conc_request_id;
461 l_wip_job_res_rec.program_id := fnd_global.conc_program_id;
462 l_wip_job_res_rec.program_application_id := fnd_global.prog_appl_id;
463
464 IF p_ahl_wo_res_rec.dml_type = 'I' THEN
465 l_wip_job_res_rec.substitution_type := 2; --add resource
466 ELSIF p_ahl_wo_res_rec.dml_type = 'U' THEN
467 l_wip_job_res_rec.substitution_type := 3; --update resource
468 ELSIF p_ahl_wo_res_rec.dml_type = 'D' THEN
469 l_wip_job_res_rec.substitution_type := 1; --delete resource
470 END IF;
471
472 l_wip_job_res_rec.organization_id := p_ahl_wo_res_rec.organization_id;
473 l_wip_job_res_rec.operation_seq_num := p_ahl_wo_res_rec.operation_seq_num;
474 l_wip_job_res_rec.resource_seq_num := p_ahl_wo_res_rec.resource_seq_num;
475 l_wip_job_res_rec.department_id := p_ahl_wo_res_rec.department_id;
476 l_wip_job_res_rec.description := p_ahl_wo_res_rec.description;
477 l_wip_job_res_rec.schedule_seq_num := p_ahl_wo_res_rec.scheduled_sequence;
478 l_wip_job_res_rec.resource_id_new := p_ahl_wo_res_rec.resource_id_new;
479 l_wip_job_res_rec.resource_id_old := p_ahl_wo_res_rec.resource_id_old;
480 l_wip_job_res_rec.uom_code := p_ahl_wo_res_rec.uom;
481 l_wip_job_res_rec.basis_type := p_ahl_wo_res_rec.cost_basis;
482 l_wip_job_res_rec.usage_rate_or_amount := p_ahl_wo_res_rec.quantity;
483 l_wip_job_res_rec.assigned_units := p_ahl_wo_res_rec.assigned_units;
484 l_wip_job_res_rec.scheduled_flag := p_ahl_wo_res_rec.scheduled_flag;
485 l_wip_job_res_rec.activity_id := p_ahl_wo_res_rec.activity_id;
486 l_wip_job_res_rec.autocharge_type := p_ahl_wo_res_rec.autocharge_type;
487 l_wip_job_res_rec.standard_rate_flag := p_ahl_wo_res_rec.standard_rate_flag;
488 l_wip_job_res_rec.applied_resource_units := p_ahl_wo_res_rec.applied_resource_units;
489 l_wip_job_res_rec.applied_resource_value := p_ahl_wo_res_rec.applied_resource_value;
490 l_wip_job_res_rec.start_date := p_ahl_wo_res_rec.start_date;
491 l_wip_job_res_rec.completion_date := p_ahl_wo_res_rec.end_date;
492 l_wip_job_res_rec.setup_id := p_ahl_wo_res_rec.setup_id;
493
494 --insert into table WJDI
495 BEGIN
496 INSERT INTO wip_job_dtls_interface
497 ( last_update_date,
498 last_updated_by,
499 creation_date,
500 created_by,
501 last_update_login,
502 request_id,
503 program_id,
504 program_application_id,
505 program_update_date,
506 load_type,
507 process_phase,
508 process_status,
509 group_id,
510 parent_header_id,
511 substitution_type,
512 operation_seq_num,
513 resource_seq_num,
514 organization_id,
515 department_id,
519 uom_code,
516 schedule_seq_num,
517 resource_id_old,
518 resource_id_new,
520 basis_type,
521 usage_rate_or_amount,
522 assigned_units,
523 scheduled_flag,
524 activity_id,
525 autocharge_type,
526 standard_rate_flag,
527 applied_resource_units,
528 applied_resource_value,
529 description,
530 start_date,
531 completion_date,
532 setup_id)
533 VALUES
534 ( l_wip_job_res_rec.last_update_date,
535 l_wip_job_res_rec.last_updated_by,
536 l_wip_job_res_rec.creation_date,
537 l_wip_job_res_rec.created_by,
538 l_wip_job_res_rec.last_update_login,
539 l_wip_job_res_rec.request_id,
540 l_wip_job_res_rec.program_id,
541 l_wip_job_res_rec.program_application_id,
542 l_wip_job_res_rec.program_update_date,
543 l_wip_job_res_rec.load_type,
544 l_wip_job_res_rec.process_phase,
545 l_wip_job_res_rec.process_status,
546 l_wip_job_res_rec.group_id,
547 l_wip_job_res_rec.parent_header_id,
548 l_wip_job_res_rec.substitution_type,
549 l_wip_job_res_rec.operation_seq_num,
550 l_wip_job_res_rec.resource_seq_num,
551 l_wip_job_res_rec.organization_id,
552 l_wip_job_res_rec.department_id,
553 l_wip_job_res_rec.schedule_seq_num,
554 l_wip_job_res_rec.resource_id_old,
555 l_wip_job_res_rec.resource_id_new,
556 l_wip_job_res_rec.uom_code,
557 l_wip_job_res_rec.basis_type,
558 l_wip_job_res_rec.usage_rate_or_amount,
559 l_wip_job_res_rec.assigned_units,
560 l_wip_job_res_rec.scheduled_flag,
561 l_wip_job_res_rec.activity_id,
562 l_wip_job_res_rec.autocharge_type,
563 l_wip_job_res_rec.standard_rate_flag,
564 l_wip_job_res_rec.applied_resource_units,
565 l_wip_job_res_rec.applied_resource_value,
566 l_wip_job_res_rec.description,
567 l_wip_job_res_rec.start_date,
568 l_wip_job_res_rec.completion_date,
569 l_wip_job_res_rec.setup_id);
570 EXCEPTION
571 WHEN OTHERS THEN
572 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_JOB_RES_INSERT_ERR');
573 FND_MSG_PUB.ADD;
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
575 RAISE;
576 END;
577 IF G_DEBUG='Y' THEN
578 AHL_DEBUG_PUB.debug('Finish: insert_job_resource, x_return_status='||x_return_status);
579 --dbms_output.put_line('Finish: insert_job_resource, x_return_status='||x_return_status);
580 END IF;
581 END insert_job_resource;
582
583 -- Declare and define local procedure insert_job_material --
584 PROCEDURE insert_job_material(
585 p_ahl_wo_mtl_rec IN AHL_WO_MTL_REC_TYPE,
586 p_group_id IN NUMBER,
587 p_parent_header_id IN NUMBER,
588 x_return_status OUT NOCOPY VARCHAR2
589 ) IS
590 l_wip_job_mtl_rec wip_job_dtls_interface%ROWTYPE;
591 l_dummy VARCHAR2(1);
592
593 BEGIN
594 x_return_status := FND_API.G_RET_STS_SUCCESS;
595
596 -- Ensure the DML operation for the Job material is Insert, Update or Delete
597 IF (p_ahl_wo_mtl_rec.dml_type <> 'I' AND p_ahl_wo_mtl_rec.dml_type <> 'U'
598 AND p_ahl_wo_mtl_rec.dml_type <> 'D') THEN
599 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_MTL_DML_TYPE');
600 FND_MSG_PUB.ADD;
601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
602 RETURN;
603 END IF;
604
605 -- Validate p_group_id and p_parent_header_id
606 OPEN job_header_exists(p_group_id, p_parent_header_id);
607 FETCH job_header_exists INTO l_dummy;
608 IF job_header_exists%NOTFOUND THEN
609 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_JOB_HEADER');
610 FND_MSG_PUB.ADD;
611 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
612 CLOSE job_header_exists;
613 RETURN;
614 ELSE
615 CLOSE job_header_exists;
616 END IF;
617
618 l_wip_job_mtl_rec.load_type := 2; --loading material
619 l_wip_job_mtl_rec.process_phase := 2;
620 l_wip_job_mtl_rec.process_status := 1;
621 l_wip_job_mtl_rec.group_id := p_group_id;
622 l_wip_job_mtl_rec.parent_header_id := p_parent_header_id;
623 l_wip_job_mtl_rec.creation_date := SYSDATE;
624 l_wip_job_mtl_rec.last_update_date := SYSDATE;
625 l_wip_job_mtl_rec.created_by := fnd_global.user_id;
626 l_wip_job_mtl_rec.last_updated_by := fnd_global.user_id;
627 l_wip_job_mtl_rec.last_update_login := fnd_global.login_id;
628 --l_wip_job_mtl_rec.last_updated_login := fnd_global.conc_login_id;
629 l_wip_job_mtl_rec.request_id := fnd_global.conc_request_id;
630 l_wip_job_mtl_rec.program_id := fnd_global.conc_program_id;
631 l_wip_job_mtl_rec.program_application_id := fnd_global.prog_appl_id;
632
633 IF p_ahl_wo_mtl_rec.dml_type = 'I' THEN
634 l_wip_job_mtl_rec.substitution_type := 2; --add materila
635 ELSIF p_ahl_wo_mtl_rec.dml_type = 'U' THEN
636 l_wip_job_mtl_rec.substitution_type := 3; --update material
637 ELSIF p_ahl_wo_mtl_rec.dml_type = 'D' THEN
638 l_wip_job_mtl_rec.substitution_type := 1; --delete material
639 END IF;
640
641 l_wip_job_mtl_rec.organization_id := p_ahl_wo_mtl_rec.organization_id;
642 l_wip_job_mtl_rec.operation_seq_num := p_ahl_wo_mtl_rec. operation_seq_num;
643 l_wip_job_mtl_rec.inventory_item_id_new := p_ahl_wo_mtl_rec.inventory_item_id_new;
644 l_wip_job_mtl_rec.inventory_item_id_old := p_ahl_wo_mtl_rec.inventory_item_id_old;
648 l_wip_job_mtl_rec.wip_supply_type := p_ahl_wo_mtl_rec.supply_type;
645 l_wip_job_mtl_rec.mrp_net_flag := p_ahl_wo_mtl_rec.mrp_net;
646 l_wip_job_mtl_rec.quantity_per_assembly := p_ahl_wo_mtl_rec.quantity_per_assembly;
647 l_wip_job_mtl_rec.required_quantity := p_ahl_wo_mtl_rec.required_quantity;
649 l_wip_job_mtl_rec.supply_locator_id := p_ahl_wo_mtl_rec.supply_locator_id;
650 l_wip_job_mtl_rec.supply_subinventory := p_ahl_wo_mtl_rec.supply_subinventory;
651 l_wip_job_mtl_rec.date_required := p_ahl_wo_mtl_rec.date_required;
652
653 --Insert into table WJDI
654 BEGIN
655 INSERT INTO wip_job_dtls_interface
656 ( last_update_date,
657 last_updated_by,
658 creation_date,
659 created_by,
660 last_update_login,
661 request_id,
662 program_id,
663 program_application_id,
664 program_update_date,
665 load_type,
666 process_phase,
667 process_status,
668 group_id,
669 parent_header_id,
670 substitution_type,
671 operation_seq_num,
672 organization_id,
673 inventory_item_id_old,
674 inventory_item_id_new,
675 mrp_net_flag,
676 quantity_per_assembly,
677 required_quantity,
678 wip_supply_type,
679 supply_locator_id,
680 supply_subinventory,
681 date_required)
682 VALUES
683 ( l_wip_job_mtl_rec.last_update_date,
684 l_wip_job_mtl_rec.last_updated_by,
685 l_wip_job_mtl_rec.creation_date,
686 l_wip_job_mtl_rec.created_by,
687 l_wip_job_mtl_rec.last_update_login,
688 l_wip_job_mtl_rec.request_id,
689 l_wip_job_mtl_rec.program_id,
690 l_wip_job_mtl_rec.program_application_id,
691 l_wip_job_mtl_rec.program_update_date,
692 l_wip_job_mtl_rec.load_type,
693 l_wip_job_mtl_rec.process_phase,
694 l_wip_job_mtl_rec.process_status,
695 l_wip_job_mtl_rec.group_id,
696 l_wip_job_mtl_rec.parent_header_id,
697 l_wip_job_mtl_rec.substitution_type,
698 l_wip_job_mtl_rec.operation_seq_num,
699 l_wip_job_mtl_rec.organization_id,
700 l_wip_job_mtl_rec.inventory_item_id_old,
701 l_wip_job_mtl_rec.inventory_item_id_new,
702 l_wip_job_mtl_rec.mrp_net_flag,
703 l_wip_job_mtl_rec.quantity_per_assembly,
704 l_wip_job_mtl_rec.required_quantity,
705 l_wip_job_mtl_rec.wip_supply_type,
706 l_wip_job_mtl_rec.supply_locator_id,
707 l_wip_job_mtl_rec.supply_subinventory,
708 l_wip_job_mtl_rec.date_required);
709 EXCEPTION
710 WHEN OTHERS THEN
711 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_JOB_MTL_INSERT_ERR');
712 FND_MSG_PUB.ADD;
713 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
714 RAISE;
715 END;
716 IF G_DEBUG='Y' THEN
717 AHL_DEBUG_PUB.debug('Finish: insert_job_material, x_return_status='||x_return_status);
718 --dbms_output.put_line('Finish: insert_job_material, x_return_status='||x_return_status);
719 END IF;
720 END insert_job_material;
721
722 -- Declare and define local procedure submit_wip_load --
723 PROCEDURE submit_wip_load(
724 x_return_status OUT NOCOPY VARCHAR2,
725 p_group_id IN NUMBER
726 ) IS
727 l_dummy VARCHAR2(1);
728 l_targetp NUMBER;
729 l_activep NUMBER;
730 l_targetp1 NUMBER;
731 l_activep1 NUMBER;
732 l_pmon_method VARCHAR2(30);
733 l_callstat NUMBER;
734 l_req_id NUMBER;
735 l_boolvar BOOLEAN;
736 l_phase VARCHAR2(80);
737 l_status VARCHAR2(80);
738 l_dev_phase VARCHAR2(80);
739 l_dev_status VARCHAR2(80);
740 l_message VARCHAR2(255);
741 CURSOR job_header_exists(c_group_id NUMBER) IS
742 SELECT 'X'
743 FROM wip_job_schedule_interface
744 WHERE group_id = c_group_id;
745
746 BEGIN
747 -- Validate p_group_id
748 OPEN job_header_exists(p_group_id);
749 FETCH job_header_exists INTO l_dummy;
750 IF job_header_exists%NOTFOUND THEN
751 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_INVALID_JOB_HEADER');
752 FND_MSG_PUB.ADD;
753 CLOSE job_header_exists;
754 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
755 RETURN;
756 ELSE
757 CLOSE job_header_exists;
758 END IF;
759
760 -- check whether Internal Concurrent Manager is up
761 fnd_concurrent.get_manager_status(applid => 0,
762 managerid => 1,
763 targetp => l_targetp1,
764 activep => l_activep1,
765 pmon_method => l_pmon_method,
766 callstat => l_callstat);
767 -- check whether Standard Concurrent Manager is up, this is not optional.
768 fnd_concurrent.get_manager_status(applid => 0,
769 managerid => 0,
770 targetp => l_targetp,
771 activep => l_activep,
772 pmon_method => l_pmon_method,
773 callstat => l_callstat);
774 IF (l_activep <= 0 OR l_activep1 <= 0) THEN
775 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_CM_DOWN');
776 FND_MSG_PUB.ADD;
780
777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
778 RETURN;
779 END IF;
781 -- check whether Concurrent Program is available
782 IF NOT fnd_program.program_exists('WICMLP','WIP') THEN
783 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_NO_WICMLP');
784 FND_MSG_PUB.ADD;
785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
786 RETURN;
787 END IF;
788
789 -- check whether Concurrent Executible is available
790 IF NOT fnd_program.executable_exists('WICMLX','WIP') THEN
791 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_NO_WICMLX');
792 FND_MSG_PUB.ADD;
793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
794 RETURN;
795 END IF;
796
797 IF G_DEBUG='Y' THEN
798 AHL_DEBUG_PUB.debug('Inside: submit_wip_load, just before calling submit_request');
799 --dbms_output.put_line('Inside: submit_wip_load, just before calling submit_request');
800 END IF;
801 -- submit request of WIP Mass Load
802 l_req_id := fnd_request.submit_request('WIP','WICMLP', NULL, NULL, FALSE,
803 TO_CHAR(p_group_id), 0, 1);
804
805 IF G_DEBUG='Y' THEN
806 AHL_DEBUG_PUB.debug('When calling submit_request, the group_id is '||to_char(p_group_id));
807 --dbms_output.put_line('When calling submit_request, the group_id is '||to_char(p_group_id));
808 END IF;
809 IF (l_req_id = 0 ) THEN
810 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WICMLP_SUBMIT_FAILURE');
811 FND_MSG_PUB.ADD;
812 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
813 IF G_DEBUG='Y' THEN
814 AHL_DEBUG_PUB.debug('Submit request itself failed and the initialized parameters for the application are: ');
815 --dbms_output.put_line('Submit request itself failed and the initialized parameters for the application are: ');
816 AHL_DEBUG_PUB.debug('user_id='||to_char(fnd_global.user_id)||' resp_id='||to_char(fnd_global.resp_id)||' resp_appl_id='||to_char(fnd_global.resp_appl_id));
817 --dbms_output.put_line('user_id='||to_char(fnd_global.user_id)||' resp_id='||to_char(fnd_global.resp_id)||' resp_appl_id='||to_char(fnd_global.resp_appl_id));
818 AHL_DEBUG_PUB.debug('user_name='||fnd_global.user_name||' resp_name='||fnd_global.resp_name||' application_name='||fnd_global.application_name);
819 --dbms_output.put_line('user_name='||fnd_global.user_name||' resp_name='||fnd_global.resp_name||' application_name='||fnd_global.application_name);
820 END IF;
821 ELSE
822 COMMIT; --This commit is a must;
823 IF G_DEBUG='Y' THEN
824 AHL_DEBUG_PUB.debug('After commit and before waiting for request');
825 --dbms_output.put_line('After commit and before waiting for request');
826 END IF;
827 -- wait for the execution result of WIP Mass Load
828 l_boolvar := fnd_concurrent.wait_for_request(
829 request_id => l_req_id,
830 interval => 15,
831 max_wait => 0,
832 phase => l_phase,
833 status => l_status,
834 dev_phase => l_dev_phase,
835 dev_status => l_dev_status,
836 message => l_message);
837 IF NOT l_boolvar THEN
838 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WICMLP_WAIT_FAILURE');
839 FND_MSG_PUB.ADD;
840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
841 IF G_DEBUG='Y' THEN
842 AHL_DEBUG_PUB.debug('Wait for request itself failed');
843 --dbms_output.put_line('Wait for request itself failed');
844 END IF;
845 ELSIF (l_dev_phase = 'COMPLETE' AND l_dev_status = 'NORMAL') THEN
846 x_return_status := FND_API.G_RET_STS_SUCCESS;
847 ELSE
848 x_return_status := FND_API.G_RET_STS_ERROR;
849 END IF;
850 END IF;
851 IF G_DEBUG='Y' THEN
852 AHL_DEBUG_PUB.debug('Finish: submit_wip_load, x_return_status='||x_return_status);
853 --dbms_output.put_line('Finish: submit_wip_load, x_return_status='||x_return_status);
854 END IF;
855 END submit_wip_load;
856
857 -- Define procedure load_wip_job --
858 PROCEDURE load_wip_job(
859 p_api_version IN NUMBER,
860 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
861 -- p_commit IN VARCHAR2 := FND_API.G_FALSE,
862 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
863 x_return_status OUT NOCOPY VARCHAR2,
864 x_msg_count OUT NOCOPY NUMBER,
865 x_msg_data OUT NOCOPY VARCHAR2,
866 p_ahl_wo_rec IN ahl_wo_rec_type,
867 p_ahl_wo_op_tbl IN ahl_wo_op_tbl_type,
868 p_ahl_wo_res_tbl IN ahl_wo_res_tbl_type,
869 p_ahl_wo_mtl_tbl IN ahl_wo_mtl_tbl_type,
870 x_wip_entity_id OUT NOCOPY NUMBER
871 ) IS
872 PRAGMA AUTONOMOUS_TRANSACTION;
873 --This API is Autonomous Transaction. We have to explicitly commit or rollback the
874 --transactions it or its called procedure contains when it exits. This autonomous
875 --transaction doesn't affect the main transaction in its calling API.
876
877 i NUMBER;
878 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_WIP_JOB';
879 l_api_version CONSTANT NUMBER := 1.0;
880 l_msg_count NUMBER;
881 l_return_status VARCHAR2(1);
882 l_group_id NUMBER;
883 l_header_id NUMBER;
884
885 l_interface1_id NUMBER;
886 l_error_msg VARCHAR2(5000);
887 l_interface_ids num_array_type;
888 l_commit_flag NUMBER;
889
893 FROM wip_job_schedule_interface
890 CURSOR get_header_interface(c_job_name VARCHAR2, c_organization_id NUMBER,
891 c_group_id NUMBER) IS
892 SELECT interface_id, header_id
894 WHERE job_name = c_job_name
895 AND organization_id = c_organization_id
896 AND group_id = c_group_id;
897
898 CURSOR get_cp_sts(c_group_id NUMBER, c_header_id NUMBER) IS
899 SELECT process_phase, process_status
900 FROM wip_job_schedule_interface
901 WHERE group_id = c_group_id
902 AND header_id = c_header_id;
903 l_get_cp_sts get_cp_sts%ROWTYPE;
904
905 BEGIN
906 --SAVEPOINT LOAD_WIP_JOB_PVT;
907 --Savepoint here is not necessary, because we have a commit statement in its called
908 --procedure submit_wip_load and it will make this savepoint invalid.
909 IF G_DEBUG='Y' THEN
910 AHL_DEBUG_PUB.enable_debug;
911 AHL_DEBUG_PUB.debug('Begin private API: AHL_WIP_JOB_PVT.LOAD_WIP_JOB');
912 END IF;
913 IF FND_API.to_boolean(p_init_msg_list) THEN
914 FND_MSG_PUB.initialize;
915 END IF;
916
917 x_return_status := FND_API.G_RET_STS_SUCCESS;
918
919 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version,
920 l_api_name, G_PKG_NAME)
921 THEN
922 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
923 END IF;
924
925 IF G_DEBUG='Y' THEN
926 AHL_DEBUG_PUB.debug('Inside: load_wip_job, just before calling insert_job_header');
927 --dbms_output.put_line('Inside: load_wip_job, just before calling insert_job_header');
928 END IF;
929 --insert job header first
930 insert_job_header( p_ahl_wo_rec => p_ahl_wo_rec,
931 p_group_id => NULL, --for single job submission
932 p_header_id => NULL,
933 x_group_id => l_group_id,
934 x_header_id => l_header_id,
935 x_return_status => l_return_status );
936 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
938 END IF;
939
940 IF G_DEBUG='Y' THEN
941 AHL_DEBUG_PUB.debug('Inside: load_wip_job, just before calling insert_job_operation');
942 --dbms_output.put_line('Inside: load_wip_job, just before calling insert_job_operation');
943 END IF;
944 --insert job operations if they are available
945 IF p_ahl_wo_op_tbl.count > 0 THEN
946 FOR i IN p_ahl_wo_op_tbl.FIRST..p_ahl_wo_op_tbl.LAST LOOP
947 insert_job_operation( p_ahl_wo_op_rec => p_ahl_wo_op_tbl(i),
948 p_group_id => l_group_id,
949 p_parent_header_id => l_header_id,
950 x_return_status => l_return_status );
951 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
953 END IF;
954 END LOOP;
955 END IF;
956
957 IF G_DEBUG='Y' THEN
958 AHL_DEBUG_PUB.debug('Inside: load_wip_job, just before calling insert_job_resource');
959 --dbms_output.put_line('Inside: load_wip_job, just before calling insert_job_resource');
960 END IF;
961 --insert resource requirements if they are available
962 IF p_ahl_wo_res_tbl.count > 0 THEN
963 FOR i IN p_ahl_wo_res_tbl.FIRST..p_ahl_wo_res_tbl.LAST LOOP
964 insert_job_resource( p_ahl_wo_res_rec => p_ahl_wo_res_tbl(i),
965 p_group_id => l_group_id,
966 p_parent_header_id => l_header_id,
967 x_return_status => l_return_status );
968 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
969 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
970 END IF;
971 END LOOP;
972 END IF;
973
974 IF G_DEBUG='Y' THEN
975 AHL_DEBUG_PUB.debug('Inside: load_wip_job, just before calling insert_job_material');
976 --dbms_output.put_line('Inside: load_wip_job, just before calling insert_job_material');
977 END IF;
978 --insert material requirements if they are available
979 IF p_ahl_wo_mtl_tbl.count > 0 THEN
980 FOR i IN p_ahl_wo_mtl_tbl.FIRST..p_ahl_wo_mtl_tbl.LAST LOOP
981 insert_job_material( p_ahl_wo_mtl_rec => p_ahl_wo_mtl_tbl(i),
982 p_group_id => l_group_id,
983 p_parent_header_id => l_header_id,
984 x_return_status => l_return_status );
985 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
986 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
987 END IF;
988 END LOOP;
989 END IF;
990
991 IF G_DEBUG='Y' THEN
992 AHL_DEBUG_PUB.debug('Inside: load_wip_job, just before calling submit_wip_load');
993 --dbms_output.put_line('Inside: load_wip_job, just before calling submit_wip_load');
994 END IF;
995 --submit WIP Mass Load
996 submit_wip_load( p_group_id => l_group_id,
997 x_return_status => l_return_status);
998
999 --check the process_phase and process_status in WJSI
1000 OPEN get_cp_sts(l_group_id, l_header_id);
1001 FETCH get_cp_sts INTO l_get_cp_sts;
1002
1003 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1004 CLOSE get_cp_sts;
1005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1006 ELSIF (l_return_status = FND_API.G_RET_STS_SUCCESS AND (get_cp_sts%NOTFOUND OR
1007 (l_get_cp_sts.process_phase = 4 AND l_get_cp_sts.process_status = 4))) THEN
1008 CLOSE get_cp_sts;
1009 OPEN get_wip_entity(p_ahl_wo_rec.wo_name, p_ahl_wo_rec.organization_id);
1010 FETCH get_wip_entity INTO x_wip_entity_id;
1011 IF get_wip_entity%NOTFOUND THEN
1012 CLOSE get_wip_entity;
1013 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_NO_WIP_ENTITY_ID');
1014 FND_MSG_PUB.ADD;
1015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1016 ELSE
1017 CLOSE get_wip_entity;
1018 END IF;
1019 --call EAM API to update manual_rebuild_flag because WICMLP can't change it.
1020 IF (p_ahl_wo_rec.dml_type = 'U' AND p_ahl_wo_rec.manual_rebuild_flag IS NOT NULL) THEN
1021 eam_workordertransactions_pub.set_manual_reb_flag(
1022 p_wip_entity_id => x_wip_entity_id,
1023 p_organization_id => p_ahl_wo_rec.organization_id,
1024 p_manual_rebuild_flag => p_ahl_wo_rec.manual_rebuild_flag,
1025 x_return_status => l_return_status);
1026 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1027 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_EAM_REBUILD_FLAG_FAIL');
1028 FND_MSG_PUB.ADD;
1029 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1030 END IF;
1031 END IF;
1032 --call EAM API to update owning_department_id because WICMLP can't change it.
1033 IF (p_ahl_wo_rec.dml_type = 'U' AND p_ahl_wo_rec.department_id IS NOT NULL) THEN
1034 eam_workordertransactions_pub.set_owning_department(
1035 p_wip_entity_id => x_wip_entity_id,
1036 p_organization_id => p_ahl_wo_rec.organization_id,
1037 p_owning_department => p_ahl_wo_rec.department_id,
1038 x_return_status => l_return_status);
1039 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1040 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_EAM_OWN_DEPT_FAIL');
1041 FND_MSG_PUB.ADD;
1042 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1043 END IF;
1044 END IF;
1045
1046 ELSE
1047 CLOSE get_cp_sts;
1048 OPEN get_header_interface(p_ahl_wo_rec.wo_name, p_ahl_wo_rec.organization_id, l_group_id);
1049 FETCH get_header_interface INTO l_interface1_id, l_header_id;
1050 IF get_header_interface%NOTFOUND THEN
1051 CLOSE get_header_interface;
1052 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1053 ELSE
1054 CLOSE get_header_interface;
1055 END IF;
1056
1057 l_interface_ids(1) := l_interface1_id;
1058 i := 2;
1059 FOR l_get_interface_ids IN get_interface_ids(l_group_id, l_header_id) LOOP
1060 l_interface_ids(i) := l_get_interface_ids.interface_id ;
1061 i := i + 1;
1062 END LOOP;
1063
1064 l_error_msg := '';
1065 FOR i IN l_interface_ids.FIRST..l_interface_ids.LAST LOOP
1066 FOR l_get_error_msg IN get_error_msg(l_interface_ids(i)) LOOP
1067 --l_error_msg := l_error_msg || replace(l_get_error_msg.error,chr(10)||chr(10),chr(10));
1068 --chr(10)='\n', chr(13)='\r', chr() function will fail in GSCC standards
1069 l_error_msg := l_error_msg || l_get_error_msg.error;
1070 END LOOP;
1071 END LOOP;
1072
1073 --No deletion for the failed records in debug mode.
1074 IF fnd_profile.value('MRP_DEBUG') <> 'Y' THEN
1075 FOR i IN l_interface_ids.FIRST..l_interface_ids.LAST LOOP
1076 DELETE FROM wip_interface_errors
1077 WHERE interface_id = l_interface_ids(i);
1078 END LOOP;
1079
1080 DELETE FROM wip_job_dtls_interface
1081 WHERE group_id = l_group_id
1082 AND parent_header_id = l_header_id;
1083
1084 DELETE FROM wip_job_schedule_interface
1085 WHERE group_id = l_group_id
1086 AND header_id = l_header_id;
1087 END IF;
1088
1089 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WICMLP_ERROR');
1090 FND_MESSAGE.SET_TOKEN('ERROR',l_error_msg);
1091 FND_MSG_PUB.ADD;
1092 x_msg_count := 1;
1093 x_msg_data := l_error_msg;
1094 x_return_status := FND_API.G_RET_STS_ERROR;
1095
1096 IF G_DEBUG='Y' THEN
1097 AHL_DEBUG_PUB.debug('The concatenated error message is '||x_msg_data);
1098 --dbms_output.put_line(substr('The concatenated error message is '||x_msg_data, 1, 255));
1099 END IF;
1100 END IF;
1101
1102 COMMIT; --Autonomous Transaction Required
1103
1104 IF G_DEBUG='Y' THEN
1105 AHL_DEBUG_PUB.debug('End private API: AHL_WIP_JOB_PVT.LOAD_WIP_JOB');
1106 AHL_DEBUG_PUB.disable_debug;
1107 END IF;
1108 EXCEPTION
1109 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1110 ROLLBACK;
1111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1112 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1113 p_count => x_msg_count,
1114 p_data => x_msg_data);
1115 IF G_DEBUG='Y' THEN
1116 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
1117 'UNEXPECTED ERROR IN PRIVATE:' );
1118 AHL_DEBUG_PUB.debug('AHL_WIP_JOB_PVT.LOAD_WIP_JOB');
1119 AHL_DEBUG_PUB.disable_debug;
1120 END IF;
1121 WHEN FND_API.G_EXC_ERROR THEN
1122 ROLLBACK;
1123 x_return_status := FND_API.G_RET_STS_ERROR;
1124 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1125 p_count => x_msg_count,
1126 p_data => x_msg_data);
1127 IF G_DEBUG='Y' THEN
1128 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
1129 'ERROR IN PRIVATE:' );
1130 AHL_DEBUG_PUB.debug('AHL_WIP_JOB_PVT.LOAD_WIP_JOB');
1131 AHL_DEBUG_PUB.disable_debug;
1135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1132 END IF;
1133 WHEN OTHERS THEN
1134 ROLLBACK;
1136 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1137 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_WIP_JOB_PVT',
1138 p_procedure_name => 'LOAD_WIP_JOB');
1139 END IF;
1140 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1141 p_count => x_msg_count,
1142 p_data => x_msg_data);
1143 IF G_DEBUG='Y' THEN
1144 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data,
1145 'OTHER ERROR IN PRIVATE:' );
1146 AHL_DEBUG_PUB.debug('AHL_WIP_JOB_PVT.LOAD_WIP_JOB');
1147 AHL_DEBUG_PUB.disable_debug;
1148 END IF;
1149
1150 END load_wip_job;
1151
1152 -- Define procedure insert_resource_txn --
1153 PROCEDURE insert_resource_txn(
1154 p_api_version IN NUMBER,
1155 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1156 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1157 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1158 x_return_status OUT NOCOPY VARCHAR2,
1159 x_msg_count OUT NOCOPY NUMBER,
1160 x_msg_data OUT NOCOPY VARCHAR2,
1161 p_ahl_res_txn_tbl IN ahl_res_txn_tbl_type
1162 ) IS
1163 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RESOURCE_TXN';
1164 l_api_version CONSTANT NUMBER := 1.0;
1165 l_msg_count NUMBER;
1166 l_return_status VARCHAR2(1);
1167 l_wip_cost_txn_rec wip_cost_txn_interface%ROWTYPE;
1168
1169 CURSOR get_user_name (c_user_id NUMBER) IS
1170 SELECT user_name
1171 FROM fnd_user
1172 WHERE user_id = c_user_id;
1173 l_user_name get_user_name%ROWTYPE;
1174 CURSOR get_org_code (c_org_id NUMBER) IS
1175 SELECT organization_code
1176 FROM mtl_parameters
1177 WHERE organization_id = c_org_id;
1178 l_org_code get_org_code%ROWTYPE;
1179 CURSOR get_job_attr (c_wip_entity_id NUMBER) IS
1180 -- fix for bug# 3161312.
1181 /*SELECT organization_id, wip_entity_name, entity_type
1182 FROM wip_entities
1183 WHERE wip_entity_id = c_wip_entity_id;*/
1184
1185 SELECT WDJ.project_id project_id,
1186 WDJ.task_id task_id,
1187 WE.organization_id organization_id,
1188 WE.wip_entity_name wip_entity_name,
1189 WE.entity_type entity_type
1190 FROM wip_discrete_jobs WDJ,
1191 wip_entities WE
1192 WHERE WDJ.wip_entity_id = WE.wip_entity_id
1193 AND WE.wip_entity_id = c_wip_entity_id;
1194
1195 l_job_attr get_job_attr%ROWTYPE;
1196 CURSOR get_employee_num (c_employee_id NUMBER, c_org_id NUMBER) IS
1197 /* SELECT employee_number,
1198 instance_id
1199 FROM bom_resource_employees bre, per_all_people_f pap
1200 WHERE bre.person_id = pap.person_id
1201 AND pap.person_id = c_employee_id; */
1202
1203 -- bug# 4553747.
1204 SELECT employee_num employee_number,
1205 instance_id
1206 FROM bom_resource_employees bre, mtl_employees_current_view mec
1207 WHERE bre.person_id = mec.employee_id
1208 AND mec.employee_id = c_employee_id
1209 AND bre.organization_id = mec.organization_id
1210 AND mec.organization_id = c_org_id;
1211
1212 l_employee_num get_employee_num%ROWTYPE;
1213 --Adithya modified the cursor code for Bug # 6326254 - Start
1214 CURSOR get_instance_sernum (c_department_id NUMBER,
1215 c_serial_number VARCHAR2,
1216 c_resource_id NUMBER,
1217 c_organization_id NUMBER)
1218 IS
1219 SELECT instance_id
1220 FROM bom_dept_res_instances
1221 WHERE department_id in (
1222 select
1223 distinct nvl(bodres.share_from_dept_id, bodres.department_id)
1224 from
1225 bom_departments bomdep,
1226 bom_department_resources bodres
1227 where
1228 bodres.department_id = bomdep.department_id and
1229 bomdep.department_id = c_department_id and
1230 bomdep.organization_id = c_organization_id
1231 )
1232 and Serial_Number=c_serial_number
1233 and Resource_id=c_resource_id;
1234
1235 -- Changes for bug # 6326254 - Begin
1236
1237 CURSOR get_operation_dept(c_wip_entity_id NUMBER, c_op_seq_no NUMBER)
1238 IS
1239 SELECT
1240 department_id
1241 FROM
1242 WIP_OPERATIONS
1243 WHERE
1244 wip_entity_id = c_wip_entity_id and
1245 operation_seq_num = c_op_seq_no;
1246
1247 l_op_dept_id NUMBER;
1248
1249 -- Changes for bug # 6326254 - End
1250
1251 BEGIN
1252 SAVEPOINT INSERT_RESOURCE_TXN_PVT;
1253 IF G_DEBUG='Y' THEN
1254 AHL_DEBUG_PUB.enable_debug;
1255 AHL_DEBUG_PUB.debug('Begin private API: AHL_WIP_JOB_PVT.INSERT_RESOURCE_TXN');
1256 END IF;
1257 IF FND_API.to_boolean(p_init_msg_list) THEN
1258 FND_MSG_PUB.initialize;
1259 END IF;
1260
1261 x_return_status := FND_API.G_RET_STS_SUCCESS;
1262
1263 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version,
1264 l_api_name, G_PKG_NAME)
1265 THEN
1266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1267 END IF;
1271 FND_MSG_PUB.add;
1268
1269 IF p_ahl_res_txn_tbl.count <= 0 THEN
1270 FND_MESSAGE.set_name('AHL','AHL_PRD_NO_RECORDS_IN_TBL');
1272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1273 END IF;
1274
1275 OPEN get_user_name(fnd_global.user_id);
1276 FETCH get_user_name INTO l_user_name;
1277 IF get_user_name%NOTFOUND THEN
1278 FND_MESSAGE.set_name('AHL','AHL_PRD_USER_ID_INVALID');
1279 FND_MSG_PUB.add;
1280 CLOSE get_user_name;
1281 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1282 END IF;
1283 CLOSE get_user_name;
1284
1285 FOR i IN p_ahl_res_txn_tbl.FIRST..p_ahl_res_txn_tbl.LAST LOOP
1286 -- Clear the record before every iteration
1287 -- Adithya added for Bug # 6326254.
1288 l_wip_cost_txn_rec := null;
1289
1290 OPEN get_job_attr(p_ahl_res_txn_tbl(i).wip_entity_id);
1291 FETCH get_job_attr INTO l_job_attr;
1292 IF get_job_attr%NOTFOUND THEN
1293 FND_MESSAGE.set_name('AHL','AHL_PRD_NO_WIP_ENTITY_ID');
1294 FND_MSG_PUB.add;
1295 CLOSE get_job_attr;
1296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1297 END IF;
1298 CLOSE get_job_attr;
1299
1300 OPEN get_org_code(l_job_attr.organization_id);
1301 FETCH get_org_code INTO l_org_code;
1302 IF get_org_code%NOTFOUND THEN
1303 FND_MESSAGE.set_name('AHL','AHL_PRD_ORG_ID_INVALID');
1304 FND_MSG_PUB.add;
1305 CLOSE get_org_code;
1306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1307 END IF;
1308 CLOSE get_org_code;
1309
1310 l_wip_cost_txn_rec.process_phase := 1; --Resource Validation
1311 l_wip_cost_txn_rec.process_status := 1; --Pending
1312 l_wip_cost_txn_rec.source_code := 'Service';
1313
1314 l_wip_cost_txn_rec.creation_date := sysdate;
1315 l_wip_cost_txn_rec.last_update_date := sysdate;
1316 l_wip_cost_txn_rec.created_by := fnd_global.user_id;
1317 --created_by_name is mandatory, otherwise Cost Manager won't pick it up.
1318 l_wip_cost_txn_rec.created_by_name := l_user_name.user_name;
1319 l_wip_cost_txn_rec.last_updated_by := fnd_global.user_id;
1320 --last_updated_by_name is mandatory, otherwise Cost Manager won't pick it up.
1321 l_wip_cost_txn_rec.last_updated_by_name := l_user_name.user_name;
1322 l_wip_cost_txn_rec.last_update_login := fnd_global.login_id;
1323 --l_wip_cost_txn_rec.last_updated_login := fnd_global.conc_login_id;
1324 l_wip_cost_txn_rec.request_id := fnd_global.conc_request_id;
1325 l_wip_cost_txn_rec.program_id := fnd_global.conc_program_id;
1326 l_wip_cost_txn_rec.program_application_id := fnd_global.prog_appl_id;
1327
1328 l_wip_cost_txn_rec.wip_entity_id := p_ahl_res_txn_tbl(i).wip_entity_id;
1329 --wip_entity_name and entity_type are mandatory, otherwise Cost Manager won't pick it up.
1330 l_wip_cost_txn_rec.wip_entity_name := l_job_attr.wip_entity_name;
1331 l_wip_cost_txn_rec.entity_type := l_job_attr.entity_type;
1332 l_wip_cost_txn_rec.project_id := l_job_attr.project_id;
1333 l_wip_cost_txn_rec.task_id := l_job_attr.task_id;
1334
1335 l_wip_cost_txn_rec.organization_id := l_job_attr.organization_id;
1336 --organization_code is mandatory, otherwise Cost Manager won't pick it up.
1337 l_wip_cost_txn_rec.organization_code := l_org_code.organization_code;
1338
1339
1340 l_wip_cost_txn_rec.operation_seq_num := p_ahl_res_txn_tbl(i).operation_seq_num;
1341 l_wip_cost_txn_rec.resource_seq_num := p_ahl_res_txn_tbl(i).resource_seq_num;
1342 --The column resource_id must be left NULL according to the document
1343 --l_wip_cost_txn_rec.resource_id := p_ahl_res_txn_tbl(i).resource_id;
1344 l_wip_cost_txn_rec.resource_id := NULL;
1345 IF (p_ahl_res_txn_tbl(i).transaction_type IS NULL) THEN
1346 l_wip_cost_txn_rec.transaction_type := 1; --Resource Transaction
1347 ELSE
1348 l_wip_cost_txn_rec.transaction_type := p_ahl_res_txn_tbl(i).transaction_type;
1349 END IF;
1350 IF (p_ahl_res_txn_tbl(i).transaction_date IS NULL) THEN
1351 l_wip_cost_txn_rec.transaction_date := SYSDATE;
1352 ELSE
1353 l_wip_cost_txn_rec.transaction_date := p_ahl_res_txn_tbl(i).transaction_date;
1354 END IF;
1355 l_wip_cost_txn_rec.transaction_uom := p_ahl_res_txn_tbl(i).transaction_uom;
1356 l_wip_cost_txn_rec.transaction_quantity := p_ahl_res_txn_tbl(i).transaction_quantity;
1357 l_wip_cost_txn_rec.employee_id := p_ahl_res_txn_tbl(i).employee_id;
1358 --If employee_id is not null, then employee_num is also required, otherwise there will
1359 --be validation error from Cost Manager
1360 IF (p_ahl_res_txn_tbl(i).employee_id IS NOT NULL) THEN
1361 OPEN get_employee_num(p_ahl_res_txn_tbl(i).employee_id, l_job_attr.organization_id);
1362 FETCH get_employee_num INTO l_employee_num;
1363 IF get_employee_num%NOTFOUND THEN
1364 FND_MESSAGE.set_name('AHL','AHL_PRD_EMPLOYEE_ID_INVALID');
1365 FND_MSG_PUB.add;
1366 CLOSE get_employee_num;
1367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1368 END IF;
1369 CLOSE get_employee_num;
1370 l_wip_cost_txn_rec.employee_num := l_employee_num.employee_number;
1371 l_wip_cost_txn_rec.instance_id := l_employee_num.instance_id;
1372 ELSE
1373 l_wip_cost_txn_rec.employee_num := NULL;
1374
1375 END IF;
1376
1377 l_wip_cost_txn_rec.department_id := p_ahl_res_txn_tbl(i).department_id;
1378 l_wip_cost_txn_rec.activity_id := p_ahl_res_txn_tbl(i).activity_id;
1379 l_wip_cost_txn_rec.activity_name := p_ahl_res_txn_tbl(i).activity_meaning;
1383
1380 l_wip_cost_txn_rec.reason_id := p_ahl_res_txn_tbl(i).reason_id;
1381 l_wip_cost_txn_rec.reason_name := p_ahl_res_txn_tbl(i).reason;
1382 l_wip_cost_txn_rec.reference := p_ahl_res_txn_tbl(i).reference;
1384 IF p_ahl_res_txn_tbl(i).serial_number IS NOT NULL
1385 AND p_ahl_res_txn_tbl(i).serial_number<>FND_API.G_MISS_CHAR
1386 THEN
1387 Open get_instance_sernum (p_ahl_res_txn_tbl(i).department_id,
1388 p_ahl_res_txn_tbl(i).serial_number,
1389 p_ahl_res_txn_tbl(i).resource_id,
1390 l_job_attr.organization_id); --Adithya modified
1391 FETCH get_instance_sernum INTO l_wip_cost_txn_rec.instance_id;
1392
1393 IF get_instance_sernum%NOTFOUND
1394 THEN
1395 FND_MESSAGE.set_name('AHL','AHL_PRD_RESTXN_SERNUM_INVALID');
1396 FND_MESSAGE.SET_TOKEN('SERNUMB',p_ahl_res_txn_tbl(i).serial_number);
1397 FND_MSG_PUB.ADD;
1398 END IF;
1399 Close get_instance_sernum;
1400 END IF;
1401 --insert resource transaction record into table WIP_COST_TXN_INTERFACE
1402 -- Adithya added code for populating charge_deparment_id if the input deparment
1403 -- is different from opeartion deparemt.Bug # 6326254
1404
1405 OPEN get_operation_dept(l_wip_cost_txn_rec.wip_entity_id, l_wip_cost_txn_rec.operation_seq_num);
1406 FETCH get_operation_dept INTO l_op_dept_id;
1407 CLOSE get_operation_dept;
1408
1409 IF l_op_dept_id <> l_wip_cost_txn_rec.department_id
1410 THEN
1411 l_wip_cost_txn_rec.charge_department_id := l_wip_cost_txn_rec.department_id;
1412 END IF;
1413 --insert resource transaction record into table WIP_COST_TXN_INTERFACE
1414
1415 BEGIN
1416 INSERT INTO wip_cost_txn_interface
1417 ( last_update_date,
1418 last_updated_by,
1419 last_updated_by_name,
1420 creation_date,
1421 created_by,
1422 created_by_name,
1423 last_update_login,
1424 request_id,
1425 program_id,
1426 program_application_id,
1427 program_update_date,
1428 process_phase,
1429 process_status,
1430 source_code,
1431 organization_id,
1432 organization_code,
1433 wip_entity_id,
1434 wip_entity_name,
1435 entity_type,
1436 project_id,
1437 task_id,
1438 operation_seq_num,
1439 resource_seq_num,
1440 resource_id,
1441 transaction_type,
1442 transaction_date,
1443 transaction_quantity,
1444 transaction_uom,
1445 employee_id,
1446 employee_num,
1447 department_id,
1448 activity_id,
1449 activity_name,
1450 reason_id,
1451 reason_name,
1452 reference,
1453 instance_id,
1454 charge_department_id)
1455 VALUES
1456 ( l_wip_cost_txn_rec.last_update_date,
1457 l_wip_cost_txn_rec.last_updated_by,
1458 l_wip_cost_txn_rec.last_updated_by_name,
1459 l_wip_cost_txn_rec.creation_date,
1460 l_wip_cost_txn_rec.created_by,
1461 l_wip_cost_txn_rec.created_by_name,
1462 l_wip_cost_txn_rec.last_update_login,
1463 l_wip_cost_txn_rec.request_id,
1464 l_wip_cost_txn_rec.program_id,
1465 l_wip_cost_txn_rec.program_application_id,
1466 l_wip_cost_txn_rec.program_update_date,
1467 l_wip_cost_txn_rec.process_phase,
1468 l_wip_cost_txn_rec.process_status,
1469 l_wip_cost_txn_rec.source_code,
1470 l_wip_cost_txn_rec.organization_id,
1471 l_wip_cost_txn_rec.organization_code,
1472 l_wip_cost_txn_rec.wip_entity_id,
1473 l_wip_cost_txn_rec.wip_entity_name,
1474 l_wip_cost_txn_rec.entity_type,
1475 l_wip_cost_txn_rec.project_id,
1476 l_wip_cost_txn_rec.task_id,
1477 l_wip_cost_txn_rec.operation_seq_num,
1478 l_wip_cost_txn_rec.resource_seq_num,
1479 l_wip_cost_txn_rec.resource_id,
1480 l_wip_cost_txn_rec.transaction_type,
1481 l_wip_cost_txn_rec.transaction_date,
1482 l_wip_cost_txn_rec.transaction_quantity,
1483 l_wip_cost_txn_rec.transaction_uom,
1484 l_wip_cost_txn_rec.employee_id,
1485 l_wip_cost_txn_rec.employee_num,
1486 l_wip_cost_txn_rec.department_id,
1487 l_wip_cost_txn_rec.activity_id,
1488 l_wip_cost_txn_rec.activity_name,
1489 l_wip_cost_txn_rec.reason_id,
1490 l_wip_cost_txn_rec.reason_name,
1491 l_wip_cost_txn_rec.reference,
1492 l_wip_cost_txn_rec.instance_id,
1493 l_wip_cost_txn_rec.charge_department_id);
1494 -- End of changes for bug # 6326254.
1495 EXCEPTION
1496 WHEN OTHERS THEN
1497 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_RES_TXN_INSERT_ERR');
1498 FND_MSG_PUB.ADD;
1499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1500 RAISE;
1501 END;
1502 END LOOP;
1503
1504 IF FND_API.TO_BOOLEAN(p_commit) THEN
1505 COMMIT;
1506 END IF;
1507 IF G_DEBUG='Y' THEN
1508 AHL_DEBUG_PUB.debug('End private API: AHL_WIP_JOB_PVT.INSERT_RESOURCE_TXN');
1509 AHL_DEBUG_PUB.disable_debug;
1510 END IF;
1511
1512 EXCEPTION
1513 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1514 ROLLBACK TO INSERT_RESOURCE_TXN_PVT;
1518 p_data => x_msg_data);
1515 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1516 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1517 p_count => x_msg_count,
1519 IF G_DEBUG='Y' THEN
1520 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
1521 'UNEXPECTED ERROR IN PRIVATE:' );
1522 AHL_DEBUG_PUB.debug('AHL_WIP_JOB_PVT.INSERT_RESOURCE_TXN');
1523 AHL_DEBUG_PUB.disable_debug;
1524 END IF;
1525 WHEN FND_API.G_EXC_ERROR THEN
1526 ROLLBACK TO INSERT_RESOURCE_TXN_PVT;
1527 x_return_status := FND_API.G_RET_STS_ERROR;
1528 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1529 p_count => x_msg_count,
1530 p_data => x_msg_data);
1531 IF G_DEBUG='Y' THEN
1532 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
1533 'ERROR IN PRIVATE:' );
1534 AHL_DEBUG_PUB.debug('AHL_WIP_JOB_PVT.INSERT_RESOURCE_TXN');
1535 AHL_DEBUG_PUB.disable_debug;
1536 END IF;
1537 WHEN OTHERS THEN
1538 ROLLBACK TO INSERT_RESOURCE_TXN_PVT;
1539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1540 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1541 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_WIP_JOB_PVT',
1542 p_procedure_name => 'INSERT_RESOURCE_TXN');
1543 END IF;
1544 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1545 p_count => x_msg_count,
1546 p_data => x_msg_data);
1547 IF G_DEBUG='Y' THEN
1548 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data,
1549 'OTHER ERROR IN PRIVATE:' );
1550 AHL_DEBUG_PUB.debug('AHL_WIP_JOB_PVT.INSERT_RESOURCE_TXN');
1551 AHL_DEBUG_PUB.disable_debug;
1552 END IF;
1553
1554 END insert_resource_txn;
1555
1556 -- Define function wip_mass_load_pending --
1557 FUNCTION wip_massload_pending(
1558 p_wip_entity_id IN NUMBER
1559 ) RETURN BOOLEAN IS
1560 CURSOR get_job_name(c_wip_entity_id NUMBER) IS
1561 SELECT wip_entity_name, organization_id
1562 FROM wip_entities
1563 WHERE wip_entity_id = c_wip_entity_id;
1564 l_job_name get_job_name%ROWTYPE;
1565 CURSOR get_pending_job(c_wip_entity_name VARCHAR2, c_organization_id NUMBER) IS
1566 SELECT 'X'
1567 FROM wip_job_schedule_interface
1568 WHERE job_name = c_wip_entity_name
1569 AND organization_id = c_organization_id
1570 AND (process_status not in (3, 4, 5)); --The successfully loaded records
1571 --or failed records still remain in job header interface table if in debug mode.
1572 l_dummy VARCHAR2(1);
1573
1574 BEGIN
1575 OPEN get_job_name (p_wip_entity_id);
1576 FETCH get_job_name INTO l_job_name;
1577 IF get_job_name%NOTFOUND THEN
1578 CLOSE get_job_name;
1579 RETURN FALSE;
1580 ELSE
1581 OPEN get_pending_job(l_job_name.wip_entity_name, l_job_name.organization_id);
1582 FETCH get_pending_job INTO l_dummy;
1583 IF get_pending_job%FOUND THEN
1584 CLOSE get_pending_job;
1585 FND_MESSAGE.set_name('AHL','AHL_COM_WIP_LOAD_PENDING');
1586 FND_MSG_PUB.add;
1587 RETURN TRUE;
1588 ELSE
1589 CLOSE get_pending_job;
1590 RETURN FALSE;
1591 END IF;
1592 END IF;
1593 END wip_massload_pending;
1594
1595 PROCEDURE load_wip_batch_jobs(
1596 p_api_version IN NUMBER,
1597 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1598 -- p_commit IN VARCHAR2 := FND_API.G_FALSE,
1599 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1600 x_return_status OUT NOCOPY VARCHAR2,
1601 x_msg_count OUT NOCOPY NUMBER,
1602 x_msg_data OUT NOCOPY VARCHAR2,
1603 p_group_id IN NUMBER,
1604 p_header_id IN NUMBER,
1605 p_submit_flag IN VARCHAR2,
1606 p_ahl_wo_rec IN ahl_wo_rec_type,
1607 p_ahl_wo_op_tbl IN ahl_wo_op_tbl_type,
1608 p_ahl_wo_res_tbl IN ahl_wo_res_tbl_type,
1609 p_ahl_wo_mtl_tbl IN ahl_wo_mtl_tbl_type,
1610 x_group_id OUT NOCOPY NUMBER,
1611 x_header_id OUT NOCOPY NUMBER,
1612 x_ahl_wip_job_tbl OUT NOCOPY ahl_wip_job_tbl_type
1613 ) IS
1614 PRAGMA AUTONOMOUS_TRANSACTION;
1615 --This API is Autonomous Transaction. We have to explicitly commit or rollback the
1616 --transactions it or its called procedure contains when it exits. This autonomous
1617 --transaction doesn't affect the main transaction in its calling API.
1618
1619 i NUMBER;
1620 j NUMBER;
1621 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_WIP_BATCH_JOBS';
1622 l_api_version CONSTANT NUMBER := 1.0;
1623 l_msg_count NUMBER;
1624 l_return_status VARCHAR2(1);
1625 l_group_id NUMBER;
1626 l_header_id NUMBER;
1627
1628 l_interface1_id NUMBER;
1629 l_error_msg VARCHAR2(5000);
1630 l_interface_ids num_array_type;
1631 l_commit_flag NUMBER;
1632
1633 CURSOR get_job_attr(c_group_id NUMBER, c_header_id NUMBER) IS
1634 SELECT group_id, header_id, interface_id, process_phase, process_status,
1638 AND header_id = c_header_id;
1635 wip_entity_id, job_name, organization_id
1636 FROM wip_job_schedule_interface
1637 WHERE group_id = c_group_id
1639 l_get_job_attr get_job_attr%ROWTYPE;
1640
1641 BEGIN
1642 --SAVEPOINT LOAD_WIP_JOB_PVT;
1643 --Savepoint here is not necessary, because we have a commit statement in its called
1644 --procedure submit_wip_load and it will make this savepoint invalid.
1645 IF G_DEBUG='Y' THEN
1646 AHL_DEBUG_PUB.enable_debug;
1647 AHL_DEBUG_PUB.debug('Begin private API: AHL_WIP_JOB_PVT.LOAD_WIP_BATCH_JOBS');
1648 END IF;
1649 IF FND_API.to_boolean(p_init_msg_list) THEN
1650 FND_MSG_PUB.initialize;
1651 END IF;
1652
1653 x_return_status := FND_API.G_RET_STS_SUCCESS;
1654
1655 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version,
1656 l_api_name, G_PKG_NAME)
1657 THEN
1658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1659 END IF;
1660
1661 IF G_DEBUG='Y' THEN
1662 AHL_DEBUG_PUB.debug('Inside: load_wip_batch_jobs, just before calling insert_job_header');
1663 --dbms_output.put_line('Inside: load_wip_batch_jobs, just before calling insert_job_header');
1664 END IF;
1665 --insert job header first
1666 insert_job_header( p_ahl_wo_rec => p_ahl_wo_rec,
1667 p_group_id => p_group_id,
1668 p_header_id => p_header_id,
1669 x_group_id => l_group_id,
1670 x_header_id => l_header_id,
1671 x_return_status => l_return_status );
1672 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1674 END IF;
1675
1676 IF G_DEBUG='Y' THEN
1677 AHL_DEBUG_PUB.debug('Inside: load_wip_batch_jobs, just before calling insert_job_operation');
1678 --dbms_output.put_line('Inside: load_wip_batch_jobs, just before calling insert_job_operation');
1679 END IF;
1680 --insert job operations if they are available
1681 IF p_ahl_wo_op_tbl.count > 0 THEN
1682 FOR i IN p_ahl_wo_op_tbl.FIRST..p_ahl_wo_op_tbl.LAST LOOP
1683 insert_job_operation( p_ahl_wo_op_rec => p_ahl_wo_op_tbl(i),
1684 p_group_id => l_group_id,
1685 p_parent_header_id => l_header_id,
1686 x_return_status => l_return_status );
1687 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1688 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1689 END IF;
1690 END LOOP;
1691 END IF;
1692
1693 IF G_DEBUG='Y' THEN
1694 AHL_DEBUG_PUB.debug('Inside: load_wip_batch_jobs, just before calling insert_job_resource');
1695 --dbms_output.put_line('Inside: load_wip_batch_jobs, just before calling insert_job_resource');
1696 END IF;
1697 --insert resource requirements if they are available
1698 IF p_ahl_wo_res_tbl.count > 0 THEN
1699 FOR i IN p_ahl_wo_res_tbl.FIRST..p_ahl_wo_res_tbl.LAST LOOP
1700 insert_job_resource( p_ahl_wo_res_rec => p_ahl_wo_res_tbl(i),
1701 p_group_id => l_group_id,
1702 p_parent_header_id => l_header_id,
1703 x_return_status => l_return_status );
1704 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1705 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1706 END IF;
1707 END LOOP;
1708 END IF;
1709
1710 IF G_DEBUG='Y' THEN
1711 AHL_DEBUG_PUB.debug('Inside: load_wip_batch_jobs, just before calling insert_job_material');
1712 --dbms_output.put_line('Inside: load_wip_batch_jobs, just before calling insert_job_material');
1713 END IF;
1714 --insert material requirements if they are available
1715 IF p_ahl_wo_mtl_tbl.count > 0 THEN
1716 FOR i IN p_ahl_wo_mtl_tbl.FIRST..p_ahl_wo_mtl_tbl.LAST LOOP
1717 insert_job_material( p_ahl_wo_mtl_rec => p_ahl_wo_mtl_tbl(i),
1718 p_group_id => l_group_id,
1719 p_parent_header_id => l_header_id,
1720 x_return_status => l_return_status );
1721 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1723 END IF;
1724 END LOOP;
1725 END IF;
1726
1727 IF G_DEBUG='Y' THEN
1728 AHL_DEBUG_PUB.debug('Inside: load_wip_batch_jobs, just before calling submit_wip_load');
1729 --dbms_output.put_line('Inside: load_wip_batch_jobs, just before calling submit_wip_load');
1730 END IF;
1731 --submit WIP Mass Load
1732
1733 x_header_id := l_header_id;
1734 x_group_id := l_group_id;
1735 IF (p_submit_flag = 'Y') THEN
1736 -- Before submitting request, save the job_name and organization_id for all the jobs
1737 -- in this group.
1738 FOR i IN 0..(l_header_id - l_group_id) LOOP
1739 OPEN get_job_attr(l_group_id, l_group_id + i);
1740 FETCH get_job_attr INTO l_get_job_attr;
1741 IF get_job_attr%NOTFOUND THEN
1742 CLOSE get_job_attr;
1743 FND_MESSAGE.set_name('AHL','AHL_WIP_ITF_REC_NOT_FOUND');
1744 FND_MSG_PUB.add;
1745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1746 END IF;
1747 x_ahl_wip_job_tbl(i+1).wip_entity_name := l_get_job_attr.job_name;
1748 x_ahl_wip_job_tbl(i+1).organization_id := l_get_job_attr.organization_id;
1749 CLOSE get_job_attr;
1750 END LOOP;
1751
1752 submit_wip_load( p_group_id => l_group_id,
1756 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1753 x_return_status => l_return_status);
1754
1755 -- Check whether wait_for_request fails
1757 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1758 END IF;
1759
1760 -- if wait_for_request returns normally
1761 FOR i IN 0..(l_header_id - l_group_id) LOOP
1762 OPEN get_job_attr(l_group_id, l_group_id + i);
1763 FETCH get_job_attr INTO l_get_job_attr;
1764
1765 IF (get_job_attr%NOTFOUND OR
1766 (l_get_job_attr.process_phase = 4 AND l_get_job_attr.process_status = 4)) THEN
1767 OPEN get_wip_entity(x_ahl_wip_job_tbl(i+1).wip_entity_name,
1768 x_ahl_wip_job_tbl(i+1).organization_id);
1769 FETCH get_wip_entity INTO x_ahl_wip_job_tbl(i+1).wip_entity_id;
1770 IF get_wip_entity%NOTFOUND THEN
1771 CLOSE get_wip_entity;
1772 CLOSE get_job_attr;
1773 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_NO_WIP_ENTITY_ID');
1774 FND_MSG_PUB.ADD;
1775 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1776 ELSE
1777 CLOSE get_wip_entity;
1778 END IF;
1779 x_ahl_wip_job_tbl(i+1).error := NULL;
1780 CLOSE get_job_attr;
1781
1782 /* This is for creating job only, so load_type = 7
1783 --call EAM API to update manual_rebuild_flag because WICMLP can't change it.
1784 IF (x_ahl_wip_job_tbl(i+1).load_type = 8 AND x_ahl_wip_job_tbl(i+1).manual_rebuild_flag IS NOT NULL) THEN
1785 eam_workordertransactions_pub.set_manual_reb_flag(
1786 p_wip_entity_id => x_ahl_wip_job_tbl(i+1).wip_entity_id,
1787 p_organization_id => x_ahl_wip_job_tbl(i+1).organization_id,
1788 p_manual_rebuild_flag => x_ahl_wip_job_tbl(i+1).manual_rebuild_flag,
1789 x_return_status => l_return_status);
1790 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1791 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_EAM_REBUILD_FLAG_FAIL');
1792 FND_MSG_PUB.ADD;
1793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1794 END IF;
1795 END IF;
1796 --call EAM API to update owning_department_id because WICMLP can't change it.
1797 IF (x_ahl_wip_job_tbl(i+1).load_type = 8 AND x_ahl_wip_job_tbl(i+1).owning_department IS NOT NULL) THEN
1798 eam_workordertransactions_pub.set_owning_department(
1799 p_wip_entity_id => x_ahl_wip_job_tbl(i+1).wip_entity_id,
1800 p_organization_id => x_ahl_wip_job_tbl(i+1).organization_id,
1801 p_owning_department => x_ahl_wip_job_tbl(i+1).owning_department,
1802 x_return_status => l_return_status);
1803 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1804 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_EAM_OWN_DEPT_FAIL');
1805 FND_MSG_PUB.ADD;
1806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1807 END IF;
1808 END IF;
1809 */
1810 ELSE
1811 CLOSE get_job_attr;
1812 l_interface_ids(1) := l_get_job_attr.interface_id;
1813 j := 2;
1814 FOR l_get_interface_ids IN get_interface_ids(
1815 l_get_job_attr.group_id, l_get_job_attr.header_id) LOOP
1816 l_interface_ids(j) := l_get_interface_ids.interface_id ;
1817 j := j + 1;
1818 END LOOP;
1819
1820 l_error_msg := '';
1821 FOR j IN l_interface_ids.FIRST..l_interface_ids.LAST LOOP
1822 FOR l_get_error_msg IN get_error_msg(l_interface_ids(j)) LOOP
1823 --l_error_msg := l_error_msg || replace(l_get_error_msg.error,chr(10)||chr(10),chr(10));
1824 --chr(10)='\n', chr(13)='\r', chr() function won't pass GSCC standard
1825 l_error_msg := l_error_msg || l_get_error_msg.error;
1826 END LOOP;
1827 END LOOP;
1828 x_ahl_wip_job_tbl(i+1).wip_entity_id := NULL;
1829 x_ahl_wip_job_tbl(i+1).error := l_error_msg;
1830
1831 --Keep the failed records in interface tables in debug mode
1832 IF fnd_profile.value('MRP_DEBUG') <> 'Y' THEN
1833 FOR j IN l_interface_ids.FIRST..l_interface_ids.LAST LOOP
1834 DELETE FROM wip_interface_errors
1835 WHERE interface_id = l_interface_ids(j);
1836 END LOOP;
1837
1838 DELETE FROM wip_job_dtls_interface
1839 WHERE group_id = l_get_job_attr.group_id
1840 AND parent_header_id = l_get_job_attr.header_id;
1841
1842 DELETE FROM wip_job_schedule_interface
1843 WHERE group_id = l_get_job_attr.group_id
1844 AND header_id = l_get_job_attr.header_id;
1845 END IF;
1846
1847 /*
1848 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WICMLP_ERROR');
1849 FND_MESSAGE.SET_TOKEN('ERROR',l_error_msg);
1850 FND_MSG_PUB.ADD;
1851 */
1852 x_msg_count := 1;
1853 x_msg_data := l_error_msg;
1854 x_return_status := FND_API.G_RET_STS_ERROR;
1855
1856 IF G_DEBUG='Y' THEN
1857 AHL_DEBUG_PUB.debug('The concatenated error message is '||x_msg_data||' '||i);
1858 --dbms_output.put_line(substr('The concatenated error message is '||x_msg_data||' '||i, 1, 255));
1859 END IF;
1860 END IF;
1861 END LOOP;
1862 END IF;
1863
1864 COMMIT; --Autonomous Transaction Required
1865
1866 IF G_DEBUG='Y' THEN
1867 AHL_DEBUG_PUB.debug('End private API: AHL_WIP_JOB_PVT.LOAD_WIP_BATCH_JOBS');
1868 AHL_DEBUG_PUB.disable_debug;
1869 END IF;
1870 EXCEPTION
1871 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1872 ROLLBACK;
1873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1874 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1875 p_count => x_msg_count,
1876 p_data => x_msg_data);
1877 IF G_DEBUG='Y' THEN
1878 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
1879 'UNEXPECTED ERROR IN PRIVATE:' );
1880 AHL_DEBUG_PUB.debug('AHL_WIP_JOB_PVT.LOAD_WIP_BATCH_JOBS');
1881 AHL_DEBUG_PUB.disable_debug;
1882 END IF;
1883 WHEN FND_API.G_EXC_ERROR THEN
1884 ROLLBACK;
1885 x_return_status := FND_API.G_RET_STS_ERROR;
1886 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1887 p_count => x_msg_count,
1888 p_data => x_msg_data);
1889 IF G_DEBUG='Y' THEN
1890 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data,
1891 'ERROR IN PRIVATE:' );
1892 AHL_DEBUG_PUB.debug('AHL_WIP_JOB_PVT.LOAD_WIP_BATCH_JOBS');
1893 AHL_DEBUG_PUB.disable_debug;
1894 END IF;
1895 WHEN OTHERS THEN
1896 ROLLBACK;
1897 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1898 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1899 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_WIP_JOB_PVT',
1900 p_procedure_name => 'LOAD_WIP_BATCH_JOBS');
1901 END IF;
1902 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1903 p_count => x_msg_count,
1904 p_data => x_msg_data);
1905 IF G_DEBUG='Y' THEN
1906 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data,
1907 'OTHER ERROR IN PRIVATE:' );
1908 AHL_DEBUG_PUB.debug('AHL_WIP_JOB_PVT.LOAD_WIP_BATCH_JOBS');
1909 AHL_DEBUG_PUB.disable_debug;
1910 END IF;
1911
1912 END load_wip_batch_jobs;
1913
1914 END AHL_WIP_JOB_PVT; -- Package Body