[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_opp_selection_tab t_opp_selection_tab
)
RETURN NUMBER;
PROCEDURE Opportunity_Selection
(
p_api_version 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_entity_id IN NUMBER,
p_entity IN VARCHAR2,
p_user_name IN VARCHAR2 := NULL,
p_resource_id IN NUMBER := NULL,
x_selected_rule_id OUT NOCOPY NUMBER,
x_matched_partner_count OUT NOCOPY NUMBER,
x_failure_code OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name VARCHAR2(30) := 'Opportunity_Selection';
Opportunity_Selection(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_entity_id => p_entity_id,
p_entity => p_entity,
p_user_name => p_user_name,
p_resource_id => p_resource_id,
p_routing_flag => 'Y',
x_partner_cnt => l_partner_cnt,
x_partner_details => l_partner_details,
x_flagcount => l_flagcount,
x_distance_tbl => l_distance_tbl,
x_distance_uom_returned => l_distance_uom_returned,
x_selected_rule_id => x_selected_rule_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
END Opportunity_Selection;
PROCEDURE Opportunity_Selection(
p_api_version 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_entity_id IN NUMBER,
p_entity IN VARCHAR2,
p_user_name IN VARCHAR2 := NULL,
p_resource_id IN NUMBER := NULL,
p_routing_flag IN VARCHAR2 := 'N',
x_partner_cnt OUT NOCOPY NUMBER,
x_partner_details OUT NOCOPY JTF_VARCHAR2_TABLE_4000,
x_flagcount OUT NOCOPY JTF_VARCHAR2_TABLE_100,
x_distance_tbl OUT NOCOPY JTF_NUMBER_TABLE,
x_distance_uom_returned OUT NOCOPY VARCHAR2,
x_selected_rule_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version NUMBER := 1.0;
l_api_name VARCHAR2(30) := 'Opportunity_Selection';
l_count := g_opp_selection_tab.COUNT;
Debug('Opportunity Rule Selection Starts....................................');
Debug('Rule # :::' || g_opp_selection_tab(i).process_rule_id);
l_attribute_id := g_opp_selection_tab(i).attribute_id;
i := Get_Next_Rule_Index(i, g_opp_selection_tab);
-- the attribute value specified in the opportunity selection.
-- ---------------------------------------------------------------------------
l_matched := FALSE;
IF (g_opp_selection_tab(i).count = 1) THEN
-- ------------------------------------------------------------------------
-- Use operator to do the match. If the match fails, go to the next rule
-- until all rules are exhausted.
-- If the match succeeds, check if last_attr_flag = TRUE. If yes, there's
-- a match. set l_stop_flag = TRUE
-- ------------------------------------------------------------------------
IF (g_rule_engine_trace_flag = 'Y') OR FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
Debug('Calling Check_Match...AND LOGIC...');
--Debug('p_rule_attr_value: ' || g_opp_selection_tab(i).attribute_value);
l_attr_val_temp := g_opp_selection_tab(i).attribute_value;
Debug('p_operator: ' || g_opp_selection_tab(i).operator);
p_rule_attr_value => g_opp_selection_tab(i).attribute_value,
p_rule_to_attr_value => g_opp_selection_tab(i).attribute_to_value,
p_operator => g_opp_selection_tab(i).operator,
p_input_filter => l_input_filter,
p_delimiter => l_delimiter,
p_return_type => l_entity_attr_value(l_attribute_id).return_type,
p_rule_currency_code => g_opp_selection_tab(i).currency_code
);
l_stop_at_index := i + g_opp_selection_tab(i).count - 1;
g_opp_selection_tab(j).attribute_value || l_delimiter;
g_opp_selection_tab(j).attribute_to_value || l_delimiter;
Debug('p_operator: ' || g_opp_selection_tab(i).operator);
p_operator => g_opp_selection_tab(i).operator,
p_input_filter => l_input_filter,
p_delimiter => l_delimiter,
p_return_type => l_entity_attr_value(l_attribute_id).return_type,
p_rule_currency_code => g_opp_selection_tab(i).currency_code
);
i := Get_Next_Rule_Index(i, g_opp_selection_tab);
IF (g_opp_selection_tab(i).last_attr_flag = 'Y') THEN
IF (l_matching_type = 'STOP_AT_FIRST_RULE') THEN
l_stop_flag := TRUE;
x_selected_rule_id := g_opp_selection_tab(i).process_rule_id;
Debug('%%%%%%%Selected Rule ID: ' || x_selected_rule_id);
Debug('Calling Partner_Selection................................');
Partner_Selection(
p_api_version => 1.0,
p_process_rule_id => x_selected_rule_id,
p_entity_id => p_entity_id,
p_entity => p_entity,
p_user_name => p_user_name,
p_resource_id => p_resource_id,
p_routing_flag => p_routing_flag,
x_partner_cnt => l_partner_cnt,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
-- Log the selected rules if the matching engine type is
-- 'BACKGROUND PARTNER MATCHING'. This provides a snapshot of what
-- actually occurred in partner matching.
-- ------------------------------------------------------------
IF (g_matching_engine_type = 'BACKGROUND_PARTNER_MATCHING') THEN
IF (l_partner_cnt > 0) THEN
l_winning_rule_flag := 'Y';
PV_ENTITY_RULES_APPLIED_PKG.Insert_Row(
px_ENTITY_RULE_APPLIED_ID => l_entity_rule_applied_id,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => p_resource_id,
p_CREATION_DATE => SYSDATE,
p_CREATED_BY => p_resource_id,
p_LAST_UPDATE_LOGIN => p_resource_id,
p_OBJECT_VERSION_NUMBER => 1,
p_REQUEST_ID => FND_API.G_MISS_NUM,
p_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
p_PROGRAM_ID => FND_API.G_MISS_NUM,
p_PROGRAM_UPDATE_DATE => SYSDATE,
p_ENTITY => p_entity,
p_ENTITY_ID => p_entity_id,
p_PROCESS_RULE_ID => x_selected_rule_id,
p_PARENT_PROCESS_RULE_ID => FND_API.G_MISS_NUM,
p_LATEST_FLAG => FND_API.G_MISS_CHAR,
p_ACTION_VALUE => FND_API.G_MISS_CHAR,
p_PROCESS_TYPE => 'BACKGROUND_PARTNER_MATCHING',
p_WINNING_RULE_FLAG => l_winning_rule_flag,
p_entity_detail => FND_API.G_MISS_CHAR,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_PROCESS_STATUS => FND_API.G_MISS_CHAR
);
END Opportunity_Selection;
PROCEDURE Partner_Selection(
p_api_version 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_process_rule_id IN NUMBER,
p_entity_id IN NUMBER,
p_entity IN VARCHAR2,
p_user_name IN VARCHAR2 := NULL,
p_resource_id IN NUMBER := NULL,
p_routing_flag IN VARCHAR2,
x_partner_cnt OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
-- -------------------------------------------------------------------------
-- Cursor for retrieving partner-to-opportunity mapping for a process rule.
-- -------------------------------------------------------------------------
CURSOR lc_partner_mapping IS
SELECT a.source_attr_id, a.target_attr_id, a.operator, b.return_type
FROM pv_entity_attr_mappings a,
pv_attributes_vl b
WHERE a.target_attr_id = b.attribute_id AND
a.process_rule_id = p_process_rule_id AND
a.source_attr_type = 'LEAD' AND
--a.source_attr_type = 'OPPORTUNITY' AND
a.target_attr_type = 'PARTNER';
CURSOR lc_partner_selection IS
SELECT a.attribute_id, a.operator,
b.attribute_value, b.attribute_to_value,
a.selection_criteria_id,
c.return_type
FROM pv_enty_select_criteria a,
pv_selected_attr_values b,
pv_attributes_vl c
WHERE a.attribute_id = c.attribute_id AND
a.selection_criteria_id = b.selection_criteria_id (+) AND
a.selection_type_code = 'PARTNER_SELECTION' AND
a.process_rule_id = p_process_rule_id
ORDER BY a.attribute_id, b.selection_criteria_id;
CURSOR lc_entity_routings (p_selected_rule_id IN NUMBER) IS
SELECT entity_routing_id, max_nearest_partner, distance_from_customer,
distance_uom_code, routing_type,
NVL(bypass_cm_ok_flag, 'N') bypass_cm_ok_flag
FROM pv_entity_routings
WHERE process_rule_id = p_selected_rule_id;
SELECT b.location_id
FROM as_leads_all a,
hz_party_sites b,
hz_locations l
WHERE a.lead_id = p_entity_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 process_rule_name
FROM pv_process_rules_vl
WHERE process_rule_id = p_process_rule_id;
l_api_name VARCHAR2(30) := 'Partner_Selection';
Debug('***Rule ID Selected Is: ' || p_process_rule_id || '***');
SELECT currency_code
INTO l_rule_currency_code
FROM pv_process_rules_b
WHERE process_rule_id = p_process_rule_id;
Debug('Appending Partner Selection Attributes...');
FOR x IN lc_partner_selection LOOP
IF (l_previous_attr_id = x.attribute_id AND
l_previous_sc_id = x.selection_criteria_id)
THEN
l_attr_value_tbl(i - 1) := l_attr_value_tbl(i - 1) ||
l_delimiter || x.attribute_value;
l_previous_sc_id := x.selection_criteria_id;
SELECT resource_id
INTO l_resource_id
FROM fnd_user a, jtf_rs_resource_extns b
WHERE a.user_id = b.user_id AND
a.user_name = p_user_name;
p_attr_selection_mode => 'OR',
p_att_delmter => l_delimiter,
p_selection_criteria => 'ALL',
p_resource_id => l_resource_id,
p_lead_id => p_entity_id,
p_auto_match_flag => 'N',
x_matched_id => x_partner_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
END Partner_Selection;
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_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;
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_matched_id IN OUT NOCOPY JTF_NUMBER_TABLE,
p_distance_tbl IN JTF_NUMBER_TABLE,
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) := 'Get_Matched_Partner_Details';
SELECT asla.INCUMBENT_PARTNER_PARTY_ID
FROM as_leads_all asla
WHERE asla.lead_id = pc_lead_id;
SELECT DECODE(status
,'PT_REJECTED','Y'
,'PT_ABANDONED','Y'
,'PT_TIMEOUT','Y'
,'N')
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;
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; */
INSERT INTO pv_matched_partners_gt
VALUES (
l_partner_id_tbl(i)
,p_distance_tbl(i)
,x_recycled_flag_tbl(i)
,x_incumbent_flag_tbl(i)
,l_order_table(i)
,l_industry_table(i)
,l_membertype_table(i)
,l_geography_table(i)
,l_capacityrating_table(i)
);
INSERT INTO pv_matched_partners_gt
(
PARTNER_ID
,RECYCLED_FLAG
,INCUMBENT_FLAG
,PARTNER_ORDER
,INDUSTRY
,MEMBER_TYPE
,GEOGRAPHY
,CAPACITY_RATING
)
VALUES (
l_partner_id_tbl(i)
,x_recycled_flag_tbl(i)
,x_incumbent_flag_tbl(i)
,l_order_table(i)
,l_industry_table(i)
,l_membertype_table(i)
,l_geography_table(i)
,l_capacityrating_table(i)
);
SELECT a.attribute_id, a.operator, b.return_type
FROM pv_enty_select_criteria a,
pv_attributes_vl b
WHERE a.process_rule_id = p_process_rule_id AND
UPPER(a.selection_type_code) = 'TIE_BREAKING' AND
a.attribute_id = b.attribute_id
ORDER BY a.rank;
SELECT b.party_id,
DECODE(a.attr_text, NULL, TO_CHAR(a.attr_value), a.attr_text) attribute_value
FROM pv_search_attr_values a,
hz_parties b
WHERE a.party_id (+) = b.party_id AND
a.attribute_id (+) = l_attribute_id AND
b.party_id IN (
SELECT * FROM TABLE (CAST(x_partner_tbl AS JTF_NUMBER_TABLE))
)
ORDER BY b.party_id;
' select ' ||
' c.party_id, '||
' (select decode(a.attr_text, null, to_char(a.attr_value), ' ||
' a.attr_text) from pv_search_attr_values a where a.party_id = c.party_id and ' ||
' a.attribute_id = :1) attribute_value, ' ||
' c.idx ' ||
' from ' ||
' (select * from (select rownum idx, ' ||
' column_value party_id from (select column_value from table (cast(:2 as ' ||
' jtf_number_table))))) c ' ||
' order by c.idx ' ;
' SELECT ' ||
' b.party_id, ' ||
' DECODE(a.attr_text, NULL, TO_CHAR(a.attr_value), a.attr_text) attribute_value, ' ||
' c.idx ' ||
' FROM pv_search_attr_values a, ' ||
' hz_parties b, ' ||
' (SELECT * ' ||
' FROM (SELECT rownum idx, column_value party_id ' ||
' FROM (SELECT column_value ' ||
' FROM TABLE (CAST(:1 AS JTF_NUMBER_TABLE))))) c ' ||
' WHERE a.party_id (+) = b.party_id AND ' ||
' a.attribute_id (+) = :2 AND ' ||
' b.party_id = c.party_id ' ||
' ORDER BY c.idx ' ;
(SELECT *
FROM (SELECT rownum idx, column_value party_id
FROM (SELECT column_value
FROM TABLE (CAST(x_partner_tbl AS JTF_NUMBER_TABLE))))) c
WHERE a.party_id (+) = b.party_id AND
a.attribute_id (+) = l_attribute_id AND
b.party_id = c.party_id
ORDER BY c.idx;
SELECT *
INTO l_dup_count
FROM (
SELECT COUNT(*)
FROM THE (SELECT CAST(l_tie_breaking_tab AS PV_TIE_BREAKING_TBL)
FROM dual) a
WHERE ROWNUM < 2
GROUP BY concat_value_str
HAVING COUNT(*) > 1) b;
SELECT *
FROM THE (SELECT CAST(l_tie_breaking_tab AS PV_TIE_BREAKING_TBL)
FROM dual) a
ORDER BY concat_value_str DESC)
LOOP
x_partner_tbl(l_index) := x.party_id;
SELECT *
FROM THE (SELECT CAST(l_tie_breaking_tab AS PV_TIE_BREAKING_TBL)
FROM dual) a
ORDER BY concat_value_str DESC, idx ASC)
LOOP
x_partner_tbl(l_index) := x.party_id;
SELECT pac.description
FROM pv_attribute_codes_vl pac,
pv_enty_attr_values peav
WHERE peav.entity_id = pc_partner_id
AND peav.entity(+) = 'PARTNER'
AND peav.attribute_id(+) = pc_attribute_id
AND peav.attribute_id = pac.attribute_id(+)
AND pac.attr_code(+) = peav.attr_value
AND peav.latest_flag = 'Y'
AND peav.enabled_flag = 'Y';
'SELECT pav.return_type, pav.display_style, psav.attr_value,psav.attr_text, pav.decimal_points ' ||
'FROM pv_attributes_vl pav, ' ||
' pv_search_attr_values psav ' ||
'WHERE psav.PARTY_ID = :1 ' ||
'AND psav.attribute_id = :2 ' ||
'AND psav.attribute_id = pav.attribute_id ' ;
'SELECT pav.return_type, pav.display_style, psav.attr_value,psav.attr_text, pav.decimal_points ' ||
'FROM pv_attributes_vl pav, ' ||
' pv_search_attr_values psav ' ||
'WHERE psav.PARTY_ID = :1 ' ||
'AND psav.attribute_id = :2 ' ||
'AND psav.attribute_id = pav.attribute_id ' ;
SELECT DECODE(pla.status,'MATCH_WITHDRAWN','UNASSIGNED'
,'MATCH_WITHDRAWN','UNASSIGNED'
,'OFFER_WITHDRAWN','UNASSIGNED'
,'PT_TIMEOUT','UNASSIGNED'
,'ACTIVE_WITHDRAWN','UNASSIGNED'
,'LOST_CHANCE','UNASSIGNED'
,'CM_ADDED','ASSIGNED'
,'PT_REJECTED','CM_REJECTED'
,'CM_BYPASSED','CM_APPROVED'
,'CM_TIMEOUT','CM_APPROVED'
,'CM_APP_FOR_PT','PT_APPROVED'
,'CM_ADD_APP_FOR_PT','PT_APPROVED'
,'PT_CREATED','PT_APPROVED'
,pla.status) assign_status
, plw.wf_status
, plw.routing_status
, pla.status
, plw.routing_type
, pla.wf_item_key
, pla.assign_sequence
FROM pv_lead_assignments pla,
pv_lead_workflows plw
WHERE plw.lead_id = pc_lead_id
AND plw.wf_item_key = pla.wf_item_key
AND plw.wf_item_type = pla.wf_item_type
AND pla.partner_id = pc_partner_id
AND plw.latest_routing_flag = 'Y';
SELECT MEANING
FROM PV_LOOKUPS
WHERE LOOKUP_TYPE = 'PV_ASN_ASSIGNMENT_STATUS'
AND LOOKUP_CODE = pc_status;
SELECT wf_engine.GetItemAttrNumber( 'PVASGNMT',p_wf_item_key,'PV_SERIAL_RANK_ATTR')
FROM dual;
SELECT pac.description
FROM pv_attribute_codes_vl pac,
pv_enty_attr_values peav
WHERE peav.entity_id = pc_partner_id
AND peav.entity(+) = 'PARTNER'
AND peav.attribute_id(+) = 3
AND peav.attribute_id = pac.attribute_id(+)
AND pac.attr_code(+) = peav.attr_value
AND peav.latest_flag = 'Y'
AND peav.enabled_flag = 'Y'
AND peav.attr_value_extn = 'Y' ;
SELECT 'Y'
FROM as_leads_all
WHERE lead_id = pc_lead_id
AND incumbent_partner_party_id = pc_partner_id;
SELECT 'N'
FROM pv_party_notifications
WHERE lead_assignment_id = pc_lead_assign_id
AND wf_item_type = 'PVASGNMT'
AND wf_item_key = pc_wf_item_key
--AND notification_type = 'MATCHED_TO'
AND user_id = FND_GLOBAL.user_id;
SELECT routing_status
FROM pv_lead_workflows
WHERE wf_item_key = pc_wf_item_key
AND wf_item_type = 'PVASGNMT'
AND latest_routing_flag = 'Y';
SELECT jtfre.category
FROM jtf_rs_resource_extns jtfre
WHERE jtfre.user_id = FND_GLOBAL.user_id
AND start_date_active <= sysdate
AND (end_date_active IS NULL OR end_date_active > sysdate);
g_opp_selection_tab.DELETE;
p_opp_selection_tab t_opp_selection_tab
)
RETURN NUMBER
IS
i NUMBER := p_current_index;
l_current_rule_id NUMBER := p_opp_selection_tab(p_current_index).process_rule_id;
l_last_index NUMBER := p_opp_selection_tab.LAST;
WHILE ((p_opp_selection_tab(i).process_rule_id = l_current_rule_id) AND
(i < l_last_index))
LOOP
i := i + 1;
(p_opp_selection_tab(i).process_rule_id = l_current_rule_id))
THEN
i := g_no_more_rules;
CURSOR lc_opp_selection IS
SELECT prr.process_rule_id,
prr.rank,
pesc.attribute_id,
prr.currency_code,
pesc.operator,
pesc.selection_criteria_id,
psav.attribute_value,
psav.attribute_to_value
FROM pv_process_rules_vl prr,
pv_enty_select_criteria pesc,
pv_selected_attr_values psav
WHERE prr.process_rule_id = pesc.process_rule_id AND
pesc.selection_criteria_id = psav.selection_criteria_id (+) AND
prr.status_code = 'ACTIVE' AND
TRUNC(SYSDATE) BETWEEN prr.start_date AND
NVL(prr.end_date, to_DATE('31-12-4000', 'DD-MM-YYYY')) AND
pesc.selection_type_code = 'OPPORTUNITY_SELECTION'
ORDER BY prr.rank DESC, prr.process_rule_id, pesc.attribute_id,
pesc.selection_criteria_id;
IF (g_opp_selection_tab.COUNT = 0) THEN
FOR l_opp_selection IN lc_opp_selection LOOP
g_opp_selection_tab(i).rank := l_opp_selection.rank;
g_opp_selection_tab(i).process_rule_id := l_opp_selection.process_rule_id;
g_opp_selection_tab(i).attribute_id := l_opp_selection.attribute_id;
g_opp_selection_tab(i).currency_code := l_opp_selection.currency_code;
g_opp_selection_tab(i).operator := l_opp_selection.operator;
g_opp_selection_tab(i).selection_criteria_id := l_opp_selection.selection_criteria_id;
g_opp_selection_tab(i).attribute_value := l_opp_selection.attribute_value;
g_opp_selection_tab(i).attribute_to_value := l_opp_selection.attribute_to_value;
l_count := g_opp_selection_tab.COUNT;
g_opp_selection_tab(i).last_attr_flag := 'Y';
g_opp_selection_tab(j).count := l_or_count;
l_next_index := g_opp_selection_tab.NEXT(i);
l_next_rule_id := g_opp_selection_tab(l_next_index).process_rule_id;
l_next_attribute_id := g_opp_selection_tab(l_next_index).attribute_id;
l_next_sc_id := g_opp_selection_tab(l_next_index).selection_criteria_id;
IF (l_next_rule_id <> g_opp_selection_tab(i).process_rule_id) THEN
g_opp_selection_tab(i).last_attr_flag := 'Y';
g_opp_selection_tab(i).last_attr_flag := 'N';
IF (l_next_rule_id = g_opp_selection_tab(i).process_rule_id) AND
(l_next_attribute_id = g_opp_selection_tab(i).attribute_id) AND
(l_next_sc_id = g_opp_selection_tab(i).selection_criteria_id)
THEN
l_or_count := l_or_count + 1;
g_opp_selection_tab(j).count := l_or_count;
l_count := g_opp_selection_tab.COUNT;
Debug(g_opp_selection_tab(i).rank || '::' ||
g_opp_selection_tab(i).process_rule_id || '::' ||
g_opp_selection_tab(i).attribute_id || '::' ||
g_opp_selection_tab(i).currency_code || '::' ||
g_opp_selection_tab(i).operator || '::' ||
g_opp_selection_tab(i).selection_criteria_id || '::' ||
g_opp_selection_tab(i).attribute_value || '::' ||
g_opp_selection_tab(i).attribute_to_value || '::' ||
g_opp_selection_tab(i).last_attr_flag || '::' ||
g_opp_selection_tab(i).count
);