[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_attr_selection_mode IN VARCHAR2,
p_att_delmter IN VARCHAR2,
p_selection_criteria IN VARCHAR2,
p_resource_id IN NUMBER,
p_lead_id IN NUMBER,
p_auto_match_flag IN VARCHAR2,
p_get_distance_flag IN VARCHAR2 := 'F',
x_matched_id OUT NOCOPY JTF_NUMBER_TABLE,
x_partner_details OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
x_distance_tbl OUT NOCOPY JTF_NUMBER_TABLE,
x_distance_uom_returned OUT NOCOPY VARCHAR2,
x_flagcount OUT NOCOPY JTF_VARCHAR2_TABLE_100,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_top_n_rows_by_profile IN VARCHAR2 := 'T'
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Manual_Match';
select asla.INCUMBENT_PARTNER_PARTY_ID
from as_leads_all asla
where asla.lead_id = pc_lead_id;
,p_attr_selection_mode => p_attr_selection_mode
,p_att_delmter => p_att_delmter
,p_selection_criteria => p_selection_criteria
,p_resource_id => p_resource_id
,p_lead_id => p_lead_id
,p_auto_match_flag => p_auto_match_flag
,p_top_n_rows_by_profile => p_top_n_rows_by_profile
,x_matched_id => x_matched_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
SELECT idx
FROM (SELECT rownum idx, column_value party_id
FROM (SELECT column_value
FROM TABLE (CAST(x_matched_id AS JTF_NUMBER_TABLE)))) a
WHERE a.party_id = l_incumbent_pt_party_id)
LOOP
l_incumbent_idx := x.idx;
SELECT b.location_id
INTO l_customer_address.location_id
FROM as_leads_all a,
hz_party_sites b,
hz_locations l
WHERE a.lead_id = p_lead_id AND
a.customer_id = b.party_id AND
b.party_site_id = a.address_id AND
b.location_id = l.location_id AND
l.geometry IS NOT NULL;
SELECT idx
FROM (SELECT rownum idx, column_value party_id
FROM (SELECT column_value
FROM TABLE (CAST(x_matched_id AS JTF_NUMBER_TABLE)))) a
WHERE a.party_id = l_incumbent_pt_party_id)
LOOP
l_incumbent_idx := x.idx;
p_attr_selection_mode IN VARCHAR2,
p_att_delmter IN VARCHAR2,
p_selection_criteria IN VARCHAR2,
p_resource_id IN NUMBER,
p_lead_id IN NUMBER,
p_auto_match_flag IN VARCHAR2,
x_matched_id OUT NOCOPY JTF_NUMBER_TABLE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_top_n_rows_by_profile IN VARCHAR2 := 'T')IS
Type l_tmp is Table of Varchar2(4000) index by binary_integer;
select jtfre.category, jtfre.source_id
from jtf_rs_resource_extns jtfre
where jtfre.resource_id = pc_resource_id;
select 'X'
from pv_party_notifications pvpn, pv_lead_assignments pvla , pv_lead_workflows pvlw
where pvlw.lead_id = pc_lead_id
and pvlw.entity = 'OPPORTUNITY'
and pvlw.LATEST_ROUTING_FLAG = 'Y'
and pvlw.routing_status = 'MATCHED'
and pvlw.wf_item_key = pvla.WF_ITEM_KEY
and pvlw.wf_item_type = pvla.wf_item_type
and pvla.lead_assignment_id = pvpn.lead_assignment_id
and pvpn.resource_id = pc_resource_id
and pvpn.notification_type = 'MATCHED_TO';
IF p_attr_selection_mode = g_and_attr_select
AND p_selection_criteria = g_drop_attr_match
THEN
fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
fnd_message.Set_Token('TEXT', 'This Attribute Selection Mode :'||g_and_attr_select||'and Selection Criteria :'||g_drop_attr_match||' Combination is not supported');
IF p_attr_selection_mode NOT IN (g_and_attr_select, g_or_attr_select) THEN
fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
fnd_message.Set_Token('TEXT', 'Attribute Selection Mode is wrong. Please pass in the correct value ');
IF p_selection_criteria NOT IN (g_drop_attr_match, g_nodrop_attr_match) THEN
fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
fnd_message.Set_Token('TEXT', 'Selection Criteria is wrong . Please pass in the correct value ');
SELECT decode ( p_attr_operator_tbl(attr_seq) , g_null_opr, 'Is Null'
, g_not_null_opr, 'Is Not Null'
, g_not_equals_opr, 'Not Equals')
INTO l_opr_meaning
FROM DUAL;
l_tmp_where := 'select distinct t.party_id,' || l_rank_base_2 || ' rank ' ||
'from pv_search_attr_values t ' ||
'where t.attribute_id = :bv' || l_bind_count;
OR (l_delm_cnt > 0 AND p_attr_selection_mode = g_and_attr_select
AND p_selection_criteria = g_nodrop_attr_match) THEN
l_attr_operator := 'like ';
OR (l_delm_cnt > 0 AND p_attr_selection_mode = g_and_attr_select
AND p_selection_criteria = g_nodrop_attr_match) THEN
l_attr_operator := 'not like ';
(l_delm_cnt > 0 AND p_attr_selection_mode = g_and_attr_select
AND p_selection_criteria = g_nodrop_attr_match) THEN
l_attr_operator := ' = ';
(l_delm_cnt > 0 AND p_attr_selection_mode = g_and_attr_select
AND p_selection_criteria = g_nodrop_attr_match) THEN
l_attr_operator := ' <> ';
OR ( p_selection_criteria = g_nodrop_attr_match AND l_delm_cnt > 0
AND p_attr_selection_mode = g_and_attr_select )
THEN
IF p_attr_operator_tbl(attr_seq) = g_greater_opr THEN
l_attr_operator := ' > ';
ELSIF l_delm_cnt > 0 AND p_selection_criteria = g_drop_attr_match
AND p_attr_operator_tbl(attr_seq) in ( g_greater_opr,
g_less_opr,
g_grt_or_equ_opr,
g_less_or_equ_opr )
THEN
fnd_message.Set_Name('PV', 'PV_WRONG_OPR_FOR_NUM_DATE');
-- Insert bind variables for between operators.
-- ----------------------------------------------------------------------------------
IF p_attr_data_type_tbl(attr_seq) = g_string_data_type THEN
fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
IF p_selection_criteria = g_drop_attr_match THEN
l_rank_base_2 := l_rank_base_2 * 2;
ELSIF l_delm_cnt > 0 AND p_attr_selection_mode = g_or_attr_select THEN
Debug('##########################################################################');
Debug('# Build where clause for multiple values and p_attr_selection_mode = OR');
IF p_selection_criteria = g_drop_attr_match THEN
Debug('##########################################################################');
Debug('# for p_selection_criteria = g_drop_attr_match');
ELSIF l_delm_cnt > 0 AND p_selection_criteria = g_nodrop_attr_match
AND p_attr_selection_mode = g_and_attr_select THEN
Debug('##########################################################################');
Debug('# Build where clause for multiple values and p_attr_selection_mode = AND');
-- the previous l_tmp_where is overwritten by this select statement here.
-- ------------------------------------------------------------------------------
IF (i > 1) THEN
l_bind_count := l_bind_count + 1;
l_tmp_where := 'select distinct t.party_id,' || l_rank_base_2 || ' rank ' ||
'from pv_search_attr_values t ' ||
'where t.attribute_id = :bv' || l_bind_count || ' and attr_value ';
-- the previous l_tmp_where is overwritten by this select statement here.
-- ------------------------------------------------------------------------------
IF (i > 1) THEN
l_bind_count := l_bind_count + 1;
l_tmp_where := 'select distinct t.party_id,' || l_rank_base_2 || ' rank ' ||
'from pv_search_attr_values t ' ||
'where t.attribute_id = :bv' || l_bind_count || ' and uppeR(attr_text) ';
l_where := 'select /*+ no_merge(t) */ distinct t.party_id, rank from ( select party_id, sum(rank) rank from ( ';
IF p_selection_criteria = g_drop_attr_match THEN
l_where := l_where || ') group by party_id having mod(sum(rank),2) <> 0 ) t, pv_partner_profiles pvpp ';
ELSIF p_selection_criteria = g_nodrop_attr_match THEN
l_where := l_where || ') group by party_id ) t, pv_partner_profiles pvpp ';
|| ' and pvpp.partner_party_id in ( select a.customer_id from as_accesses_all a '
|| ' where a.salesforce_id = :bv' || l_bind_count
|| ' and a.sales_lead_id is null and a.lead_id is null) ';
,p_selection_criteria => p_selection_criteria
,p_num_of_attrs => l_tmp_tbl.count
,p_bind_var_tbl => l_bind_var_tbl
,p_top_n_rows_by_profile => p_top_n_rows_by_profile
,x_matched_prt => x_matched_id
,x_prt_matched => l_prt_matched
,x_matched_attr_cnt => l_matched_attr_cnt
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
p_selection_criteria IN VARCHAR2,
p_num_of_attrs IN NUMBER,
p_bind_var_tbl IN bind_var_tbl,
p_top_n_rows_by_profile IN VARCHAR2 := 'T',
x_matched_prt OUT NOCOPY JTF_NUMBER_TABLE,
x_prt_matched OUT NOCOPY BOOLEAN,
x_matched_attr_cnt OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_possible_match_party_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
IF p_selection_criteria = g_drop_attr_match THEN
for i in 1..p_num_of_attrs - 1 loop
l_matching_rank := l_matching_rank * 2;
ELSIF p_selection_criteria = g_nodrop_attr_match THEN
l_matching_rank := p_num_of_attrs;
IF p_selection_criteria = g_nodrop_attr_match THEN
if l_combined_rank = l_rank_base_2 then
if l_match_count < l_top_n_rows then
IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
ELSIF p_selection_criteria = g_drop_attr_match THEN
IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
IF p_selection_criteria = g_drop_attr_match THEN
if x_matched_prt.count = 0 then
l_matching_rank := l_possible_rank_high;
IF p_selection_criteria = g_drop_attr_match THEN
l_matching_rank := l_matching_rank / 2;
ELSIF p_selection_criteria = g_nodrop_attr_match THEN
l_matching_rank := l_matching_rank-1;
Following Assumptions are made for the following select statements.
1. Flag values should each be a different power of 2 to ensure that
each bit is used by only one flag.
Also, these flag values should match with the flag constants defined
in java API to resolve flags on the front end side.
REJECTED CURRENT OPPORTUNITY = 1
PREFERRED OR INCUMBENT PARTNER FOR CURRENT OPPORTUNITY = 2
2. Most of the select statements assume that PT_APPROVED row for accepted
partner exists in pv_lead_assignments until oppty is recycled by the partner
3. ISSUE : RECYCLED from_status does not have partner_id populated in
pv_assignment_logs. So, rejected partner query may not give the correct result
PROPOSAL : We need to identify an assignment status when partner is
rejecting an oppty
after accepting it . Then, we can populate partner_id in logs table
to identify rejected partner
*/
Procedure Get_Matched_Partner_Details(
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_extra_partner_details IN JTF_VARCHAR2_TABLE_1000,
p_matched_id IN OUT NOCOPY JTF_NUMBER_TABLE,
x_partner_details OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
x_flagcount OUT NOCOPY JTF_VARCHAR2_TABLE_100,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
-- ACTIVE_OPPTY_FLAG CONSTANT NUMBER := 1;
select asla.INCUMBENT_PARTNER_PARTY_ID
from as_leads_all asla
where asla.lead_id = pc_lead_id;
select sum(flagvalue) flagcount
from (
select rejected_oppty_flag flagvalue
from dual
where exists
(select rejected_oppty_flag
from pv_lead_assignments pval
where pval.lead_id = pc_lead_id
and pval.status in ('PT_REJECTED', 'PT_ABANDONED', 'PT_TIMEOUT')
and pval.partner_id = pc_partner_id
)
union
select incumbent_partner_flag flagvalue
from dual
where pc_partner_id = pc_incumbent_pt_party_id
);
select pvpp.partner_id,
hzp.party_name,
hzop_pt.internal_flag pt_int_flag,
vend.party_name,
hzop_vend.internal_flag vend_int_flag
from hz_parties hzp , pv_partner_profiles pvpp , hz_parties vend,
hz_relationships hzr,
hz_organization_profiles HZOP_pt,
hz_organization_profiles hzop_vend
where hzr.party_id = pvpp.partner_id
and pvpp.partner_party_id = hzr.subject_id
and hzr.subject_id = hzp.party_id
and hzr.subject_table_name = 'HZ_PARTIES'
and hzr.object_table_name = 'HZ_PARTIES'
and hzr.status = 'A' and hzr.start_date <= sysdate and nvl(hzr.end_date,sysdate) >= sysdate
and hzr.subject_id = HZOP_pt.party_id and nvl(hzop_pt.effective_end_date,sysdate) >= sysdate
and hzr.object_id = HZOP_vend.party_id and nvl(hzop_vend.effective_end_date,sysdate) >= sysdate
and (HZOP_vend.internal_flag = 'N' or hzop_pt.internal_flag = 'Y')
and pvpp.partner_id in (
SELECT * FROM TABLE (CAST(p_matched_id AS JTF_NUMBER_TABLE))
)
and hzr.object_id = vend.party_id
and hzr.relationship_code in ('PARTNER_OF','VAD_OF');
select hzp.party_name, hzp.city, hzp.state ,
hzp.postal_code, hzp.country, hzp.address1,
hzp.address2, hzp.address3, hzp.party_id,
pvpp.partner_id,
to_char(pvpp.OPPTY_LAST_OFFERED_DATE, 'YYYY-MM-DD HH:MM:SS'),
pvac.description, hzr.relationship_id,
(case when hzp.status = 'A'
and hzr.status = 'A'
and nvl(hzop.effective_start_date, sysdate) <= sysdate
and nvl(hzop.effective_end_date, sysdate) >= sysdate
and hzr.start_date <= SYSDATE and NVL(hzr.end_date,SYSDATE) >= SYSDATE
then 'A'
else 'I'
end ) active_flag
from hz_parties hzp , pv_partner_profiles pvpp ,
pv_attribute_codes_vl pvac, hz_relationships hzr ,
hz_organization_profiles HZOP,
(SELECT rownum idx, column_value
FROM (SELECT column_value FROM TABLE (CAST(p_matched_id AS JTF_NUMBER_TABLE)))) x_partner
where pvpp_partner_id in (SELECT * FROM THE(select CAST(p_matched_id AS JTF_NUMBER_TABLE) from dual))
and pvpp.partner_id = x_partner.column_value
and hzr.party_id = pvpp.partner_id
and hzr.subject_id = hzp.party_id
and hzr.object_id = HZOP.party_id
and HZOP.internal_flag = 'Y'
and hzr.subject_table_name = 'HZ_PARTIES'
and hzr.object_table_name = 'HZ_PARTIES'
and pvpp.PARTNER_LEVEL = pvac.attr_code_id(+)
order by x_partner.idx;
select hzp.party_name, hzp.city, hzp.state ,
hzp.postal_code, hzp.country, hzp.address1,
hzp.address2, hzp.address3, hzp.party_id,
pvpp.partner_id,
to_char(pvpp.OPPTY_LAST_OFFERED_DATE, 'YYYY-MM-DD HH:MM:SS'),
pvac.description,
pvpp.status active_flag
from hz_parties hzp,
pv_partner_profiles pvpp,
pv_attribute_codes_vl pvac,
(SELECT rownum idx, column_value
FROM (SELECT column_value FROM TABLE (CAST(p_matched_id AS JTF_NUMBER_TABLE)))) x_partner
where pvpp.partner_id = x_partner.column_value
and pvpp.partner_party_id = hzp.party_id
and pvpp.PARTNER_LEVEL = pvac.attr_code_id(+)
order by x_partner.idx;
select distinct party_name
from hz_relationships hzr,
hz_parties hzp,
hz_organization_profiles HZOP
where hzr.subject_id = hzp.party_id
and hzr.object_id = HZOP.party_id
and HZOP.internal_flag = 'Y'
and hzr.subject_table_name = 'HZ_PARTIES'
and hzr.object_table_name = 'HZ_PARTIES'
and hzr.party_id = lc_partner_id; */
'select /*+ leading(x_partner) use_nl(x_partner pvpp hzp pvac) */ ' ||
'hzp.party_name, hzp.city, hzp.state , '||
'hzp.postal_code, hzp.country, hzp.address1, hzp.address2, hzp.address3, '||
'hzp.party_id, pvpp.partner_id, pvac.description, '||
'to_char(pvpp.oppty_last_offered_date, ''YYYY-MM-DD HH:MI:SS''), '||
'pvpp.status active_flag ' ||
'from hz_parties hzp , pv_partner_profiles pvpp, pv_attribute_codes_tl pvac, '||
'(SELECT rownum idx, column_value FROM '||
'(SELECT column_value FROM TABLE (CAST(:1 AS JTF_NUMBER_TABLE)))) x_partner '||
'where pvpp.partner_id = x_partner.column_value ' ||
'and pvpp.partner_party_id = hzp.party_id ' ||
'and pvpp.PARTNER_LEVEL = pvac.attr_code_id(+) '||
'and pvac.language(+) = USERENV(''LANG'') ' ||
'order by x_partner.idx ';
p_matched_id.delete;
select wf_status, routing_status
from pv_lead_workflows
where lead_id = pc_lead_id
and latest_routing_flag = 'Y';
select
pr.relationship_type,
pr.object_id
from hz_relationships pr,
hz_parties pt
where pr.party_id = pc_party_rel_id
AND pr.subject_table_name = 'HZ_PARTIES'
AND pr.object_table_name = 'HZ_PARTIES'
AND pr.directional_flag = 'F'
and pr.subject_id = pt.party_id;
select PVLA.PARTNER_ID, PVPN.RESOURCE_ID, 'PN' source ,
PVLA.STATUS, to_char(PVLA.STATUS_DATE, 'YYYY-MM-DD HH:MM:SS'),
pvpn.DECISION_MAKER_FLAG
from PV_LEAD_ASSIGNMENTS PVLA,
PV_PARTY_NOTIFICATIONS PVPN,
PV_LEAD_WORKFLOWS PVLW
where pvlw.LEAD_ID = pc_lead_id
and pvlw.LATEST_ROUTING_FLAG = 'Y'
and pvlw.WF_ITEM_KEY = pvla.WF_ITEM_KEY
and pvlw.WF_ITEM_TYPE = pvla.WF_ITEM_TYPE
and PVLA.LEAD_ASSIGNMENT_ID = PVPN.LEAD_ASSIGNMENT_ID(+)
and PVPN.NOTIFICATION_TYPE(+) = 'MATCHED_TO'
ORDER BY PVLA.ASSIGN_SEQUENCE, PVLA.PARTNER_ID;
select asac.partner_customer_id ,
'SALESTEAM' source , access_id, 99999999999
from as_accesses asac
where asac.lead_id = pc_lead_id
and asac.sales_lead_id is null
and (asac.partner_cont_party_id is not null
or asac.partner_customer_id is not null )
union
select pvla.partner_id partner_id, pvla.source_type source
, lead_assignment_id ,pvla.assign_sequence
from pv_lead_assignments pvla
where pvla.lead_id = pc_lead_id
and pvla.status = 'UNASSIGNED'
order by 4;
delete from pv_lead_assignments
where lead_id = p_lead_id
and source_type = 'SALESTEAM'
and status = 'UNASSIGNED';
fnd_message.SET_NAME ('PV', 'Just Deleted' || SQL%ROWCOUNT);
select pvla.partner_id partner_id, pvla.ASSIGN_SEQUENCE, pvla.source_type
from pv_lead_assignments pvla
where pvla.lead_id = pc_lead_id
and pvla.status = 'UNASSIGNED';
FOR x IN ( SELECT count(party_id) cnt
FROM (SELECT rownum idx, column_value party_id
FROM (SELECT column_value
FROM TABLE (CAST(l_partner_id_tbl AS JTF_NUMBER_TABLE))
)
) a
WHERE a.party_id = l_partner_id
GROUP BY A.PARTY_ID )
LOOP
l_party_count := x.cnt;
delete from pv_lead_assignments
where lead_id = p_lead_id
and status = 'UNASSIGNED';