[Home] [Help]
PACKAGE BODY: APPS.AHL_WF_NOTIFICATION_PVT
Source
1 PACKAGE BODY AHL_WF_NOTIFICATION_PVT AS
2 /* $Header: AHLVNTFB.pls 120.1.12020000.2 2012/12/07 01:13:40 sareepar ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_WF_NOTIFICATION_PVT';
5 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
6
7 ------------------------------------------------------------------------------------
8 -- Common constants and variables
9 ------------------------------------------------------------------------------------
10 l_log_current_level NUMBER := FND_LOG.g_current_runtime_level;
11 l_log_statement NUMBER := FND_LOG.level_statement;
12 l_log_procedure NUMBER := FND_LOG.level_procedure;
13 l_log_error NUMBER := FND_LOG.level_error;
14 l_log_exception NUMBER := FND_LOG.level_exception;
15 l_log_unexpected NUMBER := FND_LOG.level_unexpected;
16 ------------------------------------------------------------------------------------
17
18 ------------------------------------------------------------------------------------
19 -- Start of Comments
20 -- Procedure name : Launch_OA_Notification
21 -- Type : Public
22 -- Function : Launches the Workflow notification process having an embedded OA region.
23 -- Pre-reqs :
24 -- Parameters :
25 --
26 -- Launch_OA_Notification Parameters:
27 -- p_object IN CMRO object types, defined for the lookup type Required
28 -- AHL_APPR_OBJECT_TYPE
29 -- p_process_name IN Workflow process name Required
30 -- p_item_type IN Workflow process item type Required
31 -- p_subject IN Notification subject Required
32 -- p_oa_function IN AOL function name for the OA region Required
33 -- p_parami_name IN Parameter name, if any, required by the OA Required
34 -- region. Up to three can be given.
35 -- p_parami_value IN Parameter values Required
36 -- x_item_key OUT Item key of the launched notification Required
37 -- x_return_status OUT Return status. Item key to be used only if Required
38 -- this status is FND_API.G_RET_STS_SUCCESS.
39 --
40 -- End of Comments
41
42 PROCEDURE Launch_OA_Notification (
43 p_object IN VARCHAR2,
44 p_process_name IN VARCHAR2,
45 p_item_type IN VARCHAR2,
46 p_subject IN VARCHAR2,
47 p_oa_function IN VARCHAR2,
48 p_param1_name IN VARCHAR2 := NULL,
49 p_param2_name IN VARCHAR2 := NULL,
50 p_param3_name IN VARCHAR2 := NULL,
51 p_param1_value IN VARCHAR2 := NULL,
52 p_param2_value IN VARCHAR2 := NULL,
53 p_param3_value IN VARCHAR2 := NULL,
54 x_item_key OUT NOCOPY VARCHAR2,
55 x_return_status OUT NOCOPY VARCHAR2
56 ) IS
57
58 --
59 l_api_name CONSTANT VARCHAR2(30) := 'Launch_OA_Notification';
60 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
61
62 l_process_name VARCHAR2(30) := NVL(p_process_name, 'AHL_OA_NOTIFY');
63 l_item_type VARCHAR2(8) := NVL(p_item_type, 'AHLOANTF');
64 --sukhwsin::Changed key generation to generate unique keys. Also increased length of l_item_key.
65 --l_item_key VARCHAR2(30) := p_object||':'||TO_CHAR(sysdate, 'DD-MON-YYYY_HH:MM:SSSSS')
66 l_item_key VARCHAR2(60) := p_object||':'||TO_CHAR(sysdate, 'DD-MON-YYYY_HH:MM:SSSSS')||':'||dbms_random.string('U', 5);
67
68
69 l_dummy1 NUMBER;
70 l_dummy2 VARCHAR2(80);
71 l_amp VARCHAR2(1) := '&';
72 l_param_list VARCHAR2(1000);
73 l_employee_id NUMBER;
74 l_aprvl_rule_id NUMBER;
75 l_aprvl_rule_seq NUMBER;
76 l_aprvr_resource_id NUMBER;
77 l_requester_role VARCHAR2(30);
78 l_approver_role VARCHAR2(30);
79 l_display_name VARCHAR2(80);
80 --
81
82 BEGIN
83 IF (l_log_procedure >= l_log_current_level) THEN
84 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
85 END IF;
86
87 -- initialize procedure return status to success
88 x_return_status := FND_API.G_RET_STS_SUCCESS;
89
90 IF (l_log_statement >= l_log_current_level) THEN
91 FND_LOG.string(l_log_statement, l_full_name, 'the arguments: '||
92 ' p_object > '||p_object||
93 ', p_process_name > '||p_process_name||
94 ', p_item_type > '||p_item_type||
95 ', p_subject > '||p_subject||
96 ', p_oa_function > '||p_oa_function||
97 ', p_param1_name > '||p_param1_name||
98 ', p_param2_name > '||p_param2_name||
99 ', p_param3_name > '||p_param3_name||
100 ', p_param1_value > '||p_param1_value||
101 ', p_param2_value > '||p_param2_value||
102 ', p_param3_value > '||p_param3_value);
103 END IF;
104
105 -- **********************************************************************************
106 -- CREATE WORKFLOW PROCESS INSTANCE
107 WF_ENGINE.CreateProcess(
108 process => l_process_name,
109 itemtype => l_item_type,
110 itemkey => l_item_key);
111 -- **********************************************************************************
112
113 -- **********************************************************************************
114 -- SET PROCESS ATTRIBUTE: SUBJECT
115 WF_ENGINE.SetItemAttrText(
116 itemtype => l_item_type,
117 itemkey => l_item_key,
118 aname => 'SUBJECT',
119 avalue => p_subject);
120 -- **********************************************************************************
121
122 -- **********************************************************************************
123 -- SET PROCESS ATTRIBUTE: OA_BODY (OA region)
124 -- 1) set the parameter list
125 l_param_list := NVL(p_param1_name, '1')||'='||NVL(p_param1_value, '1')||l_amp||
126 NVL(p_param2_name, '1')||'='||NVL(p_param2_value, '1')||l_amp||
127 NVL(p_param3_name, '1')||'='||NVL(p_param3_value, '1');
128
129 IF (l_log_statement >= l_log_current_level) THEN
130 FND_LOG.string(l_log_statement, l_full_name, 'the param list: '||l_param_list);
131 END IF;
132
133 -- 2) set the document id attribute value
134 WF_ENGINE.SetItemAttrDocument(
135 itemtype => l_item_type,
136 itemkey => l_item_key,
137 aname => 'OA_BODY',
138 documentid => 'JSP:/OA_HTML/OA.jsp?OAFunc='||
139 p_oa_function||l_amp||l_param_list);
140 -- **********************************************************************************
141
142 -- **********************************************************************************
143 -- SET PROCESS ATTRIBUTE: APPROVER
144 -- 1) get the approval rule id and sequence
145 AHL_GENERIC_APRV_PVT.Get_Approval_Details(
146 p_object => p_object,
147 p_approval_type => NULL,
148 p_object_details => NULL,
149 x_approval_rule_id => l_aprvl_rule_id,
150 x_approver_seq => l_aprvl_rule_seq,
151 x_return_status => x_return_status);
152
153 IF (l_log_statement >= l_log_current_level) THEN
154 FND_LOG.string(l_log_statement, l_full_name, 'the returned values: '||
155 ' l_aprvl_rule_id > '||l_aprvl_rule_id||
156 ', l_aprvl_rule_seq > '||l_aprvl_rule_seq||
157 ', x_return_status > '||x_return_status);
158 END IF;
159
160 -- if returned with error, don't proceed any further
161 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
162 RETURN;
163 END IF;
164
165 -- 2) get the resource id of the approver
166 AHL_GENERIC_APRV_PVT.Get_Approver_Info(
167 p_rule_id => l_aprvl_rule_id,
168 p_current_seq => l_aprvl_rule_seq,
169 x_approver_id => l_dummy1,
170 x_approver_type => l_dummy2,
171 x_object_approver_id => l_aprvr_resource_id,
172 x_return_status => x_return_status);
173
174 IF (l_log_statement >= l_log_current_level) THEN
175 FND_LOG.string(l_log_statement, l_full_name, 'the returned values: '||
176 ' l_aprvr_resource_id > '||l_aprvr_resource_id||
177 ', x_return_status > '||x_return_status);
178 END IF;
179
180 -- if returned with error, don't proceed any further
181 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
182 RETURN;
183 END IF;
184
185 -- 3) get the employee id of the approver
186 BEGIN
187 SELECT employee_id INTO l_employee_id
188 FROM AHL_JTF_RS_EMP_V
189 WHERE resource_id = l_aprvr_resource_id;
190 EXCEPTION
191 WHEN OTHERS THEN
192 x_return_status := FND_API.G_RET_STS_ERROR;
193 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
194 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
195 p_procedure_name => l_api_name,
196 p_error_text => SUBSTR(SQLERRM,1,500));
197 END IF;
198 END;
199
200 IF (l_log_statement >= l_log_current_level) THEN
201 FND_LOG.string(l_log_statement, l_full_name, 'the returned values: '||
202 ' l_employee_id > '||l_employee_id||
203 ', x_return_status > '||x_return_status);
204 END IF;
205
206 -- if returned with error, don't proceed any further
207 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
208 RETURN;
209 END IF;
210
211 -- 4) get the role for the approver
212 WF_DIRECTORY.getrolename(
213 p_orig_system => 'PER',
214 p_orig_system_id => l_employee_id ,
215 p_name => l_approver_role,
216 p_display_name => l_dummy2);
217
218 IF (l_log_statement >= l_log_current_level) THEN
219 FND_LOG.string(l_log_statement, l_full_name, 'the returned values: '||
220 ' l_approver_role > '||l_approver_role);
221 END IF;
222
223 -- 5) set the approval role attribute value
224 WF_ENGINE.SetItemAttrText(
225 itemtype => l_item_type,
226 itemkey => l_item_key,
227 aname => 'APPROVER',
228 avalue => l_approver_role);
229 -- **********************************************************************************
230
231 -- **********************************************************************************
232 -- SET PROCESS OWNER
233 -- 1) get the employee id of the requester
234 BEGIN
235 SELECT employee_id INTO l_employee_id
236 FROM AHL_JTF_RS_EMP_V
237 WHERE user_id = FND_GLOBAL.USER_ID;
238 EXCEPTION
239 WHEN OTHERS THEN
240 x_return_status := FND_API.G_RET_STS_ERROR;
241 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
242 FND_MSG_PUB.add_exc_msg(p_pkg_name => G_PKG_NAME,
243 p_procedure_name => l_api_name,
244 p_error_text => SUBSTR(SQLERRM,1,500));
245 END IF;
246 END;
247
248 IF (l_log_statement >= l_log_current_level) THEN
249 FND_LOG.string(l_log_statement, l_full_name, 'the returned values: '||
250 ' l_employee_id > '||l_employee_id||
251 ', x_return_status > '||x_return_status);
252 END IF;
253
254 -- if returned with error, don't proceed any further
255 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
256 RETURN;
257 END IF;
258
259 -- 2) get the role for the requester
260 WF_DIRECTORY.getrolename(
261 p_orig_system => 'PER',
262 p_orig_system_id => l_employee_id ,
263 p_name => l_requester_role,
264 p_display_name => l_dummy2);
265
266 IF (l_log_statement >= l_log_current_level) THEN
267 FND_LOG.string(l_log_statement, l_full_name, 'the returned values: '||
268 ' l_requester_role > '||l_requester_role);
269 END IF;
270
271 -- 3) set the process owner
272 WF_ENGINE.SetItemOwner(
273 itemtype => l_item_type,
274 itemkey => l_item_key,
275 owner => l_requester_role);
276 -- **********************************************************************************
277
278 -- **********************************************************************************
279 -- START PROCESS
280 WF_ENGINE.StartProcess(
281 itemtype => l_item_type,
282 itemkey => l_item_key);
283 -- **********************************************************************************
284
285 -- if no errors occurred, set the item key
286 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
287 x_item_key := l_item_key;
288 END IF;
289
290 IF (l_log_procedure >= l_log_current_level) THEN
291 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
292 END IF;
293
294 END Launch_OA_Notification;
295 ------------------------------------------------------------------------------------
296
297 END AHL_WF_NOTIFICATION_PVT;