APPS.IGF_AP_EFC_SUBF SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 11

  || svuppala         16-Nov-2004     Bug # 3416849 Expected Family Contribution Updates 2005-2006
  ||  gmuralid        09-03-2003      BUG# 2833795 - EFC Mismatch Base BUG - Added a extra parameter l_call_type to procedure c_efc
  ||  gmuralid        08-03-2003      BUG# 2833795 - EFC Mismatch Base BUG
  ||  gmuralid        06-03-2003      Modifed Rounding off mechanism
  ||  gmuralid        04-03-2003      BUG#2831089 - Corrected population of intermediate values into isir record
  ||  gmuralid        03-03-2003      BUG#2826603 - Implemented Null Handling In EEA calculation
  ||  gmuralid        03-03-2003      BUG#2826603 - Included negative  and non existant value check in set up cursors including state cursor
  ||  gmuralid        17-02-2003      Included assumed values in sub functions
  ||  (reverse chronological order - newest change first)

*/

  -- SUB FUNCTIONS  for calculating EFC with FORMULA A
  PROCEDURE  a_p_inc ( p_p_inc     OUT NOCOPY    NUMBER)   AS
  /*
  ||  Created By : gmuralid
  ||  Created On : 11 Feb 2003
  ||  Purpose :    Procedure to get Parents Income , Bug# 2758804 , EFC build TD
  ||
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  ||
  ||  (reverse chronological order - newest change first)
 */
       -- Initialize the local variables with the Global variables.
    l_adjusted_gross_income    igf_ap_isir_matched.p_adjusted_gross_income%TYPE;

Line: 113

       SELECT lookup_code
         FROM igf_lookups_view
        WHERE lookup_type = 'IGF_AP_STATE_CODES'
          AND lookup_code = cp_state;

Line: 120

       SELECT tax_rate
         FROM igf_fc_state_tx   txrng
        WHERE txrng.table_code       = 'A1'
          AND (p_p_inc BETWEEN txrng.income_range_start AND txrng.income_range_end)
          AND txrng.state_code       = cp_state
          AND txrng.s_award_year     = igf_ap_efc_calc.p_sys_award_year;

Line: 129

       SELECT tax_rate, amount, tax_rate_excess, amount_excess
         FROM igf_fc_gen_tax_rts  gtxrts
        WHERE gtxrts.table_code    = 'A2'
          AND gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
          AND (cp_inc_work BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);

Line: 138

       SELECT ip_allowance_amt
         FROM igf_fc_inc_prct ipa
        WHERE ipa.table_code    = 'A3'
          AND ipa.s_award_year = igf_ap_efc_calc.p_sys_award_year
          AND ipa.people_in_household = cp_num_family_member
          AND ipa.students_in_household = cp_num_in_college ;

Line: 147

       SELECT empl_exp_allowance_rate1, empl_exp_allowance_amount1,empl_exp_allowance_rate2, empl_exp_allowance_amount2
         FROM igf_fc_efc_frm_a  efca
        WHERE efca.s_award_year = igf_ap_efc_calc.p_sys_award_year;

Line: 537

  || nsidana        11/20/2003       FA129 EFC updates for 2004-2005.
  ||  (reverse chronological order - newest change first)

 */
   l_investment_networth    igf_ap_isir_matched.p_investment_networth%TYPE;

Line: 552

      SELECT A5_default_age, parent_asset_conv_rate
        FROM igf_fc_efc_frm_a   efca
       WHERE efca.s_award_year = igf_ap_efc_calc.p_sys_award_year;

Line: 558

       SELECT parent1_allowance, parent2_allowance
         FROM igf_fc_ast_pc_dt   apdt
        WHERE apdt.s_award_year = igf_ap_efc_calc.p_sys_award_year
          AND apdt.table_code = 'A5'
          AND apdt.older_parent_age = cp_age_older_parent;

