[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT to_number(SUBSTR(result_column_name, 10)) num
FROM qa_plan_chars
WHERE plan_id = p_plan_id AND
result_column_name LIKE 'CHARACTER%';
g_result_columns.DELETE;
SELECT user_id
FROM fnd_user
WHERE user_name = p_name;
SELECT 1
FROM qa_plan_chars
WHERE plan_id = p_plan_id AND
char_id = p_char_id;
SELECT 1
FROM qa_plan_chars
WHERE plan_id = p_plan_id AND
mandatory_flag = 1 AND
enabled_flag = 1;
SELECT 1
FROM qa_plan_chars
WHERE plan_id = p_plan_id AND
prompt_sequence = p_prompt_sequence;
SELECT 1
FROM fnd_lookup_values
WHERE lookup_type = 'COLLECTION_PLAN_TYPE'
AND meaning = p_plan_type;
SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'COLLECTION_PLAN_TYPE'
AND meaning = p_plan_type;
SELECT max(prompt_sequence)
FROM qa_plan_chars
WHERE plan_id = p_plan_id;
SELECT max(to_number(substr(result_column_name, 10)))
FROM qa_plan_chars
WHERE plan_id = p_plan_id AND
upper(result_column_name) like 'CHARACTER%';
SELECT min(decode(char_id, 10, 1, 2)) item,
min(decode(char_id, 13, 1, 2)) revision,
min(decode(char_id, 19, 1, 2)) job_name,
min(decode(char_id, 20, 1, 2)) WIP_line,
min(decode(char_id, 21, 1, 2)) to_op_seq,
min(decode(char_id, 22, 1, 2)) from_op_seq,
min(decode(char_id, 23, 1, 2)) to_intraop_step,
min(decode(char_id, 24, 1, 2)) from_intraop_step,
min(decode(char_id, 16, 1, 2)) lot_number,
min(decode(char_id, 17, 1, 2)) serial_number,
min(decode(char_id, 14, 1, 2)) subinv,
min(decode(char_id, 12, 1, 2)) UOM,
min(decode(char_id, 15, 1, 2)) locator,
min(decode(char_id, 27, 1, 2)) po_number,
min(decode(char_id, 110, 1, 2)) po_rel_number,
min(decode(char_id, 28, 1, 2)) po_line,
min(decode(char_id, 33, 1, 2)) so_number,
min(decode(char_id, 35, 1, 2)) so_line,
min(decode(char_id, 26, 1, 2)) vendor,
min(decode(char_id, 60, 1, 2)) comp_item,
min(decode(char_id, 65, 1, 2)) comp_locator,
min(decode(char_id, 66, 1, 2)) comp_lot_number,
min(decode(char_id, 63, 1, 2)) comp_revision,
min(decode(char_id, 67, 1, 2)) comp_serial_number,
min(decode(char_id, 64, 1, 2)) comp_subinv,
min(decode(char_id, 62, 1, 2)) comp_UOM,
min(decode(char_id, 122, 1, 2)) task_number,
min(decode(char_id, 121, 1, 2)) project_number,
--
-- Bug 5680516.
-- This is used to set dependency flag for the collection
-- element SCARP OP SEQ (char id 144). From/To intra op step
-- can exist if either From/To op seq or the Scrap op seq
-- exists in the plan.
-- skolluku Tue Feb 13, 2007
--
min(decode(char_id, 144, 1, 2)) scrap_op_seq,
-- R12 OPM Deviations. Bug 4345503 Start
min(decode(char_id, 2147483556, 1, 2)) process_batch_num,
min(decode(char_id, 2147483555, 1, 2)) process_batchstep_num,
min(decode(char_id, 2147483554, 1, 2)) process_operation,
min(decode(char_id, 2147483553, 1, 2)) process_activity,
min(decode(char_id, 2147483552, 1, 2)) process_resources,
min(decode(char_id, 2147483551, 1, 2)) process_parameter_name
-- R12 OPM Deviations. Bug 4345503 End
FROM qa_plan_chars
WHERE plan_id = p_plan_id AND enabled_flag = 1;
SELECT qa_plans_s.nextval INTO l_to_plan_id FROM dual;
INSERT INTO qa_plans(
plan_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
name,
plan_type_code,
spec_assignment_type,
description,
import_view_name,
view_name,
effective_from,
effective_to,
template_plan_id,
esig_mode,
instructions,
multirow_flag)
SELECT
l_to_plan_id,
p_to_org_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_user_id,
p_to_plan_name,
plan_type_code,
spec_assignment_type,
description,
l_import_view,
l_plan_view,
effective_from,
effective_to,
template_plan_id,
esig_mode,
instructions,
multirow_flag
FROM qa_plans
WHERE plan_id = p_from_plan_id;
SELECT
char_id,
prompt_sequence,
prompt,
enabled_flag,
mandatory_flag,
default_value,
upper(result_column_name) result_column_name,
values_exist_flag,
displayed_flag,
default_value_id,
read_only_flag,
ss_poplist_flag,
information_flag,
decimal_precision,
uom_code
FROM
qa_plan_chars
WHERE
plan_id = p_copy_from_plan_id AND char_id NOT IN
(SELECT char_id
FROM qa_plan_chars
WHERE plan_id = p_copy_to_plan_id)
ORDER BY prompt_sequence;
INSERT INTO qa_plan_chars(
plan_id,
char_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
prompt_sequence,
prompt,
enabled_flag,
mandatory_flag,
default_value,
result_column_name,
values_exist_flag,
displayed_flag,
default_value_id,
read_only_flag,
ss_poplist_flag,
information_flag,
decimal_precision,
uom_code)
VALUES (
p_copy_to_plan_id,
pc.char_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_user_id,
l_prompt_sequence,
pc.prompt,
pc.enabled_flag,
pc.mandatory_flag,
pc.default_value,
l_result_column,
decode(p_copy_values_flag,
fnd_api.g_true, pc.values_exist_flag, 2),
pc.displayed_flag,
pc.default_value_id,
pc.read_only_flag,
pc.ss_poplist_flag,
pc.information_flag,
pc.decimal_precision,
pc.uom_code
);
SELECT
char_id,
prompt_sequence,
prompt,
enabled_flag,
mandatory_flag,
default_value,
upper(result_column_name) result_column_name,
values_exist_flag,
displayed_flag,
default_value_id,
read_only_flag,
ss_poplist_flag,
information_flag,
decimal_precision,
uom_code,
device_flag,
device_id,
override_flag
BULK COLLECT INTO
char_ids,
prompt_sequences,
prompts,
enabled_flags,
mandatory_flags,
default_values,
result_column_names,
values_exist_flags,
displayed_flags,
default_value_ids,
read_only_flags,
ss_poplist_flags,
information_flags,
decimal_precisions,
uom_codes,
device_flags,
device_ids,
override_flags
FROM
qa_plan_chars
WHERE
plan_id = p_copy_from_plan_id AND char_id NOT IN
(SELECT char_id
FROM qa_plan_chars
WHERE plan_id = p_copy_to_plan_id)
ORDER BY prompt_sequence;
INSERT INTO qa_plan_chars(
plan_id,
char_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
prompt_sequence,
prompt,
enabled_flag,
mandatory_flag,
default_value,
result_column_name,
values_exist_flag,
displayed_flag,
default_value_id,
read_only_flag,
ss_poplist_flag,
information_flag,
decimal_precision,
uom_code,
device_flag,
device_id,
override_flag)
VALUES (
p_copy_to_plan_id,
char_ids(i),
sysdate,
p_user_id,
sysdate,
p_user_id,
p_user_id,
prompt_sequences(i),
prompts(i),
enabled_flags(i),
mandatory_flags(i),
default_values(i),
result_column_names(i),
values_exist_flags(i),
displayed_flags(i),
default_value_ids(i),
read_only_flags(i),
ss_poplist_flags(i),
information_flags(i),
decimal_precisions(i),
uom_codes(i),
device_flags(i),
device_ids(i),
override_flags(i)
);
INSERT INTO qa_plan_char_value_lookups(
plan_id,
char_id,
short_code,
description,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
short_code_id)
SELECT
p_copy_to_plan_id,
char_id,
short_code,
description,
sysdate,
p_user_id,
p_user_id,
sysdate,
created_by,
short_code_id
FROM qa_plan_char_value_lookups
WHERE plan_id = p_copy_from_plan_id AND char_id NOT IN
(SELECT char_id
FROM qa_plan_chars
WHERE plan_id = p_copy_to_plan_id);
SELECT
plan_char_action_trigger_id,
trigger_sequence,
plan_id,
char_id,
operator,
low_value_lookup,
high_value_lookup,
low_value_other,
high_value_other,
low_value_other_id,
high_value_other_id
FROM qa_plan_char_action_triggers
WHERE plan_id = p_copy_from_plan_id AND char_id NOT IN
(SELECT char_id
FROM qa_plan_chars
WHERE plan_id = p_copy_to_plan_id)
ORDER BY trigger_sequence;
SELECT
plan_char_action_id,
plan_char_action_trigger_id,
action_id,
car_name_prefix,
car_type_id,
car_owner,
message,
status_code,
alr_action_id,
alr_action_set_id,
assigned_char_id,
assign_type
FROM qa_plan_char_actions
WHERE plan_char_action_trigger_id = x
ORDER BY plan_char_action_id;
SELECT
application_id,
action_id,
name,
alert_id,
action_type,
end_date_active,
enabled_flag,
description,
action_level_type,
date_last_executed,
file_name,
argument_string,
program_application_id,
concurrent_program_id,
list_application_id,
list_id,
to_recipients,
cc_recipients,
bcc_recipients,
print_recipients,
printer,
subject,
reply_to,
response_set_id,
follow_up_after_days,
column_wrap_flag,
maximum_summary_message_width,
body,
version_number
FROM alr_actions
WHERE action_id = x
AND application_id = 250;
SELECT qa_plan_char_action_triggers_s.nextval
INTO l_qpcat_id
FROM dual;
INSERT INTO qa_plan_char_action_triggers (
plan_char_action_trigger_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
trigger_sequence,
plan_id,
char_id,
operator,
low_value_lookup,
high_value_lookup,
low_value_other,
high_value_other,
low_value_other_id,
high_value_other_id)
VALUES (
l_qpcat_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_user_id,
qpcat.trigger_sequence,
p_copy_to_plan_id,
qpcat.char_id,
qpcat.operator,
qpcat.low_value_lookup,
qpcat.high_value_lookup,
qpcat.low_value_other,
qpcat.high_value_other,
qpcat.low_value_other_id,
qpcat.high_value_other_id);
UPDATE QA_PLAN_CHAR_ACTION_TRIGGERS
SET low_value_other = (SELECT displayed_field
FROM PO_LOOKUP_CODES
WHERE lookup_type = 'ERT RESULTS ACTION'
AND lookup_code = qpcat.low_value_other)
WHERE plan_char_action_trigger_id = l_qpcat_id;
SELECT qa_plan_char_actions_s.nextval
INTO l_qpca_id
FROM dual;
SELECT
alr_actions_s.nextval,
alr_action_sets_s.nextval,
alr_action_set_members_s.nextval,
qa_alr_action_name_s.nextval,
qa_alr_action_set_name_s.nextval
INTO
new_action_id,
new_action_set_id,
new_action_set_member_id,
l_action_name_seq,
l_action_set_name_seq
FROM dual;
INSERT INTO alr_actions (
application_id,
action_id,
name,
alert_id,
action_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
end_date_active,
enabled_flag,
description,
action_level_type,
date_last_executed,
file_name,
argument_string,
program_application_id,
concurrent_program_id,
list_application_id,
list_id,
to_recipients,
cc_recipients,
bcc_recipients,
print_recipients,
printer,
subject,
reply_to,
response_set_id,
follow_up_after_days,
column_wrap_flag,
maximum_summary_message_width,
body,
version_number)
VALUES (
alra.application_id,
new_action_id,
new_action_name,
alra.alert_id,
alra.action_type,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_user_id,
alra.end_date_active,
alra.enabled_flag,
alra.description,
alra.action_level_type,
alra.date_last_executed,
alra.file_name,
alra.argument_string,
alra.program_application_id,
alra.concurrent_program_id,
alra.list_application_id,
alra.list_id,
alra.to_recipients,
alra.cc_recipients,
alra.bcc_recipients,
alra.print_recipients,
alra.printer,
alra.subject,
alra.reply_to,
alra.response_set_id,
alra.follow_up_after_days,
alra.column_wrap_flag,
alra.maximum_summary_message_width,
alra.body,
alra.version_number
);
SELECT nvl(max(sequence),0) + 1
INTO l_action_set_seq
FROM alr_action_sets
WHERE application_id = 250 AND alert_id = 10177;
INSERT INTO alr_action_sets (
application_id,
action_set_id,
name,
alert_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
end_date_active,
enabled_flag,
recipients_view_only_flag,
description,
suppress_flag,
suppress_days,
sequence)
VALUES (
250,
new_action_set_id,
new_action_set_name,
10177,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_user_id,
null,
'Y',
'N',
new_action_set_name,
'N',
null,
l_action_set_seq);
SELECT nvl(max(sequence),0) + 1
INTO l_action_set_members_seq
FROM alr_action_set_members
WHERE application_id = 250 AND
alert_id = 10177 AND
action_set_id = new_action_set_id;
INSERT INTO alr_action_set_members (
application_id,
action_set_member_id,
action_set_id,
action_id,
action_group_id,
alert_id,
sequence,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
end_date_active,
enabled_flag,
summary_threshold,
abort_flag,
error_action_sequence)
VALUES (
250,
new_action_set_member_id,
new_action_set_id,
new_action_id,
null,
10177,
l_action_set_members_seq,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_user_id,
null,
'Y',
null,
'A',
null
);
INSERT INTO qa_plan_char_actions (
plan_char_action_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
plan_char_action_trigger_id,
action_id,
car_name_prefix,
car_type_id,
car_owner,
message,
status_code,
alr_action_id,
alr_action_set_id,
assigned_char_id,
assign_type)
VALUES (
l_qpca_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_user_id,
l_qpcat_id,
qpca.action_id,
qpca.car_name_prefix,
qpca.car_type_id,
qpca.car_owner,
qpca.message,
qpca.status_code,
decode(qpca.action_id,
10, new_action_id,
11, new_action_id,
12, new_action_id,
13, new_action_id,
qpca.action_id),
decode(qpca.action_id,
10, new_action_set_id,
11, new_action_set_id,
12, new_action_set_id,
13, new_action_set_id,
qpca.action_id),
qpca.assigned_char_id,
qpca.assign_type);
INSERT INTO qa_plan_char_action_outputs (
plan_char_action_id,
char_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
token_name)
SELECT
l_qpca_id,
char_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_user_id,
token_name
FROM qa_plan_char_action_outputs
WHERE plan_char_action_id = qpca.plan_char_action_id;
SELECT
plan_transaction_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
plan_id,
transaction_number,
mandatory_collection_flag,
background_collection_flag,
enabled_flag
FROM qa_plan_transactions
WHERE plan_id = p_copy_from_plan_id;
SELECT
txn_trigger_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
plan_transaction_id,
collection_trigger_id,
operator,
low_value,
low_value_id,
high_value,
high_value_id
FROM qa_plan_collection_triggers
WHERE plan_transaction_id = x;
SELECT qa_plan_transactions_s.nextval
INTO l_plan_transaction_id
FROM dual;
INSERT INTO qa_plan_transactions (
plan_transaction_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
plan_id,
transaction_number,
mandatory_collection_flag,
background_collection_flag,
enabled_flag)
VALUES (
l_plan_transaction_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_user_id,
p_copy_to_plan_id,
qpt.transaction_number,
qpt.mandatory_collection_flag,
qpt.background_collection_flag,
qpt.enabled_flag);
INSERT INTO qa_plan_collection_triggers (
txn_trigger_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
plan_transaction_id,
collection_trigger_id,
operator,
low_value,
low_value_id,
high_value,
high_value_id)
VALUES (
qa_txn_trigger_ids_s.nextval,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_user_id,
l_plan_transaction_id,
qpct.collection_trigger_id,
qpct.operator,
qpct.low_value,
qpct.low_value_id,
qpct.high_value,
qpct.high_value_id);
'SELECT 1 FROM qa_results WHERE plan_id = :id AND rownum = 1 AND ' ||
l_result_column_name || ' IS NOT NULL';
SELECT 1
FROM qa_results
WHERE plan_id = p_plan_id AND rownum = 1;
PROCEDURE delete_plan_element_actions(p_plan_id IN NUMBER,
p_element_id IN NUMBER) IS
pca_ids number_tab;
DELETE FROM qa_plan_char_action_triggers
WHERE plan_id = p_plan_id AND char_id = p_element_id
RETURNING plan_char_action_trigger_id BULK COLLECT INTO pcat_ids;
DELETE FROM qa_plan_char_actions
WHERE plan_char_action_trigger_id = pcat_ids(i)
RETURNING plan_char_action_id BULK COLLECT INTO pca_ids;
DELETE FROM qa_plan_char_action_outputs
WHERE plan_char_action_id = pca_ids(i);
END delete_plan_element_actions;
PROCEDURE delete_plan_element_values(p_plan_id IN NUMBER,
p_element_id IN NUMBER) IS
BEGIN
DELETE FROM qa_plan_char_value_lookups
WHERE plan_id = p_plan_id AND char_id = p_element_id;
END delete_plan_element_values;
PROCEDURE delete_plan_element(p_plan_id IN NUMBER, p_element_id IN NUMBER) IS
l_result_column qa_plan_chars.result_column_name%TYPE;
DELETE FROM qa_plan_chars
WHERE plan_id = p_plan_id AND char_id = p_element_id
RETURNING result_column_name
INTO l_result_column; -- needed for Bug 3926150
END delete_plan_element;
PROCEDURE delete_plan_elements(p_plan_id IN NUMBER) IS
--
-- Bug 3926150. Need to warn user if function-based index
-- is disrupted due to this action.
--
CURSOR c IS
SELECT qpc.char_id
FROM qa_plan_chars qpc, qa_char_indexes qci
WHERE qpc.plan_id = p_plan_id AND
qpc.char_id = qci.char_id AND
qpc.result_column_name <> qci.default_result_column;
DELETE FROM qa_plan_chars
WHERE plan_id = p_plan_id;
END delete_plan_elements;
PROCEDURE delete_plan_values(p_plan_id IN NUMBER) IS
BEGIN
DELETE FROM qa_plan_char_value_lookups
WHERE plan_id = p_plan_id;
END delete_plan_values;
PROCEDURE delete_plan_transactions(p_plan_id IN NUMBER) IS
pt_ids number_tab;
DELETE FROM qa_plan_transactions
WHERE plan_id = p_plan_id
RETURNING plan_transaction_id BULK COLLECT INTO pt_ids;
DELETE FROM qa_plan_collection_triggers
WHERE plan_transaction_id = pt_ids(i);
END delete_plan_transactions;
PROCEDURE delete_plan_actions(p_plan_id IN NUMBER) IS
pcat_ids number_tab;
DELETE FROM qa_plan_char_action_triggers
WHERE plan_id = p_plan_id
RETURNING plan_char_action_trigger_id BULK COLLECT INTO pcat_ids;
DELETE FROM qa_plan_char_actions
WHERE plan_char_action_trigger_id = pcat_ids(i)
RETURNING plan_char_action_id BULK COLLECT INTO pca_ids;
DELETE FROM qa_plan_char_action_outputs
WHERE plan_char_action_id = pca_ids(i);
END delete_plan_actions;
PROCEDURE delete_plan_header(p_plan_id IN NUMBER) IS
BEGIN
DELETE FROM qa_plans
WHERE plan_id = p_plan_id;
END delete_plan_header;
PROCEDURE delete_plan_private(
p_plan_name VARCHAR2,
p_commit VARCHAR2,
p_user_id NUMBER) IS
l_request_id NUMBER;
END delete_plan_private;
SELECT device_id, override_flag
FROM qa_device_info
WHERE device_name = p_device_name
AND sensor_alias = p_sensor_alias
AND enabled_flag = 1;
SELECT qa_plans_s.nextval INTO x_plan_id FROM dual;
INSERT INTO qa_plans(
plan_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
name,
plan_type_code,
spec_assignment_type,
description,
import_view_name,
view_name,
effective_from,
effective_to,
multirow_flag)
VALUES(
x_plan_id,
l_org_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id,
l_plan_name,
l_plan_type_code,
p_spec_assignment_type,
p_description,
l_import_view,
l_plan_view,
p_effective_from,
p_effective_to,
p_multirow_flag);
INSERT INTO qa_plan_chars(
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
plan_id,
char_id,
prompt_sequence,
prompt,
enabled_flag,
mandatory_flag,
default_value,
displayed_flag,
read_only_flag,
ss_poplist_flag,
information_flag,
result_column_name,
values_exist_flag,
-- 12.1 Device Integration Project
-- bhsankar Mon Nov 12 05:51:37 PST 2007
device_flag,
device_id,
override_flag)
VALUES(
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id,
l_plan_id,
l_char_id,
l_prompt_sequence,
l_prompt,
l_enabled_flag,
l_mandatory_flag,
l_default_value,
l_displayed_flag,
l_read_only_flag,
l_ss_poplist_flag,
l_information_flag,
l_result_column_name,
2, -- values_exist_flag. defaulting a 2 to values flag
-- until user calls add_value
-- 12.1 Device Integration Project
-- bhsankar Mon Nov 12 05:51:37 PST 2007
nvl(l_device_flag, 2),
x_device_id,
nvl(l_override_flag, 2)
);
PROCEDURE delete_plan_element(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_user_name IN VARCHAR2 := NULL,
p_plan_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_plan_element';
SAVEPOINT delete_plan_element_pub;
delete_plan_element_values(l_plan_id, l_element_id);
delete_plan_element_actions(l_plan_id, l_element_id);
delete_plan_element(l_plan_id, l_element_id);
ROLLBACK TO delete_plan_element_pub;
ROLLBACK TO delete_plan_element_pub;
ROLLBACK TO delete_plan_element_pub;
END delete_plan_element;
PROCEDURE delete_collection_plan(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_user_name IN VARCHAR2 := NULL,
p_plan_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_plan';
SAVEPOINT delete_plan_pub;
fnd_message.set_name('QA', 'QA_CANT_DELETE_QPLAN');
delete_plan_elements(l_plan_id);
delete_plan_values(l_plan_id);
delete_plan_transactions(l_plan_id);
delete_plan_actions(l_plan_id);
delete_plan_header(l_plan_id);
delete_plan_private(l_plan_name, p_commit, l_user_id);
ROLLBACK TO delete_plan_pub;
ROLLBACK TO delete_plan_pub;
ROLLBACK TO delete_plan_pub;
END delete_collection_plan;
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'COLLECTION_PLAN_TYPE'
AND lookup_code = p_lookup_code;