[Home] [Help]
PACKAGE BODY: APPS.IEX_CUST_OVERVIEW_PVT
Source
1 PACKAGE BODY IEX_CUST_OVERVIEW_PVT AS
2 /* $Header: iexvcuob.pls 120.54.12020000.3 2012/08/20 14:12:59 ehuh ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_CUST_OVERVIEW_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvcuob.pls';
6 G_APPL_ID NUMBER;
7 G_LOGIN_ID NUMBER;
8 G_PROGRAM_ID NUMBER;
9 G_USER_ID NUMBER;
10 G_REQUEST_ID NUMBER;
11
12 PG_DEBUG NUMBER(2);
13
14 PROCEDURE Get_Customer_Info
15 (p_api_version IN NUMBER := 1.0,
16 p_init_msg_list IN VARCHAR2,
17 p_commit IN VARCHAR2,
18 p_validation_level IN NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 p_party_id IN NUMBER,
23 p_object_source IN VARCHAR2,
24 x_customer_info_rec OUT NOCOPY customer_info_rec_type)
25 IS
26 l_api_version CONSTANT NUMBER := 1.0;
27 l_api_name CONSTANT VARCHAR2(30) := 'Get_Customer_Info';
28 l_return_status VARCHAR2(1);
29 l_msg_count NUMBER;
30 l_msg_data VARCHAR2(32767);
31
32 amount number;
33 total number;
34 l_bkr_count number; -- Added for bug#7830847 by PNAVEENK on 27-1-2009
35 -- Get Tax Code
36 CURSOR c_party_info IS
37 SELECT jgzz_fiscal_code
38 FROM hz_parties
39 WHERE party_id = p_party_id;
40
41 -- Collections score
42 CURSOR c_collections_score IS
43 SELECT a.score_value
44 FROM iex_score_histories a
45 WHERE a.creation_date =
46 (SELECT MAX(creation_date)
47 FROM iex_Score_histories
48 WHERE score_object_code = 'PARTY'
49 AND score_object_id = p_party_id)
50 AND a.score_object_code = 'PARTY'
51 AND a.score_object_id = p_party_id;
52
53 -- Collectable/Delinquent Invoices
54 -- Begin fix bug #4930425-jypark-01/11/2006-remove full table scan
55 -- CURSOR c_summ_info IS
56 -- SELECT COUNT(DECODE(ps.class, 'INV', ps.payment_schedule_id, 'DM', ps.payment_schedule_id, 'CB', ps.payment_schedule_id, NULL)) cnt_inv,
57 -- COUNT(DECODE(ps.class, 'INV', DECODE(del.status, 'DELINQUENT', del.delinquency_id, 'PREDELINQUENT', del.delinquency_id, NULL), NULL)) cnt_del
58 -- FROM ar_payment_schedules ps,
59 -- hz_cust_accounts ca,
60 -- iex_delinquencies del
61 -- WHERE ca.party_id = p_party_id
62 -- AND ps.customer_id = ca.cust_account_id
63 -- AND ps.status = 'OP'
64 -- AND del.payment_schedule_id(+) = ps.payment_schedule_id;
65
66 CURSOR c_collectible_trx IS
67 SELECT COUNT(ps.payment_schedule_id) cnt_inv
68 FROM ar_payment_schedules ps,
69 hz_cust_accounts ca
70 WHERE ca.party_id = p_party_id
71 AND ps.customer_id = ca.cust_account_id
72 AND ps.status = 'OP'
73 AND ps.class IN ('INV', 'DM', 'CB');
74
75 CURSOR c_delinquent_inv IS
76 SELECT COUNT(del.delinquency_id) cnt_del
77 FROM ar_payment_schedules ps,
78 hz_cust_accounts ca,
79 iex_delinquencies del
80 WHERE ca.party_id = p_party_id
81 AND ps.customer_id = ca.cust_account_id
82 AND ps.status = 'OP'
83 AND ps.class = 'INV'
84 AND del.status in ('DELINQUENT', 'PREDELINQUENT')
85 AND del.payment_schedule_id = ps.payment_schedule_id;
86 -- End fix bug #4930425-jypark-01/11/2006-remove full table scan
87
88 -- Delinquencies in Past Year
89 CURSOR c_delinquencies IS
90 SELECT Count(1)
91 FROM iex_delinquencies del
92 WHERE del.party_cust_id = p_party_id
93 AND del.creation_date between sysdate - 365 and sysdate ;
94
95 -- added by jypark for status in header
96 CURSOR c_filter IS
97 SELECT object_id, select_column, entity_name
98 FROM iex_object_filters
99 WHERE object_filter_type = 'IEXCUST'
100 AND active_flag = 'Y';
101
102 TYPE refCur IS REF CURSOR;
103 c_universe refCur;
104 l_sql_stmt VARCHAR2(1000);
105 l_sql_stmt_lsd VARCHAR2(1000);
106 l_status_rule_id NUMBER;
107 l_count NUMBER;
108 l_delinquency_status VARCHAR(80);
109
110 CURSOR c_rule IS
111 SELECT rl.delinquency_status, rl.priority,
112 iex_utilities.get_lookup_meaning('IEX_DELINQUENCY_STATUS', rl.delinquency_status) meaning
113 FROM iex_cu_sts_rl_lines rl, iex_cust_status_rules r
114 WHERE rl.status_rule_id = l_status_rule_id
115 AND r.status_rule_id = rl.status_rule_id
116 AND trunc(sysdate) BETWEEN trunc(nvl(r.start_date,sysdate)) AND trunc(nvl(r.end_date,sysdate))
117 AND NVL(rl.enabled_flag, 'N') = 'Y'
118 ORDER BY rl.priority;
119 c_del refCur;
120
121 -- Customer Since added by jypark 09/26/2002
122 CURSOR c_customer_since IS
123 SELECT MIN(account_established_date)
124 FROM hz_cust_accounts
125 WHERE account_established_date IS NOT NULL
126 AND party_id = p_party_id;
127
128 l_sql_select VARCHAR2(1000);
129 l_sql_where VARCHAR2(1000);
130 l_sql_cond VARCHAR2(1000);
131 l_customer_okl_info_rec IEX_CUST_OVERVIEW_PVT.Customer_OKL_Info_Rec_Type;
132 l_calc_cust_stats VARCHAR2(1); -- 5874874 gnramasa
133 BEGIN
134 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':begin');
135
136 l_sql_select := 'SELECT count(1) FROM ';
137 l_sql_where := ' WHERE ';
138 l_sql_cond := ' = :party_id';
139
140 x_customer_info_rec.party_id := p_party_id;
141
142 OPEN c_party_info;
143 FETCH c_party_info INTO x_customer_info_rec.identification_id;
144 CLOSE c_party_info;
145
146 if p_object_source = 'AR' then
147
148 -- Begin fix bug #4930425-jypark-01/11/2006-remove full table scan
149 --OPEN c_summ_info;
150 --FETCH c_summ_info INTO x_customer_info_rec.number_of_invoices,
151 -- x_customer_info_rec.invoices_overdue;
152 --CLOSE c_summ_info;
153 l_calc_cust_stats := nvl(fnd_profile.value('IEX_CALC_CUST_STATS'), 'A');
154 if (l_calc_cust_stats = 'A') then
155 OPEN c_collectible_trx;
156 FETCH c_collectible_trx INTO x_customer_info_rec.number_of_invoices;
157 CLOSE c_collectible_trx;
158
159 OPEN c_delinquent_inv;
160 FETCH c_delinquent_inv INTO x_customer_info_rec.invoices_overdue;
161 CLOSE c_delinquent_inv;
162
163 -- End fix bug #4930425-jypark-01/11/2006-remove full table scan
164
165 OPEN c_delinquencies;
166 FETCH c_delinquencies INTO x_customer_info_rec.number_of_delinquencies;
167 CLOSE c_delinquencies;
168 end if;
169 elsif p_object_source = 'OKL' then
170
171 Get_Customer_OKL_Info(
172 p_api_version => p_api_version,
173 p_init_msg_list => p_init_msg_list,
174 p_commit => p_commit,
175 p_validation_level => p_validation_level,
176 x_return_status => x_return_status,
177 x_msg_count => x_msg_count,
178 x_msg_data => x_msg_data,
179 p_party_id => p_party_id,
180 x_customer_okl_info_rec => l_customer_okl_info_rec);
181
182 x_customer_info_rec.CASES_OVERDUE := l_customer_okl_info_rec.CASES_OVERDUE;
183 x_customer_info_rec.NUMBER_OF_DEL_CASES := l_customer_okl_info_rec.NUMBER_OF_DEL_CASES;
184 x_customer_info_rec.NUMBER_OF_OKL_INV := l_customer_okl_info_rec.NUMBER_OF_OKL_INV;
185
186 end if;
187
188 OPEN c_collections_score;
189 FETCH c_collections_score INTO x_customer_info_rec.collections_score;
190 CLOSE c_collections_score;
191
192 OPEN c_customer_since;
193 FETCH c_customer_since INTO x_customer_info_rec.customer_since;
194 CLOSE c_customer_since;
195
196
197 -- added by jypark for status in header
198
199 FOR r_filter in c_filter LOOP
200 -- build SQL for universe
201 -- for bug 5874874 gnramasa 25-Apr-2007
202 l_sql_stmt := 'SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM ' || r_filter.entity_name || l_sql_where || r_filter.select_column || l_sql_cond || ')';
203 --l_sql_stmt := l_sql_select || r_filter.entity_name || l_sql_where || r_filter.select_column || l_sql_cond;
204
205 BEGIN
206 OPEN c_universe FOR l_sql_stmt USING p_party_id;
207 FETCH c_universe into l_count;
208
209 IF c_universe%FOUND AND l_count > 0 THEN
210
211 l_status_rule_id := r_filter.object_id;
212 CLOSE c_universe;
213
214 -- begin added by jypark 01/05/2004 to fix bug #3308753
215 -- begin bug 6723556 gnramasa 10th Jan 2008
216
217 IF l_status_rule_id IS NOT NULL THEN
218 FOR r_rule IN c_rule LOOP
219 l_delinquency_status := r_rule.delinquency_status;
220 iex_debug_pub.LogMessage('1. l_delinquency_status :' || l_delinquency_status);
221 IF l_delinquency_status = 'BANKRUPTCY' THEN
222 l_sql_stmt := 'SELECT count(1)' ||
223 ' FROM iex_bankruptcies' ||
224 ' WHERE party_id = :party_id' ||
225 ' AND close_date IS NULL ' ||
226 ' AND NVL(DISPOSITION_CODE, '' '') NOT IN (''DISMISSED'',''WITHDRAWN'' )';
227 ELSIF l_delinquency_status = 'DELINQUENT' THEN
228 l_sql_stmt := 'SELECT count(1)' ||
229 ' FROM iex_delinquencies' ||
230 ' WHERE party_cust_id = :party_id' ||
231 -- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
232 ' AND status = ''DELINQUENT''';
233 --Start bug 9861733 gnramasa 19th July 2010
234 /*
235 ELSIF l_delinquency_status = 'LITIGATION' THEN
236 l_sql_stmt_lsd := ' select count(1) from (' ||
237 ' SELECT litigation_id' ||
238 ' FROM iex_litigations ltg, iex_delinquencies del' ||
239 ' WHERE del.party_cust_id = :party_id' ||
240 ' AND ltg.delinquency_id = del.delinquency_id' ||
241 ' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
242 ' AND ltg.disposition_code IS NULL ' ||
243 ' UNION ' ||
244 ' SELECT litigation_id ' ||
245 ' FROM iex_litigations ' ||
246 ' WHERE party_id= :party_id ' ||
247 ' AND contract_number IS NOT NULL ' ||
248 ' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
249 ' AND disposition_code IS NULL )';
250 ELSIF l_delinquency_status = 'REPOSSESSION' THEN
251 l_sql_stmt_lsd := ' select count(1) from (' ||
252 ' SELECT repossession_id' ||
253 ' FROM iex_repossessions rps, iex_delinquencies del' ||
254 ' WHERE del.party_cust_id = :party_id' ||
255 ' AND rps.delinquency_id = del.delinquency_id' ||
256 ' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
257 ' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ||
258 ' UNION ' ||
259 ' SELECT repossession_id ' ||
260 ' FROM iex_repossessions ' ||
261 ' WHERE party_id= :party_id ' ||
262 ' AND contract_number IS NOT NULL ' ||
263 ' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
264 ' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W''))' ;
265 --' AND disposition_code IS NULL )';
266 ELSIF l_delinquency_status = 'WRITEOFF' THEN
267 l_sql_stmt_lsd := ' select count(1) from (' ||
268 ' SELECT writeoff_id' ||
269 ' FROM iex_writeoffs wrf, iex_delinquencies del' ||
270 ' WHERE del.party_cust_id = :party_id' ||
271 ' AND wrf.delinquency_id = del.delinquency_id' ||
272 ' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
273 ' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ||
274 --' AND wrf.disposition_code IS NULL ' ||
275 ' UNION ' ||
276 ' SELECT writeoff_id ' ||
277 ' FROM iex_writeoffs ' ||
278 ' WHERE party_id= :party_id ' ||
279 ' AND contract_number IS NOT NULL ' ||
280 ' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
281 ' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W''))' ;
282 --' AND disposition_code IS NULL )';
283 */
284 ELSIF l_delinquency_status = 'LITIGATION' THEN
285 l_sql_stmt := ' SELECT count(ltg.litigation_id) ' ||
286 ' FROM iex_litigations ltg ' ||
287 ' WHERE ltg.party_id = :party_id' ||
288 ' AND ltg.disposition_code IS NULL ';
289 ELSIF l_delinquency_status = 'REPOSSESSION' THEN
290 l_sql_stmt := ' SELECT count(rps.repossession_id) ' ||
291 ' FROM iex_repossessions rps ' ||
292 ' WHERE rps.party_id = :party_id' ||
293 ' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ;
294 --' AND disposition_code IS NULL )';
295 ELSIF l_delinquency_status = 'WRITEOFF' THEN
296 l_sql_stmt := ' SELECT count(wrf.writeoff_id) ' ||
297 ' FROM iex_writeoffs wrf ' ||
298 ' WHERE wrf.party_id = :party_id' ||
299 ' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ;
300 --' AND disposition_code IS NULL )';
301
302 ELSIF l_delinquency_status = 'PREDELINQUENT' THEN
303 l_sql_stmt := 'SELECT count(1)' ||
304 ' FROM iex_delinquencies' ||
305 ' WHERE party_cust_id = :party_id' ||
306 -- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
307 ' AND status = ''PREDELINQUENT''';
308 END IF;
309
310 iex_debug_pub.LogMessage('1. p_party_id :' || p_party_id);
311 /*
312 IF l_delinquency_status in ('LITIGATION','REPOSSESSION','WRITEOFF') THEN
313 iex_debug_pub.LogMessage('1. l_sql_stmt_lsd :' || l_sql_stmt_lsd);
314 OPEN c_del FOR l_sql_stmt_lsd USING p_party_id,p_party_id;
315 ELSE
316 */
317 iex_debug_pub.LogMessage('1. l_sql_stmt :' || l_sql_stmt);
318 OPEN c_del FOR l_sql_stmt USING p_party_id;
319 --END IF;
320 --End bug 9861733 gnramasa 19th July 2010
321 FETCH c_del INTO l_count;
322
323 IF l_count > 0 THEN
324 x_customer_info_rec.status := r_rule.meaning;
325 CLOSE c_del;
326 EXIT;
327 END IF;
328 CLOSE c_del;
329 END LOOP;
330 END IF;
331
332 IF x_customer_info_rec.status IS NOT NULL THEN
333 EXIT;
334 END IF;
335 -- end added by jypark 01/05/2004 to fix bug #3308753
336
337 END IF;
338 EXCEPTION
339 WHEN OTHERS THEN
340 null;
341 END ;
342 END LOOP;
343
344 IF l_status_rule_id IS NOT NULL THEN
345 FOR r_rule IN c_rule LOOP
346 l_delinquency_status := r_rule.delinquency_status;
347 iex_debug_pub.LogMessage('2. l_delinquency_status :' || l_delinquency_status);
348 IF l_delinquency_status = 'BANKRUPTCY' THEN
349 l_sql_stmt := 'SELECT count(1)' ||
350 ' FROM iex_bankruptcies' ||
351 ' WHERE party_id = :party_id' ||
352 ' AND close_date IS NULL ' ||
353 ' AND NVL(DISPOSITION_CODE , '' '') NOT IN (''DISMISSED'',''WITHDRAWN'' )';
354 ELSIF l_delinquency_status = 'DELINQUENT' THEN
355 l_sql_stmt := 'SELECT count(1)' ||
356 ' FROM iex_delinquencies' ||
357 ' WHERE party_cust_id = :party_id' ||
358 -- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
359 ' AND status = ''DELINQUENT''';
360 /*
361 ELSIF l_delinquency_status = 'LITIGATION' THEN
362 l_sql_stmt_lsd := 'select count(1) from (' ||
363 ' SELECT litigation_id' ||
364 ' FROM iex_litigations ltg, iex_delinquencies del' ||
365 ' WHERE del.party_cust_id = :party_id' ||
366 ' AND ltg.delinquency_id = del.delinquency_id' ||
367 ' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
368 ' AND ltg.disposition_code IS NULL ' ||
369 ' UNION ' ||
370 ' SELECT litigation_id ' ||
371 ' FROM iex_litigations ' ||
372 ' WHERE party_id= :party_id ' ||
373 ' AND contract_number IS NOT NULL ' ||
374 ' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
375 ' AND disposition_code IS NULL )';
376 ELSIF l_delinquency_status = 'REPOSSESSION' THEN
377 l_sql_stmt_lsd := ' select count(1) from (' ||
378 ' SELECT repossession_id' ||
379 ' FROM iex_repossessions rps, iex_delinquencies del' ||
380 ' WHERE del.party_cust_id = :party_id' ||
381 ' AND rps.delinquency_id = del.delinquency_id' ||
382 ' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
383 ' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ||
384 --' AND rps.disposition_code IS NULL ' ||
385 ' UNION ' ||
386 ' SELECT repossession_id ' ||
387 ' FROM iex_repossessions ' ||
388 ' WHERE party_id= :party_id ' ||
389 ' AND contract_number IS NOT NULL ' ||
390 ' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
391 ' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W''))' ;
392 --' AND disposition_code IS NULL )';
393 ELSIF l_delinquency_status = 'WRITEOFF' THEN
394 l_sql_stmt_lsd := ' select count(1) from (' ||
395 ' SELECT writeoff_id' ||
396 ' FROM iex_writeoffs wrf, iex_delinquencies del' ||
397 ' WHERE del.party_cust_id = :party_id' ||
398 ' AND wrf.delinquency_id = del.delinquency_id' ||
399 ' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
400 ' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ||
401 --' AND wrf.disposition_code IS NULL ' ||
402 ' UNION ' ||
403 ' SELECT writeoff_id ' ||
404 ' FROM iex_writeoffs ' ||
405 ' WHERE party_id= :party_id ' ||
406 ' AND contract_number IS NOT NULL ' ||
407 ' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
408 ' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W''))' ;
409 --' AND disposition_code IS NULL )';
410 */
411 ELSIF l_delinquency_status = 'LITIGATION' THEN
412 l_sql_stmt := ' SELECT count(ltg.litigation_id) ' ||
413 ' FROM iex_litigations ltg ' ||
414 ' WHERE ltg.party_id = :party_id' ||
415 ' AND ltg.disposition_code IS NULL ' ;
416 ELSIF l_delinquency_status = 'REPOSSESSION' THEN
417 l_sql_stmt := ' SELECT count(rps.repossession_id) ' ||
418 ' FROM iex_repossessions rps ' ||
419 ' WHERE rps.party_id = :party_id' ||
420 ' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ;
421 --' AND disposition_code IS NULL )';
422 ELSIF l_delinquency_status = 'WRITEOFF' THEN
423 l_sql_stmt := ' SELECT count(wrf.writeoff_id) ' ||
424 ' FROM iex_writeoffs wrf ' ||
425 ' WHERE wrf.party_id = :party_id' ||
426 ' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ;
427 --' AND disposition_code IS NULL )';
428
429 ELSIF l_delinquency_status = 'PREDELINQUENT' THEN
430 l_sql_stmt := 'SELECT count(1)' ||
431 ' FROM iex_delinquencies' ||
432 ' WHERE party_cust_id = :party_id' ||
433 -- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
434 ' AND status = ''PREDELINQUENT''';
435 END IF;
436
437 iex_debug_pub.LogMessage('2. p_party_id :' || p_party_id);
438 /*
439 IF l_delinquency_status in ('LITIGATION','REPOSSESSION','WRITEOFF') THEN
440 iex_debug_pub.LogMessage('2. l_sql_stmt_lsd :' || l_sql_stmt_lsd);
441 OPEN c_del FOR l_sql_stmt_lsd USING p_party_id,p_party_id;
442 ELSE
443 */
444 iex_debug_pub.LogMessage('2. l_sql_stmt :' || l_sql_stmt);
445 OPEN c_del FOR l_sql_stmt USING p_party_id;
446 --END IF;
447 --End bug 9861733 gnramasa 19th July 2010
448 --End bug 6723556 gnramasa 10th Jan 2008
449 FETCH c_del INTO l_count;
450
451 IF l_count > 0 THEN
452 x_customer_info_rec.status := r_rule.meaning;
453 CLOSE c_del;
454 EXIT;
455 END IF;
456 CLOSE c_del;
457 END LOOP;
458 END IF;
459 -- Start for bug#7830847 by PNAVEENK on 27-1-2009
460 IF x_customer_info_rec.status IS NULL THEN
461
462 SELECT count(1) into l_bkr_count
463 FROM iex_bankruptcies
464 WHERE party_id = x_customer_info_rec.party_id
465 AND close_date IS NULL
466 AND NVL(DISPOSITION_CODE, ' ') NOT IN ('DISMISSED','WITHDRAWN' );
467 IF l_bkr_count > 0 then
468 x_customer_info_rec.status := iex_utilities.get_lookup_meaning('IEX_DELINQUENCY_STATUS', 'BANKRUPTCY');
469 ELSE
470 x_customer_info_rec.status := iex_utilities.get_lookup_meaning('IEX_CUSTOMER_STATUS_TYPE', 'CURRENT');
471 END IF;
472 END IF;
473
474 -- End for bug#7830847
475 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':end');
476 END Get_Customer_Info;
477
478 PROCEDURE Get_Customer_OKL_Info
479 (p_api_version IN NUMBER := 1.0,
480 p_init_msg_list IN VARCHAR2,
481 p_commit IN VARCHAR2,
482 p_validation_level IN NUMBER,
483 x_return_status OUT NOCOPY VARCHAR2,
484 x_msg_count OUT NOCOPY NUMBER,
485 x_msg_data OUT NOCOPY VARCHAR2,
486 p_party_id IN NUMBER,
487 x_customer_okl_info_rec OUT NOCOPY customer_okl_info_rec_type)
488 IS
489 l_api_version CONSTANT NUMBER := 1.0;
490 l_api_name CONSTANT VARCHAR2(30) := 'Get_Customer_OKL_Info';
491 l_return_status VARCHAR2(1);
492 l_msg_count NUMBER;
493 l_msg_data VARCHAR2(32767);
494
495 -- Begin fix bug #4930425-jypark-01/10/2006-removed obsolete query
496 -- -- Delinquent Cases
497 -- CURSOR c_del_cases IS
498 -- SELECT Count(1)
499 -- FROM iex_delinquencies del
500 -- WHERE del.party_cust_id = p_party_id
501 -- and del.case_id is not null
502 -- and del.status = 'DELINQUENT';
503 --
504 -- -- Collectable OKL Invocies
505 -- CURSOR c_okl_inv IS
506 -- SELECT count(CNSLD.CONSOLIDATED_INVOICE_ID)
507 -- FROM IEX_BPD_CNSLD_INV_REMAINING_V cnsld, hz_cust_accounts ca
508 -- WHERE ca.party_id = p_party_id
509 -- AND cnsld.customer_id = ca.cust_account_id
510 -- AND cnsld.amount > 0;
511 --
512 -- -- Delinquent Cases in Past Year
513 -- CURSOR c_del_cases_past_year IS
514 -- SELECT Count(1)
515 -- FROM iex_delinquencies del
516 -- WHERE del.party_cust_id = p_party_id
517 -- AND del.creation_date between sysdate - 365 and sysdate
518 -- and del.case_id is not null
519 -- and del.status = 'DELINQUENT';
520 -- End fix bug #4930425-jypark-01/10/2006-removed obsolete query
521
522 BEGIN
523 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':begin');
524
525 -- Begin fix bug #4930425-jypark-01/10/2006-removed obsolete query
526 -- OPEN c_del_cases;
527 -- FETCH c_del_cases INTO x_customer_okl_info_rec.CASES_OVERDUE;
528 -- CLOSE c_del_cases;
529 --
530 -- OPEN c_okl_inv;
531 -- FETCH c_okl_inv INTO x_customer_okl_info_rec.NUMBER_OF_OKL_INV;
532 -- CLOSE c_okl_inv;
533 --
534 -- OPEN c_del_cases_past_year;
535 -- FETCH c_del_cases_past_year INTO x_customer_okl_info_rec.NUMBER_OF_DEL_CASES;
536 -- CLOSE c_del_cases_past_year;
537 -- End fix bug #4930425-jypark-01/10/2006-removed obsolete query
538
539 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':end');
540 END Get_Customer_OKL_Info;
541
542 PROCEDURE Get_Object_Info
543 (p_api_version IN NUMBER := 1.0,
544 p_init_msg_list IN VARCHAR2,
545 p_commit IN VARCHAR2,
546 p_validation_level IN NUMBER,
547 x_return_status OUT NOCOPY VARCHAR2,
548 x_msg_count OUT NOCOPY NUMBER,
549 x_msg_data OUT NOCOPY VARCHAR2,
550 p_object_id IN NUMBER,
551 p_object_type IN VARCHAR2,
552 p_object_source IN VARCHAR2,
553 x_object_info_rec OUT NOCOPY object_info_rec_type)
554 IS
555 l_api_version CONSTANT NUMBER := 1.0;
556 l_api_name CONSTANT VARCHAR2(30) := 'Get_Object_Info';
557 l_return_status VARCHAR2(1);
558 l_msg_count NUMBER;
559 l_msg_data VARCHAR2(32767);
560
561 l_party_id NUMBER;
562 l_cust_account_id NUMBER;
563 l_payment_schedule_id NUMBER;
564 l_customer_site_use_id NUMBER;
565
566 TYPE c_overdue_cur_type IS REF CURSOR;
567 TYPE c_balance_cur_type IS REF CURSOR;
568 TYPE c_dso_cur_type IS REF CURSOR; -- added by jypark 09/05/2002
569 TYPE c_summ_info_type IS REF CURSOR;
570
571 c_overdue c_overdue_cur_type;
572 c_balance c_balance_cur_type;
573 c_dso c_dso_cur_type; -- added by jypark 09/05/2002
574 c_summ_info c_summ_info_type;
575
576 l_last_pmt_info_rec last_pmt_info_rec_type;
577 l_last_okl_pmt_info_rec Last_OKL_Pmt_Info_Rec_Type;
578
579 CURSOR c_del(x_delinquency_id NUMBER) IS
580 SELECT payment_schedule_id
581 FROM iex_delinquencies
582 WHERE delinquency_id = x_delinquency_id
583 AND status not in ('CURRENT', 'CLOSE');
584
585 l_cnt_cur_codes number; --added for MOAC
586
587 l_amount_in_dispute ra_cm_requests_all.total_amount%type; --Added for bug 7612000 gnramasa 4th Dec 08
588
589 BEGIN
590 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':begin');
591 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':p_object_source=' || p_object_source ||
592 ':p_object_type=' || p_object_type || ':p_object_id=' || p_object_id);
593
594 --start moac change
595 --to check whether all the ou's has the same functional currency or not
596 l_cnt_cur_codes:= iex_currency_pvt.get_currency_count;
597 --end moac change
598
599 IF p_object_type = 'CUSTOMER' AND p_object_id IS NOT NULL THEN
600 l_party_id := p_object_id;
601 l_cust_account_id := null;
602 l_payment_schedule_id := null;
603
604 --Start bug 8359894 gnramasa 18th june 09
605 if (NVL(FND_PROFILE.VALUE('IEX_SHOW_DSO_IN_HEADER'), 'Y') = 'Y') then
606 OPEN c_summ_info FOR
607 SELECT
608 SUM(NVL(ps.acctd_amount_due_remaining,0)) net_balance,
609 SUM(DECODE(del.status, 'DELINQUENT', NVL(acctd_amount_due_remaining,0),
610 'PREDELINQUENT', NVL(acctd_amount_due_remaining,0),0)) overdue_amt
611
612 /* ROUND(
613 ( (SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y',
614 DECODE(PS.CLASS,
615 'INV', 1,
616 'DM', 1,
617 'CB', 1,
618 'DEP', 1,
619 'BR', 1, /* 22-JUL-2000 J Rautiainen BR Implementation */
620 /* 0), 0)
621 * PS.ACCTD_AMOUNT_DUE_REMAINING
622 ) * MAX(SP.CER_DSO_DAYS)
623 )
624 / DECODE(
625 SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y',DECODE(PS.CLASS,
626 'INV', 1,
627 'DM', 1,
628 'DEP', 1,
629 0), 0)
630 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
631 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
632 0)) ,
633 0, 1,
634 SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y', DECODE(PS.CLASS,
635 'INV', 1,
636 'DM', 1,
637 'DEP', 1,
638 0), 0)
639 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
640 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
641 0) )
642 )
643 ), 0) dso */
644 FROM ar_payment_schedules ps,
645 hz_cust_accounts ca,
646 -- Begin fix bug #5261855-jypark-06/16/2006-change to based table for performance
647 -- iex_delinquencies del,
648 iex_delinquencies_all del,
649 -- End fix bug #5261855-jypark-06/16/2006-change to based table for performance
650 ar_system_parameters sp
651 WHERE ca.party_id = l_party_id
652 AND ps.customer_id = ca.cust_account_id
653 AND ps.status = 'OP'
654 AND del.payment_schedule_id(+) = ps.payment_schedule_id
655 and sp.org_id=ps.org_id; --moac change
656
657 FETCH c_summ_info INTO x_object_info_rec.current_balance,
658 x_object_info_rec.amount_overdue;
659 -- x_object_info_rec.dso;
660 BEGIN
661 x_object_info_rec.dso := iex_coll_ind.get_conv_dso_ocm(l_party_id,null,null);
662 EXCEPTION
663 WHEN OTHERS THEN
664 x_object_info_rec.dso := 0;
665 end;
666
667 else
668 OPEN c_summ_info FOR
669 SELECT
670 SUM(NVL(ps.acctd_amount_due_remaining,0)) net_balance,
671 SUM(DECODE(del.status, 'DELINQUENT', NVL(acctd_amount_due_remaining,0),
672 'PREDELINQUENT', NVL(acctd_amount_due_remaining,0),0)) overdue_amt
673 FROM ar_payment_schedules ps,
674 hz_cust_accounts ca,
675 iex_delinquencies_all del,
676 ar_system_parameters sp
677 WHERE ca.party_id = l_party_id
678 AND ps.customer_id = ca.cust_account_id
679 AND ps.status = 'OP'
680 AND del.payment_schedule_id(+) = ps.payment_schedule_id
681 and sp.org_id=ps.org_id;
682
683 FETCH c_summ_info INTO x_object_info_rec.current_balance,
684 x_object_info_rec.amount_overdue;
685 end if; --if (NVL(FND_PROFILE.VALUE('IEX_SHOW_DSO_IN_HEADER'), 'Y') = 'Y') then
686
687 CLOSE c_summ_info;
688 --End bug 8359894 gnramasa 18th june 09
689
690 --Start bug 7612000 gnramasa 4th Dec 08
691 -- If value of "IEX: Exclude dispute amount from remaining amount " is Yes
692 -- then calculate the Amount thats in dispute and substract it from the amount overdue.
693 if (NVL(FND_PROFILE.VALUE('IEX_EXCLUDE_DISPUTE_AMT_FROM_REMAINING_AMT'), 'N') = 'Y') then
694 select nvl(sum(cm.total_amount * -1),0)
695 into l_amount_in_dispute
696 from ra_cm_requests cm
697 where cm.customer_trx_id in (select distinct ps.customer_trx_id
698 from ar_payment_schedules ps,
699 hz_cust_accounts ca
700 where ca.party_id = l_party_id
701 and ps.customer_id = ca.cust_account_id
702 and ps.status = 'OP')
703 and cm.status='PENDING_APPROVAL'
704 and not exists (select 1 from iex_delinquencies_all where status in ('CURRENT','CLOSE') -- bug 14499531
705 and transaction_id = cm.customer_trx_id);
706
707 x_object_info_rec.amount_overdue := x_object_info_rec.amount_overdue - l_amount_in_dispute;
708 end if;
709 --End bug 7612000 gnramasa 4th Dec 08
710
711 --start moac change
712 --if the functional currency codes of the ou's are different
713 --make the Amount overdue and Net balance fields as null.
714 if l_cnt_cur_codes <>1 then
715 x_object_info_rec.current_balance:=null;
716 x_object_info_rec.amount_overdue:=null;
717 end if;
718 --end moac change
719
720 ELSIF p_object_type = 'ACCOUNT' AND p_object_id IS NOT NULL THEN
721 l_party_id := null;
722 l_cust_account_id := p_object_id;
723 l_payment_schedule_id := null;
724
725 --start moac change
726 --if the functional currency codes of the ou's are different
727 --make the Amount overdue and Net balance fields as null.
728 if l_cnt_cur_codes <> 1 then
729 x_object_info_rec.current_balance:=null;
730 x_object_info_rec.amount_overdue:=null;
731 else
732 --end moac change
733 OPEN c_overdue FOR
734 SELECT sum(acctd_amount_due_remaining) amount
735 FROM ar_payment_schedules ps, iex_delinquencies del
736 WHERE ps.customer_id = p_object_id
737 -- fix bug #3561828 AND ps.due_date < sysdate
738 --BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
739 -- AND TRUNC(ps.due_date) < TRUNC(sysdate)
740 --END-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
741 AND ps.status = 'OP'
742 AND del.payment_schedule_id = ps.payment_schedule_id
743 AND del.status IN ('DELINQUENT', 'PREDELINQUENT');
744
745 FETCH c_overdue INTO x_object_info_rec.amount_overdue;
746 CLOSE c_overdue;
747
748 --Start bug 7612000 gnramasa 4th Dec 08
749 -- If value of "IEX: Exclude dispute amount from remaining amount " is Yes
750 -- then calculate the Amount thats in dispute and substract it from the amount overdue.
751 if (NVL(FND_PROFILE.VALUE('IEX_EXCLUDE_DISPUTE_AMT_FROM_REMAINING_AMT'), 'N') = 'Y') then
752 select nvl(sum(cm.total_amount * -1),0)
753 into l_amount_in_dispute
754 from ra_cm_requests cm
755 where cm.customer_trx_id in (select distinct ps.customer_trx_id
756 from ar_payment_schedules ps,
757 ar_system_parameters parm
758 where ps.customer_id = p_object_id
759 and ps.org_id = parm.org_id
760 and ps.status = 'OP')
761 and cm.status='PENDING_APPROVAL'
762 and not exists (select 1 from iex_delinquencies_all where status in ('CURRENT','CLOSE') -- bug 14499531
763 and transaction_id = cm.customer_trx_id);
764
765 x_object_info_rec.amount_overdue := x_object_info_rec.amount_overdue - l_amount_in_dispute;
766 end if;
767 --End bug 7612000 gnramasa 4th Dec 08
768
769 OPEN c_balance FOR
770 SELECT SUM(NVL(acctd_amount_due_remaining,0))
771 FROM ar_payment_schedules ps,
772 -- Begin fix bug #5077320-jypark-adding parameter table to show amount for selected operating unit
773 ar_system_parameters parm
774 WHERE customer_id = p_object_id
775 AND ps.org_id = parm.org_id
776 -- End fix bug #5077320-jypark-adding parameter table to show amount for selected operating unit
777 AND ps.status = 'OP';
778
779 FETCH c_balance INTO x_object_info_rec.current_balance;
780 CLOSE c_balance;
781
782 end if; --end if for check on count of currency codes
783
784 --Start bug 8359894 gnramasa 18th june 09
785 if (NVL(FND_PROFILE.VALUE('IEX_SHOW_DSO_IN_HEADER'), 'Y') = 'Y') then
786 /* OPEN c_dso FOR
787 SELECT
788 ROUND(
789 ( (SUM( DECODE(PS.CLASS,
790 'INV', 1,
791 'DM', 1,
792 'CB', 1,
793 'DEP', 1,
794 'BR', 1, /* 22-JUL-2000 J Rautiainen BR Implementation */
795 /* 0)
796 * PS.ACCTD_AMOUNT_DUE_REMAINING
797 ) * MAX(SP.CER_DSO_DAYS)
798 )
799 / DECODE(
800 SUM( DECODE(PS.CLASS,
801 'INV', 1,
802 'DM', 1,
803 'DEP', 1,
804 0)
805 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
806 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
807 0)) ,
808 0, 1,
809 SUM( DECODE(PS.CLASS,
810 'INV', 1,
811 'DM', 1,
812 'DEP', 1,
813 0)
814 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
815 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
816 0) )
817 )
818 ), 0) /* DSO */
819 /* FROM ar_system_parameters sp,
820 ar_payment_schedules ps
821 WHERE ps.customer_id = l_cust_account_id
822 AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y'
823 AND ps.org_id=sp.org_id; --added for moac change
824
825 FETCH c_dso INTO x_object_info_rec.dso;
826 CLOSE c_dso; */
827
828 BEGIN
829 x_object_info_rec.dso := iex_coll_ind.get_conv_dso_ocm(null,l_cust_account_id,null);
830 EXCEPTION
831 WHEN OTHERS THEN
832 x_object_info_rec.dso := 0;
833 END;
834
835 end if;
836 --End bug 8359894 gnramasa 18th june 09
837
838 ELSIF p_object_type = 'DELINQUENCY' AND p_object_id IS NOT NULL THEN
839 l_party_id := null;
840 l_cust_account_id := null;
841
842 OPEN c_del(p_object_id);
843 FETCH c_del INTO l_payment_schedule_id;
844 CLOSE c_del;
845
846
847 --start moac change
848 --if the functional currency codes of the ou's are different
849 --make the Amount overdue and Net balance fields as null.
850 if l_cnt_cur_codes <> 1 then
851 x_object_info_rec.current_balance:=null;
852 x_object_info_rec.amount_overdue:=null;
853 else
854 --end moac change
855 OPEN c_overdue FOR
856 SELECT ps.acctd_amount_due_remaining
857 FROM ar_payment_schedules ps, iex_delinquencies del
858 WHERE del.delinquency_id = p_object_id
859 AND ps.payment_schedule_id = del.payment_schedule_id
860 AND ps.status = 'OP'
861 AND del.status IN ('DELINQUENT', 'PREDELINQUENT');
862
863 FETCH c_overdue INTO x_object_info_rec.amount_overdue;
864 CLOSE c_overdue;
865
866 x_object_info_rec.current_balance := x_object_info_rec.amount_overdue;
867
868 --Start bug 7612000 gnramasa 4th Dec 08
869 -- If value of "IEX: Exclude dispute amount from remaining amount " is Yes
870 -- then calculate the Amount thats in dispute and substract it from the amount overdue.
871 if (NVL(FND_PROFILE.VALUE('IEX_EXCLUDE_DISPUTE_AMT_FROM_REMAINING_AMT'), 'N') = 'Y') then
872 select nvl(sum(cm.total_amount * -1),0)
873 into l_amount_in_dispute
874 from ra_cm_requests cm
875 where cm.customer_trx_id in
876 (
877 SELECT distinct ps.customer_trx_id
878 FROM ar_payment_schedules ps, iex_delinquencies del
879 WHERE del.delinquency_id = p_object_id
880 AND ps.payment_schedule_id = del.payment_schedule_id
881 AND ps.status = 'OP'
882 AND del.status IN ('DELINQUENT', 'PREDELINQUENT'))
883 and cm.status='PENDING_APPROVAL';
884
885 x_object_info_rec.amount_overdue := x_object_info_rec.amount_overdue - l_amount_in_dispute;
886 end if;
887 --End bug 7612000 gnramasa 4th Dec 08
888
889 end if; --end if for check on count of currency codes(moac change).
890
891 --Start bug 8359894 gnramasa 18th june 09
892 if (NVL(FND_PROFILE.VALUE('IEX_SHOW_DSO_IN_HEADER'), 'Y') = 'Y') then
893 OPEN c_dso FOR
894 SELECT
895 ROUND(
896 ( (SUM( DECODE(PS.CLASS,
897 'INV', 1,
898 'DM', 1,
899 'CB', 1,
900 'DEP', 1,
901 'BR', 1, /* 22-JUL-2000 J Rautiainen BR Implementation */
902 0)
903 * PS.ACCTD_AMOUNT_DUE_REMAINING
904 ) * MAX(SP.CER_DSO_DAYS)
905 )
906 / DECODE(
907 SUM( DECODE(PS.CLASS,
908 'INV', 1,
909 'DM', 1,
910 'DEP', 1,
911 0)
912 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
913 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
914 0)) ,
915 0, 1,
916 SUM( DECODE(PS.CLASS,
917 'INV', 1,
918 'DM', 1,
919 'DEP', 1,
920 0)
921 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
922 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
923 0) )
924 )
925 ), 0) /* DSO */
926 FROM ar_system_parameters sp,
927 ar_payment_schedules ps
928 WHERE NVL(ps.receipt_confirmed_flag,'Y') = 'Y'
929 AND ps.payment_schedule_id = l_payment_schedule_id
930 AND ps.org_id=sp.org_id; --added for moac change
931 FETCH c_dso INTO x_object_info_rec.dso;
932 CLOSE c_dso;
933 end if;
934 --End bug 8359894 gnramasa 18th june 09
935
936 ELSIF p_object_type = 'BILL_TO' AND p_object_id IS NOT NULL THEN
937 l_party_id := null;
938 l_cust_account_id := null;
939 l_payment_schedule_id := null;
940 l_customer_site_use_id := p_object_id;
941
942 --start moac change
943 --if the functional currency codes of the ou's are different
944 --make the Amount overdue and Net balance fields as null.
945 if l_cnt_cur_codes <> 1 then
946 x_object_info_rec.current_balance:=null;
947 x_object_info_rec.amount_overdue:=null;
948 else
949 --end moac change
950
951 OPEN c_overdue FOR
952 SELECT sum(acctd_amount_due_remaining) amount
953 FROM ar_payment_schedules ps, iex_delinquencies del
954 WHERE ps.customer_site_use_id = p_object_id
955 --BEGIN-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
956 -- AND ps.due_date < sysdate
957 --END-FIX BUG#4356388-05/24/2005-JYPARK-amount overdue should inculde Pre-Delinquent transactions which are not past due
958 AND ps.status = 'OP'
959 AND del.payment_schedule_id = ps.payment_schedule_id
960 AND del.status IN ('DELINQUENT', 'PREDELINQUENT');
961
962 FETCH c_overdue INTO x_object_info_rec.amount_overdue;
963 CLOSE c_overdue;
964
965 --Start bug 7612000 gnramasa 4th Dec 08
966 -- If value of "IEX: Exclude dispute amount from remaining amount " is Yes
967 -- then calculate the Amount thats in dispute and substract it from the amount overdue.
968 if (NVL(FND_PROFILE.VALUE('IEX_EXCLUDE_DISPUTE_AMT_FROM_REMAINING_AMT'), 'N') = 'Y') then
969 select nvl(sum(cm.total_amount * -1),0)
970 into l_amount_in_dispute
971 from ra_cm_requests cm
972 where cm.customer_trx_id in (select distinct ps.customer_trx_id
973 FROM ar_payment_schedules ps,
974 ar_system_parameters parm
975 WHERE ps.customer_site_use_id = p_object_id
976 AND ps.org_id = parm.org_id
977 AND ps.status = 'OP')
978 and cm.status='PENDING_APPROVAL';
979
980 x_object_info_rec.amount_overdue := x_object_info_rec.amount_overdue - l_amount_in_dispute;
981 end if;
982 --End bug 7612000 gnramasa 4th Dec 08
983
984 OPEN c_balance FOR
985 SELECT SUM(NVL(acctd_amount_due_remaining,0))
986 FROM ar_payment_schedules ps,
987 -- Begin fix bug #5077320-jypark-adding parameter table to show amount for selected operating unit
988 ar_system_parameters parm
989 WHERE customer_site_use_id = p_object_id
990 AND ps.org_id = parm.org_id
991 AND status = 'OP';
992 -- SELECT SUM(DECODE(class, 'INV', acctd_amount_due_remaining,
993 -- 'DM', acctd_amount_due_remaining,
994 -- 'GUAR', acctd_amount_due_remaining,
995 -- 'CB', acctd_amount_due_remaining,
996 -- 'DM', acctd_amount_due_remaining,
997 -- 'DEP', acctd_amount_due_remaining,
998 -- 'CM', acctd_amount_due_remaining,
999 -- 'PMT', acctd_amount_due_remaining))
1000 -- FROM ar_payment_schedules
1001 -- End fix bug #5077320-jypark-adding parameter table to show amount for selected operating unit
1002
1003 FETCH c_balance INTO x_object_info_rec.current_balance;
1004 CLOSE c_balance;
1005
1006 end if; --end if for check on count of currency codes(moac change).
1007
1008 --Start bug 8359894 gnramasa 18th june 09
1009 if (NVL(FND_PROFILE.VALUE('IEX_SHOW_DSO_IN_HEADER'), 'Y') = 'Y') then
1010 /* OPEN c_dso FOR
1011 SELECT
1012 ROUND(
1013 ( (SUM( DECODE(PS.CLASS,
1014 'INV', 1,
1015 'DM', 1,
1016 'CB', 1,
1017 'DEP', 1,
1018 'BR', 1, /* 22-JUL-2000 J Rautiainen BR Implementation */
1019 /* 0)
1020 * PS.ACCTD_AMOUNT_DUE_REMAINING
1021 ) * MAX(SP.CER_DSO_DAYS)
1022 )
1023 / DECODE(
1024 SUM( DECODE(PS.CLASS,
1025 'INV', 1,
1026 'DM', 1,
1027 'DEP', 1,
1028 0)
1029 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
1030 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
1031 0)) ,
1032 0, 1,
1033 SUM( DECODE(PS.CLASS,
1034 'INV', 1,
1035 'DM', 1,
1036 'DEP', 1,
1037 0)
1038 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
1039 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
1040 0) )
1041 )
1042 ), 0) /* DSO */
1043 /* FROM ar_system_parameters sp,
1044 ar_payment_schedules ps
1045 WHERE ps.customer_site_use_id = l_customer_site_use_id
1046 AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y'
1047 AND ps.org_id=sp.org_id; --added for moac change
1048
1049 FETCH c_dso INTO x_object_info_rec.dso;
1050 CLOSE c_dso; */
1051
1052 BEGIN
1053 x_object_info_rec.dso := iex_coll_ind.get_conv_dso_ocm(null,null,l_customer_site_use_id);
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056 x_object_info_rec.dso := 0;
1057 END;
1058
1059 end if;
1060 --End bug 8359894 gnramasa 18th june 09
1061
1062
1063 END IF;
1064
1065 if p_object_source = 'AR' then
1066 get_last_payment_info(
1067 p_api_version => p_api_version,
1068 p_init_msg_list => p_init_msg_list,
1069 p_commit => p_commit,
1070 p_validation_level => p_validation_level,
1071 x_return_status => x_return_status,
1072 x_msg_count => x_msg_count,
1073 x_msg_data => x_msg_data,
1074 p_object_type => p_object_type,
1075 p_object_id => p_object_id,
1076 x_last_pmt_info_rec => l_last_pmt_info_rec);
1077
1078 x_object_info_rec.last_payment_amount := l_last_pmt_info_rec.amount;
1079 x_object_info_rec.last_payment_curr := l_last_pmt_info_rec.currency_code;
1080 x_object_info_rec.last_payment_due_date := l_last_pmt_info_rec.due_date;
1081 x_object_info_rec.last_payment_date := l_last_pmt_info_rec.receipt_date;
1082 x_object_info_rec.last_payment_status := l_last_pmt_info_rec.status;
1083 x_object_info_rec.last_payment_receipt_number := l_last_pmt_info_rec.receipt_number;
1084 x_object_info_rec.last_payment_id := l_last_pmt_info_rec.cash_receipt_id;
1085
1086 elsif p_object_source = 'OKL' then
1087
1088 Get_Last_OKL_Payment_Info(
1089 p_api_version => p_api_version,
1090 p_init_msg_list => p_init_msg_list,
1091 p_commit => p_commit,
1092 p_validation_level => p_validation_level,
1093 x_return_status => x_return_status,
1094 x_msg_count => x_msg_count,
1095 x_msg_data => x_msg_data,
1096 p_object_type => p_object_type,
1097 p_object_id => p_object_id,
1098 x_last_okl_pmt_info_rec => l_last_okl_pmt_info_rec);
1099
1100 x_object_info_rec.last_okl_payment_amount := l_last_okl_pmt_info_rec.AMOUNT_APPLIED;
1101 x_object_info_rec.last_okl_payment_curr := l_last_okl_pmt_info_rec.currency_code;
1102 x_object_info_rec.last_okl_payment_due_date := l_last_okl_pmt_info_rec.due_date;
1103 x_object_info_rec.last_okl_payment_date := l_last_okl_pmt_info_rec.APPLY_DATE;
1104 x_object_info_rec.last_okl_payment_status := l_last_okl_pmt_info_rec.RECEIPT_STATUS;
1105 x_object_info_rec.last_okl_payment_receipt_num := l_last_okl_pmt_info_rec.receipt_number;
1106 x_object_info_rec.last_okl_payment_id := l_last_okl_pmt_info_rec.cash_receipt_id;
1107
1108 end if;
1109 --start moac change
1110 --if the functional currency codes of the ou's are different
1111 --make the Amount overdue and Net balance fields as null.
1112 if l_cnt_cur_codes <> 1 then
1113 x_object_info_rec.current_balance_curr:=null;
1114 else
1115 --end moac change
1116 IEX_CURRENCY_PVT.GET_FUNCT_CURR(
1117 P_API_VERSION =>1.0,
1118 p_init_msg_list => 'T',
1119 p_commit => 'F',
1120 p_validation_level => 100,
1121 X_Functional_currency => x_object_info_rec.current_balance_curr,
1122 X_return_status => x_return_status,
1123 X_MSG_COUNT => x_msg_count,
1124 X_MSG_DATA => x_msg_data );
1125 end if; --end if for check on count of currency codes(moac change).
1126
1127 x_object_info_rec.amount_overdue_curr := x_object_info_rec.current_balance_curr;
1128
1129 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':end');
1130
1131 END Get_Object_Info;
1132
1133 PROCEDURE Get_Last_Payment_Info
1134 (p_api_version IN NUMBER := 1.0,
1135 p_init_msg_list IN VARCHAR2,
1136 p_commit IN VARCHAR2,
1137 p_validation_level IN NUMBER,
1138 x_return_status OUT NOCOPY VARCHAR2,
1139 x_msg_count OUT NOCOPY NUMBER,
1140 x_msg_data OUT NOCOPY VARCHAR2,
1141 p_object_type IN VARCHAR2,
1142 p_object_id IN NUMBER,
1143 x_last_pmt_info_rec OUT NOCOPY last_pmt_info_rec_type)
1144 IS
1145 l_api_version CONSTANT NUMBER := 1.0;
1146 l_api_name CONSTANT VARCHAR2(30) := 'Get_Last_Payment_Info';
1147 l_return_status VARCHAR2(1);
1148 l_msg_count NUMBER;
1149 l_msg_data VARCHAR2(32767);
1150
1151 l_party_id NUMBER;
1152 l_cust_account_id NUMBER;
1153 l_payment_schedule_id NUMBER;
1154
1155 TYPE c_last_pmt_cur_type IS REF CURSOR;
1156
1157 c_last_pmt c_last_pmt_cur_type;
1158 c_last_pmt2 c_last_pmt_cur_type;
1159
1160 --Begin - Andre Araujo - 03/13/06 - Bug#5024219 - Improving performance
1161 l_pk_query varchar2(2000);
1162 l_pk_from varchar2(200);
1163 l_pk_where varchar2(200);
1164 l_pk_group varchar2(200);
1165
1166 l_data_query varchar2(10000);
1167 l_data_from varchar2(2000);
1168 l_data_where varchar2(2000);
1169 l_data_group varchar2(2000);
1170
1171 TYPE cv_typ IS REF CURSOR;
1172 cv cv_typ;
1173
1174 l_account NUMBER;
1175 l_site NUMBER;
1176 l_org NUMBER;
1177 l_pay_date date;
1178
1179 l_receipt NUMBER;
1180 l_PSDueDate date;
1181 l_amount AR_TRX_BAL_SUMMARY.LAST_PAYMENT_AMOUNT%TYPE;
1182 l_currency AR_TRX_BAL_SUMMARY.CURRENCY%TYPE;
1183 l_paynumber AR_TRX_BAL_SUMMARY.LAST_PAYMENT_NUMBER%TYPE;
1184 l_status varchar2(200);
1185 l_partyid number;
1186 --End - Andre Araujo - 03/13/06 - Bug#5024219 - Improving performance
1187 --Begin-fix bug #5407151-JYPARK-08012006-hide last payment due on depending on profile IEX_SHOW_LAST_PMT_DUE
1188 l_show_last_pmt_due VARCHAR2(240);
1189 --End-fix bug #5407151-JYPARK-08012006-hide last payment due on depending on profile IEX_SHOW_LAST_PMT_DUE
1190
1191 BEGIN
1192 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':begin');
1193
1194 --Begin - Andre Araujo - 03/13/06 - Bug#5024219 - Improving performance
1195 --First we will find the PK for the record with the info we want
1196 BEGIN
1197 l_pk_query := 'select ca.party_id, TRX_SUM.CUST_ACCOUNT_ID, TRX_SUM.SITE_USE_ID, TRX_SUM.ORG_ID, TRX_SUM.CURRENCY, max(TRX_SUM.LAST_PAYMENT_DATE) pay_date ';
1198 l_pk_from := 'from AR_TRX_BAL_SUMMARY TRX_SUM, hz_cust_accounts ca ';
1199 l_pk_where := 'where ca.CUST_ACCOUNT_ID = TRX_SUM.CUST_ACCOUNT_ID ';
1200 l_pk_group := 'group by ca.party_id, TRX_SUM.CUST_ACCOUNT_ID, TRX_SUM.SITE_USE_ID, TRX_SUM.ORG_ID, TRX_SUM.CURRENCY ';
1201
1202 -- Begin - Bug#5358461 - Andre Araujo - 07/07/06 - This query is not MOAC ready
1203 l_pk_from := l_pk_from || ', ar_system_parameters arsys ';
1204 l_pk_where := l_pk_where || ' AND arsys.org_id = trx_sum.org_id ';
1205 l_pk_group := l_pk_group || ' order by pay_date desc '; -- To do this pay_date was added to the end of l_pk_query
1206 -- End - Bug#5358461 - Andre Araujo - 07/07/06 - This query is not MOAC ready
1207
1208
1209 -- Begin Fix bug #5417273-JYPARK-08/01/2006-Exclude if ar_trx_bal_summary.last_payment_date is null
1210 l_pk_where := l_pk_where || ' AND trx_sum.last_payment_date IS NOT NULL ';
1211 -- End Fix bug #5417273-JYPARK-08/01/2006-Exclude if ar_trx_bal_summary.last_payment_date is null
1212
1213 IF p_object_type = 'CUSTOMER' AND p_object_id IS NOT NULL THEN
1214 l_pk_where := l_pk_where || 'and ca.party_id = :1 ';
1215 ELSIF p_object_type = 'ACCOUNT' AND p_object_id IS NOT NULL THEN
1216 l_pk_where := l_pk_where || 'and TRX_SUM.CUST_ACCOUNT_ID = :1 ';
1217 ELSIF p_object_type = 'BILL_TO' AND p_object_id IS NOT NULL THEN
1218 l_pk_where := l_pk_where || 'and TRX_SUM.SITE_USE_ID = :1 ';
1219 ELSIF p_object_type = 'DELINQUENCY' AND p_object_id IS NOT NULL THEN
1220 OPEN c_last_pmt FOR
1221 SELECT acr.receipt_date,
1222 acr.cash_receipt_id,
1223 decode(apsa.payment_schedule_id, -1, null, APSA.due_date),
1224 ara.amount_applied amount,
1225 apsa.invoice_currency_Code currency_code ,
1226 acr.receipt_number,
1227 ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CHECK_STATUS', acr.status)
1228 FROM ar_payment_schedules apsa,
1229 ar_cash_receipts acr,
1230 ar_receivable_applications ara,
1231 ar_cash_receipt_history acrh,
1232 iex_delinquencies del
1233 WHERE ara.cash_receipt_id = acr.cash_receipt_id
1234 AND ara.applied_payment_schedule_id = apsa.payment_schedule_id
1235 AND apsa.payment_schedule_id = del.payment_schedule_id
1236 AND del.delinquency_id = p_object_id
1237 AND acr.cash_receipt_id = acrh.cash_receipt_id
1238 AND nvl(acr.confirmed_flag, 'Y') = 'Y'
1239 AND acr.reversal_date is null
1240 AND acrh.status not in (decode (acrh.factor_flag, 'Y', 'RISK_ELIMINATED',
1241 'N', ' '), 'REVERSED')
1242 AND acrh.current_record_flag = 'Y'
1243 ORDER BY 1 DESC, 2 DESC, 3 ASC;
1244
1245 FETCH c_last_pmt INTO x_last_pmt_info_rec.receipt_date,
1246 x_last_pmt_info_rec.cash_receipt_id,
1247 x_last_pmt_info_rec.due_date,
1248 x_last_pmt_info_rec.amount,
1249 x_last_pmt_info_rec.currency_code,
1250 x_last_pmt_info_rec.receipt_number,
1251 x_last_pmt_info_rec.status;
1252 CLOSE c_last_pmt;
1253
1254 return;
1255 END IF;
1256
1257 l_pk_query := l_pk_query || l_pk_from || l_pk_where || l_pk_group;
1258
1259 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Query: ' || l_pk_query);
1260
1261 -- Open ref cursor for the query
1262 OPEN cv FOR l_pk_query USING p_object_id;
1263 FETCH cv INTO l_partyid, l_account, l_site, l_org, l_currency, l_pay_date;
1264 CLOSE cv;
1265
1266 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Result: ' || l_account || ':: ' || l_site || ':: ' || l_org || ':: ' || l_currency || ':: ' || l_pay_date);
1267
1268 -- Now we have the primary key to our info, get the data
1269
1270 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Query: Getting the data');
1271
1272 -- Begin fix bug #5407151-JYPARK-08/01/2006-Hide 'Last Payment Due On' depending on profile 'IEX_SHOW_LAST_PMT_DUE'
1273 l_show_last_pmt_due := nvl(fnd_profile.value('IEX_SHOW_LAST_PMT_DUE'), 'Y');
1274 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':IEX_SHOW_LAST_PMT_DUE=' || l_show_last_pmt_due);
1275
1276 IF l_show_last_pmt_due = 'Y' THEN
1277 l_data_query := 'SELECT TRX_SUM.LAST_PAYMENT_DATE, CR.CASH_RECEIPT_ID, ';
1278 l_data_query := l_data_query || ' DECODE(PS.PAYMENT_SCHEDULE_ID, -1, NULL, PS.DUE_DATE), ';
1279 l_data_query := l_data_query || ' TRX_SUM.LAST_PAYMENT_AMOUNT, TRX_SUM.CURRENCY, TRX_SUM.LAST_PAYMENT_NUMBER, ';
1280 l_data_query := l_data_query || ' ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING(''CHECK_STATUS'', CR.STATUS) ';
1281 l_data_from := 'FROM ';
1282 l_data_from := l_data_from || ' AR_TRX_BAL_SUMMARY TRX_SUM, HZ_CUST_ACCOUNTS CA, AR_CASH_RECEIPTS CR, ';
1283 l_data_from := l_data_from || ' AR_RECEIVABLE_APPLICATIONS RA, AR_PAYMENT_SCHEDULES PS ';
1284 l_data_where := 'WHERE TRX_SUM.CUST_ACCOUNT_ID = :1 ';
1285 l_data_where := l_data_where || ' and TRX_SUM.SITE_USE_ID = :2 ';
1286 l_data_where := l_data_where || ' and TRX_SUM.ORG_ID = :3 ';
1287 l_data_where := l_data_where || ' and TRX_SUM.CURRENCY = :4 ';
1288 l_data_where := l_data_where || ' and CA.PARTY_ID = :5 ';
1289 l_data_where := l_data_where || ' and ca.cust_account_id = trx_sum.cust_account_id '; --Added for bug#7512425 by PNAVEENK
1290 l_data_where := l_data_where || ' AND CR.PAY_FROM_CUSTOMER = TRX_SUM.CUST_ACCOUNT_ID '; --Added for bug#7512425 by PNAVEENK
1291
1292 if p_object_type = 'BILL_TO' AND p_object_id IS NOT NULL THEN -- added for bug 8461423
1293 l_data_where := l_data_where || ' AND CR.CUSTOMER_SITE_USE_ID = TRX_SUM.SITE_USE_ID '; -- Added for bug#7512425 by PNAVEENK
1294 end if;
1295
1296 l_data_where := l_data_where || ' and trunc(cr.receipt_date) = trunc(trx_sum.last_payment_date) '; -- Added for bug#7512425 by PNAVEENK
1297 l_data_where := l_data_where || ' and abs(cr.amount) = abs(trx_sum.last_payment_amount) '; --Added for bug#7512425 by PNAVEENK
1298 l_data_where := l_data_where || ' AND CR.RECEIPT_NUMBER = TRX_SUM.LAST_PAYMENT_NUMBER ';
1299 l_data_where := l_data_where || ' AND RA.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID ';
1300 l_data_where := l_data_where || ' AND PS.PAYMENT_SCHEDULE_ID(+) = RA.APPLIED_PAYMENT_SCHEDULE_ID';
1301 l_data_where := l_data_where || ' AND trx_sum.last_payment_date is not null'; -- added for bug 8461423
1302
1303 -- Begin - Bug#5358461 - Andre Araujo - 07/07/06 - We should ignore where payment schedule id is -1
1304 -- l_data_where := l_data_where || ' AND PS.PAYMENT_SCHEDULE_ID <> -1'; commented by ehuh 2/27/07 bug 5665646
1305 l_data_where := l_data_where || ' AND PS.PAYMENT_SCHEDULE_ID(+) > 0'; -- ehuh 2/27/07 bug 5665646 , 8461423
1306 l_data_group := ' order by due_date asc';
1307
1308 --l_data_query := l_data_query || l_data_from || l_data_where; -- Adder order by
1309 l_data_query := l_data_query || l_data_from || l_data_where || l_data_group; -- Adder order by
1310 ELSE
1311 l_data_query := 'SELECT TRX_SUM.LAST_PAYMENT_DATE, null, ';
1312 l_data_query := l_data_query || ' null, ';
1313 l_data_query := l_data_query || ' TRX_SUM.LAST_PAYMENT_AMOUNT, TRX_SUM.CURRENCY, TRX_SUM.LAST_PAYMENT_NUMBER, ';
1314 l_data_query := l_data_query || ' null ';
1315 l_data_from := 'FROM ';
1316 l_data_from := l_data_from || ' AR_TRX_BAL_SUMMARY TRX_SUM, HZ_CUST_ACCOUNTS CA ';
1317 l_data_where := 'WHERE TRX_SUM.CUST_ACCOUNT_ID = :1 ';
1318 l_data_where := l_data_where || ' and TRX_SUM.SITE_USE_ID = :2 ';
1319 l_data_where := l_data_where || ' and TRX_SUM.ORG_ID = :3 ';
1320 l_data_where := l_data_where || ' and TRX_SUM.CURRENCY = :4 ';
1321 l_data_where := l_data_where || ' and CA.PARTY_ID = :5 ';
1322
1323 l_data_query := l_data_query || l_data_from || l_data_where; -- Adder order by
1324
1325 END IF;
1326
1327 -- End - Bug#5358461 - Andre Araujo - 07/07/06 - We should ignore where payment schedule id is -1
1328
1329 -- End fix bug #5407151-JYPARK-08/01/2006-Hide 'Last Payment Due On' depending on profile 'IEX_SHOW_LAST_PMT_DUE'
1330
1331 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Data Query: ' || l_data_query);
1332
1333
1334 -- Open ref cursor for the query
1335 OPEN cv FOR l_data_query USING l_account, l_site, l_org, l_currency, l_partyid;
1336 FETCH cv INTO l_pay_date,l_receipt,l_PSDueDate,l_amount,l_currency,l_paynumber,l_status;
1337 CLOSE cv;
1338
1339 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Data Result: ' || l_pay_date || ':: ' || l_receipt || ':: ' || l_PSDueDate || ':: ' || l_amount || ':: ' || l_currency || ':: ' || l_paynumber || ':: ' || l_status);
1340
1341 x_last_pmt_info_rec.receipt_date := l_pay_date;
1342 x_last_pmt_info_rec.cash_receipt_id:= l_receipt;
1343 x_last_pmt_info_rec.due_date := l_PSDueDate;
1344 x_last_pmt_info_rec.amount := l_amount;
1345 x_last_pmt_info_rec.currency_code := l_currency;
1346 x_last_pmt_info_rec.receipt_number := l_paynumber;
1347 x_last_pmt_info_rec.status := l_status;
1348
1349 -- Now that we got the cash receipt ID we will get the due date of the payment schedule where the receipt was applied
1350
1351 -- Begin - fix bug #5665646- ehuh -2/27/2007-remove duplicate code
1352 -- IF x_last_pmt_info_rec.cash_receipt_id IS NOT NULL THEN
1353 -- OPEN c_last_pmt2 FOR
1354 -- SELECT decode(ps.payment_schedule_id, -1, null, ps.due_date)
1355 -- FROM ar_receivable_applications ra,
1356 -- ar_payment_schedules ps
1357 -- WHERE ra.cash_receipt_id = l_receipt
1358 -- AND ps.payment_schedule_id = ra.applied_payment_schedule_id;
1359 --
1360 -- FETCH c_last_pmt2 INTO x_last_pmt_info_rec.due_date;
1361 -- CLOSE c_last_pmt2;
1362 -- END IF;
1363 -- endfix - bug #5665646- ehuh -2/27/2007-remove duplicate code
1364
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':EXCEPTION!!!!!');
1368 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Query: ' || l_pk_query);
1369 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Result: ' || l_account || ':: ' || l_site || ':: ' || l_org || ':: ' || l_currency || ':: ' || l_pay_date);
1370 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Data Query: ' || l_data_query);
1371 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Data Result: ' || l_pay_date || ':: ' || l_receipt || ':: ' || l_PSDueDate || ':: ' || l_amount || ':: ' || l_currency || ':: ' || l_paynumber || ':: ' || l_status);
1372 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || SQLCODE || SQLERRM);
1373 END ;
1374 --End - Andre Araujo - 03/13/06 - Bug#5024219 - Improving performance
1375
1376 /* --Begin - Andre Araujo - 03/13/06 - Bug#5024219 - Improving performance -- Removed code replaced with the one above
1377 -- Begin fix bug #4930425-jypark-01/10/2006-change query to remove full table scan
1378 IF p_object_type = 'CUSTOMER' AND p_object_id IS NOT NULL THEN
1379 -- OPEN c_last_pmt FOR
1380 -- SELECT trx_sum.last_payment_date,
1381 -- cr.cash_receipt_id,
1382 -- decode(ps.payment_schedule_id, -1, null, ps.due_date),
1383 -- trx_sum.last_payment_amount,
1384 -- trx_sum.currency,
1385 -- trx_sum.last_payment_number,
1386 -- ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CHECK_STATUS', cr.status)
1387 -- FROM ar_trx_bal_summary trx_sum,
1388 -- hz_cust_accounts ca, ar_cash_receipts cr,
1389 -- ar_receivable_applications ra,
1390 -- ar_payment_schedules ps
1391 -- WHERE trx_sum.cust_account_id = ca.cust_account_id
1392 -- AND ca.party_id = p_object_id
1393 -- AND cr.receipt_number = trx_sum.last_payment_number
1394 -- AND ra.cash_receipt_id(+) = cr.cash_receipt_id
1395 -- AND ps.payment_schedule_id(+) = ra.applied_payment_schedule_id
1396 -- ORDER BY 1 DESC, 2 DESC, 3 ASC;
1397 --
1398 -- FETCH c_last_pmt INTO x_last_pmt_info_rec.receipt_date,
1399 -- x_last_pmt_info_rec.cash_receipt_id,
1400 -- x_last_pmt_info_rec.due_date,
1401 -- x_last_pmt_info_rec.amount,
1402 -- x_last_pmt_info_rec.currency_code,
1403 -- x_last_pmt_info_rec.receipt_number,
1404 -- x_last_pmt_info_rec.status;
1405 -- CLOSE c_last_pmt;
1406
1407 OPEN c_last_pmt FOR
1408 SELECT trx_sum.last_payment_date,
1409 cr.cash_receipt_id,
1410 trx_sum.last_payment_amount,
1411 trx_sum.currency,
1412 trx_sum.last_payment_number,
1413 ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CHECK_STATUS', cr.status)
1414 FROM ar_trx_bal_summary trx_sum,
1415 hz_cust_accounts ca, ar_cash_receipts cr
1416 WHERE trx_sum.cust_account_id = ca.cust_account_id
1417 AND ca.party_id = p_object_id
1418 AND cr.receipt_number = trx_sum.last_payment_number
1419 ORDER BY 1 DESC, 2 DESC;
1420
1421 FETCH c_last_pmt INTO x_last_pmt_info_rec.receipt_date,
1422 x_last_pmt_info_rec.cash_receipt_id,
1423 x_last_pmt_info_rec.amount,
1424 x_last_pmt_info_rec.currency_code,
1425 x_last_pmt_info_rec.receipt_number,
1426 x_last_pmt_info_rec.status;
1427 CLOSE c_last_pmt;
1428
1429 IF x_last_pmt_info_rec.cash_receipt_id IS NOT NULL THEN
1430
1431 OPEN c_last_pmt2 FOR
1432 SELECT decode(ps.payment_schedule_id, -1, null, ps.due_date)
1433 FROM ar_receivable_applications ra,
1434 ar_payment_schedules ps
1435 WHERE ra.cash_receipt_id = x_last_pmt_info_rec.cash_receipt_id
1436 AND ps.payment_schedule_id = ra.applied_payment_schedule_id;
1437
1438 FETCH c_last_pmt2 INTO x_last_pmt_info_rec.due_date;
1439 CLOSE c_last_pmt2;
1440 END IF;
1441
1442 ELSIF p_object_type = 'ACCOUNT' AND p_object_id IS NOT NULL THEN
1443 -- OPEN c_last_pmt FOR
1444 -- SELECT trx_sum.last_payment_date,
1445 -- cr.cash_receipt_id,
1446 -- decode(ps.payment_schedule_id, -1, null, ps.due_date),
1447 -- trx_sum.last_payment_amount,
1448 -- trx_sum.currency,
1449 -- trx_sum.last_payment_number,
1450 -- ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CHECK_STATUS', cr.status)
1451 -- FROM ar_trx_bal_summary trx_sum,
1452 -- ar_cash_receipts cr,
1453 -- ar_receivable_applications ra,
1454 -- ar_payment_schedules ps
1455 -- WHERE trx_sum.cust_account_id = p_object_id
1456 -- AND cr.receipt_number = trx_sum.last_payment_number
1457 -- AND ra.cash_receipt_id(+) = cr.cash_receipt_id
1458 -- AND ps.payment_schedule_id(+) = ra.applied_payment_schedule_id
1459 -- ORDER BY 1 DESC, 2 DESC, 3 ASC;
1460 --
1461 -- FETCH c_last_pmt INTO x_last_pmt_info_rec.receipt_date,
1462 -- x_last_pmt_info_rec.cash_receipt_id,
1463 -- x_last_pmt_info_rec.due_date,
1464 -- x_last_pmt_info_rec.amount,
1465 -- x_last_pmt_info_rec.currency_code,
1466 -- x_last_pmt_info_rec.receipt_number,
1467 -- x_last_pmt_info_rec.status;
1468 -- CLOSE c_last_pmt;
1469
1470 OPEN c_last_pmt FOR
1471 SELECT trx_sum.last_payment_date,
1472 cr.cash_receipt_id,
1473 trx_sum.last_payment_amount,
1474 trx_sum.currency,
1475 trx_sum.last_payment_number,
1476 ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CHECK_STATUS', cr.status)
1477 FROM ar_trx_bal_summary trx_sum,
1478 ar_cash_receipts cr
1479 WHERE trx_sum.cust_account_id = p_object_id
1480 AND cr.receipt_number = trx_sum.last_payment_number
1481 ORDER BY 1 DESC, 2 DESC;
1482
1483 FETCH c_last_pmt INTO x_last_pmt_info_rec.receipt_date,
1484 x_last_pmt_info_rec.cash_receipt_id,
1485 x_last_pmt_info_rec.amount,
1486 x_last_pmt_info_rec.currency_code,
1487 x_last_pmt_info_rec.receipt_number,
1488 x_last_pmt_info_rec.status;
1489 CLOSE c_last_pmt;
1490
1491
1492 IF x_last_pmt_info_rec.cash_receipt_id IS NOT NULL THEN
1493
1494 OPEN c_last_pmt2 FOR
1495 SELECT decode(ps.payment_schedule_id, -1, null, ps.due_date)
1496 FROM ar_receivable_applications ra,
1497 ar_payment_schedules ps
1498 WHERE ra.cash_receipt_id = x_last_pmt_info_rec.cash_receipt_id
1499 AND ps.payment_schedule_id = ra.applied_payment_schedule_id;
1500
1501 FETCH c_last_pmt2 INTO x_last_pmt_info_rec.due_date;
1502 CLOSE c_last_pmt2;
1503 END IF;
1504
1505
1506 ELSIF p_object_type = 'DELINQUENCY' AND p_object_id IS NOT NULL THEN
1507 OPEN c_last_pmt FOR
1508 SELECT acr.receipt_date,
1509 acr.cash_receipt_id,
1510 decode(apsa.payment_schedule_id, -1, null, APSA.due_date),
1511 ara.amount_applied amount,
1512 apsa.invoice_currency_Code currency_code ,
1513 acr.receipt_number,
1514 ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CHECK_STATUS', acr.status)
1515 FROM ar_payment_schedules apsa,
1516 ar_cash_receipts acr,
1517 ar_receivable_applications ara,
1518 ar_cash_receipt_history acrh,
1519 iex_delinquencies del
1520 WHERE ara.cash_receipt_id = acr.cash_receipt_id
1521 AND ara.applied_payment_schedule_id = apsa.payment_schedule_id
1522 AND apsa.payment_schedule_id = del.payment_schedule_id
1523 AND del.delinquency_id = p_object_id
1524 AND acr.cash_receipt_id = acrh.cash_receipt_id
1525 AND nvl(acr.confirmed_flag, 'Y') = 'Y'
1526 AND acr.reversal_date is null
1527 AND acrh.status not in (decode (acrh.factor_flag, 'Y', 'RISK_ELIMINATED',
1528 'N', ' '), 'REVERSED')
1529 AND acrh.current_record_flag = 'Y'
1530 -- Begin fix bug #4932926-JYPARK-02/07/2006-remove unecesaary query for performance
1531 --AND ACR.receipt_date =
1532 -- (SELECT max(a.receipt_date)
1533 -- FROM ar_cash_receipts a,
1534 -- ar_receivable_applications b,
1535 -- ar_cash_receipt_history c
1536 -- WHERE a.cash_receipt_id = b.cash_receipt_id
1537 -- AND b.applied_payment_schedule_id = apsa.payment_schedule_id
1538 -- AND a.reversal_date is null
1539 -- AND nvl(a.confirmed_flag, 'Y') = 'Y'
1540 -- AND a.cash_receipt_id = c.cash_receipt_id
1541 -- AND c.status not in (decode (C.factor_flag, 'Y', 'RISK_ELIMINATED',
1542 -- 'N', ' '), 'REVERSED')
1543 -- AND c.current_record_flag = 'Y'
1544 -- )
1545 -- End fix bug #4932926-JYPARK-02/07/2006-remove unecesaary query for performance
1546 ORDER BY 1 DESC, 2 DESC, 3 ASC;
1547
1548 FETCH c_last_pmt INTO x_last_pmt_info_rec.receipt_date,
1549 x_last_pmt_info_rec.cash_receipt_id,
1550 x_last_pmt_info_rec.due_date,
1551 x_last_pmt_info_rec.amount,
1552 x_last_pmt_info_rec.currency_code,
1553 x_last_pmt_info_rec.receipt_number,
1554 x_last_pmt_info_rec.status;
1555 CLOSE c_last_pmt;
1556 ELSIF p_object_type = 'BILL_TO' AND p_object_id IS NOT NULL THEN
1557
1558 OPEN c_last_pmt FOR
1559 SELECT trx_sum.last_payment_date,
1560 cr.cash_receipt_id,
1561 trx_sum.last_payment_amount,
1562 trx_sum.currency,
1563 trx_sum.last_payment_number,
1564 ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CHECK_STATUS', cr.status)
1565 FROM ar_trx_bal_summary trx_sum,
1566 ar_cash_receipts cr
1567 WHERE trx_sum.site_use_id = p_object_id
1568 AND cr.receipt_number = trx_sum.last_payment_number
1569 ORDER BY 1 DESC, 2 DESC;
1570
1571 FETCH c_last_pmt INTO x_last_pmt_info_rec.receipt_date,
1572 x_last_pmt_info_rec.cash_receipt_id,
1573 x_last_pmt_info_rec.amount,
1574 x_last_pmt_info_rec.currency_code,
1575 x_last_pmt_info_rec.receipt_number,
1576 x_last_pmt_info_rec.status;
1577 CLOSE c_last_pmt;
1578
1579 IF x_last_pmt_info_rec.cash_receipt_id IS NOT NULL THEN
1580
1581 OPEN c_last_pmt2 FOR
1582 SELECT decode(ps.payment_schedule_id, -1, null, ps.due_date)
1583 FROM ar_receivable_applications ra,
1584 ar_payment_schedules ps
1585 WHERE ra.cash_receipt_id = x_last_pmt_info_rec.cash_receipt_id
1586 AND ps.payment_schedule_id = ra.applied_payment_schedule_id;
1587
1588 FETCH c_last_pmt2 INTO x_last_pmt_info_rec.due_date;
1589 CLOSE c_last_pmt2;
1590 END IF;
1591
1592 END IF;
1593 -- End fix bug #4930425-jypark-01/10/2006-change query to remove full table scan
1594 --End - Andre Araujo - 03/13/06 - Bug#5024219 - Improving performance -- Remoed code replaced with the one above*/
1595
1596 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':end');
1597 END Get_Last_Payment_Info;
1598
1599 PROCEDURE Get_Last_OKL_Payment_Info
1600 (p_api_version IN NUMBER := 1.0,
1601 p_init_msg_list IN VARCHAR2,
1602 p_commit IN VARCHAR2,
1603 p_validation_level IN NUMBER,
1604 x_return_status OUT NOCOPY VARCHAR2,
1605 x_msg_count OUT NOCOPY NUMBER,
1606 x_msg_data OUT NOCOPY VARCHAR2,
1607 p_object_type IN VARCHAR2,
1608 p_object_id IN NUMBER,
1609 x_last_okl_pmt_info_rec OUT NOCOPY last_okl_pmt_info_rec_type)
1610 IS
1611 l_api_version CONSTANT NUMBER := 1.0;
1612 l_api_name CONSTANT VARCHAR2(30) := 'Get_Last_OKL_Payment_Info';
1613 l_return_status VARCHAR2(1);
1614 l_msg_count NUMBER;
1615 l_msg_data VARCHAR2(32767);
1616
1617 TYPE c_last_pmt_cur_type IS REF CURSOR;
1618 c_last_pmt c_last_pmt_cur_type;
1619
1620 BEGIN
1621 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':begin');
1622
1623 -- Begin fix bug #4930425-jypark-01/10/2006-removed obsolete query
1624 -- IF p_object_type = 'CUSTOMER' AND p_object_id IS NOT NULL THEN
1625 -- OPEN c_last_pmt FOR
1626 -- SELECT pmt.APPLY_DATE,
1627 -- pmt.RECEIVABLE_APPLICATION_ID,
1628 -- pmt.DUE_DATE,
1629 -- pmt.CASH_RECEIPT_ID,
1630 -- pmt.REFERENCE_NUMBER,
1631 -- pmt.AMOUNT_APPLIED,
1632 -- pmt.CURRENCY_CODE,
1633 -- pmt.RECEIPT_STATUS_DSP
1634 -- FROM iex_pay_okl_history_v pmt
1635 -- WHERE pmt.party_id = p_object_id and
1636 -- pmt.REVERSAL_GL_DATE is null
1637 -- ORDER BY 1 DESC;
1638 --
1639 -- FETCH c_last_pmt INTO x_last_okl_pmt_info_rec.apply_date,
1640 -- x_last_okl_pmt_info_rec.receivable_application_id,
1641 -- x_last_okl_pmt_info_rec.due_date,
1642 -- x_last_okl_pmt_info_rec.cash_receipt_id,
1643 -- x_last_okl_pmt_info_rec.receipt_number,
1644 -- x_last_okl_pmt_info_rec.amount_applied,
1645 -- x_last_okl_pmt_info_rec.currency_code,
1646 -- x_last_okl_pmt_info_rec.receipt_status;
1647 -- CLOSE c_last_pmt;
1648 --
1649 -- ELSIF p_object_type = 'ACCOUNT' AND p_object_id IS NOT NULL THEN
1650 -- OPEN c_last_pmt FOR
1651 -- SELECT pmt.APPLY_DATE,
1652 -- pmt.RECEIVABLE_APPLICATION_ID,
1653 -- pmt.DUE_DATE,
1654 -- pmt.CASH_RECEIPT_ID,
1655 -- pmt.REFERENCE_NUMBER,
1656 -- pmt.AMOUNT_APPLIED,
1657 -- pmt.CURRENCY_CODE,
1658 -- pmt.RECEIPT_STATUS_DSP
1659 -- FROM iex_pay_okl_history_v pmt
1660 -- WHERE pmt.customer_id = p_object_id and
1661 -- pmt.REVERSAL_GL_DATE is null
1662 -- ORDER BY 1 DESC;
1663 --
1664 -- FETCH c_last_pmt INTO x_last_okl_pmt_info_rec.apply_date,
1665 -- x_last_okl_pmt_info_rec.receivable_application_id,
1666 -- x_last_okl_pmt_info_rec.due_date,
1667 -- x_last_okl_pmt_info_rec.cash_receipt_id,
1668 -- x_last_okl_pmt_info_rec.receipt_number,
1669 -- x_last_okl_pmt_info_rec.amount_applied,
1670 -- x_last_okl_pmt_info_rec.currency_code,
1671 -- x_last_okl_pmt_info_rec.receipt_status;
1672 -- CLOSE c_last_pmt;
1673 --
1674 -- ELSIF p_object_type = 'DELINQUENCY' AND p_object_id IS NOT NULL THEN
1675 -- OPEN c_last_pmt FOR
1676 -- SELECT pmt.APPLY_DATE,
1677 -- pmt.RECEIVABLE_APPLICATION_ID,
1678 -- pmt.DUE_DATE,
1679 -- pmt.CASH_RECEIPT_ID,
1680 -- pmt.REFERENCE_NUMBER,
1681 -- pmt.AMOUNT_APPLIED,
1682 -- pmt.CURRENCY_CODE,
1683 -- pmt.RECEIPT_STATUS_DSP
1684 -- FROM iex_pay_okl_history_v pmt
1685 -- WHERE pmt.delinquency_id is not null and
1686 -- pmt.delinquency_id = p_object_id and
1687 -- pmt.REVERSAL_GL_DATE is null
1688 -- ORDER BY 1 DESC;
1689 --
1690 -- FETCH c_last_pmt INTO x_last_okl_pmt_info_rec.apply_date,
1691 -- x_last_okl_pmt_info_rec.receivable_application_id,
1692 -- x_last_okl_pmt_info_rec.due_date,
1693 -- x_last_okl_pmt_info_rec.cash_receipt_id,
1694 -- x_last_okl_pmt_info_rec.receipt_number,
1695 -- x_last_okl_pmt_info_rec.amount_applied,
1696 -- x_last_okl_pmt_info_rec.currency_code,
1697 -- x_last_okl_pmt_info_rec.receipt_status;
1698 -- CLOSE c_last_pmt;
1699 --
1700 -- ELSIF p_object_type = 'BILL_TO' AND p_object_id IS NOT NULL THEN
1701 -- OPEN c_last_pmt FOR
1702 -- SELECT pmt.APPLY_DATE,
1703 -- pmt.RECEIVABLE_APPLICATION_ID,
1704 -- pmt.DUE_DATE,
1705 -- pmt.CASH_RECEIPT_ID,
1706 -- pmt.REFERENCE_NUMBER,
1707 -- pmt.AMOUNT_APPLIED,
1708 -- pmt.CURRENCY_CODE,
1709 -- pmt.RECEIPT_STATUS_DSP
1710 -- FROM iex_pay_okl_history_v pmt
1711 -- WHERE pmt.customer_site_use_id = p_object_id and
1712 -- pmt.REVERSAL_GL_DATE is null
1713 -- ORDER BY 1 DESC;
1714 --
1715 -- FETCH c_last_pmt INTO x_last_okl_pmt_info_rec.apply_date,
1716 -- x_last_okl_pmt_info_rec.receivable_application_id,
1717 -- x_last_okl_pmt_info_rec.due_date,
1718 -- x_last_okl_pmt_info_rec.cash_receipt_id,
1719 -- x_last_okl_pmt_info_rec.receipt_number,
1720 -- x_last_okl_pmt_info_rec.amount_applied,
1721 -- x_last_okl_pmt_info_rec.currency_code,
1722 -- x_last_okl_pmt_info_rec.receipt_status;
1723 -- CLOSE c_last_pmt;
1724 --
1725 -- END IF;
1726 -- End fix bug #4930425-jypark-01/10/2006-removed obsolete query
1727
1728 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':end');
1729
1730 END Get_Last_OKL_Payment_Info;
1731
1732 PROCEDURE get_contact_point_info(p_api_version IN NUMBER := 1.0,
1733 p_init_msg_list IN VARCHAR2,
1734 p_commit IN VARCHAR2,
1735 p_validation_level IN NUMBER,
1736 p_party_id IN NUMBER,
1737 x_return_status OUT NOCOPY VARCHAR2,
1738 x_msg_count OUT NOCOPY NUMBER,
1739 x_msg_data OUT NOCOPY VARCHAR2,
1740 x_contact_point_info_rec OUT NOCOPY contact_point_info_rec_type)
1741 IS
1742 l_api_name CONSTANT VARCHAR2(30) := 'GET_CONTACT_POINT_INFO';
1743 CURSOR c_contact_point(x_party_id number) IS
1744 SELECT DECODE(CONTACT_POINT_PURPOSE, 'COLLECTIONS', 1, 2) C1,
1745 DECODE(PRIMARY_BY_PURPOSE, 'Y', 1, 2) C2,
1746 DECODE(PRIMARY_FLAG, 'Y', 1, 2) C3,
1747 contact_point_id, phone_country_code, phone_area_code, phone_number, phone_extension,
1748 ARPT_SQL_FUNC_UTIL.get_lookup_meaning('PHONE_LINE_TYPE', phone_line_type) phone_line_type_meaning,
1749 email_address, contact_point_type
1750 FROM hz_contact_points
1751 WHERE owner_table_name = 'HZ_PARTIES'
1752 AND owner_table_id = x_party_id
1753 AND ((contact_point_type = 'EMAIL') OR
1754 (contact_point_type = 'PHONE' AND phone_line_type NOT IN ('PAGER', 'FAX')))
1755 AND NVL(do_not_use_flag, 'N') = 'N'
1756 AND status = 'A'
1757 ORDER BY 1,2,3;
1758
1759 l_contact_point_row c_contact_point%ROWTYPE;
1760 l_email_found VARCHAR2(1);
1761 l_phone_found VARCHAR2(1);
1762 BEGIN
1763 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':begin');
1764 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':p_party_id =' || p_party_id);
1765
1766 l_email_found := 'N';
1767 l_phone_found := 'N';
1768
1769 OPEN c_contact_point(p_party_id);
1770 FETCH c_contact_point INTO l_contact_point_row;
1771
1772 LOOP
1773 EXIT WHEN (c_contact_point%NOTFOUND);
1774
1775 IF l_phone_found = 'N' AND l_contact_point_row.contact_point_type = 'PHONE' THEN
1776 x_contact_point_info_rec.phone_contact_point_id := l_contact_point_row.contact_point_id;
1777 x_contact_point_info_rec.phone_country_code := l_contact_point_row.phone_country_code;
1778 x_contact_point_info_rec.phone_area_code := l_contact_point_row.phone_area_code;
1779 x_contact_point_info_rec.phone_number := l_contact_point_row.phone_number;
1780 x_contact_point_info_rec.phone_extension := l_contact_point_row.phone_extension;
1781 x_contact_point_info_rec.phone_line_type_meaning := l_contact_point_row.phone_line_type_meaning;
1782 l_phone_found := 'Y';
1783 ELSIF l_email_found = 'N' AND l_contact_point_row.contact_point_type = 'EMAIL' THEN
1784 x_contact_point_info_rec.email_contact_point_id := l_contact_point_row.contact_point_id;
1785 x_contact_point_info_rec.email_address := l_contact_point_row.email_address;
1786 l_email_found := 'Y';
1787 END IF;
1788
1789 EXIT WHEN (l_email_found = 'Y' AND l_phone_found = 'Y');
1790 FETCH c_contact_point INTO l_contact_point_row;
1791 END LOOP;
1792
1793 CLOSE c_contact_point;
1794
1795 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':end');
1796 END get_contact_point_Info;
1797
1798 PROCEDURE get_location_Info(
1799 p_api_version IN NUMBER := 1.0,
1800 p_init_msg_list IN VARCHAR2,
1801 p_commit IN VARCHAR2,
1802 p_validation_level IN NUMBER,
1803 p_party_id IN NUMBER,
1804 x_return_status OUT NOCOPY VARCHAR2,
1805 x_msg_count OUT NOCOPY NUMBER,
1806 x_msg_data OUT NOCOPY VARCHAR2,
1807 x_location_info_rec OUT NOCOPY location_info_rec_type)
1808 IS
1809 l_api_name CONSTANT VARCHAR2(30) := 'GET_LOCATION_INFO';
1810 cursor c_get_location(x_party_id number, x_primary_flag varchar2) is
1811 select location_id, address2, address3, address4, party_id,last_update_date,
1812 party_site_id,party_site_number,site_last_update_date, LAST_UPDATED_BY ,LAST_UPDATE_LOGIN , CREATED_BY, CREATION_DATE, address1, city, state, province,
1813 postal_code, county, country_name, country_code, address_lines_phonetic,
1814 po_box_number, house_number, street_suffix, street,
1815 street_number, floor, suite, time_zone,time_zone_meaning, timezone_id, object_version_number, site_object_version_number,created_by_module, application_id
1816 from ast_locations_v
1817 where party_id = x_party_id
1818 and primary_flag = x_primary_flag;
1819 l_location_row c_get_location%ROWTYPE;
1820 x_get_location_found boolean := false;
1821 BEGIN
1822 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':begin');
1823 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':p_party_id=' || p_party_id);
1824
1825 open c_get_location(p_party_id, 'Y');
1826 fetch c_get_location into l_location_row;
1827 IF c_get_location%FOUND THEN
1828
1829 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':found location_row');
1830
1831 x_location_info_rec.location_id := l_location_row.location_id;
1832 x_location_info_rec.address2 := l_location_row.address2;
1833 x_location_info_rec.address3 := l_location_row.address3;
1834 x_location_info_rec.address4 := l_location_row.address4;
1835 x_location_info_rec.party_id := l_location_row.party_id;
1836 x_location_info_rec.address_lines_phonetic := l_location_row.address_lines_phonetic;
1837 x_location_info_rec.po_box_number := l_location_row.po_box_number;
1838 x_location_info_rec.house_number := l_location_row.house_number;
1839 x_location_info_rec.street_suffix := l_location_row.street_suffix;
1840 x_location_info_rec.street := l_location_row.street;
1841 x_location_info_rec.street_number := l_location_row.street_number;
1842 x_location_info_rec.floor := l_location_row.floor;
1843 x_location_info_rec.suite := l_location_row.suite;
1844 x_location_info_rec.time_zone := l_location_row.time_zone;
1845 x_location_info_rec.time_zone_meaning := l_location_row.time_zone_meaning;
1846 x_location_info_rec.timezone_id := l_location_row.timezone_id;
1847 x_location_info_rec.last_update_date := l_location_row.last_update_date;
1848 x_location_info_rec.creation_date := l_location_row.creation_date;
1849 x_location_info_rec.created_by := l_location_row.created_by;
1850 x_location_info_rec.last_updated_by := l_location_row.last_updated_by;
1851 x_location_info_rec.created_by := l_location_row.created_by;
1852 x_location_info_rec.last_update_login := l_location_row.last_update_login;
1853 x_location_info_rec.site_last_update_date := l_location_row.site_last_update_date;
1854 x_location_info_rec.party_site_id := l_location_row.party_site_id;
1855 x_location_info_rec.party_site_number := l_location_row.party_site_number;
1856 x_location_info_rec.address1 := l_location_row.address1;
1857 x_location_info_rec.city := l_location_row.city;
1858 x_location_info_rec.state := l_location_row.state;
1859 x_location_info_rec.province := l_location_row.province;
1860 x_location_info_rec.postal_code := l_location_row.postal_code;
1861 x_location_info_rec.county := l_location_row.county;
1862 x_location_info_rec.country_name := l_location_row.country_name;
1863 x_location_info_rec.country_code := l_location_row.country_code;
1864 x_location_info_rec.object_version_number := l_location_row.object_version_number;
1865 x_location_info_rec.site_object_version_number := l_location_row.site_object_version_number;
1866 x_location_info_rec.created_by_module := l_location_row.created_by_module;
1867 x_location_info_rec.application_id := l_location_row.application_id;
1868
1869 END IF;
1870 close c_get_location;
1871 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':end');
1872 END get_location_Info;
1873
1874 PROCEDURE Get_Customer_Summary
1875 (p_api_version IN NUMBER := 1.0,
1876 p_init_msg_list IN VARCHAR2,
1877 p_commit IN VARCHAR2,
1878 p_validation_level IN NUMBER,
1879 x_return_status OUT NOCOPY VARCHAR2,
1880 x_msg_count OUT NOCOPY NUMBER,
1881 x_msg_data OUT NOCOPY VARCHAR2,
1882 p_party_id IN NUMBER,
1883 p_object_source IN VARCHAR2,
1884 x_customer_info_rec OUT NOCOPY customer_info_rec_type,
1885 x_object_info_rec OUT NOCOPY object_info_rec_type)
1886 IS
1887 l_api_version CONSTANT NUMBER := 1.0;
1888 l_api_name CONSTANT VARCHAR2(30) := 'Get_Customer_Summary';
1889 l_return_status VARCHAR2(1);
1890 l_msg_count NUMBER;
1891 l_msg_data VARCHAR2(32767);
1892
1893 amount Number;
1894 total Number;
1895 l_bkr_count number; -- Added for bug#7590635 by PNAVEENK on 6-2-2009
1896 -- Get Tax Code
1897 CURSOR c_party_info IS
1898 SELECT jgzz_fiscal_code
1899 FROM hz_parties
1900 WHERE party_id = p_party_id;
1901
1902 -- Collections score
1903 CURSOR c_collections_score IS
1904 SELECT a.score_value
1905 FROM iex_score_histories a
1906 WHERE a.creation_date =
1907 (SELECT MAX(creation_date)
1908 FROM iex_Score_histories
1909 WHERE score_object_code = 'PARTY'
1910 AND score_object_id = p_party_id)
1911 AND a.score_object_code = 'PARTY'
1912 AND a.score_object_id = p_party_id;
1913
1914 -- Collectable/Delinquent Invoices
1915 -- start bug5874874 gnramasa 25-Apr-2007
1916 /* CURSOR c_summ_info IS
1917 SELECT COUNT(DECODE(ps.class, 'INV', ps.payment_schedule_id,
1918 'DM', ps.payment_schedule_id,
1919 'CB', ps.payment_schedule_id, NULL)) cnt_inv,
1920 COUNT(DECODE(ps.class, 'INV', DECODE(del.status, 'DELINQUENT', del.delinquency_id,
1921 'PREDELINQUENT', del.delinquency_id,NULL), NULL)) cnt_del,
1922 SUM(NVL(ps.acctd_amount_due_remaining,0)) net_balance,
1923 SUM(DECODE(del.status, 'DELINQUENT', NVL(acctd_amount_due_remaining,0),
1924 'PREDELINQUENT', NVL(acctd_amount_due_remaining,0),0)) overdue_amt,
1925 ROUND(
1926 ( (SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y',
1927 DECODE(PS.CLASS,
1928 'INV', 1,
1929 'DM', 1,
1930 'CB', 1,
1931 'DEP', 1,
1932 'BR', 1, *//* 22-JUL-2000 J Rautiainen BR Implementation */
1933 /* 0), 0)
1934 * PS.ACCTD_AMOUNT_DUE_REMAINING
1935 ) * MAX(SP.CER_DSO_DAYS)
1936 )
1937 / DECODE(
1938 SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y',DECODE(PS.CLASS,
1939 'INV', 1,
1940 'DM', 1,
1941 'DEP', 1,
1942 0), 0)
1943 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
1944 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
1945 0)) ,
1946 0, 1,
1947 SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y', DECODE(PS.CLASS,
1948 'INV', 1,
1949 'DM', 1,
1950 'DEP', 1,
1951 0), 0)
1952 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
1953 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
1954 0) )
1955 )
1956 ), 0) dso
1957 FROM ar_payment_schedules ps,
1958 hz_cust_accounts ca,
1959 iex_delinquencies del,
1960 ar_system_parameters sp
1961 WHERE ca.party_id = p_party_id
1962 AND ps.customer_id = ca.cust_account_id
1963 AND ps.status = 'OP'
1964 AND del.payment_schedule_id(+) = ps.payment_schedule_id
1965 and ps.org_id=sp.org_id; --added for MOAC change
1966 */
1967 CURSOR c_summ_info1 IS
1968 SELECT COUNT(DECODE(ps.class, 'INV', ps.payment_schedule_id, 'DM', ps.payment_schedule_id, 'CB', ps.payment_schedule_id, NULL)) cnt_inv,
1969 COUNT(DECODE(ps.class, 'INV', DECODE(del.status, 'DELINQUENT', del.delinquency_id, 'PREDELINQUENT', del.delinquency_id, NULL), NULL)) cnt_del
1970 FROM ar_payment_schedules ps,
1971 hz_cust_accounts ca,
1972 iex_delinquencies del
1973 WHERE ca.party_id = p_party_id
1974 AND ps.customer_id = ca.cust_account_id
1975 AND ps.status = 'OP'
1976 AND del.payment_schedule_id(+) = ps.payment_schedule_id;
1977
1978
1979 -- Balance, overdue amount and dso
1980 CURSOR c_summ_info2 IS
1981 SELECT SUM(NVL(ps.acctd_amount_due_remaining,0)) net_balance,
1982 SUM(DECODE(del.status, 'DELINQUENT', NVL(acctd_amount_due_remaining,0),
1983 'PREDELINQUENT', NVL(acctd_amount_due_remaining,0),0)) overdue_amt,
1984 ROUND(
1985 ( (SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y',
1986 DECODE(PS.CLASS,
1987 'INV', 1,
1988 'DM', 1,
1989 'CB', 1,
1990 'DEP', 1,
1991 'BR', 1, /* 22-JUL-2000 J Rautiainen BR Implementation */
1992 0), 0)
1993 * PS.ACCTD_AMOUNT_DUE_REMAINING
1994 ) * MAX(SP.CER_DSO_DAYS)
1995 )
1996 / DECODE(
1997 SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y',DECODE(PS.CLASS,
1998 'INV', 1,
1999 'DM', 1,
2000 'DEP', 1,
2001 0), 0)
2002 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
2003 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
2004 0)) ,
2005 0, 1,
2006 SUM( DECODE(NVL(ps.receipt_confirmed_flag,'Y'), 'Y', DECODE(PS.CLASS,
2007 'INV', 1,
2008 'DM', 1,
2009 'DEP', 1,
2010 0), 0)
2011 * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
2012 -1, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
2013 0) )
2014 )
2015 ), 0) dso
2016 FROM ar_payment_schedules ps,
2017 hz_cust_accounts ca,
2018 iex_delinquencies del,
2019 ar_system_parameters sp
2020 WHERE ca.party_id = p_party_id
2021 AND ps.customer_id = ca.cust_account_id
2022 AND ps.status = 'OP'
2023 AND del.payment_schedule_id(+) = ps.payment_schedule_id
2024 AND ps.org_id = sp.org_id;
2025 -- End bug5874874 gnramasa 25-Apr-2007
2026
2027 --Start bug 8359894 gnramasa 18th june 09
2028 -- Balance, overdue amount
2029 CURSOR c_summ_info3 IS
2030 SELECT SUM(NVL(ps.acctd_amount_due_remaining,0)) net_balance,
2031 SUM(DECODE(del.status, 'DELINQUENT', NVL(acctd_amount_due_remaining,0),
2032 'PREDELINQUENT', NVL(acctd_amount_due_remaining,0),0)) overdue_amt
2033 FROM ar_payment_schedules ps,
2034 hz_cust_accounts ca,
2035 iex_delinquencies del,
2036 ar_system_parameters sp
2037 WHERE ca.party_id = p_party_id
2038 AND ps.customer_id = ca.cust_account_id
2039 AND ps.status = 'OP'
2040 AND del.payment_schedule_id(+) = ps.payment_schedule_id
2041 AND ps.org_id = sp.org_id;
2042 --End bug 8359894 gnramasa 18th june 09
2043
2044 -- Delinquencies in Past Year
2045 CURSOR c_delinquencies IS
2046 SELECT Count(1)
2047 FROM iex_delinquencies del
2048 WHERE del.party_cust_id = p_party_id
2049 AND del.creation_date between sysdate - 365 and sysdate ;
2050
2051 -- added by jypark for status in header
2052 CURSOR c_filter IS
2053 SELECT object_id, select_column, entity_name
2054 FROM iex_object_filters
2055 WHERE object_filter_type = 'IEXCUST'
2056 AND active_flag = 'Y';
2057
2058 TYPE refCur IS REF CURSOR;
2059 c_universe refCur;
2060 l_sql_stmt VARCHAR2(1000);
2061 l_sql_stmt_lsd VARCHAR2(1000);
2062 l_status_rule_id NUMBER;
2063 l_count NUMBER;
2064 l_delinquency_status VARCHAR(80);
2065
2066 CURSOR c_rule IS
2067 SELECT rl.delinquency_status, rl.priority,
2068 iex_utilities.get_lookup_meaning('IEX_DELINQUENCY_STATUS', rl.delinquency_status) meaning
2069 FROM iex_cu_sts_rl_lines rl, iex_cust_status_rules r
2070 WHERE rl.status_rule_id = l_status_rule_id
2071 AND r.status_rule_id = rl.status_rule_id
2072 AND trunc(sysdate) BETWEEN trunc(nvl(r.start_date,sysdate)) AND trunc(nvl(r.end_date,sysdate))
2073 AND NVL(rl.enabled_flag, 'N') = 'Y'
2074 ORDER BY rl.priority;
2075 c_del refCur;
2076
2077 -- Customer Since added by jypark 09/26/2002
2078 CURSOR c_customer_since IS
2079 SELECT MIN(account_established_date)
2080 FROM hz_cust_accounts
2081 WHERE account_established_date IS NOT NULL
2082 AND party_id = p_party_id;
2083
2084 l_sql_select VARCHAR2(1000);
2085 l_sql_where VARCHAR2(1000);
2086 l_sql_cond VARCHAR2(1000);
2087 l_last_pmt_info_rec last_pmt_info_rec_type;
2088
2089 l_cnt_cur_codes number; --added for MOAC
2090 l_calc_cust_stats VARCHAR2(1);
2091
2092 l_amount_in_dispute ra_cm_requests_all.total_amount%type; --Added for bug 7612000 gnramasa 4th Dec 08
2093
2094 BEGIN
2095 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':begin');
2096
2097 --start moac change
2098 --to check whether all the ou's has the same functional currency or not
2099 l_cnt_cur_codes:= iex_currency_pvt.get_currency_count;
2100 --end moac change
2101
2102
2103 amount := 0 ;
2104 total := 0 ;
2105 l_sql_select := 'SELECT count(1) FROM ';
2106 l_sql_where := ' WHERE ';
2107 l_sql_cond := ' = :party_id';
2108
2109 x_customer_info_rec.party_id := p_party_id;
2110
2111 OPEN c_party_info;
2112 FETCH c_party_info INTO x_customer_info_rec.identification_id;
2113 CLOSE c_party_info;
2114 -- Start bug5874874 gnramasa 25-Apr-2007
2115 /* OPEN c_summ_info;
2116 FETCH c_summ_info INTO x_customer_info_rec.number_of_invoices,
2117 x_customer_info_rec.invoices_overdue,
2118 x_object_info_rec.current_balance,
2119 x_object_info_rec.amount_overdue,
2120 x_object_info_rec.dso;
2121 CLOSE c_summ_info;
2122 */
2123 l_calc_cust_stats := nvl(fnd_profile.value('IEX_CALC_CUST_STATS'), 'A');
2124
2125 if (l_calc_cust_stats = 'A') then
2126 OPEN c_summ_info1;
2127 FETCH c_summ_info1 INTO x_customer_info_rec.number_of_invoices,
2128 x_customer_info_rec.invoices_overdue;
2129 CLOSE c_summ_info1;
2130
2131 OPEN c_delinquencies;
2132 FETCH c_delinquencies INTO x_customer_info_rec.number_of_delinquencies;
2133 CLOSE c_delinquencies;
2134 end if;
2135
2136 --Start bug 8359894 gnramasa 18th june 09
2137 if (NVL(FND_PROFILE.VALUE('IEX_SHOW_DSO_IN_HEADER'), 'Y') = 'Y') then
2138 OPEN c_summ_info2;
2139 FETCH c_summ_info2 INTO x_object_info_rec.current_balance,
2140 x_object_info_rec.amount_overdue,
2141 x_object_info_rec.dso;
2142 CLOSE c_summ_info2;
2143 else
2144 OPEN c_summ_info3;
2145 FETCH c_summ_info3 INTO x_object_info_rec.current_balance,
2146 x_object_info_rec.amount_overdue;
2147 CLOSE c_summ_info3;
2148 end if;
2149 --End bug 8359894 gnramasa 18th june 09
2150
2151 --Start bug 7612000 gnramasa 4th Dec 08
2152 -- If value of "IEX: Exclude dispute amount from remaining amount " is Yes
2153 -- then calculate the Amount thats in dispute and substract it from the amount overdue.
2154 if (NVL(FND_PROFILE.VALUE('IEX_EXCLUDE_DISPUTE_AMT_FROM_REMAINING_AMT'), 'N') = 'Y') then
2155 select nvl(sum(cm.total_amount * -1),0)
2156 into l_amount_in_dispute
2157 from ra_cm_requests cm
2158 where cm.customer_trx_id in (select distinct customer_trx_id
2159 from ar_payment_schedules ps,
2160 hz_cust_accounts ca
2161 where ca.party_id = p_party_id
2162 and ps.customer_id = ca.cust_account_id
2163 and ps.status = 'OP')
2164 and cm.status='PENDING_APPROVAL';
2165
2166 x_object_info_rec.amount_overdue := x_object_info_rec.amount_overdue - l_amount_in_dispute;
2167 end if;
2168 --End bug 7612000 gnramasa 4th Dec 08
2169
2170 --start moac change
2171 --if the functional currency codes of the ou's are different
2172 --make the Amount overdue and Net balance fields as null.
2173 if l_cnt_cur_codes <>1 then
2174 x_object_info_rec.current_balance:=null;
2175 x_object_info_rec.amount_overdue:=null;
2176 end if;
2177 --end moac change
2178
2179
2180 /* OPEN c_delinquencies;
2181 FETCH c_delinquencies INTO x_customer_info_rec.number_of_delinquencies;
2182 CLOSE c_delinquencies;
2183 */
2184 -- End bug5874874 gnramasa 25-Apr-2007
2185
2186 OPEN c_collections_score;
2187 FETCH c_collections_score INTO x_customer_info_rec.collections_score;
2188 CLOSE c_collections_score;
2189
2190 OPEN c_customer_since;
2191 FETCH c_customer_since INTO x_customer_info_rec.customer_since;
2192 CLOSE c_customer_since;
2193
2194 -- added by jypark for status in header
2195
2196 FOR r_filter in c_filter LOOP
2197 -- build SQL for universe
2198 -- for bug5874874 gnramasa 25-Apr-2007
2199 l_sql_stmt := 'SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM ' || r_filter.entity_name || l_sql_where || r_filter.select_column || l_sql_cond || ')';
2200 -- l_sql_stmt := l_sql_select || r_filter.entity_name || l_sql_where || r_filter.select_column || l_sql_cond;
2201
2202 BEGIN
2203 OPEN c_universe FOR l_sql_stmt USING p_party_id;
2204 FETCH c_universe into l_count;
2205
2206 IF c_universe%FOUND AND l_count > 0 THEN
2207
2208 l_status_rule_id := r_filter.object_id;
2209 CLOSE c_universe;
2210
2211
2212 -- begin added by jypark 01/05/2004 to fix bug #3308753
2213 -- begin bug 6723556 gnramasa 10th Jan 2008
2214
2215 IF l_status_rule_id IS NOT NULL THEN
2216 FOR r_rule IN c_rule LOOP
2217 l_delinquency_status := r_rule.delinquency_status;
2218 iex_debug_pub.LogMessage('3. l_delinquency_status :' || l_delinquency_status);
2219 IF l_delinquency_status = 'BANKRUPTCY' THEN
2220 l_sql_stmt := 'SELECT count(1)' ||
2221 ' FROM iex_bankruptcies' ||
2222 ' WHERE party_id = :party_id' ||
2223 ' AND close_date IS NULL ' ||
2224 ' AND NVL(DISPOSITION_CODE, '' '') NOT IN (''DISMISSED'',''WITHDRAWN'' )';
2225 ELSIF l_delinquency_status = 'DELINQUENT' THEN
2226 l_sql_stmt := 'SELECT count(1)' ||
2227 ' FROM iex_delinquencies' ||
2228 ' WHERE party_cust_id = :party_id' ||
2229 -- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
2230 ' AND status = ''DELINQUENT''';
2231
2232 --Start bug 9861733 gnramasa 19th July 2010
2233 /*
2234 ELSIF l_delinquency_status = 'LITIGATION' THEN
2235 l_sql_stmt_lsd := 'select count(1) from (' ||
2236 ' SELECT litigation_id' ||
2237 ' FROM iex_litigations ltg, iex_delinquencies del' ||
2238 ' WHERE del.party_cust_id = :party_id' ||
2239 ' AND ltg.delinquency_id = del.delinquency_id' ||
2240 ' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
2241 ' AND ltg.disposition_code IS NULL ' ||
2242 ' UNION ' ||
2243 ' SELECT litigation_id ' ||
2244 ' FROM iex_litigations ' ||
2245 ' WHERE party_id= :party_id ' ||
2246 ' AND contract_number IS NOT NULL ' ||
2247 ' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
2248 ' AND disposition_code IS NULL )';
2249 ELSIF l_delinquency_status = 'REPOSSESSION' THEN
2250 l_sql_stmt_lsd := ' select count(1) from (' ||
2251 ' SELECT repossession_id' ||
2252 ' FROM iex_repossessions rps, iex_delinquencies del' ||
2253 ' WHERE del.party_cust_id = :party_id' ||
2254 ' AND rps.delinquency_id = del.delinquency_id' ||
2255 ' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
2256 ' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ||
2257 --' AND rps.disposition_code IS NULL ' ||
2258 ' UNION ' ||
2259 ' SELECT repossession_id ' ||
2260 ' FROM iex_repossessions ' ||
2261 ' WHERE party_id= :party_id ' ||
2262 ' AND contract_number IS NOT NULL ' ||
2263 ' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
2264 ' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W'')) ' ;
2265 -- Bug 766183 ' AND disposition_code IS NULL )';
2266 ELSIF l_delinquency_status = 'WRITEOFF' THEN
2267 l_sql_stmt_lsd := ' select count(1) from (' ||
2268 ' SELECT writeoff_id' ||
2269 ' FROM iex_writeoffs wrf, iex_delinquencies del' ||
2270 ' WHERE del.party_cust_id = :party_id' ||
2271 ' AND wrf.delinquency_id = del.delinquency_id' ||
2272 ' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
2273 ' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ||
2274 -- ' AND wrf.disposition_code IS NULL ' ||
2275 ' UNION ' ||
2276 ' SELECT writeoff_id ' ||
2277 ' FROM iex_writeoffs ' ||
2278 ' WHERE party_id= :party_id ' ||
2279 ' AND contract_number IS NOT NULL ' ||
2280 ' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
2281 ' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W''))' ;
2282 -- ' AND disposition_code IS NULL )';
2283 */
2284 ELSIF l_delinquency_status = 'LITIGATION' THEN
2285 l_sql_stmt := ' SELECT count(ltg.litigation_id) ' ||
2286 ' FROM iex_litigations ltg ' ||
2287 ' WHERE ltg.party_id = :party_id' ||
2288 ' AND ltg.disposition_code IS NULL ' ;
2289 ELSIF l_delinquency_status = 'REPOSSESSION' THEN
2290 l_sql_stmt := ' SELECT count(rps.repossession_id) ' ||
2291 ' FROM iex_repossessions rps ' ||
2292 ' WHERE rps.party_id = :party_id' ||
2293 ' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ;
2294 ELSIF l_delinquency_status = 'WRITEOFF' THEN
2295 l_sql_stmt := ' SELECT count(wrf.writeoff_id) ' ||
2296 ' FROM iex_writeoffs wrf ' ||
2297 ' WHERE wrf.party_id = :party_id' ||
2298 ' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ;
2299
2300 ELSIF l_delinquency_status = 'PREDELINQUENT' THEN
2301 l_sql_stmt := 'SELECT count(1)' ||
2302 ' FROM iex_delinquencies' ||
2303 ' WHERE party_cust_id = :party_id' ||
2304 -- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
2305 ' AND status = ''PREDELINQUENT''';
2306 END IF;
2307
2308 iex_debug_pub.LogMessage('3. p_party_id :' || p_party_id);
2309 /*
2310 IF l_delinquency_status in ('LITIGATION','REPOSSESSION','WRITEOFF') THEN
2311 iex_debug_pub.LogMessage('3. l_sql_stmt_lsd :' || l_sql_stmt_lsd);
2312 OPEN c_del FOR l_sql_stmt_lsd USING p_party_id,p_party_id;
2313 ELSE
2314 */
2315 iex_debug_pub.LogMessage('3. l_sql_stmt :' || l_sql_stmt);
2316 OPEN c_del FOR l_sql_stmt USING p_party_id;
2317 --END IF;
2318 --End bug 9861733 gnramasa 19th July 2010
2319 FETCH c_del INTO l_count;
2320
2321 IF l_count > 0 THEN
2322 x_customer_info_rec.status := r_rule.meaning;
2323 CLOSE c_del;
2324 EXIT;
2325 END IF;
2326 CLOSE c_del;
2327 END LOOP;
2328 END IF;
2329
2330 IF x_customer_info_rec.status IS NOT NULL THEN
2331 EXIT;
2332 END IF;
2333 -- end added by jypark 01/05/2004 to fix bug #3308753
2334
2335 END IF;
2336 EXCEPTION
2337 WHEN OTHERS THEN
2338 null;
2339 END ;
2340 END LOOP;
2341
2342 IF l_status_rule_id IS NOT NULL THEN
2343 FOR r_rule IN c_rule LOOP
2344 l_delinquency_status := r_rule.delinquency_status;
2345 iex_debug_pub.LogMessage('4. l_delinquency_status :' || l_delinquency_status);
2346 IF l_delinquency_status = 'BANKRUPTCY' THEN
2347 l_sql_stmt := 'SELECT count(1)' ||
2348 ' FROM iex_bankruptcies' ||
2349 ' WHERE party_id = :party_id' ||
2350 ' AND close_date IS NULL ' ||
2351 ' AND NVL(DISPOSITION_CODE , '' '') NOT IN (''DISMISSED'',''WITHDRAWN'' )';
2352 ELSIF l_delinquency_status = 'DELINQUENT' THEN
2353 l_sql_stmt := 'SELECT count(1)' ||
2354 ' FROM iex_delinquencies' ||
2355 ' WHERE party_cust_id = :party_id' ||
2356 -- fix bug #4157131 ' AND status not in (''CURRENT'', ''CLOSE'')';
2357 ' AND status = ''DELINQUENT''';
2358
2359 --Start bug 9861733 gnramasa 19th July 2010
2360 /*
2361 ELSIF l_delinquency_status = 'LITIGATION' THEN
2362 l_sql_stmt_lsd := 'select count(1) from (' ||
2363 ' SELECT litigation_id' ||
2364 ' FROM iex_litigations ltg, iex_delinquencies del' ||
2365 ' WHERE del.party_cust_id = :party_id' ||
2366 ' AND ltg.delinquency_id = del.delinquency_id' ||
2367 ' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
2368 ' AND ltg.disposition_code IS NULL ' ||
2369 ' UNION ' ||
2370 ' SELECT litigation_id ' ||
2371 ' FROM iex_litigations ' ||
2372 ' WHERE party_id= :party_id ' ||
2373 ' AND contract_number IS NOT NULL ' ||
2374 ' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
2375 ' AND disposition_code IS NULL )';
2376 ELSIF l_delinquency_status = 'REPOSSESSION' THEN
2377 l_sql_stmt_lsd := ' select count(1) from (' ||
2378 ' SELECT repossession_id' ||
2379 ' FROM iex_repossessions rps, iex_delinquencies del' ||
2380 ' WHERE del.party_cust_id = :party_id' ||
2381 ' AND rps.delinquency_id = del.delinquency_id' ||
2382 ' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
2383 ' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ||
2384 --' AND rps.disposition_code IS NULL ' ||
2385 ' UNION ' ||
2386 ' SELECT repossession_id ' ||
2387 ' FROM iex_repossessions ' ||
2388 ' WHERE party_id= :party_id ' ||
2389 ' AND contract_number IS NOT NULL ' ||
2390 ' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
2391 ' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W'')) ' ;
2392 -- Bug 766183 ' AND disposition_code IS NULL )';
2393 ELSIF l_delinquency_status = 'WRITEOFF' THEN
2394 l_sql_stmt_lsd := ' select count(1) from (' ||
2395 ' SELECT writeoff_id' ||
2396 ' FROM iex_writeoffs wrf, iex_delinquencies del' ||
2397 ' WHERE del.party_cust_id = :party_id' ||
2398 ' AND wrf.delinquency_id = del.delinquency_id' ||
2399 ' AND del.status not in (''CURRENT'', ''CLOSE'')' ||
2400 ' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ||
2401 -- ' AND wrf.disposition_code IS NULL ' ||
2402 ' UNION ' ||
2403 ' SELECT writeoff_id ' ||
2404 ' FROM iex_writeoffs ' ||
2405 ' WHERE party_id= :party_id ' ||
2406 ' AND contract_number IS NOT NULL ' ||
2407 ' AND ''DELINQUENT'' = IEX_UTILITIES.CheckContractStatus(contract_number) ' ||
2408 ' AND (disposition_code IS NULL or disposition_code = ''A'' or disposition_code = ''W''))' ;
2409 -- ' AND disposition_code IS NULL )';
2410 */
2411 ELSIF l_delinquency_status = 'LITIGATION' THEN
2412 l_sql_stmt := ' SELECT count(ltg.litigation_id) ' ||
2413 ' FROM iex_litigations ltg ' ||
2414 ' WHERE ltg.party_id = :party_id' ||
2415 ' AND ltg.disposition_code IS NULL ' ;
2416 ELSIF l_delinquency_status = 'REPOSSESSION' THEN
2417 l_sql_stmt := ' SELECT count(rps.repossession_id) ' ||
2418 ' FROM iex_repossessions rps ' ||
2419 ' WHERE rps.party_id = :party_id' ||
2420 ' AND (rps.disposition_code IS NULL or rps.disposition_code = ''A'' or rps.disposition_code = ''W'') ' ;
2421 ELSIF l_delinquency_status = 'WRITEOFF' THEN
2422 l_sql_stmt := ' SELECT count(wrf.writeoff_id) ' ||
2423 ' FROM iex_writeoffs wrf ' ||
2424 ' WHERE wrf.party_id = :party_id' ||
2425 ' AND (wrf.disposition_code IS NULL or wrf.disposition_code = ''A'' or wrf.disposition_code = ''W'') ' ;
2426
2427 -- fix bug #4157131
2428 ELSIF l_delinquency_status = 'PREDELINQUENT' THEN
2429 l_sql_stmt := 'SELECT count(1)' ||
2430 ' FROM iex_delinquencies' ||
2431 ' WHERE party_cust_id = :party_id' ||
2432 ' AND status = ''PREDELINQUENT''';
2433 END IF;
2434
2435 iex_debug_pub.LogMessage('4. p_party_id :' || p_party_id);
2436 /*
2437 IF l_delinquency_status in ('LITIGATION','REPOSSESSION','WRITEOFF') THEN
2438 iex_debug_pub.LogMessage('4. l_sql_stmt_lsd :' || l_sql_stmt_lsd);
2439 OPEN c_del FOR l_sql_stmt_lsd USING p_party_id,p_party_id;
2440 ELSE
2441 */
2442 iex_debug_pub.LogMessage('4. l_sql_stmt :' || l_sql_stmt);
2443 OPEN c_del FOR l_sql_stmt USING p_party_id;
2444 --END IF;
2445 --End bug 9861733 gnramasa 19th July 2010
2446 -- End bug 6723556 gnramasa 10th Jan 2008
2447 FETCH c_del INTO l_count;
2448
2449 IF l_count > 0 THEN
2450 x_customer_info_rec.status := r_rule.meaning;
2451 CLOSE c_del;
2452 EXIT;
2453 END IF;
2454 CLOSE c_del;
2455 END LOOP;
2456 END IF;
2457 -- Start for bug#7590635 by PNAVEENK on 6-2-2009
2458 IF x_customer_info_rec.status IS NULL THEN
2459
2460 SELECT count(1) into l_bkr_count
2461 FROM iex_bankruptcies
2462 WHERE party_id = x_customer_info_rec.party_id
2463 AND close_date IS NULL
2464 AND NVL(DISPOSITION_CODE, ' ') NOT IN ('DISMISSED','WITHDRAWN' );
2465 IF l_bkr_count > 0 then
2466 x_customer_info_rec.status := iex_utilities.get_lookup_meaning('IEX_DELINQUENCY_STATUS', 'BANKRUPTCY');
2467 ELSE
2468 x_customer_info_rec.status := iex_utilities.get_lookup_meaning('IEX_CUSTOMER_STATUS_TYPE', 'CURRENT');
2469 END IF;
2470
2471 END IF;
2472 -- End for bug#7590635
2473 get_last_payment_info(
2474 p_api_version => p_api_version,
2475 p_init_msg_list => p_init_msg_list,
2476 p_commit => p_commit,
2477 p_validation_level => p_validation_level,
2478 x_return_status => x_return_status,
2479 x_msg_count => x_msg_count,
2480 x_msg_data => x_msg_data,
2481 p_object_type => 'CUSTOMER',
2482 p_object_id => p_party_id,
2483 x_last_pmt_info_rec => l_last_pmt_info_rec);
2484
2485 x_object_info_rec.last_payment_amount := l_last_pmt_info_rec.amount;
2486 x_object_info_rec.last_payment_curr := l_last_pmt_info_rec.currency_code;
2487 x_object_info_rec.last_payment_due_date := l_last_pmt_info_rec.due_date;
2488 x_object_info_rec.last_payment_date := l_last_pmt_info_rec.receipt_date;
2489 x_object_info_rec.last_payment_status := l_last_pmt_info_rec.status;
2490 x_object_info_rec.last_payment_receipt_number := l_last_pmt_info_rec.receipt_number;
2491 x_object_info_rec.last_payment_id := l_last_pmt_info_rec.cash_receipt_id;
2492
2493 --start moac change
2494 --if the functional currency codes of the ou's are different
2495 --make the Amount overdue and Net balance fields as null.
2496 if l_cnt_cur_codes <> 1 then
2497 x_object_info_rec.current_balance_curr:=null;
2498 else
2499 --end moac change
2500 IEX_CURRENCY_PVT.GET_FUNCT_CURR(
2501 P_API_VERSION =>1.0,
2502 p_init_msg_list => 'T',
2503 p_commit => 'F',
2504 p_validation_level => 100,
2505 X_Functional_currency => x_object_info_rec.current_balance_curr,
2506 X_return_status => x_return_status,
2507 X_MSG_COUNT => x_msg_count,
2508 X_MSG_DATA => x_msg_data );
2509 end if; --end if for check on count of currency codes(moac change).
2510
2511 x_object_info_rec.amount_overdue_curr := x_object_info_rec.current_balance_curr;
2512
2513
2514 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':end');
2515 END Get_Customer_Summary;
2516
2517 PROCEDURE Get_header_info
2518 (p_api_version IN NUMBER := 1.0,
2519 p_init_msg_list IN VARCHAR2,
2520 p_commit IN VARCHAR2,
2521 p_validation_level IN NUMBER,
2522 x_return_status OUT NOCOPY VARCHAR2,
2523 x_msg_count OUT NOCOPY NUMBER,
2524 x_msg_data OUT NOCOPY VARCHAR2,
2525 p_party_type IN VARCHAR2,
2526 p_rel_party_id IN NUMBER,
2527 p_org_party_id IN NUMBER,
2528 p_person_party_id IN NUMBER,
2529 p_object_type IN VARCHAR2,
2530 p_object_id IN NUMBER,
2531 p_object_source IN VARCHAR2,
2532 x_customer_info_rec OUT NOCOPY customer_info_rec_type,
2533 x_object_info_rec OUT NOCOPY object_info_rec_type,
2534 --x_email_info_rec OUT NOCOPY email_info_rec_type,
2535 --x_phone_info_rec OUT NOCOPY phone_info_rec_type,
2536 x_contact_point_info_rec OUT NOCOPY contact_point_info_rec_type,
2537 x_location_info_rec OUT NOCOPY location_info_rec_type)
2538 IS
2539 l_api_name CONSTANT VARCHAR2(30) := 'GET_HEADER_INFO';
2540 l_api_version CONSTANT NUMBER := 1.0;
2541 l_return_status VARCHAR2(1);
2542 l_msg_count NUMBER;
2543 l_msg_data VARCHAR2(32767);
2544
2545 l_party_id NUMBER;
2546 BEGIN
2547 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':begin');
2548
2549 SAVEPOINT Get_Header_Info_PVT;
2550
2551 -- Standard call to check for call compatibility.
2552 IF NOT FND_API.Compatible_API_Call (l_api_version,
2553 p_api_version,
2554 l_api_name,
2555 G_PKG_NAME) THEN
2556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2557 END IF;
2558
2559 -- Check p_init_msg_list
2560 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2561 FND_MSG_PUB.initialize;
2562 END IF;
2563
2564 x_return_status := FND_API.G_RET_STS_SUCCESS;
2565
2566
2567 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':p_party_type=' || p_party_type || ':p_org_party_id' || p_org_party_id
2568 || ':p_person_paryt_id=' || p_person_party_id || ':p_rel_party_id=' || p_rel_party_id);
2569 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':p_object_type=' || p_object_type || ':p_object_id=' || p_object_id || ':p_object_source=' || p_object_source);
2570
2571 IF p_party_type in ('ORGANIZATION', 'RELATIONSHIP') THEN
2572 l_party_id := p_org_party_id;
2573 ELSE
2574 l_party_id := p_person_party_id;
2575 END IF;
2576
2577 IF p_object_source = 'AR' AND
2578 p_object_type = 'CUSTOMER' THEN
2579 Get_Customer_Summary(
2580 p_api_version => p_api_version,
2581 p_init_msg_list => p_init_msg_list,
2582 p_commit => p_commit,
2583 p_validation_level => p_validation_level,
2584 x_return_status => x_return_status,
2585 x_msg_count => x_msg_count,
2586 x_msg_data => x_msg_data,
2587 p_party_id => l_party_id,
2588 p_object_source => p_object_source,
2589 x_customer_info_rec => x_customer_info_rec,
2590 x_object_info_rec => x_object_info_rec);
2591 ELSE
2592 Get_Customer_Info(
2593 p_api_version => p_api_version,
2594 p_init_msg_list => p_init_msg_list,
2595 p_commit => p_commit,
2596 p_validation_level => p_validation_level,
2597 x_return_status => x_return_status,
2598 x_msg_count => x_msg_count,
2599 x_msg_data => x_msg_data,
2600 p_party_id => l_party_id,
2601 p_object_source => p_object_source,
2602 x_customer_info_rec => x_customer_info_rec);
2603
2604 Get_Object_Info(
2605 p_api_version => p_api_version,
2606 p_init_msg_list => p_init_msg_list,
2607 p_commit => p_commit,
2608 p_validation_level => p_validation_level,
2609 x_return_status => x_return_status,
2610 x_msg_count => x_msg_count,
2611 x_msg_data => x_msg_data,
2612 p_object_type => p_object_type,
2613 p_object_id => p_object_id,
2614 p_object_source => p_object_source,
2615 x_object_info_rec => x_object_info_rec);
2616
2617 END IF;
2618
2619 IF p_party_type = 'ORGANIZATION' THEN
2620 l_party_id := p_org_party_id;
2621 ELsIF p_party_type = 'RELATIONSHIP' THEN
2622 l_party_id := p_rel_party_id;
2623 ELSIF p_party_type = 'PERSON' THEN
2624 l_party_id := p_person_party_id;
2625 END IF;
2626
2627 get_contact_point_info(
2628 p_api_version => p_api_version,
2629 p_init_msg_list => p_init_msg_list,
2630 p_commit => p_commit,
2631 p_validation_level => p_validation_level,
2632 x_return_status => x_return_status,
2633 x_msg_count => x_msg_count,
2634 x_msg_data => x_msg_data,
2635 p_party_id => l_party_id,
2636 x_contact_point_info_rec => x_contact_point_info_rec);
2637
2638 get_location_Info(
2639 p_api_version => p_api_version,
2640 p_init_msg_list => p_init_msg_list,
2641 p_commit => p_commit,
2642 p_validation_level => p_validation_level,
2643 p_party_id => l_party_id,
2644 x_return_status => x_return_status,
2645 x_msg_count => x_msg_count,
2646 x_msg_data => x_msg_data,
2647 x_location_info_rec => x_location_info_rec);
2648
2649 -- Standard check of p_commit
2650 IF FND_API.To_Boolean(p_commit) THEN
2651 COMMIT WORK;
2652 END IF;
2653
2654 -- Standard call to get message count and if count is 1, get message info
2655 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2656
2657
2658 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':end');
2659 EXCEPTION
2660 WHEN FND_API.G_EXC_ERROR THEN
2661 ROLLBACK TO Get_Header_Info_PVT;
2662 x_return_status := FND_API.G_RET_STS_ERROR;
2663 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2664
2665 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2666 ROLLBACK TO Get_Header_Info_PVT;
2667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2668 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2669
2670 WHEN OTHERS THEN
2671 ROLLBACK TO Get_Header_Info_PVT;
2672 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2673 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2674 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2675 END IF;
2676 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2677 END;
2678
2679
2680 PROCEDURE Create_Default_Contact
2681 (p_api_version IN NUMBER := 1.0,
2682 p_init_msg_list IN VARCHAR2,
2683 p_commit IN VARCHAR2,
2684 p_validation_level IN NUMBER,
2685 x_return_status OUT NOCOPY VARCHAR2,
2686 x_msg_count OUT NOCOPY NUMBER,
2687 x_msg_data OUT NOCOPY VARCHAR2,
2688 p_org_party_id IN NUMBER,
2689 p_person_party_id IN NUMBER,
2690 p_phone_contact_point_id IN NUMBER,
2691 p_email_contact_point_id IN NUMBER,
2692 p_type IN VARCHAR2,
2693 p_location_id IN NUMBER,
2694 --p_cust_account_id IN NUMBER, -- ER 13329879
2695 x_relationship_id OUT NOCOPY NUMBER,
2696 x_party_id OUT NOCOPY NUMBER)
2697 IS
2698 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_DEFAULT_CONTACT';
2699 l_api_version CONSTANT NUMBER := 1.0;
2700
2701 l_party_rel_create_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
2702 l_org_contact_create_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
2703
2704 l_party_rel_update_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
2705 l_org_contact_update_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
2706
2707 l_msg_count NUMBER;
2708 l_msg_data VARCHAR2(2000);
2709 l_return_status VARCHAR2(1);
2710
2711 l_cont_object_version_number NUMBER;
2712 l_rel_object_version_number NUMBER;
2713 l_party_object_version_number NUMBER;
2714 l_object_version_number NUMBER;
2715
2716 l_party_relationship_id NUMBER;
2717 l_party_id NUMBER;
2718 l_party_number VARCHAR2(30);
2719
2720 l_msg_index_out number;
2721 l_org_contact_id NUMBER;
2722 l_application_id NUMBER; -- Bug 12359416
2723
2724 l_last_update_date date;
2725
2726 l_contact_point_create_rec HZ_CONTACT_POINT_V2PUB.contact_point_Rec_type;
2727 l_phone_create_rec HZ_CONTACT_POINT_V2PUB.phone_Rec_type;
2728 l_contact_point_id NUMBER;
2729 l_email_create_rec HZ_CONTACT_POINT_V2PUB.email_Rec_type;
2730
2731 -- Begin ER-13329879
2732 l_cust_account_role_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.cust_account_role_rec_type;
2733 l_cust_account_role_id number;
2734 l_cust_acct_site_id number;
2735 l_location_id number;
2736 t_cust_account_id number;
2737 -- End ER-13329879
2738
2739 CURSOR c_exist_rel IS
2740 SELECT *
2741 FROM hz_relationships
2742 WHERE (subject_id = l_party_id
2743 AND relationship_code = p_type
2744 AND status = 'A');
2745
2746 CURSOR c_org_contact(p_party_relationship_id NUMBER) IS
2747 SELECT org_contact_id, object_version_number, application_id -- Bug 12359416
2748 FROM hz_org_contacts
2749 WHERE party_relationship_id = p_party_relationship_id;
2750
2751 CURSOR c_party(p_party_id NUMBER) IS
2752 SELECT object_version_number
2753 FROM hz_parties
2754 WHERE party_id = p_party_id;
2755
2756 CURSOR c_contact_point(p_contact_point_id NUMBER) is
2757 SELECT *
2758 FROM hz_contact_points
2759 WHERE contact_point_id = p_contact_point_id;
2760
2761 --Begin Bug 6509624 31-Mar-2009 barathsr
2762 CURSOR c_phone_contact_point(p_contact_point_id NUMBER) is
2763 SELECT *
2764 FROM hz_contact_points
2765 WHERE owner_table_id=( SELECT owner_table_id
2766 FROM hz_contact_points
2767 WHERE contact_point_id = p_contact_point_id)
2768 AND contact_point_purpose=p_type
2769 AND contact_point_type ='PHONE'
2770 --Begin Bug 8322090 07-Apr-2009 barathsr
2771 UNION
2772 select *
2773 FROM hz_contact_points
2774 WHERE contact_point_id = p_contact_point_id;
2775 --End Bug 8322090 07-Apr-2009 barathsr
2776
2777 l_phone_rec c_phone_contact_point%ROWTYPE;
2778 --End Bug 6509624 31-Mar-2009 barathsr
2779 -- l_phone_rec c_contact_point%ROWTYPE;
2780 l_email_rec c_contact_point%ROWTYPE;
2781
2782 l_party_site_id NUMBER;
2783 l_party_site_number VARCHAR2(30);
2784 l_Party_Site_create_rec HZ_PARTY_SITE_V2PUB.Party_Site_Rec_type;
2785 l_call_api BOOLEAN;
2786
2787 CURSOR c_CheckPartySite(p_partyid number,p_location_id Number) IS
2788 SELECT party_site_id,party_site_number
2789 FROM HZ_PARTY_SITES
2790 where party_id = p_partyid
2791 AND location_id = p_location_id;
2792
2793 BEGIN
2794 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':begin');
2795
2796 SAVEPOINT Create_Default_Contact_PVT;
2797
2798 -- Standard call to check for call compatibility.
2799 IF NOT FND_API.Compatible_API_Call (l_api_version,
2800 p_api_version,
2801 l_api_name,
2802 G_PKG_NAME) THEN
2803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2804 END IF;
2805
2806 -- Check p_init_msg_list
2807 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2808 FND_MSG_PUB.initialize;
2809 END IF;
2810
2811 x_return_status := FND_API.G_RET_STS_SUCCESS;
2812
2813 l_party_rel_create_rec := AST_API_RECORDS_V2PKG.INIT_HZ_PARTY_REL_REC_TYPE_V2;
2814 l_org_contact_create_rec := AST_API_RECORDS_V2PKG.INIT_HZ_ORG_CONTACT_REC_V2;
2815
2816 l_party_rel_update_rec := AST_API_RECORDS_V2PKG.INIT_HZ_PARTY_REL_REC_TYPE_V2;
2817 l_org_contact_update_rec := AST_API_RECORDS_V2PKG.INIT_HZ_ORG_CONTACT_REC_V2;
2818
2819
2820 l_cont_object_version_number := 1.0;
2821 l_rel_object_version_number := 1.0;
2822 l_party_object_version_number := 1.0;
2823 l_object_version_number := 1.0;
2824
2825 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':p_org_party_id=' || p_org_party_id || ':p_person_party_id=' || p_person_party_id
2826 || ':p_phone_contact_point_id=' || p_phone_contact_point_id || ':p_type=' || p_type);
2827
2828 l_party_id := p_org_party_id;
2829
2830 FOR r_exist_rel IN c_exist_rel LOOP
2831 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_relationship_id=' || r_exist_rel.relationship_id);
2832
2833 l_party_rel_update_rec.relationship_id := r_exist_rel.relationship_id;
2834 l_party_rel_update_rec.subject_id := r_exist_rel.subject_id;
2835 l_party_rel_update_rec.object_id := r_exist_rel.object_id;
2836 l_party_rel_update_rec.status := 'I';
2837 l_party_rel_update_rec.start_date := r_exist_rel.start_date;
2838 l_party_rel_update_rec.end_date := sysdate;
2839 l_party_rel_update_rec.relationship_type := r_exist_rel.relationship_type;
2840 l_party_rel_update_rec.relationship_code := r_exist_rel.relationship_code;
2841 l_party_rel_update_rec.subject_table_name := r_exist_rel.subject_table_name;
2842 l_party_rel_update_rec.object_table_name := r_exist_rel.object_table_name;
2843 l_party_rel_update_rec.subject_type := r_exist_rel.subject_type;
2844 l_party_rel_update_rec.object_type := r_exist_rel.object_type;
2845 l_party_rel_update_rec.application_id := r_exist_rel.application_id;
2846
2847 l_party_rel_update_rec.party_rec.status := 'I';
2848
2849 OPEN c_org_contact(r_exist_rel.relationship_id);
2850 FETCH c_org_contact INTO l_org_contact_id, l_cont_object_version_number, l_application_id; -- Bug 12359416
2851 CLOSE c_org_contact;
2852
2853 l_org_contact_update_rec.org_contact_id := l_org_contact_id;
2854 l_org_contact_update_rec.party_rel_rec := l_party_rel_update_rec;
2855 l_org_contact_update_rec.application_id := l_application_id; -- 625; Bug 12359416
2856
2857 l_rel_object_version_number := r_exist_rel.object_version_number;
2858
2859 OPEN c_party(r_exist_rel.party_id);
2860 FETCH c_party INTO l_party_object_version_number;
2861 CLOSE c_party;
2862
2863 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Calling HZ_PARTY_CONTACT_V2PUB.Update_Org_Contact...');
2864
2865 HZ_PARTY_CONTACT_V2PUB.Update_Org_Contact(
2866 p_init_msg_list => 'F',
2867 p_org_contact_rec => l_org_contact_update_rec,
2868 x_return_status => l_return_status,
2869 x_msg_count => l_msg_count,
2870 x_msg_data => l_msg_data,
2871 p_cont_object_version_number => l_cont_object_version_number,
2872 p_rel_object_version_number => l_rel_object_version_number,
2873 p_party_object_version_number => l_party_object_version_number);
2874
2875 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_return_status=' || l_return_status);
2876 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_cont_object_version_number=' || l_cont_object_version_number);
2877 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_rel_object_version_number=' || l_rel_object_version_number);
2878 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_party_object_version_number=' || l_party_object_version_number);
2879
2880 IF l_return_status = FND_API.G_RET_STS_ERROR OR
2881 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2882 RAISE FND_API.G_EXC_ERROR;
2883 END IF;
2884
2885 END LOOP;
2886
2887 l_party_rel_create_rec.subject_id := p_org_party_id;
2888 l_party_rel_create_rec.object_id := p_person_party_id;
2889 l_party_rel_create_rec.status := 'A';
2890 l_party_rel_create_rec.start_date := SYSDATE;
2891 l_party_rel_create_rec.relationship_type := p_type;
2892 l_party_rel_create_rec.relationship_code := p_type;
2893 l_party_rel_create_rec.subject_table_name := 'HZ_PARTIES';
2894 l_party_rel_create_rec.object_table_name := 'HZ_PARTIES';
2895 l_party_rel_create_rec.subject_type := 'ORGANIZATION';
2896 l_party_rel_create_rec.object_type := 'PERSON';
2897 --Begin-fix bug#4604755-JYPARK-09/12/2005-When create 'Collections'/'Dunning' default contact in Collections,
2898 --set CREATED_BY_MODULE column value depending on lookup HZ_CREATED_BY_MODULES
2899 --l_party_rel_create_rec.created_by_module := 'IEX-DEFAULT-CONTACT';
2900 IF p_type = 'COLLECTIONS' THEN
2901 l_party_rel_create_rec.created_by_module := 'IEX_COLLECTIONS_CONTACT';
2902 ELSIF p_type = 'DUNNING' THEN
2903 l_party_rel_create_rec.created_by_module := 'IEX_DUNNING_CONTACT';
2904 END IF;
2905 --End-fix bug#4604755-JYPARK-09/12/2005-When create 'Collections'/'Dunning' default contact in Collections,
2906 --set CREATED_BY_MODULE column value depending on lookup HZ_CREATED_BY_MODULES
2907
2908 l_party_rel_create_rec.application_id := 625;
2909
2910 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':HZ_GENERATE_PARTY_NUMBER=' || fnd_profile.value('HZ_GENERATE_PARTY_NUMBER'));
2911
2912 IF NVL(fnd_profile.value('HZ_GENERATE_PARTY_NUMBER'), 'Y') = 'N' THEN
2913 SELECT hz_parties_s.nextval
2914 INTO l_party_rel_create_rec.party_rec.party_number
2915 FROM dual;
2916 ELSE
2917 l_party_rel_create_rec.party_rec.party_number := '';
2918 END IF;
2919
2920 l_party_rel_create_rec.party_rec.status := 'A';
2921 l_org_contact_create_rec.party_rel_rec := l_party_rel_create_rec;
2922
2923 --Begin-fix bug#4604755-JYPARK-09/12/2005-When create 'Collections'/'Dunning' default contact in Collections,
2924 --set CREATED_BY_MODULE column value depending on lookup HZ_CREATED_BY_MODULES
2925 -- l_org_contact_create_rec.created_by_module := 'IEX-DEFAULT-CONTACT';
2926
2927 IF p_type = 'COLLECTIONS' THEN
2928 l_org_contact_create_rec.created_by_module := 'IEX_COLLECTIONS_CONTACT';
2929 l_org_contact_create_rec.job_title := 'Collections contact'; -- ER 13329879
2930 ELSIF p_type = 'DUNNING' THEN
2931 l_org_contact_create_rec.created_by_module := 'IEX_DUNNING_CONTACT';
2932 l_org_contact_create_rec.job_title := 'Dunning contact'; -- ER 13329879
2933 END IF;
2934
2935 --End-fix bug#4604755-JYPARK-09/12/2005-When create 'Collections'/'Dunning' default contact in Collections,
2936 --set CREATED_BY_MODULE column value depending on lookup HZ_CREATED_BY_MODULES
2937
2938 l_org_contact_create_rec.application_id := 625;
2939
2940 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':Calling HZ_PARTY_CONTACT_V2PUB.Create_Org_Contact...');
2941
2942 HZ_PARTY_CONTACT_V2PUB.Create_Org_Contact(
2943 p_init_msg_list => 'F',
2944 p_org_contact_rec => l_org_contact_create_rec,
2945 x_return_status => l_return_status,
2946 x_msg_count => l_msg_count,
2947 x_msg_data => l_msg_data,
2948 x_org_contact_id => l_org_contact_id,
2949 x_party_rel_id => l_party_relationship_id,
2950 x_party_id => l_party_id,
2951 x_party_number => l_party_number );
2952
2953 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_return_status=' || l_return_status);
2954 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_org_contact_id=' || l_org_contact_id || ' l_party_id=' || l_party_id || ' l_party_number=' || l_party_number);
2955 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_party_id=' || l_party_id);
2956
2957 x_party_id := l_party_id;
2958 x_relationship_id := l_party_relationship_id;
2959
2960 IF l_return_status = FND_API.G_RET_STS_ERROR OR
2961 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2962 RAISE FND_API.G_EXC_ERROR;
2963 END IF;
2964
2965 IF p_phone_contact_point_id IS NOT NULL THEN
2966 --Begin Bug 6509624 31-Mar-2009 barathsr
2967 /* OPEN c_contact_point(p_phone_contact_point_id);
2968 FETCH c_contact_point INTO l_phone_rec;
2969 CLOSE c_contact_point;*/
2970
2971 OPEN c_phone_contact_point(p_phone_contact_point_id);
2972 LOOP
2973 FETCH c_phone_contact_point INTO l_phone_rec;
2974 EXIT WHEN c_phone_contact_point%NOTFOUND;
2975 iex_debug_pub.LogMessage('l_phone_rec.contact_point_id:' || l_phone_rec.contact_point_id);
2976 iex_debug_pub.LogMessage('l_phone_rec.owner_table_id:' || l_contact_point_create_rec.owner_table_id);
2977 --End Bug 6509624 31-Mar-2009 barathsr
2978 l_contact_point_create_rec.contact_point_type := l_phone_rec.contact_point_type;
2979 l_contact_point_create_rec.status := l_phone_rec.status;
2980 l_contact_point_create_rec.owner_table_name := l_phone_rec.owner_table_name;
2981 l_contact_point_create_rec.owner_table_id := l_party_id;
2982 l_contact_point_create_rec.primary_flag := l_phone_rec.primary_flag;
2983 l_contact_point_create_rec.contact_point_purpose := p_type;
2984 --l_contact_point_create_rec.primary_by_purpose := 'Y';--Commented for Bug 6509624 31-Mar-2009 barathsr
2985 l_contact_point_create_rec.primary_by_purpose := l_phone_rec.primary_by_purpose; --Added for Bug 6509624 31-Mar-2009 barathsr
2986 l_contact_point_create_rec.orig_system_reference:= l_phone_rec.orig_system_reference;
2987 --Begin-fix bug#4604755-JYPARK-09/12/2005-When create 'Collections'/'Dunning' default contact in Collections,
2988 --set CREATED_BY_MODULE column value depending on lookup HZ_CREATED_BY_MODULES
2989 --l_contact_point_create_rec.created_by_module := 'IEX-DEFAULT-CONTACT';
2990 IF p_type = 'COLLECTIONS' THEN
2991 l_contact_point_create_rec.created_by_module := 'IEX_COLLECTIONS_CONTACT';
2992 ELSIF p_type = 'DUNNING' THEN
2993 l_contact_point_create_rec.created_by_module := 'IEX_DUNNING_CONTACT';
2994 END IF;
2995 --End-fix bug#4604755-JYPARK-09/12/2005-When create 'Collections'/'Dunning' default contact in Collections,
2996 -- set CREATED_BY_MODULE column value depending on lookup HZ_CREATED_BY_MODULES
2997
2998 l_contact_point_create_rec.content_source_type := l_phone_rec.content_source_type;
2999 l_contact_point_create_rec.attribute_category := l_phone_rec.attribute_category;
3000 l_contact_point_create_rec.attribute1 := l_phone_rec.attribute1;
3001 l_contact_point_create_rec.attribute2 := l_phone_rec.attribute2;
3002 l_contact_point_create_rec.attribute3 := l_phone_rec.attribute3;
3003 l_contact_point_create_rec.attribute4 := l_phone_rec.attribute4;
3004 l_contact_point_create_rec.attribute5 := l_phone_rec.attribute5;
3005 l_contact_point_create_rec.attribute6 := l_phone_rec.attribute6;
3006 l_contact_point_create_rec.attribute7 := l_phone_rec.attribute7;
3007 l_contact_point_create_rec.attribute8 := l_phone_rec.attribute8;
3008 l_contact_point_create_rec.attribute9 := l_phone_rec.attribute9;
3009 l_contact_point_create_rec.attribute10 := l_phone_rec.attribute10;
3010 l_contact_point_create_rec.attribute11 := l_phone_rec.attribute11;
3011 l_contact_point_create_rec.attribute12 := l_phone_rec.attribute12;
3012 l_contact_point_create_rec.attribute13 := l_phone_rec.attribute13;
3013 l_contact_point_create_rec.attribute14 := l_phone_rec.attribute14;
3014 l_contact_point_create_rec.attribute15 := l_phone_rec.attribute15;
3015 l_contact_point_create_rec.attribute16 := l_phone_rec.attribute16;
3016 l_contact_point_create_rec.attribute17 := l_phone_rec.attribute17;
3017 l_contact_point_create_rec.attribute18 := l_phone_rec.attribute18;
3018 l_contact_point_create_rec.attribute19 := l_phone_rec.attribute19;
3019 l_contact_point_create_rec.attribute20 := l_phone_rec.attribute20;
3020
3021 l_phone_create_rec.phone_area_code := l_phone_rec.phone_area_code;
3022 l_phone_create_rec.phone_country_code := l_phone_rec.phone_country_code;
3023 l_phone_create_rec.phone_number := l_phone_rec.phone_number;
3024 l_phone_create_rec.phone_extension := l_phone_rec.phone_extension;
3025 l_phone_create_rec.phone_line_type := l_phone_rec.phone_line_type;
3026 --l_phone_create_rec.raw_phone_number := l_phone_rec.raw_phone_number;
3027
3028 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':calling hz_contact_point_v2pub.create_phone_contact_point ...');
3029
3030 hz_contact_point_v2pub.create_phone_contact_point(
3031 p_init_msg_list => 'F',
3032 x_return_status => l_return_status,
3033 x_msg_count => l_msg_count,
3034 x_msg_data => l_msg_data,
3035 p_contact_point_rec => l_contact_point_create_rec,
3036 p_phone_rec => l_phone_create_rec,
3037 x_contact_point_id => l_contact_point_id);
3038
3039 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_return_status=' || l_return_status || ':l_contact_point_id=' || l_contact_point_id);
3040
3041 IF l_return_status = FND_API.G_RET_STS_ERROR OR
3042 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3043 RAISE FND_API.G_EXC_ERROR;
3044 END IF;
3045 END LOOP;--Added for Bug 6509624 31-Mar-2009 barathsr
3046 CLOSE c_phone_contact_point;--Added for Bug 6509624 31-Mar-2009 barathsr
3047
3048 END IF; /* IF p_phone_contact_point_id IS NOT NULL THEN */
3049
3050 IF p_email_contact_point_id IS NOT NULL THEN
3051 OPEN c_contact_point(p_email_contact_point_id);
3052 FETCH c_contact_point INTO l_email_rec;
3053 CLOSE c_contact_point;
3054
3055 l_contact_point_create_rec.contact_point_type := l_email_rec.contact_point_type;
3056 l_contact_point_create_rec.status := l_email_rec.status;
3057 l_contact_point_create_rec.owner_table_name := l_email_rec.owner_table_name;
3058 l_contact_point_create_rec.owner_table_id := l_party_id;
3059 l_contact_point_create_rec.primary_flag := l_email_rec.primary_flag;
3060 l_contact_point_create_rec.contact_point_purpose := p_type;
3061 l_contact_point_create_rec.primary_by_purpose := 'Y';
3062 l_contact_point_create_rec.orig_system_reference:= l_email_rec.orig_system_reference;
3063 --Begin-fix bug#4604755-JYPARK-09/12/2005-When create 'Collections'/'Dunning' default contact in Collections,
3064 -- set CREATED_BY_MODULE column value depending on lookup HZ_CREATED_BY_MODULES
3065 --l_contact_point_create_rec.created_by_module := 'IEX-DEFAULT-CONTACT';
3066 IF p_type = 'COLLECTIONS' THEN
3067 l_contact_point_create_rec.created_by_module := 'IEX_COLLECTIONS_CONTACT';
3068 ELSIF p_type = 'DUNNING' THEN
3069 l_contact_point_create_rec.created_by_module := 'IEX_DUNNING_CONTACT';
3070 END IF;
3071 --End-fix bug#4604755-JYPARK-09/12/2005-When create 'Collections'/'Dunning' default contact in Collections,
3072 -- set CREATED_BY_MODULE column value depending on lookup HZ_CREATED_BY_MODULES
3073
3074 l_contact_point_create_rec.content_source_type := l_email_rec.content_source_type;
3075 l_contact_point_create_rec.attribute_category := l_email_rec.attribute_category;
3076 l_contact_point_create_rec.attribute1 := l_email_rec.attribute1;
3077 l_contact_point_create_rec.attribute2 := l_email_rec.attribute2;
3078 l_contact_point_create_rec.attribute3 := l_email_rec.attribute3;
3079 l_contact_point_create_rec.attribute4 := l_email_rec.attribute4;
3080 l_contact_point_create_rec.attribute5 := l_email_rec.attribute5;
3081 l_contact_point_create_rec.attribute6 := l_email_rec.attribute6;
3082 l_contact_point_create_rec.attribute7 := l_email_rec.attribute7;
3083 l_contact_point_create_rec.attribute8 := l_email_rec.attribute8;
3084 l_contact_point_create_rec.attribute9 := l_email_rec.attribute9;
3085 l_contact_point_create_rec.attribute10 := l_email_rec.attribute10;
3086 l_contact_point_create_rec.attribute11 := l_email_rec.attribute11;
3087 l_contact_point_create_rec.attribute12 := l_email_rec.attribute12;
3088 l_contact_point_create_rec.attribute13 := l_email_rec.attribute13;
3089 l_contact_point_create_rec.attribute14 := l_email_rec.attribute14;
3090 l_contact_point_create_rec.attribute15 := l_email_rec.attribute15;
3091 l_contact_point_create_rec.attribute16 := l_email_rec.attribute16;
3092 l_contact_point_create_rec.attribute17 := l_email_rec.attribute17;
3093 l_contact_point_create_rec.attribute18 := l_email_rec.attribute18;
3094 l_contact_point_create_rec.attribute19 := l_email_rec.attribute19;
3095 l_contact_point_create_rec.attribute20 := l_email_rec.attribute20;
3096
3097 l_email_create_rec.email_format := l_email_rec.email_format;
3098 l_email_create_rec.email_address := l_email_rec.email_address;
3099
3100 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':calling hz_contact_point_v2pub.create_email_contact_point ...');
3101
3102 hz_contact_point_v2pub.create_email_contact_point(
3103 p_init_msg_list => 'F',
3104 x_return_status => l_return_status,
3105 x_msg_count => l_msg_count,
3106 x_msg_data => l_msg_data,
3107 p_contact_point_rec => l_contact_point_create_rec,
3108 p_email_rec => l_email_create_rec,
3109 x_contact_point_id => l_contact_point_id);
3110
3111 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_return_status=' || l_return_status || ':l_contact_point_id=' || l_contact_point_id);
3112
3113 IF l_return_status = FND_API.G_RET_STS_ERROR OR
3114 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3115 RAISE FND_API.G_EXC_ERROR;
3116 END IF;
3117
3118 END IF; /* IF p_email_contact_point_id IS NOT NULL THEN */
3119
3120 -- begin ER 13329879
3121
3122 /*
3123 begin
3124 select location_id,party_site_id into l_location_id,l_party_site_id from hz_party_sites
3125 where identifying_address_flag = 'Y'
3126 and party_id = p_org_party_id;
3127 exception
3128 when others then
3129 iex_debug_pub.logmessage(G_PKG_NAME || '.' || l_api_name ||'Exception from getting location ID...l_party_id ==> '||l_party_id);
3130 iex_debug_pub.logmessage(G_PKG_NAME || '.' || l_api_name ||'Error Code / Msg => '||sqlerrm);
3131 RAISE FND_API.G_EXC_ERROR;
3132 end;
3133 */
3134 iex_debug_pub.logmessage(G_PKG_NAME || '.' || l_api_name ||'Location Id => '||l_location_id);
3135 iex_debug_pub.logmessage(G_PKG_NAME || '.' || l_api_name ||'Party Site Id => '||l_party_site_id);
3136
3137 begin
3138
3139 /*
3140 if p_cust_account_id is null then
3141 select cust_acct_site_id,cust_account_id into l_cust_acct_site_id,t_cust_account_id from hz_cust_acct_sites
3142 where party_site_id = l_party_site_id
3143 and status = 'A' and rownum = 1;
3144 else
3145 select cust_acct_site_id into l_cust_acct_site_id from hz_cust_acct_sites
3146 where party_site_id = l_party_site_id
3147 and cust_account_id = p_cust_account_id;
3148
3149 t_cust_account_id := p_cust_account_id;
3150 end if;
3151 */
3152
3153 -- if p_cust_account_id is null then
3154 select cust_account_id into t_cust_account_id from hz_cust_accounts
3155 where party_id = p_org_party_id
3156 and status = 'A' and rownum = 1 order by account_number;
3157 -- else
3158 -- t_cust_account_id := p_cust_account_id;
3159 -- end if;
3160
3161 /*
3162 select cust_account_id into t_cust_account_id from hz_cust_acct_sites
3163 where cust_acct_site_id = (select cust_acct_site_id from hz_cust_site_uses_all where site_use_id = :IEXCUOVW_HEADER.customer_site_use_id);
3164 */
3165
3166 update hz_cust_account_roles set status = 'I'
3167 where status = 'A'
3168 and created_by_module = l_org_contact_create_rec.created_by_module
3169 and cust_account_id = t_cust_account_id;
3170 -- and cust_account_id in (select cust_account_id from hz_cust_accounts where party_id = p_org_party_id);
3171 commit;
3172
3173 exception
3174 when others then
3175 iex_debug_pub.logmessage(G_PKG_NAME || '.' || l_api_name ||'Exception from getting CUST_ACCT_SITE_ID...l_party_site_id ==> '||l_party_site_id);
3176 iex_debug_pub.logmessage(G_PKG_NAME || '.' || l_api_name ||'Exception from getting CUST_ACCT_SITE_ID...t_cust_account_id ==> '||t_cust_account_id);
3177 iex_debug_pub.logmessage(G_PKG_NAME || '.' || l_api_name ||'Error Code / Msg => '||sqlerrm);
3178 RAISE FND_API.G_EXC_ERROR;
3179 end;
3180 iex_debug_pub.logmessage(G_PKG_NAME || '.' || l_api_name ||'From getting CUST_ACCT_SITE_ID...l_cust_acct_site_id ==> '||l_cust_acct_site_id);
3181 -- end ER 13329879
3182
3183 IF l_location_id IS NOT NULL THEN
3184
3185 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_party_id=' || l_party_id || ':l_location_id=' || l_location_id);
3186 OPEN c_CheckPartySite(l_party_id, l_location_id);
3187 FETCH c_CheckPartySite INTO l_party_site_id, l_party_site_number;
3188
3189 iex_debug_pub.logmessage(G_PKG_NAME || '.' || l_api_name ||'Party Site Id(2) => '||l_party_site_id);
3190 iex_debug_pub.logmessage(G_PKG_NAME || '.' || l_api_name ||'Party Site Number => '||l_party_site_number);
3191
3192 IF (c_CheckPartySite%FOUND) THEN
3193 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':party site existing already');
3194 l_call_api := FALSE;
3195 ELSE
3196 l_call_api := TRUE;
3197 END IF; /*End of C_CheckPartySite%FOUND if loop */
3198 CLOSE c_CheckPartySite;
3199
3200 IF l_Call_Api then
3201 l_Party_Site_Create_rec.Party_Id := l_party_id;
3202 l_Party_Site_Create_rec.Location_Id := l_location_id;
3203
3204 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':HZ_GENERATE_PARTY_SITE_NUMBER=' || fnd_profile.value('HZ_GENERATE_PARTY_SITE_NUMBER'));
3205
3206 IF NVL(fnd_profile.value('HZ_GENERATE_PARTY_SITE_NUMBER'), 'Y') = 'N' THEN
3207 SELECT hz_party_sites_s.nextval
3208 INTO l_Party_Site_Create_rec.Party_Site_Number
3209 FROM dual;
3210 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_party_site_number=' || l_party_site_create_rec.party_site_number);
3211 ELSE
3212 l_Party_Site_Create_rec.Party_Site_Number := NULL;
3213 END IF;
3214
3215 l_Party_Site_Create_rec.Identifying_Address_Flag := 'Y';
3216 l_Party_Site_Create_rec.Status := 'A';
3217
3218 IF p_type = 'COLLECTIONS' THEN
3219 l_Party_Site_Create_rec.created_by_module := 'IEX_COLLECTIONS_CONTACT';
3220 ELSIF p_type = 'DUNNING' THEN
3221 l_Party_Site_Create_rec.created_by_module := 'IEX_DUNNING_CONTACT';
3222 END IF;
3223
3224 l_Party_Site_Create_rec.Application_id := 625;
3225
3226 l_Party_Site_Create_rec.Party_Site_Name := NULL;
3227
3228 HZ_PARTY_SITE_V2PUB.Create_Party_Site (
3229 p_init_msg_list => 'F',
3230 p_party_site_rec => l_party_site_Create_rec,
3231 x_return_status => l_return_status,
3232 x_msg_count => l_msg_count,
3233 x_msg_data => l_msg_data,
3234 x_party_site_id => l_party_site_id,
3235 x_party_site_number => l_party_site_number
3236 );
3237
3238 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_return_status=' || l_return_status);
3239 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_party_site_id=' || l_party_site_id);
3240 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_return_status=' || l_return_status);
3241
3242 IF l_return_status = FND_API.G_RET_STS_ERROR OR
3243 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3244 RAISE FND_API.G_EXC_ERROR;
3245 END IF;
3246
3247 END IF; /*End of if l_Call_Api true loop for Party Site*/
3248
3249 END IF; /* IF p_location_id IS NOT NULL THEN */
3250
3251
3252 -- Begin ER 13329879
3253 IF p_type = 'COLLECTIONS' THEN
3254 L_CUST_ACCOUNT_ROLE_REC.created_by_module := 'IEX_COLLECTIONS_CONTACT';
3255 ELSIF p_type = 'DUNNING' THEN
3256 L_CUST_ACCOUNT_ROLE_REC.created_by_module := 'IEX_DUNNING_CONTACT';
3257 END IF;
3258 L_CUST_ACCOUNT_ROLE_REC.application_id := 625;
3259 L_CUST_ACCOUNT_ROLE_REC.party_id := l_party_id;
3260 L_CUST_ACCOUNT_ROLE_REC.cust_account_id := t_cust_account_id;
3261 L_CUST_ACCOUNT_ROLE_REC.primary_flag := 'N';
3262 L_CUST_ACCOUNT_ROLE_REC.role_type := 'CONTACT';
3263 L_CUST_ACCOUNT_ROLE_REC.status := 'A';
3264 -- L_CUST_ACCOUNT_ROLE_REC.cust_acct_site_id := l_cust_acct_site_id;
3265
3266 HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role (
3267 p_init_msg_list => 'F',
3268 p_cust_account_role_rec => L_CUST_ACCOUNT_ROLE_REC,
3269 x_cust_account_role_id => l_cust_account_role_id,
3270 x_return_status => l_return_status,
3271 x_msg_count => l_msg_count,
3272 x_msg_data => l_msg_data);
3273
3274 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_return_status=' || l_return_status);
3275 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_cust_account_role_id=' || l_cust_account_role_id);
3276
3277 IF l_return_status = FND_API.G_RET_STS_ERROR OR
3278 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3279 RAISE FND_API.G_EXC_ERROR;
3280 END IF;
3281 -- End ER 13329879
3282
3283 -- Standard check of p_commit
3284 IF FND_API.To_Boolean(p_commit) THEN
3285 COMMIT WORK;
3286 END IF;
3287
3288 -- Standard call to get message count and if count is 1, get message info
3289 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3290
3291
3292 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':end');
3293 EXCEPTION
3294 WHEN FND_API.G_EXC_ERROR THEN
3295 ROLLBACK TO Create_Default_Contact_PVT;
3296 x_return_status := FND_API.G_RET_STS_ERROR;
3297 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3298
3299 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3300 ROLLBACK TO Create_Default_Contact_PVT;
3301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3302 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3303
3304 WHEN OTHERS THEN
3305 ROLLBACK TO Create_Default_Contact_PVT;
3306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3307 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3308 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3309 END IF;
3310 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3311 END Create_Default_Contact;
3312 BEGIN
3313 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
3314 G_APPL_ID := FND_GLOBAL.Prog_Appl_Id;
3315 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
3316 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
3317 G_USER_ID := FND_GLOBAL.User_Id;
3318 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
3319 END IEX_CUST_OVERVIEW_PVT;