[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
08-MAR-99 S Kumar Modified and More Parameters in the Begin Insert
27-APR-99 S Kumar Modified and More Parameters in the Begin Insert
17-MAY-99 S Kumar Calling delete record, insert record for changing the
calc formula name
26-MAY-99 S Kumar Changes made due to 3i
25-JUN-99 S Kumar Modified the procedure call to cn_srp_per_rc
and cn_srp_per_quota. instead of period id to
end date
03-AUG-99 S Kumar get_schedule_id procedure go changed due to formulas
now we do not have a rate schedule directly assiged
to the quotas, it has to go through the formula
we need to validate against cn_rt_quota_assigns.
03-AUG-99 S Kumar End_record.rate_schedules_pkg call for was made from
quota end record, now we are looping through each
rate schedule we have at the rt_quota_asgns.
it means that validating all the rate schedule and
its tiers.
25-AUG-99 S Kumar Added more parameter to update the performance goal
in the srp_quota_assigns.
update_srp_quota.performance_goal.
25-AUG-99 S Kumar Changed the g_temp_Status with nvl in the update
added delete call to the rt_quota_asgns
20-SEP-99 S Kumar Modified the Package to mark the event.
Name
Purpose
Notes
*/
-- -------------------------------------------------------------------------+
-- | Variables |
----------------------------------------------------------------------------+
g_temp_status_code VARCHAR2 (30) := NULL;
SELECT cn_quotas_s.NEXTVAL
INTO x_quota_id
FROM SYS.DUAL;
PROCEDURE DELETE_RECORD (
x_quota_id NUMBER,
x_name VARCHAR2
)
IS
BEGIN
-- delete quota rules
cn_quota_rules_pkg.DELETE_RECORD (x_quota_id => x_quota_id, x_quota_rule_id => NULL, x_revenue_class_id => NULL);
cn_period_quotas_pkg.DELETE_RECORD (x_quota_id => x_quota_id);
cn_rt_quota_asgns_pkg.DELETE_RECORD (x_quota_id => x_quota_id, x_calc_formula_id => NULL, x_rt_quota_asgn_id => NULL);
UPDATE cn_quotas_all
SET delete_flag = 'Y'
WHERE quota_id = x_quota_id;
END DELETE_RECORD;
PROCEDURE INSERT_RECORD (
x_rowid IN OUT NOCOPY VARCHAR2,
x_quota_id IN OUT NOCOPY NUMBER,
x_object_version_number OUT NOCOPY NUMBER,
x_name VARCHAR2,
x_target NUMBER,
x_quota_type_code VARCHAR2,
x_period_type_code VARCHAR2,
x_usage_code VARCHAR2,
x_payment_amount NUMBER,
x_description VARCHAR2,
x_start_date DATE,
x_end_date DATE,
x_quota_status VARCHAR2,
x_start_num NUMBER,
x_end_num NUMBER,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_creation_date DATE,
x_created_by NUMBER,
x_last_update_login NUMBER,
x_incentive_type_code VARCHAR2,
x_credit_type_id NUMBER,
x_calc_formula_id NUMBER,
x_rt_sched_custom_flag VARCHAR2,
x_package_name VARCHAR2,
x_performance_goal NUMBER,
x_interval_type_id NUMBER,
x_payee_assign_flag VARCHAR2,
x_vesting_flag VARCHAR2,
x_quota_unspecified NUMBER,
x_addup_from_rev_class_flag VARCHAR2,
x_expense_account_id NUMBER,
x_liability_account_id NUMBER,
x_quota_group_code VARCHAR2,
--clku, PAYMENT ENHANCEMENT
x_payment_group_code VARCHAR2 := 'STANDARD',
--clku, 2854576
x_attribute_category VARCHAR := NULL,
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,
-- fmburu r12
x_indirect_credit VARCHAR2,
x_org_id NUMBER,
x_salesrep_end_flag VARCHAR2
)
IS
x_incremental_type VARCHAR2 (1) := 'N';
INSERT INTO cn_quotas
(quota_id,
NAME,
target,
quota_type_code,
payment_amount,
description,
start_date,
end_date,
quota_status,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
incremental_type,
calc_formula_id,
incentive_type_code,
credit_type_id,
rt_sched_custom_flag,
package_name,
performance_goal,
interval_type_id,
payee_assign_flag,
vesting_flag,
addup_from_rev_class_flag,
expense_account_id,
liability_account_id,
quota_group_code,
--clku, PAYMENT ENHANCEMENT
payment_group_code,
--clku
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
object_version_number,
-- fmburu r12
indirect_credit,
org_id,
salesreps_enddated_flag
)
VALUES (x_quota_id,
x_name,
x_target,
x_quota_type_code,
x_payment_amount,
x_description,
x_start_date,
x_end_date,
x_quota_status,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
x_incremental_type,
x_calc_formula_id,
x_incentive_type_code,
x_credit_type_id,
x_rt_sched_custom_flag,
x_package_name,
x_performance_goal,
x_interval_type_id,
x_payee_assign_flag,
x_vesting_flag,
x_addup_from_rev_class_flag,
DECODE (x_expense_account_id, cn_api.g_miss_id, NULL, x_expense_account_id),
DECODE (x_liability_account_id, cn_api.g_miss_id, NULL, x_liability_account_id),
x_quota_group_code,
--clku, PAYMENT ENHANCEMENT
x_payment_group_code,
--clku, 2854578
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,
1,
-- fmburu r12
x_indirect_credit,
x_org_id,
nvl(x_salesrep_end_flag,'N')
);
END INSERT_RECORD;
PROCEDURE UPDATE_RECORD (
x_quota_id NUMBER,
x_start_date DATE,
x_end_date DATE
)
IS
BEGIN
UPDATE cn_quotas
SET start_date = x_start_date,
end_date = x_end_date
WHERE quota_id = x_quota_id;
PROCEDURE UPDATE_RECORD (
x_quota_id NUMBER,
x_object_version_number OUT NOCOPY NUMBER,
x_name VARCHAR2,
x_target NUMBER,
x_quota_type_code VARCHAR2,
x_period_type_code VARCHAR2,
x_usage_code VARCHAR2,
x_payment_amount NUMBER,
x_description VARCHAR2,
x_start_date DATE,
x_end_date DATE,
x_quota_status VARCHAR2,
x_start_num NUMBER,
x_end_num NUMBER,
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_last_update_login NUMBER,
x_incentive_type_code VARCHAR2,
x_credit_type_id NUMBER,
x_calc_formula_id NUMBER,
x_rt_sched_custom_flag VARCHAR2,
x_package_name VARCHAR2,
x_performance_goal NUMBER,
x_interval_type_id NUMBER,
x_payee_assign_flag VARCHAR2,
x_vesting_flag VARCHAR2,
x_quota_unspecified NUMBER,
x_addup_from_rev_class_flag VARCHAR2,
x_expense_account_id NUMBER,
x_liability_account_id NUMBER,
x_quota_group_code VARCHAR2,
x_payment_group_code VARCHAR2 := 'STANDARD',
x_attribute_category VARCHAR := NULL,
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_indirect_credit VARCHAR2,
x_salesrep_end_flag VARCHAR2
)
IS
l_modified BOOLEAN := FALSE;
SELECT *
FROM cn_quotas
WHERE quota_id = x_quota_id;
SELECT *
FROM cn_srp_quota_assigns
WHERE quota_id = x_quota_id;
SELECT object_version_number
INTO l_old_ovn
FROM cn_quotas
WHERE quota_id = x_quota_id;
UPDATE cn_quotas
SET quota_id = x_quota_id,
NAME = x_name,
target = x_target,
quota_type_code = x_quota_type_code,
payment_amount = x_payment_amount,
description = x_description,
start_date = x_start_date,
end_date = x_end_date,
quota_status = x_quota_status,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login,
incremental_type = x_incremental_type,
calc_formula_id = x_calc_formula_id,
incentive_type_code = x_incentive_type_code,
credit_type_id = x_credit_type_id,
performance_goal = x_performance_goal,
rt_sched_custom_flag = x_rt_sched_custom_flag,
package_name = x_package_name,
interval_type_id = x_interval_type_id,
payee_assign_flag = x_payee_assign_flag,
vesting_flag = x_vesting_flag,
addup_from_rev_class_flag = x_addup_from_rev_class_flag,
expense_account_id = DECODE (x_expense_account_id, cn_api.g_miss_id, recinfo.expense_account_id, x_expense_account_id),
liability_account_id = DECODE (x_liability_account_id, cn_api.g_miss_id, recinfo.liability_account_id, x_liability_account_id),
quota_group_code = x_quota_group_code,
--clku, PAYMENT ENHANCEMENT
payment_group_code = x_payment_group_code,
-- clku, 2854576
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_attribute14,
attribute15 = x_attribute15,
object_version_number = l_ovn,
-- release 12
indirect_credit = x_indirect_credit,
salesreps_enddated_flag=x_salesrep_end_flag
WHERE quota_id = x_quota_id;
cn_quota_rules_pkg.DELETE_RECORD (x_quota_id => x_quota_id, x_quota_rule_id => NULL, x_revenue_class_id => NULL);
cn_srp_quota_assigns_pkg.update_srp_quota (x_quota_id => x_quota_id,
x_target => x_target,
x_payment_amount => x_payment_amount,
x_performance_goal => x_performance_goal,
x_rate_schedule_id => NULL
-- obsolete
,
x_rate_schedule_id_old => NULL
-- obsolete
,
x_disc_rate_schedule_id => NULL
-- obsolete
,
x_disc_rate_schedule_id_old => NULL
-- obsolete
,
x_payment_type_code => NULL
-- obsolete
,
x_payment_type_code_old => NULL
-- obsolete
,
x_quota_type_code => x_quota_type_code,
x_quota_type_code_old => recinfo.quota_type_code,
x_period_type_code => x_period_type_code
-- pening, how to handle the new
,
x_calc_formula_id => x_calc_formula_id
-- not used.
,
x_calc_formula_id_old => recinfo.calc_formula_id
);
SELECT role_id,
comp_plan_id,
salesrep_id,
start_date,
end_date
INTO l_role_id,
l_comp_plan_id,
l_salesrep_id,
l_start_date,
l_end_date
FROM cn_srp_plan_assigns
WHERE srp_plan_assign_id = srp_quota_assigns_info.srp_plan_assign_id;
cn_srp_period_quotas_pkg.insert_record
(x_srp_plan_assign_id => NULL,
x_quota_id => x_quota_id,
x_start_period_id => NULL -- obsolete
,
x_end_period_id => NULL -- obsolete
,
x_start_date => cn_api.next_period (recinfo.end_date, recinfo.org_id),
x_end_date => x_end_date
);
cn_srp_per_quota_rc_pkg.INSERT_RECORD
(x_srp_plan_assign_id => NULL,
x_quota_id => x_quota_id,
x_revenue_class_id => NULL,
x_start_period_id => NULL,
x_end_period_id => NULL,
x_start_date => cn_api.next_period (recinfo.end_date, recinfo.org_id),
x_end_date => x_end_date
);
cn_srp_period_quotas_pkg.DELETE_RECORD
(x_srp_plan_assign_id => NULL,
x_quota_id => x_quota_id,
x_start_period_id => NULL -- obsolete
,
x_end_period_id => NULL -- obsolete
,
x_start_date => cn_api.next_period (x_end_date, recinfo.org_id),
x_end_date => recinfo.end_date
);
cn_srp_per_quota_rc_pkg.DELETE_RECORD
(x_srp_plan_assign_id => NULL,
x_quota_id => x_quota_id,
x_revenue_class_id => NULL,
x_start_period_id => NULL,
x_end_period_id => NULL,
x_start_date => cn_api.next_period (x_end_date, recinfo.org_id),
x_end_date => recinfo.end_date
);
cn_srp_period_quotas_pkg.INSERT_RECORD
(x_srp_plan_assign_id => NULL,
x_quota_id => x_quota_id,
x_start_period_id => NULL -- obsolete
,
x_end_period_id => NULL -- obsolete
,
x_start_date => cn_api.next_period (recinfo.end_date, recinfo.org_id),
x_end_date => x_end_date
);
cn_srp_per_quota_rc_pkg.INSERT_RECORD
(x_srp_plan_assign_id => NULL,
x_quota_id => x_quota_id,
x_revenue_class_id => NULL,
x_start_period_id => NULL,
x_end_period_id => NULL,
x_start_date => cn_api.next_period (recinfo.end_date, recinfo.org_id),
x_end_date => x_end_date
);
cn_srp_period_quotas_pkg.DELETE_RECORD
(x_srp_plan_assign_id => NULL,
x_quota_id => x_quota_id,
x_start_period_id => NULL -- obsolete
,
x_end_period_id => NULL -- obsolete
,
x_start_date => cn_api.next_period (x_end_date, recinfo.org_id),
x_end_date => cn_api.next_period (recinfo.end_date, recinfo.org_id) -- bugfix 4042235
);
cn_srp_per_quota_rc_pkg.DELETE_RECORD
(x_srp_plan_assign_id => NULL,
x_quota_id => x_quota_id,
x_revenue_class_id => NULL,
x_start_period_id => NULL,
x_end_period_id => NULL,
x_start_date => cn_api.next_period (x_end_date, recinfo.org_id),
x_end_date => cn_api.next_period (recinfo.end_date, recinfo.org_id) -- bugfix 4042235
);
cn_srp_per_quota_rc_pkg.DELETE_RECORD
(x_srp_plan_assign_id => NULL,
x_quota_id => x_quota_id,
x_revenue_class_id => NULL,
x_start_period_id => NULL,
x_end_period_id => NULL,
x_start_date => recinfo.start_date,
x_end_date => recinfo.end_date
);
cn_srp_period_quotas_pkg.DELETE_RECORD
(x_srp_plan_assign_id => NULL,
x_quota_id => x_quota_id,
x_start_period_id => NULL
-- obsolete
,
x_end_period_id => NULL
-- obsolete
,
x_start_date => recinfo.start_date,
x_end_date => recinfo.end_date
);
cn_srp_period_quotas_pkg.INSERT_RECORD
(x_srp_plan_assign_id => NULL,
x_quota_id => x_quota_id,
x_start_period_id => NULL
-- obsolete
,
x_end_period_id => NULL
-- obsolete
,
x_start_date => x_start_date,
x_end_date => x_end_date
);
cn_srp_per_quota_rc_pkg.INSERT_RECORD
(x_srp_plan_assign_id => NULL,
x_quota_id => x_quota_id,
x_revenue_class_id => NULL,
x_start_period_id => NULL,
x_end_period_id => NULL,
x_start_date => x_start_date,
x_end_date => x_end_date
);
-- mark the newly inserted record as calc, because of the
-- newly inserted srp_period_quotas
-- clku, bug 3646625
cn_mark_events_pkg.mark_event_quota
(p_event_name => 'CHANGE_QUOTA_CALC',
p_object_name => x_name,
p_object_id => x_quota_id,
p_start_date => x_start_date,
p_end_date => x_end_date,
p_start_date_old => NULL,
p_end_date_old => NULL,
p_org_id => recinfo.org_id
);
-- delete the associated rows from cn_period_quots table and
-- insert the new one. Can not use only difference since
-- have to reselect the whole thing.
-- clku, 1/9/2002, commented out this part because we want to prevent inserting
-- records in cn_period_quotas with checking the formula's ytd flag. The insert of rows
-- will be taken care of in CN_PLAN_ELEMENT_PUB.Update_Period_Quota right after
-- the call of cn_period_quotas.begin_record
/*cn_period_quotas_pkg.delete_record (
x_quota_id => x_quota_id);
cn_period_quotas_pkg.insert_record (
x_quota_id => x_quota_id);*/
cn_period_quotas_pkg.delete_record (
x_quota_id => x_quota_id);
cn_period_quotas_pkg.insert_record (
x_quota_id => x_quota_id);
END UPDATE_RECORD;
SELECT *
FROM cn_quotas
WHERE quota_id = x_quota_id
FOR UPDATE OF quota_id NOWAIT;
fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
x_last_update_date DATE,
x_last_updated_by NUMBER,
x_creation_date DATE,
x_created_by NUMBER,
x_last_update_login NUMBER,
x_incentive_type_code VARCHAR2,
x_credit_type_id NUMBER,
x_calc_formula_id NUMBER,
x_rt_sched_custom_flag VARCHAR2,
x_package_name VARCHAR2,
x_performance_goal NUMBER,
x_interval_type_id NUMBER,
x_payee_assign_flag VARCHAR2,
x_vesting_flag VARCHAR2,
x_quota_unspecified NUMBER,
x_addup_from_rev_class_flag VARCHAR2,
x_expense_account_id NUMBER,
x_liability_account_id NUMBER,
x_quota_group_code VARCHAR2,
--clku PAYMENT ENHANCEMENT
x_payment_group_code VARCHAR2 := 'STANDARD',
--clku, bug 2854576
x_attribute_category VARCHAR2 := NULL,
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,
-- fmburu r12
x_indirect_credit VARCHAR2,
x_org_id NUMBER,
x_salesrep_end_flag VARCHAR2
)
IS
BEGIN
-- Saves passing it around
g_program_type := x_program_type;
IF x_operation = 'INSERT'
THEN
INSERT_RECORD (x_rowid,
x_quota_id,
x_object_version_number,
x_name,
x_target,
x_quota_type_code,
x_period_type_code,
x_usage_code,
x_payment_amount,
x_description,
x_start_date,
x_end_date,
x_quota_status,
x_start_num,
x_end_num,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
x_incentive_type_code,
x_credit_type_id,
x_calc_formula_id,
x_rt_sched_custom_flag,
x_package_name,
x_performance_goal,
x_interval_type_id,
x_payee_assign_flag,
x_vesting_flag,
x_quota_unspecified,
x_addup_from_rev_class_flag,
x_expense_account_id,
x_liability_account_id,
x_quota_group_code,
--clku, PAYMENT ENHANCEMENT
x_payment_group_code,
--clku, 2854576
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,
-- fmburu r12
x_indirect_credit,
x_org_id,
nvl(x_salesrep_end_flag,'N')
);
ELSIF x_operation = 'UPDATE'
THEN
UPDATE_RECORD (x_quota_id,
x_object_version_number,
x_name,
x_target,
x_quota_type_code,
x_period_type_code,
x_usage_code,
x_payment_amount,
x_description,
x_start_date,
x_end_date,
x_quota_status,
x_start_num,
x_end_num,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_incentive_type_code,
x_credit_type_id,
x_calc_formula_id,
x_rt_sched_custom_flag,
x_package_name,
x_performance_goal,
x_interval_type_id,
x_payee_assign_flag,
x_vesting_flag,
x_quota_unspecified,
x_addup_from_rev_class_flag,
x_expense_account_id,
x_liability_account_id,
x_quota_group_code,
--clku, PAYMENT ENHANCEMENT
x_payment_group_code,
--clku, 2854576
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,
-- fmburu r12
x_indirect_credit,
x_salesrep_end_flag
);
ELSIF x_operation = 'DELETE'
THEN
DELETE_RECORD (x_quota_id, x_name);