1 PACKAGE CSM_EMAIL_QUERY_PKG AUTHID CURRENT_USER AS
2 /* $Header: csmeqps.pls 120.3 2011/01/10 10:38:23 saradhak noship $ */
3
4
5 /*
6 * The function to be called by Process Email Mobile Queries concurrent program
7 */
8
9 -- Purpose: Per-seeded queries and to execute them
10 --
11 -- MODIFICATION HISTORY
12 -- Person Date Comments
13 -- RAVIR 22 April 2010 Created
14 --
15 -- --------- ------------------- ------------------------------------------
16 -- Enter package declarations as shown below
17 /*Function to get email preference of an user*/
18 FUNCTION GET_EMAIL_PREF
19 (p_email_id VARCHAR2)
20 RETURN VARCHAR2;
21
22 /*Function to check email id belongs to a single FND_USER*/
23 FUNCTION IS_FND_USER
24 ( p_email_id VARCHAR2)
25 RETURN NUMBER;
26
27 /*Function to check user's access to a mobile query*/
28 FUNCTION CHECK_USER_ACCESS
29 ( p_user_id NUMBER,
30 p_level_id NUMBER,
31 p_level_value NUMBER)
32 RETURN VARCHAR2;
33
34 /*Function to check if user has a vlid assignment for task number*/
35 FUNCTION CHECK_TASK_ACCESS
36 ( p_user_id NUMBER,
37 p_task_number VARCHAR2)
38 RETURN VARCHAR2;
39
40 /*Procedure to execute a Mobile Query command from a email_id and return the query results */
41 PROCEDURE EXECUTE_COMMAND
42 ( p_email_id IN VARCHAR2,
43 p_command_name IN VARCHAR2,
44 p_var_value_lst IN CSM_VARCHAR_LIST,
45 p_instance_id OUT nocopy NUMBER,
46 x_return_status OUT nocopy VARCHAR2,
47 x_error_message OUT nocopy VARCHAR2
48 );
49
50 /*Procedure for Mytasks Mobile Query*/
51 PROCEDURE GET_TASKS
52 ( p_email_id IN VARCHAR2,
53 p_result OUT nocopy CLOB,
54 x_return_status OUT nocopy VARCHAR2,
55 x_error_message OUT nocopy VARCHAR2
56 );
57
58 /*Procedure to update the task status*/
59 PROCEDURE UPDATE_TASK
60 ( p_task_number IN VARCHAR2,
61 p_task_status_id IN VARCHAR2,
62 p_result OUT nocopy CLOB,
63 x_return_status OUT nocopy VARCHAR2,
64 x_error_message OUT nocopy VARCHAR2
65 );
66
67 /*Procedure to update task statu to Accepted*/
68 PROCEDURE ACCEPT_TASK
69 ( p_task_number IN VARCHAR2,
70 p_result OUT nocopy CLOB,
71 x_return_status OUT nocopy VARCHAR2,
72 x_error_message OUT nocopy VARCHAR2
73 );
74
75 /*Procedure to update task statu to Cancel*/
76 PROCEDURE CANCEL_TASK
77 ( p_task_number IN VARCHAR2,
78 p_result OUT nocopy CLOB,
79 x_return_status OUT nocopy VARCHAR2,
80 x_error_message OUT nocopy VARCHAR2
81 );
82
83 /*Procedure to update task statu to Closed*/
84 PROCEDURE CLOSE_TASK
85 ( p_task_number IN VARCHAR2,
86 p_result OUT nocopy CLOB,
87 x_return_status OUT nocopy VARCHAR2,
88 x_error_message OUT nocopy VARCHAR2
89 );
90
91 /*Procedure to update task statu to Traveling*/
92 PROCEDURE TRAVELING_TASK
93 ( p_task_number IN VARCHAR2,
94 p_result OUT nocopy CLOB,
95 x_return_status OUT nocopy VARCHAR2,
96 x_error_message OUT nocopy VARCHAR2
97 );
98
99 /*Procedure to update task statu to Working*/
100 PROCEDURE WORKING_TASK
101 ( p_task_number IN VARCHAR2,
102 p_result OUT nocopy CLOB,
103 x_return_status OUT nocopy VARCHAR2,
104 x_error_message OUT nocopy VARCHAR2
105 );
106
107 /*Procedure to update task statu to Completed*/
108 PROCEDURE COMPLETED_TASK
109 ( p_task_number IN VARCHAR2,
110 p_result OUT nocopy CLOB,
111 x_return_status OUT nocopy VARCHAR2,
112 x_error_message OUT nocopy VARCHAR2
113 );
114
115 /*Procedure to add a task note*/
116 PROCEDURE ADD_TASK_NOTE
117 ( p_task_number IN VARCHAR2,
118 p_note_text1 IN VARCHAR2,
119 p_note_text2 IN VARCHAR2,
120 p_note_visibility IN VARCHAR2,
121 p_sig_delimiter IN VARCHAR2,
122 p_result OUT nocopy CLOB,
123 x_return_status OUT nocopy VARCHAR2,
124 x_error_message OUT nocopy VARCHAR2
125 );
126
127 /*Procedure to get the all details of a service request number*/
128 PROCEDURE GET_SR_DETAILS
129 ( p_sr_number IN VARCHAR2,
130 p_result OUT nocopy CLOB,
131 x_return_status OUT nocopy VARCHAR2,
132 x_error_message OUT nocopy VARCHAR2
133 );
134
135 /*Procedure to get the all details of a task number number*/
136 PROCEDURE GET_TASK_DETAILS
137 ( p_task_number IN VARCHAR2,
138 p_result OUT nocopy CLOB,
139 x_return_status OUT nocopy VARCHAR2,
140 x_error_message OUT nocopy VARCHAR2
141 );
142
143 /*Procedure to get the all details entitlements for a serial number*/
144 PROCEDURE GET_ENTITLEMENTS
145 ( p_serial_number IN VARCHAR2,
146 p_result OUT nocopy CLOB,
147 x_return_status OUT nocopy VARCHAR2,
148 x_error_message OUT nocopy VARCHAR2
149 );
150
151 /*Procedure to get the information of mobile query command*/
152 PROCEDURE HELP_QUERY
153 ( p_query_name IN VARCHAR2,
154 p_result OUT nocopy CLOB,
155 x_return_status OUT nocopy VARCHAR2,
156 x_error_message OUT nocopy VARCHAR2
157 );
158
159 /*Procedure to set profile to a value */
160 FUNCTION SET_PROFILE
161 ( p_profile_name VARCHAR2,
162 p_profile_value VARCHAR2
163 ) RETURN VARCHAR2;
164
165 /*Procedure to send notification on email exception */
166 PROCEDURE NOTIFY_EMAIL_EXCEPTION
167 ( p_email_id IN VARCHAR2,
168 p_subject IN VARCHAR2,
169 p_message IN VARCHAR2,
170 x_return_status OUT nocopy VARCHAR2,
171 x_error_message OUT nocopy VARCHAR2
172 );
173
174 /*Procedure to get the all details of an Item*/
175 PROCEDURE GET_ITEM_DETAILS
176 ( p_item IN VARCHAR2,
177 p_result OUT nocopy CLOB,
178 x_return_status OUT nocopy VARCHAR2,
179 x_error_message OUT nocopy VARCHAR2
180 );
181
182 /*Procedure to get the Available Substitutes or Supersessions*/
183 PROCEDURE GET_PART_SUB
184 ( P_ITEM IN VARCHAR2,
185 P_RESULT OUT NOCOPY CLOB,
186 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
187 X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
188 );
189 /*Procedure to get the Available Ohq*/
190 PROCEDURE GET_PART_AVAIL
191 ( P_ITEM IN VARCHAR2,
192 P_RESULT OUT NOCOPY CLOB,
193 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
194 X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
195 );
196
197 /*Procedure to get the Return Info of Excess and Defective items*/
198 PROCEDURE GET_PART_RETURN_INFO
199 ( P_ITEM IN VARCHAR2,
200 P_RESULT OUT NOCOPY CLOB,
201 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
202 X_ERROR_MESSAGE OUT NOCOPY VARCHAR2
203 );
204
205 /*Funxtion to convert datetime from sevre timezone to user timezone */
206 FUNCTION ADJUST_TIME
207 ( p_date_time IN DATE
208 ) RETURN DATE;
209
210 END CSM_EMAIL_QUERY_PKG;
211 -- Package spec
[Home] [Help]