1 PACKAGE BODY GMD_PARAMETERS_DTL_PKG AS
2 /* $Header: GMDPRMDB.pls 120.6 2006/05/24 12:56:25 rkrishan noship $ */
3
4
5 /*======================================================================
6 -- PROCEDURE :
7 -- INSERT_ROW
8 --
9 -- DESCRIPTION:
10 -- This particular procedure is used to insert rows in detail table
11 --
12 -- HISTORY
13 -- Sriram.S 05-NOV-2004 Created
14 --===================================================================== */
15 PROCEDURE INSERT_ROW (
16 X_ROWID OUT NOCOPY VARCHAR2,
17 X_PARAMETER_LINE_ID IN NUMBER,
18 X_PARAMETER_ID IN NUMBER,
19 X_PARM_TYPE IN NUMBER,
20 X_PARAMETER_NAME IN VARCHAR2,
21 X_PARAMETER_VALUE IN VARCHAR2,
22 X_CREATION_DATE IN DATE,
23 X_CREATED_BY IN NUMBER,
24 X_LAST_UPDATE_DATE IN DATE,
25 X_LAST_UPDATED_BY IN NUMBER,
26 X_LAST_UPDATE_LOGIN IN NUMBER
27 ) IS
28
29 CURSOR C IS
30 SELECT ROWID
31 FROM GMD_PARAMETERS_DTL
32 WHERE PARAMETER_LINE_ID = X_PARAMETER_LINE_ID;
33 BEGIN
34
35 INSERT INTO GMD_PARAMETERS_DTL (
36 PARAMETER_ID,
37 PARAMETER_LINE_ID,
38 PARAMETER_TYPE,
39 PARAMETER_NAME,
40 PARAMETER_VALUE,
41 CREATION_DATE,
42 CREATED_BY,
43 LAST_UPDATE_DATE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_LOGIN
46 ) VALUES (
47 X_PARAMETER_ID,
48 X_PARAMETER_LINE_ID,
49 X_PARM_TYPE,
50 X_PARAMETER_NAME,
51 X_PARAMETER_VALUE,
52 SYSDATE,
53 X_LAST_UPDATED_BY,
54 X_LAST_UPDATE_DATE,
55 X_LAST_UPDATED_BY,
56 X_LAST_UPDATE_LOGIN
57 );
58
59
60 OPEN C;
61 FETCH C INTO X_ROWID;
62 IF (C%NOTFOUND) THEN
63 CLOSE C;
64 RAISE NO_DATA_FOUND;
65 END IF;
66 CLOSE C;
67
68 EXCEPTION WHEN OTHERS THEN
69 NULL;
70 END INSERT_ROW;
71
72 /*======================================================================
73 -- PROCEDURE :
74 -- LOCK_ROW
75 --
76 -- DESCRIPTION:
77 -- This particular procedure is used to lock rows in detail table
78 --
79 -- HISTORY
80 -- Sriram.S 05-NOV-2004 Created
81 --===================================================================== */
82
83 PROCEDURE LOCK_ROW (
84 X_PARAMETER_LINE_ID IN NUMBER,
85 X_PARAMETER_ID IN NUMBER,
86 X_PARM_TYPE IN NUMBER,
87 X_PARAMETER_NAME IN VARCHAR2,
88 X_PARAMETER_VALUE IN VARCHAR2,
89 X_LOOKUP_TYPE IN VARCHAR2
90 ) IS
91
92 CURSOR C IS
93 SELECT
94 PARAMETER_ID,
95 PARAMETER_TYPE,
96 PARAMETER_NAME,
97 PARAMETER_VALUE
98 FROM GMD_PARAMETERS_DTL
99 WHERE PARAMETER_LINE_ID = X_PARAMETER_LINE_ID
100 FOR UPDATE OF PARAMETER_LINE_ID NOWAIT;
101
102 CURSOR cur_gem_lookups IS
103 SELECT 1
104 from GEM_LOOKUPS
105 where lookup_code =X_PARAMETER_NAME
106 and lookup_type =X_LOOKUP_TYPE;
107
108 RECINFO C%ROWTYPE;
109 GEMINFO Cur_gem_lookups%ROWTYPE;
110 NEW_DATA_ENTRY EXCEPTION;
111 BEGIN
112 OPEN C;
113 FETCH C INTO RECINFO;
114
115
116
117 IF (C%NOTFOUND) THEN
118 CLOSE C;
119 OPEN cur_gem_lookups;
120 FETCH cur_gem_lookups INTO GEMINFO;
121 if cur_gem_lookups%NOTFOUND THEN
122 CLOSE cur_gem_lookups;
123 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
124 APP_EXCEPTION.RAISE_EXCEPTION;
125 ELSE
126 NULL;
127 RAISE NEW_DATA_ENTRY;
128 END IF;
129 END IF;
130 IF c%ISOPEN THEN
131 CLOSE C;
132 END IF;
133 IF cur_gem_lookups%ISOPEN THEN
134 Close cur_gem_lookups;
135 END IF;
136 IF ( (RECINFO.PARAMETER_ID = X_PARAMETER_ID)
137 AND ((RECINFO.PARAMETER_TYPE = X_PARM_TYPE)
138 OR ((RECINFO.PARAMETER_TYPE IS NULL) AND (X_PARM_TYPE IS NULL)))
139 AND ((RECINFO.PARAMETER_NAME = X_PARAMETER_NAME)
140 OR ((RECINFO.PARAMETER_NAME IS NULL) AND (X_PARAMETER_NAME IS NULL)))
141 AND ((RECINFO.PARAMETER_VALUE = X_PARAMETER_VALUE)
142 OR ((RECINFO.PARAMETER_VALUE IS NULL) AND (X_PARAMETER_VALUE IS NULL)))
143
144 )
145 THEN
146 NULL;
147
148 ELSE
149 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
150 APP_EXCEPTION.RAISE_EXCEPTION;
151 END IF;
152
153 RETURN;
154 EXCEPTION
155 WHEN NEW_DATA_ENTRY THEN
156 IF c%ISOPEN THEN
157 CLOSE C;
158 END IF;
159 IF cur_gem_lookups%ISOPEN THEN
160 Close cur_gem_lookups;
161 END IF;
162 WHEN OTHERS THEN
163 IF c%ISOPEN THEN
164 CLOSE C;
165 END IF;
166 IF cur_gem_lookups%ISOPEN THEN
167 Close cur_gem_lookups;
168 END IF;
169
170 END LOCK_ROW;
171
172
173 /*======================================================================
174 -- PROCEDURE :
175 -- UPDATE_ROW
176 --
177 -- DESCRIPTION:
178 -- This particular procedure is used to update rows in detail table
179 --
180 -- HISTORY
181 -- Sriram.S 05-NOV-2004 Created
182 --===================================================================== */
183
184 PROCEDURE UPDATE_ROW (
185 X_PARAMETER_LINE_ID IN NUMBER,
186 X_PARAMETER_ID IN NUMBER,
187 X_PARM_TYPE IN NUMBER,
188 X_PARAMETER_NAME IN VARCHAR2,
189 X_PARAMETER_VALUE IN VARCHAR2,
190 X_LAST_UPDATE_DATE IN DATE,
191 X_LAST_UPDATED_BY IN NUMBER,
192 X_LAST_UPDATE_LOGIN IN NUMBER
193 ) IS
194 BEGIN
195
196 UPDATE GMD_PARAMETERS_DTL
197 SET
198 PARAMETER_ID = X_PARAMETER_ID,
199 PARAMETER_TYPE = X_PARM_TYPE,
200 PARAMETER_NAME = X_PARAMETER_NAME,
201 PARAMETER_VALUE = X_PARAMETER_VALUE,
202 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
203 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
204 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
205 WHERE PARAMETER_LINE_ID = X_PARAMETER_LINE_ID;
206
207 IF (SQL%NOTFOUND) THEN
208 RAISE NO_DATA_FOUND;
209 END IF;
210 EXCEPTION WHEN OTHERS THEN
211 NULL;
212
213 END UPDATE_ROW;
214
215 /*======================================================================
216 -- PROCEDURE :
217 -- DELETE_ROW
218 --
219 -- DESCRIPTION:
220 -- This particular procedureis used to delete rows in detail table
221 --
222 -- HISTORY
223 -- Sriram.S 05-NOV-2004 Created
224 --===================================================================== */
225
226 PROCEDURE DELETE_ROW (
227 X_PARAMETER_LINE_ID IN NUMBER
228 ) IS
229 BEGIN
230
231 DELETE FROM GMD_PARAMETERS_DTL
232 WHERE PARAMETER_LINE_ID = X_PARAMETER_LINE_ID;
233
234 IF (SQL%NOTFOUND) THEN
235 RAISE NO_DATA_FOUND;
236 END IF;
237 END DELETE_ROW;
238
239
240
241 /*======================================================================
242 -- PROCEDURE :
243 -- GET_PARAMETER_LIST
244 --
245 -- DESCRIPTION:
246 -- Fetches the option values and outputs a table.
247 --
248 -- HISTORY
249 -- KSHUKLA 05-NOV-2004 Created
250 --===================================================================== */
251
252 PROCEDURE GET_PARAMETER_LIST(pOrgn_id IN NUMBER,
253 V_block IN VARCHAR2,
254 Xparm_table IN OUT NOCOPY out_parm_table) IS
255
256 CURSOR cur_parameter_dtl(l_type number,l_gem_type varchar2,l_org_id number) is
257 select d.parameter_id parameter_id,d.parameter_line_id parameter_line_id
258 ,d.parameter_type parameter_type, d.parameter_name parameter_name
259 ,d.PARAMETER_VALUE PARAMETER_VALUE,d.creation_date creation_date
260 ,d.last_updated_by last_updated_by,d.last_update_date last_update_date
261 ,d.last_update_login last_update_login
262 ,d.created_by created_by
263 from gmd_parameters_dtl d, gmd_parameters_hdr h
264 where d.parameter_id = h.parameter_id
265 and d.parameter_type = l_type
266 and ((h.organization_id = l_org_id) or ((l_org_id is NULL) and (h.organization_id IS NULL)))
267 UNION
268 select NULL parameter_id,NULL parameter_line_id,NULL parameter_type,lookup_code parameter_name, NULL PARAMETER_VALUE
269 ,NULL creation_date ,NULL last_updated_by,NULL last_updated_date,NULL last_updated_login,NULL created_by
270 FROM gem_lookups l
271 WHERE lookup_type = l_gem_type
272 and ENABLED_FLAG = 'Y'
273 AND not exists (select 1
274 from gmd_parameters_dtl d, gmd_parameters_hdr h
275 where d.parameter_id = h.parameter_id
276 and d.parameter_type = l_type
277 and ((h.organization_id = l_org_id) or ((l_org_id is NULL) and (h.organization_id IS NULL)))
278 and d.parameter_name = l.lookup_code)
279 ORDER BY PARAMETER_NAME;
280
281
282 X_prcs_cnt NUMBER := 0;
283 l_lookup_type varchar2(32);
284 l_type number;
285 TYPE parm_table is table of cur_parameter_dtl%ROWTYPE;
286 l_parm_table parm_table;
287 BEGIN
288 IF v_block = 'DTL_FORM' THEN
289 l_lookup_type := 'GMD_FORMULA_PARAMETER';
290 l_type :=1;
291 ELSIF v_block = 'DTL_OPRN' THEN
292 l_lookup_type := 'GMD_OPERATION_PARAMETER';
293 l_type :=2;
294 ELSIF v_block = 'DTL_ROUT' THEN
295 l_lookup_type := 'GMD_ROUTING_PARAMETER';
296 l_type :=3;
297 ELSIF v_block = 'DTL_RECP' THEN
298 l_lookup_type := 'GMD_RECIPE_PARAMETER';
299 l_type :=4;
300 ELSIF v_block = 'DTL_SUB' THEN
301 l_lookup_type := 'GMD_SUBSTITUTION_PARAMETER';
302 l_type :=5;
303 ELSIF v_block = 'DTL_LAB' THEN
304 l_lookup_type := 'GMD_LAB_PARAMETER';
305 l_type :=6;
306 END IF;
307
308
309 FOR l_get_prcs IN cur_parameter_dtl(l_type,l_lookup_type,pOrgn_id)
310 LOOP
311 X_prcs_cnt := X_prcs_cnt+1;
312
313 Xparm_table(X_prcs_cnt).parameter_id := l_get_prcs.parameter_id;
314 Xparm_table(X_prcs_cnt).parameter_line_id:=l_get_prcs.parameter_line_id;
315 Xparm_table(X_prcs_cnt).parameter_type:=NVL(l_get_prcs.parameter_type,l_type);
316 Xparm_table(X_prcs_cnt).parameter_name:=l_get_prcs.parameter_name;
317 Xparm_table(X_prcs_cnt).parameter_value :=l_get_prcs.parameter_value;
318 Xparm_table(X_prcs_cnt).creation_date :=l_get_prcs.creation_date;
319 Xparm_table(X_prcs_cnt).last_updated_by:=l_get_prcs.last_updated_by;
320 Xparm_table(X_prcs_cnt).created_by:=l_get_prcs.created_by;
321 END LOOP;
322 END GET_PARAMETER_LIST;
323
324 END GMD_PARAMETERS_DTL_PKG;
325
[Home] [Help]