[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select category_id
into l_item_category_id
from mtl_item_categories ic,
MTL_DEFAULT_CATEGORY_SETS CS
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')
AND IC.INVENTORY_ITEM_ID = p_inventory_item_id ;
SELECT cp.profile_class_id
into l_customer_class_id
FROM
HZ_CUSTOMER_PROFILES cp ,
hz_cust_profile_classes cpc
WHERE cpc.profile_class_id=cp.PROFILE_CLASS_ID
AND cp.site_use_id IS NULL
AND cp.cust_account_id= p_customer_id;
SELECT party.CATEGORY_CODE
into l_customer_category_code
FROM
HZ_CUST_ACCOUNTS cust,
HZ_PARTIES party
WHERE cust.party_id = party.party_id
and cust.cust_account_id = p_customer_id;
select concatenated_segments
into l_item
from mtl_system_items_kfv
where inventory_item_id = p_inventory_item_id
and organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID');
select concatenated_segments
into l_category
from mtl_categories_kfv
where category_id = OE_ITORD_UTIL.get_item_category_id (p_inventory_item_id);
select 'Y' into l_exists
From oe_item_orderability hdr,
oe_item_orderability_rules rules
Where hdr.orderability_id = rules.orderability_id
and hdr.generally_available='Y'
and hdr.org_id = l_operating_unit_id
and hdr.enable_flag = 'Y'
and rules.enable_flag = 'Y'
and ( hdr.inventory_item_id = p_line_rec.inventory_item_id or hdr.item_category_id = l_x_item_category_id )
and ( rules.customer_id = p_line_rec.sold_to_org_id
or rules.customer_class_id = l_x_customer_class_id
or rules.customer_category_code = l_x_customer_category_code
or INSTR( l_x_region_id_list ,(','||to_char(rules.region_id)||',') ) <> 0
or rules.order_type_id = l_x_order_type_id
or rules.ship_to_location_id = p_line_rec.ship_to_org_id
or rules.sales_channel_code = l_x_sales_channel_code
or rules.sales_person_id = p_line_rec.salesrep_id
or rules.end_customer_id = p_line_rec.end_customer_id
or rules.bill_to_location_id = p_line_rec.invoice_to_org_id
or rules.deliver_to_location_id = p_line_rec.deliver_to_org_id
)
and rownum = 1;
select 'Y' into l_exists
From oe_item_orderability hdr,
oe_item_orderability_rules rules
Where hdr.orderability_id = rules.orderability_id
and hdr.generally_available='N'
and hdr.org_id = l_operating_unit_id
and hdr.enable_flag = 'Y'
and rules.enable_flag = 'Y'
and ( hdr.inventory_item_id = p_line_rec.inventory_item_id or hdr.item_category_id = l_x_item_category_id )
and ( rules.customer_id = p_line_rec.sold_to_org_id
or rules.customer_class_id = l_x_customer_class_id
or rules.customer_category_code = l_x_customer_category_code
or INSTR( l_x_region_id_list ,(','||to_char(rules.region_id)||',') ) <> 0
or rules.order_type_id = l_x_order_type_id
or rules.ship_to_location_id = p_line_rec.ship_to_org_id
or rules.sales_channel_code = l_x_sales_channel_code
or rules.sales_person_id = p_line_rec.salesrep_id
or rules.end_customer_id = p_line_rec.end_customer_id
or rules.bill_to_location_id = p_line_rec.invoice_to_org_id
or rules.deliver_to_location_id = p_line_rec.deliver_to_org_id
)
and rownum = 1;
select 'Y' into l_exists
From oe_item_orderability hdr
where hdr.generally_available='N'
and hdr.org_id = l_operating_unit_id
and hdr.enable_flag = 'Y'
and (hdr.inventory_item_id = p_line_rec.inventory_item_id or hdr.item_category_id = l_x_item_category_id )
and rownum = 1;
select 'Y' into l_exists
From oe_item_orderability hdr,
oe_item_orderability_rules rules
Where hdr.orderability_id = rules.orderability_id
and hdr.generally_available='Y'
and hdr.org_id = p_org_id
and hdr.enable_flag = 'Y'
and rules.enable_flag = 'Y'
and ( hdr.inventory_item_id = p_inventory_item_id or hdr.item_category_id = l_x_item_category_id )
and ( rules.customer_id = p_sold_to_org_id
or rules.customer_class_id = l_x_customer_class_id
or rules.customer_category_code = l_x_customer_category_code
or INSTR( l_x_region_id_list ,(','||to_char(rules.region_id)||',') ) <> 0
or rules.order_type_id = l_x_order_type_id
or rules.ship_to_location_id = p_ship_to_org_id
or rules.sales_channel_code = l_x_sales_channel_code
or rules.sales_person_id = p_salesrep_id
or rules.end_customer_id = p_end_customer_id
or rules.bill_to_location_id = p_invoice_to_org_id
or rules.deliver_to_location_id = p_deliver_to_org_id
)
and rownum = 1;
select 'Y' into l_exists
From oe_item_orderability hdr,
oe_item_orderability_rules rules
Where hdr.orderability_id = rules.orderability_id
and hdr.generally_available='N'
and hdr.org_id = p_org_id
and hdr.enable_flag = 'Y'
and rules.enable_flag = 'Y'
and ( hdr.inventory_item_id = p_inventory_item_id or hdr.item_category_id = l_x_item_category_id )
and ( rules.customer_id = p_sold_to_org_id
or rules.customer_class_id = l_x_customer_class_id
or rules.customer_category_code = l_x_customer_category_code
or INSTR( l_x_region_id_list ,(','||to_char(rules.region_id)||',') ) <> 0
or rules.order_type_id = l_x_order_type_id
or rules.ship_to_location_id = p_ship_to_org_id
or rules.sales_channel_code = l_x_sales_channel_code
or rules.sales_person_id = p_salesrep_id
or rules.end_customer_id = p_end_customer_id
or rules.bill_to_location_id = p_invoice_to_org_id
or rules.deliver_to_location_id = p_deliver_to_org_id
)
and rownum = 1;
select 'Y' into l_exists
From oe_item_orderability hdr
where hdr.generally_available='N'
and hdr.org_id = p_org_id
and hdr.enable_flag = 'Y'
and (hdr.inventory_item_id = p_inventory_item_id or hdr.item_category_id = l_x_item_category_id )
and rownum=1;
PROCEDURE Insert_Row
( p_item_orderability_rec IN OE_ITORD_UTIL.Item_Orderability_Rec
, x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
INSERT INTO OE_ITEM_ORDERABILITY
(
orderability_id,
org_id,
item_level,
item_category_id,
inventory_item_id,
generally_available,
enable_flag,
created_by,
creation_date,
last_updated_by,
last_update_date
)
VALUES (
p_item_orderability_rec.orderability_id,
p_item_orderability_rec.org_id,
p_item_orderability_rec.item_level,
p_item_orderability_rec.item_category_id,
p_item_orderability_rec.inventory_item_id,
p_item_orderability_rec.generally_available,
p_item_orderability_rec.enable_flag,
p_item_orderability_rec.created_by,
p_item_orderability_rec.creation_date,
--to_date(to_char(p_item_orderability_rec.creation_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
p_item_orderability_rec.last_updated_by,
p_item_orderability_rec.last_update_date
--to_date(to_char(p_item_orderability_rec.last_update_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')
);
,'Insert_Row - OE_ITEM_ORDERABILITY'
);
PROCEDURE Update_Row
( p_item_orderability_rec IN OE_ITORD_UTIL.Item_Orderability_Rec
, x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
UPDATE OE_ITEM_ORDERABILITY
SET item_level = p_item_orderability_rec.item_level,
item_category_id = p_item_orderability_rec.item_category_id,
inventory_item_id = p_item_orderability_rec.inventory_item_id,
generally_available = p_item_orderability_rec.generally_available,
enable_flag = p_item_orderability_rec.enable_flag,
created_by = p_item_orderability_rec.created_by,
creation_date = p_item_orderability_rec.creation_date ,
--creation_date = to_date(to_char(p_item_orderability_rec.creation_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
last_updated_by = p_item_orderability_rec.last_updated_by,
last_update_date = p_item_orderability_rec.last_update_date
--last_update_date = to_date(to_char(p_item_orderability_rec.last_update_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')
WHERE orderability_id = p_item_orderability_rec.orderability_id;
,'Update_Row - OE_ITEM_ORDERABILITY'
);
PROCEDURE Insert_Row
( p_item_orderability_rules_rec IN OE_ITORD_UTIL.Item_Orderability_Rules_Rec
, x_return_status OUT NOCOPY VARCHAR2
, x_rowid OUT NOCOPY ROWID
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
INSERT INTO OE_ITEM_ORDERABILITY_RULES
(
ORDERABILITY_ID,
RULE_LEVEL,
CUSTOMER_ID,
CUSTOMER_CLASS_ID,
CUSTOMER_CATEGORY_CODE,
REGION_ID,
ORDER_TYPE_ID,
SHIP_TO_LOCATION_ID,
SALES_CHANNEL_CODE,
SALES_PERSON_ID,
END_CUSTOMER_ID,
BILL_TO_LOCATION_ID,
DELIVER_TO_LOCATION_ID,
ENABLE_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CONTEXT,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
)
VALUES (
p_item_orderability_rules_rec.ORDERABILITY_ID,
p_item_orderability_rules_rec.RULE_LEVEL,
p_item_orderability_rules_rec.CUSTOMER_ID,
p_item_orderability_rules_rec.CUSTOMER_CLASS_ID,
p_item_orderability_rules_rec.CUSTOMER_CATEGORY_CODE,
p_item_orderability_rules_rec.REGION_ID,
p_item_orderability_rules_rec.ORDER_TYPE_ID,
p_item_orderability_rules_rec.SHIP_TO_LOCATION_ID,
p_item_orderability_rules_rec.SALES_CHANNEL_CODE,
p_item_orderability_rules_rec.SALES_PERSON_ID,
p_item_orderability_rules_rec.END_CUSTOMER_ID,
p_item_orderability_rules_rec.BILL_TO_LOCATION_ID,
p_item_orderability_rules_rec.DELIVER_TO_LOCATION_ID,
p_item_orderability_rules_rec.ENABLE_FLAG,
p_item_orderability_rules_rec.CREATED_BY,
p_item_orderability_rules_rec.creation_date,
--to_date(to_char(p_item_orderability_rules_rec.creation_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
p_item_orderability_rules_rec.last_updated_by,
p_item_orderability_rules_rec.last_update_date,
--to_date(to_char(p_item_orderability_rules_rec.last_update_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
p_item_orderability_rules_rec.CONTEXT,
p_item_orderability_rules_rec.ATTRIBUTE1,
p_item_orderability_rules_rec.ATTRIBUTE2,
p_item_orderability_rules_rec.ATTRIBUTE3,
p_item_orderability_rules_rec.ATTRIBUTE4,
p_item_orderability_rules_rec.ATTRIBUTE5,
p_item_orderability_rules_rec.ATTRIBUTE6,
p_item_orderability_rules_rec.ATTRIBUTE7,
p_item_orderability_rules_rec.ATTRIBUTE8,
p_item_orderability_rules_rec.ATTRIBUTE9,
p_item_orderability_rules_rec.ATTRIBUTE10,
p_item_orderability_rules_rec.ATTRIBUTE11,
p_item_orderability_rules_rec.ATTRIBUTE12,
p_item_orderability_rules_rec.ATTRIBUTE13,
p_item_orderability_rules_rec.ATTRIBUTE14,
p_item_orderability_rules_rec.ATTRIBUTE15,
p_item_orderability_rules_rec.ATTRIBUTE16,
p_item_orderability_rules_rec.ATTRIBUTE17,
p_item_orderability_rules_rec.ATTRIBUTE18,
p_item_orderability_rules_rec.ATTRIBUTE19,
p_item_orderability_rules_rec.ATTRIBUTE20
) returning rowid into x_rowid ;
,'Insert_Row - OE_ITEM_ORDERABILITY_RULES'
);
PROCEDURE Update_Row
( p_item_orderability_rules_rec IN OE_ITORD_UTIL.Item_Orderability_Rules_Rec
, p_row_id IN ROWID
, x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
UPDATE OE_ITEM_ORDERABILITY_RULES
SET RULE_LEVEL = p_item_orderability_rules_rec.RULE_LEVEL,
CUSTOMER_ID = p_item_orderability_rules_rec.CUSTOMER_ID,
CUSTOMER_CLASS_ID = p_item_orderability_rules_rec.CUSTOMER_CLASS_ID,
CUSTOMER_CATEGORY_CODE = p_item_orderability_rules_rec.CUSTOMER_CATEGORY_CODE,
REGION_ID = p_item_orderability_rules_rec.REGION_ID,
ORDER_TYPE_ID = p_item_orderability_rules_rec.ORDER_TYPE_ID,
SHIP_TO_LOCATION_ID = p_item_orderability_rules_rec.SHIP_TO_LOCATION_ID,
SALES_CHANNEL_CODE = p_item_orderability_rules_rec.SALES_CHANNEL_CODE,
SALES_PERSON_ID = p_item_orderability_rules_rec.SALES_PERSON_ID,
END_CUSTOMER_ID = p_item_orderability_rules_rec.END_CUSTOMER_ID,
BILL_TO_LOCATION_ID = p_item_orderability_rules_rec.BILL_TO_LOCATION_ID,
DELIVER_TO_LOCATION_ID = p_item_orderability_rules_rec.DELIVER_TO_LOCATION_ID,
ENABLE_FLAG = p_item_orderability_rules_rec.ENABLE_FLAG,
CREATED_BY = p_item_orderability_rules_rec.CREATED_BY,
CREATION_DATE = p_item_orderability_rules_rec.creation_date,
--CREATION_DATE = to_date(to_char(p_item_orderability_rules_rec.creation_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
LAST_UPDATED_BY = p_item_orderability_rules_rec.last_updated_by,
LAST_UPDATE_DATE = p_item_orderability_rules_rec.last_update_date,
--LAST_UPDATE_DATE = to_date(to_char(p_item_orderability_rules_rec.last_update_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
CONTEXT = p_item_orderability_rules_rec.CONTEXT,
ATTRIBUTE1 = p_item_orderability_rules_rec.ATTRIBUTE1,
ATTRIBUTE2 = p_item_orderability_rules_rec.ATTRIBUTE2,
ATTRIBUTE3 = p_item_orderability_rules_rec.ATTRIBUTE3,
ATTRIBUTE4 = p_item_orderability_rules_rec.ATTRIBUTE4,
ATTRIBUTE5 = p_item_orderability_rules_rec.ATTRIBUTE5,
ATTRIBUTE6 = p_item_orderability_rules_rec.ATTRIBUTE6,
ATTRIBUTE7 = p_item_orderability_rules_rec.ATTRIBUTE7,
ATTRIBUTE8 = p_item_orderability_rules_rec.ATTRIBUTE8,
ATTRIBUTE9 = p_item_orderability_rules_rec.ATTRIBUTE9,
ATTRIBUTE10 = p_item_orderability_rules_rec.ATTRIBUTE10,
ATTRIBUTE11 = p_item_orderability_rules_rec.ATTRIBUTE11,
ATTRIBUTE12 = p_item_orderability_rules_rec.ATTRIBUTE12,
ATTRIBUTE13 = p_item_orderability_rules_rec.ATTRIBUTE13,
ATTRIBUTE14 = p_item_orderability_rules_rec.ATTRIBUTE14,
ATTRIBUTE15 = p_item_orderability_rules_rec.ATTRIBUTE15,
ATTRIBUTE16 = p_item_orderability_rules_rec.ATTRIBUTE16,
ATTRIBUTE17 = p_item_orderability_rules_rec.ATTRIBUTE17,
ATTRIBUTE18 = p_item_orderability_rules_rec.ATTRIBUTE18,
ATTRIBUTE19 = p_item_orderability_rules_rec.ATTRIBUTE19,
ATTRIBUTE20 = p_item_orderability_rules_rec.ATTRIBUTE20
WHERE ROWID = p_row_id;
,'Update_Row - OE_ITEM_ORDERABILITY_RULES'
);
SELECT party.party_name
INTO l_rule_level_value
FROM hz_parties party,
hz_cust_accounts acct
WHERE acct.party_id = party.party_id
AND acct.cust_account_id = l_rule_level_id;
SELECT cpc.name
INTO l_rule_level_value
FROM hz_cust_profile_classes cpc
WHERE profile_class_id = l_rule_level_id;
SELECT meaning
INTO l_rule_level_value
FROM ar_lookups
WHERE lookup_type = 'CUSTOMER_CATEGORY'
AND lookup_code = p_rule_level_value;
SELECT country || ', '||state||', '||city||', '||ZONE|| ', '||postal_code_from || ' - '||postal_code_to region
INTO l_rule_level_value
FROM wsh_regions_v
WHERE region_id = l_rule_level_id;
SELECT name
INTO l_rule_level_value
FROM oe_order_types_v
WHERE order_type_id = l_rule_level_id;
SELECT meaning
INTO l_rule_level_value
FROM oe_lookups
WHERE lookup_type = 'SALES_CHANNEL'
AND lookup_code = p_rule_level_value;
SELECT name
INTO l_rule_level_value
FROM ra_salesreps
WHERE salesrep_id = l_rule_level_id;
SELECT site.location
INTO l_rule_level_value
FROM hz_cust_site_uses_all site
WHERE site.site_use_code = 'SHIP_TO'
AND site.site_use_id= l_rule_level_id;
SELECT site.location
INTO l_rule_level_value
FROM hz_cust_site_uses_all site
WHERE site.site_use_code = 'BILL_TO'
AND site.site_use_id= l_rule_level_id;
SELECT site.location
INTO l_rule_level_value
FROM hz_cust_site_uses_all site
WHERE site.site_use_code = 'DELIVER_TO'
AND site.site_use_id= l_rule_level_id;
SELECT loc.location_id
INTO l_ship_to_location_id
FROM hz_cust_site_uses_all site_uses,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc
WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND site_uses.site_use_code = 'SHIP_TO'
AND site_uses.site_use_id = p_site_use_id;
select order_type_id
into l_order_type_id
from oe_order_headers_all
where header_id = p_header_id;
select sales_channel_code
into l_sales_channel_code
from oe_order_headers_all
where header_id = p_header_id;