1 PACKAGE CSM_QUERY_PKG AUTHID CURRENT_USER AS
2 /* $Header: csmqrys.pls 120.9.12020000.2 2013/04/09 11:02:07 saradhak ship $ */
3
4
5 /*
6 * The function to be called by Mobile Admin screen to insert/update/delete a query
7 */
8
9 -- Purpose: Insert/Delete/Update a Mobile Query
10 --
11 -- MODIFICATION HISTORY
12 -- Person Date Comments
13 -- TRAJASEK 12th April 2009 Created
14 --
15 -- --------- ------------------- ------------------------------------------
16 -- Enter package declarations as shown below
17 --Procedure to insert the new query
18 PROCEDURE INSERT_QUERY
19 ( p_QUERY_ID IN NUMBER,
20 p_QUERY_NAME IN VARCHAR2,
21 P_QUERY_DESC IN VARCHAR2,
22 P_QUERY_TYPE IN VARCHAR2,
23 p_QUERY_TEXT1 IN VARCHAR2,
24 p_QUERY_TEXT2 IN VARCHAR2,
25 p_LEVEL_ID IN NUMBER,
26 p_LEVEL_VALUE IN NUMBER,
27 p_PARENT_QUERY_ID IN NUMBER,
28 p_SAVED_QUERY IN VARCHAR2,
29 p_QUERY_OUTPUT_FORMAT IN VARCHAR2,
30 p_MIME_TYPE IN VARCHAR2,
31 p_WORK_FLOW IN VARCHAR2,
32 p_PROCEDURE IN VARCHAR2,
33 p_RETENTION_POLICY IN VARCHAR2,
34 p_RETENTION_DAYS IN NUMBER,
35 p_TEMPLATE IN VARCHAR2,
36 p_TEMPLATE_FILE IN VARCHAR2,
37 p_EXECUTION_MODE IN VARCHAR2,
38 p_VARIABLE_NAME IN CSM_VARCHAR_LIST,
39 p_VARIABLE_TYPE IN CSM_VARCHAR_LIST,
40 p_VARIABLE_VALUE_CHAR IN CSM_VARCHAR_LIST,
41 p_VARIABLE_VALUE_DATE IN CSM_DATE_LIST,
42 p_HIDDEN_FLAG IN CSM_VARCHAR_LIST,
43 p_DEFAULT_FLAG IN CSM_VARCHAR_LIST,
44 p_EMAIL_ENABLED IN VARCHAR2,
45 p_RESTRICTED_FLAG IN VARCHAR2,
46 p_DISABLED_FLAG IN VARCHAR2,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_error_message OUT NOCOPY VARCHAR2
49 );
50
51 --Procedure to update the existing query
52 PROCEDURE UPDATE_QUERY
53 ( p_QUERY_ID IN NUMBER,
54 p_QUERY_NAME IN VARCHAR2,
55 P_QUERY_DESC IN VARCHAR2,
56 P_QUERY_TYPE IN VARCHAR2,
57 p_QUERY_TEXT1 IN VARCHAR2,
58 p_QUERY_TEXT2 IN VARCHAR2,
59 p_LEVEL_ID IN NUMBER,
60 p_LEVEL_VALUE IN NUMBER,
61 p_PARENT_QUERY_ID IN NUMBER,
62 p_SAVED_QUERY IN VARCHAR2,
63 p_QUERY_OUTPUT_FORMAT IN VARCHAR2,
64 p_MIME_TYPE IN VARCHAR2,
65 p_WORK_FLOW IN VARCHAR2,
66 p_PROCEDURE IN VARCHAR2,
67 p_RETENTION_POLICY IN VARCHAR2,
68 p_RETENTION_DAYS IN NUMBER,
69 p_TEMPLATE IN VARCHAR2,
70 p_TEMPLATE_FILE IN VARCHAR2,
71 p_EXECUTION_MODE IN VARCHAR2,
72 p_VARIABLE_NAME IN CSM_VARCHAR_LIST,
73 p_VARIABLE_TYPE IN CSM_VARCHAR_LIST,
74 p_VARIABLE_VALUE_CHAR IN CSM_VARCHAR_LIST,
75 p_VARIABLE_VALUE_DATE IN CSM_DATE_LIST,
76 p_HIDDEN_FLAG IN CSM_VARCHAR_LIST,
77 p_DEFAULT_FLAG IN CSM_VARCHAR_LIST,
78 p_EMAIL_ENABLED IN VARCHAR2,
79 p_RESTRICTED_FLAG IN VARCHAR2,
80 p_DISABLED_FLAG IN VARCHAR2,
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_error_message OUT NOCOPY VARCHAR2
83 );
84
85
86 --Prodecure to delete a query that is wrongly added or that is no longer required
87 PROCEDURE DELETE_QUERY
88 ( p_QUERY_ID IN NUMBER,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_error_message OUT NOCOPY VARCHAR2
91 );
92
93 --Procedure to Validate a Given Query
94 PROCEDURE VALIDATE_QUERY
95 ( p_QUERY_ID IN NUMBER DEFAULT NULL,
96 p_QUERY_TEXT1 IN VARCHAR2 DEFAULT NULL,
97 p_QUERY_TEXT2 IN VARCHAR2 DEFAULT NULL,
98 x_return_status OUT NOCOPY VARCHAR2,
99 x_error_message OUT NOCOPY VARCHAR2
100 );
101
102 --Procedure to Execute a Given Query and store in the Result table
103 PROCEDURE EXECUTE_QUERY
104 ( p_USER_ID IN NUMBER,
105 p_QUERY_ID IN NUMBER,
106 p_INSTANCE_ID IN NUMBER,
107 x_return_status OUT NOCOPY VARCHAR2,
108 x_error_message OUT NOCOPY VARCHAR2,
109 p_commit IN VARCHAR2 DEFAULT fnd_api.G_TRUE,
110 p_source_module IN VARCHAR2 DEFAULT 'MOBILEADMIN'
111 );
112
113 --Procedure to Create a Instance for a Given Query and store in the Acc table
114
115 PROCEDURE INSERT_INSTANCE
116 ( p_USER_ID IN NUMBER,
117 p_QUERY_ID IN NUMBER,
118 p_INSTANCE_ID IN VARCHAR2 DEFAULT NULL,
119 p_INSTANCE_NAME IN VARCHAR2,
120 p_VARIABLE_ID IN CSM_INTEGER_LIST,
121 p_VARIABLE_VALUE_CHAR IN CSM_VARCHAR_LIST,
122 p_VARIABLE_VALUE_DATE IN CSM_DATE_LIST,
123 p_commit IN VARCHAR2 DEFAULT fnd_api.G_TRUE,
124 x_INSTANCE_ID OUT NOCOPY NUMBER,
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_error_message OUT NOCOPY VARCHAR2
127 );
128
129 --Procedure to Delete a Instance for a Given Query and store in the Acc table
130
131 PROCEDURE DELETE_INSTANCE
132 ( p_USER_ID IN NUMBER,
133 p_QUERY_ID IN NUMBER,
134 p_INSTANCE_ID IN NUMBER,
135 p_commit IN VARCHAR2 DEFAULT fnd_api.G_TRUE,
136 x_return_status OUT NOCOPY VARCHAR2,
137 x_error_message OUT NOCOPY VARCHAR2
138 );
139
140 --Procedure to Validate Query Access
141 PROCEDURE VALIDATE_ACCESS
142 ( p_QUERY_ID IN NUMBER DEFAULT NULL,
143 p_QUERY_TEXT1 IN VARCHAR2 DEFAULT NULL,
144 p_QUERY_TEXT2 IN VARCHAR2 DEFAULT NULL,
145 p_RESPONSIBILITY_ID IN NUMBER DEFAULT NULL,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_error_message OUT NOCOPY VARCHAR2
148 );
149
150 --Procedure to Validate Work Flow
151 PROCEDURE VALIDATE_WORKFLOW
152 ( p_QUERY_ID IN NUMBER DEFAULT NULL,
153 p_WORKFLOW IN VARCHAR2 DEFAULT NULL,
154 p_VARIABLE_NAME IN CSM_VARCHAR_LIST,
155 x_return_status OUT NOCOPY VARCHAR2,
156 x_error_message OUT NOCOPY VARCHAR2
157 );
158
159 --Procedure to Validate PL/SQL Procedure
160 PROCEDURE VALIDATE_PROCEDURE
161 ( p_QUERY_ID IN NUMBER DEFAULT NULL,
162 p_PROCEDURE IN VARCHAR2 DEFAULT NULL,
163 x_return_status OUT NOCOPY VARCHAR2,
164 x_error_message OUT NOCOPY VARCHAR2
165 );
166
167 --Procedure to Insert a Result once a Given Query is executed by Custom code
168
169 PROCEDURE INSERT_RESULT
170 ( p_USER_ID IN NUMBER,
171 p_QUERY_ID IN NUMBER,
172 p_INSTANCE_ID IN VARCHAR2 DEFAULT NULL,
173 p_QUERY_RESULT IN BLOB,
174 p_commit IN VARCHAR2 DEFAULT fnd_api.G_TRUE,
175 x_return_status OUT NOCOPY VARCHAR2,
176 x_error_message OUT NOCOPY VARCHAR2
177 );
178
179 -- Procedure to set validate procedure in insert/update_query.
180 -- This allows a MQ to be created even if the procedure is not valid.
181 PROCEDURE SET_VALIDATE_PROCEDURE(p_validate_procedure IN VARCHAR2);
182
183 G_INST_IN_PROCESS NUMBER :=NULL;
184
185 END CSM_QUERY_PKG; -- Package spec
[Home] [Help]