[Home] [Help]
PACKAGE BODY: APPS.CTO_SUBASSEMBLY_SUP_PK
Source
1 package body CTO_SUBASSEMBLY_SUP_PK as
2 /* $Header: CTOSUBSB.pls 120.20.12020000.3 2013/01/22 15:11:24 ntungare 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 -- Bug 9402188.Added nvl condition to pick the supply type from msi if not present in bom.pdube
891 -- bug 13722156. Added a conditon to ensure that unimplemented components are not picked up
892 CURSOR c_config_items IS
893 select component_item_id,
894 msi.concatenated_segments,
895 component_quantity/bic.component_yield_factor, -- bugfix 4134956: take component_yield_factor into account
896 bic.operation_seq_num,
897 nvl(bor.cfm_routing_flag,-99), --default to -99 if no routing and treat it as discrete
898 bor.routing_sequence_id,
899 nvl(msi.fixed_lead_time,0),
900 nvl(msi.variable_lead_time,0),
901 nvl(msi.full_lead_time,0),
902 nvl(msi.postprocessing_lead_time,0),
903 bic.bom_item_type,
904 msi.auto_created_config_flag,
905 bor.line_id,
906 wil.line_code,
907 end_assembly_pegging_flag, --Bugfix# 3418102
908 nvl(bic.basis_type,1), /* LBM Project */
909 -- bic.wip_supply_type --4645636
910 nvl(bic.wip_supply_type, msi.wip_supply_type) -- Bug 9402188
911 from bom_inventory_components bic,
912 bom_bill_of_materials bom,
913 mtl_System_items_kfv msi,
914 --mtl_system_items msi,
915 bom_operational_routings bor,
916 wip_lines wil
917 --bugfix 4615409
918 --bom_operational_routings bor_p,--parent
919 --bom_operation_sequences bos_p
920 where bic.bill_sequence_id = bom.common_bill_sequence_id
921 and bom.assembly_item_id = pParentItemId
922 and bom.organization_id = pOrganization_id
923 and bic.component_item_id = msi.inventory_item_id
924 and bic.effectivity_date <= sysdate --bugfix
925 and nvl(bic.disable_date,sysdate+1) > sysdate --2775097
926 and msi.organization_id = pOrganization_id
927 and bor.assembly_item_id (+)= bic.component_item_id
928 and bor.ALTERNATE_ROUTING_DESIGNATOR(+) is null
929 and bor.organization_id (+) = pOrganization_id
930 and bor.line_id = wil.line_id(+)
931 and msi.auto_created_config_flag = 'Y'
932 and bom.ALTERNATE_BOM_DESIGNATOR is null --bug 4998922
933 and decode (bic.change_notice, null, 'Y', (decode((bic.implementation_date), null, 'N', 'Y'))) = 'Y'; -- bug 13722156
934
935
936
937 -- Bug 9402188.Added nvl condition to pick the supply type from msi if not present in bom.pdube
938 -- bug 13722156. Added a conditon to ensure that unimplemented components are not picked up
939 CURSOR c_config_and_ato_items IS
940 select component_item_id,
941 msi.concatenated_segments,
942 component_quantity/bic.component_yield_factor, -- bugfix 4134956: take component_yield_factor into account
943 bic.operation_seq_num,
944 nvl(bor.cfm_routing_flag,-99), --default to -99 if no routing and treat it as discrete
945 bor.routing_sequence_id,
946 nvl(msi.fixed_lead_time,0),
947 nvl(msi.variable_lead_time,0),
948 nvl(msi.full_lead_time,0),
949 nvl(msi.postprocessing_lead_time,0),
950 bic.bom_item_type,
951 msi.auto_created_config_flag,
952 bor.line_id,
953 wil.line_code,
954 end_assembly_pegging_flag, --Bugfix# 3418102
955 nvl(bic.basis_type,1),/* LBM Project */
956 -- bic.wip_supply_type --4645636
957 nvl(bic.wip_supply_type, msi.wip_supply_type) --Bugfix 9402188
958 from bom_inventory_components bic,
959 bom_bill_of_materials bom,
960 mtl_System_items_kfv msi,
961 --mtl_System_items_b msi,
962 bom_operational_routings bor,
963 wip_lines wil
964 --bugfix 4615409
965 --bom_operational_routings bor_p,--parent
966 --bom_operation_sequences bos_p
967 where bic.bill_sequence_id = bom.common_bill_sequence_id
968 and bom.assembly_item_id = pParentItemId
969 and bom.organization_id = pOrganization_id
970 and bic.component_item_id = msi.inventory_item_id
971 and bic.effectivity_date <= sysdate --bugfix
972 and nvl(bic.disable_date,sysdate+1) > sysdate --2775097
973 and msi.organization_id = pOrganization_id
974 and bor.assembly_item_id (+) = bic.component_item_id
975 and bor.ALTERNATE_ROUTING_DESIGNATOR(+) is null
976 and bor.organization_id (+) = pOrganization_id
977 and bor.line_id = wil.line_id(+)
978 and msi.replenish_to_order_flag = 'Y'
979 and bic.bom_item_type = 4
980 and bom.ALTERNATE_BOM_DESIGNATOR is null --bug 4998922
981 and decode (bic.change_notice, null, 'Y', (decode((bic.implementation_date), null, 'N', 'Y'))) = 'Y'; -- bug 13722156
982
983
984 l_index number;
985
986 l_ret_status varchar2(1) := null;
987 l_error_message varchar2(1000) := null;
988 l_msg_name varchar2(30) := null;
989
990 p_cons_item_details t_cons_item_details;
991
992 l_min_op_seq_num number;
993 l_comp_item_qty number;
994 l_comp_lot_qty number;
995 l_oper_lead_time_per number;
996 l_recurred_item_flag number;
997
998 BEGIN
999
1000 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1001
1002 IF pLower_Supplytype = 2 THEN
1003
1004 IF PG_DEBUG <> 0 THEN
1005 oe_debug_pub.add ('get_child_configurations: ' || 'Config children alone' ,1);
1006 --Bugfix 8913125: Added these messages.
1007 oe_debug_pub.add ('get_child_configurations: Parent Item:' || pParentItemId, 1);
1008 oe_debug_pub.add ('get_child_configurations: Org:' || pOrganization_id ,1);
1009 cto_wip_workflow_api_pk.cto_debug('get_child_configurations: ','Config children alone' );
1010 END IF;
1011
1012
1013 OPEN c_config_items;
1014
1015 l_stmt_num := 40;
1016 LOOP
1017 l_index := pitems_table.last+1;
1018 FETCH c_config_items INTO pitems_table(l_index).item_id,
1019 pitems_table(l_index).item_name,
1020 pitems_table(l_index).item_quantity,
1021 -- pitems_table(l_index).operation_lead_time_percent,
1022 pitems_table(l_index).operation_seq_num,
1023 pitems_table(l_index).cfm_routing_flag,
1024 pitems_table(l_index).routing_sequence_id,
1025 pitems_table(l_index).fixed_lead_time,
1026 pitems_table(l_index).variable_lead_time,
1027 pitems_table(l_index).processing_lead_time,
1028 pitems_table(l_index). postprocessing_lead_time,
1029 pitems_table(l_index).bom_item_type,
1030 pitems_table(l_index).auto_config_flag,
1031 pitems_table(l_index).line_id,
1032 pitems_table(l_index).line_code,
1033 pitems_table(l_index).pegging_flag,--Bugfix# 3418102
1034 pitems_table(l_index).basis_type,/* LBM Project */
1035 pitems_table(l_index).wip_supply_type; --4645636
1036
1037 EXIT when c_config_items%notfound;
1038
1039 IF PG_DEBUG <> 0 THEN
1040 oe_debug_pub.add ('get_child_configurations: ' || 'item added' || pitems_table(l_index).item_name ,1);
1041
1042 --Bugfix# 3418102
1043 oe_debug_pub.add ('get_child_configurations: ' || 'end_peeging_flag for project information' || pitems_table(l_index).pegging_flag ,3);
1044 END IF;
1045
1046 -- 5198966
1047 BEGIN
1048 select nvl(bos_p.OPERATION_LEAD_TIME_PERCENT,0)
1049 INTO pitems_table(l_index).operation_lead_time_percent
1050 from bom_operational_routings bor_p,--parent
1051 bom_operation_sequences bos_p
1052 where bor_p.assembly_item_id = pParentItemId
1053 and bor_p.organization_id = pOrganization_id
1054 and bor_p.ALTERNATE_ROUTING_DESIGNATOR is null
1055 and bos_p.routing_sequence_id = bor_p.common_routing_sequence_id
1056 and bos_p.operation_seq_num = pitems_table(l_index).operation_seq_num
1057 and nvl(bos_p.operation_type,1)=1 --consider events only for FLM cases.5676839
1058 --Bugfix 12581339: Disabled operations should not be looked into.
1059 and bos_p.implementation_date is not null
1060 and bos_p.effectivity_date <= sysdate
1061 and nvl(bos_p.disable_date, sysdate + 1) > sysdate;
1062 Exception
1063 WHEN no_data_found then
1064 pitems_table(l_index).operation_lead_time_percent := 0;
1065 END;
1066
1067 IF PG_DEBUG <> 0 THEN
1068 oe_debug_pub.add('get_child_configurations::configs only::oltp:' || pitems_table(l_index).operation_lead_time_percent);
1069 END IF;
1070
1071 pitems_table(l_index).parent_index := pParent_index;
1072 pitems_table(l_index).feeder_run := 'N';
1073
1074
1075 l_stmt_num := 50;
1076 CTO_UTILITY_PK.QUERY_SOURCING_ORG(
1077 P_inventory_item_id => pitems_table(l_index).item_id,
1078 P_organization_id => pOrganization_id,
1079 P_sourcing_rule_exists => v_sourcing_rule_exists,
1080 P_source_type => v_source_type,
1081 P_sourcing_org => v_sourcing_org,
1082 P_transit_lead_time => v_transit_lead_time,
1083 X_exp_error_code => v_exp_error_code,
1084 X_return_status =>x_return_status
1085 );
1086
1087 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1088 IF PG_DEBUG <> 0 THEN
1089 oe_debug_pub.add ('get_child_configurations: ' || 'failed after call to CTO_UTILITY_PK.QUERY_SOURCING_ORG with status ' || x_return_status ,1);
1090 END IF;
1091 --oe_debug_pub.add ('error message' || l_error_msg ,1);
1092 RAISE FND_API.G_EXC_ERROR;
1093 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1094 IF PG_DEBUG <> 0 THEN
1095 oe_debug_pub.add ('get_child_configurations: ' || ' failed after call to CTO_UTILITY_PK.QUERY_SOURCING_ORG with status ' || x_return_status ,1);
1096 END IF;
1097 --oe_debug_pub.add ('error message' || l_error_msg ,1);
1098 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1099 ELSE
1100
1101 IF PG_DEBUG <> 0 THEN
1102 oe_debug_pub.add('get_child_configurations: ' || 'success from CTO_UTILITY_PK.QUERY_SOURCING_ORG ' ,1);
1103 END IF;
1104 END IF;
1105
1106
1107 --by Kiran Konada
1108 --removed if block for multiple sources
1109 -- rkaza. 05/02/2005. Adding sourcing org also.
1110 pitems_table(l_index).source_type := v_source_type;
1111 pitems_table(l_index).sourcing_org := v_sourcing_org;
1112
1113
1114
1115 --call recurring item only
1116 --if item is not multiple sources AND
1117 --parent is flow AND
1118 --if item is buy or discrete or 100% transfer
1119 -- rkaza. 05/02/2005. Adding 100% transfer cases.
1120
1121 IF (
1122 pitems_table(l_index).source_type <> 66 AND
1123 pitems_table(pParent_index).cfm_routing_flag = 1
1124 AND
1125 (pitems_table(l_index).source_type = 3
1126 OR
1127 pitems_table(l_index).source_type = 1
1128 OR
1129 pitems_table(l_index).cfm_routing_flag <> 1
1130 )
1131 ) THEN
1132 oe_debug_pub.add ('calling check_recurring_item' ,1);
1133 check_recurring_item
1134 ( p_cons_item_details => p_cons_item_details,
1135 p_parent_item_id => pitems_table(pParent_index).item_id,
1136 p_organization_id => pOrganization_id,
1137 p_item_id => pitems_table(l_index).item_id,
1138 x_min_op_seq_num => l_min_op_seq_num ,
1139 x_comp_item_qty => l_comp_item_qty ,
1140 x_comp_lot_qty => l_comp_lot_qty , /* LBM Project */
1141 x_oper_lead_time_per => l_oper_lead_time_per,
1142 x_recurred_item_flag => l_recurred_item_flag
1143 );
1144 ELSE
1145 --if parent is not flow default recurre_item_flag to 1
1146 --so that standard processing takes place
1147 l_recurred_item_flag := 1;
1148
1149 END IF;
1150
1151 IF (l_recurred_item_flag = 1) THEN
1152 --
1153 -- begin bugfix 4134956: item_quantity has component_yield_factor taken into account.
1154 -- Round to 6 decimal places
1155 --
1156 /* LBM Project */
1157 if( pitems_table(l_index).basis_type = 1) /* Item Basis */
1158 then
1159 pitems_table(l_index).needed_item_qty :=
1160 round (pitems_table(pParent_index).needed_item_qty * pitems_table(l_index).item_quantity, 6);
1161 else
1162 pitems_table(l_index).needed_item_qty :=
1163 round( pitems_table(l_index).item_quantity, 6);
1164 end if;
1165
1166 --immediate parent's calculate supply quantity * childs bic component qty
1167 END IF;
1168 IF (l_recurred_item_flag = 2) THEN
1169 --
1170 -- begin bugfix 4134956: l_comp_item_qty has component_yield_factor taken into account.
1171 -- Round to 6 decimal places
1172 --
1173 /* LBM change */
1174 pitems_table(l_index).needed_item_qty :=
1175 round( ( ( pitems_table(pParent_index).needed_item_qty * l_comp_item_qty ) + l_comp_lot_qty ) , 6) ;
1176
1177 pitems_table(l_index).operation_seq_num := l_min_op_seq_num ;
1178 pitems_table(l_index).operation_lead_time_percent := l_oper_lead_time_per;
1179
1180
1181 END IF;
1182
1183 IF (l_recurred_item_flag = 3) THEN
1184 pitems_table(l_index).needed_item_qty := 0;
1185 pitems_table(l_index).comment := pitems_table(l_index).comment ||'This items supply has been consolidated';
1186 END IF;
1187
1188
1189
1190 IF ( pitems_table(l_index).source_type = 2
1191 and
1192 pitems_table(l_index).needed_item_qty > 0
1193 ) THEN
1194
1195 l_stmt_num := 60;
1196 get_child_configurations
1197 ( pParentItemId => pitems_table(l_index).item_id,
1198 pOrganization_id => pOrganization_id,
1199 pLower_Supplytype => pLower_Supplytype,
1200 pParent_index => l_index,--passing index# as parentid for children
1201 pitems_table => pitems_table,
1202 x_return_status => l_ret_status,
1203 x_error_message => l_error_message,
1204 x_message_name => l_msg_name
1205
1206 );
1207
1208 END IF; --source type and needed_item_qty
1209
1210
1211 END LOOP;
1212 Close c_config_items;
1213
1214 ELSIF pLower_Supplytype = 3 THEN
1215
1216
1217 If PG_DEBUG <> 0 Then
1218 cto_wip_workflow_api_pk.cto_debug('get_child_configurations: ','Config and ato item children' );
1219 --Bugfix 8913125: Added these messages.
1220 oe_debug_pub.add ('get_child_configurations: Config and ato item children', 1);
1221 oe_debug_pub.add ('get_child_configurations: Parent Item:' || pParentItemId, 1);
1222 oe_debug_pub.add ('get_child_configurations: Org:' || pOrganization_id ,1);
1223 End if;
1224
1225 OPEN c_config_and_ato_items;
1226
1227 l_stmt_num := 70;
1228 LOOP
1229 l_index := pitems_table.last+1;
1230
1231 FETCH c_config_and_ato_items INTO pitems_table(l_index).item_id,
1232 pitems_table(l_index).item_name,
1233 pitems_table(l_index).item_quantity,
1234 -- pitems_table(l_index).operation_lead_time_percent,
1235 pitems_table(l_index).operation_seq_num,
1236 pitems_table(l_index).cfm_routing_flag,
1237 pitems_table(l_index).routing_sequence_id,
1238 pitems_table(l_index).fixed_lead_time,
1239 pitems_table(l_index).variable_lead_time,
1240 pitems_table(l_index).processing_lead_time,
1241 pitems_table(l_index). postprocessing_lead_time,
1242 pitems_table(l_index).bom_item_type,
1243 pitems_table(l_index).auto_config_flag,
1244 pitems_table(l_index).line_id,
1245 pitems_table(l_index).line_code,
1246 pitems_table(l_index).pegging_flag,
1247 pitems_table(l_index).basis_type, /* LBM Project */
1248 pitems_table(l_index).wip_supply_type; --4645636
1249
1250
1251
1252 EXIT when c_config_and_ato_items%notfound;
1253 IF PG_DEBUG <> 0 THEN
1254 oe_debug_pub.add ('get_child_configurations: ' || 'item added' || pitems_table(l_index).item_name ,1);
1255
1256 --Bugfix# 3418102
1257 oe_debug_pub.add ('get_child_configurations: ' || 'end_pegging_flag for project information' || pitems_table(l_index).pegging_flag ,3);
1258 END IF;
1259 l_stmt_num := 71;
1260
1261 --Bugfix 8913125: Added these messages.
1262 If PG_DEBUG <> 0 Then
1263 oe_debug_pub.add ('get_child_configurations:stmt:l_stmt_num' || l_stmt_num);
1264 oe_debug_pub.add ('get_child_configurations:stmt:l_index:' || l_index);
1265 oe_debug_pub.add ('get_child_configurations:stmt:item_id:' || pitems_table(l_index).item_id);
1266 oe_debug_pub.add ('get_child_configurations:stmt:operation_seq_num:' || pitems_table(l_index).operation_seq_num);
1267 oe_debug_pub.add ('get_child_configurations:stmt:routing_sequence_id:' || pitems_table(l_index).routing_sequence_id);
1268 End If;
1269
1270 -- 5198966
1271 BEGIN
1272 select nvl(bos_p.OPERATION_LEAD_TIME_PERCENT,0)
1273 INTO pitems_table(l_index).operation_lead_time_percent
1274 from bom_operational_routings bor_p,--parent
1275 bom_operation_sequences bos_p
1276 where bor_p.assembly_item_id = pParentItemId
1277 and bor_p.organization_id = pOrganization_id
1278 and bor_p.ALTERNATE_ROUTING_DESIGNATOR is null
1279 and bos_p.routing_sequence_id = bor_p.common_routing_sequence_id
1280 and bos_p.operation_seq_num = pitems_table(l_index).operation_seq_num
1281 and nvl(bos_p.operation_type,1)=1 --consider events only for FLM cases.5676839
1282 --Begin Bugfix 8913125
1283 and implementation_date IS NOT NULL
1284 and effectivity_date <= SYSDATE
1285 and nvl(disable_date, SYSDATE + 1) > SYSDATE;
1286
1287 If PG_DEBUG <> 0 Then
1288 oe_debug_pub.add ('get_child_configurations: oltp:' ||pitems_table(l_index).operation_lead_time_percent,1);
1289 End If;
1290 --End Bugfix 8913125
1291
1292 Exception
1293 WHEN no_data_found THEN
1294 pitems_table(l_index).operation_lead_time_percent := 0;
1295 END;
1296
1297 l_stmt_num := 72;
1298
1299 pitems_table(l_index).parent_index := pParent_index;
1300 pitems_table(l_index).feeder_run := 'N';
1301
1302 l_stmt_num := 80;
1303
1304 CTO_UTILITY_PK.QUERY_SOURCING_ORG(
1305 P_inventory_item_id => pitems_table(l_index).item_id,
1306 P_organization_id => pOrganization_id,
1307 P_sourcing_rule_exists => v_sourcing_rule_exists,
1308 P_source_type => v_source_type,
1309 P_sourcing_org => v_sourcing_org,
1310 P_transit_lead_time => v_transit_lead_time,
1311 X_exp_error_code => v_exp_error_code,
1312 X_return_status =>x_return_status
1313 );
1314
1315 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1316 IF PG_DEBUG <> 0 THEN
1317 oe_debug_pub.add ('get_child_configurations: ' || 'failed after call to CTO_UTILITY_PK.QUERY_SOURCING_ORG with status ' || x_return_status ,1);
1318 END IF;
1319 --oe_debug_pub.add ('error message' || l_error_msg ,1);
1320 RAISE FND_API.G_EXC_ERROR;
1321 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1322 IF PG_DEBUG <> 0 THEN
1323 oe_debug_pub.add ('get_child_configurations: ' || ' failed after call to CTO_UTILITY_PK.QUERY_SOURCING_ORG with status ' || x_return_status ,1);
1324 END IF;
1325 --oe_debug_pub.add ('error message' || l_error_msg ,1);
1326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1327 ELSE
1328
1329 IF PG_DEBUG <> 0 THEN
1330 oe_debug_pub.add('get_child_configurations: ' || 'success from CTO_UTILITY_PK.QUERY_SOURCING_ORG ' ,1);
1331 END IF;
1332 END IF;
1333
1334
1335 --by Kiran Konada
1336 --removed if block for multiple sources
1337 -- rkaza. 05/02/2005. Adding sourcing org also.
1338
1339 pitems_table(l_index).source_type := v_source_type;
1340 pitems_table(l_index).sourcing_org := v_sourcing_org;
1341
1342
1343 --call recurring item only
1344 --if item is not multiple sources AND
1345 --parent is flow AND
1346 --if item is buy or discrete or 100% transfer
1347 -- rkaza. 05/02/2005. Added 100% transfer.
1348
1349 IF (
1350 pitems_table(l_index).source_type <> 66 AND
1351 pitems_table(pParent_index).cfm_routing_flag = 1
1352 AND
1353 (pitems_table(l_index).source_type = 3
1354 OR
1355 pitems_table(l_index).source_type = 1
1356 OR
1357 pitems_table(l_index).cfm_routing_flag <> 1
1358 )
1359 ) THEN
1360 oe_debug_pub.add ('calling check_recurring_item for item ' ,1);
1361 check_recurring_item
1362 ( p_cons_item_details => p_cons_item_details,
1363 p_parent_item_id => pitems_table(pParent_index).item_id,
1364 p_organization_id => pOrganization_id,
1365 p_item_id => pitems_table(l_index).item_id,
1366 x_min_op_seq_num => l_min_op_seq_num ,
1367 x_comp_item_qty => l_comp_item_qty ,
1368 x_comp_lot_qty => l_comp_lot_qty , /* LBM Project */
1369 x_oper_lead_time_per => l_oper_lead_time_per,
1370 x_recurred_item_flag => l_recurred_item_flag
1371 );
1372 ELSE
1373 --if parent is not flow default recurre_item_flag to 1
1374 --so that standard processing takes place
1375 l_recurred_item_flag := 1;
1376
1377 END IF;
1378
1379 IF (l_recurred_item_flag = 1) THEN
1380 --
1381 -- begin bugfix 4134956: item_quantity has component_yield_factor taken into account.
1382 -- Round to 6 decimal places
1383 --
1384
1385 /* LBM Project */
1386 if( pitems_table(l_index).basis_type = 1) /* Item Basis */
1387 then
1388 pitems_table(l_index).needed_item_qty :=
1389 round (pitems_table(pParent_index).needed_item_qty * pitems_table(l_index).item_quantity, 6);
1390 else
1391 pitems_table(l_index).needed_item_qty :=
1392 round( pitems_table(l_index).item_quantity, 6);
1393 end if;
1394
1395
1396
1397
1398 --immediate parent's calculate supply quantity * childs bic component qty
1399
1400 END IF;
1401 IF (l_recurred_item_flag = 2) THEN
1402 --
1403 -- begin bugfix 4134956: l_comp_item_qty has component_yield_factor taken into account.
1404 -- Round to 6 decimal places
1405 --
1406 /* LBM Project */
1407 pitems_table(l_index).needed_item_qty :=
1408 round( ((pitems_table(pParent_index).needed_item_qty * l_comp_item_qty) + l_comp_lot_qty ) , 6) ;
1409 pitems_table(l_index).operation_seq_num := l_min_op_seq_num ;
1410 pitems_table(l_index).operation_lead_time_percent := l_oper_lead_time_per;
1411
1412 END IF;
1413
1414 IF (l_recurred_item_flag = 3) THEN
1415 pitems_table(l_index).needed_item_qty := 0;
1416 pitems_table(l_index).comment := pitems_table(l_index).comment ||'This items supply has been consolidated';
1417 END IF;
1418
1419
1420
1421 IF ( pitems_table(l_index).source_type = 2
1422 and
1423 pitems_table(l_index).needed_item_qty > 0
1424 ) THEN
1425
1426
1427 get_child_configurations
1428 ( pParentItemId =>pitems_table(pitems_table.last).item_id,
1429 pOrganization_id => pOrganization_id,
1430 pLower_Supplytype => pLower_Supplytype,
1431 pParent_index => l_index,--passing index# as parentid for children
1432 pitems_table => pitems_table ,
1433 x_return_status => l_ret_status,
1434 x_error_message => l_error_message,
1435 x_message_name => l_msg_name
1436
1437 );
1438
1439 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
1440 IF PG_DEBUG <> 0 THEN
1441 oe_debug_pub.add ('get_child_configurations: ' || 'failed after call to get_child_configurations with status ' || l_ret_status ,1);
1442
1443 oe_debug_pub.add ('get_child_configurations: ' || 'error message' || l_error_message ,1);
1444 END IF;
1445 RAISE FND_API.G_EXC_ERROR;
1446 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1447 IF PG_DEBUG <> 0 THEN
1448 oe_debug_pub.add ('get_child_configurations: ' || ' failed after call to get_child_configurations ' || l_ret_status ,1);
1449
1450 oe_debug_pub.add ('get_child_configurations: ' || 'error message' || l_error_message ,1);
1451 END IF;
1452 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1453 ELSE
1454
1455 IF PG_DEBUG <> 0 THEN
1456 oe_debug_pub.add('get_child_configurations: ' || 'success from get_child_configurations ' ,1);
1457 END IF;
1458 END IF;
1459
1460
1461 END IF; --source type and needed_item_qty
1462
1463
1464
1465
1466
1467
1468
1469 END LOOP;
1470 Close c_config_and_ato_items;
1471
1472 END IF;
1473
1474 EXCEPTION
1475
1476 when FND_API.G_EXC_ERROR then
1477
1478 x_return_status := FND_API.G_RET_STS_ERROR;
1479 --Bugfix 8913125
1480 x_error_message := to_char(l_stmt_num)|| ': ' || substrb(sqlerrm,1,50);
1481
1482 IF PG_DEBUG <> 0 THEN
1483 oe_debug_pub.add('get_child_configurations: ' || 'CTOSUBSB.get_child_configurations expected excpn: ' || x_error_message,1);
1484 END IF;
1485
1486
1487 when FND_API.G_EXC_UNEXPECTED_ERROR then
1488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1489 --Bugfix 8913125
1490 x_error_message := to_char(l_stmt_num)|| ': ' || substrb(sqlerrm,1,50);
1491
1492 IF PG_DEBUG <> 0 THEN
1493 oe_debug_pub.add('get_child_configurations: ' || 'CTOSUBSB.get_child_configurations UN expected excpn: ' || x_error_message,1);
1494 END IF;
1495
1496
1497 when OTHERS then
1498 x_return_status := FND_API.G_RET_STS_ERROR;
1499 --Bugfix 8913125
1500 x_error_message := to_char(l_stmt_num)|| ': ' || substrb(sqlerrm,1,50);
1501
1502 IF PG_DEBUG <> 0 THEN
1503 oe_debug_pub.add('get_child_configurations: ' || 'CTOSUBSB.get_child_configurations OTHERS excpn: ' || x_error_message,1);
1504 END IF;
1505
1506
1507 /* OE_MSG_PUB.Add_Exc_Msg
1508 ( G_PKG_NAME
1509 , 'get_working_day'
1510 ); */
1511
1512 END get_child_configurations;
1513
1514
1515 /*
1516
1517 API to create flow scheudle for sub_assemblies
1518
1519 */
1520 Procedure create_flow_subassembly
1521 (
1522 pflow_sch_details in out nocopy t_flow_sch_details,
1523 pIndex in number,
1524 pitems_table in t_item_details,
1525 pShip_org in number,
1526 pProject_id in number,
1527 pTask_id in number,
1528 x_return_status out NOCOPY varchar2,
1529 x_error_message out NOCOPY VARCHAR2, /* 70 bytes to hold msg */
1530 x_message_name out NOCOPY VARCHAR2 /* 30 bytes to hold name */
1531
1532 )
1533
1534 IS
1535
1536 l_flow_schedule_rec mrp_flow_schedule_pub.flow_schedule_rec_type;
1537 l_x_flow_schedule_rec mrp_flow_schedule_pub.flow_schedule_rec_type;
1538 l_x_flow_schedule_val_rec mrp_flow_schedule_pub.flow_schedule_val_rec_type;
1539 l_return_status varchar2(1);
1540 l_msg_count number;
1541 l_msg_data varchar2(240);
1542
1543 l_flow_index number := null;
1544
1545 l_stmt_num number := 0;
1546 l_x_value varchar2(1);
1547
1548 BEGIN
1549
1550 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1551
1552 l_flow_schedule_rec.operation := MRP_GLOBALS.G_OPR_CREATE;
1553 l_flow_schedule_rec.scheduled_flag := 1;
1554
1555 l_flow_schedule_rec.primary_item_id := pitems_table(pIndex).item_id;
1556 l_flow_schedule_rec.line_id := pitems_table(pIndex).line_id ;
1557 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
1558 l_flow_schedule_rec.organization_id := pShip_org;
1559 l_flow_schedule_rec.scheduled_completion_date := pitems_table(pIndex).job_completion_date;
1560
1561 --bugfix 3418102
1562 IF pitems_table(pIndex).pegging_flag IN ('I','X') THEN --project and task id can be passed to child item
1563 l_flow_schedule_rec.project_id := pProject_id;
1564 l_flow_schedule_rec.task_id := pTask_id;
1565 END IF;
1566
1567 l_stmt_num := 90;
1568 MRP_Flow_Schedule_PUB.Process_Flow_Schedule(
1569 p_api_version_number => 1.0,
1570 p_init_msg_list => FND_API.G_TRUE,
1571 x_return_status => l_return_status,
1572 x_msg_count => l_msg_count,
1573 x_msg_data => l_msg_data,
1574 p_flow_schedule_rec => l_flow_schedule_rec,
1575 x_flow_schedule_rec => l_x_flow_schedule_rec,
1576 x_flow_schedule_val_rec => l_x_flow_schedule_val_rec
1577 );
1578
1579
1580
1581
1582 if (l_return_status = FND_API.G_RET_STS_ERROR) then --flow return status
1583 IF PG_DEBUG <> 0 THEN
1584 oe_debug_pub.add('create_flow_subassembly: ' || 'Expected error in Process Flow Schedule with status: ' || l_return_status, 1);
1585 END IF;
1586 raise FND_API.G_EXC_ERROR;
1587
1588 elsif (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then --flow returns tatus
1589 IF PG_DEBUG <> 0 THEN
1590 oe_debug_pub.add('create_flow_subassembly: ' || 'UnExpected error in Process Flow Schedule with status: ' || l_return_status, 1);
1591 END IF;
1592 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1593
1594 else --flow return status
1595 IF PG_DEBUG <> 0 THEN
1596 oe_debug_pub.add('create_flow_subassembly: ' || 'Success in Process Flow Schedule.');
1597 END IF;
1598 if (l_x_flow_schedule_rec.wip_entity_id is not NULL) then
1599 l_stmt_num := 100;
1600 IF (pflow_sch_details.count = 0 ) THEN
1601 l_flow_index := 1;
1602
1603 ELSE
1604 l_flow_index := pflow_sch_details.last+1;
1605
1606 END IF;
1607
1608 pflow_sch_details(l_flow_index).t_item_details_index := pIndex;
1609 pflow_sch_details(l_flow_index).schedule_number := l_x_flow_schedule_rec.schedule_number;
1610 pflow_sch_details(l_flow_index).wip_entity_id := l_x_flow_schedule_rec.wip_entity_id;
1611 pflow_sch_details(l_flow_index).scheduled_start_date := l_x_flow_schedule_rec.scheduled_start_date;
1612 pflow_sch_details(l_flow_index).planned_quantity := l_x_flow_schedule_rec.planned_quantity;
1613 pflow_sch_details(l_flow_index).scheduled_completion_date := l_x_flow_schedule_rec.scheduled_completion_date;
1614 pflow_sch_details(l_flow_index).build_sequence := l_x_flow_schedule_rec.build_sequence;
1615 pflow_sch_details(l_flow_index).line_id := l_x_flow_schedule_rec.line_id ;
1616
1617
1618
1619
1620 l_stmt_num := 120;
1621 INSERT INTO BOM_CTO_MLSUPPLY_FLOW_TEMP
1622 ( order_line_id,
1623 item_index,
1624 schedule_number,
1625 wip_entity_id,
1626 scheduled_start_date ,
1627 planned_quantity ,
1628 scheduled_completion_date,
1629 build_sequence,
1630 line_id
1631 )
1632 VALUES( pitems_table(pIndex).order_line_id,
1633 pflow_sch_details(l_flow_index).t_item_details_index, --current child item index
1634 pflow_sch_details(l_flow_index).schedule_number,
1635 pflow_sch_details(l_flow_index).wip_entity_id,
1636 pflow_sch_details(l_flow_index).scheduled_start_date,
1637 pflow_sch_details(l_flow_index).planned_quantity,
1638 pflow_sch_details(l_flow_index).scheduled_completion_date,
1639 pflow_sch_details(l_flow_index).build_sequence,
1640 pflow_sch_details(l_flow_index).line_id
1641 );
1642
1643
1644
1645 IF PG_DEBUG <> 0 Then
1646 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','after process flow schedule');
1647 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','ietm index'|| pflow_sch_details(1).t_item_details_index);
1648 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','item_id '||l_x_flow_schedule_rec.primary_item_id );
1649 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','scheudle_number'|| pflow_sch_details(1).schedule_number);
1650 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','wipentity id'|| pflow_sch_details(1).wip_entity_id);
1651 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','schedule start date'||pflow_sch_details(1).scheduled_start_date );
1652 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','planned qty'|| pflow_sch_details(1).planned_quantity);
1653 cto_wip_workflow_api_pk.cto_debug('create_flow_subassembly','scheudle_completion_date'|| pflow_sch_details(1).scheduled_completion_date);
1654
1655 End if;
1656
1657
1658
1659
1660
1661 IF PG_DEBUG <> 0 THEN
1662 oe_debug_pub.add('create_flow_subassembly: ' || 'alternate_bom_designator : ' ||l_x_flow_schedule_rec.alternate_bom_designator ,1);
1663
1664 oe_debug_pub.add('create_flow_subassembly: ' || 'alternate_routing_desig ' ||l_x_flow_schedule_rec.alternate_routing_desig ,1);
1665
1666 oe_debug_pub.add('create_flow_subassembly: ' || 'bom_revision ' ||l_x_flow_schedule_rec.bom_revision,1);
1667
1668 oe_debug_pub.add('create_flow_subassembly: ' || 'bom_revision_date ' ||l_x_flow_schedule_rec.bom_revision_date ,1);
1669
1670 oe_debug_pub.add('create_flow_subassembly: ' || 'build_sequence ' ||l_x_flow_schedule_rec.build_sequence ,1);
1671
1672 oe_debug_pub.add('create_flow_subassembly: ' || 'class_code' ||l_x_flow_schedule_rec.class_code ,1);
1673
1674 oe_debug_pub.add('create_flow_subassembly: ' || 'completion_locator_id ' ||l_x_flow_schedule_rec.completion_locator_id ,1);
1675
1676 oe_debug_pub.add('create_flow_subassembly: ' || 'completion_subinventory ' ||l_x_flow_schedule_rec.completion_subinventory ,1);
1677
1678 oe_debug_pub.add('create_flow_subassembly: ' || 'demand_class' ||l_x_flow_schedule_rec.demand_class ,1);
1679
1680 oe_debug_pub.add('create_flow_subassembly: ' || 'demand_source_delivery' ||l_x_flow_schedule_rec.demand_source_delivery ,1);
1681
1682 oe_debug_pub.add('create_flow_subassembly: ' || 'demand_source_header_id ' ||l_x_flow_schedule_rec.demand_source_header_id ,1);
1683
1684 oe_debug_pub.add('create_flow_subassembly: ' || 'line_id' ||l_x_flow_schedule_rec.line_id ,1);
1685
1686 oe_debug_pub.add('create_flow_subassembly: ' || 'organization_id ' ||l_x_flow_schedule_rec.organization_id ,1);
1687
1688 oe_debug_pub.add('create_flow_subassembly: ' || 'planned_quantity' ||l_x_flow_schedule_rec.planned_quantity ,1);
1689
1690 oe_debug_pub.add('create_flow_subassembly: ' || 'primary_item_id ' ||l_x_flow_schedule_rec.primary_item_id ,1);
1691
1692 oe_debug_pub.add('create_flow_subassembly: ' || 'project_id ' ||l_x_flow_schedule_rec.project_id ,1);
1693
1694 oe_debug_pub.add('create_flow_subassembly: ' || 'quantity_completed ' ||l_x_flow_schedule_rec.quantity_completed ,1);
1695
1696 oe_debug_pub.add('create_flow_subassembly: ' || 'scheduled_completion_date ' ||l_x_flow_schedule_rec.scheduled_completion_date ,1);
1697
1698 oe_debug_pub.add('create_flow_subassembly: ' || 'scheduled_flag ' ||l_x_flow_schedule_rec.scheduled_flag ,1);
1699
1700 oe_debug_pub.add('create_flow_subassembly: ' || 'scheduled_start_date' ||l_x_flow_schedule_rec.scheduled_start_date ,1);
1701
1702 oe_debug_pub.add('create_flow_subassembly: ' || 'task_id ' ||l_x_flow_schedule_rec.task_id ,1);
1703
1704 oe_debug_pub.add('create_flow_subassembly: ' || 'wip_entity_id ' ||l_x_flow_schedule_rec.wip_entity_id ,1);
1705
1706 oe_debug_pub.add('create_flow_subassembly: ' || 'scheduled_by' ||l_x_flow_schedule_rec.scheduled_by,1);
1707
1708 oe_debug_pub.add('create_flow_subassembly: ' || 'operation ' ||l_x_flow_schedule_rec.operation,1);
1709
1710 oe_debug_pub.add('create_flow_subassembly: ' || 'db_flag ' ||l_x_flow_schedule_rec.db_flag ,1);
1711
1712 oe_debug_pub.add('create_flow_subassembly: ' || 'quantity_scrapped ' ||l_x_flow_schedule_rec.quantity_scrapped ,1);
1713
1714 oe_debug_pub.add('create_flow_subassembly: ' || 'synch_schedule_num' ||l_x_flow_schedule_rec.synch_schedule_num ,1);
1715
1716 oe_debug_pub.add('create_flow_subassembly: ' || 'synch_operation_seq_num ' ||l_x_flow_schedule_rec.synch_operation_seq_num ,1);
1717
1718 oe_debug_pub.add('create_flow_subassembly: ' || 'roll_forwarded_flag ' ||l_x_flow_schedule_rec.roll_forwarded_flag ,1);
1719
1720 oe_debug_pub.add('create_flow_subassembly: ' || 'current_line_operation ' ||l_x_flow_schedule_rec.current_line_operation ,1);
1721
1722
1723
1724
1725
1726 END IF;
1727
1728
1729
1730 end if;
1731 end if; --flow return status
1732
1733 EXCEPTION
1734
1735 when FND_API.G_EXC_ERROR then
1736
1737 x_return_status := FND_API.G_RET_STS_ERROR;
1738 x_error_message := 'CTOSUBSB.create_flow_subassembly expected excpn: ' || to_char(l_stmt_num)|| ':' ||
1739 substrb(sqlerrm,1,100);
1740
1741
1742 IF PG_DEBUG <> 0 THEN
1743 IF PG_DEBUG <> 0 THEN
1744 oe_debug_pub.add('create_flow_subassembly: ' || 'CTOSUBSB.create_flow_subassembly expected excpn: ' || x_error_message,1);
1745 END IF;
1746 END IF;
1747
1748
1749 when FND_API.G_EXC_UNEXPECTED_ERROR then
1750 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1751 x_error_message := 'CTOSUBSB.create_flow_subassembly UN expected excpn: ' || to_char(l_stmt_num)|| ':' ||
1752 substrb(sqlerrm,1,100);
1753
1754
1755 IF PG_DEBUG <> 0 THEN
1756 IF PG_DEBUG <> 0 THEN
1757 oe_debug_pub.add('create_flow_subassembly: ' || 'CTOSUBSB.create_flow_subassembly UN expected excpn: ' || x_error_message,1);
1758 END IF;
1759 END IF;
1760
1761
1762
1763 when OTHERS then
1764 x_return_status := FND_API.G_RET_STS_ERROR;
1765 x_error_message := 'CTOSUBSB.create_flow_subassembly OTHERS excpn: ' || to_char(l_stmt_num)|| ':' ||
1766 substrb(sqlerrm,1,100);
1767
1768 IF PG_DEBUG <> 0 THEN
1769 IF PG_DEBUG <> 0 THEN
1770 oe_debug_pub.add('create_flow_subassembly: ' || 'CTOSUBSB.create_flow_subassembly OTHERS excpn: ' || x_error_message,1);
1771 END IF;
1772 END IF;
1773
1774
1775
1776
1777 END create_flow_subassembly;
1778
1779 /*
1780 Procedure get_mlsupply_details
1781
1782 */
1783
1784 --
1785 -- Bug 16202914
1786 -- Modified the API to replace the row by row processing in the cursors
1787 -- with Bulk processing
1788 --
1789 PROCEDURE get_mlsupply_details(
1790 x_return_status OUT NOCOPY VARCHAR2,
1791 x_error_message OUT NOCOPY VARCHAR2,
1792 x_message_name OUT NOCOPY VARCHAR2 )
1793 IS
1794 l_sourced VARCHAR2(1);
1795 l_supply_type VARCHAR2(100);
1796 l_config_ato VARCHAR2(10);
1797 l_run_req_import_flag VARCHAR2(1) := 'N';
1798
1799 CURSOR c_order_details
1800 IS
1801 SELECT DISTINCT(bcmm.order_line_id) order_line_id,
1802 oeh.order_number order_number
1803 FROM bom_cto_mlsupply_main_temp bcmm,
1804 oe_order_lines_all oel,
1805 oe_order_headers_all oeh
1806 WHERE bcmm.order_line_id = oel.line_id
1807 AND oel.header_id = oeh.header_id
1808 ORDER BY oeh.order_number,
1809 bcmm.order_line_id;
1810
1811 CURSOR c_supply_details(p_order_line_id IN NUMBER)
1812 IS
1813 SELECT item_index,
1814 parent_index,
1815 ITEM_ID,
1816 item_name,
1817 ITEM_QUANTITY,
1818 NEEDED_ITEM_QTY,
1819 AUTO_CONFIG_FLAG,
1820 JOB_START_DATE,
1821 JOB_COMPLETION_DATE,
1822 SOURCE_TYPE,
1823 CFM_ROUTING_FLAG,
1824 comments
1825 FROM bom_cto_mlsupply_main_temp
1826 WHERE order_line_id = p_order_line_id
1827 ORDER BY item_index;
1828
1829 CURSOR c_flow_supply(p_order_line_id IN NUMBER)
1830 IS
1831 SELECT item_index,
1832 schedule_number,
1833 scheduled_start_date,
1834 scheduled_completion_date,
1835 synch_schedule_num
1836 FROM bom_cto_mlsupply_flow_temp
1837 WHERE order_line_id = p_order_line_id
1838 ORDER BY item_index,
1839 scheduled_completion_date,
1840 schedule_number;
1841
1842 TYPE p_order_details_tab_typ IS TABLE OF c_order_details%rowtype INDEX BY binary_integer;
1843 TYPE p_supply_details_tab_typ IS TABLE OF c_supply_details%rowtype INDEX BY binary_integer;
1844 TYPE p_flow_supply_tab_typ IS TABLE OF c_flow_supply%rowtype INDEX BY binary_integer;
1845
1846 p_order_details_tab p_order_details_tab_typ ;
1847 p_supply_details_tab p_supply_details_tab_typ;
1848 p_flow_supply_tab p_flow_supply_tab_typ;
1849 BEGIN
1850 OPEN c_order_details;
1851 FETCH c_order_details BULK COLLECT INTO p_order_details_tab;
1852 CLOSE c_order_details;
1853
1854 IF (p_order_details_tab.COUNT <> 0) THEN
1855 FOR order_details_cntr IN 1..p_order_details_tab.COUNT
1856 LOOP
1857 IF PG_DEBUG <> 0 THEN
1858 oe_debug_pub.add(' SUPPLY FOR ORDER NUMBER = ' || p_order_details_tab(order_details_cntr).order_number ||
1859 'LINE_ID = ' || p_order_details_tab(order_details_cntr).order_line_id ,1);
1860 oe_debug_pub.add('-----------------------------------------------------------------------------------------',1);
1861 --CTO DEBUG FILE
1862 cto_wip_workflow_api_pk.cto_debug ('get_mlsupply_details',' SUPPLY FOR ORDER NUMBER = ' || p_order_details_tab(order_details_cntr).order_number ||
1863 'LINE_ID = ' || p_order_details_tab(order_details_cntr).order_line_id );
1864 cto_wip_workflow_api_pk.cto_debug ('get_mlsupply_details','-----------------------------------------------------------------------------------------');
1865 oe_debug_pub.add('INDEX--'||'PARENT_INDEX--'||'ITEM_ID--'||'ITEM_NAME--'||'ITEM_QTY--'||
1866 'NEEDED_ITEM_QTY--'|| 'CONFIG/ATO--'||'JOB_START_DATE--'||'JOB_COMPLETION_DATE--'||
1867 'SOURCED--'||'DISCREATE/FLOW/BUY--'||'COMMENTS',1);
1868 cto_wip_workflow_api_pk.cto_debug ('get_mlsupply_details','INDEX--'||'PARENT_INDEX--'||'ITEM_ID--'||'ITEM_NAME--'||'ITEM_QTY--'||
1869 'NEEDED_ITEM_QTY--'|| 'CONFIG/ATO--'||'JOB_START_DATE--'||'JOB_COMPLETION_DATE--'||
1870 'SOURCED--'||'DISCREATE/FLOW/BUY--' ||'COMMENTS');
1871 END IF;
1872
1873 OPEN c_supply_details(p_order_details_tab(order_details_cntr).order_line_id);
1874 FETCH c_supply_details BULK COLLECT INTO p_supply_details_tab;
1875 CLOSE c_supply_details;
1876
1877 IF (p_supply_details_tab.COUNT <> 0) THEN
1878 FOR supply_details_cntr IN 1..p_supply_details_tab.COUNT
1879 LOOP
1880 IF p_supply_details_tab(supply_details_cntr).auto_config_flag = 'Y' THEN
1881 l_config_ato := 'CONFIG';
1882 ELSE
1883 l_config_ato := 'ATO ITEM';
1884 END IF;
1885 -- transfer =1 , 66 = multiple sources
1886 -- rkaza. 05/02/2005. ireq project.
1887 -- Need to recommend running req import for 100% transfer cases
1888 -- also in addition to buy cases. Yet mark them as sourced.
1889 IF p_supply_details_tab(supply_details_cntr).source_type = 1 THEN
1890 l_sourced := 'Y';
1891 l_supply_type := '100% Transfer';
1892 IF l_run_req_import_flag = 'N' THEN
1893 l_run_req_import_flag := 'Y' ;
1894 END IF;
1895 ELSIF p_supply_details_tab(supply_details_cntr).source_type = 66 THEN
1896 l_sourced := 'Y';
1897 l_supply_type := 'Planning';
1898 ELSIF p_supply_details_tab(supply_details_cntr).source_type = 3 THEN
1899 l_sourced := 'N';
1900 l_supply_type := 'BUY';
1901 IF l_run_req_import_flag = 'N' THEN
1902 l_run_req_import_flag := 'Y' ;
1903 END IF;
1904 ELSE
1905 IF p_supply_details_tab(supply_details_cntr).cfm_routing_flag = 2 THEN
1906 l_sourced := 'N';
1907 l_supply_type := 'Discrete';
1908 ELSIF p_supply_details_tab(supply_details_cntr).cfm_routing_flag = -99 THEN
1909 l_sourced := 'N';
1910 l_supply_type := 'No routing Default to discrete';
1911 ELSE
1912 l_sourced := 'N';
1913 l_supply_type := 'FLOW';
1914 END IF; --cfm flag
1915 END IF; --source type
1916
1917 IF PG_DEBUG <> 0 THEN
1918 oe_debug_pub.add(p_supply_details_tab(supply_details_cntr).item_index
1919 ||' -- '||p_supply_details_tab(supply_details_cntr).parent_index
1920 ||' -- '||p_supply_details_tab(supply_details_cntr).item_id
1921 ||' -- '||p_supply_details_tab(supply_details_cntr).item_name
1922 ||' -- '||p_supply_details_tab(supply_details_cntr).item_quantity
1923 ||' -- '|| p_supply_details_tab(supply_details_cntr).needed_item_qty
1924 ||' -- '||l_config_ato
1925 ||' -- '||TO_CHAR(p_supply_details_tab(supply_details_cntr).job_start_date,'mm/dd/yyyy hh24:mi:ss')
1926 ||' -- '|| TO_CHAR(p_supply_details_tab(supply_details_cntr).job_completion_date,'mm/dd/yyyy hh24:mi:ss')
1927 ||' -- '||l_sourced
1928 ||' --'||l_supply_type
1929 ||' -- '||p_supply_details_tab(supply_details_cntr).comments,1);
1930
1931 cto_wip_workflow_api_pk.cto_debug ('get_mlsupply_details',p_supply_details_tab(supply_details_cntr).item_index
1932 ||' -- '||p_supply_details_tab(supply_details_cntr).parent_index
1933 ||' -- '||p_supply_details_tab(supply_details_cntr).item_id
1934 ||' -- '||p_supply_details_tab(supply_details_cntr).item_name
1935 ||' -- '|| p_supply_details_tab(supply_details_cntr).item_quantity
1936 ||' -- '||p_supply_details_tab(supply_details_cntr).needed_item_qty
1937 ||' -- '||l_config_ato
1938 ||' -- '||TO_CHAR(p_supply_details_tab(supply_details_cntr).job_start_date,'mm/dd/yyyy hh24:mi:ss')
1939 ||' -- '||TO_CHAR(p_supply_details_tab(supply_details_cntr).job_completion_date,'mm/dd/yyyy hh24:mi:ss')
1940 ||' -- '||l_sourced
1941 ||' -- '||l_supply_type
1942 ||' -- '||p_supply_details_tab(supply_details_cntr).comments );
1943 END IF;
1944 END LOOP;
1945 END IF;
1946
1947 OPEN c_flow_supply (p_order_details_tab(order_details_cntr).order_line_id);
1948 FETCH c_flow_supply BULK COLLECT INTO p_flow_supply_tab;
1949 CLOSE c_flow_supply ;
1950
1951 IF (p_flow_supply_tab.COUNT <> 0) THEN
1952 IF PG_DEBUG <> 0 THEN
1953 oe_debug_pub.add('INDEX(from above)--'||'SCHEDULE_NUMBER--'||'SCHEDULE_START_DATE--'||'SCHEDULE_COMPLETION_DATE--'||'PARENT_SCHEDULE_NUM(if flow parent)',1);
1954 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)');
1955 END IF;
1956
1957 FOR flow_supply_cntr IN 1..p_flow_supply_tab.COUNT
1958 LOOP
1959 IF PG_DEBUG <> 0 THEN
1960 oe_debug_pub.add(p_flow_supply_tab(flow_supply_cntr).item_index
1961 ||' -- '||p_flow_supply_tab(flow_supply_cntr).schedule_number
1962 ||' -- '||TO_CHAR(p_flow_supply_tab(flow_supply_cntr).scheduled_start_date,'mm/dd/yyyy hh24:mi:ss')
1963 ||' -- '|| TO_CHAR(p_flow_supply_tab(flow_supply_cntr).scheduled_completion_date,'mm/dd/yyyy hh24:mi:ss')
1964 ||' -- '||p_flow_supply_tab(flow_supply_cntr).synch_schedule_num,1);
1965
1966 cto_wip_workflow_api_pk.cto_debug ('get_mlsupply_details',p_flow_supply_tab(flow_supply_cntr).item_index
1967 ||' -- '||p_flow_supply_tab(flow_supply_cntr).schedule_number
1968 ||' -- '|| TO_CHAR(p_flow_supply_tab(flow_supply_cntr).scheduled_start_date,'mm/dd/yyyy hh24:mi:ss')
1969 ||' -- '|| TO_CHAR(p_flow_supply_tab(flow_supply_cntr).scheduled_completion_date,'mm/dd/yyyy hh24:mi:ss')
1970 ||' -- '||p_flow_supply_tab(flow_supply_cntr).synch_schedule_num );
1971 END IF;
1972 END LOOP;
1973 END IF;
1974 END LOOP;
1975 END IF;
1976
1977 IF l_run_req_import_flag = 'Y' THEN
1978 --bugfix 2755695
1979 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 );
1980 END IF;
1981 END get_mlsupply_details;
1982
1983
1984 /*
1985 p_top_assembly_type 1= if called from discrete code
1986 2= if called from flow code
1987 insert into wip for child discrete make --but wip mass load called with differnet sequenece
1988 insert into child buy
1989
1990
1991
1992
1993 */
1994
1995
1996 Procedure create_subassembly_jobs
1997 (
1998
1999 p_mlsupply_parameter in number, --org parameter indicating whether auto-created or ( AtOITEM and autocreated) 1= autocreated and 2 =
2000 p_Top_Assembly_LineId in number,
2001 pSupplyQty in number,
2002 p_wip_seq in number,
2003 p_status_type in number,
2004 p_class_code in varchar2,
2005 p_conc_request_id IN NUMBER,
2006 p_conc_program_id IN NUMBER,
2007 p_conc_login_id IN NUMBER,
2008 p_user_id IN NUMBER,
2009 p_appl_conc_program_id IN NUMBER,
2010 x_return_status out NOCOPY varchar2,
2011 x_error_message out NOCOPY VARCHAR2, /* 70 bytes to hold msg */
2012 x_message_name out NOCOPY VARCHAR2 /* 30 bytes to hold name */
2013 )
2014 is
2015
2016 l_finite_scheduler_flag number := null;
2017
2018 l_parent_start_date DATE ;
2019 l_child_item_id NUMBER :=0 ;
2020 l_child_qty Number :=0;
2021
2022 l_item_id mtl_system_items_kfv.inventory_item_id%type;
2023 l_ship_org mtl_system_items_kfv.organization_id%type;
2024 l_schedule_ship_date date ;
2025 l_item_name mtl_system_items_kfv.concatenated_segments%type;
2026 l_fixed_lead_time mtl_system_items_kfv.fixed_lead_time%type;
2027 l_variable_lead_time mtl_system_items_kfv.variable_lead_time%type;
2028 l_processing_lead_time mtl_system_items_kfv.full_lead_time%type;
2029 l_ordered_uom varchar2(3) := null;
2030 l_order_number number := null;
2031 l_cfm_routing_flag number := null;
2032 l_ordered_quantity number := null;
2033 l_routing_sequence_id number := null;
2034 l_lead_time number := null;
2035 L_OPERATION_SEQ_ID number := null;
2036 l_auto_config_flag varchar2(1);
2037
2038 l_min_completion_date date;
2039
2040 l_child_operation_date date;
2041 X_CHILD_COMPLETION_DATE date;
2042
2043
2044 l_project_id number := null;
2045 l_task_id number := null;
2046 L_X_RETURN_STATUS varchar2(1) ;
2047 l_x_msg_count number;
2048 l_x_msg_data varchar2(240);
2049 l_stmt_num number := 0;
2050
2051 x_groupID Number;
2052
2053 l_requestId Number;
2054 x_retVal varchar2(100);
2055 x_errMsg varchar2(100);
2056
2057 x_completion_date DATE;
2058 x_parent_job_start_date DATE ;
2059
2060 --table of records to hold the item details
2061 l_mlsupply_items t_item_details;
2062
2063 l_flow_sch_details t_flow_sch_details;
2064
2065
2066
2067
2068 l_index number;
2069 flow_index number ;
2070 errbuf varchar2(10);
2071 retcode number;
2072 max_completion_date date;
2073
2074
2075 l_return_status varchar2(1);
2076 l_msg_count number;
2077 l_msg_data varchar2(240);
2078 -- l_stmt_num number := 0;
2079
2080 l_ret_status varchar2(1);
2081 l_error_messsage varchar2(70) := null;
2082 l_msg_name varchar2(30) := null;
2083
2084 v_time1 number;
2085 v_time2 number;
2086
2087
2088
2089 CURSOR c_flow_sch IS
2090 SELECT wfs.schedule_number,
2091 wfs.wip_entity_id,
2092 wfs.scheduled_start_date,
2093 wfs.planned_quantity,
2094 wfs.scheduled_completion_date,
2095 wfs.build_sequence,
2096 wfs.line_id,
2097 wil.line_code
2098 FROM wip_flow_schedules wfs,
2099 wip_lines wil
2100 WHERE demand_source_line = p_Top_Assembly_LineId
2101 AND wfs.line_id = wil.line_id;
2102
2103
2104 l_discrete_under_flow varchar2(1) := 'N'; --will become Y if there is a discrete under top most flow parent
2105 l_sub_level_buy_item varchar2(1) := 'N';
2106 l_user_id number ;
2107 l_login_id number;
2108 --l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2109 l_program_id number;
2110
2111 l_token CTO_MSG_PUB.token_tbl;
2112
2113 -- rkaza. ireq project. 05/05/2005.
2114 l_req_input_data CTO_AUTO_PROCURE_PK.req_interface_input_data;
2115
2116 l_phantom varchar2(1);
2117 cnt_wjsi number; --Bugfix 8913125
2118
2119 l_routing_count_sa number := 0; --Bugfix 14157494
2120
2121 BEGIN
2122
2123 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2124
2125
2126 If PG_DEBUG <> 0 Then
2127 cto_wip_workflow_api_pk.cto_debug('Create_sub_assembly_jobs','Inside create sub-assembly jobs');
2128 cto_wip_workflow_api_pk.cto_debug('Create_sub_assembly_jobs','FOR LINE ID '||p_Top_Assembly_LineId );
2129 End if;
2130
2131 l_stmt_num := 140;
2132
2133 SELECT oel.inventory_item_id,
2134 oel.ship_from_org_id,
2135 oel.schedule_ship_date,
2136 oel. project_id,
2137 oel.task_id,
2138 oel.ordered_quantity,
2139 mtl.concatenated_segments,
2140 mtl.auto_created_config_flag,
2141 nvl(mtl.fixed_lead_time,0),
2142 nvl(mtl.variable_lead_time,0),
2143 nvl(mtl.full_lead_time,0),
2144 order_quantity_uom ,
2145 oeh.order_number,
2146 nvl(bor.cfm_routing_flag,-99),
2147 bor.routing_sequence_id
2148 INTO l_item_id,
2149 l_ship_org,
2150 l_schedule_ship_date,
2151 l_project_id,
2152 l_task_id,
2153 l_ordered_quantity,
2154 l_item_name,
2155 l_auto_config_flag,
2156 l_fixed_lead_time,
2157 l_variable_lead_time,
2158 l_processing_lead_time,
2159 l_ordered_uom,
2160 l_order_number,
2161 l_cfm_routing_flag,
2162 l_routing_sequence_id
2163 FROM oe_order_lines_all oel,
2164 oe_order_headers_all oeh,
2165 mtl_system_items_kfv mtl,
2166 bom_operational_routings bor
2167 WHERE oel.line_id = p_Top_Assembly_LineId
2168 AND oeh.header_id = oel.header_id
2169 AND oel.inventory_item_id = mtl.inventory_item_id
2170 AND oel.ship_from_org_id = mtl.organization_id
2171 AND bor.assembly_item_id (+)= mtl.inventory_item_id
2172 AND bor.organization_id(+) = mtl.organization_id
2173 AND bor.alternate_routing_designator(+) is null
2174 ;
2175
2176 IF (l_mlsupply_items.count = 0) THEN --adding topmoset parent details
2177 l_mlsupply_items(1).item_id := p_Top_Assembly_LineId;
2178 l_mlsupply_items(1).item_id := l_item_id;
2179 l_mlsupply_items(1).item_name := l_item_name;
2180 IF ( pSupplyQty is null) THEN
2181 l_mlsupply_items(1).item_quantity := l_ordered_quantity;
2182 l_mlsupply_items(1).needed_item_qty := l_ordered_quantity; --top most parent needed qty = supply quantity
2183 ELSE
2184 l_mlsupply_items(1).item_quantity := pSupplyQty;
2185 l_mlsupply_items(1).needed_item_qty := pSupplyQty; --top most parent needed qty = supply quantity
2186
2187 END IF;
2188 -- l_mlsupply_items(1).operation_lead_time_percent := 0;
2189 l_mlsupply_items(1).cfm_routing_flag := l_cfm_routing_flag;
2190 l_mlsupply_items(1).routing_sequence_id := l_routing_sequence_id;
2191 l_mlsupply_items(1).fixed_lead_time := l_fixed_lead_time;
2192 l_mlsupply_items(1).variable_lead_time := l_variable_lead_time;
2193 l_mlsupply_items(1).processing_lead_time := l_processing_lead_time;
2194 l_mlsupply_items(1).job_completion_date := l_schedule_ship_date;
2195
2196 If PG_DEBUG <> 0 Then
2197 cto_wip_workflow_api_pk.cto_debug('Create_sub_assembly_jobs','Entered top-most item details into table');
2198 End if;
2199
2200 IF(l_cfm_routing_flag = 1) THEN --if top most parent = flow
2201
2202
2203 l_stmt_num := 160;
2204 flow_index := 1;
2205 OPEN c_flow_sch;
2206 LOOP
2207
2208 FETCH c_flow_sch INTO l_flow_sch_details(flow_index).schedule_number,
2209 l_flow_sch_details(flow_index).wip_entity_id,
2210 l_flow_sch_details(flow_index).scheduled_start_date,
2211 l_flow_sch_details(flow_index).planned_quantity ,
2212 l_flow_sch_details(flow_index).scheduled_completion_date,
2213 l_flow_sch_details(flow_index).build_sequence,
2214 l_flow_sch_details(flow_index).line_id,
2215 l_flow_sch_details(flow_index).line_code;
2216
2217
2218 EXIT when c_flow_sch%notfound;
2219
2220 oe_debug_pub.add('create_subassembly_supply'||'top most flow parent schdeule number' || l_flow_sch_details(flow_index).schedule_number);
2221
2222
2223
2224 IF (flow_index = 1 ) THEN
2225 l_mlsupply_items(1).flow_start_index := 1;
2226 l_mlsupply_items(1).line_id := l_flow_sch_details(flow_index).line_id;
2227 l_mlsupply_items(1).line_code := l_flow_sch_details(flow_index).line_code;
2228 l_mlsupply_items(1).feeder_run := 'N';
2229 END IF;
2230 l_flow_sch_details(flow_index).t_item_details_index := 1;
2231 flow_index := l_flow_sch_details.last+1;
2232
2233
2234 END LOOP;
2235
2236 IF (l_mlsupply_items(1).flow_start_index = 1) THEN --which means there was a row inserted
2237 l_mlsupply_items(1).flow_end_index := l_flow_sch_details.last;
2238
2239
2240 l_stmt_num := 170;
2241 INSERT INTO BOM_CTO_MLSUPPLY_FLOW_TEMP
2242 ( order_line_id,
2243 item_index,
2244 schedule_number,
2245 wip_entity_id,
2246 scheduled_start_date ,
2247 planned_quantity ,
2248 scheduled_completion_date,
2249 build_sequence,
2250 line_id,
2251 synch_schedule_num,
2252 SYNCH_OPERATION_SEQ_NUM )
2253 SELECT
2254 p_Top_Assembly_LineId,
2255 1 ,
2256 schedule_number,
2257 wip_entity_id,
2258 scheduled_start_date ,
2259 planned_quantity ,
2260 scheduled_completion_date,
2261 build_sequence,
2262 line_id,
2263 synch_schedule_num,
2264 SYNCH_OPERATION_SEQ_NUM
2265 FROM wip_flow_schedules
2266 where demand_source_line = p_Top_Assembly_LineId;
2267
2268 END IF;
2269
2270 CLOSE c_flow_sch;
2271
2272
2273
2274
2275 END IF; --if top most paernt is flow
2276
2277
2278
2279
2280 --Insert for top most parent into BOM_CTO_MLSUPPLY_MAIN_TEMP table
2281 l_stmt_num := 180;
2282
2283 INSERT INTO BOM_CTO_MLSUPPLY_MAIN_TEMP
2284 ( order_line_id,
2285 item_index ,
2286 item_id,
2287 item_name,
2288 AUTO_CONFIG_FLAG,
2289 item_quantity,
2290 needed_item_qty ,
2291 cfm_routing_flag ,
2292 routing_sequence_id ,
2293 fixed_lead_time,
2294 variable_lead_time ,
2295 processing_lead_time ,
2296 job_completion_date,
2297 line_id,
2298 line_code,
2299 flow_start_index,
2300 flow_end_index
2301 )
2302 VALUES ( p_Top_Assembly_LineId,
2303 1, --as it is first elemnt
2304 l_item_id,
2305 l_item_name ,
2306 l_auto_config_flag,
2307 l_mlsupply_items(1).item_quantity,
2308 l_mlsupply_items(1).needed_item_qty ,
2309 l_cfm_routing_flag ,
2310 l_routing_sequence_id ,
2311 l_fixed_lead_time,
2312 l_variable_lead_time ,
2313 l_processing_lead_time ,
2314 l_schedule_ship_date,
2315 l_mlsupply_items(1).line_id,
2316 l_mlsupply_items(1).line_code,
2317 l_mlsupply_items(1).flow_start_index,
2318 l_mlsupply_items(1).flow_end_index
2319
2320
2321
2322 ) ;
2323
2324
2325
2326
2327
2328
2329 END IF; --top most parent details
2330
2331 l_stmt_num := 190;
2332 If PG_DEBUG <> 0 Then
2333 cto_wip_workflow_api_pk.cto_debug('Create_sub_assembly_jobs','Before calling get_child_configurations' );
2334 oe_debug_pub.add ('create_subassembly_jobs: ' || 'Before calling get_child_configurations',1);
2335 End if;
2336 get_child_configurations
2337 (
2338 pParentItemId =>l_mlsupply_items(1).item_id,
2339 pOrganization_id =>l_ship_org,
2340 pLower_Supplytype =>p_mlsupply_parameter, -- lower level supply type
2341 pParent_index =>1,--parent index passed as one
2342 pitems_table =>l_mlsupply_items,
2343 x_return_status => l_ret_status,
2344 x_error_message => l_error_messsage,
2345 x_message_name => l_msg_name
2346
2347 );
2348 l_stmt_num := 19100;
2349
2350 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2351 IF PG_DEBUG <> 0 THEN
2352 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after call to get_child_configurations with status ' || l_return_status ,1);
2353
2354 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2355 END IF;
2356 RAISE FND_API.G_EXC_ERROR;
2357 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2358 IF PG_DEBUG <> 0 THEN
2359 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to get_child_configurations ' || l_return_status ,1);
2360
2361 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2362 END IF;
2363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2364 ELSE
2365 IF PG_DEBUG <> 0 THEN
2366 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from get_child_configurations ' ,1);
2367 END IF;
2368 END IF;
2369 l_stmt_num := 19101;
2370
2371 IF (l_mlsupply_items.count = 1) THEN
2372 x_error_message := 'NO children present at level ' || p_mlsupply_parameter ;
2373 RETURN;
2374
2375
2376 END IF;
2377 l_stmt_num := 19102;
2378
2379
2380 --getting completion date
2381
2382 oe_debug_pub.add('Before starting logic of completion date',1);
2383
2384
2385 IF (l_mlsupply_items.count > 0) THEN --checks for uninitialized collection --bugfix2308063
2386
2387 l_index := 2;--completion date is needed from 2nd level discrete item
2388 l_stmt_num := 19103;
2389 LOOP
2390
2391 oe_debug_pub.add('Looping for item'||l_mlsupply_items(l_index).item_name,1);
2392 l_mlsupply_items(l_index).order_line_id := p_Top_Assembly_LineId;
2393
2394 --insert data into MAIN table
2395
2396 INSERT INTO BOM_CTO_MLSUPPLY_MAIN_TEMP
2397 ( order_line_id,
2398 item_index ,
2399 PARENT_INDEX,
2400 item_id,
2401 item_name,
2402 AUTO_CONFIG_FLAG,
2403 item_quantity,
2404 needed_item_qty ,
2405 cfm_routing_flag ,
2406 routing_sequence_id ,
2407 fixed_lead_time,
2408 variable_lead_time ,
2409 processing_lead_time ,
2410 --job_completion_date,
2411 line_id,
2412 line_code,
2413 flow_start_index,
2414 flow_end_index,
2415 source_type,
2416 comments,
2417 wip_supply_type,
2418 OPERATION_SEQ_NUM
2419 )
2420 VALUES ( p_Top_Assembly_LineId,
2421 l_index,
2422 l_mlsupply_items(l_index).parent_index,
2423 l_mlsupply_items(l_index).item_id,
2424 l_mlsupply_items(l_index).item_name,
2425 l_mlsupply_items(l_index).auto_config_flag,
2426 l_mlsupply_items(l_index).item_quantity,
2427 l_mlsupply_items(l_index).needed_item_qty ,
2428 l_mlsupply_items(l_index).cfm_routing_flag,
2429 l_mlsupply_items(l_index).routing_sequence_id ,
2430 l_mlsupply_items(l_index).fixed_lead_time,
2431 l_mlsupply_items(l_index).variable_lead_time ,
2432 l_mlsupply_items(l_index).processing_lead_time ,
2433 --l_schedule_ship_date,
2434 l_mlsupply_items(l_index).line_id,
2435 l_mlsupply_items(l_index).line_code,
2436 l_mlsupply_items(l_index).flow_start_index,
2437 l_mlsupply_items(l_index).flow_end_index,
2438 l_mlsupply_items(l_index).source_type,
2439 l_mlsupply_items(l_index).comment,
2440 l_mlsupply_items(l_index).wip_supply_type, --4645636
2441 l_mlsupply_items(l_index).operation_seq_num --4645636
2442
2443
2444 ) ;
2445
2446 EXIT WHEN l_index = l_mlsupply_items.LAST;
2447 l_index := l_mlsupply_items.NEXT(l_index);
2448 END LOOP;
2449 END IF;
2450
2451 BEGIN
2452 select 'Y' INTO l_phantom
2453 from BOM_CTO_MLSUPPLY_MAIN_TEMP
2454 where wip_supply_type = 6
2455 and rownum = 1;
2456 Exception
2457 when no_data_found then
2458 l_phantom := 'N';
2459
2460 end;
2461
2462 oe_debug_pub.add('Phantom flag'||l_phantom,1);
2463
2464 IF l_phantom = 'Y' THEN
2465 oe_debug_pub.add('About to call process_phantoms',1);
2466 --call process children under phatom
2467 process_phantoms
2468 (
2469 pitems_table=>l_mlsupply_items,
2470 p_organization_id =>l_ship_org,
2471 x_return_status => l_ret_status,
2472 x_error_message => l_error_messsage,
2473 x_message_name => l_msg_name
2474 );
2475 END IF;
2476
2477 IF (l_mlsupply_items.count > 0) THEN --checks for uninitialized collection --bugfix2308063
2478
2479 l_index := 2;--completion date is needed from 2nd level discrete item
2480 LOOP
2481
2482 oe_debug_pub.add('Looping again for item'||l_mlsupply_items(l_index).item_name,1);
2483 l_mlsupply_items(l_index).order_line_id := p_Top_Assembly_LineId;
2484
2485 -- rkaza. ireq project. 05/03/2005.
2486 -- Enabling 100% transfer rule supply creation for lower
2487 -- level items (source type = 1).
2488
2489 IF ( l_mlsupply_items(l_index).source_type in (1,2,3) and
2490 l_mlsupply_items(l_index).needed_item_qty >0
2491
2492 --4645636 do not cal times for phantom items
2493 -- l_mlsupply_items(l_index).wip_supply_type <> 6
2494 ) THEN --check if item is not sourced
2495
2496 IF( l_mlsupply_items(l_mlsupply_items(l_index).parent_index).cfm_routing_flag =2 OR
2497 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).cfm_routing_flag = -99) THEN --parent is discrete
2498 --get the value of wip finite scheduler flag if not selected previously
2499 IF(l_finite_scheduler_flag is null) THEN
2500
2501 SELECT nvl(use_finite_scheduler,2)
2502 INTO l_finite_scheduler_flag
2503 FROM wip_parameters
2504 WHERE organization_id = l_ship_org;
2505
2506 oe_debug_pub.add ('create_subassembly_jobs: ' || 'l_finite_scheduler_flag is '|| l_finite_scheduler_flag ,1);
2507
2508 END IF;--finnite scheduler
2509
2510
2511 IF ( l_mlsupply_items(l_index).job_completion_date is null) THEN
2512 IF (l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_start_date is null) THEN
2513
2514 l_stmt_num := 200;
2515 get_start_date(
2516 pCompletion_date => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_completion_date,
2517 pQty => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).needed_item_qty,
2518 pitemid => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).item_id,
2519 porganization_id => l_ship_org,
2520 pfixed_leadtime => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).fixed_lead_time,
2521 pvariable_leadtime => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).variable_lead_time,
2522 x_start_date => x_parent_job_start_date,
2523 x_return_status => l_ret_status,
2524 x_error_message => l_error_messsage,
2525 x_message_name => l_msg_name
2526 );
2527
2528
2529 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2530 IF PG_DEBUG <> 0 THEN
2531 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after call to get_start_date with status ' || l_return_status ,1);
2532
2533 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2534 END IF;
2535 RAISE FND_API.G_EXC_ERROR;
2536 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2537 IF PG_DEBUG <> 0 THEN
2538 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to get_start_date ' || l_return_status ,1);
2539
2540 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage,1);
2541 END IF;
2542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2543 ELSE
2544 IF PG_DEBUG <> 0 THEN
2545 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from get_start_date ' ,1);
2546 END IF;
2547 END IF;
2548
2549
2550 IF ( l_finite_scheduler_flag = 1) THEN
2551 IF (x_parent_job_start_date <= SYSDATE) THEN
2552 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_start_date := SYSDATE;
2553
2554 --populate start date flag = 1implies insert satrt date in wjsi instead of completion date
2555 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).populate_start_date := 1;
2556
2557 IF PG_DEBUG <> 0 THEN
2558 oe_debug_pub.add('create_subassembly_jobs: ' || 'actual parent starts date is < sysdate ' ,1);
2559 END IF;
2560
2561 ELSE
2562 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_start_date := x_parent_job_start_date;
2563 oe_debug_pub.add('create_subassembly_jobs: ' || 'parent_start_date '|| to_char(x_parent_job_start_date,'mm/dd/yy hh24:mi:ss') ,1);
2564
2565 END IF;
2566
2567 ELSE
2568 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_start_date := x_parent_job_start_date;
2569 oe_debug_pub.add('create_subassembly_jobs: ' || 'parent_start_date '|| to_char(x_parent_job_start_date,'mm/dd/yy hh:mi:ss') ,1);
2570 END IF ;
2571
2572
2573
2574
2575 --update parent items job start date
2576 l_stmt_num := 300;
2577 update bom_cto_mlsupply_main_temp
2578 set job_start_date = l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_start_date
2579 where item_index = l_mlsupply_items(l_index).parent_index
2580 and order_line_id = p_Top_Assembly_LineId ;
2581
2582
2583
2584 END IF;
2585
2586
2587 IF ( l_mlsupply_items(l_mlsupply_items(l_index).parent_index).populate_start_date is null) THEN
2588 get_completion_date(
2589
2590 pParent_job_start_date => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_start_date,
2591 porganization_id => l_ship_org,
2592 plead_time_offset_percent => l_mlsupply_items(l_index).OPERATION_LEAD_TIME_PERCENT,
2593 pParent_processing_lead_time => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).processing_lead_time ,
2594 ppostprocessing_time => l_mlsupply_items(l_index).postprocessing_lead_time ,
2595 pSource_type => l_mlsupply_items(l_index).source_type,
2596 x_child_completion_date => x_completion_date,
2597 x_return_status => l_ret_status,
2598 x_error_message => l_error_messsage,
2599 x_message_name => l_msg_name
2600 );
2601
2602
2603
2604
2605 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2606 IF PG_DEBUG <> 0 THEN
2607 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after call to get-completion_date with status ' || l_return_status ,1);
2608
2609 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2610 END IF;
2611 RAISE FND_API.G_EXC_ERROR;
2612 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2613 IF PG_DEBUG <> 0 THEN
2614 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to get_completion_date ' || l_return_status ,1);
2615
2616 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2617 END IF;
2618 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2619 ELSE
2620 IF PG_DEBUG <> 0 THEN
2621 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from get_completion_date ' ,1);
2622 oe_debug_pub.add('create_subassembly_jobs: ' || 'parent_start_date '|| to_char(x_parent_job_start_date,'mm/dd/yy hh:mi:ss') ,1);
2623 END IF;
2624 END IF;
2625
2626
2627 IF ( l_finite_scheduler_flag = 1) THEN
2628 IF (x_completion_date <= SYSDATE) THEN --sysdate check
2629 -- rkaza. 05/03/2005. Added
2630 -- source type 1 here.
2631 IF (l_mlsupply_items(l_index).source_type in (1,3)) THEN --buy and 100% transfer item
2632 l_mlsupply_items(l_index).job_completion_date := SYSDATE;
2633
2634 ELSE --make(discrete/flow)
2635 l_mlsupply_items(l_index).populate_start_date := 1; -- to insert wip inetrface with satrt date
2636
2637 l_mlsupply_items(l_index).job_completion_date := SYSDATE;
2638 l_mlsupply_items(l_index).job_start_date := SYSDATE;
2639 END IF; --buy item
2640
2641
2642
2643 ELSE --not sysdate
2644 l_mlsupply_items(l_index).job_completion_date := x_completion_date;
2645
2646 --if flow is top most item AND
2647 --if finite scheduler is on we calculate job start date
2648 --as we need to insert both first unit start date as well as last unit completion date
2649 --bugfix#2739590
2650
2651 -- rkaza. 05/05/2005.
2652 -- Following block is only
2653 -- needed for WIP items.
2654 IF(l_mlsupply_items(1).cfm_routing_flag = 1) -- top item is flow
2655 and l_mlsupply_items(l_index).source_type = 2
2656 and l_mlsupply_items(l_index).cfm_routing_flag <> 1 THEN
2657
2658 get_start_date(
2659 pCompletion_date => l_mlsupply_items(l_index).job_completion_date,
2660 pQty => l_mlsupply_items(l_index).needed_item_qty,
2661 pitemid => l_mlsupply_items(l_index).item_id,
2662 porganization_id => l_ship_org,
2663 pfixed_leadtime => l_mlsupply_items(l_index).fixed_lead_time,
2664 pvariable_leadtime => l_mlsupply_items(l_index).variable_lead_time,
2665 x_start_date => x_parent_job_start_date,
2666 x_return_status => l_ret_status,
2667 x_error_message => l_error_messsage,
2668 x_message_name => l_msg_name
2669 );
2670
2671 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2672 IF PG_DEBUG <> 0 THEN
2673 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after call to get_start_date with status ' || l_return_status ,1);
2674
2675 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2676 END IF;
2677 RAISE FND_API.G_EXC_ERROR;
2678 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2679 IF PG_DEBUG <> 0 THEN
2680 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to get_start_date ' || l_return_status ,1);
2681
2682 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage,1);
2683 END IF;
2684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2685 ELSE
2686 IF PG_DEBUG <> 0 THEN
2687 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from get_start_date ' ,1);
2688 END IF;
2689 END IF;
2690
2691 IF (x_parent_job_start_date <= SYSDATE) THEN
2692 l_mlsupply_items(l_index).job_start_date := SYSDATE;
2693
2694 IF PG_DEBUG <> 0 THEN
2695 oe_debug_pub.add('create_subassembly_jobs: ' || 'actual parent starts date is < sysdate ' ,1);
2696 END IF;
2697
2698 ELSE
2699 l_mlsupply_items(l_index).job_start_date := x_parent_job_start_date;
2700 oe_debug_pub.add('create_subassembly_jobs: ' || 'parent_start_date '|| to_char(x_parent_job_start_date,'mm/dd/yy hh:mi:ss') ,1);
2701
2702 END IF;
2703
2704
2705 END IF; --top most item is flow
2706
2707
2708
2709 END IF; --sysdate check
2710
2711 ELSE --infinite scheduler
2712 -- rkaza. 05/05/2005. Added IR also.
2713 IF (x_completion_date <= SYSDATE and l_mlsupply_items(l_index).source_type in (1, 3)) THEN
2714 l_mlsupply_items(l_index).job_completion_date := SYSDATE; --2858631
2715 ELSE
2716 l_mlsupply_items(l_index).job_completion_date := x_completion_date;
2717 END IF;
2718
2719 END IF ;
2720
2721 --original code
2722
2723
2724 ELSE --parent date is before sysdate
2725 -- rkaza. 05/05/2005. Added IR also.
2726 IF (l_mlsupply_items(l_index).source_type in (1, 3) ) THEN --buy and IR item
2727 --buy item shoiuld always get created either on sysdate or after sysdate
2728
2729 l_mlsupply_items(l_index).job_completion_date := SYSDATE;
2730
2731 ELSE --make(discrete/flow)
2732 l_mlsupply_items(l_index).populate_start_date := 1; -- to insert wip inetrface with satrt date
2733
2734 l_mlsupply_items(l_index).job_completion_date := SYSDATE;
2735 l_mlsupply_items(l_index).job_start_date := SYSDATE;
2736 END IF; --buy item
2737
2738 END IF; -- end if parent start date is after sysdate
2739
2740 update bom_cto_mlsupply_main_temp
2741 set job_completion_date = l_mlsupply_items(l_index).job_completion_date,
2742 job_start_date = l_mlsupply_items(l_index).job_start_date -- could be null value
2743 where item_index = l_index
2744 and order_line_id = p_Top_Assembly_LineId ;
2745
2746 END IF; --job completion date
2747
2748 IF (l_mlsupply_items(l_index).source_type = 2 ) THEN --make
2749 IF ( l_mlsupply_items(l_index).cfm_routing_flag = 1 ) THEN --flow item
2750
2751 --flow schedule creation
2752
2753 l_stmt_num := 210;
2754 If PG_DEBUG <> 0 Then
2755 cto_wip_workflow_api_pk.cto_debug ('create_sub_assembly_jobs','calling create flow schedule for'|| l_mlsupply_items(l_index).item_id);
2756
2757 oe_debug_pub.add('create_subassembly_jobs: ' || 'calling create flow schedule for'|| l_mlsupply_items(l_index).item_id);
2758 END IF;
2759
2760 create_flow_subassembly (
2761 pflow_sch_details => l_flow_sch_details,
2762 pIndex => l_index,
2763 pitems_table => l_mlsupply_items,
2764 pShip_org => l_ship_org,
2765 pProject_id => l_project_id,
2766 pTask_id => l_task_id,
2767 x_return_status => l_ret_status,
2768 x_error_message => l_error_messsage,
2769 x_message_name => l_msg_name
2770
2771 );
2772
2773 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2774 IF PG_DEBUG <> 0 THEN
2775 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after call to create_flow_subassembly with status ' || l_return_status ,1);
2776
2777 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2778 END IF;
2779 RAISE FND_API.G_EXC_ERROR;
2780 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2781 IF PG_DEBUG <> 0 THEN
2782 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to create_flow_subassembly ' || l_return_status ,1);
2783
2784 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2785 END IF;
2786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2787 ELSE
2788
2789 IF PG_DEBUG <> 0 THEN
2790 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from create_flow_subassembly ' ,1);
2791 END IF;
2792 END IF;
2793
2794
2795 END IF; --end flow schedule creation
2796 END IF; --make
2797 ELSE --parent is flow item
2798
2799
2800 oe_debug_pub.add (l_mlsupply_items(l_index).item_name || ' parent is a flow item',1);
2801
2802 -- if current item is discrete do not call feeder
2803 -- if current item is flow call feeder
2804
2805
2806
2807 IF(l_mlsupply_items(l_index).cfm_routing_flag =1 ) THEN -- child is flow
2808
2809 oe_debug_pub.add('checking if feeder is run',1);
2810 IF ( l_mlsupply_items(l_mlsupply_items(l_index).parent_index).feeder_run <> 'Y' ) THEN
2811 oe_debug_pub.add('calling feeder line api');
2812
2813 l_stmt_num := 220;
2814 SELECT max(scheduled_completion_date)
2815 into max_completion_date
2816 from BOM_CTO_MLSUPPLY_FLOW_TEMP
2817 where item_index = l_mlsupply_items(l_index).parent_index
2818 and order_line_id = p_Top_Assembly_LineId ;
2819
2820 oe_debug_pub.add('aparameters for feeder call');
2821 oe_debug_pub.add('max schcompletion date'||to_char(max_completion_date,'dd/mm/yy hh:mi:ss') );
2822 oe_debug_pub.add('LIne code :'||l_mlsupply_items(l_mlsupply_items(l_index).parent_index).line_code );
2823
2824 --creating child supply on feeder line
2825 l_stmt_num := 230;
2826 FLM_CREATE_PRODUCT_SYNCH.create_schedules(
2827 errbuf,
2828 retcode,
2829 l_ship_org,
2830 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).line_code,
2831 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).line_code,
2832 to_char(SYSDATE-1,'YYYY/MM/DD hh:mm:ss'),
2833 to_char(max_completion_date,'YYYY/MM/DD hh:mm:ss'), --to_char(SYSDATE+7,'YYYY/MM/DD hh:mm:ss'),
2834 'N');
2835
2836 oe_debug_pub.add('After calklling feeder line api',1);
2837
2838 --set parent feeder run flag to 'Y'
2839 l_mlsupply_items(l_mlsupply_items(l_index).parent_index).feeder_run := 'Y';
2840
2841 END IF ;--parent feeder flag
2842
2843 l_stmt_num := 240;
2844 INSERT INTO BOM_CTO_MLSUPPLY_FLOW_TEMP (
2845 order_line_id,
2846 item_index,
2847 schedule_number,
2848 wip_entity_id,
2849 scheduled_start_date ,
2850 planned_quantity ,
2851 scheduled_completion_date,
2852 build_sequence,
2853 line_id,
2854 synch_schedule_num,
2855 SYNCH_OPERATION_SEQ_NUM )
2856 SELECT p_Top_Assembly_LineId,
2857 l_index, --current child item index
2858 schedule_number,
2859 wip_entity_id,
2860 scheduled_start_date ,
2861 planned_quantity ,
2862 scheduled_completion_date,
2863 build_sequence,
2864 line_id,
2865 synch_schedule_num,
2866 SYNCH_OPERATION_SEQ_NUM
2867 FROM wip_flow_schedules
2868 where primary_item_id = l_mlsupply_items(l_index).item_id
2869 and synch_schedule_num in
2870 ( Select schedule_number
2871 from BOM_CTO_MLSUPPLY_FLOW_TEMP
2872 where item_index = l_mlsupply_items(l_index).parent_index
2873 and order_line_id = p_Top_Assembly_LineId
2874 );
2875 ELSE--child is wip/buy/IR
2876
2877
2878 l_stmt_num := 242;
2879
2880 --bugfix 2765109
2881 BEGIN
2882 Select nvl(line_op_seq_id,-99) --bugfix 2786582
2883 into l_operation_seq_id
2884 from bom_operation_sequences
2885 where routing_sequence_id = l_mlsupply_items(l_mlsupply_items(l_index).parent_index).routing_sequence_id
2886 and operation_seq_num = l_mlsupply_items(l_index).operation_seq_num
2887 and operation_type =1
2888 and nvl(EFFECTIVITY_DATE,sysdate+1) <= SYSDATE
2889 and nvl(disable_date,sysdate+1) > sysdate;
2890
2891 EXCEPTION
2892 WHEN no_data_found THEN
2893 l_operation_seq_id := -99;
2894
2895 END;
2896
2897 IF PG_DEBUG <> 0 THEN
2898 oe_debug_pub.add ('create_subassembly_jobs: ' || 'operation seq id is' || l_operation_seq_id ,1);
2899
2900
2901 END IF;
2902
2903 IF l_operation_seq_id = -99 THEN --bugfix 2765109
2904 l_stmt_num := 2421;
2905 SELECT min(scheduled_start_date)
2906 into l_child_operation_date
2907 from BOM_CTO_MLSUPPLY_FLOW_TEMP
2908 where item_index = l_mlsupply_items(l_index).parent_index;
2909
2910 IF PG_DEBUG <> 0 THEN
2911
2912 oe_debug_pub.add ('create_subassembly_jobs: ' || 'operation offsetd date is '|| l_child_operation_date,1);
2913
2914 END IF;
2915
2916 ELSE
2917
2918 IF PG_DEBUG <> 0 THEN
2919
2920 oe_debug_pub.add ('create_subassembly_jobs: ' || 'before entering get_operation offsetd ate' ,1);
2921
2922 END IF;
2923
2924 l_stmt_num := 241;
2925 SELECT min(scheduled_completion_date)
2926 into l_min_completion_date
2927 from BOM_CTO_MLSUPPLY_FLOW_TEMP
2928 where item_index = l_mlsupply_items(l_index).parent_index;
2929
2930 IF PG_DEBUG <> 0 THEN
2931 oe_debug_pub.add ('create_subassembly_jobs: ' || 'min schedule date' || to_char(l_min_completion_date,'mm/dd/yy hh:mi:ss') ,1);
2932
2933 END IF;
2934
2935
2936 l_stmt_num := 243;
2937 l_child_operation_date := MRP_FLOW_SCHEDULE_PUB.get_operation_offset_date
2938 ( p_api_version_number => 1.0,
2939 x_return_status => l_x_return_status,
2940 x_msg_count => l_x_msg_count,
2941 x_msg_data => l_x_msg_data,
2942 p_org_id => l_ship_org,
2943 p_assembly_item_id => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).item_id,
2944 p_routing_sequence_id => l_mlsupply_items(l_mlsupply_items(l_index).parent_index).routing_sequence_id,
2945 p_operation_sequence_id => l_operation_seq_id,
2946 p_assembly_qty => 1, --? what should this quantity be ,ask adrian
2947 p_assembly_comp_date => l_min_completion_date ,
2948 p_calculate_option => 1 --implies for the first unit made
2949 );
2950
2951
2952 END IF;
2953
2954 IF PG_DEBUG <> 0 THEN
2955 oe_debug_pub.add ('create_subassembly_jobs: ' || 'after get_ioperation offset date with date' || l_child_operation_date ,1);
2956
2957 END IF;
2958
2959 l_stmt_num := 244;
2960
2961 -- rkaza. 05/05/2005. Added IR here.
2962 -- Comp date can be set as child op date for IR.
2963 -- No lead time considerations for IR.
2964
2965 IF (l_mlsupply_items(l_index).source_type in (1, 3) ) THEN -- buy and IR child item
2966
2967 l_lead_time := CEIL(l_mlsupply_items(l_index).postprocessing_lead_time + 1); --ie postporcoessing+1day
2968
2969 x_child_completion_date := l_child_operation_date;
2970
2971 IF PG_DEBUG <> 0 THEN
2972 oe_debug_pub.add ('create_subassembly_jobs: ' || 'lead time for buy child is ' || l_lead_time ,1);
2973
2974 END IF;
2975
2976 if l_mlsupply_items(l_index).source_type = 3 then
2977 l_stmt_num := 250;
2978 get_working_day
2979 (
2980 porgid => l_ship_org,
2981 Pdate => l_child_operation_date,
2982 pleadtime =>l_lead_time,
2983 pdirection => 'B', --direction in getting working day 'backward' for buy item as here it is always backward
2984 x_ret_date => x_child_completion_date,
2985 x_return_status => l_ret_status,
2986 x_error_message => l_error_messsage,
2987 x_message_name => l_msg_name
2988 );
2989
2990 end if;
2991
2992 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
2993 IF PG_DEBUG <> 0 THEN
2994 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after get_wroking_day' || l_return_status ,1);
2995
2996 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
2997 END IF;
2998 RAISE FND_API.G_EXC_ERROR;
2999 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3000 IF PG_DEBUG <> 0 THEN
3001 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to get_working_day' || l_return_status ,1);
3002
3003 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
3004 END IF;
3005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3006 ELSE
3007
3008 IF PG_DEBUG <> 0 THEN
3009 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from get_working_day ' ,1);
3010 END IF;
3011 END IF;
3012
3013 IF (x_child_completion_date <= SYSDATE) THEN
3014 IF PG_DEBUG <> 0 THEN
3015 oe_debug_pub.add('create_subassembly_jobs: ' || 'buy or IR job comp date is < than sysdate,so default to sysdate' ,1);
3016 oe_debug_pub.add('create_subassembly_jobs: ' || 'buy or IR job comp date is ' || x_child_completion_date,1);
3017 END IF;
3018
3019 l_mlsupply_items(l_index).job_completion_date := SYSDATE;
3020
3021 ELSE
3022
3023 l_mlsupply_items(l_index).job_completion_date := x_child_completion_date;
3024
3025 IF PG_DEBUG <> 0 THEN
3026 oe_debug_pub.add('create_subassembly_jobs: ' || 'buy or IR job comp date is ' || x_child_completion_date,1);
3027 END IF;
3028
3029
3030 END IF;
3031
3032 update bom_cto_mlsupply_main_temp
3033 set job_completion_date = l_mlsupply_items(l_index).job_completion_date
3034 where item_index = l_index
3035 and order_line_id = p_Top_Assembly_LineId;
3036
3037
3038 ELSE --discrete child item
3039
3040 --get the value of wip finite scheduler flag if not selected previously when uder flow
3041 IF(l_finite_scheduler_flag is null) THEN
3042
3043 SELECT nvl(use_finite_scheduler,2)
3044 INTO l_finite_scheduler_flag
3045 FROM wip_parameters
3046 WHERE organization_id = l_ship_org;
3047
3048 oe_debug_pub.add ('create_subassembly_jobs: ' || 'l_finite_scheduler_flag is '|| l_finite_scheduler_flag ,1);
3049
3050 END IF;--finnite scheduler
3051
3052
3053 IF (l_finite_scheduler_flag =1) THEN
3054
3055 IF( l_child_operation_date <= SYSDATE) THEN --less than sysdate
3056 oe_debug_pub.add('create_subassembly_jobs: ' || 'Finite scheduler ON and DISCRETE job comp date is <SYSDATE., SO DEFAULT TO SYSDTAE',1);
3057 oe_debug_pub.add('create_subassembly_jobs: '|| ' DIS job coompl date is l_child_operation_date',1);
3058 l_mlsupply_items(l_index).populate_start_date := 1; -- to insert wip inetrface with satrt date
3059
3060 l_mlsupply_items(l_index).job_completion_date := SYSDATE;
3061 l_mlsupply_items(l_index).job_start_date := SYSDATE;
3062
3063 update bom_cto_mlsupply_main_temp
3064 set job_completion_date = l_mlsupply_items(l_index).job_completion_date,
3065 job_start_date = l_mlsupply_items(l_index).job_start_date
3066 where item_index = l_index
3067 and order_line_id = p_Top_Assembly_LineId;
3068
3069 ELSE-- greater sysdate
3070
3071 l_mlsupply_items(l_index).job_completion_date := l_child_operation_date;
3072 oe_debug_pub.add('create_subassembly_jobs: ' || 'DISCRETE job comp date is '|| l_mlsupply_items(l_index).job_completion_date,1);
3073
3074
3075 --if flow is top most item AND
3076 --if finite scheduler is on we calculate job start date
3077 --as we need to insert both first unit start date as well as last unit completion date
3078 --bugfix#2739590
3079
3080
3081 IF(l_mlsupply_items(1).cfm_routing_flag = 1) THEN --top most item is flow
3082
3083 get_start_date(
3084 pCompletion_date => l_mlsupply_items(l_index).job_completion_date,
3085 pQty => l_mlsupply_items(l_index).needed_item_qty,
3086 pitemid => l_mlsupply_items(l_index).item_id,
3087 porganization_id => l_ship_org,
3088 pfixed_leadtime => l_mlsupply_items(l_index).fixed_lead_time,
3089 pvariable_leadtime => l_mlsupply_items(l_index).variable_lead_time,
3090 x_start_date => x_parent_job_start_date,
3091 x_return_status => l_ret_status,
3092 x_error_message => l_error_messsage,
3093 x_message_name => l_msg_name
3094 );
3095
3096 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
3097 IF PG_DEBUG <> 0 THEN
3098 oe_debug_pub.add ('create_subassembly_jobs: ' || 'failed after call to get_start_date with status ' || l_return_status ,1);
3099
3100 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage ,1);
3101 END IF;
3102 RAISE FND_API.G_EXC_ERROR;
3103 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3104 IF PG_DEBUG <> 0 THEN
3105 oe_debug_pub.add ('create_subassembly_jobs: ' || ' failed after call to get_start_date ' || l_return_status ,1);
3106
3107 oe_debug_pub.add ('create_subassembly_jobs: ' || 'error message' || l_error_messsage,1);
3108 END IF;
3109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3110 ELSE
3111 IF PG_DEBUG <> 0 THEN
3112 oe_debug_pub.add('create_subassembly_jobs: ' || 'success from get_start_date ' ,1);
3113 END IF;
3114 END IF;
3115
3116 IF (x_parent_job_start_date <= SYSDATE) THEN
3117 l_mlsupply_items(l_index).job_start_date := SYSDATE;
3118
3119 IF PG_DEBUG <> 0 THEN
3120 oe_debug_pub.add('create_subassembly_jobs: ' || 'actual parent starts date is < sysdate ' ,1);
3121 END IF;
3122
3123 ELSE
3124 l_mlsupply_items(l_index).job_start_date := x_parent_job_start_date;
3125 oe_debug_pub.add('create_subassembly_jobs: ' || 'parent_start_date '|| to_char(x_parent_job_start_date,'mm/dd/yy hh:mi:ss') ,1);
3126
3127 END IF;
3128
3129
3130 END IF; --top most item is flow
3131
3132 update bom_cto_mlsupply_main_temp
3133 set job_completion_date = l_mlsupply_items(l_index).job_completion_date,
3134 job_start_date = l_mlsupply_items(l_index).job_start_date
3135 where item_index = l_index
3136 and order_line_id = p_Top_Assembly_LineId;
3137
3138 END IF;--sysdate checj
3139 ELSE--infinite scheduler
3140
3141
3142 l_mlsupply_items(l_index).job_completion_date := l_child_operation_date;
3143
3144
3145 oe_debug_pub.add('create_subassembly_jobs: ' || 'DISCRETE job comp date is '|| l_mlsupply_items(l_index).job_completion_date,1);
3146
3147 update bom_cto_mlsupply_main_temp
3148 set job_completion_date = l_mlsupply_items(l_index).job_completion_date
3149 where item_index = l_index
3150 and order_line_id = p_Top_Assembly_LineId;
3151
3152
3153 END IF;--finute scheduler check
3154
3155
3156 END IF; -- child is buy or IR/discrete
3157
3158
3159 END IF; --child is flow or others
3160
3161
3162 END IF ; --parent is WIP or flow
3163
3164 END IF; --check for item sourcing
3165
3166 EXIT WHEN l_index = l_mlsupply_items.LAST;
3167 l_index := l_mlsupply_items.NEXT(l_index);
3168 END LOOP;
3169
3170 END IF;
3171
3172
3173
3174 --reomve this part at end of UT
3175 If PG_DEBUG <> 0 Then
3176 oe_debug_pub.add ('DEBUG EMSSAEG AFTER COMPLETION DATE CALCULATION',1);
3177
3178 cto_wip_workflow_api_pk.cto_debug('Completion date debug messages','after completion date calculations');
3179 End if;
3180 --displaying children
3181 IF (l_mlsupply_items.count > 0) THEN --checks for uninitialized collection --bugfix2308063
3182
3183 oe_debug_pub.add ('index --'||'item_name--' || 'item_quantity--'||'needed_item_quantity--'||'OPERATION_LEAD_TIME_PERCENT--'||
3184 'operation_seq_num--'|| 'cfm_routing_flag--'||'routing_sequence_id--'||'fixed_lead_time--'||
3185 'variable_lead_time--' || 'processing_lead_time--' || 'postprocessing_lead_time--' ||
3186 'bom_item_type --' || 'parent_index--'||'job_start_date --'||'job_completion_date --'||
3187 'line_id--' || 'line_code--'||'source_type--' || 'feeder_run--' || 'flow_start_index--' ||
3188 'flow_end_index '
3189 );
3190
3191 l_index := 1;--completion date is needed from 2nd level discrete item
3192 LOOP
3193 oe_debug_pub.add ('idx=>'||l_mlsupply_items(l_index).t_item_details_index || '--' ||
3194 'name=>'||l_mlsupply_items(l_index).item_name||'--' ||
3195 'qty=>'||l_mlsupply_items(l_index).item_quantity||'--'||
3196 'qty=>'||l_mlsupply_items(l_index).needed_item_qty||'--'||
3197 'LT%=>'||l_mlsupply_items(l_index).OPERATION_LEAD_TIME_PERCENT||'--'||
3198 'opseq=>'||l_mlsupply_items(l_index).operation_seq_num||'--'||
3199 'cfm=>'||l_mlsupply_items(l_index).cfm_routing_flag||'--'||
3200 'rout=>'||l_mlsupply_items(l_index).routing_sequence_id||'--'||
3201 'FLT=>'||l_mlsupply_items(l_index).fixed_lead_time||'--'||
3202 'VLT=>'||l_mlsupply_items(l_index).variable_lead_time||'--' ||
3203 'PLT=>'||l_mlsupply_items(l_index).processing_lead_time||'--' ||
3204 'PPLT=>'||l_mlsupply_items(l_index).postprocessing_lead_time||'--' ||
3205 'BIT=>'||l_mlsupply_items(l_index).bom_item_type||' --' ||
3206 'PIDX=>'||l_mlsupply_items(l_index).parent_index||'--'||
3207 'JSTDT=>'||l_mlsupply_items(l_index).job_start_date||' --'||
3208 'JCDT=>'||l_mlsupply_items(l_index).job_completion_date||' --'||
3209 'FLINE=>'||l_mlsupply_items(l_index).line_id||'--' ||
3210 'FLcode=>'||l_mlsupply_items(l_index).line_code||'--'||
3211 'Src=>'||l_mlsupply_items(l_index).source_type||'--' ||
3212 'Feed=>'||l_mlsupply_items(l_index).feeder_run||'--' ||
3213 'FLOW_ST_IDX=>'||l_mlsupply_items(l_index).flow_start_index||'--' ||
3214 'FLOW_END_IDX=>'||l_mlsupply_items(l_index).flow_end_index
3215 );
3216
3217 If PG_DEBUG <> 0 Then
3218 cto_wip_workflow_api_pk.cto_debug ('Create_sub_assembly_jobs','idx=>'||l_mlsupply_items(l_index).t_item_details_index || '--' ||
3219 'name=>'||l_mlsupply_items(l_index).item_name||'--' ||
3220 'qty=>'||l_mlsupply_items(l_index).item_quantity||'--'||
3221 'qty=>'||l_mlsupply_items(l_index).needed_item_qty||'--'||
3222 'LT%=>'||l_mlsupply_items(l_index).OPERATION_LEAD_TIME_PERCENT||'--'||
3223 'opseq=>'||l_mlsupply_items(l_index).operation_seq_num||'--'||
3224 'cfm=>'||l_mlsupply_items(l_index).cfm_routing_flag||'--'||
3225 'rout=>'||l_mlsupply_items(l_index).routing_sequence_id||'--'||
3226 'FLT=>'||l_mlsupply_items(l_index).fixed_lead_time||'--'||
3227 'VLT=>'||l_mlsupply_items(l_index).variable_lead_time||'--' ||
3228 'PLT=>'||l_mlsupply_items(l_index).processing_lead_time||'--' ||
3229 'PPLT=>'||l_mlsupply_items(l_index).postprocessing_lead_time||'--' ||
3230 'BIT=>'||l_mlsupply_items(l_index).bom_item_type||' --' ||
3231 'PIDX=>'||l_mlsupply_items(l_index).parent_index||'--'||
3232 'JSTDT=>'||l_mlsupply_items(l_index).job_start_date||' --'||
3233 'JCDT=>'||l_mlsupply_items(l_index).job_completion_date||' --'||
3234 'FLINE=>'||l_mlsupply_items(l_index).line_id||'--' ||
3235 'FLcode=>'||l_mlsupply_items(l_index).line_code||'--'||
3236 'Src=>'||l_mlsupply_items(l_index).source_type||'--' ||
3237 'Feed=>'||l_mlsupply_items(l_index).feeder_run||'--' ||
3238 'FLOW_ST_IDX=>'||l_mlsupply_items(l_index).flow_start_index||'--' ||
3239 'FLOW_END_IDX=>'||l_mlsupply_items(l_index).flow_end_index
3240 );
3241
3242
3243 End if;
3244
3245
3246
3247
3248
3249 EXIT WHEN l_index = l_mlsupply_items.LAST;
3250 l_index := l_mlsupply_items.NEXT(l_index);
3251 END LOOP;
3252
3253 END IF;
3254 l_index := null;
3255 --removw above part at end of UT
3256
3257
3258
3259
3260
3261 --end of calcultating compeltion date
3262
3263
3264
3265
3266 IF (l_mlsupply_items.count > 0) THEN --checks for uninitialized collection
3267
3268 l_index := l_mlsupply_items.FIRST;
3269 LOOP
3270
3271
3272 IF PG_DEBUG <> 0 THEN
3273 oe_debug_pub.add('create_subassembly_jobs: ' || l_index||'-- '||
3274 l_mlsupply_items(l_index).parent_index||'-- '||
3275 l_mlsupply_items(l_index).item_id||' -- '||
3276 l_mlsupply_items(l_index).item_name||' -- '||
3277 l_mlsupply_items(l_index).job_start_date||' -- '||
3278 l_mlsupply_items(l_index).job_completion_date||' -- '||
3279 l_mlsupply_items(l_index).fixed_lead_time||' -- '||
3280 l_mlsupply_items(l_index).variable_lead_time||' -- '||
3281 l_mlsupply_items(l_index).processing_lead_time,1);
3282 END IF;
3283
3284 EXIT WHEN l_index = l_mlsupply_items.LAST;
3285 l_index := l_mlsupply_items.NEXT(l_index);
3286 END LOOP;
3287 END IF;
3288
3289
3290 IF PG_DEBUG <> 0 THEN
3291 oe_debug_pub.add('create_subassembly_jobs: ' || 'before inserting children in wjsi ',1);
3292 END IF;
3293
3294 --to test submission of wip concurrent program from PL/SQL
3295
3296 SAVEPOINT REBUILD;
3297 l_index := 2;
3298 LOOP
3299 -- rkaza. ireq project. 05/05/2005. Firing
3300 -- populate_req_interface for IR also. Passsing source type
3301 -- source org as additional parameters.
3302
3303 IF (l_mlsupply_items(l_index).source_type in (1, 3) and
3304 l_mlsupply_items(l_index).needed_item_qty >0
3305 --4645636
3306 and l_mlsupply_items(l_index).wip_supply_type <> 6
3307 ) then
3308
3309 l_stmt_num := 260;
3310
3311 If PG_DEBUG <> 0 Then
3312 cto_wip_workflow_api_pk.cto_debug ('create_sub_assembly_jobs','insert po_interafce'|| l_mlsupply_items(l_index).item_id);
3313 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'));
3314 cto_wip_workflow_api_pk.cto_debug ('create_sub_assembly_jobs','need aty'||l_mlsupply_items(l_index).needed_item_qty);
3315 End if;
3316
3317 IF PG_DEBUG <> 0 THEN
3318 oe_debug_pub.add('create_subassembly_jobs: ' || 'insert po_interafce'|| l_mlsupply_items(l_index).item_id);
3319 oe_debug_pub.add('create_sub_assembly_jobs insert po_interafce'|| l_mlsupply_items(l_index).item_id);
3320 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'));
3321 oe_debug_pub.add('create_sub_assembly_jobs need aty'||l_mlsupply_items(l_index).needed_item_qty);
3322
3323 END IF;
3324
3325 l_req_input_data.source_type := l_mlsupply_items(l_index).source_type;
3326 l_req_input_data.sourcing_org := l_mlsupply_items(l_index).sourcing_org;
3327
3328 cto_auto_procure_pk.populate_req_interface (
3329 p_interface_source_code =>'CTO-LOWER LEVEL',
3330 p_destination_org_id =>l_ship_org,
3331 p_org_id =>null,
3332 p_created_by =>p_user_id, -- created_by
3333 p_need_by_date =>l_mlsupply_items(l_index).job_completion_date,
3334 p_order_quantity =>l_mlsupply_items(l_index).needed_item_qty,
3335 p_order_uom =>l_ordered_uom,
3336 p_item_id =>l_mlsupply_items(l_index).item_id,
3337 p_item_revision => null, --so_line.item_revision
3338 -- reverted bugfix 3042904 and provided
3339 --solution thru fix 3129117
3340 p_interface_source_line_id=>p_Top_Assembly_LineId,
3341 p_unit_price => null, -- req-import decides this price
3342 --not so_line.unit_selling_price,
3343 p_batch_id =>null,
3344 p_order_number =>l_order_number,
3345 p_req_interface_input_data => l_req_input_data,
3346 x_return_status =>x_return_status );
3347
3348 --change this varname from x_reaturn status to somethinelse
3349 IF x_return_status = FND_API.G_RET_STS_ERROR THEN --po return status
3350 RAISE FND_API.G_EXC_ERROR;
3351 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN --po return status
3352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3353 ELSE
3354
3355 IF PG_DEBUG <> 0 THEN
3356 oe_debug_pub.add('create_subassembly_jobs: ' || 'Req Insert successful for '|| l_mlsupply_items(l_index).item_id ,1);
3357 END IF;
3358 IF ( l_sub_level_buy_item = 'N') THEN
3359 l_sub_level_buy_item := 'Y';
3360 END IF;
3361
3362 END IF; --PO return status
3363
3364 ELSIF (l_mlsupply_items(l_index).source_type = 2
3365 and l_mlsupply_items(l_index).needed_item_qty >0
3366 --4645636
3367 and l_mlsupply_items(l_index).wip_supply_type<>6
3368 ) THEN --make in this org
3369
3370
3371 If(l_mlsupply_items(l_index).cfm_routing_flag = 2 OR
3372 l_mlsupply_items(l_index).cfm_routing_flag = -99) THEN -- discrete routing
3373
3374 IF PG_DEBUG <> 0 THEN
3375 oe_debug_pub.add('create_subassembly_jobs: ' || 'Status passed into lower level supply code is ' || p_status_type);
3376 END IF;
3377
3378 IF(l_mlsupply_items(1).cfm_routing_flag = 2 OR
3379 l_mlsupply_items(1).cfm_routing_flag = -99) THEN --top most parent is discrete
3380
3381 l_stmt_num := 280;
3382
3383 --Bugfix 14157494: Begin -- Check if any routing is present for the line_id
3384 IF PG_DEBUG <> 0 THEN
3385 oe_debug_pub.add('create_subassembly_jobs: p_Top_Assembly_LineId: '|| p_Top_Assembly_LineId,2);
3386 END IF;
3387
3388 SELECT count(1)
3389 INTO l_routing_count_sa
3390 FROM bom_operational_routings bor,
3391 OE_ORDER_LINES_ALL oel
3392 WHERE oel.INVENTORY_ITEM_ID = l_mlsupply_items(l_index).item_id
3393 AND oel.INVENTORY_ITEM_ID = bor.assembly_item_id
3394 AND oel.ship_from_org_id = bor.organization_id
3395 AND ROWNUM = 1;
3396
3397 IF PG_DEBUG <> 0 THEN
3398 oe_debug_pub.add('create_subassembly_jobs: INVENTORY_ITEM_ID :'|| l_mlsupply_items(l_index).item_id , 1);
3399 oe_debug_pub.add('create_subassembly_jobs: l_routing_count_sa :'|| to_char(l_routing_count_sa) ,1);
3400 oe_debug_pub.add('create_subassembly_jobs: Status passed into lower level supply code is: ' || p_status_type);
3401 END IF;
3402 --Bugfix 14157494: End
3403
3404 l_stmt_num := 281;
3405 -- Fixed bug 5346922
3406 -- Removed the decode for supply type
3407 insert into wip_job_schedule_interface
3408 (last_update_date,
3409 last_updated_by,
3410 creation_date,
3411 created_by,
3412 last_update_login,
3413 request_id,
3414 program_id,
3415 program_application_id,
3416 program_update_date,
3417 group_id,
3418 source_code,
3419 process_phase,
3420 process_status,
3421 organization_id,
3422 load_type,
3423 status_type,
3424 last_unit_completion_date,
3425 primary_item_id,
3426 wip_supply_type,
3427 class_code,
3428 firm_planned_flag,
3429 start_quantity,
3430 bom_revision_date,
3431 routing_revision_date,
3432 project_id,
3433 task_id,
3434 due_date,
3435 bom_revision
3436
3437
3438 )
3439 select SYSDATE,
3440 p_user_id,
3441 SYSDATE,
3442 p_user_id,
3443 p_conc_login_id,
3444 p_conc_request_id,
3445 p_conc_program_id,
3446 p_appl_conc_program_id,
3447 SYSDATE,
3448 p_wip_seq,
3449 'WICDOL',
3450 WIP_CONSTANTS.ML_VALIDATION,
3451 WIP_CONSTANTS.PENDING, -- process_status
3452 l_ship_org, -- organization id
3453 WIP_CONSTANTS.CREATE_JOB, --Load_Type
3454 nvl(p_status_type, WIP_CONSTANTS.UNRELEASED), -- Status_Type
3455 l_mlsupply_items(l_index).job_completion_date, -- Date Completed
3456 l_mlsupply_items(l_index).item_id, --Primary_Item_Id
3457 WIP_CONSTANTS.BASED_ON_BOM, -- Wip_Supply_Type
3458 decode(p_class_code, null, null
3459 , p_class_code), --Accouting Class
3460 2, --Firm_Planned_Flag
3461 l_mlsupply_items(l_index).needed_item_qty,
3462 trunc(greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
3463 'MI')+1/(60*24), --BOM_Revision_Date
3464 --Bugfix 14157494
3465 --greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
3466 decode(l_routing_count_sa, 0, null, greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE)),
3467 --Routing_Revision_Date
3468 --bugfix 3418102
3469 decode(l_mlsupply_items(l_index).pegging_flag,'I',l_project_id,'X',l_project_id, null),
3470 decode(l_mlsupply_items(l_index).pegging_flag,'I',l_task_id,'X',l_task_id, null),
3471 --end bugfix 3418102
3472 l_mlsupply_items(l_index).job_completion_date,
3473 BOM_REVISIONS.get_item_revision_fn
3474 ( 'ALL',
3475 'ALL',
3476 l_ship_org,
3477 l_mlsupply_items(l_index).item_id,
3478 (trunc (greatest(nvl(cal.calendar_date,SYSDATE),
3479 SYSDATE),'MI')+1/(60*24) )
3480 )
3481
3482 from bom_calendar_dates cal,
3483 mtl_parameters mp,
3484 wip_parameters wp,
3485 mtl_system_items msi
3486 where mp.organization_id = l_ship_org
3487 and wp.organization_id = mp.organization_id
3488 and msi.organization_id = l_ship_org
3489 and msi.inventory_item_id = l_mlsupply_items(l_index).item_id --inventory item id
3490 and cal.calendar_code = mp.calendar_code
3491 and cal.exception_set_id = mp.calendar_exception_set_id
3492 and cal.seq_num =
3493 (select greatest(1, (cal2.prior_seq_num -
3494 (ceil(nvl(msi.fixed_lead_time,0) +
3495 nvl(msi.variable_lead_time,0) *
3496 l_mlsupply_items(l_index).needed_item_qty --bugfix 2074290: this is in primary uom
3497 ))))
3498 from bom_calendar_dates cal2
3499 where cal2.calendar_code = mp.calendar_code
3500 and cal2.exception_set_id =
3501 mp.calendar_exception_set_id
3502 and cal2.calendar_date =
3503 trunc(l_mlsupply_items(l_index).job_completion_date)
3504 );
3505
3506 --Bugfix 8913125
3507 cnt_wjsi := sql%rowcount;
3508
3509 if (cnt_wjsi > 0) then --Bugfix 8913125: Replaced sql%rowcount with cnt_wjsi
3510 IF PG_DEBUG <> 0 THEN
3511 oe_debug_pub.add('create_subassembly_jobs: ' || 'Number of Rows Inserted in WJSI for children : ' || to_char(cnt_wjsi));
3512
3513 oe_debug_pub.add('create_subassembly_jobs: ' || 'GROUP ID Inserted in WJSI for children : ' || x_groupID);
3514 END IF;
3515 x_return_status := FND_API.G_RET_STS_SUCCESS;
3516 else
3517 x_return_status := FND_API.G_RET_STS_ERROR;
3518 cto_msg_pub.cto_message('BOM', 'BOM_ATO_PROCESS_ERROR');
3519
3520 end if;
3521
3522 ELSIF(l_mlsupply_items(1).cfm_routing_flag = 1
3523 and l_mlsupply_items(l_index).needed_item_qty >0
3524
3525 --4645636
3526 and l_mlsupply_items(l_index).wip_supply_type<>6
3527 ) THEN --top most parent is flow
3528
3529 IF (l_discrete_under_flow = 'N') THEN
3530 l_discrete_under_flow := 'Y';
3531
3532 --intialize var's to be used in isnerting WJSI table
3533 l_user_id := FND_GLOBAL.USER_ID;
3534 l_login_id := FND_GLOBAL.LOGIN_ID;
3535 --l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
3536 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
3537
3538 select wip_job_schedule_interface_s.nextval
3539 into x_groupID
3540 from dual;
3541
3542
3543 END IF;
3544
3545 -- Fixed bug 5346922
3546 -- Removed the decode for supply type
3547 l_stmt_num := 280;
3548 insert into wip_job_schedule_interface
3549 (last_update_date,
3550 last_updated_by,
3551 creation_date,
3552 created_by,
3553 last_update_login,
3554 request_id,
3555 program_id,
3556 program_application_id,
3557 program_update_date,
3558 group_id,
3559 source_code,
3560 process_phase,
3561 process_status,
3562 organization_id,
3563 load_type,
3564 status_type,
3565 last_unit_completion_date,
3566 primary_item_id,
3567 wip_supply_type,
3568 class_code,
3569 firm_planned_flag,
3570 start_quantity,
3571 bom_revision_date,
3572 routing_revision_date,
3573 project_id,
3574 task_id,
3575 due_date,
3576 bom_revision,
3577 scheduling_method, --inserted ml_manual inorder to stop finite scheduler run
3578 first_unit_start_date --enter first unit start date if finite scheduler is turned on bugfix#2739590
3579
3580 )
3581 select SYSDATE,
3582 p_user_id,--l_user_id,
3583 SYSDATE,
3584 p_user_id,--l_user_id,
3585 null, --l_login_id,
3586 null,
3587 null,--35740,
3588 null,--706,
3589 SYSDATE,
3590 x_groupID,
3591 'WICDOL',
3592 WIP_CONSTANTS.ML_VALIDATION,
3593 WIP_CONSTANTS.PENDING, -- process_status
3594 l_ship_org, -- organization id
3595 WIP_CONSTANTS.CREATE_JOB, --Load_Type
3596 nvl(p_status_type, WIP_CONSTANTS.UNRELEASED), -- Status_Type
3597 l_mlsupply_items(l_index).job_completion_date, -- Date Completed
3598 l_mlsupply_items(l_index).item_id, --Primary_Item_Id
3599 WIP_CONSTANTS.BASED_ON_BOM, -- Wip_Supply_Type
3600 decode(p_class_code, null, null
3601 , p_class_code), --Accouting Class
3602 2, --Firm_Planned_Flag
3603 l_mlsupply_items(l_index).needed_item_qty,
3604 trunc(greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
3605 'MI')+1/(60*24), --BOM_Revision_Date
3606 --Bugfix 14157494
3607 decode(l_routing_count_sa, 0, null, greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE)),
3608 --greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
3609 --Routing_Revision_Date
3610 --bugfix 3418102
3611 decode(l_mlsupply_items(l_index).pegging_flag,'I',l_project_id,'X',l_project_id, null),
3612 decode(l_mlsupply_items(l_index).pegging_flag,'I',l_task_id,'X',l_task_id, null),
3613 --end bugfix 3418102
3614 l_mlsupply_items(l_index).job_completion_date,
3615 BOM_REVISIONS.get_item_revision_fn
3616 ( 'ALL',
3617 'ALL',
3618 l_ship_org,
3619 l_mlsupply_items(l_index).item_id,
3620 (trunc (greatest(nvl(cal.calendar_date,SYSDATE),
3621 SYSDATE),'MI')+1/(60*24) )
3622 ),
3623 decode(nvl(wp.use_finite_scheduler,2), 1,
3624 WIP_CONSTANTS.ML_MANUAL,
3625 null),
3626 decode(nvl(wp.use_finite_scheduler,2), 1,
3627 l_mlsupply_items(l_index).job_start_date,
3628 null)
3629 from bom_calendar_dates cal,
3630 mtl_parameters mp,
3631 wip_parameters wp,
3632 mtl_system_items msi
3633 where mp.organization_id = l_ship_org
3634 and wp.organization_id = mp.organization_id
3635 and msi.organization_id = l_ship_org
3636 and msi.inventory_item_id = l_mlsupply_items(l_index).item_id --inventory item id
3637 and cal.calendar_code = mp.calendar_code
3638 and cal.exception_set_id = mp.calendar_exception_set_id
3639 and cal.seq_num =
3640 (select greatest(1, (cal2.prior_seq_num -
3641 (ceil(nvl(msi.fixed_lead_time,0) +
3642 nvl(msi.variable_lead_time,0) *
3643 l_mlsupply_items(l_index).needed_item_qty --bugfix 2074290: this is in primary uom
3644 ))))
3645 from bom_calendar_dates cal2
3646 where cal2.calendar_code = mp.calendar_code
3647 and cal2.exception_set_id =
3648 mp.calendar_exception_set_id
3649 and cal2.calendar_date =
3650 trunc(l_mlsupply_items(l_index).job_completion_date)
3651 );
3652
3653 if (SQL%ROWCOUNT > 0) then
3654 IF PG_DEBUG <> 0 THEN
3655 oe_debug_pub.add('create_subassembly_jobs: ' || 'Number of Rows Inserted in WJSI for children : ' || to_char(SQL%ROWCOUNT));
3656
3657 oe_debug_pub.add('create_subassembly_jobs: ' || 'GROUP ID Inserted in WJSI for children : ' || x_groupID);
3658 END IF;
3659
3660 end if;
3661
3662
3663
3664
3665 END IF;--end of check for top most parent type
3666
3667
3668
3669
3670 END IF; --discrete routing , flow not checked as flow supply created during completion date calculation
3671
3672 -- rkaza. 05/05/2005. Removed IR from here. Do nothing only
3673 -- for multiple sources.
3674 ELSIF (l_mlsupply_items(l_index).source_type = 66) THEN
3675 -- 66 = multiple sources
3676 IF PG_DEBUG <> 0 THEN
3677 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');
3678 END IF;
3679
3680 END IF; --source_type
3681
3682
3683
3684 EXIT WHEN l_index = l_mlsupply_items.LAST;
3685 l_index := l_mlsupply_items.NEXT(l_index);
3686
3687
3688
3689 END LOOP;
3690
3691 IF ( l_mlsupply_items(1).cfm_routing_flag = 2 OR
3692 l_mlsupply_items(1).cfm_routing_flag = -99 ) THEN --top most parent is discrete
3693 null;
3694
3695
3696
3697 ELSIF ( l_mlsupply_items(1).cfm_routing_flag = 1) THEN --top most paernt is flow
3698
3699 --bugfix 2755695
3700 IF l_sub_level_buy_item = 'Y' THEN
3701
3702 l_token(1).token_name := 'IMPORT_SOURCE_CODE';
3703 l_token(1).token_value := 'CTO_LOWER LEVEL';
3704
3705 cto_msg_pub.cto_message('BOM', 'CTO_SUB_LEVEL_BUY_ITEMS',l_token);
3706
3707
3708 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
3709
3710
3711
3712
3713 END IF;--sublevel buy item bugfix 2755695
3714
3715
3716 IF ( l_discrete_under_flow = 'Y') THEN
3717 l_stmt_num := 300;
3718
3719
3720
3721 l_requestId := fnd_request.submit_request('WIP',
3722 'WICMLP',
3723 null,
3724 null,
3725 false,
3726 to_char(x_groupID),
3727 to_char(WIP_CONSTANTS.ATO),
3728 to_char(WIP_CONSTANTS.NO));
3729
3730
3731
3732 if(l_requestId = 0) then --conc. req not spawned
3733 x_retVal := FND_API.G_RET_STS_ERROR;
3734 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
3735 fnd_message.set_token('ERROR_TEXT', 'WICMLP');
3736 x_errMsg := fnd_message.get;
3737 ROLLBACK TO REBUILD;
3738 else
3739 IF PG_DEBUG <> 0 THEN
3740 oe_debug_pub.add('create_subassembly_jobs: ' || 'REQUEST ID Inserted in WJSI for children : ' || l_requestId);
3741 cto_wip_workflow_api_pk.cto_debug ('Requets id is=> ', l_requestId);
3742
3743 l_token(1).token_name := 'REQUEST_ID';
3744 l_token(1).token_value := l_requestId;
3745
3746
3747
3748 cto_msg_pub.cto_message('BOM', 'CTO_SUB_LEVEL_DISCRETE_REQ',l_token); --bugfix 2755695
3749
3750
3751 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
3752
3753 END IF;
3754 commit;
3755 end if;
3756
3757 END IF ; --lauch wip mass load
3758
3759
3760 END IF; --top most paernt type
3761
3762 -- Bug 16202914
3763 IF PG_DEBUG <> 0 THEN
3764 get_mlsupply_details( l_return_status,
3765 l_error_messsage,
3766 l_msg_name );
3767 END IF;
3768
3769
3770
3771 EXCEPTION
3772 when FND_API.G_EXC_ERROR then
3773
3774 x_return_status := FND_API.G_RET_STS_ERROR;
3775 --Bugfix 8913125
3776 x_error_message := to_char(l_stmt_num)|| ': ' || substrb(sqlerrm,1,50);
3777
3778 IF PG_DEBUG <> 0 THEN
3779 oe_debug_pub.add('create_subassembly_jobs: ' || 'CTOSUBSB.create_sub_assembly_jobs expected excpn: ' || x_error_message,1);
3780 END IF;
3781
3782
3783 when FND_API.G_EXC_UNEXPECTED_ERROR then
3784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3785 --Bugfix 8913125
3786 x_error_message := to_char(l_stmt_num)|| ': ' || substrb(sqlerrm,1,50);
3787
3788 IF PG_DEBUG <> 0 THEN
3789 oe_debug_pub.add('create_subassembly_jobs: ' || 'CTOSUBSB.create_sub_assembly_jobs UN expected excpn: ' || x_error_message,1);
3790 END IF;
3791
3792
3793 when OTHERS then
3794 x_return_status := FND_API.G_RET_STS_ERROR;
3795 --Bugfix 8913125
3796 x_error_message := to_char(l_stmt_num)|| ': ' || substrb(sqlerrm,1,50);
3797
3798 IF PG_DEBUG <> 0 THEN
3799 oe_debug_pub.add('create_subassembly_jobs: ' || 'CTOSUBSB.create_sub_assembly_jobs OTHERS excpn: ' || x_error_message,1);
3800 END IF;
3801
3802 END create_subassembly_jobs;
3803
3804 --4645636
3805 Procedure process_phantoms
3806 (
3807 pitems_table in out nocopy t_item_details,
3808 p_organization_id in number,
3809 x_return_status out NOCOPY varchar2,
3810 x_error_message out NOCOPY VARCHAR2, -- bytes to hold msg */
3811 x_message_name out NOCOPY VARCHAR2 --30 bytes to hold name */
3812 )
3813 is
3814
3815 l_index number;
3816 m_index number;
3817 l_phantom_idx number;
3818 l_inherit_phantom_op_seq number;
3819 l_actual_parent_idx number;
3820 l_parent_index number;
3821 x_min_op_seq_num number;
3822 l_cons_item_qty number;
3823 l_last_index number;
3824
3825 l_stmt_num number;
3826
3827 BEGIN
3828 oe_debug_pub.add('Inside process_phantoms',1);
3829 --replace phatom's parent idx with its first non-phatom parent idx in chain
3830 l_stmt_num :=10;
3831 l_index := 2;
3832 Loop
3833 IF pitems_table(l_index).wip_supply_type = 6 THEN
3834 l_phantom_idx := l_index;
3835 m_index := 3;
3836 --Loop
3837 For m_index in 3..pitems_table.last
3838 Loop
3839 IF pitems_table(m_index).parent_index = l_phantom_idx THEN
3840 pitems_table(m_index).actual_parent_idx := pitems_table(m_index).parent_index;
3841 --removing phatom from chain
3842 pitems_table(m_index).parent_index := pitems_table(l_phantom_idx).parent_index;
3843
3844 l_stmt_num := 20;
3845 update BOM_CTO_MLSUPPLY_MAIN_TEMP
3846 set actual_parent_index = pitems_table(m_index).actual_parent_idx,
3847 parent_index = pitems_table(m_index).parent_index
3848 where ITEM_INDEX = m_index;
3849
3850
3851 END IF;
3852
3853 --EXIT WHEN m_index = pitems_table.LAST;
3854 --m_index := m_index+1;
3855
3856 END LOOP;
3857 END IF;
3858
3859 EXIT WHEN l_index = pitems_table.LAST;
3860 l_index := l_index +1;
3861 END LOOP;
3862
3863 l_last_index := pitems_table.count;
3864
3865 IF PG_DEBUG = 5 THEN
3866 FOR i IN 1..l_last_index LOOP
3867 oe_debug_pub.add('index=>'||i||
3868 'item_id=>'||pitems_table(i).item_id||
3869 'parent_idx=>'||pitems_table(i).parent_index||
3870 'actual_parent_idx=>'||pitems_table(i).actual_parent_idx||
3871 'wip_supply_type=>'||pitems_table(i).wip_supply_type,5);
3872 END LOOP;
3873 END IF;
3874
3875
3876
3877
3878 --get inherit_op_sequence
3879 l_stmt_num := 30;
3880 select INHERIT_PHANTOM_OP_SEQ
3881 into l_inherit_phantom_op_seq
3882 from bom_parameters
3883 where organization_id = p_organization_id;
3884
3885 l_stmt_num :=40;
3886 IF l_inherit_phantom_op_seq = 1 THEN
3887 l_index := 2;
3888 Loop
3889 IF pitems_table(l_index).parent_index <> pitems_table(l_index).actual_parent_idx THEN --implies child of phantom
3890
3891 l_actual_parent_idx:= pitems_table(l_index).actual_parent_idx;
3892
3893 pitems_table(l_index).operation_seq_num := pitems_table(l_actual_parent_idx).operation_seq_num;
3894
3895 pitems_table(l_index).OPERATION_LEAD_TIME_PERCENT := pitems_table(l_actual_parent_idx).OPERATION_LEAD_TIME_PERCENT;
3896
3897 l_stmt_num := 50;
3898 update BOM_CTO_MLSUPPLY_MAIN_TEMP
3899 set operation_seq_num = pitems_table(l_index).operation_seq_num,
3900 OPERATION_LEAD_TIME_PERCENT = pitems_table(l_index).OPERATION_LEAD_TIME_PERCENT
3901 where ITEM_INDEX = l_index;
3902
3903 END IF;
3904
3905 EXIT WHEN l_index = pitems_table.LAST;
3906 l_index := l_index +1;
3907 END LOOP;
3908
3909 ELSE
3910 null;
3911 --(A)--leave op seq as it is
3912
3913 --(B)--need to get lead time offset % from parent
3914 l_index :=3;
3915 --Loop
3916 l_stmt_num := 60;
3917 For l_index in 3..pitems_table.last
3918 loop
3919 --EXIT WHEN l_index = pitems_table.LAST;
3920
3921
3922 IF pitems_table(l_index).parent_index <> pitems_table(l_index).actual_parent_idx THEN -- implies child of pH
3923 l_parent_index := pitems_table(l_index).parent_index;
3924
3925 IF PG_DEBUG <> 0 THEN
3926 oe_debug_pub.add('ENTERED IF BLOCK',5);
3927 oe_debug_pub.add('parent_item_id=>'||pitems_table(l_parent_index).item_id ,5);
3928 oe_debug_pub.add('item_id=>'||pitems_table(l_index).item_id ,5);
3929 oe_debug_pub.add('operation_seq_num=>'||pitems_table(l_index).operation_seq_num ,5);
3930 END IF;
3931
3932 BEGIN
3933 l_stmt_num := 70;
3934 select nvl(bos_p.OPERATION_LEAD_TIME_PERCENT,0)
3935 INTO pitems_table(l_index).operation_lead_time_percent
3936 from bom_operational_routings bor_p,--parent
3937 bom_operation_sequences bos_p
3938 where bor_p.assembly_item_id = pitems_table(l_parent_index).item_id
3939 and bor_p.organization_id = p_organization_id
3940 and bor_p.ALTERNATE_ROUTING_DESIGNATOR is null
3941 and bos_p.routing_sequence_id = bor_p.common_routing_sequence_id
3942 and bos_p.operation_seq_num = pitems_table(l_index).operation_seq_num
3943 and nvl( bos_p.operation_type,1)=1 ---consider event only for flm routing 5676839
3944 --Bugfix 12581339: Disabled operations should not be looked into.
3945 and bos_p.implementation_date is not null
3946 and bos_p.effectivity_date <= sysdate
3947 and nvl(bos_p.disable_date, sysdate + 1) > sysdate;
3948 Exception
3949 WHEN no_data_found then
3950 pitems_table(l_index).operation_lead_time_percent := 0;
3951 IF PG_DEBUG <> 0 THEN
3952 oe_debug_pub.add('lead_time=>'||pitems_table(l_index).operation_lead_time_percent,1);
3953 END IF;
3954 END;
3955
3956 IF PG_DEBUG <> 0 THEN
3957 oe_debug_pub.add('process_phantoms:lead_time=>'||pitems_table(l_index).operation_lead_time_percent,1);
3958 END IF;
3959
3960 l_stmt_num := 80;
3961 update BOM_CTO_MLSUPPLY_MAIN_TEMP
3962 set --operation_seq_num = pitems_table(l_index).operation_seq_num,
3963 OPERATION_LEAD_TIME_PERCENT = pitems_table(l_index).OPERATION_LEAD_TIME_PERCENT
3964 where ITEM_INDEX = l_index;
3965
3966 END IF;
3967 -- EXIT WHEN l_index = pitems_table.LAST;
3968 -- l_index := l_index +1 ;
3969 END LOOP;
3970
3971
3972 END IF;
3973
3974
3975 IF PG_DEBUG = 5 THEN
3976 FOR i IN 1..l_last_index LOOP
3977 oe_debug_pub.add('index=>'||i||
3978 'item_id=>'||pitems_table(i).item_id||
3979 'parent_idx=>'||pitems_table(i).parent_index||
3980 'actual_parent_idx=>'||pitems_table(i).actual_parent_idx||
3981 'wip_supply_type=>'||pitems_table(i).wip_supply_type||
3982 'LT_offset=>'||pitems_table(i).operation_lead_time_percent||
3983 'op_seq=>'||pitems_table(i).operation_seq_num,5);
3984 END LOOP;
3985 END IF;
3986
3987
3988
3989 --For MIN_OP_Seq_num calculations
3990 l_index :=2;
3991 LOOP
3992 l_parent_index := pitems_table(l_index).parent_index;
3993
3994 IF (nvl(pitems_table(l_index).wip_supply_type,1) <> 6 AND
3995 pitems_table(l_index).source_type <> 66 AND
3996 pitems_table(l_parent_index).cfm_routing_flag = 1 AND
3997 pitems_table(l_parent_index).needed_item_qty <> 0 AND
3998 (pitems_table(l_index).source_type = 3
3999 OR
4000 pitems_table(l_index).source_type = 1
4001 OR
4002 pitems_table(l_index).cfm_routing_flag <> 1
4003 )
4004 )
4005 THEN
4006 IF PG_DEBUG <> 0 THEN
4007 oe_debug_pub.add('ENTERED MIN OP SEQ BLOCK',5);
4008 END IF;
4009
4010 l_stmt_num := 90;
4011 select min(OPERATION_SEQ_NUM),sum(needed_item_qty)
4012 into x_min_op_seq_num,l_cons_item_qty
4013 FROM BOM_CTO_MLSUPPLY_MAIN_TEMP
4014 WHERE parent_index = pitems_table(l_index).parent_index
4015 AND item_id = pitems_table(l_index).item_id;
4016
4017 IF PG_DEBUG <> 0 THEN
4018 oe_debug_pub.add('item_id=>'||pitems_table(l_index).item_id,5);
4019 oe_debug_pub.add('x_min_op_seq_num=>'||x_min_op_seq_num,5);
4020 oe_debug_pub.add('l_cons_item_qty=>'||l_cons_item_qty,5);
4021 oe_debug_pub.add('parent_index=>'||pitems_table(l_index).parent_index,5);
4022 END IF;
4023
4024 l_stmt_num := 100;
4025 Update bom_cto_mlsupply_main_temp
4026 set needed_item_qty = 0
4027 where parent_index = pitems_table(l_index).parent_index
4028 AND item_id = pitems_table(l_index).item_id
4029 and Operation_seq_num <> x_min_op_seq_num;
4030
4031
4032 l_stmt_num := 110;
4033 Update bom_cto_mlsupply_main_temp
4034 set needed_item_qty = l_cons_item_qty
4035 where parent_index = pitems_table(l_index).parent_index
4036 AND item_id = pitems_table(l_index).item_id
4037 and Operation_seq_num = x_min_op_seq_num;
4038
4039
4040
4041
4042 END IF;
4043 EXIT WHEN l_index = pitems_table.LAST;
4044 l_index := l_index+1;
4045 END LOOP;
4046
4047 IF PG_DEBUG = 5 THEN
4048 FOR i IN 1..l_last_index LOOP
4049 oe_debug_pub.add('index=>'||i||
4050 'item_id=>'||pitems_table(i).item_id||
4051 'parent_idx=>'||pitems_table(i).parent_index||
4052 'actual_parent_idx=>'||pitems_table(i).actual_parent_idx||
4053 'wip_supply_type=>'||pitems_table(i).wip_supply_type||
4054 'LT_offset=>'||pitems_table(i).operation_lead_time_percent||
4055 'op_seq=>'||pitems_table(i).operation_seq_num,5);
4056 END LOOP;
4057 END IF;
4058
4059
4060 EXCEPTION
4061 when FND_API.G_EXC_ERROR then
4062
4063 x_return_status := FND_API.G_RET_STS_ERROR;
4064 x_error_message := 'CTOSUBSB.process_phantoms expected excpn: ' || to_char(l_stmt_num)|| ':' ||
4065 substrb(sqlerrm,1,100);
4066
4067
4068 IF PG_DEBUG <> 0 THEN
4069 IF PG_DEBUG <> 0 THEN
4070 oe_debug_pub.add('process_phantoms: ' || 'CTOSUBSB.create_flow_subassembly expected excpn: ' || x_error_message,1);
4071 END IF;
4072 END IF;
4073
4074
4075 when FND_API.G_EXC_UNEXPECTED_ERROR then
4076 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4077 x_error_message := 'CTOSUBSB.process_phantoms UN expected excpn: ' || to_char(l_stmt_num)|| ':' ||
4078 substrb(sqlerrm,1,100);
4079
4080
4081 IF PG_DEBUG <> 0 THEN
4082 IF PG_DEBUG <> 0 THEN
4083 oe_debug_pub.add('process_phantoms: ' || 'CTOSUBSB.create_flow_subassembly UN expected excpn: ' || x_error_message,1);
4084 END IF;
4085 END IF;
4086
4087
4088
4089 when OTHERS then
4090 x_return_status := FND_API.G_RET_STS_ERROR;
4091 x_error_message := 'CTOSUBSB.process_phantoms OTHERS excpn: ' || to_char(l_stmt_num)|| ':' ||
4092 substrb(sqlerrm,1,100);
4093
4094 IF PG_DEBUG <> 0 THEN
4095 IF PG_DEBUG <> 0 THEN
4096 oe_debug_pub.add('process_phantoms: ' || 'CTOSUBSB.create_flow_subassembly OTHERS excpn: ' || x_error_message,1);
4097 END IF;
4098 END IF;
4099
4100
4101
4102
4103 END process_phantoms;
4104
4105
4106
4107 end CTO_SUBASSEMBLY_SUP_PK;