[Home] [Help]
PACKAGE BODY: APPS.AST_UWQ_MLIST_INFO_PKG
Source
1 package body ast_uwq_mlist_info_pkg AS
2 /* $Header: astuminb.pls 120.3 2006/01/12 02:08:12 rkumares ship $ */
3
4 --Purpose: This package will be used for displaying information within the work panel
5 --Created by: Sekar Sundaram dated 5/16/02
6 --Last Updated by: Joseph Raj dated 6/08/02
7 -- changed data to header type..
8 --Derived from astnoteb.pls
9
10 procedure ast_uwq_mlist_notes (
11 p_resource_id IN NUMBER,
12 p_language IN VARCHAR2,
13 p_source_lang IN VARCHAR2,
14 p_action_key IN VARCHAR2,
15 p_workitem_data_list IN SYSTEM.ACTION_INPUT_DATA_NST default null,
16 x_notes_data_list OUT NOCOPY SYSTEM.app_info_header_nst,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 x_return_status OUT NOCOPY VARCHAR2
20 ) IS
21
22 l_ctr binary_integer;
23 l_name varchar2(500);
24 l_value varchar2(2000);
25 l_party_id number;
26 l_customer_id number;
27 l_contact_party_id number;
28 l_party_type varchar2(100);
29 l_source_type varchar2(100);
30
31 l_notes VARCHAR2(2000);
32
33 l_curr_rec VARCHAR2(3000);
34 l_new_line VARCHAR2(30);
35
36 l_fnd_user_id NUMBER;
37 l_object_code VARCHAR2(30);
38 l_object_id NUMBER;
39
40 l_months number;
41 l_from_date date;
42 l_cur_ind VARCHAR2(10);
43
44 --Code added for BugFix#4451689 --Start
45 x_client_time date;
46
47 l_client_tz_id number;
48 l_server_tz_id number;
49 --Code added for BugFix#4451689 --End
50
51
52 CURSOR C_note_details_desc(p_fnd_user_id NUMBER,
53 p_object_code VARCHAR2,
54 p_object_id NUMBER) is
55 SELECT a.notes,
56 a.created_by_name,
57 a.entered_date, --modified with a.entered_date instead of a.creation_date for bug#4177915
58 a.note_type_meaning,
59 a.note_status_meaning,
60 a.source_object_id,
61 a.source_object_code,
62 b.select_id,
63 b.select_name,
64 b.select_details,
65 b.from_table,
66 b.where_clause,
67 tl.name,
68 a.notes_detail_size
69 FROM ast_notes_bali_vl a,
70 jtf_objects_b b,
71 jtf_objects_tl tl
72 WHERE (a.note_status <> 'P' or a.created_by = p_fnd_user_id)
73 and a.object_code like p_object_code
74 and a.object_id = p_object_id
75 and a.source_object_code = b.object_code
76 and b.object_code = tl.object_code
77 and tl.language = userenv('LANG')
78 order by a.entered_date desc; --modified with a.entered_date instead of a.creation_date for bug#4177915
79
80
81 cursor C_note_details_months(p_fnd_user_id NUMBER,
82 p_object_code VARCHAR2,
83 p_object_id NUMBER,
84 p_from_date date) is
85 SELECT a.notes,
86 a.created_by_name,
87 a.entered_date, --modified with a.entered_date instead of a.creation_date for bug#4177915
88 a.note_type_meaning,
89 a.note_status_meaning,
90 a.source_object_id,
91 a.source_object_code,
92 b.select_id,
93 b.select_name,
94 b.select_details,
95 b.from_table,
96 b.where_clause,
97 tl.name,
98 a.notes_detail_size
99 FROM ast_notes_bali_vl a,
100 jtf_objects_b b,
101 jtf_objects_tl tl
102 WHERE (a.note_status <> 'P' or a.created_by = p_fnd_user_id)
103 and a.object_code like p_object_code
104 and a.object_id = p_object_id
105 and a.source_object_code = b.object_code
106 and b.object_code = tl.object_code
107 and tl.language = userenv('LANG')
108 and trunc(a.creation_date) between trunc(p_from_date) and trunc(sysdate)
109 order by a.entered_date desc; --modified with a.entered_date instead of a.creation_date for bug#4177915
110
111 CURSOR c_party_type(p_party_id in number) IS
112 SELECT party_type
113 FROM
114 hz_parties
115 WHERE party_id = p_party_id;
116
117 BEGIN
118
119 x_notes_data_list := SYSTEM.app_info_header_nst();
120
121 l_new_line := ' ';
122 l_curr_rec := null;
123
124 FOR I IN 1.. p_workitem_data_list.COUNT LOOP
125 l_name := p_workitem_data_list(i).name;
126 l_value := p_workitem_data_list(i).value;
127
128 ------ Get field name and value of your records ------
129
130 if l_name = 'CUSTOMER_ID' then
131 l_customer_id := l_value ;
132 elsif l_name = 'PARTY_ID' then
133 l_party_id := l_value ;
134 elsif l_name = 'LIST_SOURCE_TYPE' then
135 l_source_type := l_value ;
136 end if;
137 END LOOP;
138
139
140 l_fnd_user_id := fnd_profile.value('USER_ID');
141
142
143
144 l_curr_rec := null;
145 l_ctr := 1;
146
147
148 l_object_code := 'PARTY%';
149 if p_action_key in ('astuminb_cust_notes', 'astuminb_prof_cust_notes', 'astuminb_cust_notes_man', 'astuminb_prof_cust_notes_man') then -- begin of main "if"
150
151 if l_customer_id is not null then
152 l_object_id := l_customer_id;
153 elsif l_party_id is not null then
154 l_object_id := l_party_id;
155 else
156 return;
157 end if;
158
159 elsif p_action_key in ('astuminb_cont_notes', 'astuminb_prof_cont_notes', 'astuminb_cont_notes_man', 'astuminb_prof_cont_notes_man') then -- begin of main "if"
160
161 if l_source_type in ('PERSON_LIST', 'CONSUMER') then
165 l_object_id := l_party_id;
162 return;
163 end if;
164
166
167 if l_party_id is not null then
168 for c1_rec in c_party_type(l_party_id)
169 LOOP
170 l_party_type := c1_rec.party_type;
171 END LOOP;
172 end if;
173 if l_party_type = 'PARTY_RELATIONSHIP' then
174 l_party_id := l_object_id;
175 end if;
176
177 end if;
178
179 if p_action_key in ('astuminb_cust_notes','astuminb_cont_notes','astuminb_cust_notes_man','astuminb_cont_notes_man') then
180 l_cur_ind := 'DESC' ;
181
182 elsif p_action_key in ('astuminb_prof_cust_notes','astuminb_prof_cont_notes','astuminb_prof_cust_notes_man','astuminb_prof_cont_notes_man') then
183 l_months := nvl(FND_PROFILE.VALUE('AST_DEFAULT_MONTHS_TO_VIEW'), 1);
184 l_from_date := add_months(sysdate, -1 * l_months );
185 l_cur_ind := 'MON' ;
186 end if;
187
188 -----Notes in descending order ( latest to the oldest )
189 if l_object_id is not null and l_cur_ind = 'DESC' then
190 for c2_rec in C_note_details_desc (l_fnd_user_id,
191 l_object_code,
192 l_object_id)
193 LOOP
194 --Code added for BugFix#4451689 --Start
195 if fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS')='Y' THEN
196 l_client_tz_id := to_number ( fnd_profile.value('CLIENT_TIMEZONE_ID'));
197 l_server_tz_id := to_number( fnd_profile.value('SERVER_TIMEZONE_ID'));
198 --modified with c2_rec.entered_date instead of c2_rec.creation_date for bug#4177915
199 c2_rec.entered_date:=HZ_TIMEZONE_PUB.Convert_DateTime(l_server_tz_id,l_client_tz_id,c2_rec.entered_date);
200
201 end if;
202 --Code added for BugFix#4451689 --Start
203
204
205 --modified with c2_rec.entered_date instead of c2_rec.creation_date for bug#4177915
206 l_curr_rec := ' *** ' || to_char(c2_rec.entered_date,'DD-MON-RRRR HH24:MI:SS') || ' *** ' || '
207 ' || ' *** ' || c2_rec.created_by_name || ' *** ' || '
208 ' || ' *** ' || c2_rec.note_type_meaning || ' *** ' || '
209 ' || c2_rec.notes;
210
211 if nvl(c2_rec.notes_detail_size,0) > 0 then
212 l_curr_rec := l_curr_rec || ' <...>';
213 end if;
214
215 x_notes_data_list.EXTEND;
216 x_notes_data_list(x_notes_data_list.LAST) := SYSTEM.APP_INFO_HEADER_OBJ( l_curr_rec);
217 l_ctr := l_ctr + 1;
218 END LOOP;
219
220 elsif l_object_id is not null and l_cur_ind = 'MON' then
221
222 for c2_rec in C_note_details_months (l_fnd_user_id,
223 l_object_code,
224 l_object_id, l_from_date)
225 LOOP
226 --Code added for BugFix#4451689 --Start
227 if fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS')='Y' THEN
228 l_client_tz_id := to_number ( fnd_profile.value('CLIENT_TIMEZONE_ID'));
229 l_server_tz_id := to_number( fnd_profile.value('SERVER_TIMEZONE_ID'));
230 --modified with c2_rec.entered_date instead of c2_rec.creation_date for bug#4177915
231 c2_rec.entered_date:=HZ_TIMEZONE_PUB.Convert_DateTime(l_server_tz_id,l_client_tz_id,c2_rec.entered_date);
232
233 end if;
234 --Code added for BugFix#4451689 --Start
235
236
237 --modified with c2_rec.entered_date instead of c2_rec.creation_date for bug#4177915
238 l_curr_rec := ' *** ' || to_char(c2_rec.entered_date,'DD-MON-RRRR HH24:MI:SS') || ' *** ' || '
239 ' || ' *** ' || c2_rec.created_by_name || ' *** ' || '
240 ' || ' *** ' || c2_rec.note_type_meaning || ' *** ' || '
241 ' || c2_rec.notes;
242
243 if nvl(c2_rec.notes_detail_size,0) > 0 then
244 l_curr_rec := l_curr_rec || ' <...>';
245 end if;
246
247 x_notes_data_list.EXTEND;
248 x_notes_data_list(x_notes_data_list.LAST) := SYSTEM.APP_INFO_HEADER_OBJ( l_curr_rec);
249 l_ctr := l_ctr + 1;
250 END LOOP;
251 end if;
252
253
254 x_return_status :=fnd_api.g_ret_sts_success;
255
256 fnd_msg_pub.Count_And_Get(p_count => x_msg_count,
257 p_data => x_msg_data);
258
259 EXCEPTION
260
261 when fnd_api.g_exc_error then
262 x_return_status:=fnd_api.g_ret_sts_error;
263
264 when fnd_api.g_exc_unexpected_error then
265 x_return_status:=fnd_api.g_ret_sts_unexp_error;
266
267 when others then
268 x_return_status:=fnd_api.g_ret_sts_unexp_error;
269
270 fnd_msg_pub.Count_And_Get(p_count => x_msg_count,
271 p_data => x_msg_data);
272
273
274
275 end ast_uwq_mlist_notes;
276 END ast_uwq_mlist_info_pkg;