1 PACKAGE MSC_PQ_UTILS AUTHID CURRENT_USER as
2 /* $Header: MSCPQUTS.pls 120.4.12020000.2 2012/10/19 09:05:25 skathera ship $ */
3 TYPE among_values_rec IS RECORD(SEQUENCE NUMBER,
4 OBJECT_SEQUENCE NUMBER,
5 FIELD_NAME VARCHAR2(55),
6 OR_VALUES VARCHAR2(250),
7 HIDDEN_VALUES VARCHAR2(250));
8
9 TYPE among_values_tab IS TABLE OF among_values_rec INDEX BY BINARY_INTEGER;
10
11
12 FUNCTION build_where_clause(p_query_id IN NUMBER DEFAULT NULL,
13 P_source_type IN NUMBER DEFAULT NULL)
14 RETURN VARCHAR2;
15
16 FUNCTION build_order_where_clause(p_query_id IN NUMBER,
17 p_plan_id IN NUMBER)
18 RETURN VARCHAR2;
19
20 FUNCTION get_where_clause (sequence NUMBER,
21 obj_sequence NUMBER,
22 field_name IN OUT NOCOPY VARCHAR2,
23 operator NUMBER,
24 low VARCHAR2,
25 high VARCHAR2,
26 hidden_from VARCHAR2,
27 data_set IN OUT NOCOPY varchar2,
28 data_type IN OUT NOCOPY VARCHAR2,
29 lov_type IN NUMBER,
30 p_match_str IN VARCHAR2,
31 p_excp_where IN VARCHAR2)
32 RETURN VARCHAR2;
33
34 PROCEDURE retrieve_values (p_folder_id number);
35
36 FUNCTION get_among_where_clause (sequence NUMBER,
37 obj_sequence NUMBER,
38 t_operator VARCHAR2,
39 field_name IN OUT NOCOPY VARCHAR2,
40 operator NUMBER,
41 low VARCHAR2,
42 high VARCHAR2,
43 hidden_from VARCHAR2,
44 data_set IN OUT NOCOPY VARCHAR2,
45 datatype IN OUT NOCOPY VARCHAR2)
46 RETURN VARCHAR2;
47
48 PROCEDURE build_Excp_where(p_query_id IN NUMBER,
49 p_obj_sequence_id IN NUMBER,
50 p_sequence_id IN NUMBER,
51 p_plan_id IN NUMBER,
52 p_where_clause IN VARCHAR2,
53 p_excp_where_clause IN OUT NOCOPY VARCHAR2,
54 p_match_str IN VARCHAR2 DEFAULT ' AND ');
55
56 PROCEDURE store_values(p_sequence IN NUMBER,
57 p_obj_sequence IN NUMBER,
58 p_field_name IN VARCHAR2,
59 p_or_values IN VARCHAR2,
60 p_hidden_values IN VARCHAR2);
61
62 PROCEDURE clear_values;
63
64 PROCEDURE delete_rows(p_field_name in varchar2);
65
66 --PROCEDURE execute_pquery(p_plan_id IN NUMBER);
67
68 --FUNCTION execute_pquery(p_plan_id IN NUMBER) RETURN VARCHAR2;
69
70 PROCEDURE execute_one(p_plan_id IN NUMBER,
71 p_calledFromUI IN NUMBER,
72 p_partOfWorklist IN NUMBER,
73 p_query_id IN NUMBER,
74 p_query_type IN NUMBER,
75 p_execute_flag BOOLEAN DEFAULT TRUE,
76 p_master_query_id IN NUMBER DEFAULT NULL);
77
78 FUNCTION get_error RETURN VARCHAR2;
79
80 PROCEDURE execute_plan_queries(errbuf OUT NOCOPY VARCHAR2,
81 retcode OUT NOCOPY NUMBER,
82 p_plan_id IN NUMBER);
83
84 PROCEDURE execute_plan_worklists(errbuf OUT NOCOPY VARCHAR2,
85 retcode OUT NOCOPY NUMBER,
86 p_plan_id IN NUMBER);
87
88 PROCEDURE build_wrksh_query
89 (
90 p_plan_id IN NUMBER DEFAULT NULL,
91 p_query_id IN NUMBER DEFAULT NULL,
92 P_source_type IN NUMBER DEFAULT NULL,
93 p_select_clause OUT NOCOPY VARCHAR2,
94 p_from_clause OUT NOCOPY VARCHAR2,
95 p_where_clause OUT NOCOPY VARCHAR2,
96 P_column_prompts OUT NOCOPY VARCHAR2,
97 p_select_clause_dmd OUT NOCOPY VARCHAR2,
98 p_from_clause_dmd OUT NOCOPY VARCHAR2,
99 p_where_clause_dmd OUT NOCOPY VARCHAR2,
100 p_return_code OUT NOCOPY VARCHAR2 );
101
102 PROCEDURE Export_wsh
103 (
104 p_query_name IN VARCHAR2,
105 p_plan_id IN NUMBER,
106 p_query_id IN NUMBER,
107 p_file_id OUT NOCOPY NUMBER,
108 p_return_code OUT NOCOPY VARCHAR2
109 );
110
111 PROCEDURE execute_plan_worksheets(errbuf OUT NOCOPY VARCHAR2,
112 retcode OUT NOCOPY NUMBER,
113 p_plan_id IN NUMBER);
114
115 FUNCTION build_where_clause_wrksh(p_query_id IN NUMBER DEFAULT NULL,
116 P_source_type IN NUMBER DEFAULT NULL)
117 RETURN VARCHAR2;
118
119 FUNCTION get_where_clause_wrksh (sequence NUMBER,
120 obj_sequence NUMBER,
121 field_name IN OUT NOCOPY VARCHAR2,
122 operator NUMBER,
123 low VARCHAR2,
124 high VARCHAR2,
125 hidden_from VARCHAR2,
126 data_set IN OUT NOCOPY varchar2,
127 data_type IN OUT NOCOPY VARCHAR2,
128 lov_type IN NUMBER,
129 p_match_str IN VARCHAR2,
130 p_excp_where IN VARCHAR2)
131 RETURN VARCHAR2;
132
133
134 END MSC_PQ_UTILS;