ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D46FA551A9E05362FB09050512 SELECT ATA, AID, (CASE WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN BPOA WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN BPOA WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN NULL WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN BPOA WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN NULL ELSE NULL END) BPOA, (CASE WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN EPOA WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN EPOA WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN NULL WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN EPOA WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN NULL ELSE NULL END) EPOA, (CASE WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN AV WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN AV WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN 'C' WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN AV WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN 'C' ELSE 'C' END) AV, MAIN, SUB, ACCT_NO, DRC, APC, ACB, PRC, FNF, TPA, TPM, NBC, CSC, BEA, BSC, ENC, CNC, ATC, BII, PYA, CCY, PCI, sum(BEGINNING_BALANCE) BEGINNING_BALANCE ,sum(DR_AMOUNT) DR_AMOUNT ,sum(CR_AMOUNT) CR_AMOUNT ,sum(ENDING_BALANCE) ENDING_BALANCE ,RTC FROM (SELECT DEPT_TRANSFER ATA ,DEPARTMENT_ID AID ,ESTABLISHED_FISCAL_YR BPOA ,END_YEAR_AVAIL EPOA ,AVAILABILITY_TYPE_CODE AV ,TP.PERIOD_YEAR ,TP.PERIOD_NUM ,TP.ADJUSTMENT_PERIOD_FLAG , (SELECT period_year FROM gl_period_statuses WHERE CANCELLATION_DATE BETWEEN start_date AND end_date AND set_of_books_id = :P_LEDGER_ID AND application_id = 101 AND adjustment_period_flag = 'N') CANCELLATION_YEAR ,(SELECT period_num FROM gl_period_statuses WHERE CANCELLATION_DATE BETWEEN start_date AND end_date AND set_of_books_id = :P_LEDGER_ID AND application_id = 101 AND adjustment_period_flag = 'N') CANCEL_NUM ,FUND_GROUP_CODE MAIN ,TAFS_SUB_ACCT SUB ,ACCOUNT_NUMBER ACCT_NO ,DIRECT_OR_REIMB_CODE DRC ,APPOR_CAT_CODE APC ,APPOR_CAT_B_CODE ACB ,PROGRAM_REPT_CODE PRC ,FED_NON_FED FNF ,TRADING_PARTNER_AGENCY_ID TPA ,TRADING_PARTNER_MAIN_ACCOUNT TPM ,NEW_BAL_CODE NBC ,CUR_SUBSEQUENT_CODE CSC ,BEA_CAT_CODE BEA ,BORR_SRC_CODE BSC ,EXCH_NON_EXCH ENC ,CUST_NON_CUST CNC ,AUTHORITY_TYPE_CODE ATC ,BUDGET_IMPACT_IND BII ,PYA_CODE PYA ,CREDIT_COHORT_YR CCY ,PROGRAM_COST_IND PCI ,BEGINNING_BALANCE BEGINNING_BALANCE ,DR_AMOUNT DR_AMOUNT ,CR_AMOUNT CR_AMOUNT ,ENDING_BALANCE ENDING_BALANCE ,REDUCTION_TYPE_CODE RTC ,TB.NO_GTAS_CAN_TAS_FLAG NGCF FROM FV_GTAS_TB_V TB, ( SELECT period_year, period_num, adjustment_period_flag FROM gl_period_statuses WHERE set_of_books_id = :P_LEDGER_ID AND application_id = 101 AND period_name = :P_PERIOD) TP WHERE TB.SET_OF_BOOKS_ID = :P_LEDGER_ID) GROUP BY ATA, AID, (CASE WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN BPOA WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN BPOA WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN NULL WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN BPOA WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN NULL ELSE NULL END) , (CASE WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN EPOA WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN EPOA WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN NULL WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN EPOA WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN NULL ELSE NULL END) , (CASE WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN AV WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN AV WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN 'C' WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN AV WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN 'C' ELSE 'C' END) , MAIN, SUB, ACCT_NO, DRC, APC, ACB, PRC, FNF, TPA, TPM, NBC, CSC, BEA, BSC, ENC, CNC, ATC, BII, PYA, CCY, PCI ,RTC HAVING (sum(BEGINNING_BALANCE) <> 0 OR sum(DR_AMOUNT) <> 0 OR sum(CR_AMOUNT) <> 0 OR sum(ENDING_BALANCE) <>0 ) ORDER BY ATA,AID,BPOA,EPOA,AV,MAIN,SUB,ACCT_NO Y FV FVGTASTB US FV GTAS Trial Balance Imported from BI Publisher Application: Federal Financials Source: GTAS Trial Balance Report Short Name: FVGTASTB DB package: FV_GTAS_TBAL_TRX_PKG BI Publisher :p_aid_high :p_aid_low :p_chart_of_accounts_id :p_currency :p_flex_high :p_flex_low :p_ledger_id :p_main_acct_high :p_main_acct_low :p_period :p_tas 1 -10 :p_chart_of_accounts_id LOV Oracle FV_SRS_COA_BY_LEDGER Y select chart_of_accounts_id id, chart_of_accounts_id value, null description from gl_access_sets where access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') order by value,description SELECT CHART_OF_ACCOUNTS_ID FROM GL_LEDGERS WHERE LEDGER_ID = :$flex$.ledger_name Y US Chart of Accounts ID Chart of Accounts ID 2 10 :p_ledger_id LOV Oracle FV_SRS_LEDGER_NAME Y select l.ledger_id id, l.name value, l.description description from gl_access_set_ledgers ac,gl_ledgers_public_v l where ac.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and l.ledger_id=ac.ledger_id and l.ledger_category_code='PRIMARY' order by name SELECT name FROM gl_ledgers WHERE ledger_id = gl_access_set_security_pkg.get_default_ledger_id(fnd_profile.value('GL_ACCESS_SET_ID'),'F') Y US Ledger Name Ledger Name 3 20 :p_flex_low Char US Flexfield Low Flexfield Low 4 30 :p_flex_high Char US Flexfield High Flexfield High 5 40 :p_period LOV Oracle FV_OPEN_CLOSE_PERIOD Y select period_name id, period_name value, null description from gl_period_statuses where application_id=101 and ledger_id=:$flex$.fv_srs_ledger_name and closing_status in ('O','C') order by period_year desc,period_num desc Y US Period Name Period Name 6 50 :p_aid_low LOV Oracle FV_GTAS_AID_LOW Y select laid id, aid value, null description from (select distinct(treasury_dept_code) aid,treasury_dept_code laid,set_of_books_id from fv_facts_federal_accounts) where set_of_books_id=:$flex$.fv_srs_ledger_name order by aid US Agency ID Low Agency ID Low 7 60 :p_aid_high LOV Oracle FV_GTAS_AID_HIGH Y select laid id, aid value, null description from (select distinct(treasury_dept_code) aid,treasury_dept_code laid,set_of_books_id from fv_facts_federal_accounts) where set_of_books_id=:$flex$.fv_srs_ledger_name and aid>=:$flex$.fv_gtas_aid_low order by aid US Agency ID High Agency ID High 8 70 :p_main_acct_low LOV Oracle FV_GTAS_MAIN_ACCT_LOW Y select lmain id, main value, null description from (select distinct(treasury_acct_code) main,treasury_acct_code lmain,set_of_books_id from fv_facts_federal_accounts) where set_of_books_id=:$flex$.fv_srs_ledger_name order by main US Main Account Low Main Account Low 9 80 :p_main_acct_high LOV Oracle FV_GTAS_MAIN_ACCT_HIGH Y select lmain id, main value, null description from (select distinct(treasury_acct_code) main,treasury_acct_code lmain,set_of_books_id from fv_facts_federal_accounts) where set_of_books_id=:$flex$.fv_srs_ledger_name and main>=:$flex$.fv_gtas_main_acct_low order by main Y US Main Account High Main Account High 10 90 :p_tas LOV Oracle FV_TREASURY_SYMBOLS Y select treasury_symbol id, treasury_symbol value, null description from fv_treasury_symbols where set_of_books_id=:$flex$.fv_srs_ledger_name order by treasury_symbol US Treasury Account Symbol Treasury Account Symbol :$flex$.fv_gtas_aid_low Agency ID Low Agency ID High :$flex$.fv_gtas_main_acct_low Main Account Low Main Account High :$flex$.fv_srs_ledger_name Ledger Name Agency ID High :$flex$.fv_srs_ledger_name Ledger Name Agency ID Low :$flex$.fv_srs_ledger_name Ledger Name Main Account High :$flex$.fv_srs_ledger_name Ledger Name Main Account Low :$flex$.fv_srs_ledger_name Ledger Name Period Name :$flex$.fv_srs_ledger_name Ledger Name Treasury Account Symbol :$flex$.ledger_name Ledger Name Chart of Accounts ID