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