[Home] [Help]
PACKAGE BODY: APPS.WSM_JOBCOPIES_PVT
Source
1 PACKAGE BODY WSM_JobCopies_PVT AS
2 /* $Header: WSMVCPYB.pls 120.23.12010000.2 2008/11/27 12:30:01 lmente ship $ */
3
4 /*-------------------------------------------------------------+
5 | Name : Get_Job_Curr_Op_Info
6 ---------------------------------------------------------------*/
7 --OPTII-PERF: Following tables are added to store max res seq num
8 --for each op seq id.
9 type t_job_res is table of NUMBER index by binary_integer;
10
11 v_res_seq t_job_res;
12 v_op_seq_id t_job_res;
13 v_max_res_seq t_job_res;
14
15
16 PROCEDURE Get_Job_Curr_Op_Info(p_wip_entity_id IN NUMBER,
17 p_op_seq_num OUT NOCOPY NUMBER, -- max operation where qty exists
18 p_op_seq_id OUT NOCOPY NUMBER,
19 p_std_op_id OUT NOCOPY NUMBER,
20 p_dept_id OUT NOCOPY NUMBER,
21 p_intra_op OUT NOCOPY NUMBER, -- intra-op where qty exists
22 p_op_qty OUT NOCOPY NUMBER, -- qty available to transact
23 p_op_start_date OUT NOCOPY DATE,
24 p_op_comp_date OUT NOCOPY DATE,
25 x_err_code OUT NOCOPY NUMBER,
26 x_err_buf OUT NOCOPY VARCHAR2)
27 IS
28 l_stmt_num NUMBER;
29 l_qty_Q NUMBER;
30 l_qty_RUN NUMBER;
31 l_qty_TM NUMBER;
32 l_qty_SCR NUMBER;
33 l_temp NUMBER;
34
35 BEGIN
36
37 p_op_seq_num := NULL;
38 p_op_seq_id := NULL;
39 p_std_op_id := NULL;
40 p_dept_id := NULL;
41 p_intra_op := NULL;
42 p_op_qty := NULL;
43
44 l_stmt_num := 10;
45
46 SELECT max(operation_seq_num)
47 INTO p_op_seq_num
48 FROM wip_operations
49 WHERE wip_entity_id = p_wip_entity_id
50 AND ((quantity_in_queue <> 0
51 OR quantity_running <> 0
52 OR quantity_waiting_to_move <> 0)
53 OR (quantity_in_queue = 0
54 and quantity_running = 0
55 and quantity_waiting_to_move = 0
56 and quantity_scrapped = quantity_completed
57 -- this picks up the max op seq, if only scraps at ops
58 and quantity_completed > 0));
59
60 l_stmt_num := 20;
61 IF (g_debug = 'Y') THEN
62 fnd_file.put_line(fnd_file.log, 'At stmt '||l_stmt_num||' p_op_seq_num='||p_op_seq_num);
63 END IF;
64
65 IF (p_op_seq_num IS NULL) THEN -- Job creation : No records in WO for this p_wip_entity_id
66 -- OR
67 -- Unreleased Job : No qty in any operation
68 -- OR
69 -- Completed Job
70
71 -- Start : Additions to fix bug #3677276
72
73 -- Check for Completed Job Condition
74 l_stmt_num := 22;
75
76 SELECT count(*)
77 INTO l_temp
78 FROM wip_operations
79 WHERE wip_entity_id = p_wip_entity_id;
80
81 IF (l_temp > 1) THEN -- Completed Job
82 x_err_code := -2;
83 IF (g_debug = 'Y') THEN
84 fnd_file.put_line(fnd_file.log, 'Job is completed');
85 END IF;
86 return;
87 END IF;
88 -- End : Additions to fix bug #3677276
89
90 -- Check for Unreleased Job Condition
91 l_stmt_num := 25;
92 SELECT max(operation_seq_num)
93 INTO p_op_seq_num
94 FROM wip_operations
95 WHERE wip_entity_id = p_wip_entity_id
96 AND quantity_in_queue = 0
97 AND quantity_running = 0
98 AND quantity_waiting_to_move = 0
99 AND quantity_scrapped = 0;
100
101 IF (g_debug = 'Y') THEN
102 fnd_file.put_line(fnd_file.log, 'At stmt '||l_stmt_num||' p_op_seq_num='||p_op_seq_num);
103 END IF;
104
105 IF (p_op_seq_num IS NULL) THEN -- Job creation : No records in WO for this p_wip_entity_id
106 x_err_code := -1;
107 x_err_buf := 'WSM_JobCopies_PVT.Get_Job_Curr_Op_Info('||l_stmt_num||'): Warning ! No operations in the job';
108 IF (g_debug = 'Y') THEN
109 fnd_file.put_line(fnd_file.log, x_err_buf);
110 END IF;
111 return;
112 END IF;
113
114 END IF;
115
116 l_stmt_num := 30;
117
118 SELECT operation_sequence_id,
119 standard_operation_id,
120 department_id,
121 quantity_in_queue,
122 quantity_running,
123 quantity_waiting_to_move,
124 quantity_scrapped,
125 first_unit_start_date,
126 last_unit_completion_date
127 INTO p_op_seq_id,
128 p_std_op_id,
129 p_dept_id,
130 l_qty_Q,
131 l_qty_RUN,
132 l_qty_TM,
133 l_qty_SCR,
134 p_op_start_date,
135 p_op_comp_date
136 FROM wip_operations
137 WHERE wip_entity_id = p_wip_entity_id
138 AND operation_seq_num = p_op_seq_num;
139
140 IF l_qty_Q > 0 THEN
141 p_intra_op := 1;
142 p_op_qty := l_qty_Q;
143 ELSIF l_qty_RUN > 0 THEN
144 p_intra_op := 2;
145 p_op_qty := l_qty_RUN;
146 ELSIF l_qty_TM > 0 THEN
147 p_intra_op := 3;
148 p_op_qty := l_qty_TM;
149 ELSIF l_qty_SCR > 0 THEN
150 p_intra_op := 5;
151 p_op_qty := l_qty_SCR;
152 ELSE -- Unreleased Job
153 p_intra_op := 1;
154 p_op_qty := 0;
155 END IF;
156
157 l_stmt_num := 40;
158 x_err_code := 0;
159 x_err_buf := NULL;
160
161 EXCEPTION
162 WHEN OTHERS THEN
163 x_err_code := SQLCODE;
164 x_err_buf := 'WSM_JobCopies_PVT.Get_Job_Curr_Op_Info('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
165 fnd_file.put_line(fnd_file.log, x_err_buf);
166
167 END Get_Job_Curr_Op_Info;
168
169
170 /*************************
171 ** **
172 ** Create_JobCopies **
173 ** **
174 ** **
175 ** x_err_code -> **
176 ** =0 implies no errors**
177 ** =-1 implies warnings **
178 ** else, error code **
179 ** **
180 *************************/
181
182 PROCEDURE Create_JobCopies (x_err_buf OUT NOCOPY VARCHAR2,
183 x_err_code OUT NOCOPY NUMBER,
184 p_wip_entity_id IN NUMBER,
185 p_org_id IN NUMBER,
186 p_primary_item_id IN NUMBER,
187
188 p_routing_item_id IN NUMBER,
189 p_alt_rtg_desig IN VARCHAR2,
190 p_rtg_seq_id IN NUMBER,
191 -- Will be NULL till reqd for some functionality
192 p_common_rtg_seq_id IN NUMBER,
193 p_rtg_rev_date IN DATE,
194
195 p_bill_item_id IN NUMBER,
196 p_alt_bom_desig IN VARCHAR2,
197 p_bill_seq_id IN NUMBER,
198 p_common_bill_seq_id IN NUMBER,
199 p_bom_rev_date IN DATE,
200
201 p_wip_supply_type IN NUMBER,
202 p_last_update_date IN DATE,
203 p_last_updated_by IN NUMBER,
204 p_last_update_login IN NUMBER,
205 p_creation_date IN DATE,
206 p_created_by IN NUMBER,
207 p_request_id IN NUMBER,
208 p_program_app_id IN NUMBER,
209 p_program_id IN NUMBER,
210 p_program_update_date IN DATE,
211 p_inf_sch_flag IN VARCHAR2, --Y/N
212 p_inf_sch_mode IN NUMBER, --NULL/FORWARDS/BACKWARDS/MIDPOINT_FORWARDS/MIDPOINT_BACKWARDS/CURRENT_OP
213 p_inf_sch_date IN DATE , --based on mode, this will be start/completion date
214 p_new_job IN NUMBER DEFAULT NULL,
215 p_insert_wip IN NUMBER DEFAULT NULL,
216 p_phantom_exists IN NUMBER DEFAULT NULL,
217 p_charges_exist IN NUMBER DEFAULT NULL
218 )
219 IS
220
221 l_stmt_num NUMBER := 0;
222 DATETIME_FMT CONSTANT VARCHAR2(22) := 'YYYY/MM/DD HH24:MI:SS';
223 -- Note : Cannot use WSMPCNST.C_DATETIME_FMT since BOM requires date in the above format,
224 -- which is different than the one defined in WSMPCNST.
225
226 SORT_WIDTH CONSTANT NUMBER := 4;/*to be replaced with the bom profile value*/
227
228 -- Setup related variables --
229 l_wip_param_def_subinv VARCHAR2(10);
230 l_wip_param_def_locator_id NUMBER;
231 l_use_phantom_routings NUMBER := 0;
232 l_max_bill_levels NUMBER := 60;
233 l_explosion_group_id NUMBER := NULL;
234 l_op_seq_one_exists_in_ntwk NUMBER;
235 l_top_level_bill_seq_id NUMBER;
236
237 -- Current Operation related variables --
238 l_curr_op_seq_num NUMBER;
239 l_curr_op_seq_id NUMBER;
240 l_curr_op_std_op_id NUMBER;
241 l_curr_op_dept_id NUMBER;
242 l_curr_op_intra_op NUMBER;
243 l_curr_op_qty NUMBER;
244 l_is_curr_op_reco VARCHAR2(1);
245 l_curr_op_start_date DATE;
246 l_curr_op_compl_date DATE;
247 l_curr_op_first_level_comps NUMBER := 0;
248 l_curr_op_total_comps NUMBER := 0;
249 l_curr_op_max_res_seq NUMBER := 10;-- Seq# to add more resources at curr op
250 l_curr_op_is_ntwk_st_end VARCHAR2(1);
251 l_curr_first_op_attach_opseq1 NUMBER;
252
253 -- Phantoms related variables --
254 l_phantom_bill_seq_id NUMBER; -- For the phantom bill exploded
255 l_phantom_org_id NUMBER;
256 l_phantom_reqd_qty NUMBER := 1;
257 l_phantom_rtg_seq_id NUMBER;
258 l_phantom_expl_group_id NUMBER := NULL;
259
260 -- Infinite Scheduler related variables --
261 l_inf_sch_flag VARCHAR2(1);
262 l_inf_sch_mode NUMBER;
263 l_inf_sch_start_date DATE;
264 l_inf_sch_comp_date DATE;
265 l_inf_sch_returnStatus VARCHAR2(1);
266
267 -- Recommended Path related variables --
268 l_reco_path_op_seq_num NUMBER;
269 --l_reco_path_level NUMBER; --OPTII-PERF
270 l_reco_path_seq_num NUMBER; --OPTII-PERF
271 l_reco_path_start_op_seq NUMBER;
272 l_reco_path_end_op_seq NUMBER; -- Added to fix bug #3343865
273 l_network_start_op_seq NUMBER; -- Added to fix bug #3400858
274
275 -- Counters for loop --
276 l_first_level_comps_ctr NUMBER := 0; -- Earlier i
277 l_all_level_comps_ctr NUMBER := 0; -- Earlier l
278 l_all_level_comps_subctr NUMBER := 0; -- Earlier m
279
280 -- Others --
281 l_be_count NUMBER;
282 l_err_buf VARCHAR2(4000);
283 l_err_code NUMBER;
284 l_network_start VARCHAR2(1) := 'S'; -- Added to fix bug #3761385
285 l_network_end VARCHAR2(1) := 'E'; -- Added to fix bug #3761385
286 l_ato_phantom VARCHAR2(1) := 'N'; -- added for bug 7598223
287
288 -- Exceptions --
289 be_exploder_exception EXCEPTION;
290 loop_in_bom_exception EXCEPTION;
291 e_proc_error EXCEPTION;
292 e_noneff_op EXCEPTION; --Bug 4264364:Exception is added.
293
294 l_serial_start_op NUMBER; --SR12:Serial Support
295
296 CURSOR job_ops IS
297 SELECT operation_seq_num,
298 operation_sequence_id,
299 network_start_end,
300 reco_start_date,
301 reco_completion_date,
302 department_id
303 FROM wsm_copy_operations
304 WHERE wip_entity_id = p_wip_entity_id
305 ORDER BY operation_seq_num;
306
307 -- OptII Perf: New cursors ......
308 CURSOR c_job_ops IS
309 SELECT BOS.operation_seq_num operation_seq_num,
310 'N' recommended, --recommended
311 null network_start_end,
312 null RECO_PATH_SEQ_NUM,
313 BOS.operation_sequence_id,
314 BOS.routing_sequence_id,
315 BOS.standard_operation_id,
316 BSO.operation_code Standard_operation_code,
317 BOS.department_id,
318 BD.department_code,
319 BD.scrap_account,
320 BD.est_absorption_account,
321 BOS.operation_lead_time_percent,
322 BOS.minimum_transfer_quantity,
323 BOS.count_point_type,
324 BOS.operation_description,
325 BOS.effectivity_date,
326 BOS.disable_date,
327 BOS.backflush_flag,
328 BOS.option_dependent_flag,
329 BOS.operation_type,
330 BOS.reference_flag,
331 nvl(BOS.yield, 1) yield, -- CST will consider as 1, if NULL
332 BOS.implementation_date,
333 BOS.cumulative_yield,
334 BOS.reverse_cumulative_yield,
335 BOS.labor_time_calc,
336 BOS.machine_time_calc,
337 BOS.total_time_calc,
338 BOS.labor_time_user,
339 BOS.machine_time_user,
340 BOS.total_time_user,
341 BOS.net_planning_percent,
342 BOS.x_coordinate,
343 BOS.y_coordinate,
344 BOS.include_in_rollup,
345 BOS.operation_yield_enabled,
346 BOS.old_operation_sequence_id,
347 BOS.acd_type,
348 BOS.revised_item_sequence_id,
349 BOS.change_notice,
350 BOS.eco_for_production,
351 BOS.shutdown_type,
352 BOS.actual_ipk,
353 BOS.critical_to_quality,
354 BOS.value_added,
355 p_last_update_date,
356 p_last_updated_by,
357 p_last_update_login,
358 p_creation_date,
359 p_created_by,
360 p_request_id,
361 p_program_app_id,
362 p_program_id,
363 p_program_update_date,
364 BOS.attribute_category,
365 BOS.attribute1,
366 BOS.attribute2,
367 BOS.attribute3,
368 BOS.attribute4,
369 BOS.attribute5,
370 BOS.attribute6,
371 BOS.attribute7,
372 BOS.attribute8,
373 BOS.attribute9,
374 BOS.attribute10,
375 BOS.attribute11,
376 BOS.attribute12,
377 BOS.attribute13,
378 BOS.attribute14,
379 BOS.attribute15,
380 BOS.original_system_reference,
381 BOS.lowest_acceptable_yield --mes
382 FROM BOM_OPERATION_SEQUENCES BOS,
383 BOM_STANDARD_OPERATIONS BSO,
384 BOM_DEPARTMENTS BD
385 WHERE BOS.routing_sequence_id = p_common_rtg_seq_id
386 AND p_rtg_rev_date between BOS.effectivity_date and nvl(BOS.disable_date, p_rtg_rev_date+1)
387 AND BOS.standard_operation_id = BSO.standard_operation_id (+)
388 AND BOS.department_id = BD.department_id;
389
390 CURSOR c_job_nw is
391 SELECT
392 BON.from_op_seq_id,
393 BON.to_op_seq_id,
394 'N' recommended, -- recommended : Later the contiguous part of primary path will be set to Y
395 BON.transition_type,
396 BON.planning_pct,
397 BON.attribute_category,
398 BON.attribute1,
399 BON.attribute2,
400 BON.attribute3,
401 BON.attribute4,
402 BON.attribute5,
403 BON.attribute6,
404 BON.attribute7,
405 BON.attribute8,
406 BON.attribute9,
407 BON.attribute10,
408 BON.attribute11,
409 BON.attribute12,
410 BON.attribute13,
411 BON.attribute14,
412 BON.attribute15,
413 BON.original_system_reference
414 FROM BOM_OPERATION_NETWORKS BON
415 WHERE BON.from_op_seq_id in (select operation_sequence_id from
416 bom_operation_sequences where routing_sequence_id = p_common_rtg_seq_id);
417
418 CURSOR C_eff_opseq_id IS
419 select bos1.operation_sequence_id new_op_seq_id,
420 bos2.operation_sequence_id old_op_seq_id
421 from BOM_OPERATION_SEQUENCES BOS1,
422 BOM_OPERATION_SEQUENCES BOS2
423 Where BOS1.routing_sequence_id = p_common_rtg_seq_id
424 AND BOS2.routing_sequence_id = p_common_rtg_seq_id
425 AND (p_rtg_rev_date < BOS2.effectivity_date OR
426 p_rtg_rev_date >= nvl(BOS2.disable_date, p_rtg_rev_date-1))
427 AND BOS1.operation_seq_num = BOS2.operation_seq_num
428 AND p_rtg_rev_date between BOS1.effectivity_date and nvl(BOS1.disable_date, p_rtg_rev_date+1);
429
430
431
432 type t_job_ops is table of WSM_COPY_OPERATIONS%rowtype index by binary_integer;
433 type t_job_nw is table of WSM_COPY_OP_NETWORKS%rowtype index by binary_integer;
434
435
436 v_job_ops t_job_ops;
437 v_job_nw t_job_nw;
438 l_level NUMBER;
439 l_counter NUMBER;
440 l_valid_start_op NUMBER;
441 l_op_seq_num NUMBER;
442 -- Construct a table of records....
443 -- for a network like 10--> 20 --> 30
444 -- 10--> 40 --> 30 (alternate)
445 -- we'll store the info as a(10) := 20
446 -- a(20) := 30;
447 -- End op seq num will be anyway known...
448 -- if a op in this table doesnt exist in the above table constructed for operations
449 -- then there is a Exit in the link...
450
451
452 -- ST performance fix end --
453
454 --To be used only when phantom components exist
455
456 CURSOR reqs is
457 -- Primary Components --
458 SELECT * -- Fix for bug #3313480
459 FROM -- Fix for bug #3313480
460 ( -- Fix for bug #3313480 -- Start of union of 2 sqls
461 SELECT o.operation_seq_num,
462 a.component_item_id component_item_id,
463 a.component_item_id primary_component_id,
464 a.component_sequence_id,
465 decode(decode(p_wip_supply_type,
466 7,
467 nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),
468 p_wip_supply_type),
469 6,
470 a.component_item_id,
471 -1) source_phantom_id, --populate only for phantoms, else -1
472 'Y' recommended,
473 o.reco_start_date,
474 a.bill_sequence_id,
475 o.department_id,
476
477 decode(p_wip_supply_type,
478 7,
479 nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),
480 p_wip_supply_type) wip_supply_type,
481 --Bug 5216333: Suppy type specified at job should be looked at first
482 decode (p_org_id,
483 l_phantom_org_id,
484 nvl(a.supply_subinventory,
485 nvl(c.wip_supply_subinventory,
486 decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),p_wip_supply_type),
487 2,
488 l_wip_param_def_subinv,
489 3,
490 l_wip_param_def_subinv,
491 null
492 )
493 )
494 ),
495 nvl(c.wip_supply_subinventory,
496 decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),p_wip_supply_type),
497 2,
498 l_wip_param_def_subinv,
499 3,
500 l_wip_param_def_subinv,
501 null
502 )
503 )
504 ) supply_subinventory,
505 decode (p_org_id, -- supply locator id begin
506 l_phantom_org_id,
507 --Bug 5216333: Suppy type specified at job should be looked at first
508 decode (a.supply_subinventory,
509 null,
510 decode (c.wip_supply_subinventory,
511 null,
512 decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type,
513 nvl(c.wip_supply_type,1)),p_wip_supply_type),
514 2,
515 --nvl(l_wip_param_def_locator_id,-1),
516 l_wip_param_def_locator_id,
517 3,
518 --nvl(l_wip_param_def_locator_id,-1),
519 l_wip_param_def_locator_id,
520 null
521 ),
522 c.wip_supply_locator_id
523 ),
524 a.supply_locator_id
525 ),
526 decode (c.wip_supply_subinventory,
527 null,
528 decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),p_wip_supply_type),
529 2,
530 nvl(l_wip_param_def_locator_id,-1),
531 3,
532 nvl(l_wip_param_def_locator_id,-1),
533 null
534 ),
535 c.wip_supply_locator_id
536 )
537 ) supply_locator_id, -- supply locator id end
538
539 a.component_quantity / decode(a.component_yield_factor,
540 0,
541 1,
542 a.component_yield_factor
543 ) required_quantity,
544
545 a.component_quantity BILL_QUANTITY_PER_ASSEMBLY,
546
547 a.component_yield_factor,
548 a.basis_type, --LBM enh
549 a.effectivity_date effectivity_date,
550 a.disable_date,
551 null component_priority, -- bug 7143452
552 a.parent_bill_seq_id,
553
554 a.item_num,
555 a.component_remarks,
556 a.change_notice,
557 a.implementation_date,
558 a.planning_factor,
559 a.quantity_related,
560 a.so_basis,
561 a.optional,
562 a.mutually_exclusive_options,
563 a.include_in_cost_rollup,
564 a.check_atp,
565 a.shipping_allowed,
566 a.required_to_ship,
567 a.required_for_revenue,
568 a.include_on_ship_docs,
569 a.low_quantity,
570 a.high_quantity,
571 a.acd_type,
572 a.old_component_sequence_id,
573 a.operation_lead_time_percent,
574 a.revised_item_sequence_id,
575 a.bom_item_type,
576 a.from_end_item_unit_number,
577 a.to_end_item_unit_number,
578 a.eco_for_production,
579 a.enforce_int_requirements,
580 a.delete_group_name,
581 a.dg_description,
582 a.optional_on_model,
583 a.model_comp_seq_id,
584 a.plan_level,
585 a.auto_request_material,
586 a.component_item_revision_id,
587 a.from_bill_revision_id,
588 a.to_bill_revision_id,
589 a.pick_components,
590 a.include_on_bill_docs,
591 a.cost_factor,
592 a.original_system_reference,
593 a.attribute_category,
594 a.attribute1,
595 a.attribute2,
596 a.attribute3,
597 a.attribute4,
598 a.attribute5,
599 a.attribute6,
600 a.attribute7,
601 a.attribute8,
602 a.attribute9,
603 a.attribute10,
604 a.attribute11,
605 a.attribute12,
606 a.attribute13,
607 a.attribute14,
608 a.attribute15
609 FROM WSM_COPY_OPERATIONS O,
610 BOM_INVENTORY_COMPONENTS A,
611 MTL_SYSTEM_ITEMS C,
612 BOM_EXPLOSION_TEMP BE
613 WHERE be.group_id = l_explosion_group_id
614 AND be.top_bill_sequence_id = l_top_level_bill_seq_id
615 AND a.component_sequence_id = be.component_sequence_id
616 AND be.component_item_id = c.inventory_item_id
617 AND c.organization_id = p_org_id
618 AND o.wip_entity_id = p_wip_entity_id
619 AND o.operation_sequence_id = l_curr_op_seq_id
620 AND ((A.operation_seq_num = (SELECT operation_seq_num
621 FROM WSM_COPY_OPERATIONS
622 WHERE operation_sequence_id =
623 l_curr_op_seq_id
624 AND wip_entity_id = p_wip_entity_id
625 )
626 )
627 OR
628 (l_curr_first_op_attach_opseq1 = 1 and a.operation_seq_num = 1)
629 )
630 AND p_bom_rev_date between a.effectivity_date and nvl(a.disable_date, p_bom_rev_date+1)
631 AND A.EFFECTIVITY_DATE = (SELECT MAX(EFFECTIVITY_DATE)
632 FROM BOM_INVENTORY_COMPONENTS BIC,
633 ENG_REVISED_ITEMS ERI
634 WHERE BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
635 AND BIC.COMPONENT_ITEM_ID = A.COMPONENT_ITEM_ID
636 AND (decode(BIC.IMPLEMENTATION_DATE,
637 NULL,
638 BIC.OLD_COMPONENT_SEQUENCE_ID,
639 BIC.COMPONENT_SEQUENCE_ID
640 ) =
641 decode(A.IMPLEMENTATION_DATE,
642 NULL,
643 A.OLD_COMPONENT_SEQUENCE_ID,
644 A.COMPONENT_SEQUENCE_ID
645 )
646 OR
647 BIC.OPERATION_SEQ_NUM = A.OPERATION_SEQ_NUM
648 )
649 AND BIC.EFFECTIVITY_DATE <= p_bom_rev_date
650 AND BIC.REVISED_ITEM_SEQUENCE_ID =
651 ERI.REVISED_ITEM_SEQUENCE_ID(+)
652 AND (nvl(ERI.STATUS_TYPE,6) IN (4,6,7))
653 AND NOT EXISTS
654 (SELECT 'X'
655 FROM BOM_INVENTORY_COMPONENTS BICN,
656 ENG_REVISED_ITEMS ERI1
657 WHERE BICN.BILL_SEQUENCE_ID =
658 A.BILL_SEQUENCE_ID
659 AND BICN.OLD_COMPONENT_SEQUENCE_ID =
660 A.COMPONENT_SEQUENCE_ID
661 AND BICN.ACD_TYPE in (2,3)
662 AND BICN.DISABLE_DATE <=
663 p_bom_rev_date
664 AND ERI1.REVISED_ITEM_SEQUENCE_ID =
665 BICN.REVISED_ITEM_SEQUENCE_ID
666 AND (nvl(ERI1.STATUS_TYPE,6)IN(4,6,7))
667 )
668 )
669
670 UNION ALL -- ST : Performance fix : replaced UNION with UNION ALL
671 -- Substitute components --
672 SELECT O.OPERATION_SEQ_NUM,
673 S.SUBSTITUTE_COMPONENT_ID COMPONENT_ITEM_ID,
674 A.COMPONENT_ITEM_ID PRIMARY_COMPONENT_ID,
675 S.COMPONENT_SEQUENCE_ID,
676 decode(decode(p_wip_supply_type,
677 7,
678 nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),
679 p_wip_supply_type),
680 6,
681 S.SUBSTITUTE_COMPONENT_ID,
682 -1) source_phantom_id, --populate only for phantoms, else -1
683
684 'N' recommended,
685 o.reco_start_date,
686 A.BILL_SEQUENCE_ID,
687 o.department_id,
688
689 decode(p_wip_supply_type,
690 7,
691 nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),
692 p_wip_supply_type) wip_supply_type,
693 --Bug 5216333: Suppy type specified at job should be looked at first
694 decode (p_org_id,
695 l_phantom_org_id,
696 nvl(A.SUPPLY_SUBINVENTORY,
697 nvl(C.WIP_SUPPLY_SUBINVENTORY,
698 decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),p_wip_supply_type),
699 2,
700 l_wip_param_def_subinv,
701 3,
702 l_wip_param_def_subinv,
703 NULL
704 )
705 )
706 ),
707 nvl(C.WIP_SUPPLY_SUBINVENTORY,
708 decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),p_wip_supply_type),
709 2,
710 l_wip_param_def_subinv,
711 3,
712 l_wip_param_def_subinv,
713 NULL
714 )
715 )
716 ) supply_subinventory,
717 --Bug 4755122: nvl(l_wip_param_def_locator_id,-1) is replaced with l_wip_param_def_locator_id
718 --Bug 5216333: Suppy type specified at job should be looked at first
719 decode (p_org_id, -- Supply locator id begin
720 l_phantom_org_id,
721 decode (A.SUPPLY_SUBINVENTORY,
722 NULL,
723 decode (C.WIP_SUPPLY_SUBINVENTORY,
724 NULL,
725 decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE,
726 nvl(C.WIP_SUPPLY_TYPE,1)),p_wip_supply_type),
727 2,
728 --nvl(l_wip_param_def_locator_id,-1),
729 l_wip_param_def_locator_id,
730 3,
731 l_wip_param_def_locator_id,
732 NULL
733 ),
734 C.WIP_SUPPLY_LOCATOR_ID
735 ),
736 A.SUPPLY_LOCATOR_ID
737 ),
738 decode (C.WIP_SUPPLY_SUBINVENTORY,
739 NULL,
740 decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),p_wip_supply_type),
741 2,
742 --nvl(l_wip_param_def_locator_id,-1),
743 l_wip_param_def_locator_id,
744 3,
745 l_wip_param_def_locator_id,
746 NULL
747 ),
748 C.WIP_SUPPLY_LOCATOR_ID
749 )
750 ) supply_locator_id, -- Supply locator id end
751
752 S.SUBSTITUTE_ITEM_QUANTITY / decode(A.COMPONENT_YIELD_FACTOR,
753 0,
754 1,
755 A.COMPONENT_YIELD_FACTOR
756 ) required_quantity,
757
758 S.SUBSTITUTE_ITEM_QUANTITY BILL_QUANTITY_PER_ASSEMBLY,
759
760 A.COMPONENT_YIELD_FACTOR,
761 A.BASIS_TYPE, --LBM enh
762 A.EFFECTIVITY_DATE,
763 A.DISABLE_DATE,
764 s.attribute1 COMPONENT_PRIORITY,
765 A.PARENT_BILL_SEQ_ID,
766
767 A.ITEM_NUM,
768 A.COMPONENT_REMARKS,
769 S.CHANGE_NOTICE,
770 A.IMPLEMENTATION_DATE,
771 A.PLANNING_FACTOR,
772 A.QUANTITY_RELATED,
773 A.SO_BASIS,
774 A.OPTIONAL,
775 A.MUTUALLY_EXCLUSIVE_OPTIONS,
776 A.INCLUDE_IN_COST_ROLLUP,
777 A.CHECK_ATP,
778 A.SHIPPING_ALLOWED,
779 A.REQUIRED_TO_SHIP,
780 A.REQUIRED_FOR_REVENUE,
781 A.INCLUDE_ON_SHIP_DOCS,
782 A.LOW_QUANTITY,
783 A.HIGH_QUANTITY,
784 S.ACD_TYPE,
785 A.OLD_COMPONENT_SEQUENCE_ID,
786 A.OPERATION_LEAD_TIME_PERCENT,
787 A.REVISED_ITEM_SEQUENCE_ID,
788 A.BOM_ITEM_TYPE,
789 A.FROM_END_ITEM_UNIT_NUMBER,
790 A.TO_END_ITEM_UNIT_NUMBER,
791 A.ECO_FOR_PRODUCTION,
792 S.ENFORCE_INT_REQUIREMENTS,
793 A.DELETE_GROUP_NAME,
794 A.DG_DESCRIPTION,
795 A.OPTIONAL_ON_MODEL,
796 A.MODEL_COMP_SEQ_ID,
797 A.PLAN_LEVEL,
798 A.AUTO_REQUEST_MATERIAL,
799 A.COMPONENT_ITEM_REVISION_ID,
800 A.FROM_BILL_REVISION_ID,
801 A.TO_BILL_REVISION_ID,
802 A.PICK_COMPONENTS,
803 A.INCLUDE_ON_BILL_DOCS,
804 A.COST_FACTOR,
805 A.ORIGINAL_SYSTEM_REFERENCE,
806 s.attribute_category,
807 s.attribute1,
808 s.attribute2,
809 s.attribute3,
810 s.attribute4,
811 s.attribute5,
812 s.attribute6,
813 s.attribute7,
814 s.attribute8,
815 s.attribute9,
816 s.attribute10,
817 s.attribute11,
818 s.attribute12,
819 s.attribute13,
820 s.attribute14,
821 s.attribute15
822 FROM WSM_COPY_OPERATIONS O,
823 BOM_INVENTORY_COMPONENTS A,
824 BOM_SUBSTITUTE_COMPONENTS S,
825 MTL_SYSTEM_ITEMS C,
826 BOM_EXPLOSION_TEMP BE
827 WHERE BE.GROUP_ID=l_explosion_group_id
828 AND BE.TOP_BILL_SEQUENCE_ID = l_top_level_bill_seq_id
829 AND A.COMPONENT_SEQUENCE_ID=BE.COMPONENT_SEQUENCE_ID
830 AND S.COMPONENT_SEQUENCE_ID = A.COMPONENT_SEQUENCE_ID
831 AND S.SUBSTITUTE_COMPONENT_ID = C.INVENTORY_ITEM_ID
832 AND C.ORGANIZATION_ID = p_org_id
833 AND O.wip_entity_id = p_wip_entity_id
834 AND O.operation_sequence_id = l_curr_op_seq_id
835 AND ((A.operation_seq_num = (SELECT operation_seq_num
836 FROM WSM_COPY_OPERATIONS
837 WHERE operation_sequence_id =
838 l_curr_op_seq_id
839 AND wip_entity_id = p_wip_entity_id
840 )
841 )
842 OR
843 (l_curr_first_op_attach_opseq1 = 1 AND A.OPERATION_SEQ_NUM = 1)
844 )
845 AND p_bom_rev_date BETWEEN A.EFFECTIVITY_DATE and nvl(A.DISABLE_DATE, p_bom_rev_date+1)
846 AND A.EFFECTIVITY_DATE = (SELECT MAX(EFFECTIVITY_DATE)
847 FROM BOM_INVENTORY_COMPONENTS BIC,
848 ENG_REVISED_ITEMS ERI
849 WHERE BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
850 AND BIC.COMPONENT_ITEM_ID = A.COMPONENT_ITEM_ID
851 AND (decode(BIC.IMPLEMENTATION_DATE,
852 NULL,
853 BIC.OLD_COMPONENT_SEQUENCE_ID,
854 BIC.COMPONENT_SEQUENCE_ID
855 ) =
856 decode(A.IMPLEMENTATION_DATE,
857 NULL,
858 A.OLD_COMPONENT_SEQUENCE_ID,
859 A.COMPONENT_SEQUENCE_ID
860 )
861 OR
862 BIC.OPERATION_SEQ_NUM = A.OPERATION_SEQ_NUM
863 )
864 AND BIC.EFFECTIVITY_DATE <= p_bom_rev_date
865 AND BIC.REVISED_ITEM_SEQUENCE_ID =
866 ERI.REVISED_ITEM_SEQUENCE_ID(+)
867 AND (nvl(ERI.STATUS_TYPE,6) IN (4,6,7))
868 AND NOT EXISTS
869 (SELECT 'X'
870 FROM BOM_INVENTORY_COMPONENTS BICN,
871 ENG_REVISED_ITEMS ERI1
872 WHERE BICN.BILL_SEQUENCE_ID =
873 A.BILL_SEQUENCE_ID
874 AND BICN.OLD_COMPONENT_SEQUENCE_ID =
875 A.COMPONENT_SEQUENCE_ID
876 AND BICN.ACD_TYPE in (2,3)
877 AND BICN.DISABLE_DATE <=
878 p_bom_rev_date
879 AND ERI1.REVISED_ITEM_SEQUENCE_ID =
880 BICN.REVISED_ITEM_SEQUENCE_ID
881 AND (nvl(ERI1.STATUS_TYPE,6)IN(4,6,7))
882 )
883 )
884 ); -- Fix for bug #3313480 -- End of union of 2 sqls
885 -- ST : Performance fix commenting out order by
886 -- ORDER BY COMPONENT_ITEM_ID,WIP_SUPPLY_TYPE,EFFECTIVITY_DATE;
887
888 TYPE table_comp_details is TABLE OF reqs%ROWTYPE INDEX by BINARY_INTEGER;
889 t_comp_details table_comp_details;
890 TYPE table_opseq is table of number INDEX by BINARY_INTEGER;
891 t_eff_opseq_id table_opseq;
892 t_eff_opseq_num table_opseq;
893 t_eff_opseqid_pos table_opseq;
894 -- start bug 4448718
895 next_op_prim_path table_opseq;
896 disabled_prim_path BOOLEAN;
897 v_job_nw_delete table_opseq;
898 -- End bug 4448718
899 l_end_op_seq_id number;
900 l_start_op_seq_id number;
901 l_start_op_seq_num NUMBER;
902 l_opseq1_exists number;
903 l_curr_opseq_id NUMBER;
904 l_curr_reco_path_seq_num NUMBER;
905 l_index NUMBER;
906 i NUMBER;
907 j NUMBER;
908 l_go_to_next_level BOOLEAN;
909 l_from_op_seq_id NUMBER;
910 l_to_op_seq_id NUMBER;
911 l_from_op_seq_num NUMBER;
912 l_to_op_seq_num NUMBER;
913 dummy1 NUMBER;
914 dummy2 VARCHAR2(1000);
915 l_op_seq_incr NUMBER;
916
917 -- ST : MES changes --
918 l_curr_job_op_seq_num NUMBER;
919 l_curr_rtg_op_seq_num NUMBER;
920 -- ST : MES changes --
921
922 -- ST : Fix for bug 5171286 --
923 l_first_serial_txn_id NUMBER;
924
925 BEGIN
926
927 g_debug := FND_PROFILE.VALUE('MRP_DEBUG');
928
929 IF (g_debug = 'Y') THEN
930 fnd_file.put_line(fnd_file.log, 'Parameters to Create_JobCopies are :');
931 fnd_file.put_line(fnd_file.log, ' p_wip_entity_id ='||p_wip_entity_id );
932 fnd_file.put_line(fnd_file.log, ', p_org_id ='||p_org_id );
933 fnd_file.put_line(fnd_file.log, ', p_primary_item_id ='||p_primary_item_id );
934 fnd_file.put_line(fnd_file.log, ', p_routing_item_id ='||p_routing_item_id );
935 fnd_file.put_line(fnd_file.log, ', p_alt_rtg_desig ='||p_alt_rtg_desig );
936 fnd_file.put_line(fnd_file.log, ', p_rtg_seq_id ='||p_rtg_seq_id );
937 fnd_file.put_line(fnd_file.log, ', p_common_rtg_seq_id ='||p_common_rtg_seq_id );
938 fnd_file.put_line(fnd_file.log, ', p_rtg_rev_date ='||p_rtg_rev_date );
939 fnd_file.put_line(fnd_file.log, ', p_bill_item_id ='||p_bill_item_id );
940 fnd_file.put_line(fnd_file.log, ', p_alt_bom_desig ='||p_alt_bom_desig );
941 fnd_file.put_line(fnd_file.log, ', p_bill_seq_id ='||p_bill_seq_id );
942 fnd_file.put_line(fnd_file.log, ', p_common_bill_seq_id ='||p_common_bill_seq_id );
943 fnd_file.put_line(fnd_file.log, ', p_bom_rev_date ='||p_bom_rev_date );
944 fnd_file.put_line(fnd_file.log, ', p_wip_supply_type ='||p_wip_supply_type );
945 fnd_file.put_line(fnd_file.log, ', p_last_update_date ='||p_last_update_date );
946 fnd_file.put_line(fnd_file.log, ', p_last_updated_by ='||p_last_updated_by );
947 fnd_file.put_line(fnd_file.log, ', p_last_update_login ='||p_last_update_login );
948 fnd_file.put_line(fnd_file.log, ', p_creation_date ='||p_creation_date );
949 fnd_file.put_line(fnd_file.log, ', p_created_by ='||p_created_by );
950 fnd_file.put_line(fnd_file.log, ', p_request_id ='||p_request_id );
951 fnd_file.put_line(fnd_file.log, ', p_program_app_id ='||p_program_app_id );
952 fnd_file.put_line(fnd_file.log, ', p_program_id ='||p_program_id );
953 fnd_file.put_line(fnd_file.log, ', p_program_update_date ='||p_program_update_date );
954 fnd_file.put_line(fnd_file.log, ', p_inf_sch_flag ='||p_inf_sch_flag );
955 fnd_file.put_line(fnd_file.log, ', p_inf_sch_mode ='||p_inf_sch_mode );
956 fnd_file.put_line(fnd_file.log, ', p_inf_sch_date ='||p_inf_sch_date );
957 END IF;
958
959 l_stmt_num := 10;
960
961 -- Start : Basic validations for the input parameters --
962 IF (nvl(p_primary_item_id, 0) = 0) OR
963 (nvl(p_primary_item_id, -1) = -1)
964 THEN
965 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
966 FND_MESSAGE.SET_TOKEN('FLD_NAME', 'Primary Item ID in Job');
967 x_err_buf := fnd_message.get;
968 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||'): '||x_err_buf);
969 x_err_code := -2; -- Error
970 return;
971 END IF;
972
973 IF (nvl(p_routing_item_id, 0) = 0) OR
974 (nvl(p_routing_item_id, -1) = -1)
975 THEN
976 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
977 FND_MESSAGE.SET_TOKEN('FLD_NAME', 'Routing Item ID in Job');
978 x_err_buf := fnd_message.get;
979 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||'): '||x_err_buf);
980 x_err_code := -2; -- Error
981 return;
982 END IF;
983
984 IF (nvl(p_common_rtg_seq_id, 0) = 0) OR
985 (nvl(p_common_rtg_seq_id, -1) = -1)
986 THEN
987 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
988 FND_MESSAGE.SET_TOKEN('FLD_NAME', 'Common Routing Sequence ID in Job');
989 x_err_buf := fnd_message.get;
990 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||'): '||x_err_buf);
991 x_err_code := -2; -- Error
992 return;
993 END IF;
994
995 IF (p_rtg_rev_date IS NULL)
996 THEN
997 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
998 FND_MESSAGE.SET_TOKEN('FLD_NAME', 'Routing Revision Date in Job');
999 x_err_buf := fnd_message.get;
1000 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||'): '||x_err_buf);
1001 x_err_code := -2; -- Error
1002 return;
1003 END IF;
1004
1005 IF (nvl(p_wip_supply_type, 0) = 0) OR
1006 (nvl(p_wip_supply_type, -1) = -1)
1007 THEN
1008 fnd_message.set_name('WSM', 'WSM_INVALID_FIELD');
1009 FND_MESSAGE.SET_TOKEN('FLD_NAME', 'Supply Type in Job');
1010 x_err_buf := fnd_message.get;
1011 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||'): '||x_err_buf);
1012 x_err_code := -2; -- Error
1013 return;
1014 END IF;
1015 -- End : Basic validations for the input parameters --
1016
1017 IF ( (p_inf_sch_flag = 'Y')
1018 AND NOT (p_inf_sch_mode IN (WIP_CONSTANTS.FORWARDS,
1019 WIP_CONSTANTS.MIDPOINT_FORWARDS,
1020 WIP_CONSTANTS.CURRENT_OP,
1021 WIP_CONSTANTS.BACKWARDS,
1022 WIP_CONSTANTS.MIDPOINT_BACKWARDS)
1023 OR p_inf_sch_mode IS NULL
1024 )
1025 )
1026 THEN
1027 -- Invalid Infinite Scheduling Mode
1028 fnd_message.set_name('WSM', 'WSM_INVALID_INFSCH_MODE');
1029 x_err_buf := fnd_message.get;
1030 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||'): '||x_err_buf);
1031 x_err_code := -2; -- Error
1032 return;
1033 END IF;
1034
1035 -- Local variable
1036 l_inf_sch_flag := p_inf_sch_flag;
1037
1038 if p_insert_wip is NULL or p_insert_wip <> 1 then
1039 -- Start : Additions to fix bug #3677276
1040 BEGIN
1041
1042 l_stmt_num := 13;
1043
1044 UPDATE wip_operations wo
1045 SET wo.wsm_op_seq_num =
1046 (SELECT distinct(bos.operation_seq_num)
1047 FROM wip_operations wo1,
1048 bom_operation_sequences bos
1049 WHERE wo1.wip_entity_id = p_wip_entity_id
1050 AND wo1.wsm_op_seq_num IS NULL
1051 AND wo1.operation_sequence_id = bos.operation_sequence_id
1052 AND bos.routing_sequence_id = p_common_rtg_seq_id
1053 AND wo1.operation_seq_num = wo.operation_seq_num)
1054 WHERE wip_entity_id = p_wip_entity_id
1055 AND wsm_op_seq_num IS NULL;
1056 EXCEPTION
1057 WHEN OTHERS THEN
1058 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||
1059 '): Cannot upgrade wip_operations.wsm_op_seq_num for wip_entity_id '||p_wip_entity_id);
1060 END;
1061 end if;--check on p_insert_wip
1062 l_stmt_num := 15;
1063
1064 -- End : Additions to fix bug #3677276
1065
1066 -- Delete COPY tables --
1067 --Delete only when l_new_job is false....
1068 if p_new_job is NULL or p_new_job = 2 then
1069
1070 DELETE WSM_COPY_OPERATIONS
1071 WHERE wip_entity_id = p_wip_entity_id;
1072
1073 DELETE WSM_COPY_OP_NETWORKS
1074 WHERE wip_entity_id = p_wip_entity_id;
1075
1076 DELETE WSM_COPY_OP_RESOURCES
1077 WHERE wip_entity_id = p_wip_entity_id;
1078
1079 DELETE WSM_COPY_OP_RESOURCE_INSTANCES
1080 WHERE wip_entity_id = p_wip_entity_id;
1081
1082 DELETE WSM_COPY_OP_RESOURCE_USAGE
1083 WHERE wip_entity_id = p_wip_entity_id;
1084
1085 DELETE WSM_COPY_REQUIREMENT_OPS
1086 WHERE wip_entity_id = p_wip_entity_id;
1087
1088 end if; --End of check on p_new_job before deleting the copy tables
1089
1090 DELETE WSM_LOT_BASED_JOBS
1091 WHERE wip_entity_id = p_wip_entity_id
1092 RETURNING current_job_op_seq_num,current_rtg_op_seq_num,first_serial_txn_id
1093 INTO l_curr_job_op_seq_num,l_curr_rtg_op_seq_num,l_first_serial_txn_id;
1094 -- ST : Added first_serial_txn_id in the above statement for bug fix 5171286
1095
1096 -- *** Start : Set the setup variables *** --
1097 l_stmt_num := 20;
1098
1099 SELECT default_pull_supply_subinv,
1100 default_pull_supply_locator_id
1101 INTO l_wip_param_def_subinv,
1102 l_wip_param_def_locator_id
1103 FROM wip_parameters
1104 WHERE organization_id = p_org_id;
1105
1106 --OPTII-PERF:Check on p_phantom_exists is added.
1107 IF (p_common_bill_seq_id IS NOT NULL) and
1108 (p_phantom_exists = 1 OR p_phantom_exists IS NULL) THEN
1109 l_stmt_num := 30;
1110
1111 BEGIN
1112 SELECT nvl(use_phantom_routings, 0),
1113 nvl(maximum_bom_level, 60),
1114 BOM_EXPLOSION_TEMP_S.nextval -- Added here for performance improvement
1115 INTO l_use_phantom_routings,
1116 l_max_bill_levels,
1117 l_explosion_group_id
1118 FROM BOM_PARAMETERS
1119 WHERE ORGANIZATION_ID = p_org_id;
1120
1121 EXCEPTION
1122 WHEN no_data_found THEN
1123 null;
1124 END;
1125
1126 END IF;
1127 -- *** End : Set the setup variables *** --
1128 l_stmt_num := 50;
1129
1130 FOR l_opseq_rec in c_eff_opseq_id LOOP
1131 if l_opseq_rec.new_op_seq_id is not null then
1132 t_eff_opseq_id(l_opseq_rec.old_op_seq_id) := l_opseq_rec.new_op_seq_id;
1133 end if;
1134
1135 END LOOP;
1136
1137 l_counter :=0;
1138 l_stmt_num := 60;
1139 l_opseq1_exists := NULL;
1140 FOR l_job_op IN c_job_ops LOOP
1141
1142 l_counter := l_counter + 1;
1143
1144 v_job_ops(l_counter).WIP_ENTITY_ID := p_wip_entity_id;
1145 v_job_ops(l_counter).OPERATION_SEQ_NUM := l_job_op.operation_seq_num;
1146 v_job_ops(l_counter).RECOMMENDED := l_job_op.RECOMMENDED;
1147 v_job_ops(l_counter).RECO_PATH_SEQ_NUM := l_job_op.RECO_PATH_SEQ_NUM;
1148 v_job_ops(l_counter).RECO_SCHEDULED_QUANTITY := null;
1149 v_job_ops(l_counter).RECO_START_DATE := null;
1150 v_job_ops(l_counter).RECO_COMPLETION_DATE := null;
1151 v_job_ops(l_counter).NETWORK_START_END := l_job_op.NETWORK_START_END;
1152 v_job_ops(l_counter).OPERATION_SEQUENCE_ID := l_job_op.OPERATION_SEQUENCE_ID;
1153 v_job_ops(l_counter).ROUTING_SEQUENCE_ID := l_job_op.ROUTING_SEQUENCE_ID;
1154 v_job_ops(l_counter).ORGANIZATION_ID := p_org_id;
1155 v_job_ops(l_counter).STANDARD_OPERATION_ID := l_job_op.STANDARD_OPERATION_ID;
1156 v_job_ops(l_counter).STANDARD_OPERATION_CODE := l_job_op.STANDARD_OPERATION_CODE;
1157 v_job_ops(l_counter).DEPARTMENT_ID := l_job_op.DEPARTMENT_ID;
1158 v_job_ops(l_counter).DEPARTMENT_CODE := l_job_op.DEPARTMENT_CODE;
1159 v_job_ops(l_counter).SCRAP_ACCOUNT := l_job_op.SCRAP_ACCOUNT;
1160 v_job_ops(l_counter).EST_ABSORPTION_ACCOUNT := l_job_op.EST_ABSORPTION_ACCOUNT;
1161 v_job_ops(l_counter).OPERATION_LEAD_TIME_PERCENT := l_job_op.OPERATION_LEAD_TIME_PERCENT;
1162 v_job_ops(l_counter).MINIMUM_TRANSFER_QUANTITY := l_job_op.MINIMUM_TRANSFER_QUANTITY;
1163 v_job_ops(l_counter).COUNT_POINT_TYPE := l_job_op.COUNT_POINT_TYPE;
1164 v_job_ops(l_counter).OPERATION_DESCRIPTION := l_job_op.OPERATION_DESCRIPTION;
1165 v_job_ops(l_counter).EFFECTIVITY_DATE := l_job_op.EFFECTIVITY_DATE;
1166 v_job_ops(l_counter).DISABLE_DATE := l_job_op.DISABLE_DATE;
1167 v_job_ops(l_counter).BACKFLUSH_FLAG := l_job_op.BACKFLUSH_FLAG;
1168 v_job_ops(l_counter).OPTION_DEPENDENT_FLAG := l_job_op.OPTION_DEPENDENT_FLAG;
1169 v_job_ops(l_counter).OPERATION_TYPE := l_job_op.OPERATION_TYPE;
1170 v_job_ops(l_counter).REFERENCE_FLAG := l_job_op.REFERENCE_FLAG;
1171 v_job_ops(l_counter).YIELD := l_job_op.YIELD;
1172 v_job_ops(l_counter).CUMULATIVE_YIELD := l_job_op.CUMULATIVE_YIELD;
1173 v_job_ops(l_counter).REVERSE_CUMULATIVE_YIELD := l_job_op.REVERSE_CUMULATIVE_YIELD;
1174 v_job_ops(l_counter).LABOR_TIME_CALC := l_job_op.LABOR_TIME_CALC;
1175 v_job_ops(l_counter).MACHINE_TIME_CALC := l_job_op.MACHINE_TIME_CALC;
1176 v_job_ops(l_counter).TOTAL_TIME_CALC := l_job_op.TOTAL_TIME_CALC;
1177 v_job_ops(l_counter).LABOR_TIME_USER := l_job_op.LABOR_TIME_USER;
1178 v_job_ops(l_counter).MACHINE_TIME_USER := l_job_op.MACHINE_TIME_USER;
1179 v_job_ops(l_counter).TOTAL_TIME_USER := l_job_op.TOTAL_TIME_USER;
1180 v_job_ops(l_counter).NET_PLANNING_PERCENT := l_job_op.NET_PLANNING_PERCENT;
1181 v_job_ops(l_counter).X_COORDINATE := l_job_op.X_COORDINATE;
1182 v_job_ops(l_counter).Y_COORDINATE := l_job_op.Y_COORDINATE;
1183 v_job_ops(l_counter).INCLUDE_IN_ROLLUP := l_job_op.INCLUDE_IN_ROLLUP;
1184 v_job_ops(l_counter).OPERATION_YIELD_ENABLED := l_job_op.OPERATION_YIELD_ENABLED;
1185 v_job_ops(l_counter).OLD_OPERATION_SEQUENCE_ID := l_job_op.OLD_OPERATION_SEQUENCE_ID;
1186 v_job_ops(l_counter).ACD_TYPE := l_job_op.ACD_TYPE;
1187 v_job_ops(l_counter).REVISED_ITEM_SEQUENCE_ID := l_job_op.REVISED_ITEM_SEQUENCE_ID;
1188 v_job_ops(l_counter).CHANGE_NOTICE := l_job_op.CHANGE_NOTICE;
1189 v_job_ops(l_counter).IMPLEMENTATION_DATE := l_job_op.IMPLEMENTATION_DATE;
1190 v_job_ops(l_counter).ECO_FOR_PRODUCTION := l_job_op.ECO_FOR_PRODUCTION;
1191 v_job_ops(l_counter).SHUTDOWN_TYPE := l_job_op.SHUTDOWN_TYPE;
1192 v_job_ops(l_counter).ACTUAL_IPK := l_job_op.ACTUAL_IPK;
1193 v_job_ops(l_counter).CRITICAL_TO_QUALITY := l_job_op.CRITICAL_TO_QUALITY;
1194 v_job_ops(l_counter).VALUE_ADDED := l_job_op.VALUE_ADDED;
1195 -- v_job_ops(l_counter).LONG_DESCRIPTION := l_job_op.LONG_DESCRIPTION;
1196 v_job_ops(l_counter).LAST_UPDATE_DATE := p_last_update_date;
1197 v_job_ops(l_counter).LAST_UPDATED_BY := p_last_updated_by;
1198 v_job_ops(l_counter).LAST_UPDATE_LOGIN := p_last_update_login;
1199 v_job_ops(l_counter).CREATION_DATE := p_creation_date;
1200 v_job_ops(l_counter).CREATED_BY := p_created_by;
1201 v_job_ops(l_counter).REQUEST_ID := p_request_id;
1202 v_job_ops(l_counter).PROGRAM_APPLICATION_ID := p_program_app_id;
1203 v_job_ops(l_counter).PROGRAM_ID := p_program_id;
1204 v_job_ops(l_counter).PROGRAM_UPDATE_DATE := p_program_update_date;
1205 v_job_ops(l_counter).ATTRIBUTE_CATEGORY := l_job_op.ATTRIBUTE_CATEGORY;
1206 v_job_ops(l_counter).ATTRIBUTE1 := l_job_op.ATTRIBUTE1;
1207 v_job_ops(l_counter).ATTRIBUTE2 := l_job_op.ATTRIBUTE2;
1208 v_job_ops(l_counter).ATTRIBUTE3 := l_job_op.ATTRIBUTE3;
1209 v_job_ops(l_counter).ATTRIBUTE4 := l_job_op.ATTRIBUTE4;
1210 v_job_ops(l_counter).ATTRIBUTE5 := l_job_op.ATTRIBUTE5;
1211 v_job_ops(l_counter).ATTRIBUTE6 := l_job_op.ATTRIBUTE6;
1212 v_job_ops(l_counter).ATTRIBUTE7 := l_job_op.ATTRIBUTE7;
1213 v_job_ops(l_counter).ATTRIBUTE8 := l_job_op.ATTRIBUTE8;
1214 v_job_ops(l_counter).ATTRIBUTE9 := l_job_op.ATTRIBUTE9;
1215 v_job_ops(l_counter).ATTRIBUTE10 := l_job_op.ATTRIBUTE10;
1216 v_job_ops(l_counter).ATTRIBUTE11 := l_job_op.ATTRIBUTE11;
1217 v_job_ops(l_counter).ATTRIBUTE12 := l_job_op.ATTRIBUTE12;
1218 v_job_ops(l_counter).ATTRIBUTE13 := l_job_op.ATTRIBUTE13;
1219 v_job_ops(l_counter).ATTRIBUTE14 := l_job_op.ATTRIBUTE14;
1220 v_job_ops(l_counter).ATTRIBUTE15 := l_job_op.ATTRIBUTE15;
1221 v_job_ops(l_counter).ORIGINAL_SYSTEM_REFERENCE := l_job_op.ORIGINAL_SYSTEM_REFERENCE;
1222 v_job_ops(l_counter).LOWEST_ACCEPTABLE_YIELD := l_job_op.LOWEST_ACCEPTABLE_YIELD; --mes
1223 t_eff_opseq_num(l_job_op.operation_sequence_id) := l_job_op.operation_seq_num;
1224 t_eff_opseqid_pos(l_job_op.operation_sequence_id) := l_counter;
1225
1226 If l_job_op.operation_seq_num = 1 then
1227 l_opseq1_exists := 1;
1228 End if;
1229
1230 END LOOP;
1231
1232 IF v_job_ops.count = 0 THEN
1233 -- No valid operations exist for the job
1234 fnd_message.set_name('WSM', 'WSM_NO_VALID_OPS');
1235 x_err_buf := fnd_message.get;
1236 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.process_job_op_nw('||l_stmt_num||'): '||x_err_buf);
1237 x_err_code := -2; -- Error
1238
1239 return;
1240 END IF;
1241
1242 l_counter :=0;
1243 l_stmt_num := 20;
1244
1245 FOR l_job_nw in c_job_nw LOOP
1246
1247 If T_eff_opseq_id.exists(l_job_nw.from_op_seq_id) THEN
1248 l_from_op_seq_id:= t_eff_opseq_id(l_job_nw.from_op_seq_id);
1249 else
1250 l_from_op_seq_id:= l_job_nw.from_op_seq_id;
1251 end if;
1252
1253 If T_eff_opseq_id.exists(l_job_nw.to_op_seq_id) THEN
1254 l_to_op_seq_id:= t_eff_opseq_id(l_job_nw.to_op_seq_id);
1255 else
1256 l_to_op_seq_id:= l_job_nw.to_op_seq_id;
1257 end if;
1258
1259 -- start bug 4448718
1260 IF l_job_nw.TRANSITION_TYPE = 1 THEN
1261 next_op_prim_path(l_from_op_seq_id) := l_to_op_seq_id;
1262 END IF;
1263 -- end bug 4448718
1264
1265 --Following check is required because cursor c_eff_opseq_id
1266 --cannot indentify the operations from bon which do not have
1267 --effective operation in bos.
1268
1269 -- start bug 4448718
1270 if (t_eff_opseq_num.exists(l_from_op_seq_id) and
1271 t_eff_opseq_num.exists(l_to_op_seq_id)) OR
1272 (t_eff_opseq_num.exists(l_from_op_seq_id) AND
1273 l_job_nw.TRANSITION_TYPE = 1)
1274 then
1275
1276 l_from_op_seq_num := t_eff_opseq_num(l_from_op_seq_id);
1277
1278 IF t_eff_opseq_num.exists(l_to_op_seq_id) THEN
1279 l_to_op_seq_num := t_eff_opseq_num(l_to_op_seq_id);
1280 ELSE
1281 l_to_op_seq_num := NULL;
1282 l_to_op_seq_id := NULL;
1283 disabled_prim_path := TRUE;
1284 END IF;
1285
1286 -- End bug 4448718
1287
1288 l_counter := l_counter + 1;
1289
1290 v_job_nw(l_counter).WIP_ENTITY_ID := p_wip_entity_id;
1291 v_job_nw(l_counter).FROM_OP_SEQ_ID := l_from_op_seq_id;
1292 v_job_nw(l_counter).TO_OP_SEQ_ID := l_to_op_seq_id;
1293 v_job_nw(l_counter).FROM_OP_SEQ_NUM := l_from_op_seq_num;
1294 v_job_nw(l_counter).TO_OP_SEQ_NUM := l_to_op_seq_num;
1295 v_job_nw(l_counter).RECOMMENDED := l_job_nw.RECOMMENDED;
1296 v_job_nw(l_counter).ROUTING_SEQUENCE_ID := p_common_rtg_seq_id;
1297 v_job_nw(l_counter).TRANSITION_TYPE := l_job_nw.TRANSITION_TYPE;
1298 v_job_nw(l_counter).PLANNING_PCT := l_job_nw.PLANNING_PCT;
1299 v_job_nw(l_counter).LAST_UPDATE_DATE := p_last_update_date;
1300 v_job_nw(l_counter).LAST_UPDATED_BY := p_last_updated_by;
1301 v_job_nw(l_counter).LAST_UPDATE_LOGIN := p_last_update_login;
1302 v_job_nw(l_counter).CREATION_DATE := p_creation_date;
1303 v_job_nw(l_counter).CREATED_BY := p_created_by;
1304 v_job_nw(l_counter).REQUEST_ID := p_request_id;
1305 v_job_nw(l_counter).PROGRAM_APPLICATION_ID := p_program_app_id;
1306 v_job_nw(l_counter).PROGRAM_ID := p_program_id;
1307 v_job_nw(l_counter).PROGRAM_UPDATE_DATE := p_program_update_date;
1308 v_job_nw(l_counter).ATTRIBUTE_CATEGORY := l_job_nw.ATTRIBUTE_CATEGORY;
1309 v_job_nw(l_counter).ATTRIBUTE1 := l_job_nw.ATTRIBUTE1;
1310 v_job_nw(l_counter).ATTRIBUTE2 := l_job_nw.ATTRIBUTE2;
1311 v_job_nw(l_counter).ATTRIBUTE3 := l_job_nw.ATTRIBUTE3;
1312 v_job_nw(l_counter).ATTRIBUTE4 := l_job_nw.ATTRIBUTE4;
1313 v_job_nw(l_counter).ATTRIBUTE5 := l_job_nw.ATTRIBUTE5;
1314 v_job_nw(l_counter).ATTRIBUTE6 := l_job_nw.ATTRIBUTE6;
1315 v_job_nw(l_counter).ATTRIBUTE7 := l_job_nw.ATTRIBUTE7;
1316 v_job_nw(l_counter).ATTRIBUTE8 := l_job_nw.ATTRIBUTE8;
1317 v_job_nw(l_counter).ATTRIBUTE9 := l_job_nw.ATTRIBUTE9;
1318 v_job_nw(l_counter).ATTRIBUTE10 := l_job_nw.ATTRIBUTE10;
1319 v_job_nw(l_counter).ATTRIBUTE11 := l_job_nw.ATTRIBUTE11;
1320 v_job_nw(l_counter).ATTRIBUTE12 := l_job_nw.ATTRIBUTE12;
1321 v_job_nw(l_counter).ATTRIBUTE13 := l_job_nw.ATTRIBUTE13;
1322 v_job_nw(l_counter).ATTRIBUTE14 := l_job_nw.ATTRIBUTE14;
1323 v_job_nw(l_counter).ATTRIBUTE15 := l_job_nw.ATTRIBUTE15;
1324 v_job_nw(l_counter).ORIGINAL_SYSTEM_REFERENCE := l_job_nw.ORIGINAL_SYSTEM_REFERENCE;
1325 end if;
1326 END LOOP;
1327
1328 -- start bug 4448718
1329 l_stmt_num := 20.1;
1330
1331 IF disabled_prim_path THEN
1332 FOR i in 1..v_job_nw.count LOOP
1333 IF v_job_nw(i).TO_OP_SEQ_ID IS NULL THEN
1334 l_from_op_seq_id := v_job_nw(i).FROM_OP_SEQ_ID;
1335 WHILE (next_op_prim_path.exists(l_from_op_seq_id)) LOOP
1336 l_to_op_seq_id := next_op_prim_path(l_from_op_seq_id);
1337 IF t_eff_opseq_num.exists(l_to_op_seq_id) THEN
1338 IF T_eff_opseq_id.exists(l_to_op_seq_id) THEN
1339 v_job_nw(i).TO_OP_SEQ_ID := T_eff_opseq_id(l_to_op_seq_id);
1340 v_job_nw(i).TO_OP_SEQ_NUM := t_eff_opseq_num(v_job_nw(i).TO_OP_SEQ_ID);
1341 --Bug 5371323:Start of changes
1342 ELSE
1343 v_job_nw(i).TO_OP_SEQ_ID := l_to_op_seq_id;
1344 v_job_nw(i).TO_OP_SEQ_NUM := t_eff_opseq_num(l_to_op_seq_id);
1345 --Bug 5371323:End of changes
1346 END IF;
1347 exit;
1348 ELSE
1349 l_from_op_seq_id := next_op_prim_path(l_from_op_seq_id);
1350 END IF;
1351 END LOOP;
1352 END IF;
1353 END LOOP;
1354 END IF;
1355
1356 next_op_prim_path.delete;
1357
1358 -- End bug 4448718
1359 IF (v_job_nw.COUNT = 0) THEN
1360 -- Eg - Earlier routing was 10 -> 20.
1361 -- Now a new op is added and network changed to 10->30->20
1362 -- Refresh is run, But as of the job's rtg rev date, only ops 10 and 20 are effective
1363 -- In this case, the job has valid ops 10, 20; but no valid network.
1364
1365 -- No valid network links exist for the job
1366 fnd_message.set_name('WSM', 'WSM_NO_VALID_NWK_LINKS');
1367 x_err_buf := fnd_message.get;
1368 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.process_job_op_nw('||l_stmt_num||'): '||x_err_buf);
1369 x_err_code := -2; -- Error
1370
1371 return; -- This should not be commented out, since later it may get overwritten.
1372 -- Fixed bug #3465125
1373 END IF;
1374
1375 l_end_op_seq_id := 0;
1376 l_start_op_seq_id := 0;
1377 l_start_op_seq_num := 0;
1378
1379
1380 --Get the last operation
1381
1382 FOR i in 1..v_job_nw.count LOOP
1383 l_counter := 0;
1384 --Change for bug 4448718;Added check on transition type.
1385 -- as if there are operations on alt path without without
1386 --next operation, such op should not be taken as
1387 --last operation.
1388 IF v_job_nw(i).transition_type = 1 THEN
1389 For j in 1..v_job_nw.count LOOP
1390 If v_job_nw(i).to_op_seq_id = v_job_nw(j).from_op_seq_id then
1391 -- To op seq id of i is not last operation.
1392 l_counter := -1;
1393 exit;
1394 End if;
1395 END LOOP;
1396 ELSE
1397 l_counter := -1;
1398 END IF;
1399 --If it comes to this, it means that no from op exists as to op of i. Hence to op of I is last operation.
1400 if l_counter <> -1 then
1401 l_end_op_seq_id:= v_job_nw(i).to_op_seq_id;
1402 exit;
1403 end if;
1404
1405 END LOOP;
1406 --Get first operation
1407
1408 For i in 1..v_job_nw.count LOOP
1409 l_counter := 0;
1410 IF v_job_nw(i).transition_type = 1 THEN
1411 For j in 1..v_job_nw.count LOOP
1412 If v_job_nw(i).from_op_seq_id = v_job_nw(j).to_op_seq_id then
1413
1414 -- From op seq id of i is not start operation.
1415 l_counter := -1;
1416 Exit;
1417 End if;
1418 END LOOP;
1419 ELSE
1420 l_counter := -1;
1421 END IF;
1422 --If it comes to this, it means that no to op exists as from op of i. Hence from op of I is start operation.
1423 if l_counter <> -1 then
1424 l_start_op_seq_id:= v_job_nw(i).from_op_seq_id;
1425 exit;
1426 end if;
1427
1428 END LOOP;
1429 --Determine the reco path sequence number
1430 l_stmt_num := 30;
1431
1432
1433 If t_eff_opseq_num.exists(l_start_op_seq_id) then
1434 v_job_ops(t_eff_opseqid_pos(l_start_op_seq_id)).network_start_end := 'S';
1435 l_start_op_seq_num := t_eff_opseq_num(l_start_op_seq_id);
1436 else
1437 fnd_message.set_name('WSM', 'WSM_NET_START_NOT_EFFECTIVE');
1438 --x_err_buf := fnd_message.get;
1439 x_err_buf := 'l_count is'||to_char(l_start_op_seq_id);
1440 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||'): '||x_err_buf);
1441 x_err_code := -1; -- Warning
1442 --Bug 4264364:Added exception handling
1443 INSERT into WSM_LOT_BASED_JOBS
1444 (WIP_ENTITY_ID,
1445 ORGANIZATION_ID,
1446 ON_REC_PATH,
1447 INTERNAL_COPY_TYPE,
1448 COPY_PARENT_WIP_ENTITY_ID,
1449 INFINITE_SCHEDULE,
1450 ROUTING_REFRESH_DATE,
1451 LAST_UPDATE_DATE,
1452 LAST_UPDATED_BY,
1453 LAST_UPDATE_LOGIN,
1454 CREATION_DATE,
1455 CREATED_BY,
1456 REQUEST_ID,
1457 PROGRAM_APPLICATION_ID,
1458 PROGRAM_ID,
1459 PROGRAM_UPDATE_DATE
1460 )
1461 VALUES
1462 (p_wip_entity_id,
1463 p_org_id,
1464 'N',
1465 3,
1466 NULL,
1467 NULL,
1468 SYSDATE,
1469 p_last_update_date,
1470 p_last_updated_by,
1471 p_last_update_login,
1472 p_creation_date,
1473 p_created_by,
1474 p_request_id,
1475 p_program_app_id,
1476 p_program_id,
1477 p_program_update_date);
1478
1479 raise e_noneff_op;
1480 End if;
1481
1482 l_stmt_num := 40;
1483 If t_eff_opseq_num.exists(l_end_op_seq_id) then
1484 v_job_ops(t_eff_opseqid_pos(l_end_op_seq_id)).network_start_end := 'E';
1485 l_reco_path_seq_num := 1;
1486 v_job_ops(t_eff_opseqid_pos(l_end_op_seq_id)).reco_path_seq_num := l_reco_path_seq_num;
1487 v_job_ops(t_eff_opseqid_pos(l_end_op_seq_id)).recommended := 'Y';
1488 else
1489 l_reco_path_seq_num := -1;
1490 fnd_message.set_name('WSM', 'WSM_NET_END_NOT_EFFECTIVE');
1491 x_err_buf := fnd_message.get;
1492 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.process_job_op_nw('||l_stmt_num||'): '||x_err_buf);
1493 x_err_code := -1; -- Warning
1494
1495 --Bug 4264364:Added exception handling
1496 INSERT into WSM_LOT_BASED_JOBS
1497 (WIP_ENTITY_ID,
1498 ORGANIZATION_ID,
1499 ON_REC_PATH,
1500 INTERNAL_COPY_TYPE,
1501 COPY_PARENT_WIP_ENTITY_ID,
1502 INFINITE_SCHEDULE,
1503 ROUTING_REFRESH_DATE,
1504 LAST_UPDATE_DATE,
1505 LAST_UPDATED_BY,
1506 LAST_UPDATE_LOGIN,
1507 CREATION_DATE,
1508 CREATED_BY,
1509 REQUEST_ID,
1510 PROGRAM_APPLICATION_ID,
1511 PROGRAM_ID,
1512 PROGRAM_UPDATE_DATE
1513 )
1514 VALUES
1515 (p_wip_entity_id,
1516 p_org_id,
1517 'N',
1518 3,
1519 NULL,
1520 NULL,
1521 SYSDATE,
1522 p_last_update_date,
1523 p_last_updated_by,
1524 p_last_update_login,
1525 p_creation_date,
1526 p_created_by,
1527 p_request_id,
1528 p_program_app_id,
1529 p_program_id,
1530 p_program_update_date);
1531
1532 raise e_noneff_op;
1533 End if;
1534
1535 if l_reco_path_seq_num = 1 then
1536 l_curr_opseq_id := l_end_op_seq_id;
1537 l_go_to_next_level := true;
1538 While l_go_to_next_level LOOP
1539 l_go_to_next_level := false;
1540 For i in 1..v_job_nw.count LOOP
1541 If v_job_nw(i).to_op_seq_id = l_curr_opseq_id
1542 and v_job_nw(i).transition_type = 1 then
1543 --The below check will always be true as non effective ops
1544 --are already filtered in v_job_nw
1545 --and t_eff_opseq_num.exists(v_job_nw(i).from_op_seq_id) then
1546 v_job_ops(t_eff_opseqid_pos(v_job_nw(i).from_op_seq_id)).recommended := 'Y';
1547 l_reco_path_seq_num:=l_reco_path_seq_num+1;
1548 v_job_ops(t_eff_opseqid_pos(v_job_nw(i).from_op_seq_id)).reco_path_seq_num := l_reco_path_seq_num;
1549 l_curr_opseq_id := v_job_nw(i).from_op_seq_id;
1550 l_go_to_next_level := true;
1551 Exit;
1552 End if;
1553 End loop; -- for loop end
1554 END LOOP; --While loop end
1555
1556 --Update reco_path_seq_num for all ops starting from
1557 --l_reco_path_seq_num to net work end operation.
1558 For i in 1..v_job_ops.count LOOP
1559 if v_job_ops(i).reco_path_seq_num > 0 then
1560 v_job_ops(i).reco_path_seq_num:= l_reco_path_seq_num+1-v_job_ops(i).reco_path_seq_num;
1561 end if;
1562 END LOOP;
1563 end if; --end of check on l_reco_path_seq_num
1564 l_stmt_num := 40.1;
1565 If l_curr_opseq_id <> l_start_op_seq_id or l_reco_path_seq_num = -1 then
1566 fnd_message.set_name('WSM', 'WSM_DISABLED_PRIMARY_PATH');
1567 x_err_buf := fnd_message.get;
1568 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.process_job_op_nw('||l_stmt_num||'): '||x_err_buf);
1569 x_err_code := -1; -- Warning
1570 l_inf_sch_flag := 'N'; -- Do not infinite schedule this job
1571 END IF;
1572
1573 l_stmt_num := 40.2;
1574 IF l_reco_path_seq_num <> -1 THEN
1575 --
1576 for i in 1..v_job_nw.count LOOP
1577 if v_job_ops(t_eff_opseqid_pos(v_job_nw(i).from_op_seq_id)).recommended = 'Y'
1578 and v_job_ops(t_eff_opseqid_pos(v_job_nw(i).to_op_seq_id)).recommended = 'Y'
1579 and v_job_nw(i).transition_type = 1 then --Code review remark
1580 v_job_nw(i).recommended := 'Y';
1581 end if;
1582 end loop;
1583 END IF;
1584
1585 -- Insert the records into WCO ---
1586 forall l_index in v_job_ops.first..v_job_ops.last
1587 INSERT INTO WSM_COPY_OPERATIONS
1588 values v_job_ops(l_index);
1589
1590 --Start changes for 4448718
1591 -- insert records..
1592 --Remove any duplicate rows
1593 IF disabled_prim_path THEN
1594 disabled_prim_path := FALSE;
1595 l_stmt_num := 40.2;
1596 l_counter := 0;
1597 FOR i in v_job_nw.first..v_job_nw.last LOOP
1598 IF v_job_nw(i).transition_type = 1 THEN
1599 l_stmt_num := 40.3;
1600 FOR j in v_job_nw.first..v_job_nw.last LOOP
1601 l_stmt_num := 40.4;
1602 IF v_job_nw(j).transition_type <> 1 and
1603 v_job_nw(i).TO_OP_SEQ_NUM = v_job_nw(j).TO_OP_SEQ_NUM AND
1604 v_job_nw(i).FROM_OP_SEQ_NUM = v_job_nw(j).FROM_OP_SEQ_NUM THEN
1605 l_stmt_num := 40.5;
1606 l_counter := l_counter+1;
1607 v_job_nw_delete(l_counter) := j;
1608 END IF;
1609 End LOOP;
1610 END IF;
1611 END LOOP;
1612 IF l_counter > 0 THEN
1613 FOR i in v_job_nw_delete.first..v_job_nw_delete.last LOOP
1614 v_job_nw.delete(v_job_nw_delete(i));
1615 END LOOP;
1616 END IF;
1617
1618 v_job_nw_delete.delete;
1619 END IF;
1620 --End changes for 4448718
1621
1622 l_stmt_num := 40.6;
1623 -- Changes for 4448718: Used INDICES of as there can be
1624 -- null values in the table because of the deletion
1625 -- in the previous statement.
1626 --forall l_index in v_job_nw.first..v_job_nw.last
1627 forall l_index in INDICES OF v_job_nw
1628 INSERT into WSM_COPY_OP_NETWORKS
1629 values v_job_nw(l_index);
1630
1631 --This is not needed when called from wlt code
1632 l_counter := t_eff_opseqid_pos(l_start_op_seq_id);
1633 -- free up the used memory !!!
1634 v_job_ops.delete;
1635 v_job_nw.delete;
1636
1637
1638 -- ***** Make a copy of the primary Resources for the job ***** --
1639 INSERT INTO WSM_COPY_OP_RESOURCES
1640 (WIP_ENTITY_ID,
1641 OPERATION_SEQ_NUM,
1642 RESOURCE_SEQ_NUM,
1643 ORGANIZATION_ID,
1644 SUBSTITUTE_GROUP_NUM,
1645 REPLACEMENT_GROUP_NUM,
1646 RECOMMENDED,
1647 RECO_START_DATE,
1648 RECO_COMPLETION_DATE,
1649 RESOURCE_ID,
1650 RESOURCE_CODE,
1651 DEPARTMENT_ID,
1652 PHANTOM_FLAG,
1653 PHANTOM_OP_SEQ_NUM,
1654 PHANTOM_ITEM_ID,
1655 ACTIVITY_ID,
1656 STANDARD_RATE_FLAG,
1657 ASSIGNED_UNITS,
1658 -- ST : Detailed Scheduling
1659 MAX_ASSIGNED_UNITS,
1660 FIRM_TYPE,
1661 -- ST : Detailed Scheduling
1662 USAGE_RATE_OR_AMOUNT,
1663 USAGE_RATE_OR_AMOUNT_INVERSE,
1664 UOM_CODE,
1665 BASIS_TYPE,
1666 SCHEDULE_FLAG,
1667 RESOURCE_OFFSET_PERCENT,
1668 AUTOCHARGE_TYPE,
1669 SCHEDULE_SEQ_NUM,
1670 PRINCIPLE_FLAG,
1671 SETUP_ID,
1672 CHANGE_NOTICE,
1673 ACD_TYPE,
1674 LAST_UPDATE_DATE,
1675 LAST_UPDATED_BY,
1676 LAST_UPDATE_LOGIN,
1677 CREATION_DATE,
1678 CREATED_BY,
1679 REQUEST_ID,
1680 PROGRAM_APPLICATION_ID,
1681 PROGRAM_ID,
1682 PROGRAM_UPDATE_DATE,
1683 ATTRIBUTE_CATEGORY,
1684 ATTRIBUTE1,
1685 ATTRIBUTE2,
1686 ATTRIBUTE3,
1687 ATTRIBUTE4,
1688 ATTRIBUTE5,
1689 ATTRIBUTE6,
1690 ATTRIBUTE7,
1691 ATTRIBUTE8,
1692 ATTRIBUTE9,
1693 ATTRIBUTE10,
1694 ATTRIBUTE11,
1695 ATTRIBUTE12,
1696 ATTRIBUTE13,
1697 ATTRIBUTE14,
1698 ATTRIBUTE15,
1699 ORIGINAL_SYSTEM_REFERENCE
1700 )
1701 SELECT p_WIP_ENTITY_ID,
1702 --bos.OPERATION_SEQ_NUM,
1703 wco.OPERATION_SEQ_NUM,
1704 bor.RESOURCE_SEQ_NUM,
1705 p_ORG_ID,
1706 bor.SUBSTITUTE_GROUP_NUM,
1707 0, --REPLACEMENT_GROUP_NUM
1708 'Y', --RECOMMENDED
1709 NULL, --RECO_START_DATE
1710 NULL, --RECO_COMPLETION_DATE
1711 bor.RESOURCE_ID,
1712 br.RESOURCE_CODE,
1713 NULL, --DEPARTMENT_ID - this is NULL for non-phantom component resources
1714 NULL, --PHANTOM_FLAG
1715 NULL, --PHANTOM_OP_SEQ_NUM
1716 NULL, --PHANTOM_ITEM_ID
1717 bor.ACTIVITY_ID,
1718 bor.STANDARD_RATE_FLAG,
1719 bor.ASSIGNED_UNITS,
1720 -- ST : Detailed Scheduling
1721 bor.ASSIGNED_UNITS,
1722 0, -- None - NOT FIRMED
1723 -- ST : Detailed Scheduling
1724 bor.USAGE_RATE_OR_AMOUNT,
1725 bor.USAGE_RATE_OR_AMOUNT_INVERSE,
1726 br.UNIT_OF_MEASURE,
1727 bor.BASIS_TYPE,
1728 bor.SCHEDULE_FLAG,
1729 bor.RESOURCE_OFFSET_PERCENT,
1730 bor.AUTOCHARGE_TYPE,
1731 bor.SCHEDULE_SEQ_NUM,
1732 bor.PRINCIPLE_FLAG,
1733 bor.SETUP_ID,
1734 bor.CHANGE_NOTICE,
1735 bor.ACD_TYPE,
1736 p_last_update_date,
1737 p_last_updated_by,
1738 p_last_update_login,
1739 p_creation_date,
1740 p_created_by,
1741 p_request_id,
1742 p_program_app_id,
1743 p_program_id,
1744 p_program_update_date,
1745 bor.ATTRIBUTE_CATEGORY,
1746 bor.ATTRIBUTE1,
1747 bor.ATTRIBUTE2,
1748 bor.ATTRIBUTE3,
1749 bor.ATTRIBUTE4,
1750 bor.ATTRIBUTE5,
1751 bor.ATTRIBUTE6,
1752 bor.ATTRIBUTE7,
1753 bor.ATTRIBUTE8,
1754 bor.ATTRIBUTE9,
1755 bor.ATTRIBUTE10,
1756 bor.ATTRIBUTE11,
1757 bor.ATTRIBUTE12,
1758 bor.ATTRIBUTE13,
1759 bor.ATTRIBUTE14,
1760 bor.ATTRIBUTE15,
1761 bor.ORIGINAL_SYSTEM_REFERENCE
1762 FROM BOM_RESOURCES br,
1763 BOM_OPERATION_RESOURCES bor,
1764 wsm_copy_operations wco
1765 WHERE wco.wip_entity_id = p_wip_entity_id
1766 AND WCO.OPERATION_SEQUENCE_ID = bor.OPERATION_SEQUENCE_ID
1767 AND bor.RESOURCE_ID = br.RESOURCE_ID
1768 AND br.ORGANIZATION_ID = p_org_id;
1769
1770 IF SQL%ROWCOUNT > 0 THEN --Added for 4635447
1771 IF (g_debug = 'Y') THEN
1772 fnd_file.put_line(fnd_file.log, 'Created '||SQL%ROWCOUNT||' records in WCOR (pri res) for we_id='||p_wip_entity_id);
1773 END IF;
1774
1775 --IF SQL%ROWCOUNT > 0 THEN --Bug 4635477
1776 --Look for substitute resources only when at least one primary resource
1777 --exists.
1778
1779
1780 select OPERATION_SEQ_NUM,resource_seq_num
1781 bulk collect into v_op_seq_id,v_res_seq
1782 from WSM_COPY_OP_RESOURCES wcor
1783 where wcor.wip_entity_id = p_wip_entity_id;
1784
1785 FOR i in 1..v_op_seq_id.count LOOP
1786
1787 IF v_max_res_seq.exists(v_op_seq_id(i)) AND
1788 v_max_res_seq(v_op_seq_id(i)) < v_res_seq(i) THEN
1789 v_max_res_seq(v_op_seq_id(i)) := v_res_seq(i);
1790 ELSE
1791 v_max_res_seq(v_op_seq_id(i)) := v_res_seq(i);
1792 END IF;
1793
1794 end loop;
1795
1796
1797 l_stmt_num := 140;
1798
1799 -- ***** Make a copy of the substitute Resources for the job ***** --
1800 INSERT INTO WSM_COPY_OP_RESOURCES
1801 (WIP_ENTITY_ID,
1802 OPERATION_SEQ_NUM,
1803 RESOURCE_SEQ_NUM,
1804 ORGANIZATION_ID,
1805 SUBSTITUTE_GROUP_NUM,
1806 REPLACEMENT_GROUP_NUM,
1807 RECOMMENDED,
1808 RECO_START_DATE,
1809 RECO_COMPLETION_DATE,
1810 RESOURCE_ID,
1811 RESOURCE_CODE,
1812 ACTIVITY_ID,
1813 STANDARD_RATE_FLAG,
1814 ASSIGNED_UNITS,
1815 -- ST : Detailed Scheduling
1816 MAX_ASSIGNED_UNITS,
1817 FIRM_TYPE,
1818 -- ST : Detailed Scheduling
1819 USAGE_RATE_OR_AMOUNT,
1820 USAGE_RATE_OR_AMOUNT_INVERSE,
1821 UOM_CODE,
1822 BASIS_TYPE,
1823 SCHEDULE_FLAG,
1824 RESOURCE_OFFSET_PERCENT,
1825 AUTOCHARGE_TYPE,
1826 SCHEDULE_SEQ_NUM,
1827 PRINCIPLE_FLAG,
1828 SETUP_ID,
1829 CHANGE_NOTICE,
1830 ACD_TYPE,
1831 PHANTOM_FLAG,
1832 PHANTOM_OP_SEQ_NUM,
1833 PHANTOM_ITEM_ID,
1834 LAST_UPDATE_DATE,
1835 LAST_UPDATED_BY,
1836 LAST_UPDATE_LOGIN,
1837 CREATION_DATE,
1838 CREATED_BY,
1839 REQUEST_ID,
1840 PROGRAM_APPLICATION_ID,
1841 PROGRAM_ID,
1842 PROGRAM_UPDATE_DATE,
1843 ATTRIBUTE_CATEGORY,
1844 ATTRIBUTE1,
1845 ATTRIBUTE2,
1846 ATTRIBUTE3,
1847 ATTRIBUTE4,
1848 ATTRIBUTE5,
1849 ATTRIBUTE6,
1850 ATTRIBUTE7,
1851 ATTRIBUTE8,
1852 ATTRIBUTE9,
1853 ATTRIBUTE10,
1854 ATTRIBUTE11,
1855 ATTRIBUTE12,
1856 ATTRIBUTE13,
1857 ATTRIBUTE14,
1858 ATTRIBUTE15,
1859 ORIGINAL_SYSTEM_REFERENCE
1860 )
1861 SELECT p_WIP_ENTITY_ID,
1862 WCO.OPERATION_SEQ_NUM,
1863 rownum + max_res_seq(WCO.operation_seq_num),
1864 p_org_id,
1865 bsor.SUBSTITUTE_GROUP_NUM,
1866 bsor.REPLACEMENT_GROUP_NUM,
1867 'N', --RECOMMENDED
1868 NULL, --RECO_START_DATE
1869 NULL, --RECO_COMPLETION_DATE
1870 bsor.RESOURCE_ID,
1871 br.RESOURCE_CODE,
1872 bsor.ACTIVITY_ID,
1873 bsor.STANDARD_RATE_FLAG,
1874 bsor.ASSIGNED_UNITS,
1875 bsor.ASSIGNED_UNITS,
1876 0, -- None - NOT FIRMED
1877 bsor.USAGE_RATE_OR_AMOUNT,
1878 bsor.USAGE_RATE_OR_AMOUNT_INVERSE,
1879 br.UNIT_OF_MEASURE,
1880 bsor.BASIS_TYPE,
1881 bsor.SCHEDULE_FLAG,
1882 bsor.RESOURCE_OFFSET_PERCENT,
1883 bsor.AUTOCHARGE_TYPE,
1884 bsor.SCHEDULE_SEQ_NUM,
1885 bsor.PRINCIPLE_FLAG,
1886 bsor.SETUP_ID,
1887 bsor.CHANGE_NOTICE,
1888 bsor.ACD_TYPE,
1889 NULL, --PHANTOM_FLAG
1890 NULL, --PHANTOM_OP_SEQ_NUM
1891 NULL, --PHANTOM_ITEM_ID
1892 p_last_update_date,
1893 p_last_updated_by,
1894 p_last_update_login,
1895 p_creation_date,
1896 p_created_by,
1897 p_request_id,
1898 p_program_app_id,
1899 p_program_id,
1900 p_program_update_date,
1901 bsor.ATTRIBUTE_CATEGORY,
1902 bsor.ATTRIBUTE1,
1903 bsor.ATTRIBUTE2,
1904 bsor.ATTRIBUTE3,
1905 bsor.ATTRIBUTE4,
1906 bsor.ATTRIBUTE5,
1907 bsor.ATTRIBUTE6,
1908 bsor.ATTRIBUTE7,
1909 bsor.ATTRIBUTE8,
1910 bsor.ATTRIBUTE9,
1911 bsor.ATTRIBUTE10,
1912 bsor.ATTRIBUTE11,
1913 bsor.ATTRIBUTE12,
1914 - bsor.ATTRIBUTE13,
1915 bsor.ATTRIBUTE14,
1916 bsor.ATTRIBUTE15,
1917 bsor.ORIGINAL_SYSTEM_REFERENCE
1918 FROM BOM_RESOURCES br,
1919 BOM_SUB_OPERATION_RESOURCES bsor,
1920 WSM_COPY_OPERATIONS WCO
1921 WHERE WCO.wip_entity_id = p_wip_entity_id
1922 AND bsor.RESOURCE_ID = br.RESOURCE_ID
1923 AND WCO.OPERATION_SEQUENCE_ID = bsor.OPERATION_SEQUENCE_ID;
1924
1925 IF (g_debug = 'Y') THEN
1926 fnd_file.put_line(fnd_file.log, 'Created '||SQL%ROWCOUNT||' records in WCOR (subs res) for we_id='||p_wip_entity_id);
1927 END IF;
1928 END IF;--Check on SQL%ROWCOUNT
1929
1930 if p_phantom_exists = 2 and p_common_bill_seq_id IS NOT NULL THEN --Code review remark
1931 l_phantom_org_id := p_org_id; --Added for bug 4515000
1932 --Populate primary and substitute components. As no phantom exists
1933 --no need for calling bom exploder code.
1934 INSERT INTO WSM_COPY_REQUIREMENT_OPS
1935 (WIP_ENTITY_ID,
1936 OPERATION_SEQ_NUM,
1937 COMPONENT_ITEM_ID,
1938 PRIMARY_COMPONENT_ID,
1939 COMPONENT_SEQUENCE_ID,
1940 SOURCE_PHANTOM_ID,
1941 RECOMMENDED,
1942 RECO_DATE_REQUIRED,
1943 BILL_SEQUENCE_ID,
1944 DEPARTMENT_ID,
1945 ORGANIZATION_ID,
1946 WIP_SUPPLY_TYPE,
1947 SUPPLY_SUBINVENTORY,
1948 SUPPLY_LOCATOR_ID,
1949 QUANTITY_PER_ASSEMBLY,
1950 BILL_QUANTITY_PER_ASSEMBLY,
1951 COMPONENT_YIELD_FACTOR,
1952 BASIS_TYPE, --LBM enh
1953 EFFECTIVITY_DATE,
1954 DISABLE_DATE,
1955 COMPONENT_PRIORITY,
1956 PARENT_BILL_SEQ_ID,
1957 ITEM_NUM,
1958 COMPONENT_REMARKS,
1959 CHANGE_NOTICE,
1960 IMPLEMENTATION_DATE,
1961 PLANNING_FACTOR,
1962 QUANTITY_RELATED,
1963 SO_BASIS,
1964 OPTIONAL,
1965 MUTUALLY_EXCLUSIVE_OPTIONS,
1966 INCLUDE_IN_COST_ROLLUP,
1967 CHECK_ATP,
1968 SHIPPING_ALLOWED,
1969 REQUIRED_TO_SHIP,
1970 REQUIRED_FOR_REVENUE,
1971 INCLUDE_ON_SHIP_DOCS,
1972 LOW_QUANTITY,
1973 HIGH_QUANTITY,
1974 ACD_TYPE,
1975 OLD_COMPONENT_SEQUENCE_ID,
1976 OPERATION_LEAD_TIME_PERCENT,
1977 REVISED_ITEM_SEQUENCE_ID,
1978 BOM_ITEM_TYPE,
1979 FROM_END_ITEM_UNIT_NUMBER,
1980 TO_END_ITEM_UNIT_NUMBER,
1981 ECO_FOR_PRODUCTION,
1982 ENFORCE_INT_REQUIREMENTS,
1983 DELETE_GROUP_NAME,
1984 DG_DESCRIPTION,
1985 OPTIONAL_ON_MODEL,
1986 MODEL_COMP_SEQ_ID,
1987 PLAN_LEVEL,
1988 AUTO_REQUEST_MATERIAL,
1989 COMPONENT_ITEM_REVISION_ID,
1990 FROM_BILL_REVISION_ID,
1991 TO_BILL_REVISION_ID,
1992 PICK_COMPONENTS,
1993 INCLUDE_ON_BILL_DOCS,
1994 COST_FACTOR,
1995 LAST_UPDATE_DATE,
1996 LAST_UPDATED_BY,
1997 LAST_UPDATE_LOGIN,
1998 CREATION_DATE,
1999 CREATED_BY,
2000 REQUEST_ID,
2001 PROGRAM_APPLICATION_ID,
2002 PROGRAM_ID,
2003 PROGRAM_UPDATE_DATE,
2004 ATTRIBUTE_CATEGORY,
2005 ATTRIBUTE1,
2006 ATTRIBUTE2,
2007 ATTRIBUTE3,
2008 ATTRIBUTE4,
2009 ATTRIBUTE5,
2010 ATTRIBUTE6,
2011 ATTRIBUTE7,
2012 ATTRIBUTE8,
2013 ATTRIBUTE9,
2014 ATTRIBUTE10,
2015 ATTRIBUTE11,
2016 ATTRIBUTE12,
2017 ATTRIBUTE13,
2018 ATTRIBUTE14,
2019 ATTRIBUTE15,
2020 ORIGINAL_SYSTEM_REFERENCE
2021 )
2022 select p_wip_entity_id,
2023 decode(A.operation_seq_num,1,decode(l_opseq1_exists,1,A.operation_seq_num,l_start_op_seq_num),A.operation_seq_num),
2024 a.COMPONENT_ITEM_ID,
2025 a.COMPONENT_ITEM_ID,
2026 a.COMPONENT_SEQUENCE_ID,
2027 -1,
2028 'Y', --Recommended
2029 null, --RECO_DATE_REQUIRED,
2030 a.BILL_SEQUENCE_ID,
2031 bos.DEPARTMENT_ID,
2032 c.ORGANIZATION_ID,
2033 decode(p_wip_supply_type,
2034 7,
2035 nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),
2036 p_wip_supply_type),
2037 --Bug 5216333: Suppy type specified at job should be looked at first
2038 decode (p_org_id,
2039 l_phantom_org_id,
2040 nvl(a.supply_subinventory,
2041 nvl(c.wip_supply_subinventory,
2042 decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),p_wip_supply_type),
2043 2,
2044 l_wip_param_def_subinv,
2045 3,
2046 l_wip_param_def_subinv,
2047 null
2048 )
2049 )
2050 ),
2051 nvl(c.wip_supply_subinventory,
2052 decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),p_wip_supply_type),
2053 2,
2054 l_wip_param_def_subinv,
2055 3,
2056 l_wip_param_def_subinv,
2057 null
2058 )
2059 )
2060 ),--a.SUPPLY_SUBINVENTORY,
2061 --Bug 5216333: Suppy type specified at job should be looked at first
2062 decode (p_org_id, -- supply locator id begin
2063 l_phantom_org_id,
2064 decode (a.supply_subinventory,
2065 null,
2066 decode (c.wip_supply_subinventory,
2067 null,
2068 decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type,
2069 nvl(c.wip_supply_type,1)),p_wip_supply_type),
2070 2,
2071 --nvl(l_wip_param_def_locator_id,-1),
2072 l_wip_param_def_locator_id,
2073 3,
2074 --nvl(l_wip_param_def_locator_id,-1),
2075 l_wip_param_def_locator_id,
2076 null
2077 ),
2078 c.wip_supply_locator_id
2079 ),
2080 a.supply_locator_id
2081 ),
2082 --The org id will always be equal to phantom org id.So the
2083 --decode below is redundant.
2084 decode (c.wip_supply_subinventory,
2085 null,
2086 decode(decode(p_wip_supply_type,7,nvl(a.wip_supply_type, nvl(c.wip_supply_type, 1)),p_wip_supply_type),
2087 2,
2088 nvl(l_wip_param_def_locator_id,-1),
2089 3,
2090 nvl(l_wip_param_def_locator_id,-1),
2091 null
2092 ),
2093 c.wip_supply_locator_id
2094 )
2095 ), --c.wip_supply_locator_id,
2096 a.component_quantity / decode(a.component_yield_factor,
2097 0,
2098 1,
2099 a.component_yield_factor
2100 ), --qty per assembly
2101 a.component_quantity ,
2102 a.component_yield_factor,
2103 a.basis_type, --LBM enh
2104 a.effectivity_date ,
2105 a.disable_date,
2106 null, --COMPONENT_PRIORITY, -- modified for bug 7143452
2107 a.parent_bill_seq_id,
2108 a.item_num,
2109 a.component_remarks,
2110 a.change_notice,
2111 a.implementation_date,
2112 a.planning_factor,
2113 a.quantity_related,
2114 a.so_basis,
2115 a.optional,
2116 a.mutually_exclusive_options,
2117 a.include_in_cost_rollup,
2118 a.check_atp,
2119 a.shipping_allowed,
2120 a.required_to_ship,
2121 a.required_for_revenue,
2122 a.include_on_ship_docs,
2123 a.low_quantity,
2124 a.high_quantity,
2125 a.acd_type,
2126 a.old_component_sequence_id,
2127 a.operation_lead_time_percent,
2128 a.revised_item_sequence_id,
2129 a.bom_item_type,
2130 a.from_end_item_unit_number,
2131 a.to_end_item_unit_number,
2132 a.eco_for_production,
2133 a.enforce_int_requirements,
2134 a.delete_group_name,
2135 a.dg_description,
2136 a.optional_on_model,
2137 a.model_comp_seq_id,
2138 a.plan_level,
2139 a.auto_request_material,
2140 a.component_item_revision_id,
2141 a.from_bill_revision_id,
2142 a.to_bill_revision_id,
2143 a.pick_components,
2144 a.include_on_bill_docs,
2145 a.cost_factor,
2146 p_last_update_date,
2147 p_last_updated_by,
2148 p_last_update_login,
2149 p_creation_date,
2150 p_created_by,
2151 p_request_id,
2152 p_program_app_id,
2153 p_program_id,
2154 p_program_update_date,
2155 a.attribute_category,
2156 a.attribute1,
2157 a.attribute2,
2158 a.attribute3,
2159 a.attribute4,
2160 a.attribute5,
2161 a.attribute6,
2162 a.attribute7,
2163 a.attribute8,
2164 a.attribute9,
2165 a.attribute10,
2166 a.attribute11,
2167 a.attribute12,
2168 a.attribute13,
2169 a.attribute14,
2170 a.attribute15,
2171 a.original_system_reference
2172 FROM BOM_INVENTORY_COMPONENTS A,
2173 MTL_SYSTEM_ITEMS C,
2174 BOM_OPERATION_SEQUENCES BOS
2175 WHERE a.bill_sequence_id = p_common_bill_seq_id
2176 AND a.component_item_id = c.inventory_item_id
2177 AND c.organization_id = p_org_id
2178 AND BOS.routing_sequence_id = p_common_rtg_seq_id
2179 AND BOS.operation_seq_num = decode(A.operation_seq_num,1,decode(l_opseq1_exists,1,A.operation_seq_num,l_start_op_seq_num),A.operation_seq_num)
2180 AND p_rtg_rev_date between BOS.effectivity_date and nvl(BOS.disable_date, p_rtg_rev_date+1)
2181 AND p_bom_rev_date between a.effectivity_date and nvl(a.disable_date, p_bom_rev_date+1);
2182
2183 IF SQL%ROWCOUNT > 0 THEN
2184 IF (g_debug = 'Y') THEN
2185 fnd_file.put_line(fnd_file.log, 'Created '||SQL%ROWCOUNT||' records in WCR) (Primary Components) for we_id='||p_wip_entity_id);
2186 END IF;
2187 --IF SQL%ROWCOUNT > 0 THEN
2188 --Insert substitute components only when there are primary components.
2189 INSERT INTO WSM_COPY_REQUIREMENT_OPS
2190 (WIP_ENTITY_ID,
2191 OPERATION_SEQ_NUM,
2192 COMPONENT_ITEM_ID,
2193 PRIMARY_COMPONENT_ID,
2194 COMPONENT_SEQUENCE_ID,
2195 SOURCE_PHANTOM_ID,
2196 RECOMMENDED,
2197 RECO_DATE_REQUIRED,
2198 BILL_SEQUENCE_ID,
2199 DEPARTMENT_ID,
2200 ORGANIZATION_ID,
2201 WIP_SUPPLY_TYPE,
2202 SUPPLY_SUBINVENTORY,
2203 SUPPLY_LOCATOR_ID,
2204 QUANTITY_PER_ASSEMBLY,
2205 BILL_QUANTITY_PER_ASSEMBLY,
2206 COMPONENT_YIELD_FACTOR,
2207 BASIS_TYPE, --LBM enh
2208 EFFECTIVITY_DATE,
2209 DISABLE_DATE,
2210 COMPONENT_PRIORITY,
2211 PARENT_BILL_SEQ_ID,
2212 ITEM_NUM,
2213 COMPONENT_REMARKS,
2214 CHANGE_NOTICE,
2215 IMPLEMENTATION_DATE,
2216 PLANNING_FACTOR,
2217 QUANTITY_RELATED,
2218 SO_BASIS,
2219 OPTIONAL,
2220 MUTUALLY_EXCLUSIVE_OPTIONS,
2221 INCLUDE_IN_COST_ROLLUP,
2222 CHECK_ATP,
2223 SHIPPING_ALLOWED,
2224 REQUIRED_TO_SHIP,
2225 REQUIRED_FOR_REVENUE,
2226 INCLUDE_ON_SHIP_DOCS,
2227 LOW_QUANTITY,
2228 HIGH_QUANTITY,
2229 ACD_TYPE,
2230 OLD_COMPONENT_SEQUENCE_ID,
2231 OPERATION_LEAD_TIME_PERCENT,
2232 REVISED_ITEM_SEQUENCE_ID,
2233 BOM_ITEM_TYPE,
2234 FROM_END_ITEM_UNIT_NUMBER,
2235 TO_END_ITEM_UNIT_NUMBER,
2236 ECO_FOR_PRODUCTION,
2237 ENFORCE_INT_REQUIREMENTS,
2238 DELETE_GROUP_NAME,
2239 DG_DESCRIPTION,
2240 OPTIONAL_ON_MODEL,
2241 MODEL_COMP_SEQ_ID,
2242 PLAN_LEVEL,
2243 AUTO_REQUEST_MATERIAL,
2244 COMPONENT_ITEM_REVISION_ID,
2245 FROM_BILL_REVISION_ID,
2246 TO_BILL_REVISION_ID,
2247 PICK_COMPONENTS,
2248 INCLUDE_ON_BILL_DOCS,
2249 COST_FACTOR,
2250 LAST_UPDATE_DATE,
2251 LAST_UPDATED_BY,
2252 LAST_UPDATE_LOGIN,
2253 CREATION_DATE,
2254 CREATED_BY,
2255 REQUEST_ID,
2256 PROGRAM_APPLICATION_ID,
2257 PROGRAM_ID,
2258 PROGRAM_UPDATE_DATE,
2259 ATTRIBUTE_CATEGORY,
2260 ATTRIBUTE1,
2261 ATTRIBUTE2,
2262 ATTRIBUTE3,
2263 ATTRIBUTE4,
2264 ATTRIBUTE5,
2265 ATTRIBUTE6,
2266 ATTRIBUTE7,
2267 ATTRIBUTE8,
2268 ATTRIBUTE9,
2269 ATTRIBUTE10,
2270 ATTRIBUTE11,
2271 ATTRIBUTE12,
2272 ATTRIBUTE13,
2273 ATTRIBUTE14,
2274 ATTRIBUTE15,
2275 ORIGINAL_SYSTEM_REFERENCE
2276 )
2277 select
2278 p_wip_entity_id,
2279 wcro.OPERATION_SEQ_NUM,
2280 a.SUBSTITUTE_COMPONENT_ID,
2281 wcro.COMPONENT_ITEM_ID,
2282 a.COMPONENT_SEQUENCE_ID,
2283 -1,
2284 'N', --Recommended
2285 null, --RECO_DATE_REQUIRED,
2286 wcro.BILL_SEQUENCE_ID,
2287 wcro.DEPARTMENT_ID,
2288 c.ORGANIZATION_ID,
2289 wcro.WIP_SUPPLY_TYPE,
2290 wcro.SUPPLY_SUBINVENTORY,
2291 wcro.supply_locator_id,
2292 a.substitute_item_quantity / decode(wcro.component_yield_factor,
2293 0,
2294 1,
2295 wcro.component_yield_factor
2296 ), --qty per assembly
2297 a.SUBSTITUTE_ITEM_QUANTITY,
2298 wcro.component_yield_factor,
2299 wcro.basis_type, --LBM enh
2300 wcro.effectivity_date ,
2301 wcro.disable_date,
2302 a.attribute1, --COMPONENT_PRIORITY,
2303 wcro.parent_bill_seq_id,
2304 wcro.item_num,
2305 wcro.component_remarks,
2306 a.change_notice,
2307 wcro.implementation_date,
2308 wcro.planning_factor,
2309 wcro.quantity_related,
2310 wcro.so_basis,
2311 wcro.optional,
2312 wcro.mutually_exclusive_options,
2313 wcro.include_in_cost_rollup,
2314 wcro.check_atp,
2315 wcro.shipping_allowed,
2316 wcro.required_to_ship,
2317 wcro.required_for_revenue,
2318 wcro.include_on_ship_docs,
2319 wcro.low_quantity,
2320 wcro.high_quantity,
2321 a.acd_type,
2322 wcro.old_component_sequence_id,
2323 wcro.operation_lead_time_percent,
2324 wcro.revised_item_sequence_id,
2325 wcro.bom_item_type,
2326 wcro.from_end_item_unit_number,
2327 wcro.to_end_item_unit_number,
2328 wcro.eco_for_production,
2329 a.enforce_int_requirements,
2330 wcro.delete_group_name,
2331 wcro.dg_description,
2332 wcro.optional_on_model,
2333 wcro.model_comp_seq_id,
2334 wcro.plan_level,
2335 wcro.auto_request_material,
2336 wcro.component_item_revision_id,
2337 wcro.from_bill_revision_id,
2338 wcro.to_bill_revision_id,
2339 wcro.pick_components,
2340 wcro.include_on_bill_docs,
2341 wcro.cost_factor,
2342 p_last_update_date,
2343 p_last_updated_by,
2344 p_last_update_login,
2345 p_creation_date,
2346 p_created_by,
2347 p_request_id,
2348 p_program_app_id,
2349 p_program_id,
2350 p_program_update_date,
2351 a.attribute_category,
2352 a.attribute1,
2353 a.attribute2,
2354 a.attribute3,
2355 a.attribute4,
2356 a.attribute5,
2357 a.attribute6,
2358 a.attribute7,
2359 a.attribute8,
2360 a.attribute9,
2361 a.attribute10,
2362 a.attribute11,
2363 a.attribute12,
2364 a.attribute13,
2365 a.attribute14,
2366 a.attribute15,
2367 wcro.original_system_reference
2368 FROM BOM_SUBSTITUTE_COMPONENTS A,
2369 MTL_SYSTEM_ITEMS C,
2370 WSM_COPY_REQUIREMENT_OPS wcro
2371 WHERE wcro.wip_entity_id = p_wip_entity_id
2372 AND a.component_sequence_id = wcro.component_sequence_id
2373 AND a.SUBSTITUTE_COMPONENT_ID = c.inventory_item_id
2374 AND c.organization_id = p_org_id;
2375 END IF; --End of check on wcro sql rowcount.
2376 goto SKIP_TILL_HERE;
2377 end if;--Check on p_phantom_exists
2378
2379 l_stmt_num := 140;
2380 -- End : Fix for bug #3313480 --
2381
2382
2383 -- ***** Make a copy of the Bill (primary and substitute components) for the job ***** --
2384
2385 IF (p_common_bill_seq_id IS NULL) THEN -- No bill attached to the job
2386 goto SKIP_TILL_HERE;
2387 END IF;
2388
2389 l_stmt_num := 150;
2390
2391 l_top_level_bill_seq_id := p_bill_seq_id; --p_common_bill_seq_id;
2392 l_phantom_org_id := p_org_id;
2393
2394 l_stmt_num := 160;
2395
2396 IF (g_debug = 'Y') THEN
2397 fnd_file.put_line(fnd_file.log, 'At stmt('||l_stmt_num||'): Parameters to exploder_userexit for 1st level explosion :');
2398 fnd_file.put_line(fnd_file.log, 'expl_group_id ='||l_explosion_group_id||
2399 ', Bill item_id='||p_bill_item_id||
2400 ', alt_desg='||p_alt_bom_desig||
2401 ', comp_code=NULL');
2402 END IF;
2403
2404 bompexpl.exploder_userexit(
2405 verify_flag => 0,
2406 org_id => p_org_id,
2407 order_by => 1,
2408 grp_id => l_explosion_group_id,
2409 session_id => 0,
2410 levels_to_explode => 1,
2411 bom_or_eng => 1,
2412 impl_flag => 1,
2413 plan_factor_flag => 1,
2414 explode_option => 2,
2415 module => 5,
2416 cst_type_id => 0,
2417 std_comp_flag => 0,
2418 expl_qty => 1,
2419 item_id => p_bill_item_id, --p_primary_item_id, -- Fix for bug #3347947
2420 alt_desg => p_alt_bom_desig,
2421 comp_code => '',
2422 rev_date => to_char(p_bom_rev_date, DATETIME_FMT),
2423 err_msg => l_err_buf,
2424 error_code => l_err_code);
2425
2426 IF (l_err_code <> 0) THEN
2427 x_err_code := l_err_code;
2428 x_err_buf := l_err_buf;
2429
2430 raise be_exploder_exception;
2431 END IF;
2432
2433 l_stmt_num := 170;
2434
2435 l_op_seq_one_exists_in_ntwk := 0;
2436
2437
2438 BEGIN
2439 SELECT 1
2440 INTO l_op_seq_one_exists_in_ntwk
2441 FROM WSM_COPY_OP_NETWORKS
2442 WHERE routing_sequence_id = p_common_rtg_seq_id
2443 AND wip_entity_id = p_wip_entity_id -- Added for performance improvement
2444 AND 1 in (from_op_seq_num, to_op_seq_num);
2445 EXCEPTION
2446 WHEN no_data_found THEN
2447 l_op_seq_one_exists_in_ntwk := 0;
2448 WHEN too_many_rows THEN
2449 l_op_seq_one_exists_in_ntwk := 1;
2450 END;
2451
2452 IF (g_debug = 'Y') THEN
2453 SELECT count(*)
2454 INTO l_be_count
2455 FROM BOM_EXPLOSION_TEMP
2456 WHERE group_id = l_explosion_group_id;
2457
2458 fnd_file.put_line(fnd_file.log, 'At stmt('||l_stmt_num||'): '||
2459 ' l_explosion_group_id ='||l_explosion_group_id||
2460 ', l_be_count ='||l_be_count||
2461 ', l_op_seq_one_exists_in_ntwk='||l_op_seq_one_exists_in_ntwk);
2462 END IF;
2463
2464 OPEN job_ops;
2465 LOOP
2466 l_stmt_num := 171;
2467 FETCH job_ops
2468 INTO l_curr_op_seq_num,
2469 l_curr_op_seq_id,
2470 l_curr_op_is_ntwk_st_end,
2471 l_curr_op_start_date,
2472 l_curr_op_compl_date,
2473 l_curr_op_dept_id;
2474 EXIT WHEN job_ops%NOTFOUND;
2475
2476 l_curr_first_op_attach_opseq1 := 0;
2477 -- l_curr_first_op_attach_opseq1=1 implies that current op is first op
2478 -- and there is no op seq 1 in network
2479 -- Hence attach comps at opseq 1, if any, in BOM to this first op
2480
2481 IF (l_curr_op_is_ntwk_st_end = l_network_start) --'S' --Fixed bug #3761385
2482 THEN
2483 IF (l_op_seq_one_exists_in_ntwk = 0) THEN
2484 l_curr_first_op_attach_opseq1 := 1;
2485 END IF;
2486 END IF;
2487
2488
2489 -- Moved the comments down and added l_curr_first_op_attach_opseq1
2490 IF (g_debug = 'Y') THEN
2491 fnd_file.put_line(fnd_file.log, 'At stmt('||l_stmt_num||'): '||
2492 ' l_curr_op_seq_num ='||l_curr_op_seq_num ||
2493 ', l_curr_op_seq_id ='||l_curr_op_seq_id ||
2494 ', l_curr_op_is_ntwk_st_end ='||l_curr_op_is_ntwk_st_end ||
2495 ', l_curr_op_start_date ='||l_curr_op_start_date ||
2496 ', l_curr_op_compl_date ='||l_curr_op_compl_date ||
2497 ', l_curr_op_dept_id ='||l_curr_op_dept_id ||
2498 ', l_curr_first_op_attach_opseq1='||l_curr_first_op_attach_opseq1||
2499 ', l_top_level_bill_seq_id ='||l_top_level_bill_seq_id);
2500 END IF;
2501
2502 l_curr_op_total_comps := 0;
2503
2504 l_stmt_num := 172;
2505
2506 BEGIN
2507 OPEN reqs;
2508 LOOP
2509 FETCH reqs INTO t_comp_details(l_curr_op_total_comps);
2510 EXIT WHEN reqs%NOTFOUND;
2511 l_curr_op_total_comps := l_curr_op_total_comps+1;
2512 END LOOP;
2513 CLOSE reqs;
2514 END;
2515
2516
2517 IF (g_debug = 'Y') THEN
2518 fnd_file.put_line(fnd_file.log, 'l_curr_op_total_comps at stmt('||l_stmt_num||'): '||l_curr_op_total_comps);
2519 END IF;
2520
2521 l_curr_op_first_level_comps:=l_curr_op_total_comps;
2522 l_first_level_comps_ctr:=0;
2523 LOOP
2524 l_stmt_num := 173;
2525 IF (t_comp_details.exists(l_first_level_comps_ctr)) THEN
2526
2527 IF (t_comp_details(l_first_level_comps_ctr).wip_supply_type=6) THEN
2528 BEGIN
2529
2530 l_stmt_num := 180;
2531
2532 IF (g_debug = 'Y') THEN
2533 fnd_file.put_line(fnd_file.log, 'At stmt('||l_stmt_num||'): '||
2534 ' Phantom found at op'||t_comp_details(l_first_level_comps_ctr).operation_seq_num);
2535 END IF;
2536
2537 t_comp_details(l_first_level_comps_ctr).operation_seq_num :=-l_curr_op_seq_num;
2538 l_phantom_reqd_qty := t_comp_details(l_first_level_comps_ctr).required_quantity;
2539
2540 SELECT B.BILL_SEQUENCE_ID,
2541 B.ORGANIZATION_ID
2542 INTO l_phantom_bill_seq_id,
2543 l_phantom_org_id
2544 FROM BOM_BILL_OF_MATERIALS B
2545 WHERE B.ASSEMBLY_ITEM_ID = t_comp_details(l_first_level_comps_ctr).component_item_id
2546 AND B.ORGANIZATION_ID = p_org_id
2547 AND B.ALTERNATE_BOM_DESIGNATOR IS NULL;
2548
2549 EXCEPTION
2550 WHEN NO_DATA_FOUND THEN
2551 l_phantom_bill_seq_id := NULL;
2552 END;
2553
2554 -- bug 7598223: begin
2555 l_stmt_num := 185;
2556 begin
2557 select 'Y'
2558 into l_ato_phantom
2559 from mtl_system_items msi
2560 where msi.inventory_item_id = t_comp_details(l_first_level_comps_ctr).component_item_id
2561 and msi.organization_id = p_org_id
2562 and msi.replenish_to_order_flag = 'Y'
2563 and msi.bom_item_type in (1,2);
2564 exception
2565 when no_data_found then
2566 l_ato_phantom := 'N';
2567 end;
2568 -- bug 7598223: end
2569
2570 l_stmt_num := 190;
2571
2572 BEGIN
2573 SELECT common_routing_sequence_id
2574 INTO l_phantom_rtg_seq_id
2575 FROM BOM_OPERATIONAL_ROUTINGS
2576 WHERE assembly_item_id = t_comp_details(l_first_level_comps_ctr).component_item_id
2577 AND organization_id = p_org_id
2578 AND alternate_routing_designator is null
2579 AND cfm_routing_flag = 3;
2580 EXCEPTION
2581 WHEN NO_DATA_FOUND THEN
2582 null;
2583 END;
2584
2585
2586 IF (g_debug = 'Y') THEN
2587 fnd_file.put_line(fnd_file.log, 'At stmt('||l_stmt_num||'): '||
2588 'l_phantom_reqd_qty = '||l_phantom_reqd_qty||
2589 ', l_phantom_bill_seq_id = '||l_phantom_bill_seq_id||
2590 ', l_phantom_rtg_seq_id= '||l_phantom_rtg_seq_id ||
2591 ', l_phantom_org_id = '||l_phantom_org_id);
2592 END IF;
2593
2594 IF (l_phantom_bill_seq_id is not NULL and l_ato_phantom = 'N') THEN --bug 7598223
2595
2596 l_stmt_num := 200;
2597
2598
2599 SELECT BOM_EXPLOSION_TEMP_S.nextval
2600 INTO l_phantom_expl_group_id
2601 FROM DUAL;
2602
2603 l_stmt_num := 210;
2604
2605
2606 IF (g_debug = 'Y') THEN
2607 fnd_file.put_line(fnd_file.log, 'At stmt('||l_stmt_num||'): Parameters to exploder_userexit for exploding all levels :');
2608 fnd_file.put_line(fnd_file.log, 'expl_group_id ='||l_phantom_expl_group_id||
2609 ', item_id='||t_comp_details(l_first_level_comps_ctr).component_item_id||
2610 ', alt_desg=NULL'||
2611 ', comp_code='||p_bill_item_id);
2612 END IF;
2613
2614 bompexpl.exploder_userexit(
2615 verify_flag => 0,
2616 org_id => p_org_id,
2617 order_by => 1,
2618 grp_id => l_phantom_expl_group_id,
2619 session_id => 0,
2620 levels_to_explode => l_max_bill_levels-1,
2621 bom_or_eng => 1,
2622 impl_flag => 1,
2623 plan_factor_flag => 1,
2624 explode_option => 2,
2625 module => 5,
2626 cst_type_id => 0,
2627 std_comp_flag => 0,
2628 expl_qty => 1,
2629 item_id => t_comp_details(l_first_level_comps_ctr).component_item_id,
2630 alt_desg => NULL,
2631 comp_code => to_char(p_bill_item_id), --to_char(p_primary_item_id)-- Fix for bug #3347947
2632 rev_date => to_char(p_bom_rev_date, DATETIME_FMT),
2633 err_msg => l_err_buf,
2634 error_code => l_err_code);
2635
2636 IF (l_err_code <> 0) THEN
2637 x_err_code := l_err_code;
2638 x_err_buf := l_err_buf;
2639
2640 raise be_exploder_exception;
2641 END IF;
2642
2643
2644 IF (g_debug = 'Y') THEN
2645 SELECT count(*)
2646 INTO l_be_count
2647 FROM BOM_EXPLOSION_TEMP
2648 WHERE group_id = l_phantom_expl_group_id;
2649
2650 fnd_file.put_line(fnd_file.log, 'At stmt('||l_stmt_num||'): '||
2651 ' l_phantom_expl_group_id ='||l_phantom_expl_group_id||
2652 ', l_be_count ='||l_be_count);
2653 END IF;
2654
2655 END IF;
2656
2657 IF ((l_phantom_rtg_seq_id is not null) AND
2658 (l_use_phantom_routings=1) AND
2659 (l_phantom_bill_seq_id is not NULL and l_ato_phantom = 'N')) THEN --bug 7598223
2660
2661 DECLARE
2662 l_phantom_bill_levels NUMBER:=0;
2663 l_level NUMBER := 1;
2664 BEGIN
2665
2666 l_stmt_num := 210;
2667
2668 SELECT max(plan_level)
2669 INTO l_phantom_bill_levels
2670 FROM bom_explosion_temp
2671 WHERE top_bill_sequence_id = l_phantom_bill_seq_id;
2672
2673 FOR l_level in 1..l_phantom_bill_levels
2674 LOOP
2675 l_stmt_num := 220;
2676
2677 UPDATE bom_explosion_temp be
2678 SET be.primary_path_flag=1
2679 WHERE be.top_bill_sequence_id=l_phantom_bill_seq_id
2680 AND be.group_id=l_phantom_expl_group_id --l_explosion_group_id
2681 AND ((be.operation_seq_num=1)
2682 OR (exists
2683 (SELECT 1
2684 FROM bom_operational_routings bor,
2685 bom_operation_networks_v bonv,
2686 bom_operation_sequences bos
2687 WHERE bor.assembly_item_id =
2688 be.assembly_item_id
2689 AND bor.alternate_routing_designator
2690 is null
2691 AND bonv.routing_sequence_id =
2692 bor.common_routing_sequence_id
2693 AND be.operation_seq_num =
2694 bos.operation_seq_num
2695 AND bos.routing_sequence_id =
2696 bor.common_routing_sequence_id
2697 AND p_rtg_rev_date BETWEEN
2698 bos.effectivity_date and
2699 nvl(bos.disable_date, p_rtg_rev_date+1)
2700 AND NVL(BOS.operation_type, 1) = 1
2701 AND bonv.transition_type=1
2702 AND be.operation_seq_num in
2703 (bonv.FROM_seq_num,
2704 bonv.to_seq_num)
2705 )
2706 )
2707 )
2708 AND plan_level=l_level
2709 AND ((plan_level=1)
2710 OR (exists
2711 (SELECT 'x'
2712 FROM bom_explosion_temp be1
2713 WHERE be1.top_bill_sequence_id =
2714 l_phantom_bill_seq_id
2715 AND be1.group_id =
2716 l_phantom_expl_group_id --l_explosion_group_id
2717 AND be1.component_item_id =
2718 be.assembly_item_id
2719 AND SUBSTR(be1.sort_order, 1, l_level*
2720 (SORT_WIDTH)) =
2721 SUBSTR(BE.SORT_ORDER, 1, l_level*
2722 (SORT_WIDTH))
2723 AND be1.primary_path_flag=1
2724 )
2725 )
2726 );
2727
2728 END LOOP;
2729 END;
2730 ELSE
2731
2732 l_stmt_num := 230;
2733 UPDATE bom_explosion_temp be
2734 SET be.primary_path_flag=1
2735 WHERE be.top_bill_sequence_id = l_phantom_bill_seq_id;
2736
2737 END IF;
2738
2739
2740 IF (g_debug = 'Y') THEN
2741
2742 fnd_file.put_line(fnd_file.log, 'Before phan_comp '||
2743 ' PRIMARY_COMPONENT_ID = '||t_comp_details(l_first_level_comps_ctr).PRIMARY_COMPONENT_ID||
2744 ', COMPONENT_ITEM_ID = '||t_comp_details(l_first_level_comps_ctr).COMPONENT_ITEM_ID||
2745 ', l_phantom_reqd_qty = '||l_phantom_reqd_qty||
2746 ', l_phantom_bill_seq_id = '||l_phantom_bill_seq_id);
2747 END IF;
2748
2749 DECLARE
2750 CURSOR phan_comp is
2751 SELECT BE.OPERATION_SEQ_NUM,
2752 BE.COMPONENT_ITEM_ID COMPONENT_ITEM_ID,
2753 t_comp_details(l_first_level_comps_ctr).PRIMARY_COMPONENT_ID PRIMARY_COMPONENT_ID,
2754 BE.COMPONENT_SEQUENCE_ID,
2755 t_comp_details(l_first_level_comps_ctr).COMPONENT_ITEM_ID source_phantom_id,
2756 --Populate only for phantoms, else -1
2757 t_comp_details(l_first_level_comps_ctr).recommended recommended,
2758 l_curr_op_start_date reco_start_date,
2759 A.BILL_SEQUENCE_ID,
2760 l_curr_op_dept_id department_id,
2761
2762 decode(p_wip_supply_type,
2763 7,
2764 nvl(A.WIP_SUPPLY_TYPE, nvl(C.WIP_SUPPLY_TYPE, 1)),
2765 p_wip_supply_type) wip_supply_type,
2766 --Bug 5216333: Suppy type specified at job should be looked at first
2767 decode (p_org_id,
2768 l_phantom_org_id,
2769 nvl(A.SUPPLY_SUBINVENTORY,
2770 nvl(C.WIP_SUPPLY_SUBINVENTORY,
2771 decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE,
2772 nvl(C.WIP_SUPPLY_TYPE, 1)),p_wip_supply_type),
2773 2,
2774 l_wip_param_def_subinv,
2775 3,
2776 l_wip_param_def_subinv,
2777 NULL
2778 )
2779 )
2780 ),
2781 nvl(C.WIP_SUPPLY_SUBINVENTORY,
2782 decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE,
2783 nvl(C.WIP_SUPPLY_TYPE, 1)),p_wip_supply_type),
2784 2,
2785 l_wip_param_def_subinv,
2786 3,
2787 l_wip_param_def_subinv,
2788 NULL
2789 )
2790 )
2791 ) supply_subinventory,
2792 --Bug 5216333: Suppy type specified at job should be looked at first
2793 decode (p_org_id, -- Supply locator id begin
2794 l_phantom_org_id,
2795 decode (A.SUPPLY_SUBINVENTORY,
2796 NULL,
2797 decode (C.WIP_SUPPLY_SUBINVENTORY,
2798 NULL,
2799 decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE,
2800 nvl(C.WIP_SUPPLY_TYPE,
2801 1)),p_wip_supply_type),
2802 2,
2803 --nvl(l_wip_param_def_locator_id,-1),
2804 l_wip_param_def_locator_id,
2805 3,
2806 l_wip_param_def_locator_id,
2807 NULL
2808 ),
2809 C.WIP_SUPPLY_LOCATOR_ID
2810 ),
2811 A.SUPPLY_LOCATOR_ID
2812 ),
2813 decode (C.WIP_SUPPLY_SUBINVENTORY,
2814 NULL,
2815 decode(decode(p_wip_supply_type,7,nvl(A.WIP_SUPPLY_TYPE,
2816 nvl(C.WIP_SUPPLY_TYPE, 1)),p_wip_supply_type),
2817 2,
2818 --nvl(l_wip_param_def_locator_id,-1),
2819 l_wip_param_def_locator_id,
2820 3,
2821 --nvl(l_wip_param_def_locator_id,-1),
2822 l_wip_param_def_locator_id,
2823 NULL
2824 ),
2825 C.WIP_SUPPLY_LOCATOR_ID
2826 )
2827 ) supply_locator_id, -- Supply locator id end
2828
2829
2830 -- BE.extended_quantity*l_phantom_reqd_qty required_quantity,
2831 BE.extended_quantity *
2832 decode(nvl(A.BASIS_TYPE, 1),
2833 1, l_phantom_reqd_qty,
2834 1) required_quantity, --Fix bug #5034531
2835
2836 BE.component_quantity BILL_QUANTITY_PER_ASSEMBLY,
2837
2838 A.COMPONENT_YIELD_FACTOR,
2839 A.BASIS_TYPE, --LBM enh
2840 A.EFFECTIVITY_DATE EFFECTIVITY_DATE,
2841 A.DISABLE_DATE,
2842 null COMPONENT_PRIORITY, --bug fix 7143452
2843 A.PARENT_BILL_SEQ_ID,
2844
2845 A.ITEM_NUM,
2846 A.COMPONENT_REMARKS,
2847 A.CHANGE_NOTICE,
2848 A.IMPLEMENTATION_DATE,
2849 A.PLANNING_FACTOR,
2850 A.QUANTITY_RELATED,
2851 A.SO_BASIS,
2852 A.OPTIONAL,
2853 A.MUTUALLY_EXCLUSIVE_OPTIONS,
2854 A.INCLUDE_IN_COST_ROLLUP,
2855 A.CHECK_ATP,
2856 A.SHIPPING_ALLOWED,
2857 A.REQUIRED_TO_SHIP,
2858 A.REQUIRED_FOR_REVENUE,
2859 A.INCLUDE_ON_SHIP_DOCS,
2860 A.LOW_QUANTITY,
2861 A.HIGH_QUANTITY,
2862 A.ACD_TYPE,
2863 A.OLD_COMPONENT_SEQUENCE_ID,
2864 A.OPERATION_LEAD_TIME_PERCENT,
2865 A.REVISED_ITEM_SEQUENCE_ID,
2866 A.BOM_ITEM_TYPE,
2867 A.FROM_END_ITEM_UNIT_NUMBER,
2868 A.TO_END_ITEM_UNIT_NUMBER,
2869 A.ECO_FOR_PRODUCTION,
2870 A.ENFORCE_INT_REQUIREMENTS,
2871 A.DELETE_GROUP_NAME,
2872 A.DG_DESCRIPTION,
2873 A.OPTIONAL_ON_MODEL,
2874 A.MODEL_COMP_SEQ_ID,
2875 A.PLAN_LEVEL,
2876 A.AUTO_REQUEST_MATERIAL,
2877 A.COMPONENT_ITEM_REVISION_ID,
2878 A.FROM_BILL_REVISION_ID,
2879 A.TO_BILL_REVISION_ID,
2880 A.PICK_COMPONENTS,
2881 A.INCLUDE_ON_BILL_DOCS,
2882 A.COST_FACTOR,
2883 A.ORIGINAL_SYSTEM_REFERENCE,
2884 a.attribute_category,
2885 a.attribute1,
2886 a.attribute2,
2887 a.attribute3,
2888 a.attribute4,
2889 a.attribute5,
2890 a.attribute6,
2891 a.attribute7,
2892 a.attribute8,
2893 a.attribute9,
2894 a.attribute10,
2895 a.attribute11,
2896 a.attribute12,
2897 a.attribute13,
2898 a.attribute14,
2899 a.attribute15
2900 FROM BOM_INVENTORY_COMPONENTS A,
2901 MTL_SYSTEM_ITEMS C,
2902 BOM_EXPLOSION_TEMP BE
2903 WHERE BE.GROUP_ID = l_phantom_expl_group_id--l_explosion_group_id
2904 AND A.COMPONENT_SEQUENCE_ID = BE.COMPONENT_SEQUENCE_ID
2905 AND C.INVENTORY_ITEM_ID = BE.COMPONENT_ITEM_ID
2906 AND C.ORGANIZATION_ID = p_org_id
2907 AND BE.TOP_BILL_SEQUENCE_ID = l_phantom_bill_seq_id
2908 AND BE.PRIMARY_PATH_FLAG = 1
2909 AND p_bom_rev_date BETWEEN A.EFFECTIVITY_DATE and
2910 nvl(A.DISABLE_DATE, p_bom_rev_date+1)
2911 AND A.EFFECTIVITY_DATE =
2912 (SELECT MAX(EFFECTIVITY_DATE)
2913 FROM BOM_INVENTORY_COMPONENTS BIC,
2914 ENG_REVISED_ITEMS ERI
2915 WHERE BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
2916 AND BIC.COMPONENT_ITEM_ID = A.COMPONENT_ITEM_ID
2917 AND (decode(BIC.IMPLEMENTATION_DATE,
2918 NULL,
2919 BIC.OLD_COMPONENT_SEQUENCE_ID,
2920 BIC.COMPONENT_SEQUENCE_ID
2921 ) =
2922 decode(A.IMPLEMENTATION_DATE,
2923 NULL,
2924 A.OLD_COMPONENT_SEQUENCE_ID,
2925 A.COMPONENT_SEQUENCE_ID
2926 )
2927 OR
2928 BIC.OPERATION_SEQ_NUM = A.OPERATION_SEQ_NUM
2929 )
2930 AND BIC.EFFECTIVITY_DATE <= p_bom_rev_date
2931 AND BIC.REVISED_ITEM_SEQUENCE_ID =
2932 ERI.REVISED_ITEM_SEQUENCE_ID(+)
2933 AND (nvl(ERI.STATUS_TYPE,6) IN (4,6,7))
2934 AND NOT EXISTS
2935 (SELECT 'X'
2936 FROM BOM_INVENTORY_COMPONENTS BICN,
2937 ENG_REVISED_ITEMS ERI1
2938 WHERE BICN.BILL_SEQUENCE_ID =
2939 A.BILL_SEQUENCE_ID
2940 AND BICN.OLD_COMPONENT_SEQUENCE_ID =
2941 A.COMPONENT_SEQUENCE_ID
2942 AND BICN.ACD_TYPE in (2,3)
2943 AND BICN.DISABLE_DATE <=
2944 p_bom_rev_date
2945 AND ERI1.REVISED_ITEM_SEQUENCE_ID =
2946 BICN.REVISED_ITEM_SEQUENCE_ID
2947 AND (nvl(ERI1.STATUS_TYPE,6)IN(4,6,7))
2948 )
2949 )
2950 ORDER BY A.COMPONENT_ITEM_ID,
2951 nvl(A.WIP_SUPPLY_TYPE, C.WIP_SUPPLY_TYPE),
2952 TO_NUMBER(TO_CHAR(A.EFFECTIVITY_DATE,'SSSS'));
2953
2954 BEGIN
2955 l_stmt_num := 240;
2956 OPEN phan_comp;
2957 LOOP
2958 l_stmt_num := 250;
2959
2960 FETCH phan_comp INTO t_comp_details(l_curr_op_total_comps);
2961 EXIT WHEN phan_comp%NOTFOUND;
2962
2963 -- Check for BOM Loops
2964 l_stmt_num := 255;
2965 IF ((t_comp_details(l_curr_op_total_comps).component_item_id =
2966 p_bill_item_id) -- p_primary_item_id) -- Fix for bug #3347947
2967 AND
2968 (t_comp_details(l_curr_op_total_comps).wip_supply_type=6)) THEN
2969
2970 -- A loop has been detected in this Routing Network.
2971 fnd_message.set_name('WSM', 'WSM_NTWK_LOOP_EXISTS');
2972 x_err_buf := fnd_message.get;
2973 x_err_code := -2; --Error
2974
2975 raise loop_in_bom_exception;
2976 END IF;
2977
2978 -- Set the correct op_seq_num
2979 IF (t_comp_details(l_curr_op_total_comps).wip_supply_type=6) THEN
2980 t_comp_details(l_curr_op_total_comps).operation_seq_num :=
2981 -l_curr_op_seq_num;
2982 ELSE
2983 t_comp_details(l_curr_op_total_comps).operation_seq_num :=
2984 l_curr_op_seq_num;
2985 END IF;
2986
2987 l_curr_op_total_comps := l_curr_op_total_comps+1;
2988 END LOOP;
2989 CLOSE phan_comp;
2990 END;
2991 END IF; --if (t_comp_details(l_first_level_comps_ctr).wip_supply_type=6)
2992
2993 ----------END PHANTOMS------------------------------------------
2994
2995 IF (l_first_level_comps_ctr=(l_curr_op_first_level_comps - 1)) THEN
2996 EXIT;
2997 ELSE
2998 l_first_level_comps_ctr := l_first_level_comps_ctr+1;
2999 END IF;
3000 ELSE -- IF (t_comp_details.exists(l_first_level_comps_ctr))
3001 exit;
3002 END IF;
3003 END LOOP;
3004
3005
3006 -- At this point, all the components, primary and phantom exploded,
3007 -- are in t_comp_details
3008 -- These will be inserted, one by one, in WCRO
3009
3010 l_all_level_comps_ctr := 0;
3011
3012 WHILE (l_all_level_comps_ctr<(t_comp_details.last+1))
3013 LOOP
3014 l_all_level_comps_subctr := l_all_level_comps_ctr+1;
3015 IF (t_comp_details.exists(l_all_level_comps_ctr)) THEN
3016
3017
3018 ---START : MERGING REQUIREMENTS AT AN OPERATION WITH SAME ---
3019 ---COMP_ITEM_ID, PRI_COMP_ID, COMP_SEQ_ID and SRC_PHANTOM_ID---
3020 WHILE (l_all_level_comps_subctr<(t_comp_details.last+1))
3021 LOOP
3022
3023 -- Add up the total reqd_qty, by merging all requirments at an op
3024 -- with same comp_item_id, pri_comp_id, comp_seq_id, src_phantom_id.
3025 -- Also set the supply type to be = least supply type (as in WIP)
3026
3027 IF ( (t_comp_details.exists(l_all_level_comps_subctr)) AND
3028 (t_comp_details(l_all_level_comps_ctr).source_phantom_id <> -1) AND -- a phantom
3029 (t_comp_details(l_all_level_comps_subctr).source_phantom_id <> -1) -- a phantom
3030 )
3031 THEN
3032
3033
3034 IF (g_debug = 'Y') THEN
3035 fnd_file.put_line(fnd_file.log, '******START**************');
3036 fnd_file.put_line(fnd_file.log,
3037 '(l_all_level_comps_ctr).component_item_id='||t_comp_details(l_all_level_comps_ctr).component_item_id||
3038 ', (l_all_level_comps_subctr).component_item_id='||t_comp_details(l_all_level_comps_subctr).component_item_id);
3039 fnd_file.put_line(fnd_file.log,
3040 '(l_all_level_comps_ctr).primary_component_id='||t_comp_details(l_all_level_comps_ctr).primary_component_id||
3041 ', (l_all_level_comps_subctr).primary_component_id='||t_comp_details(l_all_level_comps_subctr).primary_component_id);
3042 fnd_file.put_line(fnd_file.log,
3043 '(l_all_level_comps_ctr).component_sequence_id='||t_comp_details(l_all_level_comps_ctr).component_sequence_id||
3044 ', (l_all_level_comps_subctr).component_sequence_id='||t_comp_details(l_all_level_comps_subctr).component_sequence_id);
3045 fnd_file.put_line(fnd_file.log,
3046 '(l_all_level_comps_ctr).source_phantom_id='||t_comp_details(l_all_level_comps_ctr).source_phantom_id||
3047 ', (l_all_level_comps_subctr).source_phantom_id='||t_comp_details(l_all_level_comps_subctr).source_phantom_id);
3048 fnd_file.put_line(fnd_file.log,
3049 '(l_all_level_comps_ctr).required_quantity='||t_comp_details(l_all_level_comps_ctr).required_quantity||
3050 ', (l_all_level_comps_subctr).required_quantity='||t_comp_details(l_all_level_comps_subctr).required_quantity);
3051 fnd_file.put_line(fnd_file.log,
3052 '(l_all_level_comps_ctr).BILL_QUANTITY_PER_ASSEMBLY='||t_comp_details(l_all_level_comps_ctr).BILL_QUANTITY_PER_ASSEMBLY||
3053 ', (l_all_level_comps_subctr).BILL_QUANTITY_PER_ASSEMBLY='||t_comp_details(l_all_level_comps_subctr).BILL_QUANTITY_PER_ASSEMBLY);
3054 fnd_file.put_line(fnd_file.log, '******END**************');
3055 END IF;
3056
3057 IF (t_comp_details(l_all_level_comps_ctr).component_item_id =
3058 t_comp_details(l_all_level_comps_subctr).component_item_id)
3059 AND (t_comp_details(l_all_level_comps_ctr).primary_component_id =
3060 t_comp_details(l_all_level_comps_subctr).primary_component_id)
3061 AND (t_comp_details(l_all_level_comps_ctr).component_sequence_id =
3062 t_comp_details(l_all_level_comps_subctr).component_sequence_id)
3063 AND (t_comp_details(l_all_level_comps_ctr).source_phantom_id =
3064 t_comp_details(l_all_level_comps_subctr).source_phantom_id)
3065 THEN
3066
3067
3068 IF (g_debug = 'Y') THEN
3069 fnd_file.put_line(fnd_file.log, 'MATCHING !!!!!!!!!!!!!');
3070 END IF;
3071
3072 t_comp_details(l_all_level_comps_ctr).required_quantity :=
3073 t_comp_details(l_all_level_comps_ctr).required_quantity +
3074 t_comp_details(l_all_level_comps_subctr).required_quantity;
3075
3076 IF (t_comp_details(l_all_level_comps_ctr).wip_supply_type >
3077 t_comp_details(l_all_level_comps_subctr).wip_supply_type) THEN
3078
3079 t_comp_details(l_all_level_comps_ctr).wip_supply_type :=
3080 t_comp_details(l_all_level_comps_subctr).wip_supply_type;
3081 t_comp_details(l_all_level_comps_ctr).supply_subinventory :=
3082 t_comp_details(l_all_level_comps_subctr).supply_subinventory;
3083 t_comp_details(l_all_level_comps_ctr).supply_locator_id :=
3084 t_comp_details(l_all_level_comps_subctr).supply_locator_id;
3085 END IF;
3086
3087 t_comp_details.delete(l_all_level_comps_subctr);
3088
3089 END IF;
3090 END IF;
3091 l_all_level_comps_subctr:=l_all_level_comps_subctr+1;
3092 END LOOP;
3093 ---END : MERGING REQUIREMENTS AT AN OPERATION WITH SAME ---
3094 ---COMP_ITEM_ID, PRI_COMP_ID, COMP_SEQ_ID and SRC_PHANTOM_ID---
3095
3096
3097 l_stmt_num := 260;
3098 INSERT INTO WSM_COPY_REQUIREMENT_OPS
3099 (WIP_ENTITY_ID,
3100 OPERATION_SEQ_NUM,
3101 COMPONENT_ITEM_ID,
3102 PRIMARY_COMPONENT_ID,
3103 COMPONENT_SEQUENCE_ID,
3104 SOURCE_PHANTOM_ID,
3105 RECOMMENDED,
3106 RECO_DATE_REQUIRED,
3107 BILL_SEQUENCE_ID,
3108 DEPARTMENT_ID,
3109 ORGANIZATION_ID,
3110 WIP_SUPPLY_TYPE,
3111 SUPPLY_SUBINVENTORY,
3112 SUPPLY_LOCATOR_ID,
3113 QUANTITY_PER_ASSEMBLY,
3114 BILL_QUANTITY_PER_ASSEMBLY,
3115 COMPONENT_YIELD_FACTOR,
3116 BASIS_TYPE, --LBM enh
3117 EFFECTIVITY_DATE,
3118 DISABLE_DATE,
3119 COMPONENT_PRIORITY,
3120 PARENT_BILL_SEQ_ID,
3121 ITEM_NUM,
3122 COMPONENT_REMARKS,
3123 CHANGE_NOTICE,
3124 IMPLEMENTATION_DATE,
3125 PLANNING_FACTOR,
3126 QUANTITY_RELATED,
3127 SO_BASIS,
3128 OPTIONAL,
3129 MUTUALLY_EXCLUSIVE_OPTIONS,
3130 INCLUDE_IN_COST_ROLLUP,
3131 CHECK_ATP,
3132 SHIPPING_ALLOWED,
3133 REQUIRED_TO_SHIP,
3134 REQUIRED_FOR_REVENUE,
3135 INCLUDE_ON_SHIP_DOCS,
3136 LOW_QUANTITY,
3137 HIGH_QUANTITY,
3138 ACD_TYPE,
3139 OLD_COMPONENT_SEQUENCE_ID,
3140 OPERATION_LEAD_TIME_PERCENT,
3141 REVISED_ITEM_SEQUENCE_ID,
3142 BOM_ITEM_TYPE,
3143 FROM_END_ITEM_UNIT_NUMBER,
3144 TO_END_ITEM_UNIT_NUMBER,
3145 ECO_FOR_PRODUCTION,
3146 ENFORCE_INT_REQUIREMENTS,
3147 DELETE_GROUP_NAME,
3148 DG_DESCRIPTION,
3149 OPTIONAL_ON_MODEL,
3150 MODEL_COMP_SEQ_ID,
3151 PLAN_LEVEL,
3152 AUTO_REQUEST_MATERIAL,
3153 COMPONENT_ITEM_REVISION_ID,
3154 FROM_BILL_REVISION_ID,
3155 TO_BILL_REVISION_ID,
3156 PICK_COMPONENTS,
3157 INCLUDE_ON_BILL_DOCS,
3158 COST_FACTOR,
3159 LAST_UPDATE_DATE,
3160 LAST_UPDATED_BY,
3161 LAST_UPDATE_LOGIN,
3162 CREATION_DATE,
3163 CREATED_BY,
3164 REQUEST_ID,
3165 PROGRAM_APPLICATION_ID,
3166 PROGRAM_ID,
3167 PROGRAM_UPDATE_DATE,
3168 ATTRIBUTE_CATEGORY,
3169 ATTRIBUTE1,
3170 ATTRIBUTE2,
3171 ATTRIBUTE3,
3172 ATTRIBUTE4,
3173 ATTRIBUTE5,
3174 ATTRIBUTE6,
3175 ATTRIBUTE7,
3176 ATTRIBUTE8,
3177 ATTRIBUTE9,
3178 ATTRIBUTE10,
3179 ATTRIBUTE11,
3180 ATTRIBUTE12,
3181 ATTRIBUTE13,
3182 ATTRIBUTE14,
3183 ATTRIBUTE15,
3184 ORIGINAL_SYSTEM_REFERENCE
3185 )
3186 VALUES
3187 (p_wip_entity_id,
3188 t_comp_details(l_all_level_comps_ctr).operation_seq_num,
3189 t_comp_details(l_all_level_comps_ctr).component_item_id,
3190 t_comp_details(l_all_level_comps_ctr).PRIMARY_COMPONENT_ID,
3191 t_comp_details(l_all_level_comps_ctr).component_sequence_id,
3192 t_comp_details(l_all_level_comps_ctr).source_phantom_id,
3193 t_comp_details(l_all_level_comps_ctr).RECOMMENDED,
3194 NULL,--RECO_DATE_REQUIRED
3195 t_comp_details(l_all_level_comps_ctr).BILL_SEQUENCE_ID,
3196 t_comp_details(l_all_level_comps_ctr).department_id,
3197 p_org_id,
3198 t_comp_details(l_all_level_comps_ctr).wip_supply_type,
3199 t_comp_details(l_all_level_comps_ctr).supply_subinventory,
3200 decode(t_comp_details(l_all_level_comps_ctr).supply_locator_id,
3201 -1,
3202 NULL,
3203 t_comp_details(l_all_level_comps_ctr).supply_locator_id),--SUPPLY_LOCATOR_ID
3204 t_comp_details(l_all_level_comps_ctr).required_quantity,--QUANTITY_PER_ASSEMBLY
3205 t_comp_details(l_all_level_comps_ctr).BILL_QUANTITY_PER_ASSEMBLY,
3206 t_comp_details(l_all_level_comps_ctr).COMPONENT_YIELD_FACTOR,
3207 t_comp_details(l_all_level_comps_ctr).BASIS_TYPE, --LBM enh
3208 t_comp_details(l_all_level_comps_ctr).EFFECTIVITY_DATE,
3209 t_comp_details(l_all_level_comps_ctr).DISABLE_DATE,
3210 t_comp_details(l_all_level_comps_ctr).COMPONENT_PRIORITY,
3211 t_comp_details(l_all_level_comps_ctr).PARENT_BILL_SEQ_ID,
3212 t_comp_details(l_all_level_comps_ctr).ITEM_NUM,
3213 t_comp_details(l_all_level_comps_ctr).COMPONENT_REMARKS,
3214 t_comp_details(l_all_level_comps_ctr).CHANGE_NOTICE,
3215 t_comp_details(l_all_level_comps_ctr).IMPLEMENTATION_DATE,
3216 t_comp_details(l_all_level_comps_ctr).PLANNING_FACTOR,
3217 t_comp_details(l_all_level_comps_ctr).QUANTITY_RELATED,
3218 t_comp_details(l_all_level_comps_ctr).SO_BASIS,
3219 t_comp_details(l_all_level_comps_ctr).OPTIONAL,
3220 t_comp_details(l_all_level_comps_ctr).MUTUALLY_EXCLUSIVE_OPTIONS,
3221 t_comp_details(l_all_level_comps_ctr).INCLUDE_IN_COST_ROLLUP,
3222 t_comp_details(l_all_level_comps_ctr).CHECK_ATP,
3223 t_comp_details(l_all_level_comps_ctr).SHIPPING_ALLOWED,
3224 t_comp_details(l_all_level_comps_ctr).REQUIRED_TO_SHIP,
3225 t_comp_details(l_all_level_comps_ctr).REQUIRED_FOR_REVENUE,
3226 t_comp_details(l_all_level_comps_ctr).INCLUDE_ON_SHIP_DOCS,
3227 t_comp_details(l_all_level_comps_ctr).LOW_QUANTITY,
3228 t_comp_details(l_all_level_comps_ctr).HIGH_QUANTITY,
3229 t_comp_details(l_all_level_comps_ctr).ACD_TYPE,
3230 t_comp_details(l_all_level_comps_ctr).OLD_COMPONENT_SEQUENCE_ID,
3231 t_comp_details(l_all_level_comps_ctr).OPERATION_LEAD_TIME_PERCENT,
3232 t_comp_details(l_all_level_comps_ctr).REVISED_ITEM_SEQUENCE_ID,
3233 t_comp_details(l_all_level_comps_ctr).BOM_ITEM_TYPE,
3234 t_comp_details(l_all_level_comps_ctr).FROM_END_ITEM_UNIT_NUMBER,
3235 t_comp_details(l_all_level_comps_ctr).TO_END_ITEM_UNIT_NUMBER,
3236 t_comp_details(l_all_level_comps_ctr).ECO_FOR_PRODUCTION,
3237 t_comp_details(l_all_level_comps_ctr).ENFORCE_INT_REQUIREMENTS,
3238 t_comp_details(l_all_level_comps_ctr).DELETE_GROUP_NAME,
3239 t_comp_details(l_all_level_comps_ctr).DG_DESCRIPTION,
3240 t_comp_details(l_all_level_comps_ctr).OPTIONAL_ON_MODEL,
3241 t_comp_details(l_all_level_comps_ctr).MODEL_COMP_SEQ_ID,
3242 t_comp_details(l_all_level_comps_ctr).PLAN_LEVEL,
3243 t_comp_details(l_all_level_comps_ctr).AUTO_REQUEST_MATERIAL,
3244 t_comp_details(l_all_level_comps_ctr).COMPONENT_ITEM_REVISION_ID,
3245 t_comp_details(l_all_level_comps_ctr).FROM_BILL_REVISION_ID,
3246 t_comp_details(l_all_level_comps_ctr).TO_BILL_REVISION_ID,
3247 t_comp_details(l_all_level_comps_ctr).PICK_COMPONENTS,
3248 t_comp_details(l_all_level_comps_ctr).INCLUDE_ON_BILL_DOCS,
3249 t_comp_details(l_all_level_comps_ctr).COST_FACTOR,
3250 p_last_update_date,
3251 p_last_updated_by,
3252 p_last_update_login,
3253 p_creation_date,
3254 p_created_by,
3255 p_request_id,
3256 p_program_app_id,
3257 p_program_id,
3258 p_program_update_date,
3259 t_comp_details(l_all_level_comps_ctr).attribute_category,
3260 t_comp_details(l_all_level_comps_ctr).attribute1,
3261 t_comp_details(l_all_level_comps_ctr).attribute2,
3262 t_comp_details(l_all_level_comps_ctr).attribute3,
3263 t_comp_details(l_all_level_comps_ctr).attribute4,
3264 t_comp_details(l_all_level_comps_ctr).attribute5,
3265 t_comp_details(l_all_level_comps_ctr).attribute6,
3266 t_comp_details(l_all_level_comps_ctr).attribute7,
3267 t_comp_details(l_all_level_comps_ctr).attribute8,
3268 t_comp_details(l_all_level_comps_ctr).attribute9,
3269 t_comp_details(l_all_level_comps_ctr).attribute10,
3270 t_comp_details(l_all_level_comps_ctr).attribute11,
3271 t_comp_details(l_all_level_comps_ctr).attribute12,
3272 t_comp_details(l_all_level_comps_ctr).attribute13,
3273 t_comp_details(l_all_level_comps_ctr).attribute14,
3274 t_comp_details(l_all_level_comps_ctr).attribute15,
3275 t_comp_details(l_all_level_comps_ctr).ORIGINAL_SYSTEM_REFERENCE
3276 );
3277
3278 IF (g_debug = 'Y') THEN
3279 fnd_file.put_line(fnd_file.log, 'Created '||SQL%ROWCOUNT||' records in WCRO for we_id='||p_wip_entity_id);
3280 fnd_file.put_line(fnd_file.log, 'for component='||t_comp_details(l_all_level_comps_ctr).component_item_id||
3281 ', and qpa = '||t_comp_details(l_all_level_comps_ctr).required_quantity||
3282 ', and bill_qpa = '||t_comp_details(l_all_level_comps_ctr).BILL_QUANTITY_PER_ASSEMBLY);
3283 END IF;
3284
3285 END IF; --if (t_comp_details.exists(l_all_level_comps_ctr))
3286 l_all_level_comps_ctr:=l_all_level_comps_ctr+1;
3287 END LOOP;
3288
3289 l_all_level_comps_ctr := 0;
3290
3291
3292 ----------START : PHANTOM RESOURCES------------------------
3293 IF (l_use_phantom_routings = 1) THEN
3294
3295 DECLARE
3296 CURSOR phan_resc_cursor IS
3297 SELECT BOR.SUBSTITUTE_GROUP_NUM,
3298 WCRO.recommended,
3299 BOR.resource_id,
3300 BR.resource_code,
3301 BOS.department_id,
3302 -(WCRO.OPERATION_SEQ_NUM) phantom_op_seq_num,
3303 WCRO.component_ITEM_ID phantom_item_id,
3304 BOR.activity_id,
3305 BOR.standard_rate_flag,
3306 BOR.assigned_units,
3307 decode(BOR.basis_type,
3308 wip_constants.PER_LOT,
3309 BOR.usage_rate_or_amount,
3310 BOR.usage_rate_or_amount * nvl(WCRO.QUANTITY_PER_ASSEMBLY,1)
3311 ) usage_rate_or_amount,
3312 BOR.USAGE_RATE_OR_AMOUNT_INVERSE,
3313 BR.unit_of_measure,
3314 BOR.basis_type,
3315 2 schedule_flag, --BOR.schedule_flag,
3316 -- For phantom resources, always = No.
3317 BOR.RESOURCE_OFFSET_PERCENT,
3318 BOR.autocharge_type,
3319 BOR.SCHEDULE_SEQ_NUM,
3320 BOR.PRINCIPLE_FLAG,
3321 BOR.SETUP_ID,
3322 BOR.CHANGE_NOTICE,
3323 BOR.ACD_TYPE,
3324 bor.ATTRIBUTE_CATEGORY,
3325 bor.ATTRIBUTE1,
3326 bor.ATTRIBUTE2,
3327 bor.ATTRIBUTE3,
3328 bor.ATTRIBUTE4,
3329 bor.ATTRIBUTE5,
3330 bor.ATTRIBUTE6,
3331 bor.ATTRIBUTE7,
3332 bor.ATTRIBUTE8,
3333 bor.ATTRIBUTE9,
3334 bor.ATTRIBUTE10,
3335 bor.ATTRIBUTE11,
3336 bor.ATTRIBUTE12,
3337 bor.ATTRIBUTE13,
3338 bor.ATTRIBUTE14,
3339 bor.ATTRIBUTE15,
3340 bor.ORIGINAL_SYSTEM_REFERENCE
3341 FROM --MTL_UOM_CONVERSIONS CON,
3342 BOM_RESOURCES BR,
3343 BOM_OPERATION_RESOURCES BOR,
3344 BOM_DEPARTMENT_RESOURCES BDR1,
3345 BOM_OPERATION_SEQUENCES BOS,
3346 BOM_OPERATIONAL_ROUTINGS BRTG,
3347 WSM_COPY_REQUIREMENT_OPS WCRO,
3348 MTL_SYSTEM_ITEMS MSI --BUG 7598223
3349 WHERE wcro.wip_entity_id=p_wip_entity_id
3350 AND wcro.organization_id=p_org_id
3351 AND wcro.Operation_seq_num = -l_curr_op_seq_num
3352 AND BRTG.assembly_item_id = wcro.component_item_id
3353 AND BRTG.organization_id = p_org_id
3354 AND MSI.inventory_item_id = wcro.component_item_id --BUG 7598223
3355 AND MSI.organization_id = p_org_id --BUG 7598223
3356 AND (MSI.bom_item_type not in (1, 2) or MSI.replenish_to_order_flag <> 'Y') --BUG 7598223
3357 AND NVL(BRTG.cfm_routing_flag, 3) = 3
3358 AND BRTG.alternate_routing_designator IS NULL
3359 AND BRTG.common_routing_sequence_id = BOS.routing_sequence_id
3360 AND p_rtg_rev_date BETWEEN BOS.effectivity_date and
3361 nvl(BOS.disable_date, p_rtg_rev_date+1)
3362 AND NVL(BOS.operation_type, 1) = 1
3363 AND (bos.operation_sequence_id in
3364 (
3365 (SELECT bon.FROM_op_seq_id
3366 FROM BOM_OPERATION_NETWORKS_V BON
3367 WHERE bon.transition_type=1
3368 AND bon.routing_sequence_id=BRTG.common_routing_sequence_id
3369 )
3370 UNION ALL
3371 (SELECT bon.to_op_seq_id
3372 FROM BOM_OPERATION_NETWORKS_V BON
3373 WHERE bon.transition_type=1
3374 AND bon.routing_sequence_id =
3375 BRTG.common_routing_sequence_id
3376 )
3377 )
3378 )
3379 AND BOS.operation_sequence_id = BOR.operation_sequence_id
3380 AND BOS.department_id = BDR1.department_id
3381 AND BOR.resource_id = BDR1.resource_id
3382 AND BOR.resource_id = BR.resource_id
3383 ORDER BY BOS.operation_seq_num;
3384
3385 BEGIN
3386
3387 l_stmt_num := 270;
3388
3389 SELECT max(resource_seq_num)
3390 INTO l_curr_op_max_res_seq
3391 FROM WSM_COPY_OP_RESOURCES
3392 WHERE wip_entity_id = p_wip_entity_id
3393 AND organization_id = p_org_id
3394 AND operation_seq_num = l_curr_op_seq_num;
3395
3396 IF (l_curr_op_max_res_seq is null) THEN
3397 l_curr_op_max_res_seq := 0;
3398 END IF;
3399
3400 l_stmt_num := 280;
3401 FOR cur_resc IN phan_resc_cursor
3402 LOOP
3403 -- SET resource_seq_num to be unique
3404 l_curr_op_max_res_seq := l_curr_op_max_res_seq + 10;
3405
3406 l_stmt_num := 290;
3407
3408 -- insert phantom resources
3409 INSERT INTO WSM_COPY_OP_RESOURCES
3410 (WIP_ENTITY_ID,
3411 OPERATION_SEQ_NUM,
3412 RESOURCE_SEQ_NUM,
3413 ORGANIZATION_ID,
3414 SUBSTITUTE_GROUP_NUM,
3415 REPLACEMENT_GROUP_NUM,
3416 RECOMMENDED,
3417 RECO_START_DATE,
3418 RECO_COMPLETION_DATE,
3419 RESOURCE_ID,
3420 RESOURCE_CODE,
3421 DEPARTMENT_ID,
3422 PHANTOM_FLAG,
3423 PHANTOM_OP_SEQ_NUM,
3424 PHANTOM_ITEM_ID,
3425 ACTIVITY_ID,
3426 STANDARD_RATE_FLAG,
3427 ASSIGNED_UNITS,
3428 -- ST : Detailed Scheduling
3429 MAX_ASSIGNED_UNITS,
3430 FIRM_TYPE,
3431 -- ST :Detailed Scheduling
3432 USAGE_RATE_OR_AMOUNT,
3433 USAGE_RATE_OR_AMOUNT_INVERSE,
3434 UOM_CODE,
3435 BASIS_TYPE,
3436 SCHEDULE_FLAG,
3437 RESOURCE_OFFSET_PERCENT,
3438 AUTOCHARGE_TYPE,
3439 SCHEDULE_SEQ_NUM,
3440 PRINCIPLE_FLAG,
3441 SETUP_ID,
3442 CHANGE_NOTICE,
3443 ACD_TYPE,
3444 LAST_UPDATE_DATE,
3445 LAST_UPDATED_BY,
3446 LAST_UPDATE_LOGIN,
3447 CREATION_DATE,
3448 CREATED_BY,
3449 REQUEST_ID,
3450 PROGRAM_APPLICATION_ID,
3451 PROGRAM_ID,
3452 PROGRAM_UPDATE_DATE,
3453 ATTRIBUTE_CATEGORY,
3454 ATTRIBUTE1,
3455 ATTRIBUTE2,
3456 ATTRIBUTE3,
3457 ATTRIBUTE4,
3458 ATTRIBUTE5,
3459 ATTRIBUTE6,
3460 ATTRIBUTE7,
3461 ATTRIBUTE8,
3462 ATTRIBUTE9,
3463 ATTRIBUTE10,
3464 ATTRIBUTE11,
3465 ATTRIBUTE12,
3466 ATTRIBUTE13,
3467 ATTRIBUTE14,
3468 ATTRIBUTE15,
3469 ORIGINAL_SYSTEM_REFERENCE
3470 )
3471 VALUES
3472 (p_wip_entity_id,
3473 l_curr_op_seq_num,
3474 l_curr_op_max_res_seq,
3475 p_org_id,
3476 cur_resc.substitute_group_num,
3477 0, --REPLACEMENT_GROUP_NUM, since only primary resources
3478 --from phantom explosion are considered.
3479 cur_resc.RECOMMENDED,
3480 l_curr_op_start_date,
3481 l_curr_op_compl_date,
3482 cur_resc.resource_id,
3483 cur_resc.RESOURCE_CODE,
3484 cur_resc.department_id,
3485 1,
3486 cur_resc.phantom_op_seq_num,
3487 cur_resc.phantom_item_id,
3488 cur_resc.activity_id,
3489 cur_resc.standard_rate_flag,
3490 cur_resc.assigned_units,
3491 -- ST : Detailed Scheduling
3492 cur_resc.assigned_units,
3493 0, -- Not firmed
3494 -- ST : Detailed Scheduling
3495 cur_resc.usage_rate_or_amount,
3496 cur_resc.USAGE_RATE_OR_AMOUNT_INVERSE,
3497 cur_resc.unit_of_measure,
3498 cur_resc.basis_type,
3499 cur_resc.schedule_flag,
3500 cur_resc.RESOURCE_OFFSET_PERCENT,
3501 cur_resc.autocharge_type,
3502 cur_resc.schedule_seq_num,
3503 cur_resc.principle_flag,
3504 cur_resc.setup_id,
3505 cur_resc.CHANGE_NOTICE,
3506 cur_resc.ACD_TYPE,
3507 p_last_update_date,
3508 p_last_updated_by,
3509 p_last_update_login,
3510 p_creation_date,
3511 p_created_by,
3512 p_request_id,
3513 p_program_app_id,
3514 p_program_id,
3515 p_program_update_date,
3516 cur_resc.ATTRIBUTE_CATEGORY,
3517 cur_resc.ATTRIBUTE1,
3518 cur_resc.ATTRIBUTE2,
3519 cur_resc.ATTRIBUTE3,
3520 cur_resc.ATTRIBUTE4,
3521 cur_resc.ATTRIBUTE5,
3522 cur_resc.ATTRIBUTE6,
3523 cur_resc.ATTRIBUTE7,
3524 cur_resc.ATTRIBUTE8,
3525 cur_resc.ATTRIBUTE9,
3526 cur_resc.ATTRIBUTE10,
3527 cur_resc.ATTRIBUTE11,
3528 cur_resc.ATTRIBUTE12,
3529 cur_resc.ATTRIBUTE13,
3530 cur_resc.ATTRIBUTE14,
3531 cur_resc.ATTRIBUTE15,
3532 cur_resc.ORIGINAL_SYSTEM_REFERENCE
3533 );
3534
3535 IF (g_debug = 'Y') THEN
3536 fnd_file.put_line(fnd_file.log, 'Created '||SQL%ROWCOUNT||' records in WCOR (ph) for we_id='||p_wip_entity_id);
3537 END IF;
3538
3539 END LOOP;
3540 END;
3541 END IF; --if (l_use_phantom_routings = 1)
3542 ----------END : PHANTOM RESOURCES--------------------------
3543
3544
3545 t_comp_details.delete;
3546
3547 END LOOP;
3548 CLOSE job_ops;
3549
3550 <<SKIP_TILL_HERE>>
3551
3552 l_stmt_num := 300;
3553
3554 -- ***** Create a record in wsm_lot_based_jobs ***** --
3555 Get_Job_Curr_Op_Info(p_wip_entity_id => p_wip_entity_id,
3556 p_op_seq_num => l_curr_op_seq_num,
3557 p_op_seq_id => l_curr_op_seq_id,
3558 p_std_op_id => l_curr_op_std_op_id,
3559 p_dept_id => l_curr_op_dept_id,
3560 p_intra_op => l_curr_op_intra_op,
3561 p_op_qty => l_curr_op_qty,
3562 p_op_start_date => l_curr_op_start_date,
3563 p_op_comp_date => l_curr_op_compl_date,
3564 x_err_code => l_err_code,
3565 x_err_buf => l_err_buf);
3566
3567
3568
3569 IF (g_debug = 'Y') THEN
3570 fnd_file.put_line(fnd_file.log, 'Get_Job_Curr_Op_Info returned : '||
3571 'l_curr_op_seq_num ='||l_curr_op_seq_num ||
3572 ', l_curr_op_seq_id ='||l_curr_op_seq_id ||
3573 ', l_curr_op_std_op_id ='||l_curr_op_std_op_id ||
3574 ', l_curr_op_dept_id ='||l_curr_op_dept_id ||
3575 ', l_curr_op_intra_op ='||l_curr_op_intra_op ||
3576 ', l_curr_op_qty ='||l_curr_op_qty||
3577 ', l_err_code ='||l_err_code||
3578 ', l_err_buf ='||l_err_buf);
3579 END IF;
3580
3581 IF (l_err_code = 0) THEN
3582 l_stmt_num := 310;
3583
3584 BEGIN
3585 -- Start : Changes to fix bug 3452913 --
3586 SELECT recommended
3587 INTO l_is_curr_op_reco
3588 FROM wsm_copy_operations
3589 WHERE wip_entity_id = p_wip_entity_id
3590 AND operation_sequence_id = WSMPUTIL.replacement_copy_op_seq_id
3591 (l_curr_op_seq_id,
3592 p_wip_entity_id);
3593
3594 -- End : Changes to fix bug 3452913 --
3595
3596 EXCEPTION
3597 WHEN OTHERS THEN -- including WHEN NO_DATA_FOUND THEN
3598 l_is_curr_op_reco := 'N';
3599 END;
3600
3601
3602 IF (g_debug = 'Y') THEN
3603 fnd_file.put_line(fnd_file.log, 'At stmt('||l_stmt_num||') : l_is_curr_op_reco='||l_is_curr_op_reco);
3604 END IF;
3605
3606 ELSIF (l_err_code = -1) THEN -- No WO records
3607 -- Scenario : Job creation
3608 l_is_curr_op_reco := 'Y';
3609
3610 l_stmt_num := 315;
3611
3612 SELECT 0-operation_seq_num
3613 INTO l_curr_op_seq_num -- Storing this as -ve, since finally a +ve opseq to be sent
3614 FROM wsm_copy_operations
3615 WHERE wip_entity_id = p_wip_entity_id
3616 AND network_start_end = l_network_start; --'S'; --Fixed bug #3761385
3617
3618
3619 IF (g_debug = 'Y') THEN
3620 fnd_file.put_line(fnd_file.log, 'At stmt('||l_stmt_num||') : l_is_curr_op_reco='||l_is_curr_op_reco||
3621 ', and l_curr_op_seq_num='||l_curr_op_seq_num);
3622 END IF;
3623
3624 -- Start : Additions to fix bug #3677276
3625 ELSIF (l_err_code = -2) THEN -- Completed Job - where inf sch should not be called
3626 l_is_curr_op_reco := 'Y';
3627 l_inf_sch_flag := 'N';
3628 -- End : Additions to fix bug #3677276
3629
3630 ELSE -- Some other error
3631 x_err_code := l_err_code;
3632 x_err_buf := l_err_buf;
3633
3634 return;
3635 END IF;
3636
3637 l_stmt_num := 320;
3638 --R12:Serial Support:
3639 select SERIALIZATION_START_OP into l_serial_start_op
3640 from BOM_OPERATIONAL_ROUTINGS
3641 where ROUTING_SEQUENCE_ID = p_common_rtg_seq_id;
3642
3643 IF p_insert_wip = 1 THEN
3644 select nvl(OP_SEQ_NUM_INCREMENT, 10)
3645 into l_op_seq_incr
3646 from wsm_parameters
3647 where ORGANIZATION_ID = p_org_id;
3648 END IF;
3649
3650 INSERT into WSM_LOT_BASED_JOBS
3651 (WIP_ENTITY_ID,
3652 ORGANIZATION_ID,
3653 ON_REC_PATH,
3654 INTERNAL_COPY_TYPE,
3655 COPY_PARENT_WIP_ENTITY_ID,
3656 INFINITE_SCHEDULE,
3657 ROUTING_REFRESH_DATE,
3658 LAST_UPDATE_DATE,
3659 LAST_UPDATED_BY,
3660 LAST_UPDATE_LOGIN,
3661 CREATION_DATE,
3662 CREATED_BY,
3663 REQUEST_ID,
3664 PROGRAM_APPLICATION_ID,
3665 PROGRAM_ID,
3666 PROGRAM_UPDATE_DATE,
3667 SERIALIZATION_START_OP,
3668 --OPTII-PERF: MES Impact
3669 CURRENT_JOB_OP_SEQ_NUM,
3670 CURRENT_RTG_OP_SEQ_NUM,
3671 first_serial_txn_id -- ST : Added first_serial_txn_id in the above statement for bug fix 5171286
3672 )
3673 VALUES
3674 (p_wip_entity_id,
3675 p_org_id,
3676 l_is_curr_op_reco, -- ON_REC_PATH **OPEN ISSUE**
3677 0,
3678 NULL, -- COPY_PARENT_WIP_ENTITY_ID
3679 NULL, -- INFINITE_SCHEDULE
3680 SYSDATE, --ROUTING_REFRESH_DATE
3681 p_last_update_date,
3682 p_last_updated_by,
3683 p_last_update_login,
3684 p_creation_date,
3685 p_created_by,
3686 p_request_id,
3687 p_program_app_id,
3688 p_program_id,
3689 p_program_update_date,
3690 l_serial_start_op,
3691 --OPTII-PERF: MES Impact
3692 decode(p_insert_wip,1,l_op_seq_incr,l_curr_job_op_seq_num),
3693 decode(p_insert_wip,1,-1*l_curr_op_seq_num,l_curr_rtg_op_seq_num),
3694 l_first_serial_txn_id -- ST : Added first_serial_txn_id in the above statement for bug fix 5171286
3695 );
3696
3697 IF (SQL%ROWCOUNT = 0) THEN
3698 -- No record created in WSM_LOT_BASED_JOBS
3699 fnd_message.set_name('WSM', 'WSM_NO_WLBJ_REC');
3700 x_err_buf := fnd_message.get;
3701 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||'): '||x_err_buf);
3702 x_err_code := -2; -- Error
3703
3704 return;
3705 ELSE
3706 x_err_code := 0;
3707 x_err_buf := NULL;
3708 END IF;
3709
3710
3711 IF (g_debug = 'Y') THEN
3712 fnd_file.put_line(fnd_file.log, 'Created '||SQL%ROWCOUNT||' records in WLBJ for we_id='||p_wip_entity_id);
3713 END IF;
3714
3715 l_stmt_num := 330;
3716
3717 IF (l_inf_sch_flag = 'Y') THEN
3718
3719 IF (p_inf_sch_mode IN (WIP_CONSTANTS.FORWARDS,
3720 WIP_CONSTANTS.MIDPOINT_FORWARDS,
3721 WIP_CONSTANTS.CURRENT_OP)
3722 )
3723 THEN
3724 l_inf_sch_start_date := nvl(p_inf_sch_date, nvl(l_curr_op_start_date, sysdate));
3725 l_inf_sch_comp_date := NULL;
3726 ELSIF (p_inf_sch_mode IN (WIP_CONSTANTS.BACKWARDS,
3727 WIP_CONSTANTS.MIDPOINT_BACKWARDS)
3728 )
3729 THEN
3730 l_inf_sch_start_date := NULL;
3731 l_inf_sch_comp_date := nvl(p_inf_sch_date, nvl(l_curr_op_compl_date, sysdate));
3732 ELSIF (p_inf_sch_mode IS NULL) THEN
3733
3734 IF (l_is_curr_op_reco = 'Y') THEN
3735 -- ST : Fix for bug 5181364 : do forward scheduling instead of midpoints_forward scheduling.
3736 -- l_inf_sch_mode := WIP_CONSTANTS.MIDPOINT_FORWARDS;
3737 l_inf_sch_mode := WIP_CONSTANTS.FORWARDS;
3738 ELSE
3739 l_inf_sch_mode := WIP_CONSTANTS.CURRENT_OP;
3740 END IF;
3741
3742 l_inf_sch_start_date := nvl(p_inf_sch_date, nvl(l_curr_op_start_date, sysdate));
3743 l_inf_sch_comp_date := NULL;
3744 END IF;
3745
3746 l_stmt_num := 340;
3747
3748
3749 WSM_infinite_scheduler_PVT.schedule
3750 (
3751 p_initMsgList => FND_API.g_true,
3752 p_endDebug => FND_API.g_true,
3753 p_orgID => p_org_id,
3754 p_wipEntityID => p_wip_entity_id,
3755 p_scheduleMode => nvl(p_inf_sch_mode, l_inf_sch_mode),
3756 p_startDate => l_inf_sch_start_date,
3757 p_endDate => l_inf_sch_comp_date,
3758 p_opSeqNum => 0-l_curr_op_seq_num,
3759 p_resSeqNum => NULL,
3760 x_returnStatus => l_inf_sch_returnStatus,
3761 x_errorMsg => l_err_buf,
3762 p_new_job => p_new_job,
3763 p_charges_exist => p_charges_exist
3764 );
3765
3766 IF (g_debug = 'Y') THEN
3767 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||
3768 '): WSM_infinite_scheduler_PVT.schedule returned '||l_inf_sch_returnStatus);
3769 END IF;
3770
3771 -- Start : We do not need to error if Inf Sch fails, since dates will be calculated while moving --
3772 IF(l_inf_sch_returnStatus <> FND_API.G_RET_STS_SUCCESS) THEN
3773
3774 fnd_file.put_line(fnd_file.log, 'Warning : Could not infinite schedule the job successfully');
3775 fnd_file.put_line(fnd_file.log, l_err_buf);
3776 x_err_code := -1; --Warning
3777 x_err_buf := l_err_buf;
3778 END IF;
3779 -- End : We do not need to error if Inf Sch fails,
3780 -- since dates will be calculated while moving
3781
3782 END IF;
3783
3784 l_stmt_num := 350;
3785 IF p_insert_wip = 1 THEN
3786 process_wip_info( p_wip_entity_id ,
3787 p_org_id ,
3788 p_last_update_date ,
3789 p_last_updated_by ,
3790 p_last_update_login ,
3791 p_creation_date ,
3792 p_created_by ,
3793 p_request_id ,
3794 p_program_app_id ,
3795 p_program_id ,
3796 p_program_update_date ,
3797 p_phantom_exists,
3798 l_curr_op_seq_num,
3799 x_err_code,
3800 x_err_buf);
3801
3802 END IF;
3803
3804
3805 EXCEPTION
3806 WHEN be_exploder_exception THEN
3807 x_err_buf := 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||') : '||x_err_buf;
3808 fnd_file.put_line(fnd_file.log, x_err_buf);
3809
3810 WHEN loop_in_bom_exception THEN
3811 x_err_buf := 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||') : '||x_err_buf;
3812 fnd_file.put_line(fnd_file.log, x_err_buf);
3813
3814 WHEN e_proc_error THEN
3815 x_err_buf := 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||') : '||x_err_buf;
3816 fnd_file.put_line(fnd_file.log, x_err_buf);
3817
3818 --Bug 4264364:Added exception handling
3819 WHEN e_noneff_op THEN
3820 x_err_buf := 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||') : '||x_err_buf;
3821 fnd_file.put_line(fnd_file.log, x_err_buf);
3822
3823 WHEN others THEN
3824 x_err_code := SQLCODE;
3825 x_err_buf := 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
3826 fnd_file.put_line(fnd_file.log, x_err_buf);
3827
3828 END Create_JobCopies;
3829
3830
3831
3832 /*****************************
3833 ** **
3834 ** Create_RepJobCopies **
3835 ** **
3836 *****************************/
3837
3838
3839 PROCEDURE Create_RepJobCopies (x_err_buf OUT NOCOPY VARCHAR2,
3840 x_err_code OUT NOCOPY NUMBER,
3841 p_rep_wip_entity_id IN NUMBER,
3842 p_new_wip_entity_id IN NUMBER,
3843 p_last_update_date IN DATE,
3844 p_last_updated_by IN NUMBER,
3845 p_last_update_login IN NUMBER,
3846 p_creation_date IN DATE,
3847 p_created_by IN NUMBER,
3848 p_request_id IN NUMBER,
3849 p_program_app_id IN NUMBER,
3850 p_program_id IN NUMBER,
3851 p_program_update_date IN DATE,
3852 p_inf_sch_flag IN VARCHAR2,--Y/N
3853 p_inf_sch_mode IN NUMBER, --NULL/MIDPOINT_FORWARDS/CURRENT_OP
3854 p_inf_sch_date IN DATE --based on mode, this will be start/completion date
3855 )
3856 IS
3857 l_stmt_num NUMBER := 0;
3858
3859 l_inf_sch_mode NUMBER;
3860 l_inf_sch_start_date DATE;
3861 l_inf_sch_comp_date DATE;
3862
3863 l_on_rec_path VARCHAR2(1);
3864 l_org_id NUMBER;
3865
3866 l_curr_op_start_date DATE;
3867 l_curr_op_compl_date DATE;
3868 l_curr_op_seq_num NUMBER;
3869 l_curr_op_seq_id NUMBER;
3870 l_curr_op_std_op_id NUMBER;
3871 l_curr_op_dept_id NUMBER;
3872 l_curr_op_intra_op NUMBER;
3873 l_curr_op_qty NUMBER;
3874
3875 l_inf_sch_returnStatus VARCHAR2(1);
3876
3877 e_proc_error EXCEPTION;
3878
3879 BEGIN
3880
3881 g_debug := FND_PROFILE.VALUE('MRP_DEBUG');
3882
3883 IF (g_debug = 'Y') THEN
3884 fnd_file.put_line(fnd_file.log, 'Parameters to Create_RepJobCopies are :\\n');
3885 fnd_file.put_line(fnd_file.log, ' p_rep_wip_entity_id ='||p_rep_wip_entity_id );
3886 fnd_file.put_line(fnd_file.log, ', p_new_wip_entity_id ='||p_new_wip_entity_id );
3887 fnd_file.put_line(fnd_file.log, ', p_last_update_date ='||p_last_update_date );
3888 fnd_file.put_line(fnd_file.log, ', p_last_updated_by ='||p_last_updated_by );
3889 fnd_file.put_line(fnd_file.log, ', p_last_update_login ='||p_last_update_login );
3890 fnd_file.put_line(fnd_file.log, ', p_creation_date ='||p_creation_date );
3891 fnd_file.put_line(fnd_file.log, ', p_created_by ='||p_created_by );
3892 fnd_file.put_line(fnd_file.log, ', p_request_id ='||p_request_id );
3893 fnd_file.put_line(fnd_file.log, ', p_program_app_id ='||p_program_app_id );
3894 fnd_file.put_line(fnd_file.log, ', p_program_id ='||p_program_id );
3895 fnd_file.put_line(fnd_file.log, ', p_program_update_date ='||p_program_update_date );
3896 fnd_file.put_line(fnd_file.log, ', p_inf_sch_flag ='||p_inf_sch_flag );
3897 fnd_file.put_line(fnd_file.log, ', p_inf_sch_mode ='||p_inf_sch_mode );
3898 fnd_file.put_line(fnd_file.log, ', p_inf_sch_date ='||p_inf_sch_date );
3899 END IF;
3900
3901
3902
3903
3904 IF (g_debug = 'Y') THEN
3905 fnd_file.put_line(fnd_file.log, 'In Create_RepJobCopies: Rep. we_id ='||p_rep_wip_entity_id||
3906 ', new we_id ='||p_new_wip_entity_id);
3907 END IF;
3908
3909 l_stmt_num := 10;
3910
3911 IF ( (p_inf_sch_flag = 'Y')
3912 AND (p_inf_sch_mode NOT IN (WIP_CONSTANTS.MIDPOINT_FORWARDS,
3913 WIP_CONSTANTS.CURRENT_OP)
3914 OR p_inf_sch_mode IS NOT NULL
3915 )
3916 )
3917 THEN
3918 x_err_code := -1;
3919 x_err_buf := 'WSM_JobCopies_PVT.Create_JobCopies('||l_stmt_num||'): Invalid Infinite Scheduling Mode';
3920 fnd_file.put_line(fnd_file.log, x_err_buf);
3921 return;
3922 END IF;
3923
3924 -- The following deletes will be needed, in case the API is to be made public
3925 --OPTII-PERF:Following deletes are not needed as this will be called for
3926 --only new jobs.
3927 /*
3928 DELETE WSM_COPY_OP_NETWORKS
3929 WHERE wip_entity_id = p_new_wip_entity_id;
3930
3931 DELETE WSM_COPY_OPERATIONS
3932 WHERE wip_entity_id = p_new_wip_entity_id;
3933
3934 DELETE WSM_COPY_OP_RESOURCES
3935 WHERE wip_entity_id = p_new_wip_entity_id;
3936
3937 DELETE WSM_COPY_OP_RESOURCE_INSTANCES
3938 WHERE wip_entity_id = p_new_wip_entity_id;
3939
3940 DELETE WSM_COPY_OP_RESOURCE_USAGE
3941 WHERE wip_entity_id = p_new_wip_entity_id;
3942
3943 DELETE WSM_COPY_REQUIREMENT_OPS
3944 WHERE wip_entity_id = p_new_wip_entity_id;
3945 */
3946 DELETE WSM_LOT_BASED_JOBS
3947 WHERE wip_entity_id = p_new_wip_entity_id;
3948
3949 -- ***** Make a copy of the Network for the job ***** --
3950 l_stmt_num := 20;
3951 INSERT into WSM_COPY_OP_NETWORKS
3952 (WIP_ENTITY_ID,
3953 FROM_OP_SEQ_NUM,
3954 TO_OP_SEQ_NUM,
3955 FROM_OP_SEQ_ID,
3956 TO_OP_SEQ_ID,
3957 RECOMMENDED,
3958 ROUTING_SEQUENCE_ID,
3959 TRANSITION_TYPE,
3960 PLANNING_PCT,
3961 LAST_UPDATE_DATE,
3962 LAST_UPDATED_BY,
3963 LAST_UPDATE_LOGIN,
3964 CREATION_DATE,
3965 CREATED_BY,
3966 REQUEST_ID,
3967 PROGRAM_APPLICATION_ID,
3968 PROGRAM_ID,
3969 PROGRAM_UPDATE_DATE,
3970 ATTRIBUTE_CATEGORY,
3971 ATTRIBUTE1,
3972 ATTRIBUTE2,
3973 ATTRIBUTE3,
3974 ATTRIBUTE4,
3975 ATTRIBUTE5,
3976 ATTRIBUTE6,
3977 ATTRIBUTE7,
3978 ATTRIBUTE8,
3979 ATTRIBUTE9,
3980 ATTRIBUTE10,
3981 ATTRIBUTE11,
3982 ATTRIBUTE12,
3983 ATTRIBUTE13,
3984 ATTRIBUTE14,
3985 ATTRIBUTE15,
3986 ORIGINAL_SYSTEM_REFERENCE
3987 )
3988 SELECT
3989 p_new_wip_entity_id,
3990 FROM_OP_SEQ_NUM,
3991 TO_OP_SEQ_NUM,
3992 FROM_OP_SEQ_ID,
3993 TO_OP_SEQ_ID,
3994 RECOMMENDED,
3995 ROUTING_SEQUENCE_ID,
3996 TRANSITION_TYPE,
3997 PLANNING_PCT,
3998 p_last_update_date,
3999 p_last_updated_by,
4000 p_last_update_login,
4001 p_creation_date,
4002 p_created_by,
4003 p_request_id,
4004 p_program_app_id,
4005 p_program_id,
4006 p_program_update_date,
4007 ATTRIBUTE_CATEGORY,
4008 ATTRIBUTE1,
4009 ATTRIBUTE2,
4010 ATTRIBUTE3,
4011 ATTRIBUTE4,
4012 ATTRIBUTE5,
4013 ATTRIBUTE6,
4014 ATTRIBUTE7,
4015 ATTRIBUTE8,
4016 ATTRIBUTE9,
4017 ATTRIBUTE10,
4018 ATTRIBUTE11,
4019 ATTRIBUTE12,
4020 ATTRIBUTE13,
4021 ATTRIBUTE14,
4022 ATTRIBUTE15,
4023 ORIGINAL_SYSTEM_REFERENCE
4024 FROM WSM_COPY_OP_NETWORKS
4025 WHERE wip_entity_id = p_rep_wip_entity_id;
4026
4027
4028
4029 IF (g_debug = 'Y') THEN
4030 fnd_file.put_line(fnd_file.log, 'Created '||SQL%ROWCOUNT||' records in WCON for we_id='||p_new_wip_entity_id);
4031 END IF;
4032
4033 -- ***** Make a copy of the Routing for the job ***** --
4034 l_stmt_num := 30;
4035 INSERT INTO WSM_COPY_OPERATIONS
4036 (WIP_ENTITY_ID,
4037 OPERATION_SEQ_NUM,
4038 RECOMMENDED,
4039 RECO_PATH_SEQ_NUM,
4040 NETWORK_START_END,
4041 RECO_SCHEDULED_QUANTITY,
4042 RECO_START_DATE,
4043 RECO_COMPLETION_DATE,
4044 OPERATION_SEQUENCE_ID,
4045 ROUTING_SEQUENCE_ID,
4046 ORGANIZATION_ID,
4047 STANDARD_OPERATION_ID,
4048 STANDARD_OPERATION_CODE,
4049 DEPARTMENT_ID,
4050 DEPARTMENT_CODE,
4051 SCRAP_ACCOUNT,
4052 EST_ABSORPTION_ACCOUNT,
4053 OPERATION_LEAD_TIME_PERCENT,
4054 MINIMUM_TRANSFER_QUANTITY,
4055 COUNT_POINT_TYPE,
4056 OPERATION_DESCRIPTION,
4057 EFFECTIVITY_DATE,
4058 DISABLE_DATE,
4059 BACKFLUSH_FLAG,
4060 OPTION_DEPENDENT_FLAG,
4061 OPERATION_TYPE,
4062 REFERENCE_FLAG,
4063 YIELD,
4064 CUMULATIVE_YIELD,
4065 REVERSE_CUMULATIVE_YIELD,
4066 LABOR_TIME_CALC,
4067 MACHINE_TIME_CALC,
4068 TOTAL_TIME_CALC,
4069 LABOR_TIME_USER,
4070 MACHINE_TIME_USER,
4071 TOTAL_TIME_USER,
4072 NET_PLANNING_PERCENT,
4073 X_COORDINATE,
4074 Y_COORDINATE,
4075 INCLUDE_IN_ROLLUP,
4076 OPERATION_YIELD_ENABLED,
4077 OLD_OPERATION_SEQUENCE_ID,
4078 ACD_TYPE,
4079 REVISED_ITEM_SEQUENCE_ID,
4080 CHANGE_NOTICE,
4081 ECO_FOR_PRODUCTION,
4082 SHUTDOWN_TYPE,
4083 ACTUAL_IPK,
4084 CRITICAL_TO_QUALITY,
4085 VALUE_ADDED,
4086 LAST_UPDATE_DATE,
4087 LAST_UPDATED_BY,
4088 LAST_UPDATE_LOGIN,
4089 CREATION_DATE,
4090 CREATED_BY,
4091 REQUEST_ID,
4092 PROGRAM_APPLICATION_ID,
4093 PROGRAM_ID,
4094 PROGRAM_UPDATE_DATE,
4095 ATTRIBUTE_CATEGORY,
4096 ATTRIBUTE1,
4097 ATTRIBUTE2,
4098 ATTRIBUTE3,
4099 ATTRIBUTE4,
4100 ATTRIBUTE5,
4101 ATTRIBUTE6,
4102 ATTRIBUTE7,
4103 ATTRIBUTE8,
4104 ATTRIBUTE9,
4105 ATTRIBUTE10,
4106 ATTRIBUTE11,
4107 ATTRIBUTE12,
4108 ATTRIBUTE13,
4109 ATTRIBUTE14,
4110 ATTRIBUTE15,
4111 ORIGINAL_SYSTEM_REFERENCE,
4112 LOWEST_ACCEPTABLE_YIELD --mes
4113 )
4114 SELECT
4115 p_new_wip_entity_id,
4116 OPERATION_SEQ_NUM,
4117 RECOMMENDED,
4118 RECO_PATH_SEQ_NUM,
4119 NETWORK_START_END,
4120 NULL, --RECO_SCHEDULED_QUANTITY,
4121 NULL, --RECO_START_DATE,
4122 NULL, --RECO_COMPLETION_DATE,
4123 OPERATION_SEQUENCE_ID,
4124 ROUTING_SEQUENCE_ID,
4125 ORGANIZATION_ID,
4126 STANDARD_OPERATION_ID,
4127 STANDARD_OPERATION_CODE,
4128 DEPARTMENT_ID,
4129 DEPARTMENT_CODE,
4130 SCRAP_ACCOUNT,
4131 EST_ABSORPTION_ACCOUNT,
4132 OPERATION_LEAD_TIME_PERCENT,
4133 MINIMUM_TRANSFER_QUANTITY,
4134 COUNT_POINT_TYPE,
4135 OPERATION_DESCRIPTION,
4136 EFFECTIVITY_DATE,
4137 DISABLE_DATE,
4138 BACKFLUSH_FLAG,
4139 OPTION_DEPENDENT_FLAG,
4140 OPERATION_TYPE,
4141 REFERENCE_FLAG,
4142 YIELD,
4143 CUMULATIVE_YIELD,
4144 REVERSE_CUMULATIVE_YIELD,
4145 LABOR_TIME_CALC,
4146 MACHINE_TIME_CALC,
4147 TOTAL_TIME_CALC,
4148 LABOR_TIME_USER,
4149 MACHINE_TIME_USER,
4150 TOTAL_TIME_USER,
4151 NET_PLANNING_PERCENT,
4152 X_COORDINATE,
4153 Y_COORDINATE,
4154 INCLUDE_IN_ROLLUP,
4155 OPERATION_YIELD_ENABLED,
4156 OLD_OPERATION_SEQUENCE_ID,
4157 ACD_TYPE,
4158 REVISED_ITEM_SEQUENCE_ID,
4159 CHANGE_NOTICE,
4160 ECO_FOR_PRODUCTION,
4161 SHUTDOWN_TYPE,
4162 ACTUAL_IPK,
4163 CRITICAL_TO_QUALITY,
4164 VALUE_ADDED,
4165 p_last_update_date,
4166 p_last_updated_by,
4167 p_last_update_login,
4168 p_creation_date,
4169 p_created_by,
4170 p_request_id,
4171 p_program_app_id,
4172 p_program_id,
4173 p_program_update_date,
4174 ATTRIBUTE_CATEGORY,
4175 ATTRIBUTE1,
4176 ATTRIBUTE2,
4177 ATTRIBUTE3,
4178 ATTRIBUTE4,
4179 ATTRIBUTE5,
4180 ATTRIBUTE6,
4181 ATTRIBUTE7,
4182 ATTRIBUTE8,
4183 ATTRIBUTE9,
4184 ATTRIBUTE10,
4185 ATTRIBUTE11,
4186 ATTRIBUTE12,
4187 ATTRIBUTE13,
4188 ATTRIBUTE14,
4189 ATTRIBUTE15,
4190 ORIGINAL_SYSTEM_REFERENCE,
4191 LOWEST_ACCEPTABLE_YIELD
4192 FROM WSM_COPY_OPERATIONS
4193 WHERE wip_entity_id = p_rep_wip_entity_id;
4194
4195
4196
4197 IF (g_debug = 'Y') THEN
4198 fnd_file.put_line(fnd_file.log, 'Created '||SQL%ROWCOUNT||' records in WCO for we_id='||p_new_wip_entity_id);
4199 END IF;
4200
4201 -- ***** Make a copy of the primary Resources for the job ***** --
4202 l_stmt_num := 40;
4203
4204 /* ST : Detailed Scheduling change for firm_type
4205 NOT FIRM - 0
4206 FIRM_START - 1,
4207 FIRM_END - 2,
4208 FIRM_RESOURCE ?3 ,
4209 FIRM_START_END- 4,
4210 FIRM_START_RESOURCE - 5,
4211 FIRM_END_RESOURCE - 6,
4212 FIRM_ALL 7
4213
4214 Original Firm Flag Value New Firm Flag Value
4215
4216 Firm Start - Null
4217 Firm End - Null
4218 Firm Resource . - Firm Resource
4219 Firm Start and End - Null
4220 Firm Start and Resource - Firm Resource
4221 Firm End and Resource - Firm Resource
4222 Firm All - Firm Resource
4223 */
4224
4225 INSERT INTO WSM_COPY_OP_RESOURCES
4226 (WIP_ENTITY_ID,
4227 OPERATION_SEQ_NUM,
4228 RESOURCE_SEQ_NUM,
4229 ORGANIZATION_ID,
4230 SUBSTITUTE_GROUP_NUM,
4231 REPLACEMENT_GROUP_NUM,
4232 RECOMMENDED,
4233 RECO_START_DATE,
4234 RECO_COMPLETION_DATE,
4235 RESOURCE_ID,
4236 RESOURCE_CODE,
4237 ACTIVITY_ID,
4238 STANDARD_RATE_FLAG,
4239 ASSIGNED_UNITS,
4240 -- ST : Detailed Scheduling
4241 MAX_ASSIGNED_UNITS,
4242 firm_type,
4243 batch_id,
4244 group_sequence_id,
4245 group_sequence_num,
4246 parent_resource_seq_num,
4247 -- ST : Detailed Scheduling
4248 USAGE_RATE_OR_AMOUNT,
4249 USAGE_RATE_OR_AMOUNT_INVERSE,
4250 UOM_CODE,
4251 BASIS_TYPE,
4252 SCHEDULE_FLAG,
4253 RESOURCE_OFFSET_PERCENT,
4254 AUTOCHARGE_TYPE,
4255 SCHEDULE_SEQ_NUM,
4256 PRINCIPLE_FLAG,
4257 SETUP_ID,
4258 CHANGE_NOTICE,
4259 ACD_TYPE,
4260 DEPARTMENT_ID,
4261 PHANTOM_FLAG,
4262 PHANTOM_OP_SEQ_NUM,
4263 PHANTOM_ITEM_ID,
4264 LAST_UPDATE_DATE,
4265 LAST_UPDATED_BY,
4266 LAST_UPDATE_LOGIN,
4267 CREATION_DATE,
4268 CREATED_BY,
4269 REQUEST_ID,
4270 PROGRAM_APPLICATION_ID,
4271 PROGRAM_ID,
4272 PROGRAM_UPDATE_DATE,
4273 ATTRIBUTE_CATEGORY,
4274 ATTRIBUTE1,
4275 ATTRIBUTE2,
4276 ATTRIBUTE3,
4277 ATTRIBUTE4,
4278 ATTRIBUTE5,
4279 ATTRIBUTE6,
4280 ATTRIBUTE7,
4281 ATTRIBUTE8,
4282 ATTRIBUTE9,
4283 ATTRIBUTE10,
4284 ATTRIBUTE11,
4285 ATTRIBUTE12,
4286 ATTRIBUTE13,
4287 ATTRIBUTE14,
4288 ATTRIBUTE15,
4289 ORIGINAL_SYSTEM_REFERENCE
4290 )
4291 SELECT
4292 p_new_wip_entity_id,
4293 OPERATION_SEQ_NUM,
4294 RESOURCE_SEQ_NUM,
4295 ORGANIZATION_ID,
4296 SUBSTITUTE_GROUP_NUM,
4297 REPLACEMENT_GROUP_NUM,
4298 RECOMMENDED,
4299 NULL, --RECO_START_DATE,
4300 NULL, --RECO_COMPLETION_DATE,
4301 RESOURCE_ID,
4302 RESOURCE_CODE,
4303 ACTIVITY_ID,
4304 STANDARD_RATE_FLAG,
4305 ASSIGNED_UNITS,
4306 -- ST : Detailed Scheduling
4307 MAX_ASSIGNED_UNITS,
4308 decode(firm_type
4309 ,1, 0
4310 ,2, 0
4311 ,3, 3
4312 ,4, 0
4313 ,5, 3
4314 ,6, 3
4315 ,7, 3
4316 ,0),
4317 batch_id,
4318 group_sequence_id,
4319 group_sequence_num,
4320 parent_resource_seq_num,
4321 -- ST : Detailed Scheduling
4322 USAGE_RATE_OR_AMOUNT,
4323 USAGE_RATE_OR_AMOUNT_INVERSE,
4324 UOM_CODE,
4325 BASIS_TYPE,
4326 SCHEDULE_FLAG,
4327 RESOURCE_OFFSET_PERCENT,
4328 AUTOCHARGE_TYPE,
4329 SCHEDULE_SEQ_NUM,
4330 PRINCIPLE_FLAG,
4331 SETUP_ID,
4332 CHANGE_NOTICE,
4333 ACD_TYPE,
4334 DEPARTMENT_ID,
4335 PHANTOM_FLAG,
4336 PHANTOM_OP_SEQ_NUM,
4337 PHANTOM_ITEM_ID,
4338 p_last_update_date,
4339 p_last_updated_by,
4340 p_last_update_login,
4341 p_creation_date,
4342 p_created_by,
4343 p_request_id,
4344 p_program_app_id,
4345 p_program_id,
4346 p_program_update_date,
4347 ATTRIBUTE_CATEGORY,
4348 ATTRIBUTE1,
4349 ATTRIBUTE2,
4350 ATTRIBUTE3,
4351 ATTRIBUTE4,
4352 ATTRIBUTE5,
4353 ATTRIBUTE6,
4354 ATTRIBUTE7,
4355 ATTRIBUTE8,
4356 ATTRIBUTE9,
4357 ATTRIBUTE10,
4358 ATTRIBUTE11,
4359 ATTRIBUTE12,
4360 ATTRIBUTE13,
4361 ATTRIBUTE14,
4362 ATTRIBUTE15,
4363 ORIGINAL_SYSTEM_REFERENCE
4364 FROM WSM_COPY_OP_RESOURCES
4365 WHERE wip_entity_id = p_rep_wip_entity_id;
4366
4367
4368
4369 IF (g_debug = 'Y') THEN
4370 fnd_file.put_line(fnd_file.log, 'Created '||SQL%ROWCOUNT||' records in WCOR for we_id='||p_new_wip_entity_id);
4371 END IF;
4372
4373 /* ST : Detailed Scheduling */
4374
4375 /***** Make a copy of the Instances Resources for the job *****/
4376
4377 l_stmt_num := 40;
4378 INSERT INTO WSM_COPY_OP_RESOURCE_INSTANCES
4379 (WIP_ENTITY_ID,
4380 OPERATION_SEQ_NUM,
4381 RESOURCE_SEQ_NUM,
4382 ORGANIZATION_ID,
4383 LAST_UPDATE_DATE,
4384 LAST_UPDATED_BY ,
4385 CREATION_DATE ,
4386 CREATED_BY ,
4387 LAST_UPDATE_LOGIN,
4388 INSTANCE_ID ,
4389 SERIAL_NUMBER,
4390 START_DATE ,
4391 COMPLETION_DATE,
4392 BATCH_ID
4393 )
4394 SELECT
4395 p_new_wip_entity_id,
4396 OPERATION_SEQ_NUM,
4397 RESOURCE_SEQ_NUM,
4398 ORGANIZATION_ID,
4399 LAST_UPDATE_DATE,
4400 LAST_UPDATED_BY,
4401 CREATION_DATE,
4402 CREATED_BY,
4403 LAST_UPDATE_LOGIN,
4404 INSTANCE_ID,
4405 SERIAL_NUMBER,
4406 start_date, -- START_DATE
4407 completion_date, --COMPLETION_DATE
4408 BATCH_ID
4409 FROM WSM_COPY_OP_RESOURCE_INSTANCES
4410 WHERE wip_entity_id = p_rep_wip_entity_id;
4411
4412 IF (g_debug = 'Y') THEN
4413 fnd_file.put_line(fnd_file.log, 'Created '||SQL%ROWCOUNT||' records in WCORI for we_id='||p_new_wip_entity_id);
4414 END IF;
4415
4416 /* ST : Detailed Scheduling end */
4417
4418 -- ***** Make a copy of the Bill (primary and substitute components) for the job ***** --
4419
4420 l_stmt_num := 50;
4421
4422 INSERT INTO WSM_COPY_REQUIREMENT_OPS
4423 (WIP_ENTITY_ID,
4424 OPERATION_SEQ_NUM,
4425 COMPONENT_ITEM_ID,
4426 PRIMARY_COMPONENT_ID,
4427 COMPONENT_SEQUENCE_ID,
4428 SOURCE_PHANTOM_ID,
4429 RECOMMENDED,
4430 RECO_DATE_REQUIRED,
4431 BILL_SEQUENCE_ID,
4432 DEPARTMENT_ID,
4433 ORGANIZATION_ID,
4434 WIP_SUPPLY_TYPE,
4435 SUPPLY_SUBINVENTORY,
4436 SUPPLY_LOCATOR_ID,
4437 QUANTITY_PER_ASSEMBLY,
4438 BILL_QUANTITY_PER_ASSEMBLY,
4439 COMPONENT_YIELD_FACTOR,
4440 BASIS_TYPE, --LBM enh
4441 EFFECTIVITY_DATE,
4442 DISABLE_DATE,
4443 COMPONENT_PRIORITY,
4444 PARENT_BILL_SEQ_ID,
4445 ITEM_NUM,
4446 COMPONENT_REMARKS,
4447 CHANGE_NOTICE,
4448 IMPLEMENTATION_DATE,
4449 PLANNING_FACTOR,
4450 QUANTITY_RELATED,
4451 SO_BASIS,
4452 OPTIONAL,
4453 MUTUALLY_EXCLUSIVE_OPTIONS,
4454 INCLUDE_IN_COST_ROLLUP,
4455 CHECK_ATP,
4456 SHIPPING_ALLOWED,
4457 REQUIRED_TO_SHIP,
4458 REQUIRED_FOR_REVENUE,
4459 INCLUDE_ON_SHIP_DOCS,
4460 LOW_QUANTITY,
4461 HIGH_QUANTITY,
4462 ACD_TYPE,
4463 OLD_COMPONENT_SEQUENCE_ID,
4464 OPERATION_LEAD_TIME_PERCENT,
4465 REVISED_ITEM_SEQUENCE_ID,
4466 BOM_ITEM_TYPE,
4467 FROM_END_ITEM_UNIT_NUMBER,
4468 TO_END_ITEM_UNIT_NUMBER,
4469 ECO_FOR_PRODUCTION,
4470 ENFORCE_INT_REQUIREMENTS,
4471 DELETE_GROUP_NAME,
4472 DG_DESCRIPTION,
4473 OPTIONAL_ON_MODEL,
4474 MODEL_COMP_SEQ_ID,
4475 PLAN_LEVEL,
4476 AUTO_REQUEST_MATERIAL,
4477 COMPONENT_ITEM_REVISION_ID,
4478 FROM_BILL_REVISION_ID,
4479 TO_BILL_REVISION_ID,
4480 PICK_COMPONENTS,
4481 INCLUDE_ON_BILL_DOCS,
4482 COST_FACTOR,
4483 LAST_UPDATE_DATE,
4484 LAST_UPDATED_BY,
4485 LAST_UPDATE_LOGIN,
4486 CREATION_DATE,
4487 CREATED_BY,
4488 REQUEST_ID,
4489 PROGRAM_APPLICATION_ID,
4490 PROGRAM_ID,
4491 PROGRAM_UPDATE_DATE,
4492 ATTRIBUTE_CATEGORY,
4493 ATTRIBUTE1,
4494 ATTRIBUTE2,
4495 ATTRIBUTE3,
4496 ATTRIBUTE4,
4497 ATTRIBUTE5,
4498 ATTRIBUTE6,
4499 ATTRIBUTE7,
4500 ATTRIBUTE8,
4501 ATTRIBUTE9,
4502 ATTRIBUTE10,
4503 ATTRIBUTE11,
4504 ATTRIBUTE12,
4505 ATTRIBUTE13,
4506 ATTRIBUTE14,
4507 ATTRIBUTE15,
4508 ORIGINAL_SYSTEM_REFERENCE
4509 )
4510 SELECT
4511 p_new_wip_entity_id,
4512 OPERATION_SEQ_NUM,
4513 COMPONENT_ITEM_ID,
4514 PRIMARY_COMPONENT_ID,
4515 COMPONENT_SEQUENCE_ID,
4516 SOURCE_PHANTOM_ID,
4517 RECOMMENDED,
4518 NULL, --RECO_DATE_REQUIRED,
4519 BILL_SEQUENCE_ID,
4520 DEPARTMENT_ID,
4521 ORGANIZATION_ID,
4522 WIP_SUPPLY_TYPE,
4523 SUPPLY_SUBINVENTORY,
4524 SUPPLY_LOCATOR_ID,
4525 QUANTITY_PER_ASSEMBLY,
4526 BILL_QUANTITY_PER_ASSEMBLY,
4527 COMPONENT_YIELD_FACTOR,
4528 BASIS_TYPE, --LBM enh
4529 EFFECTIVITY_DATE,
4530 DISABLE_DATE,
4531 COMPONENT_PRIORITY,
4532 PARENT_BILL_SEQ_ID,
4533 ITEM_NUM,
4534 COMPONENT_REMARKS,
4535 CHANGE_NOTICE,
4536 IMPLEMENTATION_DATE,
4537 PLANNING_FACTOR,
4538 QUANTITY_RELATED,
4539 SO_BASIS,
4540 OPTIONAL,
4541 MUTUALLY_EXCLUSIVE_OPTIONS,
4542 INCLUDE_IN_COST_ROLLUP,
4543 CHECK_ATP,
4544 SHIPPING_ALLOWED,
4545 REQUIRED_TO_SHIP,
4546 REQUIRED_FOR_REVENUE,
4547 INCLUDE_ON_SHIP_DOCS,
4548 LOW_QUANTITY,
4549 HIGH_QUANTITY,
4550 ACD_TYPE,
4551 OLD_COMPONENT_SEQUENCE_ID,
4552 OPERATION_LEAD_TIME_PERCENT,
4553 REVISED_ITEM_SEQUENCE_ID,
4554 BOM_ITEM_TYPE,
4555 FROM_END_ITEM_UNIT_NUMBER,
4556 TO_END_ITEM_UNIT_NUMBER,
4557 ECO_FOR_PRODUCTION,
4558 ENFORCE_INT_REQUIREMENTS,
4559 DELETE_GROUP_NAME,
4560 DG_DESCRIPTION,
4561 OPTIONAL_ON_MODEL,
4562 MODEL_COMP_SEQ_ID,
4563 PLAN_LEVEL,
4564 AUTO_REQUEST_MATERIAL,
4565 COMPONENT_ITEM_REVISION_ID,
4566 FROM_BILL_REVISION_ID,
4567 TO_BILL_REVISION_ID,
4568 PICK_COMPONENTS,
4569 INCLUDE_ON_BILL_DOCS,
4570 COST_FACTOR,
4571 p_last_update_date,
4572 p_last_updated_by,
4573 p_last_update_login,
4574 p_creation_date,
4575 p_created_by,
4576 p_request_id,
4577 p_program_app_id,
4578 p_program_id,
4579 p_program_update_date,
4580 ATTRIBUTE_CATEGORY,
4581 ATTRIBUTE1,
4582 ATTRIBUTE2,
4583 ATTRIBUTE3,
4584 ATTRIBUTE4,
4585 ATTRIBUTE5,
4586 ATTRIBUTE6,
4587 ATTRIBUTE7,
4588 ATTRIBUTE8,
4589 ATTRIBUTE9,
4590 ATTRIBUTE10,
4591 ATTRIBUTE11,
4592 ATTRIBUTE12,
4593 ATTRIBUTE13,
4594 ATTRIBUTE14,
4595 ATTRIBUTE15,
4596 ORIGINAL_SYSTEM_REFERENCE
4597 FROM WSM_COPY_REQUIREMENT_OPS
4598 WHERE wip_entity_id = p_rep_wip_entity_id;
4599
4600
4601
4602 IF (g_debug = 'Y') THEN
4603 fnd_file.put_line(fnd_file.log, 'Created '||SQL%ROWCOUNT||' records in WCRO for we_id='||p_new_wip_entity_id);
4604 END IF;
4605
4606 -- Do not need to make copies for Resource Usages and Instances,
4607 -- since these will be set with planning runs next and picks up this job.
4608 -- Hence, copying the resource usages from the Rep Job would be incorrect.
4609
4610 l_stmt_num := 60;
4611
4612 -- ***** Create a record in wsm_lot_based_jobs ***** --
4613 INSERT into WSM_LOT_BASED_JOBS
4614 (WIP_ENTITY_ID,
4615 ORGANIZATION_ID,
4616 ON_REC_PATH,
4617 INTERNAL_COPY_TYPE,
4618 COPY_PARENT_WIP_ENTITY_ID,
4619 INFINITE_SCHEDULE,
4620 ROUTING_REFRESH_DATE,
4621 LAST_UPDATE_DATE,
4622 LAST_UPDATED_BY,
4623 LAST_UPDATE_LOGIN,
4624 CREATION_DATE,
4625 CREATED_BY,
4626 REQUEST_ID,
4627 PROGRAM_APPLICATION_ID,
4628 PROGRAM_ID,
4629 PROGRAM_UPDATE_DATE,
4630 SERIALIZATION_START_OP,
4631 FIRST_SERIAL_TXN_ID,
4632 CURRENT_JOB_OP_SEQ_NUM,
4633 CURRENT_RTG_OP_SEQ_NUM
4634 )
4635 SELECT
4636 p_new_wip_entity_id,
4637 ORGANIZATION_ID,
4638 ON_REC_PATH,
4639 0, -- INTERNAL_COPY_TYPE,
4640 COPY_PARENT_WIP_ENTITY_ID,
4641 NULL, --INFINITE_SCHEDULE
4642 SYSDATE, --ROUTING_REFRESH_DATE
4643 p_last_update_date,
4644 p_last_updated_by,
4645 p_last_update_login,
4646 p_creation_date,
4647 p_created_by,
4648 p_request_id,
4649 p_program_app_id,
4650 p_program_id,
4651 p_program_update_date,
4652 SERIALIZATION_START_OP,
4653 FIRST_SERIAL_TXN_ID, --To avoid joining with the parent job
4654 CURRENT_JOB_OP_SEQ_NUM,
4655 CURRENT_RTG_OP_SEQ_NUM
4656 FROM WSM_LOT_BASED_JOBS
4657 WHERE wip_entity_id = p_rep_wip_entity_id;
4658
4659
4660
4661 IF (g_debug = 'Y') THEN
4662 fnd_file.put_line(fnd_file.log, 'Created '||SQL%ROWCOUNT||' records in WLBJ for we_id='||p_new_wip_entity_id);
4663 END IF;
4664
4665 IF (p_inf_sch_flag = 'Y') THEN
4666
4667 l_stmt_num := 70;
4668
4669 SELECT ON_REC_PATH,
4670 organization_id
4671 INTO l_on_rec_path,
4672 l_org_id
4673 FROM WSM_LOT_BASED_JOBS
4674 WHERE wip_entity_id = p_new_wip_entity_id;
4675
4676 l_stmt_num := 80;
4677
4678 Get_Job_Curr_Op_Info(p_wip_entity_id => p_new_wip_entity_id,
4679 p_op_seq_num => l_curr_op_seq_num,
4680 p_op_seq_id => l_curr_op_seq_id,
4681 p_std_op_id => l_curr_op_std_op_id,
4682 p_dept_id => l_curr_op_dept_id,
4683 p_intra_op => l_curr_op_intra_op,
4684 p_op_qty => l_curr_op_qty,
4685 p_op_start_date => l_curr_op_start_date,
4686 p_op_comp_date => l_curr_op_compl_date,
4687 x_err_code => x_err_code,
4688 x_err_buf => x_err_buf);
4689
4690 IF (x_err_code = -1) -- No WO records
4691 OR (x_err_code = -2) -- Invalid job
4692 OR (x_err_code <> 0) -- Some other error
4693 THEN
4694 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Create_RepJobCopies('||l_stmt_num||'): '||
4695 'Get_Job_Curr_Op_Info returned '||x_err_code);
4696 fnd_file.put_line(fnd_file.log, 'Get_Job_Curr_Op_Info returned l_curr_op_seq_num='||l_curr_op_seq_num);
4697 return;
4698 END IF;
4699
4700 IF (p_inf_sch_mode IS NULL) THEN
4701
4702 IF (l_on_rec_path = 'Y') THEN
4703 -- ST : Fix for bug 5181364 : do forward scheduling instead of midpoints_forward scheduling.
4704 -- l_inf_sch_mode := WIP_CONSTANTS.MIDPOINT_FORWARDS;
4705 l_inf_sch_mode := WIP_CONSTANTS.FORWARDS;
4706 ELSE
4707 l_inf_sch_mode := WIP_CONSTANTS.CURRENT_OP;
4708 END IF;
4709
4710 END IF;
4711
4712 l_inf_sch_start_date := nvl(p_inf_sch_date, nvl(l_curr_op_start_date, sysdate));
4713 l_inf_sch_comp_date := NULL;
4714
4715 l_stmt_num := 90;
4716
4717 WSM_infinite_scheduler_PVT.schedule
4718 (
4719 p_initMsgList => FND_API.g_true,
4720 p_endDebug => FND_API.g_true,
4721 p_orgID => l_org_id,
4722 p_wipEntityID => p_new_wip_entity_id,
4723 p_scheduleMode => nvl(p_inf_sch_mode, l_inf_sch_mode),
4724 p_startDate => l_inf_sch_start_date,
4725 p_endDate => l_inf_sch_comp_date,
4726 p_opSeqNum => 0-l_curr_op_seq_num,
4727 p_resSeqNum => NULL,
4728 x_returnStatus => l_inf_sch_returnStatus,
4729 x_errorMsg => x_err_buf
4730 );
4731
4732
4733 IF (g_debug = 'Y') THEN
4734 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Create_RepJobCopies('||l_stmt_num||
4735 '): WSM_infinite_scheduler_PVT.schedule returned '||l_inf_sch_returnStatus);
4736 END IF;
4737
4738 -- Start : We do not need to error if Inf Sch fails, since dates will be calculated while moving --
4739 IF(l_inf_sch_returnStatus <> FND_API.G_RET_STS_SUCCESS) THEN
4740 fnd_file.put_line(fnd_file.log, 'Warning : Could not infinite schedule the job successfully');
4741 fnd_file.put_line(fnd_file.log, x_err_buf);
4742 END IF;
4743 x_err_code := 0;
4744 -- End : We do not need to error if Inf Sch fails, since dates will be calculated while moving --
4745
4746 END IF;
4747
4748 l_stmt_num := 100;
4749
4750 EXCEPTION
4751
4752 WHEN e_proc_error THEN
4753 -- x_err_code := SQLCODE;
4754 x_err_buf := 'WSM_JobCopies_PVT.Create_RepJobCopies('||l_stmt_num||') : '||x_err_buf;
4755 fnd_file.put_line(fnd_file.log, x_err_buf);
4756
4757 WHEN others THEN
4758 x_err_code := SQLCODE;
4759 x_err_buf := 'WSM_JobCopies_PVT.Create_RepJobCopies('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
4760 fnd_file.put_line(fnd_file.log, x_err_buf);
4761
4762 END Create_RepJobCopies;
4763
4764
4765
4766
4767 /*************************
4768 ** **
4769 ** Upgrade_JobCopies **
4770 ** **
4771 ** **
4772 ** x_err_code -> **
4773 ** =0 implies no errors**
4774 ** else, error code **
4775 ** **
4776 *************************/
4777
4778 PROCEDURE Upgrade_JobCopies (x_err_buf OUT NOCOPY VARCHAR2,
4779 x_err_code OUT NOCOPY NUMBER
4780 )
4781 IS
4782 -- Miscellaneous variables --
4783 l_stmt_num NUMBER := 0;
4784
4785 l_counter NUMBER := 0;
4786 l_jobs_counter NUMBER := 0;
4787 l_all_jobs_ctr NUMBER := 0;
4788 l_err_ctr NUMBER := 0;
4789 l_curr_rows NUMBER := 0; -- Number of records fetched in the current bulk fetch
4790 l_inf_sch_flag VARCHAR2(1);
4791 l_ret_val BOOLEAN;
4792
4793 -- Variables to be set --
4794 l_commit_count NUMBER := 50; --Commit after every these number of jobs
4795 l_batch_size NUMBER := 500; --Bulk fetch only these number of jobs
4796
4797 -- Exceptions --
4798 e_upg_error EXCEPTION;
4799 e_profile_error EXCEPTION;
4800
4801 -- ==============================================================================================
4802 -- table types used to bulk bind data from job tables to the PL/SQL tables.
4803 -- ==============================================================================================
4804 type t_job_wip_entity_name is table of wip_entities.wip_entity_name %type;
4805 type t_job_wip_entity_id is table of wip_discrete_jobs.wip_entity_id %type;
4806 type t_job_organization_id is table of wip_discrete_jobs.organization_id %type;
4807 type t_job_status_type is table of wip_discrete_jobs.status_type %type;
4808 type t_job_primary_item_id is table of wip_discrete_jobs.primary_item_id %type;
4809 type t_job_routing_item_id is table of wip_discrete_jobs.primary_item_id %type;
4810 type t_job_alt_rtg_desig is table of wip_discrete_jobs.alternate_routing_designator %type;
4811 type t_job_common_rtg_seq_id is table of wip_discrete_jobs.common_routing_sequence_id %type;
4812 type t_job_routing_revision_date is table of wip_discrete_jobs.routing_revision_date %type;
4813 type t_job_bill_item_id is table of wip_discrete_jobs.primary_item_id %type;
4814 type t_job_alt_bom_desig is table of wip_discrete_jobs.alternate_bom_designator %type;
4815 type t_job_bill_sequence_id is table of wip_discrete_jobs.common_bom_sequence_id %type;
4816 type t_job_common_bom_seq_id is table of wip_discrete_jobs.common_bom_sequence_id %type;
4817 type t_job_bom_revision_date is table of wip_discrete_jobs.bom_revision_date %type;
4818 type t_job_wip_supply_type is table of wip_discrete_jobs.wip_supply_type %type;
4819 type t_job_upg_success is table of number index by binary_integer;
4820 type t_job_err_buf is table of varchar2(2000) index by binary_integer;
4821
4822 -- ==============================================================================================
4823 -- instantiating the tables used to bulk bind data from job tables to the PL/SQL tables.
4824 -- ==============================================================================================
4825 v_job_wip_entity_id t_job_wip_entity_id := t_job_wip_entity_id();
4826 v_job_wip_entity_name t_job_wip_entity_name := t_job_wip_entity_name();
4827 v_job_organization_id t_job_organization_id := t_job_organization_id();
4828 v_job_status_type t_job_status_type := t_job_status_type();
4829 v_job_primary_item_id t_job_primary_item_id := t_job_primary_item_id();
4830 v_job_routing_item_id t_job_routing_item_id := t_job_routing_item_id();
4831 v_job_alt_rtg_desig t_job_alt_rtg_desig := t_job_alt_rtg_desig();
4832 v_job_common_rtg_seq_id t_job_common_rtg_seq_id := t_job_common_rtg_seq_id();
4833 v_job_rtg_rev_date t_job_routing_revision_date := t_job_routing_revision_date();
4834 v_job_bill_item_id t_job_bill_item_id := t_job_bill_item_id();
4835 v_job_alt_bom_desig t_job_alt_bom_desig := t_job_alt_bom_desig();
4836 v_job_bill_sequence_id t_job_bill_sequence_id := t_job_bill_sequence_id();
4837 v_job_common_bom_seq_id t_job_common_bom_seq_id := t_job_common_bom_seq_id();
4838 v_job_bom_revision_date t_job_bom_revision_date := t_job_bom_revision_date();
4839 v_job_wip_supply_type t_job_wip_supply_type := t_job_wip_supply_type();
4840 v_job_upg_success t_job_upg_success; --:= t_job_upg_success();
4841 v_job_err_buf t_job_err_buf; --:= t_job_err_buf();
4842
4843 CURSOR upgrade_jobs IS
4844 SELECT wdj.wip_entity_id,
4845 we.wip_entity_name,
4846 wdj.organization_id,
4847 wdj.status_type,
4848 wdj.primary_item_id,
4849 decode(wdj.job_type, 1, wdj.primary_item_id, wdj.routing_reference_id) routing_item_id,
4850 wdj.alternate_routing_designator alt_rtg_desig,
4851 wdj.common_routing_sequence_id common_rtg_seq_id,
4852 nvl(wdj.routing_revision_date, sysdate) routing_revision_date,
4853 decode(wdj.job_type, 1, wdj.primary_item_id, wdj.bom_reference_id) bill_item_id,
4854 wdj.alternate_bom_designator alt_bom_desig,
4855 WSMPUTIL.GET_JOB_BOM_SEQ_ID(wdj.wip_entity_id) bill_sequence_id,
4856 wdj.common_bom_sequence_id common_bom_seq_id,
4857 wdj.bom_revision_date,
4858 wdj.wip_supply_type,
4859 1 upg_success, -- Will indicate Upgrade Successful or failure in the PLSQL table t_upgrade_jobs
4860 '' err_buf -- Will contain the error message for failed jobs
4861 FROM wsm_parameters wp,
4862 wip_entities we,
4863 wip_discrete_jobs wdj
4864 WHERE we.organization_id = wp.organization_id
4865 AND we.entity_type = 5
4866 AND wdj.organization_id = we.organization_id
4867 AND wdj.wip_entity_id = we.wip_entity_id
4868 AND wdj.status_type IN (1, 3, 4, 6) --Unreleased, Released, Complete, OnHold
4869 AND NOT EXISTS (select 1 -- To make sure same set of jobs is not picked up again
4870 from wsm_lot_based_jobs wlbj
4871 where wlbj.wip_entity_id = wdj.wip_entity_id
4872 )
4873 ORDER BY we.wip_entity_id -- Slows down the SQL prepare
4874 ;
4875
4876 TYPE table_upgrade_jobs is TABLE OF upgrade_jobs%ROWTYPE INDEX by BINARY_INTEGER;
4877 t_err_upgrade_jobs table_upgrade_jobs;
4878
4879 CURSOR lot_based_jobs IS
4880 SELECT wdj.wip_entity_id,
4881 wdj.common_routing_sequence_id common_rtg_seq_id
4882 FROM wsm_parameters wp,
4883 wip_entities we,
4884 wip_discrete_jobs wdj
4885 WHERE wdj.organization_id = wp.organization_id
4886 AND we.organization_id = wp.organization_id
4887 AND wdj.organization_id = we.organization_id
4888 AND wdj.wip_entity_id = we.wip_entity_id
4889 AND (we.entity_type = 5
4890 OR we.entity_type = 8);
4891
4892 BEGIN
4893
4894 l_stmt_num := 10;
4895
4896 -- Fix for bug #3677276 : Moved update wo.wsm_op_seq_num stmt
4897 -- to inside create_jobcopies.
4898
4899 l_stmt_num := 40;
4900
4901 IF (to_number(fnd_profile.value('WSM_CREATE_LBJ_COPY_ROUTING')) = 1) THEN
4902 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Upgrade_JobCopies('||l_stmt_num||
4903 '): Value of profile "WSM: Create Lot Based Jobs Copy Routing" is YES.');
4904 fnd_file.put_line(fnd_file.log, 'Oracle Shopfloor Management is already upgraded to create Lot Based Job Copy Routing. Upgrade failed.');
4905 raise e_upg_error;
4906 ELSIF (to_number(fnd_profile.value('WSM_CREATE_LBJ_COPY_ROUTING')) = 2) THEN
4907 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Upgrade_JobCopies('||l_stmt_num||
4908 '): Value of profile "WSM: Create Lot Based Jobs Copy Routing" is NO.');
4909 fnd_file.put_line(fnd_file.log, 'Upgrading Lot Based Jobs to Use Copy Routings ...');
4910 ELSE
4911 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Upgrade_JobCopies('||l_stmt_num||
4912 '): Not able to fetch the value of the profile "WSM: Create Lot Based Jobs Copy Routing".');
4913 raise e_profile_error;
4914 END IF;
4915
4916 fnd_file.put_line(fnd_file.log, '*****Upgrade Start Time : '||to_char(sysdate,'DD-MON-YY HH24:MI:SS')||'*****');
4917
4918 l_counter := 0;
4919
4920 BEGIN -- {
4921
4922 LOOP -- {
4923 l_stmt_num := 50;
4924
4925 OPEN upgrade_jobs;
4926 FETCH upgrade_jobs bulk collect into
4927 v_job_wip_entity_id,
4928 v_job_wip_entity_name,
4929 v_job_organization_id,
4930 v_job_status_type,
4931 v_job_primary_item_id,
4932 v_job_routing_item_id,
4933 v_job_alt_rtg_desig,
4934 v_job_common_rtg_seq_id,
4935 v_job_rtg_rev_date,
4936 v_job_bill_item_id,
4937 v_job_alt_bom_desig,
4938 v_job_bill_sequence_id,
4939 v_job_common_bom_seq_id,
4940 v_job_bom_revision_date,
4941 v_job_wip_supply_type,
4942 v_job_upg_success,
4943 v_job_err_buf
4944 LIMIT l_batch_size;
4945
4946 l_curr_rows := upgrade_jobs%rowcount;
4947 CLOSE upgrade_jobs;
4948
4949 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Upgrade_JobCopies('||l_stmt_num||
4950 '): Number of rows loaded for the current batch: '||l_curr_rows);
4951 IF (l_curr_rows = 0) THEN
4952 exit;
4953 END IF;
4954
4955 fnd_file.put_line(fnd_file.log, '*****After Bulk fetch : '||to_char(sysdate,'DD-MON-YY HH24:MI:SS')||'*****');
4956
4957 l_counter := v_job_wip_entity_id.first;
4958 WHILE l_counter is NOT NULL
4959 LOOP --{
4960 --OPTII-PERF: Open Issue-Do we need to invoke infinite scheduler
4961 --during upgrade?
4962 IF (v_job_status_type(l_counter) = 4) THEN -- Complete Jobs
4963 l_inf_sch_flag := 'N';
4964 ELSE
4965 l_inf_sch_flag := 'Y';
4966 END IF;
4967
4968 x_err_code := 0;
4969 x_err_buf := NULL;
4970
4971 l_stmt_num := 60;
4972
4973 WSM_JobCopies_PVT.Create_JobCopies -- Call #1
4974 (
4975 x_err_buf => x_err_buf,
4976 x_err_code => x_err_code,
4977 p_wip_entity_id => v_job_wip_entity_id (l_counter),
4978 p_org_id => v_job_organization_id (l_counter),
4979 p_primary_item_id => v_job_primary_item_id (l_counter),
4980 p_routing_item_id => v_job_routing_item_id (l_counter),
4981 p_alt_rtg_desig => v_job_alt_rtg_desig (l_counter),
4982 p_rtg_seq_id => NULL, -- Will be NULL till reqd for some functionality
4983 p_common_rtg_seq_id => v_job_common_rtg_seq_id(l_counter),
4984 p_rtg_rev_date => v_job_rtg_rev_date (l_counter),
4985 p_bill_item_id => v_job_bill_item_id (l_counter),
4986 p_alt_bom_desig => v_job_alt_bom_desig (l_counter),
4987 p_bill_seq_id => v_job_bill_sequence_id (l_counter),
4988 p_common_bill_seq_id => v_job_common_bom_seq_id(l_counter),
4989 p_bom_rev_date => v_job_bom_revision_date(l_counter),
4990 p_wip_supply_type => v_job_wip_supply_type (l_counter),
4991
4992 p_last_update_date => sysdate,
4993 p_last_updated_by => fnd_global.user_id,
4994 p_last_update_login => fnd_global.login_id,
4995 p_creation_date => sysdate,
4996 p_created_by => fnd_global.user_id,
4997 p_request_id => fnd_global.conc_request_id,
4998 p_program_app_id => fnd_global.prog_appl_id,
4999 p_program_id => fnd_global.conc_program_id,
5000 p_program_update_date => sysdate,
5001
5002 p_inf_sch_flag => l_inf_sch_flag,
5003 p_inf_sch_mode => NULL,
5004 p_inf_sch_date => NULL
5005 );
5006
5007 l_stmt_num := 70;
5008 IF (g_debug = 'Y') THEN
5009 fnd_file.put_line(fnd_file.log, l_stmt_num ||' x_err_code='||x_err_code);
5010 END IF;
5011
5012 IF ((x_err_code = 0) OR (x_err_code IS NULL)) THEN
5013 l_stmt_num := 71;
5014 fnd_file.put_line(fnd_file.log,
5015 l_all_jobs_ctr+1||' '||
5016 'Organization id='||v_job_organization_id(l_counter)||'; '||
5017 ' Job: '||v_job_wip_entity_name(l_counter)||
5018 ' (id='||v_job_wip_entity_id(l_counter)||') '||
5019 to_char(sysdate,'DD-MON-YY HH24:MI:SS'));
5020
5021
5022 ELSIF (x_err_code = -1) THEN -- Warning
5023 l_stmt_num := 72;
5024 fnd_file.put_line(fnd_file.log,
5025 l_all_jobs_ctr+1||' '||
5026 'Organization id='||v_job_organization_id(l_counter)||'; '||
5027 ' Job: '||v_job_wip_entity_name(l_counter)||
5028 ' (id='||v_job_wip_entity_id(l_counter)||') '||
5029 to_char(sysdate,'DD-MON-YY HH24:MI:SS') ||
5030 ' WARNING : '||x_err_buf);
5031
5032 ELSE -- Error
5033 l_stmt_num := 73;
5034 fnd_file.put_line(fnd_file.log,
5035 l_all_jobs_ctr+1||' '||
5036 'Organization id='||v_job_organization_id(l_counter)||'; '||
5037 ' Job: '||v_job_wip_entity_name(l_counter)||
5038 ' (id='||v_job_wip_entity_id(l_counter)||') '||
5039 to_char(sysdate,'DD-MON-YY HH24:MI:SS') ||
5040 ' ERROR : '||x_err_code||' '||x_err_buf);
5041
5042 l_stmt_num := 80;
5043
5044 v_job_upg_success(l_counter) := 2;
5045 v_job_err_buf(l_counter) := x_err_buf;
5046
5047 t_err_upgrade_jobs(l_err_ctr).wip_entity_id := v_job_wip_entity_id (l_counter);
5048 t_err_upgrade_jobs(l_err_ctr).wip_entity_name := v_job_wip_entity_name (l_counter);
5049 t_err_upgrade_jobs(l_err_ctr).organization_id := v_job_organization_id (l_counter);
5050 t_err_upgrade_jobs(l_err_ctr).status_type := v_job_status_type (l_counter);
5051 t_err_upgrade_jobs(l_err_ctr).primary_item_id := v_job_primary_item_id (l_counter);
5052 t_err_upgrade_jobs(l_err_ctr).routing_item_id := v_job_routing_item_id (l_counter);
5053 t_err_upgrade_jobs(l_err_ctr).alt_rtg_desig := v_job_alt_rtg_desig (l_counter);
5054 t_err_upgrade_jobs(l_err_ctr).common_rtg_seq_id := v_job_common_rtg_seq_id(l_counter);
5055 t_err_upgrade_jobs(l_err_ctr).routing_revision_date := v_job_rtg_rev_date (l_counter);
5056 t_err_upgrade_jobs(l_err_ctr).bill_item_id := v_job_bill_item_id (l_counter);
5057 t_err_upgrade_jobs(l_err_ctr).alt_bom_desig := v_job_alt_bom_desig (l_counter);
5058 t_err_upgrade_jobs(l_err_ctr).bill_sequence_id := v_job_bill_sequence_id (l_counter);
5059 t_err_upgrade_jobs(l_err_ctr).common_bom_seq_id := v_job_common_bom_seq_id(l_counter);
5060 t_err_upgrade_jobs(l_err_ctr).bom_revision_date := v_job_bom_revision_date(l_counter);
5061 t_err_upgrade_jobs(l_err_ctr).wip_supply_type := v_job_wip_supply_type (l_counter);
5062 t_err_upgrade_jobs(l_err_ctr).upg_success := v_job_upg_success (l_counter);
5063 t_err_upgrade_jobs(l_err_ctr).err_buf := v_job_err_buf (l_counter);
5064 l_err_ctr := l_err_ctr+1;
5065
5066 l_stmt_num := 90;
5067
5068 -- Delete COPY tables --
5069 DELETE WSM_COPY_OPERATIONS
5070 WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
5071
5072 DELETE WSM_COPY_OP_NETWORKS
5073 WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
5074
5075 DELETE WSM_COPY_OP_RESOURCES
5076 WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
5077
5078 DELETE WSM_COPY_OP_RESOURCE_INSTANCES
5079 WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
5080
5081 DELETE WSM_COPY_OP_RESOURCE_USAGE
5082 WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
5083
5084 DELETE WSM_COPY_REQUIREMENT_OPS
5085 WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
5086
5087 DELETE WSM_LOT_BASED_JOBS
5088 WHERE wip_entity_id = v_job_wip_entity_id(l_counter);
5089
5090 l_stmt_num := 100;
5091
5092 INSERT into WSM_LOT_BASED_JOBS
5093 (WIP_ENTITY_ID,
5094 ORGANIZATION_ID,
5095 ON_REC_PATH,
5096 INTERNAL_COPY_TYPE,
5097 COPY_PARENT_WIP_ENTITY_ID,
5098 INFINITE_SCHEDULE,
5099 ROUTING_REFRESH_DATE,
5100 LAST_UPDATE_DATE,
5101 LAST_UPDATED_BY,
5102 LAST_UPDATE_LOGIN,
5103 CREATION_DATE,
5104 CREATED_BY,
5105 REQUEST_ID,
5106 PROGRAM_APPLICATION_ID,
5107 PROGRAM_ID,
5108 PROGRAM_UPDATE_DATE
5109 )
5110 VALUES
5111 (v_job_wip_entity_id(l_counter),
5112 v_job_organization_id(l_counter),
5113 'N', -- ON_REC_PATH
5114 3, -- INTERNAL_COPY_TYPE : Copies incorrect due to Upgrade
5115 NULL, -- COPY_PARENT_WIP_ENTITY_ID
5116 NULL, -- INFINITE_SCHEDULE
5117 SYSDATE, -- ROUTING_REFRESH_DATE
5118 sysdate,
5119 fnd_global.user_id,
5120 fnd_global.login_id,
5121 sysdate,
5122 fnd_global.user_id,
5123 fnd_global.conc_request_id,
5124 fnd_global.prog_appl_id,
5125 fnd_global.conc_program_id,
5126 sysdate
5127 );
5128
5129 END IF;
5130
5131
5132 l_counter := v_job_wip_entity_id.next(l_counter);
5133 l_all_jobs_ctr := l_all_jobs_ctr + 1;
5134
5135 l_stmt_num := 110;
5136
5137 IF (MOD(l_all_jobs_ctr, l_commit_count) = 0) THEN -- Commit after every set of jobs
5138 COMMIT;
5139 END IF;
5140
5141 END LOOP; -- } WHILE l_counter is NOT NULL
5142
5143 l_stmt_num := 120;
5144
5145 v_job_wip_entity_id.delete;
5146 v_job_wip_entity_name.delete;
5147 v_job_organization_id.delete;
5148 v_job_status_type.delete;
5149 v_job_primary_item_id.delete;
5150 v_job_routing_item_id.delete;
5151 v_job_alt_rtg_desig.delete;
5152 v_job_common_rtg_seq_id.delete;
5153 v_job_rtg_rev_date.delete;
5154 v_job_bill_item_id.delete;
5155 v_job_alt_bom_desig.delete;
5156 v_job_bill_sequence_id.delete;
5157 v_job_common_bom_seq_id.delete;
5158 v_job_bom_revision_date.delete;
5159 v_job_wip_supply_type.delete;
5160 v_job_upg_success.delete;
5161 v_job_err_buf.delete;
5162
5163 END LOOP; -- } LOOP
5164
5165 END; -- } BEGIN block
5166
5167 l_stmt_num := 130;
5168 COMMIT;
5169
5170 fnd_file.put_line(fnd_file.log, '*****Upgrade End Time : '||to_char(sysdate,'DD-MON-YY HH24:MI:SS')||'*****');
5171 fnd_file.put_line(fnd_file.log, 'Upgraded '||l_all_jobs_ctr||' jobs in total');
5172
5173 fnd_file.put_line(fnd_file.log, 'Upgrade failed to create Job Copies for following '||l_err_ctr||' jobs : ');
5174 l_stmt_num := 140;
5175
5176 FOR l_jobs_counter in 0..l_err_ctr-1
5177 LOOP
5178 l_stmt_num := 141;
5179 IF (t_err_upgrade_jobs(l_jobs_counter).upg_success = 2) THEN
5180 l_stmt_num := 142;
5181 fnd_file.put_line(fnd_file.log,
5182 l_jobs_counter+1||' '||
5183 'Organization id='||t_err_upgrade_jobs(l_jobs_counter).organization_id||'; '||
5184 ' Job: '||t_err_upgrade_jobs(l_jobs_counter).wip_entity_name||
5185 ' (id='||t_err_upgrade_jobs(l_jobs_counter).wip_entity_id||') '||
5186 ' ERROR : '||t_err_upgrade_jobs(l_jobs_counter).err_buf);
5187 END IF;
5188 l_stmt_num := 143;
5189 END LOOP;
5190
5191 l_stmt_num := 144;
5192 t_err_upgrade_jobs.delete;
5193
5194 fnd_file.put_line(fnd_file.log, 'WSM_JobCopies_PVT.Upgrade_JobCopies('||l_stmt_num||')'||
5195 'Please correct the problem and run Refresh Open Jobs Copies for the above '||l_err_ctr||' jobs. ');
5196
5197 l_stmt_num := 150;
5198
5199 l_ret_val := fnd_profile.save('WSM_CREATE_LBJ_COPY_ROUTING', '1', 'SITE');
5200
5201 fnd_file.put_line(fnd_file.log, 'Set the profile "WSM: Create Lot Based Jobs Copy Routing" to YES. '||
5202 'Upgrade successful. ');
5203
5204 COMMIT;
5205
5206 l_stmt_num := 160;
5207
5208 EXCEPTION
5209 WHEN e_upg_error THEN
5210 raise_application_error (-20000, 'ERROR at line '||l_stmt_num||': Oracle Shopfloor Management is already upgraded to create Lot Based Job Copy Routing. Upgrade failed.');
5211
5212 WHEN e_profile_error THEN
5213 raise_application_error (-20000, 'ERROR at line '||l_stmt_num||': Not able to fetch the value of the profile "WSM: Create Lot Based Jobs Copy Routing"');
5214
5215 WHEN others THEN
5216 x_err_code := SQLCODE;
5217 x_err_buf := 'WSM_JobCopies_PVT.Upgrade_JobCopies('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
5218 fnd_file.put_line(fnd_file.log, x_err_buf);
5219
5220 END Upgrade_JobCopies;
5221
5222 PROCEDURE process_wip_info( p_wip_entity_id IN NUMBER,
5223 p_org_id IN NUMBER,
5224 p_last_update_date IN DATE,
5225 p_last_updated_by IN NUMBER,
5226 p_last_update_login IN NUMBER,
5227 p_creation_date IN DATE,
5228 p_created_by IN NUMBER,
5229 p_request_id IN NUMBER,
5230 p_program_app_id IN NUMBER,
5231 p_program_id IN NUMBER,
5232 p_program_update_date IN DATE,
5233 p_phantom_exists IN NUMBER,
5234 p_current_op_seq_num IN NUMBER,
5235 x_err_buf OUT NOCOPY VARCHAR2,
5236 x_err_code OUT NOCOPY NUMBER)
5237 AS
5238 l_op_seq_incr NUMBER;
5239 l_curr_op_seq_num NUMBER; --OPTII-PERF
5240 l_txn_quantity NUMBER;
5241
5242 l_return_status VARCHAR2(1);
5243 l_msg_count NUMBER;
5244 l_msg_data VARCHAR2(2000) := NULL;
5245 l_stmt_num NUMBER;
5246 begin
5247 IF p_current_op_seq_num IS NULL THEN
5248 return;
5249 END IF;
5250 IF p_current_op_seq_num < 0 THEN
5251 l_curr_op_seq_num := p_current_op_seq_num *-1;
5252 ELSE
5253 l_curr_op_seq_num := p_current_op_seq_num;
5254 END IF;
5255
5256 l_stmt_num := 10;
5257
5258 select nvl(OP_SEQ_NUM_INCREMENT, 10)
5259 into l_op_seq_incr
5260 from wsm_parameters
5261 where ORGANIZATION_ID = p_org_id;
5262
5263 l_stmt_num := 20;
5264 select start_quantity
5265 into l_txn_quantity
5266 from wip_discrete_jobs
5267 where WIP_ENTITY_ID = p_wip_entity_id;
5268
5269 l_stmt_num := 30;
5270 INSERT INTO WIP_OPERATIONS
5271 (WIP_ENTITY_ID,
5272 OPERATION_SEQ_NUM,
5273 ORGANIZATION_ID,
5274 LAST_UPDATE_DATE,
5275 LAST_UPDATED_BY,
5276 CREATION_DATE,
5277 CREATED_BY,
5278 LAST_UPDATE_LOGIN,
5279 REQUEST_ID,
5280 PROGRAM_APPLICATION_ID,
5281 PROGRAM_ID,
5282 PROGRAM_UPDATE_DATE,
5283 OPERATION_SEQUENCE_ID,
5284 STANDARD_OPERATION_ID,
5285 DEPARTMENT_ID,
5286 DESCRIPTION,
5287 SCHEDULED_QUANTITY,
5288 QUANTITY_IN_QUEUE,
5289 QUANTITY_RUNNING,
5290 QUANTITY_WAITING_TO_MOVE,
5291 QUANTITY_REJECTED,
5292 QUANTITY_SCRAPPED,
5293 QUANTITY_COMPLETED,
5294 FIRST_UNIT_START_DATE,
5295 FIRST_UNIT_COMPLETION_DATE,
5296 LAST_UNIT_START_DATE,
5297 LAST_UNIT_COMPLETION_DATE,
5298 PREVIOUS_OPERATION_SEQ_NUM,
5299 NEXT_OPERATION_SEQ_NUM,
5300 COUNT_POINT_TYPE,
5301 BACKFLUSH_FLAG,
5302 MINIMUM_TRANSFER_QUANTITY,
5303 DATE_LAST_MOVED,
5304 ATTRIBUTE_CATEGORY,
5305 ATTRIBUTE1,
5306 ATTRIBUTE2,
5307 ATTRIBUTE3,
5308 ATTRIBUTE4,
5309 ATTRIBUTE5,
5310 ATTRIBUTE6,
5311 ATTRIBUTE7,
5312 ATTRIBUTE8,
5313 ATTRIBUTE9,
5314 ATTRIBUTE10,
5315 ATTRIBUTE11,
5316 ATTRIBUTE12,
5317 ATTRIBUTE13,
5318 ATTRIBUTE14,
5319 ATTRIBUTE15,
5320 OPERATION_YIELD,
5321 OPERATION_YIELD_ENABLED,
5322 RECOMMENDED,
5323 CUMULATIVE_SCRAP_QUANTITY,
5324 WSM_OP_SEQ_NUM)
5325 SELECT WCO.wip_entity_id,
5326 l_op_seq_incr,
5327 p_org_id,
5328 WCO.LAST_UPDATE_DATE,
5329 WCO.LAST_UPDATED_BY,
5330 SYSDATE,
5331 WCO.CREATED_BY,
5332 WCO.LAST_UPDATE_LOGIN,
5333 WCO.REQUEST_ID,
5334 WCO.PROGRAM_APPLICATION_ID,
5335 WCO.PROGRAM_ID,
5336 WCO.PROGRAM_UPDATE_DATE,
5337 WCO.OPERATION_SEQUENCE_ID,
5338 WCO.STANDARD_OPERATION_ID,
5339 WCO.DEPARTMENT_ID,
5340 WCO.OPERATION_DESCRIPTION,
5341 -- Bug 5603843. Modified inside nvl clause from 0 to wdj.start_quantity
5342 ROUND(nvl(WCO.RECO_SCHEDULED_QUANTITY, wdj.start_quantity), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
5343 (decode(wdj.status_type,3, round(wdj.start_quantity,
5344 wip_constants.max_displayed_precision), 0)),
5345 0, 0, 0, 0, 0,
5346 decode(recommended, 'Y', nvl(RECO_START_DATE, WCO.LAST_UPDATE_DATE), WCO.LAST_UPDATE_DATE),
5347 decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, WCO.LAST_UPDATE_DATE), WCO.LAST_UPDATE_DATE),
5348 decode(recommended, 'Y', nvl(RECO_START_DATE, WCO.LAST_UPDATE_DATE), WCO.LAST_UPDATE_DATE),
5349 decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, WCO.LAST_UPDATE_DATE),WCO.LAST_UPDATE_DATE),
5350 NULL,NULL,--0, 0,
5351 WCO.COUNT_POINT_TYPE,
5352 WCO.BACKFLUSH_FLAG,
5353 NVL(WCO.MINIMUM_TRANSFER_QUANTITY, 0),
5354 '',
5355 WCO.ATTRIBUTE_CATEGORY,
5356 WCO.ATTRIBUTE1,
5357 WCO.ATTRIBUTE2,
5358 WCO.ATTRIBUTE3,
5359 WCO.ATTRIBUTE4,
5360 WCO.ATTRIBUTE5,
5361 WCO.ATTRIBUTE6,
5362 WCO.ATTRIBUTE7,
5363 WCO.ATTRIBUTE8,
5364 WCO.ATTRIBUTE9,
5365 WCO.ATTRIBUTE10,
5366 WCO.ATTRIBUTE11,
5367 WCO.ATTRIBUTE12,
5368 WCO.ATTRIBUTE13,
5369 WCO.ATTRIBUTE14,
5370 WCO.ATTRIBUTE15,
5371 WCO.YIELD,
5372 to_char(WCO.OPERATION_YIELD_ENABLED),
5373 nvl(RECOMMENDED, 'N'),
5374 WDJ.QUANTITY_SCRAPPED,
5375 WCO.operation_seq_num
5376 FROM WSM_COPY_OPERATIONS WCO,
5377 WIP_DISCRETE_JOBS WDJ
5378 WHERE WDJ.wip_entity_id = WCO.wip_entity_id
5379 AND WCO.network_start_end = 'S'
5380 AND WCO.wip_entity_id = p_wip_entity_id;
5381
5382 l_stmt_num := 40;
5383 INSERT INTO WIP_OPERATION_YIELDS (WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
5384 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
5385 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
5386 PROGRAM_APPLICATION_ID, PROGRAM_ID,PROGRAM_UPDATE_DATE,
5387 STATUS, SCRAP_ACCOUNT, EST_SCRAP_ABSORB_ACCOUNT)
5388 SELECT WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, WO.ORGANIZATION_ID,
5389 SYSDATE,
5390 p_last_updated_by,
5391 SYSDATE,
5392 p_last_updated_by,
5393 p_last_update_login,
5394 DECODE(p_request_id, 0, '', p_request_id),
5395 DECODE(p_program_app_id, 0, '', p_program_app_id),
5396 p_program_id,
5397 DECODE(p_program_id, 0, '', SYSDATE),
5398 NULL, BD.SCRAP_ACCOUNT, BD.EST_ABSORPTION_ACCOUNT
5399 FROM WIP_OPERATIONS WO,
5400 BOM_DEPARTMENTS BD
5401 WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
5402 AND WO.OPERATION_SEQ_NUM = l_op_seq_incr
5403 AND WO.DEPARTMENT_ID = BD.DEPARTMENT_ID;
5404
5405 --First populate wcro for non phantom components
5406 IF p_phantom_exists = 2 THEN
5407 l_stmt_num := 50;
5408 INSERT INTO WIP_REQUIREMENT_OPERATIONS
5409 (inventory_item_id,
5410 organization_id,
5411 wip_entity_id,
5412 operation_seq_num,
5413 last_update_date,
5414 last_updated_by,
5415 creation_date,
5416 created_by,
5417 last_update_login,
5418 component_sequence_id,
5419 wip_supply_type,
5420 date_required,
5421 basis_type, --LBM enh
5422 required_quantity,
5423 quantity_issued,
5424 quantity_per_assembly,
5425 component_yield_factor, --R12:Comp Shrinkage project
5426 supply_subinventory,
5427 supply_locator_id,
5428 mrp_net_flag,
5429 comments,
5430 attribute_category,
5431 attribute1,
5432 attribute2,
5433 attribute3,
5434 attribute4,
5435 attribute5,
5436 attribute6,
5437 attribute7,
5438 attribute8,
5439 attribute9,
5440 attribute10,
5441 attribute11,
5442 attribute12,
5443 attribute13,
5444 attribute14,
5445 attribute15,
5446 segment1,
5447 segment2,
5448 segment3,
5449 segment4,
5450 segment5,
5451 segment6,
5452 segment7,
5453 segment8,
5454 segment9,
5455 segment10,
5456 segment11,
5457 segment12,
5458 segment13,
5459 segment14,
5460 segment15,
5461 segment16,
5462 segment17,
5463 segment18,
5464 segment19,
5465 segment20,
5466 department_id,
5467 released_quantity)
5468 SELECT
5469 wcro.COMPONENT_ITEM_ID,
5470 wcro.organization_id,
5471 wcro.wip_entity_id,
5472 l_op_seq_incr,
5473 wcro.last_update_date,
5474 wcro.last_updated_by,
5475 wcro.creation_date,
5476 wcro.created_by,
5477 wcro.last_update_login,
5478 wcro.component_sequence_id,
5479 wcro.wip_supply_type,
5480 Nvl(WCRO.reco_date_required, WCRO.last_update_date),
5481 wcro.basis_type, --LBM enh
5482 ROUND(WCRO.quantity_per_assembly* decode(wcro.basis_type, 2, 1, wdj.start_quantity), WSMPCNST.NUMBER_OF_DECIMALS), --LBM enh
5483 0,
5484 WCRO.bill_quantity_per_assembly, --R12:Comp Shrinkage Project:Changed from qpa to bqpa
5485 WCRO.component_yield_factor, --R12:Comp Shrinkage Project:Added
5486 WCRO.supply_subinventory,
5487 WCRO.supply_locator_id,
5488 decode(WCRO.wip_supply_type,5, 2,decode(sign(WCRO.quantity_per_assembly),-1, 2,1)),
5489 WCRO.component_remarks,
5490 WCRO.attribute_category,
5491 WCRO.attribute1,
5492 WCRO.attribute2,
5493 WCRO.attribute3,
5494 WCRO.attribute4,
5495 WCRO.attribute5,
5496 WCRO.attribute6,
5497 WCRO.attribute7,
5498 WCRO.attribute8,
5499 WCRO.attribute9,
5500 WCRO.attribute10,
5501 WCRO.attribute11,
5502 WCRO.attribute12,
5503 WCRO.attribute13,
5504 WCRO.attribute14,
5505 WCRO.attribute15,
5506 MSI.segment1,
5507 MSI.segment2,
5508 MSI.segment3,
5509 MSI.segment4,
5510 MSI.segment5,
5511 MSI.segment6,
5512 MSI.segment7,
5513 MSI.segment8,
5514 MSI.segment9,
5515 MSI.segment10,
5516 MSI.segment11,
5517 MSI.segment12,
5518 MSI.segment13,
5519 MSI.segment14,
5520 MSI.segment15,
5521 MSI.segment16,
5522 MSI.segment17,
5523 MSI.segment18,
5524 MSI.segment19,
5525 MSI.segment20,
5526 WCRO.department_id,
5527 ROUND(WDJ.start_quantity*WCRO.quantity_per_assembly, WSMPCNST.NUMBER_OF_DECIMALS)
5528 FROM WIP_DISCRETE_JOBS WDJ,
5529 WSM_COPY_REQUIREMENT_OPS WCRO,
5530 MTL_SYSTEM_ITEMS MSI
5531 WHERE WCRO.WIP_ENTITY_ID = p_wip_entity_id
5532 AND WCRO.OPERATION_SEQ_NUM = l_curr_op_seq_num
5533 AND MSI.inventory_item_id = WCRO.component_item_id
5534 AND MSI.organization_id = WCRO.organization_id
5535 AND WCRO.RECOMMENDED = 'Y'
5536 AND WDJ.wip_entity_id = p_wip_entity_id;
5537 end if;
5538
5539 l_stmt_num := 60;
5540 INSERT INTO WIP_OPERATION_RESOURCES
5541 (WIP_ENTITY_ID, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM,
5542 ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID,
5543 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
5544 CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
5545 PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
5546 RESOURCE_ID, UOM_CODE,
5547 BASIS_TYPE, USAGE_RATE_OR_AMOUNT, ACTIVITY_ID,
5548 SCHEDULED_FLAG, ASSIGNED_UNITS,
5549 /* ST : Detailed Scheduling */
5550 maximum_assigned_units,
5551 batch_id,
5552 firm_flag,
5553 group_sequence_id,
5554 group_sequence_number,
5555 parent_resource_seq,
5556 /* ST : Detailed Scheduling */
5557 AUTOCHARGE_TYPE,
5558 STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS, APPLIED_RESOURCE_VALUE,
5559 START_DATE, COMPLETION_DATE,
5560 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
5561 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
5562 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
5563 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
5564 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
5565 ATTRIBUTE15,
5566 SCHEDULE_SEQ_NUM, --bugfix 2493065
5567 SUBSTITUTE_GROUP_NUM,
5568 REPLACEMENT_GROUP_NUM,
5569 PRINCIPLE_FLAG,
5570 SETUP_ID,
5571 DEPARTMENT_ID) --Bug 4522620
5572 SELECT WCOR.WIP_ENTITY_ID
5573 , l_op_seq_incr
5574 , WCOR.RESOURCE_SEQ_NUM
5575 , WCOR.ORGANIZATION_ID
5576 , NULL
5577 , SYSDATE
5578 , p_last_updated_by
5579 , SYSDATE
5580 , p_last_updated_by
5581 , p_last_update_login
5582 , DECODE(p_request_id, 0, '', p_request_id)
5583 , DECODE(p_program_app_id, 0, '', p_program_app_id)
5584 , p_program_id
5585 , SYSDATE
5586 , WCOR.RESOURCE_ID
5587 , WCOR.UOM_CODE
5588 , WCOR.BASIS_TYPE
5589 , WCOR.USAGE_RATE_OR_AMOUNT
5590 , WCOR.ACTIVITY_ID
5591 , WCOR.SCHEDULE_FLAG
5592 , WCOR.ASSIGNED_UNITS
5593 , WCOR.MAX_ASSIGNED_UNITS
5594 , WCOR.batch_id
5595 , WCOR.firm_type
5596 , WCOR.group_sequence_id
5597 , WCOR.group_sequence_num
5598 , WCOR.parent_resource_seq_num
5599 , WCOR.AUTOCHARGE_TYPE
5600 , WCOR.STANDARD_RATE_FLAG
5601 , 0
5602 , 0
5603 , decode(recommended, 'Y', nvl(RECO_START_DATE, SYSDATE), SYSDATE)
5604 , decode(recommended, 'Y', nvl(RECO_COMPLETION_DATE, SYSDATE), SYSDATE)
5605 , WCOR.ATTRIBUTE_CATEGORY
5606 , WCOR.ATTRIBUTE1
5607 , WCOR.ATTRIBUTE2
5608 , WCOR.ATTRIBUTE3
5609 , WCOR.ATTRIBUTE4
5610 , WCOR.ATTRIBUTE5
5611 , WCOR.ATTRIBUTE6
5612 , WCOR.ATTRIBUTE7
5613 , WCOR.ATTRIBUTE8
5614 , WCOR.ATTRIBUTE9
5615 , WCOR.ATTRIBUTE10
5616 , WCOR.ATTRIBUTE11
5617 , WCOR.ATTRIBUTE12
5618 , WCOR.ATTRIBUTE13
5619 , WCOR.ATTRIBUTE14
5620 , WCOR.ATTRIBUTE15
5621 , WCOR.SCHEDULE_SEQ_NUM
5622 , WCOR.SUBSTITUTE_GROUP_NUM
5623 , WCOR.REPLACEMENT_GROUP_NUM
5624 , WCOR.PRINCIPLE_FLAG
5625 , WCOR.SETUP_ID
5626 , WCOR.DEPARTMENT_ID --Bug 4522620
5627 FROM WSM_COPY_OP_RESOURCES WCOR
5628 WHERE WCOR.WIP_ENTITY_ID = p_wip_entity_id
5629 AND WCOR.OPERATION_SEQ_NUM = l_curr_op_seq_num
5630 AND WCOR.recommended='Y';
5631
5632 IF SQL%ROWCOUNT > 0 THEN
5633 l_stmt_num := 70;
5634 INSERT INTO WIP_SUB_OPERATION_RESOURCES
5635 (wip_entity_id,
5636 operation_seq_num,
5637 resource_seq_num,
5638 organization_id,
5639 repetitive_schedule_id,
5640 last_update_date,
5641 last_updated_by,
5642 creation_date,
5643 created_by,
5644 last_update_login,
5645 resource_id,
5646 uom_code,
5647 basis_type,
5648 usage_rate_or_amount,
5649 activity_id,
5650 scheduled_flag,
5651 assigned_units,
5652 maximum_assigned_units, /* ST : Detailed Scheduling */
5653 autocharge_type,
5654 standard_rate_flag,
5655 applied_resource_units,
5656 applied_resource_value,
5657 attribute_category,
5658 attribute1,
5659 attribute2,
5660 attribute3,
5661 attribute4,
5662 attribute5,
5663 attribute6,
5664 attribute7,
5665 attribute8,
5666 attribute9,
5667 attribute10,
5668 attribute11,
5669 attribute12,
5670 attribute13,
5671 attribute14,
5672 attribute15,
5673 completion_date,
5674 start_date,
5675 schedule_seq_num,
5676 substitute_group_num,
5677 replacement_group_num,
5678 setup_id)
5679 SELECT WCOR.wip_entity_id,
5680 l_op_seq_incr,
5681 WCOR.resource_seq_num,
5682 WCOR.organization_id,
5683 null,
5684 SYSDATE ,
5685 p_last_updated_by,
5686 SYSDATE,
5687 p_last_updated_by,
5688 p_last_update_login,
5689 WCOR.resource_id,
5690 WCOR.uom_code,
5691 WCOR.basis_type,
5692 WCOR.usage_rate_or_amount,
5693 WCOR.activity_id,
5694 WCOR.schedule_flag,
5695 WCOR.assigned_units,
5696 WCOR.max_assigned_units,
5697 WCOR.autocharge_type,
5698 WCOR.standard_rate_flag,
5699 0, --WCOR.applied_resource_units,--move enh?
5700 0, -- WCOR.applied_resource_value, --move enh?
5701 WCOR.attribute_category,
5702 WCOR.attribute1,
5703 WCOR.attribute2,
5704 WCOR.attribute3,
5705 WCOR.attribute4,
5706 WCOR.attribute5,
5707 WCOR.attribute6,
5708 WCOR.attribute7,
5709 WCOR.attribute8,
5710 WCOR.attribute9,
5711 WCOR.attribute10,
5712 WCOR.attribute11,
5713 WCOR.attribute12,
5714 WCOR.attribute13,
5715 WCOR.attribute14,
5716 WCOR.attribute15,
5717 --as per Zhaohui copying the dates from WCOR
5718 nvl(WCOR.RECO_START_DATE, SYSDATE),
5719 nvl(WCOR.RECO_COMPLETION_DATE, SYSDATE),
5720 WCOR.schedule_seq_num,
5721 WCOR.substitute_group_num,
5722 WCOR.replacement_group_num,
5723 WCOR.setup_id
5724 FROM WSM_COPY_OP_RESOURCES WCOR
5725 WHERE WCOR.WIP_ENTITY_ID = p_wip_entity_id
5726 AND WCOR.OPERATION_SEQ_NUM = l_curr_op_seq_num
5727 AND WCOR.PHANTOM_ITEM_ID IS NULL
5728 AND WCOR.recommended<>'Y';
5729
5730 l_stmt_num := 80;
5731 INSERT into wip_operation_resource_usage
5732 (WIP_ENTITY_ID,
5733 OPERATION_SEQ_NUM,
5734 RESOURCE_SEQ_NUM,
5735 REPETITIVE_SCHEDULE_ID,
5736 ORGANIZATION_ID,
5737 START_DATE,
5738 COMPLETION_DATE,
5739 ASSIGNED_UNITS,
5740 --resource_hours, /* ST : Detailed scheduling */
5741 LAST_UPDATE_DATE,
5742 LAST_UPDATED_BY,
5743 CREATION_DATE,
5744 CREATED_BY,
5745 LAST_UPDATE_LOGIN,
5746 REQUEST_ID,
5747 PROGRAM_APPLICATION_ID,
5748 PROGRAM_ID,
5749 PROGRAM_UPDATE_DATE,
5750 INSTANCE_ID,
5751 SERIAL_NUMBER,
5752 CUMULATIVE_PROCESSING_TIME)
5753 SELECT WCORU.WIP_ENTITY_ID,
5754 l_op_seq_incr,
5755 WCORU.RESOURCE_SEQ_NUM,
5756 null,
5757 WCORU.ORGANIZATION_ID,
5758 WCORU.START_DATE,
5759 WCORU.COMPLETION_DATE,
5760 WCORU.ASSIGNED_UNITS,
5761 --WCORU.RESOURCE_HOURS, /* ST : Detailed scheduling */
5762 SYSDATE ,
5763 p_last_updated_by,
5764 SYSDATE,
5765 p_last_updated_by,
5766 p_last_update_login,
5767 DECODE(p_request_id, 0, '', p_request_id),
5768 DECODE(p_program_app_id, 0, '', p_program_app_id),
5769 p_program_id,
5770 SYSDATE,
5771 WCORU.INSTANCE_ID,
5772 WCORU.SERIAL_NUMBER,
5773 WCORU.CUMULATIVE_PROCESSING_TIME
5774 FROM WIP_OPERATION_RESOURCES WOR,
5775 WSM_COPY_OP_RESOURCE_USAGE WCORU
5776 WHERE WCORU.WIP_ENTITY_ID= p_wip_entity_id
5777 AND WCORU.Operation_seq_num = l_curr_op_seq_num
5778 AND WOR.WIP_ENTITY_ID= WCORU.WIP_ENTITY_ID
5779 AND WOR.Operation_seq_num= WCORU.Operation_seq_num
5780 AND WOR.RESOURCE_SEQ_NUM= WCORU.RESOURCE_SEQ_NUM;
5781 END IF;--End of check on sql%rowcount
5782
5783 l_stmt_num := 90;
5784
5785 WSMPOPRN.copy_to_op_mes_info(
5786 p_wip_entity_id =>p_wip_entity_id,
5787 p_to_job_op_seq_num =>l_op_seq_incr,
5788 p_to_rtg_op_seq_num =>l_curr_op_seq_num,
5789 p_txn_quantity =>l_txn_quantity,
5790 p_user =>p_created_by,
5791 p_login =>p_last_update_login,
5792 x_return_status =>l_return_status,
5793 x_msg_count =>l_msg_count,
5794 x_msg_data =>l_msg_data
5795 );
5796
5797 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
5798 x_err_code :=1;
5799 x_err_buf := l_msg_data;
5800 end if;
5801
5802 exception
5803 WHEN others THEN
5804 x_err_code := SQLCODE;
5805 x_err_buf := 'WSM_JobCopies_PVT.process_wip_info('||l_stmt_num||'): '||substrb(sqlerrm,1,1000);
5806 fnd_file.put_line(fnd_file.log, x_err_buf);
5807 END process_wip_info;
5808
5809 FUNCTION max_res_seq (p_op_seq_id NUMBER) return NUMBER
5810 IS
5811
5812 BEGIN
5813 return(v_max_res_seq(p_op_seq_id));
5814 END max_res_seq;
5815
5816 END WSM_JobCopies_PVT;