[Home] [Help]
PACKAGE BODY: APPS.QP_PARAM_UTIL
Source
1 PACKAGE BODY QP_Param_Util AS
2 /* $Header: QPXUPRMB.pls 120.2 2005/10/03 02:26:23 prarasto noship $ */
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 SELECT QP.QP_PARAMETER_VALUES_S.nextval into l_parameter_value_id FROM dual;
44 INSERT INTO qp_parameter_values(parameter_value_id,
45 parameter_id,
46 level_name,
47 seeded_default_value,
48 created_by,
49 creation_date,
50 last_updated_by,
51 last_update_date,
52 last_update_login)
53 VALUES
54 (l_parameter_value_id,
55 param_rec.parameter_id,
56 p_level_name,
57 param_rec.seeded_value,
58 FND_GLOBAL.user_id,
59 sysdate,
60 fnd_global.user_id,
61 sysdate,
62 FND_GLOBAL.login_id);
63 END LOOP;
64 END Insert_Parameter_Values;
65
66 PROCEDURE Delete_Parameter_Values( p_level IN VARCHAR2,
67 p_level_name IN VARCHAR2) IS
68 BEGIN
69
70 DELETE FROM QP_PARAMETER_VALUES
71 WHERE level_name = p_level_name
72 AND parameter_id IN ( SELECT parameter_id FROM QP_PARAMETERS_VL
73 WHERE parameter_level = p_level );
74
75 END Delete_Parameter_Values;
76
77 PROCEDURE Populate_Parameter_Values( p_parameter_id IN NUMBER,
78 p_seeded_value IN VARCHAR2,
79 p_parameter_level IN VARCHAR2) IS
80 CURSOR l_request_type_cur IS
81 SELECT request_type_code from qp_pte_request_types_b ;
82
83 CURSOR l_pte_type_cur IS
84 SELECT lookup_code from qp_lookups where lookup_type = 'QP_PTE_TYPE';
85
86 L_PARAMETER_VALUE_ID NUMBER;
87 BEGIN
88
89 IF p_parameter_level = 'REQ' THEN
90 FOR param_value_rec in l_request_type_cur LOOP
91 SELECT QP.QP_PARAMETER_VALUES_S.nextval into l_parameter_value_id FROM dual;
92
93 INSERT INTO qp_parameter_values(parameter_value_id,
94 parameter_id,
95 level_name,
96 seeded_default_value,
97 created_by,
98 creation_date,
99 last_updated_by,
100 last_update_date,
101 last_update_login)
102 VALUES
103 (l_parameter_value_id,
104 p_parameter_id,
105 param_value_rec.request_type_code,
106 p_seeded_value,
107 FND_GLOBAL.user_id,
108 sysdate,
109 fnd_global.user_id,
110 sysdate,
111 FND_GLOBAL.login_id);
112 END LOOP;
113 END IF;
114
115
116 IF p_parameter_level = 'PTE' THEN
117 FOR param_value_rec in l_pte_type_cur LOOP
118 SELECT QP.QP_PARAMETER_VALUES_S.nextval into l_parameter_value_id FROM dual;
119
120 INSERT INTO qp_parameter_values(parameter_value_id,
121 parameter_id,
122 level_name,
123 seeded_default_value,
124 created_by,
125 creation_date,
126 last_updated_by,
127 last_update_date,
128 last_update_login)
129 VALUES
130 (l_parameter_value_id,
131 p_parameter_id,
132 param_value_rec.lookup_code,
133 p_seeded_value,
134 FND_GLOBAL.user_id,
135 sysdate,
136 fnd_global.user_id,
137 sysdate,
138 FND_GLOBAL.login_id);
139 END LOOP;
140 END IF;
141
142 END Populate_Parameter_Values;
143
144 FUNCTION Get_Parameter_Value( p_level in varchar2,
145 p_level_name in varchar2 ,
146 p_parameter_code in varchar2)
147 RETURN VARCHAR2 AS
148 l_routine VARCHAR2(100) := 'QP_Param_Util.Get_Parameter_Value';
149 l_debug VARCHAR2(3);
150 parameter_value qp_parameter_values.user_assigned_value%type;
151 Begin
152 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
153 SELECT nvl(V.USER_ASSIGNED_VALUE,V.SEEDED_DEFAULT_VALUE)
154 INTO parameter_value
155 from qp_parameters_b B, qp_parameters_tl T, qp_parameter_values V
156 Where T.parameter_id = B.parameter_id
157 and T.Language = userenv('LANG')
158 and B.parameter_id = V.parameter_id
159 and B.PARAMETER_LEVEL = p_level
160 and B.Parameter_code = p_parameter_code
161 and V.Level_Name = p_level_name;
162 RETURN parameter_value;
163 EXCEPTION
164 When OTHERS Then
165 IF l_debug = FND_API.G_TRUE THEN
166 QP_PREQ_GRP.Engine_debug('Exception in '||l_routine||' '||SQLERRM);
167 END IF;
168 RETURN NULL;
169
170 End Get_Parameter_Value;
171
172
173 FUNCTION Is_Seed_User RETURN VARCHAR2 AS
174 BEGIN
175
176 if QP_UTIL.is_seed_user then
177 return 'Y';
178 else
179 return 'N';
180 end if;
181
182 END Is_Seed_User;
183
184
185 END QP_Param_Util;