1 package body wip_massload_pub as
2 /* $Header: wipmlppb.pls 120.7.12010000.2 2008/08/15 00:54:36 kboonyap ship $ */
3
4 g_pkgName constant varchar2(30) := 'wip_massload_pub';
5 g_wipMLError constant number := 1;
6 g_wipMLWarning constant number := 2;
7
8 procedure processSerStartOp(p_rowid in rowid,
9 p_interfaceID in number,
10 x_returnStatus out nocopy varchar2,
11 x_errorMsg out nocopy varchar2);
12
13 --
14 -- Currently, this API does not support the creation of repetitive schedule.
15 --
16 procedure massLoadJobs(p_groupID in number,
17 p_validationLevel in number,
18 p_commitFlag in number,
19 x_returnStatus out nocopy varchar2,
20 x_errorMsg out nocopy varchar2) is
21 l_params wip_logger.param_tbl_t;
22 l_procName varchar2(30) := 'massLoadJobs';
23 l_logLevel number := to_number(fnd_log.g_current_runtime_level);
24 l_retStatus varchar2(1);
25 l_totalNum number;
26 l_errorCode varchar2(240);
27 l_errRecCount number := 0;
28 l_requestCount number;
29 l_wdj_old_status_type NUMBER;/*Added for bug 6641029*/
30 l_wjsi_new_status_type NUMBER;/*Added for bug 6641029*/
31 l_wdj_wip_entity_id NUMBER;/*Added for bug 6641029*/
32 l_wdj_organization_id NUMBER;/*Added for bug 6641029*/
33
34 cursor c_allrows is
35 select 1
36 from wip_job_schedule_interface
37 where group_id = p_groupID
38 and process_status = wip_constants.pending
39 and process_phase = wip_constants.ml_validation
40 and load_type <> wip_constants.create_sched
41 for update nowait;
42
43 cursor c_wjsi is
44 select rowid,
45 header_id,
46 interface_id
47 from wip_job_schedule_interface
48 where group_id = p_groupID
49 and process_phase = wip_constants.ml_validation
50 and process_status in (wip_constants.running, wip_constants.warning)
51 and load_type <> wip_constants.create_sched;
52 begin
53 x_returnStatus := fnd_api.g_ret_sts_success;
54 if (l_logLevel <= wip_constants.trace_logging) then
55 l_params(1).paramName := 'p_groupID';
56 l_params(1).paramValue := p_groupID;
57 l_params(2).paramName := 'p_validationLevel';
58 l_params(2).paramValue := p_validationLevel;
59 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
60 p_params => l_params,
61 x_returnStatus => x_returnStatus);
62 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
63 raise fnd_api.g_exc_unexpected_error;
64 end if;
65 end if;
66
67 -- print the no.of discrete requests to log
68
69 select count(*) into l_requestCount
70 from wip_job_schedule_interface
71 where group_id = p_groupID
72 and process_status = wip_constants.pending
73 and process_phase = wip_constants.ml_validation
74 and load_type <> wip_constants.create_sched;
75
76 if (l_logLevel <= wip_constants.trace_logging) then
77 wip_logger.log(l_requestCount || ' pending request(s) found for group_id' || p_groupID || ' for discrete processing', l_retStatus);
78 end if;
79
80 -- lock all the records
81 open c_allrows;
82 if ( c_allrows%isopen ) then
83 close c_allrows;
84 end if;
85
86 -- assign interface_id and set process_status to running
87 update wip_job_schedule_interface
88 set interface_id = wip_interface_s.nextval,
89 process_status = wip_constants.running
90 where group_id = p_groupID
91 and process_status = wip_constants.pending
92 and process_phase = wip_constants.ml_validation
93 and load_type <> wip_constants.create_sched;
94
95 if (p_commitFlag <> 0) then
96 commit;
97 end if;
98 --
99 -- ?? here, we should assign a different group_id for those records with load type being
100 -- create repetitive schedule
101 --
102
103 -- do the validation for those records
104 wip_validateMLHeader_pvt.validateMLHeader(p_groupID => p_groupID,
105 p_validationLevel => p_validationLevel,
106 x_returnStatus => x_returnStatus,
107 x_errorMsg => x_errorMsg);
108 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
109 raise fnd_api.g_exc_unexpected_error;
110 end if;
111
112 -- process every row which pass validation
113 for wjsi_rec in c_wjsi loop
114 begin
115 savepoint wip_massload_start;
116
117 wip_massload_pvt.processWJSI(wjsi_rec.rowid, l_retStatus, x_errorMsg);
118 if ( l_retStatus <> fnd_api.g_ret_sts_success ) then
119 raise fnd_api.g_exc_unexpected_error;
120 end if;
121
122 select count(*)
123 into l_totalNum
124 from wip_job_schedule_interface wjsi,
125 wip_job_dtls_interface wjdi
126 where wjdi.parent_header_id = wjsi.header_id
127 and wjdi.group_id = wjsi.group_id
128 and wjdi.process_phase = wip_constants.ml_validation
129 and wjdi.process_status = wip_constants.pending
130 and wjsi.rowid = wjsi_rec.rowid
131 and wjsi.load_type in (wip_constants.create_job,
132 wip_constants.create_ns_job,
133 wip_constants.resched_job);
134
135 if (l_logLevel <= wip_constants.trace_logging) then
136 wip_logger.log('There are ' || l_totalNum || ' detail records....', l_retStatus);
137 end if;
138
139 if ( l_totalNum > 0 ) then
140 wip_job_details.load_all_details(p_group_id => p_groupID,
141 p_parent_header_id => wjsi_rec.header_id,
142 p_std_alone => 0,
143 x_err_code => l_errorCode,
144 x_err_msg => x_errorMsg,
145 x_return_status => l_retStatus);
146 if ( l_retStatus <> fnd_api.g_ret_sts_success ) then
147 raise fnd_api.g_exc_unexpected_error;
148 end if;
149 end if;
150
151 -- only here an we do validation for serialization_start_op because it depends on the routing
152 -- explosion and detail loading
153 processSerStartOp(wjsi_rec.rowid,
154 wjsi_rec.interface_id,
155 l_retStatus,
156 x_errorMsg);
157 if ( l_retStatus <> fnd_api.g_ret_sts_success ) then
158 raise fnd_api.g_exc_unexpected_error;
159 end if;
160 /*6641029 Start- Added code to create requisition*/
161 select wdj.status_type,
162 wjsi.status_type,
163 wdj.wip_entity_id,
164 wdj.organization_id
165 into l_wdj_old_status_type,
166 l_wjsi_new_status_type,
167 l_wdj_wip_entity_id,
168 l_wdj_organization_id
169 from wip_discrete_jobs wdj,
170 wip_job_schedule_interface wjsi
171 where wjsi.rowid = wjsi_rec.rowid
172 and wdj.wip_entity_id = wjsi.wip_entity_id
173 and wdj.organization_id = wjsi.organization_id
174 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
175 WIP_CONSTANTS.CREATE_NS_JOB,
176 WIP_CONSTANTS.CREATE_EAM_JOB,
177 WIP_CONSTANTS.RESCHED_EAM_JOB,
178 WIP_CONSTANTS.RESCHED_JOB) ;
179 if (l_wjsi_new_status_type IN (WIP_CONSTANTS.RELEASED,WIP_CONSTANTS.HOLD)) then
180 if((wip_osp.po_req_created( l_wdj_wip_entity_id,
181 null,
182 l_wdj_organization_id,
183 null,
184 WIP_CONSTANTS.DISCRETE
185 ) = FALSE) or l_wdj_old_status_type in (WIP_CONSTANTS.UNRELEASED)) then
186 wip_osp.release_validation(l_wdj_wip_entity_id,
187 l_wdj_organization_id,
188 NULL) ;
189 end if ;
190 end if ;
191 /*6641029 End*/
192 exception
193 when fnd_api.g_exc_unexpected_error then
194 rollback to wip_massload_start;
195 wip_validateMLHeader_pvt.setInterfaceError(wjsi_rec.rowid,
196 wjsi_rec.interface_id,
197 x_errorMsg,
198 g_wipMLError);
199 if (l_logLevel <= wip_constants.trace_logging) then
200 wip_logger.log('interface ' || wjsi_rec.interface_id || ' failed: ' || x_errorMsg, l_retStatus);
201 end if;
202 end;
203 end loop;
204
205 update wip_job_schedule_interface
206 set process_status = wip_constants.completed,
207 process_phase = wip_constants.ml_complete
208 where group_id = p_groupID
209 and process_status in (wip_constants.running, wip_constants.warning)
210 and process_phase = wip_constants.ml_validation
211 and load_type <> wip_constants.create_sched;
212
213 if (p_commitFlag <> 0) then
214 commit;
215 end if;
216
217 select count(*) into l_errRecCount
218 from wip_job_schedule_interface
219 where group_id = p_groupID
220 and process_status = wip_constants.error;
221
222 if(l_errRecCount > 0) then
223 x_returnStatus := fnd_api.g_ret_sts_error;
224 else
225 x_returnStatus := fnd_api.g_ret_sts_success;
226 end if;
227
228 if (l_logLevel <= wip_constants.trace_logging) then
229 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
230 p_procReturnStatus => x_returnStatus,
231 p_msg => 'success',
232 x_returnStatus => l_retStatus);
233 end if;
234 exception
235 when wip_constants.records_locked then
236 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
237 if (l_logLevel <= wip_constants.trace_logging) then
238 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
239 p_procReturnStatus => x_returnStatus,
240 p_msg => 'records locked',
241 x_returnStatus => l_retStatus);
242 end if;
243 fnd_message.set_name('WIP', 'WIP_LOCKED_ROW_ALREADY_LOCKED');
244 x_errorMsg := fnd_message.get;
245 when fnd_api.g_exc_unexpected_error then
246 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
247 if (l_logLevel <= wip_constants.trace_logging) then
248 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
249 p_procReturnStatus => x_returnStatus,
250 p_msg => x_errorMsg,
251 x_returnStatus => l_retStatus);
252 end if;
253 end massLoadJobs;
254
255
256 -- this API is used to create one job for the given interface id. Please note that there should be no
257 -- other records under the same group id as the given interface id. This API will fail that case.
258 -- also, the load type for this record must be create standard or non-std job.
259 procedure createOneJob(p_interfaceID in number,
260 p_validationLevel in number,
261 x_wipEntityID out nocopy number,
262 x_returnStatus out nocopy varchar2,
263 x_errorMsg out nocopy varchar2) is
264 l_params wip_logger.param_tbl_t;
265 l_procName varchar2(30) := 'createOneJob';
266 l_logLevel number := to_number(fnd_log.g_current_runtime_level);
267 l_retStatus varchar2(1);
268 l_totalNum number;
269 l_groupID number;
270 l_headerID number;
271 l_rowid rowid;
272 l_errorCode varchar2(240);
273
274 cursor c_allrows is
275 select 1
276 from wip_job_schedule_interface
277 where interface_id = p_interfaceID
278 for update nowait;
279 begin
280 x_returnStatus := fnd_api.g_ret_sts_success;
281 if (l_logLevel <= wip_constants.trace_logging) then
282 l_params(1).paramName := 'p_interfaceID';
283 l_params(1).paramValue := p_interfaceID;
284 l_params(2).paramName := 'p_validationLevel';
285 l_params(2).paramValue := p_validationLevel;
286 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
287 p_params => l_params,
288 x_returnStatus => x_returnStatus);
289 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
290 raise fnd_api.g_exc_unexpected_error;
291 end if;
292 end if;
293
294 select rowid, group_id, header_id
295 into l_rowid, l_groupID, l_headerID
296 from wip_job_schedule_interface
297 where interface_id = p_interfaceID;
298
299 select count(*)
300 into l_totalNum
301 from wip_job_schedule_interface
302 where interface_id = p_interfaceID
303 and process_status = wip_constants.pending
304 and process_phase = wip_constants.ml_validation
305 and load_type in (wip_constants.create_job, wip_constants.create_ns_job);
306
307 if ( l_totalNum <> 1 ) then
308 fnd_message.set_name('WIP', 'WIP_WJSI_ONE_ROW');
309 x_errorMsg := fnd_message.get;
310 raise fnd_api.g_exc_unexpected_error;
311 end if;
312
313 select count(*)
314 into l_totalNum
315 from wip_job_schedule_interface wjsi
316 where wjsi.group_id = l_groupID;
317
318 if ( l_totalNum <> 1 ) then
319 fnd_message.set_name('WIP', 'WIP_WJSI_ONE_ROW');
320 x_errorMsg := fnd_message.get;
321 raise fnd_api.g_exc_unexpected_error;
322 end if;
323
324 -- lock all the records
325 open c_allrows;
326 if ( c_allrows%isopen ) then
327 close c_allrows;
328 end if;
329
330 update wip_job_schedule_interface
331 set process_status = wip_constants.running
332 where interface_id = p_interfaceID;
333
334 -- do the validation for those records
335 wip_validateMLHeader_pvt.validateMLHeader(p_groupID => l_groupID,
336 p_validationLevel => p_validationLevel,
337 x_returnStatus => x_returnStatus,
338 x_errorMsg => x_errorMsg);
339 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
340 raise fnd_api.g_exc_unexpected_error;
341 end if;
342
343 select count(*)
344 into l_totalNum
345 from wip_job_schedule_interface
346 where interface_id = p_interfaceID
347 and process_status in (wip_constants.running, wip_constants.warning);
348
349 if ( l_totalNum <> 1 ) then
350 fnd_message.set_name('WIP', 'WIP_WJSI_VAL_FAILED');
351 x_errorMsg := fnd_message.get;
352 raise fnd_api.g_exc_unexpected_error;
353 end if;
354
355 wip_massload_pvt.processWJSI(l_rowid, x_returnStatus, x_errorMsg);
356 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
357 raise fnd_api.g_exc_unexpected_error;
358 end if;
359
360 select count(*)
361 into l_totalNum
362 from wip_job_schedule_interface wjsi,
363 wip_job_dtls_interface wjdi
364 where wjdi.parent_header_id = wjsi.header_id
365 and wjdi.group_id = wjsi.group_id
366 and wjdi.process_phase = wip_constants.ml_validation
367 and wjdi.process_status = wip_constants.pending
368 and wjsi.rowid = l_rowid;
369
370 if (l_logLevel <= wip_constants.trace_logging) then
371 wip_logger.log('There are ' || l_totalNum || ' detail records....', l_retStatus);
372 end if;
373
374 if ( l_totalNum > 0 ) then
375 wip_job_details.load_all_details(p_group_id => l_groupID,
376 p_parent_header_id => l_headerID,
377 p_std_alone => 0,
378 x_err_code => l_errorCode,
379 x_err_msg => x_errorMsg,
380 x_return_status => l_retStatus);
381 if ( l_retStatus <> fnd_api.g_ret_sts_success ) then
382 raise fnd_api.g_exc_unexpected_error;
383 end if;
384 end if;
385
386 -- only here an we do validation for serialization_start_op because it depends on the routing
387 -- explosion and detail loading
388 processSerStartOp(l_rowid,
389 p_interfaceID,
390 l_retStatus,
391 x_errorMsg);
392 if ( l_retStatus <> fnd_api.g_ret_sts_success ) then
393 raise fnd_api.g_exc_unexpected_error;
394 end if;
395
396 select wip_entity_id
397 into x_wipEntityID
398 from wip_job_schedule_interface
399 where rowid = l_rowid;
400
401 update wip_job_schedule_interface
402 set process_status = wip_constants.completed
403 where rowid = l_rowid;
404
405 if (l_logLevel <= wip_constants.trace_logging) then
406 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
407 p_procReturnStatus => x_returnStatus,
408 p_msg => 'success',
409 x_returnStatus => l_retStatus);
410 end if;
411 exception
412 when wip_constants.records_locked then
413 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
414 fnd_message.set_name('WIP', 'WIP_LOCKED_ROW_ALREADY_LOCKED');
415 x_errorMsg := fnd_message.get;
416 wip_validateMLHeader_pvt.setInterfaceError(l_rowid,
417 p_interfaceID,
418 x_errorMsg,
419 g_wipMLError);
420 if (l_logLevel <= wip_constants.trace_logging) then
421 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
422 p_procReturnStatus => x_returnStatus,
423 p_msg => 'records locked',
424 x_returnStatus => l_retStatus);
425 end if;
426 when fnd_api.g_exc_unexpected_error then
427 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
428 wip_validateMLHeader_pvt.setInterfaceError(l_rowid,
429 p_interfaceID,
430 x_errorMsg,
431 g_wipMLError);
432 if (l_logLevel <= wip_constants.trace_logging) then
433 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
434 p_procReturnStatus => x_returnStatus,
435 p_msg => x_errorMsg,
436 x_returnStatus => l_retStatus);
437 end if;
438 end createOneJob;
439
440
441 procedure processSerStartOp(p_rowid in rowid,
442 p_interfaceID in number,
443 x_returnStatus out nocopy varchar2,
444 x_errorMsg out nocopy varchar2) is
445 l_msg varchar2(80);
446 l_dummy number;
447
448 l_wjsiSerOp number;
449 l_wdjSerOp number;
450 l_wipEntityID number;
451 l_loadType number;
452 l_primaryItemID number;
453 l_orgID number;
454
455 l_curOpSeq number;
456 l_rtgExists boolean := false;
457 l_opFound boolean := false;
458
459 cursor c_ops(v_wipEntityID number) is
460 select operation_seq_num
461 from wip_operations
462 where wip_entity_id = v_wipEntityID;
463
464 begin
465 x_returnStatus := fnd_api.g_ret_sts_success;
466 l_msg := 'WIP_ML_SERIAL_START_OP';
467
468 select wip_entity_id,
469 serialization_start_op,
470 load_type,
471 primary_item_id,
472 organization_id
473 into l_wipEntityID,
474 l_wjsiSerOp,
475 l_loadType,
476 l_primaryItemID,
477 l_orgID
478 from wip_job_schedule_interface
479 where rowid = p_rowid;
480
481 if ( l_wjsiSerOp is null and l_loadType = wip_constants.resched_job ) then
482 -- due to re-exploding, we may need to clear out the serialization start op
483 update wip_discrete_jobs wdj
484 set serialization_start_op = null
485 where wip_entity_id = l_wipEntityID
486 and serialization_start_op <> 1
487 and not exists(select 1
488 from wip_operations wo
489 where wo.wip_entity_id = wdj.wip_entity_id
490 and wo.operation_seq_num = wdj.serialization_start_op);
491 return;
492 end if;
493
494 -- when the flow comes here, regardless reschedule or job creation, we already updated wdj
495 -- with the right value for serialization_start_op from the interface table or the defaulting
496 -- logic. the column value might be different between wjsi and wdj because we do update for wdj if
497 -- wjsi is null for job creation. Otherwise, the value should be the same.
498 select serialization_start_op
499 into l_wdjSerOp
500 from wip_discrete_jobs
501 where wip_entity_id = l_wipEntityID;
502
503 if ( l_wdjSerOp is null ) then
504 return;
505 end if;
506
507 --job must have an assembly, and the assembly must be serial controlled (predefined).
508 select 1
509 into l_dummy
510 from wip_discrete_jobs wdj,
511 mtl_system_items msi
512 where wdj.primary_item_id = msi.inventory_item_id
513 and wdj.organization_id = msi.organization_id
514 and wdj.wip_entity_id = l_wipEntityID
515 and msi.serial_number_control_code = wip_constants.full_sn;
516
517 open c_ops(l_wipEntityID);
518 loop
519 fetch c_ops into l_curOpSeq;
520 exit when c_ops%NOTFOUND;
521 l_rtgExists := true;
522 if(l_curOpSeq = l_wdjSerOp) then
523 l_opFound := true;
524 exit;
525 end if;
526 end loop;
527 close c_ops;
528
529 --The routing exists, but an invalid op seq was provided
530 if ( l_rtgExists and not l_opFound ) then
531 raise fnd_api.g_exc_unexpected_error;
532 end if;
533
534 --If no routing exsts, the serialization op must be 1.
535 if ( not l_rtgExists and l_wdjSerOp <> 1 ) then
536 raise fnd_api.g_exc_unexpected_error;
537 end if;
538
539 --job must be unreleased to change the serialization op on a reschedule request.
540 if ( l_loadType = wip_constants.resched_job and l_wjsiSerOp is not null ) then
541 select 1
542 into l_dummy
543 from wip_discrete_jobs
544 where wip_entity_id = l_wipEntityID
545 and status_type = wip_constants.unreleased;
546 end if;
547
548 l_msg := 'WIP_ML_SER_DEF_FAILURE';
549 wip_job_dtls_substitutions.default_serial_associations(p_rowid => p_rowid,
550 p_wip_entity_id => l_wipEntityID,
551 p_organization_id => l_orgID,
552 x_err_msg => x_errorMsg,
553 x_return_status => x_returnStatus);
554 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
555 raise fnd_api.g_exc_unexpected_error;
556 end if;
557 exception
558 when others then
559 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
560 fnd_message.set_name('WIP', l_msg);
561 if ( l_msg = 'WIP_ML_SER_DEF_FAILURE' ) then
562 fnd_message.set_token('MESSAGE', x_errorMsg);
563 end if;
564 x_errorMsg := fnd_message.get;
565 end processSerStartOp;
566
567 end wip_massload_pub;
[Home] [Help]