[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct usr.user_name
from pv_lead_assignments aa, pv_party_notifications bb, fnd_user usr
where bb.wf_item_key = pc_itemKey
and bb.wf_item_type = pc_itemType
and bb.notification_type = pc_notify_type
and bb.lead_assignment_id = aa.lead_assignment_id
and aa.status = pc_assign_status
and bb.user_id = usr.user_id;
select name from wf_local_roles
where name = pc_rolename;
SELECT PV_LEAD_WORKFLOWS_S.nextval INTO l_itemKey
FROM dual;
select hp.party_name
from hz_relationships porg,
hz_parties hp,
hz_organization_profiles hzop,
pv_partner_profiles pvpp
where porg.party_id = pc_partner_id
and porg.subject_table_name = 'HZ_PARTIES'
and porg.object_table_name = 'HZ_PARTIES'
and porg.relationship_code = 'PARTNER_OF'
and porg.relationship_type = 'PARTNER'
and porg.status = 'A'
and PORG.start_date <= SYSDATE
and nvl(PORG.end_date, SYSDATE) >= SYSDATE
and porg.object_id = hp.party_id
and hp.status = 'A'
and hp.party_type = 'ORGANIZATION'
AND HZOP.party_id = hp.party_id
AND HZOP.effective_end_date is null
AND HZOP.internal_flag = 'Y'
AND PVPP.partner_id = PORG.party_id
AND PVPP.SALES_PARTNER_FLAG = 'Y';
l_access_code_update CONSTANT VARCHAR2(10) := 'UPDATE';
SELECT max(res.category), 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 = pc_resource_id;
SELECT ld.customer_id, ld.address_id
, pt.party_name, ld.currency_code
FROM as_leads_all ld, hz_parties pt
WHERE ld.customer_id = pt.party_id
AND ld.lead_id = pc_lead_id;
SELECT SUM(NVL(total_amount,0))
FROM as_lead_lines
WHERE lead_id = pc_lead_id;
SELECT address_id, resource_id
FROM jtf_rs_resource_extns
WHERE category = 'PARTNER'
and sysdate between start_date_active and nvl(end_date_active,sysdate)
AND source_id = pc_party_relation_id; */
SELECT b.address_id, a.resource_id
FROM jtf_rs_resource_extns a,
as_party_addresses_v b,
pv_partner_profiles c
WHERE a.category = 'PARTNER'
AND sysdate between a.start_date_active and nvl(a.end_date_active,sysdate)
AND a.source_id = pc_party_relation_id
AND a.source_id = c.partner_id
AND c.partner_party_id = b.party_id
AND b.primary_address_flag = 'Y';
l_assignment_rec.partner_access_code := l_access_code_update;
for c_check in (select 1 from as_accesses_all where
salesforce_id <> l_rs_details_tbl(i).resource_id and lead_id = l_lead_id)
loop
-- The returned table has all CM from Vendor and VAD.
-- Since VAD CM does not have person id, we need to populate partner_cont_party_id
if l_rs_details_tbl(i).person_type = pv_assignment_pub.g_resource_employee then
l_sales_team_rec.partner_cont_party_id := null;
Update AS_LEADS_ALL
SET PRM_ASSIGNMENT_TYPE = 'SINGLE',
AUTO_ASSIGNMENT_TYPE = 'PRM'
WHERE lead_id = l_lead_id;
Update AS_LEADS_ALL
SET AUTO_ASSIGNMENT_TYPE = 'PRM'
WHERE lead_id = l_lead_id;
SELECT nvl(total_amount,0), currency_code
FROM as_leads_all
WHERE lead_id = pc_lead_id;
procedure NOTIFY_ON_UPDATE_OPPTY_JBES (
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_id IN NUMBER,
p_status IN VARCHAR2,
p_lead_name IN VARCHAR2,
p_customer_id IN NUMBER,
p_total_amount IN NUMBER,
p_salesforce_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(100) := 'NOTIFY_ON_UPDATE_OPPTY_JBES';
SELECT party_name
FROM hz_parties
WHERE party_id = pc_party_id;
Notify_Party_On_Update_Oppty (
p_api_version_number => l_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_oppty_header_rec => l_opportunity_rec,
p_salesforce_id => p_salesforce_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END NOTIFY_ON_UPDATE_OPPTY_JBES;
procedure Notify_Party_On_Update_Oppty (
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_oppty_header_rec IN AS_OPPORTUNITY_PUB.header_rec_type,
p_salesforce_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(100) := 'Notify_Party_On_Update_Oppty';
SELECT pn.user_id, pn.resource_id, fu.user_name ,
decode(pn.notification_type, 'MATCHED_TO', 'CM', 'PT') user_type,
decode(pn.notification_type, 'MATCHED_TO', 0, pa.partner_id) partner_id
FROM pv_lead_workflows pw,
pv_lead_assignments pa,
pv_party_notifications pn,
jtf_rs_resource_extns extn,
as_accesses_all asac,
fnd_user fu
WHERE pw.wf_item_type = pa.wf_item_type
and pw.wf_item_key = pa.wf_item_key
AND pa.lead_assignment_id = pn.lead_assignment_id
AND pw.routing_status = 'ACTIVE'
AND pw.latest_routing_flag = 'Y'
AND pw.lead_id = pc_lead_id
AND ((pn.notification_type = 'MATCHED_TO' and 'Y' = pc_notify_cm_flag)
or (pn.notification_type = 'OFFERED_TO' and 'Y' = pc_notify_pt_flag))
AND pa.status IN ( 'PT_CREATED', 'PT_APPROVED' , 'CM_APP_FOR_PT' )
AND asac.salesforce_id = pn.resource_id
AND asac.lead_id = pw.lead_id
AND asac.sales_lead_id IS NULL
AND asac.customer_id IS NOT NULL
AND asac.salesforce_id = extn.resource_id
AND extn.user_id = fu.user_id
AND sysdate between extn.start_date_active and nvl(extn.end_date_active,sysdate)
AND sysdate between fu.start_date and nvl(fu.end_date,sysdate)
UNION
SELECT js.user_id, js.resource_id, fu.user_name,
decode(pw.created_by - js.user_id,0,'AM','OTHER') user_type, 0 partner_id
FROM as_accesses_all ac, jtf_rs_resource_extns js, fnd_user fu, pv_lead_workflows pw
WHERE (('Y' = pc_notify_ot_flag and pw.created_by <> js.user_id)
or ('Y' = pc_notify_am_flag and pw.created_by = js.user_id))
AND ac.lead_id = pc_lead_id
and ac.lead_id = pw.lead_id
and pw.entity = 'OPPORTUNITY'
AND pw.latest_routing_flag = 'Y'
AND ac.salesforce_id = js.resource_id
AND js.user_id = fu.user_id
AND ac.sales_lead_id IS NULL
AND ac.customer_id IS NOT NULL
and sysdate between js.start_date_active and nvl(js.end_date_active,sysdate)
AND sysdate between fu.start_date and nvl(fu.end_date,sysdate)
AND NOT EXISTS
(SELECT 1
FROM pv_lead_assignments pl, pv_party_notifications pv
WHERE pl.lead_assignment_id = pv.lead_assignment_id
AND pv.resource_id = ac.salesforce_id
and pv.user_id <> pw.created_by
AND pl.wf_item_type = pw.wf_item_type
AND pl.wf_item_key = pw.wf_item_key)
ORDER BY 4;
SELECT lead_workflow_id, wf_item_key, wf_item_type
FROM pv_lead_workflows pw
WHERE pw.routing_status = 'ACTIVE'
AND pw.latest_routing_flag = 'Y'
AND pw.lead_id = pc_lead_id;
SELECT nvl(notify_pt_flag,'N')
,nvl(notify_am_flag,'N')
,nvl(notify_cm_flag,'N')
,nvl(notify_others_flag,'N')
FROM pv_status_notifications
WHERE enabled_flag = 'Y'
AND status_type = 'OPPORTUNITY'
AND status_code = pc_status_code;
SELECT ld.customer_id, ld.address_id, pt.party_name,
nvl(ld.total_amount,0),ld.currency_code, ld.description
FROM as_leads_all ld, hz_parties pt
WHERE ld.customer_id = pt.party_id
AND ld.lead_id = pc_lead_id;
SELECT js.source_id, js.category, js.source_business_grp_name, fu.user_name
FROM fnd_user fu, jtf_rs_resource_extns js
WHERE fu.user_id = js.user_id
AND js.resource_id = pc_salesforce_id;
SELECT VENDOR.party_name
FROM hz_parties VENDOR,
hz_relationships PCONTACT,
pv_partner_profiles PVPP
WHERE PCONTACT.party_id = pc_party_id
AND PCONTACT.subject_table_name = 'HZ_PARTIES'
AND PCONTACT.object_table_name = 'HZ_PARTIES'
AND PCONTACT.RELATIONSHIP_TYPE = 'EMPLOYMENT'
AND PCONTACT.directional_flag = 'F'
AND PCONTACT.STATUS = 'A'
AND PCONTACT.start_date <= SYSDATE
AND nvl(PCONTACT.end_date, SYSDATE) >= SYSDATE
AND PVPP.partner_party_id = PCONTACT.object_id
AND VENDOR.party_id = PVPP.partner_party_id
AND VENDOR.PARTY_TYPE = 'ORGANIZATION'
AND VENDOR.status = 'A'
AND PVPP.SALES_PARTNER_FLAG = 'Y';
select pt.party_name, pvas.partner_id
from hz_parties pt,
pv_partner_profiles pvpp,
pv_lead_assignments pvas
where pvas.wf_item_type = pc_item_type
and pvas.wf_item_key = pc_item_key
and pvas.partner_id = pvpp.partner_id
and pvpp.partner_party_id = pt.party_id;
select decode(a.status_code, t.status, a.meaning, a.status_code),
decode(a.status_code, pc_status_code, a.meaning, a.status_code),
a.status_code, a.win_loss_indicator
from as_statuses_vl a,
(select status from as_leads_all
where lead_id = pc_lead_id) t
where a.enabled_flag = 'Y'
and a.opp_flag = 'Y'
and a.status_code in (t.status, pc_status_code);
END Notify_Party_On_Update_Oppty;
select nvl(b.indirect_channel_flag, 'N') indirect_channel_flag
from oe_lookups a, pv_channel_types b
where a.lookup_type = 'SALES_CHANNEL'
and a.lookup_code = l_channel_code
and a.lookup_type = b.channel_lookup_type (+)
and a.lookup_code = b.channel_lookup_code (+))
LOOP
l_indirect_channel_flag := x.indirect_channel_flag;
/* Call the Update Opportunity user hook. *********/
/***************************************************/
procedure Update_Opportunity_Pre (
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_oppty_header_rec IN AS_OPPORTUNITY_PUB.header_rec_type,
p_salesforce_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) := 'Update_Opportunity_Pre';
l_mode VARCHAR2(10) := 'UPDATE';
/** Notify Channel manager if the Opportunity is updated by Partner */
/** contact or VAD contact */
/***************************************************************************/
-- Initialize message list if p_init_msg_list is set to TRUE.
IF FND_API.to_Boolean( p_init_msg_list ) THEN
fnd_msg_pub.initialize;
Notify_Party_On_Update_Oppty (
p_api_version_number => l_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_oppty_header_rec => p_oppty_header_rec,
p_salesforce_id => p_salesforce_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
end Update_Opportunity_Pre;
select nvl(b.indirect_channel_flag, 'N')
from oe_lookups a, pv_channel_types b
where a.lookup_type = 'SALES_CHANNEL'
and a.lookup_code = pc_code
and a.lookup_type = b.channel_lookup_type (+)
and a.lookup_code = b.channel_lookup_code (+);
SELECT 'PARTNER_OF', PVPP.partner_id, PARTNER.party_name, peav.attr_value
FROM
hz_parties PARTNER,
hz_relationships CONTACT,
pv_partner_profiles PVPP,
pv_enty_attr_values peav
WHERE CONTACT.party_id = pc_party_id
AND CONTACT.subject_table_name = 'HZ_PARTIES'
AND CONTACT.object_table_name = 'HZ_PARTIES'
AND CONTACT.RELATIONSHIP_TYPE = 'EMPLOYMENT'
AND CONTACT.RELATIONSHIP_CODE = 'EMPLOYEE_OF'
AND CONTACT.directional_flag = 'F'
AND CONTACT.STATUS = 'A'
AND CONTACT.start_date <= SYSDATE
AND nvl(CONTACT.end_date, SYSDATE) >= SYSDATE
AND PVPP.partner_party_id = CONTACT.object_id
AND PARTNER.party_id = PVPP.partner_party_id
AND PARTNER.PARTY_TYPE = 'ORGANIZATION'
AND PARTNER.status = 'A'
AND peav.entity_id(+) = PVPP.partner_id
AND peav.entity(+) = 'PARTNER'
AND peav.attribute_id(+) = 3;
SELECT js.source_id, fu.user_name, js.category
INTO l_party_id, x_user_name, l_resource_category
FROM fnd_user fu, jtf_rs_resource_extns js
WHERE fu.user_id = js.user_id
AND js.resource_id = p_salesforce_id;