[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
1231: l_data VARCHAR2(70);
1232: l_index_out NUMBER;
1233:
1234:
1235: -- The following variables are for as_accesses_all
1236: l_access_id_tbl NUMBER_TABLE;
1237: l_owner_flag_tbl FLAG_TABLE;
1238:
1239: -- The following variables are for as_territory_accesses
2290: AND res.category = c_employee; --'EMPLOYEE';
2291:
2292: -- Get access_id, terr_id for the records that come from LEAD territory.
2293: -- Delete these records before new resource records are created in
2294: -- AS_ACCESSES_ALL table.
2295: CURSOR C_Get_Acc_Terr(c_sales_lead_id NUMBER) IS
2296: SELECT ACC.ACCESS_ID, TERRACC.TERRITORY_ID
2297: FROM AS_ACCESSES_ALL ACC, AS_TERRITORY_ACCESSES TERRACC
2298: WHERE ACC.FREEZE_FLAG = 'N'
2293: -- Delete these records before new resource records are created in
2294: -- AS_ACCESSES_ALL table.
2295: CURSOR C_Get_Acc_Terr(c_sales_lead_id NUMBER) IS
2296: SELECT ACC.ACCESS_ID, TERRACC.TERRITORY_ID
2297: FROM AS_ACCESSES_ALL ACC, AS_TERRITORY_ACCESSES TERRACC
2298: WHERE ACC.FREEZE_FLAG = 'N'
2299: AND ACC.SALES_LEAD_ID = c_sales_lead_id
2300: AND ACC.OWNER_FLAG = 'N'
2301: AND ACC.ACCESS_ID = TERRACC.ACCESS_ID;
2307:
2308: -- Check whether owner exists or not
2309: CURSOR c_check_owner_exists(c_sales_lead_id NUMBER) IS
2310: SELECT 'Y'
2311: FROM as_accesses_all acc
2312: WHERE acc.sales_lead_id = c_sales_lead_id
2313: AND acc.owner_flag = 'Y';
2314:
2315: l_api_name CONSTANT VARCHAR2(30)
2334:
2335: l_data VARCHAR2(70);
2336: l_index_out NUMBER;
2337:
2338: -- The following variables are for as_accesses_all
2339: l_access_id_tbl NUMBER_TABLE;
2340: l_resource_id NUMBER;
2341: l_group_id NUMBER;
2342: l_person_id NUMBER;
2500: END IF;
2501:
2502: -- Get access_id, terr_id for the records that come from LEAD territory.
2503: -- Delete these records before new resource records are created in
2504: -- AS_ACCESSES_ALL table.
2505: IF (AS_DEBUG_LOW_ON) THEN
2506: AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2507: 'customer_id=' || l_customer_id);
2508: AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2531: END LOOP;
2532: END IF;
2533:
2534: FORALL l_i IN 1..l_ta_access_id_tbl.count
2535: DELETE FROM AS_ACCESSES_ALL
2536: WHERE ACCESS_ID = l_ta_access_id_tbl(l_i);
2537:
2538: FORALL l_i IN 1..l_ta_terr_id_tbl.count
2539: DELETE FROM AS_TERRITORY_ACCESSES
2541: AND TERRITORY_ID = l_ta_terr_id_tbl(l_i);
2542: END IF;
2543:
2544: -- Delete non-frozen resources who are not from territory.
2545: DELETE FROM as_accesses_all acc
2546: WHERE acc.sales_lead_id = p_sales_lead_id
2547: AND acc.freeze_flag = 'N'
2548: --AND acc.salesforce_id <> p_identity_salesforce_id
2549: AND NOT EXISTS (
2908: END LOOP; -- l_AssignResources_tbl.first..l_AssignResources_tbl.last
2909: END IF; -- l_AssignResources_tbl.COUNT > 0
2910: Flush_Access_Records(l_request_id);
2911:
2912: -- If user change address_id, AS_ACCESSES_ALL table should be updated.
2913: -- There may be some records untouched, so update here, instead of
2914: -- in Flush_Access_Records.
2915: IF l_old_address_id <> l_new_address_id
2916: THEN
2913: -- There may be some records untouched, so update here, instead of
2914: -- in Flush_Access_Records.
2915: IF l_old_address_id <> l_new_address_id
2916: THEN
2917: UPDATE as_accesses_all
2918: SET address_id = l_new_address_id
2919: WHERE sales_lead_id = p_sales_lead_id
2920: AND address_id = l_old_address_id;
2921: ELSIF l_old_address_id IS NULL AND l_new_address_id IS NOT NULL
2919: WHERE sales_lead_id = p_sales_lead_id
2920: AND address_id = l_old_address_id;
2921: ELSIF l_old_address_id IS NULL AND l_new_address_id IS NOT NULL
2922: THEN
2923: UPDATE as_accesses_all
2924: SET address_id = l_new_address_id
2925: WHERE sales_lead_id = p_sales_lead_id
2926: AND address_id IS NULL;
2927: ELSIF l_old_address_id IS NOT NULL AND l_new_address_id IS NULL
2925: WHERE sales_lead_id = p_sales_lead_id
2926: AND address_id IS NULL;
2927: ELSIF l_old_address_id IS NOT NULL AND l_new_address_id IS NULL
2928: THEN
2929: UPDATE as_accesses_all
2930: SET address_id = NULL
2931: WHERE sales_lead_id = p_sales_lead_id
2932: AND address_id = l_old_address_id;
2933: END IF;
2948:
2949: DELETE FROM AS_TERRITORY_ACCESSES
2950: WHERE access_id IN
2951: (SELECT a.access_id
2952: FROM as_accesses_all a
2953: WHERE a.sales_lead_id = p_sales_lead_id
2954: AND a.freeze_flag = 'N'
2955: AND a.request_id = l_request_id)
2956: AND REQUEST_ID IS NULL;
2954: AND a.freeze_flag = 'N'
2955: AND a.request_id = l_request_id)
2956: AND REQUEST_ID IS NULL;
2957:
2958: DELETE FROM AS_ACCESSES_ALL
2959: WHERE SALES_LEAD_ID = p_sales_lead_id
2960: AND FREEZE_FLAG = 'N'
2961: AND REQUEST_ID IS NULL;
2962:
3486: )
3487: IS
3488: l_found VARCHAR2(1) := 'N';
3489: l_access_id NUMBER;
3490: l_full_access_flag VARCHAR2(1); -- from AS_ACCESSES_ALL table
3491: --l_full_access_flag VARCHAR2(1); -- from JTF_TERR_RSC table
3492: l_employee_person_id NUMBER;
3493: l_access_id_new NUMBER;
3494:
3498:
3499: CURSOR c_get_access_id(c_resource_id NUMBER, c_group_id NUMBER,
3500: c_sales_lead_id NUMBER) IS
3501: SELECT ACCESS_ID, TEAM_LEADER_FLAG
3502: FROM AS_ACCESSES_ALL
3503: WHERE SALESFORCE_ID = c_resource_id
3504: AND ((SALES_GROUP_ID IS NULL AND c_group_id IS NULL)
3505: OR SALES_GROUP_ID = c_group_id)
3506: AND SALES_LEAD_ID = c_sales_lead_id
3889: END LOOP;
3890: END IF;
3891:
3892: FORALL l_i IN 1..l_u_num_rows
3893: UPDATE AS_ACCESSES_ALL
3894: SET LAST_UPDATE_DATE = SYSDATE,
3895: LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3896: LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
3897: PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID,
3950: l_lead_rank_score, l_creation_date;
3951: CLOSE c_get_open_status_flag;
3952:
3953: FORALL l_i IN 1..l_i_num_rows
3954: INSERT INTO AS_ACCESSES_ALL
3955: (ACCESS_ID,
3956: ACCESS_TYPE,
3957: SALESFORCE_ID,
3958: SALES_GROUP_ID,
4071: BEGIN
4072: DELETE FROM as_territory_accesses
4073: WHERE access_id IN (
4074: SELECT acc.access_id
4075: FROM as_accesses_all acc
4076: WHERE acc.sales_lead_id = p_sales_lead_id
4077: AND acc.request_id = p_request_id
4078: AND acc.owner_flag = 'N'
4079: AND acc.created_by_tap_flag = 'Y');
4077: AND acc.request_id = p_request_id
4078: AND acc.owner_flag = 'N'
4079: AND acc.created_by_tap_flag = 'Y');
4080:
4081: DELETE FROM as_accesses_all
4082: WHERE sales_lead_id = p_sales_lead_id
4083: AND request_id = p_request_id
4084: AND owner_flag = 'N'
4085: AND created_by_tap_flag = 'Y';
4125: -- care which group_id is in the sales team as long as this resource is
4126: -- in the sales team.
4127: CURSOR c_check_sales_team(c_resource_id NUMBER, c_sales_lead_id NUMBER) IS
4128: SELECT acc.access_id, team_leader_flag
4129: FROM as_accesses_all acc
4130: WHERE acc.salesforce_id = c_resource_id
4131: AND acc.sales_lead_id = c_sales_lead_id;
4132:
4133: CURSOR c_get_group_id (c_resource_id NUMBER, c_rs_group_member VARCHAR2,
4245: -- Current user is not in sales team, insert this
4246: -- resource as sales team member. Since this resource doesn't
4247: -- come from territory, don't insert into
4248: -- as_territory_accesses
4249: INSERT INTO as_accesses_all
4250: (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
4251: ,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
4252: ,PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
4253: ,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG
4266: FROM sys.dual;
4267: ELSIF l_team_leader_flag = 'N'
4268: THEN
4269: -- lead creator is in sales team, but no full access
4270: UPDATE as_accesses_all
4271: SET team_leader_flag = 'Y'
4272: WHERE access_id = l_access_id;
4273: END IF; -- l_access_id IS NULL
4274:
4286: | NOTES
4287: | This procedure will get resource_id from the profile
4288: | AS_DEFAULT_RESOURCE_ID. If this profile is not set, it will get
4289: | current user's resource_id. Once resource_id is gotten
4290: | AS_SALES_LEADS_LOG, AS_SALES_LEADS, AS_ACCESSES_ALL will be updated.
4291: |
4292: | HISTORY
4293: | 12/05/01 SOLIN Created
4294: *-------------------------------------------------------------------------*/
4358:
4359: CURSOR c_access_exist(c_sales_lead_id NUMBER, c_resource_id NUMBER,
4360: c_group_id NUMBER) IS
4361: SELECT 'Y'
4362: FROM as_accesses_all
4363: WHERE sales_lead_id = c_sales_lead_id
4364: AND salesforce_id = c_resource_id
4365: AND ((sales_group_id = c_group_id) OR
4366: (sales_group_id IS NULL AND c_group_id IS NULL));
4615: 'access exist?'|| l_access_exist_flag);
4616: END IF;
4617: IF l_reject_reason_code IS NOT NULL
4618: THEN
4619: -- Clear any owner for as_accesses_all
4620: -- If reject reason_code IS NULL, this workflow process must come
4621: -- from lead owner is null, owner in as_accesses_all is cleared
4622: -- already.
4623: UPDATE as_accesses_all
4617: IF l_reject_reason_code IS NOT NULL
4618: THEN
4619: -- Clear any owner for as_accesses_all
4620: -- If reject reason_code IS NULL, this workflow process must come
4621: -- from lead owner is null, owner in as_accesses_all is cleared
4622: -- already.
4623: UPDATE as_accesses_all
4624: SET owner_flag = 'N'
4625: WHERE sales_lead_id = p_sales_lead_id;
4619: -- Clear any owner for as_accesses_all
4620: -- If reject reason_code IS NULL, this workflow process must come
4621: -- from lead owner is null, owner in as_accesses_all is cleared
4622: -- already.
4623: UPDATE as_accesses_all
4624: SET owner_flag = 'N'
4625: WHERE sales_lead_id = p_sales_lead_id;
4626: END IF;
4627:
4635: -- Default resource is not in sales team, insert this
4636: -- resource as sales team member. Since this resource doesn't
4637: -- come from territory, don't insert into
4638: -- as_territory_accesses
4639: INSERT INTO as_accesses_all
4640: (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
4641: ,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
4642: ,PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
4643: ,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
4655: FROM sys.dual;
4656: ELSE
4657: -- Default resource is in sales team, update this resource
4658: -- as owner.
4659: UPDATE as_accesses_all
4660: SET team_leader_flag = 'Y',
4661: owner_flag = 'Y',
4662: freeze_flag = l_freeze_flag,
4663: request_id = p_request_id
4751:
4752: CURSOR c_access_exist(c_sales_lead_id NUMBER, c_resource_id NUMBER,
4753: c_group_id NUMBER) IS
4754: SELECT 'Y'
4755: FROM as_accesses_all
4756: WHERE sales_lead_id = c_sales_lead_id
4757: AND salesforce_id = c_resource_id
4758: AND ((sales_group_id = c_group_id) OR
4759: (sales_group_id IS NULL AND c_group_id IS NULL));
4806: Set_Default_Lead_Owner(p_sales_lead_id, p_salesgroup_id,
4807: p_request_id,p_resource_id, x_return_status, x_msg_count, x_msg_data);
4808: ELSE
4809: -- Customer return owner, update AS_SALES_LEADS_LOG and
4810: -- AS_ACCESSES_ALL, AS_SALES_LEADS
4811: IF (AS_DEBUG_LOW_ON) THEN
4812: AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4813: 'res id in upd=' || l_Resource_Id);
4814: AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4954: CLOSE c_access_exist;
4955:
4956: IF l_reject_reason_code IS NOT NULL
4957: THEN
4958: -- Clear any owner for as_accesses_all
4959: -- If reject reason_code IS NULL, this workflow process must come
4960: -- from lead owner is null, owner in as_accesses_all is cleared
4961: -- already.
4962: UPDATE as_accesses_all
4956: IF l_reject_reason_code IS NOT NULL
4957: THEN
4958: -- Clear any owner for as_accesses_all
4959: -- If reject reason_code IS NULL, this workflow process must come
4960: -- from lead owner is null, owner in as_accesses_all is cleared
4961: -- already.
4962: UPDATE as_accesses_all
4963: SET owner_flag = 'N'
4964: WHERE sales_lead_id = p_sales_lead_id;
4958: -- Clear any owner for as_accesses_all
4959: -- If reject reason_code IS NULL, this workflow process must come
4960: -- from lead owner is null, owner in as_accesses_all is cleared
4961: -- already.
4962: UPDATE as_accesses_all
4963: SET owner_flag = 'N'
4964: WHERE sales_lead_id = p_sales_lead_id;
4965: END IF;
4966:
4965: END IF;
4966:
4967: IF l_access_exist_flag = 'Y'
4968: THEN
4969: UPDATE as_accesses_all
4970: SET team_leader_flag = 'Y',
4971: owner_flag = 'Y',
4972: freeze_flag = l_freeze_flag,
4973: created_by_tap_flag = 'Y'
4980: FETCH c_get_open_status_flag INTO l_open_status_flag,
4981: l_lead_rank_score, l_creation_date;
4982: CLOSE c_get_open_status_flag;
4983:
4984: INSERT INTO as_accesses_all
4985: (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
4986: ,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
4987: ,PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
4988: ,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
5021: IS
5022: CURSOR c_access_exist(c_sales_lead_id NUMBER, c_resource_id NUMBER,
5023: c_group_id NUMBER) IS
5024: SELECT 'Y'
5025: FROM as_accesses_all
5026: WHERE sales_lead_id = c_sales_lead_id
5027: AND salesforce_id = c_resource_id
5028: AND ((sales_group_id = c_group_id) OR
5029: (sales_group_id IS NULL AND c_group_id IS NULL));
5290: CLOSE c_access_exist;
5291:
5292: IF l_reject_reason_code IS NOT NULL
5293: THEN
5294: -- Clear any owner for as_accesses_all
5295: -- If reject reason_code IS NULL, this workflow process must come
5296: -- from lead owner is null, owner in as_accesses_all is cleared
5297: -- already.
5298: UPDATE as_accesses_all
5292: IF l_reject_reason_code IS NOT NULL
5293: THEN
5294: -- Clear any owner for as_accesses_all
5295: -- If reject reason_code IS NULL, this workflow process must come
5296: -- from lead owner is null, owner in as_accesses_all is cleared
5297: -- already.
5298: UPDATE as_accesses_all
5299: SET owner_flag = 'N'
5300: WHERE sales_lead_id = p_sales_lead_id;
5294: -- Clear any owner for as_accesses_all
5295: -- If reject reason_code IS NULL, this workflow process must come
5296: -- from lead owner is null, owner in as_accesses_all is cleared
5297: -- already.
5298: UPDATE as_accesses_all
5299: SET owner_flag = 'N'
5300: WHERE sales_lead_id = p_sales_lead_id;
5301: END IF;
5302:
5303: -- If referral_type IS NOT NULL, owner has freeze_flag = 'Y' always.
5304: -- Otherwise, owner's freeze_flag is the same as as_sales_leads.accept_flag
5305: IF l_access_exist_flag = 'Y'
5306: THEN
5307: UPDATE as_accesses_all
5308: SET team_leader_flag = 'Y',
5309: owner_flag = 'Y'
5310: -- created_by_tap_flag = 'Y'
5311: WHERE sales_lead_id = p_sales_lead_id
5317: FETCH c_get_open_status_flag INTO l_open_status_flag,
5318: l_lead_rank_score, l_creation_date;
5319: CLOSE c_get_open_status_flag;
5320:
5321: INSERT INTO as_accesses_all
5322: (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
5323: ,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
5324: ,PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
5325: ,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
5521: -- Leads re-route must not be routed back to a resource that has previously
5522: -- owned the lead before.
5523: CURSOR c_get_lead_resource(c_sales_lead_id NUMBER) IS
5524: SELECT ACC.SALESFORCE_ID, ACC.SALES_GROUP_ID, ACC.PERSON_ID, 'T'
5525: FROM AS_ACCESSES_ALL ACC
5526: WHERE ACC.SALES_LEAD_ID = c_sales_lead_id
5527: AND ACC.CREATED_BY_TAP_FLAG = 'Y'
5528: AND NOT EXISTS (
5529: SELECT 1
5684: WHERE SALES_LEAD_ID = c_sales_lead_id;
5685:
5686: CURSOR c_get_resource_avail(c_sales_lead_id NUMBER) IS
5687: SELECT 'Y'
5688: FROM AS_ACCESSES_ALL ACC
5689: WHERE ACC.SALES_LEAD_ID = c_sales_lead_id
5690: AND ACC.CREATED_BY_TAP_FLAG = 'Y';
5691: BEGIN
5692: IF (AS_DEBUG_LOW_ON) THEN
5917:
5918: CURSOR c_access_exist(c_sales_lead_id NUMBER, c_resource_id NUMBER,
5919: c_group_id NUMBER) IS
5920: SELECT freeze_flag
5921: FROM as_accesses_all
5922: WHERE sales_lead_id = c_sales_lead_id
5923: AND salesforce_id = c_resource_id
5924: AND ((sales_group_id = c_group_id) OR
5925: (sales_group_id IS NULL AND c_group_id IS NULL));
6085: OPEN c_access_exist(p_sales_lead_id, p_resource_id, p_group_id);
6086: FETCH c_access_exist INTO l_access_exist_flag;
6087: CLOSE c_access_exist;
6088:
6089: -- Clear any owner for as_accesses_all
6090: -- There may be more than one owner_flag='Y' for the lead in
6091: -- as_accesses_all:
6092: -- 1. When owner rejects the lead
6093: -- 2. When monitoring engine times out
6087: CLOSE c_access_exist;
6088:
6089: -- Clear any owner for as_accesses_all
6090: -- There may be more than one owner_flag='Y' for the lead in
6091: -- as_accesses_all:
6092: -- 1. When owner rejects the lead
6093: -- 2. When monitoring engine times out
6094: UPDATE as_accesses_all
6095: SET owner_flag = 'N'
6090: -- There may be more than one owner_flag='Y' for the lead in
6091: -- as_accesses_all:
6092: -- 1. When owner rejects the lead
6093: -- 2. When monitoring engine times out
6094: UPDATE as_accesses_all
6095: SET owner_flag = 'N'
6096: WHERE sales_lead_id = p_sales_lead_id;
6097:
6098: IF l_access_exist_flag IS NOT NULL
6102: IF l_access_exist_flag = 'Y'
6103: THEN
6104: l_freeze_flag := 'Y';
6105: END IF;
6106: UPDATE as_accesses_all
6107: SET team_leader_flag = 'Y',
6108: owner_flag = 'Y',
6109: freeze_flag = l_freeze_flag,
6110: created_by_tap_flag = 'Y'
6117: FETCH c_get_open_status_flag INTO l_open_status_flag,
6118: l_lead_rank_score, l_creation_date;
6119: CLOSE c_get_open_status_flag;
6120:
6121: INSERT INTO as_accesses_all
6122: (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY
6123: ,CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN
6124: ,ACCESS_TYPE, FREEZE_FLAG, REASSIGN_FLAG, TEAM_LEADER_FLAG
6125: ,OWNER_FLAG, CREATED_BY_TAP_FLAG
6288: UPDATE as_territory_accesses
6289: SET request_id = NULL
6290: WHERE access_id IN
6291: (SELECT a.access_id
6292: FROM as_accesses_all a
6293: WHERE a.sales_lead_id = p_sales_lead_id
6294: AND a.request_id = p_request_id)
6295: AND request_id = p_request_id;
6296:
6293: WHERE a.sales_lead_id = p_sales_lead_id
6294: AND a.request_id = p_request_id)
6295: AND request_id = p_request_id;
6296:
6297: UPDATE as_accesses_all
6298: SET request_id = NULL
6299: WHERE request_id = p_request_id
6300: AND sales_lead_id = p_sales_lead_id;
6301: END Process_Access_Record;