[Home] [Help]
PACKAGE BODY: APPS.GCS_AD_TRIALBALANCE_PKG
Source
1 PACKAGE BODY GCS_AD_TRIALBALANCE_PKG AS
2 /* $Header: gcsadtbb.pls 120.3 2006/05/29 06:57:51 vkosuri noship $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7
8 -- The API name
9 g_pkg_name VARCHAR2 (50) := 'gcs.plsql.GCS_AD_TRIALBALANCE_PKG';
10 -- A newline character. Included for convenience when writing long strings.
11 g_nl VARCHAR2 (1) := '
12 ';
13
14 import_header_error EXCEPTION;
15
16 ---------------------------------------------------------------------------
17 --Bug fix 3843350 : populate elim_entity_id into the entry header
18 /*
19 ** get_elim_entity_id
20 */
21 FUNCTION get_elim_entity_id (p_consolidation_entity_id IN NUMBER)
22 RETURN NUMBER
23 IS
24 CURSOR c_elim_entity
25 IS
26 SELECT dim_attribute_numeric_member
27 FROM fem_entities_attr
28 WHERE entity_id = p_consolidation_entity_id
29 AND attribute_id =
30 gcs_utility_pkg.g_dimension_attr_info
31 ('ENTITY_ID-ELIMINATION_ENTITY').attribute_id
32 AND version_id =
33 gcs_utility_pkg.g_dimension_attr_info
34 ('ENTITY_ID-ELIMINATION_ENTITY').version_id;
35
36 l_elim_entity_id NUMBER;
37 l_api_name VARCHAR2 (30) := 'GET_ELIM_ENTITY_ID';
38 BEGIN
39 OPEN c_elim_entity;
40
41 FETCH c_elim_entity
42 INTO l_elim_entity_id;
43
44 CLOSE c_elim_entity;
45
46 RETURN l_elim_entity_id;
47 EXCEPTION
48 WHEN OTHERS
49 THEN
50 -- Write the appropriate information to the execution report
51 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
52 THEN
53 fnd_log.STRING (fnd_log.level_error,
54 g_pkg_name || '.' || l_api_name,
55 gcs_utility_pkg.g_module_failure
56 || ' '
57 || SQLERRM
58 || ' '
59 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
60 );
61 END IF;
62
63 RETURN 0;
64 END get_elim_entity_id;
65
66 /*
67 ** import_header
68 */
69
70 FUNCTION import_header (
71 p_xns_id IN NUMBER,
72 p_entry_name IN VARCHAR2,
73 p_description IN VARCHAR2,
74 p_consideration_amount IN NUMBER,
75 p_currency_code IN VARCHAR2,
76 p_hierarchy_id IN NUMBER,
77 p_entity_id IN NUMBER,
78 p_balance_type_code IN VARCHAR2,
79 p_entry_type_code IN VARCHAR2 DEFAULT 'MANUAL'
80 )
81 RETURN NUMBER
82 IS
83 l_entry_id NUMBER(15);
84 l_new_entry_id NUMBER(15);
85 l_cal_period_id NUMBER;
86 l_year_to_apply_re NUMBER (4) := NULL;
87 l_hierarchy_id NUMBER(15);
88 l_entity_id NUMBER;
89 l_errbuf VARCHAR2 (200);
90 l_retcode VARCHAR2 (1);
91 l_processed_entry_flag VARCHAR2 (1);
92 l_api_name VARCHAR2 (30) := 'IMPORT_HEADER';
93 BEGIN
94 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' ENTER');
95 FND_FILE.NEW_LINE(FND_FILE.LOG);
96
97 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
98 THEN
99 fnd_log.STRING (fnd_log.level_procedure,
100 g_pkg_name || '.' || l_api_name,
101 gcs_utility_pkg.g_module_enter
102 || ' p_xns_id = '
103 || p_xns_id
104 || ' p_entry_name = '
105 || p_entry_name
106 || ' p_consideration_amount = '
107 || p_consideration_amount
108 || ' p_description = '
109 || p_description
110 || ' p_currency_code = '
111 || p_currency_code
112 || ' p_hierarchy_id = '
113 || p_hierarchy_id
114 || ' p_entity_id = '
115 || p_entity_id
116 || ' p_balance_type_code = '
117 || p_balance_type_code
118 || ' '
119 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
120 );
121 END IF;
122
123 -- select assoc_entry_id from gcs_ad_transactions table
124 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
125 THEN
126 fnd_log.STRING (fnd_log.level_statement,
127 g_pkg_name || '.' || l_api_name,
128 'SELECT gat.assoc_entry_id, gat.cal_period_id, fcpa.number_assign_value + 1 '
129 || g_nl
130 || 'INTO l_entry_id, l_cal_period_id, l_year_to_apply_re '
131 || g_nl
132 || 'FROM fem_cal_periods_attr fcpa, gcs_ad_transactions gat'
133 || g_nl
134 || ' WHERE fcpa.cal_period_id = gat.cal_period_id
135 AND fcpa.attribute_id = ' ||
136 gcs_utility_pkg.g_dimension_attr_info ('CAL_PERIOD_ID-ACCOUNTING_YEAR').attribute_id ||'
137 AND fcpa.version_id = ' ||
138 gcs_utility_pkg.g_dimension_attr_info ('CAL_PERIOD_ID-ACCOUNTING_YEAR').version_id ||'
139 AND gat.AD_TRANSACTION_ID = '
140 || p_xns_id
141 );
142 END IF;
143
144 SELECT gat.assoc_entry_id, gat.cal_period_id, fcpa.number_assign_value + 1
145 INTO l_entry_id, l_cal_period_id, l_year_to_apply_re
146 FROM fem_cal_periods_attr fcpa, gcs_ad_transactions gat
147 WHERE fcpa.cal_period_id = gat.cal_period_id
148 AND fcpa.attribute_id =
149 gcs_utility_pkg.g_dimension_attr_info ('CAL_PERIOD_ID-ACCOUNTING_YEAR').attribute_id
150 AND fcpa.version_id =
151 gcs_utility_pkg.g_dimension_attr_info ('CAL_PERIOD_ID-ACCOUNTING_YEAR').version_id
152 AND gat.ad_transaction_id = p_xns_id;
153
154
155 IF l_entry_id IS NULL
156 THEN
157 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' CREATE ENTRY');
158 FND_FILE.NEW_LINE(FND_FILE.LOG);
159
160 -- create an entry header if not exists
161 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
162 THEN
163 fnd_log.STRING (fnd_log.level_statement,
164 g_pkg_name || '.' || l_api_name,
165 'SELECT gcs_entry_headers_s.NEXTVAL'
166 || g_nl
167 || 'INTO l_entry_id'
168 || g_nl
169 || 'FROM dual'
170 );
171 END IF;
172
173 SELECT gcs_entry_headers_s.NEXTVAL
174 INTO l_entry_id
175 FROM DUAL;
176
177 gcs_entry_pkg.insert_entry_header
178 (p_entry_id => l_entry_id,
179 p_hierarchy_id => p_hierarchy_id,
180 p_entity_id => p_entity_id,
181 p_year_to_apply_re => l_year_to_apply_re,
182 p_start_cal_period_id => l_cal_period_id,
183 p_end_cal_period_id => NULL,
184 p_entry_type_code => p_entry_type_code,
185 p_balance_type_code => p_balance_type_code,
186 p_currency_code => p_currency_code,
187 p_process_code => 'ALL_RUN_FOR_PERIOD',
188 p_description => p_description,
189 p_entry_name => p_entry_name,
190 p_category_code => 'ACQ_DISP',
191 x_errbuf => l_errbuf,
192 x_retcode => l_retcode
193 );
194
195 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
196 THEN
197 fnd_log.STRING (fnd_log.level_statement,
198 g_pkg_name || '.' || l_api_name,
199 'UPDATE gcs_ad_transactions'
200 || g_nl
201 || 'SET ASSOC_ENTRY_ID = '
202 || l_entry_id
203 || ', total_consideration = '
204 || p_consideration_amount
205 || g_nl
206 || ', last_update_date = sysdate'
207 || g_nl
208 || 'WHERE AD_TRANSACTION_ID = '
209 || p_xns_id
210 );
211 END IF;
212
213 UPDATE gcs_ad_transactions
214 SET assoc_entry_id = l_entry_id,
215 total_consideration = p_consideration_amount,
216 last_update_date = sysdate
217 WHERE ad_transaction_id = p_xns_id;
218 ELSE
219 -- case 2: update an existing entry which has never been process before
220 -- we simply update this entry
221 BEGIN
222 SELECT 'Y'
223 INTO l_processed_entry_flag
224 FROM DUAL
225 WHERE EXISTS (SELECT run_detail_id
226 FROM gcs_cons_eng_run_dtls gcerd
227 WHERE gcerd.entry_id = l_entry_id);
228 EXCEPTION
229 WHEN NO_DATA_FOUND
230 THEN
231 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' UPDATE EXISTING ENTRY');
232 FND_FILE.NEW_LINE(FND_FILE.LOG);
233
234 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
235 THEN
236 fnd_log.STRING (fnd_log.level_statement,
237 g_pkg_name || '.' || l_api_name,
238 'UPDATE gcs_entry_headers'
239 || g_nl
240 || 'SET entry_name = '
241 || p_entry_name
242 || g_nl
243 || ', description = '
244 || p_description
245 || ', balance_type_code = '
246 || p_balance_type_code
247 || g_nl
248 || ', last_update_date = sysdate'
249 || g_nl
250 || 'WHERE entry_id = '
251 || l_entry_id
252 );
253 END IF;
254
255 UPDATE gcs_entry_headers
256 SET entry_name = p_entry_name,
257 description = p_description,
258 balance_type_code = p_balance_type_code,
259 last_update_date = sysdate
260 WHERE entry_id = l_entry_id;
261 END;
262
263 IF p_consideration_amount is not null THEN
264 -- update total_consideration in gcs_ad_transactions table
265 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
266 THEN
267 fnd_log.STRING (fnd_log.level_statement,
268 g_pkg_name || '.' || l_api_name,
269 'UPDATE gcs_ad_transactions'
270 || g_nl
271 || 'SET total_consideration = '
272 || p_consideration_amount
273 || g_nl
274 || ', last_update_date = sysdate'
275 || g_nl
276 || 'WHERE AD_TRANSACTION_ID = '
277 || p_xns_id
278 );
279 END IF;
280
281 UPDATE gcs_ad_transactions
282 SET total_consideration = p_consideration_amount,
283 last_update_date = sysdate
284 WHERE ad_transaction_id = p_xns_id;
285 END IF;
286
287 END IF;
288
289 -- case 3: update an existing entry which has been process before
290 -- we disable the existing entry and create a new one
291 IF l_processed_entry_flag = 'Y'
292 THEN
293 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' DISABLE EXISTING ENTRY AND CREATE A NEW ONE');
294 FND_FILE.NEW_LINE(FND_FILE.LOG);
295
296 -- create a new entry header
297 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
298 THEN
299 fnd_log.STRING (fnd_log.level_statement,
300 g_pkg_name || '.' || l_api_name,
301 'SELECT gcs_entry_headers_s.NEXTVAL'
302 || g_nl
303 || 'INTO l_new_entry_id'
304 || g_nl
305 || 'FROM dual'
306 );
307 END IF;
308
309 SELECT gcs_entry_headers_s.NEXTVAL
310 INTO l_new_entry_id
311 FROM DUAL;
312
313 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
314 THEN
315 fnd_log.STRING (fnd_log.level_statement,
316 g_pkg_name || '.' || l_api_name,
317 'UPDATE gcs_entry_headers'
318 || g_nl
319 || 'SET disabled_flag = ''Y'''
320 || g_nl
321 || ',entry_name = substr(entry_name, 0, 55) || '' OLD ->'' || '
322 || l_new_entry_id
323 || ', last_update_date = sysdate '
324 || g_nl
325 || 'WHERE entry_id = '
326 || l_entry_id
327 );
328 END IF;
329
330 UPDATE gcs_entry_headers
331 SET disabled_flag = 'Y',
332 entry_name = substr(entry_name, 0, 55) || ' OLD ->' || l_new_entry_id,
333 last_update_date = sysdate
334 WHERE entry_id = l_entry_id;
335
336 l_entry_id := l_new_entry_id;
337
338 gcs_entry_pkg.insert_entry_header
339 (p_entry_id => l_entry_id,
340 p_hierarchy_id => p_hierarchy_id,
341 p_entity_id => p_entity_id,
342 p_year_to_apply_re => l_year_to_apply_re,
343 p_start_cal_period_id => l_cal_period_id,
344 p_end_cal_period_id => NULL,
345 p_entry_type_code => p_entry_type_code,
346 p_balance_type_code => p_balance_type_code,
347 p_currency_code => p_currency_code,
348 p_process_code => 'ALL_RUN_FOR_PERIOD',
349 p_description => p_description,
350 p_entry_name => p_entry_name,
351 p_category_code => 'ACQ_DISP',
352 x_errbuf => l_errbuf,
353 x_retcode => l_retcode
354 );
355
356 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
357 THEN
358 fnd_log.STRING (fnd_log.level_statement,
359 g_pkg_name || '.' || l_api_name,
360 'UPDATE gcs_ad_transactions'
361 || g_nl
362 || 'SET ASSOC_ENTRY_ID = '
363 || l_entry_id
364 || ', total_consideration = '
365 || p_consideration_amount
366 || g_nl
367 || ', last_update_date = sysdate'
368 || g_nl
369 || 'WHERE AD_TRANSACTION_ID = '
370 || p_xns_id
371 );
372 END IF;
373
374 UPDATE gcs_ad_transactions
375 SET assoc_entry_id = l_entry_id,
376 total_consideration = p_consideration_amount,
377 last_update_date = sysdate
378 WHERE ad_transaction_id = p_xns_id;
379
380 END IF; -- end of case 3
381
382 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' EXIT');
383 FND_FILE.NEW_LINE(FND_FILE.LOG);
384
385 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
386 THEN
387 fnd_log.STRING (fnd_log.level_procedure,
388 g_pkg_name || '.' || l_api_name,
389 gcs_utility_pkg.g_module_success
390 || ' '
391 || l_api_name
392 || '() '
393 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
394 );
395 END IF;
396
397 RETURN l_entry_id;
398
399 EXCEPTION
400 WHEN NO_DATA_FOUND
401 THEN
402 fnd_message.set_name ('GCS', 'GCS_AD_TB_INVALID_ID');
403
404 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || SQLERRM);
405 FND_FILE.NEW_LINE(FND_FILE.LOG);
406
407 -- Write the appropriate information to the execution report
408 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
409 THEN
410 fnd_log.STRING (fnd_log.level_error,
411 g_pkg_name || '.' || l_api_name,
412 gcs_utility_pkg.g_module_failure
413 || ' '
414 || l_api_name
415 || '() ' || SQLERRM
416 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
417 );
418 END IF;
419 RETURN -1;
420 WHEN OTHERS
421 THEN
422 fnd_message.set_name ('GCS', SQLERRM);
423
424 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || SQLERRM);
425 FND_FILE.NEW_LINE(FND_FILE.LOG);
426
427 -- Write the appropriate information to the execution report
428 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
429 THEN
430 fnd_log.STRING (fnd_log.level_error,
431 g_pkg_name || '.' || l_api_name,
432 gcs_utility_pkg.g_module_failure
433 || ' '
434 || l_api_name
435 || '() ' || SQLERRM
436 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
437 );
438 END IF;
439 RETURN -1;
440 END import_header;
441
442 --
443 -- PUBLIC PROCEDURES
444 --
445 ---------------------------------------------------------------------------
446 /*
447 ** upload_header
448 */
449 -- Bug fix : 5169619 -- data type of p_xns_id(_char) changed to VARCHAR2
450 PROCEDURE upload_header (
451 p_consolidation_entity_id IN NUMBER,
452 p_hierarchy_id IN NUMBER,
453 p_transaction_date IN VARCHAR2,
454 p_currency_code IN VARCHAR2,
455 p_xns_id_char IN VARCHAR2,
456 p_category_code IN VARCHAR2,
457 p_template_type IN VARCHAR2,
458 p_entry_name IN VARCHAR2,
459 p_operating_entity_id IN NUMBER,
460 p_consideration_amount IN NUMBER,
461 p_description IN VARCHAR2
462 )
463 IS
464 BEGIN
465 NULL;
466 END upload_header;
467
468 ---------------------------------------------------------------------------
469 /*
470 ** import_entry
471 */
472
473 -- Bug fix : 5169619 -- data type of p_xns_id(_char) changed to VARCHAR2
474
475 PROCEDURE import_entry (
476 x_errbuf OUT NOCOPY VARCHAR2,
477 x_retcode OUT NOCOPY VARCHAR2,
478 p_xns_id_char IN VARCHAR2,
479 p_entry_name IN VARCHAR2,
480 p_description IN VARCHAR2,
481 p_consideration_amount IN NUMBER,
482 p_currency_code IN VARCHAR2,
483 p_hierarchy_id IN NUMBER,
484 p_consolidation_entity_id IN NUMBER,
485 p_operating_entity_id IN NUMBER
486 )
487 IS
488 l_api_name VARCHAR2 (30) := 'IMPORT_ENTRY';
489 l_entry_id NUMBER (15);
490 l_orig_entry_id NUMBER (15);
491 l_event_name VARCHAR2 (100) := 'oracle.apps.gcs.transaction.acqdisp.update';
492 l_event_key VARCHAR2 (100) := NULL;
493 l_parameter_list wf_parameter_list_t;
494 l_balance_type_code VARCHAR2 (30);
495 l_elim_entity_id NUMBER;
496 l_org_code VARCHAR2 (30);
497 p_xns_id NUMBER(15) := TO_NUMBER(p_xns_id_char);
498
499 l_line_item_vs_id NUMBER :=
500 gcs_utility_pkg.g_gcs_dimension_info ('LINE_ITEM_ID').associated_value_set_id;
501 l_ext_acct_type_attr NUMBER :=
502 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').attribute_id;
503 l_ext_acct_type_version NUMBER :=
504 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').version_id;
505 l_basic_acct_type_attr NUMBER :=
506 gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').attribute_id;
507 l_basic_acct_type_version NUMBER :=
508 gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').version_id;
509 BEGIN
510
511 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' ENTER');
512 FND_FILE.NEW_LINE(FND_FILE.LOG);
513
514 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
515 THEN
516 fnd_log.STRING (fnd_log.level_procedure,
517 g_pkg_name || '.' || l_api_name,
518 gcs_utility_pkg.g_module_enter
519 || ' p_xns_id = '
520 || p_xns_id
521 || ' p_entry_name = '
522 || p_entry_name
523 || ' p_consideration_amount = '
524 || p_consideration_amount
525 || ' p_description = '
526 || p_description
527 || ' p_currency_code = '
528 || p_currency_code
529 || ' p_hierarchy_id = '
530 || p_hierarchy_id
531 || ' p_consolidation_entity_id = '
532 || p_consolidation_entity_id
533 || ' '
534 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
535 );
536 END IF;
537
538 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
539 THEN
540 fnd_log.STRING (fnd_log.level_statement,
541 g_pkg_name || '.' || l_api_name,
542 'select assoc_entry_id into l_orig_entry_id from gcs_ad_transactions
543 where ad_transaction_id = '
544 || p_xns_id
545 );
546 END IF;
547
548 select assoc_entry_id
549 into l_orig_entry_id
550 from gcs_ad_transactions
551 where ad_transaction_id = p_xns_id;
552
553 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
554 THEN
555 fnd_log.STRING (fnd_log.level_statement,
556 g_pkg_name || '.' || l_api_name,
557 'select decode(count(entry_id), 0, ''ACTUAL'', ''ADB'') into l_balance_type_code
558 from gcs_entry_lines where entry_id = -1 and financial_elem_id = 140'
559 );
560 END IF;
561 select decode(count(entry_id), 0, 'ACTUAL', 'ADB')
562 into l_balance_type_code
563 from gcs_entry_lines
564 where entry_id = -1
565 and financial_elem_id = 140;
566
567 l_elim_entity_id := get_elim_entity_id(p_consolidation_entity_id => p_consolidation_entity_id);
568
569 l_entry_id := import_header(
570 p_xns_id => p_xns_id,
571 p_entry_name => p_entry_name,
572 p_consideration_amount => p_consideration_amount,
573 p_description => p_description,
574 p_currency_code => p_currency_code,
575 p_hierarchy_id => p_hierarchy_id,
576 p_entity_id => l_elim_entity_id,
577 p_balance_type_code => l_balance_type_code
578 );
579
580 IF l_entry_id < 0 THEN
581 RAISE import_header_error;
582 END IF;
583
584 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
585 THEN
586 fnd_log.STRING (fnd_log.level_statement,
587 g_pkg_name || '.' || l_api_name,
588 'delete from gcs_entry_lines'
589 || g_nl
590 || 'where entry_id = '
591 || l_entry_id
592 );
593 END IF;
594
595 DELETE FROM gcs_entry_lines
596 WHERE entry_id = l_entry_id;
597
598
599 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
600 THEN
601 fnd_log.STRING (fnd_log.level_statement,
602 g_pkg_name || '.' || l_api_name,
603 'update gcs_entry_lines'
604 || g_nl
605 || 'set entry_id = '
606 || l_entry_id
607 || ', last_update_date = sysdate,'
608 || g_nl
609 || ' line_type_code = decode((SELECT feata.dim_attribute_varchar_member
610 FROM fem_ext_acct_types_attr feata,
611 fem_ln_items_attr flia
612 WHERE gcs_entry_lines.line_item_id =
613 flia.line_item_id
614 AND flia.value_set_id =' ||
615 gcs_utility_pkg.g_gcs_dimension_info ('LINE_ITEM_ID').associated_value_set_id || '
616 AND flia.attribute_id = '||
617 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').attribute_id || '
618 AND flia.version_id = '||
619 gcs_utility_pkg.g_dimension_attr_info('LINE_ITEM_ID-EXTENDED_ACCOUNT_TYPE').version_id || '
620 AND feata.attribute_id = '||
621 gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').attribute_id || '
622 AND feata.version_id = '||
623 gcs_utility_pkg.g_dimension_attr_info('EXT_ACCOUNT_TYPE_CODE-BASIC_ACCOUNT_TYPE_CODE').version_id || '
624 AND feata.ext_account_type_code =
625 flia.dim_attribute_varchar_member), ''REVENUE'', ''PROFIT_LOSS'',
626 ''EXPENSE'', ''PROFIT_LOSS'', ''BALANCE_SHEET'') '
627 || g_nl
628 || 'where entry_id = -1'
629 );
630 END IF;
631
632 --Bugfix 4332257 : Resolved the Issue with YTD_BALANCE_E not being populated
633 UPDATE gcs_entry_lines
634 SET entry_id = l_entry_id,
635 last_update_date = sysdate,
636 ytd_balance_e = NVL(ytd_debit_balance_e, 0) - NVL(ytd_credit_balance_e, 0),
637 line_type_code = decode(
638 (SELECT feata.dim_attribute_varchar_member
639 FROM fem_ext_acct_types_attr feata,
640 fem_ln_items_attr flia
641 WHERE gcs_entry_lines.line_item_id = flia.line_item_id
642 AND flia.value_set_id = l_line_item_vs_id
643 AND flia.attribute_id = l_ext_acct_type_attr
644 AND flia.version_id = l_ext_acct_type_version
645 AND feata.attribute_id = l_basic_acct_type_attr
646 AND feata.version_id = l_basic_acct_type_version
647 AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
648 ), 'REVENUE', 'PROFIT_LOSS', 'EXPENSE', 'PROFIT_LOSS', 'BALANCE_SHEET'
649 )
650 WHERE entry_id = -1;
651
652 --Bugfix 4411633 : retained earnings should write to child base org
653 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
654 THEN
655 fnd_log.STRING (fnd_log.level_statement,
656 g_pkg_name || '.' || l_api_name,
657 'SELECT org_output_code INTO l_org_code '
658 || 'FROM gcs_categories_b WHERE category_code = ''ACQ_DISP'''
659 );
660 END IF;
661
662 SELECT org_output_code
663 INTO l_org_code
664 FROM gcs_categories_b
665 WHERE category_code = 'ACQ_DISP';
666
667 IF (l_org_code = 'CHILD_BASE_ORG') THEN
668 gcs_templates_dynamic_pkg.calculate_re
669 (p_entry_id => l_entry_id,
670 p_hierarchy_id => p_hierarchy_id,
671 p_bal_type_code => l_balance_type_code,
672 p_entity_id => p_operating_entity_id
673 );
674 ELSE
675 gcs_templates_dynamic_pkg.calculate_re
676 (p_entry_id => l_entry_id,
677 p_hierarchy_id => p_hierarchy_id,
678 p_bal_type_code => l_balance_type_code,
679 p_entity_id => l_elim_entity_id
680 );
681 END IF;
682 --end of Bugfix 4411633
683
684 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
685 THEN
686 fnd_log.STRING (fnd_log.level_statement,
687 g_pkg_name || '.' || l_api_name,
688 'update gcs_ad_transactions'
689 || g_nl
690 || 'set request_id = '
691 || fnd_global.conc_request_id
692 || g_nl
693 || ', last_update_date = sysdate'
694 || g_nl
695 || 'where ad_transaction_id = ' || p_xns_id
696 );
697 END IF;
698 UPDATE gcs_ad_transactions
699 SET request_id = fnd_global.conc_request_id,
700 last_update_date = sysdate
701 WHERE ad_transaction_id = p_xns_id;
702 /*
703 BEGIN
704 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' Calling entry XML Gen ');
705 FND_FILE.NEW_LINE(FND_FILE.LOG);
706
707 gcs_xml_gen_pkg.generate_entry_xml( p_entry_id => l_entry_id,
708 p_category_code => 'ACQ_DISP',
709 p_cons_rule_flag => 'N');
710 EXCEPTION
711 WHEN OTHERS
712 THEN
713 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' Generate XML error : ' || SQLERRM);
714 FND_FILE.NEW_LINE(FND_FILE.LOG);
715
716 -- Write the appropriate information to the execution report
717 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
718 THEN
719 fnd_log.STRING (fnd_log.level_error,
720 g_pkg_name || '.' || l_api_name,
721 gcs_utility_pkg.g_module_failure
722 || ' entry XML Gen failed: '
723 || SQLERRM
724 || ' '
725 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
726 );
727 END IF;
728 END;
729 */
730 BEGIN
731 IF (NVL(l_orig_entry_id,-1) <> l_entry_id) THEN
732
733 IF (l_orig_entry_id IS NULL) THEN
734 wf_event.addparametertolist( p_name => 'CHANGE_TYPE_CODE',
735 p_value => 'NEW_ACQDISP',
736 p_parameterlist => l_parameter_list
737 );
738 ELSE
739 wf_event.addparametertolist( p_name => 'CHANGE_TYPE_CODE',
740 p_value => 'ACQDISP_MODIFIED',
741 p_parameterlist => l_parameter_list
742 );
743 END IF;
744 wf_event.addparametertolist( p_name => 'ENTRY_ID',
745 p_value => l_entry_id,
746 p_parameterlist => l_parameter_list
747 );
748 wf_event.addparametertolist( p_name => 'ORIG_ENTRY_ID',
749 p_value => l_orig_entry_id,
750 p_parameterlist => l_parameter_list
751 );
752 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' RAISE WF_EVENT');
753 FND_FILE.NEW_LINE(FND_FILE.LOG);
754
755 wf_event.RAISE( p_event_name => l_event_name,
756 p_event_key => l_event_key,
757 p_parameters => l_parameter_list
758 );
759 END IF;
760
761 EXCEPTION
762 WHEN OTHERS
763 THEN
764 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' Raise impact error : ' || SQLERRM);
765 FND_FILE.NEW_LINE(FND_FILE.LOG);
766
767 -- Write the appropriate information to the execution report
768 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
769 THEN
770 fnd_log.STRING (fnd_log.level_error,
771 g_pkg_name || '.' || l_api_name,
772 gcs_utility_pkg.g_module_failure
773 || ' '
774 || SQLERRM
775 || ' '
776 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
777 );
778 END IF;
779 END;
780
781 x_retcode := fnd_api.g_ret_sts_success;
782
783 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
784 THEN
785 fnd_log.STRING (fnd_log.level_procedure,
786 g_pkg_name || '.' || l_api_name,
787 gcs_utility_pkg.g_module_success
788 || ' '
789 || l_api_name
790 || '() '
791 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
792 );
793 END IF;
794
795 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' EXIT');
796 FND_FILE.NEW_LINE(FND_FILE.LOG);
797
798 EXCEPTION
799 WHEN import_header_error
800 THEN
801 x_errbuf := fnd_message.get;
802 x_retcode := '2';
803
804 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' ERROR : ' || x_errbuf);
805 FND_FILE.NEW_LINE(FND_FILE.LOG);
806 -- Write the appropriate information to the execution report
807 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
808 THEN
809 fnd_log.STRING (fnd_log.level_error,
810 g_pkg_name || '.' || l_api_name,
811 gcs_utility_pkg.g_module_failure
812 || ' '
813 || x_errbuf
814 || ' '
815 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
816 );
817 END IF;
818 WHEN OTHERS
819 THEN
820 fnd_message.set_name ('GCS', SQLERRM);
821 x_errbuf := fnd_message.get;
822 x_retcode := '2';
823
824 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' ERROR : ' || x_errbuf);
825 FND_FILE.NEW_LINE(FND_FILE.LOG);
826
827 -- Write the appropriate information to the execution report
828 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
829 THEN
830 fnd_log.STRING (fnd_log.level_error,
831 g_pkg_name || '.' || l_api_name,
832 gcs_utility_pkg.g_module_failure
833 || ' '
834 || x_errbuf
835 || ' '
836 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
837 );
838 END IF;
839 END import_entry;
840
841 ---------------------------------------------------------------------------
842 /*
843 ** import
844 */
845
846 -- Bug fix : 5169619 -- data type of p_xns_id(_char) changed to VARCHAR2
847 PROCEDURE import (
848 x_errbuf OUT NOCOPY VARCHAR2,
849 x_retcode OUT NOCOPY VARCHAR2,
850 p_xns_id_char IN VARCHAR2,
851 p_entry_name IN VARCHAR2,
852 p_description IN VARCHAR2,
853 p_consideration_amount IN NUMBER,
854 p_currency_code IN VARCHAR2,
855 p_hierarchy_id IN NUMBER,
856 p_consolidation_entity_id IN NUMBER
857 )
858 IS
859 l_api_name VARCHAR2 (30) := 'IMPORT';
860 l_entry_id NUMBER (15);
861 l_orig_entry_id NUMBER (15);
862 l_event_name VARCHAR2 (100)
863 := 'oracle.apps.gcs.transaction.acqdisp.update';
864 l_event_key VARCHAR2 (100) := NULL;
865 l_parameter_list wf_parameter_list_t;
866 l_elim_entity_id NUMBER;
867 l_balance_type_code VARCHAR2 (30);
868 p_xns_id NUMBER(15) := TO_NUMBER(p_xns_id_char) ;
869 BEGIN
870 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' ENTER');
871 FND_FILE.NEW_LINE(FND_FILE.LOG);
872
873 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
874 THEN
875 fnd_log.STRING (fnd_log.level_procedure,
876 g_pkg_name || '.' || l_api_name,
877 gcs_utility_pkg.g_module_enter
878 || ' p_xns_id = '
879 || p_xns_id
880 || ' p_entry_name = '
881 || p_entry_name
882 || ' p_consideration_amount = '
883 || p_consideration_amount
884 || ' p_description = '
885 || p_description
886 || ' p_currency_code = '
887 || p_currency_code
888 || ' p_hierarchy_id = '
889 || p_hierarchy_id
890 || ' p_consolidation_entity_id = '
891 || p_consolidation_entity_id
892 || ' '
893 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
894 );
895 END IF;
896
897 select assoc_entry_id
898 into l_orig_entry_id
899 from gcs_ad_transactions
900 where ad_transaction_id = p_xns_id;
901
902 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
903 THEN
904 fnd_log.STRING (fnd_log.level_statement,
905 g_pkg_name || '.' || l_api_name,
906 'Original entry id = ' || l_orig_entry_id
907 );
908 END IF;
909
910 select decode(count(ad_transaction_id), 0, 'ACTUAL', 'ADB')
911 into l_balance_type_code
912 from gcs_ad_trial_balances
913 where ad_transaction_id = -1
914 and financial_elem_id = 140;
915
916 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
917 THEN
918 fnd_log.STRING (fnd_log.level_statement,
919 g_pkg_name || '.' || l_api_name,
920 'Balance type code is ' || l_balance_type_code
921 );
922 END IF;
923
924 l_elim_entity_id := get_elim_entity_id(p_consolidation_entity_id => p_consolidation_entity_id);
925
926 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
927 THEN
928 fnd_log.STRING (fnd_log.level_statement,
929 g_pkg_name || '.' || l_api_name,
930 'Elimination entity id = ' || l_elim_entity_id
931 );
932 END IF;
933
934 l_entry_id := import_header(
935 p_xns_id => p_xns_id,
936 p_entry_name => p_entry_name,
937 p_consideration_amount => p_consideration_amount,
938 p_description => p_description,
939 p_currency_code => p_currency_code,
940 p_hierarchy_id => p_hierarchy_id,
941 p_entity_id => l_elim_entity_id,
942 p_balance_type_code => l_balance_type_code,
943 p_entry_type_code => 'AUTOMATIC'
944 );
945
946 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
947 THEN
948 fnd_log.STRING (fnd_log.level_statement,
949 g_pkg_name || '.' || l_api_name,
950 'New entry id = ' || l_entry_id
951 );
952 END IF;
953
954 IF l_entry_id < 0 THEN
955 RAISE import_header_error;
956 END IF;
957
958 -- Delete the existing trial balances
959 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
960 THEN
961 fnd_log.STRING (fnd_log.level_statement,
962 g_pkg_name || '.' || l_api_name,
963 'DELETE FROM GCS_AD_TRIAL_BALANCES'
964 || g_nl
965 || 'WHERE AD_TRANSACTION_ID = '
966 || p_xns_id
967 );
968 END IF;
969
970 DELETE FROM gcs_ad_trial_balances
971 WHERE ad_transaction_id = p_xns_id;
972
973 -- Update the new load
974 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
975 THEN
976 fnd_log.STRING (fnd_log.level_statement,
977 g_pkg_name || '.' || l_api_name,
978 'UPDATE GCS_AD_TRIAL_BALANCES'
979 || g_nl
980 || 'SET ad_transaction_id = '
981 || p_xns_id
982 || ', last_update_date = sysdate'
983 || g_nl
984 || 'WHERE AD_TRANSACTION_ID = -1'
985 );
986 END IF;
987
988 UPDATE gcs_ad_trial_balances
989 SET ad_transaction_id = p_xns_id,
990 last_update_date = sysdate
991 WHERE ad_transaction_id = -1;
992 /*
993 BEGIN
994 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' Calling trial balance XML Gen ');
995 FND_FILE.NEW_LINE(FND_FILE.LOG);
996
997 gcs_xml_gen_pkg.generate_ad_xml( p_ad_transaction_id => p_xns_id);
998
999 EXCEPTION
1000 WHEN OTHERS
1001 THEN
1002 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' Generate trial balance XML error : ' || SQLERRM);
1003 FND_FILE.NEW_LINE(FND_FILE.LOG);
1004
1005 -- Write the appropriate information to the execution report
1006 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
1007 THEN
1008 fnd_log.STRING (fnd_log.level_error,
1009 g_pkg_name || '.' || l_api_name,
1010 gcs_utility_pkg.g_module_failure
1011 || ' XML Gen failed: '
1012 || SQLERRM
1013 || ' '
1014 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1015 );
1016 END IF;
1017 END;
1018 */
1019 -- invoke ad engine
1020 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' CALL AD_ENGINE');
1021 FND_FILE.NEW_LINE(FND_FILE.LOG);
1022
1023 gcs_ad_engine.process_transaction (errbuf => x_errbuf,
1024 retcode => x_retcode,
1025 p_transaction_id => p_xns_id
1026 );
1027 -- bug fix 3870797
1028 IF (x_retcode = '2') THEN
1029 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' AD_ENGINE FAILED: ' || x_errbuf);
1030 FND_FILE.NEW_LINE(FND_FILE.LOG);
1031 -- Write the appropriate information to the execution report
1032 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1033 THEN
1034 fnd_log.STRING (fnd_log.level_procedure,
1035 g_pkg_name || '.' || l_api_name,
1036 gcs_utility_pkg.g_module_failure
1037 || ' AD_ENGINE failed '
1038 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1039 );
1040 END IF;
1041 ELSE
1042 /* BEGIN
1043 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' Calling entry XML Gen ');
1044 FND_FILE.NEW_LINE(FND_FILE.LOG);
1045
1046 gcs_xml_gen_pkg.generate_entry_xml( p_entry_id => l_entry_id,
1047 p_category_code => 'ACQ_DISP',
1048 p_cons_rule_flag => 'N');
1049 EXCEPTION
1050 WHEN OTHERS
1051 THEN
1052 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' Generate entry XML error : ' || SQLERRM);
1053 FND_FILE.NEW_LINE(FND_FILE.LOG);
1054
1055 -- Write the appropriate information to the execution report
1056 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
1057 THEN
1058 fnd_log.STRING (fnd_log.level_error,
1059 g_pkg_name || '.' || l_api_name,
1060 gcs_utility_pkg.g_module_failure
1061 || ' XML Gen failed: '
1062 || SQLERRM
1063 || ' '
1064 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1065 );
1066 END IF;
1067 END;*/
1068
1069 BEGIN
1070 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' Raise Impact Analysis Event');
1071 FND_FILE.NEW_LINE(FND_FILE.LOG);
1072
1073 IF (l_orig_entry_id <> l_entry_id) THEN
1074
1075 IF (l_orig_entry_id IS NULL) THEN
1076 wf_event.addparametertolist (p_name => 'CHANGE_TYPE_CODE',
1077 p_value => 'NEW_ACQDISP',
1078 p_parameterlist => l_parameter_list
1079 );
1080 ELSE
1081 wf_event.addparametertolist (p_name => 'CHANGE_TYPE_CODE',
1082 p_value => 'ACQDISP_MODIFIED',
1083 p_parameterlist => l_parameter_list
1084 );
1085 END IF;
1086 wf_event.addparametertolist (p_name => 'ENTRY_ID',
1087 p_value => l_entry_id,
1088 p_parameterlist => l_parameter_list
1089 );
1090 wf_event.addparametertolist (p_name => 'ORIG_ENTRY_ID',
1091 p_value => l_orig_entry_id,
1092 p_parameterlist => l_parameter_list
1093 );
1094 wf_event.RAISE (p_event_name => l_event_name,
1095 p_event_key => l_event_key,
1096 p_parameters => l_parameter_list
1097 );
1098 END IF;
1099
1100 EXCEPTION
1101 WHEN OTHERS
1102 THEN
1103 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' Raise impact error : ' || SQLERRM);
1104 FND_FILE.NEW_LINE(FND_FILE.LOG);
1105
1106 -- Write the appropriate information to the execution report
1107 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
1108 THEN
1109 fnd_log.STRING (fnd_log.level_error,
1110 g_pkg_name || '.' || l_api_name,
1111 gcs_utility_pkg.g_module_failure
1112 || ' '
1113 || SQLERRM
1114 || ' '
1115 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1116 );
1117 END IF;
1118 END;
1119
1120 x_retcode := fnd_api.g_ret_sts_success;
1121
1122 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' EXIT');
1123 FND_FILE.NEW_LINE(FND_FILE.LOG);
1124 -- Write the appropriate information to the execution report
1125 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1126 THEN
1127 fnd_log.STRING (fnd_log.level_procedure,
1128 g_pkg_name || '.' || l_api_name,
1129 gcs_utility_pkg.g_module_success
1130 || ' '
1131 || l_api_name
1132 || '() '
1133 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1134 );
1135 END IF;
1136 END IF;
1137 EXCEPTION
1138 WHEN OTHERS
1139 THEN
1140 ROLLBACK TO gcs_ad_tb_import_start;
1141 fnd_message.set_name ('GCS', SQLERRM);
1142 x_errbuf := fnd_message.get;
1143 x_retcode := '2';
1144
1145 FND_FILE.PUT_LINE(FND_FILE.LOG, g_pkg_name || '.' || l_api_name || ' ERROR : ' || x_errbuf);
1146 FND_FILE.NEW_LINE(FND_FILE.LOG);
1147
1148 -- Write the appropriate information to the execution report
1149 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
1150 THEN
1151 fnd_log.STRING (fnd_log.level_error,
1152 g_pkg_name || '.' || l_api_name,
1153 gcs_utility_pkg.g_module_failure
1154 || ' '
1155 || x_errbuf
1156 || '() '
1157 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1158 );
1159 END IF;
1160 END import;
1161
1162 --
1163 -- Procedure
1164 -- undo_elim_adj
1165 -- Purpose
1166 -- An API to undo an elimination adjustment
1167 -- Arguments
1168 -- Notes
1169 --
1170 PROCEDURE undo_elim_adj (
1171 p_xns_id IN NUMBER,
1172 x_errbuf OUT NOCOPY VARCHAR2,
1173 x_retcode OUT NOCOPY VARCHAR2
1174 )
1175 IS
1176 cursor undo_c is
1177 SELECT decode(NVL(gcerd.run_detail_id, 0), 0, 'N', 'Y'), xns.assoc_entry_id
1178 FROM gcs_ad_transactions xns, gcs_cons_eng_run_dtls gcerd
1179 WHERE xns.ad_transaction_id = p_xns_id
1180 and gcerd.entry_id (+) = xns.assoc_entry_id;
1181
1182 l_processed_flag VARCHAR2 (1);
1183 l_entry_id NUMBER (15);
1184 l_event_name VARCHAR2 (100)
1185 := 'oracle.apps.gcs.transaction.acqdisp.update';
1186 l_event_key VARCHAR2 (100) := NULL;
1187 l_parameter_list wf_parameter_list_t;
1188 l_api_name VARCHAR2 (30) := 'UNDO_ELIM_ADJ';
1189 BEGIN
1190 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1191 THEN
1192 fnd_log.STRING (fnd_log.level_procedure,
1193 g_pkg_name || '.' || l_api_name,
1194 gcs_utility_pkg.g_module_enter
1195 || ' p_xns_id = '
1196 || p_xns_id
1197 || ' '
1198 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1199 );
1200 END IF;
1201
1202 open undo_c;
1203 fetch undo_c into l_processed_flag, l_entry_id;
1204 close undo_c;
1205
1206 if (l_processed_flag = 'Y') then
1207 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1208 THEN
1209 fnd_log.STRING (fnd_log.level_statement,
1210 g_pkg_name || '.' || l_api_name,
1211 'UPDATE gcs_entry_headers set disabled_flag = ''Y'' WHERE entry_id = '
1212 || l_entry_id
1213 );
1214 END IF;
1215 update gcs_entry_headers set disabled_flag = 'Y' where entry_id = l_entry_id;
1216 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1217 THEN
1218 fnd_log.STRING (fnd_log.level_statement,
1219 g_pkg_name || '.' || l_api_name,
1220 'UPDATE gcs_ad_transactions set assoc_entry_id = null, request_id = null WHERE ad_transaction_id = '
1221 || p_xns_id
1222 );
1223 END IF;
1224 update gcs_ad_transactions set assoc_entry_id = null, request_id = null where ad_transaction_id = p_xns_id;
1225
1226 begin
1227 wf_event.addparametertolist (p_name => 'CHANGE_TYPE_CODE',
1228 p_value => 'ACQDISP_UNDONE',
1229 p_parameterlist => l_parameter_list
1230 );
1231 wf_event.addparametertolist (p_name => 'ENTRY_ID',
1232 p_value => l_entry_id,
1233 p_parameterlist => l_parameter_list
1234 );
1235 wf_event.addparametertolist (p_name => 'ORIG_ENTRY_ID',
1236 p_value => NULL,
1237 p_parameterlist => l_parameter_list
1238 );
1239 EXCEPTION
1240 WHEN OTHERS THEN
1241 null;
1242 END;
1243
1244 elsif (l_processed_flag = 'N' and l_entry_id is not null) then
1245 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1246 THEN
1247 fnd_log.STRING (fnd_log.level_statement,
1248 g_pkg_name || '.' || l_api_name,
1249 'DELETE FROM gcs_entry_headers WHERE entry_id = '
1250 || l_entry_id
1251 );
1252 END IF;
1253 delete from gcs_entry_headers where entry_id = l_entry_id;
1254 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1255 THEN
1256 fnd_log.STRING (fnd_log.level_statement,
1257 g_pkg_name || '.' || l_api_name,
1258 'DELETE FROM gcs_entry_lines WHERE entry_id = '
1259 || l_entry_id
1260 );
1261 END IF;
1262 delete from gcs_entry_lines where entry_id = l_entry_id;
1263 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1264 THEN
1265 fnd_log.STRING (fnd_log.level_statement,
1266 g_pkg_name || '.' || l_api_name,
1267 'UPDATE gcs_ad_transactions set assoc_entry_id = null, request_id = null WHERE ad_transaction_id = '
1268 || p_xns_id
1269 );
1270 END IF;
1271 update gcs_ad_transactions set assoc_entry_id = null, request_id = null where ad_transaction_id = p_xns_id;
1272 ELSE
1273 IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
1274 THEN
1275 fnd_log.STRING (fnd_log.level_statement,
1276 g_pkg_name || '.' || l_api_name,
1277 'UPDATE gcs_ad_transactions set request_id = null WHERE ad_transaction_id = '
1278 || p_xns_id
1279 );
1280 END IF;
1281 update gcs_ad_transactions set request_id = null where ad_transaction_id = p_xns_id;
1282 end if;
1283
1284 IF fnd_log.g_current_runtime_level <= fnd_log.level_procedure
1285 THEN
1286 fnd_log.STRING (fnd_log.level_procedure,
1287 g_pkg_name || '.' || l_api_name,
1288 gcs_utility_pkg.g_module_success
1289 || ' '
1290 || l_api_name
1291 || '() '
1292 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1293 );
1294 END IF;
1295 EXCEPTION
1296 WHEN OTHERS
1297 THEN
1298 x_errbuf := SQLERRM;
1299 x_retcode := fnd_api.g_ret_sts_unexp_error;
1300
1301 -- Write the appropriate information to the execution report
1302 IF fnd_log.g_current_runtime_level <= fnd_log.level_error
1303 THEN
1304 fnd_log.STRING (fnd_log.level_error,
1305 g_pkg_name || '.' || l_api_name,
1306 gcs_utility_pkg.g_module_failure
1307 || ' '
1308 || x_errbuf
1309 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
1310 );
1311 END IF;
1312 END undo_elim_adj;
1313
1314 END gcs_ad_trialbalance_pkg;