APPS.IGW_REPORT_PROCESSING SQL Statements

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

Line: 24

    select distinct proposal_budget_category
    into   x_category
    from   igw_report_budget_seed
    where  proposal_form_number = p_proposal_form_number
    and	   proposal_budget_category_code = p_category_code;

Line: 44

    select period_total_direct_cost
    into   l_period_total
    from   igw_report_budget
    where  proposal_budget_category_code = p_budget_category_code
    and	   proposal_form_number = G_PROPOSAL_FORM_NUMBER
    and    proposal_id = G_PROPOSAL_ID
    and	   version_id = l_version_id
    and	   budget_period_id = p_period_id;

Line: 63

    select 	version_id
    into	l_version_id
    from	igw_budgets
    where	proposal_id = p_proposal_id
    and	final_version_flag = 'Y';

Line: 85

    select meaning
    into   l_award_role
    from   fnd_lookups
    where  lookup_type = 'AWARD_ROLE'
    and	   lookup_code = p_role_code;

Line: 97

      select meaning
      into   l_role
      from   fnd_lookups
      where  lookup_type = 'IGW_PROPOSAL_ROLE_TYPES'
      and    lookup_code = p_role_code;

Line: 117

    	select 	distinct proposal_budget_category
	,	budget_category_code
	,	details_required_flag
	from	igw_report_budget_seed
	where   proposal_form_number = p_proposal_form_number
        and	budget_category_code NOT IN ('84','SB');

Line: 125

	select 	expenditure_type
	,	budget_justification
	from	igw_budget_line_category_v	pbd
	where 	pbd.proposal_id = p_proposal_id
	and	pbd.version_id = l_version_id
	and	pbd.budget_category_code =l_budget_category_code;

Line: 135

      select 	proposal_budget_category
      ,          justification
      from	igw_report_budg_justification
      where	proposal_id = p_proposal_id
      and	version_id =  l_version_id
      and	proposal_form_number = p_proposal_form_number;

Line: 155

    delete from igw_report_budg_justification
    where p_proposal_form_number = p_proposal_form_number
    and   proposal_id = p_proposal_id
    and   version_id = l_version_id;

Line: 162

    delete from igw_prop_abstracts
    where  proposal_id = p_proposal_id
    and	   abstract_type_code = 'C.1'
    and	   abstract_type = 'IGW_ABSTRACT_TYPES';

Line: 202

        insert into igw_report_budg_justification(
						proposal_id
						,version_id
						,proposal_budget_category
						,justification
						,proposal_form_number)
					values(
						p_proposal_id
						,l_version_id
						,rec_budget_category.proposal_budget_category
						,l_category_budget_just
						,p_proposal_form_number);

Line: 230

    insert into igw_prop_abstracts(
 				   proposal_id
				   ,abstract_type_code
				   ,abstract
				   ,abstract_type
                                   ,last_update_date
                                   ,last_updated_by
                                   ,creation_date
                                   ,created_by
                                   ,last_update_login )
                           values(
                                   p_proposal_id
                                   ,'C.1'
				   ,l_proposal_budget_just
				   ,'IGW_ABSTRACT_TYPES'
				   ,sysdate
				   ,fnd_global.user_id
				   ,sysdate
				   ,fnd_global.user_id
				   ,fnd_global.login_id);

Line: 275

      select 	proposal_budget_category
      ,          justification
      from	igw_report_budg_justification
      where	proposal_id = p_proposal_id
      --and	version_id =  l_version_id
      and	proposal_form_number = p_proposal_form_number;

Line: 285

    delete from igw_prop_abstracts
    where  proposal_id = p_proposal_id
    and	   abstract_type_code = 'C.1'
    and	   abstract_type = 'IGW_ABSTRACT_TYPES';

Line: 297

    insert into igw_prop_abstracts(
 				   proposal_id
				   ,abstract_type_code
				   ,abstract
				   ,abstract_type
                                   ,last_update_date
                                   ,last_updated_by
                                   ,creation_date
                                   ,created_by
                                   ,last_update_login )
                           values(
                                   p_proposal_id
                                   ,'C.1'
				   ,l_proposal_budget_just
				   ,'IGW_ABSTRACT_TYPES'
				   ,sysdate
				   ,fnd_global.user_id
				   ,sysdate
				   ,fnd_global.user_id
				   ,fnd_global.login_id);

Line: 339

	select	organization_id
	,	question_number
	,	explanation
	from	igw_org_questions
	where	explanation is not null;

