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