[Home] [Help]
35: -- is not created by partner.
36: -- 06/02/2002 SOLIN Bug 2395613.
37: -- NULL index table key value.
38: -- 08/13/2002 SOLIN Bug 2503364, 2503366
39: -- Change SQL to update AS_ACCESSES_ALL and
40: -- AS_TERRITORY_ACCESSES
41: -- 10/01/2002 SOLIN Bug 2599946
42: -- Remove as_changed_account_all record in
43: -- Build_Lead_Sales_Team because sales lead record
56: -- Reassignment change for new lead lines.
57: -- 03/14/2003 SOLIN Bug 2852597
58: -- Port 11.5.8 fix to 11.5.9.
59: -- 03/20/2003 SOLIN Bug 2831426
60: -- Add open_flag in as_accesses_all table.
61: -- 03/28/2003 SOLIN Bug 2877597
62: -- Change C_Validate_Salesforce cursor
63: -- 04/17/2003 SOLIN Bug 2899734, 2902742
64: -- Change sql for trigger handlers.
70: -- 04/30/2003 SOLIN Bug 2931721
71: -- Reset g_resource_id_tbl in Get_Available_Resource.
72: -- 05/01/2003 SOLIN Bug 2928041
73: -- Add open_flag, object_creation_date, and
74: -- lead_rank_score in as_accesses_all table
75: -- 07/07/2003 SOLIN Bug 3035251
76: -- County qualifier doesn't work.
77: -- Set UPPER for all VARCHAR2 qualifier.
78: -- 07/10/2003 SOLIN Bug 3046959(Bug 2983881 for 11.5.8)
114: |
115: | PRIVATE VARIABLES
116: |
117: *-------------------------------------------------------------------------*/
118: -- varray for insert (AS_ACCESSES_ALL)
119: g_i_access_id NUMBER_TABLE;
120: g_i_resource_id NUMBER_TABLE; -- salesforce_id
121: g_i_group_id NUMBER_TABLE; -- sales_group_id
122: g_i_person_id NUMBER_TABLE;
130: g_i_source VARCHAR2_30_TABLE;
131: g_i_partner_customer_id NUMBER_TABLE;
132: g_i_partner_cont_party_id NUMBER_TABLE;
133:
134: -- varray for update (AS_ACCESSES_ALL)
135: g_u_access_id NUMBER_TABLE;
136: g_u_full_access_flag FLAG_TABLE;
137:
138: -- varray for insert (AS_TERRITORY_ACCESSES)
143: g_tu_access_id NUMBER_TABLE;
144: g_tu_territory_id NUMBER_TABLE;
145:
146:
147: -- length of Insert array for AS_ACCESSES_ALL
148: g_i_count NUMBER := 0;
149: -- length of Update array for AS_ACCESSES_ALL
150: g_u_count NUMBER := 0;
151: -- length of Insert array for AS_TERRITORY_ACCESSES
145:
146:
147: -- length of Insert array for AS_ACCESSES_ALL
148: g_i_count NUMBER := 0;
149: -- length of Update array for AS_ACCESSES_ALL
150: g_u_count NUMBER := 0;
151: -- length of Insert array for AS_TERRITORY_ACCESSES
152: g_ti_count NUMBER := 0;
153: -- length of Update array for AS_TERRITORY_ACCESSES
1230: l_data VARCHAR2(70);
1231: l_index_out NUMBER;
1232:
1233:
1234: -- The following variables are for as_accesses_all
1235: l_access_id_tbl NUMBER_TABLE;
1236: l_owner_flag_tbl FLAG_TABLE;
1237:
1238: -- The following variables are for as_territory_accesses
2289: AND res.category = c_employee; --'EMPLOYEE';
2290:
2291: -- Get access_id, terr_id for the records that come from LEAD territory.
2292: -- Delete these records before new resource records are created in
2293: -- AS_ACCESSES_ALL table.
2294: CURSOR C_Get_Acc_Terr(c_sales_lead_id NUMBER) IS
2295: SELECT ACC.ACCESS_ID, TERRACC.TERRITORY_ID
2296: FROM AS_ACCESSES_ALL ACC, AS_TERRITORY_ACCESSES TERRACC
2297: WHERE ACC.FREEZE_FLAG = 'N'
2292: -- Delete these records before new resource records are created in
2293: -- AS_ACCESSES_ALL table.
2294: CURSOR C_Get_Acc_Terr(c_sales_lead_id NUMBER) IS
2295: SELECT ACC.ACCESS_ID, TERRACC.TERRITORY_ID
2296: FROM AS_ACCESSES_ALL ACC, AS_TERRITORY_ACCESSES TERRACC
2297: WHERE ACC.FREEZE_FLAG = 'N'
2298: AND ACC.SALES_LEAD_ID = c_sales_lead_id
2299: AND ACC.OWNER_FLAG = 'N'
2300: AND ACC.ACCESS_ID = TERRACC.ACCESS_ID;
2306:
2307: -- Check whether owner exists or not
2308: CURSOR c_check_owner_exists(c_sales_lead_id NUMBER) IS
2309: SELECT 'Y'
2310: FROM as_accesses_all acc
2311: WHERE acc.sales_lead_id = c_sales_lead_id
2312: AND acc.owner_flag = 'Y';
2313:
2314: l_api_name CONSTANT VARCHAR2(30)
2333:
2334: l_data VARCHAR2(70);
2335: l_index_out NUMBER;
2336:
2337: -- The following variables are for as_accesses_all
2338: l_access_id_tbl NUMBER_TABLE;
2339: l_resource_id NUMBER;
2340: l_group_id NUMBER;
2341: l_person_id NUMBER;
2499: END IF;
2500:
2501: -- Get access_id, terr_id for the records that come from LEAD territory.
2502: -- Delete these records before new resource records are created in
2503: -- AS_ACCESSES_ALL table.
2504: IF (AS_DEBUG_LOW_ON) THEN
2505: AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2506: 'customer_id=' || l_customer_id);
2507: AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2530: END LOOP;
2531: END IF;
2532:
2533: FORALL l_i IN 1..l_ta_access_id_tbl.count
2534: DELETE FROM AS_ACCESSES_ALL
2535: WHERE ACCESS_ID = l_ta_access_id_tbl(l_i);
2536:
2537: FORALL l_i IN 1..l_ta_terr_id_tbl.count
2538: DELETE FROM AS_TERRITORY_ACCESSES
2540: AND TERRITORY_ID = l_ta_terr_id_tbl(l_i);
2541: END IF;
2542:
2543: -- Delete non-frozen resources who are not from territory.
2544: DELETE FROM as_accesses_all acc
2545: WHERE acc.sales_lead_id = p_sales_lead_id
2546: AND acc.freeze_flag = 'N'
2547: --AND acc.salesforce_id <> p_identity_salesforce_id
2548: AND NOT EXISTS (
2907: END LOOP; -- l_AssignResources_tbl.first..l_AssignResources_tbl.last
2908: END IF; -- l_AssignResources_tbl.COUNT > 0
2909: Flush_Access_Records(l_request_id);
2910:
2911: -- If user change address_id, AS_ACCESSES_ALL table should be updated.
2912: -- There may be some records untouched, so update here, instead of
2913: -- in Flush_Access_Records.
2914: IF l_old_address_id <> l_new_address_id
2915: THEN
2912: -- There may be some records untouched, so update here, instead of
2913: -- in Flush_Access_Records.
2914: IF l_old_address_id <> l_new_address_id
2915: THEN
2916: UPDATE as_accesses_all
2917: SET address_id = l_new_address_id
2918: WHERE sales_lead_id = p_sales_lead_id
2919: AND address_id = l_old_address_id;
2920: ELSIF l_old_address_id IS NULL AND l_new_address_id IS NOT NULL
2918: WHERE sales_lead_id = p_sales_lead_id
2919: AND address_id = l_old_address_id;
2920: ELSIF l_old_address_id IS NULL AND l_new_address_id IS NOT NULL
2921: THEN
2922: UPDATE as_accesses_all
2923: SET address_id = l_new_address_id
2924: WHERE sales_lead_id = p_sales_lead_id
2925: AND address_id IS NULL;
2926: ELSIF l_old_address_id IS NOT NULL AND l_new_address_id IS NULL
2924: WHERE sales_lead_id = p_sales_lead_id
2925: AND address_id IS NULL;
2926: ELSIF l_old_address_id IS NOT NULL AND l_new_address_id IS NULL
2927: THEN
2928: UPDATE as_accesses_all
2929: SET address_id = NULL
2930: WHERE sales_lead_id = p_sales_lead_id
2931: AND address_id = l_old_address_id;
2932: END IF;
2947:
2948: DELETE FROM AS_TERRITORY_ACCESSES
2949: WHERE access_id IN
2950: (SELECT a.access_id
2951: FROM as_accesses_all a
2952: WHERE a.sales_lead_id = p_sales_lead_id
2953: AND a.freeze_flag = 'N'
2954: AND a.request_id = l_request_id)
2955: AND REQUEST_ID IS NULL;
2953: AND a.freeze_flag = 'N'
2954: AND a.request_id = l_request_id)
2955: AND REQUEST_ID IS NULL;
2956:
2957: DELETE FROM AS_ACCESSES_ALL
2958: WHERE SALES_LEAD_ID = p_sales_lead_id
2959: AND FREEZE_FLAG = 'N'
2960: AND REQUEST_ID IS NULL;
2961:
3485: )
3486: IS
3487: l_found VARCHAR2(1) := 'N';
3488: l_access_id NUMBER;
3489: l_full_access_flag VARCHAR2(1); -- from AS_ACCESSES_ALL table
3490: --l_full_access_flag VARCHAR2(1); -- from JTF_TERR_RSC table
3491: l_employee_person_id NUMBER;
3492: l_access_id_new NUMBER;
3493:
3497:
3498: CURSOR c_get_access_id(c_resource_id NUMBER, c_group_id NUMBER,
3499: c_sales_lead_id NUMBER) IS
3500: SELECT ACCESS_ID, TEAM_LEADER_FLAG
3501: FROM AS_ACCESSES_ALL
3502: WHERE SALESFORCE_ID = c_resource_id
3503: AND ((SALES_GROUP_ID IS NULL AND c_group_id IS NULL)
3504: OR SALES_GROUP_ID = c_group_id)
3505: AND SALES_LEAD_ID = c_sales_lead_id
3888: END LOOP;
3889: END IF;
3890:
3891: FORALL l_i IN 1..l_u_num_rows
3892: UPDATE AS_ACCESSES_ALL
3893: SET LAST_UPDATE_DATE = SYSDATE,
3894: LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3895: LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
3896: PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID,
3949: l_lead_rank_score, l_creation_date;
3950: CLOSE c_get_open_status_flag;
3951:
3952: FORALL l_i IN 1..l_i_num_rows
3953: INSERT INTO AS_ACCESSES_ALL
3954: (ACCESS_ID,
3955: ACCESS_TYPE,
3956: SALESFORCE_ID,
3957: SALES_GROUP_ID,
4070: BEGIN
4071: DELETE FROM as_territory_accesses
4072: WHERE access_id IN (
4073: SELECT acc.access_id
4074: FROM as_accesses_all acc
4075: WHERE acc.sales_lead_id = p_sales_lead_id
4076: AND acc.request_id = p_request_id
4077: AND acc.owner_flag = 'N'
4078: AND acc.created_by_tap_flag = 'Y');
4076: AND acc.request_id = p_request_id
4077: AND acc.owner_flag = 'N'
4078: AND acc.created_by_tap_flag = 'Y');
4079:
4080: DELETE FROM as_accesses_all
4081: WHERE sales_lead_id = p_sales_lead_id
4082: AND request_id = p_request_id
4083: AND owner_flag = 'N'
4084: AND created_by_tap_flag = 'Y';
4124: -- care which group_id is in the sales team as long as this resource is
4125: -- in the sales team.
4126: CURSOR c_check_sales_team(c_resource_id NUMBER, c_sales_lead_id NUMBER) IS
4127: SELECT acc.access_id, team_leader_flag
4128: FROM as_accesses_all acc
4129: WHERE acc.salesforce_id = c_resource_id
4130: AND acc.sales_lead_id = c_sales_lead_id;
4131:
4132: CURSOR c_get_group_id (c_resource_id NUMBER, c_rs_group_member VARCHAR2,
4244: -- Current user is not in sales team, insert this
4245: -- resource as sales team member. Since this resource doesn't
4246: -- come from territory, don't insert into
4247: -- as_territory_accesses
4248: INSERT INTO as_accesses_all
4249: (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
4250: ,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
4251: ,PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
4252: ,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG
4265: FROM sys.dual;
4266: ELSIF l_team_leader_flag = 'N'
4267: THEN
4268: -- lead creator is in sales team, but no full access
4269: UPDATE as_accesses_all
4270: SET team_leader_flag = 'Y'
4271: WHERE access_id = l_access_id;
4272: END IF; -- l_access_id IS NULL
4273:
4285: | NOTES
4286: | This procedure will get resource_id from the profile
4287: | AS_DEFAULT_RESOURCE_ID. If this profile is not set, it will get
4288: | current user's resource_id. Once resource_id is gotten
4289: | AS_SALES_LEADS_LOG, AS_SALES_LEADS, AS_ACCESSES_ALL will be updated.
4290: |
4291: | HISTORY
4292: | 12/05/01 SOLIN Created
4293: *-------------------------------------------------------------------------*/
4355:
4356: CURSOR c_access_exist(c_sales_lead_id NUMBER, c_resource_id NUMBER,
4357: c_group_id NUMBER) IS
4358: SELECT 'Y'
4359: FROM as_accesses_all
4360: WHERE sales_lead_id = c_sales_lead_id
4361: AND salesforce_id = c_resource_id
4362: AND ((sales_group_id = c_group_id) OR
4363: (sales_group_id IS NULL AND c_group_id IS NULL));
4593: 'access exist?'|| l_access_exist_flag);
4594: END IF;
4595: IF l_reject_reason_code IS NOT NULL
4596: THEN
4597: -- Clear any owner for as_accesses_all
4598: -- If reject reason_code IS NULL, this workflow process must come
4599: -- from lead owner is null, owner in as_accesses_all is cleared
4600: -- already.
4601: UPDATE as_accesses_all
4595: IF l_reject_reason_code IS NOT NULL
4596: THEN
4597: -- Clear any owner for as_accesses_all
4598: -- If reject reason_code IS NULL, this workflow process must come
4599: -- from lead owner is null, owner in as_accesses_all is cleared
4600: -- already.
4601: UPDATE as_accesses_all
4602: SET owner_flag = 'N'
4603: WHERE sales_lead_id = p_sales_lead_id;
4597: -- Clear any owner for as_accesses_all
4598: -- If reject reason_code IS NULL, this workflow process must come
4599: -- from lead owner is null, owner in as_accesses_all is cleared
4600: -- already.
4601: UPDATE as_accesses_all
4602: SET owner_flag = 'N'
4603: WHERE sales_lead_id = p_sales_lead_id;
4604: END IF;
4605:
4613: -- Default resource is not in sales team, insert this
4614: -- resource as sales team member. Since this resource doesn't
4615: -- come from territory, don't insert into
4616: -- as_territory_accesses
4617: INSERT INTO as_accesses_all
4618: (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
4619: ,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
4620: ,PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
4621: ,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
4633: FROM sys.dual;
4634: ELSE
4635: -- Default resource is in sales team, update this resource
4636: -- as owner.
4637: UPDATE as_accesses_all
4638: SET team_leader_flag = 'Y',
4639: owner_flag = 'Y',
4640: freeze_flag = l_freeze_flag,
4641: request_id = p_request_id
4728:
4729: CURSOR c_access_exist(c_sales_lead_id NUMBER, c_resource_id NUMBER,
4730: c_group_id NUMBER) IS
4731: SELECT 'Y'
4732: FROM as_accesses_all
4733: WHERE sales_lead_id = c_sales_lead_id
4734: AND salesforce_id = c_resource_id
4735: AND ((sales_group_id = c_group_id) OR
4736: (sales_group_id IS NULL AND c_group_id IS NULL));
4782: Set_Default_Lead_Owner(p_sales_lead_id, p_salesgroup_id,
4783: p_request_id, x_return_status, x_msg_count, x_msg_data);
4784: ELSE
4785: -- Customer return owner, update AS_SALES_LEADS_LOG and
4786: -- AS_ACCESSES_ALL, AS_SALES_LEADS
4787: IF (AS_DEBUG_LOW_ON) THEN
4788: AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4789: 'res id in upd=' || l_Resource_Id);
4790: AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4930: CLOSE c_access_exist;
4931:
4932: IF l_reject_reason_code IS NOT NULL
4933: THEN
4934: -- Clear any owner for as_accesses_all
4935: -- If reject reason_code IS NULL, this workflow process must come
4936: -- from lead owner is null, owner in as_accesses_all is cleared
4937: -- already.
4938: UPDATE as_accesses_all
4932: IF l_reject_reason_code IS NOT NULL
4933: THEN
4934: -- Clear any owner for as_accesses_all
4935: -- If reject reason_code IS NULL, this workflow process must come
4936: -- from lead owner is null, owner in as_accesses_all is cleared
4937: -- already.
4938: UPDATE as_accesses_all
4939: SET owner_flag = 'N'
4940: WHERE sales_lead_id = p_sales_lead_id;
4934: -- Clear any owner for as_accesses_all
4935: -- If reject reason_code IS NULL, this workflow process must come
4936: -- from lead owner is null, owner in as_accesses_all is cleared
4937: -- already.
4938: UPDATE as_accesses_all
4939: SET owner_flag = 'N'
4940: WHERE sales_lead_id = p_sales_lead_id;
4941: END IF;
4942:
4941: END IF;
4942:
4943: IF l_access_exist_flag = 'Y'
4944: THEN
4945: UPDATE as_accesses_all
4946: SET team_leader_flag = 'Y',
4947: owner_flag = 'Y',
4948: freeze_flag = l_freeze_flag,
4949: created_by_tap_flag = 'Y'
4956: FETCH c_get_open_status_flag INTO l_open_status_flag,
4957: l_lead_rank_score, l_creation_date;
4958: CLOSE c_get_open_status_flag;
4959:
4960: INSERT INTO as_accesses_all
4961: (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
4962: ,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
4963: ,PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
4964: ,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
4997: IS
4998: CURSOR c_access_exist(c_sales_lead_id NUMBER, c_resource_id NUMBER,
4999: c_group_id NUMBER) IS
5000: SELECT 'Y'
5001: FROM as_accesses_all
5002: WHERE sales_lead_id = c_sales_lead_id
5003: AND salesforce_id = c_resource_id
5004: AND ((sales_group_id = c_group_id) OR
5005: (sales_group_id IS NULL AND c_group_id IS NULL));
5266: CLOSE c_access_exist;
5267:
5268: IF l_reject_reason_code IS NOT NULL
5269: THEN
5270: -- Clear any owner for as_accesses_all
5271: -- If reject reason_code IS NULL, this workflow process must come
5272: -- from lead owner is null, owner in as_accesses_all is cleared
5273: -- already.
5274: UPDATE as_accesses_all
5268: IF l_reject_reason_code IS NOT NULL
5269: THEN
5270: -- Clear any owner for as_accesses_all
5271: -- If reject reason_code IS NULL, this workflow process must come
5272: -- from lead owner is null, owner in as_accesses_all is cleared
5273: -- already.
5274: UPDATE as_accesses_all
5275: SET owner_flag = 'N'
5276: WHERE sales_lead_id = p_sales_lead_id;
5270: -- Clear any owner for as_accesses_all
5271: -- If reject reason_code IS NULL, this workflow process must come
5272: -- from lead owner is null, owner in as_accesses_all is cleared
5273: -- already.
5274: UPDATE as_accesses_all
5275: SET owner_flag = 'N'
5276: WHERE sales_lead_id = p_sales_lead_id;
5277: END IF;
5278:
5279: -- If referral_type IS NOT NULL, owner has freeze_flag = 'Y' always.
5280: -- Otherwise, owner's freeze_flag is the same as as_sales_leads.accept_flag
5281: IF l_access_exist_flag = 'Y'
5282: THEN
5283: UPDATE as_accesses_all
5284: SET team_leader_flag = 'Y',
5285: owner_flag = 'Y'
5286: -- created_by_tap_flag = 'Y'
5287: WHERE sales_lead_id = p_sales_lead_id
5293: FETCH c_get_open_status_flag INTO l_open_status_flag,
5294: l_lead_rank_score, l_creation_date;
5295: CLOSE c_get_open_status_flag;
5296:
5297: INSERT INTO as_accesses_all
5298: (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
5299: ,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
5300: ,PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
5301: ,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
5497: -- Leads re-route must not be routed back to a resource that has previously
5498: -- owned the lead before.
5499: CURSOR c_get_lead_resource(c_sales_lead_id NUMBER) IS
5500: SELECT ACC.SALESFORCE_ID, ACC.SALES_GROUP_ID, ACC.PERSON_ID, 'T'
5501: FROM AS_ACCESSES_ALL ACC
5502: WHERE ACC.SALES_LEAD_ID = c_sales_lead_id
5503: AND ACC.CREATED_BY_TAP_FLAG = 'Y'
5504: AND NOT EXISTS (
5505: SELECT 1
5660: WHERE SALES_LEAD_ID = c_sales_lead_id;
5661:
5662: CURSOR c_get_resource_avail(c_sales_lead_id NUMBER) IS
5663: SELECT 'Y'
5664: FROM AS_ACCESSES_ALL ACC
5665: WHERE ACC.SALES_LEAD_ID = c_sales_lead_id
5666: AND ACC.CREATED_BY_TAP_FLAG = 'Y';
5667: BEGIN
5668: IF (AS_DEBUG_LOW_ON) THEN
5893:
5894: CURSOR c_access_exist(c_sales_lead_id NUMBER, c_resource_id NUMBER,
5895: c_group_id NUMBER) IS
5896: SELECT freeze_flag
5897: FROM as_accesses_all
5898: WHERE sales_lead_id = c_sales_lead_id
5899: AND salesforce_id = c_resource_id
5900: AND ((sales_group_id = c_group_id) OR
5901: (sales_group_id IS NULL AND c_group_id IS NULL));
6061: OPEN c_access_exist(p_sales_lead_id, p_resource_id, p_group_id);
6062: FETCH c_access_exist INTO l_access_exist_flag;
6063: CLOSE c_access_exist;
6064:
6065: -- Clear any owner for as_accesses_all
6066: -- There may be more than one owner_flag='Y' for the lead in
6067: -- as_accesses_all:
6068: -- 1. When owner rejects the lead
6069: -- 2. When monitoring engine times out
6063: CLOSE c_access_exist;
6064:
6065: -- Clear any owner for as_accesses_all
6066: -- There may be more than one owner_flag='Y' for the lead in
6067: -- as_accesses_all:
6068: -- 1. When owner rejects the lead
6069: -- 2. When monitoring engine times out
6070: UPDATE as_accesses_all
6071: SET owner_flag = 'N'
6066: -- There may be more than one owner_flag='Y' for the lead in
6067: -- as_accesses_all:
6068: -- 1. When owner rejects the lead
6069: -- 2. When monitoring engine times out
6070: UPDATE as_accesses_all
6071: SET owner_flag = 'N'
6072: WHERE sales_lead_id = p_sales_lead_id;
6073:
6074: IF l_access_exist_flag IS NOT NULL
6078: IF l_access_exist_flag = 'Y'
6079: THEN
6080: l_freeze_flag := 'Y';
6081: END IF;
6082: UPDATE as_accesses_all
6083: SET team_leader_flag = 'Y',
6084: owner_flag = 'Y',
6085: freeze_flag = l_freeze_flag,
6086: created_by_tap_flag = 'Y'
6093: FETCH c_get_open_status_flag INTO l_open_status_flag,
6094: l_lead_rank_score, l_creation_date;
6095: CLOSE c_get_open_status_flag;
6096:
6097: INSERT INTO as_accesses_all
6098: (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
6099: ,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
6100: ,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
6101: ,OWNER_FLAG, CREATED_BY_TAP_FLAG
6261: UPDATE as_territory_accesses
6262: SET request_id = NULL
6263: WHERE access_id IN
6264: (SELECT a.access_id
6265: FROM as_accesses_all a
6266: WHERE a.sales_lead_id = p_sales_lead_id
6267: AND a.request_id = p_request_id)
6268: AND request_id = p_request_id;
6269:
6266: WHERE a.sales_lead_id = p_sales_lead_id
6267: AND a.request_id = p_request_id)
6268: AND request_id = p_request_id;
6269:
6270: UPDATE as_accesses_all
6271: SET request_id = NULL
6272: WHERE request_id = p_request_id
6273: AND sales_lead_id = p_sales_lead_id;
6274: END Process_Access_Record;