[Home] [Help]
PACKAGE BODY: APPS.IEX_WF_NEW_DEL_STATUS_PUB
Source
1 PACKAGE BODY IEX_WF_NEW_DEL_STATUS_PUB AS
2 /* $Header: iexwfdwb.pls 120.7.12010000.1 2008/07/29 10:13:42 appldev ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_WF_NEW_DEL_STATUS_PUB';
5
6 PG_DEBUG NUMBER(2); -- fix a bug 3975142 := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
7
8 PROCEDURE invoke_new_del_status_wf
9 (
10 p_api_version IN NUMBER := 1.0,
11 p_init_msg_list IN VARCHAR2, -- fix a bug 3975142 := FND_API.G_FALSE,
12 p_commit IN VARCHAR2, -- fix a bug 3975142 := FND_API.G_FALSE,
13 p_delinquency_id IN NUMBER,
14 p_object_id IN NUMBER,
15 p_object_type IN VARCHAR2,
16 p_user_id IN NUMBER,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2
20 )
21 IS
22 l_parameter_list wf_parameter_list_t;
23 l_event_name varchar2(240); -- fix a bug 3975142 := 'oracle.apps.iex.delstatus.create';
24 l_result VARCHAR2(10);
25 itemtype VARCHAR2(30);
26 itemkey VARCHAR2(1000);
27 l_object_type varchar2(30);
28 workflowprocess VARCHAR2(30);
29
30 l_error_msg VARCHAR2(2000);
31 l_return_status VARCHAR2(20);
32 l_msg_count NUMBER;
33 l_msg_data VARCHAR2(2000);
34 l_api_name VARCHAR2(100); -- fix a bug 3975142 := 'invoke_new_del_status_wf';
35 l_api_version_number CONSTANT NUMBER := 1.0;
36 l_api_version_number2 CONSTANT NUMBER := 2.0;
37
38 l_action_type varchar2(7); -- fix a bug 3975142 := 'created';
39 l_manager_id number;
40 l_manager_name varchar2(30);
41
42 CURSOR c_get_manager(p_user_id NUMBER) IS
43 SELECT b.user_id, b.user_name
44 FROM JTF_RS_RESOURCE_EXTNS a
45 ,JTF_RS_RESOURCE_EXTNS b
46 WHERE b.source_id = a.source_mgr_id
47 AND a.user_id = p_user_id;
48
49 BEGIN
50 -- Standard Start of API savepoint
51 SAVEPOINT invoke_new_del_status_wf;
52
53 iex_debug_pub.logmessage('IEX- Start Invoking Creating New Delinquency Workflow');
54
55 --PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
56 l_event_name := 'oracle.apps.iex.delstatus.create';
57 l_api_name := 'invoke_new_del_status_wf';
58 l_action_type := 'created';
59
60 iex_debug_pub.logmessage('IEX-1 Start Invoking and object_type'||p_object_type);
61 iex_debug_pub.logmessage('l_api_version_number..'||l_api_version_number||'p_api_version..'||p_api_version);
62 -- Standard call to check for call compatibility.
63
64 if p_object_type = 'Bankruptcy' then
65 IF NOT FND_API.Compatible_API_Call ( l_api_version_number2,
66 p_api_version,
67 l_api_name,
68 G_PKG_NAME)
69 THEN
70 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
71 END IF;
72 else
73 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
74 p_api_version,
75 l_api_name,
76 G_PKG_NAME)
77 THEN
78 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
79 END IF;
80 end if;
81
82
83 iex_debug_pub.logmessage('IEX-2 Start Invoking Creating New Delinquency Workflow');
84 -- Initialize message list if p_init_msg_list is set to TRUE.
85 IF FND_API.to_Boolean( p_init_msg_list )
86 THEN
87 FND_MSG_PUB.initialize;
88 END IF;
89
90
91
92 -- Initialize API return status to SUCCESS
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94
95 iex_debug_pub.logmessage('IEX- Before getting item key information....');
96
97 SELECT TO_CHAR(IEX_DEL_WF_S.NEXTVAL) INTO itemkey FROM dual;
98
99 itemkey := 'NEWST'||TO_CHAR(p_delinquency_id)||itemkey;
100
101 iex_debug_pub.logmessage('IEX- Before getting manager information....');
102
103 begin
104 open c_get_manager(p_user_id);
105 Fetch c_get_manager Into l_manager_id, l_manager_name;
106 if c_get_manager%NOTFOUND Then
107 l_manager_id := null;
108 l_manager_name := null;
109 end if;
110 Close c_get_manager;
111 exception
112 When others then
113 l_manager_id := null;
114 l_manager_name := null;
115 end;
116
117 iex_debug_pub.logmessage('IEX- Manager Name ....'||l_manager_name);
118 iex_debug_pub.logmessage('IEX- Manager ID ....'||l_manager_id);
119 iex_debug_pub.logmessage('IEX- Object ID ....'||p_object_id);
120
121 if p_object_type = 'CRepossession' then
122 wf_event.AddParameterToList('CONTRACT_ID', p_delinquency_id,l_parameter_list);
123 l_object_type := 'Repossession';
124 elsif p_object_type = 'CWriteoff' then
125 wf_event.AddParameterToList('CONTRACT_ID', p_delinquency_id,l_parameter_list);
126 l_object_type := 'Writeoff';
127 elsif p_object_type = 'CLitigation' then
128 wf_event.AddParameterToList('CONTRACT_ID', p_delinquency_id,l_parameter_list);
129 elsif p_object_type = 'Bankruptcy' then
130 l_object_type := 'Bankruptcy';
131 --wf_event.AddParameterToList('BAKRUPTCY_ID', p_object_id,l_parameter_list);
132 else
133 wf_event.AddParameterToList('DELINQUENCY_ID', p_delinquency_id,l_parameter_list);
134 l_object_type := p_object_type;
135 end if;
136
137 wf_event.AddParameterToList('MANAGER_ID',l_manager_id,l_parameter_list);
138 wf_event.AddParameterToList('MANAGER_NAME',l_manager_name,l_parameter_list);
139 wf_event.AddParameterToList('TYPE_ID',p_object_id,l_parameter_list);
140 wf_event.AddParameterToList('SUB_DEL_TYPE',l_object_type,l_parameter_list);
141 wf_event.AddParameterToList('ACTION_TYPE',l_action_type,l_parameter_list);
142
143 iex_debug_pub.logmessage('IEX- Starting Raising Workflow ....');
144
145 wf_event.raise( p_event_name => l_event_name
146 ,p_event_key => itemkey
147 ,p_parameters => l_parameter_list);
148
149 iex_debug_pub.logmessage('IEX- Ending Raising Workflow and before Commit ....');
150
151 COMMIT ;
152 l_parameter_list.DELETE;
153
154
155 -- Standard call to get message count and if count is 1, get message info.
156 FND_MSG_PUB.Count_And_Get
157 ( p_count => x_msg_count,
158 p_data => x_msg_data
159 );
160
161 --Start bug 6740370 gnramasa 11th Jan 08
162 EXCEPTION
163 WHEN FND_API.G_EXC_ERROR THEN
164 ROLLBACK TO invoke_new_del_status_wf;
165 x_return_status := FND_API.G_RET_STS_ERROR;
166 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
167
168 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
169 ROLLBACK TO invoke_new_del_status_wf;
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
171 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
172
173 WHEN OTHERS THEN
174 ROLLBACK TO invoke_new_del_status_wf;
175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
176 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
177 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
178 END IF;
179 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
180 --End bug 6740370 gnramasa 11th Jan 08
181 ----------------------------------
182 END invoke_new_del_status_wf;
183
184 PROCEDURE invoke_upd_del_status_wf
185 (
186 p_api_version IN NUMBER := 1.0,
187 p_init_msg_list IN VARCHAR2, -- fix a bug 3975142 := FND_API.G_FALSE,
188 p_commit IN VARCHAR2, -- fix a bug 3975142 := FND_API.G_FALSE,
189 p_delinquency_id IN NUMBER,
190 p_object_id IN NUMBER,
191 p_object_type IN VARCHAR2,
192 p_user_id IN NUMBER,
193 x_return_status OUT NOCOPY VARCHAR2,
194 x_msg_count OUT NOCOPY NUMBER,
195 x_msg_data OUT NOCOPY VARCHAR2
196 )
197 IS
198 l_parameter_list wf_parameter_list_t;
199 l_event_name varchar2(240); -- fix a bug 3975142 := 'oracle.apps.iex.delstatus.update';
200 l_result VARCHAR2(10);
201 itemtype VARCHAR2(30);
202 itemkey VARCHAR2(1000);
203 l_object_type varchar2(30);
204 workflowprocess VARCHAR2(30);
205
206 l_error_msg VARCHAR2(2000);
207 l_return_status VARCHAR2(20);
208 l_msg_count NUMBER;
209 l_msg_data VARCHAR2(2000);
210 l_api_name VARCHAR2(100); -- fix a bug 3975142 := 'invoke_upd_del_status_wf';
211 l_api_version_number CONSTANT NUMBER := 1.0;
212 l_api_version_number2 CONSTANT NUMBER := 2.0;
213
214 l_action_type varchar2(7); -- fix a bug 3975142 := 'updated';
215 l_manager_id number;
216 l_manager_name varchar2(30);
217
218 CURSOR c_get_manager(p_user_id NUMBER) IS
219 SELECT b.user_id, b.user_name
220 FROM JTF_RS_RESOURCE_EXTNS a
221 ,JTF_RS_RESOURCE_EXTNS b
222 WHERE b.source_id = a.source_mgr_id
223 AND a.user_id = p_user_id;
224
225 BEGIN
226 -- Standard Start of API savepoint
227 SAVEPOINT invoke_new_del_status_wf;
228
229 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
230 l_event_name := 'oracle.apps.iex.delstatus.update';
231 l_api_name := 'invoke_upd_del_status_wf';
232 l_action_type := 'updated';
233
234
235 -- Standard call to check for call compatibility.
236 if p_object_type = 'Bankruptcy' then
237 IF NOT FND_API.Compatible_API_Call ( l_api_version_number2,
238 p_api_version,
239 l_api_name,
240 G_PKG_NAME)
241 THEN
242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
243 END IF;
244 else
245 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
246 p_api_version,
247 l_api_name,
248 G_PKG_NAME)
249 THEN
250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
251 END IF;
252 end if;
253
254
255 -- Initialize message list if p_init_msg_list is set to TRUE.
256 IF FND_API.to_Boolean( p_init_msg_list )
257 THEN
258 FND_MSG_PUB.initialize;
259 END IF;
260
261
262
263 -- Initialize API return status to SUCCESS
264 x_return_status := FND_API.G_RET_STS_SUCCESS;
265
266 SELECT TO_CHAR(IEX_DEL_WF_S.NEXTVAL) INTO itemkey FROM dual;
267
268 itemkey := 'NEWST'||TO_CHAR(p_delinquency_id)||itemkey;
269
270 begin
271 open c_get_manager(p_user_id);
272 Fetch c_get_manager Into l_manager_id, l_manager_name;
273 if c_get_manager%NOTFOUND Then
274 l_manager_id := null;
275 l_manager_name := null;
276 end if;
277 Close c_get_manager;
278 exception
279 When others then
280 l_manager_id := null;
281 l_manager_name := null;
282 end;
283
284 if p_object_type = 'CRepossession' then
285 wf_event.AddParameterToList('CONTRACT_ID', p_delinquency_id,l_parameter_list);
286 l_object_type := 'Repossession';
287 elsif p_object_type = 'CWriteoff' then
288 wf_event.AddParameterToList('CONTRACT_ID', p_delinquency_id,l_parameter_list);
289 l_object_type := 'Writeoff';
290 elsif p_object_type = 'CLitigation' then
291 wf_event.AddParameterToList('CONTRACT_ID', p_delinquency_id,l_parameter_list);
292 l_object_type := 'Litigation';
293 else
294 wf_event.AddParameterToList('DELINQUENCY_ID', p_delinquency_id,l_parameter_list);
295 l_object_type := p_object_type;
296 end if;
297
298 wf_event.AddParameterToList('MANAGER_ID',l_manager_id,l_parameter_list);
299 wf_event.AddParameterToList('MANAGER_NAME',l_manager_name,l_parameter_list);
300 wf_event.AddParameterToList('TYPE_ID',p_object_id,l_parameter_list);
301 wf_event.AddParameterToList('SUB_DEL_TYPE',l_object_type,l_parameter_list);
302 wf_event.AddParameterToList('ACTION_TYPE',l_action_type,l_parameter_list);
303
304
305
306 wf_event.raise( p_event_name => l_event_name
307 ,p_event_key => itemkey
308 ,p_parameters => l_parameter_list);
309
310 COMMIT ;
311 l_parameter_list.DELETE;
312
313
314 -- Standard call to get message count and if count is 1, get message info.
315 FND_MSG_PUB.Count_And_Get
316 ( p_count => x_msg_count,
317 p_data => x_msg_data
318 );
319
320 --Start bug 6740370 gnramasa 11th Jan 08
321 EXCEPTION
322 WHEN FND_API.G_EXC_ERROR THEN
323 ROLLBACK TO invoke_new_del_status_wf;
324 x_return_status := FND_API.G_RET_STS_ERROR;
325 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
326
327 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
328 ROLLBACK TO invoke_new_del_status_wf;
329 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
330 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
331
332 WHEN OTHERS THEN
333 ROLLBACK TO invoke_new_del_status_wf;
334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
335 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
336 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
337 END IF;
338 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
339 --End bug 6740370 gnramasa 11th Jan 08
340 ----------------------------------
341 END invoke_upd_del_status_wf;
342
343
344 END IEX_WF_NEW_DEL_STATUS_PUB;