Line: 348

    delete from igw_report_q_explanation
    where p_proposal_form_number = p_proposal_form_number;

Line: 354

      insert into igw_report_q_explanation  (
						organization_id
						,question_number
						,explanation
						,proposal_form_number )
					values
					     (	rec_org_questions.organization_id
						,rec_org_questions.question_number
						,rec_org_questions.explanation
						,p_proposal_form_number );

Line: 383

	select 	budget_period_id
	,	start_date
	,	end_date
	from	igw_budget_periods
	where	proposal_id = p_proposal_id
	and	version_id = l_version_id;

Line: 404

    delete from igw_report_budget_base_rate
    where p_proposal_form_number = p_proposal_form_number
    and   proposal_id = p_proposal_id
    and   version_id = l_version_id;

Line: 412

      	select 	sum(base_amt)
	into	l_base_amount
	from	igw_budget_category_v		pbcv
	,	igw_budget_details_cal_amts	pbdc
	,	igw_rate_classes		prc
	where	pbcv.line_item_id = pbdc.line_item_id
	and	pbdc.rate_class_id = prc.rate_class_id
	and	prc.rate_class_type = 'O'
	and	pbdc.apply_rate_flag = 'Y'
	and	pbcv.proposal_id = p_proposal_id
	and	pbcv.version_id = l_version_id
	and 	pbcv.budget_period_id = rec_no_of_periods.budget_period_id
	and	pbcv.oh_applied_flag = 'Y';

Line: 426

	select 	total_indirect_cost
	into	l_total_indirect_cost
	from	igw_budget_periods
	where	proposal_id = p_proposal_id
	and	version_id =  l_version_id
	and	budget_period_id = rec_no_of_periods.budget_period_id;

Line: 448

        insert into igw_report_budget_base_rate(proposal_id
						,version_id
						,budget_period_id
						,base_amount
						,rate_applied
						,total_indirect_cost
						,start_date
						,end_date
						,fiscal_year
						,proposal_form_number )
					values
					      ( p_proposal_id
						,l_version_id
						,rec_no_of_periods.budget_period_id
						,l_base_amount
						,l_rate_applied
						,l_total_indirect_cost
						,rec_no_of_periods.start_date
						,rec_no_of_periods.end_date
						,l_fiscal_year
						,p_proposal_form_number	);

Line: 477

      select version_id
      ,	     sum(nvl(total_indirect_cost,0))/sum(nvl(base_amount,0)) * 100
      ,      avg(base_amount)
      into   l_version_id
      ,      l_rate_applied
      ,      l_base_amount
      from   igw_report_budget_base_rate
      where  proposal_id = p_proposal_id
      and    proposal_form_number = p_proposal_form_number
      group by proposal_id, version_id;

Line: 488

      insert into igw_report_budget_base_rate(proposal_id
						,version_id
						,budget_period_id
						,base_amount
						,rate_applied
						,total_indirect_cost
						,start_date
						,end_date
						,fiscal_year
						,proposal_form_number )
					values
					      ( p_proposal_id
						,l_version_id
						,0
						,l_base_amount
						,l_rate_applied
						,null
						,null
						,null
						,null
						,p_proposal_form_number	);

Line: 535

     their corresponding data from the database by the setting the recently updated flag to 'Y'.
     Then find their parent categories in the seed table and insert them into the above table
     ( if parent category alrady exists then update it instead of inserting). Repeat the
     process till the loop */

  PROCEDURE create_reporting_data(	p_proposal_id   		NUMBER
					,p_proposal_form_number		VARCHAR2
					,x_return_status    	OUT NOCOPY	VARCHAR2
					,x_msg_data         	OUT NOCOPY	VARCHAR2
					,x_msg_count	    	OUT NOCOPY 	NUMBER)  is

    l_budget_period_id			NUMBER(15);

Line: 553

      	select  irs.budget_category_code
      	from 	igw_report_budget_seed irs
      	where   irs.proposal_form_number = p_proposal_form_number
	and	irs.budget_category_code not in
		(select distinct IR.proposal_budget_category_code
		from igw_report_budget_seed IR
		where  IR.proposal_form_number = p_proposal_form_number);

Line: 562

      	select  distinct proposal_budget_category_code
      	from   igw_report_budget_seed
      	where  proposal_form_number = p_proposal_form_number
      	and    budget_category_code IN (
				select proposal_budget_category_code
				from   igw_report_budget
				where  proposal_form_number =  p_proposal_form_number
				and    proposal_id = p_proposal_id
				and    recently_updated_flag = 'Y');

