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 |