[Home] [Help]
PACKAGE BODY: APPS.PA_PPR_SMART_SLICE_PVT
Source
1 package body PA_ppr_smart_slice_PVT as
2 /* $Header: PAPPR04B.pls 120.0.12020000.4 2013/05/23 23:41:51 sachandr noship $
3 * * */
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PROGRESS_PVT';
6
7
8 procedure ppr_log (
9 p_msg IN VARCHAR2,
10 p_level IN number default 0
11 ) as
12
13 begin
14
15 -- if ( p_level <= g_log_level ) then
16
17 -- insert into sri_log values ( fnd_log_messages_s.nextval, p_msg);
18 pa_debug.write('PA_ppr_smart_slice_PVT', p_msg ,1);
19 --commit;
20 null;
21 -- end if;
22
23 end ppr_log;
24
25
26 PROCEDURE maintain_smart_slice (
27 p_rbs_version_id IN NUMBER :=NULL,
28 p_plan_version_id_tbl IN SYSTEM.pa_num_tbl_type :=
29 SYSTEM.pa_num_tbl_type(),
30 p_wbs_element_id IN NUMBER,
31 p_rbs_element_id IN NUMBER,
32 p_prg_rollup_flag IN VARCHAR2,
33 p_curr_record_type_id IN NUMBER,
34 p_calendar_type IN VARCHAR2,
35 p_wbs_version_id IN NUMBER,
36 p_commit IN VARCHAR2 := 'Y',
37 p_rbs_version_id_tbl IN SYSTEM.pa_num_tbl_type :=
38 SYSTEM.pa_num_tbl_type(),
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 x_return_status OUT NOCOPY VARCHAR2,
42 p_fact_slice varchar2 default 'NULL' ) IS
43
44 -- l_fact_act_rlp_exists VARCHAR2(1) := 'N';
45 -- l_rollup_status_exists VARCHAR2(1) := 'N';
46 l_exists VARCHAR2(1) := 'N';
47 l_struct_element_id NUMBER := -1;
48 l_project_id NUMBER := 1;
49 l_proj_element_ids SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
50 l_plan_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
51 l_plan_type_code_tbl SYSTEM.pa_varchar2_1_tbl_type :=
52 SYSTEM.pa_varchar2_1_tbl_type();
53 l_project_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
54 l_wbs_version_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
55 l_count NUMBER := 1;
56
57
58 -- OLAP START
59
60 l_pjt_rollup_enabled_flag varchar2(1) ;
61
62 l_new_status varchar2(1);
63 l_new_msg_count number;
64 l_new_msg_data varchar2(250);
65 l_new_rbs_version_id number ;
66 l_cbs_version_id number ;
67
68 -- OLAP END
69
70 BEGIN
71
72 Pji_Pjp_Fp_Curr_Wrap.INIT_ERR_STACK
73 ( p_package_name => g_package_name
74 , x_return_status => x_return_status );
75
76 BEGIN
77 SELECT
78 PROJ_ELEMENT_ID, project_id
79 INTO
80 l_struct_element_id, l_project_id
81 FROM
82 pa_proj_element_versions
83 WHERE
84 ELEMENT_VERSION_ID = p_wbs_version_id;
85 EXCEPTION
86 WHEN NO_DATA_FOUND THEN
87 NULL;
88 END;
89
90
91
92 -- OLAP START
93
94
95 ppr_log( ' maintain smart l_pjt_rollup_enabled_flag '||l_pjt_rollup_enabled_flag);
96
97 ppr_log( ' maintain smart p_plan_version_id_tbl.COUNT '||p_plan_version_id_tbl.COUNT);
98
99 ppr_log( ' maintain smart p_wbs_element_id '||p_wbs_element_id);
100 ppr_log( ' maintain smart p_rbs_element_id '||p_rbs_element_id);
101 ppr_log( ' maintain smart l_struct_element_id '||l_struct_element_id);
102
103
104
105
106 FOR i IN 1..p_plan_version_id_tbl.COUNT LOOP
107
108
109 ppr_log( ' maintain smart p_plan_version_id_tbl(i) '||p_plan_version_id_tbl(i));
110
111
112 if ( p_plan_version_id_tbl(i) <> -1 ) then
113 begin
114
115 select fpo.rbs_version_id , p.cbs_version_id
116 into l_new_rbs_version_id , l_cbs_version_id
117 from pa_budget_versions b , pa_proj_fp_options fpo, pa_projects_all p
118 where b.budget_version_id =p_plan_version_id_tbl(i)
119 AND b.fin_plan_type_id = fpo.fin_plan_type_id
120 AND b.budget_version_id = fpo.fin_plan_version_id
121 AND b.project_id = fpo.project_id
122 and b.project_id = p.project_id
123 AND fpo.fin_plan_option_level_code = 'PLAN_VERSION' ;
124
125 ppr_log( ' maintain smart l_new_rbs_version_id '||l_new_rbs_version_id);
126
127 ppr_log( ' p_fact_slice '||p_fact_slice);
128
129 if ( p_fact_slice = 'CBS_TO_WBS' ) then
130
131 PA_ppr_rollup_PVT.plan_rollup(
132 p_commit => 'N'
133 ,p_calling_mode => 'CREATE'
134 ,p_fact_slice => 'CBS_TO_WBS'
135 ,p_debug_mode => 'N'
136 ,p_project_id => l_project_id
137 ,p_budget_version_id => p_plan_version_id_tbl(i)
138 ,p_rbs_version_id => -1
139 ,p_cbs_version_id => l_cbs_version_id
140 ,p_proj_element_id => -1
141 ,p_rbs_element_id => -1
142 ,p_cbs_element_id => p_wbs_element_id
143 ,x_return_status => l_new_status
144 ,x_msg_count => l_new_msg_count
145 ,x_msg_data => l_new_msg_data
146 ) ;
147
148 end if;
149
150
151 if ( p_fact_slice = 'CBS_TO_RBS' ) then
152
153 PA_ppr_rollup_PVT.plan_rollup(
154 p_commit => 'N'
155 ,p_calling_mode => 'CREATE'
156 ,p_fact_slice => 'CBS_TO_RBS'
157 ,p_debug_mode => 'N'
158 ,p_project_id => l_project_id
159 ,p_budget_version_id => p_plan_version_id_tbl(i)
160 ,p_rbs_version_id => l_new_rbs_version_id
161 ,p_cbs_version_id => l_cbs_version_id
162 ,p_proj_element_id => -1
163 ,p_rbs_element_id => -1
164 ,p_cbs_element_id => p_wbs_element_id
165 ,x_return_status => l_new_status
166 ,x_msg_count => l_new_msg_count
167 ,x_msg_data => l_new_msg_data
168 ) ;
169
170 end if;
171
172
173 if ( p_fact_slice = 'WBS_TO_RBS' ) then
174
175 PA_ppr_rollup_PVT.plan_rollup(
176 p_commit => 'N'
177 ,p_calling_mode => 'CREATE'
178 ,p_fact_slice => 'WBS_TO_RBS'
179 ,p_debug_mode => 'N'
180 ,p_project_id => l_project_id
181 ,p_budget_version_id => p_plan_version_id_tbl(i)
182 ,p_rbs_version_id => l_new_rbs_version_id
183 ,p_cbs_version_id => -1
184 ,p_proj_element_id => p_wbs_element_id
185 ,p_rbs_element_id => -1
186 ,p_cbs_element_id => -1
187 ,x_return_status => l_new_status
188 ,x_msg_count => l_new_msg_count
189 ,x_msg_data => l_new_msg_data
190 ) ;
191
192 end if;
193
194 if ( p_fact_slice = 'RBS_TO_WBS' ) then
195
196 PA_ppr_rollup_PVT.plan_rollup(
197 p_commit => 'N'
198 ,p_calling_mode => 'CREATE'
199 ,p_fact_slice => 'RBS_TO_WBS'
200 ,p_debug_mode => 'N'
201 ,p_project_id => l_project_id
202 ,p_budget_version_id => p_plan_version_id_tbl(i)
203 ,p_rbs_version_id => l_new_rbs_version_id
204 ,p_cbs_version_id => -1
205 ,p_proj_element_id => -1
206 ,p_rbs_element_id => p_rbs_element_id
207 ,p_cbs_element_id => -1
208 ,x_return_status => l_new_status
209 ,x_msg_count => l_new_msg_count
210 ,x_msg_data => l_new_msg_data
211 ) ;
212
213
214 end if ;
215
216
217
218 if ( p_fact_slice = 'RBS' ) then
219
220 PA_ppr_rollup_PVT.plan_rollup(
221 p_commit => 'N'
222 ,p_calling_mode => 'CREATE'
223 ,p_fact_slice => 'RBS'
224 ,p_debug_mode => 'N'
225 ,p_project_id => l_project_id
226 ,p_budget_version_id => p_plan_version_id_tbl(i)
227 ,p_rbs_version_id => l_new_rbs_version_id
228 ,p_cbs_version_id => -1
229 ,p_proj_element_id => -1
230 ,p_rbs_element_id => -1
231 ,p_cbs_element_id => -1
232 ,x_return_status => l_new_status
233 ,x_msg_count => l_new_msg_count
234 ,x_msg_data => l_new_msg_data
235 ) ;
236
237 end if;
238
239
240 exception
241 when no_data_found then
242 null;
243 when others then
244 raise;
245 end ;
246
247 end if;
248
249 commit;
250
251 END LOOP;
252
253
254
255 IF p_commit = 'Y' THEN
256 COMMIT;
257 END IF;
258
259 IF g_debug_mode='Y' THEN
260 Pji_Utils.write2log(' maintain smart slice .. NEW MODEL END ' ,null,3);
261 end if;
262
263 return ; -- NO NEED TO EXECUTE THE BELOW CODE
264
265
266
267 -- OLAP END
268 end ;
269
270
271 End PA_ppr_smart_slice_PVT;