[Home] [Help]
PACKAGE BODY: APPS.WSMPOPRN
Source
1 PACKAGE BODY WSMPOPRN AS
2 /* $Header: WSMOPRNB.pls 120.26.12020000.3 2012/08/30 07:22:22 akuppa ship $ */
3
4 /*============================================================================+
5 | Copyright (c) 1996 Oracle Corporation, Redwood Shores, California, USA |
6 | All rights reserved. |
7 | |
8 | DESCRIPTION |
9 | This package body is used to add WIP Operations to the WIP routing |
10 | from the custom move transactions form |
11 | |
12 | HISTORY |
13 | 06/23/97 DJOFFE Created |
14 | 04/29/00 REDWIN Modified and added procs For WSM |
15 | 06/12/00 REDWIN First Check In |
16 | 09/29/00 GRATNAM Modified update_job_name()for |
17 | assy returns |
18 | 01/15/01 SBHASKAR Bugfix 1523334:Incase of Jump, insert |
19 | into wip_operation_resources |
20 | using bom_std_op_resources |
21 +============================================================================*/
22
23 g_update_flag boolean:=TRUE;
24 --mes
25 g_log_level_unexpected NUMBER := FND_LOG.LEVEL_UNEXPECTED ;
26 g_log_level_error number := FND_LOG.LEVEL_ERROR ;
27 g_log_level_exception number := FND_LOG.LEVEL_EXCEPTION ;
28 g_log_level_event number := FND_LOG.LEVEL_EVENT ;
29 g_log_level_procedure number := FND_LOG.LEVEL_PROCEDURE ;
30 g_log_level_statement number := FND_LOG.LEVEL_STATEMENT ;
31
32 g_msg_lvl_unexp_error NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ;
33 g_msg_lvl_error NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR ;
34 g_msg_lvl_success NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS ;
35 g_msg_lvl_debug_high NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH ;
36 g_msg_lvl_debug_medium NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM ;
37 g_msg_lvl_debug_low NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW ;
38
39 g_ret_success VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
40 g_ret_error VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
41 g_ret_unexpected VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
42 --mes end
43 --NSO Modification by abedajna begin
44 -- Changed the signature of the procedure so that l_op_seq_id is passed along with l_std_operatio_id.
45 -- commenting out the procedure call and replacing by the mofied call.
46 /*
47 **PROCEDURE add_operation(
48 ** p_transaction_type_id IN NUMBER,
49 ** P_Commit IN NUMBER,
50 ** X_Wip_Entity_Id IN NUMBER,
51 ** X_Organization_Id IN NUMBER,
52 ** X_From_Op IN NUMBER,
53 ** X_To_Op IN NUMBER, -- op seq num to be added in WO
54 ** X_Standard_Operation_Id IN NUMBER,
55 ** x_error_code OUT NUMBER,
56 ** x_error_msg OUT VARCHAR2
57 ** ) IS
58 */
59 --move enh? overload this
60
61 PROCEDURE Add_Operation(
62 p_transaction_type_id IN NUMBER,
63 P_Commit IN NUMBER,
64 X_Wip_Entity_Id IN NUMBER,
65 X_Organization_Id IN NUMBER,
66 X_From_Op IN NUMBER,
67 X_To_Op IN NUMBER,
68 --NSO Modification by abedajna
69 X_Standard_Operation_Id IN NUMBER,
70 X_Op_Seq_Id IN NUMBER,
71 x_error_code OUT NOCOPY NUMBER,
72 x_error_msg OUT NOCOPY VARCHAR2)
73 IS
74 BEGIN
75 add_operation(
76 p_transaction_type_id,
77 P_Commit,
78 X_Wip_Entity_Id,
79 X_Organization_Id,
80 X_From_Op,
81 X_To_Op,
82 X_Standard_Operation_Id,
83 X_Op_Seq_Id,
84 x_error_code,
85 x_error_msg,
86 null,
87 null,
88 null,
89 null,
90 null,
91 null);
92 END;
93
94 PROCEDURE add_operation(
95 p_transaction_type_id IN NUMBER,
96 P_Commit IN NUMBER,
97 X_Wip_Entity_Id IN NUMBER,
98 X_Organization_Id IN NUMBER,
99 X_From_Op IN NUMBER,
100 X_To_Op IN NUMBER, -- op seq num to be added in WO
101 X_Standard_Operation_Id IN NUMBER,
102 X_Op_Seq_Id IN NUMBER, -- CZH.I_OED-2, this is the replacement
103 x_error_code OUT NOCOPY NUMBER,
104 x_error_msg OUT NOCOPY VARCHAR2,
105 p_txn_quantity IN NUMBER,
106 p_reco_op_flag IN VARCHAR2,
107 p_to_rtg_op_seq_num IN NUMBER,
108 p_txn_date IN DATE,
109 p_dup_val_ignore IN VARCHAR2,
110 p_jump_flag IN VARCHAR2
111 ) IS
112
113 --NSO Modification by abedajna end
114
115 p_user NUMBER := FND_GLOBAL.USER_ID;
116 p_login NUMBER := FND_GLOBAL.LOGIN_ID;
117 p_req_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
118 p_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
119 p_prog_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
120 p_curdate DATE := SYSDATE;
121 p_rtg_rev_date DATE; -- ADD: CZH.I_OED-1
122 l_count NUMBER := 0;
123 l_stat_num NUMBER;
124 --l_last_op_seq NUMBER; -- DEL: CZH.I_9999
125 l_op_seq_incr NUMBER;
126 -- l_max_op_seq NUMBER;
127 l_op_seq_id NUMBER; --bugfix 2026218
128
129 l_dept_id number; -- abb H
130 l_job_type number; -- abb H
131 l_scrap_account number; -- abb H
132 l_est_absorption_account number; -- abb H
133 l_est_scrap_acc number;
134 l_fm_op_seq_id number; -- BUG2256872
135 l_po_move_exists BOOLEAN ; -- OSP FP I
136 l_recommended_op VARCHAR2(1) := 'N';
137 l_start_quantity NUMBER;
138 x_returnStatus VARCHAR2(1);
139 l_reco_start_date DATE;
140 l_reco_completion_date DATE;
141 l_infi_start_date DATE;
142 l_job_copy_flag NUMBER;
143 l_wsor_max_res_seq_num NUMBER := 0;
144
145 -- Logging variables.....
146 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
147 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
148 l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSMPOPRN.add_operation';
149 l_param_tbl WSM_Log_PVT.param_tbl_type;
150 l_return_status VARCHAR2(1);
151 l_msg_count number;
152 l_msg_data varchar2(4000);
153
154 BEGIN
155 if (l_debug = 'Y') then
156 fnd_file.put_line(fnd_file.log, 'Inside WSMPOPRN p_transaction_type_id '||p_transaction_type_id||
157 ' P_Commit '||P_Commit||
158 ' X_Wip_Entity_Id '||X_Wip_Entity_Id||
159 ' X_Organization_Id '||X_Organization_Id||
160 ' X_From_Op '||X_From_Op||
161 ' X_To_Op '||X_To_Op||
162 ' X_Standard_Operation_Id '||X_Standard_Operation_Id||
163 ' X_Op_Seq_Id '||X_Op_Seq_Id||
164 ' p_txn_quantity '||p_txn_quantity||
165 ' p_reco_op_flag '||p_reco_op_flag||
166 ' p_to_rtg_op_seq_num '||p_to_rtg_op_seq_num||
167 ' p_txn_date '||p_txn_date);
168 END IF;
169
170 -- We use program_id of -999 to indicate that the record is
171 -- created by the custom moves form
172
173 l_stat_num := 10;
174 --move enh 115.78 changed from WSMPCNST to WSMPUTIL
175 IF WSMPUTIL.REFER_SITE_LEVEL_PROFILE = 'Y' THEN
176 l_job_copy_flag := WSMPUTIL.CREATE_LBJ_COPY_RTG_PROFILE;
177 ELSE
178 l_job_copy_flag := WSMPUTIL.CREATE_LBJ_COPY_RTG_PROFILE(x_organization_id);
179 END IF;
180
181 if (l_job_copy_flag = 1) then
182 g_aps_wps_profile := 'Y';
183 else
184 g_aps_wps_profile := 'N';
185 end if;
186
187 if (l_debug = 'Y') then
188 fnd_file.put_line(fnd_file.log, 'g_aps_wps_profile '||g_aps_wps_profile);
189 END IF;
190
191 -- Moved down to fix bug # 1497882
192 /*
193 IF (p_transaction_type_id IN (1, 2)) THEN -- only for move and completion
194 delete_operation (
195 X_Wip_Entity_id,
196 X_Organization_id,
197 -- X_From_Op,
198 X_To_Op,
199 X_Error_Code,
200 X_Error_Msg);
201 END IF;
202 */
203 -- End changes to fix bug # 1497882
204
205 -- If move within the same operation, do nothing
206 l_stat_num := 200;
207 SELECT --nvl(last_operation_seq_num, 9999), -- DEL: CZH.I_9999
208 nvl(op_seq_num_increment, 10)
209 INTO --l_last_op_seq, -- DEL: CZH.I_9999
210 l_op_seq_incr
211 FROM wsm_parameters
212 WHERE organization_id = X_Organization_Id;
213
214 -- BC: CZH.BUG2168828, should call disable_operations even move to last operation
215 IF (x_from_op = x_to_op)
216 THEN -- Redundant check for Move interface
217 return;
218 -- BD: CZH.I_9999, 9999 is no longer the last op in WO
219 /************************
220 ELSIF (x_to_op = l_last_op_seq) THEN
221 if (l_debug = 'Y') then -- czh:BUG1995161
222 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: Calling Disable_operations..');
223 end if; -- czh:BUG1995161
224
225 Disable_operations (x_Wip_entity_id,
226 x_Organization_id,
227 x_From_op,
228 x_error_code,
229 x_error_msg );
230 return;
231 ************************/
232 -- ED: CZH.I_9999
233 END IF;
234 -- EC: CZH.BUG2168828
235
236 /* This is all extracted from wiloer.ppc */
237 l_stat_num := 20;
238
239 /* Code below added by AM for forward moves */
240
241 -- BD: BUG1496147
242 /***********************
243 BEGIN
244 SELECT unique max(operation_seq_num)
245 INTO l_max_op_seq
246 FROM wip_operations
247 WHERE WIP_ENTITY_ID = x_wip_entity_id
248 AND operation_seq_num NOT IN
249 ( SELECT nvl(last_operation_seq_num, 9999)
250 FROM wsm_parameters
251 WHERE organization_id = x_organization_id ) ;
252 EXCEPTION
253 WHEN NO_DATA_FOUND THEN
254 l_max_op_seq := 0;
255 END;
256 ************************/
257 -- ED: BUG1496147
258
259 if (l_debug = 'Y') then -- czh:BUG1995161
260 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: Calling delete_operation..');
261 end if; -- czh:BUG1995161
262
263 -- Moved here from up to fix bug # 1497882
264 -- in form, opertion is added when to_op_seq is selected
265 l_stat_num := 210;
266 delete_operation (X_Wip_Entity_id,
267 X_Organization_id,
268 x_to_op, -- l_max_op_seq + l_op_seq_incr, --X_To_Op,-- Fix bug #1496147
269 X_Error_Code,
270 X_Error_Msg);
271 -- End changes to fix bug # 1497882
272
273
274 if (l_debug = 'Y') then -- czh:BUG1995161
275 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: Calling Disable_operations..');
276 end if; -- czh:BUG1995161
277
278 -- CZH.I_9999, disable operation is called before adding the operation!
279 l_stat_num := 220;
280 Disable_operations (x_Wip_entity_id,
281 x_Organization_id,
282 x_From_op,
283 x_error_code,
284 x_error_msg,
285 p_txn_date);
286
287
288 /* Above code added by AM for forward moves */
289
290 --move enh added the IF
291 IF ((g_aps_wps_profile='N') AND (X_Op_Seq_Id IS NOT NULL)) THEN
292 -- NSO Modification by abedajna begin
293 -- Replaced Standard Operation Id by the op_seq_id
294
295 -- BA: CZH.I_OED-1, should honor ROUTING_REVISION_DATE
296 l_stat_num := 230;
297 --move enh added start_quantity
298 SELECT NVL(ROUTING_REVISION_DATE, SYSDATE), start_quantity
299 INTO p_rtg_rev_date, l_start_quantity
300 FROM WIP_DISCRETE_JOBS wdj
301 WHERE wdj.ORGANIZATION_ID = X_Organization_Id
302 AND wdj.WIP_ENTITY_ID = X_Wip_Entity_Id;
303 -- EA: CZH.I_OED-1
304
305 l_stat_num := 240;
306 SELECT count(*)
307 INTO l_count
308 FROM BOM_OPERATION_SEQUENCES bos,
309 WIP_DISCRETE_JOBS wdj
310 WHERE wdj.WIP_ENTITY_ID = X_Wip_Entity_Id
311 AND wdj.ORGANIZATION_ID = X_Organization_Id
312 -- AND bos.standard_operation_id = X_Standard_Operation_Id
313 AND bos.operation_sequence_id = X_Op_Seq_Id
314 AND bos.routing_sequence_id = wdj.common_routing_sequence_id
315 --BC: CZH.I_OED-1, should honor ROUTING_REVISION_DATE
316 --bug1725145
317 --validate disabled operation
318 --AND sysdate <= nvl(bos.disable_date, sysdate+1)
319 --AND bos.effectivity_date <= sysdate;
320 --endfix 1725145
321 AND p_rtg_rev_date <= nvl(bos.disable_date, p_rtg_rev_date+1)
322 AND p_rtg_rev_date >= bos.effectivity_date;
323 --EC: CZH.I_OED-1
324
325 --NSO Modification by abedajna end
326 ELSIF ((g_aps_wps_profile='Y') AND (x_op_seq_id IS NOT NULL)) THEN
327 l_stat_num := 250;
328 SELECT count(*)
329 INTO l_count
330 FROM WSM_COPY_OPERATIONS WCO
331 WHERE WCO.WIP_ENTITY_ID = X_Wip_Entity_Id
332 AND wco.operation_sequence_id = x_op_seq_id;
333 END IF;
334
335 if (l_debug = 'Y') then -- czh:BUG1995161
336 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: l_count = '||l_count);
337 end if; -- czh:BUG1995161
338
339 --move enh added the IF
340 IF ((g_aps_wps_profile='N') OR (l_count=0)) THEN
341 IF(l_count > 0) THEN -- Not a Jump Operation
342 if (l_debug = 'Y') then -- czh:BUG1995161
343 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: NOT a Jump operation. Inserting into WO..');
344 end if; -- czh:BUG1995161
345
346 -- OSP FP I begin
347 l_stat_num := 260;
348 l_po_move_exists := WSMPUTIL.check_po_move (
349 p_sequence_id => x_op_seq_id,
350 p_sequence_id_type => 'O' ,
351 p_routing_rev_date => p_rtg_rev_date,
352 x_err_code => x_error_code ,
353 x_err_msg => x_error_msg ) ;
354
355 IF (x_error_code <> 0) THEN
356 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation calling WSMPUTIL.check_po_move: '||x_error_msg);
357 return ;
358 END IF;
359 IF (l_po_move_exists) THEN
360 FND_MESSAGE.SET_NAME('WSM','WSM_OP_PO_MOVE');
361 x_error_code := -1;
362 x_error_msg := FND_MESSAGE.GET;
363 return ;
364 END IF;
365 -- OSP FP I end
366 l_stat_num := 270;
367 --bug 3162358 115.78 added CUMULATIVE_SCRAP_QUANTITY
368 INSERT INTO WIP_OPERATIONS
369 (WIP_ENTITY_ID,
370 OPERATION_SEQ_NUM,
371 ORGANIZATION_ID,
372 LAST_UPDATE_DATE,
373 LAST_UPDATED_BY,
374 CREATION_DATE,
375 CREATED_BY,
376 LAST_UPDATE_LOGIN,
377 REQUEST_ID,
378 PROGRAM_APPLICATION_ID,
379 PROGRAM_ID,
380 PROGRAM_UPDATE_DATE,
381 OPERATION_SEQUENCE_ID,
382 STANDARD_OPERATION_ID,
383 DEPARTMENT_ID,
384 DESCRIPTION,
385 SCHEDULED_QUANTITY,
386 QUANTITY_IN_QUEUE,
387 QUANTITY_RUNNING,
388 QUANTITY_WAITING_TO_MOVE,
389 QUANTITY_REJECTED,
390 QUANTITY_SCRAPPED,
391 QUANTITY_COMPLETED,
392 FIRST_UNIT_START_DATE,
393 FIRST_UNIT_COMPLETION_DATE,
394 LAST_UNIT_START_DATE,
395 LAST_UNIT_COMPLETION_DATE,
396 PREVIOUS_OPERATION_SEQ_NUM,
397 NEXT_OPERATION_SEQ_NUM,
398 COUNT_POINT_TYPE,
399 BACKFLUSH_FLAG,
400 MINIMUM_TRANSFER_QUANTITY,
401 DATE_LAST_MOVED,
402 ATTRIBUTE_CATEGORY,
403 ATTRIBUTE1,
404 ATTRIBUTE2,
405 ATTRIBUTE3,
406 ATTRIBUTE4,
407 ATTRIBUTE5,
408 ATTRIBUTE6,
409 ATTRIBUTE7,
410 ATTRIBUTE8,
411 ATTRIBUTE9,
412 ATTRIBUTE10,
413 ATTRIBUTE11,
414 ATTRIBUTE12,
415 ATTRIBUTE13,
416 ATTRIBUTE14,
417 ATTRIBUTE15,
418 OPERATION_YIELD,
419 OPERATION_YIELD_ENABLED,
420 CUMULATIVE_SCRAP_QUANTITY,
421 WSM_COSTED_QUANTITY_COMPLETED,
422 LOWEST_ACCEPTABLE_YIELD) --mes
423 SELECT X_Wip_Entity_Id,
424 -- X_From_Op + 10,
425 -- X_From_Op + l_op_seq_incr,
426 x_to_op, -- l_max_op_seq + l_op_seq_incr, -- Fix bug #1496147
427 X_Organization_Id,
428 p_curdate,
429 p_user,
430 p_curdate,
431 p_user,
432 p_login,
433 DECODE(p_req_id, 0, '', p_req_id),
434 DECODE(p_appl_id, 0, '', p_appl_id),
435 DECODE(p_commit, 1, p_prog_id, -999),
436 DECODE(p_prog_id, 0, '', p_curdate),
437 SEQ.OPERATION_SEQUENCE_ID,
438 SEQ.STANDARD_OPERATION_ID,
439 SEQ.DEPARTMENT_ID,
440 SEQ.OPERATION_DESCRIPTION,
441 ROUND(DJ.Start_Quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
442 0, 0, 0, 0, 0, 0,
443 DJ.SCHEDULED_START_DATE,
444 DJ.SCHEDULED_COMPLETION_DATE,
445 DJ.SCHEDULED_START_DATE,
446 DJ.SCHEDULED_COMPLETION_DATE,
447 -- Bug 4614970 0, 0,
448 -- x_to_op - l_op_seq_incr,0, -- Bug 4614970 The previous line is commented and replaced by this line
449 x_to_op - l_op_seq_incr,null, -- Bug 5336643 Populated null instead of zero
450 SEQ.COUNT_POINT_TYPE,
451 SEQ.BACKFLUSH_FLAG,
452 NVL(SEQ.MINIMUM_TRANSFER_QUANTITY, 0),
453 '',
454 SEQ.ATTRIBUTE_CATEGORY,
455 SEQ.ATTRIBUTE1,
456 SEQ.ATTRIBUTE2,
457 SEQ.ATTRIBUTE3,
458 SEQ.ATTRIBUTE4,
459 SEQ.ATTRIBUTE5,
460 SEQ.ATTRIBUTE6,
461 SEQ.ATTRIBUTE7,
462 SEQ.ATTRIBUTE8,
463 SEQ.ATTRIBUTE9,
464 SEQ.ATTRIBUTE10,
465 SEQ.ATTRIBUTE11,
466 SEQ.ATTRIBUTE12,
467 SEQ.ATTRIBUTE13,
468 SEQ.ATTRIBUTE14,
469 SEQ.ATTRIBUTE15,
470 SEQ.YIELD,
471 to_char(SEQ.OPERATION_YIELD_ENABLED),
472 DJ.QUANTITY_SCRAPPED,
473 0,
474 SEQ.LOWEST_ACCEPTABLE_YIELD
475 FROM BOM_OPERATIONAL_ROUTINGS R,
476 BOM_OPERATION_SEQUENCES SEQ,
477 WIP_DISCRETE_JOBS DJ
478 WHERE SEQ.ROUTING_SEQUENCE_ID =
479 nvl(r.common_routing_sequence_id, r.routing_sequence_id)
480 -- BC: CZH.I_OED-1, should honor routing revision date
481 --AND p_curdate >= SEQ.effectivity_date
482 --AND p_curdate <= NVL(SEQ.DISABLE_DATE,p_curdate+2)
483 AND p_rtg_rev_date >= SEQ.effectivity_date
484 AND p_rtg_rev_date <= NVL(SEQ.DISABLE_DATE, p_rtg_rev_date+2)
485 -- EC: CZH.I_OED-1
486 AND R.ASSEMBLY_ITEM_ID =
487 DECODE( DJ.JOB_TYPE, 1,
488 DJ.PRIMARY_ITEM_ID, DJ.ROUTING_REFERENCE_ID)
489 AND DJ.WIP_ENTITY_ID = X_Wip_Entity_Id
490 AND DJ.ORGANIZATION_ID = X_Organization_Id
491 AND SEQ.operation_sequence_id = X_Op_Seq_Id
492 --NSO Modification by abedajna begin
493 --AND SEQ.Standard_operation_id = X_Standard_Operation_Id
494 --NSO Modification by abedajna end
495 AND SEQ.routing_sequence_id = dj.common_routing_sequence_id;
496
497 --bugfix 2026218
498 --copy attachment from operations document attachment defined in the network routing form.
499 if sql%rowcount > 0 then
500 l_stat_num := 280;
501 select operation_sequence_id
502 into l_op_seq_id
503 from wip_operations
504 where wip_entity_id = x_wip_entity_id
505 and operation_seq_num = x_to_op
506 and organization_id = x_organization_id;
507
508 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
509 X_FROM_ENTITY_NAME => 'BOM_OPERATION_SEQUENCES',
510 X_FROM_PK1_VALUE => to_char(l_op_seq_id),
511 X_TO_ENTITY_NAME => 'WSM_LOT_BASED_OPERATIONS',
512 X_TO_PK1_VALUE => to_char(x_wip_entity_id),
513 X_TO_PK2_VALUE => to_char(x_to_op),
514 X_TO_PK3_VALUE => to_char(x_organization_id),
515 X_CREATED_BY => p_user,
516 X_LAST_UPDATE_LOGIN => p_login,
517 X_PROGRAM_APPLICATION_ID => p_appl_id,
518 X_PROGRAM_ID => p_prog_id,
519 X_REQUEST_ID => p_req_id);
520 end if;
521 --endfix 2026218
522 if (l_debug = 'Y') then -- czh:BUG1995161
523 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: Inserted '
524 ||sql%rowcount||' records in WO.');
525 end if; -- czh:BUG1995161
526
527
528 ELSE -- Jump Operation
529 if (l_debug = 'Y') then -- czh:BUG1995161
530 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: JUMP operation. Inserting into WO..');
531 end if; -- czh:BUG1995161
532
533
534 -- OSP FP I begin
535 l_stat_num := 290;
536 l_po_move_exists := WSMPUTIL.check_po_move (
537 p_sequence_id => x_standard_operation_id,
538 p_sequence_id_type => 'S' ,
539 p_routing_rev_date => p_rtg_rev_date,
540 x_err_code => x_error_code ,
541 x_err_msg => x_error_msg ) ;
542
543
544 IF (x_error_code <> 0) THEN
545 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation '||
546 'calling WSMPUTIL.check_po_move: '||x_error_msg);
547 return ;
548 END IF;
549 IF (l_po_move_exists) THEN
550 FND_MESSAGE.SET_NAME('WSM','WSM_OP_PO_MOVE');
551 x_error_code := -1;
552 x_error_msg := FND_MESSAGE.GET;
553 return ;
554 END IF;
555 -- OSP FP I End
556
557 -- BA: BUG2256872, fetch previous_operation_seq_id only if operation_sequence_id is NULL
558 l_stat_num := 300;
559 select nvl(operation_sequence_id, previous_operation_seq_id)
560 into l_fm_op_seq_id
561 from wip_operations
562 where wip_entity_id = x_wip_entity_id
563 and operation_seq_num = x_from_op
564 and organization_id = x_organization_id;
565 -- EA: BUG2256872
566
567 l_stat_num := 310;
568 --bug 3162358 115.78 added CUMULATIVE_SCRAP_QUANTITY
569 INSERT INTO WIP_OPERATIONS
570 (WIP_ENTITY_ID,
571 OPERATION_SEQ_NUM,
572 ORGANIZATION_ID,
573 LAST_UPDATE_DATE,
574 LAST_UPDATED_BY,
575 CREATION_DATE,
576 CREATED_BY,
577 LAST_UPDATE_LOGIN,
578 REQUEST_ID,
579 PROGRAM_APPLICATION_ID,
580 PROGRAM_ID,
581 PROGRAM_UPDATE_DATE,
582 OPERATION_SEQUENCE_ID,
583 STANDARD_OPERATION_ID,
584 DEPARTMENT_ID,
585 DESCRIPTION,
586 SCHEDULED_QUANTITY,
587 QUANTITY_IN_QUEUE,
588 QUANTITY_RUNNING,
589 QUANTITY_WAITING_TO_MOVE,
590 QUANTITY_REJECTED,
591 QUANTITY_SCRAPPED,
592 QUANTITY_COMPLETED,
593 FIRST_UNIT_START_DATE,
594 FIRST_UNIT_COMPLETION_DATE,
595 LAST_UNIT_START_DATE,
596 LAST_UNIT_COMPLETION_DATE,
597 PREVIOUS_OPERATION_SEQ_NUM,
598 NEXT_OPERATION_SEQ_NUM,
599 COUNT_POINT_TYPE,
600 BACKFLUSH_FLAG,
601 MINIMUM_TRANSFER_QUANTITY,
602 DATE_LAST_MOVED,
603 ATTRIBUTE_CATEGORY,
604 ATTRIBUTE1,
605 ATTRIBUTE2,
606 ATTRIBUTE3,
607 ATTRIBUTE4,
608 ATTRIBUTE5,
609 ATTRIBUTE6,
610 ATTRIBUTE7,
611 ATTRIBUTE8,
612 ATTRIBUTE9,
613 ATTRIBUTE10,
614 ATTRIBUTE11,
615 ATTRIBUTE12,
616 ATTRIBUTE13,
617 ATTRIBUTE14,
618 ATTRIBUTE15,
619 OPERATION_YIELD,
620 OPERATION_YIELD_ENABLED, -- BC: BUG2256872
621 PREVIOUS_OPERATION_SEQ_ID,
622 CUMULATIVE_SCRAP_QUANTITY, -- EC: BUG2256872
623 WSM_COSTED_QUANTITY_COMPLETED,
624 LOWEST_ACCEPTABLE_YIELD) --mes
625 SELECT X_Wip_Entity_Id,
626 -- X_From_Op + 10,
627 -- X_From_Op + l_op_seq_incr,
628 x_to_op, -- l_max_op_seq + l_op_seq_incr, -- Fix bug #1496147
629 X_Organization_Id,
630 p_curdate,
631 p_user,
632 p_curdate,
633 p_user,
634 p_login,
635 DECODE(p_req_id, 0, '', p_req_id),
636 DECODE(p_appl_id, 0, '', p_appl_id),
637 DECODE(p_commit, 1, p_prog_id, -999),
638 DECODE(p_prog_id, 0, '', p_curdate),
639 NULL, --SEQ.OPERATION_SEQUENCE_ID,
640 bso.STANDARD_OPERATION_ID,
641 bso.DEPARTMENT_ID,
642 bso.OPERATION_DESCRIPTION,
643 ROUND(DJ.Start_Quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
644 0, 0, 0, 0, 0, 0,
645 DJ.SCHEDULED_START_DATE,
646 DJ.SCHEDULED_COMPLETION_DATE,
647 DJ.SCHEDULED_START_DATE,
648 DJ.SCHEDULED_COMPLETION_DATE,
649 -- Bug 4614970 0, 0,
650 -- x_to_op - l_op_seq_incr,0, -- Bug 4614970 The previous line is commented and replaced by this line
651 x_to_op - l_op_seq_incr,null, -- Bug 5336643 Populated null instead of zero
652 bso.COUNT_POINT_TYPE,
653 bso.BACKFLUSH_FLAG,
654 NVL(bso.MINIMUM_TRANSFER_QUANTITY, 0),
655 '',
656 bso.ATTRIBUTE_CATEGORY,
657 bso.ATTRIBUTE1,
658 bso.ATTRIBUTE2,
659 bso.ATTRIBUTE3,
660 bso.ATTRIBUTE4,
661 bso.ATTRIBUTE5,
662 bso.ATTRIBUTE6,
663 bso.ATTRIBUTE7,
664 bso.ATTRIBUTE8,
665 bso.ATTRIBUTE9,
666 bso.ATTRIBUTE10,
667 bso.ATTRIBUTE11,
668 bso.ATTRIBUTE12,
669 bso.ATTRIBUTE13,
670 bso.ATTRIBUTE14,
671 bso.ATTRIBUTE15,
672 bso.YIELD,
673 to_char(bso.OPERATION_YIELD_ENABLED), --BC: BUG2256872
674 l_fm_op_seq_id, --EC: BUG2256872
675 DJ.QUANTITY_SCRAPPED,
676 0,
677 BSO.LOWEST_ACCEPTABLE_YIELD
678 FROM BOM_STANDARD_OPERATIONS bso,
679 WIP_DISCRETE_JOBS DJ
680 WHERE DJ.WIP_ENTITY_ID = X_Wip_Entity_Id
681 AND DJ.ORGANIZATION_ID = X_Organization_Id
682 AND bso.Standard_operation_id = X_Standard_Operation_Id;
683
684 -- BA: bugfix 2626658/2681671 moved to here from below
685 --copy attachment from operations document attachment defined in the BOM standard operation form.
686 if sql%rowcount > 0 then
687 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
688 X_FROM_ENTITY_NAME => 'BOM_STANDARD_OPERATIONS',
689 X_FROM_PK1_VALUE => to_char(x_STANDARD_OPERATION_ID),
690 X_TO_ENTITY_NAME => 'WSM_LOT_BASED_OPERATIONS',
691 X_TO_PK1_VALUE => to_char(x_wip_entity_id),
692 X_TO_PK2_VALUE => to_char(x_to_op),
693 X_TO_PK3_VALUE => to_char(x_organization_id),
694 X_CREATED_BY => p_user,
695 X_LAST_UPDATE_LOGIN => p_login,
696 X_PROGRAM_APPLICATION_ID => p_appl_id,
697 X_PROGRAM_ID => p_prog_id,
698 X_REQUEST_ID => p_req_id);
699 end if;
700
701 if (l_debug = 'Y') then
702 fnd_file.put_line(fnd_file.log,
703 'WSMPOPRN.add_operation: Inserted '||sql%rowcount||' records in WO.');
704 end if;
705 -- EA: bugfix 2626658/2681671
706 --mes
707 UPDATE WSM_LOT_BASED_JOBS
708 SET current_job_op_seq_num = x_to_op,
709 current_rtg_op_seq_num = null
710 WHERE WIP_ENTITY_ID = x_wip_entity_id;
711
712 --bug 5191386 - OSFMST1: SCRAP CODES AND BONUS CODES NOT DISPLAYED IN MOVE OUT FOR OP OUTSIDE RT
713 copy_to_op_mes_info(
714 p_wip_entity_id => x_wip_entity_id
715 , p_to_job_op_seq_num => x_to_op
716 , p_to_rtg_op_seq_num => null
717 , p_txn_quantity => p_txn_quantity
718 , p_user => p_user
719 , p_login => p_login
720 , x_return_status => l_return_status
721 , x_msg_count => l_msg_count
722 , x_msg_data => l_msg_data
723 );
724
725 IF l_return_status = g_ret_error THEN
726 RAISE FND_API.G_EXC_ERROR;
727 l_stat_num := 370;
728 IF (l_msg_count = 1) THEN
729 x_error_code := -1;
730 x_error_msg := l_msg_data;
731 ELSE
732 FOR i IN 1..l_msg_count LOOP
733 x_error_code := -1;
734 x_error_msg := substr(x_error_msg||fnd_msg_pub.get, 1, 4000);
735 END LOOP;
736 END IF;
737 RAISE FND_API.G_EXC_ERROR;
738 ELSIF l_return_status = g_ret_unexpected THEN
739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
740 END IF;
741 --end bug 5191386
742 END IF;
743
744 -- BD: bugfix 2626658/2681671
745 -- the following attachement is for jump operation. should move inside Jump condition
746 /******
747 --bugfix 2026218
748 --copy attachment from operations document attachment defined in the BOM standard operation form.
749 if sql%rowcount > 0 then
750 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
751 X_FROM_ENTITY_NAME => 'BOM_STANDARD_OPERATIONS',
752 X_FROM_PK1_VALUE => to_char(x_STANDARD_OPERATION_ID),
753 X_TO_ENTITY_NAME => 'WSM_LOT_BASED_OPERATIONS',
754 X_TO_PK1_VALUE => to_char(x_wip_entity_id),
755 X_TO_PK2_VALUE => to_char(x_to_op),
756 X_TO_PK3_VALUE => to_char(x_organization_id),
757 X_CREATED_BY => p_user,
758 X_LAST_UPDATE_LOGIN => p_login,
759 X_PROGRAM_APPLICATION_ID => p_appl_id,
760 X_PROGRAM_ID => p_prog_id,
761 X_REQUEST_ID => p_req_id);
762 end if;
763 --endfix 2026218
764
765 if (l_debug = 'Y') then
766 fnd_file.put_line(fnd_file.log,
767 'WSMPOPRN.add_operation: Inserted '||sql%rowcount||' records in WO.');
768 end if;
769 ******/
770 -- ED: bugfix 2626658/2681671
771
772 l_stat_num := 30;
773 if (l_debug = 'Y') then -- czh:BUG1995161
774 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: Calling set_prev_next..');
775 end if; -- czh:BUG1995161
776 l_stat_num := 320;
777 -- Bug 4614970 : Changed the signature of set_prev_next so that only one row in WO is updated in the procedure
778 /******
779 set_prev_next(X_wip_entity_id,
780 x_organization_id,
781 x_error_code,
782 x_error_msg);
783 ******/
784
785 set_prev_next(
786 X_wip_entity_id,
787 x_organization_id,
788 x_from_op,
789 x_to_op,
790 l_op_seq_incr,
791 x_error_code,
792 x_error_msg);
793
794 l_stat_num := 40;
795 if (l_debug = 'Y') then -- czh:BUG1995161
796 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: Calling create_op_details..');
797 end if; -- czh:BUG1995161
798
799 -- abb H optional scrap modification begin
800
801 begin
802
803 l_stat_num := 40.1;
804 SELECT BD.DEPARTMENT_ID, BD.SCRAP_ACCOUNT, BD.EST_ABSORPTION_ACCOUNT
805 into l_dept_id, l_scrap_account, l_est_absorption_account
806 FROM WIP_OPERATIONS WO,
807 BOM_DEPARTMENTS BD
808 WHERE WO.WIP_ENTITY_ID = x_wip_entity_id
809 AND WO.OPERATION_SEQ_NUM = x_to_op
810 AND WO.ORGANIZATION_ID = x_organization_id
811 AND WO.DEPARTMENT_ID = BD.DEPARTMENT_ID;
812
813 l_stat_num := 40.15;
814 select job_type
815 into l_job_type
816 from wip_discrete_jobs
817 where wip_entity_id = x_wip_entity_id;
818
819 l_stat_num := 40.2;
820 x_error_code := 0;
821 l_est_scrap_acc := WSMPUTIL.WSM_ESA_ENABLED(x_wip_entity_id, x_error_code, x_error_msg);
822 if x_error_code <> 0 then
823 x_error_msg := 'WSMOPRNB.create_op_details('||l_stat_num||')'|| x_error_msg;
824 rollback;
825 return;
826 end if;
827
828 l_stat_num := 40.3;
829 if (l_est_scrap_acc = 1 and l_job_type = 1) and
830 (l_scrap_account is null or l_est_absorption_account is null) then
831 x_error_code := -1;
832 fnd_message.set_name('WSM','WSM_NO_SCRAP_ACC');
833 fnd_message.set_token('DEPT_ID',to_char(l_dept_id));
834 x_error_msg := FND_MESSAGE.GET;
835 fnd_file.put_line(fnd_file.log, 'WSMOPRNB.create_op_details('||l_stat_num||')'|| x_error_msg);
836 rollback;
837 return;
838 end if;
839
840 exception
841 when others then
842 x_error_code := SQLCODE;
843 x_error_msg := 'WSMOPRNB.add_operation('||l_stat_num||')'|| substr(SQLERRM,1,1000);
844 rollback;
845 return;
846 end;
847
848 -- abb H optional scrap modification end
849
850 l_stat_num := 330;
851 create_op_details(x_wip_entity_id,
852 x_organization_id,
853 x_to_op, -- l_max_op_seq + l_op_seq_incr,-- Fix bug #1496147
854 x_error_code,
855 x_error_msg);
856 ELSE --(g_aps_wps_profile='Y')
857 l_stat_num := 260;
858 --move enh 115.76 removed the po_move check since it is done during copy creation
859 /* l_po_move_exists := WSMPUTIL.check_po_move (
860 p_sequence_id => x_op_seq_id,
861 p_sequence_id_type => 'O' ,
862 p_routing_rev_date => p_rtg_rev_date,
863 x_err_code => x_error_code ,
864 x_err_msg => x_error_msg ) ;
865
866 IF (x_error_code <> 0) THEN
867 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation calling WSMPUTIL.check_po_move: '||x_error_msg);
868 return ;
869 END IF;
870 IF (l_po_move_exists) THEN
871 FND_MESSAGE.SET_NAME('WSM','WSM_OP_PO_MOVE');
872 x_error_code := -1;
873 x_error_msg := FND_MESSAGE.GET;
874 return ;
875 END IF;
876 */
877 BEGIN
878 l_stat_num := 340;
879
880 SELECT nvl(WCO.recommended, 'N'), WCO.RECO_START_DATE, WCO.reco_completion_date,
881 WCO.DEPARTMENT_ID, WCO.SCRAP_ACCOUNT, WCO.EST_ABSORPTION_ACCOUNT
882 INTO l_recommended_op, l_reco_start_date, l_reco_completion_date,
883 l_dept_id, l_scrap_account, l_est_absorption_account
884 FROM WSM_COPY_OPERATIONS WCO
885 WHERE wip_entity_id = X_Wip_Entity_Id
886 AND operation_sequence_id = x_op_seq_id;
887
888 l_stat_num := 60;
889
890 WSMPOPRN.copy_plan_to_execution(x_error_code
891 , x_error_msg
892 , X_Organization_Id
893 , X_Wip_Entity_Id
894 , x_to_op
895 , p_to_rtg_op_seq_num
896 , x_op_seq_id
897 , l_recommended_op
898 , p_txn_quantity
899 , p_txn_date
900 , p_user
901 , p_login
902 , p_req_id
903 , p_appl_id
904 , p_prog_id
905 , 'N'
906 , l_start_quantity);
907
908 IF (x_error_code <> 0) THEN
909 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation calling WSMPUTIL.copy_plan_to_execution: '||x_error_msg);
910 return ;
911 ELSE
912 IF (l_debug = 'Y') THEN
913 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation calling WSMPUTIL.copy_plan_to_execution returned success');
914 END IF;
915 END IF;
916
917 --move enh 115.76 commented this out since this is merged with the previous sql
918 /* l_stat_num := 71;
919
920 SELECT WCO.DEPARTMENT_ID, WCO.SCRAP_ACCOUNT, WCO.EST_ABSORPTION_ACCOUNT
921 into l_dept_id, l_scrap_account, l_est_absorption_account
922 FROM WSM_COPY_OPERATIONS WCO
923 WHERE WCO.WIP_ENTITY_ID = X_Wip_Entity_Id
924 AND WCO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
925 AND WCO.ORGANIZATION_ID = X_Organization_Id;
926 */
927 l_stat_num := 80;
928
929 select job_type
930 into l_job_type
931 from wip_discrete_jobs
932 where wip_entity_id = X_Wip_Entity_Id;
933
934 l_stat_num := 90;
935
936 x_error_code := 0;
937 l_est_scrap_acc := WSMPUTIL.WSM_ESA_ENABLED(X_Wip_Entity_Id, x_error_code, x_error_msg);
938 if x_error_code <> 0 then
939 x_error_msg := 'WSMOPRNB.('||l_stat_num||')'|| x_error_msg;
940 rollback;
941 return;
942 end if;
943
944 l_stat_num := 100;
945
946 if (l_est_scrap_acc = 1 and l_job_type = 1) and
947 (l_scrap_account is null or l_est_absorption_account is null) then
948 x_error_code := -1;
949 fnd_message.set_name('WSM','WSM_NO_SCRAP_ACC');
950 fnd_message.set_token('DEPT_ID',to_char(l_dept_id));
951 x_error_msg := FND_MESSAGE.GET;
952 fnd_file.put_line(fnd_file.log, 'WSMOPRNB.create_op_details('||l_stat_num||')'|| x_error_msg);
953 rollback;
954 return;
955 end if;
956
957 END;
958 --endfix 2026218
959 if (l_debug = 'Y') then -- czh:BUG1995161
960 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: Inserted '
961 ||sql%rowcount||' records in WO.');
962 end if; -- czh:BUG1995161
963
964 END IF; --g_aps_wps=1
965 IF (g_aps_wps_profile='Y') THEN
966 l_stat_num := 70;
967
968 UPDATE WSM_LOT_BASED_JOBS wlbj
969 SET wlbj.on_rec_path = l_recommended_op
970 WHERE wlbj.wip_entity_id = X_Wip_Entity_Id
971 AND wlbj.organization_id = X_Organization_Id
972 AND wlbj.on_rec_path <> l_recommended_op;
973
974 BEGIN
975 SELECT nvl(max(resource_seq_num), 10)
976 INTO l_wsor_max_res_seq_num
977 FROM WIP_SUB_OPERATION_RESOURCES WSOR
978 WHERE wip_entity_id = x_wip_entity_id
979 AND OPERATION_SEQ_NUM = x_to_op;
980 EXCEPTION
981 WHEN no_data_found THEN
982 null;
983 END;
984
985 IF (l_count=0) THEN --jump outside rtg
986 INSERT INTO WIP_SUB_OPERATION_RESOURCES
987 (wip_entity_id,
988 operation_seq_num,
989 resource_seq_num,
990 organization_id,
991 repetitive_schedule_id,
992 last_update_date,
993 last_updated_by,
994 creation_date,
995 created_by,
996 last_update_login,
997 resource_id,
998 uom_code,
999 basis_type,
1000 usage_rate_or_amount,
1001 activity_id,
1002 scheduled_flag,
1003 assigned_units,
1004 maximum_assigned_units, /* ST : Detailed Scheduling */
1005 autocharge_type,
1006 standard_rate_flag,
1007 applied_resource_units,
1008 applied_resource_value,
1009 attribute_category,
1010 attribute1,
1011 attribute2,
1012 attribute3,
1013 attribute4,
1014 attribute5,
1015 attribute6,
1016 attribute7,
1017 attribute8,
1018 attribute9,
1019 attribute10,
1020 attribute11,
1021 attribute12,
1022 attribute13,
1023 attribute14,
1024 attribute15,
1025 completion_date,
1026 start_date,
1027 schedule_seq_num,
1028 substitute_group_num,
1029 replacement_group_num,
1030 setup_id)
1031 SELECT WO.wip_entity_id,
1032 x_to_op,
1033 --bug 3311695 changed the select below to a private function to be compatible with db 8.1.7.4
1034 /* rownum + (SELECT nvl(max(resource_seq_num), 10)
1035 FROM WIP_SUB_OPERATION_RESOURCES WSOR
1036 WHERE wip_entity_id = x_wip_entity_id
1037 AND OPERATION_SEQ_NUM = x_to_op),--.resource_seq_num
1038 */
1039 (rownum + l_wsor_max_res_seq_num),
1040 WO.organization_id,
1041 null,
1042 SYSDATE ,
1043 p_user,
1044 SYSDATE,
1045 p_user,
1046 p_login,
1047 BSSOR.resource_id,
1048 BR.unit_of_measure,
1049 BSSOR.basis_type,
1050 BSSOR.usage_rate_or_amount,
1051 BSSOR.activity_id,
1052 BSSOR.schedule_flag,
1053 BSSOR.assigned_units,
1054 BSSOR.assigned_units, /* ST : Detailed Scheduling */
1055 BSSOR.autocharge_type,
1056 BSSOR.standard_rate_flag,
1057 0, --WCOR.applied_resource_units,
1058 0, -- WCOR.applied_resource_value,
1059 BSSOR.attribute_category,
1060 BSSOR.attribute1,
1061 BSSOR.attribute2,
1062 BSSOR.attribute3,
1063 BSSOR.attribute4,
1064 BSSOR.attribute5,
1065 BSSOR.attribute6,
1066 BSSOR.attribute7,
1067 BSSOR.attribute8,
1068 BSSOR.attribute9,
1069 BSSOR.attribute10,
1070 BSSOR.attribute11,
1071 BSSOR.attribute12,
1072 BSSOR.attribute13,
1073 BSSOR.attribute14,
1074 BSSOR.attribute15,
1075 p_txn_date,
1076 p_txn_date,
1077 BSSOR.schedule_seq_num , --NULL, --schedule_seq_num, / -- Bug 7371846
1078 BSSOR.substitute_group_num,
1079 BSSOR.replacement_group_num, --replacement_group_num,
1080 NULL --setup_id
1081 FROM BOM_RESOURCES BR,
1082 BOM_STD_SUB_OP_RESOURCES BSSOR,
1083 WIP_OPERATIONS wo
1084 WHERE WO.WIP_ENTITY_ID = x_wip_entity_id
1085 AND WO.OPERATION_SEQ_NUM = x_to_op
1086 AND BSSOR.standard_operation_id = WO.standard_operation_id
1087 AND BSSOR.RESOURCE_ID = BR.RESOURCE_ID;
1088 END IF;
1089 --moved the following code to after processing move in form and interface
1090
1091 /***********
1092 IF ((p_jump_flag = 'Y') OR (l_recommended_op <> 'Y') OR (l_reco_start_date IS NULL)
1093 OR (l_reco_completion_date IS NULL)) THEN
1094
1095 SELECT last_unit_completion_date
1096 INTO l_infi_start_date
1097 FROM WIP_OPERATIONS
1098 WHERE wip_entity_id = X_Wip_Entity_Id
1099 AND organization_id = X_Organization_Id
1100 AND operation_seq_num = X_From_Op;
1101
1102 wsm_infinite_scheduler_pvt.schedule(
1103 p_initMsgList => fnd_api.g_true,
1104 p_endDebug => fnd_api.g_true,
1105 p_orgID => X_Organization_Id,
1106 p_wipEntityID => X_Wip_Entity_Id,
1107 p_scheduleMode => WIP_CONSTANTS.CURRENT_OP,
1108 p_startDate => l_infi_start_date,
1109 p_endDate => null,
1110 p_opSeqNum => -X_To_Op,
1111 p_scheQuantity => p_txn_quantity,
1112 x_returnStatus => x_returnStatus,
1113 x_errorMsg => x_error_msg);
1114
1115 IF (x_returnStatus <> fnd_api.g_ret_sts_success) THEN
1116 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation calling wsm_infinite_scheduler_pvt.schedule: '||x_error_msg);
1117 x_error_code := -1;
1118 return ;
1119 ELSE
1120 IF (l_debug = 'Y') THEN
1121 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation calling wsm_infinite_scheduler_pvt.schedule returned success');
1122 END IF;
1123 END IF;
1124 END IF;
1125 ***********/
1126 END IF;
1127
1128 EXCEPTION
1129 -- This just means that the operation was already inserted
1130 WHEN dup_val_on_index THEN
1131 NULL;
1132
1133 WHEN others THEN
1134 x_error_code := SQLCODE;
1135 x_error_msg := 'WSMOPRNB.add_operation('||l_stat_num||')'|| substr(SQLERRM,1,200);
1136 -- czh:BUG1995161
1137 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation: other excpn: (stmt'||l_stat_num
1138 ||'):'||x_error_msg);
1139 END add_operation;
1140
1141
1142 PROCEDURE Disable_operations (
1143 x_Wip_entity_id IN NUMBER,
1144 x_Organization_id IN NUMBER,
1145 x_From_op IN NUMBER,
1146 x_error_code OUT NOCOPY NUMBER,
1147 x_err_msg OUT NOCOPY VARCHAR2,
1148 p_txn_date IN DATE) IS
1149
1150 BEGIN
1151 -- CZH.I_9999, Disable_operations should be called before insert the
1152 -- operation in to WO, hence, all the operations with op_seq_num
1153 -- greater than x_from_op should be disabled!
1154
1155 UPDATE wip_operations
1156 SET count_point_type = 3,
1157 scheduled_quantity = 0, -- Added to fix bug #2686501
1158 --bug 3595728 change from sysdate to p_txn_date-1 second
1159 -- disable_date = sysdate -- Bug 2931071
1160 disable_date = p_txn_date - 1/(24*60*60)
1161 --end bug 3595728
1162 WHERE wip_entity_id = x_wip_entity_id
1163 AND operation_seq_num > x_from_op;
1164 -- BD: CZH.I_9999
1165 --AND operation_seq_num NOT IN
1166 -- (SELECT nvl(last_operation_seq_num, 9999)
1167 -- FROM wsm_parameters
1168 -- WHERE organization_id = x_organization_id ) ;
1169 -- ED: CZH.I_9999
1170
1171 -- bug 3203505 change order of update WOR and WRO
1172 UPDATE wip_operation_resources
1173 SET autocharge_type = 2
1174 WHERE wip_entity_id = x_wip_entity_id
1175 AND operation_seq_num > x_from_op;
1176 -- BD: CZH.I_9999
1177 --AND operation_seq_num NOT IN
1178 -- (SELECT nvl(last_operation_seq_num, 9999)
1179 -- FROM wsm_parameters
1180 -- WHERE organization_id = x_organization_id ) ;
1181 -- ED: CZH.I_9999
1182
1183 UPDATE wip_requirement_operations
1184 SET required_quantity = 0
1185 --quantity_per_assembly = 0 abb, bug 2931071
1186 WHERE wip_entity_id = x_wip_entity_id
1187 AND operation_seq_num > x_from_op;
1188 -- BD: CZH.I_9999
1189 --AND operation_seq_num NOT IN
1190 -- (SELECT nvl(last_operation_seq_num, 9999)
1191 -- FROM wsm_parameters
1192 -- WHERE organization_id = x_organization_id ) ;
1193 -- ED: CZH.I_9999
1194
1195 EXCEPTION
1196 WHEN others THEN
1197 x_error_code := SQLCODE;
1198 x_err_msg := 'WSMOPRNB.disable_operation : '|| substr(SQLERRM,1,200);
1199
1200 -- czh:BUG1995161
1201 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.Disable_operations: other excpn: '|| x_err_msg);
1202
1203
1204 END Disable_operations;
1205
1206
1207 PROCEDURE Delete_Operation(
1208 X_Wip_Entity_Id IN NUMBER,
1209 X_Organization_id IN NUMBER,
1210 --X_From_Op IN NUMBER,
1211 X_To_Op IN NUMBER,
1212 x_error_code OUT NOCOPY NUMBER,
1213 x_error_msg OUT NOCOPY VARCHAR2
1214 ) IS
1215 l_stat_num NUMBER;
1216
1217 BEGIN
1218 l_stat_num := 10;
1219 DELETE FROM WIP_OPERATIONS
1220 WHERE WIP_ENTITY_ID = X_Wip_Entity_id
1221 --AND OPERATION_SEQ_NUM > X_From_Op
1222 AND OPERATION_SEQ_NUM = X_To_Op
1223 AND PROGRAM_ID = -999
1224 AND ORGANIZATION_ID = X_Organization_Id;
1225
1226 IF SQL%ROWCOUNT > 0 THEN
1227 l_stat_num := 20;
1228 --bugfix 2026218
1229 --delete attached document, since operation was deleted from wip_operations table.
1230
1231 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(
1232 X_entity_name => 'WSM_LOT_BASED_OPERATIONS',
1233 X_pk1_value => to_char(x_wip_entity_id),
1234 X_pk2_value => to_char(x_to_op),
1235 x_pk3_value => to_char(x_organization_id),
1236 X_delete_document_flag => 'Y');
1237 --endfix 2026218
1238 -- Bug 4614970 Call to set_prev_next is not required in this procedure
1239 -- set_prev_next gets called eventually in add_operation at a different place.
1240 /**************
1241 set_prev_next(X_Wip_Entity_id,
1242 X_Organization_Id,
1243 x_error_code,
1244 x_error_msg);
1245 ****************/
1246 l_stat_num := 30;
1247 DELETE FROM WIP_OPERATION_RESOURCES
1248 WHERE WIP_ENTITY_ID = X_Wip_Entity_id
1249 --AND OPERATION_SEQ_NUM > X_From_Op
1250 AND OPERATION_SEQ_NUM = X_To_Op
1251 AND PROGRAM_ID = -999
1252 AND ORGANIZATION_ID = X_Organization_Id;
1253
1254 l_stat_num := 40;
1255 DELETE FROM WIP_OPERATION_YIELDS
1256 WHERE WIP_ENTITY_ID = X_Wip_Entity_id
1257 --AND OPERATION_SEQ_NUM > X_From_Op
1258 AND OPERATION_SEQ_NUM = X_To_Op
1259 AND PROGRAM_ID = -999
1260 AND ORGANIZATION_ID = X_Organization_Id;
1261
1262 l_stat_num := 50;
1263 DELETE FROM WIP_REQUIREMENT_OPERATIONS
1264 WHERE WIP_ENTITY_ID = X_Wip_Entity_id
1265 --AND OPERATION_SEQ_NUM > X_From_Op
1266 AND OPERATION_SEQ_NUM = X_To_Op
1267 AND ORGANIZATION_ID = X_Organization_Id;
1268 END IF;
1269 EXCEPTION
1270 WHEN others THEN
1271 x_error_code := SQLCODE;
1272 x_error_msg := 'WSMOPRNB.delete_operation('||l_stat_num||')'|| substr(SQLERRM,1,200);
1273
1274 -- czh:BUG1995161
1275 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.delete_operation: other excpn: '|| x_error_msg);
1276
1277 END delete_operation;
1278
1279 PROCEDURE create_op_details (
1280 X_wip_entity_Id IN NUMBER,
1281 X_organization_Id IN NUMBER,
1282 X_op_seq_num IN NUMBER,
1283 x_error_code OUT NOCOPY NUMBER,
1284 x_error_msg OUT NOCOPY VARCHAR2
1285 ) IS
1286 l_stat_num NUMBER;
1287
1288 BEGIN
1289 l_stat_num := 10;
1290 INSERT INTO WIP_OPERATION_RESOURCES
1291 (WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
1292 ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID,
1293 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
1294 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
1295 PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
1296 RESOURCE_ID, UOM_CODE,
1297 BASIS_TYPE, USAGE_RATE_OR_AMOUNT, ACTIVITY_ID,
1298 SCHEDULED_FLAG, ASSIGNED_UNITS, AUTOCHARGE_TYPE,
1299 STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS, APPLIED_RESOURCE_VALUE,
1300 START_DATE, COMPLETION_DATE,
1301 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
1302 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
1303 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
1304 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
1305 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
1306 ATTRIBUTE15,
1307 SCHEDULE_SEQ_NUM, --bugfix 2493065
1308 SUBSTITUTE_GROUP_NUM,
1309 PRINCIPLE_FLAG,
1310 SETUP_ID)
1311 SELECT OPS.WIP_ENTITY_ID, OPS.OPERATION_SEQ_NUM, ORS.RESOURCE_SEQ_NUM,
1312 OPS.ORGANIZATION_ID, OPS.REPETITIVE_SCHEDULE_ID,
1313 OPS.LAST_UPDATE_DATE, OPS.LAST_UPDATED_BY, OPS.CREATION_DATE,
1314 OPS.CREATED_BY, OPS.LAST_UPDATE_LOGIN, OPS.REQUEST_ID,
1315 OPS.PROGRAM_APPLICATION_ID, OPS.PROGRAM_ID,
1316 OPS.PROGRAM_UPDATE_DATE, ORS.RESOURCE_ID, RSC.UNIT_OF_MEASURE,
1317 ORS.BASIS_TYPE, ORS.USAGE_RATE_OR_AMOUNT, ORS.ACTIVITY_ID,
1318 ORS.SCHEDULE_FLAG, ORS.ASSIGNED_UNITS, ORS.AUTOCHARGE_TYPE,
1319 ORS.STANDARD_RATE_FLAG, 0, 0,
1320 OPS.FIRST_UNIT_START_DATE, OPS.LAST_UNIT_COMPLETION_DATE,
1321 ORS.ATTRIBUTE_CATEGORY, ORS.ATTRIBUTE1, ORS.ATTRIBUTE2,
1322 ORS.ATTRIBUTE3, ORS.ATTRIBUTE4, ORS.ATTRIBUTE5,
1323 ORS.ATTRIBUTE6, ORS.ATTRIBUTE7, ORS.ATTRIBUTE8,
1324 ORS.ATTRIBUTE9, ORS.ATTRIBUTE10, ORS.ATTRIBUTE11,
1325 ORS.ATTRIBUTE12, ORS.ATTRIBUTE13, ORS.ATTRIBUTE14,
1326 ORS.ATTRIBUTE15,
1327 ORS.SCHEDULE_SEQ_NUM, --bugfix 2493065
1328 ORS.SUBSTITUTE_GROUP_NUM,
1329 ORS.PRINCIPLE_FLAG,
1330 ORS.SETUP_ID
1331 FROM BOM_RESOURCES RSC,
1332 BOM_OPERATION_RESOURCES ORS,
1333 WIP_OPERATIONS OPS
1334 WHERE OPS.ORGANIZATION_ID = X_Organization_Id
1335 AND OPS.WIP_ENTITY_ID = X_Wip_Entity_Id
1336 AND OPS.OPERATION_SEQ_NUM = X_op_seq_num
1337 AND OPS.OPERATION_SEQUENCE_ID = ORS.OPERATION_SEQUENCE_ID
1338 AND ORS.RESOURCE_ID = RSC.RESOURCE_ID
1339 AND RSC.ORGANIZATION_ID = OPS.ORGANIZATION_ID;
1340
1341 -- Begin bugfix 1523334 : If the above INSERT fails, it could be because OPS.OPERATION_SEQUENCE_ID
1342 -- is null. This will be true in case of JUMP operation.
1343 -- We will fetch the details from BOM_STD_OP_RESOURCES
1344
1345 IF (sql%rowcount = 0) THEN
1346 l_stat_num := 15;
1347 INSERT INTO WIP_OPERATION_RESOURCES
1348 (WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
1349 ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID,
1350 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
1351 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
1352 PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
1353 RESOURCE_ID, UOM_CODE,
1354 BASIS_TYPE, USAGE_RATE_OR_AMOUNT, ACTIVITY_ID,
1355 SCHEDULED_FLAG, ASSIGNED_UNITS, AUTOCHARGE_TYPE,
1356 STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS, APPLIED_RESOURCE_VALUE,
1357 START_DATE, COMPLETION_DATE,
1358 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
1359 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
1360 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
1361 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
1362 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
1363 ATTRIBUTE15,
1364 SCHEDULE_SEQ_NUM,PRINCIPLE_FLAG, --Added : -- bug 7371846
1365 SUBSTITUTE_GROUP_NUM, --Added : -- bug 7371846
1366 REPLACEMENT_GROUP_NUM ) --Added : -- bug 7371846
1367 SELECT OPS.WIP_ENTITY_ID, OPS.OPERATION_SEQ_NUM, ORS.RESOURCE_SEQ_NUM,
1368 OPS.ORGANIZATION_ID, OPS.REPETITIVE_SCHEDULE_ID,
1369 OPS.LAST_UPDATE_DATE, OPS.LAST_UPDATED_BY, OPS.CREATION_DATE,
1370 OPS.CREATED_BY, OPS.LAST_UPDATE_LOGIN, OPS.REQUEST_ID,
1371 OPS.PROGRAM_APPLICATION_ID, OPS.PROGRAM_ID,
1372 OPS.PROGRAM_UPDATE_DATE, ORS.RESOURCE_ID, RSC.UNIT_OF_MEASURE,
1373 ORS.BASIS_TYPE, ORS.USAGE_RATE_OR_AMOUNT, ORS.ACTIVITY_ID,
1374 ORS.SCHEDULE_FLAG, ORS.ASSIGNED_UNITS, ORS.AUTOCHARGE_TYPE,
1375 ORS.STANDARD_RATE_FLAG, 0, 0,
1376 OPS.FIRST_UNIT_START_DATE, OPS.LAST_UNIT_COMPLETION_DATE,
1377 ORS.ATTRIBUTE_CATEGORY, ORS.ATTRIBUTE1, ORS.ATTRIBUTE2,
1378 ORS.ATTRIBUTE3, ORS.ATTRIBUTE4, ORS.ATTRIBUTE5,
1379 ORS.ATTRIBUTE6, ORS.ATTRIBUTE7, ORS.ATTRIBUTE8,
1380 ORS.ATTRIBUTE9, ORS.ATTRIBUTE10, ORS.ATTRIBUTE11,
1381 ORS.ATTRIBUTE12, ORS.ATTRIBUTE13, ORS.ATTRIBUTE14,
1382 ORS.ATTRIBUTE15,
1383 ORS.SCHEDULE_SEQ_NUM,ORS.PRINCIPLE_FLAG, --Added : -- bug 7371846
1384 ORS.SUBSTITUTE_GROUP_NUM, --Added : -- bug 7371846
1385 0 --Added : -- make it as zero on resources level -- bug 7371846
1386 FROM BOM_RESOURCES RSC,
1387 BOM_STD_OP_RESOURCES ORS,
1388 WIP_OPERATIONS OPS
1389 WHERE OPS.ORGANIZATION_ID = X_Organization_Id
1390 AND OPS.WIP_ENTITY_ID = X_Wip_Entity_Id
1391 AND OPS.OPERATION_SEQ_NUM = X_op_seq_num
1392 AND OPS.STANDARD_OPERATION_ID = ORS.STANDARD_OPERATION_ID
1393 AND ORS.RESOURCE_ID = RSC.RESOURCE_ID
1394 AND RSC.ORGANIZATION_ID = OPS.ORGANIZATION_ID;
1395 END IF;
1396 -- End bugfix 1523334
1397
1398 -- bugfix 1611094
1399 -- for jumping operation, the operation_seq_id insert into the wip_operations table will be null,
1400 -- so the previous select statment will not insert row into wip_operation_yields,
1401 -- changed where clause, so that new record will be insert into the wip_operation_yields with jumping operation
1402
1403 -- The below insert is used for Costing Changes (OP Yield)
1404 l_stat_num := 20;
1405 INSERT INTO WIP_OPERATION_YIELDS
1406 (WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
1407 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
1408 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
1409 PROGRAM_APPLICATION_ID, PROGRAM_ID,PROGRAM_UPDATE_DATE,
1410 STATUS, SCRAP_ACCOUNT, EST_SCRAP_ABSORB_ACCOUNT)
1411 SELECT WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, WO.ORGANIZATION_ID,
1412 WO.LAST_UPDATE_DATE, WO.LAST_UPDATED_BY, WO.CREATION_DATE,
1413 WO.CREATED_BY, WO.LAST_UPDATE_LOGIN, WO.REQUEST_ID,
1414 WO.PROGRAM_APPLICATION_ID, WO.PROGRAM_ID, WO.PROGRAM_UPDATE_DATE,
1415 NULL, BD.SCRAP_ACCOUNT, BD.EST_ABSORPTION_ACCOUNT
1416 FROM WIP_OPERATIONS WO,
1417 -- BOM_OPERATION_SEQUENCES BOS, fix bug 1611094
1418 BOM_DEPARTMENTS BD
1419 WHERE WO.WIP_ENTITY_ID = X_Wip_Entity_Id
1420 AND WO.OPERATION_SEQ_NUM = X_op_seq_num
1421 AND WO.ORGANIZATION_ID = X_Organization_Id
1422 -- AND WO.OPERATION_SEQUENCE_ID = BOS.OPERATION_SEQUENCE_ID --bugfix 1611094
1423 AND WO.DEPARTMENT_ID = BD.DEPARTMENT_ID; --bugfix 1611094
1424
1425 EXCEPTION
1426 WHEN others THEN
1427 x_error_code := SQLCODE;
1428 x_error_msg := 'WSMOPRNB.create_op_details('||l_stat_num||')'|| substr(SQLERRM,1,200);
1429
1430 -- czh:BUG1995161
1431 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.create_op_details: other excpn: '|| x_error_msg);
1432
1433 END create_op_details;
1434
1435 --
1436 -- bugfix 2644217: Removed the organization_id join to get an optimal plan
1437 -- ("first row with min/max range scan")
1438 -- With min/max range scan, it only looks one row ahead or one row after .....versus the
1439 -- original plan head to scan all matching rows, sort them and then pick the max or the min.
1440 --
1441 PROCEDURE set_prev_next (
1442 X_wip_entity_Id IN NUMBER,
1443 X_organization_Id IN NUMBER,
1444 x_error_code OUT NOCOPY NUMBER,
1445 x_error_msg OUT NOCOPY VARCHAR2
1446 ) IS
1447 l_stat_num NUMBER;
1448 BEGIN
1449 l_stat_num := 10;
1450 UPDATE WIP_OPERATIONS WO
1451 SET WO.PREVIOUS_OPERATION_SEQ_NUM =
1452 (SELECT MAX(OPERATION_SEQ_NUM)
1453 FROM WIP_OPERATIONS
1454 WHERE WIP_ENTITY_ID = X_Wip_Entity_Id
1455 -- bugfix 2644217: AND ORGANIZATION_ID = X_Organization_Id
1456 AND OPERATION_SEQ_NUM < WO.OPERATION_SEQ_NUM),
1457 WO.NEXT_OPERATION_SEQ_NUM =
1458 (SELECT MIN(OPERATION_SEQ_NUM)
1459 FROM WIP_OPERATIONS
1460 WHERE WIP_ENTITY_ID = X_Wip_Entity_Id
1461 -- bugfix 2644217: AND ORGANIZATION_ID = X_Organization_Id
1462 AND OPERATION_SEQ_NUM > WO.OPERATION_SEQ_NUM)
1463 WHERE WO.WIP_ENTITY_ID = X_Wip_Entity_Id
1464 AND WO.ORGANIZATION_ID = X_Organization_Id;
1465 EXCEPTION
1466 WHEN others THEN
1467 x_error_code := SQLCODE;
1468 x_error_msg := 'WSMOPRNB.set_prev_next('||l_stat_num||')'|| substr(SQLERRM,1,200);
1469
1470 -- czh:BUG1995161
1471 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.set_prev_next: other excpn: '|| x_error_msg);
1472
1473 END set_prev_next;
1474
1475 -- Begin Changes Bug 4614970
1476 -- Procedure set_prev_next has been overloaded, two new parameters introduced from the earlier procedure definition
1477
1478 PROCEDURE set_prev_next (
1479 X_wip_entity_Id IN NUMBER,
1480 X_organization_Id IN NUMBER,
1481 X_from_op IN NUMBER,
1482 X_to_op IN NUMBER,
1483 X_op_seq_incr IN NUMBER,
1484 x_error_code OUT NOCOPY NUMBER,
1485 x_error_msg OUT NOCOPY VARCHAR2
1486 ) IS
1487
1488 l_stat_num NUMBER;
1489
1490 BEGIN
1491
1492 l_stat_num := 10;
1493
1494
1495 UPDATE WIP_OPERATIONS WO
1496 SET WO.PREVIOUS_OPERATION_SEQ_NUM = decode(WO.OPERATION_SEQ_NUM - x_op_seq_incr,0,null,WO.OPERATION_SEQ_NUM - x_op_seq_incr) ,
1497 -- Bug 5336643 Added decode so that null is populated instead of zero
1498 WO.NEXT_OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM + x_op_seq_incr
1499 WHERE WO.WIP_ENTITY_ID = X_Wip_Entity_Id
1500 AND WO.ORGANIZATION_ID = X_Organization_Id
1501 AND WO.OPERATION_SEQ_NUM >= x_from_op
1502 AND WO.OPERATION_SEQ_NUM < x_to_op ;
1503
1504 EXCEPTION
1505 WHEN others THEN
1506 x_error_code := SQLCODE;
1507 x_error_msg := 'WSMOPRNB.set_prev_next overloaded ('||l_stat_num||')'|| substr(SQLERRM,1,200);
1508 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.set_prev_next overloaded : other excpn: '|| x_error_msg);
1509
1510 END set_prev_next;
1511
1512 -- End changes Bug 4614970
1513
1514 PROCEDURE get_current_op (
1515 p_wip_entity_id IN NUMBER,
1516 p_current_op_seq OUT NOCOPY NUMBER,
1517 p_current_op_step OUT NOCOPY NUMBER,
1518 p_next_mand_step OUT NOCOPY NUMBER,
1519 x_error_code OUT NOCOPY NUMBER,
1520 x_error_msg OUT NOCOPY VARCHAR2
1521 ) IS
1522
1523 l_step NUMBER;
1524 l_step_code VARCHAR2(80);
1525 l_op NUMBER;
1526 l_std_operation_id NUMBER;
1527 l_intra_op_flag_value NUMBER;
1528 l_stat_num NUMBER;
1529 BEGIN
1530
1531 -- Bugfix 1551170 begin
1532 -- We will first check if the job has been completely scrapped. If so, return with
1533 -- operation_seq_num and intraoperation_step namely SCRAP.
1534
1535
1536 l_stat_num := 5;
1537 BEGIN
1538 SELECT operation_seq_num,
1539 WIP_CONSTANTS.SCRAP,
1540 nvl(to_number(STANDARD_OPERATION_ID),0),
1541 ml.meaning
1542 INTO l_op,
1543 l_step,
1544 l_std_operation_id,
1545 l_step_code
1546 FROM
1547 mfg_lookups ml,
1548 wip_operations wo
1549 WHERE wip_entity_id = p_wip_entity_id
1550 AND quantity_in_queue = 0
1551 AND quantity_running = 0
1552 AND quantity_waiting_to_move = 0
1553 AND quantity_scrapped = quantity_completed
1554 AND quantity_completed > 0
1555 AND count_point_type <> 3
1556 AND ml.lookup_type = 'WIP_INTRAOPERATION_STEP'
1557 AND ml.lookup_code = WIP_CONSTANTS.SCRAP;
1558
1559 p_current_op_seq := l_op;
1560 p_current_op_step := l_step;
1561
1562 return; -- if the above select returns a row, then, the
1563 -- job was completely scrapped.
1564 EXCEPTION
1565 when NO_DATA_FOUND then null; -- if the above select did not return a row, proceed further..
1566 END;
1567 -- Bugfix 1551170 end
1568
1569
1570 l_stat_num :=10;
1571 SELECT operation_seq_num,
1572 decode(quantity_in_queue, 0,
1573 decode(quantity_running, 0, WIP_CONSTANTS.TOMOVE, WIP_CONSTANTS.RUN),
1574 WIP_CONSTANTS.QUEUE),
1575 nvl(to_number(STANDARD_OPERATION_ID),0),
1576 ml.meaning
1577 INTO l_op,
1578 l_step,
1579 l_std_operation_id,
1580 l_step_code
1581 FROM
1582 mfg_lookups ml,
1583 wip_operations wo
1584 WHERE wip_entity_id = p_wip_entity_id
1585 AND (quantity_in_queue <> 0
1586 OR quantity_running <> 0
1587 OR quantity_waiting_to_move <> 0)
1588 AND ml.lookup_type = 'WIP_INTRAOPERATION_STEP'
1589 AND ml.lookup_code =
1590 decode(quantity_in_queue, 0,
1591 decode(quantity_running, 0, WIP_CONSTANTS.TOMOVE, WIP_CONSTANTS.RUN),
1592 WIP_CONSTANTS.QUEUE);
1593
1594 p_current_op_seq := l_op;
1595 p_current_op_step := l_step;
1596 l_stat_num := 20;
1597 l_intra_op_flag_value := get_intra_operation_value(l_std_operation_id,
1598 x_error_code,
1599 x_error_msg);
1600 l_stat_num := 30;
1601 p_next_mand_step := get_next_mandatory_step(l_step, l_intra_op_flag_value);
1602 EXCEPTION
1603 WHEN others THEN
1604 x_error_code := SQLCODE;
1605 x_error_msg := 'WSMOPRNB.get_current_op('||l_stat_num||')'|| substr(SQLERRM,1,200);
1606 END get_current_op;
1607
1608 FUNCTION get_intra_operation_value (
1609 p_std_op_id IN NUMBER,
1610 x_error_code OUT NOCOPY NUMBER,
1611 x_error_msg OUT NOCOPY VARCHAR2
1612 ) RETURN NUMBER IS
1613
1614 l_queue_flag NUMBER;
1615 l_run_flag NUMBER;
1616 l_to_move_flag NUMBER;
1617 l_stat_num NUMBER;
1618 l_row_count NUMBER;
1619 -- Logging variables.....
1620 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
1621 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1622 l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSMPOPRN.get_intra_operation_value';
1623 l_param_tbl WSM_Log_PVT.param_tbl_type;
1624 l_error_count NUMBER;
1625 l_return_code NUMBER;
1626 l_error_msg VARCHAR2(4000);
1627 l_stmt_num NUMBER := 0;
1628
1629 BEGIN
1630 --begin WSM modification (bug. 1377752) by abedajna, 08/23/00
1631
1632 l_stat_num := 10;
1633
1634 --***VJ Changed for Performance Upgrade***--
1635 BEGIN
1636 --MES replacing WSM_OPERATION_DETAILS with BOM_STANDARD_OPERATIONS
1637 /*
1638 SELECT QUEUE_MANDATORY_FLAG,
1639 RUN_MANDATORY_FLAG,
1640 TO_MOVE_MANDATORY_FLAG
1641 INTO l_queue_flag,
1642 l_run_flag,
1643 l_to_move_flag
1644 FROM WSM_OPERATION_DETAILS
1645 WHERE STANDARD_OPERATION_ID = p_std_op_id;
1646 */
1647 SELECT decode(QUEUE_MANDATORY_FLAG, 0, 2, NULL, 2, QUEUE_MANDATORY_FLAG),
1648 decode(RUN_MANDATORY_FLAG, 0, 2, NULL, 2, RUN_MANDATORY_FLAG),
1649 decode(TO_MOVE_MANDATORY_FLAG, 0, 2, NULL, 2, TO_MOVE_MANDATORY_FLAG)
1650 INTO l_queue_flag,
1651 l_run_flag,
1652 l_to_move_flag
1653 FROM BOM_STANDARD_OPERATIONS
1654 WHERE STANDARD_OPERATION_ID = p_std_op_id;
1655 EXCEPTION
1656 WHEN NO_DATA_FOUND THEN
1657 return(1);
1658 END;
1659
1660 IF (l_debug = 'Y') THEN
1661 fnd_file.put_line(fnd_file.log, '*************************** p_std_op_id '||p_std_op_id||
1662 ' l_queue_flag '||l_queue_flag);
1663 END IF;
1664
1665 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
1666 l_msg_tokens.delete;
1667 WSM_log_PVT.logMessage (
1668 p_module_name => l_module,
1669 p_msg_text => 'After SELECT from BOM_STANDARD_OPERATIONS '||
1670 ';l_queue_flag '||
1671 l_queue_flag||
1672 ';l_run_flag '||
1673 l_run_flag||
1674 ';l_to_move_flag '||
1675 l_to_move_flag,
1676 p_stmt_num => l_stmt_num,
1677 p_msg_tokens => l_msg_tokens,
1678 p_fnd_log_level => G_LOG_LEVEL_STATEMENT,
1679 p_run_log_level => l_log_level
1680 );
1681 END IF;
1682 --***VJ End Changes***--
1683
1684 --***VJ Deleted for Performance Upgrade***--
1685 -- Replaced by above BEGIN-END block--
1686 --*** SELECT COUNT(*)
1687 --*** INTO l_row_count
1688 --*** FROM WSM_OPERATION_DETAILS
1689 --*** WHERE STANDARD_OPERATION_ID = p_std_op_id;
1690 --***
1691 --*** IF l_row_count = 0 THEN
1692 --*** return(1);
1693 --*** END IF;
1694 --***--end WSM modification (bug. 1377752) by abedajna, 08/23/00
1695 --***
1696 --***l_stat_num := 20;
1697 --*** SELECT QUEUE_MANDATORY_FLAG,
1698 --*** RUN_MANDATORY_FLAG,
1699 --*** TO_MOVE_MANDATORY_FLAG
1700 --*** INTO l_queue_flag,
1701 --*** l_run_flag,
1702 --*** l_to_move_flag
1703 --*** FROM WSM_OPERATION_DETAILS
1704 --*** WHERE STANDARD_OPERATION_ID = p_std_op_id;
1705 --***VJ End Deletions***--
1706
1707 IF l_queue_flag = 2 AND l_run_flag = 2 AND l_to_move_flag = 2 THEN
1708 return(1);
1709 ELSIF l_queue_flag = 1 AND l_run_flag = 2 AND l_to_move_flag = 2 THEN
1710 return(2);
1711 ELSIF l_queue_flag = 2 AND l_run_flag = 1 AND l_to_move_flag = 2 THEN
1712 return(3);
1713 ELSIF l_queue_flag = 1 AND l_run_flag = 1 AND l_to_move_flag = 2 THEN
1714 return(4);
1715 ELSIF l_queue_flag = 2 AND l_run_flag = 2 AND l_to_move_flag = 1 THEN
1716 return(5);
1717 ELSIF l_queue_flag = 1 AND l_run_flag = 2 AND l_to_move_flag = 1 THEN
1718 return(6);
1719 ELSIF l_queue_flag = 2 AND l_run_flag = 1 AND l_to_move_flag = 1 THEN
1720 return(7);
1721 ELSIF l_queue_flag = 1 AND l_run_flag = 1 AND l_to_move_flag = 1 THEN
1722 return(8);
1723 END IF;
1724 EXCEPTION
1725
1726 --begin WSM modification (bug. 1377752) by abedajna, 08/23/00
1727 -- WHEN no_data_found THEN
1728 -- x_error_code := SQLCODE;
1729 -- x_error_msg := 'WSMOPRNB.get_intra_operation_value('||l_stat_num||')'|| substr(SQLERRM,1,200);
1730 -- return(1);
1731 --end WSM modification (bug. 1377752) by abedajna, 08/23/00
1732 WHEN others THEN
1733 x_error_code := SQLCODE;
1734 x_error_msg := 'WSMOPRNB.get_intra_operation_value('||l_stat_num||')'|| substr(SQLERRM,1,200);
1735
1736 -- czh:BUG1995161
1737 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.get_intra_operation_value: other excpn: '|| x_error_msg);
1738
1739 END get_intra_operation_value;
1740
1741 FUNCTION get_next_mandatory_step(x_step IN NUMBER, x_flag IN NUMBER)
1742 RETURN NUMBER IS
1743 BEGIN
1744
1745 IF x_step = WIP_CONSTANTS.TOMOVE THEN
1746 return(0);
1747 ELSIF x_step = WIP_CONSTANTS.RUN and x_flag < 5 THEN
1748 return(0);
1749 ELSIF x_step = WIP_CONSTANTS.RUN THEN
1750 return(WIP_CONSTANTS.TOMOVE);
1751 ELSIF x_step = WIP_CONSTANTS.QUEUE and x_flag IN (3,4,7,8) THEN
1752 return(WIP_CONSTANTS.RUN);
1753 ELSIF x_step = WIP_CONSTANTS.QUEUE and x_flag IN (5,6) THEN
1754 return(WIP_CONSTANTS.TOMOVE);
1755 ELSIF x_step = 0 AND x_flag IN (2,4,6,8) THEN
1756 return(WIP_CONSTANTS.QUEUE);
1757 ELSIF x_step = 0 AND x_flag IN (3,7) THEN
1758 return(WIP_CONSTANTS.RUN);
1759 ELSIF x_step = 0 and x_flag = 5 THEN
1760 return(WIP_CONSTANTS.TOMOVE);
1761 ELSE
1762 return(0);
1763 END IF;
1764
1765 END get_next_mandatory_step;
1766
1767 PROCEDURE get_sec_inv_loc(
1768 p_routing_seq_id IN NUMBER,
1769 x_secondary_invetory_name OUT NOCOPY VARCHAR2,
1770 x_secondary_locator OUT NOCOPY NUMBER,
1771 x_error_code OUT NOCOPY NUMBER,
1772 x_error_msg OUT NOCOPY VARCHAR2
1773 ) IS
1774
1775 l_end_op_seq_id NUMBER;
1776 l_secondary_inventory_name VARCHAR2(10);
1777 l_secondary_locator NUMBER;
1778 l_stat_num NUMBER;
1779
1780 BEGIN
1781 -- CZH.I_OED-1: !!!! IMPORTANT !!!!
1782 -- when we call find_routing_end, we need to pass routing revision date
1783 -- I can not find where is this procedure called
1784 -- If you need to call this function, please let me know
1785
1786 l_stat_num := 10;
1787 if (l_debug = 'Y') then -- czh:BUG1995161
1788 fnd_file.put_line (fnd_file.log, 'WSMPOPRN.get_sec_inv_loc: Calling WSMPUTIL.FIND_ROUTING_END ..');
1789 end if; -- czh:BUG1995161
1790
1791 WSMPUTIL.FIND_ROUTING_END(p_routing_seq_id,
1792 l_end_op_seq_id,
1793 x_error_code,
1794 x_error_msg);
1795
1796 l_stat_num := 20;
1797 --MES replacing WSM_OPERATION_DETAILS with BOM_STANDARD_OPERATIONS
1798 /*
1799 SELECT SECONDARY_INVENTORY_NAME,
1800 INVENTORY_LOCATION_ID
1801 INTO l_secondary_inventory_name,
1802 l_secondary_locator
1803 FROM WSM_OPERATION_DETAILS WOD,
1804 BOM_OPERATION_SEQUENCES BOS
1805 WHERE WOD.STANDARD_OPERATION_ID = BOS.STANDARD_OPERATION_ID
1806 AND BOS.OPERATION_SEQUENCE_ID = l_end_op_seq_id;
1807 */
1808 SELECT DEFAULT_SUBINVENTORY,
1809 DEFAULT_LOCATOR_ID
1810 INTO l_secondary_inventory_name,
1811 l_secondary_locator
1812 FROM BOM_STANDARD_OPERATIONS BSO,
1813 BOM_OPERATION_SEQUENCES BOS
1814 WHERE BSO.STANDARD_OPERATION_ID = BOS.STANDARD_OPERATION_ID
1815 AND BOS.OPERATION_SEQUENCE_ID = l_end_op_seq_id;
1816
1817 EXCEPTION
1818 WHEN others then
1819 x_error_code := SQLCODE;
1820 x_error_msg := 'WSMOPRNB.get_sec_inv_loc('||l_stat_num||')'|| substr(SQLERRM,1,200);
1821
1822 -- czh:BUG1995161
1823 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.get_sec_inv_loc: other excpn: '|| x_error_msg);
1824
1825 END get_sec_inv_loc;
1826
1827 FUNCTION update_job_name (
1828 p_wip_entity_id IN NUMBER,
1829 p_subinventory IN VARCHAR2,
1830 p_org_id IN NUMBER,
1831 p_txn_type IN NUMBER,
1832 /*BA#1803065*/
1833 p_dup_job_name OUT NOCOPY VARCHAR2,
1834 /*BA#1803065*/
1835 x_error_code OUT NOCOPY NUMBER,
1836 x_error_msg OUT NOCOPY VARCHAR2
1837 ) return VARCHAR2 IS
1838
1839 -- Added the parameter P_UPDATE_FLAG to the function update_job_name.
1840 -- The function will update wip_entities with the new job name only
1841 -- if p_update_flag has the value 'TRUE'.
1842
1843 l_sep VARCHAR2(1);
1844 l_suffix VARCHAR2(30);
1845 l_new_name VARCHAR2(240); -- Changed to 240 from 60--
1846 l_entity_name VARCHAR2(240); -- Changed to 240 from 60--
1847 l_count NUMBER;
1848 l_stat_num NUMBER;
1849 l_comp_subinv VARCHAR2(10);
1850 another_job_exists EXCEPTION;
1851 no_sector_subinv EXCEPTION; -- abb
1852
1853 -- Bug 1522722: added code to read the new profile WSM_COMPLETE_SEC_LOT_EXTN_LEVEL
1854 -- Values can be : 1 = "Item"
1855 -- 2 = "Subinventory"
1856
1857 x_level VARCHAR2(30);
1858
1859 -- Added for 12.1 enhancement
1860 l_reuse_jobname NUMBER;
1861
1862
1863 BEGIN
1864
1865 l_count := 0;
1866
1867 -- Bug 1522722 Get the job suffix level
1868 x_level := nvl(FND_PROFILE.value('WSM_COMPLETE_SEC_LOT_EXTN_LEVEL'), '1'); -- default should be "Item"
1869
1870
1871 l_stat_num := 10;
1872
1873 --Fix bug #1504009
1874 IF (p_subinventory IS NULL) THEN
1875 select completion_subinventory
1876 into l_comp_subinv
1877 from wip_discrete_jobs
1878 where wip_entity_id = p_wip_entity_id
1879 and organization_id = p_org_id; -- as part of bugfix 2062110: added orgn_id
1880
1881 /* ST bug fix 3256834 : check if l_comp_subinv is null get it using the routing sequence id .... */
1882 IF (l_comp_subinv IS NULL) THEN
1883 /* Get the completion subinventory of the routing associated with the job */
1884 l_stat_num := 15;
1885 select bor.completion_subinventory
1886 into l_comp_subinv
1887 from wip_discrete_jobs wdj,BOM_OPERATIONAL_ROUTINGS bor
1888 where wdj.wip_entity_id = p_wip_entity_id
1889 and wdj.organization_id = p_org_id
1890 and wdj.common_routing_sequence_id = bor.routing_sequence_id;
1891
1892 END IF;
1893 /* ST bug fix 3256834 : end */
1894 ELSE
1895 l_comp_subinv := p_subinventory;
1896 END IF;
1897 --End Fix bug #1504009
1898
1899 select JOB_COMPLETION_SEPARATOR,nvl(REUSE_JOBNAME,1)
1900 into l_sep,l_reuse_jobname
1901 from wsm_parameters
1902 where organization_id = p_org_id;
1903
1904 -- The below condition is modified for 12.1 enhancement.
1905 -- If reuse job name is set to 'yes' then only we need to append sector extension.
1906 if (p_txn_type = 2 and l_reuse_jobname = 1) then -- For completions
1907 if (x_level = '1') then -- Fix bug 1522722
1908 BEGIN
1909 l_stat_num := 20;
1910 select l_sep || wse.sector_extension_code
1911 into l_suffix
1912 from wip_entities we,
1913 wsm_sector_extensions wse,
1914 wsm_item_extensions wie
1915 where we.wip_entity_id = p_wip_entity_id
1916 and we.primary_item_id = wie.inventory_item_id
1917 and we.organization_id = wie.organization_id
1918 and wie.sector_extension_id = wse.sector_extension_id;
1919 EXCEPTION
1920 when no_data_found then
1921 l_suffix := null;
1922 END;
1923 end if;
1924
1925 -- Fix bug1522722
1926 IF (l_suffix is null) or (x_level = '2') THEN -- subinventory
1927 l_stat_num := 30;
1928 -- abb bug 2345650: added the exception block and no-data-found handler.
1929 BEGIN
1930 select l_sep || wse.sector_extension_code
1931 into l_suffix
1932 from wsm_sector_extensions wse,
1933 wsm_subinventory_extensions wsube
1934 where wse.sector_extension_id = wsube.sector_extension_id
1935 and wsube.secondary_inventory_name = l_comp_subinv -- p_subinventory Fix bug #1504009
1936 and wsube.organization_id = p_org_id;
1937 EXCEPTION
1938 when no_data_found then
1939 raise no_sector_subinv;
1940 END;
1941 END IF;
1942
1943 l_stat_num := 40;
1944 select wip_entity_name
1945 into l_new_name
1946 from wip_entities
1947 where wip_entity_id = p_wip_entity_id
1948 and organization_id = p_org_id; -- as part of bugfix 2062110 : added orgn_id
1949
1950 IF l_suffix is null THEN
1951 return(null);
1952 END IF;
1953
1954 while(TRUE)
1955 LOOP
1956 l_new_name := l_new_name || l_suffix;
1957
1958 l_stat_num := 50;
1959 select count(*)
1960 into l_count
1961 from wip_entities
1962 where wip_entity_name = l_new_name
1963 /*BA#2073251*/
1964 and organization_id = p_org_id;
1965 /*EA#2073251*/
1966
1967 IF l_count = 0 THEN
1968 l_stat_num := 60;
1969 if (g_update_flag) THEN
1970 update wip_entities
1971 set wip_entity_name = l_new_name
1972 where wip_entity_id = p_wip_entity_id
1973 and organization_id = p_org_id; -- as part of bugfix 2062110 : added orgn_id
1974 end if;
1975 return(l_new_name);
1976 END IF;
1977 END LOOP;
1978
1979 -- couple of situation might occur here.
1980 -- if reuse job name is set to 'yes' no issues.
1981 -- if reuse job name is set to 'no' then we need to trim sector extn for jobs completed/cancelled before
1982 -- this parameter was set. There are no issues for jobs completed/cancelled after this parameter was set.
1983 elsif (p_txn_type = 3) then -- For Assy Returns
1984
1985 l_stat_num := 20;
1986
1987 select wip_entity_name
1988 into l_entity_name
1989 from wip_entities
1990 where wip_entity_id = p_wip_entity_id
1991 and organization_id = p_org_id;
1992
1993 l_stat_num := 30;
1994
1995 select substr(l_entity_name,1, decode(instr(l_entity_name,l_sep) -1, -1, length(l_entity_name), instr(l_entity_name, l_sep) -1))
1996 into l_new_name
1997 from sys.dual;
1998
1999
2000 l_stat_num := 40;
2001 --bugfix 2820900: moved the assigment to before IF
2002 p_dup_job_name := l_new_name; -- bugfix 1803065
2003
2004 --
2005 -- bugfix 2820900: Only when the new name is different from the current name, we will check the existence.
2006 -- This is important for this fix as there might be existing jobs which are complete/canceled but without sector lot extn.
2007 --
2008
2009 if (l_entity_name <> l_new_name) then -- bugfix 2820900 : added if clause
2010 select count(*)
2011 into l_count
2012 from wip_entities
2013 where wip_entity_name = l_new_name
2014 and organization_id = p_org_id;
2015
2016 -- p_dup_job_name := l_new_name; -- bugfix 2820900: moved the assigment above.
2017
2018
2019 if (l_count > 0) then
2020 raise another_job_exists;
2021 end if;
2022
2023 l_stat_num := 50;
2024
2025 if (g_update_flag) THEN
2026 update wip_entities
2027 set wip_entity_name = l_new_name
2028 where wip_entity_id = p_wip_entity_id
2029 and organization_id = p_org_id;
2030 end if;
2031 end if; -- bugfix 2820900 : added if clause
2032
2033 return(l_new_name);
2034
2035 elsif(l_reuse_jobname = 2) then
2036 l_stat_num := 60;
2037 select wip_entity_name
2038 into l_entity_name
2039 from wip_entities
2040 where wip_entity_id = p_wip_entity_id
2041 and organization_id = p_org_id;
2042
2043 return(l_entity_name);
2044 end if;
2045
2046 EXCEPTION
2047 when no_data_found then
2048 x_error_code := SQLCODE;
2049 x_error_msg := 'WSMOPRNB.update_job_name('||l_stat_num||'): No Data Found';
2050 -- czh:BUG1995161
2051 fnd_file.put_line(fnd_file.log, x_error_msg);
2052 return(-1);
2053
2054 when another_job_exists then
2055 FND_MESSAGE.SET_NAME('WSM', 'WSM_DUP_JOB_NAME');
2056 FND_MESSAGE.SET_TOKEN('JOB_NAME', l_new_name); -- bugfix 2820900: corrected the token name
2057 x_error_msg := fnd_message.get;
2058 x_error_code := -1;
2059 fnd_file.put_line(fnd_file.log, x_error_msg);
2060 return(-1);
2061
2062
2063 -- abb bug 2345650: added the exception block and no-data-found handler.
2064 when no_sector_subinv then
2065 FND_MESSAGE.SET_NAME('WSM', 'WSM_NO_SECTOR_SUBINV');
2066 FND_MESSAGE.SET_TOKEN('SUB', l_comp_subinv);
2067 x_error_msg := fnd_message.get;
2068 x_error_code := -1;
2069 fnd_file.put_line(fnd_file.log, x_error_msg);
2070 return(-1);
2071
2072 when others then
2073 x_error_code := SQLCODE;
2074 x_error_msg := 'WSMOPRNB.update_job_name('||l_stat_num||')'|| substr(SQLERRM,1,200);
2075 -- czh:BUG1995161
2076 fnd_file.put_line(fnd_file.log, x_error_msg);
2077 return(-1);
2078
2079 END update_job_name;
2080
2081
2082 -- Bug# 1986051.
2083 -- The following is a cover routine that sets g_update_flag as per the
2084 -- passed value p_update_flag and calls the above function update_job_name
2085
2086 FUNCTION update_job_name (
2087 p_wip_entity_id IN NUMBER,
2088 p_subinventory IN VARCHAR2,
2089 p_org_id IN NUMBER,
2090 p_txn_type IN NUMBER,
2091 p_update_flag IN BOOLEAN,
2092 /*BA#1803065*/
2093 p_dup_job_name OUT NOCOPY VARCHAR2,
2094 /*BA#1803065*/
2095 x_error_code OUT NOCOPY NUMBER,
2096 x_error_msg OUT NOCOPY VARCHAR2
2097 ) return VARCHAR2 IS
2098
2099 x_ret_val VARCHAR2(240);
2100
2101 BEGIN
2102 g_update_flag:=p_update_flag;
2103 x_ret_val:=update_job_name(p_wip_entity_id,p_subinventory,p_org_id,
2104 p_txn_type,p_dup_job_name,x_error_code,x_error_msg);
2105
2106 g_update_flag:=TRUE; -- Set the default value TRUE
2107 return(x_ret_val);
2108
2109 END update_job_name;
2110
2111 PROCEDURE update_job_name1 (
2112 p_wip_entity_id IN NUMBER,
2113 p_org_id IN NUMBER,
2114 p_reentered_job_name IN OUT NOCOPY VARCHAR2,
2115 x_error_code OUT NOCOPY NUMBER,
2116 x_error_msg OUT NOCOPY VARCHAR2
2117 ) IS
2118
2119 l_stat_num NUMBER;
2120 l_count NUMBER;
2121 another_job_exists exception;
2122
2123 BEGIN
2124
2125 l_count := 0;
2126 l_stat_num := 10;
2127
2128 select count(*)
2129 into l_count
2130 from wip_entities
2131 where wip_entity_name = p_reentered_job_name
2132 /*BA#2073251*/
2133 and organization_id = p_org_id;
2134 /*EA#2073251*/
2135
2136 if (l_count > 0) then
2137 raise another_job_exists;
2138 end if;
2139
2140 l_stat_num := 20;
2141
2142 update wip_entities
2143 set wip_entity_name = p_reentered_job_name
2144 where wip_entity_id = p_wip_entity_id
2145 and organization_id = p_org_id;
2146
2147 x_error_code := 0;
2148
2149 EXCEPTION
2150 when no_data_found then
2151 x_error_code := SQLCODE;
2152 x_error_msg := 'WSMOPRNB.update_job_name1('||l_stat_num||'): No Data Found';
2153 -- czh:BUG1995161
2154 fnd_file.put_line(fnd_file.log, x_error_msg);
2155
2156 when another_job_exists then
2157 FND_MESSAGE.SET_NAME('WSM', 'WSM_DUP_JOB_NAME'); -- bugfix 2820900: added this
2158 FND_MESSAGE.SET_TOKEN('JOB_NAME', p_reentered_job_name); -- bugfix 2820900: corrected the token name
2159 x_error_msg := fnd_message.get;
2160 x_error_code := -1;
2161
2162 --bugfix 2820900: commented
2163 --x_error_msg := 'WSMOPRNB.update_job_name1('||l_stat_num||'): Another job exists with the name ' || p_reentered_job_name;
2164 -- czh:BUG1995161
2165 fnd_file.put_line(fnd_file.log, x_error_msg);
2166
2167 when others then
2168 x_error_code := SQLCODE;
2169 x_error_msg := 'WSMOPRNB.update_job_name1('||l_stat_num||')'|| substr(SQLERRM,1,200);
2170 -- czh:BUG1995161
2171 fnd_file.put_line(fnd_file.log, x_error_msg);
2172
2173 END update_job_name1;
2174 /*EA#1803065*/
2175
2176 -- Bug 2328947
2177 PROCEDURE rollback_before_add_operation IS
2178 /*bug 4759095: We shall rollback to savepoint after obtaining the lock on wdj so that this lock is
2179 retained. This is to prevent another user from doing move transactions against the same job. */
2180 savepoint_not_found EXCEPTION;
2181 PRAGMA EXCEPTION_INIT(savepoint_not_found, -1086);
2182 begin
2183 rollback to AFTER_LOCK_WDJ;
2184 EXCEPTION
2185 when savepoint_not_found then
2186 rollback;
2187 end rollback_before_add_operation;
2188
2189 PROCEDURE copy_plan_to_execution(
2190 x_error_code OUT NOCOPY NUMBER
2191 , x_error_msg OUT NOCOPY VARCHAR2
2192 , p_org_id IN NUMBER
2193 , p_wip_entity_id IN NUMBER
2194 , p_to_job_op_seq_num IN NUMBER
2195 , p_to_rtg_op_seq_num IN NUMBER
2196 , p_to_op_seq_id IN NUMBER
2197 , p_reco_op_flag IN VARCHAR2
2198 , p_txn_quantity IN NUMBER
2199 , p_txn_date IN DATE
2200 , p_user IN NUMBER
2201 , p_login IN NUMBER
2202 , p_request_id IN NUMBER
2203 , p_program_application_id IN NUMBER
2204 , p_program_id IN NUMBER
2205 , p_dup_val_ignore IN VARCHAR2
2206 , p_start_quantity IN NUMBER
2207 )
2208 IS
2209 l_stmt_num NUMBER;
2210 l_wor_count NUMBER := 0;
2211 l_wor_reco_res NUMBER := 0;
2212 l_recommended_op VARCHAR2(1);
2213 l_est_scrap_acc NUMBER;
2214 l_job_type NUMBER;
2215 l_dept_id NUMBER;
2216 l_scrap_account NUMBER;
2217 l_est_absorption_account NUMBER;
2218 p_commit NUMBER := 1;
2219 e_proc_error EXCEPTION;
2220 l_phantom_exists NUMBER;
2221
2222 -- Logging variables.....
2223 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
2224 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2225 l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSMPOPRN.copy_plan_to_execution';
2226 l_param_tbl WSM_Log_PVT.param_tbl_type;
2227 l_return_status VARCHAR2(1);
2228 l_msg_count number;
2229 l_msg_data varchar2(4000);
2230 BEGIN
2231 l_stmt_num := 10;
2232 if (l_debug = 'Y') then
2233 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: p_org_id '||p_org_id||
2234 ' p_wip_entity_id '||p_wip_entity_id||
2235 ' p_to_job_op_seq_num '||p_to_job_op_seq_num||
2236 ' p_to_rtg_op_seq_num '||p_to_rtg_op_seq_num||
2237 ' p_to_op_seq_id '||p_to_op_seq_id||
2238 ' p_reco_op_flag '||p_reco_op_flag||
2239 ' p_txn_quantity '||p_txn_quantity||
2240 ' p_txn_date '||p_txn_date||
2241 ' p_user '||p_user||
2242 ' p_login '||p_login||
2243 ' p_request_id '||p_request_id||
2244 ' p_program_application_id '||p_program_application_id||
2245 ' p_program_id '||p_program_id);
2246 end if;
2247
2248 --move enh? ask vj abt recommended col in WO
2249 --bug 3162358 115.78 added CUMULATIVE_SCRAP_QUANTITY
2250 --bug 3370199 added wsm_op_seq_num
2251 INSERT INTO WIP_OPERATIONS
2252 (WIP_ENTITY_ID,
2253 OPERATION_SEQ_NUM,
2254 ORGANIZATION_ID,
2255 LAST_UPDATE_DATE,
2256 LAST_UPDATED_BY,
2257 CREATION_DATE,
2258 CREATED_BY,
2259 LAST_UPDATE_LOGIN,
2260 REQUEST_ID,
2261 PROGRAM_APPLICATION_ID,
2262 PROGRAM_ID,
2263 PROGRAM_UPDATE_DATE,
2264 OPERATION_SEQUENCE_ID,
2265 STANDARD_OPERATION_ID,
2266 DEPARTMENT_ID,
2267 DESCRIPTION,
2268 SCHEDULED_QUANTITY,
2269 QUANTITY_IN_QUEUE,
2270 QUANTITY_RUNNING,
2271 QUANTITY_WAITING_TO_MOVE,
2272 QUANTITY_REJECTED,
2273 QUANTITY_SCRAPPED,
2274 QUANTITY_COMPLETED,
2275 FIRST_UNIT_START_DATE,
2276 FIRST_UNIT_COMPLETION_DATE,
2277 LAST_UNIT_START_DATE,
2278 LAST_UNIT_COMPLETION_DATE,
2279 PREVIOUS_OPERATION_SEQ_NUM,
2280 NEXT_OPERATION_SEQ_NUM,
2281 COUNT_POINT_TYPE,
2282 BACKFLUSH_FLAG,
2283 MINIMUM_TRANSFER_QUANTITY,
2284 DATE_LAST_MOVED,
2285 ATTRIBUTE_CATEGORY,
2286 ATTRIBUTE1,
2287 ATTRIBUTE2,
2288 ATTRIBUTE3,
2289 ATTRIBUTE4,
2290 ATTRIBUTE5,
2291 ATTRIBUTE6,
2292 ATTRIBUTE7,
2293 ATTRIBUTE8,
2294 ATTRIBUTE9,
2295 ATTRIBUTE10,
2296 ATTRIBUTE11,
2297 ATTRIBUTE12,
2298 ATTRIBUTE13,
2299 ATTRIBUTE14,
2300 ATTRIBUTE15,
2301 OPERATION_YIELD,
2302 OPERATION_YIELD_ENABLED,
2303 RECOMMENDED,
2304 CUMULATIVE_SCRAP_QUANTITY,
2305 WSM_OP_SEQ_NUM,
2306 WSM_COSTED_QUANTITY_COMPLETED,
2307 LOWEST_ACCEPTABLE_YIELD)
2308 SELECT p_wip_entity_id,
2309 p_to_job_op_seq_num,
2310 p_org_id,
2311 SYSDATE,
2312 p_user,
2313 SYSDATE,
2314 p_user,
2315 p_login,
2316 DECODE(p_request_id, 0, '', p_request_id),
2317 DECODE(p_program_application_id, 0, '', p_program_application_id),
2318 DECODE(p_commit, 1, p_program_id, -999),
2319 DECODE(p_program_id, 0, '', SYSDATE),
2320 WCO.OPERATION_SEQUENCE_ID,
2321 WCO.STANDARD_OPERATION_ID,
2322 WCO.DEPARTMENT_ID,
2323 WCO.OPERATION_DESCRIPTION,
2324 /*bug 3686872 added nvl(WCO.RECO_SCHEDULED_QUANTITY to the code below removed p_txn_quantity*/
2325 /* Fixed Bug 13699928 . Modified Nvl from 0 to wdj.start_quantity */
2326 --ROUND(nvl(p_txn_quantity, 0), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
2327 ROUND(nvl(WCO.RECO_SCHEDULED_QUANTITY, wdj.start_quantity), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
2328 0, 0, 0, 0, 0, 0,
2329 decode(recommended, 'Y', nvl(RECO_START_DATE, p_txn_date), p_txn_date), --move_enh? populate the reco dates for planned op or ...
2330 decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, p_txn_date), p_txn_date),
2331 decode(recommended, 'Y', nvl(RECO_START_DATE, p_txn_date), p_txn_date),
2332 decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, p_txn_date), p_txn_date),
2333 0, 0,
2334 WCO.COUNT_POINT_TYPE,
2335 WCO.BACKFLUSH_FLAG,
2336 NVL(WCO.MINIMUM_TRANSFER_QUANTITY, 0),
2337 '',
2338 WCO.ATTRIBUTE_CATEGORY,
2339 WCO.ATTRIBUTE1,
2340 WCO.ATTRIBUTE2,
2341 WCO.ATTRIBUTE3,
2342 WCO.ATTRIBUTE4,
2343 WCO.ATTRIBUTE5,
2344 WCO.ATTRIBUTE6,
2345 WCO.ATTRIBUTE7,
2346 WCO.ATTRIBUTE8,
2347 WCO.ATTRIBUTE9,
2348 WCO.ATTRIBUTE10,
2349 WCO.ATTRIBUTE11,
2350 WCO.ATTRIBUTE12,
2351 WCO.ATTRIBUTE13,
2352 WCO.ATTRIBUTE14,
2353 WCO.ATTRIBUTE15,
2354 WCO.YIELD,
2355 to_char(WCO.OPERATION_YIELD_ENABLED),
2356 nvl(RECOMMENDED, 'N'),
2357 WDJ.QUANTITY_SCRAPPED,
2358 WCO.operation_seq_num,
2359 0,
2360 WCO.LOWEST_ACCEPTABLE_YIELD
2361 FROM WSM_COPY_OPERATIONS WCO,
2362 WIP_DISCRETE_JOBS WDJ
2363 WHERE WCO.wip_entity_id=p_wip_entity_id
2364 AND WCO.organization_id=p_org_id
2365 AND WCO.operation_seq_num = p_to_rtg_op_seq_num
2366 AND WDJ.organization_id = WCO.organization_id
2367 AND WDJ.wip_entity_id = WCO.wip_entity_id;
2368
2369 --bugfix 2026218
2370 --copy attachment from operations document attachment defined in the network routing form.
2371 IF SQL%ROWCOUNT > 0 THEN
2372 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
2373 X_FROM_ENTITY_NAME => 'BOM_OPERATION_SEQUENCES',
2374 X_FROM_PK1_VALUE => to_char(p_to_op_seq_id),
2375 X_TO_ENTITY_NAME => 'WSM_LOT_BASED_OPERATIONS',
2376 X_TO_PK1_VALUE => to_char(p_wip_entity_id),
2377 X_TO_PK2_VALUE => to_char(p_to_job_op_seq_num),
2378 X_TO_PK3_VALUE => to_char(p_org_id),
2379 X_CREATED_BY => p_user,
2380 X_LAST_UPDATE_LOGIN => p_login,
2381 X_PROGRAM_APPLICATION_ID => p_program_application_id,
2382 X_PROGRAM_ID => p_program_id,
2383 X_REQUEST_ID => p_request_id);
2384
2385 if (l_debug = 'Y') then -- czh:BUG1995161
2386 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted ' ||sql%rowcount||' records in WO.');
2387 end if; -- czh:BUG1995161
2388 ELSE
2389 if (l_debug = 'Y') then
2390 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||sql%rowcount||' records in WO.');
2391 end if;
2392 x_error_msg := 'WSMOPRNB.('||l_stmt_num||')'|| 'no rows in WO';
2393 x_error_code := -1;
2394 raise e_proc_error;
2395 END IF;
2396
2397 l_stmt_num := 20;
2398 /******
2399 Bug 3571019 - Get SCRAP_ACCOUNT, EST_SCRAP_ABSORB_ACCOUNT from WCO instead of BD
2400 INSERT INTO WIP_OPERATION_YIELDS
2401 (WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
2402 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
2403 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
2404 PROGRAM_APPLICATION_ID, PROGRAM_ID,PROGRAM_UPDATE_DATE,
2405 STATUS, SCRAP_ACCOUNT, EST_SCRAP_ABSORB_ACCOUNT)
2406 SELECT WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, WO.ORGANIZATION_ID,
2407 SYSDATE,
2408 p_user,
2409 SYSDATE,
2410 p_user,
2411 p_login,
2412 DECODE(p_request_id, 0, '', p_request_id),
2413 DECODE(p_program_application_id, 0, '', p_program_application_id),
2414 DECODE(p_commit, 1, p_program_id, -999),
2415 DECODE(p_program_id, 0, '', SYSDATE),
2416 NULL, BD.SCRAP_ACCOUNT, BD.EST_ABSORPTION_ACCOUNT
2417 FROM WIP_OPERATIONS WO,
2418 BOM_DEPARTMENTS BD
2419 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
2420 AND WO.OPERATION_SEQ_NUM = p_to_job_op_seq_num
2421 AND WO.ORGANIZATION_ID = p_org_id
2422 AND WO.DEPARTMENT_ID = BD.DEPARTMENT_ID; --bugfix 1611094
2423 *******/
2424
2425 INSERT INTO WIP_OPERATION_YIELDS
2426 (WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
2427 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
2428 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
2429 PROGRAM_APPLICATION_ID, PROGRAM_ID,PROGRAM_UPDATE_DATE,
2430 STATUS, SCRAP_ACCOUNT, EST_SCRAP_ABSORB_ACCOUNT)
2431 SELECT WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, WO.ORGANIZATION_ID,
2432 SYSDATE,
2433 p_user,
2434 SYSDATE,
2435 p_user,
2436 p_login,
2437 DECODE(p_request_id, 0, '', p_request_id),
2438 DECODE(p_program_application_id, 0, '', p_program_application_id),
2439 DECODE(p_commit, 1, p_program_id, -999),
2440 DECODE(p_program_id, 0, '', SYSDATE),
2441 NULL, WCO.SCRAP_ACCOUNT, WCO.EST_ABSORPTION_ACCOUNT
2442 FROM WIP_OPERATIONS WO,
2443 WSM_COPY_OPERATIONS WCO
2444 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
2445 AND WO.OPERATION_SEQ_NUM = p_to_job_op_seq_num
2446 AND WO.ORGANIZATION_ID = p_org_id
2447 AND WCO.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
2448 AND WCO.OPERATION_SEQ_NUM = WO.WSM_OP_SEQ_NUM;
2449
2450
2451 if (l_debug = 'Y') then
2452 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||sql%rowcount||' records in WOY.');
2453 end if;
2454
2455 l_stmt_num := 30;
2456 --move enh?
2457 BEGIN
2458 --commenting out following sql after code review of bug 3587239
2459 /*********
2460 SELECT 1
2461 INTO l_phantom_exists
2462 FROM WSM_COPY_REQUIREMENT_OPS
2463 WHERE EXISTS (SELECT null
2464 FROM WSM_COPY_REQUIREMENT_OPS WCRO
2465 WHERE WCRO.WIP_ENTITY_ID = p_wip_entity_id
2466 AND WCRO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
2467 AND WCRO.recommended='Y');
2468 *********/
2469 --move enh merging may be needed because of phantoms and component substitution
2470 --adding following sql after code review of bug 3587239
2471 SELECT 1
2472 INTO l_phantom_exists
2473 FROM WSM_COPY_REQUIREMENT_OPS WCRO
2474 WHERE WCRO.WIP_ENTITY_ID = p_wip_entity_id
2475 AND WCRO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
2476 AND WCRO.recommended='Y'
2477 AND WCRO.source_phantom_id <> -1;
2478 EXCEPTION
2479 WHEN no_data_found THEN
2480 l_phantom_exists := 0;
2481
2482 WHEN too_many_rows THEN
2483 l_phantom_exists := 1;
2484 END;
2485
2486 IF (l_debug='Y') THEN
2487 fnd_file.put_line(fnd_file.log, 'WSMOPRNB.add_operation' ||'(stmt_num='||l_stmt_num||') :
2488 l_phantom_exists '||l_phantom_exists);
2489 END IF;
2490
2491 l_stmt_num := 40;
2492
2493 IF (l_phantom_exists=1) THEN
2494 DECLARE
2495
2496 CURSOR c_phantoms IS
2497 SELECT WCRO.COMPONENT_ITEM_ID,
2498 WCRO.organization_id,
2499 WCRO.wip_entity_id,
2500 WCRO.COMPONENT_SEQUENCE_ID,
2501 WCRO.WIP_SUPPLY_TYPE,
2502 decode(WCRO.recommended, 'Y', Nvl(WCRO.reco_date_required, p_txn_date), p_txn_date),
2503 --move enh 115.77 component yield no longer factored in
2504 /* round((WCRO.quantity_per_assembly/decode(WCRO.COMPONENT_YIELD_FACTOR,
2505 0, 1, WCRO.COMPONENT_YIELD_FACTOR)), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION)*p_txn_quantity,
2506 round((WCRO.quantity_per_assembly/decode(WCRO.COMPONENT_YIELD_FACTOR,
2507 0, 1, WCRO.COMPONENT_YIELD_FACTOR)), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
2508 */
2509 --bug 3587239 Round the required quantity to 6 places
2510 WCRO.basis_type, --LBM enh
2511 --Bug 5236684:Component yield factor should include the yield of the source phantom also.
2512 decode(nvl(wcro.basis_type,1),1,nvl(wcro1.component_yield_factor,1),1)*wcro.component_yield_factor, --LBM enh
2513 --component shrinkage
2514 -- ROUND((WCRO.quantity_per_assembly/WCRO.component_yield_factor)
2515 ROUND((WCRO.quantity_per_assembly)
2516 *decode(wcro.basis_type, 2, 1, p_txn_quantity), WSMPCNST.NUMBER_OF_DECIMALS), --LBM enh
2517 --component shrinkage
2518 -- WCRO.quantity_per_assembly,
2519 --Bug 5236684:Bill_quantity_per_assembly should include the Bill_quantity_per_assembly of source phantom also.
2520 decode(nvl(wcro.basis_type,1),1,nvl(WCRO1.bill_quantity_per_assembly,1),1)*WCRO.bill_quantity_per_assembly,
2521 WCRO.supply_subinventory,
2522 WCRO.supply_locator_id,
2523 decode(WCRO.wip_supply_type,
2524 5, 2,
2525 decode(sign(WCRO.quantity_per_assembly),
2526 -1, 2,
2527 1)) mrp_net_flag,
2528 WCRO.component_remarks,
2529 WCRO.attribute_category,
2530 WCRO.attribute1,
2531 WCRO.attribute2,
2532 WCRO.attribute3,
2533 WCRO.attribute4,
2534 WCRO.attribute5,
2535 WCRO.attribute6,
2536 WCRO.attribute7,
2537 WCRO.attribute8,
2538 WCRO.attribute9,
2539 WCRO.attribute10,
2540 WCRO.attribute11,
2541 WCRO.attribute12,
2542 WCRO.attribute13,
2543 WCRO.attribute14,
2544 WCRO.attribute15,
2545 MSI.segment1,
2546 MSI.segment2,
2547 MSI.segment3,
2548 MSI.segment4,
2549 MSI.segment5,
2550 MSI.segment6,
2551 MSI.segment7,
2552 MSI.segment8,
2553 MSI.segment9,
2554 MSI.segment10,
2555 MSI.segment11,
2556 MSI.segment12,
2557 MSI.segment13,
2558 MSI.segment14,
2559 MSI.segment15,
2560 MSI.segment16,
2561 MSI.segment17,
2562 MSI.segment18,
2563 MSI.segment19,
2564 MSI.segment20,
2565 WCRO.department_id
2566 FROM WSM_COPY_REQUIREMENT_OPS WCRO,
2567 WSM_COPY_REQUIREMENT_OPS WCRO1,--Added for bug 5236684
2568 MTL_SYSTEM_ITEMS MSI
2569 WHERE WCRO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
2570 AND WCRO.WIP_ENTITY_ID= p_wip_entity_id
2571 AND MSI.inventory_item_id = WCRO.component_item_id
2572 AND MSI.organization_id = WCRO.organization_id
2573 AND WCRO.RECOMMENDED='Y'
2574 --Added for bug 5236684
2575 AND WCRO.WIP_ENTITY_ID = WCRO1.WIP_ENTITY_ID(+)
2576 AND WCRO1.OPERATION_SEQ_NUM(+)= -1*p_to_rtg_op_seq_num
2577 AND WCRO.source_phantom_id = WCRO1.component_item_id(+)
2578 ORDER BY WCRO.component_item_id, WCRO.wip_supply_type desc;
2579
2580 type t_inventory_item_id is table of WIP_REQUIREMENT_OPERATIONS.inventory_item_id%type index by binary_integer;
2581 type t_organization_id is table of WIP_REQUIREMENT_OPERATIONS.organization_id%type index by binary_integer;
2582 type t_wip_entity_id is table of WIP_REQUIREMENT_OPERATIONS.wip_entity_id%type index by binary_integer;
2583 type t_component_sequence_id is table of WIP_REQUIREMENT_OPERATIONS.component_sequence_id%type index by binary_integer;
2584 type t_wip_supply_type is table of WIP_REQUIREMENT_OPERATIONS.wip_supply_type%type index by binary_integer;
2585 type t_date_required is table of WIP_REQUIREMENT_OPERATIONS.date_required%type index by binary_integer;
2586 type t_basis_type is table of WIP_REQUIREMENT_OPERATIONS.basis_type%type index by binary_integer; --LBM enh
2587 type t_component_yield_factor is table of WIP_REQUIREMENT_OPERATIONS.component_yield_factor%type index by binary_integer; --LBM enh
2588 type t_required_quantity is table of WIP_REQUIREMENT_OPERATIONS.required_quantity%type index by binary_integer;
2589 type t_quantity_per_assembly is table of WIP_REQUIREMENT_OPERATIONS.quantity_per_assembly%type index by binary_integer;
2590 type t_supply_subinventory is table of WIP_REQUIREMENT_OPERATIONS.supply_subinventory%type index by binary_integer;
2591 type t_supply_locator_id is table of WIP_REQUIREMENT_OPERATIONS.supply_locator_id%type index by binary_integer;
2592 type t_mrp_net_flag is table of WIP_REQUIREMENT_OPERATIONS.mrp_net_flag%type index by binary_integer;
2593 type t_comments is table of WIP_REQUIREMENT_OPERATIONS.comments%type index by binary_integer;
2594 type t_attribute_category is table of WIP_REQUIREMENT_OPERATIONS.attribute_category%type index by binary_integer;
2595 type t_attribute1 is table of WIP_REQUIREMENT_OPERATIONS.attribute1%type index by binary_integer;
2596 type t_attribute2 is table of WIP_REQUIREMENT_OPERATIONS.attribute2%type index by binary_integer;
2597 type t_attribute3 is table of WIP_REQUIREMENT_OPERATIONS.attribute3%type index by binary_integer;
2598 type t_attribute4 is table of WIP_REQUIREMENT_OPERATIONS.attribute4%type index by binary_integer;
2599 type t_attribute5 is table of WIP_REQUIREMENT_OPERATIONS.attribute5%type index by binary_integer;
2600 type t_attribute6 is table of WIP_REQUIREMENT_OPERATIONS.attribute6%type index by binary_integer;
2601 type t_attribute7 is table of WIP_REQUIREMENT_OPERATIONS.attribute7%type index by binary_integer;
2602 type t_attribute8 is table of WIP_REQUIREMENT_OPERATIONS.attribute8%type index by binary_integer;
2603 type t_attribute9 is table of WIP_REQUIREMENT_OPERATIONS.attribute9%type index by binary_integer;
2604 type t_attribute10 is table of WIP_REQUIREMENT_OPERATIONS.attribute10%type index by binary_integer;
2605 type t_attribute11 is table of WIP_REQUIREMENT_OPERATIONS.attribute11%type index by binary_integer;
2606 type t_attribute12 is table of WIP_REQUIREMENT_OPERATIONS.attribute12%type index by binary_integer;
2607 type t_attribute13 is table of WIP_REQUIREMENT_OPERATIONS.attribute13%type index by binary_integer;
2608 type t_attribute14 is table of WIP_REQUIREMENT_OPERATIONS.attribute14%type index by binary_integer;
2609 type t_attribute15 is table of WIP_REQUIREMENT_OPERATIONS.attribute15%type index by binary_integer;
2610 type t_segment1 is table of WIP_REQUIREMENT_OPERATIONS.segment1%type index by binary_integer;
2611 type t_segment2 is table of WIP_REQUIREMENT_OPERATIONS.segment2%type index by binary_integer;
2612 type t_segment3 is table of WIP_REQUIREMENT_OPERATIONS.segment3%type index by binary_integer;
2613 type t_segment4 is table of WIP_REQUIREMENT_OPERATIONS.segment4%type index by binary_integer;
2614 type t_segment5 is table of WIP_REQUIREMENT_OPERATIONS.segment5%type index by binary_integer;
2615 type t_segment6 is table of WIP_REQUIREMENT_OPERATIONS.segment6%type index by binary_integer;
2616 type t_segment7 is table of WIP_REQUIREMENT_OPERATIONS.segment7%type index by binary_integer;
2617 type t_segment8 is table of WIP_REQUIREMENT_OPERATIONS.segment8%type index by binary_integer;
2618 type t_segment9 is table of WIP_REQUIREMENT_OPERATIONS.segment9%type index by binary_integer;
2619 type t_segment10 is table of WIP_REQUIREMENT_OPERATIONS.segment10%type index by binary_integer;
2620 type t_segment11 is table of WIP_REQUIREMENT_OPERATIONS.segment11%type index by binary_integer;
2621 type t_segment12 is table of WIP_REQUIREMENT_OPERATIONS.segment12%type index by binary_integer;
2622 type t_segment13 is table of WIP_REQUIREMENT_OPERATIONS.segment13%type index by binary_integer;
2623 type t_segment14 is table of WIP_REQUIREMENT_OPERATIONS.segment14%type index by binary_integer;
2624 type t_segment15 is table of WIP_REQUIREMENT_OPERATIONS.segment15%type index by binary_integer;
2625 type t_segment16 is table of WIP_REQUIREMENT_OPERATIONS.segment16%type index by binary_integer;
2626 type t_segment17 is table of WIP_REQUIREMENT_OPERATIONS.segment17%type index by binary_integer;
2627 type t_segment18 is table of WIP_REQUIREMENT_OPERATIONS.segment18%type index by binary_integer;
2628 type t_segment19 is table of WIP_REQUIREMENT_OPERATIONS.segment19%type index by binary_integer;
2629 type t_segment20 is table of WIP_REQUIREMENT_OPERATIONS.segment20%type index by binary_integer;
2630 type t_department_id is table of WIP_REQUIREMENT_OPERATIONS.department_id%type index by binary_integer;
2631
2632 v_inventory_item_id t_inventory_item_id;
2633 v_organization_id t_organization_id;
2634 v_wip_entity_id t_wip_entity_id;
2635 v_component_sequence_id t_component_sequence_id;
2636 v_wip_supply_type t_wip_supply_type;
2637 v_date_required t_date_required;
2638 v_basis_type t_basis_type; --LBM enh
2639 v_component_yield_factor t_component_yield_factor; --LBM enh
2640 v_required_quantity t_required_quantity;
2641 v_quantity_per_assembly t_quantity_per_assembly;
2642 v_supply_subinventory t_supply_subinventory;
2643 v_supply_locator_id t_supply_locator_id;
2644 v_mrp_net_flag t_mrp_net_flag;
2645 v_comments t_comments;
2646 v_attribute_category t_attribute_category;
2647 v_attribute1 t_attribute1;
2648 v_attribute2 t_attribute2;
2649 v_attribute3 t_attribute3;
2650 v_attribute4 t_attribute4;
2651 v_attribute5 t_attribute5;
2652 v_attribute6 t_attribute6;
2653 v_attribute7 t_attribute7;
2654 v_attribute8 t_attribute8;
2655 v_attribute9 t_attribute9;
2656 v_attribute10 t_attribute10;
2657 v_attribute11 t_attribute11;
2658 v_attribute12 t_attribute12;
2659 v_attribute13 t_attribute13;
2660 v_attribute14 t_attribute14;
2661 v_attribute15 t_attribute15;
2662 v_segment1 t_segment1;
2663 v_segment2 t_segment2;
2664 v_segment3 t_segment3;
2665 v_segment4 t_segment4;
2666 v_segment5 t_segment5;
2667 v_segment6 t_segment6;
2668 v_segment7 t_segment7;
2669 v_segment8 t_segment8;
2670 v_segment9 t_segment9;
2671 v_segment10 t_segment10;
2672 v_segment11 t_segment11;
2673 v_segment12 t_segment12;
2674 v_segment13 t_segment13;
2675 v_segment14 t_segment14;
2676 v_segment15 t_segment15;
2677 v_segment16 t_segment16;
2678 v_segment17 t_segment17;
2679 v_segment18 t_segment18;
2680 v_segment19 t_segment19;
2681 v_segment20 t_segment20;
2682 v_department_id t_department_id;
2683
2684 l_no_of_rows NUMBER;
2685 i NUMBER := 1;
2686 j NUMBER;
2687 BEGIN
2688 l_stmt_num := 50;
2689 OPEN c_phantoms;
2690 --bulk fetch records into PL/SQL tables
2691 fetch c_phantoms bulk collect into
2692 v_inventory_item_id
2693 , v_organization_id
2694 , v_wip_entity_id
2695 , v_component_sequence_id
2696 , v_wip_supply_type
2697 , v_date_required
2698 , v_basis_type --LBM enh
2699 , v_component_yield_factor --LBM enh
2700 , v_required_quantity
2701 , v_quantity_per_assembly
2702 , v_supply_subinventory
2703 , v_supply_locator_id
2704 , v_mrp_net_flag
2705 , v_comments
2706 , v_attribute_category
2707 , v_attribute1
2708 , v_attribute2
2709 , v_attribute3
2710 , v_attribute4
2711 , v_attribute5
2712 , v_attribute6
2713 , v_attribute7
2714 , v_attribute8
2715 , v_attribute9
2716 , v_attribute10
2717 , v_attribute11
2718 , v_attribute12
2719 , v_attribute13
2720 , v_attribute14
2721 , v_attribute15
2722 , v_segment1
2723 , v_segment2
2724 , v_segment3
2725 , v_segment4
2726 , v_segment5
2727 , v_segment6
2728 , v_segment7
2729 , v_segment8
2730 , v_segment9
2731 , v_segment10
2732 , v_segment11
2733 , v_segment12
2734 , v_segment13
2735 , v_segment14
2736 , v_segment15
2737 , v_segment16
2738 , v_segment17
2739 , v_segment18
2740 , v_segment19
2741 , v_segment20
2742 , v_department_id;
2743
2744 IF (l_debug = 'Y') THEN
2745 fnd_file.put_line(fnd_file.log, 'count '||v_inventory_item_id.count);
2746 END IF;
2747
2748 LOOP
2749 l_stmt_num := 60;
2750
2751 IF v_inventory_item_id.exists(i+1) THEN
2752 IF (v_inventory_item_id(i)=v_inventory_item_id(i+1)) THEN
2753 v_required_quantity(i+1) := v_required_quantity(i+1) + v_required_quantity(i);
2754 v_quantity_per_assembly(i+1) := v_quantity_per_assembly(i+1) + v_quantity_per_assembly(i);
2755 v_basis_type.delete(i); --LBM enh
2756 v_component_yield_factor.delete(i); --LBM enh
2757 v_inventory_item_id.delete(i);
2758 v_organization_id.delete(i);
2759 v_wip_entity_id.delete(i);
2760 v_component_sequence_id.delete(i);
2761 v_wip_supply_type.delete(i);
2762 v_date_required.delete(i);
2763 v_required_quantity.delete(i);
2764 v_quantity_per_assembly.delete(i);
2765 v_supply_subinventory.delete(i);
2766 v_supply_locator_id.delete(i);
2767 v_mrp_net_flag.delete(i);
2768 v_comments.delete(i);
2769 v_attribute_category.delete(i);
2770 v_attribute1.delete(i);
2771 v_attribute2.delete(i);
2772 v_attribute3.delete(i);
2773 v_attribute4.delete(i);
2774 v_attribute5.delete(i);
2775 v_attribute6.delete(i);
2776 v_attribute7.delete(i);
2777 v_attribute8.delete(i);
2778 v_attribute9.delete(i);
2779 v_attribute10.delete(i);
2780 v_attribute11.delete(i);
2781 v_attribute12.delete(i);
2782 v_attribute13.delete(i);
2783 v_attribute14.delete(i);
2784 v_attribute15.delete(i);
2785 v_segment1.delete(i);
2786 v_segment2.delete(i);
2787 v_segment3.delete(i);
2788 v_segment4.delete(i);
2789 v_segment5.delete(i);
2790 v_segment6.delete(i);
2791 v_segment7.delete(i);
2792 v_segment8.delete(i);
2793 v_segment9.delete(i);
2794 v_segment10.delete(i);
2795 v_segment11.delete(i);
2796 v_segment12.delete(i);
2797 v_segment13.delete(i);
2798 v_segment14.delete(i);
2799 v_segment15.delete(i);
2800 v_segment16.delete(i);
2801 v_segment17.delete(i);
2802 v_segment18.delete(i);
2803 v_segment19.delete(i);
2804 v_segment20.delete(i);
2805 v_department_id.delete(i);
2806
2807 END IF;
2808 i := i+1;
2809 ELSE
2810 EXIT;
2811 END IF;
2812 END LOOP;
2813
2814 IF (l_debug = 'Y') THEN
2815 fnd_file.put_line(fnd_file.log, 'count after consolidation'||v_inventory_item_id.count);
2816 END IF;
2817
2818 l_no_of_rows := v_inventory_item_id.last;
2819 j := l_no_of_rows + 1;
2820 FOR i in 1..l_no_of_rows LOOP
2821 IF v_inventory_item_id.exists(i) THEN
2822 v_inventory_item_id(j) := v_inventory_item_id(i);
2823 v_organization_id(j) := v_organization_id(i);
2824 v_wip_entity_id(j) := v_wip_entity_id(i);
2825 v_component_sequence_id(j) := v_component_sequence_id(i);
2826 v_wip_supply_type(j) := v_wip_supply_type(i);
2827 v_date_required(j) := v_date_required(i);
2828 v_basis_type(j) := v_basis_type(i); --LBM enh
2829 v_component_yield_factor(j) := v_component_yield_factor(i); --LBM enh
2830 v_required_quantity(j) := v_required_quantity(i);
2831 v_quantity_per_assembly(j) := v_quantity_per_assembly(i);
2832 v_supply_subinventory(j) := v_supply_subinventory(i);
2833 v_supply_locator_id(j) := v_supply_locator_id(i);
2834 v_mrp_net_flag(j) := v_mrp_net_flag(i);
2835 v_comments(j) := v_comments(i);
2836 v_attribute_category(j) := v_attribute_category(i);
2837 v_attribute1(j) := v_attribute1(i);
2838 v_attribute2(j) := v_attribute2(i);
2839 v_attribute3(j) := v_attribute3(i);
2840 v_attribute4(j) := v_attribute4(i);
2841 v_attribute5(j) := v_attribute5(i);
2842 v_attribute6(j) := v_attribute6(i);
2843 v_attribute7(j) := v_attribute7(i);
2844 v_attribute8(j) := v_attribute8(i);
2845 v_attribute9(j) := v_attribute9(i);
2846 v_attribute10(j) := v_attribute10(i);
2847 v_attribute11(j) := v_attribute11(i);
2848 v_attribute12(j) := v_attribute12(i);
2849 v_attribute13(j) := v_attribute13(i);
2850 v_attribute14(j) := v_attribute14(i);
2851 v_attribute15(j) := v_attribute15(i);
2852 v_segment1(j) := v_segment1(i);
2853 v_segment2(j) := v_segment2(i);
2854 v_segment3(j) := v_segment3(i);
2855 v_segment4(j) := v_segment4(i);
2856 v_segment5(j) := v_segment5(i);
2857 v_segment6(j) := v_segment6(i);
2858 v_segment7(j) := v_segment7(i);
2859 v_segment8(j) := v_segment8(i);
2860 v_segment9(j) := v_segment9(i);
2861 v_segment10(j) := v_segment10(i);
2862 v_segment11(j) := v_segment11(i);
2863 v_segment12(j) := v_segment12(i);
2864 v_segment13(j) := v_segment13(i);
2865 v_segment14(j) := v_segment14(i);
2866 v_segment15(j) := v_segment15(i);
2867 v_segment16(j) := v_segment16(i);
2868 v_segment17(j) := v_segment17(i);
2869 v_segment18(j) := v_segment18(i);
2870 v_segment19(j) := v_segment19(i);
2871 v_segment20(j) := v_segment20(i);
2872 v_department_id(j) := v_department_id(i);
2873
2874 j := j+1;
2875 END IF;
2876 END LOOP;
2877
2878 l_stmt_num := 70;
2879 FORALL i in (l_no_of_rows + 1)..(j-1)
2880 --move enh changed released quantity to start_qty*qpa on 21 Oct 03
2881 INSERT INTO WIP_REQUIREMENT_OPERATIONS
2882 (inventory_item_id,
2883 organization_id,
2884 wip_entity_id,
2885 operation_seq_num,
2886 repetitive_schedule_id,
2887 last_update_date,
2888 last_updated_by,
2889 creation_date,
2890 created_by,
2891 last_update_login,
2892 component_sequence_id,
2893 wip_supply_type,
2894 date_required,
2895 basis_type, --LBM enh
2896 required_quantity,
2897 quantity_issued,
2898 quantity_per_assembly,
2899 supply_subinventory,
2900 supply_locator_id,
2901 mrp_net_flag,
2902 comments,
2903 attribute_category,
2904 attribute1,
2905 attribute2,
2906 attribute3,
2907 attribute4,
2908 attribute5,
2909 attribute6,
2910 attribute7,
2911 attribute8,
2912 attribute9,
2913 attribute10,
2914 attribute11,
2915 attribute12,
2916 attribute13,
2917 attribute14,
2918 attribute15,
2919 segment1,
2920 segment2,
2921 segment3,
2922 segment4,
2923 segment5,
2924 segment6,
2925 segment7,
2926 segment8,
2927 segment9,
2928 segment10,
2929 segment11,
2930 segment12,
2931 segment13,
2932 segment14,
2933 segment15,
2934 segment16,
2935 segment17,
2936 segment18,
2937 segment19,
2938 segment20,
2939 department_id,
2940 released_quantity,
2941 component_yield_factor) --component shrinkage
2942 VALUES (v_inventory_item_id(i)
2943 , v_organization_id(i)
2944 , v_wip_entity_id(i)
2945 , p_to_job_op_seq_num
2946 , NULL,
2947 SYSDATE,
2948 p_user,
2949 SYSDATE,
2950 p_user,
2951 p_login
2952 , v_component_sequence_id(i)
2953 , v_wip_supply_type(i)
2954 , v_date_required(i)
2955 , v_basis_type(i) --LBM enh
2956 , v_required_quantity(i)
2957 , 0
2958 , v_quantity_per_assembly(i)
2959 , v_supply_subinventory(i)
2960 , v_supply_locator_id(i)
2961 , v_mrp_net_flag(i)
2962 , v_comments(i)
2963 , v_attribute_category(i)
2964 , v_attribute1(i)
2965 , v_attribute2(i)
2966 , v_attribute3(i)
2967 , v_attribute4(i)
2968 , v_attribute5(i)
2969 , v_attribute6(i)
2970 , v_attribute7(i)
2971 , v_attribute8(i)
2972 , v_attribute9(i)
2973 , v_attribute10(i)
2974 , v_attribute11(i)
2975 , v_attribute12(i)
2976 , v_attribute13(i)
2977 , v_attribute14(i)
2978 , v_attribute15(i)
2979 , v_segment1(i)
2980 , v_segment2(i)
2981 , v_segment3(i)
2982 , v_segment4(i)
2983 , v_segment5(i)
2984 , v_segment6(i)
2985 , v_segment7(i)
2986 , v_segment8(i)
2987 , v_segment9(i)
2988 , v_segment10(i)
2989 , v_segment11(i)
2990 , v_segment12(i)
2991 , v_segment13(i)
2992 , v_segment14(i)
2993 , v_segment15(i)
2994 , v_segment16(i)
2995 , v_segment17(i)
2996 , v_segment18(i)
2997 , v_segment19(i)
2998 , v_segment20(i)
2999 , v_department_id(i)
3000 --bug 3587239 Round the released quantity to 6 places
3001 , ROUND( decode(v_basis_type(i), 2, 1, p_start_quantity)
3002 *(v_quantity_per_assembly(i)/v_component_yield_factor(i)), WSMPCNST.NUMBER_OF_DECIMALS) --LBM enh
3003 , v_component_yield_factor(i));
3004 END;
3005 ELSE
3006
3007 INSERT INTO WIP_REQUIREMENT_OPERATIONS
3008 (inventory_item_id,
3009 organization_id,
3010 wip_entity_id,
3011 operation_seq_num,
3012 repetitive_schedule_id,
3013 last_update_date,
3014 last_updated_by,
3015 creation_date,
3016 created_by,
3017 last_update_login,
3018 component_sequence_id,
3019 wip_supply_type,
3020 date_required,
3021 basis_type, --LBM enh
3022 required_quantity,
3023 quantity_issued,
3024 quantity_per_assembly,
3025 supply_subinventory,
3026 supply_locator_id,
3027 mrp_net_flag,
3028 comments,
3029 attribute_category,
3030 attribute1,
3031 attribute2,
3032 attribute3,
3033 attribute4,
3034 attribute5,
3035 attribute6,
3036 attribute7,
3037 attribute8,
3038 attribute9,
3039 attribute10,
3040 attribute11,
3041 attribute12,
3042 attribute13,
3043 attribute14,
3044 attribute15,
3045 segment1,
3046 segment2,
3047 segment3,
3048 segment4,
3049 segment5,
3050 segment6,
3051 segment7,
3052 segment8,
3053 segment9,
3054 segment10,
3055 segment11,
3056 segment12,
3057 segment13,
3058 segment14,
3059 segment15,
3060 segment16,
3061 segment17,
3062 segment18,
3063 segment19,
3064 segment20,
3065 department_id,
3066 --VJ: Start additions for Costing enhancement for WLTEnh--
3067 costed_quantity_issued,
3068 costed_quantity_relieved,
3069 --VJ: End additions for Costing enhancement for WLTEnh--
3070 released_quantity,
3071 component_yield_factor) --component shrinkage
3072 SELECT WCRO.COMPONENT_ITEM_ID,
3073 WCRO.organization_id,
3074 WCRO.wip_entity_id,
3075 p_to_job_op_seq_num,
3076 NULL,
3077 SYSDATE,
3078 p_user,
3079 SYSDATE,
3080 p_user,
3081 p_login,
3082 WCRO.COMPONENT_SEQUENCE_ID,
3083 WCRO.WIP_SUPPLY_TYPE,
3084 decode(recommended, 'Y', Nvl(WCRO.reco_date_required, p_txn_date), p_txn_date),
3085 --move enh 115.77 component yield no longer factored in
3086 /* round((WCRO.quantity_per_assembly/decode(WCRO.COMPONENT_YIELD_FACTOR,
3087 0, 1, WCRO.COMPONENT_YIELD_FACTOR)), WSMPCNST.NUMBER_OF_DECIMALS)*p_txn_quantity,
3088 */
3089
3090 /* round((WCRO.quantity_per_assembly/decode(WCRO.COMPONENT_YIELD_FACTOR,
3091 0, 1, WCRO.COMPONENT_YIELD_FACTOR)), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
3092 */
3093 --bug 3587239 Round the required quantity to 6 places
3094 WCRO.basis_type, --LBM enh
3095 -- Component shrinkage
3096 -- ROUND((WCRO.quantity_per_assembly/wcro.component_yield_factor)
3097 ROUND((WCRO.quantity_per_assembly)
3098 * decode(wcro.basis_type, 2, 1, p_txn_quantity), WSMPCNST.NUMBER_OF_DECIMALS), --LBM enh
3099 0,
3100 --component shrinkage
3101 -- WCRO.quantity_per_assembly,
3102 WCRO.bill_quantity_per_assembly,
3103 WCRO.supply_subinventory,
3104 WCRO.supply_locator_id,
3105 decode(WCRO.wip_supply_type,
3106 5, 2,
3107 decode(sign(WCRO.quantity_per_assembly),
3108 -1, 2,
3109 1)) mrp_net_flag,
3110 WCRO.component_remarks,
3111 WCRO.attribute_category,
3112 WCRO.attribute1,
3113 WCRO.attribute2,
3114 WCRO.attribute3,
3115 WCRO.attribute4,
3116 WCRO.attribute5,
3117 WCRO.attribute6,
3118 WCRO.attribute7,
3119 WCRO.attribute8,
3120 WCRO.attribute9,
3121 WCRO.attribute10,
3122 WCRO.attribute11,
3123 WCRO.attribute12,
3124 WCRO.attribute13,
3125 WCRO.attribute14,
3126 WCRO.attribute15,
3127 MSI.segment1,
3128 MSI.segment2,
3129 MSI.segment3,
3130 MSI.segment4,
3131 MSI.segment5,
3132 MSI.segment6,
3133 MSI.segment7,
3134 MSI.segment8,
3135 MSI.segment9,
3136 MSI.segment10,
3137 MSI.segment11,
3138 MSI.segment12,
3139 MSI.segment13,
3140 MSI.segment14,
3141 MSI.segment15,
3142 MSI.segment16,
3143 MSI.segment17,
3144 MSI.segment18,
3145 MSI.segment19,
3146 MSI.segment20,
3147 WCRO.department_id,
3148 NULL,
3149 NULL,
3150 --bug 3587239 Round the released quantity to 6 places
3151 ROUND(decode(wcro.basis_type, 2, 1, p_start_quantity)
3152 *(WCRO.quantity_per_assembly/wcro.component_yield_factor), WSMPCNST.NUMBER_OF_DECIMALS), --LBM enh
3153 WCRO.component_yield_factor --component shrinkage
3154 FROM WSM_COPY_REQUIREMENT_OPS WCRO,
3155 MTL_SYSTEM_ITEMS MSI
3156 WHERE WCRO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
3157 AND WCRO.WIP_ENTITY_ID= p_wip_entity_id
3158 AND MSI.inventory_item_id = WCRO.component_item_id
3159 AND MSI.organization_id = WCRO.organization_id
3160 AND WCRO.RECOMMENDED='Y';
3161
3162 END IF;
3163
3164 if (l_debug = 'Y') then
3165 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||SQL%ROWCOUNT||' records in WRO.');
3166 end if;
3167
3168 l_stmt_num := 80;
3169
3170 INSERT INTO WIP_OPERATION_RESOURCES
3171 (WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
3172 ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID,
3173 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
3174 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
3175 PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
3176 RESOURCE_ID, UOM_CODE,
3177 BASIS_TYPE, USAGE_RATE_OR_AMOUNT, ACTIVITY_ID,
3178 SCHEDULED_FLAG, ASSIGNED_UNITS,
3179 /* ST : Detailed Scheduling */
3180 maximum_assigned_units,
3181 batch_id,
3182 firm_flag,
3183 group_sequence_id,
3184 group_sequence_number,
3185 parent_resource_seq,
3186 /* ST : Detailed Scheduling */
3187 AUTOCHARGE_TYPE,
3188 STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS, APPLIED_RESOURCE_VALUE,
3189 START_DATE, COMPLETION_DATE,
3190 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
3191 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
3192 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
3193 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
3194 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
3195 ATTRIBUTE15,
3196 SCHEDULE_SEQ_NUM, --bugfix 2493065
3197 SUBSTITUTE_GROUP_NUM,
3198 REPLACEMENT_GROUP_NUM,
3199 PRINCIPLE_FLAG,
3200 SETUP_ID)
3201 SELECT WCOR.WIP_ENTITY_ID
3202 , p_to_job_op_seq_num
3203 , WCOR.RESOURCE_SEQ_NUM
3204 , WCOR.ORGANIZATION_ID
3205 , NULL
3206 , SYSDATE
3207 , p_user
3208 , SYSDATE
3209 , p_user
3210 , p_login
3211 , DECODE(p_request_id, 0, '', p_request_id)
3212 , DECODE(p_program_application_id, 0, '', p_program_application_id)
3213 , DECODE(p_commit, 1, p_program_id, -999)
3214 , DECODE(p_program_id, 0, '', SYSDATE)
3215 , WCOR.RESOURCE_ID
3216 , WCOR.UOM_CODE
3217 , WCOR.BASIS_TYPE
3218 , WCOR.USAGE_RATE_OR_AMOUNT
3219 , WCOR.ACTIVITY_ID
3220 , WCOR.SCHEDULE_FLAG
3221 , WCOR.ASSIGNED_UNITS
3222 /* ST : Detailed Scheduling */
3223 , WCOR.MAX_ASSIGNED_UNITS
3224 , WCOR.batch_id
3225 , WCOR.firm_type
3226 , WCOR.group_sequence_id
3227 , WCOR.group_sequence_num
3228 , WCOR.parent_resource_seq_num
3229 /* ST : Detailed Scheduling */
3230 , WCOR.AUTOCHARGE_TYPE
3231 , WCOR.STANDARD_RATE_FLAG
3232 , 0
3233 , 0
3234 , decode(recommended, 'Y', nvl(RECO_START_DATE, p_txn_date), p_txn_date)
3235 , decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, p_txn_date), p_txn_date)
3236 , WCOR.ATTRIBUTE_CATEGORY
3237 , WCOR.ATTRIBUTE1
3238 , WCOR.ATTRIBUTE2
3239 , WCOR.ATTRIBUTE3
3240 , WCOR.ATTRIBUTE4
3241 , WCOR.ATTRIBUTE5
3242 , WCOR.ATTRIBUTE6
3243 , WCOR.ATTRIBUTE7
3244 , WCOR.ATTRIBUTE8
3245 , WCOR.ATTRIBUTE9
3246 , WCOR.ATTRIBUTE10
3247 , WCOR.ATTRIBUTE11
3248 , WCOR.ATTRIBUTE12
3249 , WCOR.ATTRIBUTE13
3250 , WCOR.ATTRIBUTE14
3251 , WCOR.ATTRIBUTE15
3252 , WCOR.SCHEDULE_SEQ_NUM
3253 , WCOR.SUBSTITUTE_GROUP_NUM
3254 , WCOR.REPLACEMENT_GROUP_NUM
3255 , WCOR.PRINCIPLE_FLAG
3256 , WCOR.SETUP_ID
3257 FROM WSM_COPY_OP_RESOURCES WCOR
3258 WHERE WCOR.ORGANIZATION_ID = p_org_id
3259 AND WCOR.WIP_ENTITY_ID = p_Wip_Entity_Id
3260 AND WCOR.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
3261 AND WCOR.recommended='Y';
3262
3263 l_wor_count := SQL%ROWCOUNT;
3264
3265 if (l_debug = 'Y') then
3266 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: l_wor_count '||l_wor_count||
3267 ' l_wor_reco_res '||l_wor_reco_res);
3268 end if;
3269
3270 l_stmt_num := 80;
3271 --MES added department_id
3272 INSERT INTO WIP_SUB_OPERATION_RESOURCES
3273 (wip_entity_id,
3274 operation_seq_num,
3275 resource_seq_num,
3276 organization_id,
3277 repetitive_schedule_id,
3278 last_update_date,
3279 last_updated_by,
3280 creation_date,
3281 created_by,
3282 last_update_login,
3283 resource_id,
3284 uom_code,
3285 basis_type,
3286 usage_rate_or_amount,
3287 activity_id,
3288 scheduled_flag,
3289 assigned_units,
3290 maximum_assigned_units, /* ST : Detailed Scheduling */
3291 autocharge_type,
3292 standard_rate_flag,
3293 applied_resource_units,
3294 applied_resource_value,
3295 attribute_category,
3296 attribute1,
3297 attribute2,
3298 attribute3,
3299 attribute4,
3300 attribute5,
3301 attribute6,
3302 attribute7,
3303 attribute8,
3304 attribute9,
3305 attribute10,
3306 attribute11,
3307 attribute12,
3308 attribute13,
3309 attribute14,
3310 attribute15,
3311 completion_date,
3312 start_date,
3313 schedule_seq_num,
3314 substitute_group_num,
3315 replacement_group_num,
3316 setup_id,
3317 department_id)
3318 SELECT WCOR.wip_entity_id,
3319 p_to_job_op_seq_num,
3320 WCOR.resource_seq_num,
3321 WCOR.organization_id,
3322 null,
3323 SYSDATE ,
3324 p_user,
3325 SYSDATE,
3326 p_user,
3327 p_login,
3328 WCOR.resource_id,
3329 WCOR.uom_code,
3330 WCOR.basis_type,
3331 WCOR.usage_rate_or_amount,
3332 WCOR.activity_id,
3333 WCOR.schedule_flag,
3334 WCOR.assigned_units,
3335 WCOR.max_assigned_units,
3336 WCOR.autocharge_type,
3337 WCOR.standard_rate_flag,
3338 0, --WCOR.applied_resource_units,--move enh?
3339 0, -- WCOR.applied_resource_value, --move enh?
3340 WCOR.attribute_category,
3341 WCOR.attribute1,
3342 WCOR.attribute2,
3343 WCOR.attribute3,
3344 WCOR.attribute4,
3345 WCOR.attribute5,
3346 WCOR.attribute6,
3347 WCOR.attribute7,
3348 WCOR.attribute8,
3349 WCOR.attribute9,
3350 WCOR.attribute10,
3351 WCOR.attribute11,
3352 WCOR.attribute12,
3353 WCOR.attribute13,
3354 WCOR.attribute14,
3355 WCOR.attribute15,
3356 --as per Zhaohui copying the dates from WCOR
3357 nvl(WCOR.RECO_START_DATE, p_txn_date),
3358 nvl(WCOR.RECO_COMPLETION_DATE, p_txn_date),
3359 WCOR.schedule_seq_num,
3360 WCOR.substitute_group_num,
3361 WCOR.replacement_group_num,
3362 WCOR.setup_id,
3363 WCOR.department_id
3364 FROM WSM_COPY_OP_RESOURCES WCOR
3365 WHERE WCOR.ORGANIZATION_ID = p_org_id
3366 AND WCOR.WIP_ENTITY_ID = p_Wip_Entity_Id
3367 AND WCOR.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
3368 AND WCOR.PHANTOM_ITEM_ID IS NULL
3369 AND WCOR.recommended<>'Y';
3370
3371 if (l_debug = 'Y') then
3372 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||SQL%ROWCOUNT||
3373 ' records in WIP_SUB_OPERATION_RESOURCES.');
3374 end if;
3375
3376 l_stmt_num := 90;
3377
3378 IF (l_wor_count > 0) THEN
3379 INSERT into WIP_OP_RESOURCE_INSTANCES
3380 (WIP_ENTITY_ID
3381 , OPERATION_SEQ_NUM
3382 , RESOURCE_SEQ_NUM
3383 , ORGANIZATION_ID
3384 , LAST_UPDATE_DATE
3385 , LAST_UPDATED_BY
3386 , CREATION_DATE
3387 , CREATED_BY
3388 , LAST_UPDATE_LOGIN
3389 , INSTANCE_ID
3390 , SERIAL_NUMBER
3391 , START_DATE
3392 , COMPLETION_DATE
3393 , BATCH_ID)
3394 SELECT WCORI.WIP_ENTITY_ID
3395 , p_to_job_op_seq_num
3396 , WCORI.RESOURCE_SEQ_NUM
3397 , WCORI.ORGANIZATION_ID
3398 , SYSDATE
3399 , p_user
3400 , SYSDATE
3401 , p_user
3402 , p_login
3403 , WCORI.INSTANCE_ID
3404 , WCORI.SERIAL_NUMBER
3405 , WCORI.START_DATE
3406 , WCORI.COMPLETION_DATE
3407 , WCORI.BATCH_ID
3408 FROM WSM_COPY_OP_RESOURCE_INSTANCES WCORI
3409 -- WIP_OPERATION_RESOURCES WOR Bug 5478658 join with WOR not required
3410 WHERE WCORI.WIP_ENTITY_ID= p_wip_entity_id
3411 AND WCORI.Operation_seq_num = p_to_rtg_op_seq_num ;
3412
3413 -- Bug 5478658 Join conditions with WOR removed
3414 -- AND WOR.WIP_ENTITY_ID= WCORI.WIP_ENTITY_ID
3415 -- AND WOR.Operation_seq_num= WCORI.Operation_seq_num
3416 -- AND WOR.RESOURCE_SEQ_NUM= WCORI.RESOURCE_SEQ_NUM;
3417
3418 if (l_debug = 'Y') then
3419 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||SQL%ROWCOUNT
3420 ||' rows in WIP_OP_RESOURCE_INSTANCES');
3421 end if;
3422
3423 INSERT into wip_operation_resource_usage
3424 (WIP_ENTITY_ID,
3425 OPERATION_SEQ_NUM,
3426 RESOURCE_SEQ_NUM,
3427 REPETITIVE_SCHEDULE_ID,
3428 ORGANIZATION_ID,
3429 START_DATE,
3430 COMPLETION_DATE,
3431 ASSIGNED_UNITS,
3432 -- resource_hours, /* ST : Detailed scheduling */
3433 LAST_UPDATE_DATE,
3434 LAST_UPDATED_BY,
3435 CREATION_DATE,
3436 CREATED_BY,
3437 LAST_UPDATE_LOGIN,
3438 REQUEST_ID,
3439 PROGRAM_APPLICATION_ID,
3440 PROGRAM_ID,
3441 PROGRAM_UPDATE_DATE,
3442 INSTANCE_ID,
3443 SERIAL_NUMBER,
3444 CUMULATIVE_PROCESSING_TIME)
3445 SELECT WCORU.WIP_ENTITY_ID,
3446 p_to_job_op_seq_num,
3447 WCORU.RESOURCE_SEQ_NUM,
3448 null,
3449 WCORU.ORGANIZATION_ID,
3450 WCORU.START_DATE,
3451 WCORU.COMPLETION_DATE,
3452 WCORU.ASSIGNED_UNITS,
3453 -- WCORU.RESOURCE_HOURS, /* ST : Detailed scheduling */
3454 SYSDATE ,
3455 p_user,
3456 SYSDATE,
3457 p_user,
3458 p_login,
3459 DECODE(p_request_id, 0, '', p_request_id),
3460 DECODE(p_program_application_id, 0, '', p_program_application_id),
3461 DECODE(p_commit, 1, p_program_id, -999),
3462 DECODE(p_program_id, 0, '', SYSDATE),
3463 WCORU.INSTANCE_ID,
3464 WCORU.SERIAL_NUMBER,
3465 WCORU.CUMULATIVE_PROCESSING_TIME
3466 FROM -- WIP_OPERATION_RESOURCES WOR, Bug 5478658 join with WOR not required
3467 WSM_COPY_OP_RESOURCE_USAGE WCORU
3468 WHERE WCORU.WIP_ENTITY_ID= p_wip_entity_id
3469 AND WCORU.Operation_seq_num = p_to_rtg_op_seq_num ;
3470
3471 -- Bug 5478658 Join conditions with WOR removed
3472 -- AND WOR.WIP_ENTITY_ID= WCORU.WIP_ENTITY_ID
3473 -- AND WOR.Operation_seq_num= WCORU.Operation_seq_num
3474 -- AND WOR.RESOURCE_SEQ_NUM= WCORU.RESOURCE_SEQ_NUM;
3475
3476 END IF;
3477
3478 if (l_debug = 'Y') then
3479 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||SQL%ROWCOUNT
3480 ||' rows in wip_operation_resource_usage');
3481 end if;
3482
3483 l_stmt_num := 100;
3484
3485 if (l_debug = 'Y') then -- czh:BUG1995161
3486 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Calling set_prev_next..');
3487 end if; -- czh:BUG1995161
3488
3489 set_prev_next(p_wip_entity_id,
3490 p_org_id,
3491 x_error_code,
3492 x_error_msg);
3493
3494 IF (x_error_code <> 0) THEN
3495 raise e_proc_error;
3496 END IF;
3497
3498 copy_to_op_mes_info(
3499 p_wip_entity_id => p_wip_entity_id
3500 , p_to_job_op_seq_num => p_to_job_op_seq_num
3501 , p_to_rtg_op_seq_num => p_to_rtg_op_seq_num
3502 , p_txn_quantity => p_txn_quantity
3503 , p_user => p_user
3504 , p_login => p_login
3505 , x_return_status => l_return_status
3506 , x_msg_count => l_msg_count
3507 , x_msg_data => l_msg_data
3508 );
3509
3510 IF l_return_status = g_ret_error THEN
3511 RAISE FND_API.G_EXC_ERROR;
3512 l_stmt_num := 370;
3513 IF (l_msg_count = 1) THEN
3514 x_error_code := -1;
3515 x_error_msg := l_msg_data;
3516 ELSE
3517 FOR i IN 1..l_msg_count LOOP
3518 x_error_code := -1;
3519 x_error_msg := substr(x_error_msg||fnd_msg_pub.get, 1, 4000);
3520 END LOOP;
3521 END IF;
3522 RAISE FND_API.G_EXC_ERROR;
3523 ELSIF l_return_status = g_ret_unexpected THEN
3524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3525 END IF;
3526
3527 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
3528 IF (l_return_status = g_ret_success) THEN
3529 l_msg_tokens.delete;
3530 WSM_log_PVT.logMessage (
3531 p_module_name => l_module ,
3532 p_msg_text => 'WSMPOPRN.copy_to_op_mes_info returned successfully',
3533 p_stmt_num => l_stmt_num ,
3534 p_msg_tokens => l_msg_tokens ,
3535 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
3536 p_run_log_level => l_log_level
3537 );
3538 END IF;
3539 END IF;
3540
3541 EXCEPTION
3542 -- This just means that the operation was already inserted
3543 WHEN e_proc_error THEN
3544 x_error_code := -1;
3545 x_error_msg := 'WSMPOPRN.copy_plan_to_execution('||l_stmt_num||'): '||x_error_msg;
3546 fnd_file.put_line(fnd_file.log, x_error_msg);
3547
3548 WHEN dup_val_on_index THEN
3549 BEGIN
3550 IF (p_dup_val_ignore='N') THEN
3551 x_error_code := SQLCODE;
3552 x_error_msg := 'WSMOPRNB.copy_plan_to_execution('||l_stmt_num||')'|| substr(SQLERRM,1,2000);
3553 ELSE
3554 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution other excpn: (stmt'||l_stmt_num ||'): dup_val_on_index');
3555 END IF;
3556 END;
3557
3558 WHEN others THEN
3559 x_error_code := SQLCODE;
3560 x_error_msg := 'WSMOPRNB.copy_plan_to_execution('||l_stmt_num||')'|| substr(SQLERRM,1,2000);
3561 -- czh:BUG1995161
3562 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution other excpn: (stmt'||l_stmt_num ||'):'||x_error_msg);
3563
3564 END copy_plan_to_execution;
3565
3566 PROCEDURE call_infinite_scheduler(
3567 x_error_code OUT NOCOPY NUMBER,
3568 x_error_msg OUT NOCOPY VARCHAR2,
3569 p_jump_flag IN VARCHAR2,
3570 p_wip_entity_id IN NUMBER,
3571 p_org_id IN NUMBER,
3572 p_to_op_seq_id IN NUMBER,
3573 p_fm_job_op_seq_num IN NUMBER,
3574 p_to_job_op_seq_num IN NUMBER,
3575 p_scheQuantity IN NUMBER)
3576 IS
3577 l_recommended_op VARCHAR2(1);
3578 x_returnStatus VARCHAR2(1);
3579 l_reco_start_date DATE;
3580 l_reco_completion_date DATE;
3581 l_infi_start_date DATE;
3582 l_stmt_num NUMBER;
3583 BEGIN
3584 l_stmt_num := 10;
3585 BEGIN
3586 SELECT nvl(recommended, 'N'), RECO_START_DATE, reco_completion_date
3587 INTO l_recommended_op, l_reco_start_date, l_reco_completion_date
3588 FROM WSM_COPY_OPERATIONS
3589 WHERE wip_entity_id = p_wip_entity_id
3590 AND operation_sequence_id = p_to_op_seq_id;
3591 EXCEPTION
3592 WHEN no_data_found THEN
3593 IF (l_debug = 'Y') THEN
3594 fnd_file.put_line(fnd_file.log, 'To Op not present in WSM_COPY_OPERATIONS');
3595 END IF;
3596 null;
3597 END;
3598
3599 IF ((p_jump_flag = 'Y') OR (l_recommended_op <> 'Y') OR (l_reco_start_date IS NULL)
3600 OR (l_reco_completion_date IS NULL)) THEN
3601 l_stmt_num := 20;
3602
3603 SELECT last_unit_completion_date
3604 INTO l_infi_start_date
3605 FROM WIP_OPERATIONS
3606 WHERE wip_entity_id = p_wip_entity_id
3607 AND organization_id = p_org_id
3608 AND operation_seq_num = p_fm_job_op_seq_num;
3609
3610 l_stmt_num := 30;
3611 wsm_infinite_scheduler_pvt.schedule(
3612 p_initMsgList => fnd_api.g_true,
3613 p_endDebug => fnd_api.g_true,
3614 p_orgID => p_org_id,
3615 p_wipEntityID => p_wip_entity_id,
3616 p_scheduleMode => WIP_CONSTANTS.CURRENT_OP,
3617 p_startDate => l_infi_start_date,
3618 p_endDate => null,
3619 p_opSeqNum => -p_to_job_op_seq_num,
3620 p_scheQuantity => p_scheQuantity,
3621 x_returnStatus => x_returnStatus,
3622 x_errorMsg => x_error_msg);
3623
3624 IF (x_returnStatus <> fnd_api.g_ret_sts_success) THEN
3625 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation calling wsm_infinite_scheduler_pvt.schedule: '||x_error_msg);
3626 x_error_code := -1;
3627 return ;
3628 ELSE
3629 IF (l_debug = 'Y') THEN
3630 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation calling wsm_infinite_scheduler_pvt.schedule returned success');
3631 END IF;
3632 END IF;
3633 END IF;
3634 x_error_code := 0;
3635 EXCEPTION
3636 WHEN others THEN
3637 x_error_code := SQLCODE;
3638 x_error_msg := 'WSMOPRNB.call_infinite_scheduler('||l_stmt_num||')'|| substr(SQLERRM,1,2000);
3639 -- czh:BUG1995161
3640 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.call_infinite_scheduler other excpn: (stmt'||l_stmt_num ||'):'||x_error_msg);
3641 END call_infinite_scheduler;
3642
3643 --bug 3162358 115.78 added this new procedure which will be called from WSMTXSFM.pld and WSMLBMIB.pls
3644 /*
3645 PROCEDURE upd_cumulative_scrap_qty(
3646 x_error_code OUT NOCOPY NUMBER
3647 , x_error_msg OUT NOCOPY VARCHAR2
3648 , p_org_id IN NUMBER
3649 , p_wip_entity_id IN NUMBER
3650 , p_job_op_seq_num IN NUMBER)
3651 IS
3652 l_stmt_num NUMBER;
3653 BEGIN
3654 l_stmt_num := 10;
3655 UPDATE WIP_OPERATIONS WO
3656 SET cumulative_scrap_quantity =
3657 (SELECT quantity_scrapped
3658 FROM WIP_DISCRETE_JOBS WDJ
3659 WHERE WDJ.wip_entity_id = p_wip_entity_id
3660 AND WDJ.organization_id = p_org_id)
3661 WHERE WO.wip_entity_id = p_wip_entity_id
3662 AND WO.organization_id = p_org_id
3663 AND WO.operation_seq_num = p_job_op_seq_num;
3664 EXCEPTION
3665 WHEN others THEN
3666 x_error_code := SQLCODE;
3667 x_error_msg := 'WSMOPRNB.upd_cumulative_scrap_qty('||l_stmt_num||')'|| substr(SQLERRM,1,2000);
3668 -- czh:BUG1995161
3669 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.upd_cumulative_scrap_qty other excpn: (stmt'||l_stmt_num ||'):'||x_error_msg);
3670 END upd_cumulative_scrap_qty;
3671 */
3672
3673 /******************************************************************************************
3674 Procedure to copy mes data from setup tables to job operation tables
3675 ******************************************************************************************/
3676 Procedure copy_to_op_mes_info(
3677 p_wip_entity_id IN NUMBER
3678 , p_to_job_op_seq_num IN NUMBER
3679 , p_to_rtg_op_seq_num IN NUMBER
3680 , p_txn_quantity IN NUMBER
3681 , p_user IN NUMBER
3682 , p_login IN NUMBER
3683 , x_return_status OUT NOCOPY VARCHAR2
3684 , x_msg_count OUT NOCOPY NUMBER
3685 , x_msg_data OUT NOCOPY VARCHAR2
3686 )
3687 IS
3688 l_stmt_num NUMBER;
3689 l_serialization_started NUMBER;
3690
3691 -- Logging variables.....
3692 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
3693 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3694 l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSMPLBMI.getJobOpPageProperties';
3695 l_param_tbl WSM_Log_PVT.param_tbl_type;
3696 x_error_count NUMBER;
3697 x_return_code NUMBER;
3698 x_error_msg VARCHAR2(4000);
3699
3700 BEGIN
3701 x_return_status := G_RET_SUCCESS;
3702 IF FND_LOG.LEVEL_PROCEDURE >= l_log_level THEN
3703
3704 l_param_tbl.delete;
3705 l_param_tbl(1).paramName := 'p_wip_entity_id';
3706 l_param_tbl(1).paramValue := p_wip_entity_id;
3707 l_param_tbl(2).paramName := 'p_to_job_op_seq_num';
3708 l_param_tbl(2).paramValue := p_to_job_op_seq_num;
3709 l_param_tbl(3).paramName := 'p_txn_quantity';
3710 l_param_tbl(3).paramValue := p_txn_quantity;
3711 l_param_tbl(4).paramName := 'p_user';
3712 l_param_tbl(5).paramValue := p_user;
3713 l_param_tbl(6).paramName := 'p_login';
3714 l_param_tbl(6).paramValue := p_login;
3715
3716 WSM_Log_PVT.logProcParams(p_module_name => l_module ,
3717 p_param_tbl => l_param_tbl,
3718 p_fnd_log_level => G_LOG_LEVEL_PROCEDURE
3719 );
3720 END IF;
3721
3722 INSERT INTO WSM_OP_SECONDARY_QUANTITIES
3723 (
3724 LAST_UPDATE_DATE,
3725 LAST_UPDATED_BY,
3726 LAST_UPDATE_LOGIN,
3727 CREATION_DATE,
3728 CREATED_BY,
3729 ORGANIZATION_ID,
3730 WIP_ENTITY_ID,
3731 UOM_CODE,
3732 OPERATION_SEQ_NUM,
3733 MOVE_IN_QUANTITY,
3734 MOVE_OUT_QUANTITY
3735 )
3736 SELECT SYSDATE,
3737 p_user,
3738 p_login,
3739 SYSDATE,
3740 p_user,
3741 ORGANIZATION_ID,
3742 WIP_ENTITY_ID,
3743 UOM_CODE,
3744 p_to_job_op_seq_num,
3745 CURRENT_QUANTITY,
3746 NULL
3747 FROM WSM_JOB_SECONDARY_QUANTITIES
3748 WHERE WIP_ENTITY_ID = p_wip_entity_id
3749 and currently_active = 1;
3750
3751 l_stmt_num := 92;
3752
3753 INSERT INTO WSM_OP_REASON_CODES
3754 (
3755 ORGANIZATION_ID,
3756 WIP_ENTITY_ID,
3757 OPERATION_SEQ_NUM,
3758 CODE_TYPE,
3759 REASON_CODE,
3760 QUANTITY,
3761 CREATED_BY,
3762 LAST_UPDATE_DATE,
3763 LAST_UPDATED_BY,
3764 CREATION_DATE,
3765 LAST_UPDATED_LOGIN
3766 )
3767 SELECT DISTINCT ORGANIZATION_ID,
3768 WIP_ENTITY_ID,
3769 OPERATION_SEQ_NUM,
3770 1, --CODE_TYPE,
3771 SCRAP_CODE,
3772 NULL,
3773 p_user,
3774 SYSDATE,
3775 p_user,
3776 SYSDATE,
3777 p_login
3778 FROM BOM_STD_OP_SCRAP_CODES BSOSC,
3779 WIP_OPERATIONS WO
3780 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
3781 AND WO.OPERATION_SEQ_NUM = p_to_job_op_seq_num
3782 AND BSOSC.STANDARD_OPERATION_ID = WO.STANDARD_OPERATION_ID;
3783
3784 l_stmt_num := 93;
3785 INSERT INTO WSM_OP_REASON_CODES
3786 (
3787 ORGANIZATION_ID,
3788 WIP_ENTITY_ID,
3789 OPERATION_SEQ_NUM,
3790 CODE_TYPE,
3791 REASON_CODE,
3792 QUANTITY,
3793 CREATED_BY,
3794 LAST_UPDATE_DATE,
3795 LAST_UPDATED_BY,
3796 CREATION_DATE,
3797 LAST_UPDATED_LOGIN
3798 )
3799 SELECT DISTINCT ORGANIZATION_ID,
3800 WIP_ENTITY_ID,
3801 OPERATION_SEQ_NUM,
3802 2, --CODE_TYPE,
3803 BONUS_CODE,
3804 NULL,
3805 p_user,
3806 SYSDATE,
3807 p_user,
3808 SYSDATE,
3809 p_login
3810 FROM BOM_STD_OP_BONUS_CODES BSOSC,
3811 WIP_OPERATIONS WO
3812 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
3813 AND WO.OPERATION_SEQ_NUM = p_to_job_op_seq_num
3814 AND BSOSC.STANDARD_OPERATION_ID = WO.STANDARD_OPERATION_ID;
3815
3816 UPDATE WSM_LOT_BASED_JOBS
3817 SET current_job_op_seq_num = p_to_job_op_seq_num,
3818 current_rtg_op_seq_num = p_to_rtg_op_seq_num
3819 WHERE WIP_ENTITY_ID = p_wip_entity_id;
3820
3821
3822
3823 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
3824 p_count => x_error_count ,
3825 p_data => x_error_msg
3826 );
3827 EXCEPTION
3828 WHEN FND_API.G_EXC_ERROR THEN
3829
3830 x_return_status := G_RET_ERROR;
3831 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
3832 p_count => x_error_count ,
3833 p_data => x_error_msg
3834 );
3835
3836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3837
3838 x_return_status := G_RET_UNEXPECTED;
3839
3840 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
3841 p_count => x_error_count ,
3842 p_data => x_error_msg
3843 );
3844 WHEN OTHERS THEN
3845
3846 x_return_status := G_RET_UNEXPECTED;
3847
3848 IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
3849 (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
3850 THEN
3851 WSM_log_PVT.handle_others( p_module_name => l_module ,
3852 p_stmt_num => l_stmt_num ,
3853 p_fnd_log_level => G_LOG_LEVEL_UNEXPECTED ,
3854 p_run_log_level => l_log_level
3855 );
3856 END IF;
3857
3858 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
3859 p_count => x_error_count ,
3860 p_data => x_error_msg
3861 );
3862 END;
3863 --MES END
3864
3865 --bug 5337172 intermediate function generated by rosetta
3866 function update_job_name(p_wip_entity_id NUMBER
3867 , p_subinventory VARCHAR2
3868 , p_org_id NUMBER
3869 , p_txn_type NUMBER
3870 , p_update_flag number
3871 , p_dup_job_name out nocopy VARCHAR2
3872 , x_error_code out nocopy NUMBER
3873 , x_error_msg out nocopy VARCHAR2
3874 ) return varchar2
3875
3876 as
3877 ddp_update_flag boolean;
3878 ddindx binary_integer; indx binary_integer;
3879 ddrosetta_retval varchar2(4000);
3880 begin
3881
3882 -- copy data to the local IN or IN-OUT args, if any
3883
3884 if p_update_flag is null
3885 then ddp_update_flag := null;
3886 elsif p_update_flag = 0
3887 then ddp_update_flag := false;
3888 else ddp_update_flag := true;
3889 end if;
3890
3891 -- here's the delegated call to the old PL/SQL routine
3892 ddrosetta_retval := WSMPOPRN.update_job_name(p_wip_entity_id,
3893 p_subinventory,
3894 p_org_id,
3895 p_txn_type,
3896 ddp_update_flag,
3897 p_dup_job_name,
3898 x_error_code,
3899 x_error_msg);
3900
3901 -- copy data back from the local variables to OUT or IN-OUT args, if any
3902
3903 return ddrosetta_retval;
3904 end;
3905 --end bug 5337172
3906 END WSMPOPRN;
3907