APPS.FV_GOALS_224 SQL Statements

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

Line: 83

	SELECT	rpad(nvl(substr(replace(fst.treasury_symbol,'-',''),1,20),'                    '),20, ' '), nvl(sum(amount), 0), nvl(supplemental_flag,0)
	FROM       	fv_sf224_temp fst
	WHERE     	column_group in (20,21)
      	AND        	reported_month in ('CURRENT', 'CURRENT/PRIOR')
	AND		alc_code = v_alc_code
        AND fst.sf224_processed_flag = 'Y'
        AND fst.end_period_date < v_end_date
        AND fst.record_category = 'GLRECORD'
	GROUP BY 	fst.treasury_symbol,supplemental_flag;

Line: 95

	SELECT		rpad(nvl(substr(replace(fst.treasury_symbol,'-',''),1,20),'                    '),20, ' '),
			nvl(sum(amount), 0),nvl(supplemental_flag,0)
	FROM       	fv_sf224_temp fst
	WHERE     	column_group in (30,31)
      	AND        	reported_month in ('CURRENT', 'CURRENT/PRIOR')
	AND		alc_code = v_alc_code
        AND fst.sf224_processed_flag = 'Y'
        AND fst.end_period_date < v_end_date
        AND fst.record_category = 'GLRECORD'
	GROUP BY 	fst.treasury_symbol,supplemental_flag;

Line: 108

	SELECT     	distinct to_char(accomplish_date, 'MMYY'),
			nvl(sum(amount * decode(column_group, 21,-1,1)),0),
			nvl(supplemental_flag,0)
	FROM        	fv_sf224_temp fst
	WHERE      	column_group in (21, 30)
	AND         	reported_month = 'CURRENT/PRIOR'
	AND		alc_code = v_alc_code
        AND fst.sf224_processed_flag = 'Y'
        AND fst.end_period_date < v_end_date
        AND fst.record_category = 'GLRECORD'
	GROUP BY 	to_char(accomplish_date,'MMYY'),supplemental_flag
  HAVING nvl(sum(amount * decode(column_group, 21,-1,1)),0) <> 0
	ORDER BY 	to_char(accomplish_date,'MMYY') desc;

Line: 124

	SELECT      	to_char(accomplish_date,'MMYY'),
			nvl(sum(amount * decode(column_group, 31,-1,1)),0),
			nvl(supplemental_flag,0)
	FROM        	fv_sf224_temp fst
	WHERE      	column_group in (20,31)
	AND         	reported_month in ('CURRENT/PRIOR')
	AND		alc_code = v_alc_code
        AND fst.sf224_processed_flag = 'Y'
        AND fst.end_period_date < v_end_date
        AND fst.record_category = 'GLRECORD'
	GROUP BY 	to_char(accomplish_date,'MMYY'),supplemental_flag
  HAVING nvl(sum(amount * decode(column_group, 31,-1,1)),0) <> 0
	ORDER BY 	to_char(accomplish_date,'MMYY') desc;

Line: 139

	SELECT DISTINCT alc_code
	FROM Fv_Sf224_Temp fst
	WHERE set_of_books_id = v_set_of_books_id
	AND fst.sf224_processed_flag = 'Y'
	AND fst.record_category = 'GLRECORD'
	AND fst.end_period_date < v_end_date
	AND fst.alc_code = DECODE (vp_alc, 'ALL', fst.alc_code, vp_alc);

