[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
opp.last_update_date
,opp.last_updated_by
,opp.creation_Date
,opp.created_by
,opp.last_update_login
,opp.request_id
,opp.program_application_id
,opp.program_id
,opp.program_update_date
,opp.lead_id
,opp.lead_number
,opp.orig_system_reference
,opp.lead_source_code
-- ,opp.lead_source
,opp.description
,opp.source_promotion_id
,AMS1.NAME source_promotion_code
,opp.customer_id
,PARTY.PARTY_NAME CUSTOMER_NAME
,NVL(ORGANIZATION_NAME_PHONETIC, decode(PERSON_LAST_NAME_PHONETIC,Null,Null,PERSON_LAST_NAME_PHONETIC||', ')||PERSON_FIRST_NAME_PHONETIC) CUSTOMER_NAME_PHONETIC
,opp.address_id
-- ,opp.address1 address
-- ,opp.address2
-- ,opp.address3
-- ,opp.address4
-- ,opp.city
-- ,opp.state
-- ,opp.country
-- ,opp.province
,opp.sales_stage_id
,STGTL.NAME SALES_STAGE
,opp.win_probability
,opp.status STATUS_CODE
,ASSTATUSES.MEANING status
,opp.total_amount
-- ,opp.converted_total_amount
,opp.channel_code
,ASOCHANNELS.MEANING CHANNEL
,opp.decision_date
,opp.currency_code
-- ,opp.to_currency_code
,opp.close_reason CLOSE_REASON_CODE
-- ,opp.close_reason
-- ,opp.close_competitor_code
,opp.close_competitor_id
,opp.close_competitor
,opp.close_comment
,opp.end_user_customer_id
,opp.end_user_customer_name
,opp.end_user_address_id
,opp.parent_project
-- ,opp.parent_project_code
-- ,opp.updateable_flag
,opp.price_list_id
-- ,opp.initiating_contact_id
-- ,opp.rank
-- ,opp.member_access
-- ,opp.member_role
-- ,opp.deleted_flag
-- ,opp.auto_assignment_type
-- ,opp.prm_assignment_type
-- ,opp.customer_budget
,opp.methodology_code
-- ,opp.original_lead_id
-- ,opp.decision_timeframe_code
-- ,opp.incumbent_partner_resource_id
-- ,opp.incumbent_partner_party_id
,opp.offer_id
-- ,opp.vehicle_response_code
-- ,opp.budget_status_code
-- ,opp.followup_date
,opp.no_opp_allowed_flag
,opp.delete_allowed_flag
-- ,opp.prm_exec_sponsor_flag
-- ,opp.prm_prj_lead_in_place_flag
-- ,opp.prm_ind_classIFication_code
-- ,opp.prm_lead_type
-- ,opp.org_id
,opp.attribute_category
,opp.attribute1
,opp.attribute2
,opp.attribute3
,opp.attribute4
,opp.attribute5
,opp.attribute6
,opp.attribute7
,opp.attribute8
,opp.attribute9
,opp.attribute10
,opp.attribute11
,opp.attribute12
,opp.attribute13
,opp.attribute14
,opp.attribute15
FROM
AS_LEADS_ALL OPP,
HZ_PARTIES PARTY,
AMS_P_SOURCE_CODES_V AMS1,
OE_LOOKUPS ASOCHANNELS,
AS_SALES_STAGES_ALL_B STGB,
AS_SALES_STAGES_ALL_TL STGTL,
AS_STATUSES_TL ASSTATUSES,
AS_STATUSES_B ASSTB
WHERE
OPP.CUSTOMER_ID = x_Customer_Id AND
OPP.CUSTOMER_ID = PARTY.PARTY_ID AND
OPP.SOURCE_PROMOTION_ID = AMS1.SOURCE_CODE_ID(+) AND
OPP.CHANNEL_CODE = ASOCHANNELS.LOOKUP_CODE(+) AND
ASOCHANNELS.LOOKUP_TYPE(+) = 'SALES_CHANNEL' AND
OPP.SALES_STAGE_ID = STGB.SALES_STAGE_ID(+) AND
STGB.SALES_STAGE_ID = STGTL.SALES_STAGE_ID(+) AND
STGTL.LANGUAGE(+) = USERENV('LANG') AND
OPP.STATUS = ASSTB.STATUS_CODE AND
ASSTB.OPP_OPEN_STATUS_FLAG = 'Y' AND
ASSTB.STATUS_CODE = ASSTATUSES.STATUS_CODE AND
ASSTATUSES.LANGUAGE = USERENV('LANG') ;
SELECT
last_update_date
,last_updated_by
,creation_Date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,lead_id
,lead_line_id
,original_lead_line_id
,interest_type_id
-- ,interest_type
-- ,interest_status_code
,primary_interest_code_id
-- ,primary_interest_code
,secondary_interest_code_id
-- ,secondary_interest_code
,inventory_item_id
-- ,inventory_item_conc_segs
,organization_id
,uom_code
-- ,uom
,quantity
,ship_date
,total_amount
-- ,sales_stage_id
-- ,sales_stage
-- ,win_probability
-- ,status_code
-- ,status
-- ,decision_date
-- ,channel_code
-- ,channel
-- ,unit_price
-- ,price
-- ,price_volume_margin
-- ,quoted_line_flag
-- ,member_access
-- ,member_role
-- ,currency_code
-- ,owner_scredit_percent
-- ,source_promotion_id
-- ,offer_id
-- ,org_id
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,product_category_id
,product_cat_set_id
From AS_LEAD_LINES_ALL
Where lead_id = x_Lead_Id;
l_update_access_flag VARCHAR2(1);
l_header_rec.last_update_date := C_Get_Opp_Rec.last_update_date;
l_header_rec.last_updated_by := C_Get_Opp_Rec.last_updated_by;
l_header_rec.last_update_login := C_Get_Opp_Rec.last_update_login;
l_header_rec.updateable_flag := 'N';
l_line_rec.last_update_date
:= C_Get_Opp_Line_Rec.last_update_date;
l_line_rec.last_updated_by := C_Get_Opp_Line_Rec.last_updated_by;
l_line_rec.last_update_login
:= C_Get_Opp_Line_Rec.last_update_login;
SELECT 'X'
FROM as_sales_lead_opportunity
WHERE sales_lead_id = X_Sales_Lead_Id
AND opportunity_id = X_Opportunity_Id;
SELECT salesforce_id
FROM as_accesses_all
WHERE salesforce_id = X_Identity_Salesforce_Id
AND lead_id = X_Opportunity_Id
AND team_leader_flag = X_Team_Leader_Flag;
SELECT salesforce_id
FROM as_accesses_all
WHERE lead_id = X_Opportunity_Id
AND team_leader_flag = 'Y';
CURSOR C_Get_Last_Update_Date (X_Sales_Lead_Id NUMBER) IS
SELECT last_update_date,channel_code
FROM as_sales_leads
WHERE sales_lead_id = X_Sales_Lead_Id;
SELECT sales_lead_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,lead_number
,status_code
,customer_id
,address_id
,source_promotion_id
,initiating_contact_id
,orig_system_reference
,contact_role_code
,channel_code
,budget_amount
,currency_code
,decision_timeframe_code
,close_reason
,lead_rank_code
,parent_project
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,assign_to_person_id
,assign_to_salesforce_id
,budget_status_code
,assign_date
,accept_flag
,vehicle_response_code
,total_score
,scorecard_id
,keep_flag
,urgent_flag
,import_flag
,reject_reason_code
,lead_rank_id
,deleted_flag
,assign_sales_group_id
,offer_id
-- ,security_group_id
,incumbent_partner_party_id
,incumbent_partner_resource_id
FROM as_sales_leads
WHERE sales_lead_id = X_Sales_Lead_Id;
SELECT last_update_date
,last_updated_by
,creation_Date
,created_by
,last_update_login
,lead_id
,lead_number
,orig_system_reference
,lead_source_code
,description
,source_promotion_id
,customer_id
,address_id
,sales_stage_id
,win_probability
,status status_code
-- ,initiating_contact_id
-- ,rank
,channel_code
,decision_date
,currency_code
,price_list_id
,close_reason close_reason_code
-- ,close_competitor_code
,close_competitor_id
,close_competitor
,close_comment
,end_user_customer_id
,end_user_customer_name
,end_user_address_id
,total_amount
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,parent_project
-- ,FND_API.G_MISS_NUM -- ,security_group_id
From AS_LEADS_ALL
Where lead_id = X_Opportunity_Id;
SELECT sales_lead_line_id
,last_update_date
,last_updated_by
,creation_Date
,created_by
,last_update_login
,sales_lead_id
,interest_type_id
,primary_interest_code_id
,secondary_interest_code_id
,inventory_item_id
,organization_id
,uom_code
,quantity
,budget_amount --total_amount
,source_promotion_id
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,offer_id
-- ,security_group_id
,category_id
,category_set_id
FROM as_sales_lead_lines
WHERE sales_lead_id = X_Sales_Lead_Id;
SELECT contact_id
,contact_party_id
,last_update_date
,last_updated_by
,creation_Date
,created_by
,last_update_login
,enabled_flag
,rank
,customer_id
,address_id
,phone_id
,contact_role_code
,primary_contact_flag
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
-- ,security_group_id
FROM as_sales_lead_contacts
WHERE sales_lead_id = c_sales_lead_id;
SELECT 'X'
FROM AS_LEAD_CONTACTS_ALL
WHERE contact_party_id = x_contact_party_id
and lead_id = p_opportunity_id;
l_last_update_date DATE := FND_API.G_MISS_DATE;
l_update_access_flag VARCHAR2(1);
,l_Sales_Lead_Rec.last_update_date
,l_Sales_Lead_Rec.last_updated_by
,l_Sales_Lead_Rec.creation_date
,l_Sales_Lead_Rec.created_by
,l_Sales_Lead_Rec.last_update_login
,l_Sales_Lead_Rec.request_id
,l_Sales_Lead_Rec.program_application_id
,l_Sales_Lead_Rec.program_id
,l_Sales_Lead_Rec.program_update_date
,l_Sales_Lead_Rec.lead_number
,l_Sales_Lead_Rec.status_code
,l_Sales_Lead_Rec.customer_id
,l_Sales_Lead_Rec.address_id
,l_Sales_Lead_Rec.source_promotion_id
,l_Sales_Lead_Rec.initiating_contact_id
,l_Sales_Lead_Rec.orig_system_reference
,l_Sales_Lead_Rec.contact_role_code
,l_Sales_Lead_Rec.channel_code
,l_Sales_Lead_Rec.budget_amount
,l_Sales_Lead_Rec.currency_code
,l_Sales_Lead_Rec.decision_timeframe_code
,l_Sales_Lead_Rec.close_reason
,l_Sales_Lead_Rec.lead_rank_code
,l_Sales_Lead_Rec.parent_project
,l_Sales_Lead_Rec.description
,l_Sales_Lead_Rec.attribute_category
,l_Sales_Lead_Rec.attribute1
,l_Sales_Lead_Rec.attribute2
,l_Sales_Lead_Rec.attribute3
,l_Sales_Lead_Rec.attribute4
,l_Sales_Lead_Rec.attribute5
,l_Sales_Lead_Rec.attribute6
,l_Sales_Lead_Rec.attribute7
,l_Sales_Lead_Rec.attribute8
,l_Sales_Lead_Rec.attribute9
,l_Sales_Lead_Rec.attribute10
,l_Sales_Lead_Rec.attribute11
,l_Sales_Lead_Rec.attribute12
,l_Sales_Lead_Rec.attribute13
,l_Sales_Lead_Rec.attribute14
,l_Sales_Lead_Rec.attribute15
,l_Sales_Lead_Rec.assign_to_person_id
,l_Sales_Lead_Rec.assign_to_salesforce_id
,l_Sales_Lead_Rec.budget_status_code
,l_Sales_Lead_Rec.assign_date
,l_Sales_Lead_Rec.accept_flag
,l_Sales_Lead_Rec.vehicle_response_code
,l_Sales_Lead_Rec.total_score
,l_Sales_Lead_Rec.scorecard_id
,l_Sales_Lead_Rec.keep_flag
,l_Sales_Lead_Rec.urgent_flag
,l_Sales_Lead_Rec.import_flag
,l_Sales_Lead_Rec.reject_reason_code
,l_Sales_Lead_Rec.lead_rank_id
,l_Sales_Lead_Rec.deleted_flag
,l_Sales_Lead_Rec.assign_sales_group_id
,l_Sales_Lead_Rec.offer_id
-- ,l_Sales_Lead_Rec.security_group_id
,l_Sales_Lead_Rec.incumbent_partner_party_id
,l_Sales_Lead_Rec.incumbent_partner_resource_id;
l_header_rec.last_update_date
,l_header_rec.last_updated_by
,l_header_rec.creation_Date
,l_header_rec.created_by
,l_header_rec.last_update_login
,l_header_rec.lead_id
,l_header_rec.lead_number
,l_header_rec.orig_system_reference
,l_header_rec.lead_source_code
,l_header_rec.description
,l_header_rec.source_promotion_id
,l_header_rec.customer_id
,l_header_rec.address_id
,l_header_rec.sales_stage_id
,l_header_rec.win_probability
,l_header_rec.status_code
-- ,l_header_rec.initiating_contact_id
-- ,l_header_rec.rank
,l_header_rec.channel_code
,l_header_rec.decision_date
,l_header_rec.currency_code
,l_header_rec.price_list_id
,l_header_rec.close_reason_code
-- ,l_header_rec.close_competitor_code
,l_header_rec.close_competitor_id
,l_header_rec.close_competitor
,l_header_rec.close_comment
,l_header_rec.end_user_customer_id
,l_header_rec.end_user_customer_name
,l_header_rec.end_user_address_id
,l_header_rec.total_amount
,l_header_rec.attribute_category
,l_header_rec.attribute1
,l_header_rec.attribute2
,l_header_rec.attribute3
,l_header_rec.attribute4
,l_header_rec.attribute5
,l_header_rec.attribute6
,l_header_rec.attribute7
,l_header_rec.attribute8
,l_header_rec.attribute9
,l_header_rec.attribute10
,l_header_rec.attribute11
,l_header_rec.attribute12
,l_header_rec.attribute13
,l_header_rec.attribute14
,l_header_rec.attribute15
,l_header_rec.parent_project;
FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
l_header_rec.updateable_flag := 'N';
l_line_tbl(l_cnt).last_update_date
:= P_SALES_LEAD_LINE_TBL(i).last_update_date;
l_line_tbl(l_cnt).last_updated_by
:= P_SALES_LEAD_LINE_TBL(i).last_updated_by;
l_line_tbl(l_cnt).last_update_login
:= P_SALES_LEAD_LINE_TBL(i).last_update_login;
AS_SALES_LEAD_OPP_PKG.Lead_Opp_Lines_Insert_Row (
px_LEAD_OPP_LINE_ID => l_Lead_Opp_Line_Id
,p_SALES_LEAD_LINE_ID =>
l_sales_lead_line_tbl(l_index).sales_lead_line_id
,p_OPP_LINE_ID => l_line_out_tbl(l_index).lead_line_id
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => FND_GLOBAL.User_Id
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => FND_GLOBAL.User_Id
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
,p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id
,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
,p_PROGRAM_UPDATE_DATE => SYSDATE
,p_ATTRIBUTE_CATEGORY => l_line_tbl(l_index).attribute_category
,p_ATTRIBUTE1 => l_line_tbl(l_index).attribute1
,p_ATTRIBUTE2 => l_line_tbl(l_index).attribute2
,p_ATTRIBUTE3 => l_line_tbl(l_index).attribute3
,p_ATTRIBUTE4 => l_line_tbl(l_index).attribute4
,p_ATTRIBUTE5 => l_line_tbl(l_index).attribute5
,p_ATTRIBUTE6 => l_line_tbl(l_index).attribute6
,p_ATTRIBUTE7 => l_line_tbl(l_index).attribute7
,p_ATTRIBUTE8 => l_line_tbl(l_index).attribute8
,p_ATTRIBUTE9 => l_line_tbl(l_index).attribute9
,p_ATTRIBUTE10 => l_line_tbl(l_index).attribute10
,p_ATTRIBUTE11 => l_line_tbl(l_index).attribute11
,p_ATTRIBUTE12 => l_line_tbl(l_index).attribute12
,p_ATTRIBUTE13 => l_line_tbl(l_index).attribute13
,p_ATTRIBUTE14 => l_line_tbl(l_index).attribute14
,p_ATTRIBUTE15 => l_line_tbl(l_index).attribute15
-- ,p_SECURITY_GROUP_ID => l_line_security_group_id
);
l_contact_tbl(l_cnt).last_update_date := SYSDATE;
l_contact_tbl(l_cnt).last_updated_by := FND_GLOBAL.USER_ID;
l_contact_tbl(l_cnt).last_update_login :=FND_GLOBAL.CONC_LOGIN_ID;
AS_SALES_LEADS_PUB.Update_sales_lead(
P_Api_Version_Number => l_api_version_number,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
P_Validation_Level => P_Validation_Level,
P_Check_Access_Flag => 'Y', -- 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 => l_Sales_Lead_Rec,
X_Return_Status => X_Return_Status,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data
);
AS_SALES_LEAD_OPP_PKG.Lead_Opportunity_Insert_Row (
px_LEAD_OPPORTUNITY_ID => l_Lead_Opportunity_Id
,p_SALES_LEAD_ID => P_Sales_Lead_Id
,p_OPPORTUNITY_ID => P_Opportunity_Id
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => FND_GLOBAL.User_Id
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => FND_GLOBAL.User_Id
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
,p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id
,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
,p_PROGRAM_UPDATE_DATE => SYSDATE
,p_ATTRIBUTE_CATEGORY => l_header_rec.attribute_category
,p_ATTRIBUTE1 => l_header_rec.attribute1
,p_ATTRIBUTE2 => l_header_rec.attribute2
,p_ATTRIBUTE3 => l_header_rec.attribute3
,p_ATTRIBUTE4 => l_header_rec.attribute4
,p_ATTRIBUTE5 => l_header_rec.attribute5
,p_ATTRIBUTE6 => l_header_rec.attribute6
,p_ATTRIBUTE7 => l_header_rec.attribute7
,p_ATTRIBUTE8 => l_header_rec.attribute8
,p_ATTRIBUTE9 => l_header_rec.attribute9
,p_ATTRIBUTE10 => l_header_rec.attribute10
,p_ATTRIBUTE11 => l_header_rec.attribute11
,p_ATTRIBUTE12 => l_header_rec.attribute12
,p_ATTRIBUTE13 => l_header_rec.attribute13
,p_ATTRIBUTE14 => l_header_rec.attribute14
,p_ATTRIBUTE15 => l_header_rec.attribute15
-- ,p_SECURITY_GROUP_ID => l_opp_security_group_id
);
SELECT 'X'
FROM as_sales_lead_opportunity
WHERE sales_lead_id = X_Sales_Lead_Id
AND opportunity_id = X_Opportunity_Id;
SELECT salesforce_id
FROM as_accesses_all
WHERE salesforce_id = X_Identity_Salesforce_Id
AND lead_id = X_Opportunity_Id
AND team_leader_flag = X_Team_Leader_Flag;
SELECT salesforce_id
FROM as_accesses_all
WHERE lead_id = X_Opportunity_Id
AND team_leader_flag = 'Y';
SELECT sales_lead_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,lead_number
,status_code
,customer_id
,address_id
,source_promotion_id
,initiating_contact_id
,orig_system_reference
,contact_role_code
,channel_code
,budget_amount
,currency_code
,decision_timeframe_code
,close_reason
,lead_rank_code
,parent_project
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,assign_to_person_id
,assign_to_salesforce_id
,budget_status_code
,assign_date
,accept_flag
,vehicle_response_code
,total_score
,scorecard_id
,keep_flag
,urgent_flag
,import_flag
,reject_reason_code
,lead_rank_id
,deleted_flag
,assign_sales_group_id
,offer_id
-- ,security_group_id
,incumbent_partner_party_id
,incumbent_partner_resource_id
FROM as_sales_leads
WHERE sales_lead_id = X_Sales_Lead_Id;
SELECT last_update_date
,last_updated_by
,creation_Date
,created_by
,last_update_login
,lead_id
,lead_number
,orig_system_reference
,lead_source_code
,description
,source_promotion_id
,customer_id
,address_id
,sales_stage_id
,win_probability
,status status_code
-- ,initiating_contact_id
-- ,rank
,channel_code
,decision_date
,currency_code
,price_list_id
,close_reason close_reason_code
-- ,close_competitor_code
,close_competitor_id
,close_competitor
,close_comment
,end_user_customer_id
,end_user_customer_name
,end_user_address_id
,total_amount
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,parent_project
-- ,FND_API.G_MISS_NUM -- ,security_group_id
From AS_LEADS_ALL
Where lead_id = X_Opportunity_Id;
l_last_update_date DATE := FND_API.G_MISS_DATE;
l_update_access_flag VARCHAR2(1);
,l_Sales_Lead_Rec.last_update_date
,l_Sales_Lead_Rec.last_updated_by
,l_Sales_Lead_Rec.creation_date
,l_Sales_Lead_Rec.created_by
,l_Sales_Lead_Rec.last_update_login
,l_Sales_Lead_Rec.request_id
,l_Sales_Lead_Rec.program_application_id
,l_Sales_Lead_Rec.program_id
,l_Sales_Lead_Rec.program_update_date
,l_Sales_Lead_Rec.lead_number
,l_Sales_Lead_Rec.status_code
,l_Sales_Lead_Rec.customer_id
,l_Sales_Lead_Rec.address_id
,l_Sales_Lead_Rec.source_promotion_id
,l_Sales_Lead_Rec.initiating_contact_id
,l_Sales_Lead_Rec.orig_system_reference
,l_Sales_Lead_Rec.contact_role_code
,l_Sales_Lead_Rec.channel_code
,l_Sales_Lead_Rec.budget_amount
,l_Sales_Lead_Rec.currency_code
,l_Sales_Lead_Rec.decision_timeframe_code
,l_Sales_Lead_Rec.close_reason
,l_Sales_Lead_Rec.lead_rank_code
,l_Sales_Lead_Rec.parent_project
,l_Sales_Lead_Rec.description
,l_Sales_Lead_Rec.attribute_category
,l_Sales_Lead_Rec.attribute1
,l_Sales_Lead_Rec.attribute2
,l_Sales_Lead_Rec.attribute3
,l_Sales_Lead_Rec.attribute4
,l_Sales_Lead_Rec.attribute5
,l_Sales_Lead_Rec.attribute6
,l_Sales_Lead_Rec.attribute7
,l_Sales_Lead_Rec.attribute8
,l_Sales_Lead_Rec.attribute9
,l_Sales_Lead_Rec.attribute10
,l_Sales_Lead_Rec.attribute11
,l_Sales_Lead_Rec.attribute12
,l_Sales_Lead_Rec.attribute13
,l_Sales_Lead_Rec.attribute14
,l_Sales_Lead_Rec.attribute15
,l_Sales_Lead_Rec.assign_to_person_id
,l_Sales_Lead_Rec.assign_to_salesforce_id
,l_Sales_Lead_Rec.budget_status_code
,l_Sales_Lead_Rec.assign_date
,l_Sales_Lead_Rec.accept_flag
,l_Sales_Lead_Rec.vehicle_response_code
,l_Sales_Lead_Rec.total_score
,l_Sales_Lead_Rec.scorecard_id
,l_Sales_Lead_Rec.keep_flag
,l_Sales_Lead_Rec.urgent_flag
,l_Sales_Lead_Rec.import_flag
,l_Sales_Lead_Rec.reject_reason_code
,l_Sales_Lead_Rec.lead_rank_id
,l_Sales_Lead_Rec.deleted_flag
,l_Sales_Lead_Rec.assign_sales_group_id
,l_Sales_Lead_Rec.offer_id
-- ,l_Sales_Lead_Rec.security_group_id
,l_Sales_Lead_Rec.incumbent_partner_party_id
,l_Sales_Lead_Rec.incumbent_partner_resource_id;
l_header_rec.last_update_date
,l_header_rec.last_updated_by
,l_header_rec.creation_Date
,l_header_rec.created_by
,l_header_rec.last_update_login
,l_header_rec.lead_id
,l_header_rec.lead_number
,l_header_rec.orig_system_reference
,l_header_rec.lead_source_code
,l_header_rec.description
,l_header_rec.source_promotion_id
,l_header_rec.customer_id
,l_header_rec.address_id
,l_header_rec.sales_stage_id
,l_header_rec.win_probability
,l_header_rec.status_code
-- ,l_header_rec.initiating_contact_id
-- ,l_header_rec.rank
,l_header_rec.channel_code
,l_header_rec.decision_date
,l_header_rec.currency_code
,l_header_rec.price_list_id
,l_header_rec.close_reason_code
-- ,l_header_rec.close_competitor_code
,l_header_rec.close_competitor_id
,l_header_rec.close_competitor
,l_header_rec.close_comment
,l_header_rec.end_user_customer_id
,l_header_rec.end_user_customer_name
,l_header_rec.end_user_address_id
,l_header_rec.total_amount
,l_header_rec.attribute_category
,l_header_rec.attribute1
,l_header_rec.attribute2
,l_header_rec.attribute3
,l_header_rec.attribute4
,l_header_rec.attribute5
,l_header_rec.attribute6
,l_header_rec.attribute7
,l_header_rec.attribute8
,l_header_rec.attribute9
,l_header_rec.attribute10
,l_header_rec.attribute11
,l_header_rec.attribute12
,l_header_rec.attribute13
,l_header_rec.attribute14
,l_header_rec.attribute15
,l_header_rec.parent_project;
FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
l_header_rec.updateable_flag := 'N';
AS_SALES_LEADS_PUB.Update_sales_lead(
P_Api_Version_Number => l_api_version_number,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
P_Validation_Level => P_Validation_Level,
-- P_Check_Access_Flag => 'Y', -- P_Check_Access_Flag, commented by axavier for oppty
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 => l_Sales_Lead_Rec,
X_Return_Status => X_Return_Status,
X_Msg_Count => X_Msg_Count,
X_Msg_Data => X_Msg_Data
);
AS_SALES_LEAD_OPP_PKG.Lead_Opportunity_Insert_Row (
px_LEAD_OPPORTUNITY_ID => l_Lead_Opportunity_Id
,p_SALES_LEAD_ID => P_Sales_Lead_Id
,p_OPPORTUNITY_ID => P_Opportunity_Id
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => FND_GLOBAL.User_Id
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => FND_GLOBAL.User_Id
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
,p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id
,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
,p_PROGRAM_UPDATE_DATE => SYSDATE
,p_ATTRIBUTE_CATEGORY => l_header_rec.attribute_category
,p_ATTRIBUTE1 => l_header_rec.attribute1
,p_ATTRIBUTE2 => l_header_rec.attribute2
,p_ATTRIBUTE3 => l_header_rec.attribute3
,p_ATTRIBUTE4 => l_header_rec.attribute4
,p_ATTRIBUTE5 => l_header_rec.attribute5
,p_ATTRIBUTE6 => l_header_rec.attribute6
,p_ATTRIBUTE7 => l_header_rec.attribute7
,p_ATTRIBUTE8 => l_header_rec.attribute8
,p_ATTRIBUTE9 => l_header_rec.attribute9
,p_ATTRIBUTE10 => l_header_rec.attribute10
,p_ATTRIBUTE11 => l_header_rec.attribute11
,p_ATTRIBUTE12 => l_header_rec.attribute12
,p_ATTRIBUTE13 => l_header_rec.attribute13
,p_ATTRIBUTE14 => l_header_rec.attribute14
,p_ATTRIBUTE15 => l_header_rec.attribute15
-- ,p_SECURITY_GROUP_ID => l_opp_security_group_id
);
SELECT sales_lead_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,lead_number
,status_code
,customer_id
,address_id
,source_promotion_id
,initiating_contact_id
,orig_system_reference
,contact_role_code
,channel_code
,budget_amount
,currency_code
,decision_timeframe_code
,close_reason
,lead_rank_id
,lead_rank_code
,parent_project
,description
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,assign_to_person_id
,assign_to_salesforce_id
,assign_sales_group_id
,assign_date
,budget_status_code
,accept_flag
,vehicle_response_code
,total_score
,scorecard_id
,keep_flag
,urgent_flag
,import_flag
,reject_reason_code
,deleted_flag
,offer_id
-- ,security_group_id
,incumbent_partner_party_id
,incumbent_partner_resource_id
,prm_exec_sponsor_flag
,prm_prj_lead_in_place_flag
,prm_sales_lead_type
,prm_ind_classification_code
-- the following 2 fields added for bug#3613374
,sales_methodology_id
,sales_stage_id
FROM as_sales_leads
WHERE sales_lead_id = X_Sales_Lead_Id;
SELECT sales_lead_line_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,status_code
,interest_type_id
,primary_interest_code_id
,secondary_interest_code_id
,inventory_item_id
,organization_id
,uom_code
,quantity
,budget_amount
,source_promotion_id
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,offer_id
,security_group_id
,category_id
,category_set_id
FROM as_sales_lead_lines
WHERE sales_lead_id = X_Sales_Lead_Id;
SELECT contact_id
,contact_party_id
,last_update_date
,last_updated_by
,creation_Date
,created_by
,last_update_login
,enabled_flag
,rank
,customer_id
,address_id
,phone_id
,contact_role_code
,primary_contact_flag
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
-- ,security_group_id
FROM as_sales_lead_contacts
WHERE sales_lead_id = c_sales_lead_id;
SELECT last_update_date
FROM as_leads_all
WHERE lead_id = X_Lead_Id;
SELECT 'X'
FROM as_sales_lead_opportunity
WHERE sales_lead_id = X_Sales_Lead_Id ;
SELECT freeze_flag, reassign_flag, team_leader_flag,
customer_id, address_id, salesforce_id, person_id,
partner_customer_id, sales_group_id, partner_address_id,
created_person_id, freeze_date, reassign_reason,
salesforce_role_code, salesforce_relationship_code,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6, attribute7, attribute8,
attribute9, attribute10, attribute11, attribute12, attribute13,
attribute14, attribute15, reassign_request_date,
reassign_requested_person_id, security_group_id,
partner_cont_party_id, created_by_tap_flag, prm_keep_flag
-- ffang 062502, bug 2432561
, owner_flag
-- end ffang 062502
FROM as_accesses_all
WHERE sales_lead_id = c_sl_id;
SELECT 'X'
FROM fnd_attached_documents
WHERE pk1_value=X_Sales_Lead_Id;
SELECT hz.party_site_id
FROM PV_PARTNERS_V pv,
hz_party_sites hz
WHERE pv.partner_id = x_partner_party_id
AND hz.party_id =pv.partner_party_id
AND pv.PARTNER_PARTY_NAME IS NOT NULL
AND pv.INTERNAL_FLAG||'' = 'Y'
AND pv.PARTNER_RESOURCE_ID IS NOT NULL
AND pv.INTERNAL_STATUS = 'A'
AND pv.PARTNER_STATUS = 'A'
AND pv.RELATIONSHIP_STATUS = 'A'
AND pv.SALES_PARTNER_FLAG = 'Y'
and hz.identifying_address_flag = 'Y'
and hz.status = 'A';
l_last_update_date DATE;
l_update_access_flag VARCHAR2(1);
AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Calling Has_updateLeadAccess');
AS_ACCESS_PUB.Has_updateLeadAccess(
p_api_version_number => 2.0
,p_init_msg_list => FND_API.G_FALSE
,p_validation_level => p_validation_level
,p_access_profile_rec => l_access_profile_rec
,p_admin_flag => p_admin_flag
,p_admin_group_id => p_admin_group_id
,p_person_id => l_identity_sales_member_rec.employee_person_id
,p_sales_lead_id => p_sales_lead_id
,p_check_access_flag => 'Y'
,p_identity_salesforce_id => p_identity_salesforce_id
,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_update_access_flag => l_update_access_flag);
IF l_update_access_flag <> 'Y' THEN
IF l_debug_err THEN
AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'API_NO_CREATE_PRIVILEGE');
,l_Sales_Lead_Rec.last_update_date
,l_Sales_Lead_Rec.last_updated_by
,l_Sales_Lead_Rec.creation_date
,l_Sales_Lead_Rec.created_by
,l_Sales_Lead_Rec.last_update_login
,l_Sales_Lead_Rec.request_id
,l_Sales_Lead_Rec.program_application_id
,l_Sales_Lead_Rec.program_id
,l_Sales_Lead_Rec.program_update_date
,l_Sales_Lead_Rec.lead_number
,l_Sales_Lead_Rec.status_code
,l_Sales_Lead_Rec.customer_id
,l_Sales_Lead_Rec.address_id
,l_Sales_Lead_Rec.source_promotion_id
,l_Sales_Lead_Rec.initiating_contact_id
,l_Sales_Lead_Rec.orig_system_reference
,l_Sales_Lead_Rec.contact_role_code
,l_Sales_Lead_Rec.channel_code
,l_Sales_Lead_Rec.budget_amount
,l_Sales_Lead_Rec.currency_code
,l_Sales_Lead_Rec.decision_timeframe_code
,l_Sales_Lead_Rec.close_reason
,l_Sales_Lead_Rec.lead_rank_id
,l_Sales_Lead_Rec.lead_rank_code
,l_Sales_Lead_Rec.parent_project
,l_Sales_Lead_Rec.description
,l_Sales_Lead_Rec.attribute_category
,l_Sales_Lead_Rec.attribute1
,l_Sales_Lead_Rec.attribute2
,l_Sales_Lead_Rec.attribute3
,l_Sales_Lead_Rec.attribute4
,l_Sales_Lead_Rec.attribute5
,l_Sales_Lead_Rec.attribute6
,l_Sales_Lead_Rec.attribute7
,l_Sales_Lead_Rec.attribute8
,l_Sales_Lead_Rec.attribute9
,l_Sales_Lead_Rec.attribute10
,l_Sales_Lead_Rec.attribute11
,l_Sales_Lead_Rec.attribute12
,l_Sales_Lead_Rec.attribute13
,l_Sales_Lead_Rec.attribute14
,l_Sales_Lead_Rec.attribute15
,l_Sales_Lead_Rec.assign_to_person_id
,l_Sales_Lead_Rec.assign_to_salesforce_id
,l_Sales_Lead_Rec.assign_sales_group_id
,l_Sales_Lead_Rec.assign_date
,l_Sales_Lead_Rec.budget_status_code
,l_Sales_Lead_Rec.accept_flag
,l_Sales_Lead_Rec.vehicle_response_code
,l_Sales_Lead_Rec.total_score
,l_Sales_Lead_Rec.scorecard_id
,l_Sales_Lead_Rec.keep_flag
,l_Sales_Lead_Rec.urgent_flag
,l_Sales_Lead_Rec.import_flag
,l_Sales_Lead_Rec.reject_reason_code
,l_Sales_Lead_Rec.deleted_flag
,l_Sales_Lead_Rec.offer_id
-- ,l_Sales_Lead_Rec.security_group_id
,l_Sales_Lead_Rec.incumbent_partner_party_id
,l_Sales_Lead_Rec.incumbent_partner_resource_id
,l_Sales_Lead_Rec.prm_exec_sponsor_flag
,l_Sales_Lead_Rec.prm_prj_lead_in_place_flag
,l_Sales_Lead_Rec.prm_sales_lead_type
,l_Sales_Lead_Rec.prm_ind_classification_code
-- 2 fields added for Bug#3613374
,l_sales_lead_methodology_id
,l_sales_lead_stage_id;
l_last_update_date := l_Sales_Lead_Rec.last_update_Date;
FETCH C_Get_Opportunity INTO l_header_rec.last_update_date;
l_line_tbl(l_cnt).last_update_date := SYSDATE;
l_line_tbl(l_cnt).last_updated_by := FND_GLOBAL.USER_ID;
l_line_tbl(l_cnt).last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Calling AS_SALES_LEAD_OPP_PKG.Lead_Opp_Lines_Insert_Row' ||
l_sales_lead_line_tbl(l_index).sales_lead_line_id ||
',' || l_line_out_tbl(l_index).lead_line_id);
AS_SALES_LEAD_OPP_PKG.Lead_Opp_Lines_Insert_Row (
px_LEAD_OPP_LINE_ID => l_Lead_Opp_Line_Id
,p_SALES_LEAD_LINE_ID =>
l_sales_lead_line_tbl(l_index).sales_lead_line_id
,p_OPP_LINE_ID => l_line_out_tbl(l_index).lead_line_id
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => FND_GLOBAL.User_Id
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => FND_GLOBAL.User_Id
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
,p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id
,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
,p_PROGRAM_UPDATE_DATE => SYSDATE
,p_ATTRIBUTE_CATEGORY => l_line_tbl(l_index).attribute_category
,p_ATTRIBUTE1 => l_line_tbl(l_index).attribute1
,p_ATTRIBUTE2 => l_line_tbl(l_index).attribute2
,p_ATTRIBUTE3 => l_line_tbl(l_index).attribute3
,p_ATTRIBUTE4 => l_line_tbl(l_index).attribute4
,p_ATTRIBUTE5 => l_line_tbl(l_index).attribute5
,p_ATTRIBUTE6 => l_line_tbl(l_index).attribute6
,p_ATTRIBUTE7 => l_line_tbl(l_index).attribute7
,p_ATTRIBUTE8 => l_line_tbl(l_index).attribute8
,p_ATTRIBUTE9 => l_line_tbl(l_index).attribute9
,p_ATTRIBUTE10 => l_line_tbl(l_index).attribute10
,p_ATTRIBUTE11 => l_line_tbl(l_index).attribute11
,p_ATTRIBUTE12 => l_line_tbl(l_index).attribute12
,p_ATTRIBUTE13 => l_line_tbl(l_index).attribute13
,p_ATTRIBUTE14 => l_line_tbl(l_index).attribute14
,p_ATTRIBUTE15 => l_line_tbl(l_index).attribute15
-- ,p_security_group_id => l_line_rec.security_group_id
);
l_contact_tbl(l_cnt).last_update_date := SYSDATE;
l_contact_tbl(l_cnt).last_updated_by := FND_GLOBAL.USER_ID;
l_contact_tbl(l_cnt).last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
l_SL_Rec.last_update_date := l_last_update_date;
AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Calling Update_sales_lead');
AS_SALES_LEADS_PUB.Update_sales_lead(
P_Api_Version_Number => l_api_version_number,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
P_Validation_Level => P_Validation_Level,
P_Check_Access_Flag => 'Y', -- 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 => l_SL_Rec, -- ffang 020301
-- 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
);
AS_SALES_LEAD_OPP_PKG.Lead_Opportunity_Insert_Row (
-- ffang 071202, why use l_lead_id as lead_opportunity_id output?
-- px_LEAD_OPPORTUNITY_ID => l_Lead_Id
px_LEAD_OPPORTUNITY_ID => l_Lead_Opportunity_Id
,p_SALES_LEAD_ID => P_Sales_Lead_Id
,p_OPPORTUNITY_ID => l_Lead_Id
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => FND_GLOBAL.User_Id
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => FND_GLOBAL.User_Id
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
,p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id
,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
,p_PROGRAM_UPDATE_DATE => SYSDATE
,p_ATTRIBUTE_CATEGORY => l_header_rec.attribute_category
,p_ATTRIBUTE1 => l_header_rec.attribute1
,p_ATTRIBUTE2 => l_header_rec.attribute2
,p_ATTRIBUTE3 => l_header_rec.attribute3
,p_ATTRIBUTE4 => l_header_rec.attribute4
,p_ATTRIBUTE5 => l_header_rec.attribute5
,p_ATTRIBUTE6 => l_header_rec.attribute6
,p_ATTRIBUTE7 => l_header_rec.attribute7
,p_ATTRIBUTE8 => l_header_rec.attribute8
,p_ATTRIBUTE9 => l_header_rec.attribute9
,p_ATTRIBUTE10 => l_header_rec.attribute10
,p_ATTRIBUTE11 => l_header_rec.attribute11
,p_ATTRIBUTE12 => l_header_rec.attribute12
,p_ATTRIBUTE13 => l_header_rec.attribute13
,p_ATTRIBUTE14 => l_header_rec.attribute14
,p_ATTRIBUTE15 => l_header_rec.attribute15
-- ,p_security_group_id => l_opp_security_group_id
-- l_header_rec.security_group_id
);
SELECT
notes.jtf_note_id
FROM
JTF_NOTES_B notes
WHERE
notes.source_object_id = x_sales_lead_id AND
notes.source_object_code = 'LEAD' AND
NOT EXISTS (
SELECT
context.jtf_note_id
FROM
JTF_NOTE_CONTEXTS context
WHERE
notes.jtf_note_id = context.jtf_note_id AND
context.note_context_type = 'OPPORTUNITY' AND
context.note_context_type_id = p_opportunity_id
);
SELECT JTF_NOTES_S.NEXTVAL INTO l_jtf_notes_context_id FROM DUAL;
-- Inserting into JTF_NOTES_CONTEXTS table
INSERT INTO JTF_NOTE_CONTEXTS (
NOTE_CONTEXT_ID
, JTF_NOTE_ID
, NOTE_CONTEXT_TYPE
, NOTE_CONTEXT_TYPE_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN)
values (
l_jtf_notes_context_id,
notes_rec.jtf_note_id ,
'OPPORTUNITY'
,p_opportunity_id,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID);
select applicability
from as_sales_stages_all_vl
where sales_stage_id = p_sales_stage_id;
SELECT stage.sales_stage_id
FROM as_sales_stages_all_vl stage, as_sales_meth_stage_map map1
WHERE stage.sales_stage_id = map1.sales_stage_id
AND nvl(stage.applicability,'BOTH') in ('OPPORTUNITY', 'BOTH')
AND nvl(stage.ENABLED_FLAG,'Y') = 'Y'
AND trunc(sysdate) between trunc(nvl(START_DATE_ACTIVE,sysdate))
AND trunc(nvl(END_DATE_ACTIVE,sysdate))
AND map1.sales_methodology_id = p_sales_method_id
ORDER BY STAGE_SEQUENCE;
l_last_update_date date;