[Home] [Help]
PACKAGE BODY: APPS.IEU_UWQ_TASK_INFO_PKG
Source
1 package body ieu_uwq_task_info_pkg AS
2 /* $Header: IEUTAINB.pls 120.2 2012/01/02 05:56:25 rgandhi ship $ */
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 ' *** ' || fnd_date.date_to_charDT(c2_rec.creation_date) || ' *** ' || '
185 ' || ' *** ' || c2_rec.USER_NAME || ' *** ' || '
186 ' || ' *** ' || c2_rec.note_type_meaning || ' *** ' || '
187 ' || c2_rec.notes;
188 x_notes_data_list.EXTEND;
189 --prauto(l_curr_rec); Bug 13546867
190 x_notes_data_list(x_notes_data_list.LAST) := SYSTEM.APP_INFO_HEADER_OBJ( l_curr_rec);
191 -- prauto(c2_rec.creation_date);Bug 13546867
192 --prauto(FND_DATE.STRING_TO_DATE(c2_rec.creation_date,'DD-MON-RRRR HH24:MI:SS'));Bug 13546867
193 --prauto(x_notes_data_list(x_notes_data_list.LAST));
194 l_ctr := l_ctr + 1;
195 END LOOP;
196 elsif p_action_key in ('ieu_uwq_prof_cust_notes') then
197 l_object_code := 'PARTY%';
198 for c2_rec in C_Customer_Notes(l_fnd_user_id,
199 l_object_id,
200 l_object_code,
201 l_from_date)
202 LOOP
203
204 l_curr_rec :=
205 ' *** ' || fnd_date.date_to_charDT(c2_rec.creation_date) || ' *** ' || '
206 ' || ' *** ' || c2_rec.USER_NAME || ' *** ' || '
207 ' || ' *** ' || c2_rec.note_type_meaning || ' *** ' || '
208 ' || c2_rec.notes;
209
210 x_notes_data_list.EXTEND;
211 --prauto(l_curr_rec);Bug 13546867
212 x_notes_data_list(x_notes_data_list.LAST) := SYSTEM.APP_INFO_HEADER_OBJ( l_curr_rec);
213 -- prauto(x_notes_data_list(x_notes_data_list.LAST));
214 l_ctr := l_ctr + 1;
215 END LOOP;
216 elsif p_action_key in ('ieu_uwq_prof_cont_notes') then
217 l_object_code := 'PARTY%';
218 OPEN C_note_context_id(l_object_id);
219 FETCH C_note_context_id INTO l_note_context_type_id;
220 CLOSE C_note_context_id;
221 for c2_rec in C_Contact_Notes(l_fnd_user_id,
222 l_object_id,
223 l_object_code,
224 l_note_context_type_id,
225 l_from_date)
226 LOOP
227 l_curr_rec :=
228 ' *** ' || fnd_date.date_to_charDT(c2_rec.creation_date) || ' *** ' || '
229 ' || ' *** ' || c2_rec.USER_NAME || ' *** ' || '
230 ' || ' *** ' || c2_rec.note_type_meaning || ' *** ' || '
231 ' || c2_rec.notes;
232 x_notes_data_list.EXTEND;
233 --prauto(l_curr_rec);Bug 13546867
234 x_notes_data_list(x_notes_data_list.LAST) := SYSTEM.APP_INFO_HEADER_OBJ( l_curr_rec);
235 l_ctr := l_ctr + 1;
236 -- prauto(x_notes_data_list(x_notes_data_list.LAST));Bug 13546867
237 END LOOP;
238 end if;
239 end if;
240
241
242 x_return_status :=fnd_api.g_ret_sts_success;
243
244 fnd_msg_pub.Count_And_Get(p_count => x_msg_count,
245 p_data => x_msg_data);
246
247 EXCEPTION
248
249 when fnd_api.g_exc_error then
250 x_return_status:=fnd_api.g_ret_sts_error;
251
252 when fnd_api.g_exc_unexpected_error then
253 x_return_status:=fnd_api.g_ret_sts_unexp_error;
254
255 when others then
256 x_return_status:=fnd_api.g_ret_sts_unexp_error;
257
258 fnd_msg_pub.Count_And_Get(p_count => x_msg_count,
259 p_data => x_msg_data);
260
261
262
263 end ieu_uwq_task_notes;
264 END ieu_uwq_task_info_pkg;