[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_sales_referral_lead(
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_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
P_SALES_LEAD_Rec IN AS_SALES_LEADS_PUB.SALES_LEAD_Rec_Type
DEFAULT AS_SALES_LEADS_PUB.G_MISS_SALES_LEAD_REC,
p_overriding_usernames IN t_overriding_usernames,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 2.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_sales_referral_lead';
SELECT lead.referred_by, lead.referral_status
FROM AS_SALES_LEADS LEAD
WHERE LEAD.SALES_LEAD_ID = pc_sales_lead_id;
SAVEPOINT Update_sales_referral_lead;
AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': update');
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => P_SALES_LEAD_Rec.sales_lead_id,
p_created_by => fnd_global.user_id,
p_creation_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
p_request_id => P_SALES_LEAD_Rec.request_id,
p_program_application_id => P_SALES_LEAD_Rec.program_application_id,
p_program_id => P_SALES_LEAD_Rec.program_id,
p_program_update_date => P_SALES_LEAD_Rec.program_update_date,
p_status_code => P_SALES_LEAD_Rec.REFERRAL_STATUS,
p_assign_to_person_id => P_SALES_LEAD_Rec.assign_to_person_id,
p_assign_to_salesforce_id=> P_SALES_LEAD_Rec.assign_to_salesforce_id,
p_reject_reason_code => P_SALES_LEAD_Rec.reject_reason_code,
p_assign_sales_group_id => P_SALES_LEAD_Rec.assign_sales_group_id,
p_lead_rank_id => P_SALES_LEAD_Rec.lead_rank_id,
p_qualified_flag => P_SALES_LEAD_Rec.qualified_flag,
p_category => g_log_lead_referral_category
);
l_msg_data:=l_msg_data || '** before calling Update sales lead';
as_sales_leads_pub.update_sales_lead(
P_Api_Version_Number => l_api_version,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
P_Validation_Level => P_Validation_Level,
P_Check_Access_Flag => P_Check_Access_Flag,
P_Admin_Flag => P_Admin_Flag,
P_Admin_Group_Id => P_Admin_Group_Id,
P_identity_salesforce_id => P_identity_salesforce_id,
P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl,
P_SALES_LEAD_Rec => P_SALES_LEAD_Rec,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data
);
ROLLBACK TO Update_sales_referral_lead;
ROLLBACK TO Update_sales_referral_lead;
ROLLBACK TO Update_sales_referral_lead;
END Update_sales_referral_lead;
select asl.description, asl.referred_by, asl.referral_type,lkp1.meaning referral_type_meaning,
asl.referral_status,lkp2.meaning referral_status_meaning, asl.ref_order_number, asl.ref_order_amt,
asl.ref_comm_amt, asl.ref_decline_reason,lkp3.meaning decline_reason_meaning,
asl.ref_comm_ltr_status,asl.created_by
from as_sales_leads asl, as_lookups lkp1, as_lookups lkp2, as_lookups lkp3
where asl.sales_lead_id = pc_lead_id
and asl.referral_type = lkp1.lookup_code(+)
and lkp1.lookup_type(+) = 'REFERRAL_TYPE'
and asl.referral_status = lkp2.lookup_code(+)
and lkp2.lookup_type(+) = 'REFERRAL_STATUS'
and asl.ref_decline_reason = lkp3.lookup_code(+)
and lkp3.lookup_type(+) = 'DECLINE_REASON';
SELECT lead.assign_to_person_id, usr.user_name, usr.user_id, res.source_id
FROM as_sales_leads lead, fnd_user usr, jtf_rs_resource_extns res
WHERE lead.sales_lead_id = pc_lead_id
and lead.assign_to_person_id = res.source_id
and res.user_id = usr.user_id;
SELECT acc.salesforce_id, usr.user_name, usr.user_id
FROM as_accesses_all acc, jtf_rs_resource_extns res, fnd_user usr
WHERE acc.sales_lead_id = pc_lead_id
-- AND acc. salesforce_role_code = 'REFERRAL'
AND acc.person_id IS NULL
AND acc.salesforce_id = res.resource_id
AND res.user_id = usr.user_id;
SELECT hp.party_name, hp.city, hp.state, hp.country
FROM as_sales_leads asl, hz_parties hp
WHERE asl.sales_lead_id = pc_lead_id
AND hp.party_id = asl.customer_id;
SELECT ORG.PARTY_ID, partner.party_name
from AS_SALES_LEADS LEAD, HZ_PARTIES PARTNER, HZ_RELATIONSHIPS REL,HZ_ORGANIZATION_PROFILES ORG
where LEAD.REFERRED_BY=REL.PARTY_ID and REL.SUBJECT_ID=PARTNER.PARTY_ID
and REL.OBJECT_ID=ORG.PARTY_ID
and ORG.internal_flag = 'Y'
and ORG.effective_end_date is null
and LEAD.referred_by = pc_party_id;
select meaning
from ar_lookups
where lookup_type = c_lookup_type
and lookup_code = c_lookup_code;
select category
from jtf_rs_resource_extns
where user_id = c_user_id;
select ARLKP.meaning ||' '||puser.person_first_name||' ' || puser.person_last_name partner_contact_name
from jtf_rs_resource_extns JS,
hz_relationships PCONTACT, hz_relationships PORG,
hz_parties PUSER, hz_parties PARTNER, hz_parties VENDOR,
hz_organization_profiles HZOP, pv_partner_profiles PVPP,
hz_org_contacts HZOC, ar_lookups ARLKP
where JS.user_id = c_user_id
AND JS.source_id = pcontact.party_id
AND PCONTACT.subject_table_name = 'HZ_PARTIES'
AND PCONTACT.object_table_name = 'HZ_PARTIES'
AND PCONTACT.RELATIONSHIP_TYPE in ('EMPLOYMENT')
AND PCONTACT.directional_flag = 'F'
AND PCONTACT.STATUS = 'A'
AND PCONTACT.start_date <= SYSDATE
AND nvl(PCONTACT.end_date, SYSDATE) >= SYSDATE
AND PUSER.party_id = PCONTACT.subject_id
AND PUSER.PARTY_TYPE = 'PERSON'
AND PUSER.status = 'A'
AND HZOC.party_relationship_id = PCONTACT.relationship_id
AND PORG.subject_id = PCONTACT.object_id
AND PORG.subject_table_name = 'HZ_PARTIES'
AND PORG.object_table_name = 'HZ_PARTIES'
AND PORG.RELATIONSHIP_TYPE in ('PARTNER', 'VAD')
AND PORG.STATUS = 'A'
AND PORG.start_date <= SYSDATE
AND nvl(PORG.end_date, SYSDATE) >= SYSDATE
AND PARTNER.party_id = PORG.subject_id
AND PARTNER.PARTY_TYPE = 'ORGANIZATION'
AND PARTNER.status = 'A'
AND VENDOR.party_id = PORG.object_id
AND VENDOR.PARTY_TYPE ='ORGANIZATION'
AND VENDOR.status = 'A'
AND HZOP.party_id = VENDOR.party_id
AND HZOP.effective_end_date is null
AND HZOP.internal_flag = 'Y'
AND PVPP.partner_id = PORG.party_id
AND PVPP.SALES_PARTNER_FLAG = 'Y'
AND PUSER.person_title = ARLKP.lookup_code (+)
and ARLKP.lookup_type(+) = 'CONTACT_TITLE';
select user_name,user_id
from jtf_rs_resource_extns
where user_id = c_created_by;
SELECT PV_LEAD_WORKFLOWS_S.nextval
INTO l_itemKey
FROM dual;