[Home] [Help]
2684: Cursor Check_Flow IS
2685: select flow_details_id
2686: from cs_kb_wf_flows_tl flow,
2687: cs_kb_wf_flow_details detail,
2688: cs_lookups lookup
2689: where flow.name = p_flow_name
2690: and flow.language = userenv('LANG')
2691: and flow.flow_id = detail.flow_id
2692: and detail.action = 'PUB'
2898: ) RETURN NUMBER IS
2899: BEGIN
2900:
2901: select lookup_code into x_access_level_value
2902: from cs_lookups where lookup_type = 'CS_KB_ACCESS_LEVEL'
2903: and upper(meaning) like upper(p_access_level_name);
2904:
2905: if (x_access_level_value IS NULL)
2906: then
2922: BEGIN
2923:
2924: select lookup_code
2925: INTO p_ele_content_type_code
2926: from cs_lookups
2927: where lookup_type = 'CS_KB_CONTENT_TYPE'
2928: and meaning = p_ele_content_type;
2929:
2930: IF ( (p_ele_content_type_code <> 'TEXT/HTML') AND
3509: sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
3510: +
3511: (l_avg_score * ( 1 - (sysdate - c.last_update_date)/l_time_usage) )
3512: )
3513: from cs_kb_set_used_hists a, cs_lookups cl
3514: where a.set_id = c.set_id
3515: and a.used_type = cl.lookup_code
3516: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3517: and a.creation_date >= (sysdate - l_time_usage)
3522: where c.status = 'PUB'
3523: and c.last_update_date > (sysdate-l_time_usage)
3524: and exists (
3525: select null
3526: from cs_kb_set_used_hists a, cs_lookups cl
3527: where a.set_id = c.set_id
3528: and a.used_type = cl.lookup_code
3529: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3530: and a.creation_date >= (sysdate - l_time_usage)
3541: sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
3542: +
3543: (l_avg_score * ( 1 - (sysdate - c.last_update_date)/l_time_usage) )
3544: ) + c.usage_score
3545: from cs_kb_set_links a, cs_lookups cl
3546: where a.set_id = c.set_id
3547: and a.link_type = cl.lookup_code
3548: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3549: and a.creation_date >= (sysdate - l_time_usage)
3554: where c.status = 'PUB'
3555: and c.last_update_date > (sysdate-l_time_usage)
3556: and exists (
3557: select null
3558: from cs_kb_set_links a, cs_lookups cl
3559: where a.set_id = c.set_id
3560: and a.link_type = cl.lookup_code
3561: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3562: and a.creation_date >= (sysdate - l_time_usage)
3570: update cs_kb_sets_b c set usage_score =
3571: (
3572: select round(sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
3573: )
3574: from cs_kb_set_used_hists a, cs_lookups cl
3575: where a.set_id = c.set_id
3576: and a.used_type = cl.lookup_code
3577: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3578: and a.creation_date >= (sysdate - l_time_usage)
3583: where c.status = 'PUB'
3584: and c.last_update_date <= (sysdate-l_time_usage)
3585: and exists (
3586: select null
3587: from cs_kb_set_used_hists a, cs_lookups cl
3588: where a.set_id = c.set_id
3589: and a.used_type = cl.lookup_code
3590: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3591: and a.creation_date >= (sysdate - l_time_usage)
3598: update cs_kb_sets_b c set usage_score =
3599: (
3600: select round(sum(to_number(cl.meaning)*(1-(sysdate-a.creation_date)/l_time_usage))
3601: ) + c.usage_score
3602: from cs_kb_set_links a, cs_lookups cl
3603: where a.set_id = c.set_id
3604: and a.link_type = cl.lookup_code
3605: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3606: and a.creation_date >= (sysdate - l_time_usage)
3611: where c.status = 'PUB'
3612: and c.last_update_date <= (sysdate-l_time_usage)
3613: and exists (
3614: select null
3615: from cs_kb_set_links a, cs_lookups cl
3616: where a.set_id = c.set_id
3617: and a.link_type = cl.lookup_code
3618: and cl.lookup_type = 'CS_KB_USAGE_TYPE_WEIGHT'
3619: and a.creation_date >= (sysdate - l_time_usage)
3960: CURSOR get_element_info(c_set_id IN NUMBER) IS
3961: select a.element_number, d.name, e.meaning,
3962: NVL(a.content_type, 'TEXT/HTML'), b.name, b.description, a.status
3963: from cs_kb_elements_b a , cs_kb_elements_tl b, cs_kb_set_eles c,
3964: cs_kb_element_types_vl d, cs_lookups e
3965: where a.element_id = c.element_id and c.set_id = c_set_id
3966: and a.element_id = b.element_id and b.language = userenv('LANG')
3967: and a.element_type_id = d.element_type_id
3968: and a.access_level = e.lookup_code and e.lookup_type = 'CS_KB_ACCESS_LEVEL';
4022: is TABLE OF CS_KB_SET_TYPES_TL.NAME%TYPE INDEX BY BINARY_INTEGER;
4023: Type set_name_tab_type
4024: is TABLE OF CS_KB_SETS_TL.NAME%TYPE INDEX BY BINARY_INTEGER;
4025: Type set_vis_tab_type
4026: is TABLE OF CS_LOOKUPS.MEANING%TYPE INDEX BY BINARY_INTEGER;
4027: Type set_status_tab_type
4028: is TABLE OF CS_KB_SETS_B.STATUS%TYPE INDEX BY BINARY_INTEGER;
4029: Type cat_id_tab_type
4030: is TABLE OF CS_KB_SOLN_CATEGORIES_VL.CATEGORY_ID%TYPE INDEX BY BINARY_INTEGER;
4045: is TABLE OF CS_KB_ELEMENTS_TL.NAME%TYPE INDEX BY BINARY_INTEGER;
4046: Type elmt_desc_tab_type
4047: is TABLE OF CS_KB_ELEMENTS_TL.DESCRIPTION%TYPE INDEX BY BINARY_INTEGER;
4048: Type elmt_dist_tab_type
4049: is TABLE OF CS_LOOKUPS.MEANING%TYPE INDEX BY BINARY_INTEGER;
4050: Type elmt_status_tab_type
4051: is TABLE OF CS_KB_ELEMENTS_B.STATUS%TYPE INDEX BY BINARY_INTEGER;
4052: Type elmt_ct_tab_type
4053: is TABLE OF CS_KB_ELEMENTS_B.CONTENT_TYPE%TYPE INDEX BY BINARY_INTEGER;
4364: -- Determine the Value for the content type from lookups
4365:
4366: select meaning
4367: INTO l_elmt_content_type
4368: from cs_lookups
4369: where lookup_type = 'CS_KB_CONTENT_TYPE'
4370: and lookup_code = l_elmt_cts(l_elmt_index);
4371:
4372: FND_FILE.PUT(FND_FILE.OUTPUT, ' ' || '