1 PACKAGE BODY INVPRPRO AS
2 /* $Header: INVPRPRB.pls 120.2 2005/06/20 09:19:27 appldev ship $ */
3
4 function project_where (
5 order_line_id IN number,
6 add_where_clause IN OUT NOCOPY /* file.sql.39 change */ varchar2 ) return number IS
7
8 proj_id number;
9 t_id number;
10 success number;
11
12 BEGIN
13
14 success := 0;
15 add_where_clause := '';
16
17 /* get the project and task based on order line id */
18
19 if order_line_id is null then
20 success := -1;
21 return success;
22 else
23 BEGIN
24 -- Goto the right table based on OE/OM installation
25 if (oe_install.get_active_product = 'ONT') then
26 select project_id, task_id
27 into proj_id, t_id
28 from OE_ORDER_LINES_ALL
29 where line_id = order_line_id;
30 else
31 select project_id, task_id
32 into proj_id, t_id
33 from SO_LINES_ALL
34 where line_id = order_line_id;
35 end if;
36 EXCEPTION
37 when NO_DATA_FOUND then
38 success := -2;
39 return success;
40 when OTHERS then
41 success := -3;
42 return success;
43 END;
44 end if;
45
46 /* project and task obtained succesfully */
47 /* construct the where clause depending on what the values are */
48
49 if ((proj_id IS NULL) AND (t_id IS NULL)) then
50
51 /* no project or task referenced, pick from common inventory only */
52
53 add_where_clause := '((A.LOCATOR_ID IS NULL) OR (A.LOCATOR_ID IS NOT NULL ' ||
54 'AND (EXISTS (SELECT INVENTORY_LOCATION_ID FROM MTL_ITEM_LOCATIONS WHERE INVENTORY_LOCATION_ID = A.LOCATOR_ID AND ORGANIZATION_ID = A.ORGANIZATION_ID AND PROJECT_ID IS NULL AND TASK_ID IS NULL))))';
55
56 elsif ((t_id IS NULL)) then
57
58 /* no task referenced, pick from inventory corresponding to this
59 project only */
60
61 add_where_clause := '((A.LOCATOR_ID IS NOT NULL) AND (EXISTS (SELECT INVENTORY_LOCATION_ID FROM MTL_ITEM_LOCATIONS ' ||
62 'WHERE INVENTORY_LOCATION_ID = A.LOCATOR_ID AND ORGANIZATION_ID = A.ORGANIZATION_ID AND PROJECT_ID = ' || TO_CHAR(proj_id) || ' AND TASK_ID IS NULL)))';
63
64 else
65
66 /* referencing project and task, pick only from those locators */
67
68 add_where_clause := '((A.LOCATOR_ID IS NOT NULL) AND (EXISTS (SELECT INVENTORY_LOCATION_ID FROM MTL_ITEM_LOCATIONS ' ||
69 'WHERE INVENTORY_LOCATION_ID = A.LOCATOR_ID AND ORGANIZATION_ID = A.ORGANIZATION_ID AND PROJECT_ID = ' || TO_CHAR(proj_id) || ' AND TASK_ID = '|| TO_CHAR(t_id) || ')))';
70
71 end if;
72
73 success := 1;
74 return success;
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 success := -4;
79 return success;
80 END project_where;
81
82 END INVPRPRO;
[Home] [Help]