Line: 574

    	select 	budget_period_id
    	from	igw_budget_periods
    	where	proposal_id = p_proposal_id
        and	version_id = l_version_id;

Line: 610

    delete from igw_report_budget
    where p_proposal_form_number = p_proposal_form_number
    and   proposal_id = p_proposal_id
    and   version_id = l_version_id;

Line: 622

     	  select  sum(base_amt)	period_amt
	  ,	  sum(eb_cost)	eb_amt
	  ,	  budget_period_id
	  into    l_period_direct_cost_amt
          ,	  l_period_eb_amt
	  ,	  l_budget_period_id
    	  from    igw_budget_category_v
	  where   budget_category_code = rec_lowest_category.budget_category_code
	  and	  proposal_id = p_proposal_id
          and     version_id = l_version_id
	  and	  budget_period_id = rec_budget_period.budget_period_id
	  group by proposal_id, version_id, budget_period_id;

Line: 641

          select proposal_budget_category
          into	 l_budget_category
          from   igw_report_budget_seed
          where  proposal_form_number = p_proposal_form_number
          and	 budget_category_code = rec_lowest_category.budget_category_code;

Line: 649

            select meaning
            into   l_budget_category
            from   igw_lookups_v
            where  lookup_type = 'IGW_BUDGET_CATEGORY'
            and	   lookup_code = rec_lowest_category.budget_category_code;

Line: 659

        insert into igw_report_budget(
					proposal_id
					,version_id
					,budget_period_id
					,proposal_budget_category
					,proposal_budget_category_code
					,period_total_direct_cost
					,eb_total
					,proposal_form_number
					--,order_sequence
					,recently_updated_flag
							)
				values
	                	      ( p_proposal_id
					,l_version_id
					,l_budget_period_id
                                    	,l_budget_category
					,rec_lowest_category.budget_category_code
					,l_period_direct_cost_amt
					,l_period_eb_amt
					,p_proposal_form_number
					--,igw_report_budget_seed_s.nextval
					,'Y');

Line: 698

          select   '1'
          into     l_parent_exists
          from     igw_report_budget_seed
          where    budget_category_code = rec_parent_category.proposal_budget_category_code
          and	   rownum < 2;

Line: 709

     	  select  sum(nvl(base_amt,0))	period_amt
	  ,	  sum(nvl(eb_cost,0))	eb_amt
	  ,	  budget_period_id
	  into    l_period_direct_cost_amt
          ,	  l_period_eb_amt
	  ,	  l_budget_period_id
    	  from    igw_budget_category_v
	  where   budget_category_code = rec_parent_category.proposal_budget_category_code
	  and	  proposal_id = p_proposal_id
          and     version_id = l_version_id
	  and	  budget_period_id = rec_budget_period.budget_period_id
	  group by proposal_id, version_id, budget_period_id;

Line: 726

        select   sum(nvl(period_total_direct_cost,0))
	,	 sum(nvl(eb_total,0))
  	into     l_period_direct_cost_amt1
	,	 l_period_eb_amt1
        from     igw_report_budget
	where    proposal_id = p_proposal_id
  	and	 version_id = l_version_id
	and	 budget_period_id = rec_budget_period.budget_period_id
	and	 proposal_form_number = p_proposal_form_number
        and	 proposal_budget_category_code IN (
		select 	budget_category_code
		from 	igw_report_budget_seed
		where 	proposal_form_number = p_proposal_form_number
		and	proposal_budget_category_code = rec_parent_category.proposal_budget_category_code)
        group by proposal_id, version_id, budget_period_id;

Line: 756

            select proposal_budget_category
            into   l_budget_category
            from   igw_report_budget_seed
            where  proposal_form_number = p_proposal_form_number
            and	   budget_category_code = rec_parent_category.proposal_budget_category_code;

Line: 764

              select meaning
              into   l_budget_category
              from   igw_lookups_v
              where  lookup_type = 'IGW_BUDGET_CATEGORY'
              and    lookup_code = rec_parent_category.proposal_budget_category_code;

Line: 776

            update igw_report_budget
            set    period_total_direct_cost = l_period_direct_cost_amt
	    ,      eb_total = l_period_eb_amt
            where  proposal_form_number = p_proposal_form_number
            and    proposal_id = p_proposal_id
	    and    version_id = l_version_id
  	    and	 budget_period_id = l_budget_period_id
	    and	 proposal_budget_category = l_budget_category
            and    proposal_budget_category_code = rec_parent_category.proposal_budget_category_code;

