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