eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AD_ZD_ADOP SQL Statements

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

Line: 50


        v_str_common := 'Select BUG_NUMBER, APPLIED_FILE_SYSTEM_BASE,'||
'PATCH_FILE_SYSTEM_BASE,STATUS, NODE_NAME FROM  AD_ADOP_SESSION_PATCHES WHERE '||
'BUG_NUMBER <> '||''''||'CLONE'||''''||' AND BUG_NUMBER <>' ||
''''||'CONFIG_CLONE'||''''||' AND ADOP_SESSION_ID ='||adop_sessionID;

Line: 117


        (select distinct bug_number || ','  as bug_number
         from   ad_adop_session_patches
         where  PATCH_FILE_SYSTEM_BASE is NULL
         and    status = 'Y'
         order by bug_number )
    loop
      l_bug_numbers_list := l_bug_numbers_list || rec.bug_number;

Line: 359


PROCEDURE INSERT_INTO_PATCHES_TABLE(
                                    p_session_id IN NUMBER,
                                    p_bug_number IN VARCHAR2,
                                    p_patch_run_id IN NUMBER,
                                    p_appltop_base IN VARCHAR2,
                                    p_adpatch_options IN VARCHAR2,
                                    p_autoconfig_status IN VARCHAR2,
                                    p_start_date IN DATE,
                                    p_end_date IN DATE,
                                    p_patch_top IN VARCHAR2,
                                    p_driverfile_name IN VARCHAR2,
                                    p_status IN VARCHAR2
                                  )
IS
  l_mod_name              varchar2(25) := 'INSERT_INTO_PATCHES_TABLE';

Line: 384


        SELECT
                  fn.host node_name ,
                  aat.appl_top_id appl_top_id ,
                  EXTRACTVALUE(XMLType(TEXT),'//shared_file_system') is_shared
        FROM
                  fnd_nodes fn,
                  FND_OAM_CONTEXT_FILES focf,
                  fnd_product_groups fpg,
                  ad_appl_tops aat,
                  ad_releases ar
        WHERE     focf.NAME not in ('TEMPLATE','METADATA','config.txt') and focf.CTX_TYPE='A' and
                  (focf.status is null or upper(focf.status) in ('S','F')) and
                  EXTRACTVALUE(XMLType(focf.TEXT),'//file_edition_type') = 'run' and
                  focf.node_name=fn.host and
                  (fn.support_cp='Y' or fn.support_forms='Y' or
                   fn.support_web='Y' or fn.support_admin='Y') and
                  aat.appl_top_type='R' and aat.applications_system_name=fpg.applications_system_name and
                  aat.active_flag='Y' and
                  fpg.release_name=ar.major_version||'.'||ar.minor_version||'.'||ar.tape_version and
                  fpg.aru_release_name=ar.aru_release_name and
                  aat.name=EXTRACTVALUE(XMLType(focf.TEXT),'//APPL_TOP_NAME');

Line: 409


  select substr(machine, 1,decode(instr(machine, '.', 1,1),0,length(machine),instr(machine, '.', 1, 1)-1)) into l_invoking_node
  from v$session where audsid=USERENV('SESSIONID');

Line: 435


    select count(1) into l_exists from ad_adop_session_patches
    where ADOP_SESSION_ID=p_session_id and BUG_NUMBER=p_bug_number and
          APPLIED_FILE_SYSTEM_BASE=p_appltop_base and APPLTOP_ID=rec.appl_top_id and
          NODE_NAME=rec.node_name and DRIVER_FILE_NAME=p_driverfile_name;

Line: 441


      select STATUS into l_current_status from ad_adop_session_patches
      where ADOP_SESSION_ID=p_session_id and BUG_NUMBER=p_bug_number and
            APPLIED_FILE_SYSTEM_BASE=p_appltop_base and APPLTOP_ID=rec.appl_top_id and
            NODE_NAME=rec.node_name and DRIVER_FILE_NAME=p_driverfile_name;

Line: 450


        update ad_adop_session_patches
        set PATCHRUN_ID=p_patch_run_id, STATUS=l_status, AUTOCONFIG_STATUS=l_ac_status,
            START_DATE=p_start_date, END_DATE=p_end_date
        where ADOP_SESSION_ID=p_session_id and BUG_NUMBER=p_bug_number and APPLTOP_ID=rec.appl_top_id and
              NODE_NAME=rec.node_name and DRIVER_FILE_NAME=p_driverfile_name;

Line: 457


      log(l_mod_name,'STATEMENT','Inserting new AD_ADOP_SESSION_PATCHES table entry for patch #' || p_bug_number);

Line: 459


      INSERT INTO ad_adop_session_patches
        (ADOP_SESSION_ID, BUG_NUMBER, PATCHRUN_ID, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYSTEM_BASE,
         ADPATCH_OPTIONS, APPLTOP_ID, NODE_NAME, AUTOCONFIG_STATUS, START_DATE, END_DATE, PATCH_TOP, DRIVER_FILE_NAME)
      VALUES
        (p_session_id, p_bug_number, p_patch_run_id, l_status, p_appltop_base, NULL, p_adpatch_options,
         rec.appl_top_id, rec.node_name, l_ac_status, p_start_date, p_end_date, p_patch_top, p_driverfile_name);

Line: 474


END INSERT_INTO_PATCHES_TABLE;

Line: 481


    select max(adop_session_id) into l_session_id from ad_adop_sessions
    where appltop_id=p_appltop_id and node_name=p_node_name;

Line: 486


  select cutover_status into l_cutover_status
   from ad_adop_sessions
   where appltop_id=p_appltop_id
     and node_name=p_node_name
     and adop_session_id= l_session_id;

Line: 504


        select count(*) into l_is_locked from ad_adop_sessions where adop_session_id=0 and EDITION_NAME='LOCK';

