[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_LEAD_LINES_PVT
Source
1 PACKAGE BODY AS_SALES_LEAD_LINES_PVT as
2 /* $Header: asxvsllb.pls 120.2 2006/08/25 21:29:35 solin noship $ */
3 -- Start of Comments
4 -- Package name : AS_SALES_LEAD_LINES_PVT
5 -- Purpose : Sales Leads Lines
6 -- NOTE :
7 -- History :
8 -- 03/29/2001 FFANG Created.
9 --
10 -- END of Comments
11
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_SALES_LEAD_LINES_PVT';
14 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxvsllb.pls';
15
16 -- Local procedure to reset Opp Header with total_amount
17 -- by the sum of the total_amounts of the lines
18
19 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
20 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
21 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
22 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
23
24 PROCEDURE Backupdate_Header(
25 p_sales_lead_id IN NUMBER,
26 x_return_status OUT NOCOPY VARCHAR2
27 )
28 IS
29
30 CURSOR C_line_total IS
31 SELECT sum(budget_amount) line_total
32 FROM as_sales_lead_lines
33 WHERE sales_lead_id = p_sales_lead_id;
34
35 l_line_total NUMBER;
36
37 BEGIN
38 x_return_status := FND_API.G_RET_STS_SUCCESS;
39
40 OPEN C_line_total;
41 FETCH C_line_total into l_line_total;
42 CLOSE C_line_total;
43
44 UPDATE as_sales_leads
45 SET total_amount = nvl(l_line_total, 0),
46 last_update_date = SYSDATE,
47 last_updated_by = FND_GLOBAL.USER_ID,
48 -- creation_Date = SYSDATE, -- solin, for bug 1579950
49 -- created_by = FND_GLOBAL.USER_ID, -- solin, for bug 1579950
50 last_update_login = FND_GLOBAL.CONC_LOGIN_ID
51 WHERE sales_lead_id = p_sales_lead_id;
52 IF (SQL%NOTFOUND) THEN
53 RAISE NO_DATA_FOUND;
54 END IF;
55
56 EXCEPTION
57 WHEN OTHERS
58 THEN
59 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
60 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
61
62 END Backupdate_Header;
63
64
65 -- *************************
66 -- Validation Procedures
67 -- *************************
68 --
69 -- Item level validation procedures
70 --
71
72 PROCEDURE Validate_SALES_LEAD_ID (
73 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
74 P_Validation_mode IN VARCHAR2,
75 P_Sales_Lead_Id IN NUMBER,
76 X_Return_Status OUT NOCOPY VARCHAR2,
77 X_Msg_Count OUT NOCOPY NUMBER,
78 X_Msg_Data OUT NOCOPY VARCHAR2
79 )
80 IS
81 CURSOR C_Sales_Lead_Id_Exists (X_Sales_Lead_Id NUMBER) IS
82 SELECT 'X'
83 FROM as_sales_leads
84 WHERE sales_lead_id = X_Sales_Lead_Id;
85
86 l_val VARCHAR2(1);
87
88 BEGIN
89 -- Initialize message list IF p_init_msg_list is set to TRUE.
90 IF FND_API.to_Boolean( p_init_msg_list )
91 THEN
92 FND_MSG_PUB.initialize;
93 END IF;
94
95 -- Initialize API return status to SUCCESS
96 x_return_status := FND_API.G_RET_STS_SUCCESS;
97
98 -- Debug Message
99 -- IF (AS_DEBUG_LOW_ON) THEN AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
100 -- 'Validate Sales Lead Id'); END IF;
101
102 -- ffang 092000 for bug 1406777
103 -- Calling from Create API
104 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
105 THEN
106 IF (p_SALES_LEAD_ID is NOT NULL) and
107 (p_SALES_LEAD_ID <> FND_API.G_MISS_NUM)
108 THEN
109 OPEN C_Sales_Lead_Id_Exists (p_Sales_Lead_Id);
110 FETCH C_Sales_Lead_Id_Exists into l_val;
111
112 IF C_Sales_Lead_Id_Exists%NOTFOUND
113 THEN
114 AS_UTILITY_PVT.Set_Message(
115 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
116 p_msg_name => 'API_INVALID_ID',
117 p_token1 => 'SALES_LEAD_ID',
118 p_token1_value => p_Sales_Lead_Id);
119
120 x_return_status := FND_API.G_RET_STS_ERROR;
121 END IF;
122 CLOSE C_Sales_Lead_Id_Exists ;
123 END IF;
124
125 -- Calling from Update API
126 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
127 THEN
128 -- validate NOT NULL column
129 IF (p_sales_lead_id is NULL) or (p_sales_lead_id = FND_API.G_MISS_NUM)
130 THEN
131 AS_UTILITY_PVT.Set_Message(
132 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
133 p_msg_name => 'API_MISSING_LEAD_ID');
134
135 x_return_status := FND_API.G_RET_STS_ERROR;
136 ELSE
137 OPEN C_Sales_Lead_Id_Exists (p_sales_lead_id);
138 FETCH C_Sales_Lead_Id_Exists into l_val;
139
140 IF C_Sales_Lead_Id_Exists%NOTFOUND
141 THEN
142 AS_UTILITY_PVT.Set_Message(
143 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
144 p_msg_name => 'API_INVALID_LEAD_ID',
145 p_token1 => 'VALUE',
146 p_token1_value => p_sales_lead_id );
147
148 x_return_status := FND_API.G_RET_STS_ERROR;
149 END IF;
150
151 CLOSE C_Sales_Lead_Id_Exists;
152 END IF;
153 END IF;
154 -- end ffang 092000 for bug 1306777
155
156 -- Standard call to get message count and IF count is 1, get message info.
157 FND_MSG_PUB.Count_And_Get
158 ( p_count => x_msg_count,
159 p_data => x_msg_data );
160
161 END Validate_SALES_LEAD_ID;
162
163
164 PROCEDURE Validate_INTEREST_TYPE_ID (
165 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
166 P_Validation_mode IN VARCHAR2,
167 P_INTEREST_TYPE_ID IN NUMBER,
168 X_Return_Status OUT NOCOPY VARCHAR2,
169 X_Msg_Count OUT NOCOPY NUMBER,
170 X_Msg_Data OUT NOCOPY VARCHAR2
171 )
172 IS
173 CURSOR C_Int_Type_Exists (X_Int_Type_Id NUMBER) IS
174 SELECT distinct 'X'
175 FROM as_interest_types_b
176 WHERE Interest_Type_Id = X_Int_Type_Id
177 -- ffang 012501
178 and ENABLED_FLAG = 'Y'
179 and EXPECTED_PURCHASE_FLAG = 'Y';
180
181 l_variable VARCHAR2(1);
182 BEGIN
183 -- Initialize message list IF p_init_msg_list is set to TRUE.
184 IF FND_API.to_Boolean( p_init_msg_list )
185 THEN
186 FND_MSG_PUB.initialize;
187 END IF;
188
189 -- Initialize API return status to SUCCESS
190 x_return_status := FND_API.G_RET_STS_SUCCESS;
191
192 -- Validate Interest Type ID
193 IF p_interest_type_id is NOT NULL
194 and p_interest_type_id <> FND_API.G_MISS_NUM
195 THEN
196 OPEN C_Int_Type_Exists (p_interest_type_id);
197 FETCH C_Int_Type_Exists INTO l_variable;
198
199 IF (C_Int_Type_Exists%NOTFOUND)
200 THEN
201 AS_UTILITY_PVT.Set_Message(
202 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
203 p_msg_name => 'API_INVALID_ID',
204 p_token1 => 'COLUMN',
205 p_token1_value => 'INTEREST_TYPE_ID',
206 p_token2 => 'VALUE',
207 p_token2_value => p_INTEREST_TYPE_ID );
208 x_return_status := FND_API.G_RET_STS_ERROR;
209 END IF;
210 CLOSE C_Int_Type_Exists;
211 END IF;
212
213 -- Standard call to get message count and IF count is 1, get message info.
214 FND_MSG_PUB.Count_And_Get
215 ( p_count => x_msg_count,
216 p_data => x_msg_data );
217 END Validate_INTEREST_TYPE_ID;
218
219
220 PROCEDURE Validate_PRIM_INT_CODE_ID (
221 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
222 P_Validation_mode IN VARCHAR2,
223 P_INTEREST_TYPE_ID IN NUMBER,
224 P_PRIMARY_INTEREST_CODE_ID IN NUMBER,
225 X_Return_Status OUT NOCOPY VARCHAR2,
226 X_Msg_Count OUT NOCOPY NUMBER,
227 X_Msg_Data OUT NOCOPY VARCHAR2
228 )
229 IS
230 CURSOR C_Prim_Int_Code_Exists (X_Int_Code_Id NUMBER,
231 X_Int_Type_Id NUMBER) IS
232 SELECT 'X'
233 FROM As_Interest_Codes_B Pic
234 WHERE Pic.Interest_Type_Id = X_Int_Type_Id
235 and Pic.Interest_Code_Id = X_Int_Code_Id
236 and Pic.Parent_Interest_Code_Id Is Null
237 -- ffang 012501
238 and ENABLED_FLAG = 'Y';
239
240 l_variable VARCHAR2(1);
241 BEGIN
242
243 -- Initialize message list IF p_init_msg_list is set to TRUE.
244 IF FND_API.to_Boolean( p_init_msg_list )
245 THEN
246 FND_MSG_PUB.initialize;
247 END IF;
248
249 -- Initialize API return status to SUCCESS
250 x_return_status := FND_API.G_RET_STS_SUCCESS;
251
252 -- Validate Primary Interest Code
253 IF p_primary_interest_code_id is NOT NULL
254 and p_primary_interest_code_id <> FND_API.G_MISS_NUM
255 THEN
256 OPEN C_Prim_Int_Code_Exists ( p_primary_interest_code_id,
257 p_interest_type_id);
258 FETCH C_Prim_Int_Code_Exists INTO l_variable;
259
260 IF (C_Prim_Int_Code_Exists%NOTFOUND)
261 THEN
262 AS_UTILITY_PVT.Set_Message(
263 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
264 p_msg_name => 'API_INVALID_ID',
265 p_token1 => 'COLUMN',
266 p_token1_value => 'PRIMARY_INTEREST_CODE_ID',
267 p_token2 => 'VALUE',
268 p_token2_value => p_PRIMARY_INTEREST_CODE_ID );
269 x_return_status := FND_API.G_RET_STS_ERROR;
270 END IF;
271 CLOSE C_Prim_Int_Code_Exists;
272 END IF;
273
274 -- Standard call to get message count and IF count is 1, get message info.
275 FND_MSG_PUB.Count_And_Get
276 ( p_count => x_msg_count,
277 p_data => x_msg_data );
278 END Validate_PRIM_INT_CODE_ID;
279
280
281 PROCEDURE Validate_SEC_INT_CODE_ID (
282 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
283 P_Validation_mode IN VARCHAR2,
284 P_INTEREST_TYPE_ID IN NUMBER,
285 P_PRIMARY_INTEREST_CODE_ID IN NUMBER,
286 P_SECONDARY_INTEREST_CODE_ID IN NUMBER,
287 X_Return_Status OUT NOCOPY VARCHAR2,
288 X_Msg_Count OUT NOCOPY NUMBER,
289 X_Msg_Data OUT NOCOPY VARCHAR2
290 )
291 IS
292 CURSOR C_Sec_Int_Code_Exists (X_Sec_Int_Code_Id NUMBER,
293 X_Int_Code_Id NUMBER, X_Int_Type_Id NUMBER) IS
294 SELECT 'X'
295 FROM As_Interest_Codes_B Sic
296 WHERE Sic.Interest_Type_Id = X_Int_Type_Id
297 And Sic.Interest_Code_Id = X_Sec_Int_Code_Id
298 And Sic.Parent_Interest_Code_Id = X_Int_Code_Id
299 -- ffang 012501
300 and ENABLED_FLAG = 'Y';
301
302 l_variable VARCHAR2(1);
303 BEGIN
304 -- Initialize message list IF p_init_msg_list is set to TRUE.
305 IF FND_API.to_Boolean( p_init_msg_list )
306 THEN
307 FND_MSG_PUB.initialize;
308 END IF;
309
310 -- Initialize API return status to SUCCESS
311 x_return_status := FND_API.G_RET_STS_SUCCESS;
312
313 -- Validate Secondary Interest Code
314 IF (p_secondary_interest_code_id is NOT NULL
315 and p_secondary_interest_code_id <> FND_API.G_MISS_NUM)
316 THEN
317 OPEN C_Sec_Int_Code_Exists (p_secondary_interest_code_id,
318 p_primary_interest_code_id,
319 p_interest_type_id);
320 FETCH C_Sec_Int_Code_Exists INTO l_variable;
321 IF (C_Sec_Int_Code_Exists%NOTFOUND)
322 THEN
323 AS_UTILITY_PVT.Set_Message(
324 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
325 p_msg_name => 'API_INVALID_ID',
326 p_token1 => 'COLUMN',
327 p_token1_value => 'SECONDARY_INTEREST_CODE_ID',
328 p_token2 => 'VALUE',
329 p_token2_value => p_SECONDARY_INTEREST_CODE_ID );
330 x_return_status := FND_API.G_RET_STS_ERROR;
331 END IF;
332 CLOSE C_Sec_Int_Code_Exists;
333 END IF;
334
335 -- Standard call to get message count and IF count is 1, get message info.
336 FND_MSG_PUB.Count_And_Get
337 ( p_count => x_msg_count,
338 p_data => x_msg_data );
339 END Validate_SEC_INT_CODE_ID;
340
341
342 PROCEDURE Validate_INV_ORG_ID (
343 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
344 P_Validation_mode IN VARCHAR2,
345 P_INVENTORY_ITEM_ID IN NUMBER,
346 P_ORGANIZATION_ID IN NUMBER,
347 X_Return_Status OUT NOCOPY VARCHAR2,
348 X_Msg_Count OUT NOCOPY NUMBER,
349 X_Msg_Data OUT NOCOPY VARCHAR2
350 )
351 IS
352 CURSOR C_Inventory_Item_Exists (X_Inventory_Item_Id NUMBER,
353 X_Organization_Id NUMBER) IS
354 SELECT 'X'
355 FROM mtl_system_items
356 WHERE inventory_item_id = X_Inventory_Item_Id
357 and organization_id = X_Organization_Id;
358 l_val VARCHAR2(1);
359 BEGIN
360 -- Initialize message list IF p_init_msg_list is set to TRUE.
361 IF FND_API.to_Boolean( p_init_msg_list )
362 THEN
363 FND_MSG_PUB.initialize;
364 END IF;
365
366 -- Initialize API return status to SUCCESS
367 x_return_status := FND_API.G_RET_STS_SUCCESS;
368
369 -- Debug Message
370 -- IF (AS_DEBUG_LOW_ON) THEN AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
371 -- 'Validate Invent. item Org. ID'); END IF;
372
373 -- Validate Inventory Item and Organization Id
374 IF (p_inventory_item_id is NOT NULL
375 AND p_inventory_item_id <> FND_API.G_MISS_NUM
376 AND p_organization_id IS NOT NULL
377 AND p_organization_id <> FND_API.G_MISS_NUM)
378 THEN
379 OPEN C_Inventory_Item_Exists ( p_inventory_item_id, p_organization_id );
380 FETCH C_Inventory_Item_Exists into l_val;
381
382 IF C_Inventory_Item_Exists%NOTFOUND
383 THEN
384 AS_UTILITY_PVT.Set_Message(
385 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
386 p_msg_name => 'API_INVALID_ID',
387 p_token1 => 'COLUMN',
388 p_token1_value => 'INVENTORY_ITEM',
389 p_token2 => 'VALUE',
390 p_token2_value => p_INVENTORY_ITEM_ID );
391 x_return_status := FND_API.G_RET_STS_ERROR;
392 END IF;
393 CLOSE C_Inventory_Item_Exists;
394 END IF;
395
396 -- Standard call to get message count and IF count is 1, get message info.
397 FND_MSG_PUB.Count_And_Get
398 ( p_count => x_msg_count,
399 p_data => x_msg_data
400 );
401
402 END Validate_INV_ORG_ID;
403
404
405 PROCEDURE Validate_UOM_CODE (
406 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
407 P_Validation_mode IN VARCHAR2,
408 P_UOM_CODE IN VARCHAR2,
409 X_Return_Status OUT NOCOPY VARCHAR2,
410 X_Msg_Count OUT NOCOPY NUMBER,
411 X_Msg_Data OUT NOCOPY VARCHAR2
412 )
413 IS
414 l_val varchar2(1);
415 CURSOR C_UOM_Exists (X_Uom_Code VARCHAR2) IS
416 SELECT 'X'
417 FROM mtl_units_of_measure
418 WHERE uom_code = X_Uom_Code;
419 BEGIN
420 -- Initialize message list IF p_init_msg_list is set to TRUE.
421 IF FND_API.to_Boolean( p_init_msg_list )
422 THEN
423 FND_MSG_PUB.initialize;
424 END IF;
425
426 -- Initialize API return status to SUCCESS
427 x_return_status := FND_API.G_RET_STS_SUCCESS;
428
429 -- Debug Message
430 -- IF (AS_DEBUG_LOW_ON) THEN AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
431 -- 'Validate UOM code'); END IF;
432
433 -- Validate UOM
434 IF (p_uom_code is NOT NULL AND p_uom_code <> FND_API.G_MISS_CHAR)
435 THEN
436 OPEN C_UOM_Exists ( p_uom_code );
437 FETCH C_UOM_Exists into l_val;
438
439 IF C_UOM_Exists%NOTFOUND
440 THEN
441 AS_UTILITY_PVT.Set_Message(
442 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
443 p_msg_name => 'API_INVALID_ID',
444 p_token1 => 'COLUMN',
445 p_token1_value => 'UOM_CODE',
446 p_token2 => 'VALUE',
447 p_token2_value => p_UOM_CODE );
448
449 x_return_status := FND_API.G_RET_STS_ERROR;
450 END IF;
451 CLOSE C_UOM_Exists;
452 END IF;
453
454 -- Standard call to get message count and IF count is 1, get message info.
455 FND_MSG_PUB.Count_And_Get
456 ( p_count => x_msg_count,
457 p_data => x_msg_data
458 );
459
460 END Validate_UOM_CODE;
461
462
463 PROCEDURE validate_category_id (
464 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
465 P_Validation_mode IN VARCHAR2,
466 p_category_id IN NUMBER,
467 X_Return_Status OUT NOCOPY VARCHAR2,
468 X_Msg_Count OUT NOCOPY NUMBER,
469 X_Msg_Data OUT NOCOPY VARCHAR2
470 )
471 IS
472 CURSOR C_category_id_Exists (X_cat_Id NUMBER) IS
473 SELECT distinct 'X'
474 FROM ENI_PROD_DEN_HRCHY_PARENTS_V
475 WHERE category_id = X_cat_Id
476 AND (disable_date IS NULL OR disable_date > SYSDATE)
477 and PURCHASE_interest = 'Y';
478 l_variable VARCHAR2(1);
479 BEGIN
480 -- Initialize message list IF p_init_msg_list is set to TRUE.
481 IF FND_API.to_Boolean( p_init_msg_list )
482 THEN
483 FND_MSG_PUB.initialize;
484 END IF;
485 -- Initialize API return status to SUCCESS
486 x_return_status := FND_API.G_RET_STS_SUCCESS;
487 -- Validate CATEGORY ID
488 IF p_category_id is NOT NULL
489 and p_category_id <> FND_API.G_MISS_NUM
490 THEN
491 OPEN C_category_id_Exists (p_category_id);
492 FETCH C_category_id_Exists INTO l_variable;
493
494 IF (C_category_id_Exists%NOTFOUND)
495 THEN
496 AS_UTILITY_PVT.Set_Message(
497 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
498 p_msg_name => 'API_INVALID_ID',
499 p_token1 => 'COLUMN',
500 p_token1_value => 'CATEGORY_ID',
501 p_token2 => 'VALUE',
502 p_token2_value => p_category_id );
503 x_return_status := FND_API.G_RET_STS_ERROR;
504 END IF;
505 CLOSE C_category_id_Exists;
506 END IF;
507
508 -- Standard call to get message count and IF count is 1, get message info.
509 FND_MSG_PUB.Count_And_Get
510 ( p_count => x_msg_count,
511 p_data => x_msg_data );
512 END validate_category_id;
513
514
515
516 PROCEDURE validate_category_set_id (
517 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
518 P_Validation_mode IN VARCHAR2,
519 p_category_set_id IN NUMBER,
520 X_Return_Status OUT NOCOPY VARCHAR2,
521 X_Msg_Count OUT NOCOPY NUMBER,
522 X_Msg_Data OUT NOCOPY VARCHAR2
523 )
524 IS
525 CURSOR C_category_set_id_Exists (X_cat_set_Id NUMBER) IS
526 SELECT distinct 'X'
527 FROM ENI_PROD_DEN_HRCHY_PARENTS_V
528 WHERE category_set_id = X_cat_set_Id
529 AND (disable_date IS NULL OR disable_date > SYSDATE)
530 and PURCHASE_interest = 'Y';
531 l_variable VARCHAR2(1);
532 BEGIN
533 -- Initialize message list IF p_init_msg_list is set to TRUE.
534 IF FND_API.to_Boolean( p_init_msg_list )
535 THEN
536 FND_MSG_PUB.initialize;
537 END IF;
538 -- Initialize API return status to SUCCESS
539 x_return_status := FND_API.G_RET_STS_SUCCESS;
540 -- Validate CATEGORY ID
541 IF p_category_set_id is NOT NULL
542 and p_category_set_id <> FND_API.G_MISS_NUM
543 THEN
544 OPEN C_category_set_id_Exists (p_category_set_id);
545 FETCH C_category_set_id_Exists INTO l_variable;
546
547 IF (C_category_set_id_Exists%NOTFOUND)
548 THEN
549 AS_UTILITY_PVT.Set_Message(
550 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
551 p_msg_name => 'API_INVALID_ID',
552 p_token1 => 'COLUMN',
553 p_token1_value => 'CATEGORY_SET_ID',
554 p_token2 => 'VALUE',
555 p_token2_value => p_category_set_id );
556 x_return_status := FND_API.G_RET_STS_ERROR;
557 END IF;
558 CLOSE C_category_set_id_Exists;
559 END IF;
560
561 -- Standard call to get message count and IF count is 1, get message info.
562 FND_MSG_PUB.Count_And_Get
563 ( p_count => x_msg_count,
564 p_data => x_msg_data );
565 END validate_category_set_id;
566
567 --
568 -- Record Level Validation
569 --
570
571 PROCEDURE Validate_Intrst_Type_Sec_CODE (
572 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
573 P_Validation_mode IN VARCHAR2,
574 P_INTEREST_TYPE_ID IN NUMBER,
575 P_PRIMARY_INTEREST_CODE_ID IN NUMBER,
576 P_SECONDARY_INTEREST_CODE_ID IN NUMBER,
577 X_Return_Status OUT NOCOPY VARCHAR2,
578 X_Msg_Count OUT NOCOPY NUMBER,
579 X_Msg_Data OUT NOCOPY VARCHAR2
580 )
581 IS
582 BEGIN
583 -- Initialize message list IF p_init_msg_list is set to TRUE.
584 IF FND_API.to_Boolean( p_init_msg_list )
585 THEN
586 FND_MSG_PUB.initialize;
587 END IF;
588
589 -- Initialize API return status to SUCCESS
590 x_return_status := FND_API.G_RET_STS_SUCCESS;
591
592 -- Debug Message
593 -- IF (AS_DEBUG_LOW_ON) THEN AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
594 -- 'Validate sec. interest'); END IF;
595
596 -- IF secondary interest code is not null then interest type and primary
597 -- interest code must exist.
598 IF (p_secondary_interest_code_id is NOT NULL
599 and p_secondary_interest_code_id <> FND_API.G_MISS_NUM)
600 THEN
601 IF (p_interest_type_id is NOT NULL
602 and p_interest_type_id <> FND_API.G_MISS_NUM)
603 THEN
604 IF (p_primary_interest_code_id is NOT NULL
605 and p_primary_interest_code_id <> FND_API.G_MISS_NUM)
606 THEN
607 AS_UTILITY_PVT.Set_Message(
608 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
609 p_msg_name => 'API_INVALID_ID',
610 p_token1 => 'COLUMN',
611 p_token1_value => 'PRIMARY_INTEREST_CODE',
612 p_token2 => 'VALUE',
613 p_token2_value => p_PRIMARY_INTEREST_CODE_ID );
614 x_return_status := FND_API.G_RET_STS_ERROR;
615 END IF;
616 ELSE
617 AS_UTILITY_PVT.Set_Message(
618 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
619 p_msg_name => 'API_INVALID_ID',
620 p_token1 => 'COLUMN',
621 p_token1_value => 'INTEREST_TYPE',
622 p_token2 => 'VALUE',
623 p_token2_value => p_INTEREST_TYPE_ID );
624 END IF;
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 END IF;
627
628 -- Standard call to get message count and IF count is 1, get message info.
629 FND_MSG_PUB.Count_And_Get
630 ( p_count => x_msg_count,
631 p_data => x_msg_data );
632 END Validate_Intrst_Type_Sec_CODE;
633
634
635 PROCEDURE Validate_INVENT_INTRST(
636 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
637 P_Validation_mode IN VARCHAR2,
638 P_INTEREST_TYPE_ID IN NUMBER,
639 P_INVENTORY_ITEM_ID IN NUMBER,
640 X_Return_Status OUT NOCOPY VARCHAR2,
641 X_Msg_Count OUT NOCOPY NUMBER,
642 X_Msg_Data OUT NOCOPY VARCHAR2
643 )
644 IS
645 BEGIN
646 -- Initialize message list IF p_init_msg_list is set to TRUE.
647 IF FND_API.to_Boolean( p_init_msg_list )
648 THEN
649 FND_MSG_PUB.initialize;
650 END IF;
651
652 -- Initialize API return status to SUCCESS
653 x_return_status := FND_API.G_RET_STS_SUCCESS;
654
655 -- Debug Message
656 -- IF (AS_DEBUG_LOW_ON) THEN AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
657 -- 'Validate inventory interest'); END IF;
658
659 IF ((p_INTEREST_TYPE_ID is NULL or p_INTEREST_TYPE_ID=FND_API.G_MISS_NUM)
660 AND (p_INVENTORY_ITEM_ID is NULL
661 or p_INVENTORY_ITEM_ID=FND_API.G_MISS_NUM)
662 AND p_validation_mode=AS_UTILITY_PVT.G_CREATE)
663 OR
664 (p_INTEREST_TYPE_ID IS NULL
665 AND p_INVENTORY_ITEM_ID is NULL
666 AND p_validation_mode=AS_UTILITY_PVT.G_UPDATE)
667 THEN
668 AS_UTILITY_PVT.Set_Message(
669 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
670 p_msg_name => 'API_MISSING_ID',
671 p_token1 => 'COLUMN',
672 p_token1_value => 'INTEREST_TYPE_ID/INVENTORY_ITEM_ID');
673 x_return_status := FND_API.G_RET_STS_ERROR;
674 END IF;
675
676 -- Standard call to get message count and IF count is 1, get message info.
677 FND_MSG_PUB.Count_And_Get
678 ( p_count => x_msg_count,
679 p_data => x_msg_data );
680 END Validate_INVENT_INTRST;
681
682
683 --
684 -- Inter-record level validation
685 --
686
687 /*
688 PROCEDURE Validate_Budget_Amounts(
689 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
690 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
691 P_Validation_mode IN VARCHAR2,
692 P_SALES_LEAD_ID IN NUMBER,
693 X_Return_Status OUT NOCOPY VARCHAR2,
694 X_Msg_Count OUT NOCOPY NUMBER,
695 X_Msg_Data OUT NOCOPY VARCHAR2
696 )
697 IS
698 CURSOR C_Header_Amount (X_Sales_Lead_ID NUMBER) IS
699 SELECT budget_amount
700 FROM as_sales_leads
701 where sales_lead_id = X_Sales_Lead_ID;
702
703 CURSOR C_Lines_Amounts (X_Sales_Lead_ID NUMBER) IS
704 SELECT sum (budget_amount)
705 FROM as_sales_lead_lines
706 where sales_lead_id = X_Sales_Lead_ID;
707
708 l_header_amount NUMBER;
709 l_lines_amounts NUMBER;
710 BEGIN
711 -- Initialize message list IF p_init_msg_list is set to TRUE.
712 IF FND_API.to_Boolean( p_init_msg_list )
713 THEN
714 FND_MSG_PUB.initialize;
715 END IF;
716
717 -- Initialize API return status to SUCCESS
718 x_return_status := FND_API.G_RET_STS_SUCCESS;
719
720 -- Debug Message
721 -- IF (AS_DEBUG_LOW_ON) THEN AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
722 -- 'Validate budget amount'); END IF;
723
724 -- The summary of lines' budget_amount should be equal to header's
725 -- budget_amount
726 OPEN C_Header_Amount (P_SALES_LEAD_ID);
727 FETCH C_Header_Amount into l_header_amount;
728 CLOSE C_Header_Amount;
729
730 OPEN C_Lines_Amounts (P_SALES_LEAD_ID);
731 FETCH C_Lines_Amounts into l_lines_amounts;
732 CLOSE C_Lines_Amounts;
733
734 IF l_header_amount <> l_lines_amounts
735 THEN
736 AS_UTILITY_PVT.Set_Message(
737 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
738 p_msg_name => 'AS_BUDGET_AMOUNT_NOT_MATCH');
739 x_return_status := FND_API.G_RET_STS_ERROR;
740 END IF;
741
742 -- Standard call to get message count and IF count is 1, get message info.
743 FND_MSG_PUB.Count_And_Get
744 ( p_count => x_msg_count,
745 p_data => x_msg_data );
746 END Validate_Budget_Amounts;
747 */
748
749
750 -- validation procedures
751
752 PROCEDURE Validate_sales_lead_line(
753 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
754 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
755 P_Validation_mode IN VARCHAR2,
756 P_SALES_LEAD_LINE_Rec IN AS_SALES_LEADS_PUB.SALES_LEAD_LINE_Rec_Type,
757 X_Return_Status OUT NOCOPY VARCHAR2,
758 X_Msg_Count OUT NOCOPY NUMBER,
759 X_Msg_Data OUT NOCOPY VARCHAR2
760 )
761 IS
762 l_api_name CONSTANT VARCHAR2(30) := 'Validate_sales_lead_line';
763 l_Return_Status VARCHAR2(1);
764 l_item_property_rec AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE;
765 BEGIN
766 -- Initialize message list IF p_init_msg_list is set to TRUE.
767 IF FND_API.to_Boolean( p_init_msg_list )
768 THEN
769 FND_MSG_PUB.initialize;
770 END IF;
771
772 -- Debug Message
773 IF (AS_DEBUG_LOW_ON) THEN
774
775 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
776 'PVT: ' || l_api_name || ' Start');
777 END IF;
778
779 -- Initialize API return status to SUCCESS
780 x_return_status := FND_API.G_RET_STS_SUCCESS;
781 l_return_status := FND_API.G_RET_STS_SUCCESS;
782
783 IF ( P_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM)
784 THEN
785 -- Perform item level validation
786
787 -- ffang 092800: status_code in as_sales_lead_lines has been obselete
788 /*
789 IF (P_SALES_LEAD_LINE_Rec.STATUS_CODE IS NOT NULL
790 and P_SALES_LEAD_LINE_Rec.STATUS_CODE <> FND_API.G_MISS_CHAR)
791 THEN
792 Validate_STATUS_CODE(
793 p_init_msg_list => FND_API.G_FALSE,
794 p_validation_mode => p_validation_mode,
795 p_STATUS_CODE => P_SALES_LEAD_LINE_Rec.STATUS_CODE,
796 x_return_status => x_return_status,
797 x_msg_count => x_msg_count,
798 x_msg_data => x_msg_data);
799 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
800 l_return_status := FND_API.G_RET_STS_ERROR;
801 -- raise FND_API.G_EXC_ERROR;
802 END IF;
803 END IF;
804 */
805 -- end of ffang 092800
806
807 -- ffang 080201, add validate source_promotion_id and offer_id
808 AS_SALES_LEADS_PVT.Validate_SOURCE_PROMOTION_ID(
809 p_init_msg_list => FND_API.G_FALSE,
810 p_validation_mode => p_validation_mode,
811 p_SOURCE_PROMOTION_ID =>P_SALES_LEAD_LINE_Rec.SOURCE_PROMOTION_ID,
812 x_return_status => x_return_status,
813 x_msg_count => x_msg_count,
814 x_msg_data => x_msg_data);
815 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
816 l_return_status := FND_API.G_RET_STS_ERROR;
817 -- raise FND_API.G_EXC_ERROR;
818 END IF;
819
820 AS_SALES_LEADS_PVT.Validate_OFFER_ID(
821 p_init_msg_list => FND_API.G_FALSE,
822 p_validation_mode => p_validation_mode,
823 P_SOURCE_PROMOTION_ID =>P_SALES_LEAD_LINE_Rec.source_promotion_id,
824 p_OFFER_ID => P_SALES_LEAD_LINE_Rec.OFFER_ID,
825 x_item_property_rec => l_item_property_rec,
826 x_return_status => x_return_status,
827 x_msg_count => x_msg_count,
828 x_msg_data => x_msg_data);
829 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
830 l_return_status := FND_API.G_RET_STS_ERROR;
831 -- raise FND_API.G_EXC_ERROR;
832 END IF;
833 -- end ffang 080201
834
835 Validate_INV_ORG_ID(
836 p_init_msg_list => FND_API.G_FALSE,
837 p_validation_mode => p_validation_mode,
838 p_ORGANIZATION_ID => P_SALES_LEAD_LINE_Rec.ORGANIZATION_ID,
839 p_INVENTORY_ITEM_ID => P_SALES_LEAD_LINE_Rec.INVENTORY_ITEM_ID,
840 x_return_status => x_return_status,
841 x_msg_count => x_msg_count,
842 x_msg_data => x_msg_data);
843 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
844 l_return_status := FND_API.G_RET_STS_ERROR;
845 -- raise FND_API.G_EXC_ERROR;
846 END IF;
847
848 Validate_UOM_CODE(
849 p_init_msg_list => FND_API.G_FALSE,
850 p_validation_mode => p_validation_mode,
851 p_UOM_CODE => P_SALES_LEAD_LINE_Rec.UOM_CODE,
852 x_return_status => x_return_status,
853 x_msg_count => x_msg_count,
854 x_msg_data => x_msg_data);
855 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
856 l_return_status := FND_API.G_RET_STS_ERROR;
857 -- raise FND_API.G_EXC_ERROR;
858 END IF;
859 END IF;
860
861 IF ( P_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_RECORD)
862 THEN
863 -- Perform record level validation
864
865 -- ffang 092700: use AS_INTEREST_PVT.Validate_Interest_Fields to do
866 -- the record level interests validation
867 /*
868 Validate_Intrst_Type_Sec_CODE (
869 P_Init_Msg_List => FND_API.G_FALSE,
870 P_Validation_mode => p_validation_mode,
871 P_INTEREST_TYPE_ID => P_SALES_LEAD_LINE_Rec.INTEREST_TYPE_ID,
872 P_PRIMARY_INTEREST_CODE_ID
873 => P_SALES_LEAD_LINE_Rec.PRIMARY_INTEREST_CODE_ID,
874 P_SECONDARY_INTEREST_CODE_ID
875 => P_SALES_LEAD_LINE_Rec.SECONDARY_INTEREST_CODE_ID,
876 X_Return_Status => x_return_status,
877 X_Msg_Count => x_msg_count,
878 X_Msg_Data => x_msg_data);
879 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
880 l_return_status := FND_API.G_RET_STS_ERROR;
881 -- raise FND_API.G_EXC_ERROR;
882 END IF;
883 */
884
885
886 -- ckapoor 11.5.10 Rivendell product category changes
887 -- Comment out old validations. Only use category_id validation
888
889 /*
890
891 IF (P_SALES_LEAD_LINE_Rec.interest_type_id is NOT NULL) and
892 (P_SALES_LEAD_LINE_Rec.interest_type_id <> FND_API.G_MISS_NUM)
893 THEN
894 AS_INTEREST_PVT.Validate_Interest_Fields (
895 p_interest_type_id
896 => P_SALES_LEAD_LINE_Rec.interest_type_id,
897 p_primary_interest_code_id
898 => P_SALES_LEAD_LINE_Rec.primary_interest_code_id,
899 p_secondary_interest_code_id
900 => P_SALES_LEAD_LINE_Rec.secondary_interest_code_id,
901 p_interest_status_code => NULL,
902 p_return_status => x_return_status );
903 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
904 l_return_status := FND_API.G_RET_STS_ERROR;
905 -- raise FND_API.G_EXC_ERROR;
906 END IF;
907 END IF;
908 -- end ffang 092700
909
910 Validate_INVENT_INTRST(
911 P_Init_Msg_List => FND_API.G_FALSE,
912 P_Validation_mode => p_validation_mode,
913 P_INTEREST_TYPE_ID => P_SALES_LEAD_LINE_Rec.INTEREST_TYPE_ID,
914 P_INVENTORY_ITEM_ID => P_SALES_LEAD_LINE_Rec.INVENTORY_ITEM_ID,
915 X_Return_Status => x_return_status,
916 X_Msg_Count => x_msg_count,
917 X_Msg_Data => x_msg_data);
918 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
919 l_return_status := FND_API.G_RET_STS_ERROR;
920 -- raise FND_API.G_EXC_ERROR;
921 END IF;
922 END IF;
923
924 */
925
926
927 Validate_Category_ID (
928 P_Init_Msg_List => FND_API.G_FALSE,
929 P_Validation_mode => p_validation_mode,
930 P_Category_ID => P_SALES_LEAD_LINE_Rec.Category_ID,
931 X_Return_Status => x_return_status,
932 X_Msg_Count => x_msg_count,
933 X_Msg_Data => x_msg_data);
934 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
935 l_return_status := FND_API.G_RET_STS_ERROR;
936 -- raise FND_API.G_EXC_ERROR;
937 END IF;
938
939 Validate_Category_set_ID (
940 P_Init_Msg_List => FND_API.G_FALSE,
941 P_Validation_mode => p_validation_mode,
942 P_Category_set_ID => P_SALES_LEAD_LINE_Rec.Category_set_ID,
943 X_Return_Status => x_return_status,
944 X_Msg_Count => x_msg_count,
945 X_Msg_Data => x_msg_data);
946 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
947 l_return_status := FND_API.G_RET_STS_ERROR;
948 -- raise FND_API.G_EXC_ERROR;
949 END IF;
950
951 END IF;
952
953
954 -- FFANG 112700 For bug 1512008, instead of erroring out once a invalid
955 -- column was found, raise the exception after all validation procedures
956 -- have been gone through.
957 x_return_status := l_return_status;
958 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
959 raise FND_API.G_EXC_ERROR;
960 END IF;
961 -- END FFANG 112700
962
963 -- Debug Message
964 IF (AS_DEBUG_LOW_ON) THEN
965
966 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
967 'PVT: ' || l_api_name || ' End');
968 END IF;
969 END Validate_sales_lead_line;
970
971
972
973 -- ************************
974 -- Sales Lead Line APIs
975 -- ************************
976
977 -- ffang 012501, note: p_sales_lead_id and p_sales_lead_line_rec.sales_lead_id
978 -- may cause confuse.
979
980 PROCEDURE Create_sales_lead_lines(
981 P_Api_Version_Number IN NUMBER,
982 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
983 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
984 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
985 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
986 P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
987 P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
988 P_Identity_Salesforce_Id IN NUMBER := FND_API.G_MISS_NUM,
989 P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
990 := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
991 P_SALES_LEAD_LINE_Tbl IN AS_SALES_LEADS_PUB.SALES_LEAD_LINE_Tbl_type
992 := AS_SALES_LEADS_PUB.G_MISS_SALES_LEAD_LINE_Tbl,
993 P_SALES_LEAD_ID IN NUMBER,
994 X_SALES_LEAD_LINE_OUT_Tbl OUT NOCOPY AS_SALES_LEADS_PUB.SALES_LEAD_LINE_OUT_Tbl_Type,
995 X_Return_Status OUT NOCOPY VARCHAR2,
996 X_Msg_Count OUT NOCOPY NUMBER,
997 X_Msg_Data OUT NOCOPY VARCHAR2
998 )
999
1000 IS
1001 l_api_name CONSTANT VARCHAR2(30) := 'Create_sales_lead_lines';
1002 l_api_version_number CONSTANT NUMBER := 2.0;
1003 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
1004 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
1005 l_SALES_LEAD_LINE_rec AS_SALES_LEADS_PUB.SALES_LEAD_LINE_rec_type;
1006 l_sales_lead_line_id NUMBER;
1007 l_customer_id NUMBER;
1008 l_address_id NUMBER;
1009 l_update_access_flag VARCHAR2(1);
1010 l_member_role VARCHAR2(5);
1011 l_member_access VARCHAR2(5);
1012 l_lines_amount NUMBER := 0;
1013 l_org_id NUMBER := 0;
1014 l_category_set_id NUMBER;
1015 l_category_id NUMBER;
1016
1017 -- ffang 090801, for bug 1978014
1018 -- Default source_promotion_id as header's
1019 CURSOR C_Get_Header_Campaign (c_sales_lead_id NUMBER) IS
1020 SELECT source_promotion_id, offer_id
1021 FROM as_sales_leads
1022 WHERE sales_lead_id = c_sales_lead_id;
1023
1024 CURSOR C_Get_Category_set_ID (X_cat_Id NUMBER) IS
1025 SELECT category_set_id
1026 FROM ENI_PROD_DEN_HRCHY_PARENTS_V
1027 WHERE category_id = X_cat_Id
1028 AND (disable_date IS NULL OR disable_date > SYSDATE)
1029 and PURCHASE_interest = 'Y';
1030
1031 CURSOR C_Get_Category_INFO (X_prod_Id NUMBER, X_Org_Id NUMBER) IS
1032 SELECT P.CATEGORY_ID CAT_ID, P.CATEGORY_SET_ID CAT_SET_ID
1033 FROM MTL_SYSTEM_ITEMS_B_KFV B, MTL_ITEM_CATEGORIES MIC,
1034 ENI_PROD_DEN_HRCHY_PARENTS_V P
1035 WHERE (MIC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
1036 AND MIC.ORGANIZATION_ID = B.ORGANIZATION_ID
1037 AND B.ORGANIZATION_ID = X_Org_Id
1038 and B.INVENTORY_ITEM_ID = X_prod_Id
1039 AND P.CATEGORY_ID = MIC.CATEGORY_ID
1040 AND P.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID
1041 AND P.LANGUAGE = userenv('LANG')
1042 AND P.PURCHASE_INTEREST = 'Y'
1043 AND (P.DISABLE_DATE is null OR P.DISABLE_DATE > SYSDATE)) ;
1044
1045
1046
1047 BEGIN
1048 -- Standard Start of API savepoint
1049 SAVEPOINT CREATE_SALES_LEAD_LINES_PVT;
1050
1051 -- Initialize API return status to SUCCESS
1052 x_return_status := FND_API.G_RET_STS_SUCCESS;
1053
1054
1055 -- Standard call to check for call compatibility.
1056 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1057 p_api_version_number,
1058 l_api_name,
1059 G_PKG_NAME)
1060 THEN
1061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1062 END IF;
1063
1064
1065 -- Initialize message list IF p_init_msg_list is set to TRUE.
1066 IF FND_API.to_Boolean( p_init_msg_list )
1067 THEN
1068 FND_MSG_PUB.initialize;
1069 END IF;
1070
1071 -- Debug Message
1072 IF (AS_DEBUG_LOW_ON) THEN
1073
1074 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1075 'PVT: ' || l_api_name || ' Start');
1076 END IF;
1077
1078 --
1079 -- API body
1080 --
1081 -- ******************************************************************
1082 -- Validate Environment
1083 -- ******************************************************************
1084
1085 IF FND_GLOBAL.User_Id IS NULL
1086 THEN
1087 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1088 THEN
1089 AS_UTILITY_PVT.Set_Message(
1090 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1091 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
1092 p_token1 => 'PROFILE',
1093 p_token1_value => 'USER_ID');
1094
1095 END IF;
1096 RAISE FND_API.G_EXC_ERROR;
1097 END IF;
1098
1099 IF (p_validation_level = fnd_api.g_valid_level_full)
1100 THEN
1101 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1102 p_api_version_number => 2.0
1103 ,p_init_msg_list => p_init_msg_list
1104 ,p_salesforce_id => P_Identity_Salesforce_Id
1105 ,p_admin_group_id => p_admin_group_id
1106 ,x_return_status => x_return_status
1107 ,x_msg_count => x_msg_count
1108 ,x_msg_data => x_msg_data
1109 ,x_sales_member_rec => l_identity_sales_member_rec);
1110 END IF;
1111
1112 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1113 RAISE FND_API.G_EXC_ERROR;
1114 END IF;
1115
1116 -- Debug message
1117 -- IF (AS_DEBUG_LOW_ON) THEN AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1118 -- 'Comparing sales_lead_line_id'); END IF;
1119
1120 -- IF p_sales_lead_line_rec.sales_lead_id <> p_sales_lead_id THEN
1121 -- RAISE FND_API.G_EXC_ERROR;
1122 -- END IF;
1123
1124 l_lines_amount := 0;
1125
1126 FOR l_curr_row IN 1..p_sales_lead_line_tbl.count LOOP
1127 x_sales_lead_line_out_tbl(l_curr_row).return_status :=
1128 FND_API.G_RET_STS_SUCCESS;
1129 -- Progress Message
1130 --
1131 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
1132 THEN
1133 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
1134 FND_MESSAGE.Set_Token ('ROW', 'SALES_LEAD_LINE', TRUE);
1135 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
1136 FND_MSG_PUB.Add;
1137 END IF;
1138
1139 l_sales_lead_line_rec := p_sales_lead_line_tbl(l_curr_row);
1140 l_sales_lead_line_rec.sales_lead_id := p_sales_lead_id; -- *****
1141
1142 IF (l_sales_lead_line_rec.inventory_item_id IS NOT NULL AND
1143 l_sales_lead_line_rec.inventory_item_id <> FND_API.G_MISS_NUM AND
1144 (l_sales_lead_line_rec.organization_id IS NULL OR
1145 l_sales_lead_line_rec.organization_id = FND_API.G_MISS_NUM ))
1146 THEN
1147 -- ffang 100301, use oe_profile.value function call instead of
1148 -- profile OE_ORGANIZATION_ID
1149 l_org_id := FND_PROFILE.Value('ORG_ID');
1150 IF (AS_DEBUG_LOW_ON) THEN
1151
1152 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1153 'org_id: ' || l_org_id);
1154 END IF;
1155
1156 l_sales_lead_line_rec.organization_id :=
1157 oe_profile.value('OE_ORGANIZATION_ID', l_org_id);
1158 --FND_PROFILE.Value('OE_ORGANIZATION_ID');
1159 IF (AS_DEBUG_LOW_ON) THEN
1160
1161 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1162 'organization_id: ' ||
1163 l_sales_lead_line_rec.organization_id);
1164 END IF;
1165
1166 END IF;
1167 IF (AS_DEBUG_LOW_ON) THEN
1168
1169 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1170 'inventory_item_id: '||l_sales_lead_line_rec.inventory_item_id);
1171 END IF;
1172 IF (AS_DEBUG_LOW_ON) THEN
1173
1174 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1175 'organization_id: '||l_sales_lead_line_rec.organization_id);
1176 END IF;
1177
1178 -- ffang 090801, for bug 1978014
1179 -- Default source_promotion_id as header's
1180 IF (l_sales_lead_line_rec.source_promotion_id IS NULL OR
1181 l_sales_lead_line_rec.source_promotion_id = FND_API.G_MISS_NUM)
1182 THEN
1183 OPEN C_Get_Header_Campaign (p_sales_lead_id);
1184 FETCH C_Get_Header_Campaign into
1185 l_sales_lead_line_rec.source_promotion_id,l_sales_lead_line_rec.offer_id;
1186 IF C_Get_Header_Campaign%NOTFOUND THEN
1187 AS_UTILITY_PVT.Set_Message(
1188 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1189 p_msg_name => 'API_INVALID_ID',
1190 p_token1 => 'SALES_LEAD_ID',
1191 p_token1_value => p_Sales_Lead_Id);
1192
1193 x_return_status := FND_API.G_RET_STS_ERROR;
1194 ELSE
1195 IF (l_sales_lead_line_rec.source_promotion_id IS NULL OR
1196 l_sales_lead_line_rec.source_promotion_id =
1197 FND_API.G_MISS_NUM)
1198 THEN
1199 IF (AS_DEBUG_LOW_ON) THEN
1200
1201 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW
1202 , 'No campaign in header');
1203 END IF;
1204 ELSE
1205 IF (AS_DEBUG_LOW_ON) THEN
1206
1207 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW
1208 , 'header.source_promotion_id:'
1209 || l_sales_lead_line_rec.source_promotion_id);
1210 END IF;
1211 END IF;
1212 END IF;
1213 CLOSE C_Get_Header_Campaign;
1214 END IF;
1215 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1216 RAISE FND_API.G_EXC_ERROR;
1217 END IF;
1218 -- end ffang 090801
1219
1220 -- Debug message
1221 IF (AS_DEBUG_LOW_ON) THEN
1222
1223 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1224 'Calling Validate_line');
1225 END IF;
1226
1227 -- Invoke validation procedures
1228 Validate_sales_lead_line(
1229 p_init_msg_list => FND_API.G_FALSE,
1230 p_validation_level => p_validation_level,
1231 p_validation_mode => AS_UTILITY_PVT.G_CREATE,
1232 P_SALES_LEAD_LINE_Rec => l_SALES_LEAD_LINE_rec,
1233 x_return_status => x_return_status,
1234 x_msg_count => x_msg_count,
1235 x_msg_data => x_msg_data);
1236
1237 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1238 x_sales_lead_line_out_tbl(l_curr_row).return_status :=
1239 x_return_status;
1240 RAISE FND_API.G_EXC_ERROR;
1241 END IF;
1242
1243 --- retrieve the category_id based on the Inventory_item_id and the organization_id
1244
1245 IF(l_sales_lead_line_rec.INVENTORY_ITEM_ID IS NOT NULL AND
1246 l_sales_lead_line_rec.INVENTORY_ITEM_ID <> FND_API.G_MISS_NUM AND
1247 l_sales_lead_line_rec.ORGANIZATION_ID IS NOT NULL AND
1248 l_sales_lead_line_rec.ORGANIZATION_ID <> FND_API.G_MISS_NUM AND
1249 (l_sales_lead_line_rec.category_id IS NULL OR
1250 l_sales_lead_line_rec.category_id = FND_API.G_MISS_NUM)
1251 )
1252 THEN
1253 OPEN C_Get_Category_INFO (l_sales_lead_line_rec.INVENTORY_ITEM_ID, l_sales_lead_line_rec.ORGANIZATION_ID);
1254 FETCH C_Get_Category_INFO into l_sales_lead_line_rec.category_id, l_sales_lead_line_rec.category_set_id;
1255
1256 IF C_Get_Category_INFO%NOTFOUND
1257 THEN
1258 AS_UTILITY_PVT.Set_Message(
1259 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1260 p_msg_name => 'API_INVALID_ID',
1261 p_token1 => 'COLUMN',
1262 p_token1_value => 'INVENTORY_ITEM',
1263 p_token2 => 'VALUE',
1264 p_token2_value => l_sales_lead_line_rec.INVENTORY_ITEM_ID );
1265 RAISE FND_API.G_EXC_ERROR;
1266 END IF;
1267 CLOSE C_Get_Category_INFO;
1268
1269 END IF;
1270
1271 --- END Check for the Category_set_ID
1272
1273 --- Check for the Category_set_ID
1274
1275 IF(l_sales_lead_line_rec.category_set_id IS NOT NULL AND
1276 l_sales_lead_line_rec.category_set_id <> FND_API.G_MISS_NUM)
1277 THEN
1278 l_category_set_id := l_sales_lead_line_rec.category_set_id ;
1279 ELSE
1280 OPEN C_Get_Category_set_ID (l_sales_lead_line_rec.category_id);
1281 FETCH C_Get_Category_set_ID into l_category_set_id;
1282 CLOSE C_Get_Category_set_ID;
1283 END IF;
1284
1285 --- END Check for the Category_set_ID
1286
1287
1288 IF (AS_DEBUG_LOW_ON) THEN
1289
1290 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1291 'Cateogry_set_id:' || l_category_set_id);
1292 END IF;
1293
1294
1295
1296 IF(P_Check_Access_Flag = 'Y') THEN
1297 -- Call Get_Access_Profiles to get access_profile_rec
1298 IF (AS_DEBUG_LOW_ON) THEN
1299
1300 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1301 'Calling Get_Access_Profiles');
1302 END IF;
1303
1304 AS_SALES_LEADS_PUB.Get_Access_Profiles(
1305 p_profile_tbl => p_sales_lead_profile_tbl,
1306 x_access_profile_rec => l_access_profile_rec);
1307
1308 IF (AS_DEBUG_LOW_ON) THEN
1309
1310
1311
1312 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1313 'Calling Has_updateLeadAccess');
1314
1315 END IF;
1316
1317 AS_ACCESS_PUB.Has_updateLeadAccess(
1318 p_api_version_number => 2.0
1319 ,p_init_msg_list => FND_API.G_FALSE
1320 ,p_validation_level => p_validation_level
1321 ,p_access_profile_rec => l_access_profile_rec
1322 ,p_admin_flag => p_admin_flag
1323 ,p_admin_group_id => p_admin_group_id
1324 ,p_person_id =>
1325 l_identity_sales_member_rec.employee_person_id
1326 ,p_sales_lead_id => l_sales_lead_line_rec.sales_lead_id
1327 ,p_check_access_flag => p_check_access_flag -- should be 'Y'
1328 ,p_identity_salesforce_id => p_identity_salesforce_id
1329 ,p_partner_cont_party_id => NULL
1330 ,x_return_status => x_return_status
1331 ,x_msg_count => x_msg_count
1332 ,x_msg_data => x_msg_data
1333 ,x_update_access_flag => l_update_access_flag);
1334
1335 IF l_update_access_flag <> 'Y' THEN
1336 IF (AS_DEBUG_ERROR_ON) THEN
1337
1338 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
1339 'API_NO_CREATE_PRIVILEGE');
1340 END IF;
1341 RAISE FND_API.G_EXC_ERROR;
1342 END IF;
1343
1344 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1345 RAISE FND_API.G_EXC_ERROR;
1346 END IF;
1347
1348 END IF;
1349
1350 -- Debug Message
1351 IF (AS_DEBUG_LOW_ON) THEN
1352
1353 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1354 'Calling Line_Insert_Row');
1355 END IF;
1356
1357 l_sales_lead_line_id := l_sales_lead_line_rec.sales_lead_line_id;
1358
1359 -- Invoke table handler(Sales_Lead_Line_Insert_Row)
1360 AS_SALES_LEAD_LINES_PKG.Sales_Lead_Line_Insert_Row(
1361 px_SALES_LEAD_LINE_ID => l_sales_lead_line_id,
1362 p_LAST_UPDATE_DATE => SYSDATE,
1363 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1364 p_CREATION_DATE => SYSDATE,
1365 p_CREATED_BY => FND_GLOBAL.USER_ID,
1366 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1367 p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
1368 p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
1369 p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
1370 p_PROGRAM_UPDATE_DATE => SYSDATE,
1371 p_SALES_LEAD_ID => l_SALES_LEAD_LINE_rec.SALES_LEAD_ID,
1372 p_STATUS_CODE => l_SALES_LEAD_LINE_rec.STATUS_CODE,
1373 /*p_INTEREST_TYPE_ID => l_SALES_LEAD_LINE_rec.INTEREST_TYPE_ID,
1374
1375 p_PRIMARY_INTEREST_CODE_ID
1376 => l_SALES_LEAD_LINE_rec.PRIMARY_INTEREST_CODE_ID,
1377 p_SECONDARY_INTEREST_CODE_ID
1378 => l_SALES_LEAD_LINE_rec.SECONDARY_INTEREST_CODE_ID,
1379
1380 */
1381
1382 p_CATEGORY_ID
1383 => l_SALES_LEAD_LINE_rec.CATEGORY_ID,
1384
1385 p_CATEGORY_SET_ID
1386 => l_category_set_id,
1387
1388 p_INVENTORY_ITEM_ID => l_SALES_LEAD_LINE_rec.INVENTORY_ITEM_ID,
1389 p_ORGANIZATION_ID => l_SALES_LEAD_LINE_rec.ORGANIZATION_ID,
1390 p_UOM_CODE => l_SALES_LEAD_LINE_rec.UOM_CODE,
1391 p_QUANTITY => l_SALES_LEAD_LINE_rec.QUANTITY,
1392 p_BUDGET_AMOUNT => l_SALES_LEAD_LINE_rec.BUDGET_AMOUNT,
1393 p_SOURCE_PROMOTION_ID => l_SALES_LEAD_LINE_rec.SOURCE_PROMOTION_ID,
1394 p_ATTRIBUTE_CATEGORY => l_SALES_LEAD_LINE_rec.ATTRIBUTE_CATEGORY,
1395 p_ATTRIBUTE1 => l_SALES_LEAD_LINE_rec.ATTRIBUTE1,
1396 p_ATTRIBUTE2 => l_SALES_LEAD_LINE_rec.ATTRIBUTE2,
1397 p_ATTRIBUTE3 => l_SALES_LEAD_LINE_rec.ATTRIBUTE3,
1398 p_ATTRIBUTE4 => l_SALES_LEAD_LINE_rec.ATTRIBUTE4,
1399 p_ATTRIBUTE5 => l_SALES_LEAD_LINE_rec.ATTRIBUTE5,
1400 p_ATTRIBUTE6 => l_SALES_LEAD_LINE_rec.ATTRIBUTE6,
1401 p_ATTRIBUTE7 => l_SALES_LEAD_LINE_rec.ATTRIBUTE7,
1402 p_ATTRIBUTE8 => l_SALES_LEAD_LINE_rec.ATTRIBUTE8,
1403 p_ATTRIBUTE9 => l_SALES_LEAD_LINE_rec.ATTRIBUTE9,
1404 p_ATTRIBUTE10 => l_SALES_LEAD_LINE_rec.ATTRIBUTE10,
1405 p_ATTRIBUTE11 => l_SALES_LEAD_LINE_rec.ATTRIBUTE11,
1406 p_ATTRIBUTE12 => l_SALES_LEAD_LINE_rec.ATTRIBUTE12,
1407 p_ATTRIBUTE13 => l_SALES_LEAD_LINE_rec.ATTRIBUTE13,
1408 p_ATTRIBUTE14 => l_SALES_LEAD_LINE_rec.ATTRIBUTE14,
1409 p_ATTRIBUTE15 => l_SALES_LEAD_LINE_rec.ATTRIBUTE15,
1410 p_OFFER_ID => l_SALES_LEAD_LINE_rec.OFFER_ID
1411 -- p_SECURITY_GROUP_ID => l_SALES_LEAD_LINE_rec.SECURITY_GROUP_ID
1412 );
1413
1414 x_sales_lead_line_out_tbl(l_curr_row).sales_lead_line_id :=
1415 l_sales_lead_line_id;
1416 x_sales_lead_line_out_tbl(l_curr_row).return_status := x_return_status;
1417
1418 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1419 RAISE FND_API.G_EXC_ERROR;
1420 END IF;
1421
1422 /*
1423 IF l_SALES_LEAD_LINE_rec.BUDGET_AMOUNT IS NOT NULL AND
1424 l_SALES_LEAD_LINE_rec.BUDGET_AMOUNT <> FND_API.G_MISS_NUM
1425 THEN
1426 l_lines_amount:=l_lines_amount + l_SALES_LEAD_LINE_rec.BUDGET_AMOUNT;
1427 END IF;
1428 */
1429
1430 END LOOP;
1431
1432 /*
1433 -- Debug Message
1434 IF (AS_DEBUG_LOW_ON) THEN
1435
1436 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1437 'Updating Header Budget Amount');
1438 END IF;
1439
1440 UPDATE as_sales_leads
1441 SET budget_amount = nvl(budget_amount, 0) + l_lines_amount
1442 WHERE sales_lead_id = p_SALES_LEAD_ID;
1443
1444 -- Debug Message
1445 IF (AS_DEBUG_LOW_ON) THEN
1446
1447 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1448 'Validate BUDGET_AMOUNT');
1449 END IF;
1450
1451 Validate_BUDGET_AMOUNTS(
1452 p_init_msg_list => FND_API.G_FALSE,
1453 p_validation_mode => AS_UTILITY_PVT.G_CREATE,
1454 p_SALES_LEAD_ID => P_SALES_LEAD_ID,
1455 x_return_status => x_return_status,
1456 x_msg_count => x_msg_count,
1457 x_msg_data => x_msg_data);
1458
1459 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1460 raise FND_API.G_EXC_ERROR;
1461 END IF;
1462 */
1463 -- Back update total_amount in lead header header
1464 Backupdate_Header(
1465 p_sales_lead_id => p_sales_lead_id,
1466 x_return_status => x_return_status);
1467
1468 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1469 IF (AS_DEBUG_LOW_ON) THEN
1470
1471 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1472 'Private API: Create_lead_line: Backupdate_header fail' );
1473 END IF;
1474 raise FND_API.G_EXC_ERROR;
1475 END IF;
1476
1477
1478 --
1479 -- END of API body
1480 --
1481
1482 -- Standard check for p_commit
1483 IF FND_API.to_Boolean( p_commit )
1484 THEN
1485 COMMIT WORK;
1486 END IF;
1487
1488 -- Debug Message
1489 IF (AS_DEBUG_LOW_ON) THEN
1490
1491 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1492 'PVT: ' || l_api_name || ' End');
1493 END IF;
1494
1495 -- Standard call to get message count and IF count is 1, get message info.
1496 FND_MSG_PUB.Count_And_Get
1497 ( p_count => x_msg_count,
1498 p_data => x_msg_data
1499 );
1500
1501 EXCEPTION
1502 WHEN FND_API.G_EXC_ERROR THEN
1503 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1504 P_API_NAME => L_API_NAME
1505 ,P_PKG_NAME => G_PKG_NAME
1506 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1507 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1508 ,X_MSG_COUNT => X_MSG_COUNT
1509 ,X_MSG_DATA => X_MSG_DATA
1510 ,X_RETURN_STATUS => X_RETURN_STATUS);
1511
1512 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1513 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1514 P_API_NAME => L_API_NAME
1515 ,P_PKG_NAME => G_PKG_NAME
1516 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1517 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1518 ,X_MSG_COUNT => X_MSG_COUNT
1519 ,X_MSG_DATA => X_MSG_DATA
1520 ,X_RETURN_STATUS => X_RETURN_STATUS);
1521
1522 WHEN OTHERS THEN
1523 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1524 P_API_NAME => L_API_NAME
1525 ,P_PKG_NAME => G_PKG_NAME
1526 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1527 ,P_SQLCODE => SQLCODE
1528 ,P_SQLERRM => SQLERRM
1529 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1530 ,X_MSG_COUNT => X_MSG_COUNT
1531 ,X_MSG_DATA => X_MSG_DATA
1532 ,X_RETURN_STATUS => X_RETURN_STATUS);
1533 END Create_sales_lead_lines;
1534
1535
1536 PROCEDURE Update_sales_lead_lines(
1537 P_Api_Version_Number IN NUMBER,
1538 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1539 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1540 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1541 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1542 P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1543 P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
1544 P_Identity_Salesforce_Id IN NUMBER := FND_API.G_MISS_NUM,
1545 P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
1546 := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
1547 P_SALES_LEAD_LINE_Tbl IN AS_SALES_LEADS_PUB.SALES_LEAD_LINE_Tbl_type,
1548 X_SALES_LEAD_LINE_OUT_Tbl OUT NOCOPY AS_SALES_LEADS_PUB.SALES_LEAD_LINE_OUT_Tbl_Type,
1549 X_Return_Status OUT NOCOPY VARCHAR2,
1550 X_Msg_Count OUT NOCOPY NUMBER,
1551 X_Msg_Data OUT NOCOPY VARCHAR2
1552 )
1553 IS
1554 Cursor C_Get_sales_lead_line(c_SALES_LEAD_LINE_ID Number) IS
1555 Select LAST_UPDATE_DATE,
1556 BUDGET_AMOUNT
1557 From AS_SALES_LEAD_LINES
1558 WHERE sales_lead_line_id = c_sales_lead_line_id
1559 For Update NOWAIT;
1560
1561 l_api_name CONSTANT VARCHAR2(30) := 'Update_sales_lead_lines';
1562 l_api_version_number CONSTANT NUMBER := 2.0;
1563 -- Local Variables
1564 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
1565 l_tar_SALES_LEAD_LINE_rec AS_SALES_LEADS_PUB.SALES_LEAD_LINE_Rec_Type;
1566 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
1567 l_Sales_Lead_Id NUMBER;
1568 l_Sales_Lead_Line_Id NUMBER;
1569 l_last_update_date DATE;
1570 l_update_amounts NUMBER := 0;
1571 l_budget_amount NUMBER;
1572 l_customer_id NUMBER;
1573 l_address_id NUMBER;
1574 l_update_access_flag VARCHAR2(1);
1575 l_member_role VARCHAR2(5);
1576 l_member_access VARCHAR2(5);
1577
1578 BEGIN
1579 -- Standard Start of API savepoint
1580 SAVEPOINT UPDATE_SALES_LEAD_LINES_PVT;
1581
1582 -- Standard call to check for call compatibility.
1583 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1584 p_api_version_number,
1585 l_api_name,
1586 G_PKG_NAME)
1587 THEN
1588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1589 END IF;
1590
1591 -- Initialize message list IF p_init_msg_list is set to TRUE.
1592 IF FND_API.to_Boolean( p_init_msg_list )
1593 THEN
1594 FND_MSG_PUB.initialize;
1595 END IF;
1596
1597 -- Debug Message
1598 IF (AS_DEBUG_LOW_ON) THEN
1599
1600 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1601 'PVT: ' || l_api_name || ' Start');
1602 END IF;
1603
1604 -- Initialize API return status to SUCCESS
1605 x_return_status := FND_API.G_RET_STS_SUCCESS;
1606
1607 --
1608 -- Api body
1609 --
1610
1611 -- ******************************************************************
1612 -- Validate Environment
1613 -- ******************************************************************
1614 IF FND_GLOBAL.User_Id IS NULL
1615 THEN
1616 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1617 THEN
1618 AS_UTILITY_PVT.Set_Message(
1619 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1620 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
1621 p_token1 => 'PROFILE',
1622 p_token1_value => 'USER_ID');
1623
1624 END IF;
1625 RAISE FND_API.G_EXC_ERROR;
1626 END IF;
1627
1628 IF (p_validation_level = fnd_api.g_valid_level_full)
1629 THEN
1630 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1631 p_api_version_number => 2.0
1632 ,p_init_msg_list => p_init_msg_list
1633 ,p_salesforce_id => P_Identity_Salesforce_Id
1634 ,p_admin_group_id => p_admin_group_id
1635 ,x_return_status => x_return_status
1636 ,x_msg_count => x_msg_count
1637 ,x_msg_data => x_msg_data
1638 ,x_sales_member_rec => l_identity_sales_member_rec);
1639 END IF;
1640
1641 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1642 RAISE FND_API.G_EXC_ERROR;
1643 END IF;
1644
1645 l_update_amounts := 0;
1646
1647 FOR l_curr_row IN 1..p_sales_lead_line_tbl.count LOOP
1648
1649 x_sales_lead_line_out_tbl(l_curr_row).return_status :=
1650 FND_API.G_RET_STS_SUCCESS;
1651
1652 -- Progress Message
1653 --
1654 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
1655 THEN
1656 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
1657 FND_MESSAGE.Set_Token ('ROW', 'SALES_LEAD_LINE', TRUE);
1658 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
1659 FND_MSG_PUB.Add;
1660 END IF;
1661
1662 l_tar_sales_lead_line_rec := p_sales_lead_line_tbl(l_curr_row);
1663
1664 -- Debug Message
1665 IF (AS_DEBUG_LOW_ON) THEN
1666
1667 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1668 'Open C_Get_sales_lead_line');
1669 END IF;
1670
1671 l_Sales_Lead_Id := l_tar_sales_lead_line_rec.SALES_LEAD_ID;
1672 l_Sales_Lead_Line_Id := l_tar_sales_lead_line_rec.SALES_LEAD_LINE_ID;
1673
1674 Open C_Get_sales_lead_line( l_SALES_LEAD_LINE_ID);
1675 Fetch C_Get_sales_lead_line into l_last_update_date, l_budget_amount;
1676
1677 IF ( C_Get_sales_lead_line%NOTFOUND) THEN
1678 Close C_Get_sales_lead_line;
1679 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1680 THEN
1681 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
1682 FND_MESSAGE.Set_Token ('INFO', 'SALES_LEAD_LINE', FALSE);
1683 FND_MSG_PUB.Add;
1684 END IF;
1685 raise FND_API.G_EXC_ERROR;
1686 END IF;
1687
1688 -- Debug Message
1689 IF (AS_DEBUG_LOW_ON) THEN
1690
1691 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1692 'Close C_Get_sales_lead_line');
1693 END IF;
1694 Close C_Get_sales_lead_line;
1695
1696 -- Check Whether record has been changed by someone else
1697 IF (l_tar_SALES_LEAD_LINE_rec.last_update_date is NULL or
1698 l_tar_SALES_LEAD_LINE_rec.last_update_date = FND_API.G_MISS_Date)
1699 THEN
1700 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1701 THEN
1702 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1703 FND_MESSAGE.Set_Token('COLUMN', 'LAST_UPDATE_DATE', FALSE);
1704 FND_MSG_PUB.ADD;
1705 END IF;
1706 raise FND_API.G_EXC_ERROR;
1707 END IF;
1708
1709 IF (l_tar_SALES_LEAD_LINE_rec.last_update_date <> l_last_update_date)
1710 THEN
1711 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1712 THEN
1713 FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
1714 FND_MESSAGE.Set_Token('INFO', 'SALES_LEAD_LINE', FALSE);
1715 FND_MSG_PUB.ADD;
1716 END IF;
1717 raise FND_API.G_EXC_ERROR;
1718 END IF;
1719
1720 -- Invoke validation procedures
1721 -- Debug message
1722 IF (AS_DEBUG_LOW_ON) THEN
1723
1724 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1725 'Calling Validate_line');
1726 END IF;
1727
1728 -- Invoke validation procedures
1729 Validate_sales_lead_line(
1730 p_init_msg_list => FND_API.G_FALSE,
1731 p_validation_level => p_validation_level,
1732 p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
1733 P_SALES_LEAD_LINE_Rec => l_tar_SALES_LEAD_LINE_rec,
1734 x_return_status => x_return_status,
1735 x_msg_count => x_msg_count,
1736 x_msg_data => x_msg_data);
1737
1738 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1739 x_sales_lead_line_out_tbl(l_curr_row).return_status:=x_return_status;
1740 RAISE FND_API.G_EXC_ERROR;
1741 END IF;
1742
1743 IF(P_Check_Access_Flag = 'Y') THEN
1744 -- Call Get_Access_Profiles to get access_profile_rec
1745 IF (AS_DEBUG_LOW_ON) THEN
1746
1747 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1748 'Calling Get_Access_Profiles');
1749 END IF;
1750
1751 AS_SALES_LEADS_PUB.Get_Access_Profiles(
1752 p_profile_tbl => p_sales_lead_profile_tbl,
1753 x_access_profile_rec => l_access_profile_rec);
1754
1755 IF (AS_DEBUG_LOW_ON) THEN
1756
1757
1758
1759 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1760 'Calling Has_updateLeadAccess');
1761
1762 END IF;
1763
1764 AS_ACCESS_PUB.Has_updateLeadAccess(
1765 p_api_version_number => 2.0
1766 ,p_init_msg_list => FND_API.G_FALSE
1767 ,p_validation_level => p_validation_level
1768 ,p_access_profile_rec => l_access_profile_rec
1769 ,p_admin_flag => p_admin_flag
1770 ,p_admin_group_id => p_admin_group_id
1771 ,p_person_id => l_identity_sales_member_rec.employee_person_id
1772 ,p_sales_lead_id => l_tar_sales_lead_line_rec.sales_lead_id
1773 ,p_check_access_flag => p_check_access_flag -- should be 'Y'
1774 ,p_identity_salesforce_id => p_identity_salesforce_id
1775 ,p_partner_cont_party_id => NULL
1776 ,x_return_status => x_return_status
1777 ,x_msg_count => x_msg_count
1778 ,x_msg_data => x_msg_data
1779 ,x_update_access_flag => l_update_access_flag);
1780
1781 IF l_update_access_flag <> 'Y' THEN
1782 IF (AS_DEBUG_ERROR_ON) THEN
1783
1784 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
1785 'API_NO_CREATE_PRIVILEGE');
1786 END IF;
1787 RAISE FND_API.G_EXC_ERROR;
1788 END IF;
1789
1790 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1791 RAISE FND_API.G_EXC_ERROR;
1792 END IF;
1793
1794 END IF;
1795
1796 -- Debug Message
1797 IF (AS_DEBUG_LOW_ON) THEN
1798
1799 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1800 'Calling line_Update_Row');
1801 END IF;
1802
1803 -- Invoke table handler(sales_lead_line_Update_Row)
1804 AS_SALES_LEAD_LINES_PKG.Sales_Lead_line_Update_Row(
1805 p_SALES_LEAD_LINE_ID => l_tar_SALES_LEAD_LINE_rec.SALES_LEAD_LINE_ID,
1806 p_LAST_UPDATE_DATE => SYSDATE,
1807 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1808 p_CREATION_DATE => l_tar_SALES_LEAD_LINE_rec.CREATION_DATE,
1809 p_CREATED_BY => l_tar_SALES_LEAD_LINE_rec.CREATED_BY,
1810 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1811 p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
1812 p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
1813 p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
1814 p_PROGRAM_UPDATE_DATE => SYSDATE,
1815 p_SALES_LEAD_ID => l_tar_SALES_LEAD_LINE_rec.SALES_LEAD_ID,
1816 p_STATUS_CODE => l_tar_SALES_LEAD_LINE_rec.STATUS_CODE,
1817
1818 /*p_INTEREST_TYPE_ID => l_tar_SALES_LEAD_LINE_rec.INTEREST_TYPE_ID,
1819 p_PRIMARY_INTEREST_CODE_ID =>
1820 l_tar_SALES_LEAD_LINE_rec.PRIMARY_INTEREST_CODE_ID,
1821 p_SECONDARY_INTEREST_CODE_ID =>
1822 l_tar_SALES_LEAD_LINE_rec.SECONDARY_INTEREST_CODE_ID,
1823
1824 */
1825 p_CATEGORY_ID =>
1826 l_tar_SALES_LEAD_LINE_rec.CATEGORY_ID,
1827
1828 p_CATEGORY_SET_ID =>
1829 l_tar_SALES_LEAD_LINE_rec.CATEGORY_SET_ID,
1830
1831
1832 p_INVENTORY_ITEM_ID => l_tar_SALES_LEAD_LINE_rec.INVENTORY_ITEM_ID,
1833 p_ORGANIZATION_ID => l_tar_SALES_LEAD_LINE_rec.ORGANIZATION_ID,
1834 p_UOM_CODE => l_tar_SALES_LEAD_LINE_rec.UOM_CODE,
1835 p_QUANTITY => l_tar_SALES_LEAD_LINE_rec.QUANTITY,
1836 p_BUDGET_AMOUNT => l_tar_SALES_LEAD_LINE_rec.BUDGET_AMOUNT,
1837 p_SOURCE_PROMOTION_ID =>
1838 l_tar_SALES_LEAD_LINE_rec.SOURCE_PROMOTION_ID,
1839 p_ATTRIBUTE_CATEGORY => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE_CATEGORY,
1840 p_ATTRIBUTE1 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE1,
1841 p_ATTRIBUTE2 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE2,
1842 p_ATTRIBUTE3 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE3,
1843 p_ATTRIBUTE4 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE4,
1844 p_ATTRIBUTE5 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE5,
1845 p_ATTRIBUTE6 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE6,
1846 p_ATTRIBUTE7 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE7,
1847 p_ATTRIBUTE8 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE8,
1848 p_ATTRIBUTE9 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE9,
1849 p_ATTRIBUTE10 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE10,
1850 p_ATTRIBUTE11 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE11,
1851 p_ATTRIBUTE12 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE12,
1852 p_ATTRIBUTE13 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE13,
1853 p_ATTRIBUTE14 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE14,
1854 p_ATTRIBUTE15 => l_tar_SALES_LEAD_LINE_rec.ATTRIBUTE15,
1855 p_OFFER_ID => l_tar_SALES_LEAD_LINE_rec.OFFER_ID
1856 -- p_SECURITY_GROUP_ID => l_tar_SALES_LEAD_LINE_rec.SECURITY_GROUP_ID
1857 );
1858 x_sales_lead_line_out_tbl(l_curr_row).sales_lead_line_id
1859 := l_tar_SALES_LEAD_LINE_rec.SALES_LEAD_LINE_ID;
1860 x_sales_lead_line_out_tbl(l_curr_row).return_status := x_return_status;
1861
1862 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1863 RAISE FND_API.G_EXC_ERROR;
1864 END IF;
1865
1866 /*
1867 IF l_tar_SALES_LEAD_LINE_rec.BUDGET_AMOUNT <> FND_API.G_MISS_NUM THEN
1868 l_update_amounts := l_update_amounts
1869 + NVL(l_tar_SALES_LEAD_LINE_rec.BUDGET_AMOUNT, 0)
1870 - NVL(l_budget_amount, 0);
1871 END IF;
1872 */
1873 END LOOP;
1874
1875 /*
1876 UPDATE as_sales_leads
1877 SET budget_amount = budget_amount + l_update_amounts
1878 WHERE sales_lead_id = l_sales_lead_id;
1879
1880 -- Debug Message
1881 IF (AS_DEBUG_LOW_ON) THEN
1882
1883 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1884 'Validate BUDGET_AMOUNT');
1885 END IF;
1886
1887 Validate_BUDGET_AMOUNTS(
1888 p_init_msg_list => FND_API.G_FALSE,
1889 p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
1890 p_SALES_LEAD_ID => l_SALES_LEAD_ID,
1891 x_return_status => x_return_status,
1892 x_msg_count => x_msg_count,
1893 x_msg_data => x_msg_data);
1894
1895 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1896 raise FND_API.G_EXC_ERROR;
1897 END IF;
1898 */
1899 -- Back update total_amount in lead header
1900 Backupdate_Header(
1901 p_sales_lead_id => l_sales_lead_id,
1902 x_return_status => x_return_status);
1903
1904 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1905 raise FND_API.G_EXC_ERROR;
1906 END IF;
1907
1908
1909 --
1910 -- END of API body.
1911 --
1912
1913 -- Standard check for p_commit
1914 IF FND_API.to_Boolean( p_commit )
1915 THEN
1916 COMMIT WORK;
1917 END IF;
1918
1919 -- Debug Message
1920 IF (AS_DEBUG_LOW_ON) THEN
1921
1922 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1923 'PVT: ' || l_api_name || ' End');
1924 END IF;
1925
1926 -- Standard call to get message count and IF count is 1, get message info.
1927 FND_MSG_PUB.Count_And_Get
1928 ( p_count => x_msg_count,
1929 p_data => x_msg_data
1930 );
1931
1932 EXCEPTION
1933 WHEN FND_API.G_EXC_ERROR THEN
1934 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1935 P_API_NAME => L_API_NAME
1936 ,P_PKG_NAME => G_PKG_NAME
1937 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1938 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1939 ,X_MSG_COUNT => X_MSG_COUNT
1940 ,X_MSG_DATA => X_MSG_DATA
1941 ,X_RETURN_STATUS => X_RETURN_STATUS);
1942
1943 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1944 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1945 P_API_NAME => L_API_NAME
1946 ,P_PKG_NAME => G_PKG_NAME
1947 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1948 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1949 ,X_MSG_COUNT => X_MSG_COUNT
1950 ,X_MSG_DATA => X_MSG_DATA
1951 ,X_RETURN_STATUS => X_RETURN_STATUS);
1952
1953 WHEN OTHERS THEN
1954 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1955 P_API_NAME => L_API_NAME
1956 ,P_PKG_NAME => G_PKG_NAME
1957 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1958 ,P_SQLCODE => SQLCODE
1959 ,P_SQLERRM => SQLERRM
1960 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1961 ,X_MSG_COUNT => X_MSG_COUNT
1962 ,X_MSG_DATA => X_MSG_DATA
1963 ,X_RETURN_STATUS => X_RETURN_STATUS);
1964 END Update_sales_lead_lines;
1965
1966
1967 PROCEDURE Delete_sales_lead_lines(
1968 P_Api_Version_Number IN NUMBER,
1969 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1970 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1971 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1972 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1973 P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1974 P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
1975 P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
1976 P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
1977 := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
1978 P_SALES_LEAD_LINE_Tbl IN AS_SALES_LEADS_PUB.SALES_LEAD_LINE_Tbl_type,
1979 X_SALES_LEAD_LINE_OUT_Tbl OUT NOCOPY AS_SALES_LEADS_PUB.SALES_LEAD_LINE_OUT_Tbl_Type,
1980 X_Return_Status OUT NOCOPY VARCHAR2,
1981 X_Msg_Count OUT NOCOPY NUMBER,
1982 X_Msg_Data OUT NOCOPY VARCHAR2
1983 )
1984 IS
1985 Cursor C_Get_sales_lead_line(c_SALES_LEAD_LINE_ID Number) IS
1986 Select LAST_UPDATE_DATE, BUDGET_AMOUNT
1987 From AS_SALES_LEAD_LINES
1988 WHERE sales_lead_line_id = c_sales_lead_line_id
1989 For Update NOWAIT;
1990
1991 l_api_name CONSTANT VARCHAR2(30) := 'Delete_sales_lead_lines';
1992 l_api_version_number CONSTANT NUMBER := 2.0;
1993 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
1994 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
1995 l_sales_lead_line_rec AS_SALES_LEADS_PUB.Sales_Lead_Line_Rec_Type;
1996 l_last_update_date DATE;
1997 l_delete_amounts NUMBER;
1998 l_budget_amount NUMBER;
1999 l_update_access_flag VARCHAR2(1);
2000 l_member_role VARCHAR2(5);
2001 l_member_access VARCHAR2(5);
2002 BEGIN
2003 -- Standard Start of API savepoint
2004 SAVEPOINT DELETE_SALES_LEAD_LINES_PVT;
2005
2006 -- Standard call to check for call compatibility.
2007 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2008 p_api_version_number,
2009 l_api_name,
2010 G_PKG_NAME)
2011 THEN
2012 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2013 END IF;
2014
2015 -- Initialize message list IF p_init_msg_list is set to TRUE.
2016 IF FND_API.to_Boolean( p_init_msg_list )
2017 THEN
2018 FND_MSG_PUB.initialize;
2019 END IF;
2020
2021 -- Debug Message
2022 IF (AS_DEBUG_LOW_ON) THEN
2023
2024 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2025 'PVT: ' || l_api_name || ' Start');
2026 END IF;
2027
2028 -- Initialize API return status to SUCCESS
2029 x_return_status := FND_API.G_RET_STS_SUCCESS;
2030
2031 --
2032 -- API body
2033 --
2034
2035 -- ******************************************************************
2036 -- Validate Environment
2037 -- ******************************************************************
2038 IF FND_GLOBAL.User_Id IS NULL
2039 THEN
2040 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2041 THEN
2042 AS_UTILITY_PVT.Set_Message(
2043 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2044 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
2045 p_token1 => 'PROFILE',
2046 p_token1_value => 'USER_ID');
2047 END IF;
2048 RAISE FND_API.G_EXC_ERROR;
2049 END IF;
2050
2051 IF (p_validation_level = fnd_api.g_valid_level_full)
2052 THEN
2053 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
2054 p_api_version_number => 2.0
2055 ,p_init_msg_list => p_init_msg_list
2056 ,p_salesforce_id => P_Identity_Salesforce_Id
2057 ,p_admin_group_id => p_admin_group_id
2058 ,x_return_status => x_return_status
2059 ,x_msg_count => x_msg_count
2060 ,x_msg_data => x_msg_data
2061 ,x_sales_member_rec => l_identity_sales_member_rec);
2062 END IF;
2063
2064 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2065 RAISE FND_API.G_EXC_ERROR;
2066 END IF;
2067
2068 l_delete_amounts := 0;
2069
2070 FOR l_curr_row IN 1..p_sales_lead_line_tbl.count LOOP
2071 x_sales_lead_line_out_tbl(l_curr_row).return_status
2072 := FND_API.G_RET_STS_SUCCESS;
2073
2074 -- Progress Message
2075 --
2076 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
2077 THEN
2078 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
2079 FND_MESSAGE.Set_Token ('ROW', 'AS_SALES_LEAD_LINE', TRUE);
2080 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
2081 FND_MSG_PUB.Add;
2082 END IF;
2083
2084 l_sales_lead_line_rec := p_sales_lead_line_tbl(l_curr_row);
2085
2086 Open C_Get_sales_lead_line( l_sales_lead_line_rec.sales_lead_line_id);
2087 Fetch C_Get_sales_lead_line into l_last_update_date, l_budget_amount;
2088
2089 IF ( C_Get_sales_lead_line%NOTFOUND) THEN
2090 Close C_Get_sales_lead_line;
2091 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2092 THEN
2093 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
2094 FND_MESSAGE.Set_Token ('INFO', 'SALES_LEAD_LINE', FALSE);
2095 FND_MSG_PUB.Add;
2096 END IF;
2097 raise FND_API.G_EXC_ERROR;
2098 END IF;
2099
2100 -- Debug Message
2101 IF (AS_DEBUG_LOW_ON) THEN
2102
2103 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2104 'Close C_Get_sales_lead_line');
2105 END IF;
2106 Close C_Get_sales_lead_line;
2107
2108 IF(P_Check_Access_Flag = 'Y') THEN
2109 -- Call Get_Access_Profiles to get access_profile_rec
2110 IF (AS_DEBUG_LOW_ON) THEN
2111
2112 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2113 'Calling Get_Access_Profiles');
2114 END IF;
2115
2116 AS_SALES_LEADS_PUB.Get_Access_Profiles(
2117 p_profile_tbl => p_sales_lead_profile_tbl,
2118 x_access_profile_rec => l_access_profile_rec);
2119
2120 IF (AS_DEBUG_LOW_ON) THEN
2121
2122
2123
2124 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2125 'Calling Has_updateLeadAccess');
2126
2127 END IF;
2128
2129 AS_ACCESS_PUB.Has_updateLeadAccess(
2130 p_api_version_number => 2.0
2131 ,p_init_msg_list => FND_API.G_FALSE
2132 ,p_validation_level => p_validation_level
2133 ,p_access_profile_rec => l_access_profile_rec
2134 ,p_admin_flag => p_admin_flag
2135 ,p_admin_group_id => p_admin_group_id
2136 ,p_person_id => l_identity_sales_member_rec.employee_person_id
2137 ,p_sales_lead_id => l_sales_lead_line_rec.sales_lead_id
2138 ,p_check_access_flag => p_check_access_flag -- should be 'Y'
2139 ,p_identity_salesforce_id => p_identity_salesforce_id
2140 ,p_partner_cont_party_id => NULL
2141 ,x_return_status => x_return_status
2142 ,x_msg_count => x_msg_count
2143 ,x_msg_data => x_msg_data
2144 ,x_update_access_flag => l_update_access_flag);
2145
2146 IF l_update_access_flag <> 'Y' THEN
2147 IF (AS_DEBUG_ERROR_ON) THEN
2148
2149 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
2150 'API_NO_CREATE_PRIVILEGE');
2151 END IF;
2152 RAISE FND_API.G_EXC_ERROR;
2153 END IF;
2154
2155 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2156 RAISE FND_API.G_EXC_ERROR;
2157 END IF;
2158
2159 END IF;
2160
2161 -- Debug Message
2162 IF (AS_DEBUG_LOW_ON) THEN
2163
2164 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2165 'Calling_Line_Delete_Row');
2166 END IF;
2167
2168 -- Invoke table handler(Sales_Lead_Line_Delete_Row)
2169 AS_SALES_LEAD_LINES_PKG.Sales_Lead_Line_Delete_Row(
2170 p_SALES_LEAD_LINE_ID => l_SALES_LEAD_LINE_rec.SALES_LEAD_LINE_ID);
2171
2172 x_sales_lead_line_out_tbl(l_curr_row).sales_lead_line_id
2173 := l_sales_lead_line_rec.sales_lead_line_id;
2174 x_sales_lead_line_out_tbl(l_curr_row).return_status := x_return_status;
2175
2176 -- l_delete_amounts := l_delete_amounts + nvl(l_budget_amount, 0);
2177
2178 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2179 RAISE FND_API.G_EXC_ERROR;
2180 END IF;
2181 END LOOP;
2182
2183 /*
2184 UPDATE as_sales_leads
2185 SET budget_amount = budget_amount - l_delete_amounts
2186 WHERE sales_lead_id = l_sales_lead_line_rec.sales_lead_id;
2187
2188 -- Debug Message
2189 IF (AS_DEBUG_LOW_ON) THEN
2190
2191 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2192 'Validate BUDGET_AMOUNT');
2193 END IF;
2194
2195 Validate_BUDGET_AMOUNTS(
2196 p_init_msg_list => FND_API.G_FALSE,
2197 p_validation_mode => AS_UTILITY_PVT.G_CREATE,
2198 p_SALES_LEAD_ID => l_sales_lead_line_rec.sales_lead_id,
2199 x_return_status => x_return_status,
2200 x_msg_count => x_msg_count,
2201 x_msg_data => x_msg_data);
2202
2203 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2204 raise FND_API.G_EXC_ERROR;
2205 END IF;
2206 */
2207
2208 --
2209 -- END of API body
2210 --
2211
2212 -- Standard check for p_commit
2213 IF FND_API.to_Boolean( p_commit )
2214 THEN
2215 COMMIT WORK;
2216 END IF;
2217
2218 -- Debug Message
2219 IF (AS_DEBUG_LOW_ON) THEN
2220
2221 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2222 'PVT: ' || l_api_name || ' End');
2223 END IF;
2224
2225 -- Standard call to get message count and IF count is 1, get message info.
2226 FND_MSG_PUB.Count_And_Get
2227 ( p_count => x_msg_count,
2228 p_data => x_msg_data );
2229
2230 EXCEPTION
2231 WHEN FND_API.G_EXC_ERROR THEN
2232 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2233 P_API_NAME => L_API_NAME
2234 ,P_PKG_NAME => G_PKG_NAME
2235 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2236 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2237 ,X_MSG_COUNT => X_MSG_COUNT
2238 ,X_MSG_DATA => X_MSG_DATA
2239 ,X_RETURN_STATUS => X_RETURN_STATUS);
2240
2241 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2242 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2243 P_API_NAME => L_API_NAME
2244 ,P_PKG_NAME => G_PKG_NAME
2245 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2246 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2247 ,X_MSG_COUNT => X_MSG_COUNT
2248 ,X_MSG_DATA => X_MSG_DATA
2249 ,X_RETURN_STATUS => X_RETURN_STATUS);
2250
2251 WHEN OTHERS THEN
2252 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2253 P_API_NAME => L_API_NAME
2254 ,P_PKG_NAME => G_PKG_NAME
2255 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
2256 ,P_SQLCODE => SQLCODE
2257 ,P_SQLERRM => SQLERRM
2258 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2259 ,X_MSG_COUNT => X_MSG_COUNT
2260 ,X_MSG_DATA => X_MSG_DATA
2261 ,X_RETURN_STATUS => X_RETURN_STATUS);
2262 END Delete_sales_lead_lines;
2263
2264
2265 END AS_SALES_LEAD_LINES_PVT;