APPS.JTF_PF_CONV_PKG SQL Statements

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

Line: 57

      SELECT JTF_PF_SEQ.NEXTVAL INTO seq_num FROM dual;

Line: 64

    SELECT sysdate INTO dbdate FROM dual;

Line: 68

        INSERT INTO jtf_pf_repository(
          pageobject,
          object_version_number,
          created_by,
          creation_date,
          last_updated_by,
          last_update_date,
          tech_stack,
          track_purpose)
        VALUES (
          objTabLocal(indx),
          0,
          690,
          sysdate,
          690,
          sysdate,
          tech_stack_tbl(indx),
          track_prps_tbl(indx));

Line: 125

    SELECT JTF_PF_SEQ.NEXTVAL INTO seq_num FROM dual;

Line: 126

    SELECT sysdate INTO dbdate FROM dual;

Line: 133

      INSERT INTO jtf_pf_repository(
        pageobject,
        object_version_number,
        created_by,
        creation_date,
        last_updated_by,
        last_update_date,
        tech_stack,
        track_purpose)
      VALUES (
        dummyPageObj,
        0,
        690,
        obj.info.Day,
        690,
        obj.info.Timestamp,
        tech_stack,
        track_purpose);

Line: 320

    SELECT max(a.pageobject.info.recid)
    INTO last_msg_in_jtf_rep
    FROM jtf_pf_repository a;

Line: 353

        INSERT INTO jtf_pf_repository( --ang no need to update this insert
          pageobject,                  --as the proc is not in use
          object_version_number,
          created_by,
          creation_date,
          last_updated_by,
          last_update_date)
        VALUES(
          pgObjects,
          0,
          690,
          pgObjects.info.Day,
          690,
          pgObjects.info.Timestamp);

Line: 394

    SELECT trunc(sysdate)
    INTO today
    FROM dual;

Line: 414

      SELECT min(x.day)
      INTO last_migrate_day
      FROM (SELECT max(day) AS day
            FROM JTF_PF_APP_SUMM
              UNION ALL
            SELECT max(day) AS day
            FROM JTF_PF_HOST_SUMM
              UNION ALL
            SELECT max(day) AS day
            FROM JTF_PF_PAGE_SUMM
              UNION ALL
            SELECT max(day) AS day
            FROM JTF_PF_SESSION_SUMM
              UNION ALL
            SELECT max(day) AS day
            FROM JTF_PF_USER_SUMM) x;

Line: 435

      DELETE FROM JTF_PF_APP_SUMM
      WHERE day >= last_migrate_day;

Line: 438

      DELETE FROM JTF_PF_HOST_SUMM
      WHERE day >= last_migrate_day;

Line: 441

      DELETE FROM JTF_PF_PAGE_SUMM
      WHERE day >= last_migrate_day;

Line: 444

      DELETE FROM JTF_PF_SESSION_SUMM
      WHERE (sessionid IS NULL and day >= last_migrate_day)
      OR (sessionid IS NOT NULL
          and sessionid IN
           (select distinct sessionid
            from JTF_PF_SES_ACTIVITY
            where day >= last_migrate_day
            OR last_migrate_day IS NULL));

Line: 453

      DELETE FROM JTF_PF_USER_SUMM
      WHERE day >= last_migrate_day;

Line: 460

      INSERT INTO JTF_PF_APP_SUMM(day, cnt_pages_jtf, cnt_pages_oaf,
        cnt_pages_form, cnt_pages_all, cnt_flows, cnt_users, cnt_apps,
        cnt_resps, cnt_sessions, cnt_langs)
      (SELECT day, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
        cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, cnt_flows, cnt_users,
        cnt_apps, cnt_resps, cnt_sessions, cnt_langs
      FROM JTF_PF_APP_SUMMARY_VL);

Line: 468

      INSERT INTO JTF_PF_HOST_SUMM (day, servername, serverport, jservs,
        sum_exect_jtf, sum_exect_oaf, pagehits_jtf, pagehits_oaf, pagehits_all,
        fails, pages, badpages)
      (SELECT day, NVL(servername, 'N/A'), NVL(serverport, -1), jservs,
        sum_exect_jtf, sum_exect_oaf, pagehits_jtf, pagehits_oaf,
        pagehits_jtf+pagehits_oaf, fails, pages, badpages
      FROM JTF_PF_HOST_SUMMARY_VL);

