[Home] [Help]
PACKAGE BODY: APPS.IEX_DISPUTE_PVT
Source
1 PACKAGE BODY IEX_DISPUTE_PVT AS
2 /* $Header: iexvdisb.pls 120.4.12010000.5 2008/11/14 13:08:48 pnaveenk ship $ */
3
4 --G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpimpb.pls';
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_DISPUTE_PVT';
6
7 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
8 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 --Added parameters p_skip_workflow_flag and p_dispute_date
10 --for bug#6347547 by schekuri on 08-Nov-2007
11 -- Bug #6777367 bibeura 28-Jan-2008 Added parameter p_batch_source_name
12 PROCEDURE Create_Dispute(p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE,
15 p_disp_header_rec IN IEX_DISPUTE_PUB.DISP_HEADER_REC,
16 p_disp_line_tbl IN IEX_DISPUTE_PUB.DISPUTE_LINE_TBL,
17 x_request_id OUT NOCOPY NUMBER,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 p_skip_workflow_flag IN VARCHAR2 DEFAULT 'N',
22 p_batch_source_name IN VARCHAR2 DEFAULT NULL,
23 p_dispute_date IN DATE DEFAULT NULL) AS
24
25 l_api_name VARCHAR2(50) := 'create_dispute';
26 l_api_version_number NUMBER := 1.0;
27 l_request_id NUMBER;
28 l_status VARCHAR2(20);
29 l_dis_id NUMBER; -- using this for the sequence
30 x VARCHAR(20);
31 l_return_status VARCHAR2(1);
32 l_msg_count NUMBER;
33 l_msg_data VARCHAR2(32767);
34 l_init_msg_list VARCHAR2(25) := p_init_msg_list;
35 l_commit VARCHAR2(25) := p_commit;
36
37 l_disp_header_rec IEX_DISPUTE_PUB.DISP_HEADER_REC := p_disp_header_rec;
38 lp_disp_line_tbl IEX_DISPUTE_PUB.DISPUTE_LINE_TBL := p_disp_line_tbl;
39 l_disp_line_tbl arw_cmreq_cover.Cm_line_Tbl_Type_cover;
40 l_cover_rec arw_cmreq_cover.Cm_Line_Rec_Type_Cover;
41 i NUMBER;
42 lines_count NUMBER;
43 l_line NUMBER;
44 l_tax NUMBER;
45 l_freight NUMBER;
46
47 l_multiplier NUMBER := -1;
48 l_creationSign VARCHAR2(5);
49 l_credit_memo_type_id NUMBER;
50
51 l_currency_code VARCHAR2(10) := 'USD';
52 l_request_url VARCHAR2(1000);
53 l_transaction_url VARCHAR2(1000);
54 l_trans_act_url VARCHAR2(1000);
55 l_org_id NUMBER(15);
56 l_disp_component VARCHAR2(150);
57
58 Cursor Get_Currency_Code Is
59 select invoice_currency_code, org_id
60 from ra_customer_trx
61 where customer_trx_id = l_disp_header_rec.cust_trx_id;
62
63 BEGIN
64
65 SAVEPOINT create_dispute_pvt;
66
67 -- Initialize API return status to SUCCESS
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 -- Standard call to check for call compatibility.
71 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
72 p_api_version,
73 l_api_name,
74 G_PKG_NAME)
75 THEN
76 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
77 END IF;
78
79 -- Initialize message list if p_init_msg_list is set to TRUE.
80 IF FND_API.to_Boolean(p_init_msg_list)
81 THEN
82 FND_MSG_PUB.initialize;
83 END IF;
84
85 -- Debug Message
86 -- IF PG_DEBUG < 10 THEN
87 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
88 IEX_DEBUG_PUB.logMessage('Create_Dispute: ' || 'PVT: ' || l_api_name || ' start');
89 END IF;
90 --
91 -- API body
92 -- --------------------------------------------------------
93
94
95 -- IF PG_DEBUG < 10 THEN
96 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
97 IEX_DEBUG_PUB.logMessage('Create_Dispute: ' || 'IEX_DISPUTES_PVT: CreateDispute: getting creation_sign');
98 END IF;
99 -- added by ehuh for URL parameters
100 Open Get_Currency_Code;
101 Fetch Get_Currency_Code into l_currency_code, l_org_id;
102 Close Get_Currency_Code;
103
104
105 --Begin-fix bug #3817776-JYPARK-09/30/2005-change URL string to followin irec request
106
107 IF l_disp_header_rec.dispute_section = 'LINES_SUBTOTAL' THEN
108 l_disp_component := 'DISP_SUBTOTAL';
109 ELSIF l_disp_header_rec.dispute_section = 'PERCENT' THEN
110 l_disp_component := 'DISP_TOTAL';
111 ELSIF l_disp_header_rec.dispute_section = 'SHIPPING' THEN
112 l_disp_component := 'DISP_SHIPPING';
113 ELSIF l_disp_header_rec.dispute_section = 'SPECIFIC_INVOICE_LINES' THEN
114 l_disp_component := 'DISP_SPEC_LINE';
115 ELSIF l_disp_header_rec.dispute_section = 'TAX' THEN
116 l_disp_component := 'DISP_TAX';
117 ELSIF l_disp_header_rec.dispute_section = 'TOTAL' THEN
118 l_disp_component := 'DISP_TOTAL';
119 END IF;
120
121 l_request_url := 'JSP:/OA_HTML/OA.jsp?akRegionCode=ARITEMPCMREQUESTDETAILSPAGE'||'&'||'akRegionApplicationId=222'||
122 '&'||'Irtransactiontype=REQ'||'&'||'Ircustomertrxid='||l_disp_header_rec.cust_trx_id||
123 -- '&'||'req_id='||'&'||'Ircurrencycode='||l_currency_code||'&'||'component=DISP_SPEC_LINE' ;
124 '&'||'req_id='||'&'||'Ircurrencycode='||l_currency_code||
125 '&'||'IcxPrintablePageButton=&OUnit='||l_org_id||'&NtfId=-&#NID-&' ||'component='||l_disp_component;
126
127 l_transaction_url := 'JSP:/OA_HTML/OA.jsp?akRegionCode=ARITRANSACTIONDETAILSPAGE'||'&'||'akRegionApplicationId=222'||
128 '&'||'Irtransactiontype=INV'||'&'||'Ircustomertrxid='||l_disp_header_rec.cust_trx_id||'&'||'Irtermssequencenumber=1'||
129 --'&'||'Ircurrencycode='||l_currency_code;
130 '&'||'Ircurrencycode='||l_currency_code||'&IcxPrintablePageButton=&OUnit='||l_org_id||'&NtfId=-&#NID-';
131
132 l_trans_act_url := 'JSP:/OA_HTML/OA.jsp?akRegionCode=ARITRANSACTIONDETAILSPAGE'||'&'||'akRegionApplicationId=222'||
133 '&'||'Irtransactiontype=INV'||'&'||'Ircustomertrxid='||l_disp_header_rec.cust_trx_id||'&'||'Irtermssequencenumber=1'||
134 --'&'||'Ircurrencycode='||l_currency_code||'&'||'AriInvDisplay=BOGUS_GO'||'&'||'AriInvDisplayType=INVOICE_ACTIVITIES';
135 '&'||'Ircurrencycode='||l_currency_code||'&IcxPrintablePageButton=&OUnit='||l_org_id||'&NtfId=-&#NID-&AriInvDisplay=BOGUS_GO'||'&'||'AriInvDisplayType=INVOICE_ACTIVITIES';
136 --End-fix bug #3817776-JYPARK-09/30/2005-change URL string to followin irec request
137
138 -- figure out NOCOPY creation sign
139 select TRX_TYPE.creation_sign, TRX_TYPE.CREDIT_MEMO_TYPE_ID into l_creationSign, l_credit_memo_type_id
140 from ra_cust_trx_types TRX_TYPE,
141 ra_customer_trx TRX
142 where TRX.CUST_TRX_TYPE_ID = TRX_TYPE.CUST_TRX_TYPE_ID and
143 TRX.Customer_Trx_ID = l_disp_header_rec.cust_trx_id;
144
145 if l_credit_memo_type_id is not null then
146
147 if l_creationSign = 'P' then
148 l_multiplier := -1;
149 elsif l_creationSign = 'N' then
150 l_multiplier := 1;
151 end if;
152 -- IF PG_DEBUG < 10 THEN
153 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
154 IEX_DEBUG_PUB.logMessage('Create_Dispute: ' || 'IEX_DISPUTES_PVT: CreateDispute: creation_sign is ' || l_creationSign);
155 END IF;
156 -- IF PG_DEBUG < 10 THEN
157 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
158 IEX_DEBUG_PUB.logMessage('Create_Dispute: ' || 'IEX_DISPUTES_PVT: CreateDispute: multiplier is ' || l_multiplier);
159 END IF;
160
161 -- add this to avoid bug# 2166002
162 select decode(l_disp_header_rec.line_amt, FND_API.G_MISS_NUM, 0, l_disp_header_rec.line_amt) into l_line
163 from dual;
164 select decode(l_disp_header_rec.tax_amt, FND_API.G_MISS_NUM, 0, l_disp_header_rec.tax_amt) into l_tax
165 from dual;
166 select decode(l_disp_header_rec.freight_amt, FND_API.G_MISS_NUM, 0, l_disp_header_rec.freight_amt) into l_freight
167 from dual;
168
169 if lp_disp_line_tbl is not null then
170 lines_count := lp_disp_line_tbl.count;
171 -- IF PG_DEBUG < 10 THEN
172 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
173 IEX_DEBUG_PUB.logMessage('Create_Dispute: ' || 'lines count is ' || lines_count);
174 END IF;
175
176 if lines_count >= 1 then
177 FOR i in 1..lines_count
178 LOOP
179 BEGIN
180 l_disp_line_tbl(i).customer_trx_line_id := lp_disp_line_tbl(i).customer_trx_line_id;
181 l_disp_line_tbl(i).extended_amount := lp_disp_line_tbl(i).extended_amount * l_multiplier;
182 l_disp_line_tbl(i).quantity_credited := lp_disp_line_tbl(i).quantity_credited * l_multiplier;
183
184 -- price should be positive no matter what
185 SELECT UNIT_SELLING_PRICE
186 INTO l_disp_line_tbl(i).price
187 FROM ra_customer_trx_lines
188 WHERE customer_trx_line_id = l_disp_line_tbl(i).customer_trx_line_id;
189 EXCEPTION
190 WHEN NO_DATA_FOUND THEN
191 FND_MESSAGE.SET_NAME('IEX', 'IEX_NO_UNIT_PRICE');
192 FND_MSG_PUB.Add;
193 x_return_status := FND_API.G_RET_STS_ERROR;
194 RAISE FND_API.G_EXC_ERROR;
195 END ;
196 -- dbms_output.put_line('price for customer_trx_id is'||to_char(l_disp_line_tbl(i).price)) ;
197
198 END LOOP ;
199 end if;
200 end if;
201
202 -- IF PG_DEBUG < 10 THEN
203 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
204 IEX_DEBUG_PUB.logMessage('Create_Dispute: ' || 'IEX_DISPUTES_PVT: CreateDispute: Calling AR Create Request API');
205 END IF;
206 AR_CREDIT_MEMO_API_PUB.create_request(
207 p_api_version => l_api_version_number,
208 p_init_msg_list => FND_API.G_TRUE,
209 p_commit => FND_API.G_TRUE,
210 p_validation_level => FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
211 x_return_status => l_return_status,
212 x_msg_count => l_msg_count,
213 x_msg_data => l_msg_data,
214 p_customer_trx_id => l_disp_header_rec.cust_trx_id,
215 p_line_credit_flag => l_disp_header_rec.line_credit_flag,
216 p_line_amount => l_line * l_multiplier,
217 p_tax_amount => l_tax * l_multiplier,
218 p_freight_amount => l_freight * l_multiplier,
219 p_cm_reason_code => l_disp_header_rec.cm_reason_code,
220 p_comments => l_disp_header_rec.COMMENTS,
221 p_internal_comment => p_disp_header_rec.INTERNAL_COMMENT, --Added for bug#7376422 by PNAVEENK on 4-sep-2008
222 p_orig_trx_number => l_disp_header_rec.orig_trx_number,
223 p_tax_ex_cert_num => l_disp_header_rec.tax_ex_cert_num,
224 p_request_url => l_request_url, --'AR_CREDIT_MEMO_API_PUB.print_default_page',
225 p_transaction_url => l_transaction_url, --'AR_CREDIT_MEMO_API_PUB.print_default_page',
226 p_trans_act_url => l_trans_act_url, --'AR_CREDIT_MEMO_API_PUB.print_default_page',
227 p_cm_line_tbl => l_disp_line_tbl,
228 p_skip_workflow_flag => p_skip_workflow_flag, --'N', --Modified for bug#6347547 by schekuri on 08-Nov-2007
229 p_batch_source_name => p_batch_source_name, -- Bug #6777367 bibeura 28-Jan-2008
230 x_request_id => l_request_id,
231 p_org_id => l_org_id, --Bug4696678. Fix by LKKUMAR on 26-Oct-2005. Pass Org_id.
232 p_dispute_date => p_dispute_date --Added for bug#6347547 by schekuri on 08-Nov-2007
233 );
234
235 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
236 SELECT iex_disputes_s.nextval INTO l_dis_id FROM dual;
237
238 -- IF PG_DEBUG < 10 THEN
239 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
240 IEX_DEBUG_PUB.logMessage('Create_Dispute: ' || 'IEX_DISPUTES_PVT: CreateDispute: Inserting a dispute row in IEX_DISPUTES');
241 END IF;
242 IEX_DISPUTES_PKG.Insert_Row(x_rowid => x,
243 p_dispute_id => l_dis_id,
244 p_last_update_date => sysdate,
245 p_last_updated_by => FND_GLOBAL.USER_ID,
246 p_creation_date => sysdate,
247 p_created_by => FND_GLOBAL.USER_ID,
248 p_last_update_login => FND_GLOBAL.USER_ID,
249 p_cm_request_id => l_request_id,
250 p_dispute_section => p_disp_header_rec.dispute_section,
251 p_delinquency_id => p_disp_header_rec.delinquency_id);
252 ELSE
253 -- IF PG_DEBUG < 10 THEN
254 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
255 IEX_DEBUG_PUB.logMessage('Create_Dispute: ' || 'IEX_DISPUTES_PVT: CreateDispute: Insert Failed');
256 END IF;
257 FND_MESSAGE.SET_NAME('IEX', 'IEX_DISPUTE_FAILED');
258 FND_MSG_PUB.Add;
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 RAISE FND_API.G_EXC_ERROR;
261
262 END IF ;
263
264 else -- no credit memo associated with transaction
265 -- IF PG_DEBUG < 10 THEN
266 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
267 IEX_DEBUG_PUB.logMessage('Create_Dispute: ' || 'IEX_DISPUTES_PVT: CreateDispute: Credit Memo Configuration failure');
268 END IF;
269 FND_MESSAGE.SET_NAME('IEX', 'IEX_NO_CREDIT_MEMO');
270 FND_MSG_PUB.Add;
271 x_return_status := FND_API.G_RET_STS_ERROR;
272 RAISE FND_API.G_EXC_ERROR;
273
274 end if;
275
276 IF FND_API.to_Boolean( p_commit )
277 THEN
278 COMMIT WORK;
279 END IF;
280
281 x_request_id := l_request_id;
282 x_return_status := l_return_status;
283 x_msg_count := l_msg_count;
284 x_msg_data := l_msg_data;
285 -- End of API body
286
287 -- Debug Message
288 -- IF PG_DEBUG < 10 THEN
289 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
290 IEX_DEBUG_PUB.logMessage('Create_Dispute: ' || 'PVT: ' || l_api_name || ' end');
291 END IF;
292
293 -- Standard call to get message count and if count is 1, get message info.
294 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
295 p_data => x_msg_data);
296 EXCEPTION
297 WHEN FND_API.G_EXC_ERROR THEN
298 ROLLBACK TO create_dispute_pvt;
299 x_return_status := FND_API.G_RET_STS_ERROR;
300 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
301 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
302 ROLLBACK TO create_dispute_pvt;
303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
304 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
305 WHEN OTHERS THEN
306 ROLLBACK TO create_dispute_pvt;
307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
308 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
309 THEN
310 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
311 END IF;
312 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
313
314 END create_dispute;
315
316 PROCEDURE is_delinquency_dispute(p_api_version IN NUMBER,
317 p_init_msg_list IN VARCHAR2 ,
318 p_delinquency_id IN NUMBER,
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2) AS
322
323 l_api_name VARCHAR2(50) := 'is_delinquency_dispute';
324 l_api_version_number NUMBER := 1.0;
325 l_delinquency_id NUMBER := p_delinquency_id;
326 l_count NUMBER := 0 ;
327 l_request_id NUMBER ;
328 l_status VARCHAR(20) ;
329
330 BEGIN
331 -- Standard Start of API savepoint
332 SAVEPOINT is_delinquency_dispute_pvt;
333
334 -- Initialize API return status to SUCCESS
335 x_return_status := FND_API.G_RET_STS_SUCCESS;
336
337 -- Standard call to check for call compatibility.
338 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
339 p_api_version,
340 l_api_name,
341 G_PKG_NAME)
342 THEN
343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
344 END IF;
345
346 -- Initialize message list if p_init_msg_list is set to TRUE.
347 IF FND_API.to_Boolean( p_init_msg_list )
348 THEN
349 FND_MSG_PUB.initialize;
350 END IF;
351
352 -- Debug Message
353 -- IF PG_DEBUG < 10 THEN
354 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
355 IEX_DEBUG_PUB.logMessage('is_delinquency_dispute: ' || 'PVT: ' || l_api_name || ' start');
356 END IF;
357
358 --
359 -- API body
360 --
361
362 select count(1) into l_count
363 from iex_disputes
364 where DELINQUENCY_ID = l_delinquency_id ;
365
366 IF (l_count = 0 ) THEN
367 --dbms_output.put_line('No dispute exist for this delinquency') ;
368 FND_MESSAGE.Set_Name('IEX_NO_DISP_FOR_DEL', 'IEX_NO_DISP_FOR_DEL');
369 FND_MSG_PUB.Add;
370 x_return_status := 'F' ;
371 ELSE
372 select cm_request_id into l_request_id
373 from iex_disputes
374 where delinquency_id = l_delinquency_id ;
375
376 select status into l_status
377 from ra_cm_requests
378 where request_id = l_request_id ;
379
380 IF l_status = 'PENDING_APPROVAL' or l_status = 'APPROVED_PEND_COMP' then
381 x_return_status := 'T' ;
382 ELSIF l_status = 'COMPLETE' or l_status = 'NOT_APPROVED' THEN
383 FND_MESSAGE.Set_Name('IEX_STATUS_COMPLETE_DEL', 'IEX_STATUS_COMPLETE_DEL');
384 FND_MSG_PUB.Add;
385 x_return_status := 'F' ;
386 ELSE
387 FND_MESSAGE.Set_Name('IEX_STATUS_NOEXIST_DEL', 'IEX_STATUS_NOEXIST_DEL');
388 FND_MSG_PUB.Add;
389 x_return_status := 'F' ;
390 END IF ;
391
392 END IF ;
393
394 -- End of API body
395
396 -- Debug Message
397 -- IF PG_DEBUG < 10 THEN
398 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
399 IEX_DEBUG_PUB.logMessage('is_delinquency_dispute: ' || 'PVT: ' || l_api_name || ' end');
400 END IF;
401
402 -- Standard call to get message count and if count is 1, get message info.
403 FND_MSG_PUB.Count_And_Get
404 ( p_count => x_msg_count,
405 p_data => x_msg_data
406 );
407
408 EXCEPTION
409 WHEN FND_API.G_EXC_ERROR THEN
410 ROLLBACK TO is_delinquency_dispute_pvt;
411 x_return_status := FND_API.G_RET_STS_ERROR;
412 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
413 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
414 ROLLBACK TO is_delinquency_dispute_pvt;
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
417 WHEN OTHERS THEN
418 ROLLBACK TO is_delinquency_dispute_pvt;
419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
420 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
421 THEN
422 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
423 END IF;
424 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
425
426
427
428 END is_delinquency_dispute;
429
430 PROCEDURE CANCEL_DISPUTE (p_api_version IN NUMBER,
431 p_commit IN VARCHAR2,
432 p_dispute_id IN NUMBER,
433 p_cancel_comments IN VARCHAR2,
434 x_return_status OUT NOCOPY VARCHAR2,
435 x_msg_count OUT NOCOPY NUMBER,
436 x_msg_data OUT NOCOPY VARCHAR2
437 ) IS
438
439 l_item_type VARCHAR2(100);
440 l_result VARCHAR2(100);
441 l_status VARCHAR2(8);
442 l_action_id NUMBER := 0;
443 l_function_mode VARCHAR2(10);
444 l_debug_mesg VARCHAR2(240);
445 l_approver_id NUMBER;
446 l_reason_code VARCHAR2(45);
447 l_currency_code VARCHAR2(15);
448 l_total_credit_to_invoice NUMBER;
449 l_result_flag VARCHAR2(1);
450 l_customer_trx_id NUMBER;
451
452 l_request_id NUMBER;
453 new_dispute_date DATE;
454 new_dispute_amt NUMBER;
455 remove_from_dispute_amt NUMBER;
456 l_org_id NUMBER;
457 l_document_id NUMBER;
458 l_approver_display_name VARCHAR2(100);
459 l_note_id NUMBER;
460 l_note_text ar_notes.text%TYPE;
461 l_notes wf_item_attribute_values.text_value%TYPE;
462
463 l_last_updated_by NUMBER;
464 l_last_update_login NUMBER;
465 l_last_update_date DATE;
466 l_creation_date DATE;
467 l_created_by NUMBER;
468 errmsg VARCHAR2(32767);
469 l_default_note_type varchar2(240) := FND_PROFILE.VALUE('AST_NOTES_DEFAULT_TYPE');
470 l_party_id number;
471 l_cust_account_id number;
472 l_customer_site_use_id number;
473 l_payment_schedule_id number;
474 i number;
475 l_context_tab IEX_NOTES_PVT.CONTEXTS_TBL_TYPE;
476 l_return_status VARCHAR2(1);
477 l_msg_count NUMBER;
478 l_msg_data VARCHAR2(32767);
479
480 CURSOR ps_cur(p_customer_trx_id NUMBER) IS
481 SELECT payment_schedule_id,
482 due_date,
483 amount_in_dispute,
484 dispute_date
485 FROM ar_payment_schedules ps
486 WHERE ps.customer_trx_id = p_customer_trx_id;
487
488 CURSOR c_item_type(l_item_key NUMBER) IS
489 SELECT item_type
490 FROM wf_items
491 WHERE item_key = l_item_key
492 AND item_type IN('ARCMREQ', 'ARAMECM');
493
494 cursor get_partyid(p_cust_acct_id number) is
495 select party_id
496 from hz_cust_accounts
497 where cust_account_id = p_cust_acct_id;
498
499 Cursor Get_billto(p_cust_trx_id number) Is
500 select bill_to_site_use_id
501 from ra_customer_trx
502 where customer_trx_id = p_cust_trx_id;
503
504 Cursor Get_paymentid(p_cust_trx_id number) Is
505 select customer_id,payment_schedule_id
506 from ar_payment_schedules
507 where customer_trx_id = p_cust_trx_id;
508
509 BEGIN
510
511 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
512 IEX_DEBUG_PUB.logMessage('**** BEGIN IEX_DISPUTE_PVT.CANCEL_DISPUTE ************');
513 END IF;
514
515 SAVEPOINT CANCEL_DISPUTE;
516
517 -- Initialize API return status to success
518 l_return_status := FND_API.G_RET_STS_SUCCESS;
519
520 OPEN c_item_type(p_dispute_id);
521 FETCH c_item_type
522 INTO l_item_type;
523 CLOSE c_item_type;
524
525 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
526 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: l_item_type : '|| l_item_type);
527 END IF;
528
529 IF l_item_type IS NOT NULL THEN
530
531 l_function_mode := 'RUN';
532
533 BEGIN
534
535 BEGIN
536
537 SELECT org_id
538 INTO l_org_id
539 FROM ra_cm_requests_all
540 WHERE request_id = p_dispute_id;
541
542 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
543 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: l_org_id: ' || l_org_id);
544 END IF;
545 ----------------------------------------------------------
546 l_debug_mesg := 'Get the org_id for the credit memo request';
547 ----------------------------------------------------------
548
549 mo_global.set_policy_context('S', l_org_id);
550 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
551 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: l_org_id: ' || l_org_id);
552 END IF;
553
554 EXCEPTION
555 WHEN others THEN
556 ROLLBACK TO CANCEL_DISPUTE;
557 x_return_status := FND_API.G_RET_STS_ERROR;
558 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
559 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: - exception');
560 errmsg := SQLERRM;
561 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: - errmsg='||errmsg);
562 END IF;
563 RAISE FND_API.G_EXC_ERROR;
564
565 END;
566 ---------------------------------------------------------
567 l_debug_mesg := 'Remove Transaction from Dispute';
568 ---------------------------------------------------------
569
570 IF(l_function_mode = 'RUN') THEN
571
572 l_customer_trx_id := wf_engine.getitemattrnumber(l_item_type, p_dispute_id, 'CUSTOMER_TRX_ID');
573 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
574 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: l_customer_trx_id: ' || l_customer_trx_id);
575 END IF;
576
577 SELECT total_amount * -1
578 INTO remove_from_dispute_amt
579 FROM ra_cm_requests
580 WHERE request_id = p_dispute_id;
581
582 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
583 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: remove_from_dispute_amt: ' || remove_from_dispute_amt);
584 END IF;
585
586 BEGIN
587
588 FOR ps_rec IN ps_cur(l_customer_trx_id)
589 LOOP
590
591 new_dispute_amt := ps_rec.amount_in_dispute -remove_from_dispute_amt;
592
593 IF new_dispute_amt = 0 THEN
594 new_dispute_date := NULL;
595 ELSE
596 new_dispute_date := ps_rec.dispute_date;
597 END IF;
598
599 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
600 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: new_dispute_date: '||new_dispute_date);
601 END IF;
602 arp_process_cutil.update_ps(p_ps_id => ps_rec.payment_schedule_id,
603 p_due_date => ps_rec.due_date,
604 p_amount_in_dispute => new_dispute_amt,
605 p_dispute_date => new_dispute_date,
606 p_update_dff => 'N',
607 p_attribute_category => NULL,
608 p_attribute1 => NULL,
609 p_attribute2 => NULL,
610 p_attribute3 => NULL,
611 p_attribute4 => NULL,
612 p_attribute5 => NULL,
613 p_attribute6 => NULL,
614 p_attribute7 => NULL,
615 p_attribute8 => NULL,
616 p_attribute9 => NULL,
617 p_attribute10 => NULL,
618 p_attribute11 => NULL,
619 p_attribute12 => NULL,
620 p_attribute13 => NULL,
621 p_attribute14 => NULL,
622 p_attribute15 => NULL);
623 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
624 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: After arp_process_cutil.update_ps for p_ps_id => ' || ps_rec.payment_schedule_id);
625 END IF;
626 END LOOP;
627
628 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
629 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: End of FOR LOOP');
630 END IF;
631 END;
632
633 END IF; --IF(l_function_mode = 'RUN') THEN
634
635 EXCEPTION
636 WHEN others THEN
637 ROLLBACK TO CANCEL_DISPUTE;
638 x_return_status := FND_API.G_RET_STS_ERROR;
639 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
640 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: - exception');
641 errmsg := SQLERRM;
642 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: - errmsg='||errmsg);
643 END IF;
644 RAISE FND_API.G_EXC_ERROR;
645
646 END;
647
648 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
649 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: Before calling wf_engine.itemstatus');
650 END IF;
651 wf_engine.itemstatus(itemtype => l_item_type, itemkey => p_dispute_id, status => l_status, result => l_result);
652
653 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
654 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: after workflow status check ' || l_status || ' item key' || p_dispute_id);
655 END IF;
656
657 IF l_status <> wf_engine.eng_completed THEN
658 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
659 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: process has not completed and status =>' || l_status);
660 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: Calling wf_engine.abortprocess');
661 END IF;
662 BEGIN
663 wf_engine.abortprocess(itemtype => l_item_type, itemkey => p_dispute_id);
664 wf_engine.itemstatus(itemtype => l_item_type, itemkey => p_dispute_id, status => l_status, result => l_result);
665 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
666 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: Abort process has completed and status =>' || l_status);
667 END IF;
668
669 EXCEPTION
670 WHEN others THEN
671 ROLLBACK TO CANCEL_DISPUTE;
672 x_return_status := FND_API.G_RET_STS_ERROR;
673 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
674 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: - exception');
675 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: abort process ' || l_item_type || 'itemkey ' || p_dispute_id || 'has failed');
676 errmsg := SQLERRM;
677 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: - errmsg='||errmsg);
678 END IF;
679 RAISE FND_API.G_EXC_ERROR;
680
681 END;
682
683 ---------------------------------------------------------------------
684 l_debug_mesg := 'Insert Rejected Response notes';
685 ---------------------------------------------------------------------
686 BEGIN
687 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
688 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: Insert Rejected Response notes');
689 END IF;
690 arp_global.init_global;
691
692 l_last_updated_by := arp_global.user_id;
693 l_last_update_login := arp_global.last_update_login;
694 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
695 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: l_last_updated_by: ' || l_last_updated_by || ' l_last_update_login: '|| l_last_update_login);
696 END IF;
697 l_document_id := wf_engine.getitemattrnumber(l_item_type, p_dispute_id, 'WORKFLOW_DOCUMENT_ID');
698 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
699 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: l_document_id: '||l_document_id);
700 END IF;
701 l_customer_trx_id := wf_engine.getitemattrnumber(l_item_type, p_dispute_id, 'CUSTOMER_TRX_ID');
702 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
703 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: l_customer_trx_id: '||l_customer_trx_id);
704 END IF;
705
706 if l_customer_trx_id is null then
707 SELECT customer_trx_id
708 INTO l_customer_trx_id
709 FROM ra_cm_requests
710 WHERE request_id = l_document_id;
711
712 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
713 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: l_customer_trx_id: '||l_customer_trx_id);
714 END IF;
715 end if;
716
717
718 l_notes := wf_engine.getitemattrtext(l_item_type, p_dispute_id, 'NOTES');
719
720 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
721 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: l_notes: '||l_notes);
722 END IF;
723
724 fnd_message.set_name('AR', 'AR_WF_REJECTED_RESPONSE');
725 fnd_message.set_token('REQUEST_ID', to_char(p_dispute_id));
726 fnd_message.set_token('APPROVER', fnd_global.user_id);
727
728 l_note_text := fnd_message.GET;
729 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
730 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: l_note_text: ' ||l_note_text);
731 END IF;
732
733 IF l_notes IS NOT NULL THEN
734 l_note_text := substrb(l_note_text || ' "' || l_notes || '"', 1, 2000);
735 END IF;
736
737 BEGIN
738 ---------------------------------------------------------------------------
739 l_debug_mesg := 'Insert call topic notes';
740 ---------------------------------------------------------------------------
741
742 arp_global.init_global;
743
744 l_created_by := fnd_global.user_id;
745 l_creation_date := sysdate;
746 l_last_update_login := arp_global.last_update_login;
747 l_last_update_date := sysdate;
748 l_last_updated_by := fnd_global.user_id;
749 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
750 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: Before calling arp_notes_pkg.insert_cover');
751 END IF;
752 arp_notes_pkg.insert_cover(
753 p_note_type => 'MAINTAIN',
754 p_text => l_note_text,
755 p_customer_call_id => null,
756 p_customer_call_topic_id => null,
757 p_call_action_id => NULL,
758 p_customer_trx_id => l_customer_trx_id,
759 p_note_id => l_note_id,
760 p_last_updated_by => l_last_updated_by,
761 p_last_update_date => l_last_update_date,
762 p_last_update_login => l_last_update_login,
763 p_created_by => l_created_by,
764 p_creation_date => l_creation_date);
765
766 EXCEPTION
767 WHEN others THEN
768 ROLLBACK TO CANCEL_DISPUTE;
769 x_return_status := FND_API.G_RET_STS_ERROR;
770 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
771 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: - exception');
772 errmsg := SQLERRM;
773 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: - errmsg='||errmsg);
774 END IF;
775 RAISE FND_API.G_EXC_ERROR;
776 END;
777 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
778 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: After calling arp_notes_pkg.insert_cover');
779 END IF;
780
781 END;
782 END IF; --IF l_status <> wf_engine.eng_completed THEN
783
784 -- inserting a note
785 if p_cancel_comments is not null then
786
787 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
788 iex_debug_pub.LogMessage('CANCEL_DISPUTE: Going to build context for note...');
789 END IF;
790
791 i := 1;
792 l_context_tab(i).context_type := 'IEX_DISPUTE';
793 l_context_tab(i).context_id := p_dispute_id;
794 i := i + 1;
795
796 Open Get_paymentid(l_customer_trx_id);
797 Fetch Get_paymentid INTO l_cust_account_id,l_payment_schedule_id;
798 Close Get_paymentid;
799
800 Open get_partyid(l_cust_account_id);
801 Fetch get_partyid INTO l_party_id;
802 Close get_partyid;
803
804 l_context_tab(i).context_type := 'PARTY';
805 l_context_tab(i).context_id := l_party_id;
806 i := i + 1;
807
808 /* adding account into note context */
809 l_context_tab(i).context_type := 'IEX_ACCOUNT';
810 l_context_tab(i).context_id := l_cust_account_id;
811 i := i + 1;
812
813 Open Get_billto(l_customer_trx_id);
814 Fetch Get_billto INTO l_customer_site_use_id;
815 Close Get_billto;
816
817 l_context_tab(i).context_type := 'IEX_BILLTO';
818 l_context_tab(i).context_id := l_customer_site_use_id;
819 i := i + 1;
820
821 l_context_tab(i).context_type := 'IEX_INVOICES';
822 l_context_tab(i).context_id := l_payment_schedule_id;
823 i := i + 1;
824
825 FOR i IN 1..l_context_tab.COUNT LOOP
826 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
827 iex_debug_pub.LogMessage('CANCEL_DISPUTE: l_context_tab(' || i || ').context_type = ' || l_context_tab(i).context_type);
828 iex_debug_pub.LogMessage('CANCEL_DISPUTE: l_context_tab(' || i || ').context_id = ' || l_context_tab(i).context_id);
829 END IF;
830 END LOOP;
831
832 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
833 iex_debug_pub.LogMessage('CANCEL_DISPUTE: Calling IEX_NOTES_PVT.Create_Note...');
834 END IF;
835
836 IEX_NOTES_PVT.Create_Note(
837 P_API_VERSION => 1.0,
838 P_INIT_MSG_LIST => 'F',
839 P_COMMIT => 'F',
840 P_VALIDATION_LEVEL => 100,
841 X_RETURN_STATUS => l_return_status,
842 X_MSG_COUNT => l_msg_count,
843 X_MSG_DATA => l_msg_data,
844 p_source_object_id => p_dispute_id,
845 p_source_object_code => 'IEX_DISPUTE',
846 p_note_type => l_default_note_type,
847 --p_note_type => 'IEX_DISPUTE',
848 p_notes => p_cancel_comments,
849 p_contexts_tbl => l_context_tab,
850 x_note_id => l_note_id);
851
852 --X_PRORESP_REC.NOTE_ID := l_note_id;
853
854 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
855 iex_debug_pub.LogMessage('CANCEL_DISPUTE: After call to IEX_NOTES_PVT.Create_Note');
856 iex_debug_pub.LogMessage('CANCEL_DISPUTE: Status = ' || L_RETURN_STATUS);
857 END IF;
858
859 -- check for errors
860 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
861 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
862 iex_debug_pub.LogMessage('CANCEL_DISPUTE: IEX_NOTES_PVT.Create_Note failed');
863 END IF;
864 RAISE FND_API.G_EXC_ERROR;
865 END IF;
866 else
867 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
868 iex_debug_pub.LogMessage('CANCEL_DISPUTE: no note to save');
869 END IF;
870 end if;
871
872 END IF; --IF l_item_type IS NOT NULL THEN
873
874 -- Standard check for p_commit
875 IF FND_API.to_Boolean( p_commit )
876 THEN
877 COMMIT WORK;
878 END IF;
879
880 x_return_status := l_return_status;
881 -- Standard call to get message count and if count is 1, get message info
882 FND_MSG_PUB.Count_And_Get(
883 p_encoded => FND_API.G_FALSE,
884 p_count => x_msg_count,
885 p_data => x_msg_data);
886
887 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
888 IEX_DEBUG_PUB.logMessage('**** END IEX_DISPUTE_PVT.CANCEL_DISPUTE ************');
889 END IF;
890
891 EXCEPTION
892 WHEN FND_API.G_EXC_ERROR THEN
893 ROLLBACK TO CANCEL_DISPUTE;
894 x_return_status := FND_API.G_RET_STS_ERROR;
895 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
896 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: - exception');
897 errmsg := SQLERRM;
898 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: - errmsg='||errmsg);
899 END IF;
900 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
901 WHEN others THEN
902 ROLLBACK TO CANCEL_DISPUTE;
903 x_return_status := FND_API.G_RET_STS_ERROR;
904 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
905 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: - exception');
906 errmsg := SQLERRM;
907 IEX_DEBUG_PUB.logMessage('CANCEL_DISPUTE: - errmsg='||errmsg);
908 END IF;
909
910 END CANCEL_DISPUTE;
911
912 END IEX_DISPUTE_PVT ;