[Home] [Help]
PACKAGE BODY: APPS.AS_FOUNDATION_PVT
Source
1 PACKAGE BODY AS_FOUNDATION_PVT as
2 /* $Header: asxvfoub.pls 120.1 2005/12/06 03:14:26 amagupta noship $ */
3 --
4 -- NAME
5 -- AS_FOUNDATION_PVT
6 --
7 -- HISTORY
8 -- 7/22/98 AWU CREATED
9 --
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):='AS_FOUNDATION_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12):='asxvfoub.pls';
13
14 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
15 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
16 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
17 --G_USER_ID NUMBER := FND_GLOBAL.User_Id;
18 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
19
20 G_MAX NUMBER := 14;
21
22 FUNCTION get_subOrderBy(p_col_choice IN NUMBER, p_col_name IN VARCHAR2)
23 RETURN VARCHAR2 IS
24 l_col_name varchar2(30);
25 begin
26
27 if (p_col_choice is NULL and p_col_name is NOT NULL)
28 or (p_col_choice is NOT NULL and p_col_name is NULL)
29 then
30 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
31 then
32 fnd_message.set_name('AS', 'API_MISSING_ORDERBY_ELEMENT');
33 fnd_msg_pub.add;
34 end if;
35 raise fnd_api.g_exc_error;
36 end if;
37
38 if floor(p_col_choice/10) > G_MAX
39 -- Greater than maximum order by columns
40 or floor(p_col_choice/10) = 0
41 -- only one digit
42 then
43 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
44 then
45 fnd_message.set_name('AS', 'API_INVALID_ORDERBY_CHOICE');
46 fnd_message.set_token('PARAM',p_col_choice, false);
47 fnd_msg_pub.add;
48 end if;
49 raise fnd_api.g_exc_error;
50 return '';
51 end if;
52
53 if (nls_upper(p_col_name) = 'CUSTOMER_NAME')
54 then
55 l_col_name := ' nls_upper' ||'(' ||p_col_name|| ')';
56 else
57 l_col_name := p_col_name;
58 end if;
59 if (mod(p_col_choice, 10) = 1)
60 then
61 return(l_col_name || ' ASC, ');
62 elsif (mod(p_col_choice, 10) = 0)
63 then
64 return(l_col_name || ' DESC, ');
65 else
66 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
67 then
68 fnd_message.set_name('AS', 'API_INVALID_ORDERBY_CHOICE');
69 fnd_message.set_token('PARAM',p_col_choice, false);
70 fnd_msg_pub.add;
71 end if;
72 raise fnd_api.g_exc_error;
73 return '';
74 end if;
75 end;
76
77 PROCEDURE Translate_OrderBy
78 ( p_api_version_number IN NUMBER,
79 p_init_msg_list IN VARCHAR2
80 := FND_API.G_FALSE,
81 p_validation_level IN NUMBER
82 := FND_API.G_VALID_LEVEL_FULL,
83 p_order_by_rec IN UTIL_ORDER_BY_REC_TYPE,
84 x_order_by_clause OUT NOCOPY VARCHAR2,
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_msg_count OUT NOCOPY NUMBER,
87 x_msg_data OUT NOCOPY VARCHAR2
88 ) IS
89
90 TYPE OrderByTabTyp is TABLE of VARCHAR2(80) INDEX BY BINARY_INTEGER;
91 l_orderBy_tbl OrderByTabTyp;
92 i BINARY_INTEGER := 1;
93 l_order_by_clause VARCHAR2(2000) := NULL;
94 l_api_name CONSTANT VARCHAR2(30) := 'Translate_OrderBy';
95 l_api_version_number CONSTANT NUMBER := 2.0;
96 begin
97 -- Standard call to check for call compatibility.
98 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
99 p_api_version_number,
100 l_api_name,
101 G_PKG_NAME)
102 THEN
103 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
104 THEN
105 FND_MESSAGE.Set_Name('AS', 'API_UNEXP_ERROR_IN_PROCESSING');
106 FND_MESSAGE.Set_Token('ROW', 'TRANSLATE_ORDERBY', TRUE);
107 FND_MSG_PUB.ADD;
108 END IF;
109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
110 END IF;
111
112 -- Initialize message list if p_init_msg_list is set to TRUE.
113 IF FND_API.to_Boolean( p_init_msg_list )
114 THEN
115 FND_MSG_PUB.initialize;
116 END IF;
117
118 -- Initialize API return status to success
119 --
120 x_return_status := FND_API.G_RET_STS_SUCCESS;
121
122 --
123 -- API body
124 --
125
126 -- Validate Environment
127
128 IF FND_GLOBAL.User_Id IS NULL
129 THEN
130 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
131 THEN
132 FND_MESSAGE.Set_Name('AS', 'UT_CANNOT_GET_PROFILE_VALUE');
133 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
134 FND_MSG_PUB.ADD;
135 END IF;
136 END IF;
137
138 -- initialize the table to ''.
139 for i in 1..G_MAX loop
140 l_orderBy_tbl(i) := '';
141 end loop;
142
143 -- We allow the choice seqence order such as 41, 20, 11, ...
144 -- So, we need to sort it first(put them into a table),
145 -- then loop through the whole table.
146
147 if (p_order_by_rec.col_1_choice is NOT NULL)
148 then
149 l_orderBy_tbl(floor(p_order_by_rec.col_1_choice/10)) :=
150 get_subOrderBy(p_order_by_rec.col_1_choice,
151 p_order_by_rec.col_1_name);
152 end if;
153 if (p_order_by_rec.col_2_choice is NOT NULL)
154 then
155 l_orderBy_tbl(floor(p_order_by_rec.col_2_choice/10)) :=
156 get_subOrderBy(p_order_by_rec.col_2_choice,
157 p_order_by_rec.col_2_name);
158 end if;
159 if (p_order_by_rec.col_3_choice is NOT NULL)
160 then
161 l_orderBy_tbl(floor(p_order_by_rec.col_3_choice/10)) :=
162 get_subOrderBy(p_order_by_rec.col_3_choice,
163 p_order_by_rec.col_3_name);
164 end if;
165 if (p_order_by_rec.col_4_choice is NOT NULL)
166 then
167 l_orderBy_tbl(floor(p_order_by_rec.col_4_choice/10)) :=
168 get_subOrderBy(p_order_by_rec.col_4_choice,
169 p_order_by_rec.col_4_name);
170 end if;
171 if (p_order_by_rec.col_5_choice is NOT NULL)
172 then
173 l_orderBy_tbl(floor(p_order_by_rec.col_5_choice/10)) :=
174 get_subOrderBy(p_order_by_rec.col_5_choice,
175 p_order_by_rec.col_5_name);
176 end if;
177 if (p_order_by_rec.col_6_choice is NOT NULL)
178 then
179 l_orderBy_tbl(floor(p_order_by_rec.col_6_choice/10)) :=
180 get_subOrderBy(p_order_by_rec.col_6_choice,
181 p_order_by_rec.col_6_name);
182 end if;
183 if (p_order_by_rec.col_7_choice is NOT NULL)
184 then
185 l_orderBy_tbl(floor(p_order_by_rec.col_7_choice/10)) :=
186 get_subOrderBy(p_order_by_rec.col_7_choice,
187 p_order_by_rec.col_7_name);
188 end if;
189 if (p_order_by_rec.col_8_choice is NOT NULL)
190 then
191 l_orderBy_tbl(floor(p_order_by_rec.col_8_choice/10)) :=
192 get_subOrderBy(p_order_by_rec.col_8_choice,
193 p_order_by_rec.col_8_name);
194 end if;
195 if (p_order_by_rec.col_9_choice is NOT NULL)
196 then
197 l_orderBy_tbl(floor(p_order_by_rec.col_9_choice/10)) :=
198 get_subOrderBy(p_order_by_rec.col_9_choice,
199 p_order_by_rec.col_9_name);
200 end if;
201 if (p_order_by_rec.col_10_choice is NOT NULL)
202 then
203 l_orderBy_tbl(floor(p_order_by_rec.col_10_choice/10)) :=
204 get_subOrderBy(p_order_by_rec.col_10_choice,
205 p_order_by_rec.col_10_name);
206 end if;
207 if (p_order_by_rec.col_11_choice is NOT NULL)
208 then
209 l_orderBy_tbl(floor(p_order_by_rec.col_11_choice/10)) :=
210 get_subOrderBy(p_order_by_rec.col_11_choice,
211 p_order_by_rec.col_11_name);
212 end if;
213
214 if (p_order_by_rec.col_12_choice is NOT NULL)
215 then
216 l_orderBy_tbl(floor(p_order_by_rec.col_12_choice/10)) :=
217 get_subOrderBy(p_order_by_rec.col_12_choice,
218 p_order_by_rec.col_12_name);
219 end if;
220
221 if (p_order_by_rec.col_13_choice is NOT NULL)
222 then
223 l_orderBy_tbl(floor(p_order_by_rec.col_13_choice/10)) :=
224 get_subOrderBy(p_order_by_rec.col_13_choice,
225 p_order_by_rec.col_13_name);
226 end if;
227 if (p_order_by_rec.col_14_choice is NOT NULL)
228 then
229 l_orderBy_tbl(floor(p_order_by_rec.col_14_choice/10)) :=
230 get_subOrderBy(p_order_by_rec.col_14_choice,
231 p_order_by_rec.col_14_name);
232 end if;
233
234 for i in 1..G_MAX loop
235 l_order_by_clause := l_order_by_clause || l_orderBy_tbl(i);
236 end loop;
237 l_order_by_clause := rtrim(l_order_by_clause); -- trim ''
238 l_order_by_clause := rtrim(l_order_by_clause, ','); -- trim last ,
239 x_order_by_clause := l_order_by_clause;
240
241 EXCEPTION
242
243 WHEN FND_API.G_EXC_ERROR THEN
244
245 x_return_status := FND_API.G_RET_STS_ERROR ;
246
247 FND_MSG_PUB.Count_And_Get
248 ( p_count => x_msg_count,
249 p_data => x_msg_data
250 );
251
252
253 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
254
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
256
257 FND_MSG_PUB.Count_And_Get
258 ( p_count => x_msg_count,
259 p_data => x_msg_data
260 );
261
262
263 WHEN OTHERS THEN
264
265
266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
267
268 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
269 THEN
270 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
271 END IF;
272
273 FND_MSG_PUB.Count_And_Get
274 ( p_count => x_msg_count,
275 p_data => x_msg_data
276 );
277
278 end;
279
280 PROCEDURE Get_PeriodNames
281 ( p_api_version_number IN NUMBER,
282 p_init_msg_list IN VARCHAR2
283 := FND_API.G_FALSE,
284 p_validation_level IN NUMBER
285 := FND_API.G_VALID_LEVEL_FULL,
286 p_period_rec IN UTIL_PERIOD_REC_TYPE,
287 x_period_tbl OUT NOCOPY UTIL_PERIOD_TBL_TYPE,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_count OUT NOCOPY NUMBER,
290 x_msg_data OUT NOCOPY VARCHAR2
291 ) IS
292
293 cursor get_period_name_csr(l_period_name VARCHAR2, l_period_start_date DATE,
294 l_period_end_date DATE, l_period_set_name VARCHAR2) is
295 select period_name, start_date, end_date
296 from gl_periods
297 where period_name like decode(l_period_name, NULL, '%',
298 FND_API.G_MISS_CHAR, '%',
299 l_period_name)
300 and start_date >= decode(l_period_start_date, NULL, to_date('01/01/1000',
301 'DD/MM/YYYY'),
302 FND_API.G_MISS_CHAR, to_date('01/01/1000',
303 'DD/MM/YYYY'),
304 l_period_start_date)
305 and end_date <= decode(l_period_end_date, NULL, to_date('01/01/9999',
306 'DD/MM/YYYY'),
307 FND_API.G_MISS_CHAR, to_date('01/01/9999',
308 'DD/MM/YYYY'),
309 l_period_end_date)
310 and (period_set_name = l_period_set_name or period_set_name is NULL);
311
312 l_api_name CONSTANT VARCHAR2(30) := 'Get_PeriodNames';
313 l_api_version_number CONSTANT NUMBER := 2.0;
314 l_period_set_name VARCHAR2(15);
315 l_period_name VARCHAR2(20);
316 l_start_date DATE;
317 l_end_date DATE;
318 i BINARY_INTEGER := 0;
319
320 begin
321 -- Standard call to check for call compatibility.
322 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
323 p_api_version_number,
324 l_api_name,
325 G_PKG_NAME)
326 THEN
327 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
328 THEN
329 FND_MESSAGE.Set_Name('AS', 'API_UNEXP_ERROR_IN_PROCESSING');
330 FND_MESSAGE.Set_Token('ROW', 'AS_ACCESSES', TRUE);
331 FND_MSG_PUB.ADD;
332 END IF;
333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
334 END IF;
335
336 -- Initialize message list if p_init_msg_list is set to TRUE.
337 IF FND_API.to_Boolean( p_init_msg_list )
338 THEN
339 FND_MSG_PUB.initialize;
340 END IF;
341
342 -- Initialize API return status to success
343 --
344 x_return_status := FND_API.G_RET_STS_SUCCESS;
345
346 --
347 -- API body
348 --
349
350 -- Validate Environment
351
352 IF FND_GLOBAL.User_Id IS NULL
353 THEN
354 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
355 THEN
356 FND_MESSAGE.Set_Name('AS', 'UT_CANNOT_GET_PROFILE_VALUE');
357 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
358 FND_MSG_PUB.ADD;
359 END IF;
360 END IF;
361
362 l_period_set_name := fnd_profile.value('AS_FORECAST_CALENDAR');
363 open get_period_name_csr(p_period_rec.period_name, p_period_rec.start_date, p_period_rec.end_date,
364 l_period_set_name);
365 loop
366 i := i + 1;
367 fetch get_period_name_csr into l_period_name, l_start_date, l_end_date;
368 exit when get_period_name_csr%NOTFOUND;
369 x_period_tbl(i).period_name := l_period_name;
370 x_period_tbl(i).start_date := l_start_date;
371 x_period_tbl(i).end_date := l_end_date;
372 end loop;
373
374 EXCEPTION
375
376 WHEN FND_API.G_EXC_ERROR THEN
377
378 x_return_status := FND_API.G_RET_STS_ERROR ;
379
380 FND_MSG_PUB.Count_And_Get
381 ( p_count => x_msg_count,
382 p_data => x_msg_data
383 );
384
385
386 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
387
388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
389
390 FND_MSG_PUB.Count_And_Get
391 ( p_count => x_msg_count,
392 p_data => x_msg_data
393 );
394
395
396 WHEN OTHERS THEN
397
398
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
400
401 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
402 THEN
403 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
404 END IF;
405
406 FND_MSG_PUB.Count_And_Get
407 ( p_count => x_msg_count,
408 p_data => x_msg_data
409 );
410 end;
411
412 -- Notes: The valid inputs for p_tablename are:
413 -- G_AS_LOOKUPS VARCHAR2 := 'AS_LOOKUPS',
414 -- G_AR_LOOKUPS VARCHAR2 := 'AR_LOOKUPS',
415 -- G_SO_LOOKUPS VARCHAR2 := 'SO_LOOKUPS',
416 -- G_HR_LOOKUPS VARCHAR2 := 'HR_LOOKUPS',
417 -- G_FND_COMMON_LOOKUPS VARCHAR2 := 'FND_COMMON_LOOKUPS',
418 -- G_CS_LOOKUPS VARCHAR2 := 'CS_LOOKUPS'
419
420 FUNCTION get_lookupMeaning
421 ( p_lookup_type IN VARCHAR2,
422 p_lookup_code IN VARCHAR2,
423 p_tablename IN VARCHAR2
424 ) RETURN VARCHAR2 IS
425
426 cursor as_lookups_meaning_csr(l_lookup_type VARCHAR2,
427 l_lookup_code VARCHAR2) is
428 select meaning
429 from as_lookups
430 where lookup_code = l_lookup_code
431 and lookup_type = l_lookup_type;
432
433 cursor ar_lookups_meaning_csr(l_lookup_type VARCHAR2,
434 l_lookup_code VARCHAR2) is
435 select meaning
436 from ar_lookups
437 where lookup_code = l_lookup_code
438 and lookup_type = l_lookup_type;
439 /*
440 cursor so_lookups_meaning_csr(l_lookup_type VARCHAR2,
441 l_lookup_code VARCHAR2) is
442 select meaning
443 from aso_lookups
444 where lookup_code = l_lookup_code
445 and lookup_type = l_lookup_type;
446 */
447 cursor hr_lookups_meaning_csr(l_lookup_type VARCHAR2,
448 l_lookup_code VARCHAR2) is
449 select meaning
450 from hr_lookups
451 where lookup_code = l_lookup_code
452 and lookup_type = l_lookup_type;
453
454 cursor cs_lookups_meaning_csr(l_lookup_type VARCHAR2,
455 l_lookup_code VARCHAR2) is
456 select meaning
457 from cs_lookups
458 where lookup_code = l_lookup_code
459 and lookup_type = l_lookup_type;
460
461 cursor fnd_common_lookups_meaning_csr(l_lookup_type VARCHAR2,
462 l_lookup_code VARCHAR2) is
463 select meaning
464 from fnd_common_lookups
465 where lookup_code = l_lookup_code
466 and lookup_type = l_lookup_type;
467
468 l_meaning VARCHAR2(80);
469 begin
470 if p_tablename = G_AS_LOOKUPS
471 then
472 open as_lookups_meaning_csr(p_lookup_type, p_lookup_code);
473 fetch as_lookups_meaning_csr into l_meaning;
474 close as_lookups_meaning_csr;
475 return l_meaning;
476 elsif p_tablename = G_AR_LOOKUPS
477 then
478 open ar_lookups_meaning_csr(p_lookup_type, p_lookup_code);
479 fetch ar_lookups_meaning_csr into l_meaning;
480 close ar_lookups_meaning_csr;
481 return l_meaning;
482 /*
483 elsif p_tablename = G_SO_LOOKUPS
484 then
485 open so_lookups_meaning_csr(p_lookup_type, p_lookup_code);
486 fetch so_lookups_meaning_csr into l_meaning;
487 close so_lookups_meaning_csr;
488 return l_meaning;
489 */
490 elsif p_tablename = G_HR_LOOKUPS
491 then
492 open hr_lookups_meaning_csr(p_lookup_type, p_lookup_code);
493 fetch hr_lookups_meaning_csr into l_meaning;
494 close hr_lookups_meaning_csr;
495 return l_meaning;
496 elsif p_tablename = G_CS_LOOKUPS
497 then
498 open cs_lookups_meaning_csr(p_lookup_type, p_lookup_code);
499 fetch cs_lookups_meaning_csr into l_meaning;
500 close cs_lookups_meaning_csr;
501 return l_meaning;
502 elsif p_tablename = G_FND_COMMON_LOOKUPS
503 then
504 open fnd_common_lookups_meaning_csr(p_lookup_type, p_lookup_code);
505 fetch fnd_common_lookups_meaning_csr into l_meaning;
506 close fnd_common_lookups_meaning_csr;
507 return l_meaning;
508 else
509 raise fnd_api.g_exc_error;
510 return NULL;
511 end if;
512
513 end;
514
515 FUNCTION get_unitOfMeasure(p_uom_code IN VARCHAR2) RETURN VARCHAR2 is
516
517 cursor get_unitOfMeasure_csr(l_uom_code VARCHAR2) is
518 select unit_of_measure
519 from mtl_units_of_measure
520 where uom_code = l_uom_code;
521
522 l_uom VARCHAR(25) := NULL;
523 begin
524 open get_unitOfMeasure_csr(p_uom_code);
525 fetch get_unitOfMeasure_csr into l_uom;
526 return l_uom;
527 close get_unitOfMeasure_csr;
528 end;
529
530 FUNCTION get_uomCode(p_uom IN VARCHAR2) RETURN VARCHAR2 is
531
532 cursor get_uomCode_csr(l_uom VARCHAR2) is
533 select uom_code
534 from mtl_units_of_measure
535 where unit_of_measure = l_uom;
536
537 l_uom_code VARCHAR(25) := NULL;
538 begin
539 open get_uomCode_csr(p_uom);
540 fetch get_uomCode_csr into l_uom_code;
541 return l_uom_code;
542 close get_uomCode_csr;
543 end;
544
545 PROCEDURE Get_inventory_items( p_api_version_number IN NUMBER,
546 p_init_msg_list IN VARCHAR2
547 := FND_API.G_FALSE,
548 p_identity_salesforce_id IN NUMBER,
549 p_validation_level IN NUMBER
550 := FND_API.G_VALID_LEVEL_FULL,
551 p_inventory_item_rec IN AS_FOUNDATION_PUB.Inventory_Item_REC_TYPE,
552 x_return_status OUT NOCOPY VARCHAR2,
553 x_msg_count OUT NOCOPY NUMBER,
554 x_msg_data OUT NOCOPY VARCHAR2,
555 x_inventory_item_tbl OUT NOCOPY AS_FOUNDATION_PUB.inventory_item_TBL_TYPE) IS
556
557
558 Cursor C_Get_inv_items_W_inv_id(p_inventory_item_id Number,
559 p_organization_id Number,
560 p_description Varchar2,
561 p_concatenated_segments Varchar2,
562 p_collateral_flag Varchar2,
563 p_bom_item_type Number) IS
564 Select inventory_item_id,
565 organization_id ,
566 enabled_flag,
567 start_date_active,
568 end_date_active,
569 description,
570 concatenated_segments,
571 inventory_item_flag,
572 item_catalog_group_id,
573 Collateral_flag,
574 Primary_UOM_Code,
575 Primary_Unit_of_Measure,
576 inventory_item_status_code,
577 product_family_item_id,
578 bom_item_type
579 From mtl_system_items_kfv
580 Where inventory_item_id = p_inventory_item_id
581 And (description like NVL(p_description, '%')
582 Or description is NULL)
583 And (concatenated_segments like NVL(p_concatenated_segments, '%')
584 Or concatenated_segments is NULL)
585 And organization_id = p_organization_id
586 And (collateral_flag like NVL(p_collateral_flag, '%')
587 Or collateral_flag is NULL)
588 And bom_item_type = NVL(p_bom_item_type, bom_item_type); -- Bom_item_type is not indexed
589
590 Cursor C_Get_inv_items_NO_inv_id(p_organization_id Number,
591 p_description Varchar2,
592 p_concatenated_segments Varchar2,
593 p_collateral_flag Varchar2,
594 p_bom_item_type Number) IS
595 Select inventory_item_id,
596 organization_id ,
597 enabled_flag,
598 start_date_active,
599 end_date_active,
600 description,
601 concatenated_segments,
602 inventory_item_flag,
603 item_catalog_group_id,
604 Collateral_flag,
605 Primary_UOM_Code,
606 Primary_Unit_of_Measure,
607 inventory_item_status_code,
608 product_family_item_id,
609 bom_item_type
610 From mtl_system_items_kfv
611 Where (description like p_description)
612 And (concatenated_segments like NVL(p_concatenated_segments, '%')
613 Or concatenated_segments is NULL)
614 And organization_id = p_organization_id
615 And (collateral_flag like NVL(p_collateral_flag, '%')
616 Or collateral_flag is NULL)
617 And bom_item_type = NVL(p_bom_item_type, bom_item_type) -- Bom_item_type is not indexed
618 UNION
619 Select inventory_item_id,
620 organization_id ,
621 enabled_flag,
622 start_date_active,
623 end_date_active,
624 description,
625 concatenated_segments,
626 inventory_item_flag,
627 item_catalog_group_id,
628 Collateral_flag,
629 Primary_UOM_Code,
630 Primary_Unit_of_Measure,
631 inventory_item_status_code,
632 product_family_item_id,
633 bom_item_type
634 From mtl_system_items_kfv
635 Where (description is NULL)
636 And (concatenated_segments like NVL(p_concatenated_segments, '%')
637 Or concatenated_segments is NULL)
638 And organization_id = p_organization_id
639 And (collateral_flag like NVL(p_collateral_flag, '%')
640 Or collateral_flag is NULL)
641 And bom_item_type = NVL(p_bom_item_type, bom_item_type); -- Bom_item_type is not indexed
642
643
644
645 -- Local API Variables
646 l_api_name CONSTANT VARCHAR2(30) := 'Get_inventory_items';
647 l_api_version_number CONSTANT NUMBER := 2.0;
648
649 -- Local Identity Variables
650
651 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
652
653 -- Locat tmp Variables
654 l_inventory_item_rec AS_FOUNDATION_PUB.inventory_item_Rec_Type;
655
656 -- Local record index
657 l_cur_index Number := 0;
658
659 -- Local return statuses
660 l_return_status Varchar2(1);
661
662 BEGIN
663 -- Standard call to check for call compatibility.
664 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
665 p_api_version_number,
666 l_api_name,
667 G_PKG_NAME)
668 THEN
669 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
670 END IF;
671
672
673 -- Initialize message list if p_init_msg_list is set to TRUE.
674 IF FND_API.to_Boolean( p_init_msg_list ) THEN
675 FND_MSG_PUB.initialize;
676 END IF;
677
678 -- Debug Message
679 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
680 -- THEN
681 -- dbms_output.put_line('AS_Foundation_PVT.Get_inventory_items: Start');
682 -- END IF;
683
684 -- Initialize API return status to success
685 x_return_status := FND_API.G_RET_STS_SUCCESS;
686
687
688 -- Validating Environment
689 /*
690 AS_SALES_MEMBER_PVT.Get_CurrentUser(
691 p_api_version_number => 2.0
692 ,p_salesforce_id => p_identity_salesforce_id
693 ,x_return_status => l_return_status
694 ,x_msg_count => x_msg_count
695 ,x_msg_data => x_msg_data
696 ,x_sales_member_rec => l_identity_sales_member_rec);
697
698 IF l_return_status != FND_API.G_RET_STS_SUCCESS THEN
699 RAISE FND_API.G_EXC_ERROR;
700 END IF;
701 */
702
703
704 -- API BODY
705
706 If (p_inventory_item_rec.Inventory_Item_id IS NULL) Then
707
708 -- Debug Message
709 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
710 -- THEN
711 -- dbms_output.put_line('AS_Foundation_PVT - Open NO ID Cursor to Select');
712 -- END IF;
713
714 l_inventory_item_rec.description := nvl(p_inventory_item_rec.description,'%');
715 Open C_Get_inv_items_NO_inv_id(p_inventory_item_rec.organization_id,
716 l_inventory_item_rec.description,
717 p_inventory_item_rec.concatenated_segments,
718 p_inventory_item_rec.collateral_flag,
719 p_inventory_item_rec.bom_item_type );
720
721 -- Debug Message
722 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
723 -- THEN
724 -- dbms_output.put_line('AS_Foundation_PVT - Fetching');
725 -- END IF;
726
727 Loop
728
729 Fetch C_Get_inv_items_NO_inv_id into
730 l_inventory_item_rec.inventory_item_id,
731 l_inventory_item_rec.organization_id,
732 l_inventory_item_rec.enabled_flag,
733 l_inventory_item_rec.start_date_active,
734 l_inventory_item_rec.end_date_active,
735 l_inventory_item_rec.description,
736 l_inventory_item_rec.concatenated_segments,
737 l_inventory_item_rec.inventory_item_flag,
738 l_inventory_item_rec.item_catalog_group_id,
739 l_inventory_item_rec.Collateral_flag,
740 l_inventory_item_rec.Primary_UOM_Code,
741 l_inventory_item_rec.Primary_Unit_of_Measure,
742 l_inventory_item_rec.inventory_item_status_code,
743 l_inventory_item_rec.product_family_item_id,
744 l_inventory_item_rec.bom_item_type;
745 Exit when C_Get_inv_items_NO_inv_id%NOTFOUND;
746
747 l_cur_index := l_cur_index + 1;
748 x_inventory_item_tbl(l_cur_index) := l_inventory_item_rec;
749 End Loop;
750
751 -- Debug Message
752 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
753 -- dbms_output.put_line('AS_Foundation_PVT - Closing Cursor');
754 -- dbms_output.put_line('AS_Foundation_PVT - retrived lines =' || to_char(l_cur_index));
755 -- END IF;
756
757 Close C_Get_inv_items_NO_inv_id;
758 Else
759
760 -- Debug Message
761 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
762 -- dbms_output.put_line('AS_Foundation_PVT - Open With ID Cursor to Select');
763 -- END IF;
764
765 Open C_Get_inv_items_w_inv_id(p_inventory_item_rec.inventory_item_id,
766 p_inventory_item_rec.organization_id,
767 p_inventory_item_rec.description,
768 p_inventory_item_rec.concatenated_segments,
769 p_inventory_item_rec.collateral_flag,
770 p_inventory_item_rec.bom_item_type );
771
772 -- Debug Message
773 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
774 -- dbms_output.put_line('AS_Foundation_PVT - Fetching');
775 -- END IF;
776
777 Loop
778
779 Fetch C_Get_inv_items_w_inv_id into
780 l_inventory_item_rec.inventory_item_id,
781 l_inventory_item_rec.organization_id,
782 l_inventory_item_rec.enabled_flag,
783 l_inventory_item_rec.start_date_active,
784 l_inventory_item_rec.end_date_active,
785 l_inventory_item_rec.description,
786 l_inventory_item_rec.concatenated_segments,
787 l_inventory_item_rec.inventory_item_flag,
788 l_inventory_item_rec.item_catalog_group_id,
789 l_inventory_item_rec.Collateral_flag,
790 l_inventory_item_rec.Primary_UOM_Code,
791 l_inventory_item_rec.Primary_Unit_of_Measure,
792 l_inventory_item_rec.inventory_item_status_code,
793 l_inventory_item_rec.product_family_item_id,
794 l_inventory_item_rec.bom_item_type;
795 Exit when C_Get_inv_items_w_inv_id%NOTFOUND;
796 --dbms_output.put_line('FOUND');
797 l_cur_index := l_cur_index + 1;
798 x_inventory_item_tbl(l_cur_index) := l_inventory_item_rec;
799 End Loop;
800
801 -- Debug Message
802 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
803 -- dbms_output.put_line('AS_Foundation_PVT - Closing Cursor');
804 -- dbms_output.put_line('AS_Foundation_PVT - retrived lines =' || to_char(l_cur_index));
805 -- END IF;
806
807 Close C_Get_inv_items_w_inv_id;
808
809 End if;
810
811
812 -- API Ending
813
814 x_return_status := FND_API.G_RET_STS_SUCCESS;
815
816 -- Success Message
817 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
818 THEN
819 FND_MESSAGE.Set_Name('AS', 'API_SUCCESS');
820 FND_MESSAGE.Set_Token('ROW', 'AS_Foundation', TRUE);
821 FND_MSG_PUB.Add;
822 END IF;
823
824
825 -- Debug Message
826 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
827 -- THEN
828 -- dbms_output.put_line('AS_Foundation_PVT.Get_inventory_items: End');
829 -- END IF;
830
831 -- Standard call to get message count and if count is 1, get message info.
832 FND_MSG_PUB.Count_And_Get
833 ( p_count => x_msg_count,
834 p_data => x_msg_data
835 );
836
837
838 EXCEPTION
839
840 WHEN FND_API.G_EXC_ERROR THEN
841
842 x_return_status := FND_API.G_RET_STS_ERROR ;
843
844 FND_MSG_PUB.Count_And_Get
845 ( p_count => x_msg_count,
846 p_data => x_msg_data
847 );
848
849
850 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
851
852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
853
854 FND_MSG_PUB.Count_And_Get
855 ( p_count => x_msg_count,
856 p_data => x_msg_data
857 );
858
859 WHEN NO_DATA_FOUND THEN
860
861 x_return_status := FND_API.G_RET_STS_ERROR ;
862
863 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
864 -- THEN
865 -- dbms_output.put_line('AS_Foundation_PVT - Cannot Find Inventory Item');
866 -- END IF;
867
868
869 FND_MSG_PUB.Count_And_Get
870 ( p_count => x_msg_count,
871 p_data => x_msg_data
872 );
873
874
875 WHEN OTHERS THEN
876
877
878 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
879
880 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
881 THEN
882 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
883 END IF;
884
885 FND_MSG_PUB.Count_And_Get
886 ( p_count => x_msg_count,
887 p_data => x_msg_data
888 );
889
890
891 End Get_inventory_items;
892
893 FUNCTION Get_Concatenated_Segments( p_inventory_item_id IN NUMBER
894 ,p_organization_id IN NUMBER) return Varchar2 IS
895
896 Cursor Get_One_Item(p_inventory_item_id Number,
897 p_organization_id Number) IS
898 Select Concatenated_Segments
899 From mtl_system_items_kfv
900 Where inventory_item_id = p_inventory_item_id And
901 organization_id = p_organization_id;
902
903 l_Concatenated_Segments VARCHAR2(50);
904
905 Begin
906
907 Open Get_One_Item(p_inventory_item_id, p_organization_id);
908 Fetch Get_One_Item into l_Concatenated_Segments;
909 Close Get_One_Item;
910
911 return l_Concatenated_Segments;
912
913 EXCEPTION
914
915 WHEN Others THEN
916 return NULL;
917
918 End Get_Concatenated_Segments;
919
920
921 PROCEDURE Get_inventory_itemPrice( p_api_version_number IN NUMBER,
922 p_init_msg_list IN VARCHAR2
923 := FND_API.G_FALSE,
924 p_identity_salesforce_id IN NUMBER,
925 p_validation_level IN NUMBER
926 := FND_API.G_VALID_LEVEL_FULL,
927 p_inventory_item_rec IN AS_FOUNDATION_PUB.Inventory_Item_REC_TYPE,
928 p_price_list_id IN NUMBER,
929 x_return_status OUT NOCOPY VARCHAR2,
930 x_msg_count OUT NOCOPY NUMBER,
931 x_msg_data OUT NOCOPY VARCHAR2,
932 x_list_price OUT NOCOPY NUMBER,
933 x_currency_code OUT NOCOPY VARCHAR2) IS
934
935
936 Cursor C_Get_inv_item_price( p_inventory_item_id Number,
937 p_unit_code Varchar2,
938 p_price_list_id Number) IS
939 Select pr_line.list_price,
940 pr_head.currency_code
941 From aso_i_price_lists_v pr_head, aso_i_price_list_lines_v pr_line
942 Where pr_head.price_list_id = p_price_list_id
943 And pr_line.inventory_item_id = p_inventory_item_id
944 And pr_line.uom_code = p_unit_code
945 And pr_head.price_list_id = pr_line.price_list_id
946 And trunc(sysdate) between nvl(pr_head.start_date_active, trunc(sysdate))
947 And nvl(pr_head.end_date_active, trunc(sysdate))
948 And trunc(sysdate) between nvl(pr_line.start_date_active, trunc(sysdate))
949 And nvl(pr_line.end_date_active, trunc(sysdate))
950 And rownum = 1;
951
952 Cursor C_Get_inv_item_second_pr( p_inventory_item_id Number,
953 p_unit_code Varchar2,
954 p_price_list_id Number) IS
955 Select pr_line.list_price,
956 pr_head.currency_code
957 --From oe_price_lists pr_head, oe_price_list_lines pr_line
958 From qp_price_lists_v pr_head, qp_price_list_lines_v pr_line
959 Where pr_head.price_list_id = p_price_list_id
960 And pr_line.inventory_item_id = p_inventory_item_id
961 And pr_line.unit_code = p_unit_code
962 And pr_head.secondary_price_list_id = pr_line.price_list_id
963 And trunc(sysdate) between nvl(pr_head.start_date_active, trunc(sysdate))
964 And nvl(pr_head.end_date_active, trunc(sysdate))
965 And trunc(sysdate) between nvl(pr_line.start_date_active, trunc(sysdate))
966 And nvl(pr_line.end_date_active, trunc(sysdate))
967 And rownum = 1;
968
969 -- Local API Variables
970 l_api_name CONSTANT VARCHAR2(30) := 'Get_inventory_itemPrice';
971 l_api_version_number CONSTANT NUMBER := 2.0;
972
973 -- Local Identity Variables
974
975 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
976
977 -- Local return statuses
978 l_return_status Varchar2(1);
979 l_list_price Number;
980 l_currency_code Varchar2(30);
981
982 BEGIN
983 -- Standard call to check for call compatibility.
984 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
985 p_api_version_number,
986 l_api_name,
987 G_PKG_NAME)
988 THEN
989 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
990 END IF;
991
992
993 -- Initialize message list if p_init_msg_list is set to TRUE.
994 IF FND_API.to_Boolean( p_init_msg_list ) THEN
995 FND_MSG_PUB.initialize;
996 END IF;
997
998 -- Debug Message
999 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1000 -- THEN
1001 -- dbms_output.put_line('AS_Foundation_PVT.Get_inventory_itemPrice: Start');
1002 -- END IF;
1003
1004 -- Initialize API return status to success
1005 x_return_status := FND_API.G_RET_STS_SUCCESS;
1006
1007
1008 -- Validating Environment
1009 /*
1010 AS_SALES_MEMBER_PVT.Get_CurrentUser(
1011 p_api_version_number => 2.0
1012 ,p_salesforce_id => p_identity_salesforce_id
1013 ,x_return_status => l_return_status
1014 ,x_msg_count => x_msg_count
1015 ,x_msg_data => x_msg_data
1016 ,x_sales_member_rec => l_identity_sales_member_rec);
1017
1018 IF l_return_status != FND_API.G_RET_STS_SUCCESS THEN
1019 RAISE FND_API.G_EXC_ERROR;
1020 END IF;
1021 */
1022
1023
1024 -- API BODY
1025
1026
1027 -- Debug Message
1028 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1029 -- THEN
1030 -- dbms_output.put_line('AS_Foundation_PVT - Open Primary Cursor to Select');
1031 -- END IF;
1032
1033 Open C_Get_inv_item_price (p_inventory_item_rec.inventory_item_id,
1034 p_inventory_item_rec.primary_uom_code,
1035 p_price_list_id );
1036
1037 -- Debug Message
1038 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1039 -- THEN
1040 -- dbms_output.put_line('AS_Foundation_PVT - Fetching');
1041 -- END IF;
1042
1043 -- If first price list yields nothing, use secondary
1044 Fetch C_Get_inv_item_price into l_list_price, l_currency_code;
1045
1046
1047 If (C_Get_inv_item_price%NOTFOUND = TRUE) Then
1048
1049 Open C_Get_inv_item_second_pr (p_inventory_item_rec.inventory_item_id,
1050 p_inventory_item_rec.primary_uom_code,
1051 p_price_list_id );
1052
1053
1054 -- Debug Message
1055 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1056 -- dbms_output.put_line('AS_Foundation_PVT - Open Secondary Cursor');
1057 -- END IF;
1058
1059 Fetch C_Get_inv_item_second_pr into l_list_price, l_currency_code;
1060
1061 If (C_Get_inv_item_second_pr%NOTFOUND = TRUE) Then
1062 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1063 -- dbms_output.put_line('AS_Foundation_PVT - Cannot Find List Price');
1064 -- END IF;
1065 l_list_price := NULL;
1066 x_return_status := FND_API.G_RET_STS_SUCCESS;
1067 Else
1068 x_return_status := FND_API.G_RET_STS_SUCCESS;
1069 End if;
1070
1071 -- Debug Message
1072 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1073 -- dbms_output.put_line('AS_Foundation_PVT - Closing Secondary Cursor');
1074 -- END IF;
1075
1076 Close C_Get_inv_item_second_pr;
1077
1078 Else
1079 x_return_status := FND_API.G_RET_STS_SUCCESS;
1080 End if;
1081
1082 -- Debug Message
1083 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1084 -- dbms_output.put_line('AS_Foundation_PVT - Closing Primary Cursor');
1085 -- END IF;
1086
1087 Close C_Get_inv_item_price;
1088
1089 -- API Ending
1090
1091 x_list_price := l_list_price;
1092 x_currency_code := l_currency_code;
1093
1094
1095 -- Success Message
1096 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1097 THEN
1098 FND_MESSAGE.Set_Name('AS', 'API_SUCCESS');
1099 FND_MESSAGE.Set_Token('ROW', 'AS_Foundation', TRUE);
1100 FND_MSG_PUB.Add;
1101 END IF;
1102
1103
1104 -- Debug Message
1105 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1106 --THEN
1107 -- dbms_output.put_line('AS_Foundation_PVT.Get_inventory_itemPrice: End');
1108 -- END IF;
1109
1110 -- Standard call to get message count and if count is 1, get message info.
1111 FND_MSG_PUB.Count_And_Get
1112 ( p_count => x_msg_count,
1113 p_data => x_msg_data
1114 );
1115
1116
1117 EXCEPTION
1118
1119 WHEN FND_API.G_EXC_ERROR THEN
1120
1121 x_return_status := FND_API.G_RET_STS_ERROR ;
1122
1123 FND_MSG_PUB.Count_And_Get
1124 ( p_count => x_msg_count,
1125 p_data => x_msg_data
1126 );
1127
1128
1129 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1130
1131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1132
1133 FND_MSG_PUB.Count_And_Get
1134 ( p_count => x_msg_count,
1135 p_data => x_msg_data
1136 );
1137
1138 WHEN NO_DATA_FOUND THEN
1139
1140 x_return_status := FND_API.G_RET_STS_ERROR ;
1141
1142 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1143 -- THEN
1144 -- dbms_output.put_line('AS_Foundation_PVT - Cannot Find Inventory Item List Price');
1145 -- END IF;
1146
1147
1148 FND_MSG_PUB.Count_And_Get
1149 ( p_count => x_msg_count,
1150 p_data => x_msg_data
1151 );
1152
1153
1154 WHEN OTHERS THEN
1155
1156
1157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1158
1159 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1160 THEN
1161 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1162 END IF;
1163
1164 FND_MSG_PUB.Count_And_Get
1165 ( p_count => x_msg_count,
1166 p_data => x_msg_data
1167 );
1168
1169
1170 End Get_inventory_itemPrice;
1171
1172 PROCEDURE Get_Price_List_Id(p_api_version_number IN NUMBER,
1173 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1174 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1175 x_return_status OUT NOCOPY VARCHAR2,
1176 x_msg_count OUT NOCOPY NUMBER,
1177 x_msg_data OUT NOCOPY VARCHAR2,
1178 p_currency_code IN VARCHAR2,
1179 x_price_list_id OUT NOCOPY NUMBER) IS
1180 CURSOR l_price_list_id_csr (c_group_id NUMBER, c_currency_code VARCHAR2) IS
1181 SELECT apl.price_list_id
1182 --FROM as_price_lists apl, oe_price_lists spl
1183 FROM as_price_lists apl, qp_price_lists_v spl
1184 WHERE apl.group_id = c_group_id
1185 AND apl.price_list_id = spl.price_list_id
1186 AND spl.currency_code = c_currency_code;
1187
1188 -- Local API Variables
1189 l_api_name CONSTANT VARCHAR2(30) := 'Get_Price_List_Id';
1190 l_api_version_number CONSTANT NUMBER := 2.0;
1191
1192 -- Local return statuses
1193 l_return_status Varchar2(1);
1194 l_price_list_id Number;
1195
1196 BEGIN
1197 -- Standard call to check for call compatibility.
1198 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1199 p_api_version_number,
1200 l_api_name,
1201 G_PKG_NAME)
1202 THEN
1203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1204 END IF;
1205
1206 -- Initialize message list if p_init_msg_list is set to TRUE.
1207 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1208 FND_MSG_PUB.initialize;
1209 END IF;
1210
1211 -- Debug Message
1212 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1213 -- THEN
1214 -- dbms_output.put_line('AS_Foundation_PVT.Get_Price_List_Id: Start');
1215 -- END IF;
1216
1217 -- Initialize API return status to success
1218 x_return_status := FND_API.G_RET_STS_SUCCESS;
1219
1220 -- Debug Message
1221 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1222 -- THEN
1223 -- dbms_output.put_line('AS_Foundation_PVT.Get_Price_List_Id - Open Price list Cursor');
1224 -- END IF;
1225
1226 -- IF FND_PROFILE.Value('AS_MC_PRICE_LIST_GROUP') IS NULL
1227 -- THEN
1228 -- dbms_output.put_line('AS_Foundation_PVT.Get_Price_List_Id - No Price List Group is set');
1229 -- END IF;
1230 OPEN l_price_list_id_csr(FND_PROFILE.Value('AS_MC_PRICE_LIST_GROUP'),p_currency_code);
1231
1232 -- Debug Message
1233 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1234 -- THEN
1235 -- dbms_output.put_line('AS_Foundation_PVT.Get_Price_List_Id - Fetching');
1236 -- END IF;
1237
1238 FETCH l_price_list_id_csr INTO l_price_list_id;
1239 -- Fix bug 858247 Jshang, when price list id is missing, set it to NULL instead of 0
1240 IF l_price_list_id_csr%NOTFOUND THEN
1241 -- l_price_list_id := 0;
1242 l_price_list_id := NULL;
1243 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1244 -- dbms_output.put_line('AS_FOUNDATION_PVT.Get_Price_List_Id - No Price List Id is found');
1245 -- END IF;
1246 x_return_status := FND_API.G_RET_STS_SUCCESS;
1247 Else
1248 x_return_status := FND_API.G_RET_STS_SUCCESS;
1249 END IF;
1250 CLOSE l_price_list_id_csr;
1251 x_price_list_id := l_price_list_id;
1252
1253
1254 -- Success Message
1255 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1256 THEN
1257 FND_MESSAGE.Set_Name('AS', 'API_SUCCESS');
1258 FND_MESSAGE.Set_Token('ROW', 'AS_Foundation', TRUE);
1259 FND_MSG_PUB.Add;
1260 END IF;
1261
1262 -- Debug Message
1263 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1264 -- THEN
1265 -- dbms_output.put_line('AS_Foundation_PVT.Get_Price_List_Id: End');
1266 -- END IF;
1267
1268 -- Standard call to get message count and if count is 1, get message info.
1269 FND_MSG_PUB.Count_And_Get
1270 ( p_count => x_msg_count,
1271 p_data => x_msg_data
1272 );
1273
1274 EXCEPTION
1275
1276 WHEN FND_API.G_EXC_ERROR THEN
1277 x_return_status := FND_API.G_RET_STS_ERROR ;
1278 FND_MSG_PUB.Count_And_Get
1279 ( p_count => x_msg_count,
1280 p_data => x_msg_data
1281 );
1282 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1284 FND_MSG_PUB.Count_And_Get
1285 ( p_count => x_msg_count,
1286 p_data => x_msg_data
1287 );
1288 WHEN NO_DATA_FOUND THEN
1289 x_return_status := FND_API.G_RET_STS_ERROR ;
1290 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1291 -- THEN
1292 -- dbms_output.put_line('AS_Foundation_PVT. - Cannot Find Price List Id');
1293 -- END IF;
1294 FND_MSG_PUB.Count_And_Get
1295 ( p_count => x_msg_count,
1296 p_data => x_msg_data
1297 );
1298 WHEN OTHERS THEN
1299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1300 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1301 THEN
1302 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1303 END IF;
1304 FND_MSG_PUB.Count_And_Get
1305 ( p_count => x_msg_count,
1306 p_data => x_msg_data
1307 );
1308
1309 END Get_Price_List_Id;
1310
1311
1312
1313 PROCEDURE Get_Price_Info(p_api_version_number IN NUMBER,
1314 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1315 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1316 p_inventory_item_rec IN AS_FOUNDATION_PUB.Inventory_Item_REC_TYPE DEFAULT AS_FOUNDATION_PUB.G_MISS_INVENTORY_ITEM_REC,
1317 p_secondary_interest_code_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1318 p_currency_code IN VARCHAR2,
1319 x_return_status OUT NOCOPY VARCHAR2,
1320 x_msg_count OUT NOCOPY NUMBER,
1321 x_msg_data OUT NOCOPY VARCHAR2,
1322 x_price_list_id OUT NOCOPY NUMBER,
1323 x_price OUT NOCOPY NUMBER) IS
1324
1325 CURSOR l_secondary_price_csr(c_secondary_code_id NUMBER,
1326 c_currency_code VARCHAR2) IS
1327 SELECT icm.price
1328 FROM AS_INTEREST_CODES_MC icm
1329 WHERE icm.interest_code_id = c_secondary_code_id
1330 AND icm.currency_code = c_currency_code;
1331
1332 -- Local API Variables
1333 l_api_name CONSTANT VARCHAR2(30) := 'Get_Price_Info';
1334 l_api_version_number CONSTANT NUMBER := 2.0;
1335
1336 -- Local return statuses
1337 l_return_status Varchar2(1);
1338 l_price_list_id Number;
1339 l_price Number;
1340 l_currency_code VARCHAR2(15);
1341 BEGIN
1342 -- Standard call to check for call compatibility.
1343 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1344 p_api_version_number,
1345 l_api_name,
1346 G_PKG_NAME)
1347 THEN
1348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1349 END IF;
1350
1351 -- Initialize message list if p_init_msg_list is set to TRUE.
1352 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1353 FND_MSG_PUB.initialize;
1354 END IF;
1355
1356 -- Debug Message
1357 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1358 -- THEN
1359 -- dbms_output.put_line('AS_Foundation_PVT.Get_Price_Info: Start');
1360 -- END IF;
1361
1362 -- Initialize API return status to success
1363 x_return_status := FND_API.G_RET_STS_SUCCESS;
1364
1365 -- Check inventory item id, if it's not null, use it to get the price list id and price
1366 IF (p_inventory_item_rec.inventory_item_id <> FND_API.G_MISS_NUM AND p_inventory_item_rec.inventory_item_id IS NOT NULL)
1367 THEN
1368 Get_Price_List_Id(p_api_version_number => 2.0,
1369 p_init_msg_list => FND_API.G_FALSE,
1370 x_return_status => l_return_status,
1371 x_msg_count => x_msg_count,
1372 x_msg_data => x_msg_data,
1373 p_currency_code => p_currency_code,
1374 x_price_list_id => l_price_list_id);
1375 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1376 raise FND_API.G_EXC_ERROR;
1377 END IF;
1378 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1379 -- dbms_output.put_line('AS_FOUNDATION_PVT.Get_Price_Info - Price_list_id : ' || to_char(l_price_list_id));
1380 -- END IF;
1381 Get_inventory_itemPrice( p_api_version_number => 2.0,
1382 p_init_msg_list => FND_API.G_FALSE,
1383 p_identity_salesforce_id => NULL,
1384 p_inventory_item_rec => p_inventory_item_rec,
1385 p_price_list_id => l_price_list_id,
1386 x_return_status => l_return_status,
1387 x_msg_count => x_msg_count,
1388 x_msg_data => x_msg_data,
1389 x_list_price => l_price,
1390 x_currency_code => l_currency_code);
1391 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1392 raise FND_API.G_EXC_ERROR;
1393 END IF;
1394 x_price_list_id := l_price_list_id;
1395 x_price := l_price;
1396 Elsif (p_secondary_interest_code_id IS NOT NULL) THEN
1397 open l_secondary_price_csr(p_secondary_interest_code_id,p_currency_code);
1398 fetch l_secondary_price_csr into l_price;
1399 IF l_secondary_price_csr%NOTFOUND THEN
1400 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1401 -- dbms_output.put_line('AS_Foundation_PVT.Get_Price_Info - Cannot find price');
1402 -- END IF;
1403 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
1404 FND_MESSAGE.Set_Name('AS','FC_SEC_NO_LIST_PRICE');
1405 FND_MSG_PUB.ADD;
1406 END IF;
1407 l_price := NULL;
1408 -- close l_secondary_price_csr;
1409 -- raise FND_API.G_EXC_ERROR;
1410 END IF;
1411 close l_secondary_price_csr;
1412 Else
1413 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1414 -- dbms_output.put_line('AS_Foundation_PVT.Get_Price_Info - Wrong Parameter');
1415 -- END IF;
1416 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1417 FND_MESSAGE.Set_Name('AS','API_UNEXP_ERROR_IN_PROCESSING');
1418 FND_MESSAGE.Set_Token('ROW','AS_FOUNDATION');
1419 FND_MSG_PUB.ADD;
1420 END IF;
1421 raise FND_API.G_EXC_ERROR;
1422 END IF;
1423 x_price_list_id := l_price_list_id;
1424 x_price := l_price;
1425 -- Success Message
1426 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1427 THEN
1428 FND_MESSAGE.Set_Name('AS', 'API_SUCCESS');
1429 FND_MESSAGE.Set_Token('ROW', 'AS_Foundation', TRUE);
1430 FND_MSG_PUB.Add;
1431 END IF;
1432
1433 -- Debug Message
1434 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1435 -- THEN
1436 -- dbms_output.put_line('AS_Foundation_PVT.Get_Price_Info: End');
1437 -- END IF;
1438
1439 -- Standard call to get message count and if count is 1, get message info.
1440 FND_MSG_PUB.Count_And_Get
1441 ( p_count => x_msg_count,
1442 p_data => x_msg_data
1443 );
1444
1445 EXCEPTION
1446
1447 WHEN FND_API.G_EXC_ERROR THEN
1448 x_return_status := FND_API.G_RET_STS_ERROR ;
1449 FND_MSG_PUB.Count_And_Get
1450 ( p_count => x_msg_count,
1451 p_data => x_msg_data
1452 );
1453 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1455 FND_MSG_PUB.Count_And_Get
1456 ( p_count => x_msg_count,
1457 p_data => x_msg_data
1458 );
1459 WHEN NO_DATA_FOUND THEN
1460 x_return_status := FND_API.G_RET_STS_ERROR ;
1461 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1462 -- THEN
1463 -- dbms_output.put_line('AS_Foundation_PVT. - Cannot Find Price List Id');
1464 -- END IF;
1465 FND_MSG_PUB.Count_And_Get
1466 ( p_count => x_msg_count,
1467 p_data => x_msg_data
1468 );
1469 WHEN OTHERS THEN
1470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1471 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1472 THEN
1473 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1474 END IF;
1475 FND_MSG_PUB.Count_And_Get
1476 ( p_count => x_msg_count,
1477 p_data => x_msg_data
1478 );
1479
1480 END Get_Price_Info;
1481
1482 -- Start of Comments
1483 --
1484 -- API name : Check_Volume_Amount
1485 -- Type :
1486 -- Pre-reqs :
1487 -- Function :
1488 -- This api takes inventory_item_rec, secondary_interest_code_id, currency_code, volume
1489 -- and amount as the input, it will compute volume or amount if either of them is missed
1490 -- or check the consistency between them if both of them have been set a value.
1491 --
1492 -- Parameters :
1493 -- IN :
1494 -- p_api_version_number IN NUMBER,
1495 -- p_init_msg_list IN VARCHAR2
1496 -- := FND_API.G_FALSE
1497 -- p_validation_level IN NUMBER
1498 -- := FND_API.G_VALID_LEVEL_FULL
1499 -- p_inventory_item_rec IN AS_FOUNDATION_PUB.Inventory_Item_REC_TYPE
1500 -- DEFAULT AS_FOUNDATION_PUB.G_MISS_INVENTORY_ITEM_REC
1501 -- p_secondary_interest_code_id IN NUMBER
1502 -- DEFAULT FND_API.G_MISS_NUM
1503 -- p_currency_code IN VARCHAR2
1504 -- p_volume IN NUMBER
1505 -- DEFAULT FND_API.G_MISS_NUM
1506 -- p_amount IN NUMBER
1507 -- DEFAULT FND_API.G_MISS_NUM
1508 -- x_return_status OUT VARCHAR2
1509 -- x_msg_count OUT NUMBER
1510 -- x_msg_data OUT VARCHAR2
1511 -- x_vol_tolerance_margin OUT NUMBER
1512 -- x_volume OUT NUMBER
1513 -- x_amount OUT NUMBER
1514 -- x_uom_code OUT VARCHAR2
1515 -- x_price_list_id OUT NUMBER
1516 -- x_price OUT NUMBER
1517 --
1518 -- Version :
1519 --
1520 -- HISTORY
1521 -- 19-Nov-1998 J. Shang Created
1522 -- Note :
1523 -- 1. Inventory item will overwrite the secondary interest code when both of them are set
1524 -- 2. The values needed in pass-in parameter p_inventory_item_rec maybe:
1525 -- Item_Id, Organization_Id and uom_code
1526 -- Among them, if uom_code is not set, the value in the table will be used
1527 -- 3. p_volume and p_amount are a pair of volume_amount to be checking. If one of them is missed,
1528 -- this API will compute the other one.
1529 -- 4. If the profile value tells that the volume forecasting is disabled, all parameters from
1530 -- x_vol_tolerance_margin to x_price will be NULL and x_return_status is FND_API.G_RET_STS_SUCCESS.
1531 --End of Comments
1532 PROCEDURE Check_Volume_Amount(p_api_version_number IN NUMBER,
1533 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1534 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1535 p_inventory_item_rec IN AS_FOUNDATION_PUB.Inventory_Item_REC_TYPE DEFAULT AS_FOUNDATION_PUB.G_MISS_INVENTORY_ITEM_REC,
1536 p_secondary_interest_code_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1537 p_currency_code IN VARCHAR2,
1538 p_volume IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1539 p_amount IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1540 x_return_status OUT NOCOPY VARCHAR2,
1541 x_msg_count OUT NOCOPY NUMBER,
1542 x_msg_data OUT NOCOPY VARCHAR2,
1543 x_vol_tolerance_margin OUT NOCOPY NUMBER,
1544 x_volume OUT NOCOPY NUMBER,
1545 x_amount OUT NOCOPY NUMBER,
1546 x_uom_code OUT NOCOPY VARCHAR2,
1547 x_price_list_id OUT NOCOPY NUMBER,
1548 x_price OUT NOCOPY NUMBER) IS
1549 -- Local API Variables
1550 l_api_name CONSTANT VARCHAR2(30) := 'Check_Quantity_Revenue';
1551 l_api_version_number CONSTANT NUMBER := 2.0;
1552
1553 l_volume_forecast_enable VARCHAR2(1);
1554 l_inv_item_tbl AS_FOUNDATION_PUB.Inventory_Item_tbl_type;
1555 l_return_status VARCHAR2(1);
1556 l_msg_count NUMBER;
1557 l_msg_data VARCHAR2(2000);
1558 l_price_list_id NUMBER;
1559 l_price NUMBER;
1560 l_amount_floor NUMBER;
1561 l_amount_ceiling NUMBER;
1562 l_vol_tolerance_margin NUMBER := TO_NUMBER(NVL(FND_PROFILE.Value('AS_PRICE_VOLUME_TOLERANCE_MARGIN'),'100'));
1563 BEGIN
1564 -- Standard call to check for call compatibility.
1565 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1566 p_api_version_number,
1567 l_api_name,
1568 G_PKG_NAME) THEN
1569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1570 END IF;
1571
1572 -- Initialize message list if p_init_msg_list is set to TRUE.
1573 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1574 FND_MSG_PUB.initialize;
1575 END IF;
1576
1577 -- Debug Message
1578 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1579 -- dbms_output.put_line('AS_Foundation_PVT.Check_Volume_Amount: Start');
1580 -- END IF;
1581
1582 -- Initialize API return status to success
1583 x_return_status := FND_API.G_RET_STS_SUCCESS;
1584
1585 -- API body
1586 l_volume_forecast_enable := NVL(FND_PROFILE.Value('AS_VOLUME_FORECASTING_ENABLED'),'N');
1587 -- Debug Message
1588 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1589 -- dbms_output.put_line('AS_Foundation_PVT - AS_VOLUME_FORECASTING_ENABLED:' || l_volume_forecast_enable);
1590 -- END IF;
1591 x_vol_tolerance_margin := l_vol_tolerance_margin;
1592 IF (p_inventory_item_rec.inventory_item_id IS NOT NULL) THEN
1593 AS_FOUNDATION_PUB.Get_Inventory_items(
1594 p_api_version_number => 2.0,
1595 p_init_msg_list => FND_API.G_TRUE,
1596 p_identity_salesforce_id => NULL,
1597 p_inventory_item_rec => p_inventory_item_rec,
1598 x_return_status => l_return_status,
1599 x_msg_count => l_msg_count,
1600 x_msg_data => l_msg_data,
1601 x_inventory_item_tbl => l_inv_item_tbl);
1602 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1603 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1604 -- dbms_output.put_line('AS_FOUNDATION_PVT - Item : Not Found');
1605 -- END IF;
1606 raise FND_API.G_EXC_ERROR;
1607 END IF;
1608 IF (p_inventory_item_rec.Primary_UOM_Code IS NOT NULL) THEN
1609 l_inv_item_tbl(1).Primary_UOM_Code := p_inventory_item_rec.Primary_UOM_Code;
1610 END IF;
1611 x_uom_code := l_inv_item_tbl(1).Primary_UOM_Code;
1612 -- Debug Message
1613 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1614 -- dbms_output.put_line('AS_Foundation_PVT - UOM:' || x_uom_code);
1615 -- END IF;
1616 Get_Price_Info(
1617 p_api_version_number => 2.0,
1618 p_init_msg_list => FND_API.G_FALSE,
1619 p_inventory_item_rec => l_inv_item_tbl(1),
1620 p_secondary_interest_code_id => NULL,
1621 p_currency_code => p_currency_code,
1622 x_return_status => l_return_status,
1623 x_msg_count => l_msg_count,
1624 x_msg_data => l_msg_data,
1625 x_price_list_id => l_price_list_id,
1626 x_price => l_price);
1627 Elsif (p_secondary_interest_code_id IS NOT NULL) THEN
1628 Get_Price_Info(
1629 p_api_version_number => 2.0,
1630 p_init_msg_list => FND_API.G_FALSE,
1631 p_inventory_item_rec => NULL,
1632 p_secondary_interest_code_id => p_secondary_interest_code_id,
1633 p_currency_code => p_currency_code,
1634 x_return_status => l_return_status,
1635 x_msg_count => l_msg_count,
1636 x_msg_data => l_msg_data,
1637 x_price_list_id => l_price_list_id,
1638 x_price => l_price);
1639 END IF;
1640 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1641 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1642 -- dbms_output.put_line('AS_FOUNDATION_PVT - Price : Not Found');
1643 -- END IF;
1644 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1645 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
1646 FND_MESSAGE.Set_Token('COLUMN','Price', FALSE);
1647 FND_MSG_PUB.ADD;
1648 END IF;
1649 raise FND_API.G_EXC_ERROR;
1650 END IF;
1651
1652 IF l_volume_forecast_enable = 'Y' THEN
1653 IF ((p_volume = FND_API.G_MISS_NUM AND p_amount = FND_API.G_MISS_NUM) OR l_price IS NULL) THEN
1654 IF l_price IS NULL THEN
1655 IF p_volume = FND_API.G_MISS_NUM THEN
1656 x_volume := NULL;
1657 ELSE
1658 x_volume := p_volume;
1659 END IF;
1660 IF p_amount = FND_API.G_MISS_NUM THEN
1661 x_amount := NULL;
1662 ELSE
1663 x_amount := p_amount;
1664 END IF;
1665 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1666 -- dbms_output.put_line('AS_FOUNDATION_PVT - price is missing');
1667 -- END IF;
1668 ELSE
1669 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1670 --dbms_output.put_line('AS_FOUNDATION_PVT - volume and amount are missing');
1671 -- END IF;
1672 x_volume := NULL;
1673 x_amount := NULL;
1674 END IF;
1675 Elsif (p_volume = FND_API.G_MISS_NUM or p_volume IS NULL) THEN
1676 x_volume := p_amount / l_price;
1677 x_amount := p_amount;
1678 Elsif (p_amount = FND_API.G_MISS_NUM or p_amount IS NULL) THEN
1679 x_amount := p_volume * l_price;
1680 x_volume := p_volume;
1681 Else
1682 x_amount := p_amount;
1683 x_volume := p_volume;
1684 l_amount_floor := p_volume * l_price * (100 - l_vol_tolerance_margin) / 100;
1685 l_amount_ceiling := p_volume * l_price * (100 + l_vol_tolerance_margin) / 100;
1686 IF p_amount > l_amount_ceiling OR p_amount < l_amount_floor THEN
1687 -- IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1688 -- dbms_output.put_line('AS_FOUNDATION_PVT - Validate volume, amount : Exceeds Margin');
1689 -- END IF;
1690 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1691 FND_MESSAGE.Set_Name('AS', 'API_EXCEED_MARGIN');
1692 FND_MSG_PUB.ADD;
1693 END IF;
1694 raise FND_API.G_EXC_ERROR;
1695 END IF;
1696 END IF; -- End of p_volume and p_amount Checking
1697 -- Fix bug 889659, 889809 pass back volume and amount when Volume forecasting is turned off
1698 ELSE
1699 x_vol_tolerance_margin := l_vol_tolerance_margin;
1700 IF p_volume <> FND_API.G_MISS_NUM THEN
1701 x_volume := p_volume;
1702 END IF;
1703 IF p_amount <> FND_API.G_MISS_NUM THEN
1704 x_amount := p_amount;
1705 END IF;
1706 END IF; -- End of checking p_volume_forecast_enable flag
1707 -- API body end
1708
1709 x_price_list_id := l_price_list_id;
1710 x_price := l_price;
1711 -- Success Message
1712 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
1713 FND_MESSAGE.Set_Name('AS', 'API_SUCCESS');
1714 FND_MESSAGE.Set_Token('ROW', 'AS_Foundation', TRUE);
1715 FND_MSG_PUB.Add;
1716 END IF;
1717
1718 -- Debug Message
1719 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1720 -- dbms_output.put_line('AS_Foundation_PVT.Check_Volume_Amount: End');
1721 -- END IF;
1722
1723 -- Standard call to get message count and if count is 1, get message info.
1724 FND_MSG_PUB.Count_And_Get
1725 ( p_count => x_msg_count,
1726 p_data => x_msg_data
1727 );
1728
1729 EXCEPTION
1730 WHEN FND_API.G_EXC_ERROR THEN
1731 x_return_status := FND_API.G_RET_STS_ERROR ;
1732 FND_MSG_PUB.Count_And_Get
1733 ( p_count => x_msg_count,
1734 p_data => x_msg_data
1735 );
1736 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1738 FND_MSG_PUB.Count_And_Get
1739 ( p_count => x_msg_count,
1740 p_data => x_msg_data
1741 );
1742 WHEN NO_DATA_FOUND THEN
1743 x_return_status := FND_API.G_RET_STS_ERROR ;
1744 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1745 -- THEN
1746 -- dbms_output.put_line('AS_Foundation_PVT. - Cannot Find Price List Id');
1747 -- END IF;
1748 FND_MSG_PUB.Count_And_Get
1749 ( p_count => x_msg_count,
1750 p_data => x_msg_data
1751 );
1752 WHEN OTHERS THEN
1753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1754 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1755 THEN
1756 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1757 END IF;
1758 FND_MSG_PUB.Count_And_Get
1759 ( p_count => x_msg_count,
1760 p_data => x_msg_data
1761 );
1762 END Check_Volume_Amount;
1763
1764
1765 PROCEDURE Gen_NoBind_Flex_Where(
1766 p_flex_where_tbl_type IN AS_FOUNDATION_PVT.flex_where_tbl_type,
1767 x_flex_where_clause OUT NOCOPY VARCHAR2) IS
1768 l_flex_where_cl VARCHAR2(2000) := NULL;
1769 BEGIN
1770 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1771 -- dbms_output.put_line('AS_FOUNDATION_PVT Generate Flexfield Where: begin');
1772 -- END IF;
1773
1774 FOR i IN 1..p_flex_where_tbl_type.count LOOP
1775 IF (p_flex_where_tbl_type(i).value IS NOT NULL
1776 AND p_flex_where_tbl_type(i).value <> FND_API.G_MISS_CHAR) THEN
1777 l_flex_where_cl := l_flex_where_cl||' AND '||p_flex_where_tbl_type(i).name
1778 || ' = '''||p_flex_where_tbl_type(i).value||'''';
1779 END IF;
1780 END LOOP;
1781 x_flex_where_clause := l_flex_where_cl;
1782
1783 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1784 -- dbms_output.put_line('AS_FOUNDATION_PVT Generate Flexfield Where: end');
1785 -- END IF;
1786 END;
1787
1788 PROCEDURE Gen_Flexfield_Where(
1789 p_flex_where_tbl_type IN AS_FOUNDATION_PVT.flex_where_tbl_type,
1790 x_flex_where_clause OUT NOCOPY VARCHAR2) IS
1791 l_flex_where_cl VARCHAR2(2000) := NULL;
1792 BEGIN
1793 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1794 -- dbms_output.put_line('AS_FOUNDATION_PVT Generate Flexfield Where: begin');
1795 -- END IF;
1796
1797 FOR i IN 1..p_flex_where_tbl_type.count LOOP
1798 IF (p_flex_where_tbl_type(i).value IS NOT NULL
1799 AND p_flex_where_tbl_type(i).value <> FND_API.G_MISS_CHAR) THEN
1800 l_flex_where_cl := l_flex_where_cl||' AND '||p_flex_where_tbl_type(i).name
1801 || ' = :p_ofso_flex_var'||i;
1802 END IF;
1803 END LOOP;
1804 x_flex_where_clause := l_flex_where_cl;
1805
1806 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1807 -- dbms_output.put_line('AS_FOUNDATION_PVT Generate Flexfield Where: end');
1808 -- END IF;
1809 END;
1810
1811 PROCEDURE Bind_Flexfield_Where(
1812 p_cursor_id IN NUMBER,
1813 p_flex_where_tbl_type IN AS_FOUNDATION_PVT.flex_where_tbl_type) IS
1814 BEGIN
1815 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1816 -- dbms_output.put_line('AS_FOUNDATION_PVT Bind Flexfield Where: begin');
1817 -- END IF;
1818
1819 FOR i IN 1..p_flex_where_tbl_type.count LOOP
1820 IF (p_flex_where_tbl_type(i).value IS NOT NULL
1821 AND p_flex_where_tbl_type(i).value <> FND_API.G_MISS_CHAR) THEN
1822 DBMS_SQL.Bind_Variable(p_cursor_id, ':p_ofso_flex_var'||i,
1823 p_flex_where_tbl_type(i).value);
1824 END IF;
1825 END LOOP;
1826
1827 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1828 -- dbms_output.put_line('AS_FOUNDATION_PVT Bind Flexfield Where: end');
1829 -- END IF;
1830 END;
1831
1832 PROCEDURE Get_Messages (p_message_count IN NUMBER,
1833 p_msgs OUT NOCOPY VARCHAR2
1834 )
1835 IS
1836 l_msg_list VARCHAR2(5000) := '
1837 ';
1838 l_temp_msg VARCHAR2(2000);
1839 l_appl_short_name VARCHAR2(20) ;
1840 l_message_name VARCHAR2(30) ;
1841
1842 l_id NUMBER;
1843 l_message_num NUMBER;
1844
1845 Cursor Get_Appl_Id (x_short_name VARCHAR2) IS
1846 SELECT application_id
1847 FROM fnd_application_vl
1848 WHERE application_short_name = x_short_name;
1849
1850 Cursor Get_Message_Num (x_msg VARCHAR2, x_id NUMBER, x_lang_id NUMBER) IS
1851 SELECT msg.message_number
1852 FROM fnd_new_messages msg, fnd_languages_vl lng
1853 WHERE msg.message_name = x_msg
1854 and msg.application_id = x_id
1855 and lng.LANGUAGE_CODE = msg.language_code
1856 and lng.language_id = x_lang_id;
1857 BEGIN
1858 FOR l_count in 1..p_message_count LOOP
1859
1860 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_true);
1861 fnd_message.parse_encoded(l_temp_msg, l_appl_short_name, l_message_name);
1862 OPEN Get_Appl_Id (l_appl_short_name);
1863 FETCH Get_Appl_Id into l_id;
1864 CLOSE Get_Appl_Id;
1865
1866 l_message_num := NULL;
1867 IF l_id is not NULL
1868 THEN
1869 OPEN Get_Message_Num (l_message_name, l_id,
1870 to_number(NVL(FND_PROFILE.Value('LANGUAGE'), '0')));
1871 FETCH Get_Message_Num into l_message_num;
1872 CLOSE Get_Message_Num;
1873 END IF;
1874
1875 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_previous, fnd_api.g_true);
1876
1877 IF NVL(l_message_num, 0) <> 0
1878 THEN
1879 l_temp_msg := 'APP-' || to_char(l_message_num) || ': ';
1880 ELSE
1881 l_temp_msg := NULL;
1882 END IF;
1883
1884 IF l_count = 1
1885 THEN
1886 l_msg_list := l_msg_list || l_temp_msg ||
1887 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
1888 ELSE
1889 l_msg_list := l_msg_list || l_temp_msg ||
1890 fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
1891 END IF;
1892
1893 l_msg_list := l_msg_list || '
1894 ';
1895
1896 END LOOP;
1897
1898 p_msgs := l_msg_list;
1899
1900 END Get_Messages;
1901
1902 END AS_FOUNDATION_PVT;