[Home] [Help]
PACKAGE BODY: APPS.QP_PARAM_UTIL
Source
1 PACKAGE BODY QP_Param_Util AS
2 /* $Header: QPXUPRMB.pls 120.3 2011/03/03 08:18:02 dnema ship $ */
3 PROCEDURE Get_Valueset_Select( p_valueset_id IN VARCHAR2,
4 x_select_stmt OUT NOCOPY VARCHAR2) IS
5 v_select_clause varchar2(4000) := null;
6 v_valueset_r fnd_vset.valueset_r;
7 v_valueset_dr fnd_vset.valueset_dr;
8 v_table_r fnd_vset.table_r;
9 v_cols varchar2(3000);
10 v_value_set_id number := to_number(p_valueset_id);
11 BEGIN
12 fnd_vset.get_valueset(v_value_set_id,v_valueset_r,v_valueset_dr);
13
14 v_table_r := v_valueset_r.table_info;
15 v_cols := nvl(v_table_r.ID_COLUMN_NAME, nvl(v_table_r.VALUE_COLUMN_NAME, 'null')) || ' attribute_id, ';
16 v_cols := v_cols || nvl(v_table_r.VALUE_COLUMN_NAME, 'null') || ' attribute_name, ';
17 v_cols := v_cols || 'nvl(' || nvl(v_table_r.MEANING_COLUMN_NAME, 'null') || ', ' || nvl(v_table_r.VALUE_COLUMN_NAME, 'null') || ') attribute_meaning ';
18
19 if v_table_r.TABLE_NAME is not null then
20 v_select_clause := 'select ' || v_cols || ' from ' || v_table_r.TABLE_NAME;
21 else
22 v_select_clause := 'select flex_value attribute_id, flex_value_meaning attribute_name, nvl(description, flex_value_meaning) attribute_meaning FROM fnd_flex_values_vl WHERE flex_value_set_id = '|| v_value_set_id;
23 end if;
24
25 if v_table_r.WHERE_CLAUSE is not null then
26 v_select_clause := v_select_clause || ' ' || v_table_r.WHERE_CLAUSE;
27 end if;
28
29 x_select_stmt := 'Select * from (' || v_select_clause || ') PVVO';
30 END Get_Valueset_Select;
31
32 PROCEDURE Insert_Parameter_Values( p_level IN VARCHAR2,
33 p_level_name IN VARCHAR2) IS
34 CURSOR l_param_id_cur(p_level_type VARCHAR2) IS
35 SELECT parameter_id,seeded_value
36 FROM qp_parameters_b
37 WHERE parameter_level = p_level_type;
38
39 l_parameter_value_id NUMBER;
40
41 BEGIN
42 FOR param_rec in l_param_id_cur(p_level) LOOP
43 --bug 11826293
44 --SELECT QP.QP_PARAMETER_VALUES_S.nextval into l_parameter_value_id FROM dual;
45 SELECT QP_PARAMETER_VALUES_S.nextval into l_parameter_value_id FROM dual;
46
47 INSERT INTO qp_parameter_values(parameter_value_id,
48 parameter_id,
49 level_name,
50 seeded_default_value,
51 created_by,
52 creation_date,
53 last_updated_by,
54 last_update_date,
55 last_update_login)
56 VALUES
57 (l_parameter_value_id,
58 param_rec.parameter_id,
59 p_level_name,
60 param_rec.seeded_value,
61 FND_GLOBAL.user_id,
62 sysdate,
63 fnd_global.user_id,
64 sysdate,
65 FND_GLOBAL.login_id);
66 END LOOP;
67 END Insert_Parameter_Values;
68
69 PROCEDURE Delete_Parameter_Values( p_level IN VARCHAR2,
70 p_level_name IN VARCHAR2) IS
71 BEGIN
72
73 DELETE FROM QP_PARAMETER_VALUES
74 WHERE level_name = p_level_name
75 AND parameter_id IN ( SELECT parameter_id FROM QP_PARAMETERS_VL
76 WHERE parameter_level = p_level );
77
78 END Delete_Parameter_Values;
79
80 PROCEDURE Populate_Parameter_Values( p_parameter_id IN NUMBER,
81 p_seeded_value IN VARCHAR2,
82 p_parameter_level IN VARCHAR2) IS
83 CURSOR l_request_type_cur IS
84 SELECT request_type_code from qp_pte_request_types_b ;
85
86 CURSOR l_pte_type_cur IS
87 SELECT lookup_code from qp_lookups where lookup_type = 'QP_PTE_TYPE';
88
89 L_PARAMETER_VALUE_ID NUMBER;
90 BEGIN
91
92 IF p_parameter_level = 'REQ' THEN
93 FOR param_value_rec in l_request_type_cur LOOP
94 --bug 11826293
95 --SELECT QP.QP_PARAMETER_VALUES_S.nextval into l_parameter_value_id FROM dual;
96 SELECT QP_PARAMETER_VALUES_S.nextval into l_parameter_value_id FROM dual;
97
98 INSERT INTO qp_parameter_values(parameter_value_id,
99 parameter_id,
100 level_name,
101 seeded_default_value,
102 created_by,
103 creation_date,
104 last_updated_by,
105 last_update_date,
106 last_update_login)
107 VALUES
108 (l_parameter_value_id,
109 p_parameter_id,
110 param_value_rec.request_type_code,
111 p_seeded_value,
112 FND_GLOBAL.user_id,
113 sysdate,
114 fnd_global.user_id,
115 sysdate,
116 FND_GLOBAL.login_id);
117 END LOOP;
118 END IF;
119
120
121 IF p_parameter_level = 'PTE' THEN
122 FOR param_value_rec in l_pte_type_cur LOOP
123 --bug 11826293
124 --SELECT QP.QP_PARAMETER_VALUES_S.nextval into l_parameter_value_id FROM dual;
125 SELECT QP_PARAMETER_VALUES_S.nextval into l_parameter_value_id FROM dual;
126
127 INSERT INTO qp_parameter_values(parameter_value_id,
128 parameter_id,
129 level_name,
130 seeded_default_value,
131 created_by,
132 creation_date,
133 last_updated_by,
134 last_update_date,
135 last_update_login)
136 VALUES
137 (l_parameter_value_id,
138 p_parameter_id,
139 param_value_rec.lookup_code,
140 p_seeded_value,
141 FND_GLOBAL.user_id,
142 sysdate,
143 fnd_global.user_id,
144 sysdate,
145 FND_GLOBAL.login_id);
146 END LOOP;
147 END IF;
148
149 END Populate_Parameter_Values;
150
151 FUNCTION Get_Parameter_Value( p_level in varchar2,
152 p_level_name in varchar2 ,
153 p_parameter_code in varchar2)
154 RETURN VARCHAR2 AS
155 l_routine VARCHAR2(100) := 'QP_Param_Util.Get_Parameter_Value';
156 l_debug VARCHAR2(3);
157 parameter_value qp_parameter_values.user_assigned_value%type;
158 Begin
159 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
160 SELECT nvl(V.USER_ASSIGNED_VALUE,V.SEEDED_DEFAULT_VALUE)
161 INTO parameter_value
162 from qp_parameters_b B, qp_parameters_tl T, qp_parameter_values V
163 Where T.parameter_id = B.parameter_id
164 and T.Language = userenv('LANG')
165 and B.parameter_id = V.parameter_id
166 and B.PARAMETER_LEVEL = p_level
167 and B.Parameter_code = p_parameter_code
168 and V.Level_Name = p_level_name;
169 RETURN parameter_value;
170 EXCEPTION
171 When OTHERS Then
172 IF l_debug = FND_API.G_TRUE THEN
173 QP_PREQ_GRP.Engine_debug('Exception in '||l_routine||' '||SQLERRM);
174 END IF;
175 RETURN NULL;
176
177 End Get_Parameter_Value;
178
179
180 FUNCTION Is_Seed_User RETURN VARCHAR2 AS
181 BEGIN
182
183 if QP_UTIL.is_seed_user then
184 return 'Y';
185 else
186 return 'N';
187 end if;
188
189 END Is_Seed_User;
190
191
192 END QP_Param_Util;