[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT s.order_by_clause
FROM IBE_DSP_SECTIONS_VL s
WHERE s.section_id = p_sectid;
'SELECT si.inventory_item_id, si.organization_id, si.usage_name, MSIB.web_status, ' ||
' si.start_date_active assoc_start_dt, si.end_date_active assoc_end_date, ' ||
' MSIB.start_date_active start_dt, MSIB.end_date_active end_dt ' ||
'FROM IBE_DSP_SECTION_ITEMS si, MTL_SYSTEM_ITEMS_B MSIB ' ||
'WHERE si.section_id = :sect_id ' ||
'AND si.inventory_item_id = MSIB.inventory_item_id ' ||
'AND si.organization_id = MSIB.organization_id ' ||
'AND (MSIB.WEB_STATUS = ''PUBLISHED'' OR MSIB.WEB_STATUS = ''UNPUBLISHED'') ' ||
'AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE ' ||
'AND NVL(si.end_date_active, SYSDATE) >= SYSDATE ';
'SELECT si.inventory_item_id, si.organization_id, si.usage_name, MSIV.web_status, ' ||
' si.start_date_active assoc_start_dt, si.end_date_active assoc_end_dt, ' ||
' MSIV.start_date_active start_dt, MSIV.end_date_active end_dt ' ||
'FROM IBE_DSP_SECTION_ITEMS si, MTL_SYSTEM_ITEMS_VL MSIV ' ||
'WHERE si.section_id = :sect_id ' ||
'AND si.inventory_item_id = MSIV.inventory_item_id ' ||
'AND si.organization_id = MSIV.organization_id ' ||
'AND (MSIV.WEB_STATUS = ''PUBLISHED'' OR MSIV.WEB_STATUS = ''UNPUBLISHED'') ' ||
'AND NVL(MSIV.end_date_active, SYSDATE) >= SYSDATE ' ||
'AND NVL(si.end_date_active, SYSDATE) >= SYSDATE ';
l_stmt := 'SELECT * FROM (' || L_MSIB_STMT || 'ORDER BY ' ||
L_SECT_MSIB_ORDER_STMT || ') WHERE rownum <= :itms_per_sct';
l_stmt := 'SELECT * FROM (' || L_MSIV_STMT || 'ORDER BY ' ||
l_ord_by_clause || ', ' || L_SECT_MSIV_ORDER_STMT ||
') WHERE rownum <= :itms_per_sct';
SELECT mss.child_section_id, mss.sort_order
FROM IBE_DSP_MSITE_SCT_SECTS mss, IBE_DSP_SECTIONS_B jdsb
WHERE mss.mini_site_id = p_msite_id
AND mss.child_section_id in
( SELECT mss1.child_section_id
FROM IBE_DSP_MSITE_SCT_SECTS mss1
START WITH mss1.parent_section_id = p_sectid and mss1.mini_site_id = p_msite_id
CONNECT BY PRIOR mss1.child_section_id = mss1.parent_section_id
AND mss1.mini_site_id = p_msite_id
)
AND mss.child_section_id not in
(
SELECT mss2.parent_section_id
FROM IBE_DSP_MSITE_SCT_SECTS mss2
WHERE mss2.mini_site_id = p_msite_id
AND mss2.parent_section_id is not null
)
AND jdsb.section_id = mss.child_section_id
AND (jdsb.status_code = 'PUBLISHED' OR jdsb.status_code = 'UNPUBLISHED')
AND NVL(jdsb.start_date_active, SYSDATE) <= SYSDATE
AND NVL(jdsb.end_date_active, SYSDATE) >= SYSDATE
ORDER BY mss.sort_order;
SELECT mss.child_section_id, mss.sort_order
FROM IBE_DSP_MSITE_SCT_SECTS mss, IBE_DSP_SECTIONS_B jdsb
WHERE mss.mini_site_id = p_msite_id
AND mss.child_section_id in
( SELECT mss1.child_section_id
FROM IBE_DSP_MSITE_SCT_SECTS mss1
START WITH mss1.parent_section_id = p_sectid and mss1.mini_site_id = p_msite_id
CONNECT BY PRIOR mss1.child_section_id = mss1.parent_section_id
AND mss1.mini_site_id = p_msite_id
)
AND mss.child_section_id not in
(
SELECT mss2.parent_section_id
FROM IBE_DSP_MSITE_SCT_SECTS mss2
WHERE mss2.mini_site_id = p_msite_id
AND mss2.parent_section_id is not null
)
AND jdsb.section_id = mss.child_section_id
AND jdsb.status_code = 'PUBLISHED'
AND NVL(jdsb.start_date_active, SYSDATE) <= SYSDATE
AND NVL(jdsb.end_date_active, SYSDATE) >= SYSDATE
ORDER BY mss.sort_order;
SELECT mss.parent_section_id
FROM IBE_DSP_MSITE_SCT_SECTS mss
START WITH mss.child_section_id = p_sectid AND mss.mini_site_id = p_msite_id
CONNECT BY PRIOR mss.parent_section_id = mss.child_section_id
AND mss.mini_site_id = p_msite_id;
SELECT start_date_active, end_date_active
FROM FND_LOOKUPS
WHERE lookup_type = 'IBE_RELATIONSHIP_TYPES'
AND lookup_code = p_reltype_code
AND enabled_flag = 'Y';
l_relobj_stmt := 'SELECT rr.DEST_OBJECT_ID ' ||
'FROM IBE_CT_RELATION_RULES rr, IBE_DSP_SECTIONS_B jdsb ' ||
'WHERE rr.ORIGIN_OBJECT_TYPE = :p_origintype ' ||
'AND rr.ORIGIN_OBJECT_ID = :p_originid ' ||
'AND rr.RELATION_TYPE_CODE = :p_reltype_code ' ||
'AND rr.DEST_OBJECT_TYPE = :p_dest_type ' ||
'AND rr.DEST_OBJECT_ID = jdsb.SECTION_ID ' ||
'AND (jdsb.status_code = ''PUBLISHED'' OR jdsb.status_code = ''UNPUBLISHED'') ' ||
'AND NVL(jdsb.start_date_active, SYSDATE) <= SYSDATE ' ||
'AND NVL(jdsb.end_date_active, SYSDATE) >= SYSDATE ';
l_relobj_stmt := 'SELECT rr.DEST_OBJECT_ID ' ||
'FROM IBE_CT_RELATION_RULES rr, IBE_DSP_SECTIONS_B jdsb ' ||
'WHERE rr.ORIGIN_OBJECT_TYPE = :p_origintype ' ||
'AND rr.ORIGIN_OBJECT_ID = :p_originid ' ||
'AND rr.RELATION_TYPE_CODE = :p_reltype_code ' ||
'AND rr.DEST_OBJECT_TYPE = :p_dest_type ' ||
'AND rr.DEST_OBJECT_ID = jdsb.SECTION_ID ' ||
'AND jdsb.status_code = ''PUBLISHED'' ' ||
'AND NVL(jdsb.start_date_active, SYSDATE) <= SYSDATE ' ||
'AND NVL(jdsb.end_date_active, SYSDATE) >= SYSDATE ';
l_relobj_stmt := 'SELECT rr.DEST_OBJECT_ID ' ||
'FROM IBE_CT_RELATION_RULES rr ' ||
'WHERE rr.ORIGIN_OBJECT_TYPE = :p_origintype ' ||
'AND rr.ORIGIN_OBJECT_ID = :p_originid ' ||
'AND rr.RELATION_TYPE_CODE = :p_reltype_code ' ||
'AND rr.DEST_OBJECT_TYPE = :p_dest_type ';
'SELECT MSIV.CONFIG_MODEL_TYPE, MSIV.BOM_ENABLED_FLAG, MSIV.ORDERABLE_ON_WEB_FLAG, MSIV.BACK_ORDERABLE_FLAG, ' ||
'MSIV.PRIMARY_UNIT_OF_MEASURE, MSIV.PRIMARY_UOM_CODE, ' ||
'MSIV.ITEM_TYPE, MSIV.BOM_ITEM_TYPE, ' ||
'MSIV.INDIVISIBLE_FLAG, MSIV.SERIAL_NUMBER_CONTROL_CODE, MSIV.WEB_STATUS, ' ||
'MSIV.CONCATENATED_SEGMENTS, MSIV.INVENTORY_ITEM_ID, '||
--gzhang 10/24/2002, ER#2474216
'MSIV.SERVICE_ITEM_FLAG,MSIV.SERVICEABLE_PRODUCT_FLAG,MSIV.SERVICE_DURATION_PERIOD_CODE,MSIV.SERVICE_DURATION, '||
'MSIV.SHIPPABLE_ITEM_FLAG,MSIV.INVOICEABLE_ITEM_FLAG,MSIV.INVOICE_ENABLED_FLAG, MSIV.START_DATE_ACTIVE, MSIV.END_DATE_ACTIVE '||
'FROM MTL_SYSTEM_ITEMS_VL MSIV ';
'SELECT MSIV.CONFIG_MODEL_TYPE, MSIV.BOM_ENABLED_FLAG, MSIV.ORDERABLE_ON_WEB_FLAG, MSIV.BACK_ORDERABLE_FLAG, ' ||
'MSIV.PRIMARY_UNIT_OF_MEASURE, MSIV.PRIMARY_UOM_CODE, ' ||
'MSIV.ITEM_TYPE, MSIV.BOM_ITEM_TYPE, ' ||
'MSIV.INDIVISIBLE_FLAG, MSIV.SERIAL_NUMBER_CONTROL_CODE, MSIV.WEB_STATUS, ' ||
'MSIV.CONCATENATED_SEGMENTS, MSIV.INVENTORY_ITEM_ID, '||
--gzhang 10/24/2002, ER#2474216
'MSIV.SERVICE_ITEM_FLAG,MSIV.SERVICEABLE_PRODUCT_FLAG,MSIV.SERVICE_DURATION_PERIOD_CODE,MSIV.SERVICE_DURATION, '||
'MSIV.SHIPPABLE_ITEM_FLAG,MSIV.INVOICEABLE_ITEM_FLAG,MSIV.INVOICE_ENABLED_FLAG, MSIV.TAXABLE_FLAG, MSIV.ATP_FLAG, MSIV.RETURNABLE_FLAG, ' ||
'MSIV.DOWNLOADABLE_FLAG, MSIV.MINIMUM_ORDER_QUANTITY, ' ||
'MSIV.MAXIMUM_ORDER_QUANTITY, MSIV.FIXED_ORDER_QUANTITY, ' ||
'MSIV.SERVICE_STARTING_DELAY, MSIV.SEGMENT1, MSIV.SEGMENT2, MSIV.SEGMENT3, MSIV.SEGMENT4, ' ||
'MSIV.SEGMENT5, MSIV.SEGMENT6, MSIV.SEGMENT7, MSIV.SEGMENT8, MSIV.SEGMENT9, MSIV.SEGMENT10, ' ||
'MSIV.SEGMENT11, MSIV.SEGMENT12, MSIV.SEGMENT13, MSIV.SEGMENT14, MSIV.SEGMENT15, MSIV.SEGMENT16, ' ||
'MSIV.SEGMENT17, MSIV.SEGMENT18, MSIV.SEGMENT19, MSIV.SEGMENT20, MSIV.ATTRIBUTE1, ' ||
'MSIV.ATTRIBUTE2, MSIV.ATTRIBUTE3, MSIV.ATTRIBUTE4, MSIV.ATTRIBUTE5, MSIV.ATTRIBUTE6, ' ||
'MSIV.ATTRIBUTE7, MSIV.ATTRIBUTE8, MSIV.ATTRIBUTE9, MSIV.ATTRIBUTE10, MSIV.ATTRIBUTE11, ' ||
'MSIV.ATTRIBUTE12, MSIV.ATTRIBUTE13, MSIV.ATTRIBUTE14, MSIV.ATTRIBUTE15, MSIV.ATTRIBUTE_CATEGORY, ' ||
'MSIV.COUPON_EXEMPT_FLAG, MSIV.VOL_DISCOUNT_EXEMPT_FLAG, MSIV.ELECTRONIC_FLAG, ' ||
'MSIV.GLOBAL_ATTRIBUTE_CATEGORY, ' ||
'MSIV.GLOBAL_ATTRIBUTE1, MSIV.GLOBAL_ATTRIBUTE2, MSIV.GLOBAL_ATTRIBUTE3, MSIV.GLOBAL_ATTRIBUTE4, ' ||
'MSIV.GLOBAL_ATTRIBUTE5, MSIV.GLOBAL_ATTRIBUTE6, MSIV.GLOBAL_ATTRIBUTE7, MSIV.GLOBAL_ATTRIBUTE8, ' ||
'MSIV.GLOBAL_ATTRIBUTE9, MSIV.GLOBAL_ATTRIBUTE10 FROM MTL_SYSTEM_ITEMS_VL MSIV ';
'SELECT MSIV.TAXABLE_FLAG, MSIV.ATP_FLAG, MSIV.RETURNABLE_FLAG, ' ||
'MSIV.DOWNLOADABLE_FLAG, MSIV.MINIMUM_ORDER_QUANTITY, MSIV.MAXIMUM_ORDER_QUANTITY, MSIV.FIXED_ORDER_QUANTITY, ' ||
'MSIV.SERVICE_STARTING_DELAY, MSIV.SEGMENT1, MSIV.SEGMENT2, MSIV.SEGMENT3, MSIV.SEGMENT4, ' ||
'MSIV.SEGMENT5, MSIV.SEGMENT6, MSIV.SEGMENT7, MSIV.SEGMENT8, MSIV.SEGMENT9, MSIV.SEGMENT10, ' ||
'MSIV.SEGMENT11, MSIV.SEGMENT12, MSIV.SEGMENT13, MSIV.SEGMENT14, MSIV.SEGMENT15, MSIV.SEGMENT16, ' ||
'MSIV.SEGMENT17, MSIV.SEGMENT18, MSIV.SEGMENT19, MSIV.SEGMENT20, MSIV.ATTRIBUTE1, ' ||
'MSIV.ATTRIBUTE2, MSIV.ATTRIBUTE3, MSIV.ATTRIBUTE4, MSIV.ATTRIBUTE5, MSIV.ATTRIBUTE6, ' ||
'MSIV.ATTRIBUTE7, MSIV.ATTRIBUTE8, MSIV.ATTRIBUTE9, MSIV.ATTRIBUTE10, MSIV.ATTRIBUTE11, ' ||
'MSIV.ATTRIBUTE12, MSIV.ATTRIBUTE13, MSIV.ATTRIBUTE14, MSIV.ATTRIBUTE15, MSIV.ATTRIBUTE_CATEGORY, ' ||
'MSIV.COUPON_EXEMPT_FLAG, MSIV.VOL_DISCOUNT_EXEMPT_FLAG, MSIV.ELECTRONIC_FLAG, ' ||
'MSIV.GLOBAL_ATTRIBUTE_CATEGORY, ' ||
'MSIV.GLOBAL_ATTRIBUTE1, MSIV.GLOBAL_ATTRIBUTE2, MSIV.GLOBAL_ATTRIBUTE3, MSIV.GLOBAL_ATTRIBUTE4, ' ||
'MSIV.GLOBAL_ATTRIBUTE5, MSIV.GLOBAL_ATTRIBUTE6, MSIV.GLOBAL_ATTRIBUTE7, MSIV.GLOBAL_ATTRIBUTE8, ' ||
'MSIV.GLOBAL_ATTRIBUTE9, MSIV.GLOBAL_ATTRIBUTE10, MSIV.CONCATENATED_SEGMENTS, ' ||
'MSIV.INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_VL MSIV ';
SELECT fc.symbol FROM FND_CURRENCIES fc
WHERE fc.currency_code = l_currency_code;
SELECT count(s.inventory_item_id)
INTO l_item_exists
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_tbl(i)
AND (s.end_date_active > sysdate or s.end_date_active is null )
AND s.start_date_active < sysdate;
SELECT language_code, language, description
FROM fnd_languages_tl t
WHERE EXISTS (SELECT NULL FROM ibe_msite_languages m WHERE m.language_code = t.language_code)
ORDER BY language_code;
SELECT t.language, t.description
FROM fnd_languages_tl t
WHERE language_code = l_lang_code;
SELECT nls_language
FROM fnd_languages
WHERE language_code = l_lang_code;
select MSIV.inventory_item_id
from mtl_system_items_vl MSIV
where MSIV.concatenated_segments = p_item_partnum;
-- get the select and from clauses of the query
Get_Basic_Item_Load_Query(p_load_level, l_itm_stmt);
SELECT MSIV.INVENTORY_ITEM_ID, mic.CATEGORY_ID
FROM MTL_SYSTEM_ITEMS_VL MSIV, MTL_ITEM_CATEGORIES mic
WHERE MSIV.INVENTORY_ITEM_ID = l_itmid
AND MSIV.ORGANIZATION_ID = p_organization_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 = p_category_set_id;
SELECT miuv.INVENTORY_ITEM_ID, miuv.UOM_CODE
FROM MTL_ITEM_UOMS_VIEW miuv
WHERE miuv.INVENTORY_ITEM_ID = l_itmid
AND miuv.ORGANIZATION_ID = p_organization_id
ORDER BY miuv.UOM_CODE;
SELECT miuv.INVENTORY_ITEM_ID, miuv.UOM_CODE
FROM MTL_ITEM_UOMS_VIEW miuv
WHERE miuv.INVENTORY_ITEM_ID = p_itmid
AND miuv.ORGANIZATION_ID = p_organization_id
ORDER BY miuv.UOM_CODE;
SELECT MSIV.primary_uom_code
FROM mtl_system_items_vl MSIV
WHERE MSIV.inventory_item_id = p_itmid;
select MSIV.inventory_item_id
from mtl_system_items_vl MSIV
where MSIV.concatenated_segments = l_partnum;
select MIC.category_id
FROM MTL_SYSTEM_ITEMS_B MSIB, MTL_ITEM_CATEGORIES MIC
WHERE MSIB.inventory_item_id = l_itmid
AND MSIB.organization_id = l_organization_id
AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
AND MSIB.inventory_item_id = MIC.inventory_item_id
AND MSIB.organization_id = MIC.organization_id
AND mic.CATEGORY_SET_ID = l_category_set_id;
-- get the select and from clauses of the query
l_itmid_tbl := JTF_NUMBER_TABLE();
SELECT MSIV.primary_uom_code
FROM mtl_system_items_vl MSIV
WHERE MSIV.inventory_item_id = l_item_id;
SELECT miuv.UOM_CODE
FROM MTL_ITEM_UOMS_VIEW miuv
WHERE miuv.INVENTORY_ITEM_ID = l_item_id
AND miuv.ORGANIZATION_ID = p_organization_id
ORDER BY miuv.UOM_CODE;
select MSIV.SERVICE_ITEM_FLAG,MSIV.SERVICEABLE_PRODUCT_FLAG
from MTL_SYSTEM_ITEMS_VL MSIV
where MSIV.INVENTORY_ITEM_ID IN (select NUM_VAL from IBE_TEMP_TABLE where key =
l_temp_key ) and MSIV.ORGANIZATION_ID = l_org_id;
IBE_UTIL.INSERT_INTO_TEMP_TABLE(p_item_ids(i), 'NUM',l_temp_key, x_query_string);
l_status := IBE_UTIL.delete_from_temp_table(l_temp_key);
select b.inventory_item_id
from ibe_dsp_msite_sct_items a, ibe_dsp_section_items b
where a.section_item_id = b.section_item_id
and a.mini_site_id = l_minisite_id and b.inventory_item_id IN
(select NUM_VAL from IBE_TEMP_TABLE where key = l_temp_key );
IBE_UTIL.INSERT_INTO_TEMP_TABLE(p_item_ids(i), 'NUM',l_temp_key, x_query_string);
l_status := IBE_UTIL.delete_from_temp_table(l_temp_key);