APPS.AMS_ACTPRODUCT_PVT SQL Statements

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

Line: 76

  SELECT NVL(SUM(scan_unit - scan_unit_remaining), 0)
    FROM ozf_funds_utilized_all_b
   WHERE activity_product_id = p_activity_product_id;

Line: 118

   SELECT resource_id
   FROM ams_jtf_rs_emp_v
   WHERE user_id = l_user_id;

Line: 123

        SELECT ams_act_products_s.NEXTVAL
        FROM dual;

Line: 192

        if AMS_ACCESS_PVT.check_update_access(l_act_Product_rec.act_product_used_by_id,l_act_Product_rec.arc_act_product_used_by, l_res_id, 'USER') = 'N'  then
                IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
         FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS'); --reusing message

Line: 206

        INSERT INTO AMS_ACT_PRODUCTS
        (
        activity_product_id,
        last_update_date,
        last_updated_by,
        creation_date,
        created_by,
        last_update_login,
        object_version_number,
        act_product_used_by_id,
        arc_act_product_used_by,
        inventory_item_id,
        organization_id,
        category_id,
        category_set_id,
        level_type_code,
        product_sale_type,
        primary_product_flag,
        enabled_flag,
        excluded_flag,
        attribute_category,
        attribute1,
        attribute2,
        attribute3,
        attribute4,
        attribute5,
        attribute6,
        attribute7,
        attribute8,
        attribute9,
        attribute10,
        attribute11,
        attribute12,
        attribute13,
        attribute14,
        attribute15,
        --security_group_id,
        line_lumpsum_amount,
        line_lumpsum_qty,
        channel_id,
        uom_code,
        quantity,
        scan_value,
        scan_unit_forecast,
        adjustment_flag)
        VALUES
        (
        l_act_Product_rec.activity_product_id,
        -- standard who columns
        sysdate,
        FND_GLOBAL.User_Id,
        sysdate,
        FND_GLOBAL.User_Id,
        FND_GLOBAL.Conc_Login_Id,
        1,  -- object_version_number
        l_act_Product_rec.act_product_used_by_id,
        l_act_Product_rec.arc_act_product_used_by,
        l_act_Product_rec.inventory_item_id,
        l_act_Product_rec.organization_id,
        l_act_Product_rec.category_ID,
        l_act_Product_rec.category_set_id,
        l_act_Product_rec.level_type_code,
        l_act_Product_rec.PRODUCT_SALE_TYPE,
        nvl(l_act_Product_rec.PRIMARY_PRODUCT_FLAG,'N'),
        nvl(l_act_Product_rec.ENABLED_FLAG,'Y'),
        nvl(l_act_Product_rec.EXCLUDED_FLAG,'N'),
        l_act_Product_rec.attribute_category,
        l_act_Product_rec.attribute1,
        l_act_Product_rec.attribute2,
        l_act_Product_rec.attribute3,
        l_act_Product_rec.attribute4,
        l_act_Product_rec.attribute5,
        l_act_Product_rec.attribute6,
        l_act_Product_rec.attribute7,
        l_act_Product_rec.attribute8,
        l_act_Product_rec.attribute9,
        l_act_Product_rec.attribute10,
        l_act_Product_rec.attribute11,
        l_act_Product_rec.attribute12,
        l_act_Product_rec.attribute13,
        l_act_Product_rec.attribute14,
        l_act_Product_rec.attribute15,
        --l_act_Product_rec.security_group_id,
        l_act_Product_rec.line_lumpsum_amount,
        l_act_Product_rec.line_lumpsum_qty,
        l_act_Product_rec.channel_id,
        DECODE(l_act_Product_rec.uom_code, NULL, 'Ea', FND_API.G_MISS_CHAR, 'Ea', l_act_Product_rec.uom_code),
        DECODE(l_act_Product_rec.quantity, NULL, 1, FND_API.G_MISS_NUM, 1, l_act_Product_rec.quantity),
        l_act_Product_rec.scan_value,
        l_act_Product_rec.scan_unit_forecast,
        l_act_Product_rec.adjustment_flag);

Line: 384