Line: 476

      INSERT INTO JTF_PF_PAGE_SUMM (day, pagename, tech_stack, cnt_pages,
        cnt_ses, ucnt_ses, ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs,
        sum_exect, sum_thinkt, cnt_thinkt, startt, endt, cnt_fail, cnt_forward)
      (SELECT day, pagename, tech_stack, cnt_pages, cnt_ses, ucnt_ses,
        ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs, sum_exect, sum_thinkt,
        cnt_thinkt, startt, endt, cnt_fail, cnt_forward
      FROM JTF_PF_PAGE_SUMMARY_VL);

Line: 484

      INSERT INTO JTF_PF_SESSION_SUMM (day, seqid, sessionid, user_name, userid,
        sum_exect_jtf, sum_exect_oaf, sum_exect_form, sum_thinkt_jtf,
        sum_thinkt_oaf, sum_thinkt_form, cnt_pages_jtf, cnt_pages_oaf,
        cnt_pages_form, cnt_pages_all, ucnt_pages, ucnt_flows, ucnt_users,
        ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail)
      (SELECT day, seqid, sessionid, user_name, userid, sum_exect_jtf,
        sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf,
        sum_thinkt_form, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
        cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, ucnt_pages, ucnt_flows,
        ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail
       FROM JTF_PF_SESSION_SUMMARY_VL
       WHERE (sessionid IS NULL )
       or (sessionid IS NOT NULL
           and sessionid IN
             (select distinct sessionid
             from JTF_PF_SES_ACTIVITY)));

Line: 501

      INSERT INTO JTF_PF_USER_SUMM (day, user_name, userid, sum_exect_jtf,
        sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf,
        sum_thinkt_form, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
        cnt_pages_all, ucnt_ses, ucnt_pages, ucnt_apps, ucnt_resps, ucnt_langs,
        startt, endt, cnt_fail)
      (SELECT day, user_name, userid, sum_exect_jtf, sum_exect_oaf,
        sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf, sum_thinkt_form,
        cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
        cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, ucnt_ses, ucnt_pages,
        ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail
      FROM JTF_PF_USER_SUMMARY_VL);

Line: 515

      INSERT INTO JTF_PF_APP_SUMM (day, cnt_pages_jtf, cnt_pages_oaf,
        cnt_pages_form, cnt_pages_all, cnt_flows, cnt_users, cnt_apps,
        cnt_resps, cnt_sessions, cnt_langs)
      (SELECT day, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
        cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, cnt_flows, cnt_users,
        cnt_apps, cnt_resps, cnt_sessions, cnt_langs
      FROM JTF_PF_APP_SUMMARY_VL
      WHERE day >= last_migrate_day);

Line: 524

      INSERT INTO JTF_PF_HOST_SUMM (day, servername, serverport, jservs,
        sum_exect_jtf, sum_exect_oaf, pagehits_jtf, pagehits_oaf, pagehits_all,
        fails, pages, badpages)
      (SELECT day, NVL(servername, 'N/A'), NVL(serverport, -1), jservs,
        sum_exect_jtf, sum_exect_oaf, pagehits_jtf, pagehits_oaf,
        pagehits_jtf+pagehits_oaf, fails, pages, badpages
       FROM JTF_PF_HOST_SUMMARY_VL
       WHERE day >= last_migrate_day);

Line: 533

      INSERT INTO JTF_PF_PAGE_SUMM (day, pagename, tech_stack, cnt_pages,
        cnt_ses, ucnt_ses, ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs,
        sum_exect, sum_thinkt, cnt_thinkt, startt, endt, cnt_fail, cnt_forward)
      (SELECT day, pagename, tech_stack, cnt_pages, cnt_ses, ucnt_ses,
        ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs, sum_exect, sum_thinkt,
        cnt_thinkt, startt, endt, cnt_fail, cnt_forward
       FROM JTF_PF_PAGE_SUMMARY_VL
       WHERE day >= last_migrate_day);

