[Home] [Help]
PACKAGE BODY: APPS.AMS_DELIVERABLERULES_PVT
Source
1 PACKAGE BODY AMS_DeliverableRules_PVT AS
2 /* $Header: amsvderb.pls 120.0 2005/05/31 16:40:45 appldev noship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30):='AMS_DeliverableRules_PVT';
6
7
8
9
10 -------------------------------------------------------------------------
11 -- FUNCTION
12 -- is_content_item
13 --
14 -- PURPOSE
15 -- to findout whether the deliverable's custom setup has a
16 -- object_attribute "ECON" ( for future releases or "CPAGE" )
17 --
18 -- HISTORY
19 -- 18-JUL-02 MUSMAN Created.
20 -- 14-AUG-02 MUSMAN Bug 2492857 fix. Changed the query to get the Content
21 -- in Approve_content_item, since the terminlogy has changed.
22 --------------------------------------------------------------------------
23 FUNCTION is_content_item
24 ( p_deliverable_id IN NUMBER
25 )
26 RETURN VARCHAR2 IS
27
28 CURSOR c_custom_attr
29 ( p_custom_setup_id IN NUMBER)
30 IS
31 SELECT 'Y'
32 FROM ams_custom_setup_attr
33 WHERE custom_setup_id = p_custom_setup_id
34 AND object_attribute IN ('ECON'); --,'CPAGE') ;
35
36 CURSOR c_custom_setup
37 IS
38 SELECT custom_setup_id
39 FROM ams_deliverables_all_b
40 WHERE deliverable_id = p_deliverable_id;
41
42
43 l_flag VARCHAR2(1) := 'N';
44 l_custom_Setup_id NUMBER ;
45
46 BEGIN
47
48 OPEN c_custom_setup;
49 FETCH c_custom_setup INTO l_custom_Setup_id ;
50 CLOSE c_custom_setup ;
51
52 OPEN c_custom_attr(l_custom_setup_id);
53 FETCH c_custom_attr INTO l_flag ;
54 CLOSE c_custom_attr ;
55
56 IF l_flag = 'Y' THEN
57 l_flag := FND_API.g_true;
58 ELSIF l_flag = 'N' THEN
59 l_flag := FND_API.g_false;
60 END IF;
61
62 RETURN l_flag ;
63
64 END is_content_item;
65 -------------------------------------------------------------------------
66 -- FUNCTION
67 -- call_budget_request
68 --
69 -- PURPOSE
70 -- to calling the budget api to make the budget active if
71 -- the budget_approval is not reqd.
72 --
73 -- HISTORY
74 -- 25-OCT-02 MUSMAN Created.
75 --------------------------------------------------------------------------
76 PROCEDURE call_budget_request
77 ( p_deliverable_id IN NUMBER
78 )
79 IS
80
81 l_return_status VARCHAR2(1);
82 l_msg_count NUMBER;
83 l_msg_data VARCHAR2(2000);
84 l_status_code VARCHAR2(30);
85
86 BEGIN
87
88 l_return_status := FND_API.g_ret_sts_success;
89
90 AMS_UTILITY_PVT.debug_message(' CALLING THE call_budget_request :'||p_deliverable_id);
91
92 OZF_BUDGETAPPROVAL_PVT.budget_request_approval(
93 p_init_msg_list => FND_API.g_false
94 ,p_api_version => 1.0
95 ,p_commit => FND_API.g_false
96 ,x_return_status => l_return_status
97 ,x_msg_count => l_msg_count
98 ,x_msg_data => l_msg_data
99 ,p_object_type => 'DELV'
100 ,p_object_id => p_deliverable_id
101 ,x_status_code => l_status_code
102 );
103
104 IF l_return_status <> FND_API.g_ret_sts_success THEN
105 RAISE FND_API.g_exc_error;
106 END IF;
107
108 END call_budget_request;
109
110 ------------------------------------------------------------------------
111 -- PROCEDURE
112 -- Update_Status
113 --
114 -- PURPOSE
115 -- This api is called in Update Delv api to call the approvals if necessary
116 --
117 -- HISTORY
118 -- 01-JUL-2002 musman created.
119 ------------------------------------------------------------------------
120 PROCEDURE update_delv_status(
121 p_deliverable_id IN NUMBER
122 ,p_user_status_id IN NUMBER
123 )
124 IS
125
126 l_budget_exist NUMBER;
127 l_old_status_id NUMBER;
128 l_new_status_id NUMBER;
129 l_deny_status_id NUMBER;
130 l_object_version NUMBER;
131 l_approval_type VARCHAR2(30);
132 l_return_status VARCHAR2(1);
133 l_msg_count NUMBER;
134 l_msg_data VARCHAR2(2000);
135
136
137 CURSOR c_old_status IS
138 SELECT user_status_id, object_version_number,
139 status_code,custom_setup_id
140 FROM ams_deliverables_all_b
141 WHERE deliverable_id = p_deliverable_id;
142
143 CURSOR c_budget_exist IS
144 SELECT 1
145 FROM DUAL
146 WHERE EXISTS(
147 SELECT 1
148 FROM ams_act_budgets
149 WHERE arc_act_budget_used_by = 'DELV'
150 AND act_budget_used_by_id = p_deliverable_id);
151
152
153 l_system_status_code VARCHAR2(30) := AMS_Utility_PVT.get_system_status_code(p_user_status_id) ;
154 l_old_status_code VARCHAR2(30) ;
155 l_custom_setup_id NUMBER ;
156 BEGIN
157
158 l_return_status := FND_API.g_ret_sts_success;
159
160 OPEN c_old_status;
161 FETCH c_old_status INTO l_old_status_id, l_object_version, l_old_status_code,l_custom_setup_id ;
162 CLOSE c_old_status;
163
164 AMS_Utility_PVT.debug_message('new status code'||l_system_status_code);
165 AMS_Utility_PVT.debug_message('old status code'||l_old_status_code);
166
167 IF l_old_status_id = p_user_status_id THEN
168 RETURN;
169 END IF;
170
171 AMS_Utility_PVT.check_new_status_change(
172 p_object_type => 'DELV',
173 p_object_id => p_deliverable_id,
174 p_old_status_id => l_old_status_id,
175 p_new_status_id => p_user_status_id,
176 p_custom_setup_id => l_custom_setup_id,
177 x_approval_type => l_approval_type,
178 x_return_status => l_return_status
179 );
180
181 IF l_return_status <> FND_API.g_ret_sts_success THEN
182 RAISE FND_API.g_exc_error;
183 END IF;
184
185
186 IF AMS_UTILITY_PVT.get_system_status_code(p_user_status_id)= 'AVAILABLE'
187 AND AMS_UTILITY_PVT.get_system_status_code(l_old_status_id)= 'NEW'
188 --AND nvl(l_approval_type,'A') <> 'BUDGET'
189 THEN
190 call_budget_request(p_deliverable_id);
191 END IF;
192
193
194 IF l_approval_type = 'BUDGET' THEN
195 -- start budget approval process
196 l_new_status_id := AMS_Utility_PVT.get_default_user_status(
197 'AMS_DELIV_STATUS',
198 'SUBMITTED_BA'
199 );
200 l_deny_status_id := AMS_Utility_PVT.get_default_user_status(
201 'AMS_DELIV_STATUS',
202 'DENIED_BA'
203 );
204
205
206 AMS_Approval_PVT.StartProcess(
207 p_activity_type => 'DELV',
208 p_activity_id => p_deliverable_id,
209 p_approval_type => l_approval_type,
210 p_object_version_number => l_object_version,
211 p_orig_stat_id => l_old_status_id,
212 p_new_stat_id => p_user_status_id,
213 p_reject_stat_id => l_deny_status_id,
214 p_requester_userid => AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id),
215 p_workflowprocess => 'AMS_APPROVAL',
216 p_item_type => 'AMSAPRV'
217 );
218 ELSIF l_approval_type = 'THEME' THEN
219 l_new_status_id := AMS_Utility_PVT.get_default_user_status(
220 'AMS_DELIV_STATUS',
221 'SUBMITTED_TA'
222 );
223 l_deny_status_id := AMS_Utility_PVT.get_default_user_status(
224 'AMS_DELIV_STATUS',
225 'DENIED_TA'
226 );
227
228 AMS_Approval_PVT.StartProcess(
229 p_activity_type => 'DELV',
230 p_activity_id => p_deliverable_id,
231 p_approval_type => 'CONCEPT',
232 p_object_version_number => l_object_version,
233 p_orig_stat_id => l_old_status_id,
234 p_new_stat_id => p_user_status_id,
235 p_reject_stat_id => l_deny_status_id,
236 p_requester_userid => AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id),
237 p_workflowprocess => 'AMS_CONCEPT_APPROVAL',
238 p_item_type => 'AMSAPRV'
239 );
240 ELSE
241 l_new_status_id := p_user_status_id;
242 END IF;
243 /*
244 -- because if there is no BAPL the status get changed from NEW - Available
245 AMS_UTILITY_PVT.debug_message(' l_approval_type :'||l_approval_type);
246 AMS_UTILITY_PVT.debug_message(' THE NEW STATUS ID :'||l_new_status_id);
247
248 IF AMS_UTILITY_PVT.get_system_status_code(l_new_status_id)= 'BUDGET_APPR'
249 AND nvl(l_approval_type,'A') <> 'BUDGET'
250 THEN
251 call_budget_request(p_deliverable_id);
252 END IF;
253 */
254 update_status(p_deliverable_id => p_deliverable_id,
255 p_new_status_id => l_new_status_id,
256 p_new_status_code => AMS_Utility_PVT.get_system_status_code(l_new_status_id)
257 ) ;
258
259 END update_delv_status;
260
261 ------------------------------------------------------------------------
262 -- PROCEDURE
263 -- Update_Status
264 --
265 -- PURPOSE
266 -- This api is called in Update Delv api (and in approvals' api)
267 --
268 -- HISTORY
269 -- 01-JUL-2002 musman created.
270 ------------------------------------------------------------------------
271 PROCEDURE update_status(
272 p_deliverable_id IN NUMBER
273 ,p_new_status_id IN NUMBER
274 ,p_new_status_code IN VARCHAR2
275 )
276 IS
277
278 -- CURSOR c_get_type IS
279 -- SELECT d.can_fulfill_electronic_flag
280 -- FROM ams_custom_Setup_attr a
281 -- ,ams_custom_setups_b b
282 -- ,ams_deliverables_vl d
283 -- WHERE a.object_attribute = 'ECON'
284 -- AND a.custom_Setup_id = b.custom_setup_id
285 -- AND b.object_type ='DELV'
286 -- AND d.custom_setup_id = b.custom_setup_id
287 -- AND d.deliverable_id = p_deliverable_id;
288
289 l_electronic_flag VARCHAR2(1);
290 l_category_type_id NUMBER;
291 l_return_status VARCHAR2(1);
292 l_msg_count NUMBER;
293 l_msg_data VARCHAR2(2000);
294
295 BEGIN
296
297 l_return_status := FND_API.g_ret_sts_success;
298
299 IF (p_new_status_code = 'AVAILABLE')
300 THEN
301 IF is_content_item(p_deliverable_id) = FND_API.g_true
302 THEN
303 Approve_Content_Item(
304 p_deliverable_id => p_deliverable_id
305 ,p_api_version_number => 1.0
306 ,x_return_status => l_return_status
307 ,x_msg_count => l_msg_count
308 ,x_msg_data => l_msg_data
309 );
310
311 IF l_return_status <> FND_API.g_ret_sts_success
312 THEN
313 RAISE FND_API.g_exc_error;
314 END IF;
315
316 END IF; --if deliverable is a content item
317 END IF; -- if new status code is "AVAILABLE"
318
319 UPDATE ams_deliverables_all_b
320 SET user_status_id = p_new_status_id,
321 status_code = p_new_status_code,
322 status_date = SYSDATE,
323 private_flag = DECODE(p_new_status_code,'AVAILABLE','N',private_flag)
324 WHERE deliverable_id = p_deliverable_id;
325
326 END update_status;
327
328
329 ------------------------------------------------------------------------
330 -- PROCEDURE
331 -- Approve_Content_Item
332 --
333 -- PURPOSE
334 -- This api is to approve the content associated.
335 --
336 -- HISTORY
337 -- 01-JUL-2002 aranka created.
338 -- 14-AUG-2002 musman Bug 2492857 fix.
339 -- 06-NOV-2003 musman Modified the CURSOR c_content_approve as requested by soagrawa
340 ------------------------------------------------------------------------
341
342 PROCEDURE Approve_Content_Item(
343 p_deliverable_id IN NUMBER,
344 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
345 p_commit IN VARCHAR2 := FND_API.G_FALSE,
346 p_api_version_number IN NUMBER,
347 x_return_status OUT NOCOPY VARCHAR2,
348 x_msg_count OUT NOCOPY NUMBER,
349 x_msg_data OUT NOCOPY VARCHAR2
350 )
351
352 IS
353 L_API_NAME CONSTANT VARCHAR2(30) := 'Approve_Content_Item';
354 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
355 l_return_status VARCHAR2(1);
356
357 l_content_item_id NUMBER;
358 l_citem_version_id NUMBER;
359 l_object_version_number NUMBER;
360 l_content_item_status VARCHAR2(30);
361
362 CURSOR c_content_approve IS
363 SELECT ci.content_item_id, ver.citem_version_id, ci.object_version_number, content_item_status
364 FROM ibc_associations assoc,ibc_content_items ci , ibc_citem_versions_vl ver
365 WHERE assoc.associated_object_val1 = to_char(p_deliverable_id) ---musman: bug 4145845 Fix
366 AND ci.content_type_code = 'IBC_CONTENT_BLOCK' --anchaudh on 27 Oct '03: changed AMF_EMAIL_DELIVERABLE to IBC_CONTENT_BLOCK
367 AND ci.content_item_id = ver.content_item_id
368 --AND ci.content_type_code = assoc.association_type_code
369 AND assoc.association_type_code = 'AMS_DELV'
370 AND assoc.content_item_id = ci.content_item_id
371 AND ver.citem_Version_id = (select max(citem_version_id) from ibc_citem_versions_b where content_item_id = ci.content_item_id);
372 --anchaudh on 27 Oct '03: added the above extra clause.
373
374 BEGIN
375 -- Standard Start of API savepoint
376 SAVEPOINT Approve_Content_Item;
377
378 -- Initialize message list if p_init_msg_list is set to TRUE.
379 IF FND_API.to_Boolean( p_init_msg_list )
380 THEN
381 FND_MSG_PUB.initialize;
382 END IF;
383
384 -- Debug Message
385 AMS_UTILITY_PVT.debug_message('Public API: ' || l_api_name || 'start');
386
387
388 -- Initialize API return status to SUCCESS
389 x_return_status := FND_API.G_RET_STS_SUCCESS;
390
391 -- call ibc approve content API
392 OPEN c_content_approve;
393 FETCH c_content_approve INTO l_content_item_id, l_citem_version_id, l_object_version_number, l_content_item_status;
394 CLOSE c_content_approve;
395
396 IF l_content_item_status IS NULL
397 OR l_content_item_status <> 'PENDING'
398 THEN
399 AMS_UTILITY_PVT.debug_message('l_content_item_status is either null or not PENDING ' || l_content_item_status);
400 RETURN;
401 END IF;
402
403 AMS_UTILITY_PVT.debug_message('Public API: ' || l_content_item_id || ':' || l_citem_version_id ||':' || l_object_version_number || ':' || l_content_item_status);
404
405 IBC_CITEM_ADMIN_GRP.approve_item(
406 p_citem_ver_id => l_citem_version_id,
407 p_commit => p_commit,
408 p_api_version_number => 1.0,
409 p_init_msg_list => p_init_msg_list,
410 px_object_version_number => l_object_version_number,
411 x_return_status => x_return_status,
412 x_msg_count => x_msg_count,
413 x_msg_data => x_msg_data);
414
415 -- Check return status from the above procedure call
416 IF x_return_status = FND_API.G_RET_STS_ERROR then
417 RAISE FND_API.G_EXC_ERROR;
418 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
420 END IF;
421 --
422 -- End of API body.
423 --
424 -- Standard check for p_commit
425 IF FND_API.to_Boolean( p_commit )
426 THEN
427 COMMIT WORK;
428 END IF;
429
430
431 -- Debug Message
432 AMS_UTILITY_PVT.debug_message('Public API: ' || l_api_name || 'end');
433
434 -- Standard call to get message count and if count is 1, get message info.
435 FND_MSG_PUB.Count_And_Get
436 (p_count => x_msg_count,
437 p_data => x_msg_data
438 );
439
440 EXCEPTION
441
442 WHEN AMS_Utility_PVT.resource_locked THEN
443 x_return_status := FND_API.g_ret_sts_error;
444 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
445 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
446 FND_MSG_PUB.add;
447 END IF;
448
449 WHEN FND_API.G_EXC_ERROR THEN
450 ROLLBACK TO Approve_Content_Item;
451 x_return_status := FND_API.G_RET_STS_ERROR;
452 -- Standard call to get message count and if count=1, get the message
453 FND_MSG_PUB.Count_And_Get (
454 p_encoded => FND_API.G_FALSE,
455 p_count => x_msg_count,
456 p_data => x_msg_data
457 );
458
459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
460 ROLLBACK TO Approve_Content_Item;
461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
462 -- Standard call to get message count and if count=1, get the message
463 FND_MSG_PUB.Count_And_Get (
464 p_encoded => FND_API.G_FALSE,
465 p_count => x_msg_count,
466 p_data => x_msg_data
467 );
468
469 WHEN OTHERS THEN
470 ROLLBACK TO Approve_Content_Item;
471 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
472 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
473 THEN
474 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
475 END IF;
476 -- Standard call to get message count and if count=1, get the message
477 FND_MSG_PUB.Count_And_Get (
478 p_encoded => FND_API.G_FALSE,
479 p_count => x_msg_count,
480 p_data => x_msg_data
481 );
482
483 END Approve_Content_Item;
484
485
486
487 END AMS_DeliverableRules_PVT;