PROCEDURE Update_Act_Product
( p_api_version         IN      NUMBER,
  p_init_msg_list               IN      VARCHAR2        := FND_API.G_FALSE,
  p_commit                      IN      VARCHAR2        := FND_API.G_FALSE,
  p_validation_level    IN      NUMBER  := FND_API.G_VALID_LEVEL_FULL,
  x_return_status               OUT NOCOPY     VARCHAR2,
  x_msg_count                   OUT NOCOPY     NUMBER,
  x_msg_data                    OUT NOCOPY     VARCHAR2,
  p_act_Product_rec     IN      act_Product_rec_type
) IS
        l_api_name                      CONSTANT VARCHAR2(30)  := 'Update_Act_Product';

Line: 404

   SELECT resource_id
   FROM ams_jtf_rs_emp_v
   WHERE user_id = l_user_id;

Line: 410

        SAVEPOINT Update_Act_Product_PVT;

Line: 437

                  p_validation_mode     => JTF_PLSQL_API.g_update,
                  x_return_status               => l_return_status
                );

Line: 470

        if AMS_ACCESS_PVT.check_update_access(l_act_Product_rec.act_product_used_by_id,l_act_Product_rec.arc_act_product_used_by, l_res_id, 'USER') = 'N' then
                IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
         FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS'); --reusing message

Line: 481

        update AMS_ACT_PRODUCTS
        set
                last_update_date = sysdate
                ,last_updated_by =  FND_GLOBAL.User_Id
                ,last_update_login = FND_GLOBAL.Conc_Login_Id
                ,object_version_number = l_act_Product_rec.object_version_number+1
                ,act_product_used_by_id = l_act_Product_rec.act_product_used_by_id
                ,arc_act_product_used_by = l_act_Product_rec.arc_act_product_used_by
                ,organization_id = l_act_Product_rec.organization_id
                ,inventory_item_id = l_act_Product_rec.inventory_item_id
                ,category_id = l_act_Product_rec.category_id
                ,category_set_id = l_act_Product_rec.category_set_id
                ,level_type_code = l_act_Product_rec.level_type_code
                ,product_sale_type = l_act_Product_rec.product_sale_type
                ,primary_product_flag = l_act_Product_rec.primary_product_flag
                ,enabled_flag = l_act_Product_rec.enabled_flag
                ,excluded_flag = l_act_Product_rec.excluded_flag
                ,attribute_category = l_act_Product_rec.attribute_category
                ,attribute1 = l_act_Product_rec.attribute1
                ,attribute2 = l_act_Product_rec.attribute2
                ,attribute3 = l_act_Product_rec.attribute3
                ,attribute4 = l_act_Product_rec.attribute4
                ,attribute5 = l_act_Product_rec.attribute5
                ,attribute6 = l_act_Product_rec.attribute6
                ,attribute7 = l_act_Product_rec.attribute7
                ,attribute8 = l_act_Product_rec.attribute8
                ,attribute9 = l_act_Product_rec.attribute9
                ,attribute10 = l_act_Product_rec.attribute10
                ,attribute11 = l_act_Product_rec.attribute11
                ,attribute12 = l_act_Product_rec.attribute12
                ,attribute13 = l_act_Product_rec.attribute13
                ,attribute14 = l_act_Product_rec.attribute14
                ,attribute15 = l_act_Product_rec.attribute15
                --,security_group_id = l_act_product_rec.security_group_id
                ,line_lumpsum_amount = l_act_product_rec.line_lumpsum_amount
                ,line_lumpsum_qty = l_act_product_rec.line_lumpsum_qty
                ,channel_id = l_act_Product_rec.channel_id
                ,uom_code = DECODE(l_act_Product_rec.uom_code, NULL, 'Ea', FND_API.G_MISS_CHAR, 'Ea', l_act_Product_rec.uom_code)
                ,quantity = DECODE(l_act_Product_rec.quantity, NULL, 1, FND_API.G_MISS_NUM, 1, l_act_Product_rec.quantity)
                ,scan_value = l_act_Product_rec.scan_value
                ,scan_unit_forecast = l_act_Product_rec.scan_unit_forecast
                ,adjustment_flag = l_act_Product_rec.adjustment_flag
        WHERE activity_product_id = l_act_Product_rec.activity_product_id
       AND object_version_number = l_act_Product_rec.object_version_number;

Line: 552

                ROLLBACK TO Update_Act_Product_PVT;

Line: 560

                ROLLBACK TO Update_Act_Product_PVT;

Line: 568

                ROLLBACK TO Update_Act_Product_PVT;

