[Home] [Help]
PACKAGE BODY: APPS.IEX_WRITEOFFOBJ_PUB
Source
1 PACKAGE BODY IEX_WRITEOFFOBJ_PUB as
2 /* $Header: iexpwrob.pls 120.10.12010000.3 2009/08/14 12:39:08 ehuh ship $ */
3 -- Start of Comments
4 -- Package name :IEX_WRITEOFFOBJ_PUB
5 -- Purpose : 1) Procedure to populate OKL_TRX_ADJST_B and OKL_TXL_ADJSTS_LNS_B
6 -- : 2) Procedure to call OKL_WRAPPER OKL_CREATE_ADJ_PVT
7 -- : to create an adjustment
8 -- : 3) Procedure to check approval before creating a writeoff.
9 -- : 4) Procedure to update iex_writeoff_objects after creating
10
11 G_FILE_NAME VARCHAR2(12) ;
12 PG_DEBUG NUMBER ;
13 wf_yes varchar2(1) ;
14 wf_no varchar2(1) ;
15
16 PROCEDURE AddfailMsg ( p_object IN VARCHAR2, p_operation IN VARCHAR2 ) IS
17
18 BEGIN
19 fnd_message.set_name('IEX', 'IEX_FAILED_OPERATION');
20 fnd_message.set_token('OBJECT', p_object);
21 fnd_message.set_token('UPDATE', p_operation);
22 fnd_msg_pub.add;
23
24 END AddfailMsg;
25
26 PROCEDURE Get_Messages ( p_message_count IN NUMBER, x_message OUT nocopy VARCHAR2) IS
27
28 l_msg_list VARCHAR2(32627) := '';
29 l_temp_msg VARCHAR2(32627);
30 l_appl_short_name VARCHAR2(50) ;
31 l_message_name VARCHAR2(50) ;
32 l_id NUMBER;
33 l_message_num NUMBER;
34 l_msg_count NUMBER;
35 l_msg_data VARCHAR2(32627);
36 l_new_line VARCHAR2(10) := FND_GLOBAL.NEWLINE;
37
38 Cursor Get_Appl_Id (x_short_name VARCHAR2) IS
39 SELECT application_id
40 FROM fnd_application_vl
41 WHERE application_short_name = x_short_name;
42
43 Cursor Get_Message_Num (x_msg VARCHAR2, x_id NUMBER, x_lang_id NUMBER) IS
44 SELECT msg.message_number
45 FROM fnd_new_messages msg, fnd_languages_vl lng
46 WHERE msg.message_name = x_msg
47 and msg.application_id = x_id
48 and lng.LANGUAGE_CODE = msg.language_code
49 and lng.language_id = x_lang_id;
50
51 BEGIN
52 iex_debug_pub.logmessage ( 'in get message routine');
53 FOR l_count in 1..p_message_count LOOP
54
55 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_true);
56 fnd_message.parse_encoded(l_temp_msg, l_appl_short_name, l_message_name);
57 OPEN Get_Appl_Id (l_appl_short_name);
58 FETCH Get_Appl_Id into l_id;
59 CLOSE Get_Appl_Id;
60
61 l_message_num := NULL;
62
63 IF l_id is not NULL THEN
64 OPEN Get_Message_Num (l_message_name, l_id, to_number(NVL(FND_PROFILE.Value('LANGUAGE'), '0')));
65 FETCH Get_Message_Num into l_message_num;
66 CLOSE Get_Message_Num;
67 END IF;
68
69 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_previous, fnd_api.g_true);
70
71 IF NVL(l_message_num, 0) <> 0 then
72 l_temp_msg := 'APP-' || to_char(l_message_num) || ': ';
73 ELSE
74 l_temp_msg := NULL;
75 END IF;
76
77 IF l_count = 1 then
78 l_msg_list := l_msg_list || l_temp_msg || fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
79 ELSE
80 l_msg_list := l_msg_list || l_temp_msg || fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
81 END IF;
82
83 l_msg_list := l_msg_list || '';
84 iex_debug_pub.logmessage ( 'l_msg_lis'||l_msg_list);
85 END LOOP;
86
87 x_message := l_msg_list;
88 iex_debug_pub.logmessage ( ' x_message ' ||x_message);
89 EXCEPTION
90 WHEN OTHERS THEN
91 x_message:=l_msg_list;
92 END Get_Messages;
93
94
95 PROCEDURE Get_Messages1 ( p_message_count IN NUMBER, x_message OUT nocopy VARCHAR2) IS
96
97 l_msg_list VARCHAR2(32627) := '';
98 l_temp_msg VARCHAR2(32627);
99 l_appl_short_name VARCHAR2(50) ;
100 l_message_name VARCHAR2(50) ;
101 l_id NUMBER;
102 l_message_num NUMBER;
103 l_msg_count NUMBER;
104 l_msg_data VARCHAR2(32627);
105 l_new_line VARCHAR2(10) := FND_GLOBAL.NEWLINE;
106
107 BEGIN
108 iex_debug_pub.logmessage ( 'in get message routine');
109 FOR i IN 1..p_message_count
110 LOOP
111 fnd_msg_pub.get(p_data => l_msg_data,
112 p_msg_index_out => l_msg_count,
113 p_encoded => fnd_api.g_false,
114 p_msg_index => fnd_msg_pub.g_next);
115 IF i = 1 THEN
116 l_msg_list := l_msg_data;
117 ELSE
118 l_msg_list := l_msg_list || l_new_line || l_msg_data;
119 END IF;
120 END LOOP;
121 x_message:=l_msg_list;
122 iex_debug_pub.logmessage ( ' x_message ' ||x_message);
123 EXCEPTION
124 WHEN OTHERS THEN
125 x_message:=l_msg_list;
126 END Get_Messages1;
127
128 PROCEDURE invoke_writeoff_wf(
129 p_WRITEOFF_ID IN NUMBER
130 ,p_writeoff_type IN VARCHAR2
131 ,p_request_id IN NUMBER
132 ,p_object_id IN VARCHAR2
133 ,x_return_status OUT NOCOPY VARCHAR2
134 ,x_msg_count OUT NOCOPY NUMBER
135 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
136
137 l_parameter_list wf_parameter_list_t;
138 l_key VARCHAR2(240);
139 l_seq NUMBER;
140 l_event_name varchar2(240) ;
141 l_init_msg_list VARCHAR2(1);
142 l_return_status VARCHAR2(1);
143 l_msg_count NUMBER ;
144 l_msg_data VARCHAR2(32627);
145 l_message VARCHAR2(32627);
146 l_api_name VARCHAR2(50) ;
147 l_api_name_full VARCHAR2(150);
148 l_notification_agent varchar2(100);
149 l_case_owner varchar2(100);
150 l_case_number varchar2(240);
151
152 TYPE c_getinvoiceCurTyp IS REF CURSOR;
153 c_getinvoices c_getinvoiceCurTyp;
154
155 l_object_id VARCHAR2(32627) ;
156 l_currency_code VARCHAR2(10);
157 l_amount NUMBER;
158 l_user_id number := FND_GLOBAL.User_Id;
159
160 cursor c_get_agent(p_id number) is
161 -- bug 8752313
162 -- SELECT name from wf_roles
163 -- where orig_system = 'FND_RESP'
164 -- and orig_system_id = '23366';
165 select b.user_name
166 from JTF_RS_RESOURCE_EXTNS a, JTF_RS_RESOURCE_EXTNS b
167 where b.source_id = nvl(a.source_mgr_id,a.source_id)
168 and a.user_name = (select fu.user_name from fnd_user fu where fu.user_id = p_id);
169
170 cursor c_get_ar_invoices(p_object_id IN VARCHAR2) is
171 select to_char(payment_schedule_id), invoice_currency_code, amount_due_remaining
172 from ar_payment_schedules
173 where customer_trx_id = p_object_id;
174 --where payment_schedule_id =p_object_id;
175
176 cursor c_get_cont_invoices(p_object_id IN VARCHAR2,p_request_id IN NUMBER) is
177 select chr.contract_number, sum(iwob.adjustment_amount), chr.currency_code
178 from iex_writeoff_objects iwob, okc_k_headers_b chr
179 where chr.id = p_object_id
180 and iwob.contract_id = chr.id
181 and iwob.request_id = p_request_id
182 group by chr.contract_number,chr.currency_code ;
183
184 BEGIN
185
186 SAVEPOINT invoke_writeoff_wf;
187 l_event_name := 'oracle.apps.iex.wrf.approvewriteoff';
188 l_api_name := 'invoke_writeoff_wf';
189 l_api_name_full := g_pkg_name || '.'||l_api_name;
190 x_return_status := FND_API.G_RET_STS_SUCCESS;
191
192 AddfailMsg(p_object => 'BEFORE CALLING WORKFLOW ', p_operation => 'CREATE');
193
194 l_key := l_event_name ||'-'||p_request_id;
195
196 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
197 iex_debug_pub.logmessage ('Event Key ' ||l_key ||'writeoff_type' ||p_writeoff_type||
198 'object_id'||p_object_id|| 'request_id'||p_request_id);
199 END IF;
200
201 l_amount :=0;
202
203 if p_writeoff_type ='AR_INVOICE' THEN
204 OPEN c_get_ar_invoices(p_object_id);
205 FETCH c_get_ar_invoices INTO l_object_id, l_currency_code, l_amount;
206 CLOSE c_get_ar_invoices ;
207 else
208 OPEN c_get_cont_invoices(p_object_id,p_request_id);
209 FETCH c_get_cont_invoices INTO l_object_id, l_amount, l_currency_code;
210 CLOSE c_get_cont_invoices;
211
212 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
213 iex_debug_pub.logmessage ('in contracts' ||'writeoff_type' ||p_writeoff_type||
214 'object_id'||p_object_id|| 'request_id'||p_request_id);
215 end if;
216 end if;
217
218 begin
219 iex_debug_pub.logmessage ('Get notify Agent User ID.....' ||l_user_id);
220 OPEN c_get_agent(l_user_id) ;
221 FETCH c_get_agent INTO l_notification_agent;
222 if c_get_agent%NOTFOUND then null; end if;
223 CLOSE c_get_Agent;
224
225 exception
226 when others then null;
227 end;
228
229 IF l_notification_agent IS NULL THEN l_notification_agent := 'SYSADMIN';
230 END IF;
231
232
233 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
234 iex_debug_pub.logmessage ('l_case_number ' ||l_case_number || 'l_case_owner' || l_case_owner
235 ||'<-----L_AMOUNT----->' ||l_amount);
236 END IF;
237
238 wf_event.AddParameterToList('AMOUNT', to_char(l_amount), l_parameter_list);
239 --wf_event.AddParameterToList('CASE_OWNER', l_case_owner, l_parameter_list);
240 --wf_event.AddParameterToList('CASE_NUMBER', l_case_number, l_parameter_list);
241 wf_event.AddParameterToList('NOTIFY_AGENT', l_notification_agent, l_parameter_list);
242 wf_event.AddParameterToList('REQUEST_ID', p_request_ID, l_parameter_list);
243 wf_event.AddParameterToList('WRITEOFF_ID', p_writeoff_ID, l_parameter_list);
244 wf_event.AddParameterToList('OBJECT_ID', l_object_ID, l_parameter_list);
245 wf_event.AddParameterToList('WRITEOFF_TYPE', p_writeoff_type, l_parameter_list);
246 wf_event.AddParameterToList('CURRENCY_CODE', l_currency_code, l_parameter_list);
247
248 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
249 iex_debug_pub.logmessage ('before launching workflow');
250 END IF;
251
252 wf_event.raise(p_event_name => l_event_name
253 ,p_event_key => l_key
254 ,p_parameters => l_parameter_list);
255 COMMIT ;
256 l_parameter_list.DELETE;
257
258 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
259 iex_debug_pub.logmessage ('Successfully launched writeoff workflow');
260 END IF;
261
262 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
263 EXCEPTION
264 WHEN OTHERS THEN
265 ROLLBACK TO invoke_writeoff_wf;
266 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
267 x_msg_count := l_msg_count ;
268 x_msg_data := l_msg_data ;
269 Fnd_Msg_Pub.ADD_EXC_MSG('IEX_WRITEOFFOBJ_PUB','invoke_writeoff_wf');
270 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count
271 ,p_data => x_msg_data);
272 End invoke_writeoff_wf;
273
274
275 PROCEDURE invoke_asset_mgr_wf(
276 p_object_id IN varchar2
277 ,p_request_id IN varchar2
278 ,x_return_status OUT NOCOPY VARCHAR2
279 ,x_msg_count OUT NOCOPY NUMBER
280 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
281
282 l_parameter_list wf_parameter_list_t;
283 l_key VARCHAR2(240);
284 l_seq NUMBER;
285 l_event_name varchar2(240) ;
286 l_init_msg_list VARCHAR2(1);
287 l_return_status VARCHAR2(1);
288 l_msg_count NUMBER ;
289 l_msg_data VARCHAR2(32627);
290 l_message VARCHAR2(32627);
291 l_api_name VARCHAR2(50) ;
292 l_api_name_full VARCHAR2(150);
293 l_notification_agent varchar2(100);
294 l_case_owner varchar2(100);
295 l_contract_number varchar2(120);
296 l_user_id number := FND_GLOBAL.User_Id;
297
298 cursor c_get_agent is
299 SELECT name from wf_roles
300 where orig_system = 'FND_RESP'
301 and orig_system_id = '23449';
302
303 cursor c_get_case_owner(p_object_id IN VARCHAR2) IS
304 select rs.source_name, chr.contract_number
305 from iex_cases_all_b icas,
306 jtf_rs_resource_extns rs,
307 iex_case_objects ico,
308 okc_k_headers_b chr
309 where ico.cas_id = icas.cas_id
310 and icas.owner_resource_id = rs.resource_id(+)
311 and chr.id = ico.object_id
312 and chr.id = to_number(p_object_id);
313
314 BEGIN
315 SAVEPOINT invoke_asset_mgr_wf;
316 l_event_name := 'oracle.apps.iex.wrf.terminatenotification';
317 l_api_name := 'invoke_asset_mgr_wf';
318 l_api_name_full := g_pkg_name || '.'||l_api_name;
319 x_return_status := FND_API.G_RET_STS_SUCCESS;
320
321 AddfailMsg(p_object => 'BEFORE CALLING WORKFLOW ', p_operation => 'CREATE');
322
323 l_key := l_event_name ||'-'||p_request_id;
324
325 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
326 iex_debug_pub.logmessage ('Event Key ' ||l_key);
327 END IF;
328
329 OPEN c_get_case_owner(p_object_id);
330 FETCH c_get_case_owner INTO l_case_owner, l_contract_number;
331 CLOSE c_get_case_owner;
332
333 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
334 iex_debug_pub.logmessage ('l_contract_number ' ||l_contract_number
335 || 'l_case_owner' || l_case_owner);
336 END IF;
337
338 begin
339 OPEN c_get_agent;
340 FETCH c_get_agent INTO l_notification_agent;
341 if c_get_agent%NOTFOUND then null; end if;
342 CLOSE c_get_Agent;
343
344 exception
345 when others then null;
346 end;
347
348 IF l_notification_agent IS NULL THEN
349 l_notification_agent := 'SYSADMIN';
350 END IF;
351
352 wf_event.AddParameterToList('NOTIFY_AGENT', l_notification_agent, l_parameter_list);
353 wf_event.AddParameterToList('CASE_OWNER', l_case_owner, l_parameter_list);
354 wf_event.AddParameterToList('CONTRACT_NUMBER', l_contract_number, l_parameter_list);
355
356 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
357 iex_debug_pub.logmessage ('before launching workflow');
358 END IF;
359
360 wf_event.raise(p_event_name => l_event_name
361 ,p_event_key => l_key
362 ,p_parameters => l_parameter_list);
363
364 COMMIT ;
365 l_parameter_list.DELETE;
366
367 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
368 iex_debug_pub.logmessage ('Successfully launched asset mgr workflow');
369 END IF;
370
371 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
372 EXCEPTION
373 WHEN OTHERS THEN
374 ROLLBACK TO invoke_asset_mgr_wf;
375 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
376 x_msg_count := l_msg_count ;
377 x_msg_data := l_msg_data ;
378 Fnd_Msg_Pub.ADD_EXC_MSG('IEX_WRITEOFFOBJ_PUB','invoke_asset_mgr_wf');
379 Fnd_Msg_Pub.count_and_get(p_count => x_msg_count
380 ,p_data => x_msg_data);
381 End invoke_asset_mgr_wf;
382
383
384 PROCEDURE create_writeoffs(
385 P_Api_Version_Number IN NUMBER
386 ,p_init_msg_list IN VARCHAR2
387 ,p_commit IN VARCHAR2
388 ,p_writeoff_object_rec IN writeoff_obj_rec_type := g_miss_writeoff_obj_rec_type
389 ,p_writeoff_type IN VARCHAR2
390 ,p_object_id IN VARCHAR2
391 ,p_invoice_line_id IN NUMBER
392 ,x_return_status OUT NOCOPY VARCHAR2
393 ,x_msg_count OUT NOCOPY NUMBER
394 ,x_msg_data OUT NOCOPY VARCHAR2
395 ,x_adjustment_id OUT NOCOPY NUMBER) IS
396
397 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_WRITEOFFS';
398 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.' || l_api_name;
399 l_api_version_number CONSTANT NUMBER := 2.0;
400 l_writeoff_object_id IEX_writeoff_objects.writeoff_object_id%TYPE;
401 x_adj_id IEX_writeoff_objects.receviables_adjustment_id%TYPE;
402 l_return_status VARCHAR2(1);
403 l_msg_count NUMBER ;
404 l_msg_data VARCHAR2(32627);
405 l_message VARCHAR2(32627);
406
407 l_object_version_number IEX_writeoff_objects.writeoff_object_id%TYPE;
408 l_adjv_rec adjv_rec_type;
409 x_adjv_rec adjv_rec_type;
410 l_ajlv_rec ajlv_rec_type;
411 x_ajlv_rec ajlv_rec_type;
412 l_writeoff_obj_rec writeoff_obj_rec_type ;
413 l_writeoff_rec writeoff_rec_type ;
414 l_code_combination_id ar_adjustments.code_combination_ID%TYPE;
415 l_contract_number okc_k_headers_b.contract_number%TYPE;
416 l_product okl_products_v.name%TYPE;
417 l_stream okl_strm_type_b.code%TYPE;
418
419 cursor c_get_obj_ver(p_WRITEOFF_ID IN NUMBER) is
420 select object_version_number
421 from iex_writeoffs
422 where WRITEOFF_ID =p_WRITEOFF_ID;
423
424 BEGIN
425
426 l_writeoff_obj_rec := p_writeoff_object_rec;
427 SAVEPOINT CREATE_WRITEOFFS;
428
429 IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version_number, l_api_name, G_PKG_NAME) then
430 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
431 END IF;
432
433 FND_MSG_PUB.initialize;
434 x_return_status := FND_API.G_RET_STS_SUCCESS;
435
436 l_writeoff_obj_rec.writeoff_status := 'W';
437 l_writeoff_obj_rec.writeoff_type := p_writeoff_type;
438 l_writeoff_obj_rec.writeoff_type_id := p_object_id;
439
440 IEX_WRITEOFF_OBJECTS_PUB.create_writeoff_objects(
441 P_Api_Version_Number =>p_Api_Version_Number
442 ,P_Init_Msg_List =>P_Init_Msg_List
443 ,P_Commit =>p_commit
444 ,P_writeoff_obj_rec =>l_writeoff_obj_rec
445 ,X_writeoff_object_id =>l_writeoff_object_id
446 ,x_return_status =>l_return_status
447 ,x_msg_count =>l_msg_count
448 ,x_msg_data =>l_msg_data);
449
450 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
451 iex_debug_pub.logmessage ('writeoff creation object ID '||
452 l_writeoff_object_id || 'status ' ||l_return_status);
453 END IF;
454
455 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
456 IEX_WRITEOFFOBJ_PUB.Get_Messages1 (l_msg_count,l_message);
457 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
458 iex_debug_pub.logmessage('Error' ||l_message);
459 END IF;
460 raise FND_API.G_EXC_ERROR;
461
462 ELSE
463 FND_MSG_PUB.initialize;
464 END IF;
465
466 AddfailMsg( p_object => 'Writeoffs ', p_operation => 'UPDATE' );
467
468 OPEN c_get_obj_ver(l_writeoff_obj_rec.writeoff_id);
469 FETCH c_get_obj_ver INTO l_writeoff_rec.object_version_number;
470 CLOSE c_get_obj_ver;
471
472 l_writeoff_rec.disposition_code := 'W';
473 --l_writeoff_rec.disposition_date := SYSDATE;
474 l_writeoff_rec.writeoff_id:=l_writeoff_obj_rec.writeoff_id;
475
476 IEX_WRITEOFFS_PVT.Update_writeoffs(
477 P_Api_Version_Number =>l_api_version_number
478 ,P_Init_Msg_List =>'F'
479 ,P_Commit =>'T'
480 ,P_writeoffs_Rec =>l_writeoff_rec
481 ,x_return_status => l_return_status
482 ,x_msg_count => l_msg_count
483 ,x_msg_data => l_msg_data
484 ,xo_object_version_number =>l_object_version_number
485 );
486
487 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
488 IEX_WRITEOFFOBJ_PUB.Get_Messages1 (l_msg_count,l_message);
489 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
490 iex_debug_pub.logmessage('Error' ||l_message);
491 END IF;
492 raise FND_API.G_EXC_ERROR;
493 ELSE
494 FND_MSG_PUB.initialize;
495 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
496 iex_debug_pub.logmessage ('obj of iex_writeoff' ||l_object_version_number
497 ||'l_writeoff_rec.writeoff_id' || l_writeoff_obj_rec.writeoff_id);
498 end if;
499 END IF;
500
501 IF FND_API.to_Boolean( p_commit ) THEN
502 COMMIT WORK;
503 END IF;
504
505 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
506
507 EXCEPTION
508 WHEN FND_API.G_EXC_ERROR THEN
509 ROLLBACK TO CREATE_WRITEOFFS;
510 x_return_status := FND_API.G_RET_STS_ERROR ;
511 x_msg_count := l_msg_count ;
512 x_msg_data := l_msg_data ;
513 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
514 p_data => x_msg_data);
515 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
516 ROLLBACK TO CREATE_WRITEOFFS;
517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
518 x_msg_count := l_msg_count ;
519 x_msg_data := l_msg_data ;
520 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
521 p_data => x_msg_data);
522 WHEN OTHERS THEN
523 ROLLBACK TO CREATE_WRITEOFFS;
524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
525 x_msg_count := l_msg_count ;
526 x_msg_data := l_msg_data ;
527 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,l_api_name);
528 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
529 p_data => x_msg_data);
530
531 END create_writeoffs;
532
533 /** Intialize record defintion to avoid direct server side access of variables
534 */
535 FUNCTION INIT_WRITEOFFOBJ_REC
536 RETURN IEX_WRITEOFFOBJ_PUB.writeoff_obj_rec_type
537 IS
538 l_return_rec IEX_WRITEOFFOBJ_PUB.writeoff_obj_rec_type ;
539 BEGIN
540 RETURN l_return_rec;
541 END INIT_WRITEOFFOBJ_REC;
542
543
544 /**
545 called from the workflow to approve writeoffs
546 **/
547 PROCEDURE approve_writeoffs (itemtype in varchar2,
548 itemkey in varchar2,
549 actid in number,
550 funcmode in varchar2,
551 result out nocopy varchar2) IS
552
553 l_api_version NUMBER := 1;
554 l_init_msg_list VARCHAR2(1);
555 l_return_status VARCHAR2(1);
556 l_msg_count NUMBER ;
557 l_msg_data VARCHAR2(32627);
558 l_message VARCHAR2(32627);
559
560 l_WRITEOFF_ID VARCHAR2(32627);
561 l_REQUEST_ID VARCHAR2(32627);
562 l_OBJECT_ID VARCHAR2(32627);
563 l_OBJECT_TYPE VARCHAR2(100);
564
565 cursor c_get_obj_ver(p_WRITEOFF_ID IN NUMBER) is
566 select object_version_number,writeoff_reason
567 from iex_writeoffs
568 where WRITEOFF_ID =p_WRITEOFF_ID;
569
570
571 --bug 8258156 add ar_payment_schedules_all to get org id
572 cursor c_get_writeoff_objects(p_WRITEOFF_ID IN NUMBER, p_request_id IN NUMBER) is
573 select writeoff_object_id, object_version_number, transaction_id,
574 adjustment_amount, contract_id, org_id
575 from iex_writeoff_objects io,ar_payment_schedules_all ap
576 where WRITEOFF_ID = p_WRITEOFF_ID
577 and writeoff_status ='W'
578 and transaction_id = payment_schedule_id(+)
579 and io.request_id = p_request_id;
580
581 l_api_name CONSTANT VARCHAR2(50) := 'APPROVE_WRITEOFFS';
582 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.' || l_api_name;
583 l_api_version_number CONSTANT NUMBER := 2.0;
584 l_writeoff_object_id IEX_writeoff_objects.writeoff_object_id%TYPE;
585 x_adj_id IEX_writeoff_objects.receviables_adjustment_id%TYPE;
586 l_object_version_number IEX_writeoff_objects.object_version_number%TYPE;
587
588 x_adjv_rec adjv_rec_type;
589 l_ajlv_rec ajlv_rec_type;
590 x_ajlv_rec ajlv_rec_type;
591 l_writeoff_obj_rec writeoff_obj_rec_type ;
592 l_writeoff_rec writeoff_rec_type ;
593 l_adj_reason_code iex_writeoffs.writeoff_reason%TYPE;
594 l_ctr NUMBER;
595
596 l_code_combination_id ar_adjustments.code_combination_ID%TYPE;
597 l_contract_number okc_k_headers_b.contract_number%TYPE;
598
599 BEGIN
600
601 if funcmode <> 'RUN' then
602 result := wf_engine.eng_null;
603 return;
604 end if;
605
606 l_WRITEOFF_ID:= wf_engine.GetItemAttrText(itemtype => itemtype,
607 itemkey => itemkey,
608 aname => 'WRITEOFF_ID');
609 l_REQUEST_ID:= wf_engine.GetItemAttrText(itemtype => itemtype,
610 itemkey => itemkey,
611 aname => 'REQUEST_ID');
612 l_object_id := wf_engine.GetItemAttrText(itemtype => itemtype,
613 itemkey => itemkey,
614 aname => 'OBJECT_ID');
615 l_object_type := wf_engine.GetItemAttrText(itemtype => itemtype,
616 itemkey => itemkey,
617 aname => 'WRITEOFF_TYPE');
618 /** 1) insert into okl_trx_ar_adjst_b and tl
619 ** 2) insert into okl_txl_adjst_lns_b and tl
620 ** 3) call okl_adjst_create_pvt to create adjsutment
621 ** 4) update iex_writeoff_objects with the adjustment ID
622 ** 5) update iex_writeoffs disposition code to APPROVED
623 ** 6) if writeoff type is CONTRACT then send asset mgr a termination notification */
624
625 OPEN c_get_obj_ver(l_WRITEOFF_ID);
626 FETCH c_get_obj_ver INTO l_writeoff_rec.object_version_number, l_adj_reason_code;
627 CLOSE c_get_obj_ver;
628
629 -- do not process if objects are part of a different
630 --transaction . this will not happen since the views already remove
631 --the transaction if it is part of any other request
632 l_ctr :=0;
633
634 For i in c_get_writeoff_objects (l_writeoff_id,L_REQUEST_ID) LOOP
635
636 l_ctr := 1;
637 l_writeoff_obj_rec.WRITEOFF_OBJECT_ID :=i.writeoff_object_id;
638 l_writeoff_obj_rec.OBJECT_VERSION_NUMBER :=i.object_version_number;
639
640 --START jsanju bug 4637174 replace 3 calls with one call
641 /*
642 --populate the record type to be passed(l_adjv_rec)
643 --l_adjv_rec.adjustment_reason_code:= l_adj_reason_code;
644 l_adjv_rec.adjustment_reason_code:= 'WRITE OFF';
645 l_adjv_rec.apply_date :=sysdate;
646 l_adjv_rec.gl_date := sysdate;
647 l_adjv_rec.trx_status_code :='WORKING';
648
649 -- call okl_trx_ar_adjsts_pub.insert_trx_ar_adjsts
650 --set error message,so this will be prefixed before the
651 --actual message, so it makes more sense than displaying an
652 -- OKL message.
653
654 AddfailMsg(
655 p_object => 'RECORD IN OKL_TRX_AR_ADJST_B ',
656 p_operation => 'CREATE' );
657
658 okl_trx_ar_adjsts_pub.insert_trx_ar_adjsts(
659 p_api_version =>1.0
660 ,p_init_msg_list =>'F'
661 ,x_return_status =>l_return_status
662 ,x_msg_count =>l_msg_count
663 ,x_msg_data =>l_msg_data
664 ,p_adjv_rec =>l_adjv_rec
665 ,x_adjv_rec =>x_adjv_rec);
666
667 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
668 iex_debug_pub.logmessage ('okl_trx_ar_adjsts status ' ||l_return_status);
669 iex_debug_pub.logmessage ('okl_trx_ar_adjsts adj_id ' || x_adjv_rec.id);
670 END IF;
671
672 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
673 IEX_WRITEOFFOBJ_PUB.Get_Messages1 (l_msg_count,l_message);
674 -- IF PG_DEBUG <11 THEN
675 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
676 iex_debug_pub.logmessage('Error' ||l_message);
677 END IF;
678 wf_engine.SetItemAttrText(itemtype => itemtype,
679 itemkey => itemkey,
680 aname => 'ERROR_MESSAGE',
681 avalue => l_message);
682 --raise FND_API.G_EXC_ERROR;
683 result := wf_engine.eng_completed ||':'||wf_no;
684 return;
685
686 ELSE
687 FND_MSG_PUB.initialize;
688 END IF;
689
690 --else insert of okl_trx_ar_adjsts_b is successful we can go to
691 --the third step of populating the okl_txl_adjsts_lns_b tables
692
693 --populate the record type to be passed(l_ajlv_rec)
694
695 -- call okl_txl_adjsts_lns_pub.insert_txl_adjsts_lns
696
697 l_ajlv_rec.adj_id :=x_adjv_rec.id;
698 l_ajlv_rec.psl_id :=i.transaction_id;
699 l_ajlv_rec.amount :=i.adjustment_amount;
700
701 --not sure what to pass????
702 -- l_ajlv_rec.CODE_COMBINATION_ID := 17001;
703
704 --08/13/03
705 --check for code combination before creating
706 --writeoff objects
707 OPEN c_get_code_comb(i.transaction_id);
708 FETCH c_get_code_comb INTO l_CODE_COMBINATION_ID;
709 CLOSE c_get_code_comb;
710
711 IF l_CODE_COMBINATION_ID IS NULL THEN
712 -- IF PG_DEBUG < 11 THEN
713 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
714 iex_debug_pub.logmessage ('Error in retreiving Code combination ');
715 END IF;
716
717 select contract_number into l_contract_number
718 from okc_k_headers_b
719 where id=i.contract_id;
720
721 fnd_message.set_name('IEX', 'IEX_WRITEOFFOBJ_CODE_COMB');
722 fnd_message.set_token('CONTRACT_NUMBER', l_contract_number);
723 fnd_msg_pub.add;
724 IEX_WRITEOFFOBJ_PUB.Get_Messages1 (l_msg_count,l_message);
725 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
726 iex_debug_pub.logmessage('Error' ||l_message);
727 END IF;
728 wf_engine.SetItemAttrText(itemtype => itemtype,
729 itemkey => itemkey,
730 aname => 'ERROR_MESSAGE',
731 avalue => l_message);
732 result := wf_engine.eng_completed ||':'||wf_no;
733 return;
734
735 END IF;
736
737 l_ajlv_rec.CODE_COMBINATION_ID := l_CODE_COMBINATION_ID;
738 l_code_combination_id := null;
739
740 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
741 iex_debug_pub.logmessage ('Code combination' ||l_ajlv_rec.CODE_COMBINATION_ID );
742 END IF;
743
744 --set error message,so this will be prefixed before the
745 --actual message, so it makes more sense than displaying an
746 -- OKL message.
747 AddfailMsg( p_object => 'RECORD IN OKL_TXL_ADJSTS_LNS_B ',
748 p_operation => 'CREATE' );
749
750 okl_txl_adjsts_lns_pub.insert_txl_adjsts_lns(
751 p_api_version =>1.0
752 ,p_init_msg_list =>'F'
753 ,x_return_status =>l_return_status
754 ,x_msg_count =>l_msg_count
755 ,x_msg_data =>l_msg_data
756 ,p_ajlv_rec =>l_ajlv_rec
757 ,x_ajlv_rec =>x_ajlv_rec);
758
759
760 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
761 IEX_WRITEOFFOBJ_PUB.Get_Messages1 (l_msg_count,l_message);
762 -- IF PG_DEBUG <11 THEN
763 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
764 iex_debug_pub.logmessage('Error' ||l_message);
765 END IF;
766 wf_engine.SetItemAttrText(itemtype => itemtype,
767 itemkey => itemkey,
768 aname => 'ERROR_MESSAGE',
769 avalue => l_message);
770 --raise FND_API.G_EXC_ERROR;
771 result := wf_engine.eng_completed ||':'||wf_no;
772 return;
773 ELSE
774 -- IF PG_DEBUG <11 THEN
775 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
776 iex_debug_pub.logmessage ('okl_txl_adjsts_lns status '
777 ||l_return_status || ' Id ' ||
778 x_ajlv_rec.id);
779 END IF;
780 FND_MSG_PUB.initialize;
781 END IF;
782
783 --else insert of okl_txl_adjsts_lns_b is successful we can go to
784 --the fourth step of calling okl_create_adjst_pvt
785 */
786 --END jsanju for bug 4637174 replace 3 calls with one call.
787
788 --set error message,so this will be prefixed before the
789 --actual message, so it makes more sense than displaying an
790 -- OKL message.
791
792 AddfailMsg( p_object => 'Adjustments ', p_operation => 'CREATE' );
793
794 -- IF PG_DEBUG <11 THEN
795 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
796 iex_debug_pub.logmessage ('Before calling Okl Api' ||'transaction id ' || i.transaction_id);
797 END IF;
798
799 -- Begin bug 8258156
800 -- mo_global.set_policy_context('S',7746);
801 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
802 iex_debug_pub.logmessage ('Before calling Okl Api Org ID = ' || i.org_id);
803 END IF;
804
805 mo_global.set_policy_context('S',i.org_id);
806 -- End bug 8258156
807
808 okl_create_adjst_pub.iex_create_adjustments_pub (
809 p_api_version => 1
810 ,p_init_msg_list => 'F'
811 ,p_commit_flag => 'F'
812 ,p_psl_id => i.transaction_id
813 ,p_chk_approval_limits => 'F'
814 ,x_new_adj_id => x_adj_id
815 ,x_return_status => l_return_status
816 ,x_msg_count => l_msg_count
817 ,x_msg_data => l_msg_data);
818
819 -- IF PG_DEBUG <11 THEN
820 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
821 iex_debug_pub.logmessage ('After calling Okl Api and status'||l_return_status);
822 END IF;
823
824 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
825 IEX_WRITEOFFOBJ_PUB.Get_Messages1 (l_msg_count,l_message);
826 -- IF PG_DEBUG <11 THEN
827 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
828 iex_debug_pub.logmessage('Error' ||l_message);
829 END IF;
830 wf_engine.SetItemAttrText(itemtype => itemtype,
831 itemkey => itemkey,
832 aname => 'ERROR_MESSAGE',
833 avalue => l_message);
834 --raise FND_API.G_EXC_ERROR;
835 result := wf_engine.eng_completed ||':'||wf_no;
836 return;
837 ELSE
838 FND_MSG_PUB.initialize;
839 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
840 IEx_debug_pub.logmessage ('After caling OKL Adj API and adj_id ' || x_adj_id);
841 end if;
842 END IF;
843
844 --else adjustment created and update iex_writeoff_objects
845 --with the adjustment ID
846 --populate the writeoff_obect rec
847
848 if x_adj_id IS NOT NULL then
849 l_writeoff_obj_rec.RECEVIABLES_ADJUSTMENT_ID :=x_adj_id;
850 l_writeoff_obj_rec.writeoff_status :='A';
851 else
852 -- IF PG_DEBUG < 11 THEN
853 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
854 iex_debug_pub.logmessage('Adjust ment ID is null ');
855 END IF;
856
857 AddfailMsg(p_object => 'Adjustments ',
858 p_operation => 'CREATE' );
859 --raise FND_API.G_EXC_ERROR;
860 result := wf_engine.eng_completed ||':'||wf_no;
861 return;
862 end if;
863
864 --update iex_writeoff_objects
865 --prefix this message before writeoff record is updated
866 AddfailMsg( p_object => 'Writeoff Object ',
867 p_operation => 'UPDATE' );
868
869 IEX_WRITEOFF_OBJECTS_PUB.update_writeoff_objects(
870 P_Api_Version_Number =>l_Api_Version_Number
871 ,P_Init_Msg_List =>'F'
872 ,P_Commit =>'F'
873 ,P_writeoff_obj_rec =>l_writeoff_obj_rec
874 ,xo_object_version_number =>l_object_version_number
875 ,x_return_status =>l_return_status
876 ,x_msg_count =>l_msg_count
877 ,x_msg_data =>l_msg_data);
878
879 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
880 IEX_WRITEOFFOBJ_PUB.Get_Messages1 (l_msg_count,l_message);
881 -- IF PG_DEBUG <11 THEN
882 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
883 iex_debug_pub.logmessage('Error' ||l_message);
884 END IF;
885 wf_engine.SetItemAttrText(itemtype => itemtype,
886 itemkey => itemkey,
887 aname => 'ERROR_MESSAGE',
888 avalue => l_message);
889 --raise FND_API.G_EXC_ERROR;
890 result := wf_engine.eng_completed ||':'||wf_no;
891 return;
892 ELSE
893 FND_MSG_PUB.initialize;
894 -- IF PG_DEBUG <11 THEN
895 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
896 iex_debug_pub.logmessage('Finished updating iex_writeoffobjects');
897 END IF;
898
899 END IF;
900
901 END LOOP;
902
903 if l_ctr =1 THEN
904 --update iex_writeoffs
905 --prefix this message before writeoff record is updated
906 AddfailMsg( p_object => 'Writeoffs ', p_operation => 'UPDATE' );
907
908 -- update iex_writeoff's dispostion_code;
909 l_writeoff_rec.disposition_code := 'A';
910 -- l_writeoff_rec.disposition_date := SYSDATE;
911 l_writeoff_rec.writeoff_id := l_writeoff_id;
912
913 IEX_WRITEOFFS_PVT.Update_writeoffs(
914 P_Api_Version_Number => l_api_version_number
915 ,P_Init_Msg_List => 'F'
916 ,P_Commit => 'F'
917 ,P_writeoffs_Rec => l_writeoff_rec
918 ,x_return_status => l_return_status
919 ,x_msg_count => l_msg_count
920 ,x_msg_data => l_msg_data
921 ,xo_object_version_number =>l_object_version_number);
922
923 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
924 IEX_WRITEOFFOBJ_PUB.Get_Messages1 (l_msg_count,l_message);
925 -- IF PG_DEBUG <11 THEN
926 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
927 iex_debug_pub.logmessage('Error' ||l_message);
928 END IF;
929 wf_engine.SetItemAttrText(itemtype => itemtype,
930 itemkey => itemkey,
931 aname => 'ERROR_MESSAGE',
932 avalue => l_message);
933 --raise FND_API.G_EXC_ERROR;
934 result := wf_engine.eng_completed ||':'||wf_no;
935 return;
936 ELSE
937 FND_MSG_PUB.initialize;
938 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
939 iex_debug_pub.logmessage ('obj of iex_writeoff' ||l_object_version_number
940 ||'l_writeoff_rec.writeoff_id' || l_writeoff_obj_rec.writeoff_id||
941 'in approve writeoff procedure');
942 END IF;
943
944 END IF;
945
946 /** if writeoff type is CONTRACT then send asset mgr a
947 termination notification
948 */
949 If l_object_type ='CONTRACT' THEN
950 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
951 iex_debug_pub.logmessage('l_object_id ' || l_object_id|| 'l_request_id '|| l_request_id );
952 end if;
953 -- iex_test_pub.logmessage('l_object_id ' || l_object_id||
954 -- 'l_request_id '|| l_request_id );
955 invoke_asset_mgr_wf(p_object_id => l_object_id
956 ,p_request_id => l_request_id
957 ,x_return_status => l_return_status
958 ,x_msg_count => l_msg_count
959 ,x_msg_data => l_msg_data );
960
961 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
962 Get_Messages1 (l_msg_count,l_message);
963 -- IF PG_DEBUG < 11 THEN
964 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
965 iex_debug_pub.logmessage('Error after calling asset mgr WF' ||l_message);
966 END IF;
967
968 wf_engine.SetItemAttrText(itemtype => itemtype,
969 itemkey => itemkey,
970 aname => 'ERROR_MESSAGE',
971 avalue => l_message);
972 --raise FND_API.G_EXC_ERROR;
973 result := wf_engine.eng_completed ||':'||wf_no;
974 return;
975 ELSE
976 FND_MSG_PUB.initialize;
977 END IF;
978 END IF; --object_type
979 END if ;--if l_ctr;
980
981 result := wf_engine.eng_completed ||':'||wf_yes;
982
983 EXCEPTION
984 WHEN FND_API.G_EXC_ERROR THEN
985 --resultout := wf_engine.eng_completed ||':'||wf_no;
986 -- IF PG_DEBUG < 11 THEN
987 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
988 iex_debug_pub.logmessage('Error ' ||l_message);
989 END IF;
990
991 wf_core.context('IEX_WRITEOFFOBJ_PUB',
992 'approve_writeoffs',
993 itemtype,
994 itemkey,
995 to_char(actid),
996 funcmode);
997 raise;
998
999 when others then
1000 --resultout := wf_engine.eng_completed ||':'||wf_no;
1001 -- IF PG_DEBUG < 11 THEN
1002 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1003 iex_debug_pub.logmessage('Error WHEN OTHERE' ||sqlerrm);
1004 END IF;
1005 wf_core.context('IEX_WRITEOFFOBJ_PUB',
1006 'approve_writeoffs',
1007 itemtype,
1008 itemkey,
1009 to_char(actid),
1010 funcmode);
1011 raise;
1012
1013 END approve_writeoffs;
1014
1015 /**
1016 called from the workflow to reject writeoff record
1017 update disposition_code
1018 **/
1019 PROCEDURE reject_writeoffs (itemtype in varchar2,
1020 itemkey in varchar2,
1021 actid in number,
1022 funcmode in varchar2,
1023 result out nocopy varchar2) IS
1024 l_api_version NUMBER := 1;
1025 l_init_msg_list VARCHAR2(1);
1026 l_return_status VARCHAR2(1);
1027 l_msg_count NUMBER ;
1028 l_msg_data VARCHAR2(32627);
1029 l_message VARCHAR2(32627);
1030
1031
1032 l_WRITEOFF_ID VARCHAR2(32627);
1033 l_REQUEST_ID VARCHAR2(32627);
1034
1035 cursor c_get_obj_ver(p_WRITEOFF_ID IN NUMBER) is
1036 select object_version_number
1037 from iex_writeoffs
1038 where WRITEOFF_ID =p_WRITEOFF_ID;
1039
1040
1041 l_api_name CONSTANT VARCHAR2(50) := 'REJECT_WRITEOFFS';
1042 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.' || l_api_name;
1043 l_api_version_number CONSTANT NUMBER := 2.0;
1044 l_object_version_number
1045 IEX_writeoff_objects.writeoff_object_id%TYPE;
1046
1047 l_writeoff_rec writeoff_rec_type ;
1048 l_writeoff_obj_rec writeoff_obj_rec_type ;
1049
1050 cursor c_get_writeoff_objects(p_WRITEOFF_ID IN NUMBER,
1051 p_REQUEST_ID IN NUMBER) is
1052 select writeoff_object_id,
1053 object_version_number
1054 from iex_writeoff_objects
1055 where WRITEOFF_ID =p_WRITEOFF_ID
1056 and writeoff_status ='W'
1057 and request_id =p_request_id;
1058
1059
1060 BEGIN
1061
1062 if funcmode <> 'RUN' then
1063 result := wf_engine.eng_null;
1064 return;
1065 end if;
1066 iex_debug_pub.logmessage ( 'JSANJU---inside reject writeoff');
1067 l_WRITEOFF_ID:= wf_engine.GetItemAttrText(itemtype => itemtype,
1068 itemkey => itemkey,
1069 aname => 'WRITEOFF_ID');
1070
1071
1072 l_REQUEST_ID:= wf_engine.GetItemAttrText(itemtype => itemtype,
1073 itemkey => itemkey,
1074 aname => 'REQUEST_ID');
1075
1076 OPEN c_get_obj_ver(l_WRITEOFF_ID);
1077 FETCH c_get_obj_ver INTO l_writeoff_rec.object_version_number;
1078 CLOSE c_get_obj_ver;
1079
1080
1081 --update iex_writeoff_objects
1082 --prefix this message before writeoff record is updated
1083 AddfailMsg( p_object => 'Writeoff Object ',
1084 p_operation => 'UPDATE' );
1085
1086 FOR i in c_get_writeoff_objects (l_writeoff_id,L_REQUEST_ID)
1087 LOOP
1088 l_writeoff_obj_rec.writeoff_object_id :=i.writeoff_object_id;
1089 l_writeoff_obj_rec.writeoff_status :='R';
1090 l_writeoff_obj_rec.object_version_number :=i.object_version_number;
1091
1092 IEX_WRITEOFF_OBJECTS_PUB.update_writeoff_objects(
1093 P_Api_Version_Number =>l_Api_Version_Number
1094 ,P_Init_Msg_List =>'F'
1095 ,P_Commit =>'F'
1096 ,P_writeoff_obj_rec =>l_writeoff_obj_rec
1097 ,xo_object_version_number =>l_object_version_number
1098 ,x_return_status =>l_return_status
1099 ,x_msg_count =>l_msg_count
1100 ,x_msg_data =>l_msg_data);
1101
1102 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1103 IEX_WRITEOFFOBJ_PUB.Get_Messages1 (l_msg_count,l_message);
1104 -- IF PG_DEBUG <11 THEN
1105 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1106 iex_debug_pub.logmessage('Error' ||l_message);
1107 END IF;
1108 wf_engine.SetItemAttrText(itemtype => itemtype,
1109 itemkey => itemkey,
1110 aname => 'ERROR_MESSAGE',
1111 avalue => l_message);
1112 --raise FND_API.G_EXC_ERROR;
1113 result := wf_engine.eng_completed ||':'||wf_no;
1114 return;
1115 ELSE
1116 FND_MSG_PUB.initialize;
1117 END IF;
1118
1119 END LOOP;
1120
1121 --update iex_writeoff_objects
1122 --prefix this message before writeoff record is updated
1123 AddfailMsg( p_object => 'Writeoffs ',
1124 p_operation => 'UPDATE' );
1125
1126 -- update iex_writeoff's dispostion_code;
1127 l_writeoff_rec.disposition_code := 'R';
1128 --l_writeoff_rec.disposition_date := SYSDATE;
1129 l_writeoff_rec.writeoff_id := l_writeoff_id;
1130
1131 IEX_WRITEOFFS_PVT.Update_writeoffs(
1132 P_Api_Version_Number =>l_api_version_number
1133 ,P_Init_Msg_List =>'F'
1134 ,P_Commit =>'F'
1135 ,P_writeoffs_Rec =>l_writeoff_rec
1136 ,x_return_status => l_return_status
1137 ,x_msg_count => l_msg_count
1138 ,x_msg_data => l_msg_data
1139 ,xo_object_version_number => l_object_version_number);
1140
1141 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1142 IEX_WRITEOFFOBJ_PUB.Get_Messages1 (l_msg_count,l_message);
1143 -- IF PG_DEBUG <11 THEN
1144 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1145 iex_debug_pub.logmessage('Error' ||l_message||itemkey||itemtype);
1146 END IF;
1147
1148 wf_engine.SetItemAttrText(itemtype => itemtype,
1149 itemkey => itemkey,
1150 aname => 'ERROR_MESSAGE',
1151 avalue => l_message);
1152 --raise FND_API.G_EXC_ERROR;
1153 result := wf_engine.eng_completed ||':'||wf_no;
1154 return;
1155
1156 ELSE
1157 FND_MSG_PUB.initialize;
1158 END IF;
1159
1160 result := wf_engine.eng_completed ||':'||wf_yes;
1161
1162 EXCEPTION
1163 WHEN FND_API.G_EXC_ERROR THEN
1164 --resultout := wf_engine.eng_completed ||':'||wf_no;
1165 wf_core.context('IEX_WRITEOFFOBJ_PUB',
1166 'reject_writeoffs',
1167 itemtype,
1168 itemkey,
1169 to_char(actid),
1170 funcmode);
1171 raise;
1172
1173 when others then
1174 --resultout := wf_engine.eng_completed ||':'||wf_no;
1175 wf_core.context('IEX_WRITEOFFOBJ_PUB',
1176 'reject_writeoffs',
1177 itemtype,
1178 itemkey,
1179 to_char(actid),
1180 funcmode);
1181 raise;
1182
1183 END reject_writeoffs;
1184
1185 BEGIN
1186
1187 G_FILE_NAME := 'iexpwrob.pls';
1188 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1189 wf_yes := 'Y';
1190 wf_no :='N';
1191 END IEX_WRITEOFFOBJ_PUB;
1192
1193
1194