[Home] [Help]
PACKAGE BODY: APPS.PJI_PJP_FP_CURR_WRAP
Source
1 PACKAGE BODY PJI_PJP_FP_CURR_WRAP AS
2 /* $Header: PJIPUT2B.pls 120.3 2006/03/08 00:15:11 appldev noship $ */
3
4 /* Notes to developer:
5 Rename
6 1. pji_fp_xbs_accum_f_2 to pji_fp_xbs_accum_f.
7 2. pji_fp_xbs_accum_f_1 to pji_fp_aggr_pjp1_tmp.
8 3. Remove all references to dblink on pjdev115.
9 */
10
11 g_package_name VARCHAR2(100) := 'PJI_PJP_FP_CURR_WRAP';
12
13 -----------------------------------------------------------------------
14 ----- Misc apis.. wrappers for apis to be provided by Shane -----------
15 -----------------------------------------------------------------------
16
17 function GET_GLOBAL1_CURR_CODE RETURN VARCHAR2 IS
18 BEGIN
19 RETURN PJI_UTILS.GET_GLOBAL_PRIMARY_CURRENCY;
20 END;
21
22
23 function GET_GLOBAL2_CURR_CODE RETURN VARCHAR2 IS
24 BEGIN
25 RETURN PJI_UTILS.GET_GLOBAL_SECONDARY_CURRENCY;
26 END;
27
28
29 function GET_GLOBAL_RATE_PRIMARY(p_FROM_currency_code VARCHAR2, p_exchange_date date)
30 return NUMBER IS
31 BEGIN
32 RETURN PJI_UTILS.get_global_rate_primary(
33 p_from_currency_code => p_FROM_currency_code,
34 p_exchange_date => p_exchange_date
35 );
36 END;
37
38
39 function GET_MAU_PRIMARY return NUMBER IS
40 BEGIN
41 RETURN PJI_UTILS.GET_MAU_PRIMARY;
42 END;
43
44
45 function GET_GLOBAL_RATE_SECONDARY (p_FROM_currency_code VARCHAR2, p_exchange_date DATE)
46 return NUMBER IS
47 BEGIN
48 RETURN PJI_UTILS.get_global_rate_secondary(
49 p_from_currency_code => p_FROM_currency_code,
50 p_exchange_date => p_exchange_date
51 );
52 END;
53
54
55 function GET_MAU_SECONDARY return NUMBER IS
56 BEGIN
57 RETURN PJI_UTILS.GET_MAU_SECONDARY;
58 END;
59
60
61 function GET_RATE(p_FROM_currency_code VARCHAR2, p_to_currency_code VARCHAR2, p_exchange_date date) return NUMBER IS
62 BEGIN
63 RETURN 0.01;
64 END;
65
66
67 function GET_MAU (p_currency_code VARCHAR2) return NUMBER IS
68 BEGIN
69 RETURN 0.01;
70 END;
71
72
73 FUNCTION GET_WORKER_ID RETURN NUMBER IS
74 l_worker_id NUMBER;
75 l_return_status VARCHAR2(100) := NULL;
76 BEGIN
77
78 INIT_ERR_STACK
79 ( p_package_name => g_package_name
80 , x_return_status => l_return_status );
81
82 BEGIN
83 l_worker_id := PJI_PJP_EXTRACTION_UTILS.GET_WORKER_ID;
84 EXCEPTION
85 WHEN OTHERS
86 THEN l_worker_id := 1;
87 END;
88
89 RETURN l_worker_id;
90
91 EXCEPTION
92 WHEN OTHERS THEN
93 EXCP_HANDLER
94 ( p_package_name => g_package_name
95 , p_procedure_name => 'get_ent_dates_info'
96 , x_return_status => l_return_status ) ;
97
98 RAISE;
99 END;
100
101
102 PROCEDURE get_ent_dates_info (
103 x_global_start_date OUT NOCOPY DATE
104 , x_ent_start_period_id OUT NOCOPY NUMBER
105 , x_ent_start_period_name OUT NOCOPY VARCHAR2
106 , x_ent_start_date OUT NOCOPY DATE
107 , x_ent_END_date OUT NOCOPY DATE
108 , x_global_start_J OUT NOCOPY VARCHAR2
109 , x_ent_start_J OUT NOCOPY VARCHAR2
110 , x_ent_END_J OUT NOCOPY VARCHAR2
111 ) IS
112 l_return_status VARCHAR2(100) := NULL;
113 BEGIN
114
115 INIT_ERR_STACK
116 ( p_package_name => g_package_name
117 , x_return_status => l_return_status );
118
119 x_global_start_date := pji_utils.GET_EXTRACTION_START_DATE;
120 -- TO_DATE(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE') , PJI_FM_SUM_MAIN.g_date_mask);
121
122 BEGIN
123 SELECT ent_period_id,name,start_date,END_date
124 INTO x_ent_start_period_id, x_ent_start_period_name, x_ent_start_date, x_ent_END_date
125 FROM pji_time_ent_period
126 WHERE x_global_start_date BETWEEN start_date AND END_date;
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 x_ent_start_period_id := NULL;
130 x_ent_start_period_name := NULL;
131 x_ent_start_date := NULL;
132 x_ent_END_date := NULL;
133 END;
134
135 x_global_start_J := to_char(x_global_start_date,'J');
136 x_ent_start_J := to_char(x_ent_start_date,'J');
137 x_ent_END_J := to_char(x_ent_END_date,'J');
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 EXCP_HANDLER
142 ( p_package_name => g_package_name
143 , p_procedure_name => 'get_ent_dates_info'
144 , x_return_status => l_return_status ) ;
145
146 RAISE;
147 END;
148
149
150 PROCEDURE get_global_currency_info (
151 x_currency_conversion_rule OUT NOCOPY VARCHAR2
152 , x_prorating_format OUT NOCOPY VARCHAR2
153 , x_global1_currency_code OUT NOCOPY VARCHAR2
154 , x_global2_currency_code OUT NOCOPY VARCHAR2
155 , x_global1_currency_mau OUT NOCOPY NUMBER
156 , x_global2_currency_mau OUT NOCOPY NUMBER ) IS
157
158 l_return_status VARCHAR2(100) := NULL;
159
160 BEGIN
161
162 INIT_ERR_STACK
163 ( p_package_name => g_package_name
164 , x_return_status => l_return_status );
165
166
167 BEGIN
168 x_global1_currency_mau := GET_MAU_PRIMARY;
169 x_global2_currency_mau := GET_MAU_SECONDARY;
170 EXCEPTION
171 WHEN OTHERS THEN
172 EXCP_HANDLER
173 ( p_package_name => g_package_name
174 , p_procedure_name => 'get_global_currency_mau '
175 , x_return_status => l_return_status ) ;
176 RAISE;
177 END;
178
179 BEGIN
180 SELECT DECODE (planamt_alloc_method
181 , 'PERIOD_START', 'S'
182 , 'PERIOD_END', 'E'
183 , 'DAILY', 'D'
184 , 'X')
185 INTO x_prorating_format
186 FROM pji_system_settings;
187
188 IF (x_prorating_format = 'X') THEN
189 fnd_message.set_name('PJI', 'PJI_PLNAMTALLOC_MISSING');
190 dbms_standard.raise_application_error(-20000, fnd_message.get);
191 END IF;
192
193 EXCEPTION
194 WHEN OTHERS THEN
195 fnd_message.set_name('PJI', 'PJI_PLNAMTALLOC_MISSING');
196 dbms_standard.raise_application_error(-20000, fnd_message.get);
197 END;
198
199
200 BEGIN
201 SELECT DECODE ( planamt_conv_date
202 , 'FIRST_DAY', 'S'
203 , 'LAST_DAY', 'E'
204 , 'X')
205 INTO x_currency_conversion_rule
206 FROM pji_system_settings;
207
208 IF (x_currency_conversion_rule = 'X') THEN
209 fnd_message.set_name('PJI', 'PJI_PLNAMTCONV_MISSING');
210 dbms_standard.raise_application_error(-20000, fnd_message.get);
211 END IF;
212
213 EXCEPTION
214 WHEN OTHERS THEN
215 fnd_message.set_name('PJI', 'PJI_PLNAMTCONV_MISSING');
216 dbms_standard.raise_application_error(-20000, fnd_message.get);
217 END;
218
219
220 BEGIN
221 x_global1_currency_code := PJI_UTILS.GET_GLOBAL_PRIMARY_CURRENCY;
222 x_global2_currency_code := PJI_UTILS.GET_GLOBAL_SECONDARY_CURRENCY;
223 EXCEPTION
224 WHEN OTHERS THEN RAISE;
225 END;
226
227 EXCEPTION
228 WHEN OTHERS THEN
229 EXCP_HANDLER
230 ( p_package_name => g_package_name
231 , p_procedure_name => 'get_global_currency_info '
232 , x_return_status => l_return_status ) ;
233
234 RAISE;
235 END;
236
237
238 PROCEDURE PRINT_TIME(p_tag IN VARCHAR2) IS
239 l_tag VARCHAR2(500) := p_tag || ' ' || to_char(sysdate, 'HH:MI:SS' ) || ' sid ' || userenv('SESSIONID') ;
240 BEGIN
241 -- dbms_output.put_line(l_tag);
242 -- hr_utility.trace(l_tag);
243 PJI_UTILS.WRITE2LOG(p_msg => l_tag);
244 EXCEPTION
245 WHEN OTHERS THEN
246 FND_MSG_PUB.add_exc_msg( p_pkg_name => g_package_name ,
247 p_procedure_name => 'PRINT_TIME');
248 NULL;
249 END;
250
251
252 PROCEDURE EXCP_HANDLER
253 ( p_context IN VARCHAR2 := 'ERR'
254 , p_package_name IN VARCHAR2 := NULL
255 , p_procedure_name IN VARCHAR2 := NULL
256 -- , x_processing_code OUT NOCOPY VARCHAR2
257 , x_return_status OUT NOCOPY VARCHAR2) IS
258 BEGIN
259
260 IF ( p_context = 'ERR' ) THEN
261 print_time('Error in pkg ' || p_package_name || ' procedure ' || p_procedure_name || ' is: ' || SQLERRM );
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263 -- x_processing_code := FND_API.G_RET_STS_UNEXP_ERROR;
264 -- pa_debug.RESET_ERR_STACK;
265 FND_MSG_PUB.add_exc_msg( p_pkg_name => p_package_name ,
266 p_procedure_name => p_procedure_name);
267 ELSIF ( p_context = 'START' ) THEN
268 pa_debug.RESET_ERR_STACK;
269 x_return_status := FND_API.G_RET_STS_SUCCESS;
270 -- x_processing_code := FND_API.G_RET_STS_SUCCESS;
271 print_time('Start... ' || p_package_name || ' procedure ' || p_procedure_name );
272 ELSE
273 NULL;
274 END IF;
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 RAISE;
279 END;
280
281
282 PROCEDURE INIT_ERR_STACK
283 ( p_package_name IN VARCHAR2 := NULL
284 , x_return_status OUT NOCOPY VARCHAR2) IS
285 BEGIN
286
287 -- print_time('Entering pkg ' || p_package_name || ' procedure ' || p_procedure_name || ' is: ' || SQLERRM );
288 x_return_status := FND_API.G_RET_STS_SUCCESS;
289 pa_debug.RESET_ERR_STACK;
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 RAISE;
294 END;
295
296
297 PROCEDURE SET_TABLE_STATS(
298 p_own_name IN VARCHAR2
299 , p_tab_name IN VARCHAR2
300 , p_num_rows IN NUMBER
301 , p_num_blks IN NUMBER
302 , p_avg_r_len IN NUMBER
303 ) IS
304
305 l_own_name VARCHAR2(10) := NULL;
306 PRAGMA AUTONOMOUS_TRANSACTION;
307
308 BEGIN
309
310 /*
311 print_time('p_own_name ' || p_own_name);
312 print_time('p_tab_name ' || p_tab_name);
313 print_time('p_num_rows ' || p_num_rows);
314 print_time('p_num_blks ' || p_num_blks);
315 print_time('p_avg_r_len ' || p_avg_r_len);
316 */
317
318 SELECT NVL(p_own_name, PJI_UTILS.GET_PJI_SCHEMA_NAME)
319 INTO l_own_name
320 FROM DUAL;
321
322 -- print_time('l_own_name ' || l_own_name);
323
324 FND_STATS.SET_TABLE_STATS (
325 ownname => l_own_name
326 , tabname => UPPER(p_tab_name)
327 , numrows => p_num_Rows
328 , numblks => p_num_blks
329 , avgrlen => p_Avg_r_len
330 );
331
332 COMMIT;
333
334 END;
335
336
337 END PJI_PJP_FP_CURR_WRAP;