[Home] [Help]
PACKAGE BODY: APPS.IEX_PROMISES_PUB
Source
1 PACKAGE BODY IEX_PROMISES_PUB as
2 /* $Header: iexpyprb.pls 120.15.12020000.4 2012/07/23 09:25:36 sunagesh ship $ */
3
4 PG_DEBUG NUMBER; -- := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5
6 G_APP_ID CONSTANT NUMBER := 695;
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_PROMISES_PUB';
8 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpyprb.pls';
9 G_LOGIN_ID NUMBER; -- := FND_GLOBAL.Conc_Login_Id;
10 G_PROGRAM_ID NUMBER; -- := FND_GLOBAL.Conc_Program_Id;
11 G_USER_ID NUMBER; -- := FND_GLOBAL.User_Id;
12 G_REQUEST_ID NUMBER; -- := FND_GLOBAL.Conc_Request_Id;
13
14 PROCEDURE SHOW_IN_UWQ(
15 P_API_VERSION IN NUMBER,
16 P_INIT_MSG_LIST IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
17 P_COMMIT IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
18 P_VALIDATION_LEVEL IN NUMBER, -- DEFAULT FND_API.G_VALID_LEVEL_FULL,
19 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
20 X_MSG_COUNT OUT NOCOPY NUMBER,
21 X_MSG_DATA OUT NOCOPY VARCHAR2,
22 P_PROMISE_TBL IN DBMS_SQL.NUMBER_TABLE,
23 P_STATUS IN VARCHAR2,
24 P_DAYS IN NUMBER DEFAULT NULL)
25 IS
26 l_api_name CONSTANT VARCHAR2(30) := 'SHOW_IN_UWQ';
27 l_api_version CONSTANT NUMBER := 1.0;
28 l_return_status varchar2(10);
29 l_msg_count number;
30 l_msg_data varchar2(200);
31
32 l_validation_item varchar2(100);
33 l_days NUMBER;
34 l_state varchar2(20);
35 nCount number;
36
37 Type refCur is Ref Cursor;
38 l_cursor refCur;
39 l_SQL VARCHAR2(10000);
40 l_broken_promises DBMS_SQL.NUMBER_TABLE;
41 i number;
42 j number;
43 l_uwq_active_date date;
44 l_uwq_complete_date date;
45 begin
46 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
47 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': start');
48 END IF;
49
50 -- Standard start of API savepoint
51 SAVEPOINT SHOW_IN_UWQ_PVT;
52
53 -- Standard call to check for call compatibility
54 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
55 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
56 END IF;
57
58 -- Initialize message list if p_init_msg_list is set to TRUE
59 IF FND_API.To_Boolean(p_init_msg_list) THEN
60 FND_MSG_PUB.initialize;
61 END IF;
62
63 -- Initialize API return status to success
64 l_return_status := FND_API.G_RET_STS_SUCCESS;
65
66 -- START OF BODY OF API
67
68 -- validating uwq status
69 l_validation_item := 'P_STATUS';
70 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
71 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': new uwq status: ' || P_STATUS);
72 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
73 END IF;
74 if P_STATUS is null then
75 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
76 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
77 END IF;
78 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
79 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
80 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
81 FND_MSG_PUB.Add;
82 RAISE FND_API.G_EXC_ERROR;
83 end if;
84
85 -- validating table of promises
86 l_validation_item := 'P_PROMISE_TBL';
87 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
88 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': count of P_PROMISE_TBL: ' || P_PROMISE_TBL.count);
89 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
90 END IF;
91 if P_PROMISE_TBL.count = 0 then
92 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
93 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
94 END IF;
95 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
96 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
97 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
98 FND_MSG_PUB.Add;
99 RAISE FND_API.G_EXC_ERROR;
100 end if;
101
102 -- validating p_days
103 l_validation_item := 'P_DAYS';
104 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
105 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': P_DAYS: ' || P_DAYS);
106 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
107 END IF;
108 if P_DAYS is not null and P_DAYS < 0 then
109 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
110 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
111 END IF;
112 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
113 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
114 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
115 FND_MSG_PUB.Add;
116 RAISE FND_API.G_EXC_ERROR;
117 end if;
118
119 -- filter passed promises; we need only BROKEN_PROMISE promises for update
120 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
121 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': filtering broken promises...');
122 END IF;
123 l_SQL := 'SELECT state ' ||
124 'FROM IEX_PROMISE_DETAILS ' ||
125 'WHERE ' ||
126 'PROMISE_DETAIL_ID = :P_PROMISE_ID';
127
128 j := 0;
129 for i in 1..P_PROMISE_TBL.count loop
130 open l_cursor for l_SQL
131 using P_PROMISE_TBL(i);
132 fetch l_cursor into l_state;
133
134 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
135 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': promise ' || P_PROMISE_TBL(i));
136 END IF;
137 if l_cursor%NOTFOUND then
138 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
139 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': invalid promise');
140 END IF;
141 else
142 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
143 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': state ' || l_state);
144 END IF;
145 if l_state = 'BROKEN_PROMISE' then
146 j := j + 1;
147 l_broken_promises(j) := P_PROMISE_TBL(i);
148 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
149 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': promise ' || P_PROMISE_TBL(i) || ' is added to broken promises table');
150 END IF;
151 end if;
152 end if;
153 end loop;
154 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
155 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': total broken promises ' || l_broken_promises.count);
156 END IF;
157
158 -- check for status
159 if P_STATUS = 'ACTIVE' then
160 l_uwq_active_date := NULL;
161 l_uwq_complete_date := NULL;
162 elsif P_STATUS = 'PENDING' then
163 -- set number of days
164 if P_DAYS is null then
165 l_days := to_number(nvl(fnd_profile.value('IEX_UWQ_DEFAULT_PENDING_DAYS'), '0'));
166 else
167 l_days := P_DAYS;
168 end if;
169 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
170 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': number of days: ' || l_days);
171 END IF;
172 l_uwq_active_date := sysdate + l_days;
173
174 l_uwq_complete_date := NULL;
175 elsif P_STATUS = 'COMPLETE' then
176 l_uwq_active_date := NULL;
177 l_uwq_complete_date := sysdate;
178 end if;
179
180 -- do updates of broken promises as appropriate
181 nCount := l_broken_promises.count;
182 if nCount > 0 then
183 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
184 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_active_date: ' || l_uwq_active_date);
185 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_complete_date: ' || l_uwq_complete_date);
186 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': updating promise details...');
187 END IF;
188 FORALL i in 1..nCount
189 update iex_promise_details
190 set UWQ_STATUS = P_STATUS,
191 UWQ_ACTIVE_DATE = l_uwq_active_date,
192 UWQ_COMPLETE_DATE = l_uwq_complete_date,
193 last_update_date = sysdate,
194 last_updated_by = G_USER_ID
195 where
196 promise_detail_id = l_broken_promises(i);
197 else
198 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
199 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': nothing to update');
200 END IF;
201 end if;
202
203 -- END OF BODY OF API
204
205 -- Standard check of p_commit.
206 IF FND_API.To_Boolean( p_commit ) THEN
207 COMMIT WORK;
208 END IF;
209
210 x_return_status := l_return_status;
211 -- Standard call to get message count and if count is 1, get message info
212 FND_MSG_PUB.Count_And_Get(
213 p_encoded => FND_API.G_FALSE,
214 p_count => x_msg_count,
215 p_data => x_msg_data);
216
217 EXCEPTION
218 WHEN FND_API.G_EXC_ERROR THEN
219 ROLLBACK TO SHOW_IN_UWQ_PVT;
220 x_return_status := FND_API.G_RET_STS_ERROR;
221 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
223 ROLLBACK TO SHOW_IN_UWQ_PVT;
224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
225 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
226 WHEN OTHERS THEN
227 ROLLBACK TO SHOW_IN_UWQ_PVT;
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
230 THEN
231 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
232 END IF;
233 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
234 end;
235
236
237 PROCEDURE SET_STRATEGY(P_PROMISE_ID IN NUMBER,
238 P_STATUS IN VARCHAR2)
239 IS
240 l_api_name CONSTANT VARCHAR2(30) := 'SET_STRATEGY';
241 l_return_status varchar2(10);
242 l_msg_count number;
243 l_msg_data varchar2(200);
244
245 Type refCur is Ref Cursor;
246 l_cursor refCur;
247 l_SQL VARCHAR2(10000);
248 l_cust_account_id number;
249 l_delinquency_id number;
250 l_object_type varchar2(100);
251 l_object_id number;
252 l_cnsld_id number;
253 l_contract_id number;
254
255 --begin bug#2369298 schekuri 24-Feb-2006
256 /*CURSOR del_crs(p_promise_id number) IS
257 SELECT delinquency_id, cust_account_id, CNSLD_INVOICE_ID, CONTRACT_ID
258 FROM IEX_PROMISE_DETAILS
259 WHERE PROMISE_DETAIL_ID = P_PROMISE_ID;*/
260
261 l_DefaultStrategyLevel number;
262 l_party_id number;
263 l_cust_site_use_id number;
264 l_unpro_dels number;
265 CURSOR del_crs(p_promise_id number) IS
266 SELECT del.party_cust_id,del.cust_account_id,del.CUSTOMER_SITE_USE_ID,
267 del.delinquency_id, prd.CNSLD_INVOICE_ID, prd.CONTRACT_ID
268 FROM IEX_PROMISE_DETAILS prd,
269 IEX_DELINQUENCIES_ALL del
270 WHERE prd.PROMISE_DETAIL_ID = P_PROMISE_ID
271 and prd.delinquency_id = del.delinquency_id;
272 --end bug#2369298 schekuri 24-Feb-2006
273
274 -- Start for bug8844974 PNAVEENK 27-Aug-2009
275 cursor c_strategy_level (p_party_id number) is
276 select strategy_level
277 from iex_strategies
278 where party_id= p_party_id
279 and status_code in ('OPEN', 'ONHOLD');
280 -- end for bug 8844974
281 begin
282 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
283 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': start');
284 END IF;
285
286 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
287 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': promise = ' || P_PROMISE_ID);
288 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': status = ' || P_STATUS);
289 END IF;
290
291 -- validation input
292 if P_PROMISE_ID is null then
293 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
294 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Error! P_PROMISE_ID is null');
295 END IF;
296 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
297 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
298 FND_MESSAGE.SET_TOKEN('API_PARAMETER', 'P_PROMISE_ID');
299 FND_MSG_PUB.Add;
300 RAISE FND_API.G_EXC_ERROR;
301 end if;
302
303 if P_STATUS is null then
304 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
305 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Error! P_STATUS is null');
306 END IF;
307 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
308 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
309 FND_MESSAGE.SET_TOKEN('API_PARAMETER', 'P_STATUS');
310 FND_MSG_PUB.Add;
311 RAISE FND_API.G_EXC_ERROR;
312 end if;
313
314 --begin bug#2369298 schekuri 24-Feb-2006
315 --get party_id and cust_site_use id also
316 /* getting delinquency_id, cust_account_id, l_cnsld_id and l_contract_id */
317 /*l_SQL := 'SELECT delinquency_id, cust_account_id, CNSLD_INVOICE_ID, CONTRACT_ID ' ||
318 'FROM IEX_PROMISE_DETAILS ' ||
319 'WHERE ' ||
320 'PROMISE_DETAIL_ID = :P_PROMISE_ID';
321
322 open l_cursor for l_SQL
323 using P_PROMISE_ID;
324 fetch l_cursor into l_delinquency_id, l_cust_account_id, l_cnsld_id, l_contract_id;*/
325 OPEN del_crs(P_PROMISE_ID);
326 FETCH del_crs INTO l_party_id, l_cust_account_id, l_cust_site_use_id, l_delinquency_id, l_cnsld_id, l_contract_id;
327 CLOSE del_crs;
328
329 -- Start for bug 8844974 PNAVEENK 27-AUG-2009
330 /* --get strategy level
331 select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40, 40)
332 into l_DefaultStrategyLevel
333 from iex_app_preferences_b
334 where preference_name = 'COLLECTIONS STRATEGY LEVEL'
335 and enabled_flag = 'Y'
336 and org_id is null; -- changed for bug 8708271 pnaveenk
337 --end bug#2369298 schekuri 24-Feb-2006*/
338
339 open c_strategy_level(l_party_id);
340 fetch c_strategy_level into l_DefaultStrategyLevel;
341 close c_strategy_level;
342
343 -- end for bug 8844974
344
345 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
346 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_delinquency_id = ' || l_delinquency_id);
347 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_cust_account_id = ' || l_cust_account_id);
348 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_cnsld_id = ' || l_cnsld_id);
349 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_contract_id = ' || l_contract_id);
350 END IF;
351
352 if l_delinquency_id is not null and l_cust_account_id is not null and l_cnsld_id is null and l_contract_id is null then --promise on delinquency
353 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
354 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': this is promise on delinquency. Move on with set strategy.');
355 END IF;
356 l_object_type := 'DELINQUENT';
357 l_object_id := l_delinquency_id;
358 else
359 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
360 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': This version does not support set strategy for this kind of promises. Return from the API');
361 END IF;
362 return;
363 end if;
364
365 --begin bug#2369298 schekuri 24-Feb-2006
366 --pass values of object_type object_id to strategy api
367 if l_DefaultStrategyLevel = 10 then
368 l_object_type := 'PARTY';
369 l_object_id := l_party_id;
370
371 elsif l_DefaultStrategyLevel = 20 then
372 l_object_type := 'ACCOUNT';
373 l_object_id := l_cust_account_id;
374
375 elsif l_DefaultStrategyLevel = 30 then
376 l_object_type := 'BILL_TO';
377 l_object_id := l_cust_site_use_id;
378
379 else
380 l_object_type := 'DELINQUENT';
381 l_object_id := l_delinquency_id;
382 end if;
383 --end bug#2369298 schekuri 24-Feb-2006
384
385 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
386 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling iex_strategy_pub.set_strategy...');
387 END IF;
388 iex_strategy_pub.set_strategy
389 (
390 P_Api_Version_Number => 2.0,
391 P_Init_Msg_List => 'F',
392 P_Commit => 'F',
393 p_validation_level => null,
394 X_Return_Status => l_return_status,
395 X_Msg_Count => l_msg_count,
396 X_Msg_Data => l_msg_data,
397 p_DelinquencyID => l_object_id,
398 p_ObjectType => l_object_type,
399 p_ObjectID => l_object_id,
400 p_Status => P_STATUS
401 );
402
403 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
404 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Return status ' || l_return_status);
405 END IF;
406 if l_return_status <> 'S' then
407 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
408 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Failed to set strategy');
409 END IF;
410 end if;
411
412 EXCEPTION
413 WHEN OTHERS THEN
414 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
415 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': in exception');
416 END IF;
417 end;
418
419
420 PROCEDURE SEND_FFM(P_PROMISE_ID IN NUMBER, P_PARTY_ID IN NUMBER)
421 IS
422 l_template_id number;
423 l_request_id number;
424 l_return_status varchar2(10);
425 l_msg_count number;
426 l_msg_data varchar2(200);
427 l_party_id number;
428 l_autofulfill varchar2(1);
429 begin
430 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
431 iex_debug_pub.LogMessage(G_PKG_NAME || '.SEND_FFM: start');
432 END IF;
433 l_autofulfill := fnd_profile.value('IEX_AUTO_FULFILL');
434 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
435 iex_debug_pub.LogMessage(G_PKG_NAME || '.SEND_FFM: l_autofulfill: ' || l_autofulfill);
436 END IF;
437
438 if l_autofulfill is not null and l_autofulfill = 'Y' then
439 l_template_id := to_number(fnd_profile.value('IEX_PROMISE_CONFIRM'));
440 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
441 iex_debug_pub.LogMessage(G_PKG_NAME || '.SEND_FFM: ptp ffm template_id = ' || l_template_id);
442 END IF;
443 if l_template_id is not null then
444 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
445 iex_debug_pub.LogMessage(G_PKG_NAME || '.SEND_FFM: Sending ffm ...');
446 END IF;
447 IEX_DUNNING_PVT.CALL_FFM(
448 p_api_version => 1.0,
449 p_init_msg_list => FND_API.G_TRUE,
450 p_commit => FND_API.G_TRUE,
451 p_key_name => 'promise_id',
452 p_key_id => p_promise_id,
453 p_template_id => l_template_id,
454 p_method => 'EMAIL',
455 p_party_id => p_party_id,
456 x_request_id => l_request_id,
457 x_return_status => l_return_status,
458 x_msg_count => l_msg_count,
459 x_msg_data => l_msg_data);
460
461 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
462 iex_debug_pub.LogMessage(G_PKG_NAME || '.SEND_FFM: request_id ' || l_request_id);
463 iex_debug_pub.LogMessage(G_PKG_NAME || '.SEND_FFM: Return status ' || l_return_status);
464 END IF;
465 IF l_return_status <> 'S' then
466 FND_MESSAGE.SET_NAME('IEX', 'IEX_FULFILLMENT_ERROR');
467 FND_MSG_PUB.Add;
468 end if;
469 end if;
470 end if;
471 EXCEPTION
472 WHEN OTHERS THEN
473 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
474 iex_debug_pub.LogMessage(G_PKG_NAME || '.SET_STRATEGY: in exception');
475 END IF;
476 end;
477
478
479 PROCEDURE START_PTP_WF(P_PROMISE_ID IN NUMBER, X_PROMISE_STATUS OUT NOCOPY VARCHAR2)
480 IS
481 l_wf_item_type varchar2(10);
482 l_wf_process varchar2(30); -- := 'PROMISE_WORKFLOW';
483 l_item_key varchar2(30);
484 l_result VARCHAR2(10);
485 l_return_status VARCHAR2(20);
486 l_approval_required VARCHAR2(3);
487 l_ptp_wf_item_key NUMBER;
488
489 -- generate new iex_ptp_wf item key
490 CURSOR ptp_wf_crs IS
491 select IEX_PTP_WF_S.NEXTVAL from dual;
492 begin
493 l_wf_process := 'PROMISE_WORKFLOW';
494 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
495 iex_debug_pub.LogMessage(G_PKG_NAME || '.start_ptp_wf: start');
496 END IF;
497 l_approval_required := fnd_profile.value('IEX_PTP_APPROVAL');
498 l_wf_item_type := fnd_profile.value('IEX_PTP_WF_ITEM_TYPE');
499 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
500 iex_debug_pub.LogMessage(G_PKG_NAME || '.start_ptp_wf: approval required = ' || l_approval_required);
501 iex_debug_pub.LogMessage(G_PKG_NAME || '.start_ptp_wf: item type = ' || l_wf_item_type);
502 END IF;
503
504 x_promise_status := 'COLLECTABLE';
505 if l_approval_required is not null and
506 l_approval_required = 'Y' and
507 l_wf_item_type is not null then
508
509 -- generate new iex_ptp_wf item key
510 OPEN ptp_wf_crs;
511 FETCH ptp_wf_crs INTO l_ptp_wf_item_key;
512 CLOSE ptp_wf_crs;
513
514 l_item_key := 'IEX_PTP_' || TO_CHAR(l_ptp_wf_item_key);
515 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
516 iex_debug_pub.LogMessage(G_PKG_NAME || '.start_ptp_wf: item key = ' || l_item_key);
517 END IF;
518
519 wf_engine.createprocess(
520 itemtype => l_wf_item_type,
521 itemkey => l_item_key,
522 process => l_wf_process);
523 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
524 iex_debug_pub.LogMessage(G_PKG_NAME || '.start_ptp_wf: After createprocess');
525 END IF;
526
527 wf_engine.setitemattrnumber(
528 itemtype => l_wf_item_type,
529 itemkey => l_item_key,
530 aname => 'PROMISE_ID',
531 avalue => p_promise_id);
532 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
533 iex_debug_pub.LogMessage(G_PKG_NAME || '.start_ptp_wf: After setitemattrnumber');
534 END IF;
535
536 wf_engine.startprocess(
537 itemtype => l_wf_item_type,
538 itemkey => l_item_key);
539 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
540 iex_debug_pub.LogMessage(G_PKG_NAME || '.start_ptp_wf: After startprocess');
541 END IF;
542
543 wf_engine.ItemStatus(
544 itemtype => l_wf_item_type,
545 itemkey => l_item_key,
546 status => l_return_status,
547 result => l_result);
548
549 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
550 iex_debug_pub.LogMessage(G_PKG_NAME || '.start_ptp_wf: l_return_status = ' || l_return_status);
551 iex_debug_pub.LogMessage(G_PKG_NAME || '.start_ptp_wf: l_result = ' || l_result);
552 END IF;
553
554 if l_return_status = 'COMPLETE' THEN
555 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
556 iex_debug_pub.LogMessage(G_PKG_NAME || '.start_ptp_wf: Set promise status to PENDING');
557 END IF;
558 x_promise_status := 'PENDING';
559 end if;
560 end if;
561 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
562 iex_debug_pub.LogMessage(G_PKG_NAME || '.start_ptp_wf: return_status = ' || x_promise_status);
563 iex_debug_pub.LogMessage(G_PKG_NAME || '.start_ptp_wf: end');
564 END IF;
565 EXCEPTION
566 WHEN OTHERS THEN
567 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
568 iex_debug_pub.LogMessage(G_PKG_NAME || '.start_ptp_wf: In start_ptp_wf exception');
569 END IF;
570 end;
571
572
573 PROCEDURE GET_BROKEN_ON_DATE(P_PROMISE_DATE IN DATE, X_BROKEN_ON_DATE OUT NOCOPY DATE)
574 IS
575 l_grace_period number;
576 l_broken_on_date date;
577 begin
578 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
579 iex_debug_pub.LogMessage(G_PKG_NAME || '.get_broken_on_date: start');
580 END IF;
581 l_grace_period := to_number(nvl(fnd_profile.value('IEX_PTP_GRACE_PERIOD'), '0'));
582 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
583 iex_debug_pub.LogMessage(G_PKG_NAME || '.get_broken_on_date: grace period = ' || l_grace_period);
584 END IF;
585 l_broken_on_date := p_promise_date + l_grace_period;
586 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
587 iex_debug_pub.LogMessage(G_PKG_NAME || '.get_broken_on_date: broken on date = ' || l_broken_on_date);
588 END IF;
589 x_broken_on_date := l_broken_on_date;
590 end;
591
592
593 PROCEDURE VALIDATE_INSERT_INPUT(P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_INSRT_REC_TYPE)
594 IS
595 Type refCur is Ref Cursor;
596
597 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_INSERT_INPUT';
598 l_validation_item varchar2(100);
599 l_cursor refCur;
600 l_SQL VARCHAR2(10000);
601 l_result_num number;
602 l_result_varchar varchar2(100);
603 l_fun_currency varchar2(15);
604 l_return_status VARCHAR2(1);
605 l_msg_count NUMBER;
606 l_msg_data VARCHAR2(32767);
607
608 begin
609 /* validate promise target */
610 l_validation_item := 'P_PROMISE_REC.PROMISE_TARGET';
611 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
612 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
613 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.PROMISE_TARGET);
614 END IF;
615 if P_PROMISE_REC.PROMISE_TARGET is null or
616 (P_PROMISE_REC.PROMISE_TARGET <> 'ACCOUNTS' and
617 P_PROMISE_REC.PROMISE_TARGET <> 'INVOICES' and
618 P_PROMISE_REC.PROMISE_TARGET <> 'CNSLD' and
619 P_PROMISE_REC.PROMISE_TARGET <> 'CONTRACTS')
620 then
621 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
622 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
623 END IF;
624 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
625 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
626 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
627 FND_MSG_PUB.Add;
628 RAISE FND_API.G_EXC_ERROR;
629 end if;
630
631 /* validate promise_amount */
632 l_validation_item := 'P_PROMISE_REC.PROMISE_AMOUNT';
633 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
634 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
635 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.PROMISE_AMOUNT);
636 END IF;
637 if P_PROMISE_REC.PROMISE_AMOUNT is null or P_PROMISE_REC.PROMISE_AMOUNT <= 0 then
638 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
639 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
640 END IF;
641 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
642 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
643 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
644 FND_MSG_PUB.Add;
645 RAISE FND_API.G_EXC_ERROR;
646 end if;
647
648 /* validate promise_date */
649 l_validation_item := 'P_PROMISE_REC.PROMISE_DATE';
650 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
651 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
652 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.PROMISE_DATE);
653 END IF;
654 if P_PROMISE_REC.PROMISE_DATE is null or trunc(P_PROMISE_REC.PROMISE_DATE) < trunc(sysdate) then
655 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
656 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
657 END IF;
658 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
659 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
660 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
661 FND_MSG_PUB.Add;
662 RAISE FND_API.G_EXC_ERROR;
663 end if;
664
665 /* validate currency */
666 l_validation_item := 'P_PROMISE_REC.CURRENCY_CODE';
667 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
668 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
669 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.CURRENCY_CODE);
670 END IF;
671 if P_PROMISE_REC.CURRENCY_CODE is null then
672 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
673 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
674 END IF;
675 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
676 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
677 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
678 FND_MSG_PUB.Add;
679 RAISE FND_API.G_EXC_ERROR;
680 end if;
681
682 /* validate resource_id */
683 l_validation_item := 'P_PROMISE_REC.TAKEN_BY_RESOURCE_ID';
684 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
685 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
686 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.TAKEN_BY_RESOURCE_ID);
687 END IF;
688 if P_PROMISE_REC.TAKEN_BY_RESOURCE_ID is null then
689 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
690 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
691 END IF;
692 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
693 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
694 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
695 FND_MSG_PUB.Add;
696 RAISE FND_API.G_EXC_ERROR;
697 end if;
698
699 /* get functional currency */
700 IEX_CURRENCY_PVT.GET_FUNCT_CURR(
701 p_api_version => 1.0,
702 p_init_msg_list => FND_API.G_FALSE,
703 p_commit => FND_API.G_FALSE,
704 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
705 x_return_status => l_return_status,
706 x_msg_count => l_msg_count,
707 x_msg_data => l_msg_data,
708 x_functional_currency => l_fun_currency);
709
710 /* validate payment_method */
711 l_validation_item := 'P_PROMISE_REC.PROMISE_PAYMENT_METHOD';
712 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
713 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
714 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.PROMISE_PAYMENT_METHOD);
715 END IF;
716 if P_PROMISE_REC.PROMISE_PAYMENT_METHOD is not null and rtrim(P_PROMISE_REC.PROMISE_PAYMENT_METHOD) <> '' then
717 l_SQL := 'SELECT ''X'' ' ||
718 'FROM IEX_LOOKUPS_V ' ||
719 'WHERE ' ||
720 'LOOKUP_TYPE = ''IEX_PAYMENT_TYPES'' AND LOOKUP_CODE = :P_PAYMENT_METHOD AND ' ||
721 'ENABLED_FLAG = ''Y''';
722
723 open l_cursor for l_SQL
724 using P_PROMISE_REC.PROMISE_PAYMENT_METHOD;
725 fetch l_cursor into l_result_varchar;
726
727 if l_cursor%rowcount = 0 or l_result_varchar is null then
728 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
729 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation: wrong payment method');
730 END IF;
731 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
732 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
733 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
734 FND_MSG_PUB.Add;
735 RAISE FND_API.G_EXC_ERROR;
736 end if;
737 else
738 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
739 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' is null - nothing to validate');
740 END IF;
741 end if;
742
743 /* validate cust_account_id */
744 l_validation_item := 'P_PROMISE_REC.CUST_ACCOUNT_ID';
745 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
746 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
747 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.CUST_ACCOUNT_ID);
748 END IF;
749 if P_PROMISE_REC.CUST_ACCOUNT_ID is null then
750 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
751 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
752 END IF;
753 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
754 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
755 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
756 FND_MSG_PUB.Add;
757 RAISE FND_API.G_EXC_ERROR;
758 end if;
759
760 /* validate promise object */
761 if P_PROMISE_REC.PROMISE_TARGET = 'ACCOUNTS' then
762
763 /* validate promise date */
764 l_validation_item := 'P_PROMISE_REC.PROMISE_DATE';
765 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
766 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item || ' for dublicates');
767 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.PROMISE_DATE);
768 END IF;
769
770 l_SQL := 'SELECT COUNT(1) ' ||
771 'FROM IEX_PROMISE_DETAILS ' ||
772 'WHERE ' ||
773 'DELINQUENCY_ID IS NULL AND CNSLD_INVOICE_ID IS NULL AND CONTRACT_ID IS NULL AND ' ||
774 'CUST_ACCOUNT_ID IS NOT NULL AND CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
775 'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
776 'STATUS in (''COLLECTABLE'', ''PENDING'')';
777
778 open l_cursor for l_SQL
779 using P_PROMISE_REC.CUST_ACCOUNT_ID, P_PROMISE_REC.PROMISE_DATE;
780 fetch l_cursor into l_result_num;
781
782 if l_cursor%rowcount = 0 or l_result_num > 0 then
783 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
784 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
785 END IF;
786 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
787 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
788 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
789 FND_MSG_PUB.Add;
790 RAISE FND_API.G_EXC_ERROR;
791 end if;
792
793 /* validate promise currency */
794 l_validation_item := 'P_PROMISE_REC.CURRENCY_CODE';
795 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
796 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
797 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.CURRENCY_CODE);
798 END IF;
799 if P_PROMISE_REC.CURRENCY_CODE <> l_fun_currency then
800 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
801 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
802 END IF;
803 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
804 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
805 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
806 FND_MSG_PUB.Add;
807 RAISE FND_API.G_EXC_ERROR;
808 end if;
809
810 elsif P_PROMISE_REC.PROMISE_TARGET = 'INVOICES' then
811
812 /* validate delinquency_id */
813 l_validation_item := 'P_PROMISE_REC.DELINQUENCY_ID';
814 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
815 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
816 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.DELINQUENCY_ID);
817 END IF;
818 if P_PROMISE_REC.DELINQUENCY_ID is null then
819 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
820 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
821 END IF;
822 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
823 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
824 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
825 FND_MSG_PUB.Add;
826 RAISE FND_API.G_EXC_ERROR;
827 end if;
828
829 /* validate promise date */
830 l_validation_item := 'P_PROMISE_REC.PROMISE_DATE';
831 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
832 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item || ' for dublicates');
833 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.PROMISE_DATE);
834 END IF;
835
836 l_SQL := 'SELECT COUNT(1) ' ||
837 'FROM IEX_PROMISE_DETAILS ' ||
838 'WHERE ' ||
839 'CNSLD_INVOICE_ID IS NULL AND CONTRACT_ID IS NULL AND ' ||
840 'CUST_ACCOUNT_ID IS NOT NULL AND CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
841 'DELINQUENCY_ID IS NOT NULL AND DELINQUENCY_ID = :P_DELINQUENCY_ID AND ' ||
842 'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
843 'STATUS in (''COLLECTABLE'', ''PENDING'')';
844
845 open l_cursor for l_SQL
846 using P_PROMISE_REC.CUST_ACCOUNT_ID,
847 P_PROMISE_REC.DELINQUENCY_ID,
848 P_PROMISE_REC.PROMISE_DATE;
849 fetch l_cursor into l_result_num;
850
851 if l_cursor%rowcount = 0 or l_result_num > 0 then
852 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
853 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
854 END IF;
855 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
856 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
857 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
858 FND_MSG_PUB.Add;
859 RAISE FND_API.G_EXC_ERROR;
860 end if;
861
862 /* validate promise currency */
863 l_validation_item := 'P_PROMISE_REC.CURRENCY_CODE';
864 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
865 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
866 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.CURRENCY_CODE);
867 END IF;
868 l_SQL := 'SELECT PSA.INVOICE_CURRENCY_CODE ' ||
869 'FROM AR_PAYMENT_SCHEDULES PSA, IEX_DELINQUENCIES DEL ' ||
870 'WHERE ' ||
871 'DEL.DELINQUENCY_ID = :P_DELINQUENCY_ID AND ' ||
872 'DEL.PAYMENT_SCHEDULE_ID = PSA.PAYMENT_SCHEDULE_ID';
873
874 open l_cursor for l_SQL
875 using P_PROMISE_REC.DELINQUENCY_ID;
876 fetch l_cursor into l_result_varchar;
877
878 if l_cursor%rowcount = 0 or l_result_varchar is null or l_result_varchar <> P_PROMISE_REC.CURRENCY_CODE then
879 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
880 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
881 END IF;
882 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
883 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
884 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
885 FND_MSG_PUB.Add;
886 RAISE FND_API.G_EXC_ERROR;
887 end if;
888
889 elsif P_PROMISE_REC.PROMISE_TARGET = 'CNSLD' then
890
891 /* validate consolidated_invoice_id */
892 l_validation_item := 'P_PROMISE_REC.CNSLD_INVOICE_ID';
893 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
894 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
895 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.CNSLD_INVOICE_ID);
896 END IF;
897 if P_PROMISE_REC.CNSLD_INVOICE_ID is null then
898 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
899 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
900 END IF;
901 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
902 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
903 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
904 FND_MSG_PUB.Add;
905 RAISE FND_API.G_EXC_ERROR;
906 end if;
907
908 /* validate promise date */
909 l_validation_item := 'P_PROMISE_REC.PROMISE_DATE';
910 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
911 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item || ' for dublicates');
912 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.PROMISE_DATE);
913 END IF;
914
915 l_SQL := 'SELECT COUNT(1) ' ||
916 'FROM IEX_PROMISE_DETAILS ' ||
917 'WHERE ' ||
918 'DELINQUENCY_ID IS NULL AND CONTRACT_ID IS NULL AND ' ||
919 'CUST_ACCOUNT_ID IS NOT NULL AND CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
920 'CNSLD_INVOICE_ID IS NOT NULL AND CNSLD_INVOICE_ID = :P_CNSLD_INVOICE_ID AND ' ||
921 'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
922 'STATUS in (''COLLECTABLE'', ''PENDING'')';
923
924 open l_cursor for l_SQL
925 using P_PROMISE_REC.CUST_ACCOUNT_ID,
926 P_PROMISE_REC.CNSLD_INVOICE_ID,
927 P_PROMISE_REC.PROMISE_DATE;
928 fetch l_cursor into l_result_num;
929
930 if l_cursor%rowcount = 0 or l_result_num > 0 then
931 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
932 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
933 END IF;
934 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
935 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
936 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
937 FND_MSG_PUB.Add;
938 RAISE FND_API.G_EXC_ERROR;
939 end if;
940
941 /* validate promise currency */
942 l_validation_item := 'P_PROMISE_REC.CURRENCY_CODE';
943 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
944 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
945 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.CURRENCY_CODE);
946 END IF;
947
948 l_SQL := 'SELECT CNSLD.CURRENCY_CODE ' ||
949 'FROM IEX_BPD_CNSLD_INV_REMAINING_V CNSLD ' ||
950 'WHERE ' ||
951 'CNSLD.CONSOLIDATED_INVOICE_ID = :P_CNSLD_ID';
952
953 open l_cursor for l_SQL
954 using P_PROMISE_REC.CNSLD_INVOICE_ID;
955 fetch l_cursor into l_result_varchar;
956
957 if l_cursor%rowcount = 0 or l_result_varchar is null or l_result_varchar <> P_PROMISE_REC.CURRENCY_CODE then
958 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
959 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
960 END IF;
961 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
962 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
963 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
964 FND_MSG_PUB.Add;
965 RAISE FND_API.G_EXC_ERROR;
966 end if;
967
968 elsif P_PROMISE_REC.PROMISE_TARGET = 'CONTRACTS' then
969
970 /* validate contract_id */
971 l_validation_item := 'P_PROMISE_REC.CONTRACT_ID';
972 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
973 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
974 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.CONTRACT_ID);
975 END IF;
976 if P_PROMISE_REC.CONTRACT_ID is null then
977 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
978 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
979 END IF;
980 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
981 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
982 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
983 FND_MSG_PUB.Add;
984 RAISE FND_API.G_EXC_ERROR;
985 end if;
986
987 /* validate promise date */
988 l_validation_item := 'P_PROMISE_REC.PROMISE_DATE';
989 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
990 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item || ' for dublicates');
991 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.PROMISE_DATE);
992 END IF;
993
994 l_SQL := 'SELECT COUNT(1) ' ||
995 'FROM IEX_PROMISE_DETAILS ' ||
996 'WHERE ' ||
997 'DELINQUENCY_ID IS NULL AND CNSLD_INVOICE_ID IS NULL AND ' ||
998 'CUST_ACCOUNT_ID IS NOT NULL AND CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
999 'CONTRACT_ID IS NOT NULL AND CONTRACT_ID = :P_CONTRACT_ID AND ' ||
1000 'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
1001 'STATUS in (''COLLECTABLE'', ''PENDING'')';
1002
1003 open l_cursor for l_SQL
1004 using P_PROMISE_REC.CUST_ACCOUNT_ID,
1005 P_PROMISE_REC.CONTRACT_ID,
1006 P_PROMISE_REC.PROMISE_DATE;
1007 fetch l_cursor into l_result_num;
1008
1009 if l_cursor%rowcount = 0 or l_result_num > 0 then
1010 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1011 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
1012 END IF;
1013 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
1014 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
1015 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
1016 FND_MSG_PUB.Add;
1017 RAISE FND_API.G_EXC_ERROR;
1018 end if;
1019
1020 /* validate promise currency */
1021 l_validation_item := 'P_PROMISE_REC.CURRENCY_CODE';
1022 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1023 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
1024 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.CURRENCY_CODE);
1025 END IF;
1026
1027 /* Fixed a perf bug 4932919
1028 l_SQL := 'SELECT cntr.CURRENCY_CODE ' ||
1029 'FROM iex_pay_okl_contracts_v cntr ' ||
1030 'WHERE ' ||
1031 'cntr.CONTRACT_ID = :P_CONTRACT_ID'; */
1032
1033 l_SQL := 'SELECT CURRENCY_CODE ' ||
1034 'FROM OKC_K_HEADERS_B ' ||
1035 'WHERE ' ||
1036 'ID = :P_CONTRACT_ID';
1037
1038
1039 open l_cursor for l_SQL
1040 using P_PROMISE_REC.CONTRACT_ID;
1041 fetch l_cursor into l_result_varchar;
1042
1043 if l_cursor%rowcount = 0 or l_result_varchar is null or l_result_varchar <> P_PROMISE_REC.CURRENCY_CODE then
1044 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1045 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
1046 END IF;
1047 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
1048 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
1049 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
1050 FND_MSG_PUB.Add;
1051 RAISE FND_API.G_EXC_ERROR;
1052 end if;
1053
1054 end if;
1055
1056 end;
1057
1058 PROCEDURE INSERT_PROMISE(
1059 P_API_VERSION IN NUMBER,
1060 P_INIT_MSG_LIST IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
1061 P_COMMIT IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
1062 P_VALIDATION_LEVEL IN NUMBER, -- DEFAULT FND_API.G_VALID_LEVEL_FULL,
1063 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1064 X_MSG_COUNT OUT NOCOPY NUMBER,
1065 X_MSG_DATA OUT NOCOPY VARCHAR2,
1066 P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_INSRT_REC_TYPE,
1067 X_PRORESP_REC OUT NOCOPY IEX_PROMISES_PUB.PRO_RESP_REC_TYPE)
1068 IS
1069 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_PROMISE';
1070 l_api_version CONSTANT NUMBER := 1.0;
1071 l_return_status VARCHAR2(1);
1072 l_msg_count NUMBER;
1073 l_msg_data VARCHAR2(32767);
1074
1075 i NUMBER;
1076 l_promise_id NUMBER;
1077 l_broken_on_date date;
1078 l_rowid VARCHAR2(100);
1079 l_promise_status varchar2(30);
1080 l_promise_state varchar2(30); -- := 'PROMISE';
1081
1082 l_note_payer_id NUMBER;
1083 l_payer_num_id NUMBER;
1084 l_payer_id VARCHAR2(80);
1085 l_payer_name HZ_PARTIES.PARTY_NAME%TYPE; --Changed the datatype for bug#5652085 by ehuh 2/28/07
1086 l_note_payer_type VARCHAR2(100);
1087 l_context_tab IEX_NOTES_PVT.CONTEXTS_TBL_TYPE;
1088 l_note_id NUMBER;
1089 l_org_id number; --Added for bug 7237026 barathsr 17-Nov-2008
1090
1091
1092 -- generate new promise detail
1093 CURSOR prd_genid_crs IS
1094 select IEX_PROMISE_DETAILS_S.NEXTVAL from dual;
1095 --Begin bug 7237026 17-Nov-2208 barathsr
1096 CURSOR c_org_id (p_del_id number) IS
1097 select org_id
1098 from iex_delinquencies_all
1099 where delinquency_id = p_del_id;
1100 --End bug 7237026 17-Nov-2208 barathsr
1101
1102 BEGIN
1103 l_promise_state := 'PROMISE';
1104
1105 -- Standard start of API savepoint
1106 SAVEPOINT INSERT_PROMISE_PVT;
1107
1108 -- Standard call to check for call compatibility
1109 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1111 END IF;
1112
1113 -- Initialize message list if p_init_msg_list is set to TRUE
1114 IF FND_API.To_Boolean(p_init_msg_list) THEN
1115 FND_MSG_PUB.initialize;
1116 END IF;
1117
1118 -- Initialize API return status to success
1119 l_return_status := FND_API.G_RET_STS_SUCCESS;
1120
1121 -- START OF BODY OF API
1122 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1123 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Start of body');
1124 END IF;
1125
1126 /* validate input */
1127 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1128 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating input...');
1129 END IF;
1130
1131 VALIDATE_INSERT_INPUT(P_PROMISE_REC);
1132
1133 /* validate payer info */
1134 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1135 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating parties...');
1136 END IF;
1137
1138 IEX_PAYMENTS_PUB.GET_PAYER_INFO(
1139 P_PAYER_PARTY_REL_ID => P_PROMISE_REC.PROMISED_BY_PARTY_REL_ID,
1140 P_PAYER_PARTY_ORG_ID => P_PROMISE_REC.PROMISED_BY_PARTY_ORG_ID,
1141 P_PAYER_PARTY_PER_ID => P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
1142 X_NOTE_PAYER_TYPE => l_note_payer_type,
1143 X_NOTE_PAYER_NUM_ID => l_note_payer_id,
1144 X_PAYER_NUM_ID => l_payer_num_id,
1145 X_PAYER_ID => l_payer_id,
1146 X_PAYER_NAME => l_payer_name);
1147
1148 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1149 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Generate new promise_id');
1150 END IF;
1151
1152 -- generate new promise id
1153 OPEN prd_genid_crs;
1154 FETCH prd_genid_crs INTO l_promise_id;
1155 CLOSE prd_genid_crs;
1156
1157 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1158 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': new promise_id = ' || l_promise_id);
1159 END IF;
1160
1161 -- get broken on date
1162 GET_BROKEN_ON_DATE(P_PROMISE_DATE => P_PROMISE_REC.PROMISE_DATE, X_BROKEN_ON_DATE => l_broken_on_date);
1163 -- start wf and return promise status
1164 START_PTP_WF(P_PROMISE_ID => l_promise_id, X_PROMISE_STATUS => l_promise_status);
1165
1166
1167 --Begin bug 7237026 17-Nov-2208 barathsr
1168 open c_org_id(P_PROMISE_REC.DELINQUENCY_ID);
1169 fetch c_org_id into l_org_id;
1170 close c_org_id;
1171
1172 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1173 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Doing insert...');
1174 END IF;
1175
1176 INSERT INTO IEX_PROMISE_DETAILS
1177 (
1178 PROMISE_DETAIL_ID,
1179 OBJECT_VERSION_NUMBER,
1180 PROGRAM_ID,
1181 LAST_UPDATE_DATE,
1182 LAST_UPDATED_BY,
1183 LAST_UPDATE_LOGIN,
1184 CREATION_DATE,
1185 CREATED_BY,
1186 PROMISE_DATE,
1187 PROMISE_AMOUNT,
1188 PROMISE_PAYMENT_METHOD,
1189 STATUS,
1190 ACCOUNT,
1191 PROMISE_ITEM_NUMBER,
1192 CURRENCY_CODE,
1193 CAMPAIGN_SCHED_ID,
1194 DELINQUENCY_ID,
1195 RESOURCE_ID,
1196 PROMISE_MADE_BY,
1197 CUST_ACCOUNT_ID,
1198 ATTRIBUTE_CATEGORY,
1199 ATTRIBUTE1,
1200 ATTRIBUTE2,
1201 ATTRIBUTE3,
1202 ATTRIBUTE4,
1203 ATTRIBUTE5,
1204 ATTRIBUTE6,
1205 ATTRIBUTE7,
1206 ATTRIBUTE8,
1207 ATTRIBUTE9,
1208 ATTRIBUTE10,
1209 ATTRIBUTE11,
1210 ATTRIBUTE12,
1211 ATTRIBUTE13,
1212 ATTRIBUTE14,
1213 ATTRIBUTE15,
1214 CNSLD_INVOICE_ID,
1215 CONTRACT_ID,
1216 BROKEN_ON_DATE,
1217 AMOUNT_DUE_REMAINING,
1218 STATE,
1219 ORG_ID
1220 )
1221 VALUES
1222 (
1223 l_promise_id,
1224 1.0,
1225 G_APP_ID,
1226 sysdate,
1227 G_USER_ID,
1228 G_LOGIN_ID,
1229 sysdate,
1230 G_USER_ID,
1231 P_PROMISE_REC.PROMISE_DATE,
1232 P_PROMISE_REC.PROMISE_AMOUNT,
1233 P_PROMISE_REC.PROMISE_PAYMENT_METHOD,
1234 l_promise_status,
1235 P_PROMISE_REC.ACCOUNT,
1236 P_PROMISE_REC.PROMISE_ITEM_NUMBER,
1237 P_PROMISE_REC.CURRENCY_CODE,
1238 P_PROMISE_REC.CAMPAIGN_SCHED_ID,
1239 P_PROMISE_REC.DELINQUENCY_ID,
1240 P_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
1241 P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
1242 P_PROMISE_REC.CUST_ACCOUNT_ID,
1243 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE_CATEGORY,
1244 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE1,
1245 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE2,
1246 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE3,
1247 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE4,
1248 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE5,
1249 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE6,
1250 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE7,
1251 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE8,
1252 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE9,
1253 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE10,
1254 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE11,
1255 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE12,
1256 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE13,
1257 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE14,
1258 P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE15,
1259 P_PROMISE_REC.CNSLD_INVOICE_ID,
1260 P_PROMISE_REC.CONTRACT_ID,
1261 l_broken_on_date,
1262 P_PROMISE_REC.PROMISE_AMOUNT,
1263 l_promise_state,
1264 l_org_id
1265 );
1266
1267 --End bug 7237026 17-Nov-2208 barathsr
1268
1269 X_PRORESP_REC.PROMISE_ID := l_promise_id;
1270 X_PRORESP_REC.STATUS := l_promise_status;
1271 X_PRORESP_REC.STATE := l_promise_state;
1272
1273 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1274 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Insert OK');
1275 END IF;
1276
1277 If nvl(fnd_profile.value('IEX_ALLOW_DUN_FULL_PROMISE'),'N')= 'Y' then -- added for bug 14318148
1278 --start
1279 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1280 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
1281 END IF;
1282 update_del_stage_level (
1283 p_promise_id => l_promise_id,
1284 X_RETURN_STATUS => l_return_status,
1285 X_MSG_COUNT => l_msg_count,
1286 X_MSG_DATA => l_msg_data);
1287
1288 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1289 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
1290 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Status = ' || L_RETURN_STATUS);
1291 END IF;
1292 -- check for errors
1293 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1294 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1295 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
1296 END IF;
1297 RAISE FND_API.G_EXC_ERROR;
1298 END IF;
1299 --end
1300 End If; -- added for bug 14318148
1301 -- inserting a note
1302 if P_PROMISE_REC.NOTE is not null then
1303
1304 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1305 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Going to build context for note...');
1306 END IF;
1307
1308 i := 1;
1309 /* assigning source_object and adding parties into note context */
1310 if l_note_payer_type = 'PARTY_RELATIONSHIP' then
1311 l_context_tab(i).context_type := 'PARTY';
1312 l_context_tab(i).context_id := P_PROMISE_REC.PROMISED_BY_PARTY_ORG_ID;
1313 i := i + 1;
1314 l_context_tab(i).context_type := 'PARTY';
1315 l_context_tab(i).context_id := P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID;
1316 i := i + 1;
1317 end if;
1318
1319 /* adding account into note context */
1320 l_context_tab(i).context_type := 'IEX_ACCOUNT';
1321 l_context_tab(i).context_id := P_PROMISE_REC.CUST_ACCOUNT_ID;
1322 i := i + 1;
1323
1324 l_context_tab(i).context_type := 'IEX_PROMISE';
1325 l_context_tab(i).context_id := l_promise_id;
1326 i := i + 1;
1327
1328 FOR i IN 1..l_context_tab.COUNT LOOP
1329 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1330 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_context_tab(' || i || ').context_type = ' || l_context_tab(i).context_type);
1331 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_context_tab(' || i || ').context_id = ' || l_context_tab(i).context_id);
1332 END IF;
1333 END LOOP;
1334
1335 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1336 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling IEX_NOTES_PVT.Create_Note...');
1337 END IF;
1338
1339 IEX_NOTES_PVT.Create_Note(
1340 P_API_VERSION => 1.0,
1341 P_INIT_MSG_LIST => 'F',
1342 P_COMMIT => 'F',
1343 P_VALIDATION_LEVEL => 100,
1344 X_RETURN_STATUS => l_return_status,
1345 X_MSG_COUNT => l_msg_count,
1346 X_MSG_DATA => l_msg_data,
1347 p_source_object_id => l_promise_id, -- Fixed by Ehuhh 02/05/-7 for a bug 5763697 l_note_payer_id,
1348 p_source_object_code => 'IEX_PROMISE', -- Fixed by Ehuhh 02/05/-7 for a bug 5763697 'PARTY',
1349 p_note_type => 'IEX_PROMISE',
1350 p_notes => P_PROMISE_REC.NOTE,
1351 p_contexts_tbl => l_context_tab,
1352 x_note_id => l_note_id);
1353
1354 X_PRORESP_REC.NOTE_ID := l_note_id;
1355
1356 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1357 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to IEX_NOTES_PVT.Create_Note');
1358 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Status = ' || L_RETURN_STATUS);
1359 END IF;
1360
1361 -- check for errors
1362 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1363 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1364 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': IEX_NOTES_PVT.Create_Note failed');
1365 END IF;
1366 RAISE FND_API.G_EXC_ERROR;
1367 END IF;
1368 else
1369 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1370 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': no note to save');
1371 END IF;
1372 end if;
1373
1374 /* SEND_FFM and SET_STRATEGY should be processed on the client
1375
1376 -- sending ffm
1377 --SEND_FFM(P_PROMISE_ID => l_promise_id, P_PARTY_ID => l_note_payer_id);
1378
1379 -- setting strategy
1380 --SET_STRATEGY(P_PROMISE_ID => l_promise_id, P_STATUS => 'ONHOLD');
1381 */
1382
1383 -- END OF BODY OF API
1384
1385 -- Standard check of p_commit.
1386 IF FND_API.To_Boolean( p_commit ) THEN
1387 COMMIT WORK;
1388 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1389 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': commited');
1390 END IF;
1391 END IF;
1392
1393 x_return_status := l_return_status;
1394 -- Standard call to get message count and if count is 1, get message info
1395 FND_MSG_PUB.Count_And_Get(
1396 p_encoded => FND_API.G_FALSE,
1397 p_count => x_msg_count,
1398 p_data => x_msg_data);
1399
1400 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1401 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': end of API');
1402 END IF;
1403
1404 EXCEPTION
1405 WHEN FND_API.G_EXC_ERROR THEN
1406 ROLLBACK TO INSERT_PROMISE_PVT;
1407 x_return_status := FND_API.G_RET_STS_ERROR;
1408 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1409 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1410 ROLLBACK TO INSERT_PROMISE_PVT;
1411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1412 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1413 WHEN OTHERS THEN
1414 ROLLBACK TO INSERT_PROMISE_PVT;
1415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1416 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1417 THEN
1418 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1419 END IF;
1420 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1421
1422 END;
1423
1424 PROCEDURE VALIDATE_UPDATE_INPUT(P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_UPDT_REC_TYPE)
1425 IS
1426 Type refCur is Ref Cursor;
1427
1428 l_validation_item varchar2(100);
1429 l_cursor refCur;
1430 l_SQL VARCHAR2(10000);
1431 l_result_num number;
1432 l_result_varchar varchar2(100);
1433 l_fun_currency varchar2(15);
1434 l_return_status VARCHAR2(1);
1435 l_msg_count NUMBER;
1436 l_msg_data VARCHAR2(32767);
1437 l_procedure varchar2(50); -- := 'VALIDATE_UPDATE_INPUT';
1438 l_promise_status varchar2(30);
1439 l_promise_state varchar2(30);
1440 l_del_id number;
1441 l_cust_id number;
1442 l_cnsld_id number;
1443 l_cntr_id number;
1444 l_where_clause varchar2(2000);
1445 l_promise_amount number;
1446 l_remaining_amount number;
1447 l_str_del1 varchar2(100); -- := ' AND DELINQUENCY_ID = ';
1448 l_str_del2 varchar2(100); -- := ' AND DELINQUENCY_ID is null';
1449 l_str_cnsld1 varchar2(100); -- := ' AND CNSLD_INVOICE_ID = ';
1450 l_str_cnsld2 varchar2(100); -- := ' AND CNSLD_INVOICE_ID is null';
1451 l_str_cnt1 varchar2(100); -- := ' AND CONTRACT_ID = ';
1452 l_str_cnt2 varchar2(100); -- := ' AND CONTRACT_ID is null';
1453 l_str_select varchar2(1000); -- := 'SELECT COUNT(1) ' ||
1454 -- 'FROM IEX_PROMISE_DETAILS ' ||
1455 -- 'WHERE ';
1456 l_str_cond varchar2(1000); -- := ' AND ' ||
1457 -- 'promise_detail_id <> :P_PROMISE_ID AND ' ||
1458 -- 'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
1459 -- 'STATUS in (''COLLECTABLE'', ''PENDING'')';
1460
1461 begin
1462 l_procedure := 'VALIDATE_UPDATE_INPUT';
1463 l_str_del1 := ' AND DELINQUENCY_ID = ';
1464 l_str_del2 := ' AND DELINQUENCY_ID is null';
1465 l_str_cnsld1 := ' AND CNSLD_INVOICE_ID = ';
1466 l_str_cnsld2 := ' AND CNSLD_INVOICE_ID is null';
1467 l_str_cnt1 := ' AND CONTRACT_ID = ';
1468 l_str_cnt2 := ' AND CONTRACT_ID is null';
1469 l_str_select := 'SELECT COUNT(1) ' ||
1470 'FROM IEX_PROMISE_DETAILS ' ||
1471 'WHERE ';
1472 l_str_cond := ' AND ' ||
1473 'promise_detail_id <> :P_PROMISE_ID AND ' ||
1474 'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
1475 'STATUS in (''COLLECTABLE'', ''PENDING'')';
1476 /* validate promise id */
1477 l_validation_item := 'P_PROMISE_REC.PROMISE_ID';
1478 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1479 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
1480 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.PROMISE_ID);
1481 END IF;
1482 if P_PROMISE_REC.PROMISE_ID is null then
1483 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1484 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation');
1485 END IF;
1486 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
1487 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
1488 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
1489 FND_MSG_PUB.Add;
1490 RAISE FND_API.G_EXC_ERROR;
1491 end if;
1492
1493 /* validate promise_amount */
1494 l_validation_item := 'P_PROMISE_REC.PROMISE_AMOUNT';
1495 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1496 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
1497 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.PROMISE_AMOUNT);
1498 END IF;
1499 if P_PROMISE_REC.PROMISE_AMOUNT is null or P_PROMISE_REC.PROMISE_AMOUNT <= 0 then
1500 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1501 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation');
1502 END IF;
1503 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
1504 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
1505 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
1506 FND_MSG_PUB.Add;
1507 RAISE FND_API.G_EXC_ERROR;
1508 end if;
1509
1510 /* validate resource_id */
1511 l_validation_item := 'P_PROMISE_REC.TAKEN_BY_RESOURCE_ID';
1512 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1513 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
1514 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.TAKEN_BY_RESOURCE_ID);
1515 END IF;
1516 if P_PROMISE_REC.TAKEN_BY_RESOURCE_ID is null then
1517 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1518 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation: resource id must be set');
1519 END IF;
1520 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
1521 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
1522 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
1523 FND_MSG_PUB.Add;
1524 RAISE FND_API.G_EXC_ERROR;
1525 end if;
1526
1527 /* pull data from db to do some validation */
1528 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1529 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating db data');
1530 END IF;
1531 l_SQL := 'SELECT CUST_ACCOUNT_ID, DELINQUENCY_ID, CNSLD_INVOICE_ID, CONTRACT_ID, STATUS, STATE, PROMISE_AMOUNT, AMOUNT_DUE_REMAINING ' ||
1532 'FROM IEX_PROMISE_DETAILS ' ||
1533 'WHERE ' ||
1534 'PROMISE_DETAIL_ID = :P_PROMISE_ID';
1535
1536 open l_cursor for l_SQL
1537 using P_PROMISE_REC.PROMISE_ID;
1538 fetch l_cursor into l_cust_id, l_del_id, l_cnsld_id, l_cntr_id, l_promise_status, l_promise_state, l_promise_amount, l_remaining_amount;
1539 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1540 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': From db:');
1541 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Cust_account_id = ' || l_cust_id);
1542 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Delinquency_id = ' || l_promise_status);
1543 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Cnsld_id = ' || l_cnsld_id);
1544 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Contract_id = ' || l_cntr_id);
1545 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Promise status = ' || l_promise_status);
1546 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Promise state = ' || l_promise_state);
1547 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Promise amount = ' || l_promise_amount);
1548 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Promise remaining amount = ' || l_remaining_amount);
1549 END IF;
1550
1551 /* validate promise_status */
1552 l_validation_item := 'STATUS';
1553 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1554 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
1555 END IF;
1556 if l_promise_status <> 'COLLECTABLE' then
1557 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1558 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation: status is not COLLECTABLE');
1559 END IF;
1560 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
1561 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
1562 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
1563 FND_MSG_PUB.Add;
1564 RAISE FND_API.G_EXC_ERROR;
1565 end if;
1566
1567 /* validate promise_state */
1568 l_validation_item := 'STATE';
1569 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1570 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
1571 END IF;
1572 if l_promise_state <> 'PROMISE' then
1573 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1574 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation: state is not PROMISE');
1575 END IF;
1576 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
1577 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
1578 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
1579 FND_MSG_PUB.Add;
1580 RAISE FND_API.G_EXC_ERROR;
1581 end if;
1582
1583 /* validate remaining amount */
1584 l_validation_item := 'AMOUNT_DUE_REMAINING';
1585 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1586 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
1587 END IF;
1588 if l_remaining_amount <> l_promise_amount then
1589 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1590 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation: remaining amount <> promise amount');
1591 END IF;
1592 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
1593 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
1594 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
1595 FND_MSG_PUB.Add;
1596 RAISE FND_API.G_EXC_ERROR;
1597 end if;
1598
1599 /* validate promise_date */
1600 l_validation_item := 'P_PROMISE_REC.PROMISE_DATE';
1601 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1602 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
1603 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.PROMISE_DATE);
1604 END IF;
1605 if P_PROMISE_REC.PROMISE_DATE is null or trunc(P_PROMISE_REC.PROMISE_DATE) < trunc(sysdate) then
1606 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1607 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation: promise_date must be >= current date');
1608 END IF;
1609 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
1610 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
1611 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
1612 FND_MSG_PUB.Add;
1613 RAISE FND_API.G_EXC_ERROR;
1614 end if;
1615
1616 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1617 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item || ' for dublicates');
1618 END IF;
1619
1620 /* building sql stmt to check for duplicates */
1621 l_where_clause := 'CUST_ACCOUNT_ID = ' || l_cust_id;
1622
1623
1624 if l_del_id is not null then
1625 l_where_clause := l_where_clause || l_str_del1 || l_del_id;
1626 else
1627 l_where_clause := l_where_clause || l_str_del2;
1628 end if;
1629
1630 if l_cnsld_id is not null then
1631 l_where_clause := l_where_clause || l_str_cnsld1 || l_cnsld_id;
1632 else
1633 l_where_clause := l_where_clause || l_str_cnsld2;
1634 end if;
1635
1636 if l_cntr_id is not null then
1637 l_where_clause := l_where_clause || l_str_cnt1 || l_cntr_id;
1638 else
1639 l_where_clause := l_where_clause || l_str_cnt2;
1640 end if;
1641
1642 l_SQL := l_str_select || l_where_clause || l_str_cond;
1643
1644 /* fix bind varviolation error
1645 l_SQL := 'SELECT COUNT(1) ' ||
1646 'FROM IEX_PROMISE_DETAILS ' ||
1647 'WHERE ' || l_where_clause || ' AND ' ||
1648 'promise_detail_id <> :P_PROMISE_ID AND ' ||
1649 'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
1650 'STATUS in (''COLLECTABLE'', ''PENDING'')';
1651 */
1652
1653 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1654 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': l_SQL = ' || l_SQL);
1655 END IF;
1656
1657 open l_cursor for l_SQL
1658 using P_PROMISE_REC.PROMISE_ID,
1659 P_PROMISE_REC.PROMISE_DATE;
1660 fetch l_cursor into l_result_num;
1661
1662 if l_cursor%rowcount = 0 or l_result_num > 0 then
1663 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1664 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation: found promise date duplication');
1665 END IF;
1666 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
1667 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
1668 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
1669 FND_MSG_PUB.Add;
1670 RAISE FND_API.G_EXC_ERROR;
1671 end if;
1672
1673 /* validate payment_method */
1674 l_validation_item := 'P_PROMISE_REC.PROMISE_PAYMENT_METHOD';
1675 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1676 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
1677 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' = ''' || rtrim(P_PROMISE_REC.PROMISE_PAYMENT_METHOD) || '''');
1678 END IF;
1679 if P_PROMISE_REC.PROMISE_PAYMENT_METHOD is not null and rtrim(P_PROMISE_REC.PROMISE_PAYMENT_METHOD) <> '' then
1680 l_SQL := 'SELECT ''X'' ' ||
1681 'FROM IEX_LOOKUPS_V ' ||
1682 'WHERE ' ||
1683 'LOOKUP_TYPE = ''IEX_PAYMENT_TYPES'' AND LOOKUP_CODE = :P_PAYMENT_METHOD AND ' ||
1684 'ENABLED_FLAG = ''Y''';
1685
1686 open l_cursor for l_SQL
1687 using P_PROMISE_REC.PROMISE_PAYMENT_METHOD;
1688 fetch l_cursor into l_result_varchar;
1689
1690 if l_cursor%rowcount = 0 or l_result_varchar is null then
1691 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1692 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation: wrong payment method');
1693 END IF;
1694 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
1695 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
1696 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
1697 FND_MSG_PUB.Add;
1698 RAISE FND_API.G_EXC_ERROR;
1699 end if;
1700 else
1701 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1702 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' is null - nothing to validate');
1703 END IF;
1704 end if;
1705
1706 end;
1707
1708 PROCEDURE UPDATE_PROMISE(
1709 P_API_VERSION IN NUMBER,
1710 P_INIT_MSG_LIST IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
1711 P_COMMIT IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
1712 P_VALIDATION_LEVEL IN NUMBER, -- DEFAULT FND_API.G_VALID_LEVEL_FULL,
1713 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1714 X_MSG_COUNT OUT NOCOPY NUMBER,
1715 X_MSG_DATA OUT NOCOPY VARCHAR2,
1716 P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_UPDT_REC_TYPE,
1717 X_PRORESP_REC OUT NOCOPY IEX_PROMISES_PUB.PRO_RESP_REC_TYPE)
1718 IS
1719 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PROMISE';
1720 l_api_version CONSTANT NUMBER := 1.0;
1721 l_return_status VARCHAR2(1);
1722 l_msg_count NUMBER;
1723 l_msg_data VARCHAR2(32767);
1724
1725 i NUMBER;
1726 l_promise_id NUMBER;
1727 l_broken_on_date date;
1728 l_promise_status varchar2(30);
1729
1730 l_note_payer_id NUMBER;
1731 l_payer_num_id NUMBER;
1732 l_payer_id VARCHAR2(80);
1733 l_payer_name HZ_PARTIES.PARTY_NAME%TYPE; --Changed the datatype for bug#5652085 by ehuh 2/28/07
1734 l_note_payer_type VARCHAR2(100);
1735 l_context_tab IEX_NOTES_PVT.CONTEXTS_TBL_TYPE;
1736 l_note_id NUMBER;
1737 l_cust_id number;
1738 l_SQL VARCHAR2(1000);
1739 Type refCur is Ref Cursor;
1740 l_cursor refCur;
1741
1742 BEGIN
1743 -- Standard start of API savepoint
1744 SAVEPOINT UPDATE_PROMISE_PVT;
1745
1746 -- Standard call to check for call compatibility
1747 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1748 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1749 END IF;
1750
1751 -- Initialize message list if p_init_msg_list is set to TRUE
1752 IF FND_API.To_Boolean(p_init_msg_list) THEN
1753 FND_MSG_PUB.initialize;
1754 END IF;
1755
1756 -- Initialize API return status to success
1757 l_return_status := FND_API.G_RET_STS_SUCCESS;
1758
1759 -- START OF BODY OF API
1760 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1761 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Start of body');
1762 END IF;
1763
1764 /* validate input */
1765 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1766 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating input...');
1767 END IF;
1768 VALIDATE_UPDATE_INPUT(P_PROMISE_REC);
1769
1770 /* validate promiser info */
1771 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1772 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating parties...');
1773 END IF;
1774 IEX_PAYMENTS_PUB.GET_PAYER_INFO(
1775 P_PAYER_PARTY_REL_ID => P_PROMISE_REC.PROMISED_BY_PARTY_REL_ID,
1776 P_PAYER_PARTY_ORG_ID => P_PROMISE_REC.PROMISED_BY_PARTY_ORG_ID,
1777 P_PAYER_PARTY_PER_ID => P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
1778 X_NOTE_PAYER_TYPE => l_note_payer_type,
1779 X_NOTE_PAYER_NUM_ID => l_note_payer_id,
1780 X_PAYER_NUM_ID => l_payer_num_id,
1781 X_PAYER_ID => l_payer_id,
1782 X_PAYER_NAME => l_payer_name);
1783
1784 /*get broken_on date */
1785 GET_BROKEN_ON_DATE(P_PROMISE_DATE => P_PROMISE_REC.PROMISE_DATE, X_BROKEN_ON_DATE => l_broken_on_date);
1786 /* start workflow and get new promise status */
1787 START_PTP_WF(P_PROMISE_ID => l_promise_id, X_PROMISE_STATUS => l_promise_status);
1788
1789 /* do update */
1790 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1791 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Doing update...');
1792 END IF;
1793
1794 UPDATE iex_promise_details
1795 SET PROMISE_AMOUNT = P_PROMISE_REC.PROMISE_AMOUNT,
1796 AMOUNT_DUE_REMAINING = P_PROMISE_REC.PROMISE_AMOUNT,
1797 PROMISE_DATE = P_PROMISE_REC.PROMISE_DATE,
1798 BROKEN_ON_DATE = l_broken_on_date,
1799 STATUS = l_promise_status,
1800 PROMISE_PAYMENT_METHOD = P_PROMISE_REC.PROMISE_PAYMENT_METHOD,
1801 ACCOUNT = P_PROMISE_REC.ACCOUNT,
1802 PROMISE_ITEM_NUMBER = P_PROMISE_REC.PROMISE_ITEM_NUMBER,
1803 CAMPAIGN_SCHED_ID = P_PROMISE_REC.CAMPAIGN_SCHED_ID,
1804 ATTRIBUTE_CATEGORY = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE_CATEGORY,
1805 ATTRIBUTE1 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE1,
1806 ATTRIBUTE2 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE2,
1807 ATTRIBUTE3 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE3,
1808 ATTRIBUTE4 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE4,
1809 ATTRIBUTE5 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE5,
1810 ATTRIBUTE6 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE6,
1811 ATTRIBUTE7 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE7,
1812 ATTRIBUTE8 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE8,
1813 ATTRIBUTE9 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE9,
1814 ATTRIBUTE10 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE10,
1815 ATTRIBUTE11 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE11,
1816 ATTRIBUTE12 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE12,
1817 ATTRIBUTE13 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE13,
1818 ATTRIBUTE14 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE14,
1819 ATTRIBUTE15 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE15,
1820 RESOURCE_ID = P_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
1821 PROMISE_MADE_BY = P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
1822 PROGRAM_ID = G_APP_ID,
1823 last_update_date = sysdate,
1824 last_updated_by = G_USER_ID,
1825 LAST_UPDATE_LOGIN = G_LOGIN_ID
1826 where promise_detail_id = P_PROMISE_REC.PROMISE_ID;
1827
1828 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1829 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Update OK');
1830 END IF;
1831
1832 X_PRORESP_REC.PROMISE_ID := P_PROMISE_REC.PROMISE_ID;
1833 X_PRORESP_REC.STATUS := l_promise_status;
1834
1835 /* getting promise state */
1836 l_SQL := 'SELECT state FROM IEX_PROMISE_DETAILS WHERE PROMISE_DETAIL_ID = :P_PROMISE_ID';
1837
1838 open l_cursor for l_SQL
1839 using P_PROMISE_REC.PROMISE_ID;
1840 fetch l_cursor into X_PRORESP_REC.STATE;
1841 If nvl(fnd_profile.value('IEX_ALLOW_DUN_FULL_PROMISE'),'N')= 'Y' then -- added for bug 14318148
1842 --start
1843 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1844 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
1845 END IF;
1846 update_del_stage_level (
1847 p_promise_id => P_PROMISE_REC.PROMISE_ID,
1848 X_RETURN_STATUS => l_return_status,
1849 X_MSG_COUNT => l_msg_count,
1850 X_MSG_DATA => l_msg_data);
1851
1852 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1853 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
1854 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Status = ' || L_RETURN_STATUS);
1855 END IF;
1856 -- check for errors
1857 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1858 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1859 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
1860 END IF;
1861 RAISE FND_API.G_EXC_ERROR;
1862 END IF;
1863 --end
1864 End If; -- added for bug 14318148
1865 -- inserting a note
1866 if P_PROMISE_REC.NOTE is not null then
1867
1868 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1869 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Going to build context for note...');
1870 END IF;
1871
1872 i := 1;
1873 /* assigning source_object and adding parties into note context */
1874 if l_note_payer_type = 'PARTY_RELATIONSHIP' then
1875 l_context_tab(i).context_type := 'PARTY';
1876 l_context_tab(i).context_id := P_PROMISE_REC.PROMISED_BY_PARTY_ORG_ID;
1877 i := i + 1;
1878 l_context_tab(i).context_type := 'PARTY';
1879 l_context_tab(i).context_id := P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID;
1880 i := i + 1;
1881 end if;
1882
1883 /* adding account into note context */
1884 l_SQL := 'SELECT CUST_ACCOUNT_ID ' ||
1885 'FROM IEX_PROMISE_DETAILS ' ||
1886 'WHERE ' ||
1887 'PROMISE_DETAIL_ID = :P_PROMISE_ID';
1888
1889 open l_cursor for l_SQL
1890 using P_PROMISE_REC.PROMISE_ID;
1891 fetch l_cursor into l_cust_id;
1892
1893 l_context_tab(i).context_type := 'IEX_ACCOUNT';
1894 l_context_tab(i).context_id := l_cust_id;
1895 i := i + 1;
1896
1897 l_context_tab(i).context_type := 'IEX_PROMISE';
1898 l_context_tab(i).context_id := P_PROMISE_REC.PROMISE_ID;
1899 i := i + 1;
1900
1901 FOR i IN 1..l_context_tab.COUNT LOOP
1902 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1903 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_context_tab(' || i || ').context_type = ' || l_context_tab(i).context_type);
1904 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_context_tab(' || i || ').context_id = ' || l_context_tab(i).context_id);
1905 END IF;
1906 END LOOP;
1907
1908 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1909 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling IEX_NOTES_PVT.Create_Note...');
1910 END IF;
1911
1912 IEX_NOTES_PVT.Create_Note(
1913 P_API_VERSION => 1.0,
1914 P_INIT_MSG_LIST => 'F',
1915 P_COMMIT => 'F',
1916 P_VALIDATION_LEVEL => 100,
1917 X_RETURN_STATUS => l_return_status,
1918 X_MSG_COUNT => l_msg_count,
1919 X_MSG_DATA => l_msg_data,
1920 p_source_object_id => P_PROMISE_REC.PROMISE_ID, -- Fixed by Ehuhh 02/05/-7 for a bug 5763697 l_note_payer_id,
1921 p_source_object_code => 'IEX_PROMISE', -- Fixed by Ehuhh 02/05/-7 for a bug 5763697 'PARTY',
1922 p_note_type => 'IEX_PROMISE',
1923 p_notes => P_PROMISE_REC.NOTE,
1924 p_contexts_tbl => l_context_tab,
1925 x_note_id => l_note_id);
1926
1927 X_PRORESP_REC.NOTE_ID := l_note_id;
1928
1929 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1930 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to IEX_NOTES_PVT.Create_Note');
1931 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Status = ' || L_RETURN_STATUS);
1932 END IF;
1933
1934 -- check for errors
1935 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1936 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1937 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': IEX_NOTES_PVT.Create_Note failed');
1938 END IF;
1939 RAISE FND_API.G_EXC_ERROR;
1940 END IF;
1941 else
1942 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1943 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': no note to save');
1944 END IF;
1945 end if;
1946
1947 /* SEND_FFM and SET_STRATEGY should be processed on the client
1948
1949 -- sending ffm
1950 --SEND_FFM(P_PROMISE_ID => P_PROMISE_REC.PROMISE_ID, P_PARTY_ID => l_note_payer_id);
1951
1952 -- setting strategy
1953 --SET_STRATEGY(P_PROMISE_ID => P_PROMISE_REC.PROMISE_ID, P_STATUS => 'ONHOLD');
1954 */
1955 -- commit if promise updated successfully
1956 IF FND_API.To_Boolean( p_commit ) THEN
1957 COMMIT WORK;
1958 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1959 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': commited');
1960 END IF;
1961 END IF;
1962
1963 -- END OF BODY OF API
1964
1965 x_return_status := l_return_status;
1966 -- Standard call to get message count and if count is 1, get message info
1967 FND_MSG_PUB.Count_And_Get(
1968 p_encoded => FND_API.G_FALSE,
1969 p_count => x_msg_count,
1970 p_data => x_msg_data);
1971
1972 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1973 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': end of API');
1974 END IF;
1975
1976 EXCEPTION
1977 WHEN FND_API.G_EXC_ERROR THEN
1978 ROLLBACK TO UPDATE_PROMISE_PVT;
1979 x_return_status := FND_API.G_RET_STS_ERROR;
1980 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1981 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1982 ROLLBACK TO UPDATE_PROMISE_PVT;
1983 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1984 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1985 WHEN OTHERS THEN
1986 ROLLBACK TO UPDATE_PROMISE_PVT;
1987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1988 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1989 THEN
1990 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1991 END IF;
1992 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1993
1994 END;
1995
1996
1997 PROCEDURE VALIDATE_CANCEL_INPUT(P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_CNCL_REC_TYPE)
1998 IS
1999 l_validation_item varchar2(100);
2000 l_procedure varchar2(50); -- := 'VALIDATE_CANCEL_INPUT';
2001
2002 begin
2003 l_procedure := 'VALIDATE_CANCEL_INPUT';
2004 /* validate promise id */
2005 l_validation_item := 'P_PROMISE_REC.PROMISE_ID';
2006 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2007 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
2008 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.PROMISE_ID);
2009 END IF;
2010 if P_PROMISE_REC.PROMISE_ID is null then
2011 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2012 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation');
2013 END IF;
2014 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2015 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
2016 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2017 FND_MSG_PUB.Add;
2018 RAISE FND_API.G_EXC_ERROR;
2019 end if;
2020
2021 /* validate resource_id */
2022 l_validation_item := 'P_PROMISE_REC.TAKEN_BY_RESOURCE_ID';
2023 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2024 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
2025 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.TAKEN_BY_RESOURCE_ID);
2026 END IF;
2027 if P_PROMISE_REC.TAKEN_BY_RESOURCE_ID is null then
2028 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2029 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation: resource id must be set');
2030 END IF;
2031 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2032 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
2033 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2034 FND_MSG_PUB.Add;
2035 RAISE FND_API.G_EXC_ERROR;
2036 end if;
2037
2038 end;
2039
2040 PROCEDURE CANCEL_PROMISE(
2041 P_API_VERSION IN NUMBER,
2042 P_INIT_MSG_LIST IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
2043 P_COMMIT IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
2044 P_VALIDATION_LEVEL IN NUMBER, -- DEFAULT FND_API.G_VALID_LEVEL_FULL,
2045 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2046 X_MSG_COUNT OUT NOCOPY NUMBER,
2047 X_MSG_DATA OUT NOCOPY VARCHAR2,
2048 P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_CNCL_REC_TYPE,
2049 X_PRORESP_REC OUT NOCOPY IEX_PROMISES_PUB.PRO_RESP_REC_TYPE)
2050 IS
2051 l_api_name CONSTANT VARCHAR2(30) := 'CANCEL_PROMISE';
2052 l_api_version CONSTANT NUMBER := 1.0;
2053 l_return_status VARCHAR2(1);
2054 l_msg_count NUMBER;
2055 l_msg_data VARCHAR2(32767);
2056
2057 i NUMBER;
2058 l_promise_id NUMBER;
2059 l_promise_status varchar2(30); -- := 'CANCELLED';
2060 l_note_payer_id NUMBER;
2061 l_payer_num_id NUMBER;
2062 l_payer_id VARCHAR2(80);
2063 l_payer_name HZ_PARTIES.PARTY_NAME%TYPE; --Changed the datatype for bug#5652085 by ehuh 2/28/07
2064 l_note_payer_type VARCHAR2(100);
2065 l_context_tab IEX_NOTES_PVT.CONTEXTS_TBL_TYPE;
2066 l_note_id NUMBER;
2067 l_cust_id number;
2068 l_SQL VARCHAR2(1000);
2069 Type refCur is Ref Cursor;
2070 l_cursor refCur;
2071
2072
2073 BEGIN
2074 l_promise_status := 'CANCELLED';
2075
2076 -- Standard start of API savepoint
2077 SAVEPOINT CANCEL_PROMISE_PVT;
2078
2079 -- Standard call to check for call compatibility
2080 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2082 END IF;
2083
2084 -- Initialize message list if p_init_msg_list is set to TRUE
2085 IF FND_API.To_Boolean(p_init_msg_list) THEN
2086 FND_MSG_PUB.initialize;
2087 END IF;
2088
2089 -- Initialize API return status to success
2090 l_return_status := FND_API.G_RET_STS_SUCCESS;
2091
2092 -- START OF BODY OF API
2093 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2094 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Start of body');
2095 END IF;
2096
2097 /* validate input */
2098 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2099 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating input...');
2100 END IF;
2101 VALIDATE_CANCEL_INPUT(P_PROMISE_REC);
2102
2103 /* validate promiser info */
2104 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2105 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating parties...');
2106 END IF;
2107 IEX_PAYMENTS_PUB.GET_PAYER_INFO(
2108 P_PAYER_PARTY_REL_ID => P_PROMISE_REC.PROMISED_BY_PARTY_REL_ID,
2109 P_PAYER_PARTY_ORG_ID => P_PROMISE_REC.PROMISED_BY_PARTY_ORG_ID,
2110 P_PAYER_PARTY_PER_ID => P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
2111 X_NOTE_PAYER_TYPE => l_note_payer_type,
2112 X_NOTE_PAYER_NUM_ID => l_note_payer_id,
2113 X_PAYER_NUM_ID => l_payer_num_id,
2114 X_PAYER_ID => l_payer_id,
2115 X_PAYER_NAME => l_payer_name);
2116
2117 /* do update */
2118 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2119 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Doing update...');
2120 END IF;
2121
2122 UPDATE iex_promise_details
2123 SET STATUS = l_promise_status,
2124 RESOURCE_ID = P_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
2125 PROMISE_MADE_BY = P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
2126 PROGRAM_ID = G_APP_ID,
2127 last_update_date = sysdate,
2128 last_updated_by = G_USER_ID,
2129 LAST_UPDATE_LOGIN = G_LOGIN_ID,
2130 ATTRIBUTE_CATEGORY = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE_CATEGORY, -- start added by snuthala for bug 10373547 12/16/2010
2131 ATTRIBUTE1 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE1,
2132 ATTRIBUTE2 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE2,
2133 ATTRIBUTE3 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE3,
2134 ATTRIBUTE4 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE4,
2135 ATTRIBUTE5 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE5,
2136 ATTRIBUTE6 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE6,
2137 ATTRIBUTE7 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE7,
2138 ATTRIBUTE8 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE8,
2139 ATTRIBUTE9 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE9,
2140 ATTRIBUTE10 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE10,
2141 ATTRIBUTE11 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE11,
2142 ATTRIBUTE12 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE12,
2143 ATTRIBUTE13 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE13,
2144 ATTRIBUTE14 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE14,
2145 ATTRIBUTE15 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE15 -- end added by snuthala for bug 10373547 12/16/2010
2146 where promise_detail_id = P_PROMISE_REC.PROMISE_ID;
2147
2148 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2149 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Update OK');
2150 END IF;
2151 X_PRORESP_REC.PROMISE_ID := P_PROMISE_REC.PROMISE_ID;
2152 X_PRORESP_REC.STATUS := l_promise_status;
2153
2154 /* getting promise state */
2155 l_SQL := 'SELECT state FROM IEX_PROMISE_DETAILS WHERE PROMISE_DETAIL_ID = :P_PROMISE_ID';
2156
2157 open l_cursor for l_SQL
2158 using P_PROMISE_REC.PROMISE_ID;
2159 fetch l_cursor into X_PRORESP_REC.STATE;
2160 If nvl(fnd_profile.value('IEX_ALLOW_DUN_FULL_PROMISE'),'N')= 'Y' then -- added for bug 14318148
2161 --start
2162 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2163 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
2164 END IF;
2165 update_del_stage_level (
2166 p_promise_id => P_PROMISE_REC.PROMISE_ID,
2167 X_RETURN_STATUS => l_return_status,
2168 X_MSG_COUNT => l_msg_count,
2169 X_MSG_DATA => l_msg_data);
2170
2171 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2172 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
2173 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Status = ' || L_RETURN_STATUS);
2174 END IF;
2175 -- check for errors
2176 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2177 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2178 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
2179 END IF;
2180 RAISE FND_API.G_EXC_ERROR;
2181 END IF;
2182 --end
2183 End If; -- added for bug 14318148
2184 -- inserting a note
2185 if P_PROMISE_REC.NOTE is not null then
2186
2187 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2188 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Going to build context for note...');
2189 END IF;
2190 i := 1;
2191 /* assigning source_object and adding parties into note context */
2192 if l_note_payer_type = 'PARTY_RELATIONSHIP' then
2193 l_context_tab(i).context_type := 'PARTY';
2194 l_context_tab(i).context_id := P_PROMISE_REC.PROMISED_BY_PARTY_ORG_ID;
2195 i := i + 1;
2196 l_context_tab(i).context_type := 'PARTY';
2197 l_context_tab(i).context_id := P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID;
2198 i := i + 1;
2199 end if;
2200
2201 /* adding account into note context */
2202 l_SQL := 'SELECT CUST_ACCOUNT_ID ' ||
2203 'FROM IEX_PROMISE_DETAILS ' ||
2204 'WHERE ' ||
2205 'PROMISE_DETAIL_ID = :P_PROMISE_ID';
2206
2207 open l_cursor for l_SQL
2208 using P_PROMISE_REC.PROMISE_ID;
2209 fetch l_cursor into l_cust_id;
2210
2211 l_context_tab(i).context_type := 'IEX_ACCOUNT';
2212 l_context_tab(i).context_id := l_cust_id;
2213 i := i + 1;
2214
2215 l_context_tab(i).context_type := 'IEX_PROMISE';
2216 l_context_tab(i).context_id := P_PROMISE_REC.PROMISE_ID;
2217 i := i + 1;
2218
2219 FOR i IN 1..l_context_tab.COUNT LOOP
2220 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2221 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_context_tab(' || i || ').context_type = ' || l_context_tab(i).context_type);
2222 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_context_tab(' || i || ').context_id = ' || l_context_tab(i).context_id);
2223 END IF;
2224 END LOOP;
2225
2226 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2227 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling IEX_NOTES_PVT.Create_Note...');
2228 END IF;
2229
2230 IEX_NOTES_PVT.Create_Note(
2231 P_API_VERSION => 1.0,
2232 P_INIT_MSG_LIST => 'F',
2233 P_COMMIT => 'F',
2234 P_VALIDATION_LEVEL => 100,
2235 X_RETURN_STATUS => l_return_status,
2236 X_MSG_COUNT => l_msg_count,
2237 X_MSG_DATA => l_msg_data,
2238 p_source_object_id => P_PROMISE_REC.PROMISE_ID, -- Fixed by Ehuhh 02/05/-7 for a bug 5763697 l_note_payer_id,
2239 p_source_object_code => 'IEX_PROMISE', -- Fixed by Ehuhh 02/05/-7 for a bug 5763697 'PARTY',
2240 p_note_type => 'IEX_PROMISE',
2241 p_notes => P_PROMISE_REC.NOTE,
2242 p_contexts_tbl => l_context_tab,
2243 x_note_id => l_note_id);
2244
2245 X_PRORESP_REC.NOTE_ID := l_note_id;
2246
2247 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2248 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to IEX_NOTES_PVT.Create_Note');
2249 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Status = ' || L_RETURN_STATUS);
2250 END IF;
2251
2252 -- check for errors
2253 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2254 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2255 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': IEX_NOTES_PVT.Create_Note failed');
2256 END IF;
2257 RAISE FND_API.G_EXC_ERROR;
2258 END IF;
2259 else
2260 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2261 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': no note to save');
2262 END IF;
2263 end if;
2264
2265 -- commit if promise updated successfully
2266 IF FND_API.To_Boolean( p_commit ) THEN
2267 COMMIT WORK;
2268 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2269 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': commited');
2270 END IF;
2271 END IF;
2272
2273 -- END OF BODY OF API
2274
2275 x_return_status := l_return_status;
2276 -- Standard call to get message count and if count is 1, get message info
2277 FND_MSG_PUB.Count_And_Get(
2278 p_encoded => FND_API.G_FALSE,
2279 p_count => x_msg_count,
2280 p_data => x_msg_data);
2281
2282 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2283 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': end of API');
2284 END IF;
2285
2286 EXCEPTION
2287 WHEN FND_API.G_EXC_ERROR THEN
2288 ROLLBACK TO CANCEL_PROMISE_PVT;
2289 x_return_status := FND_API.G_RET_STS_ERROR;
2290 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2291 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2292 ROLLBACK TO CANCEL_PROMISE_PVT;
2293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2294 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2295 WHEN OTHERS THEN
2296 ROLLBACK TO CANCEL_PROMISE_PVT;
2297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2298 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2299 THEN
2300 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2301 END IF;
2302 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2303
2304 END;
2305
2306
2307 PROCEDURE VALIDATE_MASS_INPUT(
2308 P_MASS_IDS_TBL IN DBMS_SQL.NUMBER_TABLE,
2309 P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_MASS_REC_TYPE)
2310 IS
2311 Type refCur is Ref Cursor;
2312
2313 l_cursor refCur;
2314 l_SQL VARCHAR2(10000);
2315 l_validation_item varchar2(100);
2316 l_procedure varchar2(50); -- := 'VALIDATE_MASS_INPUT';
2317 l_result_varchar varchar2(100);
2318 l_result_num number;
2319 i number;
2320
2321 begin
2322 l_procedure := 'VALIDATE_MASS_INPUT';
2323 /* validate delinquency table count */
2324 l_validation_item := 'P_MASS_IDS_TBL.COUNT';
2325 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2326 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
2327 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' = ' || P_MASS_IDS_TBL.COUNT);
2328 END IF;
2329 if P_MASS_IDS_TBL.COUNT = 0 then
2330 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2331 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation: no delinquencies were passed');
2332 END IF;
2333 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2334 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
2335 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2336 FND_MSG_PUB.Add;
2337 RAISE FND_API.G_EXC_ERROR;
2338 end if;
2339
2340 --commenting begin for bug 6717279 by gnramasa 25th Aug 08
2341 /* validate that all passed delinquencies belong to the same account */
2342 /*
2343 l_validation_item := 'P_MASS_IDS_TBL';
2344 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2345 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
2346 END IF;
2347 */
2348 /* building sql for validating ids */
2349 /*
2350 l_SQL := 'SELECT count(distinct CUST_ACCOUNT_ID) from iex_delinquencies where delinquency_id in (';
2351 FOR i IN 1..P_MASS_IDS_TBL.COUNT LOOP
2352 if i = 1 then
2353 l_SQL := l_SQL || P_MASS_IDS_TBL(i);
2354 else
2355 l_SQL := l_SQL || ',' || P_MASS_IDS_TBL(i);
2356 end if;
2357 END LOOP;
2358 l_SQL := l_SQL || ')';
2359 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2360 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': sql = ' || l_SQL);
2361 END IF;
2362
2363 open l_cursor for l_SQL;
2364 fetch l_cursor into l_result_num;
2365
2366 if l_result_num > 1 then
2367 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2368 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation: passed delinquencies belong to different accounts');
2369 END IF;
2370 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2371 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
2372 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2373 FND_MSG_PUB.Add;
2374 RAISE FND_API.G_EXC_ERROR;
2375 end if;
2376 */
2377 --commenting end for bug 6717279 by gnramasa 25th Aug 08
2378 /* validate promise_date */
2379 l_validation_item := 'P_PROMISE_REC.PROMISE_DATE';
2380 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2381 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
2382 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.PROMISE_DATE);
2383 END IF;
2384 if P_PROMISE_REC.PROMISE_DATE is null or trunc(P_PROMISE_REC.PROMISE_DATE) < trunc(sysdate) then
2385 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2386 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation: promise_date must be >= current date');
2387 END IF;
2388 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2389 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
2390 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2391 FND_MSG_PUB.Add;
2392 RAISE FND_API.G_EXC_ERROR;
2393 end if;
2394
2395 /* validate resource_id */
2396 l_validation_item := 'P_PROMISE_REC.TAKEN_BY_RESOURCE_ID';
2397 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2398 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
2399 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' = ' || P_PROMISE_REC.TAKEN_BY_RESOURCE_ID);
2400 END IF;
2401 if P_PROMISE_REC.TAKEN_BY_RESOURCE_ID is null then
2402 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2403 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation: resource id must be set');
2404 END IF;
2405 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2406 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
2407 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2408 FND_MSG_PUB.Add;
2409 RAISE FND_API.G_EXC_ERROR;
2410 end if;
2411
2412 /* validate payment_method */
2413 l_validation_item := 'P_PROMISE_REC.PROMISE_PAYMENT_METHOD';
2414 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2415 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': Validating ' || l_validation_item);
2416 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' = ''' || rtrim(P_PROMISE_REC.PROMISE_PAYMENT_METHOD) || '''');
2417 END IF;
2418 if P_PROMISE_REC.PROMISE_PAYMENT_METHOD is not null and rtrim(P_PROMISE_REC.PROMISE_PAYMENT_METHOD) <> '' then
2419 l_SQL := 'SELECT ''X'' ' ||
2420 'FROM IEX_LOOKUPS_V ' ||
2421 'WHERE ' ||
2422 'LOOKUP_TYPE = ''IEX_PAYMENT_TYPES'' AND LOOKUP_CODE = :P_PAYMENT_METHOD AND ' ||
2423 'ENABLED_FLAG = ''Y''';
2424
2425 open l_cursor for l_SQL
2426 using P_PROMISE_REC.PROMISE_PAYMENT_METHOD;
2427 fetch l_cursor into l_result_varchar;
2428
2429 if l_cursor%rowcount = 0 or l_result_varchar is null then
2430 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2431 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' failed validation: wrong payment method');
2432 END IF;
2433 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2434 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_procedure);
2435 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2436 FND_MSG_PUB.Add;
2437 RAISE FND_API.G_EXC_ERROR;
2438 end if;
2439 else
2440 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2441 iex_debug_pub.LogMessage(G_PKG_NAME || '.' ||l_procedure || ': ' || l_validation_item || ' is null - nothing to validate');
2442 END IF;
2443 end if;
2444
2445 end;
2446
2447
2448 PROCEDURE MASS_PROMISE(
2449 P_API_VERSION IN NUMBER,
2450 P_INIT_MSG_LIST IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
2451 P_COMMIT IN VARCHAR2, -- DEFAULT FND_API.G_FALSE,
2452 P_VALIDATION_LEVEL IN NUMBER, -- DEFAULT FND_API.G_VALID_LEVEL_FULL,
2453 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2454 X_MSG_COUNT OUT NOCOPY NUMBER,
2455 X_MSG_DATA OUT NOCOPY VARCHAR2,
2456 P_MASS_IDS_TBL IN DBMS_SQL.NUMBER_TABLE,
2457 P_MASS_PROMISE_REC IN IEX_PROMISES_PUB.PRO_MASS_REC_TYPE,
2458 X_MASS_PRORESP_TBL OUT NOCOPY IEX_PROMISES_PUB.PRO_MASS_RESP_TBL)
2459 IS
2460 l_api_name CONSTANT VARCHAR2(30) := 'MASS_PROMISE';
2461 l_api_version CONSTANT NUMBER := 1.0;
2462 l_return_status VARCHAR2(1);
2463 l_msg_count NUMBER;
2464 l_msg_data VARCHAR2(32767);
2465
2466 i NUMBER;
2467 k NUMBER;
2468 l_promise_id NUMBER;
2469 l_promise_amount NUMBER;
2470 l_currency varchar2(240);
2471 l_cust_account_id number;
2472 l_payment_schedule_id number;
2473 l_status varchar2(30);
2474 l_state varchar2(30);
2475 l_remaining_amount number;
2476 l_broken_on_date date;
2477 l_note_payer_id NUMBER;
2478 l_payer_num_id NUMBER;
2479 l_payer_id VARCHAR2(80);
2480 l_payer_name HZ_PARTIES.PARTY_NAME%TYPE; --Changed the datatype for bug#5652085 by ehuh 2/28/07
2481 l_note_payer_type VARCHAR2(100);
2482 l_context_tab IEX_NOTES_PVT.CONTEXTS_TBL_TYPE;
2483 l_note_id NUMBER;
2484 l_SQL VARCHAR2(1000);
2485 Type refCur is Ref Cursor;
2486 l_cursor refCur;
2487 l_note_type varchar2(30);
2488 l_source_object_id NUMBER;
2489 l_source_object_code varchar2(20);
2490 l_cust_site_use_id number;
2491 l_MASS_IDS_TBL DBMS_SQL.NUMBER_TABLE;
2492 l_org_id number; --Added for bug 7237026 17-Nov-2008 barathsr
2493
2494 -- generate new promise detail
2495 CURSOR prd_genid_crs IS
2496 select IEX_PROMISE_DETAILS_S.NEXTVAL from dual;
2497
2498 --Begin bug 7237026 17-Nov-2008 barathsr
2499 CURSOR c_org_id (p_del_id number) IS
2500 select org_id
2501 from iex_delinquencies_all
2502 where delinquency_id = p_del_id;
2503 --End bug 7237026 17-Nov-2008 barathsr
2504
2505
2506 BEGIN
2507
2508 -- Standard start of API savepoint
2509 SAVEPOINT MASS_PROMISE_PVT;
2510
2511 -- Standard call to check for call compatibility
2512 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2514 END IF;
2515
2516 -- Initialize message list if p_init_msg_list is set to TRUE
2517 IF FND_API.To_Boolean(p_init_msg_list) THEN
2518 FND_MSG_PUB.initialize;
2519 END IF;
2520
2521 -- Initialize API return status to success
2522 l_return_status := FND_API.G_RET_STS_SUCCESS;
2523
2524 -- START OF BODY OF API
2525 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2526 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Start of body');
2527 END IF;
2528
2529 /* validate input */
2530 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2531 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating input...');
2532 END IF;
2533 VALIDATE_MASS_INPUT(P_MASS_IDS_TBL, P_MASS_PROMISE_REC);
2534
2535 /* validate promiser info */
2536 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2537 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating parties...');
2538 END IF;
2539 IEX_PAYMENTS_PUB.GET_PAYER_INFO(
2540 P_PAYER_PARTY_REL_ID => P_MASS_PROMISE_REC.PROMISED_BY_PARTY_REL_ID,
2541 P_PAYER_PARTY_ORG_ID => P_MASS_PROMISE_REC.PROMISED_BY_PARTY_ORG_ID,
2542 P_PAYER_PARTY_PER_ID => P_MASS_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
2543 X_NOTE_PAYER_TYPE => l_note_payer_type,
2544 X_NOTE_PAYER_NUM_ID => l_note_payer_id,
2545 X_PAYER_NUM_ID => l_payer_num_id,
2546 X_PAYER_ID => l_payer_id,
2547 X_PAYER_NAME => l_payer_name);
2548
2549 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2550 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Looping thru table of ids...');
2551 END IF;
2552 k := 0;
2553 FOR i IN 1..P_MASS_IDS_TBL.COUNT LOOP
2554
2555 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2556 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': record = ' || i || '; delinquency = ' || P_MASS_IDS_TBL(i));
2557 END IF;
2558
2559 -- fixed a perf bug 4930381
2560 -- l_SQL := 'SELECT CUSTOMER_ID, CUSTOMER_SITE_USE_ID, payment_schedule_id' ||
2561 -- ' from iex_pay_invoices_v where delinquency_id = :P_DEL_ID';
2562
2563 l_SQL := 'SELECT psa.CUSTOMER_ID, psa.CUSTOMER_SITE_USE_ID, del.payment_schedule_id ' ||
2564 ' FROM iex_delinquencies del, ar_payment_schedules psa ' ||
2565 ' WHERE psa.payment_schedule_id = del.payment_schedule_id and psa.status = ''OP'' and ' ||
2566 ' psa.AMOUNT_DUE_REMAINING > 0 and del.DELINQUENCY_ID = :P_DEL_ID';
2567
2568 open l_cursor for l_SQL
2569 using P_MASS_IDS_TBL(i);
2570 fetch l_cursor into l_cust_account_id, l_cust_site_use_id, l_payment_schedule_id;
2571 close l_cursor;
2572 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2573 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_cust_account_id = ' || l_cust_account_id);
2574 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_cust_site_use_id = ' || l_cust_site_use_id);
2575 END IF;
2576
2577 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2578 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Getting promises to be canceled...');
2579 END IF;
2580 l_SQL := 'SELECT PROMISE_DETAIL_ID, PROMISE_AMOUNT, CURRENCY_CODE, STATUS, STATE, AMOUNT_DUE_REMAINING ' ||
2581 'FROM IEX_PROMISE_DETAILS ' ||
2582 'WHERE DELINQUENCY_ID = :P_DEL_ID AND STATUS in (''COLLECTABLE'', ''PENDING'') ' ||
2583 'UNION ' ||
2584 'SELECT PROMISE_DETAIL_ID, PROMISE_AMOUNT, CURRENCY_CODE, STATUS, STATE, AMOUNT_DUE_REMAINING ' ||
2585 'FROM IEX_PROMISE_DETAILS ' ||
2586 'WHERE CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
2587 'DELINQUENCY_ID IS NULL AND CNSLD_INVOICE_ID IS NULL AND CONTRACT_ID IS NULL AND ' ||
2588 'TRUNC(promise_date) = TRUNC(:P_PROMISE_DATE) AND ' ||
2589 'STATUS in (''COLLECTABLE'', ''PENDING'')';
2590
2591 open l_cursor for l_SQL
2592 using P_MASS_IDS_TBL(i), l_cust_account_id, P_MASS_PROMISE_REC.PROMISE_DATE;
2593
2594 LOOP
2595 fetch l_cursor into l_promise_id, l_promise_amount, l_currency, l_status, l_state, l_remaining_amount;
2596 exit when l_cursor%NOTFOUND;
2597
2598 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2599 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_promise_id = ' || l_promise_id);
2600 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_promise_amount = ' || l_promise_amount);
2601 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_currency = ' || l_currency);
2602 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_status = ' || l_status);
2603 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_state = ' || l_state);
2604 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_remaining_amount = ' || l_remaining_amount);
2605 END IF;
2606
2607 k := k+1;
2608 X_MASS_PRORESP_TBL(k).PROMISE_ID := l_promise_id;
2609 X_MASS_PRORESP_TBL(k).PROMISE_AMOUNT := l_promise_amount;
2610 X_MASS_PRORESP_TBL(k).CURRENCY_CODE := l_currency;
2611 X_MASS_PRORESP_TBL(k).CUST_ACCOUNT_ID := l_cust_account_id;
2612 X_MASS_PRORESP_TBL(k).CUST_SITE_USE_ID := l_cust_site_use_id;
2613 X_MASS_PRORESP_TBL(k).DELINQUENCY_ID := P_MASS_IDS_TBL(i);
2614 X_MASS_PRORESP_TBL(k).STATUS := 'CANCELLED';
2615 X_MASS_PRORESP_TBL(k).STATE := l_state;
2616 X_MASS_PRORESP_TBL(k).COLLECTABLE_AMOUNT := l_remaining_amount;
2617 l_MASS_IDS_TBL(k) := l_payment_schedule_id;
2618
2619 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2620 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Canceling the promise...');
2621 END IF;
2622 UPDATE iex_promise_details
2623 SET STATUS = 'CANCELLED',
2624 RESOURCE_ID = P_MASS_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
2625 PROMISE_MADE_BY = P_MASS_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
2626 PROGRAM_ID = G_APP_ID,
2627 last_update_date = sysdate,
2628 last_updated_by = G_USER_ID,
2629 LAST_UPDATE_LOGIN = G_LOGIN_ID
2630 where
2631 PROMISE_DETAIL_ID = l_promise_id;
2632 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2633 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Done');
2634 END IF;
2635
2636 If nvl(fnd_profile.value('IEX_ALLOW_DUN_FULL_PROMISE'),'N')= 'Y' then -- added for bug 14318148
2637 --start
2638 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2639 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
2640 END IF;
2641 update_del_stage_level (
2642 p_promise_id => l_promise_id,
2643 X_RETURN_STATUS => l_return_status,
2644 X_MSG_COUNT => l_msg_count,
2645 X_MSG_DATA => l_msg_data);
2646 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2647 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
2648 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Status = ' || L_RETURN_STATUS);
2649 END IF;
2650
2651 -- check for errors
2652 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2653 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2654 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
2655 END IF;
2656 RAISE FND_API.G_EXC_ERROR;
2657 END IF;
2658 --end
2659 End If; --added for bug 14318148
2660 END LOOP;
2661 close l_cursor;
2662
2663 -- fixed a perf bug 4930381 l_SQL := 'SELECT CUSTOMER_ID, AMOUNT_DUE_REMAINING, INVOICE_CURRENCY_CODE, CUSTOMER_SITE_USE_ID FROM iex_pay_invoices_v WHERE DELINQUENCY_ID = :P_DEL_ID';
2664 l_SQL := 'SELECT psa.CUSTOMER_ID, psa.AMOUNT_DUE_REMAINING, psa.INVOICE_CURRENCY_CODE, psa.CUSTOMER_SITE_USE_ID '||
2665 ' FROM iex_delinquencies del, ar_payment_schedules psa ' ||
2666 ' WHERE psa.payment_schedule_id = del.payment_schedule_id and psa.status = ''OP'' and ' ||
2667 ' psa.AMOUNT_DUE_REMAINING > 0 and del.DELINQUENCY_ID = :P_DEL_ID';
2668 open l_cursor for l_SQL
2669 using P_MASS_IDS_TBL(i);
2670 fetch l_cursor into l_cust_account_id, l_remaining_amount, l_currency, l_cust_site_use_id;
2671
2672 -- generate new promise id
2673 OPEN prd_genid_crs;
2674 FETCH prd_genid_crs INTO l_promise_id;
2675 CLOSE prd_genid_crs;
2676 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2677 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': new promise_id = ' || l_promise_id);
2678 END IF;
2679
2680 -- get broken on date
2681 GET_BROKEN_ON_DATE(P_PROMISE_DATE => P_MASS_PROMISE_REC.PROMISE_DATE, X_BROKEN_ON_DATE => l_broken_on_date);
2682 -- start wf and return promise status
2683 START_PTP_WF(P_PROMISE_ID => l_promise_id, X_PROMISE_STATUS => l_status);
2684
2685 --Begin bug 7237026 17-Nov-2008 barathsr
2686 open c_org_id (P_MASS_IDS_TBL(i));
2687 fetch c_org_id into l_org_id;
2688 close c_org_id;
2689
2690
2691 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2692 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Inserting new promise...');
2693 END IF;
2694 INSERT INTO IEX_PROMISE_DETAILS
2695 (
2696 PROMISE_DETAIL_ID,
2697 OBJECT_VERSION_NUMBER,
2698 PROGRAM_ID,
2699 LAST_UPDATE_DATE,
2700 LAST_UPDATED_BY,
2701 LAST_UPDATE_LOGIN,
2702 CREATION_DATE,
2703 CREATED_BY,
2704 PROMISE_DATE,
2705 PROMISE_AMOUNT,
2706 PROMISE_PAYMENT_METHOD,
2707 STATUS,
2708 ACCOUNT,
2709 PROMISE_ITEM_NUMBER,
2710 CURRENCY_CODE,
2711 CAMPAIGN_SCHED_ID,
2712 DELINQUENCY_ID,
2713 RESOURCE_ID,
2714 PROMISE_MADE_BY,
2715 CUST_ACCOUNT_ID,
2716 ATTRIBUTE_CATEGORY,
2717 ATTRIBUTE1,
2718 ATTRIBUTE2,
2719 ATTRIBUTE3,
2720 ATTRIBUTE4,
2721 ATTRIBUTE5,
2722 ATTRIBUTE6,
2723 ATTRIBUTE7,
2724 ATTRIBUTE8,
2725 ATTRIBUTE9,
2726 ATTRIBUTE10,
2727 ATTRIBUTE11,
2728 ATTRIBUTE12,
2729 ATTRIBUTE13,
2730 ATTRIBUTE14,
2731 ATTRIBUTE15,
2732 CNSLD_INVOICE_ID,
2733 CONTRACT_ID,
2734 BROKEN_ON_DATE,
2735 AMOUNT_DUE_REMAINING,
2736 STATE,
2737 ORG_ID
2738 )
2739 VALUES
2740 (
2741 l_promise_id,
2742 1.0,
2743 G_APP_ID,
2744 sysdate,
2745 G_USER_ID,
2746 G_LOGIN_ID,
2747 sysdate,
2748 G_USER_ID,
2749 P_MASS_PROMISE_REC.PROMISE_DATE,
2750 l_remaining_amount,
2751 P_MASS_PROMISE_REC.PROMISE_PAYMENT_METHOD,
2752 l_status,
2753 P_MASS_PROMISE_REC.ACCOUNT,
2754 P_MASS_PROMISE_REC.PROMISE_ITEM_NUMBER,
2755 l_currency,
2756 P_MASS_PROMISE_REC.CAMPAIGN_SCHED_ID,
2757 P_MASS_IDS_TBL(i),
2758 P_MASS_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
2759 P_MASS_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
2760 l_cust_account_id,
2761 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE_CATEGORY,
2762 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE1,
2763 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE2,
2764 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE3,
2765 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE4,
2766 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE5,
2767 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE6,
2768 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE7,
2769 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE8,
2770 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE9,
2771 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE10,
2772 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE11,
2773 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE12,
2774 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE13,
2775 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE14,
2776 P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE15,
2777 null,
2778 null,
2779 l_broken_on_date,
2780 l_remaining_amount,
2781 'PROMISE',
2782 l_org_id
2783 );
2784
2785 --End bug 7237026 17-Nov-2008 barathsr
2786
2787
2788 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2789 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Insert OK');
2790 END IF;
2791 If nvl(fnd_profile.value('IEX_ALLOW_DUN_FULL_PROMISE'),'N')= 'Y' then -- added for bug 14318148
2792 --start
2793 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2794 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
2795 END IF;
2796 update_del_stage_level (
2797 p_promise_id => l_promise_id,
2798 X_RETURN_STATUS => l_return_status,
2799 X_MSG_COUNT => l_msg_count,
2800 X_MSG_DATA => l_msg_data);
2801 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2802 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
2803 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Status = ' || L_RETURN_STATUS);
2804 END IF;
2805
2806 -- check for errors
2807 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2808 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2809 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
2810 END IF;
2811 RAISE FND_API.G_EXC_ERROR;
2812 END IF;
2813 --end
2814 End If; ---- added for bug 14318148
2815 k := k+1;
2816 X_MASS_PRORESP_TBL(k).PROMISE_ID := l_promise_id;
2817 X_MASS_PRORESP_TBL(k).PROMISE_AMOUNT := l_remaining_amount;
2818 X_MASS_PRORESP_TBL(k).CURRENCY_CODE := l_currency;
2819 X_MASS_PRORESP_TBL(k).CUST_ACCOUNT_ID := l_cust_account_id;
2820 X_MASS_PRORESP_TBL(k).CUST_SITE_USE_ID := l_cust_site_use_id;
2821 X_MASS_PRORESP_TBL(k).DELINQUENCY_ID := P_MASS_IDS_TBL(i);
2822 X_MASS_PRORESP_TBL(k).STATUS := l_status;
2823 X_MASS_PRORESP_TBL(k).STATE := 'PROMISE';
2824 X_MASS_PRORESP_TBL(k).COLLECTABLE_AMOUNT := l_remaining_amount;
2825 l_MASS_IDS_TBL(k) := l_payment_schedule_id;
2826 END LOOP;
2827
2828 l_note_type := fnd_profile.value('AST_NOTES_DEFAULT_TYPE');
2829 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2830 iex_debug_pub.LogMessage(l_api_name || ': l_note_type = ' || l_note_type);
2831 END IF;
2832
2833 -- inserting a note
2834 if P_MASS_PROMISE_REC.NOTE is not null and l_note_type is not null then
2835
2836 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2837 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Going to build context for note...');
2838 END IF;
2839 i := 1;
2840 /* adding parties into note context */
2841 if l_note_payer_type = 'PARTY_RELATIONSHIP' then
2842 l_context_tab(i).context_type := 'PARTY';
2843 l_context_tab(i).context_id := P_MASS_PROMISE_REC.PROMISED_BY_PARTY_REL_ID;
2844 i := i + 1;
2845 l_context_tab(i).context_type := 'PARTY';
2846 l_context_tab(i).context_id := P_MASS_PROMISE_REC.PROMISED_BY_PARTY_ORG_ID;
2847 i := i + 1;
2848 l_context_tab(i).context_type := 'PARTY';
2849 l_context_tab(i).context_id := P_MASS_PROMISE_REC.PROMISED_BY_PARTY_PER_ID;
2850 i := i + 1;
2851 elsif l_note_payer_type = 'PARTY_ORGANIZATION' then
2852 l_context_tab(i).context_type := 'PARTY';
2853 l_context_tab(i).context_id := P_MASS_PROMISE_REC.PROMISED_BY_PARTY_ORG_ID;
2854 i := i + 1;
2855 elsif l_note_payer_type = 'PARTY_PERSON' then
2856 l_context_tab(i).context_type := 'PARTY';
2857 l_context_tab(i).context_id := P_MASS_PROMISE_REC.PROMISED_BY_PARTY_PER_ID;
2858 i := i + 1;
2859 end if;
2860
2861 FOR k IN 1..X_MASS_PRORESP_TBL.count LOOP
2862 /* adding account to note context */
2863 l_context_tab(i).context_type := 'IEX_ACCOUNT';
2864 l_context_tab(i).context_id := X_MASS_PRORESP_TBL(k).CUST_ACCOUNT_ID;
2865 i := i + 1;
2866
2867 /* adding transaction number to note context */
2868 l_context_tab(i).context_type := 'IEX_INVOICES';
2869 l_context_tab(i).context_id := l_MASS_IDS_TBL(k);
2870 i := i + 1;
2871
2872 /* adding bill-to to note context */
2873 l_context_tab(i).context_type := 'IEX_BILLTO';
2874 l_context_tab(i).context_id := X_MASS_PRORESP_TBL(k).CUST_SITE_USE_ID;
2875 i := i + 1;
2876
2877 /* adding first promise as note source and all others as note context */
2878 if k = 1 then
2879 l_source_object_code := 'IEX_PROMISE';
2880 l_source_object_id := X_MASS_PRORESP_TBL(k).PROMISE_ID;
2881 else
2882 l_context_tab(i).context_type := 'IEX_PROMISE';
2883 l_context_tab(i).context_id := X_MASS_PRORESP_TBL(k).PROMISE_ID;
2884 i := i + 1;
2885 end if;
2886
2887 /* adding delinquency to note context */
2888 l_context_tab(i).context_type := 'IEX_DELINQUENCY';
2889 l_context_tab(i).context_id := X_MASS_PRORESP_TBL(k).DELINQUENCY_ID;
2890 i := i + 1;
2891
2892 END LOOP;
2893
2894 -- for debug purpose only
2895 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2896 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_source_object_code = ' || l_source_object_code);
2897 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_source_object_id = ' || l_source_object_id);
2898 END IF;
2899 FOR i IN 1..l_context_tab.COUNT LOOP
2900 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2901 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_context_tab(' || i || ').context_type = ' || l_context_tab(i).context_type);
2902 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_context_tab(' || i || ').context_id = ' || l_context_tab(i).context_id);
2903 END IF;
2904 END LOOP;
2905
2906 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2907 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling IEX_NOTES_PVT.Create_Note...');
2908 END IF;
2909 IEX_NOTES_PVT.Create_Note(
2910 P_API_VERSION => 1.0,
2911 P_INIT_MSG_LIST => 'F',
2912 P_COMMIT => 'F',
2913 P_VALIDATION_LEVEL => 100,
2914 X_RETURN_STATUS => l_return_status,
2915 X_MSG_COUNT => l_msg_count,
2916 X_MSG_DATA => l_msg_data,
2917 p_source_object_id => l_source_object_id,
2918 p_source_object_code => l_source_object_code,
2919 p_note_type => l_note_type,
2920 p_notes => P_MASS_PROMISE_REC.NOTE,
2921 p_contexts_tbl => l_context_tab,
2922 x_note_id => l_note_id);
2923
2924 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2925 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to IEX_NOTES_PVT.Create_Note');
2926 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Status = ' || L_RETURN_STATUS);
2927 END IF;
2928
2929 -- check for errors
2930 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2931 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2932 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': IEX_NOTES_PVT.Create_Note failed');
2933 END IF;
2934 RAISE FND_API.G_EXC_ERROR;
2935 END IF;
2936
2937 FOR k IN 1..X_MASS_PRORESP_TBL.count LOOP
2938 X_MASS_PRORESP_TBL(k).note_id := l_note_id;
2939 END LOOP;
2940 else
2941 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2942 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': no note to save');
2943 END IF;
2944 end if;
2945
2946 -- commit if promise updated successfully
2947 IF FND_API.To_Boolean( p_commit ) THEN
2948 COMMIT WORK;
2949 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2950 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': commited');
2951 END IF;
2952 END IF;
2953
2954 -- END OF BODY OF API
2955
2956 x_return_status := l_return_status;
2957 -- Standard call to get message count and if count is 1, get message info
2958 FND_MSG_PUB.Count_And_Get(
2959 p_encoded => FND_API.G_FALSE,
2960 p_count => x_msg_count,
2961 p_data => x_msg_data);
2962
2963 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2964 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': end of API');
2965 END IF;
2966
2967 EXCEPTION
2968 WHEN FND_API.G_EXC_ERROR THEN
2969 ROLLBACK TO MASS_PROMISE_PVT;
2970 x_return_status := FND_API.G_RET_STS_ERROR;
2971 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2972 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2973 ROLLBACK TO MASS_PROMISE_PVT;
2974 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2975 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2976 WHEN OTHERS THEN
2977 ROLLBACK TO MASS_PROMISE_PVT;
2978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2979 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2980 THEN
2981 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2982 END IF;
2983 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2984
2985 END;
2986
2987 Procedure update_del_stage_level (
2988 p_promise_id IN NUMBER,
2989 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2990 X_MSG_COUNT OUT NOCOPY NUMBER,
2991 X_MSG_DATA OUT NOCOPY VARCHAR2)
2992 is
2993 l_api_name CONSTANT VARCHAR2(30) := 'update_del_stage_level';
2994 l_api_version CONSTANT NUMBER := 1.0;
2995 l_return_status varchar2(10);
2996 l_msg_count number;
2997 l_msg_data varchar2(200);
2998 l_total_already_pro_amt number;
2999 l_amt_due_remaining number;
3000 l_promised_delinquency_id number;
3001 l_stage_number number;
3002 begin
3003 -- Standard start of API savepoint
3004 SAVEPOINT UPDATE_DEL_STAGE_PVT;
3005 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3006 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': start');
3007 END IF;
3008 -- Initialize API return status to success
3009 l_return_status := FND_API.G_RET_STS_SUCCESS;
3010 --start
3011 Begin
3012 select delinquency_id into l_promised_delinquency_id
3013 from iex_promise_details
3014 where promise_detail_id = p_promise_id;
3015
3016 SELECT sum(promise_amount) into l_total_already_pro_amt
3017 from iex_promise_details where delinquency_id = l_promised_delinquency_id
3018 and status = 'COLLECTABLE'
3019 and state = 'PROMISE';
3020 Exception
3021 WHEN NO_DATA_FOUND then
3022 l_total_already_pro_amt := 0;
3023 End ;
3024 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3025 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'l_total_already_pro_amt: ' || l_total_already_pro_amt);
3026 END IF;
3027 Begin
3028 SELECT amount_due_remaining into l_amt_due_remaining
3029 from ar_payment_schedules pay,
3030 iex_delinquencies del
3031 where
3032 del.payment_schedule_id = pay.payment_schedule_id
3033 and del.delinquency_id = l_promised_delinquency_id;
3034 Exception
3035 WHEN NO_DATA_FOUND then
3036 l_amt_due_remaining := 0;
3037 End ;
3038 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3039 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'l_amt_due_remaining: ' || l_amt_due_remaining);
3040 end if;
3041 if l_amt_due_remaining <= l_total_already_pro_amt and nvl(fnd_profile.value('IEX_ALLOW_DUN_FULL_PROMISE'),'N')= 'Y' then --modified for bug 14318148
3042 update iex_delinquencies_all
3043 set staged_dunning_level = 0
3044 where delinquency_id = l_promised_delinquency_id;
3045
3046 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3047 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'Updated the staged dunning level to 0 for delinquency id: ' || l_promised_delinquency_id);
3048 end if;
3049 else
3050 iex_utilities.MaxStageForanDelinquency (p_delinquency_id => l_promised_delinquency_id
3051 , p_stage_number => l_stage_number);
3052 update iex_delinquencies_all
3053 set staged_dunning_level = l_stage_number
3054 where delinquency_id = l_promised_delinquency_id
3055 and staged_dunning_level=0; --added for bug 14318148
3056 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3057 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'Updated the staged dunning level to ' || l_stage_number || ' for delinquency id: ' || l_promised_delinquency_id);
3058 end if;
3059 end if;
3060 --end
3061
3062 x_return_status := l_return_status;
3063 -- Standard call to get message count and if count is 1, get message info
3064 FND_MSG_PUB.Count_And_Get(
3065 p_encoded => FND_API.G_FALSE,
3066 p_count => x_msg_count,
3067 p_data => x_msg_data);
3068
3069 EXCEPTION
3070 WHEN FND_API.G_EXC_ERROR THEN
3071 ROLLBACK TO UPDATE_DEL_STAGE_PVT;
3072 x_return_status := FND_API.G_RET_STS_ERROR;
3073 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3074 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3075 ROLLBACK TO UPDATE_DEL_STAGE_PVT;
3076 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3077 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3078 WHEN OTHERS THEN
3079 ROLLBACK TO UPDATE_DEL_STAGE_PVT;
3080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3081 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3082 THEN
3083 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3084 END IF;
3085 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3086 end update_del_stage_level;
3087
3088 begin
3089 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3090 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
3091 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
3092 G_USER_ID := FND_GLOBAL.User_Id;
3093 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
3094
3095
3096 END;