Line: 157

  select fv.agency_location_code
   from fv_alc_business_activity_v fv
  where set_of_books_id = c_set_of_books_id
    and period_name = c_gl_period
    AND c_partial_or_full = 'Partial'
    and c_alc ='ALL'
    and c_business_activity <>'ALL'
    and business_activity_code = c_business_activity
    and c_gwa_reporter_category <> 'ALL'
    and GWA_REPORTER_CATEGORY_CODE =c_gwa_reporter_category
    AND NOT EXISTS(SELECT DISTINCT c.alc_code
                     FROM fv_sf224_temp c
                    WHERE c.set_of_books_id = c_set_of_books_id
                      AND c.alc_code = fv.agency_location_code
	              AND record_category = 'GLRECORD'
                      AND sf224_processed_flag = 'Y'
		      AND alc_code = fv.agency_location_code
                      AND end_period_date < v_end_date)
  UNION
  select agency_location_code
    from fv_alc_business_activity_v fv
   where set_of_books_id=c_set_of_books_id
     and period_name =  c_gl_period
     AND c_partial_or_full = 'Partial'
     and c_alc  ='ALL'
     and c_business_activity='ALL'
     and business_activity_code in (select lookup_code
                                      from fv_lookup_codes
                                     where LOOKUP_TYPE = 'FV_SF224_BUSINESS_ACTIVITY')
     and c_gwa_reporter_category = 'ALL'
     and GWA_REPORTER_CATEGORY_CODE in (select lookup_code
                                          from fv_lookup_codes
                                         where LOOKUP_TYPE = 'FV_SF224_GWA_REPORTER_CATEGORY' )
     AND NOT EXISTS(SELECT DISTINCT c.alc_code
                      FROM fv_sf224_temp c
                     WHERE c.set_of_books_id = c_set_of_books_id
                       AND c.alc_code = fv.agency_location_code
                       AND record_category = 'GLRECORD'
                       AND sf224_processed_flag = 'Y'
		       AND alc_code = fv.agency_location_code
                       AND end_period_date < v_end_date)
  UNION
  select agency_location_code
    from fv_alc_business_activity_v fv
   where set_of_books_id=c_set_of_books_id
     and period_name = c_gl_period
     AND c_partial_or_full = 'Partial'
     and c_alc  ='ALL'
     and c_business_activity ='ALL'
     and business_activity_code in ( select lookup_code
                                       from fv_lookup_codes
                                      where LOOKUP_TYPE = 'FV_SF224_BUSINESS_ACTIVITY')
     and c_gwa_reporter_category  <> 'ALL'
     and GWA_REPORTER_CATEGORY_CODE = c_gwa_reporter_category
     AND NOT EXISTS(SELECT DISTINCT c.alc_code
                      FROM fv_sf224_temp c
                     WHERE c.set_of_books_id = c_set_of_books_id
                       AND c.alc_code = fv.agency_location_code
                       AND record_category = 'GLRECORD'
                       AND sf224_processed_flag = 'Y'
		       AND alc_code = fv.agency_location_code
                       AND end_period_date < v_end_date)
  UNION
  select agency_location_code
    from fv_alc_business_activity_v fv
    where set_of_books_id=c_set_of_books_id
      and period_name =  c_gl_period
      AND c_partial_or_full = 'Partial'
      and c_alc  ='ALL'
      and c_business_activity <>'ALL'
      and business_activity_code =c_business_activity
      and c_gwa_reporter_category  = 'ALL'
      and GWA_REPORTER_CATEGORY_CODE in (select fmap.gwa_reporter_category_code
                                           from fv_sf224_map fmap
                                          where fmap.business_activity_code= c_business_activity )
      AND NOT EXISTS(SELECT DISTINCT c.alc_code
                       FROM fv_sf224_temp c
                      WHERE c.set_of_books_id = c_set_of_books_id
                        AND c.alc_code = fv.agency_location_code
                        AND record_category = 'GLRECORD'
                        AND sf224_processed_flag = 'Y'
			AND alc_code = fv.agency_location_code
                        AND end_period_date < v_end_date)
  UNION
  select distinct fab.agency_location_code
    from fv_alc_business_activity_v fab
    where fab.set_of_books_id=c_set_of_books_id
      AND c_partial_or_full = 'Full'
      and c_alc  ='ALL'
      and fab.agency_location_code not in (select agency_location_code
                                             from fv_alc_business_activity_v fab1
                                             where fab1.period_name =c_gl_period)
      AND NOT EXISTS(SELECT DISTINCT c.alc_code
                       FROM fv_sf224_temp c
                      WHERE c.set_of_books_id = c_set_of_books_id
                        AND c.alc_code = fab.agency_location_code
                        AND record_category = 'GLRECORD'
                        AND sf224_processed_flag = 'Y'
			AND alc_code = fab.agency_location_code
                        AND end_period_date < v_end_date);

Line: 278

        SELECT  chart_of_accounts_id
        INTO    flex_num
        FROM    gl_ledgers_public_v
        WHERE   ledger_id = v_set_of_books_id;

Line: 283

        SELECT  distinct period_type
        INTO    statuses_period_type
        FROM    gl_period_statuses
        WHERE   application_id  = '101'
        AND     ledger_id = v_set_of_books_id;

Line: 289

        SELECT  to_char(end_date,'YYMMDD'),
                TRUNC(end_date)+1
        INTO    v_reporting_date,
                v_end_date
        FROM    gl_periods glp,
                gl_ledgers_public_v gsob
        WHERE   glp.period_name                 = gl_period_name
        AND     glp.period_type                 = statuses_period_type
        AND     gsob.ledger_id            = v_set_of_books_id
        AND     gsob.chart_of_accounts_id       = flex_num
        AND     glp.period_set_name             = gsob.period_set_name;

Line: 306

	DELETE FROM FV_GOALS_224_TEMP;

Line: 319

	  SELECT count(*)
      	  INTO	var_count
	  FROM 	fv_sf224_temp fst
	  where	set_of_books_id = v_set_of_books_id
	  and  alc_code = v_alc_code
          AND fst.sf224_processed_flag = 'Y'
          AND fst.end_period_date < v_end_date
          AND fst.record_category = 'GLRECORD';

