[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;
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
1240: l_ta_access_id_tbl NUMBER_TABLE;
1241: l_ta_terr_id_tbl NUMBER_TABLE;
1242:
1243: -- index of the above variables
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;
2340: l_resource_id NUMBER;
2341: l_group_id NUMBER;
2342: l_person_id NUMBER;
2343:
2344: -- The following variables are for as_territory_accesses
2345: l_ta_access_id_tbl NUMBER_TABLE;
2346: l_ta_terr_id_tbl NUMBER_TABLE;
2347:
2348: -- index of the above variables
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
2540: WHERE ACCESS_ID = l_ta_access_id_tbl(l_i)
2541: AND TERRITORY_ID = l_ta_terr_id_tbl(l_i);
2542: END IF;
2543:
2547: AND acc.freeze_flag = 'N'
2548: --AND acc.salesforce_id <> p_identity_salesforce_id
2549: AND NOT EXISTS (
2550: SELECT 1
2551: FROM as_territory_accesses terracc
2552: WHERE terracc.access_id = acc.access_id);
2553:
2554: g_i_count := 0;
2555: g_u_count := 0;
2945: AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2946: 'owner exist? ' || l_owner_exists_flag);
2947: 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
3745:
3746: CURSOR c_get_terracc_count(c_access_id NUMBER, c_territory_id NUMBER)
3747: IS
3748: SELECT 1
3749: FROM AS_TERRITORY_ACCESSES
3750: WHERE ACCESS_ID = c_access_id
3751: AND TERRITORY_ID = c_territory_id
3752: AND ROWNUM <= 1;
3753: BEGIN
3913: END LOOP;
3914: END IF;
3915:
3916: FORALL l_i IN 1..l_tu_num_rows
3917: UPDATE AS_TERRITORY_ACCESSES
3918: SET LAST_UPDATE_DATE = SYSDATE,
3919: LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3920: LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
3921: PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID,
4021: END LOOP;
4022: END IF;
4023:
4024: FORALL l_i IN 1..l_ti_num_rows
4025: INSERT INTO AS_TERRITORY_ACCESSES
4026: (ACCESS_ID,
4027: TERRITORY_ID,
4028: USER_TERRITORY_ID,
4029: LAST_UPDATE_DATE,
4068: p_sales_lead_id IN NUMBER,
4069: p_request_id IN NUMBER)
4070: IS
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
4244:
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
4634:
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
6284: p_sales_lead_id IN NUMBER,
6285: p_request_id IN NUMBER)
6286: IS
6287: BEGIN
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