1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query for usage items in counter group

Discussion in 'Oracle CRM' started by Bharat, Apr 28, 2012.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    SELECT CC.COUNTER_ID,MSIB.SEGMENT1 "USAGE

    ITEM",MSIB.INVENTORY_ITEM_ID,cctt.name "TEMPLATE NAME",CCG.NAME "GROUP

    NAME"
    ,MSIB_NORMAL.SEGMENT1"COVERED ITEM",CII.INSTANCE_ID
    FROM CS_COUNTERS CC,
    MTL_SYSTEM_ITEMS_B MSIB,
    CSI_COUNTER_TEMPLATE_TL cctt,
    CS_COUNTER_GROUPS CCG,
    CSI_CTR_ITEM_ASSOCIATIONS CCIA,
    MTL_SYSTEM_ITEMS_B MSIB_NORMAL,
    CSI_ITEM_INSTANCES CII


    WHERE MSIB.INVENTORY_ITEM_ID=CC.USAGE_ITEM_ID
    AND CC.COUNTER_ID=CCTT.COUNTER_ID
    AND MSIB.ORGANIZATION_ID=&org_id -- org id
    AND CCG.COUNTER_GROUP_ID=CC.COUNTER_GROUP_ID
    AND CCIA.COUNTER_ID=CC.COUNTER_ID
    AND MSIB.SEGMENT1 LIKE '&a' -- item name
    AND MSIB_NORMAL.ORGANIZATION_ID=&org_id -- org id
    AND MSIB_NORMAL.INVENTORY_ITEM_ID=CCIA.INVENTORY_ITEM_ID
    AND CCIA.INVENTORY_ITEM_ID=CII.INVENTORY_ITEM_ID;
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi all,

    The below query works fine when we give usage item and organization id as parameters:

    Code (Text):
    SELECT CC.COUNTER_ID"COUNTER ID"
          ,CC.NAME"COUNTER ITEM"
          ,MSIB.SEGMENT1"USAGE ITEM"

    FROM CS_CSI_COUNTER_GROUPS CCCG,
         CS_COUNTERS CC,
         MTL_SYSTEM_ITEMS_B MSIB

    WHERE
              CC.COUNTER_GROUP_ID=CCCG.COUNTER_GROUP_ID
          AND CC.COUNTER_ID NOT IN(SELECT COUNTER_ID FROM CSI_COUNTER_TEMPLATE_B CCTB WHERE GROUP_ID=CCCG.COUNTER_GROUP_ID)
          AND MSIB.SEGMENT1='&USAGE_ITEM'
          AND CC.USAGE_ITEM_ID=MSIB.INVENTORY_ITEM_ID
          AND MSIB.ORGANIZATION_ID=&ORG_ID;