[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_select_clause OUT NOCOPY VARCHAR2,
x_from_clause OUT NOCOPY VARCHAR2,
x_where_clause OUT NOCOPY VARCHAR2,
x_order_by_clause OUT NOCOPY VARCHAR2 ) is
l_sql_statement varchar2(2000) default null;
l_select_clause varchar2(2000) default null;
l_select_clause := substr(l_sql_statement,instr(l_sql_statement,'SELECT'),
instr(l_sql_statement,'FROM')-instr(l_sql_statement,'SELECT'));
select substr(l_sql_statement,instr(l_sql_statement,'FROM'),
decode(instr(l_sql_statement,'WHERE'),
0,decode(instr(l_sql_statement,'ORDER BY'),0,
length(l_sql_statement),
instr(l_sql_statement,'ORDER BY') - instr(l_sql_statement,'FROM')),
instr(l_sql_statement,'WHERE') - instr(l_sql_statement,'FROM')))
into l_from_clause from dual;
select decode(instr(l_sql_statement,'WHERE'),
0,Null,
substr(l_sql_statement,instr(l_sql_statement,'WHERE'),
decode(instr(l_sql_statement,'ORDER BY'),
0,length(l_sql_statement),
instr(l_sql_statement,'ORDER BY') - instr(l_sql_statement,'WHERE'))))
into l_where_clause from dual;
select decode(instr(l_sql_statement,'ORDER BY'),
0,Null,
substr(l_sql_statement,instr(l_sql_statement,'ORDER BY'),
length(l_sql_statement)))
into l_order_by_clause from dual;
x_select_clause := l_select_clause;
x_select_clause OUT NOCOPY VARCHAR2,
x_from_clause OUT NOCOPY VARCHAR2,
x_where_clause OUT NOCOPY VARCHAR2,
x_order_by_clause OUT NOCOPY VARCHAR2,
x_object_code OUT NOCOPY VARCHAR2,
x_id1_col OUT NOCOPY VARCHAR2,
x_id2_col OUT NOCOPY VARCHAR2,
x_name_col OUT NOCOPY VARCHAR2) is
--Cursor to get JTOT_OBJECT_CODE from OKC_RULE_DEF_SOURCES_V
Cursor rule_source_curs is
SELECT rds.jtot_object_code object_code
FROM OKC_RULE_DEF_SOURCES_V rds
WHERE rds.rgr_rgd_code = p_rgd_code
AND rds.rgr_rdf_code = p_rgs_code
AND rds.buy_or_sell = p_buy_or_sell
AND rds.object_id_number = decode(p_object_code,
'JTOT_OBJECT1_CODE',1,
'JTOT_OBJECT2_CODE',2,
'JTOT_OBJECT3_CODE',3)
AND rds.start_date <= sysdate
AND nvl(rds.end_date,sysdate+1) > sysdate
ORDER BY rds.jtot_object_code;
l_query_string := ' SELECT '|| rule_source_rec.object_code||'.ID1, '||
rule_source_rec.object_code||'.ID2, '||
rule_source_rec.object_code||'.NAME, '||
rule_source_rec.object_code||'.DESCRIPTION';
x_select_clause => x_select_clause,
x_from_clause => x_from_clause,
x_where_clause => x_where_clause,
x_order_by_clause => x_order_by_clause);
x_select_clause OUT NOCOPY VARCHAR2,
x_from_clause OUT NOCOPY VARCHAR2,
x_where_clause OUT NOCOPY VARCHAR2,
x_order_by_clause OUT NOCOPY VARCHAR2,
x_object_code OUT NOCOPY VARCHAR2,
x_longlist_flag OUT NOCOPY VARCHAR2,
x_format_type OUT NOCOPY VARCHAR2,
x_rule_information_col OUT NOCOPY VARCHAR2,
x_meaning_col OUT NOCOPY VARCHAR2,
x_value_set_name OUT NOCOPY VARCHAR2,
x_additional_columns OUT NOCOPY VARCHAR2) is
--Cursor for getting the Validation Type
Cursor flex_value_set_cur(p_flex_value_set_id NUMBER) is
select fvs.longlist_flag
, fvs.format_type
, fvs.maximum_size
, fvs.validation_type
, fvs.flex_value_set_id
, fvs.flex_value_set_name
from FND_FLEX_VALUE_SETS fvs
Where fvs.FLEX_VALUE_SET_ID=p_flex_value_set_id;
SELECT fvt.id_column_name,
fvt.value_column_name,
fvt.meaning_column_name,
fvt.application_table_name,
fvt.additional_where_clause,
fvt.enabled_column_name,
fvt.start_date_column_name,
fvt.end_date_column_name,
fvt.additional_quickpick_columns
FROM fnd_flex_validation_tables fvt
WHERE fvt.flex_value_set_id = p_flex_value_set_id;
l_select_clause varchar2(200) default Null;
x_select_clause := 'None';
fnd_flex_val_api.get_independent_vset_select(p_value_set_id => p_flex_value_set_id,
p_inc_id_col => 'N',
x_select => l_query_string,
x_mapping_code => l_mapping_code,
x_success => l_success);
x_select_clause => x_select_clause,
x_from_clause => x_from_clause,
x_where_clause => x_where_clause,
x_order_by_clause => x_order_by_clause);
fnd_flex_val_api.get_dependent_vset_select(p_value_set_id => p_flex_value_set_id,
p_inc_id_col => 'N',
x_select => l_query_string,
x_mapping_code => l_mapping_code,
x_success => l_success);
x_select_clause => x_select_clause,
x_from_clause => x_from_clause,
x_where_clause => x_where_clause,
x_order_by_clause => x_order_by_clause);
l_select_clause := ' SELECT '||l_select_clause||' '||flex_query_t_rec.value_column_name||' , ';
l_select_clause := ' SELECT '||l_select_clause||' '||flex_query_t_rec.id_column_name||' , ';
l_select_clause := l_select_clause||' '||flex_query_t_rec.value_column_name;
l_select_clause := l_select_clause||' '||','||flex_query_t_rec.meaning_column_name||' ';
l_select_clause := l_select_clause||' '||flex_query_t_rec.value_column_name;
select l_where_clause||' '||decode(l_where_clause,null,' ',decode(instr(ltrim(flex_query_t_rec.additional_where_clause,' '),'ORDER BY'),1,' ',' AND '))||flex_query_t_rec.additional_where_clause
into l_add_where_clause from dual;
l_query_string := rtrim(ltrim(l_select_clause,' '),' ')||' '||
rtrim(ltrim(l_from_clause,' '),' ')||' '||
rtrim(ltrim(l_where_clause,' '),' ')||' '||
rtrim(ltrim(l_order_by_clause,' '),' ');
x_select_clause => x_select_clause,
x_from_clause => x_from_clause,
x_where_clause => x_where_clause,
x_order_by_clause => x_order_by_clause);
l_select_clause Varchar2(2000) Default Null;
Select dfcu.application_id,
dfcu.descriptive_flexfield_name,
-- Bug 5876083 - udhenuko Added
dfcon.enabled_flag
From okc_rule_defs_v rdfv,
fnd_descr_flex_col_usage_vl dfcu,
fnd_descr_flex_contexts_vl dfcon
where dfcu.application_id = rdfv.application_id
and dfcu.descriptive_flex_context_code = rdfv.rule_code
and dfcu.descriptive_flexfield_name = rdfv.DESCRIPTIVE_FLEXFIELD_NAME
and dfcon.application_id = rdfv.application_id
and dfcon.descriptive_flex_context_code= rdfv.rule_code
and dfcon.descriptive_flexfield_name = rdfv.DESCRIPTIVE_FLEXFIELD_NAME
and rdfv.rule_code = p_rgs_code;
x_select_clause => l_select_clause,
x_from_clause => l_from_clause,
x_where_clause => l_where_clause,
x_order_by_clause => l_order_by_clause,
x_object_code => l_object_code,
x_id1_col => l_id1_col,
x_id2_col => l_id2_col,
x_name_col => l_name_col);
l_select_clause := 'None';
x_select_clause => l_select_clause,
x_from_clause => l_from_clause,
x_where_clause => l_where_clause,
x_order_by_clause => l_order_by_clause,
x_object_code => l_object_code,
x_longlist_flag => l_longlist_flag,
x_format_type => l_format_type,
x_rule_information_col => l_rule_info_col,
x_meaning_col => l_name_col,
x_value_set_name => l_value_set_name,
x_additional_columns => l_additional_columns);
l_rule_segment_tbl(i).select_clause := l_select_clause;
p_select_clause IN VARCHAR2,
p_from_clause IN VARCHAR2,
p_where_clause IN VARCHAR2,
x_name OUT NOCOPY VARCHAR2,
x_desc OUT NOCOPY VARCHAR2
) is
l_select_clause Varchar2(2000) := p_select_clause;
l_sql_string := 'SELECT NAME, DESCRIPTION ';
l_sql_string := 'SELECT '||p_name_column||' ';
l_select_clause Varchar2(2000) Default Null;
SELECT RL.*
FROM OKC_RULE_GROUPS_B RG, OKC_RULES_B RL
WHERE RG.DNZ_CHR_ID = P_CONTRACT_ID
AND RG.CHR_ID = P_CONTRACT_ID
AND RG.RGD_CODE = P_RGD_CODE
AND RG.ID = RL.RGP_ID
AND RL.RULE_INFORMATION_CATEGORY = P_RULE_CODE;
SELECT RL.*
FROM OKC_RULE_GROUPS_B RG, OKC_RULES_B RL
WHERE RG.DNZ_CHR_ID = P_CONTRACT_ID
AND RG.CHR_ID IS NULL
AND RG.CLE_ID = P_LINE_ID
AND RG.RGD_CODE = P_RGD_CODE
AND RG.ID = RL.RGP_ID
AND RL.RULE_INFORMATION_CATEGORY = P_RULE_CODE;
l_rule_segment_tbl2(i).select_clause := l_rule_segment_tbl(i).select_clause;
if(l_rule_segment_tbl2(i).select_clause is null or l_rule_segment_tbl2(i).select_clause = 'None') then
l_rule_segment_tbl2(i).x_segment_status := 'INVALID';
p_select_clause => l_rule_segment_tbl2(i).select_clause,
p_from_clause => l_rule_segment_tbl2(i).from_clause,
p_where_clause => l_rule_segment_tbl2(i).where_clause,
x_name => x_name,
x_desc => x_desc);
select osrg.SCS_CODE
,osrg.RGD_CODE
,fl.Meaning
,fl.description
from Fnd_Lookups fl,
okc_subclass_rg_defs osrg,
okc_k_headers_v chrv
where fl.lookup_type = 'OKC_RULE_GROUP_DEF'
and fl.enabled_flag = 'Y'
and nvl(fl.start_date_active,sysdate) <= sysdate
and nvl(fl.end_date_active,sysdate+1) > sysdate
and fl.lookup_code = osrg.RGD_CODE
and nvl(osrg.start_date,sysdate) <= sysdate
and nvl(osrg.end_date,sysdate+1) > sysdate
and osrg.scs_code = chrv.scs_code
and chrv.id = p_chr_id;
/*select dfcu.descriptive_flex_context_code
, dfcu.application_column_name
, dfcu.column_seq_num
from fnd_lookups fl,
fnd_descr_flex_col_usage_vl dfcu
where fl.lookup_type = 'OKC_RULE_DEF'
and fl.lookup_code = dfcu.descriptive_flex_context_code
and fl.enabled_flag = 'Y'
and nvl(fl.start_date_active,sysdate) <= sysdate
and nvl(fl.end_date_active,sysdate+1) > sysdate
and dfcu.application_id=510
and dfcu.descriptive_flexfield_name='OKC Rule Developer DF'
and dfcu.descriptive_flex_context_code = p_rgs_code
union
*/
--after rule striping :
select dfcu.descriptive_flex_context_code
, dfcu.application_column_name
, dfcu.column_seq_num
from okc_rule_defs_v rdfv,
fnd_descr_flex_col_usage_vl dfcu
where dfcu.application_id = rdfv.application_id
and dfcu.descriptive_flex_context_code = rdfv.rule_code
and dfcu.descriptive_flexfield_name = rdfv.DESCRIPTIVE_FLEXFIELD_NAME
and rdfv.rule_code = p_rgs_code
order by 3;
select rgd_code,
rdf_code,
optional_yn,
min_cardinality,
max_cardinality
from okc_rg_def_rules
where rgd_code = p_rgd_code;