APPS.JTY_MIGRATION_PVT SQL Statements

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

Line: 52

  update jtf_terr_types_all
  set enabled_flag = 'Y'
  where sysdate between start_date_active and nvl(end_date_active, sysdate);

Line: 81

Procedure update_rsc_access
IS

BEGIN

  update jtf_terr_rsc_access_all jtra
  set jtra.trans_access_code = (
    select decode(jtr1.full_access_flag,'Y','FULL_ACCESS','VIEW')
    from jtf_terr_rsc_all jtr1
    where jtr1.TERR_RSC_ID = jtra.terr_rsc_id)
  where jtra.terr_rsc_id in
         (select jtr2.terr_rsc_id
            from jtf_terr_rsc_all jtr2
               , jtf_terr_usgs_all jtu
            where jtr2.terr_id = jtu.terr_id
              and jtu.source_id = -1001)
   and jtra.trans_access_code is null;

Line: 100

  update jtf_terr_rsc_access_all jtra
  set jtra.trans_access_code = (
    select decode(jtr1.primary_contact_flag,'Y','TEAM_LEADER','DEFAULT')
    from jtf_terr_rsc_all jtr1
    where jtr1.TERR_RSC_ID = jtra.terr_rsc_id)
  where jtra.terr_rsc_id in
         (select jtr2.terr_rsc_id
            from jtf_terr_rsc_all jtr2
               , jtf_terr_usgs_all jtu
            where jtr2.terr_id = jtu.terr_id
              and jtu.source_id = -1002)
   and jtra.trans_access_code is null;

Line: 114

  update jtf_terr_rsc_access_all jtra
  set jtra.trans_access_code = (
    select decode(jtr1.primary_contact_flag,'Y','PRIMARY_CONTACT','DEFAULT')
    from jtf_terr_rsc_all jtr1
    where jtr1.TERR_RSC_ID = jtra.terr_rsc_id)
  where jtra.terr_rsc_id in
         (select jtr2.terr_rsc_id
            from jtf_terr_rsc_all jtr2
               , jtf_terr_usgs_all jtu
            where jtr2.terr_id = jtu.terr_id
              and jtu.source_id = -1003)
   and jtra.trans_access_code is null;

Line: 128

  update jtf_terr_rsc_access_all jtra
  set jtra.trans_access_code = 'DEFAULT'
   where  jtra.trans_access_code is null;

Line: 143

END update_rsc_access;

Line: 151

Procedure update_terr_enddate_active
IS

BEGIN

  Update jtf_terr_all jterr
  set jterr.end_date_active = jterr.start_date_active  + 3652
  where jterr.end_date_active is null;

Line: 160

  Update jtf_terr_rsc_all jtr
  set jtr.end_date_active = jtr.start_date_active  + 3652
  where jtr.end_date_active is null;

Line: 174

END update_terr_enddate_active;

Line: 182

Procedure update_self_service_flag
IS

BEGIN

  Update jtf_terr_all jtabs
  set enable_self_service = 'Y'
  where (GEO_TERR_FLAG = 'Y' or NAMED_ACCOUNT_FLAG = 'Y')
         and enable_self_service is null;

Line: 201

END update_self_service_flag;

Line: 211

Procedure update_terr_type_for_terr
IS

BEGIN

   -- Update the named account terrtories with territory template

   update jtf_terr_all jt
   set jt.territory_type_id = -1
   where jt.named_account_flag = 'Y'
       and jt.territory_type_id is null;

Line: 225

   update jtf_terr_all jt
   set jt.territory_type_id = -2
   where jt.GEO_TERR_FLAG = 'Y'
       and jt.territory_type_id is null;

Line: 232

   update jtf_terr_all jt
   set jt.territory_type_id =
    (select jttu.terr_type_id
      from  jtf_terr_type_usgs_all jttu
          , jtf_terr_usgs_all jtu
      where jttu.source_id = jtu.source_id
        and jt.terr_id = jtu.terr_id
        and jttu.created_by = 2        -- territory templates shipped by Oracle
        and jttu.terr_type_id not in (-1, -2)    -- Eliminate named account
        and jtu.org_id = jttu.org_id)
   where jt.territory_type_id is null
     and jt.terr_id <> 1;

Line: 254

END update_terr_type_for_terr;

Line: 260

Procedure delete_resource_trx_type
IS

BEGIN

--      delete from jtf_qual_usgs_all where qual_type_usg_id
--      in (select qual_type_usg_id
--          from jtf_qual_type_usgs_all
--	  where qual_type_id = -1001);

Line: 270

      delete from  jtf_qual_type_usgs_all
	where qual_type_id = -1001;

Line: 273

      delete from  jtf_qual_types_all
	where qual_type_id = -1001;

Line: 292

END delete_resource_trx_type;

Line: 309

    select 'Y'
    into l_migration_complete
    from jtf_terr_types_all
    where substr(name,1,7) = 'General'
      and created_by = 2
      and rownum < 2;

