[Home] [Help]
PACKAGE BODY: APPS.CTO_SUBASSEMBLY_SUP_PK
Source
1 package body CTO_SUBASSEMBLY_SUP_PK as
2 /* $Header: CTOSUBSB.pls 120.16.12000000.2 2007/02/26 22:44:16 kkonada ship $ */
3
4 /*============================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 | Oracle Manufacturing |
8 +=============================================================================+
9 | |
10 | FILE NAME : CTOSUBSB.pls |
11 | |
12 | DESCRIPTION: |
13 | This file creates creates the sub-assembly supply
14 | Was created for ML Supply fetaure
15 | |
16 |
17 | |
18 | HISTORY : |
19 | 12-DEC-2002 Kiran Konada
20 | Intial Cretion
21 |
22 | 07-Jan-2003 Kiran Konada
23 | insert paramaeter first_unit_start in wjsi table
24 | when finite scheduler is on.
25 | also,caluclated job_start date for item when finite
26 | scheduler us ON AND top-most item is flow
27 | bugfix#2739590
28 |
29 |
30 | 20-JAn-2003 Kiran Konada
31 | bugfix 2755695
32 | Create a new mesage for a buy item
33 | a) when top-most item is flow
34 | CTO_SUB_LEVEL_BUY_ITEMS
35 |
36 b) when top-most item is discrete
37 | debug message in AFAS log file
38 | Created a new message when Discrete is under flow
39 | CTO_SUB_LEVEL_DISCRETE_REQ
40 |
41 | 24-Jan_2003 Kiran Konada
42 | bugfix 2755655 and 2756247
43 | added a outer joing bom_operational routings atbel
44 | if no routing is present, nvl(cfm_routing_flag to -99)
45 | modfied the if conditions to check for
46 | if(cfm_routing_flag = -99 or 2)
47 |
48 | 28-JAN-2003 Kiran Konada
49 | bugfix 2765109
50 | When a DIS/BUY sub-item is required at OP SEQ 1 of
51 | a flow parent . It's earliest required date would
52 | be scheduled start date of the first schedule
53 |
54 |
55 | 29-Jan-2003 Kiran Konada
56 | bugfix 2775097
57 | addded the effectivity date whil getting
58 | child configuration items
59 |
60 |
61 | 12-FEB-2003 Kiran Konada
62 | bugfix 2786582
63 | Get-operation_offset_date API requires line_op_Seq_id as input.
64 | bug: operation_Sequence_id was being passed
65 | fix: pass line_op_seq_id
66 |
67 | operation seq in BOM form belongs to EVENt aasocciated iwth flow
68 | routing.
69 | EVENT is usually associated to either line_operation (and/or) process
70 | If event is not assocaited to any line_opeartion , we wil get the
71 | component required at that particular event at the start of flow
72 | schedle
73 |
74 |
75 | 01-MAR-2002 Kiran Konada
76 | bugfix 2827357
77 | changed ceil to Floor as wipltesb.pls was using floor. Cto needs to be in sync
78 | with WIP calculations
79 |
80 |
81 | 01-MAR-2002 Kiran konada
82 | bugifx 2817556
83 | added a attribute 'comment' to record structure in spec CTOSUBSS.pls
84 | added new record and table r_consolidated_sub_item, t_cons_item_details
85 | Added a new procedure check_recurring_item
86 |
87 |
88 |
89 |
90 | 05-MAR-2002 Kiran Konada
91 | bugfix 2834244
92 | check for effectivity added
93 |
94 | 21-MAR-2002 Kiran
95 | 2858631
96 |
97 |
98 | 13-AUG-2003 Kiran Konada
99 for bug# 3063156
100 propagte bugfix 3042904 to main
101 | Passed project_id and task_id as parameters to populate_req_interface
102 |
103 |
104 |
105 |
106 | 26-AUg-2003 Kiran Konada
107 | changes for DMF-J
108 | becuase of mutiple sources enahcement
109 | sourcetype 66 (invalid sourcing) is not an error any more
110 |
111 |
112 | 03-NOV-2003 Kiran Konada
113 |
114 | Main propagation bug#3140641
115 |
116 | Reverting bugfix made on 13-AUG-2003. removing project-id and task_id as
117 | as parametrs to populate req interface
118 | Instead passing P_top_most_line_id as parameter as interface_sourc_line_id
119 | to populate_req_interafce. porject_id and task_id is calculated within pop
120 | ulate req_intreface. This is done to remove dependency on CTOPROCS.pls spec
121 | reverted bugfix 3042904 and provided
122 solution thru fix 3129117
123 | Has functional dependecy on CTOPROCB.pls
124 |
125 |
126 | 02-05-2004 Kiran Konada
127 | Bugfix# 3418102
128 | Project_id and task_id is passed to child cofniguration item supply
129 | only when item attribute end_pegging_flag is set to 'I','X'
130 |
131 | 02-03-2005 Kiran Konada
132 | BUG#4153987
133 | FP :11.5.9 - 11.5.10 : of 4134956
134 | With this fix CTO will consider the component yield factor
135 |
136 | 06-Jan-2006 Kiran Konada
137 | bugfix#4492875
138 | Removed the debug statement having sql%rowcount as parameter, which
139 | was immeditaly after sql statement and before if statement using sql%rowcount
140 |
141 | Reason : if there is a logic dependent on sql%rowcount and debug log statement before
142 | it uses sql%rowcount , then logic may go wrong
143 |
144 |
145 | 20-Feb-2006 Kiran Konada
146 | FP 5011199 base bug 4998922
147 | Look at only primary BOM's
148 |
149 | 22-Feb-2006 Kiran Konada
150 | bigfix 4615409
151 | get operation_lead_time percent from bom_operational_routings
152 | NOT from bom_inventory_components
153 |
154 |
155 | 23-Feb-2006 kiran Konada
156 | bugfix 5676839
157 | in FLM routing we should EVENTS onlu ie operation_type = 1
158 =============================================================================*/
159
160
161
162
163
164
165
166
167
168
169 TYPE r_flow_sch_details IS RECORD(
170 t_flow_sch_index number,
171 order_line_id number, --sales order_line_id
172 t_item_details_index number,
173 schedule_number wip_flow_schedules.schedule_number%type,
174 wip_entity_id wip_flow_schedules.wip_entity_id%type,
175 scheduled_start_date wip_flow_schedules.scheduled_start_date%type,
176 planned_quantity wip_flow_schedules.planned_quantity%type,
177 scheduled_completion_date wip_flow_schedules.scheduled_completion_date%type,
178 build_sequence wip_flow_schedules.build_sequence%type,
179 line_id wip_flow_schedules.line_id%type,
180 line_code wip_lines.line_code%type,
181 synch_schedule_num wip_flow_schedules.synch_schedule_num%type,
182 SYNCH_OPERATION_SEQ_NUM wip_flow_schedules.SYNCH_OPERATION_SEQ_NUM%type
183
184 );
185
186
187
188 TYPE r_consolidated_sub_item IS RECORD(
189 item_id number,
190 op_seq number,
191 -- commented element consolidate_qty number
192 consolidate_item_qty number, /* LBM change */
193 consolidate_lot_qty number /* LBM change */
194
195 );
196
197 TYPE t_flow_sch_details IS TABLE OF r_flow_sch_details INDEX BY BINARY_INTEGER;
198
199
200
201
202
203 TYPE t_cons_item_details IS TABLE OF r_consolidated_sub_item INDEX BY BINARY_INTEGER;
204
205
206
207
208
209 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
210
211 /*Procedure get_mlsupply_details( p_order_line_id IN number,
212 x_return_status out NOCOPY varchar2,
213 x_error_message out NOCOPY VARCHAR2,
214 x_message_name out NOCOPY VARCHAR2 ); */
215 Procedure process_phantoms
216 (
217 pitems_table in out nocopy t_item_details,
218 p_organization_id in number,
219 x_return_status out NOCOPY varchar2,
220 x_error_message out NOCOPY VARCHAR2, /* 70 bytes to hold msg */
221 x_message_name out NOCOPY VARCHAR2 /* 30 bytes to hold name */
222 );
223
224 PROCEDURE check_recurring_item
225 ( p_cons_item_details in out NOCOPY t_cons_item_details,
226 p_parent_item_id in number,
227 p_organization_id in number,
228 p_item_id in number,
229 x_min_op_seq_num out NOCOPY number,
230 x_comp_item_qty out NOCOPY number,
231 x_comp_lot_qty out NOCOPY number, /* LBM project */
232 x_oper_lead_time_per out NOCOPY number,
233 x_recurred_item_flag out NOCOPY number
234 )
235 is
236
237
238 v_recurr_flag varchar2(1) := 'N';
239 i number :=1 ;
240
241 l_index number;
242 l_bill_seq_id number;
243 l_stmt_num number;
244
245 BEGIN
246 IF PG_DEBUG <> 0 THEN
247 oe_debug_pub.add ('Entered check_recurred_item',1);
248 END IF;
249 x_recurred_item_flag := 1;
250 l_stmt_num :=10;
251 If (p_cons_item_details.count > 0) THEN--checks for unintialized collection
252 IF PG_DEBUG <> 0 THEN
253 oe_debug_pub.add ('inside the IF after checking for initialized collection',1);
254 END IF;
255
256 Loop
257 oe_debug_pub.add ('inside the loop aftr chking for intiliazed colelc',1);
258 l_stmt_num:=20;
259 if p_cons_item_details(i).item_id = p_item_id THEN
260 x_recurred_item_flag := 3; --recurred item
261 oe_debug_pub.add ('check_recureed_item, returning with status 3',1);
262 RETURN;--to calling program
263 END IF;
264
265 EXIT WHEN i =p_cons_item_details.LAST;
266
267 i := p_cons_item_details.NEXT(i);
268 END LOOP;
269
270 END IF;
271
272 l_stmt_num:=30;
273 IF ( v_recurr_flag = 'N') THEN
274
275 BEGIN
276
277 -- bugfix 4134956: take component yield factor into account.
278
279 /* LBM Project */
280 l_stmt_num :=40;
281 select SUM( decode( nvl(bic.basis_type,1), 1 , bic.COMPONENT_QUANTITY/bic.component_yield_factor, 0 )) ,
282 SUM( decode( nvl(bic.basis_type,1), 2 , bic.COMPONENT_QUANTITY/bic.component_yield_factor, 0 )) , 'Y'
283 INTO x_comp_item_qty , x_comp_lot_qty, v_recurr_flag
284 FROM BOM_INVENTORY_COMPONENTS bic,
285 bom_bill_of_materials bom
286 WHERE bic.bill_sequence_id = bom.common_bill_sequence_id
287 and bom.assembly_item_id = p_parent_item_id
288 and bom.organization_id = p_organization_id
289 AND bic.COMPONENT_ITEM_ID = p_item_id
290 and bic.effectivity_date <= sysdate --bugfix
291 and nvl(bic.disable_date,sysdate+1) > sysdate --2834244
292 and bom.ALTERNATE_BOM_DESIGNATOR is null --bug 4998922
293 GROUP BY bic.COMPONENT_ITEM_ID
294 HAVING COUNT(*) >1;
295 /* LBM Project */
296 exception
297 when no_data_found then
298 x_recurred_item_flag := 1;--single item not recurring
299 oe_debug_pub.add ('check_recureed_item, returning with status 1',1);
300 return;--to calling program
301 END;
302 END IF;
303
304 l_stmt_num :=60;
305 IF (v_recurr_flag = 'Y') THEN
306
307 x_recurred_item_flag := 2 ;--first item recurred
308 oe_debug_pub.add ('check_recureed_item, returning with status 2',1);
309
310 l_stmt_num :=70;
311 select min(OPERATION_SEQ_NUM)
312 into x_min_op_seq_num
313 FROM BOM_INVENTORY_COMPONENTS bic,
314 bom_bill_of_materials bom
315 WHERE bic.bill_sequence_id = bom.common_bill_sequence_id
316 and bom.assembly_item_id = p_parent_item_id
317 and bom.organization_id = p_organization_id
318 AND bic.COMPONENT_ITEM_ID = p_item_id
319 and bic.effectivity_date <= sysdate --bugfix
320 and nvl(bic.disable_date,sysdate+1) > sysdate --2834244
321 and bom.ALTERNATE_BOM_DESIGNATOR is null; --bug 4998922
322
323 l_stmt_num :=75;
324 IF PG_DEBUG <> 0 THEN
325 oe_debug_pub.add ('check_recureed_item, min op seq'|| x_min_op_seq_num,1);
326 END IF;
327
328 BEGIN
329 l_stmt_num :=80;
330 Select nvl(bos_p.OPERATION_LEAD_TIME_PERCENT,0)
331 INTO x_oper_lead_time_per
332 FROM BOM_INVENTORY_COMPONENTS bic,
333 bom_bill_of_materials bom,
334 --bugfix 4615409
335 bom_operational_routings bor_p,
336 bom_operation_sequences bos_p
337 WHERE bic.bill_sequence_id = bom.common_bill_sequence_id
338 and bom.assembly_item_id = p_parent_item_id
339 and bom.organization_id = p_organization_id
340 AND bic.COMPONENT_ITEM_ID = p_item_id
341 and bic.operation_seq_num = x_min_op_seq_num
342 and bic.effectivity_date <= sysdate --bugfix
343 and nvl(bic.disable_date,sysdate+1) > sysdate --2834244
344 and bom.ALTERNATE_BOM_DESIGNATOR is null --bug 4998922
345 --bugfix4615409
346 and bor_p.assembly_item_id = bom.assembly_item_id
347 and bor_p.organization_id = bom.organization_id
348 and bor_p.ALTERNATE_ROUTING_DESIGNATOR is null
349 and bos_p.routing_sequence_id = bor_p.common_routing_sequence_id
350 and bic.operation_seq_num=bos_p.operation_seq_num
351 and nvl(bos_p.operation_type,1)=1;--only events for FLM routing 5676839
352 EXCEPTION -- 5622588
353 WHEN no_data_found THEN
354 x_oper_lead_time_per := 0;
355 END;
356
357
358 IF PG_DEBUG <> 0 THEN
359 oe_debug_pub.add ('check_recureed_item, x_oper_lead_time_per'|| x_oper_lead_time_per ,1);
360 END IF;
361
362 l_stmt_num :=90;
363 If (p_cons_item_details.count > 0) THEN
364 l_stmt_num :=91;
365 IF PG_DEBUG <> 0 THEN
366 oe_debug_pub.add ('check_recureed_item,count more than 0',1);
367 END IF;
368 l_index := p_cons_item_details.LAST+1;
369 p_cons_item_details(l_index).item_id := p_item_id;
370 p_cons_item_details(l_index).op_seq := x_min_op_seq_num ;
371 /* LBM Project */
372 p_cons_item_details(l_index).consolidate_item_qty := x_comp_item_qty;
373 p_cons_item_details(l_index).consolidate_lot_qty := x_comp_lot_qty;
374 /* LBM Project */
375
376 ELSE
377 l_stmt_num :=92;
378 IF PG_DEBUG <> 0 THEN
379 oe_debug_pub.add ('check_recureed_item,first item',1);
380 END IF;
381 p_cons_item_details(1).item_id := p_item_id;
382 p_cons_item_details(1).op_seq := x_min_op_seq_num ;
383
384 -- commented code p_cons_item_details(1).consolidate_qty:= x_comp_item_qty;
385
386 /* LBM Project */
387 p_cons_item_details(1).consolidate_item_qty := x_comp_item_qty;
388 p_cons_item_details(1).consolidate_lot_qty := x_comp_lot_qty;
389 /* LBM Project */
390
391 END IF;
392 END IF;
393
394 EXCEPTION
395 WHEN FND_API.G_EXC_ERROR THEN
396 IF PG_DEBUG <> 0 THEN
397 oe_debug_pub.add('CTO_SUBASSEMBLY_SUP_PK: ' || 'check_recurring_item::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
398 END IF;
399 RAISE FND_API.G_EXC_ERROR;
400 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
401 IF PG_DEBUG <> 0 THEN
402 oe_debug_pub.add('CTO_SUBASSEMBLY_SUP_PK: ' || 'check_recurring_item::unexp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
403 END IF;
404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
405 WHEN OTHERS THEN
406 IF PG_DEBUG <> 0 THEN
407 oe_debug_pub.add('CTO_SUBASSEMBLY_SUP_PK: ' || 'check_recurring_item::other error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
408 END IF;
409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
410
411
412 END check_recurring_item;
413
414 PROCEDURE get_working_day
415 (porgid in number,
416 Pdate in date,
417 pleadtime in number,
418 pdirection in varchar2,
419 x_ret_date out NOCOPY date,
420 x_return_status out NOCOPY varchar2,
421 x_error_message out NOCOPY VARCHAR2, /* 70 bytes to hold msg */
422 x_message_name out NOCOPY VARCHAR2 /* 30 bytes to hold name */)
423
424 is
425 l_new_date date := null;
426
427 l_stmt_num number := 0;
428
429 BEGIN
430
431 x_return_status := FND_API.G_RET_STS_SUCCESS ;
432 IF PG_DEBUG <> 0 THEN
433 oe_debug_pub.add ('get_working_day: ' || 'Pdate=>' ||to_char(Pdate,'mm/dd/yyyy hh24:mi:ss'),5);
434 oe_debug_pub.add ('get_working_day: ' || 'trunc_Pdate=>' ||to_char(TRUNC(Pdate),'mm/dd/yyyy hh24:mi:ss'),5);
435 oe_debug_pub.add ('get_working_day: ' || 'pleadtime =>' ||pleadtime ,5);
436
437 END IF;
438
439 IF (pdirection = 'B') THEN
440
441 l_stmt_num := 10;
442
443 SELECT BCD1.CALENDAR_DATE into l_new_date
444 FROM BOM_CALENDAR_DATES BCD1,
445 BOM_CALENDAR_DATES BCD2,
446 MTL_PARAMETERS MP
447 WHERE MP.ORGANIZATION_ID = porgid
448 AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
449 AND BCD2.CALENDAR_CODE = MP.CALENDAR_CODE
450 AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
451 AND BCD2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
452 AND BCD2.CALENDAR_DATE = TRUNC(Pdate)
453 AND BCD1.SEQ_NUM = NVL(BCD2.SEQ_NUM, BCD2.PRIOR_SEQ_NUM) - pleadtime;
454 ELSIF(pdirection = 'F') THEN
455 SELECT BCD1.CALENDAR_DATE into l_new_date
456 FROM BOM_CALENDAR_DATES BCD1,
457 BOM_CALENDAR_DATES BCD2,
458 MTL_PARAMETERS MP
459 WHERE MP.ORGANIZATION_ID = porgid
460 AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
461 AND BCD2.CALENDAR_CODE = MP.CALENDAR_CODE
462 AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
463 AND BCD2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
464 AND BCD2.CALENDAR_DATE = TRUNC(Pdate)
465 AND BCD1.SEQ_NUM = NVL(BCD2.SEQ_NUM, BCD2.NEXT_SEQ_NUM) + pleadtime;
466 END IF;
467
468
469 x_ret_date := l_new_date + (Pdate - TRUNC(Pdate));
470 IF PG_DEBUG <> 0 THEN
471 null;
472 oe_debug_pub.add ('get_working_day: ' || 'l_new_date=>' ||to_char(l_new_date,'mm/dd/yyyy hh24:mi:ss'),5);
473 oe_debug_pub.add ('get_working_day: ' || 'trunc_Pdate=>' ||to_char(TRUNC(Pdate),'mm/dd/yyyy hh24:mi:ss'),5);
474 oe_debug_pub.add ('get_working_day: ' || ' x_ret_date=>' ||to_char(x_ret_date,'mm/dd/yyyy hh24:mi:ss'),5);
475
476
477 END IF;
478
479 EXCEPTION
480
481 when OTHERS then
482 x_return_status := FND_API.G_RET_STS_ERROR;
483 x_error_message := 'CTOSUBSB.get_working_day OTHERS excpn: ' || to_char(l_stmt_num)||':' ||
484 substrb(sqlerrm,1,100);
485 IF PG_DEBUG <> 0 THEN
486
487 oe_debug_pub.add('get_working_day: ' || 'CTOSUBSB.get_working_day OTHERS excpn: ' || x_error_message,1);
488
489 END IF;
490
491 /* OE_MSG_PUB.Add_Exc_Msg
492 ( G_PKG_NAME
493 , 'get_working_day'
494 ); */
495
496
497
498
499 END get_working_day;
500
501
502
503
504 /*
505 start date is calculated based on fixed and vaiable lead time
506
507
508
509 */
510
511
512 Procedure get_start_date( pCompletion_date in date,
513 pQty in number,
514 pitemid in number,
515 porganization_id in number,
516 pfixed_leadtime in number,
517 pvariable_leadtime in number,
518 x_start_date out NOCOPY date,
519 x_return_status out NOCOPY varchar2,
520 x_error_message out NOCOPY VARCHAR2, /* 70 bytes to hold msg */
521 x_message_name out NOCOPY VARCHAR2 /* 30 bytes to hold name */)
522 is
523
524 l_return_status varchar2(1) := null;
525 l_error_msg varchar2(1000) := null;
526 l_msg_name varchar2(30) := null;
527
528 l_total_lead_time number :=0;
529 l_stmt_num number := 0;
530
531
532
533 BEGIN
534 x_return_status := FND_API.G_RET_STS_SUCCESS ;
535
536
537
538 l_total_lead_time := CEIL( nvl(pfixed_leadtime,0) +( pQty * nvl(pvariable_leadtime,0) ) ); --bugfix 2827357
539
540 IF PG_DEBUG <> 0 THEN
541
542 oe_debug_pub.add ('get_start_date: ' || 'pCompletion_date=>' ||to_char(pCompletion_date,'mm/dd/yyyy hh24:mi:ss'),5);
543 oe_debug_pub.add ('get_start_date: ' || 'l_total_lead_time=>' ||l_total_lead_time,5);
544
545
546 END IF;
547
548 IF ( l_total_lead_time <> 0 ) THEN
549
550 l_stmt_num := 20;
551 get_working_day
552 (
553 porgid => porganization_id ,
554 Pdate => pCompletion_date ,
555 pleadtime => l_total_lead_time ,
556 pdirection =>'B', --direction in getting working day 'backward
557 x_ret_date => x_start_date,
558 x_return_status => l_return_status,
559 x_error_message => l_error_msg,
560 x_message_name => l_msg_name
561
562 );
563
564 /* if ( l_return_status <> FND_API.G_RET_STS_SUCCESS) then
565 IF PG_DEBUG <> 0 THEN
566 oe_debug_pub.add ('get_start_date: ' || 'get_start_date: failed after call to get_working_day with status ' || l_return_status ,1);
567
568 oe_debug_pub.add ('get_start_date: ' || 'error message' || l_error_msg ,1);
569 END IF;
570 RAISE subprogram_exp ;
571
572 end if; */
573
574 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
575 IF PG_DEBUG <> 0 THEN
576 oe_debug_pub.add ('get_start_date: ' || 'get_start_date: failed after call to get_working_day with status ' || l_return_status ,1);
577
578 oe_debug_pub.add ('get_start_date: ' || 'error message' || l_error_msg ,1);
579 END IF;
580 RAISE FND_API.G_EXC_ERROR;
581 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
582 IF PG_DEBUG <> 0 THEN
583 oe_debug_pub.add ('get_start_date: ' || 'get_start_date: failed after call to get_working_day with status ' || l_return_status ,1);
584
585 oe_debug_pub.add ('get_start_date: ' || 'error message' || l_error_msg ,1);
586 END IF;
587 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
588 ELSE
589
590 IF PG_DEBUG <> 0 THEN
591 oe_debug_pub.add('get_start_date: ' || 'success from get_working_day ' ,1);
592 END IF;
593 END IF;
594
595
596 ELSE
597 x_start_date := pCompletion_date;
598
599 END IF;
600
601 IF PG_DEBUG <> 0 THEN
602 oe_debug_pub.add ('get_start_date: ' || 'x_start_date=>' ||to_char(x_start_date,'mm/dd/yyyy hh24:mi:ss'),5);
603
604 END IF;
605
606 EXCEPTION
607
608
609 when FND_API.G_EXC_ERROR then
610
611 x_return_status := FND_API.G_RET_STS_ERROR;
612 x_error_message := 'CTOSUBSB.get_start_date expected excpn: '|| ':' ||
613 substrb(sqlerrm,1,100) ;
614
615
616 IF PG_DEBUG <> 0 THEN
617 oe_debug_pub.add('get_start_date: ' || 'CTOSUBSB.get_start_date expected excpn: ' || x_error_message,1);
618 END IF;
619
620
621
622 when FND_API.G_EXC_UNEXPECTED_ERROR then
623 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
624 x_error_message := 'CTOSUBSB.get_start_date UN expected excpn: '|| ':' ||
625 substrb(sqlerrm,1,100) ;
626
627
628 IF PG_DEBUG <> 0 THEN
629 oe_debug_pub.add('get_start_date: ' || 'CTOSUBSB.get_start_date UN expected excpn: ' || x_error_message,1);
630 END IF;
631
632
633
634
635 when OTHERS then
636 x_return_status := FND_API.G_RET_STS_ERROR;
637 x_error_message := 'CTOSUBSB.get_start_date OTHERS excpn: '|| ':' ||
638 substrb(sqlerrm,1,100) ;
639
640
641 IF PG_DEBUG <> 0 THEN
642 oe_debug_pub.add('get_start_date: ' || 'CTOSUBSB.get_start_date OTHERS excpn: ' || x_error_message,1);
643 END IF;
644
645
646 /* OE_MSG_PUB.Add_Exc_Msg
647 ( G_PKG_NAME
648 , 'get_working_day'
649 ); */
650
651
652
653
654
655
656 END get_start_date;
657
658
659
660
661 /*
662
663 completion date for make items is calculated
664 */
665
666
667 Procedure get_completion_date( pParent_job_start_date in date,
668 porganization_id in number,
669 plead_time_offset_percent in number,
670 pParent_processing_lead_time in number,
671 ppostprocessing_time in number , --valid for buy item only
672 pSource_type in number, --buy =3, make =2
673 x_child_completion_date out NOCOPY date,
674 x_return_status out NOCOPY varchar2,
675 x_error_message out NOCOPY VARCHAR2, /* 70 bytes to hold msg */
676 x_message_name out NOCOPY VARCHAR2 /* 30 bytes to hold name */)
677 is
678 l_index number;
679 l_parent_job_start_date date;
680
681 l_return_status varchar2(10) := null;
682 l_error_msg varchar2(1000) := null;
683 l_msg_name varchar2(30) := null;
684
685 l_total_lead_time number :=0;
686
687
688 l_zero number :=0;
689 l_stmt_num number := 0;
690
691
692 BEGIN
693
694 x_return_status := FND_API.G_RET_STS_SUCCESS ;
695
696 IF ( pSource_type = 2) THEN -- make item
697
698 l_total_lead_time := (plead_time_offset_percent/100)*pParent_processing_lead_time;
699 ELSIF ( pSource_type = 3) THEN --buy
700 l_total_lead_time := ( ((plead_time_offset_percent/100)*pParent_processing_lead_time )
701 - ppostprocessing_time -1
702 );
703
704 END IF;
705
706 IF l_total_lead_time>0 THEN
707
708 l_total_lead_time := ceil(l_total_lead_time);
709
710
711 l_stmt_num := 30;
712 get_working_day
713 (
714 porgid => porganization_id ,
715 Pdate => pParent_job_start_date ,
716 pleadtime => l_total_lead_time,
717 pdirection => 'F', --direction in getting working day 'Forward'
718 x_ret_date => x_child_completion_date,
719 x_return_status => l_return_status,
720 x_error_message => l_error_msg,
721 x_message_name => l_msg_name
722
723 );
724
725 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
726 IF PG_DEBUG <> 0 THEN
727 oe_debug_pub.add ('get_completion_date: ' || ' failed in call to get_working_day with status ' || l_return_status ,1);
728
729 oe_debug_pub.add ('get_completion_date: ' || 'error message' || l_error_msg ,1);
730 END IF;
731 RAISE FND_API.G_EXC_ERROR;
732 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
733 IF PG_DEBUG <> 0 THEN
734 oe_debug_pub.add ('get_completion_date: ' || 'get_start_date: failed after call to get_working_day with status ' || l_return_status ,1);
735
736 oe_debug_pub.add ('get_completion_date: ' || 'error message' || l_error_msg ,1);
737 END IF;
738 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
739 ELSE
740
741 IF PG_DEBUG <> 0 THEN
742 oe_debug_pub.add('get_completion_date: ' || 'success from get_working_day ' ,1);
743 END IF;
744 END IF;
745 ELSIF ( l_total_lead_time < 0 ) THEN
746 l_total_lead_time := FLOOR(l_total_lead_time);
747 l_total_lead_time := abs(l_total_lead_time);--2858631
748
749 l_stmt_num := 31;
750 get_working_day
751 (
752 porgid => porganization_id ,
753 Pdate => pParent_job_start_date ,
754 pleadtime => l_total_lead_time,
755 pdirection => 'B', --direction in getting working day 'Forward'
756 x_ret_date => x_child_completion_date,
757 x_return_status => l_return_status,
758 x_error_message => l_error_msg,
759 x_message_name => l_msg_name
760
761 );
762
763 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
764 IF PG_DEBUG <> 0 THEN
765 oe_debug_pub.add ('get_completion_date: ' || ' failed in call to get_working_day with status ' || l_return_status ,1);
766
767 oe_debug_pub.add ('get_completion_date: ' || 'error message' || l_error_msg ,1);
768 END IF;
769 RAISE FND_API.G_EXC_ERROR;
770 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
771 IF PG_DEBUG <> 0 THEN
772 oe_debug_pub.add ('get_completion_date: ' || 'get_start_date: failed after call to get_working_day with status ' || l_return_status ,1);
773
774 oe_debug_pub.add ('get_completion_date: ' || 'error message' || l_error_msg ,1);
775 END IF;
776 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
777 ELSE
778
779 IF PG_DEBUG <> 0 THEN
780 oe_debug_pub.add('get_completion_date: ' || 'success from get_working_day ' ,1);
781 END IF;
782 END IF; --PO return status
783
784
785
786
787 ELSE
788 x_child_completion_date := pParent_job_start_date;
789
790 END IF;
791
792
793 EXCEPTION
794
795 when FND_API.G_EXC_ERROR then
796
797 x_return_status := FND_API.G_RET_STS_ERROR;
798 x_error_message := 'CTOSUBSB.get_completion_date expected excpn: ' || to_char(l_stmt_num)|| ':' ||
799 substrb(sqlerrm,1,100);
800
801
802
803 IF PG_DEBUG <> 0 THEN
804 oe_debug_pub.add('get_completion_date: ' || 'CTOSUBSB.get_completion_date expected excpn: ' || x_error_message,1);
805 END IF;
806
807
808
809 when FND_API.G_EXC_UNEXPECTED_ERROR then
810 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
811 x_error_message := 'CTOSUBSB.get_completion_date UN expected excpn: ' || to_char(l_stmt_num)|| ':' ||
812 substrb(sqlerrm,1,100);
813
814
815
816 IF PG_DEBUG <> 0 THEN
817 oe_debug_pub.add('get_completion_date: ' || 'CTOSUBSB.get_completion_date UN expected excpn: ' || x_error_message,1);
818 END IF;
819
820
821
822
823
824 when OTHERS then
825 x_return_status := FND_API.G_RET_STS_ERROR;
826 x_error_message := 'CTOSUBSB.get_start_date OTHERS excpn: ' || to_char(l_stmt_num)|| ':' ||
827 substrb(sqlerrm,1,100);
828
829
830 IF PG_DEBUG <> 0 THEN
831 oe_debug_pub.add('get_completion_date: ' || 'CTOSUBSB.get_start_date OTHERS excpn: ' || x_error_message,1);
832 END IF;
833
834
835 /* OE_MSG_PUB.Add_Exc_Msg
836 ( G_PKG_NAME
837 , 'get_working_day'
838 ); */
839
840
841
842
843
844
845 END get_completion_date;
846
847
848
849 /*
850 cursor for autocreated config
851 cursor for ato and confiured items
852 sourcing rule check
853
854
855
856 */
857
858
859 Procedure get_child_configurations
860 (
861 pParentItemId in number,
862 pOrganization_id in number,
863 pLower_Supplytype in number, --may need to change after sajnai codes 1= autocreated 2= autocreated and ATo items
864 pParent_index in number,
865 pitems_table in out nocopy t_item_details,
866 x_return_status out NOCOPY varchar2,
867 x_error_message out NOCOPY VARCHAR2, /* 70 bytes to hold msg */
868 x_message_name out NOCOPY VARCHAR2 /* 30 bytes to hold name */
869
870
871
872 )
873
874 is
875 lComponent_item_id number :=0;
876 lComponent_quantity number :=0;
877
878
879 v_sourcing_rule_exists VARCHAR2(100);
880 v_sourcing_org NUMBER;
881 v_source_type NUMBER;
882 v_transit_lead_time NUMBER;
883 v_exp_error_code NUMBER;
884
885 -- x_return_status VARCHAR2(1);
886
887 l_stmt_num number := 0;
888
889 --cursor for configured items
890 CURSOR c_config_items IS
891 select component_item_id,
892 msi.concatenated_segments,
893 component_quantity/bic.component_yield_factor, -- bugfix 4134956: take component_yield_factor into account
894 bic.operation_seq_num,
895 nvl(bor.cfm_routing_flag,-99), --default to -99 if no routing and treat it as discrete
896 bor.routing_sequence_id,
897 nvl(msi.fixed_lead_time,0),
898 nvl(msi.variable_lead_time,0),
899 nvl(msi.full_lead_time,0),
900 nvl(msi.postprocessing_lead_time,0),
901 bic.bom_item_type,
902 msi.auto_created_config_flag,
903 bor.line_id,
904 wil.line_code,
905 end_assembly_pegging_flag, --Bugfix# 3418102
906 nvl(bic.basis_type,1), /* LBM Project */
907 bic.wip_supply_type --4645636
908 from bom_inventory_components bic,
909 bom_bill_of_materials bom,
910 mtl_System_items_kfv msi,
911 --mtl_system_items msi,
912 bom_operational_routings bor,
913 wip_lines wil
914 --bugfix 4615409
915 --bom_operational_routings bor_p,--parent
916 --bom_operation_sequences bos_p
917 where bic.bill_sequence_id = bom.common_bill_sequence_id
918 and bom.assembly_item_id = pParentItemId
919 and bom.organization_id = pOrganization_id
920 and bic.component_item_id = msi.inventory_item_id
921 and bic.effectivity_date <= sysdate --bugfix
922 and nvl(bic.disable_date,sysdate+1) > sysdate --2775097
923 and msi.organization_id = pOrganization_id
924 and bor.assembly_item_id (+)= bic.component_item_id
925 and bor.ALTERNATE_ROUTING_DESIGNATOR(+) is null
926 and bor.organization_id (+) = pOrganization_id
927 and bor.line_id = wil.line_id(+)
928 and msi.auto_created_config_flag = 'Y'
929 and bom.ALTERNATE_BOM_DESIGNATOR is null; --bug 4998922
930
931
932
933 CURSOR c_config_and_ato_items IS
934 select component_item_id,
935 msi.concatenated_segments,
936 component_quantity/bic.component_yield_factor, -- bugfix 4134956: take component_yield_factor into account
937 bic.operation_seq_num,
938 nvl(bor.cfm_routing_flag,-99), --default to -99 if no routing and treat it as discrete
939 bor.routing_sequence_id,
940 nvl(msi.fixed_lead_time,0),
941 nvl(msi.variable_lead_time,0),
942 nvl(msi.full_lead_time,0),
943 nvl(msi.postprocessing_lead_time,0),
944 bic.bom_item_type,
945 msi.auto_created_config_flag,
946 bor.line_id,
947 wil.line_code,
948 end_assembly_pegging_flag, --Bugfix# 3418102
949 nvl(bic.basis_type,1),/* LBM Project */
950 bic.wip_supply_type --4645636
951 from bom_inventory_components bic,
952 bom_bill_of_materials bom,
953 mtl_System_items_kfv msi,
954 --mtl_System_items_b msi,
955 bom_operational_routings bor,
956 wip_lines wil
957 --bugfix 4615409
958 --bom_operational_routings bor_p,--parent
959 --bom_operation_sequences bos_p
960 where bic.bill_sequence_id = bom.common_bill_sequence_id
961 and bom.assembly_item_id = pParentItemId
962 and bom.organization_id = pOrganization_id
963 and bic.component_item_id = msi.inventory_item_id
964 and bic.effectivity_date <= sysdate --bugfix
965 and nvl(bic.disable_date,sysdate+1) > sysdate --2775097
966 and msi.organization_id = pOrganization_id
967 and bor.assembly_item_id (+) = bic.component_item_id
968 and bor.ALTERNATE_ROUTING_DESIGNATOR(+) is null
969 and bor.organization_id (+) = pOrganization_id
970 and bor.line_id = wil.line_id(+)
971 and msi.replenish_to_order_flag = 'Y'
972 and bic.bom_item_type = 4
973 and bom.ALTERNATE_BOM_DESIGNATOR is null; --bug 4998922
974
975
976 l_index number;
977
978 l_ret_status varchar2(1) := null;
979 l_error_message varchar2(1000) := null;
980 l_msg_name varchar2(30) := null;
981
982 p_cons_item_details t_cons_item_details;
983
984 l_min_op_seq_num number;
985 l_comp_item_qty number;
986 l_comp_lot_qty number;
987 l_oper_lead_time_per number;
988 l_recurred_item_flag number;
989
990 BEGIN
991
992 x_return_status := FND_API.G_RET_STS_SUCCESS ;
993
994 IF pLower_Supplytype = 2 THEN
995
996 IF PG_DEBUG <> 0 THEN
997 oe_debug_pub.add ('get_child_configurations: ' || 'Config children alone' ,1);
998 cto_wip_workflow_api_pk.cto_debug('get_child_configurations: ','Config children alone' );
999 END IF;
1000
1001
1002 OPEN c_config_items;
1003
1004 l_stmt_num := 40;
1005 LOOP
1006 l_index := pitems_table.last+1;
1007 FETCH c_config_items INTO pitems_table(l_index).item_id,
1008 pitems_table(l_index).item_name,
1009 pitems_table(l_index).item_quantity,
1010 -- pitems_table(l_index).operation_lead_time_percent,
1011 pitems_table(l_index).operation_seq_num,
1012 pitems_table(l_index).cfm_routing_flag,
1013 pitems_table(l_index).routing_sequence_id,
1014 pitems_table(l_index).fixed_lead_time,
1015 pitems_table(l_index).variable_lead_time,
1016 pitems_table(l_index).processing_lead_time,
1017 pitems_table(l_index). postprocessing_lead_time,
1018 pitems_table(l_index).bom_item_type,
1019 pitems_table(l_index).auto_config_flag,
1020 pitems_table(l_index).line_id,
1021 pitems_table(l_index).line_code,
1022 pitems_table(l_index).pegging_flag,--Bugfix# 3418102
1023 pitems_table(l_index).basis_type,/* LBM Project */
1024 pitems_table(l_index).wip_supply_type; --4645636
1025
1026 EXIT when c_config_items%notfound;
1027
1028 IF PG_DEBUG <> 0 THEN
1029 oe_debug_pub.add ('get_child_configurations: ' || 'item added' || pitems_table(l_index).item_name ,1);
1030
1031 --Bugfix# 3418102
1032 oe_debug_pub.add ('get_child_configurations: ' || 'end_peeging_flag for project information' || pitems_table(l_index).pegging_flag ,3);
1033 END IF;
1034
1035 -- 5198966
1036 BEGIN
1037 select nvl(bos_p.OPERATION_LEAD_TIME_PERCENT,0)
1038 INTO pitems_table(l_index).operation_lead_time_percent
1039 from bom_operational_routings bor_p,--parent
1040 bom_operation_sequences bos_p
1041 where bor_p.assembly_item_id = pParentItemId
1042 and bor_p.organization_id = pOrganization_id
1043 and bor_p.ALTERNATE_ROUTING_DESIGNATOR is null
1044 and bos_p.routing_sequence_id = bor_p.common_routing_sequence_id
1045 and bos_p.operation_seq_num = pitems_table(l_index).operation_seq_num
1046 and nvl(bos_p.operation_type,1)=1; --consider events only for FLM cases.5676839
1047 Exception
1048 WHEN no_data_found then
1049 pitems_table(l_index).operation_lead_time_percent := 0;
1050 END;
1051
1052 pitems_table(l_index).parent_index := pParent_index;
1053 pitems_table(l_index).feeder_run := 'N';
1054
1055
1056 l_stmt_num := 50;
1057 CTO_UTILITY_PK.QUERY_SOURCING_ORG(
1058 P_inventory_item_id => pitems_table(l_index).item_id,
1059 P_organization_id => pOrganization_id,
1060 P_sourcing_rule_exists => v_sourcing_rule_exists,
1061 P_source_type => v_source_type,
1062 P_sourcing_org => v_sourcing_org,
1063 P_transit_lead_time => v_transit_lead_time,
1064 X_exp_error_code => v_exp_error_code,
1065 X_return_status =>x_return_status
1066 );
1067
1068 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1069 IF PG_DEBUG <> 0 THEN
1070 oe_debug_pub.add ('get_child_configurations: ' || 'failed after call to CTO_UTILITY_PK.QUERY_SOURCING_ORG with status ' || x_return_status ,1);
1071 END IF;
1072 --oe_debug_pub.add ('error message' || l_error_msg ,1);
1073 RAISE FND_API.G_EXC_ERROR;
1074 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1075 IF PG_DEBUG <> 0 THEN
1076 oe_debug_pub.add ('get_child_configurations: ' || ' failed after call to CTO_UTILITY_PK.QUERY_SOURCING_ORG with status ' || x_return_status ,1);
1077 END IF;
1078 --oe_debug_pub.add ('error message' || l_error_msg ,1);
1079 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1080 ELSE
1081
1082 IF PG_DEBUG <> 0 THEN
1083 oe_debug_pub.add('get_child_configurations: ' || 'success from CTO_UTILITY_PK.QUERY_SOURCING_ORG ' ,1);
1084 END IF;
1085 END IF;
1086
1087
1088 --by Kiran Konada
1089 --removed if block for multiple sources
1090 -- rkaza. 05/02/2005. Adding sourcing org also.
1091 pitems_table(l_index).source_type := v_source_type;
1092 pitems_table(l_index).sourcing_org := v_sourcing_org;
1093
1094
1095
1096 --call recurring item only
1097 --if item is not multiple sources AND
1098 --parent is flow AND
1099 --if item is buy or discrete or 100% transfer
1100 -- rkaza. 05/02/2005. Adding 100% transfer cases.
1101
1102 IF (
1103 pitems_table(l_index).source_type <> 66 AND
1104 pitems_table(pParent_index).cfm_routing_flag = 1
1105 AND
1106 (pitems_table(l_index).source_type = 3
1107 OR
1108 pitems_table(l_index).source_type = 1
1109 OR
1110 pitems_table(l_index).cfm_routing_flag <> 1
1111 )
1112 ) THEN
1113 oe_debug_pub.add ('calling check_recurring_item' ,1);
1114 check_recurring_item
1115 ( p_cons_item_details => p_cons_item_details,
1116 p_parent_item_id => pitems_table(pParent_index).item_id,
1117 p_organization_id => pOrganization_id,
1118 p_item_id => pitems_table(l_index).item_id,
1119 x_min_op_seq_num => l_min_op_seq_num ,
1120 x_comp_item_qty => l_comp_item_qty ,
1121 x_comp_lot_qty => l_comp_lot_qty , /* LBM Project */
1122 x_oper_lead_time_per => l_oper_lead_time_per,
1123 x_recurred_item_flag => l_recurred_item_flag
1124 );
1125 ELSE
1126 --if parent is not flow default recurre_item_flag to 1
1127 --so that standard processing takes place
1128 l_recurred_item_flag := 1;
1129
1130 END IF;
1131
1132 IF (l_recurred_item_flag = 1) THEN
1133 --
1134 -- begin bugfix 4134956: item_quantity has component_yield_factor taken into account.
1135 -- Round to 6 decimal places
1136 --
1137 /* LBM Project */
1138 if( pitems_table(l_index).basis_type = 1) /* Item Basis */
1139 then
1140 pitems_table(l_index).needed_item_qty :=
1141 round (pitems_table(pParent_index).needed_item_qty * pitems_table(l_index).item_quantity, 6);
1142 else
1143 pitems_table(l_index).needed_item_qty :=
1144 round( pitems_table(l_index).item_quantity, 6);
1145 end if;
1146
1147 --immediate parent's calculate supply quantity * childs bic component qty
1148 END IF;
1149 IF (l_recurred_item_flag = 2) THEN
1150 --
1151 -- begin bugfix 4134956: l_comp_item_qty has component_yield_factor taken into account.
1152 -- Round to 6 decimal places
1153 --
1154 /* LBM change */
1155 pitems_table(l_index).needed_item_qty :=
1156 round( ( ( pitems_table(pParent_index).needed_item_qty * l_comp_item_qty ) + l_comp_lot_qty ) , 6) ;
1157
1158 pitems_table(l_index).operation_seq_num := l_min_op_seq_num ;
1159 pitems_table(l_index).operation_lead_time_percent := l_oper_lead_time_per;
1160
1161
1162 END IF;
1163
1164 IF (l_recurred_item_flag = 3) THEN
1165 pitems_table(l_index).needed_item_qty := 0;
1166 pitems_table(l_index).comment := pitems_table(l_index).comment ||'This items supply has been consolidated';
1167 END IF;
1168
1169
1170
1171 IF ( pitems_table(l_index).source_type = 2
1172 and
1173 pitems_table(l_index).needed_item_qty > 0
1174 ) THEN
1175
1176 l_stmt_num := 60;
1177 get_child_configurations
1178 ( pParentItemId => pitems_table(l_index).item_id,
1179 pOrganization_id => pOrganization_id,
1180 pLower_Supplytype => pLower_Supplytype,
1181 pParent_index => l_index,--passing index# as parentid for children
1182 pitems_table => pitems_table,
1183 x_return_status => l_ret_status,
1184 x_error_message => l_error_message,
1185 x_message_name => l_msg_name
1186
1187 );
1188
1189 END IF; --source type and needed_item_qty
1190
1191
1192 END LOOP;
1193 Close c_config_items;
1194
1195 ELSIF pLower_Supplytype = 3 THEN
1196
1197
1198 If PG_DEBUG <> 0 Then
1199 cto_wip_workflow_api_pk.cto_debug('get_child_configurations: ','Config and ato item children' );
1200 End if;
1201 OPEN c_config_and_ato_items;
1202
1203 l_stmt_num := 70;
1204 LOOP
1205 l_index := pitems_table.last+1;
1206
1207 FETCH c_config_and_ato_items INTO pitems_table(l_index).item_id,
1208 pitems_table(l_index).item_name,
1209 pitems_table(l_index).item_quantity,
1210 -- pitems_table(l_index).operation_lead_time_percent,
1211 pitems_table(l_index).operation_seq_num,
1212 pitems_table(l_index).cfm_routing_flag,
1213 pitems_table(l_index).routing_sequence_id,
1214 pitems_table(l_index).fixed_lead_time,
1215 pitems_table(l_index).variable_lead_time,
1216 pitems_table(l_index).processing_lead_time,
1217 pitems_table(l_index). postprocessing_lead_time,
1218 pitems_table(l_index).bom_item_type,
1219 pitems_table(l_index).auto_config_flag,
1220 pitems_table(l_index).line_id,
1221 pitems_table(l_index).line_code,
1222 pitems_table(l_index).pegging_flag,
1223 pitems_table(l_index).basis_type, /* LBM Project */
1224 pitems_table(l_index).wip_supply_type; --4645636
1225
1226
1227
1228 EXIT when c_config_and_ato_items%notfound;
1229 IF PG_DEBUG <> 0 THEN
1230 oe_debug_pub.add ('get_child_configurations: ' || 'item added' || pitems_table(l_index).item_name ,1);
1231
1232 --Bugfix# 3418102
1233 oe_debug_pub.add ('get_child_configurations: ' || 'end_pegging_flag for project information' || pitems_table(l_index).pegging_flag ,3);
1234 END IF;
1235
1236 -- 5198966
1237 BEGIN
1238 select nvl(bos_p.OPERATION_LEAD_TIME_PERCENT,0)
1239 INTO pitems_table(l_index).operation_lead_time_percent
1240 from bom_operational_routings bor_p,--parent
1241 bom_operation_sequences bos_p
1242 where bor_p.assembly_item_id = pParentItemId
1243 and bor_p.organization_id = pOrganization_id
1244 and bor_p.ALTERNATE_ROUTING_DESIGNATOR is null
1245 and bos_p.routing_sequence_id = bor_p.common_routing_sequence_id
1246 and bos_p.operation_seq_num = pitems_table(l_index).operation_seq_num
1247 and nvl(bos_p.operation_type,1)=1; --consider events only for FLM cases.5676839
1248 Exception
1249 WHEN no_data_found THEN
1250 pitems_table(l_index).operation_lead_time_percent := 0;
1251 END;
1252
1253 pitems_table(l_index).parent_index := pParent_index;
1254 pitems_table(l_index).feeder_run := 'N';
1255
1256 l_stmt_num := 80;
1257 CTO_UTILITY_PK.QUERY_SOURCING_ORG(
1258 P_inventory_item_id => pitems_table(l_index).item_id,
1259 P_organization_id => pOrganization_id,
1260 P_sourcing_rule_exists => v_sourcing_rule_exists,
1261 P_source_type => v_source_type,
1262 P_sourcing_org => v_sourcing_org,
1263 P_transit_lead_time => v_transit_lead_time,
1264 X_exp_error_code => v_exp_error_code,
1265 X_return_status =>x_return_status
1266 );
1267
1268
1269 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1270 IF PG_DEBUG <> 0 THEN
1271 oe_debug_pub.add ('get_child_configurations: ' || 'failed after call to CTO_UTILITY_PK.QUERY_SOURCING_ORG with status ' || x_return_status ,1);
1272 END IF;
1273 --oe_debug_pub.add ('error message' || l_error_msg ,1);
1274 RAISE FND_API.G_EXC_ERROR;
1275 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1276 IF PG_DEBUG <> 0 THEN
1277 oe_debug_pub.add ('get_child_configurations: ' || ' failed after call to CTO_UTILITY_PK.QUERY_SOURCING_ORG with status ' || x_return_status ,1);
1278 END IF;
1279 --oe_debug_pub.add ('error message' || l_error_msg ,1);
1280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1281 ELSE
1282
1283 IF PG_DEBUG <> 0 THEN
1284 oe_debug_pub.add('get_child_configurations: ' || 'success from CTO_UTILITY_PK.QUERY_SOURCING_ORG ' ,1);
1285 END IF;
1286 END IF;
1287
1288
1289 --by Kiran Konada
1290 --removed if block for multiple sources
1291 -- rkaza. 05/02/2005. Adding sourcing org also.
1292 pitems_table(l_index).source_type := v_source_type;
1293 pitems_table(l_index).sourcing_org := v_sourcing_org;
1294
1295
1296 --call recurring item only
1297 --if item is not multiple sources AND
1298 --parent is flow AND
1299 --if item is buy or discrete or 100% transfer
1300 -- rkaza. 05/02/2005. Added 100% transfer.
1301
1302 IF (
1303 pitems_table(l_index).source_type <> 66 AND
1304 pitems_table(pParent_index).cfm_routing_flag = 1
1305 AND
1306 (pitems_table(l_index).source_type = 3
1307 OR
1308 pitems_table(l_index).source_type = 1
1309 OR
1310 pitems_table(l_index).cfm_routing_flag <> 1
1311 )
1312 ) THEN
1313 oe_debug_pub.add ('calling check_recurring_item for item ' ,1);
1314 check_recurring_item
1315 ( p_cons_item_details => p_cons_item_details,
1316 p_parent_item_id => pitems_table(pParent_index).item_id,
1317 p_organization_id => pOrganization_id,
1318 p_item_id => pitems_table(l_index).item_id,
1319 x_min_op_seq_num => l_min_op_seq_num ,
1320 x_comp_item_qty => l_comp_item_qty ,
1321 x_comp_lot_qty => l_comp_lot_qty , /* LBM Project */
1322 x_oper_lead_time_per => l_oper_lead_time_per,
1323 x_recurred_item_flag => l_recurred_item_flag
1324 );
1325 ELSE
1326 --if parent is not flow default recurre_item_flag to 1
1327 --so that standard processing takes place
1328 l_recurred_item_flag := 1;
1329
1330 END IF;
1331
1332 IF (l_recurred_item_flag = 1) THEN
1333 --
1334 -- begin bugfix 4134956: item_quantity has component_yield_factor taken into account.
1335 -- Round to 6 decimal places
1336 --
1337
1338 /* LBM Project */
1339 if( pitems_table(l_index).basis_type = 1) /* Item Basis */
1340 then
1341 pitems_table(l_index).needed_item_qty :=
1342 round (pitems_table(pParent_index).needed_item_qty * pitems_table(l_index).item_quantity, 6);
1343 else
1344 pitems_table(l_index).needed_item_qty :=
1345 round( pitems_table(l_index).item_quantity, 6);
1346 end if;
1347
1348
1349
1350
1351 --immediate parent's calculate supply quantity * childs bic component qty
1352
1353 END IF;
1354 IF (l_recurred_item_flag = 2) THEN
1355 --
1356 -- begin bugfix 4134956: l_comp_item_qty has component_yield_factor taken into account.
1357 -- Round to 6 decimal places
1358 --
1359 /* LBM Project */
1360 pitems_table(l_index).needed_item_qty :=
1361 round( ((pitems_table(pParent_index).needed_item_qty * l_comp_item_qty) + l_comp_lot_qty ) , 6) ;
1362 pitems_table(l_index).operation_seq_num := l_min_op_seq_num ;
1363 pitems_table(l_index).operation_lead_time_percent := l_oper_lead_time_per;
1364
1365 END IF;
1366
1367 IF (l_recurred_item_flag = 3) THEN
1368 pitems_table(l_index).needed_item_qty := 0;
1369 pitems_table(l_index).comment := pitems_table(l_index).comment ||'This items supply has been consolidated';
1370 END IF;
1371
1372
1373
1374 IF ( pitems_table(l_index).source_type = 2
1375 and
1376 pitems_table(l_index).needed_item_qty > 0
1377 ) THEN
1378
1379
1380 get_child_configurations
1381 ( pParentItemId =>pitems_table(pitems_table.last).item_id,
1382 pOrganization_id => pOrganization_id,
1383 pLower_Supplytype => pLower_Supplytype,
1384 pParent_index => l_index,--passing index# as parentid for children
1385 pitems_table => pitems_table ,
1386 x_return_status => l_ret_status,
1387 x_error_message => l_error_message,
1388 x_message_name => l_msg_name
1389
1390 );
1391
1392 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
1393 IF PG_DEBUG <> 0 THEN
1394 oe_debug_pub.add ('get_child_configurations: ' || 'failed after call to get_child_configurations with status ' || l_ret_status ,1);
1395
1396 oe_debug_pub.add ('get_child_configurations: ' || 'error message' || l_error_message ,1);
1397 END IF;
1398 RAISE FND_API.G_EXC_ERROR;
1399 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1400 IF PG_DEBUG <> 0 THEN
1401 oe_debug_pub.add ('get_child_configurations: ' || ' failed after call to get_child_configurations ' || l_ret_status ,1);
1402
1403 oe_debug_pub.add ('get_child_configurations: ' || 'error message' || l_error_message ,1);
1404 END IF;
1405 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1406 ELSE
1407
1408 IF PG_DEBUG <> 0 THEN
1409 oe_debug_pub.add('get_child_configurations: ' || 'success from get_child_configurations ' ,1);
1410 END IF;
1411 END IF;
1412
1413
1414 END IF; --source type and needed_item_qty
1415
1416
1417
1418
1419
1420
1421
1422 END LOOP;
1423 Close c_config_and_ato_items;
1424
1425 END IF;
1426
1427 EXCEPTION
1428
1429 when FND_API.G_EXC_ERROR then
1430
1431 x_return_status := FND_API.G_RET_STS_ERROR;
1432 x_error_message := 'CTOSUBSB.get_child_configurations expected excpn: ' || to_char(l_stmt_num)|| ':' ||
1433 substrb(sqlerrm,1,100);
1434
1435
1436
1437 IF PG_DEBUG <> 0 THEN
1438 oe_debug_pub.add('get_child_configurations: ' || 'CTOSUBSB.get_child_configuratiosn expected excpn: ' || x_error_message,1);
1439 END IF;
1440
1441
1442
1443 when FND_API.G_EXC_UNEXPECTED_ERROR then
1444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1445 x_error_message := 'CTOSUBSB.get_child_configurations UN expected excpn: ' || to_char(l_stmt_num)|| ':' ||
1446 substrb(sqlerrm,1,100);
1447
1448
1449
1450 IF PG_DEBUG <> 0 THEN
1451 oe_debug_pub.add('get_child_configurations: ' || 'CTOSUBSB.get_child_configurations UN expected excpn: ' || x_error_message,1);
1452 END IF;
1453
1454
1455
1456
1457
1458 when OTHERS then
1459 x_return_status := FND_API.G_RET_STS_ERROR;
1460 x_error_message := 'CTOSUBSB.get_start_date OTHERS excpn: ' || to_char(l_stmt_num)|| ':' ||
1461 substrb(sqlerrm,1,100);
1462
1463
1464 IF PG_DEBUG <> 0 THEN
1465 oe_debug_pub.add('get_child_configurations: ' || 'CTOSUBSB.get_start_date OTHERS excpn: ' || x_error_message,1);
1466 END IF;
1467
1468
1469 /* OE_MSG_PUB.Add_Exc_Msg
1470 ( G_PKG_NAME
1471 , 'get_working_day'
1472 ); */
1473
1474 END get_child_configurations;
1475
1476
1477 /*
1478
1479 API to create flow scheudle for sub_assemblies
1480
1481 */
1482 Procedure create_flow_subassembly
1483 (
1484 pflow_sch_details in out nocopy t_flow_sch_details,
1485 pIndex in number,
1486 pitems_table in t_item_details,
1487 pShip_org in number,
1488 pProject_id in number,
1489 pTask_id in number,
1490 x_return_status out NOCOPY varchar2,
1491 x_error_message out NOCOPY VARCHAR2, /* 70 bytes to hold msg */
1492 x_message_name out NOCOPY VARCHAR2 /* 30 bytes to hold name */
1493
1494 )
1495
1496 IS
1497
1498 l_flow_schedule_rec mrp_flow_schedule_pub.flow_schedule_rec_type;
1499 l_x_flow_schedule_rec mrp_flow_schedule_pub.flow_schedule_rec_type;
1500 l_x_flow_schedule_val_rec mrp_flow_schedule_pub.flow_schedule_val_rec_type;
1501 l_return_status varchar2(1);
1502 l_msg_count number;
1503 l_msg_data varchar2(240);
1504
1505 l_flow_index number := null;
1506
1507 l_stmt_num number := 0;
1508 l_x_value varchar2(1);
1509
1510 BEGIN
1511
1512 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1513
1514 l_flow_schedule_rec.operation := MRP_GLOBALS.G_OPR_CREATE;
1515 l_flow_schedule_rec.scheduled_flag := 1;
1516
1517 l_flow_schedule_rec.primary_item_id := pitems_table(pIndex).item_id;
1518 l_flow_schedule_rec.line_id := pitems_table(pIndex).line_id ;
1519 l_flow_schedule_rec.planned_quantity := pitems_table(pIndex).needed_item_qty ; --may need to chnaged the aty either primary uom qty or ordered aty
1520 l_flow_schedule_rec.organization_id := pShip_org;
1521 l_flow_schedule_rec.scheduled_completion_date := pitems_table(pIndex).job_completion_date;
1522
1523 --bugfix 3418102
1524 IF pitems_table(pIndex).pegging_flag IN ('I','X') THEN --project and task id can be passed to child item
1525 l_flow_schedule_rec.project_id := pProject_id;
1526 l_flow_schedule_rec.task_id := pTask_id;
1527 END IF;
1528
1529 l_stmt_num := 90;
1530 MRP_Flow_Schedule_PUB.Process_Flow_Schedule(
1531 p_api_version_number => 1.0,
1532 p_init_msg_list => FND_API.G_TRUE,
1533 x_return_status => l_return_status,
1534 x_msg_count => l_msg_count,
1535 x_msg_data => l_msg_data,
1536 p_flow_schedule_rec => l_flow_schedule_rec,
1537 x_flow_schedule_rec => l_x_flow_schedule_rec,
1538 x_flow_schedule_val_rec => l_x_flow_schedule_val_rec
1539 );
1540
1541
1542
1543
1544 if (l_return_status = FND_API.G_RET_STS_ERROR) then --flow return status
1545 IF PG_DEBUG <> 0 THEN
1546 oe_debug_pub.add('create_flow_subassembly: ' || 'Expected error in Process Flow Schedule with status: ' || l_return_status, 1);
1547 END IF;
1548 raise FND_API.G_EXC_ERROR;
1549
1550 elsif (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then --flow returns tatus
1551 IF PG_DEBUG <> 0 THEN
1552 oe_debug_pub.add('create_flow_subassembly: ' || 'UnExpected error in Process Flow Schedule with status: ' || l_return_status, 1);
1553 END IF;
1554 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1555
1556 else --flow return status
1557 IF PG_DEBUG <> 0 THEN
1558 oe_debug_pub.add('create_flow_subassembly: ' || 'Success in Process Flow Schedule.');
1559 END IF;
1560 if (l_x_flow_schedule_rec.wip_entity_id is not NULL) then
1561 l_stmt_num := 100;
1562 IF (pflow_sch_details.count = 0 ) THEN
1563 l_flow_index := 1;
1564
1565 ELSE
1566 l_flow_index := pflow_sch_details.last+1;
1567
1568 END IF;
1569
1570 pflow_sch_details(l_flow_index).t_item_details_index := pIndex;
1571 pflow_sch_details(l_flow_index).schedule_number := l_x_flow_schedule_rec.schedule_number;
1572 pflow_sch_details(l_flow_index).wip_entity_id := l_x_flow_schedule_rec.wip_entity_id;
1573 pflow_sch_details(l_flow_index).scheduled_start_date := l_x_flow_schedule_rec.scheduled_start_date;
1574 pflow_sch_details(l_flow_index).planned_quantity := l_x_flow_schedule_rec.planned_quantity;
1575 pflow_sch_details(l_flow_index).scheduled_completion_date := l_x_flow_schedule_rec.scheduled_completion_date;
1576 pflow_sch_details(l_flow_index).build_sequence := l_x_flow_schedule_rec.build_sequence;
1577 pflow_sch_details(l_flow_index).line_id := l_x_flow_schedule_rec.line_id ;
1578
1579
1580
1581
1582 l_stmt_num := 120;
1583 INSERT INTO BOM_CTO_MLSUPPLY_FLOW_TEMP
1584 ( order_line_id,
1585 item_index,
1586 schedule_number,
1587 wip_entity_id,
1588 scheduled_start_date ,
1589 planned_quantity ,
1590 scheduled_completion_date,
1591 build_sequence,
1592 line_id
1593 )
1594 VALUES( pitems_table(pIndex).order_line_id,
1595 pflow_sch_details(l_flow_index).t_item_details_index, --current child item index
1596 pflow_sch_details(l_flow_index).schedule_number,
1597 pflow_sch_details(l_flow_index).wip_entity_id,
1598 pflow_sch_details(l_flow_index).scheduled_start_date,
1599 pflow_sch_details(l_flow_index).planned_quantity,
1600 pflow_sch_details(l_flow_index).scheduled_completion_date,
1601 pflow_sch_details(l_flow_index).build_sequence,
1602 pflow_sch_details(l_flow_index).line_id
1603 );
1604
1605
1606
1607 IF PG_DEBUG <> 0 Then
1608 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','after process flow schedule');
1609 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','ietm index'|| pflow_sch_details(1).t_item_details_index);
1610 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','item_id '||l_x_flow_schedule_rec.primary_item_id );
1611 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','scheudle_number'|| pflow_sch_details(1).schedule_number);
1612 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','wipentity id'|| pflow_sch_details(1).wip_entity_id);
1613 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','schedule start date'||pflow_sch_details(1).scheduled_start_date );
1614 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','planned qty'|| pflow_sch_details(1).planned_quantity);
1615 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','scheudle_completion_date'|| pflow_sch_details(1).scheduled_completion_date);
1616
1617 End if;
1618
1619
1620
1621
1622
1623 IF PG_DEBUG <> 0 THEN
1624 oe_debug_pub.add('create_flow_subassembly: ' || 'alternate_bom_designator : ' ||l_x_flow_schedule_rec.alternate_bom_designator ,1);
1625
1626 oe_debug_pub.add('create_flow_subassembly: ' || 'alternate_routing_desig ' ||l_x_flow_schedule_rec.alternate_routing_desig ,1);
1627
1628 oe_debug_pub.add('create_flow_subassembly: ' || 'bom_revision ' ||l_x_flow_schedule_rec.bom_revision,1);
1629
1630 oe_debug_pub.add('create_flow_subassembly: ' || 'bom_revision_date ' ||l_x_flow_schedule_rec.bom_revision_date ,1);
1631
1632 oe_debug_pub.add('create_flow_subassembly: ' || 'build_sequence ' ||l_x_flow_schedule_rec.build_sequence ,1);
1633
1634 oe_debug_pub.add('create_flow_subassembly: ' || 'class_code' ||l_x_flow_schedule_rec.class_code ,1);
1635
1636 oe_debug_pub.add('create_flow_subassembly: ' || 'completion_locator_id ' ||l_x_flow_schedule_rec.completion_locator_id ,1);
1637
1638 oe_debug_pub.add('create_flow_subassembly: ' || 'completion_subinventory ' ||l_x_flow_schedule_rec.completion_subinventory ,1);
1639
1640 oe_debug_pub.add('create_flow_subassembly: ' || 'demand_class' ||l_x_flow_schedule_rec.demand_class ,1);
1641
1642 oe_debug_pub.add('create_flow_subassembly: ' || 'demand_source_delivery' ||l_x_flow_schedule_rec.demand_source_delivery ,1);
1643
1644 oe_debug_pub.add('create_flow_subassembly: ' || 'demand_source_header_id ' ||l_x_flow_schedule_rec.demand_source_header_id ,1);
1645
1646 oe_debug_pub.add('create_flow_subassembly: ' || 'line_id' ||l_x_flow_schedule_rec.line_id ,1);
1647
1648 oe_debug_pub.add('create_flow_subassembly: ' || 'organization_id ' ||l_x_flow_schedule_rec.organization_id ,1);
1649
1650 oe_debug_pub.add('create_flow_subassembly: ' || 'planned_quantity' ||l_x_flow_schedule_rec.planned_quantity ,1);
1651
1652 oe_debug_pub.add('create_flow_subassembly: ' || 'primary_item_id ' ||l_x_flow_schedule_rec.primary_item_id ,1);
1653
1654 oe_debug_pub.add('create_flow_subassembly: ' || 'project_id ' ||l_x_flow_schedule_rec.project_id ,1);
1655
1656 oe_debug_pub.add('create_flow_subassembly: ' || 'quantity_completed ' ||l_x_flow_schedule_rec.quantity_completed ,1);
1657
1658 oe_debug_pub.add('create_flow_subassembly: ' || 'scheduled_completion_date ' ||l_x_flow_schedule_rec.scheduled_completion_date ,1);
1659
1660 oe_debug_pub.add('create_flow_subassembly: ' || 'scheduled_flag ' ||l_x_flow_schedule_rec.scheduled_flag ,1);
1661
1662 oe_debug_pub.add('create_flow_subassembly: ' || 'scheduled_start_date' ||l_x_flow_schedule_rec.scheduled_start_date ,1);
1663
1664 oe_debug_pub.add('create_flow_subassembly: ' || 'task_id ' ||l_x_flow_schedule_rec.task_id ,1);
1665
1666 oe_debug_pub.add('create_flow_subassembly: ' || 'wip_entity_id ' ||l_x_flow_schedule_rec.wip_entity_id ,1);
1667
1668 oe_debug_pub.add('create_flow_subassembly: ' || 'scheduled_by' ||l_x_flow_schedule_rec.scheduled_by,1);
1669
1670 oe_debug_pub.add('create_flow_subassembly: ' || 'operation ' ||l_x_flow_schedule_rec.operation,1);
1671
1672 oe_debug_pub.add('create_flow_subassembly: ' || 'db_flag ' ||l_x_flow_schedule_rec.db_flag ,1);
1673
1674 oe_debug_pub.add('create_flow_subassembly: ' || 'quantity_scrapped ' ||l_x_flow_schedule_rec.quantity_scrapped ,1);
1675
1676 oe_debug_pub.add('create_flow_subassembly: ' || 'synch_schedule_num' ||l_x_flow_schedule_rec.synch_schedule_num ,1);
1677
1678 oe_debug_pub.add('create_flow_subassembly: ' || 'synch_operation_seq_num ' ||l_x_flow_schedule_rec.synch_operation_seq_num ,1);
1679
1680 oe_debug_pub.add('create_flow_subassembly: ' || 'roll_forwarded_flag ' ||l_x_flow_schedule_rec.roll_forwarded_flag ,1);
1681
1682 oe_debug_pub.add('create_flow_subassembly: ' || 'current_line_operation ' ||l_x_flow_schedule_rec.current_line_operation ,1);
1683
1684
1685
1686
1687
1688 END IF;
1689
1690
1691
1692 end if;
1693 end if; --flow return status
1694
1695 EXCEPTION
1696
1697 when FND_API.G_EXC_ERROR then
1698
1699 x_return_status := FND_API.G_RET_STS_ERROR;
1700 x_error_message := 'CTOSUBSB.create_flow_subassembly expected excpn: ' || to_char(l_stmt_num)|| ':' ||
1701 substrb(sqlerrm,1,100);
1702
1703
1704 IF PG_DEBUG <> 0 THEN
1705 IF PG_DEBUG <> 0 THEN
1706 oe_debug_pub.add('create_flow_subassembly: ' || 'CTOSUBSB.create_flow_subassembly expected excpn: ' || x_error_message,1);
1707 END IF;
1708 END IF;
1709
1710
1711 when FND_API.G_EXC_UNEXPECTED_ERROR then
1712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1713 x_error_message := 'CTOSUBSB.create_flow_subassembly UN expected excpn: ' || to_char(l_stmt_num)|| ':' ||
1714 substrb(sqlerrm,1,100);
1715
1716
1717 IF PG_DEBUG <> 0 THEN
1718 IF PG_DEBUG <> 0 THEN
1719 oe_debug_pub.add('create_flow_subassembly: ' || 'CTOSUBSB.create_flow_subassembly UN expected excpn: ' || x_error_message,1);
1720 END IF;
1721 END IF;
1722
1723
1724
1725 when OTHERS then
1726 x_return_status := FND_API.G_RET_STS_ERROR;
1727 x_error_message := 'CTOSUBSB.create_flow_subassembly OTHERS excpn: ' || to_char(l_stmt_num)|| ':' ||
1728 substrb(sqlerrm,1,100);
1729
1730 IF PG_DEBUG <> 0 THEN
1731 IF PG_DEBUG <> 0 THEN
1732 oe_debug_pub.add('create_flow_subassembly: ' || 'CTOSUBSB.create_flow_subassembly OTHERS excpn: ' || x_error_message,1);
1733 END IF;
1734 END IF;
1735
1736
1737
1738
1739 END create_flow_subassembly;
1740
1741 /*
1742 Procedure get_mlsupply_details
1743
1744 */
1745
1746 Procedure get_mlsupply_details(
1747 x_return_status out NOCOPY varchar2,
1748 x_error_message out NOCOPY VARCHAR2,
1749 x_message_name out NOCOPY VARCHAR2 )
1750 is
1751
1752
1753 l_order_line_id number;
1754 l_order_number number;
1755
1756 l_item_index number;
1757 l_parent_index number;
1758 l_item_id number;
1759 l_item_name varchar2(40);
1760 l_item_quantity number;
1761 l_needed_item_qty number;
1762 l_auto_config_flag varchar2(1);
1763 l_job_st_date date ;
1764 l_job_completion_date date;
1765 l_source_type number;
1766 l_cfm_routing_flag number;
1767 l_comments varchar2(200);
1768
1769 l_sourced varchar2(1);
1770 l_supply_type varchar2(100);
1771 l_config_ato varchar2(10);
1772
1773
1774 --l_item_index number;
1775 l_schedule_number varchar2(30);
1776 l_scheduled_start_date date;
1777 l_scheduled_completion_date date;
1778 l_synch_schedule_num varchar2(30);
1779 l_run_req_import_flag varchar2(1) := 'N';
1780
1781 CURSOR c_order_details IS
1782 Select distinct(bcmm.order_line_id),oeh.order_number
1783 from bom_cto_mlsupply_main_temp bcmm,
1784 oe_order_lines_all oel,
1785 oe_order_headers_all oeh
1786 where bcmm.order_line_id = oel.line_id
1787 and oel.header_id = oeh.header_id
1788 order by oeh.order_number, bcmm.order_line_id;
1789
1790 Cursor c_supply_details is
1791 SELECT item_index,
1792 parent_index,
1793 ITEM_ID,
1794 item_name,
1795 ITEM_QUANTITY,
1796 NEEDED_ITEM_QTY,
1797 AUTO_CONFIG_FLAG,
1798 JOB_START_DATE,
1799 JOB_COMPLETION_DATE,
1800 SOURCE_TYPE,
1801 CFM_ROUTING_FLAG,
1802 comments
1803 FROM bom_cto_mlsupply_main_temp
1804 WHERE order_line_id = l_order_line_id
1805 order by item_index;
1806
1807
1808
1809 Cursor c_flow_supply IS
1810 SELECT item_index,
1811 schedule_number,
1812 scheduled_start_date,
1813 scheduled_completion_date,
1814 synch_schedule_num
1815 FROM bom_cto_mlsupply_flow_temp
1816 WHERE order_line_id = l_order_line_id
1817 order by item_index,scheduled_completion_date,schedule_number;
1818
1819
1820
1821 BEGIN
1822
1823
1824 OPEN c_order_details;
1825
1826 LOOP
1827
1828 FETCH c_order_details INTO l_order_line_id,l_order_number ;
1829
1830
1831 EXIT when c_order_details%notfound;
1832
1833 If PG_DEBUG <> 0 Then
1834 oe_debug_pub.add(' SUPPLY FOR ORDER NUMBER = ' || l_order_number || 'LINE_ID = ' || l_order_line_id ,1);
1835 oe_debug_pub.add('-----------------------------------------------------------------------------------------',1);
1836
1837 --CTO DEBUG FILE
1838 cto_wip_workflow_api_pk.cto_debug ('get_mlsupply_details',' SUPPLY FOR ORDER NUMBER = ' || l_order_number || 'LINE_ID = ' || l_order_line_id );
1839 cto_wip_workflow_api_pk.cto_debug ('get_mlsupply_details','-----------------------------------------------------------------------------------------');
1840
1841
1842 oe_debug_pub.add('INDEX--'||'PARENT_INDEX--'||'ITEM_ID--'||'ITEM_NAME--'||'ITEM_QTY--'||'NEEDED_ITEM_QTY--'||
1843 'CONFIG/ATO--'||'JOB_START_DATE--'||'JOB_COMPLETION_DATE--'||'SOURCED--'||'DISCREATE/FLOW/BUY--'||'COMMENTS',1);
1844
1845
1846 cto_wip_workflow_api_pk.cto_debug ('get_mlsupply_details','INDEX--'||'PARENT_INDEX--'||'ITEM_ID--'||'ITEM_NAME--'||'ITEM_QTY--'||'NEEDED_ITEM_QTY--'||
1847 'CONFIG/ATO--'||'JOB_START_DATE--'||'JOB_COMPLETION_DATE--'||'SOURCED--'||'DISCREATE/FLOW/BUY--'
1848 ||'COMMENTS');
1849 End if;
1850 OPEN c_supply_details;
1851
1852 LOOP
1853
1854 FETCH c_supply_details INTO l_item_index,
1855 l_parent_index,
1856 l_item_id,
1857 l_item_name,
1858 l_item_quantity,
1859 l_needed_item_qty,
1860 l_auto_config_flag,
1861 l_job_st_date,
1862 l_job_completion_date,
1863 l_source_type,
1864 l_cfm_routing_flag,
1865 l_comments;
1866
1867 EXIT when c_supply_details%notfound;
1868
1869
1870 IF l_auto_config_flag = 'Y' THEN
1871 l_config_ato := 'CONFIG';
1872 ELSE
1873 l_config_ato := 'ATO ITEM';
1874 END IF;
1875
1876
1877 -- transfer =1 , 66 = multiple sources
1878 -- rkaza. 05/02/2005. ireq project.
1879 -- Need to recommend running req import for 100% transfer cases
1880 -- also in addition to buy cases. Yet mark them as sourced.
1881
1882 IF l_source_type = 1 THEN
1883 l_sourced := 'Y';
1884 l_supply_type := '100% Transfer';
1885 IF l_run_req_import_flag = 'N' THEN
1886 l_run_req_import_flag := 'Y' ;
1887 END IF;
1888
1889 ELSIF l_source_type = 66 then
1890 l_sourced := 'Y';
1891 l_supply_type := 'Planning';
1892
1893 ELSIF l_source_type = 3 THEN
1894 l_sourced := 'N';
1895 l_supply_type := 'BUY';
1896 IF l_run_req_import_flag = 'N' THEN
1897 l_run_req_import_flag := 'Y' ;
1898 END IF;
1899
1900 ELSE
1901 IF l_cfm_routing_flag = 2 THEN
1902 l_sourced := 'N';
1903 l_supply_type := 'Discrete';
1904 ELSIF l_cfm_routing_flag = -99 THEN
1905 l_sourced := 'N';
1906 l_supply_type := 'No routing Default to discrete';
1907
1908 ELSE
1909 l_sourced := 'N';
1910 l_supply_type := 'FLOW';
1911
1912 END IF; --cfm flag
1913
1914 END IF;--source type
1915
1916 If PG_DEBUG <> 0 Then
1917 oe_debug_pub.add(l_item_index||' -- '||l_parent_index||' -- '||l_item_id||' -- '||l_item_name||' -- '||l_item_quantity||' -- '||
1918 l_needed_item_qty||' -- '||l_config_ato||' -- '||to_char(l_job_st_date,'mm/dd/yyyy hh24:mi:ss')||' -- '||
1919 to_char(l_job_completion_date,'mm/dd/yyyy hh24:mi:ss')||' -- '||l_sourced||' --'||l_supply_type||' -- '||l_comments,1);
1920
1921
1922 cto_wip_workflow_api_pk.cto_debug ('get_mlsupply_details',l_item_index||' -- '||l_parent_index||' -- '||l_item_id||' -- '||l_item_name||' -- '||
1923 l_item_quantity||' -- '||l_needed_item_qty||' -- '||l_config_ato||' -- '||
1924 to_char(l_job_st_date,'mm/dd/yyyy hh24:mi:ss')||' -- '||to_char(l_job_completion_date,'mm/dd/yyyy hh24:mi:ss')||
1925 ' -- '||l_sourced||' --'||l_supply_type||' -- '||l_comments );
1926
1927 End if;
1928 END LOOP;
1929 CLOSE c_supply_details;
1930
1931 OPEN c_flow_supply;
1932 If PG_DEBUG <> 0 Then
1933 oe_debug_pub.add('INDEX(from above)--'||'SCHEDULE_NUMBER--'||'SCHEDULE_START_DATE--'||'SCHEDULE_COMPLETION_DATE--'||'PARENT_SCHEDULE_NUM(if flow parent)',1);
1934
1935 cto_wip_workflow_api_pk.cto_debug ('get_mlsupply_details','INDEX(from above)--'||'SCHEDULE_NUMBER--'||'SCHEDULE_START_DATE--'||'SCHEDULE_COMPLETION_DATE--'||'PARENT_SCHEDULE_NUM(if flow parent)');
1936
1937 End if;
1938 LOOP
1939 FETCH c_flow_supply into l_item_index,
1940 l_schedule_number,
1941 l_scheduled_start_date,
1942 l_scheduled_completion_date,
1943 l_synch_schedule_num;
1944
1945 EXIT when c_flow_supply%notfound;
1946 If PG_DEBUG <> 0 Then
1947 oe_debug_pub.add(l_item_index||' -- '||l_schedule_number||' -- '||to_char(l_scheduled_start_date,'mm/dd/yyyy hh24:mi:ss')||' -- '||
1948 to_char(l_scheduled_completion_date,'mm/dd/yyyy hh24:mi:ss')||' -- '||l_synch_schedule_num,1);
1949
1950
1951 cto_wip_workflow_api_pk.cto_debug ('get_mlsupply_details',l_item_index||' -- '||l_schedule_number||' -- '||
1952 to_char(l_scheduled_start_date,'mm/dd/yyyy hh24:mi:ss')||' -- '||
1953 to_char(l_scheduled_completion_date,'mm/dd/yyyy hh24:mi:ss')||' -- '||l_synch_schedule_num );
1954
1955 End if;
1956
1957 END LOOP;
1958
1959 CLOSE c_flow_supply;
1960
1961
1962
1963 END LOOP;
1964
1965 CLOSE c_order_details;
1966
1967 IF l_run_req_import_flag = 'Y' THEN
1968 --bugfix 2755695
1969 oe_debug_pub.add('SUB-ASSEMBLY(S) WITH BUY or TRANSFER SOURCING RULE EXIST(S) , PLEASE RUN REQUISITION IMPORT PROGRAM WITH IMPORT SOURCE => CTO-LOWER LEVEL ',1 );
1970 END IF;
1971
1972
1973
1974
1975 END get_mlsupply_details;
1976
1977
1978
1979 /*
1980 p_top_assembly_type 1= if called from discrete code
1981 2= if called from flow code
1982 insert into wip for child discrete make --but wip mass load called with differnet sequenece
1983 insert into child buy
1984
1985
1986
1987
1988 */
1989
1990
1991 Procedure create_subassembly_jobs
1992 (
1993
1994 p_mlsupply_parameter in number, --org parameter indicating whether auto-created or ( AtOITEM and autocreated) 1= autocreated and 2 =
1995 p_Top_Assembly_LineId in number,
1996 pSupplyQty in number,
1997 p_wip_seq in number,
1998 p_status_type in number,
1999 p_class_code in varchar2,
2000 p_conc_request_id IN NUMBER,
2001 p_conc_program_id IN NUMBER,
2002 p_conc_login_id IN NUMBER,
2003 p_user_id IN NUMBER,
2004 p_appl_conc_program_id IN NUMBER,
2005 x_return_status out NOCOPY varchar2,
2006 x_error_message out NOCOPY VARCHAR2, /* 70 bytes to hold msg */
2007 x_message_name out NOCOPY VARCHAR2 /* 30 bytes to hold name */
2008 )
2009 is
2010
2011 l_finite_scheduler_flag number := null;
2012
2013 l_parent_start_date DATE ;
2014 l_child_item_id NUMBER :=0 ;
2015 l_child_qty Number :=0;
2016
2017 l_item_id mtl_system_items_kfv.inventory_item_id%type;
2018 l_ship_org mtl_system_items_kfv.organization_id%type;
2019 l_schedule_ship_date date ;
2020 l_item_name mtl_system_items_kfv.concatenated_segments%type;
2021 l_fixed_lead_time mtl_system_items_kfv.fixed_lead_time%type;
2022 l_variable_lead_time mtl_system_items_kfv.variable_lead_time%type;
2023 l_processing_lead_time mtl_system_items_kfv.full_lead_time%type;
2024 l_ordered_uom varchar2(3) := null;
2025 l_order_number number := null;
2026 l_cfm_routing_flag number := null;
2027 l_ordered_quantity number := null;
2028 l_routing_sequence_id number := null;
2029 l_lead_time number := null;
2030 L_OPERATION_SEQ_ID number := null;
2031 l_auto_config_flag varchar2(1);
2032
2033 l_min_completion_date date;
2034
2035 l_child_operation_date date;
2036 X_CHILD_COMPLETION_DATE date;
2037
2038
2039 l_project_id number := null;
2040 l_task_id number := null;
2041 L_X_RETURN_STATUS varchar2(1) ;
2042 l_x_msg_count number;
2043 l_x_msg_data varchar2(240);
2044 l_stmt_num number := 0;
2045
2046 x_groupID Number;
2047
2048 l_requestId Number;
2049 x_retVal varchar2(100);
2050 x_errMsg varchar2(100);
2051
2052 x_completion_date DATE;
2053 x_parent_job_start_date DATE ;
2054
2055 --table of records to hold the item details
2056 l_mlsupply_items t_item_details;
2057
2058 l_flow_sch_details t_flow_sch_details;
2059
2060
2061
2062
2063 l_index number;
2064 flow_index number ;
2065 errbuf varchar2(10);
2066 retcode number;
2067 max_completion_date date;
2068
2069
2070 l_return_status varchar2(1);
2071 l_msg_count number;
2072 l_msg_data varchar2(240);
2073 -- l_stmt_num number := 0;
2074
2075 l_ret_status varchar2(1);
2076 l_error_messsage varchar2(70) := null;
2077 l_msg_name varchar2(30) := null;
2078
2079 v_time1 number;
2080 v_time2 number;
2081
2082
2083
2084 CURSOR c_flow_sch IS
2085 SELECT wfs.schedule_number,
2086 wfs.wip_entity_id,
2087 wfs.scheduled_start_date,
2088 wfs.planned_quantity,
2089 wfs.scheduled_completion_date,
2090 wfs.build_sequence,
2091 wfs.line_id,
2092 wil.line_code
2093 FROM wip_flow_schedules wfs,
2094 wip_lines wil
2095 WHERE demand_source_line = p_Top_Assembly_LineId
2096 AND wfs.line_id = wil.line_id;
2097
2098
2099 l_discrete_under_flow varchar2(1) := 'N'; --will become Y if there is a discrete under top most flow parent
2100 l_sub_level_buy_item varchar2(1) := 'N';
2101 l_user_id number ;
2102 l_login_id number;
2103 --l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2104 l_program_id number;
2105
2106 l_token CTO_MSG_PUB.token_tbl;
2107
2108 -- rkaza. ireq project. 05/05/2005.
2109 l_req_input_data CTO_AUTO_PROCURE_PK.req_interface_input_data;
2110
2111 l_phantom varchar2(1);
2112
2113 BEGIN
2114
2115 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2116
2117
2118 If PG_DEBUG <> 0 Then
2119 cto_wip_workflow_api_pk.cto_debug('Create_sub_assembly_jobs','Inside create sub-assembly jobs');
2120 cto_wip_workflow_api_pk.cto_debug('Create_sub_assembly_jobs','FOR LINE ID '||p_Top_Assembly_LineId );
2121 End if;
2122
2123 l_stmt_num := 140;
2124
2125 SELECT oel.inventory_item_id,
2126 oel.ship_from_org_id,
2127 oel.schedule_ship_date,
2128 oel. project_id,
2129 oel.task_id,
2130 oel.ordered_quantity,
2131 mtl.concatenated_segments,
2132 mtl.auto_created_config_flag,
2133 nvl(mtl.fixed_lead_time,0),
2134 nvl(mtl.variable_lead_time,0),
2135 nvl(mtl.full_lead_time,0),
2136 order_quantity_uom ,
2137 oeh.order_number,
2138 nvl(bor.cfm_routing_flag,-99),
2139 bor.routing_sequence_id
2140 INTO l_item_id,
2141 l_ship_org,
2142 l_schedule_ship_date,
2143 l_project_id,
2144 l_task_id,
2145 l_ordered_quantity,
2146 l_item_name,
2147 l_auto_config_flag,
2148 l_fixed_lead_time,
2149 l_variable_lead_time,
2150 l_processing_lead_time,
2151 l_ordered_uom,
2152 l_order_number,
2153 l_cfm_routing_flag,
2154 l_routing_sequence_id
2155 FROM oe_order_lines_all oel,
2156 oe_order_headers_all oeh,
2157 mtl_system_items_kfv mtl,
2158 bom_operational_routings bor
2159 WHERE oel.line_id = p_Top_Assembly_LineId
2160 AND oeh.header_id = oel.header_id
2161 AND oel.inventory_item_id = mtl.inventory_item_id
2162 AND oel.ship_from_org_id = mtl.organization_id
2163 AND bor.assembly_item_id (+)= mtl.inventory_item_id
2164 AND bor.organization_id(+) = mtl.organization_id
2165 AND bor.alternate_routing_designator(+) is null
2166 ;
2167
2168 IF (l_mlsupply_items.count = 0) THEN --adding topmoset parent details
2169 l_mlsupply_items(1).item_id := p_Top_Assembly_LineId;
2170 l_mlsupply_items(1).item_id := l_item_id;
2171 l_mlsupply_items(1).item_name := l_item_name;
2172 IF ( pSupplyQty is null) THEN
2173 l_mlsupply_items(1).item_quantity := l_ordered_quantity;
2174 l_mlsupply_items(1).needed_item_qty := l_ordered_quantity; --top most parent needed qty = supply quantity
2175 ELSE
2176 l_mlsupply_items(1).item_quantity := pSupplyQty;
2177 l_mlsupply_items(1).needed_item_qty := pSupplyQty; --top most parent needed qty = supply quantity
2178
2179 END IF;
2180 -- l_mlsupply_items(1).operation_lead_time_percent := 0;
2181 l_mlsupply_items(1).cfm_routing_flag := l_cfm_routing_flag;
2182 l_mlsupply_items(1).routing_sequence_id := l_routing_sequence_id;
2183 l_mlsupply_items(1).fixed_lead_time := l_fixed_lead_time;
2184 l_mlsupply_items(1).variable_lead_time := l_variable_lead_time;
2185 l_mlsupply_items(1).processing_lead_time := l_processing_lead_time;
2186 l_mlsupply_items(1).job_completion_date := l_schedule_ship_date;
2187
2188 If PG_DEBUG <> 0 Then
2189 cto_wip_workflow_api_pk.cto_debug('Create_sub_assembly_jobs','Entered top-most item details into table');
2190 End if;
2191
2192 IF(l_cfm_routing_flag = 1) THEN --if top most parent = flow
2193
2194
2195 l_stmt_num := 160;
2196 flow_index := 1;
2197 OPEN c_flow_sch;
2198 LOOP
2199
2200 FETCH c_flow_sch INTO l_flow_sch_details(flow_index).schedule_number,
2201 l_flow_sch_details(flow_index).wip_entity_id,
2202 l_flow_sch_details(flow_index).scheduled_start_date,
2203 l_flow_sch_details(flow_index).planned_quantity ,
2204 l_flow_sch_details(flow_index).scheduled_completion_date,
2205 l_flow_sch_details(flow_index).build_sequence,
2206 l_flow_sch_details(flow_index).line_id,
2207 l_flow_sch_details(flow_index).line_code;
2208
2209
2210 EXIT when c_flow_sch%notfound;
2211
2212 oe_debug_pub.add('create_subassembly_supply'||'top most flow parent schdeule number' || l_flow_sch_details(flow_index).schedule_number);
2213
2214
2215
2216 IF (flow_index = 1 ) THEN
2217 l_mlsupply_items(1).flow_start_index := 1;
2218 l_mlsupply_items(1).line_id := l_flow_sch_details(flow_index).line_id;
2219 l_mlsupply_items(1).line_code := l_flow_sch_details(flow_index).line_code;
2220 l_mlsupply_items(1).feeder_run := 'N';
2221 END IF;
2222 l_flow_sch_details(flow_index).t_item_details_index := 1;
2223 flow_index := l_flow_sch_details.last+1;
2224
2225
2226 END LOOP;
2227
2228 IF (l_mlsupply_items(1).flow_start_index = 1) THEN --which means there was a row inserted
2229 l_mlsupply_items(1).flow_end_index := l_flow_sch_details.last;
2230
2231
2232 l_stmt_num := 170;
2233 INSERT INTO BOM_CTO_MLSUPPLY_FLOW_TEMP
2234 ( order_line_id,
2235 item_index,
2236 schedule_number,
2237 wip_entity_id,
2238 scheduled_start_date ,
2239 planned_quantity ,
2240 scheduled_completion_date,
2241 build_sequence,
2242 line_id,
2243 synch_schedule_num,
2244 SYNCH_OPERATION_SEQ_NUM )
2245 SELECT
2246 p_Top_Assembly_LineId,
2247 1 ,
2248 schedule_number,
2249 wip_entity_id,
2250 scheduled_start_date ,
2251 planned_quantity ,
2252 scheduled_completion_date,
2253 build_sequence,
2254 line_id,
2255 synch_schedule_num,
2256 SYNCH_OPERATION_SEQ_NUM
2257 FROM wip_flow_schedules
2258 where demand_source_line = p_Top_Assembly_LineId;
2259
2260 END IF;
2261
2262 CLOSE c_flow_sch;
2263
2264
2265
2266
2267 END IF; --if top most paernt is flow
2268
2269
2270
2271
2272 --Insert for top most parent into BOM_CTO_MLSUPPLY_MAIN_TEMP table
2273 l_stmt_num := 180;
2274
2275 INSERT INTO BOM_CTO_MLSUPPLY_MAIN_TEMP
2276 ( order_line_id,
2277 item_index ,
2278 item_id,
2279 item_name,
2280 AUTO_CONFIG_FLAG,
2281 item_quantity,
2282 needed_item_qty ,
2283 cfm_routing_flag ,
2284 routing_sequence_id ,
2285 fixed_lead_time,
2286 variable_lead_time ,
2287 processing_lead_time ,
2288 job_completion_date,
2289 line_id,
2290 line_code,
2291 flow_start_index,
2292 flow_end_index
2293 )
2294 VALUES ( p_Top_Assembly_LineId,
2295 1, --as it is first elemnt
2296 l_item_id,
2297 l_item_name ,
2298 l_auto_config_flag,
2299 l_mlsupply_items(1).item_quantity,
2300 l_mlsupply_items(1).needed_item_qty ,
2301 l_cfm_routing_flag ,
2302 l_routing_sequence_id ,
2303 l_fixed_lead_time,
2304 l_variable_lead_time ,
2305 l_processing_lead_time ,
2306 l_schedule_ship_date,
2307 l_mlsupply_items(1).line_id,
2308 l_mlsupply_items(1).line_code,
2309 l_mlsupply_items(1).flow_start_index,
2310 l_mlsupply_items(1).flow_end_index
2311
2312
2313
2314 ) ;
2315
2316
2317
2318
2319
2320
2321 END IF; --top most parent details
2322
2323 l_stmt_num := 190;
2324 If PG_DEBUG <> 0 Then
2325 cto_wip_workflow_api_pk.cto_debug('Create_sub_assembly_jobs','Before calling get_child_configurations' );
2326 End if;
2327 get_child_configurations
2328 (
2329 pParentItemId =>l_mlsupply_items(1).item_id,
2330 pOrganization_id =>l_ship_org,
2331 pLower_Supplytype =>p_mlsupply_parameter, -- lower level supply type
2332 pParent_index =>1,--parent index passed as one
2333 pitems_table =>l_mlsupply_items,
2334 x_return_status => l_ret_status,
2335 x_error_message => l_error_messsage,
2336 x_message_name => l_msg_name
2337
2338 );
2339
2340 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2341 IF PG_DEBUG <> 0 THEN
2342 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after call to get_child_configurations with status ' || l_return_status ,1);
2343
2344 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2345 END IF;
2346 RAISE FND_API.G_EXC_ERROR;
2347 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2348 IF PG_DEBUG <> 0 THEN
2349 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to get_child_configurations ' || l_return_status ,1);
2350
2351 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2352 END IF;
2353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2354 ELSE
2355 IF PG_DEBUG <> 0 THEN
2356 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from get_child_configurations ' ,1);
2357 END IF;
2358 END IF;
2359
2360 IF (l_mlsupply_items.count = 1) THEN
2361 x_error_message := 'NO children present at level ' || p_mlsupply_parameter ;
2362 RETURN;
2363
2364
2365 END IF;
2366
2367
2368 --getting completion date
2369
2370 oe_debug_pub.add('Before starting logic of completion date',1);
2371
2372
2373 IF (l_mlsupply_items.count > 0) THEN --checks for uninitialized collection --bugfix2308063
2374
2375 l_index := 2;--completion date is needed from 2nd level discrete item
2376 LOOP
2377
2378 oe_debug_pub.add('Looping for item'||l_mlsupply_items(l_index).item_name,1);
2379 l_mlsupply_items(l_index).order_line_id := p_Top_Assembly_LineId;
2380
2381 --insert data into MAIN table
2382
2383 INSERT INTO BOM_CTO_MLSUPPLY_MAIN_TEMP
2384 ( order_line_id,
2385 item_index ,
2386 PARENT_INDEX,
2387 item_id,
2388 item_name,
2389 AUTO_CONFIG_FLAG,
2390 item_quantity,
2391 needed_item_qty ,
2392 cfm_routing_flag ,
2393 routing_sequence_id ,
2394 fixed_lead_time,
2395 variable_lead_time ,
2396 processing_lead_time ,
2397 --job_completion_date,
2398 line_id,
2399 line_code,
2400 flow_start_index,
2401 flow_end_index,
2402 source_type,
2403 comments,
2404 wip_supply_type,
2405 OPERATION_SEQ_NUM
2406 )
2407 VALUES ( p_Top_Assembly_LineId,
2408 l_index,
2409 l_mlsupply_items(l_index).parent_index,
2410 l_mlsupply_items(l_index).item_id,
2411 l_mlsupply_items(l_index).item_name,
2412 l_mlsupply_items(l_index).auto_config_flag,
2413 l_mlsupply_items(l_index).item_quantity,
2414 l_mlsupply_items(l_index).needed_item_qty ,
2415 l_mlsupply_items(l_index).cfm_routing_flag,
2416 l_mlsupply_items(l_index).routing_sequence_id ,
2417 l_mlsupply_items(l_index).fixed_lead_time,
2418 l_mlsupply_items(l_index).variable_lead_time ,
2419 l_mlsupply_items(l_index).processing_lead_time ,
2420 --l_schedule_ship_date,
2421 l_mlsupply_items(l_index).line_id,
2422 l_mlsupply_items(l_index).line_code,
2423 l_mlsupply_items(l_index).flow_start_index,
2424 l_mlsupply_items(l_index).flow_end_index,
2425 l_mlsupply_items(l_index).source_type,
2426 l_mlsupply_items(l_index).comment,
2427 l_mlsupply_items(l_index).wip_supply_type, --4645636
2428 l_mlsupply_items(l_index).operation_seq_num --4645636
2429
2430
2431 ) ;
2432
2433 EXIT WHEN l_index = l_mlsupply_items.LAST;
2434 l_index := l_mlsupply_items.NEXT(l_index);
2435 END LOOP;
2436 END IF;
2437
2438 BEGIN
2439 select 'Y' INTO l_phantom
2440 from BOM_CTO_MLSUPPLY_MAIN_TEMP
2441 where wip_supply_type = 6
2442 and rownum = 1;
2443 Exception
2444 when no_data_found then
2445 l_phantom := 'N';
2446
2447 end;
2448
2449 oe_debug_pub.add('Phantom flag'||l_phantom,1);
2450
2451 IF l_phantom = 'Y' THEN
2452 oe_debug_pub.add('About to call process_phantoms',1);
2453 --call process children under phatom
2454 process_phantoms
2455 (
2456 pitems_table=>l_mlsupply_items,
2457 p_organization_id =>l_ship_org,
2458 x_return_status => l_ret_status,
2459 x_error_message => l_error_messsage,
2460 x_message_name => l_msg_name
2461 );
2462 END IF;
2463
2464 IF (l_mlsupply_items.count > 0) THEN --checks for uninitialized collection --bugfix2308063
2465
2466 l_index := 2;--completion date is needed from 2nd level discrete item
2467 LOOP
2468
2469 oe_debug_pub.add('Looping again for item'||l_mlsupply_items(l_index).item_name,1);
2470 l_mlsupply_items(l_index).order_line_id := p_Top_Assembly_LineId;
2471
2472 -- rkaza. ireq project. 05/03/2005.
2473 -- Enabling 100% transfer rule supply creation for lower
2474 -- level items (source type = 1).
2475
2476 IF ( l_mlsupply_items(l_index).source_type in (1,2,3) and
2477 l_mlsupply_items(l_index).needed_item_qty >0
2478
2479 --4645636 do not cal times for phantom items
2480 -- l_mlsupply_items(l_index).wip_supply_type <> 6
2481 ) THEN --check if item is not sourced
2482
2483 IF( l_mlsupply_items(l_mlsupply_items(l_index).parent_index).cfm_routing_flag =2 OR
2484 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).cfm_routing_flag = -99) THEN --parent is discrete
2485 --get the value of wip finite scheduler flag if not selected previously
2486 IF(l_finite_scheduler_flag is null) THEN
2487
2488 SELECT nvl(use_finite_scheduler,2)
2489 INTO l_finite_scheduler_flag
2490 FROM wip_parameters
2491 WHERE organization_id = l_ship_org;
2492
2493 oe_debug_pub.add ('create_subassembly_jobs: ' || 'l_finite_scheduler_flag is '|| l_finite_scheduler_flag ,1);
2494
2495 END IF;--finnite scheduler
2496
2497
2498 IF ( l_mlsupply_items(l_index).job_completion_date is null) THEN
2499 IF (l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_start_date is null) THEN
2500
2501 l_stmt_num := 200;
2502 get_start_date(
2503 pCompletion_date => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_completion_date,
2504 pQty => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).needed_item_qty,
2505 pitemid => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).item_id,
2506 porganization_id => l_ship_org,
2507 pfixed_leadtime => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).fixed_lead_time,
2508 pvariable_leadtime => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).variable_lead_time,
2509 x_start_date => x_parent_job_start_date,
2510 x_return_status => l_ret_status,
2511 x_error_message => l_error_messsage,
2512 x_message_name => l_msg_name
2513 );
2514
2515
2516 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2517 IF PG_DEBUG <> 0 THEN
2518 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after call to get_start_date with status ' || l_return_status ,1);
2519
2520 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2521 END IF;
2522 RAISE FND_API.G_EXC_ERROR;
2523 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2524 IF PG_DEBUG <> 0 THEN
2525 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to get_start_date ' || l_return_status ,1);
2526
2527 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage,1);
2528 END IF;
2529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2530 ELSE
2531 IF PG_DEBUG <> 0 THEN
2532 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from get_start_date ' ,1);
2533 END IF;
2534 END IF;
2535
2536
2537 IF ( l_finite_scheduler_flag = 1) THEN
2538 IF (x_parent_job_start_date <= SYSDATE) THEN
2539 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_start_date := SYSDATE;
2540
2541 --populate start date flag = 1implies insert satrt date in wjsi instead of completion date
2542 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).populate_start_date := 1;
2543
2544 IF PG_DEBUG <> 0 THEN
2545 oe_debug_pub.add('create_subassembly_jobs: ' || 'actual parent starts date is < sysdate ' ,1);
2546 END IF;
2547
2548 ELSE
2549 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_start_date := x_parent_job_start_date;
2550 oe_debug_pub.add('create_subassembly_jobs: ' || 'parent_start_date '|| to_char(x_parent_job_start_date,'mm/dd/yy hh24:mi:ss') ,1);
2551
2552 END IF;
2553
2554 ELSE
2555 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_start_date := x_parent_job_start_date;
2556 oe_debug_pub.add('create_subassembly_jobs: ' || 'parent_start_date '|| to_char(x_parent_job_start_date,'mm/dd/yy hh:mi:ss') ,1);
2557 END IF ;
2558
2559
2560
2561
2562 --update parent items job start date
2563 l_stmt_num := 300;
2564 update bom_cto_mlsupply_main_temp
2565 set job_start_date = l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_start_date
2566 where item_index = l_mlsupply_items(l_index).parent_index
2567 and order_line_id = p_Top_Assembly_LineId ;
2568
2569
2570
2571 END IF;
2572
2573
2574 IF ( l_mlsupply_items(l_mlsupply_items(l_index).parent_index).populate_start_date is null) THEN
2575 get_completion_date(
2576
2577 pParent_job_start_date => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_start_date,
2578 porganization_id => l_ship_org,
2579 plead_time_offset_percent => l_mlsupply_items(l_index).OPERATION_LEAD_TIME_PERCENT,
2580 pParent_processing_lead_time => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).processing_lead_time ,
2581 ppostprocessing_time => l_mlsupply_items(l_index).postprocessing_lead_time ,
2582 pSource_type => l_mlsupply_items(l_index).source_type,
2583 x_child_completion_date => x_completion_date,
2584 x_return_status => l_ret_status,
2585 x_error_message => l_error_messsage,
2586 x_message_name => l_msg_name
2587 );
2588
2589
2590
2591
2592 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2593 IF PG_DEBUG <> 0 THEN
2594 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after call to get-completion_date with status ' || l_return_status ,1);
2595
2596 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2597 END IF;
2598 RAISE FND_API.G_EXC_ERROR;
2599 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2600 IF PG_DEBUG <> 0 THEN
2601 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to get_completion_date ' || l_return_status ,1);
2602
2603 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2604 END IF;
2605 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2606 ELSE
2607 IF PG_DEBUG <> 0 THEN
2608 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from get_completion_date ' ,1);
2609 oe_debug_pub.add('create_subassembly_jobs: ' || 'parent_start_date '|| to_char(x_parent_job_start_date,'mm/dd/yy hh:mi:ss') ,1);
2610 END IF;
2611 END IF;
2612
2613
2614 IF ( l_finite_scheduler_flag = 1) THEN
2615 IF (x_completion_date <= SYSDATE) THEN --sysdate check
2616 -- rkaza. 05/03/2005. Added
2617 -- source type 1 here.
2618 IF (l_mlsupply_items(l_index).source_type in (1,3)) THEN --buy and 100% transfer item
2619 l_mlsupply_items(l_index).job_completion_date := SYSDATE;
2620
2621 ELSE --make(discrete/flow)
2622 l_mlsupply_items(l_index).populate_start_date := 1; -- to insert wip inetrface with satrt date
2623
2624 l_mlsupply_items(l_index).job_completion_date := SYSDATE;
2625 l_mlsupply_items(l_index).job_start_date := SYSDATE;
2626 END IF; --buy item
2627
2628
2629
2630 ELSE --not sysdate
2631 l_mlsupply_items(l_index).job_completion_date := x_completion_date;
2632
2633 --if flow is top most item AND
2634 --if finite scheduler is on we calculate job start date
2635 --as we need to insert both first unit start date as well as last unit completion date
2636 --bugfix#2739590
2637
2638 -- rkaza. 05/05/2005.
2639 -- Following block is only
2640 -- needed for WIP items.
2641 IF(l_mlsupply_items(1).cfm_routing_flag = 1) -- top item is flow
2642 and l_mlsupply_items(l_index).source_type = 2
2643 and l_mlsupply_items(l_index).cfm_routing_flag <> 1 THEN
2644
2645 get_start_date(
2646 pCompletion_date => l_mlsupply_items(l_index).job_completion_date,
2647 pQty => l_mlsupply_items(l_index).needed_item_qty,
2648 pitemid => l_mlsupply_items(l_index).item_id,
2649 porganization_id => l_ship_org,
2650 pfixed_leadtime => l_mlsupply_items(l_index).fixed_lead_time,
2651 pvariable_leadtime => l_mlsupply_items(l_index).variable_lead_time,
2652 x_start_date => x_parent_job_start_date,
2653 x_return_status => l_ret_status,
2654 x_error_message => l_error_messsage,
2655 x_message_name => l_msg_name
2656 );
2657
2658 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2659 IF PG_DEBUG <> 0 THEN
2660 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after call to get_start_date with status ' || l_return_status ,1);
2661
2662 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2663 END IF;
2664 RAISE FND_API.G_EXC_ERROR;
2665 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2666 IF PG_DEBUG <> 0 THEN
2667 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to get_start_date ' || l_return_status ,1);
2668
2669 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage,1);
2670 END IF;
2671 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2672 ELSE
2673 IF PG_DEBUG <> 0 THEN
2674 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from get_start_date ' ,1);
2675 END IF;
2676 END IF;
2677
2678 IF (x_parent_job_start_date <= SYSDATE) THEN
2679 l_mlsupply_items(l_index).job_start_date := SYSDATE;
2680
2681 IF PG_DEBUG <> 0 THEN
2682 oe_debug_pub.add('create_subassembly_jobs: ' || 'actual parent starts date is < sysdate ' ,1);
2683 END IF;
2684
2685 ELSE
2686 l_mlsupply_items(l_index).job_start_date := x_parent_job_start_date;
2687 oe_debug_pub.add('create_subassembly_jobs: ' || 'parent_start_date '|| to_char(x_parent_job_start_date,'mm/dd/yy hh:mi:ss') ,1);
2688
2689 END IF;
2690
2691
2692 END IF; --top most item is flow
2693
2694
2695
2696 END IF; --sysdate check
2697
2698 ELSE --infinite scheduler
2699 -- rkaza. 05/05/2005. Added IR also.
2700 IF (x_completion_date <= SYSDATE and l_mlsupply_items(l_index).source_type in (1, 3)) THEN
2701 l_mlsupply_items(l_index).job_completion_date := SYSDATE; --2858631
2702 ELSE
2703 l_mlsupply_items(l_index).job_completion_date := x_completion_date;
2704 END IF;
2705
2706 END IF ;
2707
2708 --original code
2709
2710
2711 ELSE --parent date is before sysdate
2712 -- rkaza. 05/05/2005. Added IR also.
2713 IF (l_mlsupply_items(l_index).source_type in (1, 3) ) THEN --buy and IR item
2714 --buy item shoiuld always get created either on sysdate or after sysdate
2715
2716 l_mlsupply_items(l_index).job_completion_date := SYSDATE;
2717
2718 ELSE --make(discrete/flow)
2719 l_mlsupply_items(l_index).populate_start_date := 1; -- to insert wip inetrface with satrt date
2720
2721 l_mlsupply_items(l_index).job_completion_date := SYSDATE;
2722 l_mlsupply_items(l_index).job_start_date := SYSDATE;
2723 END IF; --buy item
2724
2725 END IF; -- end if parent start date is after sysdate
2726
2727 update bom_cto_mlsupply_main_temp
2728 set job_completion_date = l_mlsupply_items(l_index).job_completion_date,
2729 job_start_date = l_mlsupply_items(l_index).job_start_date -- could be null value
2730 where item_index = l_index
2731 and order_line_id = p_Top_Assembly_LineId ;
2732
2733 END IF; --job completion date
2734
2735 IF (l_mlsupply_items(l_index).source_type = 2 ) THEN --make
2736 IF ( l_mlsupply_items(l_index).cfm_routing_flag = 1 ) THEN --flow item
2737
2738 --flow schedule creation
2739
2740 l_stmt_num := 210;
2741 If PG_DEBUG <> 0 Then
2742 cto_wip_workflow_api_pk.cto_debug ('create_sub_assembly_jobs','calling create flow schedule for'|| l_mlsupply_items(l_index).item_id);
2743
2744 oe_debug_pub.add('create_subassembly_jobs: ' || 'calling create flow schedule for'|| l_mlsupply_items(l_index).item_id);
2745 END IF;
2746
2747 create_flow_subassembly (
2748 pflow_sch_details => l_flow_sch_details,
2749 pIndex => l_index,
2750 pitems_table => l_mlsupply_items,
2751 pShip_org => l_ship_org,
2752 pProject_id => l_project_id,
2753 pTask_id => l_task_id,
2754 x_return_status => l_ret_status,
2755 x_error_message => l_error_messsage,
2756 x_message_name => l_msg_name
2757
2758 );
2759
2760 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2761 IF PG_DEBUG <> 0 THEN
2762 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after call to create_flow_subassembly with status ' || l_return_status ,1);
2763
2764 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2765 END IF;
2766 RAISE FND_API.G_EXC_ERROR;
2767 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2768 IF PG_DEBUG <> 0 THEN
2769 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to create_flow_subassembly ' || l_return_status ,1);
2770
2771 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2772 END IF;
2773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2774 ELSE
2775
2776 IF PG_DEBUG <> 0 THEN
2777 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from create_flow_subassembly ' ,1);
2778 END IF;
2779 END IF;
2780
2781
2782 END IF; --end flow schedule creation
2783 END IF; --make
2784 ELSE --parent is flow item
2785
2786
2787 oe_debug_pub.add (l_mlsupply_items(l_index).item_name || ' parent is a flow item',1);
2788
2789 -- if current item is discrete do not call feeder
2790 -- if current item is flow call feeder
2791
2792
2793
2794 IF(l_mlsupply_items(l_index).cfm_routing_flag =1 ) THEN -- child is flow
2795
2796 oe_debug_pub.add('checking if feeder is run',1);
2797 IF ( l_mlsupply_items(l_mlsupply_items(l_index).parent_index).feeder_run <> 'Y' ) THEN
2798 oe_debug_pub.add('calling feeder line api');
2799
2800 l_stmt_num := 220;
2801 SELECT max(scheduled_completion_date)
2802 into max_completion_date
2803 from BOM_CTO_MLSUPPLY_FLOW_TEMP
2804 where item_index = l_mlsupply_items(l_index).parent_index
2805 and order_line_id = p_Top_Assembly_LineId ;
2806
2807 oe_debug_pub.add('aparameters for feeder call');
2808 oe_debug_pub.add('max schcompletion date'||to_char(max_completion_date,'dd/mm/yy hh:mi:ss') );
2809 oe_debug_pub.add('LIne code :'||l_mlsupply_items(l_mlsupply_items(l_index).parent_index).line_code );
2810
2811 --creating child supply on feeder line
2812 l_stmt_num := 230;
2813 FLM_CREATE_PRODUCT_SYNCH.create_schedules(
2814 errbuf,
2815 retcode,
2816 l_ship_org,
2817 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).line_code,
2818 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).line_code,
2819 to_char(SYSDATE-1,'YYYY/MM/DD hh:mm:ss'),
2820 to_char(max_completion_date,'YYYY/MM/DD hh:mm:ss'), --to_char(SYSDATE+7,'YYYY/MM/DD hh:mm:ss'),
2821 'N');
2822
2823 oe_debug_pub.add('After calklling feeder line api',1);
2824
2825 --set parent feeder run flag to 'Y'
2826 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).feeder_run := 'Y';
2827
2828 END IF ;--parent feeder flag
2829
2830 l_stmt_num := 240;
2831 INSERT INTO BOM_CTO_MLSUPPLY_FLOW_TEMP (
2832 order_line_id,
2833 item_index,
2834 schedule_number,
2835 wip_entity_id,
2836 scheduled_start_date ,
2837 planned_quantity ,
2838 scheduled_completion_date,
2839 build_sequence,
2840 line_id,
2841 synch_schedule_num,
2842 SYNCH_OPERATION_SEQ_NUM )
2843 SELECT p_Top_Assembly_LineId,
2844 l_index, --current child item index
2845 schedule_number,
2846 wip_entity_id,
2847 scheduled_start_date ,
2848 planned_quantity ,
2849 scheduled_completion_date,
2850 build_sequence,
2851 line_id,
2852 synch_schedule_num,
2853 SYNCH_OPERATION_SEQ_NUM
2854 FROM wip_flow_schedules
2855 where primary_item_id = l_mlsupply_items(l_index).item_id
2856 and synch_schedule_num in
2857 ( Select schedule_number
2858 from BOM_CTO_MLSUPPLY_FLOW_TEMP
2859 where item_index = l_mlsupply_items(l_index).parent_index
2860 and order_line_id = p_Top_Assembly_LineId
2861 );
2862 ELSE--child is wip/buy/IR
2863
2864
2865 l_stmt_num := 242;
2866
2867 --bugfix 2765109
2868 BEGIN
2869 Select nvl(line_op_seq_id,-99) --bugfix 2786582
2870 into l_operation_seq_id
2871 from bom_operation_sequences
2872 where routing_sequence_id = l_mlsupply_items(l_mlsupply_items(l_index).parent_index).routing_sequence_id
2873 and operation_seq_num = l_mlsupply_items(l_index).operation_seq_num
2874 and operation_type =1
2875 and nvl(EFFECTIVITY_DATE,sysdate+1) <= SYSDATE
2876 and nvl(disable_date,sysdate+1) > sysdate;
2877
2878 EXCEPTION
2879 WHEN no_data_found THEN
2880 l_operation_seq_id := -99;
2881
2882 END;
2883
2884 IF PG_DEBUG <> 0 THEN
2885 oe_debug_pub.add ('create_subassembly_jobs: ' || 'operation seq id is' || l_operation_seq_id ,1);
2886
2887
2888 END IF;
2889
2890 IF l_operation_seq_id = -99 THEN --bugfix 2765109
2891 l_stmt_num := 2421;
2892 SELECT min(scheduled_start_date)
2893 into l_child_operation_date
2894 from BOM_CTO_MLSUPPLY_FLOW_TEMP
2895 where item_index = l_mlsupply_items(l_index).parent_index;
2896
2897 IF PG_DEBUG <> 0 THEN
2898
2899 oe_debug_pub.add ('create_subassembly_jobs: ' || 'operation offsetd date is '|| l_child_operation_date,1);
2900
2901 END IF;
2902
2903 ELSE
2904
2905 IF PG_DEBUG <> 0 THEN
2906
2907 oe_debug_pub.add ('create_subassembly_jobs: ' || 'before entering get_operation offsetd ate' ,1);
2908
2909 END IF;
2910
2911 l_stmt_num := 241;
2912 SELECT min(scheduled_completion_date)
2913 into l_min_completion_date
2914 from BOM_CTO_MLSUPPLY_FLOW_TEMP
2915 where item_index = l_mlsupply_items(l_index).parent_index;
2916
2917 IF PG_DEBUG <> 0 THEN
2918 oe_debug_pub.add ('create_subassembly_jobs: ' || 'min schedule date' || to_char(l_min_completion_date,'mm/dd/yy hh:mi:ss') ,1);
2919
2920 END IF;
2921
2922
2923 l_stmt_num := 243;
2924 l_child_operation_date := MRP_FLOW_SCHEDULE_PUB.get_operation_offset_date
2925 ( p_api_version_number => 1.0,
2926 x_return_status => l_x_return_status,
2927 x_msg_count => l_x_msg_count,
2928 x_msg_data => l_x_msg_data,
2929 p_org_id => l_ship_org,
2930 p_assembly_item_id => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).item_id,
2931 p_routing_sequence_id => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).routing_sequence_id,
2932 p_operation_sequence_id => l_operation_seq_id,
2933 p_assembly_qty => 1, --? what should this quantity be ,ask adrian
2934 p_assembly_comp_date => l_min_completion_date ,
2935 p_calculate_option => 1 --implies for the first unit made
2936 );
2937
2938
2939 END IF;
2940
2941 IF PG_DEBUG <> 0 THEN
2942 oe_debug_pub.add ('create_subassembly_jobs: ' || 'after get_ioperation offset date with date' || l_child_operation_date ,1);
2943
2944 END IF;
2945
2946 l_stmt_num := 244;
2947
2948 -- rkaza. 05/05/2005. Added IR here.
2949 -- Comp date can be set as child op date for IR.
2950 -- No lead time considerations for IR.
2951
2952 IF (l_mlsupply_items(l_index).source_type in (1, 3) ) THEN -- buy and IR child item
2953
2954 l_lead_time := CEIL(l_mlsupply_items(l_index).postprocessing_lead_time + 1); --ie postporcoessing+1day
2955
2956 x_child_completion_date := l_child_operation_date;
2957
2958 IF PG_DEBUG <> 0 THEN
2959 oe_debug_pub.add ('create_subassembly_jobs: ' || 'lead time for buy child is ' || l_lead_time ,1);
2960
2961 END IF;
2962
2963 if l_mlsupply_items(l_index).source_type = 3 then
2964 l_stmt_num := 250;
2965 get_working_day
2966 (
2967 porgid => l_ship_org,
2968 Pdate => l_child_operation_date,
2969 pleadtime =>l_lead_time,
2970 pdirection => 'B', --direction in getting working day 'backward' for buy item as here it is always backward
2971 x_ret_date => x_child_completion_date,
2972 x_return_status => l_ret_status,
2973 x_error_message => l_error_messsage,
2974 x_message_name => l_msg_name
2975 );
2976
2977 end if;
2978
2979 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2980 IF PG_DEBUG <> 0 THEN
2981 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after get_wroking_day' || l_return_status ,1);
2982
2983 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2984 END IF;
2985 RAISE FND_API.G_EXC_ERROR;
2986 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2987 IF PG_DEBUG <> 0 THEN
2988 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to get_working_day' || l_return_status ,1);
2989
2990 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2991 END IF;
2992 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2993 ELSE
2994
2995 IF PG_DEBUG <> 0 THEN
2996 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from get_working_day ' ,1);
2997 END IF;
2998 END IF;
2999
3000 IF (x_child_completion_date <= SYSDATE) THEN
3001 IF PG_DEBUG <> 0 THEN
3002 oe_debug_pub.add('create_subassembly_jobs: ' || 'buy or IR job comp date is < than sysdate,so default to sysdate' ,1);
3003 oe_debug_pub.add('create_subassembly_jobs: ' || 'buy or IR job comp date is ' || x_child_completion_date,1);
3004 END IF;
3005
3006 l_mlsupply_items(l_index).job_completion_date := SYSDATE;
3007
3008 ELSE
3009
3010 l_mlsupply_items(l_index).job_completion_date := x_child_completion_date;
3011
3012 IF PG_DEBUG <> 0 THEN
3013 oe_debug_pub.add('create_subassembly_jobs: ' || 'buy or IR job comp date is ' || x_child_completion_date,1);
3014 END IF;
3015
3016
3017 END IF;
3018
3019 update bom_cto_mlsupply_main_temp
3020 set job_completion_date = l_mlsupply_items(l_index).job_completion_date
3021 where item_index = l_index
3022 and order_line_id = p_Top_Assembly_LineId;
3023
3024
3025 ELSE --discrete child item
3026
3027 --get the value of wip finite scheduler flag if not selected previously when uder flow
3028 IF(l_finite_scheduler_flag is null) THEN
3029
3030 SELECT nvl(use_finite_scheduler,2)
3031 INTO l_finite_scheduler_flag
3032 FROM wip_parameters
3033 WHERE organization_id = l_ship_org;
3034
3035 oe_debug_pub.add ('create_subassembly_jobs: ' || 'l_finite_scheduler_flag is '|| l_finite_scheduler_flag ,1);
3036
3037 END IF;--finnite scheduler
3038
3039
3040 IF (l_finite_scheduler_flag =1) THEN
3041
3042 IF( l_child_operation_date <= SYSDATE) THEN --less than sysdate
3043 oe_debug_pub.add('create_subassembly_jobs: ' || 'Finite scheduler ON and DISCRETE job comp date is <SYSDATE., SO DEFAULT TO SYSDTAE',1);
3044 oe_debug_pub.add('create_subassembly_jobs: '|| ' DIS job coompl date is l_child_operation_date',1);
3045 l_mlsupply_items(l_index).populate_start_date := 1; -- to insert wip inetrface with satrt date
3046
3047 l_mlsupply_items(l_index).job_completion_date := SYSDATE;
3048 l_mlsupply_items(l_index).job_start_date := SYSDATE;
3049
3050 update bom_cto_mlsupply_main_temp
3051 set job_completion_date = l_mlsupply_items(l_index).job_completion_date,
3052 job_start_date = l_mlsupply_items(l_index).job_start_date
3053 where item_index = l_index
3054 and order_line_id = p_Top_Assembly_LineId;
3055
3056 ELSE-- greater sysdate
3057
3058 l_mlsupply_items(l_index).job_completion_date := l_child_operation_date;
3059 oe_debug_pub.add('create_subassembly_jobs: ' || 'DISCRETE job comp date is '|| l_mlsupply_items(l_index).job_completion_date,1);
3060
3061
3062 --if flow is top most item AND
3063 --if finite scheduler is on we calculate job start date
3064 --as we need to insert both first unit start date as well as last unit completion date
3065 --bugfix#2739590
3066
3067
3068 IF(l_mlsupply_items(1).cfm_routing_flag = 1) THEN --top most item is flow
3069
3070 get_start_date(
3071 pCompletion_date => l_mlsupply_items(l_index).job_completion_date,
3072 pQty => l_mlsupply_items(l_index).needed_item_qty,
3073 pitemid => l_mlsupply_items(l_index).item_id,
3074 porganization_id => l_ship_org,
3075 pfixed_leadtime => l_mlsupply_items(l_index).fixed_lead_time,
3076 pvariable_leadtime => l_mlsupply_items(l_index).variable_lead_time,
3077 x_start_date => x_parent_job_start_date,
3078 x_return_status => l_ret_status,
3079 x_error_message => l_error_messsage,
3080 x_message_name => l_msg_name
3081 );
3082
3083 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
3084 IF PG_DEBUG <> 0 THEN
3085 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after call to get_start_date with status ' || l_return_status ,1);
3086
3087 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
3088 END IF;
3089 RAISE FND_API.G_EXC_ERROR;
3090 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3091 IF PG_DEBUG <> 0 THEN
3092 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to get_start_date ' || l_return_status ,1);
3093
3094 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage,1);
3095 END IF;
3096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3097 ELSE
3098 IF PG_DEBUG <> 0 THEN
3099 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from get_start_date ' ,1);
3100 END IF;
3101 END IF;
3102
3103 IF (x_parent_job_start_date <= SYSDATE) THEN
3104 l_mlsupply_items(l_index).job_start_date := SYSDATE;
3105
3106 IF PG_DEBUG <> 0 THEN
3107 oe_debug_pub.add('create_subassembly_jobs: ' || 'actual parent starts date is < sysdate ' ,1);
3108 END IF;
3109
3110 ELSE
3111 l_mlsupply_items(l_index).job_start_date := x_parent_job_start_date;
3112 oe_debug_pub.add('create_subassembly_jobs: ' || 'parent_start_date '|| to_char(x_parent_job_start_date,'mm/dd/yy hh:mi:ss') ,1);
3113
3114 END IF;
3115
3116
3117 END IF; --top most item is flow
3118
3119 update bom_cto_mlsupply_main_temp
3120 set job_completion_date = l_mlsupply_items(l_index).job_completion_date,
3121 job_start_date = l_mlsupply_items(l_index).job_start_date
3122 where item_index = l_index
3123 and order_line_id = p_Top_Assembly_LineId;
3124
3125 END IF;--sysdate checj
3126 ELSE--infinite scheduler
3127
3128
3129 l_mlsupply_items(l_index).job_completion_date := l_child_operation_date;
3130
3131
3132 oe_debug_pub.add('create_subassembly_jobs: ' || 'DISCRETE job comp date is '|| l_mlsupply_items(l_index).job_completion_date,1);
3133
3134 update bom_cto_mlsupply_main_temp
3135 set job_completion_date = l_mlsupply_items(l_index).job_completion_date
3136 where item_index = l_index
3137 and order_line_id = p_Top_Assembly_LineId;
3138
3139
3140 END IF;--finute scheduler check
3141
3142
3143 END IF; -- child is buy or IR/discrete
3144
3145
3146 END IF; --child is flow or others
3147
3148
3149 END IF ; --parent is WIP or flow
3150
3151 END IF; --check for item sourcing
3152
3153 EXIT WHEN l_index = l_mlsupply_items.LAST;
3154 l_index := l_mlsupply_items.NEXT(l_index);
3155 END LOOP;
3156
3157 END IF;
3158
3159
3160
3161 --reomve this part at end of UT
3162 If PG_DEBUG <> 0 Then
3163 oe_debug_pub.add ('DEBUG EMSSAEG AFTER COMPLETION DATE CALCULATION',1);
3164
3165 cto_wip_workflow_api_pk.cto_debug('Completion date debug messages','after completion date calculations');
3166 End if;
3167 --displaying children
3168 IF (l_mlsupply_items.count > 0) THEN --checks for uninitialized collection --bugfix2308063
3169
3170 oe_debug_pub.add ('index --'||'item_name--' || 'item_quantity--'||'needed_item_quantity--'||'OPERATION_LEAD_TIME_PERCENT--'||
3171 'operation_seq_num--'|| 'cfm_routing_flag--'||'routing_sequence_id--'||'fixed_lead_time--'||
3172 'variable_lead_time--' || 'processing_lead_time--' || 'postprocessing_lead_time--' ||
3173 'bom_item_type --' || 'parent_index--'||'job_start_date --'||'job_completion_date --'||
3174 'line_id--' || 'line_code--'||'source_type--' || 'feeder_run--' || 'flow_start_index--' ||
3175 'flow_end_index '
3176 );
3177
3178 l_index := 1;--completion date is needed from 2nd level discrete item
3179 LOOP
3180 oe_debug_pub.add ('idx=>'||l_mlsupply_items(l_index).t_item_details_index || '--' ||
3181 'name=>'||l_mlsupply_items(l_index).item_name||'--' ||
3182 'qty=>'||l_mlsupply_items(l_index).item_quantity||'--'||
3183 'qty=>'||l_mlsupply_items(l_index).needed_item_qty||'--'||
3184 'LT%=>'||l_mlsupply_items(l_index).OPERATION_LEAD_TIME_PERCENT||'--'||
3185 'opseq=>'||l_mlsupply_items(l_index).operation_seq_num||'--'||
3186 'cfm=>'||l_mlsupply_items(l_index).cfm_routing_flag||'--'||
3187 'rout=>'||l_mlsupply_items(l_index).routing_sequence_id||'--'||
3188 'FLT=>'||l_mlsupply_items(l_index).fixed_lead_time||'--'||
3189 'VLT=>'||l_mlsupply_items(l_index).variable_lead_time||'--' ||
3190 'PLT=>'||l_mlsupply_items(l_index).processing_lead_time||'--' ||
3191 'PPLT=>'||l_mlsupply_items(l_index).postprocessing_lead_time||'--' ||
3192 'BIT=>'||l_mlsupply_items(l_index).bom_item_type||' --' ||
3193 'PIDX=>'||l_mlsupply_items(l_index).parent_index||'--'||
3194 'JSTDT=>'||l_mlsupply_items(l_index).job_start_date||' --'||
3195 'JCDT=>'||l_mlsupply_items(l_index).job_completion_date||' --'||
3196 'FLINE=>'||l_mlsupply_items(l_index).line_id||'--' ||
3197 'FLcode=>'||l_mlsupply_items(l_index).line_code||'--'||
3198 'Src=>'||l_mlsupply_items(l_index).source_type||'--' ||
3199 'Feed=>'||l_mlsupply_items(l_index).feeder_run||'--' ||
3200 'FLOW_ST_IDX=>'||l_mlsupply_items(l_index).flow_start_index||'--' ||
3201 'FLOW_END_IDX=>'||l_mlsupply_items(l_index).flow_end_index
3202 );
3203
3204 If PG_DEBUG <> 0 Then
3205 cto_wip_workflow_api_pk.cto_debug ('Create_sub_assembly_jobs','idx=>'||l_mlsupply_items(l_index).t_item_details_index || '--' ||
3206 'name=>'||l_mlsupply_items(l_index).item_name||'--' ||
3207 'qty=>'||l_mlsupply_items(l_index).item_quantity||'--'||
3208 'qty=>'||l_mlsupply_items(l_index).needed_item_qty||'--'||
3209 'LT%=>'||l_mlsupply_items(l_index).OPERATION_LEAD_TIME_PERCENT||'--'||
3210 'opseq=>'||l_mlsupply_items(l_index).operation_seq_num||'--'||
3211 'cfm=>'||l_mlsupply_items(l_index).cfm_routing_flag||'--'||
3212 'rout=>'||l_mlsupply_items(l_index).routing_sequence_id||'--'||
3213 'FLT=>'||l_mlsupply_items(l_index).fixed_lead_time||'--'||
3214 'VLT=>'||l_mlsupply_items(l_index).variable_lead_time||'--' ||
3215 'PLT=>'||l_mlsupply_items(l_index).processing_lead_time||'--' ||
3216 'PPLT=>'||l_mlsupply_items(l_index).postprocessing_lead_time||'--' ||
3217 'BIT=>'||l_mlsupply_items(l_index).bom_item_type||' --' ||
3218 'PIDX=>'||l_mlsupply_items(l_index).parent_index||'--'||
3219 'JSTDT=>'||l_mlsupply_items(l_index).job_start_date||' --'||
3220 'JCDT=>'||l_mlsupply_items(l_index).job_completion_date||' --'||
3221 'FLINE=>'||l_mlsupply_items(l_index).line_id||'--' ||
3222 'FLcode=>'||l_mlsupply_items(l_index).line_code||'--'||
3223 'Src=>'||l_mlsupply_items(l_index).source_type||'--' ||
3224 'Feed=>'||l_mlsupply_items(l_index).feeder_run||'--' ||
3225 'FLOW_ST_IDX=>'||l_mlsupply_items(l_index).flow_start_index||'--' ||
3226 'FLOW_END_IDX=>'||l_mlsupply_items(l_index).flow_end_index
3227 );
3228
3229
3230 End if;
3231
3232
3233
3234
3235
3236 EXIT WHEN l_index = l_mlsupply_items.LAST;
3237 l_index := l_mlsupply_items.NEXT(l_index);
3238 END LOOP;
3239
3240 END IF;
3241 l_index := null;
3242 --removw above part at end of UT
3243
3244
3245
3246
3247
3248 --end of calcultating compeltion date
3249
3250
3251
3252
3253 IF (l_mlsupply_items.count > 0) THEN --checks for uninitialized collection
3254
3255 l_index := l_mlsupply_items.FIRST;
3256 LOOP
3257
3258
3259 IF PG_DEBUG <> 0 THEN
3260 oe_debug_pub.add('create_subassembly_jobs: ' || l_index||'-- '||
3261 l_mlsupply_items(l_index).parent_index||'-- '||
3262 l_mlsupply_items(l_index).item_id||' -- '||
3263 l_mlsupply_items(l_index).item_name||' -- '||
3264 l_mlsupply_items(l_index).job_start_date||' -- '||
3265 l_mlsupply_items(l_index).job_completion_date||' -- '||
3266 l_mlsupply_items(l_index).fixed_lead_time||' -- '||
3267 l_mlsupply_items(l_index).variable_lead_time||' -- '||
3268 l_mlsupply_items(l_index).processing_lead_time,1);
3269 END IF;
3270
3271 EXIT WHEN l_index = l_mlsupply_items.LAST;
3272 l_index := l_mlsupply_items.NEXT(l_index);
3273 END LOOP;
3274 END IF;
3275
3276
3277 IF PG_DEBUG <> 0 THEN
3278 oe_debug_pub.add('create_subassembly_jobs: ' || 'before inserting children in wjsi ',1);
3279 END IF;
3280
3281 --to test submission of wip concurrent program from PL/SQL
3282
3283 SAVEPOINT REBUILD;
3284 l_index := 2;
3285 LOOP
3286 -- rkaza. ireq project. 05/05/2005. Firing
3287 -- populate_req_interface for IR also. Passsing source type
3288 -- source org as additional parameters.
3289
3290 IF (l_mlsupply_items(l_index).source_type in (1, 3) and
3291 l_mlsupply_items(l_index).needed_item_qty >0
3292 --4645636
3293 and l_mlsupply_items(l_index).wip_supply_type <> 6
3294 ) then
3295
3296 l_stmt_num := 260;
3297
3298 If PG_DEBUG <> 0 Then
3299 cto_wip_workflow_api_pk.cto_debug ('create_sub_assembly_jobs','insert po_interafce'|| l_mlsupply_items(l_index).item_id);
3300 cto_wip_workflow_api_pk.cto_debug ('create_sub_assembly_jobs','need by date'|| to_char(l_mlsupply_items(l_index).job_completion_date,'mm/dd/yy/ hh:mi:ss'));
3301 cto_wip_workflow_api_pk.cto_debug ('create_sub_assembly_jobs','need aty'||l_mlsupply_items(l_index).needed_item_qty);
3302 End if;
3303
3304 IF PG_DEBUG <> 0 THEN
3305 oe_debug_pub.add('create_subassembly_jobs: ' || 'insert po_interafce'|| l_mlsupply_items(l_index).item_id);
3306 oe_debug_pub.add('create_sub_assembly_jobs insert po_interafce'|| l_mlsupply_items(l_index).item_id);
3307 oe_debug_pub.add('create_sub_assembly_jobs need by date'|| to_char(l_mlsupply_items(l_index).job_completion_date,'mm/dd/yy/ hh:mi:ss'));
3308 oe_debug_pub.add('create_sub_assembly_jobs need aty'||l_mlsupply_items(l_index).needed_item_qty);
3309
3310 END IF;
3311
3312 l_req_input_data.source_type := l_mlsupply_items(l_index).source_type;
3313 l_req_input_data.sourcing_org := l_mlsupply_items(l_index).sourcing_org;
3314
3315 cto_auto_procure_pk.populate_req_interface (
3316 p_interface_source_code =>'CTO-LOWER LEVEL',
3317 p_destination_org_id =>l_ship_org,
3318 p_org_id =>null,
3319 p_created_by =>p_user_id, -- created_by
3320 p_need_by_date =>l_mlsupply_items(l_index).job_completion_date,
3321 p_order_quantity =>l_mlsupply_items(l_index).needed_item_qty,
3322 p_order_uom =>l_ordered_uom,
3323 p_item_id =>l_mlsupply_items(l_index).item_id,
3324 p_item_revision => null, --so_line.item_revision
3325 -- reverted bugfix 3042904 and provided
3326 --solution thru fix 3129117
3327 p_interface_source_line_id=>p_Top_Assembly_LineId,
3328 p_unit_price => null, -- req-import decides this price
3329 --not so_line.unit_selling_price,
3330 p_batch_id =>null,
3331 p_order_number =>l_order_number,
3332 p_req_interface_input_data => l_req_input_data,
3333 x_return_status =>x_return_status );
3334
3335 --change this varname from x_reaturn status to somethinelse
3336 IF x_return_status = FND_API.G_RET_STS_ERROR THEN --po return status
3337 RAISE FND_API.G_EXC_ERROR;
3338 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN --po return status
3339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3340 ELSE
3341
3342 IF PG_DEBUG <> 0 THEN
3343 oe_debug_pub.add('create_subassembly_jobs: ' || 'Req Insert successful for '|| l_mlsupply_items(l_index).item_id ,1);
3344 END IF;
3345 IF ( l_sub_level_buy_item = 'N') THEN
3346 l_sub_level_buy_item := 'Y';
3347 END IF;
3348
3349 END IF; --PO return status
3350
3351 ELSIF (l_mlsupply_items(l_index).source_type = 2
3352 and l_mlsupply_items(l_index).needed_item_qty >0
3353 --4645636
3354 and l_mlsupply_items(l_index).wip_supply_type<>6
3355 ) THEN --make in this org
3356
3357
3358 If(l_mlsupply_items(l_index).cfm_routing_flag = 2 OR
3359 l_mlsupply_items(l_index).cfm_routing_flag = -99) THEN -- discrete routing
3360
3361 IF PG_DEBUG <> 0 THEN
3362 oe_debug_pub.add('create_subassembly_jobs: ' || 'Status passed into lower level supply code is ' || p_status_type);
3363 END IF;
3364
3365 IF(l_mlsupply_items(1).cfm_routing_flag = 2 OR
3366 l_mlsupply_items(1).cfm_routing_flag = -99) THEN --top most parent is discrete
3367
3368 l_stmt_num := 280;
3369
3370 -- Fixed bug 5346922
3371 -- Removed the decode for supply type
3372 insert into wip_job_schedule_interface
3373 (last_update_date,
3374 last_updated_by,
3375 creation_date,
3376 created_by,
3377 last_update_login,
3378 request_id,
3379 program_id,
3380 program_application_id,
3381 program_update_date,
3382 group_id,
3383 source_code,
3384 process_phase,
3385 process_status,
3386 organization_id,
3387 load_type,
3388 status_type,
3389 last_unit_completion_date,
3390 primary_item_id,
3391 wip_supply_type,
3392 class_code,
3393 firm_planned_flag,
3394 start_quantity,
3395 bom_revision_date,
3396 routing_revision_date,
3397 project_id,
3398 task_id,
3399 due_date,
3400 bom_revision
3401
3402
3403 )
3404 select SYSDATE,
3405 p_user_id,
3406 SYSDATE,
3407 p_user_id,
3408 p_conc_login_id,
3409 p_conc_request_id,
3410 p_conc_program_id,
3411 p_appl_conc_program_id,
3412 SYSDATE,
3413 p_wip_seq,
3414 'WICDOL',
3415 WIP_CONSTANTS.ML_VALIDATION,
3416 WIP_CONSTANTS.PENDING, -- process_status
3417 l_ship_org, -- organization id
3418 WIP_CONSTANTS.CREATE_JOB, --Load_Type
3419 nvl(p_status_type, WIP_CONSTANTS.UNRELEASED), -- Status_Type
3420 l_mlsupply_items(l_index).job_completion_date, -- Date Completed
3421 l_mlsupply_items(l_index).item_id, --Primary_Item_Id
3422 WIP_CONSTANTS.BASED_ON_BOM, -- Wip_Supply_Type
3423 decode(p_class_code, null, null
3424 , p_class_code), --Accouting Class
3425 2, --Firm_Planned_Flag
3426 l_mlsupply_items(l_index).needed_item_qty,
3427 trunc(greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
3428 'MI')+1/(60*24), --BOM_Revision_Date
3429 greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
3430 --Routing_Revision_Date
3431 --bugfix 3418102
3432 decode(l_mlsupply_items(l_index).pegging_flag,'I',l_project_id,'X',l_project_id, null),
3433 decode(l_mlsupply_items(l_index).pegging_flag,'I',l_task_id,'X',l_task_id, null),
3434 --end bugfix 3418102
3435 l_mlsupply_items(l_index).job_completion_date,
3436 BOM_REVISIONS.get_item_revision_fn
3437 ( 'ALL',
3438 'ALL',
3439 l_ship_org,
3440 l_mlsupply_items(l_index).item_id,
3441 (trunc (greatest(nvl(cal.calendar_date,SYSDATE),
3442 SYSDATE),'MI')+1/(60*24) )
3443 )
3444
3445 from bom_calendar_dates cal,
3446 mtl_parameters mp,
3447 wip_parameters wp,
3448 mtl_system_items msi
3449 where mp.organization_id = l_ship_org
3450 and wp.organization_id = mp.organization_id
3451 and msi.organization_id = l_ship_org
3452 and msi.inventory_item_id = l_mlsupply_items(l_index).item_id --inventory item id
3453 and cal.calendar_code = mp.calendar_code
3454 and cal.exception_set_id = mp.calendar_exception_set_id
3455 and cal.seq_num =
3456 (select greatest(1, (cal2.prior_seq_num -
3457 (ceil(nvl(msi.fixed_lead_time,0) +
3458 nvl(msi.variable_lead_time,0) *
3459 l_mlsupply_items(l_index).needed_item_qty --bugfix 2074290: this is in primary uom
3460 ))))
3461 from bom_calendar_dates cal2
3462 where cal2.calendar_code = mp.calendar_code
3463 and cal2.exception_set_id =
3464 mp.calendar_exception_set_id
3465 and cal2.calendar_date =
3466 trunc(l_mlsupply_items(l_index).job_completion_date)
3467 );
3468
3469
3470
3471
3472
3473
3474
3475 if (SQL%ROWCOUNT > 0) then
3476 IF PG_DEBUG <> 0 THEN
3477 oe_debug_pub.add('create_subassembly_jobs: ' || 'Number of Rows Inserted in WJSI for children : ' || to_char(SQL%ROWCOUNT));
3478
3479 oe_debug_pub.add('create_subassembly_jobs: ' || 'GROUP ID Inserted in WJSI for children : ' || x_groupID);
3480 END IF;
3481 x_return_status := FND_API.G_RET_STS_SUCCESS;
3482 else
3483 x_return_status := FND_API.G_RET_STS_ERROR;
3484 cto_msg_pub.cto_message('BOM', 'BOM_ATO_PROCESS_ERROR');
3485
3486 end if;
3487
3488 ELSIF(l_mlsupply_items(1).cfm_routing_flag = 1
3489 and l_mlsupply_items(l_index).needed_item_qty >0
3490
3491 --4645636
3492 and l_mlsupply_items(l_index).wip_supply_type<>6
3493 ) THEN --top most parent is flow
3494
3495 IF (l_discrete_under_flow = 'N') THEN
3496 l_discrete_under_flow := 'Y';
3497
3498 --intialize var's to be used in isnerting WJSI table
3499 l_user_id := FND_GLOBAL.USER_ID;
3500 l_login_id := FND_GLOBAL.LOGIN_ID;
3501 --l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
3502 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
3503
3504 select wip_job_schedule_interface_s.nextval
3505 into x_groupID
3506 from dual;
3507
3508
3509 END IF;
3510
3511 -- Fixed bug 5346922
3512 -- Removed the decode for supply type
3513 l_stmt_num := 280;
3514 insert into wip_job_schedule_interface
3515 (last_update_date,
3516 last_updated_by,
3517 creation_date,
3518 created_by,
3519 last_update_login,
3520 request_id,
3521 program_id,
3522 program_application_id,
3523 program_update_date,
3524 group_id,
3525 source_code,
3526 process_phase,
3527 process_status,
3528 organization_id,
3529 load_type,
3530 status_type,
3531 last_unit_completion_date,
3532 primary_item_id,
3533 wip_supply_type,
3534 class_code,
3535 firm_planned_flag,
3536 start_quantity,
3537 bom_revision_date,
3538 routing_revision_date,
3539 project_id,
3540 task_id,
3541 due_date,
3542 bom_revision,
3543 scheduling_method, --inserted ml_manual inorder to stop finite scheduler run
3544 first_unit_start_date --enter first unit start date if finite scheduler is turned on bugfix#2739590
3545
3546 )
3547 select SYSDATE,
3548 p_user_id,--l_user_id,
3549 SYSDATE,
3550 p_user_id,--l_user_id,
3551 null, --l_login_id,
3552 null,
3553 null,--35740,
3554 null,--706,
3555 SYSDATE,
3556 x_groupID,
3557 'WICDOL',
3558 WIP_CONSTANTS.ML_VALIDATION,
3559 WIP_CONSTANTS.PENDING, -- process_status
3560 l_ship_org, -- organization id
3561 WIP_CONSTANTS.CREATE_JOB, --Load_Type
3562 nvl(p_status_type, WIP_CONSTANTS.UNRELEASED), -- Status_Type
3563 l_mlsupply_items(l_index).job_completion_date, -- Date Completed
3564 l_mlsupply_items(l_index).item_id, --Primary_Item_Id
3565 WIP_CONSTANTS.BASED_ON_BOM, -- Wip_Supply_Type
3566 decode(p_class_code, null, null
3567 , p_class_code), --Accouting Class
3568 2, --Firm_Planned_Flag
3569 l_mlsupply_items(l_index).needed_item_qty,
3570 trunc(greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
3571 'MI')+1/(60*24), --BOM_Revision_Date
3572 greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
3573 --Routing_Revision_Date
3574 --bugfix 3418102
3575 decode(l_mlsupply_items(l_index).pegging_flag,'I',l_project_id,'X',l_project_id, null),
3576 decode(l_mlsupply_items(l_index).pegging_flag,'I',l_task_id,'X',l_task_id, null),
3577 --end bugfix 3418102
3578 l_mlsupply_items(l_index).job_completion_date,
3579 BOM_REVISIONS.get_item_revision_fn
3580 ( 'ALL',
3581 'ALL',
3582 l_ship_org,
3583 l_mlsupply_items(l_index).item_id,
3584 (trunc (greatest(nvl(cal.calendar_date,SYSDATE),
3585 SYSDATE),'MI')+1/(60*24) )
3586 ),
3587 decode(nvl(wp.use_finite_scheduler,2), 1,
3588 WIP_CONSTANTS.ML_MANUAL,
3589 null),
3590 decode(nvl(wp.use_finite_scheduler,2), 1,
3591 l_mlsupply_items(l_index).job_start_date,
3592 null)
3593 from bom_calendar_dates cal,
3594 mtl_parameters mp,
3595 wip_parameters wp,
3596 mtl_system_items msi
3597 where mp.organization_id = l_ship_org
3598 and wp.organization_id = mp.organization_id
3599 and msi.organization_id = l_ship_org
3600 and msi.inventory_item_id = l_mlsupply_items(l_index).item_id --inventory item id
3601 and cal.calendar_code = mp.calendar_code
3602 and cal.exception_set_id = mp.calendar_exception_set_id
3603 and cal.seq_num =
3604 (select greatest(1, (cal2.prior_seq_num -
3605 (ceil(nvl(msi.fixed_lead_time,0) +
3606 nvl(msi.variable_lead_time,0) *
3607 l_mlsupply_items(l_index).needed_item_qty --bugfix 2074290: this is in primary uom
3608 ))))
3609 from bom_calendar_dates cal2
3610 where cal2.calendar_code = mp.calendar_code
3611 and cal2.exception_set_id =
3612 mp.calendar_exception_set_id
3613 and cal2.calendar_date =
3614 trunc(l_mlsupply_items(l_index).job_completion_date)
3615 );
3616
3617 if (SQL%ROWCOUNT > 0) then
3618 IF PG_DEBUG <> 0 THEN
3619 oe_debug_pub.add('create_subassembly_jobs: ' || 'Number of Rows Inserted in WJSI for children : ' || to_char(SQL%ROWCOUNT));
3620
3621 oe_debug_pub.add('create_subassembly_jobs: ' || 'GROUP ID Inserted in WJSI for children : ' || x_groupID);
3622 END IF;
3623
3624 end if;
3625
3626
3627
3628
3629 END IF;--end of check for top most parent type
3630
3631
3632
3633
3634 END IF; --discrete routing , flow not checked as flow supply created during completion date calculation
3635
3636 -- rkaza. 05/05/2005. Removed IR from here. Do nothing only
3637 -- for multiple sources.
3638 ELSIF (l_mlsupply_items(l_index).source_type = 66) THEN
3639 -- 66 = multiple sources
3640 IF PG_DEBUG <> 0 THEN
3641 oe_debug_pub.add('create_subassembly_jobs: ' || 'item ' ||l_mlsupply_items(l_index).item_id || ' --'||l_mlsupply_items(l_index).item_name||'has multiple sources');
3642 END IF;
3643
3644 END IF; --source_type
3645
3646
3647
3648 EXIT WHEN l_index = l_mlsupply_items.LAST;
3649 l_index := l_mlsupply_items.NEXT(l_index);
3650
3651
3652
3653 END LOOP;
3654
3655 IF ( l_mlsupply_items(1).cfm_routing_flag = 2 OR
3656 l_mlsupply_items(1).cfm_routing_flag = -99 ) THEN --top most parent is discrete
3657 null;
3658
3659
3660
3661 ELSIF ( l_mlsupply_items(1).cfm_routing_flag = 1) THEN --top most paernt is flow
3662
3663 --bugfix 2755695
3664 IF l_sub_level_buy_item = 'Y' THEN
3665
3666 l_token(1).token_name := 'IMPORT_SOURCE_CODE';
3667 l_token(1).token_value := 'CTO_LOWER LEVEL';
3668
3669 cto_msg_pub.cto_message('BOM', 'CTO_SUB_LEVEL_BUY_ITEMS',l_token);
3670
3671
3672 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
3673
3674
3675
3676
3677 END IF;--sublevel buy item bugfix 2755695
3678
3679
3680 IF ( l_discrete_under_flow = 'Y') THEN
3681 l_stmt_num := 300;
3682
3683
3684
3685 l_requestId := fnd_request.submit_request('WIP',
3686 'WICMLP',
3687 null,
3688 null,
3689 false,
3690 to_char(x_groupID),
3691 to_char(WIP_CONSTANTS.ATO),
3692 to_char(WIP_CONSTANTS.NO));
3693
3694
3695
3696 if(l_requestId = 0) then --conc. req not spawned
3697 x_retVal := FND_API.G_RET_STS_ERROR;
3698 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
3699 fnd_message.set_token('ERROR_TEXT', 'WICMLP');
3700 x_errMsg := fnd_message.get;
3701 ROLLBACK TO REBUILD;
3702 else
3703 IF PG_DEBUG <> 0 THEN
3704 oe_debug_pub.add('create_subassembly_jobs: ' || 'REQUEST ID Inserted in WJSI for children : ' || l_requestId);
3705 cto_wip_workflow_api_pk.cto_debug ('Requets id is=> ', l_requestId);
3706
3707 l_token(1).token_name := 'REQUEST_ID';
3708 l_token(1).token_value := l_requestId;
3709
3710
3711
3712 cto_msg_pub.cto_message('BOM', 'CTO_SUB_LEVEL_DISCRETE_REQ',l_token); --bugfix 2755695
3713
3714
3715 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
3716
3717 END IF;
3718 commit;
3719 end if;
3720
3721 END IF ; --lauch wip mass load
3722
3723
3724 END IF; --top most paernt type
3725
3726 get_mlsupply_details( l_return_status,
3727 l_error_messsage,
3728 l_msg_name );
3729
3730
3731
3732
3733 EXCEPTION
3734 when FND_API.G_EXC_ERROR then
3735
3736 x_return_status := FND_API.G_RET_STS_ERROR;
3737 x_error_message := 'CTOSUBSB.create_sub_assembly_jobs expected excpn: ' || to_char(l_stmt_num)|| ':' ||
3738 substrb(sqlerrm,1,100) ;
3739
3740
3741 IF PG_DEBUG <> 0 THEN
3742 oe_debug_pub.add('create_subassembly_jobs: ' || 'CTOSUBSB.create_sub_assembly_jobs expected excpn: ' || x_error_message,1);
3743 END IF;
3744
3745
3746
3747 when FND_API.G_EXC_UNEXPECTED_ERROR then
3748 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3749 x_error_message := 'CTOSUBSB.create_sub_assembly_jobs UN expected excpn: ' || ':' ||
3750 substrb(sqlerrm,1,100);
3751
3752
3753 IF PG_DEBUG <> 0 THEN
3754 oe_debug_pub.add('create_subassembly_jobs: ' || 'CTOSUBSB.create_sub_assembly_jobs UN expected excpn: ' || x_error_message,1);
3755 END IF;
3756
3757
3758
3759
3760 when OTHERS then
3761 x_return_status := FND_API.G_RET_STS_ERROR;
3762 x_error_message := 'CTOSUBSB.create_sub_assembly_jobs OTHERS excpn: ' || to_char(l_stmt_num) || ':' ||
3763 substrb(sqlerrm,1,100);
3764
3765
3766 IF PG_DEBUG <> 0 THEN
3767 oe_debug_pub.add('create_subassembly_jobs: ' || 'CTOSUBSB.create_sub_assembly_jobs OTHERS excpn: ' || x_error_message,1);
3768 END IF;
3769
3770 END create_subassembly_jobs;
3771
3772 --4645636
3773 Procedure process_phantoms
3774 (
3775 pitems_table in out nocopy t_item_details,
3776 p_organization_id in number,
3777 x_return_status out NOCOPY varchar2,
3778 x_error_message out NOCOPY VARCHAR2, -- bytes to hold msg */
3779 x_message_name out NOCOPY VARCHAR2 --30 bytes to hold name */
3780 )
3781 is
3782
3783 l_index number;
3784 m_index number;
3785 l_phantom_idx number;
3786 l_inherit_phantom_op_seq number;
3787 l_actual_parent_idx number;
3788 l_parent_index number;
3789 x_min_op_seq_num number;
3790 l_cons_item_qty number;
3791 l_last_index number;
3792
3793 l_stmt_num number;
3794
3795 BEGIN
3796 oe_debug_pub.add('Inside process_phantoms',1);
3797 --replace phatom's parent idx with its first non-phatom parent idx in chain
3798 l_stmt_num :=10;
3799 l_index := 2;
3800 Loop
3801 IF pitems_table(l_index).wip_supply_type = 6 THEN
3802 l_phantom_idx := l_index;
3803 m_index := 3;
3804 --Loop
3805 For m_index in 3..pitems_table.last
3806 Loop
3807 IF pitems_table(m_index).parent_index = l_phantom_idx THEN
3808 pitems_table(m_index).actual_parent_idx := pitems_table(m_index).parent_index;
3809 --removing phatom from chain
3810 pitems_table(m_index).parent_index := pitems_table(l_phantom_idx).parent_index;
3811
3812 l_stmt_num := 20;
3813 update BOM_CTO_MLSUPPLY_MAIN_TEMP
3814 set actual_parent_index = pitems_table(m_index).actual_parent_idx,
3815 parent_index = pitems_table(m_index).parent_index
3816 where ITEM_INDEX = m_index;
3817
3818
3819 END IF;
3820
3821 --EXIT WHEN m_index = pitems_table.LAST;
3822 --m_index := m_index+1;
3823
3824 END LOOP;
3825 END IF;
3826
3827 EXIT WHEN l_index = pitems_table.LAST;
3828 l_index := l_index +1;
3829 END LOOP;
3830
3831 l_last_index := pitems_table.count;
3832
3833 IF PG_DEBUG = 5 THEN
3834 FOR i IN 1..l_last_index LOOP
3835 oe_debug_pub.add('index=>'||i||
3836 'item_id=>'||pitems_table(i).item_id||
3837 'parent_idx=>'||pitems_table(i).parent_index||
3838 'actual_parent_idx=>'||pitems_table(i).actual_parent_idx||
3839 'wip_supply_type=>'||pitems_table(i).wip_supply_type,5);
3840 END LOOP;
3841 END IF;
3842
3843
3844
3845
3846 --get inherit_op_sequence
3847 l_stmt_num := 30;
3848 select INHERIT_PHANTOM_OP_SEQ
3849 into l_inherit_phantom_op_seq
3850 from bom_parameters
3851 where organization_id = p_organization_id;
3852
3853 l_stmt_num :=40;
3854 IF l_inherit_phantom_op_seq = 1 THEN
3855 l_index := 2;
3856 Loop
3857 IF pitems_table(l_index).parent_index <> pitems_table(l_index).actual_parent_idx THEN --implies child of phantom
3858
3859 l_actual_parent_idx:= pitems_table(l_index).actual_parent_idx;
3860
3861 pitems_table(l_index).operation_seq_num := pitems_table(l_actual_parent_idx).operation_seq_num;
3862
3863 pitems_table(l_index).OPERATION_LEAD_TIME_PERCENT := pitems_table(l_actual_parent_idx).OPERATION_LEAD_TIME_PERCENT;
3864
3865 l_stmt_num := 50;
3866 update BOM_CTO_MLSUPPLY_MAIN_TEMP
3867 set operation_seq_num = pitems_table(l_index).operation_seq_num,
3868 OPERATION_LEAD_TIME_PERCENT = pitems_table(l_index).OPERATION_LEAD_TIME_PERCENT
3869 where ITEM_INDEX = l_index;
3870
3871 END IF;
3872
3873 EXIT WHEN l_index = pitems_table.LAST;
3874 l_index := l_index +1;
3875 END LOOP;
3876
3877 ELSE
3878 null;
3879 --(A)--leave op seq as it is
3880
3881 --(B)--need to get lead time offset % from parent
3882 l_index :=3;
3883 --Loop
3884 l_stmt_num := 60;
3885 For l_index in 3..pitems_table.last
3886 loop
3887 --EXIT WHEN l_index = pitems_table.LAST;
3888
3889
3890 IF pitems_table(l_index).parent_index <> pitems_table(l_index).actual_parent_idx THEN -- implies child of pH
3891 l_parent_index := pitems_table(l_index).parent_index;
3892
3893 IF PG_DEBUG <> 0 THEN
3894 oe_debug_pub.add('ENTERED IF BLOCK',5);
3895 oe_debug_pub.add('parent_item_id=>'||pitems_table(l_parent_index).item_id ,5);
3896 oe_debug_pub.add('item_id=>'||pitems_table(l_index).item_id ,5);
3897 oe_debug_pub.add('operation_seq_num=>'||pitems_table(l_index).operation_seq_num ,5);
3898 END IF;
3899
3900 BEGIN
3901 l_stmt_num := 70;
3902 select nvl(bos_p.OPERATION_LEAD_TIME_PERCENT,0)
3903 INTO pitems_table(l_index).operation_lead_time_percent
3904 from bom_operational_routings bor_p,--parent
3905 bom_operation_sequences bos_p
3906 where bor_p.assembly_item_id = pitems_table(l_parent_index).item_id
3907 and bor_p.organization_id = p_organization_id
3908 and bor_p.ALTERNATE_ROUTING_DESIGNATOR is null
3909 and bos_p.routing_sequence_id = bor_p.common_routing_sequence_id
3910 and bos_p.operation_seq_num = pitems_table(l_index).operation_seq_num
3911 and nvl( bos_p.operation_type,1)=1; ---consider event only for flm routing 5676839
3912 Exception
3913 WHEN no_data_found then
3914 pitems_table(l_index).operation_lead_time_percent := 0;
3915 oe_debug_pub.add('lead_time=>'||pitems_table(l_index).operation_lead_time_percent,1);
3916
3917 END;
3918
3919 oe_debug_pub.add('lead_time=>'||pitems_table(l_index).operation_lead_time_percent,1);
3920 l_stmt_num := 80;
3921 update BOM_CTO_MLSUPPLY_MAIN_TEMP
3922 set --operation_seq_num = pitems_table(l_index).operation_seq_num,
3923 OPERATION_LEAD_TIME_PERCENT = pitems_table(l_index).OPERATION_LEAD_TIME_PERCENT
3924 where ITEM_INDEX = l_index;
3925
3926 END IF;
3927 -- EXIT WHEN l_index = pitems_table.LAST;
3928 -- l_index := l_index +1 ;
3929 END LOOP;
3930
3931
3932 END IF;
3933
3934
3935 IF PG_DEBUG = 5 THEN
3936 FOR i IN 1..l_last_index LOOP
3937 oe_debug_pub.add('index=>'||i||
3938 'item_id=>'||pitems_table(i).item_id||
3939 'parent_idx=>'||pitems_table(i).parent_index||
3940 'actual_parent_idx=>'||pitems_table(i).actual_parent_idx||
3941 'wip_supply_type=>'||pitems_table(i).wip_supply_type||
3942 'LT_offset=>'||pitems_table(i).operation_lead_time_percent||
3943 'op_seq=>'||pitems_table(i).operation_seq_num,5);
3944 END LOOP;
3945 END IF;
3946
3947
3948
3949 --For MIN_OP_Seq_num calculations
3950 l_index :=2;
3951 LOOP
3952 l_parent_index := pitems_table(l_index).parent_index;
3953
3954 IF (nvl(pitems_table(l_index).wip_supply_type,1) <> 6 AND
3955 pitems_table(l_index).source_type <> 66 AND
3956 pitems_table(l_parent_index).cfm_routing_flag = 1 AND
3957 pitems_table(l_parent_index).needed_item_qty <> 0 AND
3958 (pitems_table(l_index).source_type = 3
3959 OR
3960 pitems_table(l_index).source_type = 1
3961 OR
3962 pitems_table(l_index).cfm_routing_flag <> 1
3963 )
3964 )
3965 THEN
3966 IF PG_DEBUG <> 0 THEN
3967 oe_debug_pub.add('ENTERED MIN OP SEQ BLOCK',5);
3968 END IF;
3969
3970 l_stmt_num := 90;
3971 select min(OPERATION_SEQ_NUM),sum(needed_item_qty)
3972 into x_min_op_seq_num,l_cons_item_qty
3973 FROM BOM_CTO_MLSUPPLY_MAIN_TEMP
3974 WHERE parent_index = pitems_table(l_index).parent_index
3975 AND item_id = pitems_table(l_index).item_id;
3976
3977 IF PG_DEBUG <> 0 THEN
3978 oe_debug_pub.add('item_id=>'||pitems_table(l_index).item_id,5);
3979 oe_debug_pub.add('x_min_op_seq_num=>'||x_min_op_seq_num,5);
3980 oe_debug_pub.add('l_cons_item_qty=>'||l_cons_item_qty,5);
3981 oe_debug_pub.add('parent_index=>'||pitems_table(l_index).parent_index,5);
3982 END IF;
3983
3984 l_stmt_num := 100;
3985 Update bom_cto_mlsupply_main_temp
3986 set needed_item_qty = 0
3987 where parent_index = pitems_table(l_index).parent_index
3988 AND item_id = pitems_table(l_index).item_id
3989 and Operation_seq_num <> x_min_op_seq_num;
3990
3991
3992 l_stmt_num := 110;
3993 Update bom_cto_mlsupply_main_temp
3994 set needed_item_qty = l_cons_item_qty
3995 where parent_index = pitems_table(l_index).parent_index
3996 AND item_id = pitems_table(l_index).item_id
3997 and Operation_seq_num = x_min_op_seq_num;
3998
3999
4000
4001
4002 END IF;
4003 EXIT WHEN l_index = pitems_table.LAST;
4004 l_index := l_index+1;
4005 END LOOP;
4006
4007 IF PG_DEBUG = 5 THEN
4008 FOR i IN 1..l_last_index LOOP
4009 oe_debug_pub.add('index=>'||i||
4010 'item_id=>'||pitems_table(i).item_id||
4011 'parent_idx=>'||pitems_table(i).parent_index||
4012 'actual_parent_idx=>'||pitems_table(i).actual_parent_idx||
4013 'wip_supply_type=>'||pitems_table(i).wip_supply_type||
4014 'LT_offset=>'||pitems_table(i).operation_lead_time_percent||
4015 'op_seq=>'||pitems_table(i).operation_seq_num,5);
4016 END LOOP;
4017 END IF;
4018
4019
4020 EXCEPTION
4021 when FND_API.G_EXC_ERROR then
4022
4023 x_return_status := FND_API.G_RET_STS_ERROR;
4024 x_error_message := 'CTOSUBSB.process_phantoms expected excpn: ' || to_char(l_stmt_num)|| ':' ||
4025 substrb(sqlerrm,1,100);
4026
4027
4028 IF PG_DEBUG <> 0 THEN
4029 IF PG_DEBUG <> 0 THEN
4030 oe_debug_pub.add('process_phantoms: ' || 'CTOSUBSB.create_flow_subassembly expected excpn: ' || x_error_message,1);
4031 END IF;
4032 END IF;
4033
4034
4035 when FND_API.G_EXC_UNEXPECTED_ERROR then
4036 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4037 x_error_message := 'CTOSUBSB.process_phantoms UN expected excpn: ' || to_char(l_stmt_num)|| ':' ||
4038 substrb(sqlerrm,1,100);
4039
4040
4041 IF PG_DEBUG <> 0 THEN
4042 IF PG_DEBUG <> 0 THEN
4043 oe_debug_pub.add('process_phantoms: ' || 'CTOSUBSB.create_flow_subassembly UN expected excpn: ' || x_error_message,1);
4044 END IF;
4045 END IF;
4046
4047
4048
4049 when OTHERS then
4050 x_return_status := FND_API.G_RET_STS_ERROR;
4051 x_error_message := 'CTOSUBSB.process_phantoms OTHERS excpn: ' || to_char(l_stmt_num)|| ':' ||
4052 substrb(sqlerrm,1,100);
4053
4054 IF PG_DEBUG <> 0 THEN
4055 IF PG_DEBUG <> 0 THEN
4056 oe_debug_pub.add('process_phantoms: ' || 'CTOSUBSB.create_flow_subassembly OTHERS excpn: ' || x_error_message,1);
4057 END IF;
4058 END IF;
4059
4060
4061
4062
4063 END process_phantoms;
4064
4065
4066
4067 end CTO_SUBASSEMBLY_SUP_PK;