[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT start_date, end_date
FROM fte_sel_group_assignments ga,
fte_sel_groups gr
WHERE gr.group_id = ga.group_id AND
gr.group_id <> p_group_id AND
ga.customer_id = p_assignee_id AND
p_assignee_type = 'CUST'
UNION ALL
SELECT start_date, end_date
FROM fte_sel_group_assignments ga,
fte_sel_groups gr
WHERE gr.group_id = ga.group_id AND
gr.group_id <> p_group_id AND
ga.customer_site_id = p_assignee_id AND
p_assignee_type = 'CUST_SITE'
UNION ALL
SELECT start_date, end_date
FROM fte_sel_group_assignments ga,
fte_sel_groups gr
WHERE gr.group_id = ga.group_id AND
gr.group_id <> p_group_id AND
ga.organization_id = p_assignee_id AND
p_assignee_type = 'ORG'
UNION ALL
SELECT start_date, end_date
FROM fte_sel_group_assignments ga,
fte_sel_groups gr
WHERE gr.group_id = ga.group_id AND
gr.group_id <> p_group_id AND
ga.organization_id is null AND
ga.customer_id is null AND
ga.customer_site_id is null AND
p_assignee_type = 'ENT';
SELECT carrier_id
FROM wsh_carrier_services
WHERE carrier_id = p_carrier_id AND
service_level = p_service_level AND
mode_of_transport = p_mode;
x_msg_data := 'Error in Delete_Results';
SELECT name
FROM fte_sel_groups
WHERE name = p_name AND
group_id <> p_group_id;
SELECT organization_id, customer_id, customer_site_id
FROM fte_sel_group_assignments
WHERE group_id = p_group_id;
if (p_mode <> 'UPDATE')
then
l_overlap := found_date_overlap(p_group_id => p_group_id,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_assignee_type => p_assignee_type,
p_assignee_id => p_assignee_id);
else -- p_mode = 'UPDATE'
/* CREATE/COPY has only one asignee
but UPDATE might have more than the current assignee
or might not be assigned at all through Search By Rule - Update
get all the assignees that this group is assigned to
for each assignee, validate whether the Start/End date overlaps
*/
FOR r_assignee IN c_assignee LOOP
IF (r_assignee.customer_id is not null) then
l_assignee_type := 'CUST';
SELECT group_id, start_date, end_date
FROM fte_sel_groups
WHERE name = p_group_name and
object_id = 1 and
nvl(end_date, sysdate) >= sysdate and
p_assignee_type = 'CUST' and
group_id NOT IN (select group_id from fte_sel_group_assignments
where customer_id = p_assignee_id or -- already
organization_id is not null or -- ORG
organization_id is null and -- ENT
customer_id is null and
customer_site_id is null)
UNION ALL
SELECT group_id, start_date, end_date
FROM fte_sel_groups
WHERE name = p_group_name and
object_id = 1 and
nvl(end_date, sysdate) >= sysdate and
p_assignee_type = 'CUST_SITE' and
group_id NOT IN (select group_id from fte_sel_group_assignments
where customer_site_id = p_assignee_id or -- already
organization_id is not null or -- ORG
organization_id is null and -- ENT
customer_id is null and
customer_site_id is null)
UNION ALL
SELECT group_id, start_date, end_date
FROM fte_sel_groups
WHERE name = p_group_name and
object_id = 1 and
nvl(end_date, sysdate) >= sysdate and
p_assignee_type = 'ORG' and
group_id NOT IN (select group_id from fte_sel_group_assignments
where organization_id = p_assignee_id or -- already
customer_id is not null or -- CUST
customer_site_id is not null) -- CUST_SITE
UNION ALL
SELECT group_id, start_date, end_date
FROM fte_sel_groups
WHERE name = p_group_name and
object_id = 1 and
nvl(end_date, sysdate) >= sysdate and
p_assignee_type = 'ENT' and
group_id NOT IN (select group_id from fte_sel_group_assignments
where customer_id is not null or -- CUST
customer_site_id is not null or -- CUST_SITE
organization_id is null and -- already
customer_id is null and
customer_site_id is null);
* Delete_Results
* 1) Delete data from FTE_SEL_RESULTS
*------------------------------------------------------------------------*/
PROCEDURE Delete_Results( p_group_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
BEGIN
DELETE FROM FTE_SEL_RESULTS
WHERE result_id in (select result_id
from fte_sel_rules r, fte_sel_result_assignments ra
where r.rule_id = ra.rule_id
and r.group_id = p_group_id);
x_msg_data := 'Error in Delete_Results';
END Delete_Results;
* 1) Insert data into FTE_SEL_RESULTS
* based on what are in FTE_SEL_RESULT_ASSIGNMENTS
* 2) Call FTE_ACS_RULE_UTIL_PKG.SET_RANGE_OVERLAP_FLAG
* to set the range_overlap_flag of each rule attribute
*------------------------------------------------------------------------*/
PROCEDURE Save_Results( p_group_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
BEGIN
INSERT INTO FTE_SEL_RESULTS(RESULT_ID, NAME, CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
SELECT result_id, to_char(result_id), sysdate,
-1, sysdate, -1, -1
from fte_sel_rules r, fte_sel_result_assignments ra
where r.rule_id = ra.rule_id
and r.group_id = p_group_id;
select fte_sel_groups_s.nextval
from dual;
select group_attribute_id,
attribute_id,
attribute_default_value,
attribute_uom_code,
attribute_name
from fte_sel_group_attributes
where group_id = x_group_id;
select FTE_SEL_RULES_S.NEXTVAL,
rule_id,
name,
precedence,
sequence_number
from fte_sel_rules
where group_id = xxx_group_id;
select rule_attribute_id,
rule_id,
attribute_name,
attribute_value_from,
attribute_value_to,
attribute_value_from_number,
attribute_value_to_number,
data_type,
grouping_number
from fte_sel_rule_restrictions
where rule_id = x_rule_id;
select FTE_SEL_RESULTS_S.NEXTVAL,
FTE_SEL_RESULT_ASSIGNMENTS_S.NEXTVAL,
result_id,
name,
description,
enabled_flag,
rank
from fte_sel_results
where result_id = x_result_id;
select result_assignment_id,
rule_id,
result_id
from fte_sel_result_assignments
where rule_id = xx_rule_id;
select FTE_SEL_RESULT_ATTRIBUTES_S.NEXTVAL,
result_attribute_id,
result_id,
attribute_code,
attribute_value
from fte_sel_result_Attributes
where result_id = xx_result_id;
SAVEPOINT insert_fte_sel_groups;
INSERT INTO fte_sel_groups(GROUP_ID,
NAME,
DESCRIPTION,
OBJECT_ID,
START_DATE,
END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ASSIGNED_FLAG,
GROUP_STATUS_FLAG)
(SELECT l_new_group_id,
l_t_new_group_id,
'Copy of '||name,
object_id,
start_date,
end_date,
sysdate,
-1,
sysdate,
-1,
-1,
'N',
group_status_flag
FROM fte_sel_groups
WHERE group_id = p_group_id);
ROLLBACK TO insert_fte_sel_groups;
t_group_attribute_id.DELETE;
t_attribute_id.DELETE;
t_attribute_default_value.DELETE;
t_attribute_uom_code.DELETE;
t_attribute_name.DELETE;
INSERT INTO fte_sel_group_attributes (
GROUP_ATTRIBUTE_ID,
ATTRIBUTE_ID,
ATTRIBUTE_DEFAULT_VALUE,
ATTRIBUTE_UOM_CODE,
GROUP_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_NAME)
VALUES
(FTE_SEL_GROUP_ATTRIBUTES_S.NEXTVAL,
t_attribute_id(j),
t_attribute_default_value(j),
t_attribute_uom_code(j),
l_new_group_id,
sysdate,
-1,
sysdate,
-1,
-1,
t_attribute_name(j));
t_rule_id.DELETE;
t_rule_name.DELETE;
t_rule_precedence.DELETE;
t_rule_sequence_number.DELETE;
INSERT INTO fte_sel_rules(
RULE_ID,
NAME,
GROUP_ID,
PRECEDENCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SEQUENCE_NUMBER)
VALUES (t_new_rule_id(k),
to_char(t_new_rule_id(k)),
l_new_group_id,
t_rule_precedence(k),
sysdate,
-1,
sysdate,
-1,
-1,
t_rule_sequence_number(k));
t_rest_rule_attribute_id.DELETE;
t_rest_rule_id.DELETE;
t_rest_attribute_name.DELETE;
t_rest_attribute_value_from.DELETE;
t_rest_attribute_value_to.DELETE;
t_rest_attribute_value_from_n.DELETE;
t_rest_attribute_value_to_n.DELETE;
t_rest_data_type.DELETE;
t_rest_grouping_number.DELETE;
INSERT INTO fte_sel_rule_restrictions(
RULE_ATTRIBUTE_ID,
RULE_ID,
ATTRIBUTE_NAME,
ATTRIBUTE_VALUE_FROM,
ATTRIBUTE_VALUE_TO,
ATTRIBUTE_VALUE_FROM_NUMBER,
ATTRIBUTE_VALUE_TO_NUMBER,
DATA_TYPE,
GROUPING_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
GROUP_ID)
VALUES (FTE_SEL_RULE_RESTRICTIONS_S.NEXTVAL,
t_new_rule_id(l),
t_rest_attribute_name(o),
t_rest_attribute_value_from(o),
t_rest_attribute_value_to(o),
t_rest_attribute_value_from_n(o),
t_rest_attribute_value_to_n(o),
t_rest_data_type(o),
t_rest_grouping_number(o),
sysdate,
-1,
sysdate,
-1,
-1,
l_new_group_id);
t_new_result_assignment_id.DELETE;
t_resass_result_assignment_id.DELETE;
t_resass_rule_id.DELETE;
t_resass_result_id.DELETE;
tt_new_result_assignment_id.DELETE;
tt_resass_result_assignment_id.DELETE;
tt_resass_rule_id.DELETE;
tt_resass_result_id.DELETE;
t_new_result_id.DELETE;
t_result_result_id.DELETE;
t_result_name.DELETE;
t_result_description.DELETE;
t_result_enabled_flag.DELETE;
t_result_rank.DELETE;
tt_new_result_id.DELETE;
tt_result_result_id.DELETE;
tt_result_name.DELETE;
tt_result_description.DELETE;
tt_result_enabled_flag.DELETE;
tt_result_rank.DELETE;
INSERT INTO fte_sel_results (
RESULT_ID,
NAME,
DESCRIPTION,
ENABLED_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RANK)
VALUES (tt_new_result_id(v),
to_char(tt_new_result_id(v)),
tt_result_description(v),
tt_result_enabled_flag(v),
sysdate,
-1,
sysdate,
-1,
-1,
tt_result_rank(v));
INSERT INTO fte_sel_result_assignments (
RESULT_ASSIGNMENT_ID,
RULE_ID,
RESULT_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (tt_new_result_assignment_id(w),
t_new_rule_id(p),
tt_new_result_id(w),
sysdate,
-1,
sysdate,
-1,
-1);
t_new_result_attribute_id.DELETE;
t_resattr_attribute_id.DELETE;
t_resattr_result_id.DELETE;
t_resattr_attribute_code.DELETE;
t_resattr_attribute_value.DELETE;
tt_new_result_attribute_id.DELETE;
tt_resattr_attribute_id.DELETE;
tt_resattr_result_id.DELETE;
tt_resattr_attribute_code.DELETE;
tt_resattr_attribute_value.DELETE;
INSERT INTO fte_sel_result_attributes (
RESULT_ATTRIBUTE_ID,
RESULT_ID,
ATTRIBUTE_CODE,
ATTRIBUTE_VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (tt_new_result_attribute_id(aa),
tt_new_result_id(x),
tt_resattr_attribute_code(aa),
tt_resattr_attribute_value(aa),
sysdate,
-1,
sysdate,
-1,
-1);
tt_new_result_attribute_id.DELETE;
tt_resattr_attribute_id.DELETE;
tt_resattr_result_id.DELETE;
tt_resattr_attribute_code.DELETE;
tt_resattr_attribute_value.DELETE;
t_new_result_id.DELETE;
t_result_result_id.DELETE;
t_result_name.DELETE;
t_result_description.DELETE;
t_result_enabled_flag.DELETE;
t_result_rank.DELETE;
tt_new_result_id.DELETE;
tt_result_result_id.DELETE;
tt_result_name.DELETE;
tt_result_description.DELETE;
tt_result_enabled_flag.DELETE;
tt_result_rank.DELETE;
tt_new_result_assignment_id.DELETE;
t_new_result_assignment_id.DELETE;
t_resass_result_assignment_id.DELETE;
t_resass_rule_id.DELETE;
t_resass_result_id.DELETE;
tt_resass_result_assignment_id.DELETE;
tt_resass_rule_id.DELETE;
tt_resass_result_id.DELETE;
SELECT attribute_value_from_number
FROM fte_sel_rule_restrictions
WHERE group_id = p_group_id
AND attribute_name like '%REGION_ID';
SELECT region_id INTO x_region_id
FROM wsh_regions_v
WHERE region_id = region_cur.attribute_value_from_number;