1 PACKAGE BODY WSMPUTIL AS
2 /* $Header: WSMUTILB.pls 120.6.12010000.7 2010/03/03 15:45:59 sisankar ship $ */
3
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | |
9 | File Name : wsmutilb.pls |
10 | Description : Contains the following procedures :
11 | FUNCTION CHECK_WSM_ORG
12 | PROCEDURE find_routing_start - overridden
13 | PROCEDURE find_routing_end - overridden
14 | FUNCTION GET_SCHEDULED_DATE - overridden
15 | FUNCTION GET_DEF_ACCT_CLASS_CODE
16 | PROCEDURE GET_DEF_COMPLETION_SUB_DTLS - overridden
17 | FUNCTION primary_loop_test
18 | PROCEDURE GET_DEFAULT_SUB_LOC
19 | PROCEDURE UPDATE_SUB_LOC
20 | FUNCTION CHECK_IF_ORG_IS_VALID
21 | PROCEDURE WRITE_TO_WIE
22 | PROCEDURE find_common_routing
23 | FUNCTION get_routing_start
24 | FUNCTION get_routing_end
25 | FUNCTION CHECK_COPROD_RELATION
26 | FUNCTION CHECK_COPROD_COMP_RELATION
27 | FUNCTION CHECK_COPROD_RELATION
28 | FUNCTION CHECK_100_PERCENT
29 | PROCEDURE AUTONOMOUS_TXN
30 | PROCEDURE OPERATION_IS_STANDARD_REPEATS - overridden
31 | PROCEDURE validate_non_std_references
32 | FUNCTION WSM_ESA_ENABLED
33 | FUNCTION WSM_CHANGE_ESA_FLAG
34 | FUNCTION network_with_disabled_op
35 | FUNCTION primary_path_is_effective_till
36 | FUNCTION effective_next_op_exists
37 | FUNCTION effective_next_op_exits
38 | FUNCTION wlt_if_costed
39 | PROCEDURE check_charges_exist
40 | FUNCTION replacement_op_seq_id
41 | FUNCTION check_po_move
42 | PROCEDURE validate_lbj_before_close
43 | PROCEDURE get_Kanban_rec_grp_info
44 | PROCEDURE get_max_kanban_asmbly_qty
45 | PROCEDURE return_att_quantity
46 | FUNCTION check_osp_operation
47 | FUNCTION CHECK_WLMTI - overridden and commented
48 | FUNCTION CHECK_WMTI - overridden and commented
49 | FUNCTION CHECK_WSMT - overridden and commented
50 | FUNCTION CHECK_WMT - commented
51 | FUNCTION CHECK_WSMTI - commented
52 | FUNCTION JOBS_WITH_QTY_AT_FROM_OP - overridden
53 | FUNCTION CREATE_LBJ_COPY_RTG_PROFILE - overridden
54 | FUNCTION GET_INV_ACCT_PERIOD
55 | PROCEDURE AUTONOMOUS_WRITE_TO_WIE
56 | FUNCTION GET_JOB_BOM_SEQ_ID
57 | FUNCTION replacement_copy_op_seq_id
58 | FUNCTION get_internal_copy_type
59 | PROCEDURE lock_wdj
60 | |
61 | Revision |
62 | 04/24/00 Anirban Dey Initial Creation |
63 +==========================================================================*/
64
65
66
67 /***************************************************************************************/
68
69 FUNCTION CHECK_WSM_ORG (
70 p_organization_id IN NUMBER,
71 x_err_code OUT NOCOPY NUMBER,
72 x_err_msg OUT NOCOPY VARCHAR2
73 )
74 RETURN INTEGER
75 IS
76 l_stmt_num NUMBER := 0;
77 l_rowcount NUMBER := 0;
78 BEGIN
79
80 x_err_code := 0;
81 x_err_msg := '';
82 l_stmt_num := 10;
83 /*
84 ** commented out by Bala Balakumar, June 01, 2000.
85 SELECT count(*)
86 INTO l_rowcount
87 FROM MTL_PARAMETERS MP
88 WHERE MP.ORGANIZATION_ID = p_organization_id
89 AND UPPER(WSM_ENABLED_FLAG)='Y';
90 */
91
92 /* Check_wsm_org should also include a check to
93 ** see if a record exists in wsm_parameters table
94 */
95
96 SELECT count(*)
97 INTO l_rowcount
98 FROM MTL_PARAMETERS MP, WSM_PARAMETERS WSM
99 WHERE WSM.ORGANIZATION_ID = p_organization_id
100 AND MP.ORGANIZATION_ID = WSM.ORGANIZATION_ID
101 AND UPPER(MP.WSM_ENABLED_FLAG)='Y';
102
103 x_err_code := 0;
104 x_err_msg := 'WSMPUTIL.CHECK_WSM_ORG: Success';
105 return(l_rowcount);
106
107 EXCEPTION
108 WHEN OTHERS THEN
109 x_err_code := SQLCODE;
110 x_err_msg := 'WSMPUTIL.CHECK_WSM_ORG: (stmt_num='||l_stmt_num||'): '||SUBSTR(SQLERRM,1,60);
111 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
112 return(0);
113
114 END CHECK_WSM_ORG;
115
116
117 /***************************************************************************************/
118
119 -- CZH.I_OED-2, currently, the primary key of BON is (FROM_OP_SEQ_ID, TO_OP_SEQ_ID)
120 -- if the operation of START_OP_SEQ_ID is not effective as of P_ROUTING_REV_DATE,
121 -- find_routing_start() will call replacement_op_seq_id() to see if it has a replacement
122 -- or not. IF it has a replacement, find_routing_start() will return x_err_code = 0,
123 -- NOT -3, however, the START_OP_SEQ_ID returned will STILL be the one defined in BON
124
125
126 PROCEDURE find_routing_start(
127 p_routing_sequence_id NUMBER,
128 start_op_seq_id OUT NOCOPY NUMBER,
129 x_err_code OUT NOCOPY NUMBER,
130 x_err_msg OUT NOCOPY VARCHAR2) IS
131 l_rtg_rev_date DATE := SYSDATE;
132 BEGIN
133 find_routing_start(
134 p_routing_sequence_id,
135 l_rtg_rev_date,
136 start_op_seq_id,
137 x_err_code,
138 x_err_msg);
139 END;
140
141 -- CZH.I_OED-1, override function
142 PROCEDURE find_routing_start(
143 p_routing_sequence_id NUMBER,
144 p_routing_rev_date DATE,
145 start_op_seq_id OUT NOCOPY NUMBER,
146 x_err_code OUT NOCOPY NUMBER,
147 x_err_msg OUT NOCOPY VARCHAR2) IS
148
149 l_eff_date DATE; -- ADD: CZH.I_OED-1
150 l_dis_date DATE; -- ADD: CZH.I_OED-1
151 l_rtg_rev_date DATE; -- ADD: CZH I_OED-1
152 l_count number; -- ADD: CZH.I_OED-1.BUG2558058
153
154 -- CZH I_OED-1: 07/03/02
155 -- this cursor finds a bon.from_op_seq_id, which is in BOS and effective
156 -- and is not in BON.to_op_seq_id
157 --
158 -- bon.disable/effectivity_date is never used before I project. They will
159 -- be used in the stage 2 of this project.
160 --
161 -- For stage 1, we will find the start of the routing without
162 -- considering the effective/disable date of the operations in BOS
163
164 -- BC: CZH I_OED-1
165 CURSOR get_start IS
166 SELECT UNIQUE bon.from_op_seq_id
167 FROM bom_operation_networks bon
168 WHERE bon.from_op_seq_id IN (
169 SELECT operation_sequence_id
170 FROM bom_operation_sequences
171 WHERE routing_sequence_id = p_routing_sequence_id
172 )
173 AND NOT EXISTS ( --bon.from_op_seq_id NOT IN
174 SELECT 'X' --unique bon1.to_op_seq_id
175 FROM bom_operation_networks bon1
176 WHERE bon1.to_op_seq_id = bon.from_op_seq_id
177 AND EXISTS ( --bon1.to_op_seq_id IN
178 SELECT 'X' --operation_sequence_id
179 FROM bom_operation_sequences
180 WHERE bon1.to_op_seq_id = operation_sequence_id
181 AND routing_sequence_id = p_routing_sequence_id
182 )
183 );
184 -- EC: CZH I_OED-1:
185
186 BEGIN
187
188 x_err_code := 0;
189 l_rtg_rev_date := NVL(p_routing_rev_date, SYSDATE); -- CZH I_OED-1
190
191 -- BC: CZH I_OED-1
192
193 -- BA: CZH.I_OED-1.BUG2558058, no network defined
194 -- error_code -1 is reserved for WSM_NO_NETWORK_EXISTS
195 SELECT count(*)
196 INTO l_count
197 FROM bom_operation_networks bon
198 WHERE bon.from_op_seq_id IN (
199 SELECT operation_sequence_id
200 FROM bom_operation_sequences
201 WHERE routing_sequence_id = p_routing_sequence_id
202 );
203 IF(l_count = 0) THEN
204 x_err_code := -1;
205 FND_MESSAGE.SET_NAME('WSM','WSM_NO_NETWORK_EXISTS');
206 x_err_msg := FND_MESSAGE.GET;
207 RETURN;
208 END IF;
209 -- EA: CZH.I_OED-1.BUG2558058
210
211 OPEN get_start;
212
213 FETCH get_start INTO start_op_seq_id;
214
215 IF get_start%NOTFOUND THEN
216 x_err_code := -2; -- CZH I_OED-1, BUG2558058 changed to -2
217 FND_MESSAGE.SET_NAME('WSM','WSM_NET_START_NOT_FOUND');
218 x_err_msg:= FND_MESSAGE.GET;
219 RETURN;
220 END IF;
221
222 LOOP
223 IF get_start%ROWCOUNT >1 THEN
224 x_err_code := -2; -- CZH I_OED-1
225 FND_MESSAGE.SET_NAME('WSM','WSM_NET_MULTIPLE_STARTS');
226 x_err_msg:= FND_MESSAGE.GET;
227 RETURN;
228 END IF;
229
230 FETCH get_start INTO start_op_seq_id;
231 EXIT WHEN get_start%NOTFOUND;
232 END LOOP;
233
234 CLOSE get_start;
235
236 -- CZH.I_OED-1, check if the start_op is effective or not
237 -- BC: CZH.I_OED-2, if it has a replacement, do not error out with x_err_code -3
238 -- We will not return the replacement op_seq_id either, because in BON,
239 -- start_op_seq_id is the 'START', and some cursors rely on this
240 SELECT effectivity_date,
241 nvl(disable_date, l_rtg_rev_date+2)
242 INTO l_eff_date,
243 l_dis_date
244 FROM bom_operation_sequences
245 WHERE routing_sequence_id = p_routing_sequence_id
246 AND operation_sequence_id = start_op_seq_id;
247
248 --IF (l_eff_date > l_rtg_rev_date OR l_dis_date <= l_rtg_rev_date) THEN
249 IF (l_rtg_rev_date NOT Between l_eff_date and l_dis_date ) THEN -- HH24MISS Add
250 IF(NVL(WSMPUTIL.replacement_op_seq_id(
251 start_op_seq_id,
252 l_rtg_rev_date), -1) = -1) THEN -- ADD: CZH.I_OED-2
253 x_err_code := -3;
254 FND_MESSAGE.SET_NAME('WSM','WSM_NET_START_NOT_EFFECTIVE');
255 x_err_msg:= FND_MESSAGE.GET;
256 RETURN;
257 END IF; -- ADD: CZH.I_OED-2
258 END IF;
259
260 -- EC: CZH I_OED-1
261 /*
262 -- OSP Begin Changes
263
264 If check_po_move (
265 p_sequence_id => p_routing_sequence_id,
266 p_sequence_id_type => 'R' ,
267 p_routing_rev_date => l_rtg_rev_date,
268 x_err_code => x_err_code ,
269 x_err_msg => x_err_msg ) then
270
271 x_err_code := -4;
272 FND_MESSAGE.SET_NAME('WSM','WSM_FIRST_OP_PO_MOVE');
273 x_err_msg:= FND_MESSAGE.GET;
274 RETURN;
275 end if;
276
277 -- OSP End Changes
278 */
279 EXCEPTION
280 WHEN OTHERS THEN
281 x_err_code := SQLCODE;
282 x_err_msg := 'WSMPUTIL.FIND_ROUTING_START '|| SUBSTR(SQLERRM,1,60);
283 -- FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
284 RETURN;
285
286 END find_routing_start;
287
288
289 /*****************************************************************************/
290
291 -- CZH.I_OED-2, currently, the primary key of BON is (FROM_OP_SEQ_ID, TO_OP_SEQ_ID)
292 -- if the operation of END_OP_SEQ_ID is not effective as of P_ROUTING_REV_DATE,
293 -- find_routing_end() will call replacement_op_seq_id() to see if it has a replacement
294 -- or not. IF it has a replacement, find_routing_start() will return x_err_code = 0,
295 -- NOT -3, however, the END_OP_SEQ_ID returned will STILL be the one defined in BON
296
297 PROCEDURE find_routing_end(
298 p_routing_sequence_id NUMBER,
299 end_op_seq_id OUT NOCOPY NUMBER,
300 x_err_code OUT NOCOPY NUMBER,
301 x_err_msg OUT NOCOPY VARCHAR2) IS
302 l_rtg_rev_date DATE := SYSDATE;
303 BEGIN
304 find_routing_end(
305 p_routing_sequence_id,
306 l_rtg_rev_date,
307 end_op_seq_id,
308 x_err_code,
309 x_err_msg);
310 END;
311
312
313 -- CZH.I_OED-1, override function
314 PROCEDURE find_routing_end (
315 p_routing_sequence_id NUMBER,
316 p_routing_rev_date DATE,
317 end_op_seq_id OUT NOCOPY NUMBER,
318 x_err_code OUT NOCOPY NUMBER,
319 x_err_msg OUT NOCOPY VARCHAR2 ) IS
320
321 l_eff_date DATE; -- ADD: CZH.I_OED-1
322 l_dis_date DATE; -- ADD: CZH.I_OED-1
323 l_rtg_rev_date DATE; -- ADD: CZH I_OED-1
324 l_count number; -- ADD: CZH.I_OED-1.BUG2558058
325
326
327 -- CZH.I_OED-1: 07/03/02
328 -- For stage 1, we will find the end of the routing without
329 -- considering the effective/disable date of the operations in BOS
330
331 -- BC: CZH I_OED-1
332 CURSOR get_end IS
333 SELECT UNIQUE bon.to_op_seq_id
334 FROM bom_operation_networks bon
335 WHERE bon.to_op_seq_id IN (
336 SELECT operation_sequence_id
337 FROM bom_operation_sequences
338 WHERE routing_sequence_id = p_routing_sequence_id
339 )
340 AND NOT EXISTS ( --bon.from_op_seq_id NOT IN
341 SELECT 'X' --unique bon1.to_op_seq_id
342 FROM bom_operation_networks bon1
343 WHERE bon1.from_op_seq_id = bon.to_op_seq_id
344 AND EXISTS ( --bon1.to_op_seq_id IN
345 SELECT 'X' --operation_sequence_id
346 FROM bom_operation_sequences
347 WHERE bon1.from_op_seq_id = operation_sequence_id
348 AND routing_sequence_id = p_routing_sequence_id
349 )
350 );
351 -- EC: CZH I_OED-1
352
353 BEGIN
354
355 x_err_code := 0;
356 l_rtg_rev_date := NVL(p_routing_rev_date, SYSDATE); -- CZH I_OED-1
357
358
359 -- BC: CZH I_OED-1
360
361 -- BA: CZH.I_OED-1.BUG2558058, no network defined
362 -- error_code -1 is reserved for WSM_NO_NETWORK_EXISTS
363 SELECT count(*)
364 INTO l_count
365 FROM bom_operation_networks bon
366 WHERE bon.from_op_seq_id IN (
367 SELECT operation_sequence_id
368 FROM bom_operation_sequences
369 WHERE routing_sequence_id = p_routing_sequence_id
370 );
371 IF(l_count = 0) THEN
372 x_err_code := -1;
373 FND_MESSAGE.SET_NAME('WSM','WSM_NO_NETWORK_EXISTS');
374 x_err_msg := FND_MESSAGE.GET;
375 RETURN;
376 END IF;
377 -- EA: CZH.I_OED-1.BUG2558058
378
379 OPEN get_end;
380 FETCH get_end INTO end_op_seq_id;
381
382 IF get_end%NOTFOUND THEN
383 x_err_code := -2; -- CZH I_OED-1, BUG2558058 changed to -2
384 FND_MESSAGE.SET_NAME('WSM','WSM_NET_END_NOT_FOUND');
385 x_err_msg:= FND_MESSAGE.GET;
386 RETURN;
387 END IF;
388
389 LOOP
390 IF get_end%ROWCOUNT >1 THEN
391 x_err_code := -2; -- CZH I_OED-1
392 FND_MESSAGE.SET_NAME('WSM','WSM_NET_MULTIPLE_ENDS');
393 x_err_msg:= FND_MESSAGE.GET;
394 RETURN;
395 END IF;
396 FETCH get_end INTO end_op_seq_id;
397 EXIT WHEN get_end%NOTFOUND;
398 END LOOP;
399
400 CLOSE get_end;
401
402 -- CZH.I_OED-1: check if the end_op is effective or not
403 -- BC: CZH.I_OED-2, if it has a replacement, do not error out with x_err_code -3.
404 -- We will not return the replacement op_seq_id either, because in BON,
405 -- end_op_seq_id is the 'END', and some cursors rely on this
406 SELECT effectivity_date,
407 nvl(disable_date, l_rtg_rev_date+2)
408 INTO l_eff_date,
409 l_dis_date
410 FROM bom_operation_sequences
411 WHERE routing_sequence_id = p_routing_sequence_id
412 AND operation_sequence_id = end_op_seq_id;
413
414 --IF (l_eff_date > l_rtg_rev_date OR l_dis_date <= l_rtg_rev_date) THEN
415 IF (l_rtg_rev_date NOT Between l_eff_date and l_dis_date ) THEN -- HH24MISS Add
416 IF(NVL(WSMPUTIL.replacement_op_seq_id(
417 end_op_seq_id,
418 l_rtg_rev_date), -1) = -1) THEN -- ADD: CZH.I_OED-2
419 x_err_code := -3;
420 FND_MESSAGE.SET_NAME('WSM','WSM_NET_END_NOT_EFFECTIVE');
421 x_err_msg:= FND_MESSAGE.GET;
422 RETURN;
423 END IF; -- ADD: CZH.I_OED-2
424 END IF;
425
426 -- EC: CZH I_OED-1
427
428 EXCEPTION
429 WHEN OTHERS THEN
430 x_err_code := SQLCODE;
431 x_err_msg := 'WSMPUTIL.FIND_ROUTING_END '|| SUBSTR(SQLERRM,1,60);
432 -- BD: 1964044 -- FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg); -- ED: 1964044
433 RETURN;
434
435 END find_routing_end;
436
437
438
439 /*****************************************************************************/
440
441 --
442 -- This is an over-loaded function which calls the same function
443 -- with p_quantity parameter.
444 --
445 -- This is created to circumvent the dependency issues with forms and other objects
446 --
447
448 FUNCTION GET_SCHEDULED_DATE
449 (
450 p_organization_id IN NUMBER,
451 p_primary_item_id IN NUMBER,
452 p_schedule_method IN VARCHAR2,
453 p_input_date IN DATE,
454 x_err_code OUT NOCOPY NUMBER,
455 x_err_msg OUT NOCOPY VARCHAR2
456 )
457 RETURN DATE IS
458
459 x_output_date DATE;
460
461 BEGIN
462 x_output_date := Get_Scheduled_Date
463 (
464 p_organization_id => p_organization_id,
465 p_primary_item_id => p_primary_item_id,
466 p_schedule_method => p_schedule_method,
467 p_input_date => p_input_date,
468 x_err_code => x_err_code,
469 x_err_msg => x_err_msg,
470 p_quantity => 0
471 );
472
473 return x_output_date;
474
475 END GET_SCHEDULED_DATE;
476
477
478 /*****************************************************************************/
479
480 --
481 -- Since this is an overloaded function, we shouldn't have
482 -- DEFAULT clause on p_quantity. Else, you'll get the following error
483 -- while calling this function.
484 -- PLS-00307: too many declarations of 'GET_SCHEDULED_DATE'
485 -- match this call
486 --
487
488 FUNCTION GET_SCHEDULED_DATE (
489 p_organization_id IN NUMBER,
490 p_primary_item_id IN NUMBER,
491 p_schedule_method IN VARCHAR2,
492 p_input_date IN DATE,
493 x_err_code OUT NOCOPY NUMBER,
494 x_err_msg OUT NOCOPY VARCHAR2,
495 p_quantity IN NUMBER
496 )
497 RETURN DATE
498 IS
499 x_output_date DATE;
500 l_lead_time NUMBER;
501 l_cum_mfg_lead_time NUMBER;
502 l_stmt_num NUMBER;
503 l_fixed_lead_time NUMBER;
504 l_variable_lead_time NUMBER;
505
506
507 CURSOR forward_cur(p_lead_time NUMBER) IS
508 SELECT BCD1.CALENDAR_DATE
509 FROM BOM_CALENDAR_DATES BCD1,
510 BOM_CALENDAR_DATES BCD2,
511 MTL_PARAMETERS MP
512 WHERE MP.ORGANIZATION_ID = p_organization_id
513 AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
514 AND BCD2.CALENDAR_CODE = MP.CALENDAR_CODE
515 AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
516 AND BCD2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
517 AND BCD2.CALENDAR_DATE = TRUNC(p_input_date)
518 AND BCD1.SEQ_NUM = NVL(BCD2.SEQ_NUM, BCD2.NEXT_SEQ_NUM) +
519 CEIL(p_lead_time);
520
521 CURSOR backward_cur(p_lead_time NUMBER) IS
522 SELECT BCD1.CALENDAR_DATE
523 FROM BOM_CALENDAR_DATES BCD1,
524 BOM_CALENDAR_DATES BCD2,
525 MTL_PARAMETERS MP
526 WHERE MP.ORGANIZATION_ID = p_organization_id
527 AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
528 AND BCD2.CALENDAR_CODE = MP.CALENDAR_CODE
529 AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
530 AND BCD2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
531 AND BCD2.CALENDAR_DATE = TRUNC(p_input_date)
532 AND BCD1.SEQ_NUM = NVL(BCD2.SEQ_NUM, BCD2.PRIOR_SEQ_NUM) +
533 DECODE(p_lead_time, 0, 0, 1-CEIL(p_lead_time));
534 /* Bugfix: 1383041
535 PrevStmt: 1 - CEIL(p_lead_time); */
536
537
538 BEGIN
539
540 l_stmt_num := 10;
541
542 -- SELECT NVL(MSI.CUM_MANUFACTURING_LEAD_TIME, 0)
543 -- SELECT NVL(MSI.full_lead_time, 0)
544 -- INTO l_cum_mfg_lead_time
545 -- FROM MTL_SYSTEM_ITEMS MSI
546 -- WHERE MSI.organization_id = p_organization_id
547 -- AND MSI.inventory_item_id = p_primary_item_id;
548
549 SELECT nvl(fixed_lead_time,0), nvl(variable_lead_time,0)
550 INTO l_fixed_lead_time, l_variable_lead_time
551 FROM MTL_SYSTEM_ITEMS
552 WHERE organization_id = p_organization_id
553 AND inventory_item_id = p_primary_item_id;
554
555 IF (SQL%NOTFOUND) THEN
556 l_cum_mfg_lead_time := 0;
557 END IF;
558
559 l_cum_mfg_lead_time := l_fixed_lead_time + l_variable_lead_time * p_quantity;
560
561 IF (UPPER(p_schedule_method) = 'F') THEN
562 l_stmt_num := 20;
563
564 OPEN forward_cur(l_cum_mfg_lead_time);
565 FETCH forward_cur INTO x_output_date;
566 IF (forward_cur%NOTFOUND) THEN
567 x_output_date := SYSDATE;
568 END IF;
569 CLOSE forward_cur;
570 x_err_code := 0;
571 -- RETURN x_output_date+( p_input_date - TRUNC(p_input_date));
572 ELSIF (UPPER(p_schedule_method) = 'B') THEN
573 l_stmt_num := 30;
574
575 OPEN backward_cur(l_cum_mfg_lead_time);
576 FETCH backward_cur INTO x_output_date;
577 IF (backward_cur%NOTFOUND) THEN
578 x_output_date := SYSDATE;
579 END IF;
580 CLOSE backward_cur;
581 x_err_code := 0;
582 -- RETURN x_output_date + ( p_input_date - TRUNC(p_input_date));
583 ELSE
584 l_stmt_num := 40;
585
586 x_output_date := SYSDATE;
587 x_err_code := 0;
588 -- RETURN x_output_date + ( p_input_date - TRUNC(p_input_date));
589 END IF;
590
591 RETURN x_output_date + ( p_input_date - TRUNC(p_input_date));
592
593 l_stmt_num := 50;
594
595
596 EXCEPTION
597 WHEN OTHERS THEN
598 x_err_code := SQLCODE;
599 x_err_msg := 'WSMPUTIL.GET_SCHEDULED_DATE('||l_stmt_num||
600 '): '|| SUBSTR(SQLERRM,1,60);
601 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
602 return SYSDATE;
603
604 END GET_SCHEDULED_DATE;
605
606
607 /*****************************************************************************/
608
609
610 FUNCTION GET_DEF_ACCT_CLASS_CODE (
611 p_organization_id IN NUMBER,
612 p_inventory_item_id IN NUMBER,
613 p_subinventory_name IN VARCHAR2,
614 x_err_code OUT NOCOPY NUMBER,
615 x_err_msg OUT NOCOPY VARCHAR2
616 )
617 RETURN VARCHAR2
618 IS
619 l_stmt_num NUMBER;
620 x_accounting_class_code VARCHAR2(10);
621
622 BEGIN
623 x_err_code := 0;
624 x_err_msg := NULL;
625 l_stmt_num := 10;
626
627 BEGIN
628 SELECT WSE.DEFAULT_ACCT_CLASS_CODE
629 INTO x_accounting_class_code
630 FROM WSM_SECTOR_EXTENSIONS WSE,
631 WSM_ITEM_EXTENSIONS WIE
632 WHERE WIE.organization_id = p_organization_id
633 AND WIE.inventory_item_id = p_inventory_item_id
634 AND WIE.sector_extension_id = WSE.sector_extension_id
635 AND WSE.organization_id = WIE.organization_id;
636
637 IF x_accounting_class_code IS NOT NULL THEN
638 RETURN x_accounting_class_code;
639 END IF;
640
641 EXCEPTION
642 WHEN NO_DATA_FOUND THEN
643 x_accounting_class_code := NULL;
644 END;
645
646 BEGIN
647 IF x_accounting_class_code IS NULL THEN
648 SELECT WSE.DEFAULT_ACCT_CLASS_CODE
649 INTO x_accounting_class_code
650 FROM WSM_SECTOR_EXTENSIONS WSE,
651 WSM_SUBINVENTORY_EXTENSIONS WSUE
652 WHERE WSUE.organization_id = p_organization_id
653 AND WSUE.secondary_inventory_name = p_subinventory_name
654 AND WSUE.sector_extension_id = WSE.sector_extension_id
655 AND WSUE.organization_id = WSE.organization_id;
656 END IF;
657
658 IF x_accounting_class_code IS NOT NULL THEN
659 RETURN x_accounting_class_code;
660 END IF;
661 EXCEPTION
662 WHEN NO_DATA_FOUND THEN
663 x_accounting_class_code := NULL;
664 END ;
665
666 BEGIN
667 IF x_accounting_class_code IS NULL THEN
668 SELECT default_acct_class_code
669 INTO x_accounting_class_code
670 from wsm_parameters
671 WHERE organization_id = p_organization_id;
672 END IF;
673
674 IF x_accounting_class_code IS NOT NULL THEN
675 RETURN x_accounting_class_code;
676 END IF;
677
678 EXCEPTION
679 WHEN NO_DATA_FOUND THEN
680 x_accounting_class_code := NULL;
681 END;
682
683 IF x_accounting_class_code IS NULL THEN
684 RETURN NULL;
685 END IF;
686
687 EXCEPTION
688 WHEN OTHERS THEN
689 x_err_code := SQLCODE;
690 x_err_msg := 'WSMPTUIL.GET_DEF_ACCT_CLASS_CODE('||l_stmt_num||'): '||SUBSTR(SQLERRM,1,60);
691 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
692 return NULL;
693 END GET_DEF_ACCT_CLASS_CODE;
694
695
696 /*****************************************************************************/
697
698 PROCEDURE GET_DEF_COMPLETION_SUB_DTLS (
699 p_organization_id IN NUMBER,
700 p_routing_sequence_id IN NUMBER,
701 x_subinventory_code OUT NOCOPY VARCHAR2,
702 x_locator_id OUT NOCOPY NUMBER,
703 x_err_code OUT NOCOPY NUMBER,
704 x_err_msg OUT NOCOPY VARCHAR2
705 ) IS
706 BEGIN
707 GET_DEF_COMPLETION_SUB_DTLS (
708 p_organization_id,
709 p_routing_sequence_id,
710 SYSDATE,
711 x_subinventory_code,
712 x_locator_id,
713 x_err_code,
714 x_err_msg
715 );
716 END;
717
718
719 -- CZH: overloading function
720
721 PROCEDURE GET_DEF_COMPLETION_SUB_DTLS (
722 p_organization_id IN NUMBER,
723 p_routing_sequence_id IN NUMBER,
724 p_routing_revision_date IN DATE,
725 x_subinventory_code OUT NOCOPY VARCHAR2,
726 x_locator_id OUT NOCOPY NUMBER,
727 x_err_code OUT NOCOPY NUMBER,
728 x_err_msg OUT NOCOPY VARCHAR2
729 ) IS
730 l_stmt_num NUMBER;
731 l_operation_seq_id NUMBER;
732
733 -- BA NSO-WLT
734 x_standard_operation_id NUMBER;
735 not_std_op EXCEPTION;
736 -- EA NSO-WLT
737
738 BEGIN
739 x_subinventory_code := NULL;
740 x_err_code := 0;
741 x_err_msg := NULL;
742 x_locator_id := NULL;
743
744 l_stmt_num := 10;
745
746
747 FIND_ROUTING_END(
748 p_routing_sequence_id,
749 p_routing_revision_date, --ADD: CZH
750 l_operation_seq_id,
751 x_err_code,
752 x_err_msg);
753
754 IF (x_err_code <> 0 ) THEN
755 x_subinventory_code := NULL;
756 x_locator_id := NULL;
757 return;
758 END IF;
759
760 --BA: CZH.I_OED-2, consider replacement
761 l_operation_seq_id := WSMPUTIL.replacement_op_seq_id(
762 l_operation_seq_id,
763 p_routing_revision_date);
764 --EA: CZH.I_OED-2
765
766 IF (l_operation_seq_id IS NOT NULL) THEN
767 -- BA NSO-WLT
768 x_standard_operation_id := 0;
769 l_stmt_num := 15;
770
771 SELECT nvl(standard_operation_id, -999)
772 INTO x_standard_operation_id
773 FROM bom_operation_sequences
774 WHERE operation_sequence_id = l_operation_seq_id;
775
776 IF x_standard_operation_id <> -999 then
777 -- EA NSO-WLT
778
779 l_stmt_num := 20;
780 --MES replacing WSM_OPERATION_DETAILS with BOM_STANDARD_OPERATIONS
781 /*
782 -- BA NSO-WLT
783 SELECT WOD.SECONDARY_INVENTORY_NAME,
784 WOD.INVENTORY_LOCATION_ID
785 INTO x_subinventory_code,
786 x_locator_id
787 FROM WSM_OPERATION_DETAILS WOD,
788 BOM_OPERATION_SEQUENCES BOS
789 WHERE BOS.operation_sequence_id = l_operation_seq_id
790 AND BOS.routing_sequence_id = p_routing_sequence_id
791 AND nvl(WOD.standard_operation_id, -999) = nvl(BOS.standard_operation_id, -999)
792 AND WOD.organization_id = p_organization_id;
793 -- EA NSO-WLT
794 */
795 -- BA NSO-WLT
796 SELECT BSO.DEFAULT_SUBINVENTORY,
797 BSO.DEFAULT_LOCATOR_ID
798 INTO x_subinventory_code,
799 x_locator_id
800 FROM BOM_STANDARD_OPERATIONS BSO,
801 BOM_OPERATION_SEQUENCES BOS
802 WHERE BOS.operation_sequence_id = l_operation_seq_id
803 AND BOS.routing_sequence_id = p_routing_sequence_id
804 AND nvl(BSO.standard_operation_id, -999) = nvl(BOS.standard_operation_id, -999)
805 AND BSO.organization_id = p_organization_id;
806 -- EA NSO-WLT
807
808 IF (SQL%NOTFOUND) THEN
809 l_stmt_num := 30;
810 x_subinventory_code := NULL;
811 x_locator_id := NULL;
812 return;
813 END IF;
814
815 -- BA NSO-WLT
816 ELSE
817 l_stmt_num := 35;
818 raise NOT_STD_OP;
819 END IF;
820 -- EA NSO-WLT
821
822 END IF;
823
824 l_stmt_num := 40;
825
826 RETURN;
827
828
829 EXCEPTION
830
831 -- BA NSO-WLT
832 WHEN NOT_STD_OP THEN
833 x_subinventory_code := NULL;
834 x_locator_id := NULL;
835 x_err_code := SQLCODE;
836 x_err_msg := 'WSMPTUIL.GET_DEF_COMPLETION_SUB_DTLS('||l_stmt_num||
837 '): '|| SUBSTR(SQLERRM,1,60);
838 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
839 return ;
840 -- EA NSO-WLT
841
842 WHEN OTHERS THEN
843 x_err_code := SQLCODE;
844 x_err_msg := 'WSMPTUIL.GET_DEF_COMPLETION_SUB_DTLS('||l_stmt_num||
845 '): '|| SUBSTR(SQLERRM,1,60);
846 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
847 return ;
848
849 END GET_DEF_COMPLETION_SUB_DTLS;
850
851
852 /*********************************************************************/
853
854
855 FUNCTION primary_loop_test (
856 p_routing_sequence_id NUMBER,
857 start_id NUMBER,
858 end_id NUMBER,
859 x_err_code OUT NOCOPY NUMBER,
860 x_err_msg OUT NOCOPY VARCHAR2 )
861 RETURN NUMBER IS
862
863
864 x_from_id NUMBER;
865 x_meet_num NUMBER;
866 x_temp NUMBER;
867 dumnum NUMBER;
868 p_count NUMBER;
869 l_st_num NUMBER;
870
871 BEGIN
872
873
874 l_st_num := 5;
875
876 -- BA: bug 3170719
877 -- if two records in BON has the same op_seq_num but different op_seq_id
878 -- we will error out
879 declare
880 cursor get_ops is
881 select bos.operation_seq_num op_seq_num,
882 bos.operation_sequence_id op_seq_id
883 from bom_operation_networks bon,
884 bom_operation_sequences bos
885 where bos.routing_sequence_id = p_routing_sequence_id
886 and bon.from_op_seq_id = bos.operation_sequence_id
887 union
888 select bos.operation_seq_num op_seq_num,
889 bos.operation_sequence_id op_seq_id
890 from bom_operation_networks bon,
891 bom_operation_sequences bos
892 where bos.routing_sequence_id = p_routing_sequence_id
893 and bon.to_op_seq_id = bos.operation_sequence_id;
894
895 type t_number is table of number index by binary_integer;
896 op_seq_ids t_number;
897 begin
898 op_seq_ids.delete;
899 for op_rec in get_ops loop
900 if op_seq_ids.exists(op_rec.op_seq_num) then
901 FND_MESSAGE.SET_NAME('WSM','WSM_NET_DUP_OP_SEQ_NUM');
902 x_err_msg:= FND_MESSAGE.GET;
903 RETURN 1;
904 else
905 op_seq_ids(op_rec.op_seq_num) := op_rec.op_seq_id;
906 end if;
907 end loop;
908 end;
909 -- EA: bug 3170719
910
911
912 l_st_num := 10;
913
914 BEGIN
915
916 SELECT 1
917 INTO dumnum
918 FROM bom_operation_networks
919 WHERE from_op_seq_id = start_id
920 AND transition_type = 1;
921
922 EXCEPTION
923 WHEN NO_DATA_FOUND THEN
924
925 FND_MESSAGE.SET_NAME('WSM','WSM_START_SHOULD_BE_PRIMARY');
926 x_err_msg:= FND_MESSAGE.GET;
927 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
928 RETURN 1;
929
930 WHEN TOO_MANY_ROWS THEN
931
932 FND_MESSAGE.SET_NAME('WSM','WSM_MULT_PRIMARY_STARTS');
933 x_err_msg:= FND_MESSAGE.GET;
934 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
935 RETURN 1;
936 END;
937
938 l_st_num := 20;
939
940 BEGIN
941
942 SELECT 1
943 INTO dumnum
944 FROM bom_operation_networks
945 WHERE to_op_seq_id = end_id
946 AND transition_type =1 ;
947
948 EXCEPTION
949 WHEN NO_DATA_FOUND THEN
950
951 FND_MESSAGE.SET_NAME('WSM','WSM_END_SHOULD_BE_PRIMARY');
952 x_err_msg := FND_MESSAGE.GET;
953 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
954 RETURN 1;
955
956 WHEN TOO_MANY_ROWS THEN
957 FND_MESSAGE.SET_NAME('WSM','WSM_MULT_PRIMARY_ENDS');
958 x_err_msg:= FND_MESSAGE.GET;
959 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
960 RETURN 1;
961
962 END;
963
964 -- OSFMAPSP2 Integration -- Add -- Start - BBK.
965
966 l_st_num := 25;
967
968 Declare
969
970 l_from_opseq_num NUMBER;
971 l_to_opseq_num NUMBER;
972
973 Begin
974
975 select bos1.operation_seq_num
976 , bos2.operation_seq_num
977 into l_from_opseq_num, l_to_opseq_num
978 from bom_operation_networks bon
979 , bom_operation_sequences bos1
980 , bom_operation_sequences bos2
981 where bos1.routing_sequence_id = p_routing_sequence_id
982 and bos2.routing_sequence_id = bos1.routing_sequence_id
983 and bon.from_op_seq_id = bos1.operation_sequence_id
984 and bos2.operation_sequence_id = bon.to_op_seq_id
985 group by bos1.routing_sequence_id
986 , bos1.operation_seq_num
987 , bos2.operation_seq_num
988 having count(bon.from_op_seq_id) > 1;
989
990 If sql%rowcount <> 0 Then
991 FND_MESSAGE.SET_NAME('WSM','WSM_DUPLICATE_LINK');
992 fnd_message.set_token('FROM_OPSEQ_NUM', l_from_opseq_num);
993 fnd_message.set_token('TO_OPSEQ_NUM', l_to_opseq_num);
994 x_err_msg:= FND_MESSAGE.GET;
995 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
996 RETURN 1;
997 End If;
998
999 EXCEPTION
1000 WHEN NO_DATA_FOUND THEN
1001 Null; -- NO duplicates found.
1002
1003 WHEN TOO_MANY_ROWS THEN
1004 FND_MESSAGE.SET_NAME('WSM','WSM_DUPLICATE_LINKS_EXIST');
1005 x_err_msg:= FND_MESSAGE.GET;
1006 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1007 RETURN 1;
1008
1009 END;
1010
1011
1012 -- OSFMAPSP2 Integration -- Add -- End - BBK.
1013
1014 l_st_num := 30;
1015
1016 BEGIN
1017
1018 SELECT count(*)
1019 INTO p_count
1020 FROM BOM_OPERATION_NETWORKS_V
1021 WHERE routing_sequence_id = p_routing_sequence_id
1022 AND transition_type = 1
1023 AND from_op_seq_id NOT IN
1024 (SELECT to_op_seq_id
1025 FROM BOM_OPERATION_NETWORKS_V
1026 WHERE routing_sequence_id = p_routing_sequence_id
1027 AND transition_type = 1 );
1028
1029 IF p_count > 1 THEN
1030 FND_MESSAGE.SET_NAME('WSM','WSM_MULT_PRIMARY_PATHS');
1031 x_err_msg:= FND_MESSAGE.GET;
1032 END IF;
1033
1034 END ;
1035
1036 l_st_num := 40;
1037
1038 x_from_id := start_id;
1039
1040 WHILE x_from_id <> end_id LOOP
1041
1042 BEGIN
1043 SELECT to_op_seq_id, to_seq_num
1044 INTO x_temp,x_meet_num
1045 FROM bom_operation_networks_v
1046 WHERE from_op_seq_id = x_from_id
1047 AND transition_type = 1;
1048
1049 EXCEPTION
1050 WHEN TOO_MANY_ROWS then
1051 FND_MESSAGE.SET_NAME('WSM','WSM_MULTIPLE_PRIMARY_PATHS_START');
1052 FND_MESSAGE.SET_TOKEN('WSM_SEQ_NUM',x_meet_num);
1053 x_err_msg:= FND_MESSAGE.GET;
1054 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1055 RETURN 1;
1056
1057 WHEN NO_DATA_FOUND THEN
1058 FND_MESSAGE.SET_NAME('WSM','WSM_PRIMARY_PATH_END_IMPROPER');
1059 FND_MESSAGE.SET_TOKEN('WSM_SEQ_NUM',x_meet_num);
1060 x_err_msg:= FND_MESSAGE.GET;
1061 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1062 RETURN 1;
1063 END;
1064
1065 l_st_num := 40;
1066
1067 BEGIN
1068 SELECT count(*)
1069 INTO p_count
1070 FROM BOM_OPERATION_NETWORKS
1071 WHERE to_op_seq_id = x_from_id
1072 AND transition_type = 1;
1073
1074 IF p_count > 1 THEN
1075 SELECT from_seq_num
1076 INTO x_meet_num
1077 FROM BOM_OPERATION_NETWORKS_V
1078 WHERE from_op_seq_id = x_from_id;
1079
1080 FND_MESSAGE.SET_NAME('WSM','WSM_MULT_PRIMARY_PATHS_MEET');
1081 FND_MESSAGE.SET_TOKEN('WSM_SEQ_NUM',x_meet_num);
1082 x_err_msg:= FND_MESSAGE.GET;
1083 END IF;
1084
1085 END;
1086
1087 x_from_id := x_temp;
1088
1089 END LOOP ;
1090
1091 RETURN 0;
1092
1093 l_st_num := 50;
1094
1095 EXCEPTION
1096
1097 WHEN OTHERS THEN
1098 x_err_code := SQLCODE;
1099 x_err_msg := 'WSMPTUIL.PRIMARY_LOOP_TEST.('||l_st_num||
1100 '): '|| SUBSTR(SQLERRM,1,60);
1101 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1102 RETURN 1;
1103
1104 END primary_loop_test;
1105
1106 /*************************************************************************/
1107
1108 PROCEDURE GET_DEFAULT_SUB_LOC ( p_org_id IN NUMBER ,
1109 p_routing_sequence_id IN NUMBER,
1110 p_end_id IN NUMBER,
1111 x_completion_subinventory OUT NOCOPY VARCHAR2,
1112 x_inventory_location_id OUT NOCOPY NUMBER,
1113 x_err_code OUT NOCOPY NUMBER,
1114 x_err_msg OUT NOCOPY VARCHAR2 ) IS
1115
1116
1117 x_standard_operation_id NUMBER;
1118 no_sub EXCEPTION;
1119
1120 -- BA NSO-WLT: Non-Standard Operations Project code change by Sadiq.
1121 not_std_op EXCEPTION;
1122 -- EA NSO-WLT
1123
1124 BEGIN
1125
1126 x_completion_subinventory := NULL;
1127 x_inventory_location_id := 0;
1128
1129 -- BA NSO-WLT
1130 SELECT nvl(standard_operation_id, '-999')
1131 INTO x_standard_operation_id
1132 FROM bom_operation_sequences
1133 WHERE operation_sequence_id = p_end_id;
1134
1135 IF x_standard_operation_id <> -999 then
1136 -- EA NSO-WLT
1137 --MES replacing WSM_OPERATION_DETAILS with BOM_STANDARD_OPERATIONS
1138 /*
1139 SELECT secondary_inventory_name, inventory_location_id
1140 INTO x_completion_subinventory,x_inventory_location_id
1141 FROM wsm_operation_details
1142 WHERE standard_operation_id = x_standard_operation_id
1143 AND organization_id = p_org_id;
1144 */
1145 SELECT DEFAULT_SUBINVENTORY, DEFAULT_LOCATOR_ID
1146 INTO x_completion_subinventory,x_inventory_location_id
1147 FROM BOM_STANDARD_OPERATIONS
1148 WHERE standard_operation_id = x_standard_operation_id
1149 AND organization_id = p_org_id;
1150
1151 IF x_completion_subinventory IS NULL THEN
1152 RAISE NO_SUB;
1153 END IF;
1154
1155 -- BA NSO-WLT
1156 ELSE
1157 raise not_std_op;
1158 END IF;
1159 -- EA NSO-WLT
1160
1161 EXCEPTION
1162
1163 WHEN NO_SUB THEN
1164 x_err_code := -1;
1165 FND_MESSAGE.SET_NAME('WSM','WSM_END_OPERATION_STK_PT');
1166 x_err_msg:= FND_MESSAGE.GET||' '||x_completion_subinventory;
1167 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1168
1169 -- BA NSO-WLT
1170 WHEN NOT_STD_OP THEN
1171 x_err_code := -1;
1172 FND_MESSAGE.SET_NAME('WSM','WSM_END_OPERATION_STK_PT');
1173 x_err_msg:= FND_MESSAGE.GET||' '||x_completion_subinventory;
1174 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1175 -- EA NSO-WLT
1176
1177 WHEN NO_DATA_FOUND THEN
1178 x_err_code := -1;
1179 FND_MESSAGE.SET_NAME('WSM','WSM_END_OPERATION_STK_PT');
1180 x_err_msg:= FND_MESSAGE.GET||' '||x_completion_subinventory;
1181 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1182
1183 WHEN OTHERS THEN
1184 x_err_code := SQLCODE;
1185 x_err_msg := 'WSMPTUIL.DEFAULT_SUB_LOC:' || SUBSTR(SQLERRM,1,60);
1186 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1187
1188 END GET_DEFAULT_SUB_LOC;
1189
1190 /************************************************************************/
1191
1192
1193 PROCEDURE UPDATE_SUB_LOC ( p_routing_sequence_id IN NUMBER,
1194 p_completion_subinventory IN VARCHAR2,
1195 p_inventory_location_id IN NUMBER,
1196 x_err_code OUT NOCOPY NUMBER,
1197 x_err_msg OUT NOCOPY VARCHAR2 ) IS
1198
1199 BEGIN
1200 UPDATE bom_operational_routings
1201 SET completion_subinventory = p_completion_subinventory
1202 WHERE routing_sequence_id = p_routing_sequence_id;
1203
1204 UPDATE bom_operational_routings
1205 SET completion_locator_id = p_inventory_location_id
1206 WHERE routing_sequence_id = p_routing_sequence_id;
1207
1208 EXCEPTION
1209
1210 WHEN OTHERS THEN
1211 x_err_code := SQLCODE;
1212 x_err_msg := 'WSMPTUIL.UPDATE_SUB_LOC:' || SUBSTR(SQLERRM,1,60);
1213 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1214
1215 END UPDATE_SUB_LOC;
1216
1217
1218
1219
1220 /**************************************************************************/
1221
1222
1223 /*
1224 ** Function CHECK_IF_ORG_IS_VALID to check for the validity
1225 ** of an organization for use in WSM.
1226 ** The following checks are made;
1227 ** 1. Org should be a Standard Costing Method Org.
1228 ** 2. Org should have inventory item lot number set to NON-UNIQUE.
1229 ** 3. Org should have WIP Parameter lot number default type to JOBNAME.
1230 ** 4. Org should NOT be WPS enabled in WIP Parameter table.
1231 ** BA#1490834
1232 ** 5. Org should NOT be WMS enabled in Inventory Parameters.
1233 ** EA#1490834
1234 */
1235
1236 FUNCTION CHECK_IF_ORG_IS_VALID
1237 ( p_organization_id IN NUMBER,
1238 x_err_code OUT NOCOPY NUMBER,
1239 x_err_msg OUT NOCOPY VARCHAR2
1240 )
1241 RETURN INTEGER
1242 IS
1243
1244 l_stmt_num NUMBER := 0;
1245 l_rowcount NUMBER := 0;
1246
1247 l_lotNumberUniqueNess number default 0;
1248 -- 1:UniqueForItem, 2:NoUniqueness
1249
1250 l_primaryCostMethod number default 0;
1251 -- 1:Standard, 2: Average.
1252
1253 l_lotNumberDefaultType number default 0;
1254 -- 1:JobName, 2:InvRules.
1255
1256 l_wpsEnabledFlag number default 0;
1257 -- 1:Yes, 2:No.
1258
1259 /*BA#1490834*/
1260
1261 l_wmsEnabledFlag varchar2(1) default 'N';
1262 -- 1:Yes, 2:No.
1263
1264 e_wmsEnabled EXCEPTION;
1265 /*EA#1490834*/
1266
1267 e_lotNumberUniqueness EXCEPTION;
1268 e_primaryCostMethod EXCEPTION;
1269 e_lotNumberDefaultType EXCEPTION;
1270 e_wpsEnabled EXCEPTION;
1271
1272 -- Bug#2131807 PJM enabled check.
1273 e_pjmEnabled EXCEPTION;
1274 l_pjm_Enabled boolean default FALSE;
1275
1276
1277 Begin
1278
1279 l_stmt_num := l_stmt_num + 10;
1280
1281 Select MTL.LOT_NUMBER_UNIQUENESS,
1282 MTL.PRIMARY_COST_METHOD,
1283 WIP.LOT_NUMBER_DEFAULT_TYPE,
1284 WIP.USE_FINITE_SCHEDULER
1285 /*BA#1490834*/
1286 , MTL.WMS_ENABLED_FLAG
1287 /*EA#1490834*/
1288 into
1289 l_lotNumberUniqueNess,
1290 l_primaryCostMethod,
1291 l_lotNumberDefaultType,
1292 l_wpsEnabledFlag
1293 /*BA#1490834*/
1294 , l_wmsEnabledFlag
1295 /*EA#1490834*/
1296 From MTL_PARAMETERS MTL, WIP_PARAMETERS WIP
1297 Where MTL.organization_id = p_organization_id
1298 And MTL.organization_id = WIP.organization_id (+);
1299
1300 If l_primaryCostMethod <> 1 Then
1301 -- NON_STANDARD costing method
1302 fnd_message.set_name('WSM', 'WSM_ORG_NOT_STD_COST');
1303 raise e_primaryCostMethod;
1304
1305 ElsIf l_lotNumberUniqueness <> 2 Then
1306 -- LotNumber is NOT Non-UNIQUE
1307 fnd_message.set_name('WSM', 'WSM_ORG_LOT_NONUNIQUE');
1308 raise e_lotNumberUniqueness;
1309
1310 ElsIf l_lotNumberDefaultType <> 1 Then
1311 -- Default Type is NOT JOBNAME
1312 fnd_message.set_name('WSM', 'WSM_ORG_LOT_DEFAULT_TYPE');
1313 raise e_lotNumberDefaultType;
1314
1315 -- Start comments to fix bug #2006687
1316 -- Commented out the following check and moved this code to WSMFPARM.fmb
1317 -- This is done to enable Agilent to have a WPS enabled OSFM org.
1318 -- ElsIf NVL(l_wpsEnabledFlag, 2) = 1 Then
1319 -- -- Org is WPS Enabled. Raise exception.
1320 --
1321 -- fnd_message.set_name('WSM', 'WSM_ORG_WPS_ENABLED');
1322 -- raise e_wpsEnabled;
1323 -- End comments to fix bug #2006687
1324
1325 /*BA#1490834*/
1326 -- OSFM and WMS can coexist, so this check
1327 -- is being removed
1328
1329 -- ElsIf NVL(l_wmsEnabledFlag, 'N') = 'Y' Then
1330 -- -- Org is WMS Enabled. Raise exception.
1331 --
1332 -- fnd_message.set_name('WSM', 'WSM_ORG_WM$
1333 -- raise e_wmsEnabled;
1334 /*EA#1490834*/
1335 Else
1336
1337 -- Bug#2131807 PJM enabled check.
1338
1339 l_stmt_num := 20;
1340
1341 l_pjm_enabled := PJM_INSTALL.check_implementation_status(
1342 p_organization_id => p_organization_id);
1343
1344 If l_pjm_enabled = TRUE Then
1345 fnd_message.set_name('WSM', 'WSM_ORG_PJM_ENABLED');
1346 raise e_pjmEnabled;
1347 End If;
1348
1349 -- Bug#2131807 PJM enabled check.
1350
1351 return 0;
1352
1353 End If;
1354
1355 EXCEPTION
1356 When e_primaryCostMethod Then
1357 x_err_code := 1;
1358 x_err_msg := fnd_message.get;
1359 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1360 return(x_err_code);
1361
1362 When e_lotNumberUniqueness Then
1363 x_err_code := 2;
1364 x_err_msg := fnd_message.get;
1365 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1366 return(x_err_code);
1367
1368 When e_lotNumberDefaultType Then
1369 x_err_code := 3;
1370 x_err_msg := fnd_message.get;
1371 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1372 return(x_err_code);
1373
1374 When e_wpsEnabled Then
1375 x_err_code := 4;
1376 x_err_msg := fnd_message.get;
1377 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1378 return(x_err_code);
1379
1380 /*BA#1490834*/
1381 When e_wmsEnabled Then
1382 x_err_code := 5;
1383 x_err_msg := fnd_message.get;
1384 return(x_err_code);
1385 /*EA#1490834*/
1386
1387 -- Bug#2131807 PJM enabled check.
1388 When e_pjmEnabled Then -- Bug#2131807 check PJM enabled
1389 x_err_code := 6;
1390 x_err_msg := fnd_message.get;
1391 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1392 return(x_err_code);
1393
1394 WHEN OTHERS Then
1395 x_err_code := SQLCODE;
1396 x_err_msg :=
1397 'WSMPUTIL.CHECK_IF_ORG_IS_VALID: (stmt_num='||
1398 l_stmt_num||'): '||
1399 SUBSTR(SQLERRM,1,60);
1400 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1401 return(x_err_code);
1402
1403
1404 End CHECK_IF_ORG_IS_VALID;
1405
1406 /**************************************************************************/
1407 -- written by abedajna, 09/07/00
1408
1409 PROCEDURE WRITE_TO_WIE ( p_header_id IN NUMBER,
1410 p_message IN VARCHAR2,
1411 p_request_id IN NUMBER,
1412 p_program_id IN NUMBER,
1413 p_program_application_id IN NUMBER,
1414 p_message_type IN NUMBER,
1415 x_err_code OUT NOCOPY NUMBER,
1416 x_err_msg OUT NOCOPY VARCHAR2) IS
1417
1418
1419 x_user NUMBER := FND_GLOBAL.user_id;
1420 x_login NUMBER := FND_GLOBAL.login_id;
1421
1422
1423 BEGIN
1424
1425 INSERT INTO WSM_INTERFACE_ERRORS (
1426 HEADER_ID,
1427 MESSAGE,
1428 LAST_UPDATE_DATE,
1429 LAST_UPDATED_BY,
1430 CREATION_DATE,
1431 CREATED_BY,
1432 LAST_UPDATE_LOGIN,
1433 REQUEST_ID,
1434 PROGRAM_ID,
1435 PROGRAM_APPLICATION_ID,
1436 MESSAGE_TYPE )
1437 values (
1438 p_header_id,
1439 p_message,
1440 SYSDATE,
1441 x_user,
1442 SYSDATE,
1443 x_user,
1444 x_login,
1445 p_request_id,
1446 p_program_id,
1447 p_program_application_id,
1448 p_message_type );
1449
1450
1451 EXCEPTION
1452
1453 WHEN OTHERS THEN
1454 x_err_code := SQLCODE;
1455 x_err_msg := 'WSMPTUIL.WRITE_TO_WIE:' || SUBSTR(SQLERRM,1,60);
1456 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1457
1458 END WRITE_TO_WIE;
1459
1460 /*BA#1577747*/
1461
1462 /* Procedure to get the common routing sequence id for a given
1463 ** Routing sequence id. If the given routing is not a common
1464 ** routing, then the routing sequence id and the common routing sequence
1465 ** id will be the same. -- bbk
1466 */
1467
1468
1469 /*****************************************************************************/
1470
1471 PROCEDURE find_common_routing ( p_routing_sequence_id IN NUMBER,
1472 p_common_routing_sequence_id OUT NOCOPY NUMBER,
1473 x_err_code OUT NOCOPY NUMBER,
1474 x_err_msg OUT NOCOPY VARCHAR2 ) IS
1475
1476 l_routing_sequence_id NUMBER;
1477 l_common_routing_sequence_id NUMBER default -999;
1478 prev_common_rout_seq_id NUMBER;
1479
1480 Begin
1481
1482 l_routing_sequence_id := p_routing_sequence_id;
1483
1484 WHILE NVL(l_routing_sequence_id,-999) <>
1485 NVL(l_common_routing_sequence_id, -999) Loop
1486
1487 -- l_counter := l_counter+1;
1488 --dbms_output.put_line('Counter is '||l_counter);
1489
1490 Select routing_sequence_id
1491 , common_routing_sequence_id
1492 Into
1493 l_routing_sequence_id
1494 , l_common_routing_sequence_id
1495
1496 From BOM_OPERATIONAL_ROUTINGS
1497
1498 Where routing_sequence_id = l_routing_sequence_id;
1499
1500
1501 If l_routing_sequence_id <>
1502 l_common_routing_sequence_id Then
1503
1504 prev_common_rout_seq_id := l_common_routing_sequence_id;
1505 l_common_routing_sequence_id := -999;
1506 l_routing_sequence_id := prev_common_rout_seq_id;
1507
1508 Else
1509
1510 p_common_routing_sequence_id := l_common_routing_sequence_id;
1511
1512 End If;
1513
1514
1515 End Loop;
1516
1517 EXCEPTION
1518
1519 WHEN OTHERS THEN
1520 x_err_code := SQLCODE;
1521 x_err_msg := 'WSMPTUIL.FIND_COMMON_ROUTING:' || SUBSTR(SQLERRM,1,60);
1522 -- FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1523
1524 End find_common_routing;
1525
1526 /*EA#1577747*/
1527
1528 -- BA OSFM-APS integration.
1529 -- Function submitted by Raghav Raghavacharya for OSFM-APS integration.
1530 -- Added to this file by Sadiq
1531
1532 /*****************************************************************************/
1533
1534 FUNCTION get_routing_start( p_routing_sequence_id IN NUMBER)
1535 RETURN NUMBER
1536 IS
1537 v_op_seq_num number;
1538 v_operation_sequence_id number;
1539 x_err_msg varchar2(2000); -- modified by bbk
1540 x_err_code number; -- modified by bbk
1541 /*BA#1577747*/
1542 e_user_exception EXCEPTION;
1543 p_common_routing_sequence_id NUMBER;
1544 /*EA#1577747*/
1545 BEGIN
1546 /*BA#1577747*/
1547
1548 WSMPUTIL.find_common_routing(
1549 p_routing_sequence_id => p_routing_sequence_id,
1550 p_common_routing_sequence_id => p_common_routing_sequence_id,
1551 x_err_code => x_err_code,
1552 x_err_msg => x_err_msg
1553 );
1554
1555 If x_err_code <> 0 Then
1556 raise e_user_exception;
1557 End If;
1558
1559 /*EA#1577747*/
1560
1561 WSMPUTIL.find_routing_start(
1562 -- p_routing_sequence_id, -- bbk
1563 p_common_routing_sequence_id, -- use this to find start.
1564 SYSDATE, --CZH: call with sysdate
1565 v_operation_sequence_id ,
1566 x_err_code,
1567 x_err_msg);
1568 -- BA: CZH I_OED-1
1569 IF (x_err_code <> 0 and x_err_code <> -3) THEN -- BUGFIX 3056524, ignore -3 also
1570 raise e_user_exception;
1571 END IF;
1572 -- EA: CZH I_OED-1
1573
1574 select operation_seq_num
1575 into v_op_seq_num
1576 from bom_operation_sequences
1577 where operation_sequence_id = v_operation_sequence_id;
1578
1579 return v_op_seq_num;
1580
1581 EXCEPTION
1582 WHEN E_USER_EXCEPTION THEN
1583 -- FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1584 return 0;
1585
1586 WHEN OTHERS THEN
1587 x_err_code := SQLCODE;
1588 x_err_msg := 'WSMPTUIL.GET_ROUTING_START' || SUBSTR(SQLERRM,1,60);
1589 -- FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
1590 return 0;
1591
1592 End get_routing_start;
1593
1594
1595 /*****************************************************************************/
1596
1597 FUNCTION get_routing_end( p_routing_sequence_id IN NUMBER)
1598 RETURN NUMBER
1599 IS
1600
1601 v_operation_sequence_id number;
1602 v_op_seq_num number;
1603
1604 x_err_msg varchar2(2000); -- modified by bbk
1605 x_err_code number; -- modified by bbk
1606 /*BA#1577747*/
1607 e_user_exception EXCEPTION;
1608 p_common_routing_sequence_id NUMBER;
1609 /*EA#1577747*/
1610
1611 BEGIN
1612 /*BA#1577747*/
1613
1614 WSMPUTIL.find_common_routing(
1615 p_routing_sequence_id => p_routing_sequence_id,
1616 p_common_routing_sequence_id => p_common_routing_sequence_id,
1617 x_err_code => x_err_code,
1618 x_err_msg => x_err_msg
1619 );
1620
1621 If x_err_code <> 0 Then
1622 raise e_user_exception;
1623 End If;
1624
1625
1626 /*EA#1577747*/
1627
1628 WSMPUTIL.find_routing_end(
1629 p_common_routing_sequence_id ,
1630 SYSDATE, -- CZH, call with SYSDATE
1631 v_operation_sequence_id ,
1632 x_err_code,
1633 x_err_msg);
1634 -- BA: CZH I_OED-1
1635 IF (x_err_code <> 0 and x_err_code <> -3) THEN -- BUGFIX 3056524, ignore -3 also
1636 raise e_user_exception;
1637 END IF;
1638 -- EA: CZH I_OED-1
1639
1640 select operation_seq_num
1641 into v_op_seq_num
1642 from bom_operation_sequences
1643 where operation_sequence_id = v_operation_sequence_id;
1644
1645 return v_op_seq_num;
1646
1647 EXCEPTION
1648 WHEN E_USER_EXCEPTION THEN
1649 -- BD: 1964044 -- FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg); -- ED: 1964044
1650 return 0;
1651
1652 WHEN OTHERS THEN
1653 x_err_code := SQLCODE;
1654 x_err_msg := 'WSMPTUIL.GET_ROUTING_END' || SUBSTR(SQLERRM,1,60);
1655 -- BD: 1964044 -- FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg); -- ED: 1964044
1656 return 0;
1657
1658 End get_routing_end;
1659
1660 -- EA OSFM-APS integration.
1661
1662
1663 /*
1664 Added on 12.29.2000 to fix Bug # 1418785.
1665 This function checks if a co-product relationship
1666 exists for a given bill sequence.
1667 */
1668
1669 /*****************************************************************************/
1670
1671 FUNCTION CHECK_COPROD_RELATION
1672 (
1673 p_bom_bill_seq_id IN NUMBER,
1674 x_err_code OUT NOCOPY NUMBER,
1675 x_err_msg OUT NOCOPY VARCHAR2
1676 )
1677 RETURN BOOLEAN IS
1678
1679 x_relation_exists BOOLEAN := TRUE;
1680 temp_bill_seq_id NUMBER := 0;
1681 l_stmt_num NUMBER := 0;
1682
1683 BEGIN
1684
1685 x_err_code := 0;
1686 x_err_msg := '';
1687
1688 SELECT bill_sequence_id
1689 INTO temp_bill_seq_id
1690 FROM wsm_co_products coprod
1691 WHERE p_bom_bill_seq_id = coprod.bill_sequence_id;
1692
1693 -- IF Clause added by Bala on Feb 8th, 2000.
1694 -- Bug# 1418785 not returning any values.
1695
1696 If SQL%ROWCOUNT > 0 Then
1697 return TRUE;
1698 End If;
1699
1700 EXCEPTION
1701
1702 WHEN OTHERS THEN
1703 x_err_code := SQLCODE;
1704 x_err_msg := substr(('WSMPUTIL.check_coprod_relation'||SUBSTR(SQLERRM,1,1000)), 1, 1000);
1705 x_relation_exists := FALSE;
1706 RETURN x_relation_exists;
1707
1708 END CHECK_COPROD_RELATION;
1709
1710 --BA 2731019
1711 FUNCTION CHECK_COPROD_COMP_RELATION
1712 (
1713 p_bom_bill_seq_id IN NUMBER,
1714 p_component_seq_id IN NUMBER
1715 )
1716 RETURN NUMBER IS
1717 temp_bill_seq_id NUMBER := 0;
1718 BEGIN
1719
1720 SELECT bill_sequence_id
1721 INTO temp_bill_seq_id
1722 FROM wsm_co_products coprod
1723 WHERE p_bom_bill_seq_id = coprod.bill_sequence_id
1724 AND p_component_seq_id = coprod.COMPONENT_SEQUENCE_ID;
1725
1726 If SQL%ROWCOUNT > 0 Then
1727 return 1;
1728 ELSE
1729 return 0;
1730 End If;
1731
1732 EXCEPTION
1733
1734 WHEN OTHERS THEN
1735 RETURN 0;
1736
1737 END CHECK_COPROD_COMP_RELATION;
1738 --EA 2731019
1739
1740 /*****************************************************************************/
1741 -- This is an overloaded function created for BOM USE alone..BBK
1742 FUNCTION CHECK_COPROD_RELATION (
1743 p_bom_bill_seq_id IN NUMBER
1744 )
1745 RETURN NUMBER IS
1746
1747 l_relation_exists NUMBER := 0;
1748 l_relation_exist_boolean BOOLEAN := FALSE;
1749 l_err_code NUMBER := 0;
1750 l_err_msg VARCHAR2(1000) := NULL;
1751
1752 BEGIN
1753
1754 l_relation_exist_boolean := WSMPUTIL.check_coprod_relation (
1755 p_bom_bill_seq_id => p_bom_bill_seq_id
1756 , x_err_code => l_err_code
1757 , x_err_msg => l_err_msg);
1758
1759 If l_err_code <> 0
1760 or l_err_msg <> NULL
1761 or l_relation_exist_boolean = FALSE Then
1762
1763 l_relation_exists := 0;
1764 Else
1765 l_relation_exists := 1;
1766
1767 End If;
1768
1769 return l_relation_exists;
1770
1771 EXCEPTION
1772
1773 WHEN OTHERS THEN
1774 l_relation_exists := 0;
1775 RETURN l_relation_exists;
1776
1777 END CHECK_COPROD_RELATION;
1778
1779 /*****************************************************************************/
1780 /*
1781 **
1782 ** This procedure is added to validate
1783 ** that the sum of planning percentages
1784 ** of all links emanating from each node
1785 ** exactly adds up to 100. This enhancement
1786 ** is done along with the APS-WSM integration
1787 **
1788 */
1789
1790 /*****************************************************************************/
1791
1792 FUNCTION CHECK_100_PERCENT ( p_routing_sequence_id IN NUMBER,
1793 x_err_code OUT NOCOPY NUMBER,
1794 x_err_msg OUT NOCOPY VARCHAR2)
1795 RETURN NUMBER IS
1796
1797 var_total_planning_pct NUMBER;
1798 p_from_seq_num NUMBER;
1799
1800 CURSOR check_percentage_sum IS
1801 SELECT distinct (from_seq_num)
1802 FROM bom_operation_networks_v
1803 WHERE routing_sequence_id = p_routing_sequence_id
1804 ORDER BY from_seq_num ;
1805
1806 BEGIN
1807
1808 OPEN check_percentage_sum;
1809
1810 LOOP
1811 FETCH check_percentage_sum INTO p_from_seq_num ;
1812 EXIT WHEN check_percentage_sum%NOTFOUND;
1813
1814 SELECT SUM(planning_pct)
1815 INTO var_total_planning_pct
1816 FROM bom_operation_networks_v
1817 WHERE from_seq_num = p_from_seq_num
1818 AND transition_type IN (1, 2)
1819 AND routing_sequence_id = p_routing_sequence_id ;
1820
1821 IF( var_total_planning_pct <> 100) THEN
1822
1823 FND_MESSAGE.SET_NAME('WSM','WSM_%_SUM_NOT_100');
1824 FND_MESSAGE.SET_TOKEN('WSM_SEQ_NUM',p_from_seq_num);
1825 x_err_msg:= FND_MESSAGE.GET;
1826 x_err_code := -1 ;
1827 RETURN 0;
1828 EXIT;
1829
1830 END IF ;
1831
1832 END LOOP ;
1833
1834 IF check_percentage_sum%ISOPEN THEN
1835 CLOSE check_percentage_sum;
1836 END IF;
1837
1838 RETURN 1;
1839
1840 EXCEPTION WHEN OTHERS THEN
1841
1842 x_err_code := SQLCODE;
1843 x_err_msg := 'WSMPUTIL.check_100_percent : '||SUBSTR(SQLERRM,1,1000);
1844 RETURN 0;
1845 IF check_percentage_sum%ISOPEN THEN
1846 CLOSE check_percentage_sum;
1847 END IF;
1848 END CHECK_100_PERCENT;
1849
1850
1851 /*****************************************************************************/
1852
1853 /*BA#1641781*/
1854 PROCEDURE AUTONOMOUS_TXN(p_user IN NUMBER,
1855 p_login IN NUMBER,
1856 p_header_id IN NUMBER,
1857 p_message IN VARCHAR2,
1858 p_request_id IN NUMBER,
1859 p_program_id IN NUMBER,
1860 p_program_application_id IN NUMBER,
1861 p_message_type IN NUMBER,
1862 p_txn_id IN NUMBER,
1863 x_err_code OUT NOCOPY NUMBER,
1864 x_err_msg OUT NOCOPY VARCHAR2)
1865
1866 IS
1867 PRAGMA autonomous_transaction;
1868 BEGIN
1869 INSERT INTO WSM_INTERFACE_ERRORS (
1870 HEADER_ID,
1871 TRANSACTION_ID,
1872 MESSAGE,
1873 LAST_UPDATE_DATE,
1874 LAST_UPDATED_BY,
1875 CREATION_DATE,
1876 CREATED_BY,
1877 LAST_UPDATE_LOGIN,
1878 REQUEST_ID,
1879 PROGRAM_ID,
1880 PROGRAM_APPLICATION_ID,
1881 MESSAGE_TYPE )
1882 values (
1883 p_header_id,
1884 p_txn_id,
1885 p_message,
1886 SYSDATE,
1887 p_user,
1888 SYSDATE,
1889 p_user,
1890 p_login,
1891 p_request_id,
1892 p_program_id,
1893 p_program_application_id,
1894 p_message_type );
1895 COMMIT;
1896 EXCEPTION
1897 WHEN OTHERS THEN
1898 x_err_code := SQLCODE;
1899 x_err_msg := substrb(sqlerrm,1,2000);
1900 rollback;
1901
1902 END AUTONOMOUS_TXN;
1903 /*EA#1641781*/
1904
1905
1906 /***********************************************************************************/
1907
1908 --
1909 -- This is an over-loaded function which calls the same function
1910 -- with p_routing_revision_date as parameter.
1911 --
1912 -- This is created to circumvent the dependency issues with forms and other objects
1913 --
1914
1915 PROCEDURE OPERATION_IS_STANDARD_REPEATS (
1916 p_routing_sequence_id IN NUMBER,
1917 p_standard_operation_id IN NUMBER,
1918 p_operation_code IN VARCHAR2,
1919 p_organization_id IN NUMBER, --BBK
1920 p_op_is_std_op OUT NOCOPY NUMBER,
1921 p_op_repeated_times OUT NOCOPY NUMBER,
1922 x_err_code OUT NOCOPY NUMBER,
1923 x_err_msg OUT NOCOPY VARCHAR2)
1924 IS
1925 BEGIN
1926 Operation_Is_Standard_Repeats(
1927 p_routing_sequence_id => p_routing_sequence_id,
1928 p_routing_revision_date => SYSDATE,
1929 p_standard_operation_id => p_standard_operation_id,
1930 p_operation_code => p_operation_code,
1931 p_organization_id => p_organization_id,
1932 p_op_is_std_op => p_op_is_std_op,
1933 p_op_repeated_times => p_op_repeated_times,
1934 x_err_code => x_err_code,
1935 x_err_msg => x_err_msg );
1936
1937 END OPERATION_IS_STANDARD_REPEATS;
1938
1939 /* **********************************************************************************
1940 -- BA: NSO-WLT
1941
1942 Function description:
1943 Given a routing (routing sequence id) and an operation (operation code or
1944 standard operation id), this procedure finds out if:
1945 1. This operation is a standard operation (p_op_is_std_op=1)
1946 Then, if it is a standard operation, is it unique in the primary path of the
1947 network routing (if so p_op_is_unique=1, otherwise 0)
1948 Then if not unique, how many times this repeats in the primary path of the
1949 network routing (p_op_repeated_times=1 if more than once, otherwise, 2).
1950 2. This operation is a non-standard op (p_op_is_std_op= 0),
1951 Then, if it is a non-standard op, is it unique in the primary path of the
1952 network routing (if so p_op_is_unique=1, otherwise 0)
1953 If not unique, how many times this repeats in the primary path of the
1954 network routing (p_op_repeated_times=1 if more than once, otherwise, 2).
1955
1956 --
1957 -- Since this is an overloaded function, we shouldn't have
1958 -- DEFAULT clause on p_quantity. Else, you'll get the following error
1959 -- while calling this function.
1960 -- PLS-00307: too many declarations of 'GET_SCHEDULED_DATE'
1961 -- match this call
1962 --
1963 ********************************************************************************** */
1964
1965 PROCEDURE OPERATION_IS_STANDARD_REPEATS(
1966 p_routing_sequence_id IN NUMBER,
1967 p_routing_revision_date IN DATE, -- CZH.I_OED-1
1968 p_standard_operation_id IN NUMBER,
1969 p_operation_code IN VARCHAR2,
1970 p_organization_id IN NUMBER, --BBK
1971 p_op_is_std_op OUT NOCOPY NUMBER,
1972 p_op_repeated_times OUT NOCOPY NUMBER,
1973 x_err_code OUT NOCOPY NUMBER,
1974 x_err_msg OUT NOCOPY VARCHAR2)
1975
1976 IS
1977
1978 l_stmt_num NUMBER;
1979 l_std_op_id NUMBER ; --BBK
1980 l_op_seq_num NUMBER;
1981 l_rtg_rev_date DATE;
1982
1983 BEGIN
1984
1985 l_rtg_rev_date := NVL(p_routing_revision_date, SYSDATE);
1986 p_op_is_std_op := 0;
1987 p_op_repeated_times := 0;
1988 x_err_code := 0;
1989 x_err_msg := NULL;
1990
1991 l_stmt_num := 10;
1992
1993 IF (p_routing_sequence_id is null) then
1994 x_err_code := 1;
1995 x_err_msg := 'WSMPUTIL.operation_is_standard_repeats: Invalid operation and/or routing ('
1996 ||l_stmt_num || ') ';
1997 p_op_is_std_op := 3; -- CZH: why?
1998 p_op_repeated_times := 3; -- CZH: why?
1999 return;
2000 END IF;
2001
2002
2003 l_stmt_num := 20;
2004
2005 IF (p_operation_code is null and p_standard_operation_id is null) then
2006 -- Job is at an NSO operation.
2007 p_op_is_std_op := 0;
2008 p_op_repeated_times := 0;
2009 return;
2010
2011 ELSIF p_standard_operation_id is not null then
2012
2013 l_std_op_id := p_standard_operation_id;
2014 p_op_is_std_op := 1;
2015
2016 ELSIF (p_operation_code is not null) then
2017
2018 l_stmt_num := 30;
2019
2020 Begin
2021
2022 select nvl(standard_operation_id, -999)
2023 into l_std_op_id
2024 from bom_standard_operations
2025 where organization_id = p_organization_id -- BBK
2026 and operation_type = 1 -- Standard Operation Type BBK
2027 and line_id is NULL -- Not for a WIP Line BBK
2028 and operation_code = p_operation_code;
2029
2030 Exception
2031
2032 WHEN NO_DATA_FOUND THEN
2033 x_err_code := 2;
2034 x_err_msg := 'WSMPUTIL.operation_is_standard_repeats ('
2035 ||l_stmt_num || '): Standard_op_id not found for this opcode.. '
2036 ||substrb(sqlerrm,1,1000);
2037
2038 WHEN OTHERS THEN
2039 x_err_code := 3;
2040 x_err_msg := 'WSMPUTIL.operation_is_standard_repeats ('
2041 ||l_stmt_num ||'): '||substrb(sqlerrm,1,1000);
2042
2043 End;
2044
2045 IF (l_std_op_id = -999) then
2046 p_op_is_std_op := 0;
2047 ELSE
2048 p_op_is_std_op := 1;
2049 END IF;
2050
2051
2052 END IF;
2053
2054 -- Let us get How many times that this is repeated
2055 -- Previous logic was wrong. Rewrote this. BBK
2056
2057 l_stmt_num := 50;
2058
2059 Declare
2060
2061 l_rtg_end_opseqid NUMBER default 0;
2062 l_err_msg varchar2(2000);
2063 l_err_code NUMBER := 0;
2064 l_counter NUMBER := 0;
2065
2066 Begin
2067
2068 /*Bug 3659838 Cursor c is replaced by a select with count*/
2069 /***************************
2070 DECLARE
2071 cursor c is
2072 -- BC: CZH.I_OED-2, consider replacement
2073 select distinct bon.from_op_seq_id,
2074 bos.standard_operation_id
2075 from bom_operation_networks bon,
2076 bom_operation_sequences bos
2077 Where bos.routing_sequence_id = p_routing_sequence_id
2078 and bos.operation_sequence_id = bon.from_op_seq_id
2079 and bos.standard_operation_id = l_std_op_id --p_standard_operation_id --Fix for 2265237
2080 and nvl(bos.disable_date, l_rtg_rev_date+1) >= l_rtg_rev_date -- CZH.I_OED-1
2081 and bos.effectivity_date <= l_rtg_rev_date -- CZH.I_OED-1
2082 UNION
2083 select distinct bon.to_op_seq_id,
2084 bos.standard_operation_id
2085 from bom_operation_networks bon,
2086 bom_operation_sequences bos
2087 Where bos.routing_sequence_id = p_routing_sequence_id
2088 and bos.operation_sequence_id = bon.to_op_seq_id
2089 and bos.standard_operation_id = l_std_op_id --p_standard_operation_id --Fix for 2265237
2090 and nvl(bos.disable_date, l_rtg_rev_date+1) >= l_rtg_rev_date --CZH.I_OED-1
2091 and bos.effectivity_date <= l_rtg_rev_date; --CZH.I_OED-1
2092 select distinct
2093 bos.operation_sequence_id,
2094 bos.standard_operation_id
2095 from bom_operation_networks bon,
2096 bom_operation_sequences bos
2097 Where bos.routing_sequence_id = p_routing_sequence_id
2098 and bos.standard_operation_id = l_std_op_id
2099 and (bos.operation_sequence_id = WSMPUTIL.replacement_op_seq_id(
2100 bon.from_op_seq_id,
2101 l_rtg_rev_date)
2102 or
2103 bos.operation_sequence_id = WSMPUTIL.replacement_op_seq_id(
2104 bon.to_op_seq_id,
2105 l_rtg_rev_date)
2106 );
2107 -- EC: CZH.I_OED-2
2108
2109 c_opseq_id NUMBER;
2110 c_stdop_id NUMBER;
2111 *****************/
2112 /*End of changes for Bug 3659838*/
2113
2114 BEGIN
2115 /*Bug 3659838
2116 OPEN c;
2117 LOOP
2118
2119 FETCH c INTO c_opseq_id, c_stdop_id;
2120 EXIT WHEN c%NOTFOUND;
2121 l_counter := l_counter+1;
2122
2123 END LOOP;
2124 CLOSE c;
2125 Bug 3659838*/
2126 /*Bug 3659838 Following SQL is added for this bug*/
2127 select count(*)
2128 into l_counter
2129 from bom_operation_sequences bos,
2130 bom_operation_sequences bos2
2131 Where bos.routing_sequence_id = p_routing_sequence_id
2132 and bos.operation_sequence_id IN
2133 (select from_op_seq_id opseqid
2134 from bom_operation_networks bon_A,
2135 bom_operation_sequences bos_A
2136 where bos_A.routing_sequence_id = p_routing_sequence_id
2137 and bon_A.from_op_seq_id = bos_A.operation_sequence_id
2138 UNION ALL
2139 select to_op_seq_id opseqid
2140 from bom_operation_networks bon_B,
2141 bom_operation_sequences bos_B
2142 where bos_B.routing_sequence_id = p_routing_sequence_id
2143 and bon_B.from_op_seq_id = bos_B.operation_sequence_id)
2144 and bos2.routing_sequence_id = p_routing_sequence_id
2145 and bos.operation_seq_num = bos2.operation_seq_num
2146 and bos2.standard_operation_id = l_std_op_id
2147 and nvl(bos2.disable_date, l_rtg_rev_date+1) >= l_rtg_rev_date
2148 and bos2.effectivity_date <= l_rtg_rev_date;
2149
2150 END;
2151
2152 p_op_repeated_times := l_counter;
2153
2154 End;
2155 EXCEPTION
2156 WHEN OTHERS THEN
2157 x_err_code := SQLCODE;
2158 x_err_msg := substr('WSMPUTIL.OPERATION_IS_STANDARD_REPEATS' ||sqlerrm,1,2000);
2159 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
2160 return;
2161
2162 END OPERATION_IS_STANDARD_REPEATS;
2163 -- BA: NSO-WLT
2164
2165 -- added by abedajna for patchset H non standard jobs project
2166
2167 procedure validate_non_std_references(p_assembly_item_id IN NUMBER,
2168 p_routing_reference_id IN NUMBER,
2169 p_bom_reference_id IN NUMBER,
2170 p_alt_routing_designator IN VARCHAR2,
2171 p_alt_bom_designator IN VARCHAR2,
2172 p_organization_id IN NUMBER,
2173 p_start_date IN DATE,
2174 p_end_date IN DATE,
2175 p_start_quantity IN NUMBER,
2176 p_mrp_net_quantity IN OUT NOCOPY NUMBER,
2177 p_class_code IN VARCHAR2,
2178 p_completion_subinventory IN VARCHAR2,
2179 p_completion_locator_id IN NUMBER,
2180 p_firm_planned_flag IN OUT NOCOPY NUMBER,
2181 p_bom_revision IN OUT NOCOPY VARCHAR2,
2182 p_bom_revision_date IN OUT NOCOPY DATE,
2183 p_routing_revision IN OUT NOCOPY VARCHAR2,
2184 p_routing_revision_date IN OUT NOCOPY DATE,
2185 x_routing_seq_id OUT NOCOPY NUMBER,
2186 x_bom_seq_id OUT NOCOPY NUMBER,
2187 validation_level NUMBER,
2188 x_error_code OUT NOCOPY NUMBER,
2189 x_err_msg OUT NOCOPY VARCHAR2) IS
2190
2191 -- validation_level = 0 => validations performed during job creation
2192 -- validation_level = 1 => validations performed for bom_reference
2193 -- validation_level = 2 => validations performed for routing_reference
2194
2195 -- *** Error Code and Message Guide ***
2196 -- 1: Routing Reference Cannot be Null
2197 -- 2: Invalid Assembly Item Id
2198 -- 3: Invalid Routing Reference Id
2199 -- 4: Invalid Bom Reference Id
2200 -- 5: Invalid Alternate Routing Designator
2201 -- 0: Invalid Alternate Bom Designator -- WARNING
2202 -- 7: Start Date cannot be greater than End Date
2203 -- 8: Both Start and End Dates must be Entered
2204 -- 9: Invalid Start Quantity
2205 -- 10: Invalid Net Quantity
2206 -- 11: Invalid Class Code
2207 -- 12: Invalid Completion Locator Id
2208 -- 13: Invalid Completion Subinventory
2209 -- 14: Invalid Firm Planned Flag
2210
2211
2212 l_no_of_records NUMBER := 0;
2213 l_dummy NUMBER := 0;
2214 l_stmt_num NUMBER;
2215 def_completion_subinventory VARCHAR2(10) := '';
2216 def_completion_locator_id NUMBER := '';
2217 l_mtl_locator_type NUMBER;
2218 l_sub_loc_control NUMBER;
2219 l_org_loc_control NUMBER;
2220 l_restrict_locators_code NUMBER;
2221 l_item_loc_control NUMBER;
2222 l_segs VARCHAR2(10000);
2223 l_loc_success BOOLEAN;
2224 l_locator_id NUMBER;
2225 l_rev_date DATE;
2226
2227 -- ST : Serial Support Project -----------------------------
2228 l_serial_control_code NUMBER;
2229 -- ST : Serial Support Project -----------------------------
2230
2231 begin
2232
2233 x_error_code := 0;
2234 x_err_msg := '';
2235
2236 l_stmt_num := 10;
2237
2238 -- routing reference cannot be null
2239 if validation_level = 0 then
2240
2241 if p_routing_reference_id is null then
2242 fnd_message.set_name('WSM','WSM_NS_RTNG_REF_NULL');
2243 x_err_msg := fnd_message.get;
2244 x_error_code := 1;
2245 return;
2246 end if;
2247
2248 end if;
2249
2250
2251 -- assembly cannot be null
2252 if validation_level = 0 then
2253
2254 if p_assembly_item_id is null then
2255 fnd_message.set_name('WSM','WSM_NS_ASS_NULL');
2256 x_err_msg := fnd_message.get;
2257 x_error_code := 1;
2258 return;
2259 end if;
2260
2261 end if;
2262
2263 -- check that the item exists and it is lot controlled
2264 if validation_level = 0 then
2265
2266 l_stmt_num := 20;
2267
2268 BEGIN
2269 select 1
2270 into l_no_of_records
2271 from mtl_system_items_kfv msi
2272 where msi.inventory_item_id = p_assembly_item_id
2273 and msi.organization_id = p_organization_id
2274 and msi.lot_control_code = 2;
2275 EXCEPTION
2276 when too_many_rows then l_no_of_records := 1;
2277 when no_data_found then
2278 x_error_code := 2;
2279 fnd_message.set_name('WSM','WSM_ASSEMBLY_NO_LOT');
2280 x_err_msg := fnd_message.get;
2281 return;
2282 END;
2283
2284 l_stmt_num := 30;
2285 if l_no_of_records <> 0 then
2286 BEGIN
2287 l_no_of_records := 0;
2288 select 1,
2289 -- ST : Serial Support Project -----------------------------
2290 serial_number_control_code
2291 into l_no_of_records,
2292 -- ST : Serial Support Project -----------------------------
2293 l_serial_control_code
2294 from mtl_system_items_kfv msi
2295 where msi.inventory_item_id = p_assembly_item_id
2296 and msi.organization_id = p_organization_id
2297 -- ST : Serial Support Project --------------
2298 and msi.serial_number_control_code IN (1,2);
2299 -- ST : Serial Support Project --------------
2300 EXCEPTION
2301 when too_many_rows then l_no_of_records := 1;
2302 when no_data_found then
2303 x_error_code := 2;
2304 fnd_message.set_name('WSM','WSM_ASSEMBLY_NOT_SERIAL');
2305 x_err_msg := fnd_message.get;
2306 return;
2307 END;
2308 end if;
2309
2310 end if;
2311
2312 -- check for the existance of the item used for routing reference
2313 if validation_level in (0,2) then
2314
2315 l_stmt_num := 40;
2316 BEGIN
2317 select 1
2318 into l_dummy
2319 from mtl_system_items_kfv msi
2320 where msi.inventory_item_id = p_routing_reference_id
2321 and msi.organization_id = p_organization_id;
2322 EXCEPTION
2323 when too_many_rows then null;
2324 when no_data_found then
2325 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
2326 fnd_message.set_token('FLD_NAME', 'ROUTING_REFERENCE_ID');
2327 x_err_msg := fnd_message.get;
2328 x_error_code := 3;
2329 return;
2330 END;
2331 end if;
2332
2333 -- check for the existance of the item used for bom reference
2334 if validation_level in (0,1) then
2335
2336 l_stmt_num := 50;
2337 if p_bom_reference_id is not null then
2338 BEGIN
2339 select 1
2340 into l_dummy
2341 from mtl_system_items_kfv msi
2342 where msi.inventory_item_id = p_bom_reference_id
2343 and msi.organization_id = p_organization_id;
2344 EXCEPTION
2345 when too_many_rows then null;
2346 when no_data_found then
2347 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
2348 fnd_message.set_token('FLD_NAME', 'BOM_REFERENCE_ID');
2349 x_err_msg := fnd_message.get;
2350 x_error_code := 4;
2351 return;
2352 END;
2353 end if;
2354
2355
2356 if p_bom_reference_id is null and p_alt_bom_designator is not null then
2357 x_err_msg := 'Warning! Alternate Bom Designator has a not null value that has been ignored.';
2358 end if;
2359
2360 end if;
2361
2362 -- check for the existance of routing
2363 if validation_level in (0,2) then
2364
2365 BEGIN
2366 l_stmt_num := 60;
2367 select bor.routing_sequence_id,
2368 bor.completion_subinventory,
2369 bor.completion_locator_id
2370 into x_routing_seq_id,
2371 def_completion_subinventory,
2372 def_completion_locator_id
2373 from bom_routing_alternates_v bor
2374 where bor.organization_id = p_organization_id
2375 and bor.assembly_item_id = p_routing_reference_id
2376 and NVL(bor.alternate_routing_designator, '&*') = NVL(p_alt_routing_designator, '&*')
2377 and bor.routing_type = 1
2378 and bor.cfm_routing_flag = 3;
2379 EXCEPTION
2380 when no_data_found then
2381 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
2382 fnd_message.set_token('FLD_NAME', 'ROUTING_REFERENCE_ID/ALTERNATE_ROUTING_DESIGNATOR');
2383 x_err_msg := fnd_message.get;
2384 x_error_code := 5;
2385 return;
2386 END;
2387
2388 end if;
2389
2390
2391 -- check for existance of bom
2392 if validation_level in (0,1) then
2393
2394 l_stmt_num := 70;
2395 if p_bom_reference_id is not null then
2396 BEGIN
2397 SELECT bom.common_bill_sequence_id
2398 INTO x_bom_seq_id
2399 FROM bom_bill_of_materials bom
2400 WHERE NVL(bom.alternate_bom_designator, '&*') = NVL(p_alt_bom_designator, '&*')
2401 AND BOM.assembly_item_id = p_bom_reference_id
2402 AND bom.organization_id = p_organization_id;
2403 EXCEPTION
2404 when no_data_found then
2405 fnd_message.set_name('WIP','WIP_BILL_DOES_NOT_EXIST');
2406 x_err_msg := fnd_message.get;
2407 --SpUA bugfix 3154345 Not really a SpUA bugfix!!
2408 --Now we will enable wrong alt_bom_desig to error out
2409 x_error_code := 6;
2410 --End SpUA bugfix
2411 END;
2412 end if;
2413
2414 end if; -- validation level
2415
2416 /***************
2417 -- date validations
2418
2419 l_stmt_num := 80;
2420 if p_start_date is null or p_end_date is null then
2421 fnd_message.set_name('WSM','WSM_NS_NULL_DATE');
2422 x_err_msg := fnd_message.get;
2423 x_error_code := 8;
2424 return;
2425 end if;
2426
2427 if p_start_date > p_end_date then
2428 fnd_message.set_name('WSM','WSM_FUSD_GT_LUCD');
2429 x_err_msg := fnd_message.get;
2430 x_error_code := 7;
2431 return;
2432 end if;
2433
2434 ***************/
2435
2436
2437 -- start quantity validations
2438 if validation_level = 0 then
2439
2440 l_stmt_num := 90;
2441 if p_start_quantity is null or (p_start_quantity is not null and p_start_quantity <= 0) then
2442 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
2443 fnd_message.set_token('FLD_NAME', 'START_QUANTITY');
2444 x_err_msg := fnd_message.get;
2445 x_error_code := 9;
2446 return;
2447 end if;
2448
2449 -- ST : Serial Support Project -----------------------------
2450 IF l_serial_control_code = 2 THEN
2451 IF floor(p_start_quantity) <> p_start_quantity THEN
2452 -- ST : Serial Support Project -----------------------------
2453 fnd_message.set_name('WSM','WSM_INVALID_JOB_TXN_QTY');
2454 x_err_msg := fnd_message.get;
2455 x_error_code := 9;
2456 return;
2457 END IF;
2458 END IF;
2459 -- ST : Serial Support Project -----------------------------
2460 end if;
2461
2462 -- net quantity validations
2463 if validation_level = 0 then
2464
2465 l_stmt_num := 100;
2466 if p_mrp_net_quantity is null then
2467 p_mrp_net_quantity := 0;
2468 else
2469 if p_mrp_net_quantity < 0 or p_mrp_net_quantity > p_start_quantity then
2470 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
2471 fnd_message.set_token('FLD_NAME', 'MRP_NET_QUANTITY');
2472 x_err_msg := fnd_message.get;
2473 x_error_code := 9;
2474 return;
2475 end if;
2476
2477 -- ST : Serial Support Project -----------------------------
2478 IF l_serial_control_code = 2 THEN
2479 IF floor(p_mrp_net_quantity) <> p_mrp_net_quantity THEN
2480 -- ST : Serial Support Project -----------------------------
2481 fnd_message.set_name('WSM','WSM_INVALID_JOB_TXN_QTY');
2482 x_err_msg := fnd_message.get;
2483 x_error_code := 9;
2484 return;
2485 END IF;
2486 END IF;
2487 -- ST : Serial Support Project -----------------------------
2488 end if;
2489
2490 end if;
2491
2492 -- class code validation
2493 if validation_level = 0 then
2494
2495 l_stmt_num := 110;
2496 BEGIN
2497 select 1
2498 into l_dummy
2499 from wip_accounting_classes
2500 where class_code = nvl(p_class_code, '***')
2501 and organization_id = p_organization_id
2502 and nvl(disable_date, sysdate + 1) > sysdate
2503 and class_type = 7;
2504 EXCEPTION
2505 when too_many_rows then null;
2506 when no_data_found then
2507 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
2508 fnd_message.set_token('FLD_NAME', 'CLASS_CODE');
2509 x_err_msg := fnd_message.get;
2510 x_error_code := 11;
2511 return;
2512 END;
2513
2514 end if;
2515
2516
2517 -- completion subinv and locator validation
2518 if validation_level = 0 then
2519
2520 l_stmt_num := 120;
2521
2522 if p_completion_subinventory is not null then
2523 /* ST bug fix 3722383 if WSM: Complete Job Sector lot extension level is set at Item level,
2524 then sector extension for the subinventory is not mandatory */
2525 if ( nvl(FND_PROFILE.value('WSM_COMPLETE_SEC_LOT_EXTN_LEVEL'), '1') = 2 ) then
2526
2527
2528 BEGIN
2529 select 1
2530 into l_dummy
2531 from wsm_subinventory_extensions
2532 where secondary_inventory_name = p_completion_subinventory
2533 and organization_id = p_organization_id;
2534 EXCEPTION
2535 when too_many_rows then null;
2536 when no_data_found then
2537 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
2538 fnd_message.set_token('FLD_NAME', 'COMPLETION_SUBINVENTORY');
2539 x_err_msg := fnd_message.get;
2540 x_error_code := 13;
2541 return;
2542 END;
2543 end if;
2544 /* ST bug fix 3722383 end */
2545
2546 select locator_type
2547 into l_mtl_locator_type
2548 from mtl_secondary_inventories
2549 where secondary_inventory_name = p_completion_subinventory
2550 and organization_id = p_organization_id;
2551
2552 /* ST bugfix3336844(2793501) call wip_locator.validate is enough for validate lcator: null or not. remove checks of
2553 --locator validation null. comment out checks of l_mtl_locator_type, it does not works if org level is locator
2554 --control but sub is not. */
2555 /***
2556 select locator_type
2557 into l_mtl_locator_type
2558 from mtl_secondary_inventories
2559 where secondary_inventory_name = p_completion_subinventory
2560 and organization_id = p_organization_id;
2561
2562 if p_completion_locator_id is not null then
2563 ***/
2564 SELECT nvl(msub.locator_type, 1) sub_loc_control,
2565 MP.stock_locator_control_code org_loc_control,
2566 MS.restrict_locators_code,
2567 MS.location_control_code item_loc_control
2568 into l_sub_loc_control, l_org_loc_control,
2569 l_restrict_locators_code, l_item_loc_control
2570 FROM mtl_system_items MS,
2571 mtl_secondary_inventories MSUB,
2572 mtl_parameters MP
2573 WHERE MP.organization_id = p_organization_id
2574 AND MS.organization_id = p_organization_id
2575 AND MS.inventory_item_id = p_assembly_item_id
2576 AND MSUB.secondary_inventory_name = p_completion_subinventory
2577 AND MSUB.organization_id = p_organization_id;
2578
2579
2580 l_locator_id := p_completion_locator_id;
2581
2582 /* STbugfix 3336844 added exception handler, since the WIP API does not have */
2583 begin
2584 WIP_LOCATOR.validate( p_organization_id,
2585 p_assembly_item_id,
2586 p_completion_subinventory,
2587 l_org_loc_control,
2588 l_sub_loc_control,
2589 l_item_loc_control,
2590 l_restrict_locators_code,
2591 NULL, NULL, NULL, NULL,
2592 l_locator_id,
2593 l_segs,
2594 l_loc_success);
2595 exception
2596 when NO_DATA_FOUND then
2597 l_stmt_num := 123;
2598 l_loc_success := FALSE;
2599 end;
2600
2601 IF not l_loc_success THEN
2602 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
2603 fnd_message.set_token('FLD_NAME', 'COMPLETION_LOCATOR_ID');
2604 x_err_msg := fnd_message.get;
2605 x_error_code := 12;
2606 return;
2607 end if;
2608 /*** ST bugfix 3336844
2609 elsif p_completion_locator_id is null then
2610 if l_mtl_locator_type = 2 then
2611 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
2612 fnd_message.set_token('FLD_NAME', 'COMPLETION_LOCATOR_ID');
2613 x_err_msg := fnd_message.get;
2614 x_error_code := 12;
2615 return;
2616 end if;
2617 end if;
2618 end fix 3336844 ***/
2619 elsif p_completion_subinventory is null and p_completion_locator_id is null then
2620 null;
2621 elsif p_completion_subinventory is null and p_completion_locator_id is not null then
2622 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
2623 fnd_message.set_token('FLD_NAME', 'COMPLETION_LOCATOR_ID');
2624 x_err_msg := fnd_message.get;
2625 x_error_code := 12;
2626 return;
2627 end if;
2628
2629 end if;
2630
2631
2632 -- validate firm planned flag
2633 if validation_level = 0 then
2634
2635 l_stmt_num:= 130;
2636
2637 if p_firm_planned_flag is not null and p_firm_planned_flag <> 2 then
2638 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
2639 fnd_message.set_token('FLD_NAME', 'FIRM_PLANNED_FLAG');
2640 x_err_msg := fnd_message.get;
2641 x_error_code := 14;
2642 return;
2643 end if;
2644 if p_firm_planned_flag is null then
2645 p_firm_planned_flag := 2;
2646 end if;
2647
2648 end if;
2649
2650 -- get revisions
2651
2652 l_stmt_num:= 140;
2653 if validation_level in (0,1,2) then
2654
2655 if p_start_date > SYSDATE then
2656 l_rev_date := p_start_date;
2657 else
2658 l_rev_date := SYSDATE;
2659 end if;
2660
2661 if validation_level in (0,1) then
2662 wip_revisions.bom_revision (p_organization_id,
2663 p_bom_reference_id,
2664 p_bom_revision,
2665 p_bom_revision_date,
2666 l_rev_date);
2667 end if;
2668
2669 if validation_level in (0,2) then
2670 wip_revisions.routing_revision (p_organization_id,
2671 p_routing_reference_id,
2672 p_routing_revision,
2673 p_routing_revision_date,
2674 l_rev_date);
2675 end if;
2676
2677 end if;
2678
2679
2680 exception
2681 when others then
2682 x_error_code := SQLCODE;
2683 x_err_msg := 'WSMPUTIL.validate_non_std_references (stmt_num='||l_stmt_num||'): '||SUBSTR(SQLERRM,1,1000);
2684 return;
2685
2686 end validate_non_std_references;
2687
2688
2689
2690 -- abb H, added by abedajna for optional scrap accounting project
2691 -- this function checks whether the org for the wip_entity_id that is passed has scrap accounting
2692 -- enabled or not.
2693 -- returns 1 if scrap accounting should be reckoned enabled for the job,
2694 -- 2 if it should be reckoned disabled for the job, zero => error
2695
2696
2697 FUNCTION WSM_ESA_ENABLED(p_wip_entity_id IN NUMBER DEFAULT NULL,
2698 err_code OUT NOCOPY NUMBER,
2699 err_msg OUT NOCOPY VARCHAR2,
2700 p_org_id IN NUMBER DEFAULT NULL,
2701 p_job_type IN NUMBER DEFAULT NULL) RETURN INTEGER IS
2702
2703 l_organization_id NUMBER;
2704 l_job_type NUMBER;
2705 l_stmt_no NUMBER;
2706 l_est_scrap_accounting NUMBER;
2707
2708 begin
2709
2710 err_code := 0;
2711 err_msg := '';
2712
2713 if p_job_type = 3 then
2714 return 2; --disabled
2715 end if;
2716
2717 l_stmt_no := 5;
2718 if p_org_id is not null then
2719 select nvl(ESTIMATED_SCRAP_ACCOUNTING, 1)
2720 into l_est_scrap_accounting
2721 from wsm_parameters
2722 where organization_id = p_org_id;
2723
2724 return l_est_scrap_accounting;
2725 end if;
2726
2727 if p_wip_entity_id is not null then
2728
2729 l_stmt_no := 10;
2730 select wdj.organization_id, wdj.job_type
2731 into l_organization_id, l_job_type
2732 from wip_discrete_jobs wdj, wip_entities we
2733 where wdj.wip_entity_id = p_wip_entity_id
2734 and wdj.wip_entity_id = we.wip_entity_id
2735 and we.entity_type = 5;
2736
2737 if l_job_type = 3 then
2738 return 2; --disabled
2739 else
2740 l_stmt_no:= 20;
2741 select nvl(ESTIMATED_SCRAP_ACCOUNTING, 1)
2742 into l_est_scrap_accounting
2743 from wsm_parameters
2744 where organization_id = l_organization_id;
2745
2746 return l_est_scrap_accounting;
2747 end if;
2748
2749 end if;
2750
2751 exception
2752 when others then
2753 err_code := SQLCODE;
2754 err_msg := 'WSMPUTIL.WSM_ESA_ENABLED (stmt_num='||l_stmt_no||'): '||SUBSTR(SQLERRM,1,1000);
2755 return 0;
2756 end;
2757
2758
2759 -- abb H, added by abedajna for optional scrap accounting project
2760 -- This API checks if all jobs in an org have one of the following status types or not:
2761 -- Unreleased, Closed On-Hold with date_released null and Cancelled. If all jobs in the
2762 -- org are in one of the statuses mentioned the API returns 0, else it returns 1. When the
2763 -- user creates a new org and wishes to setup wsm parameters in the org, this procedure is
2764 -- called by the Parameters form. In this case no row exists in wsm_parameters for the
2765 -- job as yet. In that case the proc has been designed to return 2.
2766
2767 FUNCTION WSM_CHANGE_ESA_FLAG(p_org_id IN NUMBER,
2768 err_code OUT NOCOPY NUMBER,
2769 err_msg OUT NOCOPY VARCHAR2) RETURN INTEGER IS
2770
2771 ret_val1 NUMBER := 0;
2772 l_dummy NUMBER;
2773
2774 begin
2775
2776 err_code := 0;
2777 err_msg := '';
2778
2779 BEGIN
2780 -- added by abb to take care of the case when the form is opened the first time
2781 -- in an org, Then there's no row in the wsm_parameters and change of flag
2782 -- should be allowed.
2783 begin
2784 select 1
2785 into l_dummy
2786 from wsm_parameters
2787 where organization_id = p_org_id;
2788 exception
2789 when no_data_found then return 2;
2790 end;
2791
2792 begin
2793 select 1
2794 into ret_val1
2795 from wip_discrete_jobs wdj, wip_entities we
2796 where wdj.organization_id = p_org_id
2797 and wdj.wip_entity_id = we.wip_entity_id
2798 and we.entity_type = 5
2799 and wdj.status_type = 6
2800 and wdj.date_released is not null;
2801 exception
2802 when too_many_rows then ret_val1 := 1;
2803 when no_data_found then ret_val1 := 0;
2804 end;
2805
2806 if ret_val1 = 1 then
2807 return ret_val1;
2808 else
2809 begin
2810 -- Bug#2872306 - TOPSQL Fix -- BBK
2811 -- Pushed it under dual exists condition check.
2812 -- The purpose of this sql is to find if there are OPEN Lotbased Jobs.
2813 select 1
2814 into ret_val1
2815 from dual
2816 where exists (select 1
2817 from wip_discrete_jobs wdj, wip_entities we
2818 where wdj.organization_id = p_org_id
2819 and wdj.wip_entity_id = we.wip_entity_id
2820 and we.entity_type = 5
2821 and wdj.status_type not in (1,7,12,6)
2822 );
2823 exception
2824 when too_many_rows then ret_val1 := 1;
2825 when no_data_found then ret_val1 := 0;
2826 end;
2827 return ret_val1;
2828 end if;
2829
2830 END;
2831
2832 EXCEPTION
2833 when others then
2834 err_code := SQLCODE;
2835 err_msg := 'WSMPUTIL.WSM_CHANGE_ESA_FLAG: '||SUBSTR(SQLERRM,1,1000);
2836 return 0;
2837 end;
2838
2839
2840 /***************************************************************************************/
2841
2842 -- CZH.I_OED-1
2843 -- return 0 if no disabled op is found in the routing
2844 -- return 1 if disabled op's are found in the routing
2845 -- return -1 if any unexpected error is encountered.
2846
2847 FUNCTION network_with_disabled_op (
2848 p_routing_sequence_id IN NUMBER,
2849 p_routing_rev_date IN DATE,
2850 x_err_code OUT NOCOPY NUMBER,
2851 x_err_msg OUT NOCOPY VARCHAR2
2852 ) RETURN INTEGER IS
2853 x_return NUMBER;
2854 l_rtg_rev_date DATE;
2855 BEGIN
2856 x_return := 0;
2857 x_err_code := 0;
2858 x_err_msg := NULL;
2859 l_rtg_rev_date := NVL(p_routing_rev_date, SYSDATE);
2860
2861 --
2862 -- bugfix 2721157: Performance Problem - Replaced WHERE EXISTS with WHERE IN clause
2863 -- since the sub-query has a better selectivity.
2864 -- Also, broke the query into 2 parts.
2865 --
2866
2867 begin
2868 select 1
2869 into x_return
2870 from bom_operation_networks bon
2871 where bon.from_op_seq_id in (select bos.operation_sequence_id
2872 from bom_operation_sequences bos
2873 where bos.routing_sequence_id = p_routing_sequence_id
2874 --BC: CZH.I_OED-2, should consider replacement op
2875 -- and NOT(bos.effectivity_date <= l_rtg_rev_date
2876 -- and nvl(bos.disable_date, l_rtg_rev_date+1) > l_rtg_rev_date)
2877 and nvl(WSMPUTIL.replacement_op_seq_id( bos.operation_sequence_id,
2878 l_rtg_rev_date), -1) = -1 )
2879 --EC: CZH.I_OED-2
2880 and rownum = 1; -- Added ROWNUM to limit the number of rows accessed
2881 exception
2882
2883 when NO_DATA_FOUND then
2884
2885 select 1
2886 into x_return
2887 from bom_operation_networks bon
2888 where bon.to_op_seq_id in ( select bos.operation_sequence_id
2889 from bom_operation_sequences bos
2890 where bos.routing_sequence_id = p_routing_sequence_id
2891 --BC: CZH.I_OED-2, should consider replacement op
2892 -- and NOT(bos.effectivity_date <= l_rtg_rev_date
2893 -- and nvl(bos.disable_date, l_rtg_rev_date+1) > l_rtg_rev_date)
2894 and nvl(WSMPUTIL.replacement_op_seq_id( bos.operation_sequence_id,
2895 l_rtg_rev_date), -1) = -1 )
2896 --EC: CZH.I_OED-2
2897 and rownum = 1; -- Added ROWNUM to limit the number of rows accessed
2898
2899 end;
2900
2901 RETURN x_return;
2902
2903
2904 EXCEPTION
2905 WHEN NO_DATA_FOUND THEN
2906 x_return := 0;
2907 RETURN x_return;
2908
2909 WHEN TOO_MANY_ROWS THEN
2910 x_return := 1;
2911 RETURN x_return;
2912
2913 WHEN OTHERS THEN
2914 x_err_code := SQLCODE;
2915 x_err_msg := 'WSMPUTIL.NETWORK_WITH_DISABLED_OP: '||SUBSTR(SQLERRM,1,1000);
2916 RETURN -1; -- bugfix2721157: return -1 instead of 0 to distinguish between expected and unexpected errors.
2917 END network_with_disabled_op;
2918
2919 /***************************************************************************************/
2920
2921 -- CZH.I_OED-1
2922 -- return 0 if network dose not have effective primary path up to p_op_seq_num
2923 -- return 1 if network has effective primary path up to p_op_seq_num
2924
2925 FUNCTION primary_path_is_effective_till (
2926 p_routing_sequence_id IN NUMBER,
2927 p_routing_rev_date IN DATE,
2928 p_start_op_seq_id IN OUT NOCOPY NUMBER,
2929 p_op_seq_num IN NUMBER,
2930 x_err_code OUT NOCOPY NUMBER,
2931 x_err_msg OUT NOCOPY VARCHAR2
2932 ) RETURN INTEGER IS
2933 l_op_seq_id NUMBER;
2934 l_op_seq_num NUMBER;
2935 e_bad_path EXCEPTION;
2936 l_rtg_rev_date DATE;
2937
2938 CURSOR primary_cur IS (
2939 SELECT to_op_seq_id
2940 FROM bom_operation_networks
2941 WHERE transition_type = 1
2942 START WITH from_op_seq_id = l_op_seq_id
2943 AND transition_type = 1
2944 CONNECT BY from_op_seq_id = PRIOR to_op_seq_id
2945 AND transition_type = 1
2946 );
2947
2948 BEGIN
2949 x_err_code := 0;
2950 x_err_msg := NULL;
2951 l_rtg_rev_date := NVL(p_routing_rev_date, SYSDATE);
2952
2953 -- Call find_routing_start if p_start_op_seq_id is not specified.
2954 -- If p_start_op_seq_id is specified, it should be effective,
2955 -- however, even if it is not effective it will be caught later on
2956 IF(p_start_op_seq_id IS NULL) THEN
2957 WSMPUTIL.FIND_ROUTING_START(
2958 p_routing_sequence_id,
2959 SYSDATE, -- CZH: call with SYSDATE
2960 p_start_op_seq_id,
2961 x_err_code,
2962 x_err_msg);
2963 IF (x_err_code <> 0 ) THEN
2964 raise e_bad_path;
2965 END IF;
2966 END IF;
2967
2968 l_op_seq_id := p_start_op_seq_id;
2969
2970 OPEN primary_cur;
2971 LOOP
2972 -- get bos.operation_seq_num and compare with p_op_seq_num
2973 begin
2974 SELECT operation_seq_num
2975 INTO l_op_seq_num
2976 FROM bom_operation_sequences
2977 WHERE operation_sequence_id = l_op_seq_id
2978 AND routing_sequence_id = p_routing_sequence_id;
2979 -- BD: CZH.I_OED-2, should consider replacement
2980 --AND effectivity_date <= l_rtg_rev_date
2981 --AND nvl(disable_date, l_rtg_rev_date+2) > l_rtg_rev_date;
2982 -- ED: CZH.I_OED-2
2983
2984 exception
2985 WHEN others THEN
2986 raise e_bad_path;
2987 end;
2988
2989 IF l_op_seq_num = p_op_seq_num THEN
2990 return 1;
2991 END IF;
2992
2993 FETCH primary_cur INTO l_op_seq_id;
2994 EXIT when primary_cur%NOTFOUND;
2995
2996 --BA: CZH.I_OED-2, test if it has a effective replacement
2997 IF( NVL(WSMPUTIL.replacement_op_seq_id(
2998 l_op_seq_id,
2999 l_rtg_rev_date), -1) = -1) THEN
3000 raise e_bad_path;
3001 END IF;
3002 --EA: CZH.I_OED-2
3003 END LOOP;
3004 CLOSE primary_cur;
3005
3006 x_err_code := 1;
3007 x_err_msg := 'operation ' || p_op_seq_num || ' is not on primary path';
3008 return 0;
3009
3010 EXCEPTION
3011 WHEN e_bad_path THEN
3012 return 0;
3013 WHEN others THEN
3014 x_err_code := SQLCODE;
3015 x_err_msg := substr(SQLERRM,1,200);
3016 return 0;
3017 END;
3018
3019 /***************************************************************************************/
3020 -- CZH.I_OED-1
3021 -- return 0 if current operation does not have effective next operation
3022 -- return 1 if current operation has effective next operation
3023 -- return 2 if current operation is the last operation
3024 -- return 3 if current operation is at outside routing
3025 FUNCTION effective_next_op_exists (
3026 p_organization_id IN NUMBER,
3027 p_wip_entity_id IN NUMBER,
3028 p_wo_op_seq_num IN NUMBER,
3029 p_end_op_seq_id IN NUMBER, -- CZH.I_9999
3030 x_err_code OUT NOCOPY NUMBER,
3031 x_err_msg OUT NOCOPY VARCHAR2
3032 ) RETURN INTEGER IS
3033 l_count NUMBER;
3034 l_return NUMBER:= 0;
3035 l_wo_op_seq_id NUMBER;
3036 --l_last_op_seq_num NUMBER; -- DEL: CZH.I_9999
3037 l_rtg_seq_id NUMBER := NULL; -- ADD: CZH.I_9999
3038 l_rtg_rev_date DATE := NULL; -- ADD: CZH.I_9999
3039 l_end_op_seq_id NUMBER; -- ADD: CZH.I_9999
3040
3041 BEGIN
3042 x_err_code := 0;
3043 x_err_msg := NULL;
3044
3045 -- BD: CZH.I_9999, 9999 is no longer the last operation
3046 /****************
3047 -- At the last operation
3048 SELECT nvl(LAST_OPERATION_SEQ_NUM,9999)
3049 INTO l_last_op_seq_num
3050 FROM WSM_PARAMETERS
3051 WHERE ORGANIZATION_ID = p_organization_id;
3052 if(p_wo_op_seq_num = l_last_op_seq_num) then
3053 return 2; -- at last operation
3054 end if;
3055 ****************/
3056 -- ED: CZH.I_9999
3057
3058 SELECT OPERATION_SEQUENCE_ID
3059 INTO l_wo_op_seq_id
3060 FROM WIP_OPERATIONS
3061 WHERE ORGANIZATION_ID = p_organization_id
3062 AND wip_entity_id = p_wip_entity_id
3063 AND OPERATION_SEQ_NUM = p_wo_op_seq_num;
3064
3065 IF(NVL(l_wo_op_seq_id, -1) = -1) THEN
3066 return 3; -- at outside rtg
3067
3068 -- BA: CZH.I_9999, check if at the last operation
3069 ELSE
3070 IF ( p_end_op_seq_id IS NOT NULL ) THEN -- do not call find_routing_end again
3071
3072 l_end_op_seq_id := p_end_op_seq_id;
3073
3074 ELSE -- call find_routing_end if p_end_op_seq_id is NULL
3075
3076 select common_routing_sequence_id,
3077 routing_revision_date
3078 into l_rtg_seq_id,
3079 l_rtg_rev_date
3080 from wip_discrete_jobs
3081 where wip_entity_id = p_wip_entity_id;
3082
3083 WSMPUTIL.find_routing_end (
3084 p_routing_sequence_id => l_rtg_seq_id,
3085 p_routing_rev_date => l_rtg_rev_date,
3086 end_op_seq_id => l_end_op_seq_id,
3087 x_err_code => x_err_code,
3088 x_err_msg => x_err_msg);
3089 IF (x_err_code <> 0) THEN
3090 return 0; -- no valid next operation, no end op in routing
3091 END IF;
3092
3093 -- BA: CZH.I_OED-2, should use the replacement
3094 l_end_op_seq_id := WSMPUTIL.replacement_op_seq_id(
3095 l_end_op_seq_id,
3096 l_rtg_rev_date);
3097 -- EA: CZH.I_OED-2
3098
3099 END IF;
3100
3101 IF (l_wo_op_seq_id = l_end_op_seq_id) THEN
3102 return 2; -- at last operation
3103 end if;
3104
3105 -- EA: CZH.I_9999
3106 END IF;
3107
3108 IF( l_rtg_seq_id IS NULL) THEN -- do not call this again if called before
3109 select common_routing_sequence_id,
3110 routing_revision_date
3111 into l_rtg_seq_id,
3112 l_rtg_rev_date
3113 from wip_discrete_jobs
3114 where wip_entity_id = p_wip_entity_id;
3115 END IF;
3116
3117 SELECT 1
3118 INTO l_count
3119 FROM sys.dual
3120 WHERE exists(
3121 select 1
3122 from bom_operation_networks bon
3123 --where NVL(WSMPUTIL.replacement_op_seq_id(
3124 -- bon.from_op_seq_id,
3125 -- l_rtg_rev_date), -1) = l_wo_op_seq_id
3126 where bon.from_op_seq_id IN (
3127 select bos.operation_sequence_id
3128 from bom_operation_sequences bos,
3129 bom_operation_sequences bos2
3130 where bos.operation_seq_num = bos2.operation_seq_num
3131 AND bos.routing_sequence_id = bos2.routing_sequence_id
3132 AND bos2.operation_sequence_id = l_wo_op_seq_id
3133 )
3134 and NVL(WSMPUTIL.replacement_op_seq_id(
3135 bon.to_op_seq_id,
3136 l_rtg_rev_date), -1) <> -1
3137 );
3138
3139 IF (l_count = 1) THEN
3140 return 1; -- having valid next operation(s)
3141 ELSE
3142 return 0; -- no valid next operation
3143 END IF;
3144
3145 EXCEPTION
3146 WHEN others THEN
3147 x_err_code := SQLCODE;
3148 x_err_msg := substr(SQLERRM,1,200);
3149 return 0;
3150 END effective_next_op_exists;
3151
3152
3153 /***************************************************************************************/
3154 --this is to make the UTIL compatible with 1158 + OED-1
3155 --this function is called from Move Txn form/interface on OSFM 1158+OED-1 codeline
3156 FUNCTION effective_next_op_exits (
3157 p_organization_id IN NUMBER,
3158 p_wip_entity_id IN NUMBER,
3159 p_wo_op_seq_num IN NUMBER,
3160 x_err_code OUT NOCOPY NUMBER,
3161 x_err_msg OUT NOCOPY VARCHAR2
3162 ) RETURN INTEGER IS
3163 l_count NUMBER;
3164 l_return NUMBER := 0;
3165 l_last_op_seq_num NUMBER;
3166 l_op_seq_id NUMBER;
3167 l_rtg_rev_date DATE :=NULL;
3168 BEGIN
3169 x_err_code := 0;
3170 x_err_msg := NULL;
3171
3172 -- At the last operation
3173 SELECT nvl(LAST_OPERATION_SEQ_NUM,9999)
3174 INTO l_last_op_seq_num
3175 FROM WSM_PARAMETERS
3176 WHERE ORGANIZATION_ID = p_organization_id;
3177 if(p_wo_op_seq_num = l_last_op_seq_num) then
3178 return 2; -- at last operation
3179 end if;
3180
3181 -- At outside routing operation
3182 SELECT OPERATION_SEQUENCE_ID
3183 INTO l_op_seq_id
3184 FROM WIP_OPERATIONS
3185 WHERE ORGANIZATION_ID = p_organization_id
3186 AND wip_entity_id = p_wip_entity_id
3187 AND OPERATION_SEQ_NUM = p_wo_op_seq_num;
3188 IF(NVL(l_op_seq_id, -1) = -1) THEN
3189 return 3; -- at outside rtg
3190 END IF;
3191
3192 -- to be compatible with base release, query from bom tables directly.
3193
3194 /** -- Check WSM_NEXT_OPERATION_V
3195 select count(*)
3196 into l_count
3197 from wsm_next_operations_v
3198 where wip_entity_id = p_wip_entity_id
3199 and to_wo_operation_seq_num >= p_wo_op_seq_num
3200 and fm_operation_seq_num = p_wo_op_seq_num;
3201 **/
3202 --begin fix
3203 select nvl(routing_revision_date, sysdate)
3204 into l_rtg_rev_date
3205 from wip_discrete_jobs
3206 where wip_entity_id = p_wip_entity_id;
3207
3208 select count(*)
3209 into l_count
3210 from bom_operation_networks bon
3211 where bon.from_op_seq_id = l_op_seq_id
3212 and exists (select bos.operation_sequence_id
3213 from bom_operation_sequences bos
3214 where bos.operation_sequence_id = bon.to_op_seq_id
3215 and bos.effectivity_date <= l_rtg_rev_date
3216 and NVL(bos.disable_date, l_rtg_rev_date) >= l_rtg_rev_date
3217 );
3218 --end fix
3219
3220 IF (l_count <> 0) THEN
3221 return 1; -- having valid next operation(s)
3222 ELSE
3223 return 0; -- no valid next operation
3224 END IF;
3225
3226 EXCEPTION
3227 WHEN others THEN
3228 x_err_code := SQLCODE;
3229 x_err_msg := substr(SQLERRM,1,200);
3230 return 0;
3231 END;
3232
3233 /***************************************************************************************/
3234 --this is to make the UTIL compatible with 1158 and 1157
3235 FUNCTION wlt_if_costed (
3236 p_wip_entity_id in number )
3237 RETURN NUMBER IS
3238
3239 l_dummy number;
3240
3241 BEGIN
3242 select 1
3243 into l_dummy
3244 from wsm_split_merge_transactions wsmt,
3245 wsm_sm_resulting_jobs wrj,
3246 wip_entities we
3247 where wrj.wip_entity_id = p_wip_entity_id
3248 and wrj.transaction_id = wsmt.transaction_id
3249 and wsmt.transaction_type_id in (1,2,6)
3250 and we.wip_entity_id = wrj.wip_entity_id
3251 and we.entity_type = 5
3252 and nvl(wsmt.costed,1) <> 4;
3253
3254 return 2;
3255
3256
3257 EXCEPTION
3258 when NO_DATA_FOUND then
3259 return 1;
3260
3261 when TOO_MANY_ROWS then
3262 return 2;
3263
3264 when OTHERS then
3265 return 2;
3266
3267 END;
3268
3269 /***************************************************************************************/
3270 -- Moved this procedure from WSMPLTOP to here
3271
3272 PROCEDURE check_charges_exist ( p_wip_entity_id IN NUMBER,
3273 p_organization_id IN NUMBER,
3274 p_op_seq_num IN NUMBER,
3275 p_op_seq_id IN NUMBER,
3276 p_charges_exist OUT NOCOPY NUMBER,
3277 p_manually_added_comp OUT NOCOPY NUMBER,
3278 p_issued_material OUT NOCOPY NUMBER,
3279 p_manually_added_resource OUT NOCOPY NUMBER,
3280 p_issued_resource OUT NOCOPY NUMBER,
3281 x_error_code OUT NOCOPY NUMBER,
3282 x_error_msg OUT NOCOPY VARCHAR2)
3283 IS
3284
3285 l_stmt_num NUMBER;
3286 l_dummy_number NUMBER;
3287 e_proc_exception EXCEPTION;
3288
3289 l_rtg_op_seq_num NUMBER default 0;
3290 l_qty_at_tomove NUMBER default 0;
3291 --l_last_op_seq_num NUMBER := 9999; Removed for 999 project
3292
3293 --Start additions to fix bug #2404640--
3294 l_consider_op_seq1 NUMBER;
3295 l_rtg_seq_id NUMBER;
3296 l_rtg_rev_dt DATE;
3297 l_start_op_seq_id NUMBER;
3298 l_job_start_op_seq_id NUMBER;
3299 --End additions to fix bug #2404640--
3300 l_first_op_seq_num NUMBER := 10; --bugfix 3546334
3301
3302
3303 BEGIN
3304 l_stmt_num := 10;
3305
3306 IF (l_debug = 'Y') THEN
3307 fnd_file.put_line(fnd_file.log, 'WSMPUTIL.check_charges_exist parameters are :'||
3308 'p_wip_entity_id='||p_wip_entity_id||
3309 ', p_organization_id='||p_organization_id||
3310 ', p_op_seq_num='||p_op_seq_num||
3311 ', p_op_seq_id='||p_op_seq_id);
3312 END IF;
3313
3314 p_charges_exist := 0;
3315 p_manually_added_comp := 0;
3316 p_issued_material := 0;
3317 p_manually_added_resource := 0;
3318 p_issued_resource := 0;
3319
3320 x_error_code := 0;
3321 x_error_msg := NULL;
3322
3323 --Start deletions for 9999 project
3324 --Start additions to fix bug #2458260
3325 --SELECT nvl(last_operation_seq_num, 9999)
3326 --INTO l_last_op_seq_num
3327 --FROM wsm_parameters
3328 --WHERE organization_id = p_organization_id;
3329 --End additions to fix bug #2458260
3330 --End deletions for 9999 project
3331
3332 --BA: 3546334 get first op_seq_num. when populate WRO, op_seq_num 1 in bic will
3333 --be copied into WRO as comp req at first operation. in this case seq_num in WRO
3334 --and BIC not match, thus it was incorrectly be treated as manually added comp.
3335 l_stmt_num := 15;
3336 select min(operation_seq_num)
3337 into l_first_op_seq_num
3338 from wip_operations
3339 where wip_entity_id = p_wip_entity_id;
3340 --EA: 3546334
3341
3342
3343 -- Note the JOB OPERATION SEQUENCE NUMBER need not have to be the
3344 -- same as the ONE in ROUTING. Hence, First get the corresponding
3345 -- operation sequence number in Routing and use this whenever
3346 -- a join is made with BIC or BOS --BBK
3347
3348 l_stmt_num := 20;
3349
3350 Begin
3351
3352 Select bos.operation_seq_num, wo.quantity_waiting_to_move
3353 into l_rtg_op_seq_num, l_qty_at_tomove
3354 From BOM_OPERATION_SEQUENCES bos, wip_operations wo
3355 Where bos.operation_sequence_id = NVL(wo.operation_sequence_id, -999)
3356 and wo.wip_entity_id = p_wip_entity_id
3357 and wo.operation_seq_num = p_op_seq_num
3358 and wo.organization_id = p_organization_id
3359 and wo.repetitive_schedule_id is NULL;
3360
3361 IF (l_debug = 'Y') THEN
3362 fnd_file.put_line(fnd_file.log, 'At '||l_stmt_num||' l_rtg_op_seq_num='||l_rtg_op_seq_num);
3363 fnd_file.put_line(fnd_file.log, 'At '||l_stmt_num||' l_qty_at_tomove='||l_qty_at_tomove);
3364 END IF;
3365
3366 Exception
3367 WHEN NO_DATA_FOUND Then -- Job is not at this opseqnum or is at a Jump Operation.
3368 p_charges_exist := 0;
3369 return;
3370 End;
3371
3372 l_stmt_num := 30;
3373
3374 --VJ: Start Additions to fix bug #2378859--
3375 -- Check for possible explosion of phantom components.
3376 BEGIN
3377
3378 Select 2 into p_manually_added_comp
3379 from wip_requirement_operations wro
3380 where wro.wip_entity_id = p_wip_entity_id
3381 and wro.organization_id = p_organization_id
3382 and wro.operation_seq_num = 0-p_op_seq_num -- -ve op seq num for exploded components.
3383 and wro.wip_supply_type = 6 -- Phantom components exploded
3384 and wro.required_quantity <> 0
3385 and NOT EXISTS (select 1
3386 from bom_inventory_components bic, wip_discrete_jobs wdj
3387 where bic.bill_sequence_id = NVL(wdj.common_bom_sequence_id, -999)
3388 and bic.component_item_id = wro.inventory_item_id
3389 and (bic.operation_seq_num = l_rtg_op_seq_num -- NOTE:use of BOS opseq Num
3390 or
3391 bic.operation_seq_num = 1 and p_op_seq_num = l_first_op_seq_num) --bugfix 3546334
3392 and wdj.wip_entity_id = wro.wip_entity_id
3393 and wdj.organization_id = wro.organization_id);
3394
3395 IF (l_debug = 'Y') THEN
3396 fnd_file.put_line(fnd_file.log, 'At '||l_stmt_num||' p_manually_added_comp='||p_manually_added_comp);
3397 END IF;
3398
3399 EXCEPTION
3400
3401 WHEN NO_DATA_FOUND THEN
3402 p_manually_added_comp := 0;
3403 x_error_code := 0;
3404 x_error_msg := 'WSMPUTIL.check_charges_exist (' || l_stmt_num
3405 || ') : No components have been added to this job manually or phnatom exploded. Job id= '
3406 || p_wip_entity_id;
3407
3408 WHEN TOO_MANY_ROWS THEN
3409 p_manually_added_comp := 2;
3410 x_error_code := 0;
3411 x_error_msg := 'WSMPUTIL.check_charges_exist ('
3412 || l_stmt_num
3413 || ') : Phantom Components have been exploded in this operation for this job. Job id ='
3414 || p_wip_entity_id;
3415
3416 WHEN OTHERS THEN
3417 x_error_code := SQLCODE;
3418 x_error_msg := 'WSMPUTIL.check_charges_exist (' || l_stmt_num
3419 || ') : Exception: Job id = ' || p_wip_entity_id;
3420 raise e_proc_exception;
3421
3422 END; --check for possible phantom explosions
3423 --VJ: End Additions to fix bug #2378859--
3424
3425 -- This sql checks if there are any components that the user "intends to" issue
3426 -- manually. This pl/sql block looks for components in WRO that are not
3427 -- present in bom_inventory_components. It does not matter whether or not the user
3428 -- has already issued this material to job currently, what matters is recording the
3429 -- user's "intention" to issue these matls manually.
3430
3431 -- added by BBK.
3432 -- Check Manual Material Requirement at this operation.
3433
3434 l_stmt_num := 40;
3435
3436 IF (p_manually_added_comp = 0) THEN --VJ: Added condition to fix bug #2378859--
3437
3438 --Start changes to fix bug #2404640--
3439 l_stmt_num := 50;
3440
3441 --1. Get the opseqid of the start operation of the routing
3442 SELECT common_routing_sequence_id,
3443 routing_revision_date
3444 INTO l_rtg_seq_id,
3445 l_rtg_rev_dt
3446 FROM wip_discrete_jobs
3447 WHERE wip_entity_id = p_wip_entity_id
3448 AND organization_id = p_organization_id;
3449
3450 l_stmt_num := 60;
3451
3452 find_routing_start(p_routing_sequence_id => l_rtg_seq_id,
3453 p_routing_rev_date => l_rtg_rev_dt,
3454 start_op_seq_id => l_start_op_seq_id,
3455 x_err_code => x_error_code,
3456 x_err_msg => x_error_msg);
3457
3458 l_stmt_num := 70;
3459
3460 --2. Get the opseqid of the current operation of the job
3461 SELECT operation_sequence_id
3462 INTO l_job_start_op_seq_id
3463 FROM wip_operations
3464 WHERE wip_entity_id = p_wip_entity_id
3465 AND organization_id = p_organization_id
3466 AND operation_seq_num = p_op_seq_num;
3467
3468 l_consider_op_seq1 := 0;
3469
3470 --3. If the job is currently at the first operation of the routing
3471 IF (l_start_op_seq_id = l_job_start_op_seq_id) THEN
3472
3473 l_stmt_num := 80;
3474
3475 --4. Check if there is an operation with sequence 1 in the routing of the job
3476 BEGIN
3477 SELECT 0
3478 INTO l_consider_op_seq1
3479 FROM bom_operation_sequences
3480 WHERE routing_sequence_id = l_rtg_seq_id
3481 AND operation_seq_num = 1;
3482
3483 EXCEPTION
3484 WHEN NO_DATA_FOUND THEN
3485 l_consider_op_seq1 := 1;
3486 WHEN OTHERS THEN
3487 x_error_code := SQLCODE;
3488 x_error_msg := 'WSMPUTIL.check_charges_exist (' || l_stmt_num
3489 || ') : Exception: Job id = ' || p_wip_entity_id;
3490 raise e_proc_exception;
3491 END;
3492 END IF;
3493
3494 --5. If l_consider_op_seq1 = 1, consider opseq 1 from bom while checking for components in the current op
3495 BEGIN
3496 IF (l_consider_op_seq1 = 1) THEN
3497 l_stmt_num := 90;
3498
3499 SELECT 1
3500 INTO p_manually_added_comp
3501 FROM wip_requirement_operations wro
3502 WHERE wro.wip_entity_id = p_wip_entity_id
3503 AND wro.organization_id = p_organization_id
3504 AND wro.operation_seq_num = p_op_seq_num
3505 AND wro.required_quantity <> 0
3506 AND NOT EXISTS (
3507 select 1
3508 from bom_inventory_components bic, wip_discrete_jobs wdj
3509 where bic.bill_sequence_id = NVL(wdj.common_bom_sequence_id, -999)
3510 and bic.component_item_id = wro.inventory_item_id
3511 --and bic.operation_seq_num in (1, l_rtg_op_seq_num) -- NOTE:use of BOS opseq Num
3512 and (bic.operation_seq_num = l_rtg_op_seq_num -- NOTE:use of BOS opseq Num
3513 or
3514 bic.operation_seq_num = 1 and p_op_seq_num = l_first_op_seq_num) --bugfix 3546334
3515 and wdj.wip_entity_id = wro.wip_entity_id
3516 and wdj.organization_id = wro.organization_id);
3517 ELSE
3518 --End changes to fix bug #2404640--
3519 l_stmt_num := 100;
3520
3521 SELECT 1
3522 INTO p_manually_added_comp
3523 FROM wip_requirement_operations wro
3524 WHERE wro.wip_entity_id = p_wip_entity_id
3525 AND wro.organization_id = p_organization_id
3526 AND wro.operation_seq_num = p_op_seq_num
3527 AND wro.required_quantity <> 0
3528 AND NOT EXISTS (
3529 select 1
3530 from bom_inventory_components bic, wip_discrete_jobs wdj
3531 where bic.bill_sequence_id = NVL(wdj.common_bom_sequence_id, -999)
3532 and bic.component_item_id = wro.inventory_item_id
3533 and bic.operation_seq_num = l_rtg_op_seq_num -- NOTE:use of BOS opseq Num
3534 and wdj.wip_entity_id = wro.wip_entity_id
3535 and wdj.organization_id = wro.organization_id);
3536
3537 END IF;
3538
3539 IF SQL%ROWCOUNT <> 0 Then
3540 p_manually_added_comp := 1;
3541 End If;
3542
3543
3544 IF (l_debug = 'Y') THEN
3545 fnd_file.put_line(fnd_file.log, 'At '||l_stmt_num||' p_manually_added_comp='||p_manually_added_comp);
3546 END IF;
3547
3548 EXCEPTION
3549
3550 WHEN NO_DATA_FOUND THEN
3551 p_manually_added_comp := 0;
3552 x_error_code := 0;
3553 x_error_msg := 'WSMPUTIL.check_charges_exist (' || l_stmt_num
3554 || ') : No components have been added to this job manually. Job id = '
3555 || p_wip_entity_id;
3556
3557 WHEN TOO_MANY_ROWS THEN
3558 p_manually_added_comp := 1;
3559 x_error_code := 0;
3560 x_error_msg := 'WSMPUTIL.check_charges_exist ('
3561 || l_stmt_num
3562 || ') : Components have been added to this job manually. Job id = '
3563 || p_wip_entity_id;
3564
3565 WHEN OTHERS THEN
3566 x_error_code := SQLCODE;
3567 x_error_msg := 'WSMPUTIL.check_charges_exist (' || l_stmt_num
3568 || ') : Exception: Job id = ' || p_wip_entity_id;
3569 raise e_proc_exception;
3570
3571 END; --check Manually Material Requirements
3572 END IF; --p_manually_added_comp = 0 --VJ: Added condition to fix bug #2378859--
3573
3574 -- Check if charges exist for this job. Looking at records in WRO will not give the correct
3575 -- picture if the cost processor has not been run between the time this job moved to the latest
3576 -- op in the n/w rtg to the time this code is run. So, one needs to look in MMT to see if
3577 -- any material has been *already issued* (since this job is at intraop step Q) from the
3578 -- inventory to the job. This is the purpose of the following pl/sql block.
3579
3580 l_stmt_num := 110;
3581
3582 If l_qty_at_tomove = 0 Then -- BBK
3583
3584 BEGIN
3585 --This SQL is commented out.New SQL is added that includes
3586 --mtl_material_transactions_temp also.
3587 /*
3588 select sum(primary_quantity)
3589 into l_dummy_number
3590 from mtl_material_transactions
3591 where organization_id = p_organization_id
3592 and transaction_source_id = p_wip_entity_id
3593 and operation_seq_num = p_op_seq_num
3594 --and transaction_source_type_id = 5 -- Job or Schedule
3595 -- VJ: Start changes to fix bug #2663468--
3596 and ((transaction_source_type_id = 5 -- Job or Schedule
3597 and transaction_action_id not in (40, 41, 42, 43)
3598 )
3599 or transaction_type_id not in (55, 56, 57, 58)
3600 )
3601 -- VJ: End changes to fix bug #2663468--
3602 group by inventory_item_id
3603 having sum(primary_quantity) <> 0;
3604 */
3605
3606 /*Start of Changes for Bug 3229281*/
3607 select sum(primary_quantity)
3608 into l_dummy_number
3609 from (
3610 select inventory_item_id,primary_quantity
3611 from mtl_material_transactions
3612 where organization_id = p_organization_id
3613 and transaction_source_id = p_wip_entity_id
3614 and operation_seq_num = p_op_seq_num
3615 and ((transaction_source_type_id = 5
3616 and transaction_action_id not in (40, 41, 42, 43)
3617 )
3618 or transaction_type_id not in (55, 56, 57, 58)
3619 )
3620 union all
3621 select inventory_item_id,primary_quantity
3622 from mtl_material_transactions_temp
3623 where organization_id = p_organization_id
3624 and transaction_source_id = p_wip_entity_id
3625 and operation_seq_num = p_op_seq_num
3626 and ((transaction_source_type_id = 5
3627 and transaction_action_id not in (40, 41, 42, 43)
3628 )
3629 or transaction_type_id not in (55, 56, 57, 58)
3630 )
3631 )
3632 group by inventory_item_id
3633 having sum(primary_quantity) <> 0;
3634 /*End of Changes for Bug 3229281*/
3635
3636 IF SQL%ROWCOUNT <> 0 Then
3637 p_issued_material := 1;
3638 End If;
3639
3640 IF (l_debug = 'Y') THEN
3641 fnd_file.put_line(fnd_file.log, 'At '||l_stmt_num||' l_dummy_number='||l_dummy_number);
3642 fnd_file.put_line(fnd_file.log, 'At '||l_stmt_num||' p_issued_material='||p_issued_material);
3643 END IF;
3644
3645 EXCEPTION
3646 WHEN NO_DATA_FOUND THEN
3647 p_issued_material := 0;
3648 x_error_code := 0;
3649 x_error_msg := 'WSMPUTIL.check_charges_exist ('
3650 || l_stmt_num
3651 || ') : No matl has been issued to this job. Job id = '
3652 || p_wip_entity_id;
3653
3654 WHEN TOO_MANY_ROWS THEN
3655 p_issued_material := 1;
3656 x_error_code := 0;
3657 x_error_msg := 'WSMPUTIL.check_charges_exist ('
3658 || l_stmt_num
3659 || ') Materials have been issued to this job. Job id = '
3660 || p_wip_entity_id;
3661
3662 WHEN OTHERS THEN
3663 x_error_code := SQLCODE;
3664 x_error_msg := substr(
3665 ('WSMPUTIL.check_charges_exist ('
3666 || l_stmt_num
3667 || ') Job id = '
3668 || p_wip_entity_id || ' : Exception = '||SQLERRM), 1, 1000);
3669 raise e_proc_exception;
3670 END;
3671
3672 l_stmt_num := 120;
3673
3674 -- Check if charges exist for this job. Looking at records in WRO will not give the correct
3675 -- picture if the cost processor has not been run between the time this job moved to the latest
3676 -- op in the n/w rtg to the time this code is run. So, one needs to look in WT to see if
3677 -- any resources have been *already issued* (since this job is at intraop step Q) from the
3678 -- inventory to the job. This is the purpose of the following pl/sql block.
3679
3680 BEGIN
3681 --Bug 3229281
3682 --The following sql is commented out because it does not include
3683 --resource txns in wip_cost_txn_interface table.
3684 /* select wip_entity_id
3685 into l_dummy_number
3686 from wip_transactions
3687 where organization_id = p_organization_id
3688 and wip_entity_id = p_wip_entity_id
3689 and operation_seq_num = p_op_seq_num; */
3690
3691 /*Start of Changes for Bug 3229281*/
3692 /*The following SQL checks if the net quantity of
3693 each resource is 0 or not.
3694 */
3695 select sum(primary_quantity)
3696 into l_dummy_number
3697 from
3698 (
3699 select resource_id,PRIMARY_QUANTITY
3700 from wip_transactions
3701 where organization_id = p_organization_id
3702 and wip_entity_id = p_wip_entity_id
3703 and operation_seq_num = p_op_seq_num
3704 and transaction_type in (1,3)
3705 UNION ALL
3706 select resource_id,PRIMARY_QUANTITY
3707 from wip_cost_txn_interface
3708 where organization_id = p_organization_id
3709 and wip_entity_id = p_wip_entity_id
3710 and operation_seq_num = p_op_seq_num
3711 and transaction_type in (1,3)
3712 )
3713 group by resource_id
3714 having sum(primary_quantity) <> 0;
3715
3716 /*End of Changes for Bug 3229281*/
3717
3718 IF SQL%ROWCOUNT <> 0 Then
3719 p_issued_resource := 1;
3720 End If;
3721
3722 IF (l_debug = 'Y') THEN
3723 fnd_file.put_line(fnd_file.log, 'At '||l_stmt_num||' l_dummy_number='||l_dummy_number);
3724 fnd_file.put_line(fnd_file.log, 'At '||l_stmt_num||' p_issued_resource='||p_issued_resource);
3725 END IF;
3726
3727 EXCEPTION
3728 WHEN NO_DATA_FOUND THEN
3729 p_issued_resource := 0;
3730 x_error_code := 0;
3731 x_error_msg := 'WSMPUTIL.check_charges_exist ('
3732 || l_stmt_num
3733 || ') : No resource have been issued to this job. Job id = '
3734 || p_wip_entity_id;
3735
3736 WHEN TOO_MANY_ROWS THEN
3737 p_issued_resource := 1;
3738 x_error_code := 0;
3739 x_error_msg := 'WSMPUTIL.check_charges_exist ('
3740 || l_stmt_num
3741 || ') Resources have been issued to this job. Job id = '
3742 || p_wip_entity_id;
3743
3744 WHEN OTHERS THEN
3745 x_error_code := SQLCODE;
3746 x_error_msg := substr(
3747 ('WSMPUTIL.check_charges_exist ('
3748 || l_stmt_num
3749 || ') Job id = '
3750 || p_wip_entity_id || ' : Exception = '||SQLERRM), 1, 1000);
3751 raise e_proc_exception;
3752 END;
3753
3754 Else -- Qty is at TOMOVE
3755 p_issued_material := 0;
3756 p_issued_resource := 0;
3757 x_error_code := 0;
3758 x_error_msg := 'WSMPUTIL.check_charges_exist ('
3759 || l_stmt_num
3760 || ') : Qty is at TOMOVE. Job id = '
3761 || p_wip_entity_id;
3762 End If; -- end of qty_at_tomove for materials and Resources
3763
3764 l_stmt_num := 130;
3765
3766 -- This sql checks if there are any resources that the user intends to issue
3767 -- manually. This pl/sql block looks for resources in WOR that are not
3768 -- present in bom_operation_resources. It does not matter whether or not the user
3769 -- has already issued these resources to job yet, what matters is recording the
3770 -- user's intention to issue these resources manually.
3771
3772 BEGIN
3773 -- added by BBK.
3774
3775 select 1 into p_manually_added_resource
3776 From wip_operation_resources wor
3777 Where wor.wip_entity_id = p_wip_entity_id
3778 and wor.operation_seq_num = p_op_seq_num
3779 and wor.repetitive_schedule_id is NULL
3780 and wor.applied_resource_units <> 0
3781 and NOT EXISTS (select 1
3782 From bom_operation_resources bor, wip_operations wo
3783 Where bor.operation_sequence_id = wo.operation_sequence_id
3784 and bor.resource_seq_num = wor.resource_seq_num
3785 and wo.wip_entity_id = wor.wip_entity_id
3786 and wo.operation_seq_num = wor.operation_seq_num);
3787
3788 IF SQL%ROWCOUNT <> 0 Then
3789 p_manually_added_resource := 1;
3790 End If;
3791
3792 IF (l_debug = 'Y') THEN
3793 fnd_file.put_line(fnd_file.log, 'At '||l_stmt_num||' p_manually_added_resource='||p_manually_added_resource);
3794 END IF;
3795
3796
3797 EXCEPTION
3798
3799 WHEN NO_DATA_FOUND THEN
3800 x_error_code := 0;
3801 p_manually_added_resource := 0;
3802 x_error_msg := 'WSMPUTIL.check_charges_exist ('
3803 || l_stmt_num
3804 || ') : Resources have not been issued manually to this job. Job id = '
3805 || p_wip_entity_id;
3806
3807 WHEN TOO_MANY_ROWS THEN
3808 p_manually_added_resource := 1;
3809 x_error_code := 0;
3810 x_error_msg := 'WSMPUTIL.check_charges_exist ('
3811 || l_stmt_num
3812 || ') Resources have been manually issued to this job. Job id = '
3813 || p_wip_entity_id;
3814
3815 WHEN OTHERS THEN
3816 x_error_code := SQLCODE;
3817 x_error_msg := substr(
3818 ('WSMPUTIL.check_charges_exist ('
3819 || l_stmt_num
3820 || ') Job id = '
3821 || p_wip_entity_id || ' : Exception = '||SQLERRM), 1, 1000);
3822 raise e_proc_exception;
3823 END;
3824
3825 -- Now, check if charges exist.
3826 l_stmt_num := 140;
3827
3828 if ( (p_issued_resource <> 0) or (p_manually_added_resource <> 0) or
3829 (p_issued_material <> 0) or (p_manually_added_comp <> 0)
3830 ) then
3831 p_charges_exist := 1;
3832 end if;
3833
3834 IF (l_debug = 'Y') THEN
3835 fnd_file.put_line(fnd_file.log, 'At '||l_stmt_num||' p_charges_exist='||p_charges_exist);
3836 END IF;
3837
3838 EXCEPTION
3839
3840 WHEN e_proc_exception THEN
3841 x_error_code := -99;
3842
3843 WHEN OTHERS THEN
3844 x_error_code := sqlcode;
3845 x_error_msg := substr(
3846 ('WSMPUTIL.check_charges_exist ('
3847 || l_stmt_num
3848 || ') Job id = '
3849 || p_wip_entity_id || ' : Exception = '||SQLERRM), 1, 1000);
3850
3851 END check_charges_exist;
3852
3853
3854
3855 /***************************************************************************************/
3856 -- CZH.I_OED-2
3857 -- return NULL if no effective replacement is found
3858 Function replacement_op_seq_id (
3859 p_op_seq_id NUMBER,
3860 p_routing_rev_date DATE
3861 ) RETURN INTEGER
3862 IS
3863 replacement_op_seq_id NUMBER := NULL;
3864 eff_date DATE := NULL;
3865 dis_date DATE := NULL;
3866 l_rtg_rev_date DATE;
3867 BEGIN
3868
3869 l_rtg_rev_date := NVL(p_routing_rev_date, SYSDATE);
3870
3871 SELECT operation_sequence_id,
3872 effectivity_date,
3873 disable_date
3874 INTO replacement_op_seq_id,
3875 eff_date,
3876 dis_date
3877 FROM bom_operation_sequences
3878 WHERE operation_sequence_id = p_op_seq_id;
3879
3880 --IF NOT( l_rtg_rev_date >= eff_date AND l_rtg_rev_date < nvl(dis_date, l_rtg_rev_date+1) ) THEN
3881 IF NOT( l_rtg_rev_date between eff_date and NVL(dis_date, l_rtg_rev_date+1) ) THEN
3882
3883 SELECT bos.operation_sequence_id
3884 INTO replacement_op_seq_id
3885 FROM bom_operation_sequences bos,
3886 bom_operation_sequences bos2
3887 WHERE l_rtg_rev_date between bos.effectivity_date and nvl(bos.disable_date, l_rtg_rev_date+1) --HH24MISS
3888 AND bos.operation_seq_num = bos2.operation_seq_num
3889 AND bos.routing_sequence_id = bos2.routing_sequence_id
3890 AND bos2.operation_sequence_id = p_op_seq_id;
3891
3892 END IF;
3893
3894 return replacement_op_seq_id;
3895 EXCEPTION
3896 WHEN NO_DATA_FOUND THEN
3897 return NULL;
3898
3899 WHEN OTHERS THEN
3900 return NULL;
3901 END replacement_op_seq_id;
3902
3903 -- OSP : This procedure determines if the routing (R), operation (O) in a routing
3904 -- or a standard operation (S) has PO Move charge type resources attached
3905 -- to them.
3906
3907
3908 FUNCTION check_po_move (
3909 p_sequence_id NUMBER,
3910 p_sequence_id_type VARCHAR2,
3911 p_routing_rev_date DATE,
3912 x_err_code OUT NOCOPY NUMBER,
3913 x_err_msg OUT NOCOPY VARCHAR2
3914
3915 ) RETURN BOOLEAN IS
3916
3917 x_rowcount INTEGER ;
3918
3919 BEGIN
3920 -- when the id passed is routing_sequence_id
3921
3922 if p_sequence_id_type = 'R' then
3923
3924 SELECT count(*)
3925 INTO x_rowcount
3926 FROM bom_operational_routings bor,
3927 bom_operation_resources bres,
3928 bom_operation_sequences bos
3929 WHERE bor.routing_sequence_id = p_sequence_id
3930 AND bor.common_routing_sequence_id = bos.routing_sequence_id
3931 AND bos.operation_sequence_id = bres.operation_sequence_id
3932 /* BD HH24MISS*/ /*
3933 AND nvl(p_routing_rev_date, SYSDATE)
3934 >= bos.effectivity_date
3935 AND nvl(p_routing_rev_date, SYSDATE)
3936 < nvl(bos.disable_date, nvl(p_routing_rev_date, SYSDATE)+1)
3937 */ /* ED HH24MISS*/
3938 /*BA HH24MISS */
3939 AND nvl(p_routing_rev_date, SYSDATE) BETWEEN
3940 bos.effectivity_date AND nvl(bos.disable_date, nvl(p_routing_rev_date, SYSDATE)+1)
3941 /*EA HH24MISS */
3942 AND bres.autocharge_type = WIP_CONSTANTS.PO_MOVE ;
3943
3944 -- when the id passed is operation_sequence_id
3945
3946 elsif p_sequence_id_type = 'O' then
3947
3948 SELECT count(*)
3949 INTO x_rowcount
3950 FROM bom_operation_resources bres,
3951 bom_operation_sequences bos
3952 WHERE bos.operation_sequence_id = p_sequence_id
3953 AND bos.operation_sequence_id = bres.operation_sequence_id
3954 /*BD HH24MISS */ /*
3955 AND nvl(p_routing_rev_date, SYSDATE)
3956 >= bos.effectivity_date
3957 AND nvl(p_routing_rev_date, SYSDATE)
3958 < nvl(bos.disable_date, nvl(p_routing_rev_date, SYSDATE)+1)
3959 */ /*ED HH24MISS */
3960 /*BA HH24MISS */
3961 AND nvl(p_routing_rev_date, SYSDATE) BETWEEN
3962 bos.effectivity_date AND nvl(bos.disable_date, nvl(p_routing_rev_date, SYSDATE)+1)
3963 /*EA HH24MISS */
3964 AND bres.autocharge_type = WIP_CONSTANTS.PO_MOVE ;
3965
3966 -- when the id passed is standard_operation_id
3967
3968 elsif p_sequence_id_type = 'S' then
3969
3970 SELECT count(*)
3971 INTO x_rowcount
3972 FROM bom_std_op_resources bsor
3973 WHERE bsor.standard_operation_id = p_sequence_id
3974 AND bsor.autocharge_type = WIP_CONSTANTS.PO_MOVE;
3975
3976 end if;
3977
3978 if x_rowcount <> 0 then
3979 return TRUE;
3980 else
3981 return FALSE;
3982 end if;
3983
3984 EXCEPTION
3985
3986 WHEN OTHERS THEN
3987 x_err_code := SQLCODE;
3988 x_err_msg := 'WSMPUTIL.CHECK_PO_MOVE' ||substrb(sqlerrm, 1,1000);
3989 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
3990 return FALSE;
3991
3992 END check_po_move ;
3993
3994
3995 --
3996 -- Bugfix 2617330
3997 --
3998 -- This new procedure will be used by WIP to determine if the lot based jobs
3999 -- can be closed or not. The API will accept 2 parameters: group_id and orgn_id
4000 -- Using these parameters, the API would identify all the lot based jobs in
4001 -- the table WIP_DJ_CLOSE_TEMP and validate these records.
4002 -- All jobs that fail in validation process would be printed and the value of
4003 -- column STATUS_TYPE in wip_dj_close_temp would be updated to 99.
4004 -- In the end, the status of these jobs in wip_discrete_jobs will be updated to 15 (Failed Close)
4005 -- and records in wip_dj_close_temp with status 99 will be deleted.
4006 --
4007 -- x_err_code will be set to 0 if there are any unprocessed/uncosted txn.
4008 -- Otherwise, x_err_code will have a value of 1.
4009 --
4010
4011 PROCEDURE validate_lbj_before_close (
4012 p_group_id in number,
4013 p_organization_id in number,
4014 x_err_code out nocopy number,
4015 x_err_msg out nocopy varchar2,
4016 x_return_status out nocopy varchar2 ) is
4017
4018
4019 BEGIN
4020
4021
4022 -- Initialize Variables
4023
4024 x_return_status := FND_API.G_RET_STS_SUCCESS;
4025 x_err_code := 1;
4026
4027
4028 -- bugfix 2678167 : Modified the logic to improve performance. We will directly update the status
4029 -- instead of looping thru one-by-one.
4030
4031 -- Update the temp table status_type to 99 if there are any pending txns for that LBJ record.
4032
4033 -- bugfix 3080643. added check for WLT interface in WSJI and WRJI, and Move Txn interface in WLMTI
4034 -- replace previous union query with check exists
4035
4036 update wip_dj_close_temp wt
4037 set status_type = 99
4038 where wt.group_id = p_group_id
4039 and wt.organization_id = p_organization_id
4040 and wt.status_type <> 99
4041 and exists (
4042 select '1' from wip_entities we
4043 where we.wip_entity_id = wt.wip_entity_id
4044 and we.organization_id = wt.organization_id
4045 and we.entity_type = 5) -- check only LBJs
4046 and (exists (
4047 select 1
4048 from wsm_sm_starting_jobs sj,
4049 wsm_split_merge_transactions wmt
4050 --Bug 4744794: join based on wip_entity_id is replaced with
4051 -- join based on wip_entity_name so that index is used.
4052 --where sj.wip_entity_id = wt.wip_entity_id
4053 -- Modified SQL back to wip_entity_id for bug 9433681. We cannot use job name since it's updated during completion.
4054 where sj.wip_entity_id = wt.wip_entity_id
4055 and sj.organization_id = wt.organization_id
4056 and sj.transaction_id = wmt.transaction_id
4057 and (wmt.status <> 4 or nvl(wmt.costed,1) <> 4))
4058 or exists (
4059 select 1
4060 from wsm_sm_resulting_jobs rj,
4061 wsm_split_merge_transactions wmt
4062 --Bug 4744794: join based on wip_entity_id is replaced with
4063 -- join based on wip_entity_name so that index is used.
4064 --where rj.wip_entity_id = wt.wip_entity_id
4065 -- Modified SQL back to wip_entity_id for bug 9433681. We cannot use job name since it's updated during completion.
4066 where rj.wip_entity_id = wt.wip_entity_id
4067 and rj.organization_id = wt.organization_id
4068 and rj.transaction_id = wmt.transaction_id
4069 and (wmt.status <> 4 or nvl(wmt.costed,1) <> 4))
4070 or exists (
4071 select 1
4072 from wsm_starting_jobs_interface wsji,
4073 wsm_split_merge_txn_interface wsmti
4074 where wsji.wip_entity_id = wt.wip_entity_id
4075 and wsmti.header_id = wsji.header_id
4076 and wsmti.process_status in (WIP_CONSTANTS.PENDING, WIP_CONSTANTS.RUNNING))
4077 or exists (
4078 select 1
4079 from wsm_resulting_jobs_interface wrji,
4080 wsm_split_merge_txn_interface wsmti
4081 where wrji.wip_entity_name = wt.wip_entity_name
4082 and wsmti.header_id = wrji.header_id
4083 and wsmti.process_status in (WIP_CONSTANTS.PENDING, WIP_CONSTANTS.RUNNING))
4084 --Bug 4744794: Separate SQLs are used to select the records for the cases
4085 -- wip_entity_id is Null and wip_entity_id is NOT NULL
4086 or exists (
4087 select 1
4088 from wsm_lot_move_txn_interface wlmti
4089 --where (nvl(wlmti.wip_entity_id, -9999) = wt.wip_entity_id or
4090 -- nvl(wlmti.wip_entity_name, '@#$*') = wt.wip_entity_name)
4091 where wlmti.wip_entity_id = wt.wip_entity_id
4092 and wlmti.status in (WIP_CONSTANTS.PENDING, WIP_CONSTANTS.RUNNING))
4093 or exists (
4094 select 1
4095 from wsm_lot_move_txn_interface wlmti
4096 where wlmti.wip_entity_name = wt.wip_entity_name
4097 and wlmti.organization_id = wt.organization_id
4098 and wlmti.status in (WIP_CONSTANTS.PENDING, WIP_CONSTANTS.RUNNING)));
4099
4100 if sql%rowcount > 0 then
4101 x_err_code := 0; -- this is needed by WIP to figure out whether to end the request in warning or success.
4102 end if;
4103 if (l_debug = 'Y') then
4104 fnd_file.put_line(fnd_file.log, 'WSMPUTIL.validate_lbj_before_close: Updated LBJ records to ERROR.');
4105 end if;
4106
4107
4108 -- Update the WDJ status type to 15
4109 update wip_discrete_jobs
4110 set status_type = 15 -- Failed Close.
4111 where wip_entity_id in
4112 (select wt.wip_entity_id
4113 from wip_dj_close_temp wt, wip_entities we
4114 where wt.group_id = p_group_id
4115 and wt.organization_id = p_organization_id
4116 and wt.status_type = 99
4117 and wt.wip_entity_id = we.wip_entity_id
4118 and we.entity_type = 5); -- we will touch only the LBJs.
4119
4120 if (sql%rowcount > 0) then
4121 fnd_file.put_line(fnd_file.log, 'Following jobs failed the close process because of unprocessed/uncosted WIP lot transactions or Move transactions:');
4122
4123 -- Print the entities which FAILED CLOSE
4124 for rec in (select tm.wip_entity_id, we.wip_entity_name
4125 from wip_dj_close_temp tm, wip_entities we
4126 where tm.wip_entity_id = we.wip_entity_id
4127 and tm.organization_id = we.organization_id
4128 and tm.group_id = p_group_id
4129 and tm.organization_id = p_organization_id
4130 and tm.status_type = 99
4131 and we.entity_type = 5)
4132 loop
4133 fnd_file.put_line(fnd_file.log, rec.wip_entity_name);
4134 end loop;
4135
4136 end if;
4137
4138 -- Clean up the temp table.
4139 delete from wip_dj_close_temp
4140 where group_id = p_group_id
4141 and organization_id = p_organization_id
4142 and status_type = 99;
4143
4144 if (l_debug = 'Y') then
4145 fnd_file.put_line(fnd_file.log, 'WSMPUTIL.validate_lbj_before_close: Successfully cleaned up temp table by deleting '||sql%rowcount|| ' records.');
4146 end if;
4147
4148 EXCEPTION
4149 when others then
4150 fnd_file.put_line(fnd_file.log, 'WSMPUTIL.validate_lbj_before_close: Unexpected Error: '||sqlerrm);
4151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4152
4153 END validate_lbj_before_close;
4154
4155
4156
4157
4158 -- The following procedure supplies parameters to the inventory to be used
4159 -- for the record group for the lov that displays lists all lots containing
4160 -- all the components required at the first operation in the bom of the
4161 -- chosen assembly that is being replenished.
4162
4163 PROCEDURE get_Kanban_rec_grp_info (p_organization_id IN number,
4164 p_kanban_assembly_id IN number,
4165 p_rtg_rev_date IN date,
4166 p_bom_seq_id OUT NOCOPY number,
4167 p_start_seq_num OUT NOCOPY number,
4168 p_error_code OUT NOCOPY number,
4169 p_error_msg OUT NOCOPY varchar2) IS
4170
4171 l_stmt_num number;
4172 l_routing_seq_id number;
4173 l_common_routing_sequence_id number;
4174 l_start_op_seq_id number;
4175 l_error_code number := 0;
4176 l_err_msg varchar2(2000) := '';
4177
4178 begin
4179
4180 l_stmt_num := 10;
4181
4182 begin
4183 SELECT bom.common_bill_sequence_id
4184 INTO p_bom_seq_id
4185 FROM bom_bill_of_materials bom
4186 WHERE bom.alternate_bom_designator is null
4187 AND bom.assembly_item_id = p_kanban_assembly_id
4188 AND bom.organization_id = p_organization_id;
4189 exception
4190 when no_data_found then
4191 p_bom_seq_id := null;
4192 end;
4193
4194 l_stmt_num := 20;
4195
4196 select bor.routing_sequence_id
4197 into l_routing_seq_id
4198 from bom_routing_alternates_v bor
4199 where bor.organization_id = p_organization_id
4200 and bor.assembly_item_id = p_kanban_assembly_id
4201 and bor.alternate_routing_designator is null
4202 and bor.routing_type = 1
4203 and bor.cfm_routing_flag = 3;
4204
4205
4206 l_stmt_num := 30;
4207
4208 WSMPUTIL.find_common_routing(
4209 p_routing_sequence_id => l_routing_seq_id,
4210 p_common_routing_sequence_id => l_common_routing_sequence_id,
4211 x_err_code => l_error_code,
4212 x_err_msg => l_err_msg);
4213
4214 if l_error_code <> 0 then
4215 p_error_code := l_error_code;
4216 p_error_msg := l_err_msg;
4217 return;
4218 end if;
4219
4220
4221 l_stmt_num := 40;
4222
4223 WSMPUTIL.find_routing_start ( l_common_routing_sequence_id,
4224 p_rtg_rev_date,
4225 l_start_op_seq_id,
4226 l_error_code,
4227 l_err_msg );
4228
4229 if l_error_code <> 0 then
4230 p_error_code := l_error_code;
4231 p_error_msg := l_err_msg;
4232 return;
4233 end if;
4234
4235
4236 l_stmt_num := 50;
4237
4238 select bos.operation_seq_num
4239 into p_start_seq_num
4240 from bom_operation_sequences bos
4241 where bos.operation_sequence_id = l_start_op_seq_id;
4242
4243 exception
4244
4245 when others then
4246 p_error_code := SQLCODE;
4247 p_error_msg := substr('wsmputil.get_Kanban_rec_grp_info: stmt no: '||l_stmt_num||' '||SQLERRM,1, 2000);
4248
4249
4250 end get_Kanban_rec_grp_info;
4251
4252
4253
4254
4255 -- the following procedure finds the maximum number of assemblies that can be created
4256 -- out of a given component of a given bill
4257
4258 PROCEDURE get_max_kanban_asmbly_qty (p_bill_seq_id IN number,
4259 p_component_item_id IN number,
4260 p_bom_revision_date IN date,
4261 p_start_seq_num IN number,
4262 p_available_qty IN number,
4263 p_max_asmbly_qty OUT NOCOPY number,
4264 p_error_code OUT NOCOPY number,
4265 p_error_msg OUT NOCOPY varchar2) IS
4266
4267 l_component_quantity number;
4268 l_component_yield_factor number;
4269
4270 begin
4271
4272 select component_quantity, component_yield_factor
4273 into l_component_quantity, l_component_yield_factor
4274 from bom_inventory_components
4275 where bill_sequence_id = p_bill_seq_id
4276 and component_item_id = p_component_item_id
4277 and (operation_seq_num = p_start_seq_num or operation_seq_num = 1)
4278 and p_bom_revision_date between effectivity_date and nvl(disable_date, p_bom_revision_date + 1);
4279
4280 p_max_asmbly_qty := round(((p_available_qty * l_component_yield_factor)/l_component_quantity), 6);
4281
4282 exception
4283
4284 when others then
4285 p_error_code := SQLCODE;
4286 p_error_msg := substr('wsmputil.get_max_kanban_asmbly_qty: '||' '||SQLERRM,1, 2000);
4287
4288 end get_max_kanban_asmbly_qty;
4289
4290 /* bug fix:7387499
4291 ***************************************************************************
4292 created this function for bug fix 5529692 to call this in
4293 BOM_OPERATION_NETWORKS_V to fetch standard operation id of
4294 operation that is relatively effective at a particular operation
4295 sequence number and inturn fetch its op code
4296 **************************************************************************/
4297
4298 FUNCTION get_eff_stdop_id(p_stdop_id NUMBER,
4299 p_opseq_id NUMBER)
4300 return NUMBER
4301 is
4302 l_opseq_num number;
4303 l_eff_date date;
4304 l_routseq_id number;
4305 l_eff_stdop_id number;
4306 l_operation_type number;
4307 begin
4308
4309 select operation_seq_num,routing_sequence_id,operation_type
4310 into l_opseq_num,l_routseq_id,l_operation_type
4311 from bom_operation_sequences
4312 where standard_operation_id = p_stdop_id
4313 and operation_sequence_id = p_opseq_id;
4314
4315 l_eff_date := WSMPUTIL.EFFECTIVE_DATE(l_opseq_num,l_routseq_id,l_operation_type);
4316
4317 select standard_operation_id into l_eff_stdop_id
4318 from bom_operation_sequences
4319 where effectivity_date = l_eff_date
4320 and operation_seq_num = l_opseq_num
4321 and routing_sequence_id = l_routseq_id
4322 and operation_type = l_operation_type;
4323
4324 return l_eff_stdop_id;
4325
4326 end get_eff_stdop_id;
4327
4328 /****************************************************************************************************
4329 Created this function for bug fix 5529692 to call this in
4330 BOM_OPERATION_NETWORKS_V to fetch standard department id of
4331 operation that is relatively effective at a particular operation
4332 sequence number and inturn fetch its department code
4333 ****************************************************************************************************/
4334
4335 FUNCTION get_eff_dept_id(p_dept_id number,
4336 p_opseq_id number)
4337 return number
4338 is
4339 l_opseq_num number;
4340 l_eff_date date;
4341 l_routseq_id number;
4342 l_eff_dept_id number;
4343 l_operation_type number;
4344 begin
4345
4346 select operation_seq_num,routing_sequence_id,operation_type
4347 into l_opseq_num,l_routseq_id,l_operation_type
4348 from bom_operation_sequences
4349 where department_id = p_dept_id
4350 and operation_SEQUENCE_id = p_opseq_id;
4351
4352 l_eff_date := WSMPUTIL.EFFECTIVE_DATE(l_opseq_num,l_routseq_id,l_operation_type);
4353
4354 select department_id into l_eff_dept_id
4355 from bom_operation_sequences
4356 where effectivity_date = l_eff_date
4357 and operation_seq_num = l_opseq_num
4358 and routing_sequence_id = l_routseq_id
4359 and operation_type =l_operation_type;
4360
4361 return l_eff_dept_id;
4362
4363 end get_eff_dept_id;
4364
4365
4366 /************************************************************************************************************************
4367 created this function to pick up the effectivity_date of operation that is
4368 relatively effective at particular operation sequence number of the routing,
4369 so that it can be shown in the lov attached to From
4370 and To fields in Network Routings form.
4371 ******************************************************************************************************************/
4372
4373 FUNCTION EFFECTIVE_DATE(p_oper_seq_num number,
4374 p_routing_seq_id number,
4375 p_operation_type number)
4376 RETURN DATE
4377 IS
4378 l_eff_date date;
4379 l_count number := 0;
4380 l_efe_sysdate number := 1;
4381 l_max_date date := NULL;
4382 l_min_date date := NULL;
4383 begin
4384 select count(*) into l_count
4385 from bom_operation_sequences s
4386 where s.routing_sequence_id = p_routing_seq_id
4387 and s.operation_seq_num = p_oper_seq_num
4388 and s.operation_type = p_operation_type
4389 group by s.operation_seq_num;
4390
4391 if (l_count = 1) then
4392
4393 select s.effectivity_date into l_eff_date
4394 from bom_operation_sequences s
4395 where s.routing_sequence_id = p_routing_seq_id
4396 and s.operation_seq_num = p_oper_seq_num
4397 and s.operation_type = p_operation_type;
4398
4399
4400 return l_eff_date;
4401
4402 else
4403
4404 begin
4405 select max(s.effectivity_date) into l_eff_date from bom_operation_sequences s
4406 where s.routing_sequence_id = p_routing_seq_id
4407 and s.operation_seq_num = p_oper_seq_num
4408 and sysdate <= nvl(s.disable_date, sysdate+1)
4409 and s.effectivity_date <= sysdate
4410 and s.operation_type = p_operation_type
4411 group by s.operation_seq_num ;
4412
4413
4414 exception
4415 WHEN NO_DATA_FOUND THEN
4416 l_efe_sysdate := 0;
4417 end;
4418
4419 if l_efe_sysdate = 1 then
4420
4421 return l_eff_date;
4422
4423 else
4424 begin
4425 select max(s.effectivity_date) into l_max_date
4426 from bom_operation_sequences s
4427 where s.routing_sequence_id = p_routing_seq_id
4428 and s.operation_seq_num = p_oper_seq_num
4429 and s.effectivity_date < sysdate
4430 and s.operation_type = p_operation_type
4431 group by s.operation_seq_num ;
4432
4433 exception
4434 WHEN NO_DATA_FOUND THEN
4435 null;
4436 end;
4437
4438 if (l_max_date IS NOT NULL) then
4439 return l_max_date;
4440
4441 else
4442
4443 select min(s.effectivity_date) into l_min_date
4444 from bom_operation_sequences s
4445 where s.routing_sequence_id = p_routing_seq_id
4446 and s.operation_seq_num = p_oper_seq_num
4447 and s.effectivity_date > sysdate
4448 and s.operation_type = p_operation_type
4449 group by s.operation_seq_num;
4450
4451
4452 return l_min_date;
4453
4454 end if; --l_max_date not null
4455
4456 end if; --l_efe_sysdate = 0
4457
4458 end if; -- l_count= 1
4459
4460 END EFFECTIVE_DATE;
4461
4462 --********************************************************************************************
4463 --bug fix:7387499
4464 --*******************************************************************************************
4465
4466
4467 --***********************************************************************************************
4468 -- ==============================================================================================
4469 -- PROCEDURE return_att_quantity
4470 -- ==============================================================================================
4471 --***********************************************************************************************
4472
4473 PROCEDURE return_att_quantity(p_org_id IN number,
4474 p_item_id IN number,
4475 p_rev IN varchar2,
4476 p_lot_no IN varchar2,
4477 p_subinv IN varchar2,
4478 p_locator_id IN number,
4479 p_qoh OUT NOCOPY number,
4480 p_atr OUT NOCOPY number,
4481 p_att OUT NOCOPY number,
4482 p_err_code OUT NOCOPY number,
4483 p_err_msg OUT NOCOPY varchar2 ) IS
4484
4485 lv_return_status varchar2(20);
4486 lv_msg_count number := 0;
4487 lv_msg_data varchar2(4000);
4488 lv_tree_id number;
4489 lv_qoh number;
4490 lv_rqoh number;
4491 lv_qr number;
4492 lv_qs number;
4493 lv_att number;
4494 lv_atr number;
4495
4496 BEGIN
4497 --Bug 4567588:Tree mode is changed from reservation mode (3) to transaction
4498 --mode (2)
4499 inv_quantity_tree_pvt.create_tree(
4500 P_API_VERSION_NUMBER => 1.0
4501 , P_INIT_MSG_LST => 'T'
4502 , X_RETURN_STATUS => lv_return_status
4503 , X_MSG_COUNT => lv_msg_count
4504 , X_MSG_DATA => lv_msg_data
4505 , P_ORGANIZATION_ID => p_org_id
4506 , P_INVENTORY_ITEM_ID => p_item_id
4507 , P_TREE_MODE => 2 --3
4508 , P_IS_REVISION_CONTROL => (p_rev is not null)
4509 , P_IS_LOT_CONTROL => TRUE
4510 , P_IS_SERIAL_CONTROL => FALSE
4511 , P_ASSET_SUB_ONLY => FALSE
4512 , P_INCLUDE_SUGGESTION => FALSE
4513 , P_DEMAND_SOURCE_TYPE_ID => 13
4514 , P_DEMAND_SOURCE_HEADER_ID => -9999
4515 , P_DEMAND_SOURCE_LINE_ID => NULL
4516 , P_DEMAND_SOURCE_NAME => NULL
4517 , P_LOT_EXPIRATION_DATE => null
4518 , X_TREE_ID => lv_tree_id);
4519
4520 if( lv_return_status <> 'S' ) then
4521 fnd_message.set_name('INV', 'INV_ERR_CREATETREE');
4522 p_err_msg := fnd_message.get;
4523 p_err_code := -1;
4524 return;
4525 end if;
4526
4527 inv_quantity_tree_pvt.QUERY_TREE(
4528 P_API_VERSION_NUMBER => 1.0
4529 , P_INIT_MSG_LST => 'T'
4530 , X_RETURN_STATUS => lv_return_status
4531 , X_MSG_COUNT => lv_msg_count
4532 , X_MSG_DATA => lv_msg_data
4533 , P_TREE_ID => lv_tree_id
4534 , P_REVISION => p_rev
4535 , P_LOT_NUMBER => p_lot_no
4536 , P_SUBINVENTORY_CODE => p_subinv
4537 , P_LOCATOR_ID => p_locator_id
4538 , X_QOH => lv_qoh
4539 , X_RQOH => lv_rqoh
4540 , X_QR => lv_qr
4541 , X_QS => lv_qs
4542 , X_ATT => lv_att
4543 , X_ATR => lv_atr
4544 );
4545
4546
4547 if( lv_return_status <> 'S' ) then
4548 fnd_message.set_name('INV', 'INV-CANNOT QUERY TREE');
4549 p_err_msg := fnd_message.get;
4550 p_err_code := -1;
4551 return;
4552 end if;
4553
4554 p_qoh := lv_qoh;
4555 p_att := lv_att;
4556 --Bug 4567588
4557 p_atr := lv_att; --lv_atr;
4558
4559 inv_quantity_tree_pvt.free_all(
4560 p_api_version_number => 1.0
4561 , p_init_msg_lst => 'T'
4562 , x_return_status => lv_return_status
4563 , x_msg_count => lv_msg_count
4564 , x_msg_data => lv_msg_data
4565 );
4566
4567 IF(p_qoh = 0) THEN
4568 FND_MESSAGE.set_name('WSM','WSM_ZERO_ON_HAND');
4569 p_err_msg := fnd_message.get;
4570 p_err_code := -1;
4571 return;
4572 END IF;
4573
4574 IF p_atr = 0 THEN
4575 FND_MESSAGE.set_name('WSM','WSM_LOT_FULL_RESERVED');
4576 p_err_msg := fnd_message.get;
4577 p_err_code := -1;
4578 return;
4579 END IF;
4580
4581 EXCEPTION
4582 WHEN OTHERS THEN
4583 p_err_code := SQLCODE;
4584 p_err_msg := substr('WSMPUTIL.return_att_quantity :' ||sqlerrm, 1,2000);
4585 FND_FILE.PUT_LINE(FND_FILE.LOG, p_err_msg);
4586 return;
4587 END return_att_quantity;
4588
4589
4590
4591
4592 -- OSP FP I addition begin
4593 -- this function checks to see if the operation has an OSP resource
4594 -- attached to it
4595
4596 function check_osp_operation ( p_wip_entity_id IN NUMBER,
4597 p_operation_seq_num IN OUT NOCOPY NUMBER,
4598 p_organization_id IN NUMBER )
4599
4600
4601 return boolean is
4602
4603 l_op_seq_num number;
4604
4605 begin
4606
4607 l_op_seq_num := -1 ;
4608
4609 select unique wor.operation_seq_num
4610 into l_op_seq_num
4611 from wip_operation_resources wor
4612 where wor.organization_id = p_organization_id
4613 and wor.wip_entity_id = p_wip_entity_id
4614 and wor.operation_seq_num = nvl(p_operation_seq_num,wor.operation_seq_num)
4615 and wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
4616 WIP_CONSTANTS.PO_MOVE);
4617 p_operation_seq_num := l_op_seq_num ;
4618 return true;
4619
4620 exception
4621
4622 when no_data_found then
4623
4624 return false;
4625
4626 when too_many_rows then
4627
4628 return true;
4629
4630 -- when others then has been deliberately not written so that
4631 -- exception is thrown in the calling program. this means that
4632 -- x_error_code and x_error_msg out variables need not be
4633 -- defined here and passed back.
4634
4635 end check_osp_operation ;
4636
4637
4638 ------------------------------------------------------------
4639 -- FUNCTIONS THAT CHECK TXN and TXN INTERFACE TABLES
4640 ------------------------------------------------------------
4641
4642 /***************************************************************************************/
4643
4644 FUNCTION CHECK_WLMTI (
4645 p_wip_entity_id IN NUMBER,
4646 p_wip_entity_name IN VARCHAR2,
4647 p_header_id IN NUMBER,
4648 p_transaction_date IN DATE,
4649 x_err_code OUT NOCOPY NUMBER,
4650 x_err_msg OUT NOCOPY VARCHAR2,
4651 p_organization_id IN NUMBER
4652 )
4653 RETURN NUMBER
4654 IS
4655 l_rowcount NUMBER := 0;
4656 l_stmt_num NUMBER := 0;
4657
4658 BEGIN
4659 x_err_code := 0;
4660 x_err_msg := '';
4661 l_stmt_num := 10;
4662
4663 /***************************************************************
4664 -- Fixed bug #3453139: Stubbed out this procedure, since txns should not depend on interface records.
4665
4666 l_rowcount := 0;
4667
4668 IF (p_wip_entity_id IS NOT NULL) THEN
4669
4670 l_stmt_num := 20;
4671
4672 SELECT 1
4673 INTO l_rowcount
4674 From dual
4675 where exists (select 'Unprocessed WLMTI Record exists'
4676 FROM WSM_LOT_MOVE_TXN_INTERFACE WLMTI
4677 WHERE WLMTI.entity_type = 5
4678 AND WLMTI.wip_entity_id = p_wip_entity_id
4679 AND WLMTI.status IN (WIP_CONSTANTS.PENDING,
4680 WIP_CONSTANTS.RUNNING,
4681 WIP_CONSTANTS.ERROR)
4682 AND WLMTI.transaction_date <= p_transaction_date
4683 AND WLMTI.header_id <> p_header_id);
4684 -- Use of header_id here in WLMTI is useful to support BULK MOVE Txns
4685 -- Otherwise, I don't see any use for this. -- BBK.
4686
4687 IF (l_rowcount > 0 ) THEN
4688 RETURN l_rowcount;
4689 END IF;
4690
4691
4692 ELSIF (p_wip_entity_name IS NOT NULL) THEN
4693
4694 l_stmt_num := 30;
4695
4696 SELECT 1
4697 INTO l_rowcount
4698 From dual
4699 where exists (select 'Unprocessed WLMTI Record exists'
4700 FROM WSM_LOT_MOVE_TXN_INTERFACE WLMTI
4701 WHERE WLMTI.entity_type = 5
4702 AND WLMTI.wip_entity_name = p_wip_entity_name
4703 AND WLMTI.organization_id = decode(p_organization_id, 0, WLMTI.organization_id, p_organization_id)
4704 AND WLMTI.status IN (WIP_CONSTANTS. PENDING,
4705 WIP_CONSTANTS.RUNNING,
4706 WIP_CONSTANTS.ERROR)
4707 AND WLMTI.transaction_date <= p_transaction_date
4708 AND WLMTI.header_id <> p_header_id );
4709
4710
4711 IF (l_rowcount > 0 ) THEN
4712 NULL;
4713 RETURN l_rowcount;
4714 END IF;
4715
4716 END IF;
4717 ***************************************************************/
4718
4719 x_err_code := 0;
4720 x_err_msg := 'WSMPUTIL.CHECK_WLMTI:Success';
4721
4722 If (l_debug = 'Y') Then
4723 FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPUTIL.check_wlmti: Returned Success');
4724 End If;
4725
4726 RETURN l_rowcount;
4727
4728 EXCEPTION
4729 WHEN NO_DATA_FOUND THEN -- NO UNPROCESSED TXNS EXIST
4730 l_rowcount := 0;
4731 RETURN l_rowcount;
4732
4733 WHEN OTHERS THEN
4734 x_err_code := SQLCODE;
4735 x_err_msg := 'WSMPUTIL.check_wlmti(stmt_num='||l_stmt_num||' :'||SUBSTR(SQLERRM,1,1000);
4736 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
4737
4738 END CHECK_WLMTI;
4739
4740 --
4741 -- Overloaded function, org_id missing
4742 --
4743 FUNCTION CHECK_WLMTI (
4744 p_wip_entity_id IN NUMBER,
4745 p_wip_entity_name IN VARCHAR2,
4746 p_header_id IN NUMBER,
4747 p_transaction_date IN DATE,
4748 x_err_code OUT NOCOPY NUMBER,
4749 x_err_msg OUT NOCOPY VARCHAR2
4750 )
4751 RETURN NUMBER AS
4752
4753 l_organization_id NUMBER := 0;
4754 l_return_value NUMBER := 0;
4755
4756 BEGIN
4757
4758 x_err_code := 0;
4759 x_err_msg := '';
4760
4761 /***************************************************************
4762 -- Fixed bug #3453139: Stubbed out this procedure, since txns should not depend on interface records.
4763
4764 l_return_value := check_wlmti( p_wip_entity_id => p_wip_entity_id
4765 , p_wip_entity_name => p_wip_entity_name
4766 , p_header_id => p_header_id
4767 , p_transaction_date => p_transaction_date
4768 , x_err_code => x_err_code
4769 , x_err_msg => x_err_msg
4770 , p_organization_id => l_organization_id
4771 );
4772 ***************************************************************/
4773
4774 return l_return_value;
4775
4776 END CHECK_WLMTI;
4777
4778
4779 /***************************************************************************************/
4780
4781 -- Moved this procedure from WSMPLOAD to here
4782 FUNCTION CHECK_WMTI
4783 (
4784 p_wip_entity_id IN NUMBER,
4785 p_wip_entity_name IN VARCHAR2,
4786 p_transaction_date IN DATE,
4787 x_err_code OUT NOCOPY NUMBER,
4788 x_err_msg OUT NOCOPY VARCHAR2,
4789 p_organization_id IN NUMBER
4790 )
4791 RETURN NUMBER
4792 IS
4793 l_stmt_num NUMBER := 0;
4794 l_rowcount NUMBER := 0;
4795
4796 BEGIN
4797 x_err_code := 0;
4798 x_err_msg := '';
4799 l_stmt_num := 10;
4800
4801 /***************************************************************
4802 -- Fixed bug #3453139: Stubbed out this procedure, since only online moves are now supported
4803
4804 l_rowcount := 0;
4805
4806 IF (p_wip_entity_id IS NOT NULL) THEN
4807
4808 l_stmt_num := 20;
4809
4810 SELECT 1
4811 INTO l_rowcount
4812 From dual
4813 where exists (select 'Unprocessed WMTI Record exists'
4814 FROM WIP_MOVE_TXN_INTERFACE WMTI
4815 WHERE WMTI.entity_type = 5
4816 AND WMTI.wip_entity_id = p_wip_entity_id
4817 AND WMTI.process_status IN (WIP_CONSTANTS.PENDING,
4818 WIP_CONSTANTS.RUNNING,
4819 WIP_CONSTANTS.ERROR)
4820 AND WMTI.transaction_date < nvl(p_transaction_date, SYSDATE)
4821 ); -- So that it doesn't pick up itself
4822
4823
4824 IF (l_rowcount > 0 ) THEN
4825 x_err_msg := 'WSMPUTIL.check_wmti('||l_stmt_num||') : Returning error';
4826 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
4827 RETURN l_rowcount;
4828 END IF;
4829
4830 ELSIF (p_wip_entity_name IS NOT NULL) THEN
4831
4832 l_stmt_num := 60;
4833
4834 SELECT 1
4835 INTO l_rowcount
4836 From dual
4837 where exists (select 'Unprocessed WMTI Record exists'
4838 FROM WIP_MOVE_TXN_INTERFACE WMTI
4839 WHERE WMTI.entity_type = 5
4840 AND WMTI.wip_entity_name = p_wip_entity_name
4841 AND WMTI.organization_id = decode(p_organization_id, 0, WMTI.organization_id, p_organization_id)
4842 AND WMTI.process_status IN (WIP_CONSTANTS.PENDING,
4843 WIP_CONSTANTS.RUNNING,
4844 WIP_CONSTANTS.ERROR)
4845 AND WMTI.transaction_date < nvl(p_transaction_date, SYSDATE)
4846 );
4847
4848
4849 IF (l_rowcount > 0 ) THEN
4850 x_err_msg := 'WSMPUTIL.check_wmti('||l_stmt_num||') : Returning error';
4851 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
4852 RETURN l_rowcount;
4853 END IF;
4854
4855 END IF;
4856
4857 ***************************************************************/
4858
4859 x_err_code := 0;
4860 x_err_msg := 'WSMPUTIL.CHECK_WMTI:Returned Success';
4861 IF (l_debug = 'Y') THEN
4862 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
4863 END IF;
4864 RETURN 0;
4865
4866 EXCEPTION
4867 WHEN NO_DATA_FOUND THEN
4868 l_rowcount := 0;
4869 return l_rowcount;
4870
4871 WHEN OTHERS THEN
4872 x_err_code := SQLCODE;
4873 x_err_msg := 'WSMPUTIL.check_wmti('||l_stmt_num||') :'||SUBSTR(SQLERRM,1,1000);
4874 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
4875 return 1; -- return a nonzero value.
4876
4877 END CHECK_WMTI;
4878
4879 --
4880 -- Overloaded function, org_id missing
4881 --
4882 FUNCTION CHECK_WMTI
4883 (
4884 p_wip_entity_id IN NUMBER,
4885 p_wip_entity_name IN VARCHAR2,
4886 p_transaction_date IN DATE,
4887 x_err_code OUT NOCOPY NUMBER,
4888 x_err_msg OUT NOCOPY VARCHAR2
4889 )
4890 RETURN NUMBER
4891 AS
4892
4893 l_organization_id NUMBER := 0;
4894 l_return_value NUMBER := 0;
4895
4896 BEGIN
4897
4898 x_err_code := 0;
4899 x_err_msg := NULL;
4900
4901 /***************************************************************
4902 -- Fixed bug #3453139: Stubbed out this procedure, since only online moves are now supported
4903
4904 l_return_value := check_wmti(p_wip_entity_id => p_wip_entity_id
4905 , p_wip_entity_name => p_wip_entity_name
4906 , p_transaction_date => p_transaction_date
4907 , x_err_code => x_err_code
4908 , x_err_msg => x_err_msg
4909 , p_organization_id => l_organization_id
4910 );
4911 ***************************************************************/
4912
4913 return l_return_value;
4914
4915
4916 END CHECK_WMTI;
4917
4918
4919 /***************************************************************************************/
4920
4921 -- Moved this procedure from WSMPLOAD to here
4922 FUNCTION CHECK_WSMT
4923 (
4924 p_wip_entity_id IN NUMBER,
4925 p_wip_entity_name IN VARCHAR2,
4926 p_transaction_id IN NUMBER,
4927 p_transaction_date IN DATE,
4928 x_err_code OUT NOCOPY NUMBER,
4929 x_err_msg OUT NOCOPY VARCHAR2,
4930 p_organization_id IN NUMBER
4931 )
4932 RETURN NUMBER
4933 IS
4934
4935 l_sj_rowcount NUMBER := 0;
4936 l_rj_rowcount NUMBER := 0;
4937 l_stmt_num NUMBER := 0;
4938
4939 BEGIN
4940
4941 x_err_code := 0;
4942 x_err_msg := '';
4943 l_stmt_num := 10;
4944
4945 /***************************************************************
4946 -- Fixed bug #3453139: Stubbed out this procedure, since it should not be called from anywhere.
4947
4948 if l_debug = 'Y' then
4949 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_wip_entity_id ='||p_wip_entity_id);
4950 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_transaction_id ='||p_transaction_id);
4951 end if;
4952
4953
4954 l_sj_rowcount := 0;
4955 l_rj_rowcount := 0;
4956
4957 IF (p_wip_entity_id IS NOT NULL) THEN
4958
4959 l_stmt_num := 20;
4960
4961 IF(p_transaction_id IS NULL) THEN
4962 l_stmt_num := 30;
4963
4964 Begin
4965
4966 SELECT 1
4967 INTO l_sj_rowcount
4968 FROM dual
4969 WHERE exists (select 'Unprocessed WSMT Record exists'
4970 FROM WSM_SM_STARTING_JOBS WSSJ,
4971 WSM_SPLIT_MERGE_TRANSACTIONS WSMT
4972 WHERE
4973 WSSJ.wip_entity_id = p_wip_entity_id
4974 AND WSMT.transaction_id = WSSJ.transaction_id
4975 AND WSMT.status IN (WIP_CONSTANTS.PENDING,
4976 WIP_CONSTANTS.RUNNING,
4977 WIP_CONSTANTS.ERROR)
4978 AND WSMT.transaction_date <= nvl(p_transaction_date,SYSDATE)
4979 );
4980
4981 EXCEPTION
4982 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
4983 NULL;
4984 End;
4985
4986 ELSE
4987
4988 l_stmt_num := 50;
4989
4990 Begin
4991
4992 SELECT 1
4993 INTO l_sj_rowcount
4994 FROM dual
4995 WHERE exists (select 'Unprocessed WSSJ/WSMT Record exists'
4996 FROM WSM_SM_STARTING_JOBS WSSJ,
4997 WSM_SPLIT_MERGE_TRANSACTIONS WSMT
4998 WHERE
4999 WSSJ.wip_entity_id = p_wip_entity_id
5000 AND WSMT.transaction_id = WSSJ.transaction_id
5001 AND WSMT.status IN (WIP_CONSTANTS.PENDING,
5002 WIP_CONSTANTS.RUNNING,
5003 WIP_CONSTANTS.ERROR)
5004 AND WSMT.transaction_date <= nvl(p_transaction_date,SYSDATE)
5005 AND WSMT.transaction_id <> p_transaction_id
5006 );
5007
5008 EXCEPTION
5009 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5010 NULL;
5011 End;
5012
5013 END IF;
5014
5015
5016 IF (l_sj_rowcount > 0 ) THEN
5017 x_err_msg := 'WSMPUTIL.check_wsmt('||l_stmt_num||') : Returning error';
5018 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5019 RETURN l_sj_rowcount;
5020 END IF;
5021
5022 l_stmt_num := 90;
5023
5024 IF (p_transaction_id IS NULL) THEN
5025 l_stmt_num := 100;
5026
5027 Begin
5028
5029 SELECT 1
5030 INTO l_rj_rowcount
5031 FROM dual
5032 WHERE exists (select 'Unprocessed WSRJ/WSMT Record exists'
5033 FROM WSM_SM_RESULTING_JOBS WSRJ,
5034 WSM_SPLIT_MERGE_TRANSACTIONS WSMT
5035 WHERE
5036 WSRJ.wip_entity_id = p_wip_entity_id
5037 AND WSMT.transaction_id = WSRJ.transaction_id
5038 AND WSMT.status IN (WIP_CONSTANTS.PENDING,
5039 WIP_CONSTANTS.RUNNING,
5040 WIP_CONSTANTS.ERROR)
5041 AND WSMT.transaction_date <= nvl(p_transaction_date,SYSDATE)
5042 );
5043
5044 EXCEPTION
5045 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5046 NULL;
5047 End;
5048 ELSE
5049
5050 l_stmt_num := 120;
5051
5052 Begin
5053
5054 SELECT 1
5055 INTO l_rj_rowcount
5056 FROM dual
5057 WHERE exists (select 'Unprocessed WSRJ/WSMT Record exists'
5058 FROM WSM_SM_RESULTING_JOBS WSRJ,
5059 WSM_SPLIT_MERGE_TRANSACTIONS WSMT
5060 WHERE
5061 WSRJ.wip_entity_id = p_wip_entity_id
5062 AND WSMT.transaction_id = WSRJ.transaction_id
5063 AND WSMT.status IN (WIP_CONSTANTS.PENDING,
5064 WIP_CONSTANTS.RUNNING,
5065 WIP_CONSTANTS.ERROR)
5066 AND WSMT.transaction_date <= nvl(p_transaction_date,SYSDATE)
5067 AND WSMT.transaction_id <> p_transaction_id
5068 );
5069 EXCEPTION
5070 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5071 NULL;
5072 End;
5073
5074 END IF;
5075
5076 IF (l_rj_rowcount > 0 ) THEN
5077 x_err_msg := 'WSMPUTIL.check_wsmt('||l_stmt_num||') : Returning error';
5078 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5079 RETURN l_rj_rowcount;
5080 END IF;
5081
5082 l_stmt_num := 160;
5083
5084 ELSIF (p_wip_entity_name IS NOT NULL) THEN
5085
5086 l_sj_rowcount := 0;
5087 l_rj_rowcount := 0;
5088
5089 l_stmt_num := 180;
5090
5091 IF(p_transaction_id IS NULL) THEN
5092 l_stmt_num := 190;
5093
5094 Begin
5095
5096 SELECT 1
5097 INTO l_rj_rowcount
5098 FROM dual
5099 WHERE exists (select 'Unprocessed WSRJ/WSMT Record exists'
5100 FROM WSM_SM_RESULTING_JOBS WSRJ,
5101 WSM_SPLIT_MERGE_TRANSACTIONS WSMT
5102 WHERE
5103 WSRJ.wip_entity_name = p_wip_entity_name
5104 AND WSMT.organization_id = decode(p_organization_id,
5105 0, WSMT.organization_id, p_organization_id)
5106 AND WSMT.transaction_id = WSRJ.transaction_id
5107 AND WSMT.status IN (WIP_CONSTANTS.PENDING,
5108 WIP_CONSTANTS.RUNNING,
5109 WIP_CONSTANTS.ERROR)
5110 AND WSMT.transaction_date <= nvl(p_transaction_date, SYSDATE)
5111 );
5112
5113 EXCEPTION
5114 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5115 NULL;
5116 End;
5117
5118 ELSE
5119
5120 l_stmt_num := 210;
5121
5122 Begin
5123
5124 SELECT 1
5125 INTO l_rj_rowcount
5126 FROM dual
5127 WHERE exists (select 'Unprocessed WSRJ/WSMT Record exists'
5128 FROM WSM_SM_RESULTING_JOBS WSRJ,
5129 WSM_SPLIT_MERGE_TRANSACTIONS WSMT
5130 WHERE
5131 WSRJ.wip_entity_name = p_wip_entity_name
5132 AND WSMT.organization_id = decode(p_organization_id,
5133 0, WSMT.organization_id, p_organization_id)
5134 AND WSMT.transaction_id = WSRJ.transaction_id
5135 AND WSMT.status IN (WIP_CONSTANTS.PENDING,
5136 WIP_CONSTANTS.RUNNING,
5137 WIP_CONSTANTS.ERROR)
5138 AND WSMT.transaction_date <= nvl(p_transaction_date, SYSDATE)
5139 AND WSMT.transaction_id <> p_transaction_id
5140 );
5141 EXCEPTION
5142 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5143 NULL;
5144 End;
5145
5146 END IF;
5147
5148 IF (l_rj_rowcount > 0 ) THEN
5149 x_err_msg := 'WSMPUTIL.check_wsmt('||l_stmt_num||') : Returning error';
5150 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5151 RETURN l_rj_rowcount;
5152 END IF;
5153
5154 END IF;
5155
5156 x_err_code := 0;
5157 x_err_msg := 'WSMPUTIL.CHECK_WSMT:Returned Success';
5158 IF (l_debug = 'Y') THEN
5159 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5160 END IF;
5161
5162 ***************************************************************/
5163
5164 RETURN 0;
5165
5166 EXCEPTION
5167
5168 WHEN OTHERS THEN
5169 x_err_code := SQLCODE;
5170 x_err_msg := 'WSMPUTIL.CHECK_WSMT' ||'(stmt_num='||l_stmt_num||') : '||substrb(sqlerrm, 1,1000);
5171 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5172 return 1; -- return a nonzerovalue.
5173
5174 END CHECK_WSMT;
5175
5176
5177 --
5178 -- Overloaded function, org_id missing
5179 --
5180 FUNCTION CHECK_WSMT
5181 (
5182 p_wip_entity_id IN NUMBER,
5183 p_wip_entity_name IN VARCHAR2,
5184 p_transaction_id IN NUMBER,
5185 p_transaction_date IN DATE,
5186 x_err_code OUT NOCOPY NUMBER,
5187 x_err_msg OUT NOCOPY VARCHAR2
5188 )
5189 RETURN NUMBER
5190 IS
5191
5192 l_organization_id NUMBER := 0;
5193 l_return_value NUMBER := 0;
5194
5195 BEGIN
5196
5197 x_err_code := 0;
5198 x_err_msg := NULL;
5199
5200 /***************************************************************
5201 -- Fixed bug #3453139: Stubbed out this procedure, since it should not be called from anywhere.
5202
5203 l_return_value := check_wsmt(p_wip_entity_id => p_wip_entity_id
5204 , p_wip_entity_name => p_wip_entity_name
5205 , p_transaction_id => p_transaction_id
5206 , p_transaction_date => p_transaction_date
5207 , x_err_code => x_err_code
5208 , x_err_msg => x_err_msg
5209 , p_organization_id => l_organization_id
5210 );
5211
5212 ***************************************************************/
5213
5214 return l_return_value;
5215
5216 END CHECK_WSMT;
5217
5218 /***************************************************************************************/
5219
5220 -- Check WIP MOVE TXN for a LATER Txn already registered for a job.
5221 FUNCTION CHECK_WMT (
5222 x_err_code OUT NOCOPY NUMBER
5223 , x_err_msg OUT NOCOPY VARCHAR2
5224 , p_wip_entity_id IN NUMBER
5225 , p_wip_entity_name IN VARCHAR2
5226 , p_organization_id IN NUMBER
5227 , p_transaction_date IN DATE
5228 )
5229 RETURN NUMBER
5230 IS
5231 l_stmt_num NUMBER := 0;
5232 l_rowcount NUMBER := 0;
5233
5234 BEGIN
5235 x_err_code := 0;
5236 x_err_msg := '';
5237 l_stmt_num := 10;
5238
5239 /***************************************************************
5240 -- Fixed bug #3453139: Stubbed out this procedure, since it is not called from anywhere.
5241
5242 l_rowcount := 0;
5243
5244 IF (p_wip_entity_id IS NOT NULL) THEN
5245
5246 l_stmt_num := 20;
5247
5248 -- Processed WMT Record exists with a Later Txn Date
5249 SELECT 1
5250 INTO l_rowcount
5251 FROM WIP_MOVE_TRANSACTIONS WMT
5252 WHERE WMT.wip_entity_id = p_wip_entity_id
5253 AND WMT.transaction_date > nvl(p_transaction_date, SYSDATE)
5254 AND rownum = 1;
5255
5256
5257 IF (l_rowcount > 0 ) THEN
5258 x_err_msg := 'WSMPUTIL.check_wmt('||l_stmt_num||') : error: Move Txn with a later txn date found.';
5259 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5260 RETURN l_rowcount;
5261 END IF;
5262
5263 ELSIF (p_wip_entity_name IS NOT NULL) THEN
5264
5265 l_stmt_num := 60;
5266
5267 -- Processed WMT Record exists with a Later Txn Date
5268 SELECT 1
5269 INTO l_rowcount
5270 FROM WIP_MOVE_TRANSACTIONS WMT, WIP_ENTITIES WE
5271 WHERE WMT.wip_entity_id = we.wip_entity_id
5272 AND we.wip_entity_name = p_wip_entity_name
5273 AND we.organization_id = p_organization_id
5274 AND WMT.transaction_date > nvl(p_transaction_date, SYSDATE)
5275 AND rownum = 1;
5276
5277
5278 IF (l_rowcount > 0 ) THEN
5279 x_err_msg := 'WSMPUTIL.check_wmt('||l_stmt_num||') : error: Move Txn with a later txn date found.';
5280 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5281 RETURN l_rowcount;
5282 END IF;
5283
5284 END IF;
5285
5286
5287 x_err_code := 0;
5288 ***************************************************************/
5289 RETURN 0;
5290
5291 EXCEPTION
5292 WHEN NO_DATA_FOUND THEN
5293 l_rowcount := 0;
5294 x_err_msg := 'WSMPUTIL.CHECK_WMT:Returned Success';
5295 IF (l_debug = 'Y') THEN
5296 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5297 END IF;
5298 return l_rowcount;
5299
5300 WHEN OTHERS THEN
5301 x_err_code := SQLCODE;
5302 x_err_msg := 'WSMPUTIL.check_wmt('||l_stmt_num||') :'||SUBSTR(SQLERRM,1,1000);
5303 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5304 return 1; -- return a nonzero value.
5305
5306 END CHECK_WMT;
5307
5308
5309 /***************************************************************************************/
5310
5311 FUNCTION CHECK_WSMTI
5312 (
5313 x_err_code OUT NOCOPY NUMBER,
5314 x_err_msg OUT NOCOPY VARCHAR2,
5315 p_wip_entity_id IN NUMBER,
5316 p_wip_entity_name IN VARCHAR2,
5317 p_organization_id IN NUMBER,
5318 p_transaction_date IN DATE
5319 )
5320 RETURN NUMBER
5321 IS
5322
5323 l_sj_rowcount NUMBER := 0;
5324 l_rj_rowcount NUMBER := 0;
5325 l_stmt_num NUMBER := 0;
5326
5327 l_organization_id NUMBER := 0;
5328 l_wip_entity_name WIP_ENTITIES.WIP_ENTITY_NAME%TYPE;
5329
5330 BEGIN
5331
5332 x_err_code := 0;
5333 x_err_msg := '';
5334 l_stmt_num := 10;
5335
5336 /***************************************************************
5337 -- Fixed bug #3453139: Stubbed out this procedure, since it is not called from anywhere.
5338
5339 if l_debug = 'Y' then
5340 FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPUTIL.check_wsmti('||l_stmt_num||') Input parameters are ...');
5341 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_wip_entity_id ='||p_wip_entity_id);
5342 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_wip_entity_name ='||p_wip_entity_name);
5343 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_organization_id ='||p_organization_id);
5344 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_transaction_date ='||to_char(p_transaction_date, 'DD-MON-YYYY HH24:MI:SS'));
5345 end if;
5346
5347 l_sj_rowcount := 0;
5348 l_rj_rowcount := 0;
5349
5350 IF (p_wip_entity_id IS NOT NULL) THEN
5351
5352 l_stmt_num := 20;
5353
5354 Begin
5355
5356 SELECT 1
5357 INTO l_sj_rowcount
5358 FROM dual
5359 WHERE exists (select 'Unprocessed WSJI/WSMTI Record exists'
5360 FROM WSM_STARTING_JOBS_INTERFACE WSJI,
5361 WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
5362 WHERE WSJI.wip_entity_id = p_wip_entity_id
5363 AND WSMTI.header_id = WSJI.header_id
5364 AND WSMTI.process_status IN (WIP_CONSTANTS.PENDING,
5365 WIP_CONSTANTS.RUNNING,
5366 WIP_CONSTANTS.ERROR)
5367 AND WSMTI.transaction_date <= nvl(p_transaction_date,SYSDATE)
5368 );
5369
5370 EXCEPTION
5371 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5372 NULL;
5373 End;
5374
5375 IF (l_sj_rowcount > 0 ) THEN
5376 x_err_msg := 'WSMPUTIL.check_wsmti('||l_stmt_num||') : Returning error - Unprocessed earlier WSJI Txn';
5377 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5378 RETURN l_sj_rowcount;
5379
5380 END IF;
5381
5382 l_stmt_num := 30;
5383
5384 select wip_entity_name, organization_id
5385 into l_wip_entity_name, l_organization_id
5386 from wip_entities
5387 Where wip_entity_id = p_wip_entity_id;
5388
5389 l_stmt_num := 40;
5390
5391 Begin
5392
5393 SELECT 1
5394 INTO l_rj_rowcount
5395 FROM dual
5396 WHERE exists (select 'Unprocessed WRJI/WSMTI Record exists'
5397 FROM WSM_RESULTING_JOBS_INTERFACE WRJI,
5398 WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
5399 WHERE WRJI.wip_entity_name = l_wip_entity_name
5400 AND WSMTI.organization_id = l_organization_id
5401 AND WSMTI.header_id = WRJI.header_id
5402 AND WSMTI.process_status IN (WIP_CONSTANTS.PENDING,
5403 WIP_CONSTANTS.RUNNING,
5404 WIP_CONSTANTS.ERROR)
5405 AND WSMTI.transaction_date <= nvl(p_transaction_date,SYSDATE)
5406 );
5407
5408 EXCEPTION
5409 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5410 NULL;
5411 End;
5412
5413 IF (l_rj_rowcount > 0 ) THEN
5414 x_err_msg := 'WSMPUTIL.check_wsmti('||l_stmt_num||') : Returning error - Unprocessed earlier WRJI Txn';
5415 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5416 RETURN l_rj_rowcount;
5417 END IF;
5418
5419 ELSIF (p_wip_entity_name IS NOT NULL) THEN
5420
5421 l_sj_rowcount := 0;
5422 l_rj_rowcount := 0;
5423
5424 l_stmt_num := 50;
5425
5426
5427 Begin
5428
5429 SELECT 1
5430 INTO l_sj_rowcount
5431 FROM dual
5432 WHERE exists (select 'Unprocessed WSJI/WSMTI Record exists'
5433 FROM WSM_STARTING_JOBS_INTERFACE WSJI,
5434 WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
5435 WHERE WSJI.wip_entity_name = p_wip_entity_name
5436 AND WSMTI.organization_id = p_organization_id
5437 AND WSMTI.header_id = WSJI.header_id
5438 AND WSMTI.process_status IN (WIP_CONSTANTS.PENDING,
5439 WIP_CONSTANTS.RUNNING,
5440 WIP_CONSTANTS.ERROR)
5441 AND WSMTI.transaction_date <= nvl(p_transaction_date,SYSDATE)
5442 );
5443
5444 EXCEPTION
5445 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5446 NULL;
5447 End;
5448
5449 IF (l_sj_rowcount > 0 ) THEN
5450 x_err_msg := 'WSMPUTIL.check_wsmti('||l_stmt_num||') : Returning error - Unprocessed earlier WSJI Txn';
5451 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5452 RETURN l_sj_rowcount;
5453
5454 END IF;
5455
5456 l_stmt_num := 60;
5457
5458 Begin
5459
5460 SELECT 1
5461 INTO l_rj_rowcount
5462 FROM dual
5463 WHERE exists (select 'Unprocessed WRJI/WSMTI Record exists'
5464 FROM WSM_RESULTING_JOBS_INTERFACE WRJI,
5465 WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
5466 WHERE WRJI.wip_entity_name = p_wip_entity_name
5467 AND WSMTI.organization_id = p_organization_id
5468 AND WSMTI.header_id = WRJI.header_id
5469 AND WSMTI.process_status IN (WIP_CONSTANTS.PENDING,
5470 WIP_CONSTANTS.RUNNING,
5471 WIP_CONSTANTS.ERROR)
5472 AND WSMTI.transaction_date <= nvl(p_transaction_date,SYSDATE)
5473 );
5474
5475 EXCEPTION
5476 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5477 NULL;
5478 End;
5479
5480 IF (l_rj_rowcount > 0 ) THEN
5481 x_err_msg := 'WSMPUTIL.check_wsmti('||l_stmt_num||') : Returning error - Unprocessed earlier WRJI Txn';
5482 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5483 RETURN l_rj_rowcount;
5484 END IF;
5485
5486 END IF;
5487
5488 x_err_code := 0;
5489 x_err_msg := 'WSMPUTIL.CHECK_WSMTI:Returned Success - No Unprocessed WSMTI Txns for this lot';
5490 IF (l_debug = 'Y') THEN
5491 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5492 END IF;
5493
5494 ***************************************************************/
5495
5496 RETURN 0;
5497
5498 EXCEPTION
5499
5500 WHEN OTHERS THEN
5501 x_err_code := SQLCODE;
5502 x_err_msg := 'WSMPUTIL.CHECK_WSMTI' ||'(stmt_num='||l_stmt_num||') : '||substrb(sqlerrm, 1,1000);
5503 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5504 return 1; -- return a nonzerovalue.
5505
5506 END CHECK_WSMTI;
5507
5508 /***************************************************************************************/
5509
5510 --------------------------------------------------------------------
5511 -- New Procedures/Functions added for DMF_PF.J or 11.5.10 ----------
5512 --------------------------------------------------------------------
5513 -- Import Network Routing Support through BOM Interface ----------
5514 --------------------------------------------------------------------
5515 -- Bug#/Project: FP.J Import Network Rtg - 3088690
5516 -- New/Overloaded: New
5517 -- Release : 11.5.10.
5518 -- Backward Compatible: YES
5519 -- Modified by: Bala Balakumar.
5520 --------------------------------------------------------------------
5521 FUNCTION JOBS_WITH_QTY_AT_FROM_OP(
5522 x_err_code OUT NOCOPY NUMBER
5523 , x_err_msg OUT NOCOPY varchar2
5524 , p_operation_sequence_id IN NUMBER
5525 )
5526 RETURN BOOLEAN IS
5527
5528 l_stmt_num NUMBER := 0;
5529 l_count NUMBER := 0;
5530
5531 Begin
5532
5533 If fnd_profile.value('WSM_CREATE_LBJ_COPY_ROUTING') = WIP_CONSTANTS.YES Then
5534
5535 Return FALSE;
5536
5537 End If;
5538
5539
5540 l_stmt_num := 10;
5541
5542 Select 1 into l_count
5543 From dual
5544 Where Exists ( Select 'Jobs with Qty At this Operation Exists'
5545 from wip_discrete_jobs wdj
5546 , wip_operations wo
5547 Where wdj.wip_entity_id = wo.wip_entity_id
5548 and NVL(wo.operation_sequence_id, -99999) =
5549 WSMPUTIL.replacement_op_seq_id (p_operation_sequence_id
5550 , wdj.routing_revision_date)
5551 and wdj.status_type = WIP_CONSTANTS.RELEASED
5552 and (
5553 wo.quantity_in_queue <> 0
5554 OR wo.quantity_running <> 0
5555 OR wo.quantity_waiting_to_move <> 0
5556 ));
5557
5558 If l_count <> 0 Then
5559 RETURN TRUE;
5560 Else
5561 RETURN FALSE;
5562 End If;
5563
5564 EXCEPTION
5565 WHEN NO_DATA_FOUND Then
5566 return FALSE;
5567
5568 -- WHEN OTHERS Exception should not be here.
5569 -- This should be handled by the calling program.
5570
5571 END JOBS_WITH_QTY_AT_FROM_OP;
5572
5573
5574 --------------------------------------------------------------------
5575 -- Bug#/Project: FP.J Import Network Rtg - 3088690
5576 -- New/Overloaded: New and Overloaded
5577 -- Release : 11.5.10.
5578 -- Backward Compatible: YES
5579 -- Modified by: Bala Balakumar.
5580 --------------------------------------------------------------------
5581 FUNCTION JOBS_WITH_QTY_AT_FROM_OP(
5582 x_err_code OUT NOCOPY NUMBER
5583 , x_err_msg OUT NOCOPY varchar2
5584 , p_routing_sequence_id IN NUMBER
5585 , p_operation_seq_num IN NUMBER
5586 )
5587 RETURN BOOLEAN IS
5588
5589 l_stmt_num NUMBER := 0;
5590 l_count NUMBER := 0;
5591
5592 Begin
5593
5594 If fnd_profile.value('WSM_CREATE_LBJ_COPY_ROUTING') = WIP_CONSTANTS.YES Then
5595
5596 Return FALSE;
5597
5598 End If;
5599
5600 l_stmt_num := 10;
5601
5602 Select 1 into l_count
5603 From dual
5604 Where Exists (
5605 Select 'Jobs with Qty At this Operation Exists'
5606 from bom_operation_sequences bos
5607 , wip_discrete_jobs wdj
5608 , wip_operations wo
5609 Where wdj.common_routing_sequence_id = p_routing_sequence_id
5610 and wdj.status_type = WIP_CONSTANTS.RELEASED
5611 and bos.routing_sequence_id = wdj.common_routing_sequence_id
5612 and bos.operation_seq_num = p_operation_seq_num
5613 and wdj.routing_revision_date between
5614 bos.effectivity_date and
5615 NVL(bos.disable_date, (wdj.routing_revision_date+1))
5616 and wo.wip_entity_id = wdj.wip_entity_id
5617 and wo.operation_sequence_id = bos.operation_sequence_id
5618 and (wo.quantity_in_queue <> 0
5619 OR wo.quantity_running <> 0
5620 OR wo.quantity_waiting_to_move <> 0
5621 ));
5622
5623 If l_count <> 0 Then
5624 RETURN TRUE;
5625 Else
5626 RETURN FALSE;
5627 End If;
5628
5629 EXCEPTION
5630 WHEN NO_DATA_FOUND Then
5631 return FALSE;
5632
5633 -- WHEN OTHERS Exception should not be here.
5634 -- This should be handled by the calling program.
5635
5636
5637 END JOBS_WITH_QTY_AT_FROM_OP;
5638
5639 --------------------------------------------------------------------
5640 -- Bug#/Project: FP.J OSFM/APS P2 Integration
5641 -- New/Overloaded: New
5642 -- Release : 11.5.10.
5643 -- Backward Compatible: YES
5644 -- Modified by: Bala Balakumar.
5645 --------------------------------------------------------------------
5646 FUNCTION CREATE_LBJ_COPY_RTG_PROFILE
5647 RETURN NUMBER IS
5648
5649 l_mfg_org_id varchar2(20);
5650 l_return_value NUMBER := WIP_CONSTANTS.NO;
5651
5652 BEGIN
5653
5654 /****************** I M P O R T A N T ********************************/
5655 -- YOU SHOULD UNCOMMENT THE NEXT LINE AFTER FP.J UT/ST ---------------
5656 return to_number(fnd_profile.value('WSM_CREATE_LBJ_COPY_ROUTING'));
5657 ----------------------------------------------------------------------
5658
5659 /****************** I M P O R T A N T ********************************/
5660 /***** Following code should be commented out after UT/ST for FP.J ***
5661 ----------------------------------------------------------------------
5662
5663 l_mfg_org_id := fnd_profile.value_specific(
5664 NAME => 'MFG_ORGANIZATION_ID'
5665 , USER_ID => FND_GLOBAL.user_id);
5666
5667
5668 FND_FILE.PUT_LINE(FND_FILE.LOG,
5669 ('User value is '|| to_char(FND_GLOBAL.user_id)
5670 ||', Org Id is '|| l_mfg_org_id)
5671 );
5672
5673 Select to_number(plan_code) into l_return_value
5674 from wsm_parameters
5675 where organization_id = to_number(l_mfg_org_id);
5676
5677 If l_return_value IN (WIP_CONSTANTS.YES, WIP_CONSTANTS.NO) Then
5678 return l_return_value;
5679 Else
5680 return WIP_CONSTANTS.NO;
5681 End If;
5682
5683 Exception
5684 When Others Then
5685 return WIP_CONSTANTS.NO;
5686
5687 ----------------------------------------------------------------------
5688 ************** UPTO HERE, THE CODE SHOULD BE COMMENTED OUT **********/
5689 ----------------------------------------------------------------------
5690
5691 END CREATE_LBJ_COPY_RTG_PROFILE;
5692
5693
5694 --------------------------------------------------------------------
5695 -- Bug#/Project: FP.J OSFM/APS P2 Integration
5696 -- New/Overloaded: New and OVERLOADED
5697 -- Release : 11.5.10.
5698 -- Backward Compatible: YES
5699 -- Modified by: Bala Balakumar.
5700 --------------------------------------------------------------------
5701 FUNCTION CREATE_LBJ_COPY_RTG_PROFILE
5702 (p_organization_id IN NUMBER)
5703 RETURN NUMBER IS
5704
5705 l_return_value NUMBER := WIP_CONSTANTS.NO;
5706 l_plan_code VARCHAR2(30);
5707
5708 BEGIN
5709
5710 -- Following is the strategy to be implemented in UT/ST/Cert/later for FP-J
5711 IF (WSMPUTIL.REFER_SITE_LEVEL_PROFILE = 'Y') THEN
5712 l_return_value := CREATE_LBJ_COPY_RTG_PROFILE;
5713 ELSE -- Refer to the org level setting
5714 select plan_code
5715 into l_plan_code
5716 from wsm_parameters
5717 where organization_id = p_organization_id;
5718
5719 IF (l_plan_code IS NULL) THEN -- retain the site level setting
5720 l_return_value := CREATE_LBJ_COPY_RTG_PROFILE;
5721 ELSE -- get the org-level setting
5722 l_return_value := to_number(l_plan_code);
5723 END IF;
5724 END IF;
5725 return l_return_value;
5726
5727 EXCEPTION
5728 WHEN OTHERS THEN
5729 return WIP_CONSTANTS.NO;
5730
5731 /****************** I M P O R T A N T ********************************/
5732 -- YOU SHOULD UNCOMMENT THE NEXT LINE AFTER FP.J UT/ST ---------------
5733 -- return to_number(fnd_profile.value('WSM_CREATE_LBJ_COPY_ROUTING'));
5734 ----------------------------------------------------------------------
5735
5736 /****************** I M P O R T A N T ********************************/
5737 /***** Following code should be commented out after UT/ST for FP.J ***
5738 ----------------------------------------------------------------------
5739
5740 Select to_number(plan_code) into l_return_value
5741 from wsm_parameters
5742 where organization_id = p_organization_id;
5743
5744 If l_return_value IN (WIP_CONSTANTS.YES, WIP_CONSTANTS.NO) Then
5745 return l_return_value;
5746 Else
5747 return WIP_CONSTANTS.NO;
5748 End If;
5749
5750 Exception
5751 When Others Then
5752 return WIP_CONSTANTS.NO;
5753
5754 ----------------------------------------------------------------------
5755 ************** UPTO HERE, THE CODE SHOULD BE COMMENTED OUT ************/
5756 ----------------------------------------------------------------------
5757
5758 END CREATE_LBJ_COPY_RTG_PROFILE;
5759
5760
5761 --------------------------------------------------------------------
5762 -- Bug#/Project: FP.J - Accounting Period consistent API
5763 -- New or Overloaded: New
5764 -- Release : 11.5.10.
5765 -- Backward Compatible: YES
5766 -- Modified by: Bala Balakumar.
5767 -- RETURN value of 0 indicates the date is in a non-open period.
5768 -- Exceptions should be handled by the calling programs.
5769 --------------------------------------------------------------------
5770 FUNCTION GET_INV_ACCT_PERIOD(
5771 x_err_code OUT NOCOPY NUMBER,
5772 x_err_msg OUT NOCOPY varchar2,
5773 p_organization_id IN NUMBER,
5774 p_date IN DATE) RETURN NUMBER IS
5775
5776 l_acct_period_id NUMBER := 0;
5777 l_open_past_period BOOLEAN := FALSE;
5778
5779 BEGIN
5780
5781 x_err_code := 0;
5782
5783 /* ST : Bug 3205363 Commented the following for LE Timezone change.*/
5784
5785 /*SELECT acct_period_id
5786 INTO l_acct_period_id
5787 FROM org_acct_periods
5788 WHERE organization_id = p_organization_id
5789 AND trunc(nvl(p_date, sysdate))
5790 between PERIOD_START_DATE and SCHEDULE_CLOSE_DATE
5791 AND period_close_date is NULL
5792 AND OPEN_FLAG = 'Y';*/
5793
5794 /* ST : Bug 3205363 LE Timezone change Start */
5795
5796 /* Henceforth call to be made to the tdacheck API to get the accounting period id */
5797
5798 INVTTMTX.tdatechk(org_id => p_organization_id,
5799 transaction_date => p_date,
5800 period_id => l_acct_period_id,
5801 open_past_period => l_open_past_period);
5802
5803 /* open_past_period : FALSE because the check is only for the transaction date to be in an open period.
5804 and not to check if it is in the current ( most recent ) open period */
5805
5806 if(l_acct_period_id = 0) or (l_acct_period_id = -1) then
5807 /*-------------------------------------------------------------+
5808 | 0 : No data found. |
5809 | -1 : some exception occured in the called API ...........|
5810 +-------------------------------------------------------------*/
5811 fnd_message.set_name('WSM', 'WSM_ACCT_PERIOD_NOT_OPEN');
5812 x_err_code := -1;
5813 x_err_msg := FND_MESSAGE.GET;
5814 IF (l_debug = 'Y') THEN -- bug 3373637
5815 fnd_file.put_line(fnd_file.log, 'WSMPUTIL.GET_INV_ACCT_PERIOD: '||x_err_msg
5816 || ' (organization_id = ' || p_organization_id || ')');
5817 END IF;
5818 l_acct_period_id := 0;
5819 end if;
5820
5821 /* ST : Bug 3205363 LE Timezone change End */
5822
5823 Return l_acct_period_id;
5824
5825 EXCEPTION
5826
5827 /* ST : Bug 3205363 Commented the following for LE Time zone change */
5828 /*WHEN NO_DATA_FOUND then
5829 x_err_code := -1;
5830 fnd_message.set_name('WSM', 'WSM_ACCT_PERIOD_NOT_OPEN');
5831 x_err_msg := FND_MESSAGE.GET;
5832 fnd_file.put_line(fnd_file.log, 'WSMPUTIL.GET_INV_ACCT_PERIOD: '||x_err_msg);
5833 l_acct_period_id := 0; -- Date passed is in a NON-OPEN Period.
5834 Return l_acct_period_id;*/
5835
5836 WHEN OTHERS THEN
5837 x_err_code := SQLCODE;
5838 x_err_msg := 'WSMPUTIL.GET_INV_ACCT_PERIOD: ' || substrb(sqlerrm, 1,1000);
5839 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5840 l_acct_period_id := 0; -- Date passed is in a NON-OPEN Period.
5841 Return l_acct_period_id;
5842 END GET_INV_ACCT_PERIOD;
5843
5844 --------------------------------------------------------------------
5845
5846 PROCEDURE AUTONOMOUS_WRITE_TO_WIE (
5847 p_header_id IN NUMBER,
5848 p_message IN VARCHAR2,
5849 p_request_id IN NUMBER,
5850 p_program_id IN NUMBER,
5851 p_program_application_id IN NUMBER,
5852 p_message_type IN NUMBER,
5853 x_err_code OUT NOCOPY NUMBER,
5854 x_err_msg OUT NOCOPY VARCHAR2)
5855 IS
5856 PRAGMA autonomous_transaction;
5857
5858 x_user NUMBER := FND_GLOBAL.user_id;
5859 x_login NUMBER := FND_GLOBAL.login_id;
5860
5861
5862 BEGIN
5863
5864 INSERT INTO WSM_INTERFACE_ERRORS (
5865 HEADER_ID,
5866 MESSAGE,
5867 LAST_UPDATE_DATE,
5868 LAST_UPDATED_BY,
5869 CREATION_DATE,
5870 CREATED_BY,
5871 LAST_UPDATE_LOGIN,
5872 REQUEST_ID,
5873 PROGRAM_ID,
5874 PROGRAM_APPLICATION_ID,
5875 MESSAGE_TYPE )
5876 values (
5877 p_header_id,
5878 p_message,
5879 SYSDATE,
5880 x_user,
5881 SYSDATE,
5882 x_user,
5883 x_login,
5884 p_request_id,
5885 p_program_id,
5886 p_program_application_id,
5887 p_message_type );
5888
5889 COMMIT;
5890
5891 EXCEPTION
5892 WHEN OTHERS THEN
5893 x_err_code := SQLCODE;
5894 x_err_msg := substrb(sqlerrm,1,2000);
5895 rollback;
5896
5897 END AUTONOMOUS_WRITE_TO_WIE;
5898
5899
5900 -- get bom_sequence_id for a given wip_entity_id
5901 FUNCTION GET_JOB_BOM_SEQ_ID(
5902 p_wip_entity_id in number
5903 ) RETURN NUMBER IS
5904
5905 l_common_bom_seq_id NUMBER := 0;
5906 l_bom_seq_id NUMBER := 0;
5907 l_bom_item_id NUMBER;
5908 l_alt_bom VARCHAR2(10);
5909 l_org_id NUMBER;
5910
5911 BEGIN
5912 SELECT wdj.common_bom_sequence_id,
5913 decode(wdj.job_type, 1, wdj.primary_item_id, wdj.bom_reference_id),
5914 wdj.alternate_bom_designator,
5915 wdj.organization_id
5916 INTO l_common_bom_seq_id,
5917 l_bom_item_id,
5918 l_alt_bom,
5919 l_org_id
5920 FROM wip_discrete_jobs wdj
5921 WHERE wdj.wip_entity_id = p_wip_entity_id;
5922
5923 --if(l_common_bom_seq_id IS NULL) then -- bug 3453830
5924 if(l_common_bom_seq_id IS NULL or l_common_bom_seq_id = 0) then -- bug 3453830
5925 return null;
5926 else
5927 SELECT bbom.bill_sequence_id
5928 INTO l_bom_seq_id
5929 FROM bom_bill_of_materials bbom
5930 WHERE bbom.common_bill_sequence_id = l_common_bom_seq_id
5931 AND bbom.organization_id = l_org_id
5932 AND bbom.assembly_item_id = l_bom_item_id
5933 AND nvl(bbom.alternate_bom_designator, '-@#$%') = nvl(l_alt_bom, '-@#$%');
5934 end if;
5935
5936 return l_bom_seq_id;
5937
5938 EXCEPTION
5939
5940 WHEN OTHERS THEN
5941 return -1;
5942
5943 END GET_JOB_BOM_SEQ_ID;
5944
5945
5946 -- Start : Added to fix bug 3452913 --
5947 FUNCTION replacement_copy_op_seq_id (
5948 p_job_op_seq_id NUMBER,
5949 p_wip_entity_id NUMBER
5950 ) RETURN INTEGER
5951 IS
5952 l_copy_op_seq_id NUMBER := NULL;
5953 BEGIN
5954
5955 SELECT distinct(wco.operation_sequence_id) -- Added distinct to fix bug #3507878
5956 INTO l_copy_op_seq_id
5957 FROM wsm_copy_operations wco,
5958 wip_operations wo
5959 WHERE wo.operation_sequence_id = p_job_op_seq_id
5960 AND wo.wip_entity_id = p_wip_entity_id
5961 AND wo.wip_entity_id = wco.wip_entity_id
5962 AND wo.wsm_op_seq_num = wco.operation_seq_num;
5963
5964 return l_copy_op_seq_id;
5965
5966 EXCEPTION
5967 WHEN NO_DATA_FOUND THEN
5968 return NULL;
5969
5970 WHEN OTHERS THEN
5971 return NULL;
5972 END replacement_copy_op_seq_id;
5973 -- End : Added to fix bug 3452913 --
5974
5975
5976 -- BA bug 3512105
5977 -- will return WLBJ.internal_copy_type, return -3 if not available
5978 FUNCTION get_internal_copy_type (
5979 p_wip_entity_id NUMBER
5980 ) RETURN INTEGER
5981 IS
5982 l_int_copy_type NUMBER;
5983 BEGIN
5984
5985 SELECT INTERNAL_COPY_TYPE
5986 INTO l_int_copy_type
5987 FROM wsm_lot_based_jobs
5988 WHERE wip_entity_id = p_wip_entity_id;
5989 return l_int_copy_type;
5990
5991 EXCEPTION
5992 when others then
5993 return 3;
5994 END;
5995
5996 -- EA bug 3512105
5997
5998 --bug 3754881 procedure for locking wdj to be called from the Move and WLT Forms
5999 PROCEDURE lock_wdj(
6000 x_err_code OUT NOCOPY NUMBER
6001 , x_err_msg OUT NOCOPY VARCHAR2
6002 , p_wip_entity_id IN NUMBER
6003 , p_rollback_flag IN NUMBER)
6004 IS
6005 row_locked EXCEPTION;
6006 PRAGMA EXCEPTION_INIT(row_locked, -54);
6007 l_dummy NUMBER;
6008 BEGIN
6009 IF p_rollback_flag = 1 THEN
6010 ROLLBACK TO LOCK_WDJ;
6011 END IF;
6012
6013 SAVEPOINT LOCK_WDJ;
6014
6015 SELECT 1
6016 INTO l_dummy
6017 FROM wip_discrete_jobs
6018 WHERE wip_entity_id = p_wip_entity_id
6019 FOR UPDATE NOWAIT;
6020
6021 -- bug 4932475 (base bug 4759095): Create a savepoint after locking wdj. We will rollback to this savepoint
6022 -- in rollback_before_add_operation so that the lock on the job is retained.
6023 SAVEPOINT AFTER_LOCK_WDJ;
6024
6025 x_err_code := 0;
6026
6027 EXCEPTION
6028 WHEN row_locked THEN
6029 x_err_code := 1;
6030
6031 WHEN others THEN
6032 x_err_code := SQLCODE;
6033 x_err_msg := substr('WSMPUTIL.LOCK_WDJ: ' || SQLERRM, 1, 4000);
6034 END lock_wdj;
6035 --end bug 3754881
6036 --Bug 5182520:Added the following procedure to handle material status checks.
6037 Function is_status_applicable(p_wms_installed IN VARCHAR2,
6038 p_trx_status_enabled IN NUMBER,
6039 p_trx_type_id IN NUMBER,
6040 p_lot_status_enabled IN VARCHAR2,
6041 p_serial_status_enabled IN VARCHAR2,
6042 p_organization_id IN NUMBER,
6043 p_inventory_item_id IN NUMBER,
6044 p_sub_code IN VARCHAR2,
6045 p_locator_id IN NUMBER,
6046 p_lot_number IN VARCHAR2,
6047 p_serial_number IN VARCHAR2,
6048 x_error_msg OUT NOCOPY VARCHAR2
6049 )
6050 return varchar2 is
6051 l_status_applicable VARCHAR2(1) := 'Y';
6052 l_item MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
6053 l_locator MTL_ITEM_LOCATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
6054 BEGIN
6055 IF (p_inventory_item_id IS NOT NULL and p_sub_code IS NOT NULL) THEN
6056 l_status_applicable := INV_MATERIAL_STATUS_GRP.is_status_applicable(p_wms_installed => p_wms_installed,
6057 p_trx_status_enabled => p_trx_status_enabled ,
6058 p_trx_type_id => p_trx_type_id ,
6059 p_lot_status_enabled => p_lot_status_enabled ,
6060 p_serial_status_enabled => p_serial_status_enabled,
6061 p_organization_id => p_organization_id ,
6062 p_inventory_item_id => p_inventory_item_id ,
6063 p_sub_code => p_sub_code ,
6064 p_locator_id => p_locator_id ,
6065 p_lot_number => p_lot_number ,
6066 p_serial_number => p_serial_number ,
6067 p_object_type =>'Z');
6068
6069 END IF; --End of p_inventory_item_id IS NOT NULL and p_sub_code IS NOT NULL
6070
6071 IF l_status_applicable = 'N' THEN
6072 FND_MESSAGE.SET_NAME('WSM','WSM_TRX_SUBINV_NA_DUE_MS');
6073 FND_MESSAGE.SET_TOKEN('TOKEN1', p_sub_code);
6074 x_error_msg := fnd_message.get;
6075
6076 return l_status_applicable;
6077 END IF;
6078
6079 IF (p_locator_id IS NOT NULL) THEN
6080 l_status_applicable := INV_MATERIAL_STATUS_GRP.is_status_applicable(p_wms_installed => p_wms_installed,
6081 p_trx_status_enabled => p_trx_status_enabled ,
6082 p_trx_type_id => p_trx_type_id ,
6083 p_lot_status_enabled => p_lot_status_enabled ,
6084 p_serial_status_enabled => p_serial_status_enabled,
6085 p_organization_id => p_organization_id ,
6086 p_inventory_item_id => p_inventory_item_id ,
6087 p_sub_code => p_sub_code ,
6088 p_locator_id => p_locator_id ,
6089 p_lot_number => p_lot_number ,
6090 p_serial_number => p_serial_number ,
6091 p_object_type =>'L');
6092 END IF; --End of p_locator_id IS NOT NULL
6093
6094 IF l_status_applicable = 'N' THEN
6095 select concatenated_segments
6096 into l_locator
6097 from mtl_item_locations_kfv
6098 where inventory_location_id = p_locator_id
6099 and organization_id = p_organization_id;
6100
6101 FND_MESSAGE.SET_NAME('INV','INV_TRX_LOCATOR_NA_DUE_MS');
6102 FND_MESSAGE.SET_TOKEN('TOKEN1', l_locator);
6103 x_error_msg := fnd_message.get;
6104 return l_status_applicable;
6105 END IF;
6106
6107 IF (p_lot_number IS NOT NULL) THEN
6108 l_status_applicable := INV_MATERIAL_STATUS_GRP.is_status_applicable(p_wms_installed => p_wms_installed,
6109 p_trx_status_enabled => p_trx_status_enabled ,
6110 p_trx_type_id => p_trx_type_id ,
6111 p_lot_status_enabled => p_lot_status_enabled ,
6112 p_serial_status_enabled => p_serial_status_enabled,
6113 p_organization_id => p_organization_id ,
6114 p_inventory_item_id => p_inventory_item_id ,
6115 p_sub_code => p_sub_code ,
6116 p_locator_id => p_locator_id ,
6117 p_lot_number => p_lot_number ,
6118 p_serial_number => p_serial_number ,
6119 p_object_type =>'O');
6120 END IF; --End of p_lot_number IS NOT NULL
6121
6122 IF l_status_applicable = 'N' THEN
6123 select concatenated_segments
6124 into l_item
6125 from mtl_system_items_kfv
6126 where inventory_item_id = p_inventory_item_id
6127 and organization_id = p_organization_id;
6128
6129 FND_MESSAGE.SET_NAME('INV','INV_TRX_LOT_NA_DUE_MS');
6130 FND_MESSAGE.SET_TOKEN('TOKEN1', p_lot_number);
6131 FND_MESSAGE.SET_TOKEN('TOKEN2', l_item);
6132 x_error_msg := fnd_message.get;
6133 return l_status_applicable;
6134 END IF;
6135
6136 IF (p_serial_number IS NOT NULL) THEN
6137 l_status_applicable := INV_MATERIAL_STATUS_GRP.is_status_applicable(p_wms_installed => p_wms_installed,
6138 p_trx_status_enabled => p_trx_status_enabled ,
6139 p_trx_type_id => p_trx_type_id ,
6140 p_lot_status_enabled => p_lot_status_enabled ,
6141 p_serial_status_enabled => p_serial_status_enabled,
6142 p_organization_id => p_organization_id ,
6143 p_inventory_item_id => p_inventory_item_id ,
6144 p_sub_code => p_sub_code ,
6145 p_locator_id => p_locator_id ,
6146 p_lot_number => p_lot_number ,
6147 p_serial_number => p_serial_number ,
6148 p_object_type =>'S');
6149 END IF; --End of p_lot_number IS NOT NULL
6150
6151 IF l_status_applicable = 'N' THEN
6152 select concatenated_segments
6153 into l_item
6154 from mtl_system_items_kfv
6155 where inventory_item_id = p_inventory_item_id
6156 and organization_id = p_organization_id;
6157
6158 FND_MESSAGE.SET_NAME('INV','INV_TRX_SER_NA_DUE_MS');
6159 FND_MESSAGE.SET_TOKEN('TOKEN1', p_serial_number);
6160 FND_MESSAGE.SET_TOKEN('TOKEN2', l_item);
6161 x_error_msg := fnd_message.get;
6162 END IF;
6163
6164 return l_status_applicable;
6165
6166 END is_status_applicable;
6167
6168 -- This Function is added to support Add operations/links in LBJ Interface.
6169 FUNCTION validate_job_network(
6170 p_wip_entity_id NUMBER,
6171 x_err_code OUT NOCOPY NUMBER,
6172 x_err_msg OUT NOCOPY VARCHAR2)
6173 RETURN NUMBER IS
6174
6175 type network_links IS record (
6176 operation wsm_copy_op_networks.from_op_seq_num%type,
6177 prev_op wsm_copy_op_networks.from_op_seq_num%type,
6178 prev_op_rec_flag wsm_copy_op_networks.recommended%type,
6179 next_op wsm_copy_op_networks.to_op_seq_num%type,
6180 next_op_rec_flag wsm_copy_op_networks.recommended%type);
6181
6182 type t_network_links is table of network_links index by binary_integer;
6183 v_network_links t_network_links;
6184
6185 type t_primary_path is table of number index by binary_integer;
6186 v_primary_path t_primary_path;
6187
6188 cursor c_job_network is
6189 SELECT CASE
6190 WHEN a.op_seq IS NULL THEN
6191 b.op_seq
6192 ELSE
6193 a.op_seq
6194 END operation,
6195 b.prev_seq prev_op,
6196 b.prev_op_reco,
6197 a.next_op next_op,
6198 a.next_op_reco
6199 FROM
6200 (SELECT from_op_seq_num op_seq,
6201 to_op_seq_num next_op,
6202 recommended next_op_reco
6203 FROM wsm_copy_op_networks
6204 WHERE wip_entity_id = p_wip_entity_id) a
6205 FULL OUTER JOIN
6206 (SELECT to_op_seq_num op_seq,
6207 from_op_seq_num prev_seq,
6208 recommended prev_op_reco
6209 FROM wsm_copy_op_networks
6210 WHERE wip_entity_id = p_wip_entity_id) b
6211 ON a.op_seq = b.op_seq
6212 ORDER BY 1,4;
6213
6214 l_counter number;
6215 l_start_op number;
6216 l_end_op number;
6217 l_nw_start number;
6218 l_nw_end number;
6219 l_prev_op number;
6220 l_next_op_link number;
6221 l_next_link_op number;
6222 l_reco_count number :=0;
6223 l_link_count number :=1;
6224 l_stmt_num number;
6225
6226 e_multiple_start_op exception;
6227 e_multiple_end_op exception;
6228 e_multiple_primary_path exception;
6229 e_network_loop exception;
6230 e_no_continuous_path exception;
6231
6232 BEGIN
6233
6234 l_stmt_num := 10;
6235 begin
6236 select operation_seq_num
6237 into l_nw_start
6238 from wsm_copy_operations
6239 where wip_entity_id = p_wip_entity_id
6240 and network_start_end = 'S';
6241 exception
6242 when others then
6243 raise e_multiple_start_op;
6244 end;
6245 l_stmt_num := 20;
6246 begin
6247 select operation_seq_num
6248 into l_nw_end
6249 from wsm_copy_operations
6250 where wip_entity_id = p_wip_entity_id
6251 and network_start_end = 'E';
6252 exception
6253 when others then
6254 raise e_multiple_end_op;
6255 end;
6256 l_stmt_num := 30;
6257 open c_job_network;
6258 fetch c_job_network bulk collect into v_network_links;
6259 close c_job_network;
6260 l_stmt_num := 40;
6261 l_counter := v_network_links.first;
6262 while l_counter is not null loop
6263
6264 -- Validate if the network has unique start operation.
6265 if v_network_links(l_counter).prev_op is null then
6266 if v_network_links(l_counter).operation <> nvl(l_start_op,v_network_links(l_counter).operation) then
6267 raise e_multiple_start_op;
6268 end if;
6269 l_start_op := v_network_links(l_counter).operation;
6270 end if;
6271 l_stmt_num := 50;
6272 -- Validate if the network has unique end operation.
6273 if v_network_links(l_counter).next_op is null then
6274 if v_network_links(l_counter).operation <> nvl(l_end_op,v_network_links(l_counter).operation) then
6275 raise e_multiple_end_op;
6276 end if;
6277 l_end_op := v_network_links(l_counter).operation;
6278 end if;
6279 l_stmt_num := 60;
6280 -- Validate if the network has unique primary path.
6281 if v_network_links(l_counter).operation = l_prev_op then
6282 if (v_network_links(l_counter).next_op_rec_flag='Y' and l_reco_count=1) then
6283 if l_next_op_link <> v_network_links(l_counter).next_op then
6284 raise e_multiple_primary_path;
6285 end if;
6286 elsif v_network_links(l_counter).next_op_rec_flag='Y' then
6287 l_reco_count :=1;
6288 l_next_op_link := v_network_links(l_counter).next_op;
6289 end if;
6290 else
6291 l_prev_op := v_network_links(l_counter).operation;
6292 l_reco_count :=0;
6293 if v_network_links(l_counter).next_op_rec_flag='Y' then
6294 l_reco_count :=1;
6295 l_next_op_link := v_network_links(l_counter).next_op;
6296 end if;
6297 end if;
6298 l_stmt_num := 70;
6299 -- Validate if start operation has any previous operations.
6300 if v_network_links(l_counter).operation = l_nw_start and
6301 v_network_links(l_counter).prev_op is not null then
6302 raise e_network_loop;
6303 end if;
6304 l_stmt_num := 80;
6305 -- Validate if end operation has any next operations.
6306 if v_network_links(l_counter).operation = l_nw_end and
6307 v_network_links(l_counter).next_op is not null then
6308 raise e_network_loop;
6309 end if;
6310 l_stmt_num := 90;
6311 -- Validate for loop in primary path as well as build the primary path.
6312 if (not v_primary_path.exists(v_network_links(l_counter).operation)) then
6313 if nvl(v_network_links(l_counter).next_op_rec_flag,'Y')='Y' then
6314 v_primary_path(v_network_links(l_counter).operation) := v_network_links(l_counter).next_op;
6315 l_next_link_op := v_network_links(l_counter).next_op;
6316 end if;
6317 else
6318 if v_network_links(l_counter).next_op_rec_flag='Y' then
6319 if nvl(l_next_link_op,v_network_links(l_counter).next_op) <> v_network_links(l_counter).next_op then
6320 raise e_network_loop;
6321 end if;
6322 end if;
6323 end if;
6324 l_counter := v_network_links.next(l_counter);
6325 end loop;
6326 l_stmt_num := 100;
6327 if l_start_op <> l_nw_start then
6328 raise e_multiple_start_op;
6329 end if;
6330
6331 if l_end_op <> l_nw_end then
6332 raise e_multiple_end_op;
6333 end if;
6334 l_stmt_num := 110;
6335 --Validate if primary path is continuous.
6336 l_counter := l_start_op;
6337 loop
6338 l_link_count := l_link_count+1;
6339 if (not v_primary_path.exists(l_counter)) then
6340 raise e_no_continuous_path;
6341 else
6342 l_counter := v_primary_path(l_counter);
6343 if v_primary_path(l_counter) is null then
6344 if l_link_count <> v_primary_path.count then
6345 raise e_no_continuous_path;
6346 end if;
6347 exit;
6348 end if;
6349 end if;
6350 end loop;
6351 l_stmt_num := 120;
6352 x_err_code :=0;
6353 x_err_msg := null;
6354 return 0;
6355
6356 EXCEPTION
6357
6358 when e_multiple_start_op then
6359 x_err_code := -1;
6360 fnd_message.set_name('WSM','WSM_MULT_PRIMARY_STARTS');
6361 x_err_msg := 'Error: validate_job_network: (#'||l_stmt_num||') ' ||fnd_message.get;
6362 return 1;
6363
6364 when e_multiple_end_op then
6365 x_err_code := -1;
6366 fnd_message.set_name('WSM','WSM_MULT_PRIMARY_ENDS');
6367 x_err_msg := 'Error: validate_job_network: (#'||l_stmt_num||') ' ||fnd_message.get;
6368 return 1;
6369
6370 when e_multiple_primary_path then
6371 x_err_code := -1;
6372 fnd_message.set_name('WSM','WSM_MULT_PRIMARY_PATHS');
6373 x_err_msg := 'Error: validate_job_network: (#'||l_stmt_num||') ' ||fnd_message.get;
6374 return 1;
6375
6376 when e_network_loop then
6377 x_err_code := -1;
6378 fnd_message.set_name('WSM','WSM_NTWK_LOOP_EXISTS');
6379 x_err_msg := 'Error: validate_job_network: (#'||l_stmt_num||') ' ||fnd_message.get;
6380 return 1;
6381
6382 when e_no_continuous_path then
6383 x_err_code := -1;
6384 fnd_message.set_name('WSM','WSM_PRIMARY_PATH_END_IMPROPER');
6385 fnd_message.set_token('WSM_SEQ_NUM',l_counter);
6386 x_err_msg := 'Error: validate_job_network: (#'||l_stmt_num||') ' ||fnd_message.get;
6387 return 1;
6388
6389 when others then
6390 x_err_code := -1;
6391 x_err_msg := 'Error: validate_job_network: (#'||l_stmt_num||') ' ||sqlerrm(sqlcode);
6392 return 1;
6393
6394 END validate_job_network;
6395
6396
6397
6398 END WSMPUTIL;
[Home] [Help]