ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223EF9B3869E053B46B63588994Y SELECT dept_transfer ,department_id ,(CASE WHEN CANCELLATION_YEAR IS NULL OR NO_GTAS_CAN_TAS_FLAG='Y' THEN ESTABLISHED_FISCAL_YR WHEN :P_PERIOD_YEAR < CANCELLATION_YEAR THEN ESTABLISHED_FISCAL_YR WHEN :P_PERIOD_YEAR > CANCELLATION_YEAR AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN ESTABLISHED_FISCAL_YR WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL ELSE NULL END)ESTABLISHED_FISCAL_YR ,(CASE WHEN CANCELLATION_YEAR IS NULL OR NO_GTAS_CAN_TAS_FLAG='Y' THEN end_year_avail WHEN :P_PERIOD_YEAR < CANCELLATION_YEAR THEN end_year_avail WHEN :P_PERIOD_YEAR > CANCELLATION_YEAR AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN end_year_avail WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL ELSE NULL END) end_year_avail ,(CASE WHEN CANCELLATION_YEAR IS NULL OR NO_GTAS_CAN_TAS_FLAG='Y' THEN availability_type_code WHEN :P_PERIOD_YEAR < CANCELLATION_YEAR THEN availability_type_code WHEN :P_PERIOD_YEAR > CANCELLATION_YEAR AND NO_GTAS_CAN_TAS_FLAG='N' THEN 'C' WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN availability_type_code WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NO_GTAS_CAN_TAS_FLAG='N' THEN 'C' ELSE 'C' END) availability_type_code ,fund_group_code ,tafs_sub_acct ,ussgl_account ,authority_type_code ,direct_or_reimb_code ,appor_cat_code ,appor_cat_b_code ,program_rept_code ,bea_cat_code ,borr_src_code ,cust_non_cust ,fed_non_fed ,trading_partner_agency_id ,trading_partner_main_account ,new_bal_code ,cur_subsequent_code ,exch_non_exch ,pya_code ,budget_impact_ind ,credit_cohort_yr ,program_cost_ind ,SUM(amount) amount ,d_c_indicator ,begin_end ,reduction_type_code ,NO_GTAS_CAN_TAS_FLAG FROM (SELECT tas.dept_transfer ,tas.department_id ,tas.ESTABLISHED_FISCAL_YR ,tas.END_YEAR_AVAIL ,tas.availability_type_code ,NVL((SELECT adjustment_period_flag FROM GL_PERIOD_STATUSES_V WHERE set_of_books_id = :P_LEDGER AND application_id = 101 AND period_year = :P_PERIOD_YEAR AND period_num = :P_PERIOD_NUM AND adjustment_period_flag = 'Y'), 'N') adjustment_period_flag ,(SELECT period_year FROM gl_period_statuses WHERE tas.CANCELLATION_DATE BETWEEN start_date AND end_date AND set_of_books_id = :P_LEDGER AND application_id = 101 AND adjustment_period_flag = 'N') CANCELLATION_YEAR ,(SELECT period_num FROM gl_period_statuses WHERE tas.CANCELLATION_DATE BETWEEN start_date AND end_date AND set_of_books_id = :P_LEDGER AND application_id = 101 AND adjustment_period_flag = 'N') CANCEL_NUM ,tas.fund_group_code ,tas.tafs_sub_acct ,f1.ussgl_account ,f1.authority_type_code ,f1.direct_or_reimb_code ,f1.appor_cat_code ,f1.appor_cat_b_code ,f1.program_rept_code ,f1.bea_cat_code ,f1.borr_src_code ,f1.cust_non_cust ,f1.fed_non_fed ,f1.trading_partner_agency_id ,f1.trading_partner_main_account ,f1.new_bal_code ,f1.cur_subsequent_code ,f1.exch_non_exch ,f1.pya_code ,f1.budget_impact_ind ,f1.credit_cohort_yr ,f1.program_cost_ind ,f1.amount ,f1.d_c_indicator ,f1.begin_end ,f1.reduction_type_code ,tas.NO_GTAS_CAN_TAS_FLAG FROM fv_gtas_int_summary_temp f1, fv_treasury_symbols tas WHERE f1.treasury_symbol_id = tas.treasury_symbol_id AND f1.period_year = :P_PERIOD_YEAR AND f1.set_of_books_id = :P_LEDGER AND f1.period_num <= :P_PERIOD_NUM) GROUP BY dept_transfer ,department_id ,(CASE WHEN CANCELLATION_YEAR IS NULL OR NO_GTAS_CAN_TAS_FLAG='Y' THEN ESTABLISHED_FISCAL_YR WHEN :P_PERIOD_YEAR < CANCELLATION_YEAR THEN ESTABLISHED_FISCAL_YR WHEN :P_PERIOD_YEAR > CANCELLATION_YEAR AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN ESTABLISHED_FISCAL_YR WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL ELSE NULL END) ,(CASE WHEN CANCELLATION_YEAR IS NULL OR NO_GTAS_CAN_TAS_FLAG='Y' THEN end_year_avail WHEN :P_PERIOD_YEAR < CANCELLATION_YEAR THEN end_year_avail WHEN :P_PERIOD_YEAR > CANCELLATION_YEAR AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN end_year_avail WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL ELSE NULL END) ,(CASE WHEN CANCELLATION_YEAR IS NULL OR NO_GTAS_CAN_TAS_FLAG='Y' THEN availability_type_code WHEN :P_PERIOD_YEAR < CANCELLATION_YEAR THEN availability_type_code WHEN :P_PERIOD_YEAR > CANCELLATION_YEAR AND NO_GTAS_CAN_TAS_FLAG='N' THEN 'C' WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN availability_type_code WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NO_GTAS_CAN_TAS_FLAG='N' THEN 'C' ELSE 'C' END) ,fund_group_code ,tafs_sub_acct ,ussgl_account ,authority_type_code ,direct_or_reimb_code ,appor_cat_code ,appor_cat_b_code ,program_rept_code ,bea_cat_code ,borr_src_code ,cust_non_cust ,fed_non_fed ,trading_partner_agency_id ,trading_partner_main_account ,new_bal_code ,cur_subsequent_code ,exch_non_exch ,pya_code ,budget_impact_ind ,credit_cohort_yr ,program_cost_ind ,d_c_indicator ,begin_end ,reduction_type_code ,NO_GTAS_CAN_TAS_FLAG ORDER BY dept_transfer ,department_id ,established_fiscal_yr ,end_year_avail ,availability_type_code ,fund_group_code ,tafs_sub_acct ,ussgl_account ,authority_type_code ,direct_or_reimb_code ,appor_cat_code ,appor_cat_b_code ,program_rept_code ,bea_cat_code ,borr_src_code ,cust_non_cust ,fed_non_fed ,trading_partner_agency_id ,trading_partner_main_account ,new_bal_code ,cur_subsequent_code ,exch_non_exch ,pya_code ,budget_impact_ind ,credit_cohort_yr ,program_cost_ind ,d_c_indicator ,begin_end FV FVGTINTATB US FV GTAS Bulk File Interface Application: Federal Financials Source: GTAS Bulk File Interface Report Short Name: FVGTINTATB DB package: BI Publisher :p_ledger :p_period_num :p_period_year 1 10 :p_ledger 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 US SET_OF_BOOKS_ID 2 20 :p_period_name LOV Oracle FV_GTAS_ACT_TO_PERIOD Y select period_name id, period_name value, null description from gl_period_statuses where set_of_books_id=:$flex$.fv_srs_ledger_name and application_id=101 and closing_status in ('O','C') and period_year in (select distinct fiscal_year from fv_gtas_run where set_of_books_id=:$flex$.fv_srs_ledger_name) order by value,description US To Period 3 30 :p_period_num Number US PERIOD_NUM 4 40 :p_period_year Number US Period Year 5 50 :p_agency_id_low Char US Agency ID Low 6 60 :p_agency_id_high Char US Agency ID High 7 70 :p_main_account_low Char US MAIN_ACCOUNT_LOW 8 80 :p_main_account_high Char US Main Account High