[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_ORG_MANAGER_PVT
Source
1 PACKAGE BODY AS_SALES_ORG_MANAGER_PVT as
2 /* $Header: asxvsomb.pls 120.1 2005/06/23 01:05:40 appldev ship $ */
3
4 --
5 -- NAME
6 -- AS_SALES_ORG_MANAGER_PVT
7 --
8 -- HISTORY
9 -- 6/19/98 ALHUNG CREATED
10 --
11 --
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):='AS_SALES_ORG_MANAGER_PVT';
14 G_FILE_NAME CONSTANT VARCHAR2(12):='asxvsomb.pls';
15
16
17 -- Following are private procedures and functions used by Get_Sales_Groups
18
19 Function get_sales_group_gen_select return Varchar2 IS
20
21 l_select_cl Varchar2(500);
22
23 Begin
24 l_select_cl := 'Select ' ||
25 'Sales_Group_id, Name, Start_Date_Active, End_Date_Active, ' ||
26 'Description, Parent_Sales_Group_ID, Manager_Person_Id, ' ||
27 'Manager_Salesforce_Id, Accounting_Code ' ||
28 'From AS_SALES_GROUPS_V ' ||
29 'Where 1=1 ';
30 return l_select_cl;
31
32
33 End get_sales_group_gen_select;
34
35 Function get_sales_group_gen_where(p_sales_group_rec IN AS_SALES_GROUP_PUB.Sales_Group_Rec_Type)
36 return Varchar2 IS
37
38 l_where_cl Varchar2(1000);
39
40 Begin
41
42 If (p_sales_group_rec.sales_group_id <> FND_API.G_MISS_NUM) Then
43 l_where_cl := l_where_cl || 'And sales_group_id = :p_sales_group_id ';
44 End if;
45
46 If (p_sales_group_rec.name <> FND_API.G_MISS_CHAR) Then
47 l_where_cl := l_where_cl || 'And name like :p_name ';
48 End if;
49
50 If (p_sales_group_rec.parent_sales_group_id <> FND_API.G_MISS_NUM) Then
51 l_where_cl := l_where_cl || 'And parent_sales_group_id = :p_parent_sales_group_id ';
52 End if;
53
54 If (p_sales_group_rec.manager_person_id <> FND_API.G_MISS_NUM) Then
55 l_where_cl := l_where_cl || 'And manager_person_id = :p_manager_person_id ';
56 End if;
57
58 If (p_sales_group_rec.manager_salesforce_id <> FND_API.G_MISS_NUM) Then
59 l_where_cl := l_where_cl || 'And manager_salesforce_id = :p_manager_salesforce_id ';
60 End if;
61
62 return l_where_cl;
63
64 End get_sales_group_gen_where;
65
66 Procedure get_sales_group_define_columns(p_cur_get_salesgroup IN Number) IS
67
68 l_sales_group_rec AS_SALES_GROUP_PUB.Sales_Group_Rec_Type;
69
70 Begin
71
72 dbms_sql.define_column(p_cur_get_salesgroup, 1, l_sales_group_rec.sales_group_id);
73 dbms_sql.define_column(p_cur_get_salesgroup, 2, l_sales_group_rec.name, 60);
74 dbms_sql.define_column(p_cur_get_salesgroup, 3, l_sales_group_rec.start_date_active);
75 dbms_sql.define_column(p_cur_get_salesgroup, 4, l_sales_group_rec.end_date_active);
76 dbms_sql.define_column(p_cur_get_salesgroup, 5, l_sales_group_rec.description, 240);
77 dbms_sql.define_column(p_cur_get_salesgroup, 6, l_sales_group_rec.parent_sales_group_id);
78 dbms_sql.define_column(p_cur_get_salesgroup, 7, l_sales_group_rec.manager_person_id);
79 dbms_sql.define_column(p_cur_get_salesgroup, 8, l_sales_group_rec.manager_salesforce_id);
80 dbms_sql.define_column(p_cur_get_salesgroup, 9, l_sales_group_rec.accounting_code, 80);
81
82 End get_sales_group_define_columns;
83
84 Procedure get_sales_group_bind_variables(p_cur_get_salesgroup IN Number,
85 p_sales_group_rec IN AS_SALES_GROUP_PUB.Sales_Group_Rec_Type) IS
86
87 Begin
88 If (p_sales_group_rec.sales_group_id <> FND_API.G_MISS_NUM) Then
89 dbms_sql.bind_variable(p_cur_get_salesgroup, 'p_sales_group_id',
90 p_sales_group_rec.sales_group_id);
91 End if;
92
93 If (p_sales_group_rec.name <> FND_API.G_MISS_CHAR) Then
94 dbms_sql.bind_variable(p_cur_get_salesgroup, 'p_name',
95 p_sales_group_rec.name);
96 End if;
97
98 If (p_sales_group_rec.parent_sales_group_id <> FND_API.G_MISS_NUM) Then
99 dbms_sql.bind_variable(p_cur_get_salesgroup, 'p_parent_sales_group_id',
100 p_sales_group_rec.parent_sales_group_id);
101 End if;
102
103 If (p_sales_group_rec.manager_person_id <> FND_API.G_MISS_NUM) Then
104 dbms_sql.bind_variable(p_cur_get_salesgroup, 'p_manager_person_id',
105 p_sales_group_rec.manager_person_id);
106 End if;
107
108 If (p_sales_group_rec.manager_salesforce_id <> FND_API.G_MISS_NUM) Then
109 dbms_sql.bind_variable(p_cur_get_salesgroup, 'p_manager_salesforce_id',
110 p_sales_group_rec.manager_salesforce_id);
111 End if;
112 End get_sales_group_bind_variables;
113
114 Procedure get_sales_group_column_values(p_cur_get_salesgroup IN Number,
115 x_sales_group_rec OUT NOCOPY AS_SALES_GROUP_PUB.Sales_Group_Rec_Type) IS
116
117 Begin
118
119 dbms_sql.column_value(p_cur_get_salesgroup, 1, x_sales_group_rec.sales_group_id);
120 dbms_sql.column_value(p_cur_get_salesgroup, 2, x_sales_group_rec.name);
121 dbms_sql.column_value(p_cur_get_salesgroup, 3, x_sales_group_rec.start_date_active);
122 dbms_sql.column_value(p_cur_get_salesgroup, 4, x_sales_group_rec.end_date_active);
123 dbms_sql.column_value(p_cur_get_salesgroup, 5, x_sales_group_rec.description);
124 dbms_sql.column_value(p_cur_get_salesgroup, 6, x_sales_group_rec.parent_sales_group_id);
125 dbms_sql.column_value(p_cur_get_salesgroup, 7, x_sales_group_rec.manager_person_id);
126 dbms_sql.column_value(p_cur_get_salesgroup, 8, x_sales_group_rec.manager_salesforce_id);
127 dbms_sql.column_value(p_cur_get_salesgroup, 9, x_sales_group_rec.accounting_code);
128
129 End get_sales_group_column_values;
130
131 -- Following are private procedures and functions used by get_salesmem
132
133 Function get_salesmem_gen_select return Varchar2 IS
134
135 l_select_cl Varchar2(500);
136
137 Begin
138 l_select_cl := 'Select ' ||
139 'force.salesforce_id, force.type, force.start_date_active, force.end_date_active, ' ||
140 'force.employee_person_id, null, force.Partner_address_id, ' ||
141 -- remove partner_contact_id
142 -- 'force.partner_customer_id, force.partner_contact_id, people.last_name, ' ||
143 'force.partner_customer_id, people.last_name, ' ||
144 -- remove job.name and replace it by null so don't need to change everything
145 -- 'people.first_name, people.full_name, people.email_address, job.name, ' ||
146 'people.first_name, people.full_name, people.email_address, null, ' ||
147 'sales_group.name, manage_group.sales_group_id, manage_group.name ';
148 return l_select_cl;
149
150
151 End get_salesmem_gen_select;
152
153 Function get_salesmem_gen_select_w_grp return Varchar2 IS
154
155 l_select_cl Varchar2(500);
156
157 Begin
158 l_select_cl := 'Select ' ||
159 'force.salesforce_id, force.type, force.start_date_active, force.end_date_active, ' ||
160 'force.employee_person_id, force.sales_group_id, force.Partner_address_id, ' ||
161 'force.partner_customer_id, people.last_name, ' ||
162 'people.first_name, people.full_name, people.email_address, null, ' ||
163 'sales_group.name, manage_group.sales_group_id, manage_group.name ';
164 return l_select_cl;
165
166 End get_salesmem_gen_select_w_grp;
167
168 Function get_salesmem_gen_where(p_sales_member_rec IN AS_SALES_MEMBER_PUB.Sales_Member_Rec_Type)
169 return Varchar2 IS
170
171 l_where_cl Varchar2(1000);
172
173 Begin
174
175 -- fix bug 1265779, remove PER_ASSIGNMENTS_F, PER_JOBS
176 -- l_where_cl := 'From per_jobs job, per_assignments_f assign, PER_PEOPLE_F people, ' ||
177 l_where_cl := 'From PER_PEOPLE_F people, ' ||
178 'as_sales_groups_v sales_group, AS_SALESFORCE_V force, as_sales_groups_v manage_group ';
179
180 If (p_sales_member_rec.user_id <> FND_API.G_MISS_NUM) Then
181 l_where_cl := l_where_cl || ', FND_USER fnd_user ' ||
182 'where fnd_user.employee_id = force.employee_person_id and ' ||
183 'fnd_user.user_id = :p_user_id ';
184 Else
185 l_where_cl := l_where_cl || 'Where 1=1 ';
186 End if;
187 -- Fix bug 788241
188 l_where_cl := l_where_cl || 'And force.employee_person_id = people.person_id ' ||
189 ' and sales_group.sales_group_id (+) = force.sales_group_id ' ||
190 -- fix bug 1265779, remove PER_ASSIGNMENTS_F, PER_JOBS join
191 -- ' and people.person_id = assign.person_id and assign.job_id = job.job_id ' ||
192 -- ' and assign.assignment_type = ''E'' and assign.primary_flag = ''Y'' ' ||
193 ' and force.employee_person_id = manage_group.manager_person_id(+)' ||
194 ' and trunc(sysdate) >= nvl(people.effective_start_date,trunc(sysdate)) '||
195 ' and trunc(sysdate) <= nvl(people.effective_end_date,trunc(sysdate))';
196 If (p_sales_member_rec.salesforce_id <> FND_API.G_MISS_NUM) Then
197 l_where_cl := l_where_cl || 'And force.salesforce_id like :p_salesforce_id ';
198 End if;
199
200 If (p_sales_member_rec.Employee_Person_Id <> FND_API.G_MISS_NUM) Then
201 l_where_cl := l_where_cl || 'And force.Employee_Person_Id = :p_Employee_Person_Id ';
202 End if;
203
204 If (p_sales_member_rec.sales_group_id <> FND_API.G_MISS_NUM) Then
205 l_where_cl := l_where_cl || 'And force.sales_group_id = :p_sales_group_id ';
206 End if;
207
208 If (p_sales_member_rec.Partner_Address_Id <> FND_API.G_MISS_NUM) Then
209 l_where_cl := l_where_cl || 'And force.Partner_Address_Id = :p_Partner_Address_Id ';
210 End if;
211
212 If (p_sales_member_rec.Partner_Customer_Id <> FND_API.G_MISS_NUM) Then
213 l_where_cl := l_where_cl || 'And force.Partner_Customer_Id = :p_Partner_Customer_Id ';
214 End if;
215
216 If (p_sales_member_rec.Last_name <> FND_API.G_MISS_CHAR) Then
217 l_where_cl := l_where_cl || 'And people.Last_name like :p_Last_name ';
218 End if;
219
220 If (p_sales_member_rec.first_name <> FND_API.G_MISS_CHAR) Then
221 l_where_cl := l_where_cl || 'And people.first_name like :p_first_name ';
222 End if;
223
224 If (p_sales_member_rec.Email_address <> FND_API.G_MISS_CHAR) Then
225 l_where_cl := l_where_cl || 'And people.Email_address like :p_Email_address ';
226 End if;
227
228 If (p_sales_member_rec.type <> FND_API.G_MISS_CHAR) Then
229 l_where_cl := l_where_cl || 'And force.type = :p_type ';
230 End if;
231
232 return l_where_cl;
233
234 End get_salesmem_gen_where;
235
236 Function get_salesmem_gen_where_w_grp(p_sales_member_rec IN AS_SALES_MEMBER_PUB.Sales_Member_Rec_Type)
237 return Varchar2 IS
238
239 l_where_cl Varchar2(1000);
240
241 Begin
242
243 l_where_cl := 'From PER_PEOPLE_F people, ' ||
244 'as_sales_groups_v sales_group, AS_FC_SALESFORCE_V force, as_sales_groups_v manage_group ';
245
246 If (p_sales_member_rec.user_id <> FND_API.G_MISS_NUM) Then
247 l_where_cl := l_where_cl || ', FND_USER fnd_user ' ||
248 'where fnd_user.employee_id = force.employee_person_id and ' ||
249 'fnd_user.user_id = :p_user_id ';
250 Else
251 l_where_cl := l_where_cl || 'Where 1=1 ';
252 End if;
253 l_where_cl := l_where_cl || 'And force.employee_person_id = people.person_id ' ||
254 ' and sales_group.sales_group_id (+) = force.sales_group_id ' ||
255 ' and force.employee_person_id = manage_group.manager_person_id(+)' ||
256 ' and trunc(sysdate) >= nvl(people.effective_start_date,trunc(sysdate)) '||
257 ' and trunc(sysdate) <= nvl(people.effective_end_date,trunc(sysdate))';
258 If (p_sales_member_rec.salesforce_id <> FND_API.G_MISS_NUM) Then
259 l_where_cl := l_where_cl || 'And force.salesforce_id like :p_salesforce_id ';
260 End if;
261
262 If (p_sales_member_rec.Employee_Person_Id <> FND_API.G_MISS_NUM) Then
263 l_where_cl := l_where_cl || 'And force.Employee_Person_Id = :p_Employee_Person_Id ';
264 End if;
265
266 If (p_sales_member_rec.sales_group_id <> FND_API.G_MISS_NUM) Then
267 l_where_cl := l_where_cl || 'And force.sales_group_id = :p_sales_group_id ';
268 End if;
269
270 If (p_sales_member_rec.Partner_Address_Id <> FND_API.G_MISS_NUM) Then
271 l_where_cl := l_where_cl || 'And force.Partner_Address_Id = :p_Partner_Address_Id ';
272 End if;
273
274 If (p_sales_member_rec.Partner_Customer_Id <> FND_API.G_MISS_NUM) Then
275 l_where_cl := l_where_cl || 'And force.Partner_Customer_Id = :p_Partner_Customer_Id ';
276 End if;
277
278 If (p_sales_member_rec.Last_name <> FND_API.G_MISS_CHAR) Then
279 l_where_cl := l_where_cl || 'And people.Last_name like :p_Last_name ';
280 End if;
281
282 If (p_sales_member_rec.first_name <> FND_API.G_MISS_CHAR) Then
283 l_where_cl := l_where_cl || 'And people.first_name like :p_first_name ';
284 End if;
285
286 If (p_sales_member_rec.Email_address <> FND_API.G_MISS_CHAR) Then
287 l_where_cl := l_where_cl || 'And people.Email_address like :p_Email_address ';
288 End if;
289
290 If (p_sales_member_rec.type <> FND_API.G_MISS_CHAR) Then
291 l_where_cl := l_where_cl || 'And force.type = :p_type ';
292 End if;
293
294 return l_where_cl;
295
296 End get_salesmem_gen_where_w_grp;
297
298 Procedure get_salesmem_define_cols(p_cur_get_salesmember IN Number) IS
299
300 l_sales_member_rec AS_sales_member_PUB.sales_member_Rec_Type;
301
302 Begin
303
304 dbms_sql.define_column(p_cur_get_salesmember, 1, l_sales_member_rec.salesforce_id);
305 dbms_sql.define_column(p_cur_get_salesmember, 2, l_sales_member_rec.type, 30);
306 dbms_sql.define_column(p_cur_get_salesmember, 3, l_sales_member_rec.start_date_active);
307 dbms_sql.define_column(p_cur_get_salesmember, 4, l_sales_member_rec.end_date_active);
308 dbms_sql.define_column(p_cur_get_salesmember, 5, l_sales_member_rec.employee_person_id);
309 dbms_sql.define_column(p_cur_get_salesmember, 6, l_sales_member_rec.sales_group_id);
310 dbms_sql.define_column(p_cur_get_salesmember, 7, l_sales_member_rec.partner_address_id);
311 dbms_sql.define_column(p_cur_get_salesmember, 8, l_sales_member_rec.partner_customer_id);
312 --dbms_sql.define_column(p_cur_get_salesmember, 9, l_sales_member_rec.partner_contact_id);
313 dbms_sql.define_column(p_cur_get_salesmember, 9, l_sales_member_rec.last_name, 40);
314 dbms_sql.define_column(p_cur_get_salesmember, 10, l_sales_member_rec.first_name, 20);
315 dbms_sql.define_column(p_cur_get_salesmember, 11, l_sales_member_rec.full_name, 240);
316 dbms_sql.define_column(p_cur_get_salesmember, 12, l_sales_member_rec.email_address, 240);
317 dbms_sql.define_column(p_cur_get_salesmember, 13, l_sales_member_rec.job_title, 240);
318 dbms_sql.define_column(p_cur_get_salesmember, 14, l_sales_member_rec.sales_group_name, 60);
319 dbms_sql.define_column(p_cur_get_salesmember, 15, l_sales_member_rec.managing_sales_grp_id);
320 dbms_sql.define_column(p_cur_get_salesmember, 16, l_sales_member_rec.managing_sales_grp_name, 60);
321 End get_salesmem_define_cols;
322
323 Procedure get_salesmem_bind_vars(p_cur_get_salesmember IN Number,
324 p_sales_member_rec IN AS_sales_member_PUB.sales_member_Rec_Type) IS
325
326 Begin
327 If (p_sales_member_rec.user_id <> FND_API.G_MISS_NUM) Then
328 dbms_sql.bind_variable(p_cur_get_salesmember, 'p_user_id',
329 p_sales_member_rec.user_id);
330 End if;
331
332 If (p_sales_member_rec.salesforce_id <> FND_API.G_MISS_NUM) Then
333 dbms_sql.bind_variable(p_cur_get_salesmember, 'p_salesforce_id',
334 p_sales_member_rec.salesforce_id);
335 End if;
336
337 If (p_sales_member_rec.employee_person_id <> FND_API.G_MISS_NUM) Then
338 dbms_sql.bind_variable(p_cur_get_salesmember, 'p_employee_person_id',
339 p_sales_member_rec.employee_person_id);
340 End if;
341
342 If (p_sales_member_rec.sales_group_id <> FND_API.G_MISS_NUM) Then
343 dbms_sql.bind_variable(p_cur_get_salesmember, 'p_sales_group_id',
344 p_sales_member_rec.sales_group_id);
345 End if;
346
347
348 If (p_sales_member_rec.partner_address_id <> FND_API.G_MISS_NUM) Then
349 dbms_sql.bind_variable(p_cur_get_salesmember, 'p_partner_address_id',
350 p_sales_member_rec.partner_address_id);
351 End if;
352
353 If (p_sales_member_rec.partner_customer_id <> FND_API.G_MISS_NUM) Then
354 dbms_sql.bind_variable(p_cur_get_salesmember, 'p_partner_customer_id',
355 p_sales_member_rec.partner_customer_id);
356 End if;
357
358 If (p_sales_member_rec.last_name <> FND_API.G_MISS_CHAR) Then
359 dbms_sql.bind_variable(p_cur_get_salesmember, 'p_last_name',
360 p_sales_member_rec.last_name);
361 End if;
362
363 If (p_sales_member_rec.first_name <> FND_API.G_MISS_CHAR) Then
364 dbms_sql.bind_variable(p_cur_get_salesmember, 'p_first_name',
365 p_sales_member_rec.first_name);
366 End if;
367
368 If (p_sales_member_rec.email_address <> FND_API.G_MISS_CHAR) Then
369 dbms_sql.bind_variable(p_cur_get_salesmember, 'p_email_address',
370 p_sales_member_rec.email_address);
371 End if;
372
373 If (p_sales_member_rec.type <> FND_API.G_MISS_CHAR) Then
374 dbms_sql.bind_variable(p_cur_get_salesmember, 'p_type',
375 p_sales_member_rec.type);
376 End if;
377
378 End get_salesmem_bind_vars;
379
380 Procedure get_salesmem_column_values(p_cur_get_salesmember IN Number,
381 x_sales_member_rec OUT NOCOPY AS_sales_member_PUB.sales_member_Rec_Type) IS
382
383 Begin
384
385 dbms_sql.column_value(p_cur_get_salesmember, 1, x_sales_member_rec.salesforce_id);
386 dbms_sql.column_value(p_cur_get_salesmember, 2, x_sales_member_rec.type);
387 dbms_sql.column_value(p_cur_get_salesmember, 3, x_sales_member_rec.start_date_active);
388 dbms_sql.column_value(p_cur_get_salesmember, 4, x_sales_member_rec.end_date_active);
389 dbms_sql.column_value(p_cur_get_salesmember, 5, x_sales_member_rec.employee_person_id);
390 dbms_sql.column_value(p_cur_get_salesmember, 6, x_sales_member_rec.sales_group_id);
391 dbms_sql.column_value(p_cur_get_salesmember, 7, x_sales_member_rec.partner_address_id);
392 dbms_sql.column_value(p_cur_get_salesmember, 8, x_sales_member_rec.partner_customer_id);
393 --dbms_sql.column_value(p_cur_get_salesmember, 9, x_sales_member_rec.partner_contact_id);
394 dbms_sql.column_value(p_cur_get_salesmember, 9, x_sales_member_rec.last_name);
395 dbms_sql.column_value(p_cur_get_salesmember, 10, x_sales_member_rec.first_name);
396 dbms_sql.column_value(p_cur_get_salesmember, 11, x_sales_member_rec.full_name);
397 dbms_sql.column_value(p_cur_get_salesmember, 12, x_sales_member_rec.email_address);
398 dbms_sql.column_value(p_cur_get_salesmember, 13, x_sales_member_rec.job_title);
399 dbms_sql.column_value(p_cur_get_salesmember, 14, x_sales_member_rec.sales_group_name);
400 dbms_sql.column_value(p_cur_get_salesmember, 15, x_sales_member_rec.managing_sales_grp_id);
401 dbms_sql.column_value(p_cur_get_salesmember, 16, x_sales_member_rec.managing_sales_grp_name);
402
403 End get_salesmem_column_values;
404
405 /*****************************************************************************************/
406 /************ PUBLIC PROCEDURES ****/
407 /*****************************************************************************************/
408
409 --
410 -- NAME
411 -- Get_Sales_groups
412 --
413 -- PURPOSE
414 --
415 --
416 -- NOTES
417 --
418 --
419 -- HISTORY
420 --
421 --
422 PROCEDURE Get_Sales_groups
423 ( p_api_version_number IN NUMBER,
424 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
425 p_SALES_GROUP_rec IN AS_SALES_GROUP_PUB.SALES_GROUP_rec_Type,
426 x_return_status OUT NOCOPY VARCHAR2,
427 x_msg_count OUT NOCOPY NUMBER,
428 x_msg_data OUT NOCOPY VARCHAR2,
429 x_SALES_GROUP_tbl OUT NOCOPY AS_SALES_GROUP_PUB.SALES_GROUP_tbl_Type ) IS
430
431
432 l_api_name CONSTANT VARCHAR2(30) := 'Get_Sales_groups';
433 l_api_version_number CONSTANT NUMBER := 2.0;
434
435 l_SALES_GROUP_rec AS_SALES_GROUP_PUB.SALES_GROUP_rec_type;
436 l_rec_count Number := 0;
437 l_select_cl Varchar2(500);
438 l_where_cl Varchar2(1000);
439 l_cur_get_salesgroup Number;
440 l_ignore Number;
441 l_curr_row Number := 0;
442 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
443 l_module CONSTANT VARCHAR2(255) := 'as.plsql.sompv.Get_Sales_groups';
444
445 BEGIN
446
447
448 -- Standard call to check for call compatibility.
449 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
450 p_api_version_number,
451 l_api_name,
452 G_PKG_NAME)
453 THEN
454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
455 END IF;
456
457
458 -- Initialize message list if p_init_msg_list is set to TRUE.
459 IF FND_API.to_Boolean( p_init_msg_list ) THEN
460 FND_MSG_PUB.initialize;
461 END IF;
462
463 -- Debug Message
464 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
465 THEN
466 IF l_debug THEN
467 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_ORG_MANAGER_PVT.Get_Sales_groups - BEGIN');
468 END IF;
469 END IF;
470
471 -- Initialize API return status to success
472 x_return_status := FND_API.G_RET_STS_SUCCESS;
473
474 --
475 -- API body
476
477 Begin
478
479 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
480 IF l_debug THEN
481 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_ORG_MANAGER_PVT - Open Cursor');
482 END IF;
483 END IF;
484
485 l_cur_get_salesgroup := DBMS_SQL.open_cursor;
486
487 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
488 IF l_debug THEN
489 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_ORG_MANAGER_PVT - Generate Select');
490 END IF;
491 END IF;
492
493 l_select_cl := get_sales_group_gen_select;
494
495 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
496 IF l_debug THEN
497 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_ORG_MANAGER_PVT - Generate Where');
498 END IF;
499 END IF;
500
501 l_where_cl := get_sales_group_gen_where(p_sales_group_rec);
502
503 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
504 IF l_debug THEN
505 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_ORG_MANAGER_PVT - Parse SQL');
506 END IF;
507 END IF;
508
509 DBMS_SQL.parse(l_cur_get_salesgroup,
510 l_select_cl || l_where_cl, DBMS_SQL.native);
511
512 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
513 IF l_debug THEN
514 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_ORG_MANAGER_PVT - Define Columns');
515 END IF;
516 END IF;
517
518 get_sales_group_define_columns(l_cur_get_salesgroup);
519
520 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
521 IF l_debug THEN
522 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_ORG_MANAGER_PVT - Bind Variables');
523 END IF;
524 END IF;
525
526 get_sales_group_bind_variables(l_cur_get_salesgroup, p_sales_group_rec);
527
528 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
529 IF l_debug THEN
530 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_ORG_MANAGER_PVT - Execute SQL');
531 END IF;
532 END IF;
533
534 l_ignore := DBMS_SQL.Execute(l_cur_get_salesgroup);
535
536 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
537 IF l_debug THEN
538 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_ORG_MANAGER_PVT - Column Values');
539 END IF;
540 END IF;
541
542 Loop
543 If (dbms_sql.fetch_rows(l_cur_get_salesgroup) > 0) Then
544 get_sales_group_column_values(l_cur_get_salesgroup, l_SALES_GROUP_rec);
545 l_curr_row := l_curr_row + 1;
546 x_SALES_GROUP_tbl(l_curr_row) := l_SALES_GROUP_rec;
547 Else
548 Exit;
549 End if;
550 End Loop;
551
552 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
553 IF l_debug THEN
554 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_ORG_MANAGER_PVT - Close cursor');
555 END IF;
556 END IF;
557
558 DBMS_SQL.Close_Cursor(l_cur_get_salesgroup);
559
560
561 EXCEPTION
562
563 WHEN NO_DATA_FOUND THEN
564
565 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
566 THEN
567 --FND_MESSAGE.Set_Name('AS', 'Pvt Pipeline API: Cannot find salesgroup'); -- MMSG
568 --FND_MSG_PUB.ADD;
569 IF l_debug THEN
570 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_ORG_MANAGER_PVT: Cannot find sales group');
571 END IF;
572 END IF;
573
574 x_return_status := FND_API.G_RET_STS_SUCCESS ;
575
576 FND_MSG_PUB.Count_And_Get
577 ( p_count => x_msg_count,
578 p_data => x_msg_data
579 );
580 return;
581 End;
582
583 -- End of API body.
584 --
585
586 x_return_status := FND_API.G_RET_STS_SUCCESS;
587
588 -- Debug Message
589 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
590 THEN
591 IF l_debug THEN
592 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_ORG_MANAGER_PVT.Get_Sales_groups End');
593 END IF;
594 --FND_MESSAGE.Set_Name('AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser End');
595 --FND_MSG_PUB.Add;
596 END IF;
597
598 -- Standard call to get message count and if count is 1, get message info.
599 FND_MSG_PUB.Count_And_Get
600 ( p_count => x_msg_count,
601 p_data => x_msg_data
602 );
603
604
605 EXCEPTION
606
607 WHEN FND_API.G_EXC_ERROR THEN
608 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
609 P_MODULE => l_module
610 ,P_API_NAME => L_API_NAME
611 ,P_PKG_NAME => G_PKG_NAME
612 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
613 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
614 ,P_ROLLBACK_FLAG => 'N'
615 ,X_MSG_COUNT => X_MSG_COUNT
616 ,X_MSG_DATA => X_MSG_DATA
617 ,X_RETURN_STATUS => X_RETURN_STATUS);
618
619 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
620 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
621 P_MODULE => l_module
622 ,P_API_NAME => L_API_NAME
623 ,P_PKG_NAME => G_PKG_NAME
624 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
625 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
626 ,P_ROLLBACK_FLAG => 'N'
627 ,X_MSG_COUNT => X_MSG_COUNT
628 ,X_MSG_DATA => X_MSG_DATA
629 ,X_RETURN_STATUS => X_RETURN_STATUS);
630
631 WHEN OTHERS THEN
632 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
633 P_MODULE => l_module
634 ,P_API_NAME => L_API_NAME
635 ,P_PKG_NAME => G_PKG_NAME
636 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
637 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
638 ,P_ROLLBACK_FLAG => 'N'
639 ,X_MSG_COUNT => X_MSG_COUNT
640 ,X_MSG_DATA => X_MSG_DATA
641 ,X_RETURN_STATUS => X_RETURN_STATUS);
642
643 END Get_Sales_groups;
644
645 --
646 -- NAME
647 -- Get_CurrentUser
648 --
649 -- PURPOSE
650 --
651 --
652 -- NOTES
653 -- This procedure is used to do two things
654 -- 1) Get salesforce_id, employee_id, partner_customer_id ....
655 -- 2) Validate against user_id to make sure the login person is a valid user
656 -- So, that's the reason why checking user_id first and then salesforce_id
657 --
658 -- HISTORY
659 --
660 --
661 PROCEDURE Get_CurrentUser
662 ( p_api_version_number IN NUMBER,
663 p_init_msg_list IN VARCHAR2
664 := FND_API.G_FALSE,
665 p_salesforce_id IN NUMBER,
666 p_admin_group_id IN NUMBER,
667 x_return_status OUT NOCOPY VARCHAR2,
668 x_msg_count OUT NOCOPY NUMBER,
669 x_msg_data OUT NOCOPY VARCHAR2,
670 x_sales_member_rec OUT NOCOPY AS_SALES_MEMBER_PUB.Sales_member_rec_Type ) IS
671
672
673 Cursor C_GetIdentity_FndUser(p_user_id Number) IS
674 Select force.resource_id,
675 force.category,
676 force.start_date_active,
677 force.end_date_active,
678 decode(force.category,'EMPLOYEE',force.source_id,null),
679 null,
680 null,
681 decode(force.category,'PARTY',force.source_id,null)
682 From JTF_RS_RESOURCE_EXTNS force, JTF_RS_ROLE_RELATIONS rrel
683 ,JTF_RS_ROLES_B roleb, FND_User fnd_user
684 Where force.user_id = fnd_user.user_id
685 and fnd_user.user_id = p_user_id
686 and force.category in ('EMPLOYEE','PARTY')
687 and force.resource_id = rrel.role_resource_id
688 and rrel.role_resource_type = 'RS_INDIVIDUAL'
689 and rrel.role_id = roleb.role_id
690 and roleb.role_type_code in ('SALES','TELESALES','FIELDSALES','PRM')
691 and rownum = 1;
692
693 Cursor C_GetIdentity_SFID(p_salesforce_id Number) IS
694 Select force.salesforce_id,
695 force.Type,
696 force.start_date_active,
697 force.end_date_active,
698 force.employee_person_id,
699 -- force.sales_group_id,
700 force.partner_address_id,
701 force.partner_customer_id,
702 force.partner_contact_id
703 From AS_SALESFORCE_V force
704 Where salesforce_id = p_salesforce_id
705 and rownum = 1;
706
707 Cursor C_GetAdminGroup(p_salesforce_id NUMBER, p_sales_group_id NUMBER) IS
708 Select 1
709 from dual
710 where exists(select 1 from AS_FC_SALESFORCE_V force
711 where salesforce_id = p_salesforce_id
712 and sales_group_id = p_sales_group_id);
713
714 l_found NUMBER;
715 check_salesforce_id NUMBER;
716
717 l_api_name CONSTANT VARCHAR2(30) := 'Get_CurrentUser';
718 l_api_version_number CONSTANT NUMBER := 2.0;
719 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
720 l_module CONSTANT VARCHAR2(255) := 'as.plsql.sompv.Get_CurrentUser';
721
722 BEGIN
723
724 -- Standard call to check for call compatibility.
725 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
726 p_api_version_number,
727 l_api_name,
728 G_PKG_NAME)
729 THEN
730 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
731 END IF;
732
733
734 -- Initialize message list if p_init_msg_list is set to TRUE.
735 IF FND_API.to_Boolean( p_init_msg_list ) THEN
736 FND_MSG_PUB.initialize;
737 END IF;
738
739 -- Debug Message
740 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
741 THEN
742 IF l_debug THEN
743 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_MEMBER_PVT.Get_CurrentUser - BEGIN');
744 END IF;
745 END IF;
746
747 -- Initialize API return status to success
748 x_return_status := FND_API.G_RET_STS_SUCCESS;
749
750 --
751 -- API body
752
753 Begin
754
755 -- re-initializing this variable due to weird bug. this valus is lost when the package is called
756 IF (FND_GLOBAL.User_Id < 0) THEN
757 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
758 FND_MESSAGE.Set_Name('AS', 'UT_CANNOT_GET_PROFILE_VALUE');
759 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
760 FND_MSG_PUB.ADD;
761 END IF;
762
763 open C_GetIdentity_SFID(p_salesforce_id);
764 Fetch C_GetIdentity_SFID Into x_sales_member_rec.salesforce_id,
765 x_sales_member_rec.type,
766 x_sales_member_rec.start_date_active,
767 x_sales_member_rec.end_date_active,
768 x_sales_member_rec.employee_person_id,
769 -- x_sales_member_rec.sales_group_id,
770 x_sales_member_rec.partner_address_id,
771 x_sales_member_rec.partner_customer_id,
772 x_sales_member_rec.partner_contact_id;
773 Close C_GetIdentity_SFID;
774 -- RAISE FND_API.G_EXC_ERROR;
775 Else
776 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
777 IF l_debug THEN
778 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_MEMBER_PVT - Using FndUser to find identity');
779 END IF;
780 END IF;
781
782 Open C_GetIdentity_FndUser(FND_GLOBAL.User_Id);
783
784 If (C_GetIdentity_FndUser%ROWCOUNT > 1) Then
785
786 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
787 --FND_MESSAGE.Set_Name('AS', 'Pvt Pipeline API: Found duplicated salesrep'); -- MMSG
788 --FND_MSG_PUB.ADD;
789 IF l_debug THEN
790 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_MEMBER_PVT - Found duplicated salesrep');
791 END IF;
792 END IF;
793 End if;
794 Fetch C_GetIdentity_FndUser Into x_sales_member_rec.salesforce_id,
795 x_sales_member_rec.type,
796 x_sales_member_rec.start_date_active,
797 x_sales_member_rec.end_date_active,
798 x_sales_member_rec.employee_person_id,
799 -- x_sales_member_rec.sales_group_id,
800 x_sales_member_rec.partner_address_id,
801 x_sales_member_rec.partner_customer_id,
802 x_sales_member_rec.partner_contact_id;
803 Close C_GetIdentity_FndUser;
804
805 -- raise error if salesforce_id is null
806 If (x_sales_member_rec.salesforce_id is null OR x_sales_member_rec.salesforce_id = FND_API.G_MISS_NUM) Then
807 FND_MESSAGE.Set_Name('AS', 'AS_INVALID_USER_ID');
808 FND_MESSAGE.Set_Token('VALUE', FND_GLOBAL.USER_ID, FALSE);
809 FND_MSG_PUB.ADD;
810 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
811 IF l_debug THEN
812 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_MEMBER_PVT - Cannot identify user by checking user_id at JTF resource');
813 END IF;
814 END IF;
815 x_return_status := FND_API.G_RET_STS_ERROR ;
816 FND_MSG_PUB.Count_And_Get
817 ( p_count => x_msg_count,
818 p_data => x_msg_data
819 );
820 return;
821 End if;
822
823 END IF;
824
825 check_salesforce_id := x_sales_member_rec.salesforce_id;
826
827 IF (p_admin_group_id IS NOT NULL and p_admin_group_id <> FND_API.G_MISS_NUM) THEN
828 open C_GetAdminGroup(check_salesforce_id, p_admin_group_id);
829 fetch C_GetAdminGroup into l_found;
830 IF(C_GetAdminGroup%NOTFOUND) THEN
831 close C_GetAdminGroup;
832 RAISE FND_API.G_EXC_ERROR;
833 END IF;
834 close C_GetAdminGroup;
835 END IF;
836
837 x_sales_member_rec.user_id := FND_GLOBAL.User_Id;
838
839 EXCEPTION
840
841 WHEN NO_DATA_FOUND THEN
842
843 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
844 THEN
845 --FND_MESSAGE.Set_Name('AS', 'Pvt Pipeline API: Cannot find salesrep'); -- MMSG
846 --FND_MSG_PUB.ADD;
847 IF l_debug THEN
848 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_MEMBER_PVT: Cannot identify user');
849 END IF;
850 END IF;
851
852 x_return_status := FND_API.G_RET_STS_ERROR ;
853
854 FND_MSG_PUB.Count_And_Get
855 ( p_count => x_msg_count,
856 p_data => x_msg_data
857 );
858 return;
859
860 End;
861
862 -- End of API body.
863 --
864
865 x_return_status := FND_API.G_RET_STS_SUCCESS;
866
867 -- Debug Message
868 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
869 THEN
870 IF l_debug THEN
871 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_MEMBER_PVT.Get_CurrentUser End');
872 END IF;
873 --FND_MESSAGE.Set_Name('AS_SALES_MEMBER_PVT.Get_CurrentUser End');
874 --FND_MSG_PUB.Add;
875 END IF;
876
877 -- Standard call to get message count and if count is 1, get message info.
878 FND_MSG_PUB.Count_And_Get
879 ( p_count => x_msg_count,
880 p_data => x_msg_data
881 );
882
883
884 EXCEPTION
885
886 WHEN FND_API.G_EXC_ERROR THEN
887 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
888 P_MODULE => l_module
889 ,P_API_NAME => L_API_NAME
890 ,P_PKG_NAME => G_PKG_NAME
891 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
892 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
893 ,P_ROLLBACK_FLAG => 'N'
894 ,X_MSG_COUNT => X_MSG_COUNT
895 ,X_MSG_DATA => X_MSG_DATA
896 ,X_RETURN_STATUS => X_RETURN_STATUS);
897
898 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
899 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
900 P_MODULE => l_module
901 ,P_API_NAME => L_API_NAME
902 ,P_PKG_NAME => G_PKG_NAME
903 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
904 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
905 ,P_ROLLBACK_FLAG => 'N'
906 ,X_MSG_COUNT => X_MSG_COUNT
907 ,X_MSG_DATA => X_MSG_DATA
908 ,X_RETURN_STATUS => X_RETURN_STATUS);
909
910 WHEN OTHERS THEN
911 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
912 P_MODULE => l_module
913 ,P_API_NAME => L_API_NAME
914 ,P_PKG_NAME => G_PKG_NAME
915 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
916 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
917 ,P_ROLLBACK_FLAG => 'N'
918 ,X_MSG_COUNT => X_MSG_COUNT
919 ,X_MSG_DATA => X_MSG_DATA
920 ,X_RETURN_STATUS => X_RETURN_STATUS);
921
922 END Get_CurrentUser;
923
924 --
925 -- NAME
926 -- Get_Salesreps
927 --
928 -- PURPOSE
929 --
930 --
931 -- NOTES
932 --
933 --
934 -- HISTORY
935 --
936 --
937 PROCEDURE Get_Sales_members
938 ( p_api_version_number IN NUMBER,
939 p_init_msg_list IN VARCHAR2
940 := FND_API.G_FALSE,
941 p_sales_member_rec IN AS_SALES_MEMBER_PUB.Sales_member_rec_Type,
942 x_return_status OUT NOCOPY VARCHAR2,
943 x_msg_count OUT NOCOPY NUMBER,
944 x_msg_data OUT NOCOPY VARCHAR2,
945 x_sales_member_tbl OUT NOCOPY AS_SALES_MEMBER_PUB.Sales_member_tbl_Type ) IS
946
947 l_api_name CONSTANT VARCHAR2(30) := 'Get_Sales_members';
948 l_api_version_number CONSTANT NUMBER := 2.0;
949
950 l_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_type;
951 l_rec_count Number := 1;
952
953 Cursor C_GetGroupExist(p_salesforce_id NUMBER) IS
954 select 1
955 from dual
956 where exists(select 1 from AS_FC_SALESFORCE_V sale
957 where sale.salesforce_id = p_salesforce_id);
958
959 l_found NUMBER;
960
961 l_rec_count Number := 0;
962 l_select_cl Varchar2(500);
963 l_where_cl Varchar2(1000);
964 l_cur_get_salesmember Number;
965 l_ignore Number;
966 l_curr_row Number := 0;
967 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
968 l_module CONSTANT VARCHAR2(255) := 'as.plsql.sompv.Get_Sales_members';
969
970 BEGIN
971
972
973 -- Standard call to check for call compatibility.
974 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
975 p_api_version_number,
976 l_api_name,
977 G_PKG_NAME)
978 THEN
979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
980 END IF;
981
982
983 -- Initialize message list if p_init_msg_list is set to TRUE.
984 IF FND_API.to_Boolean( p_init_msg_list ) THEN
985 FND_MSG_PUB.initialize;
986 END IF;
987
988 -- Debug Message
989 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
990 THEN
991 IF l_debug THEN
992 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_MEMBER_PVT.Get_Sales_Members - BEGIN');
993 END IF;
994 END IF;
995
996 -- Initialize API return status to success
997 x_return_status := FND_API.G_RET_STS_SUCCESS;
998
999 --
1000 -- API body
1001
1002 Begin
1003
1004 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1005 IF l_debug THEN
1006 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_ORG_MANAGER_PVT - Open Cursor');
1007 END IF;
1008 END IF;
1009
1010 l_cur_get_salesmember := DBMS_SQL.open_cursor;
1011
1012 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1013 IF l_debug THEN
1014 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_ORG_MANAGER_PVT - Generate Select');
1015 END IF;
1016 END IF;
1017
1018 open C_GetGroupExist(p_sales_member_rec.salesforce_id);
1019 fetch C_GetGroupExist into l_found;
1020 IF(C_GetGroupExist%NOTFOUND) THEN
1021 l_select_cl := get_salesmem_gen_select;
1022 ELSE
1023 l_select_cl := get_salesmem_gen_select_w_grp;
1024 END IF;
1025 close C_GetGroupExist;
1026
1027 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1028 IF l_debug THEN
1029 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_ORG_MANAGER_PVT - Generate Where');
1030 END IF;
1031 END IF;
1032
1033 open C_GetGroupExist(p_sales_member_rec.salesforce_id);
1034 fetch C_GetGroupExist into l_found;
1035 IF(C_GetGroupExist%NOTFOUND) THEN
1036 l_where_cl := get_salesmem_gen_where(p_sales_member_rec);
1037 ELSE
1038 l_where_cl := get_salesmem_gen_where_w_grp(p_sales_member_rec);
1039 END IF;
1040 close C_GetGroupExist;
1041
1042 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1043 IF l_debug THEN
1044 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_ORG_MANAGER_PVT - Parse SQL');
1045 END IF;
1046 END IF;
1047
1048
1049 DBMS_SQL.parse(l_cur_get_salesmember,
1050 l_select_cl || l_where_cl, DBMS_SQL.native);
1051
1052
1053 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1054 IF l_debug THEN
1055 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_ORG_MANAGER_PVT - Define Columns');
1056 END IF;
1057 END IF;
1058
1059 get_salesmem_define_cols(l_cur_get_salesmember);
1060
1061 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1062 IF l_debug THEN
1063 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_ORG_MANAGER_PVT - Bind Variables');
1064 END IF;
1065 END IF;
1066
1067 get_salesmem_bind_vars(l_cur_get_salesmember, p_sales_member_rec);
1068
1069 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1070 IF l_debug THEN
1071 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_ORG_MANAGER_PVT - Execute SQL');
1072 END IF;
1073 END IF;
1074
1075 l_ignore := DBMS_SQL.Execute(l_cur_get_salesmember);
1076
1077 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1078 IF l_debug THEN
1079 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_ORG_MANAGER_PVT - Column Values');
1080 END IF;
1081 END IF;
1082
1083
1084 -- initializing return status as this is always come up as 'S'
1085 x_return_status := FND_API.G_RET_STS_ERROR;
1086 Loop
1087 If (dbms_sql.fetch_rows(l_cur_get_salesmember) > 0) Then
1088 get_salesmem_column_values(l_cur_get_salesmember, l_sales_member_rec);
1089 l_curr_row := l_curr_row + 1;
1090 x_sales_member_tbl(l_curr_row) := l_sales_member_rec;
1091 x_return_status := FND_API.G_RET_STS_SUCCESS;
1092 Else
1093 Exit;
1094 End if;
1095 End Loop;
1096
1097 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1098 IF l_debug THEN
1099 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_ORG_MANAGER_PVT - Close cursor');
1100 END IF;
1101 END IF;
1102
1103 DBMS_SQL.Close_Cursor(l_cur_get_salesmember);
1104
1105 EXCEPTION
1106
1107 WHEN NO_DATA_FOUND THEN
1108
1109 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1110 THEN
1111 --FND_MESSAGE.Set_Name('AS', 'Pvt Pipeline API: Cannot find salesrep'); -- MMSG
1112 --FND_MSG_PUB.ADD;
1113 IF l_debug THEN
1114 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'AS_SALES_MEMBER_PVT: Cannot find user');
1115 END IF;
1116 END IF;
1117
1118 x_return_status := FND_API.G_RET_STS_ERROR;
1119
1120 FND_MSG_PUB.Count_And_Get
1121 ( p_count => x_msg_count,
1122 p_data => x_msg_data
1123 );
1124
1125 return;
1126
1127 End; --end for the above block
1128
1129 -- End of API body.
1130 --
1131
1132 -- Debug Message
1133 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1134 THEN
1135 IF l_debug THEN
1136 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'AS_SALES_MEMBER_PVT.Get_Sales_Members End');
1137 END IF;
1138 --FND_MESSAGE.Set_Name('AS_SALES_MEMBER_PVT.Get_CurrentUser End');
1139 --FND_MSG_PUB.Add;
1140 END IF;
1141
1142 -- Standard call to get message count and if count is 1, get message info.
1143 FND_MSG_PUB.Count_And_Get
1144 ( p_count => x_msg_count,
1145 p_data => x_msg_data
1146 );
1147
1148
1149 EXCEPTION
1150
1151 WHEN FND_API.G_EXC_ERROR THEN
1152 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1153 P_MODULE => l_module
1154 ,P_API_NAME => L_API_NAME
1155 ,P_PKG_NAME => G_PKG_NAME
1156 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1157 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1158 ,P_ROLLBACK_FLAG => 'N'
1159 ,X_MSG_COUNT => X_MSG_COUNT
1160 ,X_MSG_DATA => X_MSG_DATA
1161 ,X_RETURN_STATUS => X_RETURN_STATUS);
1162
1163 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1164 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1165 P_MODULE => l_module
1166 ,P_API_NAME => L_API_NAME
1167 ,P_PKG_NAME => G_PKG_NAME
1168 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1169 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1170 ,P_ROLLBACK_FLAG => 'N'
1171 ,X_MSG_COUNT => X_MSG_COUNT
1172 ,X_MSG_DATA => X_MSG_DATA
1173 ,X_RETURN_STATUS => X_RETURN_STATUS);
1174
1175 WHEN OTHERS THEN
1176 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1177 P_MODULE => l_module
1178 ,P_API_NAME => L_API_NAME
1179 ,P_PKG_NAME => G_PKG_NAME
1180 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1181 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1182 ,P_ROLLBACK_FLAG => 'N'
1183 ,X_MSG_COUNT => X_MSG_COUNT
1184 ,X_MSG_DATA => X_MSG_DATA
1185 ,X_RETURN_STATUS => X_RETURN_STATUS);
1186
1187 END Get_Sales_members;
1188
1189 -- Start of Comments
1190 --
1191 -- Function name : Get_Sales_Relation
1192 -- Type : Private
1193 -- Function : Return relation between two sales: Firstline manager(G_FIRSTLINE_MANAGER)
1194 -- higher level manager(G_HIGHER_MANAGER) and no relationship between them
1195 -- (G_NO_RELATION).
1196 --
1197 -- Pre-reqs : None
1198 -- Paramaeters :
1199 -- p_identity_salesforce_id IN NUMBER Required
1200 -- p_salesrep_salesforce_id IN NUMBER Optional
1201 -- DEFAULT FND_API.G_MISS_NUM
1202 -- Version :
1203 --
1204 --
1205 -- Note :
1206 -- Cases:
1207 -- 1. If p_salesrep_salesforce_id is NULL or FND_API.G_MISS_NUM, this function will
1208 -- take p_identity_salesforce_id as a root and check all relations under it and
1209 -- determine if p_identity_salesforce_id is the firstline manager or not.
1210 -- 2. If p_salesrep_salesforce_id is not NULL, the function will only check the
1211 -- relation between them.
1212 -- Example:
1213 -- Give the relation map like this:
1214 -- Manager A
1215 -- |
1216 -- / \
1217 -- / \
1218 -- Sales rep B Manager C
1219 -- |
1220 -- Sales rep D
1221 -- For above example, if you pass in Manager A as p_identity_salesforce_id, and not pass in
1222 -- p_salesrep_salesforce_id, Manager A will be higher level manager
1223 -- if you pass in Manager A as p_identity_salesforce_id, and Sales rep B as p_salesrep_salesforce_id
1224 -- Manager A will be firstline manager.
1225 /*
1226 FUNCTION Get_Sales_Relations
1227 ( p_identity_salesforce_id IN NUMBER,
1228 p_salesrep_salesforce_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
1229 ) RETURN NUMBER IS
1230
1231 CURSOR l_manager_sales_rel_csr (c_manager_id NUMBER, c_sales_id NUMBER) IS
1232 SELECT rm.reports_to_flag reports_to_flag
1233 FROM as_rep_managers_v rm, as_salesforce_v sf1, as_salesforce_v sf2
1234 WHERE rm.manager_person_id = sf1.employee_person_id
1235 AND rm.person_id = sf2.employee_person_id
1236 AND sf1.salesforce_id = c_manager_id
1237 AND sf2.salesforce_id = c_sales_id;
1238
1239 CURSOR l_sales_org_csr (c_manager_id NUMBER) IS
1240 SELECT rm.reports_to_flag reports_to_flag
1241 FROM as_rep_managers_v rm, as_salesforce_v sf
1242 WHERE rm.manager_person_id = sf.employee_person_id
1243 AND rm.person_id <> sf.employee_person_id
1244 AND sf.salesforce_id = c_manager_id;
1245
1246 l_sales_relations NUMBER;
1247 l_flag VARCHAR2(1);
1248 l_counter NUMBER := 0;
1249 BEGIN
1250 IF (p_salesrep_salesforce_id IS NULL OR p_salesrep_salesforce_id = FND_API.G_MISS_NUM) THEN
1251 l_sales_relations := G_SALESREP;
1252 FOR l_reports_to_flag IN l_sales_org_csr(p_identity_salesforce_id) LOOP
1253 l_counter := l_counter + 1;
1254 IF l_reports_to_flag.reports_to_flag = 'N' THEN
1255 l_sales_relations := G_HIGHER_MANAGER;
1256 EXIT;
1257 Elsif l_reports_to_flag.reports_to_flag = 'Y' THEN
1258 l_sales_relations := G_FIRSTLINE_MANAGER;
1259 END IF;
1260 END LOOP;
1261 Else
1262 IF (p_identity_salesforce_id = p_salesrep_salesforce_id) THEN
1263 l_sales_relations := G_IDENTICAL_SALESFORCE;
1264 Else
1265 OPEN l_manager_sales_rel_csr(p_identity_salesforce_id, p_salesrep_salesforce_id);
1266 FETCH l_manager_sales_rel_csr INTO l_flag;
1267 IF l_manager_sales_rel_csr%NOTFOUND THEN
1268 l_sales_relations := G_NO_RELATION;
1269 Elsif (l_flag = 'Y') THEN
1270 l_sales_relations := G_FIRSTLINE_MANAGER;
1271 Else
1272 l_sales_relations := G_HIGHER_MANAGER;
1273 END IF;
1274 CLOSE l_manager_sales_rel_csr;
1275 END IF;
1276 END IF;
1277 RETURN l_sales_relations;
1278 END Get_Sales_Relations;
1279 */
1280 -- This function is to fix bug 855326
1281 -- Check what the relation between a salesforce and a sales group
1282 -- The possible return value is
1283 -- E -- The salesforce is a salesrep in this sales group
1284 -- M -- The salesforce is a manager for this sales group
1285 -- A -- The salesforce is a administrator for this sales group
1286 -- N -- The salesforce is no relation with this sales group
1287 FUNCTION Get_Member_Role(p_salesforce_id NUMBER,
1288 p_sales_group_id NUMBER) RETURN VARCHAR2 IS
1289 -- change to use jtf_rs_group_members
1290 CURSOR l_employee_role_cursor(c_salesforce_id NUMBER,
1291 c_sales_group_id NUMBER) IS
1292 SELECT 'Y'
1293 FROM jtf_rs_group_members mem, jtf_rs_role_relations rrel, jtf_rs_roles_b role
1294 WHERE rrel.role_id = role.role_id
1295 and role.member_flag = 'Y'
1296 and rrel.role_resource_id = mem.group_member_id
1297 and mem.resource_id = c_salesforce_id
1298 and group_id IN
1299 (SELECT parent_group_id
1300 FROM jtf_rs_groups_denorm
1301 WHERE group_id = c_sales_group_id);
1302 -- Change to use jtf_rs_group_members, jtf_rs_roles_b and jtf_rs_role_relations
1303 CURSOR l_manager_role_cursor(c_salesforce_id NUMBER,
1304 c_sales_group_id NUMBER) IS
1305 SELECT 'Y'
1306 FROM jtf_rs_group_members mem, jtf_rs_role_relations rrel, jtf_rs_roles_b role
1307 WHERE rrel.role_id = role.role_id
1308 and role.manager_flag = 'Y'
1309 and rrel.role_resource_id = mem.group_member_id
1310 and mem.resource_id = c_salesforce_id
1311 and group_id IN
1312 (SELECT parent_group_id
1313 FROM jtf_rs_groups_denorm
1314 WHERE group_id = c_sales_group_id);
1315 CURSOR l_admin_role_cursor(c_salesforce_id NUMBER,
1316 c_sales_group_id NUMBER) IS
1317 SELECT 'Y'
1318 FROM jtf_rs_group_members mem, jtf_rs_role_relations rrel, jtf_rs_roles_b role
1319 WHERE rrel.role_id = role.role_id
1320 and role.admin_flag = 'Y'
1321 and rrel.role_resource_id = mem.group_member_id
1322 and mem.resource_id = c_salesforce_id
1323 and group_id IN
1324 (SELECT parent_group_id
1325 FROM jtf_rs_groups_denorm
1326 WHERE group_id = c_sales_group_id);
1327 l_member_role VARCHAR2(1);
1328 l_flag VARCHAR2(1);
1329 BEGIN
1330 l_member_role := 'N';
1331 OPEN l_employee_role_cursor(p_salesforce_id, p_sales_group_id);
1332 FETCH l_employee_role_cursor INTO l_flag;
1333 IF l_employee_role_cursor%FOUND THEN
1334 l_member_role := 'E';
1335 END IF;
1336 CLOSE l_employee_role_cursor;
1337 OPEN l_manager_role_cursor(p_salesforce_id, p_sales_group_id);
1338 FETCH l_manager_role_cursor INTO l_flag;
1339 IF l_manager_role_cursor%FOUND THEN
1340 l_member_role := 'M';
1341 END IF;
1342 CLOSE l_manager_role_cursor;
1343 OPEN l_admin_role_cursor(p_salesforce_id, p_sales_group_id);
1344 FETCH l_admin_role_cursor INTO l_flag;
1345 IF l_admin_role_cursor%FOUND THEN
1346 l_member_role := 'A';
1347 END IF;
1348 CLOSE l_admin_role_cursor;
1349 RETURN l_member_role;
1350 END Get_Member_Role;
1351
1352 END AS_SALES_ORG_MANAGER_PVT;