[Home] [Help]
399: l_federal_seq NUMBER;
400: l_UPDATE_fed_tas BOOLEAN := TRUE;
401: cnt NUMBER :=0;
402: cnt1 NUMBER :=0;
403: l_FEDERAL_ACCT_SYMBOL_ID FV_FACTS_FEDERAL_ACCOUNTS.FEDERAL_ACCT_SYMBOL_ID%TYPE;
404:
405: --Modified the following cursor to display
406: --sob id, sub acct and dept xfer for matching
407: --rows NOT found in the the mapping table
407: --rows NOT found in the the mapping table
408: /*
409: CURSOR c_aid_NOT_exISts IS
410: SELECT f.treasury_dept_code, f.treasury_acct_code
411: FROM fv_facts_federal_accounts f
412: WHERE NOT EXISTS
413: (SELECT *
414: FROM fv_agency_id_map m, fv_treasury_symbols tas
415: WHERE tas.FEDERAL_ACCT_SYMBOL_ID = f.FEDERAL_ACCT_SYMBOL_ID
428: */
429: CURSOR c_aid_NOT_exISts IS
430: SELECT DISTINCT f.set_of_books_id, f.treasury_dept_code, f.treasury_acct_code,
431: t.TAFS_SUB_ACCT, t.dept_transfer
432: FROM fv_facts_federal_accounts f, fv_treasury_symbols t
433: WHERE f.federal_acct_symbol_id = t.federal_acct_symbol_id
434: AND NOT EXISTS
435: (SELECT 1
436: FROM fv_agency_id_map m
446: treasury_dept_code, treasury_acct_code, f.federal_acct_symbol_id,
447: treasury_symbol_id, tafs_sub_acct, department_id, dept_transfer,
448: fund_group_code, aid,ata, f.start_date start_date, f.SET_of_books_id SET_of_books_id,
449: federal_acct_symbol_name
450: FROM fv_agency_id_map m, fv_facts_federal_accounts f, fv_treasury_symbols t
451: WHERE m.star_dept_reg = f.treasury_dept_code
452: AND m.star_main_acct = f.treasury_acct_code
453: AND f.federal_acct_symbol_id = t.federal_acct_symbol_id(+)
454: AND ((t.tafs_sub_acct IS NULL) OR (t.tafs_sub_acct IS NOT NULL AND t.tafs_sub_acct=sub));
508: AND fund_group_code = rec.star_main_acct
509: AND tafs_sub_acct = rec.sub;
510: END IF;
511: ELSE
512: -- 2) Insert new record into fv_facts_federal_accounts when there is a match
513: -- for agency id and main account
514: -- and if multiple new mappings exist for a single agency id with different sub account codes
515: -- and atleast 2 TASs exist with different sub account codes
516:
533:
534: IF cnt1 > 1 THEN
535: BEGIN
536: select FEDERAL_ACCT_SYMBOL_ID into l_FEDERAL_ACCT_SYMBOL_ID
537: from fv_facts_federal_accounts
538: where treasury_dept_code = rec.aid
539: and treasury_acct_code = rec.star_main_acct;
540:
541: UPDATE fv_treasury_symbols
544: WHERE treasury_symbol_id = rec.treasury_symbol_id;
545: l_UPDATE_fed_tas := false;
546: EXCEPTION
547: WHEN NO_DATA_FOUND THEN
548: SELECT FV_FACTS_FEDERAL_ACCOUNTS_S.nextval INTO l_federal_seq FROM dual;
549: BEGIN
550: INSERT INTO FV_FACTS_FEDERAL_ACCounTS
551: (FEDERAL_ACCT_SYMBOL_ID,
552: SET_OF_BOOKS_ID,
546: EXCEPTION
547: WHEN NO_DATA_FOUND THEN
548: SELECT FV_FACTS_FEDERAL_ACCOUNTS_S.nextval INTO l_federal_seq FROM dual;
549: BEGIN
550: INSERT INTO FV_FACTS_FEDERAL_ACCounTS
551: (FEDERAL_ACCT_SYMBOL_ID,
552: SET_OF_BOOKS_ID,
553: TREASURY_DEPT_CODE,
554: TREASURY_ACCT_CODE,
589:
590: IF (l_UPDATE_fed_tas) THEN
591:
592: SELECT count(*) INTO cnt
593: FROM fv_facts_federal_accounts
594: WHERE treasury_dept_code = rec.aid
595: AND treasury_acct_code = rec.treasury_acct_code
596: AND federal_acct_symbol_id <> rec.federal_acct_symbol_id;
597:
596: AND federal_acct_symbol_id <> rec.federal_acct_symbol_id;
597:
598: IF cnt > 0 THEN
599: SELECT federal_acct_symbol_id INTO l_federal_acct_symbol_id
600: FROM fv_facts_federal_accounts
601: WHERE treasury_dept_code = rec.aid
602: AND treasury_acct_code = rec.treasury_acct_code
603: AND federal_acct_symbol_id <> rec.federal_acct_symbol_id;
604:
607: department_id = rec.aid
608: WHERE Treasury_Symbol_Id = rec.Treasury_Symbol_Id;
609:
610: ELSE
611: UPDATE fv_facts_federal_accounts
612: SET treasury_dept_code = rec.aid
613: WHERE federal_acct_symbol_id = rec.federal_acct_symbol_id;
614:
615:
623: END IF;
624: END IF;
625: END LOOP;
626:
627: fnd_file.put_line(fnd_file.log, 'Updating fv_facts_federal_accounts with new agency id');
628: fnd_file.put_line(fnd_file.log, 'Updating fv_treasury_symbols with new allocation transfer agency id');
629: fnd_file.put_line(fnd_file.log, 'Updated: '||SQL%ROWCOUNT||' rows.');
630:
631: EXCEPTION WHEN OTHERS THEN