1 PACKAGE BODY eni_param_util_pkg AS
2 /*$Header: ENIPUTPB.pls 120.1 2006/02/22 04:51:10 lparihar noship $*/
3 -- Retrieve the default parameter values
4 -- for the Product Performance - Development
5 -- parameter portlet
6 FUNCTION get_dbi_pme_params RETURN VARCHAR2 IS
7
8 CURSOR c_first_item IS
9 SELECT
10 id,
11 value
12 FROM
13 eni_oltp_item_star
14 WHERE rownum = 1;
15
16 r_first_item c_first_item%ROWTYPE;
17
18 BEGIN
19
20 OPEN c_first_item;
21
22 BEGIN
23 FETCH c_first_item into r_first_item;
24 EXCEPTION
25 WHEN OTHERS THEN
26 NULL;
27 END;
28
29 RETURN '&'||'ENI_ITEM_ORG='||r_first_item.id;
30
31 EXCEPTION
32 WHEN OTHERS THEN
33 NULL;
34
35 END get_dbi_pme_params;
36
37 --Returns a random ItemID for the org value stored in g_default_org. If this variable is NULL it picks
38 --up a random record.
39 FUNCTION get_dbi_pme_c_params RETURN VARCHAR2 IS
40 l_first_item varchar2(50);
41 BEGIN
42 if eni_param_util_pkg.g_default_org <> null then
43 select id into l_first_item from eni_oltp_item_star
44 where organization_id = to_number(eni_param_util_pkg.g_default_org) and rownum = 1;
45 else
46 select id into l_first_item from eni_oltp_item_star
47 where rownum = 1;
48 end if;
49 eni_param_util_pkg.g_default_org := null;
50 RETURN l_first_item;
51 END get_dbi_pme_c_params;
52
53 FUNCTION get_dbi_pms_params RETURN VARCHAR2 IS
54 BEGIN
55 RETURN '&'||'FND_CATEGORY=2067';
56
57 END get_dbi_pms_params;
58 FUNCTION is_valid_org(
59 p_org_id IN NUMBER,
60 p_resp_id IN NUMBER,
61 p_as_of_date IN VARCHAR2) RETURN VARCHAR2
62 IS
63 CURSOR c_valid_org(resp_id NUMBER, as_of_date VARCHAR2)
64 IS
65 SELECT
66 'Y'
67 FROM
68 /*Bug: 4960454*/
69 fnd_user_resp_groups_all
70 WHERE
71 responsibility_id = resp_id
72 AND as_of_date BETWEEN start_date AND NVL(end_date, SYSDATE)
73 AND user_id = FND_GLOBAL.USER_ID;
74
75 r_valid_org c_valid_org%ROWTYPE;
76 CURSOR c_inv_org(org_id NUMBER)
77 IS
78 SELECT
79 'Y'
80 FROM
81 mtl_parameters
82 WHERE
83 organization_id = org_id;
84
85 r_inv_org c_inv_org%ROWTYPE;
86 BEGIN
87 -- Is the organization an inventory org?
88 OPEN c_inv_org(p_org_id);
89
90 FETCH c_inv_org INTO r_inv_org;
91
92 IF c_inv_org%NOTFOUND THEN
93 RETURN 'N';
94 END IF;
95
96 CLOSE c_inv_org;
97
98 -- Does the user have access to the inventory org?
99 OPEN c_valid_org(p_resp_id, p_as_of_date);
100
101 FETCH c_valid_org INTO r_valid_org;
102
103 IF c_valid_org%NOTFOUND THEN
104 RETURN 'N';
105 END IF;
106
107 CLOSE c_valid_org;
108
109
110
111 RETURN 'Y';
112
113 EXCEPTION
114 WHEN OTHERS THEN
115
116 IF c_valid_org%ISOPEN THEN
117 CLOSE c_valid_org;
118 END IF;
119
120 IF c_inv_org%ISOPEN THEN
121 CLOSE c_inv_org;
122 END IF;
123 RETURN 'N';
124 END is_valid_org;
125
126 --Bug#3967047
127 --Retrieve an organization id and store in the global variable g_default_org for the
128 --API get_dbi_pme_c_params so that it fetches an ItemID from the same org.
129 FUNCTION get_dbi_pme_org RETURN VARCHAR2 IS
130 l_first_item_org varchar2(50);
131
132 BEGIN
133 SELECT organization_id into l_first_item_org FROM eni_oltp_item_star
134 WHERE rownum = 1;
135
136 g_default_org := l_first_item_org;
137
138 RETURN l_first_item_org;
139 END get_dbi_pme_org;
140
141
142 END eni_param_util_pkg;
[Home] [Help]