[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TO_CHAR(AS_WORKFLOW_KEYS_S.nextval) INTO Item_Key
FROM dual;
SELECT res.resource_id
FROM jtf_rs_resource_extns res
WHERE res.category = 'EMPLOYEE'
AND res.user_id = fnd_global.user_id;
SELECT grp.group_id
FROM JTF_RS_GROUP_MEMBERS mem,
JTF_RS_ROLE_RELATIONS rrel,
JTF_RS_ROLES_B role,
JTF_RS_GROUP_USAGES u,
JTF_RS_GROUPS_B grp
WHERE mem.group_member_id = rrel.role_resource_id
AND rrel.role_resource_type = 'RS_GROUP_MEMBER'
AND rrel.role_id = role.role_id
AND role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM')
AND mem.delete_flag <> 'Y'
AND rrel.delete_flag <> 'Y'
AND SYSDATE BETWEEN rrel.start_date_active AND
NVL(rrel.end_date_active,SYSDATE)
AND mem.resource_id = c_resource_id
AND mem.group_id = u.group_id
AND u.usage = 'SALES'
AND mem.group_id = grp.group_id
AND SYSDATE BETWEEN grp.start_date_active AND
NVL(grp.end_date_active,SYSDATE)
AND ROWNUM < 2;
SELECT res.source_id
FROM jtf_rs_resource_extns res
WHERE res.resource_id = c_resource_id;
SELECT ACC.SALESFORCE_ID, ACC.SALES_GROUP_ID, ACC.PERSON_ID, 'T'
FROM AS_ACCESSES_ALL ACC
WHERE ACC.SALES_LEAD_ID = c_sales_lead_id
AND ACC.CREATED_BY_TAP_FLAG = 'Y'
AND NOT EXISTS (
SELECT 1
FROM AS_SALES_LEADS_LOG LOG
WHERE LOG.SALES_LEAD_ID = c_sales_lead_id
AND LOG.ASSIGN_TO_SALESFORCE_ID = ACC.SALESFORCE_ID
AND (LOG.ASSIGN_SALES_GROUP_ID = ACC.SALES_GROUP_ID
OR LOG.ASSIGN_SALES_GROUP_ID IS NULL AND ACC.SALES_GROUP_ID IS NULL))
ORDER BY ACC.ACCESS_ID;
g_resource_id_tbl.delete;
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, 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, 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, QUALIFIED_FLAG, ORIG_SYSTEM_CODE,
PRM_ASSIGNMENT_TYPE, AUTO_ASSIGNMENT_TYPE, PRIMARY_CONTACT_PARTY_ID,
PRIMARY_CNT_PERSON_PARTY_ID, PRIMARY_CONTACT_PHONE_ID,
REFERRED_BY, REFERRAL_TYPE, REFERRAL_STATUS, REF_DECLINE_REASON,
REF_COMM_LTR_STATUS, REF_ORDER_NUMBER, REF_ORDER_AMT,
REF_COMM_AMT, LEAD_DATE, SOURCE_SYSTEM, COUNTRY,
TOTAL_AMOUNT, EXPIRATION_DATE, LEAD_ENGINE_RUN_DATE, LEAD_RANK_IND,
CURRENT_REROUTES
FROM AS_SALES_LEADS
WHERE SALES_LEAD_ID = c_sales_lead_id;
SELECT 'Y'
FROM AS_ACCESSES_ALL ACC
WHERE ACC.SALES_LEAD_ID = c_sales_lead_id
AND ACC.CREATED_BY_TAP_FLAG = 'Y';
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.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.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,
l_sales_lead_rec.QUALIFIED_FLAG,
l_sales_lead_rec.ORIG_SYSTEM_CODE,
l_sales_lead_rec.PRM_ASSIGNMENT_TYPE,
l_sales_lead_rec.AUTO_ASSIGNMENT_TYPE,
l_sales_lead_rec.PRIMARY_CONTACT_PARTY_ID,
l_sales_lead_rec.PRIMARY_CNT_PERSON_PARTY_ID,
l_sales_lead_rec.PRIMARY_CONTACT_PHONE_ID,
l_sales_lead_rec.REFERRED_BY,
l_sales_lead_rec.REFERRAL_TYPE,
l_sales_lead_rec.REFERRAL_STATUS,
l_sales_lead_rec.REF_DECLINE_REASON,
l_sales_lead_rec.REF_COMM_LTR_STATUS,
l_sales_lead_rec.REF_ORDER_NUMBER,
l_sales_lead_rec.REF_ORDER_AMT,
l_sales_lead_rec.REF_COMM_AMT,
l_sales_lead_rec.LEAD_DATE,
l_sales_lead_rec.SOURCE_SYSTEM,
l_sales_lead_rec.COUNTRY,
l_sales_lead_rec.TOTAL_AMOUNT,
l_sales_lead_rec.EXPIRATION_DATE,
l_sales_lead_rec.LEAD_ENGINE_RUN_DATE,
l_sales_lead_rec.LEAD_RANK_IND,
l_sales_lead_rec.CURRENT_REROUTES;
PROCEDURE UpdateSalesLeads (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2 )
IS
l_customer_id NUMBER;
SELECT freeze_flag
FROM as_accesses_all
WHERE sales_lead_id = c_sales_lead_id
AND salesforce_id = c_resource_id
AND ((sales_group_id = c_group_id) OR
(sales_group_id IS NULL AND c_group_id IS NULL));
SELECT customer_id, address_id, reject_reason_code,
lead_rank_id, qualified_flag, NVL(accept_flag, 'N'), status_code
FROM as_sales_leads
WHERE Sales_lead_id = c_sales_lead_id;
SELECT DECODE(sta.opp_open_status_flag, 'Y', 'Y', 'N', NULL),
rk.min_score, sl.creation_date
FROM as_statuses_b sta, as_sales_leads sl, as_sales_lead_ranks_b rk
WHERE sl.sales_lead_id = c_sales_lead_id
AND sl.status_code = sta.status_code
AND sl.lead_rank_id = rk.rank_id(+);
'UpdateSalesLeads: Start');
AS_SALES_LEADS_LOG_PKG.Insert_Row(
px_log_id => l_sales_lead_log_id ,
p_sales_lead_id => l_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 => 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_status_code => l_status_code,
p_assign_to_person_id => l_person_id,
p_assign_to_salesforce_id => l_resource_id,
p_reject_reason_code => l_reject_reason_code,
p_assign_sales_group_id => l_group_id,
p_lead_rank_id => l_lead_rank_id,
p_qualified_flag => l_qualified_flag,
p_category => NULL);
AS_SALES_LEADS_PKG.Sales_Lead_Update_Row(
p_SALES_LEAD_ID => l_SALES_LEAD_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => FND_API.G_MISS_DATE,
p_CREATED_BY => FND_API.G_MISS_NUM,
p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
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_LEAD_NUMBER => FND_API.G_MISS_CHAR,
p_STATUS_CODE => FND_API.G_MISS_CHAR,
p_CUSTOMER_ID => l_CUSTOMER_ID,
p_ADDRESS_ID => l_ADDRESS_ID,
p_SOURCE_PROMOTION_ID => FND_API.G_MISS_NUM,
p_INITIATING_CONTACT_ID => FND_API.G_MISS_NUM,
p_ORIG_SYSTEM_REFERENCE => FND_API.G_MISS_CHAR,
p_CONTACT_ROLE_CODE => FND_API.G_MISS_CHAR,
p_CHANNEL_CODE => FND_API.G_MISS_CHAR,
p_BUDGET_AMOUNT => FND_API.G_MISS_NUM,
p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
p_DECISION_TIMEFRAME_CODE => FND_API.G_MISS_CHAR,
p_CLOSE_REASON => FND_API.G_MISS_CHAR,
p_LEAD_RANK_ID => FND_API.G_MISS_NUM,
p_LEAD_RANK_CODE => FND_API.G_MISS_CHAR,
p_PARENT_PROJECT => FND_API.G_MISS_CHAR,
p_DESCRIPTION => 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_ASSIGN_TO_PERSON_ID => l_person_id,
p_ASSIGN_TO_SALESFORCE_ID => l_resource_id,
p_ASSIGN_SALES_GROUP_ID => l_group_id,
p_ASSIGN_DATE => SYSDATE,
p_BUDGET_STATUS_CODE => FND_API.G_MISS_CHAR,
p_ACCEPT_FLAG => 'N',
p_VEHICLE_RESPONSE_CODE => FND_API.G_MISS_CHAR,
p_TOTAL_SCORE => FND_API.G_MISS_NUM,
p_SCORECARD_ID => FND_API.G_MISS_NUM,
p_KEEP_FLAG => FND_API.G_MISS_CHAR,
p_URGENT_FLAG => FND_API.G_MISS_CHAR,
p_IMPORT_FLAG => FND_API.G_MISS_CHAR,
p_REJECT_REASON_CODE => NULL,
p_DELETED_FLAG => FND_API.G_MISS_CHAR,
p_OFFER_ID => FND_API.G_MISS_NUM,
p_QUALIFIED_FLAG => l_qualified_flag,
p_ORIG_SYSTEM_CODE => FND_API.G_MISS_CHAR,
p_INC_PARTNER_PARTY_ID => FND_API.G_MISS_NUM,
p_INC_PARTNER_RESOURCE_ID => FND_API.G_MISS_NUM,
p_PRM_EXEC_SPONSOR_FLAG => FND_API.G_MISS_CHAR,
p_PRM_PRJ_LEAD_IN_PLACE_FLAG => FND_API.G_MISS_CHAR,
p_PRM_SALES_LEAD_TYPE => FND_API.G_MISS_CHAR,
p_PRM_IND_CLASSIFICATION_CODE => FND_API.G_MISS_CHAR,
p_PRM_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
p_AUTO_ASSIGNMENT_TYPE => FND_API.G_MISS_CHAR,
p_PRIMARY_CONTACT_PARTY_ID => FND_API.G_MISS_NUM,
p_PRIMARY_CNT_PERSON_PARTY_ID => FND_API.G_MISS_NUM,
p_PRIMARY_CONTACT_PHONE_ID => FND_API.G_MISS_NUM,
p_REFERRED_BY => FND_API.G_MISS_NUM,
p_REFERRAL_TYPE => FND_API.G_MISS_CHAR,
p_REFERRAL_STATUS => FND_API.G_MISS_CHAR,
p_REF_DECLINE_REASON => FND_API.G_MISS_CHAR,
p_REF_COMM_LTR_STATUS => FND_API.G_MISS_CHAR,
p_REF_ORDER_NUMBER => FND_API.G_MISS_NUM,
p_REF_ORDER_AMT => FND_API.G_MISS_NUM,
p_REF_COMM_AMT => FND_API.G_MISS_NUM,
-- bug No.2341515, 2368075
p_LEAD_DATE => FND_API.G_MISS_DATE,
p_SOURCE_SYSTEM => FND_API.G_MISS_CHAR,
p_COUNTRY => FND_API.G_MISS_CHAR,
p_TOTAL_AMOUNT => FND_API.G_MISS_NUM,
p_EXPIRATION_DATE => FND_API.G_MISS_DATE,
p_LEAD_RANK_IND => FND_API.G_MISS_CHAR,
p_LEAD_ENGINE_RUN_DATE => FND_API.G_MISS_DATE,
p_CURRENT_REROUTES => FND_API.G_MISS_NUM,
p_STATUS_OPEN_FLAG => FND_API.G_MISS_CHAR,
p_LEAD_RANK_SCORE => FND_API.G_MISS_NUM,
-- 11.5.10 new columns
p_MARKETING_SCORE => FND_API.G_MISS_NUM,
p_INTERACTION_SCORE => FND_API.G_MISS_NUM,
p_SOURCE_PRIMARY_REFERENCE => FND_API.G_MISS_CHAR,
p_SOURCE_SECONDARY_REFERENCE => FND_API.G_MISS_CHAR,
p_SALES_METHODOLOGY_ID => FND_API.G_MISS_NUM,
p_SALES_STAGE_ID => FND_API.G_MISS_NUM);
UPDATE as_accesses_all
SET owner_flag = 'N'
WHERE sales_lead_id = l_sales_lead_id;
UPDATE as_accesses_all
SET team_leader_flag = 'Y',
owner_flag = 'Y',
freeze_flag = l_freeze_flag,
created_by_tap_flag = 'Y'
WHERE sales_lead_id = l_sales_lead_id
AND salesforce_id = l_resource_id
AND ((sales_group_id = l_group_id) OR
(sales_group_id IS NULL AND l_group_id IS NULL));
INSERT INTO as_accesses_all
(ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
,OWNER_FLAG, CREATED_BY_TAP_FLAG
,CUSTOMER_ID, ADDRESS_ID, SALES_LEAD_ID, SALESFORCE_ID
,PERSON_ID, SALES_GROUP_ID, OPEN_FLAG, LEAD_RANK_SCORE
,OBJECT_CREATION_DATE)
SELECT as_accesses_s.nextval, SYSDATE, FND_GLOBAL.USER_ID,
SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, 'X',
l_freeze_flag ,'N', 'Y', 'Y', 'Y',
l_customer_id, l_address_id, l_sales_lead_id,
l_resource_id, l_person_id, l_group_id, l_open_status_flag,
l_lead_rank_score, l_creation_date
FROM SYS.DUAL;
'UpdateSalesLeads: End');
wf_core.context(itemtype, 'UpdateSalesLeads', itemtype, itemkey,
to_char(actid), funcmode);
END UpdateSalesLeads;
P_auto_select_flag => NULL,
P_effort_duration => 8,
P_effort_uom => 'HR',
P_start_date => sysdate-1,
P_end_date => sysdate+1,
P_territory_flag => 'Y',
P_calendar_flag => 'Y',
P_Sales_Lead_Id => l_sales_lead_id,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data,
X_Assign_Id_Tbl => l_Assign_Id_tbl
);
SELECT count(sales_lead_id)
FROM as_sales_leads
WHERE assign_to_salesforce_id = resource_id_in;
l_selected_id NUMBER;
l_selected_group_id NUMBER;
SELECT count(log_id)
FROM as_sales_leads_log
WHERE assign_to_salesforce_id = resource_id_in
and sales_lead_id = sl_id_in;
l_selected_id := l_leastwork_resource.resource_id;
l_selected_group_id := l_leastwork_resource.group_id;
avalue => l_selected_id);
avalue => l_selected_group_id);
SELECT manager_id
FROM jtf_rs_group_dtls_vl
WHERE group_id = group_id_in;
l_last_update_date DATE := SYSDATE;
SELECT last_update_date,
customer_id,
address_id,
assign_sales_group_id,
sales_lead_id
FROM as_sales_leads
WHERE sales_lead_id = x_sales_lead_id;
FETCH c_sales_lead INTO l_sales_lead_rec.last_update_date,
l_sales_lead_rec.customer_id,
l_sales_lead_rec.address_id,
l_sales_lead_rec.assign_sales_group_id,
l_sales_lead_rec.sales_lead_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 => 0 -- fnd_api.g_valid_level_full
,p_check_access_flag => 'N' -- fnd_api.g_miss_char
,p_admin_flag => fnd_api.g_miss_char
,p_admin_group_id => fnd_api.g_miss_num
,p_identity_salesforce_id => fnd_api.g_miss_num
,p_sales_lead_profile_tbl => l_sales_lead_profile_tbl
,p_sales_lead_rec => l_sales_lead_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);