[Home] [Help]
PACKAGE BODY: APPS.AMS_THRESHOLD_NOTIFY
Source
1 PACKAGE BODY ams_threshold_notify AS
2 /* $Header: amsvtnob.pls 115.15 2001/12/18 10:33:22 pkm ship $*/
3 g_pkg_name CONSTANT VARCHAR2(30) := 'ams_threshold_notify';
4
5 -- Start of Comments
6 -- API name ams_acct_generator
7 -- Type Private
8 -- Version Current version = 1.0
9 -- Initial version = 1.0
10 -- Created Feliu
11 -- Updated 05/15/2001 mpande 1) Introduced generic workflow process
12 -- mpande 2) Commented the old code
13 -- Updated 13/08/2001 fliu added parent owner and subject message.
14 -------------------------------------------------------------------------------
15 -------------------------------------------------------------------------------
16 -- Start of Comments
17 -- NAME
18 -- Get_User_Role
19 --
20 -- PURPOSE
21 -- This Procedure will be return the User role for
22 -- the userid sent
23 -- Called By
24 -- NOTES
25 -- End of Comments
26 -------------------------------------------------------------------------------
27 PROCEDURE Get_User_Role
28 ( p_user_id IN NUMBER,
29 x_role_name OUT VARCHAR2,
30 x_role_display_name OUT VARCHAR2 ,
31 x_return_status OUT VARCHAR2)
32 IS
33
34 CURSOR c_resource IS
35 SELECT employee_id source_id
36 FROM ams_jtf_rs_emp_v
37 WHERE resource_id = p_user_id ;
38
39 l_person_id number;
40
41 BEGIN
42 x_return_status := FND_API.G_RET_STS_SUCCESS;
43
44 OPEN c_resource ;
45 FETCH c_resource INTO l_person_id ;
46 IF c_resource%NOTFOUND THEN
47 x_return_status := FND_API.G_RET_STS_ERROR;
48 FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_RESOURCE_ID');
49 FND_MSG_PUB.Add;
50 END IF;
51 CLOSE c_resource ;
52
53 -- Pass the Employee ID to get the Role
54 WF_DIRECTORY.getrolename(p_orig_system => 'PER',
55 p_orig_system_id => l_person_id ,
56 p_name => x_role_name,
57 p_display_name => x_role_display_name );
58
59 IF x_role_name is NULL THEN
60 x_return_status := FND_API.G_RET_STS_ERROR;
61 FND_MESSAGE.Set_Name('AMS','AMS_APPR_INVALID_ROLE');
62 FND_MSG_PUB.Add;
63 END IF;
64 END Get_User_Role;
65
66
67 /*===========================================================================+
68 | Name: START_PROCESS |
69 | Purpose: Runs the Workflow process to create the Threshold Notification |
70 +===========================================================================*/
71 -- Start of Comments
72 -- API name ams_acct_generator
73 -- Type Private
74 -- Version Current version = 1.0
75 -- Initial version = 1.0
76
77 -- Created Feliu
78 -- Updated 05/15/2001 mpande 1) Introduced generic workflow process
79 -- mpande 2) Commented the old code
80 -------------------------------------------------------------------------------
81
82 PROCEDURE start_process(
83 p_api_version_number IN NUMBER
84 ,x_msg_count OUT NUMBER
85 ,x_msg_data OUT VARCHAR2
86 ,x_return_status OUT VARCHAR2
87 ,p_owner_id IN NUMBER
88 ,p_parent_owner_id IN NUMBER
89 ,p_message_text IN VARCHAR2
90 ,p_activity_log_id IN NUMBER
91 )
92 IS
93 l_itemtype VARCHAR2(30) := 'AMSGAPP';
94 l_workflowprocess VARCHAR2(30) := 'AMS_GENERIC_NOTIFICATIONS';
95 l_itemkey VARCHAR2(38);
96 l_return_status VARCHAR2(1);
97 l_api_version_number CONSTANT NUMBER := 1.0;
98 l_api_name CONSTANT VARCHAR2(30) := 'Start_Process';
99 l_owner_role VARCHAR2(30);
100 l_parent_owner_role VARCHAR2(30);
101 l_owner_disp_name VARCHAR2(30);
102 l_parent_disp_name VARCHAR2(30);
103 l_strSubject VARCHAR2(30);
104 l_strChildSubject VARCHAR2(30);
105
106 BEGIN
107 AMS_UTILITY_PVT.debug_message('Entering ams_threshold_notify.Start_process : ');
108
109 IF NOT fnd_api.compatible_api_call(
110 l_api_version_number
111 ,p_api_version_number
112 ,l_api_name
113 ,g_pkg_name) THEN
114 RAISE fnd_api.g_exc_unexpected_error;
115 END IF;
116
117 -- Initialize API return status to SUCCESS
118 x_return_status := FND_API.G_RET_STS_SUCCESS;
119
120
121 fnd_message.set_name('AMS', 'AMS_THRESHOLD_SUBJECT');
122 l_strSubject := fnd_message.get;
123 fnd_message.set_name('AMS', 'AMS_THRESHOLD_CHILDSUBJ');
124 l_strChildSubject := fnd_message.get;
125
126
127 -- Setting up the role
128 Get_User_Role(p_user_id => p_owner_id ,
129 x_role_name => l_owner_role,
130 x_role_display_name => l_owner_disp_name,
131 x_return_status => l_return_status);
132
133 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
134 RAISE FND_API.G_EXC_ERROR;
135 END IF;
136
137
138 -- we need to start 2 process here one for parent owner , other for budget owner
139 ams_generic_ntfy_pvt.StartProcess(p_activity_type => 'FTHO',
140 p_activity_id => p_activity_log_id,
141 p_workflowprocess => l_workflowprocess,
142 p_item_type => l_itemtype,
143 p_send_by => l_owner_role, -- role of the fund owner
144 p_sent_to => l_owner_role, -- role of the fund owner
145 p_item_key_suffix => '1',
146 p_subject => l_strSubject
147 );
148 IF p_parent_owner_id <>0 THEN
149 -- Setting up the parent role
150 Get_User_Role(p_user_id => p_owner_id ,
151 x_role_name => l_parent_owner_role,
152 x_role_display_name => l_parent_disp_name,
153 x_return_status => l_return_status
154 );
155
156 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159
160 -- Start process for parent owner.
161 ams_generic_ntfy_pvt.StartProcess(p_activity_type => 'FTHO',
162 p_activity_id => p_activity_log_id,
163 p_workflowprocess => l_workflowprocess,
164 p_item_type => l_itemtype,
165 p_send_by => l_parent_owner_role, -- role of the fund parent owner
166 p_sent_to => l_parent_owner_role, -- role of the fund parent owner
167 p_item_key_suffix => '2',
168 p_subject => l_strChildSubject
169 );
170 END IF;
171 -- what about exception for above call?
172
173 EXCEPTION
174
175 WHEN FND_API.G_EXC_ERROR THEN
176 x_return_status := FND_API.G_RET_STS_ERROR;
177 -- Standard call to get message count and if count=1, get the message
178 FND_MSG_PUB.Count_And_Get (
179 p_encoded => FND_API.G_FALSE,
180 p_count => x_msg_count,
181 p_data => x_msg_data
182 );
183
184 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
186 -- Standard call to get message count and if count=1, get the message
187 FND_MSG_PUB.Count_And_Get (
188 p_encoded => FND_API.G_FALSE,
189 p_count => x_msg_count,
190 p_data => x_msg_data
191 );
192
193 WHEN OTHERS THEN
194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
195 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
196 THEN
197 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
198 END IF;
199 -- Standard call to get message count and if count=1, get the message
200 FND_MSG_PUB.Count_And_Get (
201 p_encoded => FND_API.G_FALSE,
202 p_count => x_msg_count,
203 p_data => x_msg_data
204 );
205
206 END start_process; /* START_PROCESS */
207
208 --------------------------------------------------------------------------
209 -- PROCEDURE
210 -- notify_threshold_violate
211 --
212 -- PURPOSE
213 -- Generate the Approval Document for display in messages, either
214 -- text or html
215 -- IN
216 -- document_id - Item Key
217 -- display_type - either 'text/plain' or 'text/html'
218 -- document - document buffer
219 -- document_type - type of document buffer created, either 'text/plain'
220 -- or 'text/html'
221 -- OUT
222 -- USED BY
223 -- - Oracle MArketing Generic Apporval
224 -- HISTORY
225 -- 05/15/2001 MUMU PANDE CREATION
226 ----------------------------------------------------------------------------
227 PROCEDURE notify_threshold_violate(
228 document_id IN VARCHAR2
229 ,display_type IN VARCHAR2
230 ,document IN OUT VARCHAR2
231 ,document_type IN OUT VARCHAR2)
232 IS
233 l_api_name VARCHAR2(61) := g_pkg_name || 'notify_threshold_violate';
234 l_hyphen_pos1 NUMBER;
235 l_fyi_notification VARCHAR2(10000);
236 l_activity_type VARCHAR2(30);
237 l_item_type VARCHAR2(30);
238 l_item_key VARCHAR2(30);
239 l_approval_type VARCHAR2(30);
240 l_approver VARCHAR2(30);
241 l_note VARCHAR2(3000);
242 l_string VARCHAR2(1000);
243 l_string1 VARCHAR2(1000);
244 l_string2 VARCHAR2(2500);
245 l_Activity_log_id NUMBER;
246 l_message VARCHAR2(3000);
247
248 CURSOR c_message_text(p_activity_log_id NUMBER) IS
249 SELECT log_message_text
250 FROM ams_act_logs
251 WHERE activity_log_id = p_activity_log_id;
252
253 BEGIN
254 ams_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
255 document_type := 'text/plain';
256 -- parse document_id for the ':' dividing item type name from item key value
257 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
258 -- release 2.5 version of this demo
259 l_hyphen_pos1 := INSTR(document_id, ':');
260 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
261 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
262
263 l_activity_type := wf_engine.getitemattrtext(itemtype => l_item_type
264 ,itemkey => l_item_key
265 ,aname => 'AMS_ACTIVITY_TYPE');
266
267 l_activity_log_id := wf_engine.getitemattrnumber(itemtype => l_item_type
268 ,itemkey => l_item_key
269 ,aname => 'AMS_ACTIVITY_ID');
270
271 OPEN c_message_text(l_activity_log_id);
272 FETCH c_message_text INTO l_message;
273 CLOSE c_message_text;
274
275 --fnd_message.set_name('AMS', 'AMS_WF_THREHOLD_NTF');
276 --l_string := fnd_message.get;
277
278 -- IF (display_type = 'text/plain') THEN
279 l_fyi_notification := NVL(l_message,'Y')||'AMS_THRESHOLD_NOTIFY';
280 document := document || l_fyi_notification;
281 document_type := 'text/plain';
282
283 RETURN;
284 -- END IF;
285
286 /* IF (display_type = 'text/html') THEN
287 l_fyi_notification :=
288 l_string ||
289 FND_GLOBAL.LOCAL_CHR(10) ||
290 l_string1 ||
291 FND_GLOBAL.LOCAL_CHR(10) ||
292 l_string2;
293 document := document||l_appreq_notification;
294 document_type := 'text/html';
295 RETURN;
296 END IF;
297 */
298 EXCEPTION
299 WHEN OTHERS THEN
300 wf_core.context('AMSGAPP', 'Notify_requestor_FYI', l_item_type, l_item_key);
301 RAISE;
302 END notify_threshold_violate;
303
304 END ams_threshold_notify;