Line: 340

      insert into jtf_terr_types_all
      ( TERR_TYPE_ID
      , LAST_UPDATED_BY
      , LAST_UPDATE_DATE
      , CREATED_BY
      , CREATION_DATE
      , APPLICATION_SHORT_NAME
      , NAME
      , ENABLED_FLAG
      , DESCRIPTION
      , START_DATE_ACTIVE
      , ORG_ID
      , ORIG_SYSTEM_REFERENCE_ID)
      select  jtf_terr_types_s.nextval
      , 2
      , sysdate
      , 2
      , sysdate
      , 'JTF'
      , 'General ' || meaning -- Will work with NLS team to check how to do translation.
      , 'Y'
      , 'General ' || meaning
      , sysdate
      , org_id
      , source_id
      from (select distinct jtu.org_id
             , jtu.source_id
             , jsa.meaning
        from jtf_terr_usgs_all jtu
           , jtf_sources_all jsa
        where jsa.source_id = jtu.source_id);

Line: 390

      insert into jtf_terr_types_all
      ( TERR_TYPE_ID
      , LAST_UPDATED_BY
      , LAST_UPDATE_DATE
      , CREATED_BY
      , CREATION_DATE
      , APPLICATION_SHORT_NAME
      , NAME
      , ENABLED_FLAG
      , DESCRIPTION
      , START_DATE_ACTIVE
      , ORG_ID
      , ORIG_SYSTEM_REFERENCE_ID)
      select -1
      , 2
      , sysdate
      , 2
      , sysdate
      , 'JTF'
      , 'Named Account'
      , 'Y'
      , 'Named Account Territories'
      , sysdate
      , organization_id
      , -1001
      from (select distinct organization_id
            from hr_operating_units);

Line: 425

      insert into jtf_terr_types_all
      ( TERR_TYPE_ID
      , LAST_UPDATED_BY
      , LAST_UPDATE_DATE
      , CREATED_BY
      , CREATION_DATE
      , APPLICATION_SHORT_NAME
      , NAME
      , ENABLED_FLAG
      , DESCRIPTION
      , START_DATE_ACTIVE
      , ORG_ID
      , ORIG_SYSTEM_REFERENCE_ID)
      select -2
      , 2
      , sysdate
      , 2
      , sysdate
      , 'JTF'
      , 'Geography'
      , 'Y'
      , 'Geography Territories'
      , sysdate
      , org_id
      , -1001
      from (select distinct org_id
          from jtf_terr_all
          where geo_terr_flag = 'Y');

Line: 474

      insert into jtf_terr_type_usgs_all
      ( TERR_TYPE_USG_ID
      , LAST_UPDATED_BY
      , LAST_UPDATE_DATE
      , CREATED_BY
      , CREATION_DATE
      , TERR_TYPE_ID
      , SOURCE_ID
      , ORG_ID
      )
      select jtf_terr_type_usgs_s.nextval
      , 2
      , sysdate
      , 2
      , sysdate
      , terr_type_id
      , ORIG_SYSTEM_REFERENCE_ID    -- Used this column to temp store source_id
      , org_id
      from jtf_terr_types_all
      where  ORIG_SYSTEM_REFERENCE_ID is not null
        and  CREATED_BY = 2;

Line: 518

      insert into jtf_type_qtype_usgs_all
       ( TYPE_QTYPE_USG_ID
       , LAST_UPDATED_BY
       , LAST_UPDATE_DATE
       , CREATED_BY
       , CREATION_DATE
       , last_update_login
       , TERR_TYPE_ID
       , QUAL_TYPE_USG_ID
       , ORG_ID)
       select jtf_type_qtype_usgs_s.nextval
           , 2
           , sysdate
           , 2
           , sysdate
           , 2
           , jttu.terr_type_id
           , jqtu.qual_type_usg_id
           , jttu.org_id
       from jtf_terr_type_usgs_all jttu
          , jtf_qual_type_usgs_all jqtu
       where jqtu.source_id = jttu.source_id
       and jqtu.qual_type_usg_id <> -1005  -- Exclude service account transaction type
       and jttu.created_by = 2;

Line: 564

       insert into jtf_terr_type_qual_all
          (TERR_TYPE_QUAL_ID
           , LAST_UPDATED_BY
           , LAST_UPDATE_DATE
           , CREATED_BY
           , CREATION_DATE
           , QUAL_USG_ID
           , TERR_TYPE_ID
           , ORG_ID)
       select jtf_terr_type_qual_s.nextval
         , 2
         , sysdate
         , 2
         , sysdate
         , jqu.QUAL_USG_ID
         , jttu.terr_type_id
         , jttu.org_id
       from jtf_terr_type_usgs_all jttu
          , jtf_qual_type_usgs_all jqtu
          , jtf_qual_usgs_all jqu
       where jttu.created_by = 2
       and jqu.QUAL_TYPE_USG_ID = jqtu.qual_type_usg_id
       and jqtu.source_id = jttu.source_id
       and jttu.org_id = jqu.org_id
       and jttu.terr_type_id <> -1 -- For NA territories this table is not populated
       and jqu.ENABLED_FLAG = 'Y';

Line: 634

  select count(*) into l_count
  from jtf_terr_qual_all
  where qual_usg_id in (-1023,-1018);

Line: 639

    delete from jtf_seeded_qual_all_b
    where seeded_qual_id in (-1024, -1019);

Line: 642

    delete from jtf_seeded_qual_all_tl
    where seeded_qual_id in (-1024, -1019);

Line: 645

    delete from jtf_qual_usgs_all
    where qual_usg_id in (-1023,-1018);

Line: 680

 delete_resource_trx_type;