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_OPPTY_SUMRY_PKG

Source


1 PACKAGE BODY BIL_DO_L1_OPPTY_SUMRY_PKG AS
2 /* $Header: bilopl1b.pls 115.18 2002/01/29 23:03:50 pkm ship      $ */
3      -- Global Variables and Constants
4      -- G_Debug will be modified to TRUE programatically.
5      -- when the parameter passed for P_debug is Y
6      -- Once, this is set to TRUE, then the log file will be generated.
7      -- Otherwise, log file will not be generated. Only the output file
8      -- will be generated.
9      G_Debug    VARCHAR2(1) := 'N';
10      G_Trace    BOOLEAN := FALSE;
11      G_Degree   NUMBER  := 4;
12      G_Truncate VARCHAR2(1) := 'N';
13    -- Global variables for WHO variables and Concurrent program
14      G_request_id    NUMBER;
15      G_appl_id       NUMBER;
16      G_program_id    NUMBER;
17      G_user_id       NUMBER;
18      G_login_id      NUMBER;
19 
20 PROCEDURE Delete_table( ERRBUF           IN OUT VARCHAR2
21                        ,RETCODE          IN OUT VARCHAR2
22                        ,p_date           IN     DATE);
23 
24 PROCEDURE Truncate_Table;
25 
26 PROCEDURE Set_Table_Usages;
27 
28 PROCEDURE Init_Globals;
29 
30 PROCEDURE Refresh_Data_Day( ERRBUF        OUT VARCHAR2
31                            , RETCODE       OUT VARCHAR2
32                            , p_collection_date IN VARCHAR2
33                            , p_degree      IN  NUMBER   DEFAULT 4
34                            , p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
35                            );
36 
37 PROCEDURE Insert_Data( ERRBUF           IN OUT VARCHAR2
38                       ,RETCODE          IN OUT VARCHAR2
39                       ,p_degree         IN     NUMBER
40                       ,p_collect_date   IN     DATE);
41 
42 PROCEDURE Reset_Table_Usages;
43 PROCEDURE Refresh_Date_Range(ERRBUF      OUT  VARCHAR2
44                             ,RETCODE      OUT  VARCHAR2
45                             ,p_start_date IN DATE
46                             ,p_end_date IN DATE
47                             ,p_degree      IN  NUMBER   DEFAULT 4
48                             ,p_truncate_flag IN VARCHAR2 DEFAULT 'N'
49                             ,p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
50                             ,p_trace_mode  IN  VARCHAR2 DEFAULT 'N'
51                            ) ;
52 
53 PROCEDURE Refresh_Data( ERRBUF       OUT VARCHAR2
54                       ,RETCODE       OUT VARCHAR2
55                       ,p_degree      IN  NUMBER   DEFAULT 4
56                       ,p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
57                       ,p_trace_mode  IN  VARCHAR2 DEFAULT 'N'
58                       ) is
59 
60   l_collection_date_start date;
61   l_collection_date_end date;
62   l_day_count number;
63   l_rec_count number;
64 BEGIN
65   -- Start and end will be yesterday's date
66   l_collection_date_end := trunc(sysdate-1);
67   l_collection_date_start := l_collection_date_end;
68   -- Look for first missing collection date in table;
69   select max(collection_date)+1
70     into l_collection_date_start
71     from BIL_DO_L1_OPPTY_SUMRY;
72   -- Just do for sysdate-1 if nothing exists or data already
73   -- exists for sysdate
74   if l_collection_date_start is null
75       or l_collection_date_start > l_collection_date_end then
76     l_collection_date_start := l_collection_date_end;
77   end if;
78     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Parameters for Refresh_Data - Debug: ' || p_debug_mode
79           || '  Trace: ' || p_trace_mode
80           || '  Parallel Degree: ' || TO_CHAR(p_Degree),p_debug => p_Debug_mode);
81 
82       Refresh_Date_Range( ERRBUF          => ERRBUF
83                          ,RETCODE         => RETCODE
84                          ,p_start_date    => l_collection_date_start
85                          ,p_end_date    => l_collection_date_end
86                          ,p_degree        => p_degree
87                          ,p_truncate_flag => 'N'
88                          ,p_debug_mode    => p_debug_mode
89                          ,p_trace_mode    => p_trace_mode);
90  EXCEPTION
91     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
92           ERRBUF := ERRBUF||'Error in Refresh_Data:'||to_char(sqlcode)||sqlerrm;
93           RETCODE := '2' ;
94           BIL_DO_UTIL_PKG.Write_Log(
95               p_msg => 'Error in Refresh_Data:'||to_char(sqlcode)||sqlerrm
96             , p_force => 'Y',p_debug =>G_Debug);
97           ROLLBACK;
98           Reset_Table_Usages;
99 
100        WHEN OTHERS THEN
101           ERRBUF := ERRBUF||'Error in Refresh_Data:'||to_char(sqlcode)||sqlerrm;
102           RETCODE := '2';
103           BIL_DO_UTIL_PKG.Write_Log(
104               p_msg => 'Error in Refresh_Data:'||to_char(sqlcode)||sqlerrm
105             , p_force => 'Y',p_debug =>G_Debug);
106           ROLLBACK;
107           Reset_table_Usages;
108 END Refresh_Data;
109 
110 
111 PROCEDURE Refresh_Data_Range(ERRBUF      OUT  VARCHAR2
112                             ,RETCODE      OUT  VARCHAR2
113                             ,p_start_date IN VARCHAR2
114                             ,p_end_date IN VARCHAR2
115                             ,p_degree      IN  NUMBER   DEFAULT 4
116                             ,p_truncate_flag IN VARCHAR2 DEFAULT 'N'
117                             ,p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
118                             ,p_trace_mode  IN  VARCHAR2 DEFAULT 'N'
119                            ) is
120   l_collection_date_start date;
121   l_date_format VARCHAR2(50) := 'YYYY-MM-DD HH24:MI:SS';
122   l_collection_date_end date;
123 BEGIN
124   l_collection_date_start := to_date(p_start_date, l_date_format);
125   l_collection_date_end := to_date(p_end_date, l_date_format);
126 
127   Refresh_Date_Range( ERRBUF          => ERRBUF
128                      ,RETCODE         => RETCODE
129                      ,p_start_date    => l_collection_date_start
130                      ,p_end_date      => l_collection_date_end
131                      ,p_degree        => p_degree
132                      ,p_truncate_flag => p_truncate_flag
133                      ,p_debug_mode    => p_debug_mode
134                      ,p_trace_mode    => p_trace_mode);
135 
136 END Refresh_data_Range;
137 
138 PROCEDURE Refresh_Date_Range(ERRBUF      OUT  VARCHAR2
139                             ,RETCODE      OUT  VARCHAR2
140                             ,p_start_date IN DATE
141                             ,p_end_date IN DATE
142                             ,p_degree      IN  NUMBER   DEFAULT 4
143                             ,p_truncate_flag IN VARCHAR2 DEFAULT 'N'
144                             ,p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
145                             ,p_trace_mode  IN  VARCHAR2 DEFAULT 'N'
146                            ) is
147   l_collection_date_start date;
148   l_collection_date_end date;
149   l_day_count number;
150   l_rec_count0 number;
151   l_rec_count number;
152 
153 BEGIN
154   l_collection_date_start := p_start_date;
155   l_collection_date_end := p_end_date;
156   l_day_count := l_collection_date_end-l_collection_date_start+1;
157 
158   /*Set table to nologging*/
159   Set_Table_Usages;
160 
161   IF p_debug_mode = 'Y' THEN
162      G_Debug := 'Y';
163   END IF;
164 
165   IF (p_truncate_flag = 'Y') THEN
166     G_Truncate := 'Y';
167     Truncate_Table;
168   END IF;
169   --Initialize the global variables.
170       Init_Globals;
171       RETCODE := 0;
172 --Validate the input Parameters.
173       IF p_debug_mode = 'Y' THEN
174          G_Debug := 'Y';
175       END IF;
176 
177       IF p_trace_mode = 'Y' THEN
178          G_Trace := TRUE;
179          EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
180       END IF;
181 
182       IF NVL(p_degree,0) > 0 THEN
183         G_Degree := p_degree;
184       END IF;
185 
186   if l_day_count > 0 then
187     for i in 1..l_day_count LOOP
188        Refresh_Data_Day( ERRBUF            => ERRBUF
189                         ,RETCODE           => RETCODE
190                         ,p_collection_date => to_char(l_collection_date_start)
191                         ,p_degree          => G_Degree
192                         ,p_debug_mode      => G_Debug);
193       l_collection_date_start := l_collection_date_start + 1;
194     end loop;
195   end if;
196 
197  /*Analyze the table after insertion*/
198  BIL_DO_UTIL_PKG.Write_Log('Analyze table BIL_DO_L1_OPPTY_SUMRY',p_debug => p_debug_mode);
199  DBMS_STATS.gather_table_stats(ownname=>'BIL', tabName=>'BIL_DO_L1_OPPTY_SUMRY', cascade=>TRUE,
200                                   degree=>G_Degree, estimate_percent=>99, granularity=>'GLOBAL');
201 
202 
203  IF G_Trace THEN
204     EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=FALSE';
205  END IF;
206  Reset_Table_Usages;
207  BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Alter bil_do_l1_OPPTY_SUMRY table to noparallel', p_debug=>p_debug_mode);
208 
209  BIL_DO_UTIL_PKG.Write_Log(p_msg=>'End of Refresh_Date_Range  for Opportunity to Quote Bin', p_debug=>p_debug_mode);
210 
211 
212 EXCEPTION
213     WHEN OTHERS THEN
214      ERRBUF := ERRBUF||'Error in Refresh_Date_Range:'||to_char(sqlcode)||sqlerrm;
215      RETCODE := '2';
216      BIL_DO_UTIL_PKG.Write_Log(
217            p_msg => 'Error in Refresh_Date_Range:'||to_char(sqlcode)||sqlerrm
218           , p_force => 'Y',p_debug =>G_Debug);
219      ROLLBACK;
220 END Refresh_date_Range;
221 
222 /*******************************************************************
223 *ERRBUFF:    error message returned by the proc
224 *RETCODE:    completion status of the procedure
225 *p_degree:   parallel degree
226 *p_debug:    debug mode (yes or no)
227 *p_trace:    trace mode (yes or no)
228 ********************************************************************/
229 PROCEDURE Refresh_Data_Day( ERRBUF        OUT VARCHAR2
230                            , RETCODE       OUT VARCHAR2
231                            , p_collection_date IN varchar2
232                            , p_degree      IN  NUMBER   DEFAULT 4
233                            , p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
234                            ) is
235 l_collection_date date;
236 BEGIN
237 l_collection_date := to_date(p_collection_date);
238 
239 /*Delete the existing records for the same collect_date from the table*/
240 IF (G_Truncate = 'N') THEN
241 BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Delete Data for collection date: ' || TO_CHAR(l_collection_date, 'DD-MON-YYYY'), p_debug=>p_debug_mode);
242     Delete_table( ERRBUF      => ERRBUF
243                  ,RETCODE     => RETCODE
244                  ,p_date      => l_collection_date);
245 END IF;
246 
247 BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Insert Data for collection date: ' || TO_CHAR(l_collection_date, 'DD-MON-YYYY'), p_debug=>p_debug_mode);
248  /*Calling the insert procedure for inserting data to the table.*/
249 Insert_Data( ERRBUF         => ERRBUF
250             ,RETCODE        => RETCODE
251             ,p_degree       => p_degree
252             ,p_collect_date => l_collection_date);
253 
254  EXCEPTION
255     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
256           ERRBUF := ERRBUF||'Error in Refresh_Data_Day:'||to_char(sqlcode)||sqlerrm;
257           RETCODE := '2' ;
258           BIL_DO_UTIL_PKG.Write_Log(
259               p_msg => 'Error in Refresh_Data:'||to_char(sqlcode)||sqlerrm
260             , p_force => 'Y',p_debug =>G_Debug);
261           ROLLBACK;
262           Reset_Table_Usages;
263 
264        WHEN OTHERS THEN
265           ERRBUF := ERRBUF||'Error in Refresh_Data_Day:'||to_char(sqlcode)||sqlerrm;
266           RETCODE := '2';
267           BIL_DO_UTIL_PKG.Write_Log(
268               p_msg => 'Error in Refresh_Data_Day:'||to_char(sqlcode)||sqlerrm
269             , p_force => 'Y',p_debug =>G_Debug);
270           ROLLBACK;
271           Reset_table_Usages;
272 END Refresh_Data_Day;
273 
274 /********************************************************************
275 *Initialize Global variables for WHO variables and Concurrent program
276 *********************************************************************/
277  PROCEDURE Init_Globals IS
278 
279  BEGIN
280      G_request_id    := FND_GLOBAL.CONC_REQUEST_ID();
281      G_appl_id       := FND_GLOBAL.PROG_APPL_ID();
282      G_program_id    := FND_GLOBAL.CONC_PROGRAM_ID();
283      G_user_id       := FND_GLOBAL.USER_ID();
284      G_login_id      := FND_GLOBAL.CONC_LOGIN_ID();
285  END Init_Globals;
286 
287 
288 /**********************************************
289 *Insert_Data
290 ***********************************************/
291 PROCEDURE Insert_Data( ERRBUF           IN OUT VARCHAR2
292                       ,RETCODE          IN OUT VARCHAR2
293                       ,p_degree         IN     NUMBER
294                       ,p_collect_date   IN     DATE) IS
295 
296 /*Strings for Dynamic sql*/
297 l_insert_string VARCHAR2(1000) := '';
298 l_select_string VARCHAR2(5000) := '';
299 l_quote VARCHAR2(1) :=  FND_GLOBAL.LOCAL_CHR(39); -- single quote
300 
301 /*Bind variables*/
302 l_collection_date DATE ;--:= trunc(sysdate-1);
303 l_period_type     VARCHAR2(50) := FND_PROFILE.VALUE('AS_FORECAST_CALENDAR');
304 l_credit_type     VARCHAR2(50) := FND_PROFILE.VALUE('ASF_DEFAULT_FORECAST_CREDIT_TYPE');
305 l_delete_flag     VARCHAR2(1)  := 'N';
306 l_enabled_flag    VARCHAR2(1)  := 'Y';
307 l_sysdate         DATE := sysdate;
308 
309 l_stime DATE := sysdate;
310 BEGIN
311  l_collection_date := p_collect_date;
312  l_insert_string := 'INSERT INTO BIL_DO_L1_OPPTY_SUMRY( COLLECTION_DATE';
313  l_insert_string :=  l_insert_string|| ', SALES_GROUP_ID, PERIOD_NAME, PERIOD_TYPE, WON_AMOUNT, OPEN_AMOUNT';
314  l_insert_string :=  l_insert_string|| ', WEIGHTED_OPEN_AMOUNT, FORECAST_AMOUNT, LAST_UPDATE_DATE, LAST_UPDATED_BY';
315  l_insert_string :=  l_insert_string|| ', CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID';
316  l_insert_string :=  l_insert_string||', PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ';
317 
318 l_select_string := '(SELECT /*+  use_hash(bdlbgt den) Parallel(den,'|| p_degree||')*/ :l_collection_date, bdlbgt.sales_group_id, pd.period_name, pd.period_type';
319 l_select_string := l_select_string||',sum(decode(nvl(den.WIN_LOSS_INDICATOR,'||l_quote||'N'||l_quote||')';
320 l_select_string := l_select_string||'||nvl(den.OPP_OPEN_STATUS_FLAG,'||l_quote||'Y'||l_quote||'),';
321 l_select_string := l_select_string||l_quote||'WN'||l_quote||',den.c1_WON_AMOUNT,0))';
322 l_select_string := l_select_string||',sum(decode(nvl(den.WIN_LOSS_INDICATOR,'||l_quote||'N'||l_quote||')';
323 
324 l_select_string := l_select_string||'||nvl(den.OPP_OPEN_STATUS_FLAG,'||l_quote||'Y'||l_quote||'),';
325 l_select_string := l_select_string||l_quote||'NY'||l_quote||',den.c1_SALES_CREDIT_AMOUNT, 0))';
326 
327 l_select_string := l_select_string||',sum(decode(nvl(den.WIN_LOSS_INDICATOR,'||l_quote||'N'||l_quote||')';
328 l_select_string := l_select_string||'||nvl(den.OPP_OPEN_STATUS_FLAG,'||l_quote||'Y'||l_quote||'),';
329 
330 l_select_string := l_select_string||l_quote||'NY'||l_quote||',den.c1_SALES_CREDIT_AMOUNT*den.WIN_PROBABILITY/100.00, 0))';
331 l_select_string := l_select_string||',null,:l_sysdate,:G_user_id,:l_sysdate,:G_user_id,:G_login_id,:G_request_id';
332 l_select_string := l_select_string||',:G_appl_id,:G_program_id,:l_sysdate';
333 
334 l_select_string := l_select_string||' FROM  as_sales_credits_denorm den, bil_do_l1_base_grp_temp bdlbgt,as_period_days pd';
335 
336 l_select_string := l_select_string||' WHERE pd.start_date <= den.decision_date  AND pd.end_date >= den.decision_date';
337 l_select_string := l_select_string||'   AND pd.period_set_name = :l_period_type AND den.sales_group_id = bdlbgt.child_sales_group_id';
338 
339 l_select_string := l_select_string||'   AND pd.period_day = :l_collection_date ';
340 
341 l_select_string := l_select_string||'   AND den.credit_type_id = :l_credit_type  AND den.OPP_DELETED_FLAG = :l_delete_flag';
342 l_select_string := l_select_string||'   AND den.status_code in (SELECT  STATUS_CODE FROM as_statuses_b ';
343 l_select_string := l_select_string||'       WHERE  enabled_flag = :l_enabled_flag and opp_flag = :l_enabled_flag) ';
344 l_select_string := l_select_string||'   AND den.sales_stage_id in (SELECT sales_stage_id FROM as_sales_stages_all_b ';
345 l_select_string := l_select_string||'       WHERE enabled_flag = :l_enabled_flag ';
346 l_select_string := l_select_string||'         AND sysdate between start_date_active and nvl(end_date_active,sysdate))';
347 l_select_string := l_select_string||'   AND den.interest_type_id in (SELECT interest_type_id FROM as_interest_types_b ';
348 l_select_string := l_select_string||'       WHERE enabled_flag = :l_enabled_flag AND expected_purchase_flag = :l_enabled_flag)';
349 l_select_string := l_select_string||' GROUP BY  bdlbgt.sales_group_id,pd.period_name,pd.period_type)';
350 
351  EXECUTE IMMEDIATE l_insert_string||l_select_string
352  USING
353      l_collection_date
354     ,l_sysdate
355     ,G_user_id
356     ,l_sysdate
357     ,G_user_id
358     ,G_login_id
359     ,G_request_id
360     ,G_appl_id
361     ,G_program_id
362     ,l_sysdate
363     ,l_period_type
364     ,l_collection_date
365     ,l_credit_type
366     ,l_delete_flag
367     ,l_enabled_flag
368     ,l_enabled_flag
369     ,l_enabled_flag
370     ,l_enabled_flag
371     ,l_enabled_flag;
372 
373 COMMIT;
374  IF (SQL%ROWCOUNT = 0) THEN
375       BIL_DO_UTIL_PKG.Write_Log(p_msg=>'No Rows are inserted from Insert_data',p_stime=>l_stime,p_etime=>SYSDATE, p_force=>'Y',p_debug=>G_Debug);
376       RETCODE := 1;
377  END IF;
378 
379 BIL_DO_UTIL_PKG.Write_Log(p_msg=>'     Rows Inserted:'||SQL%ROWCOUNT,p_stime=>l_stime,p_etime=>SYSDATE, p_debug=>G_Debug);
380 
381 EXCEPTION
382       WHEN OTHERS THEN
383         ERRBUF := ERRBUF ||' Insert_Data:'||sqlcode||' '|| sqlerrm;
384          RETCODE := '2';
385       BIL_DO_UTIL_PKG.Write_Log(p_msg=>' Insert_Data:'||sqlcode||' '|| sqlerrm
386            , p_force=> 'Y',p_debug=>G_Debug);
387         Reset_Table_Usages;
388 end Insert_Data;
389 
390 /*************************************************************
391 *Delete_table is used to Delete existing records
392 *from BIL Table BIL_DO_L1_OPPTY_SUMRY for the collection_date.
393 **************************************************************/
394 PROCEDURE Delete_table( ERRBUF           IN OUT VARCHAR2
395                        ,RETCODE          IN OUT VARCHAR2
396                        ,p_date           IN     DATE) IS
397  l_date DATE := p_date;
398 BEGIN
399  DELETE FROM BIL_DO_L1_OPPTY_SUMRY
400   WHERE collection_date = l_date;
401  BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Deleted records from BIL Table BIL_DO_L1_OPPTY_SUMRY',p_debug=>G_Debug);
402  EXCEPTION
403   WHEN NO_DATA_FOUND THEN
404     null;
405   WHEN OTHERS THEN
406     ERRBUF := ERRBUF ||' Delete_Data:'||sqlcode||' '|| sqlerrm;
407     RETCODE := '1';
408 END;
409 
410 /*******
411 *Alter tables to logging
412 *********/
413 PROCEDURE Reset_Table_Usages IS
414 BEGIN
415     EXECUTE IMMEDIATE 'ALTER TABLE BIL.BIL_DO_L1_OPPTY_SUMRY LOGGING';
416     BIL_DO_UTIL_PKG.Write_Log('BIL Table BIL_DO_L1_OPPTY_SUMRY altered to logging',p_debug=>G_Debug);
417 END Reset_Table_Usages;
418 
419 
420 /*******************
421 *Truncate the table
422 *******************/
423 PROCEDURE Truncate_table is
424 BEGIN
425   EXECUTE IMMEDIATE 'truncate table bil.bil_do_l1_oppty_sumry';
426  BIL_DO_UTIL_PKG.Write_Log(p_msg=>'BIL Table bil_do_l1_oppty_sumry is truncated',p_debug=>G_Debug);
427 END;
428 
429 
430 /*******
431 *Alter all the tables used to nologging
432 *******/
433 PROCEDURE Set_Table_Usages IS
434 BEGIN
435     EXECUTE IMMEDIATE 'ALTER TABLE BIL.BIL_DO_L1_OPPTY_SUMRY NOLOGGING';
436     BIL_DO_UTIL_PKG.Write_Log('BIL Table BIL_DO_L1_OPPTY_SUMRY altered to nologging',p_debug=>G_Debug);
437 END Set_Table_Usages;
438 
439 END BIL_DO_L1_OPPTY_SUMRY_PKG;