[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_sRuleDetailSql VARCHAR2(2000) := 'SELECT FRO.RESULT_TYPE, FRD.RULE_DETAIL_ID, FRD.OPERATOR ,
FRD.RULE_OBJECT_ID,FRO.FLEXFIELD_NAME , FRO.FLEXFIELD_APP_SHORT_NAME,
UPPER(NVL(FRO.MULTI_RULE_RESULT_FLAG, ''N'')), UPPER(USE_DEFAULT_VALUE_FLAG)
FROM FUN_RULE_DETAILS FRD , FUN_RULE_OBJECTS_VL FRO
WHERE FRO.RULE_OBJECT_ID = FRD.RULE_OBJECT_ID
AND FRO.RULE_OBJECT_NAME=:1
AND FRO.APPLICATION_ID = :2
AND NVL(FRD.ENABLED_FLAG,''N'') = ''Y''
ORDER BY FRD.SEQ';
g_sRuleDetailMOACSql VARCHAR2(2000) := 'SELECT FRO.RESULT_TYPE, FRD.RULE_DETAIL_ID, FRD.OPERATOR ,
FRD.RULE_OBJECT_ID,FRO.FLEXFIELD_NAME , FRO.FLEXFIELD_APP_SHORT_NAME,
UPPER(NVL(FRO.MULTI_RULE_RESULT_FLAG, ''N'')), UPPER(USE_DEFAULT_VALUE_FLAG)
FROM FUN_RULE_DETAILS FRD , FUN_RULE_OBJECTS_VL FRO
WHERE FRO.RULE_OBJECT_ID = FRD.RULE_OBJECT_ID
AND FRO.RULE_OBJECT_NAME=:1
AND FRO.APPLICATION_ID = :2
AND NVL(FRD.ENABLED_FLAG,''N'') = ''Y''
AND ( (INSTANCE_LABEL IS NULL AND :3 IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND :4 IS NOT NULL AND INSTANCE_LABEL = :5))
AND
( (ORG_ID IS NULL AND :6 IS NULL) OR
(ORG_ID IS NOT NULL AND :7 IS NOT NULL AND ORG_ID = :8))
ORDER BY FRD.SEQ';
g_sCriteriaParamSql VARCHAR2(2000) := 'SELECT FRCP.PARAM_NAME,
FRC.CONDITION, FRC.PARAM_VALUE, FRCP.DATA_TYPE,
FRC.CASE_SENSITIVE_FLAG , FRC.CRITERIA_ID
FROM FUN_RULE_CRIT_PARAMS_B FRCP,
FUN_RULE_CRITERIA FRC
WHERE FRC.RULE_DETAIL_ID = :1
AND FRC.CRITERIA_PARAM_ID = FRCP.CRITERIA_PARAM_ID
UNION
SELECT LOOKUP_CODE,
FRC.CONDITION, FRC.PARAM_VALUE, ''STRINGS'',
FRC.CASE_SENSITIVE_FLAG , FRC.CRITERIA_ID
FROM FUN_LOOKUPS FLV,
FUN_RULE_CRIT_PARAMS_B FRCP,
FUN_RULE_CRITERIA FRC
WHERE LOOKUP_TYPE = ''FUN_RULE_SEED_PARAMS''
AND FRC.RULE_DETAIL_ID = :2
AND FRC.CRITERIA_PARAM_ID = FRCP.CRITERIA_PARAM_ID
AND FRCP.PARAM_NAME = FLV.LOOKUP_CODE';
g_sMultiCriteriaParamValueSql VARCHAR2(1000) := 'SELECT FRMP.PARAM_VALUE
FROM FUN_RULE_PARAM_VALUES FRMP,
FUN_RULE_CRITERIA FRC
WHERE FRC.RULE_DETAIL_ID = :1
AND FRC.CRITERIA_ID = FRMP.CRITERIA_ID
AND FRC.CRITERIA_ID = :2 ';
g_sValueSetSql VARCHAR2(1000) := 'SELECT FFVS.FORMAT_TYPE
FROM FUN_RULE_OBJECTS_B FRO,
FND_FLEX_VALUE_SETS FFVS
WHERE FRO.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
AND FRO.RULE_OBJECT_ID = :1 ';
g_sRuleObjectSql VARCHAR2(1000) := 'SELECT RESULT_TYPE, RULE_OBJECT_ID,
FLEXFIELD_NAME , FLEXFIELD_APP_SHORT_NAME
FROM FUN_RULE_OBJECTS_B
WHERE RULE_OBJECT_NAME = :1';
g_sRuleObjectMOACSql VARCHAR2(1000) := 'SELECT RESULT_TYPE, RULE_OBJECT_ID,
FLEXFIELD_NAME , FLEXFIELD_APP_SHORT_NAME
FROM FUN_RULE_OBJECTS_B
WHERE RULE_OBJECT_NAME = :1
AND ( (INSTANCE_LABEL IS NULL AND :2 IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND :3 IS NOT NULL AND INSTANCE_LABEL = :4))
AND
( (ORG_ID IS NULL AND :5 IS NULL) OR
(ORG_ID IS NOT NULL AND :6 IS NOT NULL AND ORG_ID = :7))';
g_sResultValuesSql VARCHAR2(1000) := 'SELECT RULE_DETAIL_ID , RESULT_VALUE , RESULT_APPLICATION_ID , RULE_NAME
FROM FUN_RULE_DETAILS WHERE RULE_DETAIL_ID = :1' ;
g_sDefaultValuesSql varchar2(1000) := 'SELECT DEFAULT_VALUE , DEFAULT_APPLICATION_ID FROM FUN_RULE_OBJ_ATTRIBUTES
WHERE RULE_OBJECT_ID = :1 ';
FUNCTION populateGTBulkTable(p_insert_statement IN VARCHAR2) RETURN BOOLEAN;
SELECT 'Y', RULE_OBJECT_ID INTO L_DUMMY, m_ruleObjectId FROM
FUN_RULE_OBJECTS_B FRO, FND_APPLICATION APPL
WHERE RULE_OBJECT_NAME = p_rule_object_name
AND FRO.APPLICATION_ID = APPL.APPLICATION_ID
AND APPL.APPLICATION_SHORT_NAME = p_application_short_name
AND
( (INSTANCE_LABEL IS NULL AND p_instance_label IS NULL) OR
(INSTANCE_LABEL IS NOT NULL AND p_instance_label IS NOT NULL AND INSTANCE_LABEL = p_instance_label))
AND
( (ORG_ID IS NULL AND l_org_id IS NULL) OR
(ORG_ID IS NOT NULL AND l_org_id IS NOT NULL AND ORG_ID = l_org_id))
AND PARENT_RULE_OBJECT_ID IS NOT NULL;
SELECT DISTINCT FDF.APPLICATION_TABLE_NAME
FROM FND_DESCRIPTIVE_FLEXS FDF
WHERE FDF.DESCRIPTIVE_FLEXFIELD_NAME = p_FlexFieldName
AND APPLICATION_ID IN (SELECT APPLICATION_ID FROM FND_APPLICATION_VL WHERE APPLICATION_SHORT_NAME = p_FlexFieldAppShortName);
'SELECT DFF.RULE_DETAIL_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15 , RULE_NAME , RESULT_APPLICATION_ID
FROM '|| sDFFTableName ||' DFF , FUN_RULE_DETAILS FRD
WHERE DFF.RULE_DETAIL_ID = :1 AND DFF.RULE_OBJECT_ID = :2 AND DFF.RULE_DETAIL_ID = FRD.RULE_DETAIL_ID'
, DBMS_SQL.native);
'SELECT DFF.RULE_DETAIL_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15 , RULE_NAME , RESULT_APPLICATION_ID
FROM '|| sDFFTableName ||' DFF , FUN_RULE_DETAILS FRD
WHERE DFF.RULE_DETAIL_ID = :1 AND DFF.RULE_DETAIL_ID = FRD.RULE_DETAIL_ID'
, DBMS_SQL.native);
SELECT DISTINCT FDF.APPLICATION_TABLE_NAME
FROM FND_DESCRIPTIVE_FLEXS FDF
WHERE FDF.DESCRIPTIVE_FLEXFIELD_NAME = p_FlexFieldName
AND APPLICATION_ID IN (SELECT APPLICATION_ID FROM FND_APPLICATION_VL WHERE APPLICATION_SHORT_NAME = p_FlexFieldAppShortName);
'SELECT DFF.RULE_DETAIL_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15
FROM '|| sDFFTableName ||' DFF
WHERE DFF.RULE_DETAIL_ID = -99 AND RULE_OBJECT_ID = :1'
, DBMS_SQL.native);
'SELECT DFF.RULE_DETAIL_ID, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15
FROM '|| sDFFTableName ||' DFF
WHERE DFF.RULE_DETAIL_ID = -99 '
, DBMS_SQL.native);
l_select_query VARCHAR2(20000);
l_default_select_query VARCHAR2(20000);
l_insert_statement VARCHAR2(20000);
l_stringToParse := 'select * from ' || p_param_view_name || ' where ROWNUM = 1';
l_stringToParse := 'select * from ' || p_param_view_name || ' where ' || p_additional_where_clause || ' AND ROWNUM = 1';
* and insert the record into FUN_RULE_BULK_RESULT_GT table.
*/
l_paramPresent := FALSE;
l_select_query := 'SELECT '||p_primary_key_column_name||' , ';
l_insert_statement := 'insert into FUN_RULE_BULK_RESULT_GT(ID, RESULT_VALUE , RULE_NAME ,RESULT_APPLICATION_ID, RULE_DETAIL_ID ) ';
If the parameter is a custom parameter, then select the
column name as the param name. Down the line, a query is
built with param name in the where condition. Custom Param
Name given while creating is not identical with the column
name.If the parameter chosen is a custom parameter, change the
name of the param name to the associated column name
*/
SELECT parameter_type,criteria.criteria_param_id INTO l_param_type,l_criteria_parameter_id
FROM fun_rule_crit_params_b param, fun_rule_criteria criteria
WHERE criteria.criteria_id = l_criteria_id
AND criteria.criteria_param_id = param.criteria_param_id;
SELECT COLUMN_NAME into l_param_name
FROM FUN_RULE_CRIT_PARAMS_B
WHERE criteria_param_id = l_criteria_parameter_id;
l_select_query := l_select_query ||' '''||m_resultValue||''', '''||m_ruleName||''', '||NVL(m_resultApplicationId, FND_GLOBAL.resp_appl_id)||' , '||m_ruleDetailId||' FROM '||p_param_view_name||' WHERE ';
l_default_select_query := l_select_query || ' AND ROWNUM =1 ';
l_select_query := l_select_query || l_where_clause;
l_select_query := l_select_query || ' AND ' || p_additional_where_clause;
l_insert_statement := l_insert_statement || ' ' || l_select_query ||
' AND NOT EXISTS(SELECT 1 FROM FUN_RULE_BULK_RESULT_GT WHERE ID='|| p_param_view_name||'.'||p_primary_key_column_name||')';
IF (populateGTBulkTable(l_insert_statement)) THEN
NULL;
/*If not a single rule is satisfied then simply insert the default result SELECT statement*/
/* bug 7337383 */
-- if (NOT l_isAnyRuleOk) then
-- refreshGTBulkTable;
l_select_query := 'SELECT '||p_primary_key_column_name||' , ';
l_where_clause := ' AND NOT EXISTS(SELECT 1 FROM FUN_RULE_BULK_RESULT_GT WHERE ID='|| p_param_view_name||'.'||p_primary_key_column_name||')';
l_insert_statement := 'insert into FUN_RULE_BULK_RESULT_GT(ID, RESULT_VALUE , RULE_NAME ,RESULT_APPLICATION_ID, RULE_DETAIL_ID ) ';
l_select_query := l_select_query ||' '''||m_resultValue||''', '''||m_ruleName||''', '||NVL(m_resultApplicationId, FND_GLOBAL.resp_appl_id)||' , '||m_ruleDetailId||' FROM '||p_param_view_name||' WHERE 1=1 ';
l_select_query := l_select_query || l_where_clause;
l_select_query := l_select_query || ' AND ' || p_additional_where_clause;
l_insert_statement := l_insert_statement || ' ' || l_select_query;
IF (populateGTBulkTable(l_insert_statement)) THEN
NULL;
DBMS_SQL.PARSE(source_cursor, 'delete from FUN_RULE_BULK_RESULT_GT', DBMS_SQL.native);
FUNCTION populateGTBulkTable(p_insert_statement IN VARCHAR2) RETURN BOOLEAN
IS
destination_cursor INTEGER;
DBMS_SQL.PARSE(destination_cursor,p_insert_statement,DBMS_SQL.native);
FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION , 'FUN_RULE_PUB.populateGTBulkTable:->'||p_insert_statement, FALSE);
CURSOR C1 IS SELECT * FROM FUN_RULE_BULK_RESULT_GT;
l_select VARCHAR2(20000) := 'SELECT COUNT(1) FROM '|| p_param_view_name ||' WHERE ';
l_select := l_select || ' ' || x_where_clause || ' ';
l_select := l_select || ' 1 = 1';
DBMS_SQL.PARSE(source_cursor, l_select, DBMS_SQL.native);