APPS.AMV_MATCH_PVT SQL Statements

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

Line: 346

  SELECT count(*)
  FROM   amv_matching_queue_tbl;

Line: 521

Select
    content_type_id,
    application_id,
    status_code  status,
    expiration_date
From jtf_amv_items_b
Where item_id = p_item_id;

Line: 530

Select 1
From   jtf_amv_item_authors
Where  item_id = p_item_id;

Line: 535

Select 1
From   jtf_amv_item_keywords
Where  item_id = p_item_id;

Line: 540

Select 1
From   amv_i_item_perspectives
Where  item_id = p_item_id;

Line: 621

       'Select ' ||
           'c.channel_id, ' ||
           'c.match_on_author_flag, ' ||
           'c.match_on_keyword_flag, ' ||
           'c.match_on_perspective_flag, ' ||
           'c.match_on_content_type_flag, ' ||
           'c.match_on_item_type_flag ' ||
      'From  amv_c_channels_b c ';

Line: 637

              'select 1  ' ||
              'from amv_c_chl_item_match match ' ||
              'where match.channel_id = c.channel_id ' ||
              'and   match.item_id = :item_id ' ||
              'and   match.table_name_code = '''||G_MATCH_ITEM_TABLE || ''') ';

Line: 865

Select
    c.match_on_all_criteria_flag,
    c.match_on_author_flag,
    c.match_on_keyword_flag,
    c.match_on_perspective_flag,
    c.match_on_content_type_flag,
    c.match_on_item_type_flag,
    c.application_id,
    c.access_level_type,
    c.channel_type,
    c.status,
    c.expiration_date
From amv_c_channels_b c
Where c.channel_id = p_channel_id;

Line: 997

       'Select ' ||
           'item.item_id ' ||
      'From  jtf_amv_items_b item, amv_c_channels_b chan ';

Line: 1006

                              'select 1  ' ||
                              'from amv_c_chl_item_match match ' ||
                              'where match.channel_id = chan.channel_id ' ||
                              'and match.item_id = item.item_id ' ||
                              'and table_name_code = ''' || G_MATCH_ITEM_TABLE
                                                     || ''' ) ';

Line: 1015

        'And exists (select 1 from amv_c_authors ca, jtf_amv_item_authors ia '||
                      'where ca.channel_id = chan.channel_id ' ||
                      'and ia.item_id = item.item_id ' ||
                      'and ca.author = ia.author) ';

Line: 1022

          'And exists (select 1 from amv_c_keywords ck, ' ||
                      '              jtf_amv_item_keywords ik ' ||
                      'where ck.channel_id = chan.channel_id ' ||
                      'and ik.item_id = item.item_id ' ||
                      'and ck.keyword = ik.keyword) ';

Line: 1030

          'And exists (select 1 from amv_c_chl_perspectives cp, ' ||
                                     'amv_i_item_perspectives ip ' ||
                      'where cp.channel_id = chan.channel_id ' ||
                      'and ip.item_id = item.item_id ' ||
                      'and cp.perspective_id = ip.perspective_id) ';

Line: 1038

          'And exists (select 1 from amv_c_content_types cc ' ||
                      'where cc.channel_id = chan.channel_id ' ||
                      'and cc.content_type_id = item.content_type_id) ';

Line: 1044

          'And exists (select 1 from amv_c_item_types ci ' ||
                      'where ci.channel_id = chan.channel_id ' ||
                      'and ci.item_type = item.item_type) ';

Line: 1154

Select
      approval_status_type
From  amv_c_chl_item_match
Where item_id = p_item_id
And   channel_id = p_channel_id
And   table_name_code = G_MATCH_ITEM_TABLE;

Line: 1278

Select
     content_type_id,
     item_type
From  jtf_amv_items_b
where item_id = p_item_id;

Line: 1285

Select object_version_number
From  amv_c_content_types
Where channel_id = p_channel_id
And   content_type_id = p_content_type_id;

Line: 1291

Select object_version_number
From  amv_c_item_types
Where channel_id = p_channel_id
And   item_type = l_item_type;

Line: 1297

Select c.object_version_number
From  amv_c_authors c, jtf_amv_item_authors i
Where c.channel_id = p_channel_id
And   i.item_id = p_item_id
And   i.author = c.author;

Line: 1304

Select c.object_version_number
From  amv_c_keywords c, jtf_amv_item_keywords i
Where c.channel_id = p_channel_id
And   i.item_id = p_item_id
And   i.keyword = c.keyword;

Line: 1311

Select c.object_version_number
From  amv_c_chl_perspectives c, amv_i_item_perspectives i
Where c.channel_id = p_channel_id
And   i.item_id = p_item_id
And   i.perspective_id = c.perspective_id;

Line: 1529

    l_sql_statement := 'Select 1 ' ||
       'From jtf_amv_items_b item, amv_c_channels_b chan ';

Line: 1535

          'And exists (select 1 from amv_c_authors ca, ' ||
                      '              jtf_amv_item_authors ia ' ||
                      'where ca.channel_id = chan.channel_id ' ||
                      'and ia.item_id = item.item_id ' ||
                      'and ca.author = ia.author) ';

Line: 1543

          'And exists (select 1 from amv_c_keywords ck, ' ||
                      '              jtf_amv_item_keywords ik ' ||
                      'where ck.channel_id = chan.channel_id ' ||
                      'and ik.item_id = item.item_id ' ||
                      'and ck.keyword = ik.keyword) ';

Line: 1551

          'And exists (select 1 from amv_c_chl_perspectives cp, ' ||
                                     'amv_i_item_perspectives ip ' ||
                      'where cp.channel_id = chan.channel_id ' ||
                      'and ip.item_id = item.item_id ' ||
                      'and cp.perspective_id = ip.perspective_id) ';

Line: 1559

          'And exists (select 1 from amv_c_content_types cc ' ||
                      'where cc.channel_id = chan.channel_id ' ||
                      'and cc.content_type_id = item.content_type_id) ';

Line: 1565

          'And exists (select 1 from amv_c_item_types ci ' ||
                      'where ci.channel_id = chan.channel_id ' ||
                      'and ci.item_type = item.item_type) ';

Line: 1658

Select CHANNEL_ITEM_ID
From  AMV_C_CHL_ITEM_MATCH
Where TABLE_NAME_CODE = p_table_name_code
And   ITEM_ID = p_item_id
And   CHANNEL_ID = p_channel_id
;

Line: 1666

select u.user_name,
       r.resource_id
From   amv_rs_all_res_extns_vl r
,	  fnd_user u
where  r.resource_id = res_id
and    u.user_id = r.user_id;

Line: 1674

select
      pub_need_approval_flag,
      channel_category_id,
      AMV_C_CHL_ITEM_MATCH_S.nextval,
      sysdate
from  amv_c_channels_b
Where channel_id = p_channel_id;

Line: 1683

select
      AMV_C_CHL_ITEM_MATCH_S.nextval,
      sysdate
from  dual;

Line: 1689

select owner_id
from  jtf_amv_items_b
where item_id = p_item_id;

Line: 1792

       Insert Into AMV_C_CHL_ITEM_MATCH
       (
           CHANNEL_ITEM_ID,
           OBJECT_VERSION_NUMBER,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           CREATION_DATE,
           CREATED_BY,
           LAST_UPDATE_LOGIN,
           CHANNEL_ID,
           ITEM_ID,
           TABLE_NAME_CODE,
           CHANNEL_CATEGORY_ID,
           APPLICATION_ID,
           APPROVAL_STATUS_TYPE,
           APPROVAL_DATE,
           AVAILABLE_DUE_TO_TYPE,
           AVAILABLE_FOR_CHANNEL_DATE
       )
       VALUES
       (
           l_match_id,
           1,
           l_current_date,
           l_current_user_id,
           l_current_date,
           l_current_user_id,
           l_current_login_id,
           p_channel_id,
           p_item_id,
           p_table_name_code,
           l_channel_category_id,
           p_application_id,
           l_approval_flag,
           l_current_date,
           p_match_type,
           l_current_date
       );

Line: 1854

       Insert Into AMV_C_CHL_ITEM_MATCH
       (
           CHANNEL_ITEM_ID,
           OBJECT_VERSION_NUMBER,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           CREATION_DATE,
           CREATED_BY,
           LAST_UPDATE_LOGIN,
           CHANNEL_ID,
           ITEM_ID,
           TABLE_NAME_CODE,
           CHANNEL_CATEGORY_ID,
           APPLICATION_ID,
           APPROVAL_STATUS_TYPE,
           AVAILABLE_DUE_TO_TYPE,
           AVAILABLE_FOR_CHANNEL_DATE
       )
       VALUES
       (
           l_match_id,
           1,
           l_current_date,
           l_current_user_id,
           l_current_date,
           l_current_user_id,
           l_current_login_id,
           null,
           p_item_id,
           p_table_name_code,
           p_category_id,
           p_application_id,
           AMV_UTILITY_PVT.G_APPROVED,
           p_match_type,
           l_current_date
       );

Line: 1981

  SELECT item_id
  FROM   amv_c_chl_item_match
  WHERE  table_name_code = cv_table_name_code
  AND    item_id         = cv_item_id
  AND    territory_id    = cv_territory_id
  AND    channel_id          IS NULL
  AND    channel_category_id IS NULL;

Line: 1994

  SELECT item_id
  FROM   amv_c_chl_item_match
  WHERE  table_name_code     = cv_table_name_code
  AND    item_id             = cv_item_id
  AND    channel_category_id = cv_category_id
  AND    channel_id IS NULL;

Line: 2003

Select CHANNEL_ITEM_ID
From  AMV_C_CHL_ITEM_MATCH
Where TABLE_NAME_CODE = p_table_name_code
And   ITEM_ID = p_item_id
And   CHANNEL_ID = p_channel_id
;

Line: 2011

select u.user_name,
       r.resource_id
From   amv_rs_all_res_extns_vl r
,	  fnd_user u
where  r.resource_id = res_id
and    u.user_id = r.user_id;

Line: 2019

select
      pub_need_approval_flag,
      channel_category_id,
      AMV_C_CHL_ITEM_MATCH_S.nextval,
      sysdate
from  amv_c_channels_b
Where channel_id = p_channel_id;

Line: 2028

select
      AMV_C_CHL_ITEM_MATCH_S.nextval,
      sysdate
from  dual;

Line: 2101

    INSERT INTO AMV_C_CHL_ITEM_MATCH
      (CHANNEL_ITEM_ID
      ,OBJECT_VERSION_NUMBER
      ,LAST_UPDATE_DATE
      ,LAST_UPDATED_BY
      ,CREATION_DATE
      ,CREATED_BY
      ,LAST_UPDATE_LOGIN
      ,CHANNEL_ID
      ,ITEM_ID
      ,TABLE_NAME_CODE
      ,CHANNEL_CATEGORY_ID
      ,APPLICATION_ID
      ,APPROVAL_STATUS_TYPE
      ,AVAILABLE_DUE_TO_TYPE
      ,AVAILABLE_FOR_CHANNEL_DATE
      ,TERRITORY_ID
      )
    VALUES
      (l_match_id                   -- CHANNEL_ITEM_ID
      ,1                            -- OBJECT_VERSION_NUMBER
      ,l_current_date               -- LAST_UPDATE_DATE
      ,l_current_user_id            -- LAST_UPDATED_BY
      ,l_current_date               -- CREATION_DATE
      ,l_current_user_id            -- CREATION_BY
      ,l_current_login_id           -- LAST_UPDATE_LOGIN
      ,NULL                         -- CHANNEL_ID
      ,p_item_id                    -- ITEM_ID
      ,p_table_name_code            -- TABLE_NAME_CODE
      ,NULL                         -- CHANNEL_CATEGORY_ID
      ,p_application_id             -- APPLICATION_ID
      ,AMV_UTILITY_PVT.G_APPROVED   -- APPROVAL_STATUS_TYPE
      ,p_match_type                 -- AVAILABLE_DUE_TO_TYPE
      ,l_current_date               -- AVAILABLE_FOR_CHANNEL_DATE
      ,p_territory_tbl(l_rec_count) -- TERRITORY_ID
      );

Line: 2192

       Insert Into AMV_C_CHL_ITEM_MATCH
       (
           CHANNEL_ITEM_ID,
           OBJECT_VERSION_NUMBER,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           CREATION_DATE,
           CREATED_BY,
           LAST_UPDATE_LOGIN,
           CHANNEL_ID,
           ITEM_ID,
           TABLE_NAME_CODE,
           CHANNEL_CATEGORY_ID,
           APPLICATION_ID,
           APPROVAL_STATUS_TYPE,
           APPROVAL_DATE,
           AVAILABLE_DUE_TO_TYPE,
           AVAILABLE_FOR_CHANNEL_DATE
       )
       VALUES
       (
           l_match_id,
           1,
           l_current_date,
           l_current_user_id,
           l_current_date,
           l_current_user_id,
           l_current_login_id,
           p_channel_id,
           p_item_id,
           p_table_name_code,
           l_channel_category_id,
           p_application_id,
           l_approval_flag,
           l_current_date,
           p_match_type,
           l_current_date
       );

Line: 2267

        Insert Into AMV_C_CHL_ITEM_MATCH
          (
           CHANNEL_ITEM_ID,
           OBJECT_VERSION_NUMBER,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           CREATION_DATE,
           CREATED_BY,
           LAST_UPDATE_LOGIN,
           CHANNEL_ID,
           ITEM_ID,
           TABLE_NAME_CODE,
           CHANNEL_CATEGORY_ID,
           APPLICATION_ID,
           APPROVAL_STATUS_TYPE,
           AVAILABLE_DUE_TO_TYPE,
           AVAILABLE_FOR_CHANNEL_DATE
          )
        VALUES
          (
           l_match_id,
           1,
           l_current_date,
           l_current_user_id,
           l_current_date,
           l_current_user_id,
           l_current_login_id,
           null,
           p_item_id,
           p_table_name_code,
           p_category_id,
           p_application_id,
           AMV_UTILITY_PVT.G_APPROVED,
           p_match_type,
           l_current_date
          );

Line: 2384

select
     u.user_name,
     c.owner_user_id
From  fnd_user u, jtf_rs_resource_extns r, amv_c_channels_b c
Where c.channel_id = p_id
And   r.resource_id = c.owner_user_id
And   r.user_id = u.user_id
;

Line: 2394

select
      pub_need_approval_flag,
      channel_category_id,
      AMV_C_CHL_ITEM_MATCH_S.nextval,
      sysdate
from  amv_c_channels_b
Where channel_id = p_channel_id;

Line: 2403

select
      AMV_C_CHL_ITEM_MATCH_S.nextval,
      sysdate
from  dual;

Line: 2451

      DELETE FROM amv_c_chl_item_match
	  WHERE  territory_id = p_territory_id
	  AND item_id = p_item_id
	  AND table_name_code = p_table_name_code
	  AND channel_id IS NULL
      AND channel_category_id IS NULL;

Line: 2461

  	  -- delete from channel
         DELETE FROM amv_c_chl_item_match
  	  WHERE  channel_id = p_channel_id
  	  AND item_id = p_item_id
  	  AND table_name_code = p_table_name_code;

Line: 2467

      ELSE -- delete the item to category match.
         --check category id
         IF (AMV_UTILITY_PVT.Is_CategoryIdValid(p_category_id) <> TRUE) THEN
            IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
                FND_MESSAGE.Set_name('AMV','AMV_INVALID_CATEGORY_ID');

Line: 2478

         DELETE FROM amv_c_chl_item_match
  	  WHERE  channel_category_id = p_category_id
  	  AND item_id = p_item_id
  	  AND table_name_code = p_table_name_code
  	  AND channel_id is null;

Line: 2557

SELECT DISTINCT
       RSC.terr_id
      ,TERR.name
FROM   jtf_terr_rsc_all RSC
      ,jtf_terr_srch_adv_gen_v TERR
WHERE RSC.terr_id       = TERR.terr_id
AND   RSC.resource_id   = p_resource_id
AND   RSC.resource_type = p_resource_type;

Line: 2674

SELECT DISTINCT
      item_id
FROM  amv_c_chl_item_match
WHERE territory_id = p_terr_id
AND   channel_id          IS NULL
AND   channel_category_id IS NULL
AND   table_name_code = p_table_name_code;