[Home] [Help]
19: V_orgn_id IN NUMBER,V_update_inv_ind IN VARCHAR2,
20: V_plant_id IN NUMBER) IS
21: BEGIN
22: DELETE FROM gmd_material_details_gtmp;
23: DELETE FROM gmd_technical_data_gtmp;
24: IF (V_sprd_id IS NOT NULL) THEN
25: gmd_spread_fetch_pkg.load_spread_details(V_entity_id,V_sprd_id,V_orgn_id);
26: ELSIF (V_batch_id IS NOT NULL AND V_spec_id IS NULL) THEN
27: gmd_spread_fetch_pkg.load_batch_details(V_entity_id,V_batch_id,V_orgn_id,V_update_inv_ind,V_plant_id);
1052: ORDER BY line_type;
1053: BEGIN
1054: /* Inserting the technical parameter data of item and lot to temp tables*/
1055: IF (V_sprd_id IS NOT NULL) THEN
1056: INSERT INTO GMD_TECHNICAL_DATA_GTMP
1057: (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
1058: VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
1059: SELECT a.sprd_id,c.line_id,a.tech_parm_name,a.tech_parm_id,
1060: DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
1091: ORDER BY line_type;
1092: BEGIN
1093: /* Inserting the technical parameter data of item and lot to temp tables*/
1094: IF (V_batch_id IS NOT NULL) THEN
1095: INSERT INTO GMD_TECHNICAL_DATA_GTMP
1096: (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
1097: VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
1098: SELECT V_entity_id,c.line_id,a.tech_parm_name, a.tech_parm_id,
1099: DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
1154: ORDER BY line_type;
1155: BEGIN
1156: /* Inserting the technical parameter data of item and lot to temp tables*/
1157: IF (V_formula_id IS NOT NULL) THEN
1158: INSERT INTO GMD_TECHNICAL_DATA_GTMP
1159: (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
1160: VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
1161: SELECT V_entity_id,c.line_id,a.tech_parm_name,a.tech_parm_id,
1162: DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
1302: boolean_data,last_updated_by,last_update_date,created_by,creation_date)
1303: SELECT X_line_id, V_orgn_id, tech_parm_name,tech_parm_id,
1304: X_sprd_id,sort_seq,num_data,text_data,boolean_data,
1305: l_user_id,sysdate,l_user_id,sysdate
1306: FROM gmd_technical_data_gtmp
1307: WHERE entity_id = V_entity_id
1308: AND line_id = l_rec.line_id;
1309: END LOOP;
1310: EXCEPTION
1324: FUNCTION get_density_value (V_line_id IN NUMBER,
1325: V_density_parameter IN VARCHAR2) RETURN NUMBER IS
1326: CURSOR Cur_density IS
1327: SELECT value
1328: FROM gmd_technical_data_gtmp
1329: WHERE line_id = V_line_id
1330: AND tech_parm_name = V_density_parameter;
1331: l_value NUMBER;
1332: BEGIN
1532: l_inv_val_out_rec_type GMD_QUALITY_GRP.inv_val_out_rec_type;
1533:
1534: CURSOR Cur_get_value(Pline_id NUMBER,Pparm_id NUMBER) IS
1535: SELECT value
1536: FROM gmd_technical_data_gtmp
1537: WHERE line_id = Pline_id
1538: AND tech_parm_id = Pparm_id;
1539:
1540: l_temp VARCHAR2(80);
1575: ELSE
1576: l_char_value := l_value;
1577: l_num_value := NULL;
1578: END IF;
1579: UPDATE gmd_technical_data_gtmp
1580: SET value = l_inv_val_out_rec_type.entity_value,
1581: num_data = l_num_value,
1582: qm_entity_id = l_inv_val_out_rec_type.entity_id,
1583: qm_level = l_inv_val_out_rec_type.level,
1584: text_data = l_char_value
1585: WHERE line_id = V_line_id
1586: AND tech_parm_id = l_qmrec.tech_parm_id;
1587: IF SQL%NOTFOUND THEN
1588: INSERT INTO GMD_TECHNICAL_DATA_GTMP
1589: (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,VALUE,SORT_SEQ,NUM_DATA,
1590: TEXT_DATA,QM_ENTITY_ID,QM_LEVEL,COMP_IND,MIN_VALUE,MAX_VALUE,SPEC_ID)
1591: VALUES (l_rec.entity_id,V_line_id,l_qmrec.tech_parm_name,l_qmrec.tech_parm_id,l_inv_val_out_rec_type.entity_value,
1592: l_qmrec.sort_seq,l_num_value,l_char_value,
1593: l_inv_val_out_rec_type.entity_id,l_inv_val_out_rec_type.level,l_inv_val_out_rec_type.composite_ind,
1594: l_inv_val_out_rec_type.entity_min_value,l_inv_val_out_rec_type.entity_max_value,l_inv_val_out_rec_type.spec_id);
1595: END IF;
1596: ELSE
1597: UPDATE gmd_technical_data_gtmp
1598: SET value = l_inv_val_out_rec_type.entity_value,
1599: num_data = l_num_value,
1600: qm_entity_id = l_inv_val_out_rec_type.entity_id,
1601: qm_level = l_inv_val_out_rec_type.level,
1634: AND organization_id = P_orgn_id;
1635:
1636: CURSOR cur_get_value (v_tech_parm_id NUMBER) IS
1637: SELECT a.value
1638: FROM gmd_technical_data_gtmp a, gmd_material_details_gtmp c
1639: WHERE a.tech_parm_id = v_tech_parm_id
1640: AND c.parent_line_id = P_parent_detl_id
1641: AND a.line_id = c.line_id
1642: AND c.line_type <> 3;
1646: FROM gmd_material_details_gtmp p
1647: WHERE parent_line_id = P_parent_detl_id
1648: AND line_type = 3
1649: AND NOT EXISTS ( SELECT 1
1650: FROM gmd_technical_data_gtmp g
1651: WHERE p.line_id = g.line_id
1652: AND g.tech_parm_id = v_tech_parm_id);
1653:
1654: /* Local variables */
1668: CLOSE cur_get_value;
1669:
1670: IF (l_value IS NOT NULL) THEN
1671: FOR l_rec IN cur_get_data (l_tech_parm_id) LOOP
1672: INSERT INTO gmd_technical_data_gtmp
1673: (entity_id, line_id, tech_parm_name, tech_parm_id,
1674: Value, sort_seq, num_data, TEXT_DATA, BOOLEAN_DATA)
1675: VALUES
1676: (P_entity_id, l_rec.line_id, l_density, l_tech_parm_id,
1782: ORDER BY line_type;
1783: BEGIN
1784: /* Inserting the technical parameter data of item and lot to temp tables*/
1785: IF (V_entity_id IS NOT NULL) THEN
1786: INSERT INTO GMD_TECHNICAL_DATA_GTMP
1787: (ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
1788: VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
1789: SELECT V_entity_id,c.line_id,b.tech_parm_name,a.tech_parm_id,
1790: DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
1840: V_date => SYSDATE,
1841: V_cost_orgn => l_parm_value,
1842: V_source => l_rec.cost_source,
1843: X_value => l_value);
1844: INSERT INTO GMD_TECHNICAL_DATA_GTMP (ENTITY_ID,LINE_ID,TECH_PARM_NAME,
1845: TECH_PARM_ID,VALUE,NUM_DATA,SORT_SEQ)
1846: VALUES (V_entity_id,l_rec.line,l_rec.name,l_rec.tech,l_value,l_value,l_rec.sort_seq);
1847: END IF;
1848: END LOOP;