[Home] [Help]
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:
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:
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)
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:
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:
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)
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
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
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
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
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;
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;
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,
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;
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.
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.
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');