[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT b.opp_open_status_flag
FROM as_leads_all a,
as_statuses_b b
WHERE a.lead_id = p_lead_id AND
a.status = b.status_code AND
b.opp_flag = 'Y';
select meaning from pv_lookups
where lookup_type = 'PV_ASSIGNMENT_TYPE'
and lookup_code = pc_assignment_type;
select pt.party_name
from hz_relationships pr,
hz_organization_profiles op,
hz_parties pt
where pr.party_id = pc_partner_id
and pr.subject_table_name = 'HZ_PARTIES'
and pr.object_table_name = 'HZ_PARTIES'
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 pr.subject_id = pt.party_id
and pt.status in ('A', 'I');
select ld.customer_id,
ld.address_id,
ld.lead_number,
ld.description,
ld.total_amount||' '||ld.currency_code,
pt.party_name,
lc.lead_contact_id
from as_leads_all ld,
hz_parties pt,
as_lead_contacts lc
where ld.lead_id = pc_lead_id
and ld.customer_id = pt.party_id (+)
and ld.lead_id = lc.lead_id (+) for update of ld.lead_id;
select extn.category,
extn.source_id,
fuser.user_id
from fnd_user fuser,
jtf_rs_resource_extns extn
where fuser.user_name = pc_username
and fuser.user_id = extn.user_id;
select otl.name vendor_name
from hr_all_organization_units o,
hr_all_organization_units_tl otl,
per_all_people_f p
where o.organization_id = otl.organization_id
and otl.language = userenv('lang')
and o.organization_id = p.business_group_id
and p.person_id = pc_user_source_id;
select emp.object_id pt_org_id,
hp.party_name,
prof.partner_id
from hz_relationships emp,
pv_partner_profiles prof,
hz_parties hp
where emp.party_id = pc_user_source_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.status in ('A', 'I')
and emp.object_id = prof.partner_party_id
and emp.object_id = hp.party_id
and hp.status in ('A', 'I');
select lead_workflow_id
from pv_lead_workflows
where wf_item_type = 'PVASGNMT'
and wf_item_key = pc_item_key;
select partner_customer_id, prm_keep_flag
from as_accesses_all
where lead_id = pc_lead_id;
select 1 num
from pv_partner_profiles pvpp
where pvpp.partner_id = pc_partner_id
and nvl(pvpp.status,'I') <> 'A';
select PT_ORG.party_name
from
pv_partner_accesses PT_ACCESS,
pv_partner_profiles PT_PROF,
hz_parties PT_ORG
where
PT_ACCESS.partner_id = pc_partner_id
and PT_ACCESS.partner_id = PT_PROF.partner_id
and PT_PROF.status = 'A'
and PT_PROF.partner_party_id = PT_ORG.party_id
and PT_ORG.status in ('A', 'I')
and PT_ACCESS.vad_partner_id = pc_vad_id;
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE source_id = pc_partner_id
AND category = 'PARTNER';
fnd_message.Set_token('TEXT', 'Calling pv_assignment_pvt.update_routing_stage' );
pv_assignment_pvt.update_routing_stage (
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_itemType => l_itemtype,
p_itemKey => l_itemKey,
p_routing_stage => pv_assignment_pub.g_r_status_active,
p_active_but_open_flag => 'N',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'Highest ranked partner selected: ' || l_partner_id_tbl(l_highest_rank_pt_row));
update as_accesses_all set prm_keep_flag = 'Y'
where partner_customer_id = l_access_pt_id
and lead_id = p_lead_id;
fnd_message.Set_token('TEXT', 'Navigating through partner id table and call pv_assign_util_pvt.updateaccess' );
fnd_message.Set_token('TEXT', 'CAlling pv_assign_util_pvt.updateaccess for resource_id:' || l_resource_id);
pv_assign_util_pvt.UpdateAccess(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_itemtype => l_itemtype,
p_itemkey => l_itemKey,
p_current_username => p_creating_username,
p_lead_id => p_lead_id,
p_customer_id => l_customer_id,
p_address_id => l_address_id,
p_access_action => pv_assignment_pub.G_ADD_ACCESS,
p_resource_id => l_resource_id,
p_access_type => pv_assignment_pub.G_PT_ORG_ACCESS,
x_access_id => l_access_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE as_accesses_all
SET prm_keep_flag = 'Y'
WHERE partner_customer_id = l_access_pt_id
AND lead_id = p_lead_id;
l_lead_workflow_rec.last_updated_by := l_user_id;
fnd_message.Set_token('TEXT', 'Update as_leads_all table with auto_assignment_type = PRM, prm_assignment_type = p_assignment_type' );
update as_leads_all
set auto_assignment_type = 'PRM', prm_assignment_type = p_assignment_type
where lead_id = p_lead_id;
l_rs_details_tbl.delete; -- since we are using NOCOPY, need to
l_assignment_rec.related_party_access_code := g_assign_access_update;
l_assignment_rec.partner_access_code := g_assign_access_update;
fnd_message.Set_token('TEXT', 'Calling pv_assig_util_pvt.update access for CMs resource id:' || l_party_notify_rec_tbl.resource_id(i) );
pv_assign_util_pvt.updateAccess (
p_api_version_number => l_api_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_itemtype => l_itemType,
p_itemkey => l_itemKey,
p_current_username => p_creating_username,
p_lead_id => p_lead_id,
p_customer_id => l_customer_id,
p_address_id => l_address_id,
p_access_action => pv_assignment_pub.G_ADD_ACCESS,
p_resource_id => l_party_notify_rec_tbl.resource_id(i),
p_access_type => pv_assignment_pub.G_CM_ACCESS,
x_access_id => l_temp_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
select routing_type from pv_lead_workflows
where lead_id = pc_lead_id and entity = pc_entity and latest_routing_flag = 'Y';
select a.wf_item_type
, a.wf_item_key
, a.routing_status
, a.entity
, b.lead_assignment_id
, b.assign_sequence
, b.partner_id
, b.status
, c.rowid
, c.resource_id
, c.decision_maker_flag
, c.notification_type
, c.user_id
from pv_lead_workflows a, pv_lead_assignments b, pv_party_notifications c, fnd_user usr
where a.lead_id = pc_lead_id
and a.wf_status = g_wf_status_open
and a.wf_item_type = b.wf_item_type
and a.wf_item_key = b.wf_item_key
and b.lead_assignment_id = c.lead_assignment_id
and c.user_id = usr.user_id
and usr.user_name = pc_username;
select status
from pv_lead_assignments
where wf_item_type = pc_itemtype
and wf_item_key = pc_itemkey
and status <> g_la_status_pt_created;
fnd_message.Set_Token('TEXT', 'pv_Assignment_pvt.update_party_response' );
pv_assignment_pvt.update_party_response (
p_api_version_number => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,P_rowid => l_notify_rowid
,p_lead_assignment_id => l_assignment_id
,p_party_resource_id => l_cm_rs_id
,p_response => p_statusTbl(i)
,p_reason_code => NULL
,p_rank => p_rank_Tbl(i)
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status);
fnd_message.Set_Token('TEXT', 'Calling pv_assignment_pvt.UpdateAssignment' );
pv_assignment_pvt.UpdateAssignment (
p_api_version_number => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_action => pv_assignment_pub.g_asgn_action_status_update
,p_lead_assignment_id => l_assignment_id
,p_status_date => sysdate
,p_status => l_response
,p_reason_code => NULL
,p_rank => p_rank_Tbl(i)
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status);
l_rs_details_tbl.delete;
l_assignment_rec.related_party_access_code := g_assign_access_update;
l_party_notify_rec_tbl.WF_ITEM_TYPE.delete;
l_party_notify_rec_tbl.WF_ITEM_KEY.delete;
l_party_notify_rec_tbl.LEAD_ASSIGNMENT_ID.delete;
l_party_notify_rec_tbl.NOTIFICATION_TYPE.delete;
l_party_notify_rec_tbl.RESOURCE_ID.delete;
l_party_notify_rec_tbl.USER_ID.delete;
l_party_notify_rec_tbl.USER_NAME.delete;
l_party_notify_rec_tbl.RESOURCE_RESPONSE.delete;
l_party_notify_rec_tbl.RESPONSE_DATE.delete;
l_party_notify_rec_tbl.DECISION_MAKER_FLAG.delete;
select lookup_code from pv_lookups
where lookup_type = pc_lookup_type
and lookup_code = pc_reason_code;
select pt.party_name
from hz_relationships pr,
hz_organization_profiles op,
hz_parties pt
where pr.party_id = pc_partner_id
and pr.subject_table_name = 'HZ_PARTIES'
and pr.object_table_name = 'HZ_PARTIES'
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 pr.subject_id = pt.party_id
and pt.status in ('A', 'I');
select a.wf_item_type, a.wf_item_key, a.routing_status, a.wf_status,
b.lead_assignment_id, b.status, b.assign_sequence,
c.rowid, c.resource_id, c.decision_maker_flag, c.user_id
from pv_lead_workflows a, pv_lead_assignments b, pv_party_notifications c, fnd_user usr
where a.lead_id = pc_lead_id
and a.entity = pc_entity
and a.wf_item_type = b.wf_item_type
and a.wf_item_key = b.wf_item_key
and a.latest_routing_flag = 'Y'
and b.partner_id = pc_partner_id
and b.lead_assignment_id = c.lead_assignment_id
and c.user_id = usr.user_id
and usr.user_name = pc_username
and c.notification_type = pc_notify_type;
select rowid
from pv_lead_assignments
where wf_item_type = pc_itemtype
and wf_item_key = pc_itemkey
and status in (g_la_status_cm_timeout,
g_la_status_cm_bypassed,
g_la_status_cm_approved);
select rowid
from pv_lead_assignments
where wf_item_type = pc_itemtype
and wf_item_key = pc_itemkey
and status in (g_la_status_pt_approved, g_la_status_cm_app_for_pt) and rownum < 2;
select usr.user_name, pn.resource_id
from pv_lead_assignments la,
pv_party_notifications pn,
fnd_user usr
where la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_itemkey
and la.partner_id = pc_partner_id
and la.lead_assignment_id = pn.lead_assignment_id
and pn.notification_type = pc_notify_type
and pn.user_id = usr.user_id;
select usr.user_name, pn.resource_id
from pv_lead_assignments la,
pv_party_notifications pn,
fnd_user usr
where la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_itemkey
and la.partner_id = pc_partner_id
and la.lead_assignment_id = pn.lead_assignment_id
and pn.notification_type = pc_notify_type
and pn.user_id = usr.user_id
and not exists
(select 1
from pv_lead_assignments la2,
pv_party_notifications pn2
where la2.wf_item_type = pc_itemtype
and la2.wf_item_key = pc_itemkey
and la2.partner_id <> la.partner_id
and la2.status in (g_la_status_cm_timeout,
g_la_status_cm_bypassed,
g_la_status_cm_approved,
g_la_status_cm_app_for_pt,
g_la_status_pt_approved)
and la2.lead_assignment_id = pn2.lead_assignment_id
and pn2.notification_type = pc_notify_type
and pn2.user_id = pn.user_id );
select b.resource_id partner_org_rs_id
from pv_lead_assignments la,
jtf_rs_resource_extns b
where
la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_itemkey
and la.status = pv_assignment_pub.g_la_status_pt_rejected
and la.partner_id = b.source_id
and b.category = 'PARTNER'
and sysdate between b.start_date_active and nvl(b.end_date_active,sysdate);
FOR x IN (SELECT 1
FROM pv_lead_assignments
WHERE lead_assignment_id = l_assignment_id
FOR UPDATE NOWAIT)
LOOP
null;
fnd_message.Set_Token('TEXT', 'Calling pv_assignment_pvt.UpdateAssignment');
pv_assignment_pvt.UpdateAssignment (
p_api_version_number => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_action => g_asgn_action_status_update
,p_lead_assignment_id => l_assignment_id
,p_status_date => sysdate
,p_status => p_pt_response
,p_reason_code => p_reason_code
,p_rank => NULL
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status);
fnd_message.Set_Token('TEXT', 'Calling pv_assignment_pvt.update_party_response');
pv_assignment_pvt.update_party_response (
p_api_version_number => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,P_rowid => l_notify_rowid
,p_lead_assignment_id => l_assignment_id
,p_party_resource_id => l_responder_rs_id
,p_response => p_pt_response
,p_reason_code => p_reason_code
,p_rank => NULL
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status);
fnd_message.Set_Token('TEXT', 'Calling pv_assignment_pvt.update_routing_stage');
pv_assignment_pvt.update_routing_stage (
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_itemType => l_itemtype,
p_itemKey => l_itemKey,
p_routing_stage => pv_assignment_pub.g_r_status_active,
p_active_but_open_flag => 'Y',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
fnd_message.Set_Token('TEXT', ' calling pv_assign_util_pvt.updateAccess for user name:' || l_username_tab(i));
pv_assign_util_pvt.updateAccess(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_itemtype => l_itemType,
p_itemkey => l_itemKey,
p_current_username => l_username_tab(i),
p_lead_id => p_lead_id,
p_customer_id => null,
p_address_id => null,
p_access_action => G_REMOVE_ACCESS,
p_resource_id => l_resource_id_tab(i),
p_access_type => g_pt_access,
x_access_id => l_access_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pv_assign_util_pvt.updateaccess(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_itemtype => l_itemType,
p_itemkey => l_itemKey,
p_current_username => NULL,
p_lead_id => p_lead_id,
p_customer_id => null,
p_address_id => null,
p_access_action => G_REMOVE_ACCESS,
p_resource_id => l_partner_org_rs_id,
p_access_type => G_PT_ORG_ACCESS,
x_access_id => l_access_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
l_username_tab.delete;
l_resource_id_tab.delete;
pv_assign_util_pvt.updateAccess(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_itemtype => l_itemType,
p_itemkey => l_itemKey,
p_current_username => l_username_tab(i),
p_lead_id => p_lead_id,
p_customer_id => null,
p_address_id => null,
p_access_action => G_REMOVE_ACCESS,
p_resource_id => l_resource_id_tab(i),
p_access_type => g_cm_access,
x_access_id => l_access_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
select a.wf_item_type,
a.wf_item_key,
a.routing_status,
a.routing_type,
b.lead_number,
b.description,
b.total_amount||' '||b.currency_code,
c.party_name
from pv_lead_workflows a
, as_leads_all b
, hz_parties c
where a.lead_id = pc_lead_id
and b.customer_id = c.party_id
and a.latest_routing_flag = 'Y'
and a.lead_id = b.lead_id
and c.status in ('A', 'I')
and a.entity = pc_entity;
select b.lead_assignment_id, b.assign_sequence, b.status
from pv_lead_workflows a, pv_lead_assignments b
where a.lead_id = pc_lead_id and a.latest_routing_flag = 'Y' and a.entity = pc_entity
and a.wf_item_type = b.wf_item_type
and a.wf_item_key = b.wf_item_key;
SELECT la.lead_assignment_id
from pv_lead_assignments la
where la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_itemkey
and la.status not in (g_la_status_cm_rejected,
g_la_status_pt_rejected,
g_la_status_pt_timeout,
g_la_status_lost_chance,
g_la_status_pt_abandoned
);
SELECT meaning
FROM pv_lookups
WHERE lookup_type = 'PV_ASSIGNMENT_TYPE'
AND lookup_code = pc_assignment_type;
SELECT extn.category
, extn.source_id
, pwf.created_by
FROM pv_lead_workflows pwf
, jtf_rs_resource_extns extn
WHERE pwf.created_by = extn.user_id
AND pwf.entity = 'OPPORTUNITY'
AND pwf.latest_routing_flag = 'Y'
AND pwf.lead_id = pc_lead_id;
select otl.name vendor_name
from hr_all_organization_units o,
hr_all_organization_units_tl otl,
per_all_people_f p
where o.organization_id = otl.organization_id
and otl.language = userenv('lang')
and o.organization_id = p.business_group_id
and p.person_id = pc_source_id;
select hp.party_name
from hz_relationships emp,hz_parties hp
where emp.party_id = pc_source_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.status in ('A', 'I')
and emp.object_id = hp.party_id
and hp.status in ('A', 'I');
fnd_message.Set_Token('TEXT', 'For Joint Selling and Broadcast the status will not be updated to withdrawn ' ||
'for the partners who are not interested in the opp');
fnd_message.Set_Token('TEXT','Calling pv_assignment_pvt.UpdateAssignment for assignment id:' || l_assignment_id_tbl(i));
pv_assignment_pvt.UpdateAssignment (
p_api_version_number => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_action => g_asgn_action_status_update
,p_lead_assignment_id => l_assignment_id_tbl(i)
,p_status_date => sysdate
,p_status => l_assignment_status
,p_reason_code => NULL
,p_rank => NULL
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status);
fnd_message.Set_Token('TEXT','Calling pv_assignment_pvt.UpdateAssignment for assignment id:' || l_assignment_id_tbl(i));
pv_assignment_pvt.UpdateAssignment (
p_api_version_number => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_action => g_asgn_action_status_update
,p_lead_assignment_id => l_assignment_id_tbl(i) -- l_lead_assignment_id
,p_status_date => sysdate
,p_status => g_la_status_active_withdrawn
,p_reason_code => NULL
,p_rank => NULL
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status);
fnd_message.Set_Token('TEXT','Update the routing stage by Calling pv_assignment_pvt.update_routing_stage' );
pv_assignment_pvt.update_routing_stage (
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_itemType => l_itemtype,
p_itemKey => l_itemKey,
p_routing_stage => pv_assignment_pub.g_r_status_withdrawn,
p_active_but_open_flag => 'N',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
select 1
from pv_lead_assignments
where wf_item_type = pc_itemtype
and wf_item_key = pc_itemkey
and status in (g_la_status_pt_approved, g_la_status_cm_app_for_pt);
select la.partner_id,
la.lead_assignment_id,
fu.user_id,
re.resource_id
from fnd_user fu,
jtf_rs_resource_extns re,
hz_relationships emp,
pv_partner_profiles pt,
pv_lead_assignments la
where fu.user_name = pc_user_name
and fu.user_id = re.user_id
and re.category = 'PARTY'
and re.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.status in ('A', 'I')
and emp.object_id = pt.partner_party_id
and pt.partner_id = la.partner_id
and la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_itemkey
and la.status in (g_la_status_pt_approved, g_la_status_cm_app_for_pt);
select pt.party_name
from hz_relationships pr,
hz_organization_profiles op,
hz_parties pt
where pr.party_id = pc_partner_id
and pr.subject_table_name = 'HZ_PARTIES'
and pr.object_table_name = 'HZ_PARTIES'
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 pr.subject_id = pt.party_id
and pt.status in ('A', 'I');
SELECT a.prm_assignment_type
, a.lead_number
, a.description
, a.total_amount||' '||a.currency_code
, b.party_name
FROM as_leads_all a, hz_parties b
WHERE a.lead_id = pc_lead_id
AND a.customer_id = b.party_id
and b.status in ('A', 'I');
SELECT meaning
FROM pv_lookups
WHERE lookup_type = 'PV_ASSIGNMENT_TYPE'
AND lookup_code = pc_assignment_type;
SELECT extn.category
, extn.source_id
, pwf.created_by
FROM pv_lead_workflows pwf
, jtf_rs_resource_extns extn
WHERE pwf.created_by = extn.user_id
AND pwf.entity = 'OPPORTUNITY'
AND pwf.latest_routing_flag = 'Y'
AND pwf.lead_id = pc_lead_id;
select bg.name
from per_people_x px, per_business_groups bg
where px.person_id = pc_source_id
and px.business_group_id = bg.business_group_id; */
select otl.name vendor_name
from hr_all_organization_units o,
hr_all_organization_units_tl otl,
per_all_people_f p
where o.organization_id = otl.organization_id
and otl.language = userenv('lang')
and o.organization_id = p.business_group_id
and p.person_id = pc_source_id;
select hp.party_name
from hz_relationships emp,hz_parties hp
where emp.party_id = pc_source_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.status in ('A', 'I')
and emp.object_id = hp.party_id
and hp.status in ('A', 'I');
fnd_message.Set_Token('TEXT', 'Updating assignment by calling pv_assignment_pvt.UpdateAssignment for assignment id:' || l_lead_assignment_id);
pv_assignment_pvt.UpdateAssignment (
p_api_version_number => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_action => g_asgn_action_status_update
,p_lead_assignment_id => l_lead_assignment_id
,p_status_date => sysdate
,p_status => g_la_status_pt_abandoned
,p_reason_code => p_reason_code
,p_rank => NULL
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status);
fnd_message.Set_Token('TEXT', 'All partners have abandoned. Update pv_lead_workflow routing to ABANDONED');
fnd_message.Set_Token('TEXT', 'Updating the routing stage by calling pv_assignment_pvt.update_routing_stage ');
pv_assignment_pvt.update_routing_stage (
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_itemType => l_itemtype,
p_itemKey => l_itemKey,
p_routing_stage => pv_assignment_pub.g_r_status_abandoned,
p_active_but_open_flag => 'N',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT meaning
INTO l_action_reason
FROM pv_lookups
WHERE lookup_type = 'PV_REASON_CODES'
AND lookup_code = p_reason_code;