[Home] [Help]
PACKAGE BODY: APPS.GCS_RULES_PROCESSOR
Source
1 PACKAGE BODY GCS_RULES_PROCESSOR as
2 -- $Header: gcserupb.pls 120.11 2007/11/02 07:07:02 smatam ship $
3
4 --+========================================================================+
5 -- PACKAGE Global Data
6 --+========================================================================+
7
8 -- Logging level during package execution will not change so we can
9 -- define a single runtime level here, and update it at the start
10 -- of public procedures
11 runtimeLogLevel Number;
12 packageName CONSTANT Varchar2(30) := 'GCS_RULES_PROCESSOR';
13
14 -- Context switches to FND_LOG for level constants can
15 -- be avoided by copying them here once
16 statementLogLevel CONSTANT NUMBER := FND_LOG.level_statement;
17 procedureLogLevel CONSTANT NUMBER := FND_LOG.level_procedure;
18 eventLogLevel CONSTANT NUMBER := FND_LOG.level_event;
19 exceptionLogLevel CONSTANT NUMBER := FND_LOG.level_exception;
20 errorLogLevel CONSTANT NUMBER := FND_LOG.level_error;
21 unexpectedLogLevel CONSTANT NUMBER := FND_LOG.level_unexpected;
22
23 --Exception handlers: everything that can go wrong here
24 rule_has_no_steps EXCEPTION; -- p_rule_id not found in gcs_rule_steps
25 missing_rule_id EXCEPTION; -- an index in p_rule table is null
26 missing_currency_data EXCEPTION; -- cannot get precision for currency
27 invalid_dim_set_id EXCEPTION; -- the dimension set id is invalid
28 invalid_variable EXCEPTION; -- a variable identifier is not valid
29 invalid_fem_setup EXCEPTION; -- cannot get dimInfo
30 invalid_gcs_setup EXCEPTION; -- cannot get stmts
31 out_of_balance EXCEPTION; -- entry out of bal, no suspense
32 no_entry_lines EXCEPTION; -- entry outputs no non-zero lines
33 suspense_exceeded_warn EXCEPTION; -- suspense exceeded warning
34 missing_key EXCEPTION; -- a utility pkg hash key is missing
35 bad_sign EXCEPTION; -- a line_item is missing a SIGN attr
36 no_default_cctr_found EXCEPTION; -- used in initEntriesGT_tgtDimSet()
37 convert_threshold_err EXCEPTION; -- used in createEntry
38 entry_header_error EXCEPTION; -- GCS_ENTRY_PKG.create_entry_header failed
39 templates_pkg_error EXCEPTION; -- GCS_TEMPLATES_DYNAMIC_PKG may raise
40 invalid_category_code EXCEPTION; -- unable to get data from gcs_categories_b
41
42 --The cctr_org and interco dims may require special handling
43 --The column names are long strings, so lets use shorter constants
44 cctr_column CONSTANT Varchar2(30) := 'COMPANY_COST_CENTER_ORG_ID';
45 interco_column CONSTANT varchar2(30) := 'INTERCOMPANY_ID';
46
47 -- Globally useful values
48 ruleId Number := -1;
49 ruleStepId Number := -1;
50 ruleData ruleDataRecord;
51 contextData contextRecord;
52 appGroup Number := -1;
53 systemDate date;
54 userId number;
55 ruleIteration number := 1;
56
57 mainEntryId gcs_entry_headers.entry_id%TYPE := -1;
58 statEntryId gcs_entry_headers.entry_id%TYPE := -1;
59
60 -- Dynamic SQL storage
61 srcColumnList varchar2(2000);
62 tgtColumnList varchar2(2000);
63 selColumnList varchar2(2000);
64 adtColumnList varchar2(2000);
65 insColumnList varchar2(2000);
66 modJoinClause varchar2(2000);
67 dimJoinClause varchar2(2000);
68 sqlStmt varchar2(10000);
69
70 --Bugfix 4928211: Added offColumnList to store information for offsets
71 offColumnList varchar2(2000);
72
73 --3702208: Get target-only DMS info from _DIMS table
74 Cursor getTgtDims(rsi number) is
75 SELECT d.column_name, d.target_member_id
76 FROM GCS_RULE_SCOPE_DIMS d
77 WHERE d.rule_step_id = rsi;
78
79 TYPE tgtDimsTable IS TABLE OF getTgtDims%ROWTYPE INDEX BY VARCHAR2(30);
80 tgtDims tgtDimsTable;
81
82 --See initRefTables procedure
83 cursor getStmts(ruleTypeCode varchar2) is
84 SELECT statement_num, statement_text, compiled_variables
85 FROM GCS_FORMULA_STATEMENTS
86 WHERE rule_type_code = ruleTypeCode
87 OR rule_type_code = 'E'
88 ORDER BY statement_num;
89 TYPE stmtsTable IS TABLE OF getStmts%ROWTYPE INDEX BY BINARY_INTEGER;
90 stmts stmtsTable;
91
92 --Look up suspense data for the rule
93 Cursor getSuspenseData is
94 select h.threshold_amount,
95 h.threshold_currency,
96 t.financial_elem_id,
97 t.product_id,
98 t.natural_account_id,
99 t.channel_id,
100 t.line_item_id,
101 t.project_id,
102 t.customer_id,
103 t.task_id,
104 t.user_dim1_id,
105 t.user_dim2_id,
106 t.user_dim3_id,
107 t.user_dim4_id,
108 t.user_dim5_id,
109 t.user_dim6_id,
110 t.user_dim7_id,
111 t.user_dim8_id,
112 t.user_dim9_id,
113 t.user_dim10_id
114 from gcs_dimension_templates t, gcs_hierarchies_b h
115 where t.hierarchy_id = h.hierarchy_id
116 and t.template_code = 'SUSPENSE'
117 and h.hierarchy_id = contextData.hierarchy;
118
119 suspenseData getSuspenseData%ROWTYPE;
120
121 -- Look up the steps for the rule_id
122 Cursor getSteps is
123 SELECT rule_id,
124 step_seq,
125 rule_step_id,
126 step_name,
127 formula_text,
128 parsed_formula,
129 compiled_variables,
130 sql_statement_num
131 FROM GCS_ELIM_RULE_STEPS_VL
132 WHERE rule_id = ruleId
133 ORDER BY rule_step_id;
134
135 TYPE stepDataTable IS TABLE OF getSteps%ROWTYPE INDEX BY BINARY_INTEGER;
136 stepData stepDataTable;
137
138 --A hash table full of the accessible dims
139 dimInfo GCS_UTILITY_PKG.t_hash_gcs_dimension_info;
140
141 --JH 7.22.04: join to FEM_OBJECT_DEFINITIONS first to get object_definition_id
142 --JH 7.28.94: added outerjoin to hierarchy_obj_id is it is nullable (Bug 3800142)
143 --Bugfix 4928211 (STK): Remove the selection of the hierarchy object definition id
144 --in this cursor for performance purposes
145 Cursor getDimSelections(rsi number) is
146 SELECT s.rule_step_id,
147 s.column_name,
148 s.all_source_members_flag,
149 s.target_member_id,
150 s.offset_member_id,
151 s.hierarchy_obj_id,
152 x.hierarchy_table_name,
153 initcap(replace(replace(s.column_name, '_', ''), 'ID', '')) alias
154 FROM FEM_XDIM_DIMENSIONS x,
155 GCS_RULE_SCOPE_DIMS s,
156 FEM_TAB_COLUMNS_B ftcb
157 WHERE ftcb.table_name = 'FEM_BALANCES'
158 AND ftcb.column_name = s.column_name
159 AND ftcb.dimension_id = x.dimension_id
160 AND s.rule_step_id = rsi
161 ORDER BY s.column_name;
162
163 TYPE dimsTable IS TABLE OF getDimSelections%ROWTYPE INDEX BY VARCHAR2(30);
164 selectDims dimsTable;
165
166 Cursor getCurrency Is
167 Select nvl(precision, 2), minimum_accountable_unit
168 From fnd_currencies
169 Where currency_code = contextData.currencyCode;
170
171 --jh 6.29.04: add org/interco output code.
172 --Category info
173 Cursor getCategory Is
174 Select decode(target_entity_code,
175 'ELIMINATION',
176 contextData.elimsEntity,
177 'PARENT',
178 contextData.parentEntity,
179 'CHILD',
180 contextData.childEntity,
181 -1) entityId, --default in case codes change
182 org_output_code,
183 -- interco_output_code, -- changes made by yingliu
184 net_to_re_flag,
185 support_multi_parents_flag -- changes made by yingliu
186 From gcs_categories_b
187 Where category_code = contextData.eventCategory;
188
189 -- We need a SIGN attribute to set the xtd_balance_e values
190 cursor getSigns(liaAtt number, liaVer number, ataAtt number, ataVer number) is
191 select distinct e.tgt_line_item_id lineItem,
192 nvl(ata.number_assign_value, 1) signFactor
193 from fem_ln_items_attr lia,
194 fem_ext_acct_types_attr ata,
195 gcs_entries_gt e
196 where ata.ext_account_type_code = lia.dim_attribute_varchar_member
197 and ata.attribute_id = ataAtt
198 and ata.version_id = ataVer
199 and lia.attribute_id = liaAtt
200 and lia.version_id = liaVer
201 and lia.line_item_id = e.tgt_line_item_id;
202
203 TYPE signTable is Table of getSigns%ROWTYPE Index By BINARY_INTEGER;
204 tmpSign signTable;
205 liiSign signTable;
206
207 --Get an aggregated representation of the entry
208 --NOTE that because PL/SQL cannot support using field refs
209 --in a bulk insert, and since we cannot define the getLines
210 --cursor to look exactly like the gcs_entry_lines table,
211 --it becomes necessary to use a record of tables that looks
212 --like the getLines cursor below (see writeEntry()).
213 Type numTab Is Table Of number Index By binary_integer;
214 Type varTab Is Table Of varchar2(50) Index By binary_integer;
215 Type var240Tab Is Table Of varchar2(240) Index By binary_integer;
216
217 --jh 4.26.04: Added description
218 Type lineRec Is Record(
219 cctr_org_id numTab,
220 product_id numTab,
221 nat_acct_id numTab,
222 channel_id numTab,
223 project_id numTab,
224 customer_id numTab,
225 interco_id numTab,
226 entity_id numTab,
227 finl_elem_id numTab,
228 line_item_id numTab,
229 task_id numTab,
230 user_dim1_id numTab,
231 user_dim2_id numTab,
232 user_dim3_id numTab,
233 user_dim4_id numTab,
234 user_dim5_id numTab,
235 user_dim6_id numTab,
236 user_dim7_id numTab,
237 user_dim8_id numTab,
238 user_dim9_id numTab,
239 user_dim10_id numTab,
240 balance_factor numTab,
241 net_amount numTab,
242 description var240Tab);
243
244 ccyPrecision number := -1;
245 ccyMinAcctUnit number := null;
246
247 --Changes to this cursor also require changes to lineRec
248 --jh 4.26.04: Added description
249 --getLines cursor sums the balances from gcs_entries_gt by
250 --first obtain unique source-target lines (t1) then summing
251 --the sum
252
253 cursor getLines is
254 Select tgt_company_cost_center_org_id cctr_org_id,
255 tgt_product_id product_id,
256 tgt_natural_account_id nat_acct_id,
257 tgt_channel_id channel_id,
258 tgt_project_id project_id,
259 tgt_customer_id customer_id,
260 tgt_intercompany_id interco_id,
261 tgt_entity_id entity_id,
262 tgt_financial_elem_id finl_elem_id,
263 tgt_line_item_id line_item_id,
264 tgt_task_id task_id,
265 tgt_user_dim1_id user_dim1_id,
266 tgt_user_dim2_id user_dim2_id,
267 tgt_user_dim3_id user_dim3_id,
268 tgt_user_dim4_id user_dim4_id,
269 tgt_user_dim5_id user_dim5_id,
270 tgt_user_dim6_id user_dim6_id,
271 tgt_user_dim7_id user_dim7_id,
272 tgt_user_dim8_id user_dim8_id,
273 tgt_user_dim9_id user_dim9_id,
274 tgt_user_dim10_id user_dim10_id,
275 1 balance_factor,
276 decode(ccyMinAcctUnit,
277 null,
278 decode(min(sql_statement_num),
279 0,
280 round(min(nvl(output_amount, 0)), ccyPrecision),
281 round(sum(nvl(output_amount, 0)), ccyPrecision)),
282 decode(min(sql_statement_num),
283 0,
284 round(min(nvl(output_amount, 0)) / ccyMinAcctUnit, 0) *
285 ccyMinAcctUnit,
286 round(sum(nvl(output_amount, 0)) / ccyMinAcctUnit, 0) *
287 ccyMinAcctUnit)) net_amount,
288 decode(count(unique step_name),
289 1,
290 min(step_name),
291 'MULTIPLE_RULE_STEPS') description
292
293 From (Select min(sql_statement_num) sql_statement_num,
294 min(tgt_company_cost_center_org_id) tgt_company_cost_center_org_id,
295 min(tgt_product_id) tgt_product_id,
296 min(tgt_natural_account_id) tgt_natural_account_id,
297 min(tgt_channel_id) tgt_channel_id,
298 min(tgt_project_id) tgt_project_id,
299 min(tgt_customer_id) tgt_customer_id,
300 min(tgt_intercompany_id) tgt_intercompany_id,
301 min(tgt_entity_id) tgt_entity_id,
302 min(tgt_financial_elem_id) tgt_financial_elem_id,
303 min(tgt_line_item_id) tgt_line_item_id,
304 min(tgt_task_id) tgt_task_id,
305 min(tgt_user_dim1_id) tgt_user_dim1_id,
306 min(tgt_user_dim2_id) tgt_user_dim2_id,
307 min(tgt_user_dim3_id) tgt_user_dim3_id,
308 min(tgt_user_dim4_id) tgt_user_dim4_id,
309 min(tgt_user_dim5_id) tgt_user_dim5_id,
310 min(tgt_user_dim6_id) tgt_user_dim6_id,
311 min(tgt_user_dim7_id) tgt_user_dim7_id,
312 min(tgt_user_dim8_id) tgt_user_dim8_id,
313 min(tgt_user_dim9_id) tgt_user_dim9_id,
314 min(tgt_user_dim10_id) tgt_user_dim10_id,
315 min(nvl(output_amount, 0)) output_amount,
316 min(step_name) step_name
317 from gcs_entries_gt
318 Where currency_code = contextData.currencyCode
319 And output_amount <> 0
320 Group By rule_id,
321 step_seq,
322 rule_step_id,
323 src_company_cost_center_org_id,
324 src_product_id,
325 src_natural_account_id,
326 src_channel_id,
327 src_project_id,
328 src_customer_id,
329 src_intercompany_id,
330 src_entity_id,
331 src_financial_elem_id,
332 src_line_item_id,
333 src_task_id,
334 src_user_dim1_id,
335 src_user_dim2_id,
336 src_user_dim3_id,
337 src_user_dim4_id,
338 src_user_dim5_id,
339 src_user_dim6_id,
340 src_user_dim7_id,
341 src_user_dim8_id,
342 src_user_dim9_id,
343 src_user_dim10_id,
344 tgt_company_cost_center_org_id,
345 tgt_product_id,
346 tgt_natural_account_id,
347 tgt_channel_id,
348 tgt_project_id,
349 tgt_customer_id,
350 tgt_intercompany_id,
351 tgt_entity_id,
352 tgt_financial_elem_id,
353 tgt_line_item_id,
354 tgt_task_id,
355 tgt_user_dim1_id,
356 tgt_user_dim2_id,
357 tgt_user_dim3_id,
358 tgt_user_dim4_id,
359 tgt_user_dim5_id,
360 tgt_user_dim6_id,
361 tgt_user_dim7_id,
362 tgt_user_dim8_id,
363 tgt_user_dim9_id,
364 tgt_user_dim10_id) t1
365 Group By tgt_company_cost_center_org_id,
366 tgt_product_id,
367 tgt_natural_account_id,
368 tgt_channel_id,
369 tgt_project_id,
370 tgt_customer_id,
371 tgt_intercompany_id,
372 tgt_entity_id,
373 tgt_financial_elem_id,
374 tgt_line_item_id,
375 tgt_task_id,
376 tgt_user_dim1_id,
377 tgt_user_dim2_id,
378 tgt_user_dim3_id,
379 tgt_user_dim4_id,
380 tgt_user_dim5_id,
381 tgt_user_dim6_id,
382 tgt_user_dim7_id,
383 tgt_user_dim8_id,
384 tgt_user_dim9_id,
385 tgt_user_dim10_id
386 Having decode(ccyMinAcctUnit, null, decode(min(sql_statement_num), 0,
387 round(min(nvl(output_amount, 0)), ccyPrecision),
388 round(sum(nvl(output_amount, 0)), ccyPrecision)),
389 decode(min(sql_statement_num), 0,
390 round(min(nvl(output_amount, 0)) / ccyMinAcctUnit, 0) * ccyMinAcctUnit,
391 round(sum(nvl(output_amount, 0)) / ccyMinAcctUnit, 0) * ccyMinAcctUnit)) <> 0;
392 /* Having decode( ccyMinAcctUnit,
393 null, round( sum( nvl(output_amount, 0) ), ccyPrecision ),
394 round( sum( nvl(output_amount,0) ) / ccyMinAcctUnit, 0 ) * ccyMinAcctUnit
395 ) <> 0;*/
396
397 --Bugfix 4925150: Do not execute rules processor if formula evaluates to zero for performance savings
398 CURSOR getEvaluatedFormulas(p_ownership_percent NUMBER, p_rule_id NUMBER) IS
399 SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(FORMULA_TEXT,
400 'ELIMTB',
401 1),
402 'CHILDTB',
403 1),
404 'PARTB',
405 1),
406 '%MI',
407 1 - p_ownership_percent),
408 '%OWN',
409 p_ownership_percent)
410 FROM gcs_elim_rule_steps_b
411 WHERE rule_id = p_rule_id;
412
413 --+========================================================================+
414 -- PACKAGE Private Members
415 --+========================================================================+
416
417 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
418 -- Call this for messages you want to see only when debugging the package
419 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
420 procedure writeToLog(buf IN Varchar2 := NULL) is
421 errBuf Varchar2(5000);
422 begin
423 errBuf := substr(buf, 1, 5000);
424 -- Do nothing if there is no message waiting
425 If errBuf IS NOT NULL Then
426 While errBuf is not null Loop
427 errBuf := substr(errBuf, 251);
428 End Loop;
429 End If;
430
431 end writeToLog;
432
433 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
434 -- This makes embedding logging calls in the other code less intrusive
435 -- and keeps the code more legible.
436 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
437 procedure logString(logLevel Number,
438 logProc Varchar2,
439 logLabel Varchar2,
440 logText Varchar2) is
441
442 rootString varchar2(100);
443
444 errBuf Varchar2(5000);
445
446 i number(15) := 1;
447
448 begin
449 rootString := 'gcs.plsql.GCS_RULE_PROCESSOR.';
450
451 -- May be a message on the stack or
452 -- a string passed in via the arg
453 if logText IS NULL then
454 errBuf := substr(FND_MESSAGE.get, 1, 5000);
455 else
456 errBuf := substr(logText, 1, 5000);
457 end if;
458
459 if logLevel >= runtimeLogLevel then
460 FND_LOG.string(logLevel,
461 rootString || logProc || '.' || logLabel,
462 errBuf);
463 end if;
464
465 -- STK: Bugfix 6242317
466 fnd_file.put_line(fnd_file.log, logText);
467
468 end logString;
469
470 -- changes made by yingliu
471 Procedure process_multiparent(p_entry_id IN Number) IS
472 -- get the multiple parents elimination entities
473 CURSOR c_elim_entities(l_end_date Date) IS
474 SELECT fea.dim_attribute_numeric_member elim_entity_id,
475 delta_owned,
476 gcs_entry_headers_s.nextval,
477 geca.currency_code,
478 nvl(precision, 2) precision
479 FROM gcs_cons_relationships gcr,
480 fem_entities_attr fea,
481 gcs_entity_cons_attrs geca,
482 fnd_currencies fc
483 WHERE gcr.hierarchy_id = contextData.hierarchy
484 AND gcr.child_entity_id = contextData.childEntity
485 AND gcr.actual_ownership_flag = 'N'
486 AND l_end_date between gcr.start_date and
487 nvl(gcr.end_date, l_end_date)
488 AND fea.entity_id = gcr.parent_entity_id
489 AND fea.attribute_id =
490 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY')
491 .attribute_id
492 AND fea.version_id =
493 gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY')
494 .version_id
495 AND geca.hierarchy_id = gcr.hierarchy_id
496 AND geca.entity_id = gcr.parent_entity_id
497 AND geca.currency_code = fc.currency_code;
498
499 -- get the multiple parents consolidation entities
500 CURSOR c_cons_entities(l_end_date Date) IS
501 SELECT gcr.parent_entity_id,
502 delta_owned,
503 gcs_entry_headers_s.nextval,
504 geca.currency_code,
505 nvl(precision, 2) precision
506 FROM gcs_cons_relationships gcr,
507 gcs_entity_cons_attrs geca,
508 fnd_currencies fc
509 WHERE gcr.hierarchy_id = contextData.hierarchy
510 AND gcr.child_entity_id = contextData.childEntity
511 AND gcr.actual_ownership_flag = 'N'
512 AND l_end_date between gcr.start_date and
513 nvl(gcr.end_date, l_end_date)
514 AND geca.hierarchy_id = gcr.hierarchy_id
515 AND geca.entity_id = gcr.parent_entity_id
516 AND geca.currency_code = fc.currency_code;
517
518 -- get the multiple parents child entities
519 CURSOR c_child_entities(l_end_date Date) IS
520 SELECT geh.entity_id,
521 delta_owned,
522 gcs_entry_headers_s.nextval,
523 geh.currency_code,
524 nvl(precision, 2) precision
525 FROM gcs_cons_relationships gcr,
526 gcs_entry_headers geh,
527 fnd_currencies fc
528 WHERE gcr.hierarchy_id = contextData.hierarchy
529 AND gcr.child_entity_id = contextData.childEntity
530 AND gcr.actual_ownership_flag = 'N'
531 AND l_end_date between gcr.start_date and
532 nvl(gcr.end_date, l_end_date)
533 AND geh.entry_id = p_entry_id
534 AND geh.currency_code = fc.currency_code;
535
536 -- get the processing calendar period end date
537 CURSOR c_get_end_date IS
538 SELECT date_assign_value
539 FROM fem_cal_periods_attr
540 where cal_period_id = contextData.calPeriodId
541 AND attribute_id =
542 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
543 .attribute_id
544 AND version_id =
545 gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
546 .version_id;
547
548 -- get the ownership percent for the consolidating relationship
549 CURSOR c_get_ownership IS
550 SELECT ownership_percent
551 FROM gcs_cons_relationships
552 WHERE cons_relationship_id = contextData.relationship;
553
554 CURSOR c_get_target IS
555 SELECT target_entity_code, net_to_re_flag
556 FROM gcs_categories_b
557 WHERE category_code = contextData.eventCategory;
558
559 l_entities DBMS_SQL.number_table;
560 l_percent DBMS_SQL.number_table;
561 l_precision DBMS_SQL.number_table;
562 l_seq DBMS_SQL.number_table;
563 l_currency DBMS_SQL.varchar2_table;
564 l_flag DBMS_SQL.varchar2_table;
565
566 l_end_date DATE;
567 l_rate_var NUMBER;
568 l_ccyPrecision NUMBER;
569 l_target VARCHAR2(30);
570 l_net_to_re_flag VARCHAR2(1);
571 l_owner_percent NUMBER;
572 l_errbuf VARCHAR2(100);
573 l_errcode NUMBER;
574
575 --Used by the call to GCS_TEMPLATES_DYNAMIC_PKG
576 templateRecord GCS_TEMPLATES_PKG.templateRecord;
577
578 procedureName varchar2(30);
579
580 BEGIN
581 procedureName := 'PROCESS_MULTIPARENT';
582
583 logString(procedureLogLevel,
584 procedureName,
585 'begin',
586 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
587
588 OPEN c_get_end_date;
589 FETCH c_get_end_date
590 INTO l_end_date;
591 CLOSE c_get_end_date;
592
593 OPEN c_get_ownership;
594 FETCH c_get_ownership
595 INTO l_owner_percent;
596 CLOSE c_get_ownership;
597
598 OPEN c_get_target;
599 FETCH c_get_target
600 INTO l_target, l_net_to_re_flag;
601 CLOSE c_get_target;
602
603 IF l_target = 'CHILD' THEN
604 OPEN c_child_entities(l_end_date);
605 FETCH c_child_entities BULK COLLECT
606 INTO l_entities, l_percent, l_seq, l_currency, l_precision;
607 CLOSE c_child_entities;
608 ELSIF l_target = 'ELIMINATION' THEN
609 OPEN c_elim_entities(l_end_date);
610 FETCH c_elim_entities BULK COLLECT
611 INTO l_entities, l_percent, l_seq, l_currency, l_precision;
612 CLOSE c_elim_entities;
613 ELSIF l_target = 'CONSOLIDATION' THEN
614 OPEN c_cons_entities(l_end_date);
615 FETCH c_cons_entities BULK COLLECT
616 INTO l_entities, l_percent, l_seq, l_currency, l_precision;
617 CLOSE c_cons_entities;
618 ELSE
619 RAISE invalid_category_code;
620 END IF;
621
622 --Bugfix 4122843 : Check if l_currency is not zero
623 IF (l_currency.COUNT <> 0) THEN
624 FOR i IN l_currency.first .. l_currency.last LOOP
625 gcs_utility_pkg.get_conversion_rate(P_Source_Currency => contextData.CurrencyCode,
626 P_Target_Currency => l_currency(i),
627 p_cal_period_Id => contextData.calPeriodId,
628 p_conversion_rate => l_rate_var,
629 P_errbuf => l_errbuf,
630 p_errcode => l_errcode);
631 l_percent(i) := l_rate_var * l_percent(i) / (100 - l_owner_percent);
632 IF (l_errcode = 2) THEN
633 l_flag(i) := 'X';
634 ELSE
635 l_flag(i) := 'N';
636 END IF;
637 END LOOP;
638 END IF;
639
640 IF (l_entities.COUNT <> 0) THEN
641 FORALL i IN l_entities.first .. l_entities.last
642 INSERT INTO gcs_entry_headers
643 (ENTRY_ID,
644 ENTRY_NAME,
645 HIERARCHY_ID,
646 DISABLED_FLAG,
647 ENTITY_ID,
648 CURRENCY_CODE,
649 BALANCE_TYPE_CODE,
650 START_CAL_PERIOD_ID,
651 END_CAL_PERIOD_ID,
652 YEAR_TO_APPLY_RE,
653 DESCRIPTION,
654 ENTRY_TYPE_CODE,
655 ASSOC_ENTRY_ID,
656 CATEGORY_CODE,
657 PROCESS_CODE,
658 SUSPENSE_EXCEEDED_FLAG,
659 CREATION_DATE,
660 CREATED_BY,
661 LAST_UPDATE_DATE,
662 LAST_UPDATED_BY,
663 LAST_UPDATE_LOGIN,
664 PERIOD_INIT_ENTRY_FLAG,
665 RULE_ID,
666 PROCESSED_RUN_NAME)
667 SELECT l_seq(i),
668 l_seq(i),
669 HIERARCHY_ID,
670 DISABLED_FLAG,
671 l_entities(i),
672 l_currency(i),
673 BALANCE_TYPE_CODE,
674 START_CAL_PERIOD_ID,
675 END_CAL_PERIOD_ID,
676 YEAR_TO_APPLY_RE,
677 DESCRIPTION,
678 'MULTIPLE_PARENTS',
679 p_entry_id,
680 CATEGORY_CODE,
681 PROCESS_CODE,
682 DECODE(SUSPENSE_EXCEEDED_FLAG, 'Y', 'Y', l_flag(i)),
683 CREATION_DATE,
684 CREATED_BY,
685 LAST_UPDATE_DATE,
686 LAST_UPDATED_BY,
687 LAST_UPDATE_LOGIN,
688 PERIOD_INIT_ENTRY_FLAG,
689 ruleId,
690 contextData.runName
691 FROM gcs_entry_headers
692 WHERE entry_id = p_entry_id;
693
694 FORALL i IN l_entities.first .. l_entities.last
695 INSERT INTO gcs_entry_lines
696 (ENTRY_ID,
697 LINE_TYPE_CODE,
698 DESCRIPTION,
699 COMPANY_COST_CENTER_ORG_ID,
700 FINANCIAL_ELEM_ID,
701 PRODUCT_ID,
702 NATURAL_ACCOUNT_ID,
703 CHANNEL_ID,
704 LINE_ITEM_ID,
705 PROJECT_ID,
706 CUSTOMER_ID,
707 INTERCOMPANY_ID,
708 TASK_ID,
709 USER_DIM1_ID,
710 USER_DIM2_ID,
711 USER_DIM3_ID,
712 USER_DIM4_ID,
713 USER_DIM5_ID,
714 USER_DIM6_ID,
715 USER_DIM7_ID,
716 USER_DIM8_ID,
717 USER_DIM9_ID,
718 USER_DIM10_ID,
719 XTD_BALANCE_E,
720 YTD_BALANCE_E,
721 PTD_DEBIT_BALANCE_E,
722 PTD_CREDIT_BALANCE_E,
723 YTD_DEBIT_BALANCE_E,
724 YTD_CREDIT_BALANCE_E,
725 CREATION_DATE,
726 CREATED_BY,
727 LAST_UPDATE_DATE,
728 LAST_UPDATED_BY,
729 LAST_UPDATE_LOGIN)
730 SELECT l_seq(i),
731 LINE_TYPE_CODE,
732 DESCRIPTION,
733 COMPANY_COST_CENTER_ORG_ID,
734 FINANCIAL_ELEM_ID,
735 PRODUCT_ID,
736 NATURAL_ACCOUNT_ID,
737 CHANNEL_ID,
738 LINE_ITEM_ID,
739 PROJECT_ID,
740 CUSTOMER_ID,
741 INTERCOMPANY_ID,
742 TASK_ID,
743 USER_DIM1_ID,
744 USER_DIM2_ID,
745 USER_DIM3_ID,
746 USER_DIM4_ID,
747 USER_DIM5_ID,
748 USER_DIM6_ID,
749 USER_DIM7_ID,
750 USER_DIM8_ID,
751 USER_DIM9_ID,
752 USER_DIM10_ID,
753 NULL,
754 round(nvl(YTD_BALANCE_E, 0) * l_percent(i) * -1,
755 l_precision(i)),
756 NULL,
757 NULL,
758 round(nvl(YTD_CREDIT_BALANCE_E, 0) * l_percent(i),
759 l_precision(i)),
760 round(nvl(YTD_DEBIT_BALANCE_E, 0) * l_percent(i),
761 l_precision(i)),
762 CREATION_DATE,
763 CREATED_BY,
764 LAST_UPDATE_DATE,
765 LAST_UPDATED_BY,
766 LAST_UPDATE_LOGIN
767 FROM gcs_entry_lines
768 WHERE entry_id = p_entry_id;
769
770 --Get the template record together
771 templateRecord.FINANCIAL_ELEM_ID := suspenseData.financial_elem_id;
772 templateRecord.PRODUCT_ID := suspenseData.product_id;
773 templateRecord.NATURAL_ACCOUNT_ID := suspenseData.natural_account_id;
774 templateRecord.CHANNEL_ID := suspenseData.channel_id;
775 templateRecord.LINE_ITEM_ID := suspenseData.line_item_id;
776 templateRecord.PROJECT_ID := suspenseData.project_id;
777 templateRecord.CUSTOMER_ID := suspenseData.customer_id;
778 templateRecord.TASK_ID := suspenseData.task_id;
779 templateRecord.USER_DIM1_ID := suspenseData.user_dim1_id;
780 templateRecord.USER_DIM2_ID := suspenseData.user_dim2_id;
781 templateRecord.USER_DIM3_ID := suspenseData.user_dim3_id;
782 templateRecord.USER_DIM4_ID := suspenseData.user_dim4_id;
783 templateRecord.USER_DIM5_ID := suspenseData.user_dim5_id;
784 templateRecord.USER_DIM6_ID := suspenseData.user_dim6_id;
785 templateRecord.USER_DIM7_ID := suspenseData.user_dim7_id;
786 templateRecord.USER_DIM8_ID := suspenseData.user_dim8_id;
787 templateRecord.USER_DIM9_ID := suspenseData.user_dim9_id;
788 templateRecord.USER_DIM10_ID := suspenseData.user_dim10_id;
789
790 FOR i IN l_entities.first .. l_entities.last LOOP
791
792 IF (l_net_to_re_flag = 'Y') THEN
793 BEGIN
794 GCS_TEMPLATES_DYNAMIC_PKG.calculate_re(p_entry_id => l_seq(i),
795 p_hierarchy_id => contextData.hierarchy,
796 p_bal_type_code => 'ACTUAL',
797 p_entity_id => l_entities(i));
798 EXCEPTION
799 WHEN OTHERS THEN
800 logString(exceptionLogLevel,
801 procedureName,
802 'exception',
803 'templates_pkg_error');
804 logString(exceptionLogLevel,
805 procedureName,
806 'exception',
807 'procedure "calculate_re" fail');
808 logString(exceptionLogLevel,
809 procedureName,
810 'exception',
811 null);
812 RAISE templates_pkg_error;
813 END;
814 END IF;
815
816 BEGIN
817 GCS_TEMPLATES_DYNAMIC_PKG.balance(p_entry_id => l_seq(i),
818 p_template => templateRecord,
819 p_bal_type_code => 'ACTUAL',
820 p_hierarchy_id => contextData.hierarchy,
821 p_entity_id => l_entities(i),
822 p_threshold => suspenseData.threshold_amount,
823 p_threshold_currency_code => suspenseData.threshold_currency);
824 EXCEPTION
825 WHEN OTHERS THEN
826 logString(exceptionLogLevel,
827 procedureName,
828 'exception',
829 'templates_pkg_error');
830 logString(exceptionLogLevel,
831 procedureName,
832 'exception',
833 'procedure "balance" fail');
834 logString(exceptionLogLevel, procedureName, 'exception', null);
835 RAISE templates_pkg_error;
836 END;
837 END LOOP;
838
839 END IF; --check entity count;
840
841 logString(procedureLogLevel,
842 procedureName,
843 'end',
844 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
845
846 END process_multiparent;
847 -- end of changes by yingliu
848
849 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
850 -- This procedure initializes a bunch of hash tables for use throughout
851 -- the package. To wit...
852 -- A vc2 table with the various FROM and WHERE clauses for the dynamic
853 -- SQL used to execute the formulas.
854 -- A cursor%rowtype table with dimension info used to construct
855 -- insert, select and from expressions dynamically.
856 --
857 -- It also gets the application group and global value set combination.
858 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
859 procedure initRefTables is
860
861 procedureName varchar2(30);
862
863 -- FEM procedures use these
864 errCount Number := 0;
865
866 i Varchar2(30);
867
868 begin
869 procedureName := 'INIT_REF_TABLES';
870
871 logString(procedureLogLevel,
872 procedureName,
873 'begin',
874 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
875
876 --Fill the stmt array
877 stmts.DELETE;
878 Open getStmts(contextData.eventType);
879 Fetch getStmts Bulk Collect
880 Into stmts;
881 Close getStmts;
882
883 If nvl(stmts.COUNT, 0) = 0 Then
884 RAISE invalid_gcs_setup;
885 End if;
886
887 --=======================================================
888 logString(eventLogLevel, procedureName, 'section', '1');
889 --=======================================================
890
891 dimInfo.DELETE;
892 dimInfo := GCS_UTILITY_PKG.g_gcs_dimension_info;
893 If dimInfo.COUNT > 0 Then
894 i := dimInfo.FIRST;
895 While i IS NOT NULL Loop
896 --We do not want to include some dims in the dynamic sql used here.
897 --The dynamic sql processes the dims elligible for inclusion in
898 --dim_sets, regardless of whether a particular dim_set uses them,
899 --plus the cctr_org and the interco dims. All others can go away.
900 if dimInfo(i).column_name in ('DATASET_CODE',
901 'CAL_PERIOD_ID',
902 'LEDGER_ID',
903 'SOURCE_SYSTEM_CODE',
904 'ENTITY_ID') then
905 dimInfo.DELETE(i);
906 else
907 logString(statementLogLevel,
908 procedureName,
909 'dimension',
910 'Name, ID, FEM?, GCS? = ' || dimInfo(i)
911 .column_name || ', ' || to_char(dimInfo(i).dimension_id) || ', ' ||
912 dimInfo(i).required_for_fem || ', ' || dimInfo(i)
913 .required_for_gcs);
914 end if;
915 i := dimInfo.NEXT(i);
916 End Loop; --While i IS NOT NULL
917
918 End If;
919
920 --jh 4.26.04. Check for dims after system-oriented dims have been deleted.
921 If dimInfo.COUNT = 0 Then
922 RAISE invalid_fem_setup;
923 end if;
924
925 logString(procedureLogLevel,
926 procedureName,
927 'end',
928 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
929
930 end initRefTables;
931
932 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
933 -- Writes the incoming parameter values to the database log
934 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
935 procedure logParameterValues is
936
937 --This proc is here just so the process_rule proc is not
938 --cluttered with these lines of logging calls
939 procedureName varchar2(30);
940
941 begin
942 procedureName := 'PROCESS_RULE';
943
944 --NOTE: Blowing off the usual procedure-level log (begin and end messages)
945 -- here, since the entire thing just writes log entries and will only
946 -- even be visible if statement-level logging is enabled anyway.
947 -- That is also why the procedure_name here is defined as process_rule
948 -- and not as logparameterValues.
949
950 logString(statementLogLevel,
951 procedureName,
952 'parameter',
953 'Rule ID = ' || to_char(ruleId));
954 logString(statementLogLevel,
955 procedureName,
956 'parameter',
957 '%FROM Value = ' || to_char(ruleData.fromPercent));
958 logString(statementLogLevel,
959 procedureName,
960 'parameter',
961 '%TO Value = ' || to_char(ruleData.toPercent));
962 logString(statementLogLevel,
963 procedureName,
964 'parameter',
965 '%OWN Value = ' || to_char(ruleData.toPercent));
966 logString(statementLogLevel,
967 procedureName,
968 'parameter',
969 'Consideration Amount = ' || to_char(ruleData.consideration));
970 logString(statementLogLevel,
971 procedureName,
972 'parameter',
973 'Fair Market Value = ' || to_char(ruleData.netAssetValue));
974 logString(statementLogLevel,
975 procedureName,
976 'parameter',
977 'Event Type = ' || contextData.eventType);
978 logString(statementLogLevel,
979 procedureName,
980 'parameter',
981 'Event Key = ' || to_char(contextData.eventKey));
982 logString(statementLogLevel,
983 procedureName,
984 'parameter',
985 'Parent Entity = ' ||
986 to_char(contextData.parentEntity));
987 logString(statementLogLevel,
988 procedureName,
989 'parameter',
990 'Child Entity = ' || to_char(contextData.childEntity));
991 logString(statementLogLevel,
992 procedureName,
993 'parameter',
994 'Eliminations Entity = ' || to_char(contextData.elimsEntity));
995 logString(statementLogLevel,
996 procedureName,
997 'parameter',
998 'Dataset Code = ' || to_char(contextData.datasetCode));
999 logString(statementLogLevel,
1000 procedureName,
1001 'parameter',
1002 'Relationship = ' ||
1003 to_char(contextData.relationship));
1004 logString(statementLogLevel,
1005 procedureName,
1006 'parameter',
1007 'Hierarchy = ' || to_char(contextData.hierarchy));
1008 logString(statementLogLevel,
1009 procedureName,
1010 'parameter',
1011 'Calendar Period Id = ' || to_char(contextData.calPeriodId));
1012 logString(statementLogLevel,
1013 procedureName,
1014 'parameter',
1015 'Currency Code = ' || contextData.currencyCode);
1016
1017 end logParameterValues;
1018
1019 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1020 -- Construct a set of join conditions in the following style...
1021 -- AND left.column_name = right.column_name
1022 --
1023 -- This procedure uses the dimInfo hash table.
1024 --
1025 -- This "plain vanilla" string can be manipulated to use whatever table
1026 -- aliases are appropriate to the stmts( stmt ).sql_statement.
1027 --
1028 -- See execFormulas for how this is used.
1029 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1030 procedure modelJoinClause is
1031
1032 procedureName varchar2(30);
1033 i varchar2(30);
1034
1035 begin
1036 procedureName := 'MODEL_JOIN_CLAUSE';
1037
1038 logString(procedureLogLevel,
1039 procedureName,
1040 'begin',
1041 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1042
1043 modJoinClause := null;
1044 i := dimInfo.FIRST;
1045 While i IS NOT NULL Loop
1046 if dimInfo(i).required_for_gcs = 'Y' then
1047 modJoinClause := modJoinClause || '
1048 AND left.' || rpad(dimInfo(i).column_name, 30) ||
1049 ' = right.' || dimInfo(i).column_name;
1050 end if;
1051 i := dimInfo.NEXT(i);
1052 End Loop;
1053
1054 -- Show the statement in the logfile
1055 logString(statementLogLevel,
1056 procedureName,
1057 'stmt',
1058 'Model Join Clause = ' || modJoinClause);
1059
1060 logString(procedureLogLevel,
1061 procedureName,
1062 'end',
1063 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1064
1065 end modelJoinClause;
1066
1067 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1068 -- Initialize data in the GCS_ENTRIES_GT table by creating source and
1069 -- target dimensions, and setting related "friendly" data like names, formula.
1070 --
1071 -- FOR TARGET-ONLY dimension sets: see proc initEntriesGT_tgtDimSet elsewhere.
1072 --
1073 -- FOR STANDARD dimension sets:
1074 --
1075 -- The SQL stmt used to init the gcs_entries_gt table looks like...
1076 --
1077 -- INSERT INTO GCS_ENTRIES_GT (
1078 -- rule_id, step_seq, formula_text,
1079 -- ad_input_amount, pe_input_amount, ce_input_amount, ee_input_amount,
1080 -- output_amount,
1081 -- <src_dimensions>,
1082 -- <tgt_dimensions>)
1083 -- SELECT :rid, :seq, :ftx,
1084 -- 0, 0,
1085 -- <b.source_dimension_columns>,
1086 -- <d.target_dimension_expressions>
1087 -- FROM fem_balances b,
1088 -- gcs_rule_scope_dims d
1089 -- WHERE b.source_dimension_columns =
1090 -- (select t.source_member_id
1091 -- from gcs_rule_scope_dtls t
1092 -- where t.rule_step_id = :rsi) ;
1093 --
1094 --
1095 -- For the cctr_org and interco dimensions, the target value is always
1096 -- copied from the source value. For the other dimensions, the target
1097 -- expressions are based on whether...
1098 -- a) ...the target template is a value
1099 -- then use the target's value as a constant
1100 --
1101 -- b) ...the target template is NULL
1102 -- then use the source's same-dimension value
1103 --
1104 -- An expression in the form of...
1105 -- decode( <xdim>,
1106 -- NULL, l.src_<xdim>,
1107 -- <xdim>)
1108 -- ...is constructed for each selected dimension in the dim set.
1109 --
1110 -- This procedure makes use of expression strings created elsewhere...
1111 --
1112 -- selColumnList: has expressions in the form ', b.<xdim>' for
1113 -- the current dim set id (see resolveDimSet)
1114 -- srcColumnList: created here as replace(selColumnList,', b.',', src_')
1115 -- so if the selColumnList = b.moe, b.larry, b.curly then
1116 -- the srcColumnList would come out as src_moe, src_larry,
1117 -- src_curly.
1118 -- tgtColumnList: created here as replace(selColumnList,', b.',', tgt_')
1119 -- so if the selColumnList = b.moe, b.larry, b.curly then
1120 -- the tgtColumnList would come out as tgt_moe, tgt_larry,
1121 -- tgt_curly.
1122 -- insColumnList: has expressions in the form ', <xdim>' for
1123 -- the current dim set id (see resolveDimSet)
1124 -- adtColumnList: has expressions in the form 'AND E.<xdim> = B.<xdim>'
1125 -- for the join condition to gcs_ad_trial_balances.
1126 --
1127 -- NOTE: Abbreviations used for bind vars here...
1128 -- ccy = currency code id contextData.currencyCode
1129 -- dci = dataset code contextData.datasetCode
1130 -- dsi = dim set id dimSetId (private global variable)
1131 -- dsn = dim set name dimSets(dsi).dimension_set_name
1132 -- dst = dim set type dimSets(dsi).dimension_set_type_code
1133 -- eid = entity id contextData.elimsEntity
1134 -- ftx = formula text dimSet(dsi).formula_text
1135 -- seq = step sequence stepSeq (procedure argument)
1136 -- rid = rule id stepData(seq).rule_id
1137 -- rsi = rule step id stepData(seq).rule_step_id
1138 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1139 procedure initEntriesGT_stdDimSet(stepSeq IN NUMBER,
1140 categoryInfo IN getCategory%ROWTYPE) is
1141
1142 procedureName varchar2(30);
1143 entriesStmt varchar2(20000);
1144 oEntriesStmt varchar2(20000);
1145 whereClause varchar2(5000);
1146 tgtDimStmt varchar2(5000);
1147 oTgtDimStmt varchar2(5000);
1148 fromList varchar2(2500);
1149 i varchar2(30);
1150 j number;
1151 orgId number := -1;
1152 intercoId number := -1;
1153 tgtOrg varchar2(100);
1154 cEntityType varchar2(2); --whether child entity is operating or consolidation
1155 offsetFlag varchar2(2); --whether to create an offset line or not.
1156
1157 -- changes made by yingliu:
1158 /*
1159 cursor getSpecificIntercoId is
1160 SELECT SPECIFIC_INTERCOMPANY_ID
1161 FROM GCS_HIERARCHIES_B
1162 WHERE hierarchy_id = contextData.hierarchy;
1163 */
1164 cursor getSpecificIntercoId is
1165 SELECT SPECIFIC_INTERCOMPANY_ID
1166 FROM GCS_CATEGORIES_B
1167 WHERE CATEGORY_CODE = 'INTRACOMPANY';
1168 -- end of change by yingliu
1169
1170 cursor getChildEntityType is
1171 SELECT dim_attribute_varchar_member
1172 FROM FEM_ENTITIES_ATTR
1173 WHERE entity_id = nvl(contextData.childEntity, -1)
1174 AND attribute_id =
1175 GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE')
1176 .attribute_id
1177 AND version_id =
1178 GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE')
1179 .version_id
1180 AND value_set_id =
1181 GCS_UTILITY_PKG.g_gcs_dimension_info('ENTITY_ID')
1182 .associated_value_set_id;
1183
1184 begin
1185 procedureName := 'INIT_ENTRIESGT_STD_DIMSET';
1186 tgtOrg := '';
1187
1188 logString(procedureLogLevel,
1189 procedureName,
1190 'begin',
1191 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
1192
1193 --Global states may persist since last time we did this, so reset them
1194 selColumnList := null;
1195 adtColumnList := null;
1196 j := 0;
1197 offsetFlag := 'N';
1198
1199 i := dimInfo.FIRST;
1200 While i IS NOT NULL Loop
1201
1202 --HANDLE DIM_SET_DIMS DIFFERENTLY THAN OTHER GCS DIMS: by looping
1203 --through the dimInfo array and using the index there in the
1204 --selectDims array, when we hit a dim that is not selected for
1205 --this Dim Set a no_data_found is thrown. The body of this nested
1206 --block handles selectDims columns while the no_data_found handler
1207 --handles the non-selectDims columns.
1208 BEGIN
1209
1210 --select and insert expressions
1211 selColumnList := selColumnList || ',
1212 b.' || selectDims(i).column_name;
1213
1214 IF contextData.eventType = 'A' THEN
1215 adtColumnList := adtColumnList || '
1216 AND e.src_' || selectDims(i)
1217 .column_name || ' = b.' || selectDims(i)
1218 .column_name;
1219 END IF; -- IF contextData.eventType = 'A' THEN
1220
1221 EXCEPTION
1222 WHEN NO_DATA_FOUND THEN
1223 -- Tag the insert and select lists with active-not-selected dims
1224 if dimInfo(i).required_for_gcs = 'Y' then
1225 selColumnList := selColumnList || ',
1226 b.' || dimInfo(i).column_name;
1227
1228 IF contextData.eventType = 'A' THEN
1229 adtColumnList := adtColumnList || '
1230 AND e.src_' || dimInfo(i)
1231 .column_name || ' = b.' || dimInfo(i)
1232 .column_name;
1233 END IF; -- IF contextData.eventType = 'A' THEN
1234
1235 end if;
1236
1237 END;
1238
1239 i := dimInfo.NEXT(i);
1240
1241 End Loop; --While i IS NOT NULL Loop
1242
1243 -- Set up a target expression list using the source list created
1244 -- in resolveDimSet above
1245 logString(statementLogLevel,
1246 procedureName,
1247 'stmt',
1248 'selColumnList = ' || selColumnList);
1249
1250 IF contextData.eventType = 'A' THEN
1251 logString(statementLogLevel,
1252 procedureName,
1253 'stmt',
1254 'adtColumnList = ' || adtColumnList);
1255 END IF; --IF contextData.eventType = 'A' THEN
1256
1257 srcColumnList := replace(selColumnList, 'b.', 'src_');
1258 logString(statementLogLevel,
1259 procedureName,
1260 'stmt',
1261 'srcColumnList = ' || srcColumnList);
1262
1263 tgtColumnList := replace(selColumnList, 'b.', 'tgt_');
1264 logString(statementLogLevel,
1265 procedureName,
1266 'stmt',
1267 'tgtColumnList = ' || tgtColumnList);
1268
1269 --find out if child entity is a consolidation entity.
1270 Open getChildEntityType;
1271 Fetch getChildEntityType
1272 INTO cEntityType;
1273
1274 If getChildEntityType%NOTFOUND then
1275 cEntityType := 'N'; --set entity_type to 'N' for none
1276 End if;
1277
1278 Close getChildEntityType;
1279
1280 logString(statementLogLevel,
1281 procedureName,
1282 'parameter',
1283 'child entity type = ' || cEntityType);
1284
1285 --=======================================================
1286 logString(eventLogLevel, procedureName, 'section', '2');
1287 --=======================================================
1288
1289 -- Start the statement
1290 entriesStmt := 'INSERT INTO GCS_ENTRIES_GT (
1291 rule_id, step_seq, step_name, formula_text, rule_step_id, offset_flag,
1292 sql_statement_num, currency_code, ad_input_amount, pe_input_amount,
1293 ce_input_amount, ee_input_amount, output_amount, entity_id,
1294 ytd_credit_balance_e, ytd_debit_balance_e' ||
1295 srcColumnList || tgtColumnList || ')
1296 SELECT DISTINCT :rid, :seq, :sna, :ftx, :rsi, :osf, :stn, b.currency_code,
1297 0, 0, 0, 0, 0,
1298 b.entity_id,
1299 b.ytd_credit_balance_e,
1300 b.ytd_debit_balance_e' || selColumnList;
1301
1302 whereClause := '
1303 AND ( b.entity_id in ( :pid, :cid )
1304 OR ( b.entity_id = :eid
1305 AND b.COMPANY_COST_CENTER_ORG_ID IN(
1306 SELECT o.company_cost_center_org_id
1307 FROM GCS_ENTITY_CCTR_ORGS o
1308 WHERE ( o.entity_id = :cid';
1309
1310 --If child is a consolidation entity, then look for org's of its children.
1311 IF (cEntityType = 'C') THEN
1312 whereClause := whereClause || '
1313 OR
1314 o.entity_id IN(
1315 SELECT r.child_entity_id
1316 FROM GCS_CONS_RELATIONSHIPS r
1317 START WITH r.parent_entity_id = :cid
1318 AND r.hierarchy_id = :hid
1319 AND r.actual_ownership_flag = ''Y''
1320 AND ( sysdate BETWEEN r.start_date
1321 AND NVL(r.end_date, sysdate))
1322 CONNECT BY prior r.child_entity_id = r.parent_entity_id
1323 AND r.hierarchy_id = :hid
1324 AND r.actual_ownership_flag = ''Y''
1325 AND ( sysdate BETWEEN r.start_date
1326 AND NVL(r.end_date, sysdate)))';
1327 END IF; --IF (l_entity_type= 'C')
1328 whereClause := whereClause || '
1329 ))))';
1330
1331 --=======================================================
1332 logString(eventLogLevel, procedureName, 'section', '3');
1333 --=======================================================
1334
1335 -- Add the selected dim target expressions
1336 i := dimInfo.FIRST;
1337 tgtDimStmt := '';
1338 oTgtDimStmt := '';
1339 fromList := '';
1340
1341 While i IS NOT NULL Loop
1342
1343 --Handle the dim_set_dims differently: by looping through the dimInfo
1344 --array and using the index there in the selectDims array, when we hit
1345 --a dim that is not selected for this Dim Set a no_data_found is thrown.
1346 --The body of this nested block handles selectDims columns while the
1347 --no_data_found handler handles the non-selectDims columns.
1348
1349 BEGIN
1350 j := j + 1;
1351
1352 --jh 10.19.04: add all-source-member support
1353
1354 IF selectDims(i).all_source_members_flag = 'Y' THEN
1355
1356 IF selectDims(i).target_member_id IS NULL THEN
1357 tgtDimStmt := tgtDimStmt || ',
1358 b.' || selectDims(i).column_name;
1359
1360 oTgtDimStmt := oTgtDimStmt || ',
1361 b.' || selectDims(i).column_name;
1362
1363 ELSE
1364 tgtDimStmt := tgtDimStmt || ',
1365 ' || selectDims(i).TARGET_MEMBER_ID;
1366
1367 IF selectDims(i).offset_member_id IS NOT NULL THEN
1368 oTgtDimStmt := oTgtDimStmt || ',
1369 ' || selectDims(i).OFFSET_MEMBER_ID;
1370 ELSE
1371 oTgtDimStmt := oTgtDimStmt || ',
1372 ' || selectDims(i).TARGET_MEMBER_ID;
1373 END IF; --IF selectDims(i).offset_member_id IS NOT NULL THEN
1374
1375 END IF; --IF selectDims(i).target_member_id IS NULL THEN
1376
1377 ELSE
1378 IF selectDims(i).target_member_id IS NULL THEN
1379 tgtDimStmt := tgtDimStmt || ',
1380 b.' || selectDims(i).column_name;
1381
1382 oTgtDimStmt := oTgtDimStmt || ',
1383 b.' || selectDims(i).column_name;
1384
1385 ELSE
1386 tgtDimStmt := tgtDimStmt || ',
1387 ' || selectDims(i).TARGET_MEMBER_ID;
1388
1389 IF selectDims(i).offset_member_id IS NOT NULL THEN
1390 oTgtDimStmt := oTgtDimStmt || ',
1391 ' || selectDims(i).OFFSET_MEMBER_ID;
1392 ELSE
1393 oTgtDimStmt := oTgtDimStmt || ',
1394 ' || selectDims(i).TARGET_MEMBER_ID;
1395 END IF; --IF selectDims(i).offset_member_id IS NOT NULL THEN
1396
1397 END IF; --IF selectDims(i).target_member_id IS NULL THEN
1398
1399 fromList := fromList || ',
1400 GCS_RULE_SCOPE_DTLS D' || j;
1401
1402 /*
1403 whereClause := whereClause || '
1404 AND ((b.' || selectDims(i).column_name || ' = D' || j || '.source_member_id
1405 AND D' || j || '.rule_step_id = ' || selectDims(i).rule_step_id ||'
1406 AND D' || j || '.column_name = ''' || selectDims(i).column_name ||''')';
1407 */
1408
1409 --jh 08.04.04: Bug 3802514
1410 IF selectDims(i).hierarchy_obj_id is null THEN
1411 -- whereClause := whereClause || ')';
1412 whereClause := whereClause || '
1413 AND (b.' || selectDims(i).column_name || ' = D' || j ||
1414 '.source_member_id';
1415 ELSE
1416 whereClause := whereClause || '
1417 AND b.' || selectDims(i)
1418 .column_name || ' IN (
1419 SELECT h.child_id
1420 FROM ' || selectDims(i).hierarchy_table_name || ' h
1421 WHERE h.hierarchy_obj_def_id = ' ||
1422 selectDims(i).hierarchy_obj_id || '
1423 AND h.parent_value_set_id = ' ||
1424 dimInfo(i).associated_value_set_id || '
1425 AND h.child_value_set_id = h.parent_value_set_id
1426 AND h.parent_id = D' || j ||
1427 '.source_member_id';
1428
1429 END IF; --if selectDims(r).hierarchy_object IS NULL then
1430
1431 whereClause := whereClause || '
1432 AND D' || j || '.rule_step_id = ' ||
1433 selectDims(i)
1434 .rule_step_id || '
1435 AND D' || j || '.column_name = ''' ||
1436 selectDims(i).column_name || ''')';
1437
1438 END IF; --IF selectDims(i).all_source_members_flag = 'Y' THEN
1439
1440 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1441 -- Offset Support (11/1/04)
1442 -- For each rule scope, the user should be able to define a set
1443 -- of TARGET dimensions to place the result of the formula, and a
1444 -- set of OFFSET dimensions to place the (-1) if the result.
1445 -- Note
1446 -- The variables used here are :
1447 -- offsetFlag: Whether an offset should be created. This
1448 -- variable is updated to "Y" when any active
1449 -- dimensions has an offset dimension specified.
1450 -- oTgtDimStmt: Offset target dimensions.
1451 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1452
1453 IF selectDims(i).offset_member_id IS NOT NULL THEN
1454 offsetFlag := 'Y';
1455 END IF; --IF selectDims(i).offset_member_id IS NOT NULL THEN
1456
1457 EXCEPTION
1458 WHEN NO_DATA_FOUND THEN
1459
1460 if dimInfo(i).required_for_gcs = 'Y' then
1461
1462 --See bug 3710985 for details on how cctr and
1463 --interco values are determined (jh 06.29.04)
1464 --JH 7.28.94: use categoryInfo.entityId to get base_org (3798215)
1465 if dimInfo(i).column_name = cctr_column then
1466 if categoryInfo.org_output_code = 'BASE_ORG' then
1467 orgId := GCS_UTILITY_PKG.get_org_id(categoryInfo.entityId,
1468 contextData.hierarchy);
1469 if orgId = -1 then
1470 logString(exceptionLogLevel,
1471 procedureName,
1472 'bind',
1473 'categoryInfo.entityId => ' ||
1474 to_char(categoryInfo.entityId));
1475 logString(exceptionLogLevel,
1476 procedureName,
1477 'bind',
1478 'contextData.hierarchy => ' ||
1479 to_char(contextData.hierarchy));
1480 RAISE no_default_cctr_found;
1481 end if; --if orgId = -2
1482
1483 tgtOrg := to_char(orgId);
1484
1485 --jh 11.05.04: add CHILD_BASE_ORG
1486 elsif categoryInfo.org_output_code = 'CHILD_BASE_ORG' then
1487 orgId := GCS_UTILITY_PKG.get_org_id(contextData.childEntity,
1488 contextData.hierarchy);
1489 if orgId = -1 then
1490 logString(exceptionLogLevel,
1491 procedureName,
1492 'bind',
1493 'categoryInfo.entityId => ' ||
1494 to_char(contextData.childEntity));
1495 logString(exceptionLogLevel,
1496 procedureName,
1497 'bind',
1498 'contextData.hierarchy => ' ||
1499 to_char(contextData.hierarchy));
1500 RAISE no_default_cctr_found;
1501 end if; --if orgId = -2
1502
1503 tgtOrg := to_char(orgId);
1504
1505 elsif categoryInfo.org_output_code = 'SAME_AS_SOURCE' then
1506 tgtOrg := 'b.' || dimInfo(i).column_name;
1507
1508 end if; --if categoryInfo.org_output_code='BASE_ORG'
1509
1510 tgtDimStmt := tgtDimStmt || ',
1511 ' || tgtOrg;
1512
1513 oTgtDimStmt := oTgtDimStmt || ',
1514 ' || tgtOrg;
1515
1516 elsif dimInfo(i).column_name = interco_column THEN
1517
1518 -- changes made by yingliu
1519 /*
1520 if categoryInfo.interco_output_code ='SPECIFIC_VALUE' then
1521 Open getSpecificIntercoId;
1522 Fetch getSpecificIntercoId into intercoId;
1523 Close getSpecificIntercoId;
1524
1525 if intercoId = -1 then
1526 logString( exceptionLogLevel, procedureName, 'bind',
1527 'contextData.childEntity => ' || to_char(contextData.childEntity));
1528 logString( exceptionLogLevel, procedureName, 'bind',
1529 'contextData.hierarchy => ' || to_char(contextData.hierarchy));
1530 logString( exceptionLogLevel, procedureName, 'bind',
1531 'contextData.relationship => ' || contextData.relationship);
1532 RAISE no_default_cctr_found;
1533 end if; -- if intercoId = -1
1534
1535 tgtDimStmt := tgtDimStmt || ',
1536 ' || to_char(intercoId);
1537
1538 oTgtDimStmt := oTgtDimStmt || ',
1539 ' || to_char(intercoId);
1540
1541 elsif categoryInfo.interco_output_code ='SAME_AS_TARGET_ORG' then
1542 tgtDimStmt := tgtDimStmt || ',
1543 ' || tgtOrg;
1544
1545 oTgtDimStmt := oTgtDimStmt || ',
1546 ' || tgtOrg;
1547
1548 elsif categoryInfo.interco_output_code ='SAME_AS_SOURCE_ORG' then
1549
1550 tgtDimStmt := tgtDimStmt || ',
1551 b.' || cctr_column;
1552
1553 oTgtDimStmt := oTgtDimStmt || ',
1554 b.' || cctr_column;
1555
1556 elsif categoryInfo.interco_output_code ='SAME_AS_SOURCE' then
1557
1558 tgtDimStmt := tgtDimStmt || ',
1559 b.' || dimInfo(i).column_name;
1560
1561 oTgtDimStmt := oTgtDimStmt || ',
1562 b.' || dimInfo(i).column_name;
1563 end if; --categoryInfo.interco_output_code ='SPECIFIC_VALUE'
1564 */
1565 Open getSpecificIntercoId;
1566 Fetch getSpecificIntercoId
1567 into intercoId;
1568 Close getSpecificIntercoId;
1569
1570 IF intercoId IS NULL THEN
1571 IF categoryInfo.org_output_code = 'CHILD_BASE_ORG' THEN
1572 orgId := GCS_UTILITY_PKG.get_org_id(contextData.childEntity,
1573 contextData.hierarchy);
1574 IF orgId = -1 then
1575 logString(exceptionLogLevel,
1576 procedureName,
1577 'bind',
1578 'contextData.childEntity => ' ||
1579 to_char(contextData.childEntity));
1580 logString(exceptionLogLevel,
1581 procedureName,
1582 'bind',
1583 'contextData.hierarchy => ' ||
1584 to_char(contextData.hierarchy));
1585 RAISE no_default_cctr_found;
1586 END if; --if orgId = -1
1587
1588 ELSE
1589 orgId := GCS_UTILITY_PKG.get_org_id(categoryInfo.entityId,
1590 contextData.hierarchy);
1591 IF orgId = -1 then
1592 logString(exceptionLogLevel,
1593 procedureName,
1594 'bind',
1595 'categoryInfo.entityId => ' ||
1596 to_char(categoryInfo.entityId));
1597 logString(exceptionLogLevel,
1598 procedureName,
1599 'bind',
1600 'contextData.hierarchy => ' ||
1601 to_char(contextData.hierarchy));
1602 RAISE no_default_cctr_found;
1603 END if; --if orgId = -1
1604
1605 END IF; --IF categoryInfo.org_output_code='CHILD_BASE_ORG'
1606
1607 tgtDimStmt := tgtDimStmt || ',
1608 ' || to_char(orgId);
1609
1610 oTgtDimStmt := oTgtDimStmt || ',
1611 ' || to_char(orgId);
1612
1613 ELSE
1614 tgtDimStmt := tgtDimStmt || ',
1615 ' || to_char(intercoId);
1616 oTgtDimStmt := oTgtDimStmt || ',
1617 ' || to_char(intercoId);
1618 END IF; --IF intercoId IS NULL THEN
1619
1620 -- end of changes by yingliu
1621 else
1622
1623 tgtDimStmt := tgtDimStmt || ',
1624 b.' || dimInfo(i).column_name;
1625
1626 oTgtDimStmt := oTgtDimStmt || ',
1627 b.' || dimInfo(i).column_name;
1628 end if; --dimInfo(i).column_name = cctr_column
1629
1630 END IF; --dimInfo(i).required_for_gcs = 'Y'
1631 END;
1632 i := dimInfo.NEXT(i);
1633
1634 End Loop; --While i IS NOT NULL
1635 --=======================================================
1636 logString(eventLogLevel, procedureName, 'section', '4');
1637 --=======================================================
1638
1639 -- Add the FROM and WHERE clauses
1640 entriesStmt := entriesStmt || tgtDimStmt || '
1641 FROM fem_balances b ' || fromList || '
1642 WHERE b.dataset_code = :dci
1643 AND b.cal_period_id = :cpi
1644 AND b.currency_code IN (:ccy, ''STAT'')' || whereClause;
1645
1646 ruleStepId := stepData(stepSeq).rule_step_id;
1647
1648 -- Execute the stmt
1649 logString(statementLogLevel,
1650 procedureName,
1651 'bind',
1652 'rid = ' || to_char(stepData(stepSeq).rule_id));
1653 logString(statementLogLevel,
1654 procedureName,
1655 'bind',
1656 'seq = ' || to_char(stepSeq));
1657 logString(statementLogLevel,
1658 procedureName,
1659 'bind',
1660 'sna = ' || stepData(stepSeq).step_name);
1661 logString(statementLogLevel,
1662 procedureName,
1663 'bind',
1664 'ftx = ' || stepData(stepSeq).formula_text);
1665 logString(statementLogLevel,
1666 procedureName,
1667 'bind',
1668 'rsi = ' || to_char(stepData(stepSeq).rule_step_id));
1669 logString(statementLogLevel,
1670 procedureName,
1671 'bind',
1672 'stn = ' || to_char(stepData(stepSeq).sql_statement_num));
1673 logString(statementLogLevel,
1674 procedureName,
1675 'bind',
1676 'dci = ' || to_char(contextData.datasetCode));
1677 logString(statementLogLevel,
1678 procedureName,
1679 'bind',
1680 'cpi = ' || to_char(contextData.calPeriodId));
1681 logString(statementLogLevel,
1682 procedureName,
1683 'bind',
1684 'ccy = ' || contextData.currencyCode);
1685 logString(statementLogLevel,
1686 procedureName,
1687 'bind',
1688 'pid = ' || to_char(contextData.parentEntity));
1689 logString(statementLogLevel,
1690 procedureName,
1691 'bind',
1692 'cid = ' || to_char(contextData.childEntity));
1693 logString(statementLogLevel,
1694 procedureName,
1695 'bind',
1696 'eid = ' || to_char(contextData.elimsEntity));
1697 logString(statementLogLevel,
1698 procedureName,
1699 'bind',
1700 'hid = ' || to_char(contextData.hierarchy));
1701
1702 --Consolidation rule
1703 IF contextData.eventType = 'C' THEN
1704
1705 -- Show the statement in the logfile
1706 logString(statementLogLevel,
1707 procedureName,
1708 'stmt',
1709 'entriesStmt = ' || entriesStmt);
1710
1711 --=======================================================
1712 logString(eventLogLevel, procedureName, 'section', '5');
1713 --=======================================================
1714 IF (cEntityType = 'C') THEN
1715 EXECUTE IMMEDIATE entriesStmt
1716 USING stepData(stepSeq).rule_id,
1717 stepSeq,
1718 stepData(stepSeq).step_name,
1719 stepData(stepSeq).formula_text,
1720 ruleStepId,
1721 'N',
1722 stepData(stepSeq).sql_statement_num,
1723 contextData.datasetCode,
1724 contextData.calPeriodId,
1725 contextData.currencyCode,
1726 nvl(contextData.parentEntity, -1),
1727 nvl(contextData.childEntity, -1),
1728 nvl(contextData.elimsEntity, -1),
1729 nvl(contextData.childEntity, -1),
1730 nvl(contextData.childEntity, -1),
1731 contextData.hierarchy,
1732 contextData.hierarchy;
1733
1734 ELSE
1735 EXECUTE IMMEDIATE entriesStmt
1736 USING stepData(stepSeq).rule_id,
1737 stepSeq,
1738 stepData(stepSeq).step_name,
1739 stepData(stepSeq).formula_text,
1740 ruleStepId,
1741 'N',
1742 stepData(stepSeq).sql_statement_num,
1743 contextData.datasetCode,
1744 contextData.calPeriodId,
1745 contextData.currencyCode,
1746 nvl(contextData.parentEntity, -1),
1747 nvl(contextData.childEntity, -1),
1748 nvl(contextData.elimsEntity, -1),
1749 nvl(contextData.childEntity, -1);
1750
1751 END IF; --IF (cEntityType= 'C')
1752
1753 -- Show the result in the logfile
1754 logString(statementLogLevel,
1755 procedureName,
1756 'stmt',
1757 'Rows inserted = ' || to_char(SQL%ROWCOUNT));
1758
1759 IF offsetFlag = 'Y' THEN
1760
1761 entriesStmt := replace(entriesStmt, tgtDimStmt, oTgtDimStmt);
1762
1763 -- Show the statement in the logfile
1764 logString(statementLogLevel,
1765 procedureName,
1766 'stmt',
1767 'OFFSET entriesStmt = ' || entriesStmt);
1768
1769 --=======================================================
1770 logString(eventLogLevel, procedureName, 'section', '5.1');
1771 --=======================================================
1772
1773 IF (cEntityType = 'C') THEN
1774 EXECUTE IMMEDIATE entriesStmt
1775 USING stepData(stepSeq).rule_id,
1776 stepSeq,
1777 stepData(stepSeq).step_name, '-1 * ( ' || stepData(stepSeq).formula_text || ')',
1778 ruleStepId,
1779 'Y',
1780 stepData(stepSeq).sql_statement_num,
1781 contextData.datasetCode,
1782 contextData.calPeriodId,
1783 contextData.currencyCode,
1784 nvl(contextData.parentEntity, -1),
1785 nvl(contextData.childEntity, -1),
1786 nvl(contextData.elimsEntity, -1),
1787 nvl(contextData.childEntity, -1),
1788 nvl(contextData.childEntity, -1),
1789 contextData.hierarchy,
1790 contextData.hierarchy;
1791
1792 ELSE
1793 EXECUTE IMMEDIATE entriesStmt
1794 USING stepData(stepSeq).rule_id,
1795 stepSeq,
1796 stepData(stepSeq).step_name,
1797 '-1 * ( ' || stepData(stepSeq).formula_text || ')',
1798 ruleStepId,
1799 'Y',
1800 stepData(stepSeq).sql_statement_num,
1801 contextData.datasetCode,
1802 contextData.calPeriodId,
1803 contextData.currencyCode,
1804 nvl(contextData.parentEntity, -1),
1805 nvl(contextData.childEntity, -1),
1806 nvl(contextData.elimsEntity, -1),
1807 nvl(contextData.childEntity, -1);
1808
1809 END IF; --IF (cEntityType= 'C')
1810
1811 END IF; --IF offsetFlag = 'Y' THEN
1812
1813 -- Show the result in the logfile
1814 logString(statementLogLevel,
1815 procedureName,
1816 'stmt',
1817 'Rows inserted = ' || to_char(SQL%ROWCOUNT));
1818
1819 --jh 08.24.04: Bug 3848822: AD rule needs FEM_BALANCES
1820 --for ELIMTB only.
1821
1822 -- AD rule
1823 -- Bugfix 6242317: Do not execute any queries against FEM_BALANCES
1824 ELSIF contextData.eventType = 'A' THEN
1825 entriesStmt := replace(entriesStmt,
1826 'b.entity_id in ( :pid, :cid )
1827 OR ');
1828
1829 --Bugfix 6242317: Eliminating Code which queries against FEM_BALANCES
1830 logString(statementLogLevel,
1831 procedureName,
1832 'stmt',
1833 'Eliminating execution of SQL Call #1');
1834 /*
1835 -- Show the statement in the logfile
1836 logString( statementLogLevel, procedureName, 'stmt',
1837 'AD entriesStmt = ' || entriesStmt);
1838
1839 --=======================================================
1840 logString( eventLogLevel, procedureName, 'section', '5');
1841 --=======================================================
1842 IF (cEntityType= 'C') THEN
1843 EXECUTE IMMEDIATE entriesStmt
1844 USING stepData(stepSeq).rule_id,
1845 stepSeq,
1846 stepData(stepSeq).step_name,
1847 stepData(stepSeq).formula_text,
1848 ruleStepId,
1849 'N',
1850 contextData.datasetCode,
1851 contextData.calPeriodId,
1852 contextData.currencyCode,
1853 nvl(contextData.elimsEntity, -1),
1854 nvl(contextData.childEntity, -1),
1855 nvl(contextData.childEntity, -1),
1856 contextData.hierarchy,
1857 contextData.hierarchy;
1858 ELSE
1859 EXECUTE IMMEDIATE entriesStmt
1860 USING stepData(stepSeq).rule_id,
1861 stepSeq,
1862 stepData(stepSeq).step_name,
1863 stepData(stepSeq).formula_text,
1864 ruleStepId,
1865 'N',
1866 stepData(stepSeq).sql_statement_num,
1867 contextData.datasetCode,
1868 contextData.calPeriodId,
1869 contextData.currencyCode,
1870 nvl(contextData.elimsEntity, -1),
1871 nvl(contextData.childEntity, -1);
1872 END IF; --IF (cEntityType= 'C')
1873
1874 -- Show the result in the logfile
1875 logString( statementLogLevel, procedureName, 'stmt',
1876 'Rows inserted = ' || to_char(SQL%ROWCOUNT));
1877
1878
1879 IF offsetFlag = 'Y' THEN
1880 entriesStmt := replace(entriesStmt, tgtDimStmt, oTgtDimStmt);
1881
1882 -- Show the statement in the logfile
1883 logString( statementLogLevel, procedureName, 'stmt',
1884 'OFFSET AD entriesStmt = ' || entriesStmt);
1885
1886 --=======================================================
1887 logString( eventLogLevel, procedureName, 'section', '5.1');
1888 --=======================================================
1889
1890 IF (cEntityType= 'C') THEN
1891 EXECUTE IMMEDIATE entriesStmt
1892 USING stepData(stepSeq).rule_id,
1893 stepSeq,
1894 stepData(stepSeq).step_name,
1895 '-1 * ( ' ||stepData(stepSeq).formula_text || ')',
1896 ruleStepId,
1897 'Y',
1898 stepData(stepSeq).sql_statement_num,
1899 contextData.datasetCode,
1900 contextData.calPeriodId,
1901 contextData.currencyCode,
1902 nvl(contextData.elimsEntity, -1),
1903 nvl(contextData.childEntity, -1),
1904 nvl(contextData.childEntity, -1),
1905 contextData.hierarchy,
1906 contextData.hierarchy;
1907
1908 ELSE
1909 EXECUTE IMMEDIATE entriesStmt
1910 USING stepData(stepSeq).rule_id,
1911 stepSeq,
1912 stepData(stepSeq).step_name,
1913 '-1 * ( ' ||stepData(stepSeq).formula_text || ')',
1914 ruleStepId,
1915 'Y',
1916 stepData(stepSeq).sql_statement_num,
1917 contextData.datasetCode,
1918 contextData.calPeriodId,
1919 contextData.currencyCode,
1920 nvl(contextData.elimsEntity, -1),
1921 nvl(contextData.childEntity, -1);
1922
1923 END IF; --IF (cEntityType= 'C')
1924
1925 END IF; --IF offsetFlag = 'Y' THEN
1926
1927 -- Show the result in the logfile
1928 logString( statementLogLevel, procedureName, 'stmt',
1929 'Rows inserted = ' || to_char(SQL%ROWCOUNT));
1930 */
1931
1932 --jh 08.17.04: insert a row for balance from ad_trial_balances
1933 --jh 08.17.04: Remove the inapplicable columns from the insert.
1934 IF (cEntityType = 'C') THEN
1935
1936 entriesStmt := replace(entriesStmt,
1937 '
1938 AND ( ( b.entity_id = :eid
1939 AND b.COMPANY_COST_CENTER_ORG_ID IN(
1940 SELECT o.company_cost_center_org_id
1941 FROM GCS_ENTITY_CCTR_ORGS o
1942 WHERE ( o.entity_id = :cid
1943 OR
1944 o.entity_id IN(
1945 SELECT r.child_entity_id
1946 FROM GCS_CONS_RELATIONSHIPS r
1947 START WITH r.parent_entity_id = :cid
1948 AND r.hierarchy_id = :hid
1949 AND r.actual_ownership_flag = ''Y''
1950 AND ( sysdate BETWEEN r.start_date
1951 AND NVL(r.end_date, sysdate))
1952 CONNECT BY prior r.child_entity_id = r.parent_entity_id
1953 AND r.hierarchy_id = :hid
1954 AND r.actual_ownership_flag = ''Y''
1955 AND ( sysdate BETWEEN r.start_date
1956 AND NVL(r.end_date, sysdate)))
1957 ))))',
1958 '
1959 AND ( b.entity_id = :cid )');
1960
1961 ELSE
1962 entriesStmt := replace(entriesStmt,
1963 '
1964 AND ( ( b.entity_id = :eid
1965 AND b.COMPANY_COST_CENTER_ORG_ID IN(
1966 SELECT o.company_cost_center_org_id
1967 FROM GCS_ENTITY_CCTR_ORGS o
1968 WHERE ( o.entity_id = :cid
1969 ))))',
1970 '
1971
1972 AND ( b.entity_id = :cid )');
1973
1974 END IF; --IF (cEntityType= 'C')
1975
1976 entriesStmt := replace(entriesStmt, 'b.currency_code,', ':ccy,');
1977 entriesStmt := replace(entriesStmt,
1978 'b.ytd_credit_balance_e,
1979 ');
1980 entriesStmt := replace(entriesStmt,
1981 'b.ytd_debit_balance_e,
1982 ');
1983 entriesStmt := replace(entriesStmt, 'ytd_credit_balance_e,');
1984 entriesStmt := replace(entriesStmt,
1985 'ytd_debit_balance_e,
1986 ');
1987 entriesStmt := replace(entriesStmt,
1988 'fem_balances',
1989 'gcs_ad_trial_balances');
1990 entriesStmt := replace(entriesStmt,
1991 'dataset_code',
1992 'ad_transaction_id');
1993 entriesStmt := replace(entriesStmt, ':dci', ':tid');
1994 entriesStmt := replace(entriesStmt,
1995 'AND b.cal_period_id = :cpi
1996 ');
1997 entriesStmt := replace(entriesStmt,
1998 'AND b.currency_code IN (:ccy, ''STAT'')
1999 ');
2000 --Bugfix 6242317: Commenting out the code below as it will not work with offset functionality
2001 /*
2002 entriesStmt := entriesStmt || '
2003 AND NOT EXISTS(
2004 SELECT 1
2005 FROM GCS_ENTRIES_GT E
2006 WHERE E.ENTITY_ID = B.ENTITY_ID
2007 AND ( E.RULE_ID = :rid
2008 AND E.STEP_SEQ = :seq)' || adtColumnList ||'
2009 )';
2010 */
2011
2012 -- Show the statement in the logfile
2013 logString(statementLogLevel,
2014 procedureName,
2015 'stmt',
2016 'AD entriesStmt = ' || entriesStmt);
2017 --=======================================================
2018 logString(eventLogLevel, procedureName, 'section', '6');
2019 --=======================================================
2020
2021 EXECUTE IMMEDIATE entriesStmt
2022 USING stepData(stepSeq).rule_id,
2023 stepSeq,
2024 stepData(stepSeq).step_name,
2025 stepData(stepSeq).formula_text,
2026 ruleStepId,
2027 'N',
2028 stepData(stepSeq).sql_statement_num,
2029 contextData.currencyCode,
2030 contextData.eventKey,
2031 nvl(contextData.childEntity, -1);
2032
2033 -- Show the result in the logfile
2034 logString(statementLogLevel,
2035 procedureName,
2036 'stmt',
2037 'Rows inserted = ' || to_char(SQL%ROWCOUNT));
2038
2039 IF offsetFlag = 'Y' THEN
2040
2041 entriesStmt := replace(entriesStmt, tgtDimStmt, oTgtDimStmt);
2042
2043 -- Show the statement in the logfile
2044 logString(statementLogLevel,
2045 procedureName,
2046 'stmt',
2047 'OFFSET AD entriesStmt 2 = ' || entriesStmt);
2048 --=======================================================
2049 logString(eventLogLevel, procedureName, 'section', '6.1');
2050 --=======================================================
2051
2052 EXECUTE IMMEDIATE entriesStmt
2053 USING stepData(stepSeq).rule_id,
2054 stepSeq,
2055 stepData(stepSeq).step_name,
2056 '-1 * ( ' || stepData(stepSeq).formula_text || ')',
2057 ruleStepId,
2058 'Y',
2059 stepData(stepSeq).sql_statement_num,
2060 contextData.currencyCode,
2061 contextData.eventKey,
2062 nvl(contextData.childEntity, -1);
2063 --Bug 6242317: Bind Parameters below are not required
2064 /*
2065 stepData(stepSeq).rule_id,
2066 stepSeq;
2067 */
2068 -- Show the result in the logfile
2069 logString(statementLogLevel,
2070 procedureName,
2071 'stmt',
2072 'Rows inserted = ' || to_char(SQL%ROWCOUNT));
2073
2074 --Bugfix 6242317: The code below should always run not only if the offset flag is N
2075 END IF;
2076
2077 END IF; --IF contextData.eventType = 'C' THEN
2078
2079 -- Done
2080 logString(procedureLogLevel,
2081 procedureName,
2082 'end',
2083 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
2084
2085 end initEntriesGT_stdDimSet;
2086
2087 --Bugfix 4928211: Added initEntriesGT: Used for performance purposes for consolidation rules
2088 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2089 -- Initialize data in the GCS_ENTRIES_GT table by creating source and
2090 -- target dimensions, and setting related "friendly" data like names, formula.
2091 --
2092 -- FOR TARGET-ONLY dimension sets: see proc initEntriesGT_tgtDimSet elsewhere.
2093 --
2094 -- FOR STANDARD dimension sets:
2095 --
2096 -- The SQL stmt used to init the gcs_entries_gt table looks like...
2097 --
2098 -- INSERT INTO GCS_ENTRIES_GT (
2099 -- rule_id, step_seq, formula_text,
2100 -- ad_input_amount, pe_input_amount, ce_input_amount, ee_input_amount,
2101 -- output_amount,
2102 -- <src_dimensions>,
2103 -- <tgt_dimensions>)
2104 -- SELECT :rid, :seq, :ftx,
2105 -- 0, 0,
2106 -- <b.source_dimension_columns>,
2107 -- <d.target_dimension_expressions>
2108 -- FROM fem_balances b,
2109 -- gcs_rule_scope_dims d
2110 -- WHERE b.source_dimension_columns =
2111 -- (select t.source_member_id
2112 -- from gcs_rule_scope_dtls t
2113 -- where t.rule_step_id = :rsi) ;
2114 --
2115 --
2116 -- For the cctr_org and interco dimensions, the target value is always
2117 -- copied from the source value. For the other dimensions, the target
2118 -- expressions are based on whether...
2119 -- a) ...the target template is a value
2120 -- then use the target's value as a constant
2121 --
2122 -- b) ...the target template is NULL
2123 -- then use the source's same-dimension value
2124 --
2125 -- An expression in the form of...
2126 -- decode( <xdim>,
2127 -- NULL, l.src_<xdim>,
2128 -- <xdim>)
2129 -- ...is constructed for each selected dimension in the dim set.
2130 --
2131 -- This procedure makes use of expression strings created elsewhere...
2132 --
2133 -- selColumnList: has expressions in the form ', b.<xdim>' for
2134 -- the current dim set id (see resolveDimSet)
2135 -- srcColumnList: created here as replace(selColumnList,', b.',', src_')
2136 -- so if the selColumnList = b.moe, b.larry, b.curly then
2137 -- the srcColumnList would come out as src_moe, src_larry,
2138 -- src_curly.
2139 -- tgtColumnList: created here as replace(selColumnList,', b.',', tgt_')
2140 -- so if the selColumnList = b.moe, b.larry, b.curly then
2141 -- the tgtColumnList would come out as tgt_moe, tgt_larry,
2142 -- tgt_curly.
2143 -- insColumnList: has expressions in the form ', <xdim>' for
2144 -- the current dim set id (see resolveDimSet)
2145 -- adtColumnList: has expressions in the form 'AND E.<xdim> = B.<xdim>'
2146 -- for the join condition to gcs_ad_trial_balances.
2147 --
2148 -- NOTE: Abbreviations used for bind vars here...
2149 -- ccy = currency code id contextData.currencyCode
2150 -- dci = dataset code contextData.datasetCode
2151 -- dsi = dim set id dimSetId (private global variable)
2152 -- dsn = dim set name dimSets(dsi).dimension_set_name
2153 -- dst = dim set type dimSets(dsi).dimension_set_type_code
2154 -- eid = entity id contextData.elimsEntity
2155 -- ftx = formula text dimSet(dsi).formula_text
2156 -- seq = step sequence stepSeq (procedure argument)
2157 -- rid = rule id stepData(seq).rule_id
2158 -- rsi = rule step id stepData(seq).rule_step_id
2159 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2160 procedure initEntriesGT(stepSeq IN NUMBER,
2161 categoryInfo IN getCategory%ROWTYPE,
2162 organizationId IN NUMBER,
2163 intercompanyId IN NUMBER) is
2164
2165 procedureName varchar2(30);
2166 entriesStmt varchar2(20000);
2167 oEntriesStmt varchar2(20000);
2168 whereClause varchar2(5000);
2169 tgtDimStmt varchar2(5000);
2170 oTgtDimStmt varchar2(5000);
2171 fromList varchar2(2500);
2172 i varchar2(30);
2173 j number;
2174 orgId number := -1;
2175 intercoId number := -1;
2176 tgtOrg varchar2(100);
2177 cEntityType varchar2(2); --whether child entity is operating or consolidation
2178 offsetFlag varchar2(2); --whether to create an offset line or not.
2179
2180 cursor getChildEntityType is
2181 SELECT dim_attribute_varchar_member
2182 FROM FEM_ENTITIES_ATTR
2183 WHERE entity_id = nvl(contextData.childEntity, -1)
2184 AND attribute_id =
2185 GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE')
2186 .attribute_id
2187 AND version_id =
2188 GCS_UTILITY_PKG.g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE')
2189 .version_id
2190 AND value_set_id =
2191 GCS_UTILITY_PKG.g_gcs_dimension_info('ENTITY_ID')
2192 .associated_value_set_id;
2193
2194 --Bugfix 4928211: Added objectDefnId for performance purposes
2195 objectDefnId number;
2196 groupClause varchar2(2000);
2197 TYPE tbindVarInfo IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
2198 bindVarInfo tbindVarInfo;
2199 elimEntityToken boolean := false;
2200 childEntityToken boolean := false;
2201 miToken boolean := false;
2202 ownToken boolean := false;
2203 bindVarIndex varchar2(30);
2204 entriesStmtIdx integer := dbms_sql.open_cursor;
2205 outputStmtIdx integer := dbms_sql.open_cursor;
2206 dbmsSqlVal number;
2207 rowcount number;
2208 outputStmt varchar2(1000);
2209 setOutput varchar2(100);
2210
2211 begin
2212 procedureName := 'INIT_ENTRIESGT';
2213 tgtOrg := '';
2214
2215 logString(procedureLogLevel,
2216 procedureName,
2217 'begin',
2218 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
2219
2220 --Global states may persist since last time we did this, so reset them
2221 selColumnList := null;
2222 adtColumnList := null;
2223 j := 0;
2224 offsetFlag := 'N';
2225
2226 --Assign column lists based off the active dimensions using the dynamic utility package
2227 selColumnList := gcs_rp_utility_pkg.g_rp_selColumnList;
2228 srcColumnList := gcs_rp_utility_pkg.g_rp_srcColumnList;
2229 tgtColumnList := gcs_rp_utility_pkg.g_rp_tgtColumnList;
2230 offColumnList := gcs_rp_utility_pkg.g_rp_offColumnList;
2231
2232 --Log Strings for tracking purposes
2233 logString(statementLogLevel,
2234 procedureName,
2235 'stmt',
2236 'selColumnList = ' || selColumnList);
2237 logString(statementLogLevel,
2238 procedureName,
2239 'stmt',
2240 'srcColumnList = ' || srcColumnList);
2241 logString(statementLogLevel,
2242 procedureName,
2243 'stmt',
2244 'tgtColumnList = ' || tgtColumnList);
2245 logString(statementLogLevel,
2246 procedureName,
2247 'stmt',
2248 'offColumnList = ' || offColumnList);
2249
2250 --STK: Note to Self, this can probably be enhanced rather than calling per rule step
2251 --find out if child entity is a consolidation entity.
2252 Open getChildEntityType;
2253 Fetch getChildEntityType
2254 INTO cEntityType;
2255
2256 If getChildEntityType%NOTFOUND then
2257 cEntityType := 'N'; --set entity_type to 'N' for none
2258 End if;
2259
2260 Close getChildEntityType;
2261
2262 logString(statementLogLevel,
2263 procedureName,
2264 'parameter',
2265 'child entity type = ' || cEntityType);
2266
2267 --=======================================================
2268 logString(eventLogLevel, procedureName, 'section', '2');
2269 --=======================================================
2270
2271 --Start the Statement
2272 entriesStmt := gcs_rp_utility_pkg.g_core_insert_stmt || srcColumnList ||
2273 tgtColumnList || offColumnList ||
2274 gcs_rp_utility_pkg.g_core_sel_stmt || selColumnList;
2275
2276 whereClause := gcs_rp_utility_pkg.g_core_whr_stmt;
2277 fromList := gcs_rp_utility_pkg.g_core_frm_stmt;
2278 groupClause := gcs_rp_utility_pkg.g_core_grp_stmt;
2279
2280 --Check for token usages
2281 if (INSTR(stepData(stepSeq).formula_text, 'CHILDTB') <> 0) then
2282 childEntityToken := true;
2283 logString(statementLogLevel,
2284 procedureName,
2285 'parameter',
2286 'CHILDTB Token = true');
2287 end if;
2288
2289 if (INSTR(stepData(stepSeq).formula_text, 'ELIMTB') <> 0) then
2290 elimEntityToken := true;
2291 bindVarInfo('eid') := contextData.elimsEntity;
2292 logString(statementLogLevel,
2293 procedureName,
2294 'parameter',
2295 'ELIMTB Token = true');
2296 end if;
2297
2298 --Code area to check how to determine appropriate organizations that must be used in the calculation
2299
2300 if (elimEntityToken) then
2301 if (cEntityType = 'O') then
2302 fromList := fromList || ' ,
2303 gcs_entity_cctr_orgs geo';
2304
2305 whereClause := whereClause || '
2306 AND b.company_cost_center_org_id = geo.company_cost_center_org_id
2307 AND geo.entity_id = :cid ';
2308
2309 if (childEntityToken) then
2310 whereClause := whereClause || '
2311 AND b.entity_id IN (:cid, :eid)';
2312 else
2313 whereClause := whereClause || '
2314 AND b.entity_id IN (:eid)';
2315 end if;
2316 elsif (cEntityType = 'C') then
2317 fromList := fromList || ' ,
2318 gcs_entity_cctr_orgs geo,
2319 gcs_flattened_relns gfr';
2320
2321 whereClause := whereClause || '
2322 AND b.company_cost_center_org_id = geo.company_cost_center_org_id
2323 AND geo.entity_id = gfr.child_entity_id
2324 AND gfr.run_name = :runname
2325 AND gfr.parent_entity_id = :cid ';
2326
2327 bindVarInfo('runname') := contextData.runName;
2328
2329 if (childEntityToken) then
2330 whereClause := whereClause || '
2331 AND b.entity_id IN (:cid, :eid)';
2332 else
2333 whereClause := whereClause || '
2334 AND b.entity_id IN (:eid)';
2335 end if;
2336 end if;
2337 else
2338 whereClause := whereClause || '
2339 AND b.entity_id = :cid ';
2340 end if;
2341
2342 --=======================================================
2343 logString(eventLogLevel, procedureName, 'section', '3');
2344 --=======================================================
2345
2346 -- Add the selected dim target expressions
2347 i := selectDims.FIRST;
2348 tgtDimStmt := '';
2349 oTgtDimStmt := '';
2350 offsetFlag := 'N';
2351
2352 While i IS NOT NULL Loop
2353
2354 --Handle the dim_set_dims differently: by looping through the dimInfo
2355 --array and using the index there in the selectDims array, when we hit
2356 --a dim that is not selected for this Dim Set a no_data_found is thrown.
2357 --The body of this nested block handles selectDims columns while the
2358 --no_data_found handler handles the non-selectDims columns.
2359
2360 BEGIN
2361 j := j + 1;
2362 --jh 10.19.04: add all-source-member support
2363 if selectDims(i).target_member_id IS NULL then
2364 tgtDimStmt := tgtDimStmt || ',
2365 b.' || selectDims(i).column_name;
2366 else
2367 bindVarInfo('target' || selectDims(i).alias) := selectDims(i)
2368 .target_member_id;
2369 tgtDimStmt := tgtDimStmt || ',
2370 :target' || selectDims(i).alias;
2371 end if;
2372
2373 --Bugfix 4928211: Added offset support directly in single statement
2374 oTgtDimStmt := oTgtDimStmt || ',
2375 :offset' || selecTdims(i).alias;
2376
2377 if selectDims(i).offset_member_id IS NOT NULL THEN
2378 offsetFlag := 'Y';
2379 bindVarInfo('offset' || selectDims(i).alias) := selectDims(i)
2380 .offset_member_id;
2381 else
2382 bindVarInfo('offset' || selectDims(i).alias) := NULL;
2383 end if; --IF selectDims(i).offset_member_id IS NOT NULL THEN
2384
2385 if selectDims(i).all_source_members_flag <> 'Y' then
2386
2387 fromList := fromList || ' ,
2388 gcs_rule_scope_dtls ' || selectDims(i).alias;
2389 whereClause := whereClause || '
2390 AND ' || selectDims(i)
2391 .alias || '.rule_step_id = :rsi';
2392 bindVarInfo('sourcecolumn' || selectDims(i).alias) := selectDims(i)
2393 .column_name;
2394 whereClause := whereClause || '
2395 AND ' || selectDims(i)
2396 .alias || '.column_name = :sourcecolumn' ||
2397 selectDims(i).alias;
2398
2399 --check if selecting based off of a flat list
2400 if selectDims(i).hierarchy_obj_id IS NULL then
2401 whereClause := whereClause || '
2402 AND b.' || selectDims(i)
2403 .column_name || ' = ' || selectDims(i)
2404 .alias || '.source_member_id';
2405 --for scenario where hierarchy is selected
2406 else
2407 SELECT object_definition_id
2408 INTO objectDefnId
2409 FROM fem_object_definition_b
2410 WHERE object_id = selectDims(i)
2411 .hierarchy_obj_id
2412 AND contextData.calPeriodEndDate between
2413 effective_start_date and effective_end_date;
2414
2415 fromList := fromList || ' ,
2416 ' || selectDims(i)
2417 .hierarchy_table_name || ' ' || selectDims(i)
2418 .hierarchy_table_name;
2419
2420 bindVarInfo('sourcehierarchy' || selectDims(i).alias) := objectDefnId;
2421 whereClause := whereClause || '
2422 AND ' || selectDims(i).hierarchy_table_name ||
2423 '.hierarchy_obj_def_id = :sourcehierarchy' ||
2424 selectDims(i).alias;
2425 whereClause := whereClause || '
2426 AND b.' || selectDims(i)
2427 .column_name || ' = ' || selectDims(i)
2428 .hierarchy_table_name || '.child_id';
2429 whereClause := whereClause || '
2430 AND ' || selectDims(i)
2431 .alias || '.source_member_id = ' || selectDims(i)
2432 .hierarchy_table_name || '.parent_id';
2433
2434 end if; --complete check for hierarchy scenarios
2435
2436 end if; -- check all_source_members_flag
2437
2438 EXCEPTION
2439 WHEN OTHERS THEN
2440 logString(statementLogLevel,
2441 procedureName,
2442 'error during build of sql statement',
2443 SQLERRM);
2444 END;
2445 i := selectDims.NEXT(i);
2446
2447 End Loop; --While i IS NOT NULL
2448 --=======================================================
2449 logString(eventLogLevel, procedureName, 'section', '4');
2450 --=======================================================
2451
2452 -- Add the FROM and WHERE clauses
2453 entriesStmt := entriesStmt || tgtDimStmt || oTgtDimStmt || fromList ||
2454 whereClause || groupClause;
2455
2456 ruleStepId := stepData(stepSeq).rule_step_id;
2457
2458 -- Execute the stmt
2459 logString(statementLogLevel,
2460 procedureName,
2461 'bind',
2462 'rid = ' || to_char(stepData(stepSeq).rule_id));
2463 logString(statementLogLevel,
2464 procedureName,
2465 'bind',
2466 'seq = ' || to_char(stepSeq));
2467 logString(statementLogLevel,
2468 procedureName,
2469 'bind',
2470 'sna = ' || stepData(stepSeq).step_name);
2471 logString(statementLogLevel,
2472 procedureName,
2473 'bind',
2474 'ftx = ' || stepData(stepSeq).formula_text);
2475 logString(statementLogLevel,
2476 procedureName,
2477 'bind',
2478 'rsi = ' || to_char(stepData(stepSeq).rule_step_id));
2479 logString(statementLogLevel,
2480 procedureName,
2481 'bind',
2482 'stn = ' || to_char(stepData(stepSeq).sql_statement_num));
2483 logString(statementLogLevel,
2484 procedureName,
2485 'bind',
2486 'dci = ' || to_char(contextData.datasetCode));
2487 logString(statementLogLevel,
2488 procedureName,
2489 'bind',
2490 'cpi = ' || to_char(contextData.calPeriodId));
2491 logString(statementLogLevel,
2492 procedureName,
2493 'bind',
2494 'ccy = ' || contextData.currencyCode);
2495 logString(statementLogLevel,
2496 procedureName,
2497 'bind',
2498 'pid = ' || to_char(contextData.parentEntity));
2499 logString(statementLogLevel,
2500 procedureName,
2501 'bind',
2502 'cid = ' || to_char(contextData.childEntity));
2503 logString(statementLogLevel,
2504 procedureName,
2505 'bind',
2506 'eid = ' || to_char(contextData.elimsEntity));
2507 logString(statementLogLevel,
2508 procedureName,
2509 'bind',
2510 'hid = ' || to_char(contextData.hierarchy));
2511 logString(statementLogLevel,
2512 procedureName,
2513 'bind',
2514 'osf = ' || offsetFlag);
2515
2516 --Consolidation rule
2517 IF contextData.eventType = 'C' THEN
2518
2519 logString(statementLogLevel,
2520 procedureName,
2521 'dumpBinding',
2522 'dumping bind variables');
2523 bindVarIndex := bindVarInfo.FIRST;
2524
2525 while (bindVarIndex is not null) loop
2526 logString(statementLogLevel,
2527 procedureName,
2528 'bindVarInfo',
2529 bindVarIndex);
2530 logString(statementLogLevel,
2531 procedureName,
2532 'bindVarinfo',
2533 bindVarinfo(bindVarIndex));
2534 bindVarIndex := bindVarInfo.next(bindVarIndex);
2535 end loop;
2536 -- Show the statement in the logfile
2537 logString(statementLogLevel,
2538 procedureName,
2539 'stmt',
2540 'entriesStmt = ' || entriesStmt);
2541
2542 --=======================================================
2543 logString(eventLogLevel, procedureName, 'section', '5');
2544 --=======================================================
2545 dbms_sql.parse(entriesStmtIdx, entriesStmt, DBMS_SQL.NATIVE);
2546 dbms_sql.bind_variable(entriesStmtIdx,
2547 'rid',
2548 stepData(stepSeq).rule_id);
2549 dbms_sql.bind_variable(entriesStmtIdx, 'seq', stepSeq);
2550 dbms_sql.bind_variable(entriesStmtIdx,
2551 'sna',
2552 stepData(stepSeq).step_name);
2553 dbms_sql.bind_variable(entriesStmtIdx,
2554 'ftx',
2555 stepData(stepSeq).formula_text);
2556 dbms_sql.bind_variable(entriesStmtIdx, 'rsi', ruleStepId);
2557 dbms_sql.bind_variable(entriesStmtIdx, 'osf', offsetFlag);
2558 dbms_sql.bind_variable(entriesStmtIdx,
2559 'stn',
2560 stepData(stepSeq).sql_statement_num);
2561 dbms_sql.bind_variable(entriesStmtIdx,
2562 'dci',
2563 contextData.datasetCode);
2564 dbms_sql.bind_variable(entriesStmtIdx,
2565 'cpi',
2566 contextData.calPeriodId);
2567 dbms_sql.bind_variable(entriesStmtIdx,
2568 'ccy',
2569 contextData.currencyCode);
2570 dbms_sql.bind_variable(entriesStmtIdx,
2571 'tgt_cctr_org_id',
2572 organizationId);
2573 dbms_sql.bind_variable(entriesStmtIdx,
2574 'tgt_intercompany_id',
2575 intercompanyId);
2576 dbms_sql.bind_variable(entriesStmtIdx,
2577 'eid',
2578 contextData.elimsEntity);
2579 dbms_sql.bind_variable(entriesStmtIdx,
2580 'cid',
2581 contextData.childEntity);
2582 dbms_sql.bind_variable(entriesStmtIdx,
2583 'pid',
2584 contextData.parentEntity);
2585 --Bugfix 5456211: Added ledger to the bind variables for performance purposes
2586 dbms_sql.bind_variable(entriesStmtIdx,
2587 'ledger',
2588 contextData.ledgerId);
2589
2590 logString(statementLogLevel,
2591 procedureName,
2592 'dumpBinding',
2593 'dumping bind variables');
2594 bindVarIndex := bindVarInfo.FIRST;
2595 while (bindVarIndex is not null) loop
2596 logString(statementLogLevel,
2597 procedureName,
2598 'bindVarInfo',
2599 bindVarIndex);
2600 logString(statementLogLevel,
2601 procedureName,
2602 'bindVarinfo',
2603 bindVarinfo(bindVarIndex));
2604 dbms_sql.bind_variable(entriesStmtIdx,
2605 bindVarIndex,
2606 bindVarInfo(bindVarIndex));
2607 bindVarIndex := bindVarInfo.next(bindVarIndex);
2608 end loop;
2609
2610 logString(statementLogLevel,
2611 procedureName,
2612 'binding',
2613 'completed binding variables');
2614
2615 dbmsSqlVal := dbms_sql.execute(entriesStmtIdx);
2616
2617 dbms_sql.close_cursor(entriesStmtIdx);
2618
2619 setOutput := stepData(stepSeq).formula_text;
2620 if (INSTR(setOutput, 'CHILDTB') <> 0) then
2621 setOutput := replace(setOutput, 'CHILDTB', 'ce_input_amount');
2622 end if;
2623 if (INSTR(setOutput, 'ELIMTB') <> 0) then
2624 setOutput := replace(setOutput, 'ELIMTB', 'ee_input_amount');
2625 end if;
2626 if (INSTR(setOutput, 'PARTB') <> 0) then
2627 setOutput := replace(setOutput, 'PARTB', 'pe_input_amount');
2628 end if;
2629 if (INSTR(setOutput, '%MI') <> 0) then
2630 setOutput := replace(setOutput, '%MI', ':min');
2631 miToken := true;
2632 end if;
2633 if (INSTR(setOutput, '%OWN') <> 0) then
2634 setOutput := replace(setOutput, '%OWN', ':own');
2635 ownToken := true;
2636 end if;
2637
2638 --Bugfix 5075451: Added paranthesis around setOutput so calculation is done first then currency rounding
2639
2640 outputStmt := 'UPDATE gcs_entries_gt
2641 SET output_amount = round((' || setOutput ||
2642 ') / :currPrecision) * :currPrecision
2643 WHERE rule_id = :rule_id
2644 AND step_seq = :seq';
2645
2646 dbms_sql.parse(outputStmtIdx, outputStmt, DBMS_SQL.NATIVE);
2647 dbms_sql.bind_variable(outputStmtIdx,
2648 'rule_id',
2649 stepData(stepSeq).rule_id);
2650 dbms_sql.bind_variable(outputStmtIdx, 'seq', stepSeq);
2651 dbms_sql.bind_variable(outputStmtIdx,
2652 'currPrecision',
2653 contextData.currPrecision);
2654 if (miToken) then
2655 dbms_sql.bind_variable(outputStmtIdx,
2656 'min',
2657 1 - ruleData.toPercent);
2658 end if;
2659 if (ownToken) then
2660 dbms_sql.bind_variable(outputStmtIdx, 'own', ruleData.toPercent);
2661 end if;
2662 dbmsSqlVal := dbms_sql.execute(outputStmtIdx);
2663 dbms_sql.close_cursor(outputStmtIdx);
2664
2665 /*
2666 IF (cEntityType= 'C') THEN
2667 EXECUTE IMMEDIATE entriesStmt
2668 USING stepData(stepSeq).rule_id,
2669 stepSeq,
2670 stepData(stepSeq).step_name,
2671 stepData(stepSeq).formula_text,
2672 ruleStepId,
2673 'N',
2674 stepData(stepSeq).sql_statement_num,
2675 contextData.datasetCode,
2676 contextData.calPeriodId,
2677 contextData.currencyCode,
2678 nvl(contextData.parentEntity, -1),
2679 nvl(contextData.childEntity, -1),
2680 nvl(contextData.elimsEntity, -1),
2681 nvl(contextData.childEntity, -1),
2682 nvl(contextData.childEntity, -1),
2683 contextData.hierarchy,
2684 contextData.hierarchy;
2685
2686 ELSE
2687 EXECUTE IMMEDIATE entriesStmt
2688 USING stepData(stepSeq).rule_id,
2689 stepSeq,
2690 stepData(stepSeq).step_name,
2691 stepData(stepSeq).formula_text,
2692 ruleStepId,
2693 'N',
2694 stepData(stepSeq).sql_statement_num,
2695 contextData.datasetCode,
2696 contextData.calPeriodId,
2697 contextData.currencyCode,
2698 nvl(contextData.parentEntity, -1),
2699 nvl(contextData.childEntity, -1),
2700 nvl(contextData.elimsEntity, -1),
2701 nvl(contextData.childEntity, -1);
2702
2703 END IF; --IF (cEntityType= 'C')
2704 */
2705 -- Show the result in the logfile
2706 logString(statementLogLevel,
2707 procedureName,
2708 'stmt',
2709 'Rows inserted = ' || to_char(SQL%ROWCOUNT));
2710
2711 END IF; --IF contextData.eventType = 'C' THEN
2712
2713 -- Done
2714 logString(procedureLogLevel,
2715 procedureName,
2716 'end',
2717 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
2718
2719 end initEntriesGT;
2720
2721 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2722 -- Initialize data in the GCS_ENTRIES_GT table by creating source and
2723 -- target dimensions and setting related outfile data like rule_id, names.
2724 --
2725 -- FOR STANDARD TARGET SETS: see proc initEntriesGT_stdDimSet elsewhere.
2726 --
2727 -- FOR TARGET-ONLY dimension sets:
2728 --
2729 -- We only have to insert into the gcs_entries_gt table the single
2730 -- target DVS template, which must be a complete set of detail values...
2731 --
2732 -- The SQL stmt used to init the gcs_entries_gt table looks like...
2733 --
2734 -- INSERT INTO GCS_ENTRIES_GT (
2735 -- rule_id, step_seq, formula_text,
2736 -- input_amount, output_amount, <tgt_dimensions>)
2737 -- SELECT :rid, :seq, :ftx,
2738 -- 0, 0, <l.target_dimension_expressions>
2739 -- FROM gcs_rule_scope_dims d
2740 -- WHERE d.rule_step_id = :rsi;
2741 --
2742 -- The target expressions are actually the l.tgt_ column names.
2743 --
2744 -- NOTE: Abbreviations used for bind vars here...
2745 -- ccy = currency code id contextData.currencyCode
2746 -- eid = entity id contextData.elimsEntity
2747 -- dci = dataset code contextData.datasetCode
2748 -- ftx = formula text dimSet(dsi).formula_text
2749 -- seq = step sequence stepSeq (procedure argument)
2750 -- rid = rule id stepData(seq).rule_id
2751 -- rsi = rule step stepData(seq).rule_step_id
2752 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2753 procedure initEntriesGT_tgtDimSet(stepSeq IN NUMBER,
2754 categoryInfo IN getCategory%ROWTYPE) is
2755
2756 procedureName varchar2(30);
2757
2758 entriesStmt varchar2(10000);
2759 i varchar2(30);
2760 orgId number := -1;
2761 intercoId number := -1;
2762
2763 -- changes made by yingliu:
2764 /*
2765 cursor getSpecificIntercoId is
2766 SELECT SPECIFIC_INTERCOMPANY_ID
2767 FROM GCS_HIERARCHIES_B
2768 WHERE hierarchy_id = contextData.hierarchy;
2769 */
2770 cursor getSpecificIntercoId is
2771 SELECT SPECIFIC_INTERCOMPANY_ID
2772 FROM GCS_CATEGORIES_B
2773 WHERE CATEGORY_CODE = 'INTRACOMPANY';
2774 -- end of change by yingliu
2775
2776 --We need a cctr and interco value to use here.
2777 --See bug 3710985 for details on how cctr and
2778 --interco values are determined (jh 06.29.04).
2779
2780 begin
2781 procedureName := 'INIT_ENTRIESGT_TGT_DIMSET';
2782
2783 logString(procedureLogLevel,
2784 procedureName,
2785 'begin',
2786 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
2787
2788 --Identify a default cctr and interco dim member to use
2789
2790 --JH 7.28.04: use categoryInfo.entityId to get base_org (3798215)
2791 orgId := GCS_UTILITY_PKG.get_org_id(categoryInfo.entityId,
2792 contextData.hierarchy);
2793
2794 if orgId = -1 then
2795 logString(exceptionLogLevel,
2796 procedureName,
2797 'bind',
2798 'categoryInfo.entityId => ' ||
2799 to_char(categoryInfo.entityId));
2800 logString(exceptionLogLevel,
2801 procedureName,
2802 'bind',
2803 'contextData.hierarchy => ' ||
2804 to_char(contextData.hierarchy));
2805 RAISE no_default_cctr_found;
2806 end if;
2807
2808 -- changes made by yingliu
2809 /*
2810 if categoryInfo.interco_output_code ='SPECIFIC_VALUE' then
2811 Open getSpecificIntercoId;
2812 Fetch getSpecificIntercoId into intercoId;
2813 Close getSpecificIntercoId;
2814 else intercoId := orgId;
2815 end if;
2816 */
2817 Open getSpecificIntercoId;
2818 Fetch getSpecificIntercoId
2819 into intercoId;
2820 Close getSpecificIntercoId;
2821
2822 IF intercoId IS NULL THEN
2823 intercoId := orgId;
2824 END IF;
2825 -- end of changes by yingliu
2826
2827 --=======================================================
2828 logString(eventLogLevel, procedureName, 'section', '2');
2829 --=======================================================
2830
2831 -- Set up a target expression list
2832 i := dimInfo.FIRST;
2833 tgtColumnList := null;
2834 While i IS NOT NULL Loop
2835
2836 if dimInfo(i).required_for_gcs = 'Y' then
2837
2838 tgtColumnList := tgtColumnList || ',
2839 tgt_' || dimInfo(i).column_name;
2840
2841 end if;
2842
2843 i := dimInfo.NEXT(i);
2844
2845 End Loop;
2846
2847 --=======================================================
2848 logString(eventLogLevel, procedureName, 'section', '3');
2849 --=======================================================
2850
2851 -- Start the statement
2852 entriesStmt := 'INSERT INTO GCS_ENTRIES_GT (
2853 rule_id, step_seq, step_name, formula_text, rule_step_id,
2854 currency_code,ad_input_amount, pe_input_amount,
2855 ce_input_amount, ee_input_amount, output_amount' ||
2856 tgtColumnList || ')
2857 SELECT DISTINCT :rid, :seq, :sna, :ftx, r..rule_step_id, :ccy, 0, 0, 0, 0,0 ';
2858
2859 --=======================================================
2860 logString(eventLogLevel, procedureName, 'section', '4');
2861 --=======================================================
2862
2863 -- Add the target expressions
2864 i := dimInfo.FIRST;
2865 While i IS NOT NULL Loop
2866
2867 if dimInfo(i).required_for_gcs = 'Y' then
2868
2869 --See bug 3710985 for details on how cctr and
2870 --interco values are determined (jh 06.29.04)
2871
2872 if dimInfo(i).column_name = cctr_column then
2873 entriesStmt := entriesStmt || ',
2874 ' || to_char(orgId);
2875
2876 elsif dimInfo(i).column_name = interco_column then
2877 entriesStmt := entriesStmt || ',
2878 ' || to_char(intercoId);
2879
2880 else
2881
2882 if tgtDims(dimInfo(i).column_name).target_member_id IS NULL THEN
2883 entriesStmt := entriesStmt || ',
2884 NULL';
2885 else
2886 entriesStmt := entriesStmt || ',
2887 ' || tgtDims(dimInfo(i).column_name)
2888 .target_member_id;
2889
2890 end if; --tgtDims(dimInfo(i).column_name).target_member_id IS NULL THEN
2891
2892 end if; --if dimInfo(i).column_name = cctr_column
2893
2894 end if; --if dimInfo(i).required_for_gcs = 'Y'
2895
2896 i := dimInfo.NEXT(i);
2897
2898 End Loop;
2899
2900 --=======================================================
2901 logString(eventLogLevel, procedureName, 'section', '5');
2902 --=======================================================
2903
2904 -- Join target-only DMS info to GCS_RULE_SCOPE_DIMS
2905 -- Add the FROM and WHERE clauses
2906 entriesStmt := entriesStmt || '
2907 FROM gcs_dimension_set_dims d
2908 WHERE g.rule_step_id = :rsi';
2909
2910 --jh 4.30.04: update dimSetId
2911 ruleStepId := stepData(stepSeq).rule_step_id;
2912
2913 -- Show the statement in the logfile
2914 logString(statementLogLevel,
2915 procedureName,
2916 'stmt',
2917 'Target-only entriesStmt = ' || entriesStmt);
2918 logString(statementLogLevel,
2919 procedureName,
2920 'bind',
2921 'rid => ' || to_char(stepData(stepSeq).rule_id));
2922 logString(statementLogLevel,
2923 procedureName,
2924 'bind',
2925 'seq => ' || to_char(stepSeq));
2926 logString(statementLogLevel,
2927 procedureName,
2928 'bind',
2929 'sna => ' || stepData(stepSeq).step_name);
2930 logString(statementLogLevel,
2931 procedureName,
2932 'bind',
2933 'ftx => ' || to_char(stepData(stepSeq).formula_text));
2934 logString(statementLogLevel,
2935 procedureName,
2936 'bind',
2937 'ccy => ' || contextData.currencyCode);
2938 logString(statementLogLevel,
2939 procedureName,
2940 'bind',
2941 'rsi => ' || ruleStepId);
2942
2943 --=======================================================
2944 logString(eventLogLevel, procedureName, 'section', '6');
2945 --=======================================================
2946
2947 -- Execute the stmt
2948 EXECUTE IMMEDIATE entriesStmt
2949 USING stepData(stepSeq).rule_id,
2950 stepSeq,
2951 stepData(stepSeq).step_name,
2952 stepData(stepSeq).formula_text,
2953 contextData.currencyCode,
2954 ruleStepId;
2955
2956 -- Show the result in the logfile
2957 logString(statementLogLevel,
2958 procedureName,
2959 'stmt',
2960 'Rows inserted = ' || to_char(SQL%ROWCOUNT));
2961
2962 -- Doney
2963 logString(procedureLogLevel,
2964 procedureName,
2965 'end',
2966 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
2967
2968 end initEntriesGT_tgtDimSet;
2969
2970 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2971 -- Use balances from FEM_Balances, GCS_AD_Trail_balances or both as inputs
2972 -- to formulas, updating GCS_ENTRIES_GT with the results.
2973 --
2974 -- PARAMETERS:
2975 -- stepSeq IN Number the step_seq value to process
2976 --
2977 -- NOTE: the form that the dynamic sql stmt takes here will be one of those
2978 -- defined in the procedure initSqlStmts, above. It is impossible to
2979 -- anticipate the various combinations and order of occurance of the bind
2980 -- variables involved in these stmts, since the formula expression may use
2981 -- any, all or no variable(s) in any order the customer desires. Since
2982 -- native dynamic SQL can only bind values based on the relative position
2983 -- within the stmt, it cannot support this requirement. DBMS_SQL can do
2984 -- the bind by variable name and so must be used instead.
2985 --
2986 -- For example...
2987 -- Given that cctr_org, nat acct, IC, product and user1 are GCS active
2988 -- dims. The dim set has cctr_org, nat acct and user1 selected. The
2989 -- sqlStmt used is 5. A formula is assumed as shown.
2990 --
2991 -- The resulting stmt would look like this:
2992 --
2993 -- SELECT rowidtochar(e.rowid) row_id,
2994 -- ( 1 - :now )
2995 -- * sum( t1.debit_amount - t1.credit_amount )
2996 -- + sum(ee.ytd_debit_balance_e - ee.ytd_credit_balance_e) formula,
2997 -- sum( t1.debit_amount - t1.credit_amount ) T1_AMT,
2998 -- 0 T2_AMT,
2999 -- 0 PE_AMT,
3000 -- 0 ce_AMT,
3001 -- sum(ee.ytd_debit_balance_e - ee.ytd_credit_balance_e) EE_AMT
3002 -- FROM gcs_ad_trial_balances t1,
3003 -- fem_balances b,
3004 -- GCS_ENTRIES_GT e
3005 -- WHERE t1.ad_transaction_id = :xns
3006 -- AND t1.trial_balance_seq = 1
3007 -- AND b.entity_id = :eid
3008 -- AND b.dataset_code = :dci
3009 -- AND b.currency_code = :ccy
3010 -- AND b.cal_period_id = :cpi
3011 -- AND e.rule_id = :rid
3012 -- AND e.step_seq = :seq
3013 -- AND t1.company_cost_center_org_id = e.src_company_cost_center_org
3014 -- AND t1.natural_account_id = e.src_natural_account_id
3015 -- AND t1.user_dim1_id = e.src_user_dim1_id
3016 -- AND t1.product_id = e.src_product_id
3017 -- AND t1.intercompany_id = e.src_intercompany_id
3018 -- AND b.company_cost_center_org_id = e.src_company_cost_center_org
3019 -- AND b.natural_account_id = e.src_natural_account_id
3020 -- AND b.user_dim1_id = e.src_user_dim1_id
3021 -- AND b.product_id = e.src_product_id
3022 -- AND b.intercompany_id = e.src_intercompany_id
3023 -- GROUP BY e.rowid;
3024 --
3025 -- The group by and aggregation operators (in the select list) are
3026 -- necessary since we may have multiple rows with different object_ids
3027 -- in FEM_BALANCES that have identical dimension values otherwise.
3028 -- This may also occur in an ADTB (not sure so aggregating anyhow).
3029 -- However, any formula that has sql_statement = 0 won't need to aggregate.
3030 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3031 procedure execFormulas(stepSeq IN Number) is
3032
3033 procedureName varchar2(30);
3034
3035 row_id DBMS_SQL.varchar2_table;
3036 sourceAmt DBMS_SQL.number_table;
3037 targetAmt DBMS_SQL.number_table;
3038 t1Amt DBMS_SQL.number_table;
3039 t2Amt DBMS_SQL.number_table;
3040 peAmt DBMS_SQL.number_table;
3041 ceAmt DBMS_SQL.number_table;
3042 eeAmt DBMS_SQL.number_table;
3043
3044 dc1 integer;
3045 i number := 0; --a re-usable index
3046 stmt number := 0; --SQL statement index
3047 varList GCS_ELIM_RULE_STEPS_B.compiled_variables%TYPE;
3048 varName varchar2(10);
3049
3050 fetchSize CONSTANT integer := 2000;
3051 listDelim CONSTANT varchar2(10) := ',';
3052
3053 selClause Varchar2(1000);
3054
3055 --Bugfix 6242317: Determine the number of rows for the specific step
3056 lRowCount NUMBER(15);
3057
3058 begin
3059 procedureName := 'EXEC_FORMULAS';
3060
3061 logString(procedureLogLevel,
3062 procedureName,
3063 'begin',
3064 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
3065
3066 dc1 := DBMS_SQL.open_cursor;
3067
3068 --=======================================================
3069 logString(eventLogLevel, procedureName, 'section', '1');
3070 --=======================================================
3071
3072 row_id.DELETE;
3073 sourceAmt.DELETE;
3074 targetAmt.DELETE;
3075 t1Amt.DELETE;
3076 t2Amt.DELETE;
3077 peAmt.DELETE;
3078 ceAmt.DELETE;
3079 eeAmt.DELETE;
3080 --The gcs_formula_statements table uses offset index values starting with 0
3081 --The bulk select that fills the stmts array assigns keys starting with 1
3082 --JH 4.29.04: updated the offset.
3083 stmt := 0;
3084
3085 stmt := stepData(stepSeq).sql_statement_num + 1;
3086
3087 -- Begin the statement
3088 selClause := 'SELECT rowidtochar(e.rowid) row_id, ' || '
3089 ' || stepData(stepSeq).parsed_formula || ' formula ';
3090
3091 sqlStmt := stmts(stmt).statement_text;
3092
3093 --=======================================================
3094 logString(eventLogLevel, procedureName, 'section', '2');
3095 --=======================================================
3096
3097 -- Add the dimension join conditions
3098 if stmt between 2 and 8 then
3099
3100 modelJoinClause; --fills modJoinClause with a "plain vanilla" clause
3101
3102 -- Are any t1. aliases used?
3103 if instr(stmts(stmt).statement_text, 't1.') > 0 then
3104 selClause := selClause || '
3105 ,sum( nvl(t1.debit_amount,0) - nvl(t1.credit_amount,0) ) T1_AMT ';
3106 dimJoinClause := replace(modJoinClause, 'right.', 'e.src_');
3107 dimJoinClause := replace(dimJoinClause, 'left.', 't1.');
3108 dimJoinClause := replace(dimJoinClause, '=', '(+) = ');
3109 sqlStmt := sqlStmt || dimJoinClause;
3110 else
3111 selClause := selClause || ',
3112 0 T1_AMT ';
3113 end if;
3114
3115 -- Are any t2. aliases used?
3116 if instr(stmts(stmt).statement_text, 't2.') > 0 then
3117 selClause := selClause || '
3118 ,sum( nvl(t2.debit_amount,0) - nvl(t2.credit_amount,0) ) T2_AMT ';
3119 dimJoinClause := replace(modJoinClause, 'right.', 'e.src_');
3120 dimJoinClause := replace(dimJoinClause, 'left.', 't2.');
3121 dimJoinClause := replace(dimJoinClause, '=', '(+) = ');
3122 sqlStmt := sqlStmt || dimJoinClause;
3123 else
3124 selClause := selClause || ',
3125 0 T2_AMT ';
3126 end if;
3127
3128 -- Are any pe. aliases used?
3129 if instr(stmts(stmt).statement_text, 'pe.') > 0 then
3130 selClause := selClause || '
3131 ,sum( nvl(pe.ytd_debit_balance_e,0) - nvl(pe.ytd_credit_balance_e,0) ) PE_AMT ';
3132 dimJoinClause := replace(modJoinClause, 'right.', 'e.src_');
3133 dimJoinClause := replace(dimJoinClause, 'left.', 'pe.src_');
3134 dimJoinClause := replace(dimJoinClause, '=', '(+) = ');
3135 sqlStmt := sqlStmt || dimJoinClause;
3136 else
3137 selClause := selClause || ',
3138 0 PE_AMT ';
3139 end if;
3140
3141 -- Are any se. aliases used?
3142 if instr(stmts(stmt).statement_text, 'se.') > 0 then
3143 selClause := selClause || '
3144 ,sum( nvl(se.ytd_debit_balance_e,0) - nvl(se.ytd_credit_balance_e,0) ) CE_AMT ';
3145 dimJoinClause := replace(modJoinClause, 'right.', 'e.src_');
3146 dimJoinClause := replace(dimJoinClause, 'left.', 'se.src_');
3147 dimJoinClause := replace(dimJoinClause, '=', '(+) = ');
3148 sqlStmt := sqlStmt || dimJoinClause;
3149 else
3150 selClause := selClause || ',
3151 0 CE_AMT ';
3152 end if;
3153
3154 -- Are any ee. aliases used?
3155 --JH 4.29.04: Add outer joins
3156
3157 if instr(stmts(stmt).statement_text, 'ee.') > 0 then
3158 selClause := selClause || '
3159 ,sum( nvl(ee.ytd_debit_balance_e,0) - nvl(ee.ytd_credit_balance_e,0) ) EE_AMT ';
3160 dimJoinClause := replace(modJoinClause, 'right.', 'e.src_');
3161 dimJoinClause := replace(dimJoinClause, 'left.', 'ee.src_');
3162 dimJoinClause := replace(dimJoinClause, '=', '(+) = ');
3163 sqlStmt := sqlStmt || dimJoinClause;
3164 else
3165 selClause := selClause || ',
3166 0 EE_AMT ';
3167 end if;
3168
3169 --JH 5.3.04: Add values for where statement_num <1
3170 else
3171 selClause := selClause || ',
3172 0 T1_AMT,
3173 0 T2_AMT,
3174 0 PE_AMT,
3175 0 CE_AMT,
3176 0 EE_AMT ';
3177
3178 end if;
3179
3180 --Add the GROUP BY clause
3181 sqlStmt := sqlStmt || '
3182 GROUP BY e.rowid';
3183
3184 --=======================================================
3185 logString(eventLogLevel, procedureName, 'section', '3');
3186 --=======================================================
3187
3188 -- Parse the stmt
3189 sqlStmt := selClause || sqlStmt;
3190 logString(statementLogLevel,
3191 procedureName,
3192 'stmt',
3193 'sqlStmt =
3194 ' || sqlStmt);
3195 DBMS_SQL.parse(dc1, sqlStmt, 1);
3196
3197 --=======================================================
3198 logString(eventLogLevel, procedureName, 'section', '4');
3199 --=======================================================
3200
3201 --Variables may not be embedded in the parsed_formula string
3202 --in the order they appear in the compiled_variables string
3203 varList := stepData(stepSeq).compiled_variables;
3204
3205 SELECT count(*)
3206 INTO lRowCount
3207 FROM gcs_entries_gt
3208 WHERE rule_id = stepData(stepSeq).rule_id
3209 AND step_seq = stepSeq
3210 AND offset_flag = 'N';
3211
3212 logString(statementLogLevel,
3213 procedureName,
3214 'bind',
3215 'number of rows=' || lRowCount);
3216
3217 While varList IS NOT NULL Loop
3218
3219 i := instr(varList, listDelim);
3220 if i = 0 then
3221 i := length(varList) + 1;
3222 end if;
3223 varName := lower(substr(varList, 1, (i - 1)));
3224 varList := substr(varList, (i + 2));
3225
3226 logString(statementLogLevel,
3227 procedureName,
3228 'bind',
3229 'varName=' || varName);
3230
3231 if varName = 'coi' then
3232 logString(statementLogLevel,
3233 procedureName,
3234 'bind',
3235 'coi=' || to_char(ruleData.consideration));
3236
3237 if (stepData(stepSeq).sql_statement_num = 0) then
3238 logString(statementLogLevel,
3239 procedureName,
3240 'bind',
3241 'truecoi=' || ruleData.consideration);
3242 DBMS_SQL.bind_variable(dc1, 'coi', ruleData.consideration);
3243 else
3244 --Bugfix 6242317: Need to Divide COI by the number of rows for the specific step
3245 if (lRowCount <> 0) then
3246 logString(statementLogLevel,
3247 procedureName,
3248 'bind',
3249 'truecoi=' || ruleData.consideration / lRowCount);
3250 DBMS_SQL.bind_variable(dc1,
3251 'coi',
3252 ruleData.consideration / lRowCount);
3253 else
3254 logString(statementLogLevel,
3255 procedureName,
3256 'bind',
3257 'truecoi=0');
3258 DBMS_SQL.bind_variable(dc1, 'coi', 0);
3259 end if;
3260 end if;
3261 elsif varName = 'nav' then
3262 --Bugfix 6511825: Need to Divide NAV by the number of rows for the specific step
3263 if (stepData(stepSeq).sql_statement_num = 0) then
3264 logString(statementLogLevel,
3265 procedureName,
3266 'bind',
3267 'truenav=' || ruleData.netAssetValue);
3268 DBMS_SQL.bind_variable(dc1, 'nav', ruleData.netAssetValue);
3269 else
3270 if (lRowCount <> 0) then
3271 logString(statementLogLevel,
3272 procedureName,
3273 'bind',
3274 'truenav=' || to_char(ruleData.netAssetValue / lRowCount));
3275 DBMS_SQL.bind_variable(dc1,
3276 'nav',
3277 ruleData.netAssetValue / lRowCount);
3278 else
3279 logString(statementLogLevel, procedureName, 'bind', 'nav=0');
3280 DBMS_SQL.bind_variable(dc1, 'nav', 0);
3281 end if;
3282 end if;
3283 elsif varName = 'now' then
3284 logString(statementLogLevel,
3285 procedureName,
3286 'bind',
3287 'now=' || to_char(ruleData.toPercent));
3288 DBMS_SQL.bind_variable(dc1, 'now', ruleData.toPercent);
3289
3290 elsif varName = 'was' then
3291 logString(statementLogLevel,
3292 procedureName,
3293 'bind',
3294 'was=' || to_char(ruleData.fromPercent));
3295 DBMS_SQL.bind_variable(dc1, 'was', ruleData.fromPercent);
3296
3297 elsif varName = 'own' then
3298 logString(statementLogLevel,
3299 procedureName,
3300 'bind',
3301 'own=' || to_char(ruleData.toPercent));
3302 DBMS_SQL.bind_variable(dc1, 'own', ruleData.toPercent);
3303
3304 elsif varName = 'min' then
3305 logString(statementLogLevel,
3306 procedureName,
3307 'bind',
3308 'min=' || to_char(1 - ruleData.toPercent));
3309 DBMS_SQL.bind_variable(dc1, 'own', ruleData.toPercent);
3310
3311 else
3312 FND_MESSAGE.set_name('GCS', 'GCS_INVALID_VARIABLE');
3313 FND_MESSAGE.set_token('PROCEDURE',
3314 packageName || '.' || procedureName);
3315 FND_MESSAGE.set_token('VAR_NAME', varName);
3316 logString(exceptionLogLevel,
3317 procedureName,
3318 'exception',
3319 'invalid_variable');
3320 RAISE invalid_variable;
3321 end if;
3322 End Loop; --while varlist is not null
3323
3324 --=======================================================
3325 logString(eventLogLevel, procedureName, 'section', '5');
3326 --=======================================================
3327
3328 --Bind variables specific to each statement's WHERE clause
3329 varList := stmts(stmt).compiled_variables;
3330
3331 While varList IS NOT NULL Loop
3332
3333 i := instr(varList, listDelim);
3334 if i = 0 then
3335 i := length(varList) + 1;
3336 end if;
3337 varName := lower(substr(varList, 1, (i - 1)));
3338 varList := substr(varList, (i + 1));
3339
3340 if varName = 'cpi' then
3341 logString(statementLogLevel,
3342 procedureName,
3343 'bind',
3344 'cpi=' || to_char(contextData.calPeriodId));
3345 DBMS_SQL.bind_variable(dc1, 'cpi', contextData.calPeriodId);
3346
3347 elsif varName = 'eid' then
3348 logString(statementLogLevel,
3349 procedureName,
3350 'bind',
3351 'eid=' || to_char(contextData.elimsEntity));
3352 DBMS_SQL.bind_variable(dc1, 'eid', contextData.elimsEntity);
3353
3354 elsif varName = 'sei' then
3355 logString(statementLogLevel,
3356 procedureName,
3357 'bind',
3358 'sei=' || to_char(contextData.childEntity));
3359 DBMS_SQL.bind_variable(dc1, 'sei', contextData.childEntity);
3360
3361 elsif varName = 'pei' then
3362 logString(statementLogLevel,
3363 procedureName,
3364 'bind',
3365 'pei=' || to_char(contextData.parentEntity));
3366 DBMS_SQL.bind_variable(dc1, 'pei', contextData.parentEntity);
3367
3368 elsif varName = 'dci' then
3369 logString(statementLogLevel,
3370 procedureName,
3371 'bind',
3372 'dci=' || to_char(contextData.datasetCode));
3373 DBMS_SQL.bind_variable(dc1, 'dci', contextData.datasetCode);
3374
3375 elsif varName = 'ccy' then
3376 logString(statementLogLevel,
3377 procedureName,
3378 'bind',
3379 'ccy=' || contextData.currencyCode);
3380 DBMS_SQL.bind_variable(dc1, 'ccy', contextData.currencyCode);
3381
3382 elsif varName = 'xns' then
3383 logString(statementLogLevel,
3384 procedureName,
3385 'bind',
3386 'xns=' || to_char(contextData.eventKey));
3387 DBMS_SQL.bind_variable(dc1, 'xns', contextData.eventKey);
3388
3389 elsif varName = 'rid' then
3390 logString(statementLogLevel,
3391 procedureName,
3392 'bind',
3393 'rid=' || to_char(stepData(stepSeq).rule_id));
3394 DBMS_SQL.bind_variable(dc1, 'rid', stepData(stepSeq).rule_id);
3395
3396 elsif varName = 'seq' then
3397 logString(statementLogLevel,
3398 procedureName,
3399 'bind',
3400 'seq=' || to_char(stepSeq));
3401 DBMS_SQL.bind_variable(dc1, 'seq', stepSeq);
3402
3403 else
3404 FND_MESSAGE.set_name('GCS', 'GCS_INVALID_VARIABLE');
3405 FND_MESSAGE.set_token('PROCEDURE',
3406 packageName || '.' || procedureName);
3407 FND_MESSAGE.set_token('VAR_NAME', varName);
3408 logString(exceptionLogLevel,
3409 procedureName,
3410 'exception',
3411 'invalid_variable');
3412 RAISE invalid_variable;
3413 end if;
3414 End Loop; --while varlist is not null
3415
3416 --=======================================================
3417 logString(eventLogLevel, procedureName, 'section', '6');
3418 --=======================================================
3419
3420 --Define columns
3421 DBMS_SQL.define_array(dc1, 1, row_id, fetchSize, 1);
3422 DBMS_SQL.define_array(dc1, 2, targetAmt, fetchSize, 1);
3423 DBMS_SQL.define_array(dc1, 3, t1Amt, fetchSize, 1);
3424 DBMS_SQL.define_array(dc1, 4, t2Amt, fetchSize, 1);
3425 DBMS_SQL.define_array(dc1, 5, peAmt, fetchSize, 1);
3426 DBMS_SQL.define_array(dc1, 6, ceAmt, fetchSize, 1);
3427 DBMS_SQL.define_array(dc1, 7, eeAmt, fetchSize, 1);
3428
3429 --=======================================================
3430 logString(eventLogLevel, procedureName, 'section', '7');
3431 --=======================================================
3432
3433 --Execute the dynamic cursor
3434 i := DBMS_SQL.execute(dc1);
3435
3436 --=======================================================
3437 logString(eventLogLevel, procedureName, 'section', '8');
3438 --=======================================================
3439
3440 --Fetch through the cursor (see DEFINE_ARRAY() calls above)
3441 i := fetchSize;
3442
3443 While i = fetchSize Loop
3444 i := DBMS_SQL.fetch_rows(dc1);
3445 DBMS_SQL.column_value(dc1, 1, row_id);
3446 DBMS_SQL.column_value(dc1, 2, targetAmt);
3447 DBMS_SQL.column_value(dc1, 3, t1Amt);
3448 DBMS_SQL.column_value(dc1, 4, t2Amt);
3449 DBMS_SQL.column_value(dc1, 5, peAmt);
3450 DBMS_SQL.column_value(dc1, 6, ceAmt);
3451 DBMS_SQL.column_value(dc1, 7, eeAmt);
3452 End Loop; --DBMS_SQL.fetch_rows loop
3453
3454 logString(statementLogLevel,
3455 procedureName,
3456 'stmt',
3457 to_char(row_id.COUNT) || ' row(s) fetched');
3458
3459 --=======================================================
3460 logString(eventLogLevel, procedureName, 'section', '9');
3461 --=======================================================
3462
3463 --Update the ENTRIES table
3464 if row_id.FIRST IS NOT NULL then
3465
3466 FORALL r IN row_id.FIRST .. row_id.LAST
3467 UPDATE GCS_ENTRIES_GT
3468 SET output_amount = decode(offset_flag,
3469 'N',
3470 targetAmt(r),
3471 -1 * targetAmt(r)),
3472 ad_input_amount = t1Amt(r) + t2Amt(r),
3473 pe_input_amount = peAmt(r),
3474 ce_input_amount = ceAmt(r),
3475 ee_input_amount = eeAmt(r)
3476 WHERE rowid = chartorowid(row_id(r));
3477 logString(statementLogLevel,
3478 procedureName,
3479 'stmt',
3480 to_char(SQL%ROWCOUNT) || ' row(s) updated');
3481 end if;
3482
3483 DBMS_SQL.close_cursor(dc1);
3484
3485 -- Done
3486 logString(procedureLogLevel,
3487 procedureName,
3488 'end',
3489 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
3490
3491 exception
3492 when others then
3493 -- make sure we close the cursor and then RAISE
3494 if DBMS_SQL.is_open(dc1) then
3495 DBMS_SQL.close_cursor(dc1);
3496 end if;
3497 RAISE;
3498
3499 end execFormulas;
3500
3501 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3502 -- The result of this procedure is a GCS_ENTRIES_GT table with the fully
3503 -- resolved dimension member combinations for every line of every dimension
3504 -- member set for each step for the rule. The table will have the source
3505 -- and target dimensions, the input and out values for the formula, the
3506 -- step name and friendly formula text, etc.
3507 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3508 procedure processRuleSteps is
3509
3510 procedureName varchar(30);
3511 i number := 0;
3512 j number := 0;
3513
3514 k varchar2(30);
3515
3516 categoryInfo getCategory%ROWTYPE;
3517
3518 cursor isTgtOnly(rsi number) is
3519 SELECT 1
3520 FROM GCS_RULE_SCOPE_DTLS T, GCS_RULE_SCOPE_DIMS D
3521 WHERE D.ALL_SOURCE_MEMBERS_FLAG = 'N'
3522 AND D.TARGET_MEMBER_ID IS NOT NULL
3523 AND D.RULE_STEP_ID = T.RULE_STEP_ID
3524 AND D.RULE_STEP_ID = rsi;
3525
3526 cursor getSpecificIntercoId is
3527 SELECT SPECIFIC_INTERCOMPANY_ID
3528 FROM GCS_CATEGORIES_B
3529 WHERE CATEGORY_CODE = 'INTRACOMPANY';
3530
3531 --Bugfix 4928211 (STK): Added organization and intercompany values over here
3532 organizationId number;
3533 intercompanyId number;
3534
3535 begin
3536 procedureName := 'PROCESS_RULE_STEPS';
3537
3538 logString(procedureLogLevel,
3539 procedureName,
3540 'begin',
3541 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
3542
3543 --On the first go round we have to get the steps and resolve the dim_sets
3544 if ruleIteration = 1 then
3545
3546 --Make sure these are empty when we start!
3547 stepData.DELETE;
3548 ruleStepId := -1;
3549
3550 --Bugfix 4928211 (STK): Added org and interco values here to make code more performance
3551 --Do this only for consolidation rules for the time being
3552 --Get the category info
3553 Open getCategory;
3554 Fetch getCategory
3555 Into categoryInfo;
3556 If getCategory%NOTFOUND or categoryInfo.entityId = -1 Then
3557 Close getCategory;
3558 logString(exceptionLogLevel,
3559 procedureName,
3560 'eventCategory => ',
3561 contextData.eventCategory);
3562 logString(exceptionLogLevel,
3563 procedureName,
3564 'parentEntityId => ',
3565 contextData.parentEntity);
3566 logString(exceptionLogLevel,
3567 procedureName,
3568 'childEntityId => ',
3569 contextData.childEntity);
3570 logString(exceptionLogLevel,
3571 procedureName,
3572 'elimEntityId => ',
3573 contextData.elimsEntity);
3574 RAISE invalid_category_code;
3575 End If;
3576 Close getCategory;
3577
3578 if (contextData.eventType = 'C') then
3579
3580 --Bugfix 6160542: Added Elimination Base Org
3581 --Populate values for company_cost_center_org and intercompany
3582 if categoryInfo.org_output_code = 'BASE_ORG' then
3583 organizationId := GCS_UTILITY_PKG.get_org_id(contextData.parentEntity,
3584 contextData.hierarchy);
3585 elsif categoryInfo.org_output_code = 'ELIM_BASE_ORG' then
3586 organizationId := GCS_UTILITY_PKG.get_base_org_id(contextData.elimsEntity);
3587 else
3588 organizationId := GCS_UTILITY_PKG.get_org_id(contextData.childEntity,
3589 contextData.hierarchy);
3590 end if;
3591
3592 if organizationId = -1 then
3593 logString(exceptionLogLevel,
3594 procedureName,
3595 'bind',
3596 'contextData.parentEntity => ' ||
3597 to_char(contextData.parentEntity));
3598 logString(exceptionLogLevel,
3599 procedureName,
3600 'bind',
3601 'contextData.childEntity => ' ||
3602 to_char(contextData.childEntity));
3603 logString(exceptionLogLevel,
3604 procedureName,
3605 'bind',
3606 'contextData.hierarchy => ' ||
3607 to_char(contextData.hierarchy));
3608 RAISE no_default_cctr_found;
3609 end if; --if orgId = -2
3610
3611 --Bugfix 4928211: Determine the organization and intercompany target value
3612 SELECT NVL(specific_intercompany_id, organizationId)
3613 INTO intercompanyId
3614 FROM gcs_categories_b
3615 WHERE category_code = 'INTRACOMPANY';
3616
3617 end if; -- if contextData.eventType = 'C'
3618
3619 logString(statementLogLevel,
3620 procedureName,
3621 'parameter',
3622 'org_output = ' || categoryInfo.org_output_code);
3623 --changes made by yingiu
3624 /*
3625 logString( statementLogLevel, procedureName, 'parameter',
3626 'interco_output = ' || categoryInfo.interco_output_code);
3627 */
3628 -- end of changes by yingliu
3629
3630 --=======================================================
3631 logString(eventLogLevel, procedureName, 'section', '2');
3632 --=======================================================
3633
3634 For s In getSteps Loop
3635
3636 --we select the steps in order by the dimension_set_id, so that we
3637 --can detect when that value breaks from fetch to fetch, but we want
3638 --to access the stepData array by the step_seq, since it is unique
3639 --here and the dim_set_id is not. So, let's loop through and assign
3640 --the fetched rows into the array using the desired index, while we
3641 --process each dimension set in order.
3642 i := s.step_seq;
3643 stepData(i) := s;
3644
3645 --Resolve the dimension set if the value breaks since the last fetch
3646 --Skip this if we are doing the STAT iteration of a monetary entity
3647 If stepData(i).rule_step_id <> ruleStepId Then
3648
3649 --We have not resolved this set id yet
3650 ruleStepId := stepData(i).rule_step_id;
3651
3652 End If; --If stepData(i).rule_step_id <> ruleStepId Then
3653
3654 End Loop; --For s In getSteps
3655
3656 -- Check data integrity
3657 if stepData.FIRST IS NULL then
3658 RAISE rule_has_no_steps;
3659 end if;
3660
3661 end if; --if ruleIteration = 1
3662
3663 --=======================================================
3664 logString(eventLogLevel, procedureName, 'section', '3');
3665 --=======================================================
3666
3667 --Loop through each step and...
3668 -- initialize a row in GCS_ENTRIES_GT for each source/target dim combo pair
3669 -- execute the formula for the step and update GCS_ENTRIES_GT for the result
3670 i := stepData.FIRST;
3671 While i IS NOT NULL Loop
3672
3673 --=======================================================
3674 logString(eventLogLevel,
3675 procedureName,
3676 'step',
3677 'begin ' || to_char(i));
3678 --=======================================================
3679
3680 -- Initialize rows in gcs_entries_gt for all steps
3681 ruleStepId := stepData(i).rule_step_id;
3682
3683 -- Fill the Dims arrays for this rule scope
3684 selectDims.DELETE;
3685 For r in getDimSelections(ruleStepId) Loop
3686 selectDims(r.column_name) := r;
3687 logString(statementLogLevel,
3688 procedureName,
3689 'rule_scope_dimension',
3690 'Name, ID, FEM?, GCS? = ' || dimInfo(r.column_name)
3691 .column_name || ', ' ||
3692 to_char(dimInfo(r.column_name).dimension_id) || ', ' ||
3693 dimInfo(r.column_name)
3694 .required_for_fem || ', ' || dimInfo(r.column_name)
3695 .required_for_gcs);
3696 End Loop;
3697
3698 --Stop if there are no dimensions used by this dim set
3699 If selectDims.COUNT = 0 Then
3700 RAISE invalid_dim_set_id;
3701 End If;
3702
3703 logString(eventLogLevel, procedureName, 'section', '4');
3704
3705 --Bugfix 4928211: Modified initEntriesGT for performance purposes for Consolidation Rules
3706 --Changes for A Rules will follow
3707 if (contextData.eventType = 'A') then
3708 initEntriesGT_stdDimSet(i, categoryInfo);
3709
3710 --SKAMDAR: Deleted obsolete code path to make code easier to read
3711
3712 -- Execute each step's formula and store the result
3713 -- NOTE: This has to be inside the loop since each step may have
3714 -- a different formula, each formula may have a different
3715 -- sql_statement requirement and so must be dynamically
3716 -- executed separately.
3717 execFormulas(i);
3718
3719 else
3720 initEntriesGT(i, categoryInfo, organizationId, intercompanyId);
3721 end if;
3722 --=======================================================
3723 logString(eventLogLevel, procedureName, 'step', 'end ' || to_char(i));
3724 --=======================================================
3725
3726 i := stepData.NEXT(i);
3727
3728 End Loop; --While i IS NOT NULL
3729
3730 -- Done
3731 logString(procedureLogLevel,
3732 procedureName,
3733 'end',
3734 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
3735
3736 end processRuleSteps;
3737
3738 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3739 -- Write rows into the GCS_ENTRY_[HEADERS|LINES] tables.
3740 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3741 Function writeEntryData(linesData IN lineRec,
3742 categoryInfo IN getCategory%ROWTYPE) Return NUMBER is
3743
3744 procedureName varchar(30);
3745
3746 cursor getEntryId is
3747 Select gcs_entry_headers_s.nextval From dual;
3748 entryId gcs_entry_headers.entry_id%TYPE := -1;
3749
3750 cursor getADEntryId is
3751 SELECT B.assoc_entry_id
3752 FROM GCS_AD_TRANSACTIONS B
3753 WHERE B.AD_TRANSACTION_ID = contextData.eventKey;
3754
3755 errbuf varchar2(100);
3756 retcode varchar2(100);
3757 endCalPers number;
3758 procCode varchar2(50);
3759
3760 begin
3761 procedureName := 'WRITE_ENTRY_DATA';
3762 endCalPers := contextData.calPeriodId;
3763 procCode := 'SINGLE_RUN_FOR_PERIOD';
3764
3765 logString(procedureLogLevel,
3766 procedureName,
3767 'begin',
3768 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
3769
3770 -- Get an entry_id
3771 entryId := -1;
3772
3773 if contextData.eventType <> 'A' then
3774 Open getEntryId;
3775 Fetch getEntryId
3776 Into entryId;
3777 Close getEntryId;
3778 else
3779 Open getADEntryId;
3780 Fetch getADEntryId
3781 Into entryId;
3782 Close getADEntryId;
3783 end if;
3784
3785 --Save the id so we can update the gcs_cons_eng_run_dtls table
3786 if ruleIteration = 1 then
3787 mainEntryId := entryId;
3788 elsif ruleIteration = 2 then
3789 statEntryId := entryId;
3790 end if;
3791
3792 -- Set values for some of the create_entry_header API args
3793 if contextData.eventType = 'A' then
3794 procCode := 'ALL_RUN_FOR_PERIOD';
3795 endCalPers := null;
3796 end if;
3797
3798 --=======================================================
3799 --Write the entry header record
3800 logString(eventLogLevel, procedureName, 'section', '2');
3801 --=======================================================
3802
3803 logString(statementLogLevel,
3804 procedureName,
3805 'GCS_ENTRY_PKG.create_entry_header parameter',
3806 'p_entry_id=>' || to_char(entryId));
3807
3808 logString(statementLogLevel,
3809 procedureName,
3810 'GCS_ENTRY_PKG.create_entry_header parameter',
3811 'p_hierarchy_id=>' || to_char(contextData.hierarchy));
3812
3813 logString(statementLogLevel,
3814 procedureName,
3815 'GCS_ENTRY_PKG.create_entry_header parameter',
3816 'p_entity_id=>' || to_char(categoryInfo.entityId));
3817
3818 logString(statementLogLevel,
3819 procedureName,
3820 'GCS_ENTRY_PKG.create_entry_header parameter',
3821 'p_start_cal_period_id=>' || to_char(contextData.calPeriodId));
3822
3823 logString(statementLogLevel,
3824 procedureName,
3825 'GCS_ENTRY_PKG.create_entry_header parameter',
3826 'p_end_cal_period_id=>' || to_char(endCalPers));
3827
3828 logString(statementLogLevel,
3829 procedureName,
3830 'GCS_ENTRY_PKG.create_entry_header parameter',
3831 'p_entry_type_code=>' || 'AUTOMATIC');
3832
3833 logString(statementLogLevel,
3834 procedureName,
3835 'GCS_ENTRY_PKG.create_entry_header parameter',
3836 'p_balance_type_code=>' || 'ACTUAL');
3837
3838 logString(statementLogLevel,
3839 procedureName,
3840 'GCS_ENTRY_PKG.create_entry_header parameter',
3841 'p_currency_code=>' || contextData.currencyCode);
3842
3843 logString(statementLogLevel,
3844 procedureName,
3845 'GCS_ENTRY_PKG.create_entry_header parameter',
3846 'p_process_code=>' || procCode);
3847
3848 logString(statementLogLevel,
3849 procedureName,
3850 'GCS_ENTRY_PKG.create_entry_header parameter',
3851 'p_category_code=>' || contextData.eventCategory);
3852
3853 logString(statementLogLevel,
3854 procedureName,
3855 'GCS_ENTRY_PKG.create_entry_header parameter',
3856 'p_xlate_flag=>' || 'N');
3857
3858 logString(statementLogLevel,
3859 procedureName,
3860 'GCS_ENTRY_PKG.create_entry_header parameter',
3861 'p_rule_id=>' || to_char(ruleId));
3862
3863 --Create new entry on if this is not an AD event.
3864 if contextData.eventType <> 'A' then
3865 GCS_ENTRY_PKG.create_entry_header(X_ERRBUF => errbuf,
3866 X_RETCODE => retcode,
3867 P_ENTRY_ID => entryId,
3868 P_HIERARCHY_ID => contextData.hierarchy,
3869 P_ENTITY_ID => categoryInfo.entityId,
3870 P_START_CAL_PERIOD_ID => contextData.calPeriodId,
3871 P_END_CAL_PERIOD_ID => endCalPers,
3872 P_ENTRY_TYPE_CODE => 'AUTOMATIC',
3873 P_BALANCE_TYPE_CODE => 'ACTUAL',
3874 P_CURRENCY_CODE => contextData.currencyCode,
3875 P_PROCESS_CODE => procCode,
3876 P_CATEGORY_CODE => contextData.eventCategory,
3877 P_XLATE_FLAG => 'N',
3878 P_RULE_ID => ruleId);
3879
3880 if retcode = fnd_api.g_ret_sts_unexp_error then
3881 --Handler is in process_rule proc
3882 logString(exceptionLogLevel,
3883 procedureName,
3884 'exception',
3885 'GCS_ENTRY_PKG.create_entry_header: ' || errbuf);
3886 RAISE entry_header_error;
3887 end if;
3888
3889 else
3890
3891 --bug 4253081: update GCS_ENTRY_HEADER with RULE_ID
3892 UPDATE GCS_ENTRY_HEADERS
3893 SET RULE_ID = ruleId
3894 WHERE ENTRY_ID = entryId;
3895
3896 logString(statementLogLevel,
3897 procedureName,
3898 'stmt',
3899 to_char(SQL%ROWCOUNT) || ' row(s) updated');
3900
3901 DELETE FROM GCS_ENTRY_LINES WHERE ENTRY_ID = entryId;
3902
3903 logString(statementLogLevel,
3904 procedureName,
3905 'stmt',
3906 to_char(SQL%ROWCOUNT) || ' row(s) deleted');
3907
3908 end if; --if contextData.eventType <> 'A' then
3909
3910 --=======================================================
3911 logString(eventLogLevel, procedureName, 'section', '3');
3912 --=======================================================
3913
3914 -- Insert the records
3915
3916 ForAll x In linesData.line_item_id.FIRST .. linesData.line_item_id.LAST
3917
3918 Insert Into gcs_entry_lines
3919 (entry_id,
3920 company_cost_center_org_id,
3921 financial_elem_id,
3922 product_id,
3923 natural_account_id,
3924 channel_id,
3925 line_item_id,
3926 project_id,
3927 customer_id,
3928 intercompany_id,
3929 task_id,
3930 user_dim1_id,
3931 user_dim2_id,
3932 user_dim3_id,
3933 user_dim4_id,
3934 user_dim5_id,
3935 user_dim6_id,
3936 user_dim7_id,
3937 user_dim8_id,
3938 user_dim9_id,
3939 user_dim10_id,
3940 ytd_balance_e,
3941 ytd_debit_balance_e,
3942 ytd_credit_balance_e,
3943 creation_date,
3944 created_by,
3945 last_update_date,
3946 last_updated_by,
3947 last_update_login,
3948 description)
3949 Values
3950 (entryId,
3951 linesData.cctr_org_id(x),
3952 linesData.finl_elem_id(x),
3953 linesData.product_id(x),
3954 linesData.nat_acct_id(x),
3955 linesData.channel_id(x),
3956 linesData.line_item_id(x),
3957 linesData.project_id(x),
3958 linesData.customer_id(x),
3959 linesData.interco_id(x),
3960 linesData.task_id(x),
3961 linesData.user_dim1_id(x),
3962 linesData.user_dim2_id(x),
3963 linesData.user_dim3_id(x),
3964 linesData.user_dim4_id(x),
3965 linesData.user_dim5_id(x),
3966 linesData.user_dim6_id(x),
3967 linesData.user_dim7_id(x),
3968 linesData.user_dim8_id(x),
3969 linesData.user_dim9_id(x),
3970 linesData.user_dim10_id(x),
3971 linesData.net_amount(x) * linesData.balance_factor(x),
3972 decode(abs(linesData.net_amount(x)),
3973 linesData.net_amount(x),
3974 linesData.net_amount(x),
3975 0),
3976 decode(abs(linesData.net_amount(x)),
3977 linesData.net_amount(x),
3978 0,
3979 abs(linesData.net_amount(x))),
3980 systemDate,
3981 userId,
3982 systemDate,
3983 userId,
3984 null,
3985 linesData.description(x));
3986
3987 -- Done
3988 logString(procedureLogLevel,
3989 procedureName,
3990 'end',
3991 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
3992 RETURN(entryId);
3993
3994 end writeEntryData;
3995
3996 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3997 -- Write rows into the GCS_ENTRY_[HEADERS|LINES] tables.
3998 -- Applicable to contextData.eventType = 'A' (AD activity) only.
3999 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4000 procedure createEntry is
4001
4002 procedureName varchar(30);
4003 l_suspense_exceeded_flag VARCHAR2(1);
4004
4005 entryId gcs_entry_headers.entry_id%TYPE := -1;
4006
4007 Cursor getConversionRate(amt number) Is
4008 select --+ INDEX_DESC( r, GL_DAILY_RATES_U1 )
4009 (conversion_rate * amt)
4010 from gl_daily_rates r
4011 where from_currency = suspenseData.threshold_currency
4012 and to_currency = contextData.currencyCode
4013 and conversion_date < SYSDATE
4014 and rownum < 2;
4015
4016 outOfbalance number := 0;
4017 thresholdAmt number := 0;
4018 cctrOrg number;
4019 interCompany number;
4020
4021 i binary_integer;
4022
4023 --This is a record of tables because of an issue with
4024 --using a table of records. As such there is no easy
4025 --way to DELETE it before re-using it and it is easier
4026 --to declare it locally, everytime the proc runs
4027 linesData lineRec;
4028
4029 --we need these values to look up the multiplier
4030 --that determines the SIGN of amounts we put in
4031 --the xtd_balance_e column(s)
4032 liaAttr number;
4033 liaVers number;
4034 ataAttr number;
4035 ataVers number;
4036
4037 --Used by the call to GCS_TEMPLATES_DYNAMIC_PKG
4038 templateRecord GCS_TEMPLATES_PKG.templateRecord;
4039
4040 -- See package global data declares above for cursor
4041 categoryInfo getCategory%ROWTYPE;
4042
4043 --Bugfix 6242317: Add the attribute information
4044 l_line_item_type_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
4045 .attribute_id;
4046 l_line_item_type_version NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE')
4047 .version_id;
4048 l_acct_type_attr NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE')
4049 .attribute_id;
4050 l_acct_type_version NUMBER(15) := gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE')
4051 .version_id;
4052
4053 begin
4054 procedureName := 'CREATE_ENTRY';
4055
4056 logString(procedureLogLevel,
4057 procedureName,
4058 'begin',
4059 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
4060
4061 --++++++++++++++++++++++++++++++++++++++++++++++++++++
4062 --Some things we cannot do without:
4063 -- category info
4064 -- A valid currency and precision
4065 -- An entry with at least two non-zero-amount lines
4066 -- A balance_factor to use in populating [xtd|ytd]_balance_e
4067 -- An entry that either...
4068 -- a) balances (debits = credits), or
4069 -- b) can be balanced by a suspense line, or
4070 -- c) uses the STAT currency
4071 --
4072 -- Check for these things and stop if there's any problem
4073 --++++++++++++++++++++++++++++++++++++++++++++++++++++
4074
4075 --Get the category info
4076 Open getCategory;
4077 Fetch getCategory
4078 Into categoryInfo;
4079 If getCategory%NOTFOUND or categoryInfo.entityId = -1 Then
4080 Close getCategory;
4081 logString(exceptionLogLevel,
4082 procedureName,
4083 'eventCategory => ',
4084 contextData.eventCategory);
4085 logString(exceptionLogLevel,
4086 procedureName,
4087 'parentEntityId => ',
4088 contextData.parentEntity);
4089 logString(exceptionLogLevel,
4090 procedureName,
4091 'childEntityId => ',
4092 contextData.childEntity);
4093 logString(exceptionLogLevel,
4094 procedureName,
4095 'elimEntityId => ',
4096 contextData.elimsEntity);
4097 RAISE invalid_category_code;
4098 End If;
4099 Close getCategory;
4100
4101 --Get a currency precision
4102 ccyPrecision := -1;
4103 ccyMinAcctUnit := null;
4104 Open getCurrency;
4105 Fetch getCurrency
4106 Into ccyPrecision, ccyMinAcctUnit;
4107 Close getCurrency;
4108
4109 if nvl(ccyPrecision, -1) < 0 then
4110 logString(exceptionLogLevel,
4111 procedureName,
4112 'exception',
4113 'Missing rounding data for currency code ' ||
4114 contextData.currencyCode);
4115 RAISE missing_currency_data;
4116 end if;
4117
4118 logString(statementLogLevel,
4119 procedureName,
4120 'parameter',
4121 'Currency Precision = ' ||
4122 to_char(ccyPrecision));
4123
4124 logString(statementLogLevel,
4125 procedureName,
4126 'parameter',
4127 'Currency Minimum Accountable Unit = ' ||
4128 to_char(ccyMinAcctUnit));
4129
4130 -- Entry lines are an aggregation from the gcs_entries_gt table
4131 Open getLines;
4132 Fetch getLines Bulk Collect
4133 Into linesData;
4134
4135 Close getLines;
4136
4137 --=======================================================
4138 logString(eventLogLevel, procedureName, 'section', '2');
4139 --=======================================================
4140
4141 --Set the balance_factor: this is either 1 or -1 and is
4142 --multiplied by the net_amount to populate the value of
4143 --[xtd|ytd]_balances_e columns.
4144
4145 -- To know what multiplier to use we have to get an attribute
4146 -- of an attribute of the line_item_id for every distinct lii
4147 -- in the GCS_ENTRIES_GT table. Then we can store the value
4148 -- onto the linsData.balance_factor record for use in the bulk
4149 -- insert that writes entry lines to GCS_ENTRY_LINES.
4150
4151 --Get the attribute_id and version_id for the EXTENDED_ACCOUNT_TYPE
4152 declare
4153 liaKey varchar2(100) := 'LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE';
4154 begin
4155 liaAttr := GCS_UTILITY_PKG.g_dimension_attr_info(liaKey).attribute_id;
4156 liaVers := GCS_UTILITY_PKG.g_dimension_attr_info(liaKey).version_id;
4157 exception
4158 when no_data_found then
4159 logString(exceptionLogLevel,
4160 procedureName,
4161 'exception',
4162 'missing_key');
4163 FND_MESSAGE.set_name('GCS', 'GCS_MISSING_KEY');
4164 FND_MESSAGE.set_token('HASH_KEY', liaKey);
4165 RAISE missing_key;
4166 end;
4167
4168 --=======================================================
4169 logString(eventLogLevel, procedureName, 'section', '3');
4170 --=======================================================
4171
4172 --Get the attribute_id and version_id for the EXT_ACCOUNT_TYPE_CODE
4173 declare
4174 ataKey varchar2(100) := 'EXT_ACCOUNT_TYPE_CODE-SIGN';
4175 begin
4176 ataAttr := GCS_UTILITY_PKG.g_dimension_attr_info(ataKey).attribute_id;
4177 ataVers := GCS_UTILITY_PKG.g_dimension_attr_info(ataKey).version_id;
4178 exception
4179 when no_data_found then
4180 logString(exceptionLogLevel,
4181 procedureName,
4182 'exception',
4183 'missing_key');
4184 FND_MESSAGE.set_name('GCS', 'GCS_MISSING_KEY');
4185 FND_MESSAGE.set_token('HASH_KEY', ataKey);
4186 RAISE missing_key;
4187 end;
4188
4189 --=======================================================
4190 logString(eventLogLevel, procedureName, 'section', '4');
4191 --=======================================================
4192
4193 -- get the sign factors into a local array
4194 tmpSign.DELETE;
4195 liiSign.DELETE;
4196 Open getSigns(liaAttr, liaVers, ataAttr, ataVers);
4197 Fetch getSigns Bulk Collect
4198 Into tmpSign;
4199 If getSigns%ROWCOUNT = 0 Then
4200 logString(exceptionLogLevel, procedureName, 'exception', 'bad_sign');
4201 /*4.29.04 UNCOMMENT THIS!!!!
4202 Close getSigns;
4203 logString( exceptionLogLevel, procedureName, 'exception', 'bad_sign');
4204 FND_MESSAGE.set_name( 'GCS', 'GCS_BAD_SIGN' );
4205 FND_MESSAGE.set_token( 'LINE_ITEM_ID' , to_char( linesData.line_item_id(i) ) );
4206 RAISE bad_sign;
4207 */
4208 End If;
4209 Close getSigns;
4210
4211 --=======================================================
4212 logString(eventLogLevel, procedureName, 'section', '5');
4213 --=======================================================
4214
4215 -- Index the array by line_item_id
4216 i := tmpSign.FIRST;
4217 While i IS NOT NULL Loop
4218 liiSign(tmpSign(i).lineItem) := tmpSign(i);
4219 i := tmpSign.NEXT(i);
4220 End Loop;
4221
4222 --=======================================================
4223 logString(eventLogLevel, procedureName, 'section', '6');
4224 --=======================================================
4225
4226 -- Make sure we have a factor for each line_item_id
4227 i := linesData.line_item_id.FIRST;
4228 While i IS NOT NULL Loop
4229 begin
4230 --if a line_item_id in linesData was skipped somehow, then the read
4231 --of that index value in liiSign will throw a no_data_found
4232 If nvl(liiSign(linesData.line_item_id(i)).signFactor, 2) IN (1, -1) Then
4233 linesData.balance_factor(i) := liiSign(linesData.line_item_id(i))
4234 .signFactor;
4235 Else
4236 RAISE no_data_found;
4237 End If;
4238 i := linesData.line_item_id.NEXT(i);
4239 exception
4240 when no_data_found then
4241 logString(exceptionLogLevel,
4242 procedureName,
4243 'exception',
4244 'bad_sign');
4245 /*4.29.04 UNCOMMENT THIS!!!!
4246 FND_MESSAGE.set_name( 'GCS', 'GCS_BAD_SIGN' );
4247 FND_MESSAGE.set_token( 'LINE_ITEM_ID' , to_char( linesData.line_item_id(i) ) );
4248 RAISE bad_sign;
4249 */
4250 --4.29.04 REMOVE THE NEXT 2 LINES!!!!!
4251 linesData.balance_factor(i) := 1;
4252 i := linesData.line_item_id.NEXT(I);
4253
4254 end;
4255 End Loop; --While i IS NOT NULL
4256
4257 --=======================================================
4258 logString(eventLogLevel, procedureName, 'section', '7');
4259 --=======================================================
4260
4261 -- Can bypass most of the rest if this is a STAT entry
4262 if contextData.currencyCode = 'STAT' then
4263
4264 if linesData.cctr_org_id.COUNT <> 0 then
4265 entryId := writeEntryData(linesData, categoryInfo);
4266 -- changes made by yingliu
4267 if contextData.eventType <> 'A' AND
4268 categoryInfo.support_multi_parents_flag = 'Y' then
4269 process_multiparent(entryId);
4270 end if;
4271 -- end of changes by yingliu
4272 end if;
4273
4274 -- Make sure we end up with at least some non-zero lines!
4275 elsif linesData.cctr_org_id.COUNT = 0 then
4276 logString(statementLogLevel, procedureName, 'data', 'No entry lines');
4277 --Bug 3645309: Remove entry from gcs_cons_eng_run_dtls if no lines generated
4278 if contextData.eventType = 'C' then
4279 DELETE gcs_cons_eng_run_dtls
4280 WHERE run_detail_id = contextData.eventKey;
4281 END IF;
4282 -- bug 4115816
4283 -- RAISE no_entry_lines;
4284
4285 else
4286
4287 --=======================================================
4288 logString(eventLogLevel, procedureName, 'section', '8');
4289 --=======================================================
4290
4291 -- Write the entry lines
4292 entryId := writeEntryData(linesData, categoryInfo);
4293
4294 --=======================================================
4295 logString(eventLogLevel, procedureName, 'section', '9');
4296 --=======================================================
4297 --Look up the suspense data
4298 Open getSuspenseData; --(contextData.hierarchy);
4299 Fetch getSuspenseData
4300 Into suspenseData;
4301 Close getSuspenseData;
4302
4303 --=======================================================
4304 logString(eventLogLevel, procedureName, 'section', '10');
4305 --=======================================================
4306
4307 --Get the template record together
4308 templateRecord.FINANCIAL_ELEM_ID := suspenseData.financial_elem_id;
4309 templateRecord.PRODUCT_ID := suspenseData.product_id;
4310 templateRecord.NATURAL_ACCOUNT_ID := suspenseData.natural_account_id;
4311 templateRecord.CHANNEL_ID := suspenseData.channel_id;
4312 templateRecord.LINE_ITEM_ID := suspenseData.line_item_id;
4313 templateRecord.PROJECT_ID := suspenseData.project_id;
4314 templateRecord.CUSTOMER_ID := suspenseData.customer_id;
4315 templateRecord.TASK_ID := suspenseData.task_id;
4316 templateRecord.USER_DIM1_ID := suspenseData.user_dim1_id;
4317 templateRecord.USER_DIM2_ID := suspenseData.user_dim2_id;
4318 templateRecord.USER_DIM3_ID := suspenseData.user_dim3_id;
4319 templateRecord.USER_DIM4_ID := suspenseData.user_dim4_id;
4320 templateRecord.USER_DIM5_ID := suspenseData.user_dim5_id;
4321 templateRecord.USER_DIM6_ID := suspenseData.user_dim6_id;
4322 templateRecord.USER_DIM7_ID := suspenseData.user_dim7_id;
4323 templateRecord.USER_DIM8_ID := suspenseData.user_dim8_id;
4324 templateRecord.USER_DIM9_ID := suspenseData.user_dim9_id;
4325 templateRecord.USER_DIM10_ID := suspenseData.user_dim10_id;
4326 thresholdAmt := suspenseData.threshold_amount;
4327
4328 --jh 4.30.04: added log.
4329 logString(statementLogLevel,
4330 procedureName,
4331 'parameter',
4332 'FINANCIAL_ELEM_ID => ' ||
4333 templateRecord.FINANCIAL_ELEM_ID);
4334 logString(statementLogLevel,
4335 procedureName,
4336 'parameter',
4337 'PRODUCT_ID => ' || templateRecord.PRODUCT_ID);
4338 logString(statementLogLevel,
4339 procedureName,
4340 'parameter',
4341 'NATURAL_ACCOUNT_ID => ' ||
4342 templateRecord.NATURAL_ACCOUNT_ID);
4343 logString(statementLogLevel,
4344 procedureName,
4345 'parameter',
4346 'CHANNEL_ID => ' || templateRecord.CHANNEL_ID);
4347 logString(statementLogLevel,
4348 procedureName,
4349 'parameter',
4350 'LINE_ITEM_ID => ' || templateRecord.LINE_ITEM_ID);
4351 logString(statementLogLevel,
4352 procedureName,
4353 'parameter',
4354 'PROJECT_ID => ' || templateRecord.PROJECT_ID);
4355 logString(statementLogLevel,
4356 procedureName,
4357 'parameter',
4358 'CUSTOMER_ID => ' || templateRecord.CUSTOMER_ID);
4359 logString(statementLogLevel,
4360 procedureName,
4361 'parameter',
4362 'TASK_ID => ' || templateRecord.TASK_ID);
4363 logString(statementLogLevel,
4364 procedureName,
4365 'parameter',
4366 'USER_DIM1_ID => ' || templateRecord.USER_DIM1_ID);
4367 logString(statementLogLevel,
4368 procedureName,
4369 'parameter',
4370 'USER_DIM2_ID => ' || templateRecord.USER_DIM2_ID);
4371 logString(statementLogLevel,
4372 procedureName,
4373 'parameter',
4374 'USER_DIM3_ID => ' || templateRecord.USER_DIM3_ID);
4375 logString(statementLogLevel,
4376 procedureName,
4377 'parameter',
4378 'USER_DIM4_ID => ' || templateRecord.USER_DIM4_ID);
4379 logString(statementLogLevel,
4380 procedureName,
4381 'parameter',
4382 'USER_DIM5_ID => ' || templateRecord.USER_DIM5_ID);
4383 logString(statementLogLevel,
4384 procedureName,
4385 'parameter',
4386 'USER_DIM6_ID => ' || templateRecord.USER_DIM6_ID);
4387 logString(statementLogLevel,
4388 procedureName,
4389 'parameter',
4390 'USER_DIM17_ID => ' || templateRecord.USER_DIM7_ID);
4391 logString(statementLogLevel,
4392 procedureName,
4393 'parameter',
4394 'USER_DIM8_ID => ' || templateRecord.USER_DIM8_ID);
4395 logString(statementLogLevel,
4396 procedureName,
4397 'parameter',
4398 'USER_DIM9_ID => ' || templateRecord.USER_DIM9_ID);
4399 logString(statementLogLevel,
4400 procedureName,
4401 'parameter',
4402 'USER_DIM10_ID => ' || templateRecord.USER_DIM10_ID);
4403 logString(statementLogLevel,
4404 procedureName,
4405 'parameter',
4406 'threshold_amount => ' || to_char(thresholdAmt));
4407
4408 --=======================================================
4409 logString(eventLogLevel, procedureName, 'section', '11');
4410 --=======================================================
4411
4412 --Call the API to create suspense lines
4413 --followed by the one to handle Retained Earnings
4414 --JH 5.3.04: Added p_rel_id
4415 begin
4416
4417 GCS_TEMPLATES_DYNAMIC_PKG.balance(p_entry_id => entryId,
4418 p_template => templateRecord,
4419 p_bal_type_code => 'ACTUAL',
4420 p_hierarchy_id => contextData.hierarchy,
4421 p_entity_id => categoryInfo.entityId,
4422 p_threshold => thresholdAmt,
4423 p_threshold_currency_code => suspenseData.threshold_currency);
4424
4425 exception
4426
4427 when OTHERS then
4428 logString(exceptionLogLevel,
4429 procedureName,
4430 'exception',
4431 'templates_pkg_error');
4432 logString(exceptionLogLevel,
4433 procedureName,
4434 'exception',
4435 'procedure "balance" fail');
4436 logString(exceptionLogLevel, procedureName, 'exception', null);
4437 RAISE templates_pkg_error;
4438
4439 end;
4440
4441 begin
4442
4443 GCS_TEMPLATES_DYNAMIC_PKG.calculate_re(p_entry_id => entryId,
4444 p_hierarchy_id => contextData.hierarchy,
4445 p_bal_type_code => 'ACTUAL',
4446 p_entity_id => categoryInfo.entityId);
4447
4448 exception
4449
4450 when OTHERS then
4451 logString(exceptionLogLevel,
4452 procedureName,
4453 'exception',
4454 'templates_pkg_error');
4455 logString(exceptionLogLevel,
4456 procedureName,
4457 'exception',
4458 'procedure "calculate_re" fail');
4459 logString(exceptionLogLevel, procedureName, 'exception', null);
4460 RAISE templates_pkg_error;
4461
4462 end;
4463
4464 -- changes made by yingliu
4465 if contextData.eventType <> 'A' AND
4466 categoryInfo.support_multi_parents_flag = 'Y' then
4467 process_multiparent(entryId);
4468 end if;
4469
4470 -- Bugfix 6242317: Update the account type code
4471 if contextData.eventType = 'A' then
4472
4473 --Bugfix 6242317: Update Line Type Code
4474 UPDATE gcs_entry_lines gel
4475 SET line_type_code = (SELECT DECODE(gel.description,
4476 'RE_LINE',
4477 'CALCULATED',
4478 DECODE(feata.dim_attribute_varchar_member,
4479 'ASSET',
4480 'BALANCE_SHEET',
4481 'LIABILITY',
4482 'BALANCE_SHEET',
4483 'EQUITY',
4484 'BALANCE_SHEET',
4485 'PROFIT_LOSS'))
4486 FROM fem_ext_acct_types_attr feata,
4487 fem_ln_items_attr flia
4488 WHERE gel.line_item_id = flia.line_item_id
4489 AND flia.attribute_id =
4490 l_line_item_type_attr
4491 AND flia.version_id =
4492 l_line_item_type_version
4493 AND flia.dim_attribute_varchar_member =
4494 feata.ext_account_type_code
4495 AND feata.attribute_id = l_acct_type_attr
4496 AND feata.version_id =
4497 l_acct_type_version)
4498 WHERE gel.entry_id = entryId;
4499
4500 end if;
4501
4502 -- bug fix 3920448
4503 SELECT SUSPENSE_EXCEEDED_FLAG
4504 INTO l_suspense_exceeded_flag
4505 FROM gcs_entry_headers
4506 WHERE entry_id = entryId;
4507
4508 IF l_suspense_exceeded_flag = 'Y' THEN
4509 raise suspense_exceeded_warn;
4510 END IF;
4511
4512 -- end changes by yingliu
4513 end if; --if contextData.currencyCode = 'STAT'
4514
4515 -- Done
4516 logString(procedureLogLevel,
4517 procedureName,
4518 'end',
4519 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
4520
4521 end createEntry;
4522
4523 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4524 -- Write rows into the GCS_ENTRY_[HEADERS|LINES] tables.
4525 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4526 Function writeConsolidationEntryData(linesData IN lineRec,
4527 categoryInfo IN getCategory%ROWTYPE)
4528 Return NUMBER is
4529
4530 procedureName varchar(30);
4531
4532 cursor getEntryId is
4533 Select gcs_entry_headers_s.nextval From dual;
4534 entryId gcs_entry_headers.entry_id%TYPE := -1;
4535
4536 errbuf varchar2(100);
4537 retcode varchar2(100);
4538 procCode varchar2(50);
4539 l_row_count number;
4540 endCalPers number;
4541
4542 --Bugfix 4928211: Added offset flag to improve performance
4543 offsetFlag varchar2(1);
4544
4545 begin
4546 procedureName := 'WRITE_CONSOLIDATION_ENTRY_DATA';
4547 endCalPers := contextData.calPeriodId;
4548 procCode := 'SINGLE_RUN_FOR_PERIOD';
4549
4550 logString(procedureLogLevel,
4551 procedureName,
4552 'begin',
4553 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
4554
4555 -- Get an entry_id
4556 entryId := -1;
4557
4558 Open getEntryId;
4559 Fetch getEntryId
4560 Into entryId;
4561 Close getEntryId;
4562
4563 --Save the id so we can update the gcs_cons_eng_run_dtls table
4564 if ruleIteration = 1 then
4565 mainEntryId := entryId;
4566 elsif ruleIteration = 2 then
4567 statEntryId := entryId;
4568 end if;
4569
4570 --=======================================================
4571 --create the entry lines
4572 logString(eventLogLevel,
4573 procedureName,
4574 'section',
4575 'inserting into gcs_entry_lines');
4576 --=======================================================
4577
4578 logString(statementLogLevel,
4579 procedureName,
4580 'event',
4581 'Inserting into gcs_entry_lines');
4582
4583 select decode(count(1), 0, 'N', 'Y')
4584 into offsetFlag
4585 from gcs_rule_scope_dims grsd, gcs_elim_rule_steps_b grsb
4586 where grsd.column_name = 'LINE_ITEM_ID'
4587 and grsd.offset_member_id is not null
4588 and grsb.rule_step_id = grsd.rule_step_id
4589 and grsb.rule_id = ruleId;
4590
4591 logString(statementLogLevel,
4592 procedureName,
4593 'event',
4594 'Value of offset flag is: ' || offsetFlag);
4595
4596 gcs_rp_utility_pkg.create_entry_lines(p_entry_id => entryId,
4597 p_offset_flag => offsetFlag,
4598 p_row_count => l_row_count);
4599
4600 logString(statementLogLevel,
4601 procedureName,
4602 'event',
4603 'Completed insert into gcs_entry_lines ' || l_row_count);
4604
4605 --check if any lines where created
4606 if (l_row_count = 0) then
4607 DELETE gcs_cons_eng_run_dtls
4608 WHERE run_detail_id = contextData.eventKey;
4609 RAISE no_entry_lines;
4610 else
4611 --=======================================================
4612 --create the entry header
4613 logString(eventLogLevel, procedureName, 'section', '3');
4614 --=======================================================
4615
4616 logString(statementLogLevel,
4617 procedureName,
4618 'GCS_ENTRY_PKG.create_entry_header parameter',
4619 'p_entry_id=>' || to_char(entryId));
4620
4621 logString(statementLogLevel,
4622 procedureName,
4623 'GCS_ENTRY_PKG.create_entry_header parameter',
4624 'p_hierarchy_id=>' || to_char(contextData.hierarchy));
4625
4626 logString(statementLogLevel,
4627 procedureName,
4628 'GCS_ENTRY_PKG.create_entry_header parameter',
4629 'p_entity_id=>' || to_char(categoryInfo.entityId));
4630
4631 logString(statementLogLevel,
4632 procedureName,
4633 'GCS_ENTRY_PKG.create_entry_header parameter',
4634 'p_start_cal_period_id=>' ||
4635 to_char(contextData.calPeriodId));
4636
4637 logString(statementLogLevel,
4638 procedureName,
4639 'GCS_ENTRY_PKG.create_entry_header parameter',
4640 'p_end_cal_period_id=>' || to_char(endCalPers));
4641
4642 logString(statementLogLevel,
4643 procedureName,
4644 'GCS_ENTRY_PKG.create_entry_header parameter',
4645 'p_entry_type_code=>' || 'AUTOMATIC');
4646
4647 --Bugfix 5103251: Removed hard-coding of balance type code
4648 logString(statementLogLevel,
4649 procedureName,
4650 'GCS_ENTRY_PKG.create_entry_header parameter',
4651 'p_balance_type_code=>' || contextData.balanceTypeCode);
4652
4653 logString(statementLogLevel,
4654 procedureName,
4655 'GCS_ENTRY_PKG.create_entry_header parameter',
4656 'p_currency_code=>' || contextData.currencyCode);
4657
4658 logString(statementLogLevel,
4659 procedureName,
4660 'GCS_ENTRY_PKG.create_entry_header parameter',
4661 'p_process_code=>' || procCode);
4662
4663 logString(statementLogLevel,
4664 procedureName,
4665 'GCS_ENTRY_PKG.create_entry_header parameter',
4666 'p_category_code=>' || contextData.eventCategory);
4667
4668 logString(statementLogLevel,
4669 procedureName,
4670 'GCS_ENTRY_PKG.create_entry_header parameter',
4671 'p_xlate_flag=>' || 'N');
4672
4673 logString(statementLogLevel,
4674 procedureName,
4675 'GCS_ENTRY_PKG.create_entry_header parameter',
4676 'p_rule_id=>' || to_char(ruleId));
4677
4678 --Create new entry on if this is not an AD event.
4679 GCS_ENTRY_PKG.create_entry_header(X_ERRBUF => errbuf,
4680 X_RETCODE => retcode,
4681 P_ENTRY_ID => entryId,
4682 P_HIERARCHY_ID => contextData.hierarchy,
4683 P_ENTITY_ID => categoryInfo.entityId,
4684 P_START_CAL_PERIOD_ID => contextData.calPeriodId,
4685 P_END_CAL_PERIOD_ID => endCalPers,
4686 P_ENTRY_TYPE_CODE => 'AUTOMATIC',
4687 P_BALANCE_TYPE_CODE => contextData.balanceTypeCode,
4688 P_CURRENCY_CODE => contextData.currencyCode,
4689 P_PROCESS_CODE => procCode,
4690 P_CATEGORY_CODE => contextData.eventCategory,
4691 P_XLATE_FLAG => 'N',
4692 P_RULE_ID => ruleId);
4693
4694 if retcode = fnd_api.g_ret_sts_unexp_error then
4695 --Handler is in process_rule proc
4696 logString(exceptionLogLevel,
4697 procedureName,
4698 'exception',
4699 'GCS_ENTRY_PKG.create_entry_header: ' || errbuf);
4700 RAISE entry_header_error;
4701 end if;
4702
4703 end if; --check row_count = 0
4704
4705 -- Done
4706 logString(procedureLogLevel,
4707 procedureName,
4708 'end',
4709 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
4710 RETURN(entryId);
4711
4712 end writeConsolidationEntryData;
4713
4714 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4715 -- Write rows into the GCS_ENTRY_[HEADERS|LINES] tables for Consolidation Rules
4716 -- Bugfix 4928211: Added this code to improve performance of the rules processor
4717 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4718 procedure createConsolidationEntry is
4719
4720 procedureName varchar(30);
4721 l_suspense_exceeded_flag VARCHAR2(1);
4722
4723 entryId gcs_entry_headers.entry_id%TYPE := -1;
4724
4725 Cursor getConversionRate(amt number) Is
4726 select --+ INDEX_DESC( r, GL_DAILY_RATES_U1 )
4727 (conversion_rate * amt)
4728 from gl_daily_rates r
4729 where from_currency = suspenseData.threshold_currency
4730 and to_currency = contextData.currencyCode
4731 and conversion_date < SYSDATE
4732 and rownum < 2;
4733
4734 outOfbalance number := 0;
4735 thresholdAmt number := 0;
4736 cctrOrg number;
4737 interCompany number;
4738
4739 i binary_integer;
4740
4741 --This is a record of tables because of an issue with
4742 --using a table of records. As such there is no easy
4743 --way to DELETE it before re-using it and it is easier
4744 --to declare it locally, everytime the proc runs
4745 linesData lineRec;
4746
4747 --we need these values to look up the multiplier
4748 --that determines the SIGN of amounts we put in
4749 --the xtd_balance_e column(s)
4750 liaAttr number;
4751 liaVers number;
4752 ataAttr number;
4753 ataVers number;
4754
4755 --Used by the call to GCS_TEMPLATES_DYNAMIC_PKG
4756 templateRecord GCS_TEMPLATES_PKG.templateRecord;
4757
4758 -- See package global data declares above for cursor
4759 categoryInfo getCategory%ROWTYPE;
4760
4761 begin
4762 procedureName := 'CREATE_CONSOLIDATION_ENTRY';
4763
4764 logString(procedureLogLevel,
4765 procedureName,
4766 'begin',
4767 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
4768
4769 --++++++++++++++++++++++++++++++++++++++++++++++++++++
4770 --Some things we cannot do without:
4771 -- category info
4772 -- A valid currency and precision
4773 -- An entry with at least two non-zero-amount lines
4774 -- A balance_factor to use in populating [xtd|ytd]_balance_e
4775 -- An entry that either...
4776 -- a) balances (debits = credits), or
4777 -- b) can be balanced by a suspense line, or
4778 -- c) uses the STAT currency
4779 --
4780 -- Check for these things and stop if there's any problem
4781 --++++++++++++++++++++++++++++++++++++++++++++++++++++
4782
4783 --Get the category info
4784 Open getCategory;
4785 Fetch getCategory
4786 Into categoryInfo;
4787 If getCategory%NOTFOUND or categoryInfo.entityId = -1 Then
4788 Close getCategory;
4789 logString(exceptionLogLevel,
4790 procedureName,
4791 'eventCategory => ',
4792 contextData.eventCategory);
4793 logString(exceptionLogLevel,
4794 procedureName,
4795 'parentEntityId => ',
4796 contextData.parentEntity);
4797 logString(exceptionLogLevel,
4798 procedureName,
4799 'childEntityId => ',
4800 contextData.childEntity);
4801 logString(exceptionLogLevel,
4802 procedureName,
4803 'elimEntityId => ',
4804 contextData.elimsEntity);
4805 RAISE invalid_category_code;
4806 End If;
4807 Close getCategory;
4808
4809 --Get a currency precision
4810 ccyPrecision := -1;
4811 ccyMinAcctUnit := null;
4812 Open getCurrency;
4813 Fetch getCurrency
4814 Into ccyPrecision, ccyMinAcctUnit;
4815 Close getCurrency;
4816
4817 if nvl(ccyPrecision, -1) < 0 then
4818 logString(exceptionLogLevel,
4819 procedureName,
4820 'exception',
4821 'Missing rounding data for currency code ' ||
4822 contextData.currencyCode);
4823 RAISE missing_currency_data;
4824 end if;
4825
4826 logString(statementLogLevel,
4827 procedureName,
4828 'parameter',
4829 'Currency Precision = ' ||
4830 to_char(ccyPrecision));
4831
4832 logString(statementLogLevel,
4833 procedureName,
4834 'parameter',
4835 'Currency Minimum Accountable Unit = ' ||
4836 to_char(ccyMinAcctUnit));
4837
4838 --=======================================================
4839 logString(eventLogLevel, procedureName, 'section', '2');
4840 --=======================================================
4841
4842 -- Write the entry lines
4843 entryId := writeConsolidationEntryData(linesData, categoryInfo);
4844
4845 --=======================================================
4846 logString(eventLogLevel, procedureName, 'section', '9');
4847 --=======================================================
4848 --Look up the suspense data
4849 Open getSuspenseData; --(contextData.hierarchy);
4850 Fetch getSuspenseData
4851 Into suspenseData;
4852 Close getSuspenseData;
4853
4854 --=======================================================
4855 logString(eventLogLevel, procedureName, 'section', '10');
4856 --=======================================================
4857
4858 --Get the template record together
4859 templateRecord.FINANCIAL_ELEM_ID := suspenseData.financial_elem_id;
4860 templateRecord.PRODUCT_ID := suspenseData.product_id;
4861 templateRecord.NATURAL_ACCOUNT_ID := suspenseData.natural_account_id;
4862 templateRecord.CHANNEL_ID := suspenseData.channel_id;
4863 templateRecord.LINE_ITEM_ID := suspenseData.line_item_id;
4864 templateRecord.PROJECT_ID := suspenseData.project_id;
4865 templateRecord.CUSTOMER_ID := suspenseData.customer_id;
4866 templateRecord.TASK_ID := suspenseData.task_id;
4867 templateRecord.USER_DIM1_ID := suspenseData.user_dim1_id;
4868 templateRecord.USER_DIM2_ID := suspenseData.user_dim2_id;
4869 templateRecord.USER_DIM3_ID := suspenseData.user_dim3_id;
4870 templateRecord.USER_DIM4_ID := suspenseData.user_dim4_id;
4871 templateRecord.USER_DIM5_ID := suspenseData.user_dim5_id;
4872 templateRecord.USER_DIM6_ID := suspenseData.user_dim6_id;
4873 templateRecord.USER_DIM7_ID := suspenseData.user_dim7_id;
4874 templateRecord.USER_DIM8_ID := suspenseData.user_dim8_id;
4875 templateRecord.USER_DIM9_ID := suspenseData.user_dim9_id;
4876 templateRecord.USER_DIM10_ID := suspenseData.user_dim10_id;
4877 thresholdAmt := suspenseData.threshold_amount;
4878
4879 logString(statementLogLevel,
4880 procedureName,
4881 'parameter',
4882 'FINANCIAL_ELEM_ID => ' || templateRecord.FINANCIAL_ELEM_ID);
4883 logString(statementLogLevel,
4884 procedureName,
4885 'parameter',
4886 'PRODUCT_ID => ' || templateRecord.PRODUCT_ID);
4887 logString(statementLogLevel,
4888 procedureName,
4889 'parameter',
4890 'NATURAL_ACCOUNT_ID => ' || templateRecord.NATURAL_ACCOUNT_ID);
4891 logString(statementLogLevel,
4892 procedureName,
4893 'parameter',
4894 'CHANNEL_ID => ' || templateRecord.CHANNEL_ID);
4895 logString(statementLogLevel,
4896 procedureName,
4897 'parameter',
4898 'LINE_ITEM_ID => ' || templateRecord.LINE_ITEM_ID);
4899 logString(statementLogLevel,
4900 procedureName,
4901 'parameter',
4902 'PROJECT_ID => ' || templateRecord.PROJECT_ID);
4903 logString(statementLogLevel,
4904 procedureName,
4905 'parameter',
4906 'CUSTOMER_ID => ' || templateRecord.CUSTOMER_ID);
4907 logString(statementLogLevel,
4908 procedureName,
4909 'parameter',
4910 'TASK_ID => ' || templateRecord.TASK_ID);
4911 logString(statementLogLevel,
4912 procedureName,
4913 'parameter',
4914 'USER_DIM1_ID => ' || templateRecord.USER_DIM1_ID);
4915 logString(statementLogLevel,
4916 procedureName,
4917 'parameter',
4918 'USER_DIM2_ID => ' || templateRecord.USER_DIM2_ID);
4919 logString(statementLogLevel,
4920 procedureName,
4921 'parameter',
4922 'USER_DIM3_ID => ' || templateRecord.USER_DIM3_ID);
4923 logString(statementLogLevel,
4924 procedureName,
4925 'parameter',
4926 'USER_DIM4_ID => ' || templateRecord.USER_DIM4_ID);
4927 logString(statementLogLevel,
4928 procedureName,
4929 'parameter',
4930 'USER_DIM5_ID => ' || templateRecord.USER_DIM5_ID);
4931 logString(statementLogLevel,
4932 procedureName,
4933 'parameter',
4934 'USER_DIM6_ID => ' || templateRecord.USER_DIM6_ID);
4935 logString(statementLogLevel,
4936 procedureName,
4937 'parameter',
4938 'USER_DIM17_ID => ' || templateRecord.USER_DIM7_ID);
4939 logString(statementLogLevel,
4940 procedureName,
4941 'parameter',
4942 'USER_DIM8_ID => ' || templateRecord.USER_DIM8_ID);
4943 logString(statementLogLevel,
4944 procedureName,
4945 'parameter',
4946 'USER_DIM9_ID => ' || templateRecord.USER_DIM9_ID);
4947 logString(statementLogLevel,
4948 procedureName,
4949 'parameter',
4950 'USER_DIM10_ID => ' || templateRecord.USER_DIM10_ID);
4951 logString(statementLogLevel,
4952 procedureName,
4953 'parameter',
4954 'threshold_amount => ' || to_char(thresholdAmt));
4955
4956 --=======================================================
4957 logString(eventLogLevel, procedureName, 'section', '11');
4958 --=======================================================
4959
4960 --Call the API to create suspense lines
4961 --followed by the one to handle Retained Earnings
4962 --JH 5.3.04: Added p_rel_id
4963 begin
4964
4965 --Bugfix 5103251: Removed hard-coding of balance type code
4966 GCS_TEMPLATES_DYNAMIC_PKG.balance(p_entry_id => entryId,
4967 p_template => templateRecord,
4968 p_bal_type_code => contextData.balanceTypeCode,
4969 p_hierarchy_id => contextData.hierarchy,
4970 p_entity_id => categoryInfo.entityId,
4971 p_threshold => thresholdAmt,
4972 p_threshold_currency_code => suspenseData.threshold_currency);
4973
4974 exception
4975 when OTHERS then
4976 logString(exceptionLogLevel,
4977 procedureName,
4978 'exception',
4979 'templates_pkg_error');
4980 logString(exceptionLogLevel,
4981 procedureName,
4982 'exception',
4983 'procedure "balance" fail');
4984 logString(exceptionLogLevel, procedureName, 'exception', null);
4985 RAISE templates_pkg_error;
4986 end;
4987
4988 begin
4989
4990 --Bugfix 5103251: Removed hard-coding of balance type code
4991 GCS_TEMPLATES_DYNAMIC_PKG.calculate_re(p_entry_id => entryId,
4992 p_hierarchy_id => contextData.hierarchy,
4993 p_bal_type_code => contextData.balanceTypeCode,
4994 p_entity_id => categoryInfo.entityId);
4995
4996 exception
4997 when OTHERS then
4998 logString(exceptionLogLevel,
4999 procedureName,
5000 'exception',
5001 'templates_pkg_error');
5002 logString(exceptionLogLevel,
5003 procedureName,
5004 'exception',
5005 'procedure "calculate_re" fail');
5006 logString(exceptionLogLevel, procedureName, 'exception', null);
5007 RAISE templates_pkg_error;
5008
5009 end;
5010
5011 -- changes made by yingliu
5012 if contextData.eventType <> 'A' AND
5013 categoryInfo.support_multi_parents_flag = 'Y' then
5014 process_multiparent(entryId);
5015 end if;
5016
5017 -- bug fix 3920448
5018 SELECT SUSPENSE_EXCEEDED_FLAG
5019 INTO l_suspense_exceeded_flag
5020 FROM gcs_entry_headers
5021 WHERE entry_id = entryId;
5022
5023 IF l_suspense_exceeded_flag = 'Y' THEN
5024 raise suspense_exceeded_warn;
5025 END IF;
5026
5027 -- Done
5028 logString(procedureLogLevel,
5029 procedureName,
5030 'end',
5031 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5032
5033 exception
5034 when no_entry_lines then
5035 logString(eventLogLevel,
5036 procedureName,
5037 'Event',
5038 'Zero entry lines generated');
5039 end createConsolidationEntry;
5040
5041 --+========================================================================+
5042 -- PACKAGE PUBLIC Members
5043 --+========================================================================+
5044
5045 Function process_rule(p_rule_id IN NUMBER,
5046 p_stat_flag IN VARCHAR2,
5047 p_context IN contextRecord,
5048 p_rule_data IN ruleDataRecord) RETURN NUMBER IS
5049
5050 procedureName Varchar2(30);
5051 errMsg Varchar2(2000);
5052 l_return_value Number := 0;
5053 --Assume we run each rule once, but we may have
5054 --to run twice if the p_stat_flag = 'Y'
5055 runCount Number := 1;
5056
5057 --Bugfix 4925150: Do not execute rules processor if formula evaluates to zero for performance savings
5058 --Provided space for 250 characters in the parsed formula
5059 TYPE l_parsed_formula_type IS TABLE OF VARCHAR2(250);
5060 l_parsed_formula l_parsed_formula_type;
5061 l_parsed_result NUMBER;
5062 l_valid_formula BOOLEAN := FALSE;
5063
5064 Begin
5065 procedureName := 'PROCESS_RULE';
5066
5067 -- Make sure we have the current runtime log level
5068 -- THIS LINE OF CODE MUST BE THE FIRST EXECUTED!!
5069 runtimeLogLevel := FND_LOG.g_current_runtime_level;
5070
5071 logString(procedureLogLevel,
5072 procedureName,
5073 'begin',
5074 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5075 -- Report the incoming parameter data
5076 ruleId := p_rule_id;
5077 ruleData := p_rule_data;
5078 contextData := p_context;
5079 mainEntryId := null;
5080 statEntryId := null;
5081 logParameterValues;
5082 initRefTables;
5083
5084 --We may run a rule twice, once for monetary and once for STAT currency.
5085 --The p_stat_flag value decides this but avoid doing two go rounds if the
5086 --original currency is STAT and the flag is set to Y.
5087 if nvl(p_stat_flag, 'N') = 'Y' AND contextData.currencyCode <> 'STAT' then
5088 runCount := 2;
5089 end if;
5090
5091 --Bugfix 4928211: Select precision of currency into contextData
5092 select NVL(minimum_accountable_unit, POWER(10, -precision))
5093 into contextData.currPrecision
5094 from fnd_currencies
5095 where currency_code = contextData.currencyCode;
5096
5097 --Bugfix 4925150: Do not execute rules processor if formula evaluates to zero for performance savings
5098 if contextData.eventType = 'C' then
5099
5100 OPEN getEvaluatedFormulas(ruleData.toPercent, p_rule_id);
5101 FETCH getEvaluatedFormulas BULK COLLECT
5102 INTO l_parsed_formula;
5103 CLOSE getEvaluatedFormulas;
5104
5105 FOR i IN 1 .. l_parsed_formula.COUNT LOOP
5106
5107 EXECUTE IMMEDIATE 'SELECT ' || l_parsed_formula(i) || ' FROM DUAL'
5108 INTO l_parsed_result;
5109
5110 if (l_parsed_result <> 0) then
5111 l_valid_formula := TRUE;
5112 EXIT;
5113 end if;
5114
5115 END LOOP;
5116
5117 if (NOT l_valid_formula) then
5118 delete from gcs_cons_eng_run_dtls
5119 where run_detail_id = contextData.eventKey;
5120 end if;
5121
5122 else
5123 --For A Always execute the formula
5124 l_valid_formula := TRUE;
5125 end if;
5126
5127 if (l_valid_formula) then
5128
5129 --Bugfix 5456211: Initialize Ledger Id on context data for performance improvements
5130 SELECT fem_ledger_id
5131 INTO contextData.ledgerId
5132 FROM gcs_hierarchies_b
5133 WHERE hierarchy_id = contextData.hierarchy;
5134
5135 For x in 1 .. runCount Loop
5136
5137 --================================================================
5138 logString(eventLogLevel, procedureName, 'iteration', to_char(x));
5139 --================================================================
5140
5141 --Flag whether this is the STAT iteration, i.e., x = 2
5142 ruleIteration := x;
5143
5144 --Run the rule step-by-step, storing the full details of the process
5145 --into the GCS_ENTRIES_GT table for use in creating worksheet entries
5146 --and writing the execution report
5147 processRuleSteps;
5148
5149 --Use the very detailed output stored in the GCS_ENTRIES_GT table
5150 --to create worksheet entries summarized across dimensions, and
5151 --balance any unbalanced entries where applicable
5152
5153 BEGIN
5154 if (contextData.eventType = 'A') then
5155 createEntry;
5156 else
5157 createConsolidationEntry;
5158 end if;
5159 EXCEPTION
5160 WHEN suspense_exceeded_warn THEN
5161 l_return_value := 1;
5162 END;
5163
5164 --================================================================
5165 logString(eventLogLevel, procedureName, 'STAT', '');
5166 --================================================================
5167
5168 --Handle any STAT currency rows on loop iteration 2
5169 if ruleIteration = 2 then
5170 contextData.currencyCode := 'STAT';
5171 end if;
5172
5173 End Loop; --For x in 1..runCount
5174 end if;
5175 --================================================================
5176 logString(eventLogLevel,
5177 procedureName,
5178 'Update eventKey: eventType =>',
5179 contextData.eventType);
5180 --================================================================
5181
5182 --Update the eventKey for the entries
5183 if contextData.eventType = 'C' then
5184 UPDATE gcs_cons_eng_run_dtls
5185 SET entry_id = mainEntryId,
5186 stat_entry_id = statEntryId,
5187 -- SKAMDAR : Added updates for request_error_code, and bp_request_error_code
5188 request_error_code = DECODE(l_return_value,
5189 1,
5190 'WARNING',
5191 'COMPLETED'),
5192 bp_request_error_code = DECODE(l_return_value,
5193 1,
5194 'WARNING',
5195 'COMPLETED')
5196 WHERE run_detail_id = contextData.eventKey;
5197
5198 logString(statementLogLevel,
5199 procedureName,
5200 'stmt',
5201 to_char(SQL%ROWCOUNT) || ' row(s) updated');
5202
5203 /*
5204 elsif contextData.eventType = 'A' then
5205
5206 UPDATE gcs_ad_transactions
5207 SET assoc_entry_id = mainEntryId
5208 WHERE ad_transaction_id = contextData.eventKey;
5209
5210 logString( statementLogLevel, procedureName, 'stmt',
5211 to_char(SQL%ROWCOUNT) || ' row(s) updated' );
5212 */
5213 end if;
5214
5215 -- Done
5216 logString(procedureLogLevel,
5217 procedureName,
5218 'end',
5219 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5220 RETURN(l_return_value);
5221
5222 -- +++++++++++++++++++++++++++++++++++
5223 -- ERROR HANDLERS
5224 -- ++++++++++++++++++++++++++++++++++++
5225
5226 EXCEPTION
5227
5228 WHEN rule_has_no_steps THEN
5229 logString(exceptionLogLevel,
5230 procedureName,
5231 'exception',
5232 'rule has no steps');
5233 FND_MESSAGE.set_name('GCS', 'GCS_MISSING_RULE_STEPS');
5234 logString(procedureLogLevel,
5235 procedureName,
5236 'end',
5237 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5238 if contextData.eventType = 'C' then
5239 UPDATE gcs_cons_eng_run_dtls
5240 SET request_error_code = 'GCS_MISSING_RULE_STEPS',
5241 bp_request_error_code = 'GCS_MISSING_RULE_STEPS'
5242 WHERE run_detail_id = contextData.eventKey;
5243 end if;
5244 RETURN(2);
5245
5246 WHEN missing_currency_data THEN
5247 logString(exceptionLogLevel,
5248 procedureName,
5249 'exception',
5250 'missing_currency_data');
5251 FND_MESSAGE.set_name('GCS', 'GCS_MISSING_CURRENCY_DATA');
5252 logString(procedureLogLevel,
5253 procedureName,
5254 'end',
5255 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5256 if contextData.eventType = 'C' then
5257 UPDATE gcs_cons_eng_run_dtls
5258 SET request_error_code = 'GCS_MISSING_CURRENCY_DATA',
5259 bp_request_error_code = 'GCS_MISSING_CURRENCY_DATA'
5260 WHERE run_detail_id = contextData.eventKey;
5261 end if;
5262 RETURN(2);
5263
5264 WHEN invalid_dim_set_id THEN
5265 logString(exceptionLogLevel,
5266 procedureName,
5267 'exception',
5268 'invalid_dim_set_id');
5269 FND_MESSAGE.set_name('GCS', 'GCS_INVALID_DIM_SET_ID');
5270 logString(procedureLogLevel,
5271 procedureName,
5272 'end',
5273 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5274 if contextData.eventType = 'C' then
5275 UPDATE gcs_cons_eng_run_dtls
5276 SET request_error_code = 'GCS_INVALID_DIM_SET_ID',
5277 bp_request_error_code = 'GCS_INVALID_DIM_SET_ID'
5278 WHERE run_detail_id = contextData.eventKey;
5279 end if;
5280 RETURN(2);
5281
5282 WHEN invalid_variable THEN
5283 --An error msg is placed on the stack at the exception raise point
5284 --A logString call is made at the exception raise point
5285 logString(procedureLogLevel,
5286 procedureName,
5287 'end',
5288 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5289 if contextData.eventType = 'C' then
5290 UPDATE gcs_cons_eng_run_dtls
5291 SET request_error_code = 'GCS_INVALID_VARIABLE',
5292 bp_request_error_code = 'GCS_INVALID_VARIABLE'
5293 WHERE run_detail_id = contextData.eventKey;
5294 end if;
5295 RETURN(2);
5296
5297 WHEN invalid_fem_setup THEN
5298 logString(exceptionLogLevel,
5299 procedureName,
5300 'exception',
5301 'invalid_fem_setup');
5302 logString(procedureLogLevel,
5303 procedureName,
5304 'end',
5305 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5306 RETURN(2);
5307
5308 WHEN invalid_gcs_setup THEN
5309 logString(exceptionLogLevel,
5310 procedureName,
5311 'exception',
5312 'invalid_gcs_setup');
5313 logString(procedureLogLevel,
5314 procedureName,
5315 'end',
5316 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5317 RETURN(2);
5318
5319 WHEN out_of_balance THEN
5320 logString(exceptionLogLevel,
5321 procedureName,
5322 'exception',
5323 'out_of_balance');
5324 FND_MESSAGE.set_name('GCS', 'GCS_OUT_OF_BALANCE');
5325 logString(procedureLogLevel,
5326 procedureName,
5327 'end',
5328 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5329 RETURN(3);
5330
5331 WHEN templates_pkg_error THEN
5332 --raise by gcs_templates_dynamic_pkg...
5333 logString(procedureLogLevel,
5334 procedureName,
5335 'end',
5336 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5337 if contextData.eventType = 'C' then
5338 UPDATE gcs_cons_eng_run_dtls
5339 SET request_error_code = 'GCS_TEMPLATES_PKG_ERROR',
5340 bp_request_error_code = 'GCS_TEMPLATES_PKG_ERROR'
5341 WHERE run_detail_id = contextData.eventKey;
5342 end if;
5343 RETURN(2);
5344 /*
5345 WHEN no_entry_lines THEN
5346 logString( exceptionLogLevel, procedureName, 'exception', 'no_entry_lines' );
5347 FND_MESSAGE.set_name( 'GCS', 'GCS_NO_ENTRY_LINES' );
5348 logString( procedureLogLevel, procedureName, 'end', to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5349 RETURN (1);
5350 */
5351 WHEN missing_key THEN
5352 --An error msg is placed on the stack at the exception raise point
5353 --A logString call is made at the exception raise point
5354 logString(procedureLogLevel,
5355 procedureName,
5356 'end',
5357 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5358 RETURN(2);
5359
5360 WHEN bad_sign THEN
5361 --An error msg is placed on the stack at the exception raise point
5362 --A logString call is made at the exception raise point
5363 logString(procedureLogLevel,
5364 procedureName,
5365 'end',
5366 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5367 RETURN(2);
5368
5369 WHEN no_default_cctr_found THEN
5370 logString(exceptionLogLevel,
5371 procedureName,
5372 'exception',
5373 'no_default_cctr_found');
5374 FND_MESSAGE.set_name('GCS', 'GCS_NO_DEFAULT_CCTR');
5375 logString(procedureLogLevel,
5376 procedureName,
5377 'end',
5378 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5379 RETURN(2);
5380
5381 WHEN convert_threshold_err THEN
5382 logString(exceptionLogLevel,
5383 procedureName,
5384 'exception',
5385 'convert_threshold_err');
5386 FND_MESSAGE.set_name('GCS', 'GCS_CONVERT_THRESHOLD_ERR');
5387 logString(procedureLogLevel,
5388 procedureName,
5389 'end',
5390 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5391 if contextData.eventType = 'C' then
5392 UPDATE gcs_cons_eng_run_dtls
5393 SET request_error_code = 'GCS_CONVERT_THRESHOLD_ERR',
5394 bp_request_error_code = 'GCS_CONVERT_THRESHOLD_ERR'
5395 WHERE run_detail_id = contextData.eventKey;
5396 end if;
5397 RETURN(2);
5398
5399 WHEN entry_header_error THEN
5400 logString(exceptionLogLevel,
5401 procedureName,
5402 'exception',
5403 'entry_header_error');
5404 FND_MESSAGE.set_name('GCS', 'GCS_ENTRY_UNEXPECTED_ERR');
5405 logString(procedureLogLevel,
5406 procedureName,
5407 'end',
5408 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5409 if contextData.eventType = 'C' then
5410 UPDATE gcs_cons_eng_run_dtls
5411 SET request_error_code = 'GCS_ENTRY_HEADER_ERROR',
5412 bp_request_error_code = 'GCS_ENTRY_HEADER_ERROR'
5413 WHERE run_detail_id = contextData.eventKey;
5414 end if;
5415 RETURN(2);
5416
5417 WHEN invalid_category_code THEN
5418 logString(exceptionLogLevel,
5419 procedureName,
5420 'exception',
5421 'invalid_category_code');
5422 FND_MESSAGE.set_name('GCS', 'GCS_INVALID_CATEGORY_CODE');
5423 FND_MESSAGE.set_token('CATEGORY_CODE', contextData.eventCategory);
5424 logString(procedureLogLevel,
5425 procedureName,
5426 'end',
5427 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5428 RETURN(2);
5429
5430 WHEN OTHERS THEN
5431 errMsg := substr(SQLERRM, 1, 2000);
5432 logString(unexpectedLogLevel, procedureName, 'whenOthers', errMsg);
5433 FND_MESSAGE.set_name('GCS', 'GCS_UNHANDLED_EXCEPTION');
5434 FND_MESSAGE.set_token('PROCEDURE',
5435 packageName || '.' || procedureName);
5436 FND_MESSAGE.set_token('EVENT', 'OTHERS');
5437 if contextData.eventType = 'C' then
5438 UPDATE gcs_cons_eng_run_dtls
5439 SET request_error_code = 'GCS_UNHANDLED_EXCEPTION',
5440 bp_request_error_code = 'GCS_UNHANDLED_EXCEPTION'
5441 WHERE run_detail_id = contextData.eventKey;
5442 end if;
5443 logString(procedureLogLevel,
5444 procedureName,
5445 'end',
5446 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5447 RETURN(2);
5448
5449 End process_rule;
5450
5451 -- Overloaded member to handle multiple Rules in one API call
5452 Function process_rule(p_rules IN OUT NOCOPY ruleParmsTable,
5453 p_context IN contextRecord,
5454 p_rules_data IN ruleDataTable) RETURN NUMBER IS
5455
5456 procedureName varchar2(30);
5457
5458 i Number := 0;
5459 result number := 2;
5460 retVal number := 0; --NOTE: If p_rules is empty, return error
5461 errMsg Varchar2(2000);
5462
5463 Begin
5464 procedureName := 'PROCESS_RULE';
5465
5466 -- Make sure we have the current runtime log level
5467 -- THIS LINE OF CODE MUST BE THE FIRST EXECUTED!!
5468 runtimeLogLevel := FND_LOG.g_current_runtime_level;
5469
5470 logString(procedureLogLevel,
5471 procedureName,
5472 'begin',
5473 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5474
5475 --Loop through p_rules, calling the overloaded process_rule for each
5476 i := p_rules.FIRST;
5477 While i IS NOT NULL Loop
5478
5479 logString(statementLogLevel,
5480 procedureName,
5481 'parameter',
5482 'Processing Rule ID ' || to_char(i));
5483
5484 --Make sure we don't get a NO_DATA_FOUND error because of a
5485 --mis-match in the indexes between p_rules and p_rules_data
5486 Begin
5487 p_rules(i).result := process_rule(p_rule_id => p_rules(i).ruleId,
5488 p_stat_flag => p_rules(i).statFlag,
5489 p_context => p_context,
5490 p_rule_data => p_rules_data(i));
5491
5492 Exception
5493 When NO_DATA_FOUND Then
5494 RAISE missing_rule_id;
5495 End;
5496
5497 --Store the overall return value
5498 if result = 1 then
5499 retVal := 1;
5500 elsif result = 2 then
5501 retVal := 2;
5502 EXIT;
5503 end if;
5504
5505 i := p_rules.NEXT(i);
5506
5507 End Loop;
5508
5509 -- Done
5510 logString(procedureLogLevel,
5511 procedureName,
5512 'end',
5513 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5514 RETURN(retVal);
5515
5516 EXCEPTION
5517
5518 WHEN MISSING_RULE_ID THEN
5519 logString(exceptionLogLevel,
5520 procedureName,
5521 'exception',
5522 'missing_rule_id');
5523 FND_MESSAGE.set_name('GCS', 'GCS_MISSING_RULE_ID');
5524 FND_MESSAGE.set_token('PROCEDURE',
5525 packageName || '.' || procedureName);
5526 logString(procedureLogLevel,
5527 procedureName,
5528 'end',
5529 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5530 RETURN(2);
5531
5532 WHEN OTHERS THEN
5533 errMsg := substr(SQLERRM, 1, 2000);
5534 logString(unexpectedLogLevel, procedureName, 'whenOthers', errMsg);
5535 FND_MESSAGE.set_name('GCS', 'GCS_UNHANDLED_EXCEPTION');
5536 FND_MESSAGE.set_token('PROCEDURE',
5537 packageName || '.' || procedureName);
5538 FND_MESSAGE.set_token('EVENT', 'OTHERS');
5539 logString(procedureLogLevel,
5540 procedureName,
5541 'end',
5542 to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
5543 RETURN(2);
5544
5545 End process_rule;
5546
5547 -- Initialization of parameters
5548 BEGIN
5549 runtimeLogLevel := FND_LOG.g_current_runtime_level;
5550 systemDate := trunc(SYSDATE);
5551 userId := FND_GLOBAL.user_id;
5552 END GCS_RULES_PROCESSOR;