[Home] [Help]
PACKAGE BODY: APPS.PJI_CM_UTIL
Source
1 PACKAGE BODY Pji_Cm_Util AS
2 /* $Header: PJIRX17B.pls 120.3 2007/10/24 04:07:28 paljain ship $ */
3 g_debug_mode VARCHAR2(1) :=NVL(Fnd_Profile.value('PA_DEBUG_MODE'), 'N');
4 PROCEDURE Generate_CM_Procedure(
5 x_return_status IN OUT NOCOPY VARCHAR2
6 , x_msg_count IN OUT NOCOPY NUMBER
7 , x_msg_data IN OUT NOCOPY VARCHAR2
8 ) IS
9 l_counter NUMBER :=1;
10 l_declare VARCHAR2(10000);
11 l_stat VARCHAR2(20000);
12 l_char VARCHAR2(1);
13 BEGIN
14
15 IF x_return_status IS NULL THEN
16 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
17 x_msg_count :=0;
18 END IF;
19
20 l_stat := ' CREATE OR REPLACE PACKAGE PJI_CALC_ENGINE AS '
21 || 'PROCEDURE Compute_Fp_Measures('
22 || ' p_seeded_measures SYSTEM.PA_Num_Tbl_Type := SYSTEM.PA_Num_Tbl_Type() '
23 || ', x_custom_measures OUT NOCOPY SYSTEM.PA_Num_Tbl_Type'
24 || ', x_return_status IN OUT NOCOPY VARCHAR2 '
25 || ', x_msg_count IN OUT NOCOPY NUMBER '
26 || ', x_msg_data IN OUT NOCOPY VARCHAR2 '
27 || '); '
28 || ' PROCEDURE Compute_AC_Measures('
29 || ' p_seeded_measures SYSTEM.PA_Num_Tbl_Type := SYSTEM.PA_Num_Tbl_Type() '
30 || ', x_custom_measures OUT NOCOPY SYSTEM.PA_Num_Tbl_Type'
31 || ', x_return_status IN OUT NOCOPY VARCHAR2 '
32 || ', x_msg_count IN OUT NOCOPY NUMBER '
33 || ', x_msg_data IN OUT NOCOPY VARCHAR2 '
34 || ');'
35 || ' END PJI_CALC_ENGINE; ';
36
37 EXECUTE IMMEDIATE l_stat;
38
39 l_stat := ' CREATE OR REPLACE PACKAGE BODY PJI_CALC_ENGINE AS '
40 || Generate_Procedure_String('FP')
41 || Generate_Procedure_String('AC')
42 || ' END PJI_CALC_ENGINE; ';
43
44 COMMIT;
45
46 /* l_counter :=1;
47 WHILE l_counter <= LENGTH(l_stat) LOOP
48 l_char := SUBSTR(l_stat,l_counter,1);
49 IF l_char = ';' OR l_char = ',' THEN
50 DBMS_OUTPUT.Put_line(l_char);
51 ELSE
52 DBMS_OUTPUT.Put(l_char);
53 END IF;
54 l_counter := l_counter+1;
55 END LOOP;
56 */ EXECUTE IMMEDIATE l_stat;
57
58 EXCEPTION
59 WHEN OTHERS THEN
60 x_return_status := Fnd_Api.G_RET_STS_ERROR;
61 x_msg_count := x_msg_count + 1;
62 -- DBMS_OUTPUT.put_line(SQLERRM);
63 RAISE;
64 END;
65
66 FUNCTION Generate_Procedure_String(
67 p_measure_source IN VARCHAR2)
68 RETURN VARCHAR2
69 IS
70
71 l_stat VARCHAR2(20000);
72 l_db_column_name VARCHAR2(80);
73 l_measure_formula VARCHAR2(2000);
74 l_counter NUMBER :=1;
75
76 CURSOR c_custom_measure IS
77 SELECT measure_formula
78 FROM pji_mt_measure_sets_b
79 WHERE measure_source = p_measure_source
80 AND measure_set_type = 'CUSTOM_CALC'
81 ORDER BY measure_set_code;
82
83 CURSOR c_seeded_measure IS
84 SELECT db_column_name
85 FROM pji_mt_measure_sets_b
86 WHERE measure_source = p_measure_source
87 AND db_column_name IS NOT NULL
88 AND measure_set_type = 'SEEDED'
89 AND measure_set_code NOT IN ('PPF_MSR_ACWP')
90 ORDER BY measure_set_code;
91
92 BEGIN
93 l_stat := ' PROCEDURE Compute_'|| p_measure_source || '_Measures('
94 || ' p_seeded_measures SYSTEM.PA_Num_Tbl_Type'
95 || ', x_custom_measures OUT NOCOPY SYSTEM.PA_Num_Tbl_Type'
96 || ', x_return_status IN OUT NOCOPY VARCHAR2 '
97 || ', x_msg_count IN OUT NOCOPY NUMBER '
98 || ', x_msg_data IN OUT NOCOPY VARCHAR2 '
99 || ') IS ';
100
101 l_counter := 1;
102 OPEN c_seeded_measure;
103 LOOP
104 FETCH c_seeded_measure INTO l_db_column_name;
105 EXIT WHEN c_seeded_measure%NOTFOUND;
106 l_stat := l_stat || l_db_column_name || ' NUMBER := p_seeded_measures('|| l_counter ||'); ';
107 l_counter := l_counter + 1;
108 END LOOP;
109 CLOSE c_seeded_measure;
110
111 l_stat := l_stat || ' BEGIN '
112 || ' x_custom_measures := SYSTEM.PA_Num_Tbl_Type(); '
113 || ' x_custom_measures.extend(15); '
114 || ' x_msg_count := 0; '
115 || ' x_return_status := Fnd_Api.G_RET_STS_SUCCESS; ' ;
116
117 l_counter := 1;
118 OPEN c_custom_measure;
119 LOOP
120 FETCH c_custom_measure INTO l_measure_formula;
121 EXIT WHEN c_custom_measure%NOTFOUND;
122 l_stat := l_stat || 'BEGIN x_custom_measures(' || l_counter || ') := ';
123 IF (l_measure_formula IS NOT NULL) AND (LENGTH(l_measure_formula) > 0) THEN
124 l_stat := l_stat || l_measure_formula || ';';--'NVL(' || l_measure_formula || ',0); ';
125 ELSE
126 l_stat := l_stat || ' NULL; ';
127 END IF;
128 l_stat := l_stat || ' EXCEPTION '
129 || ' WHEN ZERO_DIVIDE THEN '
130 || ' x_custom_measures(' || l_counter || ') := 0; '
131 || ' x_return_status := Pji_Rep_Util.G_RET_STS_WARNING; '
132 || ' x_msg_count := x_msg_count + 1; '
133 || ' END; ';
134 l_counter := l_counter + 1;
135 END LOOP;
136 CLOSE c_custom_measure;
137
138 l_stat := l_stat || ' END Compute_'|| p_measure_source || '_Measures; ';
139 RETURN l_stat;
140 END Generate_Procedure_String;
141
142
143
144 PROCEDURE Apply_Measure(p_itd_measure_id IN pji_mt_measures_b.measure_id%TYPE
145 ,p_ptd_measure_id IN pji_mt_measures_b.measure_id%TYPE
146 ,p_itd_name IN pji_mt_measures_tl.name%TYPE
147 ,p_ptd_name IN pji_mt_measures_tl.name%TYPE
148 ,p_qtd_measure_id IN pji_mt_measures_b.measure_id%TYPE
149 ,p_ytd_measure_id IN pji_mt_measures_b.measure_id%TYPE
150 ,p_qtd_name IN pji_mt_measures_tl.name%TYPE
151 ,p_ytd_name IN pji_mt_measures_tl.name%TYPE
152 ,p_measure_set_code IN pji_mt_measures_b.measure_set_code%TYPE
153 ,p_last_update_date IN pji_mt_measures_b.last_update_date%TYPE
154 ,p_last_updated_by IN pji_mt_measures_b.last_updated_by%TYPE
155 ,p_creation_date IN pji_mt_measures_b.creation_date%TYPE
156 ,p_created_by IN pji_mt_measures_b.created_by%TYPE
157 ,p_last_update_Login IN pji_mt_measures_b.last_update_Login%TYPE
158 ,X_return_status OUT NOCOPY VARCHAR2
159 ,X_msg_data OUT NOCOPY VARCHAR2
160 ,X_msg_count OUT NOCOPY NUMBER
161 ) AS
162 l_return_status VARCHAR2(1) :=Fnd_Api.G_RET_STS_SUCCESS;
163 l_msg_count NUMBER :=0;
164 l_msg_data VARCHAR2(200);
165 l_itd_measure_id NUMBER;
166 l_ptd_measure_id NUMBER ;
167 l_qtd_measure_id NUMBER;
168 l_ytd_measure_id NUMBER ;
169 l_measure_set_code VARCHAR2(300):=p_measure_set_code;
170 l_msg_index_out NUMBER;
171 l_data VARCHAR2(200);
172 l_rowid ROWID;
173 BEGIN
174 X_return_status :=Fnd_Api.G_RET_STS_SUCCESS;
175 IF p_itd_measure_id IS NOT NULL THEN
176 IF p_itd_name IS NOT NULL THEN
177 UPDATE Pji_Mt_Measures_Tl
178 SET Name = p_itd_name,
179 Last_Update_Date =p_last_update_date,
180 Last_Updated_By = p_last_updated_by,
181 Last_Update_Login = p_last_update_login,
182 Source_Lang = USERENV('Lang')
183 WHERE Measure_Id = p_itd_measure_id
184 AND USERENV('Lang') IN (LANGUAGE, Source_Lang);
185 IF (SQL%NOTFOUND) THEN
186 IF g_debug_mode = 'Y' THEN
187 Pa_Debug.g_err_stage:= 'while updating pji_mt_measure_tl'||TO_CHAR(p_itd_measure_id);
188 Pa_Debug.WRITE('Apply_changes',Pa_Debug.g_err_stage, Pa_Fp_Constants_Pkg.g_debug_level5);
189 END IF;
190 RAISE NO_DATA_FOUND;
191 END IF;
192
193 ELSE
194 NULL;
195 /* Commented out ,if delete functionality is added it can be uncommented
196 delete from PJI_MT_MEASURES_TL
197 where measure_id = l_itd_measure_id;
198
199 delete from PJI_MT_MEASURES_B
200 where measure_id = l_itd_measure_id;
201 */
202 -- PJI_MT_MEASURES_PKG.DELETE_ROW (p_measure_id =>l_itd_measure_id);
203 END IF;
204
205 ELSE
206 IF p_itd_name IS NOT NULL THEN
207
208 Pji_Mt_Measures_Pkg.INSERT_ROW(
209 X_rowid =>l_rowid,
210 X_measure_id =>l_itd_measure_id ,
211 X_measure_set_code =>p_measure_set_code,
212 X_measure_code =>l_measure_set_code||'_ITD',
213 X_xtd_type =>'ITD',
214 X_pl_sql_api =>NULL,
215 X_object_version_number =>1,
216 X_name =>p_itd_name,
217 X_description =>p_itd_name,
218 X_last_update_date =>p_last_update_date,
219 X_last_updated_by =>p_last_updated_by,
220 X_creation_date =>p_creation_date,
221 X_created_by =>p_created_by,
222 X_last_update_Login =>p_last_update_Login,
223 X_return_status =>l_return_status,
224 X_msg_data =>l_msg_data,
225 X_msg_count =>l_msg_count);
226
227 END IF;
228 END IF;
229 IF p_ptd_measure_id IS NOT NULL THEN
230 IF p_ptd_name IS NOT NULL THEN
231
232 UPDATE Pji_Mt_Measures_Tl
233 SET Name = p_ptd_name,
234 Last_Update_Date =p_last_update_date,
235 Last_Updated_By = p_last_updated_by,
236 Last_Update_Login = p_last_update_login,
237 Source_Lang = USERENV('Lang')
238 WHERE Measure_Id = p_ptd_measure_id
239 AND USERENV('Lang') IN (LANGUAGE, Source_Lang);
240
241 IF (SQL%NOTFOUND) THEN
242 IF g_debug_mode = 'Y' THEN
243 Pa_Debug.g_err_stage:= 'while updating pji_mt_measure_tl'||TO_CHAR(p_ptd_measure_id);
244 Pa_Debug.WRITE('pji_cm_util.Apply_changes',Pa_Debug.g_err_stage, Pa_Fp_Constants_Pkg.g_debug_level5);
245 END IF;
246 RAISE NO_DATA_FOUND;
247 END IF;
248 ELSE
249 NULL;
250 /* Commented out ,if delete functionality is added it can be uncommented
251 delete from PJI_MT_MEASURES_TL
252 where measure_id = l_ptd_measure_id;
253
254 delete from PJI_MT_MEASURES_B
255 where measure_id = l_ptd_measure_id;
256 */
257 -- PJI_MT_MEASURES_PKG.DELETE_ROW (p_measure_id =>l_ptd_measure_id);
258 END IF;
259
260 ELSE
261 IF p_ptd_name IS NOT NULL THEN
262
263 Pji_Mt_Measures_Pkg.INSERT_ROW(
264 X_rowid =>l_rowid,
265 X_measure_id =>l_ptd_measure_id ,
266 X_measure_set_code =>p_measure_set_code,
267 X_measure_code =>l_measure_set_code||'_PTD',
268 X_xtd_type =>'PTD',
269 X_pl_sql_api =>NULL,
270 X_object_version_number =>1,
271 X_name =>p_ptd_name,
272 X_description =>p_ptd_name,
273 X_last_update_date =>p_last_update_date,
274 X_last_updated_by =>p_last_updated_by,
275 X_creation_date =>p_creation_date,
276 X_created_by =>p_created_by,
277 X_last_update_Login =>p_last_update_Login,
278 X_return_status =>l_return_status,
279 X_msg_data =>l_msg_data,
280 X_msg_count =>l_msg_count);
281 END IF;
282 END IF;
283
284
285 IF p_qtd_measure_id IS NOT NULL THEN
286 IF p_qtd_name IS NOT NULL THEN
287 UPDATE Pji_Mt_Measures_Tl
288 SET Name = p_qtd_name,
289 Last_Update_Date =p_last_update_date,
290 Last_Updated_By = p_last_updated_by,
291 Last_Update_Login = p_last_update_login,
292 Source_Lang = USERENV('Lang')
293 WHERE Measure_Id = p_qtd_measure_id
294 AND USERENV('Lang') IN (LANGUAGE, Source_Lang);
295 IF (SQL%NOTFOUND) THEN
296 IF g_debug_mode = 'Y' THEN
297 Pa_Debug.g_err_stage:= 'while updating pji_mt_measure_tl'||TO_CHAR(p_qtd_measure_id);
298 Pa_Debug.WRITE('Apply_changes',Pa_Debug.g_err_stage, Pa_Fp_Constants_Pkg.g_debug_level5);
299 END IF;
300 RAISE NO_DATA_FOUND;
301 END IF;
302
303 ELSE
304 NULL;
305 END IF;
306
307 ELSE
308 IF p_qtd_name IS NOT NULL THEN
309
310 Pji_Mt_Measures_Pkg.INSERT_ROW(
311 X_rowid =>l_rowid,
312 X_measure_id =>l_qtd_measure_id ,
313 X_measure_set_code =>p_measure_set_code,
314 X_measure_code =>l_measure_set_code||'_QTD',
315 X_xtd_type =>'QTD',
316 X_pl_sql_api =>NULL,
317 X_object_version_number =>1,
318 X_name =>p_qtd_name,
319 X_description =>p_qtd_name,
320 X_last_update_date =>p_last_update_date,
321 X_last_updated_by =>p_last_updated_by,
322 X_creation_date =>p_creation_date,
323 X_created_by =>p_created_by,
324 X_last_update_Login =>p_last_update_Login,
325 X_return_status =>l_return_status,
326 X_msg_data =>l_msg_data,
327 X_msg_count =>l_msg_count);
328
329 END IF;
330 END IF;
331
332
333 IF p_ytd_measure_id IS NOT NULL THEN
334 IF p_ytd_name IS NOT NULL THEN
335 UPDATE Pji_Mt_Measures_Tl
336 SET Name = p_ytd_name,
337 Last_Update_Date =p_last_update_date,
338 Last_Updated_By = p_last_updated_by,
339 Last_Update_Login = p_last_update_login,
340 Source_Lang = USERENV('Lang')
341 WHERE Measure_Id = p_ytd_measure_id
342 AND USERENV('Lang') IN (LANGUAGE, Source_Lang);
343 IF (SQL%NOTFOUND) THEN
344 IF g_debug_mode = 'Y' THEN
345 Pa_Debug.g_err_stage:= 'while updating pji_mt_measure_tl'||TO_CHAR(p_ytd_measure_id);
346 Pa_Debug.WRITE('Apply_changes',Pa_Debug.g_err_stage, Pa_Fp_Constants_Pkg.g_debug_level5);
347 END IF;
348 RAISE NO_DATA_FOUND;
349 END IF;
350
351 ELSE
352 NULL;
353 END IF;
354
355 ELSE
356 IF p_ytd_name IS NOT NULL THEN
357
358 Pji_Mt_Measures_Pkg.INSERT_ROW(
359 X_rowid =>l_rowid,
360 X_measure_id =>l_ytd_measure_id ,
361 X_measure_set_code =>p_measure_set_code,
362 X_measure_code =>l_measure_set_code||'_YTD',
363 X_xtd_type =>'YTD',
364 X_pl_sql_api =>NULL,
365 X_object_version_number =>1,
366 X_name =>p_ytd_name,
367 X_description =>p_ytd_name,
368 X_last_update_date =>p_last_update_date,
369 X_last_updated_by =>p_last_updated_by,
370 X_creation_date =>p_creation_date,
371 X_created_by =>p_created_by,
372 X_last_update_Login =>p_last_update_Login,
373 X_return_status =>l_return_status,
374 X_msg_data =>l_msg_data,
375 X_msg_count =>l_msg_count);
376
377 END IF;
378 END IF;
379
380 EXCEPTION
381 WHEN NO_DATA_FOUND THEN
382
383 x_return_status := Fnd_Api.G_RET_STS_ERROR;
384 l_msg_count := Fnd_Msg_Pub.count_msg;
385
386 IF l_msg_count = 1 AND x_msg_data IS NULL THEN
387 Pa_Interface_Utils_Pub.get_messages
388 (p_encoded => Fnd_Api.G_TRUE
389 ,p_msg_index => 1
390 ,p_msg_count => l_msg_count
391 ,p_msg_data => l_msg_data
392 ,p_data => l_data
393 ,p_msg_index_out => l_msg_index_out);
394 x_msg_data := l_data;
395 x_msg_count := l_msg_count;
396 ELSE
397 x_msg_count := l_msg_count;
398 END IF;
399
400 IF g_debug_mode = 'Y' THEN
401 Pa_Debug.reset_curr_function;
402 END IF;
403
404 RAISE NO_DATA_FOUND;
405
406 WHEN OTHERS THEN
407
408 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
409 x_msg_count := 1;
410 x_msg_data := SQLERRM;
411
412 Fnd_Msg_Pub.add_exc_msg
413 ( p_pkg_name => 'PJI_CM_UTIL'
414 ,p_procedure_name => 'Apply_Measure'
415 ,p_error_text => x_msg_data);
416
417 IF g_debug_mode = 'Y' THEN
418 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
419 Pa_Debug.WRITE('pji_cm_util.apply_changes',Pa_Debug.g_err_stage,
420 Pa_Fp_Constants_Pkg.g_debug_level5);
421 Pa_Debug.reset_curr_function;
422 END IF;
423 END Apply_Measure;
424
425
426
427
428
429 END Pji_Cm_Util;