Line: 579

END Update_Act_Product;

Line: 592

PROCEDURE Delete_Act_Product
( p_api_version         IN     NUMBER,
  p_init_msg_list               IN     VARCHAR2 := FND_API.G_FALSE,
  p_commit                      IN     VARCHAR2 := FND_API.G_FALSE,
  p_validation_level    IN     NUMBER           := FND_API.G_VALID_LEVEL_FULL,
  x_return_status               OUT NOCOPY    VARCHAR2,
  x_msg_count                   OUT NOCOPY    NUMBER,
  x_msg_data                    OUT NOCOPY    VARCHAR2,
  p_act_product_id              IN     NUMBER,
  p_object_version       IN     NUMBER
) IS
        l_api_name              CONSTANT VARCHAR2(30)  := 'Delete_Act_Product';

Line: 618

   SELECT distinct a.ARC_ACT_PRODUCT_USED_BY, a.ACT_PRODUCT_USED_BY_ID
     FROM ams_act_products a, ams_act_products b
   WHERE  a.ARC_ACT_PRODUCT_USED_BY = b.ARC_ACT_PRODUCT_USED_BY
   AND a.ACT_PRODUCT_USED_BY_ID = b.ACT_PRODUCT_USED_BY_ID
   AND b.ACTIVITY_PRODUCT_ID = l_act_id;

Line: 625

     SELECT 'dummy'
     FROM ams_act_products
   WHERE  ARC_ACT_PRODUCT_USED_BY = c_obj_type
   AND ACT_PRODUCT_USED_BY_ID = c_obj_id;

Line: 631

   SELECT resource_id
   FROM ams_jtf_rs_emp_v
   WHERE user_id = l_user_id;

Line: 636

   SELECT arc_act_product_used_by, act_product_used_by_id
   FROM ams_act_products
   WHERE activity_product_id = l_actprd_id;

Line: 642

        SAVEPOINT Delete_Act_Product_PVT;

Line: 681

        if AMS_ACCESS_PVT.check_update_access(l_acc_obj_id, l_acc_obj, l_res_id, 'USER') <> 'F' then
                IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
         FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS'); --reusing message

Line: 697

                DELETE FROM AMS_ACT_PRODUCTS
                WHERE  activity_product_id = l_act_product_id
                  and  object_version_number = p_object_version;

Line: 766

                ROLLBACK TO Delete_Act_Product_PVT;

Line: 774

                ROLLBACK TO Delete_Act_Product_PVT;

Line: 788

                ROLLBACK TO Delete_Act_Product_PVT;

Line: 799

END Delete_Act_Product;

Line: 828

        SELECT activity_product_id
          FROM AMS_ACT_PRODUCTS
         WHERE activity_product_id = p_act_product_id
           AND object_version_number = p_object_version
           FOR UPDATE of activity_product_id NOWAIT;

Line: 1054

   SELECT distinct primary_product_flag
   FROM ams_act_products
   WHERE category_set_id = p_category_set_id
   AND act_product_used_by_id  = p_act_product_used_by_id
   AND arc_act_product_used_by =  p_arc_act_product_used_by
   AND primary_product_flag = 'Y';

Line: 1137

        select level_type_code
        from AMS_ACT_PRODUCTS
        where ARC_ACT_PRODUCT_USED_BY = p_act_Product_rec.ARC_ACT_PRODUCT_USED_BY
        and   ACT_PRODUCT_USED_BY_ID = p_act_Product_rec.ACT_PRODUCT_USED_BY_ID;

Line: 1143

                select 'Y'
                  from ozf_funds_all_b
         where fund_id = l_fund_id
           and fund_type = 'FULLY_ACCRUED'
           and accrual_discount_level = 'ORDER' ;

Line: 1150

  SELECT OFFER_TYPE, custom_setup_id
    FROM ams_offers
   WHERE qp_list_header_id = p_act_Product_rec.act_product_used_by_id;

Line: 1691

        select category_id
        from AMS_ACT_PRODUCTS
        where ARC_ACT_PRODUCT_USED_BY = p_act_Product_rec.ARC_ACT_PRODUCT_USED_BY
        and   ACT_PRODUCT_USED_BY_ID = p_act_Product_rec.ACT_PRODUCT_USED_BY_ID
        and   EXCLUDED_FLAG = 'N';