Line: 506


          insert into ad_adop_sessions


          (ADOP_SESSION_ID,PREPARE_STATUS,APPLY_STATUS,FINALIZE_STATUS,CUTOVER_STATUS,CLEANUP_STATUS,ABORT_STATUS,STATUS,EDITION_NAME,NODE_NAME)
          values (0,'X','X','X','X','X','X','Y','LOCK',p_node_name);

Line: 537


        select count(*) into l_is_locked from ad_adop_sessions where adop_session_id=0 and EDITION_NAME='LOCK' and node_name=p_node_name;

Line: 539


          delete from ad_adop_sessions where adop_session_id=0 and EDITION_NAME='LOCK' and node_name=p_node_name;

Line: 559


|     This api updates the cutover phase for a patching cycle     |
|      of a multi node environment.                               |
+-----------------------------------------------------------------*/
procedure SET_CUTOVER_STATUS(p_appltop_id in number,p_node_name in varchar2,p_status in varchar2) is
   l_session_id       number;

Line: 565


  select max(adop_session_id) into l_session_id from ad_adop_sessions
    where appltop_id=p_appltop_id and node_name=p_node_name
      and prepare_status='Y' and apply_status='Y';

Line: 568


  update ad_adop_sessions set cutover_status=p_status
   where adop_session_id= l_session_id
     and appltop_id=p_appltop_id
     and node_name=p_node_name;

Line: 583


      select
      '/* '||e.object_name||' '||s.osuser||' '||s.username||' */' info,
      'ALTER SYSTEM KILL SESSION '||''''||s.sid||','||s.serial#||'''' kill
      from
      v$session s, v$process p, database_properties run -- /* run edition name */
      , dba_objects_ae e
      where s.type <> 'BACKGROUND' and p.addr    = s.paddr
      and run.property_name = 'DEFAULT_EDITION'
      and e.object_id = s.session_edition_id
      and e.object_name < run.property_value
      and s.username in
      ( select oracle_username from  fnd_oracle_userid
        where  read_only_flag in ('A', 'B', 'C', 'E', 'U') );

Line: 621


         update ad_adop_sessions set status='F'
         where adop_session_id=p_session_id;

Line: 656


        select count(*) into l_is_locked from ad_adop_session_patches where adop_session_id=0 and BUG_NUMBER=p_lock_name;

Line: 659


          insert into ad_adop_session_patches
          (ADOP_SESSION_ID,BUG_NUMBER,STATUS,NODE_NAME)
          values (0,p_lock_name,'N',p_node_name);

Line: 682


     select count(1) into l_is_locked from ad_adop_session_patches
      where adop_session_id=0 and BUG_NUMBER=p_lock_name
            and node_name=p_node_name;

Line: 687


       delete from ad_adop_session_patches
        where adop_session_id=0 and BUG_NUMBER=p_lock_name
              and node_name=p_node_name;

Line: 709


  delete AD_SNAPSHOT_FILES
  where (snapshot_id, file_id, nvl(containing_file_id, -2)) in
        (select ou.snapshot_id, ou.file_id, nvl(ou.containing_file_id, -2)
         from   AD_SNAPSHOT_FILES OU, AD_SNAPSHOTS OUASN,
                AD_APPL_TOPS ouaat, FND_PRODUCT_GROUPS oufpg,
                AD_RELEASES rel
         where  OU.SNAPSHOT_ID=OUASN.SNAPSHOT_ID
         and    OUASN.SNAPSHOT_TYPE='P'
         and    ouasn.snapshot_name='CURRENT_VIEW'
         and    ouasn.appl_top_id=ouaat.appl_top_id
         and    ouaat.appl_top_id=nvl(p_appl_top_id, ouaat.appl_top_id)
         and    ouaat.appl_top_type='R'
         and    ouaat.applications_system_name=oufpg.applications_system_name
         and    oufpg.product_group_id=1
         and    rel.aru_release_name=oufpg.aru_release_name
         and    ouasn.release_id=rel.release_id
         and    ouaat.active_flag='Y'
         and    oufpg.release_name=rel.major_version||'.'||
                                   rel.minor_version||'.'||
                                   rel.tape_version
         and not exists (
           select 'x'
           from   AD_SNAPSHOT_FILES ASF, AD_SNAPSHOTS ASN
           where  ASF.SNAPSHOT_ID=ASN.SNAPSHOT_ID
           and    ASN.SNAPSHOT_TYPE='C'
           and    ASN.SNAPSHOT_NAME=OUASN.SNAPSHOT_NAME
           and    ASN.APPL_TOP_ID=OUASN.APPL_TOP_ID
           and    OU.FILE_ID=ASF.FILE_ID
           and    NVL(OU.CONTAINING_FILE_ID, -2)=NVL(ASF.CONTAINING_FILE_ID, -2)
           and    OUASN.APPL_TOP_ID=ASN.APPL_TOP_ID)
        );

Line: 741


  delete AD_SNAPSHOT_FILES
  where (snapshot_id, file_id, nvl(containing_file_id, -2)) in
        (select ou.snapshot_id, ou.file_id, nvl(ou.containing_file_id, -2)
         from   AD_SNAPSHOT_FILES OU, AD_SNAPSHOTS OUASN,
                AD_APPL_TOPS ouaat, FND_PRODUCT_GROUPS oufpg,
                AD_RELEASES rel
         where  OU.SNAPSHOT_ID=OUASN.SNAPSHOT_ID
         and    OUASN.SNAPSHOT_TYPE='Q'
         and    ouasn.snapshot_name='GLOBAL_VIEW'
         and    ouasn.appl_top_id=ouaat.appl_top_id
         and    ouaat.appl_top_type='G'
         and    ouaat.applications_system_name=oufpg.applications_system_name
         and    oufpg.product_group_id=1
         and    rel.aru_release_name=oufpg.aru_release_name
         and    ouasn.release_id=rel.release_id
         and    ouaat.active_flag='Y'
         and    oufpg.release_name=rel.major_version||'.'||
                                   rel.minor_version||'.'||
                                   rel.tape_version
         and not exists (
           select 'x'
           from   AD_SNAPSHOT_FILES ASF, AD_SNAPSHOTS ASN
           where  ASF.SNAPSHOT_ID=ASN.SNAPSHOT_ID
           and    ASN.SNAPSHOT_TYPE='G'
           and    ASN.SNAPSHOT_NAME=OUASN.SNAPSHOT_NAME
           and    ASN.APPL_TOP_ID=OUASN.APPL_TOP_ID
           and    OU.FILE_ID=ASF.FILE_ID
           and    NVL(OU.CONTAINING_FILE_ID, -2)=NVL(ASF.CONTAINING_FILE_ID, -2)
           and    OUASN.APPL_TOP_ID=ASN.APPL_TOP_ID)
        );

