[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 <> pv_assignment_pub.g_la_status_pt_created;
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_tbl(i)
,p_status_date => sysdate
,p_status => pv_assignment_pub.g_la_status_cm_bypassed
,p_reason_code => NULL
,p_rank => NULL
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
select a.lead_assignment_id, a.status
from pv_lead_assignments a
where a.wf_item_type = pc_itemtype
and a.wf_item_key = pc_itemkey;
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_tbl(i)
,p_status_date => sysdate
,p_status => pv_assignment_pub.g_la_status_cm_timeout
,p_reason_code => NULL
,p_rank => NULL
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
update pv_party_notifications
set resource_response = pv_assignment_pub.g_la_status_cm_timeout,
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 lead_assignment_id = l_assignment_id_tbl(i)
and resource_response is null
and notification_type = pv_assignment_pub.g_notify_type_matched_to;
select partner_id, assign_sequence
from pv_lead_assignments la
where la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_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)
order by assign_sequence;
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 => itemtype,
p_itemKey => itemKey,
p_routing_stage => pv_assignment_pub.g_r_status_offered,
p_active_but_open_flag => 'N',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
select la.partner_id, la.assign_sequence, a.lead_id, a.lead_workflow_id, la.lead_assignment_id
from pv_lead_workflows a, pv_lead_assignments la
where la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_itemkey
and la.assign_sequence >= pc_sequence
and la.status in ( pv_assignment_pub.g_la_status_cm_approved,
pv_assignment_pub.g_la_status_cm_app_for_pt,
pv_assignment_pub.g_la_status_cm_bypassed,
pv_assignment_pub.g_la_status_cm_timeout)
and la.wf_item_type = a.wf_item_type
and la.wf_item_key = a.wf_item_key
order by assign_sequence;
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_cm_app_for_pt
and la.partner_id = b.source_id
and b.category = 'PARTNER';
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 => itemType,
p_itemkey => itemKey,
p_current_username => NULL,
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_pt_org_rs_id,
p_access_type => pv_assignment_pub.G_PT_ORG_ACCESS,
x_access_id => l_access_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
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 => itemtype,
p_itemKey => itemKey,
p_routing_stage => pv_assignment_pub.g_r_status_active,
p_active_but_open_flag => 'Y',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
select a.activity_result_code
from wf_item_activity_statuses a
where a.item_type = pc_itemtype
and a.item_key = pc_itemkey
and a.process_activity =
(select d.from_process_activity
from wf_process_activities c, wf_activity_transitions d
where d.to_process_activity = pc_to_activity_id
and d.from_process_activity = c.instance_id
and c.activity_name = pc_from_activity_name and rownum < 2);
select la.lead_assignment_id, la.partner_id, la.status
from pv_lead_assignments la
where la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_itemkey
and la.partner_id = pc_partner_id;
select la.lead_assignment_id, la.partner_id, la.status
from pv_lead_assignments la
where la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_itemkey;
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 (pv_assignment_pub.g_la_status_cm_timeout,
pv_assignment_pub.g_la_status_cm_approved,
pv_assignment_pub.g_la_status_cm_bypassed,
pv_assignment_pub.g_la_status_cm_app_for_pt,
pv_assignment_pub.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_timeout
and la.partner_id = b.source_id
and b.category = 'PARTNER';
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_lost_chance
and la.partner_id = b.source_id
and b.category = 'PARTNER';
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 in (pv_assignment_pub.g_la_status_cm_app_for_pt,
pv_assignment_pub.g_la_status_pt_approved)
and la.partner_id = b.source_id
and b.category = 'PARTNER';
select la.lead_assignment_id, la.partner_id
from pv_lead_assignments la
where la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_itemkey
and la.assign_sequence > pc_rank
and la.status in (pv_assignment_pub.g_la_status_cm_timeout,
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_approved);
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_tbl(i)
,p_status_date => sysdate
,p_status => pv_assignment_pub.g_la_status_pt_timeout
,p_reason_code => NULL
,p_rank => NULL
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
l_username_tbl.delete;
l_resource_id_tbl.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 => itemType,
p_itemkey => itemKey,
p_current_username => l_username_tbl(i),
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_tbl(i),
p_access_type => pv_assignment_pub.g_pt_access,
x_access_id => l_access_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_username_tbl.delete;
l_resource_id_tbl.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 => itemType,
p_itemkey => itemKey,
p_current_username => l_username_tbl(i),
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_tbl(i),
p_access_type => pv_assignment_pub.g_cm_access,
x_access_id => l_access_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_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 => itemType,
p_itemkey => itemKey,
p_current_username => NULL,
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_REMOVE_ACCESS,
p_resource_id => l_partner_org_rs_id,
p_access_type => pv_assignment_pub.G_PT_ORG_ACCESS,
x_access_id => l_access_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
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_tbl(i)
,p_status_date => sysdate
,p_status => pv_assignment_pub.g_la_status_lost_chance
,p_reason_code => NULL
,p_rank => NULL
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
l_username_tbl.delete;
l_resource_id_tbl.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 => itemType,
p_itemkey => itemKey,
p_current_username => l_username_tbl(i),
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_tbl(i),
p_access_type => pv_assignment_pub.G_PT_ACCESS,
x_access_id => l_access_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_username_tbl.delete;
l_resource_id_tbl.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 => itemType,
p_itemkey => itemKey,
p_current_username => l_username_tbl(i),
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_tbl(i),
p_access_type => pv_assignment_pub.G_CM_ACCESS,
x_access_id => l_access_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_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 => itemType,
p_itemkey => itemKey,
p_current_username => NULL,
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_REMOVE_ACCESS,
p_resource_id => l_lc_partner_rs_id,
p_access_type => pv_assignment_pub.G_PT_ORG_ACCESS,
x_access_id => l_access_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_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 => itemType,
p_itemkey => itemKey,
p_current_username => NULL,
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_partner_org_rs_id,
p_access_type => pv_assignment_pub.G_PT_ORG_ACCESS,
x_access_id => l_access_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
select la.status
from pv_lead_assignments la
where la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_itemkey
and la.partner_id = pc_partner_id;
select la.status
from pv_lead_assignments la
where la.wf_item_type = pc_itemtype
and la.wf_item_key = pc_itemkey
and la.status = pv_assignment_pub.g_la_status_cm_app_for_pt;
select la.status
from pv_lead_assignments la
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_timeout);
FOR x IN (SELECT routing_type
FROM pv_lead_workflows
WHERE lead_id = l_lead_id AND
latest_routing_flag = 'Y')
LOOP
l_routing_type := x.routing_type;
FOR x IN (SELECT COUNT(*) approved_count
FROM pv_lead_assignments
WHERE wf_item_type = itemtype AND
wf_item_key = itemkey AND
status IN (pv_assignment_pub.g_la_status_cm_add_app_for_pt,
pv_assignment_pub.g_la_status_cm_app_for_pt,
pv_assignment_pub.g_la_status_pt_approved))
LOOP
IF (x.approved_count > 0) THEN
l_routing_stage := PV_ASSIGNMENT_PUB.g_r_status_active;
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 => itemtype,
p_itemKey => itemKey,
p_routing_stage => l_routing_stage,
p_active_but_open_flag => 'N',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
select c.CONCAT_CAT_PARENTAGE, decode(nvl(b.total_amount,0),0,'', b.total_amount || ' ' || a.currency_code) amount
from as_leads_all a, as_lead_lines_all b, eni_prod_den_hrchy_parents_v c
where a.lead_id = pc_entity_id
and a.lead_id = b.lead_id
and b.product_cat_set_id = c.category_set_id
and b.product_category_id = c.category_id;
select max(length(c.CONCAT_CAT_PARENTAGE)), max(length(to_char(b.total_amount) || ' ' || a.currency_code))
from as_leads_all a, as_lead_lines_all b, eni_prod_den_hrchy_parents_v c
where a.lead_id = pc_entity_id
and a.lead_id = b.lead_id
and b.product_cat_set_id = c.category_set_id
and b.product_category_id = c.category_id;
select attribute_code,attribute_label_long
from ak_attributes_tl ak
where attribute_application_id = 522
AND ATTRIBUTE_code in ('ASF_AMOUNT','ASF_PRODUCT_CATEGORY')
AND ak.language= userenv('LANG');
SELECT EMAIL_ADDRESS emailAddress, PRIMARY_CONTACT_FLAG primaryContact,
PERSON_PRE_NAME_ADJUNCT title, FIRST_NAME||',' || LAST_NAME fullName,
trim(PHONE_COUNTRY_CODE || ' ' || AREA_CODE || ' ' || PHONE_NUMBER || ' ' ||EXTENSION) phoneNumber
FROM AS_OPPORTUNITY_CONTACTS_V WHERE lead_id = pc_lead_id;
SELECT max(length(EMAIL_ADDRESS)),
max(length(PERSON_PRE_NAME_ADJUNCT)), max(length( FIRST_NAME||',' || LAST_NAME)),
max(length(trim(PHONE_COUNTRY_CODE || ' ' || AREA_CODE || ' ' || PHONE_NUMBER || ' ' ||EXTENSION)))
FROM AS_OPPORTUNITY_CONTACTS_V WHERE lead_id = pc_entity_id;
select attribute_code,attribute_label_long
from ak_attributes_tl ak
where attribute_application_id = 522
AND ATTRIBUTE_code in ('ASF_EMAIL','ASF_PRIMARY','ASF_TITLE','ASF_PHONE','ASF_CONTACT_NAME')
AND ak.language= userenv('LANG');
select entered_date, entered_by_name, notes, NOTES_DETAIL
from jtf_notes_vl
where source_object_code = 'OPPORTUNITY'
AND SOURCE_OBJECT_ID = pc_entity_id
AND NOTE_STATUS = 'E' -- only publish notes
ORDER BY CREATION_DATE DESC;
select max(length(entered_date)), max(length(entered_by_name))
from jtf_notes_vl
where source_object_code = 'OPPORTUNITY'
AND SOURCE_OBJECT_ID = pc_entity_id
AND NOTE_STATUS = 'E';
select attribute_code,attribute_label_long
from ak_attributes_tl ak
where attribute_application_id = 522
AND ATTRIBUTE_code in ('ASF_DATE','ASF_CREATED_BY','ASF_NOTE')
AND ak.language= userenv('LANG');
SELECT LOWER(b.meaning) meaning
FROM pv_lead_workflows a,
fnd_lookup_values_vl b
WHERE a.lead_id = pc_lead_id AND
a.routing_type = b.lookup_code AND
b.lookup_type = 'PV_ASSIGNMENT_TYPE';
SELECT otl.name vendor_name
FROM pv_lead_workflows a,
fnd_user b,
hr_all_organization_units o,
hr_all_organization_units_tl otl,
per_all_people_f p
WHERE a.lead_id = pc_entity_id AND
a.created_by = b.user_id AND
o.organization_id = otl.organization_id AND
otl.language = userenv('lang') AND
o.organization_id = p.business_group_id AND
b.employee_id = p.person_id and
p.effective_start_date <= sysdate and
p.effective_end_date >= sysdate
;
select
nvl(h.name, e.party_name) organization
from
PV_LEAD_ASSIGNMENTS C,
pv_oppty_routing_logs a,
pv_partner_profiles d,
hz_parties e,
HR_ALL_ORGANIZATION_UNITS_TL h
where
c.wf_item_type = 'PVASGNMT'
and c.wf_item_key = (select substr(context,10,LENGTH(CONTEXT)-10)
from wf_notifications where notification_id = pc_notification)
and c.partner_id = pc_partner_id
and c.lead_assignment_id = a.lead_assignment_id
and a.user_response in ('PT_APPROVED', 'CM_APP_FOR_PT')
and c.partner_id = d.partner_id
and d.partner_party_id = e.party_id
AND A.vendor_business_unit_id = H.ORGANIZATION_ID (+)
AND H.LANGUAGE (+) = USERENV('LANG');
select
case
when a.vendor_user_id is null then
(select hzp.party_name
from hz_relationships hzr, hz_parties hzp
where hzr.party_id=g.source_id and hzr.subject_type='PERSON' and
hzr.subject_id=hzp.party_id and hzr.object_type= 'ORGANIZATION' )
else (g.source_first_name || ' ' || g.source_last_name)
end person_name
from
PV_LEAD_ASSIGNMENTS C,
pv_oppty_routing_logs a,
jtf_rs_resource_extns g
where
c.wf_item_type = 'PVASGNMT'
and c.wf_item_key = (select substr(context,10,LENGTH(CONTEXT)-10)
from wf_notifications where notification_id = pc_notification)
and c.partner_id = pc_partner_id
and c.lead_assignment_id = a.lead_assignment_id
and a.user_response in ('PT_APPROVED', 'CM_APP_FOR_PT')
and nvl(a.vendor_user_id, a.pt_contact_user_id) = g.user_id (+);