Line: 1698

        select category_id
        from AMS_ACT_PRODUCTS
        where ARC_ACT_PRODUCT_USED_BY = p_act_Product_rec.ARC_ACT_PRODUCT_USED_BY
        and   ACT_PRODUCT_USED_BY_ID = p_act_Product_rec.ACT_PRODUCT_USED_BY_ID
        and  level_type_code = 'FAMILY'  -- musman: in prod assoc also we store cat id for lite
        and   CATEGORY_ID IS NOT NULL;

Line: 1706

        select INVENTORY_ITEM_ID
        from AMS_ACT_PRODUCTS
        where ARC_ACT_PRODUCT_USED_BY = p_act_Product_rec.ARC_ACT_PRODUCT_USED_BY
        and   ACT_PRODUCT_USED_BY_ID = p_act_Product_rec.ACT_PRODUCT_USED_BY_ID
        and   INVENTORY_ITEM_ID IS NOT NULL;

Line: 1716

        select 1
        from dual
        where exists (  select 1
                                        from MTL_ITEM_CATEGORIES
                                        where INVENTORY_ITEM_ID = p_act_Product_rec.INVENTORY_ITEM_ID
                                        and   CATEGORY_ID = l_cat_id);

Line: 1725

  SELECT COUNT(*)
    FROM ams_act_products
   WHERE arc_act_product_used_by = 'OFFR'
     AND act_product_used_by_id = p_act_Product_rec.act_product_used_by_id
     AND category_id = p_act_product_rec.category_id
     AND channel_id = p_act_Product_rec.channel_id
     AND excluded_flag = 'N';

Line: 1734

  SELECT COUNT(*)
    FROM ams_act_products
   WHERE arc_act_product_used_by = 'OFFR'
     AND act_product_used_by_id = p_act_Product_rec.act_product_used_by_id
     AND category_id = p_act_product_rec.category_id
     AND channel_id IS NULL
     AND excluded_flag = 'N';

Line: 1743

  SELECT COUNT(*)
    FROM ams_act_products
   WHERE arc_act_product_used_by = 'OFFR'
     AND act_product_used_by_id = p_act_Product_rec.act_product_used_by_id
     AND inventory_item_id = p_act_Product_rec.inventory_item_id
     AND channel_id = p_act_Product_rec.channel_id
     AND excluded_flag = 'N';

Line: 1752

  SELECT count(*)
    FROM ams_act_products
   WHERE arc_act_product_used_by = 'OFFR'
     AND act_product_used_by_id = p_act_Product_rec.act_product_used_by_id
     AND inventory_item_id = p_act_Product_rec.inventory_item_id
     AND channel_id IS NULL
     AND excluded_flag = 'N';

Line: 1761

  SELECT category_id
    FROM ams_act_products
   WHERE arc_act_product_used_by = p_act_product_rec.arc_act_product_used_by
     AND act_product_used_by_id = p_act_product_rec.act_product_used_by_id
     AND channel_id = p_act_Product_rec.channel_id
     AND category_id IS NOT NULL;

Line: 1769

  SELECT category_id
    FROM ams_act_products
   WHERE arc_act_product_used_by = p_act_product_rec.arc_act_product_used_by
     AND act_product_used_by_id = p_act_product_rec.act_product_used_by_id
     AND channel_id IS NULL
     AND category_id IS NOT NULL;

Line: 1777

  SELECT inventory_item_id
    FROM ams_act_products
   WHERE arc_act_product_used_by = p_act_product_rec.arc_act_product_used_by
     AND act_product_used_by_id = p_act_product_rec.act_product_used_by_id
     AND channel_id = p_act_Product_rec.channel_id
     AND inventory_item_id IS NOT NULL;

Line: 1785

  SELECT inventory_item_id
    FROM ams_act_products
   WHERE arc_act_product_used_by = p_act_product_rec.arc_act_product_used_by
     AND act_product_used_by_id = p_act_product_rec.act_product_used_by_id
     AND channel_id IS NULL
     AND inventory_item_id IS NOT NULL;

Line: 1793

  SELECT 1
    FROM DUAL
   WHERE EXISTS(SELECT 1
                  FROM mtl_item_categories
                 WHERE category_id = l_cat_id
                   AND inventory_item_id = l_item_id);

Line: 1803

  SELECT offer_type
    FROM ams_offers
   WHERE qp_list_header_id = l_id;