Line: 542

      INSERT INTO JTF_PF_SESSION_SUMM (day, seqid, sessionid, user_name, userid,
        sum_exect_jtf, sum_exect_oaf, sum_exect_form, sum_thinkt_jtf,
        sum_thinkt_oaf, sum_thinkt_form, cnt_pages_jtf, cnt_pages_oaf,
        cnt_pages_form, cnt_pages_all, ucnt_pages, ucnt_flows, ucnt_users,
        ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail)
      (SELECT  day, seqid, sessionid, user_name, userid, sum_exect_jtf,
        sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf,
        sum_thinkt_form, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
        cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, ucnt_pages, ucnt_flows,
        ucnt_users, ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail
       FROM JTF_PF_SESSION_SUMMARY_VL
       WHERE (sessionid IS NULL and day >= last_migrate_day)
       or (sessionid IS NOT NULL
           and sessionid IN
             (select distinct sessionid
              from JTF_PF_SES_ACTIVITY
              where day >= last_migrate_day )));

Line: 560

      INSERT INTO JTF_PF_USER_SUMM (day, user_name, userid, sum_exect_jtf,
        sum_exect_oaf, sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf,
        sum_thinkt_form, cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
        cnt_pages_all, ucnt_ses, ucnt_pages, ucnt_apps, ucnt_resps, ucnt_langs,
        startt, endt, cnt_fail)
      (SELECT day, user_name, userid, sum_exect_jtf, sum_exect_oaf,
        sum_exect_form, sum_thinkt_jtf, sum_thinkt_oaf, sum_thinkt_form,
        cnt_pages_jtf, cnt_pages_oaf, cnt_pages_form,
        cnt_pages_jtf+cnt_pages_oaf+cnt_pages_form, ucnt_ses, ucnt_pages,
        ucnt_apps, ucnt_resps, ucnt_langs, startt, endt, cnt_fail
       FROM JTF_PF_USER_SUMMARY_VL
       WHERE day >= last_migrate_day);

Line: 587

      SELECT x.pageobject, x.tech_stack
      FROM JTF_PF_REPOSITORY x
      WHERE x.pageobject.info.RecId > starting_po and
      x.track_purpose not in (2, 4)
      ORDER BY x.pageobject.ses.SessionId, x.pageobject.jsp.StartTime;

Line: 594

      SELECT nvl(max_po,-100000000000), nvl(ses_seqid,0), nvl(anon_seqid,0)
      FROM JTF_PF_SEQ_VL;

Line: 623

            INSERT INTO JTF_PF_ANON_ACTIVITY(
              SEQID,
              DAY,
              TECH_STACK,
              TIMESTAMP,
              SERVERNAME,
              SERVERPORT,
              JSERVPORT,
              FLOWID,
              SESSIONID,
              USERID,
              PROXYID,
              APPID,
              RESPID,
              LANGID,
              STARTT,
              PAGENAME,
              STATUSCODE,
              EXECT,
              THINKT,
              PO,
              OBJECT_VERSION_NUMBER,
              CREATED_BY,
              CREATION_DATE,
              LAST_UPDATED_BY,
              LAST_UPDATE_DATE)
            VALUES(
              seqcnt1,
              currpo.info.day,
              currtechstack,
              currpo.info.timestamp,
              currpo.loc.servername,
              currpo.loc.serverport,
              currpo.loc.jservport,
              null,
              null,
              null,
              null,
              null,
              null,
              null,
              NVL(currpo.jsp.starttime, -1), -- ANG - Remove NVL condition,
                                             -- still what to do when starttime
                                             -- is null
              NVL(currpo.jsp.NAME, 'N/A'), -- ANG - Remove NVL condition, again
                                           -- there are records in repository
                                           -- table where the value for name
                                           -- coln is null. How should this be
                                           -- handled?
              NVL(currpo.jsp.statusCode, -1), -- ANG - need to get rid of NVL
                                              -- condition
              NVL(currpo.jsp.executionTime, -1), -- ANG - need to get rid of NVL
                                                 -- condition
              thinkT,
              currpo.info.Recid,
              0,
              690,
              currpo.info.day,
              690,
              currpo.info.timestamp);

