APPS.AMS_RUNTIME_PROD_PVT SQL Statements

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

Line: 14

   SELECT start_date_active, end_date_active
   INTO l_start_date, l_end_date
   FROM FND_LOOKUPS
   WHERE lookup_type = 'IBE_RELATIONSHIP_TYPES'
     AND lookup_code = p_relation_type_code
     AND enabled_flag = 'Y';

Line: 64

    SELECT COUNT(B.SECTION_ITEM_ID) INTO rowsReturned FROM JTF_DSP_SECTION_ITEMS s, JTF_DSP_MSITE_SCT_ITEMS b
    WHERE S.SECTION_ITEM_ID = B.SECTION_ITEM_ID AND B.MINI_SITE_ID = p_msite_id
    AND S.INVENTORY_ITEM_ID = p_item_id
    AND NVL(S.START_DATE_ACTIVE,SYSDATE) < SYSDATE
    AND NVL(S.END_DATE_ACTIVE,SYSDATE) > SYSDATE;

Line: 113

   ' AND ICRI.related_item_id IN (SELECT D.item_id FROM AMS_IBA_MS_ITEMS_DENORM D
        WHERE D.MINISITE_ID = :msite_id
              AND NVL(D.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
              AND NVL(D.END_DATE_ACTIVE,SYSDATE) >= SYSDATE )';

Line: 119

   ' AND ICRI.related_item_id IN (SELECT D.item_id FROM AMS_IBA_MS_ITEMS_DENORM D
        WHERE D.MINISITE_ID = :msite_id
              AND D.TOP_SECTION_ID = :top_section_id
              AND NVL(D.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
              AND NVL(D.END_DATE_ACTIVE,SYSDATE) >= SYSDATE )';

Line: 126

   ' AND ICRI.related_item_id NOT IN (SELECT D.item_id FROM AMS_IBA_MS_ITEMS_DENORM D
        WHERE D.MINISITE_ID = :msite_id
              AND D.TOP_SECTION_ID = :top_section_id
              AND D.ITEM_ID = ICRI.related_item_id
              AND NVL(D.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
              AND NVL(D.END_DATE_ACTIVE,SYSDATE) >= SYSDATE )';

Line: 134

'SELECT DISTINCT ICRI.related_item_id
 FROM ibe_ct_related_items ICRI,
      mtl_system_items_b   MSIB
 WHERE ICRI.relation_type_code = :rel_type_code1
   AND NOT EXISTS( SELECT NULL
                   FROM ibe_ct_rel_exclusions ICRE
                   WHERE ICRE.relation_type_code = ICRI.relation_type_code
                     AND ICRE.inventory_item_id  = ICRI.inventory_item_id
                     AND ICRE.related_item_id    = ICRI.related_item_id )
   AND MSIB.organization_id   = :org_id3
   AND MSIB.inventory_item_id = ICRI.related_item_id
   AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
   AND NVL(MSIB.end_date_active, SYSDATE)   >= SYSDATE
   AND MSIB.web_status        = ''PUBLISHED''
   AND ICRI.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 151

' SELECT DISTINCT ICRI.related_item_id
  FROM ibe_ct_related_items ICRI,
      mtl_system_items_b   MSIB
  WHERE ICRI.relation_type_code = :rel_type_code1
   AND MSIB.organization_id    = :org_id2
   AND MSIB.inventory_item_id  = ICRI.related_item_id
   AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
   AND NVL(MSIB.end_date_active, SYSDATE)   >= SYSDATE
   AND MSIB.web_status         = ''PUBLISHED''
   AND ICRI.inventory_item_id  IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 164

  SELECT ICRE.related_item_id
  FROM ibe_ct_rel_exclusions ICRE
  WHERE ICRE.relation_type_code = :rel_type_code1
   AND ICRE.inventory_item_id  IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 171

    SELECT DISTINCT ICRI.related_item_id
    BULK COLLECT INTO :items_tbl1
    FROM ibe_ct_related_items ICRI,
         mtl_system_items_b   MSIB
    WHERE ICRI.relation_type_code = :rel_type_code2
      AND NOT EXISTS( SELECT NULL
                      FROM ibe_ct_rel_exclusions ICRE
                      WHERE ICRE.relation_type_code = ICRI.relation_type_code
                        AND ICRE.inventory_item_id  = ICRI.inventory_item_id
                        AND ICRE.related_item_id    = ICRI.related_item_id )
      AND MSIB.organization_id   = :org_id4
      AND MSIB.inventory_item_id = ICRI.related_item_id
      AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
      AND NVL(MSIB.end_date_active, SYSDATE)   >= SYSDATE
      AND MSIB.web_status        = ''PUBLISHED''
      AND ICRI.inventory_item_id  IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 445

