[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
LAST_UPDATED_BY NUMBER(15);
LAST_UPDATE_DATE DATE;
LAST_UPDATE_LOGIN NUMBER(15);
SELECT rsc.resource_name resource_name
, rol.role_name role_name
, grp.group_name group_name
, rsc.resource_id resource_id
, grp.group_id group_id
, rol.role_code role_code
FROM jtf_rs_resource_extns_vl rsc
, jtf_rs_groups_vl grp
, jtf_rs_roles_vl rol
, jtf_tty_named_acct_rsc narsc
, jtf_tty_terr_grp_accts ga
-- , jtf_tty_named_accts na
WHERE rsc.resource_id = narsc.resource_id
AND grp.group_id = narsc.rsc_group_id
AND rol.role_code = narsc.rsc_role_code
AND narsc.terr_group_account_id = ga.terr_group_account_id
AND narsc.rsc_resource_type = 'RS_EMPLOYEE'
-- AND ga.named_account_id = na.named_account_id
AND ga.named_account_id = P_NAID;
SELECT jtf_tty_webadi_int_id, terr_grp_acct_id
FROM jtf_tty_webadi_interface--JTF_TTY_WEBADI_INT_GT --
WHERE user_id=p_userid;
SELECT jtf_tty_interface_s.NEXTVAL INTO SEQ
FROM dual;
DELETE /*+ INDEX(jtf_tty_webadi_intf_n1) */
FROM JTF_TTY_WEBADI_INTERFACE tty
WHERE tty.user_id = TO_NUMBER(p_userid);
SELECT resource_id INTO salesMgr FROM jtf_rs_resource_extns
WHERE user_id = TO_NUMBER(p_userid);
'INSERT into JTF_TTY_WEBADI_INTERFACE ' || --JTF_TTY_WEBADI_INT_GT
' ( USER_SEQUENCE,USER_ID,TERR_GRP_ACCT_ID,JTF_TTY_WEBADI_INT_ID,NAMED_ACCOUNT,SITE_TYPE,TRADE_NAME,DUNS, '||
' GU_DUNS,GU_NAME,CITY,STATE,POSTAL_CODE,TERRITORY_GROUP, ' ||
' CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE ' ||
' ) ' ||
' SELECT ' ||
' :SEQ USER_SEQUENCE,'||
' :P_USERID USER_ID,'|| ' GAID, NAID, NAMED_ACCOUNT,SITE_TYPE,TRADE_NAME, SITE_DUNS, '||
' GU_DUNS,GU_NAME,CITY,STATE,POSTAL_CODE, GRPNAME, ' ||
' :P_USERID CREATED_BY, :curr_date CREATION_DATE,' ||
' :P_USERID LAST_UPDATED_BY, :curr_date LAST_UPDATE_DATE '||
' FROM (';
/* main select */
IF P_SEARCHTYPE = '91' --Territory Group
AND P_SEARCHVALUE IS NOT NULL AND trim(P_SEARCHVALUE) IS NOT NULL AND SUBSTR(trim(P_SEARCHVALUE),1,1)<> '%' THEN
l_var1 := UPPER(trim(P_SEARCHVALUE)) || '%';
' select /*+ ORDERED */ ' ||
' hzp.party_name named_account, ' ||
' lkp.meaning site_type, ' ||
' hzp.known_as trade_name, ' ||
' hzp.duns_number_c site_duns, ' ||
' GU.GU_NAME gu_name, ' ||
' GU.GU_DUNS gu_duns, ' ||
' hzl.city city, ' ||
' hzl.state state, ' ||
' hzl.postal_code postal_code, ' ||
' ttygrp.terr_group_name grpname, ' ||
' ga.terr_group_account_id gaid, '||
' ga.terr_group_id tgid, ' ||
' na.named_account_id naid ' ||
' from jtf_tty_terr_grp_accts ga ' ||
' ,jtf_tty_terr_groups ttygrp ' ||
' ,jtf_tty_named_accts na ' ||
' ,fnd_lookups lkp ' ||
' ,hz_parties hzp ' ||
' ,hz_party_sites hzps ' ||
' ,hz_locations hzl ' ||
' , ( /* Global Ultimate */ ' ||
' SELECT min(gup.party_name) GU_NAME ' ||
' , min(gup.duns_number_c) GU_DUNS ' ||
' , hzr.object_id GU_OBJECT_ID ' ||
' FROM hz_parties gup ' ||
' , hz_relationships hzr ' ||
' WHERE hzr.subject_table_name = ''HZ_PARTIES'' ' ||
' AND hzr.object_table_name = ''HZ_PARTIES'' ' ||
' AND hzr.relationship_type = ''GLOBAL_ULTIMATE'' ' ||
' AND hzr.relationship_code = ''GLOBAL_ULTIMATE_OF'' ' ||
' AND hzr.status = ''A'' ' ||
' AND hzr.subject_id = gup.party_id ' ||
' AND gup.status = ''A'' ' ||
' group by hzr.object_id ) GU ' ||
' where ' ||
' ga.terr_group_account_id IN ' ||
' ( ' ||
' select /*+ NO_MERGE */ narsc.terr_group_account_id ' ||
' from jtf_tty_named_acct_rsc narsc, ' ||
' jtf_tty_srch_my_resources_v repdn ' ||
' where current_user_id = :P_USERID ' ||
' and narsc.rsc_group_id = repdn.group_id ' ||
' and narsc.resource_id = repdn.resource_id ' ||
' ) ' ||
' and ttygrp.terr_group_id = ga.terr_group_id ' ||
' and ttygrp.active_from_date <= sysdate ' ||
' and ( ttygrp.active_to_date is null or ' ||
' ttygrp.active_to_date >= sysdate) ' ||
' and upper(ttygrp.terr_group_name) like :P_SEARCHSTR ' ||
' and na.named_account_id = ga.named_account_id ' ||
' AND na.site_type_code = lkp.lookup_code ' ||
' and lkp.lookup_type = ''JTF_TTY_SITE_TYPE_CODE'' ' ||
' and hzp.party_id = na.party_id ' ||
' and hzps.party_id = na.party_id ' ||
' and hzps.party_site_id = na.party_site_id ' ||
' and hzps.location_id = hzl.location_id ' ||
' AND GU.GU_OBJECT_ID (+) = hzp.party_id ' ||
' ) '; -- done
' select * from ( ' ||
' select hzp.party_name named_account, ' ||
' lkp.meaning site_type, ' ||
' hzp.known_as trade_name, ' ||
' hzp.duns_number_c site_duns, ' ||
' GU.GU_NAME gu_name, ' ||
' GU.GU_DUNS gu_duns, ' ||
' hzl.city city, ' ||
' hzl.state state, ' ||
' hzl.postal_code postal_code, ' ||
' hzl.country country, ' ||
' ttygrp.terr_group_name grpname, ' ||
' ga.terr_group_account_id gaid, ' ||
' ga.terr_group_id tgid, ' ||
' lkp.lookup_code sitetypecode, ' ||
' hzp.province, ' ||
' na.named_account_id naid ' ||
' from hz_parties hzp, ' ||
' hz_party_sites hzps, ' ||
' hz_locations hzl, ' ||
' jtf_tty_named_accts na, ' ||
' jtf_tty_terr_grp_accts ga, ' ||
' fnd_lookups lkp, ' ||
' jtf_tty_terr_groups ttygrp ' ||
' , ( /* Global Ultimate */ ' ||
' SELECT min(gup.party_name) GU_NAME ' ||
' , min(gup.duns_number_c) GU_DUNS ' ||
' , hzr.object_id GU_OBJECT_ID ' ||
' FROM hz_parties gup ' ||
' , hz_relationships hzr ' ||
' WHERE hzr.subject_table_name = ''HZ_PARTIES'' ' ||
' AND hzr.object_table_name = ''HZ_PARTIES'' ' ||
' AND hzr.relationship_type = ''GLOBAL_ULTIMATE'' ' ||
' AND hzr.relationship_code = ''GLOBAL_ULTIMATE_OF'' ' ||
' AND hzr.status = ''A'' ' ||
' AND hzr.subject_id = gup.party_id ' ||
' AND gup.status = ''A'' ' ||
' group by hzr.object_id ) GU ' ||
' where hzp.party_id = na.party_id ' ||
' and hzps.party_id = na.party_id ' ||
' and hzps.party_site_id = na.party_site_id ' ||
' and hzps.location_id = hzl.location_id ' ||
' and na.site_type_code = lkp.lookup_code ' ||
' and lkp.lookup_type = ''JTF_TTY_SITE_TYPE_CODE'' ' ||
' and na.named_account_id = ga.named_account_id ' ||
' and ttygrp.terr_group_id = ga.terr_group_id ' ||
' and ttygrp.active_from_date <= sysdate ' ||
' and ( ttygrp.active_to_date is null ' ||
' or ' ||
' ttygrp.active_to_date >= sysdate ' ||
' ) ' ||
' AND GU.GU_OBJECT_ID (+) = hzp.party_id ' ||
' ) '; -- not done
' where gaid IN ( select /*+ NO_MERGE */ narsc.terr_group_account_id ' ||
' from jtf_tty_named_acct_rsc narsc, '||
' jtf_tty_srch_my_resources_v repdn '||
' where narsc.resource_id = repdn.resource_id '||
' and narsc.rsc_group_id = repdn.group_id '||
' and repdn.current_user_id = :P_USERID ) ' ||
' )';
' where EXISTS ( select narsc.terr_group_account_id '||
' from jtf_tty_named_acct_rsc narsc, '||
' jtf_tty_srch_my_resources_v repdn '||
' where narsc.resource_id = repdn.resource_id '||
' and narsc.rsc_group_id = repdn.group_id '||
' and narsc.terr_group_account_id = gaid '||
' and repdn.current_user_id = :P_USERID ) ' ||
' and ';
' INSERT into JTF_TTY_WEBADI_INTERFACE ' || --JTF_TTY_WEBADI_INTERFACE '||
' ( USER_SEQUENCE,USER_ID,TERR_GRP_ACCT_ID,JTF_TTY_WEBADI_INT_ID,NAMED_ACCOUNT,SITE_TYPE,TRADE_NAME,DUNS, '||
' GU_DUNS,GU_NAME,CITY,STATE,POSTAL_CODE,TERRITORY_GROUP, ' ||
' CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE ' ||
' ) ' ||
' SELECT ' ||
' L_SEQ USER_SEQUENCE,'||
' P_USERID USER_ID,'|| ' GAID, NAID, NAMED_ACCOUNT,SITE_TYPE,TRADE_NAME, SITE_DUNS, '||
' GU_DUNS,GU_NAME,CITY,STATE,POSTAL_CODE, GRPNAME, ' ||
' P_USERID CREATED_BY, L_CURR_DATE CREATION_DATE,' ||
' P_USERID LAST_UPDATED_BY, L_CURR_DATE LAST_UPDATE_DATE '||
' FROM ( ' ||
' select '||
' ga.terr_group_id tgid, ' ||
' na.named_account_id naid, ' ||
' hzp.party_name named_account, ' ||
' hzp.party_id party_id,'||
' lkp.meaning site_type, ' ||
' hzp.known_as trade_name, ' ||
' hzp.duns_number_c site_duns, ' ||
' GU.GU_DUNS gu_duns, ' ||
' GU.GU_NAME gu_name, ' ||
' hzl.city city, ' ||
' hzl.state state, ' ||
' hzl.postal_code postal_code, ' ||
' hzl.country country, ' ||
' ttygrp.terr_group_name grpname, ' ||
' ga.terr_group_account_id gaid,' ||
' lkp.lookup_code sitetypecode, '||
' hzp.sic_code siccode, ' ||
' hzp.province privince, '||
' decode(hzps.identifying_address_flag, ''Y'', ''Y'', ''N'' ) identifying_addr_flag ' ||
' from hz_parties hzp, ' ||
' hz_locations hzl, ' ||
' hz_party_sites hzps, ' ||
' jtf_tty_named_accts na, ' ||
' jtf_tty_terr_grp_accts ga, ' ||
' fnd_lookups lkp, ' ||
' jtf_tty_terr_groups ttygrp ' ||
' , ( /* Global Ultimate */ ' ||
' SELECT min(gup.party_name) GU_NAME ' ||
' , min(gup.duns_number_c) GU_DUNS ' ||
' , hzr.object_id GU_OBJECT_ID ' ||
' FROM hz_parties gup ' ||
' , hz_relationships hzr ' ||
' WHERE hzr.subject_table_name = ''HZ_PARTIES'' ' ||
' AND hzr.object_table_name = ''HZ_PARTIES'' ' ||
' AND hzr.relationship_type = ''GLOBAL_ULTIMATE'' ' ||
' AND hzr.relationship_code = ''GLOBAL_ULTIMATE_OF'' ' ||
' AND hzr.status = ''A'' ' ||
' AND hzr.subject_id = gup.party_id ' ||
' AND gup.status = ''A'' ' ||
' group by hzr.object_id ) GU ' ||
' where hzp.party_id = na.party_id ' ||
' AND hzps.party_site_id = na.party_site_id ' ||
' AND hzp.party_id = hzps.party_id ' ||
' AND hzps.party_id = na.party_id ' ||
' AND hzps.location_id = hzl.location_id ' ||
' and na.site_type_code = lkp.lookup_code ' ||
' and lkp.lookup_type = ''JTF_TTY_SITE_TYPE_CODE'' ' ||
' and na.named_account_id = ga.named_account_id ' ||
' AND ttygrp.terr_group_id = ga.terr_group_id ' ||
' AND GU.GU_OBJECT_ID (+) = hzp.party_id ' ;
' AND hzp.PARTY_ID in ( select hzr.object_id '||
' from hz_parties hzp1, ' ||
' hz_relationships hzr '||
' where hzp1.party_id = hzr.subject_id '||
' and hzr.subject_table_name = ''HZ_PARTIES'' '||
' and hzr.object_table_name = ''HZ_PARTIES'' '||
' and hzr.relationship_type = ''DOMESTIC_ULTIMATE'' '||
' and hzr.relationship_code = ''DOMESTIC_ULTIMATE_OF'' '||
' and hzr.status = ''A'' ';
' select /*+ NO_MERGE */ narsc1.terr_group_account_id '||
' from jtf_tty_named_acct_rsc narsc1, '||
'( SELECT dir.resource_id, ' ||
' MY_GRPS.group_id , ' ||
' MY_GRPS.CURRENT_USER_ID ' ||
' FROM jtf_rs_group_members grpmemo , ' ||
' jtf_rs_resource_extns dir , ' ||
' ( SELECT /*+ NO_MERGE */ dv.group_id , ' ||
' mrsc.user_id CURRENT_USER_ID ' ||
' FROM jtf_rs_group_usages usg , ' ||
'jtf_rs_groups_denorm dv , ' ||
'jtf_rs_rep_managers sgh , '||
'jtf_rs_resource_extns mrsc , ' ||
'jtf_rs_roles_b rol , ' ||
'jtf_rs_role_relations rlt ' ||
' WHERE usg.usage = ''SALES'' ' ||
' AND usg.group_id = dv.group_id ' ||
' AND rlt.role_id = rol.role_id ' ||
' AND rlt.role_relate_id = sgh.par_role_relate_id '||
' AND dv.parent_group_id = sgh.group_id ' ||
' AND sgh.resource_id = sgh.parent_resource_id ' ||
' AND (sgh.hierarchy_type IN (''MGR_TO_MGR'') ' ||
' OR rol.role_code = FND_PROFILE.VALUE(''JTF_TTY_NA_PROXY_USER_ROLE'')) ' ||
' AND mrsc.resource_id = sgh.resource_id ' ||
' ) MY_GRPS ' ||
' WHERE grpmemo.resource_id = dir.resource_id ' ||
' AND grpmemo.group_id = MY_GRPS.group_id ' ||
' UNION ALL ' ||
' SELECT dir.resource_id , '||
' grpmemo.group_id , ' ||
' dir.user_id CURRENT_USER_ID ' ||
' FROM jtf_rs_group_members grpmemo , ' ||
' jtf_rs_resource_extns dir , ' ||
' jtf_rs_group_usages usg ' ||
' WHERE usg.usage = ''SALES'' ' ||
' AND grpmemo.resource_id = dir.resource_id ' ||
' AND grpmemo.group_id = usg.group_id ' ||
' ) repdn1 ' ||
' where narsc1.resource_id = repdn1.resource_id '||
' and narsc1.rsc_group_id = repdn1.group_id '||
' and repdn1.current_user_id = :P_SALESPERSON ';
' select narsc1.terr_group_account_id '||
' from jtf_tty_named_acct_rsc narsc1, '||
' jtf_rs_group_members mem1, '||
' jtf_rs_groups_denorm grpdn1 '||
' where narsc1.resource_id = mem1.resource_id '||
' and narsc1.rsc_group_id = mem1.group_id '||
' and mem1.delete_flag = ''N'' '||
' and mem1.group_id = grpdn1.group_id '||
' and SYSDATE BETWEEN NVL(grpdn1.start_date_active, SYSDATE-1) '||
' AND NVL(grpdn1.end_date_active, SYSDATE+1) '||
' and grpdn1.parent_group_id = :P_SALES_GROUP ';
' select narsc1.terr_group_account_id'||
' from jtf_tty_named_acct_rsc narsc1,'||
' jtf_tty_my_resources_v repdn1,'||
' jtf_rs_rep_managers repmgr1,'||
' jtf_rs_groups_denorm grpdn1 '||
' where narsc1.resource_id = repmgr1.resource_id'||
' and narsc1.rsc_group_id = repmgr1.group_id'||
' and repmgr1.group_id = grpdn1.group_id'||
' and repdn1.resource_id = repmgr1.parent_resource_id'||
' and repdn1.parent_group_id = grpdn1.parent_group_id'||
' and repdn1.current_user_id = :P_USERID '||
' and repdn1.role_code = :P_SALES_ROLE ';
' select narsc1.terr_group_account_id '||
' from jtf_tty_named_acct_rsc narsc1, '||
' jtf_tty_my_resources_v repdn1 '||
' where narsc1.resource_id = repdn1.resource_id '||
' and narsc1.rsc_group_id = repdn1.group_id '||
' and repdn1.current_user_id = :P_SALESPERSON '||
' and repdn1.parent_group_id = :P_SALES_GROUP ';
' select narsc1.terr_group_account_id '||
' from jtf_tty_named_acct_rsc narsc1, '||
' jtf_tty_my_resources_v repdn1 '||
' where narsc1.resource_id = repdn1.resource_id '||
' and narsc1.rsc_group_id = repdn1.group_id '||
' and repdn1.current_user_id = :P_SALESPERSON '||
' and repdn1.current_user_role_code = :P_SALES_ROLE ';
' select narsc1.terr_group_account_id '||
' from jtf_tty_named_acct_rsc narsc1, '||
' jtf_tty_my_resources_v repdn1, '||
' jtf_rs_rep_managers repmgr1, '||
' jtf_rs_groups_denorm grpdn1 '||
' where narsc1.resource_id = repmgr1.resource_id '||
' and narsc1.rsc_group_id = repmgr1.group_id '||
' and repmgr1.group_id = grpdn1.group_id '||
' and repdn1.resource_id = repmgr1.parent_resource_id '||
' and repdn1.current_user_id = :P_USERID '||
' and repdn1.role_code = :P_SALES_ROLE '||
' and grpdn1.parent_group_id = :P_SALES_GROUP ';
' select narsc1.terr_group_account_id '||
' from jtf_tty_named_acct_rsc narsc1, '||
' jtf_tty_my_resources_v repdn1 '||
' where narsc1.resource_id = repdn1.resource_id '||
' and narsc1.rsc_group_id = repdn1.group_id '||
' and repdn1.current_user_id = :P_SALESPERSON ' ||
' and repdn1.parent_group_id = :P_SALES_GROUP '||
' and repdn1.current_user_role_code = :P_SALES_ROLE ';
' ( select narsc1.terr_group_account_id '||
' from jtf_tty_named_acct_rsc narsc1, '||
' jtf_tty_my_directs_v dir '||
' where narsc1.resource_id = dir.resource_id '||
' and narsc1.rsc_group_id = dir.group_id '||
' and dir.group_id = dir.parent_group_id '||
' and narsc1.assigned_flag = ''N'' '||
' and dir.current_user_id = :P_USERID ';
'( select narsc1.terr_group_account_id '||
' from jtf_tty_named_acct_rsc narsc1, '||
' jtf_rs_resource_extns rsc1 '||
' where narsc1.resource_id = rsc1.resource_id '||
' and narsc1.assigned_flag = ''N'' '||
' and rsc1.user_id = :P_USERID ';
' ( select narsc.terr_group_account_id '||
' from jtf_tty_named_acct_rsc narsc, '||
' jtf_tty_srch_my_resources_v repdn '||
' where narsc.resource_id = repdn.resource_id '||
' and narsc.rsc_group_id = repdn.group_id '||
' and repdn.current_user_id = :P_USERID ';
' and not exists ( select ''Y'' '||
' from jtf_tty_named_acct_rsc narsc1, '||
' jtf_tty_my_directs_v dir '||
' where narsc1.resource_id = dir.resource_id '||
' and narsc1.rsc_group_id = dir.group_id '||
' and dir.group_id = dir.parent_group_id '||
' and narsc1.terr_group_account_id = narsc.terr_group_account_id '||
' and narsc1.assigned_flag = ''N'' '||
' and dir.current_user_id = :P_USERID ';
' and not exists ( select ''Y'' '||
' from jtf_tty_named_acct_rsc narsc1, '||
' jtf_rs_resource_extns rsc1 '||
' where narsc1.resource_id = rsc1.resource_id '||
' and narsc1.terr_group_account_id = narsc.terr_group_account_id '||
' and narsc1.assigned_flag = ''N'' '||
' and rsc1.user_id = :P_USERID ';
l_na_query := l_na_query || ' EXISTS (select narsc1.terr_group_account_id ';
' ga.terr_group_account_id IN (select /*+ NO_MERGE */ narsc1.terr_group_account_id ';
insert into tmp values(p_ExtraWhereClause, 'p_ExtraWhereClause'); commit;
insert into tmp values(p_paranum,'p_paranum');
insert into tmp values(p_1, 'p_1');
insert into tmp values(p_2, 'p_2');
insert into tmp values(p_3, 'p_3');
insert into tmp values(p_4, 'p_4');
insert into tmp values(p_5, 'p_5'); commit;
l_sql := 'SELECT role_code, MAX(num) num '
|| 'FROM ( '
|| 'SELECT rol.role_code role_code, COUNT(rol.role_code) num '
|| 'FROM '
|| ' ( SELECT /*+ DYNAMIC_SAMPLING(jtw,5) */ '
|| ' jtw.jtf_tty_webadi_int_id '
|| ' FROM jtf_tty_webadi_interface jtw ' --JTF_TTY_WEBADI_INT_GT
|| ' WHERE jtw.user_id = ' ||p_userid
|| ' ) sub, '
|| ' jtf_rs_roles_vl rol, '
|| ' jtf_tty_named_acct_rsc narsc, '
|| ' jtf_tty_terr_grp_accts ga '
|| ' WHERE rol.role_code = narsc.rsc_role_code '
|| ' AND narsc.terr_group_account_id = ga.terr_group_account_id '
|| ' AND ga.named_account_id = sub.jtf_tty_webadi_int_id '
|| ' GROUP BY ga.named_account_id, rol.role_code '
|| ' ORDER BY MAX(rol.role_name) '
|| ' ) '
|| ' GROUP BY role_code ';
UPDATE JTF_TTY_WEBADI_INTERFACE -- /*+ INDEX JTF_TTY_WEBADI_INTF_N2 */
SET RESOURCE1_NAME=RESOURCE_NAME(1),GROUP1_NAME=GROUP_NAME(1),ROLE1_NAME=ROLE_NAME(1),
RESOURCE2_NAME=RESOURCE_NAME(2),GROUP2_NAME=GROUP_NAME(2),ROLE2_NAME=ROLE_NAME(2),
RESOURCE3_NAME=RESOURCE_NAME(3),GROUP3_NAME=GROUP_NAME(3),ROLE3_NAME=ROLE_NAME(3),
RESOURCE4_NAME=RESOURCE_NAME(4),GROUP4_NAME=GROUP_NAME(4),ROLE4_NAME=ROLE_NAME(4),
RESOURCE5_NAME=RESOURCE_NAME(5),GROUP5_NAME=GROUP_NAME(5),ROLE5_NAME=ROLE_NAME(5),
RESOURCE6_NAME=RESOURCE_NAME(6),GROUP6_NAME=GROUP_NAME(6),ROLE6_NAME=ROLE_NAME(6),
RESOURCE7_NAME=RESOURCE_NAME(7),GROUP7_NAME=GROUP_NAME(7),ROLE7_NAME=ROLE_NAME(7),
RESOURCE8_NAME=RESOURCE_NAME(8),GROUP8_NAME=GROUP_NAME(8),ROLE8_NAME=ROLE_NAME(8),
RESOURCE9_NAME=RESOURCE_NAME(9),GROUP9_NAME=GROUP_NAME(9),ROLE9_NAME=ROLE_NAME(9),
RESOURCE10_NAME=RESOURCE_NAME(10),GROUP10_NAME=GROUP_NAME(10),ROLE10_NAME=ROLE_NAME(10),
RESOURCE11_NAME=RESOURCE_NAME(11),GROUP11_NAME=GROUP_NAME(11),ROLE11_NAME=ROLE_NAME(11),
RESOURCE12_NAME=RESOURCE_NAME(12),GROUP12_NAME=GROUP_NAME(12),ROLE12_NAME=ROLE_NAME(12),
RESOURCE13_NAME=RESOURCE_NAME(13),GROUP13_NAME=GROUP_NAME(13),ROLE13_NAME=ROLE_NAME(13),
RESOURCE14_NAME=RESOURCE_NAME(14),GROUP14_NAME=GROUP_NAME(14),ROLE14_NAME=ROLE_NAME(14),
RESOURCE15_NAME=RESOURCE_NAME(15),GROUP15_NAME=GROUP_NAME(15),ROLE15_NAME=ROLE_NAME(15),
RESOURCE16_NAME=RESOURCE_NAME(16),GROUP16_NAME=GROUP_NAME(16),ROLE16_NAME=ROLE_NAME(16),
RESOURCE17_NAME=RESOURCE_NAME(17),GROUP17_NAME=GROUP_NAME(17),ROLE17_NAME=ROLE_NAME(17),
RESOURCE18_NAME=RESOURCE_NAME(18),GROUP18_NAME=GROUP_NAME(18),ROLE18_NAME=ROLE_NAME(18),
RESOURCE19_NAME=RESOURCE_NAME(19),GROUP19_NAME=GROUP_NAME(19),ROLE19_NAME=ROLE_NAME(19),
RESOURCE20_NAME=RESOURCE_NAME(20),GROUP20_NAME=GROUP_NAME(20),ROLE20_NAME=ROLE_NAME(20),
RESOURCE21_NAME=RESOURCE_NAME(21),GROUP21_NAME=GROUP_NAME(21),ROLE21_NAME=ROLE_NAME(21),
RESOURCE22_NAME=RESOURCE_NAME(22),GROUP22_NAME=GROUP_NAME(22),ROLE22_NAME=ROLE_NAME(22),
RESOURCE23_NAME=RESOURCE_NAME(23),GROUP23_NAME=GROUP_NAME(23),ROLE23_NAME=ROLE_NAME(23),
RESOURCE24_NAME=RESOURCE_NAME(24),GROUP24_NAME=GROUP_NAME(24),ROLE24_NAME=ROLE_NAME(24),
RESOURCE25_NAME=RESOURCE_NAME(25),GROUP25_NAME=GROUP_NAME(25),ROLE25_NAME=ROLE_NAME(25),
RESOURCE26_NAME=RESOURCE_NAME(26),GROUP26_NAME=GROUP_NAME(26),ROLE26_NAME=ROLE_NAME(26),
RESOURCE27_NAME=RESOURCE_NAME(27),GROUP27_NAME=GROUP_NAME(27),ROLE27_NAME=ROLE_NAME(27),
RESOURCE28_NAME=RESOURCE_NAME(28),GROUP28_NAME=GROUP_NAME(28),ROLE28_NAME=ROLE_NAME(28),
RESOURCE29_NAME=RESOURCE_NAME(29),GROUP29_NAME=GROUP_NAME(29),ROLE29_NAME=ROLE_NAME(29),
RESOURCE30_NAME=RESOURCE_NAME(30),GROUP30_NAME=GROUP_NAME(30),ROLE30_NAME=ROLE_NAME(30)
WHERE user_id = p_userid
--and TERR_GRP_ACCT_ID = NAST_tbl.TERR_GRP_ACCT_ID(m);