1 PACKAGE BODY WSMPUTIL AS
2 /* $Header: WSMUTILB.pls 120.6.12010000.3 2008/10/22 10:42:02 ybabulal 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 where sj.wip_entity_name = wt.wip_entity_name
4054 and sj.organization_id = wt.organization_id
4055 and sj.transaction_id = wmt.transaction_id
4056 and (wmt.status <> 4 or nvl(wmt.costed,1) <> 4))
4057 or exists (
4058 select 1
4059 from wsm_sm_resulting_jobs rj,
4060 wsm_split_merge_transactions wmt
4061 --Bug 4744794: join based on wip_entity_id is replaced with
4062 -- join based on wip_entity_name so that index is used.
4063 --where rj.wip_entity_id = wt.wip_entity_id
4064 where rj.wip_entity_name = wt.wip_entity_name
4065 and rj.organization_id = wt.organization_id
4066 and rj.transaction_id = wmt.transaction_id
4067 and (wmt.status <> 4 or nvl(wmt.costed,1) <> 4))
4068 or exists (
4069 select 1
4070 from wsm_starting_jobs_interface wsji,
4071 wsm_split_merge_txn_interface wsmti
4072 where wsji.wip_entity_id = wt.wip_entity_id
4073 and wsmti.header_id = wsji.header_id
4074 and wsmti.process_status in (WIP_CONSTANTS.PENDING, WIP_CONSTANTS.RUNNING))
4075 or exists (
4076 select 1
4077 from wsm_resulting_jobs_interface wrji,
4078 wsm_split_merge_txn_interface wsmti
4079 where wrji.wip_entity_name = wt.wip_entity_name
4080 and wsmti.header_id = wrji.header_id
4081 and wsmti.process_status in (WIP_CONSTANTS.PENDING, WIP_CONSTANTS.RUNNING))
4082 --Bug 4744794: Separate SQLs are used to select the records for the cases
4083 -- wip_entity_id is Null and wip_entity_id is NOT NULL
4084 or exists (
4085 select 1
4086 from wsm_lot_move_txn_interface wlmti
4087 --where (nvl(wlmti.wip_entity_id, -9999) = wt.wip_entity_id or
4088 -- nvl(wlmti.wip_entity_name, '@#$*') = wt.wip_entity_name)
4089 where wlmti.wip_entity_id = wt.wip_entity_id
4090 and wlmti.status in (WIP_CONSTANTS.PENDING, WIP_CONSTANTS.RUNNING))
4091 or exists (
4092 select 1
4093 from wsm_lot_move_txn_interface wlmti
4094 where wlmti.wip_entity_name = wt.wip_entity_name
4095 and wlmti.organization_id = wt.organization_id
4096 and wlmti.status in (WIP_CONSTANTS.PENDING, WIP_CONSTANTS.RUNNING)));
4097
4098 if sql%rowcount > 0 then
4099 x_err_code := 0; -- this is needed by WIP to figure out whether to end the request in warning or success.
4100 end if;
4101 if (l_debug = 'Y') then
4102 fnd_file.put_line(fnd_file.log, 'WSMPUTIL.validate_lbj_before_close: Updated '||sql%rowcount|| ' LBJ records to ERROR.');
4103 end if;
4104
4105
4106 -- Update the WDJ status type to 15
4107 update wip_discrete_jobs
4108 set status_type = 15 -- Failed Close.
4109 where wip_entity_id in
4110 (select wt.wip_entity_id
4111 from wip_dj_close_temp wt, wip_entities we
4112 where wt.group_id = p_group_id
4113 and wt.organization_id = p_organization_id
4114 and wt.status_type = 99
4115 and wt.wip_entity_id = we.wip_entity_id
4116 and we.entity_type = 5); -- we will touch only the LBJs.
4117
4118 if (l_debug = 'Y') then
4119 fnd_file.put_line(fnd_file.log, 'WSMPUTIL.validate_lbj_before_close: Successfully updated status of '||sql%rowcount|| ' records to FAILED CLOSE.');
4120 end if;
4121
4122 if (sql%rowcount > 0) then
4123 fnd_file.put_line(fnd_file.log, 'Following jobs failed the close process because of unprocessed/uncosted WIP lot transactions:');
4124
4125 -- Print the entities which FAILED CLOSE
4126 for rec in (select tm.wip_entity_id, we.wip_entity_name
4127 from wip_dj_close_temp tm, wip_entities we
4128 where tm.wip_entity_id = we.wip_entity_id
4129 and tm.organization_id = we.organization_id
4130 and tm.group_id = p_group_id
4131 and tm.organization_id = p_organization_id
4132 and tm.status_type = 99
4133 and we.entity_type = 5)
4134 loop
4135 fnd_file.put_line(fnd_file.log, rec.wip_entity_name);
4136 end loop;
4137
4138 end if;
4139
4140
4141 -- Clean up the temp table.
4142 delete from wip_dj_close_temp
4143 where group_id = p_group_id
4144 and organization_id = p_organization_id
4145 and status_type = 99;
4146
4147 if (l_debug = 'Y') then
4148 fnd_file.put_line(fnd_file.log, 'WSMPUTIL.validate_lbj_before_close: Successfully cleaned up temp table by deleting '||sql%rowcount|| ' records.');
4149 end if;
4150
4151 EXCEPTION
4152 when others then
4153 fnd_file.put_line(fnd_file.log, 'WSMPUTIL.validate_lbj_before_close: Unexpected Error: '||sqlerrm);
4154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4155
4156 END validate_lbj_before_close;
4157
4158
4159
4160
4161 -- The following procedure supplies parameters to the inventory to be used
4162 -- for the record group for the lov that displays lists all lots containing
4163 -- all the components required at the first operation in the bom of the
4164 -- chosen assembly that is being replenished.
4165
4166 PROCEDURE get_Kanban_rec_grp_info (p_organization_id IN number,
4167 p_kanban_assembly_id IN number,
4168 p_rtg_rev_date IN date,
4169 p_bom_seq_id OUT NOCOPY number,
4170 p_start_seq_num OUT NOCOPY number,
4171 p_error_code OUT NOCOPY number,
4172 p_error_msg OUT NOCOPY varchar2) IS
4173
4174 l_stmt_num number;
4175 l_routing_seq_id number;
4176 l_common_routing_sequence_id number;
4177 l_start_op_seq_id number;
4178 l_error_code number := 0;
4179 l_err_msg varchar2(2000) := '';
4180
4181 begin
4182
4183 l_stmt_num := 10;
4184
4185 begin
4186 SELECT bom.common_bill_sequence_id
4187 INTO p_bom_seq_id
4188 FROM bom_bill_of_materials bom
4189 WHERE bom.alternate_bom_designator is null
4190 AND bom.assembly_item_id = p_kanban_assembly_id
4191 AND bom.organization_id = p_organization_id;
4192 exception
4193 when no_data_found then
4194 p_bom_seq_id := null;
4195 end;
4196
4197 l_stmt_num := 20;
4198
4199 select bor.routing_sequence_id
4200 into l_routing_seq_id
4201 from bom_routing_alternates_v bor
4202 where bor.organization_id = p_organization_id
4203 and bor.assembly_item_id = p_kanban_assembly_id
4204 and bor.alternate_routing_designator is null
4205 and bor.routing_type = 1
4206 and bor.cfm_routing_flag = 3;
4207
4208
4209 l_stmt_num := 30;
4210
4211 WSMPUTIL.find_common_routing(
4212 p_routing_sequence_id => l_routing_seq_id,
4213 p_common_routing_sequence_id => l_common_routing_sequence_id,
4214 x_err_code => l_error_code,
4215 x_err_msg => l_err_msg);
4216
4217 if l_error_code <> 0 then
4218 p_error_code := l_error_code;
4219 p_error_msg := l_err_msg;
4220 return;
4221 end if;
4222
4223
4224 l_stmt_num := 40;
4225
4226 WSMPUTIL.find_routing_start ( l_common_routing_sequence_id,
4227 p_rtg_rev_date,
4228 l_start_op_seq_id,
4229 l_error_code,
4230 l_err_msg );
4231
4232 if l_error_code <> 0 then
4233 p_error_code := l_error_code;
4234 p_error_msg := l_err_msg;
4235 return;
4236 end if;
4237
4238
4239 l_stmt_num := 50;
4240
4241 select bos.operation_seq_num
4242 into p_start_seq_num
4243 from bom_operation_sequences bos
4244 where bos.operation_sequence_id = l_start_op_seq_id;
4245
4246 exception
4247
4248 when others then
4249 p_error_code := SQLCODE;
4250 p_error_msg := substr('wsmputil.get_Kanban_rec_grp_info: stmt no: '||l_stmt_num||' '||SQLERRM,1, 2000);
4251
4252
4253 end get_Kanban_rec_grp_info;
4254
4255
4256
4257
4258 -- the following procedure finds the maximum number of assemblies that can be created
4259 -- out of a given component of a given bill
4260
4261 PROCEDURE get_max_kanban_asmbly_qty (p_bill_seq_id IN number,
4262 p_component_item_id IN number,
4263 p_bom_revision_date IN date,
4264 p_start_seq_num IN number,
4265 p_available_qty IN number,
4266 p_max_asmbly_qty OUT NOCOPY number,
4267 p_error_code OUT NOCOPY number,
4268 p_error_msg OUT NOCOPY varchar2) IS
4269
4270 l_component_quantity number;
4271 l_component_yield_factor number;
4272
4273 begin
4274
4275 select component_quantity, component_yield_factor
4276 into l_component_quantity, l_component_yield_factor
4277 from bom_inventory_components
4278 where bill_sequence_id = p_bill_seq_id
4279 and component_item_id = p_component_item_id
4280 and (operation_seq_num = p_start_seq_num or operation_seq_num = 1)
4281 and p_bom_revision_date between effectivity_date and nvl(disable_date, p_bom_revision_date + 1);
4282
4283 p_max_asmbly_qty := round(((p_available_qty * l_component_yield_factor)/l_component_quantity), 6);
4284
4285 exception
4286
4287 when others then
4288 p_error_code := SQLCODE;
4289 p_error_msg := substr('wsmputil.get_max_kanban_asmbly_qty: '||' '||SQLERRM,1, 2000);
4290
4291 end get_max_kanban_asmbly_qty;
4292
4293 /* bug fix:7387499
4294 ***************************************************************************
4295 created this function for bug fix 5529692 to call this in
4296 BOM_OPERATION_NETWORKS_V to fetch standard operation id of
4297 operation that is relatively effective at a particular operation
4298 sequence number and inturn fetch its op code
4299 **************************************************************************/
4300
4301 FUNCTION get_eff_stdop_id(p_stdop_id NUMBER,
4302 p_opseq_id NUMBER)
4303 return NUMBER
4304 is
4305 l_opseq_num number;
4306 l_eff_date date;
4307 l_routseq_id number;
4308 l_eff_stdop_id number;
4309 l_operation_type number;
4310 begin
4311
4312 select operation_seq_num,routing_sequence_id,operation_type
4313 into l_opseq_num,l_routseq_id,l_operation_type
4314 from bom_operation_sequences
4315 where standard_operation_id = p_stdop_id
4316 and operation_sequence_id = p_opseq_id;
4317
4318 l_eff_date := WSMPUTIL.EFFECTIVE_DATE(l_opseq_num,l_routseq_id,l_operation_type);
4319
4320 select standard_operation_id into l_eff_stdop_id
4321 from bom_operation_sequences
4322 where effectivity_date = l_eff_date
4323 and operation_seq_num = l_opseq_num
4324 and routing_sequence_id = l_routseq_id
4325 and operation_type = l_operation_type;
4326
4327 return l_eff_stdop_id;
4328
4329 end get_eff_stdop_id;
4330
4331 /****************************************************************************************************
4332 Created this function for bug fix 5529692 to call this in
4333 BOM_OPERATION_NETWORKS_V to fetch standard department id of
4334 operation that is relatively effective at a particular operation
4335 sequence number and inturn fetch its department code
4336 ****************************************************************************************************/
4337
4338 FUNCTION get_eff_dept_id(p_dept_id number,
4339 p_opseq_id number)
4340 return number
4341 is
4342 l_opseq_num number;
4343 l_eff_date date;
4344 l_routseq_id number;
4345 l_eff_dept_id number;
4346 l_operation_type number;
4347 begin
4348
4349 select operation_seq_num,routing_sequence_id,operation_type
4350 into l_opseq_num,l_routseq_id,l_operation_type
4351 from bom_operation_sequences
4352 where department_id = p_dept_id
4353 and operation_SEQUENCE_id = p_opseq_id;
4354
4355 l_eff_date := WSMPUTIL.EFFECTIVE_DATE(l_opseq_num,l_routseq_id,l_operation_type);
4356
4357 select department_id into l_eff_dept_id
4358 from bom_operation_sequences
4359 where effectivity_date = l_eff_date
4360 and operation_seq_num = l_opseq_num
4361 and routing_sequence_id = l_routseq_id
4362 and operation_type =l_operation_type;
4363
4364 return l_eff_dept_id;
4365
4366 end get_eff_dept_id;
4367
4368
4369 /************************************************************************************************************************
4370 created this function to pick up the effectivity_date of operation that is
4371 relatively effective at particular operation sequence number of the routing,
4372 so that it can be shown in the lov attached to From
4373 and To fields in Network Routings form.
4374 ******************************************************************************************************************/
4375
4376 FUNCTION EFFECTIVE_DATE(p_oper_seq_num number,
4377 p_routing_seq_id number,
4378 p_operation_type number)
4379 RETURN DATE
4380 IS
4381 l_eff_date date;
4382 l_count number := 0;
4383 l_efe_sysdate number := 1;
4384 l_max_date date := NULL;
4385 l_min_date date := NULL;
4386 begin
4387 select count(*) into l_count
4388 from bom_operation_sequences s
4389 where s.routing_sequence_id = p_routing_seq_id
4390 and s.operation_seq_num = p_oper_seq_num
4391 and s.operation_type = p_operation_type
4392 group by s.operation_seq_num;
4393
4394 if (l_count = 1) then
4395
4396 select s.effectivity_date into l_eff_date
4397 from bom_operation_sequences s
4398 where s.routing_sequence_id = p_routing_seq_id
4399 and s.operation_seq_num = p_oper_seq_num
4400 and s.operation_type = p_operation_type;
4401
4402
4403 return l_eff_date;
4404
4405 else
4406
4407 begin
4408 select max(s.effectivity_date) into l_eff_date from bom_operation_sequences s
4409 where s.routing_sequence_id = p_routing_seq_id
4410 and s.operation_seq_num = p_oper_seq_num
4411 and sysdate <= nvl(s.disable_date, sysdate+1)
4412 and s.effectivity_date <= sysdate
4413 and s.operation_type = p_operation_type
4414 group by s.operation_seq_num ;
4415
4416
4417 exception
4418 WHEN NO_DATA_FOUND THEN
4419 l_efe_sysdate := 0;
4420 end;
4421
4422 if l_efe_sysdate = 1 then
4423
4424 return l_eff_date;
4425
4426 else
4427 begin
4428 select max(s.effectivity_date) into l_max_date
4429 from bom_operation_sequences s
4430 where s.routing_sequence_id = p_routing_seq_id
4431 and s.operation_seq_num = p_oper_seq_num
4432 and s.effectivity_date < sysdate
4433 and s.operation_type = p_operation_type
4434 group by s.operation_seq_num ;
4435
4436 exception
4437 WHEN NO_DATA_FOUND THEN
4438 null;
4439 end;
4440
4441 if (l_max_date IS NOT NULL) then
4442 return l_max_date;
4443
4444 else
4445
4446 select min(s.effectivity_date) into l_min_date
4447 from bom_operation_sequences s
4448 where s.routing_sequence_id = p_routing_seq_id
4449 and s.operation_seq_num = p_oper_seq_num
4450 and s.effectivity_date > sysdate
4451 and s.operation_type = p_operation_type
4452 group by s.operation_seq_num;
4453
4454
4455 return l_min_date;
4456
4457 end if; --l_max_date not null
4458
4459 end if; --l_efe_sysdate = 0
4460
4461 end if; -- l_count= 1
4462
4463 END EFFECTIVE_DATE;
4464
4465 --********************************************************************************************
4466 --bug fix:7387499
4467 --*******************************************************************************************
4468
4469
4470 --***********************************************************************************************
4471 -- ==============================================================================================
4472 -- PROCEDURE return_att_quantity
4473 -- ==============================================================================================
4474 --***********************************************************************************************
4475
4476 PROCEDURE return_att_quantity(p_org_id IN number,
4477 p_item_id IN number,
4478 p_rev IN varchar2,
4479 p_lot_no IN varchar2,
4480 p_subinv IN varchar2,
4481 p_locator_id IN number,
4482 p_qoh OUT NOCOPY number,
4483 p_atr OUT NOCOPY number,
4484 p_att OUT NOCOPY number,
4485 p_err_code OUT NOCOPY number,
4486 p_err_msg OUT NOCOPY varchar2 ) IS
4487
4488 lv_return_status varchar2(20);
4489 lv_msg_count number := 0;
4490 lv_msg_data varchar2(4000);
4491 lv_tree_id number;
4492 lv_qoh number;
4493 lv_rqoh number;
4494 lv_qr number;
4495 lv_qs number;
4496 lv_att number;
4497 lv_atr number;
4498
4499 BEGIN
4500 --Bug 4567588:Tree mode is changed from reservation mode (3) to transaction
4501 --mode (2)
4502 inv_quantity_tree_pvt.create_tree(
4503 P_API_VERSION_NUMBER => 1.0
4504 , P_INIT_MSG_LST => 'T'
4505 , X_RETURN_STATUS => lv_return_status
4506 , X_MSG_COUNT => lv_msg_count
4507 , X_MSG_DATA => lv_msg_data
4508 , P_ORGANIZATION_ID => p_org_id
4509 , P_INVENTORY_ITEM_ID => p_item_id
4510 , P_TREE_MODE => 2 --3
4511 , P_IS_REVISION_CONTROL => (p_rev is not null)
4512 , P_IS_LOT_CONTROL => TRUE
4513 , P_IS_SERIAL_CONTROL => FALSE
4514 , P_ASSET_SUB_ONLY => FALSE
4515 , P_INCLUDE_SUGGESTION => FALSE
4516 , P_DEMAND_SOURCE_TYPE_ID => 13
4517 , P_DEMAND_SOURCE_HEADER_ID => -9999
4518 , P_DEMAND_SOURCE_LINE_ID => NULL
4519 , P_DEMAND_SOURCE_NAME => NULL
4520 , P_LOT_EXPIRATION_DATE => null
4521 , X_TREE_ID => lv_tree_id);
4522
4523 if( lv_return_status <> 'S' ) then
4524 fnd_message.set_name('INV', 'INV_ERR_CREATETREE');
4525 p_err_msg := fnd_message.get;
4526 p_err_code := -1;
4527 return;
4528 end if;
4529
4530 inv_quantity_tree_pvt.QUERY_TREE(
4531 P_API_VERSION_NUMBER => 1.0
4532 , P_INIT_MSG_LST => 'T'
4533 , X_RETURN_STATUS => lv_return_status
4534 , X_MSG_COUNT => lv_msg_count
4535 , X_MSG_DATA => lv_msg_data
4536 , P_TREE_ID => lv_tree_id
4537 , P_REVISION => p_rev
4538 , P_LOT_NUMBER => p_lot_no
4539 , P_SUBINVENTORY_CODE => p_subinv
4540 , P_LOCATOR_ID => p_locator_id
4541 , X_QOH => lv_qoh
4542 , X_RQOH => lv_rqoh
4543 , X_QR => lv_qr
4544 , X_QS => lv_qs
4545 , X_ATT => lv_att
4546 , X_ATR => lv_atr
4547 );
4548
4549
4550 if( lv_return_status <> 'S' ) then
4551 fnd_message.set_name('INV', 'INV-CANNOT QUERY TREE');
4552 p_err_msg := fnd_message.get;
4553 p_err_code := -1;
4554 return;
4555 end if;
4556
4557 p_qoh := lv_qoh;
4558 p_att := lv_att;
4559 --Bug 4567588
4560 p_atr := lv_att; --lv_atr;
4561
4562 inv_quantity_tree_pvt.free_all(
4563 p_api_version_number => 1.0
4564 , p_init_msg_lst => 'T'
4565 , x_return_status => lv_return_status
4566 , x_msg_count => lv_msg_count
4567 , x_msg_data => lv_msg_data
4568 );
4569
4570 IF(p_qoh = 0) THEN
4571 FND_MESSAGE.set_name('WSM','WSM_ZERO_ON_HAND');
4572 p_err_msg := fnd_message.get;
4573 p_err_code := -1;
4574 return;
4575 END IF;
4576
4577 IF p_atr = 0 THEN
4578 FND_MESSAGE.set_name('WSM','WSM_LOT_FULL_RESERVED');
4579 p_err_msg := fnd_message.get;
4580 p_err_code := -1;
4581 return;
4582 END IF;
4583
4584 EXCEPTION
4585 WHEN OTHERS THEN
4586 p_err_code := SQLCODE;
4587 p_err_msg := substr('WSMPUTIL.return_att_quantity :' ||sqlerrm, 1,2000);
4588 FND_FILE.PUT_LINE(FND_FILE.LOG, p_err_msg);
4589 return;
4590 END return_att_quantity;
4591
4592
4593
4594
4595 -- OSP FP I addition begin
4596 -- this function checks to see if the operation has an OSP resource
4597 -- attached to it
4598
4599 function check_osp_operation ( p_wip_entity_id IN NUMBER,
4600 p_operation_seq_num IN OUT NOCOPY NUMBER,
4601 p_organization_id IN NUMBER )
4602
4603
4604 return boolean is
4605
4606 l_op_seq_num number;
4607
4608 begin
4609
4610 l_op_seq_num := -1 ;
4611
4612 select unique wor.operation_seq_num
4613 into l_op_seq_num
4614 from wip_operation_resources wor
4615 where wor.organization_id = p_organization_id
4616 and wor.wip_entity_id = p_wip_entity_id
4617 and wor.operation_seq_num = nvl(p_operation_seq_num,wor.operation_seq_num)
4618 and wor.autocharge_type IN (WIP_CONSTANTS.PO_RECEIPT,
4619 WIP_CONSTANTS.PO_MOVE);
4620 p_operation_seq_num := l_op_seq_num ;
4621 return true;
4622
4623 exception
4624
4625 when no_data_found then
4626
4627 return false;
4628
4629 when too_many_rows then
4630
4631 return true;
4632
4633 -- when others then has been deliberately not written so that
4634 -- exception is thrown in the calling program. this means that
4635 -- x_error_code and x_error_msg out variables need not be
4636 -- defined here and passed back.
4637
4638 end check_osp_operation ;
4639
4640
4641 ------------------------------------------------------------
4642 -- FUNCTIONS THAT CHECK TXN and TXN INTERFACE TABLES
4643 ------------------------------------------------------------
4644
4645 /***************************************************************************************/
4646
4647 FUNCTION CHECK_WLMTI (
4648 p_wip_entity_id IN NUMBER,
4649 p_wip_entity_name IN VARCHAR2,
4650 p_header_id IN NUMBER,
4651 p_transaction_date IN DATE,
4652 x_err_code OUT NOCOPY NUMBER,
4653 x_err_msg OUT NOCOPY VARCHAR2,
4654 p_organization_id IN NUMBER
4655 )
4656 RETURN NUMBER
4657 IS
4658 l_rowcount NUMBER := 0;
4659 l_stmt_num NUMBER := 0;
4660
4661 BEGIN
4662 x_err_code := 0;
4663 x_err_msg := '';
4664 l_stmt_num := 10;
4665
4666 /***************************************************************
4667 -- Fixed bug #3453139: Stubbed out this procedure, since txns should not depend on interface records.
4668
4669 l_rowcount := 0;
4670
4671 IF (p_wip_entity_id IS NOT NULL) THEN
4672
4673 l_stmt_num := 20;
4674
4675 SELECT 1
4676 INTO l_rowcount
4677 From dual
4678 where exists (select 'Unprocessed WLMTI Record exists'
4679 FROM WSM_LOT_MOVE_TXN_INTERFACE WLMTI
4680 WHERE WLMTI.entity_type = 5
4681 AND WLMTI.wip_entity_id = p_wip_entity_id
4682 AND WLMTI.status IN (WIP_CONSTANTS.PENDING,
4683 WIP_CONSTANTS.RUNNING,
4684 WIP_CONSTANTS.ERROR)
4685 AND WLMTI.transaction_date <= p_transaction_date
4686 AND WLMTI.header_id <> p_header_id);
4687 -- Use of header_id here in WLMTI is useful to support BULK MOVE Txns
4688 -- Otherwise, I don't see any use for this. -- BBK.
4689
4690 IF (l_rowcount > 0 ) THEN
4691 RETURN l_rowcount;
4692 END IF;
4693
4694
4695 ELSIF (p_wip_entity_name IS NOT NULL) THEN
4696
4697 l_stmt_num := 30;
4698
4699 SELECT 1
4700 INTO l_rowcount
4701 From dual
4702 where exists (select 'Unprocessed WLMTI Record exists'
4703 FROM WSM_LOT_MOVE_TXN_INTERFACE WLMTI
4704 WHERE WLMTI.entity_type = 5
4705 AND WLMTI.wip_entity_name = p_wip_entity_name
4706 AND WLMTI.organization_id = decode(p_organization_id, 0, WLMTI.organization_id, p_organization_id)
4707 AND WLMTI.status IN (WIP_CONSTANTS. PENDING,
4708 WIP_CONSTANTS.RUNNING,
4709 WIP_CONSTANTS.ERROR)
4710 AND WLMTI.transaction_date <= p_transaction_date
4711 AND WLMTI.header_id <> p_header_id );
4712
4713
4714 IF (l_rowcount > 0 ) THEN
4715 NULL;
4716 RETURN l_rowcount;
4717 END IF;
4718
4719 END IF;
4720 ***************************************************************/
4721
4722 x_err_code := 0;
4723 x_err_msg := 'WSMPUTIL.CHECK_WLMTI:Success';
4724
4725 If (l_debug = 'Y') Then
4726 FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPUTIL.check_wlmti: Returned Success');
4727 End If;
4728
4729 RETURN l_rowcount;
4730
4731 EXCEPTION
4732 WHEN NO_DATA_FOUND THEN -- NO UNPROCESSED TXNS EXIST
4733 l_rowcount := 0;
4734 RETURN l_rowcount;
4735
4736 WHEN OTHERS THEN
4737 x_err_code := SQLCODE;
4738 x_err_msg := 'WSMPUTIL.check_wlmti(stmt_num='||l_stmt_num||' :'||SUBSTR(SQLERRM,1,1000);
4739 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
4740
4741 END CHECK_WLMTI;
4742
4743 --
4744 -- Overloaded function, org_id missing
4745 --
4746 FUNCTION CHECK_WLMTI (
4747 p_wip_entity_id IN NUMBER,
4748 p_wip_entity_name IN VARCHAR2,
4749 p_header_id IN NUMBER,
4750 p_transaction_date IN DATE,
4751 x_err_code OUT NOCOPY NUMBER,
4752 x_err_msg OUT NOCOPY VARCHAR2
4753 )
4754 RETURN NUMBER AS
4755
4756 l_organization_id NUMBER := 0;
4757 l_return_value NUMBER := 0;
4758
4759 BEGIN
4760
4761 x_err_code := 0;
4762 x_err_msg := '';
4763
4764 /***************************************************************
4765 -- Fixed bug #3453139: Stubbed out this procedure, since txns should not depend on interface records.
4766
4767 l_return_value := check_wlmti( p_wip_entity_id => p_wip_entity_id
4768 , p_wip_entity_name => p_wip_entity_name
4769 , p_header_id => p_header_id
4770 , p_transaction_date => p_transaction_date
4771 , x_err_code => x_err_code
4772 , x_err_msg => x_err_msg
4773 , p_organization_id => l_organization_id
4774 );
4775 ***************************************************************/
4776
4777 return l_return_value;
4778
4779 END CHECK_WLMTI;
4780
4781
4782 /***************************************************************************************/
4783
4784 -- Moved this procedure from WSMPLOAD to here
4785 FUNCTION CHECK_WMTI
4786 (
4787 p_wip_entity_id IN NUMBER,
4788 p_wip_entity_name IN VARCHAR2,
4789 p_transaction_date IN DATE,
4790 x_err_code OUT NOCOPY NUMBER,
4791 x_err_msg OUT NOCOPY VARCHAR2,
4792 p_organization_id IN NUMBER
4793 )
4794 RETURN NUMBER
4795 IS
4796 l_stmt_num NUMBER := 0;
4797 l_rowcount NUMBER := 0;
4798
4799 BEGIN
4800 x_err_code := 0;
4801 x_err_msg := '';
4802 l_stmt_num := 10;
4803
4804 /***************************************************************
4805 -- Fixed bug #3453139: Stubbed out this procedure, since only online moves are now supported
4806
4807 l_rowcount := 0;
4808
4809 IF (p_wip_entity_id IS NOT NULL) THEN
4810
4811 l_stmt_num := 20;
4812
4813 SELECT 1
4814 INTO l_rowcount
4815 From dual
4816 where exists (select 'Unprocessed WMTI Record exists'
4817 FROM WIP_MOVE_TXN_INTERFACE WMTI
4818 WHERE WMTI.entity_type = 5
4819 AND WMTI.wip_entity_id = p_wip_entity_id
4820 AND WMTI.process_status IN (WIP_CONSTANTS.PENDING,
4821 WIP_CONSTANTS.RUNNING,
4822 WIP_CONSTANTS.ERROR)
4823 AND WMTI.transaction_date < nvl(p_transaction_date, SYSDATE)
4824 ); -- So that it doesn't pick up itself
4825
4826
4827 IF (l_rowcount > 0 ) THEN
4828 x_err_msg := 'WSMPUTIL.check_wmti('||l_stmt_num||') : Returning error';
4829 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
4830 RETURN l_rowcount;
4831 END IF;
4832
4833 ELSIF (p_wip_entity_name IS NOT NULL) THEN
4834
4835 l_stmt_num := 60;
4836
4837 SELECT 1
4838 INTO l_rowcount
4839 From dual
4840 where exists (select 'Unprocessed WMTI Record exists'
4841 FROM WIP_MOVE_TXN_INTERFACE WMTI
4842 WHERE WMTI.entity_type = 5
4843 AND WMTI.wip_entity_name = p_wip_entity_name
4844 AND WMTI.organization_id = decode(p_organization_id, 0, WMTI.organization_id, p_organization_id)
4845 AND WMTI.process_status IN (WIP_CONSTANTS.PENDING,
4846 WIP_CONSTANTS.RUNNING,
4847 WIP_CONSTANTS.ERROR)
4848 AND WMTI.transaction_date < nvl(p_transaction_date, SYSDATE)
4849 );
4850
4851
4852 IF (l_rowcount > 0 ) THEN
4853 x_err_msg := 'WSMPUTIL.check_wmti('||l_stmt_num||') : Returning error';
4854 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
4855 RETURN l_rowcount;
4856 END IF;
4857
4858 END IF;
4859
4860 ***************************************************************/
4861
4862 x_err_code := 0;
4863 x_err_msg := 'WSMPUTIL.CHECK_WMTI:Returned Success';
4864 IF (l_debug = 'Y') THEN
4865 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
4866 END IF;
4867 RETURN 0;
4868
4869 EXCEPTION
4870 WHEN NO_DATA_FOUND THEN
4871 l_rowcount := 0;
4872 return l_rowcount;
4873
4874 WHEN OTHERS THEN
4875 x_err_code := SQLCODE;
4876 x_err_msg := 'WSMPUTIL.check_wmti('||l_stmt_num||') :'||SUBSTR(SQLERRM,1,1000);
4877 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
4878 return 1; -- return a nonzero value.
4879
4880 END CHECK_WMTI;
4881
4882 --
4883 -- Overloaded function, org_id missing
4884 --
4885 FUNCTION CHECK_WMTI
4886 (
4887 p_wip_entity_id IN NUMBER,
4888 p_wip_entity_name IN VARCHAR2,
4889 p_transaction_date IN DATE,
4890 x_err_code OUT NOCOPY NUMBER,
4891 x_err_msg OUT NOCOPY VARCHAR2
4892 )
4893 RETURN NUMBER
4894 AS
4895
4896 l_organization_id NUMBER := 0;
4897 l_return_value NUMBER := 0;
4898
4899 BEGIN
4900
4901 x_err_code := 0;
4902 x_err_msg := NULL;
4903
4904 /***************************************************************
4905 -- Fixed bug #3453139: Stubbed out this procedure, since only online moves are now supported
4906
4907 l_return_value := check_wmti(p_wip_entity_id => p_wip_entity_id
4908 , p_wip_entity_name => p_wip_entity_name
4909 , p_transaction_date => p_transaction_date
4910 , x_err_code => x_err_code
4911 , x_err_msg => x_err_msg
4912 , p_organization_id => l_organization_id
4913 );
4914 ***************************************************************/
4915
4916 return l_return_value;
4917
4918
4919 END CHECK_WMTI;
4920
4921
4922 /***************************************************************************************/
4923
4924 -- Moved this procedure from WSMPLOAD to here
4925 FUNCTION CHECK_WSMT
4926 (
4927 p_wip_entity_id IN NUMBER,
4928 p_wip_entity_name IN VARCHAR2,
4929 p_transaction_id IN NUMBER,
4930 p_transaction_date IN DATE,
4931 x_err_code OUT NOCOPY NUMBER,
4932 x_err_msg OUT NOCOPY VARCHAR2,
4933 p_organization_id IN NUMBER
4934 )
4935 RETURN NUMBER
4936 IS
4937
4938 l_sj_rowcount NUMBER := 0;
4939 l_rj_rowcount NUMBER := 0;
4940 l_stmt_num NUMBER := 0;
4941
4942 BEGIN
4943
4944 x_err_code := 0;
4945 x_err_msg := '';
4946 l_stmt_num := 10;
4947
4948 /***************************************************************
4949 -- Fixed bug #3453139: Stubbed out this procedure, since it should not be called from anywhere.
4950
4951 if l_debug = 'Y' then
4952 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_wip_entity_id ='||p_wip_entity_id);
4953 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_transaction_id ='||p_transaction_id);
4954 end if;
4955
4956
4957 l_sj_rowcount := 0;
4958 l_rj_rowcount := 0;
4959
4960 IF (p_wip_entity_id IS NOT NULL) THEN
4961
4962 l_stmt_num := 20;
4963
4964 IF(p_transaction_id IS NULL) THEN
4965 l_stmt_num := 30;
4966
4967 Begin
4968
4969 SELECT 1
4970 INTO l_sj_rowcount
4971 FROM dual
4972 WHERE exists (select 'Unprocessed WSMT Record exists'
4973 FROM WSM_SM_STARTING_JOBS WSSJ,
4974 WSM_SPLIT_MERGE_TRANSACTIONS WSMT
4975 WHERE
4976 WSSJ.wip_entity_id = p_wip_entity_id
4977 AND WSMT.transaction_id = WSSJ.transaction_id
4978 AND WSMT.status IN (WIP_CONSTANTS.PENDING,
4979 WIP_CONSTANTS.RUNNING,
4980 WIP_CONSTANTS.ERROR)
4981 AND WSMT.transaction_date <= nvl(p_transaction_date,SYSDATE)
4982 );
4983
4984 EXCEPTION
4985 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
4986 NULL;
4987 End;
4988
4989 ELSE
4990
4991 l_stmt_num := 50;
4992
4993 Begin
4994
4995 SELECT 1
4996 INTO l_sj_rowcount
4997 FROM dual
4998 WHERE exists (select 'Unprocessed WSSJ/WSMT Record exists'
4999 FROM WSM_SM_STARTING_JOBS WSSJ,
5000 WSM_SPLIT_MERGE_TRANSACTIONS WSMT
5001 WHERE
5002 WSSJ.wip_entity_id = p_wip_entity_id
5003 AND WSMT.transaction_id = WSSJ.transaction_id
5004 AND WSMT.status IN (WIP_CONSTANTS.PENDING,
5005 WIP_CONSTANTS.RUNNING,
5006 WIP_CONSTANTS.ERROR)
5007 AND WSMT.transaction_date <= nvl(p_transaction_date,SYSDATE)
5008 AND WSMT.transaction_id <> p_transaction_id
5009 );
5010
5011 EXCEPTION
5012 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5013 NULL;
5014 End;
5015
5016 END IF;
5017
5018
5019 IF (l_sj_rowcount > 0 ) THEN
5020 x_err_msg := 'WSMPUTIL.check_wsmt('||l_stmt_num||') : Returning error';
5021 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5022 RETURN l_sj_rowcount;
5023 END IF;
5024
5025 l_stmt_num := 90;
5026
5027 IF (p_transaction_id IS NULL) THEN
5028 l_stmt_num := 100;
5029
5030 Begin
5031
5032 SELECT 1
5033 INTO l_rj_rowcount
5034 FROM dual
5035 WHERE exists (select 'Unprocessed WSRJ/WSMT Record exists'
5036 FROM WSM_SM_RESULTING_JOBS WSRJ,
5037 WSM_SPLIT_MERGE_TRANSACTIONS WSMT
5038 WHERE
5039 WSRJ.wip_entity_id = p_wip_entity_id
5040 AND WSMT.transaction_id = WSRJ.transaction_id
5041 AND WSMT.status IN (WIP_CONSTANTS.PENDING,
5042 WIP_CONSTANTS.RUNNING,
5043 WIP_CONSTANTS.ERROR)
5044 AND WSMT.transaction_date <= nvl(p_transaction_date,SYSDATE)
5045 );
5046
5047 EXCEPTION
5048 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5049 NULL;
5050 End;
5051 ELSE
5052
5053 l_stmt_num := 120;
5054
5055 Begin
5056
5057 SELECT 1
5058 INTO l_rj_rowcount
5059 FROM dual
5060 WHERE exists (select 'Unprocessed WSRJ/WSMT Record exists'
5061 FROM WSM_SM_RESULTING_JOBS WSRJ,
5062 WSM_SPLIT_MERGE_TRANSACTIONS WSMT
5063 WHERE
5064 WSRJ.wip_entity_id = p_wip_entity_id
5065 AND WSMT.transaction_id = WSRJ.transaction_id
5066 AND WSMT.status IN (WIP_CONSTANTS.PENDING,
5067 WIP_CONSTANTS.RUNNING,
5068 WIP_CONSTANTS.ERROR)
5069 AND WSMT.transaction_date <= nvl(p_transaction_date,SYSDATE)
5070 AND WSMT.transaction_id <> p_transaction_id
5071 );
5072 EXCEPTION
5073 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5074 NULL;
5075 End;
5076
5077 END IF;
5078
5079 IF (l_rj_rowcount > 0 ) THEN
5080 x_err_msg := 'WSMPUTIL.check_wsmt('||l_stmt_num||') : Returning error';
5081 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5082 RETURN l_rj_rowcount;
5083 END IF;
5084
5085 l_stmt_num := 160;
5086
5087 ELSIF (p_wip_entity_name IS NOT NULL) THEN
5088
5089 l_sj_rowcount := 0;
5090 l_rj_rowcount := 0;
5091
5092 l_stmt_num := 180;
5093
5094 IF(p_transaction_id IS NULL) THEN
5095 l_stmt_num := 190;
5096
5097 Begin
5098
5099 SELECT 1
5100 INTO l_rj_rowcount
5101 FROM dual
5102 WHERE exists (select 'Unprocessed WSRJ/WSMT Record exists'
5103 FROM WSM_SM_RESULTING_JOBS WSRJ,
5104 WSM_SPLIT_MERGE_TRANSACTIONS WSMT
5105 WHERE
5106 WSRJ.wip_entity_name = p_wip_entity_name
5107 AND WSMT.organization_id = decode(p_organization_id,
5108 0, WSMT.organization_id, p_organization_id)
5109 AND WSMT.transaction_id = WSRJ.transaction_id
5110 AND WSMT.status IN (WIP_CONSTANTS.PENDING,
5111 WIP_CONSTANTS.RUNNING,
5112 WIP_CONSTANTS.ERROR)
5113 AND WSMT.transaction_date <= nvl(p_transaction_date, SYSDATE)
5114 );
5115
5116 EXCEPTION
5117 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5118 NULL;
5119 End;
5120
5121 ELSE
5122
5123 l_stmt_num := 210;
5124
5125 Begin
5126
5127 SELECT 1
5128 INTO l_rj_rowcount
5129 FROM dual
5130 WHERE exists (select 'Unprocessed WSRJ/WSMT Record exists'
5131 FROM WSM_SM_RESULTING_JOBS WSRJ,
5132 WSM_SPLIT_MERGE_TRANSACTIONS WSMT
5133 WHERE
5134 WSRJ.wip_entity_name = p_wip_entity_name
5135 AND WSMT.organization_id = decode(p_organization_id,
5136 0, WSMT.organization_id, p_organization_id)
5137 AND WSMT.transaction_id = WSRJ.transaction_id
5138 AND WSMT.status IN (WIP_CONSTANTS.PENDING,
5139 WIP_CONSTANTS.RUNNING,
5140 WIP_CONSTANTS.ERROR)
5141 AND WSMT.transaction_date <= nvl(p_transaction_date, SYSDATE)
5142 AND WSMT.transaction_id <> p_transaction_id
5143 );
5144 EXCEPTION
5145 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5146 NULL;
5147 End;
5148
5149 END IF;
5150
5151 IF (l_rj_rowcount > 0 ) THEN
5152 x_err_msg := 'WSMPUTIL.check_wsmt('||l_stmt_num||') : Returning error';
5153 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5154 RETURN l_rj_rowcount;
5155 END IF;
5156
5157 END IF;
5158
5159 x_err_code := 0;
5160 x_err_msg := 'WSMPUTIL.CHECK_WSMT:Returned Success';
5161 IF (l_debug = 'Y') THEN
5162 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5163 END IF;
5164
5165 ***************************************************************/
5166
5167 RETURN 0;
5168
5169 EXCEPTION
5170
5171 WHEN OTHERS THEN
5172 x_err_code := SQLCODE;
5173 x_err_msg := 'WSMPUTIL.CHECK_WSMT' ||'(stmt_num='||l_stmt_num||') : '||substrb(sqlerrm, 1,1000);
5174 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5175 return 1; -- return a nonzerovalue.
5176
5177 END CHECK_WSMT;
5178
5179
5180 --
5181 -- Overloaded function, org_id missing
5182 --
5183 FUNCTION CHECK_WSMT
5184 (
5185 p_wip_entity_id IN NUMBER,
5186 p_wip_entity_name IN VARCHAR2,
5187 p_transaction_id IN NUMBER,
5188 p_transaction_date IN DATE,
5189 x_err_code OUT NOCOPY NUMBER,
5190 x_err_msg OUT NOCOPY VARCHAR2
5191 )
5192 RETURN NUMBER
5193 IS
5194
5195 l_organization_id NUMBER := 0;
5196 l_return_value NUMBER := 0;
5197
5198 BEGIN
5199
5200 x_err_code := 0;
5201 x_err_msg := NULL;
5202
5203 /***************************************************************
5204 -- Fixed bug #3453139: Stubbed out this procedure, since it should not be called from anywhere.
5205
5206 l_return_value := check_wsmt(p_wip_entity_id => p_wip_entity_id
5207 , p_wip_entity_name => p_wip_entity_name
5208 , p_transaction_id => p_transaction_id
5209 , p_transaction_date => p_transaction_date
5210 , x_err_code => x_err_code
5211 , x_err_msg => x_err_msg
5212 , p_organization_id => l_organization_id
5213 );
5214
5215 ***************************************************************/
5216
5217 return l_return_value;
5218
5219 END CHECK_WSMT;
5220
5221 /***************************************************************************************/
5222
5223 -- Check WIP MOVE TXN for a LATER Txn already registered for a job.
5224 FUNCTION CHECK_WMT (
5225 x_err_code OUT NOCOPY NUMBER
5226 , x_err_msg OUT NOCOPY VARCHAR2
5227 , p_wip_entity_id IN NUMBER
5228 , p_wip_entity_name IN VARCHAR2
5229 , p_organization_id IN NUMBER
5230 , p_transaction_date IN DATE
5231 )
5232 RETURN NUMBER
5233 IS
5234 l_stmt_num NUMBER := 0;
5235 l_rowcount NUMBER := 0;
5236
5237 BEGIN
5238 x_err_code := 0;
5239 x_err_msg := '';
5240 l_stmt_num := 10;
5241
5242 /***************************************************************
5243 -- Fixed bug #3453139: Stubbed out this procedure, since it is not called from anywhere.
5244
5245 l_rowcount := 0;
5246
5247 IF (p_wip_entity_id IS NOT NULL) THEN
5248
5249 l_stmt_num := 20;
5250
5251 -- Processed WMT Record exists with a Later Txn Date
5252 SELECT 1
5253 INTO l_rowcount
5254 FROM WIP_MOVE_TRANSACTIONS WMT
5255 WHERE WMT.wip_entity_id = p_wip_entity_id
5256 AND WMT.transaction_date > nvl(p_transaction_date, SYSDATE)
5257 AND rownum = 1;
5258
5259
5260 IF (l_rowcount > 0 ) THEN
5261 x_err_msg := 'WSMPUTIL.check_wmt('||l_stmt_num||') : error: Move Txn with a later txn date found.';
5262 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5263 RETURN l_rowcount;
5264 END IF;
5265
5266 ELSIF (p_wip_entity_name IS NOT NULL) THEN
5267
5268 l_stmt_num := 60;
5269
5270 -- Processed WMT Record exists with a Later Txn Date
5271 SELECT 1
5272 INTO l_rowcount
5273 FROM WIP_MOVE_TRANSACTIONS WMT, WIP_ENTITIES WE
5274 WHERE WMT.wip_entity_id = we.wip_entity_id
5275 AND we.wip_entity_name = p_wip_entity_name
5276 AND we.organization_id = p_organization_id
5277 AND WMT.transaction_date > nvl(p_transaction_date, SYSDATE)
5278 AND rownum = 1;
5279
5280
5281 IF (l_rowcount > 0 ) THEN
5282 x_err_msg := 'WSMPUTIL.check_wmt('||l_stmt_num||') : error: Move Txn with a later txn date found.';
5283 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5284 RETURN l_rowcount;
5285 END IF;
5286
5287 END IF;
5288
5289
5290 x_err_code := 0;
5291 ***************************************************************/
5292 RETURN 0;
5293
5294 EXCEPTION
5295 WHEN NO_DATA_FOUND THEN
5296 l_rowcount := 0;
5297 x_err_msg := 'WSMPUTIL.CHECK_WMT:Returned Success';
5298 IF (l_debug = 'Y') THEN
5299 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5300 END IF;
5301 return l_rowcount;
5302
5303 WHEN OTHERS THEN
5304 x_err_code := SQLCODE;
5305 x_err_msg := 'WSMPUTIL.check_wmt('||l_stmt_num||') :'||SUBSTR(SQLERRM,1,1000);
5306 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5307 return 1; -- return a nonzero value.
5308
5309 END CHECK_WMT;
5310
5311
5312 /***************************************************************************************/
5313
5314 FUNCTION CHECK_WSMTI
5315 (
5316 x_err_code OUT NOCOPY NUMBER,
5317 x_err_msg OUT NOCOPY VARCHAR2,
5318 p_wip_entity_id IN NUMBER,
5319 p_wip_entity_name IN VARCHAR2,
5320 p_organization_id IN NUMBER,
5321 p_transaction_date IN DATE
5322 )
5323 RETURN NUMBER
5324 IS
5325
5326 l_sj_rowcount NUMBER := 0;
5327 l_rj_rowcount NUMBER := 0;
5328 l_stmt_num NUMBER := 0;
5329
5330 l_organization_id NUMBER := 0;
5331 l_wip_entity_name WIP_ENTITIES.WIP_ENTITY_NAME%TYPE;
5332
5333 BEGIN
5334
5335 x_err_code := 0;
5336 x_err_msg := '';
5337 l_stmt_num := 10;
5338
5339 /***************************************************************
5340 -- Fixed bug #3453139: Stubbed out this procedure, since it is not called from anywhere.
5341
5342 if l_debug = 'Y' then
5343 FND_FILE.PUT_LINE(FND_FILE.LOG, 'WSMPUTIL.check_wsmti('||l_stmt_num||') Input parameters are ...');
5344 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_wip_entity_id ='||p_wip_entity_id);
5345 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_wip_entity_name ='||p_wip_entity_name);
5346 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_organization_id ='||p_organization_id);
5347 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_transaction_date ='||to_char(p_transaction_date, 'DD-MON-YYYY HH24:MI:SS'));
5348 end if;
5349
5350 l_sj_rowcount := 0;
5351 l_rj_rowcount := 0;
5352
5353 IF (p_wip_entity_id IS NOT NULL) THEN
5354
5355 l_stmt_num := 20;
5356
5357 Begin
5358
5359 SELECT 1
5360 INTO l_sj_rowcount
5361 FROM dual
5362 WHERE exists (select 'Unprocessed WSJI/WSMTI Record exists'
5363 FROM WSM_STARTING_JOBS_INTERFACE WSJI,
5364 WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
5365 WHERE WSJI.wip_entity_id = p_wip_entity_id
5366 AND WSMTI.header_id = WSJI.header_id
5367 AND WSMTI.process_status IN (WIP_CONSTANTS.PENDING,
5368 WIP_CONSTANTS.RUNNING,
5369 WIP_CONSTANTS.ERROR)
5370 AND WSMTI.transaction_date <= nvl(p_transaction_date,SYSDATE)
5371 );
5372
5373 EXCEPTION
5374 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5375 NULL;
5376 End;
5377
5378 IF (l_sj_rowcount > 0 ) THEN
5379 x_err_msg := 'WSMPUTIL.check_wsmti('||l_stmt_num||') : Returning error - Unprocessed earlier WSJI Txn';
5380 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5381 RETURN l_sj_rowcount;
5382
5383 END IF;
5384
5385 l_stmt_num := 30;
5386
5387 select wip_entity_name, organization_id
5388 into l_wip_entity_name, l_organization_id
5389 from wip_entities
5390 Where wip_entity_id = p_wip_entity_id;
5391
5392 l_stmt_num := 40;
5393
5394 Begin
5395
5396 SELECT 1
5397 INTO l_rj_rowcount
5398 FROM dual
5399 WHERE exists (select 'Unprocessed WRJI/WSMTI Record exists'
5400 FROM WSM_RESULTING_JOBS_INTERFACE WRJI,
5401 WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
5402 WHERE WRJI.wip_entity_name = l_wip_entity_name
5403 AND WSMTI.organization_id = l_organization_id
5404 AND WSMTI.header_id = WRJI.header_id
5405 AND WSMTI.process_status IN (WIP_CONSTANTS.PENDING,
5406 WIP_CONSTANTS.RUNNING,
5407 WIP_CONSTANTS.ERROR)
5408 AND WSMTI.transaction_date <= nvl(p_transaction_date,SYSDATE)
5409 );
5410
5411 EXCEPTION
5412 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5413 NULL;
5414 End;
5415
5416 IF (l_rj_rowcount > 0 ) THEN
5417 x_err_msg := 'WSMPUTIL.check_wsmti('||l_stmt_num||') : Returning error - Unprocessed earlier WRJI Txn';
5418 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5419 RETURN l_rj_rowcount;
5420 END IF;
5421
5422 ELSIF (p_wip_entity_name IS NOT NULL) THEN
5423
5424 l_sj_rowcount := 0;
5425 l_rj_rowcount := 0;
5426
5427 l_stmt_num := 50;
5428
5429
5430 Begin
5431
5432 SELECT 1
5433 INTO l_sj_rowcount
5434 FROM dual
5435 WHERE exists (select 'Unprocessed WSJI/WSMTI Record exists'
5436 FROM WSM_STARTING_JOBS_INTERFACE WSJI,
5437 WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
5438 WHERE WSJI.wip_entity_name = p_wip_entity_name
5439 AND WSMTI.organization_id = p_organization_id
5440 AND WSMTI.header_id = WSJI.header_id
5441 AND WSMTI.process_status IN (WIP_CONSTANTS.PENDING,
5442 WIP_CONSTANTS.RUNNING,
5443 WIP_CONSTANTS.ERROR)
5444 AND WSMTI.transaction_date <= nvl(p_transaction_date,SYSDATE)
5445 );
5446
5447 EXCEPTION
5448 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5449 NULL;
5450 End;
5451
5452 IF (l_sj_rowcount > 0 ) THEN
5453 x_err_msg := 'WSMPUTIL.check_wsmti('||l_stmt_num||') : Returning error - Unprocessed earlier WSJI Txn';
5454 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5455 RETURN l_sj_rowcount;
5456
5457 END IF;
5458
5459 l_stmt_num := 60;
5460
5461 Begin
5462
5463 SELECT 1
5464 INTO l_rj_rowcount
5465 FROM dual
5466 WHERE exists (select 'Unprocessed WRJI/WSMTI Record exists'
5467 FROM WSM_RESULTING_JOBS_INTERFACE WRJI,
5468 WSM_SPLIT_MERGE_TXN_INTERFACE WSMTI
5469 WHERE WRJI.wip_entity_name = p_wip_entity_name
5470 AND WSMTI.organization_id = p_organization_id
5471 AND WSMTI.header_id = WRJI.header_id
5472 AND WSMTI.process_status IN (WIP_CONSTANTS.PENDING,
5473 WIP_CONSTANTS.RUNNING,
5474 WIP_CONSTANTS.ERROR)
5475 AND WSMTI.transaction_date <= nvl(p_transaction_date,SYSDATE)
5476 );
5477
5478 EXCEPTION
5479 WHEN NO_DATA_FOUND THEN -- No UNPROCESSED Txns exist
5480 NULL;
5481 End;
5482
5483 IF (l_rj_rowcount > 0 ) THEN
5484 x_err_msg := 'WSMPUTIL.check_wsmti('||l_stmt_num||') : Returning error - Unprocessed earlier WRJI Txn';
5485 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5486 RETURN l_rj_rowcount;
5487 END IF;
5488
5489 END IF;
5490
5491 x_err_code := 0;
5492 x_err_msg := 'WSMPUTIL.CHECK_WSMTI:Returned Success - No Unprocessed WSMTI Txns for this lot';
5493 IF (l_debug = 'Y') THEN
5494 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5495 END IF;
5496
5497 ***************************************************************/
5498
5499 RETURN 0;
5500
5501 EXCEPTION
5502
5503 WHEN OTHERS THEN
5504 x_err_code := SQLCODE;
5505 x_err_msg := 'WSMPUTIL.CHECK_WSMTI' ||'(stmt_num='||l_stmt_num||') : '||substrb(sqlerrm, 1,1000);
5506 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5507 return 1; -- return a nonzerovalue.
5508
5509 END CHECK_WSMTI;
5510
5511 /***************************************************************************************/
5512
5513 --------------------------------------------------------------------
5514 -- New Procedures/Functions added for DMF_PF.J or 11.5.10 ----------
5515 --------------------------------------------------------------------
5516 -- Import Network Routing Support through BOM Interface ----------
5517 --------------------------------------------------------------------
5518 -- Bug#/Project: FP.J Import Network Rtg - 3088690
5519 -- New/Overloaded: New
5520 -- Release : 11.5.10.
5521 -- Backward Compatible: YES
5522 -- Modified by: Bala Balakumar.
5523 --------------------------------------------------------------------
5524 FUNCTION JOBS_WITH_QTY_AT_FROM_OP(
5525 x_err_code OUT NOCOPY NUMBER
5526 , x_err_msg OUT NOCOPY varchar2
5527 , p_operation_sequence_id IN NUMBER
5528 )
5529 RETURN BOOLEAN IS
5530
5531 l_stmt_num NUMBER := 0;
5532 l_count NUMBER := 0;
5533
5534 Begin
5535
5536 If fnd_profile.value('WSM_CREATE_LBJ_COPY_ROUTING') = WIP_CONSTANTS.YES Then
5537
5538 Return FALSE;
5539
5540 End If;
5541
5542
5543 l_stmt_num := 10;
5544
5545 Select 1 into l_count
5546 From dual
5547 Where Exists ( Select 'Jobs with Qty At this Operation Exists'
5548 from wip_discrete_jobs wdj
5549 , wip_operations wo
5550 Where wdj.wip_entity_id = wo.wip_entity_id
5551 and NVL(wo.operation_sequence_id, -99999) =
5552 WSMPUTIL.replacement_op_seq_id (p_operation_sequence_id
5553 , wdj.routing_revision_date)
5554 and wdj.status_type = WIP_CONSTANTS.RELEASED
5555 and (
5556 wo.quantity_in_queue <> 0
5557 OR wo.quantity_running <> 0
5558 OR wo.quantity_waiting_to_move <> 0
5559 ));
5560
5561 If l_count <> 0 Then
5562 RETURN TRUE;
5563 Else
5564 RETURN FALSE;
5565 End If;
5566
5567 EXCEPTION
5568 WHEN NO_DATA_FOUND Then
5569 return FALSE;
5570
5571 -- WHEN OTHERS Exception should not be here.
5572 -- This should be handled by the calling program.
5573
5574 END JOBS_WITH_QTY_AT_FROM_OP;
5575
5576
5577 --------------------------------------------------------------------
5578 -- Bug#/Project: FP.J Import Network Rtg - 3088690
5579 -- New/Overloaded: New and Overloaded
5580 -- Release : 11.5.10.
5581 -- Backward Compatible: YES
5582 -- Modified by: Bala Balakumar.
5583 --------------------------------------------------------------------
5584 FUNCTION JOBS_WITH_QTY_AT_FROM_OP(
5585 x_err_code OUT NOCOPY NUMBER
5586 , x_err_msg OUT NOCOPY varchar2
5587 , p_routing_sequence_id IN NUMBER
5588 , p_operation_seq_num IN NUMBER
5589 )
5590 RETURN BOOLEAN IS
5591
5592 l_stmt_num NUMBER := 0;
5593 l_count NUMBER := 0;
5594
5595 Begin
5596
5597 If fnd_profile.value('WSM_CREATE_LBJ_COPY_ROUTING') = WIP_CONSTANTS.YES Then
5598
5599 Return FALSE;
5600
5601 End If;
5602
5603 l_stmt_num := 10;
5604
5605 Select 1 into l_count
5606 From dual
5607 Where Exists (
5608 Select 'Jobs with Qty At this Operation Exists'
5609 from bom_operation_sequences bos
5610 , wip_discrete_jobs wdj
5611 , wip_operations wo
5612 Where wdj.common_routing_sequence_id = p_routing_sequence_id
5613 and wdj.status_type = WIP_CONSTANTS.RELEASED
5614 and bos.routing_sequence_id = wdj.common_routing_sequence_id
5615 and bos.operation_seq_num = p_operation_seq_num
5616 and wdj.routing_revision_date between
5617 bos.effectivity_date and
5618 NVL(bos.disable_date, (wdj.routing_revision_date+1))
5619 and wo.wip_entity_id = wdj.wip_entity_id
5620 and wo.operation_sequence_id = bos.operation_sequence_id
5621 and (wo.quantity_in_queue <> 0
5622 OR wo.quantity_running <> 0
5623 OR wo.quantity_waiting_to_move <> 0
5624 ));
5625
5626 If l_count <> 0 Then
5627 RETURN TRUE;
5628 Else
5629 RETURN FALSE;
5630 End If;
5631
5632 EXCEPTION
5633 WHEN NO_DATA_FOUND Then
5634 return FALSE;
5635
5636 -- WHEN OTHERS Exception should not be here.
5637 -- This should be handled by the calling program.
5638
5639
5640 END JOBS_WITH_QTY_AT_FROM_OP;
5641
5642 --------------------------------------------------------------------
5643 -- Bug#/Project: FP.J OSFM/APS P2 Integration
5644 -- New/Overloaded: New
5645 -- Release : 11.5.10.
5646 -- Backward Compatible: YES
5647 -- Modified by: Bala Balakumar.
5648 --------------------------------------------------------------------
5649 FUNCTION CREATE_LBJ_COPY_RTG_PROFILE
5650 RETURN NUMBER IS
5651
5652 l_mfg_org_id varchar2(20);
5653 l_return_value NUMBER := WIP_CONSTANTS.NO;
5654
5655 BEGIN
5656
5657 /****************** I M P O R T A N T ********************************/
5658 -- YOU SHOULD UNCOMMENT THE NEXT LINE AFTER FP.J UT/ST ---------------
5659 return to_number(fnd_profile.value('WSM_CREATE_LBJ_COPY_ROUTING'));
5660 ----------------------------------------------------------------------
5661
5662 /****************** I M P O R T A N T ********************************/
5663 /***** Following code should be commented out after UT/ST for FP.J ***
5664 ----------------------------------------------------------------------
5665
5666 l_mfg_org_id := fnd_profile.value_specific(
5667 NAME => 'MFG_ORGANIZATION_ID'
5668 , USER_ID => FND_GLOBAL.user_id);
5669
5670
5671 FND_FILE.PUT_LINE(FND_FILE.LOG,
5672 ('User value is '|| to_char(FND_GLOBAL.user_id)
5673 ||', Org Id is '|| l_mfg_org_id)
5674 );
5675
5676 Select to_number(plan_code) into l_return_value
5677 from wsm_parameters
5678 where organization_id = to_number(l_mfg_org_id);
5679
5680 If l_return_value IN (WIP_CONSTANTS.YES, WIP_CONSTANTS.NO) Then
5681 return l_return_value;
5682 Else
5683 return WIP_CONSTANTS.NO;
5684 End If;
5685
5686 Exception
5687 When Others Then
5688 return WIP_CONSTANTS.NO;
5689
5690 ----------------------------------------------------------------------
5691 ************** UPTO HERE, THE CODE SHOULD BE COMMENTED OUT **********/
5692 ----------------------------------------------------------------------
5693
5694 END CREATE_LBJ_COPY_RTG_PROFILE;
5695
5696
5697 --------------------------------------------------------------------
5698 -- Bug#/Project: FP.J OSFM/APS P2 Integration
5699 -- New/Overloaded: New and OVERLOADED
5700 -- Release : 11.5.10.
5701 -- Backward Compatible: YES
5702 -- Modified by: Bala Balakumar.
5703 --------------------------------------------------------------------
5704 FUNCTION CREATE_LBJ_COPY_RTG_PROFILE
5705 (p_organization_id IN NUMBER)
5706 RETURN NUMBER IS
5707
5708 l_return_value NUMBER := WIP_CONSTANTS.NO;
5709 l_plan_code VARCHAR2(30);
5710
5711 BEGIN
5712
5713 -- Following is the strategy to be implemented in UT/ST/Cert/later for FP-J
5714 IF (WSMPUTIL.REFER_SITE_LEVEL_PROFILE = 'Y') THEN
5715 l_return_value := CREATE_LBJ_COPY_RTG_PROFILE;
5716 ELSE -- Refer to the org level setting
5717 select plan_code
5718 into l_plan_code
5719 from wsm_parameters
5720 where organization_id = p_organization_id;
5721
5722 IF (l_plan_code IS NULL) THEN -- retain the site level setting
5723 l_return_value := CREATE_LBJ_COPY_RTG_PROFILE;
5724 ELSE -- get the org-level setting
5725 l_return_value := to_number(l_plan_code);
5726 END IF;
5727 END IF;
5728 return l_return_value;
5729
5730 EXCEPTION
5731 WHEN OTHERS THEN
5732 return WIP_CONSTANTS.NO;
5733
5734 /****************** I M P O R T A N T ********************************/
5735 -- YOU SHOULD UNCOMMENT THE NEXT LINE AFTER FP.J UT/ST ---------------
5736 -- return to_number(fnd_profile.value('WSM_CREATE_LBJ_COPY_ROUTING'));
5737 ----------------------------------------------------------------------
5738
5739 /****************** I M P O R T A N T ********************************/
5740 /***** Following code should be commented out after UT/ST for FP.J ***
5741 ----------------------------------------------------------------------
5742
5743 Select to_number(plan_code) into l_return_value
5744 from wsm_parameters
5745 where organization_id = p_organization_id;
5746
5747 If l_return_value IN (WIP_CONSTANTS.YES, WIP_CONSTANTS.NO) Then
5748 return l_return_value;
5749 Else
5750 return WIP_CONSTANTS.NO;
5751 End If;
5752
5753 Exception
5754 When Others Then
5755 return WIP_CONSTANTS.NO;
5756
5757 ----------------------------------------------------------------------
5758 ************** UPTO HERE, THE CODE SHOULD BE COMMENTED OUT ************/
5759 ----------------------------------------------------------------------
5760
5761 END CREATE_LBJ_COPY_RTG_PROFILE;
5762
5763
5764 --------------------------------------------------------------------
5765 -- Bug#/Project: FP.J - Accounting Period consistent API
5766 -- New or Overloaded: New
5767 -- Release : 11.5.10.
5768 -- Backward Compatible: YES
5769 -- Modified by: Bala Balakumar.
5770 -- RETURN value of 0 indicates the date is in a non-open period.
5771 -- Exceptions should be handled by the calling programs.
5772 --------------------------------------------------------------------
5773 FUNCTION GET_INV_ACCT_PERIOD(
5774 x_err_code OUT NOCOPY NUMBER,
5775 x_err_msg OUT NOCOPY varchar2,
5776 p_organization_id IN NUMBER,
5777 p_date IN DATE) RETURN NUMBER IS
5778
5779 l_acct_period_id NUMBER := 0;
5780 l_open_past_period BOOLEAN := FALSE;
5781
5782 BEGIN
5783
5784 x_err_code := 0;
5785
5786 /* ST : Bug 3205363 Commented the following for LE Timezone change.*/
5787
5788 /*SELECT acct_period_id
5789 INTO l_acct_period_id
5790 FROM org_acct_periods
5791 WHERE organization_id = p_organization_id
5792 AND trunc(nvl(p_date, sysdate))
5793 between PERIOD_START_DATE and SCHEDULE_CLOSE_DATE
5794 AND period_close_date is NULL
5795 AND OPEN_FLAG = 'Y';*/
5796
5797 /* ST : Bug 3205363 LE Timezone change Start */
5798
5799 /* Henceforth call to be made to the tdacheck API to get the accounting period id */
5800
5801 INVTTMTX.tdatechk(org_id => p_organization_id,
5802 transaction_date => p_date,
5803 period_id => l_acct_period_id,
5804 open_past_period => l_open_past_period);
5805
5806 /* open_past_period : FALSE because the check is only for the transaction date to be in an open period.
5807 and not to check if it is in the current ( most recent ) open period */
5808
5809 if(l_acct_period_id = 0) or (l_acct_period_id = -1) then
5810 /*-------------------------------------------------------------+
5811 | 0 : No data found. |
5812 | -1 : some exception occured in the called API ...........|
5813 +-------------------------------------------------------------*/
5814 fnd_message.set_name('WSM', 'WSM_ACCT_PERIOD_NOT_OPEN');
5815 x_err_code := -1;
5816 x_err_msg := FND_MESSAGE.GET;
5817 IF (l_debug = 'Y') THEN -- bug 3373637
5818 fnd_file.put_line(fnd_file.log, 'WSMPUTIL.GET_INV_ACCT_PERIOD: '||x_err_msg
5819 || ' (organization_id = ' || p_organization_id || ')');
5820 END IF;
5821 l_acct_period_id := 0;
5822 end if;
5823
5824 /* ST : Bug 3205363 LE Timezone change End */
5825
5826 Return l_acct_period_id;
5827
5828 EXCEPTION
5829
5830 /* ST : Bug 3205363 Commented the following for LE Time zone change */
5831 /*WHEN NO_DATA_FOUND then
5832 x_err_code := -1;
5833 fnd_message.set_name('WSM', 'WSM_ACCT_PERIOD_NOT_OPEN');
5834 x_err_msg := FND_MESSAGE.GET;
5835 fnd_file.put_line(fnd_file.log, 'WSMPUTIL.GET_INV_ACCT_PERIOD: '||x_err_msg);
5836 l_acct_period_id := 0; -- Date passed is in a NON-OPEN Period.
5837 Return l_acct_period_id;*/
5838
5839 WHEN OTHERS THEN
5840 x_err_code := SQLCODE;
5841 x_err_msg := 'WSMPUTIL.GET_INV_ACCT_PERIOD: ' || substrb(sqlerrm, 1,1000);
5842 FND_FILE.PUT_LINE(FND_FILE.LOG, x_err_msg);
5843 l_acct_period_id := 0; -- Date passed is in a NON-OPEN Period.
5844 Return l_acct_period_id;
5845 END GET_INV_ACCT_PERIOD;
5846
5847 --------------------------------------------------------------------
5848
5849 PROCEDURE AUTONOMOUS_WRITE_TO_WIE (
5850 p_header_id IN NUMBER,
5851 p_message IN VARCHAR2,
5852 p_request_id IN NUMBER,
5853 p_program_id IN NUMBER,
5854 p_program_application_id IN NUMBER,
5855 p_message_type IN NUMBER,
5856 x_err_code OUT NOCOPY NUMBER,
5857 x_err_msg OUT NOCOPY VARCHAR2)
5858 IS
5859 PRAGMA autonomous_transaction;
5860
5861 x_user NUMBER := FND_GLOBAL.user_id;
5862 x_login NUMBER := FND_GLOBAL.login_id;
5863
5864
5865 BEGIN
5866
5867 INSERT INTO WSM_INTERFACE_ERRORS (
5868 HEADER_ID,
5869 MESSAGE,
5870 LAST_UPDATE_DATE,
5871 LAST_UPDATED_BY,
5872 CREATION_DATE,
5873 CREATED_BY,
5874 LAST_UPDATE_LOGIN,
5875 REQUEST_ID,
5876 PROGRAM_ID,
5877 PROGRAM_APPLICATION_ID,
5878 MESSAGE_TYPE )
5879 values (
5880 p_header_id,
5881 p_message,
5882 SYSDATE,
5883 x_user,
5884 SYSDATE,
5885 x_user,
5886 x_login,
5887 p_request_id,
5888 p_program_id,
5889 p_program_application_id,
5890 p_message_type );
5891
5892 COMMIT;
5893
5894 EXCEPTION
5895 WHEN OTHERS THEN
5896 x_err_code := SQLCODE;
5897 x_err_msg := substrb(sqlerrm,1,2000);
5898 rollback;
5899
5900 END AUTONOMOUS_WRITE_TO_WIE;
5901
5902
5903 -- get bom_sequence_id for a given wip_entity_id
5904 FUNCTION GET_JOB_BOM_SEQ_ID(
5905 p_wip_entity_id in number
5906 ) RETURN NUMBER IS
5907
5908 l_common_bom_seq_id NUMBER := 0;
5909 l_bom_seq_id NUMBER := 0;
5910 l_bom_item_id NUMBER;
5911 l_alt_bom VARCHAR2(10);
5912 l_org_id NUMBER;
5913
5914 BEGIN
5915 SELECT wdj.common_bom_sequence_id,
5916 decode(wdj.job_type, 1, wdj.primary_item_id, wdj.bom_reference_id),
5917 wdj.alternate_bom_designator,
5918 wdj.organization_id
5919 INTO l_common_bom_seq_id,
5920 l_bom_item_id,
5921 l_alt_bom,
5922 l_org_id
5923 FROM wip_discrete_jobs wdj
5924 WHERE wdj.wip_entity_id = p_wip_entity_id;
5925
5926 --if(l_common_bom_seq_id IS NULL) then -- bug 3453830
5927 if(l_common_bom_seq_id IS NULL or l_common_bom_seq_id = 0) then -- bug 3453830
5928 return null;
5929 else
5930 SELECT bbom.bill_sequence_id
5931 INTO l_bom_seq_id
5932 FROM bom_bill_of_materials bbom
5933 WHERE bbom.common_bill_sequence_id = l_common_bom_seq_id
5934 AND bbom.organization_id = l_org_id
5935 AND bbom.assembly_item_id = l_bom_item_id
5936 AND nvl(bbom.alternate_bom_designator, '-@#$%') = nvl(l_alt_bom, '-@#$%');
5937 end if;
5938
5939 return l_bom_seq_id;
5940
5941 EXCEPTION
5942
5943 WHEN OTHERS THEN
5944 return -1;
5945
5946 END GET_JOB_BOM_SEQ_ID;
5947
5948
5949 -- Start : Added to fix bug 3452913 --
5950 FUNCTION replacement_copy_op_seq_id (
5951 p_job_op_seq_id NUMBER,
5952 p_wip_entity_id NUMBER
5953 ) RETURN INTEGER
5954 IS
5955 l_copy_op_seq_id NUMBER := NULL;
5956 BEGIN
5957
5958 SELECT distinct(wco.operation_sequence_id) -- Added distinct to fix bug #3507878
5959 INTO l_copy_op_seq_id
5960 FROM wsm_copy_operations wco,
5961 wip_operations wo
5962 WHERE wo.operation_sequence_id = p_job_op_seq_id
5963 AND wo.wip_entity_id = p_wip_entity_id
5964 AND wo.wip_entity_id = wco.wip_entity_id
5965 AND wo.wsm_op_seq_num = wco.operation_seq_num;
5966
5967 return l_copy_op_seq_id;
5968
5969 EXCEPTION
5970 WHEN NO_DATA_FOUND THEN
5971 return NULL;
5972
5973 WHEN OTHERS THEN
5974 return NULL;
5975 END replacement_copy_op_seq_id;
5976 -- End : Added to fix bug 3452913 --
5977
5978
5979 -- BA bug 3512105
5980 -- will return WLBJ.internal_copy_type, return -3 if not available
5981 FUNCTION get_internal_copy_type (
5982 p_wip_entity_id NUMBER
5983 ) RETURN INTEGER
5984 IS
5985 l_int_copy_type NUMBER;
5986 BEGIN
5987
5988 SELECT INTERNAL_COPY_TYPE
5989 INTO l_int_copy_type
5990 FROM wsm_lot_based_jobs
5991 WHERE wip_entity_id = p_wip_entity_id;
5992 return l_int_copy_type;
5993
5994 EXCEPTION
5995 when others then
5996 return 3;
5997 END;
5998
5999 -- EA bug 3512105
6000
6001 --bug 3754881 procedure for locking wdj to be called from the Move and WLT Forms
6002 PROCEDURE lock_wdj(
6003 x_err_code OUT NOCOPY NUMBER
6004 , x_err_msg OUT NOCOPY VARCHAR2
6005 , p_wip_entity_id IN NUMBER
6006 , p_rollback_flag IN NUMBER)
6007 IS
6008 row_locked EXCEPTION;
6009 PRAGMA EXCEPTION_INIT(row_locked, -54);
6010 l_dummy NUMBER;
6011 BEGIN
6012 IF p_rollback_flag = 1 THEN
6013 ROLLBACK TO LOCK_WDJ;
6014 END IF;
6015
6016 SAVEPOINT LOCK_WDJ;
6017
6018 SELECT 1
6019 INTO l_dummy
6020 FROM wip_discrete_jobs
6021 WHERE wip_entity_id = p_wip_entity_id
6022 FOR UPDATE NOWAIT;
6023
6024 -- bug 4932475 (base bug 4759095): Create a savepoint after locking wdj. We will rollback to this savepoint
6025 -- in rollback_before_add_operation so that the lock on the job is retained.
6026 SAVEPOINT AFTER_LOCK_WDJ;
6027
6028 x_err_code := 0;
6029
6030 EXCEPTION
6031 WHEN row_locked THEN
6032 x_err_code := 1;
6033
6034 WHEN others THEN
6035 x_err_code := SQLCODE;
6036 x_err_msg := substr('WSMPUTIL.LOCK_WDJ: ' || SQLERRM, 1, 4000);
6037 END lock_wdj;
6038 --end bug 3754881
6039 --Bug 5182520:Added the following procedure to handle material status checks.
6040 Function is_status_applicable(p_wms_installed IN VARCHAR2,
6041 p_trx_status_enabled IN NUMBER,
6042 p_trx_type_id IN NUMBER,
6043 p_lot_status_enabled IN VARCHAR2,
6044 p_serial_status_enabled IN VARCHAR2,
6045 p_organization_id IN NUMBER,
6046 p_inventory_item_id IN NUMBER,
6047 p_sub_code IN VARCHAR2,
6048 p_locator_id IN NUMBER,
6049 p_lot_number IN VARCHAR2,
6050 p_serial_number IN VARCHAR2,
6051 x_error_msg OUT NOCOPY VARCHAR2
6052 )
6053 return varchar2 is
6054 l_status_applicable VARCHAR2(1) := 'Y';
6055 l_item MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
6056 l_locator MTL_ITEM_LOCATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
6057 BEGIN
6058 IF (p_inventory_item_id IS NOT NULL and p_sub_code IS NOT NULL) THEN
6059 l_status_applicable := INV_MATERIAL_STATUS_GRP.is_status_applicable(p_wms_installed => p_wms_installed,
6060 p_trx_status_enabled => p_trx_status_enabled ,
6061 p_trx_type_id => p_trx_type_id ,
6062 p_lot_status_enabled => p_lot_status_enabled ,
6063 p_serial_status_enabled => p_serial_status_enabled,
6064 p_organization_id => p_organization_id ,
6065 p_inventory_item_id => p_inventory_item_id ,
6066 p_sub_code => p_sub_code ,
6067 p_locator_id => p_locator_id ,
6068 p_lot_number => p_lot_number ,
6069 p_serial_number => p_serial_number ,
6070 p_object_type =>'Z');
6071
6072 END IF; --End of p_inventory_item_id IS NOT NULL and p_sub_code IS NOT NULL
6073
6074 IF l_status_applicable = 'N' THEN
6075 FND_MESSAGE.SET_NAME('WSM','WSM_TRX_SUBINV_NA_DUE_MS');
6076 FND_MESSAGE.SET_TOKEN('TOKEN1', p_sub_code);
6077 x_error_msg := fnd_message.get;
6078
6079 return l_status_applicable;
6080 END IF;
6081
6082 IF (p_locator_id IS NOT NULL) THEN
6083 l_status_applicable := INV_MATERIAL_STATUS_GRP.is_status_applicable(p_wms_installed => p_wms_installed,
6084 p_trx_status_enabled => p_trx_status_enabled ,
6085 p_trx_type_id => p_trx_type_id ,
6086 p_lot_status_enabled => p_lot_status_enabled ,
6087 p_serial_status_enabled => p_serial_status_enabled,
6088 p_organization_id => p_organization_id ,
6089 p_inventory_item_id => p_inventory_item_id ,
6090 p_sub_code => p_sub_code ,
6091 p_locator_id => p_locator_id ,
6092 p_lot_number => p_lot_number ,
6093 p_serial_number => p_serial_number ,
6094 p_object_type =>'L');
6095 END IF; --End of p_locator_id IS NOT NULL
6096
6097 IF l_status_applicable = 'N' THEN
6098 select concatenated_segments
6099 into l_locator
6100 from mtl_item_locations_kfv
6101 where inventory_location_id = p_locator_id
6102 and organization_id = p_organization_id;
6103
6104 FND_MESSAGE.SET_NAME('INV','INV_TRX_LOCATOR_NA_DUE_MS');
6105 FND_MESSAGE.SET_TOKEN('TOKEN1', l_locator);
6106 x_error_msg := fnd_message.get;
6107 return l_status_applicable;
6108 END IF;
6109
6110 IF (p_lot_number IS NOT NULL) THEN
6111 l_status_applicable := INV_MATERIAL_STATUS_GRP.is_status_applicable(p_wms_installed => p_wms_installed,
6112 p_trx_status_enabled => p_trx_status_enabled ,
6113 p_trx_type_id => p_trx_type_id ,
6114 p_lot_status_enabled => p_lot_status_enabled ,
6115 p_serial_status_enabled => p_serial_status_enabled,
6116 p_organization_id => p_organization_id ,
6117 p_inventory_item_id => p_inventory_item_id ,
6118 p_sub_code => p_sub_code ,
6119 p_locator_id => p_locator_id ,
6120 p_lot_number => p_lot_number ,
6121 p_serial_number => p_serial_number ,
6122 p_object_type =>'O');
6123 END IF; --End of p_lot_number IS NOT NULL
6124
6125 IF l_status_applicable = 'N' THEN
6126 select concatenated_segments
6127 into l_item
6128 from mtl_system_items_kfv
6129 where inventory_item_id = p_inventory_item_id
6130 and organization_id = p_organization_id;
6131
6132 FND_MESSAGE.SET_NAME('INV','INV_TRX_LOT_NA_DUE_MS');
6133 FND_MESSAGE.SET_TOKEN('TOKEN1', p_lot_number);
6134 FND_MESSAGE.SET_TOKEN('TOKEN2', l_item);
6135 x_error_msg := fnd_message.get;
6136 return l_status_applicable;
6137 END IF;
6138
6139 IF (p_serial_number IS NOT NULL) THEN
6140 l_status_applicable := INV_MATERIAL_STATUS_GRP.is_status_applicable(p_wms_installed => p_wms_installed,
6141 p_trx_status_enabled => p_trx_status_enabled ,
6142 p_trx_type_id => p_trx_type_id ,
6143 p_lot_status_enabled => p_lot_status_enabled ,
6144 p_serial_status_enabled => p_serial_status_enabled,
6145 p_organization_id => p_organization_id ,
6146 p_inventory_item_id => p_inventory_item_id ,
6147 p_sub_code => p_sub_code ,
6148 p_locator_id => p_locator_id ,
6149 p_lot_number => p_lot_number ,
6150 p_serial_number => p_serial_number ,
6151 p_object_type =>'S');
6152 END IF; --End of p_lot_number IS NOT NULL
6153
6154 IF l_status_applicable = 'N' THEN
6155 select concatenated_segments
6156 into l_item
6157 from mtl_system_items_kfv
6158 where inventory_item_id = p_inventory_item_id
6159 and organization_id = p_organization_id;
6160
6161 FND_MESSAGE.SET_NAME('INV','INV_TRX_SER_NA_DUE_MS');
6162 FND_MESSAGE.SET_TOKEN('TOKEN1', p_serial_number);
6163 FND_MESSAGE.SET_TOKEN('TOKEN2', l_item);
6164 x_error_msg := fnd_message.get;
6165 END IF;
6166
6167 return l_status_applicable;
6168
6169 END is_status_applicable;
6170
6171 -- This Function is added to support Add operations/links in LBJ Interface.
6172 FUNCTION validate_job_network(
6173 p_wip_entity_id NUMBER,
6174 x_err_code OUT NOCOPY NUMBER,
6175 x_err_msg OUT NOCOPY VARCHAR2)
6176 RETURN NUMBER IS
6177
6178 type network_links IS record (
6179 operation wsm_copy_op_networks.from_op_seq_num%type,
6180 prev_op wsm_copy_op_networks.from_op_seq_num%type,
6181 prev_op_rec_flag wsm_copy_op_networks.recommended%type,
6182 next_op wsm_copy_op_networks.to_op_seq_num%type,
6183 next_op_rec_flag wsm_copy_op_networks.recommended%type);
6184
6185 type t_network_links is table of network_links index by binary_integer;
6186 v_network_links t_network_links;
6187
6188 type t_primary_path is table of number index by binary_integer;
6189 v_primary_path t_primary_path;
6190
6191 cursor c_job_network is
6192 SELECT CASE
6193 WHEN a.op_seq IS NULL THEN
6194 b.op_seq
6195 ELSE
6196 a.op_seq
6197 END operation,
6198 b.prev_seq prev_op,
6199 b.prev_op_reco,
6200 a.next_op next_op,
6201 a.next_op_reco
6202 FROM
6203 (SELECT from_op_seq_num op_seq,
6204 to_op_seq_num next_op,
6205 recommended next_op_reco
6206 FROM wsm_copy_op_networks
6207 WHERE wip_entity_id = p_wip_entity_id) a
6208 FULL OUTER JOIN
6209 (SELECT to_op_seq_num op_seq,
6210 from_op_seq_num prev_seq,
6211 recommended prev_op_reco
6212 FROM wsm_copy_op_networks
6213 WHERE wip_entity_id = p_wip_entity_id) b
6214 ON a.op_seq = b.op_seq
6215 ORDER BY 1,4;
6216
6217 l_counter number;
6218 l_start_op number;
6219 l_end_op number;
6220 l_nw_start number;
6221 l_nw_end number;
6222 l_prev_op number;
6223 l_next_op_link number;
6224 l_next_link_op number;
6225 l_reco_count number :=0;
6226 l_link_count number :=1;
6227 l_stmt_num number;
6228
6229 e_multiple_start_op exception;
6230 e_multiple_end_op exception;
6231 e_multiple_primary_path exception;
6232 e_network_loop exception;
6233 e_no_continuous_path exception;
6234
6235 BEGIN
6236
6237 l_stmt_num := 10;
6238 begin
6239 select operation_seq_num
6240 into l_nw_start
6241 from wsm_copy_operations
6242 where wip_entity_id = p_wip_entity_id
6243 and network_start_end = 'S';
6244 exception
6245 when others then
6246 raise e_multiple_start_op;
6247 end;
6248 l_stmt_num := 20;
6249 begin
6250 select operation_seq_num
6251 into l_nw_end
6252 from wsm_copy_operations
6253 where wip_entity_id = p_wip_entity_id
6254 and network_start_end = 'E';
6255 exception
6256 when others then
6257 raise e_multiple_end_op;
6258 end;
6259 l_stmt_num := 30;
6260 open c_job_network;
6261 fetch c_job_network bulk collect into v_network_links;
6262 close c_job_network;
6263 l_stmt_num := 40;
6264 l_counter := v_network_links.first;
6265 while l_counter is not null loop
6266
6267 -- Validate if the network has unique start operation.
6268 if v_network_links(l_counter).prev_op is null then
6269 if v_network_links(l_counter).operation <> nvl(l_start_op,v_network_links(l_counter).operation) then
6270 raise e_multiple_start_op;
6271 end if;
6272 l_start_op := v_network_links(l_counter).operation;
6273 end if;
6274 l_stmt_num := 50;
6275 -- Validate if the network has unique end operation.
6276 if v_network_links(l_counter).next_op is null then
6277 if v_network_links(l_counter).operation <> nvl(l_end_op,v_network_links(l_counter).operation) then
6278 raise e_multiple_end_op;
6279 end if;
6280 l_end_op := v_network_links(l_counter).operation;
6281 end if;
6282 l_stmt_num := 60;
6283 -- Validate if the network has unique primary path.
6284 if v_network_links(l_counter).operation = l_prev_op then
6285 if (v_network_links(l_counter).next_op_rec_flag='Y' and l_reco_count=1) then
6286 if l_next_op_link <> v_network_links(l_counter).next_op then
6287 raise e_multiple_primary_path;
6288 end if;
6289 elsif v_network_links(l_counter).next_op_rec_flag='Y' then
6290 l_reco_count :=1;
6291 end if;
6292 else
6293 l_prev_op := v_network_links(l_counter).operation;
6294 l_reco_count :=0;
6295 if v_network_links(l_counter).next_op_rec_flag='Y' then
6296 l_reco_count :=1;
6297 l_next_op_link := v_network_links(l_counter).next_op;
6298 end if;
6299 end if;
6300 l_stmt_num := 70;
6301 -- Validate if start operation has any previous operations.
6302 if v_network_links(l_counter).operation = l_nw_start and
6303 v_network_links(l_counter).prev_op is not null then
6304 raise e_network_loop;
6305 end if;
6306 l_stmt_num := 80;
6307 -- Validate if end operation has any next operations.
6308 if v_network_links(l_counter).operation = l_nw_end and
6309 v_network_links(l_counter).next_op is not null then
6310 raise e_network_loop;
6311 end if;
6312 l_stmt_num := 90;
6313 -- Validate for loop in primary path as well as build the primary path.
6314 if (not v_primary_path.exists(v_network_links(l_counter).operation)) then
6315 if nvl(v_network_links(l_counter).next_op_rec_flag,'Y')='Y' then
6316 v_primary_path(v_network_links(l_counter).operation) := v_network_links(l_counter).next_op;
6317 l_next_link_op := v_network_links(l_counter).next_op;
6318 end if;
6319 else
6320 if v_network_links(l_counter).next_op_rec_flag='Y' then
6321 if nvl(l_next_link_op,v_network_links(l_counter).next_op) <> v_network_links(l_counter).next_op then
6322 raise e_network_loop;
6323 end if;
6324 end if;
6325 end if;
6326 l_counter := v_network_links.next(l_counter);
6327 end loop;
6328 l_stmt_num := 100;
6329 if l_start_op <> l_nw_start then
6330 raise e_multiple_start_op;
6331 end if;
6332
6333 if l_end_op <> l_nw_end then
6334 raise e_multiple_end_op;
6335 end if;
6336 l_stmt_num := 110;
6337 --Validate if primary path is continuous.
6338 l_counter := l_start_op;
6339 loop
6340 l_link_count := l_link_count+1;
6341 if (not v_primary_path.exists(l_counter)) then
6342 raise e_no_continuous_path;
6343 else
6344 l_counter := v_primary_path(l_counter);
6345 if v_primary_path(l_counter) is null then
6346 if l_link_count <> v_primary_path.count then
6347 raise e_no_continuous_path;
6348 end if;
6349 exit;
6350 end if;
6351 end if;
6352 end loop;
6353 l_stmt_num := 120;
6354 x_err_code :=0;
6355 x_err_msg := null;
6356 return 0;
6357
6358 EXCEPTION
6359
6360 when e_multiple_start_op then
6361 x_err_code := -1;
6362 fnd_message.set_name('WSM','WSM_MULT_PRIMARY_STARTS');
6363 x_err_msg := 'Error: validate_job_network: (#'||l_stmt_num||') ' ||fnd_message.get;
6364 return 1;
6365
6366 when e_multiple_end_op then
6367 x_err_code := -1;
6368 fnd_message.set_name('WSM','WSM_MULT_PRIMARY_ENDS');
6369 x_err_msg := 'Error: validate_job_network: (#'||l_stmt_num||') ' ||fnd_message.get;
6370 return 1;
6371
6372 when e_multiple_primary_path then
6373 x_err_code := -1;
6374 fnd_message.set_name('WSM','WSM_MULT_PRIMARY_PATHS');
6375 x_err_msg := 'Error: validate_job_network: (#'||l_stmt_num||') ' ||fnd_message.get;
6376 return 1;
6377
6378 when e_network_loop then
6379 x_err_code := -1;
6380 fnd_message.set_name('WSM','WSM_NTWK_LOOP_EXISTS');
6381 x_err_msg := 'Error: validate_job_network: (#'||l_stmt_num||') ' ||fnd_message.get;
6382 return 1;
6383
6384 when e_no_continuous_path then
6385 x_err_code := -1;
6386 fnd_message.set_name('WSM','WSM_PRIMARY_PATH_END_IMPROPER');
6387 fnd_message.set_token('WSM_SEQ_NUM',l_counter);
6388 x_err_msg := 'Error: validate_job_network: (#'||l_stmt_num||') ' ||fnd_message.get;
6389 return 1;
6390
6391 when others then
6392 x_err_code := -1;
6393 x_err_msg := 'Error: validate_job_network: (#'||l_stmt_num||') ' ||sqlerrm(sqlcode);
6394 return 1;
6395
6396 END validate_job_network;
6397
6398
6399
6400 END WSMPUTIL;
[Home] [Help]