[Home] [Help]
PACKAGE BODY: APPS.WSMPOPRN
Source
1 PACKAGE BODY WSMPOPRN AS
2 /* $Header: WSMOPRNB.pls 120.25.12010000.2 2008/09/24 15:39:09 tbhande 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, --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.RESOURCE_SEQ_NUM, --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 --ROUND(nvl(p_txn_quantity, 0), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
2326 ROUND(nvl(WCO.RECO_SCHEDULED_QUANTITY, 0), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
2327 0, 0, 0, 0, 0, 0,
2328 decode(recommended, 'Y', nvl(RECO_START_DATE, p_txn_date), p_txn_date), --move_enh? populate the reco dates for planned op or ...
2329 decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, p_txn_date), p_txn_date),
2330 decode(recommended, 'Y', nvl(RECO_START_DATE, p_txn_date), p_txn_date),
2331 decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, p_txn_date), p_txn_date),
2332 0, 0,
2333 WCO.COUNT_POINT_TYPE,
2334 WCO.BACKFLUSH_FLAG,
2335 NVL(WCO.MINIMUM_TRANSFER_QUANTITY, 0),
2336 '',
2337 WCO.ATTRIBUTE_CATEGORY,
2338 WCO.ATTRIBUTE1,
2339 WCO.ATTRIBUTE2,
2340 WCO.ATTRIBUTE3,
2341 WCO.ATTRIBUTE4,
2342 WCO.ATTRIBUTE5,
2343 WCO.ATTRIBUTE6,
2344 WCO.ATTRIBUTE7,
2345 WCO.ATTRIBUTE8,
2346 WCO.ATTRIBUTE9,
2347 WCO.ATTRIBUTE10,
2348 WCO.ATTRIBUTE11,
2349 WCO.ATTRIBUTE12,
2350 WCO.ATTRIBUTE13,
2351 WCO.ATTRIBUTE14,
2352 WCO.ATTRIBUTE15,
2353 WCO.YIELD,
2354 to_char(WCO.OPERATION_YIELD_ENABLED),
2355 nvl(RECOMMENDED, 'N'),
2356 WDJ.QUANTITY_SCRAPPED,
2357 WCO.operation_seq_num,
2358 0,
2359 WCO.LOWEST_ACCEPTABLE_YIELD
2360 FROM WSM_COPY_OPERATIONS WCO,
2361 WIP_DISCRETE_JOBS WDJ
2362 WHERE WCO.wip_entity_id=p_wip_entity_id
2363 AND WCO.organization_id=p_org_id
2364 AND WCO.operation_seq_num = p_to_rtg_op_seq_num
2365 AND WDJ.organization_id = WCO.organization_id
2366 AND WDJ.wip_entity_id = WCO.wip_entity_id;
2367
2368 --bugfix 2026218
2369 --copy attachment from operations document attachment defined in the network routing form.
2370 IF SQL%ROWCOUNT > 0 THEN
2371 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
2372 X_FROM_ENTITY_NAME => 'BOM_OPERATION_SEQUENCES',
2373 X_FROM_PK1_VALUE => to_char(p_to_op_seq_id),
2374 X_TO_ENTITY_NAME => 'WSM_LOT_BASED_OPERATIONS',
2375 X_TO_PK1_VALUE => to_char(p_wip_entity_id),
2376 X_TO_PK2_VALUE => to_char(p_to_job_op_seq_num),
2377 X_TO_PK3_VALUE => to_char(p_org_id),
2378 X_CREATED_BY => p_user,
2379 X_LAST_UPDATE_LOGIN => p_login,
2380 X_PROGRAM_APPLICATION_ID => p_program_application_id,
2381 X_PROGRAM_ID => p_program_id,
2382 X_REQUEST_ID => p_request_id);
2383
2384 if (l_debug = 'Y') then -- czh:BUG1995161
2385 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted ' ||sql%rowcount||' records in WO.');
2386 end if; -- czh:BUG1995161
2387 ELSE
2388 if (l_debug = 'Y') then
2389 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||sql%rowcount||' records in WO.');
2390 end if;
2391 x_error_msg := 'WSMOPRNB.('||l_stmt_num||')'|| 'no rows in WO';
2392 x_error_code := -1;
2393 raise e_proc_error;
2394 END IF;
2395
2396 l_stmt_num := 20;
2397 /******
2398 Bug 3571019 - Get SCRAP_ACCOUNT, EST_SCRAP_ABSORB_ACCOUNT from WCO instead of BD
2399 INSERT INTO WIP_OPERATION_YIELDS
2400 (WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
2401 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
2402 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
2403 PROGRAM_APPLICATION_ID, PROGRAM_ID,PROGRAM_UPDATE_DATE,
2404 STATUS, SCRAP_ACCOUNT, EST_SCRAP_ABSORB_ACCOUNT)
2405 SELECT WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, WO.ORGANIZATION_ID,
2406 SYSDATE,
2407 p_user,
2408 SYSDATE,
2409 p_user,
2410 p_login,
2411 DECODE(p_request_id, 0, '', p_request_id),
2412 DECODE(p_program_application_id, 0, '', p_program_application_id),
2413 DECODE(p_commit, 1, p_program_id, -999),
2414 DECODE(p_program_id, 0, '', SYSDATE),
2415 NULL, BD.SCRAP_ACCOUNT, BD.EST_ABSORPTION_ACCOUNT
2416 FROM WIP_OPERATIONS WO,
2417 BOM_DEPARTMENTS BD
2418 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
2419 AND WO.OPERATION_SEQ_NUM = p_to_job_op_seq_num
2420 AND WO.ORGANIZATION_ID = p_org_id
2421 AND WO.DEPARTMENT_ID = BD.DEPARTMENT_ID; --bugfix 1611094
2422 *******/
2423
2424 INSERT INTO WIP_OPERATION_YIELDS
2425 (WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
2426 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
2427 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
2428 PROGRAM_APPLICATION_ID, PROGRAM_ID,PROGRAM_UPDATE_DATE,
2429 STATUS, SCRAP_ACCOUNT, EST_SCRAP_ABSORB_ACCOUNT)
2430 SELECT WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, WO.ORGANIZATION_ID,
2431 SYSDATE,
2432 p_user,
2433 SYSDATE,
2434 p_user,
2435 p_login,
2436 DECODE(p_request_id, 0, '', p_request_id),
2437 DECODE(p_program_application_id, 0, '', p_program_application_id),
2438 DECODE(p_commit, 1, p_program_id, -999),
2439 DECODE(p_program_id, 0, '', SYSDATE),
2440 NULL, WCO.SCRAP_ACCOUNT, WCO.EST_ABSORPTION_ACCOUNT
2441 FROM WIP_OPERATIONS WO,
2442 WSM_COPY_OPERATIONS WCO
2443 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
2444 AND WO.OPERATION_SEQ_NUM = p_to_job_op_seq_num
2445 AND WO.ORGANIZATION_ID = p_org_id
2446 AND WCO.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
2447 AND WCO.OPERATION_SEQ_NUM = WO.WSM_OP_SEQ_NUM;
2448
2449
2450 if (l_debug = 'Y') then
2451 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||sql%rowcount||' records in WOY.');
2452 end if;
2453
2454 l_stmt_num := 30;
2455 --move enh?
2456 BEGIN
2457 --commenting out following sql after code review of bug 3587239
2458 /*********
2459 SELECT 1
2460 INTO l_phantom_exists
2461 FROM WSM_COPY_REQUIREMENT_OPS
2462 WHERE EXISTS (SELECT null
2463 FROM WSM_COPY_REQUIREMENT_OPS WCRO
2464 WHERE WCRO.WIP_ENTITY_ID = p_wip_entity_id
2465 AND WCRO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
2466 AND WCRO.recommended='Y');
2467 *********/
2468 --move enh merging may be needed because of phantoms and component substitution
2469 --adding following sql after code review of bug 3587239
2470 SELECT 1
2471 INTO l_phantom_exists
2472 FROM WSM_COPY_REQUIREMENT_OPS WCRO
2473 WHERE WCRO.WIP_ENTITY_ID = p_wip_entity_id
2474 AND WCRO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
2475 AND WCRO.recommended='Y'
2476 AND WCRO.source_phantom_id <> -1;
2477 EXCEPTION
2478 WHEN no_data_found THEN
2479 l_phantom_exists := 0;
2480
2481 WHEN too_many_rows THEN
2482 l_phantom_exists := 1;
2483 END;
2484
2485 IF (l_debug='Y') THEN
2486 fnd_file.put_line(fnd_file.log, 'WSMOPRNB.add_operation' ||'(stmt_num='||l_stmt_num||') :
2487 l_phantom_exists '||l_phantom_exists);
2488 END IF;
2489
2490 l_stmt_num := 40;
2491
2492 IF (l_phantom_exists=1) THEN
2493 DECLARE
2494
2495 CURSOR c_phantoms IS
2496 SELECT WCRO.COMPONENT_ITEM_ID,
2497 WCRO.organization_id,
2498 WCRO.wip_entity_id,
2499 WCRO.COMPONENT_SEQUENCE_ID,
2500 WCRO.WIP_SUPPLY_TYPE,
2501 decode(WCRO.recommended, 'Y', Nvl(WCRO.reco_date_required, p_txn_date), p_txn_date),
2502 --move enh 115.77 component yield no longer factored in
2503 /* round((WCRO.quantity_per_assembly/decode(WCRO.COMPONENT_YIELD_FACTOR,
2504 0, 1, WCRO.COMPONENT_YIELD_FACTOR)), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION)*p_txn_quantity,
2505 round((WCRO.quantity_per_assembly/decode(WCRO.COMPONENT_YIELD_FACTOR,
2506 0, 1, WCRO.COMPONENT_YIELD_FACTOR)), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
2507 */
2508 --bug 3587239 Round the required quantity to 6 places
2509 WCRO.basis_type, --LBM enh
2510 --Bug 5236684:Component yield factor should include the yield of the source phantom also.
2511 decode(nvl(wcro.basis_type,1),1,nvl(wcro1.component_yield_factor,1),1)*wcro.component_yield_factor, --LBM enh
2512 --component shrinkage
2513 -- ROUND((WCRO.quantity_per_assembly/WCRO.component_yield_factor)
2514 ROUND((WCRO.quantity_per_assembly)
2515 *decode(wcro.basis_type, 2, 1, p_txn_quantity), WSMPCNST.NUMBER_OF_DECIMALS), --LBM enh
2516 --component shrinkage
2517 -- WCRO.quantity_per_assembly,
2518 --Bug 5236684:Bill_quantity_per_assembly should include the Bill_quantity_per_assembly of source phantom also.
2519 decode(nvl(wcro.basis_type,1),1,nvl(WCRO1.bill_quantity_per_assembly,1),1)*WCRO.bill_quantity_per_assembly,
2520 WCRO.supply_subinventory,
2521 WCRO.supply_locator_id,
2522 decode(WCRO.wip_supply_type,
2523 5, 2,
2524 decode(sign(WCRO.quantity_per_assembly),
2525 -1, 2,
2526 1)) mrp_net_flag,
2527 WCRO.component_remarks,
2528 WCRO.attribute_category,
2529 WCRO.attribute1,
2530 WCRO.attribute2,
2531 WCRO.attribute3,
2532 WCRO.attribute4,
2533 WCRO.attribute5,
2534 WCRO.attribute6,
2535 WCRO.attribute7,
2536 WCRO.attribute8,
2537 WCRO.attribute9,
2538 WCRO.attribute10,
2539 WCRO.attribute11,
2540 WCRO.attribute12,
2541 WCRO.attribute13,
2542 WCRO.attribute14,
2543 WCRO.attribute15,
2544 MSI.segment1,
2545 MSI.segment2,
2546 MSI.segment3,
2547 MSI.segment4,
2548 MSI.segment5,
2549 MSI.segment6,
2550 MSI.segment7,
2551 MSI.segment8,
2552 MSI.segment9,
2553 MSI.segment10,
2554 MSI.segment11,
2555 MSI.segment12,
2556 MSI.segment13,
2557 MSI.segment14,
2558 MSI.segment15,
2559 MSI.segment16,
2560 MSI.segment17,
2561 MSI.segment18,
2562 MSI.segment19,
2563 MSI.segment20,
2564 WCRO.department_id
2565 FROM WSM_COPY_REQUIREMENT_OPS WCRO,
2566 WSM_COPY_REQUIREMENT_OPS WCRO1,--Added for bug 5236684
2567 MTL_SYSTEM_ITEMS MSI
2568 WHERE WCRO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
2569 AND WCRO.WIP_ENTITY_ID= p_wip_entity_id
2570 AND MSI.inventory_item_id = WCRO.component_item_id
2571 AND MSI.organization_id = WCRO.organization_id
2572 AND WCRO.RECOMMENDED='Y'
2573 --Added for bug 5236684
2574 AND WCRO.WIP_ENTITY_ID = WCRO1.WIP_ENTITY_ID(+)
2575 AND WCRO1.OPERATION_SEQ_NUM(+)= -1*p_to_rtg_op_seq_num
2576 AND WCRO.source_phantom_id = WCRO1.component_item_id(+)
2577 ORDER BY WCRO.component_item_id, WCRO.wip_supply_type desc;
2578
2579 type t_inventory_item_id is table of WIP_REQUIREMENT_OPERATIONS.inventory_item_id%type index by binary_integer;
2580 type t_organization_id is table of WIP_REQUIREMENT_OPERATIONS.organization_id%type index by binary_integer;
2581 type t_wip_entity_id is table of WIP_REQUIREMENT_OPERATIONS.wip_entity_id%type index by binary_integer;
2582 type t_component_sequence_id is table of WIP_REQUIREMENT_OPERATIONS.component_sequence_id%type index by binary_integer;
2583 type t_wip_supply_type is table of WIP_REQUIREMENT_OPERATIONS.wip_supply_type%type index by binary_integer;
2584 type t_date_required is table of WIP_REQUIREMENT_OPERATIONS.date_required%type index by binary_integer;
2585 type t_basis_type is table of WIP_REQUIREMENT_OPERATIONS.basis_type%type index by binary_integer; --LBM enh
2586 type t_component_yield_factor is table of WIP_REQUIREMENT_OPERATIONS.component_yield_factor%type index by binary_integer; --LBM enh
2587 type t_required_quantity is table of WIP_REQUIREMENT_OPERATIONS.required_quantity%type index by binary_integer;
2588 type t_quantity_per_assembly is table of WIP_REQUIREMENT_OPERATIONS.quantity_per_assembly%type index by binary_integer;
2589 type t_supply_subinventory is table of WIP_REQUIREMENT_OPERATIONS.supply_subinventory%type index by binary_integer;
2590 type t_supply_locator_id is table of WIP_REQUIREMENT_OPERATIONS.supply_locator_id%type index by binary_integer;
2591 type t_mrp_net_flag is table of WIP_REQUIREMENT_OPERATIONS.mrp_net_flag%type index by binary_integer;
2592 type t_comments is table of WIP_REQUIREMENT_OPERATIONS.comments%type index by binary_integer;
2593 type t_attribute_category is table of WIP_REQUIREMENT_OPERATIONS.attribute_category%type index by binary_integer;
2594 type t_attribute1 is table of WIP_REQUIREMENT_OPERATIONS.attribute1%type index by binary_integer;
2595 type t_attribute2 is table of WIP_REQUIREMENT_OPERATIONS.attribute2%type index by binary_integer;
2596 type t_attribute3 is table of WIP_REQUIREMENT_OPERATIONS.attribute3%type index by binary_integer;
2597 type t_attribute4 is table of WIP_REQUIREMENT_OPERATIONS.attribute4%type index by binary_integer;
2598 type t_attribute5 is table of WIP_REQUIREMENT_OPERATIONS.attribute5%type index by binary_integer;
2599 type t_attribute6 is table of WIP_REQUIREMENT_OPERATIONS.attribute6%type index by binary_integer;
2600 type t_attribute7 is table of WIP_REQUIREMENT_OPERATIONS.attribute7%type index by binary_integer;
2601 type t_attribute8 is table of WIP_REQUIREMENT_OPERATIONS.attribute8%type index by binary_integer;
2602 type t_attribute9 is table of WIP_REQUIREMENT_OPERATIONS.attribute9%type index by binary_integer;
2603 type t_attribute10 is table of WIP_REQUIREMENT_OPERATIONS.attribute10%type index by binary_integer;
2604 type t_attribute11 is table of WIP_REQUIREMENT_OPERATIONS.attribute11%type index by binary_integer;
2605 type t_attribute12 is table of WIP_REQUIREMENT_OPERATIONS.attribute12%type index by binary_integer;
2606 type t_attribute13 is table of WIP_REQUIREMENT_OPERATIONS.attribute13%type index by binary_integer;
2607 type t_attribute14 is table of WIP_REQUIREMENT_OPERATIONS.attribute14%type index by binary_integer;
2608 type t_attribute15 is table of WIP_REQUIREMENT_OPERATIONS.attribute15%type index by binary_integer;
2609 type t_segment1 is table of WIP_REQUIREMENT_OPERATIONS.segment1%type index by binary_integer;
2610 type t_segment2 is table of WIP_REQUIREMENT_OPERATIONS.segment2%type index by binary_integer;
2611 type t_segment3 is table of WIP_REQUIREMENT_OPERATIONS.segment3%type index by binary_integer;
2612 type t_segment4 is table of WIP_REQUIREMENT_OPERATIONS.segment4%type index by binary_integer;
2613 type t_segment5 is table of WIP_REQUIREMENT_OPERATIONS.segment5%type index by binary_integer;
2614 type t_segment6 is table of WIP_REQUIREMENT_OPERATIONS.segment6%type index by binary_integer;
2615 type t_segment7 is table of WIP_REQUIREMENT_OPERATIONS.segment7%type index by binary_integer;
2616 type t_segment8 is table of WIP_REQUIREMENT_OPERATIONS.segment8%type index by binary_integer;
2617 type t_segment9 is table of WIP_REQUIREMENT_OPERATIONS.segment9%type index by binary_integer;
2618 type t_segment10 is table of WIP_REQUIREMENT_OPERATIONS.segment10%type index by binary_integer;
2619 type t_segment11 is table of WIP_REQUIREMENT_OPERATIONS.segment11%type index by binary_integer;
2620 type t_segment12 is table of WIP_REQUIREMENT_OPERATIONS.segment12%type index by binary_integer;
2621 type t_segment13 is table of WIP_REQUIREMENT_OPERATIONS.segment13%type index by binary_integer;
2622 type t_segment14 is table of WIP_REQUIREMENT_OPERATIONS.segment14%type index by binary_integer;
2623 type t_segment15 is table of WIP_REQUIREMENT_OPERATIONS.segment15%type index by binary_integer;
2624 type t_segment16 is table of WIP_REQUIREMENT_OPERATIONS.segment16%type index by binary_integer;
2625 type t_segment17 is table of WIP_REQUIREMENT_OPERATIONS.segment17%type index by binary_integer;
2626 type t_segment18 is table of WIP_REQUIREMENT_OPERATIONS.segment18%type index by binary_integer;
2627 type t_segment19 is table of WIP_REQUIREMENT_OPERATIONS.segment19%type index by binary_integer;
2628 type t_segment20 is table of WIP_REQUIREMENT_OPERATIONS.segment20%type index by binary_integer;
2629 type t_department_id is table of WIP_REQUIREMENT_OPERATIONS.department_id%type index by binary_integer;
2630
2631 v_inventory_item_id t_inventory_item_id;
2632 v_organization_id t_organization_id;
2633 v_wip_entity_id t_wip_entity_id;
2634 v_component_sequence_id t_component_sequence_id;
2635 v_wip_supply_type t_wip_supply_type;
2636 v_date_required t_date_required;
2637 v_basis_type t_basis_type; --LBM enh
2638 v_component_yield_factor t_component_yield_factor; --LBM enh
2639 v_required_quantity t_required_quantity;
2640 v_quantity_per_assembly t_quantity_per_assembly;
2641 v_supply_subinventory t_supply_subinventory;
2642 v_supply_locator_id t_supply_locator_id;
2643 v_mrp_net_flag t_mrp_net_flag;
2644 v_comments t_comments;
2645 v_attribute_category t_attribute_category;
2646 v_attribute1 t_attribute1;
2647 v_attribute2 t_attribute2;
2648 v_attribute3 t_attribute3;
2649 v_attribute4 t_attribute4;
2650 v_attribute5 t_attribute5;
2651 v_attribute6 t_attribute6;
2652 v_attribute7 t_attribute7;
2653 v_attribute8 t_attribute8;
2654 v_attribute9 t_attribute9;
2655 v_attribute10 t_attribute10;
2656 v_attribute11 t_attribute11;
2657 v_attribute12 t_attribute12;
2658 v_attribute13 t_attribute13;
2659 v_attribute14 t_attribute14;
2660 v_attribute15 t_attribute15;
2661 v_segment1 t_segment1;
2662 v_segment2 t_segment2;
2663 v_segment3 t_segment3;
2664 v_segment4 t_segment4;
2665 v_segment5 t_segment5;
2666 v_segment6 t_segment6;
2667 v_segment7 t_segment7;
2668 v_segment8 t_segment8;
2669 v_segment9 t_segment9;
2670 v_segment10 t_segment10;
2671 v_segment11 t_segment11;
2672 v_segment12 t_segment12;
2673 v_segment13 t_segment13;
2674 v_segment14 t_segment14;
2675 v_segment15 t_segment15;
2676 v_segment16 t_segment16;
2677 v_segment17 t_segment17;
2678 v_segment18 t_segment18;
2679 v_segment19 t_segment19;
2680 v_segment20 t_segment20;
2681 v_department_id t_department_id;
2682
2683 l_no_of_rows NUMBER;
2684 i NUMBER := 1;
2685 j NUMBER;
2686 BEGIN
2687 l_stmt_num := 50;
2688 OPEN c_phantoms;
2689 --bulk fetch records into PL/SQL tables
2690 fetch c_phantoms bulk collect into
2691 v_inventory_item_id
2692 , v_organization_id
2693 , v_wip_entity_id
2694 , v_component_sequence_id
2695 , v_wip_supply_type
2696 , v_date_required
2697 , v_basis_type --LBM enh
2698 , v_component_yield_factor --LBM enh
2699 , v_required_quantity
2700 , v_quantity_per_assembly
2701 , v_supply_subinventory
2702 , v_supply_locator_id
2703 , v_mrp_net_flag
2704 , v_comments
2705 , v_attribute_category
2706 , v_attribute1
2707 , v_attribute2
2708 , v_attribute3
2709 , v_attribute4
2710 , v_attribute5
2711 , v_attribute6
2712 , v_attribute7
2713 , v_attribute8
2714 , v_attribute9
2715 , v_attribute10
2716 , v_attribute11
2717 , v_attribute12
2718 , v_attribute13
2719 , v_attribute14
2720 , v_attribute15
2721 , v_segment1
2722 , v_segment2
2723 , v_segment3
2724 , v_segment4
2725 , v_segment5
2726 , v_segment6
2727 , v_segment7
2728 , v_segment8
2729 , v_segment9
2730 , v_segment10
2731 , v_segment11
2732 , v_segment12
2733 , v_segment13
2734 , v_segment14
2735 , v_segment15
2736 , v_segment16
2737 , v_segment17
2738 , v_segment18
2739 , v_segment19
2740 , v_segment20
2741 , v_department_id;
2742
2743 IF (l_debug = 'Y') THEN
2744 fnd_file.put_line(fnd_file.log, 'count '||v_inventory_item_id.count);
2745 END IF;
2746
2747 LOOP
2748 l_stmt_num := 60;
2749
2750 IF v_inventory_item_id.exists(i+1) THEN
2751 IF (v_inventory_item_id(i)=v_inventory_item_id(i+1)) THEN
2752 v_required_quantity(i+1) := v_required_quantity(i+1) + v_required_quantity(i);
2753 v_quantity_per_assembly(i+1) := v_quantity_per_assembly(i+1) + v_quantity_per_assembly(i);
2754 v_basis_type.delete(i); --LBM enh
2755 v_component_yield_factor.delete(i); --LBM enh
2756 v_inventory_item_id.delete(i);
2757 v_organization_id.delete(i);
2758 v_wip_entity_id.delete(i);
2759 v_component_sequence_id.delete(i);
2760 v_wip_supply_type.delete(i);
2761 v_date_required.delete(i);
2762 v_required_quantity.delete(i);
2763 v_quantity_per_assembly.delete(i);
2764 v_supply_subinventory.delete(i);
2765 v_supply_locator_id.delete(i);
2766 v_mrp_net_flag.delete(i);
2767 v_comments.delete(i);
2768 v_attribute_category.delete(i);
2769 v_attribute1.delete(i);
2770 v_attribute2.delete(i);
2771 v_attribute3.delete(i);
2772 v_attribute4.delete(i);
2773 v_attribute5.delete(i);
2774 v_attribute6.delete(i);
2775 v_attribute7.delete(i);
2776 v_attribute8.delete(i);
2777 v_attribute9.delete(i);
2778 v_attribute10.delete(i);
2779 v_attribute11.delete(i);
2780 v_attribute12.delete(i);
2781 v_attribute13.delete(i);
2782 v_attribute14.delete(i);
2783 v_attribute15.delete(i);
2784 v_segment1.delete(i);
2785 v_segment2.delete(i);
2786 v_segment3.delete(i);
2787 v_segment4.delete(i);
2788 v_segment5.delete(i);
2789 v_segment6.delete(i);
2790 v_segment7.delete(i);
2791 v_segment8.delete(i);
2792 v_segment9.delete(i);
2793 v_segment10.delete(i);
2794 v_segment11.delete(i);
2795 v_segment12.delete(i);
2796 v_segment13.delete(i);
2797 v_segment14.delete(i);
2798 v_segment15.delete(i);
2799 v_segment16.delete(i);
2800 v_segment17.delete(i);
2801 v_segment18.delete(i);
2802 v_segment19.delete(i);
2803 v_segment20.delete(i);
2804 v_department_id.delete(i);
2805
2806 END IF;
2807 i := i+1;
2808 ELSE
2809 EXIT;
2810 END IF;
2811 END LOOP;
2812
2813 IF (l_debug = 'Y') THEN
2814 fnd_file.put_line(fnd_file.log, 'count after consolidation'||v_inventory_item_id.count);
2815 END IF;
2816
2817 l_no_of_rows := v_inventory_item_id.last;
2818 j := l_no_of_rows + 1;
2819 FOR i in 1..l_no_of_rows LOOP
2820 IF v_inventory_item_id.exists(i) THEN
2821 v_inventory_item_id(j) := v_inventory_item_id(i);
2822 v_organization_id(j) := v_organization_id(i);
2823 v_wip_entity_id(j) := v_wip_entity_id(i);
2824 v_component_sequence_id(j) := v_component_sequence_id(i);
2825 v_wip_supply_type(j) := v_wip_supply_type(i);
2826 v_date_required(j) := v_date_required(i);
2827 v_basis_type(j) := v_basis_type(i); --LBM enh
2828 v_component_yield_factor(j) := v_component_yield_factor(i); --LBM enh
2829 v_required_quantity(j) := v_required_quantity(i);
2830 v_quantity_per_assembly(j) := v_quantity_per_assembly(i);
2831 v_supply_subinventory(j) := v_supply_subinventory(i);
2832 v_supply_locator_id(j) := v_supply_locator_id(i);
2833 v_mrp_net_flag(j) := v_mrp_net_flag(i);
2834 v_comments(j) := v_comments(i);
2835 v_attribute_category(j) := v_attribute_category(i);
2836 v_attribute1(j) := v_attribute1(i);
2837 v_attribute2(j) := v_attribute2(i);
2838 v_attribute3(j) := v_attribute3(i);
2839 v_attribute4(j) := v_attribute4(i);
2840 v_attribute5(j) := v_attribute5(i);
2841 v_attribute6(j) := v_attribute6(i);
2842 v_attribute7(j) := v_attribute7(i);
2843 v_attribute8(j) := v_attribute8(i);
2844 v_attribute9(j) := v_attribute9(i);
2845 v_attribute10(j) := v_attribute10(i);
2846 v_attribute11(j) := v_attribute11(i);
2847 v_attribute12(j) := v_attribute12(i);
2848 v_attribute13(j) := v_attribute13(i);
2849 v_attribute14(j) := v_attribute14(i);
2850 v_attribute15(j) := v_attribute15(i);
2851 v_segment1(j) := v_segment1(i);
2852 v_segment2(j) := v_segment2(i);
2853 v_segment3(j) := v_segment3(i);
2854 v_segment4(j) := v_segment4(i);
2855 v_segment5(j) := v_segment5(i);
2856 v_segment6(j) := v_segment6(i);
2857 v_segment7(j) := v_segment7(i);
2858 v_segment8(j) := v_segment8(i);
2859 v_segment9(j) := v_segment9(i);
2860 v_segment10(j) := v_segment10(i);
2861 v_segment11(j) := v_segment11(i);
2862 v_segment12(j) := v_segment12(i);
2863 v_segment13(j) := v_segment13(i);
2864 v_segment14(j) := v_segment14(i);
2865 v_segment15(j) := v_segment15(i);
2866 v_segment16(j) := v_segment16(i);
2867 v_segment17(j) := v_segment17(i);
2868 v_segment18(j) := v_segment18(i);
2869 v_segment19(j) := v_segment19(i);
2870 v_segment20(j) := v_segment20(i);
2871 v_department_id(j) := v_department_id(i);
2872
2873 j := j+1;
2874 END IF;
2875 END LOOP;
2876
2877 l_stmt_num := 70;
2878 FORALL i in (l_no_of_rows + 1)..(j-1)
2879 --move enh changed released quantity to start_qty*qpa on 21 Oct 03
2880 INSERT INTO WIP_REQUIREMENT_OPERATIONS
2881 (inventory_item_id,
2882 organization_id,
2883 wip_entity_id,
2884 operation_seq_num,
2885 repetitive_schedule_id,
2886 last_update_date,
2887 last_updated_by,
2888 creation_date,
2889 created_by,
2890 last_update_login,
2891 component_sequence_id,
2892 wip_supply_type,
2893 date_required,
2894 basis_type, --LBM enh
2895 required_quantity,
2896 quantity_issued,
2897 quantity_per_assembly,
2898 supply_subinventory,
2899 supply_locator_id,
2900 mrp_net_flag,
2901 comments,
2902 attribute_category,
2903 attribute1,
2904 attribute2,
2905 attribute3,
2906 attribute4,
2907 attribute5,
2908 attribute6,
2909 attribute7,
2910 attribute8,
2911 attribute9,
2912 attribute10,
2913 attribute11,
2914 attribute12,
2915 attribute13,
2916 attribute14,
2917 attribute15,
2918 segment1,
2919 segment2,
2920 segment3,
2921 segment4,
2922 segment5,
2923 segment6,
2924 segment7,
2925 segment8,
2926 segment9,
2927 segment10,
2928 segment11,
2929 segment12,
2930 segment13,
2931 segment14,
2932 segment15,
2933 segment16,
2934 segment17,
2935 segment18,
2936 segment19,
2937 segment20,
2938 department_id,
2939 released_quantity,
2940 component_yield_factor) --component shrinkage
2941 VALUES (v_inventory_item_id(i)
2942 , v_organization_id(i)
2943 , v_wip_entity_id(i)
2944 , p_to_job_op_seq_num
2945 , NULL,
2946 SYSDATE,
2947 p_user,
2948 SYSDATE,
2949 p_user,
2950 p_login
2951 , v_component_sequence_id(i)
2952 , v_wip_supply_type(i)
2953 , v_date_required(i)
2954 , v_basis_type(i) --LBM enh
2955 , v_required_quantity(i)
2956 , 0
2957 , v_quantity_per_assembly(i)
2958 , v_supply_subinventory(i)
2959 , v_supply_locator_id(i)
2960 , v_mrp_net_flag(i)
2961 , v_comments(i)
2962 , v_attribute_category(i)
2963 , v_attribute1(i)
2964 , v_attribute2(i)
2965 , v_attribute3(i)
2966 , v_attribute4(i)
2967 , v_attribute5(i)
2968 , v_attribute6(i)
2969 , v_attribute7(i)
2970 , v_attribute8(i)
2971 , v_attribute9(i)
2972 , v_attribute10(i)
2973 , v_attribute11(i)
2974 , v_attribute12(i)
2975 , v_attribute13(i)
2976 , v_attribute14(i)
2977 , v_attribute15(i)
2978 , v_segment1(i)
2979 , v_segment2(i)
2980 , v_segment3(i)
2981 , v_segment4(i)
2982 , v_segment5(i)
2983 , v_segment6(i)
2984 , v_segment7(i)
2985 , v_segment8(i)
2986 , v_segment9(i)
2987 , v_segment10(i)
2988 , v_segment11(i)
2989 , v_segment12(i)
2990 , v_segment13(i)
2991 , v_segment14(i)
2992 , v_segment15(i)
2993 , v_segment16(i)
2994 , v_segment17(i)
2995 , v_segment18(i)
2996 , v_segment19(i)
2997 , v_segment20(i)
2998 , v_department_id(i)
2999 --bug 3587239 Round the released quantity to 6 places
3000 , ROUND( decode(v_basis_type(i), 2, 1, p_start_quantity)
3001 *(v_quantity_per_assembly(i)/v_component_yield_factor(i)), WSMPCNST.NUMBER_OF_DECIMALS) --LBM enh
3002 , v_component_yield_factor(i));
3003 END;
3004 ELSE
3005
3006 INSERT INTO WIP_REQUIREMENT_OPERATIONS
3007 (inventory_item_id,
3008 organization_id,
3009 wip_entity_id,
3010 operation_seq_num,
3011 repetitive_schedule_id,
3012 last_update_date,
3013 last_updated_by,
3014 creation_date,
3015 created_by,
3016 last_update_login,
3017 component_sequence_id,
3018 wip_supply_type,
3019 date_required,
3020 basis_type, --LBM enh
3021 required_quantity,
3022 quantity_issued,
3023 quantity_per_assembly,
3024 supply_subinventory,
3025 supply_locator_id,
3026 mrp_net_flag,
3027 comments,
3028 attribute_category,
3029 attribute1,
3030 attribute2,
3031 attribute3,
3032 attribute4,
3033 attribute5,
3034 attribute6,
3035 attribute7,
3036 attribute8,
3037 attribute9,
3038 attribute10,
3039 attribute11,
3040 attribute12,
3041 attribute13,
3042 attribute14,
3043 attribute15,
3044 segment1,
3045 segment2,
3046 segment3,
3047 segment4,
3048 segment5,
3049 segment6,
3050 segment7,
3051 segment8,
3052 segment9,
3053 segment10,
3054 segment11,
3055 segment12,
3056 segment13,
3057 segment14,
3058 segment15,
3059 segment16,
3060 segment17,
3061 segment18,
3062 segment19,
3063 segment20,
3064 department_id,
3065 --VJ: Start additions for Costing enhancement for WLTEnh--
3066 costed_quantity_issued,
3067 costed_quantity_relieved,
3068 --VJ: End additions for Costing enhancement for WLTEnh--
3069 released_quantity,
3070 component_yield_factor) --component shrinkage
3071 SELECT WCRO.COMPONENT_ITEM_ID,
3072 WCRO.organization_id,
3073 WCRO.wip_entity_id,
3074 p_to_job_op_seq_num,
3075 NULL,
3076 SYSDATE,
3077 p_user,
3078 SYSDATE,
3079 p_user,
3080 p_login,
3081 WCRO.COMPONENT_SEQUENCE_ID,
3082 WCRO.WIP_SUPPLY_TYPE,
3083 decode(recommended, 'Y', Nvl(WCRO.reco_date_required, p_txn_date), p_txn_date),
3084 --move enh 115.77 component yield no longer factored in
3085 /* round((WCRO.quantity_per_assembly/decode(WCRO.COMPONENT_YIELD_FACTOR,
3086 0, 1, WCRO.COMPONENT_YIELD_FACTOR)), WSMPCNST.NUMBER_OF_DECIMALS)*p_txn_quantity,
3087 */
3088
3089 /* round((WCRO.quantity_per_assembly/decode(WCRO.COMPONENT_YIELD_FACTOR,
3090 0, 1, WCRO.COMPONENT_YIELD_FACTOR)), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
3091 */
3092 --bug 3587239 Round the required quantity to 6 places
3093 WCRO.basis_type, --LBM enh
3094 -- Component shrinkage
3095 -- ROUND((WCRO.quantity_per_assembly/wcro.component_yield_factor)
3096 ROUND((WCRO.quantity_per_assembly)
3097 * decode(wcro.basis_type, 2, 1, p_txn_quantity), WSMPCNST.NUMBER_OF_DECIMALS), --LBM enh
3098 0,
3099 --component shrinkage
3100 -- WCRO.quantity_per_assembly,
3101 WCRO.bill_quantity_per_assembly,
3102 WCRO.supply_subinventory,
3103 WCRO.supply_locator_id,
3104 decode(WCRO.wip_supply_type,
3105 5, 2,
3106 decode(sign(WCRO.quantity_per_assembly),
3107 -1, 2,
3108 1)) mrp_net_flag,
3109 WCRO.component_remarks,
3110 WCRO.attribute_category,
3111 WCRO.attribute1,
3112 WCRO.attribute2,
3113 WCRO.attribute3,
3114 WCRO.attribute4,
3115 WCRO.attribute5,
3116 WCRO.attribute6,
3117 WCRO.attribute7,
3118 WCRO.attribute8,
3119 WCRO.attribute9,
3120 WCRO.attribute10,
3121 WCRO.attribute11,
3122 WCRO.attribute12,
3123 WCRO.attribute13,
3124 WCRO.attribute14,
3125 WCRO.attribute15,
3126 MSI.segment1,
3127 MSI.segment2,
3128 MSI.segment3,
3129 MSI.segment4,
3130 MSI.segment5,
3131 MSI.segment6,
3132 MSI.segment7,
3133 MSI.segment8,
3134 MSI.segment9,
3135 MSI.segment10,
3136 MSI.segment11,
3137 MSI.segment12,
3138 MSI.segment13,
3139 MSI.segment14,
3140 MSI.segment15,
3141 MSI.segment16,
3142 MSI.segment17,
3143 MSI.segment18,
3144 MSI.segment19,
3145 MSI.segment20,
3146 WCRO.department_id,
3147 NULL,
3148 NULL,
3149 --bug 3587239 Round the released quantity to 6 places
3150 ROUND(decode(wcro.basis_type, 2, 1, p_start_quantity)
3151 *(WCRO.quantity_per_assembly/wcro.component_yield_factor), WSMPCNST.NUMBER_OF_DECIMALS), --LBM enh
3152 WCRO.component_yield_factor --component shrinkage
3153 FROM WSM_COPY_REQUIREMENT_OPS WCRO,
3154 MTL_SYSTEM_ITEMS MSI
3155 WHERE WCRO.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
3156 AND WCRO.WIP_ENTITY_ID= p_wip_entity_id
3157 AND MSI.inventory_item_id = WCRO.component_item_id
3158 AND MSI.organization_id = WCRO.organization_id
3159 AND WCRO.RECOMMENDED='Y';
3160
3161 END IF;
3162
3163 if (l_debug = 'Y') then
3164 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||SQL%ROWCOUNT||' records in WRO.');
3165 end if;
3166
3167 l_stmt_num := 80;
3168
3169 INSERT INTO WIP_OPERATION_RESOURCES
3170 (WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
3171 ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID,
3172 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
3173 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
3174 PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
3175 RESOURCE_ID, UOM_CODE,
3176 BASIS_TYPE, USAGE_RATE_OR_AMOUNT, ACTIVITY_ID,
3177 SCHEDULED_FLAG, ASSIGNED_UNITS,
3178 /* ST : Detailed Scheduling */
3179 maximum_assigned_units,
3180 batch_id,
3181 firm_flag,
3182 group_sequence_id,
3183 group_sequence_number,
3184 parent_resource_seq,
3185 /* ST : Detailed Scheduling */
3186 AUTOCHARGE_TYPE,
3187 STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS, APPLIED_RESOURCE_VALUE,
3188 START_DATE, COMPLETION_DATE,
3189 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
3190 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
3191 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
3192 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
3193 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
3194 ATTRIBUTE15,
3195 SCHEDULE_SEQ_NUM, --bugfix 2493065
3196 SUBSTITUTE_GROUP_NUM,
3197 REPLACEMENT_GROUP_NUM,
3198 PRINCIPLE_FLAG,
3199 SETUP_ID)
3200 SELECT WCOR.WIP_ENTITY_ID
3201 , p_to_job_op_seq_num
3202 , WCOR.RESOURCE_SEQ_NUM
3203 , WCOR.ORGANIZATION_ID
3204 , NULL
3205 , SYSDATE
3206 , p_user
3207 , SYSDATE
3208 , p_user
3209 , p_login
3210 , DECODE(p_request_id, 0, '', p_request_id)
3211 , DECODE(p_program_application_id, 0, '', p_program_application_id)
3212 , DECODE(p_commit, 1, p_program_id, -999)
3213 , DECODE(p_program_id, 0, '', SYSDATE)
3214 , WCOR.RESOURCE_ID
3215 , WCOR.UOM_CODE
3216 , WCOR.BASIS_TYPE
3217 , WCOR.USAGE_RATE_OR_AMOUNT
3218 , WCOR.ACTIVITY_ID
3219 , WCOR.SCHEDULE_FLAG
3220 , WCOR.ASSIGNED_UNITS
3221 /* ST : Detailed Scheduling */
3222 , WCOR.MAX_ASSIGNED_UNITS
3223 , WCOR.batch_id
3224 , WCOR.firm_type
3225 , WCOR.group_sequence_id
3226 , WCOR.group_sequence_num
3227 , WCOR.parent_resource_seq_num
3228 /* ST : Detailed Scheduling */
3229 , WCOR.AUTOCHARGE_TYPE
3230 , WCOR.STANDARD_RATE_FLAG
3231 , 0
3232 , 0
3233 , decode(recommended, 'Y', nvl(RECO_START_DATE, p_txn_date), p_txn_date)
3234 , decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, p_txn_date), p_txn_date)
3235 , WCOR.ATTRIBUTE_CATEGORY
3236 , WCOR.ATTRIBUTE1
3237 , WCOR.ATTRIBUTE2
3238 , WCOR.ATTRIBUTE3
3239 , WCOR.ATTRIBUTE4
3240 , WCOR.ATTRIBUTE5
3241 , WCOR.ATTRIBUTE6
3242 , WCOR.ATTRIBUTE7
3243 , WCOR.ATTRIBUTE8
3244 , WCOR.ATTRIBUTE9
3245 , WCOR.ATTRIBUTE10
3246 , WCOR.ATTRIBUTE11
3247 , WCOR.ATTRIBUTE12
3248 , WCOR.ATTRIBUTE13
3249 , WCOR.ATTRIBUTE14
3250 , WCOR.ATTRIBUTE15
3251 , WCOR.SCHEDULE_SEQ_NUM
3252 , WCOR.SUBSTITUTE_GROUP_NUM
3253 , WCOR.REPLACEMENT_GROUP_NUM
3254 , WCOR.PRINCIPLE_FLAG
3255 , WCOR.SETUP_ID
3256 FROM WSM_COPY_OP_RESOURCES WCOR
3257 WHERE WCOR.ORGANIZATION_ID = p_org_id
3258 AND WCOR.WIP_ENTITY_ID = p_Wip_Entity_Id
3259 AND WCOR.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
3260 AND WCOR.recommended='Y';
3261
3262 l_wor_count := SQL%ROWCOUNT;
3263
3264 if (l_debug = 'Y') then
3265 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: l_wor_count '||l_wor_count||
3266 ' l_wor_reco_res '||l_wor_reco_res);
3267 end if;
3268
3269 l_stmt_num := 80;
3270 --MES added department_id
3271 INSERT INTO WIP_SUB_OPERATION_RESOURCES
3272 (wip_entity_id,
3273 operation_seq_num,
3274 resource_seq_num,
3275 organization_id,
3276 repetitive_schedule_id,
3277 last_update_date,
3278 last_updated_by,
3279 creation_date,
3280 created_by,
3281 last_update_login,
3282 resource_id,
3283 uom_code,
3284 basis_type,
3285 usage_rate_or_amount,
3286 activity_id,
3287 scheduled_flag,
3288 assigned_units,
3289 maximum_assigned_units, /* ST : Detailed Scheduling */
3290 autocharge_type,
3291 standard_rate_flag,
3292 applied_resource_units,
3293 applied_resource_value,
3294 attribute_category,
3295 attribute1,
3296 attribute2,
3297 attribute3,
3298 attribute4,
3299 attribute5,
3300 attribute6,
3301 attribute7,
3302 attribute8,
3303 attribute9,
3304 attribute10,
3305 attribute11,
3306 attribute12,
3307 attribute13,
3308 attribute14,
3309 attribute15,
3310 completion_date,
3311 start_date,
3312 schedule_seq_num,
3313 substitute_group_num,
3314 replacement_group_num,
3315 setup_id,
3316 department_id)
3317 SELECT WCOR.wip_entity_id,
3318 p_to_job_op_seq_num,
3319 WCOR.resource_seq_num,
3320 WCOR.organization_id,
3321 null,
3322 SYSDATE ,
3323 p_user,
3324 SYSDATE,
3325 p_user,
3326 p_login,
3327 WCOR.resource_id,
3328 WCOR.uom_code,
3329 WCOR.basis_type,
3330 WCOR.usage_rate_or_amount,
3331 WCOR.activity_id,
3332 WCOR.schedule_flag,
3333 WCOR.assigned_units,
3334 WCOR.max_assigned_units,
3335 WCOR.autocharge_type,
3336 WCOR.standard_rate_flag,
3337 0, --WCOR.applied_resource_units,--move enh?
3338 0, -- WCOR.applied_resource_value, --move enh?
3339 WCOR.attribute_category,
3340 WCOR.attribute1,
3341 WCOR.attribute2,
3342 WCOR.attribute3,
3343 WCOR.attribute4,
3344 WCOR.attribute5,
3345 WCOR.attribute6,
3346 WCOR.attribute7,
3347 WCOR.attribute8,
3348 WCOR.attribute9,
3349 WCOR.attribute10,
3350 WCOR.attribute11,
3351 WCOR.attribute12,
3352 WCOR.attribute13,
3353 WCOR.attribute14,
3354 WCOR.attribute15,
3355 --as per Zhaohui copying the dates from WCOR
3356 nvl(WCOR.RECO_START_DATE, p_txn_date),
3357 nvl(WCOR.RECO_COMPLETION_DATE, p_txn_date),
3358 WCOR.schedule_seq_num,
3359 WCOR.substitute_group_num,
3360 WCOR.replacement_group_num,
3361 WCOR.setup_id,
3362 WCOR.department_id
3363 FROM WSM_COPY_OP_RESOURCES WCOR
3364 WHERE WCOR.ORGANIZATION_ID = p_org_id
3365 AND WCOR.WIP_ENTITY_ID = p_Wip_Entity_Id
3366 AND WCOR.OPERATION_SEQ_NUM = p_to_rtg_op_seq_num
3367 AND WCOR.PHANTOM_ITEM_ID IS NULL
3368 AND WCOR.recommended<>'Y';
3369
3370 if (l_debug = 'Y') then
3371 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||SQL%ROWCOUNT||
3372 ' records in WIP_SUB_OPERATION_RESOURCES.');
3373 end if;
3374
3375 l_stmt_num := 90;
3376
3377 IF (l_wor_count > 0) THEN
3378 INSERT into WIP_OP_RESOURCE_INSTANCES
3379 (WIP_ENTITY_ID
3380 , OPERATION_SEQ_NUM
3381 , RESOURCE_SEQ_NUM
3382 , ORGANIZATION_ID
3383 , LAST_UPDATE_DATE
3384 , LAST_UPDATED_BY
3385 , CREATION_DATE
3386 , CREATED_BY
3387 , LAST_UPDATE_LOGIN
3388 , INSTANCE_ID
3389 , SERIAL_NUMBER
3390 , START_DATE
3391 , COMPLETION_DATE
3392 , BATCH_ID)
3393 SELECT WCORI.WIP_ENTITY_ID
3394 , p_to_job_op_seq_num
3395 , WCORI.RESOURCE_SEQ_NUM
3396 , WCORI.ORGANIZATION_ID
3397 , SYSDATE
3398 , p_user
3399 , SYSDATE
3400 , p_user
3401 , p_login
3402 , WCORI.INSTANCE_ID
3403 , WCORI.SERIAL_NUMBER
3404 , WCORI.START_DATE
3405 , WCORI.COMPLETION_DATE
3406 , WCORI.BATCH_ID
3407 FROM WSM_COPY_OP_RESOURCE_INSTANCES WCORI
3408 -- WIP_OPERATION_RESOURCES WOR Bug 5478658 join with WOR not required
3409 WHERE WCORI.WIP_ENTITY_ID= p_wip_entity_id
3410 AND WCORI.Operation_seq_num = p_to_rtg_op_seq_num ;
3411
3412 -- Bug 5478658 Join conditions with WOR removed
3413 -- AND WOR.WIP_ENTITY_ID= WCORI.WIP_ENTITY_ID
3414 -- AND WOR.Operation_seq_num= WCORI.Operation_seq_num
3415 -- AND WOR.RESOURCE_SEQ_NUM= WCORI.RESOURCE_SEQ_NUM;
3416
3417 if (l_debug = 'Y') then
3418 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||SQL%ROWCOUNT
3419 ||' rows in WIP_OP_RESOURCE_INSTANCES');
3420 end if;
3421
3422 INSERT into wip_operation_resource_usage
3423 (WIP_ENTITY_ID,
3424 OPERATION_SEQ_NUM,
3425 RESOURCE_SEQ_NUM,
3426 REPETITIVE_SCHEDULE_ID,
3427 ORGANIZATION_ID,
3428 START_DATE,
3429 COMPLETION_DATE,
3430 ASSIGNED_UNITS,
3431 -- resource_hours, /* ST : Detailed scheduling */
3432 LAST_UPDATE_DATE,
3433 LAST_UPDATED_BY,
3434 CREATION_DATE,
3435 CREATED_BY,
3436 LAST_UPDATE_LOGIN,
3437 REQUEST_ID,
3438 PROGRAM_APPLICATION_ID,
3439 PROGRAM_ID,
3440 PROGRAM_UPDATE_DATE,
3441 INSTANCE_ID,
3442 SERIAL_NUMBER,
3443 CUMULATIVE_PROCESSING_TIME)
3444 SELECT WCORU.WIP_ENTITY_ID,
3445 p_to_job_op_seq_num,
3446 WCORU.RESOURCE_SEQ_NUM,
3447 null,
3448 WCORU.ORGANIZATION_ID,
3449 WCORU.START_DATE,
3450 WCORU.COMPLETION_DATE,
3451 WCORU.ASSIGNED_UNITS,
3452 -- WCORU.RESOURCE_HOURS, /* ST : Detailed scheduling */
3453 SYSDATE ,
3454 p_user,
3455 SYSDATE,
3456 p_user,
3457 p_login,
3458 DECODE(p_request_id, 0, '', p_request_id),
3459 DECODE(p_program_application_id, 0, '', p_program_application_id),
3460 DECODE(p_commit, 1, p_program_id, -999),
3461 DECODE(p_program_id, 0, '', SYSDATE),
3462 WCORU.INSTANCE_ID,
3463 WCORU.SERIAL_NUMBER,
3464 WCORU.CUMULATIVE_PROCESSING_TIME
3465 FROM -- WIP_OPERATION_RESOURCES WOR, Bug 5478658 join with WOR not required
3466 WSM_COPY_OP_RESOURCE_USAGE WCORU
3467 WHERE WCORU.WIP_ENTITY_ID= p_wip_entity_id
3468 AND WCORU.Operation_seq_num = p_to_rtg_op_seq_num ;
3469
3470 -- Bug 5478658 Join conditions with WOR removed
3471 -- AND WOR.WIP_ENTITY_ID= WCORU.WIP_ENTITY_ID
3472 -- AND WOR.Operation_seq_num= WCORU.Operation_seq_num
3473 -- AND WOR.RESOURCE_SEQ_NUM= WCORU.RESOURCE_SEQ_NUM;
3474
3475 END IF;
3476
3477 if (l_debug = 'Y') then
3478 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Inserted '||SQL%ROWCOUNT
3479 ||' rows in wip_operation_resource_usage');
3480 end if;
3481
3482 l_stmt_num := 100;
3483
3484 if (l_debug = 'Y') then -- czh:BUG1995161
3485 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution: Calling set_prev_next..');
3486 end if; -- czh:BUG1995161
3487
3488 set_prev_next(p_wip_entity_id,
3489 p_org_id,
3490 x_error_code,
3491 x_error_msg);
3492
3493 IF (x_error_code <> 0) THEN
3494 raise e_proc_error;
3495 END IF;
3496
3497 copy_to_op_mes_info(
3498 p_wip_entity_id => p_wip_entity_id
3499 , p_to_job_op_seq_num => p_to_job_op_seq_num
3500 , p_to_rtg_op_seq_num => p_to_rtg_op_seq_num
3501 , p_txn_quantity => p_txn_quantity
3502 , p_user => p_user
3503 , p_login => p_login
3504 , x_return_status => l_return_status
3505 , x_msg_count => l_msg_count
3506 , x_msg_data => l_msg_data
3507 );
3508
3509 IF l_return_status = g_ret_error THEN
3510 RAISE FND_API.G_EXC_ERROR;
3511 l_stmt_num := 370;
3512 IF (l_msg_count = 1) THEN
3513 x_error_code := -1;
3514 x_error_msg := l_msg_data;
3515 ELSE
3516 FOR i IN 1..l_msg_count LOOP
3517 x_error_code := -1;
3518 x_error_msg := substr(x_error_msg||fnd_msg_pub.get, 1, 4000);
3519 END LOOP;
3520 END IF;
3521 RAISE FND_API.G_EXC_ERROR;
3522 ELSIF l_return_status = g_ret_unexpected THEN
3523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3524 END IF;
3525
3526 IF (G_LOG_LEVEL_STATEMENT >= l_log_level) THEN
3527 IF (l_return_status = g_ret_success) THEN
3528 l_msg_tokens.delete;
3529 WSM_log_PVT.logMessage (
3530 p_module_name => l_module ,
3531 p_msg_text => 'WSMPOPRN.copy_to_op_mes_info returned successfully',
3532 p_stmt_num => l_stmt_num ,
3533 p_msg_tokens => l_msg_tokens ,
3534 p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
3535 p_run_log_level => l_log_level
3536 );
3537 END IF;
3538 END IF;
3539
3540 EXCEPTION
3541 -- This just means that the operation was already inserted
3542 WHEN e_proc_error THEN
3543 x_error_code := -1;
3544 x_error_msg := 'WSMPOPRN.copy_plan_to_execution('||l_stmt_num||'): '||x_error_msg;
3545 fnd_file.put_line(fnd_file.log, x_error_msg);
3546
3547 WHEN dup_val_on_index THEN
3548 BEGIN
3549 IF (p_dup_val_ignore='N') THEN
3550 x_error_code := SQLCODE;
3551 x_error_msg := 'WSMOPRNB.copy_plan_to_execution('||l_stmt_num||')'|| substr(SQLERRM,1,2000);
3552 ELSE
3553 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution other excpn: (stmt'||l_stmt_num ||'): dup_val_on_index');
3554 END IF;
3555 END;
3556
3557 WHEN others THEN
3558 x_error_code := SQLCODE;
3559 x_error_msg := 'WSMOPRNB.copy_plan_to_execution('||l_stmt_num||')'|| substr(SQLERRM,1,2000);
3560 -- czh:BUG1995161
3561 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.copy_plan_to_execution other excpn: (stmt'||l_stmt_num ||'):'||x_error_msg);
3562
3563 END copy_plan_to_execution;
3564
3565 PROCEDURE call_infinite_scheduler(
3566 x_error_code OUT NOCOPY NUMBER,
3567 x_error_msg OUT NOCOPY VARCHAR2,
3568 p_jump_flag IN VARCHAR2,
3569 p_wip_entity_id IN NUMBER,
3570 p_org_id IN NUMBER,
3571 p_to_op_seq_id IN NUMBER,
3572 p_fm_job_op_seq_num IN NUMBER,
3573 p_to_job_op_seq_num IN NUMBER,
3574 p_scheQuantity IN NUMBER)
3575 IS
3576 l_recommended_op VARCHAR2(1);
3577 x_returnStatus VARCHAR2(1);
3578 l_reco_start_date DATE;
3579 l_reco_completion_date DATE;
3580 l_infi_start_date DATE;
3581 l_stmt_num NUMBER;
3582 BEGIN
3583 l_stmt_num := 10;
3584 BEGIN
3585 SELECT nvl(recommended, 'N'), RECO_START_DATE, reco_completion_date
3586 INTO l_recommended_op, l_reco_start_date, l_reco_completion_date
3587 FROM WSM_COPY_OPERATIONS
3588 WHERE wip_entity_id = p_wip_entity_id
3589 AND operation_sequence_id = p_to_op_seq_id;
3590 EXCEPTION
3591 WHEN no_data_found THEN
3592 IF (l_debug = 'Y') THEN
3593 fnd_file.put_line(fnd_file.log, 'To Op not present in WSM_COPY_OPERATIONS');
3594 END IF;
3595 null;
3596 END;
3597
3598 IF ((p_jump_flag = 'Y') OR (l_recommended_op <> 'Y') OR (l_reco_start_date IS NULL)
3599 OR (l_reco_completion_date IS NULL)) THEN
3600 l_stmt_num := 20;
3601
3602 SELECT last_unit_completion_date
3603 INTO l_infi_start_date
3604 FROM WIP_OPERATIONS
3605 WHERE wip_entity_id = p_wip_entity_id
3606 AND organization_id = p_org_id
3607 AND operation_seq_num = p_fm_job_op_seq_num;
3608
3609 l_stmt_num := 30;
3610 wsm_infinite_scheduler_pvt.schedule(
3611 p_initMsgList => fnd_api.g_true,
3612 p_endDebug => fnd_api.g_true,
3613 p_orgID => p_org_id,
3614 p_wipEntityID => p_wip_entity_id,
3615 p_scheduleMode => WIP_CONSTANTS.CURRENT_OP,
3616 p_startDate => l_infi_start_date,
3617 p_endDate => null,
3618 p_opSeqNum => -p_to_job_op_seq_num,
3619 p_scheQuantity => p_scheQuantity,
3620 x_returnStatus => x_returnStatus,
3621 x_errorMsg => x_error_msg);
3622
3623 IF (x_returnStatus <> fnd_api.g_ret_sts_success) THEN
3624 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation calling wsm_infinite_scheduler_pvt.schedule: '||x_error_msg);
3625 x_error_code := -1;
3626 return ;
3627 ELSE
3628 IF (l_debug = 'Y') THEN
3629 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.add_operation calling wsm_infinite_scheduler_pvt.schedule returned success');
3630 END IF;
3631 END IF;
3632 END IF;
3633 x_error_code := 0;
3634 EXCEPTION
3635 WHEN others THEN
3636 x_error_code := SQLCODE;
3637 x_error_msg := 'WSMOPRNB.call_infinite_scheduler('||l_stmt_num||')'|| substr(SQLERRM,1,2000);
3638 -- czh:BUG1995161
3639 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.call_infinite_scheduler other excpn: (stmt'||l_stmt_num ||'):'||x_error_msg);
3640 END call_infinite_scheduler;
3641
3642 --bug 3162358 115.78 added this new procedure which will be called from WSMTXSFM.pld and WSMLBMIB.pls
3643 /*
3644 PROCEDURE upd_cumulative_scrap_qty(
3645 x_error_code OUT NOCOPY NUMBER
3646 , x_error_msg OUT NOCOPY VARCHAR2
3647 , p_org_id IN NUMBER
3648 , p_wip_entity_id IN NUMBER
3649 , p_job_op_seq_num IN NUMBER)
3650 IS
3651 l_stmt_num NUMBER;
3652 BEGIN
3653 l_stmt_num := 10;
3654 UPDATE WIP_OPERATIONS WO
3655 SET cumulative_scrap_quantity =
3656 (SELECT quantity_scrapped
3657 FROM WIP_DISCRETE_JOBS WDJ
3658 WHERE WDJ.wip_entity_id = p_wip_entity_id
3659 AND WDJ.organization_id = p_org_id)
3660 WHERE WO.wip_entity_id = p_wip_entity_id
3661 AND WO.organization_id = p_org_id
3662 AND WO.operation_seq_num = p_job_op_seq_num;
3663 EXCEPTION
3664 WHEN others THEN
3665 x_error_code := SQLCODE;
3666 x_error_msg := 'WSMOPRNB.upd_cumulative_scrap_qty('||l_stmt_num||')'|| substr(SQLERRM,1,2000);
3667 -- czh:BUG1995161
3668 fnd_file.put_line(fnd_file.log, 'WSMPOPRN.upd_cumulative_scrap_qty other excpn: (stmt'||l_stmt_num ||'):'||x_error_msg);
3669 END upd_cumulative_scrap_qty;
3670 */
3671
3672 /******************************************************************************************
3673 Procedure to copy mes data from setup tables to job operation tables
3674 ******************************************************************************************/
3675 Procedure copy_to_op_mes_info(
3676 p_wip_entity_id IN NUMBER
3677 , p_to_job_op_seq_num IN NUMBER
3678 , p_to_rtg_op_seq_num IN NUMBER
3679 , p_txn_quantity IN NUMBER
3680 , p_user IN NUMBER
3681 , p_login IN NUMBER
3682 , x_return_status OUT NOCOPY VARCHAR2
3683 , x_msg_count OUT NOCOPY NUMBER
3684 , x_msg_data OUT NOCOPY VARCHAR2
3685 )
3686 IS
3687 l_stmt_num NUMBER;
3688 l_serialization_started NUMBER;
3689
3690 -- Logging variables.....
3691 l_msg_tokens WSM_Log_PVT.token_rec_tbl;
3692 l_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3693 l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSMPLBMI.getJobOpPageProperties';
3694 l_param_tbl WSM_Log_PVT.param_tbl_type;
3695 x_error_count NUMBER;
3696 x_return_code NUMBER;
3697 x_error_msg VARCHAR2(4000);
3698
3699 BEGIN
3700 x_return_status := G_RET_SUCCESS;
3701 IF FND_LOG.LEVEL_PROCEDURE >= l_log_level THEN
3702
3703 l_param_tbl.delete;
3704 l_param_tbl(1).paramName := 'p_wip_entity_id';
3705 l_param_tbl(1).paramValue := p_wip_entity_id;
3706 l_param_tbl(2).paramName := 'p_to_job_op_seq_num';
3707 l_param_tbl(2).paramValue := p_to_job_op_seq_num;
3708 l_param_tbl(3).paramName := 'p_txn_quantity';
3709 l_param_tbl(3).paramValue := p_txn_quantity;
3710 l_param_tbl(4).paramName := 'p_user';
3711 l_param_tbl(5).paramValue := p_user;
3712 l_param_tbl(6).paramName := 'p_login';
3713 l_param_tbl(6).paramValue := p_login;
3714
3715 WSM_Log_PVT.logProcParams(p_module_name => l_module ,
3716 p_param_tbl => l_param_tbl,
3717 p_fnd_log_level => G_LOG_LEVEL_PROCEDURE
3718 );
3719 END IF;
3720
3721 INSERT INTO WSM_OP_SECONDARY_QUANTITIES
3722 (
3723 LAST_UPDATE_DATE,
3724 LAST_UPDATED_BY,
3725 LAST_UPDATE_LOGIN,
3726 CREATION_DATE,
3727 CREATED_BY,
3728 ORGANIZATION_ID,
3729 WIP_ENTITY_ID,
3730 UOM_CODE,
3731 OPERATION_SEQ_NUM,
3732 MOVE_IN_QUANTITY,
3733 MOVE_OUT_QUANTITY
3734 )
3735 SELECT SYSDATE,
3736 p_user,
3737 p_login,
3738 SYSDATE,
3739 p_user,
3740 ORGANIZATION_ID,
3741 WIP_ENTITY_ID,
3742 UOM_CODE,
3743 p_to_job_op_seq_num,
3744 CURRENT_QUANTITY,
3745 NULL
3746 FROM WSM_JOB_SECONDARY_QUANTITIES
3747 WHERE WIP_ENTITY_ID = p_wip_entity_id
3748 and currently_active = 1;
3749
3750 l_stmt_num := 92;
3751
3752 INSERT INTO WSM_OP_REASON_CODES
3753 (
3754 ORGANIZATION_ID,
3755 WIP_ENTITY_ID,
3756 OPERATION_SEQ_NUM,
3757 CODE_TYPE,
3758 REASON_CODE,
3759 QUANTITY,
3760 CREATED_BY,
3761 LAST_UPDATE_DATE,
3762 LAST_UPDATED_BY,
3763 CREATION_DATE,
3764 LAST_UPDATED_LOGIN
3765 )
3766 SELECT DISTINCT ORGANIZATION_ID,
3767 WIP_ENTITY_ID,
3768 OPERATION_SEQ_NUM,
3769 1, --CODE_TYPE,
3770 SCRAP_CODE,
3771 NULL,
3772 p_user,
3773 SYSDATE,
3774 p_user,
3775 SYSDATE,
3776 p_login
3777 FROM BOM_STD_OP_SCRAP_CODES BSOSC,
3778 WIP_OPERATIONS WO
3779 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
3780 AND WO.OPERATION_SEQ_NUM = p_to_job_op_seq_num
3781 AND BSOSC.STANDARD_OPERATION_ID = WO.STANDARD_OPERATION_ID;
3782
3783 l_stmt_num := 93;
3784 INSERT INTO WSM_OP_REASON_CODES
3785 (
3786 ORGANIZATION_ID,
3787 WIP_ENTITY_ID,
3788 OPERATION_SEQ_NUM,
3789 CODE_TYPE,
3790 REASON_CODE,
3791 QUANTITY,
3792 CREATED_BY,
3793 LAST_UPDATE_DATE,
3794 LAST_UPDATED_BY,
3795 CREATION_DATE,
3796 LAST_UPDATED_LOGIN
3797 )
3798 SELECT DISTINCT ORGANIZATION_ID,
3799 WIP_ENTITY_ID,
3800 OPERATION_SEQ_NUM,
3801 2, --CODE_TYPE,
3802 BONUS_CODE,
3803 NULL,
3804 p_user,
3805 SYSDATE,
3806 p_user,
3807 SYSDATE,
3808 p_login
3809 FROM BOM_STD_OP_BONUS_CODES BSOSC,
3810 WIP_OPERATIONS WO
3811 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
3812 AND WO.OPERATION_SEQ_NUM = p_to_job_op_seq_num
3813 AND BSOSC.STANDARD_OPERATION_ID = WO.STANDARD_OPERATION_ID;
3814
3815 UPDATE WSM_LOT_BASED_JOBS
3816 SET current_job_op_seq_num = p_to_job_op_seq_num,
3817 current_rtg_op_seq_num = p_to_rtg_op_seq_num
3818 WHERE WIP_ENTITY_ID = p_wip_entity_id;
3819
3820
3821
3822 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
3823 p_count => x_error_count ,
3824 p_data => x_error_msg
3825 );
3826 EXCEPTION
3827 WHEN FND_API.G_EXC_ERROR THEN
3828
3829 x_return_status := G_RET_ERROR;
3830 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
3831 p_count => x_error_count ,
3832 p_data => x_error_msg
3833 );
3834
3835 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3836
3837 x_return_status := G_RET_UNEXPECTED;
3838
3839 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
3840 p_count => x_error_count ,
3841 p_data => x_error_msg
3842 );
3843 WHEN OTHERS THEN
3844
3845 x_return_status := G_RET_UNEXPECTED;
3846
3847 IF (G_LOG_LEVEL_UNEXPECTED >= l_log_level) OR
3848 (FND_MSG_PUB.check_msg_level(G_MSG_LVL_UNEXP_ERROR))
3849 THEN
3850 WSM_log_PVT.handle_others( p_module_name => l_module ,
3851 p_stmt_num => l_stmt_num ,
3852 p_fnd_log_level => G_LOG_LEVEL_UNEXPECTED ,
3853 p_run_log_level => l_log_level
3854 );
3855 END IF;
3856
3857 FND_MSG_PUB.Count_And_Get ( p_encoded => 'F' ,
3858 p_count => x_error_count ,
3859 p_data => x_error_msg
3860 );
3861 END;
3862 --MES END
3863
3864 --bug 5337172 intermediate function generated by rosetta
3865 function update_job_name(p_wip_entity_id NUMBER
3866 , p_subinventory VARCHAR2
3867 , p_org_id NUMBER
3868 , p_txn_type NUMBER
3869 , p_update_flag number
3870 , p_dup_job_name out nocopy VARCHAR2
3871 , x_error_code out nocopy NUMBER
3872 , x_error_msg out nocopy VARCHAR2
3873 ) return varchar2
3874
3875 as
3876 ddp_update_flag boolean;
3877 ddindx binary_integer; indx binary_integer;
3878 ddrosetta_retval varchar2(4000);
3879 begin
3880
3881 -- copy data to the local IN or IN-OUT args, if any
3882
3883 if p_update_flag is null
3884 then ddp_update_flag := null;
3885 elsif p_update_flag = 0
3886 then ddp_update_flag := false;
3887 else ddp_update_flag := true;
3888 end if;
3889
3890 -- here's the delegated call to the old PL/SQL routine
3891 ddrosetta_retval := WSMPOPRN.update_job_name(p_wip_entity_id,
3892 p_subinventory,
3893 p_org_id,
3894 p_txn_type,
3895 ddp_update_flag,
3896 p_dup_job_name,
3897 x_error_code,
3898 x_error_msg);
3899
3900 -- copy data back from the local variables to OUT or IN-OUT args, if any
3901
3902 return ddrosetta_retval;
3903 end;
3904 --end bug 5337172
3905 END WSMPOPRN;
3906