[Home] [Help]
PACKAGE BODY: APPS.GHG_SOURCE_COMBINATIONS_PKG
Source
1 PACKAGE BODY GHG_SOURCE_COMBINATIONS_PKG AS
2 /*$Header: ghgsrcmb.pls 120.0.12020000.1 2012/10/18 05:13:55 mgijare noship $ */
3
4 PROCEDURE insert_row (x_rowid IN OUT NOCOPY VARCHAR2,
5 x_source_comb_id NUMBER,
6 x_source_comb_code VARCHAR2,
7 x_source_comb_desc VARCHAR2,
8 x_source_id NUMBER,
9 x_start_date DATE,
10 x_end_date DATE,
11 x_code_combination_id NUMBER,
12 x_scope_lookup_code VARCHAR2,
13 x_method_lookup_code VARCHAR2,
14 x_rate_override_type VARCHAR2,
15 x_supplier_id NUMBER,
16 x_supplier_site_id NUMBER,
17 x_supplier_type VARCHAR2,
18 x_item_id NUMBER,
19 x_item_type VARCHAR2,
20 x_method_justification VARCHAR2,
21 x_ghg_organization_id NUMBER,
22 x_location_code VARCHAR2,
23 x_transport_type_lookup_code VARCHAR2,
24 x_created_by NUMBER,
25 x_creation_date DATE,
26 x_last_updated_by NUMBER,
27 x_last_update_date DATE,
28 x_last_update_login NUMBER,
29 x_org_id NUMBER ) IS
30
31 v_debug_info VARCHAR2(100);
32
33 CURSOR c IS
34 SELECT ROWID
35 FROM GHG_SOURCE_COMBINATIONS
36 WHERE source_combination_id = x_source_comb_id;
37
38 BEGIN
39
40 v_debug_info := 'Inserting into GHG_SOURCE_COMBINATIONS';
41
42 INSERT INTO GHG_SOURCE_COMBINATIONS (source_combination_id,
43 source_combination_code,
44 source_combination_description,
45 source_id,
46 start_date,
47 end_date,
48 code_combination_id,
49 scope_lookup_code,
50 method_lookup_code,
51 rate_override_type,
52 supplier_id,
53 supplier_site_id,
54 supplier_type,
55 item_id,
56 item_type,
57 method_justification,
58 ghg_organization_id,
59 location_code,
60 transport_type_lookup_code,
61 created_by,
62 creation_date,
63 last_updated_by,
64 last_update_date,
65 last_update_login,
66 org_id)
67 VALUES (x_source_comb_id,
68 x_source_comb_code,
69 x_source_comb_desc,
70 x_source_id ,
71 x_start_date ,
72 x_end_date ,
73 x_code_combination_id,
74 x_scope_lookup_code ,
75 x_method_lookup_code,
76 x_rate_override_type,
77 x_supplier_id ,
78 x_supplier_site_id ,
79 x_supplier_type ,
80 x_item_id ,
81 x_item_type ,
82 x_method_justification,
83 x_ghg_organization_id ,
84 x_location_code ,
85 x_transport_type_lookup_code,
86 x_created_by ,
87 x_creation_date ,
88 x_last_updated_by ,
89 x_last_update_date ,
90 x_last_update_login ,
91 x_org_id );
92
93
94 v_debug_info := 'Open cursor c';
95 OPEN c;
96
97 v_debug_info := 'Fetch cursor c';
98 FETCH c INTO x_rowid;
99
100 IF (c%notfound) THEN
101 v_debug_info := 'Close cursor c - ROW NOT FOUND';
102 CLOSE c;
103 RAISE no_data_found;
104 END IF;
105
106 v_debug_info := 'Close cursor c';
107 CLOSE c;
108
109 EXCEPTION
110 WHEN NO_DATA_FOUND THEN
111 NULL;
112
113 WHEN OTHERS THEN
114 IF (SQLCODE <> -20001) THEN
115 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
116 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
117 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
118 END IF;
119 APP_EXCEPTION.RAISE_EXCEPTION;
120
121 END insert_row;
122
123 PROCEDURE update_row (x_rowid IN OUT NOCOPY VARCHAR2,
124 x_source_comb_id NUMBER,
125 x_source_comb_code VARCHAR2,
126 x_source_comb_desc VARCHAR2,
127 x_source_id NUMBER,
128 x_start_date DATE,
129 x_end_date DATE,
130 x_code_combination_id NUMBER,
131 x_scope_lookup_code VARCHAR2,
132 x_method_lookup_code VARCHAR2,
133 x_rate_override_type VARCHAR2,
134 x_supplier_id NUMBER,
135 x_supplier_site_id NUMBER,
136 x_supplier_type VARCHAR2,
137 x_item_id NUMBER,
138 x_item_type VARCHAR2,
139 x_method_justification VARCHAR2,
140 x_ghg_organization_id NUMBER,
141 x_location_code VARCHAR2,
142 x_transport_type_lookup_code VARCHAR2,
143 x_created_by NUMBER,
144 x_creation_date DATE,
145 x_last_updated_by NUMBER,
146 x_last_update_date DATE,
147 x_last_update_login NUMBER,
148 x_org_id NUMBER ) IS
149
150 v_debug_info VARCHAR2(100);
151
152 BEGIN
153
154 v_debug_info := 'Updating GHG_SOURCE_COMBINATIONS';
155
156 UPDATE GHG_SOURCE_COMBINATIONS
157 SET source_combination_id = x_source_comb_id,
158 source_combination_code = x_source_comb_code,
159 source_combination_description = x_source_comb_desc,
160 source_id = x_source_id,
161 start_date = x_start_date,
162 end_date = x_end_date,
163 code_combination_id = x_code_combination_id,
164 scope_lookup_code = x_scope_lookup_code,
165 method_lookup_code = x_method_lookup_code,
166 rate_override_type = x_rate_override_type,
167 supplier_id = x_supplier_id,
168 supplier_site_id = x_supplier_site_id,
169 supplier_type = x_supplier_type,
170 item_id = x_item_id,
171 item_type = x_item_type,
172 method_justification = x_method_justification,
173 ghg_organization_id = x_ghg_organization_id,
174 location_code = x_location_code,
175 transport_type_lookup_code = x_transport_type_lookup_code,
176 created_by = x_created_by,
177 creation_date = x_creation_date,
178 last_updated_by = x_last_updated_by,
179 last_update_date = x_last_update_date,
180 last_update_login = x_last_update_login,
181 org_id = x_org_id
182 WHERE rowid = x_rowid;
183
184 IF (SQL%NOTFOUND) THEN
185 RAISE NO_DATA_FOUND;
186 END IF;
187
188 EXCEPTION
189 WHEN NO_DATA_FOUND THEN
190 NULL;
191
192 WHEN OTHERS THEN
193 IF (SQLCODE <> -20001) THEN
194 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
195 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
196 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
197 END IF;
198 APP_EXCEPTION.RAISE_EXCEPTION;
199
200 END update_row;
201
202 PROCEDURE delete_row (x_source_comb_id NUMBER) IS
203
204 v_debug_info VARCHAR2(100);
205
206 BEGIN
207
208 DELETE FROM GHG_SOURCE_COMBINATIONS
209 WHERE source_combination_id = x_source_comb_id;
210
211 IF (sql%notfound) THEN
212 RAISE no_data_found;
213 END IF;
214
215
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN
218 NULL;
219
220 WHEN OTHERS THEN
221 IF (SQLCODE <> -20001) THEN
222 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
223 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
224 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
225 END IF;
226 APP_EXCEPTION.RAISE_EXCEPTION;
227
228
229 END delete_row;
230
231 PROCEDURE lock_row (x_rowid IN OUT NOCOPY VARCHAR2,
232 x_source_comb_id NUMBER,
233 x_source_comb_code VARCHAR2,
234 x_source_comb_desc VARCHAR2,
235 x_source_id NUMBER,
236 x_start_date DATE,
237 x_end_date DATE,
238 x_code_combination_id NUMBER,
239 x_scope_lookup_code VARCHAR2,
240 x_method_lookup_code VARCHAR2,
241 x_rate_override_type VARCHAR2,
242 x_supplier_id NUMBER,
243 x_supplier_site_id NUMBER,
244 x_supplier_type VARCHAR2,
245 x_item_id NUMBER,
246 x_item_type VARCHAR2,
247 x_method_justification VARCHAR2,
248 x_ghg_organization_id NUMBER,
249 x_location_code VARCHAR2,
250 x_transport_type_lookup_code VARCHAR2,
251 x_created_by NUMBER,
252 x_creation_date DATE,
253 x_last_updated_by NUMBER,
254 x_last_update_date DATE,
255 x_last_update_login NUMBER,
256 x_org_id NUMBER ) IS
257
258 v_debug_info VARCHAR2(100);
259
260 CURSOR c IS
261 SELECT source_combination_id,
262 source_combination_code,
263 source_combination_description,
264 source_id,
265 start_date,
266 end_date,
267 code_combination_id,
268 scope_lookup_code,
269 method_lookup_code,
270 rate_override_type,
271 supplier_id,
272 supplier_site_id,
273 supplier_type,
274 item_id,
275 item_type,
276 method_justification,
277 ghg_organization_id,
278 location_code,
279 transport_type_lookup_code,
280 created_by,
281 creation_date,
282 last_updated_by,
283 last_update_date,
284 last_update_login,
285 org_id
286 FROM GHG_SOURCE_COMBINATIONS
287 WHERE rowid = x_rowid
288 FOR UPDATE of source_id NOWAIT;
289 recinfo C%ROWTYPE;
290
291 BEGIN
292
293 v_debug_info := 'Open cursor C';
294
295 OPEN C;
296
297 v_debug_info := 'Fetch cursor C';
298
299 FETCH C INTO recinfo;
300
301 IF (C%NOTFOUND) THEN
302 v_debug_info := 'Close cursor C - ROW NOT FOUND';
303 CLOSE C;
304 RAISE NO_DATA_FOUND;
305 END IF;
306
307 v_debug_info := 'Close cursor C';
308
309 CLOSE C;
310
311 IF ( ((recinfo.source_combination_id = x_source_comb_id) OR
312 (recinfo.source_combination_id IS NULL AND x_source_comb_id IS NULL))
313 AND ((recinfo.source_combination_code = x_source_comb_code) OR
314 (recinfo.source_combination_code IS NULL AND x_source_comb_code IS NULL))
315 AND ((recinfo.source_combination_description = x_source_comb_desc) OR
316 (recinfo.source_combination_description IS NULL AND x_source_comb_desc IS NULL))
317 AND ((recinfo.source_id = x_source_id) OR
318 (recinfo.source_id IS NULL AND x_source_id IS NULL))
319 AND ((recinfo.start_date = x_start_date) OR
320 (recinfo.start_date IS NULL AND x_start_date IS NULL))
321 AND ((recinfo.end_date= x_end_date) OR
322 (recinfo.end_date IS NULL AND x_end_date IS NULL))
323 AND ((recinfo.code_combination_id = x_code_combination_id) OR
324 (recinfo.code_combination_id IS NULL AND x_code_combination_id IS NULL))
325 AND ((recinfo.scope_lookup_code = x_scope_lookup_code) OR
326 (recinfo.scope_lookup_code IS NULL AND x_scope_lookup_code IS NULL))
327 AND ((recinfo.method_lookup_code = x_method_lookup_code) OR
331 AND ((recinfo.supplier_id = x_supplier_id) OR
328 (recinfo.method_lookup_code IS NULL AND x_method_lookup_code IS NULL))
329 AND ((recinfo.rate_override_type = x_rate_override_type) OR
330 (recinfo.rate_override_type IS NULL AND x_rate_override_type IS NULL))
332 (recinfo.supplier_id IS NULL AND x_supplier_id IS NULL))
333 AND ((recinfo.supplier_site_id = x_supplier_site_id) OR
334 (recinfo.supplier_site_id IS NULL AND x_supplier_site_id IS NULL))
335 AND ((recinfo.supplier_type = x_supplier_type) OR
336 (recinfo.supplier_type IS NULL AND x_supplier_type IS NULL))
337 AND ((recinfo.item_id = x_item_id) OR
338 (recinfo.item_id IS NULL AND x_item_id IS NULL))
339 AND ((recinfo.item_type = x_item_type) OR
340 (recinfo.item_type IS NULL AND x_item_type IS NULL))
341 AND ((recinfo.method_justification = x_method_justification) OR
342 (recinfo.method_justification IS NULL AND x_method_justification IS NULL))
343 AND ((recinfo.ghg_organization_id = x_ghg_organization_id) OR
344 (recinfo.ghg_organization_id IS NULL AND x_ghg_organization_id IS NULL))
345 AND ((recinfo.location_code = x_location_code) OR
346 (recinfo.location_code IS NULL AND x_location_code IS NULL))
347 AND ((recinfo.transport_type_lookup_code = x_transport_type_lookup_code) OR
348 (recinfo.transport_type_lookup_code IS NULL AND x_transport_type_lookup_code IS NULL))
349 AND ((recinfo.created_by = x_created_by) OR
350 (recinfo.created_by IS NULL AND x_created_by IS NULL))
351 AND ((recinfo.creation_date = x_creation_date) OR
352 (recinfo.creation_date IS NULL AND x_creation_date IS NULL))
353 AND ((recinfo.last_updated_by = x_last_updated_by) OR
354 (recinfo.last_updated_by IS NULL AND x_last_updated_by IS NULL))
355 AND ((recinfo.last_update_date = x_last_update_date) OR
356 (recinfo.last_update_date IS NULL AND x_last_update_date IS NULL))
357 AND ((recinfo.org_id = x_org_id) OR
358 (recinfo.org_id IS NULL AND x_org_id IS NULL)))
359 THEN
360 NULL;
361 ELSE
362 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
363 APP_EXCEPTION.RAISE_EXCEPTION;
364 END IF;
365
366 EXCEPTION
367 WHEN NO_DATA_FOUND THEN
368 NULL;
369
370 WHEN OTHERS THEN
371 IF (SQLCODE <> -20001) THEN
372 IF (SQLCODE = -54) THEN
373 FND_MESSAGE.SET_NAME('GHG', 'GHG_RESOURCE_BUSY');
374 ELSE
375 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
376 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
377 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
378 END IF;
379 END IF;
380 APP_EXCEPTION.RAISE_EXCEPTION;
381
382 END lock_row;
383
384 FUNCTION source_comb_in_use (x_source_comb_id NUMBER) RETURN BOOLEAN IS
385 v_row_count number;
386 BEGIN
387 SELECT count(*)
388 into v_row_count
389 FROM GHG_SOURCE_COMBINATIONS_ALL
390 where source_combination_id = x_source_comb_id;
391
392 IF v_row_count > 0 THEN
393 RETURN TRUE;
394 ELSE
395 RETURN FALSE;
396 END IF;
397 END;
398
399 FUNCTION generate_source_comb_id RETURN NUMBER IS
400 v_source_comb_id NUMBER;
401 BEGIN
402 select GHG_SOURCE_COMBINATIONS_S.NEXTVAL
403 into v_source_comb_id
404 from dual;
405
406 RETURN v_source_comb_id;
407 END;
408
409 END GHG_SOURCE_COMBINATIONS_PKG;