[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
OE_ITORD_PUB.insert_rules(p_Item_Orderability_Import_Tbl(i));
IF p_Item_Orderability_Import_Rec.last_updated_by is NULL then
fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
fnd_message.set_token('ATTRIBUTE','LAST_UPDATED_BY');
IF p_Item_Orderability_Import_Rec.last_update_date is NULL then
fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
fnd_message.set_token('ATTRIBUTE','LAST_UPDATE_DATE');
SELECT 'Y' into l_exists
FROM HR_OPERATING_UNITS
where organization_id = p_Item_Orderability_Import_Rec.org_id
and rownum = 1;
select 'Y' into l_exists
from fnd_user where user_id = p_Item_Orderability_Import_Rec.created_by
and end_date is NULL;
IF p_Item_Orderability_Import_Rec.last_updated_by is NULL then
begin
select 'Y' into l_exists
from fnd_user where user_id = p_Item_Orderability_Import_Rec.last_updated_by
and end_date is NULL;
fnd_message.set_token('ATTRIBUTE','LAST_UPDATED_BY');
SELECT 'Y'
INTO l_exists
FROM hz_parties party,
hz_cust_accounts acct
WHERE acct.party_id = party.party_id
AND acct.status = 'A'
AND acct.cust_account_id = p_Item_Orderability_Import_Rec.customer_id ;
SELECT 'Y'
INTO l_exists
FROM hz_cust_profile_classes cpc
WHERE profile_class_id = p_Item_Orderability_Import_Rec.customer_class_id ;
SELECT 'Y'
INTO l_exists
FROM ar_lookups
WHERE lookup_type = 'CUSTOMER_CATEGORY'
AND lookup_code = p_Item_Orderability_Import_Rec.customer_category_code ;
SELECT 'Y'
INTO l_exists
FROM wsh_regions_v
WHERE region_id = p_Item_Orderability_Import_Rec.region_id;
SELECT 'Y'
INTO l_exists
FROM oe_order_types_v
WHERE order_type_id = p_Item_Orderability_Import_Rec.order_type_id ;
SELECT 'Y'
INTO l_exists
FROM hz_cust_site_uses_all site
WHERE site.site_use_code = 'SHIP_TO'
AND site.site_use_id= p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID;
SELECT 'Y'
INTO l_exists
FROM oe_lookups
WHERE lookup_type = 'SALES_CHANNEL'
AND lookup_code =p_Item_Orderability_Import_Rec.sales_channel_code;
SELECT 'Y'
INTO l_exists
FROM ra_salesreps
WHERE salesrep_id = p_Item_Orderability_Import_Rec.SALES_PERSON_ID;
SELECT 'Y'
INTO l_exists
FROM hz_parties party,
hz_cust_accounts acct
WHERE acct.party_id = party.party_id
AND acct.cust_account_id = p_Item_Orderability_Import_Rec.end_customer_id ;
SELECT 'Y'
INTO l_exists
FROM hz_cust_site_uses_all site
WHERE site.site_use_code = 'BILL_TO'
AND site.site_use_id= p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID ;
SELECT 'Y'
INTO l_exists
FROM hz_cust_site_uses_all site
WHERE site.site_use_code = 'DELIVER_TO'
AND site.site_use_id= p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID;
select 'Y' into l_exists
from mtl_system_items_b
where inventory_item_id = p_Item_Orderability_Import_Rec.inventory_item_id
and organization_id = oe_sys_parameters.value('MASTER_ORGANIZATION_ID',p_Item_Orderability_Import_Rec.org_id );
select 'Y' into l_exists
from mtl_categories
where category_id = p_Item_Orderability_Import_Rec.item_category_id ;
SELECT 'Y'
INTO l_exists
FROM mtl_item_categories ic,
mtl_default_category_sets cs,
oe_item_orderability oei
WHERE ic.category_set_id=cs.category_set_id
AND cs.functional_area_id = 7
AND ic.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID',p_Item_Orderability_Import_Rec.org_id)
AND ic.inventory_item_id = p_Item_Orderability_Import_Rec.inventory_item_id
AND oei.enable_flag='Y'
AND ic.category_id = oei.item_category_id
and org_id = p_Item_Orderability_Import_Rec.org_id
AND rownum = 1;
sql_stmt := ' select count(1) '
|| ' from oe_item_orderability io_hdr , oe_item_orderability_rules io_rules '
|| ' where io_hdr.orderability_id = io_rules.orderability_id '
|| ' and io_hdr.enable_flag = ''Y'' '
|| ' and io_rules.enable_flag=''Y'' '
|| ' and io_hdr.org_id = '|| p_Item_Orderability_Import_Rec.org_id
|| ' and io_hdr.inventory_item_id = ' || p_Item_Orderability_Import_Rec.inventory_item_id
|| ' and io_rules.rule_level = ' ||''''|| p_Item_Orderability_Import_Rec.rule_level ||''''
|| ' and io_rules.'||l_rule_level_coulmn
|| ' = ' ;
SELECT 'Y'
into l_exists
FROM mtl_item_categories ic,
mtl_default_category_sets cs,
oe_item_orderability oei
WHERE ic.category_set_id=cs.category_set_id
AND cs.functional_area_id = 7
AND ic.organization_id=oe_sys_parameters.Value('MASTER_ORGANIZATION_ID',p_Item_Orderability_Import_Rec.org_id)
AND ic.inventory_item_id = oei.inventory_item_id
AND oei.enable_flag='Y'
AND ic.category_id = p_Item_Orderability_Import_Rec.ITEM_CATEGORY_ID
and org_id = p_Item_Orderability_Import_Rec.org_id
AND rownum = 1;
sql_stmt := ' select count(1) '
|| ' from oe_item_orderability io_hdr , oe_item_orderability_rules io_rules '
|| ' where io_hdr.orderability_id = io_rules.orderability_id '
|| ' and io_hdr.enable_flag = ''Y'' '
|| ' and io_rules.enable_flag=''Y'' '
|| ' and io_hdr.org_id = '|| p_Item_Orderability_Import_Rec.org_id
|| ' and io_hdr.item_category_id = ' || p_Item_Orderability_Import_Rec.item_category_id
|| ' and io_rules.rule_level = ' ||''''|| p_Item_Orderability_Import_Rec.rule_level ||''''
|| ' and io_rules.'||l_rule_level_coulmn
|| ' = ' ;
Procedure insert_rules( p_Item_Orderability_Import_Rec IN OUT NOCOPY OE_ITORD_PUB.Item_Orderability_Import_Rec ) IS
l_item_orderability_rec OE_ITORD_UTIL.Item_Orderability_Rec;
oe_debug_pub.add('Entering OE_ITORD_PUB.insert_rules');
select orderability_id into l_orderability_id
from oe_item_orderability
where enable_flag = 'Y'
and org_id = p_Item_Orderability_Import_Rec.org_id
and item_level = p_Item_Orderability_Import_Rec.ITEM_LEVEL
and ( inventory_item_id = p_Item_Orderability_Import_Rec.inventory_item_id
OR item_category_id = p_Item_Orderability_Import_Rec.inventory_item_id );
SELECT OE_ITEM_ORDERABILITY_S.nextval
INTO l_orderability_id
FROM dual;
l_item_orderability_rec.last_updated_by := p_Item_Orderability_Import_Rec.last_updated_by;
l_item_orderability_rec.last_update_date := p_Item_Orderability_Import_Rec.last_update_date;
OE_ITORD_UTIL.INSERT_ROW (l_item_orderability_rec,l_status);
l_item_orderability_rules_rec.LAST_UPDATED_BY := p_Item_Orderability_Import_Rec.last_updated_by;
l_item_orderability_rules_rec.LAST_UPDATE_DATE := p_Item_Orderability_Import_Rec.last_update_date;
OE_ITORD_UTIL.Insert_Row( l_item_orderability_rules_rec
, l_status
, l_rowid
);
select orderability_id into l_orderability_id
from oe_item_orderability_rules where rowid = l_rowid ;
oe_debug_pub.add('Leaving OE_ITORD_PUB.insert_rules');
End insert_rules;