[Home] [Help]
2999:
3000: l_cursor := DBMS_SQL.OPEN_CURSOR;
3001: DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.NATIVE);
3002: DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_org_id', cs_std.get_item_valdn_orgzn_id);
3003: DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_prof_val', fnd_profile.value('CS_KB_PRODUCT_CATEGORY_SET'));
3004: DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_name', p_name);
3005: DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, x_number);
3006: ignore := DBMS_SQL.EXECUTE(l_cursor);
3007:
3037: and ic.category_set_id = cp_prof_val
3038: and upper(it.concatenated_segments) = upper(cp_segments) ;
3039: BEGIN
3040: open cur_segments(cs_std.get_item_valdn_orgzn_id,
3041: fnd_profile.value('CS_KB_PRODUCT_CATEGORY_SET'),
3042: p_segment);
3043: fetch cur_segments into x_number;
3044: if cur_segments%NOTFOUND then
3045: close cur_segments;
3081:
3082: l_cursor := DBMS_SQL.OPEN_CURSOR;
3083: DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.NATIVE);
3084: DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_org_id', cs_std.get_item_valdn_orgzn_id);
3085: DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_prof_val', fnd_profile.value('CS_SR_PLATFORM_CATEGORY_SET'));
3086: DBMS_SQL.BIND_VARIABLE(l_cursor, ':l_name', p_name);
3087: DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, x_number);
3088: ignore := DBMS_SQL.EXECUTE(l_cursor);
3089:
3120: and upper(it.concatenated_segments) = upper(cp_segments) ;
3121:
3122: BEGIN
3123: open cur_segments(cs_std.get_item_valdn_orgzn_id,
3124: fnd_profile.value('CS_SR_PLATFORM_CATEGORY_SET'),
3125: p_segment);
3126: fetch cur_segments into x_number;
3127: if cur_segments%NOTFOUND then
3128: close cur_segments;
3444: p_commit IN VARCHAR2 := FND_API.G_FALSE)
3445: IS
3446: -- Default time usage to 1 year if the profile is not set.
3447: CURSOR Get_Time_Usage_Csr IS
3448: Select nvl(fnd_profile.value('CS_KB_USAGE_TIME_SPAN'), 365) from dual;
3449:
3450: -- Bug 32170161, replace sum with avg.
3451: CURSOR Get_Avg_Score_Csr(p_time_usge_span NUMBER) IS
3452: select avg(usage_score)/p_time_usge_span
3465: where status = 'PUB'
3466: and last_update_date > (sysdate - p_time_usage_span);
3467:
3468: CURSOR Get_Coefficient_Csr IS
3469: select fnd_profile.value('CS_KB_USAGE_LIMIT_FACTOR') from dual;
3470:
3471: -- (4740480)
3472: CURSOR Get_Set_Count(p_time_usage_span NUMBER) IS
3473: SELECT count(set_id)
3983: FROM mtl_system_items_vl it, mtl_item_categories ic, cs_kb_set_products a
3984: where it.inventory_item_id = ic.inventory_item_id
3985: and it.organization_id = ic.organization_id
3986: and it.organization_id = cs_std.get_item_valdn_orgzn_id
3987: and ic.category_set_id = fnd_profile.value('CS_KB_PRODUCT_CATEGORY_SET')
3988: and it.inventory_item_id = a.product_id
3989: and a.set_id = c_set_id;
3990:
3991: -- Fetch info for all platforms the solution links to
3994: FROM mtl_system_items_vl it, mtl_item_categories ic, cs_kb_set_platforms a
3995: where it.inventory_item_id = ic.inventory_item_id
3996: and it.organization_id = ic.organization_id
3997: and it.organization_id = cs_std.get_item_valdn_orgzn_id
3998: and ic.category_set_id = fnd_profile.value('CS_SR_PLATFORM_CATEGORY_SET')
3999: and it.inventory_item_id = a.platform_id
4000: and a.set_id = c_set_id;
4001:
4002: -- Fetch all category ids associated with this solution