[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Cursor to get QType Usage from geo_terr_id. This value will be inserted in
table jtf_qtype_usgs_all */
CURSOR c_get_qtype_usgs(l_geo_terr_id NUMBER) IS
SELECT ra.access_type
FROM
JTF_TTY_GEO_TERR_RSC grsc
, jtf_tty_geo_terr gtr
, jtf_tty_terr_grp_roles tgr
, jtf_tty_role_access ra
WHERE grsc.GEO_TERRITORY_ID = l_geo_terr_id
AND gtr.geo_territory_id = grsc.geo_territory_id
AND grsc.rsc_role_code = tgr.role_code
AND tgr.terr_group_id = gtr.terr_group_id
AND ra.terr_group_role_id = tgr.terr_group_role_id;
SELECT b.role_code role_code
,b.terr_group_id
FROM jtf_tty_terr_grp_roles b
WHERE
b.terr_group_id = l_terr_group_id
ORDER BY b.role_code;
SELECT DISTINCT a.resource_id
, a.rsc_group_id
, NVL(a.rsc_resource_type,'RS_EMPLOYEE') rsc_resource_type
FROM jtf_tty_geo_terr_rsc a
, jtf_tty_geo_terr b
WHERE a.geo_territory_id = b.geo_territory_id
AND b.geo_territory_id = l_geo_territory_id
AND a.rsc_role_code = l_role;
SELECT DISTINCT a.access_type
FROM jtf_tty_role_access a
, jtf_tty_terr_grp_roles b
WHERE a.terr_group_role_id = b.terr_group_role_id
AND b.terr_group_id = lp_terr_group_id
AND b.role_code = lp_role;
/* Check if call is for update or create
for the geo_terr_id if there is record present in terr_all
then call is for update.In this case ..*/
BEGIN
SELECT
terr_id
INTO l_terr_id
FROM jtf_terr_all
WHERE geo_territory_id = p_geo_terr_id;
/* If l_terr_id is not null then call is from update
So delete all the relevant records and then let continue the create
process
if create then create the terr_id using the sequence*/
IF l_terr_id IS NOT NULL THEN
/* Update case All the delete scripts here */
DELETE FROM jtf_terr_usgs_all where terr_id = l_terr_id;
DELETE FROM jtf_terr_qtype_usgs_all where terr_id = l_terr_id;
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_terr_id);
DELETE FROM jtf_terr_rsc_all WHERE terr_id = l_terr_id;
DELETE FROM jtf_terr_all WHERE terr_id = l_terr_id;
SELECT JTF_TERR_S.nextval
INTO l_terr_id
FROM dual;
/* get the parent territory for geo's parent This will be inserted in parent_terr_id of terr_all */
/* get parent terr id and Org Id */
BEGIN
--dbms_output.put_line('p_geo_parent_terr_id:'||p_geo_parent_terr_id);
SELECT terr_id,
org_id,
rank,
start_Date_active,
end_date_active,
territory_type_id
INTO l_parent_terr_id,
l_org_id,
l_rank,
l_start_date_active,
l_end_date_active,
l_terr_type_id
FROM jtf_terr_all
WHERE geo_territory_id = p_geo_parent_terr_id;
SELECT terr_group_id
INTO l_geo_terr_group_id
FROM jtf_tty_geo_terr
where geo_territory_id = p_geo_terr_id;
BEGIN -- insert into terr_all
--dbms_output.put_line('TERRITORY ID ' || l_terr_id);
INSERT INTO jtf_terr_all
( TERR_ID
, NAME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,APPLICATION_SHORT_NAME
, ENABLED_FLAG
, PARENT_TERRITORY_ID
, RANK
, TERRITORY_TYPE_ID
,ORG_ID
,OBJECT_VERSION_NUMBER
,CATCH_ALL_FLAG
,TERR_GROUP_FLAG
,GEO_TERR_FLAG
,GEO_TERRITORY_ID
,TERR_GROUP_ID
,START_DATE_ACTIVE
,END_DATE_ACTIVE
)
SELECT l_terr_id
, p_geo_terr_name
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, SYSDATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, 'JTF'
, 'Y'
, l_parent_terr_id
, l_rank--TODO Rank
, l_terr_type_id
, l_org_id --org id
, OBJECT_VERSION_NUMBER
, 'N'
, 'Y'
, 'Y'
, p_geo_terr_id
, l_geo_terr_group_id
, l_start_date_active --TODO END_DATE_ACTIVE
, l_end_date_active
FROM jtf_tty_geo_terr
where geo_territory_id = p_geo_terr_id;
--dbms_output.put_line(' After inserting jtf_terr_all');
END; -- insert into terr_all
BEGIN --insert into jtf_terr_usgs_all
--dbms_output.put_line('insert into jtf_terr_usgs_all');
/* insert into terr_usgs_all */
--dbms_output.put_line('Before inserting in terr usgs all Terr ID = ' || l_terr_id);
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
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, SYSDATE --CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, l_terr_id
, -1001 -- FOR SALES ??
, l_org_id
from jtf_tty_geo_terr
where geo_territory_id = p_geo_terr_id;
--dbms_output.put_line(' After inserting jtf_terr_usgs_all');
END; --insert into jtf_terr_usgs_all
BEGIN -- insert into QType Usage
/* Open the cursor to get the Qtype */
FOR acctype IN c_get_qtype_usgs(p_geo_terr_id) LOOP
IF acctype.access_type='ACCOUNT' THEN
l_qual_type_usg_id := -1001;
SELECT JTF_TERR_QTYPE_USGS_S.NEXTVAL
INTO l_terr_qtype_usg_id
FROM DUAL;
/* Insert into table jtf_terr_qtype_all */
--dbms_output.put_line('insert into jtf_terr_qtype_usgs_all');
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 l_terr_qtype_usg_id
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, CREATED_BY
, SYSDATE --CREATION_DATE
, LAST_UPDATE_LOGIN
, l_terr_id
, l_qual_type_usg_id
, l_org_id
FROM jtf_tty_geo_terr
WHERE geo_territory_id = p_geo_terr_id;
END; --insert into QType Usage
/*insert in jtf_terr_rsc_all */
SELECT JTF_TERR_RSC_S.NEXTVAL
INTO l_terr_rsc_id
FROM DUAL;
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
,GROUP_ID
,ROLE
,PRIMARY_CONTACT_FLAG
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,ORG_ID
,OBJECT_VERSION_NUMBER )
SELECT l_terr_rsc_id
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,SYSDATE --CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
, l_terr_id
, rsc.resource_id
, rsc.rsc_resource_type
, rsc.rsc_group_id
, tran_type.role_code
, 'N'
, l_start_date_active
, l_end_date_active
, l_org_id --org id
, 1
FROM jtf_tty_geo_terr
WHERE geo_territory_id = p_geo_terr_id;
--dbms_output.put_line(' After inserting jtf_terr_rsc_all');
/*insert in jtf_terr_rsc_access_all table */
FOR rsc_acc IN c_role_access(l_geo_terr_group_id, tran_type.role_code) LOOP
--dbms_output.put_line('acc_type:'||rsc_acc.access_type);
SELECT JTF_TERR_RSC_ACCESS_S.NEXTVAL
INTO l_terr_rsc_access_id
FROM DUAL;
/* insert into jft_Terr_rsc_Access_all */
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
,ORG_ID
,OBJECT_VERSION_NUMBER
,TRANS_ACCESS_CODE
)
SELECT
l_terr_rsc_access_id
,SYSDATE
,fnd_global.user_id
,SYSDATE--CREATION_DATE
,fnd_global.user_id
,fnd_global.login_id
,l_terr_rsc_id
--,l_access_type --commented out
, rsc_acc.access_type
, l_org_id --org Id
, 1
, c.trans_access_code
FROM jtf_terr_rsc_all b
, jtf_terr_rsc_access_all c
WHERE b.terr_id = l_parent_terr_id
AND b.role = tran_type.role_code
AND b.resource_type <> 'RS_EMPLOYEE'
AND b.terr_rsc_id = c.terr_rsc_id
AND c.access_type = rsc_acc.access_type;