APPS.AD_PA_CRITERIA_UTIL SQL Statements

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

Line: 4

 procedure update_rp
is
  l_advisor_criteria_id          ad_pa_criteria.ADVISOR_CRITERIA_ID%TYPE;

Line: 8

  l_last_update_date             date;

Line: 11

  l_last_update_date             := sysdate;

Line: 29

    select advisor_criteria_id, pre_seeded_flag, created_by  into l_advisor_criteria_id,
     l_pre_seeded, l_created_by
      from AD_PA_CRITERIA
       where advisor_criteria_id ='Recommended Patches'
        and pre_seeded_flag = 'Y';

Line: 37

      update AD_PA_CRITERIA
      set created_by = 2, last_updated_by = 2
      where advisor_criteria_id ='Recommended Patches'
      and pre_seeded_flag = 'Y';

Line: 45

    insert into AD_PA_CRITERIA
      (advisor_criteria_id, advisor_criteria_description,pre_seeded_flag,
      creation_date,last_update_date,last_updated_by,created_by)
    values('Recommended Patches','Recommended Patches for Current Codelevel','Y',sysdate,sysdate,2,2);

Line: 54

   delete from ad_pa_criteria_prod_fams
    where advisor_criteria_id = 'Recommended Patches';

Line: 57

	 insert into ad_pa_criteria_prod_fams
    (PRODUCT_FAMILY_ABBREVIATION,ADVISOR_CRITERIA_ID,RECOMMENDED_PATCH_FLAG,NEW_CODE_LEVEL_FLAG,LICENSED_FLAG,
	  CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
   select distinct pfm.product_family_abbreviation,'Recommended Patches','Y','N','Y',sysdate,1,sysdate,1
	  from fnd_product_installations fpi, fnd_application a,
			ad_pm_product_info pi, ad_pm_prod_family_map pfm
		where fpi.status in ('S', 'I')
     and fpi.application_id = a.application_id
     and a.application_short_name= pi.application_short_name
     and pi.PSEUDO_PRODUCT_FLAG='N'
     and pi.product_abbreviation = pfm.product_abbreviation ;

Line: 71

     insert into ad_pa_criteria_prod_fams
    (PRODUCT_FAMILY_ABBREVIATION,ADVISOR_CRITERIA_ID,RECOMMENDED_PATCH_FLAG,NEW_CODE_LEVEL_FLAG,LICENSED_FLAG,
	  CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
   select distinct pi.product_abbreviation,'Recommended Patches', 'Y', 'N', 'N',sysdate, 1, sysdate, 1
    from ad_pm_product_info pi
     where pi.pseudo_product_flag='Y'
      and pi.product_family_flag='Y'
      and not exists
       (select 'X' from ad_pa_criteria_prod_fams adf
        where adf.product_family_abbreviation= pi.product_abbreviation
         and adf.advisor_criteria_id='Recommended Patches');

Line: 87

		     select pi.PRODUCT_FAMILY_ABBREVIATION adi_prod_abbr, pfm.product_family_abbreviation adp_prod_abbr  from ad_pm_product_info pi, ad_pa_criteria_prod_fams pfm
		      where  pfm.PRODUCT_FAMILY_ABBREVIATION = pi.PRODUCT_FAMILY_ABBREVIATION
            and  pfm.ADVISOR_CRITERIA_ID ='Recommended Patches' for update of pfm.product_family_abbreviation;

Line: 94

		             update ad_pa_criteria_prod_fams
	               	set product_family_abbreviation = prod_abb.adi_prod_abbr
                   where current of prod_family;

Line: 104

     'Attempting to insert a duplicate record '||
       'into AD_PA_CRITERIA_PROD_FAMS advisor_criteria_id =  '
         || l_advisor_criteria_id || ' and pre_seeded_flag '||
           l_pre_seeded);

Line: 110

  update ad_pa_criteria
   set last_update_date = l_last_update_date
    where advisor_criteria_id ='Recommended Patches';

Line: 113

end update_rp;

Line: 116

procedure update_nc
is
  l_advisor_criteria_id          ad_pa_criteria.ADVISOR_CRITERIA_ID%TYPE;

Line: 120

  l_last_update_date             date;

Line: 123

  l_last_update_date             := sysdate;

Line: 142

    select advisor_criteria_id, pre_seeded_flag, created_by  into l_advisor_criteria_id,
     l_pre_seeded, l_created_by
      from AD_PA_CRITERIA
      where advisor_criteria_id ='New Codelevels'
       and pre_seeded_flag = 'Y';

Line: 149

      update AD_PA_CRITERIA
      set created_by = 2, last_updated_by = 2
      where advisor_criteria_id ='New Codelevels'
       and pre_seeded_flag = 'Y';

Line: 157

      insert into AD_PA_CRITERIA
       (advisor_criteria_id, advisor_criteria_description,pre_seeded_flag,
       creation_date,last_update_date,last_updated_by,created_by)
     values('New Codelevels','New Codelevels','Y',sysdate,sysdate,2,2);

Line: 164

    delete from ad_pa_criteria_prod_fams
     where advisor_criteria_id = 'New Codelevels';

Line: 167

     insert into ad_pa_criteria_prod_fams
    (PRODUCT_FAMILY_ABBREVIATION,ADVISOR_CRITERIA_ID,RECOMMENDED_PATCH_FLAG,NEW_CODE_LEVEL_FLAG,LICENSED_FLAG,
	  CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    select distinct pfm.product_family_abbreviation,'New Codelevels','N','Y','Y',sysdate,1,sysdate,1
	   from fnd_product_installations fpi, fnd_application a,
			ad_pm_product_info pi, ad_pm_prod_family_map pfm
		   where fpi.status in ('S', 'I')
        and fpi.application_id = a.application_id
        and a.application_short_name= pi.application_short_name
        and  pi.PSEUDO_PRODUCT_FLAG='N'
        and pi.product_abbreviation = pfm.product_abbreviation ;

Line: 179

     insert into ad_pa_criteria_prod_fams
     (PRODUCT_FAMILY_ABBREVIATION,ADVISOR_CRITERIA_ID,RECOMMENDED_PATCH_FLAG,NEW_CODE_LEVEL_FLAG,LICENSED_FLAG,
	   CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
      select distinct pi.product_abbreviation,'New Codelevels', 'N', 'Y', 'N',sysdate, 1, sysdate, 1
      from ad_pm_product_info pi
       where pi.pseudo_product_flag='Y'
        and pi.product_family_flag='Y'
        and not exists
        (select 'X' from ad_pa_criteria_prod_fams adf
         where adf.product_family_abbreviation= pi.product_abbreviation
          and adf.advisor_criteria_id='New Codelevels');

Line: 195

		   select adi.PRODUCT_FAMILY_ABBREVIATION adi_prod_abbr, adp.product_family_abbreviation adp_prod_abbr  from ad_pm_product_info adi, ad_pa_criteria_prod_fams adp
		   where  adp.PRODUCT_FAMILY_ABBREVIATION = adi.PRODUCT_FAMILY_ABBREVIATION
        and  adp.ADVISOR_CRITERIA_ID ='New Codelevels' for update of adp.product_family_abbreviation;

Line: 202

		      update ad_pa_criteria_prod_fams
		       set product_family_abbreviation = prod_abb.adi_prod_abbr
          where current of prod_family;

Line: 212

     'Attempting to insert a duplicate record '||
       'into AD_PA_CRITERIA_PROD_FAMS advisor_criteria_id =  '||
          l_advisor_criteria_id || ' and pre_seeded_flag '||
           l_pre_seeded);

Line: 218

  update ad_pa_criteria
   set last_update_date = l_last_update_date
    where advisor_criteria_id ='New Codelevels';

Line: 221

end update_nc;

Line: 223

procedure update_rpandnc
is
  l_advisor_criteria_id          ad_pa_criteria.ADVISOR_CRITERIA_ID%TYPE;

Line: 227

  l_last_update_date             date;

Line: 230

  l_last_update_date             := sysdate;

Line: 249

    select advisor_criteria_id, pre_seeded_flag, created_by  into l_advisor_criteria_id,
     l_pre_seeded, l_created_by
      from AD_PA_CRITERIA
      where advisor_criteria_id ='Recommended Patches and New Codelevels'
       and pre_seeded_flag = 'Y';

Line: 256

      update AD_PA_CRITERIA
      set created_by = 2, last_updated_by = 2
      where advisor_criteria_id ='Recommended Patches and New Codelevels'
       and pre_seeded_flag = 'Y';

Line: 264

     insert into AD_PA_CRITERIA
       (advisor_criteria_id, advisor_criteria_description,pre_seeded_flag,
       creation_date,last_update_date,last_updated_by,created_by)
     values('Recommended Patches and New Codelevels','Current Recommended Patches and New Codelevels','Y',sysdate,sysdate,2,2);

Line: 271

    delete from ad_pa_criteria_prod_fams
     where advisor_criteria_id = 'Recommended Patches and New Codelevels';

Line: 274

     insert into ad_pa_criteria_prod_fams
    (PRODUCT_FAMILY_ABBREVIATION,ADVISOR_CRITERIA_ID,RECOMMENDED_PATCH_FLAG,NEW_CODE_LEVEL_FLAG,LICENSED_FLAG,
	  CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
    select distinct pfm.product_family_abbreviation,'Recommended Patches and New Codelevels','Y','Y','Y',sysdate,1,sysdate,1
	   from fnd_product_installations fpi, fnd_application a,
			ad_pm_product_info pi, ad_pm_prod_family_map pfm
		   where fpi.status in ('S', 'I')
        and fpi.application_id = a.application_id
        and a.application_short_name= pi.application_short_name
        and  pi.PSEUDO_PRODUCT_FLAG='N'
        and pi.product_abbreviation = pfm.product_abbreviation ;

Line: 286

     insert into ad_pa_criteria_prod_fams
    (PRODUCT_FAMILY_ABBREVIATION,ADVISOR_CRITERIA_ID,RECOMMENDED_PATCH_FLAG,NEW_CODE_LEVEL_FLAG,LICENSED_FLAG,
	  CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY)
     select distinct pi.product_abbreviation,'Recommended Patches and New Codelevels', 'Y', 'Y', 'N',sysdate, 1, sysdate, 1
      from ad_pm_product_info pi
       where pi.pseudo_product_flag='Y'
        and pi.product_family_flag='Y'
        and not exists
        (select 'X' from ad_pa_criteria_prod_fams adf
         where adf.product_family_abbreviation= pi.product_abbreviation
          and adf.advisor_criteria_id='Recommended Patches and New Codelevels');

Line: 302

		   select adi.PRODUCT_FAMILY_ABBREVIATION adi_prod_abbr, adp.product_family_abbreviation adp_prod_abbr  from ad_pm_product_info adi, ad_pa_criteria_prod_fams adp
		   where  adp.PRODUCT_FAMILY_ABBREVIATION = adi.PRODUCT_FAMILY_ABBREVIATION
        and  adp.ADVISOR_CRITERIA_ID ='Recommended Patches and New Codelevels' for update of adp.product_family_abbreviation;

Line: 309

		      update ad_pa_criteria_prod_fams
		       set product_family_abbreviation = prod_abb.adi_prod_abbr
          where current of prod_family;

Line: 319

     'Attempting to insert a duplicate record '||
       'into AD_PA_CRITERIA_PROD_FAMS advisor_criteria_id =  '||
          l_advisor_criteria_id || ' and pre_seeded_flag '||
           l_pre_seeded);

Line: 325

  update ad_pa_criteria
   set last_update_date = l_last_update_date
    where advisor_criteria_id ='Recommended Patches and New Codelevels';

Line: 328

end update_rpandnc;

Line: 331

procedure update_all
is

  --
  -- Create/update all three of the pre-seeded Criteria Sets
  -- Delete rows from ad_pa_criteria and ad_pa_criteria_products for 11.5.9
 x_statement varchar2(100);

Line: 349

         delete from ad_pa_criteria
         where advisor_criteria_id = 'RecPatches and New Codelevels';

Line: 351

         delete from ad_pa_criteria_prod_fams
         where advisor_criteria_id = 'RecPatches and New Codelevels';

Line: 355

	 --vsigamal 10-Oct-2006 Bug # 5575432 - This is to delete existing old advisor_criteria_id from the database
	 delete from ad_pa_criteria_prod_fams
	 where advisor_criteria_id in ( select advisor_criteria_id
                                        from ad_pa_criteria
				        where pre_seeded_flag='Y');

Line: 360

	 delete from ad_pa_criteria
	 where pre_seeded_flag='Y';

Line: 369

         select 'y' into x_statement
         from dba_tables
         where table_name = 'AD_PA_CRITERIA_PRODUCTS'
         and owner = G_UN_FND;

Line: 376

              delete from ad_pa_criteria ac
              where advisor_criteria_id in ( select advisor_criteria_id
                                             from ad_pa_criteria_products acp
                                             where acp.advisor_criteria_id = ac.advisor_criteria_id);

Line: 380

	      DELETE from AD_PA_CRITERIA_products;

Line: 392

  update_rp;

Line: 393

  update_nc;

Line: 394

  update_rpandnc;

Line: 395

end update_all;