[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_tbl => l_partner_tbl,
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_tbl OUT NOCOPY JTF_NUMBER_TABLE,
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);
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,
p_incumbent_partner_only => 'N',
x_partner_tbl => x_partner_tbl,
x_partner_details => x_partner_details,
x_flagcount => x_flagcount,
x_distance_tbl => x_distance_tbl,
x_distance_uom_returned => x_distance_uom_returned,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
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
);
Debug('Calling Partner_Selection for incumbent partner only......');
Partner_Selection(
p_api_version => 1.0,
p_process_rule_id => null,
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,
p_incumbent_partner_only => 'Y',
x_partner_tbl => x_partner_tbl,
x_partner_details => x_partner_details,
x_flagcount => x_flagcount,
x_distance_tbl => x_distance_tbl,
x_distance_uom_returned => x_distance_uom_returned,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
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,
p_incumbent_partner_only IN VARCHAR2 := 'N',
x_partner_tbl OUT NOCOPY JTF_NUMBER_TABLE,
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_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
);
SELECT INCUMBENT_PARTNER_PARTY_ID
INTO l_preferred_partner_party_id
FROM as_leads_all asla
WHERE lead_id = p_entity_id;
SELECT idx
FROM (SELECT rownum idx, column_value party_id
FROM (SELECT column_value
FROM TABLE (CAST(x_partner_tbl AS JTF_NUMBER_TABLE)))) a
WHERE a.party_id = l_preferred_partner_party_id)
LOOP
l_preferred_idx := x.idx;
SELECT a.user_name
INTO l_user_name
FROM fnd_user a, jtf_rs_resource_extns b
WHERE b.resource_id = p_resource_id AND
a.user_id = b.user_id;
END Partner_Selection;
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 /*+ leading(c) */ ' ||
' 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(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 *
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;
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
);
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;