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_BASE_GRP_PKG

Source


1 PACKAGE BODY BIL_DO_L1_BASE_GRP_PKG AS
2 /* $Header: bilgrl1b.pls 115.14 2002/01/29 13:55:58 pkm ship      $ */
3 
4   -- Global Variables and Constants
5      -- G_Debug will be modified to TRUE programatically.
6      -- when the parameter passed for P_debug is Y
7      -- Once, this is set to TRUE, 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    BOOLEAN := FALSE;
12      G_Degree   NUMBER  := 4;
13 
14    -- Global variables for WHO variables and Concurrent program
15      G_request_id    NUMBER;
16      G_appl_id       NUMBER;
17      G_program_id    NUMBER;
18      G_user_id       NUMBER;
19      G_login_id      NUMBER;
20 
21 PROCEDURE Truncate_Table;
22 
23 PROCEDURE Set_Table_Usages;
24 
25 PROCEDURE Init_Globals;
26 
27 PROCEDURE Insert_Parent_Data( ERRBUF           IN OUT VARCHAR2
28                              ,RETCODE          IN OUT VARCHAR2
29                              ,p_degree         IN     NUMBER);
30 
31 PROCEDURE Insert_Hirarchial_Data( ERRBUF           IN OUT VARCHAR2
32                                  ,RETCODE          IN OUT VARCHAR2
33                                  ,p_group_id       IN     NUMBER
34                                  ,p_level          IN     NUMBER
35                                  ,p_degree         IN     NUMBER);
36 
37 PROCEDURE Insert_From_Denorm(  ERRBUF           IN OUT VARCHAR2
38                               ,RETCODE          IN OUT VARCHAR2
39                               ,p_level          IN     NUMBER
40                               ,p_degree         IN     NUMBER);
41 
42 PROCEDURE Reset_Table_Usages;
43 
44 
45 
46 /*******************************************************************
47 *Inserts data to the level equal to profile option value of
48 *BIL_DO_L1_GRP_AGGR_LVL.
49 *ERRBUFF:    error message returned by the proc
50 *RETCODE:    completion status of the procedure
51 *p_degree:   parallel degree
52 *p_debug:    debug mode (yes or no)
53 *p_trace:    trace mode (yes or no)
54 ********************************************************************/
55 procedure collect_temp_data( ERRBUF        OUT VARCHAR2
56                            , RETCODE       OUT VARCHAR2
57                            , p_degree      IN  NUMBER   DEFAULT 4
58                            , p_debug_mode  IN  VARCHAR2 DEFAULT 'N'
59                            , p_trace_mode  IN  VARCHAR2 DEFAULT 'N'
60                            ) is
61 
62   CURSOR lvl_cur(l_level number) is
63     select child_sales_group_id, Hier_level
64       from bil_do_l1_base_grp_temp
65      where Hier_level = l_level;
66 
67  l_profile_option number := FND_PROFILE.VALUE('BIL_DO_L1_GRP_AGGR_LVL');
68  /*Fetching the hirarchial level to which data need to be retrieved.*/
69  l_counter number := 1;
70 BEGIN
71    /*Initialize the globals*/
72     RETCODE := 0;
73     Init_Globals;
74   -- Validate input parameters
75       IF p_debug_mode = 'Y' THEN
76          G_Debug := 'Y';
77       END IF;
78 
79       IF p_trace_mode = 'Y' THEN
80          G_Trace := TRUE;
81          EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
82       END IF;
83 
84       IF NVL(p_degree,0) > 0 THEN
85         G_Degree := p_degree;
86       END IF;
87 
88     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Parameters for collect_temp_data - Debug: ' || p_debug_mode
89           || '  Trace: ' || p_trace_mode
90           || '  Parallel Degree: ' || TO_CHAR(G_Degree)
91           || '  Profile Option: ' || TO_CHAR(l_profile_option), p_debug=>G_Debug);
92 
93 /*Set table to nologging*/
94 Set_Table_Usages;
95 /*Truncate the table*/
96 BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Truncating table', p_debug=>G_Debug);
97 
98 Truncate_table;
99 
100 /*insert all the parents*/
101 Insert_Parent_Data( ERRBUF    => ERRBUF
102                    ,RETCODE   => RETCODE
103                    ,p_degree  => p_degree);
104 
105  /*Loops till it equals the hirarchial level*/
106  while(l_counter < l_profile_option)
107   loop
108    for j in lvl_cur(l_counter)/*fetches and loops sales groups at level l_counter*/
109    loop
110      Insert_Hirarchial_Data( ERRBUF      => ERRBUF
111                             ,RETCODE     => RETCODE
112                             ,p_group_id  => j.child_sales_group_id
113                             ,p_level     => l_counter
114                             ,p_degree    => p_degree); -- inserting values for level (l_Counter+1)
115      if (lvl_cur%notfound)  then
116        exit;
117      end if;
118    end loop;--for loop
119    l_counter := l_counter + 1;
120   end loop; --while loop
121 
122   /*Inserting the last level of data into the table*/
123  insert_from_denorm(  ERRBUF      => ERRBUF
124                      ,RETCODE     => RETCODE
125                      ,p_level     => l_profile_option
126                      ,p_degree    => p_degree);
127 
128  /*Analyze the table after insertion*/
129  BIL_DO_UTIL_PKG.Write_Log('Analyze table BIL_DO_L1_BASE_GRP_TEMP',p_debug=>G_Debug);
130  DBMS_STATS.gather_table_stats(ownname=>'BIL', tabName=>'BIL_DO_L1_BASE_GRP_TEMP', cascade=>TRUE,
131                                   degree=>G_Degree, estimate_percent=>99, granularity=>'GLOBAL');
132 
133 
134  IF G_Trace THEN
135     EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=FALSE';
136  END IF;
137 Reset_Table_usages;
138 EXCEPTION
139       WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
140           ERRBUF := ERRBUF||'Error in collect_temp_data:'||to_char(sqlcode)||sqlerrm;
141           RETCODE := '2' ;
142           BIL_DO_UTIL_PKG.Write_Log(
143               p_msg => 'Error in Collect_Temp_Data:'||to_char(sqlcode)||sqlerrm
144             , p_force => 'Y', p_debug => G_Debug);
145           ROLLBACK;
146           Reset_Table_Usages;
147 
148 
149        WHEN OTHERS THEN
150           ERRBUF := ERRBUF||'Error in Collect_Temp_Data:'||to_char(sqlcode)||sqlerrm;
151           RETCODE := '2';
152           BIL_DO_UTIL_PKG.Write_Log(
153               p_msg => 'Error in Collect_Temp_Data:'||to_char(sqlcode)||sqlerrm
154             , p_force => 'Y', p_debug => G_Debug);
155           ROLLBACK;
156           Reset_table_Usages;
157 END collect_temp_data;
158 
159 /********************************************************************
160 *Initialize Global variables for WHO variables and Concurrent program
161 *********************************************************************/
162  PROCEDURE Init_Globals IS
163 
164  BEGIN
165      G_request_id    := FND_GLOBAL.CONC_REQUEST_ID();
166      G_appl_id       := FND_GLOBAL.PROG_APPL_ID();
167      G_program_id    := FND_GLOBAL.CONC_PROGRAM_ID();
168      G_user_id       := FND_GLOBAL.USER_ID();
169      G_login_id      := FND_GLOBAL.CONC_LOGIN_ID();
170  END Init_Globals;
171 
172 
173 
174  /**************************************************************
175  * Insert_parent_data inserts all the top level
176  *salesgroups(parents) as hier_level=1 in the table
177  ***************************************************************/
178 
179  PROCEDURE Insert_Parent_Data( ERRBUF           IN OUT VARCHAR2
180                              ,RETCODE          IN OUT VARCHAR2
181                              ,p_degree         IN     NUMBER)IS
182  l_sysdate DATE := sysdate;
183  l_level NUMBER := 1;
184 
185  l_insert_statement VARCHAR2(1000);
186  l_select_statement VARCHAR2(5000);
187  l_quote            VARCHAR2(1) :=  FND_GLOBAL.LOCAL_CHR(39); -- single quote
188  l_parent_group     VARCHAR2(50) := 'PARENT_GROUP';
189  l_sales            VARCHAR2(20) := 'SALES';
190  l_stime            DATE := sysdate;
191  BEGIN
192  l_insert_statement := 'INSERT INTO  /*+ APPEND PARALLEL(bgt,'||p_degree||') */ bil_do_l1_base_grp_temp bgt';
193  l_insert_statement := l_insert_statement||'( child_sales_group_id,sales_group_id,Hier_level,creation_date,created_by';
194  l_insert_statement := l_insert_statement||',last_update_date,last_updated_by,last_update_login,request_id';
195  l_insert_statement := l_insert_statement||',program_application_id,program_id,program_update_date)   ';
196 
197  l_select_statement :=' (SELECT  group_id,group_id, :l_level, :l_sysdate, :G_user_id, :l_sysdate, :G_user_id, :G_login_id';
198  l_select_statement :=l_select_statement||' , :G_request_id, :G_appl_id, :G_program_id, :l_sysdate  FROM  ';
199 
200  l_select_statement :=l_select_statement||' ( SELECT /*+ Parallel(REL,'||p_degree||') */  distinct REL.related_grouP_id group_id ';
201  l_select_statement :=l_select_statement||'     FROM jtf_rs_grp_relations REL, jtf_rs_group_usages usg ';
202  l_select_statement :=l_select_statement||'    WHERE relation_type = :l_parent_group AND related_group_id not in ';
203  l_select_statement :=l_select_statement||'         (SELECT group_id FROM apps.jtf_rs_grp_relations) ';
204  l_select_statement :=l_select_statement||'      AND (start_date_active <= :l_sysdate OR start_date_active IS NULL) ';
205  l_select_statement :=l_select_statement||'      AND (end_date_active > :l_sysdate OR end_date_active IS NULL) ';
206  l_select_statement :=l_select_statement||'      AND usg.group_id = REL.related_group_id    AND usg.Usage = :l_sales))';
207 
208 
209  EXECUTE IMMEDIATE l_insert_statement||l_select_statement
210  USING
211     l_level
212    ,l_sysdate
213    ,G_user_id
214    ,l_sysdate
215    ,G_user_id
216    ,G_login_id
217    ,G_request_id
218    ,G_appl_id
219    ,G_program_id
220    ,l_sysdate
221    ,l_parent_group
222    ,l_sysdate
223    ,l_sysdate
224    ,l_sales;
225 
226  COMMIT;
227  IF (SQL%ROWCOUNT = 0) THEN
228       BIL_DO_UTIL_PKG.Write_Log(p_msg=>'No Rows are inserted from Insert_parent_data',p_stime=>l_stime,p_etime=>SYSDATE, p_force=>'Y',p_debug=>G_Debug);
229  END IF;
230  BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Rows Inserted from Insert_parent_data:'||SQL%ROWCOUNT,p_stime=>l_stime,p_etime=>SYSDATE, p_debug=>G_Debug);
231  EXCEPTION
232       WHEN OTHERS THEN
233         ERRBUF := ERRBUF ||' Insert_Parent_Data:'||sqlcode||' '|| sqlerrm;
234         RETCODE := '2';
235          ROLLBACK;
236         BIL_DO_UTIL_PKG.Write_Log(p_msg=>' Insert_Parent_Data:'||sqlcode||' '|| sqlerrm
237            , p_force=> 'Y', p_debug => G_Debug);
238 
239 END Insert_Parent_Data;
240 
241 /**********************************************
242 *insert_hirarchial_data is used to insert hirarchial
243 *data up to the level specified in profile_option
244 *starting from top.
245 *@p_level - Determines which level of data is being inserted.
246 *@Group_id - Sales_group_id
247 ***********************************************/
248 procedure Insert_Hirarchial_Data( ERRBUF           IN OUT VARCHAR2
249                                  ,RETCODE          IN OUT VARCHAR2
250                                  ,p_group_id       IN     NUMBER
251                                  ,p_level          IN     NUMBER
252                                  ,p_degree         IN     NUMBER) is
253 l_level NUMBER := 0;
254 l_sysdate DATE := sysdate;
255 
256 l_insert_statement VARCHAR2(1000);
257 l_select_statement VARCHAR2(5000);
258 l_quote            VARCHAR2(1) :=  FND_GLOBAL.LOCAL_CHR(39); -- single quote
259 l_relation_type    VARCHAR2(20) := 'PARENT_GROUP';
260 l_delete_flag_y    VARCHAR2(1) := 'Y';
261 l_delete_flag_n    VARCHAR2(1) := 'N';
262 l_sales            VARCHAR2(20) := 'SALES';
263 l_stime            DATE := sysdate;
264 BEGIN
265  /* inserting data for the given level */
266 l_level := p_level + 1;
267 l_insert_statement := 'INSERT INTO  /*+ APPEND PARALLEL(bgt,'||p_degree||') */ bil_do_l1_base_grp_temp bgt';
268 l_insert_statement := l_insert_statement||' ( child_sales_group_id, sales_group_id, Hier_level, creation_date';
269 l_insert_statement := l_insert_statement||', created_by, last_update_date, last_updated_by, last_update_login';
270 l_insert_statement := l_insert_statement||', request_id, program_application_id, program_id, program_update_date)';
271 
272 l_select_statement := '(SELECT /*+ PARALLEL(REL,'||p_degree||') */ REL.group_id';
273 l_select_statement := l_select_statement||'       ,REL.group_id, :l_level, :l_sysdate, :G_user_id';
274 l_select_statement := l_select_statement||'       ,:l_sysdate,:G_user_id,:G_login_id,:G_request_id';
275 l_select_statement := l_select_statement||'       ,:G_appl_id,:G_program_id,:l_sysdate';
276 l_select_statement := l_select_statement||'   FROM jtf_rs_grp_relations REL, jtf_rs_group_usages usg ';
277 l_select_statement := l_select_statement||'  WHERE REL.relation_type = :l_relation_type';
278 l_select_statement := l_select_statement||'    AND (REL.start_date_active <= :l_sysdate OR REL.start_date_active is null)';
279 l_select_statement := l_select_statement||'    AND (REL.end_date_active >= :l_sysdate OR REL.end_date_active is null)';
280 l_select_statement := l_select_statement||'    AND NVL(rel.delete_flag, :l_delete_flag_n) <> :l_delete_flag_y';
281 l_select_statement := l_select_statement||'    AND rel.group_id <> :p_group_id AND rel.related_group_id = :p_group_id ';
282 l_select_statement := l_select_statement||'    AND rel.group_id = usg.group_id AND usg.usage = :l_sales) ';
283 
284 EXECUTE IMMEDIATE l_insert_statement||l_select_statement
285 USING
286      l_level
287     ,l_sysdate
288     ,G_user_id
289     ,l_sysdate
290     ,G_user_id
291     ,G_login_id
292     ,G_request_id
293     ,G_appl_id
294     ,G_program_id
295     ,l_sysdate
296     ,l_relation_type
297     ,l_sysdate
298     ,l_sysdate
299     ,l_delete_flag_n
300     ,l_delete_flag_y
301     ,p_group_id
302     ,p_group_id
303     ,l_sales;
304 
305 COMMIT;
306  IF (SQL%ROWCOUNT = 0) THEN
307       BIL_DO_UTIL_PKG.Write_Log(p_msg=>'No Rows are inserted from Insert_Hirarchial_Data',p_stime=>l_stime,p_etime=>SYSDATE, p_force=>'Y',p_debug=>G_Debug);
308  END IF;
309 
310  BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Rows Inserted from Insert_Hirarchial_Data:'||SQL%ROWCOUNT,p_stime=>l_stime,p_etime=>SYSDATE, p_debug=>G_Debug);
311 
312 EXCEPTION
313       WHEN OTHERS THEN
314         ERRBUF := ERRBUF ||' Insert_Hirarchial_Data:'||sqlcode||' '|| sqlerrm;
315          RETCODE := '2';
316          ROLLBACK;
317       BIL_DO_UTIL_PKG.Write_Log(p_msg=>' Insert_Hirarchial_Data:'||sqlcode||' '|| sqlerrm
318            , p_force=> 'Y', p_debug => G_Debug);
319 
320 end insert_hirarchial_data;
321 
322 /*****************************************************************************************************
323 *
324 *insert_from_denorm is used insert data from jtf_rs_groups_denorm table from hirarchial level=p_level.
325 *@p_group_id - Group_id which needs to be inserted along with all its children.
326 *
327 ******************************************************************************************************/
328 procedure Insert_From_Denorm(  ERRBUF           IN OUT VARCHAR2
329                               ,RETCODE          IN OUT VARCHAR2
330                               ,p_level          IN     NUMBER
331                               ,p_degree         IN     NUMBER) is
332 l_level number := 0;
333 l_sysdate DATE := sysdate;
334 
335 l_insert_statement VARCHAR2(1000);
336 l_select_statement VARCHAR2(5000);
337 l_quote            VARCHAR2(1) :=  FND_GLOBAL.LOCAL_CHR(39); -- single quote
338 l_sales            VARCHAR2(20) := 'SALES';
339 l_stime            DATE := sysdate;
340 BEGIN
341 l_level := p_level + 1;
342 
343 l_insert_statement := 'INSERT INTO  /*+ APPEND PARALLEL(bgt,'||p_degree||') */ bil_do_l1_base_grp_temp bgt';
344 l_insert_statement := l_insert_statement||' ( child_sales_group_id, sales_group_id, Hier_level, creation_date';
345 l_insert_statement := l_insert_statement||', created_by, last_update_date, last_updated_by, last_update_login';
346 l_insert_statement := l_insert_statement||', request_id, program_application_id, program_id, program_update_date)';
347 
348 l_select_statement :=' (select /*+ PARALLEL(den,'||p_degree||') */  distinct den.group_id';
349 l_select_statement :=l_select_statement||' , den.parent_group_id, :l_level, :l_sysdate, :G_user_id, :l_sysdate';
350 l_select_statement :=l_select_statement||' , :G_user_id, :G_login_id, :G_request_id, :G_appl_id';
351 l_select_statement :=l_select_statement||' , :G_program_id, :l_sysdate';
352 l_select_statement :=l_select_statement||' from jtf_rs_groups_denorm den, jtf_rs_group_usages usg ';
353 l_select_statement :=l_select_statement||' where den.parent_group_id in ';
354 l_select_statement :=l_select_statement||'     (select child_sales_group_id from bil_do_l1_base_grp_temp';
355 l_select_statement :=l_select_statement||'       where Hier_level = :p_level)';
356 l_select_statement :=l_select_statement||'  and (den.start_date_active <= :l_sysdate OR den.start_date_active is null)';
357 l_select_statement :=l_select_statement||'  and (den.end_date_active >= :l_sysdate OR den.End_date_active is null)';
361   USING
358 l_select_statement :=l_select_statement||'  and den.parent_group_id <> den.group_id AND usg.group_id = den.group_id  AND usg.usage = :l_sales)';
359 
360 EXECUTE IMMEDIATE l_insert_statement||l_select_statement
362      l_level
363     ,l_sysdate
364     ,G_user_id
365     ,l_sysdate
366     ,G_user_id
367     ,G_login_id
368     ,G_request_id
369     ,G_appl_id
370     ,G_program_id
371     ,l_sysdate
372     ,p_level
373     ,l_sysdate
374     ,l_sysdate
375     ,l_sales;
376 
377 COMMIT;
378  IF (SQL%ROWCOUNT = 0) THEN
379       BIL_DO_UTIL_PKG.Write_Log(p_msg=>'No Rows are inserted from Insert_From_Denorm',p_stime=>l_stime,p_etime=>SYSDATE, p_force=>'Y',p_debug=>G_Debug);
380  END IF;
381 
382  BIL_DO_UTIL_PKG.Write_Log(p_msg=>'Rows Inserted from Insert_From_Denorm:'||SQL%ROWCOUNT,p_stime=>l_stime,p_etime=>SYSDATE, p_debug=>G_Debug);
383 EXCEPTION
384       WHEN OTHERS THEN
385         ERRBUF := ERRBUF ||' Insert_From_Denorm:'||sqlcode||' '|| sqlerrm;
386         RETCODE := '2';
387          ROLLBACK;
388         BIL_DO_UTIL_PKG.Write_Log(p_msg=>' Insert_From_Denorm:'||sqlcode||' '|| sqlerrm
389            , p_force=> 'Y',p_debug => G_Debug);
390 
391 END Insert_From_Denorm;
392 
393 /*******************
394 *Truncate the table
395 *******************/
396 PROCEDURE Truncate_table is
397 BEGIN
398   EXECUTE IMMEDIATE 'truncate table bil.bil_do_l1_base_grp_temp';
399  BIL_DO_UTIL_PKG.Write_Log(p_msg=>'BIL Table bil_do_l1_base_grp_temp is truncated',p_debug=>G_Debug);
400 END;
401 
402 /*************************
403 *Alter tables to logging
404 *************************/
405 PROCEDURE Reset_Table_Usages IS
406 BEGIN
407     EXECUTE IMMEDIATE 'ALTER TABLE bil.bil_do_l1_base_grp_temp LOGGING';
408     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'BIL Table bil_do_l1_base_grp_temp altered to logging', p_debug=>G_Debug);
409 END Reset_Table_Usages;
410 
411   /***********************************************************
412   *  Alter all the tables used to nologging, drop the indexes
413   ***********************************************************/
414   PROCEDURE Set_Table_Usages IS
415   BEGIN
416 
417     EXECUTE IMMEDIATE 'ALTER TABLE bil.bil_do_l1_base_grp_temp NOLOGGING';
418 
419     BIL_DO_UTIL_PKG.Write_Log(p_msg=>'BIL Table bil_do_l1_base_grp_temp altered to nologging',p_debug=>G_Debug);
420 
421   END Set_Table_Usages;
422 
423 END BIL_DO_L1_BASE_GRP_PKG;