[Home] [Help]
PACKAGE BODY: APPS.AMS_APPROVAL_UTIL_PVT
Source
1 PACKAGE BODY ams_approval_util_pvt as
2 /* $Header: amsvuapb.pls 120.2.12010000.1 2008/07/24 15:18:56 appldev ship $ */
3 PROCEDURE Get_Object_Owner(itemtype IN VARCHAR2,
4 itemkey IN VARCHAR2,
5 x_approver_id OUT NOCOPY NUMBER,
6 x_return_status OUT NOCOPY VARCHAR2)
7
8 IS
9 l_activity_type VARCHAR2(30);
10 l_activity_id NUMBER;
11 l_return_status VARCHAR2(1);
12 l_table_name VARCHAR2(30);
13 l_pk_name VARCHAR2(30);
14 l_stmt VARCHAR2(200);
15 l_owner_id NUMBER;
16
17 BEGIN
18 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
19 -- Determine the Activity Type
20
21 l_activity_type := Wf_Engine.GetItemAttrText(
22 itemtype => itemtype,
23 itemkey => itemkey,
24 aname => 'AMS_ACTIVITY_TYPE');
25
26 IF l_activity_type NOT IN ('CAMP','CSCH','EVEH', 'EVEO', 'EONE',
27 'DELV','OFFR') THEN -- 4378800 Added OFFR
28 Fnd_Message.Set_Name('AMS','AMS_APPR_FUNC_INVALID');
29 Fnd_Msg_Pub.ADD;
30 x_return_status := Fnd_Api.G_RET_STS_ERROR;
31 RETURN;
32 END IF;
33
34 -- Determine the Primary Key
35
36 l_activity_id := Wf_Engine.GetItemAttrNumber(
37 itemtype => itemtype,
38 itemkey => itemkey,
39 aname => 'AMS_ACTIVITY_ID');
40
41
42 -- Get the Table and PK
43
44 Ams_Utility_Pvt.get_qual_table_name_and_pk(p_sys_qual => l_activity_type,
45 x_return_status => x_return_status,
46 x_table_name => l_table_name,
47 x_pk_name => l_pk_name);
48
49
50 IF x_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
51 Fnd_Message.Set_Name('AMS','AMS_APPR_FUNC_NOAPPR');
52 Fnd_Msg_Pub.ADD;
53 x_return_status := Fnd_Api.G_RET_STS_ERROR;
54 RETURN;
55 END IF;
56
57 -- Get the owner_user_id from the table
58 l_stmt := 'SELECT owner_user_id FROM '||l_table_name||' where '||l_pk_name||' = :b1';
59
60 -- Change the owner_user_id to owner_id when the activity type is offer : Bug#6337333
61 IF l_activity_type IN ('OFFR') THEN
62 l_stmt := 'SELECT owner_id FROM '||l_table_name||' where '||l_pk_name||' = :b1';
63 END IF;
64
65
66 EXECUTE IMMEDIATE l_stmt INTO l_owner_id USING l_activity_id;
67
68 x_approver_id := l_owner_id;
69
70 END Get_Object_Owner;
71
72
73 PROCEDURE Get_Parent_Object_Owner(itemtype IN VARCHAR2,
74 itemkey IN VARCHAR2,
75 x_approver_id OUT NOCOPY NUMBER,
76 x_return_status OUT NOCOPY VARCHAR2)
77 IS
78 l_activity_type VARCHAR2(30);
79 l_activity_id NUMBER;
80 l_owner_id NUMBER;
81
82 TYPE owner_csr_type IS REF CURSOR ;
83 l_owner_details owner_csr_type;
84
85 BEGIN
86 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
87
88 -- Determine the Activity Type
89
90 l_activity_type := Wf_Engine.GetItemAttrText(
91 itemtype => itemtype,
92 itemkey => itemkey,
93 aname => 'AMS_ACTIVITY_TYPE');
94
95 IF l_activity_type NOT IN ('CSCH','EVEO', 'OFFR') THEN
96 Fnd_Message.Set_Name('AMS','AMS_APPR_FUNC_INVALID');
97 Fnd_Msg_Pub.ADD;
98 x_return_status := Fnd_Api.G_RET_STS_ERROR;
99 RETURN;
100 END IF;
101
102 IF l_activity_type IN ('CSCH','EVEO') AND
103 NVL(Fnd_Profile.Value(name => 'AMS_SOURCE_FROM_PARENT'), 'N') = 'N' THEN
104 Fnd_Message.Set_Name('AMS','AMS_APPR_FUNC_INVALID');
105 Fnd_Msg_Pub.ADD;
106 x_return_status := Fnd_Api.G_RET_STS_ERROR;
107 RETURN;
108 END IF;
109
110 -- Determine the Primary Key
111
112 l_activity_id := Wf_Engine.GetItemAttrNumber(
113 itemtype => itemtype,
114 itemkey => itemkey,
115 aname => 'AMS_ACTIVITY_ID');
116
117 IF l_activity_type = 'CSCH' THEN
118 OPEN l_owner_details FOR
119 SELECT B.owner_user_id
120 FROM ams_campaign_schedules_vl A, ams_campaigns_vl B
121 WHERE B.campaign_id = A.campaign_id
122 AND A.schedule_id = l_activity_id;
123 ELSIF l_activity_type = 'EVEO' THEN
124 OPEN l_owner_details FOR
125 SELECT B.owner_user_id
126 FROM ams_event_offers_vl A, ams_event_headers_vl B
127 WHERE B.event_header_id = A.event_header_id
128 AND A.event_offer_id = l_activity_id;
129 ELSIF l_activity_type = 'OFFR' THEN
130 OPEN l_owner_details FOR
131 SELECT B.owner_user_id
132 FROM ams_act_offers A, ams_campaigns_vl B
133 WHERE B.campaign_id = A.act_offer_used_by_id
134 AND A.arc_act_offer_used_by = 'CAMP'
135 AND A.qp_list_header_id = l_activity_id;
136 END IF;
137
138 FETCH l_owner_details INTO l_owner_id;
139 IF l_owner_details%NOTFOUND THEN
140 CLOSE l_owner_details;
141 Fnd_Message.Set_Name('AMS','AMS_APPR_FUNC_NOAPPR');
142 Fnd_Msg_Pub.ADD;
143 x_return_status := Fnd_Api.G_RET_STS_ERROR;
144 RETURN;
145 END IF;
146 CLOSE l_owner_details;
147
148 x_approver_id := l_owner_id;
149
150 END Get_Parent_Object_Owner;
151
152 PROCEDURE Get_Budget_Owner(itemtype IN VARCHAR2,
153 itemkey IN VARCHAR2,
154 x_approver_id OUT NOCOPY NUMBER,
155 x_return_status OUT NOCOPY VARCHAR2)
156 IS
157 l_budget_id NUMBER;
158 l_owner_id NUMBER;
159 l_activity_type VARCHAR2(30);
160 l_activity_id NUMBER;
161
162 CURSOR c_fund_owner IS
163 SELECT owner
164 FROM ozf_funds_all_b
165 WHERE fund_id = l_budget_id;
166
167 -- Change for SQL Repository Perf Fix
168 /*
169 CURSOR c_budget_source_owner IS
170 SELECT owner
171 from ozf_funds_all_b
172 WHERE fund_id IN (SELECT budget_source_id
173 FROM ozf_act_budgets
174 WHERE activity_budget_id = l_activity_id);
175 */
176 CURSOR c_budget_source_owner IS
177 SELECT f.owner
178 FROM ozf_funds_all_b f, ozf_act_budgets a
179 WHERE f.fund_id = a.budget_source_id
180 AND a.activity_budget_id = l_activity_id;
181
182 BEGIN
183 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
184
185 l_activity_type := Wf_Engine.GetItemAttrText(
186 itemtype => itemtype,
187 itemkey => itemkey,
188 aname => 'AMS_ACTIVITY_TYPE');
189
190
191 IF l_activity_type NOT IN ('CAMP','CSCH','EVEH', 'EVEO', 'EONE',
192 'DELV', 'FREQ','OFFR') THEN -- 4378800 Added OFFR
193 Fnd_Message.Set_Name('AMS','AMS_APPR_FUNC_INVALID');
194 Fnd_Msg_Pub.ADD;
195 x_return_status := Fnd_Api.G_RET_STS_ERROR;
196 RETURN;
197 END IF;
198
199 l_activity_id := Wf_Engine.GetItemAttrNumber(
200 itemtype => itemtype,
201 itemkey => itemkey,
202 aname => 'AMS_ACTIVITY_ID');
203
204 -- Determine the Budget ID
205 IF l_activity_type <> 'FREQ' THEN
206 l_budget_id := Wf_Engine.GetItemAttrNumber(
207 itemtype => itemtype,
208 itemkey => itemkey,
209 aname => 'AMS_BUDGET_ID',
210 ignore_notfound => true);
211
212 IF l_budget_id IS NOT NULL THEN
213
214 OPEN c_fund_owner;
215 FETCH c_fund_owner INTO l_owner_id;
216 IF c_fund_owner%NOTFOUND THEN
217 CLOSE c_fund_owner;
218 -- Set Message here
219 Fnd_Message.Set_Name('AMS','AMS_CAMP_BAD_FUND_SOURCE_ID');
220 Fnd_Msg_Pub.ADD;
221 x_return_status := Fnd_Api.G_RET_STS_ERROR;
222 RETURN;
223 END IF;
224 CLOSE c_fund_owner;
225
226 ELSE
227
228 Fnd_Message.Set_Name('AMS','AMS_APPR_FUNC_INVALID'); -- Not Budget Line
229 Fnd_Msg_Pub.ADD;
230 x_return_status := Fnd_Api.G_RET_STS_ERROR;
231 RETURN;
232 END IF;
233
234 ELSE -- it is FREQ
235 OPEN c_budget_source_owner;
236 FETCH c_budget_source_owner INTO l_owner_id;
237
238 IF c_budget_source_owner%NOTFOUND THEN
239 CLOSE c_budget_source_owner;
240 -- Set Message here
241 Fnd_Message.Set_Name('AMS','AMS_CAMP_BAD_FUND_SOURCE_ID');
242 Fnd_Msg_Pub.ADD;
243 x_return_status := Fnd_Api.G_RET_STS_ERROR;
244 RETURN;
245 END IF;
246 CLOSE c_budget_source_owner;
247
248 END IF;
249
250 x_approver_id := l_owner_id;
251
252 END Get_Budget_Owner;
253
254 PROCEDURE Get_Parent_Budget_Owner(itemtype IN VARCHAR2,
255 itemkey IN VARCHAR2,
256 x_approver_id OUT NOCOPY NUMBER,
257 x_return_status OUT NOCOPY VARCHAR2)
258 IS
259 l_activity_type VARCHAR2(30);
260 l_activity_id NUMBER;
261 l_owner_id NUMBER;
262
263 CURSOR c_parent_fund_owner IS
264 SELECT B.owner
265 FROM ozf_funds_all_b A, ozf_funds_all_b B
266 WHERE A.parent_fund_id = B.fund_id
267 AND B.fund_id = l_activity_id;
268
269 CURSOR c_budget_source_par_owner IS
270 -- Will return owner if budget is a parent
271 SELECT B.owner
272 FROM ozf_funds_all_b A, ozf_funds_all_b B
273 WHERE A.parent_fund_id = B.fund_id
274 AND B.fund_id IN (SELECT budget_source_id
275 FROM ozf_act_budgets
276 WHERE activity_budget_id = l_activity_id);
277
278
279 BEGIN
280 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
281
282 l_activity_type := Wf_Engine.GetItemAttrText(
283 itemtype => itemtype,
284 itemkey => itemkey,
285 aname => 'AMS_ACTIVITY_TYPE');
286
287 IF l_activity_type NOT IN ('RFRQ', 'FREQ') THEN
288 Fnd_Message.Set_Name('AMS','AMS_APPR_FUNC_INVALID');
289 Fnd_Msg_Pub.ADD;
290 x_return_status := Fnd_Api.G_RET_STS_ERROR;
291 RETURN;
292 END IF;
293
294 -- Determine the Budget ID
295
296 l_activity_id := Wf_Engine.GetItemAttrNumber(
297 itemtype => itemtype,
298 itemkey => itemkey,
299 aname => 'AMS_ACTIVITY_ID');
300
301 IF l_activity_type = 'RFRQ' THEN
302 OPEN c_parent_fund_owner;
303 FETCH c_parent_fund_owner INTO l_owner_id;
304 IF c_parent_fund_owner%NOTFOUND THEN
305 CLOSE c_parent_fund_owner;
306 Fnd_Message.Set_Name('AMS','AMS_CAMP_BAD_FUND_SOURCE_ID');
307 Fnd_Msg_Pub.ADD;
308 x_return_status := Fnd_Api.G_RET_STS_ERROR;
309 RETURN;
310 END IF;
311 CLOSE c_parent_fund_owner;
312 ELSE
313 OPEN c_budget_source_par_owner;
314 FETCH c_budget_source_par_owner INTO l_owner_id;
315 IF c_budget_source_par_owner%NOTFOUND THEN
316 CLOSE c_budget_source_par_owner;
317 Fnd_Message.Set_Name('AMS','AMS_CAMP_BAD_FUND_SOURCE_ID');
318 Fnd_Msg_Pub.ADD;
319 x_return_status := Fnd_Api.G_RET_STS_ERROR;
320 RETURN;
321 END IF;
322 CLOSE c_budget_source_par_owner;
323 END IF;
324
325 x_approver_id := l_owner_id;
326
327 END Get_Parent_Budget_Owner;
328 END ams_approval_util_pvt;