Line: 713

              INSERT INTO JTF_PF_SES_ACTIVITY(
                SEQID,
                DAY,
                TECH_STACK,
                TIMESTAMP,
                SERVERNAME,
                SERVERPORT,
                JSERVPORT,
                FLOWID,
                SESSIONID,
                USERID,
                PROXYID,
                APPID,
                RESPID,
                LANGID,
                STARTT,
                PAGENAME,
                STATUSCODE,
                EXECT,
                THINKT,
                PO,
                OBJECT_VERSION_NUMBER,
                CREATED_BY,
                CREATION_DATE,
                LAST_UPDATED_BY,
                LAST_UPDATE_DATE)
              VALUES(
                seqcnt2,
                currpo.info.day,
                currtechstack,
                currpo.info.timestamp,
                currpo.loc.servername,
                currpo.loc.serverport,
                currpo.loc.jservport,
                null,
                currpo.ses.sessionid,
                currpo.ses.userid,
                currpo.ses.proxyid,
                currpo.ses.appid,
                currpo.ses.respid,
                currpo.ses.langid,
                currpo.jsp.starttime,
                currpo.jsp.NAME,
                currpo.jsp.statusCode,
                currpo.jsp.executionTime,
                thinkT,
                currpo.info.Recid,
                0,
                690,
                currpo.info.day,
                690,
                currpo.info.timestamp);

Line: 805

      SELECT x.pageobject.params
      FROM JTF_PF_REPOSITORY x
      WHERE x.pageobject.info.RecId = poid;

Line: 876

      SELECT X.PAGEOBJECT.INFO.RECID
      FROM JTF_PF_REPOSITORY X
      WHERE X.TRACK_PURPOSE  IN (0,1)
      AND X.LAST_UPDATE_DATE <= LEAST(START_DATE,
        (SELECT LAST_RECORD_MIGRATED_TIME
        FROM JTF_PF_PURGEABLE
        WHERE TRACK_PURPOSE IN (0,1)
        ));

Line: 887

      SELECT X.PAGEOBJECT.INFO.RECID
      FROM JTF_PF_REPOSITORY X
      WHERE X.TRACK_PURPOSE   = 4
      AND X.LAST_UPDATE_DATE <= LEAST(START_DATE,
        (SELECT LAST_RECORD_MIGRATED_TIME
        FROM JTF_PF_PURGEABLE
        WHERE TRACK_PURPOSE = 4
        ));

Line: 898

      SELECT X.PAGEOBJECT.INFO.RECID
      FROM JTF_PF_REPOSITORY X
      WHERE X.TRACK_PURPOSE   = 2
      AND X.LAST_UPDATE_DATE <= LEAST( START_DATE,
        (SELECT LAST_RECORD_MIGRATED_TIME
        FROM JTF_PF_PURGEABLE
        WHERE TRACK_PURPOSE = 2
        ));

Line: 909

      SELECT X.PAGEOBJECT.INFO.RECID
      FROM JTF_PF_REPOSITORY X
      WHERE X.TRACK_PURPOSE   = 3
      AND X.LAST_UPDATE_DATE <= LEAST (START_DATE ,
        (SELECT MIN(LAST_RECORD_MIGRATED_TIME)
        FROM JTF_PF_PURGEABLE
        WHERE TRACK_PURPOSE IN(0,1,2)
        ));

Line: 924

      SELECT FOU.ORACLE_USERNAME INTO JTF_OWNER
      FROM   FND_ORACLE_USERID FOU,
             FND_PRODUCT_INSTALLATIONS FPI,
             FND_APPLICATION A
      WHERE  FOU.ORACLE_ID = FPI.ORACLE_ID
      AND    FPI.APPLICATION_ID = A.APPLICATION_ID
      AND    A.APPLICATION_SHORT_NAME = 'JTF';

Line: 943

            DELETE
            FROM JTF_PF_REPOSITORY X
            WHERE X.PAGEOBJECT.INFO.RECID = RECTABTYPE1(I);

Line: 950

      RECTABTYPE1.DELETE;

Line: 958

            DELETE
            FROM JTF_PF_REPOSITORY X
            WHERE X.PAGEOBJECT.INFO.RECID = RECTABTYPE1(I);

Line: 965

      RECTABTYPE1.DELETE;

