4 PROCEDURE Cumulative_Yield(
1 PACKAGE BODY BOMCUMYD AS
2 /* $Header: BOMCMYLB.pls 120.3 2010/06/28 17:49:25 umajumde ship $ */
3
5 ERRBUF IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
6 RETCODE IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
7 Current_org_id IN NUMBER,
8 Scope IN NUMBER DEFAULT 1,
9 Flag_Value IN VARCHAR2,
10 Item_Id IN NUMBER,
11 Operation_Type IN NUMBER DEFAULT 1,
12 Update_Events IN NUMBER DEFAULT 1,
13 Item_Low IN VARCHAR2,
14 Item_High IN VARCHAR2
15 ) IS
16 Item_Name VARCHAR2(40) ;
17 Range_Item_Id NUMBER := 0 ;
18 ROUTING_SEQ_ID NUMBER := 1 ;
19 conc_status BOOLEAN ;
20 Current_Error_Code VARCHAR2(20) := NULL;
21 l_routing_sequence_id number ; --bug 9676295
22
23 Item_Range_Not_Specified EXCEPTION;
24 Item_Specific_Not_Specified EXCEPTION;
25
26 CURSOR Item_Range_Both is
27 SELECT inventory_item_id,concatenated_segments
28 FROM mtl_system_items_b_kfv
29 WHERE concatenated_segments BETWEEN item_low and item_high
30 AND organization_id = current_org_id;
31
32 CURSOR Item_Range_Low is
33 SELECT inventory_item_id,concatenated_segments
34 FROM mtl_system_items_b_kfv
35 WHERE concatenated_segments >= item_low
36 AND organization_id = current_org_id;
37
38 CURSOR Item_Range_High is
39 SELECT inventory_item_id,concatenated_segments
40 FROM mtl_system_items_b_kfv
41 WHERE concatenated_segments <= item_high
42 AND organization_id = current_org_id;
43
44 CURSOR Cur_Rtg(Item_Id NUMBER) IS
45 SELECT routing_sequence_id,alternate_routing_designator
46 FROM bom_operational_routings
47 WHERE assembly_item_id = Item_Id
48 AND cfm_routing_flag = 3
49 AND organization_id = current_org_id;
50
51 BEGIN
52 /* Print the list of parameters */
53 FND_FILE.PUT_LINE(FND_FILE.LOG,'******************************************') ;
54 FND_FILE.PUT_LINE( FND_FILE.LOG,'SCOPE='||to_char(scope));
55 FND_FILE.PUT_LINE( FND_FILE.LOG,'CURRENT_ORG_ID='||to_char(current_org_id));
56 FND_FILE.PUT_LINE( FND_FILE.LOG,'ITEM_ID='||to_char(item_id));
57 FND_FILE.PUT_LINE( FND_FILE.LOG,'ITEM_FROM='||item_low);
58 FND_FILE.PUT_LINE( FND_FILE.LOG,'ITEM_TO='||item_high);
59 FND_FILE.PUT_LINE( FND_FILE.LOG,'OPERATION_TYPE='||to_char(operation_type));
60 FND_FILE.PUT_LINE( FND_FILE.LOG,'UPDATE_EVENTS='||to_char(update_events));
61 FND_FILE.PUT_LINE(FND_FILE.LOG,'******************************************') ;
62 /* Make sure the right set of parameter are passed */
63 IF (scope = 2) AND ((item_low IS NULL) and (item_high is NULL)) THEN
64 raise Item_Range_Not_Specified ;
65 END IF ;
66
67 IF ((scope = 1) AND (item_id IS NULL)) THEN
68 raise Item_Specific_Not_Specified ;
69 END IF ;
70
71
72 /* Open the cursor Item_Range for getting the Item ID's */
73 If scope = 2 Then
74 If ((Item_low is NOT NULL) and (Item_High is NOT NULL)) then
75 Open Item_Range_Both;
76 Loop
77 Fetch Item_Range_Both
78 Into Range_Item_Id,Item_Name;
79
80 EXIT WHEN Item_Range_Both%NOTFOUND;
81
82 /* Check if routing exists ,if it does then call the function to calculate cumulative yield */
83
84 For C1 in Cur_Rtg(Range_Item_Id)
85 Loop
86 if (C1.ALTERNATE_ROUTING_DESIGNATOR is not NULL) then
87 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processing routing for item....' || Item_Name || ' with Alternate... ' || C1.ALTERNATE_ROUTING_DESIGNATOR);
88 else
89 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processing routing for item....' || Item_Name);
90 end if;
91 --changes for bug 9676295 begin
92 begin
93 select common_routing_sequence_id into l_routing_sequence_id from bom_operational_routings
94 where common_routing_sequence_id <> routing_sequence_id and routing_sequence_id = C1.routing_sequence_id;
95 exception
96 when no_data_found then
97 l_routing_sequence_id := C1.routing_sequence_id;
98 end;
99 BOM_CALC_CYNP.calc_cynp(l_routing_sequence_id,1,1);
100 --changes for bug 9676295 end
101 End Loop;
102 End Loop;
103 ElsIf (Item_Low is NOT NULL) then
104 Open Item_Range_Low;
105 Loop
106 Fetch Item_Range_Low
107 Into Range_Item_Id,Item_Name;
108
109 EXIT WHEN Item_Range_Low%NOTFOUND;
110
111 /* Check if routing exists ,if it does then call the function to calculate cumulative yield */
112
113 For C1 in Cur_Rtg(Range_Item_Id)
114 Loop
115 if (C1.ALTERNATE_ROUTING_DESIGNATOR is not NULL) then
116 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processing routing for item....' || Item_Name || ' with Alternate... ' || C1.ALTERNATE_ROUTING_DESIGNATOR);
117 else
118 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processing routing for item....' || Item_Name);
119 end if;
120 --changes for bug 9676295 begin
121 begin
122 select common_routing_sequence_id into l_routing_sequence_id from bom_operational_routings
123 where common_routing_sequence_id <> routing_sequence_id and routing_sequence_id = C1.routing_sequence_id;
124 exception
125 when no_data_found then
126 l_routing_sequence_id := C1.routing_sequence_id;
127 end;
128 BOM_CALC_CYNP.calc_cynp(l_routing_sequence_id,1,1);
132 End Loop;
129 --changes for bug 9676295 end
130
131 End Loop;
133 ElsIf (Item_High is NOT NULL) then
134 Open Item_Range_High;
135 Loop
136 Fetch Item_Range_High
137 Into Range_Item_Id,Item_Name;
138
139 EXIT WHEN Item_Range_High%NOTFOUND;
140
141 /* Check if routing exists ,if it does then call the function to calculate cumulative yield */
142
143 For C1 in Cur_Rtg(Range_Item_Id)
144 Loop
145 if (C1.ALTERNATE_ROUTING_DESIGNATOR is not NULL) then
146 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processing routing for item....' || Item_Name || ' with Alternate... ' || C1.ALTERNATE_ROUTING_DESIGNATOR);
147 else
148 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processing routing for item....' || Item_Name);
149 end if;
150 --changes for bug 9676295 begin
151 begin
152 select common_routing_sequence_id into l_routing_sequence_id from bom_operational_routings
153 where common_routing_sequence_id <> routing_sequence_id and routing_sequence_id = C1.routing_sequence_id;
154 exception
155 when no_data_found then
156 l_routing_sequence_id := C1.routing_sequence_id;
157 end;
158 BOM_CALC_CYNP.calc_cynp(l_routing_sequence_id,1,1);
159 --changes for bug 9676295 end
160
161 End Loop;
162 End Loop;
163 End If;
164 Else
165 SELECT concatenated_segments
166 INTO Item_Name
167 FROM mtl_system_items_b_kfv
168 WHERE inventory_item_id = Item_Id
169 AND organization_id = Current_Org_Id;
170
171 For C1 in Cur_Rtg(Item_Id)
172 Loop
173 if (C1.ALTERNATE_ROUTING_DESIGNATOR is not NULL) then
174 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processing routing for item....' || Item_Name || ' with Alternate... ' || C1.ALTERNATE_ROUTING_DESIGNATOR);
175 else
176 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processing routing for item....' || Item_Name);
177 end if;
178 --changes for bug 9676295 begin
179 begin
180 select common_routing_sequence_id into l_routing_sequence_id from bom_operational_routings
181 where common_routing_sequence_id <> routing_sequence_id and routing_sequence_id = C1.routing_sequence_id;
182 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processing common routing for item....' || Item_Name || ' with common routing sequence id ' || l_routing_sequence_id);
183 exception
184 when no_data_found then
185 l_routing_sequence_id := C1.routing_sequence_id;
186 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processing (not commoned) routing for item....' || Item_Name || ' with routing sequence id ' || l_routing_sequence_id);
187 end;
188 BOM_CALC_CYNP.calc_cynp(l_routing_sequence_id,1,1);
189 --changes for bug 9676295 end
190
191 End Loop;
192 end if;
193 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processing ends for Cumulative Yield...');
194 Exception
195 WHEN Item_Range_Not_Specified THEN
196 FND_FILE.PUT_LINE( FND_FILE.LOG,'ITEM RANGE NOT SPECIFIED');
197 RETCODE := 2;
198 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
199 WHEN Item_Specific_Not_Specified THEN
200 FND_FILE.PUT_LINE( FND_FILE.LOG,'Specific Item Value not Specified');
201 RETCODE := 2;
202 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
203 WHEN OTHERS THEN
204 FND_FILE.PUT_LINE(FND_FILE.LOG,'Others '||SQLCODE || ':'||SQLERRM) ;
205 RETCODE := 2;
206 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
207 End Cumulative_Yield;
208
209 PROCEDURE flm_calc_util(
210 org_id IN NUMBER,
211 item_id IN NUMBER,
212 opr_type IN NUMBER,
213 rtg_seq_id IN NUMBER,
214 Flow_Line_Id IN NUMBER
215 ) IS
216 l_tpct NUMBER;
217 v_Lead_Time_Profile_Val varchar2(50); --Variable to define profile for Lead Time Basis
218 l_line_hours NUMBER;
219 l_line_Seconds NUMBER;
220 BEGIN
221 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside calculation');
222
223 BOM_CALC_CYNP.calc_cynp(rtg_seq_id, opr_type, 1);
224 BOM_CALC_TPCT.calculate_tpct(rtg_seq_id, to_char(opr_type));
225 BOM_CALC_OP_TIMES_PK.calculate_operation_times(org_id, rtg_seq_id);
226
227 /*** Updation of TPCT in the Master items Table *****/
228 SELECT TOTAL_PRODUCT_CYCLE_TIME into l_tpct FROM
229 BOM_OPERATIONAL_ROUTINGS bor
230 WHERE bor.ROUTING_SEQUENCE_ID = rtg_seq_id
231 AND bor.ORGANIZATION_ID = org_id
232 AND bor.ASSEMBLY_ITEM_ID = item_id;
233
234 -- Bug 4632067. Lead time should be in days, hence dividing by 24
235 -- Bug 5869157 The profile option "BOM:LEAD TIME CALCULATION BASIS" will control whether the lead time is calculated
236 -- by dividing by 24 or by line hours
237 /** Get the value of profile option BOM:LEAD TIME CALCULATION BASIS **/
238 fnd_profile.get('BOM:LEAD_TIME_CALCULATION_BASIS', v_Lead_Time_Profile_Val);
239 IF Flow_Line_Id is NULL THEN /*** Line is not specified ***/
240 UPDATE MTL_SYSTEM_ITEMS
241 SET FULL_LEAD_TIME = l_tpct/24,
242 FIXED_LEAD_TIME = l_tpct/24,
243 PROGRAM_UPDATE_DATE = SYSDATE
244 WHERE INVENTORY_ITEM_ID = item_id
245 AND ORGANIZATION_ID = org_id;
246 ELSE
247 IF v_Lead_Time_Profile_Val = 'LHR' THEN /**--Prfoile option is Line hrs-- **/
248 /**Get the line hours using LINE_ID**/
252
249 Select (STOP_TIME - START_TIME) into l_line_Seconds
250 from wip_lines where line_id = Flow_Line_Id
251 and ORGANIZATION_ID = org_id;
253 l_line_hours := l_line_Seconds/3600;
254
255 /**Update using Line hours**/
256 UPDATE MTL_SYSTEM_ITEMS
257 SET FULL_LEAD_TIME = l_tpct/l_line_hours,
258 FIXED_LEAD_TIME = l_tpct/l_line_hours,
259 PROGRAM_UPDATE_DATE = SYSDATE
260 WHERE INVENTORY_ITEM_ID = item_id
261 AND ORGANIZATION_ID = org_id;
262 ELSE
263 UPDATE MTL_SYSTEM_ITEMS
264 SET FULL_LEAD_TIME = l_tpct/24,
265 FIXED_LEAD_TIME = l_tpct/24,
266 PROGRAM_UPDATE_DATE = SYSDATE
267 WHERE INVENTORY_ITEM_ID = item_id
268 AND ORGANIZATION_ID = org_id;
269 END IF;
270 END IF;
271
272 END flm_calc_util;
273
274 FUNCTION check_op_type(
275 rtg_seq_id IN NUMBER,
276 op_type IN NUMBER
277 ) RETURN NUMBER IS
278
279 CURSOR process_chk is
280 SELECT null from bom_operation_sequences
281 WHERE routing_sequence_id = rtg_seq_id
282 and process_op_seq_id is not null;
283
284 CURSOR line_chk is
285 SELECT null from bom_operation_sequences
286 WHERE routing_sequence_id = rtg_seq_id
287 and line_op_seq_id is not null;
288
289 temp NUMBER := 1;
290 BEGIN
291 IF op_type = 2 THEN
292 open process_chk;
293 fetch process_chk into temp;
294 IF process_chk%NOTFOUND THEN
295 temp := 1;
296 ELSE
297 temp := 2;
298 END IF;
299 close process_chk;
300 ELSIF op_type = 3 THEN
301 open line_chk;
302 fetch line_chk into temp;
303 IF line_chk%NOTFOUND THEN
304 temp := 1;
305 ELSE
306 temp := 3;
307 END IF;
308 close line_chk;
309 END IF;
310 return temp;
311 END check_op_type;
312
313 PROCEDURE print_debug(
314 item_id IN NUMBER,
315 alt IN VARCHAR2,
316 rtg_seq_id IN NUMBER
317 )
318 IS
319 BEGIN
320 if (alt is not NULL) then
321 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing routing for item....'
322 || to_char(item_id) || ' with Alternate... '
323 || alt || 'Rtg_seq_id = '
324 || to_char(rtg_seq_id));
325 else
326 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing routing for item....'
327 || to_char(item_id) || 'Rtg_seq_id '
328 || to_char(rtg_seq_id));
329 end if;
330 END print_debug;
331
332 PROCEDURE Flow_Batch_Calc(
333 ERRBUF IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
334 RETCODE IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
335 Current_org_id IN NUMBER,
336 Scope IN NUMBER DEFAULT 1,
337 Flag_Value IN VARCHAR2,
338 Item_Id IN NUMBER,
339 Line_Id IN NUMBER,
340 oper_type IN NUMBER DEFAULT 3,
341 Item_Low IN VARCHAR2,
342 Item_High IN VARCHAR2,
343 Operation_Name IN VARCHAR2,
344 Event_Name IN VARCHAR2
345 ) IS
346 Item_Name VARCHAR2(40) ;
347 Current_Error_Code VARCHAR2(20) := NULL;
348 conc_status BOOLEAN ;
349 Operation_Code VARCHAR2(4);
350
351 TYPE item_id_tab_type IS TABLE of
352 MTL_SYSTEM_ITEMS_B_KFV.inventory_item_id%TYPE
353 INDEX by BINARY_INTEGER;
354
355 TYPE item_name_tab_type IS TABLE of
356 MTL_SYSTEM_ITEMS_B_KFV.concatenated_segments%TYPE
357 INDEX by BINARY_INTEGER;
358
359 item_tab item_id_tab_type;
360 item_name_tab item_name_tab_type;
361
362 i NUMBER := 1;
363 l_all NUMBER := 0;
364 l_tpct NUMBER;
365 l_op_type NUMBER := 3;
366 l_op_flag NUMBER;
367 l_found BOOLEAN := false;
368
369 No_Op_On_Line EXCEPTION;
370 No_Event_On_Line EXCEPTION;
371
372 CURSOR Item_Range_Both is
373 SELECT inventory_item_id,concatenated_segments
374 FROM mtl_system_items_b_kfv
375 WHERE concatenated_segments BETWEEN item_low and item_high
376 AND organization_id = current_org_id;
377
378 CURSOR Item_Range_Low is
379 SELECT inventory_item_id,concatenated_segments
380 FROM mtl_system_items_b_kfv
381 WHERE concatenated_segments >= item_low
382 AND organization_id = current_org_id;
383
384 CURSOR Item_Range_High is
385 SELECT inventory_item_id,concatenated_segments
386 FROM mtl_system_items_b_kfv
387 WHERE concatenated_segments <= item_high
388 AND organization_id = current_org_id;
389
390 CURSOR Cur_Line_Flow_Rtg (v_line NUMBER) is
391 SELECT routing_sequence_id, alternate_routing_designator,
392 assembly_item_id
393 FROM BOM_OPERATIONAL_ROUTINGS bor
394 WHERE bor.ORGANIZATION_ID = current_org_id
395 AND bor.CFM_ROUTING_FLAG = 1
396 AND bor.LINE_ID = v_line;
397
398 CURSOR Cur_Line_Op_Flow_Rtg (v_line NUMBER, v_operation_code VARCHAR2) is
399 SELECT DISTINCT bor.routing_sequence_id, bor.alternate_routing_designator,
400 bor.assembly_item_id
401 FROM BOM_OPERATIONAL_ROUTINGS bor,
402 BOM_OPERATION_SEQUENCES bos,
403 BOM_STANDARD_OPERATIONS bso
404 WHERE bor.ORGANIZATION_ID = current_org_id
405 AND bor.CFM_ROUTING_FLAG = 1
406 AND bor.LINE_ID = v_line
407 AND bso.OPERATION_CODE = v_operation_code
408 AND bos.STANDARD_OPERATION_ID = bso.STANDARD_OPERATION_ID
409 AND bor.ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID
410 AND bos.OPERATION_TYPE = l_op_type;
411
412 CURSOR Cur_Flow_Rtg is
416 WHERE bor.ORGANIZATION_ID = current_org_id
413 SELECT routing_sequence_id, alternate_routing_designator,
414 assembly_item_id
415 FROM BOM_OPERATIONAL_ROUTINGS bor
417 AND bor.CFM_ROUTING_FLAG = 1;
418
419 CURSOR Cur_Item_Line_Flow_Rtg (Item_id NUMBER, v_line NUMBER) is
420 SELECT routing_sequence_id, alternate_routing_designator
421 FROM BOM_OPERATIONAL_ROUTINGS bor
422 WHERE bor.ORGANIZATION_ID = current_org_id
423 AND bor.ASSEMBLY_ITEM_ID = Item_id
424 AND bor.CFM_ROUTING_FLAG = 1
425 AND bor.LINE_ID = v_line;
426
427 CURSOR Cur_Item_Line_Op_Flow_Rtg (Item_id NUMBER, v_line NUMBER, v_operation_code VARCHAR2) is
428 SELECT DISTINCT bor.routing_sequence_id, bor.alternate_routing_designator
429 FROM BOM_OPERATIONAL_ROUTINGS bor,
430 BOM_OPERATION_SEQUENCES bos,
431 BOM_STANDARD_OPERATIONS bso
432 WHERE bor.ORGANIZATION_ID = current_org_id
433 AND bor.ASSEMBLY_ITEM_ID = Item_id
434 AND bor.CFM_ROUTING_FLAG = 1
435 AND bor.LINE_ID = v_line
436 AND bso.OPERATION_CODE = v_operation_code
437 AND bos.STANDARD_OPERATION_ID = bso.STANDARD_OPERATION_ID
438 AND bor.ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID
439 AND bos.OPERATION_TYPE = l_op_type;
440
441 CURSOR Cur_Item_Flow_Rtg (Item_id NUMBER) is
442 SELECT routing_sequence_id, alternate_routing_designator
443 FROM BOM_OPERATIONAL_ROUTINGS bor
444 WHERE bor.ORGANIZATION_ID = current_org_id
445 AND bor.ASSEMBLY_ITEM_ID = Item_id
446 AND bor.CFM_ROUTING_FLAG = 1;
447
448
449 BEGIN
450 /* Print the list of parameters */
451
452 FND_FILE.PUT_LINE(FND_FILE.LOG,'******************************************') ;
453 FND_FILE.PUT_LINE(FND_FILE.LOG,'SCOPE='||to_char(scope));
454 FND_FILE.PUT_LINE(FND_FILE.LOG,'CURRENT_ORG_ID='||to_char(current_org_id));
455 FND_FILE.PUT_LINE(FND_FILE.LOG,'ITEM_ID='||to_char(item_id));
456 FND_FILE.PUT_LINE(FND_FILE.LOG,'ITEM_FROM='||item_low);
457 FND_FILE.PUT_LINE(FND_FILE.LOG,'ITEM_TO='||item_high);
458 FND_FILE.PUT_LINE(FND_FILE.LOG,'OPERATION_TYPE='||to_char(oper_type));
459 FND_FILE.PUT_LINE(FND_FILE.LOG,'LINE_ID='||to_char(Line_Id));
460 FND_FILE.PUT_LINE(FND_FILE.LOG,'OPERATION_Name='||Operation_Name);
461 FND_FILE.PUT_LINE(FND_FILE.LOG,'Event_Name='||Event_Name);
462 FND_FILE.PUT_LINE(FND_FILE.LOG,'******************************************') ;
463
464 i := 1;
465 item_tab.DELETE;
466
467 IF scope = 1 THEN
468 IF item_id is NULL THEN
469 l_all := 1;
470 ELSE
471 item_tab(1) := item_id;
472 END IF;
473 ELSIF scope = 2 THEN
474 IF (item_high is NULL) and (item_low is NULL) THEN
475 l_all := 1;
476 ELSIF (item_high is NOT NULL) AND (item_low is NULL) THEN
477 FOR C1 in item_range_high
478 LOOP
479 item_tab(i) := C1.inventory_item_id;
480 item_name_tab(i) := C1.concatenated_segments;
481 i := i + 1;
482 END LOOP;
483 ELSIF (item_low is NOT NULL) AND (item_high is NULL) THEN
484 FOR C1 in item_range_low
485 LOOP
486 item_tab(i) := C1.inventory_item_id;
487 item_name_tab(i) := C1.concatenated_segments;
488 i := i + 1;
489 END LOOP;
490 ELSE
491 FOR C1 in item_range_both
492 LOOP
493 item_tab(i) := C1.inventory_item_id;
494 item_name_tab(i) := C1.concatenated_segments;
495 i := i + 1;
496 END LOOP;
497 END IF;
498 END IF;
499
500 -- IF Operation_Name is NULL THEN -- Event based filtering
501 -- Changed for the bug found during testing
502 IF Operation_name is NULL AND Event_Name is NOT NULL THEN
503 Operation_code := Event_Name;
504 l_op_type := 1;
505 ELSE
506 l_op_type := oper_type;
507 Operation_Code := Operation_Name;
508 END IF;
509
510 IF l_all = 0 THEN
511 FND_FILE.PUT_LINE(FND_FILE.LOG,'Item/Item range specified, op type='||to_char(l_op_type));
512 FOR i IN 1..item_tab.COUNT
513 LOOP
514 IF Line_id is NULL THEN /*** Line is not specified ***/
515 FND_FILE.PUT_LINE(FND_FILE.LOG,'No line is specified');
516 FOR C1 in Cur_Item_Flow_Rtg(item_tab(i))
517 Loop
518 print_debug(item_tab(i), C1.Alternate_Routing_Designator, C1.routing_sequence_id);
519 flm_calc_util(current_org_id, item_tab(i), l_op_type, C1.routing_sequence_id, Line_Id);
520 End Loop;
521 ELSIF Operation_code is NULL THEN /*** Only Line is specified ***/
522 FND_FILE.PUT_LINE(FND_FILE.LOG,'Only line is specified');
523 FOR C1 in Cur_Item_Line_Flow_Rtg(item_tab(i), Line_Id)
524 Loop
525 print_debug(item_tab(i), C1.Alternate_Routing_Designator, C1.routing_sequence_id);
526 flm_calc_util(current_org_id, item_tab(i), l_op_type, C1.routing_sequence_id, Line_Id);
527 End Loop;
528 ELSIF l_op_type > 1 THEN /*** Line and operation are specified ***/
529 FND_FILE.PUT_LINE(FND_FILE.LOG,'Line and operation is specified');
530 FOR C1 in Cur_Item_Line_Op_Flow_Rtg(item_tab(i), Line_id, Operation_Code)
531 Loop
532 print_debug(item_tab(i), C1.Alternate_Routing_Designator, C1.routing_sequence_id);
533 flm_calc_util(current_org_id, item_tab(i), l_op_type, C1.routing_sequence_id, Line_Id);
537 raise No_Op_On_Line;
534 l_found := true;
535 End Loop;
536 IF scope = 1 AND NOT l_found THEN
538 END IF;
539 ELSE /*** Line, operation_type and Event are specified ***/
540 FND_FILE.PUT_LINE(FND_FILE.LOG,'Line, operation and event specified');
541 FOR C1 in Cur_Item_Line_Op_Flow_Rtg(item_tab(i), Line_id, Operation_Code)
542 Loop
543 l_op_flag := check_op_type(C1.routing_sequence_id, oper_type);
544 IF l_op_flag <> 1 THEN
545 print_debug(item_tab(i), C1.Alternate_Routing_Designator, C1.routing_sequence_id);
546 flm_calc_util(current_org_id, item_tab(i), l_op_flag, C1.routing_sequence_id, Line_Id);
547 l_found := true;
548 END IF;
549 End Loop;
550 IF scope = 1 AND NOT l_found THEN
551 raise No_Event_On_Line;
552 END IF;
553 END IF;
554 End Loop;
555 ELSE
556 FND_FILE.PUT_LINE(FND_FILE.LOG,'Item/Item range NOT specified, selecting all items...');
557 IF Line_id is NULL THEN /*** Line is not specified ***/
558 FND_FILE.PUT_LINE(FND_FILE.LOG,'No line is specified');
559 FOR C1 in Cur_Flow_Rtg
560 Loop
561 print_debug(C1.assembly_item_id, C1.Alternate_Routing_Designator, C1.routing_sequence_id);
562 flm_calc_util(current_org_id, C1.assembly_item_id, l_op_type, C1.routing_sequence_id, Line_Id);
563 End Loop;
564 ELSIF Operation_code is NULL THEN /*** Only Line is specified ***/
565 FND_FILE.PUT_LINE(FND_FILE.LOG,'Only line is specified');
566 FOR C1 in Cur_Line_Flow_Rtg(Line_Id)
567 Loop
568 print_debug(C1.assembly_item_id, C1.Alternate_Routing_Designator, C1.routing_sequence_id);
569 flm_calc_util(current_org_id, C1.assembly_item_id, l_op_type, C1.routing_sequence_id, Line_Id);
570 End Loop;
571 ELSIF l_op_type > 1 THEN /*** Line and operation specified ***/
572 FND_FILE.PUT_LINE(FND_FILE.LOG,'Line and operation specified');
573 FOR C1 in Cur_Line_Op_Flow_Rtg(Line_id, Operation_Code)
574 Loop
575 print_debug(C1.assembly_item_id, C1.Alternate_Routing_Designator, C1.routing_sequence_id);
576 flm_calc_util(current_org_id, C1.assembly_item_id, l_op_type, C1.routing_sequence_id, Line_Id);
577 End Loop;
578 ELSE /*** Line, Operation and event specified ***/
579 FND_FILE.PUT_LINE(FND_FILE.LOG,'Line, operation and event specified');
580 FOR C1 in Cur_Line_Op_Flow_Rtg(Line_id, Operation_Code)
581 Loop
582 l_op_flag := check_op_type(C1.routing_sequence_id, oper_type);
583 IF l_op_flag <> 1 THEN
584 print_debug(C1.assembly_item_id, C1.Alternate_Routing_Designator, C1.routing_sequence_id);
585 flm_calc_util(current_org_id, C1.assembly_item_id, l_op_flag, C1.routing_sequence_id, Line_Id);
586 END IF;
587 End Loop;
588
589 END IF;
590 END IF;
591 Exception
592
593 WHEN No_Op_On_Line THEN
594 FND_FILE.PUT_LINE(FND_FILE.LOG,'Specified operation not available for the item on the line specified');
595 RETCODE := 2;
596 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
597 WHEN No_Event_On_Line THEN
598 FND_FILE.PUT_LINE(FND_FILE.LOG,'Specified event not available for the item on the line specified');
599 RETCODE := 2;
600 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
601
602 WHEN OTHERS THEN
603 FND_FILE.PUT_LINE(FND_FILE.LOG,'Others '||SQLCODE || ':'||SQLERRM) ;
604 RETCODE := 2;
605 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
606
607 END Flow_Batch_Calc;
608
609 END BOMCUMYD ;
[Home] [Help]