Line: 774


  delete AD_SNAPSHOT_BUGFIXES
  where (SNAPSHOT_ID, BUGFIX_ID) in
        (select OU.SNAPSHOT_ID, OU.BUGFIX_ID
         from   AD_SNAPSHOT_BUGFIXES OU, AD_SNAPSHOTS OUASN,
                AD_APPL_TOPS ouaat, FND_PRODUCT_GROUPS oufpg,
                AD_RELEASES rel
         where  OU.SNAPSHOT_ID=OUASN.SNAPSHOT_ID
         and    OUASN.SNAPSHOT_TYPE='P'
         and    ouasn.snapshot_name='CURRENT_VIEW'
         and    ouasn.appl_top_id=ouaat.appl_top_id
         and    ouaat.appl_top_id=nvl(p_appl_top_id, ouaat.appl_top_id)
         and    ouaat.appl_top_type='R'
         and    ouaat.applications_system_name=oufpg.applications_system_name
         and    oufpg.product_group_id=1
         and    rel.aru_release_name=oufpg.aru_release_name
         and    ouasn.release_id=rel.release_id
         and    ouaat.active_flag='Y'
         and    oufpg.release_name=rel.major_version||'.'||
                                   rel.minor_version||'.'||
                                   rel.tape_version
         and not exists (
           select 'x'
           from   AD_SNAPSHOT_BUGFIXES ASF, AD_SNAPSHOTS ASN
           where  ASF.SNAPSHOT_ID=ASN.SNAPSHOT_ID
           and    ASN.SNAPSHOT_TYPE='C'
           and    ASN.SNAPSHOT_NAME=OUASN.SNAPSHOT_NAME
           and    ASN.APPL_TOP_ID=OUASN.APPL_TOP_ID
           and    OU.BUGFIX_ID=ASF.BUGFIX_ID
           and    OUASN.APPL_TOP_ID=ASN.APPL_TOP_ID)
        );

Line: 805


  delete AD_SNAPSHOT_BUGFIXES
  where (SNAPSHOT_ID, BUGFIX_ID) in
        (select OU.SNAPSHOT_ID, OU.BUGFIX_ID
         from   AD_SNAPSHOT_BUGFIXES OU, AD_SNAPSHOTS OUASN,
                AD_APPL_TOPS ouaat, FND_PRODUCT_GROUPS oufpg,
                AD_RELEASES rel
         where  OU.SNAPSHOT_ID=OUASN.SNAPSHOT_ID
         and    OUASN.SNAPSHOT_TYPE='Q'
         and    ouasn.snapshot_name='GLOBAL_VIEW'
         and    ouasn.appl_top_id=ouaat.appl_top_id
         and    ouaat.appl_top_type='G'
         and    ouaat.applications_system_name=oufpg.applications_system_name
         and    rel.aru_release_name=oufpg.aru_release_name
         and    ouasn.release_id=rel.release_id
         and    ouaat.active_flag='Y'
         and    oufpg.release_name=rel.major_version||'.'||
                                   rel.minor_version||'.'||
                                   rel.tape_version
         and not exists (
           select 'x'
           from   AD_SNAPSHOT_BUGFIXES ASF, AD_SNAPSHOTS ASN
           where  ASF.SNAPSHOT_ID=ASN.SNAPSHOT_ID
           and    ASN.SNAPSHOT_TYPE='G'
           and    ASN.SNAPSHOT_NAME=OUASN.SNAPSHOT_NAME
           and    ASN.APPL_TOP_ID=OUASN.APPL_TOP_ID
           and    OU.BUGFIX_ID=ASF.BUGFIX_ID)
        );

Line: 836


  select ASF1.SNAPSHOT_ID,
         ASF2.FILE_ID,
         ASF2.CONTAINING_FILE_ID,
         ASF2.FILE_VERSION_ID
  from   AD_SNAPSHOTS ASN1, AD_SNAPSHOT_FILES ASF1,
         AD_SNAPSHOTS ASN2, AD_SNAPSHOT_FILES ASF2,
         AD_APPL_TOPS AAT, FND_PRODUCT_GROUPS FPG,
         AD_RELEASES rel
  where  ASN1.SNAPSHOT_ID=ASF1.SNAPSHOT_ID
  and    ASN2.SNAPSHOT_ID=ASF2.SNAPSHOT_ID
  and    ASN1.SNAPSHOT_TYPE='P'
  and    ASN2.SNAPSHOT_TYPE='C'
  and    ASN1.SNAPSHOT_NAME='CURRENT_VIEW'
  and    ASN2.SNAPSHOT_NAME=ASN1.SNAPSHOT_NAME
  and    ASN1.APPL_TOP_ID=ASN2.APPL_TOP_ID
  and    ASF1.FILE_ID=ASF2.FILE_ID
  and    NVL(ASF1.CONTAINING_FILE_ID, -2)=NVL(ASF2.CONTAINING_FILE_ID, -2)
  and    NVL(ASF1.FILE_VERSION_ID, -2)<>NVL(ASF2.FILE_VERSION_ID, -2)
  and    ASN1.APPL_TOP_ID=AAT.APPL_TOP_ID
  and    AAT.APPL_TOP_ID=nvl(p_appl_top_id, AAT.APPL_TOP_ID)
  and    AAT.APPLICATIONS_SYSTEM_NAME=FPG.APPLICATIONS_SYSTEM_NAME
  and    fpg.PRODUCT_GROUP_ID=1
  and    rel.aru_release_name=fpg.aru_release_name
  and    asn1.release_id=rel.release_id
  and    asn2.release_id=rel.release_id
  and    aat.active_flag='Y'
  and    fpg.release_name=rel.major_version||'.'||
                          rel.minor_version||'.'||
                          rel.tape_version;

