[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM cn_quotas_all
WHERE quota_id = p_quota_id;
SELECT DECODE (l_quota.quota_type_code,
'REVENUE', 'RNQ',
'TARGET', 'RQ',
'UNIT_BASED_QUOTA', 'UQ',
'UNIT_BASED_NON_QUOTA', 'UNQ',
'DISCOUNT', 'DIS',
'MARGIN', 'MR'
)
|| '_'
|| DECODE (l_quota.trx_group_code, 'INDIVIDUAL', 'In', 'GROUP', 'Gr', 'In')
|| '_'
|| DECODE (l_quota.discount_option_code, 'NONE', 'DiN', 'QUOTA', 'DiQ', 'PAYMENT', 'DiP', 'DiN')
|| '_'
|| DECODE (l_quota.payment_type_code, 'FIXED', 'Fix', 'PAYMENT', 'Pay', 'TRANSACTION', 'App', '')
|| '_'
|| DECODE (l_quota.cumulative_flag, 'Y', 'CuY', 'N', 'CuN', 'CuN')
|| '_'
|| DECODE (l_quota.split_flag, 'Y', 'SY', 'N', 'SN', 'SN')
|| '_'
|| DECODE (l_quota.itd_flag, 'Y', 'IY', 'N', 'IN', 'IN')
|| '_'
|| TO_CHAR (l_quota.org_id)
INTO l_formula_name
FROM DUAL;
SELECT calc_sql_exp_id
FROM cn_calc_sql_exps_all
WHERE NAME = p_name
AND ORG_ID = p_org_id;
l_sql_select VARCHAR2 (500);
SELECT DECODE (p_name,
'Revenue', 'ch.transaction_amount',
'Quantity', 'ch.quantity',
'Discount Percentage/100', 'ch.discount_percentage/100',
'Margin Percentage/100', 'ch.margin_percentage/100',
'ch.transaction_amount'
)
INTO l_sql_select
FROM DUAL;
p_sql_select => UPPER (l_sql_select),
p_sql_from => 'CN_COMMISSION_HEADERS CH',
p_piped_expression_disp => '(' || UPPER (p_name) || ')' || '|',
p_piped_sql_select => '(' || UPPER (l_sql_select) || ')' || '|',
p_piped_sql_from => 'CN_COMMISSION_HEADERS CH|',
x_calc_sql_exp_id => l_perf_measure_id,
x_exp_type_code => l_exp_type_code,
x_status => l_status,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_object_version_number => l_ovn
);
p_sql_select VARCHAR2,
p_sql_from VARCHAR2,
p_disp VARCHAR2,
p_piped_sql_from VARCHAR2,
x_calc_sql_exp_id OUT NOCOPY NUMBER
)
IS
CURSOR expr IS
SELECT calc_sql_exp_id
FROM cn_calc_sql_exps_all
WHERE NAME = p_exp_name
AND ORG_ID = p_org_id;
p_sql_select => p_sql_select,
p_sql_from => p_sql_from,
p_piped_expression_disp => '(' || p_disp || ')' || '|',
p_piped_sql_select => '(' || p_sql_select || ')|',
p_piped_sql_from => p_piped_sql_from,
x_calc_sql_exp_id => x_calc_sql_exp_id,
x_exp_type_code => l_exp_type_code,
x_status => l_status,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_object_version_number => l_ovn
);
SELECT calc_sql_exp_id
FROM cn_calc_sql_exps_all
WHERE NAME = p_name
and org_id = p_org_id;
p_sql_select => 'CH.DISCOUNT_PERCENTAGE/100',
p_sql_from => 'CN_COMMISSION_HEADERS CH',
p_piped_expression_disp => '(' || UPPER (p_name) || ')' || '|',
p_piped_sql_select => '(CH.DISCOUNT_PERCENTAGE/100)|',
p_piped_sql_from => 'CN_COMMISSION_HEADERS CH|',
x_calc_sql_exp_id => l_calc_sql_exp_id,
x_exp_type_code => l_exp_type_code,
x_status => l_status,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_object_version_number => l_ovn
);
SELECT calc_sql_exp_id
FROM cn_calc_sql_exps_all
WHERE NAME = p_name
and org_id = p_org_id;
p_sql_select => 'RateResult',
p_sql_from => 'DUAL',
p_piped_expression_disp => 'RateResult|',
p_piped_sql_select => 'RateResult|',
p_piped_sql_from => 'DUAL|',
x_calc_sql_exp_id => l_calc_sql_exp_id,
x_exp_type_code => l_exp_type_code,
x_status => l_status,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_object_version_number => l_ovn
);
SELECT f.calc_formula_id
FROM cn_calc_formulas_all f,
cn_quotas_all q
WHERE f.NAME = l_formula_name
AND q.quota_id = p_quota_id
AND ((q.org_id = f.org_id) OR (q.org_id IS NULL AND f.org_id IS NULL));
l_sql_select VARCHAR (500);
SELECT *
FROM cn_quotas_all
WHERE quota_id = p_quota_id;
SELECT calc_formula_id
FROM cn_calc_formulas_all
WHERE NAME = 'Discount Option Formula'
AND org_id = p_org_id;
SELECT DECODE (l_quota.quota_type_code,
'TARGET', 'Revenue',
'REVENUE', 'Revenue',
'UNIT_BASED_QUOTA', 'Quantity',
'UNIT_BASED_NON_QUOTA', 'Quantity',
'DISCOUNT', 'Discount Percentage',
'MARGIN', 'Margin Percentage',
'Revenue'
)
INTO l_perf_measure_name
FROM DUAL;
l_sql_select := 'CH.TRANSACTION_AMOUNT/CSPQ.ITD_TARGET';
l_sql_select := 'CH.TRANSACTION_AMOUNT/CSQA.TARGET';
l_sql_select := 'CH.TRANSACTION_AMOUNT';
l_sql_select := 'CH.QUANTITY/CSQA.TARGET';
l_sql_select := 'CH.QUANTITY';
l_sql_select := 'CH.DISCOUNT_PERCENTAGE/100';
l_sql_select := 'CH.MARGIN_PERCENTAGE/100';
l_sql_select := l_sql_select || '*CL.EVENT_FACTOR*CL.QUOTA_FACTOR';
l_sql_select :=
l_sql_select || '*cn_formula_' || ABS (l_discount_formula_id) || '_' || ABS (l_quota.org_id)
|| '_pkg.get_result(p_commission_line_id)';
l_sql_select := 'SUM(' || l_sql_select || ')';
l_sql_select,
l_sql_from,
l_exp_disp,
l_piped_sql_from,
l_input_exp_id);
l_sql_select := 'RateResult';
l_sql_select := l_sql_select || '*ABS(SUM(CH.QUANTITY))/SUM(CH.QUANTITY)';
l_sql_select := l_sql_select || '*CL.PAYMENT_FACTOR*ABS(CH.QUANTITY)/CH.QUANTITY';
l_sql_select := l_sql_select || '*ABS(SUM(CH.TRANSACTION_AMOUNT))/SUM(CH.TRANSACTION_AMOUNT)';
l_sql_select := l_sql_select || '*CL.PAYMENT_FACTOR*ABS(CH.TRANSACTION_AMOUNT)/CH.TRANSACTION_AMOUNT';
l_sql_select := l_sql_select || '*ABS(SUM(CSQA.PAYMENT_AMOUNT*CH.QUANTITY))/SUM(CH.QUANTITY)';
l_sql_select := l_sql_select || '*CSQA.PAYMENT_AMOUNT*CL.PAYMENT_FACTOR*ABS(CH.QUANTITY)/CH.QUANTITY';
l_sql_select := l_sql_select || '*ABS(SUM(CSQA.PAYMENT_AMOUNT*CH.TRANSACTION_AMOUNT))/SUM(CH.TRANSACTION_AMOUNT)';
l_sql_select := l_sql_select || '*CSQA.PAYMENT_AMOUNT*CL.PAYMENT_FACTOR*ABS(CH.TRANSACTION_AMOUNT)/CH.TRANSACTION_AMOUNT';
l_sql_select :=
l_sql_select
|| '*SUM(CL.PAYMENT_FACTOR*CH.TRANSACTION_AMOUNT*cn_formula_'
|| ABS (l_discount_formula_id)
|| '_'
|| ABS (l_quota.org_id)
|| '_pkg.get_result(p_commission_line_id))';
l_sql_select := l_sql_select || '*SUM(CL.PAYMENT_FACTOR*CH.TRANSACTION_AMOUNT)';
l_sql_select := l_sql_select || '*CL.PAYMENT_FACTOR*CH.TRANSACTION_AMOUNT';
l_sql_select :=
l_sql_select || '*cn_formula_' || ABS (l_discount_formula_id) || '_' || ABS (l_quota.org_id)
|| '_pkg.get_result(p_commission_line_id)';
l_sql_select,
l_sql_from,
l_exp_disp,
l_piped_sql_from,
l_output_exp_id);
SELECT *
FROM cn_quotas_all
WHERE quota_id = p_quota_id;