[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure INSERT_SEC_RULE
( p_order_type IN Number,
p_item_name IN Varchar2,
p_customer_name IN Varchar2,
p_supplier_name IN Varchar2,
p_customer_site_name IN Varchar2,
p_supplier_site_name IN Varchar2,
p_org_name IN Varchar2,
p_grantee_type IN Varchar2,
p_grantee_key IN Varchar2,
p_start_date IN Date,
p_end_date IN Date,
p_privilege IN Varchar2,
p_order_number IN Varchar2,
p_company_name IN Varchar2,
p_return_code OUT NOCOPY Number,
p_err_msg OUT NOCOPY Varchar2)
IS
l_return_code Number :=0;
SELECT sr.rule_id
FROM msc_x_security_rules sr
WHERE decode(p_order_type,null,-1,sr.order_type) = decode(p_order_type,null,-1,p_order_type)
AND decode(l_item_id,null,-1,sr.item_id) = decode(l_item_id,null,-1,l_item_id)
AND decode(l_customer_id,null,-1,sr.customer_id) = decode(l_customer_id,null,-1,l_customer_id)
AND decode(l_supplier_id,null,-1,sr.supplier_id) = decode(l_supplier_id,null,-1,l_supplier_id)
AND decode(l_customer_site_id,null,-1,sr.customer_site_id)= decode(l_customer_site_id,null,-1,l_customer_site_id)
AND decode(l_supplier_site_id,null,-1,sr.supplier_site_id)= decode(l_supplier_site_id,null,-1,l_supplier_site_id)
AND decode(l_org_id,null,-1,sr.org_id) = decode(l_org_id,null,-1,l_org_id)
AND decode(p_order_number,null,'xx',sr.order_number) = decode(p_order_number,null,'xx',p_order_number)
AND sr.grantee_type = p_grantee_type
AND sr.grantee_key = l_grantee_key
AND sr.privilege = p_privilege
AND sr.company_id = l_company_id;
insert into msc_x_security_rules(rule_id, order_type,item_id,customer_id,customer_site_id,
supplier_id,supplier_site_id,org_id,order_number,grantee_type,
grantee_key,privilege,company_id,effective_from_date,effective_to_date,
item_name,creation_date,created_by,last_update_date,last_updated_by)
values
(msc_security_rules_s.nextval,l_lookup_code,l_item_id,l_customer_id,l_customer_site_id,
l_supplier_id,l_supplier_site_id,l_org_id,p_order_number,p_grantee_type,
l_grantee_key,p_privilege,l_company_id,l_start_date,l_end_date,
p_item_name,sysdate,fnd_global.user_id,sysdate,fnd_global.user_id);
insert into msc_x_security_rules(rule_id, order_type,item_id,customer_id,customer_site_id,
supplier_id,supplier_site_id,org_id,order_number,grantee_type,
grantee_key,privilege,company_id,effective_from_date,effective_to_date,
item_name,creation_date,created_by,last_update_date,last_updated_by)
values
(msc_security_rules_s.nextval,NULL,l_item_id,l_customer_id,l_customer_site_id,
l_supplier_id,l_supplier_site_id,l_org_id,p_order_number,p_grantee_type,
l_grantee_key,p_privilege,l_company_id,l_start_date,l_end_date,
p_item_name,sysdate,fnd_global.user_id,sysdate,fnd_global.user_id);
p_err_msg := p_err_msg ||' '||'error while inserting the record';
END INSERT_SEC_RULE;
SELECT sr.rule_id
FROM msc_x_security_rules sr
WHERE decode(l_lookup_code,null,-1,sr.order_type) = decode(l_lookup_code,null,-1,l_lookup_code)
AND decode(l_item_id,null,-1,sr.item_id) = decode(l_item_id,null,-1,l_item_id)
AND decode(l_customer_id,null,-1,sr.customer_id) = decode(l_customer_id,null,-1,l_customer_id)
AND decode(l_supplier_id,null,-1,sr.supplier_id) = decode(l_supplier_id,null,-1,l_supplier_id)
AND decode(l_customer_site_id,null,-1,sr.customer_site_id)= decode(l_customer_site_id,null,-1,l_customer_site_id)
AND decode(l_supplier_site_id,null,-1,sr.supplier_site_id)= decode(l_supplier_site_id,null,-1,l_supplier_site_id)
AND decode(l_org_id,null,-1,sr.org_id) = decode(l_org_id,null,-1,l_org_id)
AND decode(p_order_number,null,-1,sr.order_number) = decode(p_order_number,null,-1,p_order_number)
AND sr.grantee_type = p_grantee_type
AND sr.grantee_key = l_grantee_key
AND sr.privilege = p_privilege
AND sr.company_id = l_company_id
AND sr.rule_id <> p_rule_id;
update msc_x_security_rules set
order_type = l_lookup_code,
item_id = l_item_id,
customer_id = l_customer_id,
customer_site_id = l_customer_site_id,
supplier_id = l_supplier_id,
supplier_site_id = l_supplier_site_id,
org_id = l_org_id,
order_number = p_order_number,
grantee_type = p_grantee_type,
grantee_key = l_grantee_key,
privilege = p_privilege,
company_id = l_company_id,
effective_from_date = l_start_date,
effective_to_date = l_end_date,
item_name = p_item_name,
-- order_type_meaning = p_order_type,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where rule_id = p_rule_id;
update msc_x_security_rules set
order_type = null,
item_id = l_item_id,
customer_id = l_customer_id,
customer_site_id = l_customer_site_id,
supplier_id = l_supplier_id,
supplier_site_id = l_supplier_site_id,
org_id = l_org_id,
order_number = p_order_number,
grantee_type = p_grantee_type,
grantee_key = l_grantee_key,
privilege = p_privilege,
company_id = l_company_id,
effective_from_date = l_start_date,
effective_to_date = l_end_date,
item_name = p_item_name,
-- order_type_meaning = p_order_type,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where rule_id = p_rule_id;
select lookup_code into l_lookup_code
from fnd_lookup_values
where lookup_type='MSC_X_ORDER_TYPE'
and meaning=p_order_type
and language=userenv('lang');
sql_statement :='Select mc.company_id
from msc_companies mc
where mc.company_name=:1';
select s.inventory_item_id into l_item_id
from
msc_item_suppliers s
, msc_trading_partners tp
where
s.supplier_id = tp.partner_id
and tp.partner_type = 1
and tp.partner_name = nvl(p_company_name,sys_context('msc','company_name'))
and s.supplier_item_name=p_item_name
and s.plan_id=-1
and rownum<2;
select s.inventory_item_id into l_item_id
from
msc_item_customers s
, msc_trading_partners tp
where
s.customer_id = tp.partner_id
and tp.partner_type = 2
and tp.partner_name = nvl(p_company_name,sys_context('msc','company_name'))
and s.customer_item_name=p_item_name
and rownum<2;
select cm.inventory_item_id into l_item_id
from msc_items cm
where
item_name=p_item_name;
select company_id into l_cust_company_id
from msc_companies where
company_name=p_customer_name;
Select mcr.subject_id into l_customer_id
from msc_company_relationships mcr,
msc_companies mc
where
mcr.subject_id = mc.company_id
and mcr.relationship_type = 2
and mcr.object_id =l_company_id
and mc.company_name=p_customer_name ;
Select company_id into l_supp_company_id
from msc_companies
where company_name=p_supplier_name;
Select mcr.subject_id into l_supplier_id
from msc_company_relationships mcr,
msc_companies mc
where
mcr.subject_id = mc.company_id
and mcr.relationship_type = 1
and mcr.object_id =l_company_id
and mc.company_name=p_supplier_name ;
select company_site_id into l_cust_site_company_id
from msc_company_sites
where company_id=l_company_id
and company_site_name=p_customer_site_name;
select cs.company_site_id into l_customer_site_id
from msc_company_relationships mcr,
msc_company_sites cs
where mcr.subject_id = cs.company_id
and mcr.relationship_type = 2
and mcr.object_id=l_company_id
and cs.company_site_name=p_customer_site_name
and mcr.subject_id=l_customer_id ; -- added to validate if customer site belongs to a customer
select company_site_id into l_supp_site_company_id
from msc_company_sites
where company_id=l_company_id
and company_site_name=p_supplier_site_name;
select cs.company_site_id into l_supplier_site_id
from msc_company_relationships mcr,
msc_company_sites cs
where mcr.subject_id = cs.company_id
and mcr.relationship_type = 1--( 'supplier of ' )
and mcr.object_id=l_company_id
and cs.company_site_name=p_supplier_site_name
and mcr.subject_id=l_supplier_id ; -- added to validate if supplier site belongs to a supplier
select company_site_id into l_org_id
from msc_company_sites
where company_id=l_company_id
and company_site_name=p_org_name;
select company_id into l_grantee_key from msc_companies
where company_name=p_grantee_key;
select user_id into l_grantee_key from fnd_user
where user_name=p_grantee_key;
select responsibility_id into l_grantee_key from fnd_responsibility_vl
where responsibility_name=p_grantee_key;
select group_id into l_grantee_key from msc_groups
where group_name =p_grantee_key;