Line: 792

            insert into igw_report_budget(
					proposal_id
					,version_id
					,budget_period_id
					,proposal_budget_category
					,proposal_budget_category_code
					,period_total_direct_cost
					,eb_total
					,proposal_form_number
					,recently_updated_flag
							)
				values
	                	      ( p_proposal_id
					,l_version_id
					,l_budget_period_id
                                    	,l_budget_category
					,rec_parent_category.proposal_budget_category_code
					,l_period_direct_cost_amt
					,l_period_eb_amt
					,p_proposal_form_number
					,'Y');

Line: 815

        update igw_report_budget
        set    recently_updated_flag = 'N'
        where  proposal_form_number = p_proposal_form_number
        and    proposal_id = p_proposal_id
        and    proposal_budget_category_code IN (
	       select 	budget_category_code
	       from 	igw_report_budget_seed
	       where 	proposal_form_number = p_proposal_form_number
	       and	proposal_budget_category_code =                                   rec_parent_category.proposal_budget_category_code);

Line: 826

          update igw_report_budget
          set    recently_updated_flag = 'Y'
          where  proposal_form_number = p_proposal_form_number
          and    proposal_id = p_proposal_id
          and    proposal_budget_category_code = rec_parent_category.proposal_budget_category_code;

Line: 854

          select  sum(base_amt)	period_amt
	  ,	  sum(eb_cost)	eb_amt
	  ,	  budget_period_id
	  into	  l_period_direct_cost_amt
          ,	  l_period_eb_amt
	  ,	  l_budget_period_id
    	  from	  igw_budget_category_v
	  where	budget_category_code NOT IN
               (
		select budget_category_code
		from	igw_report_budget_seed
		where	proposal_form_number = p_proposal_form_number)
	  and	  proposal_id = p_proposal_id
          and 	  version_id = l_version_id
	  and	  budget_period_id = rec_budget_period.budget_period_id
	  group by proposal_id, version_id, budget_period_id;

Line: 877

	update 	igw_report_budget
	set	period_total_direct_cost = l_period_direct_cost_amt
	,	eb_total = l_period_eb_amt
	where	proposal_form_number = p_proposal_form_number
	and	proposal_id = p_proposal_id
	and	version_id = l_version_id
	and	budget_period_id = rec_budget_period.budget_period_id
	and	proposal_budget_category_code = '39';

Line: 920

    	select 	proposal_budget_category
	,	budget_category_code
	,	details_required_flag
	from	igw_report_budget_seed
	where   proposal_form_number = p_proposal_form_number
        and     details_required_flag = 'Y';

Line: 928

	select 	line_item_description
	,	line_item_cost
	from	igw_budget_line_category_v	pbd
	where 	pbd.proposal_id = p_proposal_id
	and	pbd.version_id = l_version_id
	and	pbd.budget_period_id = 1
	and	pbd.budget_category_code =l_budget_category_code;

Line: 955

    delete from igw_report_itemized_budget
    where p_proposal_form_number = p_proposal_form_number
    and   proposal_id = p_proposal_id
    and   version_id = l_version_id
    and	  proposal_form_number = p_proposal_form_number;

Line: 977

            insert into igw_report_itemized_budget (	proposal_id
							,version_id
							,budget_period_id
							,proposal_budget_category
							,expenditure_description
							,proposal_form_number)
						values
						     (	p_proposal_id
							,l_version_id
							,1
							,rec_budget_category.proposal_budget_category
							,l_expenditure_description
							,p_proposal_form_number);

Line: 1023

    select abstract
    from igw_prop_abstracts
    where proposal_id = p_proposal_id
    and   abstract_type_code =  p_abstract_type_code;

Line: 1051

    select answer,
           explanation
    into  p_response1,
          p_response2
    from igw_prop_questions
    where proposal_id = p_proposal_id and
          question_number = p_question_no;

Line: 1059

   select answer,
          explanation
   into   p_response1,
          p_response2
   from   igw_org_questions
   where  organization_id = p_organization_id
   and    question_number = p_question_no;

Line: 1067

   select answer,
          explanation
   into p_response1,
        p_response2
   from igw_prop_person_questions
   where proposal_id = p_proposal_id and
         question_number = p_question_no and
         party_id = p_party_id;

Line: 1091

    select answer
    into  v_response
    from igw_prop_questions
    where proposal_id = p_proposal_id and
          question_number = p_question_no;

