1 package body wip_massload_pub as
2 /* $Header: wipmlppb.pls 120.13.12020000.4 2013/04/02 18:38:53 pding 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 l_success number; --8296679
34
35 cursor c_allrows is
36 select 1
37 from wip_job_schedule_interface
38 where group_id = p_groupID
39 and process_status = wip_constants.pending
40 and process_phase = wip_constants.ml_validation
41 and load_type <> wip_constants.create_sched
42 for update nowait;
43
44 cursor c_wjsi is
45 select rowid,
46 header_id,
47 interface_id,
48 job_name, --8494582
49 wip_entity_id, --8494582
50 organization_id, --8494582
51 organization_code, --8494582
52 load_type, --8494582
53 status_type, --8296679
54 class_code, --8296679
55 date_released --8296679
56 from wip_job_schedule_interface
57 where group_id = p_groupID
58 and process_phase = wip_constants.ml_validation
59 and process_status in (wip_constants.running, wip_constants.warning)
60 and load_type <> wip_constants.create_sched;
61
62 cursor cur_wdj is
63 select wdj.wip_entity_id,
64 wdj.organization_id,
65 wdj.status_type,
66 wdj.class_code,
67 wdj.date_released
68 from wip_discrete_jobs wdj, wip_job_schedule_interface wjsi
69 where wjsi.group_id = p_groupID
70 and process_status in (wip_constants.running, wip_constants.warning)
71 and wjsi.wip_entity_id = wdj.wip_entity_id
72 and wdj.status_type in (wip_constants.released, wip_constants.hold);
73
74 begin
75 x_returnStatus := fnd_api.g_ret_sts_success;
76 if (l_logLevel <= wip_constants.trace_logging) then
77 l_params(1).paramName := 'p_groupID';
78 l_params(1).paramValue := p_groupID;
79 l_params(2).paramName := 'p_validationLevel';
80 l_params(2).paramValue := p_validationLevel;
81 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
82 p_params => l_params,
83 x_returnStatus => x_returnStatus);
84 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
85 raise fnd_api.g_exc_unexpected_error;
86 end if;
87 end if;
88
89 -- print the no.of discrete requests to log
90
91 select count(*) into l_requestCount
92 from wip_job_schedule_interface
93 where group_id = p_groupID
94 and process_status = wip_constants.pending
95 and process_phase = wip_constants.ml_validation
96 and load_type <> wip_constants.create_sched;
97
98 if (l_logLevel <= wip_constants.trace_logging) then
99 wip_logger.log(l_requestCount || ' pending request(s) found for group_id' || p_groupID || ' for discrete processing', l_retStatus);
100 end if;
101
102 -- lock all the records
103 open c_allrows;
104 if ( c_allrows%isopen ) then
105 close c_allrows;
106 end if;
107
108 -- assign interface_id and set process_status to running
109 update wip_job_schedule_interface
110 set interface_id = wip_interface_s.nextval,
111 process_status = wip_constants.running
112 where group_id = p_groupID
113 and process_status = wip_constants.pending
114 and process_phase = wip_constants.ml_validation
115 and load_type <> wip_constants.create_sched;
116
117 if (p_commitFlag <> 0) then
118 commit;
119 end if;
120 --
121 -- ?? here, we should assign a different group_id for those records with load type being
122 -- create repetitive schedule
123 --
124
125 -- do the validation for those records
126 wip_validateMLHeader_pvt.validateMLHeader(p_groupID => p_groupID,
127 p_validationLevel => p_validationLevel,
128 x_returnStatus => x_returnStatus,
129 x_errorMsg => x_errorMsg);
130 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
131 raise fnd_api.g_exc_unexpected_error;
132 end if;
133
134 -- process every row which pass validation
135 for wjsi_rec in c_wjsi loop
136 begin
137 savepoint wip_massload_start;
138
139 /*Bug 8494582: Adding the following IF and SQL to fetch old job status in case of updating a job*/
140 if(wjsi_rec.load_type in (WIP_CONSTANTS.RESCHED_JOB,
141 WIP_CONSTANTS.RESCHED_EAM_JOB)) then
142 select wdj.status_type
143 into l_wdj_old_status_type
144 from wip_discrete_jobs wdj
145 where wdj.organization_id=wjsi_rec.organization_id
146 and wdj.wip_entity_id=wjsi_rec.wip_entity_id;
147 end if;
148 /*8494582 End*/
149
150 wip_massload_pvt.processWJSI(wjsi_rec.rowid, l_retStatus, x_errorMsg);
151 if ( l_retStatus <> fnd_api.g_ret_sts_success ) then
152 raise fnd_api.g_exc_unexpected_error;
153 end if;
154
155 select count(*)
156 into l_totalNum
157 from wip_job_schedule_interface wjsi,
158 wip_job_dtls_interface wjdi
159 where wjdi.parent_header_id = wjsi.header_id
160 and wjdi.group_id = wjsi.group_id
161 and wjdi.process_phase = wip_constants.ml_validation
162 and wjdi.process_status = wip_constants.pending
163 and wjsi.rowid = wjsi_rec.rowid
164 and wjsi.load_type in (wip_constants.create_job,
165 wip_constants.create_ns_job,
166 wip_constants.resched_job);
167
168 if (l_logLevel <= wip_constants.trace_logging) then
169 wip_logger.log('There are ' || l_totalNum || ' detail records....', l_retStatus);
170 end if;
171
172 if ( l_totalNum > 0 ) then
173 wip_job_details.load_all_details(p_group_id => p_groupID,
174 p_parent_header_id => wjsi_rec.header_id,
175 p_std_alone => 0,
176 x_err_code => l_errorCode,
177 x_err_msg => x_errorMsg,
178 x_return_status => l_retStatus);
179 if ( l_retStatus <> fnd_api.g_ret_sts_success ) then
180 raise fnd_api.g_exc_unexpected_error;
181 end if;
182 end if;
183
184 -- only here an we do validation for serialization_start_op because it depends on the routing
185 -- explosion and detail loading
186 processSerStartOp(wjsi_rec.rowid,
187 wjsi_rec.interface_id,
188 l_retStatus,
189 x_errorMsg);
190 if ( l_retStatus <> fnd_api.g_ret_sts_success ) then
191 raise fnd_api.g_exc_unexpected_error;
192 end if;
193
194 /* Fix 8296679: Moved this code from wip_massload_pvt.processWJSI to release the job
195 after loading operation from WJDI to populate quantity in queue of the first operation
196 added in WJDI
197 */
198 -- release job if necessary
199 if (wjsi_rec.load_type in (wip_constants.create_job, wip_constants.create_ns_job) and
200 wjsi_rec.status_type in (wip_constants.released, wip_constants.hold) ) then
201 wip_mass_load_processor.ml_release(wjsi_rec.wip_entity_id,
202 wjsi_rec.organization_id,
203 wjsi_rec.class_code,
204 wjsi_rec.status_type,
205 l_success,
206 x_errorMsg,
207 nvl(wjsi_rec.date_released, sysdate));
208
209 if ( l_success = 0 ) then
210 raise fnd_api.g_exc_unexpected_error;
211 end if;
212 end if;
213 /* End Fix 8296679 */
214
215 /*6641029 Start- Added code to create requisition*/
216 select wjsi.status_type,
217 wdj.wip_entity_id,
218 wdj.organization_id
219 into l_wjsi_new_status_type, /*Removed old status type for bug 8494582*/
220 l_wdj_wip_entity_id,
221 l_wdj_organization_id
222 from wip_discrete_jobs wdj,
223 wip_job_schedule_interface wjsi
224 where wjsi.rowid = wjsi_rec.rowid
225 and wdj.wip_entity_id = wjsi.wip_entity_id
226 and wdj.organization_id = wjsi.organization_id
227 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
228 WIP_CONSTANTS.CREATE_NS_JOB,
229 WIP_CONSTANTS.CREATE_EAM_JOB,
230 WIP_CONSTANTS.RESCHED_EAM_JOB,
231 WIP_CONSTANTS.RESCHED_JOB) ;
232
233 if (l_wjsi_new_status_type IN (WIP_CONSTANTS.RELEASED,WIP_CONSTANTS.HOLD)) then
234 if((wip_osp.po_req_created( l_wdj_wip_entity_id,
235 null,
236 l_wdj_organization_id,
237 null,
238 WIP_CONSTANTS.DISCRETE
239 ) = FALSE) or l_wdj_old_status_type in (WIP_CONSTANTS.UNRELEASED)) then
240 /*Bug 16529960: wip_osp.release_validation and its subsequencial call CREATE_REQUISITION does not have any exception handling and also it is not returning any error messages .
241 And CREATE_REQUISITION will throw exception by callingAPP_EXCEPTION.RAISE_EXCEPTION when validation failed, which is not handled here. So add the exception handing block */
242 begin
243 wip_osp.release_validation(l_wdj_wip_entity_id,
244 l_wdj_organization_id,
245 NULL) ;
246 exception
247 when others then
248 x_errorMsg := fnd_message.get;
249 if (l_logLevel <= wip_constants.trace_logging) then
250 wip_logger.log('calling wip_osp.release_validation failed in wip_massload_pub.massLoadJobs', l_retStatus);
251 end if;
252 raise fnd_api.g_exc_unexpected_error;
253 end;
254 end if ;
255 end if ;
256 /*6641029 End*/
257
258 -- Bug Fix 12383088. Commit after processing every single job so that if INVCPOPEN is run simulataneous with WML
259 -- then for the committed jobs WPB will be populated by INVCPOPEN.
260 -- This fix will reduce the probability of jobs having missing WPB records rather than fix it completely.
261 -- Also this will avoid locking of Job for a long time.
262 if (p_commitFlag <> 0) then
263 commit;
264 end if;
265
266 exception
267 when fnd_api.g_exc_unexpected_error then
268 rollback to wip_massload_start;
269 wip_validateMLHeader_pvt.setInterfaceError(wjsi_rec.rowid,
270 wjsi_rec.interface_id,
271 x_errorMsg,
272 g_wipMLError);
273 if (l_logLevel <= wip_constants.trace_logging) then
277 when others then
274 wip_logger.log('interface ' || wjsi_rec.interface_id || ' failed: ' || x_errorMsg, l_retStatus);
275 end if;
276 /*Bug 16529960 Add exception handler to avoid massload terminated because of unexpected exception*/
278 rollback to wip_massload_start;
279 wip_validateMLHeader_pvt.setInterfaceError(wjsi_rec.rowid,
280 wjsi_rec.interface_id,
281 'unexp error: ' || SQLERRM,
282 g_wipMLError);
283 if (l_logLevel <= wip_constants.trace_logging) then
284 wip_logger.log('unexp error: ' || SQLERRM||' for interface id: '||wjsi_rec.interface_id, l_retStatus);
285 end if;
286 end;
287 end loop;
288
289 -- Bug Fix 12383088. Create Period Balance for Periods created during WML run when Job was uncommitted and unhandled in INVCPOPEN.
290 for wdj_rec in cur_wdj loop
291 INSERT INTO WIP_PERIOD_BALANCES
292 (ACCT_PERIOD_ID, WIP_ENTITY_ID,
293 LAST_UPDATE_DATE, LAST_UPDATED_BY,
294 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
295 ORGANIZATION_ID, CLASS_TYPE,
296 TL_RESOURCE_IN, TL_OVERHEAD_IN,
297 TL_OUTSIDE_PROCESSING_IN, PL_MATERIAL_IN,
298 PL_MATERIAL_OVERHEAD_IN, PL_RESOURCE_IN,
299 PL_OVERHEAD_IN, PL_OUTSIDE_PROCESSING_IN,
300 TL_MATERIAL_OUT, TL_RESOURCE_OUT,
301 TL_OVERHEAD_OUT, TL_OUTSIDE_PROCESSING_OUT,
302 PL_MATERIAL_OUT, PL_MATERIAL_OVERHEAD_OUT,
303 PL_RESOURCE_OUT, PL_OVERHEAD_OUT,
304 PL_OUTSIDE_PROCESSING_OUT, PL_MATERIAL_OVERHEAD_VAR,
305 PL_MATERIAL_VAR, PL_OUTSIDE_PROCESSING_VAR,
306 PL_OVERHEAD_VAR, PL_RESOURCE_VAR,
307 TL_MATERIAL_VAR, TL_OUTSIDE_PROCESSING_VAR,
308 TL_OVERHEAD_VAR, TL_RESOURCE_VAR,
309 TL_MATERIAL_OVERHEAD_OUT, TL_MATERIAL_OVERHEAD_VAR)
310 SELECT OAP.ACCT_PERIOD_ID, wdj_rec.wip_entity_id,
311 SYSDATE, fnd_global.user_id,
312 SYSDATE, fnd_global.user_id,fnd_global.login_id ,
313 wdj_rec.organization_id, WC.CLASS_TYPE,
314 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
315 0, 0, 0, 0, 0, 0, 0, 0
316 FROM ORG_ACCT_PERIODS OAP,
317 WIP_ACCOUNTING_CLASSES WC
318 WHERE WC.CLASS_CODE = wdj_rec.class_code
319 AND WC.ORGANIZATION_ID = wdj_rec.organization_id
320 AND OAP.ORGANIZATION_ID = wdj_rec.organization_id
321 AND OAP.SCHEDULE_CLOSE_DATE >= TRUNC(wdj_rec.date_released)
322 AND OAP.PERIOD_CLOSE_DATE IS NULL
323 AND NOT EXISTS (SELECT 'balance record already there'
324 FROM WIP_PERIOD_BALANCES WPB
325 WHERE WPB.WIP_ENTITY_ID = wdj_rec.wip_entity_id
326 AND WPB.ACCT_PERIOD_ID = OAP.ACCT_PERIOD_ID
327 AND WPB.ORGANIZATION_ID = OAP.ORGANIZATION_ID);
328 end loop;
329
330
331
332 update wip_job_schedule_interface
333 set process_status = wip_constants.completed,
334 process_phase = wip_constants.ml_complete
335 where group_id = p_groupID
336 and process_status in (wip_constants.running, wip_constants.warning)
337 and process_phase = wip_constants.ml_validation
338 and load_type <> wip_constants.create_sched;
339
340 if (p_commitFlag <> 0) then
341 commit;
342 end if;
343
344 select count(*) into l_errRecCount
345 from wip_job_schedule_interface
346 where group_id = p_groupID
347 and process_status = wip_constants.error;
348
349 if(l_errRecCount > 0) then
350 x_returnStatus := fnd_api.g_ret_sts_error;
351 else
352 x_returnStatus := fnd_api.g_ret_sts_success;
353 end if;
354
355 if (l_logLevel <= wip_constants.trace_logging) then
356 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
357 p_procReturnStatus => x_returnStatus,
358 p_msg => 'success',
359 x_returnStatus => l_retStatus);
360 end if;
361 exception
362 when wip_constants.records_locked then
363 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
364 if (l_logLevel <= wip_constants.trace_logging) then
365 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
366 p_procReturnStatus => x_returnStatus,
367 p_msg => 'records locked',
368 x_returnStatus => l_retStatus);
369 end if;
370 fnd_message.set_name('WIP', 'WIP_LOCKED_ROW_ALREADY_LOCKED');
371 x_errorMsg := fnd_message.get;
372 when fnd_api.g_exc_unexpected_error then
373 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
374 if (l_logLevel <= wip_constants.trace_logging) then
375 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
376 p_procReturnStatus => x_returnStatus,
377 p_msg => x_errorMsg,
378 x_returnStatus => l_retStatus);
379 end if;
380 /*Bug 16529960: Add exception handler to log any unexpected exception*/
381 when others then
382 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
383 if (l_logLevel <= wip_constants.trace_logging) then
384 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
385 p_procReturnStatus => x_returnStatus,
386 p_msg => 'unexp error: ' || SQLERRM,
387 x_returnStatus => l_retStatus);
388 end if;
392 -- this API is used to create one job for the given interface id. Please note that there should be no
389 end massLoadJobs;
390
391
393 -- other records under the same group id as the given interface id. This API will fail that case.
394 -- also, the load type for this record must be create standard or non-std job.
395 procedure createOneJob(p_interfaceID in number,
396 p_validationLevel in number,
397 x_wipEntityID out nocopy number,
398 x_returnStatus out nocopy varchar2,
399 x_errorMsg out nocopy varchar2) is
400 l_params wip_logger.param_tbl_t;
401 l_procName varchar2(30) := 'createOneJob';
402 l_logLevel number := to_number(fnd_log.g_current_runtime_level);
403 l_retStatus varchar2(1);
404 l_totalNum number;
405 l_groupID number;
406 l_headerID number;
407 l_rowid rowid;
408 l_errorCode varchar2(240);
409 l_load_type wip_job_schedule_interface.load_type%TYPE; --8936011
410 l_status_type wip_job_schedule_interface.status_type%TYPE; --8936011
411 l_wip_entity_id wip_job_schedule_interface.wip_entity_id%TYPE; --8936011
412 l_organization_id wip_job_schedule_interface.organization_id%TYPE; --8936011
413 l_class_code wip_job_schedule_interface.class_code%TYPE; --8936011
414 l_date_released wip_job_schedule_interface.date_released%TYPE; --8936011
415 l_success number; --8936011
416
417 cursor c_allrows is
418 select 1
419 from wip_job_schedule_interface
420 where interface_id = p_interfaceID
421 for update nowait;
422 begin
423 x_returnStatus := fnd_api.g_ret_sts_success;
424 if (l_logLevel <= wip_constants.trace_logging) then
425 l_params(1).paramName := 'p_interfaceID';
426 l_params(1).paramValue := p_interfaceID;
427 l_params(2).paramName := 'p_validationLevel';
428 l_params(2).paramValue := p_validationLevel;
429 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
430 p_params => l_params,
431 x_returnStatus => x_returnStatus);
432 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
433 raise fnd_api.g_exc_unexpected_error;
434 end if;
435 end if;
436
437 select rowid, group_id, header_id
438 into l_rowid, l_groupID, l_headerID
439 from wip_job_schedule_interface
440 where interface_id = p_interfaceID;
441
442 select count(*)
443 into l_totalNum
444 from wip_job_schedule_interface
445 where interface_id = p_interfaceID
446 and process_status = wip_constants.pending
447 and process_phase = wip_constants.ml_validation
448 and load_type in (wip_constants.create_job, wip_constants.create_ns_job);
449
450 if ( l_totalNum <> 1 ) then
451 fnd_message.set_name('WIP', 'WIP_WJSI_ONE_ROW');
452 x_errorMsg := fnd_message.get;
453 raise fnd_api.g_exc_unexpected_error;
454 end if;
455
456 select count(*)
457 into l_totalNum
458 from wip_job_schedule_interface wjsi
459 where wjsi.group_id = l_groupID;
460
461 if ( l_totalNum <> 1 ) then
462 fnd_message.set_name('WIP', 'WIP_WJSI_ONE_ROW');
463 x_errorMsg := fnd_message.get;
464 raise fnd_api.g_exc_unexpected_error;
465 end if;
466
467 -- lock all the records
468 open c_allrows;
469 if ( c_allrows%isopen ) then
470 close c_allrows;
471 end if;
472
473 update wip_job_schedule_interface
474 set process_status = wip_constants.running
475 where interface_id = p_interfaceID;
476
477 -- do the validation for those records
478 wip_validateMLHeader_pvt.validateMLHeader(p_groupID => l_groupID,
479 p_validationLevel => p_validationLevel,
480 x_returnStatus => x_returnStatus,
481 x_errorMsg => x_errorMsg);
482 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
483 raise fnd_api.g_exc_unexpected_error;
484 end if;
485
486 select count(*)
487 into l_totalNum
488 from wip_job_schedule_interface
489 where interface_id = p_interfaceID
490 and process_status in (wip_constants.running, wip_constants.warning);
491
492 if ( l_totalNum <> 1 ) then
493 fnd_message.set_name('WIP', 'WIP_WJSI_VAL_FAILED');
494 x_errorMsg := fnd_message.get;
495 raise fnd_api.g_exc_unexpected_error;
496 end if;
497
498 wip_massload_pvt.processWJSI(l_rowid, x_returnStatus, x_errorMsg);
499 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
500 raise fnd_api.g_exc_unexpected_error;
501 end if;
502
503 select count(*)
504 into l_totalNum
505 from wip_job_schedule_interface wjsi,
506 wip_job_dtls_interface wjdi
507 where wjdi.parent_header_id = wjsi.header_id
508 and wjdi.group_id = wjsi.group_id
509 and wjdi.process_phase = wip_constants.ml_validation
510 and wjdi.process_status = wip_constants.pending
511 and wjsi.rowid = l_rowid;
512
513 if (l_logLevel <= wip_constants.trace_logging) then
514 wip_logger.log('There are ' || l_totalNum || ' detail records....', l_retStatus);
515 end if;
516
517 if ( l_totalNum > 0 ) then
518 wip_job_details.load_all_details(p_group_id => l_groupID,
519 p_parent_header_id => l_headerID,
520 p_std_alone => 0,
521 x_err_code => l_errorCode,
522 x_err_msg => x_errorMsg,
526 end if;
523 x_return_status => l_retStatus);
524 if ( l_retStatus <> fnd_api.g_ret_sts_success ) then
525 raise fnd_api.g_exc_unexpected_error;
527 end if;
528
529 -- only here an we do validation for serialization_start_op because it depends on the routing
530 -- explosion and detail loading
531 processSerStartOp(l_rowid,
532 p_interfaceID,
533 l_retStatus,
534 x_errorMsg);
535 if ( l_retStatus <> fnd_api.g_ret_sts_success ) then
536 raise fnd_api.g_exc_unexpected_error;
537 end if;
538
539 select wip_entity_id,
540 load_type, status_type, organization_id, class_code, date_released --8936011
541 into x_wipEntityID,
542 l_load_type, l_status_type, l_organization_id, l_class_code, l_date_released --8936011
543 from wip_job_schedule_interface
544 where rowid = l_rowid;
545
546 /* Fix 8936011: Moved this code from wip_massload_pvt.processWJSI
547 to release the job after loading operation from WJDI to populate quantity
548 in queue of the first operation added in WJDI
549 */
550 -- release job if necessary
551
552 if (l_load_type in (wip_constants.create_job, wip_constants.create_ns_job) and
553 l_status_type in (wip_constants.released, wip_constants.hold) ) then
554 wip_mass_load_processor.ml_release(x_wipEntityID,
555 l_organization_id,
556 l_class_code,
557 l_status_type,
558 l_success,
559 x_errorMsg,
560 nvl(l_date_released, sysdate));
561
562 if ( l_success = 0 ) then
563 raise fnd_api.g_exc_unexpected_error;
564 end if;
565 end if;
566
567 /* End Fix 8936011 */
568
569 update wip_job_schedule_interface
570 set process_status = wip_constants.completed
571 where rowid = l_rowid;
572
573 if (l_logLevel <= wip_constants.trace_logging) then
574 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
575 p_procReturnStatus => x_returnStatus,
576 p_msg => 'success',
577 x_returnStatus => l_retStatus);
578 end if;
579 exception
580 when wip_constants.records_locked then
581 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
582 fnd_message.set_name('WIP', 'WIP_LOCKED_ROW_ALREADY_LOCKED');
583 x_errorMsg := fnd_message.get;
584 wip_validateMLHeader_pvt.setInterfaceError(l_rowid,
585 p_interfaceID,
586 x_errorMsg,
587 g_wipMLError);
588 if (l_logLevel <= wip_constants.trace_logging) then
589 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
590 p_procReturnStatus => x_returnStatus,
591 p_msg => 'records locked',
592 x_returnStatus => l_retStatus);
593 end if;
594 when fnd_api.g_exc_unexpected_error then
595 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
596 wip_validateMLHeader_pvt.setInterfaceError(l_rowid,
597 p_interfaceID,
598 x_errorMsg,
599 g_wipMLError);
600 if (l_logLevel <= wip_constants.trace_logging) then
601 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
602 p_procReturnStatus => x_returnStatus,
603 p_msg => x_errorMsg,
604 x_returnStatus => l_retStatus);
605 end if;
606 end createOneJob;
607
608
609 procedure processSerStartOp(p_rowid in rowid,
610 p_interfaceID in number,
611 x_returnStatus out nocopy varchar2,
612 x_errorMsg out nocopy varchar2) is
613 l_msg varchar2(80);
614 l_dummy number;
615 l_wjsiSerOp number;
616 l_wdjSerOp number;
617 l_wipEntityID number;
618 l_loadType number;
619 l_primaryItemID number;
620 l_orgID number;
621
622 l_curOpSeq number;
623 l_rtgExists boolean := false;
624 l_opFound boolean := false;
625
626 cursor c_ops(v_wipEntityID number) is
627 select operation_seq_num
628 from wip_operations
629 where wip_entity_id = v_wipEntityID;
630
631 begin
632 x_returnStatus := fnd_api.g_ret_sts_success;
633 l_msg := 'WIP_ML_SERIAL_START_OP';
634
635 select wip_entity_id,
636 serialization_start_op,
637 load_type,
638 primary_item_id,
639 organization_id
640 into l_wipEntityID,
641 l_wjsiSerOp,
642 l_loadType,
643 l_primaryItemID,
644 l_orgID
645 from wip_job_schedule_interface
646 where rowid = p_rowid;
647
648 if ( l_wjsiSerOp is null and l_loadType = wip_constants.resched_job ) then
649 -- due to re-exploding, we may need to clear out the serialization start op
650 update wip_discrete_jobs wdj
651 set serialization_start_op = null
652 where wip_entity_id = l_wipEntityID
653 and serialization_start_op <> 1
654 and not exists(select 1
655 from wip_operations wo
659 end if;
656 where wo.wip_entity_id = wdj.wip_entity_id
657 and wo.operation_seq_num = wdj.serialization_start_op);
658 return;
660
661 -- when the flow comes here, regardless reschedule or job creation, we already updated wdj
662 -- with the right value for serialization_start_op from the interface table or the defaulting
663 -- logic. the column value might be different between wjsi and wdj because we do update for wdj if
664 -- wjsi is null for job creation. Otherwise, the value should be the same.
665 select serialization_start_op
666 into l_wdjSerOp
667 from wip_discrete_jobs
668 where wip_entity_id = l_wipEntityID;
669
670 --Bug#14511983: Updating the serialization_start_op in WDJ when the operations have been added
671 -- This case happens when operations are added and the serialization_start_op is assigned to the new added operation
672 BEGIN
673 SELECT 1
674 into l_dummy
675 from wip_operations wo
676 WHERE wo.wip_entity_id = l_wipEntityID and wo.operation_seq_num = NVL(l_wjsiSerOp,-99) AND rownum=1;
677 exception
678 WHEN NO_DATA_FOUND THEN
679 l_dummy:=0;
680 end;
681 if ( l_wdjSerOp is null and l_dummy=0) then
682 return;
683 elsif (l_wdjSerOp is null and l_dummy=1 ) then
684 update wip_discrete_jobs wdj
685 set serialization_start_op = l_wjsiSerOp
686 where wip_entity_id = l_wipEntityID
687 and exists(select 1
688 from wip_operations wo
689 where wo.wip_entity_id = wdj.wip_entity_id
690 and wo.operation_seq_num = l_wjsiSerOp) returning serialization_start_op into l_wdjSerOp;
691 end if;
692
693 --job must have an assembly, and the assembly must be serial controlled (predefined).
694 select 1
695 into l_dummy
696 from wip_discrete_jobs wdj,
697 mtl_system_items msi
698 where wdj.primary_item_id = msi.inventory_item_id
699 and wdj.organization_id = msi.organization_id
700 and wdj.wip_entity_id = l_wipEntityID
701 and msi.serial_number_control_code = wip_constants.full_sn;
702
703 open c_ops(l_wipEntityID);
704 loop
705 fetch c_ops into l_curOpSeq;
706 exit when c_ops%NOTFOUND;
707 l_rtgExists := true;
708 if(l_curOpSeq = l_wdjSerOp) then
709 l_opFound := true;
710 exit;
711 end if;
712 end loop;
713 close c_ops;
714
715 --The routing exists, but an invalid op seq was provided
716 if ( l_rtgExists and not l_opFound ) then
717 raise fnd_api.g_exc_unexpected_error;
718 end if;
719
720 --If no routing exsts, the serialization op must be 1.
721 if ( not l_rtgExists and l_wdjSerOp <> 1 ) then
722 raise fnd_api.g_exc_unexpected_error;
723 end if;
724
725 --job must be unreleased to change the serialization op on a reschedule request.
726 if ( l_loadType = wip_constants.resched_job and l_wjsiSerOp is not null ) then
727 select 1
728 into l_dummy
729 from wip_discrete_jobs
730 where wip_entity_id = l_wipEntityID
731 and status_type = wip_constants.unreleased;
732 end if;
733
734 l_msg := 'WIP_ML_SER_DEF_FAILURE';
735 wip_job_dtls_substitutions.default_serial_associations(p_rowid => p_rowid,
736 p_wip_entity_id => l_wipEntityID,
737 p_organization_id => l_orgID,
738 x_err_msg => x_errorMsg,
739 x_return_status => x_returnStatus);
740 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
741 raise fnd_api.g_exc_unexpected_error;
742 end if;
743 exception
744 when others then
745 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
746 fnd_message.set_name('WIP', l_msg);
747 if ( l_msg = 'WIP_ML_SER_DEF_FAILURE' ) then
748 fnd_message.set_token('MESSAGE', x_errorMsg);
749 end if;
750 x_errorMsg := fnd_message.get;
751 end processSerStartOp;
752
753 end wip_massload_pub;
[Home] [Help]