[Home] [Help]
PACKAGE BODY: APPS.GCS_XML_DT_UTILITY_PKG
Source
1 PACKAGE BODY GCS_XML_DT_UTILITY_PKG AS
2 /* $Header: gcsxmldtutilb.pls 120.24 2008/01/10 09:21:52 rthati noship $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7
8 -- The API name
9 g_pkg_name CONSTANT VARCHAR2 (40) := 'gcs.plsql.GCS_XML_DT_UTILITY_PKG';
10 -- A newline character. Included for convenience when writing long strings.
11 g_nl VARCHAR2 (1) := '
12 ';
13 -- session id
14 g_session_id NUMBER;
15
16 --
17 -- PRIVATE PROCEDURES
18 --
19 ---------------------------------------------------------------------------
20 --
21 -- Procedure
22 -- get_value_set_clause
23 -- Purpose
24 -- An API to handle Data Submission Trial Balance Data Template Literals
25 -- for handling value_set_id for dimensions
26 -- Arguments
27 -- Notes
28 -- Created to fix bug 5394468
29
30 -- Bugfix 5843592 , Added one parameter p_load_id
31 PROCEDURE get_value_set_clause ( p_entity_id IN NUMBER,
32 p_load_id IN NUMBER,
33 p_value_set_clause OUT NOCOPY VARCHAR2)
34
35
36 IS
37 TYPE r_dimension_vs_id IS RECORD
38 (dimension_id NUMBER(15),
39 value_set_id NUMBER);
40
41 TYPE t_dimension_vs_id IS TABLE OF r_dimension_vs_id;
42
43 l_dimension_vs_id t_dimension_vs_id;
44
45 -- Bug Fix: 5843592, Get the attribute id and version id of the CAL_PERIOD_END_DATE of calendar period
46 l_period_end_date_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
47 .attribute_id;
48 l_period_end_date_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
49 .version_id;
50
51 l_api_name VARCHAR2 (30) := 'GET_VALUE_SET_CLAUSE';
52
53
54 BEGIN
55
56 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
57 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, '<<Enter>>');
58 END IF;
59
60 -- Bugfix 5843592, Get the correct source ledger Id, depending upon the calendar period
61
62 SELECT gvcd.dimension_id,
63 gvcd.value_set_id
64 BULK COLLECT INTO
65 l_dimension_vs_id
66 FROM fem_global_vs_combo_defs gvcd,
67 fem_ledgers_attr fla,
68 gcs_entities_attr gea,
69 fem_tab_column_prop ftcp,
70 fem_tab_columns_b ftcb,
71 gcs_data_sub_dtls gdsd,
72 fem_cal_periods_attr fcpa
73 WHERE gdsd.load_id = p_load_id
74 AND gea.entity_id = p_entity_id
75 AND gea.data_type_code = gdsd.balance_type_code
76 AND fcpa.cal_period_id = gdsd.cal_period_id
77 AND fcpa.attribute_id = l_period_end_date_attr
78 AND fcpa.version_id = l_period_end_date_version
79 AND fcpa.date_assign_value BETWEEN gea.effective_start_date
80 AND NVL(gea.effective_end_date, fcpa.date_assign_value)
81 AND fla.ledger_id = gea.ledger_id
82 AND fla.attribute_id = pLedgerVsComboAttr
83 AND fla.version_id = pLedgerVsComboVersion
84 AND ftcb.table_name = 'FEM_BALANCES'
85 AND ftcb.dimension_id = gvcd.dimension_id
86 AND ftcb.column_name = ftcp.column_name
87 AND ftcb.column_name <> 'INTERCOMPANY_ID'
88 AND ftcp.column_property_code = 'PROCESSING_KEY'
89 AND ftcp.table_name = ftcb.table_name
90 AND gvcd.global_vs_combo_id = fla.dim_attribute_numeric_member;
91
92 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
93 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'Dimension Value Set retrieved in the collection');
94 END IF;
95
96 IF (l_dimension_vs_id.FIRST IS NOT NULL AND l_dimension_vs_id.LAST IS NOT NULL) THEN
97
98 FOR l_counter IN l_dimension_vs_id.FIRST..l_dimension_vs_id.LAST LOOP
99
100 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
101 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'Adding value set clause for dimension-id :' || l_dimension_vs_id(l_counter).dimension_id);
102 END IF;
103
104 IF (l_dimension_vs_id(l_counter).dimension_id = 8) THEN
105
106 p_value_set_clause := p_value_set_clause || g_nl ||
107 ' AND fcov.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id || g_nl ||
108 ' AND fciv.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
109
110 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 14) THEN
111
112 p_value_set_clause := p_value_set_clause || g_nl ||
113 ' AND fliv.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
114
115 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 3 AND
116
117 gcs_utility_pkg.get_fem_dim_required('PRODUCT_ID') = 'Y') THEN
118 p_value_set_clause := p_value_set_clause || g_nl ||
119 ' AND fpb.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
120
121 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 2 AND
122
123 gcs_utility_pkg.get_fem_dim_required('NATURAL_ACCOUNT_ID') = 'Y') THEN
124 p_value_set_clause := p_value_set_clause || g_nl ||
125 ' AND fnab.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
126
127 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 13 AND
128
129 gcs_utility_pkg.get_fem_dim_required('CHANNEL_ID') = 'Y') THEN
130 p_value_set_clause := p_value_set_clause || g_nl ||
131 ' AND fchb.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
132
133 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 15 AND
134
135 gcs_utility_pkg.get_fem_dim_required('PROJECT_ID') = 'Y') THEN
136 p_value_set_clause := p_value_set_clause || g_nl ||
137 ' AND fpjb.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
138
139 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 16 AND
140
141 gcs_utility_pkg.get_fem_dim_required('CUSTOMER_ID') = 'Y') THEN
142 p_value_set_clause := p_value_set_clause || g_nl ||
143 ' AND fcb.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
144
145 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 30 AND
146
147 gcs_utility_pkg.get_fem_dim_required('TASK_ID') = 'Y') THEN
148 p_value_set_clause := p_value_set_clause || g_nl ||
149 ' AND ftb.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
150
151 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 19 AND
152
153 gcs_utility_pkg.get_fem_dim_required('USER_DIM1_ID') = 'Y') THEN
154 p_value_set_clause := p_value_set_clause || g_nl ||
155 ' AND fud1.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
156
157 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 20 AND
158
159 gcs_utility_pkg.get_fem_dim_required('USER_DIM2_ID') = 'Y') THEN
160 p_value_set_clause := p_value_set_clause || g_nl ||
161 ' AND fud2.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
162
163 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 21 AND
164
165 gcs_utility_pkg.get_fem_dim_required('USER_DIM3_ID') = 'Y') THEN
166 p_value_set_clause := p_value_set_clause || g_nl ||
167 ' AND fud3.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
168
169 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 22 AND
170
171 gcs_utility_pkg.get_fem_dim_required('USER_DIM4_ID') = 'Y') THEN
172 p_value_set_clause := p_value_set_clause || g_nl ||
173 ' AND fud4.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
174
175 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 23 AND
176
177 gcs_utility_pkg.get_fem_dim_required('USER_DIM5_ID') = 'Y') THEN
178 p_value_set_clause := p_value_set_clause || g_nl ||
179 ' AND fud5.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
180
181 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 24 AND
182
183 gcs_utility_pkg.get_fem_dim_required('USER_DIM6_ID') = 'Y') THEN
184 p_value_set_clause := p_value_set_clause || g_nl ||
185 ' AND fud6.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
186
187 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 25 AND
188
189 gcs_utility_pkg.get_fem_dim_required('USER_DIM7_ID') = 'Y') THEN
190 p_value_set_clause := p_value_set_clause || g_nl ||
191 ' AND fud7.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
192
193 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 26 AND
194
195 gcs_utility_pkg.get_fem_dim_required('USER_DIM8_ID') = 'Y') THEN
196 p_value_set_clause := p_value_set_clause || g_nl ||
197 ' AND fud8.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
198
199 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 27 AND
200
201 gcs_utility_pkg.get_fem_dim_required('USER_DIM9_ID') = 'Y') THEN
202 p_value_set_clause := p_value_set_clause || g_nl ||
203 ' AND fud9.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
204
205 ELSIF (l_dimension_vs_id(l_counter).dimension_id = 28 AND
206
207 gcs_utility_pkg.get_fem_dim_required('USER_DIM10_ID') = 'Y') THEN
208 p_value_set_clause := p_value_set_clause || g_nl ||
209 ' AND fud10.value_set_id = ' || l_dimension_vs_id(l_counter).value_set_id ;
210
211 END IF;
212
213 END LOOP;
214 END IF;
215
216 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
217 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, '<<Exit>>');
218 END IF;
219
220 END;
221
222 ---------------------------------------------------------------------------
223 --
224 -- Function
225 -- before_cmtb_report
226 -- Purpose
227 -- An API to handle consolidation Trial Balance Data Template Literals
228 -- Arguments
229 -- Notes
230 --
231 FUNCTION before_cmtb_report RETURN BOOLEAN
232 IS
233 l_api_name VARCHAR2 (30) := 'BEFORE_CMTB_REPORT';
234
235
236 -- Bugfix 5087857
237 -- Bugfix 5696229: added distinct clause
238
239 CURSOR c1 ( p_entity_id number,
240 p_run_name varchar2 )
241 IS
242 SELECT DISTINCT gcr.child_entity_id
243 FROM gcs_cons_relationships gcr,
244 gcs_cons_eng_runs gcer,
245 fem_cal_periods_attr fcpa
246 WHERE gcr.parent_entity_id = p_entity_id
247 AND gcr.dominant_parent_flag = 'Y'
248 AND gcr.hierarchy_id = gcer.hierarchy_id
249 AND gcer.run_name = p_run_name
250 AND gcer.MOST_RECENT_FLAG = 'Y'
251 AND fcpa.cal_period_id = gcer.cal_period_id
252 AND fcpa.attribute_id = pCalPeriodEndDateAttr
253 AND fcpa.version_id = pCalPeriodEndDateVersion
254 AND fcpa.date_assign_value BETWEEN gcr.start_date
255 AND NVL(gcr.end_date,fcpa.date_assign_value);
256
257
258 BEGIN
259 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
260 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_pkg_name || '.' || l_api_name || '.begin', '<<Enter>>');
261 END IF;
262
263 -- Prepare parent entity and it's children in comma delimited list
264 -- which would be used by IN clause of data template query
265 entityIdListLiteral :=' AND fb.entity_id IN ('||pEntityId;
266 FOR temp in c1(pEntityId, pRunName) LOOP
267 entityIdListLiteral := entityIdListLiteral ||','|| temp.child_entity_id;
268 END LOOP;
269 entityIdListLiteral := entityIdListLiteral ||') ';
270
271 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
272 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'entityIdListLiteral : ' || entityIdListLiteral);
273 END IF;
274
275 -- insert into temp_cmtb values (entityIdListLiteral);
276 -- commit;
277 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
278 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_pkg_name || '.' || l_api_name || '.end', '<<Exit>>');
279 END IF;
280
281 RETURN(TRUE);
282
283 EXCEPTION
284 WHEN OTHERS THEN
285 BEGIN
286 -- Write appropriate error log information
287 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
288 FND_LOG.STRING (FND_LOG.LEVEL_ERROR, g_pkg_name || '.' || l_api_name, substr(SQLERRM, 1, 200) || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
289 END IF; RETURN(FALSE);
290 END;
291
292 END before_cmtb_report;
293
294 ---------------------------------------------------------------------------
295 --
296 -- Function
297 -- before_dstb_report
298 -- Purpose
299 -- An API to handle Data Submission Trial Balance Data Template Literals
300 -- Arguments
301 -- Notes
302 --
303 FUNCTION before_dstb_report RETURN BOOLEAN
304 IS
305 l_api_name VARCHAR2 (30) := 'BEFORE_DSTB_REPORT';
306 l_currency_type_code VARCHAR2(30);
307 l_currency_code VARCHAR2(15);
308 l_balance_type_code VARCHAR2(30);
309 l_source_system_code VARCHAR2(30);
310 l_entity_currency_code VARCHAR2(30);
311 l_map_flag VARCHAR2(30);
312 --start bug fix 5394468
313 l_value_set_clause VARCHAR2(2000);
314 l_entity_id NUMBER;
315 --end bug fix 5394468
316
317 -- Bug Fix: 5843592, Get the attribute id and version id of the CAL_PERIOD_END_DATE of calendar period
318 l_period_end_date_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
319 .attribute_id;
320 l_period_end_date_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
321 .version_id;
322
323
324 BEGIN
325 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
326 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_pkg_name || '.' || l_api_name || '.begin', '<<Enter>>');
327 END IF;
328 -- Retrieve information to construct appropriate literals
329 -- used by data template to avoid unnecessary decodes
330 -- Since decodes are making index unutilized and impacting performance
331
332
333 -- Bugfix 5843592, Get the correct source ledger Id, depending upon the calendar period
334
335 SELECT gdsd.currency_type_code,
336 gdsd.currency_code,
337 gdsd.balance_type_code,
338 gea.source_system_code,
339 fla_comp.dim_attribute_varchar_member entity_currency_code,
340 gdsd.entity_id
341 INTO l_currency_type_code,
342 l_currency_code,
343 l_balance_type_code,
344 l_source_system_code,
345 l_entity_currency_code,
346 l_entity_id
347 FROM gcs_data_sub_dtls gdsd,
348 gcs_entities_attr gea,
349 fem_ledgers_attr fla_comp,
350 fem_cal_periods_attr fcpa
351 WHERE gdsd.load_id = pXmlFileId
352 AND gea.entity_id = gdsd.entity_id
353 AND gea.data_type_code = gdsd.balance_type_code
354 AND fcpa.cal_period_id = gdsd.cal_period_id
355 AND fcpa.attribute_id = l_period_end_date_attr
356 AND fcpa.version_id = l_period_end_date_version
357 AND fcpa.date_assign_value BETWEEN gea.effective_start_date AND NVL(gea.effective_end_date, fcpa.date_assign_value)
358 AND fla_comp.ledger_id = gea.ledger_id
359 AND fla_comp.attribute_id = pLedgerCurrAttr
360 AND fla_comp.version_id = pLedgerCurrVersion ;
361
362 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
363 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'Load Id : ' || pXmlFileId );
364 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'Source System Code : ' || l_source_system_code);
365 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'Balance Type Code : ' || l_balance_type_code);
366 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'Currency Type Code : ' || l_currency_type_code);
367 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'Currency Code : ' || l_currency_code);
368 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'Entity Currency Code : ' || l_entity_currency_code);
369 END IF;
370
371 currencyLiteral := ' ';
372 currencyTypeLiteral := ' ';
373 entityOrgsLiteral := ' ';
374 finElemsLiteral := ' ';
375
376 -- Prepare literals for data template line level select without need complex decodes
377 -- Also when l_currency_code is not null we need not call fnd_currency.safe_get_format_mask
378 -- at line level
379 IF (l_currency_code IS NULL ) THEN
380 dsSelectLiteral :=
381 'to_char(fb.ytd_debit_balance_e,fnd_currency.safe_get_format_mask(fb.currency_code,50)) ytd_debit_balance_e,'||
382 'to_char(fb.ytd_credit_balance_e,fnd_currency.safe_get_format_mask(fb.currency_code,50)) ytd_credit_balance_e,'||
383 'to_char(fb.ytd_balance_e,fnd_currency.safe_get_format_mask(fb.currency_code,50)) ytd_balance_e,'||
384 'to_char(fb.ytd_balance_f,fnd_currency.safe_get_format_mask(fb.currency_code,50)) ytd_balance_f,'||
385 ' fb.currency_code currency_code1,'||
386 ' fct.name currency_name1,';
387 ELSE
388 dsSelectLiteral :=
389 'to_char(fb.ytd_debit_balance_e,:FORMAT_MASK) ytd_debit_balance_e,'||
390 'to_char(fb.ytd_credit_balance_e,:FORMAT_MASK) ytd_credit_balance_e,'||
391 'to_char(fb.ytd_balance_e,:FORMAT_MASK) ytd_balance_e,'||
392 'to_char(fb.ytd_balance_f,:FORMAT_MASK) ytd_balance_f,'||
393 ':currency_code currency_code1,'||
394 ':currency_name currency_name1,';
395
396 END IF;
397
398 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
399 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'dsSelectLiteral : ' || dsSelectLiteral);
400 END IF;
401
402 -- Prepare financial element where clause literal for data template line level query
403 -- Bug 5066176 :: Santosh
404 -- Default financial_elem_id to 100 if financial_elem_id is not enabled
405 -- Bug 5162091 :: Santosh
406 IF (l_balance_type_code = 'ADB') THEN
407 finElemsLiteral := ' AND fb.FINANCIAL_ELEM_ID = 140 ';
408 ELSE
409 finElemsLiteral := ' AND NVL(fb.FINANCIAL_ELEM_ID,100) NOT IN (140,10000) ';
410 END IF;
411
412 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
413 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'finElemsLiteral : ' || finElemsLiteral);
414 END IF;
415
416 -- Prepare base_currency where clause literal for data template line level query
417 IF (l_source_system_code <> 10 AND l_currency_type_code = 'BASE_CURRENCY') THEN
418 currencyLiteral := ' AND fb.currency_code = '||''''||l_currency_code||'''';
419 ELSE
420 currencyLiteral := ' ';
421 END IF;
422
423 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
424 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'currencyLiteral : ' || currencyLiteral);
425 END IF;
426
427 -- Check if this is a mapped (for which org mapping in not required)
428 -- or unmapped (for which org mapping is required) dstb case
429
430 -- Bugfix 5843592, Get the correct source ledger Id, depending upon the calendar period
431
432 SELECT decode(gvcd.value_set_id, fch_gvcd.value_set_id, 'MAPPED', 'UNMAPPED')
433 INTO l_map_flag
434 FROM fem_global_vs_combo_defs gvcd,
435 fem_ledgers_attr fla,
436 gcs_entities_attr gea,
437 fem_tab_column_prop ftcp,
438 fem_tab_columns_b ftcb,
439 gcs_system_options gso,
440 fem_global_vs_combo_defs fch_gvcd,
441 gcs_data_sub_dtls gdsd ,
442 fem_cal_periods_attr fcpa
443 WHERE gso.fch_global_vs_combo_id = fch_gvcd.global_vs_combo_id
444 AND gea.entity_id = gdsd.entity_id
445 AND gea.data_type_code = gdsd.balance_type_code
446 AND fcpa.cal_period_id = gdsd.cal_period_id
447 AND fcpa.attribute_id = l_period_end_date_attr
448 AND fcpa.version_id = l_period_end_date_version
449 AND fcpa.date_assign_value BETWEEN gea.effective_start_date AND NVL(gea.effective_end_date, fcpa.date_assign_value)
450 AND fla.ledger_id = gea.ledger_id
451 AND fla.attribute_id = pLedgerVsComboAttr
452 AND fla.version_id = pLedgerVsComboVersion
453 AND ftcb.table_name = 'FEM_BALANCES'
454 AND ftcb.dimension_id = gvcd.dimension_id
455 AND ftcb.column_name = ftcp.column_name
456 AND ftcb.column_name <> 'INTERCOMPANY_ID'
457 AND ftcp.column_property_code = 'PROCESSING_KEY'
458 AND ftcp.table_name = ftcb.table_name
459 AND gvcd.global_vs_combo_id = fla.dim_attribute_numeric_member
460 AND gvcd.dimension_id = 8
461 AND fch_gvcd.dimension_id = 8
462 AND gdsd.load_id = pXmlFileId;
463
464 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
465 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_map_flag : ' || l_map_flag);
466 END IF;
467
468 -- If OGL dstb case
469 IF (l_source_system_code = 10 ) THEN
470 -- if loaded currency = entity currency then entered currency case
471 IF (l_currency_code = l_entity_currency_code) THEN
472 currencyTypeLiteral := ' AND fb.currency_type_code = '||''''||'ENTERED'||'''';
473 -- else translated currency case
474 ELSE
475 currencyTypeLiteral := ' AND fb.currency_type_code = '||''''||'TRANSLATED'||'''';
476 END IF;
477
478 -- if mapped OGL dstb
479 IF (l_map_flag = 'MAPPED') THEN
480 entityOrgsLiteral := ' AND EXISTS (SELECT 1 '||
481 ' FROM fem_cctr_orgs_b cob, '||
482 ' gcs_entity_cctr_orgs eco '||
483 ' WHERE cob.value_set_id = :ORG_VALUE_SET_ID'||
484 ' AND eco.entity_id = :ENTITY_ID'||
485 ' AND eco.company_cost_center_org_id = cob.company_cost_center_org_id '||
486 ' AND cob.company_cost_center_org_id = fb.company_cost_center_org_id)';
487 END IF;
488 -- else Non OGL dstb case
489 ELSE
490 currencyTypeLiteral := ' ';
491 entityOrgsLiteral := ' ';
492 END IF;
493
494 -- Check if unmapped dstb case then for prepare entity org literal
495 -- irrespective of source system code value
496 IF (l_map_flag = 'UNMAPPED') THEN
497
498 entityOrgsLiteral := ' AND EXISTS (SELECT 1 '||
499 ' FROM fem_cctr_orgs_hier fcoh, '||
500 ' fem_global_vs_combo_defs fgvscd_master, '||
501 ' gcs_system_options gso, '||
502 ' fem_global_vs_combo_defs fgvscd_child, '||
503 ' fem_xdim_dimensions fxd, '||
504 ' fem_object_definition_b fodb, '||
505 ' gcs_entity_cctr_orgs feco '||
506 ' WHERE fcoh.child_id = fb.company_cost_center_org_id '||
507 ' AND fxd.dimension_id = 8 '||
508 ' AND fodb.object_id = fxd.default_mvs_hierarchy_obj_id '||
509 ' AND fcoh.hierarchy_obj_def_id = fodb.object_definition_id '||
510 ' AND fgvscd_master.global_vs_combo_id = gso.fch_global_vs_combo_id '||
511 ' AND fgvscd_master.dimension_id = 8 '||
512 -- hakumar 5350290: removed trailing tabs from SUB_GLOBAL_VS_COMBO_ID
513 ' AND fgvscd_child.global_vs_combo_id = :SUB_GLOBAL_VS_COMBO_ID'||
514 ' AND fgvscd_child.dimension_id = 8 '||
515 ' AND fcoh.parent_value_set_id = fgvscd_master.value_set_id '||
516 ' AND fcoh.child_value_set_id = fgvscd_child.value_set_id '||
517 ' AND feco.entity_id = :ENTITY_ID'||
518 ' AND fcoh.parent_id = feco.company_cost_center_org_id) ';
519
520
521 END IF;
522 --start bug fix 5394468
523 get_value_set_clause ( p_entity_id => l_entity_id,
524 p_load_id => pXmlFileId,
525 p_value_set_clause => l_value_set_clause);
526
527 entityOrgsLiteral := entityOrgsLiteral || l_value_set_clause;
528 --end bug fix 5394468
529
530 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
531 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'currencyTypeLiteral : ' || currencyTypeLiteral);
532 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'entityOrgsLiteral : ' || entityOrgsLiteral);
533 END IF;
534
535 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
536 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_pkg_name || '.' || l_api_name || '.end', '<<Exit>>');
537 END IF;
538
539 RETURN(TRUE);
540
541 EXCEPTION
542 WHEN OTHERS THEN
543 BEGIN
544
545 -- Write appropriate error log information
546 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
547 FND_LOG.STRING (FND_LOG.LEVEL_ERROR, g_pkg_name || '.' || l_api_name, SUBSTR(SQLERRM, 1, 200) || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
548 END IF;
549 RETURN(FALSE);
550 END;
551
552 END before_dstb_report;
553
554
555
556
557 --
558 -- Function
559 -- before_femnonposted_report
560 -- Purpose
561 -- An API to handle Fem Non Posted Data Template Literals
562 -- Arguments
563 -- Notes
564 --
565 FUNCTION before_femnonposted_report RETURN BOOLEAN
566 IS
567 l_api_name VARCHAR2 (30) := 'BEFORE_FEMNONPOSTED_REPORT';
568 l_ledger_id NUMBER;
569 l_balances_rule_id NUMBER;
570 l_source_datasetcode NUMBER;
571 l_datatype_code VARCHAR2(150);
572 l_dataset_code VARCHAR2(150);
573 l_budget_vers_id NUMBER;
574 l_enc_type_id NUMBER;
575 l_cal_period VARCHAR2(150);
576 l_coa_id NUMBER;
577 l_currency_code VARCHAR2(150);
578 l_segment_column VARCHAR2(150);
579 l_global_vsid NUMBER;
580 l_fch_vsid NUMBER;
581 l_cal_pd_end_date VARCHAR2(150);
582 l_def_hier_id NUMBER;
583 l_her_obj_def_id NUMBER := -1;
584 l_map_flag VARCHAR2(10) := 'UNMAPPED';
585 g_dimension_attr_info gcs_utility_pkg.t_hash_dimension_attr_info := gcs_utility_pkg.g_dimension_attr_info;
586
587 -- Bug Fix: 5843592, Get the attribute id and version id of the CAL_PERIOD_END_DATE of calendar period
588
589 l_period_end_date_attr NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
590 .attribute_id;
591 l_period_end_date_version NUMBER := gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
592 .version_id;
593
594 BEGIN
595 --Do initial logging
596 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
597 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_pkg_name || '.' || l_api_name || '.begin', '<<Enter>>');
598 END IF;
599
600 /*
601 From the entity_id, Fetch currency_code and dataset_code.
602 */
603
604 -- Bugfix 5843592, Get the correct source ledger Id, depending upon the calendar period
605
606 SELECT fla.dim_attribute_varchar_member,
607 fda.dim_attribute_varchar_member,
608 gea.ledger_id
609 INTO l_currency_code,
610 l_dataset_code,
611 l_ledger_id
612 FROM gcs_data_type_codes_vl gdtcb,
613 gcs_entities_attr gea,
614 fem_ledgers_attr fla,
615 fem_datasets_attr fda,
616 fem_cal_periods_attr fcpa
617 WHERE gea.data_type_code = gdtcb.data_type_code
618 AND fla.ledger_id = gea.ledger_id
619 AND fda.dataset_code = gdtcb.source_dataset_code
620 AND gea.entity_id = pEntityId
621 AND gea.data_type_code = pDataTypeCode
622 AND fcpa.cal_period_id = pCalPeriodId
623 AND fcpa.attribute_id = l_period_end_date_attr
624 AND fcpa.version_id = l_period_end_date_version
625 AND fcpa.date_assign_value BETWEEN gea.effective_start_date
626 AND NVL(gea.effective_end_date, fcpa.date_assign_value )
627 AND fla.attribute_id = gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE').attribute_id
628 AND fla.version_id = gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE').version_id
629 AND fda.attribute_id = gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-DATASET_BALANCE_TYPE_CODE').attribute_id
630 AND fda.version_id = gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-DATASET_BALANCE_TYPE_CODE').version_id ;
631
632 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
633 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_currency_code : '||l_currency_code);
634 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_dataset_code : '||l_dataset_code);
635 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_ledger_id : '||l_ledger_id);
636 END IF;
637
638 -- Bugfix 5843592, Get the correct source data set code, depending upon the calendar period
639
640 SELECT fibrd.actual_output_dataset_code
641 INTO l_source_datasetcode
642 FROM gcs_entities_attr gea ,
643 fem_object_definition_b fodb,
644 fem_intg_bal_rule_defs fibrd,
645 fem_cal_periods_attr fcpa
646 WHERE fodb.object_id = gea.balances_rule_id
647 AND fibrd.bal_rule_obj_def_id = fodb.object_definition_id
648 AND gea.entity_id = pEntityId
649 AND gea.data_type_code = pDataTypeCode
650 AND fcpa.cal_period_id = pCalPeriodId
651 AND fcpa.attribute_id = l_period_end_date_attr
652 AND fcpa.version_id = l_period_end_date_version
653 AND fcpa.date_assign_value BETWEEN gea.effective_start_date AND NVL(gea.effective_end_date, fcpa.date_assign_value ) ;
654
655
656 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
657 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_source_datasetcode : '||l_source_datasetcode);
658 END IF;
659
660 /*
661 From each dataset code (fem_datasets_attr), you can get the
662 balance type (actual, budget, or encumbrance), and the
663 budget_version_id or encumbrance_type_id, if applicable.
664 */
665
666 /* Distinguish between budget and encumbrance*/
667 IF(l_dataset_code='BUDGET') THEN
668
669 SELECT fda.dim_attribute_numeric_member
670 INTO l_budget_vers_id
671 FROM fem_datasets_attr fda
672 WHERE fda.attribute_id = gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-BUDGET_ID').attribute_id
673 AND fda.version_id = gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-BUDGET_ID').version_id
674 AND fda.dataset_code = l_source_datasetcode ; -- datasetcode from previous query
675
676 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
677 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_budget_vers_id : '||l_budget_vers_id);
678 END IF;
679
680 END IF;
681
682 IF(l_dataset_code='ENCUMBRANCE') THEN
683
684 SELECT fda.dim_attribute_numeric_member
685 INTO l_enc_type_id
686 FROM fem_datasets_attr fda
687 WHERE fda.attribute_id = gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-ENCUMBRANCE_TYPE_ID').attribute_id
688 AND fda.version_id = gcs_utility_pkg.g_dimension_attr_info('DATASET_CODE-ENCUMBRANCE_TYPE_ID').version_id
689 AND fda.dataset_code = l_source_datasetcode ; -- datasetcode from previous query
690
691 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
692 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_enc_type_id : '||l_enc_type_id);
693 END IF;
694
695 END IF;
696
697 /*
698 From the cal_period_id in fem_cal_periods_tl, you can get the cal_period_name,
699 which will match the period_name value in GL
700 */
701
702 SELECT cal_period_name
703 INTO l_cal_period
704 FROM fem_cal_periods_tl
705 WHERE cal_period_id = pCalPeriodId --obtained from UI parameters
706 AND language = userenv('LANG');
707
708 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
709 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_cal_period : '||l_cal_period);
710 END IF;
711
712 /*
713 we need to know which column it corresponds to in the GL data table
714 */
715
716 SELECT application_column_name
717 INTO l_segment_column
718 FROM fnd_segment_attribute_values fsav ,
719 gl_sets_of_books gsob
720 WHERE fsav.id_flex_num = gsob.CHART_OF_ACCOUNTS_ID
721 AND fsav.segment_attribute_type = 'GL_BALANCING'
722 AND fsav.attribute_value = 'Y'
723 AND fsav.application_id = 101
724 AND fsav.id_flex_code = 'GL#'
725 AND gsob.set_of_books_id = l_ledger_id;
726
727 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
728 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_segment_column : '||l_segment_column);
729 END IF;
730
731 /* Finally form the literal to return back to the main template*/
732 FilterParamsLiteral := ' AND gbd.LEDGER_ID = ' || l_ledger_id ;
733 FilterParamsLiteral := FilterParamsLiteral || ' AND gbd.CURRENCY_CODE = ''' || l_currency_code ||'''' ;
734 FilterParamsLiteral := FilterParamsLiteral || ' AND gbd.PERIOD_NAME = ''' || l_cal_period ||'''' ;
735
736 IF (l_budget_vers_id IS NOT NULL) THEN
737 FilterParamsLiteral := FilterParamsLiteral || ' and gbd.BUDGET_VERSION_ID = ' || l_enc_type_id ;
738 END IF;
739 IF (l_enc_type_id IS NOT NULL) THEN
740 FilterParamsLiteral := FilterParamsLiteral || ' and gbd.ENCUMBRANCE_TYPE_ID =' || l_enc_type_id ;
741 END IF;
742
743 FilterParamsLiteral := FilterParamsLiteral || ' AND gcc.' ||l_segment_column || ' = fidl.balance_seg_value';
744 FilterParamsLiteral := FilterParamsLiteral || ' AND fidl.ledger_id = ' || l_ledger_id ;
745 FilterParamsLiteral := FilterParamsLiteral || ' AND fidl.dataset_code = ' || l_source_datasetcode ;
746
747 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
748 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'FilterParamsLiteral : '||FilterParamsLiteral);
749 END IF;
750
751 --Collect header level Names here
752 --LEDGER
753 SELECT '''' ||LEDGER_NAME || ''''
754 INTO LedgerLiteral
755 FROM fem_ledgers_vl
756 WHERE ledger_id = l_ledger_id;
757
758 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
759 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'LedgerLiteral : '||LedgerLiteral);
760 END IF;
761
762 --Decide the Mapped/Unmapped status.
763
764 -- Bugfix 5843592, Get the correct source ledger Id, depending upon the calendar period
765
766 SELECT gvcd.value_set_id,
767 fch_gvcd.value_set_id,
768 decode(gvcd.global_vs_combo_id, fch_gvcd.global_vs_combo_id, 'MAPPED', 'UNMAPPED')
769 INTO l_global_vsid,
770 l_fch_vsid,
771 l_map_flag
772 FROM fem_global_vs_combo_defs gvcd,
773 fem_ledgers_attr fla,
774 gcs_entities_attr gea,
775 gcs_system_options gso,
776 fem_global_vs_combo_defs fch_gvcd,
777 gcs_data_sub_dtls gdsd,
778 fem_cal_periods_attr fcpa
779 WHERE gso.fch_global_vs_combo_id = fch_gvcd.global_vs_combo_id
780 AND fla.ledger_id = gea.ledger_id
781 AND fla.attribute_id = pLedgerVsComboAttr
782 AND fla.version_id = pLedgerVsComboVersion
783 AND gdsd.load_id = pLoadId
784 AND gea.entity_id = gdsd.entity_id
785 AND gea.data_type_code = gdsd.balance_type_code
786 AND fcpa.cal_period_id = gdsd.cal_period_id
787 AND fcpa.attribute_id = l_period_end_date_attr
788 AND fcpa.version_id = l_period_end_date_version
789 AND fcpa.date_assign_value BETWEEN gea.effective_start_date
790 AND NVL(gea.effective_end_date, fcpa.date_assign_value )
791 AND gvcd.global_vs_combo_id = fla.dim_attribute_numeric_member
792 AND gvcd.dimension_id = 8
793 AND fch_gvcd.dimension_id = 8 ;
794
795 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
796 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_global_vsid : '||l_global_vsid);
797 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_fch_vsid : '||l_fch_vsid);
798 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_map_flag : '||l_map_flag);
799 END IF;
800
801 IF (l_map_flag = 'MAPPED') THEN
802 entityOrgsLiteral := ' AND EXISTS (SELECT 1 '||
803 ' FROM gcs_entity_cctr_orgs eco '||
804 ' WHERE eco.entity_id =' ||pEntityId ||
805 ' AND eco.company_cost_center_org_id = fb.company_cost_center_org_id) ';
806
807 ELSE
808 --Do further filtration on HIERARCHY_OBJ_DEF_ID
809 -- Get the Cal Period End Date
810 SELECT fcpa.date_assign_value
811 INTO l_cal_pd_end_date
812 FROM fem_cal_periods_attr fcpa
813 WHERE fcpa.attribute_id = l_period_end_date_attr
814 AND fcpa.version_id = l_period_end_date_version
815 AND fcpa.cal_period_id = pCalPeriodId ;
816
817 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
818 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_cal_pd_end_date : '||l_cal_pd_end_date);
819 END IF;
820
821 -- Get the object_def_id with care for end date
822 SELECT NVL(default_mvs_hierarchy_obj_id,-1)
823 INTO l_def_hier_id
824 FROM fem_xdim_dimensions
825 WHERE dimension_id = 8;
826
827 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
828 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_def_hier_id : '||l_def_hier_id);
829 END IF;
830 --Bugfix 5853090
831 IF(l_def_hier_id IS NULL OR l_def_hier_id = -1) THEN
832
833 entityOrgsLiteral := 'and 1=2' ;
834
835 ELSE
836
837 SELECT object_definition_id
838 INTO l_her_obj_def_id
839 FROM fem_object_definition_b
840 WHERE object_id = l_def_hier_id
841 AND l_cal_pd_end_date BETWEEN effective_start_date AND effective_end_date;
842
843 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
844 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_her_obj_def_id : '||l_her_obj_def_id);
845 END IF;
846
847 -- Append the extra filter clause here
848
849 entityOrgsLiteral := ' AND fb.company_cost_center_org_id IN (SELECT ' ||
850 ' child_id FROM fem_cctr_orgs_hier WHERE parent_value_set_id = ' || l_fch_vsid ||
851 ' AND child_value_set_id = ' || l_global_vsid ||
852 ' AND parent_id in (SELECT company_cost_center_org_id FROM gcs_entity_cctr_orgs'||
853 ' WHERE entity_id = ' || pEntityId || ')' || ' AND HIERARCHY_OBJ_DEF_ID = ' || l_her_obj_def_id || ')';
854
855
856 END IF;
857 END IF;
858
859 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT THEN
860 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'entityOrgsLiteral : '||entityOrgsLiteral);
861 END IF;
862
863 --Finally return true to show success of the call.
864 RETURN(TRUE);
865 EXCEPTION
866 WHEN OTHERS THEN
867 BEGIN
868 -- Write appropriate error log information
869 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
870 FND_LOG.STRING (FND_LOG.LEVEL_ERROR, g_pkg_name || '.' || l_api_name, SUBSTR(SQLERRM, 1, 200) || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
871 END IF;
872 RETURN(FALSE);
873 END;
874 END before_femnonposted_report;
875
876 --
877 -- Function
878 -- writeback_report
879 -- Purpose
880 -- An API to handle Adjustment Writeback Data Template Literals
881 -- Arguments
882 -- Notes
883 --
884 FUNCTION writeback_report RETURN BOOLEAN
885 IS
886 l_api_name VARCHAR2 (30) := 'WRITEBACK_REPORT';
887 CURSOR c1 ( p_Xml_File_Id NUMBER)
888 IS
889 SELECT application_column_name
890 FROM fnd_id_flex_segments fifs,
891 gcs_writeback_headers gwh,
892 gl_sets_of_books gsb
893 WHERE gwh.writeback_id = p_Xml_File_Id
894 AND gwh.ledger_id = gsb.set_of_books_id
895 AND fifs.id_flex_num = gsb.chart_of_accounts_id
896 AND id_flex_code = 'GL#'
897 AND application_id = 101
898 ORDER BY segment_num;
899 counter NUMBER;
900
901 BEGIN
902 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
903 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_pkg_name || '.' || l_api_name || '.begin', '<<Enter>>');
904 END IF;
905
906 accountName := NULL;
907 FOR temp IN c1(pXmlFileId) LOOP
908 IF accountName IS NULL THEN
909 accountName := temp.application_column_name;
910 ELSE
911 accountName := accountName || '||''-''||' || temp.application_column_name;
912 END IF;
913 END LOOP;
914
915 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
916 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'accountName : ' || accountName);
917 END IF;
918
919 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
920 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_pkg_name || '.' || l_api_name || '.end', '<<Exit>>');
921 END IF;
922
923 RETURN(TRUE);
924
925 EXCEPTION WHEN OTHERS THEN
926 BEGIN
927 -- Write appropriate error log information
928 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
929 FND_LOG.STRING (FND_LOG.LEVEL_ERROR, g_pkg_name || '.' || l_api_name, SUBSTR(SQLERRM, 1, 200) || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
930 END IF;
931 RETURN(FALSE);
932 END;
933
934 END writeback_report;
935
936 --
937 -- Function
938 -- before_entry_report
939 -- Purpose
940 -- Adds the additional where clasue to the query displaying the entry
941 -- report data depending upon the LINE_TYPE_CODE column.
942 -- Arguments
943 -- Notes
944 -- Added to fix bug 5518000
945 --
946 FUNCTION before_entry_report RETURN BOOLEAN
947 IS
948 l_api_name VARCHAR2 (30) := 'BEFORE_ENTRY_REPORT';
949 CURSOR period_cur (pEntryId NUMBER, pAccountingYrAttrId NUMBER, pAccountingYrVerId NUMBER)
950 IS
951 SELECT fcpa.number_assign_value view_period_year
952 FROM gcs_entry_headers geh,
953 fem_cal_periods_attr fcpa
954 WHERE geh.entry_id = pEntryId
955 AND fcpa.cal_period_id = pCalPeriodId
956 AND fcpa.attribute_id = pAccountingYrAttrId
957 AND fcpa.version_id = pAccountingYrVerId;
958
959 CURSOR year_to_apply_re_cur (pEntryId NUMBER)
960 IS
961 SELECT geh.year_to_apply_re
962 FROM gcs_entry_headers geh
963 WHERE geh.entry_id = pEntryId;
964
965 l_year_to_appy_re GCS_ENTRY_HEADERS.YEAR_TO_APPLY_RE%TYPE;
966 l_view_period FEM_CAL_PERIODS_ATTR.NUMBER_ASSIGN_VALUE%TYPE;
967
968 BEGIN
969 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
970 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_pkg_name || '.' || l_api_name || '.begin', '<<Enter>>');
971 END IF;
972
973 --Bugfix 6610924: The default value of 'addWhereClause' must be blank
974 addWhereClause := ' ';
975 l_year_to_appy_re := NULL;
976 l_view_period := NULL;
977
978 OPEN period_cur(pXmlFileId, pCalPeriodYearAttr, pCalPeriodYearVersion);
979 FETCH period_cur INTO l_view_period;
980 CLOSE period_cur;
981
982 OPEN year_to_apply_re_cur(pXmlFileId);
983 FETCH year_to_apply_re_cur INTO l_year_to_appy_re;
984 CLOSE year_to_apply_re_cur;
985
986 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
987 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_year_to_appy_re : ' || l_year_to_appy_re);
988 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'l_view_period : ' || l_view_period);
989 END IF;
990
991
992 IF (pSourceUI = 'MANUAL' OR pSourceUI = 'ACQ_DISP' OR pSourceUI = 'REPORTING') THEN
993 IF (l_year_to_appy_re IS NULL) THEN
994 addWhereClause := ' ';
995 addREWhereClause := 'AND 1=2';
996 ELSE
997 addWhereClause := 'AND gel.line_type_code IN (''PROFIT_LOSS'',''BALANCE_SHEET'')';
998 addREWhereClause := 'AND gel.line_type_code = ''CALCULATED''';
999 END IF;
1000 ELSIF (pSourceUI = 'CONS_MONITOR') THEN
1001 addREWhereClause := 'AND 1=2';
1002 IF (l_year_to_appy_re IS NULL) THEN
1003 addWhereClause := ' ';
1004 ELSIF (l_view_period < l_year_to_appy_re) THEN
1005 addWhereClause := 'AND gel.line_type_code IN(''PROFIT_LOSS'',''BALANCE_SHEET'')';
1006 ELSE
1007 addWhereClause := 'AND gel.line_type_code IN(''BALANCE_SHEET'',''CALCULATED'')';
1008 END IF;
1009 END IF;
1010
1011 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1012 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'addWhereClause : ' || addWhereClause);
1013 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, g_pkg_name || '.' || l_api_name, 'addREWhereClause : ' || addREWhereClause);
1014 END IF;
1015
1016 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1017 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_pkg_name || '.' || l_api_name || '.end', '<<Exit>>');
1018 END IF;
1019
1020 RETURN(TRUE);
1021
1022 EXCEPTION WHEN OTHERS THEN
1023 BEGIN
1024 -- Write appropriate error log information
1025 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
1026 FND_LOG.STRING (FND_LOG.LEVEL_ERROR, g_pkg_name || '.' || l_api_name, SUBSTR(SQLERRM, 1, 200) || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1027 END IF;
1028 RETURN(FALSE);
1029 END;
1030 END before_entry_report;
1031 --
1032 -- Function
1033 -- intercompany_report
1034 -- Purpose
1035 -- Returns where clause as per the variable values passed and
1036 -- are appended to the data template query
1037 -- Arguments
1038 -- Notes
1039 -- Added to fix bug 5861665.
1040 --
1041 FUNCTION intercompany_report RETURN BOOLEAN
1042 IS
1043 l_api_name VARCHAR2 (30) := 'INTERCOMPANY_REPORT';
1044 BEGIN
1045 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1046 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_pkg_name || '.' || l_api_name || '.begin', '<<Enter>>');
1047 END IF;
1048
1049 --Checking for the value of the counter entity passed
1050 --If Counter Entity = -1 condition is always true
1051 --Else we return the condition that appends to the where clause
1052 IF (pCEntity = -1) THEN
1053 counterEntityIdLiteral := ' ';
1054 ELSE
1055 counterEntityIdLiteral := 'AND (gcerd.child_entity_id = ' || pCEntity ||
1056 ' OR gcerd.contra_child_entity_id = ' || pCEntity || ')';
1057 END IF;
1058
1059 --Checking for the value of the Rule id passed
1060 --If Rule id = -1 condition is always true
1061 --Else we return the condition that appends to the where clause
1062 IF (pRule = -1) THEN
1063 ruleIdLiteral := ' ';
1064 ELSE
1065 ruleIdLiteral := 'AND gcerd.rule_id = ' || pRule;
1066 END IF;
1067
1068 --Bugfix:6008841
1069 --Checking for the values of pTransactionType
1070 --If pTransactionType is 'N', return string that fetch rows with suspense_exceeded_flag = 'N' or 'X'
1071 --If pTransactionType is 'Y', return string that fetch rows with suspense_exceeded_flag = 'Y' or 'X'
1072 --Else we return the empty String
1073 IF (pTransactionType = 'N') THEN
1074 suspenseExFlagLiteral := 'AND geh.suspense_exceeded_flag IN (''N'',''X'')';
1075 ELSIF(pTransactionType = 'Y') THEN
1076 suspenseExFlagLiteral := 'AND geh.suspense_exceeded_flag IN (''X'',''Y'')';
1077 ELSE
1078 suspenseExFlagLiteral := ' ';
1079 END IF;
1080
1081 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1082 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_pkg_name || '.' || l_api_name || '.' || counterEntityIdLiteral, '<<Enter>>');
1083 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_pkg_name || '.' || l_api_name || '.' || ruleIdLiteral, '<<Enter>>');
1084 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_pkg_name || '.' || l_api_name || '.' || suspenseExFlagLiteral, '<<Enter>>');
1085 END IF;
1086
1087 RETURN(TRUE);
1088
1089 EXCEPTION WHEN OTHERS THEN
1090 BEGIN
1091 -- Write appropriate error log information
1092 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
1093 FND_LOG.STRING (FND_LOG.LEVEL_ERROR, g_pkg_name || '.' || l_api_name, SUBSTR(SQLERRM, 1, 200) || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1094 END IF;
1095 RETURN(FALSE);
1096 END;
1097 END intercompany_report;
1098
1099 END GCS_XML_DT_UTILITY_PKG;