1 PACKAGE CSM_QUERY_PUB AUTHID CURRENT_USER AS
2 /* $Header: csmqryps.pls 120.3 2011/01/06 09:15:44 ravir noship $ */
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 27th July 2009 Created
14 --
15 -- --------- ------------------- ------------------------------------------
16 -- Enter package declarations as shown below
17 --Procedure to insert the new query
18 PROCEDURE INSERT_QUERY
19 (
20 p_API_VERSION IN NUMBER,
21 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
22 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
23 p_QUERY_ID IN NUMBER,
24 p_QUERY_NAME IN VARCHAR2,
25 P_QUERY_DESC IN VARCHAR2,
26 P_QUERY_TYPE IN VARCHAR2,
27 p_QUERY_TEXT1 IN VARCHAR2,
28 p_QUERY_TEXT2 IN VARCHAR2,
29 p_LEVEL_ID IN NUMBER,
30 p_LEVEL_VALUE IN NUMBER,
31 p_PARENT_QUERY_ID IN NUMBER,
32 p_SAVED_QUERY IN VARCHAR2,
33 p_QUERY_OUTPUT_FORMAT IN VARCHAR2,
34 p_MIME_TYPE IN VARCHAR2,
35 p_WORK_FLOW IN VARCHAR2,
36 p_PROCEDURE IN VARCHAR2,
37 p_RETENTION_POLICY IN VARCHAR2,
38 p_RETENTION_DAYS IN NUMBER,
39 p_TEMPLATE IN VARCHAR2,
40 p_TEMPLATE_FILE IN VARCHAR2,
41 p_EXECUTION_MODE IN VARCHAR2,
42 p_VARIABLE_NAME IN CSM_VARCHAR_LIST,
43 p_VARIABLE_TYPE IN CSM_VARCHAR_LIST,
44 p_VARIABLE_VALUE_CHAR IN CSM_VARCHAR_LIST,
45 p_VARIABLE_VALUE_DATE IN CSM_DATE_LIST,
46 p_HIDDEN_FLAG IN CSM_VARCHAR_LIST,
47 p_DEFAULT_FLAG IN CSM_VARCHAR_LIST,
48 p_EMAIL_ENABLED IN VARCHAR2,
49 p_RESTRICTED_FLAG IN VARCHAR2,
50 p_DISABLED_FLAG IN VARCHAR2,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_error_message OUT NOCOPY VARCHAR2
53 );
54
55 --Procedure to update the existing query
56 PROCEDURE UPDATE_QUERY
57 (
58 p_API_VERSION IN NUMBER,
59 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
60 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
61 p_QUERY_ID IN NUMBER,
62 p_QUERY_NAME IN VARCHAR2,
63 P_QUERY_DESC IN VARCHAR2,
64 P_QUERY_TYPE IN VARCHAR2,
65 p_QUERY_TEXT1 IN VARCHAR2,
66 p_QUERY_TEXT2 IN VARCHAR2,
67 p_LEVEL_ID IN NUMBER,
68 p_LEVEL_VALUE IN NUMBER,
69 p_PARENT_QUERY_ID IN NUMBER,
70 p_SAVED_QUERY IN VARCHAR2,
71 p_QUERY_OUTPUT_FORMAT IN VARCHAR2,
72 p_MIME_TYPE IN VARCHAR2,
73 p_WORK_FLOW IN VARCHAR2,
74 p_PROCEDURE IN VARCHAR2,
75 p_RETENTION_POLICY IN VARCHAR2,
76 p_RETENTION_DAYS IN NUMBER,
77 p_TEMPLATE IN VARCHAR2,
78 p_TEMPLATE_FILE IN VARCHAR2,
79 p_EXECUTION_MODE IN VARCHAR2,
80 p_VARIABLE_NAME IN CSM_VARCHAR_LIST,
81 p_VARIABLE_TYPE IN CSM_VARCHAR_LIST,
82 p_VARIABLE_VALUE_CHAR IN CSM_VARCHAR_LIST,
83 p_VARIABLE_VALUE_DATE IN CSM_DATE_LIST,
84 p_HIDDEN_FLAG IN CSM_VARCHAR_LIST,
85 p_DEFAULT_FLAG IN CSM_VARCHAR_LIST,
86 p_EMAIL_ENABLED IN VARCHAR2,
87 p_RESTRICTED_FLAG IN VARCHAR2,
88 p_DISABLED_FLAG IN VARCHAR2,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_error_message OUT NOCOPY VARCHAR2
91 );
92
93
94 --Prodecure to delete a query that is wrongly added or that is no longer required
95 PROCEDURE DELETE_QUERY
96 (
97 p_API_VERSION IN NUMBER,
98 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
99 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
100 p_QUERY_ID IN NUMBER,
101 x_return_status OUT NOCOPY VARCHAR2,
102 x_error_message OUT NOCOPY VARCHAR2
103 );
104
105 --Procedure to Execute a Given Query and store in the Result table
106 PROCEDURE EXECUTE_QUERY
107 (
108 p_API_VERSION IN NUMBER,
109 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
110 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
111 p_USER_ID IN NUMBER,
112 p_QUERY_ID IN NUMBER,
113 p_INSTANCE_ID IN NUMBER,
114 x_return_status OUT NOCOPY VARCHAR2,
115 x_error_message OUT NOCOPY VARCHAR2
116 );
117
118 --Procedure to Create a Instance for a Given Query and store in the Acc table
119
120 PROCEDURE INSERT_INSTANCE
121 (
122 p_API_VERSION IN NUMBER,
123 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
124 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
125 p_USER_ID IN NUMBER,
126 p_QUERY_ID IN NUMBER,
127 p_INSTANCE_ID IN VARCHAR2 DEFAULT NULL,
128 p_INSTANCE_NAME IN VARCHAR2,
129 p_VARIABLE_ID IN CSM_INTEGER_LIST,
130 p_VARIABLE_VALUE_CHAR IN CSM_VARCHAR_LIST,
131 p_VARIABLE_VALUE_DATE IN CSM_DATE_LIST,
132 x_INSTANCE_ID OUT NOCOPY NUMBER,
133 x_return_status OUT NOCOPY VARCHAR2,
134 x_error_message OUT NOCOPY VARCHAR2
135 );
136
137 --Procedure to Delete a Instance for a Given Query and store in the Acc table
138
139 PROCEDURE DELETE_INSTANCE
140 ( p_API_VERSION IN NUMBER,
141 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
142 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
143 p_USER_ID IN NUMBER,
144 p_QUERY_ID IN NUMBER,
145 p_INSTANCE_ID IN NUMBER,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_error_message OUT NOCOPY VARCHAR2
148 );
149
150 --Public Procedure to Insert a Result once a Given Query is executed by Custom code
151
152 PROCEDURE INSERT_RESULT
153 ( p_API_VERSION IN NUMBER,
154 p_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
155 p_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
156 p_USER_ID IN NUMBER,
157 p_QUERY_ID IN NUMBER,
158 p_INSTANCE_ID IN VARCHAR2 DEFAULT NULL,
159 p_QUERY_RESULT IN BLOB,
160 x_return_status OUT NOCOPY VARCHAR2,
161 x_error_message OUT NOCOPY VARCHAR2
162 );
163
164 END CSM_QUERY_PUB; -- Package spec