[Home] [Help]
PACKAGE BODY: APPS.AST_UWQ_LLIST_INFO_PKG
Source
4 --Purpose: This package will be used for displaying information within the work panel
1 package body ast_uwq_llist_info_pkg AS
2 /* $Header: astulinb.pls 120.3 2006/01/12 02:07:52 rkumares ship $ */
3
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_llist_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,
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
24 l_name varchar2(500);
25 l_value varchar2(2000);
26 l_party_id number;
27 l_contact_party_id number;
28 l_party_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_lead_id NUMBER;
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 CURSOR C_note_details_desc(p_fnd_user_id NUMBER,
52 p_object_code VARCHAR2,
53 p_object_id NUMBER) is
54 SELECT a.notes,
55 a.created_by_name,
56 a.entered_date, --modified with a.entered_date instead of a.creation_date for bug#4177915
57 a.note_type_meaning,
58 a.note_status_meaning,
59 a.source_object_id,
60 a.source_object_code,
61 b.select_id,
62 b.select_name,
63 b.select_details,
64 b.from_table,
65 b.where_clause,
66 tl.name,
67 a.notes_detail_size
68 FROM ast_notes_bali_vl a,
69 jtf_objects_b b,
70 jtf_objects_tl tl
71 WHERE (a.note_status <> 'P' or a.created_by = p_fnd_user_id)
72 and a.object_code like p_object_code
73 and a.object_id = p_object_id
74 and a.source_object_code = b.object_code
75 and b.object_code = tl.object_code
76 and tl.language = userenv('LANG')
77 order by a.entered_date desc; --modified with a.entered_date instead of a.creation_date for bug#4177915
78
79
80 cursor C_note_details_months(p_fnd_user_id NUMBER,
81 p_object_code VARCHAR2,
82 p_object_id NUMBER,
83 p_from_date date) is
84 SELECT a.notes,
85 a.created_by_name,
86 a.entered_date, --modified with a.entered_date instead of a.creation_date for bug#4177915
87 a.note_type_meaning,
88 a.note_status_meaning,
89 a.source_object_id,
90 a.source_object_code,
91 b.select_id,
92 b.select_name,
93 b.select_details,
94 b.from_table,
95 b.where_clause,
96 tl.name,
97 a.notes_detail_size
98 FROM ast_notes_bali_vl a,
99 jtf_objects_b b,
100 jtf_objects_tl tl
101 WHERE (a.note_status <> 'P' or a.created_by = p_fnd_user_id)
102 and a.object_code like p_object_code
103 and a.object_id = p_object_id
104 and a.source_object_code = b.object_code
105 and b.object_code = tl.object_code
106 and tl.language = userenv('LANG')
107 and trunc(a.creation_date) between trunc(p_from_date) and trunc(sysdate)
108 order by a.entered_date desc; --modified with a.entered_date instead of a.creation_date for bug#4177915
109
110 BEGIN
111
112 x_notes_data_list := SYSTEM.app_info_header_nst();
113
114 l_new_line := ' ';
115 l_curr_rec := null;
116
117 FOR I IN 1.. p_workitem_data_list.COUNT LOOP
118 l_name := p_workitem_data_list(i).name;
119 l_value := p_workitem_data_list(i).value;
120
121 ------ Get field name and value of your records ------
122
123 if l_name = 'CONTACT_PARTY_ID' then
124 l_contact_party_id := l_value ;
125 elsif l_name = 'PARTY_ID' then
126 l_party_id := l_value ;
127 elsif l_name = 'SALES_LEAD_ID' then
128 l_lead_id := l_value ;
129 end if;
130 END LOOP;
131
132 l_fnd_user_id := fnd_profile.value('USER_ID');
133
134
135
136 l_curr_rec := null;
137 l_ctr := 1;
138
139 if p_action_key in ('astulinb_cust_notes', 'astulinb_prof_cust_notes') then -- begin of main "if"
140
141 l_object_id := l_party_id;
142 l_object_code := 'PARTY%';
143
144 elsif p_action_key in ('astulinb_cont_notes', 'astulinb_prof_cont_notes') then -- begin of main "if"
145
146 l_object_id := l_contact_party_id;
150
147 l_object_code := 'PARTY%';
148
149 elsif p_action_key in ('astulinb_lead_notes', 'astulinb_prof_lead_notes') then -- begin of main "if"
151 l_object_id := l_lead_id;
152 l_object_code := 'LEAD';
153
154 end if;
155
156 if p_action_key in ('astulinb_cust_notes','astulinb_cont_notes', 'astulinb_lead_notes') then
157 l_cur_ind := 'DESC' ;
158 elsif p_action_key in ('astulinb_prof_cust_notes','astulinb_prof_cont_notes', 'astulinb_prof_lead_notes') then
159 l_months := nvl(FND_PROFILE.VALUE('AST_DEFAULT_MONTHS_TO_VIEW'), 1);
160 l_from_date := add_months(sysdate, -1 * l_months );
161 l_cur_ind := 'MON' ;
162 end if;
163
164 -----Notes in descending order ( latest to the oldest )
165 if l_object_id is not null and l_cur_ind = 'DESC' then
166 for c2_rec in C_note_details_desc (l_fnd_user_id,
167 l_object_code,
168 l_object_id)
169 LOOP
170 --Code added for BugFix#4451689 --Start
171 if fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS')='Y' THEN
172 l_client_tz_id := to_number ( fnd_profile.value('CLIENT_TIMEZONE_ID'));
173 l_server_tz_id := to_number( fnd_profile.value('SERVER_TIMEZONE_ID'));
174 --modified with c2_rec.entered_date instead of c2_rec.creation_date for bug#4177915
175 c2_rec.entered_date:=HZ_TIMEZONE_PUB.Convert_DateTime(l_server_tz_id,l_client_tz_id,c2_rec.entered_date);
176
177 end if;
178 --Code added for BugFix#4451689 --Start
179
180 --modified with c2_rec.entered_date instead of c2_rec.creation_date for bug#4177915
181 l_curr_rec := ' *** ' || to_char(c2_rec.entered_date,'DD-MON-RRRR HH24:MI:SS') || ' *** ' || '
182 ' || ' *** ' || c2_rec.created_by_name || ' *** ' || '
183 ' || ' *** ' || c2_rec.note_type_meaning || ' *** ' || '
184 ' || c2_rec.notes;
185
186 if nvl(c2_rec.notes_detail_size,0) > 0 then
187 l_curr_rec := l_curr_rec || ' <...>';
188 end if;
189
190 x_notes_data_list.EXTEND;
191 x_notes_data_list(x_notes_data_list.LAST) := SYSTEM.APP_INFO_HEADER_OBJ( l_curr_rec);
192 l_ctr := l_ctr + 1;
193 END LOOP;
194
195 elsif l_object_id is not null and l_cur_ind = 'MON' then
196 for c2_rec in C_note_details_months (l_fnd_user_id,
197 l_object_code,
198 l_object_id, l_from_date)
199 LOOP
200 --Code added for BugFix#4451689 --Start
201 if fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS')='Y' THEN
202 l_client_tz_id := to_number ( fnd_profile.value('CLIENT_TIMEZONE_ID'));
203 l_server_tz_id := to_number( fnd_profile.value('SERVER_TIMEZONE_ID'));
204
205 --modified with c2_rec.entered_date instead of c2_rec.creation_date for bug#4177915
206 c2_rec.entered_date:=HZ_TIMEZONE_PUB.Convert_DateTime(l_server_tz_id,l_client_tz_id,c2_rec.entered_date);
207
208 end if;
209 --Code added for BugFix#4451689 --Start
210 --modified with c2_rec.entered_date instead of c2_rec.creation_date for bug#4177915
211 l_curr_rec := ' *** ' || to_char(c2_rec.entered_date,'DD-MON-RRRR HH24:MI:SS') || ' *** ' || '
212 ' || ' *** ' || c2_rec.created_by_name || ' *** ' || '
213 ' || ' *** ' || c2_rec.note_type_meaning || ' *** ' || '
214 ' || c2_rec.notes;
215
216 if nvl(c2_rec.notes_detail_size,0) > 0 then
217 l_curr_rec := l_curr_rec || ' <...>';
218 end if;
219
220 x_notes_data_list.EXTEND;
221 x_notes_data_list(x_notes_data_list.LAST) := SYSTEM.APP_INFO_HEADER_OBJ( l_curr_rec);
222 l_ctr := l_ctr + 1;
223 END LOOP;
224 end if;
225
226
227 x_return_status :=fnd_api.g_ret_sts_success;
228
229 fnd_msg_pub.Count_And_Get(p_count => x_msg_count,
230 p_data => x_msg_data);
231
232 EXCEPTION
233
234 when fnd_api.g_exc_error then
235 x_return_status:=fnd_api.g_ret_sts_error;
236
237 when fnd_api.g_exc_unexpected_error then
238 x_return_status:=fnd_api.g_ret_sts_unexp_error;
239
240 when others then
241 x_return_status:=fnd_api.g_ret_sts_unexp_error;
242
243 fnd_msg_pub.Count_And_Get(p_count => x_msg_count,
244 p_data => x_msg_data);
245
246
247
248 end ast_uwq_llist_notes;
249 END ast_uwq_llist_info_pkg;