[Home] [Help]
PACKAGE BODY: APPS.IEX_STRY_CHECKLIST_PUB
Source
1 PACKAGE BODY IEX_STRY_CHECKLIST_PUB AS
2 /* $Header: iexpschb.pls 120.1.12010000.3 2008/08/13 15:36:34 pnaveenk ship $ */
3 /*
4 * This procedure needs to be called with an itemtype and workflow process
5 * which'll launch workflow .Start Workflow will call workflow based on
6 * Meth_flag in methodology base table
7 */
8
9 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_STRY_CHECKLIST_PUB';
10
11 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
12 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
13
14 PROCEDURE create_checklist_strategy
15 (
16 P_Api_Version_Number IN NUMBER,
17 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
18 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
19 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
20 X_Return_Status OUT NOCOPY VARCHAR2,
21 X_Msg_Count OUT NOCOPY NUMBER,
22 X_Msg_Data OUT NOCOPY VARCHAR2,
23 p_StrategyID IN NUMBER
24 ) IS
25
26 l_result VARCHAR2(10);
27 l_error_msg VARCHAR2(2000);
28 l_return_status VARCHAR2(20);
29 l_msg_count NUMBER;
30 l_msg_data VARCHAR2(2000);
31 l_api_name VARCHAR2(100) := 'CREATE_CHECKLIST_STRATEGY';
32 l_api_version_number CONSTANT NUMBER := 2.0;
33
34
35 fdelinquencyId number;
36 fObjectId number;
37 fobjectType varchar2(40);
38 fStrategyVersionNumber number := 0;
39 fCheckListTemplateID number;
40
41 l_ObjectType VARCHAR2(30);
42 l_strategy_id number;
43 l_strategy_template_id number;
44 l_object_version_number number := 2.0;
45 x_work_item_id number;
46
47 l_strategy_rec IEX_STRATEGY_PVT.STRATEGY_REC_TYPE;
48 l_strategy2_rec IEX_STRATEGY_PVT.STRATEGY_REC_TYPE;
49 l_strategy_work_item_rec IEX_STRATEGY_WORK_ITEMS_PVT.STRATEGY_WORK_ITEM_REC_TYPE;
50
51 cursor c_witems(p_template_id NUMBER)
52 is
53 select sxref.strategy_temp_id TEMPLATE_ID,
54 sxref.WORK_ITEM_TEMP_ID WORK_ITEM_TEMPLATE_ID,
55 sxref.work_item_order ORDER_BY
56 from iex_strategy_work_temp_xref sxref
57 where sxref.strategy_temp_id = p_template_id;
58
59 c_StrategySelect varchar2(1000) :=
60 ' select s.delinquency_id, ' ||
61 ' s.object_id object_id, s.object_type object_type , s.object_version_number, ' ||
62 ' t.check_list_temp_id ' ||
63 ' from iex_strategies s, iex_strategy_templates_b t where ' ||
64 ' s.strategy_id = :pObjectID and s.strategy_template_id = t.strategy_temp_id ' ;
65 BEGIN
66
67 -- Standard Start of API savepoint
68 SAVEPOINT create_checklist_strategy;
69
70 -- Initialize API return status to SUCCESS
71 l_return_status := FND_API.G_RET_STS_SUCCESS;
72
73 -- Initialize message list IF p_init_msg_list is set to TRUE.
74 IF FND_API.to_Boolean( p_init_msg_list ) THEN
75 FND_MSG_PUB.initialize;
76 END IF;
77
78 -- Debug Message
79 IEX_DEBUG_PUB.LogMessage('PUB:' || G_PKG_NAME || '.' || l_api_name || ' Start');
80
81 -- Debug Message
82 IEX_DEBUG_PUB.LogMessage('1. S.ID= ' || p_StrategyID || ' CLT.ID= ' );
83
84 -- Debug Message
85 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
86 IEX_DEBUG_PUB.LogMessage(
87 debug_msg => 'PUB:' || G_PKG_NAME || '.' || l_api_name || ' Start',
88 print_date => 'Y');
89 END IF;
90
91 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
92 IEX_DEBUG_PUB.LogMessage(
93 debug_msg => '1. S.ID= ' || p_StrategyID || ' CLT.ID= ',
94 print_date => 'Y');
95 END IF;
96
97 -- Initialize API return status to SUCCESS
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 BEGIN
101 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
102 iex_debug_pub.logmessage( debug_msg => '15. S.ID= ' || p_StrategyID, print_date => 'Y');
103 IEX_DEBUG_PUB.LogMessage( debug_msg => c_StrategySelect || ' Start', print_date => 'Y');
104 END IF;
105
106 Execute Immediate c_StrategySelect into
107 fDelinquencyID, fObjectID,
108 fObjectType, fStrategyVersionNumber, fCheckListTemplateID using p_StrategyID;
109
110 EXCEPTION
111 When NO_DATA_FOUND then
112 x_return_status := 'F';
113
114 AS_UTILITY_PVT.Set_Message(
115 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
116 p_msg_name => 'IEX_STRATEGY_NOT_EXISTS',
117 p_token1 => 'STRATEGY_ID ',
118 p_token1_value => to_char(p_StrategyID));
119
120 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
121 iex_debug_pub.logmessage( debug_msg => '17. IEX_STRATEGY_NOT_EXISTS', print_date => 'Y');
122 END IF;
123 RAISE FND_API.G_EXC_ERROR;
124 return;
125 When OTHERS then
126 x_return_status := 'F';
127 AS_UTILITY_PVT.Set_Message(
128 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
129 p_msg_name => 'IEX_OTHERS_SQL',
130 p_token1 => 'STRATEGY_ID ',
131 p_token1_value => to_char(p_StrategyID));
132 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
133 iex_debug_pub.logmessage( debug_msg => '18. ERROR: IEX_OTHERS_SQL ', print_date => 'Y');
134 END IF;
135 RAISE FND_API.G_EXC_ERROR;
136 return;
137
138 END;
139
140 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
141 IEX_DEBUG_PUB.LogMessage(
142 debug_msg => '1. S.ID= ' || p_StrategyID || ' DB. CLT.ID= ' || fCheckListTemplateID,
143 print_date => 'Y');
144 END IF;
145
146 if (fCheckListTemplateID is NULL) then
147 x_return_status := 'F';
148 AS_UTILITY_PVT.Set_Message(
149 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
150 p_msg_name => 'IEX_CHECKLIST_NOT_EXIST',
151 p_token1 => 'STRATEGY_ID ',
152 p_token1_value => to_char(p_StrategyID));
153 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
154 iex_debug_pub.logmessage( debug_msg => '20. CheckListTemplate Not exists ', print_date => 'Y');
155 END IF;
156 RAISE FND_API.G_EXC_ERROR;
157 return;
158 end if;
159
160 BEGIN
161
162 l_strategy_rec.strategy_template_id := fCheckListTemplateID;
163 l_strategy_rec.delinquency_id := fdelinquencyId;
164 l_strategy_rec.next_work_item_id := null;
165 l_strategy_rec.object_id := fObjectID;
166 l_strategy_rec.object_type := fObjectType;
167 l_strategy_rec.status_code := 'CLOSED';
168 l_strategy_rec.checklist_yn := 'Y';
169 l_object_version_number := 1;
170 --Bug#6870773 Naveen
171 if nvl(fnd_profile.value('IEX_PROC_STR_ORG'),'N') = 'Y' then
172 --l_strategy_rec.org_id := fnd_profile.value('ORG_ID') ;
173 l_strategy_rec.org_id:=mo_global.get_current_org_id;
174
175 else
176 l_strategy_rec.org_id := null;
177 end if;
178
179 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
180 iex_debug_pub.logmessage( debug_msg => '2. Create Checklist strategy ', print_date => 'Y');
181 END IF;
182
183 IEX_DEBUG_PUB.LogMessage('2. Create Checklist strategy ');
184
185 iex_strategy_pvt.create_strategy(
186 P_Api_Version_Number=>2.0,
187 p_commit => FND_API.G_FALSE,
188 P_Init_Msg_List =>FND_API.G_FALSE,
189 p_strategy_rec => l_strategy_rec,
190 x_return_status=>l_return_status,
191 x_msg_count=>l_msg_count,
192 x_msg_data=>l_msg_data,
193 x_strategy_id => l_strategy_id
194 );
195
196 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
197 iex_debug_pub.logmessage( debug_msg => 'Return status = ' || l_return_status, print_date => 'Y');
198 END IF;
199 IEX_DEBUG_PUB.LogMessage('Return status = ' || l_return_status);
200 if (x_return_status <> 'S') then
201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
202 end if;
203
204 l_strategy_rec.strategy_id := l_strategy_id;
205
206 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
207 iex_debug_pub.logmessage( debug_msg => 'Strategy created. id = ' || l_strategy_id, print_date => 'Y');
208 END IF;
209 IEX_DEBUG_PUB.LogMessage('Strategy created. id = ' || l_strategy_id);
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
214 iex_debug_pub.logmessage( debug_msg => 'IEX_STRATEGY_CREATE_FAILED', print_date => 'Y');
215 END IF;
216 AS_UTILITY_PVT.Set_Message(
217 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
218 p_msg_name => 'IEX_STRATEGY_CREATE_FAILED',
219 p_token1 => 'OBJECT_ID ',
220 p_token1_value => to_char(fObjectID));
221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
222 END;
223
224
225 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
226 iex_debug_pub.logmessage( debug_msg => '3. Create WorkItems '
227 || l_strategy_rec.strategy_id, print_date => 'Y');
228 END IF;
229 IEX_DEBUG_PUB.LogMessage('3. Create WorkItems '
230 || l_strategy_rec.strategy_id);
231
232 FOR c_get_witem_rec in c_witems(fCheckListTemplateID ) LOOP
233
234 -- IF PG_DEBUG < 10 THEN
235 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
236 iex_debug_pub.logMessage ('create_checklist_strategy: ' || 'work_item_template_id'|| c_get_witem_rec.work_item_template_id);
237 END IF;
238
239 l_strategy_work_item_rec.resource_id := 0;
240 l_strategy_work_item_rec.work_item_template_id
241 :=c_get_witem_rec.work_item_template_id;
242 l_strategy_work_item_rec.strategy_id := l_strategy_rec.strategy_id;
243 l_strategy_work_item_rec.status_code
244 :='OPEN';
245 l_strategy_work_item_rec.strategy_temp_id := fCheckListTemplateID;
246 l_strategy_work_item_rec.work_item_order :=c_get_witem_rec.order_by;
247
248 l_strategy_work_item_rec.execute_start :=SYSDATE;
249
250 -- IF PG_DEBUG < 10 THEN
251 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
252 iex_debug_pub.logmessage ('create_checklist_strategy: ' || 'before calling create_work_pvt.create');
253 END IF;
254
255 iex_strategy_work_items_pvt.create_strategy_work_items
256 (P_Api_Version_Number =>2.0,
257 P_Init_Msg_List =>FND_API.G_TRUE,
258 P_Commit =>FND_API.G_FALSE,
259 p_validation_level =>FND_API.G_VALID_LEVEL_NONE,
260 p_strategy_work_item_rec =>l_strategy_work_item_rec,
261 x_work_item_id =>x_work_item_id,
262 x_return_status =>l_return_status,
263 x_msg_count =>l_msg_count,
264 x_msg_data =>l_msg_data);
265
266 -- IF PG_DEBUG < 10 THEN
267 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
268 iex_debug_pub.logmessage ('create_checklist_strategy: ' || 'after calling create_work_pvt.create');
269 END IF;
270 -- IF PG_DEBUG < 10 THEN
271 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
272 iex_debug_pub.logmessage ('create_checklist_strategy: ' || 'and l_return_status from the pvt ='||l_return_status);
273 END IF;
274 if (x_return_status <> 'S') then
275 RAISE FND_API.G_EXC_ERROR;
276 end if;
277
278 END LOOP;
279
280 IEX_DEBUG_PUB.LogMessage('Return status = ' || l_return_status);
281
282
283 l_strategy2_rec.strategy_id := p_StrategyID;
284 l_strategy2_rec.object_version_number := fStrategyVersionNumber;
285 l_strategy2_rec.checklist_strategy_id := l_strategy_rec.strategy_id;
286
287 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
288 iex_debug_pub.logmessage( debug_msg => '2. Update Main Strategy ', print_date => 'Y');
289 END IF;
290
291 IEX_DEBUG_PUB.LogMessage('2. Update Main Strategy ');
292
293 iex_strategy_pvt.update_strategy(
294 P_Api_Version_Number=>2.0,
295 p_commit => FND_API.G_FALSE,
296 P_Init_Msg_List =>FND_API.G_FALSE,
297 p_strategy_rec => l_strategy2_rec,
298 x_return_status=>l_return_status,
299 x_msg_count=>l_msg_count,
300 x_msg_data=>l_msg_data,
301 xo_object_version_number => l_object_version_number
302 );
303 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
304 iex_debug_pub.logmessage( debug_msg => 'Return status = ' || l_return_status, print_date => 'Y');
305 END IF;
306 IEX_DEBUG_PUB.LogMessage('Return status = ' || l_return_status);
307 if (x_return_status <> 'S') then
308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
309 end if;
310
311 -- Standard check for p_commit
312 IF FND_API.to_Boolean(p_commit) THEN
313 COMMIT WORK;
314 END IF;
315
316 -- Standard call to get message count and if count is 1, get message info.
317 FND_MSG_PUB.Count_And_Get
318 ( p_count => x_msg_count,
319 p_data => x_msg_data
320 );
321
322 IEX_DEBUG_PUB.LogMessage('Delinquency cursor ends' );
323
324
325 EXCEPTION
326
327 WHEN FND_API.G_EXC_ERROR THEN
328 x_return_status := FND_API.G_RET_STS_ERROR;
329 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
330
331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
333 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
334
335 WHEN OTHERS THEN
336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
337 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
338 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
339 END IF;
340 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
341
342 END create_checklist_strategy;
343
344 END IEX_STRY_CHECKLIST_PUB;