Line: 867


  select ASF1.SNAPSHOT_ID,
         ASF2.FILE_ID,
         ASF2.CONTAINING_FILE_ID,
         ASF2.FILE_VERSION_ID
  from   AD_SNAPSHOTS ASN1, AD_SNAPSHOT_FILES ASF1,
         AD_SNAPSHOTS ASN2, AD_SNAPSHOT_FILES ASF2,
         AD_APPL_TOPS AAT, FND_PRODUCT_GROUPS FPG,
         AD_RELEASES rel
  where  ASN1.SNAPSHOT_ID=ASF1.SNAPSHOT_ID
  and    ASN2.SNAPSHOT_ID=ASF2.SNAPSHOT_ID
  and    ASN1.SNAPSHOT_TYPE='Q'
  and    ASN2.SNAPSHOT_TYPE='G'
  and    ASN1.SNAPSHOT_NAME='GLOBAL_VIEW'
  and    ASN2.SNAPSHOT_NAME=ASN1.SNAPSHOT_NAME
  and    ASN1.APPL_TOP_ID=ASN2.APPL_TOP_ID
  and    ASF1.FILE_ID=ASF2.FILE_ID
  and    NVL(ASF1.CONTAINING_FILE_ID, -2)=NVL(ASF2.CONTAINING_FILE_ID, -2)
  and    NVL(ASF1.FILE_VERSION_ID, -2)<>NVL(ASF2.FILE_VERSION_ID, -2)
  and    ASN1.APPL_TOP_ID=AAT.APPL_TOP_ID
  and    AAT.APPLICATIONS_SYSTEM_NAME=FPG.APPLICATIONS_SYSTEM_NAME
  and    fpg.PRODUCT_GROUP_ID=1
  and    rel.aru_release_name=fpg.aru_release_name
  and    asn1.release_id=rel.release_id
  and    asn2.release_id=rel.release_id
  and    aat.active_flag='Y'
  and    fpg.release_name=rel.major_version||'.'||
                          rel.minor_version||'.'||
                          rel.tape_version;

Line: 899


        update AD_SNAPSHOT_FILES
        set    FILE_VERSION_ID=cr.FILE_VERSION_ID
        where SNAPSHOT_ID=cr.SNAPSHOT_ID
        and   file_id=cr.file_id
        and   nvl(containing_file_id, -2)=nvl(cr.containing_file_id, -2);

Line: 908


        update AD_SNAPSHOT_FILES
        set    FILE_VERSION_ID=globalr.FILE_VERSION_ID
        where SNAPSHOT_ID=globalr.SNAPSHOT_ID
        and   file_id=globalr.file_id
        and   nvl(containing_file_id, -2)=nvl(globalr.containing_file_id, -2);

Line: 917


  insert into ad_snapshot_files(SNAPSHOT_FILE_ID,          SNAPSHOT_ID,                FILE_ID,
                                CONTAINING_FILE_ID,        FILE_SIZE,                  CHECKSUM,
                                FILE_VERSION_ID,           UPDATE_SOURCE_ID,           UPDATE_TYPE,
                                CREATION_DATE,             LAST_UPDATE_DATE,           LAST_UPDATED_BY,
                                CREATED_BY,                APPL_TOP_ID,                INCONSISTENT_FLAG,
                                SERVER_TYPE_ADMIN_FLAG,    SERVER_TYPE_FORMS_FLAG,     SERVER_TYPE_NODE_FLAG,
                                SERVER_TYPE_WEB_FLAG,      DEST_FILE_ID,               FILE_TYPE_FLAG,
                                IREP_GATHERED_FLAG,        LAST_PATCHED_DATE)
        select AD_SNAPSHOT_FILES_S.NEXTVAL,    ouasnpat.SNAPSHOT_ID,            ouasf.FILE_ID,
               ouasf.CONTAINING_FILE_ID,       ouasf.FILE_SIZE,                 ouasf.CHECKSUM,
               ouasf.FILE_VERSION_ID,          ouasf.UPDATE_SOURCE_ID,          ouasf.UPDATE_TYPE,
               ouasf.CREATION_DATE,            ouasf.LAST_UPDATE_DATE,          ouasf.LAST_UPDATED_BY,
               ouasf.CREATED_BY,               ouasf.APPL_TOP_ID,               ouasf.INCONSISTENT_FLAG,
               ouasf.SERVER_TYPE_ADMIN_FLAG,   ouasf.SERVER_TYPE_FORMS_FLAG,    ouasf.SERVER_TYPE_NODE_FLAG,
               ouasf.SERVER_TYPE_WEB_FLAG,     ouasf.DEST_FILE_ID,              ouasf.FILE_TYPE_FLAG,
               ouasf.IREP_GATHERED_FLAG,       ouasf.LAST_PATCHED_DATE
        from   ad_snapshot_files ouasf, ad_snapshots ouasn, AD_APPL_TOPS ouaat, FND_PRODUCT_GROUPS oufpg, AD_RELEASES rel, ad_snapshots ouasnpat
        where  ouasf.SNAPSHOT_ID=OUASN.SNAPSHOT_ID
        and    ouasn.appl_top_id=ouasnpat.appl_top_id
        and    ouasn.release_id=ouasnpat.release_id
        and    ouasnpat.snapshot_type='P'
        and    ouasn.snapshot_name=ouasnpat.snapshot_name
        and    OUASN.SNAPSHOT_TYPE='C'
        and    ouasn.snapshot_name='CURRENT_VIEW'
        and    OUASN.APPL_TOP_ID=OUAAT.APPL_TOP_ID
        and    ouaat.appl_top_id=nvl(p_appl_top_id, ouaat.appl_top_id)
        and    ouaat.appl_top_type='R'
        and    ouaat.applications_system_name=oufpg.applications_system_name
        and    oufpg.product_group_id=1
        and    rel.aru_release_name=oufpg.aru_release_name
        and    ouasn.release_id=rel.release_id
        and    ouaat.active_flag='Y'
        and    oufpg.release_name=rel.major_version||'.'||
                                  rel.minor_version||'.'||
                                  rel.tape_version
        and not exists (
           select 'x'
           from   AD_SNAPSHOT_FILES ASF, AD_SNAPSHOTS ASN
           where  ASF.SNAPSHOT_ID=ASN.SNAPSHOT_ID
           and    ASN.SNAPSHOT_TYPE='P'
           and    ASN.SNAPSHOT_NAME=OUASN.SNAPSHOT_NAME
           and    ASN.APPL_TOP_ID=OUASN.APPL_TOP_ID
           and    OUASF.FILE_ID=ASF.FILE_ID
           and    NVL(OUASF.CONTAINING_FILE_ID, -2)=NVL(ASF.CONTAINING_FILE_ID, -2));

Line: 962


  insert into ad_snapshot_files(SNAPSHOT_FILE_ID,          SNAPSHOT_ID,                FILE_ID,
                                CONTAINING_FILE_ID,        FILE_SIZE,                  CHECKSUM,
                                FILE_VERSION_ID,           UPDATE_SOURCE_ID,           UPDATE_TYPE,
                                CREATION_DATE,             LAST_UPDATE_DATE,           LAST_UPDATED_BY,
                                CREATED_BY,                APPL_TOP_ID,                INCONSISTENT_FLAG,
                                SERVER_TYPE_ADMIN_FLAG,    SERVER_TYPE_FORMS_FLAG,     SERVER_TYPE_NODE_FLAG,
                                SERVER_TYPE_WEB_FLAG,      DEST_FILE_ID,               FILE_TYPE_FLAG,
                                IREP_GATHERED_FLAG,        LAST_PATCHED_DATE)
        select AD_SNAPSHOT_FILES_S.NEXTVAL,    ouasnpat.SNAPSHOT_ID,            ouasf.FILE_ID,
               ouasf.CONTAINING_FILE_ID,       ouasf.FILE_SIZE,                 ouasf.CHECKSUM,
               ouasf.FILE_VERSION_ID,          ouasf.UPDATE_SOURCE_ID,          ouasf.UPDATE_TYPE,
               ouasf.CREATION_DATE,            ouasf.LAST_UPDATE_DATE,          ouasf.LAST_UPDATED_BY,
               ouasf.CREATED_BY,               ouasf.APPL_TOP_ID,               ouasf.INCONSISTENT_FLAG,
               ouasf.SERVER_TYPE_ADMIN_FLAG,   ouasf.SERVER_TYPE_FORMS_FLAG,    ouasf.SERVER_TYPE_NODE_FLAG,
               ouasf.SERVER_TYPE_WEB_FLAG,     ouasf.DEST_FILE_ID,              ouasf.FILE_TYPE_FLAG,
               ouasf.IREP_GATHERED_FLAG,       ouasf.LAST_PATCHED_DATE
        from   ad_snapshot_files ouasf, ad_snapshots ouasn, AD_APPL_TOPS ouaat, FND_PRODUCT_GROUPS oufpg, AD_RELEASES rel, ad_snapshots ouasnpat
        where  ouasf.SNAPSHOT_ID=OUASN.SNAPSHOT_ID
        and    ouasn.appl_top_id=ouasnpat.appl_top_id
        and    ouasn.release_id=ouasnpat.release_id
        and    ouasnpat.snapshot_type='Q'
        and    ouasn.snapshot_name=ouasnpat.snapshot_name
        and    OUASN.SNAPSHOT_TYPE='G'
        and    ouasn.snapshot_name='GLOBAL_VIEW'
        and    OUASN.APPL_TOP_ID=OUAAT.APPL_TOP_ID
        and    ouaat.appl_top_type='G'
        and    ouaat.applications_system_name=oufpg.applications_system_name
        and    oufpg.product_group_id=1
        and    rel.aru_release_name=oufpg.aru_release_name
        and    ouasn.release_id=rel.release_id
        and    ouaat.active_flag='Y'
        and    oufpg.release_name=rel.major_version||'.'||
                                  rel.minor_version||'.'||
                                  rel.tape_version
        and not exists (
           select 'x'
           from   AD_SNAPSHOT_FILES ASF, AD_SNAPSHOTS ASN
           where  ASF.SNAPSHOT_ID=ASN.SNAPSHOT_ID
           and    ASN.SNAPSHOT_TYPE='Q'
           and    ASN.SNAPSHOT_NAME=OUASN.SNAPSHOT_NAME
           and    ASN.APPL_TOP_ID=OUASN.APPL_TOP_ID
           and    OUASF.FILE_ID=ASF.FILE_ID
           and    NVL(OUASF.CONTAINING_FILE_ID, -2)=NVL(ASF.CONTAINING_FILE_ID, -2));

Line: 1017


select PATCH_RUN_BUG_ID
from   ad_patch_run_bugs
where  PATCH_RUN_ID=x_patch_run_id;

Line: 1022


select distinct abbreviation
from   ad_te_level_history
where  patch_run_id=x_patch_run_id;

Line: 1031


     delete ad_patch_run_bug_actions
     where  PATCH_RUN_BUG_ID=run_bug_id.patch_run_bug_id;

Line: 1036


  delete ad_patch_run_bugs where patch_run_id=x_patch_run_id;

Line: 1041


     delete AD_TE_LEVEL_HISTORY
     where  patch_run_id=x_patch_run_id and abbreviation=te.abbreviation;

Line: 1045


        select max(baseline), max(codelevel)
        into   v_blvl, v_clvl
        from   AD_TE_LEVEL_HISTORY
        where  abbreviation=te.abbreviation;

Line: 1050


        update ad_trackable_entities
        set    baseline=v_blvl, codelevel=v_clvl
        where  abbreviation=te.abbreviation;

Line: 1056


         delete ad_trackable_entities
         where  abbreviation=te.abbreviation;

Line: 1063


  delete ad_patch_runs where patch_run_id=x_patch_run_id;

Line: 1068


    'delete ad_check_files acf                                    ' ||
    'where not exists                                             ' ||
    '      (select ''x''                                          ' ||
    '       from   ad_check_file_history acfh                     ' ||
    '       where  acfh.check_file_id=acf.check_file_id           ' ||
    '       and    acfh.patch_run_id not in (:1, :2)) ';

Line: 1093


       v_query := 'select max(ACFH2.PATCH_RUN_ID) PRID,           ' ||
                  '       ACFH2.CHECK_FILE_ID CFID                ' ||
                  'from   AD_CHECK_FILE_HISTORY ACFH1,            ' ||
                  '       AD_CHECK_FILE_HISTORY ACFH2             ' ||
                  'where  ACFH1.CHECK_FILE_ID=ACFH2.CHECK_FILE_ID ' ||
                  'and    ACFH1.PATCH_RUN_ID in (:1, :2)          ' ||
                  'and    ACFH2.PATCH_RUN_ID not in (:3, :4)      ' ||
                  'group by ACFH2.CHECK_FILE_ID';

Line: 1107


          v_qry := 'update ad_check_files acf                     ' ||
                   'set    acf.file_version_id=(                  ' ||
                   '           select acfh.file_Version_id        ' ||
                   '           from   ad_check_file_history acfh  ' ||
                   '           where  acfh.patch_run_id=:1' ||
                   '           and    acfh.check_file_id=:2) ' ||
                   'where acf.check_file_id=:3';

Line: 1117


       v_qry := 'delete ad_check_file_history acfh ' ||
                'where  acfh.patch_run_id in (:1, :2)';

Line: 1128


SELECT AASP.PATCHRUN_ID
FROM   AD_ADOP_SESSION_PATCHES AASP
WHERE  AASP.ADOP_SESSION_ID=x_session_id
AND  AASP.PATCHRUN_ID IS NOT NULL
UNION
SELECT ACFH.PATCH_RUN_ID
FROM AD_CHECK_FILE_HISTORY ACFH,
     AD_ADOP_SESSION_PATCHES aasp
WHERE ACFH.PATCH_RUN_ID>AASP.PATCHRUN_ID
AND   AASP.ADOP_SESSION_ID=x_session_id
AND   AASP.PATCHRUN_ID<>-1
AND   AASP.PATCHRUN_ID IS NOT NULL;

Line: 1157


select AAT.APPL_TOP_ID,
       ASN.SNAPSHOT_ID,
       ASN.SNAPSHOT_NAME,
       ASN.SNAPSHOT_TYPE,
       AR.RELEASE_ID
FROM   AD_APPL_TOPS AAT,
       AD_SNAPSHOTS ASN,
       AD_RELEASES AR,
       FND_PRODUCT_GROUPS FPG,
       FND_NODES FN
WHERE  AAT.APPL_TOP_TYPE='R'
and    aat.active_flag='Y'
AND    AAT.APPL_TOP_ID=ASN.APPL_TOP_ID
and    ASN.SNAPSHOT_TYPE  in ('C', 'P')
and    ASN.SNAPSHOT_NAME='CURRENT_VIEW'
and    AR.RELEASE_ID=ASN.RELEASE_ID
and    AR.MAJOR_VERSION||'.'||AR.MINOR_VERSION||'.'||AR.TAPE_VERSION=FPG.RELEASE_NAME
and    FPG.APPLICATIONS_SYSTEM_NAME = AAT.APPLICATIONS_SYSTEM_NAME
and    UPPER(FN.NODE_NAME)=UPPER(AAT.name)
and    ( FN.SUPPORT_CP='Y' or FN.SUPPORT_FORMS='Y' or FN.SUPPORT_WEB='Y' or FN.SUPPORT_ADMIN='Y')
and    fn.node_name is not null
union
select AAT.APPL_TOP_ID,
       ASN.SNAPSHOT_ID,
       ASN.SNAPSHOT_NAME,
       ASN.SNAPSHOT_TYPE,
       AR.RELEASE_ID
FROM   AD_APPL_TOPS AAT,
       AD_SNAPSHOTS ASN,
       AD_RELEASES AR,
       FND_PRODUCT_GROUPS FPG
WHERE  AAT.APPL_TOP_TYPE='G'
and    aat.active_flag='Y'
and    aat.name='GLOBAL'
AND    AAT.APPL_TOP_ID=ASN.APPL_TOP_ID
and    ASN.SNAPSHOT_TYPE in ('G', 'Q')
and    ASN.SNAPSHOT_NAME='GLOBAL_VIEW'
and    AR.RELEASE_ID=ASN.RELEASE_ID
and    AR.MAJOR_VERSION||'.'||AR.MINOR_VERSION||'.'||AR.TAPE_VERSION=FPG.RELEASE_NAME
and    FPG.APPLICATIONS_SYSTEM_NAME = AAT.APPLICATIONS_SYSTEM_NAME;

Line: 1202


    update ad_snapshots
    set    release_id=snp.release_id
    where  appl_top_id=snp.appl_top_id
    and    snapshot_type=decode(snp.snapshot_type, 'P', 'C', 'Q', 'G')
    and    snapshot_name=snp.snapshot_name
    and    release_id<>snp.release_id;