Line: 2072

        SELECT *
        FROM ams_act_products
        WHERE activity_product_id = p_act_Product_rec.activity_product_id;

Line: 2225

SELECT CATEGORY_CONCAT_SEGS
  FROM mtl_categories_v
 WHERE category_id = p_category_id;

Line: 2230

SELECT
NVL(d.category_desc, category_concat_segs) categoryName
FROM
   mtl_default_category_sets a ,
   mtl_category_sets_b b ,
   mtl_categories_v c ,
   ENI_PROD_DEN_HRCHY_PARENTS_V d
WHERE
    a.functional_area_id in (7,11)
AND a.category_set_id = b.category_set_id
AND b.structure_id = c.structure_id
AND c.category_id = d.category_id(+)
AND c.category_id = p_category_id;

Line: 2245

SELECT c.category_concat_segs
FROM mtl_categories_v c
WHERE c.category_id = p_category_id;

Line: 2254

SELECT ATTRIBUTE_LABEL_LONG
from AK_REGION_ITEMS_VL
where region_code like 'AMS_COMPETITOR_PRODUCTS'
and attribute_code like 'AMS_INVALID';

Line: 2260

SELECT
  ARAT.ATTRIBUTE_LABEL_LONG
FROM
  AK_REGION_ITEMS_TL ARAT,
  AK_REGION_ITEMS ARA
WHERE
  ARAT.REGION_APPLICATION_ID = ARA.REGION_APPLICATION_ID AND
  ARAT.REGION_CODE = ARA.REGION_CODE AND
  ARAT.ATTRIBUTE_APPLICATION_ID = ARA.ATTRIBUTE_APPLICATION_ID AND
  ARAT.ATTRIBUTE_CODE = ARA.ATTRIBUTE_CODE AND
  ARAT.LANGUAGE = USERENV('LANG') AND
  ARA.REGION_CODE = 'AMS_COMPETITOR_PRODUCTS' AND
  ARA.ATTRIBUTE_CODE = 'AMS_INVALID';

Line: 2319

SELECT  CONCATENATED_DESCRIPTION
  FROM ams_mtl_Categories_denorm_vl
 WHERE category_id = p_category_id;

Line: 2324

SELECT
NVL(d.concat_cat_parentage, c.description) categoryDescr
FROM
   mtl_default_category_sets a ,
   mtl_category_sets_b b ,
   mtl_categories_v c ,
   ENI_PROD_DEN_HRCHY_PARENTS_V d
WHERE
    a.functional_area_id in (7,11)
AND a.category_set_id = b.category_set_id
AND b.structure_id = c.structure_id
AND c.category_id = d.category_id(+)
AND c.category_id = p_category_id;

Line: 2339

SELECT c.description
FROM mtl_categories_v c
WHERE c.category_id = p_category_id;

Line: 2348

SELECT ATTRIBUTE_LABEL_LONG
from AK_REGION_ITEMS_VL
where region_code like 'AMS_COMPETITOR_PRODUCTS'
and attribute_code like 'AMS_INVALID';

Line: 2354

SELECT
  ARAT.ATTRIBUTE_LABEL_LONG
FROM
  AK_REGION_ITEMS_TL ARAT,
  AK_REGION_ITEMS ARA
WHERE
  ARAT.REGION_APPLICATION_ID = ARA.REGION_APPLICATION_ID AND
  ARAT.REGION_CODE = ARA.REGION_CODE AND
  ARAT.ATTRIBUTE_APPLICATION_ID = ARA.ATTRIBUTE_APPLICATION_ID AND
  ARAT.ATTRIBUTE_CODE = ARA.ATTRIBUTE_CODE AND
  ARAT.LANGUAGE = USERENV('LANG') AND
  ARA.REGION_CODE = 'AMS_COMPETITOR_PRODUCTS' AND
  ARA.ATTRIBUTE_CODE = 'AMS_INVALID';

Line: 2429

FUNCTION UPDATE_SCHEDULE_ACTIVITIES(p_subscription_guid   IN       RAW,
                 p_event               IN OUT NOCOPY  WF_EVENT_T
) RETURN VARCHAR2
IS
   l_schedule_id     NUMBER;

