[Home] [Help]
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
44: -- may be updated in lead creation process
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)
139: g_ti_access_id NUMBER_TABLE;
140: g_ti_territory_id NUMBER_TABLE;
141:
142: -- varray for update (AS_TERRITORY_ACCESSES)
138: -- varray for insert (AS_TERRITORY_ACCESSES)
139: g_ti_access_id NUMBER_TABLE;
140: g_ti_territory_id NUMBER_TABLE;
141:
142: -- varray for update (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
152: g_ti_count NUMBER := 0;
153: -- length of Update array for AS_TERRITORY_ACCESSES
154: g_tu_count NUMBER := 0;
155:
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
154: g_tu_count NUMBER := 0;
155:
156:
157: g_resource_id_tbl AS_LEAD_ROUTING_WF.NUMBER_TABLE;
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
1239: l_ta_access_id_tbl NUMBER_TABLE;
1240: l_ta_terr_id_tbl NUMBER_TABLE;
1241:
1242: -- index of the above variables
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;
2339: l_resource_id NUMBER;
2340: l_group_id NUMBER;
2341: l_person_id NUMBER;
2342:
2343: -- The following variables are for as_territory_accesses
2344: l_ta_access_id_tbl NUMBER_TABLE;
2345: l_ta_terr_id_tbl NUMBER_TABLE;
2346:
2347: -- index of the above variables
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
2539: WHERE ACCESS_ID = l_ta_access_id_tbl(l_i)
2540: AND TERRITORY_ID = l_ta_terr_id_tbl(l_i);
2541: END IF;
2542:
2546: AND acc.freeze_flag = 'N'
2547: --AND acc.salesforce_id <> p_identity_salesforce_id
2548: AND NOT EXISTS (
2549: SELECT 1
2550: FROM as_territory_accesses terracc
2551: WHERE terracc.access_id = acc.access_id);
2552:
2553: g_i_count := 0;
2554: g_u_count := 0;
2944: AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2945: 'owner exist? ' || l_owner_exists_flag);
2946: 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
3744:
3745: CURSOR c_get_terracc_count(c_access_id NUMBER, c_territory_id NUMBER)
3746: IS
3747: SELECT 1
3748: FROM AS_TERRITORY_ACCESSES
3749: WHERE ACCESS_ID = c_access_id
3750: AND TERRITORY_ID = c_territory_id
3751: AND ROWNUM <= 1;
3752: BEGIN
3912: END LOOP;
3913: END IF;
3914:
3915: FORALL l_i IN 1..l_tu_num_rows
3916: UPDATE AS_TERRITORY_ACCESSES
3917: SET LAST_UPDATE_DATE = SYSDATE,
3918: LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3919: LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
3920: PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID,
4020: END LOOP;
4021: END IF;
4022:
4023: FORALL l_i IN 1..l_ti_num_rows
4024: INSERT INTO AS_TERRITORY_ACCESSES
4025: (ACCESS_ID,
4026: TERRITORY_ID,
4027: USER_TERRITORY_ID,
4028: LAST_UPDATE_DATE,
4067: p_sales_lead_id IN NUMBER,
4068: p_request_id IN NUMBER)
4069: IS
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
4243:
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
4612:
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
6257: p_sales_lead_id IN NUMBER,
6258: p_request_id IN NUMBER)
6259: IS
6260: BEGIN
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