Line: 566

      SELECT tax_rate, amount, tax_rate_excess, amount_excess
        FROM igf_fc_gen_tax_rts  gtxrts
       WHERE gtxrts.table_code    = 'A4'
         AND gtxrts.s_award_year  = igf_ap_efc_calc.p_sys_award_year
         AND (cp_business_networth BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);

Line: 726

       SELECT tax_rate, amount, tax_rate_excess, amount_excess
         FROM igf_fc_gen_tax_rts  gtxrts
        WHERE gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
          AND gtxrts.table_code   = 'A6'
          AND (cp_p_aai BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);

Line: 890

       SELECT lookup_code
         FROM igf_lookups_view
        WHERE lookup_type = 'IGF_AP_STATE_CODES'
          AND lookup_code = cp_state;

Line: 897

       SELECT tax_rate
         FROM igf_fc_state_tx    txrng
        WHERE txrng.s_award_year  = igf_ap_efc_calc.p_sys_award_year
          AND txrng.table_code    = 'A7'
          AND txrng.state_code    = cp_state;

Line: 906

       SELECT tax_rate, amount, tax_rate_excess, amount_excess
         FROM igf_fc_gen_tax_rts  gtxrts
        WHERE gtxrts.table_code    = 'A2'
          AND gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
          AND (cp_inc_work BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);

Line: 914

       SELECT stud_inc_prot_allowance
         FROM igf_fc_efc_frm_a  efca
        WHERE efca.s_award_year = igf_ap_efc_calc.p_sys_award_year;

Line: 1098

       SELECT stud_available_income
         FROM igf_fc_efc_frm_a  efca
        WHERE efca.s_award_year = igf_ap_efc_calc.p_sys_award_year;

Line: 1156

      SELECT stud_asset_assessment_rate
        FROM igf_fc_efc_frm_a   efca
       WHERE efca.s_award_year = igf_ap_efc_calc.p_sys_award_year;

Line: 1338

       SELECT income_protection_diff_9
         FROM igf_fc_efc_frm_a   efca
        WHERE efca.s_award_year = igf_ap_efc_calc.p_sys_award_year;

Line: 1345

       SELECT tax_rate, amount, tax_rate_excess, amount_excess
         FROM igf_fc_gen_tax_rts  gtxrts
        WHERE gtxrts.table_code   = 'A6'
          AND gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
          AND (cp_p_aai BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);

Line: 1526

       SELECT lookup_code
         FROM igf_lookups_view
        WHERE lookup_type = 'IGF_AP_STATE_CODES'
          AND lookup_code = cp_state;

Line: 1533

       SELECT tax_rate
         FROM igf_fc_state_tx    txrng
        WHERE txrng.table_code   = 'B1'
          AND txrng.s_award_year = igf_ap_efc_calc.p_sys_award_year
          AND txrng.state_code   = cp_state;

Line: 1541

       SELECT tax_rate, amount, tax_rate_excess, amount_excess
         FROM igf_fc_gen_tax_rts  gtxrts
        WHERE gtxrts.table_code    = 'B2'
          AND gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
          AND (cp_inc_work BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);

Line: 1549

       SELECT  unmarried_stud_ipa_amt, mss_half_time_ipa_amt,married_stud_ipa_amt, unmarried_stud_eea_amt,
               married_one_work_eea_amt,married_two_work_eea_rate, married_two_work_eea_amt
         FROM  igf_fc_efc_frm_b  efcb
        WHERE  efcb.s_award_year = igf_ap_efc_calc.p_sys_award_year;

Line: 1808

       SELECT stud_available_income
         FROM igf_fc_efc_frm_b  efcb
        WHERE efcb.s_award_year = igf_ap_efc_calc.p_sys_award_year;

Line: 1874

       SELECT stud_asset_conv_rate
         FROM igf_fc_efc_frm_b   efcb
        WHERE efcb.s_award_year = igf_ap_efc_calc.p_sys_award_year;

