1 PACKAGE FII_RECONVERSION_PKG AUTHID CURRENT_USER AS
2 /* $Header: FIICRECS.pls 115.6 2003/10/07 18:16:40 phu noship $ */
3
4 --
5 -- PACKAGE
6 -- FII_RECONVERSION_PKG
7 --
8 -- PURPOSE
9 -- This package will cache reconversion rates and use them to convert the
10 -- global amounts of the base tables for AP, AR and/or GL based on the
11 -- passed in parameters
12 -- The concurrent manager will call the procedure reconvert_amounts() of
13 -- this package when processing FII Currency Re-conversion SRS request.
14 --
15 -- HISTORY
16 -- 07/22/03 L Poon Created
17 --
18
19 --
20 -- PROCEDURE
21 -- reconvert_amounts()
22 --
23 -- PARAMETERS:
24 -- errbuf Error message for the concurrent manager
25 -- retcode Return code for the concurrent manager
26 -- p_transaction_type Specify what transactions are to be reconverted (AP,
27 -- AR, GL, or ALL)
28 -- p_currency_type Specify which global currency amounts are to be
29 -- reconverted (PRIMARY, SECONDARY, or ALL)
30 -- p_primary_rate_type The rate type used to reconvert primary global
31 -- amounts
32 -- p_secondary_rate_type The rate type used to reconvert secondary global
33 -- amounts
34 -- p_from_date The reconversion start date string
35 -- p_to_date The reconversion end date string
36 -- p_log_filename The log filename used when not running by SRS
37 -- p_output_filename The output filename used when not running by SRS
38 --
39 -- DESCRIPTION:
40 -- This is the main function of this currency reconversion package. It will
41 -- initialize the variables and validate the passed in parameters from the
42 -- concurrent manager. Then, it will call other procdures to cache rates,
43 -- reconvert global amounts for different products and print execution report.
44 --
45 PROCEDURE reconvert_amounts(
46 errbuf IN OUT NOCOPY VARCHAR2
47 , retcode IN OUT NOCOPY VARCHAR2
48 , p_currency_type IN VARCHAR2
49 , p_primary_rate_type IN VARCHAR2
50 , p_secondary_rate_type IN VARCHAR2
51 , p_from_date IN VARCHAR2
52 , p_to_date IN VARCHAR2
53 , p_transaction_type IN VARCHAR2 DEFAULT 'ALL'
54 , p_log_filename IN VARCHAR2 DEFAULT NULL
55 , p_output_filename IN VARCHAR2 DEFAULT NULL);
56
57
58 --
59 -- FUNCTION
60 -- cache_rates()
61 --
62 -- PARAMETERS:
63 -- p_request_id The currency reconversion request ID
64 -- p_gl_name The product name for GL
65 -- p_ap_name The product name for AP
66 -- p_ar_name The product name for AR
67 -- p_primary_currency The primary global currency code
68 -- p_primary_rate_type The rate type used to cache reconversion rates for
69 -- primary global currency
70 -- p_secondary_currency The secondary global currency code
71 -- p_secondary_rate_type The rate type used to cache reconversion rates for
72 -- secondary global currency
73 -- p_cache_for_gl_flag Indicate if it caches rates to reconvert GL
74 -- p_cache_for_ap_flag Indicate if it caches rates to reconvert AP
75 -- p_cache_for_ar_flag Indicate if it caches rates to reconvert AR
76 -- p_from_date_id The start date to cache rates (in Julian format)
77 -- p_to_date_id The end date to cache rates (in Julian format)
78 --
79 -- DESCRIPTION:
80 -- It will insert global conversion rates (from set of books currencies to
81 -- global currencies) into FII_RECONV_RATES_GT for the specified date range.
82 --
83 -- The return value can be:
84 -- 'C' - all the required rates are cached
85 -- 'M' - there are missing rates
86 -- 'N' - no rates are cached
87 --
88 FUNCTION cache_rates(
89 p_request_id IN NUMBER
90 , p_gl_name IN VARCHAR2
91 , p_ap_name IN VARCHAR2
92 , p_ar_name IN VARCHAR2
93 , p_primary_currency IN VARCHAR2
94 , p_primary_rate_type IN VARCHAR2
95 , p_secondary_currency IN VARCHAR2
96 , p_secondary_rate_type IN VARCHAR2
97 , p_cache_for_gl_flag IN VARCHAR2
98 , p_cache_for_ap_flag IN VARCHAR2
99 , p_cache_for_ar_flag IN VARCHAR2
100 , p_from_date_id IN NUMBER
101 , p_to_date_id IN NUMBER) RETURN VARCHAR2;
102
103
104 -- PROCEDURE
105 -- reconvert_gl()
106 --
107 -- PARAMETERS:
108 -- errbuf Error message for the concurrent manager
109 -- retcode Return code for the concurrent manager
110 -- p_request_id The currency reconversion request ID
111 -- p_user_id The ID for the user who submit this request
112 -- p_product_name The product name for GL
113 -- p_primary_currency The primary global currency code
114 -- p_primary_mau The minimum accountable unit of primary global
115 -- currency
116 -- p_secondary_currency The secondary global currency code
117 -- p_primary_mau The minimum accountable unit of secondary global
118 -- currency
119 -- p_from_date_id The start date to cache rates (in Julian format)
120 -- p_to_date_id The end date to cache rates (in Julian format)
121 -- p_log_filename The log filename used when not running by SRS
122 -- p_output_filename The output filename used when not running by SRS
123 --
124 -- DESCRIPTION:
125 -- It will reconvert global amounts for the GL base table FII_GL_JE_SUMMARY_B.
126 --
127 PROCEDURE reconvert_gl(
128 errbuf IN OUT NOCOPY VARCHAR2
129 , retcode IN OUT NOCOPY VARCHAR2
130 , p_request_id IN NUMBER
131 , p_user_id IN NUMBER
132 , p_product_name IN VARCHAR2
133 , p_primary_currency IN VARCHAR2
134 , p_primary_mau IN NUMBER
135 , p_secondary_currency IN VARCHAR2
136 , p_secondary_mau IN NUMBER
137 , p_from_date_id IN NUMBER
138 , p_to_date_id IN NUMBER
139 , p_log_filename IN VARCHAR2
140 , p_output_filename IN VARCHAR2);
141
142
143 -- PROCEDURE
144 -- reconvert_ap()
145 --
146 -- PARAMETERS:
147 -- errbuf Error message for the concurrent manager
148 -- retcode Return code for the concurrent manager
149 -- p_request_id The currency reconversion request ID
150 -- p_user_id The ID for the user who submit this request
151 -- p_product_name The product name for AP
152 -- p_primary_currency The primary global currency code
153 -- p_primary_mau The minimum accountable unit of primary global
154 -- currency
155 -- p_secondary_currency The secondary global currency code
156 -- p_primary_mau The minimum accountable unit of secondary global
157 -- currency
158 -- p_from_date_id The start date to cache rates (in Julian format)
159 -- p_to_date_id The end date to cache rates (in Julian format)
160 -- p_log_filename The log filename used when not running by SRS
161 -- p_output_filename The output filename used when not running by SRS
162 --
163 -- DESCRIPTION:
164 -- It will reconvert global amounts for the AP base table FII_AP_INV_B.
165 --
166 PROCEDURE reconvert_ap(
167 errbuf IN OUT NOCOPY VARCHAR2
168 , retcode IN OUT NOCOPY VARCHAR2
169 , p_request_id IN NUMBER
170 , p_user_id IN NUMBER
171 , p_product_name IN VARCHAR2
172 , p_primary_currency IN VARCHAR2
173 , p_primary_mau IN NUMBER
174 , p_secondary_currency IN VARCHAR2
175 , p_secondary_mau IN NUMBER
176 , p_from_date_id IN NUMBER
177 , p_to_date_id IN NUMBER
178 , p_log_filename IN VARCHAR2
179 , p_output_filename IN VARCHAR2);
180
181
182 -- PROCEDURE
183 -- reconvert_ar()
184 --
185 -- PARAMETERS:
186 -- errbuf Error message for the concurrent manager
187 -- retcode Return code for the concurrent manager
188 -- p_request_id The currency reconversion request ID
189 -- p_user_id The ID for the user who submit this request
190 -- p_product_name The product name for AR
191 -- p_primary_currency The primary global currency code
192 -- p_primary_mau The minimum accountable unit of primary global
193 -- currency
194 -- p_secondary_currency The secondary global currency code
195 -- p_primary_mau The minimum accountable unit of secondary global
196 -- currency
197 -- p_from_date_id The start date to cache rates (in Julian format)
198 -- p_to_date_id The end date to cache rates (in Julian format)
199 -- p_debug_mode Indicate if it is in debug mode (TRUE or FALSE)
200 -- p_log_filename The log filename used when not running by SRS
201 -- p_output_filename The output filename used when not running by SRS
202 --
203 -- DESCRIPTION:
204 -- It will reconvert global amounts for the AR base table FII_AR_REVENUE_B.
205 --
206 PROCEDURE reconvert_ar(
207 errbuf IN OUT NOCOPY VARCHAR2
208 , retcode IN OUT NOCOPY VARCHAR2
209 , p_request_id IN NUMBER
210 , p_user_id IN NUMBER
211 , p_product_name IN VARCHAR2
212 , p_primary_currency IN VARCHAR2
213 , p_primary_mau IN NUMBER
214 , p_secondary_currency IN VARCHAR2
215 , p_secondary_mau IN NUMBER
216 , p_from_date_id IN NUMBER
217 , p_to_date_id IN NUMBER
218 , p_log_filename IN VARCHAR2
219 , p_output_filename IN VARCHAR2);
220
221
222 -- PROCEDURE
223 -- print_report()
224 --
225 -- PARAMETERS:
226 -- p_request_id The currency reconversion request ID
227 -- p_transaction_type The passed parameter, Transaction Type
228 -- p_currency_type The passed parameter, Currency Type
229 -- p_primary_rate_type The passed parameter, Primary Rate Type
230 -- p_secondary_rate_type The passed parameter, Secondary Rate Type
231 -- p_from_date The passed parameter, From Date
232 -- p_to_date The passed parameter, TO Date
233 -- p_primary_currency The primary global currency code
234 -- p_secondary_currency The secondary global currency code
235 -- p_cache_rate_status The status for caching rates
236 -- p_completion_status The main request's completion status
237 --
238 -- DESCRIPTION:
239 -- It will print the execution report for different modes. For ERROR mode, it
240 -- will list all the missing/invalid rates and proper error messages. For
241 -- SUCCESS mode, it will list all the cached rates and the number of rows
242 -- updated for each base table.
243 --
244 PROCEDURE print_report(
245 p_request_id IN NUMBER
246 , p_transaction_type IN VARCHAR2
247 , p_currency_type IN VARCHAR2
248 , p_primary_rate_type IN VARCHAR2
249 , p_secondary_rate_type IN VARCHAR2
250 , p_from_date IN VARCHAR2
251 , p_to_date IN VARCHAR2
252 , p_primary_currency IN VARCHAR2
253 , p_secondary_currency IN VARCHAR2
254 , p_cache_rate_status IN VARCHAR2
255 , p_completion_status IN VARCHAR2);
256
257
258 -- FUNCTION
259 -- print_report_hdr()
260 --
261 -- PARAMETERS:
262 -- None
263 --
264 -- DESCRIPTION:
265 -- It will print the report header.
266 --
267 PROCEDURE print_report_hdr( p_line_count IN OUT NOCOPY NUMBER
268 , p_page_count IN NUMBER);
269
270
271 -- PROCEDURE
272 -- print_mtable_hdr()
273 --
274 -- PARAMETERS:
275 -- None
276 --
277 -- DESCRIPTION:
278 -- It will print the missing conversion rate table header.
279 --
280 PROCEDURE print_mtable_hdr(p_line_count IN OUT NOCOPY NUMBER);
281
282
283 -- PROCEDURE
284 -- print_ctable_hdr()
285 --
286 -- PARAMETERS:
287 -- None
288 --
289 -- DESCRIPTION:
290 -- It will print the cached conversion rate table header.
291 --
292 PROCEDURE print_ctable_hdr(p_line_count IN OUT NOCOPY NUMBER);
293
294 -- PROCEDURE
295 -- print_sql()
296 --
297 -- PARAMETERS:
298 -- p_sql_desc The short description for that SQL to be print
299 -- p_sql_stmt The SQL string to be print
300 -- p_num_of_lines The number of lines for the SQL string to be print
301 --
302 -- DESCRIPTION:
303 -- It will print the passed SQL statement to log file.
304 --
305 PROCEDURE print_sql( p_sql_desc IN VARCHAR2
306 , p_sql_stmt IN DBMS_SQL.VARCHAR2S
307 , p_num_of_lines IN NUMBER);
308
309
310 -- PROCEDURE
311 -- func_enter()
312 --
313 -- PARAMETERS:
314 -- p_func_name The function name
315 --
316 -- DESCRIPTION:
317 -- It will print some customerized output to log and then call
318 -- FII_MESSAGE.func_enter() to print standard output for entering function
319 --
320 PROCEDURE func_enter(p_func_name IN VARCHAR2);
321
322
323 -- PROCEDURE
324 -- func_succ()
325 --
326 -- PARAMETERS:
327 -- p_func_name The function name
328 --
329 -- DESCRIPTION:
330 -- It will print some customerized output to log and then call
331 -- FII_MESSAGE.func_succ() to print standard output for exiting function
332 -- successfully
333 --
334 PROCEDURE func_succ(p_func_name IN VARCHAR2);
335
336
337 -- PROCEDURE
338 -- func_fail()
339 --
340 -- PARAMETERS:
341 -- p_func_name The function name
342 -- p_debug_step The debug step code
343 -- p_err_msg The additional error message to be print to log
344 --
345 -- DESCRIPTION:
346 -- It will print some additional output/error message to log and then call
347 -- FII_MESSAGE.func_fail() to print standard output for exiting function with
348 -- error
349 --
350 PROCEDURE func_fail( p_func_name IN VARCHAR2
351 , p_debug_step IN VARCHAR2 DEFAULT NULL
352 , p_err_msg IN VARCHAR2 DEFAULT NULL
353 );
354
355 END FII_RECONVERSION_PKG;