[Home] [Help]
PACKAGE BODY: APPS.GCS_AD_ENGINE
Source
1 package body GCS_AD_ENGINE as
2 -- $Header: gcsadenb.pls 120.2 2007/11/28 06:05:00 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 := FND_LOG.g_current_runtime_level;
12 packageName CONSTANT Varchar2(30) := 'GCS_AD_ENGINE';
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 -- Globally useful storage - make them global to the package so we
24 -- don't have to pass them from procedure to procedure as args.
25 requestId number(15); --concurrent request id
26 currencyCode varchar2(15); --currency for audit journals created here
27 timeStamp Date := SYSDATE; --all journals get this datetime stamp
28 xnsId number(15); --p_transaction_id from process_transaction
29 dummy number; --used for numeric throw away return values
30
31 --ruleAtts:
32 --attributes used to locate the rule_id(s) we will process
33 TYPE ruleAttsRecord IS RECORD (
34 oldTreatment number(15),
35 newTreatment number(15),
36 xnsTypeCode varchar2(30)
37 );
38 TYPE ruleAttsTable is TABLE of ruleAttsRecord INDEX BY binary_integer;
39 ruleAtts ruleAttsTable;
40
41 --ruleData:
42 --local storage for RP parameter
43 TYPE ruleDataTable IS
44 TABLE OF GCS_RULES_PROCESSOR.ruleDataRecord INDEX BY BINARY_INTEGER;
45 ruleData ruleDataTable;
46
47 --contextData:
48 --local storage for RP parameter
49 TYPE contextDataTable IS
50 TABLE OF GCS_RULES_PROCESSOR.contextRecord INDEX BY BINARY_INTEGER;
51 contextData contextDataTable;
52
53 -- Used in getting the netAssetValues
54 TYPE totalRecord IS RECORD (dr number, cr number);
55 total totalRecord;
56
57 --Exception handlers: everything that can go wrong here
58 invalid_arguments EXCEPTION;
59 invalid_xns_type EXCEPTION;
60 no_rule_found EXCEPTION;
61 rule_processor_failure EXCEPTION;
62 missing_key EXCEPTION; -- a utility pkg hash key is missing
63
64 ------------------------------------------
65 -- Shared cursor definitions
66 ------------------------------------------
67
68 --xnsCursor:
69 --Select the transaction control record(s)
70 cursor xnsCursor is
71 SELECT *
72 FROM GCS_AD_TRANSACTIONS
73 WHERE ad_transaction_id = xnsId;
74
75
76 --linesCursor:
77 --Select a trial balance
78 --Sort the cursor by trial balance line number
79 cursor linesCursor (tbType varchar2) is
80 SELECT *
81 FROM GCS_AD_TRIAL_BALANCES
82 WHERE ad_transaction_id = xnsId
83 AND trial_balance_seq = tbType;
84
85 Type xnsLinesTable is Table of linesCursor%ROWTYPE Index By binary_integer;
86 xnsLines xnsLinesTable;
87
88
89 --ruleCursor:
90 --Find the correct elimination rule id(s)
91 --5.17.04: Add condition: enabled_flag='Y'
92 cursor ruleCursor (i number) is
93 SELECT r.rule_id, r.rule_name
94 FROM GCS_ELIM_RULES_VL r
95 WHERE r.transaction_type_code = ruleAtts(i).xnsTypeCode
96 AND r.from_treatment_id = ruleAtts(i).oldTreatment
97 AND r.to_treatment_id = ruleAtts(i).newTreatment
98 AND r.enabled_flag ='Y';
99
100 TYPE ruleTable is TABLE of ruleCursor%ROWTYPE index by binary_integer;
101 xnsRules ruleTable;
102
103
104 --hierRelCursor:
105 --Find the percentages, treatments, etc
106 cursor hierRelCursor ( relId number) is
107 SELECT t.hierarchy_name,
108 d.dataset_code,
109 r.hierarchy_id,
110 r.treatment_id,
111 r.ownership_percent,
112 r.parent_entity_id parentEntity,
113 r.child_entity_id childEntity,
114 -1 elimsEntity
115 FROM GCS_HIERARCHIES_TL t,
116 GCS_DATASET_CODES d,
117 GCS_CONS_RELATIONSHIPS r
118 WHERE t.language = userenv('LANG')
119 AND t.hierarchy_id = r.hierarchy_id
120 AND d.hierarchy_id = r.hierarchy_id
121 AND r.cons_relationship_id = relId
122 AND r.actual_ownership_flag='Y';
123
124
125 --netAssetValueCursor
126 --Find the net asset value
127 cursor navCursor(ataAtt NUMBER, ataVers NUMBER, liaAtt NUMBER, liaVers NUMBER) Is
128 Select b.trial_balance_seq, sum( nvl(b.debit_amount,0) - nvl(b.credit_amount,0) )
129 from fem_ln_items_attr lia,
130 fem_ext_acct_types_attr ata,
131 gcs_ad_trial_balances b
132 where lia.dim_attribute_varchar_member in ('ASSET', 'LIABILITY')
133 and ata.attribute_id = ataAtt
134 and ata.version_id = ataVers
135 and ata.ext_account_type_code = lia.dim_attribute_varchar_member
136 and lia.attribute_id = liaAtt
137 and lia.version_id = liaVers
138 and lia.line_item_id = b.line_item_id
139 and b.ad_transaction_id = xnsId
140 group by b.trial_balance_seq;
141
142 --+========================================================================+
143 -- PACKAGE Private Members
144 --+========================================================================+
145
146 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
147 -- Call this for messages you want to see only when debugging the package
148 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
149 procedure writeToLog (buf IN Varchar2 := NULL) is
150 errBuf Varchar2(5000) := substr(buf, 1, 5000);
151 begin
152
153 -- Do nothing if there is no message waiting
154 If errBuf IS NOT NULL Then
155 DBMS_OUTPUT.new_line;
156 While errBuf is not null Loop
157 DBMS_OUTPUT.put_line( substr( errBuf, 1, 250 ) );
158 errBuf := substr( errBuf, 251 );
159 End Loop;
160 DBMS_OUTPUT.put_line('.');
161 DBMS_OUTPUT.new_line;
162 End If;
163
164 end writeToLog;
165
166
167 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
168 -- This makes embedding logging calls in the other code less intrusive
169 -- and keeps the code more legible.
170 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
171 procedure logString (
172 logLevel Number,
173 logProc Varchar2,
174 logLabel Varchar2,
175 logText Varchar2
176 ) is
177
178 rootString varchar2(100) := 'gcs.plsql.GCS_AD_ENGINE.';
179
180 errBuf Varchar2(5000);
181
182 begin
183
184 -- May be a message on the stack or
185 -- a string passed in via the arg
186 if logText IS NULL then
187 errBuf := substr( FND_MESSAGE.get, 1, 5000 );
188 else
189 errBuf := substr( logText, 1, 5000 );
190 end if;
191
192 /* if runtimeLogLevel = -1 then
193 writeToLog( rootString ||
194 logProc || '.' ||
195 logLabel || ': ' ||
196 errBuf );
197 end if;
198 */
199 if logLevel >= runtimeLogLevel then
200 FND_LOG.string(
201 logLevel,
202 rootString || logProc || '.' || logLabel,
203 errBuf);
204 end if;
205
206 end logString;
207
208
209 --+========================================================================+
210 -- PACKAGE PUBLIC Members
211 --+========================================================================+
212
213 PROCEDURE process_transaction (
214 errbuf IN OUT NOCOPY VARCHAR2,
215 retcode IN OUT NOCOPY NUMBER,
216 p_transaction_id IN NUMBER
217 ) is
218
219 procedureName Varchar2(30) := 'PROCESS_TRANSACTION';
220
221 i number; --throw away register
222
223 newRelation boolean := FALSE;
224 endRelation boolean := FALSE;
225
226 fromRelData hierRelCursor%ROWTYPE;
227 toRelData hierRelCursor%ROWTYPE;
228
229 parentEntity number;
230 childEntity number;
231 elimsEntity number;
232 hierarchyId number;
233 dataSet number;
234 relationshipId number;
235
236 xnsData xnsCursor%ROWTYPE;
237 ccy GCS_ENTITY_CONS_ATTRS.currency_code%TYPE;
238
239 ruleErrorMsg varchar2(2000);
240 ruleRetcode number;
241
242 --variables used to to look up and store the NAV
243 liaAttr number;
244 ataAttr number;
245 liaVers number;
246 ataVers number;
247 liaKey varchar2(100) := 'LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE';
248 ataKey varchar2(100) := 'EXT_ACCOUNT_TYPE_CODE-SIGN';
249 navTB1 number;
250 navTB2 number;
251 tbSeq number;
252 navTmp number;
253
254
255 --Bugfix 4226223 : Used to populate the line type code on gcs_ad_transactionx 4226223 : Populate the line_type_code for A&D Entries
256 l_line_item_vs_id NUMBER :=
257 gcs_utility_pkg.g_gcs_dimension_info ('LINE_ITEM_ID').associated_value_set_id;
258 l_ext_acct_type_attr NUMBER :=
259 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').attribute_id;
260 l_ext_acct_type_version NUMBER :=
261 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').version_id;
262 l_basic_acct_type_attr NUMBER :=
263 gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').attribute_id;
264 l_basic_acct_type_version NUMBER :=
265 gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').version_id;
266 l_entry_id NUMBER(15);
267
268 BEGIN
269
270 logString( procedureLogLevel, procedureName, 'begin', to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
271 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, packageName || '.' || procedureName || ' ENTER');
272 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
273
274 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
275 -- Note that data integrity is assumed based on the dedicated UI
276 -- We will enable logging to allow for runtime data integrity
277 -- auditing when an issue is suspected, but do not need to check
278 -- for data integrity per se.
279 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
280
281 requestId := fnd_global.conc_request_id;
282 xnsId := p_transaction_id;
283 retcode := 0;
284 -- logString( eventLogLevel, procedureName, 'jh: xnsId = ', xnsId);
285 --=======================================================
286 logString( eventLogLevel, procedureName, 'section', '2');
287 --=======================================================
288
289 -------------------------------------
290 -- Verify user access
291 -------------------------------------
292 --What to do here?
293
294 --=======================================================
295 logString( eventLogLevel, procedureName, 'section', '3');
296 --=======================================================
297
298 -------------------------------------
299 --Find the xnsData record
300 -------------------------------------
301 Open xnsCursor;
302 Fetch xnsCursor Into xnsData;
303 Close xnsCursor;
304
305 -- Place the request_id onto the header row and commit
306 Update gcs_ad_transactions
307 Set request_id = requestId
308 Where ad_transaction_id = xnsId;
309 If SQL%ROWCOUNT = 1 Then COMMIT;
310 Else RAISE invalid_arguments;
311 End If;
312
313 --If this is a rerun, nuke the old stuff
314 /* Removed 4/26/04. Per discussion 4/22/04: The AD UI will handle deleting
315 entry lines if consolidation has not been run for the period
316
317 if xnsData.assoc_entry_id IS NOT NULL then
318
319 UPDATE gcs_entry_headers
320 SET DISABLED_FLAG = 'Y'
321 WHERE entry_id = xnsData.assoc_entry_id;
322
323 DELETE FROM gcs_entry_lines
324 WHERE entry_id = xnsData.assoc_entry_id;
325
326 end if;
327 */
328 -- if xnsRerun then
329 -- GCS_BALANCES_PROCESSOR.undoEntries( xnsId );
330 -- end if;
331
332 --=======================================================
333 logString( eventLogLevel, procedureName, 'section', '4');
334 --=======================================================
335
336 --================================================
337 -- Obtain net asset value (NAV)
338 --================================================
342 liaKey varchar2(100) := 'LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE';
339
340 --Get the attribute_id and version_id for the EXTENDED_ACCOUNT_TYPE
341 declare
343 begin
344 liaAttr := GCS_UTILITY_PKG.g_dimension_attr_info(liaKey).attribute_id;
345 liaVers := GCS_UTILITY_PKG.g_dimension_attr_info(liaKey).version_id;
346 logString( statementLogLevel, procedureName, 'liaAttr ', liaAttr);
347 logString( statementLogLevel, procedureName, 'liaVers ', liaVers);
348 exception
349 when no_data_found then
350 logString( exceptionLogLevel, procedureName, 'exception', 'missing_key');
351 FND_MESSAGE.set_name( 'GCS', 'GCS_MISSING_KEY' );
352 FND_MESSAGE.set_token( 'HASH_KEY' , liaKey );
353 RAISE missing_key;
354 end;
355
356 --Get the attribute_id and version_id for the EXT_ACCOUNT_TYPE_CODE
357 declare
358 ataKey varchar2(100) := 'EXT_ACCOUNT_TYPE_CODE-SIGN';
359 begin
360 ataAttr := GCS_UTILITY_PKG.g_dimension_attr_info(ataKey).attribute_id;
361 ataVers := GCS_UTILITY_PKG.g_dimension_attr_info(ataKey).version_id;
362 logString( statementLogLevel, procedureName, 'ataAttr ', ataAttr);
363 logString( statementLogLevel, procedureName, 'ataVers ', ataVers);
364 exception
365 when no_data_found then
366 logString( exceptionLogLevel, procedureName, 'exception', 'missing_key');
367 FND_MESSAGE.set_name( 'GCS', 'GCS_MISSING_KEY' );
368 FND_MESSAGE.set_token( 'HASH_KEY' , ataKey );
369 RAISE missing_key;
370 end;
371
372 --Get the NAV
373 Open navCursor(ataAttr, ataVers, liaAttr, liaVers);
374 loop
375 Fetch navCursor Into tbSeq, navTmp;
376 Exit when navCursor%NOTFOUND;
377 if tbSeq = 1 then
378 navTB1 := navTmp;
379 elsif tbSeq =2 then
380 navTB2 := navTmp;
381 end if;
382 end loop;
383 Close navCursor;
384
385 --================================================
386 -- Collect ruleData, contextData
387 --================================================
388
389 ruleData.DELETE;
390 ruleAtts.DELETE;
391
392 if xnsData.pre_cons_relationship_id is NULL then
393 newRelation := TRUE;
394 else
395 Open hierRelCursor( xnsData.pre_cons_relationship_id );
396 Fetch hierRelCursor Into fromRelData;
397 Close hierRelCursor;
398 end if;
399
400 if xnsData.post_cons_relationship_id is NULL then
401 endRelation := TRUE;
402 else
403 Open hierRelCursor( xnsData.post_cons_relationship_id );
404 Fetch hierRelCursor Into toRelData;
405 Close hierRelCursor;
406 end if;
407
408 If newRelation then
409
410 --The first-time ever AD event would have to be an ACQ transaction.
411 --The pre_cons_relationship_id in this case is NULL and the previous
412 --treatment and ownership percentage are stored in the intermediate_
413 --columns of the transaction table. Only the toRelData record has data.
414
415 if xnsData.transaction_type_code <> 'ACQ' then
416 RAISE invalid_xns_type;
417 end if;
418 -- There will only be one rule to run
419 ruleData(1).fromPercent := 0;
420 ruleData(1).toPercent := toRelData.ownership_percent/100;
421 ruleData(1).consideration := xnsData.total_consideration;
422 ruleData(1).netAssetValue := navTB1;--xnsData.net_asset_value;
423
424
425 ruleAtts(1).oldTreatment := xnsData.intermediate_treatment_id;
426 ruleAtts(1).newTreatment := toRelData.treatment_id;
427 ruleAtts(1).xnsTypeCode := xnsData.transaction_type_code;
428
429 --Use the toRelData record to identify the entities
430 parentEntity := toRelData.parentEntity;
431 childEntity := toRelData.childEntity;
432 hierarchyId := toRelData.hierarchy_id;
433 dataSet := toRelData.dataset_code;
434 relationshipId := xnsData.post_cons_relationship_id;
435
436 Elsif endRelation then
437
438 -- A disposal resulting in zero ownership MUST be a DIS transaction.
439 -- The post_cons_relationship_id in this case is NULL and the final
440 -- treatment and percentage are in the intermediate_ columns in the
441 -- transaction table.
442
443 if xnsData.transaction_type_code <> 'DIS' then
444 RAISE invalid_xns_type;
445 end if;
446 -- There will only be one rule to run
447 ruleData(1).fromPercent := fromRelData.ownership_percent/100;
448 ruleData(1).toPercent := 0;
449 ruleData(1).consideration := xnsData.total_consideration;
450 ruleData(1).netAssetValue := navTB1;--xnsData.net_asset_value;
451
452 ruleAtts(1).oldTreatment := fromRelData.treatment_id;
453 ruleAtts(1).newTreatment := xnsData.intermediate_treatment_id;
454 ruleAtts(1).xnsTypeCode := xnsData.transaction_type_code;
455
456 --Use the fromRelData record to identify the entities
457 parentEntity := fromRelData.parentEntity;
458 childEntity := fromRelData.childEntity;
459 hierarchyId := fromRelData.hierarchy_id;
460 dataSet := fromRelData.dataset_code;
461 relationshipId := xnsData.pre_cons_relationship_id;
462
463
467 -- - AQ+ inherits the "to" data via the hierarchy
464 Elsif xnsData.transaction_type_code = 'PO+' Then
465 -- PO+ is ALWAYS followed by an implied AQ+
466 -- - PO- inherits the "from" data via the hierarchy
468 -- Intermediate data is on the xnsData record
469
470
471 --Set up data and atts for the first (PO+) rule
472 ruleData(1).fromPercent := fromRelData.ownership_percent/100;
473 ruleData(1).toPercent := xnsData.intermediate_percent_owned/100;
474 ruleData(1).consideration := 0;
475 ruleData(1).netAssetValue := navTB1;--xnsData.net_asset_value;
476
477 ruleAtts(1).oldTreatment := fromRelData.treatment_id;
478 ruleAtts(1).newTreatment := xnsData.intermediate_treatment_id;
479 -- 09.01.04: jh:replace use of PO+
480 ruleAtts(1).xnsTypeCode := 'PO-';
481
482 --Set up data and atts for the second (AQ+) rule
483 ruleData(2).fromPercent := ruleData(1).toPercent/100;
484 ruleData(2).toPercent := toRelData.ownership_percent/100;
485 ruleData(2).consideration := xnsData.total_consideration;
486 ruleData(2).netAssetValue := navTB2;--xnsData.net_asset_value;
487
488 ruleAtts(2).oldTreatment := ruleAtts(1).newTreatment;
489 ruleAtts(2).newTreatment := toRelData.treatment_id;
490 ruleAtts(2).xnsTypeCode := 'AQ+';
491
492 --Use the toRelData record to identify the entities
493 parentEntity := toRelData.parentEntity;
494 childEntity := toRelData.childEntity;
495 hierarchyId := toRelData.hierarchy_id;
496 dataSet := toRelData.dataset_code;
497 relationshipId := xnsData.post_cons_relationship_id;
498
499 Else
500 -- There will only be one rule to run
501 ruleData(1).fromPercent := fromRelData.ownership_percent/100;
502 ruleData(1).toPercent := toRelData.ownership_percent/100;
503 ruleData(1).consideration := xnsData.total_consideration;
504 ruleData(1).netAssetValue := navTB1;--xnsData.net_asset_value;
505
506 ruleAtts(1).oldTreatment := fromRelData.treatment_id;
507 ruleAtts(1).newTreatment := toRelData.treatment_id;
508 ruleAtts(1).xnsTypeCode := xnsData.transaction_type_code;
509
510 --Use the toRelData record to identify the entities
511 parentEntity := toRelData.parentEntity;
512 childEntity := toRelData.childEntity;
513 hierarchyId := toRelData.hierarchy_id;
514 dataSet := toRelData.dataset_code;
515 relationshipId := xnsData.post_cons_relationship_id;
516 End If;
517
518 logString( statementLogLevel, procedureName, 'parameter',
519 'fromPercent => ' || ruleData(1).fromPercent );
520 logString( statementLogLevel, procedureName, 'parameter',
521 'toPercent => ' || ruleData(1).toPercent );
522 logString( statementLogLevel, procedureName, 'parameter',
523 'consideration => ' || ruleData(1).consideration );
524 logString( statementLogLevel, procedureName, 'parameter',
525 'netAssetValue => ' || ruleData(1).netAssetValue );
526 logString( statementLogLevel, procedureName, 'parameter',
527 'oldTreatment => ' || ruleAtts(1).oldTreatment );
528 logString( statementLogLevel, procedureName, 'parameter',
529 'newTreatment => ' || ruleAtts(1).newTreatment );
530 logString( statementLogLevel, procedureName, 'parameter',
531 'xnsTypeCode => ' || ruleAtts(1).xnsTypeCode );
532 logString( statementLogLevel, procedureName, 'parameter',
533 'parentEntity => ' || parentEntity );
534 logString( statementLogLevel, procedureName, 'parameter',
535 'childEntity => ' || childEntity );
536 logString( statementLogLevel, procedureName, 'parameter',
537 'hierarchyId => ' || hierarchyId );
538 logString( statementLogLevel, procedureName, 'parameter',
539 'dataset => ' || dataset );
540 logString( statementLogLevel, procedureName, 'parameter',
541 'relationshipId => ' || relationshipId );
542
543 --=======================================================
544 logString( eventLogLevel, procedureName, 'section', '5');
545 --=======================================================
546
547 --Find elimination rule(s)
548 xnsRules.DELETE;
549 For x IN ruleAtts.FIRST..ruleAtts.LAST Loop
550 Open ruleCursor( x );
551 Fetch ruleCursor Into xnsRules(x);
552 if ruleCursor%NOTFOUND then
553 Close ruleCursor;
554 RAISE no_rule_found;
555 end if;
556 Close ruleCursor;
557 End Loop;
558
559 --=======================================================
560 logString( eventLogLevel, procedureName, 'section', '6');
561 --=======================================================
562
563 -- Get the elimsEntity.
564
565 declare
566 key varchar2(100) := 'ENTITY_ID-ELIMINATION_ENTITY';
567 attr number;
568 vers number;
569
570 --jh 5.18.04: elims entity_id is stored in dim_attribute_numeric_member,
571 --not entity_id
572 cursor elimsEntityCursor is
573 SELECT f.dim_attribute_numeric_member, g.currency_code
574 FROM GCS_ENTITY_CONS_ATTRS g,
575 FEM_ENTITIES_ATTR f
576 WHERE f.attribute_id = attr
580 AND g.entity_id = parentEntity;
577 AND f.version_id = vers
578 AND f.entity_id = g.entity_id
579 AND g.hierarchy_id = hierarchyId
581 begin
582
583 --Get attribute_id and version_id (throws no_data_found if data error)
584 attr := GCS_UTILITY_PKG.g_dimension_attr_info(key).attribute_id;
585 vers := GCS_UTILITY_PKG.g_dimension_attr_info(key).version_id;
586 logString( statementLogLevel, procedureName, 'parameter',
587 'attribute_id => ' ||attr);
588 logString( statementLogLevel, procedureName, 'parameter',
589 'version_id => ' || vers);
590 --Get the elims entity and currency
591 Open elimsEntityCursor;
592 Fetch elimsEntityCursor Into elimsEntity, ccy;
593 Close elimsEntityCursor;
594 logString( statementLogLevel, procedureName, 'parameter',
595 'elimsEntity => ' || elimsEntity );
596 logString( statementLogLevel, procedureName, 'parameter',
597 'ccy => ' || ccy );
598 exception
599 when no_data_found then
600 logString( exceptionLogLevel, procedureName, 'exception', 'missing_key');
601 FND_MESSAGE.set_name( 'GCS', 'GCS_MISSING_KEY' );
602 FND_MESSAGE.set_token( 'HASH_KEY' , key );
603
604 RAISE missing_key;
605 end;
606
607 --=======================================================
608 logString( eventLogLevel, procedureName, 'section', '7');
609 --=======================================================
610 --jh 5.14.04: add eventCategory, 'ACQ_DISP'
611 -- Set up the contextData, suspenseDims
612 contextData.DELETE;
613 For x In ruleData.FIRST..ruleData.LAST Loop
614 contextdata(x).eventType := 'A';
615 contextData(x).eventKey := xnsId;
616 contextData(x).parentEntity := parentEntity;
617 contextData(x).childEntity := childEntity;
618 contextData(x).elimsEntity := elimsEntity;
619 contextData(x).datasetCode := dataSet;
620 contextData(x).hierarchy := hierarchyId;
621 contextData(x).calPeriodId := xnsData.cal_period_id;
622 contextData(x).currencyCode := ccy;
623 contextData(x).relationship := relationshipId;
624 contextData(x).eventCategory := 'ACQ_DISP';
625
626 End Loop;
627
628 --=======================================================
629 logString( eventLogLevel, procedureName, 'section', '8');
630 --=======================================================
631
632 -------------------------------------
633 -- Process rule steps
634 -------------------------------------
635 For x in ruleData.FIRST..ruleData.LAST Loop
636
637 ruleRetcode := GCS_RULES_PROCESSOR.process_rule (
638 p_rule_id => xnsRules(x).rule_id,
639 p_stat_flag =>'N',
640 p_context => contextData(x),
641 p_rule_data => ruleData(x));
642
643 --Bugfix 4226223 : Populate the line_type_code on gcs_entry_lines
644 BEGIN
645 SELECT assoc_entry_id
646 INTO l_entry_id
647 FROM gcs_ad_transactions
648 WHERE ad_transaction_id = p_transaction_id;
649
650 -- Bugfix 6469074: Remove call to update the line_type_code
651 /*
652 IF (l_entry_id IS NOT NULL) THEN
653 UPDATE gcs_entry_lines gel
654 SET line_type_code = DECODE(
655 (SELECT feata.dim_attribute_varchar_member
656 FROM fem_ext_acct_types_attr feata,
657 fem_ln_items_attr flia
658 WHERE gel.line_item_id = flia.line_item_id
659 AND flia.value_set_id = l_line_item_vs_id
660 AND flia.attribute_id = l_ext_acct_type_attr
661 AND flia.version_id = l_ext_acct_type_version
662 AND feata.attribute_id = l_basic_acct_type_attr
663 AND feata.version_id = l_basic_acct_type_version
664 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
665 ), 'REVENUE', 'PROFIT_LOSS',
666 'EXPENSE', 'PROFIT_LOSS',
667 'BALANCE_SHEET'
668 )
669 WHERE entry_id = l_entry_id
670 AND line_type_code IS NOT NULL;
671 END IF;
672 */
673 EXCEPTION
674 WHEN OTHERS THEN
675 NULL;
676 END;
677
678 If ruleRetcode > 0 then
679
680 ruleErrorMsg := FND_MESSAGE.get;
681 FND_MESSAGE.set_name( 'GCS', 'GCS_RULE_FAILURE' );
682 FND_MESSAGE.set_token( 'PROCEDURE', packageName||'.'||procedureName );
683 FND_MESSAGE.set_token( 'RULE_NAME', xnsRules(x).rule_name );
684 logString( exceptionLogLevel, procedureName, 'exception', FND_MESSAGE.get);
685 if ruleErrorMsg IS NOT NULL then
686 logString( exceptionLogLevel, procedureName, 'exception', ruleErrorMsg);
687 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, packageName || '.' || procedureName || ' ERROR: ' || ruleErrorMsg);
688 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
689
690 end if;
691
692 if ruleRetcode = 1 then
693 FND_MESSAGE.set_name( 'GCS', 'GCS_CONCREQ_WARNING' );
694 errbuf := FND_MESSAGE.get;
695 retcode := 1;
696 elsif ruleRetcode = 2 then
697 retcode := 2;
698 FND_MESSAGE.set_name( 'GCS', 'GCS_CONCREQ_FAILURE' );
699 errbuf := FND_MESSAGE.get;
700 RAISE rule_processor_failure;
701 elsif ruleRetCode = 3 then
702 --special case indicates out of balance entry
703 retcode := 2;
704 FND_MESSAGE.set_name( 'GCS', 'GCS_CONCREQ_FAILURE' );
705 errbuf := FND_MESSAGE.get;
706 RAISE rule_processor_failure;
707 end if;
708
709 End If;
710
711 End Loop; --for x in 1..xnsData.LAST
712
713 --=======================================================
714 logString( eventLogLevel, procedureName, 'section', '9');
715 --=======================================================
716
717 -- ++++++++++++++++++++++++++++++++++++
718 -- CLEAN UP SECTION
719 -- ++++++++++++++++++++++++++++++++++++
720
721 -- Cannot get here unless retcode is a 0 (success) or a 1 (warning)
722 COMMIT;
723
724 --=======================================================
725 logString( eventLogLevel, procedureName, 'section', '11');
726 --=======================================================
727
728 -------------------------------------
729 -- Write out file
730 -------------------------------------
731
732 -------------------------------------
733 -- Write log file footer and return
734 -------------------------------------
735 FND_MESSAGE.set_name( 'GCS', 'GCS_CONCREQ_SUCCESS' );
736 errbuf := FND_MESSAGE.get;
737
738 logString( procedureLogLevel, procedureName, 'end', to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
739 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, packageName || '.' || procedureName || ' END');
740 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
741
742
743 -- ++++++++++++++++++++++++++++++++++++
744 -- ERROR HANDLERS
745 -- ++++++++++++++++++++++++++++++++++++
746
747 EXCEPTION
748
749 WHEN invalid_arguments THEN
750 -- The only way this can raise is if the transaction_id is not valid
751 -- As such, no point to update the interface rows' status_code and
752 -- no need to commit (since no changes are made in the handler)
753 ROLLBACK;
754 FND_MESSAGE.set_name( 'GCS', 'GCS_INVALID_COMMAND_LINE' );
755 FND_MESSAGE.set_token( 'PROCEDURE' , packageName||'.'||procedureName );
756 logString( exceptionLogLevel, procedureName, 'GCS', FND_MESSAGE.get );
757
758 FND_MESSAGE.set_name( 'GCS', 'GCS_CONCREQ_FAILURE' );
759 errbuf := FND_MESSAGE.get;
760 retcode := 2;
761
762 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, packageName || '.' || procedureName || ' ERROR: ' || errbuf);
763 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
764
765 logString( procedureLogLevel, procedureName, 'end', to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
766
767 WHEN invalid_xns_type THEN
768 ROLLBACK;
769 FND_MESSAGE.set_name( 'GCS', 'GCS_INVALID_XNS_TYPE' );
770 FND_MESSAGE.set_token( 'PROCEDURE' , packageName||'.'||procedureName );
771 FND_MESSAGE.set_token( 'TRANSACTION_TYPE', xnsData.transaction_type_code );
772 logString( exceptionLogLevel, procedureName, 'GCS', FND_MESSAGE.get );
773
774 FND_MESSAGE.set_name( 'GCS', 'GCS_CONCREQ_FAILURE' );
775 errbuf := FND_MESSAGE.get;
776 retcode := 2;
777 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, packageName || '.' || procedureName || ' ERROR: ' || errbuf);
778 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
779
780 logString( procedureLogLevel, procedureName, 'end', to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
781
782 WHEN no_rule_found THEN
783 ROLLBACK;
784 FND_MESSAGE.set_name( 'GCS', 'GCS_MISSING_RULE' );
785 FND_MESSAGE.set_token( 'PROCEDURE' , packageName||'.'||procedureName );
786 logString( exceptionLogLevel, procedureName, 'GCS', FND_MESSAGE.get );
787
788 FND_MESSAGE.set_name( 'GCS', 'GCS_CONCREQ_FAILURE' );
789 errbuf := FND_MESSAGE.get;
790 retcode := 2;
791 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, packageName || '.' || procedureName || ' ERROR: ' || errbuf);
792 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
793
794 logString( procedureLogLevel, procedureName, 'end', to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
795
796 WHEN missing_key THEN
797 --An error msg is placed on the stack at the exception raise point
798 --A logString call is made at the exception raise point
799 ROLLBACK;
800 FND_MESSAGE.set_name( 'GCS', 'GCS_CONCREQ_FAILURE' );
801 errbuf := FND_MESSAGE.get;
802 retcode := 2;
803 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, packageName || '.' || procedureName || ' ERROR: ' || errbuf);
804 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
805
806 logString( procedureLogLevel, procedureName, 'end', to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
807
808 WHEN rule_processor_failure THEN
809 --errbuf and retcode are set at the point where this is raised
810 ROLLBACK;
811 logString( procedureLogLevel, procedureName, 'end', to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
812
813 WHEN OTHERS THEN
814 ROLLBACK;
815 FND_MESSAGE.set_name( 'GCS', 'GCS_UNHANDLED_EXCEPTION' );
816 FND_MESSAGE.set_token( 'PROCEDURE' , packageName||'.'||procedureName );
817 FND_MESSAGE.set_token( 'EVENT', 'OTHERS' );
818 logString( exceptionLogLevel, procedureName, 'GCS', FND_MESSAGE.get );
819
820 FND_MESSAGE.set_name( 'GCS', 'GCS_CONCREQ_FAILURE' );
821 retcode := 2;
822 errbuf := FND_MESSAGE.get;
823 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, packageName || '.' || procedureName || ' ERROR: '|| errbuf);
824 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
825
826 logString( procedureLogLevel, procedureName, 'end', to_char(SYSDATE, 'DD-MON-YYYY HH:MI:SS'));
827
828 END process_transaction;
829
830 --
831 -- PACKAGE "Constructor"
832 --
833
834 END GCS_AD_ENGINE;