1 PACKAGE BODY AMS_ActProduct_PVT as
2 /*$Header: amsvprdb.pls 120.4 2006/05/17 00:17:47 inanaiah noship $*/
3 -- NAME
4 -- AMS_ActProduct_PVT
5 --
6 -- HISTORY
7 -- 1/1/2000 rvaka CREATED
8 --
9 G_PACKAGE_NAME CONSTANT VARCHAR2(30):='AMS_ActProduct_PVT';
10 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvprdb.pls';
11 G_module_name constant varchar2(100):='oracle.apps.ams.plsql.'||G_PACKAGE_NAME;
12
13 -- Debug mode
14 g_debug boolean := FALSE;
15 --g_debug boolean := TRUE;
16 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
17 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
18 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
19
20 AMS_LOG_PROCEDURE constant number := FND_LOG.LEVEL_PROCEDURE;
21 AMS_LOG_EXCEPTION constant Number := FND_LOG.LEVEL_EXCEPTION;
22 AMS_LOG_STATEMENT constant Number := FND_LOG.LEVEL_STATEMENT;
23
24 AMS_LOG_PROCEDURE_ON boolean := AMS_UTILITY_PVT.logging_enabled(AMS_LOG_PROCEDURE);
25 AMS_LOG_EXCEPTION_ON boolean := AMS_UTILITY_PVT.logging_enabled(AMS_LOG_EXCEPTION);
26 AMS_LOG_STATEMENT_ON boolean := AMS_UTILITY_PVT.logging_enabled(AMS_LOG_STATEMENT);
27
28
29 --
30 -- Procedure and function declarations.
31 /*****************************************************************************************/
32 -- Start of Comments
33 --
34 -- NAME
35 -- Create_Act_Product
36 --
37 -- PURPOSE
38 -- This procedure is to create a Product record that satisfy caller needs
39 --
40 -- HISTORY
41 -- 11/11/1999 rvaka created
42 -- 08/01/2000 sugupta added access code to prevent hacking
43 -- 04/03/2001 abhola call to AMS_ACCESS_PVT changed to check for return value N
44 -- 01-MAY-2001 julou modified, added 3 columns to ams_act_products
45 -- security_group_id, line_lumpsum_amount, line_lumpsum_qty
46 -- 03-May-2001 rssharma Added validation for Offers(prod)
47 -- 07-May-2001 rssharma changed the validation for offer
48 -- 18-Oct-2001 Musman Added the validation for the schedules.
49 -- 05-Nov-2001 musman Commented out the reference to security_group_id
50 -- 07-may-2002 abhola resolved bug # 2156368
51 -- 22-Oct-2002 Musman Added the validation for primary_product_flag
52 -- 11-Sep-2003 MUSMAN Added the validation reqd FOR modl object.
53 -- 10-Feb-2005 inanaiah Added the validation for category_id, category_set_id, inventory_id in Validate_Act_Product_Items.
54 -- 17-Mar-2005 mkothari Relaxed category_set_id validation for FUND - Bug 4241326
55 -- (also modified get_category_name and description functions)
56 -- 26-May-2005 musman Added schedule validation
57 -- 26-Sep-2005 musman Commenting out the validation for schedules.BUG:4634617 fix
58 -- 31-JAN-2006 inanaiah Bug 4956134 fix - sql id 14423554, 14423628
59 --
60 -- End of Comments
61
62 /*
63 --bug: 4634617 fix as per r12 requirement removing the validation
64 --PROCEDURE check_product_val_for_csch
65 --( p_act_Product_rec IN act_Product_rec_type,
66 -- x_return_status OUT NOCOPY VARCHAR2
67 --);
68 */
69
70
71 FUNCTION get_actual_unit(p_activity_product_id IN NUMBER)
72 RETURN NUMBER
73 IS
74
75 CURSOR c_actual_unit IS
76 SELECT NVL(SUM(scan_unit - scan_unit_remaining), 0)
77 FROM ozf_funds_utilized_all_b
78 WHERE activity_product_id = p_activity_product_id;
79
80 l_actual_unit NUMBER := 0;
81
82 BEGIN
83
84 OPEN c_actual_unit;
85 FETCH c_actual_unit INTO l_actual_unit;
86 CLOSE c_actual_unit;
87
88 RETURN l_actual_unit;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 RETURN 0;
93
94 END ;
95
96 PROCEDURE Create_Act_Product
97 ( p_api_version IN NUMBER,
98 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
99 p_commit IN VARCHAR2 := FND_API.G_FALSE,
100 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
101 x_return_status OUT NOCOPY VARCHAR2,
102 x_msg_count OUT NOCOPY NUMBER,
103 x_msg_data OUT NOCOPY VARCHAR2,
104 p_act_Product_rec IN act_Product_rec_type,
105 x_act_product_id OUT NOCOPY NUMBER
106 ) IS
107 l_api_name CONSTANT VARCHAR2(30) := 'Create_Act_Product';
108 l_api_version CONSTANT NUMBER := 1.0;
109 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME || '.' || l_api_name;
110 -- Status Local Variables
111 l_return_status VARCHAR2(1); -- Return value from procedures
112 l_act_Product_rec act_Product_rec_type := p_act_Product_rec;
113 l_act_product_id NUMBER;
114 l_user_id NUMBER;
115 l_res_id NUMBER;
116
117 CURSOR get_res_id(l_user_id IN NUMBER) IS
118 SELECT resource_id
119 FROM ams_jtf_rs_emp_v
120 WHERE user_id = l_user_id;
121
122 CURSOR C_act_product_id IS
123 SELECT ams_act_products_s.NEXTVAL
124 FROM dual;
125 BEGIN
126 -- Standard Start of API savepoint
127 SAVEPOINT Create_Act_Product_PVT;
128 -- Standard call to check for call compatibility.
129 IF NOT FND_API.Compatible_API_Call ( l_api_version,
130 p_api_version,
131 l_api_name,
132 G_PACKAGE_NAME)
133 THEN
134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135 END IF;
136 -- Initialize message list IF p_init_msg_list is set to TRUE.
137 IF FND_API.to_Boolean( p_init_msg_list )
138 THEN
139 FND_MSG_PUB.initialize;
140 END IF;
141 -- Initialize API return status to success
142 x_return_status := FND_API.G_RET_STS_SUCCESS;
143 --
144 -- API body
145 --
146 ----------------------- validate -----------------------
147 IF (AMS_DEBUG_HIGH_ON) THEN
148
149 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
150 END IF;
151 Validate_Act_Product
152 ( p_api_version => 1.0
153 ,p_init_msg_list => p_init_msg_list
154 ,p_validation_level => p_validation_level
155 ,x_return_status => l_return_status
156 ,x_msg_count => x_msg_count
157 ,x_msg_data => x_msg_data
158 ,p_act_Product_rec => l_act_Product_rec
159 );
160 -- If any errors happen abort API.
161 IF l_return_status = FND_API.G_RET_STS_ERROR
162 THEN
163 RAISE FND_API.G_EXC_ERROR;
164 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
165 THEN
166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
167 END IF;
168 --------------- CHECK ACCESS FOR THE USER-------------------
169 ----------added sugupta 07/25/2000
170 -- modified sugupta 09/05/2000 bug 1391106
171 -- Ownrship not checked for Messages screen - its a work around. Thsi IF loop should be
172 -- removed once Nari comesout with a better solution
173 -- Changed by rssharma as we will not require this validation for exclusion .. added prod to the list
174 IF l_act_Product_rec.arc_act_product_used_by NOT IN ('MESG','OFFR' , 'PROD') THEN
175 IF (AMS_DEBUG_HIGH_ON) THEN
176
177 AMS_Utility_PVT.debug_message(l_api_name||': check access');
178 END IF;
179 l_user_id := FND_GLOBAL.User_Id;
180 IF (AMS_DEBUG_HIGH_ON) THEN
181
182 AMS_Utility_PVT.debug_message(l_api_name||': check access- user id='||l_user_id);
183 END IF;
184 if l_user_id IS NOT NULL then
185 open get_res_id(l_user_id);
186 fetch get_res_id into l_res_id;
187 close get_res_id;
188 end if;
189 --
190 -- Changed access to check for value N
191 --
192 if AMS_ACCESS_PVT.check_update_access(l_act_Product_rec.act_product_used_by_id,l_act_Product_rec.arc_act_product_used_by, l_res_id, 'USER') = 'N' then
193 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
194 FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS'); --reusing message
195 FND_MSG_PUB.add;
196 END IF;
197 RAISE FND_API.g_exc_error;
198 end if;
199 END IF;
200 -------------------------------create---------------------------------
201 -- Get ID for activity product from sequence.
202 OPEN c_act_product_id;
203 FETCH c_act_product_id INTO l_act_Product_rec.activity_product_id;
204 CLOSE c_act_product_id;
205
206 INSERT INTO AMS_ACT_PRODUCTS
207 (
208 activity_product_id,
209 last_update_date,
210 last_updated_by,
211 creation_date,
212 created_by,
213 last_update_login,
214 object_version_number,
215 act_product_used_by_id,
216 arc_act_product_used_by,
217 inventory_item_id,
218 organization_id,
219 category_id,
220 category_set_id,
221 level_type_code,
222 product_sale_type,
223 primary_product_flag,
224 enabled_flag,
225 excluded_flag,
226 attribute_category,
227 attribute1,
228 attribute2,
229 attribute3,
230 attribute4,
231 attribute5,
232 attribute6,
233 attribute7,
234 attribute8,
235 attribute9,
236 attribute10,
237 attribute11,
238 attribute12,
239 attribute13,
240 attribute14,
241 attribute15,
242 --security_group_id,
243 line_lumpsum_amount,
244 line_lumpsum_qty,
245 channel_id,
246 uom_code,
247 quantity,
248 scan_value,
249 scan_unit_forecast,
250 adjustment_flag)
251 VALUES
252 (
253 l_act_Product_rec.activity_product_id,
254 -- standard who columns
255 sysdate,
256 FND_GLOBAL.User_Id,
257 sysdate,
258 FND_GLOBAL.User_Id,
259 FND_GLOBAL.Conc_Login_Id,
260 1, -- object_version_number
261 l_act_Product_rec.act_product_used_by_id,
262 l_act_Product_rec.arc_act_product_used_by,
263 l_act_Product_rec.inventory_item_id,
264 l_act_Product_rec.organization_id,
265 l_act_Product_rec.category_ID,
266 l_act_Product_rec.category_set_id,
267 l_act_Product_rec.level_type_code,
268 l_act_Product_rec.PRODUCT_SALE_TYPE,
269 nvl(l_act_Product_rec.PRIMARY_PRODUCT_FLAG,'N'),
270 nvl(l_act_Product_rec.ENABLED_FLAG,'Y'),
271 nvl(l_act_Product_rec.EXCLUDED_FLAG,'N'),
272 l_act_Product_rec.attribute_category,
273 l_act_Product_rec.attribute1,
274 l_act_Product_rec.attribute2,
275 l_act_Product_rec.attribute3,
276 l_act_Product_rec.attribute4,
277 l_act_Product_rec.attribute5,
278 l_act_Product_rec.attribute6,
279 l_act_Product_rec.attribute7,
280 l_act_Product_rec.attribute8,
281 l_act_Product_rec.attribute9,
282 l_act_Product_rec.attribute10,
283 l_act_Product_rec.attribute11,
284 l_act_Product_rec.attribute12,
285 l_act_Product_rec.attribute13,
286 l_act_Product_rec.attribute14,
287 l_act_Product_rec.attribute15,
288 --l_act_Product_rec.security_group_id,
289 l_act_Product_rec.line_lumpsum_amount,
290 l_act_Product_rec.line_lumpsum_qty,
291 l_act_Product_rec.channel_id,
292 DECODE(l_act_Product_rec.uom_code, NULL, 'Ea', FND_API.G_MISS_CHAR, 'Ea', l_act_Product_rec.uom_code),
293 DECODE(l_act_Product_rec.quantity, NULL, 1, FND_API.G_MISS_NUM, 1, l_act_Product_rec.quantity),
294 l_act_Product_rec.scan_value,
295 l_act_Product_rec.scan_unit_forecast,
296 l_act_Product_rec.adjustment_flag);
297 -- set OUT value
298 x_act_product_id := l_act_Product_rec.activity_product_id;
299
300 /*
301 -- added by sugupta on 07/11/2000
302 -- indicate proiduct has been defined for the entity
303 AMS_ObjectAttribute_PVT.modify_object_attribute(
304 p_api_version => l_api_version,
305 p_init_msg_list => FND_API.g_false,
306 p_commit => FND_API.g_false,
307 p_validation_level => FND_API.g_valid_level_full,
308
309 x_return_status => l_return_status,
310 x_msg_count => x_msg_count,
311 x_msg_data => x_msg_data,
312
313 p_object_type => l_act_Product_rec.arc_act_product_used_by,
314 p_object_id => l_act_Product_rec.act_product_used_by_id,
315 p_attr => 'PROD',
316 p_attr_defined_flag => 'Y'
317 );
318
319 IF l_return_status = FND_API.g_ret_sts_error THEN
320 RAISE FND_API.g_exc_error;
321 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
322 RAISE FND_API.g_exc_unexpected_error;
323 END IF;
324 */
325 --
326 -- END of API body.
327 --
328 -- Standard check of p_commit.
329 IF FND_API.To_Boolean ( p_commit )
330 THEN
331 COMMIT WORK;
332 END IF;
333 -- Standard call to get message count AND IF count is 1, get message info.
334 FND_MSG_PUB.Count_AND_Get
335 ( p_count => x_msg_count,
336 p_data => x_msg_data,
337 p_encoded => FND_API.G_FALSE
338 );
339 EXCEPTION
340 WHEN FND_API.G_EXC_ERROR THEN
341 ROLLBACK TO Create_Act_Product_PVT;
342 x_return_status := FND_API.G_RET_STS_ERROR ;
343 FND_MSG_PUB.Count_AND_Get
344 ( p_count => x_msg_count,
345 p_data => x_msg_data,
346 p_encoded => FND_API.G_FALSE
347 );
348 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
349 ROLLBACK TO Create_Act_Product_PVT;
350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
351 FND_MSG_PUB.Count_AND_Get
352 ( p_count => x_msg_count,
353 p_data => x_msg_data,
354 p_encoded => FND_API.G_FALSE
355 );
356 WHEN OTHERS THEN
357 IF (c_act_product_id%ISOPEN) THEN
358 CLOSE c_act_product_id;
359 END IF;
360 ROLLBACK TO Create_Act_Product_PVT;
361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
362 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
363 THEN
364 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
365 END IF;
366 FND_MSG_PUB.Count_AND_Get
367 ( p_count => x_msg_count,
368 p_data => x_msg_data,
369 p_encoded => FND_API.G_FALSE
370 );
371 END Create_Act_Product;
372 /*****************************************************************************************/
373 -- Start of Comments
374 --
375 -- NAME
376 -- Update_Act_Product
377 --
378 -- PURPOSE
379 -- This procedure is to update a Product record that satisfy caller needs
380 --
381 -- HISTORY
382 -- 11/11/1999 rvaka created
383 -- End of Comments
384 PROCEDURE Update_Act_Product
385 ( p_api_version IN NUMBER,
386 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
387 p_commit IN VARCHAR2 := FND_API.G_FALSE,
388 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
389 x_return_status OUT NOCOPY VARCHAR2,
390 x_msg_count OUT NOCOPY NUMBER,
391 x_msg_data OUT NOCOPY VARCHAR2,
392 p_act_Product_rec IN act_Product_rec_type
393 ) IS
394 l_api_name CONSTANT VARCHAR2(30) := 'Update_Act_Product';
395 l_api_version CONSTANT NUMBER := 1.0;
396 -- Status Local Variables
397 l_return_status VARCHAR2(1); -- Return value from procedures
398 l_act_Product_rec act_Product_rec_type;
399 ------
400 l_user_id NUMBER;
401 l_res_id NUMBER;
402
403 CURSOR get_res_id(l_user_id IN NUMBER) IS
404 SELECT resource_id
405 FROM ams_jtf_rs_emp_v
406 WHERE user_id = l_user_id;
407
408 BEGIN
409 -- Standard Start of API savepoint
410 SAVEPOINT Update_Act_Product_PVT;
411 -- Standard call to check for call compatibility.
412 IF NOT FND_API.Compatible_API_Call ( l_api_version,
413 p_api_version,
414 l_api_name,
415 G_PACKAGE_NAME)
416 THEN
417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
418 END IF;
419 -- Initialize message list IF p_init_msg_list is set to TRUE.
420 IF FND_API.to_Boolean( p_init_msg_list ) THEN
421 FND_MSG_PUB.initialize;
422 END IF;
423 -- Initialize API return status to success
424 x_return_status := FND_API.G_RET_STS_SUCCESS;
425 --
426 -- API body
427 --
428 complete_act_Product_rec(
429 p_act_Product_rec,
430 l_act_Product_rec
431 );
432
433 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item
434 THEN
435 Validate_Act_Product_Items
436 ( p_act_Product_rec => l_act_Product_rec,
437 p_validation_mode => JTF_PLSQL_API.g_update,
438 x_return_status => l_return_status
439 );
440 -- If any errors happen abort API.
441 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
442 THEN
443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
444 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
445 THEN
446 RAISE FND_API.G_EXC_ERROR;
447 END IF;
448 END IF;
449
450 --------------- CHECK ACCESS FOR THE USER-------------------
451 ----------added sugupta 07/25/2000
452 IF (AMS_DEBUG_HIGH_ON) THEN
453
454 AMS_Utility_PVT.debug_message(l_api_name||': check access');
455 END IF;
456 l_user_id := FND_GLOBAL.User_Id;
457 IF (AMS_DEBUG_HIGH_ON) THEN
458
459 AMS_Utility_PVT.debug_message(l_api_name||': check access- user id='||l_user_id);
460 END IF;
461 if l_user_id IS NOT NULL then
462 open get_res_id(l_user_id);
463 fetch get_res_id into l_res_id;
464 close get_res_id;
465 end if;
466 --
467 -- Changed Access call to check N instead of F
468 --
469 if l_act_Product_rec.arc_act_product_used_by NOT IN ('OFFR') THEN
470 if AMS_ACCESS_PVT.check_update_access(l_act_Product_rec.act_product_used_by_id,l_act_Product_rec.arc_act_product_used_by, l_res_id, 'USER') = 'N' then
471 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
472 FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS'); --reusing message
473 FND_MSG_PUB.add;
474 END IF;
475 RAISE FND_API.g_exc_error;
476 end if;
477 end if;
478 ---------------------------------update-----------------------------
479 -- Perform the database operation
480
481 update AMS_ACT_PRODUCTS
482 set
483 last_update_date = sysdate
484 ,last_updated_by = FND_GLOBAL.User_Id
485 ,last_update_login = FND_GLOBAL.Conc_Login_Id
486 ,object_version_number = l_act_Product_rec.object_version_number+1
487 ,act_product_used_by_id = l_act_Product_rec.act_product_used_by_id
488 ,arc_act_product_used_by = l_act_Product_rec.arc_act_product_used_by
489 ,organization_id = l_act_Product_rec.organization_id
490 ,inventory_item_id = l_act_Product_rec.inventory_item_id
491 ,category_id = l_act_Product_rec.category_id
492 ,category_set_id = l_act_Product_rec.category_set_id
493 ,level_type_code = l_act_Product_rec.level_type_code
494 ,product_sale_type = l_act_Product_rec.product_sale_type
495 ,primary_product_flag = l_act_Product_rec.primary_product_flag
496 ,enabled_flag = l_act_Product_rec.enabled_flag
497 ,excluded_flag = l_act_Product_rec.excluded_flag
498 ,attribute_category = l_act_Product_rec.attribute_category
499 ,attribute1 = l_act_Product_rec.attribute1
500 ,attribute2 = l_act_Product_rec.attribute2
501 ,attribute3 = l_act_Product_rec.attribute3
502 ,attribute4 = l_act_Product_rec.attribute4
503 ,attribute5 = l_act_Product_rec.attribute5
504 ,attribute6 = l_act_Product_rec.attribute6
505 ,attribute7 = l_act_Product_rec.attribute7
506 ,attribute8 = l_act_Product_rec.attribute8
507 ,attribute9 = l_act_Product_rec.attribute9
508 ,attribute10 = l_act_Product_rec.attribute10
509 ,attribute11 = l_act_Product_rec.attribute11
510 ,attribute12 = l_act_Product_rec.attribute12
511 ,attribute13 = l_act_Product_rec.attribute13
512 ,attribute14 = l_act_Product_rec.attribute14
513 ,attribute15 = l_act_Product_rec.attribute15
514 --,security_group_id = l_act_product_rec.security_group_id
515 ,line_lumpsum_amount = l_act_product_rec.line_lumpsum_amount
516 ,line_lumpsum_qty = l_act_product_rec.line_lumpsum_qty
517 ,channel_id = l_act_Product_rec.channel_id
518 ,uom_code = DECODE(l_act_Product_rec.uom_code, NULL, 'Ea', FND_API.G_MISS_CHAR, 'Ea', l_act_Product_rec.uom_code)
519 ,quantity = DECODE(l_act_Product_rec.quantity, NULL, 1, FND_API.G_MISS_NUM, 1, l_act_Product_rec.quantity)
520 ,scan_value = l_act_Product_rec.scan_value
521 ,scan_unit_forecast = l_act_Product_rec.scan_unit_forecast
522 ,adjustment_flag = l_act_Product_rec.adjustment_flag
523 WHERE activity_product_id = l_act_Product_rec.activity_product_id
524 AND object_version_number = l_act_Product_rec.object_version_number;
525 IF (SQL%NOTFOUND)
526 THEN
527 -- Error, check the msg level and added an error message to the
528 -- API message list
529 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
530 THEN -- MMSG
531 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
532 FND_MSG_PUB.Add;
533 END IF;
534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
535 END IF;
536 --
537 -- END of API body.
538 --
539 -- Standard check of p_commit.
540 IF FND_API.To_Boolean ( p_commit )
541 THEN
542 COMMIT WORK;
543 END IF;
544 -- Standard call to get message count AND IF count is 1, get message info.
545 FND_MSG_PUB.Count_AND_Get
546 ( p_count => x_msg_count,
547 p_data => x_msg_data,
548 p_encoded => FND_API.G_FALSE
549 );
550 EXCEPTION
551 WHEN FND_API.G_EXC_ERROR THEN
552 ROLLBACK TO Update_Act_Product_PVT;
553 x_return_status := FND_API.G_RET_STS_ERROR ;
554 FND_MSG_PUB.Count_AND_Get
555 ( p_count => x_msg_count,
556 p_data => x_msg_data,
557 p_encoded => FND_API.G_FALSE
558 );
559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
560 ROLLBACK TO Update_Act_Product_PVT;
561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
562 FND_MSG_PUB.Count_AND_Get
563 ( p_count => x_msg_count,
564 p_data => x_msg_data,
565 p_encoded => FND_API.G_FALSE
566 );
567 WHEN OTHERS THEN
568 ROLLBACK TO Update_Act_Product_PVT;
569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
570 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
571 THEN
572 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
573 END IF;
574 FND_MSG_PUB.Count_AND_Get
575 ( p_count => x_msg_count,
576 p_data => x_msg_data,
577 p_encoded => FND_API.G_FALSE
578 );
579 END Update_Act_Product;
580 /*****************************************************************************************/
581 -- Start of Comments
582 --
583 -- NAME
584 -- Delete_Act_Product
585 --
586 -- PURPOSE
587 -- This procedure is to delete a product record that satisfy caller needs
588 --
589 -- HISTORY
590 -- 11/11/1999 rvaka created
591 -- End of Comments
592 PROCEDURE Delete_Act_Product
593 ( p_api_version IN NUMBER,
594 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
595 p_commit IN VARCHAR2 := FND_API.G_FALSE,
596 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
597 x_return_status OUT NOCOPY VARCHAR2,
598 x_msg_count OUT NOCOPY NUMBER,
599 x_msg_data OUT NOCOPY VARCHAR2,
600 p_act_product_id IN NUMBER,
601 p_object_version IN NUMBER
602 ) IS
603 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Act_Product';
604 l_api_version CONSTANT NUMBER := 1.0;
605 -- Status Local Variables
606 l_return_status VARCHAR2(1); -- Return value from procedures
607 l_act_product_id NUMBER := p_act_product_id;
608 l_object_type VARCHAR2(30);
609 l_object_id NUMBER;
610 l_dummy VARCHAR2(100);
611 l_acc_obj VARCHAR2(30);
612 l_acc_obj_id NUMBER;
613 ------
614 l_user_id NUMBER;
615 l_res_id NUMBER;
616
617 cursor get_association_info(l_act_id IN NUMBER) is
618 SELECT distinct a.ARC_ACT_PRODUCT_USED_BY, a.ACT_PRODUCT_USED_BY_ID
619 FROM ams_act_products a, ams_act_products b
620 WHERE a.ARC_ACT_PRODUCT_USED_BY = b.ARC_ACT_PRODUCT_USED_BY
621 AND a.ACT_PRODUCT_USED_BY_ID = b.ACT_PRODUCT_USED_BY_ID
622 AND b.ACTIVITY_PRODUCT_ID = l_act_id;
623
624 cursor get_count(c_obj_type IN VARCHAR2, c_obj_id IN NUMBER) is
625 SELECT 'dummy'
626 FROM ams_act_products
627 WHERE ARC_ACT_PRODUCT_USED_BY = c_obj_type
628 AND ACT_PRODUCT_USED_BY_ID = c_obj_id;
629
630 CURSOR get_res_id(l_user_id IN NUMBER) IS
631 SELECT resource_id
632 FROM ams_jtf_rs_emp_v
633 WHERE user_id = l_user_id;
634
635 CURSOR get_obj_info(l_actprd_id IN NUMBER) IS
636 SELECT arc_act_product_used_by, act_product_used_by_id
637 FROM ams_act_products
638 WHERE activity_product_id = l_actprd_id;
639
640 BEGIN
641 -- Standard Start of API savepoint
642 SAVEPOINT Delete_Act_Product_PVT;
643 -- Standard call to check for call compatibility.
644 IF NOT FND_API.Compatible_API_Call ( l_api_version,
645 p_api_version,
646 l_api_name,
647 G_PACKAGE_NAME)
648 THEN
649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
650 END IF;
651 -- Initialize message list IF p_init_msg_list is set to TRUE.
652 IF FND_API.to_Boolean( p_init_msg_list )
653 THEN
654 FND_MSG_PUB.initialize;
655 END IF;
656 -- Initialize API return status to success
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658 --
659 -- API body
660 --
661 --------------- CHECK ACCESS FOR THE USER-------------------
662 ----------added sugupta 07/25/2000
663 l_user_id := FND_GLOBAL.User_Id;
664 IF (AMS_DEBUG_HIGH_ON) THEN
665
666 AMS_Utility_PVT.debug_message(l_api_name||': check access- user id='||l_user_id);
667 END IF;
668 if l_user_id IS NOT NULL then
669 open get_res_id(l_user_id);
670 fetch get_res_id into l_res_id;
671 close get_res_id;
672 end if;
673
674 open get_obj_info(p_act_product_id);
675 fetch get_obj_info into l_acc_obj, l_acc_obj_id;
676 close get_obj_info;
677
678 -- Commented out the following call to check_update_access
679 -- as it does not work for Messages tab. GDEODHAR : Oct 06, 2000
680 /*
681 if AMS_ACCESS_PVT.check_update_access(l_acc_obj_id, l_acc_obj, l_res_id, 'USER') <> 'F' then
682 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
683 FND_MESSAGE.set_name('AMS', 'AMS_EVO_NO_UPDATE_ACCESS'); --reusing message
684 FND_MSG_PUB.add;
685 END IF;
686 RAISE FND_API.g_exc_error;
687 end if;
688 */
689 -- End of commented part. GDEODHAR : Oct 06, 2000.
690 ---------------------delete-------------------------------------
691 OPEN get_association_info(l_act_product_id);
692 FETCH get_association_info into l_object_type, l_object_id;
693 close get_association_info;
694
695 -- Perform the database operation
696 -- Delete header data
697 DELETE FROM AMS_ACT_PRODUCTS
698 WHERE activity_product_id = l_act_product_id
699 and object_version_number = p_object_version;
700 IF SQL%NOTFOUND THEN
701 --
702 -- Add error message to API message list.
703 --
704 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
705 THEN
706 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
707 FND_MSG_PUB.add;
708 END IF;
709 RAISE FND_API.G_EXC_ERROR;
710 END IF;
711
712 /*
713 ----- Modify Object Attribute ---------------
714
715 OPEN get_count(l_object_type,l_object_id);
716 FETCH get_count into l_dummy;
717
718 if (get_count%NOTFOUND) then
719
720 -- need to make a call to update ams_objec_attributes that no information
721 -- exist for this combination of master obj type and id and using object type
722 -- and set attribute defined flag to N
723
724 AMS_ObjectAttribute_PVT.modify_object_attribute(
725 p_api_version => l_api_version,
726 p_init_msg_list => FND_API.g_false,
727 p_commit => FND_API.g_false,
728 p_validation_level => FND_API.g_valid_level_full,
729
730 x_return_status => x_return_status,
731 x_msg_count => x_msg_count,
732 x_msg_data => x_msg_data,
733
734 p_object_type => l_object_type,
735 p_object_id => l_object_id,
736 p_attr => 'PROD',
737 p_attr_defined_flag => 'N'
738 );
739 IF x_return_status = FND_API.g_ret_sts_error THEN
740 RAISE FND_API.g_exc_error;
741 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
742 RAISE FND_API.g_exc_unexpected_error;
743 END IF;
744
745 end if;
746
747 close get_count;
748 */
749
750 --
751 -- END of API body.
752 --
753 -- Standard check of p_commit.
754 IF FND_API.To_Boolean ( p_commit )
755 THEN
756 COMMIT WORK;
757 END IF;
758 -- Standard call to get message count AND IF count is 1, get message info.
759 FND_MSG_PUB.Count_AND_Get
760 ( p_count => x_msg_count,
761 p_data => x_msg_data,
762 p_encoded => FND_API.G_FALSE
763 );
764 EXCEPTION
765 WHEN FND_API.G_EXC_ERROR THEN
766 ROLLBACK TO Delete_Act_Product_PVT;
767 x_return_status := FND_API.G_RET_STS_ERROR ;
768 FND_MSG_PUB.Count_AND_Get
769 ( p_count => x_msg_count,
770 p_data => x_msg_data,
771 p_encoded => FND_API.G_FALSE
772 );
773 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
774 ROLLBACK TO Delete_Act_Product_PVT;
775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
776 FND_MSG_PUB.Count_AND_Get
777 ( p_count => x_msg_count,
778 p_data => x_msg_data,
779 p_encoded => FND_API.G_FALSE
780 );
781 WHEN OTHERS THEN
782 IF (get_association_info%ISOPEN) THEN
783 CLOSE get_association_info;
784 END IF;
785 IF (get_count%ISOPEN) THEN
786 CLOSE get_count;
787 END IF;
788 ROLLBACK TO Delete_Act_Product_PVT;
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
790 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
791 THEN
792 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
793 END IF;
794 FND_MSG_PUB.Count_AND_Get
795 ( p_count => x_msg_count,
796 p_data => x_msg_data,
797 p_encoded => FND_API.G_FALSE
798 );
799 END Delete_Act_Product;
800 /*****************************************************************************************/
801 -- Start of Comments
802 --
803 -- NAME
804 -- Lock_Act_product
805 --
806 -- PURPOSE
807 -- This procedure is to lock a product record that satisfy caller needs
808 --
809 -- HISTORY
810 -- 11/11/1999 rvaka created
811 -- End of Comments
812 PROCEDURE Lock_Act_product
813 ( p_api_version IN NUMBER,
814 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
815 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
816 x_return_status OUT NOCOPY VARCHAR2,
817 x_msg_count OUT NOCOPY NUMBER,
818 x_msg_data OUT NOCOPY VARCHAR2,
819 p_act_product_id IN NUMBER,
820 p_object_version IN NUMBER
821 ) IS
822 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Act_Product';
823 l_api_version CONSTANT NUMBER := 1.0;
824 -- Status Local Variables
825 l_return_status VARCHAR2(1); -- Return value from procedures
826 l_act_product_id NUMBER;
827 CURSOR c_act_product IS
828 SELECT activity_product_id
829 FROM AMS_ACT_PRODUCTS
830 WHERE activity_product_id = p_act_product_id
831 AND object_version_number = p_object_version
832 FOR UPDATE of activity_product_id NOWAIT;
833 BEGIN
834 -- Standard call to check for call compatibility.
835 IF NOT FND_API.Compatible_API_Call ( l_api_version,
836 p_api_version,
837 l_api_name,
838 G_PACKAGE_NAME)
839 THEN
840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
841 END IF;
842 -- Initialize message list if p_init_msg_list is set to TRUE.
843 IF FND_API.to_Boolean( p_init_msg_list )
844 THEN
845 FND_MSG_PUB.initialize;
846 END IF;
847 -- Initialize API return status to success
848 x_return_status := FND_API.G_RET_STS_SUCCESS;
849 --
850 -- API body
851 --
852 -- Perform the database operation
853 OPEN c_act_product;
854 FETCH c_act_product INTO l_act_product_id;
855 IF (c_act_product%NOTFOUND) THEN
856 CLOSE c_act_product;
857 -- Error, check the msg level and added an error message to the
858 -- API message list
859 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
860 THEN -- MMSG
861 FND_MESSAGE.Set_Name('AMS', 'AMS_API_RECORD_NOT_FOUND');
862 FND_MSG_PUB.Add;
863 END IF;
864 RAISE FND_API.G_EXC_ERROR;
865 END IF;
866 CLOSE c_act_product;
867 --
868 -- END of API body.
869 --
870 -- Standard call to get message count AND IF count is 1, get message info.
871 FND_MSG_PUB.Count_AND_Get
872 ( p_count => x_msg_count,
873 p_data => x_msg_data,
874 p_encoded => FND_API.G_FALSE
875 );
876 EXCEPTION
877 WHEN FND_API.G_EXC_ERROR THEN
878 x_return_status := FND_API.G_RET_STS_ERROR ;
879 FND_MSG_PUB.Count_AND_Get
880 ( p_count => x_msg_count,
881 p_data => x_msg_data,
882 p_encoded => FND_API.G_FALSE
883 );
884 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
886 FND_MSG_PUB.Count_AND_Get
887 ( p_count => x_msg_count,
888 p_data => x_msg_data,
889 p_encoded => FND_API.G_FALSE
890 );
891 WHEN AMS_Utility_PVT.resource_locked THEN
892 x_return_status := FND_API.g_ret_sts_error;
893 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
894 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
895 FND_MSG_PUB.add;
896 END IF;
897 FND_MSG_PUB.Count_AND_Get
898 ( p_count => x_msg_count,
899 p_data => x_msg_data,
900 p_encoded => FND_API.G_FALSE
901 );
902 WHEN OTHERS THEN
903 IF (c_act_product%ISOPEN) THEN
904 CLOSE c_act_product;
905 END IF;
906 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
907 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
908 THEN
909 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
910 END IF;
911 FND_MSG_PUB.Count_AND_Get
912 ( p_count => x_msg_count,
913 p_data => x_msg_data,
914 p_encoded => FND_API.G_FALSE
915 );
916 END Lock_Act_Product;
917 /*****************************************************************************************/
918 -- Start of Comments
919 --
920 -- NAME
921 -- Validate_Act_Product
922 --
923 -- PURPOSE
924 -- This procedure is to validate an activity product record
925 --
926 -- HISTORY
927 -- 1/1/2000 rvaka created
928 -- End of Comments
929 PROCEDURE Validate_Act_Product
930 ( p_api_version IN NUMBER,
931 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
932 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
933 x_return_status OUT NOCOPY VARCHAR2,
934 x_msg_count OUT NOCOPY NUMBER,
935 x_msg_data OUT NOCOPY VARCHAR2,
936 p_act_Product_rec IN act_Product_rec_type
937 ) IS
938 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Act_Product';
939 l_api_version CONSTANT NUMBER := 1.0;
940 l_full_name CONSTANT VARCHAR2(60) := G_PACKAGE_NAME || '.' || l_api_name;
941 -- Status Local Variables
942 l_return_status VARCHAR2(1); -- Return value from procedures
943 l_act_Product_rec act_Product_rec_type := p_act_Product_rec;
944 l_default_act_product_rec act_Product_rec_type;
945 l_act_product_id NUMBER;
946 BEGIN
947 -- Standard call to check for call compatibility.
948 IF NOT FND_API.Compatible_API_Call ( l_api_version,
949 p_api_version,
950 l_api_name,
951 G_PACKAGE_NAME)
952 THEN
953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
954 END IF;
955 -- Initialize message list if p_init_msg_list is set to TRUE.
956 IF FND_API.to_Boolean( p_init_msg_list ) THEN
957 FND_MSG_PUB.initialize;
958 END IF;
959 -- Initialize API return status to success
960 x_return_status := FND_API.G_RET_STS_SUCCESS;
961 --
962 -- API body
963 --
964 IF (AMS_DEBUG_HIGH_ON) THEN
965
966 AMS_Utility_PVT.debug_message(l_full_name||': check items');
967 END IF;
968 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item
969 THEN
970 Validate_Act_Product_Items
971 ( p_act_Product_rec => l_act_Product_rec,
972 p_validation_mode => JTF_PLSQL_API.g_create,
973 x_return_status => l_return_status
974 );
975 -- If any errors happen abort API.
976 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
977 THEN
978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
979 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
980 THEN
981 RAISE FND_API.G_EXC_ERROR;
982 END IF;
983 END IF;
984 -- Perform cross attribute validation and missing attribute checks. Record
985 -- level validation.
986 IF (AMS_DEBUG_HIGH_ON) THEN
987
988 AMS_Utility_PVT.debug_message(l_full_name||': check record level');
989 END IF;
990 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record
991 THEN
992 Validate_Act_product_Record(
993 p_act_Product_rec => l_act_Product_rec,
994 x_return_status => l_return_status
995 );
996 -- If any errors happen abort API.
997 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
998 RAISE FND_API.G_EXC_ERROR;
999 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1000 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1001 END IF;
1002 END IF;
1003 --
1004 -- END of API body.
1005 --
1006 -------------------- finish --------------------------
1007 FND_MSG_PUB.count_and_get(
1008 p_encoded => FND_API.g_false,
1009 p_count => x_msg_count,
1010 p_data => x_msg_data
1011 );
1012 EXCEPTION
1013 WHEN FND_API.G_EXC_ERROR THEN
1014 x_return_status := FND_API.G_RET_STS_ERROR ;
1015 FND_MSG_PUB.Count_AND_Get
1016 ( p_count => x_msg_count,
1017 p_data => x_msg_data,
1018 p_encoded => FND_API.G_FALSE
1019 );
1020 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1021 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1022 FND_MSG_PUB.Count_AND_Get
1023 ( p_count => x_msg_count,
1024 p_data => x_msg_data,
1025 p_encoded => FND_API.G_FALSE
1026 );
1027 WHEN OTHERS THEN
1028 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1029 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1030 THEN
1031 FND_MSG_PUB.Add_Exc_Msg( G_PACKAGE_NAME,l_api_name);
1032 END IF;
1033 FND_MSG_PUB.Count_AND_Get
1034 ( p_count => x_msg_count,
1035 p_data => x_msg_data,
1036 p_encoded => FND_API.G_FALSE
1037 );
1038 END Validate_Act_Product;
1039
1040 -------------------------------------------------------------------------------------------
1041
1042 PROCEDURE check_primary_flag
1043 (
1044 p_act_Product_rec IN act_Product_rec_type,
1045 x_return_status OUT NOCOPY VARCHAR2
1046 )IS
1047
1048
1049
1050 CURSOR get_primary_flag(p_category_set_id IN NUMBER
1051 ,p_act_product_used_by_id IN NUMBER
1052 ,p_arc_act_product_used_by IN VARCHAR2)
1053 IS
1054 SELECT distinct primary_product_flag
1055 FROM ams_act_products
1056 WHERE category_set_id = p_category_set_id
1057 AND act_product_used_by_id = p_act_product_used_by_id
1058 AND arc_act_product_used_by = p_arc_act_product_used_by
1059 AND primary_product_flag = 'Y';
1060
1061
1062 l_primary_flag VARCHAR2(1) := 'N';
1063
1064 BEGIN
1065
1066 x_return_status := FND_API.g_ret_sts_success;
1067
1068 IF (AMS_DEBUG_HIGH_ON) THEN
1069
1070
1071
1072 AMS_UTILITY_PVT.debug_message('check_primary_flag: '|| p_act_product_rec.primary_product_flag);
1073
1074 END IF;
1075
1076 IF p_act_product_rec.primary_product_flag <> FND_API.G_MISS_CHAR
1077 AND p_act_product_rec.primary_product_flag = 'Y'
1078 THEN
1079 OPEN get_primary_flag(p_act_product_rec.category_set_id
1080 ,p_act_product_rec.act_product_used_by_id
1081 ,p_act_product_rec.arc_act_product_used_by);
1082 FETCH get_primary_flag INTO l_primary_flag;
1083 CLOSE get_primary_flag;
1084
1085 IF (AMS_DEBUG_HIGH_ON) THEN
1086
1087
1088
1089 AMS_UTILITY_PVT.debug_message('l_primary_flag: '|| l_primary_flag);
1090
1091 END IF;
1092
1093 IF l_primary_flag = 'Y'
1094 THEN
1095 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1096 THEN
1097 FND_MESSAGE.set_name('AMS', 'AMS_CANNOT_UPD_PRIMARY_FLAG');
1098 FND_MSG_PUB.add;
1099 END IF;
1100 x_return_status := FND_API.g_ret_sts_error;
1101 RETURN;
1102 END IF;
1103 END IF;
1104
1105 IF (AMS_DEBUG_HIGH_ON) THEN
1106
1107
1108
1109 AMS_UTILITY_PVT.debug_message('check_primary_flag is checked with no errors');
1110
1111 END IF;
1112
1113 END check_primary_flag;
1114
1115
1116
1117 /*****************************************************************************************/
1118 -- Start of Comments
1119 --
1120 -- NAME
1121 -- Validate_Act_Product_Items
1122 --
1123 -- PURPOSE
1124 -- This procedure is to validate product items
1125 -- End of Comments
1126 PROCEDURE Validate_Act_Product_Items
1127 ( p_act_Product_rec IN act_Product_rec_type,
1128 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1129 x_return_status OUT NOCOPY VARCHAR2
1130 ) IS
1131 l_table_name VARCHAR2(30);
1132 l_pk_name VARCHAR2(30);
1133 l_pk_value VARCHAR2(30);
1134 l_level VARCHAR2(150) := null;
1135
1136 CURSOR c_include_level IS
1137 select level_type_code
1138 from AMS_ACT_PRODUCTS
1139 where ARC_ACT_PRODUCT_USED_BY = p_act_Product_rec.ARC_ACT_PRODUCT_USED_BY
1140 and ACT_PRODUCT_USED_BY_ID = p_act_Product_rec.ACT_PRODUCT_USED_BY_ID;
1141
1142 CURSOR c_get_budget_type(l_fund_id IN NUMBER) IS
1143 select 'Y'
1144 from ozf_funds_all_b
1145 where fund_id = l_fund_id
1146 and fund_type = 'FULLY_ACCRUED'
1147 and accrual_discount_level = 'ORDER' ;
1148
1149 CURSOR c_offer_type IS
1150 SELECT OFFER_TYPE, custom_setup_id
1151 FROM ams_offers
1152 WHERE qp_list_header_id = p_act_Product_rec.act_product_used_by_id;
1153
1154 l_budget_flag VARCHAR2(1);
1155 l_offer_type VARCHAR2(30);
1156 l_custom_setup_id NUMBER;
1157
1158 BEGIN
1159 -- Initialize API/Procedure return status to success
1160 x_return_status := FND_API.G_RET_STS_SUCCESS;
1161 -- Check required parameters
1162 IF (p_act_Product_rec.act_product_used_by_id = FND_API.G_MISS_NUM OR
1163 p_act_Product_rec.act_product_used_by_id IS NULL)
1164 THEN
1165 -- missing required fields
1166 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1167 THEN -- MMSG
1168 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_NO_USEDBYID');
1169 FND_MSG_PUB.add;
1170 END IF;
1171 x_return_status := FND_API.G_RET_STS_ERROR;
1172 -- If any error happens abort API.
1173 RETURN;
1174 END IF;
1175
1176 -- arc_act_product_used_by
1177 IF (p_act_Product_rec.arc_act_product_used_by = FND_API.G_MISS_CHAR OR
1178 p_act_Product_rec.arc_act_product_used_by IS NULL)
1179 THEN
1180 -- missing required fields
1181 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1182 THEN -- MMSG
1183 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_NO_USEDBY');
1184 FND_MSG_PUB.add;
1185 END IF;
1186 x_return_status := FND_API.G_RET_STS_ERROR;
1187 -- If any error happens abort API.
1188 RETURN;
1189 END IF;
1190
1191 check_primary_flag(p_act_Product_rec,x_return_status);
1192 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1193 THEN
1194 RETURN;
1195 END IF;
1196
1197 -- category_id
1198 IF (p_act_Product_rec.level_type_code = 'FAMILY'
1199 AND (p_act_Product_rec.category_id IS NULL
1200 OR p_act_Product_rec.category_id = FND_API.G_MISS_NUM))
1201 THEN
1202 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN -- MMSG
1203 FND_MESSAGE.set_name('AMS', 'AMS_CAT_NAME_MISSING');
1204 FND_MSG_PUB.add;
1205 END IF;
1206 x_return_status := FND_API.G_RET_STS_ERROR;
1207 RETURN;
1208 END IF;
1209
1210 --Category_set_id
1211 IF (p_act_Product_rec.level_type_code = 'FAMILY'
1212 AND (p_act_Product_rec.arc_act_product_used_by <> 'OFFR'
1213 AND p_act_Product_rec.arc_act_product_used_by <> 'FUND')
1214 AND (p_act_Product_rec.category_set_id IS NULL
1215 OR p_act_Product_rec.category_set_id = FND_API.G_MISS_NUM))
1216 THEN
1217 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1218 FND_MESSAGE.set_name('AMS', 'AMS_CAT_SET_ID_MISSING');
1219 FND_MSG_PUB.add;
1220 END IF;
1221 x_return_status := FND_API.G_RET_STS_ERROR;
1222 RETURN;
1223 END IF;
1224
1225 --Inventory_item_id
1226 IF (p_act_Product_rec.level_type_code = 'PRODUCT'
1227 AND (p_act_Product_rec.inventory_item_id IS NULL
1228 OR p_act_Product_rec.inventory_item_id = FND_API.G_MISS_NUM))
1229 THEN
1230 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1231 FND_MESSAGE.set_name('AMS', 'AMS_INVENTORY_ITEM_ID_MISSING');
1232 FND_MSG_PUB.add;
1233 END IF;
1234 x_return_status := FND_API.G_RET_STS_ERROR;
1235 RETURN;
1236 END IF;
1237
1238 IF p_act_Product_rec.arc_act_product_used_by = 'OFFR' THEN
1239 OPEN c_offer_type;
1240 FETCH c_offer_type INTO l_offer_type, l_custom_setup_id;
1241 CLOSE c_offer_type;
1242
1243 IF l_offer_type = 'SCAN_DATA' THEN
1244 IF p_act_Product_rec.level_type_code = 'PRODUCT' THEN -- category does not have uom
1245 IF p_act_Product_rec.uom_code = FND_API.G_MISS_CHAR
1246 OR p_act_Product_rec.uom_code IS NULL
1247 THEN
1248 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN -- MMSG
1249 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_NO_UOM');
1250 FND_MSG_PUB.add;
1251 END IF;
1252 x_return_status := FND_API.G_RET_STS_ERROR;
1253 RETURN;
1254 END IF;
1255 END IF;
1256
1257 IF p_act_Product_rec.quantity = FND_API.G_MISS_NUM
1258 OR p_act_Product_rec.quantity IS NULL
1259 THEN
1260 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN -- MMSG
1261 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_NO_QUANTITY');
1262 FND_MSG_PUB.add;
1263 END IF;
1264 x_return_status := FND_API.G_RET_STS_ERROR;
1265 RETURN;
1266 END IF;
1267
1268 IF l_custom_setup_id <> 117 THEN -- channel_id not mandatory for special pricing
1269 IF p_act_Product_rec.channel_id = FND_API.G_MISS_NUM
1270 OR p_act_Product_rec.channel_id IS NULL
1271 THEN
1272 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN -- MMSG
1273 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_NO_SCAN_TYPE');
1274 FND_MSG_PUB.add;
1275 END IF;
1276 x_return_status := FND_API.G_RET_STS_ERROR;
1277 RETURN;
1278 END IF;
1279 END IF;
1280
1281 IF p_act_Product_rec.scan_value = FND_API.G_MISS_NUM
1282 OR p_act_Product_rec.scan_value IS NULL
1283 THEN
1284 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN -- MMSG
1285 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_NO_SCAN_VALUE');
1286 FND_MSG_PUB.add;
1287 END IF;
1288 x_return_status := FND_API.G_RET_STS_ERROR;
1289 RETURN;
1290 END IF;
1291
1292 IF p_act_Product_rec.scan_unit_forecast = FND_API.G_MISS_NUM
1293 OR p_act_Product_rec.scan_unit_forecast IS NULL
1294 THEN
1295 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN -- MMSG
1296 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_NO_UNIT_FCST');
1297 FND_MSG_PUB.add;
1298 END IF;
1299 x_return_status := FND_API.G_RET_STS_ERROR;
1300 RETURN;
1301 END IF;
1302 END IF; -- end scan data
1303 END IF; -- end offer
1304
1305 -- Validate uniqueness
1306 IF p_validation_mode = JTF_PLSQL_API.g_create
1307 AND p_act_Product_rec.activity_product_id IS NOT NULL
1308 THEN
1309 IF AMS_Utility_PVT.check_uniqueness(
1310 'ams_act_products',
1311 'activity_product_id = ' || p_act_Product_rec.activity_product_id
1312 ) = FND_API.g_false
1313 THEN
1314 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1315 THEN
1316 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_DUPLICATE_ID');
1317 FND_MSG_PUB.add;
1318 END IF;
1319 x_return_status := FND_API.g_ret_sts_error;
1320 RETURN;
1321 END IF;
1322 END IF;
1323 --
1324 -- check for lookups....ARC_ACT_PRODUCT_USED_BY
1325 --
1326 IF p_act_Product_rec.arc_act_product_used_by <> FND_API.g_miss_char THEN
1327 IF AMS_Utility_PVT.check_lookup_exists(
1328 p_lookup_type => 'AMS_SYS_ARC_QUALIFIER',
1329 p_lookup_code => p_act_Product_rec.arc_act_product_used_by
1330 ) = FND_API.g_false
1331 THEN
1332 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1333 THEN
1334 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_BAD_USEDBY');
1335 FND_MSG_PUB.add;
1336 END IF;
1337 x_return_status := FND_API.g_ret_sts_error;
1338 RETURN;
1339 END IF;
1340 END IF;
1341 --
1342 -- check for lookups....LEVEL_TYPE_CODE
1343 --
1344 IF p_act_Product_rec.LEVEL_TYPE_CODE <> FND_API.g_miss_char THEN
1345 IF AMS_Utility_PVT.check_lookup_exists(
1346 p_lookup_type => 'AMS_PRODUCT_LEVEL',
1347 p_lookup_code => p_act_Product_rec.LEVEL_TYPE_CODE
1348 ) = FND_API.g_false
1349 THEN
1350 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1351 THEN
1352 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_BAD_LEVELTYPE');
1353 FND_MSG_PUB.add;
1354 END IF;
1355 x_return_status := FND_API.g_ret_sts_error;
1356 RETURN;
1357 END IF;
1358 END IF;
1359
1360 --
1361 -- Bug # 2156368
1362 --
1363 /*************** Code added by ABHOLA ************/
1364
1365
1366 IF ((p_act_Product_rec.act_product_used_by_id <> FND_API.g_miss_num)
1367 AND
1368 (p_act_Product_rec.arc_act_product_used_by='FUND'))
1369 THEN
1370
1371 OPEN c_get_budget_type(p_act_Product_rec.act_product_used_by_id);
1372 FETCH c_get_budget_type INTO l_budget_flag;
1373 CLOSE c_get_budget_type;
1374
1375 if (l_budget_flag = 'Y') then
1376
1377 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1378 THEN
1379 FND_MESSAGE.set_name('AMS', 'AMS_CANNOT_ASSOCIATE_PROD');
1380 FND_MSG_PUB.add;
1381 END IF;
1382 x_return_status := FND_API.G_RET_STS_ERROR;
1383 -- If any errors happen abort API/Procedure.
1384 RETURN;
1385
1386 end if ;
1387
1388 END IF;
1389
1390
1391 /****************************************************/
1392 --
1393 -- Begin Validate Referential
1394 --
1395 -- Check FK parameter: act_product_used_by_id #1
1396 IF p_act_Product_rec.act_product_used_by_id <> FND_API.g_miss_num
1397 THEN
1398 IF p_act_Product_rec.arc_act_product_used_by='EVEH'
1399 THEN
1400 l_table_name := 'AMS_EVENT_HEADERS_VL';
1401 l_pk_name := 'EVENT_HEADER_ID';
1402 ELSIF p_act_Product_rec.arc_act_product_used_by='EVEO'
1403 THEN
1404 l_table_name := 'AMS_EVENT_OFFERS_VL';
1405 l_pk_name := 'EVENT_OFFER_ID';
1406
1407 ELSIF p_act_Product_rec.arc_act_product_used_by='EONE'
1408 THEN
1409 l_table_name := 'AMS_EVENT_OFFERS_VL';
1410 l_pk_name := 'EVENT_OFFER_ID';
1411
1412 ELSIF p_act_Product_rec.arc_act_product_used_by='CAMP'
1413 THEN
1414 l_table_name := 'AMS_CAMPAIGNS_VL';
1415 l_pk_name := 'CAMPAIGN_ID';
1416 -- 03-May-2001 RSSHARMA added
1417 ELSIF p_act_Product_rec.arc_act_product_used_by='PROD'
1418 THEN
1419 l_table_name := 'AMS_ACT_PRODUCTS_V';
1420 l_pk_name := 'ACTIVITY_PRODUCT_ID';
1421 -- end 03-May-2001
1422 ELSIF p_act_Product_rec.arc_act_product_used_by='MESG'
1423 THEN
1424 l_table_name := 'AMS_MESSAGES_VL';
1425 l_pk_name := 'MESSAGE_ID';
1426 --07-May-2001 RSSHARMA changed the table name and the primary key
1427 ELSIF p_act_Product_rec.arc_act_product_used_by='OFFR'
1428 THEN
1429 l_table_name := 'QP_LIST_HEADERS_B';
1430 l_pk_name := 'LIST_HEADER_ID';
1431 --end change on 07-May-2001
1432 ELSIF p_act_Product_rec.arc_act_product_used_by='FUND'
1433 THEN
1434 l_table_name := 'OZF_FUNDS_ALL_VL';
1435 l_pk_name := 'FUND_ID';
1436 ELSIF p_act_product_rec.arc_act_product_used_by ='CSCH'
1437 THEN
1438 l_table_name := 'AMS_CAMPAIGN_SCHEDULES_B';
1439 l_pk_name := 'SCHEDULE_ID';
1440 ELSIF p_act_product_rec.arc_act_product_used_by ='MODL'
1441 THEN
1442 l_table_name := 'AMS_DM_MODELS_V';
1443 l_pk_name := 'MODEL_ID';
1444 END IF;
1445
1446 l_pk_value := p_act_Product_rec.act_product_used_by_id;
1447 IF AMS_Utility_PVT.Check_FK_Exists (
1448 p_table_name => l_table_name
1449 ,p_pk_name => l_pk_name
1450 ,p_pk_value => l_pk_value
1451 ) = FND_API.G_FALSE
1452 THEN
1453 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1454 THEN
1455 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_INVALID_REFERENCE');
1456 FND_MSG_PUB.add;
1457 END IF;
1458 x_return_status := FND_API.G_RET_STS_ERROR;
1459 -- If any errors happen abort API/Procedure.
1460 RETURN;
1461 END IF; -- check_fk_exists
1462 END IF;
1463 -- Check FK parameter: inventory_item_id
1464 IF p_act_Product_rec.inventory_item_id <> FND_API.g_miss_num
1465 THEN
1466 l_table_name := 'MTL_SYSTEM_ITEMS_VL';
1467 l_pk_name := 'inventory_item_id';
1468 l_pk_value := p_act_Product_rec.inventory_item_id;
1469 IF AMS_Utility_PVT.Check_FK_Exists (
1470 p_table_name => l_table_name
1471 ,p_pk_name => l_pk_name
1472 ,p_pk_value => l_pk_value
1473 ) = FND_API.G_FALSE
1474 THEN
1475 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1476 THEN
1477 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_INVALID_ITEM');
1478 FND_MSG_PUB.add;
1479 END IF;
1480 x_return_status := FND_API.G_RET_STS_ERROR;
1481 -- If any errors happen abort API/Procedure.
1482 RETURN;
1483 END IF; -- check_fk_exists
1484 END IF;
1485
1486 -- Check FK parameter:organization_id
1487 IF p_act_Product_rec.organization_id <> FND_API.g_miss_num
1488 THEN
1489 l_table_name := 'MTL_SYSTEM_ITEMS_VL';
1490 l_pk_name := 'organization_id';
1491 l_pk_value := p_act_Product_rec.organization_id;
1492 IF AMS_Utility_PVT.Check_FK_Exists (
1493 p_table_name => l_table_name
1494 ,p_pk_name => l_pk_name
1495 ,p_pk_value => l_pk_value
1496 ) = FND_API.G_FALSE
1497 THEN
1498 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1499 THEN
1500 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_INVALID_ORG');
1501 FND_MSG_PUB.add;
1502 END IF;
1503 x_return_status := FND_API.G_RET_STS_ERROR;
1504 -- If any errors happen abort API/Procedure.
1505 RETURN;
1506 END IF; -- check_fk_exists
1507 END IF;
1508
1509 IF p_act_Product_rec.category_id <> FND_API.g_miss_num
1510 THEN
1511 l_table_name := 'MTL_CATEGORIES';
1512 l_pk_name := 'category_id';
1513 l_pk_value := p_act_Product_rec.category_id;
1514 IF AMS_Utility_PVT.Check_FK_Exists (
1515 p_table_name => l_table_name
1516 ,p_pk_name => l_pk_name
1517 ,p_pk_value => l_pk_value
1518 ) = FND_API.G_FALSE
1519 THEN
1520 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1521 THEN
1522 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_INVALID_CAT');
1523 FND_MSG_PUB.add;
1524 END IF;
1525 x_return_status := FND_API.G_RET_STS_ERROR;
1526 -- If any errors happen abort API/Procedure.
1527 RETURN;
1528 END IF; -- check_fk_exists
1529 END IF;
1530
1531 IF p_act_Product_rec.category_set_id <> FND_API.g_miss_num
1532 THEN
1533 l_table_name := 'MTL_CATEGORY_SETS';
1534 l_pk_name := 'category_set_id';
1535 l_pk_value := p_act_Product_rec.category_set_id;
1536 IF AMS_Utility_PVT.Check_FK_Exists (
1537 p_table_name => l_table_name
1538 ,p_pk_name => l_pk_name
1539 ,p_pk_value => l_pk_value
1540 ) = FND_API.G_FALSE
1541 THEN
1542 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1543 THEN
1544 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_INVALID_ITEMCAT');
1545 FND_MSG_PUB.add;
1546 END IF;
1547 x_return_status := FND_API.G_RET_STS_ERROR;
1548 -- If any errors happen abort API/Procedure.
1549 RETURN;
1550 END IF; -- check_fk_exists
1551 END IF;
1552
1553
1554 -- check for flags
1555
1556 ----------------------- enabled_flag ------------------------
1557 IF p_act_Product_rec.enabled_flag <> FND_API.g_miss_char
1558 AND p_act_Product_rec.enabled_flag IS NOT NULL
1559 THEN
1560 IF AMS_Utility_PVT.is_Y_or_N(p_act_Product_rec.enabled_flag) = FND_API.g_false
1561 THEN
1562 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1563 THEN
1564 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_BAD_ENABLED_FLAG');
1565 FND_MSG_PUB.add;
1566 END IF;
1567 x_return_status := FND_API.g_ret_sts_error;
1568 RETURN;
1569 END IF;
1570 END IF;
1571 ----------------------- excluded_flag ------------------------
1572 IF p_act_Product_rec.excluded_flag <> FND_API.g_miss_char
1573 AND p_act_Product_rec.excluded_flag IS NOT NULL
1574 THEN
1575 IF AMS_Utility_PVT.is_Y_or_N(p_act_Product_rec.excluded_flag) = FND_API.g_false
1576 THEN
1577 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1578 THEN
1579 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_BAD_EXCLUDED_FLAG');
1580 FND_MSG_PUB.add;
1581 END IF;
1582 x_return_status := FND_API.g_ret_sts_error;
1583 RETURN;
1584 END IF;
1585 END IF;
1586
1587 ----------------------- primary_product_flag ------------------------
1588 IF p_act_Product_rec.primary_product_flag <> FND_API.g_miss_char
1589 AND p_act_Product_rec.primary_product_flag IS NOT NULL
1590 THEN
1591 IF AMS_Utility_PVT.is_Y_or_N(p_act_Product_rec.primary_product_flag) = FND_API.g_false
1592 THEN
1593 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1594 THEN
1595 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_BAD_PRIMARY_FLAG');
1596 FND_MSG_PUB.add;
1597 END IF;
1598 x_return_status := FND_API.g_ret_sts_error;
1599 RETURN;
1600 END IF;
1601 END IF;
1602
1603 -------------------- Product Name --------------------
1604 -- bug 4102448
1605 IF p_act_product_rec.category_id IS NULL AND p_act_product_rec.inventory_item_id IS NULL THEN
1606 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1607 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_NO_PROD');
1608 FND_MSG_PUB.add;
1609 END IF;
1610 x_return_status := FND_API.g_ret_sts_error;
1611 END IF;
1612
1613 -------------------------------------------------------------------------------
1614 -- added sugupta 06/06/2000
1615 --- Create time validations for ACT_OFFERS
1616 -----------------------------------------------------------------------------
1617
1618 /* IF p_act_Product_rec.ARC_ACT_PRODUCT_USED_BY = 'OFFR' THEN
1619 -- go inside database and look for existing 'PROD' level row
1620 OPEN c_include_level;
1621 FETCH c_include_level into l_level;
1622 CLOSE c_include_level;
1623
1624 IF l_level IS NULL THEN
1625 l_level := 'NEW';
1626 END IF;
1627
1628 -- if level =PROD, error out.. no more include/exclude rows allowed
1629 IF l_level = 'PRODUCT' THEN
1630 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1631 THEN
1632 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_NO_MORE_ROWS');
1633 FND_MSG_PUB.add;
1634 END IF;
1635 x_return_status := FND_API.g_ret_sts_error;
1636 RETURN;
1637 ELSE
1638 -- either no rows exist or it exists for CAT
1639 -- if row exists for CAT and to be added one is include row, error out
1640 -- in this release though.. this stage not necessary as CAT/subCAT wont be allowed to be excluded
1641 IF l_level = 'FAMILY' and p_act_Product_rec.excluded_flag <> 'Y' THEN
1642 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1643 THEN
1644 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_NO_MORE_ROWS');
1645 FND_MSG_PUB.add;
1646 END IF;
1647 x_return_status := FND_API.g_ret_sts_error;
1648 RETURN;
1649 END IF;
1650 -- if no row exists, and exclusion row being added, error out
1651
1652 IF l_level = 'NEW' and p_act_Product_rec.excluded_flag = 'Y' THEN
1653 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1654 THEN
1655 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_NO_INCLUDE_ROWS');
1656 FND_MSG_PUB.add;
1657 END IF;
1658 x_return_status := FND_API.g_ret_sts_error;
1659 RETURN;
1660 END IF;
1661 -- all other cases are allowed... which are:
1662 -- new row for inclusion (CAT or PROD)
1663 -- PROD level rows for exclusion (if level = CAT)
1664 END IF; -- l_level = PROD
1665 END IF; -- used_by = OFFR */
1666
1667 END Validate_Act_Product_Items;
1668
1669 /*****************************************************************************************/
1670 -- Start of Comments
1671 --
1672 -- NAME
1673 -- Validate_Act_product_Record
1674 --
1675 -- PURPOSE
1676 -- This procedure is to validate product record
1677 --
1678 -- NOTES
1679 -- End of Comments
1680 PROCEDURE Validate_Act_product_Record(
1681 p_act_Product_rec IN act_Product_rec_type,
1682 x_return_status OUT NOCOPY VARCHAR2
1683 ) IS
1684 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Act_product_Record';
1685 l_api_version CONSTANT NUMBER := 1.0;
1686 -- Status Local Variables
1687 l_return_status VARCHAR2(1); -- Return value from procedures
1688 item_in_cat NUMBER := 0; -- return value for cursor c_check_item
1689
1690 CURSOR c_get_categories IS
1691 select category_id
1692 from AMS_ACT_PRODUCTS
1693 where ARC_ACT_PRODUCT_USED_BY = p_act_Product_rec.ARC_ACT_PRODUCT_USED_BY
1694 and ACT_PRODUCT_USED_BY_ID = p_act_Product_rec.ACT_PRODUCT_USED_BY_ID
1695 and EXCLUDED_FLAG = 'N';
1696
1697 CURSOR c_get_all_categories IS
1698 select category_id
1699 from AMS_ACT_PRODUCTS
1700 where ARC_ACT_PRODUCT_USED_BY = p_act_Product_rec.ARC_ACT_PRODUCT_USED_BY
1701 and ACT_PRODUCT_USED_BY_ID = p_act_Product_rec.ACT_PRODUCT_USED_BY_ID
1702 and level_type_code = 'FAMILY' -- musman: in prod assoc also we store cat id for lite
1703 and CATEGORY_ID IS NOT NULL;
1704
1705 CURSOR c_get_all_items IS
1706 select INVENTORY_ITEM_ID
1707 from AMS_ACT_PRODUCTS
1708 where ARC_ACT_PRODUCT_USED_BY = p_act_Product_rec.ARC_ACT_PRODUCT_USED_BY
1709 and ACT_PRODUCT_USED_BY_ID = p_act_Product_rec.ACT_PRODUCT_USED_BY_ID
1710 and INVENTORY_ITEM_ID IS NOT NULL;
1711
1712 cat_id NUMBER;
1713 item_id NUMBER;
1714
1715 CURSOR c_check_item(l_cat_id IN NUMBER) IS
1716 select 1
1717 from dual
1718 where exists ( select 1
1719 from MTL_ITEM_CATEGORIES
1720 where INVENTORY_ITEM_ID = p_act_Product_rec.INVENTORY_ITEM_ID
1721 and CATEGORY_ID = l_cat_id);
1722
1723 -- julou cursors to check duplication of items and categories for scan data
1724 CURSOR c_scan_cat_dup1 IS
1725 SELECT COUNT(*)
1726 FROM ams_act_products
1727 WHERE arc_act_product_used_by = 'OFFR'
1728 AND act_product_used_by_id = p_act_Product_rec.act_product_used_by_id
1729 AND category_id = p_act_product_rec.category_id
1730 AND channel_id = p_act_Product_rec.channel_id
1731 AND excluded_flag = 'N';
1732
1733 CURSOR c_scan_cat_dup2 IS
1734 SELECT COUNT(*)
1735 FROM ams_act_products
1736 WHERE arc_act_product_used_by = 'OFFR'
1737 AND act_product_used_by_id = p_act_Product_rec.act_product_used_by_id
1738 AND category_id = p_act_product_rec.category_id
1739 AND channel_id IS NULL
1740 AND excluded_flag = 'N';
1741
1742 CURSOR c_scan_item_dup1 IS
1743 SELECT COUNT(*)
1744 FROM ams_act_products
1745 WHERE arc_act_product_used_by = 'OFFR'
1746 AND act_product_used_by_id = p_act_Product_rec.act_product_used_by_id
1747 AND inventory_item_id = p_act_Product_rec.inventory_item_id
1748 AND channel_id = p_act_Product_rec.channel_id
1749 AND excluded_flag = 'N';
1750
1751 CURSOR c_scan_item_dup2 IS
1752 SELECT count(*)
1753 FROM ams_act_products
1754 WHERE arc_act_product_used_by = 'OFFR'
1755 AND act_product_used_by_id = p_act_Product_rec.act_product_used_by_id
1756 AND inventory_item_id = p_act_Product_rec.inventory_item_id
1757 AND channel_id IS NULL
1758 AND excluded_flag = 'N';
1759
1760 CURSOR c_all_cat1 IS
1761 SELECT category_id
1762 FROM ams_act_products
1763 WHERE arc_act_product_used_by = p_act_product_rec.arc_act_product_used_by
1764 AND act_product_used_by_id = p_act_product_rec.act_product_used_by_id
1765 AND channel_id = p_act_Product_rec.channel_id
1766 AND category_id IS NOT NULL;
1767
1768 CURSOR c_all_cat2 IS
1769 SELECT category_id
1770 FROM ams_act_products
1771 WHERE arc_act_product_used_by = p_act_product_rec.arc_act_product_used_by
1772 AND act_product_used_by_id = p_act_product_rec.act_product_used_by_id
1773 AND channel_id IS NULL
1774 AND category_id IS NOT NULL;
1775
1776 CURSOR c_all_item1 IS
1777 SELECT inventory_item_id
1778 FROM ams_act_products
1779 WHERE arc_act_product_used_by = p_act_product_rec.arc_act_product_used_by
1780 AND act_product_used_by_id = p_act_product_rec.act_product_used_by_id
1781 AND channel_id = p_act_Product_rec.channel_id
1782 AND inventory_item_id IS NOT NULL;
1783
1784 CURSOR c_all_item2 IS
1785 SELECT inventory_item_id
1786 FROM ams_act_products
1787 WHERE arc_act_product_used_by = p_act_product_rec.arc_act_product_used_by
1788 AND act_product_used_by_id = p_act_product_rec.act_product_used_by_id
1789 AND channel_id IS NULL
1790 AND inventory_item_id IS NOT NULL;
1791
1792 CURSOR c_check_item_in_cat(l_cat_id NUMBER, l_item_id NUMBER) IS
1793 SELECT 1
1794 FROM DUAL
1795 WHERE EXISTS(SELECT 1
1796 FROM mtl_item_categories
1797 WHERE category_id = l_cat_id
1798 AND inventory_item_id = l_item_id);
1799 l_count1 NUMBER := 0; -- count for same item or cat
1800 l_count2 NUMBER := 0; -- count for item in category
1801
1802 CURSOR c_offer_type(l_id NUMBER) IS
1803 SELECT offer_type
1804 FROM ams_offers
1805 WHERE qp_list_header_id = l_id;
1806 l_offer_type VARCHAR2(30);
1807
1808 BEGIN
1809 -- Standard call to check for call compatibility.
1810 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1811 l_api_version,
1812 l_api_name,
1813 G_PACKAGE_NAME)
1814 THEN
1815 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1816 END IF;
1817 -- Initialize API return status to success
1818 x_return_status := FND_API.G_RET_STS_SUCCESS;
1819 -- API body
1820 -- added sugupta 06/16/2000
1821 -- DO NOT ALLOW SAME PRODUCT / CATEGORY TO BE INCLUDED /EXCLUDED AGAIN...
1822 -- irrespective of excluded_Flag value... if same product/category appears.. error out..
1823 -- checking for cat duplication
1824 -- julou check items and categories for scan data
1825 IF p_act_Product_rec.arc_act_product_used_by = 'OFFR' THEN
1826 OPEN c_offer_type(p_act_Product_rec.act_product_used_by_id);
1827 FETCH c_offer_type INTO l_offer_type;
1828 CLOSE c_offer_type;
1829
1830 IF l_offer_type = 'SCAN_DATA' THEN
1831 IF p_act_Product_rec.level_type_code = 'PRODUCT' THEN
1832 --first check if duplicate item exists
1833 IF p_act_Product_rec.channel_id <> FND_API.G_MISS_NUM
1834 AND p_act_Product_rec.channel_id IS NOT NULL
1835 THEN
1836 OPEN c_scan_item_dup1;
1837 FETCH c_scan_item_dup1 INTO l_count1;
1838 CLOSE c_scan_item_dup1;
1839 ELSE
1840 OPEN c_scan_item_dup2;
1841 FETCH c_scan_item_dup2 INTO l_count1;
1842 CLOSE c_scan_item_dup2;
1843 END IF;
1844
1845 IF l_count1 > 0 THEN
1846 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1847 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_DUP_ITEM');
1848 FND_MSG_PUB.add;
1849 END IF;
1850
1851 x_return_status := FND_API.g_ret_sts_error;
1852 RETURN;
1853 END IF;
1854
1855 -- check if item in existing categories
1856 IF p_act_Product_rec.channel_id <> FND_API.G_MISS_NUM
1857 AND p_act_Product_rec.channel_id IS NOT NULL
1858 THEN
1859 FOR i IN c_all_cat1 LOOP
1860 OPEN c_check_item_in_cat(i.category_id, p_act_Product_rec.inventory_item_id);
1861 FETCH c_check_item_in_cat INTO l_count2;
1862 CLOSE c_check_item_in_cat;
1863
1864 IF l_count2 = 1 THEN
1865 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1866 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_ITEM_IN_CAT');
1867 FND_MSG_PUB.add;
1868 END IF;
1869
1870 x_return_status := FND_API.g_ret_sts_error;
1871 RETURN;
1872 END IF;
1873 END LOOP;
1874 ELSE
1875 FOR i IN c_all_cat2 LOOP
1876 OPEN c_check_item_in_cat(i.category_id, p_act_Product_rec.inventory_item_id);
1877 FETCH c_check_item_in_cat INTO l_count2;
1878 CLOSE c_check_item_in_cat;
1879
1880 IF l_count2 = 1 THEN
1881 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1882 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_ITEM_IN_CAT');
1883 FND_MSG_PUB.add;
1884 END IF;
1885
1886 x_return_status := FND_API.g_ret_sts_error;
1887 RETURN;
1888 END IF;
1889 END LOOP;
1890 END IF;
1891 ELSIF p_act_Product_rec.level_type_code = 'FAMILY' THEN
1892 --first check if duplicate category exists
1893 IF p_act_Product_rec.channel_id <> FND_API.G_MISS_NUM
1894 AND p_act_Product_rec.channel_id IS NOT NULL
1895 THEN
1896 OPEN c_scan_cat_dup1;
1897 FETCH c_scan_cat_dup1 INTO l_count1;
1898 CLOSE c_scan_cat_dup1;
1899 ELSE
1900 OPEN c_scan_cat_dup2;
1901 FETCH c_scan_cat_dup2 INTO l_count1;
1902 CLOSE c_scan_cat_dup2;
1903 END IF;
1904
1905 IF l_count1 > 0 THEN
1906 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1907 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_DUP_CAT');
1908 FND_MSG_PUB.add;
1909 END IF;
1910
1911 x_return_status := FND_API.g_ret_sts_error;
1912 RETURN;
1913 END IF;
1914
1915 -- check if new category containing existing items
1916 IF p_act_Product_rec.channel_id <> FND_API.G_MISS_NUM
1917 AND p_act_Product_rec.channel_id IS NOT NULL
1918 THEN
1919 FOR i IN c_all_item1 LOOP
1920 OPEN c_check_item_in_cat(p_act_Product_rec.category_id, i.inventory_item_id);
1921 FETCH c_check_item_in_cat INTO l_count2;
1922 CLOSE c_check_item_in_cat;
1923
1924 IF l_count2 = 1 THEN
1925 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1926 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_CAT_HAS_ITEM');
1927 FND_MSG_PUB.add;
1928 END IF;
1929
1930 x_return_status := FND_API.g_ret_sts_error;
1931 RETURN;
1932 END IF;
1933 END LOOP;
1934 ELSE
1935 FOR i IN c_all_item2 LOOP
1936 OPEN c_check_item_in_cat(p_act_Product_rec.category_id, i.inventory_item_id);
1937 FETCH c_check_item_in_cat INTO l_count2;
1938 CLOSE c_check_item_in_cat;
1939
1940 IF l_count2 = 1 THEN
1941 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1942 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_CAT_HAS_ITEM');
1943 FND_MSG_PUB.add;
1944 END IF;
1945
1946 x_return_status := FND_API.g_ret_sts_error;
1947 RETURN;
1948 END IF;
1949 END LOOP;
1950 END IF;
1951 END IF;
1952 END IF;
1953 ELSE -- julou end checking for scan data. code below is not changed
1954
1955 IF p_act_Product_rec.level_type_code = 'FAMILY' THEN
1956
1957 OPEN c_get_all_categories;
1958 LOOP
1959 FETCH c_get_all_categories INTO cat_id;
1960
1961 EXIT WHEN c_get_all_categories%NOTFOUND;
1962
1963 IF p_act_Product_rec.category_id = cat_id THEN
1964 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1965 THEN
1966 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_DUPE_CAT');
1967 FND_MSG_PUB.add;
1968 END IF;
1969 x_return_status := FND_API.g_ret_sts_error;
1970 CLOSE c_get_all_categories;
1971 RETURN;
1972 END IF;
1973 END LOOP;
1974 CLOSE c_get_all_categories;
1975 END IF;
1976 -- checking for item duplication..
1977 -- for now, i do not care the item belongs to what category.. item inclusion/exclusion holds good
1978 -- irrespective of category
1979 IF p_act_Product_rec.level_type_code = 'PRODUCT' THEN
1980
1981 OPEN c_get_all_items;
1982 LOOP
1983 FETCH c_get_all_items INTO item_id;
1984
1985 EXIT WHEN c_get_all_items%NOTFOUND;
1986
1987 IF p_act_Product_rec.inventory_item_id = item_id THEN
1988 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1989 THEN
1990 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_DUPE_ITEM');
1991 FND_MSG_PUB.add;
1992 END IF;
1993 x_return_status := FND_API.g_ret_sts_error;
1994 CLOSE c_get_all_items;
1995 RETURN;
1996 END IF;
1997 END LOOP;
1998 CLOSE c_get_all_items;
1999 END IF;
2000 -- for any row, CAT cannot be excluded
2001 /** commented by abhola
2002 IF p_act_Product_rec.level_type_code = 'FAMILY'
2003 AND p_act_Product_rec.excluded_flag = 'Y' THEN
2004 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2005 THEN
2006 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_CANT_EXCLUDE_CAT');
2007 FND_MSG_PUB.add;
2008 END IF;
2009 x_return_status := FND_API.g_ret_sts_error;
2010 RETURN;
2011 END IF;
2012 **/
2013
2014
2015 -- for any row, if excluding a product, the item need to be associated to the category
2016 /***** commented by ABHOLA
2017
2018 IF p_act_Product_rec.level_type_code = 'PRODUCT'
2019 AND p_act_Product_rec.excluded_flag = 'Y' THEN
2020
2021 OPEN c_get_categories;
2022 LOOP
2023 FETCH c_get_categories INTO cat_id;
2024
2025 EXIT WHEN c_get_categories%NOTFOUND;
2026
2027 OPEN c_check_item(cat_id);
2028 FETCH c_check_item into item_in_cat;
2029 CLOSE c_check_item;
2030
2031 EXIT WHEN item_in_cat = 1;
2032 END LOOP;
2033 CLOSE c_get_categories;
2034 IF item_in_cat <> 1 THEN
2035 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2036 THEN
2037 FND_MESSAGE.set_name('AMS', 'AMS_ACT_PRD_NOT_IN_CAT');
2038 FND_MSG_PUB.add;
2039 END IF;
2040 x_return_status := FND_API.g_ret_sts_error;
2041 RETURN;
2042 END IF;
2043
2044 END IF;
2045 *****/
2046
2047 -- END of API body.
2048 END IF;
2049 ---------------------Product/Category hierarchy validation for schedule -----------------
2050 /*
2051 --bug: 4634617 fix as per r12 requirement removing the validation
2052 IF ( p_act_product_rec.arc_act_product_used_by = 'CSCH'
2053 OR p_act_product_rec.arc_act_product_used_by = 'CAMP')
2054 THEN
2055 check_product_val_for_csch(p_act_product_rec,x_return_status);
2056 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2057 THEN
2058 RETURN;
2059 END IF;
2060 END IF;
2061 */
2062
2063
2064 END Validate_Act_product_Record;
2065
2066
2067 PROCEDURE complete_act_Product_rec(
2068 p_act_Product_rec IN act_Product_rec_type,
2069 x_act_Product_rec OUT NOCOPY act_Product_rec_type
2070 ) IS
2071 CURSOR c_product IS
2072 SELECT *
2073 FROM ams_act_products
2074 WHERE activity_product_id = p_act_Product_rec.activity_product_id;
2075
2076 l_act_Product_rec c_product%ROWTYPE;
2077 BEGIN
2078 x_act_Product_rec := p_act_Product_rec;
2079 OPEN c_product;
2080 FETCH c_product INTO l_act_Product_rec;
2081 IF c_product%NOTFOUND THEN
2082 CLOSE c_product;
2083 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2084 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2085 FND_MSG_PUB.add;
2086 END IF;
2087 RAISE FND_API.g_exc_error;
2088 END IF;
2089 CLOSE c_product;
2090 IF p_act_Product_rec.act_product_used_by_id = FND_API.g_miss_num THEN
2091 x_act_Product_rec.act_product_used_by_id :=l_act_Product_rec.act_product_used_by_id;
2092 END IF;
2093 IF p_act_Product_rec.arc_act_product_used_by = FND_API.g_miss_char THEN
2094 x_act_Product_rec.arc_act_product_used_by := l_act_Product_rec.arc_act_product_used_by;
2095 END IF;
2096 IF p_act_Product_rec.inventory_item_id = FND_API.g_miss_num THEN
2097 x_act_Product_rec.inventory_item_id := l_act_Product_rec.inventory_item_id;
2098 END IF;
2099 IF p_act_Product_rec.ORGANIZATION_ID = FND_API.g_miss_num THEN
2100 x_act_Product_rec.ORGANIZATION_ID := l_act_Product_rec.ORGANIZATION_ID;
2101 END IF;
2102 IF p_act_Product_rec.CATEGORY_ID = FND_API.g_miss_num THEN
2103 x_act_Product_rec.CATEGORY_ID := l_act_Product_rec.CATEGORY_ID;
2104 END IF;
2105 IF p_act_Product_rec.CATEGORY_SET_ID = FND_API.g_miss_num THEN
2106 x_act_Product_rec.CATEGORY_SET_ID := l_act_Product_rec.CATEGORY_SET_ID;
2107 END IF;
2108 IF p_act_Product_rec.LEVEL_TYPE_CODE = FND_API.g_miss_char THEN
2109 x_act_Product_rec.LEVEL_TYPE_CODE := l_act_Product_rec.LEVEL_TYPE_CODE;
2110 END IF;
2111 IF p_act_Product_rec.ENABLED_FLAG = FND_API.g_miss_char THEN
2112 x_act_Product_rec.ENABLED_FLAG := l_act_Product_rec.ENABLED_FLAG;
2113 END IF;
2114 IF p_act_Product_rec.EXCLUDED_FLAG = FND_API.g_miss_char THEN
2115 x_act_Product_rec.EXCLUDED_FLAG := l_act_Product_rec.EXCLUDED_FLAG;
2116 END IF;
2117 IF p_act_Product_rec.PRIMARY_PRODUCT_FLAG = FND_API.g_miss_char THEN
2118 x_act_Product_rec.PRIMARY_PRODUCT_FLAG := l_act_Product_rec.PRIMARY_PRODUCT_FLAG;
2119 END IF;
2120 IF p_act_Product_rec.PRODUCT_SALE_TYPE = FND_API.g_miss_char THEN
2121 x_act_Product_rec.PRODUCT_SALE_TYPE := l_act_Product_rec.PRODUCT_SALE_TYPE;
2122 END IF;
2123 IF p_act_Product_rec.ATTRIBUTE_CATEGORY = FND_API.g_miss_char THEN
2124 x_act_Product_rec.ATTRIBUTE_CATEGORY := l_act_Product_rec.ATTRIBUTE_CATEGORY;
2125 END IF;
2126 IF p_act_Product_rec.ATTRIBUTE1 = FND_API.g_miss_char THEN
2127 x_act_Product_rec.ATTRIBUTE1 := l_act_Product_rec.ATTRIBUTE1;
2128 END IF;
2129 IF p_act_Product_rec.ATTRIBUTE2 = FND_API.g_miss_char THEN
2130 x_act_Product_rec.ATTRIBUTE2 := l_act_Product_rec.ATTRIBUTE2;
2131 END IF;
2132 IF p_act_Product_rec.ATTRIBUTE3 = FND_API.g_miss_char THEN
2133 x_act_Product_rec.ATTRIBUTE3 := l_act_Product_rec.ATTRIBUTE3;
2134 END IF;
2135 IF p_act_Product_rec.ATTRIBUTE4 = FND_API.g_miss_char THEN
2136 x_act_Product_rec.ATTRIBUTE4 := l_act_Product_rec.ATTRIBUTE4;
2137 END IF;
2138 IF p_act_Product_rec.ATTRIBUTE5 = FND_API.g_miss_char THEN
2139 x_act_Product_rec.ATTRIBUTE5 := l_act_Product_rec.ATTRIBUTE5;
2140 END IF;
2141 IF p_act_Product_rec.ATTRIBUTE6 = FND_API.g_miss_char THEN
2142 x_act_Product_rec.ATTRIBUTE6 := l_act_Product_rec.ATTRIBUTE6;
2143 END IF;
2144 IF p_act_Product_rec.ATTRIBUTE7 = FND_API.g_miss_char THEN
2145 x_act_Product_rec.ATTRIBUTE7 := l_act_Product_rec.ATTRIBUTE7;
2146 END IF;
2147 IF p_act_Product_rec.ATTRIBUTE8 = FND_API.g_miss_char THEN
2148 x_act_Product_rec.ATTRIBUTE8 := l_act_Product_rec.ATTRIBUTE8;
2149 END IF;
2150 IF p_act_Product_rec.ATTRIBUTE9 = FND_API.g_miss_char THEN
2151 x_act_Product_rec.ATTRIBUTE9 := l_act_Product_rec.ATTRIBUTE9;
2152 END IF;
2153 IF p_act_Product_rec.ATTRIBUTE10 = FND_API.g_miss_char THEN
2154 x_act_Product_rec.ATTRIBUTE10 := l_act_Product_rec.ATTRIBUTE10;
2155 END IF;
2156 IF p_act_Product_rec.ATTRIBUTE11 = FND_API.g_miss_char THEN
2157 x_act_Product_rec.ATTRIBUTE11 := l_act_Product_rec.ATTRIBUTE11;
2158 END IF;
2159 IF p_act_Product_rec.ATTRIBUTE11 = FND_API.g_miss_char THEN
2160 x_act_Product_rec.ATTRIBUTE11 := l_act_Product_rec.ATTRIBUTE11;
2161 END IF;
2162 IF p_act_Product_rec.ATTRIBUTE12 = FND_API.g_miss_char THEN
2163 x_act_Product_rec.ATTRIBUTE12 := l_act_Product_rec.ATTRIBUTE12;
2164 END IF;
2165 IF p_act_Product_rec.ATTRIBUTE13 = FND_API.g_miss_char THEN
2166 x_act_Product_rec.ATTRIBUTE13 := l_act_Product_rec.ATTRIBUTE13;
2167 END IF;
2168 IF p_act_Product_rec.ATTRIBUTE14 = FND_API.g_miss_char THEN
2169 x_act_Product_rec.ATTRIBUTE14 := l_act_Product_rec.ATTRIBUTE14;
2170 END IF;
2171 IF p_act_Product_rec.ATTRIBUTE15 = FND_API.g_miss_char THEN
2172 x_act_Product_rec.ATTRIBUTE15 := l_act_Product_rec.ATTRIBUTE15;
2173 END IF;
2174 /*
2175 IF p_act_Product_rec.security_group_id = FND_API.g_miss_num THEN
2176 x_act_Product_rec.security_group_id := l_act_Product_rec.security_group_id;
2177 END IF;
2178 */
2179 IF p_act_Product_rec.line_lumpsum_amount = FND_API.g_miss_num THEN
2180 x_act_Product_rec.line_lumpsum_amount := l_act_Product_rec.line_lumpsum_amount;
2181 END IF;
2182 IF p_act_Product_rec.line_lumpsum_qty = FND_API.g_miss_num THEN
2183 x_act_Product_rec.line_lumpsum_qty := l_act_Product_rec.line_lumpsum_qty;
2184 END IF;
2185
2186 IF p_act_Product_rec.scan_value = FND_API.g_miss_num THEN
2187 x_act_Product_rec.scan_value := l_act_Product_rec.scan_value;
2188 END IF;
2189
2190 IF p_act_Product_rec.scan_unit_forecast = FND_API.g_miss_num THEN
2191 x_act_Product_rec.scan_unit_forecast := l_act_Product_rec.scan_unit_forecast;
2192 END IF;
2193
2194 IF p_act_Product_rec.channel_id = FND_API.g_miss_num THEN
2195 x_act_Product_rec.channel_id := l_act_Product_rec.channel_id;
2196 END IF;
2197
2198 IF p_act_Product_rec.adjustment_flag = FND_API.g_miss_char THEN
2199 x_act_Product_rec.adjustment_flag := l_act_Product_rec.adjustment_flag;
2200 END IF;
2201
2202 IF p_act_Product_rec.uom_code = FND_API.g_miss_char THEN
2203 x_act_Product_rec.uom_code := l_act_Product_rec.uom_code;
2204 END IF;
2205
2206 IF p_act_Product_rec.quantity = FND_API.g_miss_num THEN
2207 x_act_Product_rec.quantity := l_act_Product_rec.quantity;
2208 END IF;
2209
2210 END complete_act_Product_rec;
2211
2212
2213 /*
2214 This function will be getting the Calculated Category Name with invalid,
2215 depending upon object type.
2216 */
2217 FUNCTION get_category_name(
2218 p_category_id IN NUMBER,
2219 p_category_set_id IN NUMBER,
2220 p_object_type in varchar2
2221 ) RETURN VARCHAR2
2222 IS
2223
2224 CURSOR get_cat_name IS
2225 SELECT CATEGORY_CONCAT_SEGS
2226 FROM mtl_categories_v
2227 WHERE category_id = p_category_id;
2228
2229 CURSOR get_cat_name2 IS
2230 SELECT
2231 NVL(d.category_desc, category_concat_segs) categoryName
2232 FROM
2233 mtl_default_category_sets a ,
2234 mtl_category_sets_b b ,
2235 mtl_categories_v c ,
2236 ENI_PROD_DEN_HRCHY_PARENTS_V d
2237 WHERE
2238 a.functional_area_id in (7,11)
2239 AND a.category_set_id = b.category_set_id
2240 AND b.structure_id = c.structure_id
2241 AND c.category_id = d.category_id(+)
2242 AND c.category_id = p_category_id;
2243
2244 CURSOR get_cat_name3 IS
2245 SELECT c.category_concat_segs
2246 FROM mtl_categories_v c
2247 WHERE c.category_id = p_category_id;
2248
2249 --inanaiah Bug 4956134 fix - sql id 14423554, 14423628
2250 --inanaiah - Bug 5025294 fix - removed XXXIFC_region_items reference
2251 --inanaiah - Bug 5207293 fix - removed "like" as it is an exact match - Ids 17263290/17263381
2252 /*
2253 CURSOR get_prompt IS
2254 SELECT ATTRIBUTE_LABEL_LONG
2255 from AK_REGION_ITEMS_VL
2256 where region_code like 'AMS_COMPETITOR_PRODUCTS'
2257 and attribute_code like 'AMS_INVALID';
2258 */
2259 CURSOR get_prompt IS
2260 SELECT
2261 ARAT.ATTRIBUTE_LABEL_LONG
2262 FROM
2263 AK_REGION_ITEMS_TL ARAT,
2264 AK_REGION_ITEMS ARA
2265 WHERE
2266 ARAT.REGION_APPLICATION_ID = ARA.REGION_APPLICATION_ID AND
2267 ARAT.REGION_CODE = ARA.REGION_CODE AND
2268 ARAT.ATTRIBUTE_APPLICATION_ID = ARA.ATTRIBUTE_APPLICATION_ID AND
2269 ARAT.ATTRIBUTE_CODE = ARA.ATTRIBUTE_CODE AND
2270 ARAT.LANGUAGE = USERENV('LANG') AND
2271 ARA.REGION_CODE = 'AMS_COMPETITOR_PRODUCTS' AND
2272 ARA.ATTRIBUTE_CODE = 'AMS_INVALID';
2273
2274 l_cat_name VARCHAR2(4000);
2275 l_name VARCHAR2(4000);
2276 l_name2 VARCHAR2(4000);
2277 l_prompt VARCHAR2(80);
2278
2279 BEGIN
2280 OPEN get_prompt;
2281 FETCH get_prompt INTO l_prompt;
2282 CLOSE get_prompt;
2283
2284 IF (p_object_type = 'FUND' OR p_object_type = 'OFFR')
2285 THEN
2286 OPEN get_cat_name2;
2287 FETCH get_cat_name2 INTO l_name2;
2288 CLOSE get_cat_name2;
2289 l_cat_name := l_name2;
2290 ELSE
2291 OPEN get_cat_name;
2292 FETCH get_cat_name INTO l_name;
2293 CLOSE get_cat_name;
2294 l_cat_name := l_name ||' - '||l_prompt;
2295 END IF;
2296
2297 IF l_cat_name IS NULL
2298 THEN
2299 OPEN get_cat_name3;
2300 FETCH get_cat_name3 INTO l_name2;
2301 CLOSE get_cat_name3;
2302 l_cat_name := l_name2 ||' - '||l_prompt;
2303 END IF;
2304
2305 return (l_cat_name);
2306 EXCEPTION
2307 WHEN OTHERS THEN
2308 return l_prompt;
2309 END;
2310
2311 FUNCTION get_category_desc(
2312 p_category_id IN NUMBER,
2313 p_category_set_id IN NUMBER,
2314 p_object_type in varchar2
2315 ) RETURN VARCHAR2
2316 IS
2317
2318 CURSOR get_cat_Desc IS
2319 SELECT CONCATENATED_DESCRIPTION
2320 FROM ams_mtl_Categories_denorm_vl
2321 WHERE category_id = p_category_id;
2322
2323 CURSOR get_cat_Desc2 IS
2324 SELECT
2325 NVL(d.concat_cat_parentage, c.description) categoryDescr
2326 FROM
2327 mtl_default_category_sets a ,
2328 mtl_category_sets_b b ,
2329 mtl_categories_v c ,
2330 ENI_PROD_DEN_HRCHY_PARENTS_V d
2331 WHERE
2332 a.functional_area_id in (7,11)
2333 AND a.category_set_id = b.category_set_id
2334 AND b.structure_id = c.structure_id
2335 AND c.category_id = d.category_id(+)
2336 AND c.category_id = p_category_id;
2337
2338 CURSOR get_cat_Desc3 IS
2339 SELECT c.description
2340 FROM mtl_categories_v c
2341 WHERE c.category_id = p_category_id;
2342
2343 --inanaiah Bug 4956134 fix - sql id 14423554, 14423628
2344 --inanaiah - Bug 5025294 fix - removed XXXIFC_region_items reference
2345 --inanaiah - Bug 5207293 fix - removed "like" as it is an exact match - Ids 17263290/17263381
2346 /*
2347 CURSOR get_prompt IS
2348 SELECT ATTRIBUTE_LABEL_LONG
2349 from AK_REGION_ITEMS_VL
2350 where region_code like 'AMS_COMPETITOR_PRODUCTS'
2351 and attribute_code like 'AMS_INVALID';
2352 */
2353 CURSOR get_prompt IS
2354 SELECT
2355 ARAT.ATTRIBUTE_LABEL_LONG
2356 FROM
2357 AK_REGION_ITEMS_TL ARAT,
2358 AK_REGION_ITEMS ARA
2359 WHERE
2360 ARAT.REGION_APPLICATION_ID = ARA.REGION_APPLICATION_ID AND
2361 ARAT.REGION_CODE = ARA.REGION_CODE AND
2362 ARAT.ATTRIBUTE_APPLICATION_ID = ARA.ATTRIBUTE_APPLICATION_ID AND
2363 ARAT.ATTRIBUTE_CODE = ARA.ATTRIBUTE_CODE AND
2364 ARAT.LANGUAGE = USERENV('LANG') AND
2365 ARA.REGION_CODE = 'AMS_COMPETITOR_PRODUCTS' AND
2366 ARA.ATTRIBUTE_CODE = 'AMS_INVALID';
2367
2368 l_cat_desc VARCHAR2(4000);
2369 l_desc VARCHAR2(4000);
2370 l_desc2 VARCHAR2(4000);
2371 l_prompt VARCHAR2(80);
2372
2373 BEGIN
2374
2375 OPEN get_prompt;
2376 FETCH get_prompt INTO l_prompt;
2377 CLOSE get_prompt;
2378
2379 IF (p_object_type = 'FUND' OR p_object_type = 'OFFR')
2380 THEN
2381 OPEN get_cat_Desc2;
2382 FETCH get_cat_Desc2 INTO l_desc2;
2383 CLOSE get_cat_Desc2;
2384 l_cat_desc := l_desc2;
2385 ELSE
2386 OPEN get_cat_Desc;
2387 FETCH get_cat_Desc INTO l_desc;
2388 CLOSE get_cat_Desc;
2389 l_cat_desc := l_desc ||' - '||l_prompt;
2390 END IF;
2391
2392 IF l_cat_desc IS NULL
2393 THEN
2394 OPEN get_cat_desc3;
2395 FETCH get_cat_desc3 INTO l_desc2;
2396 CLOSE get_cat_desc3;
2397 l_cat_desc := l_desc2 ||' - '||l_prompt;
2398 END IF;
2399
2400 return (l_cat_desc);
2401 EXCEPTION
2402 WHEN OTHERS THEN
2403 return l_prompt;
2404 END;
2405
2406 -- Private procedure to write debug message to FND_LOG table
2407 PROCEDURE write_debug_message(p_log_level NUMBER,
2408 p_procedure_name VARCHAR2,
2409 p_label VARCHAR2,
2410 p_text VARCHAR2
2411 )
2412 IS
2413 l_module_name VARCHAR2(400);
2414 DELIMETER CONSTANT VARCHAR2(1) := '.';
2415 LABEL_PREFIX CONSTANT VARCHAR2(15) := 'WFScheduleExec';
2416 BEGIN
2417 IF AMS_UTILITY_PVT.logging_enabled (p_log_level)
2418 THEN
2419 -- Set the Module Name
2420 l_module_name := 'ams'||DELIMETER||'plsql'||DELIMETER||G_PACKAGE_NAME||DELIMETER||p_procedure_name||DELIMETER||LABEL_PREFIX||'-'||p_label;
2421 -- Log the Message
2422 AMS_UTILITY_PVT.debug_message(p_log_level,
2423 l_module_name,
2424 p_text
2425 );
2426 END IF;
2427 END write_debug_message;
2428
2429 FUNCTION UPDATE_SCHEDULE_ACTIVITIES(p_subscription_guid IN RAW,
2430 p_event IN OUT NOCOPY WF_EVENT_T
2431 ) RETURN VARCHAR2
2432 IS
2433 l_schedule_id NUMBER;
2434 l_association_id NUMBER;
2435 l_citem_id NUMBER;
2436 l_citem_ver_id NUMBER;
2437 l_act_prod_id NUMBER;
2438 l_Return_status varchar2(20);
2439
2440 CURSOR c_citem_assoc (l_csch_id IN NUMBER) IS
2441 SELECT assoc.association_id, assoc.content_item_id, ci.live_citem_version_id
2442 FROM ibc_associations assoc, ibc_content_Items ci
2443 --by musman:as per r12 requirement,live version stamping should be done for collab content
2444 WHERE assoc.association_type_code in ('AMS_PLCE') --('AMS_COLB','AMS_PLCE')
2445 AND assoc.associated_object_val1 = to_char(l_csch_id) --musman:bug 4145845 Fix
2446 AND assoc.content_item_id = ci.content_Item_id;
2447
2448 /* -- primary product flag should be marked from the UI
2449 CURSOR c_act_prod_id (l_csch_id IN NUMBER) IS
2450 SELECT activity_product_id
2451 from ams_act_products act, ams_campaign_schedules_b csc
2452 where act.ARC_ACT_PRODUCT_USED_BY = 'CSCH'
2453 and act.ACT_PRODUCT_USED_BY_ID = l_csch_id
2454 and act.LEVEL_TYPE_CODE = 'FAMILY'
2455 and act.ACT_PRODUCT_USED_BY_ID = csc.SCHEDULE_ID
2456 and csc.USAGE = 'LITE';
2457 */
2458 PROCEDURE_NAME CONSTANT VARCHAR2(30) := 'UPDATE_SCHEDULE_ACTIVITIES';
2459
2460 BEGIN
2461
2462 -- Get the Value of SCHEDULE_ID
2463 l_schedule_id := p_event.getValueForParameter('SCHEDULE_ID');
2464
2465 OPEN c_citem_assoc(l_schedule_id);
2466 LOOP
2467 FETCH c_citem_assoc INTO l_association_id, l_citem_id, l_citem_ver_id;
2468 EXIT WHEN c_citem_assoc%NOTFOUND;
2469
2470 IF l_association_id IS NOT null
2471 AND l_citem_id IS NOT null
2472 AND l_citem_ver_id IS NOT NULl
2473 THEN
2474 Ibc_Associations_Pkg.UPDATE_ROW(
2475 p_association_id => l_association_id
2476 ,p_citem_version_id => l_citem_ver_id
2477 );
2478 END IF;
2479 END LOOP;
2480 CLOSE c_citem_assoc;
2481
2482 /*
2483 OPEN c_act_prod_id(l_schedule_id);
2484 FETCH c_act_prod_id INTO l_act_prod_id;
2485 CLOSE c_act_prod_id ;
2486
2487 IF (l_act_prod_id IS NOT NULL)
2488 THEN
2489 UPDATE ams_act_products
2490 SET primary_product_flag = 'Y'
2491 WHERE activity_product_id =l_act_prod_id;
2492 END IF;
2493 */
2494
2495 return 'SUCCESS';
2496
2497 EXCEPTION
2498
2499 WHEN OTHERS THEN
2500 WF_CORE.CONTEXT('AMS_ACT_PRODUCTS','UPDATE_SCHEDULE_ACTIVITIES',
2501 p_event.getEventName( ), p_subscription_guid);
2502 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2503 RETURN 'ERROR';
2504 END UPDATE_SCHEDULE_ACTIVITIES;
2505
2506 /** API to be used by Campign approval process **/
2507 /** Before approval to find out whether content is approved or not **/
2508
2509 procedure IS_ALL_CONTENT_APPROVED (
2510 p_schedule_id IN NUMBER,
2511 x_return_status OUT NOCOPY VARCHAR2
2512 )
2513 IS
2514 cursor C_Content( l_sch_id IN NUMBER) IS
2515 SELECT count(*)
2516 FROM IBC_ASSOCIATIONS IbcAssn,
2517 ibc_content_items citem
2518 WHERE IbcAssn.ASSOCIATED_OBJECT_VAL1 = to_char(l_sch_id )-- musman:bug 4145845 Fix
2519 AND IbcAssn.Content_item_id = citem.content_item_id
2520 AND citem.content_item_status <> 'APPROVED'
2521 AND ibcassn.ASSOCIATION_TYPE_CODE in ('AMS_COLB','AMS_PLCE') ;
2522
2523 COUNTER NUMBER;
2524 BEGIN
2525 open C_Content(p_schedule_id);
2526 fetch C_Content into COUNTER;
2527 if (C_Content%notfound) then
2528 x_return_status := 'Y';
2529 end if;
2530 if (COUNTER > 0) then
2531 x_return_status := 'N';
2532 else
2533 x_return_status := 'Y';
2534 end if;
2535 close C_Content;
2536 END IS_ALL_CONTENT_APPROVED;
2537
2538 ------------------------------------------------------------------------------------------
2539 /*
2540 --bug: 4634617 fix as per r12 requirement removing the validation
2541 PROCEDURE check_product_val_for_csch
2542 ( p_act_Product_rec IN act_Product_rec_type,
2543 x_return_status OUT NOCOPY VARCHAR2
2544 )IS
2545
2546
2547
2548 l_campaign_id NUMBER;
2549 l_usage varchar2(30);
2550
2551
2552 CURSOR get_csch_detl
2553 IS
2554 SELECT campaign_id,usage
2555 FROM ams_campaign_schedules_b
2556 WHERE schedule_id = p_act_product_rec.act_product_used_by_id;
2557
2558 CURSOR check_prod_exist
2559 IS
2560 SELECT 1
2561 from ams_act_products
2562 where arc_act_product_used_by = 'CAMP'
2563 and act_product_used_by_id = l_campaign_id
2564 and level_type_code = 'PRODUCT'
2565 and organization_id = p_act_product_rec.organization_id
2566 and inventory_item_id = p_act_product_rec.inventory_item_id;
2567
2568 CURSOR check_prod_cat_exist
2569 IS
2570 SELECT 1
2571 from ams_act_products a
2572 ,mtl_item_categories ml
2573 where arc_act_product_used_by = 'CAMP'
2574 and act_product_used_by_id = l_campaign_id
2575 and level_type_code = 'FAMILY'
2576 and a.category_id = p_act_product_rec.category_id
2577 and ml.organization_id = p_act_product_rec.organization_id
2578 and ml.inventory_item_id = p_act_product_rec.inventory_item_id
2579 and ml.category_id = a.CATEGORY_ID
2580 and ml.category_set_id = p_act_product_rec.category_SET_id;
2581
2582 CURSOR check_cat_exist
2583 IS
2584 SELECT 1
2585 from ams_act_products
2586 where arc_act_product_used_by = 'CAMP'
2587 and act_product_used_by_id = l_campaign_id
2588 and level_type_code = 'FAMILY'
2589 and category_id = p_act_product_rec.category_id
2590 and category_set_id = p_act_product_rec.category_set_id;
2591
2592 CURSOR check_cat_exist_hrchy
2593 IS
2594 select 1
2595 from ENI_PROD_DEN_HRCHY_PARENTS_V
2596 where category_id = p_act_product_rec.category_id
2597 start with category_id in (select category_id
2598 from ams_act_products
2599 where arc_act_product_used_by = 'CAMP'
2600 and act_product_used_by_id = l_campaign_id
2601 and level_type_code = 'FAMILY')
2602 connect by prior category_id = category_parent_id ;
2603
2604
2605 l_count NUMBER := 0;
2606 l_api_name constant VARCHAR2(30) := 'check_product_val_for_csch';
2607 l_full_name CONSTANT VARCHAR2(60) := g_package_name ||'.'|| l_api_name;
2608
2609 CURSOR c_item_cat_check
2610 IS
2611 SELECT 1
2612 from mtl_item_categories
2613 where inventory_item_id = p_act_product_rec.inventory_item_id
2614 and category_id = p_act_product_rec.category_id
2615 and category_set_id = p_act_product_rec.category_set_id;
2616
2617 BEGIN
2618
2619 x_return_status := FND_API.g_ret_sts_success;
2620 -- checking if cat_id and inv id is passed,assoc exist.
2621 IF (AMS_LOG_PROCEDURE_ON) THEN
2622 AMS_UTILITY_PVT.debug_message(AMS_LOG_PROCEDURE,G_module_name,l_full_name||':Start');
2623 END IF;
2624
2625 IF (( p_act_product_rec.inventory_item_id <> FND_API.G_MISS_NUM
2626 AND p_act_product_rec.category_id <> FND_API.G_MISS_NUM )
2627 AND p_act_product_rec.level_type_code = 'PRODUCT')
2628 THEN
2629 OPEN c_item_cat_check;
2630 FETCH c_item_cat_check into l_count;
2631 CLOSE c_item_cat_check;
2632 IF l_count = 0 THEN
2633 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2634 FND_MESSAGE.set_name('AMS', 'AMS_API_PRD_ITEM_IN_CAT');
2635 FND_MSG_PUB.add;
2636 END IF;
2637 END IF;
2638 l_count := 0;
2639 END IF;
2640
2641 IF p_act_product_rec.arc_act_product_used_by = 'CSCH'
2642 THEN
2643 IF (AMS_DEBUG_HIGH_ON) THEN
2644 AMS_UTILITY_PVT.debug_message('checking for hierarchy inheritance for schedule:'|| p_act_product_rec.act_product_used_by_id);
2645 AMS_UTILITY_PVT.debug_message('inventory_item_id:'|| p_act_product_rec.inventory_item_id);
2646 AMS_UTILITY_PVT.debug_message('organization_id:'|| p_act_product_rec.organization_id);
2647 AMS_UTILITY_PVT.debug_message('category_id'|| p_act_product_rec.category_id);
2648 AMS_UTILITY_PVT.debug_message('level_type_code'|| p_act_product_rec.level_type_code);
2649 END IF;
2650
2651 OPEN get_csch_detl;
2652 FETCH get_csch_detl INTO l_campaign_id,l_usage;
2653 CLOSE get_csch_detl;
2654
2655 IF (AMS_DEBUG_HIGH_ON) THEN
2656 AMS_UTILITY_PVT.debug_message('campaign_id:'|| l_campaign_id);
2657 END IF;
2658 IF (AMS_LOG_STATEMENT_ON) THEN
2659 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,G_module_name,'inv_id:'
2660 ||p_act_product_rec.inventory_item_id||',cat_id:'||p_act_product_rec.category_id
2661 ||',schId'||p_act_product_rec.act_product_used_by_id ||',campaign_id:'|| l_campaign_id);
2662 END IF;
2663
2664
2665 IF (l_usage is not null
2666 AND l_usage = 'LITE')
2667 THEN
2668 IF ( p_act_product_rec.inventory_item_id <> FND_API.G_MISS_NUM
2669 AND p_act_product_rec.organization_id <> FND_API.G_MISS_NUM )
2670 THEN
2671
2672 IF (AMS_LOG_STATEMENT_ON) THEN
2673 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,G_module_name,'checking if the product association exist in campaign');
2674 END IF;
2675 --- checking if the product association exist in campaign
2676 OPEN check_prod_exist;
2677 FETCH check_prod_exist INTO l_count;
2678 CLOSE check_prod_exist;
2679 IF l_count = 0 THEN
2680 IF (AMS_LOG_STATEMENT_ON) THEN
2681 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,G_module_name,'check_prod_exist does not exist');
2682 END IF;
2683 --- checking if the category, which is assigned to the product,direct assoc with camp
2684 -- or the category exists in its hierarchy association exist in campaign
2685 If ( p_act_product_rec.category_id IS NOT NULL
2686 AND p_Act_product_Rec.category_id <> FND_API.G_MISS_NUM) THEN
2687 IF (AMS_LOG_STATEMENT_ON) THEN
2688 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,G_module_name,'INSIDE Category, cat-set-id not null condn');
2689 END IF;
2690 --checking direct assoc
2691 OPEN check_prod_cat_exist;
2692 FETCH check_prod_cat_exist INTO l_count;
2693 CLOSE check_prod_cat_exist;
2694 IF l_count = 0 THEN
2695 -- checking if the category exist in the hierachy
2696 OPEN check_cat_exist_hrchy;
2697 FETCH check_cat_exist_hrchy INTO l_count;
2698 CLOSE check_cat_exist_hrchy;
2699 IF l_count = 0 THEN
2700 IF (AMS_LOG_STATEMENT_ON) THEN
2701 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,G_module_name,'check_cat_exist does not exist');
2702 END IF;
2703 --- both hierarch doesn't exist raising an error
2704 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2705 THEN
2706 FND_MESSAGE.set_name('AMS', 'AMS_PROD_ASSOC_NOT_IN_CAMP');
2707 FND_MSG_PUB.add;
2708 END IF;
2709 x_return_status := FND_API.g_ret_sts_error;
2710 RETURN;
2711 END IF;
2712 END IF;
2713 ELSE --- if there is no category passed, which means the product assoc doesnot there in campaign
2714 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2715 THEN
2716 FND_MESSAGE.set_name('AMS', 'AMS_PROD_ASSOC_NOT_IN_CAMP');
2717 FND_MSG_PUB.add;
2718 END IF;
2719 x_return_status := FND_API.g_ret_sts_error;
2720 RETURN;
2721 END IF;
2722 END IF;
2723 --- checking if the category association exist. this we would need for just category
2724 -- association.
2725 ELSIF (p_act_product_rec.category_id <> FND_API.G_MISS_NUM
2726 AND p_act_product_rec.category_set_id <> FND_API.G_MISS_NUM)
2727 THEN
2728 OPEN check_cat_exist;
2729 FETCH check_cat_exist INTO l_count;
2730 CLOSE check_cat_exist;
2731 IF l_count = 0
2732 THEN
2733 IF (AMS_LOG_STATEMENT_ON) THEN
2734 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,G_module_name,'check_cat_exist does not exist');
2735 END IF;
2736 -- checking if the category exist in the hierachy
2737 OPEN check_cat_exist_hrchy;
2738 FETCH check_cat_exist_hrchy INTO l_count;
2739 CLOSE check_cat_exist_hrchy;
2740 IF l_count = 0
2741 THEN
2742 IF (AMS_LOG_STATEMENT_ON) THEN
2743 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,G_module_name,'HIERARCHY ALSO DOESNT EXIST');
2744 END IF;
2745 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2746 THEN
2747 FND_MESSAGE.set_name('AMS', 'AMS_CAT_ASSOC_NOT_IN_CAMP');
2748 FND_MSG_PUB.add;
2749 END IF;
2750 x_return_status := FND_API.g_ret_sts_error;
2751 RETURN;
2752 END IF;
2753 END IF;
2754 END IF; -- invId/cat Id chceck
2755 END IF; -- l_usage
2756 END IF; -- obj_type = csch
2757
2758 IF (AMS_LOG_STATEMENT_ON) THEN
2759 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,G_module_name,'check_product_val_for_csch is checked with no errors');
2760 END IF;
2761
2762 IF (AMS_LOG_PROCEDURE_ON) THEN
2763 AMS_UTILITY_PVT.debug_message(AMS_LOG_STATEMENT,G_module_name,l_full_name||'- End');
2764 END IF;
2765
2766 END check_product_val_for_csch;
2767
2768 */
2769
2770 FUNCTION GET_CATEGORY_SET_ID
2771 RETURN NUMBER IS
2772
2773 CURSOR get_cat_Set_id
2774 IS Select category_Set_id
2775 FROM ENI_PROD_DEN_HRCHY_PARENTS_V a
2776 WHERE rownum <2 ;
2777
2778 l_cat_set_id NUMBER;
2779
2780 begin
2781 open get_cat_set_id;
2782 fetch get_Cat_set_id INTO l_cat_set_id;
2783 close get_cat_set_id;
2784 return l_cat_set_id;
2785
2786 End;
2787
2788 FUNCTION GET_LEVEL_TYPE_CODE( p_inv_id IN NUMBER
2789 ,p_Cat_id IN NUMBER)
2790 RETURN VARCHAR2 IS
2791 l_level_type_code varchar2(10):= 'FAMILY';
2792
2793 BEGIN
2794 If p_inv_id is not null
2795 and p_inv_id <> FND_API.G_MISS_NUM
2796 THEN
2797 l_level_type_code := 'PRODUCT';
2798 End If;
2799
2800 Return l_level_type_code;
2801 End;
2802
2803 END AMS_ActProduct_PVT;
[Home] [Help]