[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_custom_rec.attribute_name := '&REQ_FACT_UPDATE_DATE';
WHEN 'ORGANIZATION+FII_OPERATING_UNITS' THEN '(select organization_id id, name value from hr_all_organization_units_tl where language = userenv(''LANG''))'
WHEN 'ITEM+ENI_ITEM_PO_CAT' THEN 'eni_item_po_cat_v'
WHEN 'ITEM+POA_ITEMS' THEN 'poa_items_v '
WHEN 'HRI_PERSON+HRI_PER' THEN '(select person_id id, substrb(first_name,1,1) || ''. ''|| last_name value from per_all_people_f where sysdate between effective_start_date and effective_end_date)'
WHEN 'POA_PERSON+INV_CREATOR' THEN '(select person_id id, substrb(first_name,1,1) || ''. ''|| last_name value from per_all_people_f where sysdate between effective_start_date and effective_end_date)'
WHEN 'SUPPLIER+POA_SUPPLIERS' THEN 'poa_suppliers_v'
WHEN 'SUPPLIER+POA_SUPPLIER_SITES' THEN 'poa_supplier_sites_v'
WHEN 'ITEM+POA_COMMODITIES' THEN 'po_commodities_tl'
WHEN 'ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION' THEN '(select organization_id id, name value from hr_all_organization_units_tl where language = userenv(''LANG''))'
WHEN 'LOOKUP+RETURN_REASON' THEN 'mtl_transaction_reasons'
WHEN 'LOOKUP+CONTRACT_DOCTYPE' THEN 'poa_dbi_contract_type_v'
WHEN 'POA_PERSON+REQUESTER' THEN '(select person_id id, substrb(first_name,1,1) || ''. ''|| last_name value from per_all_people_f where sysdate between effective_start_date and effective_end_date)'
WHEN 'FII_COMPANIES+FII_COMPANIES' THEN
'(select t.flex_value_id id, t.description value, f.summary_flag
from fnd_flex_values_tl t, fii_com_pmv_agrt_nodes c, fnd_flex_values f
where c.company_id = t.flex_value_id and t.language = userenv(''LANG'') and t.flex_value_id = f.flex_value_id)'
WHEN 'ORGANIZATION+HRI_CL_ORGCC' THEN
'(select t.flex_value_id id, t.description value, f.summary_flag
from fnd_flex_values_tl t, fii_cc_pmv_agrt_nodes c, fnd_flex_values f
where c.cost_center_id = t.flex_value_id and t.language = userenv(''LANG'') and t.flex_value_id = f.flex_value_id)'
WHEN 'POA_PERSON+NEG_CREATOR' THEN '(select person_id id, substrb(first_name,1,1) || ''. ''|| last_name value from per_all_people_f where sysdate between effective_start_date and effective_end_date)'
WHEN 'LOOKUP+NEG_DOCTYPES' THEN 'poa_neg_doctypes_v'
ELSE ''
END);
l_in_join_rec.table_name := '(select /*+no_merge*/ a.parent_company_id, a.company_id, a.company_id child_company_id '||
'from ('||l_sql||')a '||
'where a.com_agg_flag = ''Y'')';
l_in_join_rec.table_name := '(select /*+no_merge*/ a.company_id, a.company_id child_company_id '||
'from ('||l_sql||')a '||
'where a.com_agg_flag = ''Y'')';
l_in_join_rec.table_name := '(select /*+no_merge*/ a.company_id, com.child_company_id, a.com_agg_flag '||
'from ('||l_sql||')a, '||
'fii_company_hierarchies com '||
'where a.company_id = com.parent_company_id)';
l_in_join_rec.table_name := '(select /*+no_merge*/ b.parent_cc_id, b.cost_center_id, b.cost_center_id child_cc_id '||
'from ('||l_sql||')b '||
'where b.cc_agg_flag = ''Y'')';
l_in_join_rec.table_name := '(select /*+no_merge*/ b.cost_center_id, b.cost_center_id child_cc_id '||
'from ('||l_sql||')b '||
'where b.cc_agg_flag = ''Y'')';
l_in_join_rec.table_name := '(select /*+no_merge*/ b.cost_center_id, cc.child_cc_id, b.cc_agg_flag '||
'from ('||l_sql||')b, '||
'fii_cost_ctr_hierarchies cc '||
'where b.cost_center_id = cc.parent_cc_id)';
FUNCTION get_viewby_select_clause(p_viewby IN VARCHAR2, p_func_area IN VARCHAR2,
p_version IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF(p_func_area = 'PO' and p_version = '7.1') THEN
IF ((p_viewby = 'HRI_PERSON+HRI_PER')
or (p_viewby = 'SUPPLIER+POA_SUPPLIERS') ) THEN
return
'select decode(v.value,null,fnd_message.get_string'
|| '(''POA'', ''POA_DBI_APL_UNASSIGNED''),v.value) VIEWBY,'
|| fnd_global.newline
|| ' decode(v.id, null, -1, v.id) VIEWBYID,'
|| fnd_global.newline;
'select decode(v.value,null,fnd_message.get_string'
|| '(''POA'', ''POA_DBI_APL_UNASSIGNED''),v.value) VIEWBY,'
|| fnd_global.newline
|| ' decode(v.id, null, -1, v.id) VIEWBYID,'
|| fnd_global.newline;
'select decode(v.name, null, fnd_message.get_string(''POA'',''POA_DBI_APL_UNASSIGNED''), v.name) VIEWBY,
decode(v.commodity_id,null, -1, v.commodity_id) VIEWBYID,';
'select decode(v.reason_name, null, fnd_message.get_string(''POA'', ''POA_DBI_APL_UNASSIGNED''), v.reason_name) VIEWBY,
decode(v.reason_id, null, -1, v.reason_id) VIEWBYID,';
'select v.value VIEWBY,v.id VIEWBYID,';
p_selected_commodity in varchar2,
p_context_code in varchar2,
p_restrict_lov in varchar2 := 'Y'
) return varchar2
is
l_exists number;
if(p_selected_commodity = 'ALL' or p_selected_commodity = '-1') then
return '1';
select count(*) into l_exists
from po_commodity_categories cat
where cat.commodity_id = p_selected_commodity
and cat.category_id = p_category_code;
select count(*) into l_exists
from po_commodity_categories cat,
po_commodity_grants gr,
fnd_user usr
where usr.user_id = fnd_global.user_id
and gr.person_id = usr.employee_id
and cat.commodity_id = gr.commodity_id
and cat.category_id = p_category_code
and (to_char(gr.commodity_id) = p_selected_commodity or p_selected_commodity = 'ALL');
select count(*) into l_exists
from ak_web_user_sec_attr_values isp,
fnd_application appl
where isp.web_user_id = fnd_global.user_id
and isp.attribute_application_id = appl.application_id
and appl.application_short_name = 'POS'
and isp.attribute_code = 'ICX_SUPPLIER_ORG_ID'
and isp.number_value = p_supplier_id;
select count(*)
into l_exists
from
poa_supplier_sites_v sup,
ak_web_user_sec_attr_values isp,
fnd_application appl
where isp.attribute_application_id = appl.application_id
and isp.number_value = sup.vendor_id
and isp.web_user_id = fnd_global.user_id
and isp.attribute_code = 'ICX_SUPPLIER_ORG_ID'
and to_char(sup.id) = p_supplier_site_id
and appl.application_short_name = 'POS';
select count(*) into l_exists
from fii_operating_units_v v,
per_organization_list per
where v.id = p_ou_id
and v.id = per.organization_id
and security_profile_id = l_sec_profile_id ;
SELECT COUNT(*)
INTO l_exists
FROM fii_operating_units_v v
WHERE v.id = p_ou_id
AND v.id = poa_dbi_util_pkg.get_ou_org_id ;
function get_display_com(p_id in varchar2, p_parent_id in varchar2, p_selected_company in varchar2) return varchar2
is
l_count number;
if (p_selected_company = 'ALL') then
if (p_id <> p_parent_id) then
/* we want to consider only those rows that represent a
* self relation
*/
return '0';
select count(*) into l_count
from fii_company_grants
where user_id = fnd_global.user_id
and company_id = to_number(p_id)
and report_region_code = 'POA_DBI_INV_STATUS';
else /* p_selected_company <> 'ALL' */
if (p_parent_id = p_selected_company) then
return '1';
function get_display_cc(p_id in varchar2, p_parent_id in varchar2, p_selected_cc in varchar2) return varchar2
is
l_count number;
if (p_selected_cc = 'ALL') then
if (p_id <> p_parent_id) then
/* we want to consider only those rows that represent a
* self relation
*/
return '0';
select count(*) into l_count
from fii_cost_center_grants
where user_id = fnd_global.user_id
and cost_center_id = to_number(p_id)
and report_region_code = 'POA_DBI_INV_STATUS';
if (p_parent_id = p_selected_cc) then
return '1';
/* select count(*) into l_exists
from
po_commodity_grants sec,
fnd_user,
fnd_menu_entries me,
fnd_form_functions f
where
user_id=fnd_global.user_id
and person_id=employee_id
and sec.commodity_id=p_commodity_id
and f.function_name='POA_DBI_COMMODITY_RPTS_VIEW'
and me.function_id=f.function_id
and sec.menu_id=me.menu_id; */
select count(*) into l_exists
from po_commodity_grants sec,
fnd_menus menu,
fnd_user usr
where usr.user_id = fnd_global.user_id
and sec.person_id = usr.employee_id
and sec.commodity_id = p_commodity_id
and menu.menu_name = 'PO_COMMODITY_MANAGER'
and sec.menu_id = menu.menu_id;
* the report viewby and the selected company as input and returns the
* subquery and a flag which indicates whether all the nodes accessed are
* aggregated or not.
*/
procedure get_company_sql(p_viewby in varchar2,
p_company_id in varchar2,
p_region_code in varchar2,
p_company_sql out nocopy varchar2,
p_agg_flag out nocopy varchar2)
is
l_leaf_flag varchar2(1);
l_viewby_sql := 'select company_id, aggregated_flag com_agg_flag '||l_rtn||
'from fii_company_grants '||l_rtn||
'where user_id = fnd_global.user_id '||l_rtn||
'and report_region_code = ®IONCODE';
select count(*) into l_non_agrt_nodes
from fii_company_grants
where user_id = fnd_global.user_id
and report_region_code = p_region_code
and aggregated_flag = 'N';
l_viewby_sql := 'select company_id, aggregated_flag com_agg_flag '||l_rtn||
'from fii_com_pmv_agrt_nodes '||l_rtn||
'where company_id = &COMPANYID';
select count(*) into l_non_agrt_nodes
from fii_com_pmv_agrt_nodes
where company_id = to_number(p_company_id)
and aggregated_flag = 'N';
select count(1) into l_company_count
from fii_company_grants
where user_id = fnd_global.user_id
and report_region_code = p_region_code;
select to_char(company_id) into l_top_node
from fii_company_grants
where user_id = fnd_global.user_id
and report_region_code = p_region_code;
select is_leaf_flag into l_leaf_flag
from fii_company_hierarchies
where parent_company_id = to_number(l_top_node)
and parent_company_id = child_company_id;
l_viewby_sql := 'select company_id, aggregated_flag com_agg_flag '||l_rtn||
'from fii_com_pmv_agrt_nodes '||l_rtn||
'where company_id = &TOPCOMPANYID';
select count(*) into l_non_agrt_nodes
from fii_com_pmv_agrt_nodes
where company_id = l_top_node
and aggregated_flag = 'N';
l_viewby_sql := 'select parent_company_id, child_company_id company_id, aggregate_next_level_flag com_agg_flag '||l_rtn||
'from fii_company_hierarchies '||l_rtn||
'where child_level = parent_level+1 '||l_rtn||
'and parent_company_id = &TOPCOMPANYID';
select count(*) into l_non_agrt_nodes
from fii_company_hierarchies
where child_level = parent_level + 1
and parent_company_id = l_top_node
and aggregate_next_level_flag = 'N';
l_viewby_sql := 'select company_id, aggregated_flag com_agg_flag '||l_rtn||
'from fii_company_grants '||l_rtn||
'where user_id = fnd_global.user_id '||l_rtn||
'and report_region_code = ®IONCODE';
select count(*) into l_non_agrt_nodes
from fii_company_grants
where user_id = fnd_global.user_id
and report_region_code = p_region_code
and aggregated_flag = 'N';
select is_leaf_flag into l_leaf_flag
from fii_company_hierarchies
where parent_company_id = to_number(p_company_id)
and parent_company_id = child_company_id;
l_viewby_sql := 'select company_id, aggregated_flag com_agg_flag '||l_rtn||
'from fii_com_pmv_agrt_nodes '||l_rtn||
'where company_id = &COMPANYID';
select count(*) into l_non_agrt_nodes
from fii_com_pmv_agrt_nodes
where company_id = to_number(p_company_id)
and aggregated_flag = 'N';
l_viewby_sql := 'select parent_company_id, child_company_id company_id, aggregate_next_level_flag com_agg_flag '||l_rtn||
'from fii_company_hierarchies '||l_rtn||
'where child_level = parent_level+1 '||l_rtn||
'and parent_company_id = &COMPANYID';
select count(*) into l_non_agrt_nodes
from fii_company_hierarchies
where child_level = parent_level + 1
and parent_company_id = p_company_id
and aggregate_next_level_flag = 'N';
* the report viewby and the selected cost-center as input and returns the
* subquery and a flag which indicates whether all the nodes accessed are
* aggregated or not.
*/
procedure get_cost_ctr_sql(p_viewby in varchar2,
p_cost_center_id in varchar2,
p_region_code in varchar2,
p_cost_ctr_sql out nocopy varchar2,
p_agg_flag out nocopy varchar2)
is
l_leaf_flag varchar2(1);
l_viewby_sql := 'select cost_center_id, aggregated_flag cc_agg_flag '||l_rtn||
'from fii_cost_center_grants '||l_rtn||
'where user_id = fnd_global.user_id '||l_rtn||
'and report_region_code = ®IONCODE';
select count(*) into l_non_agrt_nodes
from fii_cost_center_grants
where user_id = fnd_global.user_id
and report_region_code = p_region_code
and aggregated_flag = 'N';
l_viewby_sql := 'select cost_center_id, aggregated_flag cc_agg_flag '||l_rtn||
'from fii_cc_pmv_agrt_nodes '||l_rtn||
'where cost_center_id = &COSTCTRID';
select count(*) into l_non_agrt_nodes
from fii_cc_pmv_agrt_nodes
where cost_center_id = p_cost_center_id
and aggregated_flag = 'N';
select count(1) into l_cost_center_count
from fii_cost_center_grants
where user_id = fnd_global.user_id
and report_region_code = p_region_code;
select to_char(cost_center_id) into l_top_node
from fii_cost_center_grants
where user_id = fnd_global.user_id
and report_region_code = p_region_code;
select is_leaf_flag into l_leaf_flag
from fii_cost_ctr_hierarchies
where parent_cc_id = to_number(l_top_node)
and parent_cc_id = child_cc_id;
l_viewby_sql := 'select cost_center_id, aggregated_flag cc_agg_flag '||l_rtn||
'from fii_cc_pmv_agrt_nodes '||l_rtn||
'where cost_center_id = &TOPCOSTCTRID';
select count(*) into l_non_agrt_nodes
from fii_cc_pmv_agrt_nodes
where cost_center_id = l_top_node
and aggregated_flag = 'N';
l_viewby_sql := 'select parent_cc_id, child_cc_id cost_center_id, aggregate_next_level_flag cc_agg_flag '||l_rtn||
'from fii_cost_ctr_hierarchies '||l_rtn||
'where child_level = parent_level+1 '||l_rtn||
'and parent_cc_id = &TOPCOSTCTRID';
select count(*) into l_non_agrt_nodes
from fii_cost_ctr_hierarchies
where child_level = parent_level + 1
and parent_cc_id = l_top_node
and aggregate_next_level_flag = 'N';
l_viewby_sql := 'select cost_center_id, aggregated_flag cc_agg_flag '||l_rtn||
'from fii_cost_center_grants '||l_rtn||
'where user_id = fnd_global.user_id '||l_rtn||
'and report_region_code = ®IONCODE';
select count(*) into l_non_agrt_nodes
from fii_cost_center_grants
where user_id = fnd_global.user_id
and report_region_code = p_region_code
and aggregated_flag = 'N';
select is_leaf_flag into l_leaf_flag
from fii_cost_ctr_hierarchies
where parent_cc_id = p_cost_center_id
and parent_cc_id = child_cc_id;
l_viewby_sql := 'select cost_center_id, aggregated_flag cc_agg_flag '||l_rtn||
'from fii_cc_pmv_agrt_nodes '||l_rtn||
'where cost_center_id = &COSTCTRID';
select count(*) into l_non_agrt_nodes
from fii_cc_pmv_agrt_nodes
where cost_center_id = p_cost_center_id
and aggregated_flag = 'N';
l_viewby_sql := 'select parent_cc_id, child_cc_id cost_center_id, aggregate_next_level_flag cc_agg_flag '||l_rtn||
'from fii_cost_ctr_hierarchies '||l_rtn||
'where child_level = parent_level+1 '||l_rtn||
'and parent_cc_id = &COSTCTRID';
select count(*) into l_non_agrt_nodes
from fii_cost_ctr_hierarchies
where child_level = parent_level + 1
and parent_cc_id = p_cost_center_id
and aggregate_next_level_flag = 'N';
SELECT id into l_supplier_id
FROM (
SELECT id
FROM poa_suppliers_v
ORDER BY value)
WHERE ROWNUM=1;
SELECT id into l_supplier_id
FROM (
SELECT id
FROM
poa_suppliers_v v,
ak_web_user_sec_attr_values isp,
fnd_application appl
WHERE
fnd_global.user_id = isp.web_user_id
AND isp.attribute_application_id = appl.application_id
AND appl.application_short_name = 'POS'
AND isp.attribute_code = 'ICX_SUPPLIER_ORG_ID'
AND v.id = isp.number_value
ORDER BY value)
WHERE ROWNUM=1;
select name into stmt
from fii_time_ent_year
where l_asof_date between start_date and end_date;
select name into stmt
from fii_time_ent_qtr
where l_asof_date between start_date and end_date;
select name into stmt
from fii_time_ent_period
where l_asof_date between start_date and end_date;
select to_char(sequence) into l_week
from fii_time_week
where l_asof_date between start_date and end_date;
select substr(week_id,3,2) into l_year
from fii_time_week
where l_asof_date between start_date and end_date;
select count(1) into l_count
from fii_company_grants
where user_id = fnd_global.user_id
and report_region_code = l_region_code;
select to_char(company_id) into l_top_company_value
from fii_company_grants
where user_id = fnd_global.user_id
and report_region_code = l_region_code;
select count(1) into l_count
from fii_cost_center_grants
where user_id = fnd_global.user_id
and report_region_code = l_region_code;
select to_char(cost_center_id) into l_top_cost_ctr_value
from fii_cost_center_grants
where user_id = fnd_global.user_id
and report_region_code = l_region_code;