1 PACKAGE BODY Eng_Eco_Cost AS
2 /* $Header: ENGCOSTB.pls 120.1 2006/01/16 03:34:54 rnarveka noship $ */
3
4 TYPE cost_record_type IS RECORD (
5 revised_item_id NUMBER,
6 item_cost NUMBER );
7
8 TYPE repetative_record_type IS RECORD (
9 item_cost NUMBER,
10 demand_quantity NUMBER,
11 period_name_year VARCHAR2(8)
12 );
13
14 TYPE eco_cost_table IS TABLE OF cost_record_type
15 INDEX BY BINARY_INTEGER;
16
17 TYPE Rep_Table_Type IS TABLE OF repetative_record_type
18 INDEX BY BINARY_INTEGER;
19
20 l_repetative_cost_table Rep_Table_Type;
21 l_eco_cost_table Eco_Cost_Table;
22
23 /* Function: Calc_Cost_Of_Items_On_Eco
24
25 Description:
26 Function will calculate the material cost of the items that exist on the Eco and
27 will return this cost as the cost of the items as they exist in production.
28
29 Parameters: change_notice varchar2(10)
30 org_id number;
31
32 Return value: Number
33
34 */
35
36 FUNCTION Calc_Cost_Of_Items_On_Eco ( p_change_notice IN varchar2,
37 p_org_id IN number )
38 RETURN NUMBER
39 IS
40 l_items_cost number;
41 BEGIN
42
43 SELECT sum(cic.material_cost)
44 INTO l_items_cost
45 FROM cst_item_costs cic,
46 eng_revised_items eri
47 WHERE eri.change_notice = p_change_notice
48 AND eri.organization_id = p_org_id
49 AND cic.inventory_item_id = eri.revised_item_id
50 AND cic.organization_id = eri.organization_id
51 AND cic.cost_type_id = 1;
52
53 -- if no exception is raised, then return the value of l_items_cost
54 RETURN l_items_cost;
55
56 EXCEPTION
57 WHEN OTHERS THEN
58 return 0;
59 END Calc_Cost_Of_Items_On_Eco;
60
61
62 /* Procedure: Calc_Cost_Of_Changes
63
64 Description:
65 Procedure will calculate the changes that their cost, sum it all together to
66 give the cost of changes on the ECO.
67 If there is a change operation and the existing quantity is changed from 6 to 2
68 then the final change in cost is -4 * cost of that item. If this cost is added to
69 the cost of the assembly as it exists in production it will give the cost of the
70 the item after the ECO is eimplemented. Similary when an item is added on a bill
71 the cost of that item will be added to the original bill and if an item is being
72 deleted, then the cost will be deducted from the cost of the assembly.
73 Cost of an item will be ( planning % of Quantity ) * material cost of that item.
74
75 Parameters: change_notice varchar2(10)
76 org_id number;
77
78 Return value: None, but will create a pl/sql table .
79
80 */
81
82 PROCEDURE Calc_Cost_Of_Changes ( p_change_notice IN varchar2,
83 p_org_id IN number)
84 IS
85 CURSOR cost_of_change IS
86 SELECT eri.revised_item_id,
87 sum (cic.material_cost * (
88 ( DECODE( (bic1.planning_factor - bic2.planning_factor), 0,
89 bic1.planning_factor,
90 (bic1.planning_factor - bic2.planning_factor)
91 ) *
92 ( decode( bic1.acd_type,
96 )
93 1, -bic1.component_quantity,
94 2, (bic2.component_quantity - bic1.component_quantity),
95 3, bic2.component_quantity
97 )
98 ) /100
99 )
100 ) item_cost
101 from bom_inventory_components bic1,
102 bom_inventory_components bic2,
103 eng_revised_items eri,
104 mtl_system_items msi,
105 cst_item_costs cic
106 where eri.change_notice = p_change_notice
107 and eri.organization_id = p_org_id
108 and bic1.bill_sequence_id = eri.bill_sequence_id
109 and bic1.revised_item_sequence_id = eri.revised_item_sequence_id
110 and bic1.change_notice = eri.change_notice
111 and bic1.implementation_date is null
112 and bic2.bill_sequence_id = eri.bill_sequence_id
113 and ( ( bic1.acd_type in (2,3) and
114 bic2.component_sequence_id = bic1.old_component_sequence_id
115 )
116 or
117 bic1.acd_type = 1 and
118 bic2.component_sequence_id = bic1.component_sequence_id
119 )
120 and msi.inventory_item_id = bic1.component_item_id
121 and msi.organization_id = eri.organization_id
122 and cic.inventory_item_id = bic1.component_item_id
123 and cic.organization_id = eri.organization_id
124 and cic.cost_type_id = 1
125 group by eri.revised_item_id;
126
127 idx NUMBER;
128 BEGIN
129
130 --dbms_output.put_line('Executing calc_cost_of_changes . . .');
131
132 -- Loop thru the cursor and calculate and store the cost of
133 -- change for each of the revised items.
134 -- Store the result in the pl/sql table l_eco_cost_table
135 idx := 1;
136 FOR c_cost_of_change in cost_of_change LOOP
137 l_eco_cost_table(idx).revised_item_id := c_cost_of_change.revised_item_id;
138 l_eco_cost_table(idx).item_cost := c_cost_of_change.item_cost;
139
140 --dbms_output.put_line('Revised Item: ' || to_char(l_eco_cost_table(idx).revised_item_id) ||
141 -- ' Cost of Change: ' || to_Char(l_eco_cost_table(idx).item_cost));
142
143 idx := idx + 1;
144 END LOOP; -- Cost of change loop ends
145 END Calc_Cost_Of_Changes;
146
147
148 /* Function: get_cost_of_item
149
150 Description:
151 Will search the eco_cost_table and return the cost of the matching item
152
153 Return: NUMBER
154
155 */
156 FUNCTION Get_Cost_Of_Item( p_revised_item_id IN NUMBER )
157 RETURN NUMBER
158 IS
159 idx NUMBER;
160 BEGIN
161 FOR idx IN 1 .. l_eco_cost_table.count LOOP
162 IF l_eco_cost_table(idx).revised_item_id = p_revised_item_id THEN
163 RETURN l_eco_cost_table(idx).item_cost;
164 END IF;
165 END LOOP;
166 END Get_Cost_Of_Item;
167
168
169 /* Procedure: Get_Cost_Quantity
170
171 Description:
172 Will return the item cost and the demand quantity for a period. This is the cost and
173 quantity for a item with repetitive demand.
174
175 Return: None, but set two o/p parameters x_item_cost and x_demand_quantity
176
177 */
178
179 PROCEDURE Get_Cost_Quantity ( p_period_name_year IN VARCHAR2,
180 x_demand_quantity OUT NOCOPY NUMBER,
181 x_item_cost OUT NOCOPY NUMBER
182 )
183 IS
184 idx NUMBER;
185 BEGIN
186 idx := 0;
187
188 FOR idx IN 1 .. l_repetative_cost_table.count LOOP
189 IF l_repetative_cost_table(idx).period_name_year = p_period_name_year THEN
190 x_demand_quantity := l_repetative_cost_table(idx).demand_quantity;
191 x_item_cost := l_repetative_cost_table(idx).item_cost;
192
193 exit;
194 END IF;
195 END LOOP;
196
197 IF idx <> 0 THEN
198 l_repetative_cost_table.DELETE(idx);
199 ELSE
200 -- If procedure executes till this point i.e. no period record is found
201 -- then set return values to 0
202 x_demand_quantity := 0;
203 x_item_cost := 0;
204 END IF;
205
206 END Get_Cost_Quantity;
207
208
209 /* Procedure: Insert_Into_Temp_Table
210
211 Description:
212 Will insert the passed data into the ENG_BIS_ECO_COST_TEMP table.
213
214 Return: None
215
216 */
217
218 PROCEDURE Insert_Into_Temp_Table (p_eco_cost IN NUMBER,
219 p_total_cost_saving IN NUMBER,
220 p_demand_quantity IN NUMBER,
221 p_period_name_year IN VARCHAR2,
222 p_period_start_date IN DATE,
223 p_change_notice IN VARCHAR2,
224 p_org_id IN NUMBER ,
225 p_query_id IN NUMBER
226 )
227 IS
228 BEGIN
229
230 INSERT INTO BOM_FORM_QUERY
231 ( number1,
232 number2,
233 number4,
234 char1,
235 date1,
236 char2,
237 number3,
238 query_id,
239 last_update_date,
240 last_updated_by,
241 creation_date,
242 created_by
243 )
247 p_period_name_year,
244 VALUES ( p_eco_cost,
245 p_demand_quantity,
246 p_total_cost_saving,
248 p_period_start_date,
249 p_change_notice,
250 p_org_id,
251 p_query_id,
252 sysdate,
253 1,
254 sysdate,
255 1
256 );
257
258 END Insert_Into_Temp_Table;
259
260 /* Procedure: Calc_Repetitive_Demand
261
262 Description:
263 When a rate based demand exists, which spans across multiple periods, then
264 the demand needs to be split in the appropriate calendar periods with the
265 appropriate quantity.
266
267 Returns: None.
268 */
269 PROCEDURE Calc_Repetitive_Demand( p_demand_date IN DATE,
270 p_comp_date IN DATE,
271 p_req_qty IN NUMBER,
272 p_daily_rate IN NUMBER,
273 p_org_id IN NUMBER,
274 p_est_cost IN NUMBER,
275 p_cost_change IN NUMBER
276 )
277 IS
278 l_required_quantity NUMBER;
279 l_start_date DATE;
280 --
281 -- Cursor will select periods between the demand_date and demand completion date
282 -- This will then be used to spread the rate based demand.
283 --
284 CURSOR cal_periods IS
285 SELECT period_start_date, next_date, period_name
286 FROM bom_org_cal_periods_view
287 WHERE organization_id = p_org_id
288 AND next_date > p_demand_date
289 AND period_start_date <= p_comp_date;
290
291 idx NUMBER;
292 BEGIN
293 l_required_quantity := p_req_qty;
294 l_start_date := p_demand_date;
295
296 --dbms_output.put_line('Calculate repetative demand . . .');
297
298 idx := nvl(l_repetative_cost_table.count, 0) + 1;
299
300 FOR c_cal_periods IN cal_periods LOOP
301 IF p_comp_date > c_cal_periods.next_date THEN
302 l_required_quantity := l_required_quantity -
303 (c_cal_periods.next_date - l_start_date) *
304 p_daily_rate;
305 l_start_date := c_cal_periods.next_date;
306
307 ELSIF p_comp_date < c_cal_periods.next_date and
308 p_comp_date > c_cal_periods.period_start_date
309 THEN
310 l_required_quantity := (c_cal_periods.period_start_date - p_comp_date) *
311 p_daily_rate;
312 END IF;
313
314 -- Create a record in the pl/sql table and store the
315 -- demand quantity and the cost for that period.
316
317 l_repetative_cost_table(idx).demand_quantity := l_required_quantity;
318 l_repetative_cost_table(idx).item_cost := p_cost_change * l_required_quantity;
319 l_repetative_cost_table(idx).period_name_year := c_cal_periods.period_name || '/' ||
320 to_char(c_cal_periods.period_start_date,
321 'YYYY');
322 idx := idx + 1;
323
324 END LOOP;
325
326 END Calc_Repetitive_Demand;
327
328
329
330 PROCEDURE Eco_Cost_Calculate ( p_change_notice IN varchar2,
331 p_org_id IN number,
332 p_plan_name IN varchar2,
333 p_start_date IN DATE,
334 p_end_date IN DATE,
335 p_query_id IN number)
336 IS
337
338 l_cost_of_eco number;
339 l_cost_of_changes number;
340 l_estimated_cost number;
341 l_required_quantity number;
342 l_mfg_exists boolean;
343 l_eng_exists boolean;
344 l_temp_qty number;
345 l_temp_cost number;
346
347 --
348 -- Cursor to check which type of items exist on the ECO
349 -- and decide whether to use mfg_cost or eng_cost
350 --
351 CURSOR check_assembly_type IS
352 SELECT nvl(eco.estimated_mfg_cost, 0) mfg_cost,
353 nvl(eco.estimated_eng_cost, 0) eng_cost,
354 bom.assembly_type
355 FROM eng_engineering_changes eco,
356 eng_revised_items eri,
357 bom_bill_of_materials bom
358 WHERE eco.change_notice = p_change_notice
359 AND eco.organization_id = p_org_id
360 AND eri.change_notice = eco.change_notice
361 AND eri.organization_id = eco.organization_id
362 AND bom.bill_sequence_id = eri.bill_sequence_id;
363
364 --
365 -- Cursor to get the calendar periods that lie between the
366 -- given start and end dates
367 --
368 CURSOR calendar_periods IS
369 SELECT period_start_date, next_date, period_name
370 FROM bom_org_cal_periods_view
371 WHERE organization_id = p_org_id
372 AND next_date >= p_start_date
373 AND period_start_date <= p_end_date;
374
375 --
376 -- Cursor to get the demand for all items that are planned using the
377 -- plan_name and those that lie on the ECO only.
378 --
379 CURSOR item_demand (cp_start_date DATE,
380 cp_end_date DATE,
381 cp_change_notice varchar2,
382 cp_org_id number,
383 cp_plan_name varchar2) IS
384 SELECT using_requirements_quantity,
385 using_assembly_demand_date,
386 assembly_demand_comp_date,
387 daily_demand_rate,
388 revised_item_id
389 FROM eng_revised_items eri,
390 mrp_gross_requirements mgr
391 WHERE eri.change_notice = cp_change_notice
392 AND eri.organization_id = cp_org_id
393 AND mgr.compile_designator = cp_plan_name
394 AND mgr.organization_id = eri.organization_id
395 AND mgr.inventory_item_id = eri.revised_item_id
396 AND mgr.using_assembly_demand_date >= cp_start_date
400
397 AND mgr.using_assembly_demand_date < cp_end_date;
398
399 BEGIN
401 /*
402 This calculation is no longer required.
403
404 l_cost_of_eco := Calc_Cost_Of_Items_On_Eco(p_change_notice => p_change_notice,
405 p_org_id => p_org_id);
406 */
407
408 Calc_Cost_Of_Changes(p_change_notice => p_change_notice,
409 p_org_id => p_org_id);
410
411
412
413 -- If the ECO has both manufacturing and engineering items, then sum both the
414 -- estimated_mfg_cost and the estimated_eng_cost
415 -- If the ECO has only Engineering items, then only use the estimated_eng_cost
416 -- If the ECO has only Manufacturing items, then only use the estimated_mfg_cost
417 l_mfg_exists := FALSE;
418 l_eng_exists := FALSE;
419
420
421 FOR c_assembly_type IN check_assembly_type LOOP
422
423 IF c_assembly_type.assembly_type = 1 THEN
424 l_mfg_exists := TRUE;
425 ELSIF c_assembly_type.assembly_type = 2 THEN
426 l_eng_exists := TRUE;
427 END IF;
428
429 IF l_mfg_exists = TRUE AND
430 l_eng_exists = TRUE THEN
431
432 l_estimated_cost := c_assembly_type.mfg_cost + c_assembly_type.eng_cost;
433
434 -- If both the values are found, then no need to continue any further
435 EXIT;
436
437 ELSIF l_mfg_exists = TRUE AND
438 l_eng_exists = FALSE THEN
439
440 l_estimated_cost := c_assembly_type.mfg_cost;
441
442 ELSE
443 l_estimated_cost := c_assembly_type.eng_cost;
444 END IF;
445
446 END LOOP;
447
448 -- At this point you have the cost of the assemblies as they exist in production
449 -- , changed cost of the ECO after the changes would be implemented
450 -- estimated cost to be used depending on the type of items that exist on the ECO
451 --
452 -- Now calculate the demand based on the plan_name and aggregate the demand based on the
453 -- Calendar periods.
454
455 -- Store the implementation cost in the global variable so that the
456 -- report can read it.
457 ENG_ECO_COST.g_estimated_Cost := l_estimated_Cost;
458
459 l_estimated_Cost := -l_estimated_Cost;
460 FOR c_calendar_periods IN calendar_periods LOOP
461
462 -- For every period initialize l_required_quantity and l_cost_of_changes before proceeding
463 -- with any calculations.
464
465 l_required_quantity := 0;
466 l_cost_of_changes := 0;
467
468 --dbms_output.put_line('Period: ' || c_calendar_periods.period_name ||
469 -- ' Estimated Cost: ' || to_char(l_estimated_Cost));
470
471 FOR c_item_demand IN item_demand(cp_start_date => c_calendar_periods.period_start_date,
472 cp_end_date => c_calendar_periods.next_date,
473 cp_change_notice => p_change_notice,
474 cp_org_id => p_org_id,
475 cp_plan_name => p_plan_name)
476 LOOP
477
478 --dbms_output.put_line('Getting demand for ' || c_calendar_periods.period_name);
479
480 --
481 -- If the demand for any item is repetitive or rate based, then
482 -- calculate the quantity of the demand based on the daily_demand_rate
483 --
484
485 IF NVL(c_item_demand.daily_demand_rate,0) <> 0 THEN
486
487 -- If the demand completion date span across multiple
488 -- periods, then create records with appropriate
489 -- quantity in the ENG_ECO_COSTS_TEMP table.
490
491 Calc_Repetitive_Demand(
492 p_demand_date => c_item_demand.using_assembly_demand_date,
493 p_comp_date => c_item_demand.assembly_demand_comp_date,
494 p_req_qty => c_item_demand.using_requirements_quantity,
495 p_org_id => p_org_id,
496 p_daily_rate => c_item_demand.daily_demand_rate,
497 p_est_cost => l_estimated_cost,
498 p_cost_change => Get_Cost_Of_Item(p_revised_item_id =>
499 c_item_demand.revised_item_id)
500 );
501 ELSE
502 l_required_quantity := l_required_quantity +
503 c_item_demand.using_requirements_quantity;
504
505 l_cost_of_changes := l_cost_of_changes +
506 c_item_demand.using_requirements_quantity *
507 Get_Cost_Of_Item(p_revised_item_id =>
508 c_item_demand.revised_item_id);
509 END IF;
510
511 END LOOP; -- Item demand Loop Ends
512
513
514 --
515 -- Check if there is any demand generated for this period by any of the
516 -- repetative demand items
517 --
518 --dbms_output.put_line('Getting cost and quantity . . .');
519
520 Get_Cost_Quantity(p_period_name_year => (c_calendar_periods.period_name) || '/' ||
521 to_char(c_calendar_periods.period_start_date,'YYYY'),
522 x_demand_quantity => l_temp_qty,
523 x_item_cost => l_temp_cost
524 );
525 --
526 -- Cost and Quantity is found, then
527 --
528 IF l_temp_qty <> 0 and l_temp_cost <> 0 THEN
529 l_required_quantity := l_required_quantity + l_temp_qty;
530 l_cost_of_changes := l_cost_of_changes + l_temp_cost;
531 END IF;
532 --
533 -- quantities for all the items in a given period are calculated, then
534 -- insert records into the ENG_ECO_COSTS_TEMP table
535
536 IF l_required_quantity <> 0 THEN
537 --dbms_output.put_line('Inserting data . . .');
538
539 Insert_Into_Temp_Table(p_eco_cost => l_cost_of_changes,
540 p_total_cost_saving => (l_estimated_cost + l_cost_of_changes),
541 p_demand_quantity => l_required_quantity,
542 p_period_name_year => (c_calendar_periods.period_name) || '/' ||
543 to_char(c_calendar_periods.period_start_date,
544 'YYYY'),
545 p_period_start_date => c_calendar_periods.period_start_date,
546 p_change_notice => p_change_notice,
547 p_org_id => p_org_id,
548 p_query_id => p_query_id
549 );
550 END IF;
551
552 l_estimated_cost := l_estimated_cost + l_cost_of_changes;
553
554 END LOOP; -- Calendar periods Loop Ends
555
556 END Eco_Cost_Calculate;
557
558 /* Function : g_estimated_cost
559
560 Return : number
561
562 Description
563 Will simply return the estimated cost to the client
564 */
565
566 FUNCTION get_estimated_Cost RETURN NUMBER
567 IS
568 BEGIN
569 return ENG_ECO_COST.g_estimated_cost;
570 END;
571
572 END Eng_Eco_Cost;
[Home] [Help]