1 PACKAGE BODY WSM_MES_UTILITIES_PVT AS
2 /* $Header: WSMMESUB.pls 120.26 2006/08/22 06:46:54 nlal noship $ */
3 --mes
4 g_log_level_unexpected NUMBER := FND_LOG.LEVEL_UNEXPECTED ;
5 g_log_level_error number := FND_LOG.LEVEL_ERROR ;
6 g_log_level_exception number := FND_LOG.LEVEL_EXCEPTION ;
7 g_log_level_event number := FND_LOG.LEVEL_EVENT ;
8 g_log_level_procedure number := FND_LOG.LEVEL_PROCEDURE ;
9 g_log_level_statement number := FND_LOG.LEVEL_STATEMENT ;
10
11 g_msg_lvl_unexp_error NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ;
12 g_msg_lvl_error NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR ;
13 g_msg_lvl_success NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS ;
14 g_msg_lvl_debug_high NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH ;
15 g_msg_lvl_debug_medium NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM ;
16 g_msg_lvl_debug_low NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ;
17
18 g_ret_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
19 g_ret_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
20 g_ret_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
21 --mes end
22
23 /*
24 * Will return a codemask, indicate whether move in, move out, move to next is allowed
25 *
26 * 2^16 = 65536 move in
27 * 2^17 = 131072 move out
28 * 2^18 = 262144 move to next op
29 */
30 function move_txn_allowed(
31 p_responsibility_id in number,
32 p_wip_entity_id in number,
33 p_org_id in number,
34 p_job_op_seq_num in number,
35 p_standard_op_id in number,
36 p_intraop_step in number,
37 p_status_type in number
38 ) return number is
39
40 l_char_temp varchar2(1) := 'E';
41 l_excluded number := 2;
42 l_use_org_settings number := 0;
43 l_queue_mandatory number := 0;
44 l_run_mandatory number := 0;
45 l_to_move_mandatory number := 0;
46 l_move_in_option number := 0;
47 l_move_next_option number := 0;
48
49 -- Logging variables.....
50 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
51 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
52 l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSM_MES_UTILITIES_PVT.move_txn_allowed';
53 l_param_tbl WSM_Log_PVT.param_tbl_type;
54 l_error_count NUMBER;
55 l_return_code NUMBER;
56 l_error_msg VARCHAR2(4000);
57
58 begin
59 IF FND_LOG.LEVEL_PROCEDURE >= l_log_level THEN
60 l_param_tbl.delete;
61 l_param_tbl(1).paramName := 'p_responsibility_id';
62 l_param_tbl(1).paramValue := p_responsibility_id;
63 l_param_tbl(2).paramName := 'p_wip_entity_id';
64 l_param_tbl(2).paramValue := p_wip_entity_id;
65 l_param_tbl(3).paramName := 'p_org_id';
66 l_param_tbl(3).paramValue := p_org_id;
67 l_param_tbl(4).paramName := 'p_job_op_seq_num';
68 l_param_tbl(4).paramValue := p_job_op_seq_num;
69 l_param_tbl(5).paramName := 'p_standard_op_id';
70 l_param_tbl(5).paramValue := p_standard_op_id;
71 l_param_tbl(6).paramName := 'p_intraop_step';
72 l_param_tbl(6).paramValue := p_intraop_step;
73 l_param_tbl(7).paramName := 'p_status_type';
74 l_param_tbl(7).paramValue := p_status_type;
75
76 WSM_Log_PVT.logProcParams(
77 p_module_name => l_module,
78 p_param_tbl => l_param_tbl,
79 p_fnd_log_level => G_LOG_LEVEL_PROCEDURE
80 );
81 END IF;
82
83 if (p_job_op_seq_num IS NULL or p_status_type = 6) then
84 return 0; -- No transaction allowed for future operations
85 end if;
86
87 -- check if the standard operation has responsibility exclusion
88 l_excluded := 2;
89 if(p_responsibility_id > 0 and p_standard_op_id IS NOT NULL) then
90 begin
91 select 1
92 into l_excluded
93 from bom_std_op_resp_exclusions bsore
94 where standard_operation_id = p_standard_op_id
95 and responsibility_id = p_responsibility_id;
96 exception
97 when too_many_rows then
98 l_excluded := 1;
99 when others then
100 l_excluded := 2;
101 end;
102 end if;
103 if(l_excluded = 1) then
104 return 0; -- No transaction allowed for excluded operations
105 end if;
106
107
108 -- get org setting on move txns
109
110 select NVL(move_in_option, 0), -- default: optional
111 NVL(move_to_next_op_option, 0) -- default: optional
112 into l_move_in_option,
113 l_move_next_option
114 from wsm_parameters wp
115 where organization_id = p_org_id;
116
117 -- check the mandatory steps on standard operation definition
118 -- NOTE: data migrated from wsm_operation_details to bso will be yes/no = 1,2
119 -- and new data in bso is default null = no while updating std ops form gives yes/no = 0,1
120 -- The net effect is yes = 1 and no = 0 or 2 or null. Best to code as if 1, else
121 --
122 -- Change for Bugfix 5347555 only use_org_settings default null = yes
123 -- Note: we do not need to check bos vs. bso as changes to bso are updated to bos immediately.
124 -- however in the future we may need to add a check as in wsm_txn_allowed if routing-level changes become allowed.
125 if(p_standard_op_id IS NOT NULL) then
126 begin
127 select NVL(use_org_settings, 1), -- default: use org settings
128 NVL(queue_mandatory_flag, 0), -- default: no
129 NVL(run_mandatory_flag, 0), -- default: no
130 NVL(to_move_mandatory_flag, 0) -- default: no
131 into l_use_org_settings,
132 l_queue_mandatory,
133 l_run_mandatory,
134 l_to_move_mandatory
135 from bom_standard_operations bso
136 where standard_operation_id = p_standard_op_id;
137 exception
138 when others then
139 l_use_org_settings := 1; -- use org settings if bso had some problem
140 l_queue_mandatory := 0; -- won't be used
141 l_run_mandatory := 0; -- won't be used
142 l_to_move_mandatory := 0; -- won't be used
143 end;
144 /* Bugfix 5450128 use org settings when std_op is null */
145 else
146 l_use_org_settings := 1; -- use org settings if non std op
147 l_queue_mandatory := 0; -- won't be used
148 l_run_mandatory := 0; -- won't be used
149 l_to_move_mandatory := 0; -- won't be used
150 end if;
151
152 -- for move in / move out / move to next op
153 if(p_intraop_step = 1) then -- At queue
154 if(l_use_org_settings = 1) then -- use org setting
155 if(l_move_in_option = 1) then
156 return 65536;
157 elsif(l_move_in_option = 2) then
158 return 131072;
159 else
160 return (65536 + 131072);
161 end if;
162 else
163 if(l_run_mandatory = 1) then
164 return 65536;
165 else
166 return (65536 + 131072);
167 end if;
168 end if;
169 elsif(p_intraop_step = 2) then -- At Running
170 return 131072;
171 elsif(p_intraop_step = 3) then -- At ToMove
172 return 262144;
173 end if;
174
175 exception
176 when others then
177 return 0;
178 end move_txn_allowed;
179
180
181 /*
182 * Will return 1 if allowed, 0 otherwise
183 *
184 * p_transaction_type:
185 *
186 * 2^0 = 1 View Job Operation
187 * 2^1 = 2 View Plan Details
188 * 2^2 = 4 View Lot Traveler
189 * 2^3 = 8 View Blank Lot Traveler
190 * 2^4 = 16 Split Job
191 * 2^5 = 32 Merge Jobs
192 * 2^6 = 64 Update Assembly
193 * 2^7 = 128 Update Routing
194 * 2^8 = 256 Update Lot Name
195 * 2^9 = 512 Update Quantity
196 * 2^10 = 1024 Transact Materials
197 * 2^11 = 2048 Jump To Operation
198 * 2^12 = 4096 Undo Move
199 * 2^15 = 32768 Change component during backflush
200 * 2^16 = 65536 Move In
201 * 2^17 = 131072 Move Out
202 * 2^18 = 262144 Move To Next Op
203 */
204 procedure wsm_transaction_allowed(
205 p_transaction_type in number,
206 p_responsibility_id in number,
207 p_wip_entity_id in number,
208 p_org_id in number,
209 p_job_op_seq_num in number,
210 p_standard_op_id in number,
211 p_intraop_step in number,
212 p_status_type in number,
213 x_allowed out nocopy number,
214 x_error_msg_name out nocopy varchar2
215 ) is
216
217 l_excluded number;
218 l_txn_id number;
219 l_undo_source_code varchar2(60);
220 l_charge_jump_from_queue number;
221 l_txn_allowed number;
222 l_wip_run_enabled_flag number;
223 l_wip_to_move_enabled_flag number;
224 l_routing_op_seq_num number;
225 l_wsm_move_in number;
226 l_wsm_move_to_next_op number;
227 l_op_use_org_settings number;
228 l_op_to_move_mandatory_flag number;
229 l_op_run_mandatory_flag number;
230 l_max_op_seq_num number;
231 l_org_allow_undo number; -- variable added for bug 5205280
232 l_internal_copy_type number; -- added for bugfix 5441529
233 l_max_move_txn_date date; -- bugfix 5471833
234 l_max_wlt_txn_date date; -- bugfix 5471833
235
236 -- Logging variables.....
237 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
238 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
239 l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSM_MES_UTILITIES_PVT.wsm_transaction_allowed';
240 l_param_tbl WSM_Log_PVT.param_tbl_type;
241 l_error_count NUMBER;
242 l_return_code NUMBER;
243 l_error_msg VARCHAR2(4000);
244
245 begin
246 IF FND_LOG.LEVEL_PROCEDURE >= l_log_level THEN
247 l_param_tbl.delete;
248 l_param_tbl(1).paramName := 'p_transaction_type';
249 l_param_tbl(1).paramValue := p_transaction_type;
250 l_param_tbl(2).paramName := 'p_responsibility_id';
251 l_param_tbl(2).paramValue := p_responsibility_id;
252 l_param_tbl(3).paramName := 'p_wip_entity_id';
253 l_param_tbl(3).paramValue := p_wip_entity_id;
254 l_param_tbl(4).paramName := 'p_org_id';
255 l_param_tbl(4).paramValue := p_org_id;
256 l_param_tbl(5).paramName := 'p_job_op_seq_num';
257 l_param_tbl(5).paramValue := p_job_op_seq_num;
258 l_param_tbl(6).paramName := 'p_standard_op_id';
259 l_param_tbl(6).paramValue := p_standard_op_id;
260 l_param_tbl(7).paramName := 'p_intraop_step';
261 l_param_tbl(7).paramValue := p_intraop_step;
262 l_param_tbl(8).paramName := 'p_status_type';
263 l_param_tbl(8).paramValue := p_status_type;
264
265 WSM_Log_PVT.logProcParams(
266 p_module_name => l_module,
267 p_param_tbl => l_param_tbl,
268 p_fnd_log_level => G_LOG_LEVEL_PROCEDURE
269 );
270 END IF;
271
272 /* Bugfix 5441529 cannot transact on jobs which failed upgrade */
273 /* internal_copy_type = 0 if succeeded, 3 if failed */
274 begin
275 select nvl(internal_copy_type,0)
276 into l_internal_copy_type
277 from wsm_lot_based_jobs
278 where wip_entity_id = p_wip_entity_id;
279
280 exception
281 when no_data_found then
282 l_internal_copy_type := 3;
283 when others then
284 l_internal_copy_type := 3;
285 end;
286
287 if (l_internal_copy_type = 3) then
288 x_allowed := 0;
289 x_error_msg_name := 'WSM_NO_VALID_COPY';
290 return;
291 end if;
292 /* End Bugfix 5441529 */
293
294 if(p_transaction_type not in (1, 2, 4, 8)) then --skip validation for view job op, plan details, traveler
295 -- check future operation
296 if (p_job_op_seq_num IS NULL ) then
297 x_allowed := 0; -- No transaction allowed for future operations
298 x_error_msg_name := 'WSM_NO_TXN_FUTURE_OPERATION';
299 return;
300 end if;
301
302 --check for job status other than released or completed
303 if (p_status_type NOT IN (3, 4)) then
304 x_allowed := 0; -- No transaction allowed for job status other than released or completed
305 x_error_msg_name := 'WSM_MES_TXN_ONLY_REL_COMPL_JOB';
306 return;
307 end if;
308
309 --check completed op or completed job
310 if (((p_intraop_step IS NULL) OR (p_intraop_step IS NOT NULL AND p_intraop_step = -1)) AND
311 (p_transaction_type NOT IN (1024, 4096)) ) then
312 x_allowed := 0; -- No transaction allowed for completed jobs or ops except return, txn material
313 x_error_msg_name := 'WSM_COMP_RETURN_TXN_MAT_ONLY';
314 return;
315 end if;
316
317 end if; --if(p_transaction_type not in (1, 2, 4, 8))
318
319 if(p_transaction_type not in (1, 4, 8)) then --skip validations for view job op, traveler
320
321 -- start bugfix 5225744
322 if ((p_transaction_type = 32) AND (p_standard_op_id IS NULL)) then
323 x_allowed := 0; --cannot merge at non-std op
324 x_error_msg_name := 'WSM_MES_NO_NONSTD_OP_MERGE';
325 return;
326 end if;
327 -- end bugfix 5225744
328
329 -- check intra-op step running
330 if(p_intraop_step = 2) then
331 if(p_transaction_type in (16, 32, 64, 128, 256, 512, 2048)) then
332 x_allowed := 0; -- No transaction allowed at run for WLT and Jump
333 x_error_msg_name := 'WSM_TXN_NOT_ALLOWED_IN_RUN';
334 return;
335 end if;
336 end if;
337
338 if(p_transaction_type = 4096) then
339
340 -- bug 5205280 begin addition
341 -- check to see if undo is allowed in the organization as set in the WSM organization parameters
342
343 select allow_backward_move_flag
344 into l_org_allow_undo
345 from wsm_parameters wp
346 where organization_id = p_org_id;
347
348 if ( l_org_allow_undo <> 1) then
349 x_allowed := 0;
350 x_error_msg_name := 'WSM_UNDO_NOT_ENABLED';
351 return;
352 end if;
353
354 -- bug 5205280 end of additions
355
356 l_txn_id := NULL;
357 select max(transaction_id)
358 into l_txn_id
359 from wip_move_transactions
360 where organization_id = p_org_id
361 and wip_entity_id = p_wip_entity_id
362 and wsm_undo_txn_id IS NULL;
363
364 if(l_txn_id IS NULL) then
365 x_allowed := 0; -- No move to be undone
366 x_error_msg_name := 'WSM_NO_MOVE_TXNS';
367 return;
368 else
369 select source_code
370 into l_undo_source_code
371 from wip_move_transactions
372 where transaction_id = l_txn_id;
373 end if;
374
375 if ((l_undo_source_code IS NULL) OR
376 (l_undo_source_code NOT IN (
377 'move in oa page',
378 'move out oa page',
379 'jump oa page',
380 'move to next op oa page')))
381 then
382 x_allowed := 0;
383 x_error_msg_name := 'WSM_MES_UNDO_FORMSINTERFACE_OA';
384 return;
385 end if;
386
387 if ((p_intraop_step IS NULL) OR (p_intraop_step IS NOT NULL AND p_intraop_step = -1)) then --completed op
388 --job needs to be completed and job op should be max
389 select max(operation_seq_num)
390 into l_max_op_seq_num
391 from wip_operations
392 where wip_entity_id = p_wip_entity_id;
393
394 if (l_max_op_seq_num <> p_job_op_seq_num AND p_status_type <> 4) then
395 x_allowed := 0;
396 x_error_msg_name := 'WSM_MES_UNDO_NOT_ALLOWED';
397 return;
398 end if;
399 end if;
400
401 /* Bugfix 5471833 if last txn performed was WLT then cannot undo */
402 l_max_move_txn_date :=null;
403 l_max_wlt_txn_date :=null;
404
405 begin
406 select max(transaction_date)
407 into l_max_move_txn_date
408 from wip_move_transactions
409 where organization_id = p_org_id
410 and wip_entity_id = p_wip_entity_id
411 and wsm_undo_txn_id IS NULL;
412 exception
413 when no_data_found then
414 l_max_move_txn_date :=null;
415 end;
416
417 begin
418 select max(wsmt.transaction_date)
419 into l_max_wlt_txn_date
420 FROM wsm_split_merge_transactions wsmt,
421 wsm_sm_starting_jobs wssj
422 WHERE wsmt.transaction_id = wssj.transaction_id
423 AND wssj.wip_entity_id = p_wip_entity_id;
424 exception
425 when no_data_found then
426 l_max_wlt_txn_date :=null;
427 end;
428
429 if (l_max_wlt_txn_date is not null AND
430 ((l_max_move_txn_date is null) OR
431 (l_max_move_txn_date is not null and l_max_move_txn_date < l_max_wlt_txn_date))) then
432 x_allowed := 0;
433 x_error_msg_name := 'WSM_MES_NO_UNDO_AFTER_WLT';
434 return;
435 end if;
436 /* End Bugfix 5471833 */
437
438 end if; --if(p_transaction_type = 4096)
439
440 -- check jump
441 if(p_transaction_type = 2048) then
442
443 l_charge_jump_from_queue := 2;
444 select charge_jump_from_queue
445 into l_charge_jump_from_queue
446 from wsm_parameters
447 where organization_id = p_org_id;
448
449 if(l_charge_jump_from_queue = 1 and p_intraop_step = 1) then
450 x_allowed := 0;
451 x_error_msg_name := 'WSM_MES_JUMP_QUEUE_CHG_OP';
452 return;
453 end if;
454 end if;
455
456 -- check operation responsibility exclusion
457 l_excluded := 0;
458 if(p_responsibility_id > 0 and p_standard_op_id IS NOT NULL and p_transaction_type<>2) then
459 begin
460 select 1
461 into l_excluded
462 from bom_std_op_resp_exclusions bsore
463 where standard_operation_id = p_standard_op_id
464 and responsibility_id = p_responsibility_id;
465 exception
466 when too_many_rows then
467 l_excluded := 1;
468 when others then
469 l_excluded := 0;
470 end;
471 end if;
472 if(l_excluded = 1) then
473 x_allowed := 0; -- No txn allowed where curr resp is excluded at current std op
474 x_error_msg_name := 'WSM_TXN_OPERATION_EXCLUDED';
475 return;
476 end if;
477
478 -- check responsibility settings
479 l_txn_allowed := p_transaction_type;
480 if ( (p_responsibility_id > 0) AND (p_transaction_type NOT IN (65536, 131072, 262144)) ) then
481 begin
482 select bitand(code_mask, p_transaction_type)
483 into l_txn_allowed
484 from wsm_responsibility_settings wrs
485 where responsibility_id = p_responsibility_id;
486 exception
487 when others then
488 null;
489 end;
490 end if;
491 if(l_txn_allowed = 0) then
492 x_allowed := 0; -- No transaction based on responsibility settings
493 x_error_msg_name := 'WSM_TXN_NOT_ALLOWED_RESP';
494 return;
495 end if;
496
497 --Validations for Move In, Move Out, Move To Next Op
498 if (p_transaction_type IN (65536, 131072, 262144)) then
499
500 begin
501 SELECT current_rtg_op_seq_num
502 INTO l_routing_op_seq_num
503 FROM wsm_lot_based_jobs
504 WHERE wip_entity_id = p_wip_entity_id;
505 exception
506 when no_data_found then
507 l_routing_op_seq_num := null;
508 end;
509
510 --Bug 4914167:SQL id 15041164:WIP_PARAMETERS_V is replaced
511 --with wvis to fix share memory violations.
512 --SELECT run_enabled_flag, to_move_enabled_flag
513 SELECT DECODE(SUM(DECODE(WVIS.STEP_LOOKUP_TYPE,2,1,0)),0,2,1),
514 DECODE(SUM(DECODE(WVIS.STEP_LOOKUP_TYPE,3,
515 DECODE(WVIS.RECORD_CREATOR,'USER',1,0),0)),0,2,1)
516 INTO l_wip_run_enabled_flag, l_wip_to_move_enabled_flag
517 --FROM WIP_PARAMETERS_V
518 FROM WIP_VALID_INTRAOPERATION_STEPS WVIS
519 WHERE organization_id = p_org_id;
520
521 -- Optional = 0, Always = 1, Never = 2
522 SELECT nvl(move_in_option, 0), nvl(move_to_next_op_option, 0) --bugfix 5336838 changed from default 2 to 0
523 INTO l_wsm_move_in, l_wsm_move_to_next_op
524 FROM WSM_PARAMETERS
525 WHERE organization_id = p_org_id;
526
527 -- NOTE: data migrated from wsm_operation_details to bso will be yes/no = 1,2
528 -- and new data in bso is default null = no while updating std ops form gives yes/no = 0,1
529 -- The net effect is yes = 1 and no = 0 or 2 or null. Best to code as if 1, else
530 --
531 -- Change for Bugfix 5347555 use_org_settings default null = yes
532 -- Note: we do not really need a check on bos vs. bso because bso changes are updated to bos immediately.
533 -- however leaving this as-is as we may support routing-level changes in the future; move_txn_allowed would need to be changed.
534 begin
535 IF (l_routing_op_seq_num IS NOT NULL) THEN
536 SELECT nvl(BOS.use_org_settings, 1), nvl(BOS.run_mandatory_flag, 0), nvl(BOS.to_move_mandatory_flag, 0)
537 INTO l_op_use_org_settings, l_op_run_mandatory_flag, l_op_to_move_mandatory_flag
538 FROM BOM_OPERATION_SEQUENCES BOS, WIP_OPERATIONS WO
539 WHERE WO.wip_entity_id = p_wip_entity_id
540 AND WO.operation_seq_num = p_job_op_seq_num
541 AND BOS.operation_sequence_id = WO.operation_sequence_id;
542 ELSE
543 SELECT nvl(BSO.use_org_settings, 1), nvl(BSO.run_mandatory_flag, 0), nvl(BSO.to_move_mandatory_flag, 0)
544 INTO l_op_use_org_settings, l_op_run_mandatory_flag, l_op_to_move_mandatory_flag
545 FROM BOM_STANDARD_OPERATIONS BSO, WIP_OPERATIONS WO
546 WHERE WO.wip_entity_id = p_wip_entity_id
547 AND WO.operation_seq_num = p_job_op_seq_num
548 AND BSO.standard_operation_id = WO.standard_operation_id
549 AND BSO.organization_id = WO.organization_id;
550 END IF;--IF (l_routing_op_seq_num IS NOT NULL)
551 exception
552 when others then
553 l_op_use_org_settings := 1; --use org settings if op_seq or std_op is not found
554 l_op_run_mandatory_flag := 0; --this won't be used if use_org_settings = 1
555 l_op_to_move_mandatory_flag := 0; --this won't be used if use_org_settings = 1
556 end;
557
558 --move to next op: check when no valid next routing op can be found
559 IF (p_transaction_type = 262144 AND l_routing_op_seq_num IS NULL) THEN --jumped
560 l_excluded := 1;
561 ELSIF (p_transaction_type = 262144 AND l_routing_op_seq_num IS NOT NULL) THEN --check routing refreshed and rtg op now lost
562 begin
563 SELECT 0
564 INTO l_excluded
565 FROM wsm_copy_op_networks
566 WHERE wip_entity_id = p_wip_entity_id
567 AND (from_op_seq_num = l_routing_op_seq_num
568 OR to_op_seq_num = l_routing_op_seq_num);
569 exception
570 when no_data_found then
571 l_excluded := 1;
572 when too_many_rows then
573 l_excluded := 0;
574 when others then
575 l_excluded := 0;
576 end;
577 ELSE
578 l_excluded := 0;
579 END IF;
580
581 if(l_excluded = 1) then
582 x_allowed := 0; -- Txn not allowed if jumped or if routing refreshed and rtg op now lost
583 x_error_msg_name := 'WSM_MES_NEXT_RTG_OP_NOT_FOUND';
584 return;
585 end if;
586
587 --check if wip_parameters settings conflict with wsm_parameter settings
588 IF (l_wip_run_enabled_flag = 2 AND p_transaction_type = 65536) THEN --bugfix 5336838 limit error to Move In txn
589 IF l_op_use_org_settings = 1 THEN
590 IF (l_wsm_move_in = 1 OR l_wsm_move_in = 0)THEN --bugfix 5336838 both Always and Optional will error out
591 x_allowed := 0;
592 x_error_msg_name := 'WSM_MES_WIP_RUN_DIS_WSM_MV_IN';
593 return;
594 END IF;
595 ELSE
596 /* IF l_op_run_mandatory_flag = 1 THEN */ --bugfix 5336838 both Mandatory and Optional will error out
597 x_allowed := 0;
598 x_error_msg_name := 'WSM_MES_WIP_RUN_DIS_BOS_MV_IN';
599 return;
600 /* END IF; */
601 END IF;
602 END IF;--IF l_wip_run_enabled_flag = 2
603
604 IF (l_wip_to_move_enabled_flag = 2 AND p_transaction_type = 131072) THEN --bugfix 5336838 limit error to Move Out txn
605 IF l_op_use_org_settings = 1 THEN
606 IF l_wsm_move_to_next_op = 1 THEN
607 x_allowed := 0;
608 x_error_msg_name := 'WSM_MES_WIP_WSM_MOVE';
609 return;
610 END IF;
611 ELSE
612 IF l_op_to_move_mandatory_flag = 1 THEN
613 x_allowed := 0;
614 x_error_msg_name := 'WSM_MES_WIP_BOS_MOVE';
615 return;
616 END IF;
617 END IF;
618 END IF;--IF l_wip_to_move_enabled_flag = 2
619 end if; --if (p_transaction_type IN (65536, 131072, 262144))
620 end if; --if(p_transaction_type not in (1, 4, 8))
621
622 x_allowed := 1;
623 x_error_msg_name := null;
624
625 end wsm_transaction_allowed;
626
627 /*
628 * Will return 1 if job status changed, 0 otherwise
629 */
630 function wsm_job_changed(
631 p_wip_entity_id in number,
632 p_job_op_seq_num in number,
633 p_intraop_step in number,
634 p_status_type in number,
635 p_quantity in number,
636 p_job_name in varchar2
637 ) return number is
638
639 l_job_op_seq_num number;
640 l_intraop_step number;
641 l_status_type number;
642 l_quantity number;
643 l_job_name varchar2(100);
644
645 -- Logging variables.....
646 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
647 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
648 l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSM_MES_UTILITIES_PVT.wsm_job_changed';
649 l_param_tbl WSM_Log_PVT.param_tbl_type;
650 l_error_count NUMBER;
651 l_return_code NUMBER;
652 l_error_msg VARCHAR2(4000);
653
654 begin
655 IF FND_LOG.LEVEL_PROCEDURE >= l_log_level THEN
656 l_param_tbl.delete;
657 l_param_tbl(1).paramName := 'p_wip_entity_id';
658 l_param_tbl(1).paramValue := p_wip_entity_id;
659 l_param_tbl(2).paramName := 'p_job_op_seq_num';
660 l_param_tbl(2).paramValue := p_job_op_seq_num;
661 l_param_tbl(3).paramName := 'p_intraop_step';
662 l_param_tbl(3).paramValue := p_intraop_step;
663 l_param_tbl(4).paramName := 'p_status_type';
664 l_param_tbl(4).paramValue := p_status_type;
665 l_param_tbl(5).paramName := 'p_quantity';
666 l_param_tbl(5).paramValue := p_quantity;
667 l_param_tbl(6).paramName := 'p_job_name';
668 l_param_tbl(6).paramValue := p_job_name;
669
670 WSM_Log_PVT.logProcParams(
671 p_module_name => l_module,
672 p_param_tbl => l_param_tbl,
673 p_fnd_log_level => G_LOG_LEVEL_PROCEDURE
674 );
675 END IF;
676
677 if ((p_job_op_seq_num IS NULL ) OR
678 ((p_intraop_step IS NULL) OR (p_intraop_step IS NOT NULL AND p_intraop_step = -1))) then
679 return 0; --skip job state check for future and completed job ops
680 end if;
681
682 select we.wip_entity_name job_name,
683 wo.operation_seq_num job_op_seq_num,
684 wo.quantity_in_queue
685 + wo.quantity_running
686 + wo.quantity_waiting_to_move assembly_quantity,
687 wdj.status_type status_type,
688 case when wo.quantity_in_queue>0 then 1
689 when wo.quantity_running>0 then 2
690 when wo.quantity_waiting_to_move>0 then 3
691 else null end intraop_step_code
692 into l_job_name,
693 l_job_op_seq_num,
694 l_quantity,
695 l_status_type,
696 l_intraop_step
697 from wip_discrete_jobs WDJ,
698 wip_entities WE,
699 wip_operations WO
700 where WE.entity_type in (5, 8)
701 and WDJ.wip_entity_id = we.wip_entity_id
702 and WDJ.organization_id = we.organization_id
703 and WDJ.status_type in (3, 6)
704 and WO.wip_entity_id = WDJ.wip_entity_id
705 and WO.organization_id = WDJ.organization_id
706 and wo.quantity_in_queue
707 + wo.quantity_running
708 + wo.quantity_waiting_to_move > 0
709 and WDJ.WIP_ENTITY_ID = p_wip_entity_id;
710
711 if(l_job_name = p_job_name and
712 l_job_op_seq_num = p_job_op_seq_num and
713 l_quantity = p_quantity and
714 l_status_type = p_status_type and
715 l_intraop_step = p_intraop_step) then
716 return 0;
717 else
718 return 1;
719 end if;
720
721 exception
722 when no_data_found then -- completed job, no current op
723 if(p_status_type = 6) then
724 return 0;
725 else
726 return 1;
727 end if;
728 when others then
729 return 1;
730 end wsm_job_changed;
731
732
733
734 /*
735 * find corrent job operations for that is with a give resource / instance
736 */
737 function get_current_job_op (
738 p_organization_id in number,
739 p_department_id in number,
740 p_resource_id in number,
741 p_instance_id in number,
742 p_serial_number in varchar2) return varchar2
743 is
744 l_job_name wip_entities.wip_entity_name%type;
745 l_wip_entity_id number;
746 l_op_seq_num number;
747 l_rtg_op_seq_num number;
748 l_status_type number;
749
750 begin
751
752 if(p_instance_id IS NULL) then
753 select
754 we.wip_entity_name,
755 wdj.wip_entity_id,
756 wo.operation_seq_num,
757 wdj.status_type,
758 wlbj.current_rtg_op_seq_num
759 into l_job_name,
760 l_wip_entity_id,
761 l_op_seq_num,
762 l_status_type,
763 l_rtg_op_seq_num
764 from wip_discrete_jobs WDJ,
765 wip_entities WE,
766 wip_operations WO,
767 wip_operation_resources WOR,
768 wsm_lot_based_jobs WLBJ
769 where WE.entity_type in (5, 8)
770 and WDJ.wip_entity_id = we.wip_entity_id
771 and WDJ.organization_id = we.organization_id
772 and WDJ.status_type in (3, 6)
773 and WO.wip_entity_id = WDJ.wip_entity_id
774 and WO.organization_id = WDJ.organization_id
775 and WO.operation_seq_num = WOR.operation_seq_num
776 and WO.wip_entity_id = WOR.wip_entity_id
777 and WO.organization_id = WOR.organization_id
778 and WO.quantity_in_queue
779 + WO.quantity_running
780 + WO.quantity_waiting_to_move <> 0
781 and not exists (
782 select BDRI.instance_id
783 from BOM_DEPT_RES_INSTANCES BDRI
784 where BDRI.department_id = WO.department_id
785 and BDRI.resource_id = WOR.resource_id
786 and rownum = 1
787 )
788 and WDJ.organization_id = p_organization_id
789 and WO.department_id = p_department_id
790 and WOR.resource_id = p_resource_id
791 and WE.wip_entity_id = WLBJ.wip_entity_id
792 and WE.organization_id = WLBJ.organization_id;
793
794 else
795
796 select
797 we.wip_entity_name,
798 wdj.wip_entity_id,
799 wo.operation_seq_num,
800 wdj.status_type,
801 wlbj.current_rtg_op_seq_num
802 into l_job_name,
803 l_wip_entity_id,
804 l_op_seq_num,
805 l_status_type,
806 l_rtg_op_seq_num
807 from wip_discrete_jobs WDJ,
808 wip_entities WE,
809 wip_operations WO,
810 wip_operation_resources WOR,
811 wip_op_resource_instances WORI,
812 wsm_lot_based_jobs WLBJ
813 where WE.entity_type in (5, 8)
814 and WDJ.wip_entity_id = we.wip_entity_id
815 and WDJ.organization_id = we.organization_id
816 and WDJ.status_type in (3, 6)
817 and WO.wip_entity_id = WDJ.wip_entity_id
818 and WO.organization_id = WDJ.organization_id
819 and WO.operation_seq_num = WOR.operation_seq_num
820 and WO.wip_entity_id = WOR.wip_entity_id
821 and WO.organization_id = WOR.organization_id
822 and WOR.wip_entity_id = WORI.wip_entity_id
823 and WOR.operation_seq_num = WORI.operation_seq_num
824 and WOR.resource_seq_num = WORI.resource_seq_num
825 and WO.quantity_in_queue
826 + WO.quantity_running
827 + WO.quantity_waiting_to_move <> 0
828 and WDJ.organization_id = p_organization_id
829 and WO.department_id = p_department_id
830 and WOR.resource_id = p_resource_id
831 and WORI.instance_id = p_instance_id
832 and WORI.serial_number = p_serial_number
833 and WE.wip_entity_id = WLBJ.wip_entity_id
834 and WE.organization_id = WLBJ.organization_id;
835
836 end if;
837
838 return l_job_name || '|!@$%^&*|' || l_wip_entity_id || '|!@$%^&*|' || l_op_seq_num || '|!@$%^&*|' || l_rtg_op_seq_num || '|!@$%^&*|' || l_status_type;
839
840 exception
841 when too_many_rows then
842 return 'MultipleJobs';
843 when others then
844 return null;
845 end get_current_job_op;
846
847
848 /*
849 * Bugfix 5356648 OSP warnings. Check for po reqs and orders.
850 *
851 * Will return 1 if po reqs/orders exist, 0 otherwise
852 *
853 * p_transaction_type:
854 *
855 * 2^4 = 16 Split Job
856 * 2^5 = 32 Merge Jobs
857 * 2^6 = 64 Update Assembly
858 * 2^7 = 128 Update Routing
859 * 2^11 = 2048 Jump To Operation
860 * 2^12 = 4096 Undo Move
861 */
862 function check_po_req_exists(
863 p_txn_type in number,
864 p_wip_entity_id in number
865 ) return number is
866
867 l_charge_jump_from_queue number;
868
869 l_job_op_seq_num number;
870 l_org_id number;
871
872 -- Logging variables.....
873 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
874 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
875 l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSM_MES_UTILITIES_PVT.check_po_req_exists';
876 l_param_tbl WSM_Log_PVT.param_tbl_type;
877
878 begin
879 IF FND_LOG.LEVEL_PROCEDURE >= l_log_level THEN
880 l_param_tbl.delete;
881 l_param_tbl(1).paramName := 'p_wip_entity_id';
882 l_param_tbl(1).paramValue := p_wip_entity_id;
883 l_param_tbl(2).paramName := 'p_txn_type';
884 l_param_tbl(2).paramValue := p_txn_type;
885
886 WSM_Log_PVT.logProcParams(
887 p_module_name => l_module,
888 p_param_tbl => l_param_tbl,
889 p_fnd_log_level => G_LOG_LEVEL_PROCEDURE
890 );
891 END IF;
892
893 select organization_id,
894 current_job_op_seq_num
895 into l_org_id,
896 l_job_op_seq_num
897 from wsm_lot_based_jobs
898 where wip_entity_id = p_wip_entity_id;
899
900 IF (p_txn_type IN (16,32,64,128,2048,4096)) THEN
901
902 IF wip_osp.po_req_exists(p_wip_entity_id, NULL, l_org_id, l_job_op_seq_num, 5) THEN
903
904 if (p_txn_type = 2048) then /* Jump */
905
906 SELECT nvl(charge_jump_from_queue,2)
907 INTO l_charge_jump_from_queue
908 FROM wsm_parameters
909 WHERE organization_id = l_org_id;
910
911 if (l_charge_jump_from_queue = 2) then
912 return 1; /* Charge Jump From Queue = No and po reqs or headers exist */
913 else
914 return 0; /* Charge Jump From Queue = Yes even if po reqs or headers exist */
915 end if;
916
917 else /* Split, Merge, Upd Rtg, Upd Assy, Undo */
918 return 1;
919
920 end if;
921
922 ELSE /* no po reqs or headers exist */
923 return 0;
924
925 END IF;
926
927 ELSE /* called for any other txn */
928 return 0;
929 END IF;
930
931 exception
932 when others then
933 return 0;
934 end check_po_req_exists;
935
936 --Bug 5409116:Function get_share_from_dept is added.
937 function get_share_from_dept(
938 p_department_id in number,
939 p_resource_id in number) return number IS
940
941 l_share_from_dept NUMBER;
942 -- Logging variables.....
943 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
944 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
945 l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSM_MES_UTILITIES_PVT.get_share_from_dept';
946 l_param_tbl WSM_Log_PVT.param_tbl_type;
947 l_error_count NUMBER;
948 l_return_code NUMBER;
949 l_error_msg VARCHAR2(4000);
950
951 begin
952 IF FND_LOG.LEVEL_PROCEDURE >= l_log_level THEN
953 l_param_tbl.delete;
954 l_param_tbl(1).paramName := 'p_department_id';
955 l_param_tbl(1).paramValue := p_department_id;
956 l_param_tbl(1).paramName := 'p_resource_id';
957 l_param_tbl(1).paramValue := p_resource_id;
958
959 WSM_Log_PVT.logProcParams(
960 p_module_name => l_module,
961 p_param_tbl => l_param_tbl,
962 p_fnd_log_level => G_LOG_LEVEL_PROCEDURE
963 );
964 END IF;
965
966 begin
967 select SHARE_FROM_DEPT_ID
968 into l_share_from_dept
969 from BOM_DEPARTMENT_RESOURCES
970 where department_id = p_department_id
971 and resource_id = p_resource_id
972 and SHARE_FROM_DEPT_ID IS NOT NULL;
973 exception
974 when others then
975 l_share_from_dept := p_department_id;
976 end;
977
978 return(l_share_from_dept);
979 end get_share_from_dept;
980
981 END;
[Home] [Help]