[Home] [Help]
PACKAGE BODY: APPS.BEN_TCS_STMT_VALID_HRCHY
Source
1 PACKAGE BODY BEN_TCS_STMT_VALID_HRCHY as
2 /* $Header: bentcshg.pkb 120.1 2006/04/12 04:45 srangasa noship $ */
3 item_hrchy_values cat_item_hrchy_table ;
4 subcat_hrchy_values cat_subcat_hrchy_table ;
5
6 --
7 -- ============================================================================
8 -- <<write>>
9 -- ============================================================================
10 --
11 PROCEDURE WRITE (p_string IN VARCHAR2)
12 IS
13 BEGIN
14 ben_batch_utils.WRITE (p_string);
15 END WRITE;
16
17
18
19 PROCEDURE obj_hrchy_gen(p_stmt_id IN NUMBER, p_bg_id IN NUMBER ,
20 p_period_id IN NUMBER ,
21 p_item_hrchy_values IN OUT NOCOPY cat_item_hrchy_table ,p_subcat_hrchy_values IN OUT NOCOPY cat_subcat_hrchy_table )
22 IS
23 l_proc VARCHAR2 (100)
24 := 'ben_tcs_stmt_valid_hrchy.obj_hrchy_gen';
25
26 CURSOR c_stmt_sect (v_stmt_id IN NUMBER, v_bg_id IN NUMBER)
27 IS
28 SELECT cat_id
29 FROM ben_tcs_stmt_cat stmt_cat
30 WHERE stmt_cat.stmt_id = v_stmt_id
31 AND stmt_cat.business_group_id = v_bg_id;
32
33 CURSOR c_cat_subcat (v_cat_id IN NUMBER)
34 IS
35 SELECT subcat_id
36 FROM ben_tcs_all_objects_in_cat stmt_all_obj
37 WHERE stmt_all_obj.cat_id = v_cat_id AND subcat_id IS NOT NULL;
38
39 CURSOR c_cat_item (v_cat_id IN NUMBER)
40 IS
41 SELECT stmt_all_obj.item_id, col_cat.contributor_type_cd cntr_cd ,stmt_all_obj.all_objects_in_cat_id obj_id,
42 stmt_all_obj.row_in_cat_id row_id
43 FROM ben_tcs_all_objects_in_cat stmt_all_obj, ben_tcs_col_in_cat col_cat
44 WHERE stmt_all_obj.cat_id = v_cat_id AND item_id IS NOT NULL
45 AND col_cat.col_in_cat_id = stmt_all_obj.col_in_cat_id;
46
47 CURSOR c_cat_parent (v_cat_id IN NUMBER)
48 IS
49 SELECT cat_id , row_in_cat_id ,all_objects_in_cat_id
50 FROM ben_tcs_all_objects_in_cat stmt_all_obj
51 WHERE stmt_all_obj.subcat_id = v_cat_id;
52
53 CURSOR c_cat_hrchy (v_cat_id IN NUMBER, v_stmt_id NUMBER , v_period_id NUMBER)
54 IS
55 SELECT sub_cat_id, lvl_num
56 FROM ben_tcs_cat_subcat_hrchy cat_subcat_hr
57 WHERE cat_subcat_hr.cat_id = v_cat_id
58 AND cat_subcat_hr.stmt_id = v_stmt_id
59 ANd cat_subcat_hr.stmt_perd_id = v_period_id ;
60
61 CURSOR c_item_hrchy (v_cat_id IN NUMBER, v_stmt_id NUMBER, v_period_id NUMBER)
62 IS
63 SELECT item_id, lvl_num,cntr_cd,row_in_cat_id ,all_objects_in_cat_id
64 FROM ben_tcs_cat_item_hrchy cat_item_hr
65 WHERE cat_item_hr.cat_id = v_cat_id
66 AND cat_item_hr.stmt_id = v_stmt_id
67 AND cat_item_hr.stmt_perd_id = v_period_id ;
68
69 stmt_sect_rec c_stmt_sect%ROWTYPE;
70 cat_subcat_rec c_cat_subcat%ROWTYPE;
71 cat_item_rec c_cat_item%ROWTYPE;
72 cat_parent_rec c_cat_parent%ROWTYPE;
73 cat_hrchy_rec c_cat_hrchy%ROWTYPE;
74 item_hrchy_rec c_item_hrchy%ROWTYPE;
75
76 TYPE stmt_obj_hrchy_table IS TABLE OF NUMBER
77 INDEX BY BINARY_INTEGER;
78
79 stmt_obj_hrchy stmt_obj_hrchy_table;
80 l_number_of_rows NUMBER;
81 l_number_of_rows_temp NUMBER;
82 l_check_count NUMBER;
83 temp_count NUMBER;
84 l_next_row NUMBER;
85 l_counter NUMBER;
86 l_cur_cat_id NUMBER;
87 l_id NUMBER;
88 l_count NUMBER;
89 l_cur_row NUMBER;
90 l_flag VARCHAR2 (1);
91 Enter VARCHAR2 (1);
92 l_item_cnt NUMBER := 0;
93 distinct_item NUMBER :=0;
94 item_count NUMBER := 0;
95 subcat_count NUMBER := 0;
96 item_table item_hrchy_table;
97 cat_type VARCHAR2 (20);
98
99
100 BEGIN
101 WRITE ( 'Generating Hierarchy tables entries . In obj_hrchy_gen ..' );
102 hr_utility.set_location ('Entering:' || l_proc, 25);
103 l_count := 0;
104 l_counter := 1;
105 item_hrchy_values := p_item_hrchy_values ;
106 subcat_hrchy_values := p_subcat_hrchy_values ;
107 WRITE( ' item_id count ' || item_hrchy_values.COUNT);
108 WRITE( ' subcat count ' || subcat_hrchy_values.COUNT);
109
110 SAVEPOINT hrchy;
111
112 OPEN c_stmt_sect (p_stmt_id, p_bg_id);
113 LOOP
114 FETCH c_stmt_sect
115 INTO l_id;
116 EXIT WHEN c_stmt_sect%NOTFOUND;
117 l_count := l_count + 1;
118 stmt_obj_hrchy (l_count) := l_id;
119 END LOOP;
120 CLOSE c_stmt_sect;
121 IF NVL (stmt_obj_hrchy.LAST, 0) = 0
122 THEN
123 WRITE('No Section Found ....' );
124 fnd_message.raise_error;
125 END IF;
126 l_number_of_rows := stmt_obj_hrchy.COUNT;
127
128 WHILE (l_counter <= l_number_of_rows)
129 LOOP
130 l_cur_cat_id := stmt_obj_hrchy (l_counter);
131 DELETE FROM ben_tcs_cat_item_hrchy
132 WHERE cat_id = stmt_obj_hrchy (l_counter)
133 and stmt_id = p_stmt_id
134 and stmt_perd_id = p_period_id;
135 DELETE FROM ben_tcs_cat_subcat_hrchy
136 WHERE cat_id = stmt_obj_hrchy (l_counter)
137 and stmt_id = p_stmt_id
138 and stmt_perd_id = p_period_id;
139 OPEN c_cat_subcat (l_cur_cat_id);
140
141 LOOP
142 FETCH c_cat_subcat
143 INTO l_id;
144
145 l_flag := 'N';
146 EXIT WHEN c_cat_subcat%NOTFOUND;
147 l_check_count := stmt_obj_hrchy.COUNT;
148
149 FOR table_row IN 1 .. l_check_count
150 LOOP
151 IF (l_id = stmt_obj_hrchy (table_row))
152 THEN
153 stmt_obj_hrchy (table_row) := -1;
154 EXIT;
155 END IF;
156 END LOOP;
157 l_count := l_count + 1;
158 stmt_obj_hrchy (l_count) := l_id;
159 END LOOP;
160
161 CLOSE c_cat_subcat;
162
163 l_number_of_rows := stmt_obj_hrchy.COUNT;
164 l_counter := l_counter + 1;
165
166 OPEN c_cat_item (l_cur_cat_id);
167 LOOP
168 FETCH c_cat_item
169 INTO cat_item_rec;
170
171 EXIT WHEN c_cat_item%NOTFOUND;
172
173 IF c_cat_item%FOUND
174 THEN
175 item_count := item_hrchy_values.COUNT ;
176 item_hrchy_values(item_count +1 ).stmt_id:= p_stmt_id;
177 item_hrchy_values(item_count +1 ).cat_id:=l_cur_cat_id;
178 item_hrchy_values(item_count +1 ).item_id:=cat_item_rec.item_id;
179 item_hrchy_values(item_count +1 ).lvl_num:=1;
180 item_hrchy_values(item_count +1 ).cntr_cd := cat_item_rec.cntr_cd ;
181 item_hrchy_values(item_count +1 ).row_cat_id :=cat_item_rec.row_id;
182 item_hrchy_values(item_count +1 ).all_objects_id := cat_item_rec.obj_id;
183 item_hrchy_values(item_count +1 ).perd_id:= p_period_id;
184
185 INSERT INTO ben_tcs_cat_item_hrchy
186 (stmt_id, cat_id, item_id, lvl_num,cntr_cd ,row_in_cat_id , all_objects_in_cat_id,stmt_perd_id
187 )
188 VALUES (p_stmt_id, l_cur_cat_id, cat_item_rec.item_id, 1,cat_item_rec.cntr_cd,
189 cat_item_rec.row_id ,cat_item_rec.obj_id , p_period_id);
190 distinct_item := 0;
191 for i in 1..item_table.count
192 loop
193 if (item_table(i).item_id = cat_item_rec.item_id ) then
194 distinct_item := i ;
195 exit;
196 end if;
197 end loop;
198 if ( distinct_item = 0 ) then
199 l_item_cnt := l_item_cnt +1;
200 item_table(l_item_cnt).item_id := cat_item_rec.item_id;
201 item_table(l_item_cnt).stmt_id := p_stmt_id;
202 item_table(l_item_cnt).cntr_cd := cat_item_rec.cntr_cd;
203 end if;
204 END IF;
205 END LOOP;
206 CLOSE c_cat_item;
207 -- Update ben_tcs_row_in_cat set stmt_generated = 'Y' where cat_id = l_cur_cat_id;
208 END LOOP;
209
210 l_number_of_rows := stmt_obj_hrchy.COUNT;
211 l_number_of_rows_temp := stmt_obj_hrchy.COUNT;
212 l_cur_row := stmt_obj_hrchy.COUNT;
213 FOR table_row IN 1 .. l_number_of_rows
214 LOOP
215 If (stmt_obj_hrchy (l_cur_row) <> -1 ) then
216 l_cur_cat_id := stmt_obj_hrchy (l_cur_row);
217 OPEN c_cat_parent (l_cur_cat_id);
218
219 LOOP
220 FETCH c_cat_parent
221 INTO cat_parent_rec;
222
223 EXIT WHEN c_cat_parent%NOTFOUND;
224 Enter := 'N';
225 IF c_cat_parent%FOUND
226 THEN
227 FOR temp_count IN 1 .. l_number_of_rows_temp
228 LOOP
229 IF cat_parent_rec.cat_id = stmt_obj_hrchy (temp_count) THEN
230 Enter := 'Y';
231 END IF;
232 END LOOP;
233 IF ENTER = 'Y' THEN
234
235 subcat_count := subcat_hrchy_values.COUNT ;
236 subcat_hrchy_values(subcat_count +1 ).stmt_id:= p_stmt_id;
237 subcat_hrchy_values(subcat_count +1 ).cat_id:=cat_parent_rec.cat_id;
238 subcat_hrchy_values(subcat_count +1 ).subcat_id:=l_cur_cat_id;
239 subcat_hrchy_values(subcat_count +1 ).lvl_num:=1;
240 subcat_hrchy_values(subcat_count +1 ).row_cat_id:=cat_parent_rec.row_in_cat_id;
241 subcat_hrchy_values(subcat_count +1 ).perd_id:= p_period_id;
242 INSERT INTO ben_tcs_cat_subcat_hrchy
243 (stmt_id, cat_id, sub_cat_id, lvl_num,row_in_cat_id,stmt_perd_id
244 )
245 VALUES (p_stmt_id, cat_parent_rec.cat_id, l_cur_cat_id, 1 ,cat_parent_rec.row_in_cat_id , p_period_id);
246
247 --Added For Stk Ext
248 distinct_item := 0;
249 SELECT cat_type_cd
250 INTO cat_type
251 FROM BEN_TCS_CAT
252 WHERE cat_id = l_cur_cat_id ;
253 IF ( cat_type = 'STKOPTEXT' ) THEN
254 for i in 1..item_table.count
255 loop
256 if (item_table(i).subcat_id = l_cur_cat_id ) then
257 distinct_item := i ;
258 exit;
259 end if;
260 end loop;
261 if ( distinct_item = 0 ) then
262 l_item_cnt := l_item_cnt +1;
263 item_table(l_item_cnt).subcat_id := l_cur_cat_id;
264 item_table(l_item_cnt).item_id := -1;
265 item_table(l_item_cnt).stmt_id := p_stmt_id;
266 end if;
267 END IF;
268 -- end of Stk Ext
269 OPEN c_cat_hrchy (l_cur_cat_id, p_stmt_id,p_period_id);
270
271 LOOP
272 FETCH c_cat_hrchy
273 INTO cat_hrchy_rec;
274
275 EXIT WHEN c_cat_hrchy%NOTFOUND;
276
277 IF c_cat_hrchy%FOUND
278 THEN
279 subcat_count := subcat_hrchy_values.COUNT ;
280 subcat_hrchy_values(subcat_count +1 ).stmt_id:= p_stmt_id;
281 subcat_hrchy_values(subcat_count +1 ).cat_id:=cat_parent_rec.cat_id;
282 subcat_hrchy_values(subcat_count +1 ).subcat_id:=cat_hrchy_rec.sub_cat_id;
283 subcat_hrchy_values(subcat_count +1 ).lvl_num:=cat_hrchy_rec.lvl_num + 1;
284 subcat_hrchy_values(subcat_count +1 ).row_cat_id:=cat_parent_rec.row_in_cat_id;
285 subcat_hrchy_values(subcat_count +1 ).perd_id:=p_period_id;
286
287 INSERT INTO ben_tcs_cat_subcat_hrchy
288 (stmt_id, cat_id,
289 sub_cat_id,
290 lvl_num,
291 row_in_cat_id ,stmt_perd_id
292 )
293 VALUES (p_stmt_id, cat_parent_rec.cat_id,
294 cat_hrchy_rec.sub_cat_id,
295 cat_hrchy_rec.lvl_num + 1,
296 cat_parent_rec.row_in_cat_id,
297 p_period_id
298 );
299 END IF;
300 END LOOP;
301 WRITE('After processing all subcategories ..') ;
302 CLOSE c_cat_hrchy;
303
304 OPEN c_item_hrchy (l_cur_cat_id, p_stmt_id,p_period_id);
305 LOOP
306 FETCH c_item_hrchy
307 INTO item_hrchy_rec;
308
309
310 EXIT WHEN c_item_hrchy%NOTFOUND;
311
312 IF c_item_hrchy%FOUND
313 THEN
314 item_count := item_hrchy_values.COUNT ;
315 item_hrchy_values(item_count +1 ).stmt_id:= p_stmt_id;
316 item_hrchy_values(item_count +1 ).cat_id:=cat_parent_rec.cat_id;
317 item_hrchy_values(item_count +1 ).item_id:=item_hrchy_rec.item_id;
318 item_hrchy_values(item_count +1 ).lvl_num:=item_hrchy_rec.lvl_num + 1;
319 item_hrchy_values(item_count +1 ).cntr_cd := item_hrchy_rec.cntr_cd ;
320 item_hrchy_values(item_count +1 ).row_cat_id := cat_parent_rec.row_in_cat_id;
321 item_hrchy_values(item_count +1 ).all_objects_id := cat_parent_rec.all_objects_in_cat_id;
322 item_hrchy_values(item_count +1 ).perd_id := p_period_id;
323
324 INSERT INTO ben_tcs_cat_item_hrchy
325 (stmt_id, cat_id,
326 item_id,
327 lvl_num ,cntr_cd, row_in_cat_id ,all_objects_in_cat_id,stmt_perd_id
328 )
329 VALUES (p_stmt_id, cat_parent_rec.cat_id,
330 item_hrchy_rec.item_id,
331 item_hrchy_rec.lvl_num + 1,
332 item_hrchy_rec.cntr_cd,
333 cat_parent_rec.row_in_cat_id,
334 cat_parent_rec.all_objects_in_cat_id ,
335 p_period_id
336 );
337
338 END IF;
339 END LOOP;
340 CLOSE c_item_hrchy;
341 END IF;
342 end if;
343 END LOOP;
344 CLOSE c_cat_parent;
345 end if ;
346 l_cur_row := l_cur_row - 1;
347 END LOOP;
348
349 ROLLBACK TO hrchy;
350 FOR i IN 1..item_table.COUNT
351 LOOP
352 WRITE(' in hierarchy items to be processed stmt id : '||p_stmt_id || 'item id is : '||item_table(i).item_id ) ;
353 INSERT INTO ben_tcs_cat_item_hrchy
354 (stmt_id, cat_id,
355 item_id,lvl_num,cntr_cd ,stmt_perd_id)
356 VALUES (p_stmt_id ,-999 ,item_table(i).item_id ,-1 ,item_table(i).cntr_cd ,item_table(i).subcat_id );
357
358 END LOOP;
359 p_item_hrchy_values := item_hrchy_values ;
360 p_subcat_hrchy_values := subcat_hrchy_values ;
361 EXCEPTION
362 WHEN OTHERS THEN
363 WRITE(SQLERRM);
364 WRITE('Error in hierarchy generation');
365 END obj_hrchy_gen;
366
367
368 PROCEDURE stmt_gen_valid_process (p_stmt_id IN NUMBER, p_bg_id IN NUMBER ,
369 p_period_id IN NUMBER ,
370 p_item_hrchy_values IN OUT NOCOPY cat_item_hrchy_table ,p_subcat_hrchy_values IN OUT NOCOPY cat_subcat_hrchy_table ,
371 p_status OUT NOCOPY Boolean )
372
373 IS
374 l_proc VARCHAR2 (100)
375 := 'ben_tcs_stmt_valid_hrchy.stmt_gen_valid_process';
376 BEGIN
377 WRITE(l_proc);
378 obj_hrchy_gen(p_stmt_id, p_bg_id ,p_period_id , p_item_hrchy_values ,p_subcat_hrchy_values);
379 WRITE('after hierarchy generation....');
380 p_status := true;
381 EXCEPTION
382 WHEN OTHERS THEN
383 p_status := false;
384
385 END stmt_gen_valid_process;
386
387
388 end BEN_TCS_STMT_VALID_HRCHY;