Line: 1880

       SELECT parent1_allowance, parent2_allowance
         FROM igf_fc_ast_pc_dt   apdt
        WHERE apdt.table_code       = 'B4'         --gmuralid table code I think needs to be B4 , it was A5
          AND apdt.older_parent_age = cp_age_student
          AND apdt.s_award_year     = igf_ap_efc_calc.p_sys_award_year;

Line: 1889

       SELECT tax_rate, amount, tax_rate_excess, amount_excess
         FROM igf_fc_gen_tax_rts  gtxrts
        WHERE gtxrts.table_code    = 'B3'  --gmuralid Table code i think needs to be B3, it was A4
          AND gtxrts.s_award_year =  igf_ap_efc_calc.p_sys_award_year
          AND (cp_business_networth BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);

Line: 2185

       SELECT lookup_code
         FROM igf_lookups_view
        WHERE lookup_type = 'IGF_AP_STATE_CODES'
          AND lookup_code = cp_state;

Line: 2192

       SELECT tax_rate
         FROM igf_fc_state_tx   txrng
        WHERE txrng.table_code       = 'C1'
          AND txrng.state_code       = cp_state
          AND txrng.s_award_year     = igf_ap_efc_calc.p_sys_award_year
          AND (p_s_inc BETWEEN txrng.income_range_start AND txrng.income_range_end) ;

Line: 2201

       SELECT tax_rate, amount, tax_rate_excess, amount_excess
         FROM igf_fc_gen_tax_rts  gtxrts
        WHERE gtxrts.table_code    = 'C2'
          AND gtxrts.s_award_year  = igf_ap_efc_calc.p_sys_award_year
          AND (cp_inc_work BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);

Line: 2210

       SELECT ip_allowance_amt
         FROM igf_fc_inc_prct     ipa
        WHERE ipa.table_code            = 'C3'
          AND ipa.people_in_household   = cp_num_family_member
          AND ipa.students_in_household = cp_num_in_college
          AND ipa.s_award_year          = igf_ap_efc_calc.p_sys_award_year;

Line: 2219

       SELECT eea_mrd_2_wrk_rate, eea_mrd_2_wrk_amt,
              eea_mrd_1_wrk_rate, eea_mrd_1_wrk_amt
         FROM igf_fc_efc_frm_c  efcc
        WHERE efcc.s_award_year = igf_ap_efc_calc.p_sys_award_year;

Line: 2528

       SELECT stud_asset_conv_rate
         FROM igf_fc_efc_frm_c   efcc
        WHERE efcc.s_award_year = igf_ap_efc_calc.p_sys_award_year;

Line: 2534

       SELECT parent1_allowance, parent2_allowance
         FROM igf_fc_ast_pc_dt apdt
        WHERE apdt.table_code = 'C5'
          AND apdt.older_parent_age = cp_age_student
          AND apdt.s_award_year = igf_ap_efc_calc.p_sys_award_year;

Line: 2542

       SELECT tax_rate, amount, tax_rate_excess, amount_excess
         FROM igf_fc_gen_tax_rts  gtxrts
        WHERE gtxrts.table_code    = 'C4'
          AND gtxrts.s_award_year = igf_ap_efc_calc.p_sys_award_year
          AND (cp_business_networth BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);

Line: 2710

       SELECT tax_rate, amount, tax_rate_excess, amount_excess
         FROM igf_fc_gen_tax_rts  gtxrts
        WHERE gtxrts.table_code    = 'C6'
          AND gtxrts.s_award_year= igf_ap_efc_calc.p_sys_award_year
          AND (cp_aai BETWEEN gtxrts.income_range_start AND gtxrts.income_range_end);

Line: 2819

   ||  nsidana        11/18/2003     FA129 EFC updates for 2004-2005.
   ||
   ||  (reverse chronological order - newest change first)
  */

  l_date DATE;