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