APPS.BIS_PMV_DRILL_PVT SQL Statements

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

Line: 38

    select session_value
    from   bis_user_attributes
    where  user_id = cpUserId
    and    session_id = cpSessionId
    and    function_name = pFunctionName
    and    attribute_name = 'VIEW_BY';

Line: 48

    select attribute_name
    from   bis_user_attributes
    where  user_id = cpUserId
    and    session_id = cpSessionId
    and    ( attribute_name like 'TIME%' or attribute_name like 'EDW_TIME_M%' );

Line: 92

          DELETE FROM bis_user_attributes where session_id= pSessionId AND function_name= pFunctionName
          AND schedule_id is null
          AND user_id=pUserId
          AND attribute_name = 'VIEW_BY';

Line: 97

          INSERT INTO BIS_USER_ATTRIBUTES (user_id, function_name,
                                      session_id, attribute_name,
                                      session_value,
                                      dimension,
                                      creation_date, created_by,
                                      last_update_Date, last_updated_by)
                              VALUES (pUserId, pFunctionName,
                                      pSessionId, 'VIEW_BY' ,
                                      lTimeDimLevels,
                                      lViewByDimension,
                                      sysdate, -1, sysdate, -1);

Line: 194

  DELETE FROM bis_user_attributes
  WHERE function_name=pFunctionName
  AND user_id=pUserId
  AND session_id=pSessionId
  AND schedule_id IS NULL;

Line: 313

    select nvl(attribute2,attribute_code) attribute_code, substr(attribute2,1,instr(attribute2,'+')-1) dimension
    from   ak_region_items_vl
    where  region_code = rtrim(cpRegionCode)
    and    node_query_flag = 'Y'
    order by display_sequence;

Line: 320

    select nvl(attribute2,attribute_code) attribute_code, substr(attribute2,1,instr(attribute2,'+')-1) dimension
    from   ak_region_items_vl ak
    where  ak.region_code = rtrim(cpRegionCode)
    and    ak.node_query_flag = 'Y'
    AND nvl(substr(ak.attribute2,1,instr(ak.attribute2,'+')-1), ak.attribute_code) NOT IN (
        select DISTINCT nvl(substr(b.attribute_name,1,instr(b.attribute_name,'+')-1), attribute_code)
        from bis_user_attributes b
        where b.page_id=pPageId
        and b.user_id =pUserId
        )
    order by display_sequence;

Line: 334

        SELECT nvl(attribute2,attribute_code) attribute_code, substr(attribute2,1,instr(attribute2,'+')-1) dimension, display_sequence
        FROM ak_region_items
        WHERE region_code=cpNestedRegionCode
        AND node_query_flag='Y'
      UNION
        SELECT nvl(attribute2,attribute_code) attribute_code, substr(attribute2,1,instr(attribute2,'+')-1) dimension, display_sequence
        FROM   ak_region_items
        WHERE  region_code = rtrim(cpRegionCode)
        AND    node_query_flag = 'Y'
        AND nvl(substr(attribute2,1,instr(attribute2,'+')-1), attribute_code) NOT IN (
        	SELECT DISTINCT nvl(substr(attribute2,1,instr(attribute2,'+')-1), attribute_code)
          FROM ak_region_items
          WHERE region_code =cpNestedRegionCode
        )
        ORDER BY display_sequence;

Line: 351

        SELECT nvl(attribute2,attribute_code) attribute_code, substr(attribute2,1,instr(attribute2,'+')-1) dimension, display_sequence
        from ak_region_items
        where region_code=cpNestedRegionCode
        and node_query_flag='Y'
        and nvl(substr(attribute2,1,instr(attribute2,'+')-1), attribute_code)  not in (
          SELECT DISTINCT nvl(substr(attribute_name ,1,instr(attribute_name ,'+')-1), attribute_code)
          FROM bis_user_attributes
          WHERE page_id=pPageId
          AND user_id=pUserId
        )
      UNION
      --2. default region w/o page, w/o nested
        SELECT nvl(attribute2,attribute_code) attribute_code, substr(attribute2,1,instr(attribute2,'+')-1) dimension, display_sequence
        FROM ak_region_items
        WHERE region_code= rtrim(cpRegionCode)
        AND node_query_flag='Y'
        AND nvl(substr(attribute2,1,instr(attribute2,'+')-1), attribute_code)  NOT IN (
            SELECT DISTINCT nvl( substr(attribute_name ,1,instr(attribute_name ,'+')-1), attribute_code)
            FROM bis_user_attributes
            WHERE page_id=pPageId
            AND 	user_id=pUserId
          UNION
            SELECT DISTINCT nvl( substr(attribute2,1,instr(attribute2,'+')-1), attribute_code)
            FROM ak_region_items
            WHERE region_code =cpNestedRegionCode
        )
        ORDER BY display_sequence;

Line: 380

    select session_value
    from   bis_user_attributes
    where  user_id = cpUserId
    and    session_id = cpSessionId
    and    attribute_name = 'VIEW_BY';

Line: 387

    select attribute_name
    from   bis_user_attributes
    where  user_id = cpUserId
    and    session_id = cpSessionId
    and    ( attribute_name like 'TIME%' or attribute_name like 'EDW_TIME_M%' );

Line: 404

     SELECT nested_region_code
     FROM ak_region_items
     WHERE region_code = cpRegionCode
     AND nested_region_code IS NOT NULL;

Line: 416

  DELETE FROM bis_user_attributes
  WHERE function_name=pFunctionName
  AND user_id=pUserId
  AND session_id=pSessionId
  AND schedule_id IS NULL;

Line: 433

  DELETE FROM bis_user_attributes where session_id= pSessionId AND function_name= pFunctionName
  AND schedule_id is null
  AND user_id=pUserId
  AND attribute_name in
   (SELECT attribute_name from bis_user_attributes where user_id=pUserId and page_id=pPageId);

