1 PACKAGE BODY AML_PURGE_SALES_LEADS AS
2 /* $Header: amlslprgb.pls 115.5 2004/02/09 12:13:42 bmuthukr noship $ */
3 -- Start of Comments
4 -- Package name : AML_PURGE_SALES_LEADS
5 -- Purpose : Sales Leads Management
6 -- NOTE :
7 -- History :
8 -- 10/17/2003 BMUTHUKR Created
9 -- Purpose : For purging the unqualified leads.
10 --
11 -- 11/24/2003 BMUTHUKR Modified
12 -- This program should delete only the unqualifed leads that are not converted to opportunity.
13 -- Hence adding additional condition in the where clause.
14 --
15 -- 12/10/2003 BMUTHUKR Modified
16 -- Added TRUNC in the SELECT statement in the purge_unqualified_leads procedure body to resolve bug 3307084.
17 -- It is already there in the cursor.
18 --
19 -- 01/20/2004 BMUTHUKR Modified
20 -- Included the profile AS_DEFAULT_LEAD_STATUS in the where clause. Rrefer bug 3376658.
21 --
22 -- END of Comments
23
24
25 TYPE del_count IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
26
27 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AML_PURGE_SALES_LEADS';
28 G_FILE_NAME CONSTANT VARCHAR2(15) := 'amlslprgb.pls';
29 G_DEBUG_MODE VARCHAR2(1) := 'Y';
30 RECS_DELETED del_count;
31
32 PROCEDURE AML_Debug(p_flag in number default 2, p_mesg in Varchar2)
33 IS
34 BEGIN
35
36 if nvl(g_debug_mode,'N') = 'Y' and p_flag = 2 then
37 fnd_file.put(1, substr(p_mesg,1,255));
38 fnd_file.new_line(1,1);
39 elsif p_flag = 1 then
40 fnd_file.put(1, substr(p_mesg,1,255));
41 fnd_file.new_line(1,1);
42 end if;
43
44 END AML_Debug;
45
46
47 PROCEDURE initialize_count IS
48 BEGIN
49 Recs_deleted(1) := 0;
50 Recs_deleted(2) := 0;
51 Recs_deleted(3) := 0;
52 Recs_deleted(4) := 0;
53 Recs_deleted(5) := 0;
54 Recs_deleted(6) := 0;
55 -- Recs_deleted(7) := 0;
56 Recs_deleted(8) := 0;
57 Recs_deleted(9) := 0;
58 Recs_deleted(10) := 0;
59 Recs_deleted(11) := 0;
60 END initialize_count;
61
62 PROCEDURE write_count IS
63 l_no_of_rows_deleted CONSTANT varchar2(100) := ' rows deleted from the table ';
64 BEGIN
65 aml_Debug(1,Recs_deleted(1)||l_no_of_rows_deleted||'AS_ACCESSES_ALL');
66 aml_debug(1,Recs_deleted(2)||l_no_of_rows_deleted||'AS_CHANGED_ACCOUNTS_ALL');
67 aml_debug(1,Recs_deleted(3)||l_no_of_rows_deleted||'AML_INTERACTION_LEADS');
68 aml_debug(1,Recs_deleted(4)||l_no_of_rows_deleted||'AS_SALES_LEADS_LOG');
69 aml_debug(1,Recs_deleted(5)||l_no_of_rows_deleted||'AS_SALES_LEAD_CONTACTS');
70 aml_debug(1,Recs_deleted(6)||l_no_of_rows_deleted||'AS_SALES_LEAD_LINES');
71 -- aml_debug(1,Recs_deleted(7)||l_no_of_rows_deleted||'AS_SALES_LEAD_OPPORTUNITY');
72 aml_debug(1,Recs_deleted(8)||l_no_of_rows_deleted||'AML_MONITOR_LOG');
73 aml_debug(1,Recs_deleted(9)||l_no_of_rows_deleted||'PV_ENTITY_RULES_APPLIED');
74 aml_debug(1,Recs_deleted(10)||l_no_of_rows_deleted||'AS_TERRITORY_ACCESSES');
75 aml_debug(1,Recs_deleted(11)||l_no_of_rows_deleted||'AS_SALES_LEADS');
76 END write_count;
77
78
79 PROCEDURE Delete_Unqualified_Leads(P_Id_Tab IN AML_PURGE_SALES_LEADS.Sales_Lead_Id_Tab) IS
80 BEGIN
81
82 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
83 DELETE
84 FROM AS_ACCESSES_ALL
85 WHERE Sales_Lead_Id = P_Id_Tab(I);
86 Recs_deleted(1) := Recs_deleted(1) + SQL%ROWCOUNT;
87
88 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
89 DELETE
90 FROM AS_CHANGED_ACCOUNTS_ALL
91 WHERE Sales_Lead_Id = P_Id_Tab(I);
92 Recs_deleted(2) := Recs_deleted(2) + SQL%ROWCOUNT;
93
94 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
95 DELETE
96 FROM AML_INTERACTION_LEADS
97 WHERE Sales_Lead_Id = P_Id_Tab(I);
98 Recs_deleted(3) := Recs_deleted(3) + SQL%ROWCOUNT;
99
100 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
101 DELETE
102 FROM AS_SALES_LEADS_LOG
103 WHERE Sales_Lead_Id = P_Id_Tab(I);
104 Recs_deleted(4) := Recs_deleted(4) + SQL%ROWCOUNT;
105
106 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
107 DELETE
108 FROM AS_SALES_LEAD_CONTACTS
109 WHERE Sales_Lead_Id = P_Id_Tab(I);
110 Recs_deleted(5) := Recs_deleted(5) + SQL%ROWCOUNT;
111
112 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
113 DELETE
114 FROM AS_SALES_LEAD_LINES
115 WHERE Sales_Lead_Id = P_Id_Tab(I);
116 Recs_deleted(6) := Recs_deleted(6) + SQL%ROWCOUNT;
117
118 /*FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
119 DELETE
120 FROM AS_SALES_LEAD_OPPORTUNITY
121 WHERE Sales_Lead_Id = P_Id_Tab(I);
122 Recs_deleted(7) := Recs_deleted(7) + SQL%ROWCOUNT;*/
123
124 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
125 DELETE
126 FROM AML_MONITOR_LOG
127 WHERE Sales_Lead_Id = P_Id_Tab(I);
128 Recs_deleted(8) := Recs_deleted(8) + SQL%ROWCOUNT;
129
130 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
131 DELETE
132 FROM PV_ENTITY_RULES_APPLIED
133 WHERE Entity_Id = P_Id_Tab(I);
134 Recs_deleted(9) := Recs_deleted(9) + SQL%ROWCOUNT;
135
136 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
137 DELETE
138 FROM AS_TERRITORY_ACCESSES
139 WHERE Access_Id = P_Id_Tab(I);
140 Recs_deleted(10) := Recs_deleted(10) + SQL%ROWCOUNT;
141
142 FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
143 DELETE
144 FROM AS_SALES_LEADS
145 WHERE Sales_Lead_Id = P_Id_Tab(I);
146 Recs_deleted(11) := Recs_deleted(11) + SQL%ROWCOUNT;
147
148 END Delete_Unqualified_Leads;
149
150
151 /*-------------------------------------------------------------------------*
152 | PUBLIC ROUTINE
153 | Purge_Unqualified_Leads
154 |
155 | PURPOSE
156 | The main program to find the unqualified leads and then passes to the
157 | private procedure Purge_Unqualified_Leads in the form of Pl/Sql tables.
158 | Concurrent program will call this procedure.
159 |
160 *-------------------------------------------------------------------------*/
161 PROCEDURE Purge_Unqualified_Leads(
162 errbuf OUT NOCOPY VARCHAR2,
163 retcode OUT NOCOPY VARCHAR2,
164 p_start_date IN VARCHAR2,
165 p_end_date IN VARCHAR2,
166 p_debug_mode IN VARCHAR2 DEFAULT 'N',
167 p_trace_mode IN VARCHAR2 DEFAULT 'N') IS
168
169 Id_Tab AML_PURGE_SALES_LEADS.Sales_Lead_Id_Tab;
170 l_status boolean;
171 l_start_date date;
172 l_end_date date;
173
174 CURSOR Collect_Sales_Lead_Ids(l_start_date IN date, l_end_date IN date) IS
175 SELECT Sales_Lead_Id
176 FROM As_sales_leads
177 WHERE Qualified_Flag = 'N'
178 AND TRUNC(Creation_Date) BETWEEN TRUNC(l_start_date) AND TRUNC(l_end_date)
179 AND status_code <> fnd_profile.value('AS_LEAD_LINK_STATUS')
180 AND status_code = fnd_profile.value('AS_DEFAULT_LEAD_STATUS');
181
182 BEGIN
183 l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
184 l_end_date := to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
185 IF p_trace_mode = 'Y' THEN
186 dbms_session.set_sql_trace(TRUE);
187 ELSE
188 dbms_session.set_sql_trace(FALSE);
189 END IF;
190 initialize_count;
191 G_DEBUG_MODE := P_DEBUG_MODE;
192
193 aml_debug(2,'Purge Sales Leads starts');
194
195 /* First, unqualified the leads that needs to be purged are identified and the sales lead id is taken. This is stored in
196 a Pl/Sql table. Then all the records in child tables like AS_ACCESSES_ALL, AS_SALES_LEAD_LINES and the main
200 for the remaining records it has be done with a simple select statement.
197 table AS_SALES_LEADS are deleted. Since the number of records to be purged will be high this is done in batch
198 of 10000 records. The Sales Lead Ids which are stored in a Pl/Sql table are then passed to the local procedure
199 Delete_Unqualified_Leads. Since this procedure takes sales lead ids in batches of 10000,
201
202 For example, if there are 102300 unqualified leads, then this loop will be executed 10 times. There will be
203 2300 pending unqualified leads which will be processed in the simple SELECT that immediately follows this cursor.*/
204
205 OPEN Collect_Sales_Lead_Ids(l_start_date, l_end_date);
206 aml_debug(2,'Fetching data');
207 LOOP
208
209 IF Id_Tab.FIRST IS NOT NULL THEN
210 Id_Tab.DELETE;
211 END IF;
212
213 --Records are taken in multiples of 10000.
214 FETCH Collect_Sales_Lead_Ids BULK COLLECT INTO Id_Tab LIMIT 10000;
215
216 EXIT WHEN Collect_Sales_Lead_Ids%NOTFOUND; --If < 10000 records are remaining.
217 Delete_Unqualified_Leads(Id_Tab); --Pass the lead nos to the Purge_Qualified_Leads procedure to delete them.
218
219 END LOOP;
220 CLOSE Collect_Sales_Lead_Ids;
221
222 --Remaining records to be deleted. So pick sales lead ids for all the remaining records and put it in
223 --a Pl/Sql table.
224 SELECT Sales_Lead_Id BULK COLLECT
225 INTO Id_Tab
226 FROM AS_SALES_LEADS
227 WHERE Qualified_Flag = 'N'
228 AND TRUNC(Creation_Date) BETWEEN TRUNC(l_start_date) AND TRUNC(l_end_date)
229 AND status_code <> fnd_profile.value('AS_LEAD_LINK_STATUS')
230 AND status_code = fnd_profile.value('AS_DEFAULT_LEAD_STATUS');
231
232 IF Id_Tab.FIRST IS NOT NULL THEN --This should be executed only if atleast there is one record.
233 Delete_Unqualified_Leads(Id_Tab);
234 END IF;
235
236 COMMIT;
237 write_count;
238 aml_debug(2,'Purging sales leads completed successfully');
239
240 EXCEPTION
241 WHEN FND_API.G_EXC_ERROR THEN
242 aml_debug(2,'Expected error');
243
244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
245 aml_debug(2,'Unexpected error');
246
247 WHEN others THEN
248 aml_debug(2,'SQLCODE ' || to_char(SQLCODE) ||
249 ' SQLERRM ' || substr(SQLERRM, 1, 100));
250
251 errbuf := SQLERRM;
252 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
253 l_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', SQLERRM);
254
255 END Purge_Unqualified_Leads;
256
257 END AML_PURGE_SALES_LEADS;
[Home] [Help]