[Home] [Help]
PACKAGE BODY: APPS.GHG_TRANSACTION_BATCHES_PKG
Source
1 PACKAGE BODY GHG_TRANSACTION_BATCHES_PKG AS
2 /*$Header: ghgtxnbb.pls 120.2.12020000.2 2012/10/17 04:32:12 sasuren ship $ */
3
4 PROCEDURE insert_row (x_rowid IN OUT NOCOPY VARCHAR2,
5 x_transaction_batch_id NUMBER,
6 x_transaction_batch_number VARCHAR2,
7 x_batch_type VARCHAR2,
8 x_batch_sub_type VARCHAR2,
9 x_vendor_id NUMBER,
10 x_vendor_site_id NUMBER,
11 x_description VARCHAR2,
12 x_status VARCHAR2,
13 x_reversal_date DATE,
14 x_reversed_transact_batch_id NUMBER,
15 x_org_id NUMBER,
16 x_set_of_books_id NUMBER,
17 x_created_by NUMBER,
18 x_creation_date DATE,
19 x_last_updated_by NUMBER,
20 x_last_update_date DATE,
21 x_last_update_login NUMBER,
22 x_called_from_api VARCHAR2 DEFAULT 'N') IS
23
24 v_debug_info VARCHAR2(100);
25
26 CURSOR c IS
27 SELECT ROWID
28 FROM GHG_TRANSACTION_BATCHES_ALL
29 WHERE transaction_batch_id = x_transaction_batch_id;
30
31 BEGIN
32
33 v_debug_info := 'Inserting into GHG_TRANSACTION_BATCHES_ALL';
34
35 INSERT INTO GHG_TRANSACTION_BATCHES_ALL (transaction_batch_id,
36 transaction_batch_number,
37 batch_type,
38 batch_sub_type,
39 vendor_id,
40 vendor_site_id,
41 description,
42 status,
43 reversal_date,
44 reversed_transaction_batch_id,
45 org_id,
46 set_of_books_id,
47 created_by,
48 creation_date,
49 last_updated_by,
50 last_update_date,
51 last_update_login)
52 VALUES (x_transaction_batch_id,
53 x_transaction_batch_number,
54 x_batch_type,
55 x_batch_sub_type,
56 x_vendor_id,
57 x_vendor_site_id,
58 x_description,
59 x_status,
60 x_reversal_date,
61 x_reversed_transact_batch_id,
62 x_org_id,
63 x_set_of_books_id,
64 x_created_by,
65 x_creation_date,
66 x_last_updated_by,
67 x_last_update_date,
68 x_last_update_login);
69
70 v_debug_info := 'Open cursor c';
71 OPEN c;
72
73 v_debug_info := 'Fetch cursor c';
74 FETCH c INTO x_rowid;
75
76 IF (c%notfound) THEN
77 v_debug_info := 'Close cursor c - ROW NOT FOUND';
78 CLOSE c;
79 RAISE no_data_found;
80 END IF;
81
82 v_debug_info := 'Close cursor c';
83 CLOSE c;
84
85 EXCEPTION
86 WHEN OTHERS THEN
87 IF x_called_from_api = 'Y' THEN
88 RAISE;
89 ELSE
90 IF (SQLCODE <> -20001) THEN
91 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
92 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
93 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
94 END IF;
95 APP_EXCEPTION.RAISE_EXCEPTION;
96 END IF;
97
98 END insert_row;
99
100 PROCEDURE update_row (x_rowid IN OUT NOCOPY VARCHAR2,
101 x_transaction_batch_id NUMBER,
102 x_transaction_batch_number VARCHAR2,
103 x_batch_type VARCHAR2,
104 x_batch_sub_type VARCHAR2,
105 x_vendor_id NUMBER,
106 x_vendor_site_id NUMBER,
107 x_description VARCHAR2,
108 x_status VARCHAR2,
109 x_reversal_date DATE,
110 x_reversed_transact_batch_id NUMBER,
111 x_org_id NUMBER,
112 x_set_of_books_id NUMBER,
113 x_created_by NUMBER,
114 x_creation_date DATE,
115 x_last_updated_by NUMBER,
116 x_last_update_date DATE,
117 x_last_update_login NUMBER) IS
118
119 v_debug_info VARCHAR2(100);
120
121 BEGIN
122
123 v_debug_info := 'Updating GHG_TRANSACTION_BATCHES_ALL';
124
125 UPDATE GHG_TRANSACTION_BATCHES_ALL
126 SET transaction_batch_id = x_transaction_batch_id,
127 transaction_batch_number = x_transaction_batch_number,
128 batch_type = x_batch_type,
129 batch_sub_type = x_batch_sub_type,
130 vendor_id = x_vendor_id,
131 vendor_site_id = x_vendor_site_id,
132 description = x_description,
133 status = x_status,
134 reversal_date = x_reversal_date,
135 reversed_transaction_batch_id = x_reversed_transact_batch_id,
136 org_id = x_org_id,
137 set_of_books_id = x_set_of_books_id,
138 created_by = x_created_by,
139 creation_date = x_creation_date,
140 last_updated_by = x_last_updated_by,
141 last_update_date = x_last_update_date,
142 last_update_login = x_last_update_login
143 WHERE rowid = x_rowid;
144
145 IF (SQL%NOTFOUND) THEN
146 RAISE NO_DATA_FOUND;
147 END IF;
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 IF (SQLCODE <> -20001) THEN
152 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
153 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
154 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
155 END IF;
156 APP_EXCEPTION.RAISE_EXCEPTION;
157
158 END update_row;
159
160 PROCEDURE delete_row (x_transaction_batch_id NUMBER) IS
161
162 v_row_count NUMBER(15);
163
164 BEGIN
165
166
167 DELETE FROM GHG_TRANSACTIONS_ALL
168 WHERE transaction_batch_id = x_transaction_batch_id;
169
170 DELETE FROM GHG_TRANSACTION_DETAILS_ALL a
171 WHERE not exists (select 1
172 from GHG_TRANSACTIONS_ALL b
173 where a.transaction_id = b.transaction_id);
174
175 IF (sql%notfound) THEN
176 NULL;
177 END IF;
178
179 DELETE FROM GHG_TRANSACTION_BATCHES_ALL
180 WHERE transaction_batch_id = x_transaction_batch_id;
181
182 IF (sql%notfound) THEN
183 RAISE no_data_found;
184 END IF;
185
186 END delete_row;
187
188 PROCEDURE lock_row (x_rowid IN OUT NOCOPY VARCHAR2,
189 x_transaction_batch_id NUMBER,
190 x_transaction_batch_number VARCHAR2,
191 x_batch_type VARCHAR2,
192 x_batch_sub_type VARCHAR2,
193 x_vendor_id NUMBER,
194 x_vendor_site_id NUMBER,
195 x_description VARCHAR2,
196 x_status VARCHAR2,
197 x_reversal_date DATE,
198 x_reversed_transact_batch_id NUMBER,
199 x_org_id NUMBER,
200 x_set_of_books_id NUMBER,
201 x_created_by NUMBER,
202 x_creation_date DATE,
203 x_last_updated_by NUMBER,
204 x_last_update_date DATE,
205 x_last_update_login NUMBER) IS
206
207 v_debug_info VARCHAR2(100);
208
209 CURSOR c IS
210 SELECT transaction_batch_id,
211 transaction_batch_number,
212 batch_type,
213 batch_sub_type,
214 vendor_id,
215 vendor_site_id,
216 description,
217 status,
218 reversal_date,
219 reversed_transaction_batch_id,
220 org_id,
221 set_of_books_id,
222 created_by,
223 creation_date,
224 last_updated_by,
225 last_update_date,
226 last_update_login
227 FROM GHG_TRANSACTION_BATCHES_ALL
228 WHERE rowid = x_rowid
229 FOR UPDATE of transaction_batch_id NOWAIT;
230 recinfo C%ROWTYPE;
231
232 BEGIN
233
234 v_debug_info := 'Open cursor C';
235
236 OPEN C;
237
238 v_debug_info := 'Fetch cursor C';
239
240 FETCH C INTO recinfo;
241
242 IF (C%NOTFOUND) THEN
243 v_debug_info := 'Close cursor C - ROW NOT FOUND';
244 CLOSE C;
245 RAISE NO_DATA_FOUND;
246 END IF;
247
248 v_debug_info := 'Close cursor C';
249
250 CLOSE C;
251
252 IF ( ((recinfo.transaction_batch_id = x_transaction_batch_id) OR
253 (recinfo.transaction_batch_id IS NULL AND
254 x_transaction_batch_id IS NULL))
255 AND ((recinfo.transaction_batch_number = x_transaction_batch_number) OR
256 (recinfo.transaction_batch_number IS NULL AND
257 x_transaction_batch_number IS NULL))
258 AND ((recinfo.batch_type = x_batch_type) OR
259 (recinfo.batch_type IS NULL AND x_batch_type IS NULL))
260 AND ((recinfo.batch_sub_type = x_batch_sub_type) OR
261 (recinfo.batch_sub_type IS NULL AND x_batch_sub_type IS NULL))
262 AND ((recinfo.vendor_id = x_vendor_id) OR
263 (recinfo.vendor_id IS NULL AND x_vendor_id IS NULL))
264 AND ((recinfo.vendor_site_id = x_vendor_site_id) OR
265 (recinfo.vendor_site_id IS NULL AND x_vendor_site_id IS NULL))
266 AND ((recinfo.description = x_description) OR
267 (recinfo.description IS NULL AND x_description IS NULL))
268 AND ((recinfo.status = x_status) OR
269 (recinfo.status IS NULL AND x_status IS NULL))
270 AND ((recinfo.reversal_date = x_reversal_date) OR
271 (recinfo.reversal_date IS NULL AND x_reversal_date IS NULL))
272 AND ((recinfo.reversed_transaction_batch_id =
273 x_reversed_transact_batch_id) OR
274 (recinfo.reversed_transaction_batch_id IS NULL AND
275 x_reversed_transact_batch_id IS NULL))
276 AND ((recinfo.org_id = x_org_id) OR
277 (recinfo.org_id IS NULL AND x_org_id IS NULL))
278 AND ((recinfo.set_of_books_id = x_set_of_books_id) OR
279 (recinfo.set_of_books_id IS NULL AND x_set_of_books_id IS NULL))
280 AND ((recinfo.created_by = x_created_by) OR
281 (recinfo.created_by IS NULL AND x_created_by IS NULL))
282 AND ((recinfo.creation_date = x_creation_date) OR
283 (recinfo.creation_date IS NULL AND x_creation_date IS NULL))
284 AND ((recinfo.last_updated_by = x_last_updated_by) OR
285 (recinfo.last_updated_by IS NULL AND x_last_updated_by IS NULL))
286 AND ((recinfo.last_update_date = x_last_update_date) OR
287 (recinfo.last_update_date IS NULL AND x_last_update_date IS NULL))
288 AND ((recinfo.last_update_login = x_last_update_login) OR
289 (recinfo.last_update_login IS NULL AND x_last_update_login IS NULL)))
290 THEN
291 NULL;
292 ELSE
293 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
294 APP_EXCEPTION.RAISE_EXCEPTION;
295 END IF;
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 IF (SQLCODE <> -20001) THEN
300 IF (SQLCODE = -54) THEN
301 FND_MESSAGE.SET_NAME('GHG', 'GHG_RESOURCE_BUSY');
302 ELSE
303 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
304 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
305 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
306 END IF;
307 END IF;
308 APP_EXCEPTION.RAISE_EXCEPTION;
309
310 END lock_row;
311
312 PROCEDURE copy_batch (x_copy_transaction_batch_id NUMBER,
313 x_transaction_batch_id NUMBER,
314 x_created_by NUMBER,
315 x_creation_date DATE,
316 x_last_updated_by NUMBER,
317 x_last_update_date DATE,
318 x_last_update_login NUMBER) IS
319
320 BEGIN
321
322 INSERT INTO GHG_TRANSACTIONS_ALL
323 (txn_type_lookup_code,
324 transaction_id,
325 transaction_batch_id,
326 --emission_rate_id,
327 transaction_source_id,
328 --emission_factor_id,
329 ghg_organization_id,
330 transaction_type_lookup_code,
331 usage_quantity,
332 unit_of_measure,
333 uom_conversion,
334 emission_quantity,
335 energy_quantity,
336 transaction_date_from,
337 transaction_date_to,
338 description,
339 org_id,
340 set_of_books_id,
341 invoice_id,
342 transaction_line_number,
343 distribution_line_number,
344 vendor_id,
345 vendor_site_id,
346 inventory_item_id,
347 ghg_asset_id,
348 attribute1,
349 attribute2,
350 attribute3,
351 attribute4,
352 attribute5,
353 attribute6,
354 attribute7,
355 attribute8,
356 attribute9,
357 attribute10,
358 attribute11,
359 attribute12,
360 attribute13,
361 attribute14,
362 attribute15,
363 attribute16,
364 attribute17,
365 attribute18,
366 attribute19,
367 attribute20,
368 attribute_category,
369 created_by,
370 creation_date,
371 last_updated_by,
372 last_update_date,
373 last_update_login,
374 emission_scope_lookup_code,
375 location_code,
376 measurement_criteria)
377 SELECT txn_type_lookup_code,
378 GHG_TRANSACTIONS_S.NEXTVAL transaction_id,
379 x_transaction_batch_id,
380 --emission_rate_id,
381 transaction_source_id,
382 --emission_factor_id,
383 ghg_organization_id,
384 transaction_type_lookup_code,
385 usage_quantity,
386 unit_of_measure,
387 uom_conversion,
388 emission_quantity,
389 energy_quantity,
390 transaction_date_from,
391 transaction_date_to,
392 description,
393 org_id,
394 set_of_books_id,
395 invoice_id,
396 transaction_line_number,
397 distribution_line_number,
398 vendor_id,
399 vendor_site_id,
400 inventory_item_id,
401 ghg_asset_id,
402 attribute1,
403 attribute2,
404 attribute3,
405 attribute4,
406 attribute5,
407 attribute6,
408 attribute7,
409 attribute8,
410 attribute9,
411 attribute10,
412 attribute11,
413 attribute12,
414 attribute13,
415 attribute14,
416 attribute15,
417 attribute16,
418 attribute17,
419 attribute18,
420 attribute19,
421 attribute20,
422 attribute_category,
423 x_created_by,
424 x_creation_date,
425 x_last_updated_by,
426 x_last_update_date,
427 x_last_update_login,
428 emission_scope_lookup_code,
429 location_code,
430 measurement_criteria
431 FROM GHG_TRANSACTIONS_V
432 WHERE transaction_batch_id = x_copy_transaction_batch_id;
433
434 COMMIT;
435
436 FOR calc_emissions IN (SELECT transaction_id from ghg_transactions_v where
437 transaction_batch_id = x_transaction_batch_id)
438 LOOP
439 GHG_EMISSION_RATES_PKG.calculate_emissions(calc_emissions.transaction_id);
440 END LOOP;
441
442 COMMIT;
443
444 END copy_batch;
445
446 PROCEDURE reverse_batch (x_transaction_batch_id NUMBER,
447 x_reversed_transact_batch_id NUMBER,
448 x_created_by NUMBER,
449 x_creation_date DATE,
450 x_last_updated_by NUMBER,
451 x_last_update_date DATE,
452 x_last_update_login NUMBER) IS
453
454 BEGIN
455
456 INSERT INTO GHG_TRANSACTIONS_ALL
457 (txn_type_lookup_code,
458 transaction_id,
459 transaction_batch_id,
460 --emission_rate_id,
461 transaction_source_id,
462 --emission_factor_id,
463 ghg_organization_id,
464 transaction_type_lookup_code,
465 usage_quantity,
466 unit_of_measure,
467 uom_conversion,
468 emission_quantity,
469 energy_quantity,
470 transaction_date_from,
471 transaction_date_to,
472 description,
473 org_id,
474 set_of_books_id,
475 invoice_id,
476 transaction_line_number,
477 distribution_line_number,
478 vendor_id,
479 vendor_site_id,
480 inventory_item_id,
481 ghg_asset_id,
482 attribute1,
483 attribute2,
484 attribute3,
485 attribute4,
486 attribute5,
487 attribute6,
488 attribute7,
489 attribute8,
490 attribute9,
491 attribute10,
492 attribute11,
493 attribute12,
494 attribute13,
495 attribute14,
496 attribute15,
497 attribute16,
498 attribute17,
499 attribute18,
500 attribute19,
501 attribute20,
502 attribute_category,
503 created_by,
504 creation_date,
505 last_updated_by,
506 last_update_date,
507 last_update_login,
508 emission_scope_lookup_code,
509 location_code,
510 measurement_criteria)
511 SELECT txn_type_lookup_code,
512 GHG_TRANSACTIONS_S.NEXTVAL transaction_id,
513 x_transaction_batch_id,
514 --emission_rate_id,
515 transaction_source_id,
516 --emission_factor_id,
517 ghg_organization_id,
518 transaction_type_lookup_code,
519 NVL(usage_quantity, 0) * -1,
520 unit_of_measure,
521 uom_conversion,
522 NVL(emission_quantity, 0) * -1,
523 NVL(energy_quantity, 0) * -1,
524 transaction_date_from,
525 transaction_date_to,
526 description,
527 org_id,
528 set_of_books_id,
529 invoice_id,
530 transaction_line_number,
531 distribution_line_number,
532 vendor_id,
533 vendor_site_id,
534 inventory_item_id,
535 ghg_asset_id,
536 attribute1,
537 attribute2,
538 attribute3,
539 attribute4,
540 attribute5,
541 attribute6,
542 attribute7,
543 attribute8,
544 attribute9,
545 attribute10,
546 attribute11,
547 attribute12,
548 attribute13,
549 attribute14,
550 attribute15,
551 attribute16,
552 attribute17,
553 attribute18,
554 attribute19,
555 attribute20,
556 attribute_category,
557 x_created_by,
558 x_creation_date,
559 x_last_updated_by,
560 x_last_update_date,
561 x_last_update_login,
562 emission_scope_lookup_code,
563 location_code,
564 measurement_criteria
565 FROM GHG_TRANSACTIONS_V
566 WHERE transaction_batch_id = x_reversed_transact_batch_id;
567
568 COMMIT;
569
570 FOR calc_emissions IN (SELECT transaction_id from GHG_transactions_v where
571 transaction_batch_id = x_transaction_batch_id)
572 LOOP
573 GHG_EMISSION_RATES_PKG.calculate_emissions(calc_emissions.transaction_id);
574 END LOOP;
575
576 COMMIT;
577
578 END reverse_batch;
579
580 END GHG_TRANSACTION_BATCHES_PKG;