[Home] [Help]
PACKAGE BODY: APPS.GCS_AGGREGATION_DYNAMIC_PKG
Source
1 PACKAGE BODY GCS_AGGREGATION_DYNAMIC_PKG AS
2 /* $Header: gcsaggbb.pls 120.3 2006/03/06 23:05:29 yingliu noship $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7 g_api VARCHAR2(40) := 'gcs.plsql.GCS_AGGREGATION_DYNAMIC_PKG';
8
9 TYPE t_entity_org_info IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
10 g_entity_org_info T_ENTITY_ORG_INFO;
11
12 --
13 -- PUBLIC FUNCTIONS
14 --
15
16 FUNCTION retrieve_org_id(p_entity_id NUMBER) RETURN NUMBER IS
17 BEGIN
18 RETURN g_entity_org_info(p_entity_id);
19 END retrieve_org_id;
20
21 --
22 -- Procedure
23 -- insert_full_entry_lines
24 -- Notes
25 -- The QRY select table has the following structure:
26 -- <entity id> <CTA template dimensions> <default org id>
27 -- Entity id Child entities of the consolidation entity
28 -- that has any CTAs from the hierarchy under it
29 -- Default org id Default org id for the entity id
30 -- CTA template dimensions All CTAs from the hierarchy under the entity
31 --
32 PROCEDURE insert_full_entry_lines(
33 p_entry_id NUMBER,
34 p_stat_entry_id NUMBER,
35 p_cons_entity_id NUMBER,
36 p_hierarchy_id NUMBER,
37 p_relationship_id NUMBER,
38 p_cal_period_id NUMBER,
39 p_period_end_date DATE,
40 p_currency_code VARCHAR2,
41 p_balance_type_code VARCHAR2,
42 p_dataset_code NUMBER)
43 IS
44 fn_name VARCHAR2(30) := 'INSERT_FULL_ENTRY_LINES';
45
46 l_bal_by_org_flag VARCHAR2(1);
47 l_default_org_id NUMBER;
48 l_intercompany_id NUMBER;
49
50 l_re_template GCS_TEMPLATES_PKG.TemplateRecord;
51 l_sus_template GCS_TEMPLATES_PKG.TemplateRecord;
52
53 CURSOR get_child_info IS
54 SELECT child_entity_id,
55 gcs_utility_pkg.get_org_id(child_entity_id, hierarchy_id) org_id
56 FROM gcs_cons_relationships
57 WHERE hierarchy_id = p_hierarchy_id
58 AND parent_entity_id = p_cons_entity_id
59 AND actual_ownership_flag = 'Y';
60
61 BEGIN
62 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
63 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
64 g_api || '.' || fn_name,
65 GCS_UTILITY_PKG.g_module_enter || fn_name ||
66 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
67 END IF;
68 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_enter ||
69 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
70
71 -- Get information of the hierarchy
72 SELECT balance_by_org_flag
73 INTO l_bal_by_org_flag
74 FROM gcs_hierarchies_b
75 WHERE hierarchy_id = p_hierarchy_id;
76
77 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
78 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
79 g_api || '.' || fn_name,
80 'l_bal_by_org_flag = ' || l_bal_by_org_flag || ' ' ||
81 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
82 END IF;
83
84 -- Balancing by org or by entity
85 IF (l_bal_by_org_flag = 'Y') THEN -- Balancing by org
86
87 -- Create entry lines
88 -- bug fix 5066467: removed ordered hint
89 INSERT /*+ APPEND */ INTO GCS_ENTRY_LINES
90 (entry_id, line_type_code,
91 company_cost_center_org_id, line_item_id, intercompany_id,
92 xtd_balance_e, ytd_balance_e,
93 ptd_debit_balance_e, ptd_credit_balance_e,
94 ytd_debit_balance_e, ytd_credit_balance_e,
95 creation_date, created_by,
96 last_update_date, last_updated_by, last_update_login)
97 SELECT
98 decode(currency_code, 'STAT', p_stat_entry_id, p_entry_id), null,
99 company_cost_center_org_id, line_item_id, intercompany_id,
100 sum(xtd_balance_e), sum(ytd_balance_e),
101 sum(ptd_debit_balance_e), sum(ptd_credit_balance_e),
102 sum(ytd_debit_balance_e), sum(ytd_credit_balance_e),
103 sysdate, GCS_AGGREGATION_PKG.g_fnd_user_id,
104 sysdate, GCS_AGGREGATION_PKG.g_fnd_user_id,
105 GCS_AGGREGATION_PKG.g_fnd_login_id
106 FROM
107 GCS_HIERARCHIES_B ghb,
108 FEM_BALANCES fb,
109 GCS_CONS_RELATIONSHIPS gcr,
110 GCS_TREATMENTS_B gt
111 WHERE
112 ghb.hierarchy_id = p_hierarchy_id
113 AND gcr.hierarchy_id = ghb.hierarchy_id
114 AND gcr.parent_entity_id = p_cons_entity_id
115 AND gcr.actual_ownership_flag = 'Y'
116 AND p_period_end_date BETWEEN gcr.start_date
117 AND nvl(gcr.end_date, p_period_end_date)
118 AND gt.treatment_id (+) = gcr.treatment_id
119 AND nvl(gt.consolidation_type_code, 'FULL') <> 'NONE'
120 AND fb.dataset_code = p_dataset_code
121 AND fb.ledger_id = ghb.fem_ledger_id
122 AND fb.cal_period_id = p_cal_period_id
123 AND fb.source_system_code = GCS_UTILITY_PKG.g_gcs_source_system_code
124 AND fb.currency_code IN (p_currency_code, 'STAT')
125 AND fb.entity_id = gcr.child_entity_id
126 GROUP BY
127 fb.currency_code,
128 fb.company_cost_center_org_id,
129 fb.intercompany_id,
130 fb.line_item_id;
131
132 ELSE -- Balancing by Entity: need special handling of RE/Suspense/CTA
133
134 -- Values used for the special processing:
135 -- * default org id for the consolidation entity
136 l_default_org_id := GCS_UTILITY_PKG.get_org_id(p_cons_entity_id,
137 p_hierarchy_id);
138
139 -- * For determining intercompany type
140 SELECT specific_intercompany_id
141 INTO l_intercompany_id
142 FROM GCS_CATEGORIES_B
143 WHERE category_code = 'AGGREGATION';
144
145 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
146 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
147 g_api || '.' || fn_name,
148 'l_intercompany_id = ' || l_intercompany_id || ' ' ||
149 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
150 END IF;
151
152 -- * Retained earnings account template
153 GCS_TEMPLATES_PKG.get_dimension_template(p_hierarchy_id, 'RE',
154 p_balance_type_code,
155 l_re_template);
156
157 -- * Suspense account template
158 GCS_TEMPLATES_PKG.get_dimension_template(p_hierarchy_id, 'SUSPENSE',
159 p_balance_type_code,
160 l_sus_template);
161
162 -- For CTA processing: find default org id of all direct child entities
163 FOR rec IN get_child_info LOOP
164 g_entity_org_info(rec.child_entity_id) := rec.org_id;
165 END LOOP;
166
167
168 -- bug fix 5066467: rewrite the code for hanlding RE/SUSPENSE/CTA
169 -- Now we first select the rows from fb to gcs_entry_lines_gt table, RE/SUSPENSE will be handled in this step
170 -- Then we update gcs_entry_lines_gt for CTA lines
171 -- Lastly, we move everything from gcs_entry_lines_gt to gcs_entry_lines
172 INSERT INTO gcs_entry_lines_gt
173 (entry_id, line_item_id, company_cost_center_org_id, intercompany_id,
174 xtd_balance_e, ytd_balance_e,
175 ptd_debit_balance_e, ptd_credit_balance_e,
176 ytd_debit_balance_e, ytd_credit_balance_e)
177 SELECT
178 decode(currency_code, 'STAT', p_stat_entry_id, p_entry_id),
179 fb.line_item_id,
180 -- company_cost_center_org_id
181 decode('Y',
182 -- matching against Retained Earnings Account template
183 l_default_org_id,
184 -- matching against Suspense Account template
185 l_default_org_id,
186 company_cost_center_org_id),
187 -- intercompany_id
188 decode(intercompany_id, company_cost_center_org_id,
189 decode(l_intercompany_id, NULL,
190 decode('Y',
191 -- matching against Retained Earnings Account template
192 l_default_org_id,
193 -- matching against Suspense Account template
194 l_default_org_id,
195 company_cost_center_org_id),
196 intercompany_id), intercompany_id),
197 sum(xtd_balance_e), sum(ytd_balance_e),
198 sum(ptd_debit_balance_e), sum(ptd_credit_balance_e),
199 sum(ytd_debit_balance_e), sum(ytd_credit_balance_e)
200 FROM
201 GCS_HIERARCHIES_B ghb,
202 FEM_BALANCES fb,
203 GCS_CONS_RELATIONSHIPS gcr,
204 GCS_TREATMENTS_B gt
205 WHERE ghb.hierarchy_id = p_hierarchy_id
206 AND gcr.hierarchy_id = p_hierarchy_id
207 AND gcr.parent_entity_id = p_cons_entity_id
208 AND gcr.actual_ownership_flag = 'Y'
209 AND p_period_end_date BETWEEN gcr.start_date AND
210 NVL (gcr.end_date, p_period_end_date)
211 AND gt.treatment_id(+) = gcr.treatment_id
212 AND NVL(gt.consolidation_type_code, 'FULL') <> 'NONE'
213 AND fb.dataset_code = p_dataset_code
214 AND fb.ledger_id = ghb.fem_ledger_id
215 AND fb.cal_period_id = p_cal_period_id
216 AND fb.source_system_code = gcs_utility_pkg.g_gcs_source_system_code
217 AND fb.currency_code IN (p_currency_code, 'STAT')
218 AND fb.entity_id = gcr.child_entity_id
219 GROUP BY
220 fb.currency_code,
221 -- company_cost_center_org_id
222 decode('Y',
223 -- matching against Retained Earnings Account template
224 l_default_org_id,
225 -- matching against Suspense Account template
226 l_default_org_id,
227 company_cost_center_org_id),
228 -- intercompany_id
229 decode(intercompany_id, company_cost_center_org_id,
230 decode(l_intercompany_id, NULL,
231 decode('Y',
232 -- matching against Retained Earnings Account template
233 l_default_org_id,
234 -- matching against Suspense Account template
235 l_default_org_id,
236 company_cost_center_org_id),
237 intercompany_id), intercompany_id),
238 fb.line_item_id;
239
240 UPDATE gcs_entry_lines_gt gelg
241 SET company_cost_center_org_id = l_default_org_id,
242 intercompany_id = DECODE(intercompany_id, company_cost_center_org_id,
243 DECODE(l_intercompany_id, NULL, l_default_org_id),
244 intercompany_id)
245 WHERE (
246 line_item_id, company_cost_center_org_id) IN (
247 SELECT
248 line_item_id,
249 retrieve_org_id (cr2.child_entity_id)
250 FROM gcs_cons_relationships cr2,
251 gcs_curr_treatments_b gctb
252 WHERE cr2.parent_entity_id = p_cons_entity_id
253 AND cr2.hierarchy_id = p_hierarchy_id
254 AND cr2.actual_ownership_flag = 'Y'
255 AND p_period_end_date BETWEEN cr2.start_date
256 AND NVL (cr2.end_date,
257 p_period_end_date
258 )
259 AND gctb.curr_treatment_id IN (
260 SELECT gcr.curr_treatment_id
261 FROM gcs_cons_relationships gcr
262 START WITH gcr.hierarchy_id = p_hierarchy_id
263 AND gcr.parent_entity_id =
264 p_cons_entity_id
265 AND gcr.actual_ownership_flag = 'Y'
266 AND p_period_end_date
267 BETWEEN gcr.start_date
268 AND NVL (gcr.end_date,
269 p_period_end_date
270 )
274 AND gcr.actual_ownership_flag = 'Y'
271 CONNECT BY PRIOR gcr.child_entity_id =
272 gcr.parent_entity_id
273 AND gcr.hierarchy_id = p_hierarchy_id
275 AND p_period_end_date
276 BETWEEN gcr.start_date
277 AND NVL (gcr.end_date,
278 p_period_end_date
279 ))
280 GROUP BY
281 line_item_id, cr2.child_entity_id);
282
283 INSERT /*+ append */INTO gcs_entry_lines
284 (entry_id, company_cost_center_org_id, line_item_id,
285 intercompany_id,
286 xtd_balance_e, ytd_balance_e,
287 ptd_debit_balance_e, ptd_credit_balance_e,
288 ytd_debit_balance_e, ytd_credit_balance_e,
289 creation_date, created_by, last_update_date,
290 last_updated_by, last_update_login)
291 SELECT entry_id, company_cost_center_org_id, line_item_id,
292 intercompany_id,
293
294 SUM (xtd_balance_e), SUM (ytd_balance_e),
295 SUM (ptd_debit_balance_e), SUM (ptd_credit_balance_e),
296 SUM (ytd_debit_balance_e), SUM (ytd_credit_balance_e),
297 SYSDATE, gcs_aggregation_pkg.g_fnd_user_id, SYSDATE,
298 gcs_aggregation_pkg.g_fnd_user_id,
299 gcs_aggregation_pkg.g_fnd_login_id
300 FROM gcs_entry_lines_gt
301 GROUP BY entry_id,
302 company_cost_center_org_id,
303 line_item_id,
304 intercompany_id;
305 END IF; -- l_bal_by_org_flag
306
307 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
308 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
309 g_api || '.' || fn_name,
310 'Inserted ' || to_char(SQL%ROWCOUNT) || ' row(s)');
311 END IF;
312
313 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
314 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
315 g_api || '.' || fn_name,
316 GCS_UTILITY_PKG.g_module_success || fn_name ||
317 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
318 END IF;
319 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_success ||
320 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
325 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
326 g_api || '.' || fn_name,
327 GCS_UTILITY_PKG.g_module_failure || fn_name ||
328 to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
329 END IF;
330 --FND_FILE.PUT_LINE(FND_FILE.LOG, GCS_UTILITY_PKG.g_module_failure ||
331 -- fn_name || to_char(sysdate, ' DD-MON-YYYY HH:MI:SS'));
332 RAISE;
333 END insert_full_entry_lines;
334
335 END GCS_AGGREGATION_DYNAMIC_PKG;