[Home] [Help]
PACKAGE BODY: APPS.AST_UWQ_OLIST_INFO_PKG
Source
1 package body ast_uwq_olist_info_pkg AS
2 /* $Header: astuoinb.pls 120.3 2006/01/12 01:57:22 rkumares ship $ */
3
4 --Purpose: This package will be used for displaying information within the work panel for the opportunity node
5 --Created by: Joseph Raj dated 10/10/02
6 --Last Updated by: Joseph Raj dated 10/10/02
7 -- changed data to header type..
8 --Derived from astnoteb.pls
9
10 procedure ast_uwq_olist_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
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 = '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 ('astuoinb_cust_notes', 'astuoinb_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 ('astuoinb_cont_notes', 'astuoinb_prof_cont_notes') then -- begin of main "if"
145
146 l_object_id := l_contact_party_id;
147 l_object_code := 'PARTY%';
148
149 elsif p_action_key in ('astuoinb_opp_notes', 'astuoinb_prof_opp_notes') then -- begin of main "if"
150
151 l_object_id := l_lead_id;
152 l_object_code := 'OPPORTUNITY';
153
154 end if;
155
156 if p_action_key in ('astuoinb_cust_notes','astuoinb_cont_notes', 'astuoinb_opp_notes') then
157
158 l_cur_ind := 'DESC' ;
159 elsif p_action_key in ('astuoinb_prof_cust_notes','astuoinb_prof_cont_notes', 'astuoinb_prof_opp_notes') then
160 l_months := nvl(FND_PROFILE.VALUE('AST_DEFAULT_MONTHS_TO_VIEW'), 1);
161 l_from_date := add_months(sysdate, -1 * l_months );
162 l_cur_ind := 'MON' ;
163 end if;
164
165 -----Notes in descending order ( latest to the oldest )
166 if l_object_id is not null and l_cur_ind = 'DESC' then
167 for c2_rec in C_note_details_desc (l_fnd_user_id,
168 l_object_code,
169 l_object_id)
170 LOOP
171 --Code added for BugFix#4451689 --Start
172 if fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS')='Y' THEN
173 l_client_tz_id := to_number ( fnd_profile.value('CLIENT_TIMEZONE_ID'));
174 l_server_tz_id := to_number( fnd_profile.value('SERVER_TIMEZONE_ID'));
175
176 --modified with c2_rec.entered_date instead of c2_rec.creation_date_date for bug#4177915
177 c2_rec.entered_date:=HZ_TIMEZONE_PUB.Convert_DateTime(l_server_tz_id,l_client_tz_id,c2_rec.entered_date);
178
179 end if;
180 --Code added for BugFix#4451689 --Start
181 --modified with c2_rec.entered_date instead of c2_rec.creation_date_date for bug#4177915
182 l_curr_rec := ' *** ' || to_char(c2_rec.entered_date,'DD-MON-RRRR HH24:MI:SS') || ' *** ' || '
183 ' || ' *** ' || c2_rec.created_by_name || ' *** ' || '
184 ' || ' *** ' || c2_rec.note_type_meaning || ' *** ' || '
185 ' || c2_rec.notes;
186
187 if nvl(c2_rec.notes_detail_size,0) > 0 then
188 l_curr_rec := l_curr_rec || ' <...>';
189 end if;
190
191 x_notes_data_list.EXTEND;
192 x_notes_data_list(x_notes_data_list.LAST) := SYSTEM.APP_INFO_HEADER_OBJ( l_curr_rec);
193 l_ctr := l_ctr + 1;
194 END LOOP;
195
196 elsif l_object_id is not null and l_cur_ind = 'MON' then
197 for c2_rec in C_note_details_months (l_fnd_user_id,
198 l_object_code,
199 l_object_id, l_from_date)
200 LOOP
201
202 --Code added for BugFix#4451689 --Start
203 if fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS')='Y' THEN
204 l_client_tz_id := to_number ( fnd_profile.value('CLIENT_TIMEZONE_ID'));
205 l_server_tz_id := to_number( fnd_profile.value('SERVER_TIMEZONE_ID'));
206
207 --modified with c2_rec.entered_date instead of c2_rec.creation_date_date for bug#4177915
208 c2_rec.entered_date:=HZ_TIMEZONE_PUB.Convert_DateTime(l_server_tz_id,l_client_tz_id,c2_rec.entered_date);
209
210 end if;
211 --Code added for BugFix#4451689 --Start
212 --modified with c2_rec.entered_date instead of c2_rec.creation_date_date for bug#4177915
213 l_curr_rec := ' *** ' || to_char(c2_rec.entered_date,'DD-MON-RRRR HH24:MI:SS') || ' *** ' || '
214 ' || ' *** ' || c2_rec.created_by_name || ' *** ' || '
215 ' || ' *** ' || c2_rec.note_type_meaning || ' *** ' || '
216 ' || c2_rec.notes;
217
218 if nvl(c2_rec.notes_detail_size,0) > 0 then
219 l_curr_rec := l_curr_rec || ' <...>';
220 end if;
221
222 x_notes_data_list.EXTEND;
223 x_notes_data_list(x_notes_data_list.LAST) := SYSTEM.APP_INFO_HEADER_OBJ( l_curr_rec);
224 l_ctr := l_ctr + 1;
225 END LOOP;
226 end if;
227
228
229 x_return_status :=fnd_api.g_ret_sts_success;
230
231 fnd_msg_pub.Count_And_Get(p_count => x_msg_count,
232 p_data => x_msg_data);
233
234 EXCEPTION
235
236 when fnd_api.g_exc_error then
237 x_return_status:=fnd_api.g_ret_sts_error;
238
239 when fnd_api.g_exc_unexpected_error then
240 x_return_status:=fnd_api.g_ret_sts_unexp_error;
241
242 when others then
243 x_return_status:=fnd_api.g_ret_sts_unexp_error;
244
245 fnd_msg_pub.Count_And_Get(p_count => x_msg_count,
246 p_data => x_msg_data);
247
248
249
250 end ast_uwq_olist_notes;
251 END ast_uwq_olist_info_pkg;