Line: 440

       insert into bis_user_attributes (USER_ID,
                                      FUNCTION_NAME,
                                      SESSION_ID,
                                      SESSION_VALUE,
                                      SESSION_DESCRIPTION,
                                      ATTRIBUTE_NAME,
                                      DIMENSION,
                                      PERIOD_DATE)
          SELECT  pUserId,
              pFunctionName,
              pSessionId,
              SESSION_VALUE,
              SESSION_DESCRIPTION,
              ATTRIBUTE_NAME,
              DIMENSION,
              PERIOD_DATE
          FROM    bis_user_attributes
          where   user_id = pUserId
          AND page_id = pPageId;

Line: 469

    DELETE FROM bis_user_attributes where session_id= pSessionId AND function_name= pFunctionName
    and schedule_id is null AND user_id=pUserId
    AND attribute_name in
     (SELECT attribute_name from bis_user_attributes where schedule_id=pSCheduleId);

Line: 475

    DELETE FROM bis_user_attributes where session_id= pSessionId AND function_name= pFunctionName
    and schedule_id is null AND user_id=pUserId
    AND attribute_name in
     (SELECT attribute_name||'_FROM' from bis_user_attributes where schedule_id=pSCheduleId);

Line: 481

    DELETE FROM bis_user_attributes where session_id= pSessionId AND function_name= pFunctionName
    and schedule_id is null AND user_id=pUserId
    AND attribute_name in
     (SELECT attribute_name||'_TO' from bis_user_attributes where schedule_id=pSCheduleId);

Line: 536

       insert into bis_user_attributes (USER_ID,
                                      FUNCTION_NAME,
                                      SESSION_ID,
                                      SESSION_VALUE,
                                      SESSION_DESCRIPTION,
                                      ATTRIBUTE_NAME,
                                      DIMENSION,
                                      PERIOD_DATE)
        select  USER_ID,
              pFunctionName,
              SESSION_ID,
              SESSION_VALUE,
              SESSION_DESCRIPTION,
              ATTRIBUTE_NAME,
              DIMENSION,
              PERIOD_DATE
        from    bis_user_attributes
        where   function_name = pPreFunctionName
        and     attribute_name in (vAttributeCodeTable(i),
                                   vAttributeCodeTable(i)||'_FROM',
                                 vAttributeCodeTable(i)||'_TO')
        and     session_id = pSessionId
        and     user_id = pUserId;

Line: 560

       insert into bis_user_attributes (USER_ID,
                                      FUNCTION_NAME,
                                      SESSION_ID,
                                      SESSION_VALUE,
                                      SESSION_DESCRIPTION,
                                      ATTRIBUTE_NAME,
                                      DIMENSION,
                                      PERIOD_DATE)
        select  pUserId,
              pFunctionName,
              pSessionId,
              SESSION_VALUE,
              SESSION_DESCRIPTION,
              ATTRIBUTE_NAME,
              DIMENSION,
              PERIOD_DATE
        from    bis_user_attributes
        where   schedule_id = pScheduleId
        and     attribute_name in (vAttributeCodeTable(i),
                                 vAttributeCodeTable(i)||'_FROM',
                                 vAttributeCodeTable(i)||'_TO');

Line: 588

       insert into bis_user_attributes (USER_ID,
                                      FUNCTION_NAME,
                                      SESSION_ID,
                                      SESSION_VALUE,
                                      SESSION_DESCRIPTION,
                                      ATTRIBUTE_NAME,
                                      DIMENSION,
                                      PERIOD_DATE)
        select  USER_ID,
              pFunctionName,
              SESSION_ID,
              SESSION_VALUE,
              SESSION_DESCRIPTION,
              ATTRIBUTE_NAME,
              DIMENSION,
              PERIOD_DATE
        from    bis_user_attributes
        where   function_name = pPreFunctionName
        and     attribute_name = vDimensionTable(i)||'_HIERARCHY'
        and     session_id = pSessionId
        and     user_id = pUserId;

Line: 613

         DELETE FROM bis_user_attributes
         WHERE session_id =pSessionId
         AND function_name = pFunctionName
         AND schedule_id IS NULL
         AND user_id=pUserId
         AND attribute_name = vDimensionTable(i)||'_HIERARCHY';

Line: 621

       insert into bis_user_attributes (USER_ID,
                                      FUNCTION_NAME,
                                      SESSION_ID,
                                      SESSION_VALUE,
                                      SESSION_DESCRIPTION,
                                      ATTRIBUTE_NAME,
                                      DIMENSION,
                                      PERIOD_DATE)
        select  pUserId,
              pFunctionName,
              pSessionId,
              SESSION_VALUE,
              SESSION_DESCRIPTION,
              ATTRIBUTE_NAME,
              DIMENSION,
              PERIOD_DATE
        from    bis_user_attributes
        where   schedule_id = pScheduleId
        and     attribute_name = vDimensionTable(i)||'_HIERARCHY';

Line: 698

          DELETE FROM bis_user_attributes where session_id= pSessionId AND function_name= pFunctionName
          AND schedule_id is null
          AND user_id=pUserId
          AND attribute_name = 'VIEW_BY';

Line: 703

          INSERT INTO BIS_USER_ATTRIBUTES (user_id, function_name,
                                      session_id, attribute_name,
                                      session_value,
                                      dimension,
                                      creation_date, created_by,
                                      last_update_Date, last_updated_by)
                              VALUES (pUserId, pFunctionName,
                                      pSessionId, 'VIEW_BY' ,
                                      lTimeDimLevels,
                                      lViewByDimension,
                                      sysdate, -1, sysdate, -1);