[Home] [Help]
PACKAGE BODY: APPS.IEX_PROFILE
Source
1 PACKAGE BODY IEX_PROFILE AS
2 /* $Header: iexrcntb.pls 120.21.12020000.2 2012/07/24 19:10:43 schekuri ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_PROFILE';
4 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexrcntb.pls';
5 PG_DEBUG NUMBER;
6 g_line varchar2(100);
7 TYPE curr_rec_type IS RECORD (
8 set_of_books_id ar_system_parameters.set_of_books_id%TYPE ,
9 base_currency gl_sets_of_books.currency_code%TYPE ,
10 past_year_from DATE,
11 past_year_to DATE
12 );
13
14 g_curr_rec curr_rec_type;
15
16 ---------------------------------------------------------------------
17 -- Get_past_year_inv_info
18 --------------------------------------------------------------------
19 -- Queries Past year installment information. Called from
20 -- get_profile_info provedure.
21 ---------------------------------------------------------------------
22 PROCEDURE Get_past_year_inv_info
23 (p_filter_mode IN Varchar2,
24 p_filter_id IN Number,
25 p_using_paying_rel IN VARCHAR2,
26 p_total_inv OUT NOCOPY Number,
27 p_unpaid_inv OUT NOCOPY Number,
28 p_ontime_inv OUT NOCOPY Number,
29 p_late_inv OUT NOCOPY Number,
30 p_error_msg OUT NOCOPY Varchar2)
31 IS
32 BEGIN
33 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
34 IEX_DEBUG_PUB.LogMessage(g_line) ;
35 IEX_DEBUG_PUB.LogMessage('GET_PAST_YEAR_INV_INFO --->> Start <<--- ') ;
36 IEX_DEBUG_PUB.LogMessage(g_line) ;
37 END IF;
38
39 --------------------------------------------------------------
40 -- Past Year Unpaid Installments
41 --------------------------------------------------------------
42 if p_filter_mode = 'PARTY' then
43 BEGIN
44 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
45 SELECT SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
46 SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
47 SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
48 INTO p_unpaid_inv,
49 p_ontime_inv,
50 p_late_inv
51 from ar_payment_schedules aps,
52 hz_cust_accounts hzca
53 where aps.customer_id = hzca.cust_account_id
54 and aps.class IN ('INV', 'DM', 'CB')
55 and aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
56 and hzca.party_id IN
57 (SELECT p_filter_id FROM dual
58 UNION
59 SELECT ar.related_party_id
60 FROM ar_paying_relationships_v ar
61 WHERE ar.party_id = p_filter_id
62 AND TRUNC(sysdate) BETWEEN
63 TRUNC(NVL(ar.effective_start_date,sysdate)) AND
64 TRUNC(NVL(ar.effective_end_date,sysdate)) );
65 ELSE
66 SELECT SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
67 SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
68 SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
69 INTO p_unpaid_inv,
70 p_ontime_inv,
71 p_late_inv
72 from ar_payment_schedules aps,
73 hz_cust_accounts hzca
74 where aps.customer_id = hzca.cust_account_id
75 and aps.class IN ('INV', 'DM', 'CB')
76 and aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
77 and hzca.party_id = p_filter_id ;
78
79 END IF;
80 EXCEPTION
81 WHEN OTHERS THEN
82 p_error_msg :=
83 'Get Past Year Invoice Info >> Party >> Unpaid Installments'
84 || SQLCODE || ' << ' || SQLERRM ;
85 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
86 iex_debug_pub.logMessage(p_error_msg) ;
87 END IF;
88 End ;
89 ELSIF p_filter_mode = 'CUST' then
90 BEGIN
91 SELECT SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
92 SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
93 SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
94 INTO p_unpaid_inv,
95 p_ontime_inv,
96 p_late_inv
97 from ar_payment_schedules aps,
98 hz_cust_accounts hzca
99 where aps.customer_id = hzca.cust_account_id
100 and aps.class IN ('INV', 'DM', 'CB')
101 and aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
102 and hzca.cust_account_id = p_filter_id ;
103 EXCEPTION
104 WHEN OTHERS THEN
105 p_error_msg :=
106 'Get Past Year Invoice Info >> Cust >> Unpaid Installments'
107 || SQLCODE || ' << ' || SQLERRM ;
108 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
109 iex_debug_pub.logMessage(p_error_msg) ;
110 END IF;
111 End ;
112
113 ELSIF p_filter_mode = 'DEL' then -- added by jypark
114 BEGIN
115 SELECT SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
116 SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
117 SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
118 INTO p_unpaid_inv,
119 p_ontime_inv,
120 p_late_inv
121 from ar_payment_schedules aps,
122 iex_delinquencies del
123 where aps.class IN ('INV', 'DM', 'CB')
124 and aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
125 and del.payment_schedule_id = aps.payment_schedule_id
126 and del.delinquency_id = p_filter_id;
127 EXCEPTION
128 WHEN OTHERS THEN
129 p_error_msg :=
130 'Get Past Year Invoice Info >> Del >> Unpaid Installments'
131 || SQLCODE || ' << ' || SQLERRM ;
132 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
133 iex_debug_pub.logMessage(p_error_msg) ;
134 END IF;
135 End ;
136 ELSIF p_filter_mode = 'BILL_TO' then -- added by jypark
137 BEGIN
138 SELECT SUM(DECODE(aps.status, 'OP', 1, 0)), -- Unpaid Installments
139 SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 0, 1), 0)), -- On time Installments
140 SUM(DECODE(aps.status, 'CL', DECODE(SIGN(aps.actual_date_closed - aps.due_date), 1, 1, 0), 0)) -- Late Installements
141 INTO p_unpaid_inv,
142 p_ontime_inv,
143 p_late_inv
144 from ar_payment_schedules aps
145 where aps.class IN ('INV', 'DM', 'CB')
146 and aps.due_date between g_curr_rec.past_year_from and g_curr_rec.past_year_to
147 and aps.customer_site_use_id = p_filter_id;
148 EXCEPTION
149 WHEN OTHERS THEN
150 p_error_msg :=
151 'Get Past Year Invoice Info >> Del >> Unpaid Installments'
152 || SQLCODE || ' << ' || SQLERRM ;
153 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
154 iex_debug_pub.logMessage(p_error_msg) ;
155 END IF;
156 End ;
157
158 End If ;
159
160 -- Calculating Total Transactions (Sum of Unpaid, Late and Ontime)
161 p_total_inv := p_unpaid_inv + p_late_inv + p_ontime_inv ;
162 p_error_msg := null ;
163
164 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
165 IEX_DEBUG_PUB.LogMessage(g_line) ;
166 IEX_DEBUG_PUB.LogMessage('GET_PAST_YEAR_INV_INFO --->> End <<--- ') ;
167 IEX_DEBUG_PUB.LogMessage(g_line) ;
168 END IF;
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 p_error_msg := 'Get Past Year Invoice Info >> ' || SQLCODE || ' << ' || SQLERRM ;
173 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
174 iex_debug_pub.logMessage(p_error_msg) ;
175 END IF;
176 End Get_past_year_inv_info;
177
178 ------------------------------------------------------------------------------
179 -- PROCEDURE GET_PROFILE_INFO
180 ------------------------------------------------------------------------------
181 -- IEX/AST 1) Total Promises, Broken Promises, Open Promises
182 -- 2) Credit Limit, Credit Rating, Credit Status, Collector Name
183 -- 3) Outcome, Last Contact Date, Contacted By, Result
184 --
185 ------------------------------------------------------------------------------
186 PROCEDURE GET_PROFILE_INFO
187 (p_api_version IN NUMBER,
188 p_init_msg_list IN VARCHAR2,
189 p_commit IN VARCHAR2,
190 p_validation_level IN NUMBER,
191 x_return_status OUT NOCOPY VARCHAR2,
192 x_msg_count OUT NOCOPY NUMBER,
193 x_msg_data OUT NOCOPY VARCHAR2,
194 p_calling_app IN VARCHAR2,
195 p_filter_mode IN VARCHAR2 ,
196 p_Party_id IN Number,
197 p_cust_account_id IN Number,
198 p_delinquency_id IN Number, -- added by jypark
199 p_customer_site_use_id IN Number, -- added by jypark for Bill-to
200 p_using_paying_rel IN VARCHAR2,
201 x_profile_rec OUT NOCOPY Profile_Rec)
202 IS
203 l_api_version CONSTANT NUMBER := 1.0;
204 l_api_name CONSTANT VARCHAR2(30) := 'GET_PROFILE_INFO';
205 l_return_status VARCHAR2(1);
206 l_msg_count NUMBER;
207 l_msg_data VARCHAR2(32767);
208 l_Profile_rec Profile_rec ;
209
210 v_msg_data Varchar2(100) ;
211 v_return_status Varchar2(100) ;
212 v_msg_count Number ;
213
214 v_profile_sql Varchar2(5000) ;
215 v_profile_sql2 Varchar2(5000) ;
216 v_profile_sql_late Varchar2(5000) ;
217 v_profile_where Varchar2(200) ;
218 v_profile_filter_id Number ;
219 v_apply_date_filter varchar2(200) ;
220
221 l_filter_id Number ;
222 l_error_msg varchar2(2000) ;
223
224 l_today date;
225 start_date_time_val date;--Added for Bug 8200476 16-Jul-2009 barathsr
226
227
228 -- START BUG 5187355 scherkas 07/27/2006
229 -- START BUG 6529958 gnramasa 7th mar 2008
230 CURSOR PARTY_INT_CUR(p_party_id Number)
231 IS
232 -- SELECT JIOV.short_description outcome,
233 -- jii.start_date_time s_date,
234 -- JRREV.resource_Name res_name,
235 -- JIRV.short_description result
236 -- FROM JTF_IH_INTERACTIONS JII,
237 -- JTF_RS_RESOURCE_EXTNS_VL JRREV,
238 -- JTF_IH_OUTCOMES_VL JIOV,
239 -- JTF_IH_RESULTS_VL JIRV
240 -- WHERE JII.resource_id = JRREV.resource_id
241 -- AND JIOV.Outcome_id = JII.outcome_id
242 -- AND JIRV.Result_id(+) = JII.result_id
243 -- AND JII.Party_Id = p_Party_id
244 -- AND JII.start_date_time = (select Max(i.start_date_time)
245 -- from jtf_ih_interactions i
246 -- where i.party_id = jii.party_id) ;
247
248 SELECT JIOV.short_description outcome,
249 jii.start_date_time s_date,
250 JRREV.resource_Name res_name,
251 JIRV.short_description result
252 FROM JTF_IH_INTERACTIONS JII,
253 JTF_RS_RESOURCE_EXTNS_TL JRREV,
254 JTF_IH_OUTCOMES_TL JIOV,
255 JTF_IH_RESULTS_TL JIRV
256 WHERE JII.resource_id = JRREV.resource_id
257 AND JRREV.LANGUAGE (+)= USERENV('LANG')
258 AND JIOV.Outcome_id = JII.outcome_id
259 AND JIOV.LANGUAGE (+)= USERENV('LANG')
260 AND JIRV.Result_id(+) = JII.result_id
261 AND JIRV.LANGUAGE (+)= USERENV('LANG')
262 AND JII.Party_Id = p_party_id
263 AND JII.start_date_time = (select Max(i.start_date_time)
264 from jtf_ih_interactions i
265 where i.party_id = jii.party_id) ;
266 -- END BUG 5187355 scherkas 07/27/2006
267
268
269 --Begin Bug 8200476 16-Jul-2009 barathsr
270
271 cursor cust_int_get_dt30_cur(cust_acct_id number)
272 is
273 SELECT
274 /*+ leading(a) index(A JTF_IH_ACTIVITIES_N3) index(I JTF_IH_INTERACTIONS_CUS01)*/
275 MAX(i.start_date_time)
276 FROM jtf_ih_interactions i,
277 jtf_ih_activities a
278 WHERE a.cust_account_id = cust_acct_id
279 AND a.interaction_id = i.interaction_id
280 and i.start_date_time between (trunc(sysdate)-30) and sysdate;
281
282 cursor cust_int_get_dt365_cur(cust_acct_id number)
283 is
284 SELECT
285 /*+ leading(a) index(A JTF_IH_ACTIVITIES_N3) index(I JTF_IH_INTERACTIONS_CUS01)*/
286 MAX(i.start_date_time)
287 FROM jtf_ih_interactions i,
288 jtf_ih_activities a
289 WHERE a.cust_account_id = cust_acct_id
290 AND a.interaction_id = i.interaction_id
291 and i.start_date_time between (trunc(sysdate)-365) and sysdate;
292
293 cursor cust_int_get_dt_cur(cust_acct_id number)
294 is
295 SELECT
296 /*+ leading(a) index(A JTF_IH_ACTIVITIES_N3) index(I JTF_IH_INTERACTIONS_CUS01)*/
297 MAX(i.start_date_time)
298 FROM jtf_ih_interactions i,
299 jtf_ih_activities a
300 WHERE a.cust_account_id = cust_acct_id
301 AND a.interaction_id = i.interaction_id;
302
303 --End Bug 8200476 16-Jul-2009 barathsr
304
305 CURSOR CUST_INT_CUR(cust_acct_id Number,date_time date) --Added for Bug 8200476 16-Jul-2009 barathsr
306 IS
307 SELECT
308 /*+ index (jia JTF_IH_ACTIVITIES_N4) index(JII JTF_IH_INTERACTIONS_N6)*/ --Added for Bug 8200476 16-Jul-2009 barathsr
309 DISTINCT JIOVT.short_description outcome,
310 jii.start_date_time s_date ,
311 JRRES.source_Name res_name ,
312 JIRV.short_description result
313 FROM JTF_IH_INTERACTIONS JII ,
314 jtf_rs_resource_extns JRRES,
315 JTF_IH_OUTCOMES_tl JIOVT ,
316 jtf_ih_outcomes_b jiovb ,
317 JTF_IH_RESULTS_tl JIRV ,
318 JTF_IH_ACTIVITIES JIA
319 WHERE JII.resource_id = JRRES.resource_id
320 AND jii.interaction_id = jia.interaction_id
321 AND jiovt.outcome_id = jiovb.outcome_id
322 AND JIOVB.Outcome_id(+) = JII.outcome_id
323 AND JIRV.Result_id(+) = JII.result_id
324 AND jia.cust_account_id = cust_acct_id
325 AND jiovt.language = userenv('LANG')
326 AND JII.start_date_time = date_time; --Added for Bug 8200476 16-Jul-2009 barathsr
327 --Start of comment for Bug 8200476 16-Jul-2009 barathsr
328 -- (SELECT
329 -- /*+ index(I JTF_IH_ACTIVITIES_N3)*/
330 /* MAX(i.start_date_time)
331 FROM jtf_ih_interactions i,
332 jtf_ih_activities a
333 WHERE a.cust_account_id = cust_acct_id
334 AND a.interaction_id = i.interaction_id);
335 --End bug 7572544 16-Jan-2009 barathsr*/
336
337
338 -- CURSOR CUST_INT_CUR(cust_acct_id Number)
339 -- IS
340 -- START BUG 5187355 scherkas 07/27/2006
341
342 -- SELECT DISTINCT JIOV.short_description outcome,
343 -- jii.start_date_time s_date,
344 -- JRREV.resource_Name res_name,
345 -- JIRV.short_description result
346 -- FROM JTF_IH_INTERACTIONS JII,
347 -- JTF_RS_RESOURCE_EXTNS_VL JRREV,
348 -- JTF_IH_OUTCOMES_VL JIOV,
349 --START BUG 4930386 jsanju 01/10/06
350 --JTF_IH_RESULTS_VL JIRV,
351 -- JTF_IH_RESULTS_B JIRB,
352 -- JTF_IH_RESULTS_TL JIRV,
353 --END BUG 4930386 jsanju 01/10/06
354 -- JTF_IH_ACTIVITIES_VL JIA
355 -- WHERE JII.resource_id = JRREV.resource_id
356 -- AND jii.interaction_id = jia.interaction_id
357 -- AND JIOV.Outcome_id = JII.outcome_id
358
359 --START BUG 4930386 jsanju 01/10/06
360 --AND JIRV.Result_id(+) = JII.result_id
361 -- AND JIRB.Result_id(+) = JII.result_id
362 -- AND JIRV.Result_id(+) =JIRB.Result_id
363 -- AND JIRV.LANGUAGE (+)= USERENV('LANG')
364 --END BUG 4930386 jsanju 01/10/06
365 -- AND jia.cust_account_id = cust_acct_id
366 -- AND JII.start_date_time =
367 -- (select Max(i.start_date_time)
368 -- from jtf_ih_interactions i,
369 -- jtf_ih_activities a
370 -- where a.cust_account_id = cust_acct_id
371 -- AND a.interaction_id = i.interaction_id) ;
372
373 /*SELECT DISTINCT JIOV.short_description outcome,
374 jii.start_date_time s_date,
375 JRREV.resource_Name res_name,
376 JIRV.short_description result
377 FROM JTF_IH_INTERACTIONS JII,
378 JTF_RS_RESOURCE_EXTNS_TL JRREV,
379 JTF_IH_OUTCOMES_TL JIOV,
380 JTF_IH_RESULTS_B JIRB,
381 JTF_IH_RESULTS_TL JIRV,
382 JTF_IH_ACTIVITIES JIA
383 WHERE JII.resource_id = JRREV.resource_id
384 AND JRREV.LANGUAGE (+)= USERENV('LANG')
385 AND jii.interaction_id = jia.interaction_id
386 AND JIOV.Outcome_id = JII.outcome_id
387 AND JIOV.LANGUAGE (+)= USERENV('LANG')
388 AND JIRB.Result_id(+) = JII.result_id
389 AND JIRV.Result_id(+) =JIRB.Result_id
390 AND JIRV.LANGUAGE (+)= USERENV('LANG')
391 AND jia.cust_account_id = cust_acct_id
392 AND JII.start_date_time =
393 (select Max(i.start_date_time)
394 from jtf_ih_interactions i,
395 jtf_ih_activities a
396 where a.cust_account_id = cust_acct_id
397 AND a.interaction_id = i.interaction_id);*/
398 -- END BUG 5187355 scherkas 07/27/2006
399 --End of comment for Bug 8200476 16-Jul-2009 barathsr
400
401
402 CURSOR PARTY_INT_PAYING_CUR(p_party_id Number)
403 IS
404 -- START BUG 5187355 scherkas 07/27/2006
405 -- SELECT JIOV.short_description outcome,
406 -- jii.start_date_time s_date,
407 -- JRREV.resource_Name res_name,
408 -- JIRV.short_description result
409 -- FROM JTF_IH_INTERACTIONS JII,
410 -- JTF_RS_RESOURCE_EXTNS_VL JRREV,
411 -- JTF_IH_OUTCOMES_VL JIOV,
412 -- JTF_IH_RESULTS_VL JIRV
413 -- WHERE JII.resource_id = JRREV.resource_id
414 -- AND JIOV.Outcome_id = JII.outcome_id
415 -- AND JIRV.Result_id(+) = JII.result_id
416 -- AND JII.Party_Id IN
417 -- (SELECT p_party_id FROM dual
418 -- UNION
419 -- SELECT ar.related_party_id
420 -- FROM ar_paying_relationships_v ar
421 -- WHERE ar.party_id = p_party_id
422 -- AND TRUNC(sysdate) BETWEEN
423 -- TRUNC(NVL(ar.effective_start_date,sysdate)) AND
424 -- TRUNC(NVL(ar.effective_end_date,sysdate)) )
425 -- AND JII.start_date_time = (select Max(i.start_date_time)
426 -- from jtf_ih_interactions i
427 -- where i.party_id = jii.party_id) ;
428
429 SELECT JIOV.short_description outcome,
430 jii.start_date_time s_date,
431 JRREV.resource_Name res_name,
432 JIRV.short_description result
433 FROM JTF_IH_INTERACTIONS JII,
434 JTF_RS_RESOURCE_EXTNS_TL JRREV,
435 JTF_IH_OUTCOMES_TL JIOV,
436 JTF_IH_RESULTS_TL JIRV
437 WHERE JII.resource_id = JRREV.resource_id
438 AND JRREV.LANGUAGE (+)= USERENV('LANG')
439 AND JIOV.Outcome_id = JII.outcome_id
440 AND JIOV.LANGUAGE (+)= USERENV('LANG')
441 AND JIRV.Result_id(+) = JII.result_id
442 AND JIRV.LANGUAGE (+)= USERENV('LANG')
443 AND JII.Party_Id IN
444 (SELECT p_party_id FROM dual
445 UNION
446 SELECT ar.related_party_id
447 FROM ar_paying_relationships_v ar
448 WHERE ar.party_id = p_party_id
449 AND TRUNC(sysdate) BETWEEN
450 TRUNC(NVL(ar.effective_start_date,sysdate)) AND
451 TRUNC(NVL(ar.effective_end_date,sysdate)) )
452 AND JII.start_date_time = (select Max(i.start_date_time)
453 from jtf_ih_interactions i
454 where i.party_id = jii.party_id) ;
455 -- END BUG 5187355 scherkas 07/27/2006
456 -- END BUG 6529958 gnramasa 7th mar 2008
457
458
459 CURSOR PARTY_CUSTOMER_PROFILE_CUR(p_party_id NUMBER)
460 IS
461 SELECT coll.name,
462 arpt_sql_func_util.get_lookup_meaning('CREDIT_RATING', cust_prof.credit_rating)
463 FROM hz_customer_profiles cust_prof, ar_collectors coll
464 WHERE cust_prof.party_id = p_party_id
465 AND coll.collector_id(+) = cust_prof.collector_id
466 AND cust_prof.cust_account_id = -1;
467
468 CURSOR CUST_CUSTOMER_PROFILE_CUR(p_cust_account_id NUMBER)
469 IS
470 SELECT coll.name,
471 arpt_sql_func_util.get_lookup_meaning('CREDIT_RATING', cust_prof.credit_rating)
472 FROM hz_customer_profiles cust_prof, ar_collectors coll
473 WHERE cust_prof.cust_account_id = p_cust_account_id
474 AND coll.collector_id(+) = cust_prof.collector_id
475 AND cust_prof.site_use_id IS NULL;
476
477 CURSOR SITE_CUSTOMER_PROFILE_CUR(p_customer_site_use_id NUMBER)
478 IS
479 SELECT coll.name,
480 arpt_sql_func_util.get_lookup_meaning('CREDIT_RATING', cust_prof.credit_rating)
481 FROM hz_customer_profiles cust_prof, ar_collectors coll
482 WHERE cust_prof.site_use_id = p_customer_site_use_id
483 AND coll.collector_id(+) = cust_prof.collector_id;
484
485 CURSOR C_DEL(p_delinquency_id NUMBER)
486 IS
487 SELECT cust_account_id, customer_site_use_id
488 FROM iex_delinquencies
489 WHERE delinquency_id = p_delinquency_id;
490
491 CURSOR C_SITE(p_customer_site_use_id NUMBER)
492 IS
493 SELECT cust_account_id
494 FROM hz_cust_site_uses site_use, hz_cust_acct_sites acct_site
495 WHERE site_use.site_use_id = p_customer_site_use_id
496 AND acct_site.cust_acct_site_id = site_use.cust_acct_site_id;
497
498 l_cust_account_id NUMBER;
499 l_customer_site_use_id NUMBER;
500 l_credit_limit_amt_func NUMBER;
501 -- Start for the bug 8630157 by PNAVEENK
502 l_conversion_type VARCHAR(30);
503
504 CURSOR C_PARTY_CREDIT(p_party_id NUMBER , p_conversion_type varchar2)
505 IS
506 -- Begin fix bug #5685635-12/08/2006-return null when credit limits value is null instead of -2
507 --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
508 SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
509 -- End fix bug #5685635-12/08/2006-return null when credit limits value is null instead of -2
510 -- sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit))),
511 sysdate, p_conversion_type, prof_amt.overall_credit_limit))),
512 -- start for bug 13583293 by sunagesh
513 -- DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
514 -- DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
515 NULL,
516 NULL, -- end for bug 13583293 by sunagesh
517 g_curr_rec.base_currency
518 FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt
519 -- ar_cmgt_setup_options cm_opt
520 WHERE prof.party_id = p_party_id
521 AND prof.site_use_id IS NULL
522 AND prof.status = 'A'
523 -- Begin fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
524 -- AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
525 -- AND prof_amt.cust_account_id = prof.cust_account_id
526 AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id(+)
527 AND prof_amt.cust_account_id(+) = prof.cust_account_id
528 -- End fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
529 AND prof_amt.site_use_id IS NULL
530 --Begin-fix bug#4610424-JYPARK-09/16/2005-exclude credir limit for account
531 AND prof.cust_account_id = -1;
532 --End-fix bug#4610424-JYPARK-09/16/2005-exclude credir limit for account
533
534 CURSOR C_CUST_CREDIT(p_cust_account_id NUMBER , p_conversion_type varchar2)
535 IS
536 -- Begin fix bug #5685635-12/08/2006-return null when credit limits value is null instead of -2
537 --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
538 SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
539 -- End fix bug #5685635-12/08/2006-return null when credit limits value is null instead of -2
540 -- sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit))),
541 sysdate, p_conversion_type, prof_amt.overall_credit_limit))),
542 -- start for bug 13583293 by sunagesh
543 -- DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
544 -- DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
545 NULL,
546 NULL, -- end for bug 13583293 by sunagesh
547 g_curr_rec.base_currency
548 FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt
549 -- ar_cmgt_setup_options cm_opt
550 WHERE prof.cust_account_id = p_cust_account_id
551 AND prof.site_use_id IS NULL
552 AND prof.status = 'A'
553 -- Begin fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
554 -- AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
555 -- AND prof_amt.cust_account_id = p_cust_account_id
556 AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id(+)
557 AND prof_amt.cust_account_id(+) = p_cust_account_id
558 -- End fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
559 AND prof_amt.site_use_id IS NULL;
560
561 CURSOR C_SITE_CREDIT(p_customer_site_use_id NUMBER , p_conversion_type varchar2)
562 IS
563 -- Begin fix bug #5685635-12/08/2006-return null when credit limits value is null instead of -2
564 --SELECT SUM(gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
565 SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
566 -- End fix bug #5685635-12/08/2006-return null when credit limits value is null instead of -2
567 -- sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit))),
568 sysdate, p_conversion_type, prof_amt.overall_credit_limit))),
569 -- start for bug 13583293 by sunagesh
570 --DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
571 --DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 0)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N')),
572 NULL,
573 NULL, -- end for bug 13583293 by sunagesh
574 g_curr_rec.base_currency
575 FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt
576 -- ar_cmgt_setup_options cm_opt
577 WHERE prof.site_use_id = p_customer_site_use_id
578 AND prof.status = 'A'
579 -- Begin fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
580 -- AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
581 -- AND prof_amt.site_use_id = p_customer_site_use_id;
582 AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id(+)
583 AND prof_amt.site_use_id(+) = p_customer_site_use_id;
584 -- End fix bug #5194537-JYPARK-05/03/2006-add outer join when credit limit not exist
585
586 --start bug for 13583293 by sunagesh
587 CURSOR C_PARTY_PROFILE(p_party_id NUMBER)
588 IS
589 SELECT
590 DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 'N',0,NULL)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), 0, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N'),''),
591 DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 'N',0,NULL)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), 0, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N'),'')
592 FROM hz_customer_profiles prof
593 WHERE prof.party_id =p_party_id
594 AND prof.cust_account_id = -1
595 AND prof.site_use_id IS NULL
596 AND prof.status = 'A';
597
598 CURSOR C_CUST_PROFILE(p_cust_account_id NUMBER)
599 IS
600 SELECT
601 DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 'N',0,NULL)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), 0, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N'),''),
602 DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 'N',0,NULL)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), 0, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N'),'')
603 FROM hz_customer_profiles prof
604 WHERE prof.cust_account_id = p_cust_account_id
605 AND prof.site_use_id IS NULL
606 AND prof.status = 'A';
607
608 CURSOR C_SITE_PROFILE(p_customer_site_use_id NUMBER)
609 IS
610 SELECT
611 DECODE(MAX(DECODE(prof.credit_hold, 'Y', 1, 'N',0,NULL)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), 0, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N'),''),
612 DECODE(MAX(DECODE(prof.dunning_letters, 'Y', 1, 'N',0,NULL)), 1, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'Y'), 0, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO', 'N'),'')
613 FROM hz_customer_profiles prof
614 WHERE prof.site_use_id = p_customer_site_use_id
615 AND prof.status = 'A';
616
617 -- end bug for 13583293 by sunagesh
618
619 l_credit_status NUMBER;
620 BEGIN
621 l_conversion_type := NVL(FND_PROFILE.VALUE('IEX_EXCHANGE_RATE_TYPE'), 'Corporate');
622 l_today := TRUNC(sysdate) ;
623 IF p_filter_mode = 'DEL' then
624 l_filter_id := p_delinquency_id ;
625 elsIF p_filter_mode = 'CUST' then
626 l_filter_id := p_cust_account_id ;
627 elsIF p_filter_mode = 'PARTY' then
628 l_filter_id := p_party_id ;
629 elsIF p_filter_mode = 'BILL_TO' then
630 l_filter_id := p_customer_site_use_id ;
631 END IF ;
632
633
634 SAVEPOINT Get_Profile_Info_PVT;
635
636 -- Standard call to check for call compatibility.
637 IF NOT FND_API.Compatible_API_Call (l_api_version,
638 p_api_version,
639 l_api_name,
640 G_PKG_NAME) THEN
641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
642 END IF;
643
644 -- Check p_init_msg_list
645 IF FND_API.to_Boolean( p_init_msg_list ) THEN
646 FND_MSG_PUB.initialize;
647 END IF;
648
649 x_return_status := FND_API.G_RET_STS_SUCCESS;
650 --Bug4562698. Moved the Constructor to GET_PROFILE_INFO. Begin.
651 Begin
652 SELECT distinct sob.currency_code
653 INTO g_curr_rec.base_currency
654 FROM ar_system_parameters sysp,
655 gl_sets_of_books sob
656 WHERE sob.set_of_books_id = sysp.set_of_books_id;
657 Exception when others then
658 g_curr_rec.base_currency := NULL;
659 End;
660
661 -- Past Year From and To
662 SELECT TRUNC(add_months(sysdate, - 12)) pastYearFrom ,
663 TRUNC(sysdate) pastYearTo
664 INTO g_curr_rec.past_year_from,
665 g_curr_rec.past_year_to
666 FROM dual;
667
668 --Bug4562698. Moved the Constructor to GET_PROFILE_INFO. End.
669
670
671 Get_past_year_inv_info(
672 p_filter_mode => p_filter_mode,
673 p_filter_id => l_filter_id,
674 p_using_paying_rel => p_using_paying_rel,
675 p_total_inv => l_profile_rec.Installments_due,
676 p_unpaid_inv => l_profile_rec.Unpaid_Installments,
677 p_ontime_inv => l_profile_rec.Ontime_Installments,
678 p_late_inv => l_profile_rec.late_Installments,
679 p_error_msg => l_error_msg) ;
680
681 BEGIN
682 --------------------------------------------------------------
683 -- Past Year Total Promises
684 --------------------------------------------------------------
685 IF p_filter_mode = 'PARTY' then
686 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
687 SELECT COUNT(1),
688 SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
689 SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
690 Into l_profile_rec.Total_Promises,
691 l_profile_rec.Broken_Promises,
692 l_profile_rec.Open_Promises
693 FROM IEX_PROMISE_DETAILS IPD,
694 HZ_CUST_ACCOUNTS HZCA,
695 IEX_DELINQUENCIES DEL -- Moac Change Added Delinquency
696 WHERE IPD.cust_account_id = HZCA.cust_Account_id
697 AND IPD.DELINQUENCY_ID = DEL.DELINQUENCY_ID
698 AND trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
699 AND HZCA.Party_id IN
700 (SELECT p_party_id FROM dual
701 UNION
702 SELECT ar.related_party_id
703 FROM ar_paying_relationships_v ar
704 WHERE ar.party_id = p_party_id
705 AND TRUNC(sysdate) BETWEEN
706 TRUNC(NVL(ar.effective_start_date,sysdate)) AND
707 TRUNC(NVL(ar.effective_end_date,sysdate)) );
708 ELSE
709 SELECT COUNT(1),
710 SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
711 SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
712 Into l_profile_rec.Total_Promises,
713 l_profile_rec.Broken_Promises,
714 l_profile_rec.Open_Promises
715 FROM IEX_PROMISE_DETAILS IPD,
716 HZ_CUST_ACCOUNTS HZCA,
717 IEX_DELINQUENCIES DEL
718 WHERE IPD.cust_account_id = HZCA.cust_Account_id
719 AND IPD.DELINQUENCY_ID = DEL.DELINQUENCY_ID
720 AND trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
721 AND HZCA.Party_id = p_party_id ;
722 END IF;
723
724 ELSIF p_filter_mode = 'CUST' then
725 SELECT COUNT(1),
726 SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
727 SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
728 Into l_profile_rec.Total_Promises,
729 l_profile_rec.Broken_Promises,
730 l_profile_rec.Open_Promises
731 FROM IEX_PROMISE_DETAILS IPD,
732 HZ_CUST_ACCOUNTS HZCA,
733 IEX_DELINQUENCIES DEL -- Moac Change Added Delinquency
734 WHERE IPD.cust_account_id = HZCA.cust_Account_id
735 AND IPD.DELINQUENCY_ID = DEL.DELINQUENCY_ID
736 AND trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
737 AND HZCA.cust_account_id = p_cust_account_id ;
738
739 ELSIF p_filter_mode = 'DEL' then -- added by jypark
740 SELECT COUNT(1),
741 SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
742 SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
743 Into l_profile_rec.Total_Promises,
744 l_profile_rec.Broken_Promises,
745 l_profile_rec.Open_Promises
746 FROM IEX_PROMISE_DETAILS IPD
747 WHERE trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
748 AND IPD.delinquency_id = p_delinquency_id;
749
750 ELSIF p_filter_mode = 'BILL_TO' then -- added by jypark
751 SELECT COUNT(1),
752 SUM(DECODE(IPD.STATE, 'BROKEN_PROMISE', 1, 0)),
753 SUM(DECODE(IPD.STATUS, 'COLLECTABLE', 1, 'PENDING', 1, 'FULLFILLED', 1, 'IN_QUESTION', 1, 'OPEN', 1, 0))
754 Into l_profile_rec.Total_Promises,
755 l_profile_rec.Broken_Promises,
756 l_profile_rec.Open_Promises
757 FROM IEX_PROMISE_DETAILS IPD,
758 IEX_DELINQUENCIES DEL
759 WHERE trunc(IPD.CREATION_DATE) BETWEEN g_curr_rec.past_year_from AND g_curr_rec.past_year_to
760 AND IPD.delinquency_id = DEL.delinquency_id
761 AND DEL.customer_site_use_id = p_customer_site_use_id;
762
763 END IF ;
764 EXCEPTION
765 WHEN OTHERS THEN
766 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
767 IEX_DEBUG_PUB.LogMessage('Unexpected Error:C_CUSTOMER_PROFILE_CUR - ' || SQLCODE || ' Mesg - ' || SQLERRM) ;
768 END IF;
769 ROLLBACK TO Get_Profile_Info_PVT;
770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
771 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
772 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
773 END IF;
774 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
775 END ;
776
777 BEGIN
778 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
779 IEX_DEBUG_PUB.LogMessage('Filter Mode = ' || p_Filter_mode || ' party_id = ' || to_char(p_party_id) || ' cust_account_id = ' || to_char(p_cust_account_id) );
780 IEX_DEBUG_PUB.LogMessage(' delinquency_id = ' || to_char(p_delinquency_id) || ' customer_site_use_id= ' || to_char(p_customer_site_use_id)) ;
781 END IF;
782
783 IF p_filter_mode = 'PARTY' then
784 IF NVL(p_using_paying_rel, 'N') = 'Y' THEN
785 OPEN PARTY_INT_PAYING_CUR(p_party_id) ;
786 FETCH PARTY_INT_PAYING_CUR INTO
787 l_profile_rec.Last_Outcome ,
788 l_profile_rec.last_contact_date ,
789 l_profile_rec.Last_Contacted_By ,
790 l_profile_rec.Last_Result ;
791 CLOSE PARTY_INT_PAYING_CUR ;
792
793 ELSE
794 OPEN PARTY_INT_CUR(p_party_id) ;
795 FETCH PARTY_INT_CUR INTO
796 l_profile_rec.Last_Outcome ,
797 l_profile_rec.last_contact_date ,
798 l_profile_rec.Last_Contacted_By ,
799 l_profile_rec.Last_Result ;
800 CLOSE PARTY_INT_CUR ;
801 END IF;
802 ELSIF p_filter_mode = 'CUST' then
803 --Begin Bug 8200476 16-Jul-2009 barathsr
804 --Added Begin-End block for Bug 8811872 19-Aug-2009 barathsr
805 begin
806 open cust_int_get_dt30_cur(p_cust_account_id);
807 fetch cust_int_get_dt30_cur into
808 start_date_time_val;
809 if (cust_int_get_dt30_cur%notfound or start_date_time_val is null) then
810 open cust_int_get_dt365_cur(p_cust_account_id);
811 fetch cust_int_get_dt365_cur into
812 start_date_time_val;
813 if (cust_int_get_dt365_cur%notfound or start_date_time_val is null) then
814 open cust_int_get_dt_cur(p_cust_account_id);
815 fetch cust_int_get_dt_cur into
816 start_date_time_val;
817 if (cust_int_get_dt_cur%notfound or start_date_time_val is null) then
818 IEX_DEBUG_PUB.LogMessage('no values to fetch');
819 IEX_DEBUG_PUB.LogMessage('error in fetching values in date_time_cursor');
820 end if;
821 close cust_int_get_dt_cur;
822 end if;
823 close cust_int_get_dt365_cur;
824 end if;
825 close cust_int_get_dt30_cur;
826 --End Bug 8200476 16-Jul-2009 barathsr
827
828 OPEN CUST_INT_CUR(p_cust_Account_id,start_date_time_val);--Added for Bug 8200476 16-Jul-2009 barathsr
829 FETCH CUST_INT_CUR INTO
830 l_profile_rec.Last_Outcome ,
831 l_profile_rec.last_contact_date ,
832 l_profile_rec.Last_Contacted_By ,
833 l_profile_rec.Last_Result;
834 CLOSE CUST_INT_CUR ;
835 exception
836 when others then
837 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
838 IEX_DEBUG_PUB.LogMessage('Unexpected Error:CUST_INT_CUR - ' || SQLCODE || ' Mesg - ' || SQLERRM) ;
839 END IF;
840 end;
841
842 ELSIF p_filter_mode = 'DEL' then
843 OPEN C_DEL(p_delinquency_id);
844 FETCH C_DEL INTO l_cust_account_id, l_customer_site_use_id;
845 CLOSE C_DEL;
846
847 OPEN CUST_INT_CUR(l_cust_account_id,start_date_time_val);--Added for Bug 8200476 16-Jul-2009 barathsr -- added by jypark but no delinquency dependency
848 FETCH CUST_INT_CUR INTO
849 l_profile_rec.Last_Outcome ,
850 l_profile_rec.last_contact_date ,
851 l_profile_rec.Last_Contacted_By ,
852 l_profile_rec.Last_Result ;
853 ELSIF p_filter_mode = 'BILL_TO' then
854 OPEN C_SITE(p_customer_site_use_id);
855 FETCH C_SITE INTO l_cust_account_id;
856 CLOSE C_SITE;
857
858 OPEN CUST_INT_CUR(l_cust_account_id,start_date_time_val);--Added for Bug 8200476 16-Jul-2009 barathsr -- added by jypark but no delinquency dependency
859 FETCH CUST_INT_CUR INTO
860 l_profile_rec.Last_Outcome ,
861 l_profile_rec.last_contact_date ,
862 l_profile_rec.Last_Contacted_By ,
863 l_profile_rec.Last_Result ;
864 CLOSE CUST_INT_CUR;
865 END IF ;
866 IEX_DEBUG_PUB.LogMessage ( ' Entered in profile check block');
867 IF p_filter_mode = 'PARTY' then
868 IEX_DEBUG_PUB.LogMessage ( 'Entered in PARTY_CUSTOMER_PROFILE_CUR');
869 OPEN PARTY_CUSTOMER_PROFILE_CUR(p_party_id);
870 FETCH PARTY_CUSTOMER_PROFILE_CUR INTO l_profile_rec.collector_name, l_profile_rec.credit_rating;
871 CLOSE PARTY_CUSTOMER_PROFILE_CUR;
872 ELSIF p_filter_mode = 'CUST' then
873 IEX_DEBUG_PUB.LogMessage ( 'Entered in CUST_CUSTOMER_PROFILE_CUR');
874 OPEN CUST_CUSTOMER_PROFILE_CUR(p_cust_account_id);
875 FETCH CUST_CUSTOMER_PROFILE_CUR INTO l_profile_rec.collector_name, l_profile_rec.credit_rating;
876 CLOSE CUST_CUSTOMER_PROFILE_CUR;
877 ELSIF p_filter_mode = 'BILL_TO' then
878 IEX_DEBUG_PUB.LogMessage ( 'Entered in SITE_CUSTOMER_PROFILE_CUR');
879 OPEN SITE_CUSTOMER_PROFILE_CUR(p_customer_site_use_id);
880 FETCH SITE_CUSTOMER_PROFILE_CUR INTO l_profile_rec.collector_name, l_profile_rec.credit_rating;
881 CLOSE SITE_CUSTOMER_PROFILE_CUR;
882 ELSIF p_filter_mode= 'DEL' then
883 IF l_customer_site_use_id IS NOT NULL THEN
884 IEX_DEBUG_PUB.LogMessage ( 'Entered in SITE_CUSTOMER_PROFILE_CUR');
885 OPEN SITE_CUSTOMER_PROFILE_CUR(l_customer_site_use_id);
886 FETCH SITE_CUSTOMER_PROFILE_CUR INTO l_profile_rec.collector_name, l_profile_rec.credit_rating;
887 CLOSE SITE_CUSTOMER_PROFILE_CUR;
888 END IF;
889 END IF ;
890
891
892 EXCEPTION
893 WHEN OTHERS THEN
894 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
895 IEX_DEBUG_PUB.LogMessage('Unexpected Error:C_CUSTOMER_PROFILE_CUR - ' || SQLCODE || ' Mesg - ' || SQLERRM) ;
896 END IF;
897 ROLLBACK TO Get_Profile_Info_PVT;
898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
899 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
900 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
901 END IF;
902 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
903 END ;
904
905 BEGIN
906
907 -- Begin -- bug#4300281 - 15/4/2005 - vaijayanthi - Calculate 'Include Dunning' value based on Filter Mode
908 IEX_DEBUG_PUB.LogMessage ( ' Enterd in credit limit calculation block');
909
910 IF p_filter_mode='PARTY' THEN -- Party Level
911 IEX_DEBUG_PUB.LogMessage ( 'Entered in c_party_credit');
912 OPEN c_party_credit(p_party_id , l_conversion_type);
913 FETCH c_party_credit
914 INTO l_profile_rec.credit_limit_amt, l_profile_rec.credit_status, l_profile_rec.include_dunning, l_profile_rec.credit_limit_amt_curr;
915 CLOSE c_party_credit;
916 ELSIF p_filter_mode='CUST' THEN -- Account Level
917 IEX_DEBUG_PUB.LogMessage ( 'Entered in c_cust_credit');
918 OPEN c_cust_credit(p_cust_account_id , l_conversion_type);
919 FETCH c_cust_credit
920 INTO l_profile_rec.credit_limit_amt, l_profile_rec.credit_status, l_profile_rec.include_dunning, l_profile_rec.credit_limit_amt_curr;
921 CLOSE c_cust_credit;
922 ELSIF p_filter_mode='BILL_TO' THEN -- Bill to Level
923 OPEN c_site_credit(p_customer_site_use_id , l_conversion_type);
924 FETCH c_site_credit
925 INTO l_profile_rec.credit_limit_amt, l_profile_rec.credit_status, l_profile_rec.include_dunning, l_profile_rec.credit_limit_amt_curr;
926 CLOSE c_site_credit;
927 END IF;
928
929
930 IEX_DEBUG_PUB.LogMessage('Credit limit amount of cust_account_id = ' || to_char(p_cust_account_id) || 'is ' || l_profile_rec.credit_limit_amt);
931 IEX_DEBUG_PUB.LogMessage('Credit Status of cust_account_id = ' || to_char(p_cust_account_id) || 'is ' || l_profile_rec.credit_status);
932 IEX_DEBUG_PUB.LogMessage('Include dunning of cust_account_id = ' || to_char(p_cust_account_id) || 'is ' || l_profile_rec.include_dunning);
933 IEX_DEBUG_PUB.LogMessage('Credit amount currency of cust_account_id = ' || to_char(p_cust_account_id) || 'is ' || l_profile_rec.credit_limit_amt_curr);
934
935 -- End for the bug 8630157 by PNAVEENK
936 --Bug4562698. Null out the Amount if the currency code is null. Start
937 IF g_curr_rec.base_currency IS NULL THEN
938 l_profile_rec.credit_limit_amt := NULL;
939 END IF;
940 --Bug4562698. Null out the Amount if the currency code is null. End.
941
942 /* -- Old Code
943 IF p_party_id IS NOT NULL THEN
944 OPEN c_party_credit(p_party_id);
945 FETCH c_party_credit
946 INTO l_profile_rec.credit_limit_amt, l_profile_rec.credit_status, l_profile_rec.include_dunning, l_profile_rec.credit_limit_amt_curr;
947 CLOSE c_party_credit;
948 ELSIF p_cust_account_id IS NOT NULL THEN
949 OPEN c_cust_credit(p_cust_account_id);
950 FETCH c_cust_credit
951 INTO l_profile_rec.credit_limit_amt, l_profile_rec.credit_status, l_profile_rec.include_dunning, l_profile_rec.credit_limit_amt_curr;
952 CLOSE c_cust_credit;
953 ELSIF p_customer_site_use_id IS NOT NULL THEN
954 OPEN c_site_credit(p_customer_site_use_id);
955 FETCH c_site_credit
956 INTO l_profile_rec.credit_limit_amt, l_profile_rec.credit_status, l_profile_rec.include_dunning, l_profile_rec.credit_limit_amt_curr;
957 CLOSE c_site_credit;
958 END IF; */
959
960 -- end -- bug#4300281 - 15/4/2005 - vaijayanthi - Calculate 'Include Dunning' value based on Filter Mode
961 -- start bug for 13583293 by sunagesh
962 IEX_DEBUG_PUB.LogMessage ( ' Enterd in Dunning profile and Credit hold status finding block');
963
964 IF p_filter_mode='PARTY' THEN -- Party Level
965 IEX_DEBUG_PUB.LogMessage ( 'Entered in c_party_profile');
966 OPEN c_party_profile(p_party_id);
967 FETCH c_party_profile
968 INTO l_profile_rec.credit_status, l_profile_rec.include_dunning;
969 CLOSE c_party_profile;
970 ELSIF p_filter_mode='CUST' THEN -- Account Level
971 IEX_DEBUG_PUB.LogMessage ( 'Entered in c_cust_profile');
972 OPEN c_cust_profile(p_cust_account_id );
973 FETCH c_cust_profile
974 INTO l_profile_rec.credit_status, l_profile_rec.include_dunning;
975 CLOSE c_cust_profile;
976 ELSIF p_filter_mode='BILL_TO' THEN -- Bill to Level
977 OPEN c_site_profile(p_customer_site_use_id );
978 FETCH c_site_profile
979 INTO l_profile_rec.credit_status, l_profile_rec.include_dunning;
980 CLOSE c_site_profile;
981 END IF;
982
983 IEX_DEBUG_PUB.LogMessage('Credit Status is ' || l_profile_rec.credit_status);
984 IEX_DEBUG_PUB.LogMessage('Include dunning is ' || l_profile_rec.include_dunning);
985 -- end bug for 13583293 by sunagesh
986
987
988 EXCEPTION
989 WHEN OTHERS THEN
990
991 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
992 IEX_DEBUG_PUB.LogMessage('Unexpected Error:C_CREDIT - ' || SQLCODE || ' Mesg - ' || SQLERRM) ;
993
994 END IF;
995 ROLLBACK TO Get_Profile_Info_PVT;
996 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
997 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
998 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
999 END IF;
1000 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1001 END;
1002
1003 x_profile_rec := l_profile_rec ;
1004
1005 -- Standard check of p_commit
1006 IF FND_API.To_Boolean(p_commit) THEN
1007 COMMIT WORK;
1008 END IF;
1009
1010 -- Standard call to get message count AND IF count is 1, get message info
1011 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1012 EXCEPTION
1013 WHEN FND_API.G_EXC_ERROR THEN
1014 ROLLBACK TO Get_Profile_Info_PVT;
1015 x_return_status := FND_API.G_RET_STS_ERROR;
1016 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1017
1018 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1019 ROLLBACK TO Get_Profile_Info_PVT;
1020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1021 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1022
1023 WHEN OTHERS THEN
1024 ROLLBACK TO Get_Profile_Info_PVT;
1025 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1026 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1027 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1028 END IF;
1029 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1030 END GET_PROFILE_INFO;
1031
1032 BEGIN
1033 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1034 g_line := '-----------------------------------------' ;
1035 --Bug4562698. Moved the Constructor to GET_PROFILE_INFO. Begin
1036 /* SELECT sob.currency_code
1037 INTO g_curr_rec.base_currency
1038 FROM ar_system_parameters sysp,
1039 gl_sets_of_books sob
1040 WHERE sob.set_of_books_id = sysp.set_of_books_id;
1041
1042 -- Past Year From and To
1043 SELECT TRUNC(add_months(sysdate, - 12)) pastYearFrom ,
1044 TRUNC(sysdate) pastYearTo
1045 INTO g_curr_rec.past_year_from,
1046 g_curr_rec.past_year_to
1047 FROM dual; */
1048 --Bug4562698. Moved the Constructor to GET_PROFILE_INFO. Begin
1049
1050 END IEX_PROFILE;