[Home] [Help]
2694: Cursor Check_Flow IS
2695: select flow_details_id
2696: from cs_kb_wf_flows_tl flow,
2697: cs_kb_wf_flow_details detail,
2698: cs_lookups lookup
2699: where flow.name = p_flow_name
2700: and flow.language = userenv('LANG')
2701: and flow.flow_id = detail.flow_id
2702: and detail.action = 'PUB'
2908: ) RETURN NUMBER IS
2909: BEGIN
2910:
2911: select lookup_code into x_access_level_value
2912: from cs_lookups where lookup_type = 'CS_KB_ACCESS_LEVEL'
2913: and upper(meaning) like upper(p_access_level_name);
2914:
2915: if (x_access_level_value IS NULL)
2916: then
2932: BEGIN
2933:
2934: select lookup_code
2935: INTO p_ele_content_type_code
2936: from cs_lookups
2937: where lookup_type = 'CS_KB_CONTENT_TYPE'
2938: and meaning = p_ele_content_type;
2939:
2940: IF ( (p_ele_content_type_code <> 'TEXT/HTML') AND
3519: sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
3520: +
3521: (l_avg_score * ( 1 - (sysdate - c.last_update_date)/l_time_usage) )
3522: )
3523: from cs_kb_set_used_hists a, cs_lookups cl
3524: where a.set_id = c.set_id
3525: and a.used_type = cl.lookup_code
3526: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3527: and a.creation_date >= (sysdate - l_time_usage)
3532: where c.status = 'PUB'
3533: and c.last_update_date > (sysdate-l_time_usage)
3534: and exists (
3535: select null
3536: from cs_kb_set_used_hists a, cs_lookups cl
3537: where a.set_id = c.set_id
3538: and a.used_type = cl.lookup_code
3539: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3540: and a.creation_date >= (sysdate - l_time_usage)
3551: sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
3552: +
3553: (l_avg_score * ( 1 - (sysdate - c.last_update_date)/l_time_usage) )
3554: ) + c.usage_score
3555: from cs_kb_set_links a, cs_lookups cl
3556: where a.set_id = c.set_id
3557: and a.link_type = cl.lookup_code
3558: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3559: and a.creation_date >= (sysdate - l_time_usage)
3564: where c.status = 'PUB'
3565: and c.last_update_date > (sysdate-l_time_usage)
3566: and exists (
3567: select null
3568: from cs_kb_set_links a, cs_lookups cl
3569: where a.set_id = c.set_id
3570: and a.link_type = cl.lookup_code
3571: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3572: and a.creation_date >= (sysdate - l_time_usage)
3580: update cs_kb_sets_b c set usage_score =
3581: (
3582: select round(sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
3583: )
3584: from cs_kb_set_used_hists a, cs_lookups cl
3585: where a.set_id = c.set_id
3586: and a.used_type = cl.lookup_code
3587: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3588: and a.creation_date >= (sysdate - l_time_usage)
3593: where c.status = 'PUB'
3594: and c.last_update_date <= (sysdate-l_time_usage)
3595: and exists (
3596: select null
3597: from cs_kb_set_used_hists a, cs_lookups cl
3598: where a.set_id = c.set_id
3599: and a.used_type = cl.lookup_code
3600: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3601: and a.creation_date >= (sysdate - l_time_usage)
3608: update cs_kb_sets_b c set usage_score =
3609: (
3610: select round(sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
3611: ) + c.usage_score
3612: from cs_kb_set_links a, cs_lookups cl
3613: where a.set_id = c.set_id
3614: and a.link_type = cl.lookup_code
3615: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3616: and a.creation_date >= (sysdate - l_time_usage)
3621: where c.status = 'PUB'
3622: and c.last_update_date <= (sysdate-l_time_usage)
3623: and exists (
3624: select null
3625: from cs_kb_set_links a, cs_lookups cl
3626: where a.set_id = c.set_id
3627: and a.link_type = cl.lookup_code
3628: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3629: and a.creation_date >= (sysdate - l_time_usage)
3970: CURSOR get_element_info(c_set_id IN NUMBER) IS
3971: select a.element_number, d.name, e.meaning,
3972: NVL(a.content_type, 'TEXT/HTML'), b.name, b.description, a.status
3973: from cs_kb_elements_b a , cs_kb_elements_tl b, cs_kb_set_eles c,
3974: cs_kb_element_types_vl d, cs_lookups e
3975: where a.element_id = c.element_id and c.set_id = c_set_id
3976: and a.element_id = b.element_id and b.language = userenv('LANG')
3977: and a.element_type_id = d.element_type_id
3978: and a.access_level = e.lookup_code and e.lookup_type = 'CS_KB_ACCESS_LEVEL';
4032: is TABLE OF CS_KB_SET_TYPES_TL.NAME%TYPE INDEX BY BINARY_INTEGER;
4033: Type set_name_tab_type
4034: is TABLE OF CS_KB_SETS_TL.NAME%TYPE INDEX BY BINARY_INTEGER;
4035: Type set_vis_tab_type
4036: is TABLE OF CS_LOOKUPS.MEANING%TYPE INDEX BY BINARY_INTEGER;
4037: Type set_status_tab_type
4038: is TABLE OF CS_KB_SETS_B.STATUS%TYPE INDEX BY BINARY_INTEGER;
4039: Type cat_id_tab_type
4040: is TABLE OF CS_KB_SOLN_CATEGORIES_VL.CATEGORY_ID%TYPE INDEX BY BINARY_INTEGER;
4055: is TABLE OF CS_KB_ELEMENTS_TL.NAME%TYPE INDEX BY BINARY_INTEGER;
4056: Type elmt_desc_tab_type
4057: is TABLE OF CS_KB_ELEMENTS_TL.DESCRIPTION%TYPE INDEX BY BINARY_INTEGER;
4058: Type elmt_dist_tab_type
4059: is TABLE OF CS_LOOKUPS.MEANING%TYPE INDEX BY BINARY_INTEGER;
4060: Type elmt_status_tab_type
4061: is TABLE OF CS_KB_ELEMENTS_B.STATUS%TYPE INDEX BY BINARY_INTEGER;
4062: Type elmt_ct_tab_type
4063: is TABLE OF CS_KB_ELEMENTS_B.CONTENT_TYPE%TYPE INDEX BY BINARY_INTEGER;
4374: -- Determine the Value for the content type from lookups
4375:
4376: select meaning
4377: INTO l_elmt_content_type
4378: from cs_lookups
4379: where lookup_type = 'CS_KB_CONTENT_TYPE'
4380: and lookup_code = l_elmt_cts(l_elmt_index);
4381:
4382: FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '