[Home] [Help]
PACKAGE BODY: APPS.GCS_TRANS_DYN_BUILD_PKG
Source
1 PACKAGE BODY GCS_TRANS_DYN_BUILD_PKG AS
2 /* $Header: gcsxltdb.pls 120.7 2007/06/29 11:45:31 vkosuri noship $ */
3
4 --
5 -- Private Exceptions
6 --
7 GCS_CCY_APPLSYS_NOT_FOUND EXCEPTION;
8 GCS_CCY_DYN_PKG_BUILD_ERR EXCEPTION;
9
10 --
11 -- Private Global Variables
12 --
13 -- The API name
14 g_api CONSTANT VARCHAR2(50) := 'gcs.plsql.GCS_TRANS_DYN_BUILD_PKG';
15
16
17 -- Action types for writing module information to the log file. Used for
18 -- the procedure log_file_module_write.
19 g_module_enter CONSTANT VARCHAR2(2) := '>>';
20 g_module_success CONSTANT VARCHAR2(2) := '<<';
21 g_module_failure CONSTANT VARCHAR2(2) := '<x';
22
23 -- For holding error text
24 g_error_text VARCHAR2(32767);
25
26 -- Newline character
27 g_nl CONSTANT VARCHAR2(1) := '
28 ';
29
30 --
31 -- Procedure
32 -- Module_Log_Write
33 -- Purpose
34 -- Write the procedure or function entered or exited, and the time that
35 -- this happened. Write it to the log repository.
36 -- Arguments
37 -- p_module Name of the module
38 -- p_action_type Entered, Exited Successfully, or Exited with Failure
39 -- Example
40 -- GCS_TRANS_DYN_BUILD_PKG.Module_Log_Write
41 -- Notes
42 --
43 PROCEDURE Module_Log_Write
44 (p_module VARCHAR2,
45 p_action_type VARCHAR2) IS
46 BEGIN
47 -- Only print if the log level is set at the appropriate level
48 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
49 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, g_api || '.' || p_module,
50 p_action_type || ' ' || p_module || '() ' ||
51 to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
52 END IF;
53 FND_FILE.PUT_LINE(FND_FILE.LOG, p_action_type || ' ' || p_module ||
54 '() ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
55 END Module_Log_Write;
56
57 --
58 -- Procedure
59 -- Write_To_Log
60 -- Purpose
61 -- Write the text given to the log in 3500 character increments
62 -- this happened. Write it to the log repository.
63 -- Arguments
64 -- p_module Name of the module
65 -- p_level Logging level
66 -- p_text Text to write
67 -- Example
68 -- GCS_TRANSLATION_PKG.Write_To_Log
69 -- Notes
70 --
71 PROCEDURE Write_To_Log
72 (p_module VARCHAR2,
73 p_level NUMBER,
74 p_text VARCHAR2)
75 IS
76 api_module_concat VARCHAR2(200);
77 text_with_date VARCHAR2(32767);
78 text_with_date_len NUMBER;
79 curr_index NUMBER;
80 BEGIN
81 -- Only print if the log level is set at the appropriate level
82 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= p_level THEN
83 api_module_concat := g_api || '.' || p_module;
84 text_with_date := to_char(sysdate,'DD-MON-YYYY HH:MI:SS')||g_nl||p_text;
85 text_with_date_len := length(text_with_date);
86 curr_index := 1;
87 WHILE curr_index <= text_with_date_len LOOP
88 fnd_log.string(p_level, api_module_concat,
89 substr(text_with_date, curr_index, 3500));
90 curr_index := curr_index + 3500;
91 END LOOP;
92 END IF;
93 END Write_To_Log;
94
95
96 -- Bugfix 5707630: Added two extra argument to the procedure. Dimension is added
97 -- only when its enabled for historical rates (for historical rate).
98 --
99 -- Procedure
100 -- Build_Dimension_Row
101 -- Purpose
102 -- Build one row of the comma or join list in ad_ddl.
103 -- Arguments
104 -- p_item The item to write if the dimension is used
105 -- p_def_item The item to write if the dimension is unused
106 -- p_rownum The row number to use for ad_ddl
107 -- p_dim_req Whether or not the dimension is required
108 -- p_hrate_dim_req Whether or not the dimension is required for Historicla Rates
109 -- p_key The processing key type - 'H' - Historical rate, 'F' - FCH Processing key
110 -- Example
111 -- GCS_TRANS_DYN_BUILD_PKG.Build_Dimension_Row
112 -- Notes
113 --
114 PROCEDURE Build_Dimension_Row(
115 p_item VARCHAR2,
116 p_def_item VARCHAR2,
117 p_rownum NUMBER,
118 p_dim_req VARCHAR2,
119 p_hrate_dim_req VARCHAR2,
120 p_key VARCHAR2) IS
121 BEGIN
122 IF p_key = 'F' THEN
123 IF p_dim_req = 'Y' THEN
124 ad_ddl.build_statement(p_item, p_rownum);
125 ELSE
126 ad_ddl.build_statement(p_def_item, p_rownum);
127 END IF;
128 ELSE
129 IF p_hrate_dim_req <> 'N' THEN
130 ad_ddl.build_statement(p_item, p_rownum);
131 ELSE
132 ad_ddl.build_statement(p_def_item, p_rownum);
133 END IF;
134 END IF;
135 END Build_Dimension_Row;
136
137
138 --
139 -- Public procedures/functions
140 --
141 -- Bugfix 5707630: Added a new argument
142 -- The procedure is now a function as it will return the line number to the
143 -- GCS_DYN_HRATES_BUILD_PKG and GCS_DYN_RE_BUILD_PKG.
144 -- The enabled for historical rates flag and the hrate type is also passed to build_dimension_row.
145 -- Added cttr and ic dimensions as well.
146 --
147 -- Procedure
148 -- Build_Comma_List
149 -- Purpose
150 -- Build a list of the dimensions, delimited by commas. Use null if
151 -- the dimension is not used.
152 -- Arguments
153 -- p_prefix The prefix to put on the dimensions
154 -- p_suffix The suffix to put on the dimensions
155 -- p_null_text The text to be inserted for the null case
156 -- p_first_rownum The first row number to use for ad_ddl
157 -- p_key Whether Historical rates keys or FCH processing keys.
158 -- Example
159 -- GCS_TRANS_DYN_BUILD_PKG.Build_Comma_List
160 -- Notes
161 --
162 FUNCTION Build_Comma_List( p_prefix VARCHAR2,
163 p_suffix VARCHAR2,
164 p_null_text VARCHAR2,
165 p_first_rownum NUMBER,
166 p_key VARCHAR2) RETURN NUMBER IS
167 BEGIN
168 -- Go through each of the optional dimensions, and fill out accordingly
169 build_dimension_row(p_prefix||'COMPANY_COST_CENTER_ORG_ID,'||p_suffix, p_null_text, p_first_rownum, g_cctr_req, g_cctr_hrate_req, p_key);
170 build_dimension_row(p_prefix||'INTERCOMPANY_ID,'||p_suffix, p_null_text, p_first_rownum+1, g_ic_req, g_ic_hrate_req, p_key);
171 build_dimension_row(p_prefix||'FINANCIAL_ELEM_ID,'||p_suffix, p_null_text, p_first_rownum+2, g_fe_req, g_fe_hrate_req, p_key);
172 build_dimension_row(p_prefix||'PRODUCT_ID,'||p_suffix, p_null_text, p_first_rownum+3, g_prd_req, g_prd_hrate_req, p_key);
173 build_dimension_row(p_prefix||'NATURAL_ACCOUNT_ID,'||p_suffix, p_null_text, p_first_rownum+4, g_na_req, g_na_hrate_req, p_key);
174 build_dimension_row(p_prefix||'CHANNEL_ID,'||p_suffix, p_null_text, p_first_rownum+5, g_chl_req, g_chl_hrate_req, p_key);
175 build_dimension_row(p_prefix||'PROJECT_ID,'||p_suffix, p_null_text, p_first_rownum+6, g_prj_req, g_prj_hrate_req, p_key);
176 build_dimension_row(p_prefix||'CUSTOMER_ID,'||p_suffix, p_null_text, p_first_rownum+7, g_cst_req, g_cst_hrate_req, p_key);
177 build_dimension_row(p_prefix||'TASK_ID,'||p_suffix, p_null_text, p_first_rownum+8, g_tsk_req, g_tsk_hrate_req, p_key);
178 build_dimension_row(p_prefix||'USER_DIM1_ID,'||p_suffix, p_null_text, p_first_rownum+9, g_ud1_req, g_ud1_hrate_req, p_key);
179 build_dimension_row(p_prefix||'USER_DIM2_ID,'||p_suffix, p_null_text, p_first_rownum+10, g_ud2_req, g_ud2_hrate_req, p_key);
180 build_dimension_row(p_prefix||'USER_DIM3_ID,'||p_suffix, p_null_text, p_first_rownum+11, g_ud3_req, g_ud3_hrate_req, p_key);
181 build_dimension_row(p_prefix||'USER_DIM4_ID,'||p_suffix, p_null_text, p_first_rownum+12, g_ud4_req, g_ud4_hrate_req, p_key);
182 build_dimension_row(p_prefix||'USER_DIM5_ID,'||p_suffix, p_null_text, p_first_rownum+13, g_ud5_req, g_ud5_hrate_req, p_key);
183 build_dimension_row(p_prefix||'USER_DIM6_ID,'||p_suffix, p_null_text, p_first_rownum+14, g_ud6_req, g_ud6_hrate_req, p_key);
184 build_dimension_row(p_prefix||'USER_DIM7_ID,'||p_suffix, p_null_text, p_first_rownum+15, g_ud7_req, g_ud7_hrate_req, p_key);
185 build_dimension_row(p_prefix||'USER_DIM8_ID,'||p_suffix, p_null_text, p_first_rownum+16, g_ud8_req, g_ud8_hrate_req, p_key);
186 build_dimension_row(p_prefix||'USER_DIM9_ID,'||p_suffix, p_null_text, p_first_rownum+17, g_ud9_req, g_ud9_hrate_req, p_key);
187 build_dimension_row(p_prefix||'USER_DIM10_ID,'||p_suffix, p_null_text, p_first_rownum+18, g_ud10_req, g_ud10_hrate_req, p_key);
188
189 return p_first_rownum + 19;
190 END Build_Comma_List;
191
192
193 -- Bugfix 5707630: Added a new argument.
194 -- The procedure is now a function as it will return the line number to the
195 -- GCS_DYN_HRATES_BUILD_PKG and GCS_DYN_RE_BUILD_PKG. The enabled for historical
196 -- rates flag and the hrate type is also passed to build_dimension_row.
197 -- Added cttr, ic and line item.
198 --
199 -- Procedure
200 -- Build_Join_List
201 -- Purpose
202 -- Build a list of the dimensions, delimited by commas. Use null if
203 -- the dimension is not used.
204 -- Arguments
205 -- p_left The text to put before the left dimension
206 -- p_middle The text to put between the two dimensions
207 -- p_right The text to put after the right dimension
208 -- p_first_rownum The first row number to use for ad_ddl
209 -- p_key Whether Historical rates keys or FCH processing keys.
210 -- Example
211 -- GCS_TRANS_DYN_BUILD_PKG.Build_Join_List
212 -- Notes
213 --
214 FUNCTION Build_Join_List(p_left VARCHAR2,
215 p_middle VARCHAR2,
216 p_right VARCHAR2,
217 p_first_rownum NUMBER,
218 p_key VARCHAR2) RETURN NUMBER IS
219 BEGIN
220 -- Go through each of the optional dimensions, and fill out accordingly
221 build_dimension_row(p_left||'COMPANY_COST_CENTER_ORG_ID'||p_middle||'COMPANY_COST_CENTER_ORG_ID'||p_right, '', p_first_rownum, g_cctr_req, g_cctr_hrate_req, p_key);
222 build_dimension_row(p_left||'INTERCOMPANY_ID'||p_middle||'INTERCOMPANY_ID'||p_right, '', p_first_rownum+1, g_ic_req, g_ic_hrate_req, p_key);
223 build_dimension_row(p_left||'FINANCIAL_ELEM_ID'||p_middle||'FINANCIAL_ELEM_ID'||p_right, '', p_first_rownum+2, g_fe_req, g_fe_hrate_req, p_key);
224 build_dimension_row(p_left||'PRODUCT_ID'||p_middle||'PRODUCT_ID'||p_right, '', p_first_rownum+3, g_prd_req, g_prd_hrate_req, p_key);
225 build_dimension_row(p_left||'NATURAL_ACCOUNT_ID'||p_middle||'NATURAL_ACCOUNT_ID'||p_right, '', p_first_rownum+4, g_na_req, g_na_hrate_req, p_key);
226 build_dimension_row(p_left||'CHANNEL_ID'||p_middle||'CHANNEL_ID'||p_right, '', p_first_rownum+5, g_chl_req, g_chl_hrate_req, p_key);
227 build_dimension_row(p_left||'PROJECT_ID'||p_middle||'PROJECT_ID'||p_right, '', p_first_rownum+6, g_prj_req, g_prj_hrate_req, p_key);
228 build_dimension_row(p_left||'CUSTOMER_ID'||p_middle||'CUSTOMER_ID'||p_right, '', p_first_rownum+7, g_cst_req, g_cst_hrate_req, p_key);
229 build_dimension_row(p_left||'TASK_ID'||p_middle||'TASK_ID'||p_right, '', p_first_rownum+8, g_tsk_req, g_tsk_hrate_req, p_key);
230 build_dimension_row(p_left||'USER_DIM1_ID'||p_middle||'USER_DIM1_ID'||p_right, '', p_first_rownum+9, g_ud1_req, g_ud1_hrate_req, p_key);
231 build_dimension_row(p_left||'USER_DIM2_ID'||p_middle||'USER_DIM2_ID'||p_right, '', p_first_rownum+10, g_ud2_req, g_ud2_hrate_req, p_key);
232 build_dimension_row(p_left||'USER_DIM3_ID'||p_middle||'USER_DIM3_ID'||p_right, '', p_first_rownum+11, g_ud3_req, g_ud3_hrate_req, p_key);
233 build_dimension_row(p_left||'USER_DIM4_ID'||p_middle||'USER_DIM4_ID'||p_right, '', p_first_rownum+12, g_ud4_req, g_ud4_hrate_req, p_key);
234 build_dimension_row(p_left||'USER_DIM5_ID'||p_middle||'USER_DIM5_ID'||p_right, '', p_first_rownum+13, g_ud5_req, g_ud5_hrate_req, p_key);
235 build_dimension_row(p_left||'USER_DIM6_ID'||p_middle||'USER_DIM6_ID'||p_right, '', p_first_rownum+14, g_ud6_req, g_ud6_hrate_req, p_key);
236 build_dimension_row(p_left||'USER_DIM7_ID'||p_middle||'USER_DIM7_ID'||p_right, '', p_first_rownum+15, g_ud7_req, g_ud7_hrate_req, p_key);
237 build_dimension_row(p_left||'USER_DIM8_ID'||p_middle||'USER_DIM8_ID'||p_right, '', p_first_rownum+16, g_ud8_req, g_ud8_hrate_req, p_key);
238 build_dimension_row(p_left||'USER_DIM9_ID'||p_middle||'USER_DIM9_ID'||p_right, '', p_first_rownum+17, g_ud9_req, g_ud9_hrate_req, p_key);
239 build_dimension_row(p_left||'USER_DIM10_ID'||p_middle||'USER_DIM10_ID'||p_right, '', p_first_rownum+18, g_ud10_req, g_ud10_hrate_req, p_key);
240
241 return p_first_rownum + 19;
242 END Build_Join_List;
243
244
245 -- Bugfix 5725759: Added the initilization procedure that will initialize
246 -- the historical rates and fem dimension required variables.
247 PROCEDURE Initialize_Dimensions IS
248
249 BEGIN
250
251 gcs_utility_pkg.init_dimension_info;
252
253 -- Set the global variables determining which dimensions are used
254 -- Bugfix 5707630: Set values for company_cost_center_org_id and intercompany_id.
255 g_cctr_req := gcs_utility_pkg.get_dimension_required('COMPANY_COST_CENTER_ORG_ID');
256 g_ic_req := gcs_utility_pkg.get_dimension_required('INTERCOMPANY_ID');
257 g_fe_req := gcs_utility_pkg.get_dimension_required('FINANCIAL_ELEM_ID');
258 g_prd_req := gcs_utility_pkg.get_dimension_required('PRODUCT_ID');
259 g_na_req := gcs_utility_pkg.get_dimension_required('NATURAL_ACCOUNT_ID');
260 g_chl_req := gcs_utility_pkg.get_dimension_required('CHANNEL_ID');
261 g_prj_req := gcs_utility_pkg.get_dimension_required('PROJECT_ID');
262 g_cst_req := gcs_utility_pkg.get_dimension_required('CUSTOMER_ID');
263 g_tsk_req := gcs_utility_pkg.get_dimension_required('TASK_ID');
264 g_ud1_req := gcs_utility_pkg.get_dimension_required('USER_DIM1_ID');
265 g_ud2_req := gcs_utility_pkg.get_dimension_required('USER_DIM2_ID');
266 g_ud3_req := gcs_utility_pkg.get_dimension_required('USER_DIM3_ID');
267 g_ud4_req := gcs_utility_pkg.get_dimension_required('USER_DIM4_ID');
268 g_ud5_req := gcs_utility_pkg.get_dimension_required('USER_DIM5_ID');
269 g_ud6_req := gcs_utility_pkg.get_dimension_required('USER_DIM6_ID');
270 g_ud7_req := gcs_utility_pkg.get_dimension_required('USER_DIM7_ID');
271 g_ud8_req := gcs_utility_pkg.get_dimension_required('USER_DIM8_ID');
272 g_ud9_req := gcs_utility_pkg.get_dimension_required('USER_DIM9_ID');
273 g_ud10_req := gcs_utility_pkg.get_dimension_required('USER_DIM10_ID');
274
275 -- Bugfix 5707630: For each dimension, set the historical enabled flag value.
276 g_cctr_hrate_req := gcs_utility_pkg.get_hrate_dim_required('COMPANY_COST_CENTER_ORG_ID');
277 g_ic_hrate_req := gcs_utility_pkg.get_hrate_dim_required('INTERCOMPANY_ID');
278 g_fe_hrate_req := gcs_utility_pkg.get_hrate_dim_required('FINANCIAL_ELEM_ID');
279 g_prd_hrate_req := gcs_utility_pkg.get_hrate_dim_required('PRODUCT_ID');
280 g_na_hrate_req := gcs_utility_pkg.get_hrate_dim_required('NATURAL_ACCOUNT_ID');
281 g_chl_hrate_req := gcs_utility_pkg.get_hrate_dim_required('CHANNEL_ID');
282 g_prj_hrate_req := gcs_utility_pkg.get_hrate_dim_required('PROJECT_ID');
283 g_cst_hrate_req := gcs_utility_pkg.get_hrate_dim_required('CUSTOMER_ID');
284 g_tsk_hrate_req := gcs_utility_pkg.get_hrate_dim_required('TASK_ID');
285 g_ud1_hrate_req := gcs_utility_pkg.get_hrate_dim_required('USER_DIM1_ID');
286 g_ud2_hrate_req := gcs_utility_pkg.get_hrate_dim_required('USER_DIM2_ID');
287 g_ud3_hrate_req := gcs_utility_pkg.get_hrate_dim_required('USER_DIM3_ID');
288 g_ud4_hrate_req := gcs_utility_pkg.get_hrate_dim_required('USER_DIM4_ID');
289 g_ud5_hrate_req := gcs_utility_pkg.get_hrate_dim_required('USER_DIM5_ID');
290 g_ud6_hrate_req := gcs_utility_pkg.get_hrate_dim_required('USER_DIM6_ID');
291 g_ud7_hrate_req := gcs_utility_pkg.get_hrate_dim_required('USER_DIM7_ID');
292 g_ud8_hrate_req := gcs_utility_pkg.get_hrate_dim_required('USER_DIM8_ID');
293 g_ud9_hrate_req := gcs_utility_pkg.get_hrate_dim_required('USER_DIM9_ID');
294 g_ud10_hrate_req := gcs_utility_pkg.get_hrate_dim_required('USER_DIM10_ID');
295
296 END Initialize_Dimensions;
300 -- The logic has been split into two packages, one will contains procedures for
297
298
299 -- Bugfix 5707630: The main logic will no longer be present in this package now.
301 -- translation of historical rates and the other will contain the translation
302 -- for retained earnings. This package will create the dynamic generic package
303 -- that will have code to call procedures in the historical rates and the
304 -- retained earnings packages.
305 PROCEDURE Create_Package(
306 x_errbuf OUT NOCOPY VARCHAR2,
307 x_retcode OUT NOCOPY VARCHAR2) IS
308 -- row number to be used in dynamically creating the package
309 r NUMBER := 1;
310
311 err VARCHAR2(2000);
312
313 status VARCHAR2(1);
314 industry VARCHAR2(1);
315 appl VARCHAR2(30);
316
317 module VARCHAR2(30);
318 BEGIN
319 module := 'CREATE_PACKAGE';
320 module_log_write(module, g_module_enter);
321 Initialize_Dimensions;
322
323 -- Get APPLSYS information. Needed for ad_ddl
324 IF NOT fnd_installation.get_app_info('FND', status, industry, appl) THEN
325 raise gcs_ccy_applsys_not_found;
326 END IF;
327
328 -- Create the package body
329 -- Bugfix 5725759: Added function Get_RE_Data_Exists and Initialize_Data_Load_Status procedure
330 ad_ddl.build_statement('CREATE OR REPLACE PACKAGE BODY GCS_TRANS_DYNAMIC_PKG AS', r); r:=r+1;
331 ad_ddl.build_statement(' ', r); r:=r+1;
332 ad_ddl.build_statement(' -- The API name', r); r:=r+1;
333 ad_ddl.build_statement(' g_api VARCHAR2(50) := ''gcs.plsql.GCS_TRANS_DYNAMIC_PKG'';', r); r:=r+1;
334 ad_ddl.build_statement(' ', r); r:=r+1;
335 ad_ddl.build_statement(' -- Action types for writing module information to the log file. Used for', r); r:=r+1;
336 ad_ddl.build_statement(' -- the procedure log_file_module_write.', r); r:=r+1;
337 ad_ddl.build_statement(' g_module_enter VARCHAR2(2) := ''>>'';', r); r:=r+1;
338 ad_ddl.build_statement(' g_module_success VARCHAR2(2) := ''<<'';', r); r:=r+1;
339 ad_ddl.build_statement(' g_module_failure VARCHAR2(2) := ''<x'';', r); r:=r+1;
340 ad_ddl.build_statement(' ', r); r:=r+1;
341 ad_ddl.build_statement(' -- A newline character. Included for convenience when writing long strings.', r); r:=r+1;
342 ad_ddl.build_statement(' g_nl VARCHAR2(1) := ''', r); r:=r+1;
343 ad_ddl.build_statement(''';', r); r:=r+1;
344 ad_ddl.build_statement(' ', r); r:=r+1;
345 ad_ddl.build_statement('--', r); r:=r+1;
346 ad_ddl.build_statement('-- PRIVATE EXCEPTIONS', r); r:=r+1;
347 ad_ddl.build_statement('--', r); r:=r+1;
348 ad_ddl.build_statement(' GCS_CCY_NO_DATA EXCEPTION;', r); r:=r+1;
349 ad_ddl.build_statement(' GCS_CCY_ENTRY_CREATE_FAILED EXCEPTION;', r); r:=r+1;
350 ad_ddl.build_statement(' ', r); r:=r+1;
351 ad_ddl.build_statement('--', r); r:=r+1;
352 ad_ddl.build_statement('-- PRIVATE PROCEDURES/FUNCTIONS', r); r:=r+1;
353 ad_ddl.build_statement('--', r); r:=r+1;
354 ad_ddl.build_statement(' ', r); r:=r+1;
355 ad_ddl.build_statement(' --', r); r:=r+1;
356 ad_ddl.build_statement(' -- Procedure', r); r:=r+1;
357 ad_ddl.build_statement(' -- Module_Log_Write', r); r:=r+1;
358 ad_ddl.build_statement(' -- Purpose', r); r:=r+1;
359 ad_ddl.build_statement(' -- Write the procedure or function entered or exited, and the time that', r); r:=r+1;
360 ad_ddl.build_statement(' -- this happened. Write it to the log repository.', r); r:=r+1;
361 ad_ddl.build_statement(' -- Arguments', r); r:=r+1;
362 ad_ddl.build_statement(' -- p_module Name of the module', r); r:=r+1;
363 ad_ddl.build_statement(' -- p_action_type Entered, Exited Successfully, or Exited with Failure', r); r:=r+1;
364 ad_ddl.build_statement(' -- Example', r); r:=r+1;
365 ad_ddl.build_statement(' -- GCS_TRANSLATION_PKG.Module_Log_Write', r); r:=r+1;
366 ad_ddl.build_statement(' -- Notes', r); r:=r+1;
367 ad_ddl.build_statement(' --', r); r:=r+1;
368 ad_ddl.build_statement(' PROCEDURE Module_Log_Write', r); r:=r+1;
369 ad_ddl.build_statement(' (p_module VARCHAR2,', r); r:=r+1;
370 ad_ddl.build_statement(' p_action_type VARCHAR2) IS', r); r:=r+1;
371 ad_ddl.build_statement(' BEGIN', r); r:=r+1;
372 ad_ddl.build_statement(' -- Only print if the log level is set at the appropriate level', r); r:=r+1;
373 ad_ddl.build_statement(' IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN', r); r:=r+1;
374 ad_ddl.build_statement(' fnd_log.string(FND_LOG.LEVEL_PROCEDURE, g_api || ''.'' || p_module,', r); r:=r+1;
375 ad_ddl.build_statement(' p_action_type || '' '' || p_module || ''() '' ||', r); r:=r+1;
376 ad_ddl.build_statement(' to_char(sysdate, ''DD-MON-YYYY HH:MI:SS''));', r); r:=r+1;
377 ad_ddl.build_statement(' END IF;', r); r:=r+1;
378 ad_ddl.build_statement(' FND_FILE.PUT_LINE(FND_FILE.LOG, p_action_type || '' '' || p_module ||', r); r:=r+1;
379 ad_ddl.build_statement(' ''() '' || to_char(sysdate, ''DD-MON-YYYY HH:MI:SS''));', r); r:=r+1;
380 ad_ddl.build_statement(' END Module_Log_Write;', r); r:=r+1;
381 ad_ddl.build_statement(' ', r); r:=r+1;
382 ad_ddl.build_statement(' --', r); r:=r+1;
383 ad_ddl.build_statement(' -- Procedure', r); r:=r+1;
384 ad_ddl.build_statement(' -- Write_To_Log', r); r:=r+1;
385 ad_ddl.build_statement(' -- Purpose', r); r:=r+1;
386 ad_ddl.build_statement(' -- Write the text given to the log in 3500 character increments', r); r:=r+1;
387 ad_ddl.build_statement(' -- this happened. Write it to the log repository.', r); r:=r+1;
388 ad_ddl.build_statement(' -- Arguments', r); r:=r+1;
389 ad_ddl.build_statement(' -- p_module Name of the module', r); r:=r+1;
393 ad_ddl.build_statement(' -- GCS_TRANSLATION_PKG.Write_To_Log', r); r:=r+1;
390 ad_ddl.build_statement(' -- p_level Logging level', r); r:=r+1;
391 ad_ddl.build_statement(' -- p_text Text to write', r); r:=r+1;
392 ad_ddl.build_statement(' -- Example', r); r:=r+1;
394 ad_ddl.build_statement(' -- Notes', r); r:=r+1;
395 ad_ddl.build_statement(' --', r); r:=r+1;
396 ad_ddl.build_statement(' PROCEDURE Write_To_Log', r); r:=r+1;
397 ad_ddl.build_statement(' (p_module VARCHAR2,', r); r:=r+1;
398 ad_ddl.build_statement(' p_level NUMBER,', r); r:=r+1;
399 ad_ddl.build_statement(' p_text VARCHAR2)', r); r:=r+1;
400 ad_ddl.build_statement(' IS', r); r:=r+1;
401 ad_ddl.build_statement(' api_module_concat VARCHAR2(200);', r); r:=r+1;
402 ad_ddl.build_statement(' text_with_date VARCHAR2(32767);', r); r:=r+1;
403 ad_ddl.build_statement(' text_with_date_len NUMBER;', r); r:=r+1;
404 ad_ddl.build_statement(' curr_index NUMBER;', r); r:=r+1;
405 ad_ddl.build_statement(' BEGIN', r); r:=r+1;
406 ad_ddl.build_statement(' -- Only print if the log level is set at the appropriate level', r); r:=r+1;
407 ad_ddl.build_statement(' IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= p_level THEN', r); r:=r+1;
408 ad_ddl.build_statement(' api_module_concat := g_api || ''.'' || p_module;', r); r:=r+1;
409 ad_ddl.build_statement(' text_with_date := to_char(sysdate,''DD-MON-YYYY HH:MI:SS'')||g_nl||p_text;', r); r:=r+1;
410 ad_ddl.build_statement(' text_with_date_len := length(text_with_date);', r); r:=r+1;
411 ad_ddl.build_statement(' curr_index := 1;', r); r:=r+1;
412 ad_ddl.build_statement(' WHILE curr_index <= text_with_date_len LOOP', r); r:=r+1;
413 ad_ddl.build_statement(' fnd_log.string(p_level, api_module_concat,', r); r:=r+1;
414 ad_ddl.build_statement(' substr(text_with_date, curr_index, 3500));', r); r:=r+1;
415 ad_ddl.build_statement(' curr_index := curr_index + 3500;', r); r:=r+1;
416 ad_ddl.build_statement(' END LOOP;', r); r:=r+1;
417 ad_ddl.build_statement(' END IF;', r); r:=r+1;
418 ad_ddl.build_statement(' END Write_To_Log;', r); r:=r+1;
419 ad_ddl.build_statement(' ', r); r:=r+1;
420 ad_ddl.build_statement(' ', r); r:=r+1;
421 ad_ddl.build_statement(' ', r); r:=r+1;
422 ad_ddl.build_statement(' ', r); r:=r+1;
423 ad_ddl.build_statement(' --', r); r:=r+1;
424 ad_ddl.build_statement(' -- Function', r); r:=r+1;
425 ad_ddl.build_statement(' -- Get_RE_Data_Exists', r); r:=r+1;
426 ad_ddl.build_statement(' -- Purpose', r); r:=r+1;
427 ad_ddl.build_statement(' -- Determines whether the data was loaded for the given combination or not.', r); r:=r+1;
428 ad_ddl.build_statement(' -- Arguments', r); r:=r+1;
429 ad_ddl.build_statement(' -- p_hier_dataset_code The dataset code in FEM_BALANCES.', r); r:=r+1;
430 ad_ddl.build_statement(' -- p_cal_period_id The current period''s cal_period_id.', r); r:=r+1;
431 ad_ddl.build_statement(' -- p_source_system_code GCS source system code.', r); r:=r+1;
432 ad_ddl.build_statement(' -- p_from_ccy From currency code.', r); r:=r+1;
433 ad_ddl.build_statement(' -- p_ledger_id The ledger in FEM_BALANCES.', r); r:=r+1;
434 ad_ddl.build_statement(' -- p_entity_id Entity on which the translation is being performed.', r); r:=r+1;
435 ad_ddl.build_statement(' -- p_line_item_id Line Item Id of retained earnings selected for the hierarchy.', r); r:=r+1;
436 ad_ddl.build_statement(' -- Example', r); r:=r+1;
437 ad_ddl.build_statement(' -- GCS_TRANSLATION_PKG.Get_RE_Data_Exists', r); r:=r+1;
438 ad_ddl.build_statement(' -- Notes', r); r:=r+1;
439 ad_ddl.build_statement(' --', r); r:=r+1;
440 ad_ddl.build_statement(' ', r); r:=r+1;
441 ad_ddl.build_statement(' FUNCTION Get_RE_Data_Exists(', r); r:=r+1;
442 ad_ddl.build_statement(' p_hier_dataset_code NUMBER,', r); r:=r+1;
443 ad_ddl.build_statement(' p_cal_period_id NUMBER,', r); r:=r+1;
444 ad_ddl.build_statement(' p_source_system_code NUMBER,', r); r:=r+1;
445 ad_ddl.build_statement(' p_from_ccy VARCHAR2,', r); r:=r+1;
446 ad_ddl.build_statement(' p_ledger_id NUMBER,', r); r:=r+1;
447 ad_ddl.build_statement(' p_entity_id NUMBER,', r); r:=r+1;
448 ad_ddl.build_statement(' p_line_item_id NUMBER) RETURN VARCHAR2 IS', r); r:=r+1;
449 ad_ddl.build_statement(' ', r); r:=r+1;
450 ad_ddl.build_statement(' l_re_data_flag VARCHAR2(10);', r); r:=r+1;
451 ad_ddl.build_statement(' CURSOR re_data_cur (', r); r:=r+1;
452 ad_ddl.build_statement(' p_hier_dataset_code NUMBER,', r); r:=r+1;
453 ad_ddl.build_statement(' p_cal_period_id NUMBER,', r); r:=r+1;
454 ad_ddl.build_statement(' p_source_system_code NUMBER,', r); r:=r+1;
455 ad_ddl.build_statement(' p_from_ccy VARCHAR2,', r); r:=r+1;
456 ad_ddl.build_statement(' p_ledger_id NUMBER,', r); r:=r+1;
457 ad_ddl.build_statement(' p_entity_id NUMBER,', r); r:=r+1;
458 ad_ddl.build_statement(' p_line_item_id NUMBER) IS', r); r:=r+1;
459 ad_ddl.build_statement(' SELECT ''X''', r); r:=r+1;
460 ad_ddl.build_statement(' FROM FEM_BALANCES fb', r); r:=r+1;
461 ad_ddl.build_statement(' WHERE fb.dataset_code = p_hier_dataset_code', r); r:=r+1;
462 ad_ddl.build_statement(' AND fb.cal_period_id = p_cal_period_id', r); r:=r+1;
463 ad_ddl.build_statement(' AND fb.source_system_code = p_source_system_code', r); r:=r+1;
464 ad_ddl.build_statement(' AND fb.currency_code = p_from_ccy', r); r:=r+1;
468 ad_ddl.build_statement(' ', r); r:=r+1;
465 ad_ddl.build_statement(' AND fb.ledger_id = p_ledger_id', r); r:=r+1;
466 ad_ddl.build_statement(' AND fb.entity_id = p_entity_id', r); r:=r+1;
467 ad_ddl.build_statement(' AND fb.line_item_id = p_line_item_id;', r); r:=r+1;
469 ad_ddl.build_statement(' BEGIN', r); r:=r+1;
470 ad_ddl.build_statement(' OPEN re_data_cur (', r); r:=r+1;
471 ad_ddl.build_statement(' p_hier_dataset_code,', r); r:=r+1;
472 ad_ddl.build_statement(' p_cal_period_id,', r); r:=r+1;
473 ad_ddl.build_statement(' p_source_system_code,', r); r:=r+1;
474 ad_ddl.build_statement(' p_from_ccy,', r); r:=r+1;
475 ad_ddl.build_statement(' p_ledger_id,', r); r:=r+1;
476 ad_ddl.build_statement(' p_entity_id,', r); r:=r+1;
477 ad_ddl.build_statement(' p_line_item_id);', r); r:=r+1;
478 ad_ddl.build_statement(' FETCH re_data_cur INTO l_re_data_flag;', r); r:=r+1;
479 ad_ddl.build_statement(' CLOSE re_data_cur;', r); r:=r+1;
480 ad_ddl.build_statement(' ', r); r:=r+1;
481 ad_ddl.build_statement(' IF l_re_data_flag IS NOT NULL THEN', r); r:=r+1;
482 ad_ddl.build_statement(' l_re_data_flag := ''Y'';', r); r:=r+1;
483 ad_ddl.build_statement(' ELSE', r); r:=r+1;
484 ad_ddl.build_statement(' l_re_data_flag := ''N'';', r); r:=r+1;
485 ad_ddl.build_statement(' END IF;', r); r:=r+1;
486 ad_ddl.build_statement(' ', r); r:=r+1;
487 ad_ddl.build_statement(' RETURN l_re_data_flag;', r); r:=r+1;
488 ad_ddl.build_statement(' ', r); r:=r+1;
489 ad_ddl.build_statement(' END Get_RE_Data_Exists;', r); r:=r+1;
490 ad_ddl.build_statement(' ', r); r:=r+1;
491 ad_ddl.build_statement(' ', r); r:=r+1;
492 ad_ddl.build_statement('--', r); r:=r+1;
493 ad_ddl.build_statement('-- Public procedures', r); r:=r+1;
494 ad_ddl.build_statement('--', r); r:=r+1;
495 ad_ddl.build_statement(' ', r); r:=r+1;
496 ad_ddl.build_statement(' ', r); r:=r+1;
497 ad_ddl.build_statement(' PROCEDURE Initialize_Data_Load_Status (', r); r:=r+1;
498 ad_ddl.build_statement(' p_hier_dataset_code NUMBER,', r); r:=r+1;
499 ad_ddl.build_statement(' p_cal_period_id NUMBER,', r); r:=r+1;
500 ad_ddl.build_statement(' p_source_system_code NUMBER,', r); r:=r+1;
501 ad_ddl.build_statement(' p_from_ccy VARCHAR2,', r); r:=r+1;
502 ad_ddl.build_statement(' p_ledger_id NUMBER,', r); r:=r+1;
503 ad_ddl.build_statement(' p_entity_id NUMBER,', r); r:=r+1;
504 ad_ddl.build_statement(' p_line_item_id NUMBER) IS', r); r:=r+1;
505 ad_ddl.build_statement(' BEGIN', r); r:=r+1;
506 ad_ddl.build_statement(' re_data_loaded_flag :=', r); r:=r+1;
507 ad_ddl.build_statement(' Get_RE_Data_Exists (', r); r:=r+1;
508 ad_ddl.build_statement(' p_hier_dataset_code,', r); r:=r+1;
509 ad_ddl.build_statement(' p_cal_period_id,', r); r:=r+1;
510 ad_ddl.build_statement(' p_source_system_code,', r); r:=r+1;
511 ad_ddl.build_statement(' p_from_ccy,', r); r:=r+1;
512 ad_ddl.build_statement(' p_ledger_id,', r); r:=r+1;
513 ad_ddl.build_statement(' p_entity_id,', r); r:=r+1;
514 ad_ddl.build_statement(' p_line_item_id);', r); r:=r+1;
515 ad_ddl.build_statement(' END;', r); r:=r+1;
516 ad_ddl.build_statement(' ', r); r:=r+1;
517 ad_ddl.build_statement(' ', r); r:=r+1;
518 ad_ddl.build_statement('-- Start bugfix 5707630: Added public procedure for Roll_Forward_Rates, ', r); r:=r+1;
519 ad_ddl.build_statement('-- Translate_First_Ever_Period, Translate_Subsequent_Period and ', r); r:=r+1;
520 ad_ddl.build_statement('-- Create_New_Entry procedures.This public procedures will call theier respective', r); r:=r+1;
521 ad_ddl.build_statement('-- private procedures (one for historical rates and the other for retained earnings).', r); r:=r+1;
522 ad_ddl.build_statement('--', r); r:=r+1;
523 ad_ddl.build_statement(' PROCEDURE Roll_Forward_Rates', r); r:=r+1;
524 ad_ddl.build_statement(' (p_hier_dataset_code NUMBER,', r); r:=r+1;
525 ad_ddl.build_statement(' p_source_system_code NUMBER,', r); r:=r+1;
526 ad_ddl.build_statement(' p_ledger_id NUMBER,', r); r:=r+1;
527 ad_ddl.build_statement(' p_cal_period_id NUMBER,', r); r:=r+1;
528 ad_ddl.build_statement(' p_prev_period_id NUMBER,', r); r:=r+1;
529 ad_ddl.build_statement(' p_entity_id NUMBER,', r); r:=r+1;
530 ad_ddl.build_statement(' p_hierarchy_id NUMBER,', r); r:=r+1;
531 ad_ddl.build_statement(' p_from_ccy VARCHAR2,', r); r:=r+1;
532 ad_ddl.build_statement(' p_to_ccy VARCHAR2,', r); r:=r+1;
533 ad_ddl.build_statement(' p_eq_xlate_mode VARCHAR2,', r); r:=r+1;
534 ad_ddl.build_statement(' p_hier_li_id NUMBER) IS', r); r:=r+1;
535 ad_ddl.build_statement(' module VARCHAR2(30) := ''ROLL_FORWARD_RATES:PUBLIC'';', r); r:=r+1;
536 ad_ddl.build_statement(' BEGIN', r); r:=r+1;
537 ad_ddl.build_statement(' write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_enter);', r); r:=r+1;
538 ad_ddl.build_statement(' ', r); r:=r+1;
539 ad_ddl.build_statement(' GCS_TRANS_HRATES_DYNAMIC_PKG.Roll_Forward_Historical_Rates', r); r:=r+1;
540 ad_ddl.build_statement(' (p_hier_dataset_code, ', r); r:=r+1;
541 ad_ddl.build_statement(' p_source_system_code, ', r); r:=r+1;
542 ad_ddl.build_statement(' p_ledger_id, ', r); r:=r+1;
543 ad_ddl.build_statement(' p_cal_period_id, ', r); r:=r+1;
547 ad_ddl.build_statement(' p_from_ccy, ', r); r:=r+1;
544 ad_ddl.build_statement(' p_prev_period_id, ', r); r:=r+1;
545 ad_ddl.build_statement(' p_entity_id, ', r); r:=r+1;
546 ad_ddl.build_statement(' p_hierarchy_id, ', r); r:=r+1;
548 ad_ddl.build_statement(' p_to_ccy, ', r); r:=r+1;
549 ad_ddl.build_statement(' p_eq_xlate_mode, ', r); r:=r+1;
550 ad_ddl.build_statement(' p_hier_li_id);', r); r:=r+1;
551 ad_ddl.build_statement(' ', r); r:=r+1;
552 ad_ddl.build_statement(' GCS_TRANS_RE_DYNAMIC_PKG.Roll_Forward_Retained_Earnings', r); r:=r+1;
553 ad_ddl.build_statement(' (p_hier_dataset_code, ', r); r:=r+1;
554 ad_ddl.build_statement(' p_source_system_code, ', r); r:=r+1;
555 ad_ddl.build_statement(' p_ledger_id, ', r); r:=r+1;
556 ad_ddl.build_statement(' p_cal_period_id, ', r); r:=r+1;
557 ad_ddl.build_statement(' p_prev_period_id, ', r); r:=r+1;
558 ad_ddl.build_statement(' p_entity_id, ', r); r:=r+1;
559 ad_ddl.build_statement(' p_hierarchy_id, ', r); r:=r+1;
560 ad_ddl.build_statement(' p_from_ccy, ', r); r:=r+1;
561 ad_ddl.build_statement(' p_to_ccy, ', r); r:=r+1;
562 ad_ddl.build_statement(' p_eq_xlate_mode, ', r); r:=r+1;
563 ad_ddl.build_statement(' p_hier_li_id);', r); r:=r+1;
564 ad_ddl.build_statement(' ', r); r:=r+1;
565 ad_ddl.build_statement(' write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);', r); r:=r+1;
566 ad_ddl.build_statement(' END Roll_Forward_Rates;', r); r:=r+1;
567 ad_ddl.build_statement(' ', r); r:=r+1;
568 ad_ddl.build_statement(' ', r); r:=r+1;
569 ad_ddl.build_statement('--', r); r:=r+1;
570 ad_ddl.build_statement(' PROCEDURE Translate_First_Ever_Period', r); r:=r+1;
571 ad_ddl.build_statement(' (p_hier_dataset_code NUMBER,', r); r:=r+1;
572 ad_ddl.build_statement(' p_source_system_code NUMBER,', r); r:=r+1;
573 ad_ddl.build_statement(' p_ledger_id NUMBER,', r); r:=r+1;
574 ad_ddl.build_statement(' p_cal_period_id NUMBER,', r); r:=r+1;
575 ad_ddl.build_statement(' p_entity_id NUMBER,', r); r:=r+1;
576 ad_ddl.build_statement(' p_hierarchy_id NUMBER,', r); r:=r+1;
577 ad_ddl.build_statement(' p_from_ccy VARCHAR2,', r); r:=r+1;
578 ad_ddl.build_statement(' p_to_ccy VARCHAR2,', r); r:=r+1;
579 ad_ddl.build_statement(' p_eq_xlate_mode VARCHAR2,', r); r:=r+1;
580 ad_ddl.build_statement(' p_is_xlate_mode VARCHAR2,', r); r:=r+1;
581 ad_ddl.build_statement(' p_avg_rate NUMBER,', r); r:=r+1;
582 ad_ddl.build_statement(' p_end_rate NUMBER,', r); r:=r+1;
583 ad_ddl.build_statement(' p_group_by_flag VARCHAR2,', r); r:=r+1;
584 ad_ddl.build_statement(' p_round_factor NUMBER,', r); r:=r+1;
585 ad_ddl.build_statement(' p_hier_li_id NUMBER) IS', r); r:=r+1;
586 ad_ddl.build_statement(' module VARCHAR2(50) := ''TRANSLATE_FIRST_EVER_PERIOD:PUBLIC'';', r); r:=r+1;
587 ad_ddl.build_statement(' BEGIN', r); r:=r+1;
588 ad_ddl.build_statement(' write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_enter);', r); r:=r+1;
589 ad_ddl.build_statement(' ', r); r:=r+1;
590 ad_ddl.build_statement(' GCS_TRANS_HRATES_DYNAMIC_PKG.Trans_HRates_First_Per', r); r:=r+1;
591 ad_ddl.build_statement(' (p_hier_dataset_code,', r); r:=r+1;
592 ad_ddl.build_statement(' p_source_system_code,', r); r:=r+1;
593 ad_ddl.build_statement(' p_ledger_id,', r); r:=r+1;
594 ad_ddl.build_statement(' p_cal_period_id,', r); r:=r+1;
595 ad_ddl.build_statement(' p_entity_id,', r); r:=r+1;
596 ad_ddl.build_statement(' p_hierarchy_id,', r); r:=r+1;
597 ad_ddl.build_statement(' p_from_ccy,', r); r:=r+1;
598 ad_ddl.build_statement(' p_to_ccy,', r); r:=r+1;
599 ad_ddl.build_statement(' p_eq_xlate_mode,', r); r:=r+1;
600 ad_ddl.build_statement(' p_is_xlate_mode,', r); r:=r+1;
601 ad_ddl.build_statement(' p_avg_rate,', r); r:=r+1;
602 ad_ddl.build_statement(' p_end_rate,', r); r:=r+1;
603 ad_ddl.build_statement(' p_group_by_flag,', r); r:=r+1;
604 ad_ddl.build_statement(' p_round_factor,', r); r:=r+1;
605 ad_ddl.build_statement(' p_hier_li_id);', r); r:=r+1;
606 ad_ddl.build_statement(' ', r); r:=r+1;
607 ad_ddl.build_statement(' IF re_data_loaded_flag = ''Y'' THEN', r); r:=r+1;
608 ad_ddl.build_statement(' GCS_TRANS_RE_DYNAMIC_PKG.Trans_RE_First_Per', r); r:=r+1;
609 ad_ddl.build_statement(' (p_hier_dataset_code,', r); r:=r+1;
610 ad_ddl.build_statement(' p_source_system_code,', r); r:=r+1;
611 ad_ddl.build_statement(' p_ledger_id,', r); r:=r+1;
612 ad_ddl.build_statement(' p_cal_period_id,', r); r:=r+1;
613 ad_ddl.build_statement(' p_entity_id,', r); r:=r+1;
614 ad_ddl.build_statement(' p_hierarchy_id,', r); r:=r+1;
615 ad_ddl.build_statement(' p_from_ccy,', r); r:=r+1;
616 ad_ddl.build_statement(' p_to_ccy,', r); r:=r+1;
617 ad_ddl.build_statement(' p_eq_xlate_mode,', r); r:=r+1;
618 ad_ddl.build_statement(' p_is_xlate_mode,', r); r:=r+1;
619 ad_ddl.build_statement(' p_avg_rate,', r); r:=r+1;
620 ad_ddl.build_statement(' p_end_rate,', r); r:=r+1;
621 ad_ddl.build_statement(' p_group_by_flag,', r); r:=r+1;
622 ad_ddl.build_statement(' p_round_factor,', r); r:=r+1;
623 ad_ddl.build_statement(' p_hier_li_id);', r); r:=r+1;
624 ad_ddl.build_statement(' END IF;', r); r:=r+1;
625 ad_ddl.build_statement(' ', r); r:=r+1;
626 ad_ddl.build_statement(' write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);', r); r:=r+1;
627 ad_ddl.build_statement(' END Translate_First_Ever_Period;', r); r:=r+1;
631 ad_ddl.build_statement(' PROCEDURE Translate_Subsequent_Period', r); r:=r+1;
628 ad_ddl.build_statement(' ', r); r:=r+1;
629 ad_ddl.build_statement(' ', r); r:=r+1;
630 ad_ddl.build_statement('--', r); r:=r+1;
632 ad_ddl.build_statement(' (p_hier_dataset_code NUMBER,', r); r:=r+1;
633 ad_ddl.build_statement(' p_cal_period_id NUMBER,', r); r:=r+1;
634 ad_ddl.build_statement(' p_prev_period_id NUMBER,', r); r:=r+1;
635 ad_ddl.build_statement(' p_entity_id NUMBER,', r); r:=r+1;
636 ad_ddl.build_statement(' p_hierarchy_id NUMBER,', r); r:=r+1;
637 ad_ddl.build_statement(' p_ledger_id NUMBER,', r); r:=r+1;
638 ad_ddl.build_statement(' p_from_ccy VARCHAR2,', r); r:=r+1;
639 ad_ddl.build_statement(' p_to_ccy VARCHAR2,', r); r:=r+1;
640 ad_ddl.build_statement(' p_eq_xlate_mode VARCHAR2,', r); r:=r+1;
641 ad_ddl.build_statement(' p_is_xlate_mode VARCHAR2,', r); r:=r+1;
642 ad_ddl.build_statement(' p_avg_rate NUMBER,', r); r:=r+1;
643 ad_ddl.build_statement(' p_end_rate NUMBER,', r); r:=r+1;
644 ad_ddl.build_statement(' p_group_by_flag VARCHAR2,', r); r:=r+1;
645 ad_ddl.build_statement(' p_round_factor NUMBER,', r); r:=r+1;
646 ad_ddl.build_statement(' p_source_system_code NUMBER,', r); r:=r+1;
647 ad_ddl.build_statement(' p_hier_li_id NUMBER) IS', r); r:=r+1;
648 ad_ddl.build_statement(' module VARCHAR2(50) := ''TRANSLATE_SUBSEQUENT_PERIOD:PUBLIC'';', r); r:=r+1;
649 ad_ddl.build_statement(' BEGIN', r); r:=r+1;
650 ad_ddl.build_statement(' write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_enter);', r); r:=r+1;
651 ad_ddl.build_statement(' ', r); r:=r+1;
652 ad_ddl.build_statement(' GCS_TRANS_HRATES_DYNAMIC_PKG.Trans_HRates_Subseq_Per', r); r:=r+1;
653 ad_ddl.build_statement(' (p_hier_dataset_code,', r); r:=r+1;
654 ad_ddl.build_statement(' p_cal_period_id,', r); r:=r+1;
655 ad_ddl.build_statement(' p_prev_period_id,', r); r:=r+1;
656 ad_ddl.build_statement(' p_entity_id,', r); r:=r+1;
657 ad_ddl.build_statement(' p_hierarchy_id,', r); r:=r+1;
658 ad_ddl.build_statement(' p_ledger_id,', r); r:=r+1;
659 ad_ddl.build_statement(' p_from_ccy,', r); r:=r+1;
660 ad_ddl.build_statement(' p_to_ccy,', r); r:=r+1;
661 ad_ddl.build_statement(' p_eq_xlate_mode,', r); r:=r+1;
662 ad_ddl.build_statement(' p_is_xlate_mode,', r); r:=r+1;
663 ad_ddl.build_statement(' p_avg_rate,', r); r:=r+1;
664 ad_ddl.build_statement(' p_end_rate,', r); r:=r+1;
665 ad_ddl.build_statement(' p_group_by_flag,', r); r:=r+1;
666 ad_ddl.build_statement(' p_round_factor,', r); r:=r+1;
667 ad_ddl.build_statement(' p_source_system_code,', r); r:=r+1;
668 ad_ddl.build_statement(' p_hier_li_id);', r); r:=r+1;
669 ad_ddl.build_statement(' ', r); r:=r+1;
670 ad_ddl.build_statement(' IF re_data_loaded_flag = ''Y'' THEN', r); r:=r+1;
671 ad_ddl.build_statement(' GCS_TRANS_RE_DYNAMIC_PKG.Trans_RE_Subseq_Per', r); r:=r+1;
672 ad_ddl.build_statement(' (p_hier_dataset_code,', r); r:=r+1;
673 ad_ddl.build_statement(' p_cal_period_id,', r); r:=r+1;
674 ad_ddl.build_statement(' p_prev_period_id,', r); r:=r+1;
675 ad_ddl.build_statement(' p_entity_id,', r); r:=r+1;
676 ad_ddl.build_statement(' p_hierarchy_id,', r); r:=r+1;
677 ad_ddl.build_statement(' p_ledger_id,', r); r:=r+1;
678 ad_ddl.build_statement(' p_from_ccy,', r); r:=r+1;
679 ad_ddl.build_statement(' p_to_ccy,', r); r:=r+1;
680 ad_ddl.build_statement(' p_eq_xlate_mode,', r); r:=r+1;
681 ad_ddl.build_statement(' p_is_xlate_mode,', r); r:=r+1;
682 ad_ddl.build_statement(' p_avg_rate,', r); r:=r+1;
683 ad_ddl.build_statement(' p_end_rate,', r); r:=r+1;
684 ad_ddl.build_statement(' p_group_by_flag,', r); r:=r+1;
685 ad_ddl.build_statement(' p_round_factor,', r); r:=r+1;
686 ad_ddl.build_statement(' p_source_system_code,', r); r:=r+1;
687 ad_ddl.build_statement(' p_hier_li_id);', r); r:=r+1;
688 ad_ddl.build_statement(' END IF;', r); r:=r+1;
689 ad_ddl.build_statement(' ', r); r:=r+1;
690 ad_ddl.build_statement(' write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);', r); r:=r+1;
691 ad_ddl.build_statement(' END Translate_Subsequent_Period;', r); r:=r+1;
692 ad_ddl.build_statement(' ', r); r:=r+1;
693 ad_ddl.build_statement(' ', r); r:=r+1;
694 ad_ddl.build_statement('-- Create_New_Entry will not split as it does not use gcs_historical_rates table.', r); r:=r+1;
695 ad_ddl.build_statement('--', r); r:=r+1;
696 ad_ddl.build_statement(' PROCEDURE Create_New_Entry', r); r:=r+1;
697 ad_ddl.build_statement(' (p_new_entry_id NUMBER,', r); r:=r+1;
698 ad_ddl.build_statement(' p_hierarchy_id NUMBER,', r); r:=r+1;
699 ad_ddl.build_statement(' p_entity_id NUMBER,', r); r:=r+1;
700 ad_ddl.build_statement(' p_cal_period_id NUMBER,', r); r:=r+1;
701 ad_ddl.build_statement(' p_balance_type_code VARCHAR2,', r); r:=r+1;
702 ad_ddl.build_statement(' p_to_ccy VARCHAR2) IS', r); r:=r+1;
703 ad_ddl.build_statement(' module VARCHAR2(50) := ''CREATE_NEW_ENTRY:PUBLIC'';', r); r:=r+1;
704 ad_ddl.build_statement(' -- Used to keep information for gcs_entry_pkg.create_entry_header.', r); r:=r+1;
705 ad_ddl.build_statement(' errbuf VARCHAR2(2000);', r); r:=r+1;
706 ad_ddl.build_statement(' retcode VARCHAR2(2000);', r); r:=r+1;
707 ad_ddl.build_statement(' ', r); r:=r+1;
708 ad_ddl.build_statement(' -- Used because we need an IN OUT parameter', r); r:=r+1;
709 ad_ddl.build_statement(' new_entry_id NUMBER := p_new_entry_id;', r); r:=r+1;
713 ad_ddl.build_statement(' ', r); r:=r+1;
710 ad_ddl.build_statement(' ', r); r:=r+1;
711 ad_ddl.build_statement(' BEGIN', r); r:=r+1;
712 ad_ddl.build_statement(' write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_enter);', r); r:=r+1;
714 ad_ddl.build_statement(' ', r); r:=r+1;
715 ad_ddl.build_statement(' -- Create the entry', r); r:=r+1;
716 ad_ddl.build_statement(' GCS_ENTRY_PKG.create_entry_header(', r); r:=r+1;
717 ad_ddl.build_statement(' x_errbuf => errbuf,', r); r:=r+1;
718 ad_ddl.build_statement(' x_retcode => retcode,', r); r:=r+1;
719 ad_ddl.build_statement(' p_entry_id => new_entry_id,', r); r:=r+1;
720 ad_ddl.build_statement(' p_hierarchy_id => p_hierarchy_id,', r); r:=r+1;
721 ad_ddl.build_statement(' p_entity_id => p_entity_id,', r); r:=r+1;
722 ad_ddl.build_statement(' p_start_cal_period_id => p_cal_period_id,', r); r:=r+1;
723 ad_ddl.build_statement(' p_end_cal_period_id => p_cal_period_id,', r); r:=r+1;
724 ad_ddl.build_statement(' p_entry_type_code => ''AUTOMATIC'',', r); r:=r+1;
725 ad_ddl.build_statement(' p_balance_type_code => p_balance_type_code,', r); r:=r+1;
726 ad_ddl.build_statement(' p_currency_code => p_to_ccy,', r); r:=r+1;
727 ad_ddl.build_statement(' p_process_code => ''SINGLE_RUN_FOR_PERIOD'',', r); r:=r+1;
728 ad_ddl.build_statement(' p_category_code => ''TRANSLATION'',', r); r:=r+1;
729 ad_ddl.build_statement(' p_xlate_flag => ''Y'',', r); r:=r+1;
730 ad_ddl.build_statement(' p_period_init_entry_flag => ''N'');', r); r:=r+1;
731 ad_ddl.build_statement(' ', r); r:=r+1;
732 ad_ddl.build_statement(' IF retcode IN (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) THEN', r); r:=r+1;
733 ad_ddl.build_statement(' raise GCS_CCY_ENTRY_CREATE_FAILED;', r); r:=r+1;
734 ad_ddl.build_statement(' END IF;', r); r:=r+1;
735 ad_ddl.build_statement(' ', r); r:=r+1;
736 ad_ddl.build_statement(' write_to_log(module, FND_LOG.LEVEL_STATEMENT,', r); r:=r+1;
737 ad_ddl.build_statement(' ''INSERT /*+ parallel (gcs_entry_lines) */ INTO gcs_entry_lines(entry_id, '' ||', r); r:=r+1;
738 ad_ddl.build_statement(' ''line_item_id, company_cost_center_org_id, '' ||', r); r:=r+1;
739 ad_ddl.build_statement(' ''intercompany_id, financial_elem_id, product_id, '' ||', r); r:=r+1;
740 ad_ddl.build_statement(' ''natural_account_id, channel_id, project_id, customer_id, task_id, '' ||', r); r:=r+1;
741 ad_ddl.build_statement(' ''user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id, user_dim5_id, '' ||', r); r:=r+1;
742 ad_ddl.build_statement(' ''user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id, user_dim10_id, '' ||', r); r:=r+1;
743 ad_ddl.build_statement(' ''xtd_balance_e, ytd_balance_e, ptd_debit_balance_e, ptd_credit_balance_e, '' ||', r); r:=r+1;
744 ad_ddl.build_statement(' ''ytd_debit_balance_e, ytd_credit_balance_e, creation_date, created_by, '' ||', r); r:=r+1;
745 ad_ddl.build_statement(' ''last_update_date, last_updated_by, last_update_login)'' || g_nl ||', r); r:=r+1;
746 ad_ddl.build_statement(' ''SELECT '' || p_new_entry_id || '', '' ||', r); r:=r+1;
747 ad_ddl.build_statement(' ''tgt.line_item_id, '' ||', r); r:=r+1;
748 r := build_comma_list('''tgt.', ' '' ||', '''NULL, '' ||', r, 'F');
749 ad_ddl.build_statement(' g_nl ||', r); r:=r+1;
750 ad_ddl.build_statement(' ''fxata.number_assign_value *'' || g_nl ||', r); r:=r+1;
751 ad_ddl.build_statement(' ''decode(tgt.account_type_code,'' || g_nl ||', r); r:=r+1;
752 ad_ddl.build_statement(' '' ''''REVENUE'''', tgt.xlate_ptd_dr - tgt.xlate_ptd_cr,'' || g_nl ||', r); r:=r+1;
753 ad_ddl.build_statement(' '' ''''EXPENSE'''', tgt.xlate_ptd_dr - tgt.xlate_ptd_cr,'' || g_nl ||', r); r:=r+1;
754 ad_ddl.build_statement(' '' tgt.xlate_ytd_dr - tgt.xlate_ytd_cr),'' || g_nl ||', r); r:=r+1;
755 ad_ddl.build_statement(' ''fxata.number_assign_value * (tgt.xlate_ytd_dr - tgt.xlate_ytd_cr),'' || g_nl ||', r); r:=r+1;
756 ad_ddl.build_statement(' ''tgt.xlate_ptd_dr, tgt.xlate_ptd_cr, tgt.xlate_ytd_dr, tgt.xlate_ytd_cr, sysdate, '' ||', r); r:=r+1;
757 ad_ddl.build_statement(' gcs_translation_pkg.g_fnd_user_id || '', sysdate, '' ||', r); r:=r+1;
758 ad_ddl.build_statement(' gcs_translation_pkg.g_fnd_user_id || '', '' ||', r); r:=r+1;
759 ad_ddl.build_statement(' gcs_translation_pkg.g_fnd_login_id || g_nl ||', r); r:=r+1;
760 ad_ddl.build_statement(' ''FROM gcs_translation_gt, tgt,'' || g_nl ||', r); r:=r+1;
761 ad_ddl.build_statement(' '' fem_ln_items_attr li,'' || g_nl ||', r); r:=r+1;
762 ad_ddl.build_statement(' '' fem_ext_acct_types_attr fxata'' || g_nl ||', r); r:=r+1;
763 ad_ddl.build_statement(' ''WHERE li.line_item_id = tgt.line_item_id'' || g_nl ||', r); r:=r+1;
764 ad_ddl.build_statement(' ''AND li.attribute_id = '' || gcs_translation_pkg.g_li_acct_type_attr_id || g_nl ||', r); r:=r+1;
765 ad_ddl.build_statement(' ''AND li.version_id = '' || gcs_translation_pkg.g_li_acct_type_v_id || g_nl ||', r); r:=r+1;
766 ad_ddl.build_statement(' ''AND fxata.ext_account_type_code = li.dim_attribute_varchar_label'' || g_nl ||', r); r:=r+1;
767 ad_ddl.build_statement(' ''AND fxata.attribute_id = '' || gcs_translation_pkg.g_xat_sign_attr_id || g_nl ||', r); r:=r+1;
768 ad_ddl.build_statement(' ''AND fxata.version_id = '' || gcs_translation_pkg.g_xat_sign_v_id);', r); r:=r+1;
769 ad_ddl.build_statement(' ', r); r:=r+1;
770 ad_ddl.build_statement(' INSERT /*+ parallel (gcs_entry_lines) */ INTO gcs_entry_lines(', r); r:=r+1;
771 ad_ddl.build_statement(' entry_id, line_item_id, company_cost_center_org_id,', r); r:=r+1;
772 ad_ddl.build_statement(' intercompany_id, financial_elem_id,', r); r:=r+1;
776 ad_ddl.build_statement(' user_dim10_id, xtd_balance_e, ytd_balance_e, ptd_debit_balance_e,', r); r:=r+1;
773 ad_ddl.build_statement(' product_id, natural_account_id, channel_id, project_id, customer_id,', r); r:=r+1;
774 ad_ddl.build_statement(' task_id, user_dim1_id, user_dim2_id, user_dim3_id, user_dim4_id,', r); r:=r+1;
775 ad_ddl.build_statement(' user_dim5_id, user_dim6_id, user_dim7_id, user_dim8_id, user_dim9_id,', r); r:=r+1;
777 ad_ddl.build_statement(' ptd_credit_balance_e, ytd_debit_balance_e, ytd_credit_balance_e,', r); r:=r+1;
778 ad_ddl.build_statement(' creation_date, created_by, last_update_date, last_updated_by,', r); r:=r+1;
779 ad_ddl.build_statement(' last_update_login)', r); r:=r+1;
780 ad_ddl.build_statement(' SELECT', r); r:=r+1;
781 ad_ddl.build_statement(' p_new_entry_id,', r); r:=r+1;
782 ad_ddl.build_statement(' tgt.line_item_id,', r); r:=r+1;
783 ad_ddl.build_statement(' ', r); r:=r+1;
784 r := build_comma_list(' tgt.', '', ' NULL,', r, 'F');
785 ad_ddl.build_statement(' fxata.number_assign_value *', r); r:=r+1;
786 ad_ddl.build_statement(' decode(tgt.account_type_code,', r); r:=r+1;
787 ad_ddl.build_statement(' ''REVENUE'', tgt.xlate_ptd_dr - tgt.xlate_ptd_cr,', r); r:=r+1;
788 ad_ddl.build_statement(' ''EXPENSE'', tgt.xlate_ptd_dr - tgt.xlate_ptd_cr,', r); r:=r+1;
789 ad_ddl.build_statement(' tgt.xlate_ytd_dr - tgt.xlate_ytd_cr),', r); r:=r+1;
790 ad_ddl.build_statement(' fxata.number_assign_value * (tgt.xlate_ytd_dr - tgt.xlate_ytd_cr),', r); r:=r+1;
791 ad_ddl.build_statement(' tgt.xlate_ptd_dr, tgt.xlate_ptd_cr, tgt.xlate_ytd_dr, tgt.xlate_ytd_cr,', r); r:=r+1;
792 ad_ddl.build_statement(' sysdate, gcs_translation_pkg.g_fnd_user_id, sysdate,', r); r:=r+1;
793 ad_ddl.build_statement(' gcs_translation_pkg.g_fnd_user_id, gcs_translation_pkg.g_fnd_login_id', r); r:=r+1;
794 ad_ddl.build_statement(' FROM gcs_translation_gt tgt,', r); r:=r+1;
795 ad_ddl.build_statement(' fem_ln_items_attr li,', r); r:=r+1;
796 ad_ddl.build_statement(' fem_ext_acct_types_attr fxata', r); r:=r+1;
797 ad_ddl.build_statement(' WHERE li.line_item_id = tgt.line_item_id', r); r:=r+1;
798 ad_ddl.build_statement(' AND li.attribute_id = gcs_translation_pkg.g_li_acct_type_attr_id', r); r:=r+1;
799 ad_ddl.build_statement(' AND li.version_id = gcs_translation_pkg.g_li_acct_type_v_id', r); r:=r+1;
800 ad_ddl.build_statement(' AND fxata.ext_account_type_code = li.dim_attribute_varchar_member', r); r:=r+1;
801 ad_ddl.build_statement(' AND fxata.attribute_id = gcs_translation_pkg.g_xat_sign_attr_id', r); r:=r+1;
802 ad_ddl.build_statement(' AND fxata.version_id = gcs_translation_pkg.g_xat_sign_v_id;', r); r:=r+1;
803 ad_ddl.build_statement(' ', r); r:=r+1;
804 ad_ddl.build_statement(' write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);', r); r:=r+1;
805 ad_ddl.build_statement(' EXCEPTION', r); r:=r+1;
806 ad_ddl.build_statement(' WHEN GCS_CCY_ENTRY_CREATE_FAILED THEN', r); r:=r+1;
807 ad_ddl.build_statement(' module_log_write(module, g_module_failure);', r); r:=r+1;
808 ad_ddl.build_statement(' raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;', r); r:=r+1;
809 ad_ddl.build_statement(' WHEN OTHERS THEN', r); r:=r+1;
810 ad_ddl.build_statement(' FND_MESSAGE.set_name(''GCS'', ''GCS_CCY_NEW_ENTRY_UNEXP_ERR'');', r); r:=r+1;
811 ad_ddl.build_statement(' GCS_TRANSLATION_PKG.g_error_text := FND_MESSAGE.get;', r); r:=r+1;
812 ad_ddl.build_statement(' write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, GCS_TRANSLATION_PKG.g_error_text);', r); r:=r+1;
813 ad_ddl.build_statement(' write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);', r); r:=r+1;
814 ad_ddl.build_statement(' module_log_write(module, g_module_failure);', r); r:=r+1;
815 ad_ddl.build_statement(' raise GCS_TRANSLATION_PKG.GCS_CCY_SUBPROGRAM_RAISED;', r); r:=r+1;
816 ad_ddl.build_statement(' ', r); r:=r+1;
817 ad_ddl.build_statement(' ', r); r:=r+1;
818 ad_ddl.build_statement(' write_to_log(module, FND_LOG.LEVEL_PROCEDURE,g_module_success);', r); r:=r+1;
819 ad_ddl.build_statement(' END Create_New_Entry;', r); r:=r+1;
820 ad_ddl.build_statement(' ', r); r:=r+1;
821 ad_ddl.build_statement(' ', r); r:=r+1;
822 ad_ddl.build_statement('END GCS_TRANS_DYNAMIC_PKG;', r);
823
824 ad_ddl.create_plsql_object(appl, 'APPS', 'GCS_TRANS_DYNAMIC_PKG', 1, r, 'TRUE', err);
825
826 IF err = 'TRUE' THEN
827 raise GCS_CCY_DYN_PKG_BUILD_ERR;
828 END IF;
829
830 module_log_write(module, g_module_success);
831 EXCEPTION
832 WHEN GCS_CCY_APPLSYS_NOT_FOUND THEN
833 FND_MESSAGE.SET_NAME('GCS', 'GCS_APPLSYS_NOT_FOUND');
834 g_error_text := FND_MESSAGE.get;
835 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
836 x_errbuf := g_error_text;
837 x_retcode := '2';
838 module_log_write(module, g_module_failure);
839 WHEN GCS_CCY_DYN_PKG_BUILD_ERR THEN
840 FND_MESSAGE.set_name('GCS', 'GCS_CCY_DYN_PKG_BUILD_ERR');
841 g_error_text := FND_MESSAGE.get;
842 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
843 x_errbuf := g_error_text;
844 x_retcode := '2';
845 module_log_write(module, g_module_failure);
846 WHEN OTHERS THEN
847 FND_MESSAGE.set_name('GCS', 'GCS_CCY_DYN_PKG_ERR');
848 g_error_text := FND_MESSAGE.get;
849 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, g_error_text);
850 write_to_log(module, FND_LOG.LEVEL_UNEXPECTED, SQLERRM);
851 x_errbuf := g_error_text;
852 x_retcode := '2';
853 module_log_write(module, g_module_failure);
854 END Create_Package;
855
856 END GCS_TRANS_DYN_BUILD_PKG;