eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.BIL_DO_L1_LD_OPPTY_DLY_PKG

Source


1 PACKAGE BODY BIL_DO_L1_LD_OPPTY_DLY_PKG AS
2 /* $Header: billdl1b.pls 115.19 2002/01/29 13:56:05 pkm ship      $ */
3 
4   -- Global Variables and Constants
5      -- G_Debug will be modified to 'Y' programatically.
6      -- when the parameter passed for P_debug is Y
7      -- Once, this is set to 'Y', then the log file will be generated.
8      -- Otherwise, log file will not be generated. Only the output file
9      -- will be generated.
10      G_Debug    VARCHAR2(1) := 'N';
11      G_Trace    VARCHAR2(1) := 'N';
12 
13 
14 
15    -- Global variables for WHO variables and Concurrent program
16      G_request_id    NUMBER;
17      G_appl_id       NUMBER;
18      G_program_id    NUMBER;
19      G_user_id       NUMBER;
20      G_login_id      NUMBER;
21 
22 
23 
24 PROCEDURE Delete_Data (
25        ERRBUF            IN OUT VARCHAR2
26       ,RETCODE           IN OUT VARCHAR2
27       ,p_date IN DATE
28     ) ;
29 
30  PROCEDURE Init_Globals;
31 
32  PROCEDURE Insert_Data (
33        ERRBUF               IN OUT VARCHAR2
34       ,RETCODE              IN OUT VARCHAR2
35       ,p_date               IN DATE
36       ,p_degree             IN NUMBER
37     ) ;
38 
39   PROCEDURE Refresh_Data_Day
40     (
41         ERRBUF        IN OUT VARCHAR2
42       , RETCODE       IN OUT VARCHAR2
43       , p_date        IN  DATE
44       , p_delete_flag IN  VARCHAR2 DEFAULT 'Y'
45       , p_degree      IN  VARCHAR2   DEFAULT '4'
46       , p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
47       , p_trace_mode  IN  VARCHAR2 DEFAULT 'N'
48     ) ;
49 
50   PROCEDURE Reset_Table_Usages;
51 
52   PROCEDURE Set_Table_Usages;
53 
54 
55 
56       /*******Main procedure : initializes global variables, deletes and then inserts previous day's data
57     ERRBUFF:    error message returned by the proc
58     RETCODE:    completion status of the procedure
59     p_degree:   parallel degree
60     p_debug:    debug mode (yes or no)
61     p_trace:    trace mode (yes or no)
62   *******/
63   PROCEDURE Refresh_Data
64     (
65         ERRBUF        OUT VARCHAR2
66       , RETCODE       OUT VARCHAR2
67       , p_degree      IN  VARCHAR2   DEFAULT '4'
68       , p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
69       , p_trace_mode  IN  VARCHAR2 DEFAULT 'N'
70     ) IS
71 
72     -- Local variables
73     l_collect_for_date DATE     := TRUNC(SYSDATE-1); -- date for which data is collected; one day prior to collection date
74     l_degree           NUMBER   := TO_NUMBER(p_degree); -- parallel degree
75     l_collection_date_start DATE;
76     l_collection_date_loop DATE;
77 
78     BEGIN
79     RETCODE := 0;
80 
81       Init_Globals;
82 
83       -- Validate input parameters
84       IF p_debug_mode = 'Y' THEN
85          G_Debug := 'Y';
86       END IF;
87 
88       IF p_trace_mode = 'Y' THEN
89          G_Trace := 'Y';
90          EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
91       END IF;
92 
93 
94      BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Parameters for Refesh_Data - Debug: ' || p_debug_mode
95          || '  Trace: ' || p_trace_mode
96          || '  Parallel Degree: ' || p_degree, p_debug =>p_debug_mode);
97 
98 
99      Set_table_Usages;
100 
101     SELECT MAX(collection_date)+1
102     INTO l_collection_date_start
103     FROM bil_do_l1_ld_oppty_dly;
104 
105      l_collection_date_loop  := l_collection_date_start;
106      WHILE (l_collection_date_loop <=l_collect_for_date) LOOP
107       Refresh_Data_Day
108       (
109           ERRBUF        => ERRBUF
110         , RETCODE       => RETCODE
111         , p_date        => l_collection_date_loop
112         , p_delete_flag => 'Y'
113         , p_degree      => p_degree
114         , p_debug_mode  => p_debug_mode
115         , p_trace_mode  => p_trace_mode
116        );
117        l_collection_date_loop := l_collection_date_loop+1;
118     END LOOP;
119 
120       IF l_collection_date_start > l_collect_for_date THEN
121       l_collection_date_start := l_collect_for_date;
122       Refresh_Data_Day
123       (
124           ERRBUF        => ERRBUF
125         , RETCODE       => RETCODE
126         , p_date        => l_collection_date_start
127         , p_delete_flag => 'Y'
128         , p_degree      => p_degree
129         , p_debug_mode  => p_debug_mode
130         , p_trace_mode  => p_trace_mode
131        );
132      END IF;
133 
134 
135     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Analyze table bil_do_l1_ld_oppty_dly', p_debug=> p_debug_mode);
136 
137     DBMS_STATS.gather_table_stats(ownname=>'BIL', tabName=>'BIL_DO_L1_LD_OPPTY_DLY', cascade=>TRUE,
138                                   degree=>l_Degree, estimate_percent=>99, granularity=>'GLOBAL');
139 
140 
141 
142     IF G_Trace = 'Y' THEN
143          EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=FALSE';
144     END IF;
145 
146     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Alter bil_do_l1_ld_oppty_dly table to noparallel', p_debug=>p_debug_mode);
147     Reset_Table_Usages;
148 
149     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'End of Refresh Data  for Lead to Opportunity Bin', p_debug=>p_debug_mode);
150 
151 
152 
153     END Refresh_Data;
154 
155 
156   /*******Initial load procedure: loads data for a date range
157     ERRBUFF:            error message returned by the proc
158     RETCODE:            completion status of the procedure
159     p_start_date:       start date of the range
160     p_end_date:         end date of the range
161     p_degree:           parallel degree
162     p_truncate_flag:    truncate flag (yes or no)
163     p_debug_mode:       debug mode (yes or no)
164     p_trace_mode:       trace mode (yes or no)
165   *******/
166   PROCEDURE Initial_Load
167     (       ERRBUF          OUT VARCHAR2
168            ,RETCODE         OUT VARCHAR2
169            ,p_start_date    IN  VARCHAR2
170            ,p_end_date      IN  VARCHAR2
171            ,p_degree        IN  VARCHAR2 DEFAULT '4'
172            ,p_truncate_flag IN  VARCHAR2 DEFAULT 'N'
173            ,p_debug_mode    IN  VARCHAR2 DEFAULT 'N'
174            ,p_trace_mode    IN  VARCHAR2 DEFAULT 'N'
175     )   IS
176 
177     -- Local variables
178     l_date_fmt  VARCHAR2(30) := 'YYYY-MM-DD HH24:MI:SS';
179     l_start_date DATE := TO_DATE(p_start_date, l_date_fmt);
180     l_end_date   DATE := TO_DATE(p_end_date, l_date_fmt);
181     l_degree           NUMBER   := TO_NUMBER(p_degree); -- parallel degree
182 
183     BEGIN
184 
185       RETCODE := 0;
186 
187       Init_Globals;
188 
189       -- Validate input parameters
190       IF p_debug_mode = 'Y' THEN
191          G_Debug := 'Y';
192       END IF;
193 
194       IF p_trace_mode = 'Y' THEN
195          G_Trace := 'Y';
196          EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
197       END IF;
198 
199 
200     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Parameters for Refesh_Data - Start Date : ' || p_start_date
201     || ' End date: ' || p_end_date
202     || ' Parallel Degree: ' || p_degree
203     || ' Truncate flag: ' || p_truncate_flag
204     || ' Debug: ' || p_debug_mode
205     || ' Trace: ' || p_trace_mode
206     , p_debug =>p_debug_mode);
207 
208 
209     Set_table_Usages;
210 
211     IF p_truncate_flag = 'Y' THEN
212         EXECUTE IMMEDIATE 'TRUNCATE TABLE BIL.BIL_DO_L1_LD_OPPTY_DLY';
213         WHILE l_start_date <= l_end_date LOOP
214             Refresh_Data_Day
215             (
216                 ERRBUF        => ERRBUF
217               , RETCODE       => RETCODE
218               , p_date        => l_start_date
219               , p_delete_flag => 'N'
220               , p_degree      => p_degree
221               , p_debug_mode  => p_debug_mode
222              , p_trace_mode  => p_trace_mode
223             );
224             l_start_date := l_start_date + 1;
225         END LOOP;
226 
227     ELSE
228         WHILE l_start_date <= l_end_date LOOP
229             Refresh_Data_Day
230             (
231                 ERRBUF        => ERRBUF
232               , RETCODE       => RETCODE
233               , p_date        => l_start_date
234               , p_delete_flag => 'Y'
235               , p_degree      => p_degree
236               , p_debug_mode  => p_debug_mode
237              , p_trace_mode  => p_trace_mode
238             );
239             l_start_date := l_start_date + 1;
240         END LOOP;
241     END IF;
242 
243     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Analyze table bil_do_l1_ld_oppty_dly', p_debug=> p_debug_mode);
244 
245     DBMS_STATS.gather_table_stats(ownname=>'BIL', tabName=>'BIL_DO_L1_LD_OPPTY_DLY', cascade=>TRUE,
246                                   degree=>l_Degree, estimate_percent=>99, granularity=>'GLOBAL');
247 
248     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'End of initial load of bil_do_l1_ld_oppty_dly', p_debug=> p_debug_mode);
249 
250     IF G_Trace = 'Y' THEN
251          EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=FALSE';
252     END IF;
253 
254     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Alter bil_do_l1_ld_oppty_dly table to noparallel', p_debug=>p_debug_mode);
255     Reset_Table_Usages;
256 
257     EXCEPTION
258        WHEN OTHERS THEN
259           ERRBUF := ERRBUF||'Error in Initial_Load: '||to_char(sqlcode)||sqlerrm;
260           RETCODE := '2';
261           BIL_DO_UTIL_PKG.Write_Log(
262               p_msg => 'Error in Initial_Load: '||to_char(sqlcode)||sqlerrm
263             , p_force => 'Y');
264           ROLLBACK;
265           Reset_table_Usages;
266 
267 END INITIAL_LOAD;
268 
269 
270 
271 
272   /*******
273   Delete data for the specified date
274   ERRBUFF:            error message returned by the proc
275   RETCODE:            completion status of the procedure
276   p_date:             date for which the data will be deleted
277   *******/
278   PROCEDURE Delete_Data (
279               ERRBUF  IN OUT VARCHAR2
280              ,RETCODE IN OUT VARCHAR2
281              ,p_date  IN DATE
282             ) IS
283 
284   BEGIN
285 
286     DELETE FROM bil_do_l1_ld_oppty_dly
287       WHERE collection_date = p_date;
288 
289     COMMIT;
290 
291     EXCEPTION
292 	 WHEN NO_DATA_FOUND THEN
293 	  NULL;
294       WHEN OTHERS THEN
295         ERRBUF := ERRBUF ||' Delete_Data:'||sqlcode||' '|| sqlerrm;
296         RETCODE := '1';
297   END Delete_Data;
298 
299 
300 /******* Initialize Global variables for WHO variables and Concurrent program
301 *******/
302  PROCEDURE Init_Globals IS
303 
304  BEGIN
305      G_request_id    := FND_GLOBAL.CONC_REQUEST_ID();
306      G_appl_id       := FND_GLOBAL.PROG_APPL_ID();
307      G_program_id    := FND_GLOBAL.CONC_PROGRAM_ID();
308      G_user_id       := FND_GLOBAL.USER_ID();
309      G_login_id      := FND_GLOBAL.CONC_LOGIN_ID();
310  END Init_Globals;
311 
312 
313 /******* Insert data for the specified day
314     ERRBUFF:    error message returned by the proc
315     RETCODE:    completion status of the procedure
316     p_date:     date for which data will be inserted
317     p_degree:   parallel degree
318 *******/
319 PROCEDURE Insert_Data (
320               ERRBUF            IN OUT VARCHAR2
321              ,RETCODE           IN OUT VARCHAR2
322              ,p_date            IN DATE
323              ,p_degree          IN NUMBER
324             ) IS
325 
326  l_sysdate DATE := SYSDATE; -- to be used in insert
327  l_stime   DATE := SYSDATE; -- time when insert started, to be used by Write_log
328  l_insert_stmnt  VARCHAR2(20000);
329  l_select_stmnt1 VARCHAR2(20000);
330  l_select_stmnt2 VARCHAR2(20000);
331  l_quote VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(39); -- single quote
332  l_row_count  NUMBER;
333 
334 
335   BEGIN
336 
337   l_insert_stmnt := 'INSERT INTO /*+ APPEND PARALLEL(bld, ' || p_degree || ') */ bil_do_l1_ld_oppty_dly bld';
338   l_insert_stmnt := l_insert_stmnt || '(';
339   l_insert_stmnt := l_insert_stmnt || ' collection_date';
340   l_insert_stmnt := l_insert_stmnt || ', sales_group_id';
341   l_insert_stmnt := l_insert_stmnt || ', total_leads_all';
345   l_insert_stmnt := l_insert_stmnt || ', touched_leads_day';
342   l_insert_stmnt := l_insert_stmnt || ', open_leads_all';
343   l_insert_stmnt := l_insert_stmnt || ', open_leads_day';
344   l_insert_stmnt := l_insert_stmnt || ', touched_leads_all';
346   l_insert_stmnt := l_insert_stmnt || ', converted_leads_all';
347   l_insert_stmnt := l_insert_stmnt || ', converted_leads_day';
348   l_insert_stmnt := l_insert_stmnt || ', creation_date';
349   l_insert_stmnt := l_insert_stmnt || ', created_by';
350   l_insert_stmnt := l_insert_stmnt || ', last_update_date';
351   l_insert_stmnt := l_insert_stmnt || ', last_updated_by';
352   l_insert_stmnt := l_insert_stmnt || ', last_update_login';
353   l_insert_stmnt := l_insert_stmnt || ', request_id';
354   l_insert_stmnt := l_insert_stmnt || ', program_application_id';
355   l_insert_stmnt := l_insert_stmnt || ', program_id';
356   l_insert_stmnt := l_insert_stmnt || ', program_update_date';
357   l_insert_stmnt := l_insert_stmnt || ')';
358   l_select_stmnt1  := 'SELECT /*+ PARALLEL(v, ' || p_degree || ') */';
359   l_select_stmnt1  := l_select_stmnt1 || ':p_date';
360   l_select_stmnt1  := l_select_stmnt1 ||  '     , nvl(v.sales_group_id, -999) sales_group_id';
361   l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.total_leads_all) total_leads_all';
362   l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.open_leads_all) open_leads_all';
363   l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.open_leads_day) open_leads_day';
364   l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.touched_leads_all) touched_leads_all';
365   l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.touched_leads_day) touched_leads_day';
366   l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.converted_leads_all) converted_leads_all';
367   l_select_stmnt1  := l_select_stmnt1 ||  '     , SUM(v.converted_leads_day) converted_leads_day';
368   l_select_stmnt1  := l_select_stmnt1 ||  '     , SYSDATE';
369   l_select_stmnt1  := l_select_stmnt1 ||  '     ,' || G_user_id;
370   l_select_stmnt1  := l_select_stmnt1 ||  '     , SYSDATE';
371   l_select_stmnt1  := l_select_stmnt1 ||  '     ,' || G_user_id;
372   l_select_stmnt1  := l_select_stmnt1 ||  '     ,' || G_login_id;
373   l_select_stmnt1  := l_select_stmnt1 ||  '     ,' || G_request_id;
374   l_select_stmnt1  := l_select_stmnt1 ||  '     ,' || G_appl_id;
375   l_select_stmnt1  := l_select_stmnt1 ||  '     ,' || G_program_id;
376   l_select_stmnt1  := l_select_stmnt1 ||  '     , SYSDATE';
377   l_select_stmnt2  := '   FROM ';
378   l_select_stmnt2  := l_select_stmnt2 ||  '   (SELECT /*+ PARALLEL(sl, ' || p_degree ||') */';
379   l_select_stmnt2  := l_select_stmnt2 ||  '     grp.sales_group_id sales_group_id ';
380   l_select_stmnt2  := l_select_stmnt2 ||  '     , COUNT(sl.sales_lead_id) total_leads_all ';
381   l_select_stmnt2  := l_select_stmnt2 ||  '    , DECODE(st.opp_open_status_flag, ' || l_quote ||'Y' || l_quote || ', COUNT(sl.sales_lead_id), 0) open_leads_all ';
382   l_select_stmnt2  := l_select_stmnt2 ||  '   , DECODE(' || ':p_date' || ', TRUNC(sl.creation_date) , DECODE(st.opp_open_status_flag, ';
383   l_select_stmnt2  := l_select_stmnt2 || l_quote || 'Y' || l_quote || ' , COUNT(sl.sales_lead_id), 0) ,0) open_leads_day ';
384   l_select_stmnt2  := l_select_stmnt2 || '    , DECODE(st.opp_open_status_flag, '|| l_quote ||'Y' || l_quote ;
385   l_select_stmnt2  := l_select_stmnt2 || '    , DECODE(sl.creation_date, sl.last_update_date, 0, COUNT(sl.sales_lead_id))';
386   l_select_stmnt2  := l_select_stmnt2 || '       , 0) touched_leads_all ';
387   l_select_stmnt2  := l_select_stmnt2 || '     , DECODE(' || ':p_date' || ', TRUNC(sl.last_update_date)';
388   l_select_stmnt2  := l_select_stmnt2 || '       , DECODE(st.opp_open_status_flag, ' || l_quote ||'Y' || l_quote;
389   l_select_stmnt2  := l_select_stmnt2 || '         , DECODE(sl.creation_date, sl.last_update_date, 0, COUNT(sl.sales_lead_id))';
390   l_select_stmnt2  := l_select_stmnt2 || '       , 0) ,0) touched_leads_day ';
391   l_select_stmnt2  := l_select_stmnt2 || '     , 0 converted_leads_all ';
392   l_select_stmnt2  := l_select_stmnt2 || '     , 0 converted_leads_day ';
393   l_select_stmnt2  := l_select_stmnt2 || '   FROM ';
394   l_select_stmnt2  := l_select_stmnt2 || '       as_sales_leads sl ';
395   l_select_stmnt2  := l_select_stmnt2 || '     , as_statuses_b  st ';
396   l_select_stmnt2  := l_select_stmnt2 || '     , bil_do_l1_base_grp_temp grp ';
397   l_select_stmnt2  := l_select_stmnt2 || '   WHERE ';
398   l_select_stmnt2  := l_select_stmnt2 || '        sl.status_code = st.status_code ';
399   l_select_stmnt2  := l_select_stmnt2 || '    AND st.lead_flag = ' || l_quote ||'Y' || l_quote;
400   l_select_stmnt2  := l_select_stmnt2 || '    AND st.enabled_flag = ' || l_quote ||'Y' || l_quote;
401   l_select_stmnt2  := l_select_stmnt2 || '    AND NVL(sl.deleted_flag, '|| l_quote || 'N' || l_quote || ') <> ' || l_quote ||'Y' || l_quote;
402   l_select_stmnt2  := l_select_stmnt2 || '    AND grp.child_sales_group_id = sl.assign_sales_group_id ';
403   l_select_stmnt2  := l_select_stmnt2 || '   GROUP BY ';
404   l_select_stmnt2  := l_select_stmnt2 || '      grp.sales_group_id ';
405   l_select_stmnt2  := l_select_stmnt2 || '    , st.opp_open_status_flag ';
406   l_select_stmnt2  := l_select_stmnt2 || '    , sl.creation_date ';
407   l_select_stmnt2  := l_select_stmnt2 || '    , sl.last_update_date ';
408   l_select_stmnt2  := l_select_stmnt2 || ' UNION ALL ';
409   l_select_stmnt2  := l_select_stmnt2 || '      SELECT /*+ PARALLEL(sl, ' || p_degree || ') */';
410   l_select_stmnt2  := l_select_stmnt2 || '         grp.sales_group_id sales_group_id ';
411   l_select_stmnt2  := l_select_stmnt2 || '       , 0 total_leads_all ';
412   l_select_stmnt2  := l_select_stmnt2 || '       , 0 open_leads_all ';
413   l_select_stmnt2  := l_select_stmnt2 || '       , 0 open_leads_day ';
414   l_select_stmnt2  := l_select_stmnt2 || '       , 0 touched_leads_all ';
415   l_select_stmnt2  := l_select_stmnt2 || '       , 0 touched_leads_day ';
416   l_select_stmnt2  := l_select_stmnt2 || '       , 0 converted_leads_all ';
417   l_select_stmnt2  := l_select_stmnt2 || '       , DECODE(' || ':p_date' || ', TRUNC(slop.creation_date) ';
418   l_select_stmnt2  := l_select_stmnt2 || '          , COUNT(DISTINCT sl.sales_lead_id), 0) converted_leads_day ';
422   l_select_stmnt2  := l_select_stmnt2 || '     , bil_do_l1_base_grp_temp grp ';
419   l_select_stmnt2  := l_select_stmnt2 || '     FROM ';
420   l_select_stmnt2  := l_select_stmnt2 || '        as_sales_leads sl ';
421   l_select_stmnt2  := l_select_stmnt2 || '      , as_sales_lead_opportunity slop ';
423   l_select_stmnt2  := l_select_stmnt2 || '     WHERE ';
424   l_select_stmnt2  := l_select_stmnt2 || '        NVL(sl.deleted_flag, ' || l_quote ||'N' || l_quote ||') <> ' || l_quote ||'Y' || l_quote;
425   l_select_stmnt2  := l_select_stmnt2 || '    AND sl.sales_lead_id = slop.sales_lead_id ';
426   l_select_stmnt2  := l_select_stmnt2 || '    AND grp.child_sales_group_id = sl.assign_sales_group_id';
427   l_select_stmnt2  := l_select_stmnt2 || '   AND NOT EXISTS (SELECT sales_lead_id FROM as_sales_lead_opportunity slo';
428   l_select_stmnt2  := l_select_stmnt2 || '                     WHERE slo.sales_lead_id = slop.sales_lead_id';
429   l_select_stmnt2  := l_select_stmnt2 || '                     AND slo.creation_date < :p_date)';
430   l_select_stmnt2  := l_select_stmnt2 || '   GROUP BY grp.sales_group_id';
431   l_select_stmnt2  := l_select_stmnt2 || '            , TRUNC(slop.creation_date)';
432 
433   l_select_stmnt2  := l_select_stmnt2 || ' UNION ALL ';
434   l_select_stmnt2  := l_select_stmnt2 || '      SELECT /*+ PARALLEL(sl, ' || p_degree || ') */';
435   l_select_stmnt2  := l_select_stmnt2 || '         grp.sales_group_id sales_group_id ';
436   l_select_stmnt2  := l_select_stmnt2 || '       , 0 total_leads_all ';
437   l_select_stmnt2  := l_select_stmnt2 || '       , 0 open_leads_all ';
438   l_select_stmnt2  := l_select_stmnt2 || '       , 0 open_leads_day ';
439   l_select_stmnt2  := l_select_stmnt2 || '       , 0 touched_leads_all ';
440   l_select_stmnt2  := l_select_stmnt2 || '       , 0 touched_leads_day ';
441   l_select_stmnt2  := l_select_stmnt2 || '       , COUNT(DISTINCT sl.sales_lead_id) converted_leads_all ';
442   l_select_stmnt2  := l_select_stmnt2 || '       , 0 converted_leads_day ';
443   l_select_stmnt2  := l_select_stmnt2 || '     FROM ';
444   l_select_stmnt2  := l_select_stmnt2 || '        as_sales_leads sl ';
445   l_select_stmnt2  := l_select_stmnt2 || '      , as_sales_lead_opportunity slop ';
446   l_select_stmnt2  := l_select_stmnt2 || '     , bil_do_l1_base_grp_temp grp ';
447   l_select_stmnt2  := l_select_stmnt2 || '     WHERE ';
448   l_select_stmnt2  := l_select_stmnt2 || '        NVL(sl.deleted_flag, ' || l_quote ||'N' || l_quote ||') <> ' || l_quote ||'Y' || l_quote;
449   l_select_stmnt2  := l_select_stmnt2 || '    AND sl.sales_lead_id = slop.sales_lead_id ';
450   l_select_stmnt2  := l_select_stmnt2 || '    AND grp.child_sales_group_id = sl.assign_sales_group_id';
451   l_select_stmnt2  := l_select_stmnt2 || '   GROUP BY grp.sales_group_id';
452   l_select_stmnt2  := l_select_stmnt2 || '            , TRUNC(slop.creation_date)';
453   l_select_stmnt2  := l_select_stmnt2 || '   ) V ';
454   l_select_stmnt2  := l_select_stmnt2 || '   GROUP BY v.sales_group_id ';
455 
456 /*dbms_output.put_line(substr(l_insert_stmnt,1,150));
457 dbms_output.put_line(substr(l_insert_stmnt,151,150));
458 dbms_output.put_line(substr(l_insert_stmnt,301,150));
459 dbms_output.put_line(substr(l_insert_stmnt,451,150));
460 dbms_output.put_line(substr(l_insert_stmnt,601,150));
461 dbms_output.put_line(substr(l_insert_stmnt,751,150));
462 dbms_output.put_line(substr(l_insert_stmnt,901,150));*/
463 
464 
465 /*dbms_output.put_line(substr(l_select_stmnt1,1,150));
466 dbms_output.put_line(substr(l_select_stmnt1,151,150));
467 dbms_output.put_line(substr(l_select_stmnt1,301,150));
468 dbms_output.put_line(substr(l_select_stmnt1,451,150));
469 dbms_output.put_line(substr(l_select_stmnt1,601,150));
470 dbms_output.put_line(substr(l_select_stmnt1,751,150));
471 dbms_output.put_line(substr(l_select_stmnt1,901,150)); */
472 
473 /* dbms_output.put_line(substr(l_select_stmnt2,1,150));
474 dbms_output.put_line(substr(l_select_stmnt2,151,150));
475 dbms_output.put_line(substr(l_select_stmnt2,301,150));
476 dbms_output.put_line(substr(l_select_stmnt2,451,150));
477 dbms_output.put_line(substr(l_select_stmnt2,601,150));
478 dbms_output.put_line(substr(l_select_stmnt2,751,150));
479 dbms_output.put_line(substr(l_select_stmnt2,901,150));
480 dbms_output.put_line(substr(l_select_stmnt2,1051,150));
481 dbms_output.put_line(substr(l_select_stmnt2,1201,150));
482 dbms_output.put_line(substr(l_select_stmnt2,1351,150));
483 dbms_output.put_line(substr(l_select_stmnt2,1501,150));
484 dbms_output.put_line(substr(l_select_stmnt2,1651,150));
485 dbms_output.put_line(substr(l_select_stmnt2,1801,150));
486 dbms_output.put_line(substr(l_select_stmnt2,1951,150));
487 dbms_output.put_line(substr(l_select_stmnt2,2111,150));
488 dbms_output.put_line(substr(l_select_stmnt2,2261,150));
489 dbms_output.put_line(substr(l_select_stmnt2,2311,150));
490 dbms_output.put_line(substr(l_select_stmnt2,2461,150)); */
491 
492   EXECUTE IMMEDIATE l_insert_stmnt || l_select_stmnt1 || l_select_stmnt2
493   USING
494     p_date
495   , p_date
496   , p_date
497   , p_date
498   , p_date;
499 
500 
501  COMMIT;
502 
503  l_row_count := SQL%ROWCOUNT;
504 
505  BIL_DO_UTIL_PKG.Write_Log(p_msg=>'     Rows Inserted: '|| l_row_count,p_stime=>l_stime,p_etime=>SYSDATE, p_debug=>G_Debug);
506 
507  IF l_row_count = 0 THEN
508         BIL_DO_UTIL_PKG.Write_Log(p_msg=>'     No rows Inserted. ', p_force=>'Y');
509  END IF;
510 
511 
512 
513  EXCEPTION
514       WHEN OTHERS THEN
515         ERRBUF := ERRBUF ||' Insert_Data: '||sqlcode||' '|| sqlerrm;
516         RETCODE := '2';
517         BIL_DO_UTIL_PKG.Write_Log(p_msg=>' Insert_Data: '||sqlcode||' '|| sqlerrm
518            , p_force=> 'Y');
519         ROLLBACK;
520         --DBMS_OUTPUT.PUT_LINE('Error in Insert_Data: ' ||sqlcode||' '|| sqlerrm);
521 
522 END Insert_Data;
523 
524 
525  /******* deletes and then inserts specified day's data
526     ERRBUFF:    error message returned by the proc
527     RETCODE:    completion status of the procedure
531     p_trace_mode:    trace mode (yes or no)
528     p_degree:   parallel degree
529     p_date  :   date for which data is collected
530     p_debug_mode:    debug mode (yes or no)
532   *******/
533 
534   PROCEDURE Refresh_Data_Day
535     (
536         ERRBUF        IN OUT VARCHAR2
537       , RETCODE       IN OUT VARCHAR2
538 	  , p_date        IN  DATE
539       , p_delete_flag IN  VARCHAR2 DEFAULT 'Y'
540       , p_degree      IN  VARCHAR2 DEFAULT '4'
541       , p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
542       , p_trace_mode  IN  VARCHAR2 DEFAULT 'N'
543     ) IS
544 
545     -- Local variables
546     l_collect_for_date DATE     := p_date; -- date for which data is collected
547     l_degree           NUMBER   := TO_NUMBER(p_degree); -- parallel degree
548 
549 
550     BEGIN
551 
552      IF p_delete_flag = 'Y' THEN
553      BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Delete Data for collection date: ' || TO_CHAR(l_collect_for_date, 'DD-MON-YYYY'), p_debug=>p_debug_mode);
554         Delete_Data (
555            ERRBUF       => ERRBUF
556           ,RETCODE      => RETCODE
557           ,p_date => l_collect_for_date
558          );
559      END IF;
560 
561 
562      BIL_DO_UTIL_PKG.Write_Log('Insert Data for collection date: ' || TO_CHAR(l_collect_for_date, 'DD-MON-YYYY'), p_debug=>p_debug_mode);
563 
564      Insert_Data (
565            ERRBUF           => ERRBUF
566           ,RETCODE          => RETCODE
567           ,p_date           => l_collect_for_date
568           ,p_degree         => l_degree
569          );
570 
571      EXCEPTION
572        WHEN OTHERS THEN
573           ERRBUF := ERRBUF||'Error in Refresh_Data_Day:'||to_char(sqlcode)||sqlerrm;
574           RETCODE := '2';
575           BIL_DO_UTIL_PKG.Write_Log(
576               p_msg => 'Error in Refresh_Data_Day for collection date: ' || TO_CHAR(l_collect_for_date, 'DD-MON-YYYY') ||to_char(sqlcode)||sqlerrm
577             , p_force => 'Y');
578           ROLLBACK;
579 
580 
581 
582   END Refresh_Data_Day;
583 
584 
585 
586 
587 
588   /*******
589     Alter tables to logging
590   *******/
591   PROCEDURE Reset_Table_Usages IS
592   BEGIN
593 
594 
595     EXECUTE IMMEDIATE 'ALTER TABLE bil.bil_do_l1_ld_oppty_dly LOGGING';
596 
597     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'BIL Table bil_do_l1_ld_oppty_dly altered to logging', p_debug=>G_Debug);
598   END Reset_Table_Usages;
599 
600   /*******
601     Alter all the tables used to nologging, drop the indexes
602   *******/
603   PROCEDURE Set_Table_Usages IS
604   BEGIN
605 
606     EXECUTE IMMEDIATE 'ALTER TABLE bil.bil_do_l1_ld_oppty_dly NOLOGGING';
607 
608     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'BIL Table bil_do_l1_ld_oppty_dly altered to nologging', p_debug=>G_Debug);
609 
610 
611   END Set_Table_Usages;
612 
613 END BIL_DO_L1_LD_OPPTY_DLY_PKG;