1 PACKAGE BODY GMDOPVAL_PUB AS
2 /* $Header: GMDPOPVB.pls 120.1 2005/09/29 11:20:45 srsriran noship $ */
3
4 /* ====================================================================
5 FUNCTION:
6 check_duplicate_oprn
7
8 DESCRIPTION:
9 This PL/SQL function is responsible for
10 checking duplication of operations.
11
12 REQUIREMENTS
13 oprn_no and oprn_vers must be non null values.
14 SYNOPSIS:
15 X_ret := gmdopval_pub.check_duplicate_oprn(poprn_no, poprn_vers, pcalledby_form);
16
17 RETURNS:
18 0 Success
19 -1 Some required values for procedure are missing.
20 -30 GMD_OPRN_EXISTS Duplicate Operation.
21 ====================================================================*/
22 FUNCTION check_duplicate_oprn(poprn_no IN VARCHAR2,
23 poprn_vers IN NUMBER,
24 pcalledby_form IN VARCHAR2 DEFAULT 'F') RETURN NUMBER IS
25
26 /* Local variables.
27 ================ */
28 l_ret NUMBER := 0;
29
30 /* Cursor Definitions.
31 =================== */
32 CURSOR Cur_check_dup IS
33 SELECT 1
34 FROM SYS.DUAL
35 WHERE EXISTS (SELECT 1
36 FROM gmd_operations
37 WHERE oprn_no = poprn_no
41 IF (poprn_no IS NULL OR poprn_vers IS NULL) THEN
38 AND oprn_vers = poprn_vers);
39 duplicate_oprn EXCEPTION;
40 BEGIN
42 RETURN -1;
43 END IF;
44 OPEN Cur_check_dup;
45 FETCH Cur_check_dup INTO l_ret;
46 IF (Cur_check_dup%FOUND) THEN
47 CLOSE Cur_check_dup;
48 RAISE duplicate_oprn;
49 END IF;
50 CLOSE Cur_check_dup;
51 RETURN 0;
52 EXCEPTION
53 WHEN duplicate_oprn THEN
54 IF (pcalledby_form = 'T') THEN
55 FND_MESSAGE.SET_NAME('GMD', 'FM_OPER_CODE_EXISTS');
56 APP_EXCEPTION.RAISE_EXCEPTION;
57 ELSE
58 RETURN GMD_OPRN_EXISTS;
59 END IF;
60 END check_duplicate_oprn;
61
62 /* ====================================================================
63 FUNCTION:
64 check_oprn_class
65
66 DESCRIPTION:
67 This PL/SQL function is responsible for
68 checking that the operation class is valid.
69
70 SYNOPSIS:
71 X_ret := gmdopval_pub.check_oprn_class(poprn_class, pcalledby_form);
72
73 RETURNS:
74 0 Success
75 -1 Some required values for procedure are missing.
76 -31 GMD_INV_OPRN_CLASS Operation class is not valid.
77 ==================================================================== */
78 FUNCTION check_oprn_class(poprn_class IN VARCHAR2, pcalledby_form IN VARCHAR2 DEFAULT 'F') RETURN NUMBER IS
79 /* Cursor Definitions.
80 =================== */
81 CURSOR Cur_oprn_class IS
82 SELECT 1
83 FROM SYS.DUAL
84 WHERE EXISTS (SELECT 1
85 FROM fm_oprn_cls
86 WHERE oprn_class = poprn_class
87 AND delete_mark = 0);
88 invalid_oprn_class EXCEPTION;
89 l_ret NUMBER;
90 BEGIN
91 IF (poprn_class IS NOT NULL) THEN
92 OPEN Cur_oprn_class;
93 FETCH Cur_oprn_class INTO l_ret;
94 IF (Cur_oprn_class%NOTFOUND) THEN
95 CLOSE Cur_oprn_class;
96 RAISE invalid_oprn_class;
97 END IF;
98 CLOSE Cur_oprn_class;
99 END IF;
100 RETURN 0;
101 EXCEPTION
102 WHEN invalid_oprn_class THEN
103 IF (pcalledby_form = 'T') THEN
104 FND_MESSAGE.SET_NAME('GMD','FM_INV_OPRN_CLASS');
105 APP_EXCEPTION.RAISE_EXCEPTION;
106 ELSE
107 RETURN GMD_INV_OPRN_CLASS;
108 END IF;
109 END check_oprn_class;
110
111 /* ====================================================================
112 FUNCTION:
113 check_activity
114
115 DESCRIPTION:
116 This PL/SQL function is responsible for
117 checking that the activity is valid.
118
119 SYNOPSIS:
120 X_ret := gmdopval_pub.check_activity(pactivity, pcalledby_form);
121
122 REQUIREMENTS
123 activity must be a non null value.
124 RETURNS:
125 0 Success
126 -1 Some required values for procedure are missing.
127 -32 GMD_ACTV_INVALID Invalid Activity.
128 ==================================================================== */
129 FUNCTION check_activity(pactivity IN VARCHAR2, pcalledby_form IN VARCHAR2 DEFAULT 'F') RETURN NUMBER IS
130 CURSOR Cur_check_actv IS
131 SELECT 1
132 FROM SYS.DUAL
133 WHERE EXISTS (SELECT 1
134 FROM fm_actv_mst
135 WHERE activity = pactivity
136 AND delete_mark = 0);
137 invalid_activity EXCEPTION;
138 l_ret NUMBER;
139 BEGIN
140 IF (pactivity IS NULL) THEN
141 RETURN -1;
142 ELSE
143 OPEN Cur_check_actv;
144 FETCH Cur_check_actv INTO l_ret;
145 IF (Cur_check_actv%NOTFOUND) THEN
146 CLOSE Cur_check_actv;
147 RAISE invalid_activity;
148 END IF;
149 CLOSE Cur_check_actv;
150 END IF;
151 RETURN 0;
152 EXCEPTION
153 WHEN invalid_activity THEN
154 IF (pcalledby_form = 'T') THEN
155 FND_MESSAGE.SET_NAME('GMD','FM_INVACTIVITY');
156 APP_EXCEPTION.RAISE_EXCEPTION;
157 ELSE
158 RETURN GMD_ACTV_INVALID;
159 END IF;
160 END check_activity;
161
162 /* ====================================================================
163 FUNCTION:
164 check_resource
165
166 DESCRIPTION:
167 This PL/SQL function is responsible for
168 checking that the resource is valid.
169
170 SYNOPSIS:
171 X_ret := gmdopval_pub.check_resource(presource, pcalledby_form);
172
173 REQUIREMENTS
174 resource must be a non null value.
175
176 RETURNS:
177 0 Success
178 -1 Some required values for procedure are missing.
179 -33 GMD_BAD_RESOURCE Invalid resource.
180 ==================================================================== */
181 FUNCTION check_resource(presource IN VARCHAR2, pcalledby_form IN VARCHAR2 DEFAULT 'F') RETURN NUMBER IS
182 CURSOR Cur_check_rsrc IS
183 SELECT 1
184 FROM SYS.DUAL
185 WHERE EXISTS (SELECT 1
186 FROM cr_rsrc_mst
187 WHERE resources = presource
188 AND delete_mark = 0);
189 invalid_resource EXCEPTION;
190 l_ret NUMBER;
191 BEGIN
192 IF (presource IS NULL) THEN
193 RETURN -1;
194 ELSE
195 OPEN Cur_check_rsrc;
196 FETCH Cur_check_rsrc INTO l_ret;
197 IF (Cur_check_rsrc%NOTFOUND) THEN
198 CLOSE Cur_check_rsrc;
199 RAISE invalid_resource;
200 END IF;
201 CLOSE Cur_check_rsrc;
202 END IF;
203 RETURN 0;
204 EXCEPTION
205 WHEN invalid_resource THEN
206 IF (pcalledby_form = 'T') THEN
210 RETURN GMD_BAD_RESOURCE;
207 FND_MESSAGE.SET_NAME('GMD','FM_BAD_RESOURCE');
208 APP_EXCEPTION.RAISE_EXCEPTION;
209 ELSE
211 END IF;
212 END check_resource;
213
214 /* ====================================================================
215 FUNCTION:
216 check_cost_cmpnt_cls
217
218 DESCRIPTION:
219 This PL/SQL function is responsible for
220 checking that the cost component class is a valid value.
221
222 SYNOPSIS:
223 X_ret := gmdopval_pub.check_cost_cmpnt_cls(pcost_cmpntcls_code, pcalledby_form);
224
225 REQUIREMENTS
226 cost component class must be a non null value.
227
228 RETURNS:
229 0 Success
230 -1 Some required values for procedure are missing.
231 -34 GMD_CMPNT_CLASS_ERR Invalid cost component class.
232 ==================================================================== */
233 FUNCTION check_cost_cmpnt_cls(pcost_cmpntcls_code IN VARCHAR2, pcalledby_form IN VARCHAR2 DEFAULT 'F') RETURN NUMBER IS
234 /* Cursor Definitions.
235 =================== */
236 CURSOR Cur_cost_cmpnt_cls IS
237 SELECT 1
238 FROM SYS.DUAL
239 WHERE EXISTS (SELECT 1
240 FROM cm_cmpt_mst
241 WHERE cost_cmpntcls_code = pcost_cmpntcls_code
242 AND delete_mark = 0);
243 invalid_cost_cmpnt_cls EXCEPTION;
244 l_ret NUMBER;
245 BEGIN
246 IF (pcost_cmpntcls_code IS NULL) THEN
247 RETURN -1;
248 ELSE
249 OPEN Cur_cost_cmpnt_cls;
250 FETCH Cur_cost_cmpnt_cls INTO l_ret;
251 IF (Cur_cost_cmpnt_cls%NOTFOUND) THEN
252 CLOSE Cur_cost_cmpnt_cls;
253 RAISE invalid_cost_cmpnt_cls;
254 END IF;
255 CLOSE Cur_cost_cmpnt_cls;
256 END IF;
257 RETURN 0;
258 EXCEPTION
259 WHEN invalid_cost_cmpnt_cls THEN
260 IF (pcalledby_form = 'T') THEN
261 FND_MESSAGE.SET_NAME('GMD','FM_BADUSAGEIND');
262 APP_EXCEPTION.RAISE_EXCEPTION;
263 ELSE
264 RETURN GMD_CMPNT_CLASS_ERR;
265 END IF;
266 END check_cost_cmpnt_cls;
267
268 /* ====================================================================
269 FUNCTION:
270 check_cost_analysis
271
272 DESCRIPTION:
273 This PL/SQL function is responsible for
274 checking that the cost analysis code is a valid value.
275
276 SYNOPSIS:
277 X_ret := gmdopval_pub.check_cost_analysis(pcost_analysis_code, pcalledby_form);
278
279 RETURNS:
280 0 Success
281 -1 Some required values for procedure are missing.
282 -35 GMD_COST_ANALYSIS_ERR Invalid Cost analysis code.
283 ==================================================================== */
284 FUNCTION check_cost_analysis(pcost_analysis_code IN VARCHAR2, pcalledby_form IN VARCHAR2 DEFAULT 'F') RETURN NUMBER IS
285 /* Cursor Definitions.
286 =================== */
287 CURSOR Cur_analysis_code IS
288 SELECT 1
289 FROM SYS.DUAL
290 WHERE EXISTS (SELECT 1
291 FROM cm_alys_mst
292 WHERE cost_analysis_code = pcost_analysis_code
293 AND delete_mark=0);
294 invalid_cost_analysis EXCEPTION;
295 l_ret NUMBER;
296 BEGIN
297 IF (pcost_analysis_code IS NULL) THEN
298 RETURN -1;
299 ELSE
300 OPEN Cur_analysis_code;
301 FETCH Cur_analysis_code INTO l_ret;
302 IF (Cur_analysis_code%NOTFOUND) THEN
303 CLOSE Cur_analysis_code;
304 RAISE invalid_cost_analysis;
305 END IF;
306 CLOSE Cur_analysis_code;
307 END IF;
308 RETURN 0;
309 EXCEPTION
310 WHEN invalid_cost_analysis THEN
311 IF (pcalledby_form = 'T') THEN
312 FND_MESSAGE.SET_NAME('GMD','FM_INV_COST_ANALYSIS');
313 APP_EXCEPTION.RAISE_EXCEPTION;
314 ELSE
315 RETURN GMD_COST_ANALYSIS_ERR;
316 END IF;
317 END check_cost_analysis;
318
319 END GMDOPVAL_PUB;