[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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';
SELECT COUNT(B.SECTION_ITEM_ID) INTO rowsReturned FROM IBE_DSP_SECTION_ITEMS s, IBE_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;
'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 ICRI.inventory_item_id = :item_id2
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
/*Bug 2922902*/ AND ICRE.organization_id = ICRI.organization_id
AND ICRE.related_item_id = ICRI.related_item_id )
AND MSIB.organization_id = :org_id3
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' ';
'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 ICRI.inventory_item_id = :item_id2
AND MSIB.organization_id = :org_id3
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' ';
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 MRI.inventory_item_id = :item_id6
AND MSIB.organization_id = :org_id7
AND MSIB.organization_id = MRI.organization_id --Bug 2922902
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'' ';
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 MRI.related_item_id = :item_id10
AND MSIB.organization_id = :org_id11
AND MSIB.organization_id = MRI.organization_id --Bug 2922902
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'' ';
SELECT ICRE.related_item_id
FROM ibe_ct_rel_exclusions ICRE
WHERE ICRE.relation_type_code = :rel_type_code13
AND ICRE.inventory_item_id = :item_id14
AND ICRE.organization_id = :org_id15 '; --Bug 2922902
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 ICRI.inventory_item_id = :item_id3
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
/*Bug 2922902*/ AND ICRE.organization_id = ICRI.organization_id
AND ICRE.related_item_id = ICRI.related_item_id )
AND MSIB.organization_id = :org_id4
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' ';
BEGIN -- begin sub-block to handle the SELECT statement's exception
SELECT ICRR.sql_statement
INTO l_sql_stmt
FROM ibe_ct_relation_rules ICRR
WHERE ICRR.relation_type_code = p_rel_type_code
AND ICRR.origin_object_type = 'N'
AND ICRR.dest_object_type = 'N';
END; -- end sub-block to handle the SELECT statement's exception
l_temp_itemids_query CONSTANT VARCHAR2(200) := ' IN (select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)';
l_minisite_stmt CONSTANT VARCHAR2(2000) :=' AND EXISTS (SELECT 1 FROM IBE_DSP_SECTION_ITEMS s, IBE_DSP_MSITE_SCT_ITEMS b
WHERE S.SECTION_ITEM_ID = B.SECTION_ITEM_ID
AND B.MINI_SITE_ID = :msite_id
AND S.INVENTORY_ITEM_ID = ICRI.related_item_id
AND NVL(S.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
AND NVL(S.END_DATE_ACTIVE,SYSDATE) >= SYSDATE )';
' 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 ICRI.inventory_item_id IN
( select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)
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
/*Bug 2922902 */
AND ICRE.organization_id = ICRI.organization_id
AND ICRE.related_item_id = ICRI.related_item_id )
AND MSIB.organization_id = :org_id3
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' ';
'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 ICRI.inventory_item_id
IN ( select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)
AND MSIB.organization_id = :org_id3
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' ';
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'',
''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
AND MRI.inventory_item_id
IN ( select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)
AND MSIB.organization_id = :org_id7
AND MSIB.organization_id = MRI.organization_id --Bug 2922902
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'' ';
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 MRI.related_item_id IN ( select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)
AND MSIB.organization_id = :org_id11
AND MSIB.organization_id = MRI.organization_id --Bug 2922902
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'' ';
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 NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)
AND ICRE.organization_id = :org_id15 '; --Bug 2922902
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 ICRI.inventory_item_id IN (
select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key )
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
/*Bug 2922902*/
AND ICRE.organization_id = ICRI.organization_id
AND ICRE.related_item_id = ICRI.related_item_id )
AND MSIB.organization_id = :org_id4
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' ';
' 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 ICRI.inventory_item_id IN (
select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)
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
/*Bug 2922902*/ AND ICRE.organization_id = ICRI.organization_id
AND ICRE.related_item_id = ICRI.related_item_id )
AND MSIB.organization_id = :org_id3
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' OR MSIB.web_status = ''UNPUBLISHED'') ';
' 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 ICRI.inventory_item_id = :item_id2
AND MSIB.organization_id = :org_id3
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' OR MSIB.web_status = ''UNPUBLISHED'') ';
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 MRI.inventory_item_id = :item_id6
AND MSIB.organization_id = :org_id7
AND MSIB.organization_id = MRI.organization_id --Bug 2922902
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'' OR MSIB.web_status = ''UNPUBLISHED'') ';
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 MRI.related_item_id = :item_id10
AND MSIB.organization_id = :org_id11
AND MSIB.organization_id = MRI.organization_id --Bug 2922902
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'' OR MSIB.web_status = ''UNPUBLISHED'')
AND MRI.reciprocal_flag = ''Y'' ';
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 ICRI.inventory_item_id = :item_id3
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
/*Bug 2922902*/AND ICRE.organization_id = ICRI.organization_id
AND ICRE.related_item_id = ICRI.related_item_id )
AND MSIB.organization_id = :org_id4
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' OR MSIB.web_status = ''UNPUBLISHED'') ';
' 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 ICRI.inventory_item_id = :item_id2
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.organization_id = ICRI.organization_id /*Bug 2922902 */
AND ICRE.related_item_id = ICRI.related_item_id )
AND MSIB.organization_id = :org_id3
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' ';
' 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 ICRI.inventory_item_id = :item_id2
AND MSIB.organization_id = :org_id3
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' ';
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 MRI.inventory_item_id = :item_id6
AND MSIB.organization_id = :org_id7
AND MSIB.organization_id = MRI.organization_id --Bug 2922902
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'' ';
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 MRI.related_item_id = :item_id10
AND MSIB.organization_id = :org_id11
AND MSIB.organization_id = MRI.organization_id --Bug 2922902
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'' ';
SELECT ICRE.related_item_id
FROM ibe_ct_rel_exclusions ICRE
WHERE ICRE.relation_type_code = :rel_type_code13
AND ICRE.inventory_item_id = :item_id14
AND ICRE.organization_id = :org_id15 '; --Bug 2922902
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 ICRI.inventory_item_id = :item_id3
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.organization_id = ICRI.organization_id /*Bug 2922902*/
AND ICRE.related_item_id = ICRI.related_item_id )
AND MSIB.organization_id = :org_id4
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' ';
' 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 ICRI.inventory_item_id = :item_id2
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.organization_id = ICRI.organization_id /*Bug 2922902*/
AND ICRE.related_item_id = ICRI.related_item_id )
AND MSIB.organization_id = :org_id3
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' OR MSIB.web_status = ''UNPUBLISHED'') ';
' 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 ICRI.inventory_item_id = :item_id2
AND MSIB.organization_id = :org_id3
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' OR MSIB.web_status = ''UNPUBLISHED'') ';
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 MRI.inventory_item_id = :item_id6
AND MSIB.organization_id = :org_id7
AND MSIB.organization_id = MRI.organization_id --Bug 2922902
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'' OR MSIB.web_status = ''UNPUBLISHED'') ';
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 MRI.related_item_id = :item_id10
AND MSIB.organization_id = :org_id11
AND MSIB.organization_id = MRI.organization_id --Bug 2922902
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'' OR MSIB.web_status = ''UNPUBLISHED'')
AND MRI.reciprocal_flag = ''Y'' ';
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 ICRI.inventory_item_id = :item_id3
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.organization_id = ICRI.organization_id /*Bug 2922902*/
AND ICRE.related_item_id = ICRI.related_item_id )
AND MSIB.organization_id = :org_id4
AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
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'' OR MSIB.web_status = ''UNPUBLISHED'') ';
IBE_UTIL.debug('Inserting to ibe_temp_table.');
IBE_UTIL.INSERT_INTO_TEMP_TABLE(p_item_ids(i), 'NUM',l_temp_key, x_query_string);
BEGIN -- begin sub-block to handle the SELECT statement's exception
SELECT ICRR.sql_statement
INTO l_sql_stmt
FROM ibe_ct_relation_rules ICRR
WHERE ICRR.relation_type_code = p_rel_type_code
AND ICRR.origin_object_type = 'N'
AND ICRR.dest_object_type = 'N';
END; -- end sub-block to handle the SELECT statement's exception
SELECT COUNT(*) INTO l_dummy from ibe_temp_table;
l_status := IBE_UTIL.delete_from_temp_table(l_temp_key);