Line: 2441

   SELECT assoc.association_id, assoc.content_item_id, ci.live_citem_version_id
     FROM ibc_associations assoc, ibc_content_Items ci
     --by musman:as per r12 requirement,live version stamping should be done for collab content
    WHERE assoc.association_type_code in ('AMS_PLCE') --('AMS_COLB','AMS_PLCE')
      AND assoc.associated_object_val1 = to_char(l_csch_id) --musman:bug 4145845 Fix
      AND assoc.content_item_id = ci.content_Item_id;

Line: 2450

        SELECT activity_product_id
        from ams_act_products act, ams_campaign_schedules_b csc
        where act.ARC_ACT_PRODUCT_USED_BY = 'CSCH'
        and act.ACT_PRODUCT_USED_BY_ID = l_csch_id
        and act.LEVEL_TYPE_CODE = 'FAMILY'
        and act.ACT_PRODUCT_USED_BY_ID = csc.SCHEDULE_ID
        and csc.USAGE = 'LITE';

Line: 2458

PROCEDURE_NAME CONSTANT    VARCHAR2(30) := 'UPDATE_SCHEDULE_ACTIVITIES';

Line: 2474

         Ibc_Associations_Pkg.UPDATE_ROW(
               p_association_id                  => l_association_id
               ,p_citem_version_id               => l_citem_ver_id
               );

Line: 2489

     UPDATE ams_act_products
     SET primary_product_flag = 'Y'
     WHERE activity_product_id =l_act_prod_id;

Line: 2500

      WF_CORE.CONTEXT('AMS_ACT_PRODUCTS','UPDATE_SCHEDULE_ACTIVITIES',
                        p_event.getEventName( ), p_subscription_guid);

Line: 2504

END UPDATE_SCHEDULE_ACTIVITIES;

Line: 2515

SELECT count(*)
FROM   IBC_ASSOCIATIONS IbcAssn,
       ibc_content_items citem
WHERE  IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_sch_id )-- musman:bug 4145845 Fix
       AND IbcAssn.Content_item_id    = citem.content_item_id
       AND citem.content_item_status <> 'APPROVED'
       AND ibcassn.ASSOCIATION_TYPE_CODE  in ('AMS_COLB','AMS_PLCE') ;

Line: 2554

SELECT campaign_id,usage
FROM ams_campaign_schedules_b
WHERE schedule_id = p_act_product_rec.act_product_used_by_id;

Line: 2560

 SELECT 1
 from ams_act_products
 where arc_act_product_used_by = 'CAMP'
 and act_product_used_by_id = l_campaign_id
 and level_type_code = 'PRODUCT'
 and organization_id = p_act_product_rec.organization_id
 and inventory_item_id = p_act_product_rec.inventory_item_id;

Line: 2570

 SELECT 1
 from ams_act_products a
 ,mtl_item_categories ml
 where arc_act_product_used_by = 'CAMP'
 and act_product_used_by_id = l_campaign_id
 and level_type_code = 'FAMILY'
 and a.category_id = p_act_product_rec.category_id
 and ml.organization_id = p_act_product_rec.organization_id
 and ml.inventory_item_id = p_act_product_rec.inventory_item_id
 and ml.category_id = a.CATEGORY_ID
 and ml.category_set_id = p_act_product_rec.category_SET_id;

Line: 2584

 SELECT 1
 from ams_act_products
 where arc_act_product_used_by = 'CAMP'
 and act_product_used_by_id = l_campaign_id
 and level_type_code = 'FAMILY'
 and category_id = p_act_product_rec.category_id
 and category_set_id = p_act_product_rec.category_set_id;

Line: 2594

 select 1
 from ENI_PROD_DEN_HRCHY_PARENTS_V
 where category_id =  p_act_product_rec.category_id
 start with category_id in (select category_id
                           from ams_act_products
                           where arc_act_product_used_by = 'CAMP'
                            and  act_product_used_by_id =  l_campaign_id
                            and level_type_code = 'FAMILY')
 connect by  prior category_id = category_parent_id     ;

Line: 2611

 SELECT 1
 from mtl_item_categories
 where inventory_item_id = p_act_product_rec.inventory_item_id
 and  category_id = p_act_product_rec.category_id
 and category_set_id = p_act_product_rec.category_set_id;

Line: 2774

   IS Select category_Set_id
   FROM ENI_PROD_DEN_HRCHY_PARENTS_V a
   WHERE rownum <2 ;