[Home] [Help]
PACKAGE BODY: APPS.AS_OPP_SALES_CREDIT_PVT
Source
1 PACKAGE BODY AS_OPP_SALES_CREDIT_PVT as
2 /* $Header: asxvlscb.pls 120.6 2005/12/27 21:26:54 subabu ship $ */
3 -- Start of Comments
4 -- Package name : AS_OPP_SALES_CREDIT_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_OPP_SALES_CREDIT_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxvlscb.pls';
13
14
15 FUNCTION get_prob_bucket(p_win_probability IN NUMBER) RETURN NUMBER IS
16
17 l_retVal NUMBER;
18 l_win_probability NUMBER;
19
20 BEGIN
21 l_retVal := 0;
22
23 l_win_probability := nvl(p_win_probability, -1);
24
25 IF 0 <= l_win_probability AND l_win_probability < 40 THEN
26 l_retVal := 1;
27 ELSIF 40 <= l_win_probability AND l_win_probability < 60 THEN
28 l_retVal := 2;
29 ELSIF 60 <= l_win_probability AND l_win_probability < 80 THEN
30 l_retVal := 3;
31 ELSIF 80 <= l_win_probability THEN
32 l_retVal := 4;
33 END IF;
34
35 RETURN l_retVal;
36 END get_prob_bucket;
37
38
39 FUNCTION Apply_Forecast_Defaults(
40 p_old_win_probability IN NUMBER,
41 p_old_win_loss_indicator IN VARCHAR2,
42 p_old_forecast_rollup_flag IN VARCHAR2,
43 p_old_sales_credit_amount IN NUMBER,
44 p_win_probability IN NUMBER,
45 p_win_loss_indicator IN VARCHAR2,
46 p_forecast_rollup_flag IN VARCHAR2,
47 p_sales_credit_amount IN NUMBER,
48 p_Trigger_Mode IN VARCHAR2,
49 x_opp_worst_forecast_amount IN OUT NOCOPY NUMBER,
50 x_opp_forecast_amount IN OUT NOCOPY NUMBER,
51 x_opp_best_forecast_amount IN OUT NOCOPY NUMBER
52 )
53 RETURN BOOLEAN
54 IS
55 l_old_win_probability NUMBER;
56 l_old_win_loss_indicator VARCHAR2(8);
57 l_old_forecast_rollup_flag VARCHAR2(8);
58 l_old_sales_credit_amount NUMBER;
59 l_win_probability NUMBER;
60 l_win_loss_indicator VARCHAR2(8);
61 l_forecast_rollup_flag VARCHAR2(8);
62 l_sales_credit_amount NUMBER;
63 l_Trigger_Mode VARCHAR2(32);
64
65 l_apply_frcst_def BOOLEAN;
66 l_old_prob_bucket NUMBER;
67 l_prob_bucket NUMBER;
68 l_opp_worst_forecast_amount NUMBER;
69 l_opp_forecast_amount NUMBER;
70 l_opp_best_forecast_amount NUMBER;
71 l_defaulting_type VARCHAR2(64);
72 BEGIN
73 l_old_win_probability := nvl(p_old_win_probability, 0);
74 l_old_win_loss_indicator := nvl(p_old_win_loss_indicator, 'N');
75 l_old_forecast_rollup_flag := nvl(p_old_forecast_rollup_flag, 'N');
76 l_old_sales_credit_amount := nvl(p_old_sales_credit_amount, 0);
77 l_win_probability := nvl(p_win_probability, 0);
78 l_win_loss_indicator := nvl(p_win_loss_indicator, 'N');
79 l_forecast_rollup_flag := nvl(p_forecast_rollup_flag, 'N');
80 l_sales_credit_amount := nvl(p_sales_credit_amount, 0);
81 l_Trigger_Mode := nvl(p_Trigger_Mode, 'NONE');
82
83 l_apply_frcst_def := FALSE;
84 l_defaulting_type :=
85 nvl(FND_PROFILE.Value('ASN_FRCST_DEFAULTING_TYPE'), 'z');
86
87 IF nvl(l_forecast_rollup_flag, 'N') = 'Y' THEN
88 l_prob_bucket := get_prob_bucket(l_win_probability);
89 IF l_Trigger_Mode = 'ON-UPDATE' THEN
90 l_old_prob_bucket := get_prob_bucket(l_old_win_probability);
91
92 IF l_old_win_probability <> l_win_probability AND
93 l_win_loss_indicator <> 'W' AND
94 ( l_defaulting_type <> 'W'
95 OR l_old_prob_bucket <> l_prob_bucket)
96 THEN
97 l_apply_frcst_def := TRUE;
98 ELSIF l_old_win_loss_indicator <> l_win_loss_indicator AND
99 (l_old_win_loss_indicator = 'W' OR
100 l_win_loss_indicator = 'W') THEN
101 l_apply_frcst_def := TRUE;
102 ELSIF l_old_forecast_rollup_flag = 'N' THEN
103 l_apply_frcst_def := TRUE;
104 ELSIF l_old_sales_credit_amount <> l_sales_credit_amount THEN
105 l_apply_frcst_def := TRUE;
106 END IF;
107 ELSIF l_Trigger_Mode = 'ON-INSERT' THEN
108 l_apply_frcst_def := TRUE;
109 END IF;
110
111 IF l_apply_frcst_def THEN
112 IF l_win_loss_indicator = 'W' THEN
113 l_opp_worst_forecast_amount := l_sales_credit_amount;
114 l_opp_forecast_amount := l_sales_credit_amount;
115 l_opp_best_forecast_amount := l_sales_credit_amount;
116 ELSIF l_defaulting_type = 'W' THEN
117 IF l_prob_bucket = 1 THEN
118 l_opp_worst_forecast_amount := 0;
119 l_opp_forecast_amount := 0;
120 l_opp_best_forecast_amount := 0;
121 ELSIF l_prob_bucket = 2 THEN
122 l_opp_worst_forecast_amount := 0;
123 l_opp_forecast_amount := 0;
124 l_opp_best_forecast_amount := l_sales_credit_amount;
125 ELSIF l_prob_bucket = 3 THEN
126 l_opp_worst_forecast_amount := 0;
127 l_opp_forecast_amount := l_sales_credit_amount;
128 l_opp_best_forecast_amount := l_sales_credit_amount;
129 ELSE
130 l_opp_worst_forecast_amount := l_sales_credit_amount;
131 l_opp_forecast_amount := l_sales_credit_amount;
132 l_opp_best_forecast_amount := l_sales_credit_amount;
133 END IF;
134 ELSE
135 l_opp_worst_forecast_amount := 0;
136 l_opp_forecast_amount :=
137 l_sales_credit_amount*l_win_probability/100;
138 l_opp_best_forecast_amount := l_sales_credit_amount;
139 END IF;
140 x_opp_worst_forecast_amount := l_opp_worst_forecast_amount;
141 x_opp_forecast_amount := l_opp_forecast_amount;
142 x_opp_best_forecast_amount := l_opp_best_forecast_amount;
143 END IF; -- Of l_apply_frcst_def
144 END IF; -- of nvl(l_forecast_rollup_flag, 'z') = 'Y' ...
145 RETURN l_apply_frcst_def;
146 END Apply_Forecast_Defaults;
147
148
149 -- Hint: Primary key needs to be returned.
150 PROCEDURE Create_sales_credits(
151 P_Api_Version_Number IN NUMBER,
152 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
153 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
154 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
155 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
156 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
157 P_Admin_Group_Id IN NUMBER,
158 P_Identity_Salesforce_Id IN NUMBER := NULL,
159 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
160 p_partner_cont_party_id IN NUMBER := FND_API.G_MISS_NUM,
161 P_SALES_CREDIT_tbl IN AS_OPPORTUNITY_PUB.SALES_CREDIT_tbl_Type
162 := AS_OPPORTUNITY_PUB.G_MISS_SALES_CREDIT_tbl,
163 X_SALES_CREDIT_out_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.sales_credit_out_tbl_type,
164 X_Return_Status OUT NOCOPY VARCHAR2,
165 X_Msg_Count OUT NOCOPY NUMBER,
166 X_Msg_Data OUT NOCOPY VARCHAR2
167 )
168
169 IS
170
171 CURSOR lead_customer( p_lead_id NUMBER) IS
172 select customer_id, address_id
173 from as_leads
174 where lead_id = p_lead_id;
175
176 CURSOR C_Person_Id(p_resource_id NUMBER) IS
177 SELECT source_id
178 FROM JTF_RS_RESOURCE_EXTNS
179 WHERE resource_id = p_resource_id
180 AND category = 'EMPLOYEE';
181
182 CURSOR C_Partner_Id(p_resource_id NUMBER) IS
183 SELECT source_id, address_id
184 FROM JTF_RS_RESOURCE_EXTNS
185 WHERE resource_id = p_resource_id
186 AND (category = 'PARTNER'
187 OR category = 'PARTY');
188
189 -- solin, for bug 1554330
190 CURSOR c_get_opp_freeze_flag(c_LEAD_ID NUMBER) IS
191 SELECT FREEZE_FLAG
192 FROM AS_LEADS
193 WHERE LEAD_ID = c_LEAD_ID;
194 /*
195 Modified for bug# 4168544.
196 Change the select clause so that it returns team_leader_flag,last_update_date,access_id
197 instead of 'X'.
198 */
199 cursor get_dup_sales_team(c_customer_id NUMBER,c_address_id NUMBER,c_lead_id NUMBER,c_salesforce_id NUMBER, c_sales_group_id NUMBER ) is
200 select team_leader_flag,last_update_date,access_id
201 from as_accesses
202 where customer_id = c_customer_id
203 --and nvl(address_id, -99) = nvl(c_address_id, -99)
204 and nvl(lead_id, -99) = nvl(c_lead_id, -99)
205 and salesforce_id = c_salesforce_id
206 and nvl(sales_group_id, -99) = nvl(c_sales_group_id, -99);
207
208 -- Jean add here. Use to get sales group id
209 -- for partner contact resource type
210 CURSOR c_group_id(c_SALESFORCE_ID NUMBER) IS
211 -- SELECT decode(count(*), 1, to_char(max(c.group_id)), decode(fnd_profile.value_specific('ASF_DEFAULT_GROUP_ROLE', max(a.user_id)),'XXXXX',null,fnd_profile.value_specific('ASF_DEFAULT_GROUP_ROLE',max(a.user_id))))
212 -- SELECT decode(count(*), 1, to_char(max(c.group_id)))
213 -- SELECT to_char(max(c.group_id))
214 -- FROM jtf_rs_resource_extns a, fnd_user b, jtf_rs_group_members c
215 -- WHERE a.user_id = b.user_id
216 -- AND a.resource_id = c.resource_id
217 -- AND a.resource_id = c_SALESFORCE_ID;
218 -- fix the bug 2549218
219 SELECT to_char(max(c.group_id))
220 FROM jtf_rs_resource_extns a, fnd_user b, jtf_rs_group_members c, JTF_RS_ROLE_RELATIONS d , JTF_RS_ROLES_B e
221 WHERE a.user_id = b.user_id
222 AND a.resource_id = c.resource_id
223 AND e.ROLE_TYPE_CODE in ('SALES','TELESALES','FIELDSALES','PRM')
224 AND c.GROUP_MEMBER_ID = d.ROLE_RESOURCE_ID
225 AND d.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER' AND d.ROLE_ID = e.ROLE_ID
226 AND a.resource_id = c_SALESFORCE_ID;
227
228
229
230 l_api_name CONSTANT VARCHAR2(30) := 'Create_sales_credits';
231 l_api_version_number CONSTANT NUMBER := 2.0;
232 l_return_status_full VARCHAR2(1);
233 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
234 l_sales_credit_rec AS_OPPORTUNITY_PUB.SALES_CREDIT_rec_Type;
235 l_sales_credit_id NUMBER;
236 l_sales_credit_count CONSTANT NUMBER := P_Sales_Credit_Tbl.count;
237 l_update_access_flag VARCHAR2(1);
238 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
239
240 l_Sales_Team_Rec AS_ACCESS_PUB.Sales_Team_Rec_Type
241 := AS_ACCESS_PUB.G_MISS_SALES_TEAM_REC;
242
243
244
245 l_access_id NUMBER;
246 l_customer_id NUMBER;
247 l_address_id NUMBER;
248 l_freeze_flag VARCHAR2(1) := 'N'; -- solin, for bug 1554330
249 l_allow_flag VARCHAR2(1); -- solin, for bug 1554330
250 l_group_id_str VARCHAR2(50);
251 l_val VARCHAR2(1);
252 l_temp_bool BOOLEAN;
253 l_win_probability NUMBER;
254 l_win_loss_indicator as_statuses_b.win_loss_indicator%Type;
255 l_forecast_rollup_flag as_statuses_b.forecast_rollup_flag%Type;
256 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
257
258 l_upd_access_id NUMBER;
259 l_upd_team_flag VARCHAR2(1);
260 l_upd_date DATE;
261 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Create_sales_credits';
262
263 BEGIN
264 -- Standard Start of API savepoint
265 SAVEPOINT CREATE_SALES_CREDITS_PVT;
266
267 -- Standard call to check for call compatibility.
268 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
269 p_api_version_number,
270 l_api_name,
271 G_PKG_NAME)
272 THEN
273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
274 END IF;
275
276 --FND_MSG_PUB.G_MSG_LEVEL_THRESHOLD := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
277
278 -- Initialize message list if p_init_msg_list is set to TRUE.
279 IF FND_API.to_Boolean( p_init_msg_list )
280 THEN
281 FND_MSG_PUB.initialize;
282 END IF;
283
284
285 -- Debug Message
286 IF l_debug THEN
287 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
288 'Private API1 ' || l_api_name || ' start');
289 END IF;
290
291
292 -- Initialize API return status to SUCCESS
293 x_return_status := FND_API.G_RET_STS_SUCCESS;
294
295 --
296 -- API body
297 --
298
299 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
300 /*
301 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure
302 -- is invoked for customization purpose
303 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
304 THEN
305 AS_CALLOUT_PKG.Create_sales_credit_BC(
306 p_api_version_number => 2.0,
307 p_init_msg_list => FND_API.G_FALSE,
308 p_commit => FND_API.G_FALSE,
309 p_validation_level => p_validation_level,
310 P_SALES_CREDIT_Rec => P_SALES_CREDIT_Rec,
311 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
312 x_return_status => x_return_status,
313 x_msg_count => x_msg_count,
314 x_msg_data => x_msg_data);
315 END IF;
316 */
317
318 -- ******************************************************************
319 -- Validate Environment
320 -- ******************************************************************
321 IF FND_GLOBAL.User_Id IS NULL
322 THEN
323 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
324 THEN
325 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
326 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
327 FND_MSG_PUB.ADD;
328 END IF;
329 RAISE FND_API.G_EXC_ERROR;
330 END IF;
331
332 IF(P_Check_Access_Flag = 'Y') THEN
333 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
334 p_api_version_number => 2.0
335 ,p_init_msg_list => p_init_msg_list
336 ,p_salesforce_id => p_identity_salesforce_id
337 ,p_admin_group_id => p_admin_group_id
338 ,x_return_status => x_return_status
339 ,x_msg_count => x_msg_count
340 ,x_msg_data => x_msg_data
341 ,x_sales_member_rec => l_identity_sales_member_rec);
342
343 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
344 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
345 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
346 'Private API2 Get_CurrentUser fail');
347 END IF;
348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
349 END IF;
350
351
352 -- Call Get_Access_Profiles to get access_profile_rec
353 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
354 p_profile_tbl => p_profile_tbl,
355 x_access_profile_rec => l_access_profile_rec);
356
357 AS_ACCESS_PUB.has_updateOpportunityAccess
358 ( p_api_version_number => 2.0
359 ,p_init_msg_list => p_init_msg_list
360 ,p_validation_level => p_validation_level
361 ,p_access_profile_rec => l_access_profile_rec
362 ,p_admin_flag => p_admin_flag
363 ,p_admin_group_id => p_admin_group_id
364 ,p_person_id => l_identity_sales_member_rec.employee_person_id
365 ,p_opportunity_id => p_sales_credit_tbl(1).LEAD_ID
366 ,p_check_access_flag => p_check_access_flag
367 ,p_identity_salesforce_id => p_identity_salesforce_id
368 ,p_partner_cont_party_id => p_partner_cont_party_id
369 ,x_return_status => x_return_status
370 ,x_msg_count => x_msg_count
371 ,x_msg_data => x_msg_data
372 ,x_update_access_flag => l_update_access_flag );
373
374 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
375 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
376 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
377 'has_updateOpportunityAccess fail');
378 END IF;
379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
380 END IF;
381
382 IF (l_update_access_flag <> 'Y') THEN
383 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
384 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
385 FND_MESSAGE.Set_Token('INFO', 'CUSTOMER_ID,OPPORTUNITY_ID,SALESFORCE_ID', FALSE);
386 FND_MSG_PUB.ADD;
387 END IF;
388 RAISE FND_API.G_EXC_ERROR;
389 END IF;
390 END IF;
391
392 -- solin, for bug 1554330
393 OPEN c_get_opp_freeze_flag(p_sales_credit_tbl(1).LEAD_ID);
394 FETCH c_get_opp_freeze_flag INTO l_freeze_flag;
395 CLOSE c_get_opp_freeze_flag;
396
397 IF l_freeze_flag = 'Y'
398 THEN
399 l_allow_flag := NVL(FND_PROFILE.VALUE('AS_ALLOW_UPDATE_FROZEN_OPP'),'Y');
400 IF l_allow_flag <> 'Y' THEN
401 AS_UTILITY_PVT.Set_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
402 'API_OPP_FROZEN');
403 RAISE FND_API.G_EXC_ERROR;
404 END IF;
405 END IF;
406 -- end 1554330
407
408 FOR I in 1 .. l_sales_credit_count LOOP
409 -- Invoke table handler(AS_SALES_CREDITS_PKG.Insert_Row)
410
411 X_SALES_CREDIT_out_tbl(I).return_status := FND_API.G_RET_STS_SUCCESS;
412 l_SALES_CREDIT_ID := p_SALES_CREDIT_Tbl(I).SALES_CREDIT_ID;
413
414 -- Progress Message
415 --
416 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
417 THEN
418 --FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
419 --FND_MESSAGE.Set_Token ('ROW', 'AS_OPP_SALES_CREDIT', TRUE);
420 --FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(I), FALSE);
421 --FND_MSG_PUB.Add;
422 IF l_debug THEN
423 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
424 'Processing AS_OPP_SALES_CREDIT row number '||to_char(I));
425 END IF;
426 END IF;
427
428 l_sales_credit_rec := p_SALES_CREDIT_tbl(I);
429
430 -- Debug message
431 IF l_debug THEN
432 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
433 'Private API3 Validate_sales_credit');
434 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
435 l_sales_credit_rec.partner_customer_id);
436 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
437 l_sales_credit_rec.person_id);
438 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
439 l_sales_credit_rec.salesforce_id);
440 END IF;
441
442 IF nvl(l_sales_credit_rec.partner_customer_id, fnd_api.g_miss_num) = fnd_api.g_miss_num and
443 nvl(l_sales_credit_rec.person_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
444 THEN
445 open C_Person_Id(l_sales_credit_rec.salesforce_id);
446 fetch C_Person_Id into l_sales_credit_rec.person_id;
447 close C_Person_Id;
448 IF nvl(l_sales_credit_rec.person_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
449 THEN
450 open C_Partner_Id(l_sales_credit_rec.salesforce_id);
451 fetch C_Partner_Id into l_sales_credit_rec.partner_customer_id,
452 l_sales_credit_rec.partner_address_id;
453 close C_Partner_Id;
454 END IF;
455 END IF;
456 IF l_debug THEN
457 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
458 'Before sales group');
459 END IF;
460
461 -- Get partner contact's sales group id
462 IF nvl(l_sales_credit_rec.person_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
463 AND nvl(l_sales_credit_rec.salesgroup_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
464
465 THEN
466 open C_Group_Id(l_sales_credit_rec.salesforce_id);
467 fetch C_Group_Id into l_group_id_str;
468 close C_Group_Id;
469 END IF;
470 IF l_debug THEN
471 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
472 l_group_id_str);
473 END IF;
474
475
476 IF l_group_id_str IS NOT NULL
477 THEN
478 IF instr(l_group_id_str, '(') > 0
479 THEN
480 l_sales_credit_rec.salesgroup_id := to_number(substr(l_group_id_str, 1, instr(l_group_id_str, '(') - 1));
481 ELSE
482 l_sales_credit_rec.salesgroup_id := to_number(l_group_id_str);
483 END IF;
484
485 END IF;
486
487 -- Invoke validation procedures
488 Validate_sales_credit(
489 p_init_msg_list => FND_API.G_FALSE,
490 p_validation_level => p_validation_level,
491 p_validation_mode => AS_UTILITY_PVT.G_CREATE,
492 P_SALES_CREDIT_Rec => l_SALES_CREDIT_Rec,
493 x_return_status => x_return_status,
494 x_msg_count => x_msg_count,
495 x_msg_data => x_msg_data);
496
497 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
498 IF l_debug THEN
499 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
500 'Private API4 Validate_sales_credit fail');
501 END IF;
502 RAISE FND_API.G_EXC_ERROR;
503 END IF;
504
505
506 -- Debug Message
507 IF l_debug THEN
508 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
509 'Private API5 Calling create table handler');
510 END IF;
511
512 Select lead.win_probability, status.win_loss_indicator,
513 status.forecast_rollup_flag
514 Into l_win_probability, l_win_loss_indicator,
515 l_forecast_rollup_flag
516 From as_leads_all lead, as_statuses_vl status
517 Where lead_id = l_sales_credit_rec.LEAD_ID
518 And lead.status = status.status_code(+);
519
520 l_temp_bool := Apply_Forecast_Defaults(l_win_probability,
521 l_win_loss_indicator, 'N', -11, l_win_probability,
522 l_win_loss_indicator, l_forecast_rollup_flag,
523 l_sales_credit_rec.CREDIT_AMOUNT, 'ON-INSERT',
524 l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT,
525 l_sales_credit_rec.OPP_FORECAST_AMOUNT,
526 l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT);
527 -- Begin Added for ASNB
528 IF (l_sales_credit_rec.DEFAULTED_FROM_OWNER_FLAG IS NULL or
529 l_sales_credit_rec.DEFAULTED_FROM_OWNER_FLAG = FND_API.G_MISS_CHAR) AND
530 nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'), 'N') = 'Y' AND
531 nvl(fnd_profile.value('AS_FORECAST_CREDIT_TYPE_ID'), 'N') = l_sales_credit_rec.CREDIT_TYPE_ID
532 THEN
533 l_sales_credit_rec.DEFAULTED_FROM_OWNER_FLAG := 'Y';
534 END IF;
535 -- End Added for ASNB
536 AS_SALES_CREDITS_PKG.Insert_Row(
537 px_SALES_CREDIT_ID => l_SALES_CREDIT_ID,
538 p_LAST_UPDATE_DATE => SYSDATE,
539 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
540 p_CREATION_DATE => SYSDATE,
541 p_CREATED_BY => FND_GLOBAL.USER_ID,
542 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
543 p_REQUEST_ID => l_sales_credit_rec.REQUEST_ID,
544 p_PROGRAM_APPLICATION_ID => l_sales_credit_rec.PROGRAM_APPLICATION_ID,
545 p_PROGRAM_ID => l_sales_credit_rec.PROGRAM_ID,
546 p_PROGRAM_UPDATE_DATE => l_sales_credit_rec.PROGRAM_UPDATE_DATE,
547 p_LEAD_ID => l_sales_credit_rec.LEAD_ID,
548 p_LEAD_LINE_ID => l_sales_credit_rec.LEAD_LINE_ID,
549 p_SALESFORCE_ID => l_sales_credit_rec.SALESFORCE_ID,
550 p_PERSON_ID => l_sales_credit_rec.PERSON_ID,
551 p_SALESGROUP_ID => l_sales_credit_rec.SALESGROUP_ID,
552 p_PARTNER_CUSTOMER_ID => l_sales_credit_rec.PARTNER_CUSTOMER_ID,
553 p_PARTNER_ADDRESS_ID => l_sales_credit_rec.PARTNER_ADDRESS_ID,
554 p_REVENUE_AMOUNT => l_sales_credit_rec.REVENUE_AMOUNT,
555 p_REVENUE_PERCENT => l_sales_credit_rec.REVENUE_PERCENT,
556 p_QUOTA_CREDIT_AMOUNT => l_sales_credit_rec.QUOTA_CREDIT_AMOUNT,
557 p_QUOTA_CREDIT_PERCENT => l_sales_credit_rec.QUOTA_CREDIT_PERCENT,
558 p_ATTRIBUTE_CATEGORY => l_sales_credit_rec.ATTRIBUTE_CATEGORY,
559 p_ATTRIBUTE1 => l_sales_credit_rec.ATTRIBUTE1,
560 p_ATTRIBUTE2 => l_sales_credit_rec.ATTRIBUTE2,
561 p_ATTRIBUTE3 => l_sales_credit_rec.ATTRIBUTE3,
562 p_ATTRIBUTE4 => l_sales_credit_rec.ATTRIBUTE4,
563 p_ATTRIBUTE5 => l_sales_credit_rec.ATTRIBUTE5,
564 p_ATTRIBUTE6 => l_sales_credit_rec.ATTRIBUTE6,
565 p_ATTRIBUTE7 => l_sales_credit_rec.ATTRIBUTE7,
566 p_ATTRIBUTE8 => l_sales_credit_rec.ATTRIBUTE8,
567 p_ATTRIBUTE9 => l_sales_credit_rec.ATTRIBUTE9,
568 p_ATTRIBUTE10 => l_sales_credit_rec.ATTRIBUTE10,
569 p_ATTRIBUTE11 => l_sales_credit_rec.ATTRIBUTE11,
570 p_ATTRIBUTE12 => l_sales_credit_rec.ATTRIBUTE12,
571 p_ATTRIBUTE13 => l_sales_credit_rec.ATTRIBUTE13,
572 p_ATTRIBUTE14 => l_sales_credit_rec.ATTRIBUTE14,
573 p_ATTRIBUTE15 => l_sales_credit_rec.ATTRIBUTE15,
574 p_MANAGER_REVIEW_FLAG => l_sales_credit_rec.MANAGER_REVIEW_FLAG,
575 p_MANAGER_REVIEW_DATE => l_sales_credit_rec.MANAGER_REVIEW_DATE,
576 p_ORIGINAL_SALES_CREDIT_ID => l_sales_credit_rec.ORIGINAL_SALES_CREDIT_ID,
577 -- p_CREDIT_TYPE => l_sales_credit_rec.CREDIT_TYPE,
578 p_CREDIT_PERCENT => l_sales_credit_rec.CREDIT_PERCENT,
579 p_CREDIT_AMOUNT => l_sales_credit_rec.CREDIT_AMOUNT,
580 -- p_SECURITY_GROUP_ID => l_sales_credit_rec.SECURITY_GROUP_ID,
581 p_CREDIT_TYPE_ID => l_sales_credit_rec.CREDIT_TYPE_ID,
582 p_OPP_WORST_FORECAST_AMOUNT => l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT,
583 p_OPP_FORECAST_AMOUNT => l_sales_credit_rec.OPP_FORECAST_AMOUNT,
584 p_OPP_BEST_FORECAST_AMOUNT => l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT,
585 P_DEFAULTED_FROM_OWNER_FLAG =>l_sales_credit_rec.DEFAULTED_FROM_OWNER_FLAG -- -- Added for ASNB
586 );
587
588 -- Hint: Primary key should be returned.
589 -- x_SALES_CREDIT_ID := px_SALES_CREDIT_ID;
590
591 X_SALES_CREDIT_out_tbl(I).SALES_CREDIT_ID := l_SALES_CREDIT_ID;
592 X_SALES_CREDIT_out_tbl(I).return_status := x_return_status;
593
594 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
595 RAISE FND_API.G_EXC_ERROR;
596 ELSE
597 IF l_debug THEN
598 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
599 'Private API6 Created sales credit: ' ||l_SALES_CREDIT_ID );
600 END IF;
601
602 END IF;
603
604
605
606 -- Add access for the salesforce who is assigned the sales credit
607
608 OPEN lead_customer(l_sales_credit_rec.LEAD_ID);
609 FETCH lead_customer INTO l_customer_id, l_address_id;
610 CLOSE lead_customer;
611
612 OPEN get_dup_sales_team(l_customer_id,l_address_id ,l_sales_credit_rec.LEAD_ID , l_sales_credit_rec.SALESFORCE_ID, l_sales_credit_rec.SALESGROUP_ID );
613 FETCH get_dup_sales_team into l_upd_team_flag,l_upd_date,l_upd_access_id;
614 l_Sales_Team_Rec.team_leader_flag := FND_API.G_MISS_CHAR;
615 l_Sales_Team_Rec.lead_id := l_sales_credit_rec.LEAD_ID;
616 l_Sales_Team_Rec.customer_id := l_Customer_Id;
617 l_Sales_Team_Rec.address_id := l_Address_Id;
618 l_Sales_Team_Rec.salesforce_id := l_sales_credit_rec.SALESFORCE_ID;
619 l_sales_team_rec.sales_group_id := l_sales_credit_rec.SALESGROUP_ID;
620 l_sales_team_rec.person_id := l_sales_credit_rec.PERSON_ID;
621 l_sales_team_rec.partner_customer_id := l_sales_credit_rec.PARTNER_CUSTOMER_ID;
622 l_sales_team_rec.partner_address_id := l_sales_credit_rec.PARTNER_ADDRESS_ID;
623 IF get_dup_sales_team%NOTFOUND THEN
624 -- Jean 5/11, for bug 1610145
625 -- the following condition added for ASNB
626 IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'), 'N') = 'Y' then
627 l_Sales_Team_Rec.team_leader_flag := 'Y';
628 ELSE
629 IF(l_sales_credit_rec.CREDIT_TYPE_ID = FND_PROFILE.VALUE('AS_FORECAST_CREDIT_TYPE_ID') AND (l_sales_team_rec.partner_customer_id IS NULL OR l_sales_team_rec.partner_customer_id = FND_API.G_MISS_NUM))
630 THEN
631 l_Sales_Team_Rec.team_leader_flag := 'Y';
632 ELSE
633 l_Sales_Team_Rec.team_leader_flag := 'N';
634 END IF;
635 END IF;
636 -- end bug 1610145
637
638 l_Sales_Team_Rec.reassign_flag := 'N';
639 l_Sales_Team_Rec.freeze_flag :=
640 nvl(FND_PROFILE.Value('AS_DEFAULT_FREEZE_FLAG'), 'Y');
641
642 -- Debug Message
643 IF l_debug THEN
644 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
645 'Calling Create_SalesTeam');
646 END IF;
647
648 AS_ACCESS_PUB.Create_SalesTeam (
649 p_api_version_number => 2.0
650 ,p_init_msg_list => FND_API.G_FALSE
651 ,p_commit => FND_API.G_FALSE
652 ,p_validation_level => p_Validation_Level
653 ,p_access_profile_rec => l_access_profile_rec
654 ,p_check_access_flag => P_Check_Access_flag
655 ,p_admin_flag => P_Admin_Flag
656 ,p_admin_group_id => P_Admin_Group_Id
657 ,p_identity_salesforce_id => P_Identity_Salesforce_Id
658 ,p_sales_team_rec => l_Sales_Team_Rec
659 ,X_Return_Status => x_Return_Status
660 ,X_Msg_Count => X_Msg_Count
661 ,X_Msg_Data => X_Msg_Data
662 ,x_access_id => l_Access_Id
663 );
664
665 -- Debug Message
666 IF l_debug THEN
667 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
668 'Create_SalesTeam: l_access_id = ' || l_access_id);
669 END IF;
670
671 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
672 IF l_debug THEN
673 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
674 'Create_SalesTeam fail');
675 END IF;
676 RAISE FND_API.G_EXC_ERROR;
677 END IF;
678 -- The following else part added for ASNB
679 /*
680 Modified for bug# 4168544.
681 If the sales creditor exists in the sales team then update the
682 full access flag ie team_leader_flag to 'Y' if not already set.
683 */
684 ELSE -- get_dup_sales_team found
685 IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'), 'N') = 'Y' AND
686 nvl(l_upd_team_flag,'N') <> 'Y'
687 THEN
688 l_Sales_Team_Rec.last_update_date := l_upd_date;
689 l_Sales_Team_Rec.access_id := l_upd_access_id;
690 l_Sales_Team_Rec.team_leader_flag := 'Y';
691 AS_ACCESS_PUB.Update_SalesTeam (
692 p_api_version_number => 2.0
693 ,p_init_msg_list => FND_API.G_FALSE
694 ,p_commit => FND_API.G_FALSE
695 ,p_validation_level => p_Validation_Level
696 ,p_access_profile_rec => l_access_profile_rec
697 ,p_check_access_flag => P_Check_Access_flag
698 ,p_admin_flag => P_Admin_Flag
699 ,p_admin_group_id => P_Admin_Group_Id
700 ,p_identity_salesforce_id => P_Identity_Salesforce_Id
701 ,p_sales_team_rec => l_Sales_Team_Rec
702 ,X_Return_Status => x_Return_Status
703 ,X_Msg_Count => X_Msg_Count
704 ,X_Msg_Data => X_Msg_Data
705 ,x_access_id => l_Access_Id );
706
707 -- Debug Message
708 IF l_debug THEN
709 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
710 'update_SalesTeam: l_access_id = ' || l_access_id);
711 END IF;
712
713 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
714 IF l_debug THEN
715 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
716 'update_SalesTeam fail');
717 END IF;
718 RAISE FND_API.G_EXC_ERROR;
719 END IF;
720 END IF;
721 END IF;
722 CLOSE get_dup_sales_team;
723 End LOOP;
724
725 --
726 -- End of API body
727 --
728
729 -- Standard check for p_commit
730 IF FND_API.to_Boolean( p_commit )
731 THEN
732 COMMIT WORK;
733 END IF;
734
735
736 -- Debug Message
737 IF l_debug THEN
738 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
739 'Private API7 ' || l_api_name || 'end');
740 END IF;
741
742
743 -- Standard call to get message count and if count is 1, get message info.
744 FND_MSG_PUB.Count_And_Get
745 ( p_count => x_msg_count,
746 p_data => x_msg_data
747 );
748
749 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
750 /*
751 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout
752 -- procedure is invoked for customization purpose
753 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
754 THEN
755 AS_CALLOUT_PKG.Create_sales_credit_AC(
756 p_api_version_number => 2.0,
757 p_init_msg_list => FND_API.G_FALSE,
758 p_commit => FND_API.G_FALSE,
759 p_validation_level => p_validation_level,
760 P_SALES_CREDIT_Rec => P_SALES_CREDIT_Rec,
761 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
762 x_return_status => x_return_status,
763 x_msg_count => x_msg_count,
764 x_msg_data => x_msg_data);
765 END IF;
766 */
767 EXCEPTION
768 WHEN FND_API.G_EXC_ERROR THEN
769 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
770 P_MODULE => l_module
771 ,P_API_NAME => L_API_NAME
772 ,P_PKG_NAME => G_PKG_NAME
773 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
774 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
775 ,X_MSG_COUNT => X_MSG_COUNT
776 ,X_MSG_DATA => X_MSG_DATA
777 ,X_RETURN_STATUS => X_RETURN_STATUS);
778
779 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
780 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
781 P_MODULE => l_module
782 ,P_API_NAME => L_API_NAME
783 ,P_PKG_NAME => G_PKG_NAME
784 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
785 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
786 ,X_MSG_COUNT => X_MSG_COUNT
787 ,X_MSG_DATA => X_MSG_DATA
788 ,X_RETURN_STATUS => X_RETURN_STATUS);
789
790 WHEN OTHERS THEN
791 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
792 P_MODULE => l_module
793 ,P_API_NAME => L_API_NAME
794 ,P_PKG_NAME => G_PKG_NAME
795 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
796 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
797 ,X_MSG_COUNT => X_MSG_COUNT
798 ,X_MSG_DATA => X_MSG_DATA
799 ,X_RETURN_STATUS => X_RETURN_STATUS);
800 End Create_sales_credits;
801
802
803 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
804 PROCEDURE Update_sales_credits(
805 P_Api_Version_Number IN NUMBER,
806 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
807 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
808 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
809 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
810 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
811 P_Admin_Group_Id IN NUMBER,
812 P_Identity_Salesforce_Id IN NUMBER := NULL,
813 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
814 p_partner_cont_party_id IN NUMBER := FND_API.G_MISS_NUM,
815 P_SALES_CREDIT_tbl IN AS_OPPORTUNITY_PUB.SALES_CREDIT_tbl_Type,
816 X_SALES_CREDIT_out_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.sales_credit_out_tbl_type,
817 X_Return_Status OUT NOCOPY VARCHAR2,
818 X_Msg_Count OUT NOCOPY NUMBER,
819 X_Msg_Data OUT NOCOPY VARCHAR2
820 )
821
822 IS
823
824 CURSOR C_Person_Id(p_resource_id NUMBER) IS
825 SELECT source_id
826 FROM JTF_RS_RESOURCE_EXTNS
827 WHERE resource_id = p_resource_id
828 AND category = 'EMPLOYEE';
829
830 CURSOR C_Partner_Id(p_resource_id NUMBER) IS
831 SELECT source_id, address_id
832 FROM JTF_RS_RESOURCE_EXTNS
833 WHERE resource_id = p_resource_id
834 AND category = 'PARTNER';
835
836 Cursor C_Get_sales_credit(c_SALES_CREDIT_ID Number) IS
837 Select rowid,
838 SALES_CREDIT_ID,
839 LAST_UPDATE_DATE,
840 LAST_UPDATED_BY,
841 CREATION_DATE,
842 CREATED_BY,
843 LAST_UPDATE_LOGIN,
844 REQUEST_ID,
845 PROGRAM_APPLICATION_ID,
846 PROGRAM_ID,
847 PROGRAM_UPDATE_DATE,
848 LEAD_ID,
849 LEAD_LINE_ID,
850 SALESFORCE_ID,
851 PERSON_ID,
852 SALESGROUP_ID,
853 PARTNER_CUSTOMER_ID,
854 PARTNER_ADDRESS_ID,
855 REVENUE_AMOUNT,
856 REVENUE_PERCENT,
857 QUOTA_CREDIT_AMOUNT,
858 QUOTA_CREDIT_PERCENT,
859 ATTRIBUTE_CATEGORY,
860 ATTRIBUTE1,
861 ATTRIBUTE2,
862 ATTRIBUTE3,
863 ATTRIBUTE4,
864 ATTRIBUTE5,
865 ATTRIBUTE6,
866 ATTRIBUTE7,
867 ATTRIBUTE8,
868 ATTRIBUTE9,
869 ATTRIBUTE10,
870 ATTRIBUTE11,
871 ATTRIBUTE12,
872 ATTRIBUTE13,
873 ATTRIBUTE14,
874 ATTRIBUTE15,
875 MANAGER_REVIEW_FLAG,
876 MANAGER_REVIEW_DATE,
877 ORIGINAL_SALES_CREDIT_ID,
878 -- CREDIT_TYPE,
879 CREDIT_PERCENT,
880 CREDIT_AMOUNT,
881 -- SECURITY_GROUP_ID,
882 CREDIT_TYPE_ID
883 From AS_SALES_CREDITS
884 WHERE SALES_CREDIT_ID = c_SALES_CREDIT_ID
885 -- Hint: Developer need to provide Where clause
886 For Update NOWAIT;
887
888 -- solin, for bug 1554330
889 CURSOR c_get_opp_freeze_flag(c_LEAD_ID NUMBER) IS
890 SELECT FREEZE_FLAG
891 FROM AS_LEADS
892 WHERE LEAD_ID = c_LEAD_ID;
893
894 CURSOR lead_customer( p_lead_id NUMBER) IS
895 select customer_id, address_id
896 from as_leads
897 where lead_id = p_lead_id;
898 /*
899 Modified for bug# 4168544.
900 Change the select clause so that it returns team_leader_flag,last_update_date,access_id
901 instead of 'X'.
902 */
903 cursor get_dup_sales_team(c_customer_id NUMBER,c_address_id NUMBER,c_lead_id NUMBER,c_salesforce_id NUMBER, c_sales_group_id NUMBER ) is
904 select team_leader_flag,last_update_date,access_id
905 from as_accesses
906 where customer_id = c_customer_id
907 --and nvl(address_id, -99) = nvl(c_address_id, -99)
908 and nvl(lead_id, -99) = nvl(c_lead_id, -99)
909 and salesforce_id = c_salesforce_id
910 and nvl(sales_group_id, -99) = nvl(c_sales_group_id, -99);
911
912 cursor get_dup_sales_partner(c_customer_id NUMBER,c_address_id NUMBER,c_lead_id NUMBER,c_salesforce_id NUMBER, c_sales_group_id NUMBER ) is
913 select 'X'
914 from as_accesses
915 where customer_id = c_customer_id
916 --and nvl(address_id, -99) = nvl(c_address_id, -99)
917 and nvl(lead_id, -99) = nvl(c_lead_id, -99)
918 and salesforce_id = c_salesforce_id;
919 --and nvl(sales_group_id, -99) = nvl(c_sales_group_id, -99);
920
921
922 l_api_name CONSTANT VARCHAR2(30) := 'Update_sales_credits';
923 l_api_version_number CONSTANT NUMBER := 2.0;
924 -- Local Variables
925 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
926 l_ref_SALES_CREDIT_rec AS_OPPORTUNITY_PUB.SALES_CREDIT_Rec_Type;
927 l_tar_SALES_CREDIT_rec AS_OPPORTUNITY_PUB.SALES_CREDIT_Rec_Type;
928 l_SALES_CREDIT_rec AS_OPPORTUNITY_PUB.SALES_CREDIT_Rec_Type;
929 l_rowid ROWID;
930 l_update_access_flag VARCHAR2(1);
931 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
932 l_freeze_flag VARCHAR2(1) := 'N'; -- solin, for bug 1554330
933 l_allow_flag VARCHAR2(1); -- solin, for bug 1554330
934 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
935
936 l_Sales_Team_Rec AS_ACCESS_PUB.Sales_Team_Rec_Type
937 := AS_ACCESS_PUB.G_MISS_SALES_TEAM_REC;
938 l_access_id NUMBER;
939 l_customer_id NUMBER;
940 l_address_id NUMBER;
941 l_val VARCHAR2(1);
942 l_temp_bool BOOLEAN;
943 l_win_probability NUMBER;
944 l_win_loss_indicator as_statuses_b.win_loss_indicator%Type;
945 l_forecast_rollup_flag as_statuses_b.forecast_rollup_flag%Type;
946
947 l_upd_access_id NUMBER;
948 l_upd_team_flag VARCHAR2(1);
949 l_upd_date DATE;
950 l_forecast_credit_type_id CONSTANT NUMBER := FND_PROFILE.Value('AS_FORECAST_CREDIT_TYPE_ID');
951 l_opp_worst_forecast_amount NUMBER;
952 l_opp_forecast_amount NUMBER;
953 l_opp_best_forecast_amount NUMBER;
954 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Update_sales_credits';
955
956 BEGIN
957 -- Standard Start of API savepoint
958 SAVEPOINT UPDATE_SALES_CREDITS_PVT;
959
960 -- Standard call to check for call compatibility.
961 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
962 p_api_version_number,
963 l_api_name,
964 G_PKG_NAME)
965 THEN
966 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
967 END IF;
968
969
970 -- Initialize message list if p_init_msg_list is set to TRUE.
971 IF FND_API.to_Boolean( p_init_msg_list )
972 THEN
973 FND_MSG_PUB.initialize;
974 END IF;
975
976
977 -- Debug Message
978 IF l_debug THEN
979 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
980 'Private API8 ' || l_api_name || ' start');
981 END IF;
982
983
984
985 -- Initialize API return status to SUCCESS
986 x_return_status := FND_API.G_RET_STS_SUCCESS;
987
988 --
989 -- Api body
990 --
991 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
992 /*
993 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout
994 -- procedure is invoked for customization purpose
995 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
996 THEN
997 AS_CALLOUT_PKG.Update_sales_credit_BU(
998 p_api_version_number => 2.0,
999 p_init_msg_list => FND_API.G_FALSE,
1000 p_commit => FND_API.G_FALSE,
1001 p_validation_level => p_validation_level,
1002 p_identity_salesforce_id => p_identity_salesforce_id,
1003 P_SALES_CREDIT_Rec => P_SALES_CREDIT_Rec,
1004 x_return_status => x_return_status,
1005 x_msg_count => x_msg_count,
1006 x_msg_data => x_msg_data);
1007 END IF;
1008 */
1009
1010 IF(P_Check_Access_Flag = 'Y') THEN
1011 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1012 p_api_version_number => 2.0
1013 ,p_init_msg_list => p_init_msg_list
1014 ,p_salesforce_id => p_identity_salesforce_id
1015 ,p_admin_group_id => p_admin_group_id
1016 ,x_return_status => x_return_status
1017 ,x_msg_count => x_msg_count
1018 ,x_msg_data => x_msg_data
1019 ,x_sales_member_rec => l_identity_sales_member_rec);
1020 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1021 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1022 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1023 'Private API9 Get_CurrentUser fail');
1024 END IF;
1025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1026 END IF;
1027
1028 -- Call Get_Access_Profiles to get access_profile_rec
1029 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
1030 p_profile_tbl => p_profile_tbl,
1031 x_access_profile_rec => l_access_profile_rec);
1032
1033 AS_ACCESS_PUB.has_updateOpportunityAccess
1034 ( p_api_version_number => 2.0
1035 ,p_init_msg_list => p_init_msg_list
1036 ,p_validation_level => p_validation_level
1037 ,p_access_profile_rec => l_access_profile_rec
1038 ,p_admin_flag => p_admin_flag
1039 ,p_admin_group_id => p_admin_group_id
1040 ,p_person_id => l_identity_sales_member_rec.employee_person_id
1041 ,p_opportunity_id => p_sales_credit_tbl(1).LEAD_ID
1042 ,p_check_access_flag => p_check_access_flag
1043 ,p_identity_salesforce_id => p_identity_salesforce_id
1044 ,p_partner_cont_party_id => p_partner_cont_party_id
1045 ,x_return_status => x_return_status
1046 ,x_msg_count => x_msg_count
1047 ,x_msg_data => x_msg_data
1048 ,x_update_access_flag => l_update_access_flag );
1049
1050 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1051 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1052 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1053 'has_updateOpportunityAccess fail');
1054 END IF;
1055 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1056 END IF;
1057
1058 IF (l_update_access_flag <> 'Y') THEN
1059 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1060 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
1061 FND_MESSAGE.Set_Token('INFO', 'CUSTOMER_ID,OPPORTUNITY_ID,SALESFORCE_ID', FALSE);
1062 FND_MSG_PUB.ADD;
1063 END IF;
1064 RAISE FND_API.G_EXC_ERROR;
1065 END IF;
1066 END IF;
1067
1068 -- solin, for bug 1554330
1069 OPEN c_get_opp_freeze_flag(p_sales_credit_tbl(1).LEAD_ID);
1070 FETCH c_get_opp_freeze_flag INTO l_freeze_flag;
1071 CLOSE c_get_opp_freeze_flag;
1072
1073 IF l_freeze_flag = 'Y'
1074 THEN
1075 l_allow_flag := NVL(FND_PROFILE.VALUE('AS_ALLOW_UPDATE_FROZEN_OPP'),'Y');
1076 IF l_allow_flag <> 'Y' THEN
1077 AS_UTILITY_PVT.Set_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
1078 'API_OPP_FROZEN');
1079 RAISE FND_API.G_EXC_ERROR;
1080 END IF;
1081 END IF;
1082 -- end 1554330
1083
1084 FOR I in 1 .. P_SALES_CREDIT_tbl.count LOOP
1085
1086 X_SALES_CREDIT_out_tbl(I).return_status := FND_API.G_RET_STS_SUCCESS;
1087 l_tar_SALES_CREDIT_rec := P_SALES_CREDIT_tbl(I);
1088
1089 IF l_debug THEN
1090 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1091 'Private API10processing sales_credit_id: ' || P_SALES_CREDIT_tbl(I).sales_credit_id );
1092 END IF;
1093
1094
1095 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1096 THEN
1097 --FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
1098 --FND_MESSAGE.Set_Token ('ROW', 'AS_OPP_SALES_CREDIT', TRUE);
1099 --FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(I), FALSE);
1100 --FND_MSG_PUB.Add;
1101 IF l_debug THEN
1102 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1103 'Processing AS_OPP_SALES_CREDIT row number '||to_char(I));
1104 END IF;
1105 END IF;
1106
1107 l_sales_credit_rec := p_SALES_CREDIT_tbl(I);
1108
1109 -- Debug Message
1110 IF l_debug THEN
1111 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1112 'Private API11- Open Cursor to Select');
1113
1114 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1115 'Private API1 SALES_CREDIT_ID = ' ||l_tar_SALES_CREDIT_rec.SALES_CREDIT_ID);
1116 END IF;
1117
1118
1119 Open C_Get_sales_credit( l_tar_SALES_CREDIT_rec.SALES_CREDIT_ID);
1120
1121 Fetch C_Get_sales_credit into
1122 l_rowid,
1123 l_ref_SALES_CREDIT_rec.SALES_CREDIT_ID,
1124 l_ref_SALES_CREDIT_rec.LAST_UPDATE_DATE,
1125 l_ref_SALES_CREDIT_rec.LAST_UPDATED_BY,
1126 l_ref_SALES_CREDIT_rec.CREATION_DATE,
1127 l_ref_SALES_CREDIT_rec.CREATED_BY,
1128 l_ref_SALES_CREDIT_rec.LAST_UPDATE_LOGIN,
1129 l_ref_SALES_CREDIT_rec.REQUEST_ID,
1130 l_ref_SALES_CREDIT_rec.PROGRAM_APPLICATION_ID,
1131 l_ref_SALES_CREDIT_rec.PROGRAM_ID,
1132 l_ref_SALES_CREDIT_rec.PROGRAM_UPDATE_DATE,
1133 l_ref_SALES_CREDIT_rec.LEAD_ID,
1134 l_ref_SALES_CREDIT_rec.LEAD_LINE_ID,
1135 l_ref_SALES_CREDIT_rec.SALESFORCE_ID,
1136 l_ref_SALES_CREDIT_rec.PERSON_ID,
1137 l_ref_SALES_CREDIT_rec.SALESGROUP_ID,
1138 l_ref_SALES_CREDIT_rec.PARTNER_CUSTOMER_ID,
1139 l_ref_SALES_CREDIT_rec.PARTNER_ADDRESS_ID,
1140 l_ref_SALES_CREDIT_rec.REVENUE_AMOUNT,
1141 l_ref_SALES_CREDIT_rec.REVENUE_PERCENT,
1142 l_ref_SALES_CREDIT_rec.QUOTA_CREDIT_AMOUNT,
1143 l_ref_SALES_CREDIT_rec.QUOTA_CREDIT_PERCENT,
1144 l_ref_SALES_CREDIT_rec.ATTRIBUTE_CATEGORY,
1145 l_ref_SALES_CREDIT_rec.ATTRIBUTE1,
1146 l_ref_SALES_CREDIT_rec.ATTRIBUTE2,
1147 l_ref_SALES_CREDIT_rec.ATTRIBUTE3,
1148 l_ref_SALES_CREDIT_rec.ATTRIBUTE4,
1149 l_ref_SALES_CREDIT_rec.ATTRIBUTE5,
1150 l_ref_SALES_CREDIT_rec.ATTRIBUTE6,
1151 l_ref_SALES_CREDIT_rec.ATTRIBUTE7,
1152 l_ref_SALES_CREDIT_rec.ATTRIBUTE8,
1153 l_ref_SALES_CREDIT_rec.ATTRIBUTE9,
1154 l_ref_SALES_CREDIT_rec.ATTRIBUTE10,
1155 l_ref_SALES_CREDIT_rec.ATTRIBUTE11,
1156 l_ref_SALES_CREDIT_rec.ATTRIBUTE12,
1157 l_ref_SALES_CREDIT_rec.ATTRIBUTE13,
1158 l_ref_SALES_CREDIT_rec.ATTRIBUTE14,
1159 l_ref_SALES_CREDIT_rec.ATTRIBUTE15,
1160 l_ref_SALES_CREDIT_rec.MANAGER_REVIEW_FLAG,
1161 l_ref_SALES_CREDIT_rec.MANAGER_REVIEW_DATE,
1162 l_ref_SALES_CREDIT_rec.ORIGINAL_SALES_CREDIT_ID,
1163 -- l_ref_SALES_CREDIT_rec.CREDIT_TYPE,
1164 l_ref_SALES_CREDIT_rec.CREDIT_PERCENT,
1165 l_ref_SALES_CREDIT_rec.CREDIT_AMOUNT,
1166 -- l_ref_SALES_CREDIT_rec.SECURITY_GROUP_ID,
1167 l_ref_SALES_CREDIT_rec.CREDIT_TYPE_ID;
1168
1169 If ( C_Get_sales_credit%NOTFOUND) Then
1170
1171 IF l_debug THEN
1172 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1173 'Private14PI: C_Get_sales_credit%NOTFOUND ');
1174 END IF;
1175
1176 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1177 THEN
1178 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
1179 FND_MESSAGE.Set_Token ('INFO', 'sales_credit', FALSE);
1180 FND_MSG_PUB.Add;
1181 END IF;
1182 raise FND_API.G_EXC_ERROR;
1183 END IF;
1184 -- Debug Message
1185 IF l_debug THEN
1186 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Close Cursor');
1187 END IF;
1188 Close C_Get_sales_credit;
1189
1190 If (l_tar_SALES_CREDIT_rec.last_update_date is NULL or
1191 l_tar_SALES_CREDIT_rec.last_update_date = FND_API.G_MISS_Date ) Then
1192 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1193 THEN
1194 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1195 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
1196 FND_MSG_PUB.ADD;
1197 END IF;
1198 raise FND_API.G_EXC_ERROR;
1199 End if;
1200 -- Check Whether record has been changed by someone else
1201 If (l_tar_SALES_CREDIT_rec.last_update_date <> l_ref_SALES_CREDIT_rec.last_update_date) Then
1202 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1203 THEN
1204 FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
1205 FND_MESSAGE.Set_Token('INFO', 'sales_credit', FALSE);
1206 FND_MSG_PUB.ADD;
1207 END IF;
1208 raise FND_API.G_EXC_ERROR;
1209 End if;
1210
1211 -- Debug message
1212 IF l_debug THEN
1213 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1214 'Private API15Validate_sales_credit');
1215
1216 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1217 l_sales_credit_rec.partner_customer_id);
1218 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1219 l_sales_credit_rec.salesforce_id);
1220 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1221 l_sales_credit_rec.person_id);
1222 END IF;
1223
1224
1225 IF nvl(l_sales_credit_rec.partner_customer_id, fnd_api.g_miss_num) = fnd_api.g_miss_num and
1226 nvl(l_sales_credit_rec.person_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1227 THEN
1228 open C_Person_Id(l_sales_credit_rec.salesforce_id);
1229 fetch C_Person_Id into l_sales_credit_rec.person_id;
1230 close C_Person_Id;
1231 IF nvl(l_sales_credit_rec.person_id, fnd_api.g_miss_num) = fnd_api.g_miss_num
1232 THEN
1233 open C_Partner_Id(l_sales_credit_rec.salesforce_id);
1234 fetch C_Partner_Id into l_sales_credit_rec.partner_customer_id,
1235 l_sales_credit_rec.partner_address_id;
1236 close C_Partner_Id;
1237 END IF;
1238 END IF;
1239
1240 -- Invoke validation procedures
1241 Validate_sales_credit(
1242 p_init_msg_list => FND_API.G_FALSE,
1243 p_validation_level => p_validation_level,
1244 p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
1245 P_SALES_CREDIT_Rec => l_SALES_CREDIT_Rec,
1246 x_return_status => x_return_status,
1247 x_msg_count => x_msg_count,
1248 x_msg_data => x_msg_data);
1249
1250 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1251 IF l_debug THEN
1252 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1253 'Private API: Validate_sales_credit fail');
1254 END IF;
1255 RAISE FND_API.G_EXC_ERROR;
1256 END IF;
1257
1258 -- Hint: Add corresponding Master-Detail business logic here if necessary.
1259
1260 -- Debug Message
1261 IF l_debug THEN
1262 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1263 'Private API: Calling update table handler');
1264 END IF;
1265
1266 Select lead.win_probability, status.win_loss_indicator,
1267 status.forecast_rollup_flag
1268 Into l_win_probability, l_win_loss_indicator,
1269 l_forecast_rollup_flag
1270 From as_leads_all lead, as_statuses_vl status
1271 Where lead_id = l_sales_credit_rec.LEAD_ID
1272 And lead.status = status.status_code(+);
1273
1274 IF l_sales_credit_rec.CREDIT_TYPE_ID = l_forecast_credit_type_id THEN
1275 -- No change in BFW values for quota creditssince this API is called in
1276 -- R12 Telesales to only change the credit owner and not anything else.
1277 l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT := NULL;
1278 l_sales_credit_rec.OPP_FORECAST_AMOUNT := NULL;
1279 l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT := NULL;
1280 ELSE
1281 l_opp_worst_forecast_amount := NULL;
1282 l_opp_forecast_amount := NULL;
1283 l_opp_best_forecast_amount := NULL;
1284 IF l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT <> FND_API.G_MISS_NUM THEN
1285 l_opp_worst_forecast_amount := l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT;
1286 END IF;
1287 IF l_sales_credit_rec.OPP_FORECAST_AMOUNT <> FND_API.G_MISS_NUM THEN
1288 l_opp_forecast_amount := l_sales_credit_rec.OPP_FORECAST_AMOUNT;
1289 END IF;
1290 IF l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT <> FND_API.G_MISS_NUM THEN
1291 l_opp_best_forecast_amount := l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT;
1292 END IF;
1293
1294 IF l_opp_worst_forecast_amount IS NULL OR
1295 l_opp_forecast_amount IS NULL OR
1296 l_opp_best_forecast_amount IS NULL
1297 THEN
1298 l_temp_bool := Apply_Forecast_Defaults(l_win_probability,
1299 l_win_loss_indicator, l_forecast_rollup_flag, -11,
1300 l_win_probability,
1301 l_win_loss_indicator, l_forecast_rollup_flag,
1302 l_sales_credit_rec.CREDIT_AMOUNT, 'ON-UPDATE',
1303 l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT,
1304 l_sales_credit_rec.OPP_FORECAST_AMOUNT,
1305 l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT);
1306
1307 -- Override manual values
1308 IF l_opp_worst_forecast_amount IS NOT NULL THEN
1309 l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT := l_opp_worst_forecast_amount;
1310 END IF;
1311 IF l_opp_forecast_amount IS NOT NULL THEN
1312 l_sales_credit_rec.OPP_FORECAST_AMOUNT := l_opp_forecast_amount;
1313 END IF;
1314 IF l_opp_best_forecast_amount IS NOT NULL THEN
1315 l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT := l_opp_best_forecast_amount;
1316 END IF;
1317 END IF;
1318 END IF;
1319
1320 -- Begin Added for ASNB
1321 IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'), 'N') = 'Y'
1322 and (l_sales_credit_rec.DEFAULTED_FROM_OWNER_FLAG IS NULL or
1323 l_sales_credit_rec.DEFAULTED_FROM_OWNER_FLAG = FND_API.G_MISS_CHAR)
1324 THEN
1325 IF nvl(fnd_profile.value('AS_FORECAST_CREDIT_TYPE_ID'), -1) = l_sales_credit_rec.CREDIT_TYPE_ID
1326 THEN
1327 l_sales_credit_rec.DEFAULTED_FROM_OWNER_FLAG := 'Y';
1328 ELSE
1329 l_sales_credit_rec.DEFAULTED_FROM_OWNER_FLAG := 'N';
1330 END IF;
1331 END IF;
1332 -- End Added for ASNB
1333 -- Invoke table handler(AS_SALES_CREDITS_PKG.Update_Row)
1334 AS_SALES_CREDITS_PKG.Update_Row(
1335 p_SALES_CREDIT_ID => l_sales_credit_rec.SALES_CREDIT_ID,
1336 p_LAST_UPDATE_DATE => SYSDATE,
1337 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1338 p_CREATION_DATE => FND_API.G_MISS_DATE,
1339 p_CREATED_BY => FND_API.G_MISS_NUM,
1340 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1341 p_REQUEST_ID => l_sales_credit_rec.REQUEST_ID,
1342 p_PROGRAM_APPLICATION_ID => l_sales_credit_rec.PROGRAM_APPLICATION_ID,
1343 p_PROGRAM_ID => l_sales_credit_rec.PROGRAM_ID,
1344 p_PROGRAM_UPDATE_DATE => l_sales_credit_rec.PROGRAM_UPDATE_DATE,
1345 p_LEAD_ID => l_sales_credit_rec.LEAD_ID,
1346 p_LEAD_LINE_ID => l_sales_credit_rec.LEAD_LINE_ID,
1347 p_SALESFORCE_ID => l_sales_credit_rec.SALESFORCE_ID,
1348 p_PERSON_ID => l_sales_credit_rec.PERSON_ID,
1349 p_SALESGROUP_ID => l_sales_credit_rec.SALESGROUP_ID,
1350 p_PARTNER_CUSTOMER_ID => l_sales_credit_rec.PARTNER_CUSTOMER_ID,
1351 p_PARTNER_ADDRESS_ID => l_sales_credit_rec.PARTNER_ADDRESS_ID,
1352 p_REVENUE_AMOUNT => l_sales_credit_rec.REVENUE_AMOUNT,
1353 p_REVENUE_PERCENT => l_sales_credit_rec.REVENUE_PERCENT,
1354 p_QUOTA_CREDIT_AMOUNT => l_sales_credit_rec.QUOTA_CREDIT_AMOUNT,
1355 p_QUOTA_CREDIT_PERCENT => l_sales_credit_rec.QUOTA_CREDIT_PERCENT,
1356 p_ATTRIBUTE_CATEGORY => l_sales_credit_rec.ATTRIBUTE_CATEGORY,
1357 p_ATTRIBUTE1 => l_sales_credit_rec.ATTRIBUTE1,
1358 p_ATTRIBUTE2 => l_sales_credit_rec.ATTRIBUTE2,
1359 p_ATTRIBUTE3 => l_sales_credit_rec.ATTRIBUTE3,
1360 p_ATTRIBUTE4 => l_sales_credit_rec.ATTRIBUTE4,
1361 p_ATTRIBUTE5 => l_sales_credit_rec.ATTRIBUTE5,
1362 p_ATTRIBUTE6 => l_sales_credit_rec.ATTRIBUTE6,
1363 p_ATTRIBUTE7 => l_sales_credit_rec.ATTRIBUTE7,
1364 p_ATTRIBUTE8 => l_sales_credit_rec.ATTRIBUTE8,
1365 p_ATTRIBUTE9 => l_sales_credit_rec.ATTRIBUTE9,
1366 p_ATTRIBUTE10 => l_sales_credit_rec.ATTRIBUTE10,
1367 p_ATTRIBUTE11 => l_sales_credit_rec.ATTRIBUTE11,
1368 p_ATTRIBUTE12 => l_sales_credit_rec.ATTRIBUTE12,
1369 p_ATTRIBUTE13 => l_sales_credit_rec.ATTRIBUTE13,
1370 p_ATTRIBUTE14 => l_sales_credit_rec.ATTRIBUTE14,
1371 p_ATTRIBUTE15 => l_sales_credit_rec.ATTRIBUTE15,
1372 p_MANAGER_REVIEW_FLAG => l_sales_credit_rec.MANAGER_REVIEW_FLAG,
1373 p_MANAGER_REVIEW_DATE => l_sales_credit_rec.MANAGER_REVIEW_DATE,
1374 p_ORIGINAL_SALES_CREDIT_ID => l_sales_credit_rec.ORIGINAL_SALES_CREDIT_ID,
1375 -- p_CREDIT_TYPE => l_sales_credit_rec.CREDIT_TYPE,
1376 p_CREDIT_PERCENT => l_sales_credit_rec.CREDIT_PERCENT,
1377 p_CREDIT_AMOUNT => l_sales_credit_rec.CREDIT_AMOUNT,
1378 -- p_SECURITY_GROUP_ID => l_sales_credit_rec.SECURITY_GROUP_ID,
1379 p_CREDIT_TYPE_ID => l_sales_credit_rec.CREDIT_TYPE_ID,
1380 p_OPP_WORST_FORECAST_AMOUNT => l_sales_credit_rec.OPP_WORST_FORECAST_AMOUNT,
1381 p_OPP_FORECAST_AMOUNT => l_sales_credit_rec.OPP_FORECAST_AMOUNT,
1382 p_OPP_BEST_FORECAST_AMOUNT => l_sales_credit_rec.OPP_BEST_FORECAST_AMOUNT,
1383 P_DEFAULTED_FROM_OWNER_FLAG =>l_sales_credit_rec.DEFAULTED_FROM_OWNER_FLAG -- Added for ASNB
1384 );
1385
1386 X_SALES_CREDIT_out_tbl(I).SALES_CREDIT_ID := l_sales_credit_rec.SALES_CREDIT_ID;
1387 X_SALES_CREDIT_out_tbl(I).return_status := x_return_status;
1388
1389 IF l_debug THEN
1390 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1391 'Private API17: Updated sales credit: ' ||l_sales_credit_rec.SALES_CREDIT_ID );
1392 END IF;
1393
1394 if (l_sales_credit_rec.SALESGROUP_ID <> fnd_api.g_miss_num OR nvl(l_sales_credit_rec.PARTNER_CUSTOMER_ID, fnd_api.g_miss_num) <> fnd_api.g_miss_num)
1395 then
1396 -- Add access for the salesforce who is assigned the sales credit
1397
1398 OPEN lead_customer(l_sales_credit_rec.LEAD_ID);
1399 FETCH lead_customer INTO l_customer_id, l_address_id;
1400 CLOSE lead_customer;
1401
1402 IF nvl(l_sales_credit_rec.PARTNER_CUSTOMER_ID, fnd_api.g_miss_num) =fnd_api.g_miss_num
1403 THEN
1404 OPEN get_dup_sales_team(l_customer_id,l_address_id ,l_sales_credit_rec.LEAD_ID , l_sales_credit_rec.SALESFORCE_ID, l_sales_credit_rec.SALESGROUP_ID );
1405 FETCH get_dup_sales_team into l_upd_team_flag,l_upd_date,l_upd_access_id;
1406 l_Sales_Team_Rec.team_leader_flag := FND_API.G_MISS_CHAR;
1407 l_Sales_Team_Rec.lead_id := l_sales_credit_rec.LEAD_ID;
1408 l_Sales_Team_Rec.customer_id := l_Customer_Id;
1409 l_Sales_Team_Rec.address_id := l_Address_Id;
1410 l_Sales_Team_Rec.salesforce_id := l_sales_credit_rec.SALESFORCE_ID;
1411 l_sales_team_rec.sales_group_id := l_sales_credit_rec.SALESGROUP_ID;
1412 l_sales_team_rec.person_id := l_sales_credit_rec.PERSON_ID;
1413 l_sales_team_rec.partner_customer_id := l_sales_credit_rec.PARTNER_CUSTOMER_ID;
1414 l_sales_team_rec.partner_address_id := l_sales_credit_rec.PARTNER_ADDRESS_ID;
1415 IF get_dup_sales_team%NOTFOUND THEN
1416 -- Jean 5/11, for bug 1610145
1417
1418 -- The followng condition added for ASNB
1419 IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'), 'N') = 'Y' then
1420 l_Sales_Team_Rec.team_leader_flag := 'Y';
1421 ELSE
1422 IF(l_sales_credit_rec.CREDIT_TYPE_ID = FND_PROFILE.VALUE('AS_FORECAST_CREDIT_TYPE_ID') AND (l_sales_team_rec.partner_customer_id IS NULL OR l_sales_team_rec.partner_customer_id = FND_API.G_MISS_NUM))
1423 THEN
1424 l_Sales_Team_Rec.team_leader_flag := 'Y';
1425 ELSE
1426 l_Sales_Team_Rec.team_leader_flag := 'N';
1427 END IF;
1428 END IF;
1429 -- end bug 1610145
1430
1431 l_Sales_Team_Rec.reassign_flag := 'N';
1432 l_Sales_Team_Rec.freeze_flag :=
1433 nvl(FND_PROFILE.Value('AS_DEFAULT_FREEZE_FLAG'), 'Y');
1434
1435 -- Debug Message
1436 IF l_debug THEN
1437 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1438 'Calling Create_SalesTeam');
1439 END IF;
1440
1441 AS_ACCESS_PUB.Create_SalesTeam (
1442 p_api_version_number => 2.0
1443 ,p_init_msg_list => FND_API.G_FALSE
1444 ,p_commit => FND_API.G_FALSE
1445 ,p_validation_level => p_Validation_Level
1446 ,p_access_profile_rec => l_access_profile_rec
1447 ,p_check_access_flag => P_Check_Access_flag
1448 ,p_admin_flag => P_Admin_Flag
1449 ,p_admin_group_id => P_Admin_Group_Id
1450 ,p_identity_salesforce_id => P_Identity_Salesforce_Id
1451 ,p_sales_team_rec => l_Sales_Team_Rec
1452 ,X_Return_Status => x_Return_Status
1453 ,X_Msg_Count => X_Msg_Count
1454 ,X_Msg_Data => X_Msg_Data
1455 ,x_access_id => l_Access_Id
1456 );
1457
1458 -- Debug Message
1459 IF l_debug THEN
1460 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1461 'Create_SalesTeam: l_access_id = ' || l_access_id);
1462 END IF;
1463
1464 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1465 IF l_debug THEN
1466 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1467 'Create_SalesTeam fail');
1468 END IF;
1469 RAISE FND_API.G_EXC_ERROR;
1470 END IF;
1471 -- The following else part added for ASNB
1472 /*
1473 Modified for bug# 4168544.
1474 If the sales creditor exists in the sales team then update the
1475 full access flag ie team_leader_flag to 'Y' if not already set.
1476 */
1477 ELSE -- get_dup_sales_team found
1478 IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'), 'N') = 'Y' AND
1479 nvl(l_upd_team_flag,'N') <> 'Y'
1480 THEN
1481 l_Sales_Team_Rec.last_update_date := l_upd_date;
1482 l_Sales_Team_Rec.access_id := l_upd_access_id;
1483 l_Sales_Team_Rec.team_leader_flag := 'Y';
1484 AS_ACCESS_PUB.Update_SalesTeam (
1485 p_api_version_number => 2.0
1486 ,p_init_msg_list => FND_API.G_FALSE
1487 ,p_commit => FND_API.G_FALSE
1488 ,p_validation_level => p_Validation_Level
1489 ,p_access_profile_rec => l_access_profile_rec
1490 ,p_check_access_flag => P_Check_Access_flag
1491 ,p_admin_flag => P_Admin_Flag
1492 ,p_admin_group_id => P_Admin_Group_Id
1493 ,p_identity_salesforce_id => P_Identity_Salesforce_Id
1494 ,p_sales_team_rec => l_Sales_Team_Rec
1495 ,X_Return_Status => x_Return_Status
1496 ,X_Msg_Count => X_Msg_Count
1497 ,X_Msg_Data => X_Msg_Data
1498 ,x_access_id => l_Access_Id );
1499
1500 -- Debug Message
1501 IF l_debug THEN
1502 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1503 'update_SalesTeam: l_access_id = ' || l_access_id);
1504 END IF;
1505
1506 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1507 IF l_debug THEN
1508 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1509 'update_SalesTeam fail');
1510 END IF;
1511 RAISE FND_API.G_EXC_ERROR;
1512 END IF;
1513 END IF;
1514 END IF;
1515 CLOSE get_dup_sales_team;
1516 END IF;
1517
1518 IF nvl(l_sales_credit_rec.PARTNER_CUSTOMER_ID, fnd_api.g_miss_num) <>fnd_api.g_miss_num
1519 THEN
1520
1521 OPEN get_dup_sales_partner(l_customer_id,l_address_id ,l_sales_credit_rec.LEAD_ID , l_sales_credit_rec.SALESFORCE_ID, l_sales_credit_rec.SALESGROUP_ID );
1522 FETCH get_dup_sales_partner into l_val;
1523 IF get_dup_sales_partner%NOTFOUND THEN
1524 l_Sales_Team_Rec.team_leader_flag := FND_API.G_MISS_CHAR;
1525 l_Sales_Team_Rec.lead_id := l_sales_credit_rec.LEAD_ID;
1526 l_Sales_Team_Rec.customer_id := l_Customer_Id;
1527 l_Sales_Team_Rec.address_id := l_Address_Id;
1528 l_Sales_Team_Rec.salesforce_id := l_sales_credit_rec.SALESFORCE_ID;
1529 l_sales_team_rec.sales_group_id := l_sales_credit_rec.SALESGROUP_ID;
1530 l_sales_team_rec.person_id := l_sales_credit_rec.PERSON_ID;
1531 l_sales_team_rec.partner_customer_id := l_sales_credit_rec.PARTNER_CUSTOMER_ID;
1532 l_sales_team_rec.partner_address_id := l_sales_credit_rec.PARTNER_ADDRESS_ID;
1533 -- Jean 5/11, for bug 1610145
1534 IF(l_sales_credit_rec.CREDIT_TYPE_ID = FND_PROFILE.VALUE('AS_FORECAST_CREDIT_TYPE_ID') AND (l_sales_team_rec.partner_customer_id IS NULL OR l_sales_team_rec.partner_customer_id = FND_API.G_MISS_NUM))
1535 THEN
1536 l_Sales_Team_Rec.team_leader_flag := 'Y';
1537 ELSE
1538 l_Sales_Team_Rec.team_leader_flag := 'N';
1539 END IF;
1540 -- end bug 1610145
1541
1542 l_Sales_Team_Rec.reassign_flag := 'N';
1543 l_Sales_Team_Rec.freeze_flag :=
1544 nvl(FND_PROFILE.Value('AS_DEFAULT_FREEZE_FLAG'), 'Y');
1545
1546 -- Debug Message
1547 IF l_debug THEN
1548 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1549 'Calling Create_SalesTeam');
1550 END IF;
1551
1552 AS_ACCESS_PUB.Create_SalesTeam (
1553 p_api_version_number => 2.0
1554 ,p_init_msg_list => FND_API.G_FALSE
1555 ,p_commit => FND_API.G_FALSE
1556 ,p_validation_level => p_Validation_Level
1557 ,p_access_profile_rec => l_access_profile_rec
1558 ,p_check_access_flag => P_Check_Access_flag
1559 ,p_admin_flag => P_Admin_Flag
1560 ,p_admin_group_id => P_Admin_Group_Id
1561 ,p_identity_salesforce_id => P_Identity_Salesforce_Id
1562 ,p_sales_team_rec => l_Sales_Team_Rec
1563 ,X_Return_Status => x_Return_Status
1564 ,X_Msg_Count => X_Msg_Count
1565 ,X_Msg_Data => X_Msg_Data
1566 ,x_access_id => l_Access_Id
1567 );
1568
1569 -- Debug Message
1570 IF l_debug THEN
1571 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1572 'Create_SalesTeam: l_access_id = ' || l_access_id);
1573 END IF;
1574
1575 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1576 IF l_debug THEN
1577 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1578 'Create_SalesTeam fail');
1579 END IF;
1580 RAISE FND_API.G_EXC_ERROR;
1581 END IF;
1582 END IF;
1583 CLOSE get_dup_sales_partner;
1584 END IF;
1585 end if;
1586
1587 END LOOP;
1588
1589 --
1590 -- End of API body.
1591 --
1592
1593 -- Standard check for p_commit
1594 IF FND_API.to_Boolean( p_commit )
1595 THEN
1596 COMMIT WORK;
1597 END IF;
1598
1599
1600 -- Debug Message
1601 IF l_debug THEN
1602 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1603 'Private API: ' || l_api_name || 'end');
1604 END IF;
1605
1606
1607
1608 -- Standard call to get message count and if count is 1, get message info.
1609 FND_MSG_PUB.Count_And_Get
1610 ( p_count => x_msg_count,
1611 p_data => x_msg_data
1612 );
1613
1614 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
1615 /*
1616 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout
1617 -- procedure is invoked for customization purpose
1618 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
1619 THEN
1620 AS_CALLOUT_PKG.Update_sales_credit_AU(
1621 p_api_version_number => 2.0,
1622 p_init_msg_list => FND_API.G_FALSE,
1623 p_commit => FND_API.G_FALSE,
1624 p_validation_level => p_validation_level,
1625 p_identity_salesforce_id => p_identity_salesforce_id,
1626 P_SALES_CREDIT_Rec => P_SALES_CREDIT_Rec,
1627 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
1628 x_return_status => x_return_status,
1629 x_msg_count => x_msg_count,
1630 x_msg_data => x_msg_data);
1631 END IF;
1632 */
1633
1634 EXCEPTION
1635 WHEN FND_API.G_EXC_ERROR THEN
1636 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1637 P_MODULE => l_module
1638 ,P_API_NAME => L_API_NAME
1639 ,P_PKG_NAME => G_PKG_NAME
1640 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1641 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1642 ,X_MSG_COUNT => X_MSG_COUNT
1643 ,X_MSG_DATA => X_MSG_DATA
1644 ,X_RETURN_STATUS => X_RETURN_STATUS);
1645
1646 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1647 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1648 P_MODULE => l_module
1649 ,P_API_NAME => L_API_NAME
1650 ,P_PKG_NAME => G_PKG_NAME
1651 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1652 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1653 ,X_MSG_COUNT => X_MSG_COUNT
1654 ,X_MSG_DATA => X_MSG_DATA
1655 ,X_RETURN_STATUS => X_RETURN_STATUS);
1656
1657 WHEN OTHERS THEN
1658 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1659 P_MODULE => l_module
1660 ,P_API_NAME => L_API_NAME
1661 ,P_PKG_NAME => G_PKG_NAME
1662 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1663 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1664 ,X_MSG_COUNT => X_MSG_COUNT
1665 ,X_MSG_DATA => X_MSG_DATA
1666 ,X_RETURN_STATUS => X_RETURN_STATUS);
1667 End Update_sales_credits;
1668
1669
1670 PROCEDURE modify_sales_credits(
1671 P_Api_Version_Number IN NUMBER,
1672 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1673 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1674 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1675 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
1676 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
1677 P_Admin_Group_Id IN NUMBER,
1678 P_Identity_Salesforce_Id IN NUMBER := NULL,
1679 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
1680 p_partner_cont_party_id IN NUMBER := FND_API.G_MISS_NUM,
1681 P_SALES_CREDIT_tbl IN AS_OPPORTUNITY_PUB.SALES_CREDIT_tbl_Type,
1682 X_SALES_CREDIT_out_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.sales_credit_out_tbl_type,
1683 X_Return_Status OUT NOCOPY VARCHAR2,
1684 X_Msg_Count OUT NOCOPY NUMBER,
1685 X_Msg_Data OUT NOCOPY VARCHAR2
1686 )
1687
1688 IS
1689
1690 CURSOR C_DELETED_SALES_CREDITS( p_lead_line_id NUMBER,
1691 p_forecast_credit_type_id NUMBER )IS
1692 SELECT *
1693 FROM AS_SALES_CREDITS
1694 WHERE lead_line_id = p_lead_line_id
1695 AND credit_type_id = p_forecast_credit_type_id;
1696
1697 -- solin, for bug 1554330
1698 CURSOR c_get_opp_freeze_flag(c_LEAD_ID NUMBER) IS
1699 SELECT FREEZE_FLAG
1700 FROM AS_LEADS
1701 WHERE LEAD_ID = c_LEAD_ID;
1702
1703 l_api_name CONSTANT VARCHAR2(30) := 'modify_sales_credits';
1704 l_api_version_number CONSTANT NUMBER := 2.0;
1705 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
1706
1707 l_SALES_CREDIT_rec AS_OPPORTUNITY_PUB.SALES_CREDIT_Rec_Type;
1708 l_total_forecast_amount NUMBER;
1709 l_total_forecast_percent NUMBER := 0;
1710 l_forecast_credit_type_id NUMBER := FND_PROFILE.Value('AS_FORECAST_CREDIT_TYPE_ID');
1711
1712 l_update_access_flag VARCHAR2(1);
1713 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
1714
1715 l_index NUMBER;
1716 l_sales_credit_tbl AS_OPPORTUNITY_PUB.SALES_CREDIT_tbl_Type;
1717 r_sales_credit_tbl AS_OPPORTUNITY_PUB.SALES_CREDIT_tbl_Type;
1718 n_sales_credit_tbl AS_OPPORTUNITY_PUB.SALES_CREDIT_tbl_Type;
1719 u_sales_credit_tbl AS_OPPORTUNITY_PUB.SALES_CREDIT_tbl_Type;
1720 d_sales_credit_tbl AS_OPPORTUNITY_PUB.SALES_CREDIT_tbl_Type;
1721 l_lead_line_id NUMBER := p_sales_credit_tbl(1).lead_line_id;
1722 l_freeze_flag VARCHAR2(1) := 'N'; -- solin, for bug 1554330
1723 l_allow_flag VARCHAR2(1); -- solin, for bug 1554330
1724 J NUMBER;
1725 NL NUMBER;
1726 UL NUMBER;
1727 DL NUMBER;
1728 delete_flag BOOLEAN;
1729 s_index NUMBER;
1730 e_index NUMBER;
1731 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1732 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.modify_sales_credits';
1733
1734 BEGIN
1735 -- Standard Start of API savepoint
1736 SAVEPOINT MODIFY_SALES_CREDITS_PVT;
1737
1738 -- Standard call to check for call compatibility.
1739 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1740 p_api_version_number,
1741 l_api_name,
1742 G_PKG_NAME)
1743 THEN
1744 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1745 END IF;
1746
1747
1748 -- Initialize message list if p_init_msg_list is set to TRUE.
1749 IF FND_API.to_Boolean( p_init_msg_list )
1750 THEN
1751 FND_MSG_PUB.initialize;
1752 END IF;
1753
1754
1755 -- Debug Message
1756 IF l_debug THEN
1757 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1758 'Private API 18: ' || l_api_name || ' start');
1759 END IF;
1760
1761 -- Initialize API return status to SUCCESS
1762 x_return_status := FND_API.G_RET_STS_SUCCESS;
1763
1764 --
1765 -- Api body
1766 --
1767
1768 IF(P_Check_Access_Flag = 'Y') THEN
1769 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1770 p_api_version_number => 2.0
1771 ,p_init_msg_list => p_init_msg_list
1772 ,p_salesforce_id => p_identity_salesforce_id
1773 ,p_admin_group_id => p_admin_group_id
1774 ,x_return_status => x_return_status
1775 ,x_msg_count => x_msg_count
1776 ,x_msg_data => x_msg_data
1777 ,x_sales_member_rec => l_identity_sales_member_rec);
1778 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1779 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1780 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1781 'Private API 19: Get_CurrentUser fail');
1782 END IF;
1783 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1784 END IF;
1785
1786 -- Call Get_Access_Profiles to get access_profile_rec
1787 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
1788 p_profile_tbl => p_profile_tbl,
1789 x_access_profile_rec => l_access_profile_rec);
1790
1791 AS_ACCESS_PUB.has_updateOpportunityAccess
1792 ( p_api_version_number => 2.0
1793 ,p_init_msg_list => p_init_msg_list
1794 ,p_validation_level => p_validation_level
1795 ,p_access_profile_rec => l_access_profile_rec
1796 ,p_admin_flag => p_admin_flag
1797 ,p_admin_group_id => p_admin_group_id
1798 ,p_person_id => l_identity_sales_member_rec.employee_person_id
1799 ,p_opportunity_id => p_sales_credit_tbl(1).LEAD_ID
1800 ,p_check_access_flag => p_check_access_flag
1801 ,p_identity_salesforce_id => p_identity_salesforce_id
1802 ,p_partner_cont_party_id => p_partner_cont_party_id
1803 ,x_return_status => x_return_status
1804 ,x_msg_count => x_msg_count
1805 ,x_msg_data => x_msg_data
1806 ,x_update_access_flag => l_update_access_flag );
1807
1808 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1809 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1810 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1811 'has_updateOpportunityAccess fail');
1812 END IF;
1813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1814 END IF;
1815
1816 IF (l_update_access_flag <> 'Y') THEN
1817 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1818 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
1819 FND_MESSAGE.Set_Token('INFO', 'CUSTOMER_ID,OPPORTUNITY_ID,SALESFORCE_ID', FALSE);
1820 FND_MSG_PUB.ADD;
1821 END IF;
1822 RAISE FND_API.G_EXC_ERROR;
1823 END IF;
1824 END IF;
1825
1826 -- Jean correct here
1827 -- solin, for bug 1554330
1828 s_index := p_sales_credit_tbl.FIRST;
1829 --OPEN c_get_opp_freeze_flag(p_sales_credit_tbl(1).LEAD_ID);
1830 OPEN c_get_opp_freeze_flag(p_sales_credit_tbl(s_index).LEAD_ID);
1831 FETCH c_get_opp_freeze_flag INTO l_freeze_flag;
1832 CLOSE c_get_opp_freeze_flag;
1833 -- end of Jean correct
1834
1835 IF l_freeze_flag = 'Y'
1836 THEN
1837 l_allow_flag := NVL(FND_PROFILE.VALUE('AS_ALLOW_UPDATE_FROZEN_OPP'),'Y');
1838 IF l_allow_flag <> 'Y' THEN
1839 AS_UTILITY_PVT.Set_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
1840 'API_OPP_FROZEN');
1841 RAISE FND_API.G_EXC_ERROR;
1842 END IF;
1843 END IF;
1844 -- end 1554330
1845
1846 IF l_forecast_credit_type_id IS NULL THEN
1847 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1848 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1849 'The profile AS_FORECAST_CREDIT_TYPE_ID is null');
1850 END IF;
1851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1852 END IF;
1853
1854 -- Jean Correct the fix for the bug 2422928
1855 -- Filter out empty rows, Bug 2422928
1856 --J := 1;
1857 --FOR I in 1 .. P_SALES_CREDIT_tbl.count LOOP
1858 -- IF p_SALES_CREDIT_tbl.exists(I) THEN
1859 -- r_SALES_CREDIT_tbl(J) := p_SALES_CREDIT_tbl(I);
1860 -- J := J + 1;
1861 -- END IF;
1862 --END LOOP;
1863
1864 --l_lead_line_id := r_sales_credit_tbl(1).lead_line_id;
1865
1866 -- Validate 100% Forecast credit percent
1867 --FOR I in 1 .. P_SALES_CREDIT_tbl.count LOOP
1868 -- l_sales_credit_rec := r_SALES_CREDIT_tbl(I);
1869 J := 1;
1870 s_index := P_SALES_CREDIT_tbl.FIRST;
1871 e_index := P_SALES_CREDIT_tbl.LAST;
1872 FOR I in s_index .. e_index LOOP
1873 IF p_SALES_CREDIT_tbl.exists(I) THEN
1874 r_SALES_CREDIT_tbl(J) := p_SALES_CREDIT_tbl(I);
1875 J := J + 1;
1876 END IF;
1877 END LOOP;
1878
1879 FOR I in 1 .. r_SALES_CREDIT_tbl.count LOOP
1880 l_sales_credit_rec := r_SALES_CREDIT_tbl(I);
1881
1882 -- Invoke validation procedures
1883 Validate_sales_credit(
1884 p_init_msg_list => FND_API.G_FALSE,
1885 p_validation_level => p_validation_level,
1886 p_validation_mode => FND_API.G_MISS_CHAR, --AS_UTILITY_PVT.G_CREATE,
1887 P_SALES_CREDIT_Rec => l_SALES_CREDIT_Rec,
1888 x_return_status => x_return_status,
1889 x_msg_count => x_msg_count,
1890 x_msg_data => x_msg_data);
1891
1892 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1893 IF l_debug THEN
1894 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1895 'Private API 21: Validate_sales_credit fail');
1896 END IF;
1897 RAISE FND_API.G_EXC_ERROR;
1898 END IF;
1899
1900
1901 IF l_sales_credit_rec.credit_type_id <> l_forecast_credit_type_id THEN
1902 IF l_debug THEN
1903 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1904 'Private API: Credit_type is not forecast credit type');
1905 END IF;
1906 RAISE FND_API.G_EXC_ERROR;
1907 ELSE
1908 l_total_forecast_amount := l_total_forecast_amount + l_sales_credit_rec.credit_amount;
1909 l_total_forecast_percent := l_total_forecast_percent + l_sales_credit_rec.credit_percent;
1910 END IF;
1911
1912 END LOOP;
1913
1914 -- 100% Validation
1915 IF nvl(l_total_forecast_percent, 0) <> 100 THEN
1916 IF l_debug THEN
1917 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1918 'Private API 23: 100% Forecast Credit validation fail');
1919 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1920 'Private API 24: l_total_percent = '||l_total_forecast_percent );
1921 END IF;
1922 RAISE FND_API.G_EXC_ERROR;
1923 END IF;
1924
1925 IF l_lead_line_id IS NULL OR l_lead_line_id = FND_API.G_MISS_NUM THEN
1926 IF l_debug THEN
1927 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1928 'Private API 25: lead_line_id is missing in the first sales credit record');
1929 END IF;
1930 RAISE FND_API.G_EXC_ERROR;
1931 END IF;
1932
1933
1934 -- Fix for the bug 2902247
1935 l_index := 1;
1936
1937 NL := 1;
1938 UL :=1;
1939 FOR I in 1 .. r_SALES_CREDIT_tbl.count LOOP
1940 IF r_SALES_CREDIT_tbl(I).sales_credit_id IS NULL OR r_SALES_CREDIT_tbl(I).sales_credit_id = FND_API.G_MISS_NUM THEN
1941 n_SALES_CREDIT_tbl(NL) := r_SALES_CREDIT_tbl(I);
1942 NL := NL + 1;
1943 ELSE
1944 u_SALES_CREDIT_tbl(UL) := r_SALES_CREDIT_tbl(I);
1945 UL := UL+1;
1946 END IF;
1947 END LOOP;
1948
1949 DL := 1;
1950 FOR scr in C_DELETED_SALES_CREDITS(l_lead_line_id,l_forecast_credit_type_id) LOOP
1951 delete_flag := True;
1952 FOR I in 1 .. u_SALES_CREDIT_tbl.count LOOP
1953 if(scr.sales_credit_id = u_SALES_CREDIT_tbl(I).sales_credit_id)
1954 then
1955 delete_flag := False;
1956
1957 end if;
1958 END LOOP;
1959 IF(delete_flag = true)
1960 THEN
1961 d_sales_credit_tbl(DL).sales_credit_id := scr.sales_credit_id;
1962 d_sales_credit_tbl(DL).lead_id := scr.lead_id;
1963 d_sales_credit_tbl(DL).lead_line_id := scr.lead_line_id;
1964 DL := DL + 1;
1965 END IF;
1966 END LOOP;
1967
1968 IF (DL <> 1)
1969 THEN
1970 AS_OPP_sales_credit_PVT.Delete_sales_credits(
1971 P_Api_Version_Number => 2.0,
1972 P_Init_Msg_List => FND_API.G_FALSE,
1973 P_Commit => FND_API.G_FALSE,
1974 P_Validation_Level => FND_API.G_VALID_LEVEL_NONE,
1975 P_Check_Access_Flag => FND_API.G_FALSE,
1976 P_Admin_Flag => P_Admin_Flag,
1977 P_Admin_Group_Id => P_Admin_Group_Id,
1978 P_Profile_Tbl => P_Profile_tbl,
1979 P_Partner_Cont_Party_Id => p_partner_cont_party_id,
1980 P_Identity_Salesforce_Id => p_identity_salesforce_id,
1981 P_Sales_Credit_Tbl => d_sales_credit_tbl,
1982 X_Sales_Credit_Out_Tbl => x_sales_credit_out_tbl,
1983 X_Return_Status => x_return_status,
1984 X_Msg_Count => x_msg_count,
1985 X_Msg_Data => x_msg_data);
1986
1987 -- Check return status from the above procedure call
1988 IF x_return_status = FND_API.G_RET_STS_ERROR then
1989 raise FND_API.G_EXC_ERROR;
1990 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1991 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1992 END IF;
1993
1994 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1995 IF l_debug THEN
1996 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1997 'Private API 26: Delete_Sales_credits fail');
1998 END IF;
1999 END IF;
2000 END IF;
2001
2002 IF (NL <> 1)
2003 THEN
2004 AS_OPP_sales_credit_PVT.Create_sales_credits(
2005 P_Api_Version_Number => 2.0,
2006 P_Init_Msg_List => FND_API.G_FALSE,
2007 P_Commit => FND_API.G_FALSE,
2008 P_Validation_Level => FND_API.G_VALID_LEVEL_NONE,
2009 P_Check_Access_Flag => FND_API.G_FALSE,
2010 P_Admin_Flag => P_Admin_Flag ,
2011 P_Admin_Group_Id => P_Admin_Group_Id,
2012 P_Identity_Salesforce_Id => P_Identity_Salesforce_Id,
2013 P_Partner_Cont_Party_Id => p_partner_cont_party_id,
2014 P_Profile_Tbl => P_Profile_tbl,
2015 P_Sales_Credit_Tbl => n_sales_credit_tbl,
2016 X_Sales_Credit_Out_Tbl => x_sales_credit_out_tbl,
2017 X_Return_Status => x_return_status,
2018 X_Msg_Count => x_msg_count,
2019 X_Msg_Data => x_msg_data);
2020
2021
2022 -- Check return status from the above procedure call
2023 IF x_return_status = FND_API.G_RET_STS_ERROR then
2024 raise FND_API.G_EXC_ERROR;
2025 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2026 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2027 END IF;
2028
2029 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2030 IF l_debug THEN
2031 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2032 'Private API 27: Create_Sales_credits fail');
2033 END IF;
2034 END IF;
2035 END IF;
2036
2037 IF (UL <>1)
2038 THEN
2039 AS_OPP_sales_credit_PVT.Update_sales_credits(
2040 P_Api_Version_Number => 2.0,
2041 P_Init_Msg_List => FND_API.G_FALSE,
2042 P_Commit => FND_API.G_FALSE,
2043 P_Validation_Level => FND_API.G_VALID_LEVEL_NONE,
2044 P_Check_Access_Flag => FND_API.G_FALSE,
2045 P_Admin_Flag => P_Admin_Flag ,
2046 P_Admin_Group_Id => P_Admin_Group_Id,
2047 P_Identity_Salesforce_Id => P_Identity_Salesforce_Id,
2048 P_Partner_Cont_Party_Id => p_partner_cont_party_id,
2049 P_Profile_Tbl => P_Profile_tbl,
2050 P_Sales_Credit_Tbl => u_sales_credit_tbl,
2051 X_Sales_Credit_Out_Tbl => x_sales_credit_out_tbl,
2052 X_Return_Status => x_return_status,
2053 X_Msg_Count => x_msg_count,
2054 X_Msg_Data => x_msg_data);
2055
2056
2057 -- Check return status from the above procedure call
2058 IF x_return_status = FND_API.G_RET_STS_ERROR then
2059 raise FND_API.G_EXC_ERROR;
2060 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2061 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2062 END IF;
2063
2064 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2065 IF l_debug THEN
2066 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2067 'Private API 28: Update_Sales_credits fail');
2068 END IF;
2069 END IF;
2070 END IF;
2071
2072 --FOR scr in C_DELETED_SALES_CREDITS(l_lead_line_id,l_forecast_credit_type_id) LOOP
2073 -- l_sales_credit_tbl(l_index).sales_credit_id := scr.sales_credit_id;
2074 -- l_sales_credit_tbl(l_index).lead_id := scr.lead_id;
2075 -- l_sales_credit_tbl(l_index).lead_line_id := scr.lead_line_id;
2076 -- l_index := l_index + 1;
2077 --END LOOP;
2078
2079 --AS_OPP_sales_credit_PVT.Delete_sales_credits(
2080 -- P_Api_Version_Number => 2.0,
2081 -- P_Init_Msg_List => FND_API.G_FALSE,
2082 -- P_Commit => FND_API.G_FALSE,
2083 -- P_Validation_Level => FND_API.G_VALID_LEVEL_NONE,
2084 -- P_Check_Access_Flag => FND_API.G_FALSE,
2085 -- P_Admin_Flag => P_Admin_Flag,
2086 -- P_Admin_Group_Id => P_Admin_Group_Id,
2087 -- P_Profile_Tbl => P_Profile_tbl,
2088 -- P_Partner_Cont_Party_Id => p_partner_cont_party_id,
2089 -- P_Identity_Salesforce_Id => p_identity_salesforce_id,
2090 -- P_Sales_Credit_Tbl => l_sales_credit_tbl,
2091 -- X_Sales_Credit_Out_Tbl => x_sales_credit_out_tbl,
2092 -- X_Return_Status => x_return_status,
2093 -- X_Msg_Count => x_msg_count,
2094 -- X_Msg_Data => x_msg_data);
2095
2096 -- Check return status from the above procedure call
2097 --IF x_return_status = FND_API.G_RET_STS_ERROR then
2098 -- raise FND_API.G_EXC_ERROR;
2099 --elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2100 -- raise FND_API.G_EXC_UNEXPECTED_ERROR;
2101 --END IF;
2102
2103 --IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2104 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2105 -- 'Private API 26: Delete_Sales_credits fail');
2106 --END IF;
2107
2108
2109 --AS_OPP_sales_credit_PVT.Create_sales_credits(
2110 -- P_Api_Version_Number => 2.0,
2111 -- P_Init_Msg_List => FND_API.G_FALSE,
2112 -- P_Commit => FND_API.G_FALSE,
2113 -- P_Validation_Level => FND_API.G_VALID_LEVEL_NONE,
2114 -- P_Check_Access_Flag => FND_API.G_FALSE,
2115 -- P_Admin_Flag => P_Admin_Flag ,
2116 -- P_Admin_Group_Id => P_Admin_Group_Id,
2117 -- P_Identity_Salesforce_Id => P_Identity_Salesforce_Id,
2118 -- P_Partner_Cont_Party_Id => p_partner_cont_party_id,
2119 -- P_Profile_Tbl => P_Profile_tbl,
2120 -- P_Sales_Credit_Tbl => r_sales_credit_tbl,
2121 -- X_Sales_Credit_Out_Tbl => x_sales_credit_out_tbl,
2122 -- X_Return_Status => x_return_status,
2123 -- X_Msg_Count => x_msg_count,
2124 -- X_Msg_Data => x_msg_data);
2125
2126
2127 -- Check return status from the above procedure call
2128 --IF x_return_status = FND_API.G_RET_STS_ERROR then
2129 -- raise FND_API.G_EXC_ERROR;
2130 --elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
2131 -- raise FND_API.G_EXC_UNEXPECTED_ERROR;
2132 --END IF;
2133
2134 --IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2135 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2136 -- 'Private API 27: Create_Sales_credits fail');
2137 --END IF;
2138 -- end of the fix for the bug 2902247
2139
2140
2141 --
2142 -- End of API body.
2143 --
2144
2145 -- Standard check for p_commit
2146 IF FND_API.to_Boolean( p_commit )
2147 THEN
2148 COMMIT WORK;
2149 END IF;
2150
2151
2152 -- Debug Message
2153 IF l_debug THEN
2154 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2155 'Private API 28: ' || l_api_name || 'end');
2156 END IF;
2157
2158 -- Standard call to get message count and if count is 1, get message info.
2159 FND_MSG_PUB.Count_And_Get
2160 ( p_count => x_msg_count,
2161 p_data => x_msg_data
2162 );
2163
2164
2165 EXCEPTION
2166 WHEN FND_API.G_EXC_ERROR THEN
2167 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2168 P_MODULE => l_module
2169 ,P_API_NAME => L_API_NAME
2170 ,P_PKG_NAME => G_PKG_NAME
2171 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2172 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2173 ,X_MSG_COUNT => X_MSG_COUNT
2174 ,X_MSG_DATA => X_MSG_DATA
2175 ,X_RETURN_STATUS => X_RETURN_STATUS);
2176
2177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2178 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2179 P_MODULE => l_module
2180 ,P_API_NAME => L_API_NAME
2181 ,P_PKG_NAME => G_PKG_NAME
2182 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2183 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2184 ,X_MSG_COUNT => X_MSG_COUNT
2185 ,X_MSG_DATA => X_MSG_DATA
2186 ,X_RETURN_STATUS => X_RETURN_STATUS);
2187
2188 WHEN OTHERS THEN
2189 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2190 P_MODULE => l_module
2191 ,P_API_NAME => L_API_NAME
2192 ,P_PKG_NAME => G_PKG_NAME
2193 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
2194 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2195 ,X_MSG_COUNT => X_MSG_COUNT
2196 ,X_MSG_DATA => X_MSG_DATA
2197 ,X_RETURN_STATUS => X_RETURN_STATUS);
2198
2199 End modify_sales_credits;
2200
2201
2202 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
2203 -- The Master delete procedure may not be needed depends on different business requirements.
2204 PROCEDURE Delete_sales_credits(
2205 P_Api_Version_Number IN NUMBER,
2206 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2207 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2208 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2209 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
2210 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
2211 P_Admin_Group_Id IN NUMBER,
2212 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
2213 P_identity_salesforce_id IN NUMBER := NULL,
2214 p_partner_cont_party_id IN NUMBER := FND_API.G_MISS_NUM,
2215 P_SALES_CREDIT_tbl IN AS_OPPORTUNITY_PUB.SALES_CREDIT_tbl_type,
2216 X_SALES_CREDIT_out_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.sales_credit_out_tbl_type,
2217 X_Return_Status OUT NOCOPY VARCHAR2,
2218 X_Msg_Count OUT NOCOPY NUMBER,
2219 X_Msg_Data OUT NOCOPY VARCHAR2
2220 )
2221
2222 IS
2223 -- solin, for bug 1554330
2224 CURSOR c_get_opp_freeze_flag(c_LEAD_ID NUMBER) IS
2225 SELECT FREEZE_FLAG
2226 FROM AS_LEADS
2227 WHERE LEAD_ID = c_LEAD_ID;
2228
2229 l_api_name CONSTANT VARCHAR2(30) := 'Delete_sales_credits';
2230 l_api_version_number CONSTANT NUMBER := 2.0;
2231 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
2232 l_update_access_flag VARCHAR2(1);
2233 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
2234 l_freeze_flag VARCHAR2(1) := 'N'; -- solin, for bug 1554330
2235 l_allow_flag VARCHAR2(1); -- solin, for bug 1554330
2236 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2237 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Delete_sales_credits';
2238
2239 BEGIN
2240 -- Standard Start of API savepoint
2241 SAVEPOINT DELETE_SALES_CREDITS_PVT;
2242
2243 -- Standard call to check for call compatibility.
2244 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2245 p_api_version_number,
2246 l_api_name,
2247 G_PKG_NAME)
2248 THEN
2249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2250 END IF;
2251
2252
2253 -- Initialize message list if p_init_msg_list is set to TRUE.
2254 IF FND_API.to_Boolean( p_init_msg_list )
2255 THEN
2256 FND_MSG_PUB.initialize;
2257 END IF;
2258
2259
2260 -- Debug Message
2261 IF l_debug THEN
2262 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2263 'Private API 29: ' || l_api_name || 'start');
2264 END IF;
2265
2266
2267
2268 -- Initialize API return status to SUCCESS
2269 x_return_status := FND_API.G_RET_STS_SUCCESS;
2270
2271 --
2272 -- Api body
2273 --
2274 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
2275 /*
2276 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout
2277 -- procedure is invoked for customization purpose
2278 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
2279 THEN
2280 AS_CALLOUT_PKG.Delete_sales_credit_BD(
2281 p_api_version_number => 2.0,
2282 p_init_msg_list => FND_API.G_FALSE,
2283 p_commit => FND_API.G_FALSE,
2284 p_validation_level => p_validation_level,
2285 p_identity_salesforce_id => p_identity_salesforce_id,
2286 P_SALES_CREDIT_Rec => P_SALES_CREDIT_Rec,
2287 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
2288 x_return_status => x_return_status,
2289 x_msg_count => x_msg_count,
2290 x_msg_data => x_msg_data);
2291 END IF;
2292 */
2293
2294
2295 IF(P_Check_Access_Flag = 'Y') THEN
2296 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
2297 p_api_version_number => 2.0
2298 ,p_init_msg_list => p_init_msg_list
2299 ,p_salesforce_id => p_identity_salesforce_id
2300 ,p_admin_group_id => p_admin_group_id
2301 ,x_return_status => x_return_status
2302 ,x_msg_count => x_msg_count
2303 ,x_msg_data => x_msg_data
2304 ,x_sales_member_rec => l_identity_sales_member_rec);
2305 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2306 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2307 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2308 'Private API 30: Get_CurrentUser fail');
2309 END IF;
2310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2311 END IF;
2312
2313 -- Call Get_Access_Profiles to get access_profile_rec
2314 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
2315 p_profile_tbl => p_profile_tbl,
2316 x_access_profile_rec => l_access_profile_rec);
2317
2318 AS_ACCESS_PUB.has_updateOpportunityAccess
2319 ( p_api_version_number => 2.0
2320 ,p_init_msg_list => p_init_msg_list
2321 ,p_validation_level => p_validation_level
2322 ,p_access_profile_rec => l_access_profile_rec
2323 ,p_admin_flag => p_admin_flag
2324 ,p_admin_group_id => p_admin_group_id
2325 ,p_person_id => l_identity_sales_member_rec.employee_person_id
2326 ,p_opportunity_id => p_sales_credit_tbl(1).LEAD_ID
2327 ,p_check_access_flag => p_check_access_flag
2328 ,p_identity_salesforce_id => p_identity_salesforce_id
2329 ,p_partner_cont_party_id => p_partner_cont_party_id
2330 ,x_return_status => x_return_status
2331 ,x_msg_count => x_msg_count
2332 ,x_msg_data => x_msg_data
2333 ,x_update_access_flag => l_update_access_flag );
2334
2335 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2336 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2337 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2338 'has_updateOpportunityAccess fail');
2339 END IF;
2340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2341 END IF;
2342
2343 IF (l_update_access_flag <> 'Y') THEN
2344 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2345 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
2346 FND_MESSAGE.Set_Token('INFO', 'CUSTOMER_ID,OPPORTUNITY_ID,SALESFORCE_ID', FALSE);
2347 FND_MSG_PUB.ADD;
2348 END IF;
2349 RAISE FND_API.G_EXC_ERROR;
2350 END IF;
2351 END IF;
2352
2353 -- solin, for bug 1554330
2354 OPEN c_get_opp_freeze_flag(p_sales_credit_tbl(1).LEAD_ID);
2355 FETCH c_get_opp_freeze_flag INTO l_freeze_flag;
2356 CLOSE c_get_opp_freeze_flag;
2357
2358 IF l_freeze_flag = 'Y'
2359 THEN
2360 l_allow_flag := NVL(FND_PROFILE.VALUE('AS_ALLOW_UPDATE_FROZEN_OPP'),'Y');
2361 IF l_allow_flag <> 'Y' THEN
2362 AS_UTILITY_PVT.Set_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2363 'API_OPP_FROZEN');
2364 RAISE FND_API.G_EXC_ERROR;
2365 END IF;
2366 END IF;
2367 -- end 1554330
2368
2369 -- Invoke table handler(AS_SALES_CREDITS_PKG.Delete_Row)
2370 FOR I in 1 .. P_SALES_CREDIT_tbl.count LOOP
2371
2372 X_SALES_CREDIT_out_tbl(I).return_status := FND_API.G_RET_STS_SUCCESS;
2373
2374 -- Progress Message
2375 --
2376 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2377 THEN
2378 --FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
2379 --FND_MESSAGE.Set_Token ('ROW', 'AS_OPP_SALES_CREDIT', TRUE);
2380 --FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(I), FALSE);
2381 --FND_MSG_PUB.Add;
2382 IF l_debug THEN
2383 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2384 'Processing AS_OPP_SALES_CREDIT row number '||to_char(I));
2385 END IF;
2386 END IF;
2387
2388 -- Debug Message
2389 IF l_debug THEN
2390 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2391 'Private API 31: Calling delete table handler');
2392 END IF;
2393
2394 AS_SALES_CREDITS_PKG.Delete_Row(
2395 p_SALES_CREDIT_ID => p_SALES_CREDIT_tbl(I).SALES_CREDIT_ID);
2396
2397 X_SALES_CREDIT_out_tbl(I).SALES_CREDIT_ID := p_SALES_CREDIT_tbl(I).SALES_CREDIT_ID;
2398 X_SALES_CREDIT_out_tbl(I).return_status := x_return_status;
2399
2400 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2401 RAISE FND_API.G_EXC_ERROR;
2402 END IF;
2403 END LOOP;
2404 --
2405 -- End of API body
2406 --
2407
2408 -- Standard check for p_commit
2409 IF FND_API.to_Boolean( p_commit )
2410 THEN
2411 COMMIT WORK;
2412 END IF;
2413
2414
2415 -- Debug Message
2416 IF l_debug THEN
2417 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2418 'Private API 32: ' || l_api_name || 'end');
2419 END IF;
2420
2421 -- Standard call to get message count and if count is 1, get message info.
2422 FND_MSG_PUB.Count_And_Get
2423 ( p_count => x_msg_count,
2424 p_data => x_msg_data
2425 );
2426
2427 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
2428 /*
2429 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout
2430 -- procedure is invoked for customization purpose
2431 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
2432 THEN
2433 AS_CALLOUT_PKG.Delete_sales_credit_AD(
2434 p_api_version_number => 2.0,
2435 p_init_msg_list => FND_API.G_FALSE,
2436 p_commit => FND_API.G_FALSE,
2437 p_validation_level => p_validation_level,
2438 p_identity_salesforce_id => p_identity_salesforce_id,
2439 P_SALES_CREDIT_Rec => P_SALES_CREDIT_Rec,
2440 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
2441 x_return_status => x_return_status,
2442 x_msg_count => x_msg_count,
2443 x_msg_data => x_msg_data);
2444 END IF;
2445 */
2446
2447 EXCEPTION
2448 WHEN FND_API.G_EXC_ERROR THEN
2449 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2450 P_MODULE => l_module
2451 ,P_API_NAME => L_API_NAME
2452 ,P_PKG_NAME => G_PKG_NAME
2453 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2454 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2455 ,X_MSG_COUNT => X_MSG_COUNT
2456 ,X_MSG_DATA => X_MSG_DATA
2457 ,X_RETURN_STATUS => X_RETURN_STATUS);
2458
2459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2460 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2461 P_MODULE => l_module
2462 ,P_API_NAME => L_API_NAME
2463 ,P_PKG_NAME => G_PKG_NAME
2464 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2465 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2466 ,X_MSG_COUNT => X_MSG_COUNT
2467 ,X_MSG_DATA => X_MSG_DATA
2468 ,X_RETURN_STATUS => X_RETURN_STATUS);
2469
2470 WHEN OTHERS THEN
2471 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2472 P_MODULE => l_module
2473 ,P_API_NAME => L_API_NAME
2474 ,P_PKG_NAME => G_PKG_NAME
2475 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
2476 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2477 ,X_MSG_COUNT => X_MSG_COUNT
2478 ,X_MSG_DATA => X_MSG_DATA
2479 ,X_RETURN_STATUS => X_RETURN_STATUS);
2480 End Delete_sales_credits;
2481
2482
2483 -- Item-level validation procedures
2484 PROCEDURE Validate_SALES_CREDIT_ID (
2485 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2486 P_Validation_mode IN VARCHAR2,
2487 P_SALES_CREDIT_ID IN NUMBER,
2488 X_Return_Status OUT NOCOPY VARCHAR2,
2489 X_Msg_Count OUT NOCOPY NUMBER,
2490 X_Msg_Data OUT NOCOPY VARCHAR2
2491 )
2492 IS
2493 CURSOR C_Sales_Credit_Id_Exists (c_Sales_Credit_Id NUMBER) IS
2494 SELECT 'X'
2495 FROM as_sales_credits
2496 WHERE sales_credit_id = c_Sales_Credit_Id;
2497
2498 l_val VARCHAR2(1);
2499 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2500 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Validate_SALES_CREDIT_ID';
2501
2502
2503 BEGIN
2504
2505 -- Initialize message list if p_init_msg_list is set to TRUE.
2506 IF FND_API.to_Boolean( p_init_msg_list )
2507 THEN
2508 FND_MSG_PUB.initialize;
2509 END IF;
2510
2511
2512 -- Initialize API return status to SUCCESS
2513 x_return_status := FND_API.G_RET_STS_SUCCESS;
2514
2515
2516 -- Calling from Create API
2517 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
2518 THEN
2519 IF (p_SALES_CREDIT_ID is NOT NULL) and (p_SALES_CREDIT_ID <> FND_API.G_MISS_NUM)
2520 THEN
2521 OPEN C_Sales_Credit_Id_Exists (p_Sales_Credit_Id);
2522 FETCH C_Sales_Credit_Id_Exists into l_val;
2523 IF C_Sales_Credit_Id_Exists%FOUND THEN
2524 IF l_debug THEN
2525 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2526 'Private API 33: SALES_CREDIT_ID exist');
2527 END IF;
2528
2529 x_return_status := FND_API.G_RET_STS_ERROR;
2530 END IF;
2531 CLOSE C_Sales_Credit_Id_Exists;
2532 END IF;
2533
2534 -- Calling from Update API
2535 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
2536 THEN
2537 -- validate NOT NULL column
2538 IF (p_SALES_CREDIT_ID is NULL) or (p_SALES_CREDIT_ID = FND_API.G_MISS_NUM)
2539 THEN
2540 IF l_debug THEN
2541 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2542 'Private API 34: Violate NOT NULL constraint(SALES_CREDIT_ID)');
2543 END IF;
2544 x_return_status := FND_API.G_RET_STS_ERROR;
2545 ELSE
2546 OPEN C_Sales_Credit_Id_Exists (p_Sales_Credit_Id);
2547 FETCH C_Sales_Credit_Id_Exists into l_val;
2548 IF C_Sales_Credit_Id_Exists%NOTFOUND
2549 THEN
2550 IF l_debug THEN
2551 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2552 'Private API 35: SALES_CREDIT_ID is not valid');
2553 END IF;
2554
2555 x_return_status := FND_API.G_RET_STS_ERROR;
2556 END IF;
2557 CLOSE C_Sales_Credit_Id_Exists;
2558 END IF;
2559
2560 END IF;
2561
2562 -- Standard call to get message count and if count is 1, get message info.
2563 FND_MSG_PUB.Count_And_Get
2564 ( p_count => x_msg_count,
2565 p_data => x_msg_data
2566 );
2567
2568 END Validate_SALES_CREDIT_ID;
2569
2570
2571 PROCEDURE Validate_LEAD_ID (
2572 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2573 P_Validation_mode IN VARCHAR2,
2574 P_LEAD_ID IN NUMBER,
2575 X_Return_Status OUT NOCOPY VARCHAR2,
2576 X_Msg_Count OUT NOCOPY NUMBER,
2577 X_Msg_Data OUT NOCOPY VARCHAR2
2578 )
2579 IS
2580
2581 CURSOR C_Lead_Id_Exists (c_Lead_Id NUMBER) IS
2582 SELECT 'X'
2583 FROM as_leads
2584 WHERE lead_id = c_Lead_Id;
2585
2586 l_val VARCHAR2(1);
2587 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2588 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Validate_LEAD_ID';
2589
2590
2591 BEGIN
2592
2593 -- Initialize message list if p_init_msg_list is set to TRUE.
2594 IF FND_API.to_Boolean( p_init_msg_list )
2595 THEN
2596 FND_MSG_PUB.initialize;
2597 END IF;
2598
2599
2600 -- Initialize API return status to SUCCESS
2601 x_return_status := FND_API.G_RET_STS_SUCCESS;
2602
2603
2604 IF (p_LEAD_ID is NULL) or (p_LEAD_ID = FND_API.G_MISS_NUM)
2605 THEN
2606 IF l_debug THEN
2607 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2608 'Private API 36: Violate NOT NULL constraint(LEAD_ID)');
2609 END IF;
2610
2611 x_return_status := FND_API.G_RET_STS_ERROR;
2612 ELSE
2613 OPEN C_Lead_Id_Exists (p_Lead_Id);
2614 FETCH C_Lead_Id_Exists into l_val;
2615 IF C_Lead_Id_Exists%NOTFOUND
2616 THEN
2617 IF l_debug THEN
2618 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2619 'Private API 37: LEAD_ID is not valid');
2620 END IF;
2621
2622 x_return_status := FND_API.G_RET_STS_ERROR;
2623 END IF;
2624 CLOSE C_Lead_Id_Exists;
2625 END IF;
2626
2627 -- Standard call to get message count and if count is 1, get message info.
2628 FND_MSG_PUB.Count_And_Get
2629 ( p_count => x_msg_count,
2630 p_data => x_msg_data
2631 );
2632
2633 END Validate_LEAD_ID;
2634
2635
2636 PROCEDURE Validate_LEAD_LINE_ID (
2637 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2638 P_Validation_mode IN VARCHAR2,
2639 P_LEAD_LINE_ID IN NUMBER,
2640 X_Return_Status OUT NOCOPY VARCHAR2,
2641 X_Msg_Count OUT NOCOPY NUMBER,
2642 X_Msg_Data OUT NOCOPY VARCHAR2
2643 )
2644 IS
2645
2646 CURSOR C_Lead_Line_Id_Exists (c_Lead_Line_Id NUMBER) IS
2647 SELECT 'X'
2648 FROM as_lead_lines
2649 WHERE lead_line_id = c_Lead_Line_Id;
2650
2651 l_val VARCHAR2(1);
2652 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2653 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Validate_LEAD_LINE_ID';
2654
2655 BEGIN
2656
2657 -- Initialize message list if p_init_msg_list is set to TRUE.
2658 IF FND_API.to_Boolean( p_init_msg_list )
2659 THEN
2660 FND_MSG_PUB.initialize;
2661 END IF;
2662
2663
2664 -- Initialize API return status to SUCCESS
2665 x_return_status := FND_API.G_RET_STS_SUCCESS;
2666
2667
2668 IF (p_LEAD_LINE_ID is NULL) or (p_LEAD_LINE_ID = FND_API.G_MISS_NUM)
2669 THEN
2670 IF l_debug THEN
2671 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2672 'Private API 38: Violate NOT NULL constraint(LEAD_LINE_ID)');
2673 END IF;
2674
2675 x_return_status := FND_API.G_RET_STS_ERROR;
2676 ELSE
2677 OPEN C_Lead_Line_Id_Exists (p_Lead_Line_Id);
2678 FETCH C_Lead_Line_Id_Exists into l_val;
2679 IF C_Lead_Line_Id_Exists%NOTFOUND
2680 THEN
2681 IF l_debug THEN
2682 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2683 'Private API 39: LEAD_LINE_ID is not valid');
2684 END IF;
2685
2686 x_return_status := FND_API.G_RET_STS_ERROR;
2687 END IF;
2688 CLOSE C_Lead_Line_Id_Exists;
2689 END IF;
2690
2691 -- Standard call to get message count and if count is 1, get message info.
2692 FND_MSG_PUB.Count_And_Get
2693 ( p_count => x_msg_count,
2694 p_data => x_msg_data
2695 );
2696
2697 END Validate_LEAD_LINE_ID;
2698
2699
2700 PROCEDURE Validate_SALESFORCE_ID (
2701 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2702 P_Validation_mode IN VARCHAR2,
2703 P_SALESFORCE_ID IN NUMBER,
2704 X_Return_Status OUT NOCOPY VARCHAR2,
2705 X_Msg_Count OUT NOCOPY NUMBER,
2706 X_Msg_Data OUT NOCOPY VARCHAR2
2707 )
2708 IS
2709
2710 CURSOR C_Salesforce_Id_Exists (c_Salesforce_Id NUMBER) IS
2711 SELECT 'X'
2712 FROM as_salesforce_v
2713 WHERE salesforce_id = c_Salesforce_Id;
2714
2715 l_val VARCHAR2(1);
2716 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2717 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Validate_SALESFORCE_ID';
2718
2719 BEGIN
2720
2721 -- Initialize message list if p_init_msg_list is set to TRUE.
2722 IF FND_API.to_Boolean( p_init_msg_list )
2723 THEN
2724 FND_MSG_PUB.initialize;
2725 END IF;
2726
2727
2728 -- Initialize API return status to SUCCESS
2729 x_return_status := FND_API.G_RET_STS_SUCCESS;
2730
2731
2732 IF (p_SALESFORCE_ID is NULL) or (p_SALESFORCE_ID = FND_API.G_MISS_NUM)
2733 THEN
2734 IF l_debug THEN
2735 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2736 'Private API 40: Violate NOT NULL constraint(SALESFORCE_ID)');
2737 END IF;
2738
2739 x_return_status := FND_API.G_RET_STS_ERROR;
2740 ELSE
2741 OPEN C_Salesforce_Id_Exists (p_Salesforce_Id);
2742 FETCH C_Salesforce_Id_Exists into l_val;
2743 IF C_Salesforce_Id_Exists%NOTFOUND
2744 THEN
2745 IF l_debug THEN
2746 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2747 'Private API 41: SALESFORCE_ID is not valid');
2748 END IF;
2749
2750 x_return_status := FND_API.G_RET_STS_ERROR;
2751 END IF;
2752 CLOSE C_Salesforce_Id_Exists;
2753 END IF;
2754
2755 -- Standard call to get message count and if count is 1, get message info.
2756 FND_MSG_PUB.Count_And_Get
2757 ( p_count => x_msg_count,
2758 p_data => x_msg_data
2759 );
2760
2761 END Validate_SALESFORCE_ID;
2762
2763
2764 PROCEDURE Validate_PERSON_ID (
2765 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2766 P_Validation_mode IN VARCHAR2,
2767 P_PERSON_ID IN NUMBER,
2768 X_Return_Status OUT NOCOPY VARCHAR2,
2769 X_Msg_Count OUT NOCOPY NUMBER,
2770 X_Msg_Data OUT NOCOPY VARCHAR2
2771 )
2772 IS
2773
2774 CURSOR C_PERSON_ID_Exists(c_PERSON_ID NUMBER) IS
2775 SELECT 'X'
2776 FROM as_salesforce_v
2777 WHERE EMPLOYEE_PERSON_ID = c_PERSON_ID;
2778
2779 l_val VARCHAR2(1);
2780 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2781 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Validate_PERSON_ID';
2782
2783 BEGIN
2784
2785 -- Initialize message list if p_init_msg_list is set to TRUE.
2786 IF FND_API.to_Boolean( p_init_msg_list )
2787 THEN
2788 FND_MSG_PUB.initialize;
2789 END IF;
2790
2791
2792 -- Initialize API return status to SUCCESS
2793 x_return_status := FND_API.G_RET_STS_SUCCESS;
2794
2795 IF (p_PERSON_ID is NOT NULL) and
2796 (p_PERSON_ID <> FND_API.G_MISS_NUM)
2797 THEN
2798 OPEN C_PERSON_ID_Exists (p_PERSON_ID);
2799 FETCH C_PERSON_ID_Exists into l_val;
2800 IF C_PERSON_ID_Exists%NOTFOUND THEN
2801 IF l_debug THEN
2802 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2803 'Private API 42: PERSON_ID is invalid');
2804 END IF;
2805
2806 x_return_status := FND_API.G_RET_STS_ERROR;
2807 END IF;
2808 CLOSE C_PERSON_ID_Exists;
2809 END IF;
2810
2811 -- Standard call to get message count and if count is 1, get message info.
2812 FND_MSG_PUB.Count_And_Get
2813 ( p_count => x_msg_count,
2814 p_data => x_msg_data
2815 );
2816
2817 END Validate_PERSON_ID;
2818
2819
2820 PROCEDURE Validate_SALESGROUP_ID(
2821 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2822 P_Validation_mode IN VARCHAR2,
2823 P_SALESGROUP_ID IN NUMBER,
2824 P_PERSON_ID IN NUMBER,
2825 X_Return_Status OUT NOCOPY VARCHAR2,
2826 X_Msg_Count OUT NOCOPY NUMBER,
2827 X_Msg_Data OUT NOCOPY VARCHAR2
2828 )
2829 IS
2830
2831 CURSOR C_SALES_GROUP_ID_Exists(c_SALES_GROUP_ID NUMBER) IS
2832 SELECT 'X'
2833 FROM as_sales_groups_v
2834 WHERE SALES_GROUP_ID = c_SALES_GROUP_ID;
2835
2836 CURSOR C_PRTNR_SALES_GROUP_ID_Exists(c_SALES_GROUP_ID NUMBER) IS
2837 SELECT 'X'
2838 FROM JTF_RS_GROUPS_B a, JTF_RS_GROUP_USAGES b
2839 WHERE a.group_id = b.group_id
2840 AND b.usage in ('SALES','PRM')
2841 AND sysdate between nvl(a.start_date_active,sysdate) and
2842 nvl(a.end_date_active,sysdate)
2843 AND a.group_id = c_SALES_GROUP_ID;
2844
2845 l_val VARCHAR2(1);
2846 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2847
2848 BEGIN
2849
2850 -- Initialize message list if p_init_msg_list is set to TRUE.
2851 IF FND_API.to_Boolean( p_init_msg_list )
2852 THEN
2853 FND_MSG_PUB.initialize;
2854 END IF;
2855
2856
2857 -- Initialize API return status to SUCCESS
2858 x_return_status := FND_API.G_RET_STS_SUCCESS;
2859
2860 /* IF (p_SALESGROUP_ID is NOT NULL) and (p_SALESGROUP_ID <>
2861 FND_API.G_MISS_NUM)
2862 THEN
2863 IF (p_PERSON_ID is NOT NULL) and (p_PERSON_ID <> FND_API.G_MISS_NUM)
2864 THEN
2865 OPEN C_SALES_GROUP_ID_Exists (p_SALESGROUP_ID);
2866 FETCH C_SALES_GROUP_ID_Exists into l_val;
2867 IF C_SALES_GROUP_ID_Exists%NOTFOUND THEN
2868 IF l_debug THEN
2869 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
2870 'SALES_GROUP_ID1 is invalid');
2871 END IF;
2872
2873 x_return_status := FND_API.G_RET_STS_ERROR;
2874 END IF;
2875 CLOSE C_SALES_GROUP_ID_Exists;
2876 ELSE
2877 OPEN C_PRTNR_SALES_GROUP_ID_Exists (p_SALESGROUP_ID);
2878 FETCH C_PRTNR_SALES_GROUP_ID_Exists into l_val;
2879 IF C_PRTNR_SALES_GROUP_ID_Exists%NOTFOUND THEN
2880 IF l_debug THEN
2881 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
2882 'SALES_GROUP_ID2 is invalid');
2883 END IF;
2884 x_return_status := FND_API.G_RET_STS_ERROR;
2885 END IF;
2886 CLOSE C_PRTNR_SALES_GROUP_ID_Exists;
2887 END IF;
2888 END IF;
2889
2890 */
2891 -- Standard call to get message count and if count is 1, get message info.
2892 FND_MSG_PUB.Count_And_Get
2893 ( p_count => x_msg_count,
2894 p_data => x_msg_data
2895 );
2896
2897 END Validate_SALESGROUP_ID;
2898
2899
2900 PROCEDURE Validate_PARTNER_CUSTOMER_ID (
2901 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2902 P_Validation_mode IN VARCHAR2,
2903 P_PARTNER_CUSTOMER_ID IN NUMBER,
2904 X_Return_Status OUT NOCOPY VARCHAR2,
2905 X_Msg_Count OUT NOCOPY NUMBER,
2906 X_Msg_Data OUT NOCOPY VARCHAR2
2907 )
2908 IS
2909
2910 CURSOR C_PARTNER_CUSTOMER_ID_Exists(c_PARTNER_CUSTOMER_ID NUMBER) IS
2911 SELECT 'X'
2912 FROM as_salesforce_v
2913 WHERE PARTNER_CUSTOMER_ID = c_PARTNER_CUSTOMER_ID;
2914
2915 l_val VARCHAR2(1);
2916 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2917 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Validate_PARTNER_CUSTOMER_ID';
2918
2919 BEGIN
2920
2921 -- Initialize message list if p_init_msg_list is set to TRUE.
2922 IF FND_API.to_Boolean( p_init_msg_list )
2923 THEN
2924 FND_MSG_PUB.initialize;
2925 END IF;
2926
2927
2928 -- Initialize API return status to SUCCESS
2929 x_return_status := FND_API.G_RET_STS_SUCCESS;
2930
2931 IF (p_PARTNER_CUSTOMER_ID is NOT NULL) and
2932 (p_PARTNER_CUSTOMER_ID <> FND_API.G_MISS_NUM)
2933 THEN
2934 OPEN C_PARTNER_CUSTOMER_ID_Exists (p_PARTNER_CUSTOMER_ID);
2935 FETCH C_PARTNER_CUSTOMER_ID_Exists into l_val;
2936 IF C_PARTNER_CUSTOMER_ID_Exists%NOTFOUND THEN
2937 IF l_debug THEN
2938 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2939 'Private API 43: PARTNER_CUSTOMER_ID is invalid');
2940 END IF;
2941
2942 x_return_status := FND_API.G_RET_STS_ERROR;
2943 END IF;
2944 CLOSE C_PARTNER_CUSTOMER_ID_Exists;
2945 END IF;
2946
2947 -- Standard call to get message count and if count is 1, get message info.
2948 FND_MSG_PUB.Count_And_Get
2949 ( p_count => x_msg_count,
2950 p_data => x_msg_data
2951 );
2952
2953 END Validate_PARTNER_CUSTOMER_ID;
2954
2955
2956 PROCEDURE Validate_PARTNER_ADDRESS_ID (
2957 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2958 P_Validation_mode IN VARCHAR2,
2959 P_PARTNER_ADDRESS_ID IN NUMBER,
2960 X_Return_Status OUT NOCOPY VARCHAR2,
2961 X_Msg_Count OUT NOCOPY NUMBER,
2962 X_Msg_Data OUT NOCOPY VARCHAR2
2963 )
2964 IS
2965
2966 CURSOR C_PARTNER_ADDRESS_ID_Exists(c_PARTNER_ADDRESS_ID NUMBER) IS
2967 SELECT 'X'
2968 FROM as_salesforce_v
2969 WHERE PARTNER_ADDRESS_ID = c_PARTNER_ADDRESS_ID;
2970
2971 l_val VARCHAR2(1);
2972 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2973 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Validate_PARTNER_ADDRESS_ID';
2974
2975 BEGIN
2976
2977 -- Initialize message list if p_init_msg_list is set to TRUE.
2978 IF FND_API.to_Boolean( p_init_msg_list )
2979 THEN
2980 FND_MSG_PUB.initialize;
2981 END IF;
2982
2983
2984 -- Initialize API return status to SUCCESS
2985 x_return_status := FND_API.G_RET_STS_SUCCESS;
2986
2987 IF (p_PARTNER_ADDRESS_ID is NOT NULL) and
2988 (p_PARTNER_ADDRESS_ID <> FND_API.G_MISS_NUM)
2989 THEN
2990 OPEN C_PARTNER_ADDRESS_ID_Exists (p_PARTNER_ADDRESS_ID);
2991 FETCH C_PARTNER_ADDRESS_ID_Exists into l_val;
2992 IF C_PARTNER_ADDRESS_ID_Exists%NOTFOUND THEN
2993 IF l_debug THEN
2994 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2995 'Private API 44: PARTNER_ADDRESS_ID is invalid');
2996 END IF;
2997
2998 x_return_status := FND_API.G_RET_STS_ERROR;
2999 END IF;
3000 CLOSE C_PARTNER_ADDRESS_ID_Exists;
3001 END IF;
3002
3003 -- Standard call to get message count and if count is 1, get message info.
3004 FND_MSG_PUB.Count_And_Get
3005 ( p_count => x_msg_count,
3006 p_data => x_msg_data
3007 );
3008
3009 END Validate_PARTNER_ADDRESS_ID;
3010
3011
3012 PROCEDURE Validate_CREDIT_TYPE_ID (
3013 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3014 P_Validation_mode IN VARCHAR2,
3015 P_CREDIT_TYPE_ID IN NUMBER,
3016 X_Return_Status OUT NOCOPY VARCHAR2,
3017 X_Msg_Count OUT NOCOPY NUMBER,
3018 X_Msg_Data OUT NOCOPY VARCHAR2
3019 )
3020 IS
3021
3022 CURSOR C_Credit_Type_Id_Exists (c_Credit_Type_Id NUMBER) IS
3023 SELECT 'X'
3024 FROM oe_sales_credit_types
3025 WHERE sales_credit_type_id = c_Credit_Type_Id;
3026
3027 l_val VARCHAR2(1);
3028 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3029 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Validate_CREDIT_TYPE_ID';
3030
3031 BEGIN
3032
3033 -- Initialize message list if p_init_msg_list is set to TRUE.
3034 IF FND_API.to_Boolean( p_init_msg_list )
3035 THEN
3036 FND_MSG_PUB.initialize;
3037 END IF;
3038
3039
3040 -- Initialize API return status to SUCCESS
3041 x_return_status := FND_API.G_RET_STS_SUCCESS;
3042
3043
3044 IF (p_CREDIT_TYPE_ID is NULL) or (p_CREDIT_TYPE_ID = FND_API.G_MISS_NUM)
3045 THEN
3046 --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
3047 -- 'Private API 45: Violate NOT NULL constraint(CREDIT_TYPE_ID)');
3048
3049 AS_UTILITY_PVT.Set_Message(
3050 p_module => l_module,
3051 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
3052 p_msg_name => 'API_MISSING_ID',
3053 p_token1 => 'COLUMN',
3054 p_token1_value => 'CREDIT_TYPE_ID');
3055
3056
3057 x_return_status := FND_API.G_RET_STS_ERROR;
3058 ELSE
3059 OPEN C_Credit_Type_Id_Exists (p_Credit_Type_Id);
3060 FETCH C_Credit_Type_Id_Exists into l_val;
3061 IF C_Credit_Type_Id_Exists%NOTFOUND
3062 THEN
3063 IF l_debug THEN
3064 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
3065 'Private API 46: CREDIT_TYPE_ID is not valid');
3066 END IF;
3067
3068 x_return_status := FND_API.G_RET_STS_ERROR;
3069 END IF;
3070 CLOSE C_Credit_Type_Id_Exists;
3071 END IF;
3072
3073 -- Standard call to get message count and if count is 1, get message info.
3074 FND_MSG_PUB.Count_And_Get
3075 ( p_count => x_msg_count,
3076 p_data => x_msg_data
3077 );
3078
3079 END Validate_CREDIT_TYPE_ID;
3080
3081
3082 -- Hint: inter-field level validation can be added here.
3083 -- Hint: If p_validation_mode = AS_UTILITY_PVT.G_VALIDATE_UPDATE, we should use cursor
3084 -- to get old values for all fields used in inter-field validation and set all
3085 -- G_MISS_XXX fields to original value stored in database table.
3086 PROCEDURE Validate_SALES_CREDIT_rec(
3087 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3088 P_Validation_mode IN VARCHAR2,
3089 P_SALES_CREDIT_Rec IN AS_OPPORTUNITY_PUB.SALES_CREDIT_Rec_Type,
3090 X_Return_Status OUT NOCOPY VARCHAR2,
3091 X_Msg_Count OUT NOCOPY NUMBER,
3092 X_Msg_Data OUT NOCOPY VARCHAR2
3093 )
3094 IS
3095
3096 CURSOR C_Salesforce_Person_Exists (X_Salesforce_Id NUMBER,
3097 X_Person_Id NUMBER ) IS
3098 SELECT 'X'
3099 FROM jtf_rs_resource_extns res,
3100 jtf_rs_role_relations rrel,
3101 jtf_rs_roles_b role
3102 WHERE sysdate between res.start_date_active and nvl(res.end_date_active,sysdate)
3103 AND sysdate between rrel.start_date_active and nvl(rrel.end_date_active,sysdate)
3104 AND res.resource_id = rrel.role_resource_id
3105 AND rrel.role_resource_type = 'RS_INDIVIDUAL'
3106 AND rrel.role_id = role.role_id
3107 AND role.role_type_code IN ('SALES', 'TELESALES', 'FIELDSALES', 'PRM')
3108 AND role.admin_flag = 'N'
3109 AND res.resource_id = X_Salesforce_Id
3110 AND res.source_id = X_Person_Id
3111 AND res.category ='EMPLOYEE';
3112
3113 CURSOR C_Salesforce_Partner_Exists (X_Salesforce_Id NUMBER,
3114 X_Partner_Customer_Id NUMBER) IS
3115 SELECT 'X'
3116 FROM as_salesforce_v
3117 WHERE ((type = 'PARTNER' and partner_customer_id = X_Partner_Customer_Id)
3118 or (type = 'PARTY' and partner_contact_id = X_Partner_Customer_Id))
3119 AND salesforce_id = X_Salesforce_Id;
3120
3121 CURSOR C_Salesgroup_Exists (X_Sales_Group_Id NUMBER) IS
3122 SELECT 'X'
3123 FROM as_sales_groups_v
3124 WHERE sales_group_id = X_Sales_Group_Id;
3125
3126 l_val VARCHAR2(1);
3127 l_api_name CONSTANT VARCHAR2(30) := 'Validate_sales_credit_rec';
3128 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3129 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Validate_SALES_CREDIT_rec';
3130
3131 BEGIN
3132
3133 -- Initialize message list if p_init_msg_list is set to TRUE.
3134 IF FND_API.to_Boolean( p_init_msg_list )
3135 THEN
3136 FND_MSG_PUB.initialize;
3137 END IF;
3138
3139 -- Debug Message
3140 IF l_debug THEN
3141 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3142 'Private API 47: ' || l_api_name || ' start');
3143 END IF;
3144
3145
3146
3147 -- Initialize API return status to SUCCESS
3148 x_return_status := FND_API.G_RET_STS_SUCCESS;
3149
3150 -- Validate member columns
3151 --
3152 -- Member must exist
3153 --
3154 IF l_debug THEN
3155 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'salesforce id' || p_sales_credit_rec.salesforce_id);
3156
3157 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'person_id' || p_sales_credit_rec.person_id);
3158 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'partner_customer_id' || p_sales_credit_rec.partner_customer_id);
3159 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'partner_customer_id' || p_sales_credit_rec.salesgroup_id);
3160 END IF;
3161
3162
3163 IF p_sales_credit_rec.salesforce_id is NULL
3164 THEN
3165 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3166 THEN
3167 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
3168 FND_MESSAGE.Set_Token('COLUMN', 'TRANS_SALESFORCE', TRUE);
3169 FND_MSG_PUB.ADD;
3170 END IF;
3171
3172 IF l_debug THEN
3173 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3174 'Private API 48: SALESFORCE_ID is NULL');
3175 END IF;
3176
3177 x_return_status := FND_API.G_RET_STS_ERROR;
3178
3179 -- Employee and Partner cannot exist in the same record
3180 --
3181 -- ELSIF ( nvl(p_sales_credit_rec.partner_customer_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num or
3182 -- nvl(p_sales_credit_rec.partner_address_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num ) and
3183 -- ( nvl(p_sales_credit_rec.person_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num or
3184 -- nvl(p_sales_credit_rec.salesgroup_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num )
3185 ELSIF ( nvl(p_sales_credit_rec.partner_customer_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num) and
3186 (nvl(p_sales_credit_rec.person_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num)
3187
3188
3189 THEN
3190 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3191 THEN
3192 FND_MESSAGE.Set_Name('AS', 'API_MEMBER_TOO_MANY_VALUES');
3193 FND_MSG_PUB.ADD;
3194 END IF;
3195
3196 x_return_status := FND_API.G_RET_STS_ERROR;
3197
3198 -- Validate employee if one exists
3199 --
3200 ELSIF nvl(p_sales_credit_rec.person_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
3201 THEN
3202
3203 -- First sales group must exist for employee sales credit record
3204 --
3205 -- Fix bug 855326 Remove the checking on salesgroup_id, Actually
3206 -- If an admin who is not in any group created an opp., sales group
3207 -- id in sales credit table would be NULL. So, remove this logic.
3208 -- IF p_sales_credit_rec.salesgroup_id is NULL
3209 -- THEN
3210 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3211 -- THEN
3212 -- FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
3213 -- FND_MESSAGE.Set_Token('COLUMN', 'SALESGROUP_ID', FALSE);
3214 -- FND_MSG_PUB.ADD;
3215 -- END IF;
3216 --
3217 -- l_return_status := FND_API.G_RET_STS_ERROR;
3218 --
3219 -- ELSE
3220
3221 OPEN C_Salesforce_Person_Exists (p_sales_credit_rec.salesforce_id,
3222 p_sales_credit_rec.person_id );
3223 FETCH C_Salesforce_Person_Exists INTO l_val;
3224 IF C_Salesforce_Person_Exists%NOTFOUND
3225 THEN
3226 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3227 THEN
3228 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
3229 FND_MESSAGE.Set_Token('COLUMN', 'SALESFORCE_ID, PERSON_ID', FALSE);
3230 FND_MESSAGE.Set_Token('VALUE', p_sales_credit_rec.salesforce_id || ',' ||
3231 p_sales_credit_rec.person_id, FALSE);
3232 FND_MSG_PUB.ADD;
3233 END IF;
3234
3235 x_return_status := FND_API.G_RET_STS_ERROR;
3236 END IF;
3237 CLOSE C_Salesforce_Person_Exists;
3238
3239 -- Validate that partner exists
3240 --
3241 ELSIF nvl(p_sales_credit_rec.partner_customer_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num
3242 THEN
3243 OPEN C_Salesforce_Partner_Exists (p_sales_credit_rec.salesforce_id,
3244 p_sales_credit_rec.partner_customer_id);
3245 FETCH C_Salesforce_Partner_Exists INTO l_val;
3246 IF C_Salesforce_Partner_Exists%NOTFOUND
3247 THEN
3248 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3249 THEN
3250 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
3251 FND_MESSAGE.Set_Token('COLUMN', 'SALESFORCE_ID, PARTNER_CUSTOMER_ID,
3252 PARTNER_ADDRESS_ID', FALSE);
3253 FND_MESSAGE.Set_Token('VALUE', p_sales_credit_rec.salesforce_id || ',' ||
3254 p_sales_credit_rec.partner_customer_id || ',' ||
3255 p_sales_credit_rec.partner_address_id , FALSE);
3256 FND_MSG_PUB.ADD;
3257 END IF;
3258
3259 x_return_status := FND_API.G_RET_STS_ERROR;
3260 END IF;
3261 CLOSE C_Salesforce_Partner_Exists;
3262 ELSE
3263 IF l_debug THEN
3264 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3265 'Private API 49: Both PERSON_ID and PARTNER_CUSTOMER_ID are NULL');
3266 END IF;
3267
3268 x_return_status := FND_API.G_RET_STS_ERROR;
3269 END IF;
3270
3271 -- Debug Message
3272 IF l_debug THEN
3273 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3274 'Private API 50: ' || l_api_name || ' end');
3275 END IF;
3276
3277
3278 -- Standard call to get message count and if count is 1, get message info.
3279 FND_MSG_PUB.Count_And_Get
3280 ( p_count => x_msg_count,
3281 p_data => x_msg_data
3282 );
3283
3284 END Validate_SALES_CREDIT_Rec;
3285
3286 PROCEDURE Validate_sales_credit(
3287 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3288 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3289 P_Validation_mode IN VARCHAR2,
3290 P_SALES_CREDIT_Rec IN AS_OPPORTUNITY_PUB.SALES_CREDIT_Rec_Type,
3291 X_Return_Status OUT NOCOPY VARCHAR2,
3292 X_Msg_Count OUT NOCOPY NUMBER,
3293 X_Msg_Data OUT NOCOPY VARCHAR2
3294 )
3295 IS
3296 l_api_name CONSTANT VARCHAR2(30) := 'Validate_sales_credit';
3297 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3298 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.Validate_sales_credit';
3299
3300 BEGIN
3301
3302 -- Debug Message
3303 IF l_debug THEN
3304 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3305 'Private API 51: ' || l_api_name || 'start');
3306 END IF;
3307
3308
3309
3310 -- Initialize API return status to SUCCESS
3311 x_return_status := FND_API.G_RET_STS_SUCCESS;
3312
3313 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM) THEN
3314 -- Hint: We provide validation procedure for every column. Developer should delete
3315 -- unnecessary validation procedures.
3316
3317 IF l_debug THEN
3318 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3319 'Private API 52: Validate Items start');
3320 END IF;
3321 -- Begin Added for ASNB
3322 IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'), 'N') = 'Y' AND
3323 nvl(fnd_profile.value('AS_FORECAST_CREDIT_TYPE_ID'), 'N') <> P_SALES_CREDIT_Rec.CREDIT_TYPE_ID THEN
3324 duplicate_sales_credit(
3325 p_init_msg_list => FND_API.G_FALSE,
3326 p_validation_mode => p_validation_mode,
3327 P_SALES_CREDIT_Rec => P_SALES_CREDIT_Rec,
3328 x_return_status => x_return_status,
3329 x_msg_count => x_msg_count,
3330 x_msg_data => x_msg_data);
3331 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3332 raise FND_API.G_EXC_ERROR;
3333 END IF;
3334
3335 IF l_debug THEN
3336 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3337 'Private API 53: Validated SALES_CREDIT_ID');
3338 END IF;
3339 END IF;
3340 -- End Added for ASNB
3341
3342 Validate_SALES_CREDIT_ID(
3343 p_init_msg_list => FND_API.G_FALSE,
3344 p_validation_mode => p_validation_mode,
3345 p_SALES_CREDIT_ID => P_SALES_CREDIT_Rec.SALES_CREDIT_ID,
3346 x_return_status => x_return_status,
3347 x_msg_count => x_msg_count,
3348 x_msg_data => x_msg_data);
3349 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3350 raise FND_API.G_EXC_ERROR;
3351 END IF;
3352
3353 IF l_debug THEN
3354 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3355 'Private API 53: Validated SALES_CREDIT_ID');
3356 END IF;
3357
3358
3359 Validate_LEAD_ID(
3360 p_init_msg_list => FND_API.G_FALSE,
3361 p_validation_mode => p_validation_mode,
3362 p_LEAD_ID => P_SALES_CREDIT_Rec.LEAD_ID,
3363 x_return_status => x_return_status,
3364 x_msg_count => x_msg_count,
3365 x_msg_data => x_msg_data);
3366 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3367 raise FND_API.G_EXC_ERROR;
3368 END IF;
3369
3370 IF l_debug THEN
3371 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3372 'Private API 54: Validated LEAD_ID');
3373 END IF;
3374
3375
3376 Validate_LEAD_LINE_ID(
3377 p_init_msg_list => FND_API.G_FALSE,
3378 p_validation_mode => p_validation_mode,
3379 p_LEAD_LINE_ID => P_SALES_CREDIT_Rec.LEAD_LINE_ID,
3380 x_return_status => x_return_status,
3381 x_msg_count => x_msg_count,
3382 x_msg_data => x_msg_data);
3383 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3384 raise FND_API.G_EXC_ERROR;
3385 END IF;
3386
3387 IF l_debug THEN
3388 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3389 'Private API 55: Validated LEAD_LINE_ID');
3390
3391 END IF;
3392
3393 /* validated in record level
3394 Validate_SALESFORCE_ID(
3395 p_init_msg_list => FND_API.G_FALSE,
3396 p_validation_mode => p_validation_mode,
3397 p_SALESFORCE_ID => P_SALES_CREDIT_Rec.SALESFORCE_ID,
3398 x_return_status => x_return_status,
3399 x_msg_count => x_msg_count,
3400 x_msg_data => x_msg_data);
3401 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3402 raise FND_API.G_EXC_ERROR;
3403 END IF;
3404
3405 Validate_PERSON_ID(
3406 p_init_msg_list => FND_API.G_FALSE,
3407 p_validation_mode => p_validation_mode,
3408 p_PERSON_ID => P_SALES_CREDIT_Rec.PERSON_ID,
3409 x_return_status => x_return_status,
3410 x_msg_count => x_msg_count,
3411 x_msg_data => x_msg_data);
3412 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3413 raise FND_API.G_EXC_ERROR;
3414 END IF;
3415 */
3416
3417 Validate_SALESGROUP_ID(
3418 p_init_msg_list => FND_API.G_FALSE,
3419 p_validation_mode => p_validation_mode,
3420 p_SALESGROUP_ID => P_SALES_CREDIT_Rec.SALESGROUP_ID,
3421 p_PERSON_ID => P_SALES_CREDIT_Rec.PERSON_ID,
3422 x_return_status => x_return_status,
3423 x_msg_count => x_msg_count,
3424 x_msg_data => x_msg_data);
3425 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3426 raise FND_API.G_EXC_ERROR;
3427 END IF;
3428
3429 IF l_debug THEN
3430 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3431 'Private API 56: Validated SALESGROUP_ID');
3432 END IF;
3433
3434
3435 /* Validated in record level
3436 Validate_PARTNER_CUSTOMER_ID(
3437 p_init_msg_list => FND_API.G_FALSE,
3438 p_validation_mode => p_validation_mode,
3439 p_PARTNER_CUSTOMER_ID => P_SALES_CREDIT_Rec.PARTNER_CUSTOMER_ID,
3440 x_return_status => x_return_status,
3441 x_msg_count => x_msg_count,
3442 x_msg_data => x_msg_data);
3443 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3444 raise FND_API.G_EXC_ERROR;
3445 END IF;
3446
3447 Validate_PARTNER_ADDRESS_ID(
3448 p_init_msg_list => FND_API.G_FALSE,
3449 p_validation_mode => p_validation_mode,
3450 p_PARTNER_ADDRESS_ID => P_SALES_CREDIT_Rec.PARTNER_ADDRESS_ID,
3451 x_return_status => x_return_status,
3452 x_msg_count => x_msg_count,
3453 x_msg_data => x_msg_data);
3454 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3455 raise FND_API.G_EXC_ERROR;
3456 END IF;
3457 */
3458
3459 Validate_CREDIT_TYPE_ID(
3460 p_init_msg_list => FND_API.G_FALSE,
3461 p_validation_mode => p_validation_mode,
3462 p_CREDIT_TYPE_ID => P_SALES_CREDIT_Rec.CREDIT_TYPE_ID,
3463 x_return_status => x_return_status,
3464 x_msg_count => x_msg_count,
3465 x_msg_data => x_msg_data);
3466 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3467 raise FND_API.G_EXC_ERROR;
3468 END IF;
3469 IF l_debug THEN
3470 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3471 'Private API 57: Validated CREDIT_TYPE_ID ');
3472
3473 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3474 'Private API 58: Validate Items end');
3475 END IF;
3476
3477
3478 END IF;
3479
3480
3481 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_RECORD) THEN
3482 -- Hint: Inter-field level validation can be added here
3483 -- invoke record level validation procedures
3484 Validate_SALES_CREDIT_Rec(
3485 p_init_msg_list => FND_API.G_FALSE,
3486 p_validation_mode => p_validation_mode,
3487 P_SALES_CREDIT_Rec => P_SALES_CREDIT_Rec,
3488 x_return_status => x_return_status,
3489 x_msg_count => x_msg_count,
3490 x_msg_data => x_msg_data);
3491
3492 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3493 IF l_debug THEN
3494 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3495 'Private API 59: SALES_CREDIT_Rec is invalid');
3496 END IF;
3497
3498 raise FND_API.G_EXC_ERROR;
3499 END IF;
3500 END IF;
3501
3502
3503 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_RECORD) THEN
3504 -- invoke inter-record level validation procedures
3505 NULL;
3506 END IF;
3507
3508 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_ENTITY) THEN
3509 -- invoke inter-entity level validation procedures
3510 NULL;
3511 END IF;
3512
3513 -- Debug Message
3514 IF l_debug THEN
3515 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3516 'Private API 60: ' || l_api_name || 'end');
3517 END IF;
3518
3519
3520 END Validate_sales_credit;
3521 -- The following procedure added for ASNB
3522 PROCEDURE duplicate_sales_credit(
3523 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3524 P_Validation_mode IN VARCHAR2,
3525 P_SALES_CREDIT_Rec IN AS_OPPORTUNITY_PUB.SALES_CREDIT_Rec_Type,
3526 X_Return_Status OUT NOCOPY VARCHAR2,
3527 X_Msg_Count OUT NOCOPY NUMBER,
3528 X_Msg_Data OUT NOCOPY VARCHAR2
3529 ) is
3530 CURSOR C_dup_Sales_Credit_Exists IS
3531 SELECT 'X'
3532 FROM as_sales_credits
3533 WHERE lead_id =P_SALES_CREDIT_Rec.lead_id
3534 AND lead_line_id = P_SALES_CREDIT_Rec.lead_line_id
3535 AND salesforce_id = P_SALES_CREDIT_Rec.salesforce_id
3536 AND person_id =P_SALES_CREDIT_Rec.person_id
3537 AND salesgroup_id =P_SALES_CREDIT_Rec.salesgroup_id
3538 AND credit_type_id = P_SALES_CREDIT_Rec.credit_type_id
3539 AND (P_SALES_CREDIT_Rec.sales_credit_id is null or P_SALES_CREDIT_Rec.sales_credit_id = fnd_api.g_miss_num
3540 OR sales_credit_id <> P_SALES_CREDIT_Rec.sales_credit_id);
3541 l_val VARCHAR2(1);
3542 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lscpv.duplicate_sales_credit';
3543 BEGIN
3544 -- Initialize message list if p_init_msg_list is set to TRUE.
3545 IF FND_API.to_Boolean( p_init_msg_list )
3546 THEN
3547 FND_MSG_PUB.initialize;
3548 END IF;
3549
3550
3551 -- Initialize API return status to SUCCESS
3552 x_return_status := FND_API.G_RET_STS_SUCCESS;
3553
3554 OPEN C_dup_Sales_Credit_Exists;
3555 FETCH C_dup_Sales_Credit_Exists into l_val;
3556 IF C_dup_Sales_Credit_Exists%FOUND THEN
3557 AS_UTILITY_PVT.Set_Message(
3558 p_module => l_module,
3559 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
3560 p_msg_name => 'AS_DUP_SALES_CREDITS');
3561 x_return_status := FND_API.G_RET_STS_ERROR;
3562 END IF;
3563 CLOSE C_dup_Sales_Credit_Exists;
3564 FND_MSG_PUB.Count_And_Get
3565 ( p_count => x_msg_count,
3566 p_data => x_msg_data
3567 );
3568
3569 END duplicate_sales_credit;
3570
3571 End AS_OPP_SALES_CREDIT_PVT;