1 PACKAGE BODY GMD_LCF_FETCH_PKG AS
2 /* $Header: GMDLCFMB.pls 120.15 2006/10/11 19:20:33 rajreddy noship $ */
3
4 /*##############################################################
5 # NAME
6 # calculate
7 # SYNOPSIS
8 # proc calculate
9 # DESCRIPTION
10 # This procedure calculates the values for the products
11 # by performing the rollups based on data type.
12 ###############################################################*/
13
14 PROCEDURE calculate (V_formulation_spec_id IN NUMBER, V_line_id IN NUMBER,
15 X_return_status OUT NOCOPY VARCHAR2) IS
16 CURSOR Cur_get_prod IS
17 SELECT a.tech_parm_name,a.lm_unit_code,b.tech_parm_id,a.data_type
18 FROM gmd_tech_parameters_b a, gmd_technical_reqs b
19 WHERE a.tech_parm_id = b.tech_parm_id
20 AND b.formulation_spec_id = V_formulation_spec_id
21 AND a.data_type IN (5,6,12)
22 UNION
23 SELECT a.tech_parm_name,a.lm_unit_code,b.tech_parm_id,a.data_type
24 FROM gmd_tech_parameters_b a, gmd_formulation_specs b
25 WHERE a.tech_parm_id = b.tech_parm_id
26 AND b.formulation_spec_id = V_formulation_spec_id
27 AND a.data_type IN (5,6,12);
28
29 BEGIN
30 FOR l_rec IN Cur_get_prod LOOP
31 FND_MSG_PUB.INITIALIZE;
32 IF (l_rec.data_type = 5 OR l_rec.data_type = 12) THEN
33 rollup_wt_pct (V_parm_name => l_rec.tech_parm_name,
34 V_parm_id => l_rec.tech_parm_id,
35 V_line_id => V_line_id,
36 X_return_status => X_return_status);
37 ELSIF l_rec.data_type = 6 THEN
38 rollup_vol_pct (V_parm_name => l_rec.tech_parm_name,
39 V_parm_id => l_rec.tech_parm_id,
40 V_line_id => V_line_id,
41 X_return_status => X_return_status);
42 END IF;
43 END LOOP;
44 END calculate;
45
46 /*##############################################################
47 # NAME
48 # rollup_wt_pct
49 # SYNOPSIS
50 # proc rollup_wt_pct
51 # DESCRIPTION
52 # This procedure gets the values for the products for the
53 # by performing the weight rollup.
54 ###############################################################*/
55
56 PROCEDURE rollup_wt_pct (V_parm_name IN VARCHAR2,
57 V_line_id IN NUMBER,
58 V_parm_id IN NUMBER,
59 X_return_status OUT NOCOPY VARCHAR2) IS
60
61 CURSOR Cur_get_line_ingred IS
62 SELECT NVL(SUM(weight), 0), NVL(SUM(weightpct), 0)
63 FROM
64 (SELECT qty_mass weight, qty_mass * value weightpct
65 FROM gmd_lcf_details_gtmp d, gmd_lcf_tech_data_gtmp t
66 WHERE d.line_id = t.line_id (+)
67 AND t.tech_parm_id (+) = V_parm_id
68 AND line_type = -1);
69
70 X_ingred_wt NUMBER;
71 X_ingred_wtpct NUMBER;
72 X_rollup NUMBER;
73 BEGIN
74 X_return_status := FND_API.g_ret_sts_success;
75
76 OPEN Cur_get_line_ingred;
77 FETCH Cur_get_line_ingred INTO X_ingred_wt, X_ingred_wtpct;
78 CLOSE Cur_get_line_ingred;
79
80 IF (NVL(X_ingred_wt,0)) <> 0 THEN
81 X_rollup := (NVL(X_ingred_wtpct,0)) / (NVL(X_ingred_wt,0));
82 END IF;
83
84 UPDATE gmd_lcf_tech_data_gtmp
85 SET value = X_rollup
86 WHERE tech_parm_id = V_parm_id
87 AND line_id = V_line_id;
88
89 IF SQL%NOTFOUND THEN
90 INSERT INTO GMD_LCF_TECH_DATA_GTMP
91 (TECH_PARM_ID,
92 VALUE,
93 NUM_DATA,
94 LINE_ID)
95 VALUES
96 (V_parm_id,
97 X_rollup,
98 X_rollup,
99 V_line_id);
100 END IF;
101
102 IF X_rollup IS NULL THEN
103 gmd_api_grp.log_message('GMD_WEIGHT_CALCULATE','V_PARM_NAME', V_parm_name);
104 X_return_status := FND_API.g_ret_sts_error;
105 END IF;
106
107 EXCEPTION
108 WHEN OTHERS THEN
109 fnd_msg_pub.add_exc_msg ('GMD_LCF_FETCH_PKG', 'Rollup_Wt_Pct');
110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
111 END rollup_wt_pct;
112
113 /*##############################################################
114 # NAME
115 # rollup_vol_pct
116 # SYNOPSIS
117 # proc rollup_vol_pct
118 # DESCRIPTION
119 # This procedure gets the values for the products for the
120 # by performing the voulme rollup.
121 ###############################################################*/
122
123 PROCEDURE rollup_vol_pct (V_parm_name IN VARCHAR2,
124 V_line_id IN NUMBER,
125 V_parm_id IN NUMBER,
126 X_return_status OUT NOCOPY VARCHAR2) IS
127
128 CURSOR Cur_get_line_ing IS
129 SELECT SUM(volume), SUM(volumepct)
130 FROM
131 (SELECT qty_vol volume, qty_vol * value volumepct
132 FROM gmd_lcf_details_gtmp d, gmd_lcf_tech_data_gtmp t
133 WHERE d.line_id = t.line_id (+)
134 AND t.tech_parm_id (+) = V_parm_id
135 AND line_type = -1);
136
137 X_ingred_vol NUMBER;
138 X_ingred_volpct NUMBER;
139 X_rollup NUMBER;
140 BEGIN
141 X_return_status := FND_API.g_ret_sts_success;
142
143 OPEN Cur_get_line_ing;
144 FETCH Cur_get_line_ing INTO X_ingred_vol, X_ingred_volpct;
145 CLOSE Cur_get_line_ing;
146
147 IF (NVL(X_ingred_vol,0)) <> 0 THEN
148 X_rollup := (NVL(X_ingred_volpct,0)) / (NVL(X_ingred_vol,0));
149 END IF;
150
151 UPDATE gmd_lcf_tech_data_gtmp
152 SET value = X_rollup
153 WHERE tech_parm_id = V_parm_id
154 AND line_id = V_line_id;
155
156 IF SQL%NOTFOUND THEN
157 INSERT INTO GMD_LCF_TECH_DATA_GTMP
158 (TECH_PARM_ID,
159 NUM_DATA,
160 VALUE,
161 LINE_ID)
162 VALUES
163 (V_parm_id,
164 X_rollup,
165 X_rollup,
166 V_line_id);
167 END IF;
168
169 IF X_rollup IS NULL THEN
170 gmd_api_grp.log_message('GMD_WEIGHT_CALCULATE','V_PARM_NAME', V_parm_name);
171 X_return_status := FND_API.g_ret_sts_error;
172 END IF;
173 EXCEPTION
174 WHEN OTHERS THEN
175 fnd_msg_pub.add_exc_msg ('GMD_LCF_FETCH_PKG', 'Rollup_Vol_Pct');
176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
177 END rollup_vol_pct;
178
179 /*##############################################################
180 # NAME
181 # get_density_value
182 # SYNOPSIS
183 # proc get_density_value
184 # DESCRIPTION
185 # This procedure gets the density value for uom conversion.
186 ###############################################################*/
187
188 FUNCTION get_density_value (V_line_id IN NUMBER,
189 V_density_parameter IN VARCHAR2) RETURN NUMBER IS
190 CURSOR Cur_density IS
191 SELECT value
192 FROM gmd_lcf_tech_data_gtmp
193 WHERE line_id = V_line_id
194 AND tech_parm_name = V_density_parameter;
195 l_value NUMBER;
196 BEGIN
197 OPEN Cur_density;
198 FETCH Cur_density INTO l_value;
199 CLOSE Cur_density;
200 RETURN (l_value);
201 END get_density_value;
202
203 /*##############################################################
204 # NAME
205 # update_line_mass_vol_qty
206 # SYNOPSIS
207 # proc update_line_mass_vol_qty
208 # DESCRIPTION
209 # This procedure calculates the qtys to mass and volume.
210 ###############################################################*/
211
212 PROCEDURE update_line_mass_vol_qty (V_orgn_id IN NUMBER,
213 V_line_id IN NUMBER,
214 V_density_parameter IN VARCHAR2,
215 V_mass_uom IN VARCHAR2,
216 V_vol_uom IN VARCHAR2,
217 X_return_status OUT NOCOPY VARCHAR2) IS
218
219 CURSOR Cur_line_qty IS
220 SELECT inventory_item_id, lot_number, qty,
221 detail_uom,primary_uom,secondary_uom
222 FROM gmd_lcf_details_gtmp
223 WHERE line_id = V_line_id;
224
225 CURSOR Cur_line_item_number (V_inventory_item_id NUMBER)IS
226 SELECT concatenated_segments
227 FROM mtl_system_items_kfv
228 WHERE inventory_item_id = V_inventory_item_id;
229
230 l_conv_factor NUMBER;
231 l_mass_qty NUMBER;
232 l_primary_qty NUMBER;
233 l_vol_qty NUMBER;
234 l_item_no VARCHAR2(1000);
235 l_error NUMBER := 0;
236 l_rec Cur_line_qty%ROWTYPE;
237 LINE_NOT_FOUND EXCEPTION;
238 BEGIN
239 X_return_status := FND_API.g_ret_sts_success;
240
241 l_conv_factor := get_density_value (V_line_id => V_line_id,
242 V_density_parameter => V_density_parameter);
243 OPEN Cur_line_qty;
244 FETCH Cur_line_qty INTO l_rec;
245 IF Cur_line_qty%NOTFOUND THEN
246 CLOSE Cur_line_qty;
247 RAISE LINE_NOT_FOUND;
248 END IF;
249 CLOSE Cur_line_qty;
250
251 IF l_rec.detail_uom <> V_mass_uom THEN
252 l_mass_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
253 pformula_id => 0,
254 plot_number => NULL,
255 pcur_qty => l_rec.qty,
256 pcur_uom => l_rec.detail_uom,
257 pnew_uom => V_mass_uom,
258 patomic => 0,
259 plab_id => V_orgn_id,
260 pcnv_factor => l_conv_factor);
261 IF l_mass_qty < 0 THEN
262 l_error := 1;
263 l_mass_qty := NULL;
264 END IF;
265 ELSE
266 l_mass_qty := l_rec.qty;
267 END IF;
268
269 IF l_rec.detail_uom <> V_vol_uom THEN
270 l_vol_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
271 pformula_id => 0,
272 plot_number => NULL,
273 pcur_qty => l_rec.qty,
274 pcur_uom => l_rec.detail_uom,
275 pnew_uom => V_vol_uom,
276 patomic => 0,
277 plab_id => V_orgn_id,
278 pcnv_factor => l_conv_factor);
279 IF l_vol_qty < 0 THEN
280 l_error := 1;
281 l_vol_qty := NULL;
282 END IF;
283 ELSE
284 l_vol_qty := l_rec.qty;
285 END IF;
286
287 IF l_rec.detail_uom <> l_rec.primary_uom THEN
288 l_primary_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_rec.inventory_item_id,
289 pformula_id => 0,
290 plot_number => NULL,
291 pcur_qty => l_rec.qty,
292 pcur_uom => l_rec.detail_uom,
293 pnew_uom => l_rec.primary_uom,
294 patomic => 0,
295 plab_id => V_orgn_id);
296 IF l_primary_qty < 0 THEN
297 l_error := 1;
298 l_primary_qty := NULL;
299 END IF;
300 ELSE
301 l_primary_qty := l_rec.qty;
302 END IF;
303
304 UPDATE gmd_lcf_details_gtmp
305 SET qty_mass = l_mass_qty,
306 mass_uom = V_mass_uom,
307 qty_vol = l_vol_qty,
308 vol_uom = V_vol_uom,
309 primary_qty = l_primary_qty,
310 primary_uom = l_rec.primary_uom
311 WHERE line_id = V_line_id;
312
313 OPEN Cur_line_item_number(l_rec.inventory_item_id);
314 FETCH Cur_line_item_number INTO l_item_no;
315 CLOSE Cur_line_item_number;
316
317 IF l_error = 1 THEN
318 X_return_status := FND_API.g_ret_sts_error;
319 gmd_api_grp.log_message('LM_BAD_UOMCV', 'ITEM_NO',l_item_no);
320 END IF;
321
322 EXCEPTION
323 WHEN line_not_found THEN
324 X_return_status := FND_API.g_ret_sts_error;
325 WHEN OTHERS THEN
326 fnd_msg_pub.add_exc_msg ('GMD_LCF_FETCH_PKG', 'Update_Line_Mass_Vol_Qty');
327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
328 END update_line_mass_vol_qty;
329
330 /*##############################################################
331 # NAME
332 # update_line_mass_qty
333 # SYNOPSIS
334 # proc update_line_mass_qty
335 # DESCRIPTION
336 # This procedure calculates the qtys to mass and volume.
337 ###############################################################*/
338
339 PROCEDURE update_mass_vol_qty (V_orgn_id IN NUMBER,
340 V_entity_id IN NUMBER,
341 V_density_parameter IN VARCHAR2,
342 V_mass_uom IN VARCHAR2,
343 V_vol_uom IN VARCHAR2,
344 X_return_status OUT NOCOPY VARCHAR2) IS
345 CURSOR Cur_get_lines IS
346 SELECT line_id
347 FROM gmd_lcf_details_gtmp
348 WHERE line_type <> 1;
349
350 l_return_status VARCHAR2(1);
351 BEGIN
352 X_return_status := FND_API.g_ret_sts_success;
353
354 FOR l_rec IN Cur_get_lines LOOP
355 l_return_status := FND_API.g_ret_sts_success;
356 update_line_mass_vol_qty (V_orgn_id => V_orgn_id,
357 V_line_id => l_rec.line_id,
358 V_density_parameter => V_density_parameter,
359 V_mass_uom => V_mass_uom,
360 V_vol_uom => V_vol_uom,
361 X_return_status => l_return_status);
362 IF l_return_status <> x_return_status THEN
363 X_return_status := l_return_status;
364 END IF;
365 END LOOP;
366 END update_mass_vol_qty;
367
368 /*##############################################################
369 # NAME
370 # load_cost_values
371 # SYNOPSIS
372 # proc load_cost_values
373 # DESCRIPTION
374 # This procedure inserts the data into temp tables and will
375 # be fetched in the form.
376 ###############################################################*/
377
378 PROCEDURE load_cost_values (V_orgn_id IN NUMBER, V_inv_item_id IN NUMBER, V_cost_type IN VARCHAR2,
379 V_date IN DATE, V_cost_orgn IN VARCHAR2, V_source IN NUMBER, X_value OUT NOCOPY NUMBER) IS
380 l_msg_data VARCHAR2(2000);
381 l_msg_count NUMBER;
382 l_total_cost NUMBER;
383 l_cost_type VARCHAR2(80);
384 l_no_rows NUMBER;
385 l_cost NUMBER;
386 l_qty NUMBER;
387 l_component NUMBER;
388 l_analy_code VARCHAR2(70);
389 l_return_status VARCHAR2(1);
390 BEGIN
391 --Insert the optimize parameter values defined in the formulation specification screen.
392 --Get the costing source organization.
393 l_cost_type := V_cost_type;
394
395 IF (V_source = 1) THEN
396 --Call the Process cost api to get the values.
397 l_qty := gmf_cmcommon.get_process_item_cost (p_api_version => 1.0
398 , p_init_msg_list => 'F'
399 , x_return_status => l_return_status
400 , x_msg_count => l_msg_count
401 , x_msg_data => l_msg_data
402 , p_inventory_item_id => V_inv_item_id
403 , p_organization_id => V_cost_orgn
404 , p_transaction_date => V_date
405 , p_detail_flag => 1
406 , p_cost_method => l_cost_type
407 , p_cost_component_class_id => l_component
408 , p_cost_analysis_code => l_analy_code
409 , x_total_cost => l_total_cost
410 , x_no_of_rows => l_no_rows);
411
412 IF (l_qty > 0) THEN
413 X_value := l_total_cost;
414 END IF;
415 ELSE
416 --Call the External cost api to get the values.
417 l_cost := gmd_lcf_util.get_cost (p_item_id => V_inv_item_id
418 ,p_organization_id => V_orgn_id
419 ,p_cost_orgn_id => V_cost_orgn
420 ,p_lot_no => NULL
421 ,p_qty => NULL
422 ,p_uom => NULL
423 ,p_cost_date => V_date);
424 IF (l_cost >= 0) THEN
425 X_value := l_cost;
426 END IF;
427 END IF;
428 EXCEPTION
429 WHEN OTHERS THEN
430 fnd_msg_pub.add_exc_msg ('GMD_LCF_FETCH_PKG', 'Load_cost_Values');
431 END load_cost_values;
432
433 /*##############################################################
434 # NAME
435 # load_tech_values
436 # SYNOPSIS
437 # proc load_tech_values
438 # DESCRIPTION
439 # This procedure inserts the data into temp tables and will
440 # be fetched in the form.
441 ###############################################################*/
442
443 PROCEDURE load_tech_values (V_orgn_id IN NUMBER, V_formulation_spec_id IN NUMBER, V_date IN DATE) IS
444 CURSOR Cur_get_type IS
445 SELECT a.*, b.line_id, b.tech_parm_id tech, c.inventory_item_id
446 FROM gmd_tech_parameters_b a, gmd_lcf_tech_data_gtmp b, gmd_lcf_details_gtmp c
447 WHERE a.tech_parm_id = b.tech_parm_id
448 AND b.line_id = c.line_id;
449
450 CURSOR Cur_get_value (V_inventory_item_id NUMBER, V_tech_parm_id NUMBER) IS
451 SELECT a.num_data
452 FROM gmd_technical_data_vl a, gmd_lcf_tech_data_gtmp b, gmd_lcf_details_gtmp c
453 WHERE a.tech_parm_id = b.tech_parm_id
454 AND a.tech_parm_id = V_tech_parm_id
455 AND a.inventory_item_id = c.inventory_item_id
456 AND a.inventory_item_id = V_inventory_item_id
457 AND a.organization_id = V_orgn_id;
458
459 CURSOR Cur_get_cost_method (P_orgn_id NUMBER) IS
460 SELECT Cost_Type, cost_source
461 FROM gmd_tech_parameters_b
462 WHERE organization_id = P_orgn_id
463 AND Default_cost_parameter = 1;
464
465 l_density_parameter VARCHAR2(240);
466 l_value NUMBER;
467 X_value NUMBER;
468 l_parm_value VARCHAR2(240);
469 l_return_status VARCHAR2(1);
470 l_cost_type VARCHAR2(4);
471 l_cost_source NUMBER;
472 BEGIN
473 l_density_parameter := FND_PROFILE.VALUE('LM$DENSITY');
474 /* Inserting the technical parameter data of item and lot to temp tables*/
475 IF (V_orgn_id IS NOT NULL) THEN
476 INSERT INTO GMD_LCF_TECH_DATA_GTMP
477 (LINE_ID,TECH_PARM_ID,TECH_PARM_NAME,QCASSY_TYP_ID)
478 SELECT c.line_id,b.tech_parm_id,d.tech_parm_name,d.qcassy_typ_id
479 FROM gmd_technical_reqs b,gmd_lcf_details_gtmp c, gmd_tech_parameters_b d
480 WHERE b.tech_parm_id = d.tech_parm_id
481 AND b.formulation_spec_id = V_formulation_spec_id;
482
483 -- if tech params data type is not 12 then insert the values from item tech data tables
484 -- at once no need to loop through.
485 INSERT INTO GMD_LCF_TECH_DATA_GTMP
486 (LINE_ID,TECH_PARM_ID,TECH_PARM_NAME,QCASSY_TYP_ID)
487 SELECT c.line_id,b.tech_parm_id,b.tech_parm_name,b.qcassy_typ_id
488 FROM gmd_tech_parameters_b b, gmd_lcf_details_gtmp c,
489 gmd_formulation_specs e
490 WHERE b.tech_parm_id = e.tech_parm_id
491 AND e.formulation_spec_id = V_formulation_spec_id;
492
493 gmd_api_grp.fetch_parm_values (P_orgn_id => V_orgn_id
494 ,P_parm_name => 'GMD_COST_SOURCE_ORGN'
495 ,P_parm_value => l_parm_value
496 ,X_return_status => l_return_status);
497
498 -- Get cost type and cost source in cost source orgn
499 IF l_parm_value IS NOT NULL THEN
500 OPEN Cur_get_cost_method(l_parm_value);
501 FETCH Cur_get_cost_method INTO l_cost_type, l_cost_source;
502 CLOSE Cur_get_cost_method;
503 END IF;
504
505 IF l_cost_type IS NOT NULL THEN
506 OPEN Cur_get_cost_method(V_orgn_id);
507 FETCH Cur_get_cost_method INTO l_cost_type, l_cost_source;
508 CLOSE Cur_get_cost_method;
509 END IF;
510
511 FOR l_rec IN Cur_get_type LOOP
512 IF l_rec.data_type = 12 THEN
513 load_cost_values (V_orgn_id => V_orgn_id,
514 V_inv_item_id => l_rec.inventory_item_id,
515 V_cost_type => NVL(l_rec.cost_type,l_cost_type),
516 V_date => V_date,
517 V_cost_orgn => NVL(l_parm_value,V_orgn_id),
518 V_source => NVL(l_rec.cost_source, l_cost_source),
519 X_value => l_value);
520 UPDATE GMD_LCF_TECH_DATA_GTMP
521 SET value = l_value,
522 num_data = l_value
523 WHERE tech_parm_id = l_rec.tech
524 AND line_id = l_rec.line_id;
525 ELSIF l_rec.qcassy_typ_id IS NOT NULL THEN
526 load_quality_data (V_line_id => l_rec.line_id,
527 V_orgn_id => V_orgn_id,
528 V_qcassy_typ_id => l_rec.qcassy_typ_id,
529 V_tech_parm_id => l_rec.tech);
530 ELSE
531 OPEN Cur_get_value (l_rec.inventory_item_id,l_rec.tech);
532 FETCH Cur_get_value INTO X_value;
533 CLOSE Cur_get_value;
534 UPDATE GMD_LCF_TECH_DATA_GTMP
535 SET value = X_value,
536 num_data = X_value
537 WHERE tech_parm_id = l_rec.tech
538 AND line_id = l_rec.line_id;
539 END IF;
540 END LOOP;
541
542 --A Row will be inserted for density parameter and this will be used for
543 --Product rollup calculations.
544 INSERT INTO GMD_LCF_TECH_DATA_GTMP
545 (LINE_ID,TECH_PARM_ID,TECH_PARM_NAME,VALUE,NUM_DATA,QCASSY_TYP_ID)
546 SELECT c.line_id,a.tech_parm_id,d.tech_parm_name,
547 a.num_data,a.num_data,d.qcassy_typ_id
548 FROM gmd_technical_data_vl a,
549 gmd_lcf_details_gtmp c, gmd_tech_parameters_b d
550 WHERE a.tech_parm_id = d.tech_parm_id
551 AND d.tech_parm_name = l_density_parameter
552 AND a.organization_id = V_orgn_id
553 AND a.inventory_item_id = c.inventory_item_id;
554 END IF;
555 EXCEPTION
556 WHEN OTHERS THEN
557 fnd_msg_pub.add_exc_msg ('GMD_LCF_FETCH_PKG', 'Load_Tech_Values');
558 END load_tech_values;
559
560 /*##############################################################
561 # NAME
562 # load_items
563 # SYNOPSIS
564 # proc load_items
565 # DESCRIPTION
566 # This procedure inserts the data into temp tables and will
567 # be fetched in the form.
568 ###############################################################*/
569
570 PROCEDURE load_items (V_formulation_spec_id IN NUMBER, V_organization_id IN NUMBER,V_ingred_pick_base IN VARCHAR2,
571 V_formula_no IN VARCHAR2, V_batch_no IN VARCHAR2,V_date IN DATE) IS
572 CURSOR Cur_get_sim_material IS
573 SELECT a.*, b.concatenated_segments item
574 FROM gmd_material_details_gtmp a, mtl_system_items_kfv b
575 WHERE line_type = -1
576 AND a.inventory_item_id = b.inventory_item_id
577 AND b.organization_id = V_organization_id;
578
579 CURSOR Cur_get_spec IS
580 SELECT std_uom
581 FROM gmd_formulation_specs
582 WHERE formulation_spec_id = V_formulation_spec_id;
583
584 CURSOR Cur_get_formula IS
585 SELECT a.*, b.concatenated_segments, b.description, b.primary_uom_code
586 FROM gmd_material_reqs a, mtl_system_items_kfv b
587 WHERE formulation_spec_id = V_formulation_spec_id
588 AND a.inventory_item_id = b.inventory_item_id
589 AND b.organization_id = V_organization_id
590 ORDER BY b.concatenated_segments;
591
592 CURSOR Cur_get_comp IS
593 SELECT b.concatenated_segments, b.inventory_item_id,
594 b.description, b.primary_uom_code
595 FROM mtl_system_items_kfv b
596 WHERE EXISTS (SELECT 1
597 FROM gmd_lcf_category_hdr_gtmp a, mtl_item_categories c
598 WHERE a.category_set_id = c.category_set_id
599 AND a.category_id = c.category_id
600 AND b.organization_id = c.organization_id
601 AND c.organization_id = V_organization_id
602 AND b.inventory_item_id = c.inventory_item_id)
603 ORDER BY b.concatenated_segments;
604
605 CURSOR Cur_density (V_inv_item_id NUMBER,V_density_parameter VARCHAR2) IS
606 SELECT num_data
607 FROM gmd_technical_data_vl
608 WHERE organization_id = V_organization_id
609 AND inventory_item_id = V_inv_item_id
610 AND tech_parm_name = V_density_parameter;
611
612 l_value NUMBER;
613 l_line_id NUMBER DEFAULT 0;
614 l_line_no NUMBER DEFAULT 0;
615 l_new_qty NUMBER;
616 l_std_uom VARCHAR2(3);
617 X_return_status VARCHAR2(1);
618 l_density VARCHAR2(32);
619
620 l_formula_rec Cur_get_formula%ROWTYPE;
621 l_simulation_rec Cur_get_sim_material%ROWTYPE;
622 l_comp_rec Cur_get_comp%ROWTYPE;
623 BEGIN
624 l_density := FND_PROFILE.VALUE ('LM$DENSITY');
625 --Get the product uom
626 OPEN Cur_get_spec;
627 FETCH Cur_get_spec INTO l_std_uom;
628 CLOSE Cur_get_spec;
629 IF (V_batch_no IS NOT NULL OR V_formula_no IS NOT NULL) THEN
630 --if batch or formula number is passed then load the ingredients from simulator temp tables.
631 OPEN Cur_get_sim_material;
632 LOOP
633 FETCH Cur_get_sim_material INTO l_simulation_rec;
634 EXIT WHEN Cur_get_sim_material%NOTFOUND;
635 --Call the uom routine to convert item's primary uom from product uom
636 --defined in formulation specification screen.
637 OPEN Cur_density (l_simulation_rec.inventory_item_id, l_density);
638 FETCH Cur_density INTO l_value;
639 CLOSE Cur_density;
640 l_new_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_simulation_rec.inventory_item_id,
641 pformula_id => NULL,
642 plot_number => NULL,
643 pcur_qty => 1,
644 pcur_uom => l_simulation_rec.detail_uom,
645 pnew_uom => l_std_uom,
646 patomic => 0,
647 plab_id => V_organization_id,
648 pcnv_factor => l_value);
649 IF (l_new_qty = -99999) THEN
650 X_return_status := FND_API.g_ret_sts_error;
651 gmd_api_grp.log_message('IC_API_UOM_CONVERSION_ERROR', 'ITEM_NO',l_simulation_rec.item,
652 'FROM_UOM',l_simulation_rec.detail_uom,'TO_UOM',l_std_uom );
653 END IF;
654 INSERT INTO GMD_LCF_DETAILS_GTMP
655 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,CONCATENATED_SEGMENTS,
656 CONV_FACTOR,DESCRIPTION,DETAIL_UOM,PRIMARY_UOM,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
657 VALUES
658 (1,l_simulation_rec.line_id,l_simulation_rec.line_type,l_simulation_rec.line_no,0,
659 l_simulation_rec.inventory_item_id,l_simulation_rec.item,l_new_qty,
660 l_simulation_rec.description,l_simulation_rec.detail_uom,l_simulation_rec.detail_uom,
661 l_simulation_rec.created_by,l_simulation_rec.creation_date,
662 l_simulation_rec.last_updated_by,l_simulation_rec.last_update_date);
663 gmd_lcf_fetch_pkg.get_category_value (V_inventory_item_id => l_simulation_rec.inventory_item_id,
664 V_organization_id => V_organization_id,
665 V_line_id => l_simulation_rec.line_id);
666 END LOOP;
667 CLOSE Cur_get_sim_material;
668 ELSE
669 IF (V_formulation_spec_id IS NOT NULL) THEN
670 IF (V_ingred_pick_base = 'MAT') THEN
671 OPEN Cur_get_formula;
672 LOOP
673 l_line_id := l_line_id + 1;
674 FETCH Cur_get_formula INTO l_formula_rec;
675 EXIT WHEN Cur_get_formula%NOTFOUND;
676 --Call the uom routine to convert item's primary uom from product uom
677 --defined in formulation specification screen.
678 OPEN Cur_density (l_formula_rec.inventory_item_id, l_density);
679 FETCH Cur_density INTO l_value;
680 CLOSE Cur_density;
681 l_new_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_formula_rec.inventory_item_id,
682 pformula_id => NULL,
683 plot_number => NULL,
684 pcur_qty => 1,
685 pcur_uom => l_formula_rec.primary_uom_code,
686 pnew_uom => l_std_uom,
687 patomic => 0,
688 plab_id => V_organization_id,
689 pcnv_factor => l_value);
690 IF (l_new_qty = -99999) THEN
691 X_return_status := FND_API.g_ret_sts_error;
692 gmd_api_grp.log_message('IC_API_UOM_CONVERSION_ERROR', 'ITEM_NO',l_formula_rec.concatenated_segments,
693 'FROM_UOM',l_formula_rec.primary_uom_code,'TO_UOM',l_std_uom );
694 END IF;
695 INSERT INTO GMD_LCF_DETAILS_GTMP
696 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,CONCATENATED_SEGMENTS,
697 CONV_FACTOR,DESCRIPTION,DETAIL_UOM,PRIMARY_UOM,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
698 VALUES
699 (1,l_line_id,-1,l_formula_rec.line_no,0,l_formula_rec.inventory_item_id,l_formula_rec.concatenated_segments,
700 l_new_qty,l_formula_rec.description,l_formula_rec.primary_uom_code,l_formula_rec.primary_uom_code,
701 l_formula_rec.created_by,l_formula_rec.creation_date,
702 l_formula_rec.last_updated_by,l_formula_rec.last_update_date);
703 gmd_lcf_fetch_pkg.get_category_value (V_inventory_item_id => l_formula_rec.inventory_item_id,
704 V_organization_id => V_organization_id,
705 V_line_id => l_line_id);
706 END LOOP;
707 CLOSE Cur_get_formula;
708 ELSE
709 OPEN Cur_get_comp;
710 LOOP
711 l_line_id := l_line_id + 1;
712 l_line_no := l_line_no + 1;
713 FETCH Cur_get_comp INTO l_comp_rec;
714 EXIT WHEN Cur_get_comp%NOTFOUND;
715 --Call the uom routine to convert item's primary uom from product uom
716 --defined in formulation specification screen.
717 OPEN Cur_density (l_comp_rec.inventory_item_id, l_density);
718 FETCH Cur_density INTO l_value;
719 CLOSE Cur_density;
720 l_new_qty := gmd_labuom_calculate_pkg.uom_conversion (pitem_id => l_comp_rec.inventory_item_id,
721 pformula_id => NULL,
722 plot_number => NULL,
723 pcur_qty => 1,
724 pcur_uom => l_comp_rec.primary_uom_code,
725 pnew_uom => l_std_uom,
726 patomic => 0,
727 plab_id => V_organization_id,
728 pcnv_factor => l_value);
729 IF (l_new_qty = -99999) THEN
730 X_return_status := FND_API.g_ret_sts_error;
731 gmd_api_grp.log_message('IC_API_UOM_CONVERSION_ERROR', 'ITEM_NO',l_comp_rec.concatenated_segments,
732 'FROM_UOM',l_comp_rec.primary_uom_code,'TO_UOM',l_std_uom );
733 END IF;
734
735 INSERT INTO GMD_LCF_DETAILS_GTMP
736 (ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,CONCATENATED_SEGMENTS,
737 CONV_FACTOR,DESCRIPTION,DETAIL_UOM,PRIMARY_UOM)
738 VALUES
739 (1,l_line_id,-1,l_line_no,0,l_comp_rec.inventory_item_id,l_comp_rec.concatenated_segments,
740 l_new_qty,l_comp_rec.description,l_comp_rec.primary_uom_code,l_comp_rec.primary_uom_code);
741
742 gmd_lcf_fetch_pkg.get_category_value (V_inventory_item_id => l_comp_rec.inventory_item_id,
743 V_organization_id => V_organization_id,
744 V_line_id => l_line_id);
745 END LOOP;
746 CLOSE Cur_get_comp;
747 END IF;
748 END IF;
749 END IF;
750 --Call to load the item technical data.
751 gmd_lcf_fetch_pkg.load_tech_values (V_orgn_id => V_organization_id,
752 V_formulation_spec_id => V_formulation_spec_id,
753 V_date => V_date);
754 EXCEPTION
755 WHEN OTHERS THEN
756 fnd_msg_pub.add_exc_msg ('GMD_LCF_FETCH_PKG', 'Load_items');
757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
758 END load_items;
759
760 /*##############################################################
761 # NAME
762 # load_categories
763 # SYNOPSIS
764 # proc load_categories
765 # DESCRIPTION
766 # This procedure inserts the data into temp tables and will
767 # be fetched in the form.
768 ###############################################################*/
769
770 PROCEDURE load_categories (V_formulation_spec_id IN NUMBER) IS
771 BEGIN
772 IF (V_formulation_spec_id IS NOT NULL) THEN
773 INSERT INTO GMD_LCF_CATEGORY_HDR_GTMP
774 (CATEGORY_ID,CATEGORY_NAME,CATEGORY_SET_ID,
775 CATEGORY_SET_NAME,MIN_PCT,MAX_PCT)
776 SELECT gcr.category_id,mc.concatenated_segments,
777 gcr.category_set_id,mcs.category_set_name,
778 gcr.min_pct,gcr.max_pct
779 FROM mtl_category_sets mcs, mtl_categories_kfv mc, gmd_compositional_reqs gcr
780 WHERE mcs.category_set_id = gcr.category_set_id
781 AND mc.category_id = gcr.category_id
782 AND gcr.formulation_spec_id = V_formulation_spec_id
783 ORDER BY order_no;
784 END IF;
785 EXCEPTION
786 WHEN OTHERS THEN
787 fnd_msg_pub.add_exc_msg ('GMD_LCF_FETCH_PKG', 'Load_Categories');
788 END load_categories;
789
790 /*##############################################################
791 # NAME
792 # proc get_category_value
793 # SYNOPSIS
794 # proc get_category_value
795 # DESCRIPTION
796 # This procedure will see that a particular item is in category
797 # and based on that insert the date into temp table.
798 ###############################################################*/
799
800 PROCEDURE get_category_value (V_inventory_item_id IN NUMBER, V_organization_id IN NUMBER,
801 V_line_id IN NUMBER) IS
802 CURSOR Cur_check_hdr_category IS
803 SELECT category_id
804 FROM gmd_lcf_category_hdr_gtmp;
805
806 CURSOR Cur_check_item_category (V_category_id NUMBER) IS
807 SELECT 1
808 FROM mtl_item_categories
809 WHERE category_id = V_category_id
810 AND inventory_item_id = V_inventory_item_id
811 AND organization_id = V_organization_id;
812
813 l_value_ind NUMBER;
814 l_category_id NUMBER;
815 l_line_id NUMBER;
816 l_temp NUMBER;
817 BEGIN
818 FOR l_rec IN Cur_check_hdr_category LOOP
819 OPEN Cur_check_item_category(l_rec.category_id);
820 FETCH Cur_check_item_category INTO l_temp;
821 IF (Cur_check_item_category%FOUND) THEN
822 l_value_ind := 1;
823 ELSE
824 l_value_ind := 0;
825 END IF;
826 CLOSE Cur_check_item_category;
827 INSERT INTO GMD_LCF_CATEGORY_DTL_GTMP (LINE_ID,VALUE_IND,CATEGORY_ID)
828 VALUES (V_line_id,l_value_ind,l_rec.category_id);
829 END LOOP;
830 EXCEPTION
831 WHEN OTHERS THEN
832 fnd_msg_pub.add_exc_msg ('GMD_LCF_FETCH_PKG', 'Load_Category_Value');
833 END get_category_value;
834
835 /*##############################################################
836 # NAME
837 # load_quality_data
838 # SYNOPSIS
839 # proc load_quality_data
840 # DESCRIPTION
841 # This procedure inserts the data into temp tables from quality
842 # tables.
843 ###############################################################*/
844
845 PROCEDURE load_quality_data (V_line_id IN NUMBER, V_orgn_id IN NUMBER,
846 V_qcassy_typ_id IN NUMBER,V_tech_parm_id IN NUMBER) IS
847
848 CURSOR Cur_get_data IS
849 SELECT *
850 FROM gmd_lcf_details_gtmp
851 WHERE line_id = V_line_id;
852
853 l_rec Cur_get_data%ROWTYPE;
854 l_return_status VARCHAR2(1);
855 x_return_status VARCHAR2(1);
856 l_value VARCHAR2(80);
857 l_inv_inp_rec_type GMD_QUALITY_GRP.inv_inp_rec_type;
858 l_inv_val_out_rec_type GMD_QUALITY_GRP.inv_val_out_rec_type;
859 BEGIN
860 OPEN Cur_get_data;
861 FETCH Cur_get_data INTO l_rec;
862 CLOSE Cur_get_data;
863 l_inv_inp_rec_type.organization_id := V_orgn_id;
864 l_inv_inp_rec_type.inventory_item_id := l_rec.inventory_item_id;
865 l_inv_inp_rec_type.grade_code := l_rec.grade_code;
866 l_inv_inp_rec_type.lot_number := l_rec.lot_number;
867 l_inv_inp_rec_type.subinventory := l_rec.subinventory_code;
868 l_inv_inp_rec_type.locator_id := l_rec.locator_id;
869 l_inv_inp_rec_type.plant_id := NULL;
870 l_inv_inp_rec_type.test_id := V_qcassy_typ_id;
871 gmd_quality_grp.get_inv_test_value (P_inv_test_inp_rec => l_inv_inp_rec_type,
872 x_inv_test_out_rec => l_inv_val_out_rec_type,
873 x_return_status => l_return_status);
874 l_value := l_inv_val_out_rec_type.entity_value;
875
876 IF (l_value IS NOT NULL) THEN
877 UPDATE gmd_lcf_tech_data_gtmp
878 SET value = l_inv_val_out_rec_type.entity_value,
879 num_data = l_inv_val_out_rec_type.entity_value
880 WHERE line_id = V_line_id
881 AND tech_parm_id = V_tech_parm_id;
882 END IF;
883 EXCEPTION
884 WHEN OTHERS THEN
885 fnd_msg_pub.add_exc_msg ('GMD_LCF_FETCH_PKG', 'load_quality_data');
886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
887 NULL;
888 END load_quality_data;
889
890 /*##############################################################
891 # NAME
892 # generate_lcf_data
893 # SYNOPSIS
894 # proc generate_lcf_data
895 # DESCRIPTION
896 # This procedure inserts the data into rows and columns then
897 # call the lcf engine.
898 ###############################################################*/
899
900 PROCEDURE generate_lcf_data (V_formulation_spec_id IN NUMBER, V_organization_id IN NUMBER,
901 V_formula_no IN VARCHAR2, V_batch_no IN VARCHAR2,V_date IN DATE,
902 X_return_code OUT NOCOPY NUMBER) IS
903
904 --Formulation specification details.
905 CURSOR Cur_get_formulation IS
906 SELECT *
907 FROM gmd_formulation_specs
908 WHERE formulation_spec_id = V_formulation_spec_id;
909
910 --Line id to be used to identify the specific line id number so that
911 --we can pass the value as 1 for that item and 0 to others.
912 CURSOR Cur_get_lineid IS
913 SELECT line_id, inventory_item_id, concatenated_segments, conv_factor
914 FROM gmd_lcf_details_gtmp
915 ORDER BY line_id;
916
917 --Get the total count of material rows.
918 CURSOR Cur_get_line_count IS
919 SELECT COUNT(*)
920 FROM gmd_lcf_details_gtmp;
921
922 --Get the technical parameter values for the paramter defined in the
923 --formulation specification screen as optimize function.
924 CURSOR Cur_get_optprm_value (V_tech_parm_id NUMBER, V_line_id NUMBER) IS
925 SELECT value
926 FROM gmd_lcf_tech_data_gtmp
927 WHERE tech_parm_id = V_tech_parm_id
928 AND line_id = V_line_id;
929
930 --Get the material requirements defined for particular formulation specification.
931 CURSOR Cur_get_matl_req IS
932 SELECT a.inventory_item_id, a.min_qty, a.max_qty,
933 a.item_uom, a.range_type, b.concatenated_segments
934 FROM gmd_material_reqs a, gmd_lcf_details_gtmp b
935 WHERE (a.min_qty IS NOT NULL OR a.max_qty IS NOT NULL)
936 AND a.formulation_spec_id = V_formulation_spec_id
937 AND a.inventory_item_id = b.inventory_item_id
938 ORDER BY b.line_id;
939
940 --Get the line id and other details for item id passed from
941 --the above requirement cursor
942 CURSOR Cur_get_matreq_line (V_inventory_item_id NUMBER) IS
943 SELECT line_id, concatenated_segments, conv_factor
944 FROM gmd_lcf_details_gtmp
945 WHERE inventory_item_id = V_inventory_item_id
946 ORDER BY line_id;
947
948 --Get the compositional requirements defined for particular formulation specification.
949 CURSOR Cur_get_comp_req (V_formulation_spec_id NUMBER) IS
950 SELECT a.category_id, a.min_pct, a.max_pct, b.category_name
951 FROM gmd_compositional_reqs a, gmd_lcf_category_hdr_gtmp b
952 WHERE a.category_id = b.category_id
953 AND (a.min_pct IS NOT NULL OR a.max_pct IS NOT NULL)
954 AND a.formulation_spec_id = V_formulation_spec_id
955 ORDER BY order_no;
956
957 --Get the line id(number like 3 or 4 etc) for the category id passed from
958 --the above requirement cursor
959 CURSOR Cur_get_category (V_category_id NUMBER) IS
960 SELECT line_id
961 FROM gmd_lcf_category_dtl_gtmp
962 WHERE category_id = V_category_id
963 AND value_ind = 1;
964
965 --Get the technical requirements defined for particular formulation specification.
966 CURSOR Cur_get_tech_req (V_formulation_spec_id NUMBER) IS
967 SELECT a.tech_parm_id,a.min_value, a.max_value, b.tech_parm_name
968 FROM gmd_technical_reqs a, gmd_tech_parameters_b b
969 WHERE a.tech_parm_id = b.tech_parm_id
970 AND (a.min_value IS NOT NULL OR a.max_value IS NOT NULL)
971 AND a.formulation_spec_id = V_formulation_spec_id;
972 -- ORDER BY a.tech_parm_id;
973
974 --Get the technical parameter value and line id and for the above tech parameters.
975 CURSOR Cur_get_tech_value (V_tech_parm_id NUMBER) IS
976 SELECT line_id,value
977 FROM gmd_lcf_tech_data_gtmp
978 WHERE tech_parm_id = V_tech_parm_id
979 ORDER BY line_id;
980
981 --Get the lineid from details table to update the qty in categories dtl table.
982 CURSOR Cur_get_line_dtl (V_item VARCHAR2) IS
983 SELECT line_id, inventory_item_id, detail_uom, conv_factor
984 FROM gmd_lcf_details_gtmp
985 WHERE concatenated_segments = V_item;
986
987 CURSOR Cur_get_conv_factor (V_line_id NUMBER) IS
988 SELECT conv_factor
989 FROM gmd_lcf_details_gtmp
990 WHERE line_id = V_line_id;
991
992 l_new_qty NUMBER;
993 l_min_qty NUMBER;
994 l_max_qty NUMBER;
995 l_prod_qty NUMBER;
996 l_count NUMBER;
997 l_line_id NUMBER;
998 l_conv_factor NUMBER;
999 l_inv_item_id NUMBER;
1000 l_dtl_lineid NUMBER;
1001 j_return NUMBER;
1002 l_detail_uom VARCHAR2(3);
1003 x_return_status VARCHAR2(1);
1004
1005 l_line_row NUMBER := 0;
1006 l_row NUMBER := 1;
1007 l_rt_row NUMBER := 1;
1008 i NUMBER := 0;
1009
1010 l_formulation Cur_get_formulation%ROWTYPE;
1011 l_lt_matrix gmd_lcf_engine.matrix;
1012 l_rt_matrix gmd_lcf_engine.char_matrix;
1013 l_var gmd_lcf_engine.char_row;
1014 l_solved_tab gmd_lcf_engine.solved_tab;
1015
1016 --Following are used to print the matrix data for debug.
1017 l_print_line VARCHAR2(2000);
1018 l_value NUMBER;
1019 l_print_value VARCHAR2(40);
1020
1021 BEGIN
1022 gmd_debug.log_initialize('LCF');
1023 --Load formulation specification details
1024 OPEN Cur_get_formulation;
1025 FETCH Cur_get_formulation INTO l_formulation;
1026 CLOSE Cur_get_formulation;
1027
1028 --Call the load items procedure here to load the material lines and there item technical data.
1029 gmd_lcf_fetch_pkg.load_items (V_formulation_spec_id => V_formulation_spec_id,
1030 V_organization_id => V_organization_id,
1031 V_ingred_pick_base => l_formulation.ingred_pick_base_ind,
1032 V_formula_no => V_formula_no,
1033 V_batch_no => V_batch_no,
1034 V_date => V_date);
1035 --Get the material line count
1036 OPEN Cur_get_line_count;
1037 FETCH Cur_get_line_count INTO l_count;
1038 CLOSE Cur_get_line_count;
1039
1040 --Product qty after the process loss if any
1041 --IF (l_formulation.process_loss IS NOT NULL) THEN
1042 --l_prod_qty := (l_formulation.std_qty - l_formulation.process_loss);
1043 --ELSE
1044 l_prod_qty := l_formulation.std_qty;
1045 --END IF;
1046
1047 --For each line get the technical parameter value defined as objective ind in formulation
1048 --Specification screen.
1049 FOR l_rec IN Cur_get_lineid LOOP
1050 FOR l_value IN Cur_get_optprm_value (l_formulation.tech_parm_id,l_rec.line_id) LOOP
1051 i := i + 1;
1052 l_lt_matrix(0)(i) := NVL(l_value.value,0);
1053 END LOOP;
1054 END LOOP;
1055
1056 --Standard(Gross weight) qty defined.
1057 l_rt_matrix(1) (0) := 'Standard Qty';
1058 l_rt_matrix(1) (1) := l_prod_qty;
1059 l_rt_matrix(1) (2) := 1e20;
1060 l_rt_matrix(1) (3) := 1e20;
1061
1062 --Get all material lines Pass the value 1 to them.
1063 FOR l_rec1 IN Cur_get_lineid LOOP
1064 l_line_row := l_line_row + 1;
1065 l_lt_matrix(1)(l_line_row) := l_rec1.conv_factor;
1066 l_var(l_line_row) := l_rec1.concatenated_segments;
1067 END LOOP;
1068
1069 --Get the requirements defined in the material requirement screen.
1070 FOR l_mat IN Cur_get_matl_req LOOP
1071 --Get the lineid for each item defined as mateiral requirement
1072 l_line_id := 1;
1073 IF l_mat.min_qty IS NOT NULL THEN
1074 --Increment the row numbers for left and right matrix
1075 l_row := l_row + 1;
1076 l_rt_row := l_rt_row + 1;
1077 FOR l_mat_line IN Cur_get_matreq_line (l_mat.inventory_item_id) LOOP
1078
1079 --Pass value 0 to all the items which has no min qty defined.
1080 FOR i IN l_line_id .. (l_mat_line.line_id - 1) loop
1081 l_lt_matrix(l_row)(i) := 0;
1082 END LOOP;
1083
1084 --Pass value 1 all the items which has min qty defined.
1085 l_lt_matrix(l_row)(l_mat_line.line_id) := l_mat_line.conv_factor;
1086 l_line_id := l_mat_line.line_id + 1;
1087 END LOOP; --FOR l_mat_line IN Cur_get_matreq_line (l_mat.inventory_item_id) LOOP
1088
1089 FOR i IN l_line_id .. l_count loop
1090 l_lt_matrix(l_row)(i) := 0;
1091 END LOOP;
1092 --If the range type is % then multiply the min qty with product qty and divide by 100
1093 IF (l_mat.range_type = 0) THEN
1094 l_min_qty := ((l_prod_qty * l_mat.min_qty) / 100);
1095 ELSE
1096 l_min_qty := l_mat.min_qty;
1097 END IF;
1098 l_rt_matrix(l_rt_row)(0) := l_mat.concatenated_segments;
1099 l_rt_matrix(l_rt_row)(1) := l_min_qty;
1100 l_rt_matrix(l_rt_row)(2) := 1e20;
1101 l_rt_matrix(l_rt_row)(3) := 0;
1102 END IF; --IF l_mat.min_qty IS NOT NULL THEN
1103
1104 --Pass the value 0 all the items which has no max qty defined.
1105 l_line_id := 1;
1106 IF l_mat.max_qty IS NOT NULL THEN
1107 --Increment the row numbers for left and right matrix
1108 l_row := l_row + 1;
1109 l_rt_row := l_rt_row + 1;
1110 FOR l_mat_line IN Cur_get_matreq_line (l_mat.inventory_item_id) LOOP
1111 FOR i IN l_line_id .. (l_mat_line.line_id - 1) loop
1112 l_lt_matrix(l_row)(i) := 0;
1113 END LOOP;
1114
1115 --Pass value 1 all the items which has max qty defined.
1116 l_lt_matrix(l_row)(l_mat_line.line_id) := l_mat_line.conv_factor;
1117 l_line_id := l_mat_line.line_id + 1;
1118 END LOOP; --FOR l_mat_line IN Cur_get_matreq_line (l_mat.inventory_item_id) LOOP
1119
1120 FOR i IN l_line_id .. l_count loop
1121 l_lt_matrix(l_row)(i) := 0;
1122 END LOOP;
1123
1124 --If the range type is % then multiply the min qty with product qty and divide by 100
1125 IF (l_mat.range_type = 0) THEN
1126 l_max_qty := ((l_prod_qty * l_mat.max_qty) / 100);
1127 ELSE
1128 l_max_qty := l_mat.max_qty;
1129 END IF;
1130 l_rt_matrix(l_rt_row)(0) := l_mat.concatenated_segments;
1131 l_rt_matrix(l_rt_row)(1) := l_max_qty;
1132 l_rt_matrix(l_rt_row)(2) := 0;
1133 l_rt_matrix(l_rt_row)(3) := 1e20;
1134 END IF; --IF l_mat.max_qty IS NOT NULL THEN
1135 END LOOP; --FOR l_mat IN Cur_get_matl_req (V_formulation_spec_id) LOOP
1136
1137 --Get the requirements defined in the compositional requirement screen.
1138 FOR l_comp IN Cur_get_comp_req (V_formulation_spec_id) LOOP
1139 l_line_id := 1;
1140 --Get the lineid for each category defined as compositional requirement
1141 IF l_comp.min_pct IS NOT NULL THEN
1142 --Increment the row numbers for left and right matrix
1143 l_row := l_row + 1;
1144 l_rt_row := l_rt_row + 1;
1145 FOR l_comp_rec IN Cur_get_category (l_comp.category_id) LOOP
1146 --Get the conversion factor for each line and pass it to the matrix
1147 FOR l_factor IN Cur_get_conv_factor (l_comp_rec.line_id) LOOP
1148 --Pass the value 0 to all the items which has no min pct defined.
1149 FOR i IN l_line_id .. (l_comp_rec.line_id - 1) LOOP
1150 l_lt_matrix(l_row)(i) := 0;
1151 END LOOP;
1152
1153 l_lt_matrix(l_row)(l_comp_rec.line_id) := l_factor.conv_factor;
1154 l_line_id := l_comp_rec.line_id + 1;
1155 END LOOP; -- FOR l_factor IN Cur_get_conv_factor (l_comp_rec.line_id) LOOP
1156 END LOOP; --FOR l_comp_rec IN Cur_get_category (l_comp.category_id) LOOP
1157
1158 --Pass the value 0 to all other items
1159 FOR i IN l_line_id .. l_count LOOP
1160 l_lt_matrix(l_row)(i) := 0;
1161 END LOOP;
1162
1163 --Pass the item name min pct value to the matirx.
1164 l_rt_matrix(l_rt_row)(0) := l_comp.category_name;
1165 l_rt_matrix(l_rt_row)(1) := ((l_prod_qty * l_comp.min_pct) /100);
1166 l_rt_matrix(l_rt_row)(2) := 1e20;
1167 l_rt_matrix(l_rt_row)(3) := 0;
1168 END IF; --IF l_comp.min_pct IS NOT NULL THEN
1169
1170 l_line_id := 1;
1171
1172 IF l_comp.max_pct IS NOT NULL THEN
1173 --Increment the row numbers for left and right matrix
1174 l_row := l_row + 1;
1175 l_rt_row := l_rt_row + 1;
1176 FOR l_comp_rec IN Cur_get_category (l_comp.category_id) LOOP
1177 --Get the conversion factor for each line and pass it to the matrix
1178 FOR l_factor IN Cur_get_conv_factor (l_comp_rec.line_id) LOOP
1179 --Pass the value 0 to all the items which has no min pct defined.
1180 FOR i IN l_line_id .. (l_comp_rec.line_id - 1) LOOP
1181 l_lt_matrix(l_row)(i) := 0;
1182 END LOOP;
1183
1184 l_lt_matrix(l_row)(l_comp_rec.line_id) := l_factor.conv_factor;
1185 l_line_id := l_comp_rec.line_id + 1;
1186 END LOOP; --FOR l_factor IN Cur_get_conv_factor (l_comp_rec.line_id) LOOP
1187 END LOOP; --FOR l_comp_rec IN Cur_get_category (l_comp.category_id) LOOP
1188
1189 --Pass the value 0 to all other items
1190 FOR i IN l_line_id .. l_count LOOP
1191 l_lt_matrix(l_row)(i) := 0;
1192 END LOOP;
1193
1194 --Pass the item name max pct value to the matirx.
1195 l_rt_matrix(l_rt_row)(0) := l_comp.category_name;
1196 l_rt_matrix(l_rt_row)(1) := ((l_prod_qty * l_comp.max_pct) /100);
1197 l_rt_matrix(l_rt_row)(2) := 0;
1198 l_rt_matrix(l_rt_row)(3) := 1e20;
1199 END IF; --IF l_comp.max_pct IS NOT NULL THEN
1200 END LOOP; --FOR l_comp IN Cur_get_comp_req (V_formulation_spec_id) LOOP
1201
1202 --Get the requirements defined in the compositional requirement screen.
1203 FOR l_tech IN Cur_get_tech_req (V_formulation_spec_id) LOOP
1204 l_line_id := 1;
1205
1206 IF l_tech.min_value IS NOT NULL THEN
1207 --Increment the row numbers for left and right matrix
1208 l_row := l_row + 1;
1209 l_rt_row := l_rt_row + 1;
1210 --Get the lineid for each parameter defined as technical requirement
1211 FOR l_tech_value IN Cur_get_tech_value (l_tech.tech_parm_id) LOOP
1212 --Get the conversion factor for each line and pass it to the matrix
1213 FOR l_factor IN Cur_get_conv_factor (l_tech_value.line_id) LOOP
1214 --Pass the value 0 to all the items which has no min pct defined.
1215 FOR i IN l_line_id .. (l_tech_value.line_id - 1) LOOP
1216 l_lt_matrix(l_row)(i) := 0;
1217 END LOOP;
1218
1219 --Pass the tech parameter value to all the items which has min value defined.
1220 l_lt_matrix(l_row)(l_tech_value.line_id) := (NVL(l_tech_value.value,0)* l_factor.conv_factor);
1221 l_line_id := l_tech_value.line_id + 1;
1222 END LOOP; --FOR l_factor IN Cur_get_conv_factor (l_tech_value.line_id) LOOP
1223 END LOOP; --FOR l_tech_value IN Cur_get_tech_value (l_tech.tech_parm_id) LOOP
1224
1225 --Pass the value 0 to all other items
1226 FOR i IN l_line_id .. l_count LOOP
1227 l_lt_matrix(l_row)(i) := 0;
1228 END LOOP;
1229
1230 --Pass the item name min pct value to the matirx.
1231 l_rt_matrix(l_rt_row)(0) := l_tech.tech_parm_name;
1232 l_rt_matrix(l_rt_row)(1) := (l_prod_qty * l_tech.min_value);
1233 l_rt_matrix(l_rt_row)(2) := 1e20;
1234 l_rt_matrix(l_rt_row)(3) := 0;
1235 END IF; --IF l_tech.min_value IS NOT NULL THEN
1236
1237 l_line_id := 1;
1238
1239 IF l_tech.max_value IS NOT NULL THEN
1240 --Increment the row numbers for left and right matrix
1241 l_row := l_row + 1;
1242 l_rt_row := l_rt_row + 1;
1243 --Get the lineid for each parameter defined as technical requirement
1244 FOR l_tech_value IN Cur_get_tech_value (l_tech.tech_parm_id) LOOP
1245 --Get the conversion factor for each line and pass it to the matrix
1246 FOR l_factor IN Cur_get_conv_factor (l_tech_value.line_id) LOOP
1247 --Pass the value 0 to all the items which has no min pct defined.
1248 FOR i IN l_line_id .. (l_tech_value.line_id - 1) LOOP
1249 l_lt_matrix(l_row)(i) := 0;
1250 END LOOP;
1251
1252 --Pass the tech parameter value to all the items which has max value defined.
1253 l_lt_matrix(l_row)(l_tech_value.line_id) := (NVL(l_tech_value.value,0)* l_factor.conv_factor);
1254 l_line_id := l_tech_value.line_id + 1;
1255 END LOOP;
1256 END LOOP; --FOR l_tech_value IN Cur_get_tech_value (l_tech.tech_parm_id) LOOP
1257
1258 --Pass the value 0 to all other items
1259 FOR i IN l_line_id .. l_count LOOP
1260 l_lt_matrix(l_row)(i) := 0;
1261 END LOOP;
1262
1263 --Pass the item name min pct value to the matirx.
1264 l_rt_matrix(l_rt_row)(0) := l_tech.tech_parm_name;
1265 l_rt_matrix(l_rt_row)(1) := (l_prod_qty * l_tech.max_value);
1266 l_rt_matrix(l_rt_row)(2) := 0;
1267 l_rt_matrix(l_rt_row)(3) := 1e20;
1268 END IF; --IF l_tech.max_value IS NOT NULL THEN
1269 END LOOP; --FOR l_tech IN Cur_get_tech_req (V_formulation_spec_id) LOOP
1270
1271 --change the constraints in the first loop and number of varaibles in second
1272 /*FOR i IN 0..14 LOOP
1273 l_print_line := NULL;
1274 FOR j IN 1..16 LOOP
1275 l_value := ROUND(l_lt_matrix(i)(j),5);
1276 l_print_value := RPAD(TO_CHAR(l_value, '9990.99999'), 10);
1277 l_print_line := l_print_line||l_print_value||' ';
1278 END LOOP;
1279 gmd_debug.put_line(l_print_line);
1280 END LOOP; */
1281
1282 --After builiding the matrix call the lcf engine routine.
1283 gmd_lcf_engine.evaluate (P_spec_id => V_formulation_spec_id,
1284 P_constraints => l_lt_matrix.count - 1,
1285 P_variables => l_count,
1286 P_matrix => l_lt_matrix,
1287 p_rhs_matrix => l_rt_matrix,
1288 p_var_row => l_var,
1289 X_solved_tab => l_solved_tab,
1290 X_return => j_return);
1291 X_return_code := j_return;
1292 FOR i IN 1 .. l_solved_tab.count LOOP
1293 OPEN Cur_get_line_dtl(l_solved_tab(i).item);
1294 FETCH Cur_get_line_dtl INTO l_dtl_lineid,l_inv_item_id,l_detail_uom,l_conv_factor;
1295 CLOSE Cur_get_line_dtl;
1296
1297 --Update the qty for each category line id returned from above cursor.
1298 UPDATE gmd_lcf_category_dtl_gtmp
1299 SET qty = l_solved_tab(i).qty
1300 WHERE line_id = l_dtl_lineid;
1301
1302 l_new_qty := l_solved_tab(i).qty;
1303
1304 --Update quantities for the item returned from the uom routine.
1305 UPDATE gmd_lcf_details_gtmp
1306 SET qty = l_new_qty
1307 WHERE concatenated_segments = l_solved_tab(i).item;
1308 END LOOP;
1309
1310 --Delete the items where qty is null
1311 DELETE
1312 FROM gmd_lcf_details_gtmp
1313 WHERE qty IS NULL OR qty = 0;
1314
1315 EXCEPTION
1316 WHEN OTHERS THEN
1317 fnd_msg_pub.add_exc_msg ('GMD_LCF_FETCH_PKG', 'Generate_Lcf_Data');
1318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1319 NULL;
1320 END generate_lcf_data;
1321
1322
1323
1324
1325 /* following procedures are wrote to debug the procedures materials and technical data */
1326
1327 PROCEDURE temp_dump IS
1328 cursor cur_rec IS
1329 select *
1330 from gmd_lcf_details_gtmp;
1331 BEGIN
1332 FOR L_RECORD IN CUR_REC LOOP
1333 gmd_debug.put_line('item_no'||l_record.concatenated_segments);
1334 gmd_debug.put_line('detail_uom'||l_record.detail_uom);
1335 gmd_debug.put_line('line_id'||l_record.line_id);
1336 gmd_debug.put_line('qty'||l_record.qty);
1337 gmd_debug.put_line('min_qty'||l_record.min_qty);
1338 gmd_debug.put_line('max_qty'||l_record.max_qty);
1339 gmd_debug.put_line('entity'||l_record.entity_id);
1340 gmd_debug.put_line('qtymass'||l_record.qty_mass);
1341 gmd_debug.put_line('massuom'||l_record.mass_uom);
1342 gmd_debug.put_line('qtyvol'||l_record.qty_vol);
1343 gmd_debug.put_line('voluom'||l_record.vol_uom);
1344 END LOOP;
1345 END temp_dump;
1346
1347 procedure temp_param IS
1348 cursor cur_rec1 IS
1349 select a.*,b.concatenated_segments
1350 from gmd_lcf_tech_data_gtmp a, gmd_lcf_details_gtmp b
1351 where a.line_id= b.line_id;
1352 begin
1353 FOR L_REC IN CUR_REC1 LOOP
1354 gmd_debug.put_line('item lineid techparmname value');
1355 gmd_debug.put_line(l_rec.concatenated_segments|| '-' ||l_rec.line_id|| '-' ||l_rec.tech_parm_name|| '-' ||l_rec.value);
1356 END LOOP;
1357 end temp_param;
1358
1359 procedure temp_category IS
1360 cursor cur_rec1 IS
1361 select *
1362 from gmd_lcf_category_dtl_gtmp;
1363 begin
1364 FOR L_REC IN CUR_REC1 LOOP
1365 gmd_debug.put_line('category lineid value_ind');
1366 gmd_debug.put_line(l_rec.category_id|| '-' ||l_rec.line_id|| '-' ||l_rec.value_ind);
1367 END LOOP;
1368 end temp_category;
1369
1370
1371 END GMD_LCF_FETCH_PKG;
[Home] [Help]