[Home] [Help]
PACKAGE BODY: APPS.IEU_UWQ_TASK_INFO_PKG
Source
1 package body ieu_uwq_task_info_pkg AS
2 /* $Header: IEUTAINB.pls 115.14 2003/11/25 18:59:43 dolee noship $ */
3
4 --Purpose: This package will be used for displaying information within the work panel
5 --Created by: Don-May Lee dated 12/9/02
6 -- changed data to header type..
7
8 procedure ieu_uwq_task_notes (
9 p_resource_id IN NUMBER,
10 p_language IN VARCHAR2,
11 p_source_lang IN VARCHAR2,
12 p_action_key IN VARCHAR2,
13 p_workitem_data_list IN SYSTEM.ACTION_INPUT_DATA_NST default null,
14 x_notes_data_list OUT NOCOPY SYSTEM.app_info_header_nst,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2,
17 x_return_status OUT NOCOPY VARCHAR2
18 ) IS
19
20 l_ctr binary_integer;
21 l_name varchar2(500);
22 l_value varchar2(1996);
23 l_party_id number;
24 l_customer_id number;
25 l_task_id number;
26 l_note_context_type_id number;
27 l_party_type varchar2(100);
28 l_source_type varchar2(100);
29
30 l_notes VARCHAR2(2000);
31
32 l_curr_rec VARCHAR2(3000);
33 l_new_line VARCHAR2(30);
34
35 l_fnd_user_id NUMBER;
36 l_object_code VARCHAR2(30);
37 l_object_id NUMBER;
38
39 l_months number;
40 l_from_date date;
41 l_cur_ind VARCHAR2(10);
42 l_count number :=0;
43
44
45 cursor C_note_details_months(p_fnd_user_id NUMBER,
46 p_object_id NUMBER,
47 p_object_code varchar2,
48 p_from_date date) is
49 select notes, source_name USER_NAME, creation_date, note_type_meaning
50 from (
51 SELECT b.rowid ,b.jtf_note_id ,b.creation_date ,
52 b.created_by ,b.last_update_date ,b.last_updated_by ,
53 b.last_update_login ,tl.notes ,
54 b.entered_by ,b.entered_date ,b.source_object_id ,b.source_object_code ,
55 c.note_context_type_id ,c.note_context_type ,b.note_status ,fnd_status.meaning ,
56 res.source_name ,b.note_type ,fnd_type.meaning note_type_meaning
57 FROM jtf_notes_b b, jtf_notes_tl tl, jtf_note_contexts c, fnd_lookups fnd_status,
58 fnd_lookups fnd_type, jtf_rs_resource_extns res
59 WHERE b.jtf_note_id = tl.jtf_note_id and tl.language = userenv('LANG')
60 and b.jtf_note_id = c.jtf_note_id and b.note_type = fnd_type.lookup_code(+)
61 and fnd_type.lookup_type(+) = 'JTF_NOTE_TYPE'
62 and b.note_status = fnd_status.lookup_code(+)
63 and fnd_status.lookup_type(+) = 'JTF_NOTE_STATUS'
64 and res.user_id(+) = b.created_by
65 )
66 where NOTE_CONTEXT_TYPE = p_object_code
67 and NOTE_CONTEXT_TYPE_ID = p_object_id
68 and creation_date > p_from_date
69 order by creation_date desc;
70
71 cursor C_Customer_Notes(p_fnd_user_id NUMBER,
72 p_object_id NUMBER,
73 p_object_code varchar2,
74 p_from_date date) is
75 select notes, source_name USER_NAME, creation_date, note_type_meaning
76 from (
77 SELECT b.rowid ,b.jtf_note_id ,b.creation_date ,
78 b.created_by ,b.last_update_date ,b.last_updated_by ,
79 b.last_update_login ,tl.notes ,
80 b.entered_by ,b.entered_date ,b.source_object_id ,b.source_object_code ,
81 c.note_context_type_id ,c.note_context_type ,b.note_status ,fnd_status.meaning ,
82 res.source_name ,b.note_type ,fnd_type.meaning note_type_meaning
83 FROM jtf_notes_b b, jtf_notes_tl tl, jtf_note_contexts c, fnd_lookups fnd_status,
84 fnd_lookups fnd_type, jtf_rs_resource_extns res
85 WHERE b.jtf_note_id = tl.jtf_note_id and tl.language = userenv('LANG')
86 and b.jtf_note_id = c.jtf_note_id and b.note_type = fnd_type.lookup_code(+)
87 and fnd_type.lookup_type(+) = 'JTF_NOTE_TYPE'
88 and b.note_status = fnd_status.lookup_code(+)
89 and fnd_status.lookup_type(+) = 'JTF_NOTE_STATUS'
90 and res.user_id(+) = b.created_by
91 )
92 where NOTE_CONTEXT_TYPE LIKE p_object_code
93 and NOTE_CONTEXT_TYPE_ID in (select customer_id
94 from jtf_tasks_b
95 where task_id= p_object_id
96 )
97 and creation_date > p_from_date
98 order by creation_date desc;
99
100 cursor C_Contact_Notes(p_fnd_user_id NUMBER,
101 p_object_id NUMBER,
102 p_object_code varchar2,
103 p_context_type_id number,
104 p_from_date date) is
105 select notes, source_name USER_NAME, creation_date, note_type_meaning
106 from (
107 SELECT b.rowid ,b.jtf_note_id ,b.creation_date ,
108 b.created_by ,b.last_update_date ,b.last_updated_by ,
109 b.last_update_login ,tl.notes ,
110 b.entered_by ,b.entered_date ,b.source_object_id ,b.source_object_code ,
111 c.note_context_type_id ,c.note_context_type ,b.note_status ,fnd_status.meaning ,
112 res.source_name ,b.note_type ,fnd_type.meaning note_type_meaning
113 FROM jtf_notes_b b, jtf_notes_tl tl, jtf_note_contexts c, fnd_lookups fnd_status,
114 fnd_lookups fnd_type, jtf_rs_resource_extns res
115 WHERE b.jtf_note_id = tl.jtf_note_id and tl.language = userenv('LANG')
116 and b.jtf_note_id = c.jtf_note_id and b.note_type = fnd_type.lookup_code(+)
117 and fnd_type.lookup_type(+) = 'JTF_NOTE_TYPE'
118 and b.note_status = fnd_status.lookup_code(+)
119 and fnd_status.lookup_type(+) = 'JTF_NOTE_STATUS'
120 and res.user_id(+) = b.created_by
121 )
122 where NOTE_CONTEXT_TYPE like p_object_code
123 and NOTE_CONTEXT_TYPE_ID = p_context_type_id
124 and creation_date > p_from_date
125 order by creation_date desc;
126
127 cursor C_note_context_id(p_object_id NUMBER) is
128 select contact_id
129 from jtf_task_contacts
130 where task_id = p_object_id
131 and (primary_flag is null or primary_flag = 'Y')
132 order by primary_flag;
133
134 BEGIN
135
136 x_notes_data_list := SYSTEM.app_info_header_nst();
137
138 l_new_line := ' ';
139 l_curr_rec := null;
140 l_object_code := 'TASK';
141 FOR I IN 1.. p_workitem_data_list.COUNT LOOP
142 l_name := p_workitem_data_list(i).name;
143 l_value := p_workitem_data_list(i).value;
144
145 ------ Get field name and value of your records ------
146
147 if l_name = 'TASK_ID' then
148 l_task_id := l_value ;
149 end if;
150 END LOOP;
151
152
153 l_fnd_user_id := fnd_profile.value('USER_ID');
154 l_curr_rec := null;
155 l_ctr := 1;
156
157
158
159 if p_action_key in ('ieu_uwq_prof_task_notes','ieu_uwq_prof_cust_notes', 'ieu_uwq_prof_cont_notes') then -- begin of main "if"
160
161 if l_task_id is not null then
162 l_object_id := l_task_id;
163 l_months := nvl(FND_PROFILE.VALUE('IEU_DEFAULT_NOTE_MONTHS'), 1);
164 l_from_date := add_months(sysdate, -1 * l_months );
165 l_cur_ind := 'MON' ;
166 else
167 return;
168 end if;
169
170 end if;
171
172
173 if l_object_id is not null and l_cur_ind = 'MON' then
174 if p_action_key in ('ieu_uwq_prof_task_notes') then
175 l_object_code := 'TASK';
176 for c2_rec in C_note_details_months (l_fnd_user_id,
177 l_object_id,
178 l_object_code,
179 l_from_date)
180 LOOP
181
182
183 l_curr_rec :=
184 ' *** ' || to_char(c2_rec.creation_date,'DD-MON-RRRR HH:MI:SS') || ' *** ' || '
185 ' || ' *** ' || c2_rec.USER_NAME || ' *** ' || '
186 ' || ' *** ' || c2_rec.note_type_meaning || ' *** ' || '
187 ' || c2_rec.notes;
188 x_notes_data_list.EXTEND;
189 x_notes_data_list(x_notes_data_list.LAST) := SYSTEM.APP_INFO_HEADER_OBJ( l_curr_rec);
190 l_ctr := l_ctr + 1;
191 END LOOP;
192 elsif p_action_key in ('ieu_uwq_prof_cust_notes') then
193 l_object_code := 'PARTY%';
194 for c2_rec in C_Customer_Notes(l_fnd_user_id,
195 l_object_id,
196 l_object_code,
197 l_from_date)
198 LOOP
199
200 l_curr_rec :=
201 ' *** ' || to_char(c2_rec.creation_date,'DD-MON-RRRR HH:MI:SS') || ' *** ' || '
202 ' || ' *** ' || c2_rec.USER_NAME || ' *** ' || '
203 ' || ' *** ' || c2_rec.note_type_meaning || ' *** ' || '
204 ' || c2_rec.notes;
205 x_notes_data_list.EXTEND;
206 x_notes_data_list(x_notes_data_list.LAST) := SYSTEM.APP_INFO_HEADER_OBJ( l_curr_rec);
207 l_ctr := l_ctr + 1;
208 END LOOP;
209 elsif p_action_key in ('ieu_uwq_prof_cont_notes') then
210 l_object_code := 'PARTY%';
211 OPEN C_note_context_id(l_object_id);
212 FETCH C_note_context_id INTO l_note_context_type_id;
213 CLOSE C_note_context_id;
214 for c2_rec in C_Contact_Notes(l_fnd_user_id,
215 l_object_id,
216 l_object_code,
217 l_note_context_type_id,
218 l_from_date)
219 LOOP
220 l_curr_rec :=
221 ' *** ' || to_char(c2_rec.creation_date,'DD-MON-RRRR HH:MI:SS') || ' *** ' || '
222 ' || ' *** ' || c2_rec.USER_NAME || ' *** ' || '
223 ' || ' *** ' || c2_rec.note_type_meaning || ' *** ' || '
224 ' || c2_rec.notes;
225 x_notes_data_list.EXTEND;
226 x_notes_data_list(x_notes_data_list.LAST) := SYSTEM.APP_INFO_HEADER_OBJ( l_curr_rec);
227 l_ctr := l_ctr + 1;
228 END LOOP;
229 end if;
230 end if;
231
232
233 x_return_status :=fnd_api.g_ret_sts_success;
234
235 fnd_msg_pub.Count_And_Get(p_count => x_msg_count,
236 p_data => x_msg_data);
237
238 EXCEPTION
239
240 when fnd_api.g_exc_error then
241 x_return_status:=fnd_api.g_ret_sts_error;
242
243 when fnd_api.g_exc_unexpected_error then
244 x_return_status:=fnd_api.g_ret_sts_unexp_error;
245
246 when others then
247 x_return_status:=fnd_api.g_ret_sts_unexp_error;
248
249 fnd_msg_pub.Count_And_Get(p_count => x_msg_count,
250 p_data => x_msg_data);
251
252
253
254 end ieu_uwq_task_notes;
255 END ieu_uwq_task_info_pkg;