Line: 1097

   select answer
   into   v_response
   from   igw_org_questions
   where  organization_id = p_organization_id
   and    question_number = p_question_no;

Line: 1103

   select answer
   into v_response
   from igw_prop_person_questions
   where proposal_id = p_proposal_id and
         question_number = p_question_no and
         party_id = p_party_id;

Line: 1124

    select explanation
    into  v_explanation
    from igw_prop_questions
    where proposal_id = p_proposal_id and
          question_number = p_question_no;

Line: 1130

   select explanation
   into   v_explanation
   from   igw_org_questions
   where  organization_id = p_organization_id
   and    question_number = p_question_no;

Line: 1136

   select explanation
   into v_explanation
   from igw_prop_person_questions
   where proposal_id = p_proposal_id and
         question_number = p_question_no and
         party_id = p_person_id;

Line: 1155

select no_of_subjects
into v_subjects
from igw_study_titles ST,
     igw_subject_information SI
where ST.proposal_id = p_proposal_id and
      ST.study_title_id = SI.study_title_id and
      SI.subject_race_code = p_subject_race and
      SI.subject_type_code = p_subject_gender and
      SI.study_title_id    = p_study_title_id;

Line: 1175

  select  decode(segment_p,'SEGMENT1',pjd.segment1,
                         'SEGMENT2',pjd.segment2,
                         'SEGMENT3',pjd.segment3,
                         'SEGMENT4',pjd.segment4,
                         'SEGMENT5',pjd.segment5,
                         'SEGMENT6',pjd.segment6,
                         'SEGMENT7',pjd.segment7,
                         'SEGMENT8',pjd.segment8,
                         'SEGMENT9',pjd.segment9,
                         'SEGMENT10',pjd.segment10,
                         'SEGMENT11',pjd.segment11,
                         'SEGMENT12',pjd.segment12,
                         'SEGMENT13',pjd.segment13,
                         'SEGMENT14',pjd.segment14,
                         'SEGMENT15',pjd.segment15,
                         'SEGMENT16',pjd.segment16,
                         'SEGMENT17',pjd.segment17,
                         'SEGMENT18',pjd.segment18,
                         'SEGMENT19',pjd.segment19,
                         'SEGMENT20',pjd.segment20,
                         'SEGMENT21',pjd.segment21,
                         'SEGMENT22',pjd.segment22,
                         'SEGMENT23',pjd.segment23,
                         'SEGMENT24',pjd.segment24,
                         'SEGMENT25',pjd.segment25,
                         'SEGMENT26',pjd.segment26,
                         'SEGMENT27',pjd.segment27,
                         'SEGMENT28',pjd.segment28,
                         'SEGMENT29',pjd.segment29,
                         'SEGMENT30',pjd.segment30)
   FROM   per_position_definitions pjd,
          per_all_positions        pap,
          per_assignments_x        paf,
          per_people_x             ppx
   WHERE  ppx.person_id              = paf.person_id
   and    ppx.business_group_id      = paf.business_group_id
   and    paf.primary_flag           = 'Y'
   and    paf.position_id            = pap.position_id
   and    pap.position_definition_id = pjd.position_definition_id
   and    ppx.person_id              = person_id_v;

Line: 1242

  cursor c1 is select phone_number
  from per_phones
  where parent_id = v_person_id and
     parent_table = 'PER_ALL_PEOPLE_F' and
     phone_type = v_phone_type and
     date_to is null;

Line: 1271

  SELECT PER_D.DEGREE
  FROM IGW_PROP_PERSON_DEGREES PROP_D,
       IGW_PERSON_DEGREES PER_D
  WHERE PER_D.PERSON_DEGREE_ID = PROP_D.PERSON_DEGREE_ID AND
      PROP_D.SHOW_FLAG = 'Y' AND
      PER_D.PARTY_ID = party_id_p and
      PROP_D.proposal_id = proposal_id_p
  ORDER BY PROP_D.DEGREE_SEQUENCE;

Line: 1318

select count(*)
into row_count
from igw_org_types
where organization_id = p_org_id
and   organization_type_code in (p_org_type1, p_org_type2, p_org_type3);

Line: 1337

     select party_name
     into   l_org_party_name
     from   hz_parties
     where  party_id = p_party_id
     and    party_type = 'ORGANIZATION';

Line: 1343

     select name
     into   l_org_party_name
     from   hr_organization_units
     where  organization_id = p_org_id;