Line: 973

            DELETE
            FROM JTF_PF_REPOSITORY X
            WHERE X.PAGEOBJECT.INFO.RECID = RECTABTYPE1(I);

Line: 980

      RECTABTYPE1.DELETE;

Line: 988

            DELETE
            FROM JTF_PF_REPOSITORY X
            WHERE X.PAGEOBJECT.INFO.RECID = RECTABTYPE1(I);

Line: 995

      RECTABTYPE1.DELETE;

Line: 1019

    DELETE FROM JTF_PF_SES_ACTIVITY WHERE DAY <= START_DATE;

Line: 1020

    DELETE FROM JTF_PF_ANON_ACTIVITY WHERE DAY <= START_DATE;

Line: 1021

    DELETE FROM JTF_PF_APP_SUMM WHERE DAY <= START_DATE;

Line: 1022

    DELETE FROM JTF_PF_HOST_SUMM WHERE DAY <= START_DATE;

Line: 1023

    DELETE FROM JTF_PF_PAGE_SUMM WHERE DAY <= START_DATE;

Line: 1024

    DELETE FROM JTF_PF_SESSION_SUMM WHERE DAY <= START_DATE;

Line: 1025

    DELETE FROM JTF_PF_USER_SUMM WHERE DAY <= START_DATE;

Line: 1059

      SELECT MAX(x.pageobject.ses.sessionid) + 3
      FROM JTF_PF_REPOSITORY_TMP x;

Line: 1064

    DELETE FROM JTF_PF_REPOSITORY_TMP;

Line: 1065

    DELETE FROM JTF_PF_REPOSITORY x WHERE x.pageobject.info.day > sysdate;

Line: 1068

    INSERT INTO JTF_PF_REPOSITORY_TMP(
      pageobject,
      object_version_number,
      created_by,
      creation_date,
      last_updated_by,
      last_update_date,
      last_update_login,
      security_group_id,
      tech_stack)
    (SELECT
      pageobject,
      object_version_number,
      created_by,
      creation_date,
      last_updated_by,
      last_update_date,
      last_update_login,
      security_group_id,
      tech_stack
     FROM JTF_PF_REPOSITORY x
     WHERE x.pageobject.info.day > sysdate - 1
     AND x.pageobject.info.day < sysdate);

Line: 1102

      UPDATE JTF_PF_REPOSITORY_TMP x
      SET x.pageobject.info.day     = x.pageobject.info.day       + 1,
        x.pageobject.info.timestamp = x.pageobject.info.timestamp + 1,
        x.pageobject.jsp.starttime  =
          x.pageobject.jsp.starttime  + 1000 * 60 * 60 * 24;

Line: 1109

      UPDATE JTF_PF_REPOSITORY_TMP x
      SET x.pageobject.ses.sessionid = DECODE(x.pageobject.ses.sessionid, NULL,
        x.pageobject.ses.sessionid, -1,x.pageobject.ses.sessionid,
        x.pageobject.ses.sessionid - max_sessionid)
      WHERE x.pageobject.ses IS NOT NULL;

Line: 1116

      INSERT INTO JTF_PF_REPOSITORY(
        pageobject,
        object_version_number,
        created_by,
        creation_date,
        last_updated_by,
        last_update_date,
        last_update_login,
        security_group_id,
        tech_stack)
      (SELECT
        pageobject,
        object_version_number,
        created_by,
        creation_date,
        last_updated_by,
        last_update_date,
        last_update_login,
        security_group_id,
        tech_stack
       FROM JTF_PF_REPOSITORY_TMP);

Line: 1177

    SELECT COUNT(TRACK_PURPOSE)
    INTO TP
    FROM JTF_PF_PURGEABLE
    WHERE TRACK_PURPOSE = TRK_PRPS;

Line: 1182

      UPDATE JTF_PF_PURGEABLE
      SET LAST_RECORD_MIGRATED_TIME = LST_RCD_MGRTD_TM
      WHERE TRACK_PURPOSE = TRK_PRPS;

Line: 1186

      INSERT INTO JTF_PF_PURGEABLE(
        TRACK_PURPOSE,
        LAST_RECORD_MIGRATED_TIME)
      VALUES(
        TRK_PRPS,
        LST_RCD_MGRTD_TM);