[Home] [Help]
PACKAGE BODY: APPS.FV_CGAC_CRT_TAS
Source
1 PACKAGE BODY FV_CGAC_CRT_TAS AS
2 --$Header: FVCRTTASB.pls 120.26.12020000.2 2013/02/13 14:24:16 snama ship $
3 -- GLOBAL VARIABLES
4 g_coa_id Gl_Sets_Of_Books.chart_of_accounts_id%TYPE;
5 g_module_name VARCHAR2(100) := 'fv.plsql.FV_CGAC_CRT_TAS.';
6
7 -- TO RECORD EXCEPTIONS
8 g_error_code NUMBER;
9 g_error BOOLEAN;
10 g_error_message VARCHAR2(80);
11
12 vl_req_id number;
13
14 -- ------------------------------------------------------------------
15 -- Procedure purge_interface_table
16 -- ------------------------------------------------------------------
17 -- Procedure that is called from Main method to purge the interface
18 -- table fv_cgac_crt_tas_temp. This table contains data used
19 -- for creating the Create Annual Treasury Account Symbols report output.
20 -- ------------------------------------------------------------------
21
22 PROCEDURE purge_interface_table IS
23 l_module_name VARCHAR2(200) ;
24
25 BEGIN
26 l_module_name := g_module_name || 'purge_interface_table';
27
28 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
29 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START CGAC PURGE_INTERFACE_TABLE PROCESS.....');
30 END IF;
31
32 DELETE FROM fv_cgac_crt_tas_temp;
33 COMMIT;
34
35 EXCEPTION
36 --
37 WHEN NO_DATA_FOUND THEN
38 NULL;
39 --
40 WHEN OTHERS THEN
41 g_error_code := SQLCODE;
42 g_error_message := 'purge_interface_table '||SQLERRM;
43 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',g_error_message);
44
45 END purge_interface_table;
46
47
48
49 -- Procedure Main
50 -- -------------------------------------------------------------------------
51 -- Main procedure is called from the Create Treasury Account Symbols process
52 -- This procedure calls all the subsequent procedures.
53 -- ------------------------------------------------------------------
54 PROCEDURE Main( errbuf OUT NOCOPY VARCHAR2,
55 retcode OUT NOCOPY NUMBER,
56 p_ledger_id NUMBER,
57 p_tas_type VARCHAR2,
58 p_run_mode VARCHAR2,
59 p_copy_fiscal_year NUMBER,
60 p_tas_type_enable VARCHAR2,
61 p_tas_type_enable_tp VARCHAR2,
62 p_fund_prefix_indicator VARCHAR2,
63 p_fund_prefix VARCHAR2,
64 p_agency_identifier NUMBER,
65 p_agency_identifier_tp NUMBER,
66 p_report_id NUMBER,
67 p_attribute_set VARCHAR2,
68 p_output_format VARCHAR2) IS
69
70 l_module_name VARCHAR2(200) ;
71
72 -- CURSOR DEFINITIONS
73 CURSOR c_treasury_symbol IS
74 SELECT a.* FROM fv_treasury_symbols a, fv_facts_federal_accounts b
75 WHERE b.treasury_dept_code = nvl(p_agency_identifier,b.treasury_dept_code)
76 AND (a.time_frame = 'A' or a.time_frame = 'M')
77 AND a.end_year_avail = p_copy_fiscal_year
78 --AND a.auto_create = 'Y'
79 AND a.federal_acct_symbol_id = b.federal_acct_symbol_id
80 AND a.set_of_books_id = p_ledger_id;
81
82 CURSOR c_fund_value(p_treasury_symbol_id NUMBER) IS
83 SELECT * FROM fv_fund_parameters
84 WHERE treasury_symbol_id = p_treasury_symbol_id
85 AND set_of_books_id = p_ledger_id;
86 --AND auto_create='Y';
87
88 CURSOR c_budget_distribution_hdr(p_treasury_symbol_id NUMBER, p_fund_value VARCHAR2) IS
89 SELECT * FROM fv_budget_distribution_hdr
90 WHERE treasury_symbol_id = p_treasury_symbol_id
91 AND set_of_books_id = p_ledger_id
92 AND fund_value = p_fund_value;
93
94 CURSOR c_budget_distribution_dtl(p_distribution_hdr_id NUMBER, p_fund_value VARCHAR2) IS
95 SELECT * FROM fv_budget_distribution_dtl
96 WHERE distribution_id = p_distribution_hdr_id
97 AND fund_value = p_fund_value
98 AND set_of_books_id = p_ledger_id;
99
100 --BETC changes
101 CURSOR c_tp_treasury_symbol IS
102 SELECT * FROM fv_tp_treasury_symbols
103 WHERE end_year_avail = p_copy_fiscal_year
104 AND agency_id = nvl(p_agency_identifier_tp,agency_id)
105 AND (authority_duration_code = 'A' or authority_duration_code = 'M');
106 --AND auto_create_flag = 'Y';
107
108 -- RECORD DEFINITIONS
109 treasury_symbol_record FV_TREASURY_SYMBOLS%ROWTYPE;
110 fund_values_record FV_FUND_PARAMETERS%ROWTYPE;
111 bud_distr_hdr_record FV_BUDGET_DISTRIBUTION_HDR%ROWTYPE;
112 bud_distr_dtl_record FV_BUDGET_DISTRIBUTION_DTL%ROWTYPE;
113 cgac_crttas_record FV_CGAC_CRT_TAS_TEMP%ROWTYPE;
114
115 l_max_period GL_PERIODS.PERIOD_NUM%TYPE;
116 l_min_period GL_PERIODS.PERIOD_NUM%TYPE;
117 l_start_date GL_PERIODS.START_DATE%TYPE;
118 l_end_date GL_PERIODS.END_DATE%TYPE;
119 l_cancellation_date FV_TREASURY_SYMBOLS.CANCELLATION_DATE%TYPE;
120 l_ts_str VARCHAR2(100);
121 l_treasury_symbol_id FV_TREASURY_SYMBOLS.treasury_symbol_id%TYPE;
122 l_fund_value FV_FUND_PARAMETERS.FUND_VALUE%TYPE;
123 l_bud_distribution_id FV_BUDGET_DISTRIBUTION_HDR.distribution_id%TYPE;
124 l_bud_distribution_dtl_id FV_BUDGET_DISTRIBUTION_DTL.distribution_dtl_id%TYPE;
125 l_treasury_acct_code FV_FACTS_FEDERAL_ACCOUNTS.treasury_acct_code%TYPE;
126 l_treasury_dept_code FV_FACTS_FEDERAL_ACCOUNTS.treasury_dept_code%TYPE;
127
128 v_bal_seg_name VARCHAR2(20);
129 v_acc_seg_name VARCHAR2(20);
130 v_fyr_segment_name VARCHAR2(20);
131 len NUMBER;
132
133 l_bud_dtl_cursor INTEGER;
134 l_exec_ret INTEGER;
135 l_user_id INTEGER;
136 l_update_query VARCHAR2(2000);
137 v_bfy_Segment VARCHAR2(30);
138
139 x_appcol_name VARCHAR2(100);
140 x_seg_name VARCHAR2(100);
141 x_prompt VARCHAR2(100);
142 x_value_set_name VARCHAR2(100);
143 x_storage_value VARCHAR2(100);
144
145 tas_duplicate_cnt NUMBER;
146 tas_duplicate_cnt1 NUMBER;
147 fund_duplicate_cnt NUMBER;
148 fund_duplicate_cnt1 NUMBER;
149
150 --BETC enhancements
151 l_betc_map_cnt NUMBER DEFAULT 0;
152 tp_treasury_symbol_record FV_TP_TREASURY_SYMBOLS%ROWTYPE;
153 l_tp_ts_str VARCHAR2(100);
154 tp_tas_duplicate_cnt NUMBER;
155 tp_tas_duplicate_cnt1 NUMBER;
156 l_tp_treasury_symbol FV_TP_TREASURY_SYMBOLS.treasury_symbol%TYPE;
157 l_auto_create_flag_betc FV_TREASURY_SYMBOLS.auto_create_flag_betc%TYPE;
158 l_auto_create_flag FV_TP_TREASURY_SYMBOLS.auto_create_flag%TYPE;
159 l_auto_create_flag_tas FV_TREASURY_SYMBOLS.auto_create%TYPE;
160 l_auto_create_fund FV_FUND_PARAMETERS.auto_create%TYPE;
161 l_auto_create_dist FV_BUDGET_DISTRIBUTION_HDR.auto_create_flag%TYPE;
162
163 bal_seg_suffix NUMBER;
164 bfy_seg_suffix NUMBER;
165 v_default_segment VARCHAR2(1000);
166
167 orig_established_fiscal_yr FV_TREASURY_SYMBOLS.established_fiscal_yr%TYPE;
168 orig_end_year_avail FV_TREASURY_SYMBOLS.end_year_avail%TYPE;
169
170 l_phase VARCHAR2(80);
171 l_status VARCHAR2(80);
172 l_dev_status VARCHAR2(80);
173 l_dev_phase VARCHAR2(80);
174 l_message VARCHAR2(80);
175 l_call_status BOOLEAN;
176 l_tas_type VARCHAR2(20);
177 l_fund_prefix_indicator VARCHAR2(10);
178 l_max_fund_value_size fnd_flex_value_sets.maximum_size%TYPE;
179 l_flex_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%type;
180
181 BEGIN
182 l_module_name := g_module_name || 'Main ';
183
184 -- Assign initial values
185 retcode := 0;
186 errbuf := NULL;
187
188 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
189 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Start CGAC Create Process');
190 END IF;
191 /* Get delimiter fo accounting flexfield */
192 BEGIN
193 select concatenated_segment_delimiter
194 into l_flex_delimiter
195 from fnd_id_flex_structures flex, gl_ledgers l
196 where l.chart_of_accounts_id = flex.id_flex_num
197 and l.ledger_id = p_ledger_id
198 and flex.id_flex_code = 'GL#'
199 and flex.application_id = 101;
200
201 EXCEPTION
202 WHEN OTHERS THEN
203 errbuf := g_error_message|| SQLERRM;
204 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, errbuf);
205 retcode := -1;
206 RETURN;
207 END;
208
209 purge_interface_table;
210
211 IF g_error_code IS NOT NULL THEN
212 retcode := g_error_code;
213 errbuf := g_error_message;
214 END IF;
215 l_user_id := Fnd_Global.user_id;
216
217 BEGIN
218 SELECT chart_of_accounts_id
219 INTO g_coa_id
220 FROM gl_ledgers_public_v
221 WHERE ledger_id = p_ledger_id;
222 EXCEPTION
223 WHEN NO_DATA_FOUND THEN
224 errbuf := 'Error in Main: Chart of Accounts are not defined';
225 retcode := -1;
226 ROLLBACK;
227 RETURN;
228 END;
229
230 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
231 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Chart of accounts Id is.....'||g_coa_id);
232 END IF;
233
234 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
235 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Processing treasury symbols');
236 END IF;
237
238 IF (p_tas_type = 'AGENCY_TAS' OR p_tas_type = 'BOTH') THEN
239
240 FOR treasury_symbol_record IN c_treasury_symbol LOOP
241 l_treasury_symbol_id := treasury_symbol_record.treasury_symbol_id ;
242 cgac_crttas_record := NULL;
243
244 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
245 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Processing treasury symbol: '||treasury_symbol_record.treasury_symbol);
246 END IF;
247
248 BEGIN
249 SELECT treasury_dept_code, treasury_acct_code into l_treasury_dept_code, l_treasury_acct_code
250 FROM fv_facts_federal_accounts b, fv_treasury_symbols a
251 WHERE a.treasury_symbol_id = l_treasury_symbol_id
252 AND a.federal_acct_symbol_id = b.federal_acct_symbol_id
253 AND a.set_of_books_id = p_ledger_id;
254 EXCEPTION
255 WHEN NO_DATA_FOUND THEN
256 errbuf := 'Error in Main: Error in retrieving treasury_dept_code, treasury_acct_code.'|| SQLERRM;
257 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,errbuf);
258 retcode :=-1;
259 ROLLBACK;
260 RETURN;
261 END;
262
263 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
264 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' l_treasury_dept_code = '
265 ||l_treasury_dept_code|| ' l_treasury_acct_code = '||l_treasury_acct_code);
266 END IF;
267
268 -- Insert Original Treasury Symbol values into interface record
269 cgac_crttas_record.original_treasury_symbol := treasury_symbol_record.treasury_symbol;
270 cgac_crttas_record.original_treasury_symbol_id := l_treasury_symbol_id;
271 cgac_crttas_record.set_of_books_id := p_ledger_id;
272 cgac_crttas_record.AID := treasury_symbol_record.department_id;
273 -- Bug 10247675
274 cgac_crttas_record.MAIN := l_treasury_acct_code;
275 cgac_crttas_record.SP := treasury_symbol_record.sub_level_prefix_code;
276 cgac_crttas_record.ATA := treasury_symbol_record.dept_transfer;
277 cgac_crttas_record.EPOA := treasury_symbol_record.end_year_avail;
278 cgac_crttas_record.BPOA := treasury_symbol_record.established_fiscal_yr;
279 cgac_crttas_record.SUB := treasury_symbol_record.tafs_sub_acct;
280
281 BEGIN
282 SELECT auto_create INTO l_auto_create_flag_tas FROM fv_treasury_symbols
283 WHERE set_of_books_id = p_ledger_id
284 AND treasury_symbol_id = l_treasury_symbol_id;
285 EXCEPTION
286 WHEN NO_DATA_FOUND THEN
287 l_auto_create_flag_tas := NULL;
288 END;
289
290 cgac_crttas_record.auto_tas := l_auto_create_flag_tas;
291 -- Bug 10247675
292 IF l_auto_create_flag_tas IS NULL OR l_auto_create_flag_tas = 'N' THEN
293 INSERT INTO fv_cgac_crt_tas_temp values cgac_crttas_record;
294 END IF;
295
296 -- Check if Auto create checkbox on TAS/BETC mapping form is checked
297 BEGIN
298 SELECT auto_create_flag_betc into l_auto_create_flag_betc FROM fv_treasury_symbols where
299 treasury_symbol_id = cgac_crttas_record.original_treasury_symbol_id
300 AND set_of_books_id = p_ledger_id;
301 EXCEPTION
302 WHEN NO_DATA_FOUND THEN
303 l_auto_create_flag_betc := NULL;
304 END;
305 cgac_crttas_record.auto_betc := l_auto_create_flag_betc;
306
307 IF l_auto_create_flag_tas IS NOT NULL and l_auto_create_flag_tas = 'Y' THEN
308
309 --assigning the new values
310 SELECT fv_treasury_symbols_s.nextval INTO treasury_symbol_record.treasury_symbol_id from dual;
311
312 --treasury_symbol_record.established_fiscal_yr := p_create_fiscal_year;
313 orig_established_fiscal_yr := treasury_symbol_record.established_fiscal_yr;
314 orig_end_year_avail := treasury_symbol_record.end_year_avail;
315 treasury_symbol_record.established_fiscal_yr := orig_end_year_avail + 1;
316 treasury_symbol_record.end_year_avail := (orig_end_year_avail - orig_established_fiscal_yr)
317 + treasury_symbol_record.established_fiscal_yr;
318
319 cgac_crttas_record.NEW_BPOA := treasury_symbol_record.established_fiscal_yr;
320 cgac_crttas_record.NEW_EPOA := treasury_symbol_record.end_year_avail;
321
322 l_start_date := to_date('01-10-'||to_char(treasury_symbol_record.ESTABLISHED_FISCAL_YR-1),'dd-mm-yyyy');
323 treasury_symbol_record.start_date := l_start_date;
324 l_end_date := to_date('30-09-'||to_char(treasury_symbol_record.end_year_avail),'dd-mm-yyyy');
325 treasury_symbol_record.expiration_date := l_end_date;
326 l_cancellation_date := add_months(treasury_symbol_record.expiration_date,60);
327
328 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
329 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Cancellation Date = '
330 ||TO_CHAR(l_cancellation_date));
331 END IF;
332 treasury_symbol_record.cancellation_date := l_cancellation_date;
333 treasury_symbol_record.SF224_QTR1_OUTLAY := NULL;
334 treasury_symbol_record.SF224_QTR2_OUTLAY := NULL;
335 treasury_symbol_record.SF224_QTR3_OUTLAY := NULL;
336 treasury_symbol_record.SF224_QTR4_OUTLAY := NULL;
337 treasury_symbol_record.PRECLOSING_UNEXPENDED_AMT := NULL;
338 treasury_symbol_record.CREATION_DATE := sysdate;
339 treasury_symbol_record.LAST_UPDATE_DATE := sysdate;
340 treasury_symbol_record.CREATED_BY := l_user_id;
341 treasury_symbol_record.LAST_UPDATED_BY := l_user_id;
342
343 ---deriving the new treasury symbol
344 l_ts_str := null;
345 IF (treasury_symbol_record.sub_level_prefix_code IS NOT NULL) THEN
346 l_ts_str := '(' ||treasury_symbol_record.sub_level_prefix_code||')';
347 END IF;
348
349 IF (treasury_symbol_record.dept_transfer IS NOT NULL) THEN
350 l_ts_str := l_ts_str || treasury_symbol_record.dept_transfer||'-';
351 END IF;
352
353 IF (l_treasury_dept_code IS NOT NULL) THEN
354 l_ts_str := l_ts_str || l_treasury_dept_code;
355 END IF;
356
357 IF (treasury_symbol_record.time_frame = 'X' AND treasury_symbol_record.availability_type_code IS NULL) THEN
358 NULL;
359 ELSIF (treasury_symbol_record.time_frame = 'X' AND treasury_symbol_record.availability_type_code ='X') THEN
360 l_ts_str := l_ts_str || 'X';
361 ELSIF (treasury_symbol_record.time_frame = 'X' AND treasury_symbol_record.availability_type_code ='F') THEN
362 l_ts_str := l_ts_str || 'F';
363 ELSIF (treasury_symbol_record.time_frame = 'X' AND treasury_symbol_record.availability_type_code ='A') THEN
364 l_ts_str := l_ts_str || 'A';
365 ELSIF (treasury_symbol_record.time_frame = 'X' AND treasury_symbol_record.availability_type_code ='M') THEN
366 l_ts_str := l_ts_str || 'M';
367 ELSIF (treasury_symbol_record.time_frame = 'A' ) THEN
368 l_ts_str := l_ts_str || SUBSTR(treasury_symbol_record.end_year_avail,3);
369 ELSIF (treasury_symbol_record.time_frame = 'M' ) THEN
370 -- Bug 12536017
371 l_ts_str := l_ts_str || SUBSTR(treasury_symbol_record.established_fiscal_yr,3)||'/'||SUBSTR(treasury_symbol_record.end_year_avail,3);
372 END IF;
373
374 IF (l_treasury_acct_code IS NOT NULL) THEN
375 l_ts_str := l_ts_str||l_treasury_acct_code;
376 END IF;
377
378 IF (ltrim(treasury_symbol_record.tafs_sub_acct,0) IS NOT NULL) THEN
379 l_ts_str := l_ts_str||l_flex_delimiter||LTRIM(treasury_symbol_record.tafs_sub_acct,0);
380 END IF;
381
382 treasury_symbol_record.treasury_symbol := l_ts_str;
383 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
384 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'New Treasury Symbol is '
385 ||l_ts_str);
386 END IF;
387
388 -- Insert New Treasury Symbol values into interface record
389 cgac_crttas_record.treasury_symbol_id := treasury_symbol_record.treasury_symbol_id;
390 cgac_crttas_record.treasury_symbol := treasury_symbol_record.treasury_symbol;
391
392 select count(*)into tas_duplicate_cnt from fv_treasury_symbols where
393 treasury_symbol = treasury_symbol_record.treasury_symbol
394 or (sub_level_prefix_code = treasury_symbol_record.sub_level_prefix_code and
395 dept_transfer = treasury_symbol_record.dept_transfer and
396 time_frame = treasury_symbol_record.time_frame and
397 established_fiscal_yr = treasury_symbol_record.established_fiscal_yr and
398 end_year_avail = treasury_symbol_record.end_year_avail and
399 availability_type_code = treasury_symbol_record.availability_type_code and
400 tafs_sub_acct = treasury_symbol_record.tafs_sub_acct);
401
402 select count(*)into tas_duplicate_cnt1 from fv_cgac_crt_tas_temp where
403 treasury_symbol = treasury_symbol_record.treasury_symbol;
404
405 IF tas_duplicate_cnt > 0 OR tas_duplicate_cnt1 > 0 THEN
406 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Duplicate TAS: '||l_ts_str);
407 errbuf := 'New Duplicate Treasury Account Symbol';
408 cgac_crttas_record.error_message := errbuf;
409 INSERT INTO fv_cgac_crt_tas_temp values cgac_crttas_record;
410 GOTO end_tas;
411 END IF;
412
413 IF p_run_mode = 'F' then
414 -- Commented for GSCC error
415 -- INSERT INTO fv_treasury_symbols VALUES treasury_symbol_record
416
417 INSERT INTO fv_treasury_symbols(TREASURY_SYMBOL_ID,
418 TREASURY_SYMBOL,
419 SET_OF_BOOKS_ID,
420 SF224_TYPE_CODE,
421 FUND_GROUP_CODE,
422 TIME_FRAME,
423 ESTABLISHED_FISCAL_YR,
424 DEPARTMENT_ID,
425 BUREAU_ID,
426 LAST_UPDATE_DATE,
427 LAST_UPDATED_BY,
428 LAST_UPDATE_LOGIN,
429 CREATION_DATE,
430 CREATED_BY,
431 EXPIRATION_DATE,
432 CANCELLATION_DATE,
433 RESOURCE_TYPE,
434 PRECLOSING_UNEXPENDED_AMT,
435 CLOSE_REQUISITIONS,
436 YEARS_AVAILABLE,
437 BUSINESS_LINE,
438 ATTRIBUTE1,
439 ATTRIBUTE2,
440 ATTRIBUTE3,
441 ATTRIBUTE4,
442 ATTRIBUTE5,
443 ATTRIBUTE6,
444 ATTRIBUTE7,
445 ATTRIBUTE8,
446 ATTRIBUTE9,
447 ATTRIBUTE10,
448 ATTRIBUTE11,
449 ATTRIBUTE12,
450 ATTRIBUTE13,
451 ATTRIBUTE14,
452 ATTRIBUTE15,
453 ATTRIBUTE_CATEGORY,
454 ORG_ID,
455 FEDERAL_ACCT_SYMBOL_ID,
456 DEPT_TRANSFER,
457 TAFS_SUB_ACCT,
458 TAFS_SPLIT_CODE,
459 TAFS_SPLIT_NAME,
460 PUBLIC_LAW_CODE,
461 TS_FLAG,
462 DEF_INDEF_FLAG,
463 SF224_QTR1_OUTLAY,
464 SF224_QTR2_OUTLAY,
465 SF224_QTR3_OUTLAY,
466 SF224_QTR4_OUTLAY,
467 CUST_NON_CUST,
468 SUB_LEVEL_PREFIX_CODE,
469 END_YEAR_AVAIL,
470 AVAILABILITY_TYPE_CODE,
471 FUND_TYPE,
472 FACTS_REPORTABLE_INDICATOR,
473 START_DATE,
474 RECEIPT_ACCOUNT_INDICATOR,
475 AUTO_CREATE,
476 --PRECLOSING_SUBCLASS_AMT,
477 --SUBCLASS_CODE,
478 FINANCING_ACCOUNT,
479 NO_PYA_ACCT_FLAG,
480 AUTO_CREATE_FLAG_BETC,
481 GTAS_REPORTABLE_INDICATOR ) VALUES (
482 treasury_symbol_record.TREASURY_SYMBOL_ID,
483 treasury_symbol_record.TREASURY_SYMBOL,
484 treasury_symbol_record.SET_OF_BOOKS_ID,
485 treasury_symbol_record.SF224_TYPE_CODE,
486 treasury_symbol_record.FUND_GROUP_CODE,
487 treasury_symbol_record.TIME_FRAME,
488 treasury_symbol_record.ESTABLISHED_FISCAL_YR,
489 treasury_symbol_record.DEPARTMENT_ID,
490 treasury_symbol_record.BUREAU_ID,
491 treasury_symbol_record.LAST_UPDATE_DATE,
492 treasury_symbol_record.LAST_UPDATED_BY,
493 treasury_symbol_record.LAST_UPDATE_LOGIN,
494 treasury_symbol_record.CREATION_DATE,
495 treasury_symbol_record.CREATED_BY,
496 treasury_symbol_record.EXPIRATION_DATE,
497 treasury_symbol_record.CANCELLATION_DATE,
498 treasury_symbol_record.RESOURCE_TYPE,
499 treasury_symbol_record.PRECLOSING_UNEXPENDED_AMT,
500 treasury_symbol_record.CLOSE_REQUISITIONS,
501 treasury_symbol_record.YEARS_AVAILABLE,
502 treasury_symbol_record.BUSINESS_LINE,
503 treasury_symbol_record.ATTRIBUTE1,
504 treasury_symbol_record.ATTRIBUTE2,
505 treasury_symbol_record.ATTRIBUTE3,
506 treasury_symbol_record.ATTRIBUTE4,
507 treasury_symbol_record.ATTRIBUTE5,
508 treasury_symbol_record.ATTRIBUTE6,
509 treasury_symbol_record.ATTRIBUTE7,
510 treasury_symbol_record.ATTRIBUTE8,
511 treasury_symbol_record.ATTRIBUTE9,
512 treasury_symbol_record.ATTRIBUTE10,
513 treasury_symbol_record.ATTRIBUTE11,
514 treasury_symbol_record.ATTRIBUTE12,
515 treasury_symbol_record.ATTRIBUTE13,
516 treasury_symbol_record.ATTRIBUTE14,
517 treasury_symbol_record.ATTRIBUTE15,
518 treasury_symbol_record.ATTRIBUTE_CATEGORY,
519 treasury_symbol_record.ORG_ID,
520 treasury_symbol_record.FEDERAL_ACCT_SYMBOL_ID,
521 treasury_symbol_record.DEPT_TRANSFER,
522 treasury_symbol_record.TAFS_SUB_ACCT,
523 treasury_symbol_record.TAFS_SPLIT_CODE,
524 treasury_symbol_record.TAFS_SPLIT_NAME,
525 treasury_symbol_record.PUBLIC_LAW_CODE,
526 treasury_symbol_record.TS_FLAG,
527 treasury_symbol_record.DEF_INDEF_FLAG,
528 treasury_symbol_record.SF224_QTR1_OUTLAY,
529 treasury_symbol_record.SF224_QTR2_OUTLAY,
530 treasury_symbol_record.SF224_QTR3_OUTLAY,
531 treasury_symbol_record.SF224_QTR4_OUTLAY,
532 treasury_symbol_record.CUST_NON_CUST,
533 treasury_symbol_record.SUB_LEVEL_PREFIX_CODE,
534 treasury_symbol_record.END_YEAR_AVAIL,
535 treasury_symbol_record.AVAILABILITY_TYPE_CODE,
536 treasury_symbol_record.FUND_TYPE,
537 treasury_symbol_record.FACTS_REPORTABLE_INDICATOR,
538 treasury_symbol_record.START_DATE,
539 treasury_symbol_record.RECEIPT_ACCOUNT_INDICATOR,
540 treasury_symbol_record.AUTO_CREATE,
541 --treasury_symbol_record.PRECLOSING_SUBCLASS_AMT,
542 --treasury_symbol_record.SUBCLASS_CODE,
543 treasury_symbol_record.FINANCING_ACCOUNT,
544 treasury_symbol_record.NO_PYA_ACCT_FLAG,
545 treasury_symbol_record.AUTO_CREATE_FLAG_BETC,
546 treasury_symbol_record.GTAS_REPORTABLE_INDICATOR ) ;
547 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
548 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Inserted new treasury symbol');
549 END IF;
550
551 IF l_auto_create_flag_betc ='Y' THEN
552
553 -- Check whether the original TAS has any BETC mappings assigned
554 SELECT COUNT(*) INTO l_betc_map_cnt FROM fv_tas_betc_map WHERE
555 treasury_symbol_id = cgac_crttas_record.original_treasury_symbol_id
556 AND set_of_books_id = p_ledger_id
557 AND (tas_type is null or tas_type='A');
558
559 --Insert BETC mapping for the newly created TAS
560 IF l_betc_map_cnt > 0 THEN
561 INSERT INTO fv_tas_betc_map(
562 treasury_symbol_id,
563 betc_code,
564 set_of_books_id,
565 created_by,
566 last_update_date,
567 last_updated_by,
568 creation_date,
569 last_update_login,
570 tas_type)
571 SELECT
572 treasury_symbol_record.treasury_symbol_id,
573 betc_code,
574 p_ledger_id,
575 l_user_id,
576 sysdate,
577 l_user_id,
578 sysdate,
579 l_user_id,
580 'A'
581 FROM fv_tas_betc_map
582 WHERE treasury_symbol_id = cgac_crttas_record.original_treasury_symbol_id
583 AND set_of_books_id = p_ledger_id;
584 END IF;
585 END IF;
586 END IF;
587
588 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
589 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Processing funds');
590 END IF;
591
592 FOR fund_values_record in c_fund_value(l_treasury_symbol_id) loop
593 l_fund_value := fund_values_record.fund_value;
594 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
595 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' Processing fund value: '||l_fund_value);
596 END IF;
597
598 -- Insert old fund value into interface record
599 cgac_crttas_record.original_fund_value := fund_values_record.fund_value;
600 cgac_crttas_record.original_fund_parameter_id := fund_values_record.fund_parameter_id;
601
602 -- Insert default values for new fund into the interface record
603 cgac_crttas_record.fund_value := NULL;
604 cgac_crttas_record.fund_parameter_id := NULL;
605 -- Bug 1024675
606 cgac_crttas_record.error_message := NULL;
607
608 cgac_crttas_record.auto_fund := NULL;
609 cgac_crttas_record.description := NULL;
610 cgac_crttas_record.default_segment := NULL;
611 cgac_crttas_record.auto_dist := NULL;
612
613 BEGIN
614 SELECT auto_create into l_auto_create_fund FROM fv_fund_parameters
615 WHERE treasury_symbol_id = l_treasury_symbol_id
616 AND fund_value = l_fund_value
617 AND set_of_books_id = p_ledger_id;
618 EXCEPTION
619 WHEN NO_DATA_FOUND THEN
620 l_auto_create_fund := NULL;
621 END;
622
623 cgac_crttas_record.auto_fund := l_auto_create_fund;
624
625 IF l_auto_create_fund IS NOT NULL and l_auto_create_fund = 'N' THEN
626 INSERT INTO fv_cgac_crt_tas_temp values cgac_crttas_record;
627 END IF;
628
629 IF l_auto_create_fund IS NOT NULL and l_auto_create_fund = 'Y' THEN
630 IF (fund_values_record.auto_create_fund_value is not null)then
631 fund_values_record.fund_value := fund_values_record.auto_create_fund_value;
632 ELSE
633 len := length(p_fund_prefix);
634 IF p_fund_prefix_indicator = 'I' THEN
635 fund_values_record.fund_value := p_fund_prefix || fund_values_record.fund_value;
636 ELSE
637 fund_values_record.fund_value := p_fund_prefix|| substr(fund_values_record.fund_value,len+1);
638 END IF;
639 END IF; --IF (fund_values_record.auto_create_fund_value is not null)then
640
641 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
642 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'New fund value: '||fund_values_record.fund_value);
643 END IF;
644
645 --Insert new fund records into fv_fund_parameters table and interface table only if a new fund is created.
646 IF fund_values_record.fund_value IS NOT NULL THEN
647
648 --Insert the fund value into the balancing segment value set
649 -- Getting the Account and Balancing segments' application column names
650 BEGIN
651 FV_UTILITY.get_segment_col_names(g_coa_id,
652 v_acc_seg_name,
653 v_bal_seg_name,
654 g_error,
655 g_error_message);
656 EXCEPTION
657 WHEN OTHERS THEN
658 errbuf := g_error_message || SQLERRM;
659 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, errbuf);
660 retcode := -1;
661 ROLLBACK;
662 RETURN;
663 END;
664
665 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
666 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Balancing segment: '||v_bal_seg_name);
667 END IF;
668
669 --Fetch the value set name for the balancing segment
670 BEGIN
671 g_error := FND_FLEX_APIS.get_segment_info(101,
672 'GL#',
673 g_coa_id,
674 substr(v_bal_seg_name,8),
675 x_appcol_name ,
676 x_seg_name ,
677 x_prompt,
678 x_value_set_name ) ;
679 EXCEPTION
680 WHEN OTHERS THEN
681 errbuf := g_error_message|| SQLERRM;
682 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, errbuf);
683 retcode := -1;
684 ROLLBACK;
685 RETURN;
686 END;
687
688 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
689 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Value set: '||x_value_set_name);
690 END IF;
691
692 --Bug13542164
693 --Get the maximum length of the value set being used for the fund value
694 BEGIN
695 SELECT maximum_size
696 INTO l_max_fund_value_size
697 FROM fnd_flex_value_sets
698 WHERE flex_value_set_name = x_value_set_name;
699
700 EXCEPTION WHEN OTHERS THEN
701 errbuf := g_error_message|| SQLERRM;
702 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, errbuf);
703 retcode := -1;
704 ROLLBACK;
705 RETURN;
706 END;
707
708 -- Maximum fund length
709 --IF length(fund_values_record.fund_value)> 12 THEN
710 IF length(fund_values_record.fund_value)> l_max_fund_value_size THEN
711 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
712 'Length of fund '||fund_values_record.fund_value|| ' is greater than value set length.
713 Fund length should not be greater than: '|| l_max_fund_value_size);
714 errbuf := 'New fund Exceeds Maximum Fund Length' ;
715 cgac_crttas_record.error_message := errbuf;
716 INSERT INTO fv_cgac_crt_tas_temp values cgac_crttas_record;
717 GOTO end_fund;
718 END IF;
719 -- Duplicate fund value
720 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'fund_values_record.fund_value '||fund_values_record.fund_value);
721 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'x_value_set_name'||x_value_set_name);
722
723 -- Insert new values for new fund into the interface record
724 cgac_crttas_record.fund_value := fund_values_record.fund_value;
725 cgac_crttas_record.original_fund_parameter_id := fund_values_record.fund_parameter_id;
726
727 select count(*)into fund_duplicate_cnt from fnd_flex_values val, fnd_flex_value_sets vset
728 where val.flex_value = fund_values_record.fund_value and val.flex_value_set_id = vset.flex_value_set_id
729 and vset.flex_value_set_name= x_value_set_name;
730
731 select count(*) into fund_duplicate_cnt1 from fv_cgac_crt_tas_temp where
732 fund_value = fund_values_record.fund_value;
733
734 IF (fund_duplicate_cnt IS NOT NULL AND fund_duplicate_cnt > 0) OR (fund_duplicate_cnt1 IS NOT NULL AND fund_duplicate_cnt1 > 0) THEN
735 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Duplicate Fund: '||fund_values_record.fund_value);
736 errbuf := 'New Duplicate Fund Value';
737 cgac_crttas_record.error_message := errbuf;
738 INSERT INTO fv_cgac_crt_tas_temp values cgac_crttas_record;
739 -- Bug 10247675
740 -- All duplicate funds should display the error message
741 UPDATE fv_cgac_crt_tas_temp SET error_message = 'New Duplicate Fund Value'
742 WHERE fund_value = fund_values_record.fund_value;
743 GOTO end_fund;
744 END IF;
745 --create fund in the balancing segment
746 IF p_run_mode = 'F' THEN
747 BEGIN
748 fnd_flex_Val_api.create_independent_vset_value(x_value_set_name,fund_values_record.fund_value,fund_values_record.fund_value,'Y',sysdate,null,'N',null,null,x_storage_value);
749 EXCEPTION
750 WHEN OTHERS THEN
751 errbuf := 'Error in inserting fund into value set '||x_value_set_name|| SQLERRM;
752 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Error in inserting fund into value set '||x_value_set_name|| SQLERRM);
753 retcode := -1;
754 ROLLBACK;
755 RETURN;
756 END;
757
758 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
759 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Fund inserted into balancing segment valueset ');
760 END IF;
761 END IF;
762
763 -- assigning the values to be inserted into the tables
764 fund_values_record.treasury_symbol_id := treasury_symbol_record.treasury_symbol_id;
765
766 SELECT fv_fund_parameters_s.nextval INTO fund_values_record.fund_parameter_id FROM dual;
767
768 fund_values_record.auto_create_fund_value := NULL;
769 fund_values_record.creation_date := SYSDATE;
770 fund_values_record.last_update_date := SYSDATE;
771 fund_values_record.created_by := l_user_id;
772 fund_values_record.last_updated_by := l_user_id;
773
774 -- Bug 13444351: Copy established fiscal year, expiration
775 -- and cancellation dates from the new treasury account symbol
776 fund_values_record.established_fiscal_yr := treasury_symbol_record.established_fiscal_yr;
777 fund_values_record.fund_expire_date := treasury_symbol_record.expiration_date;
778 fund_values_record.fund_cancel_date := treasury_symbol_record.cancellation_date;
779
780 IF p_run_mode = 'F' THEN
781 INSERT INTO fv_fund_parameters VALUES fund_values_record;
782 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
783 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Inserted new fund value');
784 END IF;
785 END IF;
786
787 BEGIN
788 SELECT auto_create_flag into l_auto_create_dist FROM fv_budget_distribution_hdr
789 WHERE treasury_symbol_id = l_treasury_symbol_id
790 AND set_of_books_id = p_ledger_id
791 AND fund_value = l_fund_value;
792 cgac_crttas_record.auto_dist := l_auto_create_dist;
793 EXCEPTION
794 WHEN NO_DATA_FOUND THEN
795 l_auto_create_dist := NULL;
796 END;
797 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_auto_create_dist'||l_auto_create_dist);
798
799 -- Inserting data into the interface
800 INSERT INTO fv_cgac_crt_tas_temp values cgac_crttas_record;
801 IF (SQL%rowcount >0) THEN
802 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,'Inserted into interface record') ;
803 END IF;
804
805 IF l_auto_create_dist is NOT NULL and l_auto_create_dist = 'Y' THEN
806 FOR budget_distribution_hdr_record IN c_budget_distribution_hdr(l_treasury_symbol_id,l_fund_value) LOOP
807 l_bud_distribution_id := budget_distribution_hdr_record.distribution_id;
808 -- Bug 1024675
809 cgac_crttas_record.error_message := NULL;
810 cgac_crttas_record.default_segment := NULL;
811
812 SELECT FV_BE_TRX_HDRS_S.nextval INTO budget_distribution_hdr_record.distribution_id FROM dual;
813 budget_distribution_hdr_record.treasury_symbol_id := treasury_symbol_record.treasury_symbol_id;
814 budget_distribution_hdr_record.fund_value := fund_values_record.fund_value;
815 budget_distribution_hdr_record.creation_date := SYSDATE;
816 budget_distribution_hdr_record.last_update_date := SYSDATE;
817 budget_distribution_hdr_record.created_by := l_user_id;
818 budget_distribution_hdr_record.last_updated_by := l_user_id;
819
820 IF p_run_mode = 'F' THEN
821 -- Commented for GSCC error
822 -- INSERT INTO fv_budget_distribution_hdr VALUES budget_distribution_hdr_record;
823 INSERT INTO fv_budget_distribution_hdr
824 (DISTRIBUTION_ID,
825 FUND_VALUE,
826 TREASURY_SYMBOL,
827 SET_OF_BOOKS_ID,
828 LAST_UPDATE_DATE,
829 LAST_UPDATED_BY,
830 CREATION_DATE,
831 CREATED_BY,
832 LAST_UPDATE_LOGIN,
833 ATTRIBUTE1,
834 ATTRIBUTE2,
835 ATTRIBUTE3,
836 ATTRIBUTE4,
837 ATTRIBUTE5,
838 ATTRIBUTE6,
839 ATTRIBUTE7,
840 ATTRIBUTE8,
841 ATTRIBUTE9,
842 ATTRIBUTE10,
843 ATTRIBUTE11,
844 ATTRIBUTE12,
845 ATTRIBUTE13,
846 ATTRIBUTE14,
847 ATTRIBUTE15,
848 ATTRIBUTE_CATEGORY,
849 ORG_ID,
850 FACTS_PRGM_SEGMENT,
851 TREASURY_SYMBOL_ID,
852 FREEZE_DEFINITION_FLAG,
853 AUTO_CREATE_FLAG)
854 VALUES (
855 budget_distribution_hdr_record.DISTRIBUTION_ID,
856 budget_distribution_hdr_record.FUND_VALUE,
857 budget_distribution_hdr_record.TREASURY_SYMBOL,
858 budget_distribution_hdr_record.SET_OF_BOOKS_ID,
859 budget_distribution_hdr_record.LAST_UPDATE_DATE,
860 budget_distribution_hdr_record.LAST_UPDATED_BY,
861 budget_distribution_hdr_record.CREATION_DATE,
862 budget_distribution_hdr_record.CREATED_BY,
863 budget_distribution_hdr_record.LAST_UPDATE_LOGIN,
864 budget_distribution_hdr_record.ATTRIBUTE1,
865 budget_distribution_hdr_record.ATTRIBUTE2,
866 budget_distribution_hdr_record.ATTRIBUTE3,
867 budget_distribution_hdr_record.ATTRIBUTE4,
868 budget_distribution_hdr_record.ATTRIBUTE5,
869 budget_distribution_hdr_record.ATTRIBUTE6,
870 budget_distribution_hdr_record.ATTRIBUTE7,
871 budget_distribution_hdr_record.ATTRIBUTE8,
872 budget_distribution_hdr_record.ATTRIBUTE9,
873 budget_distribution_hdr_record.ATTRIBUTE10,
874 budget_distribution_hdr_record.ATTRIBUTE11,
875 budget_distribution_hdr_record.ATTRIBUTE12,
876 budget_distribution_hdr_record.ATTRIBUTE13,
877 budget_distribution_hdr_record.ATTRIBUTE14,
878 budget_distribution_hdr_record.ATTRIBUTE15,
879 budget_distribution_hdr_record.ATTRIBUTE_CATEGORY,
880 budget_distribution_hdr_record.ORG_ID,
881 budget_distribution_hdr_record.FACTS_PRGM_SEGMENT,
882 budget_distribution_hdr_record.TREASURY_SYMBOL_ID,
883 budget_distribution_hdr_record.FREEZE_DEFINITION_FLAG,
884 budget_distribution_hdr_record.AUTO_CREATE_FLAG);
885
886 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
887 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' Inserted new budget header');
888 END IF;
889 END IF;
890
891 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
892 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Processing budget distributions');
893 END IF;
894
895 FOR budget_distribution_dtl_record IN c_budget_distribution_dtl(l_bud_distribution_id,l_fund_value) LOOP
896 l_bud_distribution_dtl_id := budget_distribution_dtl_record.distribution_dtl_id;
897 budget_distribution_dtl_record.distribution_id := budget_distribution_dtl_record.distribution_id;
898 -- Bug 1024675
899 cgac_crttas_record.error_message := NULL;
900
901 SELECT FV_BE_TRX_DTLS_S.nextval INTO budget_distribution_dtl_record.distribution_dtl_id FROM dual;
902 budget_distribution_dtl_record.fund_value := fund_values_record.fund_value;
903 budget_distribution_dtl_record.creation_date := SYSDATE;
904 budget_distribution_dtl_record.last_update_date := SYSDATE;
905 budget_distribution_dtl_record.created_by := l_user_id;
906 budget_distribution_dtl_record.last_updated_by := l_user_id;
907
908 -- Insert values into the interface table
909 cgac_crttas_record.distribution_id := l_bud_distribution_id;
910 cgac_crttas_record.distribution_dtl_id := l_bud_distribution_dtl_id;
911
912 BEGIN
913 SELECT description into cgac_crttas_record.description FROM fv_budget_levels
914 WHERE set_of_books_id = p_ledger_id
915 AND budget_level_id = budget_distribution_dtl_record.budget_level_id;
916 EXCEPTION
917 WHEN OTHERS THEN
918 cgac_crttas_record.description := NULL;
919 END;
920
921 -- Getting Fiscal year segment name from fv_pya_fiscal_year_segment
922 BEGIN
923 SELECT fyr_segment_value INTO v_fyr_segment_name FROM fv_pya_fiscalyear_map
924 WHERE period_year = (p_copy_fiscal_year + 1)
925 AND set_of_books_id = p_ledger_id;
926 EXCEPTION
927 WHEN NO_DATA_FOUND THEN
928 errbuf := 'Error in Main: Please set the segment value in Define Federal Financial Options form';
929 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Error in Main: Please set the segment value in Define Federal Financial Options form');
930 retcode := -1;
931 ROLLBACK;
932 RETURN;
933 END;
934
935 BEGIN
936 SELECT application_column_name INTO v_bfy_Segment FROM fv_pya_fiscalyear_segment
937 WHERE set_of_books_id=p_ledger_id ;
938 EXCEPTION
939 WHEN NO_DATA_FOUND THEN
940 errbuf := 'Error in Main: Please set the BFY segment value in Define Federal Financial Options form' ;
941 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Error in Main: Please set the BFY segment value in Define Federal Financial Options form');
942 retcode := -1;
943 ROLLBACK;
944 RETURN;
945 END;
946
947 select default_segment into v_default_segment from fv_budget_distribution_dtl
948 where distribution_dtl_id = l_bud_distribution_dtl_id;
949
950 bal_seg_suffix := substr(v_bal_seg_name,8);
951 bfy_seg_suffix := substr(v_bfy_Segment,8);
952
953 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
954 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Balancing segment suffix is: '||bal_seg_suffix);
955 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Budget Fiscal Year segment suffix is: '||bfy_seg_suffix);
956 END IF;
957
958 IF (bal_seg_suffix <>'1') then
959 v_default_segment := substr(v_default_segment,1,
960 instr(v_default_segment,l_flex_delimiter,1,bal_seg_suffix-1)) ||
961 fund_values_record.fund_value || substr(v_default_segment,instr(v_default_segment,l_flex_delimiter,1,bal_seg_suffix));
962
963 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
964 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Default segment is ' ||v_default_segment);
965 END IF;
966
967 cgac_crttas_record.default_segment := v_default_segment;
968 ELSE
969 v_default_segment := fund_values_record.fund_value ||substr(v_default_segment,instr(v_default_segment,l_flex_delimiter,1,bal_seg_suffix));
970 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
971 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Default segment is ' ||v_default_segment);
972 END IF;
973 cgac_crttas_record.default_segment := v_default_segment;
974 END IF;
975
976 IF (bfy_seg_suffix <>'1') then
977 v_default_segment := substr(v_default_segment,1,
978 instr(v_default_segment,l_flex_delimiter,1,bfy_seg_suffix-1)) ||
979 v_fyr_segment_name || substr(v_default_segment,
980 instr(v_default_segment,l_flex_delimiter,1,bfy_seg_suffix));
981
982 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
983 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Default segment is ' || v_default_segment);
984 END IF;
985 cgac_crttas_record.default_segment := v_default_segment;
986 ELSE
987 v_default_segment := v_fyr_segment_name ||substr(v_default_segment,
988 instr(v_default_segment,l_flex_delimiter,1,bfy_seg_suffix));
989 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
990 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Default segment is ' ||v_default_segment);
991 END IF;
992 cgac_crttas_record.default_segment := v_default_segment;
993 END IF;
994
995 IF p_run_mode = 'F' then
996 INSERT INTO fv_budget_distribution_dtl values budget_distribution_dtl_record;
997
998 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
999 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Inserted new budget header');
1000 END IF;
1001
1002 l_update_query := 'UPDATE fv_budget_distribution_dtl SET '||
1003 v_bal_seg_name||'='''||fund_values_record.fund_value||''' , '|| v_bfy_Segment||' ='||
1004 v_fyr_segment_name||' WHERE distribution_dtl_id = '||budget_distribution_dtl_record.distribution_dtl_id;
1005
1006 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1007 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_update_query);
1008 END IF;
1009
1010 BEGIN
1011 l_bud_dtl_cursor := DBMS_SQL.OPEN_CURSOR ;
1012 EXCEPTION
1013 WHEN OTHERS THEN
1014 errbuf := sqlerrm || ' Budget Detail - Open Cursor ' ;
1015 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.open_l_bud_dtl_cursor', errbuf) ;
1016 retcode := -1;
1017 ROLLBACK;
1018 RETURN;
1019 END ;
1020
1021 BEGIN
1022 dbms_sql.parse(l_bud_dtl_cursor, l_update_query, DBMS_SQL.V7) ;
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 errbuf := sqlerrm || ' Budget Detail - Parse ' ;
1026 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.parsel_bud_dtl_cursor', errbuf) ;
1027 retcode := -1;
1028 ROLLBACK;
1029 RETURN;
1030 END ;
1031
1032 BEGIN
1033 l_exec_ret := dbms_sql.execute(l_bud_dtl_cursor);
1034 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.execute_bud_dtl_cursor', 'update statement executed') ;
1035 EXCEPTION
1036 WHEN OTHERS THEN
1037 errbuf := sqlerrm || ' Budget Detail - Execute Cursor ' ;
1038 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.execute_bud_dtl_cursor', errbuf) ;
1039 retcode := -1;
1040 ROLLBACK;
1041 RETURN;
1042 END ;
1043
1044 BEGIN
1045 dbms_sql.Close_Cursor(l_bud_dtl_cursor);
1046 EXCEPTION
1047 WHEN OTHERS THEN
1048 errbuf := sqlerrm ;
1049 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name||'.close_bud_dtl_cursor',errbuf) ;
1050 retcode := -1;
1051 ROLLBACK;
1052 RETURN;
1053 END ;
1054
1055 UPDATE fv_budget_distribution_dtl set default_segment = v_default_segment
1056 WHERE distribution_dtl_id = budget_distribution_dtl_record.distribution_dtl_id;
1057
1058 SELECT default_segment INTO cgac_crttas_record.default_segment
1059 FROM fv_budget_distribution_dtl
1060 WHERE distribution_dtl_id = budget_distribution_dtl_record.distribution_dtl_id;
1061 END IF;
1062
1063 END LOOP;
1064 <<end_dist>>
1065 NULL;
1066 END LOOP; -- END LOOP FOR CURSOR c_budget_distribution_hdr(l_treasury_symbol_id,l_fund_value)
1067 END IF; -- END IF l_auto_create_dist is NOT NULL and l_auto_create_dist = 'Y' THEN
1068 END IF; -- end IF fund_values_record.fund_value IS NOT NULL THEN
1069 END IF;
1070 <<end_fund>>
1071 NULL;
1072 END LOOP; -- END LOOP FOR CURSOR c_fund_value(l_treasury_symbol_id) loop
1073 END IF;
1074 <<end_tas>>
1075 NULL;
1076 END LOOP; -- END LOOP FOR CURSOR c_treasury_symbol LOOP
1077
1078 IF p_tas_type = 'AGENCY_TAS' THEN
1079 l_tas_type := 'Agency TAS';
1080 ELSIF p_tas_type ='TP_TAS' THEN
1081 l_tas_type := 'Trading Partner TAS';
1082 ELSE
1083 l_tas_type := 'Both';
1084 END IF;
1085
1086 IF p_fund_prefix_indicator = 'I' THEN
1087 l_fund_prefix_indicator := 'Insert';
1088 ELSIF p_fund_prefix_indicator = 'O' THEN
1089 l_fund_prefix_indicator := 'Overlay';
1090 ELSE
1091 l_fund_prefix_indicator := p_fund_prefix_indicator;
1092 END IF;
1093
1094 vl_req_id :=
1095 FND_REQUEST.SUBMIT_REQUEST ('FV','RXFVCRTTAS','','',FALSE,
1096 'DIRECT',
1097 p_report_id,
1098 'AGENCY_TAS',
1099 p_output_format,
1100 p_ledger_id,
1101 l_tas_type,
1102 p_run_mode,
1103 p_copy_fiscal_year,
1104 p_tas_type_enable,
1105 p_tas_type_enable_tp,
1106 l_fund_prefix_indicator,
1107 p_fund_prefix,
1108 p_agency_identifier,
1109 p_agency_identifier_tp);
1110 COMMIT;
1111
1112 IF vl_req_id = 0 THEN
1113 errbuf := 'Error submitting RX Report ';
1114 retcode := 2 ;
1115 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, errbuf) ;
1116 return;
1117 ElSIF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1118 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1119 'Concurrent Request Id for RX Report - ' ||vl_req_id);
1120 END IF;
1121 END IF; -- end of IF p_tas_type = "AGENCY_TAS' or p_tas_type = 'BOTH'
1122
1123 IF p_tas_type = 'BOTH' THEN
1124 -- Check status of completed concurrent program and if complete purge
1125 -- fv_cgac_crt_tas_temp
1126 IF vl_req_id <> 0 THEN
1127 l_call_status := fnd_concurrent.wait_for_request
1128 (
1129 request_id => vl_req_id,
1130 interval => 20,
1131 max_wait => 0,
1132 phase => l_phase,
1133 status => l_status,
1134 dev_phase => l_dev_phase,
1135 dev_status => l_dev_status,
1136 message => l_message
1137 );
1138
1139 IF (l_call_status = FALSE) THEN
1140 errbuf := 'Cannot wait for the completion of Rx-Rollover Treasury Account Symbol process';
1141 retcode := 1;
1142 END IF;
1143 END IF;
1144 purge_interface_table;
1145 END IF;
1146
1147 IF (p_tas_type = 'TP_TAS' OR p_tas_type = 'BOTH') THEN
1148 --Auto creation for Trading Partner TASs
1149 FOR tp_treasury_symbol_record IN c_tp_treasury_symbol LOOP
1150 --Original Trading Partner Treasury Account Symbol
1151 l_tp_treasury_symbol := tp_treasury_symbol_record.treasury_symbol;
1152 cgac_crttas_record := null;
1153
1154 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1155 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Processing trading partner treasury account symbol: '||tp_treasury_symbol_record.treasury_symbol);
1156 END IF;
1157
1158 cgac_crttas_record.original_treasury_symbol := tp_treasury_symbol_record.treasury_symbol;
1159 cgac_crttas_record.original_treasury_symbol_id := tp_treasury_symbol_record.treasury_symbol_id;
1160 orig_established_fiscal_yr := tp_treasury_symbol_record.established_fiscal_yr;
1161
1162 orig_end_year_avail := tp_treasury_symbol_record.end_year_avail;
1163
1164 cgac_crttas_record.AID := tp_treasury_symbol_record.agency_id;
1165 cgac_crttas_record.MAIN := tp_treasury_symbol_record.main_acct_code;
1166 cgac_crttas_record.SP := tp_treasury_symbol_record.sub_level_prefix_code;
1167 cgac_crttas_record.ATA := tp_treasury_symbol_record.dept_transfer;
1168 cgac_crttas_record.EPOA := tp_treasury_symbol_record.end_year_avail;
1169 cgac_crttas_record.BPOA := tp_treasury_symbol_record.established_fiscal_yr;
1170 cgac_crttas_record.SUB := tp_treasury_symbol_record.sub_acct_code;
1171
1172 -- Check if Auto create checkbox on TAS/BETC mapping form and Trading
1173 -- Partner Treasury Account Symbols form is checked
1174 BEGIN
1175 SELECT auto_create_flag, auto_create_flag_betc into
1176 l_auto_create_flag, l_auto_create_flag_betc
1177 FROM fv_tp_treasury_symbols where
1178 treasury_symbol = cgac_crttas_record.original_treasury_symbol;
1179 EXCEPTION
1180 WHEN NO_DATA_FOUND THEN
1181 l_auto_create_flag := NULL;
1182 END;
1183
1184 cgac_crttas_record.AUTO_TAS := l_auto_create_flag;
1185 -- Bug 13941471
1186 cgac_crttas_record.AUTO_BETC := l_auto_create_flag_betc;
1187
1188 IF l_auto_create_flag = 'Y' THEN
1189 tp_treasury_symbol_record.established_fiscal_yr := (p_copy_fiscal_year+1);
1190 tp_treasury_symbol_record.end_year_avail := tp_treasury_symbol_record.established_fiscal_yr +
1191 (orig_end_year_avail - orig_established_fiscal_yr);
1192 tp_treasury_symbol_record.CREATION_DATE := sysdate;
1193 tp_treasury_symbol_record.LAST_UPDATE_DATE := sysdate;
1194 tp_treasury_symbol_record.CREATED_BY := l_user_id;
1195 tp_treasury_symbol_record.LAST_UPDATED_BY := l_user_id;
1196
1197 cgac_crttas_record.NEW_BPOA := tp_treasury_symbol_record.established_fiscal_yr;
1198 cgac_crttas_record.NEW_EPOA := tp_treasury_symbol_record.end_year_avail;
1199
1200 --deriving the new Trading Partner Treasury Account Symbol
1201 /*Rules:
1202 1)Parenthesis are added around the Sub-Level Prefix Code
1203 2)A hyphen is displayed after the Allocation Transfer Agency Id
1204 3)The Fiscal Year is derived as follows:
1205 1. If Beginning Period of Availability, Ending Period of Availability,
1206 and Availability Type Code are blank; the Fiscal Year is not used.
1207 2. If not null, use the Availability Type value.
1208 3. If the Beginning Period of Availability and Ending Period of Availability
1209 are not null and have the same value, use the last 2 digits of the Ending
1210 Period of Availability.
1211 4. If the Beginning Period of Availability and Ending Period of Availability
1212 are NOT the same value, use the last 2 digits of the Beginning Period of
1213 Availability, a "/", and the last 2 digits of the Ending Period of Availability.
1214 4)A period is added prior to the Sub-Account Code. Leading zeros are not included.
1215 */
1216
1217 l_tp_ts_str := null;
1218 IF (tp_treasury_symbol_record.sub_level_prefix_code IS NOT NULL) THEN
1219 l_tp_ts_str := '(' ||tp_treasury_symbol_record.sub_level_prefix_code||')';
1220 END IF;
1221
1222 IF (tp_treasury_symbol_record.dept_transfer IS NOT NULL) THEN
1223 l_tp_ts_str := l_tp_ts_str || tp_treasury_symbol_record.dept_transfer||'-';
1224 END IF;
1225
1226 IF (tp_treasury_symbol_record.agency_id IS NOT NULL) THEN
1227 l_tp_ts_str := l_tp_ts_str || tp_treasury_symbol_record.agency_id;
1228 END IF;
1229
1230 IF (tp_treasury_symbol_record.established_fiscal_yr IS NULL AND
1231 tp_treasury_symbol_record.end_year_avail IS NULL AND
1232 tp_treasury_symbol_record.availability_type_code IS NULL ) THEN
1233 NULL;
1234 ELSIF (tp_treasury_symbol_record.availability_type_code IS NOT NULL ) THEN
1235 l_tp_ts_str := l_tp_ts_str || tp_treasury_symbol_record.availability_type_code;
1236 ELSIF (tp_treasury_symbol_record.established_fiscal_yr IS NOT NULL AND
1237 tp_treasury_symbol_record.end_year_avail IS NOT NULL AND
1238 tp_treasury_symbol_record.established_fiscal_yr = tp_treasury_symbol_record.end_year_avail) THEN
1239 l_tp_ts_str := l_tp_ts_str || SUBSTR(tp_treasury_symbol_record.end_year_avail,3);
1240 ELSIF (tp_treasury_symbol_record.established_fiscal_yr IS NOT NULL AND
1241 tp_treasury_symbol_record.end_year_avail IS NOT NULL AND
1242 tp_treasury_symbol_record.established_fiscal_yr <> tp_treasury_symbol_record.end_year_avail) THEN
1243 l_tp_ts_str := l_tp_ts_str || SUBSTR(tp_treasury_symbol_record.established_fiscal_yr,4) || '/' ||SUBSTR(tp_treasury_symbol_record.end_year_avail,4);
1244 END IF;
1245
1246 IF (tp_treasury_symbol_record.main_acct_code IS NOT NULL) THEN
1247 l_tp_ts_str := l_tp_ts_str||tp_treasury_symbol_record.main_acct_code;
1248 END IF;
1249
1250 IF (ltrim(tp_treasury_symbol_record.sub_acct_code,0) IS NOT NULL) THEN
1251 l_tp_ts_str := l_tp_ts_str||l_flex_delimiter||LTRIM(tp_treasury_symbol_record.sub_acct_code,0);
1252 END IF;
1253
1254 tp_treasury_symbol_record.treasury_symbol := l_tp_ts_str;
1255 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1256 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'New Trading Partner Treasury Symbol is '
1257 ||l_tp_ts_str);
1258 END IF;
1259 cgac_crttas_record.treasury_symbol := l_tp_ts_str;
1260
1261 select count(*)into tp_tas_duplicate_cnt from fv_tp_treasury_symbols where
1262 treasury_symbol = tp_treasury_symbol_record.treasury_symbol;
1263
1264 select count(*)into tp_tas_duplicate_cnt1 from fv_cgac_crt_tas_temp where
1265 treasury_symbol = tp_treasury_symbol_record.treasury_symbol;
1266
1267 IF tp_tas_duplicate_cnt > 0 OR tp_tas_duplicate_cnt1 > 0 THEN
1268 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Duplicate Tradning Partner TAS: '||l_tp_ts_str);
1269 errbuf := 'New Trading Partner TAS value already exists';
1270 cgac_crttas_record.error_message := errbuf;
1271 INSERT INTO fv_cgac_crt_tas_temp values cgac_crttas_record;
1272 GOTO end_tp_tas;
1273 END IF;
1274 END IF;
1275
1276 -- Insert into interface table to display on Rxi report
1277 INSERT INTO fv_cgac_crt_tas_temp values cgac_crttas_record;
1278
1279 IF (SQL%rowcount >0) THEN
1280 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,'Inserted TP TAS data into interface record') ;
1281 END IF;
1282
1283 IF p_run_mode = 'F' then
1284
1285 IF l_auto_create_flag ='Y' THEN
1286 -- GTAS. To support new treasury_symbol_id column in fv_tp_treasury_symbols
1287 SELECT fv_treasury_symbols_s.nextval INTO
1288 tp_treasury_symbol_record.treasury_symbol_id FROM dual;
1289
1290 -- Commented for GSCC error
1291 -- INSERT INTO fv_tp_treasury_symbols VALUES tp_treasury_symbol_record;
1292 INSERT INTO fv_tp_treasury_symbols(TREASURY_SYMBOL,
1293 --SUB_LEVEL_PREFIX_CODE,
1294 DEPT_TRANSFER,
1295 AGENCY_ID,
1296 ESTABLISHED_FISCAL_YR,
1297 END_YEAR_AVAIL,
1298 AVAILABILITY_TYPE_CODE,
1299 MAIN_ACCT_CODE,
1300 SUB_ACCT_CODE,
1301 CREATED_BY,
1302 CREATION_DATE,
1303 LAST_UPDATE_DATE,
1304 LAST_UPDATE_LOGIN,
1305 LAST_UPDATED_BY,
1306 SUB_LEVEL_PREFIX_CODE,
1307 AUTHORITY_DURATION_CODE,
1308 FEDERAL_ACCT_SYMBOL_NAME,
1309 AUTO_CREATE_FLAG,
1310 END_DATE,
1311 START_DATE,
1312 TREASURY_SYMBOL_ID,
1313 SET_OF_BOOKS_ID,
1314 ATTRIBUTE1,
1315 ATTRIBUTE2,
1316 ATTRIBUTE3,
1317 ATTRIBUTE4,
1318 ATTRIBUTE5,
1319 ATTRIBUTE6,
1320 ATTRIBUTE7,
1321 ATTRIBUTE8,
1322 ATTRIBUTE9,
1323 ATTRIBUTE10,
1324 ATTRIBUTE11,
1325 ATTRIBUTE12,
1326 ATTRIBUTE13,
1327 ATTRIBUTE14,
1328 ATTRIBUTE15,
1329 ATTRIBUTE_CATEGORY,
1330 ORG_ID,
1331 AUTO_CREATE_FLAG_BETC) VALUES (
1332 tp_treasury_symbol_record.TREASURY_SYMBOL,
1333 --tp_treasury_symbol_record.SUB_LEVEL_PREFIX_CODE,
1334 tp_treasury_symbol_record.DEPT_TRANSFER,
1335 tp_treasury_symbol_record.AGENCY_ID,
1336 tp_treasury_symbol_record.ESTABLISHED_FISCAL_YR,
1337 tp_treasury_symbol_record.END_YEAR_AVAIL,
1338 tp_treasury_symbol_record.AVAILABILITY_TYPE_CODE,
1339 tp_treasury_symbol_record.MAIN_ACCT_CODE,
1340 tp_treasury_symbol_record.SUB_ACCT_CODE,
1341 tp_treasury_symbol_record.CREATED_BY,
1342 tp_treasury_symbol_record.CREATION_DATE,
1343 tp_treasury_symbol_record.LAST_UPDATE_DATE,
1344 tp_treasury_symbol_record.LAST_UPDATE_LOGIN,
1345 tp_treasury_symbol_record.LAST_UPDATED_BY,
1346 tp_treasury_symbol_record.SUB_LEVEL_PREFIX_CODE,
1347 tp_treasury_symbol_record.AUTHORITY_DURATION_CODE,
1348 tp_treasury_symbol_record.FEDERAL_ACCT_SYMBOL_NAME,
1349 tp_treasury_symbol_record.AUTO_CREATE_FLAG,
1350 tp_treasury_symbol_record.END_DATE,
1351 tp_treasury_symbol_record.START_DATE,
1352 tp_treasury_symbol_record.TREASURY_SYMBOL_ID,
1353 tp_treasury_symbol_record.SET_OF_BOOKS_ID,
1354 tp_treasury_symbol_record.ATTRIBUTE1,
1355 tp_treasury_symbol_record.ATTRIBUTE2,
1356 tp_treasury_symbol_record.ATTRIBUTE3,
1357 tp_treasury_symbol_record.ATTRIBUTE4,
1358 tp_treasury_symbol_record.ATTRIBUTE5,
1359 tp_treasury_symbol_record.ATTRIBUTE6,
1360 tp_treasury_symbol_record.ATTRIBUTE7,
1361 tp_treasury_symbol_record.ATTRIBUTE8,
1362 tp_treasury_symbol_record.ATTRIBUTE9,
1363 tp_treasury_symbol_record.ATTRIBUTE10,
1364 tp_treasury_symbol_record.ATTRIBUTE11,
1365 tp_treasury_symbol_record.ATTRIBUTE12,
1366 tp_treasury_symbol_record.ATTRIBUTE13,
1367 tp_treasury_symbol_record.ATTRIBUTE14,
1368 tp_treasury_symbol_record.ATTRIBUTE15,
1369 tp_treasury_symbol_record.ATTRIBUTE_CATEGORY,
1370 tp_treasury_symbol_record.ORG_ID,
1371 tp_treasury_symbol_record.AUTO_CREATE_FLAG_BETC);
1372
1373 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1374 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Inserted new Trading Partner Treasury Account symbol');
1375 END IF;
1376
1377
1378 -- Check whether the original TAS has any BETC mappings assigned
1379 --SELECT COUNT(*) INTO l_betc_map_cnt FROM fv_tp_tas_betc_map WHERE
1380
1381 SELECT COUNT(*) INTO l_betc_map_cnt
1382 FROM fv_tas_betc_map
1383 WHERE treasury_symbol_id = cgac_crttas_record.original_treasury_symbol_id
1384 AND tas_type='T';
1385
1386 --Insert BETC mapping for the newly created TP TAS
1387 IF l_betc_map_cnt > 0 and l_auto_create_flag_betc = 'Y' THEN --GTAS
1388 --GTAS
1389 --INSERT INTO fv_tp_tas_betc_map(
1390 INSERT INTO fv_tas_betc_map(
1391 treasury_symbol_id,
1392 betc_code,
1393 created_by,
1394 last_update_date,
1395 last_updated_by,
1396 creation_date,
1397 last_update_login,
1398 default_flag,
1399 tas_type,
1400 set_of_books_id)
1401 SELECT
1402 tp_treasury_symbol_record.treasury_symbol_id,
1403 betc_code,
1404 l_user_id,
1405 sysdate,
1406 l_user_id,
1407 sysdate,
1408 l_user_id,
1409 default_flag,
1410 'T',
1411 p_ledger_id
1412 FROM fv_tas_betc_map
1413 WHERE treasury_symbol_id = cgac_crttas_record.original_treasury_symbol_id;
1414 ELSE
1415 cgac_crttas_record.AUTO_BETC := 'N';
1416 END IF;
1417 END IF;
1418 END IF;
1419 <<end_tp_tas>>
1420 NULL;
1421 END LOOP; -- END LOOP FOR FOR tp_treasury_symbol_record IN c_tp_treasury_symbol LOOP
1422
1423 IF p_tas_type = 'AGENCY_TAS' THEN
1424 l_tas_type := 'Agency TAS';
1425 ELSIF p_tas_type = 'TP_TAS' THEN
1426 l_tas_type := 'Trading Partner TAS';
1427 ELSE
1428 l_tas_type := 'Both';
1429 END IF;
1430
1431 IF p_fund_prefix_indicator = 'I' THEN
1432 l_fund_prefix_indicator := 'Insert';
1433 ELSIF p_fund_prefix_indicator = 'O' THEN
1434 l_fund_prefix_indicator := 'Overlay';
1435 ELSE
1436 l_fund_prefix_indicator := p_fund_prefix_indicator;
1437 END IF;
1438
1439 vl_req_id :=
1440 FND_REQUEST.SUBMIT_REQUEST ('FV','RXFVCRTTAS','','',FALSE,
1441 'DIRECT',
1442 p_report_id,
1443 'TP',
1444 p_output_format,
1445 p_ledger_id,
1446 l_tas_type,
1447 p_run_mode,
1448 p_copy_fiscal_year,
1449 p_tas_type_enable,
1450 p_tas_type_enable_tp,
1451 l_fund_prefix_indicator,
1452 p_fund_prefix,
1453 p_agency_identifier,
1454 p_agency_identifier_tp);
1455 COMMIT;
1456
1457 IF vl_req_id = 0 THEN
1458 errbuf := 'Error submitting RX Report ';
1459 retcode := 2 ;
1460 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, errbuf) ;
1461 return;
1462 ElSIF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1463 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1464 'Concurrent Request Id for RX Report - ' ||vl_req_id);
1465 END IF;
1466 END IF;
1467
1468 EXCEPTION
1469 WHEN OTHERS THEN
1470 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,SQLERRM) ;
1471 retcode := -1;
1472 errbuf := SQLERRM;
1473 ROLLBACK;
1474 RETURN;
1475
1476 END Main;
1477
1478 END FV_CGAC_CRT_TAS;