[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM as_leads_all
WHERE lead_id = pc_lead_id
AND incumbent_partner_party_id = pc_partner_id;
SELECT 'X'
FROM as_leads_all al
, pv_lead_assignments ass
WHERE al.lead_id = ass.lead_id
AND al.incumbent_partner_party_id = ass.partner_id
AND ass.wf_item_type = pc_item_type
AND ass.wf_item_key = pc_item_key
AND al.lead_id = pc_lead_id ;
UPDATE as_leads_all
SET incumbent_partner_party_id = NULL ,
incumbent_partner_resource_id = NULL
WHERE lead_id = p_lead_id;
select lead_number from as_leads_all where lead_id = pc_lead_id;
CURSOR C2 IS SELECT PV_PARTY_NOTIFICATIONS_S.nextval FROM sys.dual;
INSERT into pv_party_notifications (
PARTY_NOTIFICATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
NOTIFICATION_ID,
NOTIFICATION_TYPE,
LEAD_ASSIGNMENT_ID,
WF_ITEM_TYPE,
WF_ITEM_KEY,
USER_ID,
--USER_NAME,
RESOURCE_ID,
DECISION_MAKER_FLAG,
RESOURCE_RESPONSE,
RESPONSE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
) values (
l_party_notification_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id,
1,
p_party_notify_rec.REQUEST_ID,
p_party_notify_rec.PROGRAM_APPLICATION_ID,
p_party_notify_rec.PROGRAM_ID,
p_party_notify_rec.PROGRAM_UPDATE_DATE,
p_party_notify_rec.NOTIFICATION_ID,
p_party_notify_rec.NOTIFICATION_TYPE,
p_party_notify_rec.LEAD_ASSIGNMENT_ID,
p_party_notify_rec.WF_ITEM_TYPE,
p_party_notify_rec.WF_ITEM_KEY,
p_party_notify_rec.USER_ID,
--p_party_notify_rec.USER_NAME,
p_party_notify_rec.RESOURCE_ID,
p_party_notify_rec.DECISION_MAKER_FLAG,
p_party_notify_rec.RESOURCE_RESPONSE,
p_party_notify_rec.RESPONSE_DATE,
p_party_notify_rec.ATTRIBUTE_CATEGORY,
p_party_notify_rec.ATTRIBUTE1,
p_party_notify_rec.ATTRIBUTE2,
p_party_notify_rec.ATTRIBUTE3,
p_party_notify_rec.ATTRIBUTE4,
p_party_notify_rec.ATTRIBUTE5,
p_party_notify_rec.ATTRIBUTE6,
p_party_notify_rec.ATTRIBUTE7,
p_party_notify_rec.ATTRIBUTE8,
p_party_notify_rec.ATTRIBUTE9,
p_party_notify_rec.ATTRIBUTE10,
p_party_notify_rec.ATTRIBUTE11,
p_party_notify_rec.ATTRIBUTE12,
p_party_notify_rec.ATTRIBUTE13,
p_party_notify_rec.ATTRIBUTE14,
p_party_notify_rec.ATTRIBUTE15
);
select pv_lead_assignments_s.nextval into l_Lead_assignment_ID from sys.dual;
insert into pv_lead_assignments(
LEAD_ASSIGNMENT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LEAD_ID,
PARTNER_ID,
PARTNER_ACCESS_CODE,
RELATED_PARTY_ID,
RELATED_PARTY_ACCESS_CODE,
ASSIGN_SEQUENCE,
STATUS_DATE,
STATUS,
REASON_CODE,
SOURCE_TYPE,
WF_ITEM_TYPE,
WF_ITEM_KEY,
ERROR_TXT
) values (
l_Lead_assignment_ID,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id,
0,
p_assignment_rec.LEAD_ID,
p_assignment_rec.PARTNER_ID,
p_assignment_rec.PARTNER_ACCESS_CODE,
p_assignment_rec.RELATED_PARTY_ID,
p_assignment_rec.RELATED_PARTY_ACCESS_CODE,
p_assignment_rec.ASSIGN_SEQUENCE,
p_assignment_rec.STATUS_DATE,
p_assignment_rec.STATUS,
p_assignment_rec.REASON_CODE,
p_assignment_rec.SOURCE_TYPE,
p_assignment_rec.WF_ITEM_TYPE,
nvl(p_assignment_rec.WF_ITEM_KEY, l_lead_assignment_id),
p_assignment_rec.ERROR_TXT
);
SELECT extn.category
FROM fnd_user fuser,
jtf_rs_resource_extns extn
WHERE fuser.user_id = pc_user_id
AND fuser.user_id = extn.user_id;
select pv_lead_workflows_s.nextval into l_Lead_Workflow_ID from sys.dual;
insert into pv_lead_workflows(
LEAD_WORKFLOW_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LEAD_ID,
ENTITY,
WF_ITEM_TYPE,
WF_ITEM_KEY,
ROUTING_TYPE,
ROUTING_STATUS,
WF_STATUS,
MATCHED_DUE_DATE,
OFFERED_DUE_DATE,
BYPASS_CM_OK_FLAG,
LATEST_ROUTING_FLAG,
FAILURE_CODE,
FAILURE_MESSAGE
) values (
l_Lead_Workflow_ID,
sysdate,
nvl(p_workflow_rec.last_updated_by, fnd_global.user_id),
sysdate,
nvl(p_workflow_rec.created_by, fnd_global.user_id),
fnd_global.conc_login_id,
0,
p_workflow_rec.Lead_ID,
p_workflow_rec.Entity,
p_workflow_rec.wf_Item_Type,
to_char(l_lead_workflow_id),
p_workflow_rec.routing_type,
p_workflow_rec.routing_status,
p_workflow_rec.wf_status,
null,
null,
p_workflow_rec.bypass_cm_ok_flag,
p_workflow_rec.latest_routing_flag,
p_workflow_rec.failure_code,
p_workflow_rec.failure_message
);
l_user_id := nvl(p_workflow_rec.last_updated_by, fnd_global.user_id);
procedure delete_lead_assignment_row (
p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_lead_assignment_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) is
l_api_name CONSTANT VARCHAR2(30) := 'delete_lead_assignment_row';
delete from pv_lead_assignments
where lead_assignment_id = p_lead_assignment_id;
fnd_message.SET_token('TEXT', 'Deleted ' || sql%rowcount || ' rows. Should have deleted 1 row');
end delete_lead_assignment_row;
SELECT
cj.resource_id person_resource_id,
cj.category user_type,
cj.source_id party_id,
cj.source_name name, -- cm name (use in error message)
cu.user_id userid,
cu.user_name logon_user -- cm fnd_user exists
FROM
jtf_rs_resource_extns cj,
fnd_user cu
WHERE
cj.resource_id = pc_rs_id
AND cj.user_id = cu.user_id (+)
AND (cu.end_date > sysdate OR cu.end_date IS NULL);
SELECT distinct -- user could have both CM roles
pt_acc.resource_id rs_id,
'INTERNAL' origin
FROM
pv_partner_accesses PT_ACC,
pv_partner_profiles PT_PROF,
jtf_rs_resource_extns extn,
per_all_people_f per,
jtf_rs_role_relations rel,
jtf_rs_roles_b role,
fnd_user usr
WHERE
pt_acc.partner_id = pc_partner_id and
pt_acc.partner_id = pt_prof.partner_id and
pt_prof.status = 'A' and
pt_acc.resource_id = extn.resource_id and
extn.category = pv_assignment_pub.g_resource_employee and
extn.source_id = per.person_id and
(trunc(sysdate) between per.effective_start_date and per.effective_end_date) and
extn.resource_id = rel.role_resource_id and
rel.role_resource_type = 'RS_INDIVIDUAL' and
(rel.end_date_active is null or rel.end_date_active > sysdate) and
rel.delete_flag = 'N' and
rel.role_id = role.role_id and
role.role_type_code = 'PRM' and
role.role_code in ('CHANNEL_MANAGER', 'CHANNEL_REP') and
extn.user_id = usr.user_id and
(usr.end_date > sysdate OR usr.end_date IS NULL);
SELECT res.resource_id
FROM jtf_rs_resource_extns res
where resource_id = to_number(fnd_profile.value('PV_DEFAULT_CM'));
select distinct
c.resource_id
from
pv_partner_profiles a,
hz_relationships b,
jtf_rs_resource_extns c,
as_accesses_all d,
fnd_user usr
where
a.partner_id = pc_partner_id and
a.partner_party_id = b.object_id and
b.subject_table_name = 'HZ_PARTIES' and
b.object_table_name = 'HZ_PARTIES' and
b.directional_flag = 'F' and
b.relationship_code = 'EMPLOYEE_OF' and
b.relationship_type = 'EMPLOYMENT' and
(b.end_date is null or b.end_date > sysdate) and
b.status = 'A' and
b.party_id = c.source_id and
c.category = pv_assignment_pub.g_resource_party and
sysdate between c.start_date_active and nvl(c.end_date_active,sysdate) and
c.resource_id = d.salesforce_id and
d.lead_id = pc_opportunity_id and
c.user_id = usr.user_id and
(usr.end_date > sysdate OR usr.end_date IS NULL);
SELECT
pj.resource_id
FROM
pv_partner_profiles prof,
hz_relationships pr2,
jtf_rs_resource_extns pj,
fnd_user usr
WHERE
prof.partner_id = pc_partner_id
and prof.partner_party_id = pr2.object_id
and pr2.subject_table_name = 'HZ_PARTIES'
and pr2.object_table_name = 'HZ_PARTIES'
and pr2.directional_flag = 'F'
and pr2.relationship_code = 'EMPLOYEE_OF'
and pr2.relationship_type = 'EMPLOYMENT'
and (pr2.end_date is null or pr2.end_date > sysdate)
and pr2.status = 'A'
and pr2.party_id = pj.source_id
and pj.category = pv_assignment_pub.g_resource_party
and sysdate between pj.start_date_active and nvl(pj.end_date_active,sysdate)
and pj.user_id = usr.user_id
and (usr.end_date > sysdate OR usr.end_date IS NULL)
and exists(select 1 from jtf_auth_principal_maps jtfpm,
jtf_auth_principals_b jtfp1, jtf_auth_domains_b jtfd,
jtf_auth_principals_b jtfp2, jtf_auth_role_perms jtfrp,
jtf_auth_permissions_b jtfperm
where usr.user_name = jtfp1.principal_name
and jtfp1.is_user_flag=1
and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
and jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
and jtfp2.is_user_flag=0
and jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
and jtfrp.positive_flag = 1
and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
and jtfperm.permission_name = 'PV_OPPTY_CONTACT'
and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
and jtfd.domain_name='CRM_DOMAIN' );
select pt.party_name,
ar.meaning,
pr.relationship_code,
pr.object_id,
imp.indirectly_managed_flag
from pv_partner_profiles pf,
hz_relationships pr,
hz_organization_profiles op,
ar_lookups ar,
hz_parties pt,
(select distinct a.partner_id, 'Y' indirectly_managed_flag from pv_partner_accesses a, pv_partner_profiles b
where a.partner_id = pc_party_rel_id
and a.vad_partner_id = b.partner_id and b.status = 'A') imp
where pf.partner_id = pc_party_rel_id
and pf.partner_id = imp.partner_id (+)
and pr.party_id = pf.partner_id
and pr.subject_table_name = 'HZ_PARTIES'
and pr.object_table_name = 'HZ_PARTIES'
and (pr.end_date is null or pr.end_date > sysdate)
and pr.status in ('A', 'I')
and pr.object_id = op.party_id
and op.internal_flag = 'Y'
and op.effective_end_date is null
and ar.lookup_type = 'PARTY_RELATIONS_TYPE'
AND AR.lookup_code = pr.relationship_code
and pr.subject_id = pt.party_id
and pt.status in ('A', 'I');
SELECT distinct -- user could have both CM roles
pt_acc.resource_id rs_id,
'EXTERNAL' origin
FROM
pv_partner_accesses PT_ACC,
pv_partner_profiles PT_PROF,
jtf_rs_resource_extns extn,
hz_relationships emp,
jtf_rs_role_relations rel,
jtf_rs_roles_b role
where
PT_ACC.partner_id = pc_partner_id and
PT_ACC.vad_partner_id = pc_vad_id and
PT_ACC.vad_partner_id = PT_PROF.partner_id and
PT_PROF.status = 'A' and
PT_ACC.resource_id = extn.resource_id and
extn.category = pv_assignment_pub.g_resource_party and
extn.source_id = emp.party_id and
emp.subject_table_name = 'HZ_PARTIES' and
emp.object_table_name = 'HZ_PARTIES' and
emp.directional_flag = 'F' and
emp.relationship_code = 'EMPLOYEE_OF' and
emp.relationship_type = 'EMPLOYMENT' and
(emp.end_date is null or emp.end_date > sysdate) and
emp.status in ('A', 'I') and
emp.object_id = PT_PROF.partner_party_id and
extn.resource_id = rel.role_resource_id and
rel.role_resource_type = 'RS_INDIVIDUAL' and
(rel.end_date_active is null or rel.end_date_active > sysdate) and
rel.delete_flag = 'N' and
rel.role_id = role.role_id and
role.role_type_code = 'PRM' and
role.role_code in ('CHANNEL_MANAGER', 'CHANNEL_REP');
fnd_message.Set_Token('TEXT', 'cm resource id: ' || l_rs_rec.rs_id || ' already selected' );
fnd_message.Set_Token('TEXT', 'cm resource id: ' || l_rs_rec.rs_id || ' already selected' );
procedure UpdateAccess
( p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_current_username IN VARCHAR2,
p_lead_id IN NUMBER,
p_customer_id IN NUMBER,
p_address_id IN NUMBER,
p_resource_id IN NUMBER,
p_access_type IN NUMBER,
p_access_action IN NUMBER,
x_access_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
as
l_temp varchar2(100);
'select a.category, b.user_name, a.source_id ' ||
'from jtf_rs_resource_extns a, fnd_user b ' ||
'where a.resource_id = :p_resource_id and a.user_id = b.user_id ' ;
'select re.source_id from jtf_rs_resource_extns re where re.resource_id = :p_resource_id ';
l_api_name CONSTANT VARCHAR2(30) := 'UpdateAccess';
select access_id , prm_keep_flag, salesforce_id
from as_accesses_all
where salesforce_id = pc_salesforce_id
and lead_id = pc_lead_id;
select am.resource_id, owner.resource_id
from pv_lead_workflows wf, jtf_rs_resource_extns am, as_leads_all ld, jtf_rs_resource_extns owner
where wf.lead_id = pc_lead_id
and wf.entity = 'OPPORTUNITY'
and wf.latest_routing_flag = 'Y'
and wf.created_by = am.user_id
and ld.lead_id = pc_lead_id
and ld.created_by = owner.user_id;
SELECT COUNT(*) sales_credit_count
FROM as_sales_credits
WHERE lead_id = pc_lead_id AND
salesforce_id = pc_salesforce_id AND
credit_type_id = 1 AND
NVL(credit_amount, 0) > 0;
SELECT sales_credit_id
FROM as_sales_credits
WHERE lead_id = pc_lead_id AND
salesforce_id = pc_salesforce_id AND
credit_type_id = 2 AND
NVL(credit_amount, 0) > 0;
SELECT pn.resource_id
FROM pv_lead_workflows pw, pv_lead_assignments pa,
pv_party_notifications pn
WHERE pw.wf_item_key = pa.wf_item_key
AND pa.lead_assignment_id = pn.lead_assignment_id
AND pw.latest_routing_flag = 'Y'
AND pw.lead_id = pc_lead_id
AND pn.notification_type = 'OFFERED_TO';
select decode(st.opp_open_status_flag,'N',NULL,st.opp_open_status_flag)
from as_leads_all ld, as_statuses_b st
where ld.lead_id = pc_lead_id
and ld.status = st.status_code;
SELECT sql_text
FROM pv_entity_Attrs
WHERE entity = 'PARTNER' and
attribute_id = 7
;
select source_id from jtf_rs_resource_extns
where resource_id = pc_salesforce_id
;
SELECT pvpp.partner_id
FROM hz_parties PARTNER, hz_relationships HZR_PART_CONT, hz_org_contacts ORG_CONTACT,
hz_contact_points hcp, pv_partner_profiles pvpp, jtf_rs_resource_extns res
WHERE
PARTNER.PARTY_ID = pvpp.partner_party_id and
PARTNER.party_type = 'ORGANIZATION' AND
HZR_PART_CONT.object_id = PARTNER.PARTY_ID AND
HZR_PART_CONT.relationship_type = 'EMPLOYMENT' AND
HZR_PART_CONT.subject_table_name = 'HZ_PARTIES' AND
HZR_PART_CONT.object_table_name = 'HZ_PARTIES' AND
HZR_PART_CONT.PARTY_ID = res.source_id and
res.resource_id = pc_salesforce_id and
HZR_PART_CONT.relationship_id = ORG_CONTACT.party_relationship_id AND
hcp.owner_table_id(+) = HZR_PART_CONT.PARTY_ID AND
hcp.CONTACT_POINT_TYPE(+) = 'PHONE' AND
hcp.owner_table_name(+) = 'HZ_PARTIES' and
hcp.primary_flag(+) ='Y'
;
select 'CM_OR_REP'
from as_accesses_all
where access_id = pc_access_id
and partner_customer_id is null
and partner_cont_party_id is null;
select 'Access Type: '||decode(p_access_type, 1, 'CM', 2, 'PT', 3, 'PT ORG') ||
' Access Action: '||decode(p_access_action, 1, 'ADD', 2, 'REMOVE') into l_debug_string from dual;
select decode(p_access_type, pv_assignment_pub.G_CM_ACCESS, 'CM',
pv_assignment_pub.G_PT_ACCESS, 'PT',
pv_assignment_pub.G_PT_ORG_ACCESS, 'PT_ORG',
'UNKNOWN') into l_temp from dual;
DELETE FROM as_accesses_all acc
WHERE access_id = l_access_id_tbl(i) AND
salesforce_id NOT IN (l_am_rs_id, l_ld_owner_rs_id);
DELETE FROM as_sales_credits
WHERE lead_id = p_lead_id AND
salesforce_id = p_resource_id AND
credit_type_id = 2;
DELETE FROM as_accesses_all acc
WHERE access_id = l_access_id_tbl(i) AND
salesforce_id NOT IN (l_am_rs_id, l_ld_owner_rs_id);
DELETE FROM as_accesses_all acc
WHERE access_id = l_access_id_tbl(i) AND
salesforce_id NOT IN (l_am_rs_id, l_ld_owner_rs_id);
update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y', team_leader_flag = 'Y', open_flag = l_open_flag
where access_id = l_access_id_tbl(i);
update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y', team_leader_flag = 'Y', open_flag = l_open_flag
where access_id = l_access_id_tbl(i);
update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y'
--, team_leader_flag = 'Y'
where access_id = l_access_id_tbl(i) and salesforce_id = l_pt_resource_id;
update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y', team_leader_flag = 'N', open_flag = l_open_flag
where access_id = l_access_id_tbl(i);
update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y'
--, team_leader_flag = 'N'
where access_id = l_access_id_tbl(i) and salesforce_id = l_pt_resource_id;
update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y', open_flag = l_open_flag, team_leader_flag = 'Y'
where access_id = l_access_id_tbl(i);
--For exisitng contatcs, we do nto need to update the team leader flag.
-- We need to leave it the way it was.
-- THats why we are not updating team_leader_flag
update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y', open_flag = l_open_flag
--,team_leader_flag = 'Y'
where access_id = l_access_id_tbl(i);
update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y', team_leader_flag = 'N', open_flag = l_open_flag
where access_id = l_access_id_tbl(i);
update as_accesses_all set prm_keep_flag = 'Y', freeze_flag = 'Y'
--, team_leader_flag = 'N'
where access_id = l_access_id_tbl(i) and salesforce_id = l_pt_resource_id;
select as_accesses_s.nextval into l_sales_team_rec.Access_Id from dual;
l_sales_team_rec.Last_Update_Date := SYSDATE;
l_sales_team_rec.Last_Updated_By := FND_GLOBAL.User_Id;
l_sales_team_rec.Last_Update_Login := FND_GLOBAL.Conc_Login_Id;
insert into as_accesses_all (
ACCESS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ACCESS_TYPE,
FREEZE_FLAG,
REASSIGN_FLAG,
TEAM_LEADER_FLAG,
CUSTOMER_ID,
ADDRESS_ID,
SALESFORCE_ID,
PERSON_ID,
PARTNER_CUSTOMER_ID,
PARTNER_ADDRESS_ID,
LEAD_ID,
FREEZE_DATE,
SALESFORCE_ROLE_CODE,
SALESFORCE_RELATIONSHIP_CODE,
SALES_GROUP_ID,
INTERNAL_UPDATE_ACCESS,
SALES_LEAD_ID,
PARTNER_CONT_PARTY_ID,
OWNER_FLAG,
CREATED_BY_TAP_FLAG,
PRM_KEEP_FLAG,
OPEN_FLAG)
values (
l_sales_team_rec.Access_id,
l_sales_team_rec.Last_Update_Date,
l_sales_team_rec.Last_Updated_By,
l_sales_team_rec.Creation_Date,
l_sales_team_rec.Created_By,
l_sales_team_rec.Last_Update_Login,
'X',
l_sales_team_rec.Freeze_Flag,
l_sales_team_rec.Reassign_Flag,
l_sales_team_rec.Team_Leader_Flag,
l_sales_team_rec.Customer_Id,
l_sales_team_rec.Address_Id,
l_sales_team_rec.Salesforce_id,
l_sales_team_rec.Person_Id,
l_sales_team_rec.Partner_Customer_id,
l_sales_team_rec.Partner_Address_id,
l_sales_team_rec.lead_id,
l_sales_team_rec.Freeze_Date,
l_sales_team_rec.Salesforce_Role_Code,
l_sales_team_rec.Salesforce_Relationship_code,
l_sales_team_rec.Sales_group_id,
1,
l_sales_team_rec.Sales_lead_id,
l_sales_team_rec.Partner_Cont_Party_Id,
l_sales_team_rec.owner_flag,
l_sales_team_rec.created_by_tap_flag,
'Y',
l_open_flag
);
end UpdateAccess;
select wl.wf_item_type, wl.wf_item_key, wl.routing_status, wl.wf_status
into x_itemType, x_itemKey, x_routing_status, x_wf_status
from pv_lead_workflows wl
where wl.lead_id = p_lead_id
and wl.entity = p_entity
and wl.latest_routing_flag = 'Y';
SELECT DECODE(COUNT(*),
0,
null,
1,
TO_CHAR(MAX(grp.group_id)),
FND_PROFILE.VALUE_SPECIFIC('ASF_DEFAULT_GROUP_ROLE',
MAX(RES.user_id))) salesgroup_id
FROM JTF_RS_GROUP_MEMBERS mem,
JTF_RS_ROLE_RELATIONS rrel,
JTF_RS_ROLES_B role,
JTF_RS_GROUP_USAGES u,
JTF_RS_GROUPS_B grp,
JTF_RS_RESOURCE_EXTNS RES
WHERE mem.group_member_id = rrel.role_resource_id AND
rrel.role_resource_type = 'RS_GROUP_MEMBER' AND
rrel.role_id = role.role_id AND
role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM') AND
mem.delete_flag <> 'Y' AND
rrel.delete_flag <> 'Y' AND
sysdate BETWEEN rrel.start_date_active AND
NVL(rrel.end_date_active, SYSDATE) AND
mem.group_id = u.group_id AND
u.usage in ('SALES','PRM') AND
mem.group_id = grp.group_id AND
sysdate BETWEEN grp.start_date_active AND
NVL(grp.end_date_active,sysdate) AND
mem.resource_id = RES.resource_id AND
RES.resource_id = p_resource_id;
select error_message , error_stack
from wf_item_activity_statuses
where item_type = pc_itemtype
and item_key in
(select item_key from wf_items
start with item_type = pc_itemtype and item_key = pc_itemkey
connect by parent_item_key = prior item_key and parent_item_type = pc_itemtype)
and error_message is not null and error_name <> 'WFMLRSND_FAILED';