[Home] [Help]
PACKAGE BODY: APPS.GCS_IMPACT_WRITER_PKG
Source
1 Package body GCS_IMPACT_WRITER_PKG AS
2 /* $Header: gcsImpactWriterb.pls 120.7 2008/01/09 13:54:29 rthati noship $ */
3
4 -- The module name
5 g_module CONSTANT VARCHAR2(40) := 'gcs.plsql.GCS_IMPACT_WRITER_PKG';
6
7 FUNCTION GDSD_UPDATE_IMPACT( p_subscription_guid IN RAW,
8 p_event IN OUT NOCOPY wf_event_t) return VARCHAR2
9 IS
10 l_matching_ds NUMBER;
11 l_company_id NUMBER;
12 l_her_obj_def_id NUMBER;
13 l_inner_query VARCHAR2(10000);
14 l_event_key VARCHAR2(150);
15 l_posting_run_id VARCHAR2(150);
16 l_orgs_flag_param NUMBER;
17 --Bugfix 5569620
18 l_load_id NUMBER;
19 -- Start Bugfix 5613525
20 l_fch_gvcd_value_set_id NUMBER;
21 -- Ledger/cal_period/bsv column/mapping info cache table
22 TYPE ldg_period_bsv_info_rec_type IS RECORD( ledger_id NUMBER,
23 cal_period_id NUMBER,
24 cal_period_name VARCHAR2(150),
25 ledger_gvcd_value_set_id NUMBER,
26 map_flag VARCHAR2(10),
27 segment_column VARCHAR2(30) );
28
29 TYPE t_ledger_period_bsv_info IS TABLE OF ldg_period_bsv_info_rec_type;
30 l_ledger_period_bsv_info t_ledger_period_bsv_info;
31 -- Eng Bugfix 5613525
32
33 g_dimension_attr_info gcs_utility_pkg.t_hash_dimension_attr_info := gcs_utility_pkg.g_dimension_attr_info;
34
35 CURSOR c_get_all_period_bsv_combos( p_ledger_id NUMBER,
36 p_cal_period_id NUMBER) -- Bugfix 5303024
37 IS
38 SELECT delta_run_id,
39 balance_seg_value
40 FROM fem_intg_delta_loads
41 WHERE ledger_id = p_ledger_id
42 AND cal_period_id = p_cal_period_id
43 AND loaded_flag = 'Y';
44
45 -- Need to be dummy cursor for type compatibility
46 CURSOR c_inner_loop_cursor
47 IS
48 SELECT gbd.delta_run_id,
49 gbd.period_name,
50 gbd.currency_code,
51 gbd.actual_flag
52 FROM gl_balances_delta gbd,
53 gl_code_combinations gcc;
54
55 row_inner_rec c_inner_loop_cursor%ROWTYPE;
56
57 TYPE c_inner_dynamic_cursor is REF CURSOR;
58 c_inner_query_cv c_inner_dynamic_cursor;
59 --Bugfix 5843592
60 CURSOR c_all_ledger_entities(p_ledger_id NUMBER,
61 p_cal_period_id NUMBER,
62 p_bal_type_code VARCHAR2)
63 IS
64 SELECT gea.entity_id
65 FROM gcs_entities_attr gea,
66 fem_cal_periods_attr fcpa
67 WHERE gea.ledger_id = p_ledger_id
68 AND gea.data_type_code = DECODE(p_bal_type_code, 'A', 'ACTUAL', 'N/A')
69 AND fcpa.attribute_id = gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id
70 AND fcpa.version_id = gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').version_id
71 AND fcpa.cal_period_id = p_cal_period_id
72 AND fcpa.date_assign_value BETWEEN gea.effective_start_date
73 AND NVL(gea.effective_end_date, fcpa.date_assign_value);
74
75 Row_rec_entity_ledger c_all_ledger_entities%ROWTYPE;
76
77 CURSOR c_list_of_orgs ( EntityId NUMBER,
78 p_orgs_flag_param NUMBER,
79 p_fch_vsid NUMBER,
80 p_ledger_gvcd_value_set_id NUMBER,
81 p_her_obj_def_id NUMBER)
82 IS
83 SELECT company_cost_center_org_id
84 FROM gcs_entity_cctr_orgs
85 WHERE entity_id = EntityId
86 AND 1 = p_orgs_flag_param
87
88 UNION
89
90 SELECT child_id
91 FROM fem_cctr_orgs_hier
92 WHERE 2 = p_orgs_flag_param
93 AND parent_value_set_id = p_fch_vsid
94 AND child_value_set_id = p_ledger_gvcd_value_set_id
95 AND parent_id IN ( SELECT company_cost_center_org_id
96 FROM gcs_entity_cctr_orgs
97 WHERE entity_id = EntityId )
98 AND hierarchy_obj_def_id = p_her_obj_def_id ;
99
100 CURSOR c_check_bal_seg_val ( p_comp_display_code VARCHAR2,
101 p_company_id NUMBER)
102 IS
103 SELECT 1
104 FROM fem_companies_b
105 WHERE company_id = p_company_id
106 AND company_display_code = p_comp_display_code;
107
108
109 BEGIN
110
111 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
112 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_module || '.GDSD_UPDATE_IMPACT.begin', '<<Enter>>');
113 END IF;
114
115 -- Code to consume business event and get ledger id
116 l_event_key:= p_event.getEventKey();
117
118 l_posting_run_id := substr(l_event_key, 0, (instr(l_event_key,':', 1) - 1) );
119
120 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
121 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'l_posting_run_id: '||l_posting_run_id);
122 END IF;
123
124 -- Bugfix 5303024
125 -- Bugfix 5371570: Need to remove the reference to set of books id for R12 compatibility. Retrieve the ledger_id from gl_je_headers
126
127 --Start Bugfix 5613525
128 --Retrive consolidation chart of account's cctr-org value set id
129 SELECT fch_gvcd.value_set_id
130 INTO l_fch_gvcd_value_set_id
131 FROM gcs_system_options gso,
132 fem_global_vs_combo_defs fch_gvcd
133 WHERE gso.fch_global_vs_combo_id = fch_gvcd.global_vs_combo_id
134 AND fch_gvcd.dimension_id = 8;
135
136 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
137 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'fch_vsid: '
138 || l_fch_gvcd_value_set_id);
139 END IF;
140 -- More than one ledger/period combination can correspond to a particular posting_run_id
141 -- so decide the Mapping status and the Balancing Segment name for ledger/periods.
142 -- gl_sets_of_books has been converted to a view based on gl_ledgers table for backward compatibility
143 -- so refer gl_ledgers table rather than using view for performance reasons
144 SELECT DISTINCT gjh.ledger_id,
145 fcpt.cal_period_id,
146 fcpt.cal_period_name,
147 gvcd.value_set_id,
148 decode(gvcd.value_set_id, l_fch_gvcd_value_set_id, 'MAPPED', 'UNMAPPED'),
149 fsav.application_column_name
150 BULK COLLECT
151 INTO l_ledger_period_bsv_info
152 FROM gl_je_batches gjb,
153 fem_cal_periods_tl fcpt,
154 gl_je_headers gjh,
155 fem_intg_calendar_map ficm,
156 fem_ledgers_attr fla,
157 fem_global_vs_combo_defs gvcd,
158 fnd_segment_attribute_values fsav
159 WHERE gjb.posting_run_id = l_posting_run_id
160 AND gjb.status = 'P'
161 AND gjb.default_period_name = fcpt.cal_period_name
162 AND gjb.je_batch_id = gjh.je_batch_id
163 AND fcpt.language = userenv('LANG')
164 AND fcpt.calendar_id = ficm.calendar_id
165 AND fcpt.dimension_group_id = ficm.dimension_group_id
166 AND ficm.period_set_name = gjb.period_set_name
167 AND ficm.period_type = gjb.accounted_period_type
168 AND fla.attribute_id = gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO').attribute_id
169 AND fla.version_id = gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-GLOBAL_VS_COMBO').version_id
170 AND fla.ledger_id = gjh.ledger_id
171 AND gvcd.global_vs_combo_id = fla.dim_attribute_numeric_member
172 AND gvcd.dimension_id = 8
173 AND fsav.id_flex_num = gjb.chart_of_accounts_id
174 AND fsav.segment_attribute_type = 'GL_BALANCING'
175 AND fsav.attribute_value = 'Y'
176 AND fsav.application_id = 101
177 AND fsav.id_flex_code = 'GL#';
178
179
180 IF ( l_ledger_period_bsv_info.FIRST IS NOT NULL AND l_ledger_period_bsv_info.LAST IS NOT NULL )
181 THEN
182
183 FOR l_ledger_period_bsv_index IN l_ledger_period_bsv_info.FIRST..l_ledger_period_bsv_info.LAST
184 LOOP
185
186 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
187 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'l_ledger_period_bsv_index: '
188 || l_ledger_period_bsv_index);
189 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'ledger_id: '
190 || l_ledger_period_bsv_info(l_ledger_period_bsv_index).ledger_id);
191 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'cal_period_id: '
192 || l_ledger_period_bsv_info(l_ledger_period_bsv_index).cal_period_id);
193 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'cal_period_name: '
194 || l_ledger_period_bsv_info(l_ledger_period_bsv_index).cal_period_name);
195 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'ledger_gvcd_value_set_id: '
196 || l_ledger_period_bsv_info(l_ledger_period_bsv_index).ledger_gvcd_value_set_id);
197 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'map_flag: '
198 || l_ledger_period_bsv_info(l_ledger_period_bsv_index).map_flag);
199 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'segment_column: '
200 || l_ledger_period_bsv_info(l_ledger_period_bsv_index).segment_column);
201
202 END IF;
203
204
205 FOR l_rec_outer IN c_get_all_period_bsv_combos( l_ledger_period_bsv_info(l_ledger_period_bsv_index).ledger_id,
206 l_ledger_period_bsv_info(l_ledger_period_bsv_index).cal_period_id ) -- Bugfix 5303024
207 LOOP
208
209 -- End Bugfix 5613525
210
211 l_inner_query :='SELECT gbd.delta_run_id, gbd.period_name, '||
212 ' gbd.currency_code, gbd.actual_flag ' ||
213 ' FROM gl_balances_delta gbd,' ||
214 ' gl_code_combinations gcc '||
215 ' WHERE gbd.period_name = :period '||
216 ' AND gbd.ledger_id = :ledger' ||
217 ' AND gcc.code_combination_id = gbd.code_combination_id '||
218 ' AND gcc.' ||
219 l_ledger_period_bsv_info(l_ledger_period_bsv_index).segment_column ||
220 '= :balseg' ||
221 ' AND gbd.delta_run_id > :DeltaRunId';
222
223 OPEN c_inner_query_cv FOR l_inner_query USING l_ledger_period_bsv_info(l_ledger_period_bsv_index).cal_period_name,
224 l_ledger_period_bsv_info(l_ledger_period_bsv_index).ledger_id,
225 l_rec_outer.balance_seg_value,
226 l_rec_outer.delta_run_id;
227 LOOP
228 FETCH c_inner_query_cv into row_inner_rec;
229 EXIT WHEN c_inner_query_cv%NOTFOUND;
230
231 --Special logic to determine Entity Id
232 --Step1 : take all FEM entities that use the ledger_id passed in
233 --Bugfix 5843592
234 FOR rec_entity in c_all_ledger_entities(l_ledger_period_bsv_info(l_ledger_period_bsv_index).ledger_id,
235 l_ledger_period_bsv_info(l_ledger_period_bsv_index).cal_period_id,
236 row_inner_rec.actual_flag)
237 LOOP
238
239 --this will be in the FCH GVSC's org value set
240 --gcs_entity_cctr_orgs to get the list of Organization values for this entity
241 IF (l_ledger_period_bsv_info(l_ledger_period_bsv_index).map_flag = 'MAPPED') THEN
242 l_orgs_flag_param := 1;
243 l_her_obj_def_id := -1;
244
245 ELSE
246 --Do further filtration on HIERARCHY_OBJ_DEF_ID
247 SELECT object_definition_id
248 INTO l_her_obj_def_id
249 FROM fem_object_definition_b fodb,
250 fem_xdim_dimensions fxd,
251 fem_cal_periods_attr fcpa
252 WHERE fodb.object_id = fxd.default_mvs_hierarchy_obj_id
253 AND dimension_id = 8
254 AND fcpa.attribute_id = gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id
255 AND fcpa.version_id = gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').version_id
256 AND fcpa.cal_period_id = l_ledger_period_bsv_info(l_ledger_period_bsv_index).cal_period_id
257 AND fcpa.date_assign_value BETWEEN effective_start_date AND effective_end_date;
258
259 -- Append the extra filter clause here
260 l_orgs_flag_param := 2;
261
262 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
263 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'l_her_obj_def_id: '||l_her_obj_def_id);
264 END IF;
265
266 END IF;
267
268 FOR rec_list_of_orgs IN c_list_of_orgs( rec_entity.entity_id,
269 l_orgs_flag_param,
270 l_fch_gvcd_value_set_id,
271 l_ledger_period_bsv_info(l_ledger_period_bsv_index).ledger_gvcd_value_set_id,
272 l_her_obj_def_id )
273 LOOP
274 -- use fem_cctr_orgs_attr to get the company id values for each organization
275 SELECT dim_attribute_numeric_member
276 INTO l_company_id
277 FROM fem_cctr_orgs_attr fda
278 WHERE fda.company_cost_center_org_id = rec_list_of_orgs.company_cost_center_org_id
279 AND fda.attribute_id = gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').attribute_id
280 AND fda.version_id = gcs_utility_pkg.g_dimension_attr_info('COMPANY_COST_CENTER_ORG_ID-COMPANY').version_id;
281
282 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
283 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'l_company_id: '||l_company_id);
284 END IF;
285
286 --Check if Balance Seg Values match
287 OPEN c_check_bal_seg_val(l_rec_outer.balance_seg_value, l_company_id);
288
289 FETCH c_check_bal_seg_val INTO l_matching_ds;
290
291 IF c_check_bal_seg_val%NOTFOUND THEN
292 CLOSE c_check_bal_seg_val;
293 ELSE
294 CLOSE c_check_bal_seg_val;
295 UPDATE gcs_data_sub_dtls
296 SET status_code = 'IMPACTED'
297 WHERE cal_period_id = l_ledger_period_bsv_info(l_ledger_period_bsv_index).cal_period_id
298 AND entity_id = rec_entity.entity_id
299 AND currency_code = row_inner_rec.currency_code
300 AND most_recent_flag = 'Y'
301 AND balance_type_code = decode(row_inner_rec.actual_flag,'A','ACTUAL','N/A')
302 --Start Bugfix 5569620
303 RETURNING load_id INTO l_load_id;
304
305 --Roll forward impact to consolidation data statuses for the impacted load
306 GCS_CONS_MONITOR_PKG.update_data_status ( p_load_id => l_load_id );
307 --End Bugfix 5569620
308
309 COMMIT;
310
311 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
312 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'Data Submission Impacted for: ');
313 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'Balance Type Code: '||row_inner_rec.actual_flag);
314 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'Cal Period Id: '||l_ledger_period_bsv_info(l_ledger_period_bsv_index).cal_period_id);
315 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'Entity Id: '||rec_entity.entity_id);
316 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_module || '.GDSD_UPDATE_IMPACT', 'Currency Code: '||row_inner_rec.currency_code);
317 END IF;
318
319 END IF;
320
321 END LOOP; --rec_list_of_orgs
322
323 END LOOP; --rec_entity
324
325 END LOOP; --c_inner_query_cv
326
327 END LOOP; --l_rec_outer
328
329 -- Start Bugfix 5613525
330 END LOOP; --l_ledger_period_bsv_index
331
332 END IF; --l_ledger_period_bsv_info not null
333 -- Eng Bugfix 5613525
334
335 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
336 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_module || '.GDSD_UPDATE_IMPACT.end', '<<Exit>>');
337 END IF;
338
339 RETURN 'SUCCESS';
340
341 EXCEPTION
342 WHEN OTHERS THEN
343 BEGIN
344 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
345 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, g_module || '.GDSD_UPDATE_IMPACT.end', SUBSTR(SQLERRM, 1, 255));
346 END IF;
347
348 RETURN 'FAILURE';
349 END;
350 END GDSD_UPDATE_IMPACT;
351
352 END GCS_IMPACT_WRITER_PKG;