[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(level)
FROM gmd_parsed_expression p
WHERE expression_type <> -1
START WITH p.tech_parm_id = V_tech_parm_id
CONNECT BY PRIOR expression_parm_id = p.tech_parm_id
AND (p.data_type <> 11 OR p.tech_parm_id <> p.expression_parm_id);
/*SELECT max(level)
FROM gmd_parsed_expression p, gmd_tech_parameters_b b
WHERE expression_type <> -1
AND p.tech_parm_id = b.tech_parm_id (+)
START WITH p.tech_parm_id = V_tech_parm_id
CONNECT BY PRIOR expression_parm_id = p.tech_parm_id
AND (b.data_type <> 11 or p.tech_parm_id <> expression_parm_id);*/
/* Delete any parsed expression which was existing already */
DELETE FROM gmd_parsed_expression
WHERE tech_parm_id = p_tech_parm_id;
/*If their is a preceding string then insert it as either an operator or operand */
insert_expression_key (P_orgn_id => P_orgn_id,
P_tech_parm_id => P_tech_parm_id,
P_key => L_str,
X_return_status => L_return_status);
insert_expression_key (P_orgn_id => P_orgn_id,
P_tech_parm_id => P_tech_parm_id,
P_key => L_str,
X_return_status => L_return_status);
/* Now let us update all the existing parsed expressions which are referrring to this parameter */
UPDATE gmd_parsed_expression a
SET expression_type = -1,
expression_parm_id = P_tech_parm_id
WHERE expression_type = 0
AND EXISTS (SELECT 1
FROM gmd_tech_parameters_b b
WHERE b.tech_parm_name = a.expression_key
AND b.tech_parm_id = p_tech_parm_id)
AND EXISTS (SELECT 1
FROM gmd_tech_parameters_b c
WHERE NVL(c.organization_id, -1) = NVL(p_orgn_id, -1)
AND c.tech_parm_id = a.tech_parm_id);
* Procedure insert_expression_key
*
* Procedure:- This procedure evaluates the key and inserts it as an operator or
* a parameter based on its value.
*
*********************************************************************************/
PROCEDURE insert_expression_key
( p_orgn_id IN NUMBER,
p_tech_parm_id IN NUMBER,
p_key IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
L_expression VARCHAR2(80);
INSERT_EXPRESSION_ERR EXCEPTION;
/* First insert the enclosing bracket to evaluate the expression piece seperately */
add_expression_row (P_tech_parm_id => P_tech_parm_id,
P_key => '(',
P_Type => 1,
P_exp_parm_id => NULL,
X_return_status => l_return_status);
RAISE insert_expression_err;
RAISE insert_expression_err;
RAISE insert_expression_err;
/* This is a pure techical parameter so lets insert it as an operand */
add_expression_row (P_tech_parm_id => P_tech_parm_id,
P_key => P_key,
P_Type => 0,
P_exp_parm_id => NULL,
X_return_status => l_return_status);
RAISE insert_expression_err;
/* Insert rollup type operators with a different p type */
IF P_key IN ('ISUM', 'BSUM') THEN
add_expression_row (P_tech_parm_id => P_tech_parm_id,
P_key => P_key,
P_Type => 2,
P_exp_parm_id => NULL,
X_return_status => l_return_status);
RAISE insert_expression_err;
RAISE insert_expression_err;
RAISE insert_expression_err;
WHEN insert_expression_err THEN
x_return_status := l_return_status;
fnd_msg_pub.add_exc_msg ('GMD_EXPRESSION_UTIL', 'Insert_Expression_Key');
END insert_expression_key;
SELECT tech_parm_id, data_type
FROM gmd_tech_parameters_b
WHERE tech_parm_name = V_parameter
AND NVL(organization_id, -1) = NVL(v_orgn_id, -1)
AND delete_mark = 0;
* Procedure:- This procedure inserts the key to the temporary table
*
*********************************************************************************/
PROCEDURE add_expression_row
( p_tech_parm_id IN NUMBER,
p_key IN VARCHAR2,
p_type IN VARCHAR2,
p_data_type IN NUMBER,
p_exp_parm_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_user_id NUMBER(15) DEFAULT FND_PROFILE.VALUE('USER_ID');
INSERT INTO GMD_PARSED_EXPRESSION (tech_exp_seq_id, tech_parm_id, expression_key, expression_type,
expression_parm_id, creation_date, created_by, last_updated_by,
last_update_date,data_type)
VALUES (gmd_tech_exp_seq_id_s.nextval, p_tech_parm_id, p_key, p_type, p_exp_parm_id, sysdate, l_user_id,
l_user_id, sysdate,p_data_type);
l_exp_tab.delete(1,l_count);
l_expression_tab.delete (l_start, l_end);
SELECT qty_mass, qty_vol
FROM gmd_material_details_gtmp
WHERE line_id = V_line_id;
SELECT value
FROM gmd_technical_data_gtmp
WHERE line_id = v_line_id
AND tech_parm_name = V_parameter;
SELECT expression_key, expression_type
FROM gmd_parsed_expression p
WHERE (expression_type <> -1 or
p.tech_parm_id = expression_parm_id)
START WITH p.tech_parm_id = V_parm_id
CONNECT BY PRIOR expression_parm_id = p.tech_parm_id
AND PRIOR p.tech_parm_id <> PRIOR expression_parm_id
ORDER SIBLINGS BY tech_exp_seq_id; /* Bug No. 6044965 */
SELECT line_id
FROM gmd_material_details_gtmp a
WHERE entity_id = p_entity_id
AND rollup_ind = 1
AND EXISTS (SELECT 1
FROM gmd_material_details_gtmp b
WHERE line_type = V_line_type
AND entity_id = p_entity_id
AND a.parent_line_id = b.parent_line_id);