Line: 447

	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_01, v_orig_suppl_ind);

Line: 487

	SELECT	distinct '02'
	INTO		v_entry_number
	FROM		fv_sf224_temp fst
	WHERE		rpad(substr(replace(treasury_symbol,'-',''),1,20),20,' ') IN
                  (SELECT substr(goals_224_record, 12,20)
                   FROM fv_goals_224_temp where alc_code = v_alc_code)
	AND		rpad(substr(replace(treasury_symbol,'-',''),1,20),20,' ') = v_treasury_symbol
	and		alc_code IN
                  (SELECT substr(goals_224_record, 4,8)
                   FROM fv_goals_224_temp where alc_code = v_alc_code)
	AND		alc_code = v_alc_code
        AND fst.sf224_processed_flag = 'Y'
        AND fst.end_period_date < v_end_date
        AND fst.record_category = 'GLRECORD';

Line: 532

	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_02, v_orig_suppl_ind);

Line: 553

      		SELECT 	nvl(sum(amount * decode(column_group, 21,-1,1)),0),
			nvl(supplemental_flag,0)
		INTO    v_pmt_tran_current_amt,v_orig_suppl_ind
		FROM    fv_sf224_temp fst
		WHERE   column_group in (30,21)
		AND     reported_month = 'CURRENT'
	        AND	alc_code = v_alc_code
                AND fst.sf224_processed_flag = 'Y'
                AND fst.end_period_date < v_end_date
                AND fst.record_category = 'GLRECORD'
		GROUP BY supplemental_flag
    HAVING nvl(sum(amount * decode(column_group, 21,-1,1)),0) <> 0;

Line: 607

	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_03, v_orig_suppl_ind);

Line: 662

	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record, v_orig_suppl_ind);

Line: 696

		SELECT	nvl(sum(amount*decode(column_group, 31,-1,1)),0),
			nvl(supplemental_flag,0)
		INTO        v_collection_rcvd_amt,v_orig_suppl_ind
		FROM        fv_sf224_temp fst
		WHERE     	column_group in (20,31)
		AND         reported_month in ('CURRENT','CURRENT/PRIOR')
	        AND	alc_code = v_alc_code
                AND fst.sf224_processed_flag = 'Y'
                AND fst.end_period_date < v_end_date
                AND fst.record_category = 'GLRECORD'
		GROUP BY supplemental_flag
    HAVING nvl(sum(amount*decode(column_group, 31,-1,1)),0) <> 0;

Line: 745

	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_14, v_orig_suppl_ind);

Line: 763

	SELECT	nvl(sum(amount*decode(column_group, 31,-1,1)),0),
		nvl(supplemental_flag,0)
	INTO        v_deposit_current_amt,v_orig_suppl_ind
	FROM        fv_sf224_temp fst
	WHERE      	column_group in (20,31)
	AND         reported_month = ('CURRENT')
	AND	alc_code = v_alc_code
        AND fst.sf224_processed_flag = 'Y'
        AND fst.end_period_date < v_end_date
        AND fst.record_category = 'GLRECORD'
	GROUP BY 	to_char(accomplish_date,'MM-YYYY'),supplemental_flag
  HAVING nvl(sum(amount*decode(column_group, 31,-1,1)),0) <> 0;

Line: 808

	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_15, v_orig_suppl_ind);

Line: 872

	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record, v_orig_suppl_ind);

Line: 897

  SELECT COUNT(*)
    INTO l_count_non_reclass
    FROM fv_sf224_temp fst
   WHERE column_group in (20,21, 30, 31)
     AND reported_month in ('CURRENT', 'CURRENT/PRIOR')
	   AND alc_code = v_alc_code
     AND fst.sf224_processed_flag = 'Y'
     AND fst.end_period_date < v_end_date
     AND fst.record_category = 'GLRECORD'
     AND NVL(reclass, 'N') = 'N';

Line: 947

	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_26, v_orig_suppl_ind);

Line: 965

	SELECT	lpad(count(*), 14, '0'), max(supplemental_flag)
	INTO		v_count_per_alc, v_orig_suppl_ind
	FROM		fv_goals_224_temp
	WHERE alc_code = v_alc_code;

Line: 995

	  insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, alc_code, goals_224_record, supplemental_flag)
	  values(fv_goals_224_temp_id_s.nextval, v_record_type, v_alc_code, v_record_98, v_orig_suppl_ind);

Line: 1017

	SELECT	lpad(count(*), 14, '0')
	INTO		v_count
	FROM		fv_goals_224_temp
	WHERE 	goals_224_record_type not in ('98','99');

Line: 1048

  	insert into fv_goals_224_temp(goals_224_temp_id, goals_224_record_type, goals_224_record, supplemental_flag)
  	values(fv_goals_224_temp_id_s.nextval, v_record_type, v_record_99, v_orig_suppl_ind);