[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
LAST_UPDATE_DATE date_tbl_type,
LAST_UPDATED_BY number_tbl_type,
CREATION_DATE date_tbl_type,
CREATED_BY number_tbl_type,
LAST_UPDATE_LOGIN number_tbl_type,
TERR_QUAL_ID number_tbl_type,
COMPARISON_OPERATOR varchar2_tbl_type,
LOW_VALUE_CHAR varchar2_tbl_type,
HIGH_VALUE_CHAR varchar2_tbl_type,
LOW_VALUE_NUMBER number_tbl_type,
HIGH_VALUE_NUMBER number_tbl_type,
INTEREST_TYPE_ID number_tbl_type,
PRIMARY_INTEREST_CODE_ID number_tbl_type,
SECONDARY_INTEREST_CODE_ID number_tbl_type,
CURRENCY_CODE varchar2_tbl_type,
ID_USED_FLAG var_1_tbl_type,
LOW_VALUE_CHAR_ID number_tbl_type,
ORG_ID number_tbl_type,
VALUE1_ID number_tbl_type,
VALUE2_ID number_tbl_type,
VALUE3_ID number_tbl_type
);
LAST_UPDATE_DATE date_tbl_type,
LAST_UPDATED_BY number_tbl_type,
CREATION_DATE date_tbl_type,
CREATED_BY number_tbl_type,
LAST_UPDATE_LOGIN number_tbl_type,
TERR_ID number_tbl_type,
QUAL_USG_ID number_tbl_type,
ORG_ID number_tbl_type
);
SELECT TERR_NAME, trim(HIERARCHY) hierarchy, ORG_ID, org_name
FROM JTY_WEBADI_OTH_TERR_INTF
where user_sequence = v_user_sequence
and interface_type = v_intf_type
and status is null;
update JTY_WEBADI_OTH_TERR_INTF
set parent_terr_id = null
where user_sequence = p_user_sequence
and interface_type = p_intf_type
and hierarchy is null;
update JTY_WEBADI_OTH_TERR_INTF
set parent_terr_id = l_parent_id
, parent_terr_name = l_parent
where terr_name = l_hierarchy_tbl(c).terr_name
and user_sequence = p_user_sequence
and interface_type = p_intf_type;
select count(*) into l_count
from jtf_terr_all
where name = l_parent
and parent_territory_id = l_anc_id
and org_id = l_hierarchy_tbl(c).org_id;
update JTY_WEBADI_OTH_TERR_INTF
set parent_terr_id = null
, parent_terr_name = l_parent
where terr_name = l_hierarchy_tbl(c).terr_name
and user_sequence = p_user_sequence
and interface_type = p_intf_type;
-- lowest level parent_id found, update interface table
select terr_id into l_parent_id
from jtf_terr_all
where org_id = l_hierarchy_tbl(c).org_id
and name = l_parent
and parent_territory_id = l_anc_id;
update JTY_WEBADI_OTH_TERR_INTF
set parent_terr_id = l_parent_id
, parent_terr_name = l_parent
where terr_name = l_hierarchy_tbl(c).terr_name
and user_sequence = p_user_sequence
and interface_type = p_intf_type;
update JTY_WEBADI_OTH_TERR_INTF
set status = x_return_status
, error_msg = X_Msg_Data
where terr_name = l_hierarchy_tbl(c).terr_name
and user_sequence = p_user_sequence
and interface_type = p_intf_type;
select count(*) into l_count
from jtf_terr_all
where name = l_parent
and parent_territory_id = l_anc_id
and org_id = l_hierarchy_tbl(c).org_id;
update JTY_WEBADI_OTH_TERR_INTF
set parent_terr_id = null
, parent_terr_name = l_parent
where terr_name = l_hierarchy_tbl(c).terr_name
and user_sequence = p_user_sequence
and interface_type = p_intf_type;
select terr_id into l_parent_id
from jtf_terr_all
where org_id = l_hierarchy_tbl(c).org_id
and name = l_parent
and parent_territory_id = l_anc_id;
update JTY_WEBADI_OTH_TERR_INTF
set status = x_return_status
, error_msg = X_Msg_Data
where terr_name = l_hierarchy_tbl(c).terr_name
and user_sequence = p_user_sequence
and interface_type = p_intf_type;
select jqh.qualifier_name, sub.lay_seq_num
from JTY_WEBADI_QUAL_HEADER jqh,
(
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
1 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual1_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
2 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual2_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
3 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual3_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
4 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual4_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
5 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual5_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
6 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual6_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
7 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual7_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
8 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual8_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
9 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual9_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
10 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual10_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
11 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual11_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
12 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual12_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
13 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual13_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
14 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual14_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
15 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual15_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
16 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual16_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
17 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual17_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
18 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual18_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
19 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual19_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
20 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual20_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
21 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual21_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
22 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual22_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
23 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual23_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
24 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual24_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select jut.lay_seq_num, jut.user_sequence, jut.terr_type_id,
25 qual_num
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.qual25_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header) sub
where jqh.qualifier_num = sub.qual_num
and jqh.user_sequence = sub.user_sequence
and not exists (select 1 from jtf_terr_type_qual_all jttq
where jttq.terr_type_id = sub.terr_type_id
and jttq.qual_usg_id = jqh.qual_usg_id);
update JTY_WEBADI_OTH_TERR_INTF jwot
set status = x_return_status,
error_msg = X_Msg_Data
where jwot.terr_name is null
and jwot.USER_SEQUENCE = p_USER_SEQUENCE
and jwot.INTERFACE_TYPE = p_intf_type
and jwot.status is null;
update JTY_WEBADI_OTH_TERR_INTF jwot
set status = x_return_status,
error_msg = X_Msg_Data
where ( (jwot.TERR_ID IS NULL and jwot.header = 'TERR')
OR (jwot.header = 'QUAL' and jwot.TERR_QUAL_ID1 IS NULL
and jwot.TERR_QUAL_ID2 IS NULL and jwot.TERR_QUAL_ID3 IS NULL
and jwot.TERR_QUAL_ID4 IS NULL and jwot.TERR_QUAL_ID5 IS NULL
and jwot.TERR_QUAL_ID6 IS NULL and jwot.TERR_QUAL_ID7 IS NULL
and jwot.TERR_QUAL_ID8 IS NULL and jwot.TERR_QUAL_ID9 IS NULL
and jwot.TERR_QUAL_ID10 IS NULL and jwot.TERR_QUAL_ID11 IS NULL
and jwot.TERR_QUAL_ID12 IS NULL and jwot.TERR_QUAL_ID13 IS NULL
and jwot.TERR_QUAL_ID14 IS NULL and jwot.TERR_QUAL_ID15 IS NULL
and jwot.TERR_QUAL_ID16 IS NULL and jwot.TERR_QUAL_ID17 IS NULL
and jwot.TERR_QUAL_ID18 IS NULL and jwot.TERR_QUAL_ID19 IS NULL
and jwot.TERR_QUAL_ID20 IS NULL and jwot.TERR_QUAL_ID21 IS NULL
and jwot.TERR_QUAL_ID22 IS NULL and jwot.TERR_QUAL_ID23 IS NULL
and jwot.TERR_QUAL_ID24 IS NULL and jwot.TERR_QUAL_ID25 IS NULL)
OR EXISTS (select 1 from jty_webadi_resources jwr
where jwot.lay_seq_num = jwr.lay_seq_num
and jwr.terr_rsc_id is null
and jwr.header = l_header)
)
and jwot.USER_SEQUENCE = p_USER_SEQUENCE
and jwot.INTERFACE_TYPE = p_intf_type
and jwot.action_flag = l_action_flag
and jwot.status is null;
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.status = x_return_status,
jwot.error_msg = X_Msg_Data
where not exists ( SELECT 1
FROM jtf_terr_all jt
WHERE jt.TERRITORY_TYPE_ID = jwot.TERR_TYPE_ID
and jt.terr_id = jwot.TERR_ID
AND jt.ORG_ID = jwot.ORG_ID )
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.action_flag = l_action_flag
and jwot.header <> l_header
and jwot.status is null
AND jwot.TERR_ID is not null;
update JTY_WEBADI_OTH_TERR_INTF jwot
set status = x_return_status,
error_msg = X_Msg_Data
where jwot.TERR_TYPE_ID is null
AND jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.header <> l_header
and jwot.status is null;
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.status = x_return_status,
jwot.error_msg = X_Msg_Data
where exists
(select 1 from JTY_WEBADI_RESOURCES jwr
where jwot.lay_seq_num = jwr.lay_seq_num
AND jwr.TRANS_ACCESS_CODE1 is null
AND jwr.TRANS_ACCESS_CODE2 is null
AND jwr.TRANS_ACCESS_CODE3 is null
AND jwr.TRANS_ACCESS_CODE4 is null
AND jwr.TRANS_ACCESS_CODE5 is null
AND jwr.TRANS_ACCESS_CODE6 is null
AND jwr.TRANS_ACCESS_CODE7 is null
AND jwr.TRANS_ACCESS_CODE8 is null
AND jwr.TRANS_ACCESS_CODE9 is null
AND jwr.TRANS_ACCESS_CODE10 is null
and jwr.USER_SEQUENCE = jwot.USER_SEQUENCE
and jwr.interface_type = jwot.interface_type
and jwr.header = jwot.header)
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.header = l_header
and jwot.status is null;
UPDATE JTY_WEBADI_RESOURCES jut
SET jut.TRANS_ACCESS_CODE1 = NVL(jut.TRANS_ACCESS_CODE1,'NONE')
, jut.TRANS_ACCESS_CODE2 = NVL(jut.TRANS_ACCESS_CODE2,'NONE')
, jut.TRANS_ACCESS_CODE3 = NVL(jut.TRANS_ACCESS_CODE3,'NONE')
, jut.TRANS_ACCESS_CODE4 = NVL(jut.TRANS_ACCESS_CODE4,'NONE')
, jut.TRANS_ACCESS_CODE5 = NVL(jut.TRANS_ACCESS_CODE5,'NONE')
, jut.TRANS_ACCESS_CODE6 = NVL(jut.TRANS_ACCESS_CODE6,'NONE')
, jut.TRANS_ACCESS_CODE7 = NVL(jut.TRANS_ACCESS_CODE7,'NONE')
, jut.TRANS_ACCESS_CODE8 = NVL(jut.TRANS_ACCESS_CODE8,'NONE')
, jut.TRANS_ACCESS_CODE9 = NVL(jut.TRANS_ACCESS_CODE9,'NONE')
, jut.TRANS_ACCESS_CODE10 = NVL(jut.TRANS_ACCESS_CODE10,'NONE')
WHERE jut.USER_SEQUENCE = p_user_sequence
AND jut.header = 'RSC'
AND jut.INTERFACE_TYPE = p_intf_type;
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.status = x_return_status,
jwot.error_msg = X_Msg_Data
where exists
(select 1 from JTY_WEBADI_RESOURCES jwr
where jwot.lay_seq_num = jwr.lay_seq_num
AND jwr.TRANS_ACCESS_CODE1 = 'NONE'
AND jwr.TRANS_ACCESS_CODE2 = 'NONE'
AND jwr.TRANS_ACCESS_CODE3 = 'NONE'
AND jwr.TRANS_ACCESS_CODE4 = 'NONE'
AND jwr.TRANS_ACCESS_CODE5 = 'NONE'
AND jwr.TRANS_ACCESS_CODE6 = 'NONE'
AND jwr.TRANS_ACCESS_CODE7 = 'NONE'
AND jwr.TRANS_ACCESS_CODE8 = 'NONE'
AND jwr.TRANS_ACCESS_CODE9 = 'NONE'
AND jwr.TRANS_ACCESS_CODE10 = 'NONE'
and jwr.USER_SEQUENCE = jwot.USER_SEQUENCE
and jwr.interface_type = jwot.interface_type
and jwr.header = jwot.header)
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.header = l_header
and jwot.status is null;
update JTY_WEBADI_OTH_TERR_INTF jwot
set status = x_return_status
, error_msg = X_Msg_Data
where jwot.USER_SEQUENCE = p_USER_SEQUENCE
and jwot.INTERFACE_TYPE = p_intf_type
and jwot.status is null
and jwot.action_flag = 'C'
and jwot.header = 'RSC'
and exists( select 1
from JTF_TERR_RSC_ALL jtr,JTY_WEBADI_RESOURCES jwr
where jwr.USER_SEQUENCE = jwot.USER_SEQUENCE
and jwr.header = jwot.header
and jwr.INTERFACE_TYPE = jwot.INTERFACE_TYPE
and jwot.lay_seq_num = jwr.lay_seq_num
and jtr.TERR_ID = jwr.TERR_ID
and jtr.RESOURCE_ID = jwr.RESOURCE_ID
and decode(jtr.resource_type,'RS_GROUP',1,'RS_TEAM',2,'RS_ROLE',3,0) = jwr.resource_type
);
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.status = x_return_status,
jwot.error_msg = X_Msg_Data
where exists
(select 1 from JTY_WEBADI_RESOURCES jwr
where jwot.lay_seq_num = jwr.lay_seq_num
and jwr.USER_SEQUENCE = jwot.USER_SEQUENCE
and jwr.interface_type = jwot.interface_type
and jwr.header = jwot.header
and NOT(jwr.RES_START_DATE between jwot.TERR_START_DATE and jwot.TERR_END_DATE)
--and (jwr.RES_START_DATE <= nvl(jwot.TERR_START_DATE,sysdate))
)
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.header = l_header
and jwot.status is null;
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.status = x_return_status,
jwot.error_msg = X_Msg_Data
where exists
(select 1 from JTY_WEBADI_RESOURCES jwr
where jwot.lay_seq_num = jwr.lay_seq_num
and jwr.USER_SEQUENCE = jwot.USER_SEQUENCE
and jwr.interface_type = jwot.interface_type
and jwr.header = jwot.header
and not (jwr.RES_END_DATE between nvl(jwot.TERR_START_DATE,sysdate)
and NVL(jwot.TERR_END_DATE, ADD_MONTHS(NVL(jwot.TERR_START_DATE,SYSDATE), 12)))
)
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.header = l_header
and jwot.status is null;
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.status = x_return_status,
jwot.error_msg = decode(jwot.error_msg,null,X_Msg_Data||': '||qual_rec.qualifier_name(i),
jwot.error_msg ||', ' || qual_rec.qualifier_name(i))
where jwot.lay_seq_num = qual_rec.lay_seq_num(i)
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.header = l_header;
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
set status = x_return_status,
error_msg = X_Msg_Data
where jwot.interface_type = p_intf_type
AND jwot.HEADER= l_header
and jwot.USER_SEQUENCE = p_USER_SEQUENCE
AND jwot.hierarchy is not null
and jwot.TERR_START_DATE is not null
and jwot.status is null
and ( exists ( select 1
from JTY_WEBADI_OTH_TERR_INTF jwot2
where jwot.parent_terr_id is null
and NOT(jwot.TERR_START_DATE between NVL(jwot2.terr_start_date,SYSDATE)
and NVL(jwot2.TERR_END_DATE, ADD_MONTHS(NVL(jwot2.TERR_START_DATE,SYSDATE), 12)))
and jwot.parent_terr_name = jwot2.terr_name)
or exists ( select 1
from JTF_TERR_ALL jta
where jwot.parent_terr_id = jta.terr_id
and NOT(jwot.TERR_START_DATE between jta.START_DATE_ACTIVE and jta.END_DATE_ACTIVE))
);
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
set status = x_return_status,
error_msg = X_Msg_Data
where jwot.interface_type = p_intf_type
AND jwot.HEADER= l_header
and jwot.USER_SEQUENCE = p_USER_SEQUENCE
AND jwot.hierarchy is not null
AND jwot.TERR_END_DATE is not null
and jwot.status is null
and (exists ( select 1
from JTY_WEBADI_OTH_TERR_INTF jwot2
where jwot.parent_terr_id is null
and NOT (jwot.TERR_END_DATE between NVL(jwot2.terr_start_date,SYSDATE)
and NVL(jwot2.TERR_END_DATE, ADD_MONTHS(NVL(jwot2.TERR_START_DATE,SYSDATE), 12)))
and jwot.parent_terr_name = jwot2.terr_name)
or exists ( select 1
from JTF_TERR_ALL jta
where jwot.parent_terr_id = jta.terr_id
and NOT(jwot.TERR_END_DATE between jta.START_DATE_ACTIVE and jta.END_DATE_ACTIVE))
);
update JTY_WEBADI_OTH_TERR_INTF jwot
set status = x_return_status,
error_msg = X_Msg_Data
where (jwot.org_id is null
or mo_global.check_access(jwot.org_id) <> 'Y')
and jwot.USER_SEQUENCE = p_USER_SEQUENCE
and jwot.INTERFACE_TYPE = p_intf_type
and jwot.status is null;
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
set (status,error_msg) = (SELECT jwot2.STATUS, jwot2.error_msg
FROM JTY_WEBADI_OTH_TERR_INTF jwot2
where jwot.terr_name = jwot2.terr_name
and jwot2.header = l_header
and jwot.USER_SEQUENCE = jwot2.USER_SEQUENCE
and jwot.INTERFACE_TYPE = jwot2.INTERFACE_TYPE)
WHERE jwot.USER_SEQUENCE = p_USER_SEQUENCE
and jwot.INTERFACE_TYPE = p_intf_type
and jwot.header <> l_header
and jwot.status is null;
select count(*) into l_count
from JTY_WEBADI_OTH_TERR_INTF jwot
where status is not null
and jwot.USER_SEQUENCE = p_USER_SEQUENCE
and jwot.INTERFACE_TYPE = p_intf_type;
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
set status = x_return_status
where status is null
and jwot.USER_SEQUENCE = p_USER_SEQUENCE
and jwot.INTERFACE_TYPE = p_intf_type;
update JTY_WEBADI_OTH_TERR_INTF jwot
set LAST_UPDATE_LOGIN = l_login_id,
(terr_start_date,TERR_END_DATE) =
(SELECT NVL(jwot2.TERR_START_DATE,TRUNC(SYSDATE)),
NVL(jwot2.TERR_END_DATE, ADD_MONTHS(NVL(jwot2.TERR_START_DATE,TRUNC(SYSDATE)), 12) )
FROM JTY_WEBADI_OTH_TERR_INTF jwot2
WHERE jwot.terr_name = jwot2.terr_name
and jwot.user_sequence = jwot2.user_sequence
and jwot2.header = 'TERR'
and rownum = 1),
usage_id = (select jwot2.usage_id from
JTY_WEBADI_OTH_TERR_INTF jwot2
where jwot.user_sequence = jwot2.user_sequence
and interface_type = 'D'
and jwot2.usage_id is not null
and rownum = 1)
where jwot.user_sequence = p_user_sequence
and jwot.interface_type = l_intf_type
and jwot.action_flag = l_action_flag
and jwot.status is null;
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
SET (TERR_ID, TERR_START_DATE, TERR_END_DATE) =
(select jta.terr_id, NVL(jwot.TERR_START_DATE,jta.START_DATE_ACTIVE)
, NVL(jwot.TERR_END_DATE,jta.END_DATE_ACTIVE)
from jtf_terr_all jta
where jta.name = jwot.terr_name
--and jta.parent_territory_id = jwot.parent_terr_id
and rownum = 1)
where status is null
and interface_type = l_intf_type
and user_sequence = p_user_sequence
and exists (select 1
from jtf_terr_all jta
where jta.name = jwot.terr_name
--and jta.parent_territory_id = jwot.parent_terr_id
) ;
update JTY_WEBADI_OTH_TERR_INTF
set QUAL1_VALUE_ID = NULL, QUAL2_VALUE_ID = NULL
, QUAL3_VALUE_ID = NULL, QUAL4_VALUE_ID = NULL
, QUAL5_VALUE_ID = NULL, QUAL6_VALUE_ID = NULL
, QUAL7_VALUE_ID = NULL, QUAL8_VALUE_ID = NULL
, QUAL9_VALUE_ID = NULL, QUAL10_VALUE_ID = NULL
, QUAL11_VALUE_ID = NULL, QUAL12_VALUE_ID = NULL
, QUAL13_VALUE_ID = NULL, QUAL14_VALUE_ID = NULL
, QUAL15_VALUE_ID = NULL, QUAL16_VALUE_ID = NULL
, QUAL17_VALUE_ID = NULL, QUAL18_VALUE_ID = NULL
, QUAL19_VALUE_ID = NULL, QUAL20_VALUE_ID = NULL
, QUAL21_VALUE_ID = NULL, QUAL22_VALUE_ID = NULL
, QUAL23_VALUE_ID = NULL, QUAL24_VALUE_ID = NULL
, QUAL25_VALUE_ID = NULL
where status is null
and interface_type = l_intf_type
and user_sequence = p_user_sequence
and action_flag = l_action_flag
and header = 'QUAL';
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_id = null
where jwot.status is null
and jwot.interface_type = l_intf_type
and jwot.user_sequence = p_user_sequence
and jwot.action_flag = l_action_flag
and exists (select 1 from JTY_WEBADI_OTH_TERR_INTF jwot2
where jwot.terr_name = jwot2.terr_name
and jwot.action_flag = jwot2.action_flag
and jwot.user_sequence = jwot2.user_sequence
and jwot.interface_type = jwot2.interface_type
and jwot2.header = 'TERR');
UPDATE JTY_WEBADI_RESOURCES jwr
SET TERR_ID = (select jwot.terr_id
from JTY_WEBADI_OTH_TERR_INTF jwot
where jwr.lay_seq_num = jwot.lay_seq_num
and rownum = 1) ,
TERR_RSC_ID = NULL,
TERR_RSC_ACCESS_ID1 = NULL, TERR_RSC_ACCESS_ID2 = NULL,
TERR_RSC_ACCESS_ID3 = NULL, TERR_RSC_ACCESS_ID4 = NULL,
TERR_RSC_ACCESS_ID5 = NULL, TERR_RSC_ACCESS_ID6 = NULL,
TERR_RSC_ACCESS_ID7 = NULL, TERR_RSC_ACCESS_ID8 = NULL,
TERR_RSC_ACCESS_ID9 = NULL, TERR_RSC_ACCESS_ID10 = NULL
where exists
( select 1 from JTY_WEBADI_OTH_TERR_INTF jwot
where jwot.interface_type = l_intf_type
and jwot.user_sequence = p_user_sequence
and jwot.status is null
and jwot.action_flag = l_action_flag
and jwot.lay_seq_num = jwr.lay_seq_num
and jwot.header = 'RSC');
PROCEDURE UPDATE_TERR_QUAL_ID(
P_USER_SEQUENCE IN NUMBER,
P_INTF_TYPE IN VARCHAR2,
P_HEADER IN VARCHAR2)
IS
cursor get_terr_qual_id_csr(
v_header varchar2,
v_user_sequence number,
v_intf_type varchar2,
v_qual_num number
) IS
select jtq.terr_qual_id, jwot.lay_seq_num
from JTY_WEBADI_QUAL_HEADER qgt , jtf_terr_qual_all jtq, JTY_WEBADI_OTH_TERR_INTF jwot
WHERE qgt.qual_usg_id = jtq.qual_usg_id
and jtq.org_id = jwot.org_id
AND qgt.user_sequence = jwot.user_sequence
AND jtq.terr_id = jwot.terr_id
and jwot.header = v_header
AND jwot.USER_SEQUENCE = v_user_sequence
AND jwot.interface_type = v_intf_type
and qgt.qualifier_num = v_qual_num
and jwot.status is null
group by jwot.terr_id, jtq.terr_qual_id, jwot.lay_seq_num;
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id1 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('1 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id2 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('2 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id3 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('3 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id4 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('4 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id5 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('5 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id6 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('6 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id7 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('7 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id8 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('8 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id9 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('9 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id10 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('10 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id11 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('11 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id12 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('12 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id13 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('13 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id14 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('14 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id15 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('15 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id16 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('16 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id17 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('17 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id18 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('18 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id19 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('19 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id20 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('20 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id21 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('21 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id22 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('22 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id23 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('23 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id24 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('24 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
update JTY_WEBADI_OTH_TERR_INTF jwot
set jwot.terr_qual_id25 = l_Terr_Qual_Rec.TERR_QUAL_ID(j)
where jwot.lay_seq_num = l_terr_qual_rec.lay_seq_num(j)
and jwot.header = p_header
and jwot.user_sequence = p_user_sequence
and jwot.interface_type = p_intf_type
and jwot.status is null;
--dbms_output.put_line('25 Update terr_qual_id, actual row processed: '||SQL%ROWCOUNT);
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
SET ACTION_FLAG = 'U'
WHERE LAY_SEQ_NUM not in
( SELECT MIN(jwot2.LAY_SEQ_NUM)
FROM JTY_WEBADI_OTH_TERR_INTF jwot2
WHERE jwot.ACTION_FLAG = jwot2.ACTION_FLAG
and jwot.header = jwot2.header
AND jwot.USER_SEQUENCE = jwot2.USER_SEQUENCE
AND jwot.interface_type = jwot2.interface_type
AND jwot.ACTION_FLAG = jwot2.action_flag
group by jwot.terr_id)
AND jwot.ACTION_FLAG = 'C'
and jwot.header = p_header
AND jwot.USER_SEQUENCE = p_user_sequence
AND jwot.interface_type = p_intf_type;
END UPDATE_TERR_QUAL_ID;
PROCEDURE delete_records(
P_USER_SEQUENCE IN NUMBER,
P_INTF_TYPE IN VARCHAR2,
p_action_flag IN VARCHAR2)
IS
cursor get_del_terr_csr(
v_user_sequence number,
v_action_flag varchar2,
v_intf_type varchar2,
v_header varchar2
) IS
SELECT TERR_ID, lay_seq_num
FROM JTY_WEBADI_OTH_TERR_INTF
WHERE interface_type = v_intf_type
AND action_flag = v_action_flag
AND user_sequence = v_user_sequence
AND header = v_header
AND status IS NULL;
select sub.terr_qual_id, sub.lay_seq_num
from ( select terr_qual_id1 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual1_value_id is not null
and jwot.status is null
union all
select terr_qual_id2 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual2_value_id is not null
and jwot.status is null
union all
select terr_qual_id3 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual3_value_id is not null
and jwot.status is null
union all
select terr_qual_id4 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual4_value_id is not null
and jwot.status is null
union all
select terr_qual_id5 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual5_value_id is not null
and jwot.status is null
union all
select terr_qual_id6 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual6_value_id is not null
and jwot.status is null
union all
select terr_qual_id7 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual7_value_id is not null
and jwot.status is null
union all
select terr_qual_id8 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual8_value_id is not null
and jwot.status is null
union all
select terr_qual_id9 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual9_value_id is not null
and jwot.status is null
union all
select terr_qual_id10 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual10_value_id is not null
and jwot.status is null
union all
select terr_qual_id11 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual11_value_id is not null
and jwot.status is null
union all
select terr_qual_id12 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual12_value_id is not null
and jwot.status is null
union all
select terr_qual_id13 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual13_value_id is not null
and jwot.status is null
union all
select terr_qual_id14 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual14_value_id is not null
and jwot.status is null
union all
select terr_qual_id15 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual15_value_id is not null
and jwot.status is null
union all
select terr_qual_id16 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual16_value_id is not null
and jwot.status is null
union all
select terr_qual_id17 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual17_value_id is not null
and jwot.status is null
union all
select terr_qual_id18 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual18_value_id is not null
and jwot.status is null
union all
select terr_qual_id19 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual19_value_id is not null
and jwot.status is null
union all
select terr_qual_id20 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual20_value_id is not null
and jwot.status is null
union all
select terr_qual_id21 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual21_value_id is not null
and jwot.status is null
union all
select terr_qual_id22 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual22_value_id is not null
and jwot.status is null
union all
select terr_qual_id23 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual23_value_id is not null
and jwot.status is null
union all
select terr_qual_id24 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual24_value_id is not null
and jwot.status is null
union all
select terr_qual_id25 terr_qual_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual25_value_id is not null
and jwot.status is null
) sub;
select sub.qual_value_id, sub.lay_seq_num
from ( select qual1_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual1_value_id is not null
and jwot.status is null
union all
select qual2_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual2_value_id is not null
and jwot.status is null
union all
select qual3_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual3_value_id is not null
and jwot.status is null
union all
select qual4_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual4_value_id is not null
and jwot.status is null
union all
select qual5_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual5_value_id is not null
and jwot.status is null
union all
select qual6_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual6_value_id is not null
and jwot.status is null
union all
select qual7_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual7_value_id is not null
and jwot.status is null
union all
select qual8_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual8_value_id is not null
and jwot.status is null
union all
select qual9_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual9_value_id is not null
and jwot.status is null
union all
select qual10_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual10_value_id is not null
and jwot.status is null
union all
select qual11_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual11_value_id is not null
and jwot.status is null
union all
select qual12_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual12_value_id is not null
and jwot.status is null
union all
select qual13_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual13_value_id is not null
and jwot.status is null
union all
select qual14_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual14_value_id is not null
and jwot.status is null
union all
select qual15_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual15_value_id is not null
and jwot.status is null
union all
select qual16_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual16_value_id is not null
and jwot.status is null
union all
select qual17_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual17_value_id is not null
and jwot.status is null
union all
select qual18_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual18_value_id is not null
and jwot.status is null
union all
select qual19_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual19_value_id is not null
and jwot.status is null
union all
select qual20_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual20_value_id is not null
and jwot.status is null
union all
select qual21_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual21_value_id is not null
and jwot.status is null
union all
select qual22_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual22_value_id is not null
and jwot.status is null
union all
select qual23_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual23_value_id is not null
and jwot.status is null
union all
select qual24_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual24_value_id is not null
and jwot.status is null
union all
select qual25_value_id qual_value_id, lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF jwot
WHERE jwot.user_sequence = v_user_sequence
and jwot.action_flag = v_action_flag
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.qual25_value_id is not null
and jwot.status is null
) sub;
SELECT jwr.TERR_RSC_ID, jwot.lay_seq_num
FROM JTY_WEBADI_OTH_TERR_INTF jwot,
JTY_WEBADI_RESOURCES jwr
WHERE jwot.lay_seq_num = jwr.lay_seq_num
and jwot.header = jwr.header
and jwot.user_sequence = jwr.user_sequence
AND jwot.interface_type = jwr.interface_type
AND jwot.interface_type = v_intf_type
AND jwot.action_flag = v_action_flag
AND jwot.user_sequence = v_user_sequence
AND jwot.header = v_header
AND jwot.status IS NULL;
--Delete Territory Values
DELETE from JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID IN
( SELECT TERR_QUAL_ID FROM JTF_TERR_QUAL_ALL
WHERE TERR_ID = l_del_terr_rec.terr_id(i) );
DELETE from JTF_TERR_QUAL_ALL WHERE TERR_ID = l_del_terr_rec.terr_id(i);
DELETE from JTF_TERR_QTYPE_USGS_ALL WHERE TERR_ID = l_del_terr_rec.terr_id(i);
DELETE from JTF_TERR_USGS_ALL WHERE TERR_ID = l_del_terr_rec.terr_id(i);
DELETE from JTF_TERR_RSC_ACCESS_ALL WHERE TERR_RSC_ID IN
( SELECT TERR_RSC_ID FROM JTF_TERR_RSC_ALL
WHERE TERR_ID = l_del_terr_rec.terr_id(i) );
DELETE from JTF_TERR_RSC_ALL Where TERR_ID = l_del_terr_rec.terr_id(i);
DELETE from JTF_TERR_ALL WHERE TERR_ID = l_del_terr_rec.terr_id(i);
-- update all records including the qual and rsc which will be deleted
forall i in l_del_terr_rec.terr_id.first..l_del_terr_rec.terr_id.last
update JTY_WEBADI_OTH_TERR_INTF
set status = x_return_status
where terr_id = l_del_terr_rec.terr_id(i)
and user_sequence = p_user_sequence
and interface_type = p_intf_type;
fnd_message.set_name ('JTF', 'JTY_OTH_TERR_DELETE_TERR');
update JTY_WEBADI_OTH_TERR_INTF
set status = x_return_status,
error_msg = x_msg_data
where lay_seq_num = l_del_terr_rec.lay_seq_num(i)
and user_sequence = p_user_sequence
and interface_type = p_intf_type;
DELETE FROM JTF_TERR_VALUES_ALL
WHERE TERR_VALUE_ID = l_del_qual_val_rec.qual_value_id(i);
SELECT COUNT(*) INTO l_qual_values_count FROM JTF_TERR_VALUES_ALL WHERE TERR_QUAL_ID = l_del_qual_rec.terr_qual_id;
DELETE from JTF_TERR_QUAL_ALL jtq
WHERE jtq.terr_qual_id = l_del_qual_rec.terr_qual_id;
update JTY_WEBADI_OTH_TERR_INTF
set status = x_return_status
where lay_seq_num = l_del_qual_val_rec.lay_seq_num(i)
and user_sequence = p_user_sequence
and action_flag = p_action_flag
and header = l_header
and interface_type = p_intf_type;
fnd_message.set_name ('JTF', 'JTY_OTH_TERR_DELETE_QUAL');
update JTY_WEBADI_OTH_TERR_INTF
set status = x_return_status,
error_msg = x_msg_data
where lay_seq_num = l_del_qual_val_rec.lay_seq_num(i)
and user_sequence = p_user_sequence
and action_flag = p_action_flag
and header = l_header
and interface_type = p_intf_type;
DELETE FROM JTF_TERR_RSC_ACCESS_ALL
WHERE TERR_RSC_ID = l_del_rsc_rec.terr_rsc_id(i);
DELETE FROM JTF_TERR_RSC_ALL
WHERE TERR_RSC_ID = l_del_rsc_rec.terr_rsc_id(i);
update JTY_WEBADI_OTH_TERR_INTF
set status = x_return_status
where lay_seq_num = l_del_rsc_rec.lay_seq_num(i)
and user_sequence = p_user_sequence
and action_flag = p_action_flag
and header = l_header
and interface_type = p_intf_type;
fnd_message.set_name ('JTF', 'JTY_OTH_TERR_DELETE_RSC');
update JTY_WEBADI_OTH_TERR_INTF
set status = x_return_status,
error_msg = x_msg_data
where lay_seq_num = l_del_rsc_rec.lay_seq_num(i)
and user_sequence = p_user_sequence
and action_flag = p_action_flag
and header = l_header
and interface_type = p_intf_type;
END delete_records;
PROCEDURE INSERT_TERR_QUAL(
p_terr_qual_rec IN OUT NOCOPY Terr_Qual_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_OVERLAP_ALLOWED_FLAG varchar2(1) := 'Y';
INSERT INTO JTF_TERR_QUAL_ALL(
TERR_QUAL_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_ID,
QUAL_USG_ID,
ORG_ID,
OVERLAP_ALLOWED_FLAG)
VALUES (
--JTF_TERR_QUAL_s.nextval,
l_Terr_Qual_Rec.TERR_QUAL_ID(i),
l_Terr_Qual_Rec.last_update_date(i),
l_Terr_Qual_Rec.last_updated_by(i),
l_Terr_Qual_Rec.creation_date(i),
l_Terr_Qual_Rec.created_by(i),
l_Terr_Qual_Rec.last_update_login(i),
l_Terr_Qual_Rec.terr_id(i),
l_Terr_Qual_Rec.qual_usg_id(i),
l_Terr_Qual_Rec.org_id(i),
l_OVERLAP_ALLOWED_FLAG );
END INSERT_TERR_QUAL;
PROCEDURE INSERT_TERR_VALUES (
p_terr_qual_rec IN Terr_Qual_Rec_Type,
p_terr_values_out_rec OUT NOCOPY Terr_values_out_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Terr_Values_Rec Terr_values_rec_type;
l_Terr_Values_Rec.LAST_UPDATE_DATE(i) := l_Terr_Qual_Rec.LAST_UPDATE_DATE(i);
l_Terr_Values_Rec.LAST_UPDATED_BY(i) := l_Terr_Qual_Rec.LAST_UPDATED_BY(i);
l_Terr_Values_Rec.LAST_UPDATE_LOGIN(i) := l_Terr_Qual_Rec.LAST_UPDATE_LOGIN(i);
--dbms_output.put_line(l_Terr_Values_Rec.LAST_UPDATED_BY(i)||', '||
l_Terr_Values_Rec.LAST_UPDATE_DATE(i)||', '||
l_Terr_Values_Rec.CREATED_BY(i)||', '||
l_Terr_Values_Rec.CREATION_DATE(i)||', '||
l_Terr_Values_Rec.LAST_UPDATE_LOGIN(i)||', '||
l_Terr_Values_Rec.TERR_QUAL_ID(i)||', '||
l_Terr_Values_Rec.COMPARISON_OPERATOR(i)||', '||
l_Terr_Values_Rec.LOW_VALUE_CHAR(i)||', '||
l_Terr_Values_Rec.HIGH_VALUE_CHAR(i)||', '||
l_Terr_Values_Rec.LOW_VALUE_NUMBER(i)||', '||
l_Terr_Values_Rec.HIGH_VALUE_NUMBER(i)||', '||
l_Terr_Values_Rec.INTEREST_TYPE_ID(i)||', '||
l_Terr_Values_Rec.PRIMARY_INTEREST_CODE_ID(i)||', '||
l_Terr_Values_Rec.SECONDARY_INTEREST_CODE_ID(i)||', '||
l_Terr_Values_Rec.CURRENCY_CODE(i)||', '||
l_Terr_Values_Rec.ID_USED_FLAG(i)||', '||
l_Terr_Values_Rec.LOW_VALUE_CHAR_ID(i)||', '||
l_Terr_Values_Rec.ORG_ID(i)||', '||
l_Terr_Values_Rec.VALUE1_ID(i)||', '||
l_Terr_Values_Rec.VALUE2_ID(i)||', '||
l_Terr_Values_Rec.VALUE3_ID(i) );
INSERT INTO JTF_TERR_VALUES_ALL(
TERR_VALUE_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
TERR_QUAL_ID,
COMPARISON_OPERATOR,
LOW_VALUE_CHAR,
HIGH_VALUE_CHAR,
LOW_VALUE_NUMBER,
HIGH_VALUE_NUMBER,
INTEREST_TYPE_ID,
PRIMARY_INTEREST_CODE_ID,
SECONDARY_INTEREST_CODE_ID,
CURRENCY_CODE,
ID_USED_FLAG,
LOW_VALUE_CHAR_ID,
ORG_ID,
VALUE1_ID,
VALUE2_ID,
VALUE3_ID
)
VALUES (
JTF_TERR_VALUES_s.nextval,
l_Terr_Values_Rec.LAST_UPDATED_BY(i),
l_Terr_Values_Rec.LAST_UPDATE_DATE(i),
l_Terr_Values_Rec.CREATED_BY(i),
l_Terr_Values_Rec.CREATION_DATE(i),
l_Terr_Values_Rec.LAST_UPDATE_LOGIN(i),
l_Terr_Values_Rec.TERR_QUAL_ID(i),
l_Terr_Values_Rec.COMPARISON_OPERATOR(i),
l_Terr_Values_Rec.LOW_VALUE_CHAR(i),
l_Terr_Values_Rec.HIGH_VALUE_CHAR(i),
l_Terr_Values_Rec.LOW_VALUE_NUMBER(i),
l_Terr_Values_Rec.HIGH_VALUE_NUMBER(i),
l_Terr_Values_Rec.INTEREST_TYPE_ID(i),
l_Terr_Values_Rec.PRIMARY_INTEREST_CODE_ID(i),
l_Terr_Values_Rec.SECONDARY_INTEREST_CODE_ID(i),
l_Terr_Values_Rec.CURRENCY_CODE(i),
l_Terr_Values_Rec.ID_USED_FLAG(i),
l_Terr_Values_Rec.LOW_VALUE_CHAR_ID(i),
l_Terr_Values_Rec.ORG_ID(i),
l_Terr_Values_Rec.VALUE1_ID(i),
l_Terr_Values_Rec.VALUE2_ID(i),
l_Terr_Values_Rec.VALUE3_ID(i)
) RETURNING TERR_VALUE_ID,TERR_QUAL_ID
BULK COLLECT INTO p_terr_values_out_rec.TERR_VALUE_ID,
p_terr_values_out_rec.TERR_QUAL_ID;
END INSERT_TERR_VALUES;
PROCEDURE UPDATE_TERR(
p_user_sequence IN number,
p_action_flag IN varchar2,
x_return_status OUT NOCOPY varchar2,
x_msg_data OUT NOCOPY varchar2
) IS
CURSOR get_terr_all_csr (
v_user_sequence number,
v_header varchar2,
v_action_flag varchar2,
v_intf_type varchar2) IS
Select
NVL(TERR_ID,JTF_TERR_s.nextval) TERR_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
'JTF' APPLICATION_SHORT_NAME,
TERR_NAME NAME,
'Y' ENABLED_FLAG,
NVL(TERR_START_DATE,TRUNC(SYSDATE)) START_DATE_ACTIVE,
RANK,
NVL(TERR_END_DATE, ADD_MONTHS(NVL(TERR_START_DATE,TRUNC(SYSDATE)), 12) )END_DATE_ACTIVE,
TERR_NAME DESCRIPTION,
'Y' UPDATE_FLAG,
TERR_TYPE_ID TERRITORY_TYPE_ID,
PARENT_TERR_ID PARENT_TERRITORY_ID,
'N' TEMPLATE_FLAG,
'N' ESCALATION_TERRITORY_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORG_ID,
NUM_WINNERS,
0 NUM_QUAL,
lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF
where header = v_header
and status is null
and USER_SEQUENCE = v_USER_SEQUENCE
and INTERFACE_TYPE = v_intf_type
and action_flag = v_action_flag;
LAST_UPDATE_DATE date_tbl_type,
LAST_UPDATED_BY number_tbl_type,
CREATION_DATE date_tbl_type,
CREATED_BY number_tbl_type,
LAST_UPDATE_LOGIN number_tbl_type,
APPLICATION_SHORT_NAME varchar2_tbl_type,
NAME var_2000_tbl_type,
ENABLED_FLAG var_1_tbl_type,
START_DATE_ACTIVE date_tbl_type,
RANK number_tbl_type,
END_DATE_ACTIVE date_tbl_type,
DESCRIPTION varchar2_tbl_type,
UPDATE_FLAG var_1_tbl_type,
TERRITORY_TYPE_ID number_tbl_type,
PARENT_TERRITORY_ID number_tbl_type,
TEMPLATE_FLAG var_1_tbl_type,
ESCALATION_TERRITORY_FLAG var_1_tbl_type,
ATTRIBUTE_CATEGORY varchar2_tbl_type,
ATTRIBUTE1 varchar2_tbl_type,
ATTRIBUTE2 varchar2_tbl_type,
ATTRIBUTE3 varchar2_tbl_type,
ATTRIBUTE4 varchar2_tbl_type,
ATTRIBUTE5 varchar2_tbl_type,
ATTRIBUTE6 varchar2_tbl_type,
ATTRIBUTE7 varchar2_tbl_type,
ATTRIBUTE8 varchar2_tbl_type,
ATTRIBUTE9 varchar2_tbl_type,
ATTRIBUTE10 varchar2_tbl_type,
ATTRIBUTE11 varchar2_tbl_type,
ATTRIBUTE12 varchar2_tbl_type,
ATTRIBUTE13 varchar2_tbl_type,
ATTRIBUTE14 varchar2_tbl_type,
ATTRIBUTE15 varchar2_tbl_type,
ORG_ID number_tbl_type ,
NUM_WINNERS number_tbl_type,
NUM_QUAL number_tbl_type,
LAY_SEQ_NUM number_tbl_type
);
l_terr_all_rec.LAST_UPDATE_DATE, l_terr_all_rec.LAST_UPDATED_BY,
l_terr_all_rec.CREATION_DATE, l_terr_all_rec.CREATED_BY,
l_terr_all_rec.LAST_UPDATE_LOGIN, l_terr_all_rec.APPLICATION_SHORT_NAME,
l_terr_all_rec.NAME, l_terr_all_rec.ENABLED_FLAG,
l_terr_all_rec.START_DATE_ACTIVE, l_terr_all_rec.RANK,
l_terr_all_rec.END_DATE_ACTIVE, l_terr_all_rec.DESCRIPTION,
l_terr_all_rec.UPDATE_FLAG, l_terr_all_rec.TERRITORY_TYPE_ID,
l_terr_all_rec.PARENT_TERRITORY_ID, l_terr_all_rec.TEMPLATE_FLAG,
l_terr_all_rec.ESCALATION_TERRITORY_FLAG, l_terr_all_rec.ATTRIBUTE_CATEGORY,
l_terr_all_rec.ATTRIBUTE1, l_terr_all_rec.ATTRIBUTE2, l_terr_all_rec.ATTRIBUTE3,
l_terr_all_rec.ATTRIBUTE4, l_terr_all_rec.ATTRIBUTE5, l_terr_all_rec.ATTRIBUTE6,
l_terr_all_rec.ATTRIBUTE7, l_terr_all_rec.ATTRIBUTE8, l_terr_all_rec.ATTRIBUTE9,
l_terr_all_rec.ATTRIBUTE10,l_terr_all_rec.ATTRIBUTE11,l_terr_all_rec.ATTRIBUTE12,
l_terr_all_rec.ATTRIBUTE13,l_terr_all_rec.ATTRIBUTE14,l_terr_all_rec.ATTRIBUTE15,
l_terr_all_rec.ORG_ID, l_terr_all_rec.NUM_WINNERS, l_terr_all_rec.NUM_QUAL,
l_terr_all_rec.lay_seq_num;
INSERT INTO JTF_TERR_ALL(
TERR_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
APPLICATION_SHORT_NAME, NAME, ENABLED_FLAG,
START_DATE_ACTIVE, RANK, END_DATE_ACTIVE, DESCRIPTION,
UPDATE_FLAG, TERRITORY_TYPE_ID, PARENT_TERRITORY_ID,
TEMPLATE_FLAG, ESCALATION_TERRITORY_FLAG, ATTRIBUTE_CATEGORY,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
ORG_ID, NUM_WINNERS, NUM_QUAL
) VALUES (
l_terr_all_rec.TERR_ID(i),
l_terr_all_rec.LAST_UPDATE_DATE(i), l_terr_all_rec.LAST_UPDATED_BY(i),
l_terr_all_rec.CREATION_DATE(i), l_terr_all_rec.CREATED_BY(i),
l_terr_all_rec.LAST_UPDATE_LOGIN(i), l_terr_all_rec.APPLICATION_SHORT_NAME(i),
l_terr_all_rec.NAME(i), l_terr_all_rec.ENABLED_FLAG(i),
l_terr_all_rec.START_DATE_ACTIVE(i), l_terr_all_rec.RANK(i),
l_terr_all_rec.END_DATE_ACTIVE(i), l_terr_all_rec.DESCRIPTION(i),
l_terr_all_rec.UPDATE_FLAG(i), l_terr_all_rec.TERRITORY_TYPE_ID(i),
l_terr_all_rec.PARENT_TERRITORY_ID(i), l_terr_all_rec.TEMPLATE_FLAG(i),
l_terr_all_rec.ESCALATION_TERRITORY_FLAG(i), l_terr_all_rec.ATTRIBUTE_CATEGORY(i),
l_terr_all_rec.ATTRIBUTE1(i), l_terr_all_rec.ATTRIBUTE2(i), l_terr_all_rec.ATTRIBUTE3(i),
l_terr_all_rec.ATTRIBUTE4(i), l_terr_all_rec.ATTRIBUTE5(i), l_terr_all_rec.ATTRIBUTE6(i),
l_terr_all_rec.ATTRIBUTE7(i), l_terr_all_rec.ATTRIBUTE8(i), l_terr_all_rec.ATTRIBUTE9(i),
l_terr_all_rec.ATTRIBUTE10(i),l_terr_all_rec.ATTRIBUTE11(i),l_terr_all_rec.ATTRIBUTE12(i),
l_terr_all_rec.ATTRIBUTE13(i),l_terr_all_rec.ATTRIBUTE14(i),l_terr_all_rec.ATTRIBUTE15(i),
l_terr_all_rec.ORG_ID(i), l_terr_all_rec.NUM_WINNERS(i), l_terr_all_rec.NUM_QUAL(i));
update JTY_WEBADI_OTH_TERR_INTF
set parent_terr_id = l_terr_all_rec.terr_id(i)
where parent_terr_name = l_terr_all_rec.name(i)
and USER_SEQUENCE = P_USER_SEQUENCE
and interface_type = l_intf_type
and parent_terr_id is null;
update JTY_WEBADI_OTH_TERR_INTF
set terr_id = l_terr_all_rec.terr_id(i)
where terr_name = l_terr_all_rec.name(i)
and USER_SEQUENCE = P_USER_SEQUENCE
and interface_type = l_intf_type
and action_flag = p_action_flag;
INSERT INTO JTF_TERR_USGS_ALL(
TERR_USG_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_ID,
SOURCE_ID,
ORG_ID
)
SELECT
JTF_TERR_USGS_s.nextval TERR_USG_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_ID,
usage_id SOURCE_ID,
ORG_ID
from JTY_WEBADI_OTH_TERR_INTF
where header = l_header
and status is null
and USER_SEQUENCE = p_USER_SEQUENCE
and INTERFACE_TYPE = l_intf_type
and action_flag = p_action_flag;
INSERT INTO JTF_TERR_QTYPE_USGS_ALL(
TERR_QTYPE_USG_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
TERR_ID,
QUAL_TYPE_USG_ID,
ORG_ID
)
select
JTF_TERR_QTYPE_USGS_s.nextval TERR_QUAL_TYPE_USG_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
TERR_ID,
QUAL_TYPE_ID QUAL_TYPE_USG_ID,
ORG_ID
from JTY_WEBADI_QUAL_TYPE_HEADER gt,
JTY_WEBADI_OTH_TERR_INTF jut
where jut.header = L_header
and jut.status is null
and jut.USER_SEQUENCE = P_USER_SEQUENCE
and jut.INTERFACE_TYPE = l_intf_type
and jut.action_flag = p_action_flag
and gt.user_sequence = jut.user_sequence;
update JTY_WEBADI_OTH_TERR_INTF
set status = x_return_status
, error_msg = X_Msg_Data
where lay_seq_num = l_terr_all_rec.lay_seq_num(i)
and interface_type = l_intf_type
and header = l_header
and user_sequence = p_user_sequence;
Update JTF_TERR_ALL
SET
LAST_UPDATE_DATE = l_terr_all_rec.LAST_UPDATE_DATE(i),
LAST_UPDATED_BY = l_terr_all_rec.LAST_UPDATED_BY(i),
LAST_UPDATE_LOGIN = l_terr_all_rec.LAST_UPDATE_LOGIN(i),
NAME = l_terr_all_rec.NAME(i),
START_DATE_ACTIVE = l_terr_all_rec.START_DATE_ACTIVE(i),
END_DATE_ACTIVE = l_terr_all_rec.END_DATE_ACTIVE(i),
PARENT_TERRITORY_ID = l_terr_all_rec.PARENT_TERRITORY_ID(i),
TERRITORY_TYPE_ID = l_terr_all_rec.TERRITORY_TYPE_ID(i),
RANK = l_terr_all_rec.RANK(i),
DESCRIPTION = l_terr_all_rec.DESCRIPTION(i),
ENABLED_FLAG = l_terr_all_rec.ENABLED_FLAG(i),
ATTRIBUTE_CATEGORY = l_terr_all_rec.ATTRIBUTE_CATEGORY(i),
ATTRIBUTE1 = l_terr_all_rec.ATTRIBUTE1(i),
ATTRIBUTE2 = l_terr_all_rec.ATTRIBUTE2(i),
ATTRIBUTE3 = l_terr_all_rec.ATTRIBUTE3(i),
ATTRIBUTE4 = l_terr_all_rec.ATTRIBUTE4(i),
ATTRIBUTE5 = l_terr_all_rec.ATTRIBUTE5(i),
ATTRIBUTE6 = l_terr_all_rec.ATTRIBUTE6(i),
ATTRIBUTE7 = l_terr_all_rec.ATTRIBUTE7(i),
ATTRIBUTE8 = l_terr_all_rec.ATTRIBUTE8(i),
ATTRIBUTE9 = l_terr_all_rec.ATTRIBUTE9(i),
ATTRIBUTE10 = l_terr_all_rec.ATTRIBUTE10(i),
ATTRIBUTE11 = l_terr_all_rec.ATTRIBUTE11(i),
ATTRIBUTE12 = l_terr_all_rec.ATTRIBUTE12(i),
ATTRIBUTE13 = l_terr_all_rec.ATTRIBUTE13(i),
ATTRIBUTE14 = l_terr_all_rec.ATTRIBUTE14(i),
ATTRIBUTE15 = l_terr_all_rec.ATTRIBUTE15(i),
ORG_ID = l_terr_all_rec.ORG_ID(i),
NUM_WINNERS = l_terr_all_rec.NUM_WINNERS(i)
where terr_id = l_terr_all_rec.Terr_Id(i);
update JTY_WEBADI_OTH_TERR_INTF
set status = x_return_status
, error_msg = X_Msg_Data
where lay_seq_num = l_terr_all_rec.lay_seq_num(i)
and interface_type = l_intf_type
and header = l_header
and user_sequence = p_user_sequence;
END UPDATE_TERR;
PROCEDURE UPDATE_TERR_QUAL(
P_USER_SEQUENCE IN NUMBER,
-- P_ACTION_FLAG IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR get_qual_csr(
v_user_sequence number,
v_action_flag varchar2,
v_intf_type varchar2,
v_header varchar2) IS
select JTF_TERR_QUAL_s.nextval TERR_QUAL_ID, sub.terr_id, sub.qual_value_id,
sub.qual_value1, sub.qual_value2, sub.qual_value3,
sub.org_id, sub.last_updated_by, sub.last_update_date,
sub.last_update_login, sub.creation_date, sub.created_by,
jq.qual_usg_id, jq.display_type qual_type, jq.CONVERT_TO_ID_FLAG,
jq.qualifier_num, jq.html_lov_sql1,
(case
when jq.COMPARISON_OPERATOR = '=' then '='
when (jq.COMPARISON_OPERATOR LIKE '%LIKE%') AND (instr(sub.qual_VALUE1,'_') > 0) or (instr(sub.qual_VALUE1,'%') > 0 and sub.qual_VALUE2 is null) then 'LIKE'
when (jq.COMPARISON_OPERATOR LIKE '%BETWEEN%') AND (sub.qual_VALUE1 is not null and sub.qual_VALUE2 is not null) then 'BETWEEN'
else '='
end) qual_cond
from JTY_WEBADI_QUAL_HEADER jq,
(
select terr_id, user_sequence,
1 qual_num, QUAL1_VALUE_ID qual_value_id,
QUAL1_VALUE1 qual_VALUE1,
QUAL1_VALUE2 qual_VALUE2,
QUAL1_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id1 is null
and jut.qual1_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
2 qual_num, QUAL2_VALUE_ID qual_value_id,
QUAL2_VALUE1 qual_VALUE1,
QUAL2_VALUE2 qual_VALUE2,
QUAL2_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id2 is null
and jut.qual2_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
3 qual_num, QUAL3_VALUE_ID qual_value_id,
QUAL3_VALUE1 qual_VALUE1,
QUAL3_VALUE2 qual_VALUE2,
QUAL3_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id3 is null
and jut.qual3_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
4 qual_num, QUAL4_VALUE_ID qual_value_id,
QUAL4_VALUE1 qual_VALUE1,
QUAL4_VALUE2 qual_VALUE2,
QUAL4_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id4 is null
and jut.qual4_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
5 qual_num, QUAL5_VALUE_ID qual_value_id,
QUAL5_VALUE1 qual_VALUE1,
QUAL5_VALUE2 qual_VALUE2,
QUAL5_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id5 is null
and jut.qual5_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
6 qual_num, QUAL6_VALUE_ID qual_value_id,
QUAL6_VALUE1 qual_VALUE1,
QUAL6_VALUE2 qual_VALUE2,
QUAL6_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id6 is null
and jut.qual6_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
7 qual_num, QUAL7_VALUE_ID qual_value_id,
QUAL7_VALUE1 qual_VALUE1,
QUAL7_VALUE2 qual_VALUE2,
QUAL7_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id7 is null
and jut.qual7_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
8 qual_num, QUAL8_VALUE_ID qual_value_id,
QUAL8_VALUE1 qual_VALUE1,
QUAL8_VALUE2 qual_VALUE2,
QUAL8_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id8 is null
and jut.qual8_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
9 qual_num, QUAL9_VALUE_ID qual_value_id,
QUAL9_VALUE1 qual_VALUE1,
QUAL9_VALUE2 qual_VALUE2,
QUAL9_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id9 is null
and jut.qual9_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
10 qual_num, QUAL10_VALUE_ID qual_value_id,
QUAL10_VALUE1 qual_VALUE1,
QUAL10_VALUE2 qual_VALUE2,
QUAL10_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id10 is null
and jut.qual10_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
11 qual_num, QUAL11_VALUE_ID qual_value_id,
QUAL11_VALUE1 qual_VALUE1,
QUAL11_VALUE2 qual_VALUE2,
QUAL11_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id11 is null
and jut.qual11_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
12 qual_num, QUAL12_VALUE_ID qual_value_id,
QUAL12_VALUE1 qual_VALUE1,
QUAL12_VALUE2 qual_VALUE2,
QUAL12_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id12 is null
and jut.qual12_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
13 qual_num, QUAL13_VALUE_ID qual_value_id,
QUAL13_VALUE1 qual_VALUE1,
QUAL13_VALUE2 qual_VALUE2,
QUAL13_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id13 is null
and jut.qual13_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
14 qual_num, QUAL14_VALUE_ID qual_value_id,
QUAL14_VALUE1 qual_VALUE1,
QUAL14_VALUE2 qual_VALUE2,
QUAL14_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id14 is null
and jut.qual14_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
15 qual_num, QUAL15_VALUE_ID qual_value_id,
QUAL15_VALUE1 qual_VALUE1,
QUAL15_VALUE2 qual_VALUE2,
QUAL15_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id15 is null
and jut.qual15_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
16 qual_num, QUAL16_VALUE_ID qual_value_id,
QUAL16_VALUE1 qual_VALUE1,
QUAL16_VALUE2 qual_VALUE2,
QUAL16_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id16 is null
and jut.qual16_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
17 qual_num, QUAL17_VALUE_ID qual_value_id,
QUAL17_VALUE1 qual_VALUE1,
QUAL17_VALUE2 qual_VALUE2,
QUAL17_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id17 is null
and jut.qual17_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
18 qual_num, QUAL18_VALUE_ID qual_value_id,
QUAL18_VALUE1 qual_VALUE1,
QUAL18_VALUE2 qual_VALUE2,
QUAL18_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id18 is null
and jut.qual18_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
19 qual_num, QUAL19_VALUE_ID qual_value_id,
QUAL19_VALUE1 qual_VALUE1,
QUAL19_VALUE2 qual_VALUE2,
QUAL19_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id19 is null
and jut.qual19_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
20 qual_num, QUAL20_VALUE_ID qual_value_id,
QUAL20_VALUE1 qual_VALUE1,
QUAL20_VALUE2 qual_VALUE2,
QUAL20_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id20 is null
and jut.qual20_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
21 qual_num, QUAL21_VALUE_ID qual_value_id,
QUAL21_VALUE1 qual_VALUE1,
QUAL21_VALUE2 qual_VALUE2,
QUAL21_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id21 is null
and jut.qual21_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
22 qual_num, QUAL22_VALUE_ID qual_value_id,
QUAL22_VALUE1 qual_VALUE1,
QUAL22_VALUE2 qual_VALUE2,
QUAL22_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id22 is null
and jut.qual22_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
23 qual_num, QUAL23_VALUE_ID qual_value_id,
QUAL23_VALUE1 qual_VALUE1,
QUAL23_VALUE2 qual_VALUE2,
QUAL23_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id23 is null
and jut.qual23_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
24 qual_num, QUAL24_VALUE_ID qual_value_id,
QUAL24_VALUE1 qual_VALUE1,
QUAL24_VALUE2 qual_VALUE2,
QUAL24_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id24 is null
and jut.qual24_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
25 qual_num, QUAL25_VALUE_ID qual_value_id,
QUAL25_VALUE1 qual_valUE1,
QUAL25_VALUE2 qual_valUE2,
QUAL25_VALUE3 qual_valUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id25 is null
and jut.qual25_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header) sub
where jq.user_sequence = sub.user_sequence
and jq.qualifier_num = sub.qual_num;
select sub.TERR_QUAL_ID, sub.terr_id, sub.qual_value_id,
sub.qual_value1, sub.qual_value2, sub.qual_value3,
sub.org_id, sub.last_updated_by, sub.last_update_date,
sub.last_update_login, sub.creation_date, sub.created_by,
jq.qual_usg_id, jq.display_type qual_type, jq.CONVERT_TO_ID_FLAG,
jq.qualifier_num, jq.html_lov_sql1,
(case
when jq.COMPARISON_OPERATOR = '=' then '='
when (jq.COMPARISON_OPERATOR LIKE '%LIKE%') AND (instr(sub.qual_VALUE1,'_') > 0) or (instr(sub.qual_VALUE1,'%') > 0 and sub.qual_VALUE2 is null) then 'LIKE'
when (jq.COMPARISON_OPERATOR LIKE '%BETWEEN%') AND (sub.qual_VALUE1 is not null and sub.qual_VALUE2 is not null) then 'BETWEEN'
else '='
end) qual_cond
from JTY_WEBADI_QUAL_HEADER jq,
(
select terr_id, user_sequence,
terr_qual_id1 terr_qual_id,
1 qual_num, QUAL1_VALUE_ID qual_value_id,
QUAL1_VALUE1 qual_VALUE1,
QUAL1_VALUE2 qual_VALUE2,
QUAL1_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id1 is not null
and jut.QUAL1_VALUE_ID is null
and jut.qual1_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id2 terr_qual_id,
2 qual_num, QUAL2_VALUE_ID qual_value_id,
QUAL2_VALUE1 qual_VALUE1,
QUAL2_VALUE2 qual_VALUE2,
QUAL2_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id2 is not null
and jut.QUAL2_VALUE_ID is null
and jut.qual2_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id3 terr_qual_id,
3 qual_num, QUAL3_VALUE_ID qual_value_id,
QUAL3_VALUE1 qual_VALUE1,
QUAL3_VALUE2 qual_VALUE2,
QUAL3_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id3 is not null
and jut.QUAL3_VALUE_ID is null
and jut.qual3_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id4 terr_qual_id,
4 qual_num, QUAL4_VALUE_ID qual_value_id,
QUAL4_VALUE1 qual_VALUE1,
QUAL4_VALUE2 qual_VALUE2,
QUAL4_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id4 is not null
and jut.QUAL4_VALUE_ID is null
and jut.qual4_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id5 terr_qual_id,
5 qual_num, QUAL5_VALUE_ID qual_value_id,
QUAL5_VALUE1 qual_VALUE1,
QUAL5_VALUE2 qual_VALUE2,
QUAL5_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id5 is not null
and jut.QUAL5_VALUE_ID is null
and jut.qual5_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id6 terr_qual_id,
6 qual_num, QUAL6_VALUE_ID qual_value_id,
QUAL6_VALUE1 qual_VALUE1,
QUAL6_VALUE2 qual_VALUE2,
QUAL6_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id6 is not null
and jut.QUAL6_VALUE_ID is null
and jut.qual6_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id7 terr_qual_id,
7 qual_num, QUAL7_VALUE_ID qual_value_id,
QUAL7_VALUE1 qual_VALUE1,
QUAL7_VALUE2 qual_VALUE2,
QUAL7_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id7 is not null
and jut.QUAL7_VALUE_ID is null
and jut.qual7_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id8 terr_qual_id,
8 qual_num, QUAL8_VALUE_ID qual_value_id,
QUAL8_VALUE1 qual_VALUE1,
QUAL8_VALUE2 qual_VALUE2,
QUAL8_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id8 is not null
and jut.QUAL8_VALUE_ID is null
and jut.qual8_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id9 terr_qual_id,
9 qual_num, QUAL9_VALUE_ID qual_value_id,
QUAL9_VALUE1 qual_VALUE1,
QUAL9_VALUE2 qual_VALUE2,
QUAL9_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id9 is not null
and jut.QUAL9_VALUE_ID is null
and jut.qual9_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id10 terr_qual_id,
10 qual_num, QUAL10_VALUE_ID qual_value_id,
QUAL10_VALUE1 qual_VALUE1,
QUAL10_VALUE2 qual_VALUE2,
QUAL10_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id10 is not null
and jut.QUAL10_VALUE_ID is null
and jut.qual10_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id11 terr_qual_id,
11 qual_num, QUAL11_VALUE_ID qual_value_id,
QUAL11_VALUE1 qual_VALUE1,
QUAL11_VALUE2 qual_VALUE2,
QUAL11_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id11 is not null
and jut.QUAL11_VALUE_ID is null
and jut.qual11_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id12 terr_qual_id,
12 qual_num, QUAL12_VALUE_ID qual_value_id,
QUAL12_VALUE1 qual_VALUE1,
QUAL12_VALUE2 qual_VALUE2,
QUAL12_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id12 is not null
and jut.QUAL12_VALUE_ID is null
and jut.qual12_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id13 terr_qual_id,
13 qual_num, QUAL13_VALUE_ID qual_value_id,
QUAL13_VALUE1 qual_VALUE1,
QUAL13_VALUE2 qual_VALUE2,
QUAL13_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id13 is not null
and jut.QUAL13_VALUE_ID is null
and jut.qual13_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id14 terr_qual_id,
14 qual_num, QUAL14_VALUE_ID qual_value_id,
QUAL14_VALUE1 qual_VALUE1,
QUAL14_VALUE2 qual_VALUE2,
QUAL14_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id14 is not null
and jut.QUAL14_VALUE_ID is null
and jut.qual14_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id15 terr_qual_id,
15 qual_num, QUAL15_VALUE_ID qual_value_id,
QUAL15_VALUE1 qual_VALUE1,
QUAL15_VALUE2 qual_VALUE2,
QUAL15_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id15 is not null
and jut.QUAL15_VALUE_ID is null
and jut.qual15_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id16 terr_qual_id,
16 qual_num, QUAL16_VALUE_ID qual_value_id,
QUAL16_VALUE1 qual_VALUE1,
QUAL16_VALUE2 qual_VALUE2,
QUAL16_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id16 is not null
and jut.QUAL16_VALUE_ID is null
and jut.qual16_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id17 terr_qual_id,
17 qual_num, QUAL17_VALUE_ID qual_value_id,
QUAL17_VALUE1 qual_VALUE1,
QUAL17_VALUE2 qual_VALUE2,
QUAL17_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id17 is not null
and jut.QUAL17_VALUE_ID is null
and jut.qual17_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id18 terr_qual_id,
18 qual_num, QUAL18_VALUE_ID qual_value_id,
QUAL18_VALUE1 qual_VALUE1,
QUAL18_VALUE2 qual_VALUE2,
QUAL18_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id18 is not null
and jut.QUAL18_VALUE_ID is null
and jut.qual18_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id19 terr_qual_id,
19 qual_num, QUAL19_VALUE_ID qual_value_id,
QUAL19_VALUE1 qual_VALUE1,
QUAL19_VALUE2 qual_VALUE2,
QUAL19_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id19 is not null
and jut.QUAL19_VALUE_ID is null
and jut.qual19_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id20 terr_qual_id,
20 qual_num, QUAL20_VALUE_ID qual_value_id,
QUAL20_VALUE1 qual_VALUE1,
QUAL20_VALUE2 qual_VALUE2,
QUAL20_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id20 is not null
and jut.QUAL20_VALUE_ID is null
and jut.qual20_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id21 terr_qual_id,
21 qual_num, QUAL21_VALUE_ID qual_value_id,
QUAL21_VALUE1 qual_VALUE1,
QUAL21_VALUE2 qual_VALUE2,
QUAL21_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id21 is not null
and jut.QUAL21_VALUE_ID is null
and jut.qual21_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id22 terr_qual_id,
22 qual_num, QUAL22_VALUE_ID qual_value_id,
QUAL22_VALUE1 qual_VALUE1,
QUAL22_VALUE2 qual_VALUE2,
QUAL22_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id22 is not null
and jut.QUAL22_VALUE_ID is null
and jut.qual22_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id23 terr_qual_id,
23 qual_num, QUAL23_VALUE_ID qual_value_id,
QUAL23_VALUE1 qual_VALUE1,
QUAL23_VALUE2 qual_VALUE2,
QUAL23_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id23 is not null
and jut.QUAL23_VALUE_ID is null
and jut.qual23_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id24 terr_qual_id,
24 qual_num, QUAL24_VALUE_ID qual_value_id,
QUAL24_VALUE1 qual_VALUE1,
QUAL24_VALUE2 qual_VALUE2,
QUAL24_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id24 is not null
and jut.QUAL24_VALUE_ID is null
and jut.qual24_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id25 terr_qual_id,
25 qual_num, QUAL25_VALUE_ID qual_value_id,
QUAL25_VALUE1 qual_valUE1,
QUAL25_VALUE2 qual_valUE2,
QUAL25_VALUE3 qual_valUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id25 is not null
and jut.QUAL25_VALUE_ID is null
and jut.qual25_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
and jut.header = v_header) sub
where jq.user_sequence = sub.user_sequence
and jq.qualifier_num = sub.qual_num;
select sub.TERR_QUAL_ID, sub.terr_id, sub.qual_value_id,
sub.qual_value1, sub.qual_value2, sub.qual_value3,
sub.org_id, sub.last_updated_by, sub.last_update_date,
sub.last_update_login, sub.creation_date, sub.created_by,
jq.qual_usg_id, jq.display_type qual_type, jq.CONVERT_TO_ID_FLAG,
jq.qualifier_num, jq.html_lov_sql1,
(case
when jq.COMPARISON_OPERATOR = '=' then '='
when (jq.COMPARISON_OPERATOR LIKE '%LIKE%') AND (instr(sub.qual_VALUE1,'_') > 0) or (instr(sub.qual_VALUE1,'%') > 0 and sub.qual_VALUE2 is null) then 'LIKE'
when (jq.COMPARISON_OPERATOR LIKE '%BETWEEN%') AND (sub.qual_VALUE1 is not null and sub.qual_VALUE2 is not null) then 'BETWEEN'
else '='
end) qual_cond
from JTY_WEBADI_QUAL_HEADER jq,
(
select terr_id, user_sequence,
terr_qual_id1 terr_qual_id,
1 qual_num, QUAL1_VALUE_ID qual_value_id,
QUAL1_VALUE1 qual_VALUE1,
QUAL1_VALUE2 qual_VALUE2,
QUAL1_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id1 is not null
and jut.QUAL1_VALUE_ID is not null
and jut.qual1_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id2 terr_qual_id,
2 qual_num, QUAL2_VALUE_ID qual_value_id,
QUAL2_VALUE1 qual_VALUE1,
QUAL2_VALUE2 qual_VALUE2,
QUAL2_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id2 is not null
and jut.QUAL2_VALUE_ID is not null
and jut.qual2_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id3 terr_qual_id,
3 qual_num, QUAL3_VALUE_ID qual_value_id,
QUAL3_VALUE1 qual_VALUE1,
QUAL3_VALUE2 qual_VALUE2,
QUAL3_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id3 is not null
and jut.QUAL3_VALUE_ID is not null
and jut.qual3_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id4 terr_qual_id,
4 qual_num, QUAL4_VALUE_ID qual_value_id,
QUAL4_VALUE1 qual_VALUE1,
QUAL4_VALUE2 qual_VALUE2,
QUAL4_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id4 is not null
and jut.QUAL4_VALUE_ID is not null
and jut.qual4_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id5 terr_qual_id,
5 qual_num, QUAL5_VALUE_ID qual_value_id,
QUAL5_VALUE1 qual_VALUE1,
QUAL5_VALUE2 qual_VALUE2,
QUAL5_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id5 is not null
and jut.QUAL5_VALUE_ID is not null
and jut.qual5_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id6 terr_qual_id,
6 qual_num, QUAL6_VALUE_ID qual_value_id,
QUAL6_VALUE1 qual_VALUE1,
QUAL6_VALUE2 qual_VALUE2,
QUAL6_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id6 is not null
and jut.QUAL6_VALUE_ID is not null
and jut.qual6_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id7 terr_qual_id,
7 qual_num, QUAL7_VALUE_ID qual_value_id,
QUAL7_VALUE1 qual_VALUE1,
QUAL7_VALUE2 qual_VALUE2,
QUAL7_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id7 is not null
and jut.QUAL7_VALUE_ID is not null
and jut.qual7_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id8 terr_qual_id,
8 qual_num, QUAL8_VALUE_ID qual_value_id,
QUAL8_VALUE1 qual_VALUE1,
QUAL8_VALUE2 qual_VALUE2,
QUAL8_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id8 is not null
and jut.QUAL8_VALUE_ID is not null
and jut.qual8_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id9 terr_qual_id,
9 qual_num, QUAL9_VALUE_ID qual_value_id,
QUAL9_VALUE1 qual_VALUE1,
QUAL9_VALUE2 qual_VALUE2,
QUAL9_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id9 is not null
and jut.QUAL9_VALUE_ID is not null
and jut.qual9_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id10 terr_qual_id,
10 qual_num, QUAL10_VALUE_ID qual_value_id,
QUAL10_VALUE1 qual_VALUE1,
QUAL10_VALUE2 qual_VALUE2,
QUAL10_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id10 is not null
and jut.QUAL10_VALUE_ID is not null
and jut.qual10_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id11 terr_qual_id,
11 qual_num, QUAL11_VALUE_ID qual_value_id,
QUAL11_VALUE1 qual_VALUE1,
QUAL11_VALUE2 qual_VALUE2,
QUAL11_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id11 is not null
and jut.QUAL11_VALUE_ID is not null
and jut.qual11_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id12 terr_qual_id,
12 qual_num, QUAL12_VALUE_ID qual_value_id,
QUAL12_VALUE1 qual_VALUE1,
QUAL12_VALUE2 qual_VALUE2,
QUAL12_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id12 is not null
and jut.QUAL12_VALUE_ID is not null
and jut.qual12_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id13 terr_qual_id,
13 qual_num, QUAL13_VALUE_ID qual_value_id,
QUAL13_VALUE1 qual_VALUE1,
QUAL13_VALUE2 qual_VALUE2,
QUAL13_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id13 is not null
and jut.QUAL13_VALUE_ID is not null
and jut.qual13_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id14 terr_qual_id,
14 qual_num, QUAL14_VALUE_ID qual_value_id,
QUAL14_VALUE1 qual_VALUE1,
QUAL14_VALUE2 qual_VALUE2,
QUAL14_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id14 is not null
and jut.QUAL14_VALUE_ID is not null
and jut.qual14_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id15 terr_qual_id,
15 qual_num, QUAL15_VALUE_ID qual_value_id,
QUAL15_VALUE1 qual_VALUE1,
QUAL15_VALUE2 qual_VALUE2,
QUAL15_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id15 is not null
and jut.QUAL15_VALUE_ID is not null
and jut.qual15_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id16 terr_qual_id,
16 qual_num, QUAL16_VALUE_ID qual_value_id,
QUAL16_VALUE1 qual_VALUE1,
QUAL16_VALUE2 qual_VALUE2,
QUAL16_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id16 is not null
and jut.QUAL16_VALUE_ID is not null
and jut.qual16_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id17 terr_qual_id,
17 qual_num, QUAL17_VALUE_ID qual_value_id,
QUAL17_VALUE1 qual_VALUE1,
QUAL17_VALUE2 qual_VALUE2,
QUAL17_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id17 is not null
and jut.QUAL17_VALUE_ID is not null
and jut.qual17_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id18 terr_qual_id,
18 qual_num, QUAL18_VALUE_ID qual_value_id,
QUAL18_VALUE1 qual_VALUE1,
QUAL18_VALUE2 qual_VALUE2,
QUAL18_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id18 is not null
and jut.QUAL18_VALUE_ID is not null
and jut.qual18_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id19 terr_qual_id,
19 qual_num, QUAL19_VALUE_ID qual_value_id,
QUAL19_VALUE1 qual_VALUE1,
QUAL19_VALUE2 qual_VALUE2,
QUAL19_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id19 is not null
and jut.QUAL19_VALUE_ID is not null
and jut.qual19_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id20 terr_qual_id,
20 qual_num, QUAL20_VALUE_ID qual_value_id,
QUAL20_VALUE1 qual_VALUE1,
QUAL20_VALUE2 qual_VALUE2,
QUAL20_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id20 is not null
and jut.QUAL20_VALUE_ID is not null
and jut.qual20_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id21 terr_qual_id,
21 qual_num, QUAL21_VALUE_ID qual_value_id,
QUAL21_VALUE1 qual_VALUE1,
QUAL21_VALUE2 qual_VALUE2,
QUAL21_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id21 is not null
and jut.QUAL21_VALUE_ID is not null
and jut.qual21_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id22 terr_qual_id,
22 qual_num, QUAL22_VALUE_ID qual_value_id,
QUAL22_VALUE1 qual_VALUE1,
QUAL22_VALUE2 qual_VALUE2,
QUAL22_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id22 is not null
and jut.QUAL22_VALUE_ID is not null
and jut.qual22_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id23 terr_qual_id,
23 qual_num, QUAL23_VALUE_ID qual_value_id,
QUAL23_VALUE1 qual_VALUE1,
QUAL23_VALUE2 qual_VALUE2,
QUAL23_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id23 is not null
and jut.QUAL23_VALUE_ID is not null
and jut.qual23_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id24 terr_qual_id,
24 qual_num, QUAL24_VALUE_ID qual_value_id,
QUAL24_VALUE1 qual_VALUE1,
QUAL24_VALUE2 qual_VALUE2,
QUAL24_VALUE3 qual_VALUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id24 is not null
and jut.QUAL24_VALUE_ID is not null
and jut.qual24_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id25 terr_qual_id,
25 qual_num, QUAL25_VALUE_ID qual_value_id,
QUAL25_VALUE1 qual_valUE1,
QUAL25_VALUE2 qual_valUE2,
QUAL25_VALUE3 qual_valUE3, ORG_ID,
LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
CREATED_BY, CREATION_DATE
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id25 is not null
and jut.QUAL25_VALUE_ID is not null
and jut.qual25_value1 is not null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header) sub
where jq.user_sequence = sub.user_sequence
and jq.qualifier_num = sub.qual_num;
select sub.TERR_QUAL_ID, sub.terr_id, sub.qual_value_id,
sub.qual_num
from (
select terr_id, user_sequence,
terr_qual_id1 terr_qual_id,
1 qual_num, QUAL1_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id1 is not null
and jut.QUAL1_VALUE_ID is not null
and jut.qual1_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id2 terr_qual_id,
2 qual_num, QUAL2_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id2 is not null
and jut.QUAL2_VALUE_ID is not null
and jut.qual2_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id3 terr_qual_id,
3 qual_num, QUAL3_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id3 is not null
and jut.QUAL3_VALUE_ID is not null
and jut.qual3_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id4 terr_qual_id,
4 qual_num, QUAL4_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id4 is not null
and jut.QUAL4_VALUE_ID is not null
and jut.qual4_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id5 terr_qual_id,
5 qual_num, QUAL5_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id5 is not null
and jut.QUAL5_VALUE_ID is not null
and jut.qual5_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id6 terr_qual_id,
6 qual_num, QUAL6_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id6 is not null
and jut.QUAL6_VALUE_ID is not null
and jut.qual6_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id7 terr_qual_id,
7 qual_num, QUAL7_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id7 is not null
and jut.QUAL7_VALUE_ID is not null
and jut.qual7_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id8 terr_qual_id,
8 qual_num, QUAL8_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id8 is not null
and jut.QUAL8_VALUE_ID is not null
and jut.qual8_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id9 terr_qual_id,
9 qual_num, QUAL9_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id9 is not null
and jut.QUAL9_VALUE_ID is not null
and jut.qual9_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id10 terr_qual_id,
10 qual_num, QUAL10_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id10 is not null
and jut.QUAL10_VALUE_ID is not null
and jut.qual10_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id11 terr_qual_id,
11 qual_num, QUAL11_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id11 is not null
and jut.QUAL11_VALUE_ID is not null
and jut.qual11_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id12 terr_qual_id,
12 qual_num, QUAL12_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id12 is not null
and jut.QUAL12_VALUE_ID is not null
and jut.qual12_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id13 terr_qual_id,
13 qual_num, QUAL13_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id13 is not null
and jut.QUAL13_VALUE_ID is not null
and jut.qual13_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id14 terr_qual_id,
14 qual_num, QUAL14_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id14 is not null
and jut.QUAL14_VALUE_ID is not null
and jut.qual14_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id15 terr_qual_id,
15 qual_num, QUAL15_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id15 is not null
and jut.QUAL15_VALUE_ID is not null
and jut.qual15_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id16 terr_qual_id,
16 qual_num, QUAL16_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id16 is not null
and jut.QUAL16_VALUE_ID is not null
and jut.qual16_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id17 terr_qual_id,
17 qual_num, QUAL17_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id17 is not null
and jut.QUAL17_VALUE_ID is not null
and jut.qual17_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id18 terr_qual_id,
18 qual_num, QUAL18_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id18 is not null
and jut.QUAL18_VALUE_ID is not null
and jut.qual18_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id19 terr_qual_id,
19 qual_num, QUAL19_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id19 is not null
and jut.QUAL19_VALUE_ID is not null
and jut.qual19_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id20 terr_qual_id,
20 qual_num, QUAL20_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id20 is not null
and jut.QUAL20_VALUE_ID is not null
and jut.qual20_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id21 terr_qual_id,
21 qual_num, QUAL21_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id21 is not null
and jut.QUAL21_VALUE_ID is not null
and jut.qual21_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id22 terr_qual_id,
22 qual_num, QUAL22_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id22 is not null
and jut.QUAL22_VALUE_ID is not null
and jut.qual22_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id23 terr_qual_id,
23 qual_num, QUAL23_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id23 is not null
and jut.QUAL23_VALUE_ID is not null
and jut.qual23_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id24 terr_qual_id,
24 qual_num, QUAL24_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id24 is not null
and jut.QUAL24_VALUE_ID is not null
and jut.qual24_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header
union all
select terr_id, user_sequence,
terr_qual_id25 terr_qual_id,
25 qual_num, QUAL25_VALUE_ID qual_value_id
FROM JTY_WEBADI_OTH_TERR_INTF jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.status is null
and jut.terr_qual_id25 is not null
and jut.QUAL25_VALUE_ID is not null
and jut.qual25_value1 is null
and jut.INTERFACE_TYPE = v_intf_type
AND jut.ACTION_FLAG = v_action_flag
and jut.header = v_header) sub;
l_Terr_Qual_Rec.org_id, l_Terr_Qual_Rec.last_updated_by,
l_Terr_Qual_Rec.last_update_date, l_Terr_Qual_Rec.last_update_login,
l_Terr_Qual_Rec.creation_date, l_Terr_Qual_Rec.created_by,
l_Terr_Qual_Rec.qual_usg_id, l_Terr_Qual_Rec.qual_type,
l_Terr_Qual_Rec.CONVERT_TO_ID_FLAG, l_Terr_Qual_Rec.qualifier_num,
l_Terr_Qual_Rec.html_lov_sql1, l_Terr_Qual_Rec.qual_cond;
INSERT_TERR_QUAL (
p_Terr_Qual_Rec => l_Terr_Qual_Rec,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
INSERT_TERR_VALUES(
p_Terr_Qual_Rec => l_terr_qual_rec,
p_terr_values_out_rec => l_terr_values_out_rec,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
set
jwot.terr_qual_id1 = decode(l_Terr_Qual_Rec.qualifier_num(i),1,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id1),
jwot.terr_qual_id2 = decode(l_Terr_Qual_Rec.qualifier_num(i),2,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id2),
jwot.terr_qual_id3 = decode(l_Terr_Qual_Rec.qualifier_num(i),3,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id3),
jwot.terr_qual_id4 = decode(l_Terr_Qual_Rec.qualifier_num(i),4,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id4),
jwot.terr_qual_id5 = decode(l_Terr_Qual_Rec.qualifier_num(i),5,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id5),
jwot.terr_qual_id6 = decode(l_Terr_Qual_Rec.qualifier_num(i),6,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id6),
jwot.terr_qual_id7 = decode(l_Terr_Qual_Rec.qualifier_num(i),7,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id7),
jwot.terr_qual_id8 = decode(l_Terr_Qual_Rec.qualifier_num(i),8,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id8),
jwot.terr_qual_id9 = decode(l_Terr_Qual_Rec.qualifier_num(i),9,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id9),
jwot.terr_qual_id10 = decode(l_Terr_Qual_Rec.qualifier_num(i),10,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id10),
jwot.terr_qual_id11 = decode(l_Terr_Qual_Rec.qualifier_num(i),11,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id11),
jwot.terr_qual_id12 = decode(l_Terr_Qual_Rec.qualifier_num(i),12,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id12),
jwot.terr_qual_id13 = decode(l_Terr_Qual_Rec.qualifier_num(i),13,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id13),
jwot.terr_qual_id14 = decode(l_Terr_Qual_Rec.qualifier_num(i),14,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id14),
jwot.terr_qual_id15 = decode(l_Terr_Qual_Rec.qualifier_num(i),15,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id15),
jwot.terr_qual_id16 = decode(l_Terr_Qual_Rec.qualifier_num(i),16,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id16),
jwot.terr_qual_id17 = decode(l_Terr_Qual_Rec.qualifier_num(i),17,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id17),
jwot.terr_qual_id18 = decode(l_Terr_Qual_Rec.qualifier_num(i),18,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id18),
jwot.terr_qual_id19 = decode(l_Terr_Qual_Rec.qualifier_num(i),19,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id19),
jwot.terr_qual_id20 = decode(l_Terr_Qual_Rec.qualifier_num(i),20,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id20),
jwot.terr_qual_id21 = decode(l_Terr_Qual_Rec.qualifier_num(i),21,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id21),
jwot.terr_qual_id22 = decode(l_Terr_Qual_Rec.qualifier_num(i),22,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id22),
jwot.terr_qual_id23 = decode(l_Terr_Qual_Rec.qualifier_num(i),23,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id23),
jwot.terr_qual_id24 = decode(l_Terr_Qual_Rec.qualifier_num(i),24,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id24),
jwot.terr_qual_id25 = decode(l_Terr_Qual_Rec.qualifier_num(i),25,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id25)
where TERR_ID = l_Terr_Qual_Rec.TERR_ID(i)
and user_sequence = p_user_sequence
and header = l_header
and interface_type = l_intf_type;
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
SET STATUS = x_return_status,
ERROR_MSG = x_msg_data
WHERE l_Terr_Qual_Rec.TERR_QUAL_ID(i) in
( jwot.TERR_QUAL_ID1, jwot.TERR_QUAL_ID2, jwot.TERR_QUAL_ID3,
jwot.TERR_QUAL_ID4, jwot.TERR_QUAL_ID5, jwot.TERR_QUAL_ID6,
jwot.TERR_QUAL_ID7, jwot.TERR_QUAL_ID8, jwot.TERR_QUAL_ID9,
jwot.TERR_QUAL_ID10, jwot.TERR_QUAL_ID11, jwot.TERR_QUAL_ID12,
jwot.TERR_QUAL_ID13, jwot.TERR_QUAL_ID14, jwot.TERR_QUAL_ID15,
jwot.TERR_QUAL_ID16, jwot.TERR_QUAL_ID17, jwot.TERR_QUAL_ID18,
jwot.TERR_QUAL_ID19, jwot.TERR_QUAL_ID20, jwot.TERR_QUAL_ID21,
jwot.TERR_QUAL_ID22, jwot.TERR_QUAL_ID23,
jwot.TERR_QUAL_ID24, jwot.TERR_QUAL_ID25)
AND interface_type = l_intf_type
and header = l_header
and user_sequence = p_user_sequence;
l_Terr_Qual_Rec.org_id, l_Terr_Qual_Rec.last_updated_by,
l_Terr_Qual_Rec.last_update_date, l_Terr_Qual_Rec.last_update_login,
l_Terr_Qual_Rec.creation_date, l_Terr_Qual_Rec.created_by,
l_Terr_Qual_Rec.qual_usg_id, l_Terr_Qual_Rec.qual_type,
l_Terr_Qual_Rec.CONVERT_TO_ID_FLAG, l_Terr_Qual_Rec.qualifier_num,
l_Terr_Qual_Rec.html_lov_sql1, l_Terr_Qual_Rec.qual_cond;
INSERT_TERR_QUAL (
p_Terr_Qual_Rec => l_Terr_Qual_Rec,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
INSERT_TERR_VALUES(
p_Terr_Qual_Rec => l_terr_qual_rec,
p_terr_values_out_rec => l_terr_values_out_rec,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
set
jwot.terr_qual_id1 = decode(l_Terr_Qual_Rec.qualifier_num(i),1,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id1),
jwot.terr_qual_id2 = decode(l_Terr_Qual_Rec.qualifier_num(i),2,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id2),
jwot.terr_qual_id3 = decode(l_Terr_Qual_Rec.qualifier_num(i),3,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id3),
jwot.terr_qual_id4 = decode(l_Terr_Qual_Rec.qualifier_num(i),4,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id4),
jwot.terr_qual_id5 = decode(l_Terr_Qual_Rec.qualifier_num(i),5,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id5),
jwot.terr_qual_id6 = decode(l_Terr_Qual_Rec.qualifier_num(i),6,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id6),
jwot.terr_qual_id7 = decode(l_Terr_Qual_Rec.qualifier_num(i),7,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id7),
jwot.terr_qual_id8 = decode(l_Terr_Qual_Rec.qualifier_num(i),8,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id8),
jwot.terr_qual_id9 = decode(l_Terr_Qual_Rec.qualifier_num(i),9,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id9),
jwot.terr_qual_id10 = decode(l_Terr_Qual_Rec.qualifier_num(i),10,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id10),
jwot.terr_qual_id11 = decode(l_Terr_Qual_Rec.qualifier_num(i),11,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id11),
jwot.terr_qual_id12 = decode(l_Terr_Qual_Rec.qualifier_num(i),12,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id12),
jwot.terr_qual_id13 = decode(l_Terr_Qual_Rec.qualifier_num(i),13,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id13),
jwot.terr_qual_id14 = decode(l_Terr_Qual_Rec.qualifier_num(i),14,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id14),
jwot.terr_qual_id15 = decode(l_Terr_Qual_Rec.qualifier_num(i),15,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id15),
jwot.terr_qual_id16 = decode(l_Terr_Qual_Rec.qualifier_num(i),16,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id16),
jwot.terr_qual_id17 = decode(l_Terr_Qual_Rec.qualifier_num(i),17,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id17),
jwot.terr_qual_id18 = decode(l_Terr_Qual_Rec.qualifier_num(i),18,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id18),
jwot.terr_qual_id19 = decode(l_Terr_Qual_Rec.qualifier_num(i),19,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id19),
jwot.terr_qual_id20 = decode(l_Terr_Qual_Rec.qualifier_num(i),20,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id20),
jwot.terr_qual_id21 = decode(l_Terr_Qual_Rec.qualifier_num(i),21,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id21),
jwot.terr_qual_id22 = decode(l_Terr_Qual_Rec.qualifier_num(i),22,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id22),
jwot.terr_qual_id23 = decode(l_Terr_Qual_Rec.qualifier_num(i),23,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id23),
jwot.terr_qual_id24 = decode(l_Terr_Qual_Rec.qualifier_num(i),24,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id24),
jwot.terr_qual_id25 = decode(l_Terr_Qual_Rec.qualifier_num(i),25,l_Terr_Qual_Rec.TERR_QUAL_ID(i),jwot.terr_qual_id25)
where TERR_ID = l_Terr_Qual_Rec.TERR_ID(i)
and user_sequence = p_user_sequence
and header = l_header
and interface_type = l_intf_type;
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
SET STATUS = x_return_status,
ERROR_MSG = x_msg_data
WHERE l_Terr_Qual_Rec.TERR_QUAL_ID(i) in
( jwot.TERR_QUAL_ID1, jwot.TERR_QUAL_ID2, jwot.TERR_QUAL_ID3,
jwot.TERR_QUAL_ID4, jwot.TERR_QUAL_ID5, jwot.TERR_QUAL_ID6,
jwot.TERR_QUAL_ID7, jwot.TERR_QUAL_ID8, jwot.TERR_QUAL_ID9,
jwot.TERR_QUAL_ID10, jwot.TERR_QUAL_ID11, jwot.TERR_QUAL_ID12,
jwot.TERR_QUAL_ID13, jwot.TERR_QUAL_ID14, jwot.TERR_QUAL_ID15,
jwot.TERR_QUAL_ID16, jwot.TERR_QUAL_ID17, jwot.TERR_QUAL_ID18,
jwot.TERR_QUAL_ID19, jwot.TERR_QUAL_ID20, jwot.TERR_QUAL_ID21,
jwot.TERR_QUAL_ID22, jwot.TERR_QUAL_ID23,
jwot.TERR_QUAL_ID24, jwot.TERR_QUAL_ID25)
AND interface_type = l_intf_type
and header = l_header
and user_sequence = p_user_sequence;
l_Terr_Qual_Rec.org_id, l_Terr_Qual_Rec.last_updated_by,
l_Terr_Qual_Rec.last_update_date, l_Terr_Qual_Rec.last_update_login,
l_Terr_Qual_Rec.creation_date, l_Terr_Qual_Rec.created_by,
l_Terr_Qual_Rec.qual_usg_id, l_Terr_Qual_Rec.qual_type,
l_Terr_Qual_Rec.CONVERT_TO_ID_FLAG, l_Terr_Qual_Rec.qualifier_num,
l_Terr_Qual_Rec.html_lov_sql1, l_Terr_Qual_Rec.qual_cond;
INSERT_TERR_VALUES(
p_Terr_Qual_Rec => l_terr_qual_rec,
p_terr_values_out_rec => l_terr_values_out_rec,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
SET STATUS = x_return_status,
ERROR_MSG = x_msg_data
WHERE l_Terr_Qual_Rec.TERR_QUAL_ID(i) in
( jwot.TERR_QUAL_ID1, jwot.TERR_QUAL_ID2, jwot.TERR_QUAL_ID3,
jwot.TERR_QUAL_ID4, jwot.TERR_QUAL_ID5, jwot.TERR_QUAL_ID6,
jwot.TERR_QUAL_ID7, jwot.TERR_QUAL_ID8, jwot.TERR_QUAL_ID9,
jwot.TERR_QUAL_ID10, jwot.TERR_QUAL_ID11, jwot.TERR_QUAL_ID12,
jwot.TERR_QUAL_ID13, jwot.TERR_QUAL_ID14, jwot.TERR_QUAL_ID15,
jwot.TERR_QUAL_ID16, jwot.TERR_QUAL_ID17, jwot.TERR_QUAL_ID18,
jwot.TERR_QUAL_ID19, jwot.TERR_QUAL_ID20, jwot.TERR_QUAL_ID21,
jwot.TERR_QUAL_ID22, jwot.TERR_QUAL_ID23,
jwot.TERR_QUAL_ID24, jwot.TERR_QUAL_ID25)
AND interface_type = l_intf_type
and header = l_header
and user_sequence = p_user_sequence;
l_Terr_Qual_Rec.org_id, l_Terr_Qual_Rec.last_updated_by,
l_Terr_Qual_Rec.last_update_date, l_Terr_Qual_Rec.last_update_login,
l_Terr_Qual_Rec.creation_date, l_Terr_Qual_Rec.created_by,
l_Terr_Qual_Rec.qual_usg_id, l_Terr_Qual_Rec.qual_type,
l_Terr_Qual_Rec.CONVERT_TO_ID_FLAG, l_Terr_Qual_Rec.qualifier_num,
l_Terr_Qual_Rec.html_lov_sql1, l_Terr_Qual_Rec.qual_cond;
l_Terr_Values_Rec.LAST_UPDATE_DATE(i) := l_Terr_Qual_Rec.LAST_UPDATE_DATE(i);
l_Terr_Values_Rec.LAST_UPDATED_BY(i) := l_Terr_Qual_Rec.LAST_UPDATED_BY(i);
l_Terr_Values_Rec.LAST_UPDATE_LOGIN(i) := l_Terr_Qual_Rec.LAST_UPDATE_LOGIN(i);
Update JTF_TERR_VALUES_ALL
SET
LAST_UPDATED_BY = l_Terr_Values_Rec.LAST_UPDATED_BY(i),
LAST_UPDATE_DATE = l_Terr_Values_Rec.LAST_UPDATE_DATE(i),
LAST_UPDATE_LOGIN = l_Terr_Values_Rec.LAST_UPDATE_LOGIN(i),
TERR_QUAL_ID = l_Terr_Values_Rec.TERR_QUAL_ID(i),
COMPARISON_OPERATOR = l_Terr_Values_Rec.COMPARISON_OPERATOR(i),
LOW_VALUE_CHAR = l_Terr_Values_Rec.LOW_VALUE_CHAR(i),
HIGH_VALUE_CHAR = l_Terr_Values_Rec.HIGH_VALUE_CHAR(i),
LOW_VALUE_NUMBER = l_Terr_Values_Rec.LOW_VALUE_NUMBER(i),
HIGH_VALUE_NUMBER = l_Terr_Values_Rec.HIGH_VALUE_NUMBER(i),
INTEREST_TYPE_ID = l_Terr_Values_Rec.INTEREST_TYPE_ID(i),
PRIMARY_INTEREST_CODE_ID = l_Terr_Values_Rec.PRIMARY_INTEREST_CODE_ID(i),
SECONDARY_INTEREST_CODE_ID = l_Terr_Values_Rec.SECONDARY_INTEREST_CODE_ID(i),
CURRENCY_CODE = l_Terr_Values_Rec.CURRENCY_CODE(i),
ID_USED_FLAG = l_Terr_Values_Rec.ID_USED_FLAG(i),
LOW_VALUE_CHAR_ID = l_Terr_Values_Rec.LOW_VALUE_CHAR_ID(i),
ORG_ID = l_Terr_Values_Rec.ORG_ID(i),
VALUE1_ID = l_Terr_Values_Rec.VALUE1_ID(i),
VALUE2_ID = l_Terr_Values_Rec.VALUE2_ID(i),
VALUE3_ID = l_Terr_Values_Rec.VALUE3_ID(i)
where TERR_VALUE_ID = l_Terr_Values_Rec.TERR_VALUE_ID(i);
fnd_message.set_name ('JTF', 'JTY_OTH_TERR_UPDATE_QUAL_VAL');
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
SET STATUS = x_return_status,
error_msg = 'U: ' || x_msg_data
WHERE l_Terr_Qual_Rec.TERR_QUAL_ID(i) in
( jwot.TERR_QUAL_ID1, jwot.TERR_QUAL_ID2, jwot.TERR_QUAL_ID3,
jwot.TERR_QUAL_ID4, jwot.TERR_QUAL_ID5, jwot.TERR_QUAL_ID6,
jwot.TERR_QUAL_ID7, jwot.TERR_QUAL_ID8, jwot.TERR_QUAL_ID9,
jwot.TERR_QUAL_ID10, jwot.TERR_QUAL_ID11, jwot.TERR_QUAL_ID12,
jwot.TERR_QUAL_ID13, jwot.TERR_QUAL_ID14, jwot.TERR_QUAL_ID15,
jwot.TERR_QUAL_ID16, jwot.TERR_QUAL_ID17, jwot.TERR_QUAL_ID18,
jwot.TERR_QUAL_ID19, jwot.TERR_QUAL_ID20, jwot.TERR_QUAL_ID21,
jwot.TERR_QUAL_ID22, jwot.TERR_QUAL_ID23,
jwot.TERR_QUAL_ID24, jwot.TERR_QUAL_ID25)
AND interface_type = l_intf_type
and header = l_header
and action_flag = l_action_flag
and user_sequence = p_user_sequence;
DELETE FROM JTF_TERR_VALUES_ALL
where TERR_VALUE_ID = l_Terr_Qual_Rec.qual_value_id(i);
DELETE FROM JTF_TERR_QUAL_ALL jtq
WHERE not exists
( select 1 from JTF_TERR_VALUES_ALL jtv
where jtv.terr_qual_id = jtq.terr_qual_id)
and jtq.terr_qual_id = l_Terr_Qual_Rec.TERR_QUAL_ID(i);
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
SET STATUS = x_return_status
WHERE l_Terr_Qual_Rec.TERR_QUAL_ID(i) in
( jwot.TERR_QUAL_ID1, jwot.TERR_QUAL_ID2, jwot.TERR_QUAL_ID3,
jwot.TERR_QUAL_ID4, jwot.TERR_QUAL_ID5, jwot.TERR_QUAL_ID6,
jwot.TERR_QUAL_ID7, jwot.TERR_QUAL_ID8, jwot.TERR_QUAL_ID9,
jwot.TERR_QUAL_ID10, jwot.TERR_QUAL_ID11, jwot.TERR_QUAL_ID12,
jwot.TERR_QUAL_ID13, jwot.TERR_QUAL_ID14, jwot.TERR_QUAL_ID15,
jwot.TERR_QUAL_ID16, jwot.TERR_QUAL_ID17, jwot.TERR_QUAL_ID18,
jwot.TERR_QUAL_ID19, jwot.TERR_QUAL_ID20, jwot.TERR_QUAL_ID21,
jwot.TERR_QUAL_ID22, jwot.TERR_QUAL_ID23,
jwot.TERR_QUAL_ID24, jwot.TERR_QUAL_ID25)
AND interface_type = l_intf_type
and header = l_header
and action_flag = l_action_flag
and user_sequence = p_user_sequence;
fnd_message.set_name ('JTF', 'JTY_OTH_TERR_DELETE_QUAL_VAL');
UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
SET STATUS = x_return_status,
error_msg = 'U: ' || x_msg_data
WHERE l_Terr_Qual_Rec.TERR_QUAL_ID(i) in
( jwot.TERR_QUAL_ID1, jwot.TERR_QUAL_ID2, jwot.TERR_QUAL_ID3,
jwot.TERR_QUAL_ID4, jwot.TERR_QUAL_ID5, jwot.TERR_QUAL_ID6,
jwot.TERR_QUAL_ID7, jwot.TERR_QUAL_ID8, jwot.TERR_QUAL_ID9,
jwot.TERR_QUAL_ID10, jwot.TERR_QUAL_ID11, jwot.TERR_QUAL_ID12,
jwot.TERR_QUAL_ID13, jwot.TERR_QUAL_ID14, jwot.TERR_QUAL_ID15,
jwot.TERR_QUAL_ID16, jwot.TERR_QUAL_ID17, jwot.TERR_QUAL_ID18,
jwot.TERR_QUAL_ID19, jwot.TERR_QUAL_ID20, jwot.TERR_QUAL_ID21,
jwot.TERR_QUAL_ID22, jwot.TERR_QUAL_ID23,
jwot.TERR_QUAL_ID24, jwot.TERR_QUAL_ID25)
AND interface_type = l_intf_type
and header = l_header
and action_flag = l_action_flag
and user_sequence = p_user_sequence;
update JTY_WEBADI_OTH_TERR_INTF
set status = x_return_status
where interface_type = l_intf_type
and header = l_header
and status is null
and user_sequence = p_user_sequence;
END UPDATE_TERR_QUAL;
PROCEDURE UPDATE_TERR_RSC(
P_USER_SEQUENCE IN NUMBER,
--p_action_flag IN VARCHAR2,
x_return_status out nocopy varchar2,
x_msg_data out nocopy varchar2
) IS
cursor GET_RSC_CSR(
v_user_sequence number,
v_intf_type varchar2,
v_header varchar2,
v_action_flag varchar2) IS
SELECT jwot.LAY_SEQ_NUM, jwr.TERR_RSC_ID,
jwot.LAST_UPDATE_DATE, jwot.LAST_UPDATED_BY, jwot.CREATION_DATE,
jwot.CREATED_BY, jwot.LAST_UPDATE_LOGIN,
jwot.TERR_ID, jwr.resource_id,
decode(jwr.resource_type,0, 'RS_'||res.category,
--decode(res.category, 'PARTNER', 'RS_PARTNER', 'EMPLOYEE', 'RS_EMPLOYEE',NULL)
1,'RS_GROUP',2,'RS_TEAM',3,'RS_ROLE', null) RESOURCE_TYPE,
jwr.role_code, null PRIMARY_CONTACT_FLAG,
NVL(jwr.RES_START_DATE, jwot.TERR_START_DATE) START_DATE_ACTIVE,
NVL(jwr.RES_END_DATE, jwot.TERR_END_DATE) END_DATE_ACTIVE,
jwot.org_id, 'N' FULL_ACCESS_FLAG,
jwr.group_id, NULL SECURITY_GROUP_ID,
res.source_id person_id, NULL OBJECT_VERSION_NUMBER,
jwr.ATTRIBUTE_CATEGORY, jwr.ATTRIBUTE1, jwr.ATTRIBUTE2,
jwr.ATTRIBUTE3, jwr.ATTRIBUTE4, jwr.ATTRIBUTE5,
jwr.ATTRIBUTE6, jwr.ATTRIBUTE7, jwr.ATTRIBUTE8,
jwr.ATTRIBUTE9, jwr.ATTRIBUTE10, jwr.ATTRIBUTE11,
jwr.ATTRIBUTE12, jwr.ATTRIBUTE13, jwr.ATTRIBUTE14,
jwr.ATTRIBUTE15
from
JTY_WEBADI_OTH_TERR_INTF jwot,
JTY_WEBADI_RESOURCES jwr,
jtf_rs_resource_extns res
where jwr.RESOURCE_ID = res.resource_id(+)
and jwr.user_sequence = jwot.user_sequence
and jwr.interface_type = jwot.interface_type
and jwot.lay_seq_num = jwr.lay_seq_num
and jwot.user_sequence = v_user_sequence
and jwot.interface_type = v_intf_type
and jwot.header = v_header
and jwot.status is null
and jwr.header = jwot.header
and jwot.action_flag = v_action_flag;
select sub.TERR_RSC_ACCESS_ID,
jwot.LAST_UPDATE_DATE, jwot.LAST_UPDATED_BY,
jwot.CREATION_DATE, jwot.CREATED_BY, jwot.LAST_UPDATE_LOGIN,
sub.TERR_RSC_ID, jq.QUAL_TYPE_NAME ACCESS_TYPE,
jwot.ORG_ID, sub.TRANS_ACCESS_CODE
from JTY_WEBADI_QUAL_TYPE_HEADER jq,
JTY_WEBADI_OTH_TERR_INTF jwot,
(
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
1 qual_type_num, TERR_RSC_ACCESS_ID1 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE1 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE1 is not null
and jut.TERR_RSC_ACCESS_ID1 is null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
2 qual_type_num, TERR_RSC_ACCESS_ID2 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE2 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE2 is not null
and jut.TERR_RSC_ACCESS_ID2 is null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
3 qual_type_num, TERR_RSC_ACCESS_ID3 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE3 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE3 is not null
and jut.TERR_RSC_ACCESS_ID3 is null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
4 qual_type_num, TERR_RSC_ACCESS_ID4 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE4 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE4 is not null
and jut.TERR_RSC_ACCESS_ID4 is null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
5 qual_type_num, TERR_RSC_ACCESS_ID5 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE5 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE5 is not null
and jut.TERR_RSC_ACCESS_ID5 is null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID,lay_seq_num, header,
6 qual_type_num, TERR_RSC_ACCESS_ID6 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE6 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE6 is not null
and jut.TERR_RSC_ACCESS_ID6 is null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
7 qual_type_num, TERR_RSC_ACCESS_ID7 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE7 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE7 is not null
and jut.TERR_RSC_ACCESS_ID7 is null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
8 qual_type_num, TERR_RSC_ACCESS_ID8 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE8 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE8 is not null
and jut.TERR_RSC_ACCESS_ID8 is null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
9 qual_type_num, TERR_RSC_ACCESS_ID9 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE9 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE9 is not null
and jut.TERR_RSC_ACCESS_ID9 is null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10 qual_type_num, TERR_RSC_ACCESS_ID10 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE10 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE10 is not null
and jut.TERR_RSC_ACCESS_ID10 is null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type) sub
where sub.user_sequence = jq.user_sequence
and sub.qual_type_num = jq.QUAL_TYPE_NUM
and sub.lay_seq_num = jwot.lay_seq_num
and sub.user_sequence = jwot.user_sequence
and sub.header = jwot.header
and jwot.interface_type = v_intf_type
and jwot.status is null;
select sub.TERR_RSC_ACCESS_ID,
jwot.LAST_UPDATE_DATE, jwot.LAST_UPDATED_BY,
jwot.CREATION_DATE, jwot.CREATED_BY, jwot.LAST_UPDATE_LOGIN,
sub.TERR_RSC_ID, jq.QUAL_TYPE_NAME ACCESS_TYPE,
jwot.ORG_ID, sub.TRANS_ACCESS_CODE
from JTY_WEBADI_QUAL_TYPE_HEADER jq,
JTY_WEBADI_OTH_TERR_INTF jwot,
(
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
1 qual_type_num, TERR_RSC_ACCESS_ID1 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE1 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE1 is not null
and jut.TERR_RSC_ACCESS_ID1 is not null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
2 qual_type_num, TERR_RSC_ACCESS_ID2 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE2 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE2 is not null
and jut.TERR_RSC_ACCESS_ID2 is not null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
3 qual_type_num, TERR_RSC_ACCESS_ID3 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE3 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE3 is not null
and jut.TERR_RSC_ACCESS_ID3 is not null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
4 qual_type_num, TERR_RSC_ACCESS_ID4 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE4 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE4 is not null
and jut.TERR_RSC_ACCESS_ID4 is not null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
5 qual_type_num, TERR_RSC_ACCESS_ID5 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE5 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE5 is not null
and jut.TERR_RSC_ACCESS_ID5 is not null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID,lay_seq_num, header,
6 qual_type_num, TERR_RSC_ACCESS_ID6 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE6 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE6 is not null
and jut.TERR_RSC_ACCESS_ID6 is not null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
7 qual_type_num, TERR_RSC_ACCESS_ID7 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE7 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE7 is not null
and jut.TERR_RSC_ACCESS_ID7 is not null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
8 qual_type_num, TERR_RSC_ACCESS_ID8 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE8 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE8 is not null
and jut.TERR_RSC_ACCESS_ID8 is not null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
9 qual_type_num, TERR_RSC_ACCESS_ID9 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE9 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE9 is not null
and jut.TERR_RSC_ACCESS_ID9 is not null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type
union all
select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10 qual_type_num, TERR_RSC_ACCESS_ID10 TERR_RSC_ACCESS_ID,
TRANS_ACCESS_CODE10 TRANS_ACCESS_CODE
FROM JTY_WEBADI_RESOURCES jut
where jut.USER_SEQUENCE = v_user_sequence
and jut.TRANS_ACCESS_CODE10 is not null
and jut.TERR_RSC_ACCESS_ID10 is not null
and jut.header = v_header
and jut.INTERFACE_TYPE = v_intf_type) sub
where sub.user_sequence = jq.user_sequence
and sub.qual_type_num = jq.QUAL_TYPE_NUM
and sub.lay_seq_num = jwot.lay_seq_num
and sub.user_sequence = jwot.user_sequence
and sub.header = jwot.header
and jwot.interface_type = v_intf_type
and jwot.status is null;
LAST_UPDATE_DATE date_tbl_type,
LAST_UPDATED_BY number_tbl_type,
CREATION_DATE date_tbl_type,
CREATED_BY number_tbl_type,
LAST_UPDATE_LOGIN number_tbl_type,
TERR_ID number_tbl_type,
RESOURCE_ID number_tbl_type,
RESOURCE_TYPE varchar2_tbl_type,
ROLE_CODE varchar2_tbl_type,
PRIMARY_CONTACT_FLAG varchar2_tbl_type,
START_DATE_ACTIVE date_tbl_type,
END_DATE_ACTIVE date_tbl_type,
ORG_ID number_tbl_type,
FULL_ACCESS_FLAG varchar2_tbl_type,
GROUP_ID number_tbl_type,
SECURITY_GROUP_ID number_tbl_type,
PERSON_ID number_tbl_type,
OBJECT_VERSION_NUMBER number_tbl_type,
ATTRIBUTE_CATEGORY varchar2_tbl_type,
ATTRIBUTE1 varchar2_tbl_type,
ATTRIBUTE2 varchar2_tbl_type,
ATTRIBUTE3 varchar2_tbl_type,
ATTRIBUTE4 varchar2_tbl_type,
ATTRIBUTE5 varchar2_tbl_type,
ATTRIBUTE6 varchar2_tbl_type,
ATTRIBUTE7 varchar2_tbl_type,
ATTRIBUTE8 varchar2_tbl_type,
ATTRIBUTE9 varchar2_tbl_type,
ATTRIBUTE10 varchar2_tbl_type,
ATTRIBUTE11 varchar2_tbl_type,
ATTRIBUTE12 varchar2_tbl_type,
ATTRIBUTE13 varchar2_tbl_type,
ATTRIBUTE14 varchar2_tbl_type,
ATTRIBUTE15 varchar2_tbl_type
);
LAST_UPDATE_DATE date_tbl_type,
LAST_UPDATED_BY number_tbl_type,
CREATION_DATE date_tbl_type,
CREATED_BY number_tbl_type,
LAST_UPDATE_LOGIN number_tbl_type,
TERR_RSC_ID number_tbl_type,
ACCESS_TYPE varchar2_tbl_type,
TRANS_ACCESS_CODE varchar2_tbl_type,
ORG_ID number_tbl_type);
l_rsc_rec.TERR_RSC_ID, l_rsc_rec.LAST_UPDATE_DATE, l_rsc_rec.LAST_UPDATED_BY,
l_rsc_rec.CREATION_DATE, l_rsc_rec.CREATED_BY, l_rsc_rec.LAST_UPDATE_LOGIN,
l_rsc_rec.TERR_ID, l_rsc_rec.RESOURCE_ID, l_rsc_rec.RESOURCE_TYPE,
l_rsc_rec.ROLE_CODE, l_rsc_rec.PRIMARY_CONTACT_FLAG, l_rsc_rec.START_DATE_ACTIVE,
l_rsc_rec.END_DATE_ACTIVE, l_rsc_rec.ORG_ID, l_rsc_rec.FULL_ACCESS_FLAG,
l_rsc_rec.GROUP_ID, l_rsc_rec.SECURITY_GROUP_ID, l_rsc_rec.PERSON_ID,
l_rsc_rec.OBJECT_VERSION_NUMBER, l_rsc_rec.ATTRIBUTE_CATEGORY, l_rsc_rec.ATTRIBUTE1,
l_rsc_rec.ATTRIBUTE2, l_rsc_rec.ATTRIBUTE3, l_rsc_rec.ATTRIBUTE4,
l_rsc_rec.ATTRIBUTE5, l_rsc_rec.ATTRIBUTE6, l_rsc_rec.ATTRIBUTE7,
l_rsc_rec.ATTRIBUTE8, l_rsc_rec.ATTRIBUTE9, l_rsc_rec.ATTRIBUTE10,
l_rsc_rec.ATTRIBUTE11, l_rsc_rec.ATTRIBUTE12, l_rsc_rec.ATTRIBUTE13,
l_rsc_rec.ATTRIBUTE14, l_rsc_rec.ATTRIBUTE15;
-- direct insert into the table since the package JTF_TERRITORY_RESOURCE_PVT
-- doesnot support flex field insert
if (l_action_flag = 'C' AND l_rsc_rec.RESOURCE_ID.count > 0) then
--dbms_output.put_line('Create territory resource information, rowcount: ' || l_rsc_rec.RESOURCE_ID.count);
INSERT INTO JTF_TERR_RSC_ALL (
TERR_RSC_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
TERR_ID, RESOURCE_ID, RESOURCE_TYPE,
ROLE, PRIMARY_CONTACT_FLAG, START_DATE_ACTIVE,
END_DATE_ACTIVE, ORG_ID, FULL_ACCESS_FLAG,
GROUP_ID, SECURITY_GROUP_ID, PERSON_ID,
OBJECT_VERSION_NUMBER, ATTRIBUTE_CATEGORY, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15)
VALUES ( JTF_TERR_RSC_s.nextval, l_rsc_rec.LAST_UPDATE_DATE(i),
l_rsc_rec.LAST_UPDATED_BY(i), l_rsc_rec.CREATION_DATE(i),
l_rsc_rec.CREATED_BY(i), l_rsc_rec.LAST_UPDATE_LOGIN(i),
l_rsc_rec.TERR_ID(i), l_rsc_rec.RESOURCE_ID(i), l_rsc_rec.RESOURCE_TYPE(i),
l_rsc_rec.ROLE_CODE(i), l_rsc_rec.PRIMARY_CONTACT_FLAG(i), l_rsc_rec.START_DATE_ACTIVE(i),
l_rsc_rec.END_DATE_ACTIVE(i), l_rsc_rec.ORG_ID(i), l_rsc_rec.FULL_ACCESS_FLAG(i),
l_rsc_rec.GROUP_ID(i), l_rsc_rec.SECURITY_GROUP_ID(i), l_rsc_rec.PERSON_ID(i),
l_rsc_rec.OBJECT_VERSION_NUMBER(i), l_rsc_rec.ATTRIBUTE_CATEGORY(i), l_rsc_rec.ATTRIBUTE1(i),
l_rsc_rec.ATTRIBUTE2(i), l_rsc_rec.ATTRIBUTE3(i), l_rsc_rec.ATTRIBUTE4(i),
l_rsc_rec.ATTRIBUTE5(i), l_rsc_rec.ATTRIBUTE6(i), l_rsc_rec.ATTRIBUTE7(i),
l_rsc_rec.ATTRIBUTE8(i), l_rsc_rec.ATTRIBUTE9(i), l_rsc_rec.ATTRIBUTE10(i),
l_rsc_rec.ATTRIBUTE11(i), l_rsc_rec.ATTRIBUTE12(i), l_rsc_rec.ATTRIBUTE13(i),
l_rsc_rec.ATTRIBUTE14(i), l_rsc_rec.ATTRIBUTE15(i) )
RETURNING terr_rsc_id, resource_id, terr_id
bulk collect into l_get_terr_rsc_rec.terr_rsc_id,
l_get_terr_rsc_rec.resource_id, l_get_terr_rsc_rec.terr_id;
update JTY_WEBADI_RESOURCES
set terr_rsc_id = l_get_terr_rsc_rec.terr_rsc_id(i)
where resource_id = l_get_terr_rsc_rec.resource_id(i)
-- and terr_id = l_get_terr_rsc_rec.terr_id(i)
and interface_type = l_intf_type
and user_sequence = p_user_sequence
and header = l_header
and terr_rsc_id is null;
l_rsc_rec.TERR_RSC_ID, l_rsc_rec.LAST_UPDATE_DATE, l_rsc_rec.LAST_UPDATED_BY,
l_rsc_rec.CREATION_DATE, l_rsc_rec.CREATED_BY, l_rsc_rec.LAST_UPDATE_LOGIN,
l_rsc_rec.TERR_ID, l_rsc_rec.RESOURCE_ID, l_rsc_rec.RESOURCE_TYPE,
l_rsc_rec.ROLE_CODE, l_rsc_rec.PRIMARY_CONTACT_FLAG, l_rsc_rec.START_DATE_ACTIVE,
l_rsc_rec.END_DATE_ACTIVE, l_rsc_rec.ORG_ID, l_rsc_rec.FULL_ACCESS_FLAG,
l_rsc_rec.GROUP_ID, l_rsc_rec.SECURITY_GROUP_ID, l_rsc_rec.PERSON_ID,
l_rsc_rec.OBJECT_VERSION_NUMBER, l_rsc_rec.ATTRIBUTE_CATEGORY, l_rsc_rec.ATTRIBUTE1,
l_rsc_rec.ATTRIBUTE2, l_rsc_rec.ATTRIBUTE3, l_rsc_rec.ATTRIBUTE4,
l_rsc_rec.ATTRIBUTE5, l_rsc_rec.ATTRIBUTE6, l_rsc_rec.ATTRIBUTE7,
l_rsc_rec.ATTRIBUTE8, l_rsc_rec.ATTRIBUTE9, l_rsc_rec.ATTRIBUTE10,
l_rsc_rec.ATTRIBUTE11, l_rsc_rec.ATTRIBUTE12, l_rsc_rec.ATTRIBUTE13,
l_rsc_rec.ATTRIBUTE14, l_rsc_rec.ATTRIBUTE15;
-- direct insert into the table since the package JTF_TERRITORY_RESOURCE_PVT
-- doesnot support flex field insert
if (l_action_flag = 'U' AND l_rsc_rec.TERR_RSC_ID.count > 0) then
--dbms_output.put_line('Update territory resource information, rowcount: ' || l_rsc_rec.TERR_RSC_ID.count);
UPDATE JTF_TERR_RSC_ALL
SET LAST_UPDATE_DATE = l_rsc_rec.LAST_UPDATE_DATE(i),
LAST_UPDATED_BY = l_rsc_rec.LAST_UPDATED_BY(i),
LAST_UPDATE_LOGIN = l_rsc_rec.LAST_UPDATE_LOGIN(i),
TERR_ID = l_rsc_rec.TERR_ID(i),
RESOURCE_ID = l_rsc_rec.RESOURCE_ID(i),
RESOURCE_TYPE = l_rsc_rec.RESOURCE_TYPE(i),
ROLE = l_rsc_rec.ROLE_CODE(i),
START_DATE_ACTIVE = l_rsc_rec.START_DATE_ACTIVE(i),
END_DATE_ACTIVE = l_rsc_rec.END_DATE_ACTIVE(i),
ORG_ID = l_rsc_rec.ORG_ID(i),
GROUP_ID = l_rsc_rec.GROUP_ID(i),
PERSON_ID = l_rsc_rec.PERSON_ID(i),
ATTRIBUTE_CATEGORY = l_rsc_rec.ATTRIBUTE_CATEGORY(i),
ATTRIBUTE1 = l_rsc_rec.ATTRIBUTE1(i),
ATTRIBUTE2 = l_rsc_rec.ATTRIBUTE2(i),
ATTRIBUTE3 = l_rsc_rec.ATTRIBUTE3(i),
ATTRIBUTE4 = l_rsc_rec.ATTRIBUTE4(i),
ATTRIBUTE5 = l_rsc_rec.ATTRIBUTE5(i),
ATTRIBUTE6 = l_rsc_rec.ATTRIBUTE6(i),
ATTRIBUTE7 = l_rsc_rec.ATTRIBUTE7(i),
ATTRIBUTE8 = l_rsc_rec.ATTRIBUTE8(i),
ATTRIBUTE9 = l_rsc_rec.ATTRIBUTE9(i),
ATTRIBUTE10 = l_rsc_rec.ATTRIBUTE10(i),
ATTRIBUTE11 = l_rsc_rec.ATTRIBUTE11(i),
ATTRIBUTE12 = l_rsc_rec.ATTRIBUTE12(i),
ATTRIBUTE13 = l_rsc_rec.ATTRIBUTE13(i),
ATTRIBUTE14 = l_rsc_rec.ATTRIBUTE14(i),
ATTRIBUTE15 = l_rsc_rec.ATTRIBUTE15(i)
WHERE TERR_RSC_ID = l_rsc_rec.TERR_RSC_ID(i);
--dbms_output.put_line(' # records processed for update: '||SQL%ROWCOUNT);
l_rsc_access_rec.LAST_UPDATE_DATE, l_rsc_access_rec.LAST_UPDATED_BY,
l_rsc_access_rec.CREATION_DATE, l_rsc_access_rec.CREATED_BY,
l_rsc_access_rec.LAST_UPDATE_LOGIN,
l_rsc_access_rec.TERR_RSC_ID, l_rsc_access_rec.ACCESS_TYPE,
l_rsc_access_rec.ORG_ID, l_rsc_access_rec.TRANS_ACCESS_CODE;
INSERT INTO JTF_TERR_RSC_ACCESS_ALL(
TERR_RSC_ACCESS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TERR_RSC_ID,
ACCESS_TYPE,
TRANS_ACCESS_CODE,
ORG_ID)
VALUES (
JTF_TERR_RSC_ACCESS_s.nextval,
l_rsc_access_rec.LAST_UPDATE_DATE(i),
l_rsc_access_rec.LAST_UPDATED_BY(i),
l_rsc_access_rec.CREATION_DATE(i),
l_rsc_access_rec.CREATED_BY(i),
l_rsc_access_rec.LAST_UPDATE_LOGIN(i),
l_rsc_access_rec.TERR_RSC_ID(i),
l_rsc_access_rec.ACCESS_TYPE(i),
l_rsc_access_rec.TRANS_ACCESS_CODE(i),
l_rsc_access_rec.ORG_ID(i)
);
update JTY_WEBADI_OTH_TERR_INTF jwot
set status = x_return_status
where exists
(select 1 from JTY_WEBADI_RESOURCES jwr
where jwr.LAY_SEQ_NUM = jwot.LAY_SEQ_NUM
and jwr.header = jwot.header
and jwr.user_sequence = jwot.user_sequence
and jwr.interface_type = jwot.interface_type
and jwr.terr_rsc_id = l_rsc_access_rec.terr_rsc_id(i))
and jwot.USER_SEQUENCE = p_user_sequence
and jwot.header = l_header
and jwot.interface_type = l_intf_type
and jwot.status is null;
update JTY_WEBADI_OTH_TERR_INTF jwot
set status = x_return_status,
error_msg = x_msg_data
where exists
(select 1 from JTY_WEBADI_RESOURCES jwr
where jwr.LAY_SEQ_NUM = jwot.LAY_SEQ_NUM
and jwr.header = jwot.header
and jwr.user_sequence = jwot.user_sequence
and jwr.interface_type = jwot.interface_type
and jwr.terr_rsc_id = l_rsc_access_rec.terr_rsc_id(i))
and jwot.USER_SEQUENCE = p_user_sequence
and jwot.header = l_header
and jwot.interface_type = l_intf_type
and jwot.status is null;
l_rsc_access_rec.LAST_UPDATE_DATE, l_rsc_access_rec.LAST_UPDATED_BY,
l_rsc_access_rec.CREATION_DATE, l_rsc_access_rec.CREATED_BY,
l_rsc_access_rec.LAST_UPDATE_LOGIN,
l_rsc_access_rec.TERR_RSC_ID, l_rsc_access_rec.ACCESS_TYPE,
l_rsc_access_rec.ORG_ID, l_rsc_access_rec.TRANS_ACCESS_CODE;
Update JTF_TERR_RSC_ACCESS_ALL
SET
LAST_UPDATE_DATE = l_rsc_access_rec.LAST_UPDATE_DATE(i),
LAST_UPDATED_BY = l_rsc_access_rec.LAST_UPDATED_BY(i),
LAST_UPDATE_LOGIN = l_rsc_access_rec.LAST_UPDATE_LOGIN(i),
TERR_RSC_ID = l_rsc_access_rec.TERR_RSC_ID(i),
ACCESS_TYPE = l_rsc_access_rec.ACCESS_TYPE(i),
TRANS_ACCESS_CODE = l_rsc_access_rec.TRANS_ACCESS_CODE(i),
ORG_ID = l_rsc_access_rec.ORG_ID(i)
where TERR_RSC_ACCESS_ID = l_rsc_access_rec.TERR_RSC_ACCESS_ID(i);
update JTY_WEBADI_OTH_TERR_INTF jwot
set status = x_return_status
where exists
(select 1 from JTY_WEBADI_RESOURCES jwr
where jwr.LAY_SEQ_NUM = jwot.LAY_SEQ_NUM
and jwr.header = jwot.header
and jwr.user_sequence = jwot.user_sequence
and jwr.interface_type = jwot.interface_type
and jwr.terr_rsc_id = l_rsc_access_rec.terr_rsc_id(i))
and jwot.USER_SEQUENCE = p_user_sequence
and jwot.header = l_header
and jwot.interface_type = l_intf_type
and jwot.status is null;
fnd_message.set_name ('JTF', 'JTY_OTH_TERR_UPDATE_ACCESS');
update JTY_WEBADI_OTH_TERR_INTF jwot
set status = x_return_status,
error_msg = x_msg_data
where exists
(select 1 from JTY_WEBADI_RESOURCES jwr
where jwr.LAY_SEQ_NUM = jwot.LAY_SEQ_NUM
and jwr.header = jwot.header
and jwr.user_sequence = jwot.user_sequence
and jwr.interface_type = jwot.interface_type
and jwr.terr_rsc_id = l_rsc_access_rec.terr_rsc_id(i))
and jwot.USER_SEQUENCE = p_user_sequence
and jwot.header = l_header
and jwot.interface_type = l_intf_type
and jwot.status is null;
fnd_message.set_name ('JTF', 'JTY_OTH_TERR_UPDATE_RSC');
update JTY_WEBADI_OTH_TERR_INTF jwot
set status = x_return_status,
error_msg = X_Msg_Data
where exists
(select 1 from JTY_WEBADI_RESOURCES jwr
where jwr.LAY_SEQ_NUM = jwot.LAY_SEQ_NUM
and jwr.header = jwot.header
and jwr.user_sequence = jwot.user_sequence
and jwr.interface_type = jwot.interface_type)
and jwot.USER_SEQUENCE = p_user_sequence
and jwot.header = l_header
and jwot.interface_type = l_intf_type
and jwot.status is null;
END UPDATE_TERR_RSC;
PROCEDURE UPDATE_TERR_DEF(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
P_USER_SEQUENCE IN NUMBER,
--p_ORG_id IN NUMBER,
p_usage_id IN NUMBER,
p_user_id IN NUMBER
) IS
--l_api_version_number CONSTANT NUMBER := 1.0;
delete from JTY_WEBADI_OTH_TERR_INTF
where interface_type = l_intf_type
and status is not null;
UPDATE_TERR_QUAL_ID(
P_USER_SEQUENCE => p_user_sequence,
P_INTF_TYPE => l_intf_type,
P_HEADER => l_header);
delete_records(
P_USER_SEQUENCE => p_user_sequence,
P_INTF_TYPE => l_intf_type ,
p_action_flag => l_action_flag);
UPDATE_TERR(
p_user_sequence => p_user_sequence,
p_action_flag => l_action_flag,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
-- process terr update
--dbms_output.put_line(' process terr update... ');
UPDATE_TERR(
p_user_sequence => p_user_sequence,
p_action_flag => l_action_flag,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
UPDATE_TERR_RSC(P_USER_SEQUENCE => p_user_sequence,
--p_action_flag => l_action_flag,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
UPDATE_TERR_QUAL(P_USER_SEQUENCE => p_user_sequence,
--p_action_flag => l_action_flag,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
select NVL(status,'S') status, NVL(error_msg,'Success') error_msg, lay_seq_num
bulk collect into l_status, l_error_msg, l_lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF
where interface_type = l_intf_type
and user_sequence = p_user_sequence;
-- rollback all updated info
rollback;
update JTY_WEBADI_OTH_TERR_INTF
set status = l_status(i),
error_msg = l_error_msg(i)
where lay_seq_num = l_lay_seq_num(i)
and interface_type = l_intf_type
and user_sequence = p_user_sequence;
select NVL(status,'S') status, NVL(error_msg,'Success') error_msg, lay_seq_num
bulk collect into l_status, l_error_msg, l_lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF
where interface_type = l_intf_type
and user_sequence = p_user_sequence;
-- rollback all updated info
rollback;
update JTY_WEBADI_OTH_TERR_INTF
set status = l_status(i),
error_msg = l_error_msg(i)
where lay_seq_num = l_lay_seq_num(i)
and interface_type = l_intf_type
and user_sequence = p_user_sequence;
select NVL(status,'S') status, NVL(error_msg,'Success') error_msg, lay_seq_num
bulk collect into l_status, l_error_msg, l_lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF
where interface_type = l_intf_type
and user_sequence = p_user_sequence;
-- rollback all updated info
rollback;
update JTY_WEBADI_OTH_TERR_INTF
set status = l_status(i),
error_msg = l_error_msg(i)
where lay_seq_num = l_lay_seq_num(i)
and interface_type = l_intf_type
and user_sequence = p_user_sequence;
select NVL(status,'S') status, NVL(error_msg,'Success') error_msg, lay_seq_num
bulk collect into l_status, l_error_msg, l_lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF
where interface_type = l_intf_type
and user_sequence = p_user_sequence;
-- rollback all updated info
rollback;
update JTY_WEBADI_OTH_TERR_INTF
set status = l_status(i),
error_msg = l_error_msg(i)
where lay_seq_num = l_lay_seq_num(i)
and interface_type = l_intf_type
and user_sequence = p_user_sequence;
select NVL(status,'S') status, NVL(error_msg,'Success') error_msg, lay_seq_num
bulk collect into l_status, l_error_msg, l_lay_seq_num
from JTY_WEBADI_OTH_TERR_INTF
where interface_type = l_intf_type
and user_sequence = p_user_sequence;
update JTY_WEBADI_OTH_TERR_INTF
set status = l_status(i),
error_msg = l_error_msg(i)
where lay_seq_num = l_lay_seq_num(i)
and interface_type = l_intf_type
and user_sequence = p_user_sequence;
END UPDATE_TERR_DEF;