PACKAGE BODY: APPS.MSC_SEARCH_TREE

Source

1 PACKAGE BODY MSC_SEARCH_TREE AS
2         /* $Header: MSCSRCHB.pls 120.1 2005/07/06 13:31:37 pabram noship $ */
3 
4 PROCEDURE query_results(p_query_id NUMBER, p_where VARCHAR2, p_type VARCHAR2) IS
5 
6   stmt_str	VARCHAR2(2000);
7 
8 BEGIN
9 
10   IF p_type = 'MSC_ORGANIZATIONS_TREE' THEN
11 
12     stmt_str := 'INSERT INTO msc_form_query ' ||
13 	'(query_id,last_update_date, last_updated_by, creation_date, '||
14 	'created_by, last_update_login, ' ||
15 	'number1, char1, number2, number3, char2, number4, char3, ' ||
16 	'number5, char4, number6, char5, number7, char6, ' ||
17 	'number8, char7, number9, char8, number10, char9) ' ||
18 	'SELECT distinct :query_id, sysdate, 1, sysdate, 1, 1, ' ||
19 	'search.plan_id, search.compile_designator, search.sr_instance_id, ' ||
20 	'search.organization_id, search.organization_code, '||
21 	'search.category_id, search.category_name, '||
22         'search.product_family_id, search.product_family_name, '||
23 	'search.inventory_item_id, search.item_name, search.component_id, '||
24 	'search.component_name, search.department_id, '||
25 	'search.department_code, search.resource_id, search.resource_code, '||
26 	'search.line_id, search.line_code ' ||
27 	'FROM msc_search_orgs_v search ' ||
28 	'WHERE 1=1 '|| p_where;
29 
30   ELSIF p_type = 'MSC_ITEMS_TREE' THEN
31 
32     stmt_str := 'INSERT INTO msc_form_query ' ||
33 	'(query_id,last_update_date, last_updated_by, creation_date, '||
34 	'created_by, last_update_login, ' ||
35 	'number1, char1, number2, number3, char2, number4, char3, ' ||
36 	'number5, number6, char4, number7, char5, number8, char6, ' ||
37 	'number9, char7, number10, char8, number11, char9) ' ||
38 	'SELECT :query_id, sysdate, 1, sysdate, 1, 1, ' ||
39 	'plan_id, compile_designator, sr_instance_id, organization_id, '||
40 	'organization_code, product_family_id, product_family_name, '||
41 	'category_set_id, category_id, category_name, inventory_item_id, ' ||
42 	'item_name, component_id, component_name, department_id, '||
43 	'department_code, resource_id, resource_code, line_id, line_code ' ||
44 	'FROM msc_search_items_v search ' ||
45 	'WHERE 1=1 ' || p_where;
46 
47   ELSIF p_type = 'MSC_RESOURCES_TREE' THEN
48 
49     stmt_str := 'INSERT INTO msc_form_query ' ||
50 	'(query_id,last_update_date, last_updated_by, creation_date, '||
51 	'created_by, last_update_login, ' ||
52 	'number1, char1, number2, number3, char2, char3, ' ||
53 	'char4, number4, char5, ' ||
54 	'number5, char6, number6, char7, number7, char8) ' ||
55 	'SELECT :query_id, sysdate, 1, sysdate, 1, 1, ' ||
56 	'plan_id, compile_designator, sr_instance_id, organization_id, '||
57 	'organization_code, department_class, resource_group, '||
58 	'inventory_item_id, item_name, department_id, '||
59 	'department_code, resource_id, resource_code, line_id, line_code ' ||
60 	'FROM msc_search_resources_v search ' ||
61 	'WHERE 1=1 ' || p_where;
62 
63   ELSIF p_type = 'MSC_PROJECTS_TREE' THEN
64 
65     stmt_str := 'INSERT INTO msc_form_query ' ||
66 	'(query_id,last_update_date, last_updated_by, creation_date, '||
67 	'created_by, last_update_login, ' ||
68 	'number1, char1, number2, number3, char2, char3, ' ||
69 	'number4, char4, number5, char5, number6, char6) ' ||
70 	'SELECT :query_id, sysdate, 1, sysdate, 1, 1, ' ||
71 	'plan_id, compile_designator, sr_instance_id, organization_id, '||
72 	'organization_code, planning_group, '||
73 	'inventory_item_id, item_name, project_id, '||
74 	'project_name, task_id, task_name ' ||
75 	'FROM msc_search_projects_v search ' ||
76 	'WHERE 1=1 ' || p_where;
77 
78   ELSIF p_type = 'MSC_ACTIONS_TREE' THEN
79 
80     stmt_str := 'INSERT INTO msc_form_query ' ||
81 	'(query_id,last_update_date, last_updated_by, creation_date, '||
82 	'created_by, last_update_login, ' ||
83 	'number1, char1, number2, number3, char2, char3, ' ||
84 	'number4, char4, number5, char5) ' ||
85 	'SELECT :query_id, sysdate, 1, sysdate, 1, 1, ' ||
86 	'plan_id, compile_designator, sr_instance_id, organization_id, '||
87 	'organization_code, version, exception_type, exception_type_text, '||
88 	'inventory_item_id, item_name '||
89 	'FROM msc_search_actions_v search ' ||
90 	'WHERE 1=1 ' || p_where;
91 
92   END IF;
93 
94   EXECUTE IMMEDIATE stmt_str
95 	USING p_query_id;
96 
97 END query_results;
98 
99 END MSC_SEARCH_TREE;