' SELECT DISTINCT ICRI.related_item_id
  FROM ibe_ct_related_items ICRI,
      mtl_system_items_b   MSIB
  WHERE ICRI.relation_type_code = :rel_type_code1
   AND NOT EXISTS( SELECT NULL
                   FROM ibe_ct_rel_exclusions ICRE
                   WHERE ICRE.relation_type_code = ICRI.relation_type_code
                     AND ICRE.inventory_item_id  = ICRI.inventory_item_id
                     AND ICRE.related_item_id    = ICRI.related_item_id )
   AND MSIB.organization_id   = :org_id2
   AND MSIB.inventory_item_id = ICRI.related_item_id
   AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
   AND NVL(MSIB.end_date_active, SYSDATE)   >= SYSDATE
   AND MSIB.web_status        = ''PUBLISHED''
   AND ICRI.inventory_item_id  IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 463

' SELECT DISTINCT ICRI.related_item_id
  FROM ibe_ct_related_items ICRI,
      mtl_system_items_b   MSIB
  WHERE ICRI.relation_type_code = :rel_type_code1
   AND MSIB.organization_id    = :org_id2
   AND MSIB.inventory_item_id  = ICRI.related_item_id
   AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
   AND NVL(MSIB.end_date_active, SYSDATE)   >= SYSDATE
   AND MSIB.web_status         = ''PUBLISHED''
   AND ICRI.inventory_item_id  IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 477

 SELECT MRI.related_item_id
 FROM mtl_related_items  MRI,
      mtl_system_items_b MSIB
 WHERE MRI.relationship_type_id = DECODE(:rel_type_code5, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
                                  3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
                                  7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
                                  11, ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
   AND MSIB.organization_id     = :org_id7
   AND MSIB.inventory_item_id   = MRI.related_item_id
   AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
   AND NVL(MSIB.end_date_active, SYSDATE)   >= SYSDATE
   AND MSIB.web_status          = ''PUBLISHED''
   AND MRI.inventory_item_id  IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 493

  SELECT MRI.inventory_item_id
  FROM mtl_related_items MRI,
      mtl_system_items_b MSIB
  WHERE MRI.relationship_type_id = DECODE(:rel_type_code9, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
                                  3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
                                  7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
                                  11, ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
   AND MSIB.organization_id     = :org_id11
   AND MSIB.inventory_item_id   = MRI.inventory_item_id
   AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
   AND NVL(MSIB.end_date_active, SYSDATE)   >= SYSDATE
   AND MSIB.web_status          = ''PUBLISHED''
   AND MRI.reciprocal_flag      = ''Y''
   AND MRI.related_item_id  IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 510

  SELECT ICRE.related_item_id
  FROM ibe_ct_rel_exclusions ICRE
  WHERE ICRE.relation_type_code = :rel_type_code13
   AND ICRE.inventory_item_id  IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 517

    SELECT DISTINCT ICRI.related_item_id
    BULK COLLECT INTO :items_tbl1
    FROM ibe_ct_related_items ICRI,
         mtl_system_items_b   MSIB
    WHERE ICRI.relation_type_code = :rel_type_code2
      AND NOT EXISTS( SELECT NULL
                      FROM ibe_ct_rel_exclusions ICRE
                      WHERE ICRE.relation_type_code = ICRI.relation_type_code
                        AND ICRE.inventory_item_id  = ICRI.inventory_item_id
                        AND ICRE.related_item_id    = ICRI.related_item_id )
      AND MSIB.organization_id   = :org_id4
      AND MSIB.inventory_item_id = ICRI.related_item_id
      AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
      AND NVL(MSIB.end_date_active, SYSDATE)   >= SYSDATE
      AND MSIB.web_status        = ''PUBLISHED''
      AND ICRI.inventory_item_id  IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 827

   'BEGIN  SELECT MSIB.inventory_item_id
     BULK COLLECT INTO :items_tbl1
        FROM mtl_system_items_b   MSIB,
             ams_iba_ms_items_denorm D
        WHERE MSIB.organization_id   = :org_id2
              AND MSIB.inventory_item_id = D.item_id
              AND D.MINISITE_ID = :msite_id3
              AND NVL(D.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
              AND NVL(D.END_DATE_ACTIVE,SYSDATE) >= SYSDATE
              AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
              AND NVL(MSIB.end_date_active, SYSDATE)   >= SYSDATE
              AND MSIB.web_status  = ''PUBLISHED''
              AND MSIB.inventory_item_id  IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 842

   'SELECT MSIB.inventory_item_id
        FROM mtl_system_items_b   MSIB,
             ams_iba_ms_items_denorm D
        WHERE MSIB.organization_id   = :org_id2
              AND MSIB.inventory_item_id = D.item_id
              AND D.MINISITE_ID = :msite_id3
              AND NVL(D.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
              AND NVL(D.END_DATE_ACTIVE,SYSDATE) >= SYSDATE
              AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
              AND NVL(MSIB.end_date_active, SYSDATE)   >= SYSDATE
              AND MSIB.web_status  = ''PUBLISHED''
              AND MSIB.inventory_item_id  IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 860

        SELECT D.item_id
        FROM ams_iba_ms_items_denorm D
        WHERE D.MINISITE_ID = :msite_id
        AND D.top_section_id = :top_section_id)';

Line: 866

   'BEGIN SELECT MSIB.inventory_item_id
        BULK COLLECT INTO :items_tbl1
        FROM  mtl_system_items_b   MSIB
        WHERE MSIB.organization_id   = :org_id2
              AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
              AND NVL(MSIB.end_date_active, SYSDATE)   >= SYSDATE
              AND MSIB.web_status  = ''PUBLISHED''
              AND MSIB.inventory_item_id  IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 876

   'SELECT MSIB.inventory_item_id
        FROM  mtl_system_items_b   MSIB
        WHERE MSIB.organization_id   = :org_id2
              AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
              AND NVL(MSIB.end_date_active, SYSDATE)   >= SYSDATE
              AND MSIB.web_status  = ''PUBLISHED''
              AND MSIB.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';

Line: 1201

  select inventory_item_id
    bulk collect into l_quote_prod_lst
    from aso_quote_lines_all_v
   where quote_header_id = p_quote_id;

Line: 1524

   'SELECT TO_NUMBER(PA.product_attr_value)
        FROM  qp_list_lines PL,
              qp_pricing_attributes PA
        WHERE PA.list_header_id = :price_list_id
              AND PL.list_line_id = PA.list_line_id
              AND PA.product_attr_value IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_in_prod_lst AS JTF_VARCHAR2_TABLE_100)) t)';

Line: 1778

     select MSIV.primary_uom_code
     from mtl_system_items_vl MSIV
     where MSIV.inventory_item_id = l_itmid;

Line: 1783

   'SELECT MSIV.INVENTORY_ITEM_ID, MSIV.CONCATENATED_SEGMENTS,' ||
    ' MSIV.ORDERABLE_ON_WEB_FLAG, MSIV.PRIMARY_UNIT_OF_MEASURE,' ||
    ' MSIV.PRIMARY_UOM_CODE, MSIV.DESCRIPTION, MSIV.LONG_DESCRIPTION,' ||
    ' MSIV.MINIMUM_ORDER_QUANTITY, MSIV.MAXIMUM_ORDER_QUANTITY' ||
    ' FROM MTL_SYSTEM_ITEMS_VL MSIV ' ||
    ' WHERE MSIV.ORGANIZATION_ID = :org_id' ||
    ' AND MSIV.WEB_STATUS = ''PUBLISHED'''||
    ' AND NVL(MSIV.START_DATE_ACTIVE, SYSDATE) <= SYSDATE ' ||
    ' AND NVL(MSIV.END_DATE_ACTIVE, SYSDATE) >= SYSDATE '   ||
    ' AND MSIV.INVENTORY_ITEM_ID IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_itmid_tbl AS JTF_NUMBER_TABLE)) t)';

Line: 1795

   'SELECT MSIV.INVENTORY_ITEM_ID, MIC.CATEGORY_ID' ||
   ' FROM MTL_SYSTEM_ITEMS_VL MSIV, MTL_ITEM_CATEGORIES MIC' ||
   ' WHERE MSIV.ORGANIZATION_ID = :org_id' ||
   ' AND NVL(MSIV.START_DATE_ACTIVE, SYSDATE) <= SYSDATE' ||
   ' AND NVL(MSIV.END_DATE_ACTIVE, SYSDATE) >= SYSDATE' ||
   ' AND MSIV.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID' ||
   ' AND MSIV.ORGANIZATION_ID = MIC.ORGANIZATION_ID' ||
   ' AND MIC.CATEGORY_SET_ID = :category_set' ||
   ' AND MSIV.INVENTORY_ITEM_ID IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_itmid_tbl AS JTF_NUMBER_TABLE)) t)';

Line: 2033

	   select inventory_item_id
	    bulk collect into l_quote_prod_lst
	    from aso_quote_lines_all_v
	   where quote_header_id = p_quote_id;