Line: 1211


  update ad_snapshots
  set    snapshot_type=DECODE(snapshot_type, 'C', 'P', 'P', 'C',
                                            'G', 'Q', 'Q', 'G')
  where  snapshot_id in (
     select ASN.SNAPSHOT_ID
     FROM   AD_APPL_TOPS AAT,
            AD_SNAPSHOTS ASN,
            AD_RELEASES AR,
            FND_PRODUCT_GROUPS FPG,
            FND_NODES FN
     WHERE  AAT.APPL_TOP_TYPE='R'
     and    aat.active_flag='Y'
     AND    AAT.APPL_TOP_ID=ASN.APPL_TOP_ID
     and    ASN.SNAPSHOT_TYPE  in ('C', 'P')
     and    ASN.SNAPSHOT_NAME='CURRENT_VIEW'
     and    AR.RELEASE_ID=ASN.RELEASE_ID
     and    AR.MAJOR_VERSION||'.'||AR.MINOR_VERSION||'.'||AR.TAPE_VERSION=FPG.RELEASE_NAME
     and    FPG.APPLICATIONS_SYSTEM_NAME = AAT.APPLICATIONS_SYSTEM_NAME
     and    UPPER(FN.NODE_NAME)=UPPER(AAT.name)
     and    ( FN.SUPPORT_CP='Y' or FN.SUPPORT_FORMS='Y' or FN.SUPPORT_WEB='Y' or FN.SUPPORT_ADMIN='Y' )
     and    fn.node_name is not null
     union
     select ASN.SNAPSHOT_ID
     FROM   AD_APPL_TOPS AAT,
            AD_SNAPSHOTS ASN,
            AD_RELEASES AR,
            FND_PRODUCT_GROUPS FPG
     WHERE  AAT.APPL_TOP_TYPE='G'
     and    aat.active_flag='Y'
     AND    AAT.APPL_TOP_ID=ASN.APPL_TOP_ID
     and    ASN.SNAPSHOT_TYPE in ('G', 'Q')
     and    ASN.SNAPSHOT_NAME='GLOBAL_VIEW'
     and    AR.RELEASE_ID=ASN.RELEASE_ID
     and    AR.MAJOR_VERSION||'.'||AR.MINOR_VERSION||'.'||AR.TAPE_VERSION=FPG.RELEASE_NAME
     and    FPG.APPLICATIONS_SYSTEM_NAME = AAT.APPLICATIONS_SYSTEM_NAME);

Line: 1267


     select node_name,appltop_id into l_node_name,l_appltop_id
     from ad_adop_sessions
     where node_type='master' and adop_session_id=p_session_id;

Line: 1284


       select status into l_status from ad_adop_sessions
       where node_type='master' and adop_session_id=p_session_id;

Line: 1294


PROCEDURE INSERT_CLONE_TRACKING_ROW(p_run_base in varchar2,
                                    p_patch_base in varchar2)
is
l_mod_name         varchar2(30) := 'INSERT_CLONE_TRACKING_ROW';

Line: 1302


select distinct fn.host host
from   fnd_product_groups fpg, ad_appl_tops aat,
       ad_releases ar, fnd_nodes fn
where  aat.appl_top_type='R'
and    aat.applications_system_name=fpg.applications_system_name
and    aat.active_flag='Y'
and    fpg.release_name=ar.major_version||'.'||ar.minor_version||'.'||ar.tape_version
and    fpg.aru_release_name=ar.aru_release_name
and    fn.host is not null
and    ( fn.support_cp='Y' or fn.support_forms='Y' or FN.SUPPORT_WEB='Y' or FN.SUPPORT_ADMIN='Y');

Line: 1313


  SELECT NVL(MAX(ADOP_SESSION_ID),0)
  INTO   l_adop_session_id
  FROM   AD_ADOP_SESSIONS
  WHERE  APPLY_STATUS IN ('P','N')
  AND    PREPARE_STATUS IN ('Y','X')
  AND    ABORT_STATUS<> 'Y'
  AND    CLEANUP_STATUS <> 'Y';

Line: 1328


        SELECT  aat.appl_top_id
        into    l_appl_top_id
        FROM    FND_OAM_CONTEXT_FILES focf,
                fnd_product_groups fpg,
                ad_appl_tops aat,
                ad_releases ar
        WHERE   focf.NAME not in ('TEMPLATE','METADATA','config.txt')
        and     focf.CTX_TYPE='A'
        and     (focf.status is null or upper(focf.status) in ('S','F'))
        and     EXTRACTVALUE(XMLType(focf.TEXT),'//file_edition_type') = 'run'
        and     focf.node_name=node.host
        and     aat.appl_top_type='R'
        and     aat.applications_system_name=fpg.applications_system_name
        and     aat.active_flag='Y'
        and     fpg.release_name=ar.major_version||'.'||
                                 ar.minor_version||'.'||
                                 ar.tape_version
        and     fpg.aru_release_name=ar.aru_release_name
        and     aat.name=EXTRACTVALUE(XMLType(focf.TEXT),'//APPL_TOP_NAME');

Line: 1348


      select count(1) into l_clone_exists
      from ad_adop_session_patches
      where APPLTOP_ID=l_appl_top_id and node_name=node.host
      and bug_number='CLONE' and status='N'
      and  clone_status='NOT-STARTED';

Line: 1356


      insert into ad_adop_session_patches(ADOP_SESSION_ID, BUG_NUMBER, STATUS,
         APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYSTEM_BASE, APPLTOP_ID,
         NODE_NAME, AUTOCONFIG_STATUS, START_DATE, CLONE_STATUS)
      values (l_adop_session_id, 'CLONE', 'N', p_run_base, p_patch_base,
            l_appl_top_id, node.host, 'N', sysdate, 'NOT-STARTED');

Line: 1363


end INSERT_CLONE_TRACKING_ROW;

