[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cn_rt_quota_asgns_s.NEXTVAL
INTO x_rt_quota_asgn_id
FROM SYS.DUAL;
PROCEDURE INSERT_RECORD (
x_org_id IN NUMBER,
x_rowid IN OUT NOCOPY VARCHAR2,
x_rt_quota_asgn_id IN OUT NOCOPY NUMBER,
x_calc_formula_id NUMBER,
x_quota_id NUMBER,
x_start_date DATE,
x_end_date DATE,
x_rate_schedule_id NUMBER,
x_attribute_category VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_creation_date DATE,
x_created_by NUMBER,
x_last_update_login NUMBER,
x_object_version_number IN OUT NOCOPY NUMBER
)
IS
BEGIN
-- Get Sequence Number
get_uid (x_rt_quota_asgn_id);
INSERT INTO cn_rt_quota_asgns
(org_id,
rt_quota_asgn_id,
calc_formula_id,
quota_id,
start_date,
end_date,
rate_schedule_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number
)
VALUES (x_org_id,
x_rt_quota_asgn_id,
x_calc_formula_id,
x_quota_id,
x_start_date,
x_end_date,
x_rate_schedule_id,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
x_object_version_number
);
cn_srp_rate_assigns_pkg.INSERT_RECORD (x_srp_plan_assign_id => NULL,
x_srp_quota_assign_id => NULL,
x_srp_rate_assign_id => NULL,
x_quota_id => x_quota_id,
x_rate_schedule_id => x_rate_schedule_id,
x_rt_quota_asgn_id => x_rt_quota_asgn_id,
x_rate_tier_id => NULL,
x_commission_rate => NULL,
x_commission_amount => NULL,
x_disc_rate_table_flag => NULL
);
END INSERT_RECORD;
SELECT *
FROM cn_rt_quota_asgns
WHERE rt_quota_asgn_id = x_rt_quota_asgn_id
FOR UPDATE OF rt_quota_asgn_id NOWAIT;
fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
PROCEDURE UPDATE_RECORD (
x_rt_quota_asgn_id IN OUT NOCOPY NUMBER,
x_calc_formula_id NUMBER,
x_quota_id NUMBER,
x_start_date DATE,
x_end_date DATE,
x_rate_schedule_id NUMBER,
x_attribute_category VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_creation_date DATE,
x_created_by NUMBER,
x_last_update_login NUMBER,
x_object_version_number IN OUT NOCOPY NUMBER
)
IS
CURSOR c
IS
SELECT *
FROM cn_rt_quota_asgns_all
WHERE rt_quota_asgn_id = x_rt_quota_asgn_id
FOR UPDATE OF rt_quota_asgn_id NOWAIT;
UPDATE cn_rt_quota_asgns
SET start_date = x_start_date,
end_date = x_end_date,
rate_schedule_id = x_rate_schedule_id,
calc_formula_id = x_calc_formula_id,
attribute_category = x_attribute_category,
attribute1 = x_attribute1,
attribute2 = x_attribute2,
attribute3 = x_attribute3,
attribute4 = x_attribute4,
attribute5 = x_attribute5,
attribute6 = x_attribute6,
attribute7 = x_attribute7,
attribute8 = x_attribute8,
attribute9 = x_attribute9,
attribute10 = x_attribute10,
attribute11 = x_attribute11,
attribute12 = x_attribute12,
attribute13 = x_attribute13,
attribute14 = x_attribute15,
attribute15 = x_attribute15,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
object_version_number = x_object_version_number
WHERE rt_quota_asgn_id = x_rt_quota_asgn_id;
cn_srp_rate_assigns_pkg.DELETE_RECORD (x_srp_plan_assign_id => NULL,
x_srp_rate_assign_id => NULL,
x_quota_id => x_quota_id,
x_rate_schedule_id => recinfo.rate_schedule_id,
x_rt_quota_asgn_id => x_rt_quota_asgn_id,
x_rate_tier_id => NULL
);
cn_srp_rate_assigns_pkg.INSERT_RECORD (x_srp_plan_assign_id => NULL,
x_srp_quota_assign_id => NULL,
x_srp_rate_assign_id => NULL,
x_quota_id => x_quota_id,
x_rate_schedule_id => x_rate_schedule_id,
x_rt_quota_asgn_id => x_rt_quota_asgn_id,
x_rate_tier_id => NULL,
x_commission_rate => NULL,
x_commission_amount => NULL,
x_disc_rate_table_flag => NULL
);
END UPDATE_RECORD;
PROCEDURE INSERT_RECORD (
x_calc_formula_id IN NUMBER,
x_quota_id IN NUMBER
)
IS
CURSOR calc_edge_curs (
l_parent_id NUMBER
)
IS
SELECT DISTINCT child_id
FROM cn_calc_edges
WHERE edge_type = 'FE' AND parent_id IN (SELECT calc_sql_exp_id
FROM cn_formula_inputs
WHERE calc_formula_id = l_parent_id
UNION
SELECT output_exp_id
FROM cn_calc_formulas
WHERE calc_formula_id = l_parent_id);
SELECT rt_quota_asgn_id
FROM cn_rt_quota_asgns
WHERE quota_id = l_quota_id AND calc_formula_id = l_calc_formula_id;
l_stack.DELETE (l_stack.LAST);
insert_node_record (l_parent_calc_formula_id, x_quota_id);
END INSERT_RECORD;
PROCEDURE insert_node_record (
x_calc_formula_id IN NUMBER,
x_quota_id IN NUMBER
)
IS
-- Procedure is use to call for inserting the record when you insert or
-- Update the Quotas. Called from CN_QUOTAS_PKG
-- cn_rt_quota-assings is a batch insert
-- insert the srp_rate_assigns
CURSOR srp_rate_insert_curs
IS
SELECT quota_id,
rate_schedule_id,
rt_quota_asgn_id
FROM cn_rt_quota_asgns_all
WHERE quota_id = x_quota_id AND calc_formula_id = x_calc_formula_id;
recinfo srp_rate_insert_curs%ROWTYPE;
SELECT start_date,
end_date,
rate_schedule_id
FROM cn_rt_formula_asgns_all
WHERE calc_formula_id = x_calc_formula_id;
SELECT start_date,
end_date,
org_id
INTO l_quota_start_date,
l_quota_end_date,
l_org_id
FROM cn_quotas_all
WHERE quota_id = x_quota_id;
INSERT INTO cn_rt_quota_asgns_all
(rt_quota_asgn_id,
calc_formula_id,
quota_id,
start_date,
end_date,
rate_schedule_id,
org_id
)
SELECT cn_rt_quota_asgns_s.NEXTVAL,
x_calc_formula_id,
x_quota_id,
l_start_date,
l_end_date,
rt_date.rate_schedule_id,
l_org_id
FROM DUAL;
OPEN srp_rate_insert_curs;
FETCH srp_rate_insert_curs
INTO recinfo;
EXIT WHEN srp_rate_insert_curs%NOTFOUND;
cn_srp_rate_assigns_pkg.INSERT_RECORD (x_srp_plan_assign_id => NULL,
x_srp_quota_assign_id => NULL,
x_srp_rate_assign_id => NULL,
x_quota_id => recinfo.quota_id,
x_rate_schedule_id => recinfo.rate_schedule_id,
x_rt_quota_asgn_id => recinfo.rt_quota_asgn_id,
x_rate_tier_id => NULL,
x_commission_rate => NULL,
x_commission_amount => NULL,
x_disc_rate_table_flag => NULL
);
CLOSE srp_rate_insert_curs;
END insert_node_record;
PROCEDURE DELETE_RECORD (
x_quota_id IN NUMBER,
x_calc_formula_id IN NUMBER,
x_rt_quota_asgn_id IN NUMBER
)
IS
-- Procedure is use to call for deleting the record when you update the
-- Called from CN_QUOTAS_PKG
-- The folllowing query is re-written as two queries for fixing the
-- sql perf bug # 4932376
-- CURSOR srp_rate_assigns_delete IS
-- SELECT quota_id,
-- rate_schedule_id,
-- calc_formula_id
-- FROM cn_rt_quota_asgns
-- WHERE rt_quota_asgn_id = NVL (x_rt_quota_asgn_id, rt_quota_asgn_id)
-- AND quota_id = NVL (x_quota_id, quota_id);
CURSOR srp_rate_assigns_delete
IS
SELECT quota_id,
rate_schedule_id,
calc_formula_id
FROM cn_rt_quota_asgns
WHERE rt_quota_asgn_id = x_rt_quota_asgn_id AND quota_id = quota_id;
CURSOR srp_rate_assigns_delete1
IS
SELECT quota_id,
rate_schedule_id,
calc_formula_id
FROM cn_rt_quota_asgns
WHERE rt_quota_asgn_id = rt_quota_asgn_id AND quota_id = x_quota_id;
recinfo srp_rate_assigns_delete%ROWTYPE;
OPEN srp_rate_assigns_delete;
FETCH srp_rate_assigns_delete
INTO recinfo;
EXIT WHEN srp_rate_assigns_delete%NOTFOUND;
cn_srp_rate_assigns_pkg.DELETE_RECORD (x_srp_plan_assign_id => NULL,
x_srp_rate_assign_id => NULL,
x_quota_id => recinfo.quota_id,
x_rate_schedule_id => recinfo.rate_schedule_id,
x_rt_quota_asgn_id => x_rt_quota_asgn_id,
x_rate_tier_id => NULL
);
CLOSE srp_rate_assigns_delete;
DELETE FROM cn_rt_quota_asgns
WHERE rt_quota_asgn_id = x_rt_quota_asgn_id;
OPEN srp_rate_assigns_delete1;
FETCH srp_rate_assigns_delete1
INTO recinfo;
EXIT WHEN srp_rate_assigns_delete1%NOTFOUND;
cn_srp_rate_assigns_pkg.DELETE_RECORD (x_srp_plan_assign_id => NULL,
x_srp_rate_assign_id => NULL,
x_quota_id => recinfo.quota_id,
x_rate_schedule_id => recinfo.rate_schedule_id,
x_rate_tier_id => NULL
);
CLOSE srp_rate_assigns_delete1;
DELETE FROM cn_rt_quota_asgns
WHERE quota_id = x_quota_id;
END DELETE_RECORD;
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_creation_date DATE,
x_created_by NUMBER,
x_last_update_login NUMBER,
x_program_type VARCHAR2,
x_object_version_number IN OUT NOCOPY NUMBER
)
IS
BEGIN
-- Saves passing it around
g_program_type := x_program_type;
IF x_operation = 'INSERT'
THEN
INSERT_RECORD (x_org_id,
x_rowid,
x_rt_quota_asgn_id,
x_calc_formula_id,
x_quota_id,
x_start_date,
x_end_date,
x_rate_schedule_id,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
x_object_version_number
);
ELSIF x_operation = 'UPDATE'
THEN
UPDATE_RECORD (x_rt_quota_asgn_id,
x_calc_formula_id,
x_quota_id,
x_start_date,
x_end_date,
x_rate_schedule_id,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
x_object_version_number
);
ELSIF x_operation = 'DELETE'
THEN
DELETE_RECORD (x_quota_id, x_calc_formula_id, x_rt_quota_asgn_id);