[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR C2 IS SELECT PV_OPPTY_ROUTING_LOGS_S.nextval FROM sys.dual;
SELECT business_group_id
FROM per_all_people_f a
, fnd_user b
WHERE b.user_id = pc_user_id
AND b.employee_id = a.person_id;
INSERT INTO pv_oppty_routing_logs
(
OPPTY_ROUTING_LOG_ID
, EVENT
, LEAD_ID
, LEAD_WORKFLOW_ID
, ROUTING_TYPE
, LATEST_ROUTING_FLAG
, BYPASS_CM_FLAG
, LEAD_ASSIGNMENT_ID
, EVENT_DATE
, VENDOR_USER_ID
, PT_CONTACT_USER_ID
, USER_RESPONSE
, REASON_CODE
, USER_TYPE
, VENDOR_BUSINESS_UNIT_ID
)
VALUES
(
l_routing_log_id
, P_oppty_routing_log_rec.event
, P_oppty_routing_log_rec.lead_id
, P_oppty_routing_log_rec.lead_workflow_id
, P_oppty_routing_log_rec.routing_type
, P_oppty_routing_log_rec.latest_routing_flag
, P_oppty_routing_log_rec.bypass_cm_flag
, P_oppty_routing_log_rec.lead_assignment_id
, P_oppty_routing_log_rec.event_date
, P_oppty_routing_log_rec.vendor_user_id
, P_oppty_routing_log_rec.pt_contact_user_id
, P_oppty_routing_log_rec.user_response
, P_oppty_routing_log_rec.reason_code
, P_oppty_routing_log_rec.user_type
, l_business_unit_id
);
CURSOR C2 IS SELECT PV_ASSIGNMENT_LOGS_S.nextval FROM sys.dual;
INSERT into pv_assignment_logs (
ASSIGNMENT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
LEAD_ASSIGNMENT_ID,
PARTNER_ID,
ASSIGN_SEQUENCE,
CM_ID,
LEAD_ID,
DURATION,
FROM_LEAD_STATUS,
TO_LEAD_STATUS,
STATUS,
STATUS_DATE,
WF_ITEM_TYPE,
WF_ITEM_KEY,
WF_PT_USER,
WF_CM_USER,
WORKFLOW_ID,
ERROR_TXT,
TRANS_TYPE,
STATUS_CHANGE_COMMENTS
) values (
l_assignment_log_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id,
1,
p_assignment_log_rec.LEAD_ASSIGNMENT_ID,
p_assignment_log_rec.PARTNER_ID,
p_assignment_log_rec.ASSIGN_SEQUENCE,
p_assignment_log_rec.CM_ID,
p_assignment_log_rec.LEAD_ID,
p_assignment_log_rec.DURATION,
p_assignment_log_rec.FROM_LEAD_STATUS,
p_assignment_log_rec.TO_LEAD_STATUS,
p_assignment_log_rec.STATUS,
p_assignment_log_rec.STATUS_DATE,
p_assignment_log_rec.WF_ITEM_TYPE,
p_assignment_log_rec.WF_ITEM_KEY,
p_assignment_log_rec.WF_PT_USER,
p_assignment_log_rec.WF_CM_USER,
p_assignment_log_rec.WORKFLOW_ID,
p_assignment_log_rec.ERROR_TXT,
p_assignment_log_rec.TRANS_TYPE,
p_assignment_log_rec.STATUS_CHANGE_COMMENTS
);
PROCEDURE update_party_response(
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_rowid IN ROWID,
p_lead_assignment_id IN NUMBER,
p_party_resource_id IN NUMBER,
p_response IN VARCHAR2,
p_reason_code IN VARCHAR2,
p_rank 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_party_response';
update pv_party_notifications
set resource_response = p_response,
response_date = sysdate,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
where rowid = p_rowid
returning lead_assignment_id, resource_id
into l_lead_assignment_id, l_party_resource_id;
fnd_message.SET_NAME('PV', 'Cannot find row to update');
fnd_message.SET_NAME('PV', 'Updated wrong row');
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 => p_lead_assignment_id
,p_status_date => sysdate
,p_status => l_response
,p_reason_code => p_reason_code
,p_rank => p_rank
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => x_return_status);
End update_party_response;
select c.notification_id,
b.party_notification_id
from pv_party_notifications b,
wf_item_activity_statuses d,
wf_notifications c,
fnd_user usr
where b.wf_item_type = pc_itemtype
and b.wf_item_key = pc_itemkey
and b.notification_type = pc_notify_type
and d.item_type = b.wf_item_type
and d.item_key = b.wf_item_key
and d.assigned_user = 'PV' || pc_notify_type || pc_itemkey || '+' || pc_partner_id
and b.user_id = usr.user_id
and usr.user_name = c.original_recipient
and c.context = pc_itemtype || ':' || pc_itemkey || ':' || d.process_activity;
update pv_party_notifications
set notification_id = l_notify_id_tbl(j),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
where party_notification_id = l_party_notify_id_tbl(j);
procedure UpdateAssignment (
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_action IN VARCHAR2
,p_lead_assignment_id IN number
,p_status_date IN DATE
,p_status IN VARCHAR2
,p_reason_code IN VARCHAR2
,p_rank IN NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2) is
l_api_name CONSTANT VARCHAR2(30) := 'UpdateAssignment';
SELECT
a.rowid,
a.object_version_number,
a.partner_id,
a.assign_sequence,
a.lead_id,
a.status,
a.reason_code ,
a.status_date,
a.wf_item_type,
a.wf_item_key,
a.partner_access_code,
a.related_party_access_code,
b.routing_status,
b.lead_workflow_id,
b.routing_type,
b.latest_routing_flag,
b.bypass_cm_ok_flag
FROM pv_lead_assignments a, pv_lead_workflows b
WHERE a.lead_assignment_id = pc_lead_assignment_id
AND a.wf_item_type = b.wf_item_type
AND a.wf_item_key = b.wf_item_key;
SELECT notification_type
FROM pv_party_notifications a
WHERE a.wf_item_key = pc_wf_item_key
AND a.wf_item_type = pc_wf_item_type
AND a.user_id = fnd_global.user_id
AND a.notification_type= pv_assignment_pub.g_notify_type_matched_to;
/*SELECT notification_type
FROM pv_party_notifications a, pv_assignment_logs c
WHERE a.user_id = c.created_by
AND a.wf_item_key = c.wf_item_key
AND a.wf_item_key = pc_wf_item_key
AND a.wf_item_type = pc_wf_item_type;
select pv_assign.lead_assignment_id
from hz_relationships EMP_TO_ORG,
hz_relationships ORG_TO_VEND,
hz_organization_profiles HZOP,
pv_lead_assignments PV_ASSIGN,
pv_lead_workflows PV_LEAD_WF,
jtf_rs_resource_extns LEAD_SOURCE,
pv_enty_attr_values PEAV
where PV_ASSIGN.lead_assignment_id = pc_lead_assignment_id
and PV_ASSIGN.wf_item_type = PV_LEAD_WF.wf_item_type
and PV_ASSIGN.wf_item_key = PV_LEAD_WF.wf_item_key
and PV_LEAD_WF.created_by = LEAD_SOURCE.user_id
and EMP_TO_ORG.party_id = LEAD_SOURCE.source_id
and EMP_TO_ORG.subject_table_name = 'HZ_PARTIES'
and EMP_TO_ORG.object_table_name = 'HZ_PARTIES'
and EMP_TO_ORG.directional_flag = 'F'
and EMP_TO_ORG.status in ('A', 'I')
and EMP_TO_ORG.relationship_code = 'EMPLOYEE_OF'
and EMP_TO_ORG.relationship_type = 'EMPLOYMENT'
and EMP_TO_ORG.object_id = ORG_TO_VEND.subject_id
and ORG_TO_VEND.subject_table_name = 'HZ_PARTIES'
and ORG_TO_VEND.object_table_name = 'HZ_PARTIES'
and ORG_TO_VEND.status in ('A', 'I')
and ORG_TO_VEND.relationship_type = 'PARTNER'
and ORG_TO_VEND.object_id = HZOP.party_id
and HZOP.internal_flag = 'Y'
and HZOP.effective_end_date is null
and ORG_TO_VEND.party_id = PV_ASSIGN.related_party_id
and PEAV.entity_id(+) = ORG_TO_VEND.party_id
and PEAV.entity(+) = 'PARTNER'
and PEAV.attribute_id(+) = 3
and PEAV.attr_value = 'VAD';
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;
if p_action = pv_assignment_pub.g_asgn_action_status_update then
fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
p_action not in (pv_assignment_pub.g_asgn_action_status_update,
pv_assignment_pub.g_asgn_action_move_to_log) then
fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
if p_action = pv_assignment_pub.g_asgn_action_status_update then
if l_routing_status in (pv_assignment_pub.g_r_status_active, pv_assignment_pub.g_r_status_offered) then
-- we are not doing this for matched status here because partners/related_party_id access do not
-- change until all CMs have approved/rejected or timedout
-- that's why we are doing it in set_offered_attributes API
if p_status in ( pv_assignment_pub.g_la_status_pt_rejected,
pv_assignment_pub.g_la_status_pt_timeout,
pv_assignment_pub.g_la_status_pt_abandoned,
pv_assignment_pub.g_la_status_offer_withdrawn,
pv_assignment_pub.g_la_status_lost_chance) then
OPEN lc_get_vad_assign(pc_lead_assignment_id => p_lead_assignment_id);
l_partner_access_code := pv_assignment_pub.g_assign_access_update;
l_related_party_access_code := pv_assignment_pub.g_assign_access_update;
update pv_lead_assignments
set status_date = p_status_date,
status = p_status,
reason_code = p_reason_code ,
assign_sequence = nvl(p_rank, assign_sequence),
partner_access_code = l_partner_access_code,
related_party_access_code = decode(nvl(related_party_id,-999), -999, null, l_related_party_access_code),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
where rowid = l_rowid;
fnd_message.SET_TOKEN('TEXT', 'Cannot find row to update');
delete from pv_lead_assignments where rowid = l_rowid;
fnd_message.SET_TOKEN('TEXT', 'Cannot find row to delete');
pv_leadlog_pvt.InsertAssignLogRow (
X_Rowid => l_rowid,
x_assignlog_ID => l_assignment_log_id,
p_Lead_assignment_ID => p_lead_assignment_ID,
p_Last_Updated_By => FND_GLOBAL.USER_ID,
p_Last_Update_Date => SYSDATE,
p_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
p_Created_By => FND_GLOBAL.USER_ID,
p_Creation_Date => SYSDATE,
p_Object_Version_Number => l_object_version_number,
p_lead_id => l_lead_id,
p_partner_id => l_partner_id,
p_assign_sequence => l_assign_sequence,
p_status_date => l_status_date,
p_status => l_status,
p_wf_item_type => l_wf_item_type,
p_wf_item_key => l_wf_item_key,
p_trans_type => NULL,
p_error_txt => NULL,
p_status_change_comments => NULL,
p_cm_id => NULL,
p_duration => NULL,
p_wf_pt_user => NULL,
p_wf_cm_user => NULL,
x_return_status => x_return_status);
end UpdateAssignment;
SELECT routing_status
FROM pv_lead_workflows
WHERE wf_item_type = pc_itemtype
AND wf_item_key = pc_itemkey;
select
a1.lead_id, d.user_name access_user, a2.resource_id
from
pv_lead_assignments a1,
pv_party_notifications a2,
jtf_rs_resource_extns b,
as_accesses_all c,
fnd_user d
where
a1.wf_item_type = pc_itemtype
and a1.wf_item_key = pc_itemkey
and a1.status in ( pv_assignment_pub.g_la_status_cm_rejected,
pv_assignment_pub.g_la_status_pt_rejected,
pv_assignment_pub.g_la_status_pt_timeout,
pv_assignment_pub.g_la_status_lost_chance,
pv_assignment_pub.g_la_status_match_withdrawn,
pv_assignment_pub.g_la_status_offer_withdrawn)
and not exists
(select 1 from pv_lead_assignments la , pv_party_notifications pn
where la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_itemkey
and la.status in (pv_assignment_pub.g_la_status_cm_approved,
pv_assignment_pub.g_la_status_pt_approved,
pv_assignment_pub.g_la_status_pt_created,
pv_assignment_pub.g_la_status_cm_app_for_pt,
pv_assignment_pub.g_la_status_cm_timeout)
and la.lead_assignment_id = pn.lead_assignment_id
and pn.resource_id = a2.resource_id)
and a1.lead_assignment_id = a2.lead_assignment_id
and a2.resource_id = b.resource_id
and b.user_id = d.user_id
and a2.resource_id = c.salesforce_id
and a1.lead_id = c.lead_id;
select
a.lead_id, 'PARTNER', b.resource_id
from
pv_lead_assignments a,
jtf_rs_resource_extns b,
as_accesses_all c
where
a.wf_item_type = pc_itemtype
and a.wf_item_key = pc_itemkey
and a.status in ( pv_assignment_pub.g_la_status_cm_rejected,
pv_assignment_pub.g_la_status_pt_rejected,
pv_assignment_pub.g_la_status_pt_timeout,
pv_assignment_pub.g_la_status_lost_chance,
pv_assignment_pub.g_la_status_match_withdrawn,
pv_assignment_pub.g_la_status_offer_withdrawn)
and a.partner_id = b.source_id
and b.category = 'PARTNER'
AND B.RESOURCE_ID = C.SALESFORCE_ID
and c.lead_id = a.lead_id;
select
d.lead_id, 'PARTY', c.resource_id
from
pv_lead_assignments la,
pv_partner_profiles pvpp,
hz_relationships b,
jtf_rs_resource_extns c,
as_accesses_all d
where
la.wf_item_type = pc_itemtype and
la.wf_item_key = pc_itemkey and
la.partner_id = pc_partner_id and
la.partner_id = pvpp.partner_id and
pvpp.status in ('A', 'I') and
pvpp.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.status in ('A', 'I') and
b.party_id = c.source_id and
c.category = pv_assignment_pub.g_resource_party and
c.resource_id = d.salesforce_id and
d.lead_id = la.lead_id
union all
select
a1.lead_id, d.user_name access_user, a2.resource_id
from
pv_lead_assignments a1,
pv_party_notifications a2,
jtf_rs_resource_extns b,
as_accesses_all c,
fnd_user d
where
a1.wf_item_type = pc_itemtype
and a1.wf_item_key = pc_itemkey
and a1.partner_id = pc_partner_id
and not exists
(select 1 from pv_lead_assignments la , pv_party_notifications pn
where la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_itemkey
and la.partner_id <> a1.partner_id
and la.status in (pv_assignment_pub.g_la_status_cm_approved,
pv_assignment_pub.g_la_status_pt_approved,
pv_assignment_pub.g_la_status_pt_created,
pv_assignment_pub.g_la_status_cm_app_for_pt,
pv_assignment_pub.g_la_status_cm_timeout)
and la.lead_assignment_id = pn.lead_assignment_id
and pn.resource_id = a2.resource_id)
and a1.lead_assignment_id = a2.lead_assignment_id
and a2.notification_type = pv_assignment_pub.g_notify_type_matched_to
and a2.resource_id = b.resource_id
and b.user_id = d.user_id
and a2.resource_id = c.salesforce_id
and a1.lead_id = c.lead_id
union all
select
c.lead_id, 'PARTNER', b.resource_id
from
pv_lead_assignments la,
jtf_rs_resource_extns b,
as_accesses_all c
where
la.wf_item_type = pc_itemtype and
la.wf_item_key = pc_itemkey and
la.partner_id = pc_partner_id and
la.partner_id = b.source_id and
b.category = 'PARTNER' and
B.RESOURCE_ID = C.SALESFORCE_ID and
c.lead_id = la.lead_id;
SELECT
d.lead_id, 'PARTY', c.resource_id
FROM
pv_lead_assignments la,
pv_partner_profiles pvpp,
hz_relationships b,
jtf_rs_resource_extns c,
as_accesses_all d
WHERE
la.wf_item_type = pc_itemtype
AND la.wf_item_key = pc_itemkey
AND la.partner_id = pvpp.partner_id
AND pvpp.status in ('A', 'I')
AND pvpp.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.status in ('A', 'I')
AND b.party_id = c.source_id
AND c.category = pv_assignment_pub.g_resource_party
AND c.resource_id = d.salesforce_id
AND d.lead_id = la.lead_id
UNION ALL
SELECT
c.lead_id, 'PARTNER', b.resource_id
FROM
pv_lead_assignments la,
jtf_rs_resource_extns b,
as_accesses_all c
WHERE
la.wf_item_type = pc_itemtype
AND la.wf_item_key = pc_itemkey
AND la.partner_id = b.source_id
AND b.category = 'PARTNER'
AND b.resource_id = c.salesforce_id
AND c.lead_id = la.lead_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 => p_itemType,
p_itemkey => p_itemKey,
p_current_username => NULL, --- obsolete column
p_lead_id => l_lead_id,
p_customer_id => null,
p_address_id => null,
p_access_action => pv_assignment_pub.G_REMOVE_ACCESS,
p_resource_id => l_resource_id,
p_access_type => l_access_type,
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 => p_itemType,
p_itemkey => p_itemKey,
p_current_username => NULL, --- obsolete column
p_lead_id => l_lead_id,
p_customer_id => null,
p_address_id => null,
p_access_action => pv_assignment_pub.G_REMOVE_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);
SELECT decode(lc_timeoutType, pv_assignment_pub.g_matched_timeout,
decode(cm_timeout_uom_code,'DAYS',(cm_timeout*24),cm_timeout)
, pv_assignment_pub.g_offered_timeout,
decode(partner_timeout_uom_code,'DAYS',(partner_timeout*24),partner_timeout))
FROM PV_ENTITY_ROUTINGS
WHERE PROCESS_RULE_ID = lc_process_rule_id;
select UPGRADE_TZ_ID into l_GMT_timezone_id
from fnd_timezones_vl
where timezone_code = 'GMT';
'select nvl(max(timeout_period), fnd_profile.value(:bv1))*60 ' ||
'from pv_country_timeouts pr ' ||
'where pr.timeout_type = :1 ' ||
'and pr.country_code in ';
l_query := l_query || ' ( select loc.country from '||
' hz_locations loc, hz_party_sites pty, as_leads_all lead '||
' where pty.location_id = loc.location_id '||
' and pty.party_site_id = lead.address_id '||
' and lead.lead_id = :2 ) ';
l_query := l_query || ' ( select hzl.country from '||
'hz_locations hzl, hz_party_sites hzps, pv_lead_assignments lead, '||
'hz_parties partner, hz_relationships hzrl, hz_organization_profiles hzop '||
'where hzl.location_id = hzps.location_id '||
'and hzps.party_id = partner.party_id '||
'and hzrl.party_id = lead.partner_id '||
'and hzrl.subject_id = partner.party_id '||
'and hzrl.object_id = hzop.party_id '||
'and hzrl.subject_table_name = ''HZ_PARTIES'' '||
'and hzrl.object_table_name = ''HZ_PARTIES'' '||
'and hzrl.status in (''A'',''I'') '||
'and hzop.internal_flag = ''Y'' '||
'and hzop.effective_end_date is null '||
'and partner.status = ''A'' '||
'and lead.wf_item_type = :2 ' ||
'and lead.wf_item_key = :3 ' ||
'and hzps.identifying_address_flag(+) = ''Y'' ';
update pv_lead_workflows set matched_due_date = l_due_date,
object_version_number = object_version_number + 1
where wf_item_type = p_itemtype
and wf_item_key = p_itemkey;
update pv_lead_workflows set offered_due_date = l_due_date,
object_version_number = object_version_number + 1
where wf_item_type = p_itemtype
and wf_item_key = p_itemkey;
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 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.status in ( pv_assignment_pub.g_la_status_cm_approved,
pv_assignment_pub.g_la_status_cm_added,
pv_assignment_pub.g_la_status_cm_bypassed,
pv_assignment_pub.g_la_status_cm_app_for_pt,
pv_assignment_pub.g_la_status_cm_timeout)
and la.lead_assignment_id = pn.lead_assignment_id
and pn.notification_type = pc_notify_type
and usr.user_id = pn.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 usr.user_id = pn.user_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 => p_itemType,
p_itemkey => p_itemKey,
p_current_username => l_username_tbl(i),
p_lead_id => l_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_tbl(i),
p_access_type => pv_assignment_pub.G_PT_ACCESS,
x_access_id => l_temp_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
update pv_partner_profiles
set oppty_last_offered_date = sysdate,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
where partner_id = p_partner_id;
update pv_partner_profiles
set oppty_last_offered_date = sysdate,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
where partner_id in (select partner_id from pv_lead_assignments
where wf_item_type = p_itemtype
and wf_item_key = p_itemkey
and status in
( pv_assignment_pub.g_la_status_cm_approved,
pv_assignment_pub.g_la_status_cm_added,
pv_assignment_pub.g_la_status_cm_bypassed,
pv_assignment_pub.g_la_status_cm_app_for_pt,
pv_assignment_pub.g_la_status_cm_timeout
));
update pv_lead_assignments
set partner_access_code = decode(status,
pv_assignment_pub.g_la_status_cm_app_for_pt, pv_assignment_pub.g_assign_access_update,
pv_assignment_pub.g_assign_access_view),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
where wf_item_type = p_itemtype
and wf_item_key = p_itemkey
and partner_id = p_partner_id;
update pv_lead_assignments
set partner_access_code = decode(status,
pv_assignment_pub.g_la_status_cm_app_for_pt, pv_assignment_pub.g_assign_access_update,
pv_assignment_pub.g_assign_access_view),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
where rowid in (select rowid from pv_lead_assignments
where wf_item_type = p_itemtype
and wf_item_key = p_itemkey
and status in
( pv_assignment_pub.g_la_status_cm_approved,
pv_assignment_pub.g_la_status_cm_added,
pv_assignment_pub.g_la_status_cm_bypassed,
pv_assignment_pub.g_la_status_cm_app_for_pt,
pv_assignment_pub.g_la_status_cm_timeout
));
procedure update_routing_stage (
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_routing_stage IN VARCHAR2
,p_active_but_open_flag IN VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2) is
l_api_name CONSTANT VARCHAR2(30) := 'update_routing_stage';
select rowid,
lead_id,
lead_workflow_id,
routing_status,
routing_type
from pv_lead_workflows
where wf_item_type = pc_itemtype
and wf_item_key = pc_itemkey;
select b.lead_assignment_id,b.partner_id
from pv_lead_workflows a, pv_lead_assignments b
where a.lead_workflow_id = pc_workflow_id
and a.wf_item_type = b.wf_item_type
and a.wf_item_key = b.wf_item_key
and b.status in ('CM_APPROVED','CM_TIMEOUT','CM_BYPASSED','CM_APP_FOR_PT');
select b.lead_assignment_id,b.partner_id
from pv_lead_workflows a, pv_lead_assignments b
where a.lead_workflow_id = pc_workflow_id
and a.wf_item_type = b.wf_item_type
and a.wf_item_key = b.wf_item_key
and b.status in ('PT_APPROVED','CM_APP_FOR_PT')
and not exists (select 1 from pv_assignment_logs aa
where aa.lead_assignment_id = b.lead_assignment_id
and aa.to_lead_status = 'ACTIVE');
select b.lead_assignment_id,b.partner_id
from pv_lead_workflows a, pv_lead_assignments b
where a.lead_workflow_id = pc_workflow_id
and a.wf_item_type = b.wf_item_type
and a.wf_item_key = b.wf_item_key
and b.status in ('PT_ABANDONED')
and not exists (select 1 from pv_assignment_logs aa
where aa.lead_assignment_id = b.lead_assignment_id
and aa.to_lead_status = 'ABANDONED');
fnd_message.SET_TOKEN('TEXT', 'Cannot find workflow row to update. Itemkey: ' || p_itemkey);
FOR x IN (SELECT lead_number FROM as_leads_all WHERE lead_id = l_lead_id) LOOP
l_log_params_tbl(1).param_name := 'OPP_NUMBER';
SELECT a.partner_id, c.party_name
FROM pv_lead_assignments a,
pv_partner_profiles b,
hz_parties c
WHERE a.wf_item_type = p_itemtype AND
a.wf_item_key = p_itemkey AND
a.status IN ('CM_APPROVED', 'CM_BYPASSED', 'CM_TIMEOUT') AND
a.partner_id = b.partner_id AND
b.partner_party_id = c.party_id
)
LOOP
l_log_params_tbl(3).param_name := 'PARTNER_NAME';
update pv_lead_workflows
set routing_status = p_routing_stage,
wf_status = l_wf_status,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
where rowid = l_rowid returning entity into l_entity;
update as_leads_all
set prm_assignment_type = nvl(l_assignment_type, prm_assignment_type),
auto_assignment_type = 'TAP'
where lead_id = l_lead_id;
update as_sales_leads
set prm_assignment_type = nvl(l_assignment_type, prm_assignment_type),
auto_assignment_type = 'TAP'
where sales_lead_id = l_lead_id;
end update_routing_stage;
select meaning
from pv_lookups
where lookup_type = pc_lookup_type
and lookup_code = pc_lookup_code;
update pv_lead_workflows
set latest_routing_flag = decode(wf_item_key, p_itemkey, 'Y', 'N'),
object_version_number = object_version_number + 1
where lead_id = p_entity_id
and entity = p_entity
and latest_routing_flag = 'Y';
select pv_party_notifications_s.nextval
from fnd_tables where rownum <= pc_count;
INSERT into pv_party_notifications (
PARTY_NOTIFICATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_LOGIN,
WF_ITEM_TYPE,
WF_ITEM_KEY,
NOTIFICATION_TYPE,
LEAD_ASSIGNMENT_ID,
USER_ID,
--USER_NAME,
RESOURCE_ID,
RESOURCE_RESPONSE,
RESPONSE_DATE,
DECISION_MAKER_FLAG
) values (
l_party_notify_id_tbl(i),
sysdate,
fnd_global.user_id,
sysdate,
1,
fnd_global.user_id,
fnd_global.conc_login_id,
p_party_notify_rec_tbl.wf_item_type(i),
p_party_notify_rec_tbl.wf_item_key(i),
p_party_notify_rec_tbl.notification_type(i),
p_party_notify_rec_tbl.lead_assignment_id(i),
p_party_notify_rec_tbl.user_id(i),
--p_party_notify_rec_tbl.user_name(i),
p_party_notify_rec_tbl.resource_id(i),
p_party_notify_rec_tbl.resource_response(i),
p_party_notify_rec_tbl.response_date(i),
p_party_notify_rec_tbl.decision_maker_flag(i)
);
l_selected_pt_only boolean;
select
nvl(b.notify_pt_flag, 'N'),
nvl(b.notify_cm_flag, 'N'),
nvl(b.notify_am_flag, 'N'),
nvl(b.notify_others_flag, 'N'),
b.enabled_flag
from pv_status_notifications b
where
b.status_type = 'ROUTING'
and b.status_code = pc_route_stage;
SELECT pn.user_id, pn.resource_id, usr.user_name,
decode(pn.notification_type, 'MATCHED_TO', 'CM', 'PT') user_type,
decode(pc_ignore_pt_flag, 'Y', 0, pa.partner_id) partner_id, pa.status
FROM pv_lead_assignments pa, pv_party_notifications pn, pv_lead_workflows pw, fnd_user usr
WHERE
((pn.notification_type = 'MATCHED_TO' and 'Y' = pc_notify_cm_flag)
or (pn.notification_type = 'OFFERED_TO' and 'Y' = pc_notify_pt_flag))
and pw.lead_id = pc_lead_id
and pw.entity = 'OPPORTUNITY'
and pw.latest_routing_flag = 'Y'
AND pa.lead_assignment_id = pn.lead_assignment_id
and pw.wf_item_type = pa.wf_item_type
and pw.wf_item_key = pa.wf_item_key
AND pn.user_id = usr.user_id
AND sysdate between usr.start_date and nvl(usr.end_date,sysdate)
union
SELECT js.user_id, js.resource_id, fu.user_name,
decode(pw.created_by - js.user_id,0,'AM','OT') user_type,
decode(pc_ignore_pt_flag, 'Y', 0, pl.partner_id) partner_id, pl.status
FROM as_accesses_all ac, jtf_rs_resource_extns js, fnd_user fu,
pv_lead_workflows pw, pv_lead_assignments pl
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 ac.salesforce_id = js.resource_id
AND js.user_id = fu.user_id
and pw.entity = 'OPPORTUNITY'
and pw.latest_routing_flag = 'Y'
and pl.wf_item_type = pw.wf_item_type
and pl.wf_item_key = pw.wf_item_key
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 pa, pv_party_notifications pv
where pa.wf_item_type = pw.wf_item_type
and pv.user_id <> pw.created_by
and pa.wf_item_key = pw.wf_item_key
AND pa.lead_assignment_id = pv.lead_assignment_id
and pv.resource_id = ac.salesforce_id)
order by 5,4;
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 c.resource_name
from pv_lead_assignments a,
pv_party_notifications b,
jtf_rs_resource_extns_vl c
where a.wf_item_type = pc_itemtype
and a.wf_item_key = pc_itemkey
and a.partner_id = pc_partner_id
and a.lead_assignment_id = b.lead_assignment_id
and b.resource_response = pc_response
and b.user_id = c.user_id;
select b.meaning
from pv_lead_assignments a,
pv_lookups b
where a.wf_item_type = pc_itemtype
and a.wf_item_key = pc_itemkey
and a.partner_id = pc_partner_id
and a.reason_code = b.lookup_code
and b.lookup_type = 'PV_REASON_CODES';
l_selected_pt_only := true;
if l_selected_pt_only and l_partner_id <> p_partner_id then
exit;
select pv_lead_workflows_s.nextval into l_itemkey from dual;