[Home] [Help]
PACKAGE BODY: APPS.AS_OPP_LINE_PVT
Source
1 PACKAGE BODY AS_OPP_LINE_PVT as
2 /* $Header: asxvldlb.pls 120.5 2005/11/23 03:23:56 sumahali ship $ */
3 -- Start of Comments
4 -- Package name : AS_OPP_LINE_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_OPP_LINE_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxvldlb.pls';
13
14 -- Functional area for product catalog
15 G_FUNCTIONAL_AREA Constant NUMBER := 11;
16
17 -- Local procedure to reset Opp Header with total_amount
18 -- by the sum of the total_amounts of the lines
19
20 PROCEDURE Backupdate_Header(
21 p_lead_id IN NUMBER,
22 x_return_status OUT NOCOPY VARCHAR2
23 )
24 IS
25
26 CURSOR C_line_total IS
27 SELECT sum(total_amount) line_total
28 FROM as_lead_lines
29 WHERE lead_id = p_lead_id;
30 -- Cursor added for ASNB
31 CURSOR C_renue_opp_forst_tot IS
32 SELECT nvl(sum(OPP_FORECAST_AMOUNT),0) credit_total
33 FROM as_sales_credits
34 WHERE lead_id = p_lead_id
35 AND credit_type_id = FND_PROFILE.VALUE('AS_FORECAST_CREDIT_TYPE_ID');
36
37 l_line_total NUMBER;
38 l_tot_revenue_opp_forecast_amt NUMBER := 0; --Added for ASNB
39
40 BEGIN
41 x_return_status := FND_API.G_RET_STS_SUCCESS;
42
43 OPEN C_line_total;
44 FETCH C_line_total into l_line_total;
45 CLOSE C_line_total;
46
47 -- Begin Added for ASNB
48 OPEN C_renue_opp_forst_tot;
49 FETCH C_renue_opp_forst_tot into l_tot_revenue_opp_forecast_amt;
50 CLOSE C_renue_opp_forst_tot;
51 -- End Added for ASNB
52
53 UPDATE as_leads_all
54 SET total_amount = nvl(l_line_total, 0),
55 TOTAL_REVENUE_OPP_FORECAST_AMT = l_tot_revenue_opp_forecast_amt, -- Added for ASNB
56 last_update_date = SYSDATE,
57 last_updated_by = FND_GLOBAL.USER_ID,
58 -- creation_Date = SYSDATE, -- solin, for bug 1579950
59 -- created_by = FND_GLOBAL.USER_ID, -- solin, for bug 1579950
60 last_update_login = FND_GLOBAL.CONC_LOGIN_ID
61 WHERE lead_id = p_lead_id;
62 IF (SQL%NOTFOUND) THEN
63 RAISE NO_DATA_FOUND;
64 END IF;
65
66 EXCEPTION
67 WHEN OTHERS
68 THEN
69 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
70 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
71
72 END Backupdate_Header;
73
74 -- Local Procedure to apply manual forecast values to Sales Credits
75 PROCEDURE Apply_Manual_Forecast_Values(
76 p_lead_line_id IN NUMBER,
77 p_opp_worst_forecast_amount IN NUMBER,
78 p_opp_forecast_amount IN NUMBER,
79 p_opp_best_forecast_amount IN NUMBER
80 )
81 IS
82 l_forecast_credit_type_id CONSTANT NUMBER := FND_PROFILE.Value('AS_FORECAST_CREDIT_TYPE_ID');
83 l_opp_worst_forecast_amount NUMBER := p_opp_worst_forecast_amount;
84 l_opp_forecast_amount NUMBER := p_opp_forecast_amount;
85 l_opp_best_forecast_amount NUMBER := p_opp_best_forecast_amount;
86 BEGIN
87
88 IF l_opp_worst_forecast_amount = FND_API.G_MISS_NUM THEN
89 l_opp_worst_forecast_amount := NULL;
90 END IF;
91 IF l_opp_forecast_amount = FND_API.G_MISS_NUM THEN
92 l_opp_forecast_amount := NULL;
93 END IF;
94 IF l_opp_best_forecast_amount = FND_API.G_MISS_NUM THEN
95 l_opp_best_forecast_amount := NULL;
96 END IF;
97
98 IF l_opp_worst_forecast_amount IS NOT NULL OR
99 l_opp_forecast_amount IS NOT NULL OR
100 l_opp_best_forecast_amount IS NOT NULL
101 THEN
102 Update as_sales_credits
103 Set opp_worst_forecast_amount = nvl(l_opp_worst_forecast_amount,
104 opp_worst_forecast_amount),
105 opp_forecast_amount = nvl(l_opp_forecast_amount,
106 opp_forecast_amount),
107 opp_best_forecast_amount = nvl(l_opp_best_forecast_amount,
108 opp_best_forecast_amount)
109 where lead_line_id = p_lead_line_id AND
110 credit_type_id = l_forecast_credit_type_id;
111 END IF;
112
113 END Apply_Manual_Forecast_Values;
114
115
116 -- Local procedure to reset Sales Credits with Credit_amount
117 -- because of the change of the total_amount in the line
118 --
119 -- Recalculate the sales credit amount distribution based
120 -- on the existing credit percent or implied credit percent
121 -- for the Forecast credit type.
122 -- Also applies manual forecast values if supplied irrespective
123 -- of change in line amount.
124
125 PROCEDURE Recalculate_Sales_Credits(
126 p_lead_id IN NUMBER,
127 p_lead_line_id IN NUMBER,
128 p_line_amount_old IN NUMBER,
129 p_line_amount_new IN NUMBER,
130 p_opp_worst_forecast_amount IN NUMBER,
131 p_opp_forecast_amount IN NUMBER,
132 p_opp_best_forecast_amount IN NUMBER,
133 x_return_status OUT NOCOPY VARCHAR2
134 )
135 IS
136 l_forecast_credit_type_id CONSTANT NUMBER := FND_PROFILE.Value('AS_FORECAST_CREDIT_TYPE_ID');
137 CURSOR C_sales_credits(c_lead_line_id NUMBER ) IS
138 SELECT sales_credit_id,
139 credit_amount,
140 credit_percent,
141 credit_type_id
142 FROM as_sales_credits
143 WHERE lead_line_id = c_lead_line_id;
144
145 l_credit_percent NUMBER;
146 l_credit_amount NUMBER;
147 l_line_amount_old NUMBER := nvl( p_line_amount_old, 0);
148 l_line_amount_new NUMBER := nvl( p_line_amount_new, 0);
149 l_credit_type_id NUMBER := FND_PROFILE.Value('AS_FORECAST_CREDIT_TYPE_ID');
150 l_temp_bool BOOLEAN;
151 l_opp_worst_forecast_amount NUMBER;
152 l_opp_forecast_amount NUMBER;
153 l_opp_best_forecast_amount NUMBER;
154 l_win_probability NUMBER;
155 l_win_loss_indicator as_statuses_b.win_loss_indicator%Type;
156 l_forecast_rollup_flag as_statuses_b.forecast_rollup_flag%Type;
157
158 l_count NUMBER;
159
160 BEGIN
161 x_return_status := FND_API.G_RET_STS_SUCCESS;
162
163 IF l_line_amount_new = FND_API.G_MISS_NUM THEN
164 l_line_amount_new := l_line_amount_old;
165 END IF;
166
167 IF l_line_amount_old <> l_line_amount_new
168 THEN
169 Select lead.win_probability, status.win_loss_indicator,
170 status.forecast_rollup_flag
171 Into l_win_probability, l_win_loss_indicator,
172 l_forecast_rollup_flag
173 From as_leads_all lead, as_statuses_vl status
174 Where lead_id = p_lead_id
175 And lead.status = status.status_code(+);
176 IF l_line_amount_old <> 0 THEN
177 FOR sc_rec In C_sales_credits(p_lead_line_id) LOOP
178 l_credit_percent := nvl(sc_rec.credit_percent, sc_rec.credit_amount*100/p_line_amount_old);
179 l_credit_amount := l_credit_percent * p_line_amount_new/100;
180
181 l_opp_worst_forecast_amount := NULL;
182 l_opp_forecast_amount := NULL;
183 l_opp_best_forecast_amount := NULL;
184 l_temp_bool := AS_OPP_SALES_CREDIT_PVT.Apply_Forecast_Defaults(
185 l_win_probability, l_win_loss_indicator, l_forecast_rollup_flag,
186 -11, l_win_probability, l_win_loss_indicator,
187 l_forecast_rollup_flag, l_credit_amount, 'ON-UPDATE',
188 l_opp_worst_forecast_amount, l_opp_forecast_amount,
189 l_opp_best_forecast_amount);
190
191 -- Manual Override of BWF amounts
192 IF sc_rec.credit_type_id = l_forecast_credit_type_id THEN
193 IF nvl(p_opp_worst_forecast_amount, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
194 THEN
195 l_opp_worst_forecast_amount := p_opp_worst_forecast_amount;
196 END IF;
197 IF nvl(p_opp_forecast_amount, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
198 THEN
199 l_opp_forecast_amount := p_opp_forecast_amount;
200 END IF;
201 IF nvl(p_opp_best_forecast_amount, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
202 THEN
203 l_opp_best_forecast_amount := p_opp_best_forecast_amount;
204 END IF;
205 END IF;
206
207 UPDATE as_sales_credits
208 SET object_version_number = nvl(object_version_number,0) + 1, credit_percent = l_credit_percent,
209 credit_amount = l_credit_amount,
210 last_update_date = SYSDATE,
211 last_updated_by = FND_GLOBAL.USER_ID,
212 opp_worst_forecast_amount = nvl(l_opp_worst_forecast_amount,
213 opp_worst_forecast_amount),
214 opp_forecast_amount = nvl(l_opp_forecast_amount,
215 opp_forecast_amount),
216 opp_best_forecast_amount = nvl(l_opp_best_forecast_amount,
217 opp_best_forecast_amount)
218 WHERE sales_credit_id = sc_rec.sales_credit_id;
219 END LOOP;
220 ELSE
221 FOR sc_rec In C_sales_credits(p_lead_line_id) LOOP
222 l_credit_percent := nvl(sc_rec.credit_percent, 0);
223 l_credit_amount := l_credit_percent * p_line_amount_new/100;
224
225 l_opp_worst_forecast_amount := NULL;
226 l_opp_forecast_amount := NULL;
227 l_opp_best_forecast_amount := NULL;
228 l_temp_bool := AS_OPP_SALES_CREDIT_PVT.Apply_Forecast_Defaults(
229 l_win_probability, l_win_loss_indicator, l_forecast_rollup_flag,
230 -11, l_win_probability, l_win_loss_indicator,
231 l_forecast_rollup_flag, l_credit_amount, 'ON-UPDATE',
232 l_opp_worst_forecast_amount, l_opp_forecast_amount,
233 l_opp_best_forecast_amount);
234
235 -- Manual Override of BWF amounts
236 IF sc_rec.credit_type_id = l_forecast_credit_type_id THEN
237 IF nvl(p_opp_worst_forecast_amount, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
238 THEN
239 l_opp_worst_forecast_amount := p_opp_worst_forecast_amount;
240 END IF;
241 IF nvl(p_opp_forecast_amount, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
242 THEN
243 l_opp_forecast_amount := p_opp_forecast_amount;
244 END IF;
245 IF nvl(p_opp_best_forecast_amount, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
246 THEN
247 l_opp_best_forecast_amount := p_opp_best_forecast_amount;
248 END IF;
249 END IF;
250
251
252 UPDATE as_sales_credits
253 SET object_version_number = nvl(object_version_number,0) + 1, credit_percent = l_credit_percent,
254 credit_amount = l_credit_amount,
255 last_update_date = SYSDATE,
256 last_updated_by = FND_GLOBAL.USER_ID,
257 opp_worst_forecast_amount = nvl(l_opp_worst_forecast_amount,
258 opp_worst_forecast_amount),
259 opp_forecast_amount = nvl(l_opp_forecast_amount,
260 opp_forecast_amount),
261 opp_best_forecast_amount = nvl(l_opp_best_forecast_amount,
262 opp_best_forecast_amount)
263 WHERE sales_credit_id = sc_rec.sales_credit_id;
264 END LOOP;
265 END IF;
266 ELSE
267 Apply_Manual_Forecast_Values(p_lead_line_id,
268 p_opp_worst_forecast_amount, p_opp_forecast_amount,
269 p_opp_best_forecast_amount);
270 END IF;
271
272 EXCEPTION
273 WHEN OTHERS
274 THEN
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277
278 END Recalculate_Sales_Credits;
279
280 -- Local Procedure validates interest ids and returns SUCCESS if all ids are
281 -- valid, ERROR otherwise
282 -- Procedure assumes that at least the interest type exists
283 --
284 PROCEDURE Validate_Interest_Fields ( p_interest_type_id IN NUMBER,
285 p_primary_interest_code_id IN NUMBER,
286 p_secondary_interest_code_id IN NUMBER,
287 p_return_status OUT NOCOPY VARCHAR2
288 )
289 Is
290 CURSOR C_Int_Type_Exists (X_Int_Type_Id NUMBER) IS
291 SELECT 'X'
292 FROM as_interest_types_b
293 WHERE Interest_Type_Id = X_Int_Type_Id;
294
295 CURSOR C_Prim_Int_Code_Exists (X_Int_Code_Id NUMBER,
296 X_Int_Type_Id NUMBER) IS
297 SELECT 'X'
298 FROM As_Interest_Codes_B Pic
299 WHERE Pic.Interest_Type_Id = X_Int_Type_Id
300 and Pic.Interest_Code_Id = X_Int_Code_Id
301 and Pic.Parent_Interest_Code_Id Is Null;
302
303 CURSOR C_Sec_Int_Code_Exists (X_Sec_Int_Code_Id NUMBER,
304 X_Int_Code_Id NUMBER,
305 X_Int_Type_Id NUMBER) IS
306 SELECT 'X'
307 FROM As_Interest_Codes_B Sic
308 WHERE Sic.Interest_Type_Id = X_Int_Type_Id
309 And Sic.Interest_Code_Id = X_Sec_Int_Code_Id
310 And Sic.Parent_Interest_Code_Id = X_Int_Code_Id;
311
312 l_variable VARCHAR2(1);
313 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
314 Begin
315
316 OPEN C_Int_Type_Exists (p_interest_type_id);
317 FETCH C_Int_Type_Exists INTO l_variable;
318
319 IF (C_Int_Type_Exists%NOTFOUND)
320 THEN
321 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
322 THEN
323 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
324 FND_MESSAGE.Set_Token('COLUMN', 'INTEREST_TYPE', FALSE);
325 FND_MESSAGE.Set_Token('VALUE', p_interest_type_id, FALSE);
326 FND_MSG_PUB.Add;
327 END IF;
328
329 l_return_status := FND_API.G_RET_STS_ERROR;
330 END IF;
331 CLOSE C_Int_Type_Exists;
332
333
334 IF p_primary_interest_code_id is NOT NULL
335 and p_primary_interest_code_id <> FND_API.G_MISS_NUM
336 THEN
337 OPEN C_Prim_Int_Code_Exists ( p_primary_interest_code_id,
338 p_interest_type_id);
339 FETCH C_Prim_Int_Code_Exists INTO l_variable;
340
341 IF (C_Prim_Int_Code_Exists%NOTFOUND)
342 THEN
343 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_Msg_Lvl_Error)
344 THEN
345 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
346 FND_MESSAGE.Set_Token('COLUMN', 'PRIMARY_INTEREST_CODE', FALSE);
347 FND_MESSAGE.Set_Token('VALUE', p_primary_interest_code_id, FALSE);
348 FND_MSG_PUB.Add;
349 END IF;
350
351 l_return_status := FND_API.G_RET_STS_ERROR;
352 END IF;
353 CLOSE C_Prim_Int_Code_Exists;
354 END IF;
355
356
357 IF p_secondary_interest_code_id is NOT NULL
358 and p_secondary_interest_code_id <> FND_API.G_MISS_NUM
359 THEN
360 OPEN C_Sec_Int_Code_Exists (p_secondary_interest_code_id,
361 p_primary_interest_code_id,
362 p_interest_type_id);
363 FETCH C_Sec_Int_Code_Exists INTO l_variable;
364 IF (C_Sec_Int_Code_Exists%NOTFOUND)
365 THEN
366 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
367 THEN
368 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
369 FND_MESSAGE.Set_Token('COLUMN', 'SECONDARY_INTEREST_CODE', FALSE);
370 FND_MESSAGE.Set_Token('VALUE', p_secondary_interest_code_id, FALSE);
371 FND_MSG_PUB.ADD;
372 END IF;
373
374 l_return_status := FND_API.G_RET_STS_ERROR;
375 END IF;
376 CLOSE C_Sec_Int_Code_Exists;
377 END IF;
378
379 p_return_status := l_return_status;
380
381 END Validate_Interest_Fields;
382
383 -- Local Procedure
384 -- This should be used ONLY when old line data(pre 11.5.10) needs to be validated
385 -- Note that is assumes that either of interest_type_id or inventory_item_id is not null
386 PROCEDURE Validate_Old_Line_rec( p_interest_type_id IN NUMBER,
387 p_primary_interest_code_id IN NUMBER,
388 p_secondary_interest_code_id IN NUMBER,
389 p_inventory_item_id IN NUMBER,
390 p_organization_id IN NUMBER,
391 p_return_status OUT NOCOPY VARCHAR2
392 )
393 IS
394
395 CURSOR C_Inventory_Item_Exists (c_Inventory_Item_Id NUMBER,
396 c_Organization_Id NUMBER) IS
397 SELECT 'X'
398 FROM mtl_system_items
399 WHERE inventory_item_id = c_Inventory_Item_Id
400 and organization_id = c_Organization_Id;
401
402 CURSOR C_Category_Item_Exists ( c_interest_type_id number,
403 c_primary_interest_code_id number,
404 c_secondary_interest_code_id number,
405 c_inventory_item_id number,
406 c_organization_id number) IS
407 select 'x'
408 from as_inv_item_lov_v
409 where interest_type_id = c_interest_type_id and
410 (primary_interest_code_id = c_primary_interest_code_id or
411 c_primary_interest_code_id is null) and
412 (secondary_interest_code_id = c_secondary_interest_code_id or
413 c_secondary_interest_code_id is null)and
414 inventory_item_id = c_inventory_item_id and
415 organization_id = c_organization_id;
416
417
418 l_val VARCHAR2(1);
419 l_return_status VARCHAR2(1);
420 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Old_Line_rec';
421 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
422
423
424 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Validate_Old_Line_rec';
425 BEGIN
426 -- Debug Message
427 IF l_debug THEN
428 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
429 'Private API: ' || l_api_name || ' start');
430
431 END IF;
432
433 -- Initialize API return status to SUCCESS
434 p_return_status := FND_API.G_RET_STS_SUCCESS;
435
436 -- Validate the interest fields
437 IF p_interest_type_id is NOT NULL and
438 p_interest_type_id <> FND_API.G_MISS_NUM
439 THEN
440 Validate_Interest_Fields (
441 p_interest_type_id => p_interest_type_id,
442 p_primary_interest_code_id => p_primary_interest_code_id,
443 p_secondary_interest_code_id => p_secondary_interest_code_id,
444 p_return_status => l_return_status
445 );
446
447 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
448 THEN
449 p_return_status := l_return_status;
450 END IF;
451
452 -- Jean add in 6/5 for the bug 1801521
453 -- No need to check for this profile as profile is obsoleted
454 -- IF (FND_PROFILE.VALUE('AS_INV_CATEGORIES_FLAG') = 'Y')
455 --THEN
456 IF p_interest_type_id is NOT NULL and
457 p_interest_type_id <> FND_API.G_MISS_NUM and
458 p_inventory_item_id is NOT NULL and
459 p_inventory_item_id <> FND_API.G_MISS_NUM
460 THEN
461 OPEN C_Category_Item_Exists ( p_interest_type_id,
462 p_primary_interest_code_id,
463 p_secondary_interest_code_id,
464 p_inventory_item_id,
465 p_organization_id );
466 FETCH C_Category_Item_Exists into l_val;
467 IF C_Category_Item_Exists%NOTFOUND
468 THEN
469 IF l_debug THEN
470 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
471 'Private API: Inventory item doesnot match category');
472 END IF;
473
474 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
475 THEN
476 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ITEM_CATEGORY');
477 FND_MSG_PUB.ADD;
478 END IF;
479 p_return_status := FND_API.G_RET_STS_ERROR;
480 END IF;
481 CLOSE C_Category_Item_Exists;
482 END IF;
483 --END IF;
484
485 END IF;
486
487
488 -- Validate Inventory Item and Organization Id
489 --
490 IF p_inventory_item_id is NOT NULL and
491 p_inventory_item_id <> FND_API.G_MISS_NUM and
492 ( p_organization_id is NULL or
493 p_organization_id = FND_API.G_MISS_NUM )
494 THEN
495 IF l_debug THEN
496 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
497 'Private API: ORGANIZATION_ID is missing');
498
499 END IF;
500
501 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
502 THEN
503 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
504 FND_MESSAGE.Set_Token('COLUMN', 'ORGANIZATION_ID', FALSE);
505 FND_MSG_PUB.ADD;
506 END IF;
507
508 p_return_status := FND_API.G_RET_STS_ERROR;
509 ELSIF p_inventory_item_id is NOT NULL and
510 p_inventory_item_id <> FND_API.G_MISS_NUM
511 THEN
512 OPEN C_Inventory_Item_Exists ( p_inventory_item_id,
513 p_organization_id );
514 FETCH C_Inventory_Item_Exists into l_val;
515 IF C_Inventory_Item_Exists%NOTFOUND
516 THEN
517 IF l_debug THEN
518 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
519 'Private API: INVENTORY_ITEM_ID is invalid');
520 END IF;
521
522 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
523 THEN
524 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
525 FND_MESSAGE.Set_Token('COLUMN', 'INVENTORY_ITEM_ID', FALSE);
526 FND_MESSAGE.Set_Token('VALUE', p_inventory_item_id, FALSE);
527 FND_MSG_PUB.ADD;
528 END IF;
529
530 p_return_status := FND_API.G_RET_STS_ERROR;
531 END IF;
532 CLOSE C_Inventory_Item_Exists;
533 END IF;
534
535 -- Debug Message
536 IF l_debug THEN
537 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
538 'Private API: ' || l_api_name || ' end');
539
540 END IF;
541
542 END Validate_Old_Line_Rec;
543
544 -- Local procedure to derive product category from
545 -- interest code/primary/secondary/itemid(pre 11.5.10)
546 PROCEDURE Derive_PRODUCT_CATEGORY (
547 p_Line_Rec IN OUT NOCOPY AS_OPPORTUNITY_PUB.Line_Rec_Type,
548 p_Return_Status OUT NOCOPY VARCHAR2
549 )
550 IS
551 CURSOR C_GET_CATEGORY_FROM_ITEM(l_organization_id NUMBER,
552 l_item_id NUMBER) IS
553 select category_id,category_set_id from mtl_item_categories
554 where category_set_id=
555 (select category_set_id
556 from mtl_default_category_sets
557 where functional_area_id=G_FUNCTIONAL_AREA)
558 and organization_id=l_organization_id
559 and inventory_item_id=l_item_id;
560
561 CURSOR C_GET_CATEGORY_FROM_IT(c_interest_type_id NUMBER) IS
562 select product_category_id, product_cat_set_id
563 from AS_INTEREST_TYPES_B
564 where interest_type_id = c_interest_type_id;
565
566 CURSOR C_GET_CATEGORY_FROM_PIC(c_interest_type_id NUMBER, c_interest_code_id NUMBER) IS
567 select product_category_id, product_cat_set_id
568 from AS_INTEREST_CODES_B
569 where interest_code_id = c_interest_code_id
570 and interest_type_id = c_interest_type_id;
571
572 CURSOR C_GET_CATEGORY_FROM_SIC(c_interest_type_id NUMBER, c_pri_interest_code_id NUMBER, c_sec_interest_code_id NUMBER) IS
573 select product_category_id, product_cat_set_id
574 from AS_INTEREST_CODES_B
575 where interest_code_id = c_sec_interest_code_id
576 and interest_type_id = c_interest_type_id
577 and parent_interest_code_id = c_pri_interest_code_id;
578
579 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
580 l_category_id NUMBER;
581 l_category_set_id NUMBER;
582 l_return_status VARCHAR2(1);
583 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Derive_PRODUCT_CATEGORY';
584 BEGIN
585
586 -- Either interest type or inventory item must be populated
587 IF (p_line_rec.interest_type_id is NOT NULL and p_line_rec.interest_type_id <> FND_API.G_MISS_NUM) or
588 (p_line_rec.inventory_item_id is NOT NULL and p_line_rec.inventory_item_id <> FND_API.G_MISS_NUM)
589 THEN
590 Validate_Old_Line_Rec (
591 p_interest_type_id => p_line_rec.interest_type_id,
592 p_primary_interest_code_id => p_line_rec.primary_interest_code_id,
593 p_secondary_interest_code_id => p_line_rec.secondary_interest_code_id,
594 p_inventory_item_id => p_line_rec.inventory_item_id,
595 p_organization_id => p_line_rec.organization_id,
596 p_return_status => l_return_status
597 );
598
599 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
600 IF (p_Line_Rec.inventory_item_id is not null
601 and p_Line_Rec.inventory_item_id <> FND_API.G_MISS_NUM) THEN
602 Open C_GET_CATEGORY_FROM_ITEM(p_Line_Rec.organization_id, p_Line_Rec.inventory_item_id);
603 Fetch C_GET_CATEGORY_FROM_ITEM INTO l_category_id,l_category_set_id;
604 IF C_GET_CATEGORY_FROM_ITEM%FOUND THEN
605 CLOSE C_GET_CATEGORY_FROM_ITEM;
606 p_Line_Rec.Product_Category_Id := l_category_id;
607 p_Line_Rec.Product_Cat_Set_Id := l_category_set_id;
608 ELSE
609 CLOSE C_GET_CATEGORY_FROM_ITEM;
610 IF l_debug THEN
611 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
612 'Private API: Unable to derive product category from item');
613 END IF;
614 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
615 THEN
616 FND_MESSAGE.Set_Name('AS', 'API_DERIVE_PC_ERROR');
617 FND_MSG_PUB.ADD;
618 END IF;
619 l_return_status := FND_API.G_RET_STS_ERROR;
620 END IF;
621 ELSIF (p_Line_Rec.secondary_interest_code_id is not null
622 and p_Line_Rec.secondary_interest_code_id <> FND_API.G_MISS_NUM) THEN
623 Open C_GET_CATEGORY_FROM_SIC(p_Line_Rec.interest_type_id,p_Line_Rec.primary_interest_code_id,p_Line_Rec.secondary_interest_code_id);
624 Fetch C_GET_CATEGORY_FROM_SIC INTO l_category_id,l_category_set_id;
625 IF C_GET_CATEGORY_FROM_SIC%FOUND THEN
626 CLOSE C_GET_CATEGORY_FROM_SIC;
627 p_Line_Rec.Product_Category_Id := l_category_id;
628 p_Line_Rec.Product_Cat_Set_Id := l_category_set_id;
629 ELSE
630 CLOSE C_GET_CATEGORY_FROM_SIC;
631 IF l_debug THEN
632 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
633 'Private API: Unable to derive product category from secondary interest code');
634 END IF;
635 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
636 THEN
637 FND_MESSAGE.Set_Name('AS', 'API_DERIVE_PC_ERROR');
638 FND_MSG_PUB.ADD;
639 END IF;
640 l_return_status := FND_API.G_RET_STS_ERROR;
641 END IF;
642 ELSIF (p_Line_Rec.primary_interest_code_id is not null
643 and p_Line_Rec.primary_interest_code_id <> FND_API.G_MISS_NUM) THEN
644 Open C_GET_CATEGORY_FROM_PIC(p_Line_Rec.interest_type_id,p_Line_Rec.primary_interest_code_id);
645 Fetch C_GET_CATEGORY_FROM_PIC INTO l_category_id,l_category_set_id;
646 IF C_GET_CATEGORY_FROM_PIC%FOUND THEN
647 CLOSE C_GET_CATEGORY_FROM_PIC;
648 p_Line_Rec.Product_Category_Id := l_category_id;
649 p_Line_Rec.Product_Cat_Set_Id := l_category_set_id;
650 ELSE
651 CLOSE C_GET_CATEGORY_FROM_PIC;
652 IF l_debug THEN
653 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
654 'Private API: Unable to derive product category from primary interest code');
655 END IF;
656 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
657 THEN
658 FND_MESSAGE.Set_Name('AS', 'API_DERIVE_PC_ERROR');
659 FND_MSG_PUB.ADD;
660 END IF;
661 l_return_status := FND_API.G_RET_STS_ERROR;
662 END IF;
663 ELSIF (p_Line_Rec.interest_type_id is not null
664 and p_Line_Rec.interest_type_id <> FND_API.G_MISS_NUM) THEN
665 Open C_GET_CATEGORY_FROM_IT(p_Line_Rec.interest_type_id);
666 Fetch C_GET_CATEGORY_FROM_IT INTO l_category_id,l_category_set_id;
667 IF C_GET_CATEGORY_FROM_IT%FOUND THEN
668 CLOSE C_GET_CATEGORY_FROM_IT;
669 p_Line_Rec.Product_Category_Id := l_category_id;
670 p_Line_Rec.Product_Cat_Set_Id := l_category_set_id;
671 ELSE
672 CLOSE C_GET_CATEGORY_FROM_IT;
673 IF l_debug THEN
674 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
675 'Private API: Unable to derive product category from interest type');
676 END IF;
677 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
678 THEN
679 FND_MESSAGE.Set_Name('AS', 'API_DERIVE_PC_ERROR');
680 FND_MSG_PUB.ADD;
681 END IF;
682 l_return_status := FND_API.G_RET_STS_ERROR;
683 END IF;
684 END IF;
685 END IF;
686 END IF;
687
688 p_return_status := l_return_status;
689
690 END Derive_PRODUCT_CATEGORY;
691
692 -- Hint: Primary key needs to be returned.
693 PROCEDURE Create_opp_lines(
694 P_Api_Version_Number IN NUMBER,
695 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
696 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
697 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
698 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
699 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
700 P_Admin_Group_Id IN NUMBER,
701 P_Identity_Salesforce_Id IN NUMBER := NULL,
702 p_salesgroup_id IN NUMBER := NULL,
703 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
704 -- Suresh Mahalingam: Removed init to FND_API.G_MISS_NUM to fix GSCC warning
705 P_Partner_Cont_Party_id IN NUMBER,
706 P_Line_Tbl IN AS_OPPORTUNITY_PUB.Line_Tbl_Type :=
707 AS_OPPORTUNITY_PUB.G_MISS_Line_Tbl,
708 P_Header_Rec IN AS_OPPORTUNITY_PUB.Header_Rec_Type,
709 X_LINE_OUT_TBL OUT NOCOPY AS_OPPORTUNITY_PUB.Line_out_Tbl_type,
710 X_Return_Status OUT NOCOPY VARCHAR2,
711 X_Msg_Count OUT NOCOPY NUMBER,
712 X_Msg_Data OUT NOCOPY VARCHAR2
713 )
714
715 IS
716 /* salesgroup_id will be passed in by parameter p_salesgroup_id
717 CURSOR c_salesgroup_id(p_resource_id number) IS
718 SELECT group_id
719 FROM JTF_RS_GROUP_MEMBERS
720 WHERE resource_id = p_resource_id
721 ORDER BY GROUP_ID;
722 */
723
724 -- solin, for bug 1554330
725 CURSOR c_get_opp_freeze_flag(c_LEAD_ID NUMBER) IS
726 SELECT FREEZE_FLAG
727 FROM AS_LEADS
728 WHERE LEAD_ID = c_LEAD_ID;
729
730 CURSOR c_decision_date(c_lead_id NUMBER) IS
731 select decision_date
732 from as_leads_all
733 where lead_id = c_lead_id;
734
735 CURSOR c_lead_org_id(p_lead_id NUMBER) IS
736 select org_id
737 from as_leads_all
738 where lead_id = p_lead_id;
739
740
741 CURSOR c_campaign_id(c_LEAD_ID NUMBER) IS
742 SELECT SOURCE_PROMOTION_ID
743 FROM AS_OPPORTUNITIES_V
744 WHERE LEAD_ID = c_LEAD_ID;
745
746 CURSOR c_offer_id(c_LEAD_ID NUMBER) IS
747 SELECT OFFER_ID
748 FROM AS_OPPORTUNITIES_V
749 WHERE LEAD_ID = c_LEAD_ID;
750
751 CURSOR c_valid_group(p_salesforce_id NUMBER, p_sales_group_id NUMBER) is
752 select 'Y'
753 from as_fc_salesforce_v sf
754 where sf.sales_group_id = p_sales_group_id
755 and sf.salesforce_id = p_salesforce_id;
756
757 /*
758 CURSOR c_isd_group(c_sales_group_id NUMBER, c_resource_id NUMBER) IS
759 select gm1.group_id
760 from jtf_rs_group_members gm1,
761 jtf_rs_groups_vl gp1,
762 jtf_rs_groups_vl gp
763 where gm1.resource_id = c_resource_id
764 and gp1.group_id = gm1.group_id
765 and gp.group_name||'-iSD' = gp1.group_name
766 and gp.group_id = c_sales_group_id;
767 */
768
769 l_api_name CONSTANT VARCHAR2(30) := 'Create_opp_lines';
770 l_api_version_number CONSTANT NUMBER := 2.0;
771 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
772 l_Line_Rec AS_OPPORTUNITY_PUB.Line_Rec_Type;
773 l_LEAD_LINE_ID NUMBER;
774 l_line_count CONSTANT NUMBER := P_Line_Tbl.count;
775 l_update_access_flag VARCHAR2(1);
776 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
777
778 l_sales_credit_tbl AS_OPPORTUNITY_PUB.Sales_Credit_Tbl_type;
779 l_sales_credit_rec AS_OPPORTUNITY_PUB.Sales_Credit_Rec_type;
780 -- l_salesgroup_id NUMBER;
781 x_sales_credit_out_tbl AS_OPPORTUNITY_PUB.Sales_Credit_Out_Tbl_Type;
782
783 l_forecast_credit_type_id NUMBER := FND_PROFILE.Value('AS_FORECAST_CREDIT_TYPE_ID');
784 l_isd_credit_type_id NUMBER := FND_PROFILE.Value('AS_ISD_CREDIT_TYPE_ID');
785 l_isd_sales_group_id NUMBER := FND_PROFILE.Value('AS_ISD_SALES_GROUP_ID');
786
787
788 l_freeze_flag VARCHAR2(1) := 'N'; -- solin, for bug 1554330
789 l_allow_flag VARCHAR2(1); -- solin, for bug 1554330
790 l_decision_date DATE;
791
792 l_valid_group VARCHAR2(1) := 'N';
793
794 org_id NUMBER;
795 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
796
797 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Create_opp_lines';
798 BEGIN
799 -- Standard Start of API savepoint
800 SAVEPOINT CREATE_OPP_LINES_PVT;
801
802 -- Standard call to check for call compatibility.
803 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
804 p_api_version_number,
805 l_api_name,
806 G_PKG_NAME)
807 THEN
808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
809 END IF;
810
811
812 -- Initialize message list if p_init_msg_list is set to TRUE.
813 IF FND_API.to_Boolean( p_init_msg_list )
814 THEN
815 FND_MSG_PUB.initialize;
816 END IF;
817
818
819 -- Debug Message
820 IF l_debug THEN
821 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
822 'Private API: ' || l_api_name || ' start');
823 END IF;
824
825
826 -- Initialize API return status to SUCCESS
827 x_return_status := FND_API.G_RET_STS_SUCCESS;
828
829 --
830 -- API body
831 --
832
833 /*
834 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
835 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is
836 -- invoked for customization purpose
837 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
838 THEN
839 AS_CALLOUT_PKG.Create_opp_lines_BC(
840 p_api_version_number => 2.0,
841 p_init_msg_list => FND_API.G_FALSE,
842 p_commit => FND_API.G_FALSE,
843 p_validation_level => p_validation_level,
844 P_Line_Rec => P_Line_Rec,
845 -- Hint: Add detail tables as parameter lists if it's master-detail
846 relationship.
847 x_return_status => x_return_status,
848 x_msg_count => x_msg_count,
849 x_msg_data => x_msg_data);
850 END IF;
851 */
852
853
854 -- ******************************************************************
855 -- Validate Environment
856 -- ******************************************************************
857 IF FND_GLOBAL.User_Id IS NULL
858 THEN
859 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
860 THEN
861 FND_MESSAGE.Set_Name(' + appShortName +',
862 'UT_CANNOT_GET_PROFILE_VALUE');
863 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
864 FND_MSG_PUB.ADD;
865 END IF;
866 RAISE FND_API.G_EXC_ERROR;
867 END IF;
868
869 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
870 p_api_version_number => 2.0
871 ,p_init_msg_list => p_init_msg_list
872 ,p_salesforce_id => p_identity_salesforce_id
873 ,p_admin_group_id => p_admin_group_id
874 ,x_return_status => x_return_status
875 ,x_msg_count => x_msg_count
876 ,x_msg_data => x_msg_data
877 ,x_sales_member_rec => l_identity_sales_member_rec);
878
879 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
880 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
881 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
882 'Private API: Get_CurrentUser fail');
883 END IF;
884 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
885 END IF;
886
887 -- fix for the bug 2776714. Give a meaningful error message when defualt
888 --forecast credit type is null
889 IF l_forecast_credit_type_id IS NULL THEN
890 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
891 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
892 'The profile AS_FORECAST_CREDIT_TYPE_ID is null');
893 END IF;
894
895 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
896 FND_MESSAGE.Set_Name('AS', 'AS_CREDIT_TYPE_MISSING');
897 FND_MSG_PUB.ADD;
898 END IF;
899
900 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
901 RAISE FND_API.G_EXC_ERROR;
902 END IF;
903
904
905 IF(P_Check_Access_Flag = 'Y') THEN
906
907 -- Call Get_Access_Profiles to get access_profile_rec
908 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
909 p_profile_tbl => p_profile_tbl,
910 x_access_profile_rec => l_access_profile_rec);
911
912 AS_ACCESS_PUB.has_updateOpportunityAccess
913 ( p_api_version_number => 2.0
914 ,p_init_msg_list => p_init_msg_list
915 ,p_validation_level => p_validation_level
916 ,p_access_profile_rec => l_access_profile_rec
917 ,p_admin_flag => p_admin_flag
918 ,p_admin_group_id => p_admin_group_id
919 ,p_person_id => l_identity_sales_member_rec.employee_person_id
920 ,p_opportunity_id => p_line_tbl(1).LEAD_ID
921 ,p_check_access_flag => p_check_access_flag
922 ,p_identity_salesforce_id => p_identity_salesforce_id
923 ,p_partner_cont_party_id => p_partner_cont_party_id
924 ,x_return_status => x_return_status
925 ,x_msg_count => x_msg_count
926 ,x_msg_data => x_msg_data
927 ,x_update_access_flag => l_update_access_flag );
928
929 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
930 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
931 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
932 'Private API: has_updateOpportunityAccess fail');
933 END IF;
934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
935 END IF;
936
937 IF (l_update_access_flag <> 'Y') THEN
938 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
939 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
940 FND_MESSAGE.Set_Token('INFO', 'CUSTOMER_ID,OPPORTUNITY_ID,SALESFORCE_ID', FALSE);
941 FND_MSG_PUB.ADD;
942 END IF;
943 RAISE FND_API.G_EXC_ERROR;
944 ELSE
945 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
946 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
947 'Private API: has_updateOpportunityAccess succeed');
948 END IF;
949 END IF;
950 END IF;
951
952 -- solin, for bug 1554330
953 OPEN c_get_opp_freeze_flag(p_line_tbl(1).LEAD_ID);
954 FETCH c_get_opp_freeze_flag INTO l_freeze_flag;
955 CLOSE c_get_opp_freeze_flag;
956
957 IF l_freeze_flag = 'Y'
958 THEN
959 l_allow_flag := NVL(FND_PROFILE.VALUE('AS_ALLOW_UPDATE_FROZEN_OPP'),'Y');
960 IF l_allow_flag <> 'Y' THEN
961 AS_UTILITY_PVT.Set_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
962 'API_OPP_FROZEN');
963 RAISE FND_API.G_EXC_ERROR;
964 END IF;
965 END IF;
966 -- end 1554330
967
968 FOR l_curr_row IN 1..l_line_count LOOP
969 X_line_out_tbl(l_curr_row).return_status := FND_API.G_RET_STS_SUCCESS;
970
971 -- Progress Message
972 --
973 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
974 THEN
975 --FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
976 --FND_MESSAGE.Set_Token ('ROW', 'AS_LEAD_LINE', TRUE);
977 --FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
978 --FND_MSG_PUB.Add;
979 IF l_debug THEN
980 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
981 'Processing AS_LEAD_LINE row number '||l_curr_row );
982 END IF;
983
984 END IF;
985
986 l_line_rec := P_Line_Tbl(l_curr_row);
987
988 -- Debug message
989 IF l_debug THEN
990 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
991 'Private API: Validate_opp_line');
992 END IF;
993
994 -- Default organization from profile ASO_PRODUCT_ORGANIZATION_ID if
995 -- necessary
996 -- Jean change here using profile OE_ORGANIZATION_ID
997
998 /* Commented out for MOAC changes. ORG_ID should be passed and will not
999 be defaulted
1000 IF( l_line_rec.inventory_item_id IS NOT NULL AND
1001 l_line_rec.inventory_item_id <> FND_API.G_MISS_NUM AND
1002 (l_line_rec.organization_id IS NULL OR
1003 l_line_rec.organization_id = FND_API.G_MISS_NUM ))
1004 THEN
1005 --l_line_rec.organization_id := FND_PROFILE.Value('ASO_PRODUCT_ORGANIZATION_ID');
1006 org_id := FND_PROFILE.Value('ORG_ID');
1007 --l_line_rec.organization_id := FND_PROFILE.Value('OE_ORGANIZATION_ID');
1008 l_line_rec.organization_id := oe_profile.value('OE_ORGANIZATION_ID', org_id);
1009
1010 END IF;
1011 */
1012
1013 -- Bug 4657299, Defaulting org_id from header rec
1014 IF l_line_rec.org_id IS NULL
1015 OR l_line_rec.org_id = FND_API.G_MISS_NUM THEN
1016 org_id := NULL;
1017 OPEN c_lead_org_id (l_line_rec.lead_id);
1018 FETCH c_lead_org_id INTO org_id;
1019 CLOSE c_lead_org_id;
1020 l_line_rec.org_id := org_id;
1021 END IF;
1022
1023 -- Default forecast date for the purchase line
1024 IF (l_line_rec.FORECAST_DATE is NULL OR
1025 l_line_rec.FORECAST_DATE = FND_API.G_MISS_Date ) THEN
1026
1027 /* Fix for bug# 4111558 */
1028 IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'),'N') = 'Y' then
1029 l_line_rec.ROLLING_FORECAST_FLAG := 'N';
1030 else
1031 OPEN c_decision_date (l_line_rec.lead_id);
1032 FETCH c_decision_date INTO l_line_rec.FORECAST_DATE;
1033 CLOSE c_decision_date;
1034 l_line_rec.ROLLING_FORECAST_FLAG := 'Y';
1035 end if;
1036
1037 ELSE
1038 l_line_rec.ROLLING_FORECAST_FLAG := 'N';
1039 END IF;
1040
1041 IF (l_line_rec.total_amount IS NULL OR
1042 l_line_rec.total_amount = FND_API.G_MISS_NUM ) THEN
1043 l_line_rec.total_amount := 0;
1044 END IF;
1045
1046 IF (l_line_rec.source_promotion_id IS NULL OR
1047 l_line_rec.source_promotion_id = FND_API.G_MISS_NUM ) THEN
1048 OPEN c_campaign_id (l_line_rec.lead_id);
1049 FETCH c_campaign_id INTO l_line_rec.source_promotion_id;
1050 CLOSE c_campaign_id;
1051
1052 END IF;
1053
1054 IF (l_line_rec.offer_id IS NULL OR
1055 l_line_rec.offer_id = FND_API.G_MISS_NUM ) THEN
1056 OPEN c_offer_id (l_line_rec.lead_id);
1057 FETCH c_offer_id INTO l_line_rec.offer_id;
1058 CLOSE c_offer_id;
1059
1060 END IF;
1061
1062 -- Trunc forecast date
1063 l_line_rec.FORECAST_DATE := trunc(l_line_rec.FORECAST_DATE);
1064
1065 -- Bug 3739252
1066 -- If product category_id and category_set_id is not passed,
1067 -- we can try to derive it first from item and then from a
1068 -- combination of interest_type_id, primary_interest_code_id
1069 -- and secondary_interest_code_id.
1070 if (((l_line_rec.product_category_id is NULL)
1071 or (l_line_rec.product_category_id = FND_API.G_MISS_NUM))
1072 and ((l_line_rec.product_cat_set_id is NULL)
1073 or (l_line_rec.product_cat_set_id = FND_API.G_MISS_NUM))) then
1074 Derive_PRODUCT_CATEGORY(p_Line_Rec => l_Line_Rec, p_Return_Status => x_return_status);
1075 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1076 -- Debug message
1077 IF l_debug THEN
1078 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1079 'Private API: Derive_PRODUCT_CATEGORY fail');
1080 END IF;
1081 RAISE FND_API.G_EXC_ERROR;
1082 END IF;
1083 end if;
1084
1085
1086
1087 Validate_opp_line(
1088 p_init_msg_list => FND_API.G_FALSE,
1089 p_validation_level => p_validation_level,
1090 p_validation_mode => AS_UTILITY_PVT.G_CREATE,
1091 P_Line_Rec => l_Line_Rec,
1092 x_return_status => x_return_status,
1093 x_msg_count => x_msg_count,
1094 x_msg_data => x_msg_data);
1095
1096 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1097 -- Debug message
1098 IF l_debug THEN
1099 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1100 'Private API: Validate_opp_line fail');
1101 END IF;
1102
1103 RAISE FND_API.G_EXC_ERROR;
1104 END IF;
1105
1106
1107 -- Debug Message
1108 IF l_debug THEN
1109 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1110 'Private API: Calling create table handler');
1111 END IF;
1112
1113 l_LEAD_LINE_ID := l_Line_rec.LEAD_LINE_ID;
1114
1115 -- Invoke table handler(AS_LEAD_LINES_PKG.Insert_Row)
1116 AS_LEAD_LINES_PKG.Insert_Row(
1117 px_LEAD_LINE_ID => l_LEAD_LINE_ID,
1118 p_LAST_UPDATE_DATE => SYSDATE,
1119 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1120 p_CREATION_DATE => SYSDATE,
1121 p_CREATED_BY => FND_GLOBAL.USER_ID,
1122 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1123 p_REQUEST_ID => l_Line_Rec.REQUEST_ID,
1124 p_PROGRAM_APPLICATION_ID => l_Line_Rec.PROGRAM_APPLICATION_ID,
1125 p_PROGRAM_ID => l_Line_Rec.PROGRAM_ID,
1126 p_PROGRAM_UPDATE_DATE => l_Line_Rec.PROGRAM_UPDATE_DATE,
1127 p_LEAD_ID => l_Line_Rec.LEAD_ID,
1128 p_INTEREST_TYPE_ID => l_Line_Rec.INTEREST_TYPE_ID,
1129 p_PRIMARY_INTEREST_CODE_ID => l_Line_Rec.PRIMARY_INTEREST_CODE_ID,
1130 p_SECONDARY_INTEREST_CODE_ID =>
1131 l_Line_Rec.SECONDARY_INTEREST_CODE_ID,
1132 p_INTEREST_STATUS_CODE => l_Line_Rec.INTEREST_STATUS_CODE,
1133 p_INVENTORY_ITEM_ID => l_Line_Rec.INVENTORY_ITEM_ID,
1134 p_ORGANIZATION_ID => l_Line_Rec.ORGANIZATION_ID,
1135 p_UOM_CODE => l_Line_Rec.UOM_CODE,
1136 p_QUANTITY => l_Line_Rec.QUANTITY,
1137 p_TOTAL_AMOUNT => l_Line_Rec.TOTAL_AMOUNT,
1138 p_SALES_STAGE_ID => l_Line_Rec.SALES_STAGE_ID,
1139 p_WIN_PROBABILITY => l_Line_Rec.WIN_PROBABILITY,
1140 p_DECISION_DATE => l_Line_Rec.DECISION_DATE,
1141 p_ORG_ID => l_Line_Rec.ORG_ID,
1142 p_ATTRIBUTE_CATEGORY => l_Line_Rec.ATTRIBUTE_CATEGORY,
1143 p_ATTRIBUTE1 => l_Line_Rec.ATTRIBUTE1,
1144 p_ATTRIBUTE2 => l_Line_Rec.ATTRIBUTE2,
1145 p_ATTRIBUTE3 => l_Line_Rec.ATTRIBUTE3,
1146 p_ATTRIBUTE4 => l_Line_Rec.ATTRIBUTE4,
1147 p_ATTRIBUTE5 => l_Line_Rec.ATTRIBUTE5,
1148 p_ATTRIBUTE6 => l_Line_Rec.ATTRIBUTE6,
1149 p_ATTRIBUTE7 => l_Line_Rec.ATTRIBUTE7,
1150 p_ATTRIBUTE8 => l_Line_Rec.ATTRIBUTE8,
1151 p_ATTRIBUTE9 => l_Line_Rec.ATTRIBUTE9,
1152 p_ATTRIBUTE10 => l_Line_Rec.ATTRIBUTE10,
1153 p_ATTRIBUTE11 => l_Line_Rec.ATTRIBUTE11,
1154 p_ATTRIBUTE12 => l_Line_Rec.ATTRIBUTE12,
1155 p_ATTRIBUTE13 => l_Line_Rec.ATTRIBUTE13,
1156 p_ATTRIBUTE14 => l_Line_Rec.ATTRIBUTE14,
1157 p_ATTRIBUTE15 => l_Line_Rec.ATTRIBUTE15,
1158 p_STATUS_CODE => l_Line_Rec.STATUS_CODE,
1159 p_CHANNEL_CODE => l_Line_Rec.CHANNEL_CODE,
1160 p_QUOTED_LINE_FLAG => l_Line_Rec.QUOTED_LINE_FLAG,
1161 p_PRICE => l_Line_Rec.PRICE,
1162 p_PRICE_VOLUME_MARGIN => l_Line_Rec.PRICE_VOLUME_MARGIN,
1163 p_SHIP_DATE => l_Line_Rec.SHIP_DATE,
1164 p_FORECAST_DATE => l_Line_Rec.FORECAST_DATE,
1165 p_ROLLING_FORECAST_FLAG => l_Line_Rec.ROLLING_FORECAST_FLAG,
1166 p_SOURCE_PROMOTION_ID => l_Line_Rec.SOURCE_PROMOTION_ID,
1167 p_OFFER_ID => l_Line_Rec.OFFER_ID,
1168 p_PRODUCT_CATEGORY_ID => l_Line_Rec.PRODUCT_CATEGORY_ID,
1169 p_PRODUCT_CAT_SET_ID => l_Line_Rec.PRODUCT_CAT_SET_ID);
1170
1171 X_Line_out_tbl(l_curr_row).LEAD_LINE_ID := l_LEAD_LINE_ID;
1172 X_Line_out_tbl(l_curr_row).return_status := x_return_status;
1173
1174 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1175 RAISE FND_API.G_EXC_ERROR;
1176 END IF;
1177
1178 IF l_debug THEN
1179 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1180 'Private API1: Created line_id: '||l_LEAD_LINE_ID );
1181 END IF;
1182
1183
1184 --
1185 -- Create sales credit for the sales rep by default
1186 --
1187
1188 /* salesgroup_id is passed in by p_salesgroup_id */
1189 -- Get the salesgroup_id
1190 -- l_salesgroup_id := null;
1191 -- OPEN c_salesgroup_id(l_identity_sales_member_rec.salesforce_id);
1192 -- FETCH c_salesgroup_id INTO l_salesgroup_id;
1193 -- CLOSE c_salesgroup_id;
1194
1195 -- Build l_sales_credit_rec
1196 l_sales_credit_rec.last_update_date := SYSDATE;
1197 l_sales_credit_rec.last_updated_by := FND_GLOBAL.USER_ID;
1198 l_sales_credit_rec.creation_Date := SYSDATE;
1199 l_sales_credit_rec.created_by := FND_GLOBAL.USER_ID;
1200 l_sales_credit_rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
1201 l_sales_credit_rec.lead_id := l_Line_Rec.lead_id;
1202 l_sales_credit_rec.lead_line_id := l_LEAD_LINE_ID;
1203 l_sales_credit_rec.salesforce_id := l_identity_sales_member_rec.salesforce_id;
1204 l_sales_credit_rec.person_id := l_identity_sales_member_rec.employee_person_id;
1205
1206 IF l_debug THEN
1207 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1208 'employee_person_id' ||l_identity_sales_member_rec.employee_person_id );
1209 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1210 'partner_customer_id' ||l_identity_sales_member_rec.partner_customer_id );
1211 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1212 'partner_contact_id' ||l_identity_sales_member_rec.partner_contact_id );
1213 END IF;
1214
1215 l_sales_credit_rec.salesgroup_id := p_salesgroup_id;
1216 IF (l_identity_sales_member_rec.partner_customer_id is NOT NULL) and (l_identity_sales_member_rec.partner_customer_id <>FND_API.G_MISS_NUM)
1217 THEN
1218 l_sales_credit_rec.partner_customer_id := l_identity_sales_member_rec.partner_customer_id;
1219 l_sales_credit_rec.partner_address_id := l_identity_sales_member_rec.partner_address_id;
1220 ELSE
1221 l_sales_credit_rec.partner_customer_id := l_identity_sales_member_rec.partner_contact_id;
1222 END IF;
1223 l_sales_credit_rec.credit_type_id := l_forecast_credit_type_id;
1224 l_sales_credit_rec.credit_amount := l_Line_Rec.total_amount;
1225 l_sales_credit_rec.credit_percent := 100;
1226
1227 l_sales_credit_tbl(1) := l_sales_credit_rec;
1228
1229 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_RECORD) THEN
1230 AS_OPP_sales_credit_PVT.Validate_SALES_CREDIT_Rec(
1231 p_init_msg_list => FND_API.G_FALSE,
1232 p_validation_mode => AS_UTILITY_PVT.G_CREATE,
1233 P_SALES_CREDIT_Rec => l_sales_credit_Rec,
1234 x_return_status => x_return_status,
1235 x_msg_count => x_msg_count,
1236 x_msg_data => x_msg_data);
1237
1238 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1239 IF l_debug THEN
1240 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1241 'Private API2: Create_Opp_line: validate_sc_rec fail' );
1242 END IF;
1243
1244 raise FND_API.G_EXC_ERROR;
1245 END IF;
1246 END IF;
1247
1248 AS_OPP_sales_credit_PVT.Create_sales_credits(
1249 P_Api_Version_Number => 2.0,
1250 P_Init_Msg_List => FND_API.G_FALSE,
1251 P_Commit => FND_API.G_FALSE,
1252 P_Validation_Level => 100, --FND_API.G_VALID_LEVEL_FULL,
1253 P_Check_Access_Flag => FND_API.G_FALSE,
1254 P_Admin_Flag => FND_API.G_FALSE,
1255 P_Admin_Group_Id => P_Admin_Group_Id,
1256 P_Identity_Salesforce_Id => P_Identity_Salesforce_Id,
1257 P_Partner_Cont_Party_Id => p_partner_cont_party_id,
1258 P_Profile_Tbl => P_Profile_tbl,
1259 P_Sales_Credit_Tbl => l_sales_credit_tbl,
1260 X_Sales_Credit_Out_Tbl => x_sales_credit_out_tbl,
1261 X_Return_Status => x_return_status,
1262 X_Msg_Count => x_msg_count,
1263 X_Msg_Data => x_msg_data);
1264
1265 -- Check return status from the above procedure call
1266 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1267 THEN
1268 IF l_debug THEN
1269 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1270 'Private API3: Create_Opp_line: Create_Sales_credit fail' );
1271 END IF;
1272 raise FND_API.G_EXC_ERROR;
1273 END IF;
1274
1275 -- Default sales credit for iSD sales group for orcale internal only
1276
1277 IF ( nvl( FND_PROFILE.Value('ASF_IS_ORACLE_INTERNAL'), 'N') = 'Y') AND
1278 ( l_isd_credit_type_id IS NOT NULL ) AND
1279 ( l_isd_sales_group_id IS NOT NULL ) THEN
1280
1281 l_sales_credit_rec.credit_type_id := l_isd_credit_type_id;
1282 l_sales_credit_rec.salesgroup_id := l_isd_sales_group_id;
1283 l_sales_credit_tbl(1) := l_sales_credit_rec;
1284
1285 open c_valid_group(l_sales_credit_rec.salesforce_id, l_sales_credit_rec.salesgroup_id);
1286 fetch c_valid_group into l_valid_group;
1287 close c_valid_group;
1288
1289 IF nvl( l_valid_group, 'N') = 'Y' THEN
1290 AS_OPP_sales_credit_PVT.Create_sales_credits(
1291 P_Api_Version_Number => 2.0,
1292 P_Init_Msg_List => FND_API.G_FALSE,
1293 P_Commit => FND_API.G_FALSE,
1294 P_Validation_Level => 100, --FND_API.G_VALID_LEVEL_FULL,
1295 P_Check_Access_Flag => FND_API.G_FALSE,
1296 P_Admin_Flag => FND_API.G_FALSE,
1297 P_Admin_Group_Id => P_Admin_Group_Id,
1298 P_Identity_Salesforce_Id => P_Identity_Salesforce_Id,
1299 P_Partner_Cont_Party_Id => p_partner_cont_party_id,
1300 P_Profile_Tbl => P_Profile_tbl,
1301 P_Sales_Credit_Tbl => l_sales_credit_tbl,
1302 X_Sales_Credit_Out_Tbl => x_sales_credit_out_tbl,
1303 X_Return_Status => x_return_status,
1304 X_Msg_Count => x_msg_count,
1305 X_Msg_Data => x_msg_data);
1306
1307 -- Check return status from the above procedure call
1308 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1309 THEN
1310 IF l_debug THEN
1311 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1312 'Private API3: Create_Opp_line: Create_Sales_credit fail' );
1313 END IF;
1314 raise FND_API.G_EXC_ERROR;
1315 END IF;
1316
1317 ELSE
1318 IF l_debug THEN
1319 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1320 'Private API: l_isd_sales_group_id is invalid ' );
1321 END IF;
1322 END IF;
1323 END IF;
1324
1325 -- Override Forecast Defaults with manual values if any
1326 Apply_Manual_Forecast_Values(l_LEAD_LINE_ID,
1327 l_Line_Rec.opp_worst_forecast_amount, l_Line_Rec.opp_forecast_amount,
1328 l_Line_Rec.opp_best_forecast_amount);
1329
1330 END LOOP;
1331
1332
1333 -- Back update total_amount in opp header
1334 Backupdate_Header(
1335 p_lead_id => p_header_rec.lead_id,
1336 x_return_status => x_return_status);
1337
1338 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1339 IF l_debug THEN
1340 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1341 'Private API: Create_Opp_line: Backupdate_header fail' );
1342 END IF;
1343 raise FND_API.G_EXC_ERROR;
1344 END IF;
1345
1346 -- Assign/Reassign the territory resources for the opportunity
1347
1348 -- Debug Message
1349 IF l_debug THEN
1350 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1351 'Calling Opportunity Real Time API');
1352 END IF;
1353
1354 AS_RTTAP_OPPTY.RTTAP_WRAPPER(
1355 P_Api_Version_Number => 1.0,
1356 P_Init_Msg_List => FND_API.G_FALSE,
1357 P_Commit => FND_API.G_FALSE,
1358 p_lead_id => p_line_tbl(1).LEAD_ID,
1359 X_Return_Status => x_return_status,
1360 X_Msg_Count => x_msg_count,
1361 X_Msg_Data => x_msg_data
1362 );
1363
1364 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1365 IF l_debug THEN
1366 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1367 'Opportunity Real Time API fail');
1368 END IF;
1369 RAISE FND_API.G_EXC_ERROR;
1370 END IF;
1371
1372 --
1373 -- End of API body
1374 --
1375
1376 -- Standard check for p_commit
1377 IF FND_API.to_Boolean( p_commit )
1378 THEN
1379 COMMIT WORK;
1380 END IF;
1381
1382
1383 -- Debug Message
1384 IF l_debug THEN
1385 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1386 'Private API: ' || l_api_name || ' end');
1387 END IF;
1388
1389
1390 -- Standard call to get message count and if count is 1, get message info.
1391 FND_MSG_PUB.Count_And_Get
1392 ( p_count => x_msg_count,
1393 p_data => x_msg_data
1394 );
1395
1396 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
1397 /*
1398 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is
1399 -- invoked for customization purpose
1400 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
1401 THEN
1402 AS_CALLOUT_PKG.Create_opp_lines_AC(
1403 p_api_version_number => 2.0,
1404 p_init_msg_list => FND_API.G_FALSE,
1405 p_commit => FND_API.G_FALSE,
1406 p_validation_level => p_validation_level,
1407 P_Line_Rec => P_Line_Rec,
1408 -- Hint: Add detail tables as parameter lists if it's master-detail
1409 -- relationship.
1410 x_return_status => x_return_status,
1411 x_msg_count => x_msg_count,
1412 x_msg_data => x_msg_data);
1413 END IF;
1414 */
1415
1416 EXCEPTION
1417 WHEN FND_API.G_EXC_ERROR THEN
1418 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1419 P_MODULE => l_module
1420 ,P_API_NAME => L_API_NAME
1421 ,P_PKG_NAME => G_PKG_NAME
1422 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1423 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1424 ,X_MSG_COUNT => X_MSG_COUNT
1425 ,X_MSG_DATA => X_MSG_DATA
1426 ,X_RETURN_STATUS => X_RETURN_STATUS);
1427
1428 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1429 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1430 P_MODULE => l_module
1431 ,P_API_NAME => L_API_NAME
1432 ,P_PKG_NAME => G_PKG_NAME
1433 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1434 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1435 ,X_MSG_COUNT => X_MSG_COUNT
1436 ,X_MSG_DATA => X_MSG_DATA
1437 ,X_RETURN_STATUS => X_RETURN_STATUS);
1438
1439 WHEN OTHERS THEN
1440 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1441 P_MODULE => l_module
1442 ,P_API_NAME => L_API_NAME
1443 ,P_PKG_NAME => G_PKG_NAME
1444 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1445 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1446 ,X_MSG_COUNT => X_MSG_COUNT
1447 ,X_MSG_DATA => X_MSG_DATA
1448 ,X_RETURN_STATUS => X_RETURN_STATUS);
1449 End Create_opp_lines;
1450
1451
1452 -- Hint: Add corresponding update detail table procedures if it's master-detail
1453 -- relationship.
1454 PROCEDURE Update_opp_lines(
1455 P_Api_Version_Number IN NUMBER,
1456 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1457 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1458 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1459 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
1460 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
1461 P_Admin_Group_Id IN NUMBER,
1462 P_Identity_Salesforce_Id IN NUMBER := NULL,
1463 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
1464 -- Suresh Mahalingam: Removed init to FND_API.G_MISS_NUM to fix GSCC warning
1465 P_Partner_Cont_Party_id IN NUMBER,
1466 P_Line_Tbl IN AS_OPPORTUNITY_PUB.Line_Tbl_Type,
1467 P_Header_Rec IN AS_OPPORTUNITY_PUB.Header_Rec_Type,
1468 X_LINE_OUT_TBL OUT NOCOPY AS_OPPORTUNITY_PUB.Line_out_Tbl_type,
1469 X_Return_Status OUT NOCOPY VARCHAR2,
1470 X_Msg_Count OUT NOCOPY NUMBER,
1471 X_Msg_Data OUT NOCOPY VARCHAR2
1472 )
1473
1474 IS
1475 Cursor C_Get_opp_line(c_LEAD_LINE_ID Number) IS
1476 Select LAST_UPDATE_DATE, TOTAL_AMOUNT
1477 From AS_LEAD_LINES
1478 WHERE LEAD_LINE_ID = c_LEAD_LINE_ID
1479 For Update NOWAIT;
1480
1481 -- solin, for bug 1554330
1482 CURSOR c_get_opp_freeze_flag(c_LEAD_ID NUMBER) IS
1483 SELECT FREEZE_FLAG
1484 FROM AS_LEADS
1485 WHERE LEAD_ID = c_LEAD_ID;
1486
1487 CURSOR c_decision_date(c_lead_id NUMBER) IS
1488 select decision_date
1489 from as_leads_all
1490 where lead_id = c_lead_id;
1491
1492 l_api_name CONSTANT VARCHAR2(30) := 'Update_opp_lines';
1493 l_api_version_number CONSTANT NUMBER := 2.0;
1494 -- Local Variables
1495 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
1496 l_ref_Line_rec AS_OPPORTUNITY_PUB.Line_Rec_Type;
1497 l_rowid ROWID;
1498 l_Line_Rec AS_OPPORTUNITY_PUB.Line_Rec_Type;
1499 l_line_count CONSTANT NUMBER := P_Line_Tbl.count;
1500 l_last_update_date DATE;
1501 l_update_access_flag VARCHAR2(1);
1502 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
1503
1504 l_line_amount_old NUMBER;
1505 l_freeze_flag VARCHAR2(1) := 'N'; -- solin, for bug 1554330
1506 l_allow_flag VARCHAR2(1); -- solin, for bug 1554330
1507 l_decision_date DATE;
1508 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1509
1510 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Update_opp_lines';
1511 BEGIN
1512 -- Standard Start of API savepoint
1513 SAVEPOINT UPDATE_OPP_LINES_PVT;
1514
1515 -- Standard call to check for call compatibility.
1516 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1517 p_api_version_number,
1518 l_api_name,
1519 G_PKG_NAME)
1520 THEN
1521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1522 END IF;
1523
1524
1525 -- Initialize message list if p_init_msg_list is set to TRUE.
1526 IF FND_API.to_Boolean( p_init_msg_list )
1527 THEN
1528 FND_MSG_PUB.initialize;
1529 END IF;
1530
1531
1532 -- Debug Message
1533 IF l_debug THEN
1534 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1535 'Private API: ' || l_api_name || ' start');
1536 END IF;
1537
1538
1539 -- Initialize API return status to SUCCESS
1540 x_return_status := FND_API.G_RET_STS_SUCCESS;
1541
1542 --
1543 -- Api body
1544 --
1545 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
1546 /*
1547 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is
1548 -- invoked for customization purpose
1549 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
1550 THEN
1551 AS_CALLOUT_PKG.Update_opp_lines_BU(
1552 p_api_version_number => 2.0,
1553 p_init_msg_list => FND_API.G_FALSE,
1554 p_commit => FND_API.G_FALSE,
1555 p_validation_level => p_validation_level,
1556 p_identity_salesforce_id => p_identity_salesforce_id,
1557 P_Line_Rec => P_Line_Rec,
1558 -- Hint: Add detail tables as parameter lists if it's master-detail
1559 -- relationship.
1560 x_return_status => x_return_status,
1561 x_msg_count => x_msg_count,
1562 x_msg_data => x_msg_data);
1563 END IF;
1564 */
1565
1566 IF(P_Check_Access_Flag = 'Y') THEN
1567 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1568 p_api_version_number => 2.0
1569 ,p_init_msg_list => p_init_msg_list
1570 ,p_salesforce_id => p_identity_salesforce_id
1571 ,p_admin_group_id => p_admin_group_id
1572 ,x_return_status => x_return_status
1573 ,x_msg_count => x_msg_count
1574 ,x_msg_data => x_msg_data
1575 ,x_sales_member_rec => l_identity_sales_member_rec);
1576
1577 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1578 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1579 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1580 'Private API: Get_CurrentUser fail');
1581 END IF;
1582 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1583 END IF;
1584
1585
1586 -- Call Get_Access_Profiles to get access_profile_rec
1587 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
1588 p_profile_tbl => p_profile_tbl,
1589 x_access_profile_rec => l_access_profile_rec);
1590
1591 AS_ACCESS_PUB.has_updateOpportunityAccess
1592 ( p_api_version_number => 2.0
1593 ,p_init_msg_list => p_init_msg_list
1594 ,p_validation_level => p_validation_level
1595 ,p_access_profile_rec => l_access_profile_rec
1596 ,p_admin_flag => p_admin_flag
1597 ,p_admin_group_id => p_admin_group_id
1598 ,p_person_id => l_identity_sales_member_rec.employee_person_id
1599 ,p_opportunity_id => p_line_tbl(1).LEAD_ID
1600 ,p_check_access_flag => p_check_access_flag
1601 ,p_identity_salesforce_id => p_identity_salesforce_id
1602 ,p_partner_cont_party_id => p_partner_cont_party_id
1603 ,x_return_status => x_return_status
1604 ,x_msg_count => x_msg_count
1605 ,x_msg_data => x_msg_data
1606 ,x_update_access_flag => l_update_access_flag );
1607
1608 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1609 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1610 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1611 'has_updateOpportunityAccess fail');
1612 END IF;
1613 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1614 END IF;
1615
1616 IF (l_update_access_flag <> 'Y') THEN
1617 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1618 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
1619 FND_MESSAGE.Set_Token('INFO', 'CUSTOMER_ID,OPPORTUNITY_ID,SALESFORCE_ID', FALSE);
1620 FND_MSG_PUB.ADD;
1621 END IF;
1622 RAISE FND_API.G_EXC_ERROR;
1623 END IF;
1624 END IF;
1625
1626 -- solin, for bug 1554330
1627 OPEN c_get_opp_freeze_flag(p_line_tbl(1).LEAD_ID);
1628 FETCH c_get_opp_freeze_flag INTO l_freeze_flag;
1629 CLOSE c_get_opp_freeze_flag;
1630
1631 IF l_freeze_flag = 'Y'
1632 THEN
1633 l_allow_flag := NVL(FND_PROFILE.VALUE('AS_ALLOW_UPDATE_FROZEN_OPP'),'Y');
1634 IF l_allow_flag <> 'Y' THEN
1635 AS_UTILITY_PVT.Set_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
1636 'API_OPP_FROZEN');
1637 RAISE FND_API.G_EXC_ERROR;
1638 END IF;
1639 END IF;
1640 -- end 1554330
1641
1642 FOR l_curr_row IN 1..l_line_count LOOP
1643 X_Line_out_tbl(l_curr_row).return_status := FND_API.G_RET_STS_SUCCESS;
1644
1645 -- Progress Message
1646 --
1647 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1648 THEN
1649 --FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
1650 --FND_MESSAGE.Set_Token ('ROW', 'AS_LEAD_LINE', TRUE);
1651 --FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
1652 --FND_MSG_PUB.Add;
1653 IF l_debug THEN
1654 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1655 'Processing AS_LEAD_LINE row number '||l_curr_row );
1656 END IF;
1657
1658 END IF;
1659
1660 l_Line_rec := P_Line_Tbl(l_curr_row);
1661
1662
1663 -- Debug Message
1664 IF l_debug THEN
1665 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1666 'Private API: - Open Cursor to Select');
1667 END IF;
1668
1669 Open C_Get_opp_line( l_Line_rec.LEAD_LINE_ID);
1670
1671 Fetch C_Get_opp_line into l_last_update_date, l_line_amount_old;
1672
1673 If ( C_Get_opp_line%NOTFOUND) Then
1674 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1675 THEN
1676 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
1677 FND_MESSAGE.Set_Token ('INFO', 'opp_line', FALSE);
1678 FND_MSG_PUB.Add;
1679 END IF;
1680 raise FND_API.G_EXC_ERROR;
1681 END IF;
1682 -- Debug Message
1683 IF l_debug THEN
1684 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1685 'Private API: - Close Cursor');
1686 END IF;
1687
1688 Close C_Get_opp_line;
1689
1690 If (l_Line_rec.last_update_date is NULL or
1691 l_Line_rec.last_update_date = FND_API.G_MISS_Date ) Then
1692 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1693 THEN
1694 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1695 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
1696 FND_MSG_PUB.ADD;
1697 END IF;
1698 raise FND_API.G_EXC_ERROR;
1699 End if;
1700 -- Check Whether record has been changed by someone else
1701 If (l_Line_rec.last_update_date <> l_last_update_date)
1702 Then
1703 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1704 THEN
1705 FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
1706 FND_MESSAGE.Set_Token('INFO', 'opp_line', FALSE);
1707 FND_MSG_PUB.ADD;
1708 END IF;
1709 raise FND_API.G_EXC_ERROR;
1710 End if;
1711
1712 -- Debug message
1713 IF l_debug THEN
1714 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1715 'Private API: Validate_competitor');
1716 END IF;
1717
1718 -- Invoke validation procedures
1719 Validate_opp_line(
1720 p_init_msg_list => FND_API.G_FALSE,
1721 p_validation_level => p_validation_level,
1722 p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
1723 P_Line_Rec => l_Line_Rec,
1724 x_return_status => x_return_status,
1725 x_msg_count => x_msg_count,
1726 x_msg_data => x_msg_data);
1727
1728 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1729 -- Debug message
1730 IF l_debug THEN
1731 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1732 'Private API: Validate_opp_line fail');
1733 END IF;
1734 RAISE FND_API.G_EXC_ERROR;
1735 END IF;
1736
1737 -- Default forecast date for the purchase line
1738 IF (l_line_rec.FORECAST_DATE is NULL ) THEN
1739 --Fix for bug# 4111558
1740 IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'),'N') = 'Y' then
1741 l_line_rec.ROLLING_FORECAST_FLAG := 'N';
1742 else
1743 OPEN c_decision_date (l_line_rec.lead_id);
1744 FETCH c_decision_date INTO l_line_rec.FORECAST_DATE;
1745 CLOSE c_decision_date;
1746 l_line_rec.ROLLING_FORECAST_FLAG := 'Y';
1747 end if;
1748 ELSIF l_line_rec.FORECAST_DATE = FND_API.G_MISS_DATE THEN
1749 null;
1750 ELSE
1751 OPEN c_decision_date (l_line_rec.lead_id);
1752 FETCH c_decision_date INTO l_decision_date;
1753 CLOSE c_decision_date;
1754 --IF trunc(l_line_rec.FORECAST_DATE) <> trunc (l_decision_date) THEN
1755 l_line_rec.ROLLING_FORECAST_FLAG := 'N';
1756 --END IF;
1757 END IF;
1758
1759 -- Trunc forecast date
1760 l_line_rec.FORECAST_DATE := trunc(l_line_rec.FORECAST_DATE);
1761
1762 -- Added for MOAC bug 4747288
1763 IF l_line_rec.ORG_ID IS NULL THEN
1764 l_line_rec.ORG_ID := FND_API.G_MISS_NUM;
1765 END IF;
1766
1767
1768 -- Debug Message
1769 IF l_debug THEN
1770 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1771 'Private API: Calling update table handler');
1772 END IF;
1773
1774 -- Invoke table handler(AS_LEAD_LINES_PKG.Update_Row)
1775 AS_LEAD_LINES_PKG.Update_Row(
1776 p_LEAD_LINE_ID => l_Line_rec.LEAD_LINE_ID,
1777 p_LAST_UPDATE_DATE => SYSDATE,
1778 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1779 p_CREATION_DATE => FND_API.G_MISS_DATE,
1780 p_CREATED_BY => FND_API.G_MISS_NUM,
1781 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1782 p_REQUEST_ID => l_Line_rec.REQUEST_ID,
1783 p_PROGRAM_APPLICATION_ID => l_Line_rec.PROGRAM_APPLICATION_ID,
1784 p_PROGRAM_ID => l_Line_rec.PROGRAM_ID,
1785 p_PROGRAM_UPDATE_DATE => l_Line_rec.PROGRAM_UPDATE_DATE,
1786 p_LEAD_ID => l_Line_rec.LEAD_ID,
1787 p_INTEREST_TYPE_ID => l_Line_rec.INTEREST_TYPE_ID,
1788 p_PRIMARY_INTEREST_CODE_ID => l_Line_rec.PRIMARY_INTEREST_CODE_ID,
1789 p_SECONDARY_INTEREST_CODE_ID =>
1790 l_Line_rec.SECONDARY_INTEREST_CODE_ID,
1791 p_INTEREST_STATUS_CODE => l_Line_rec.INTEREST_STATUS_CODE,
1792 p_INVENTORY_ITEM_ID => l_Line_rec.INVENTORY_ITEM_ID,
1793 p_ORGANIZATION_ID => l_Line_rec.ORGANIZATION_ID,
1794 p_UOM_CODE => l_Line_rec.UOM_CODE,
1795 p_QUANTITY => l_Line_rec.QUANTITY,
1796 p_TOTAL_AMOUNT => l_Line_rec.TOTAL_AMOUNT,
1797 p_SALES_STAGE_ID => l_Line_rec.SALES_STAGE_ID,
1798 p_WIN_PROBABILITY => l_Line_rec.WIN_PROBABILITY,
1799 p_DECISION_DATE => l_Line_rec.DECISION_DATE,
1800 p_ORG_ID => l_Line_rec.ORG_ID,
1801 p_ATTRIBUTE_CATEGORY => l_Line_rec.ATTRIBUTE_CATEGORY,
1802 p_ATTRIBUTE1 => l_Line_rec.ATTRIBUTE1,
1803 p_ATTRIBUTE2 => l_Line_rec.ATTRIBUTE2,
1804 p_ATTRIBUTE3 => l_Line_rec.ATTRIBUTE3,
1805 p_ATTRIBUTE4 => l_Line_rec.ATTRIBUTE4,
1806 p_ATTRIBUTE5 => l_Line_rec.ATTRIBUTE5,
1807 p_ATTRIBUTE6 => l_Line_rec.ATTRIBUTE6,
1808 p_ATTRIBUTE7 => l_Line_rec.ATTRIBUTE7,
1809 p_ATTRIBUTE8 => l_Line_rec.ATTRIBUTE8,
1810 p_ATTRIBUTE9 => l_Line_rec.ATTRIBUTE9,
1811 p_ATTRIBUTE10 => l_Line_rec.ATTRIBUTE10,
1812 p_ATTRIBUTE11 => l_Line_rec.ATTRIBUTE11,
1813 p_ATTRIBUTE12 => l_Line_rec.ATTRIBUTE12,
1814 p_ATTRIBUTE13 => l_Line_rec.ATTRIBUTE13,
1815 p_ATTRIBUTE14 => l_Line_rec.ATTRIBUTE14,
1816 p_ATTRIBUTE15 => l_Line_rec.ATTRIBUTE15,
1817 p_STATUS_CODE => l_Line_rec.STATUS_CODE,
1818 p_CHANNEL_CODE => l_Line_rec.CHANNEL_CODE,
1819 p_QUOTED_LINE_FLAG => l_Line_rec.QUOTED_LINE_FLAG,
1820 p_PRICE => l_Line_rec.PRICE,
1821 p_PRICE_VOLUME_MARGIN => l_Line_rec.PRICE_VOLUME_MARGIN,
1822 p_SHIP_DATE => l_Line_rec.SHIP_DATE,
1823 p_FORECAST_DATE => l_Line_Rec.FORECAST_DATE,
1824 p_ROLLING_FORECAST_FLAG => l_Line_Rec.ROLLING_FORECAST_FLAG,
1825 p_SOURCE_PROMOTION_ID => l_Line_rec.SOURCE_PROMOTION_ID,
1826 p_OFFER_ID => l_Line_rec.OFFER_ID,
1827 p_PRODUCT_CATEGORY_ID => l_Line_Rec.PRODUCT_CATEGORY_ID,
1828 p_PRODUCT_CAT_SET_ID => l_Line_Rec.PRODUCT_CAT_SET_ID);
1829
1830 X_line_out_tbl(l_curr_row).LEAD_line_ID := l_line_rec.LEAD_line_ID;
1831 X_line_out_tbl(l_curr_row).return_status := x_return_status;
1832
1833 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1834 RAISE FND_API.G_EXC_ERROR;
1835 END IF;
1836
1837 -- Recalculate sales credits for the line
1838 Recalculate_Sales_Credits(
1839 p_lead_id => l_Line_rec.LEAD_ID,
1840 p_lead_line_id => l_line_rec.LEAD_line_ID,
1841 p_line_amount_old => l_line_amount_old,
1842 p_line_amount_new => l_Line_rec.TOTAL_AMOUNT,
1843 p_opp_worst_forecast_amount => l_Line_Rec.opp_worst_forecast_amount,
1844 p_opp_forecast_amount => l_Line_Rec.opp_forecast_amount,
1845 p_opp_best_forecast_amount => l_Line_Rec.opp_best_forecast_amount,
1846 x_return_status => x_return_status);
1847
1848 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1849 raise FND_API.G_EXC_ERROR;
1850 END IF;
1851
1852 END LOOP;
1853
1854 -- Back update total_amount in opp header
1855 Backupdate_Header(
1856 p_lead_id => p_header_rec.lead_id,
1857 x_return_status => x_return_status);
1858
1859 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1860 raise FND_API.G_EXC_ERROR;
1861 END IF;
1862
1863 -- Assign/Reassign the territory resources for the opportunity
1864
1865 -- Debug Message
1866 IF l_debug THEN
1867 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1868 'Calling Opportunity Real Time API');
1869 END IF;
1870
1871 AS_RTTAP_OPPTY.RTTAP_WRAPPER(
1872 P_Api_Version_Number => 1.0,
1873 P_Init_Msg_List => FND_API.G_FALSE,
1874 P_Commit => FND_API.G_FALSE,
1875 p_lead_id => p_line_tbl(1).LEAD_ID,
1876 X_Return_Status => x_return_status,
1877 X_Msg_Count => x_msg_count,
1878 X_Msg_Data => x_msg_data
1879 );
1880
1881 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1882 IF l_debug THEN
1883 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1884 'Opportunity Real Time API fail');
1885 END IF;
1886
1887 RAISE FND_API.G_EXC_ERROR;
1888 END IF;
1889
1890 --
1891 -- End of API body.
1892 --
1893
1894 -- Standard check for p_commit
1895 IF FND_API.to_Boolean( p_commit )
1896 THEN
1897 COMMIT WORK;
1898 END IF;
1899
1900
1901 -- Debug Message
1902 IF l_debug THEN
1903 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1904 'Private API: ' || l_api_name || ' end');
1905 END IF;
1906
1907
1908 -- Standard call to get message count and if count is 1, get message info.
1909 FND_MSG_PUB.Count_And_Get
1910 ( p_count => x_msg_count,
1911 p_data => x_msg_data
1912 );
1913
1914 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
1915 /*
1916 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is
1917 -- invoked for customization purpose
1918 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
1919 THEN
1920 AS_CALLOUT_PKG.Update_opp_lines_AU(
1921 p_api_version_number => 2.0,
1922 p_init_msg_list => FND_API.G_FALSE,
1923 p_commit => FND_API.G_FALSE,
1924 p_validation_level => p_validation_level,
1925 p_identity_salesforce_id => p_identity_salesforce_id,
1926 P_Line_Rec => P_Line_Rec,
1927 -- Hint: Add detail tables as parameter lists if it's master-detail
1928 -- relationship.
1929 x_return_status => x_return_status,
1930 x_msg_count => x_msg_count,
1931 x_msg_data => x_msg_data);
1932 END IF;
1933 */
1934 EXCEPTION
1935 WHEN FND_API.G_EXC_ERROR THEN
1936 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1937 P_MODULE => l_module
1938 ,P_API_NAME => L_API_NAME
1939 ,P_PKG_NAME => G_PKG_NAME
1940 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1941 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1942 ,X_MSG_COUNT => X_MSG_COUNT
1943 ,X_MSG_DATA => X_MSG_DATA
1944 ,X_RETURN_STATUS => X_RETURN_STATUS);
1945
1946 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1947 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1948 P_MODULE => l_module
1949 ,P_API_NAME => L_API_NAME
1950 ,P_PKG_NAME => G_PKG_NAME
1951 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1952 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1953 ,X_MSG_COUNT => X_MSG_COUNT
1954 ,X_MSG_DATA => X_MSG_DATA
1955 ,X_RETURN_STATUS => X_RETURN_STATUS);
1956
1957 WHEN OTHERS THEN
1958 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1959 P_MODULE => l_module
1960 ,P_API_NAME => L_API_NAME
1961 ,P_PKG_NAME => G_PKG_NAME
1962 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1963 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1964 ,X_MSG_COUNT => X_MSG_COUNT
1965 ,X_MSG_DATA => X_MSG_DATA
1966 ,X_RETURN_STATUS => X_RETURN_STATUS);
1967 End Update_opp_lines;
1968
1969
1970 -- Hint: Add corresponding delete detail table procedures if it's master-detail
1971 -- relationship.
1972 -- The Master delete procedure may not be needed depends on different business
1973 -- requirements.
1974 PROCEDURE Delete_opp_lines(
1975 P_Api_Version_Number IN NUMBER,
1976 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1977 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1978 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1979 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
1980 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
1981 P_Admin_Group_Id IN NUMBER,
1982 P_identity_salesforce_id IN NUMBER := NULL,
1983 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
1984 -- Suresh Mahalingam: Removed init to FND_API.G_MISS_NUM to fix GSCC warning
1985 P_Partner_Cont_Party_id IN NUMBER,
1986 P_Line_Tbl IN AS_OPPORTUNITY_PUB.Line_Tbl_Type,
1987 P_Header_Rec IN AS_OPPORTUNITY_PUB.Header_Rec_Type,
1988 X_LINE_OUT_TBL OUT NOCOPY AS_OPPORTUNITY_PUB.Line_out_Tbl_type,
1989 X_Return_Status OUT NOCOPY VARCHAR2,
1990 X_Msg_Count OUT NOCOPY NUMBER,
1991 X_Msg_Data OUT NOCOPY VARCHAR2
1992 )
1993
1994 IS
1995
1996 CURSOR C_sales_credits(c_lead_line_id NUMBER) IS
1997 SELECT sales_credit_id
1998 from as_sales_credits
1999 WHERE lead_line_id = c_lead_line_id;
2000
2001 CURSOR C_decision_factors(c_lead_line_id NUMBER) IS
2002 SELECT lead_decision_factor_id
2003 from as_lead_decision_factors
2004 WHERE lead_line_id = c_lead_line_id;
2005
2006 CURSOR C_competitor_products(c_lead_line_id NUMBER) IS
2007 SELECT lead_competitor_prod_id
2008 from as_lead_comp_products
2009 WHERE lead_line_id = c_lead_line_id;
2010
2011
2012 -- solin, for bug 1554330
2013 CURSOR c_get_opp_freeze_flag(c_LEAD_ID NUMBER) IS
2014 SELECT FREEZE_FLAG
2015 FROM AS_LEADS
2016 WHERE LEAD_ID = c_LEAD_ID;
2017
2018 l_api_name CONSTANT VARCHAR2(30) := 'Delete_opp_lines';
2019 l_api_version_number CONSTANT NUMBER := 2.0;
2020 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
2021 l_Line_Rec AS_OPPORTUNITY_PUB.Line_Rec_Type;
2022 l_LEAD_LINE_ID NUMBER;
2023 l_line_count CONSTANT NUMBER := P_Line_Tbl.count;
2024 l_update_access_flag VARCHAR2(1);
2025 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
2026
2027 l_freeze_flag VARCHAR2(1) := 'N'; -- solin, for bug 1554330
2028 l_allow_flag VARCHAR2(1); -- solin, for bug 1554330
2029
2030 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2031
2032 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Delete_opp_lines';
2033 BEGIN
2034 -- Standard Start of API savepoint
2035 SAVEPOINT DELETE_OPP_LINES_PVT;
2036
2037 -- Standard call to check for call compatibility.
2038 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2039 p_api_version_number,
2040 l_api_name,
2041 G_PKG_NAME)
2042 THEN
2043 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2044 END IF;
2045
2046
2047 -- Initialize message list if p_init_msg_list is set to TRUE.
2048 IF FND_API.to_Boolean( p_init_msg_list )
2049 THEN
2050 FND_MSG_PUB.initialize;
2051 END IF;
2052
2053
2054 -- Debug Message
2055 IF l_debug THEN
2056 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2057 'Private API: ' || l_api_name || ' start');
2058 END IF;
2059
2060
2061 -- Initialize API return status to SUCCESS
2062 x_return_status := FND_API.G_RET_STS_SUCCESS;
2063
2064 --
2065 -- Api body
2066 --
2067 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
2068 /*
2069 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is
2070 -- invoked for customization purpose
2071 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
2072 THEN
2073 AS_CALLOUT_PKG.Delete_opp_lines_BD(
2074 p_api_version_number => 2.0,
2075 p_init_msg_list => FND_API.G_FALSE,
2076 p_commit => FND_API.G_FALSE,
2077 p_validation_level => p_validation_level,
2078 p_identity_salesforce_id => p_identity_salesforce_id,
2079 P_Line_Rec => P_Line_Rec,
2080 -- Hint: Add detail tables as parameter lists if it's master-detail
2081 -- relationship.
2082 x_return_status => x_return_status,
2083 x_msg_count => x_msg_count,
2084 x_msg_data => x_msg_data);
2085 END IF;
2086 */
2087
2088 IF(P_Check_Access_Flag = 'Y') THEN
2089 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
2090 p_api_version_number => 2.0
2091 ,p_init_msg_list => p_init_msg_list
2092 ,p_salesforce_id => p_identity_salesforce_id
2093 ,p_admin_group_id => p_admin_group_id
2094 ,x_return_status => x_return_status
2095 ,x_msg_count => x_msg_count
2096 ,x_msg_data => x_msg_data
2097 ,x_sales_member_rec => l_identity_sales_member_rec);
2098
2099 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2100 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2101 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2102 'Private API: Get_CurrentUser fail');
2103 END IF;
2104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2105 END IF;
2106
2107 -- Call Get_Access_Profiles to get access_profile_rec
2108 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
2109 p_profile_tbl => p_profile_tbl,
2110 x_access_profile_rec => l_access_profile_rec);
2111
2112 AS_ACCESS_PUB.has_updateOpportunityAccess
2113 ( p_api_version_number => 2.0
2114 ,p_init_msg_list => p_init_msg_list
2115 ,p_validation_level => p_validation_level
2116 ,p_access_profile_rec => l_access_profile_rec
2117 ,p_admin_flag => p_admin_flag
2118 ,p_admin_group_id => p_admin_group_id
2119 ,p_person_id => l_identity_sales_member_rec.employee_person_id
2120 ,p_opportunity_id => p_line_tbl(1).LEAD_ID
2121 ,p_check_access_flag => p_check_access_flag
2122 ,p_identity_salesforce_id => p_identity_salesforce_id
2123 ,p_partner_cont_party_id => p_partner_cont_party_id
2124 ,x_return_status => x_return_status
2125 ,x_msg_count => x_msg_count
2126 ,x_msg_data => x_msg_data
2127 ,x_update_access_flag => l_update_access_flag );
2128
2129 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2130 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
2131 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2132 'has_updateOpportunityAccess fail');
2133 END IF;
2134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2135 END IF;
2136
2137 IF (l_update_access_flag <> 'Y') THEN
2138 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2139 FND_MESSAGE.Set_Name('AS', 'API_NO_UPDATE_PRIVILEGE');
2140 FND_MESSAGE.Set_Token('INFO', 'CUSTOMER_ID,OPPORTUNITY_ID,SALESFORCE_ID', FALSE);
2141 FND_MSG_PUB.ADD;
2142 END IF;
2143 RAISE FND_API.G_EXC_ERROR;
2144 END IF;
2145 END IF;
2146
2147 -- solin, for bug 1554330
2148 OPEN c_get_opp_freeze_flag(p_line_tbl(1).LEAD_ID);
2149 FETCH c_get_opp_freeze_flag INTO l_freeze_flag;
2150 CLOSE c_get_opp_freeze_flag;
2151
2152 IF l_freeze_flag = 'Y'
2153 THEN
2154 l_allow_flag := NVL(FND_PROFILE.VALUE('AS_ALLOW_UPDATE_FROZEN_OPP'),'Y');
2155 IF l_allow_flag <> 'Y' THEN
2156 AS_UTILITY_PVT.Set_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2157 'API_OPP_FROZEN');
2158 RAISE FND_API.G_EXC_ERROR;
2159 END IF;
2160 END IF;
2161 -- end 1554330
2162
2163
2164 FOR l_curr_row IN 1..l_line_count LOOP
2165 X_line_out_tbl(l_curr_row).return_status := FND_API.G_RET_STS_SUCCESS;
2166
2167 -- Progress Message
2168 --
2169 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2170 THEN
2171 --FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
2172 --FND_MESSAGE.Set_Token ('ROW', 'AS_LEAD_LINE', TRUE);
2173 --FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
2174 --FND_MSG_PUB.Add;
2175 IF l_debug THEN
2176 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2177 'Processing AS_LEAD_LINE row number '||l_curr_row );
2178 END IF;
2179
2180 END IF;
2181
2182 l_line_rec := P_Line_Tbl(l_curr_row);
2183
2184 -- Debug Message
2185 IF l_debug THEN
2186 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2187 'Private API: Calling delete table handler');
2188 END IF;
2189
2190 -- Invoke table handler(AS_LEAD_LINES_PKG.Delete_Row)
2191 AS_LEAD_LINES_PKG.Delete_Row(
2192 p_LEAD_LINE_ID => l_Line_rec.LEAD_LINE_ID);
2193
2194 -- Delete sales credits under this line
2195
2196 FOR sc_c IN C_sales_credits(l_Line_rec.LEAD_LINE_ID) LOOP
2197 AS_SALES_CREDITS_PKG.Delete_Row(
2198 p_SALES_CREDIT_ID => sc_c.sales_credit_id );
2199 END LOOP;
2200
2201 FOR df_c IN C_decision_factors(l_Line_rec.LEAD_LINE_ID) LOOP
2202 AS_LEAD_DECISION_FACTORS_PKG.Delete_Row(
2203 p_LEAD_DECISION_FACTOR_ID => df_c.lead_decision_factor_id );
2204 END LOOP;
2205
2206 FOR cp_c IN C_competitor_products(l_Line_rec.LEAD_LINE_ID) LOOP
2207 AS_LEAD_COMP_PRODUCTS_PKG.Delete_Row(
2208 p_LEAD_COMPETITOR_PROD_ID => cp_c.lead_competitor_prod_id );
2209 END LOOP;
2210
2211
2212 X_Line_out_tbl(l_curr_row).LEAD_LINE_ID := l_LEAD_LINE_ID;
2213 X_Line_out_tbl(l_curr_row).return_status := x_return_status;
2214
2215 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2216 RAISE FND_API.G_EXC_ERROR;
2217 END IF;
2218 END LOOP;
2219
2220 -- back update total_amount in opp header
2221 Backupdate_Header(
2222 p_lead_id => p_header_rec.lead_id,
2223 x_return_status => x_return_status);
2224
2225 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2226 raise FND_API.G_EXC_ERROR;
2227 END IF;
2228
2229 -- Assign/Reassign the territory resources for the opportunity
2230
2231 -- Debug Message
2232 IF l_debug THEN
2233 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2234 'Calling Opportunity Real Time API');
2235 END IF;
2236
2237 AS_RTTAP_OPPTY.RTTAP_WRAPPER(
2238 P_Api_Version_Number => 1.0,
2239 P_Init_Msg_List => FND_API.G_FALSE,
2240 P_Commit => FND_API.G_FALSE,
2241 p_lead_id => p_line_tbl(1).LEAD_ID,
2242 X_Return_Status => x_return_status,
2243 X_Msg_Count => x_msg_count,
2244 X_Msg_Data => x_msg_data
2245 );
2246
2247 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2248 IF l_debug THEN
2249 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2250 'Opportunity Real Time API fail');
2251 END IF;
2252 RAISE FND_API.G_EXC_ERROR;
2253 END IF;
2254
2255 --
2256 -- End of API body
2257 --
2258
2259 -- Standard check for p_commit
2260 IF FND_API.to_Boolean( p_commit )
2261 THEN
2262 COMMIT WORK;
2263 END IF;
2264
2265
2266 -- Debug Message
2267 IF l_debug THEN
2268 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
2269 'Private API: ' || l_api_name || ' end');
2270 END IF;
2271
2272
2273 -- Standard call to get message count and if count is 1, get message info.
2274 FND_MSG_PUB.Count_And_Get
2275 ( p_count => x_msg_count,
2276 p_data => x_msg_data
2277 );
2278
2279 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
2280 /*
2281 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
2282 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
2283 THEN
2284 AS_CALLOUT_PKG.Delete_opp_lines_AD(
2285 p_api_version_number => 2.0,
2286 p_init_msg_list => FND_API.G_FALSE,
2287 p_commit => FND_API.G_FALSE,
2288 p_validation_level => p_validation_level,
2289 p_identity_salesforce_id => p_identity_salesforce_id,
2290 P_Line_Rec => P_Line_Rec,
2291 -- Hint: Add detail tables as parameter lists if it's master-detail
2292 -- relationship.
2293 x_return_status => x_return_status,
2294 x_msg_count => x_msg_count,
2295 x_msg_data => x_msg_data);
2296 END IF;
2297 */
2298 EXCEPTION
2299 WHEN FND_API.G_EXC_ERROR THEN
2300 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2301 P_MODULE => l_module
2302 ,P_API_NAME => L_API_NAME
2303 ,P_PKG_NAME => G_PKG_NAME
2304 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2305 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2306 ,X_MSG_COUNT => X_MSG_COUNT
2307 ,X_MSG_DATA => X_MSG_DATA
2308 ,X_RETURN_STATUS => X_RETURN_STATUS);
2309
2310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2311 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2312 P_MODULE => l_module
2313 ,P_API_NAME => L_API_NAME
2314 ,P_PKG_NAME => G_PKG_NAME
2315 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2316 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2317 ,X_MSG_COUNT => X_MSG_COUNT
2318 ,X_MSG_DATA => X_MSG_DATA
2319 ,X_RETURN_STATUS => X_RETURN_STATUS);
2320
2321 WHEN OTHERS THEN
2322 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2323 P_MODULE => l_module
2324 ,P_API_NAME => L_API_NAME
2325 ,P_PKG_NAME => G_PKG_NAME
2326 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
2327 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2328 ,X_MSG_COUNT => X_MSG_COUNT
2329 ,X_MSG_DATA => X_MSG_DATA
2330 ,X_RETURN_STATUS => X_RETURN_STATUS);
2331 End Delete_opp_lines;
2332
2333
2334 -- Item-level validation procedures
2335 PROCEDURE Validate_LEAD_LINE_ID (
2336 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2337 P_Validation_mode IN VARCHAR2,
2338 P_LEAD_LINE_ID IN NUMBER,
2339 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
2340 X_Return_Status OUT NOCOPY VARCHAR2,
2341 X_Msg_Count OUT NOCOPY NUMBER,
2342 X_Msg_Data OUT NOCOPY VARCHAR2
2343 )
2344 IS
2345
2346 CURSOR C_Lead_Line_Id_Exists (c_Lead_Line_Id NUMBER) IS
2347 SELECT 'X'
2348 FROM as_lead_lines
2349 WHERE lead_line_id = c_Lead_Line_Id;
2350
2351 l_val VARCHAR2(1);
2352 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2353
2354 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Validate_LEAD_LINE_ID';
2355 BEGIN
2356
2357 -- Initialize message list if p_init_msg_list is set to TRUE.
2358 IF FND_API.to_Boolean( p_init_msg_list )
2359 THEN
2360 FND_MSG_PUB.initialize;
2361 END IF;
2362
2363
2364 -- Initialize API return status to SUCCESS
2365 x_return_status := FND_API.G_RET_STS_SUCCESS;
2366
2367
2368 -- Calling from Create API
2369 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
2370 THEN
2371 IF (p_LEAD_LINE_ID is NOT NULL) and (p_LEAD_LINE_ID <> FND_API.G_MISS_NUM)
2372 THEN
2373 OPEN C_Lead_Line_Id_Exists (p_Lead_Line_Id);
2374 FETCH C_Lead_Line_Id_Exists into l_val;
2375 IF C_Lead_Line_Id_Exists%FOUND THEN
2376 IF l_debug THEN
2377 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2378 'Private API: LEAD_LINE_ID exist');
2379 END IF;
2380 x_return_status := FND_API.G_RET_STS_ERROR;
2381 END IF;
2382 CLOSE C_Lead_Line_Id_Exists;
2383 END IF;
2384
2385 -- Calling from Update API
2386 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
2387 THEN
2388 -- validate NOT NULL column
2389 IF (p_LEAD_LINE_ID is NULL) or (p_LEAD_LINE_ID = FND_API.G_MISS_NUM)
2390 THEN
2391 IF l_debug THEN
2392 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2393 'Private API: Violate NOT NULL constraint(LEAD_LINE_ID)');
2394 END IF;
2395 x_return_status := FND_API.G_RET_STS_ERROR;
2396 ELSE
2397 OPEN C_Lead_Line_Id_Exists (p_Lead_Line_Id);
2398 FETCH C_Lead_Line_Id_Exists into l_val;
2399 IF C_Lead_Line_Id_Exists%NOTFOUND
2400 THEN
2401 IF l_debug THEN
2402 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2403 'Private API: LEAD_LINE_ID is not valid');
2404 END IF;
2405 x_return_status := FND_API.G_RET_STS_ERROR;
2406 END IF;
2407 CLOSE C_Lead_Line_Id_Exists;
2408 END IF;
2409
2410 END IF;
2411
2412 -- Standard call to get message count and if count is 1, get message info.
2413 FND_MSG_PUB.Count_And_Get
2414 ( p_count => x_msg_count,
2415 p_data => x_msg_data
2416 );
2417
2418 END Validate_LEAD_LINE_ID;
2419
2420
2421 PROCEDURE Validate_REQUEST_ID (
2422 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2423 P_Validation_mode IN VARCHAR2,
2424 P_REQUEST_ID IN NUMBER,
2425 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
2426 X_Return_Status OUT NOCOPY VARCHAR2,
2427 X_Msg_Count OUT NOCOPY NUMBER,
2428 X_Msg_Data OUT NOCOPY VARCHAR2
2429 )
2430 IS
2431 BEGIN
2432
2433 -- Initialize message list if p_init_msg_list is set to TRUE.
2434 IF FND_API.to_Boolean( p_init_msg_list )
2435 THEN
2436 FND_MSG_PUB.initialize;
2437 END IF;
2438
2439
2440 -- Initialize API return status to SUCCESS
2441 x_return_status := FND_API.G_RET_STS_SUCCESS;
2442
2443 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
2444 THEN
2445 -- Hint: Validate data
2446 -- IF p_REQUEST_ID is not NULL and p_REQUEST_ID <> G_MISS_CHAR
2447 -- verify if data is valid
2448 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
2449 NULL;
2450 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
2451 THEN
2452 -- Hint: Validate data
2453 -- IF p_REQUEST_ID <> G_MISS_CHAR
2454 -- verify if data is valid
2455 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
2456 NULL;
2457 END IF;
2458
2459 -- Standard call to get message count and if count is 1, get message info.
2460 FND_MSG_PUB.Count_And_Get
2461 ( p_count => x_msg_count,
2462 p_data => x_msg_data
2463 );
2464
2465 END Validate_REQUEST_ID;
2466
2467
2468 PROCEDURE Validate_LEAD_ID (
2469 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2470 P_Validation_mode IN VARCHAR2,
2471 P_LEAD_ID IN NUMBER,
2472 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
2473 X_Return_Status OUT NOCOPY VARCHAR2,
2474 X_Msg_Count OUT NOCOPY NUMBER,
2475 X_Msg_Data OUT NOCOPY VARCHAR2
2476 )
2477 IS
2478
2479 CURSOR C_Lead_Id_Exists (c_Lead_Id NUMBER) IS
2480 SELECT 'X'
2481 FROM as_leads
2482 WHERE lead_id = c_Lead_Id;
2483
2484 l_val VARCHAR2(1);
2485 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2486
2487 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Validate_LEAD_ID';
2488 BEGIN
2489
2490 -- Initialize message list if p_init_msg_list is set to TRUE.
2491 IF FND_API.to_Boolean( p_init_msg_list )
2492 THEN
2493 FND_MSG_PUB.initialize;
2494 END IF;
2495
2496
2497 -- Initialize API return status to SUCCESS
2498 x_return_status := FND_API.G_RET_STS_SUCCESS;
2499
2500
2501 IF (p_LEAD_ID is NULL) or (p_LEAD_ID = FND_API.G_MISS_NUM)
2502 THEN
2503 IF l_debug THEN
2504 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2505 'Private API: Violate NOT NULL constraint(LEAD_ID)');
2506 END IF;
2507 x_return_status := FND_API.G_RET_STS_ERROR;
2508 ELSE
2509 OPEN C_Lead_Id_Exists (p_Lead_Id);
2510 FETCH C_Lead_Id_Exists into l_val;
2511 IF C_Lead_Id_Exists%NOTFOUND
2512 THEN
2513 IF l_debug THEN
2514 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2515 'Private API: LEAD_ID is not valid');
2516 END IF;
2517 x_return_status := FND_API.G_RET_STS_ERROR;
2518 END IF;
2519 CLOSE C_Lead_Id_Exists;
2520 END IF;
2521
2522 -- Standard call to get message count and if count is 1, get message info.
2523 FND_MSG_PUB.Count_And_Get
2524 ( p_count => x_msg_count,
2525 p_data => x_msg_data
2526 );
2527
2528 END Validate_LEAD_ID;
2529
2530 /* commented by nkamble
2531 PROCEDURE Validate_INTEREST_TYPE_ID (
2532 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2533 P_Validation_mode IN VARCHAR2,
2534 P_INTEREST_TYPE_ID IN NUMBER,
2535 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
2536 X_Return_Status OUT NOCOPY VARCHAR2,
2537 X_Msg_Count OUT NOCOPY NUMBER,
2538 X_Msg_Data OUT NOCOPY VARCHAR2
2539 )
2540 IS
2541
2542 CURSOR C_INTEREST_TYPE_ID_Exists(c_interest_type_id NUMBER) IS
2543 SELECT 'X'
2544 FROM as_interest_types_all
2545 WHERE interest_type_id = c_interest_type_id;
2546
2547 l_val VARCHAR2(1);
2548 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2549
2550 BEGIN
2551
2552 -- Initialize message list if p_init_msg_list is set to TRUE.
2553 IF FND_API.to_Boolean( p_init_msg_list )
2554 THEN
2555 FND_MSG_PUB.initialize;
2556 END IF;
2557
2558
2559 -- Initialize API return status to SUCCESS
2560 x_return_status := FND_API.G_RET_STS_SUCCESS;
2561
2562 IF (p_INTEREST_TYPE_ID is NOT NULL) and
2563 (p_INTEREST_TYPE_ID <> FND_API.G_MISS_NUM)
2564 THEN
2565 OPEN C_INTEREST_TYPE_ID_Exists (p_INTEREST_TYPE_ID);
2566 FETCH C_INTEREST_TYPE_ID_Exists into l_val;
2567 IF C_INTEREST_TYPE_ID_Exists%NOTFOUND THEN
2568 IF l_debug THEN
2569 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
2570 'Private API: INTEREST_TYPE_ID is invalid');
2571 END IF;
2572 x_return_status := FND_API.G_RET_STS_ERROR;
2573 END IF;
2574 CLOSE C_INTEREST_TYPE_ID_Exists;
2575 END IF;
2576
2577 -- Standard call to get message count and if count is 1, get message info.
2578 FND_MSG_PUB.Count_And_Get
2579 ( p_count => x_msg_count,
2580 p_data => x_msg_data
2581 );
2582
2583 END Validate_INTEREST_TYPE_ID;*/
2584
2585 /* commented by nkamble
2586 PROCEDURE Validate_P_INTEREST_CODE_ID (
2587 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2588 P_Validation_mode IN VARCHAR2,
2589 P_PRIMARY_INTEREST_CODE_ID IN NUMBER,
2590 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
2591 X_Return_Status OUT NOCOPY VARCHAR2,
2592 X_Msg_Count OUT NOCOPY NUMBER,
2593 X_Msg_Data OUT NOCOPY VARCHAR2
2594 )
2595 IS
2596
2597 CURSOR C_P_INTEREST_CODE_ID_Exists(c_primary_interest_code_id NUMBER) IS
2598 SELECT 'X'
2599 FROM as_interest_codes_v
2600 WHERE interest_code_id = c_primary_interest_code_id;
2601
2602 l_val VARCHAR2(1);
2603 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2604
2605 BEGIN
2606
2607 -- Initialize message list if p_init_msg_list is set to TRUE.
2608 IF FND_API.to_Boolean( p_init_msg_list )
2609 THEN
2610 FND_MSG_PUB.initialize;
2611 END IF;
2612
2613
2614 -- Initialize API return status to SUCCESS
2615 x_return_status := FND_API.G_RET_STS_SUCCESS;
2616
2617 IF (p_PRIMARY_INTEREST_CODE_ID is NOT NULL) and
2618 (p_PRIMARY_INTEREST_CODE_ID <> FND_API.G_MISS_NUM)
2619 THEN
2620 OPEN C_P_INTEREST_CODE_ID_Exists (p_PRIMARY_INTEREST_CODE_ID);
2621 FETCH C_P_INTEREST_CODE_ID_Exists into l_val;
2622 IF C_P_INTEREST_CODE_ID_Exists%NOTFOUND THEN
2623 IF l_debug THEN
2624 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
2625 'Private API: PRIMARY_INTEREST_CODE_ID is invalid');
2626 END IF;
2627 x_return_status := FND_API.G_RET_STS_ERROR;
2628 END IF;
2629 CLOSE C_P_INTEREST_CODE_ID_Exists;
2630 END IF;
2631
2632 -- Standard call to get message count and if count is 1, get message info.
2633 FND_MSG_PUB.Count_And_Get
2634 ( p_count => x_msg_count,
2635 p_data => x_msg_data
2636 );
2637
2638 END Validate_P_INTEREST_CODE_ID;*/
2639
2640 /* commented by nkamble
2641 PROCEDURE Validate_S_INTEREST_CODE_ID (
2642 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2643 P_Validation_mode IN VARCHAR2,
2644 P_SECONDARY_INTEREST_CODE_ID IN NUMBER,
2645 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
2646 X_Return_Status OUT NOCOPY VARCHAR2,
2647 X_Msg_Count OUT NOCOPY NUMBER,
2648 X_Msg_Data OUT NOCOPY VARCHAR2
2649 )
2650 IS
2651
2652 CURSOR C_S_INTEREST_CODE_ID_Exists(C_S_INTEREST_code_id NUMBER) IS
2653 SELECT 'X'
2654 FROM as_interest_codes_v
2655 WHERE interest_code_id = C_S_INTEREST_code_id;
2656
2657 l_val VARCHAR2(1);
2658 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2659
2660 BEGIN
2661
2662 -- Initialize message list if p_init_msg_list is set to TRUE.
2663 IF FND_API.to_Boolean( p_init_msg_list )
2664 THEN
2665 FND_MSG_PUB.initialize;
2666 END IF;
2667
2668
2669 -- Initialize API return status to SUCCESS
2670 x_return_status := FND_API.G_RET_STS_SUCCESS;
2671
2672 IF (p_SECONDARY_INTEREST_CODE_ID is NOT NULL) and
2673 (p_SECONDARY_INTEREST_CODE_ID <> FND_API.G_MISS_NUM)
2674 THEN
2675 OPEN C_S_INTEREST_CODE_ID_Exists (p_SECONDARY_INTEREST_CODE_ID);
2676 FETCH C_S_INTEREST_CODE_ID_Exists into l_val;
2677 IF C_S_INTEREST_CODE_ID_Exists%NOTFOUND THEN
2678
2679 IF l_debug THEN
2680 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
2681 'Private API: SECONDARY_INTEREST_CODE_ID is invalid');
2682 END IF;
2683
2684 x_return_status := FND_API.G_RET_STS_ERROR;
2685 END IF;
2686 CLOSE C_S_INTEREST_CODE_ID_Exists;
2687 END IF;
2688
2689 -- Standard call to get message count and if count is 1, get message info.
2690 FND_MSG_PUB.Count_And_Get
2691 ( p_count => x_msg_count,
2692 p_data => x_msg_data
2693 );
2694
2695 END Validate_S_INTEREST_CODE_ID;*/
2696
2697 PROCEDURE Validate_PRODUCT_CATEGORY (
2698 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2699 P_Validation_mode IN VARCHAR2,
2700 P_CATEGORY_SET_ID IN NUMBER,
2701 P_CATEGORY_ID IN NUMBER,
2702 P_LEAD_LINE_ID IN NUMBER,
2703 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
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_GET_OLD_PROD_CAT_INFO(l_lead_line_id NUMBER) IS
2711 SELECT PRODUCT_CATEGORY_ID, PRODUCT_CAT_SET_ID
2712 FROM AS_LEAD_LINES_ALL
2713 WHERE LEAD_LINE_ID = l_lead_line_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_old_product_category_id NUMBER;
2718 l_old_product_cat_set_id NUMBER;
2719 l_return_status VARCHAR2(1);
2720 l_prod_cat_fields_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2721 l_validation_level VARCHAR2(1) := 'L';
2722 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Validate_PRODUCT_CATEGORY';
2723 BEGIN
2724
2725 -- Initialize message list if p_init_msg_list is set to TRUE.
2726 IF FND_API.to_Boolean( p_init_msg_list )
2727 THEN
2728 FND_MSG_PUB.initialize;
2729 END IF;
2730
2731
2732 -- Initialize API return status to SUCCESS
2733 l_return_status := FND_API.G_RET_STS_SUCCESS;
2734
2735 IF l_debug THEN
2736 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2737 'Private API: Validating product category '|| P_CATEGORY_SET_ID ||'+'||P_CATEGORY_ID);
2738 END IF;
2739
2740
2741 IF ((P_CATEGORY_ID is NULL)
2742 or (P_CATEGORY_ID = FND_API.G_MISS_NUM))
2743 THEN
2744 l_return_status := FND_API.G_RET_STS_ERROR;
2745
2746 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2747 THEN
2748 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
2749 FND_MESSAGE.Set_Token('COLUMN', 'PRODUCT_CATEGORY_ID', FALSE);
2750 FND_MSG_PUB.ADD;
2751 END IF;
2752 ELSIF ((P_CATEGORY_SET_ID is NULL)
2753 or (P_CATEGORY_SET_ID = FND_API.G_MISS_NUM))
2754 THEN
2755 l_return_status := FND_API.G_RET_STS_ERROR;
2756
2757 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2758 THEN
2759 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
2760 FND_MESSAGE.Set_Token('COLUMN', 'PRODUCT_CAT_SET_ID', FALSE);
2761 FND_MSG_PUB.ADD;
2762 END IF;
2763 ELSE
2764 -- Insure that all ids are valid
2765 --
2766
2767 OPEN C_GET_OLD_PROD_CAT_INFO ( P_LEAD_LINE_ID );
2768 Fetch C_GET_OLD_PROD_CAT_INFO INTO l_old_product_category_id, l_old_product_cat_set_id;
2769
2770 IF ((l_old_product_category_id is NOT NULL) and
2771 (l_old_product_cat_set_id is NOT NULL) and
2772 (l_old_product_category_id = P_CATEGORY_ID) and
2773 (l_old_product_cat_set_id = P_CATEGORY_SET_ID))
2774 THEN
2775 l_validation_level := 'L';
2776 ELSE
2777 l_validation_level := 'H';
2778 END IF;
2779
2780 Validate_Prod_Cat_Fields ( p_product_category_id => P_CATEGORY_ID,
2781 p_product_cat_set_id => P_CATEGORY_SET_ID,
2782 p_validation_level => l_validation_level,
2783 x_return_status => l_prod_cat_fields_status
2784 );
2785
2786 IF l_prod_cat_fields_status <> FND_API.G_RET_STS_SUCCESS
2787 THEN
2788 l_return_status := FND_API.G_RET_STS_ERROR;
2789 END IF;
2790 END IF;
2791
2792 -- Standard call to get message count and if count is 1, get message info.
2793 FND_MSG_PUB.Count_And_Get
2794 ( p_count => x_msg_count,
2795 p_data => x_msg_data
2796 );
2797
2798 x_return_status := l_return_status;
2799
2800 END Validate_PRODUCT_CATEGORY;
2801
2802 -- Procedure validates product category ids and returns SUCCESS if all ids are
2803 -- valid, ERROR otherwise
2804 -- Procedure assumes that at least the product category exists
2805 -- The validation level can have one of the two values 'L' or 'H'
2806 -- The validation level determines whether the validation will be low or high
2807 -- If the validation level is High, the procedure determines that the product
2808 -- category exists and is valid
2809 -- If the validation level is Low, the procedure only determines that the product
2810 -- category exists
2811 --
2812 PROCEDURE Validate_Prod_Cat_Fields ( p_product_category_id IN NUMBER,
2813 p_product_cat_set_id IN NUMBER,
2814 p_validation_level IN VARCHAR2 := 'L',
2815 x_return_status OUT NOCOPY VARCHAR2
2816 )
2817 Is
2818 CURSOR C_Prod_Cat_Exists (X_Product_Category_Id NUMBER, X_Product_Cat_Set_Id NUMBER) IS
2819 SELECT 'X'
2820 FROM ENI_PROD_DEN_HRCHY_PARENTS_V
2821 WHERE Category_Id = X_Product_Category_Id
2822 and Category_Set_Id = X_Product_Cat_Set_Id;
2823
2824
2825 CURSOR C_Prod_Cat_Exists_And_Valid (X_Product_Category_Id NUMBER, X_Product_Cat_Set_Id NUMBER) IS
2826 SELECT 'X'
2827 FROM ENI_PROD_DEN_HRCHY_PARENTS_V
2828 WHERE Category_Id = X_Product_Category_Id
2829 and Category_Set_Id = X_Product_Cat_Set_Id
2830 and Purchase_Interest = 'Y'
2831 and ((Disable_Date is null) or (Disable_Date > SYSDATE));
2832
2833 l_variable VARCHAR2(1);
2834 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2835 Begin
2836
2837 IF (p_validation_level = 'H')
2838 THEN
2839 OPEN C_Prod_Cat_Exists_And_Valid (p_product_category_id, p_product_cat_set_id);
2840 FETCH C_Prod_Cat_Exists_And_Valid INTO l_variable;
2841
2842 IF (C_Prod_Cat_Exists_And_Valid%NOTFOUND)
2843 THEN
2844 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
2845 THEN
2846 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
2847 FND_MESSAGE.Set_Token('COLUMN', 'PRODUCT_CATEGORY', FALSE);
2848 FND_MESSAGE.Set_Token('VALUE', p_product_category_id, FALSE);
2849 FND_MSG_PUB.Add;
2850 END IF;
2851
2852 l_return_status := FND_API.G_RET_STS_ERROR;
2853 END IF;
2854 CLOSE C_Prod_Cat_Exists_And_Valid;
2855 ELSE
2856 OPEN C_Prod_Cat_Exists (p_product_category_id, p_product_cat_set_id);
2857 FETCH C_Prod_Cat_Exists INTO l_variable;
2858
2859 IF (C_Prod_Cat_Exists%NOTFOUND)
2860 THEN
2861 IF FND_MSG_PUB.CHECK_MSG_LEVEL (FND_MSG_PUB.G_MSG_LVL_ERROR)
2862 THEN
2863 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
2864 FND_MESSAGE.Set_Token('COLUMN', 'PRODUCT_CATEGORY', FALSE);
2865 FND_MESSAGE.Set_Token('VALUE', p_product_category_id, FALSE);
2866 FND_MSG_PUB.Add;
2867 END IF;
2868
2869 l_return_status := FND_API.G_RET_STS_ERROR;
2870 END IF;
2871 CLOSE C_Prod_Cat_Exists;
2872 END IF;
2873
2874 x_return_status := l_return_status;
2875
2876 END Validate_Prod_Cat_Fields;
2877
2878 PROCEDURE Validate_INVENTORY_ITEM_ID (
2879 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2880 P_Validation_mode IN VARCHAR2,
2881 P_INVENTORY_ITEM_ID IN NUMBER,
2882 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
2883 X_Return_Status OUT NOCOPY VARCHAR2,
2884 X_Msg_Count OUT NOCOPY NUMBER,
2885 X_Msg_Data OUT NOCOPY VARCHAR2
2886 )
2887 IS
2888
2889 CURSOR C_INVENTORY_ITEM_ID_Exists(c_inventory_item_id NUMBER) IS
2890 SELECT 'X'
2891 FROM mtl_system_items
2892 WHERE inventory_item_id = c_inventory_item_id;
2893
2894 l_val VARCHAR2(1);
2895 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2896
2897 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Validate_INVENTORY_ITEM_ID';
2898 BEGIN
2899
2900 -- Initialize message list if p_init_msg_list is set to TRUE.
2901 IF FND_API.to_Boolean( p_init_msg_list )
2902 THEN
2903 FND_MSG_PUB.initialize;
2904 END IF;
2905
2906
2907 -- Initialize API return status to SUCCESS
2908 x_return_status := FND_API.G_RET_STS_SUCCESS;
2909
2910 IF (p_INVENTORY_ITEM_ID is NOT NULL) and
2911 (p_INVENTORY_ITEM_ID <> FND_API.G_MISS_NUM)
2912 THEN
2913 OPEN C_INVENTORY_ITEM_ID_Exists (p_INVENTORY_ITEM_ID);
2914 FETCH C_INVENTORY_ITEM_ID_Exists into l_val;
2915 IF C_INVENTORY_ITEM_ID_Exists%NOTFOUND THEN
2916
2917 IF l_debug THEN
2918 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
2919 'Private API: INVENTORY_ITEM_ID is invalid');
2920 END IF;
2921 x_return_status := FND_API.G_RET_STS_ERROR;
2922 END IF;
2923 CLOSE C_INVENTORY_ITEM_ID_Exists;
2924 END IF;
2925
2926 -- Standard call to get message count and if count is 1, get message info.
2927 FND_MSG_PUB.Count_And_Get
2928 ( p_count => x_msg_count,
2929 p_data => x_msg_data
2930 );
2931
2932 END Validate_INVENTORY_ITEM_ID;
2933
2934
2935 PROCEDURE Validate_ORGANIZATION_ID (
2936 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2937 P_Validation_mode IN VARCHAR2,
2938 P_ORGANIZATION_ID IN NUMBER,
2939 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
2940 X_Return_Status OUT NOCOPY VARCHAR2,
2941 X_Msg_Count OUT NOCOPY NUMBER,
2942 X_Msg_Data OUT NOCOPY VARCHAR2
2943 )
2944 IS
2945 BEGIN
2946
2947 -- Initialize message list if p_init_msg_list is set to TRUE.
2948 IF FND_API.to_Boolean( p_init_msg_list )
2949 THEN
2950 FND_MSG_PUB.initialize;
2951 END IF;
2952
2953
2954 -- Initialize API return status to SUCCESS
2955 x_return_status := FND_API.G_RET_STS_SUCCESS;
2956
2957 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
2958 THEN
2959 -- Hint: Validate data
2960 -- IF p_ORGANIZATION_ID is not NULL and p_ORGANIZATION_ID<>G_MISS_CHAR
2961 -- verify if data is valid
2962 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
2963 NULL;
2964 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
2965 THEN
2966 -- Hint: Validate data
2967 -- IF p_ORGANIZATION_ID <> G_MISS_CHAR
2968 -- verify if data is valid
2969 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
2970 NULL;
2971 END IF;
2972
2973 -- Standard call to get message count and if count is 1, get message info.
2974 FND_MSG_PUB.Count_And_Get
2975 ( p_count => x_msg_count,
2976 p_data => x_msg_data
2977 );
2978
2979 END Validate_ORGANIZATION_ID;
2980
2981
2982 PROCEDURE Validate_UOM_CODE (
2983 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2984 P_Validation_mode IN VARCHAR2,
2985 P_UOM_CODE IN VARCHAR2,
2986 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
2987 X_Return_Status OUT NOCOPY VARCHAR2,
2988 X_Msg_Count OUT NOCOPY NUMBER,
2989 X_Msg_Data OUT NOCOPY VARCHAR2
2990 )
2991 IS
2992
2993 CURSOR C_UOM_CODE_Exists(c_uom_code VARCHAR2) IS
2994 SELECT 'X'
2995 FROM mtl_units_of_measure
2996 WHERE uom_code = c_uom_code;
2997
2998 l_val VARCHAR2(1);
2999 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3000
3001 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Validate_UOM_CODE';
3002 BEGIN
3003
3004 -- Initialize message list if p_init_msg_list is set to TRUE.
3005 IF FND_API.to_Boolean( p_init_msg_list )
3006 THEN
3007 FND_MSG_PUB.initialize;
3008 END IF;
3009
3010 -- Initialize API return status to SUCCESS
3011 x_return_status := FND_API.G_RET_STS_SUCCESS;
3012
3013 IF (p_UOM_CODE is NOT NULL) and
3014 (p_UOM_CODE <> FND_API.G_MISS_CHAR)
3015 THEN
3016 OPEN C_UOM_CODE_Exists (p_UOM_CODE);
3017 FETCH C_UOM_CODE_Exists into l_val;
3018 IF C_UOM_CODE_Exists%NOTFOUND THEN
3019 IF l_debug THEN
3020 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
3021 'Private API: UOM_CODE is invalid');
3022 END IF;
3023
3024 x_return_status := FND_API.G_RET_STS_ERROR;
3025 END IF;
3026 CLOSE C_UOM_CODE_Exists;
3027 END IF;
3028
3029 -- Standard call to get message count and if count is 1, get message info.
3030 FND_MSG_PUB.Count_And_Get
3031 ( p_count => x_msg_count,
3032 p_data => x_msg_data
3033 );
3034
3035 END Validate_UOM_CODE;
3036
3037
3038 PROCEDURE Validate_QUANTITY (
3039 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3040 P_Validation_mode IN VARCHAR2,
3041 P_QUANTITY IN NUMBER,
3042 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
3043 X_Return_Status OUT NOCOPY VARCHAR2,
3044 X_Msg_Count OUT NOCOPY NUMBER,
3045 X_Msg_Data OUT NOCOPY VARCHAR2
3046 )
3047 IS
3048 BEGIN
3049
3050 -- Initialize message list if p_init_msg_list is set to TRUE.
3051 IF FND_API.to_Boolean( p_init_msg_list )
3052 THEN
3053 FND_MSG_PUB.initialize;
3054 END IF;
3055
3056
3057 -- Initialize API return status to SUCCESS
3058 x_return_status := FND_API.G_RET_STS_SUCCESS;
3059
3060 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
3061 THEN
3062 -- Hint: Validate data
3063 -- IF p_QUANTITY is not NULL and p_QUANTITY <> G_MISS_CHAR
3064 -- verify if data is valid
3065 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
3066 NULL;
3067 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
3068 THEN
3069 -- Hint: Validate data
3070 -- IF p_QUANTITY <> G_MISS_CHAR
3071 -- verify if data is valid
3072 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
3073 NULL;
3074 END IF;
3075
3076 -- Standard call to get message count and if count is 1, get message info.
3077 FND_MSG_PUB.Count_And_Get
3078 ( p_count => x_msg_count,
3079 p_data => x_msg_data
3080 );
3081
3082 END Validate_QUANTITY;
3083
3084
3085 PROCEDURE Validate_TOTAL_AMOUNT (
3086 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3087 P_Validation_mode IN VARCHAR2,
3088 P_TOTAL_AMOUNT IN NUMBER,
3089 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_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 BEGIN
3096
3097 -- Initialize message list if p_init_msg_list is set to TRUE.
3098 IF FND_API.to_Boolean( p_init_msg_list )
3099 THEN
3100 FND_MSG_PUB.initialize;
3101 END IF;
3102
3103
3104 -- Initialize API return status to SUCCESS
3105 x_return_status := FND_API.G_RET_STS_SUCCESS;
3106
3107 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
3108 THEN
3109 -- Hint: Validate data
3110 -- IF p_TOTAL_AMOUNT is not NULL and p_TOTAL_AMOUNT <> G_MISS_CHAR
3111 -- verify if data is valid
3112 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
3113 NULL;
3114 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
3115 THEN
3116 -- Hint: Validate data
3117 -- IF p_TOTAL_AMOUNT <> G_MISS_CHAR
3118 -- verify if data is valid
3119 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
3120 NULL;
3121 END IF;
3122
3123 -- Standard call to get message count and if count is 1, get message info.
3124 FND_MSG_PUB.Count_And_Get
3125 ( p_count => x_msg_count,
3126 p_data => x_msg_data
3127 );
3128
3129 END Validate_TOTAL_AMOUNT;
3130
3131
3132 PROCEDURE Validate_QUOTED_LINE_FLAG (
3133 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3134 P_Validation_mode IN VARCHAR2,
3135 P_QUOTED_LINE_FLAG IN VARCHAR2,
3136 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
3137 X_Return_Status OUT NOCOPY VARCHAR2,
3138 X_Msg_Count OUT NOCOPY NUMBER,
3139 X_Msg_Data OUT NOCOPY VARCHAR2
3140 )
3141 IS
3142
3143 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3144
3145 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Validate_QUOTED_LINE_FLAG';
3146 BEGIN
3147
3148 -- Initialize message list if p_init_msg_list is set to TRUE.
3149 IF FND_API.to_Boolean( p_init_msg_list )
3150 THEN
3151 FND_MSG_PUB.initialize;
3152 END IF;
3153
3154
3155 -- Initialize API return status to SUCCESS
3156 x_return_status := FND_API.G_RET_STS_SUCCESS;
3157
3158 IF (p_QUOTED_LINE_FLAG is NOT NULL) and
3159 (p_QUOTED_LINE_FLAG <> FND_API.G_MISS_CHAR)
3160 THEN
3161 IF (UPPER(p_QUOTED_LINE_FLAG) <> 'Y') and
3162 (UPPER(p_QUOTED_LINE_FLAG) <> 'N')
3163 THEN
3164 IF l_debug THEN
3165 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
3166 'Private API: QUOTED_LINE_FLAG is invalid');
3167 END IF;
3168
3169 x_return_status := FND_API.G_RET_STS_ERROR;
3170 END IF;
3171 END IF;
3172
3173 -- Standard call to get message count and if count is 1, get message info.
3174 FND_MSG_PUB.Count_And_Get
3175 ( p_count => x_msg_count,
3176 p_data => x_msg_data
3177 );
3178
3179 END Validate_QUOTED_LINE_FLAG;
3180
3181
3182 PROCEDURE Validate_PRICE (
3183 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3184 P_Validation_mode IN VARCHAR2,
3185 P_PRICE IN NUMBER,
3186 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
3187 X_Return_Status OUT NOCOPY VARCHAR2,
3188 X_Msg_Count OUT NOCOPY NUMBER,
3189 X_Msg_Data OUT NOCOPY VARCHAR2
3190 )
3191 IS
3192 BEGIN
3193
3194 -- Initialize message list if p_init_msg_list is set to TRUE.
3195 IF FND_API.to_Boolean( p_init_msg_list )
3196 THEN
3197 FND_MSG_PUB.initialize;
3198 END IF;
3199
3200
3201 -- Initialize API return status to SUCCESS
3202 x_return_status := FND_API.G_RET_STS_SUCCESS;
3203
3204 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
3205 THEN
3206 -- Hint: Validate data
3207 -- IF p_PRICE is not NULL and p_PRICE <> G_MISS_CHAR
3208 -- verify if data is valid
3209 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
3210 NULL;
3211 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
3212 THEN
3213 -- Hint: Validate data
3214 -- IF p_PRICE <> G_MISS_CHAR
3215 -- verify if data is valid
3216 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
3217 NULL;
3218 END IF;
3219
3220 -- Standard call to get message count and if count is 1, get message info.
3221 FND_MSG_PUB.Count_And_Get
3222 ( p_count => x_msg_count,
3223 p_data => x_msg_data
3224 );
3225
3226 END Validate_PRICE;
3227
3228
3229 PROCEDURE Validate_PRICE_VOLUME_MARGIN (
3230 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3231 P_Validation_mode IN VARCHAR2,
3232 P_PRICE_VOLUME_MARGIN IN NUMBER,
3233 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
3234 X_Return_Status OUT NOCOPY VARCHAR2,
3235 X_Msg_Count OUT NOCOPY NUMBER,
3236 X_Msg_Data OUT NOCOPY VARCHAR2
3237 )
3238 IS
3239 BEGIN
3240
3241 -- Initialize message list if p_init_msg_list is set to TRUE.
3242 IF FND_API.to_Boolean( p_init_msg_list )
3243 THEN
3244 FND_MSG_PUB.initialize;
3245 END IF;
3246
3247
3248 -- Initialize API return status to SUCCESS
3249 x_return_status := FND_API.G_RET_STS_SUCCESS;
3250
3251 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
3252 THEN
3253 -- Hint: Validate data
3254 -- IF p_PRICE_VOLUME_MARGIN is not NULL and p_PRICE_VOLUME_MARGIN <>
3255 -- G_MISS_CHAR, verify if data is valid
3256 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
3257 NULL;
3258 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
3259 THEN
3260 -- Hint: Validate data
3261 -- IF p_PRICE_VOLUME_MARGIN <> G_MISS_CHAR
3262 -- verify if data is valid
3263 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
3264 NULL;
3265 END IF;
3266
3267 -- Standard call to get message count and if count is 1, get message info.
3268 FND_MSG_PUB.Count_And_Get
3269 ( p_count => x_msg_count,
3270 p_data => x_msg_data
3271 );
3272
3273 END Validate_PRICE_VOLUME_MARGIN;
3274
3275
3276 PROCEDURE Validate_SHIP_DATE (
3277 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3278 P_Validation_mode IN VARCHAR2,
3279 P_SHIP_DATE IN DATE,
3280 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
3281 X_Return_Status OUT NOCOPY VARCHAR2,
3282 X_Msg_Count OUT NOCOPY NUMBER,
3283 X_Msg_Data OUT NOCOPY VARCHAR2
3284 )
3285 IS
3286 BEGIN
3287
3288 -- Initialize message list if p_init_msg_list is set to TRUE.
3289 IF FND_API.to_Boolean( p_init_msg_list )
3290 THEN
3291 FND_MSG_PUB.initialize;
3292 END IF;
3293
3294
3295 -- Initialize API return status to SUCCESS
3296 x_return_status := FND_API.G_RET_STS_SUCCESS;
3297
3298 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
3299 THEN
3300 -- Hint: Validate data
3301 -- IF p_SHIP_DATE is not NULL and p_SHIP_DATE <> G_MISS_CHAR
3302 -- verify if data is valid
3303 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
3304 NULL;
3305 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
3306 THEN
3307 -- Hint: Validate data
3308 -- IF p_SHIP_DATE <> G_MISS_CHAR
3309 -- verify if data is valid
3310 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
3311 NULL;
3312 END IF;
3313
3314 -- Standard call to get message count and if count is 1, get message info.
3315 FND_MSG_PUB.Count_And_Get
3316 ( p_count => x_msg_count,
3317 p_data => x_msg_data
3318 );
3319
3320 END Validate_SHIP_DATE;
3321
3322
3323 PROCEDURE Validate_O_OPPORTUNITY_LINE_ID (
3324 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3325 P_Validation_mode IN VARCHAR2,
3326 P_O_OPPORTUNITY_LINE_ID IN NUMBER,
3327 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
3328 X_Return_Status OUT NOCOPY VARCHAR2,
3329 X_Msg_Count OUT NOCOPY NUMBER,
3330 X_Msg_Data OUT NOCOPY VARCHAR2
3331 )
3332 IS
3333
3334 CURSOR C_O_OPPORTUNITY_LINE_ID_Exists(c_o_opportunity_line_id NUMBER) IS
3335 SELECT 'X'
3336 FROM as_lead_lines
3337 WHERE lead_line_id = c_o_opportunity_line_id;
3338
3339 l_val VARCHAR2(1);
3340 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3341
3342 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Validate_O_OPPORTUNITY_LINE_ID';
3343 BEGIN
3344
3345 -- Initialize message list if p_init_msg_list is set to TRUE.
3346 IF FND_API.to_Boolean( p_init_msg_list )
3347 THEN
3348 FND_MSG_PUB.initialize;
3349 END IF;
3350
3351
3352 -- Initialize API return status to SUCCESS
3353 x_return_status := FND_API.G_RET_STS_SUCCESS;
3354
3355 IF (p_O_OPPORTUNITY_LINE_ID is NOT NULL) and
3356 (p_O_OPPORTUNITY_LINE_ID <> FND_API.G_MISS_NUM)
3357 THEN
3358 OPEN C_O_OPPORTUNITY_LINE_ID_Exists (p_O_OPPORTUNITY_LINE_ID);
3359 FETCH C_O_OPPORTUNITY_LINE_ID_Exists into l_val;
3360 IF C_O_OPPORTUNITY_LINE_ID_Exists%NOTFOUND THEN
3361 IF l_debug THEN
3362 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
3363 'Private API: O_OPPORTUNITY_LINE_ID is invalid');
3364 END IF;
3365 x_return_status := FND_API.G_RET_STS_ERROR;
3366 END IF;
3367 CLOSE C_O_OPPORTUNITY_LINE_ID_Exists;
3368 END IF;
3369
3370 -- Standard call to get message count and if count is 1, get message info.
3371 FND_MSG_PUB.Count_And_Get
3372 ( p_count => x_msg_count,
3373 p_data => x_msg_data
3374 );
3375
3376 END Validate_O_OPPORTUNITY_LINE_ID;
3377
3378
3379 PROCEDURE Validate_SOURCE_PROMOTION_ID (
3380 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3381 P_Validation_mode IN VARCHAR2,
3382 P_SOURCE_PROMOTION_ID IN NUMBER,
3383 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
3384 X_Return_Status OUT NOCOPY VARCHAR2,
3385 X_Msg_Count OUT NOCOPY NUMBER,
3386 X_Msg_Data OUT NOCOPY VARCHAR2
3387 )
3388 IS
3389
3390 --CURSOR C_SOURCE_PROMOTION_ID_Exists (c_Source_Code_ID VARCHAR2) IS
3391 -- SELECT 'X'
3392 -- FROM ams_source_codes
3393 -- WHERE source_code_id = c_Source_Code_ID
3394 -- and active_flag = 'Y';
3395
3396 -- Jean changed here based on campaign LOV and offer LOV enhancement
3397
3398 CURSOR C_SOURCE_PROMOTION_ID_Exists (c_Source_Code_ID VARCHAR2) IS
3399 SELECT 'X'
3400 FROM ams_p_source_codes_v
3401 WHERE source_code_id = c_Source_Code_ID
3402 --AND status in ('ACTIVE', 'ONHOLD', 'COMPLETED')
3403 -- Fix for Bug 3093911 (Base Enh No: 2824485).
3404 -- Condition changed to include One Off Events.
3405 AND source_type <> 'OFFR';
3406
3407 l_val VARCHAR2(1);
3408 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3409
3410 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Validate_SOURCE_PROMOTION_ID';
3411 BEGIN
3412
3413 -- Initialize message list if p_init_msg_list is set to TRUE.
3414 IF FND_API.to_Boolean( p_init_msg_list )
3415 THEN
3416 FND_MSG_PUB.initialize;
3417 END IF;
3418
3419
3420 -- Initialize API return status to SUCCESS
3421 x_return_status := FND_API.G_RET_STS_SUCCESS;
3422
3423 IF (p_SOURCE_PROMOTION_ID is NOT NULL) and
3424 (p_SOURCE_PROMOTION_ID <> FND_API.G_MISS_NUM)
3425 THEN
3426 -- SOURCE_PROMOTION_ID should exist in ams_source_codes
3427 OPEN C_SOURCE_PROMOTION_ID_Exists (p_SOURCE_PROMOTION_ID);
3428 FETCH C_SOURCE_PROMOTION_ID_Exists into l_val;
3429 IF C_SOURCE_PROMOTION_ID_Exists%NOTFOUND THEN
3430 --AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
3431 -- 'Private API: SOURCE_PROMOTION_ID is invalid');
3432
3433 AS_UTILITY_PVT.Set_Message(
3434 p_module => l_module,
3435 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
3436 p_msg_name => 'API_INVALID_SOURCE_PROM_ID',
3437 p_token1 => 'VALUE',
3438 p_token1_value => p_SOURCE_PROMOTION_ID );
3439
3440 x_return_status := FND_API.G_RET_STS_ERROR;
3441 END IF;
3442 CLOSE C_SOURCE_PROMOTION_ID_Exists;
3443 END IF;
3444
3445 -- Standard call to get message count and if count is 1, get message info.
3446 FND_MSG_PUB.Count_And_Get
3447 ( p_count => x_msg_count,
3448 p_data => x_msg_data
3449 );
3450
3451 END Validate_SOURCE_PROMOTION_ID;
3452
3453
3454 PROCEDURE Validate_OFFER_ID (
3455 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3456 P_Validation_mode IN VARCHAR2,
3457 P_OFFER_ID IN NUMBER,
3458 X_Item_Property_Rec OUT NOCOPY AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE,
3459 X_Return_Status OUT NOCOPY VARCHAR2,
3460 X_Msg_Count OUT NOCOPY NUMBER,
3461 X_Msg_Data OUT NOCOPY VARCHAR2
3462 )
3463 IS
3464
3465 --CURSOR C_OFFER_ID_Exists (c_OFFER_ID VARCHAR2) IS
3466 -- SELECT 'X'
3467 -- FROM ams_act_offers
3468 -- WHERE activity_offer_id = c_OFFER_ID;
3469
3470 -- Jean changed here for offer, campaign enhancement
3471
3472 CURSOR C_OFFER_ID_Exists (c_OFFER_ID VARCHAR2) IS
3473 SELECT 'X'
3474 FROM ams_p_source_codes_v a
3475 WHERE a.source_type = 'OFFR'
3476 AND sysdate between nvl(a.start_date, sysdate-1)
3477 and nvl(a.end_date, sysdate+1)
3478 --AND a.status = 'ACTIVE'
3479 AND a.source_code_id = c_OFFER_ID;
3480
3481
3482 l_val VARCHAR2(1);
3483 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3484
3485 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Validate_OFFER_ID';
3486 BEGIN
3487
3488 -- Initialize message list if p_init_msg_list is set to TRUE.
3489 IF FND_API.to_Boolean( p_init_msg_list )
3490 THEN
3491 FND_MSG_PUB.initialize;
3492 END IF;
3493
3494
3495 -- Initialize API return status to SUCCESS
3496 x_return_status := FND_API.G_RET_STS_SUCCESS;
3497
3498 IF (p_OFFER_ID is NOT NULL) and
3499 (p_OFFER_ID <> FND_API.G_MISS_NUM)
3500 THEN
3501 -- OFFER_ID should exist in ams_source_codes
3502 OPEN C_OFFER_ID_Exists (p_OFFER_ID);
3503 FETCH C_OFFER_ID_Exists into l_val;
3504 IF C_OFFER_ID_Exists%NOTFOUND THEN
3505 IF l_debug THEN
3506 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
3507 'Private API: OFFER_ID is invalid');
3508 END IF;
3509 x_return_status := FND_API.G_RET_STS_ERROR;
3510 END IF;
3511 CLOSE C_OFFER_ID_Exists;
3512 END IF;
3513
3514 -- Standard call to get message count and if count is 1, get message info.
3515 FND_MSG_PUB.Count_And_Get
3516 ( p_count => x_msg_count,
3517 p_data => x_msg_data
3518 );
3519
3520 END Validate_OFFER_ID;
3521
3522
3523 -- Hint: inter-field level validation can be added here.
3524 -- Hint: If p_validation_mode = AS_UTILITY_PVT.G_VALIDATE_UPDATE, we should use
3525 -- cursor to get old values for all fields used in inter-field validation
3526 -- and set all G_MISS_XXX fields to original value stored in database
3527 -- table.
3528 PROCEDURE Validate_Line_rec(
3529 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3530 P_Validation_mode IN VARCHAR2,
3531 P_Line_Rec IN AS_OPPORTUNITY_PUB.Line_Rec_Type,
3532 X_Return_Status OUT NOCOPY VARCHAR2,
3533 X_Msg_Count OUT NOCOPY NUMBER,
3534 X_Msg_Data OUT NOCOPY VARCHAR2
3535 )
3536 IS
3537
3538 CURSOR C_Inventory_Item_Exists (c_Inventory_Item_Id NUMBER,
3539 c_Organization_Id NUMBER) IS
3540 SELECT 'X'
3541 FROM mtl_system_items
3542 WHERE inventory_item_id = c_Inventory_Item_Id
3543 and organization_id = c_Organization_Id;
3544
3545 CURSOR C_Category_Item_Exists ( c_product_category_id number,
3546 c_product_cat_set_id number,
3547 c_inventory_item_id number,
3548 c_organization_id number) IS
3549 select 'x'
3550 FROM
3551 MTL_ITEM_CATEGORIES MIC,
3552 MTL_SYSTEM_ITEMS_VL ITEMS,
3553 ENI_PROD_DEN_HRCHY_PARENTS_V P
3554 WHERE
3555 MIC.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND
3556 MIC.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND
3557 MIC.CATEGORY_ID = P.CATEGORY_ID AND
3558 MIC.CATEGORY_SET_ID = P.CATEGORY_SET_ID AND
3559 P.LANGUAGE = userenv('LANG') AND
3560 (P.DISABLE_DATE is null OR P.DISABLE_DATE > SYSDATE) AND
3561 P.PURCHASE_INTEREST = 'Y' AND
3562 MIC.CATEGORY_ID = c_product_category_id AND
3563 MIC.CATEGORY_SET_ID = c_product_cat_set_id AND
3564 MIC.INVENTORY_ITEM_ID = c_inventory_item_id AND
3565 MIC.ORGANIZATION_ID = c_organization_id;
3566
3567 l_val VARCHAR2(1);
3568 l_return_status VARCHAR2(1);
3569 l_api_name CONSTANT VARCHAR2(30) := 'Validate_line_rec';
3570 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3571
3572
3573 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Validate_Line_rec';
3574 BEGIN
3575
3576 -- Initialize message list if p_init_msg_list is set to TRUE.
3577 IF FND_API.to_Boolean( p_init_msg_list )
3578 THEN
3579 FND_MSG_PUB.initialize;
3580 END IF;
3581
3582 -- Debug Message
3583 IF l_debug THEN
3584 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3585 'Private API: ' || l_api_name || ' start');
3586
3587 END IF;
3588
3589 -- Initialize API return status to SUCCESS
3590 x_return_status := FND_API.G_RET_STS_SUCCESS;
3591
3592 -- Validate Inventory Item and Organization Id
3593 --
3594 IF p_line_rec.inventory_item_id is NOT NULL and
3595 p_line_rec.inventory_item_id <> FND_API.G_MISS_NUM and
3596 ( p_line_rec.organization_id is NULL or
3597 p_line_rec.organization_id = FND_API.G_MISS_NUM )
3598 THEN
3599 IF l_debug THEN
3600 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
3601 'Private API: ORGANIZATION_ID is missing');
3602
3603 END IF;
3604
3605 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3606 THEN
3607 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
3608 FND_MESSAGE.Set_Token('COLUMN', 'ORGANIZATION_ID', FALSE);
3609 FND_MSG_PUB.ADD;
3610 END IF;
3611
3612 x_return_status := FND_API.G_RET_STS_ERROR;
3613 ELSIF p_line_rec.inventory_item_id is NOT NULL and
3614 p_line_rec.inventory_item_id <> FND_API.G_MISS_NUM
3615 THEN
3616 -- Verify if inventory item exists
3617 OPEN C_Inventory_Item_Exists ( p_line_rec.inventory_item_id,
3618 p_line_rec.organization_id );
3619 FETCH C_Inventory_Item_Exists into l_val;
3620 IF C_Inventory_Item_Exists%NOTFOUND
3621 THEN
3622 IF l_debug THEN
3623 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
3624 'Private API: INVENTORY_ITEM_ID is invalid');
3625 END IF;
3626
3627 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3628 THEN
3629 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ID');
3630 FND_MESSAGE.Set_Token('COLUMN', 'INVENTORY_ITEM_ID', FALSE);
3631 FND_MESSAGE.Set_Token('VALUE', p_line_rec.inventory_item_id, FALSE);
3632 FND_MSG_PUB.ADD;
3633 END IF;
3634
3635 x_return_status := FND_API.G_RET_STS_ERROR;
3636 END IF;
3637 CLOSE C_Inventory_Item_Exists;
3638
3639 -- Verify if inventory item exists for selected category
3640 IF (x_return_status = FND_API.G_RET_STS_SUCCESS)
3641 THEN
3642 -- Jean add in 6/5 for the bug 1801521
3643 OPEN C_Category_Item_Exists ( p_line_rec.product_category_id,
3644 p_line_rec.product_cat_set_id,
3645 p_line_rec.inventory_item_id,
3646 p_line_rec.organization_id );
3647 FETCH C_Category_Item_Exists into l_val;
3648 IF C_Category_Item_Exists%NOTFOUND
3649 THEN
3650 IF l_debug THEN
3651 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
3652 'Private API: Inventory item doesnot match category');
3653 END IF;
3654
3655 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3656 THEN
3657 FND_MESSAGE.Set_Name('AS', 'API_INVALID_ITEM_CATEGORY');
3658 FND_MSG_PUB.ADD;
3659 END IF;
3660 x_return_status := FND_API.G_RET_STS_ERROR;
3661 END IF;
3662 CLOSE C_Category_Item_Exists;
3663 END IF;
3664 END IF;
3665
3666 -- Debug Message
3667 IF l_debug THEN
3668 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3669 'Private API: ' || l_api_name || ' end');
3670
3671 END IF;
3672
3673 -- Standard call to get message count and if count is 1, get message info.
3674 FND_MSG_PUB.Count_And_Get
3675 ( p_count => x_msg_count,
3676 p_data => x_msg_data
3677 );
3678
3679 END Validate_Line_Rec;
3680
3681
3682 PROCEDURE Validate_opp_line(
3683 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3684 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3685 P_Validation_mode IN VARCHAR2,
3686 P_Line_Rec IN AS_OPPORTUNITY_PUB.Line_Rec_Type,
3687 X_Return_Status OUT NOCOPY VARCHAR2,
3688 X_Msg_Count OUT NOCOPY NUMBER,
3689 X_Msg_Data OUT NOCOPY VARCHAR2
3690 )
3691 IS
3692 l_api_name CONSTANT VARCHAR2(30) := 'Validate_opp_line';
3693 x_item_property_rec AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE;
3694 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3695
3696 l_module CONSTANT VARCHAR2(255) := 'as.plsql.ldlpv.Validate_opp_line';
3697 BEGIN
3698
3699 -- Debug Message
3700 IF l_debug THEN
3701 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3702 'Private API: ' || l_api_name || ' start');
3703 END IF;
3704
3705
3706 -- Initialize API return status to SUCCESS
3707 x_return_status := FND_API.G_RET_STS_SUCCESS;
3708
3709 -- Added for MOAC
3710 -- Validate Inventory Item and Organization Id
3711 --
3712 IF p_line_rec.inventory_item_id is NOT NULL and
3713 p_line_rec.inventory_item_id <> FND_API.G_MISS_NUM and
3714 ( p_line_rec.organization_id is NULL or
3715 p_line_rec.organization_id = FND_API.G_MISS_NUM )
3716 THEN
3717 IF l_debug THEN
3718 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
3719 'Private API: ORGANIZATION_ID is missing');
3720
3721 END IF;
3722
3723 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
3724 THEN
3725 FND_MESSAGE.Set_Name('AS', 'AS_INV_ORG_NULL');
3726 FND_MSG_PUB.ADD;
3727 END IF;
3728
3729 x_return_status := FND_API.G_RET_STS_ERROR;
3730 raise FND_API.G_EXC_ERROR;
3731 END IF;
3732
3733 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM) THEN
3734 -- Hint: We provide validation procedure for every column. Developer
3735 -- should delete unnecessary validation procedures.
3736
3737 IF l_debug THEN
3738 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3739 'Private API: Validate Items start');
3740 END IF;
3741
3742 Validate_LEAD_LINE_ID(
3743 p_init_msg_list => FND_API.G_FALSE,
3744 p_validation_mode => p_validation_mode,
3745 p_LEAD_LINE_ID => P_Line_Rec.LEAD_LINE_ID,
3746 x_item_property_rec => x_item_property_rec,
3747 x_return_status => x_return_status,
3748 x_msg_count => x_msg_count,
3749 x_msg_data => x_msg_data);
3750 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3751 raise FND_API.G_EXC_ERROR;
3752 END IF;
3753 IF l_debug THEN
3754 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3755 'Private API: Validated LEAD_LINE_ID');
3756 END IF;
3757
3758 Validate_LEAD_ID(
3759 p_init_msg_list => FND_API.G_FALSE,
3760 p_validation_mode => p_validation_mode,
3761 p_LEAD_ID => P_Line_Rec.LEAD_ID,
3762 x_item_property_rec => x_item_property_rec,
3763 x_return_status => x_return_status,
3764 x_msg_count => x_msg_count,
3765 x_msg_data => x_msg_data);
3766 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3767 raise FND_API.G_EXC_ERROR;
3768 END IF;
3769 IF l_debug THEN
3770 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3771 'Private API: Validated LEAD_ID');
3772 END IF;
3773
3774
3775 /*
3776 Validate_INTEREST_TYPE_ID(
3777 p_init_msg_list => FND_API.G_FALSE,
3778 p_validation_mode => p_validation_mode,
3779 p_INTEREST_TYPE_ID => P_Line_Rec.INTEREST_TYPE_ID,
3780 x_item_property_rec => x_item_property_rec,
3781 x_return_status => x_return_status,
3782 x_msg_count => x_msg_count,
3783 x_msg_data => x_msg_data);
3784 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3785 raise FND_API.G_EXC_ERROR;
3786 END IF;
3787 IF l_debug THEN
3788 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3789 'Private API: Validated INTEREST_TYPE_ID');
3790 END IF;
3791 */
3792
3793 /*
3794 Validate_P_INTEREST_CODE_ID(
3795 p_init_msg_list => FND_API.G_FALSE,
3796 p_validation_mode => p_validation_mode,
3797 p_PRIMARY_INTEREST_CODE_ID => P_Line_Rec.PRIMARY_INTEREST_CODE_ID,
3798 x_item_property_rec => x_item_property_rec,
3799 x_return_status => x_return_status,
3800 x_msg_count => x_msg_count,
3801 x_msg_data => x_msg_data);
3802 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3803 raise FND_API.G_EXC_ERROR;
3804 END IF;
3805 IF l_debug THEN
3806 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3807 'Private API: Validated P_INTEREST_CODE_ID');
3808 END IF;
3809 */
3810
3811 /*
3812 Validate_S_INTEREST_CODE_ID(
3813 p_init_msg_list => FND_API.G_FALSE,
3814 p_validation_mode => p_validation_mode,
3815 p_SECONDARY_INTEREST_CODE_ID => P_Line_Rec.SECONDARY_INTEREST_CODE_ID,
3816 x_item_property_rec => x_item_property_rec,
3817 x_return_status => x_return_status,
3818 x_msg_count => x_msg_count,
3819 x_msg_data => x_msg_data);
3820 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3821 raise FND_API.G_EXC_ERROR;
3822 END IF;
3823
3824 IF l_debug THEN
3825 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3826 'Private API: Validated S_INTEREST_CODE_ID');
3827 END IF;
3828 */
3829 Validate_PRODUCT_CATEGORY(
3830 p_init_msg_list => FND_API.G_FALSE,
3831 p_validation_mode => p_validation_mode,
3832 P_CATEGORY_SET_ID => P_Line_Rec.product_cat_set_id,
3833 P_CATEGORY_ID => P_Line_Rec.product_category_id,
3834 P_LEAD_LINE_ID => P_Line_Rec.lead_line_id,
3835 x_item_property_rec => x_item_property_rec,
3836 x_return_status => x_return_status,
3837 x_msg_count => x_msg_count,
3838 x_msg_data => x_msg_data);
3839 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3840 raise FND_API.G_EXC_ERROR;
3841 END IF;
3842
3843 IF l_debug THEN
3844 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3845 'Private API: Validated PRODUCT_CATEGORY_ID');
3846 END IF;
3847
3848
3849 /* validated in record-level
3850 Validate_INVENTORY_ITEM_ID(
3851 p_init_msg_list => FND_API.G_FALSE,
3852 p_validation_mode => p_validation_mode,
3853 p_INVENTORY_ITEM_ID => P_Line_Rec.INVENTORY_ITEM_ID,
3854 x_item_property_rec => x_item_property_rec,
3855 x_return_status => x_return_status,
3856 x_msg_count => x_msg_count,
3857 x_msg_data => x_msg_data);
3858 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3859 raise FND_API.G_EXC_ERROR;
3860 END IF;
3861
3862 Validate_ORGANIZATION_ID(
3863 p_init_msg_list => FND_API.G_FALSE,
3864 p_validation_mode => p_validation_mode,
3865 p_ORGANIZATION_ID => P_Line_Rec.ORGANIZATION_ID,
3866 x_item_property_rec => x_item_property_rec,
3867 x_return_status => x_return_status,
3868 x_msg_count => x_msg_count,
3869 x_msg_data => x_msg_data);
3870 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3871 raise FND_API.G_EXC_ERROR;
3872 END IF;
3873 */
3874
3875 Validate_UOM_CODE(
3876 p_init_msg_list => FND_API.G_FALSE,
3877 p_validation_mode => p_validation_mode,
3878 p_UOM_CODE => P_Line_Rec.UOM_CODE,
3879 x_item_property_rec => x_item_property_rec,
3880 x_return_status => x_return_status,
3881 x_msg_count => x_msg_count,
3882 x_msg_data => x_msg_data);
3883
3884 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3885 raise FND_API.G_EXC_ERROR;
3886 END IF;
3887 IF l_debug THEN
3888 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3889 'Private API: Validated UOM_CODE');
3890 END IF;
3891
3892
3893
3894 /*
3895 Validate_QUANTITY(
3896 p_init_msg_list => FND_API.G_FALSE,
3897 p_validation_mode => p_validation_mode,
3898 p_QUANTITY => P_Line_Rec.QUANTITY,
3899 x_item_property_rec => x_item_property_rec,
3900 x_return_status => x_return_status,
3901 x_msg_count => x_msg_count,
3902 x_msg_data => x_msg_data);
3903 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3904 raise FND_API.G_EXC_ERROR;
3905 END IF;
3906
3907 Validate_TOTAL_AMOUNT(
3908 p_init_msg_list => FND_API.G_FALSE,
3909 p_validation_mode => p_validation_mode,
3910 p_TOTAL_AMOUNT => P_Line_Rec.TOTAL_AMOUNT,
3911 x_item_property_rec => x_item_property_rec,
3912 x_return_status => x_return_status,
3913 x_msg_count => x_msg_count,
3914 x_msg_data => x_msg_data);
3915 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3916 raise FND_API.G_EXC_ERROR;
3917 END IF;
3918 */
3919
3920 Validate_QUOTED_LINE_FLAG(
3921 p_init_msg_list => FND_API.G_FALSE,
3922 p_validation_mode => p_validation_mode,
3923 p_QUOTED_LINE_FLAG => P_Line_Rec.QUOTED_LINE_FLAG,
3924 x_item_property_rec => x_item_property_rec,
3925 x_return_status => x_return_status,
3926 x_msg_count => x_msg_count,
3927 x_msg_data => x_msg_data);
3928 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3929 raise FND_API.G_EXC_ERROR;
3930 END IF;
3931
3932 IF l_debug THEN
3933 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3934 'Private API: Validated QUOTED_LINE_FLAG');
3935 END IF;
3936
3937
3938
3939 /*
3940 Validate_PRICE(
3941 p_init_msg_list => FND_API.G_FALSE,
3942 p_validation_mode => p_validation_mode,
3943 p_PRICE => P_Line_Rec.PRICE,
3944 x_item_property_rec => x_item_property_rec,
3945 x_return_status => x_return_status,
3946 x_msg_count => x_msg_count,
3947 x_msg_data => x_msg_data);
3948 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3949 raise FND_API.G_EXC_ERROR;
3950 END IF;
3951
3952 Validate_PRICE_VOLUME_MARGIN(
3953 p_init_msg_list => FND_API.G_FALSE,
3954 p_validation_mode => p_validation_mode,
3955 p_PRICE_VOLUME_MARGIN => P_Line_Rec.PRICE_VOLUME_MARGIN,
3956 x_item_property_rec => x_item_property_rec,
3957 x_return_status => x_return_status,
3958 x_msg_count => x_msg_count,
3959 x_msg_data => x_msg_data);
3960 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3961 raise FND_API.G_EXC_ERROR;
3962 END IF;
3963
3964 Validate_SHIP_DATE(
3965 p_init_msg_list => FND_API.G_FALSE,
3966 p_validation_mode => p_validation_mode,
3967 p_SHIP_DATE => P_Line_Rec.SHIP_DATE,
3968 x_item_property_rec => x_item_property_rec,
3969 x_return_status => x_return_status,
3970 x_msg_count => x_msg_count,
3971 x_msg_data => x_msg_data);
3972 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3973 raise FND_API.G_EXC_ERROR;
3974 END IF;
3975 */
3976
3977 Validate_SOURCE_PROMOTION_ID(
3978 p_init_msg_list => FND_API.G_FALSE,
3979 p_validation_mode => p_validation_mode,
3980 p_SOURCE_PROMOTION_ID => P_Line_Rec.SOURCE_PROMOTION_ID,
3981 x_item_property_rec => x_item_property_rec,
3982 x_return_status => x_return_status,
3983 x_msg_count => x_msg_count,
3984 x_msg_data => x_msg_data);
3985 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3986 raise FND_API.G_EXC_ERROR;
3987 END IF;
3988 IF l_debug THEN
3989 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
3990 'Private API: Validated SOURCE_PROMOTION_ID');
3991 END IF;
3992
3993
3994
3995 Validate_OFFER_ID(
3996 p_init_msg_list => FND_API.G_FALSE,
3997 p_validation_mode => p_validation_mode,
3998 p_OFFER_ID => P_Line_Rec.OFFER_ID,
3999 x_item_property_rec => x_item_property_rec,
4000 x_return_status => x_return_status,
4001 x_msg_count => x_msg_count,
4002 x_msg_data => x_msg_data);
4003 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4004 raise FND_API.G_EXC_ERROR;
4005 END IF;
4006
4007 IF l_debug THEN
4008 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4009 'Private API: Validated OFFER_ID');
4010 END IF;
4011
4012
4013
4014 IF l_debug THEN
4015 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4016 'Private API: Validate Items end');
4017 END IF;
4018
4019 END IF;
4020
4021 -- Conditional Validation removed as part of MOAC bug 4747288
4022 -- IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_RECORD) THEN
4023 -- Hint: Inter-field level validation can be added here
4024 -- invoke record level validation procedures
4025 Validate_Line_Rec(
4026 p_init_msg_list => FND_API.G_FALSE,
4027 p_validation_mode => p_validation_mode,
4028 P_Line_Rec => P_Line_Rec,
4029 x_return_status => x_return_status,
4030 x_msg_count => x_msg_count,
4031 x_msg_data => x_msg_data);
4032
4033 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4034 raise FND_API.G_EXC_ERROR;
4035 END IF;
4036
4037 -- END IF;
4038
4039 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_RECORD) THEN
4040 -- invoke inter-record level validation procedures
4041 NULL;
4042 END IF;
4043
4044 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_ENTITY) THEN
4045 -- invoke inter-entity level validation procedures
4046 NULL;
4047 END IF;
4048
4049
4050 -- Debug Message
4051 IF l_debug THEN
4052 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
4053 'Private API: ' || l_api_name || ' end');
4054 END IF;
4055
4056 END Validate_opp_line;
4057
4058 End AS_OPP_LINE_PVT;