[Home] [Help]
2989:
2990: l_cursor := DBMS_SQL.OPEN_CURSOR;
2991: DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.NATIVE);
2992: DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_org_id', cs_std.get_item_valdn_orgzn_id);
2993: DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_prof_val', fnd_profile.value('CS_KB_PRODUCT_CATEGORY_SET'));
2994: DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_name', p_name);
2995: DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, x_number);
2996: ignore := DBMS_SQL.EXECUTE(l_cursor);
2997:
3027: and ic.category_set_id = cp_prof_val
3028: and upper(it.concatenated_segments) = upper(cp_segments) ;
3029: BEGIN
3030: open cur_segments(cs_std.get_item_valdn_orgzn_id,
3031: fnd_profile.value('CS_KB_PRODUCT_CATEGORY_SET'),
3032: p_segment);
3033: fetch cur_segments into x_number;
3034: if cur_segments%NOTFOUND then
3035: close cur_segments;
3071:
3072: l_cursor := DBMS_SQL.OPEN_CURSOR;
3073: DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.NATIVE);
3074: DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_org_id', cs_std.get_item_valdn_orgzn_id);
3075: DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_prof_val', fnd_profile.value('CS_SR_PLATFORM_CATEGORY_SET'));
3076: DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_name', p_name);
3077: DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, x_number);
3078: ignore := DBMS_SQL.EXECUTE(l_cursor);
3079:
3110: and upper(it.concatenated_segments) = upper(cp_segments) ;
3111:
3112: BEGIN
3113: open cur_segments(cs_std.get_item_valdn_orgzn_id,
3114: fnd_profile.value('CS_SR_PLATFORM_CATEGORY_SET'),
3115: p_segment);
3116: fetch cur_segments into x_number;
3117: if cur_segments%NOTFOUND then
3118: close cur_segments;
3434: p_commit IN VARCHAR2 := FND_API.G_FALSE)
3435: IS
3436: -- Default time usage to 1 year if the profile is not set.
3437: CURSOR Get_Time_Usage_Csr IS
3438: Select nvl(fnd_profile.value('CS_KB_USAGE_TIME_SPAN'), 365) from dual;
3439:
3440: -- Bug 32170161, replace sum with avg.
3441: CURSOR Get_Avg_Score_Csr(p_time_usge_span NUMBER) IS
3442: select avg(usage_score)/p_time_usge_span
3455: where status = 'PUB'
3456: and last_update_date > (sysdate - p_time_usage_span);
3457:
3458: CURSOR Get_Coefficient_Csr IS
3459: select fnd_profile.value('CS_KB_USAGE_LIMIT_FACTOR') from dual;
3460:
3461: -- (4740480)
3462: CURSOR Get_Set_Count(p_time_usage_span NUMBER) IS
3463: SELECT count(set_id)
3973: FROM mtl_system_items_vl it, mtl_item_categories ic, cs_kb_set_products a
3974: where it.inventory_item_id = ic.inventory_item_id
3975: and it.organization_id = ic.organization_id
3976: and it.organization_id = cs_std.get_item_valdn_orgzn_id
3977: and ic.category_set_id = fnd_profile.value('CS_KB_PRODUCT_CATEGORY_SET')
3978: and it.inventory_item_id = a.product_id
3979: and a.set_id = c_set_id;
3980:
3981: -- Fetch info for all platforms the solution links to
3984: FROM mtl_system_items_vl it, mtl_item_categories ic, cs_kb_set_platforms a
3985: where it.inventory_item_id = ic.inventory_item_id
3986: and it.organization_id = ic.organization_id
3987: and it.organization_id = cs_std.get_item_valdn_orgzn_id
3988: and ic.category_set_id = fnd_profile.value('CS_SR_PLATFORM_CATEGORY_SET')
3989: and it.inventory_item_id = a.platform_id
3990: and a.set_id = c_set_id;
3991:
3992: -- Fetch all category ids associated with this solution