Line: 1399


     select
      (XMLQuery(
      ' for $i in /oa_context/oa_services/oa_service_list/oa_service/oa_service_status
       where $i/text() eq "enabled"
       return fn:concat($i/@oa_var,",") '
       PASSING XMLType(TEXT) RETURNING CONTENT
      )).getStringVal() services, node_name node
     from FND_OAM_CONTEXT_FILES
     where NAME not in ('TEMPLATE','METADATA','config.txt') and
           CTX_TYPE='A' and (status is null or upper(status) in ('S','F'))
           and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type') = 'patch';

Line: 1413


    select
     (XMLQuery(
     ' for $i in /oa_context/oa_services/oa_service_group_list/oa_service_group/oa_service_group_status
      where $i/text() eq "enabled"
      return fn:concat($i/@oa_var,",") '
      PASSING XMLType(TEXT) RETURNING CONTENT
     )).getStringVal() group_services, node_name node
    from FND_OAM_CONTEXT_FILES
    where NAME not in ('TEMPLATE','METADATA','config.txt') and
          CTX_TYPE='A' and (status is null or upper(status) in ('S','F'))
          and EXTRACTVALUE(XMLType(TEXT),'//file_edition_type')= 'patch';

Line: 1489


    select abort_status into abort_status from ad_adop_sessions
    where prepare_status <> 'X' and node_type='master' and
    adop_session_id = (select max(adop_session_id) from ad_adop_sessions where
    prepare_status <> 'X' and node_type='master');

Line: 1517


    select max(adop_session_id) into session_id from ad_adop_sessions
    where prepare_status <> 'X' and node_type='master' and cleanup_status <> 'Y' ;

Line: 1524


  for rec in (select node_name from ad_adop_sessions where adop_session_id = session_id) loop
    node_map(rec.node_name) := 1;

Line: 1529


     select count(1) into cnt from ad_adop_sessions where adop_session_id = session_id
     and prepare_status <> 'Y';

Line: 1533


       for rec in (select node_name from ad_adop_sessions where adop_session_id
       = session_id and prepare_status <> 'Y') loop
         node_map(rec.node_name) := 0;

Line: 1543


         select count(1) into cnt from ad_adop_sessions where adop_session_id =
         session_id and node_name = node and ((apply_status = 'N') or
         (apply_status='P' and status in ('F','R')));

Line: 1551


         select count(1) into cnt from ad_adop_sessions where adop_session_id = session_id
         and ((apply_status = 'N' and status in ('C','N')) or (apply_status ='P' and
         status = 'F'));

Line: 1555


         select count(1) into cnt1 from ad_adop_sessions where adop_session_id =
         session_id and apply_status in ('P','Y') and status='C';

Line: 1559


            for rec in (select node_name from ad_adop_sessions where adop_session_id
            = session_id and ((apply_status = 'N' and status in ('C','N')) or
            (apply_status ='P' and status = 'F'))) loop
             node_map(rec.node_name) := 0;

Line: 1569


     select count(1) into cnt from ad_adop_sessions where adop_session_id =
     session_id and cutover_status not in ('X','Y');

Line: 1573


       for rec in (select node_name from ad_adop_sessions where adop_session_id
       = session_id and cutover_status not in ('X','Y')) loop
         node_map(rec.node_name) := 0;

Line: 1581


     select count(1) into cnt from ad_adop_sessions where adop_session_id =
     session_id and abort_status not in ('X','Y');

Line: 1585


       for rec in (select node_name from ad_adop_sessions where adop_session_id
       = session_id and abort_status not in ('X','Y')) loop
         node_map(rec.node_name) := 0;

Line: 1600


  for rec in (select node_name from ad_adop_sessions where adop_session_id = session_id) loop
    if ((node_map(rec.node_name)) = 1) then
      node_list := node_list || ',' || rec.node_name;

Line: 1628


  select max(adop_session_id) into l_session_id
  from ad_adop_sessions
  where node_type='master' and prepare_status <> 'X';

Line: 1632


  select abandon_flag into l_abandon_flag
  from ad_adop_sessions
  where adop_session_id = l_session_id and node_name = node;

Line: 1639


     select count(1) into l_cnt from ad_adop_sessions slave, ad_adop_sessions master
     where
         (slave.prepare_status <> master.prepare_status or
         slave.apply_status   <> master.apply_status or
         slave.cutover_status <> master.cutover_status or
         slave.abort_status   <> master.abort_status) and
         master.adop_session_id = l_session_id and
         slave.adop_session_id  = l_session_id and
         master.node_type = 'master' and
         slave.node_name = node;

Line: 1663


  select max(adop_session_id) into l_session_id
  from ad_adop_sessions
  where node_type='master' and prepare_status <> 'X';

Line: 1668


    for rec in (select node_name from ad_adop_sessions where
                adop_session_id=l_session_id and
                abandon_flag <> l_session_id)
    loop
       if l_node_list is NULL then
          l_node_list := rec.node_name;

Line: 1680


        select slave.node_name node_name from ad_adop_sessions slave,ad_adop_sessions master
        where
         (slave.prepare_status <> master.prepare_status or
         slave.apply_status   <> master.apply_status or
         slave.cutover_status <> master.cutover_status or
         slave.abort_status   <> master.abort_status) and
         master.adop_session_id = l_session_id and
         slave.adop_session_id  = l_session_id and
         slave.abandon_flag = l_session_id and
         master.node_type = 'master' and
         slave.node_type = 'slave')
    loop
       if l_node_list is NULL then
          l_node_list := rec.node_name;

Line: 1705


  update ad_adop_sessions set abandon_flag = NULL
  where node_name=dest_node  and abandon_flag is not null and
        adop_session_id = (select max(adop_session_id) from ad_adop_sessions where
                           ((prepare_status='Y' and apply_status='Y' and cutover_status='Y') or
                            (abort_status='Y')) and node_type='master');

Line: 1718


   select max(adop_session_id)
   into id
   from ad_adop_sessions
   where prepare_status <> 'X'
   and node_type='master'
   and cutover_status = 'N'
   and abort_status='N';