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.BSC_BIS_PMDLDR_DELDATA

Source


1 PACKAGE BODY BSC_BIS_PMDLDR_DELDATA AS
2 /* $Header: BSCPMDDB.pls 120.0 2005/06/01 14:57:20 appldev noship $ */
3 
4  TYPE T_KPI_REC_TYPE IS RECORD(
5    indicator            BSC_KPIS_B.INDICATOR%TYPE
6  );
7  TYPE G_KPI_TAB_TYPE is table of T_KPI_REC_TYPE index by binary_integer;
8  TYPE curType IS REF CURSOR ;
9 
10  TYPE T_TABLE_REC_TYPE IS RECORD(
11    NAME             VARCHAR2(100)
12  );
13 
14  TYPE G_TABLE_TAB_TYPE is table of T_TABLE_REC_TYPE index by binary_integer;
15 
16  G_KPI_TABLE  G_KPI_TAB_TYPE ;
17  G_KPI_TABLE_SIZE binary_integer := 0;
18 
19  G_TABNAME_TABLE  G_TABLE_TAB_TYPE ;
20  G_TABNAME_TABLE_SIZE binary_integer := 0;
21 
22  G_REC_LEVEL  number := 32767000;
23 
24  PROCEDURE  SET_RECURSIVE_LEVEL( P_lvl number)
25  IS
26  BEGIN
27    G_REC_LEVEL := P_lvl;
28  END;
29 
30  PROCEDURE ADD_TABLE(name varchar2)
31  IS
32  BEGIN
33      BSC_APPS.Add_Value_Big_In_Cond( 3, name );
34      G_TABNAME_TABLE_SIZE := G_TABNAME_TABLE_SIZE + 1;
35      G_TABNAME_TABLE(G_TABNAME_TABLE_SIZE).name := name;
36 
37  END;
38 
39  PROCEDURE REFRESH_BSC_TMP_BIG_IN_COND
40  IS
41    l_dummy varchar2(2000);
42  BEGIN
43    l_dummy :=  BSC_APPS.Get_New_Big_In_Cond_Varchar2( 4, 'TABLE_NAME');
44 
45    For i in 1..G_TABNAME_TABLE_SIZE
46    loop
47      BSC_APPS.Add_Value_Big_In_Cond( 4, G_TABNAME_TABLE(i).name );
48    end loop;
49    --debug('Refreshed  ' || G_TABNAME_TABLE_SIZE || ' to temp'  );
50    G_TABNAME_TABLE.delete;
51    G_TABNAME_TABLE_SIZE := 0;
52  END;
53 
54  FUNCTION get_indicatorstr
55  RETURN varchar2
56  IS
57    l_string varchar2(3000) := '';
58  BEGIN
59 
60    For i in 1..G_KPI_TABLE_SIZE
61    loop
62      if (i = 1) then
63        l_string := l_string || G_KPI_TABLE(i).indicator ;
64      else
65        l_string := l_string || ', ' ||  G_KPI_TABLE(i).indicator ;
66      end if;
67    end loop;
68 
69    return l_string;
70  End;
71 
72 
73 
74  PROCEDURE  DEBUG(txt varchar2)
75  IS
76  BEGIN
77    --Cond added for bug 3964235
78    IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
79      FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, 'bis.plsql.BSC_BIS_PMDLDR_DELDATA.DEBUG', txt);
80    END IF;
81  END;
82 
83 
84  PROCEDURE INITIALIZE
85  IS
86  BEGIN
87    G_KPI_TABLE_SIZE := 0;
88    G_KPI_TABLE.delete;
89 
90    BSC_APPS.Init_Bsc_Apps;
91    BSC_APPS.Init_Big_In_Cond_Table;
92 
93    G_TABNAME_TABLE_SIZE := 0;
94    G_TABNAME_TABLE.delete;
95 
96 
97  END;
98 
99  PROCEDURE SELECT_INDICATOR(p_ind number) IS
100  BEGIN
101    G_KPI_TABLE_SIZE := G_KPI_TABLE_SIZE + 1;
102    G_KPI_TABLE(G_KPI_TABLE_SIZE).INDICATOR := p_ind;
103  END;
104 
105  FUNCTION INIT_RELATED_TABLES(
106     p_stmtWhereInIndics varchar2)
107  RETURN NUMBER
108  IS
109    l_stmt varchar2(2000);
110    cur_init_tbl          curType;
111    l_tbl_name          varchar2(1000);
112    l_rownum   number := 0;
113  BEGIN
114 
115    l_stmt :=
116    ' SELECT DISTINCT TABLE_NAME ' ||
117    ' FROM BSC_KPI_DATA_TABLES_V ' ||
118    ' WHERE (' ||  p_stmtWhereInIndics || ' ) ';
119    -- || ' AND TABLE_NAME IS NOT NULL ';
120    debug('Executing ' || l_stmt);
121    open cur_init_tbl for l_stmt;
122    loop
123      FETCH cur_init_tbl INTO l_tbl_name;
124      exit when cur_init_tbl%NOTFOUND;
125      if ( l_tbl_name is not null ) then
126        ADD_TABLE(l_tbl_name);
127        l_rownum := l_rownum + 1;
128      end if;
129      --debug('Fetched ' || l_tbl_name || ' to BSC_TMP_BIG_IN_COND' );
130    end loop;
131    debug(l_rownum || ' rows fetched in INIT_RELATED_TABLES');
132    return l_rownum;
133    close cur_init_tbl;
134    EXCEPTION
135    WHEN OTHERS THEN
136      close cur_init_tbl;
137      raise;
138  END;
139 
140  FUNCTION MARK_TABLES_BY_INDICATORS( p_stmtWhereInTables varchar2, p_stmtTempWhereInTables varchar2)
141  RETURN NUMBER
142  IS
143    l_stmt varchar2(2000);
144    cur_tbl_mark_by_ind          curType;
145    l_tbl_name          varchar2(1000);
146    l_stmtWhereNotInTables varchar2(1000);
147    l_rownum number :=0;
148  BEGIN
149    l_stmtWhereNotInTables := p_stmtWhereInTables ;
150    l_stmt :=
151    ' SELECT TABLE_NAME ' ||
152    ' FROM BSC_KPI_DATA_TABLES_V ' ||
153    ' WHERE INDICATOR IN ( ' ||
154    ' SELECT INDICATOR ' ||
155    ' FROM BSC_KPI_DATA_TABLES_V ' ||
156    ' WHERE ' ||  p_stmtTempWhereInTables ||
157    ' ) ' ||
158    ' AND TABLE_NAME IS NOT NULL' ||
159    ' MINUS ' ||  l_stmtWhereNotInTables;
160 
161    debug('Executing '|| l_stmt);
162 
163    open cur_tbl_mark_by_ind for l_stmt;
164    loop
165      FETCH cur_tbl_mark_by_ind INTO l_tbl_name;
166      exit when cur_tbl_mark_by_ind%NOTFOUND;
167      ADD_TABLE(l_tbl_name);
168      l_rownum := l_rownum+1;
169      -- debug('Fetched ' || l_tbl_name || ' to BSC_TMP_BIG_IN_COND' );
170    end loop;
171    close cur_tbl_mark_by_ind;
172    debug(l_rownum || ' rows fetched in MARK_TABLES_BY_INDICATORS ');
173 
174    return l_rownum;
175    EXCEPTION
176    WHEN OTHERS THEN
177      close cur_tbl_mark_by_ind;
178      raise;
179   END;
180 
181  FUNCTION MARK_TABLES( p_stmtWhereInTables varchar2, p_stmtTempWhereInSrcTables varchar2,
182                        p_stmtTempWhereInTables varchar2 )
183  RETURN NUMBER
184  IS
185    l_stmt varchar2(2000);
186    cur_tbl_mark          curType;
187    l_tbl_name          varchar2(1000);
188    l_stmtWhereNotInTables varchar2(1000);
189    l_rownum       number := 0;
190  BEGIN
191    REFRESH_BSC_TMP_BIG_IN_COND;
192 
193    --l_stmtWhereNotInTables := ' NOT ( ' ||  p_stmtWhereInTables || ' ) ' ;
194    l_stmtWhereNotInTables := p_stmtWhereInTables ;
195 
196    l_stmt :=
197    ' SELECT * from ( SELECT TABLE_NAME ' ||
198    ' FROM BSC_DB_TABLES_RELS WHERE ' || p_stmtTempWhereInSrcTables ||
199    ' UNION ' ||
200    ' SELECT SOURCE_TABLE_NAME ' ||
201    ' FROM BSC_DB_TABLES_RELS WHERE ' || p_stmtTempWhereInTables || ' ) ' ||
202    ' MINUS ' || l_stmtWhereNotInTables;
203 
204    debug('Executing '|| l_stmt);
205 
206    open cur_tbl_mark for l_stmt;
207    loop
208      FETCH cur_tbl_mark INTO l_tbl_name;
209      exit when cur_tbl_mark%NOTFOUND;
210      ADD_TABLE(l_tbl_name);
211      l_rownum := l_rownum+1;
212      -- debug('Fetched ' || l_tbl_name || ' to BSC_TMP_BIG_IN_COND' );
213    end loop;
214    l_rownum:= l_rownum + MARK_TABLES_BY_INDICATORS( p_stmtWhereInTables, p_stmtTempWhereInTables);
215    close cur_tbl_mark;
216    debug(l_rownum || ' rows fetched in MARK_TABLES ');
217    return l_rownum;
218    EXCEPTION
219    WHEN OTHERS THEN
220      close cur_tbl_mark;
221      raise;
222  END;
223 
224  PROCEDURE MARK_INDICATORS(
225    p_stmtWhereInTables varchar2,
226    p_stmtWhereInIndics varchar2
227  )
228  IS
229    l_stmt varchar2(2000);
230    l_kpi  number;
231    l_stmtNotWhereInIndics varchar2(2000);
232    l_stmtWhereInIndics varchar2(2000);
233 
234    cur_kpi_mark          curType;
235    l_rownum       number := 0;
236  BEGIN
237    --l_stmtWhereInTables :=  substr(l_stmtWhereInTables, instr(l_stmtWhereInTables, 'IN') + 2 );
238    l_stmtWhereInIndics:= BSC_APPS.Get_New_Big_In_Cond_Number( 5, 'INDICATOR');
239 
240    l_stmtNotWhereInIndics := substr(p_stmtWhereInIndics, instr(p_stmtWhereInIndics, ' IN') + 3 );
241    l_stmt :=
242        'SELECT DISTINCT INDICATOR ' ||
243        ' FROM BSC_KPI_DATA_TABLES_V ' ||
244        ' WHERE TABLE_NAME IN ( ' || p_stmtWhereInTables || ' ) ' ||
245        --' WHERE ( ' || p_stmtWhereInTables || ' ) ' ||
246        ' MINUS ' || l_stmtNotWhereInIndics ;
247    debug('executing ' || l_stmt);
248    open cur_kpi_mark for l_stmt;
249    loop
250      FETCH cur_kpi_mark INTO l_kpi;
251      exit when cur_kpi_mark%NOTFOUND;
252      BSC_APPS.Add_Value_Big_In_Cond( 5, l_kpi );
253 
254    end loop;
255    close cur_kpi_mark;
256  END;
257 
258  PROCEDURE GET_RELATED_INDICATORS
259  IS
260    l_stmtWhereInIndics varchar2(2000);
261    l_stmtWhereInTables varchar2(2000);
262    l_stmtTempWhereInSrcTables varchar2(2000);
263    l_stmtTempWhereInSTables varchar2(2000);
264    l_rownum   number :=0;
265    l_rowmarked number;
266    l_rec_level number := 0 ;
267  BEGIN
268    l_stmtWhereInIndics:= BSC_APPS.Get_New_Big_In_Cond_Number( 1, 'INDICATOR');
269    l_stmtWhereInTables :=  BSC_APPS.Get_New_Big_In_Cond_Varchar2( 3, 'TABLE_NAME');
270    l_stmtTempWhereInSrcTables :=  BSC_APPS.Get_New_Big_In_Cond_Varchar2( 4, 'SOURCE_TABLE_NAME');
271    l_stmtTempWhereInSTables :=  BSC_APPS.Get_New_Big_In_Cond_Varchar2( 4, 'TABLE_NAME');
272    l_stmtWhereInTables :=  substr(l_stmtWhereInTables, instr(l_stmtWhereInTables, 'IN') + 2 );
273 
274 
275    debug('l_stmtWhereInIndics '||l_stmtWhereInIndics);
276    debug('l_stmtWhereInTables ' || l_stmtWhereInTables);
277    debug('l_stmtTempWhereInSrcTables ' || l_stmtTempWhereInSrcTables);
278    debug('l_stmtTempWhereInSTables ' || l_stmtTempWhereInSTables);
279 
280    For i in 1..G_KPI_TABLE_SIZE
281    loop
282      BSC_APPS.Add_Value_Big_In_Cond(1, G_KPI_TABLE(i).indicator);
283      -- debug('Fetched ' || G_KPI_TABLE(i).indicator || ' to BSC_TMP_BIG_IN_COND' );
284    end loop;
285    l_rownum := INIT_RELATED_TABLES( l_stmtWhereInIndics);
286 
287    while (l_rec_level < G_REC_LEVEL)
288    loop
289      debug('Recursive ' || l_rec_level );
290      l_rowmarked := MARK_TABLES( l_stmtWhereInTables, l_stmtTempWhereInSrcTables, l_stmtTempWhereInSTables);
291      l_rownum := l_rownum + l_rowmarked;
292      l_rec_level := l_rec_level + 1;
293 
294      exit when (l_rowmarked = 0);
295    end loop;
296    debug('Toatl ' || l_rownum || ' rows fetched ');
297    MARK_INDICATORS( l_stmtWhereInTables ,l_stmtWhereInIndics );
298 
299  END;
300 
301 END BSC_BIS_PMDLDR_DELDATA;