[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT user_id
FROM fnd_user
WHERE user_name = p_name;
SELECT *
FROM qa_specs
WHERE spec_id = p_id;
SELECT *
FROM qa_specs
WHERE spec_name = p_spec_name;
SELECT char_id
FROM qa_chars
WHERE char_context_flag = 1 AND
enabled_flag = 1 AND
name = p_element_name;
SELECT 1
FROM qa_spec_chars qsc, qa_specs qs
WHERE qs.spec_id = p_spec_id
AND qs.common_spec_id = qsc.spec_id;
SELECT revision_qty_control_code
from mtl_system_items
where inventory_item_id = p_item_id
and organization_id = p_org_id;
SELECT spec_name
FROM qa_specs
WHERE category_set_id = p_category_set_id
AND category_id = p_category_id
AND item_id = p_item_id
AND item_revision = nvl(p_item_revision, 'NONE')
AND organization_id = p_org_id
AND vendor_id = p_vendor_id
AND customer_id = p_customer_id
AND char_id = p_char_id
AND spec_element_value = p_sub_type_element_value;
SELECT category_set_id, structure_id, validate_flag
FROM mtl_category_sets
WHERE category_set_name = p_category_set_name;
(SELECT category_id
FROM mtl_category_set_valid_cats vc
WHERE vc.category_set_id = ' || p_category_set_id || ')';
SELECT spec_id
FROM qa_specs
WHERE spec_name = p_spec_name AND organization_id = l_org_id;
SELECT 1
FROM qa_specs
WHERE spec_name = p_name;
SELECT 1
FROM qa_spec_chars
WHERE spec_id = p_spec_id AND char_id = p_char_id;
SELECT 1
FROM qa_specs
WHERE spec_id = p_spec_id
AND spec_id <> common_spec_id;
SELECT 1
FROM qa_specs
WHERE spec_name = p_spec_name
AND spec_id <> common_spec_id;
SELECT 1
FROM qa_specs
WHERE common_spec_id = p_spec_id
AND spec_id <> common_spec_id;
SELECT 1
FROM qa_specs qs1, qa_specs qs2
WHERE qs1.spec_name = p_spec_name
AND qs1.spec_id = qs2.common_spec_id
AND qs2.spec_id <> qs2.common_spec_id;
SELECT spec_id
FROM qa_specs
WHERE spec_name = p_reference_spec;
SELECT qa_specs_s.nextval INTO x_spec_id FROM DUAL;
INSERT INTO qa_specs(
spec_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
spec_name,
organization_id,
effective_from,
effective_to,
common_spec_id,
assignment_type,
category_set_id,
category_id,
item_id,
item_revision,
vendor_id,
customer_id,
hide_plan_chars_flag,
char_id,
spec_element_value,
spec_status, --Bug 3500244
attribute_category, -- Bug 7430441
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15 )
VALUES(
x_spec_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id,
p_spec_name,
l_org_id,
p_effective_from,
p_effective_to,
l_reference_spec_id,
p_assignment_type,
l_category_set_id,
l_category_id,
l_item_id,
nvl(p_item_revision, 'NONE'),
l_vendor_id,
l_customer_id,
1,
l_char_id,
l_sub_type_value,
l_spec_status, --Bug 3500244
p_attribute_category , -- Bug 7430441
p_attribute1 ,
p_attribute2 ,
p_attribute3 ,
p_attribute4 ,
p_attribute5 ,
p_attribute6 ,
p_attribute7 ,
p_attribute8 ,
p_attribute9 ,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15 );
INSERT INTO qa_spec_chars(
spec_id,
char_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
enabled_flag,
target_value,
upper_spec_limit,
lower_spec_limit,
upper_reasonable_limit,
lower_reasonable_limit,
upper_user_defined_limit,
lower_user_defined_limit,
uom_code,
attribute_category, -- Bug 7430441
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15 )
VALUES(
l_spec_id,
l_char_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id,
l_enabled_flag,
p_target_value,
p_upper_spec_limit,
p_lower_spec_limit,
p_upper_reasonable_limit,
p_lower_reasonable_limit,
p_upper_user_defined_limit,
p_lower_user_defined_limit,
p_uom_code,
p_attribute_category , -- Bug 7430441
p_attribute1 ,
p_attribute2 ,
p_attribute3 ,
p_attribute4 ,
p_attribute5 ,
p_attribute6 ,
p_attribute7 ,
p_attribute8 ,
p_attribute9 ,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15 );
PROCEDURE delete_spec_private(p_spec_id IN NUMBER) IS
--
-- The real work of deleting a specification and all its
-- spec elements. Do not commit.
--
BEGIN
DELETE
FROM qa_spec_chars
WHERE spec_id = p_spec_id;
DELETE
FROM qa_specs
WHERE spec_id = p_spec_id;
END delete_spec_private;
PROCEDURE delete_spec_element_private(
p_spec_id IN NUMBER,
p_element_id IN NUMBER) IS
BEGIN
DELETE
FROM qa_spec_chars
WHERE spec_id = p_spec_id
AND char_id = p_element_id;
END delete_spec_element_private;
PROCEDURE delete_specification(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_user_name IN VARCHAR2 := NULL,
p_spec_name IN VARCHAR2,
p_organization_code IN VARCHAR2,
p_commit IN VARCHAR2 := fnd_api.g_false,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30):= 'delete_specification';
SAVEPOINT delete_specification;
delete_spec_private(l_spec_id);
ROLLBACK TO delete_specification;
ROLLBACK TO delete_specification;
ROLLBACK TO delete_specification;
END delete_specification;
PROCEDURE delete_spec_element(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_user_name IN VARCHAR2 := NULL,
p_spec_name IN VARCHAR2,
p_organization_code IN VARCHAR2,
p_element_name IN VARCHAR2,
p_commit IN VARCHAR2 := fnd_api.g_false,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30):= 'delete_spec_element';
SAVEPOINT delete_spec_element;
fnd_message.set_name('QA', 'QA_DELETE_SPEC_ELEM_ON_CHILD');
delete_spec_element_private(l_spec_id, l_char_id);
ROLLBACK TO delete_spec_element;
ROLLBACK TO delete_spec_element;
ROLLBACK TO delete_spec_element;
END delete_spec_element;
SELECT qa_specs_s.nextval INTO x_spec_id FROM DUAL;
INSERT INTO qa_specs(
spec_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
spec_name,
organization_id,
effective_from,
effective_to,
common_spec_id,
assignment_type,
category_set_id,
category_id,
item_id,
item_revision,
vendor_id,
customer_id,
hide_plan_chars_flag,
char_id,
spec_element_value,
spec_status ) --Bug 3500244
SELECT
x_spec_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id,
p_to_spec_name,
l_to_org_id,
effective_from,
effective_to,
common_spec_id,
assignment_type,
category_set_id,
category_id,
l_to_item_id,
nvl(p_to_item_revision, 'NONE'),
vendor_id,
customer_id,
hide_plan_chars_flag,
char_id,
spec_element_value,
40 --Bug 3500244 see note below
FROM qa_specs
WHERE spec_id = l_spec_id;
INSERT INTO qa_spec_chars(
spec_id,
char_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
enabled_flag,
target_value,
upper_spec_limit,
lower_spec_limit,
upper_reasonable_limit,
lower_reasonable_limit,
upper_user_defined_limit,
lower_user_defined_limit)
SELECT
x_spec_id,
char_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id,
enabled_flag,
target_value,
upper_spec_limit,
lower_spec_limit,
upper_user_defined_limit,
lower_user_defined_limit,
upper_reasonable_limit,
lower_reasonable_limit
FROM qa_spec_chars
WHERE spec_id = l_spec_id;