[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_header_rec.last_update_date := sysdate;
SELECT name
FROM jtf_task_statuses_vl
WHERE task_status_id = x_status_id
AND trunc(sysdate) BETWEEN
trunc(nvl(start_date_active, sysdate)) AND
trunc(nvl(end_date_active, sysdate));
SELECT name
FROM jtf_task_priorities_vl
WHERE task_priority_id = x_priority_id
AND trunc(sysdate) BETWEEN
trunc(nvl(start_date_active, sysdate)) AND
trunc(nvl(end_date_active, sysdate));
SELECT object_code, name
FROM jtf_objects_vl
WHERE object_code in ( SELECT object_code FROM jtf_object_usages WHERE object_user_code = 'RESOURCES' )
AND object_code = 'RS_EMPLOYEE';
SELECT user_name
FROM fnd_user
WHERE user_id = x_user_id;
SELECT subject_id, object_id
FROM hz_relationships
WHERE party_id = x_party_id
AND directional_flag = 'F'
AND status = 'A';
SELECT party_name
FROM Hz_parties
WHERE party_id = x_party_id;
SELECT party_site_id
FROM ast_locations_v
WHERE party_id = x_party_id
AND location_id = x_location_id;
SELECT contact_point_id
FROM hz_contact_points
WHERE owner_table_id = x_owner_table_id
and owner_table_name = 'HZ_PARTIES'
and contact_point_type = 'PHONE'
and status = 'A'
and primary_flag = 'Y';
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_creation_date IN DATE,
p_created_by IN NUMBER
)
IS
BEGIN
g_jtf_note_contexts_tab(p_counter).note_context_type := p_context_type;
g_jtf_note_contexts_tab(p_counter).last_update_date := p_last_update_date;
g_jtf_note_contexts_tab(p_counter).last_updated_by := p_last_updated_by;
g_jtf_note_contexts_tab(p_counter).last_update_login := p_last_update_login;
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_login IN NUMBER,
p_party_id IN NUMBER,
x_jtf_note_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;
SELECT contact_party_id
FROM as_lead_contacts
WHERE lead_id = p_opp_id
and primary_contact_flag = 'Y';
SELECT contact_party_id
FROM as_sales_lead_contacts
WHERE sales_lead_id = p_lead_id
and primary_contact_flag = 'Y';
SELECT distinct object_type_code, object_id
FROM jtf_task_references_b
WHERE task_id = p_task_id;
SELECT cust_account_id
FROM iex_delinquencies
WHERE delinquency_id = p_delinquency_id;
SELECT subject_id, object_id
FROM hz_relationships
WHERE party_id = p_party_id
AND directional_flag = 'F'
AND status = 'A';
g_jtf_note_contexts_tab.delete;
p_last_update_date, p_last_updated_by, p_last_update_login,
p_creation_date, p_created_by);
p_last_update_date, p_last_updated_by, p_last_update_login,
p_creation_date, p_created_by);
p_last_update_date, p_last_updated_by, p_last_update_login,
p_creation_date, p_created_by);
p_last_update_date, p_last_updated_by, p_last_update_login,
p_creation_date, p_created_by);
p_last_update_date, p_last_updated_by, p_last_update_login,
p_creation_date, p_created_by);
p_last_update_date, p_last_updated_by, p_last_update_login,
p_creation_date, p_created_by);
p_last_update_date, p_last_updated_by, p_last_update_login,
p_creation_date, p_created_by);
p_last_update_date, p_last_updated_by, p_last_update_login,
p_creation_date, p_created_by);
p_last_update_date, p_last_updated_by, p_last_update_login,
p_creation_date, p_created_by);
p_last_update_date, p_last_updated_by, p_last_update_login,
p_creation_date, p_created_by);
P_LAST_UPDATE_DATE => p_last_update_date,
P_LAST_UPDATED_BY => p_last_updated_by,
P_CREATION_DATE => p_creation_date,
P_CREATED_BY => p_created_by,
P_LAST_UPDATE_LOGIN => p_last_update_login,
X_JTF_NOTE_ID => x_jtf_note_id,
P_NOTE_TYPE => l_note_type,
P_NOTE_STATUS => l_note_status,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data,
P_JTF_NOTE_CONTEXTS_TAB => g_jtf_note_contexts_tab
);
p_last_update_date IN DATE,
p_lead_id IN NUMBER,
p_lead_number IN VARCHAR2,
p_description IN VARCHAR2,
p_status_code IN VARCHAR2,
p_source_promotion_id IN NUMBER,
p_customer_id IN NUMBER,
p_address_id IN NUMBER,
p_sales_stage_id IN NUMBER,
p_win_probability IN NUMBER,
p_total_amount IN NUMBER,
--New parameter added for R12 forecast amount enhancement
p_total_revenue_forecast_amt IN NUMBER,
p_channel_code IN VARCHAR2,
p_decision_date IN DATE,
p_currency_code IN VARCHAR2,
p_vehicle_response_code IN VARCHAR2,
p_customer_budget IN NUMBER,
--Code commented for R12 Enhancement --Start
/* p_close_competitor_code IN VARCHAR2,
p_close_competitor_id IN NUMBER,
p_close_competitor IN VARCHAR2, */
--Code commented for R12 Enhancement --End
p_close_comment IN VARCHAR2,
p_parent_project IN VARCHAR2,
p_freeze_flag IN VARCHAR2,
header_rec IN OUT NOCOPY AS_OPPORTUNITY_PUB.Header_Rec_type
)
AS
l_num NUMBER := AS_FOUNDATION_PUB.Get_Constant('FND_API.G_MISS_NUM');
l_last_update_date DATE;
l_sales_methodology_id NUMBER := fnd_profile.value('AS_SALES_METHODOLOGY'); -- Updated by Sumita for bug # 4100911
SELECT party_name
FROM hz_parties
WHERE party_id = p_close_party_id;
l_last_update_date := NVL(p_last_update_date, l_date);
header_rec.last_update_date := l_last_update_date;
p_last_update_date IN DATE,
p_lead_id IN NUMBER,
p_lead_number IN VARCHAR2,
p_description IN VARCHAR2,
p_status_code IN VARCHAR2,
-- Added by Sumita on 10.14.2004 for bug # 3812865
-- Adding source code as it is required while creating a lead if the profile OS: Source Code Required for Opportunity is set to 'yes' as we get the source
-- code from the view defined for bali for marketing list but in case of personal node - contacts, list is generated from the universal search where we
-- do not get the source code in the Bali.
p_source_code IN VARCHAR2,
p_source_code_id IN NUMBER,
-- End Mod.
p_customer_id IN NUMBER,
p_contact_party_id IN NUMBER,
p_address_id IN NUMBER,
p_sales_stage_id IN NUMBER,
p_win_probability IN NUMBER,
p_total_amount IN NUMBER,
p_total_revenue_forecast_amt IN NUMBER,
p_channel_code IN VARCHAR2,
p_decision_date IN DATE,
p_currency_code IN VARCHAR2,
p_vehicle_response_code IN VARCHAR2,
p_customer_budget IN NUMBER,
--Code commented for R12 Enhancement --Start
/* p_close_competitor_code IN VARCHAR2,
p_close_competitor_id IN NUMBER,
p_close_competitor IN VARCHAR2, */
--Code commented for R12 Enhancement --End
p_close_comment IN VARCHAR2,
p_parent_project IN VARCHAR2,
p_freeze_flag IN VARCHAR2,
p_salesgroup_id IN NUMBER,
p_called_node IN VARCHAR2,
p_action_key IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_lead_id OUT NOCOPY NUMBER
)
AS
l_api_version NUMBER := 2.0;
SELECT party_site_id
FROM hz_party_sites
WHERE party_id = p_customer_id
AND identifying_address_flag = 'Y'
AND status = 'A';
SELECT party_site_id
FROM ast_locations_v
WHERE party_id = x_party_id
AND location_id = x_location_id;
SELECT source_code_id
FROM Ams_source_codes
WHERE source_code = p_source_code;
p_last_update_date,
p_lead_id ,
p_lead_number,
p_description,
p_status_code,
s_source_code_id,
p_customer_id,
l_address_id,
p_sales_stage_id,
p_win_probability,
p_total_amount,
p_total_revenue_forecast_amt,
p_channel_code,
p_decision_date,
p_currency_code,
l_vehicle_response_code,
p_customer_budget,
--Code commented for R12 Enhancement --Start
/* p_close_competitor_code,
p_close_competitor_id,
p_close_competitor , */
--Code commented for R12 Enhancement --End
p_close_comment,
p_parent_project,
p_freeze_flag ,
header_rec
);
l_last_update_date DATE;
SELECT o.org_contact_id
FROM hz_relationships r,
hz_org_contacts o
WHERE o.party_relationship_id = r.relationship_id
AND r.party_id = X_PARTY_ID
AND r.directional_flag = 'F';
SELECT contact_point_id
FROM hz_contact_points
WHERE owner_table_id = x_owner_table_id
and owner_table_name = 'HZ_PARTIES'
and contact_point_type = 'PHONE'
and primary_flag = 'Y';
SELECT LEAD_NUMBER FROM AS_SALES_LEADS
WHERE SALES_LEAD_ID = p_sales_lead_id;
SELECT SOURCE_NAME, SOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID = p_resource_id;
SELECT party_site_id
FROM hz_party_sites
WHERE party_id = p_customer_id
AND identifying_address_flag = 'Y'
AND status = 'A';
SELECT party_site_id
FROM ast_locations_v
WHERE party_id = x_party_id
AND location_id = x_location_id;
SELECT source_code_id
FROM Ams_source_codes
WHERE source_code = p_source_code;
SELECT access_id, last_update_datE
FROM as_accesses_all
WHERE sales_lead_id = x_sales_lead_id;
G_Access_Rec_Type.mgr_update_profile_value := nvl(FND_PROFILE.VALUE('AS_MGR_UPDATE'),'R');
G_Access_Rec_Type.admin_update_profile_value := nvl(FND_PROFILE.VALUE('AS_ADMIN_UPDATE'),'R');
l_sales_lead_profile_tbl(4).profile_name := 'AS_MGR_UPDATE';
l_sales_lead_profile_tbl(4).profile_value := G_access_rec_type.mgr_update_profile_value;
l_sales_lead_profile_tbl(5).profile_name := 'AS_ADMIN_UPDATE';
l_sales_lead_profile_tbl(5).profile_value := G_access_rec_type.admin_update_profile_value;
FETCH c_get_access_id INTO l_access_id, l_last_update_date;
l_salesteam_rec.last_update_date := l_last_update_date;
AS_ACCESS_PUB.Update_SalesTeam
(
p_api_version_number => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => l_validation_level,
p_access_profile_rec => G_Access_Rec_Type,
p_check_access_flag => 'Y',
p_admin_flag => l_admin_flag,
p_admin_group_id => p_admin_group_id,
p_identity_salesforce_id => p_identity_salesforce_id,
p_sales_team_rec => l_salesteam_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_access_id => v_access_id
);
PROCEDURE update_lead (
p_sales_lead_id IN NUMBER := FND_API.G_MISS_NUM,
p_admin_group_id IN NUMBER := FND_API.G_MISS_NUM,
p_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
p_last_update_date IN DATE,
p_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_customer_id IN NUMBER := FND_API.G_MISS_NUM,
p_address_id IN NUMBER := FND_API.G_MISS_NUM,
p_assign_to_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
p_admin_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_assign_sales_group_id IN NUMBER := FND_API.G_MISS_NUM,
p_budget_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_source_promotion_id IN NUMBER := FND_API.G_MISS_NUM,
p_lead_rank_id IN NUMBER := FND_API.G_MISS_NUM,
p_decision_timeframe_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_initiating_contact_id IN NUMBER := FND_API.G_MISS_NUM,
p_accept_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_qualified_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_phone_id IN NUMBER := FND_API.G_MISS_NUM,
p_close_reason_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_called_node IN VARCHAR2 := FND_API.G_MISS_CHAR,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version NUMBER := 2.0;
SELECT SOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID = p_resource_id;
l_sales_lead_rec.last_update_date := p_last_update_date;
G_Access_Rec_Type.mgr_update_profile_value := nvl(FND_PROFILE.VALUE('AS_MGR_UPDATE'),'R');
G_Access_Rec_Type.admin_update_profile_value := nvl(FND_PROFILE.VALUE('AS_ADMIN_UPDATE'),'R');
l_sales_lead_profile_tbl(4).profile_name := 'AS_MGR_UPDATE';
l_sales_lead_profile_tbl(4).profile_value := G_access_rec_type.mgr_update_profile_value;
l_sales_lead_profile_tbl(5).profile_name := 'AS_ADMIN_UPDATE';
l_sales_lead_profile_tbl(5).profile_value := G_access_rec_type.admin_update_profile_value;
AS_SALES_LEADS_PUB.update_sales_lead(
P_API_VERSION_NUMBER => l_api_version
,P_INIT_MSG_LIST => l_init_msg_list
,P_COMMIT => l_commit
,P_VALIDATION_LEVEL => l_validation_level
,P_CHECK_ACCESS_FLAG => 'N'
,P_ADMIN_FLAG => l_admin_flag
,P_ADMIN_GROUP_ID => p_admin_group_id
,P_IDENTITY_SALESFORCE_ID => p_identity_salesforce_id
,P_SALES_LEAD_PROFILE_TBL => l_sales_lead_profile_tbl
,P_SALES_LEAD_REC => l_sales_lead_rec
,X_RETURN_STATUS => x_return_status
,X_MSG_COUNT => x_msg_count
,X_MSG_DATA => x_msg_data
);
/** Adding this to fix bug 2918647. We should call Lead_Process_After_Update
after update_sales_lead.
**/
AS_SALES_LEADS_PUB.Lead_Process_After_Update(
P_Api_Version_Number => l_api_version
,P_Init_Msg_List => l_init_msg_list
,P_Commit => l_commit
,P_Validation_Level => l_validation_level
,P_check_access_flag => 'N'
,P_admin_flag => l_admin_flag
,P_Admin_Group_Id => p_admin_group_id
,P_identity_salesforce_id => p_identity_salesforce_id
,P_SalesGroup_Id => l_assign_sales_group_id
,P_Sales_Lead_Id => p_sales_lead_id
,X_Return_Status => x_return_status
,X_Msg_Count => x_msg_count
,X_Msg_Data => x_msg_data
);
G_Access_Rec_Type.mgr_update_profile_value := nvl(FND_PROFILE.VALUE('AS_MGR_UPDATE'),'R');
G_Access_Rec_Type.admin_update_profile_value := nvl(FND_PROFILE.VALUE('AS_ADMIN_UPDATE'),'R');
l_sales_lead_profile_tbl(4).profile_name := 'AS_MGR_UPDATE';
l_sales_lead_profile_tbl(4).profile_value := G_access_rec_type.mgr_update_profile_value;
l_sales_lead_profile_tbl(5).profile_name := 'AS_ADMIN_UPDATE';
l_sales_lead_profile_tbl(5).profile_value := G_access_rec_type.admin_update_profile_value;
p_last_update_date IN DATE,
p_new_sales_group_id IN NUMBER,
p_new_owner_id IN NUMBER, --person_id of new owner
p_called_node IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
as
l_old_access_id NUMBER; --remove
G_Access_Rec_Type.mgr_update_profile_value := nvl(FND_PROFILE.VALUE('AS_MGR_UPDATE'),'R');
G_Access_Rec_Type.admin_update_profile_value := nvl(FND_PROFILE.VALUE('AS_ADMIN_UPDATE'),'R');
x_update_access_flag => l_access_flag
);
Update the sales lead by assigning it to new owner. Then call
Rebuild_Lead_Sales_Team to insert a row into as_accesses_all if needed.
**/
ast_uwq_wrapper_pkg.update_lead(
p_sales_lead_id => p_sales_lead_id,
p_admin_group_id => p_admin_group_id,
p_identity_salesforce_id => p_resource_id,
p_last_update_date => p_last_update_date,
p_assign_to_salesforce_id => p_new_salesforce_id,
p_admin_flag => l_admin_flag,
p_assign_sales_group_id => p_new_sales_group_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT customer_id, source_promotion_id
FROM as_sales_leads
WHERE sales_lead_id = p_sales_lead_id;
l_sales_lead_profile_tbl(4).profile_name := 'AS_MGR_UPDATE';
l_sales_lead_profile_tbl(4).profile_value := G_Access_Rec_Type.mgr_update_profile_value;
l_sales_lead_profile_tbl(5).profile_name := 'AS_ADMIN_UPDATE';
l_sales_lead_profile_tbl(5).profile_value := G_Access_Rec_Type.admin_update_profile_value;
PROCEDURE update_opportunity (
p_admin_flag IN VARCHAR2,
p_admin_group_id IN NUMBER,
p_resource_id IN NUMBER,
p_last_update_date IN DATE,
p_lead_id IN NUMBER,
p_lead_number IN VARCHAR2,
p_description IN VARCHAR2,
p_status_code IN VARCHAR2,
p_close_reason_code IN VARCHAR2,
p_source_promotion_id IN NUMBER,
p_customer_id IN NUMBER,
p_contact_party_id IN NUMBER,
p_address_id IN NUMBER,
p_sales_stage_id IN NUMBER,
p_win_probability IN NUMBER,
p_total_amount IN NUMBER,
p_total_revenue_forecast_amt IN NUMBER, --added for R12
p_channel_code IN VARCHAR2,
p_decision_date IN DATE,
p_currency_code IN VARCHAR2,
p_vehicle_response_code IN VARCHAR2,
p_customer_budget IN NUMBER,
--Code commented for R12 Enhancement --Start
/* p_close_competitor_code IN VARCHAR2,
p_close_competitor_id IN NUMBER,
p_close_competitor IN VARCHAR2, */
--Code commented for R12 Enhancement --End
p_close_comment IN VARCHAR2,
p_parent_project IN VARCHAR2,
p_freeze_flag IN VARCHAR2,
p_called_node IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_lead_id OUT NOCOPY NUMBER
)
AS
l_api_version NUMBER := 2.0;
p_last_update_date,
p_lead_id ,
p_lead_number,
p_description,
p_status_code,
p_source_promotion_id,
p_customer_id,
p_address_id,
p_sales_stage_id,
p_win_probability,
p_total_amount,
p_total_revenue_forecast_amt, --added for R12
p_channel_code,
p_decision_date,
p_currency_code,
p_vehicle_response_code,
p_customer_budget,
--Code commented for R12 Enhancement --Start
/* p_close_competitor_code,
p_close_competitor_id,
p_close_competitor , */
--Code commented for R12 Enhancement --End
p_close_comment,
p_parent_project,
p_freeze_flag ,
header_rec
);
AS_BUSINESS_EVENT_PUB.before_Oppty_update(
p_api_version_number => l_api_version,
p_init_msg_list=>l_init_msg_list,
p_commit=>l_commit_BE,
p_validation_level=>l_valid_level_full,
p_lead_id=>p_lead_id,
x_return_status=>x_return_status,
x_msg_count=>x_msg_count,
x_msg_data=>x_msg_data,
x_event_key=>x_event_key);
AS_OPPORTUNITY_PUB.Update_Opp_Header
(
p_api_version_number => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => l_valid_level_full,
p_header_rec => header_rec,
p_check_access_flag => 'N',
p_admin_flag => l_admin_flag,
p_admin_group_id => p_admin_group_id,
p_identity_salesforce_id => p_resource_id,
p_profile_tbl => v_profile_tbl,
p_partner_cont_party_id => null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_lead_id => x_lead_id
);
AS_BUSINESS_EVENT_PUB.Update_oppty_post_event(
p_api_version_number => l_api_version,
p_init_msg_list=>l_init_msg_list,
p_commit=>l_commit,
p_validation_level=>l_valid_level_full,
p_lead_id=>p_lead_id,
p_event_key=>x_event_key,
x_return_status=>x_return_status,
x_msg_count=>x_msg_count,
x_msg_data=>x_msg_data);
dbms_output.put_line('Update Opp: There are ' || l_count || ' messages.');
END update_opportunity;