[Home] [Help]
PACKAGE BODY: APPS.IEX_CALL_HISTORY
Source
1 PACKAGE BODY IEX_CALL_HISTORY
2 /* $Header: iexhiclb.pls 120.0 2004/01/24 03:18:48 appldev noship $ */
3 AS
4 ---------------------------------------------------------------------
5 -- Forward Declarations - Call History
6 ---------------------------------------------------------------------
7 PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
8
9 Procedure Get_Activity_Status
10 (p_Interaction_id IN Number,
11 p_doc_ref IN Varchar2,
12 p_Activity_Exists OUT NOCOPY Varchar2) ;
13
14 ---------------------------------------------------------------------
15 -- Forward Declarations - Call History Activities
16 ---------------------------------------------------------------------
17
18 Procedure Get_Action_Description
19 (p_Action_id IN Number,
20 p_Action_Desc OUT NOCOPY JTF_IH_ACTIONS_VL.Short_Description%TYPE) ;
21
22 Procedure Get_OUTCOME_Description
23 (p_Outcome_Id IN Number,
24 p_Outcome_Desc OUT NOCOPY JTF_IH_OUTCOMES_VL.Short_Description%TYPE) ;
25
26 Procedure Get_REASON_Description
27 (p_REASON_id IN Number,
28 p_REASON_Desc OUT NOCOPY JTF_IH_REASONS_VL.Short_Description%TYPE) ;
29
30 Procedure Get_result_description
31 (p_result_id IN Number,
32 p_result_Desc OUT NOCOPY JTF_IH_RESULTS_VL.Short_Description%TYPE) ;
33
34 Procedure Get_Object_Name
35 (p_doc_ref IN Varchar2,
36 p_object_name OUT NOCOPY JTF_OBJECTS_VL.name%TYPE) ;
37 ---------------------------------------------------------------------
38 -- QUERY_INTERACTION_RECORDS
39 ---------------------------------------------------------------------
40 -- Queries and returns history records for the passed date range
41 -- Called from the form block.
42 ---------------------------------------------------------------------
43 PROCEDURE QUERY_INTERACTION_RECORDS (
44 p_mode IN Varchar2,
45 p_id IN Number ,
46 P_From_Date IN Date ,
47 P_To_Date IN Date ,
48 p_Interaction_tbl IN OUT NOCOPY Interaction_Tbl,
49 p_error_code IN OUT NOCOPY Varchar2 ,
50 p_error_mesg IN OUT NOCOPY Varchar2 )
51 IS
52 l_cnt Number ;
53 v_interaction_cur Interaction_cursor ;
54 v_interaction_sql Varchar2(1000) ;
55 v_activity_status Varchar2(1000) ;
56 v_interaction_id JTF_IH_INTERACTIONS.Interaction_Id%TYPE ;
57
58 CURSOR Interaction_party_cur
59 (p_party_id Number,
60 p_from_date Date,
61 p_to_date Date)
62 IS
63 Select JIIN.interaction_id ,
64 JIIN.start_date_time ,
65 JREV.resource_name
66 From JTF_IH_INTERACTIONS JIIN ,
67 JTF_RS_RESOURCE_EXTNS_VL JREV
68 where JIIN.party_id = p_party_id
69 AND JIIN.start_date_time
70 BETWEEN p_from_date and (p_to_date + 1)
71 AND JIIN.Resource_Id = JREV.resource_id
72 ORDER BY JIIN.start_date_time DESC ;
73
74 CURSOR Interaction_cust_cur
75 (p_cust_account_id Number,
76 p_from_date Date,
77 p_to_date Date)
78 IS
79 Select JIIN.interaction_id ,
80 JIIN.start_date_time ,
81 JREV.resource_name
82 From JTF_IH_INTERACTIONS JIIN ,
83 JTF_RS_RESOURCE_EXTNS_VL JREV,
84 JTF_IH_ACTIVITIES JIA
85 where JIA.cust_account_id = p_cust_account_id
86 AND JIA.Interaction_Id = JIIN.Interaction_Id
87 AND JIIN.Resource_Id = JREV.resource_id
88 AND JIIN.start_date_time
89 BETWEEN p_from_date and (p_to_date + 1)
90 ORDER BY JIIN.start_date_time DESC ;
91
92 Begin
93
94 if p_mode = 'PARTY' then
95 OPEN Interaction_party_cur(p_id, p_from_date, p_to_date) ;
96 FETCH Interaction_party_cur
97 BULK COLLECT INTO
98 v_interaction_id_tbl,
99 v_start_date_time_tbl,
100 v_resource_name_tbl ;
101
102 close Interaction_party_cur ;
103 elsif p_mode = 'CUST' then
104 OPEN Interaction_cust_cur(p_id, p_from_date, p_to_date) ;
105 FETCH Interaction_cust_cur
106 BULK COLLECT INTO
107 v_interaction_id_tbl,
108 v_start_date_time_tbl,
109 v_resource_name_tbl ;
110
111 close Interaction_cust_cur ;
112 End IF ;
113
114
115
116 FOR l_cnt IN 1..v_interaction_id_tbl.count LOOP
117
118 p_Interaction_tbl(l_cnt).Interaction_id
119 := v_Interaction_id_tbl(l_cnt);
120
121 v_interaction_id := v_Interaction_id_tbl(l_cnt) ;
122
123 p_Interaction_tbl(l_cnt).Start_Date
124 := To_Char(v_start_date_time_tbl(l_cnt),'DD-MON-YYYY') ;
125 p_Interaction_tbl(l_cnt).Start_Time
126 := To_char(v_start_date_time_tbl(l_cnt), 'HH:MM:SS') ;
127
128 p_Interaction_tbl(l_cnt).resource_name
129 := v_resource_name_tbl(l_cnt);
130
131 -- Dispute Activity Status
132 get_Activity_status(v_interaction_id, 'IEX_DISPUTE',
133 v_activity_status) ;
134 If v_activity_status NOT IN ('Yes', 'No') then
135 p_Interaction_tbl(l_cnt).Disputed := 'ERROR' ;
136 Else
137 p_Interaction_tbl(l_cnt).Disputed := v_activity_status ;
138 End If ;
139
140 -- Payment Activity Status
141 get_Activity_status(v_interaction_id, 'IEX_PAYMENT',
142 v_activity_status) ;
143 If v_activity_status NOT IN ('Yes', 'No') then
144 p_Interaction_tbl(l_cnt).Paid := 'ERROR' ;
145 Else
146 p_Interaction_tbl(l_cnt).Paid := v_activity_status ;
147 End If ;
148
149 -- Correspondence Activity Status
150 get_Activity_status(v_interaction_id, 'IEX_DUNNING',
151 v_activity_status) ;
152 If v_activity_status NOT IN ('Yes', 'No') then
153 p_Interaction_tbl(l_cnt).Correspondence_Sent := 'ERROR' ;
154 Else
155 p_Interaction_tbl(l_cnt).Correspondence_Sent
156 := v_activity_status ;
157 End If ;
158
159 -- Promise_to_pay Activity Status
160 get_Activity_status(v_interaction_id, 'IEX_PROMISE', v_activity_status) ;
161 If v_activity_status NOT IN ('Yes', 'No') then
162 p_Interaction_tbl(l_cnt).Promise_to_pay := 'ERROR' ;
163 Else
164 p_Interaction_tbl(l_cnt).Promise_to_pay
165 := v_activity_status ;
166 End If ;
167 END LOOP ;
168
169 Exception
170 WHEN OTHERS THEN
171 p_error_code := SQLCODE ;
172 p_error_mesg := SQLERRM ;
173
174 End ;
175 ---------------------------------------------------------------------
176 -- PROCEDURE QUERY_ACTIVITY_RECORDS
177 ---------------------------------------------------------------------
178 -- Queries activity records for the passed interaction_id from
179 -- JTF_IH_ACTIVITIES table. This returns a table of records which is
180 -- used as the source for detail block in the call history form.
181 ---------------------------------------------------------------------
182 PROCEDURE QUERY_ACTIVITY_RECORDS
183 (P_Interaction_id IN OUT NOCOPY Number ,
184 p_Interaction_Activity_tbl IN OUT NOCOPY
185 Interaction_Activity_Tbl,
186 p_error_code IN OUT NOCOPY Varchar2,
187 p_error_mesg IN OUT NOCOPY Varchar2)
188 IS
189 l_cnt Number := 1 ;
190 v_activity_cur Interaction_Activity_cursor ;
191 v_activity_sql Varchar2(1000) ;
192 v_activity_rec Activity_Select_Rec ;
193 Begin
194
195 v_activity_sql :=
196 'Select
197 JIA.interaction_id ,
198 JIA.activity_id ,
199 JIA.action_id ,
200 JIA.outcome_id ,
201 JIA.reason_id ,
202 JIA.result_id ,
203 JIA.duration ,
204 JIA.doc_id ,
205 JIA.doc_ref ,
206 JIA.object_id ,
207 JIA.object_type ,
208 JIA.source_code_id ,
209 JIA.source_code
210 From JTF_IH_ACTIVITIES JIA
211 Where JIA.Interaction_id = :interaction_id' ;
212
213 OPEN v_activity_cur FOR v_activity_sql USING p_interaction_id ;
214
215 LOOP
216 FETCH v_activity_cur INTO v_activity_rec ;
217 EXIT WHEN v_activity_cur%NOTFOUND ;
218
219 p_interaction_activity_tbl(l_cnt).Interaction_id
220 := v_activity_rec.Interaction_id ;
221
222 p_interaction_activity_tbl(l_cnt).Action_Id
223 := v_activity_rec.Action_Id ;
224 If v_activity_rec.action_id is NOT NULL then
225 Get_Action_Description(v_activity_rec.action_id ,
226 p_interaction_activity_tbl(l_cnt).Action_Description ) ;
227 End If ;
228
229 p_interaction_activity_tbl(l_cnt).Outcome_Id
230 := v_activity_rec.outcome_id ;
231 If v_activity_rec.outcome_id is NOT NULL then
232 Get_Outcome_Description (v_activity_rec.Outcome_id ,
233 p_interaction_activity_tbl(l_cnt).Outcome_Description ) ;
234 End If ;
235
236 p_interaction_activity_tbl(l_cnt).Reason_Id
237 := v_activity_rec.reason_id ;
238 If v_activity_rec.Reason_id is NOT NULL then
239 Get_Reason_Description(v_activity_rec.Reason_id ,
240 p_interaction_activity_tbl(l_cnt).Reason_Description ) ;
241 End If ;
242
243 p_interaction_activity_tbl(l_cnt).Result_Id
244 := v_activity_rec.result_id ;
245 If v_activity_rec.Result_id is NOT NULL then
246 Get_Result_Description(v_activity_rec.Result_id ,
247 p_Interaction_activity_tbl(l_cnt).Result_Description ) ;
248 End If ;
249
250 p_interaction_activity_tbl(l_cnt).Duration
251 := v_activity_rec.Duration ;
252
253 p_interaction_activity_tbl(l_cnt).doc_id
254 := v_activity_rec.doc_id ;
255
256 p_interaction_activity_tbl(l_cnt).doc_ref
257 := v_activity_rec.doc_ref ;
258
259 p_interaction_activity_tbl(l_cnt).object_id
260 := v_activity_rec.object_id ;
261
262 p_interaction_activity_tbl(l_cnt).Object_type
263 := v_activity_rec.Object_type ;
264
265 p_interaction_activity_tbl(l_cnt).Source_Code_Id
266 := v_activity_rec.Source_Code_Id ;
267
268 p_interaction_activity_tbl(l_cnt).Source_Code_Type
269 := v_activity_rec.Source_Code_Type ;
270
271 -- Populate Object Name and Description
272 If v_activity_Rec.doc_ref IS NOT NULL then
273 get_object_name(v_activity_rec.doc_ref,
274 p_interaction_activity_tbl(l_cnt).Object_Name) ;
275 End IF ;
276
277
278 l_cnt := l_cnt + 1 ;
279
280 END LOOP ;
281 CLOSE V_Activity_Cur ;
282 Exception
283 WHEN OTHERS THEN
284 p_error_code := SQLCODE ;
285 End ;
286 ---------------------------------------------------------------------
287 -- GET_ACTIVITY_STATUS
288 ---------------------------------------------------------------------
289 -- Takes Interaction_id and doc_Ref as parameters and returns the
290 -- Activity Count
291 ---------------------------------------------------------------------
292 Procedure Get_Activity_Status
293 (p_Interaction_id IN Number,
294 p_doc_ref IN Varchar2,
295 p_Activity_Exists OUT NOCOPY Varchar2)
296 IS
297 v_sql Varchar2(1000) ;
298 v_ret Number ;
299 Begin
300 v_sql :=
301 'Select Count(*)
302 from jtf_ih_activities
303 where Interaction_id = :Interaction_Id
304 AND doc_ref = :doc_ref' ;
305
306 EXECUTE IMMEDIATE v_sql
307 INTO v_ret
308 USING p_Interaction_id, p_doc_ref ;
309
310 If v_ret >= 1 then
311 p_activity_exists := 'Yes' ;
312 Else
313 p_activity_exists := 'No' ;
314 End If ;
315
316 EXCEPTION
317 When others then
318 p_Activity_Exists :=substr(to_char(sqlcode) || sqlerrm, 1, 120) ;
319 END Get_Activity_Status ;
320
321 ---------------------------------------------------------------------
322 -- GET_ACTION_DESCRIPTION
323 ---------------------------------------------------------------------
324 -- Takes Action_id as parameter and returns the corresponding
325 -- description from JTF_IH_ACTIONS_VL view
326 ---------------------------------------------------------------------
327 Procedure Get_Action_Description
328 (p_Action_id IN Number,
329 p_Action_Desc OUT NOCOPY JTF_IH_ACTIONS_VL.Short_Description%TYPE)
330 IS
331 v_sql Varchar2(100) ;
332 Begin
333 v_sql :=
334 'Select Short_Description
335 from JTF_IH_ACTIONS_VL
336 where Action_id = :Action_id' ;
337
338 EXECUTE IMMEDIATE v_sql
339 INTO p_action_desc
340 USING p_action_id ;
341 EXCEPTION
342 When NO_DATA_FOUND then
343 p_action_desc := null ;
344 When others then
345 p_Action_desc := substr(sqlcode || sqlerrm, 1, 120) ;
346 END Get_Action_Description ;
347
348
349 ---------------------------------------------------------------------
350 -- GET_OBJECT_NAME
351 ---------------------------------------------------------------------
352 -- Takes Object Code as parameter and returns the corresponding
353 -- description from JTF_OBJECTS_VL view
354 ---------------------------------------------------------------------
355 Procedure Get_Object_Name
356 (p_doc_ref IN Varchar2,
357 p_object_name OUT NOCOPY JTF_OBJECTS_VL.name%TYPE)
358 IS
359 v_sql Varchar2(200) ;
360 Begin
361 v_sql :=
362 'Select Name
363 from JTF_Objects_vl
364 where object_code = :p_doc_ref' ;
365
366 EXECUTE IMMEDIATE v_sql
367 INTO p_object_name
368 USING p_doc_ref ;
369 EXCEPTION
370 When NO_DATA_FOUND then
371 p_object_name := null ;
372 When others then
373 p_object_name := substr(sqlcode || sqlerrm, 1, 120) ;
374 END Get_Object_Name ;
375 ---------------------------------------------------------------------
376 -- GET_Outcome_DESCRIPTION
377 ---------------------------------------------------------------------
378 -- Takes Action_id as parameter and returns the corresponding
379 -- description from JTF_IH_ACTIONS_VL view
380 ---------------------------------------------------------------------
381 Procedure Get_OUTCOME_Description
382 (p_Outcome_Id IN Number,
383 p_Outcome_Desc OUT NOCOPY JTF_IH_OUTCOMES_VL.Short_Description%TYPE)
384 IS
385 v_sql Varchar2(100) ;
386 Begin
387 v_sql :=
388 'Select Short_Description
389 from JTF_IH_OUTCOMES_VL
390 where Outcome_Id = :Outcome_Id' ;
391
392 EXECUTE IMMEDIATE v_sql
393 INTO p_outcome_desc
394 USING p_outcome_id ;
395 EXCEPTION
396 When NO_DATA_FOUND then
397 p_outcome_desc := null ;
398 When others then
399 p_outcome_desc:= substr(sqlcode || sqlerrm, 1, 120) ;
400 END GET_OUTCOME_DESCRIPTION ;
401
402
403
404 ---------------------------------------------------------------------
405 -- GET_REASON_DESCRIPTION
406 ---------------------------------------------------------------------
407 -- Takes Action_id as parameter and returns the corresponding
408 -- description from JTF_IH_REASONS_VL view
409 ---------------------------------------------------------------------
410 Procedure Get_REASON_Description
411 (p_REASON_id IN Number,
412 p_REASON_Desc OUT NOCOPY JTF_IH_REASONS_VL.Short_Description%TYPE)
413 IS
414 v_sql Varchar2(100) ;
415 Begin
416 v_sql :=
417 'Select Short_Description
418 from JTF_IH_REASONS_VL
419 where REASON_id = :REASON_id' ;
420
421 EXECUTE IMMEDIATE v_sql
422 INTO p_reason_desc
423 USING p_reason_id ;
424 EXCEPTION
425 When NO_DATA_FOUND then
426 p_reason_desc := null ;
427 When others then
428 p_REASON_desc := substr(sqlcode || sqlerrm, 1, 120) ;
429 END GET_REASON_DESCRIPTION ;
430
431 ---------------------------------------------------------------------
432 -- GET_RESULT_DESCRIPTION
433 ---------------------------------------------------------------------
434 -- Takes result_id as parameter and returns the corresponding
435 -- description from JTF_IH_RESULTS_VL view
436 ---------------------------------------------------------------------
437 Procedure Get_result_description
438 (p_result_id IN Number,
439 p_result_Desc OUT NOCOPY JTF_IH_RESULTS_VL.Short_Description%TYPE)
440 IS
441 v_sql Varchar2(100) ;
442 Begin
443 v_sql :=
444 'Select Short_Description
445 from JTF_IH_RESULTS_VL
446 where result_id = :result_id' ;
447
448 EXECUTE IMMEDIATE v_sql
449 INTO p_result_desc
450 USING p_result_id ;
451 EXCEPTION
452 When NO_DATA_FOUND then
453 p_result_desc := null ;
454 When others then
455 p_result_desc := substr(sqlcode || sqlerrm, 1, 120) ;
456 END GET_RESULT_DESCRIPTION ;
457
458
459 End IEX_CALL_HISTORY ;