[Home] [Help]
PACKAGE BODY: APPS.GHG_TRANSACTIONS_PKG
Source
1 PACKAGE BODY GHG_TRANSACTIONS_PKG AS
2 /*$Header: ghgtxnb.pls 120.3.12020000.2 2012/10/17 04:35:54 sasuren ship $ */
3
4 PROCEDURE insert_row (x_rowid IN OUT NOCOPY VARCHAR2,
5 x_txn_type_lookup_code VARCHAR2,
6 x_emission_id NUMBER,
7 x_emission_batch_id NUMBER,
8 x_emission_source_id NUMBER,
9 x_facility_id NUMBER,
10 x_emission_type_lookup_code VARCHAR2,
11 x_usage_quantity NUMBER,
12 x_unit_of_measure VARCHAR2,
13 x_uom_conversion NUMBER,
14 x_emission_quantity NUMBER,
15 x_energy_quantity NUMBER,
16 x_emission_date_from DATE,
17 x_emission_date_to DATE,
18 x_description VARCHAR2,
19 x_org_id NUMBER,
20 x_set_of_books_id NUMBER,
21 x_invoice_id NUMBER,
22 x_emission_line_number NUMBER,
23 x_distribution_line_number NUMBER,
24 x_invoice_line_number NUMBER,
25 x_vendor_id NUMBER,
26 x_vendor_site_id NUMBER,
27 x_inventory_item_id NUMBER,
28 x_ghg_asset_id NUMBER,
29 x_attribute1 VARCHAR2,
30 x_attribute2 VARCHAR2,
31 x_attribute3 VARCHAR2,
32 x_attribute4 VARCHAR2,
33 x_attribute5 VARCHAR2,
34 x_attribute6 VARCHAR2,
35 x_attribute7 VARCHAR2,
36 x_attribute8 VARCHAR2,
37 x_attribute9 VARCHAR2,
38 x_attribute10 VARCHAR2,
39 x_attribute11 VARCHAR2,
40 x_attribute12 VARCHAR2,
41 x_attribute13 VARCHAR2,
42 x_attribute14 VARCHAR2,
43 x_attribute15 VARCHAR2,
44 x_attribute16 VARCHAR2,
45 x_attribute17 VARCHAR2,
46 x_attribute18 VARCHAR2,
47 x_attribute19 VARCHAR2,
48 x_attribute20 VARCHAR2,
49 x_attribute_category VARCHAR2,
50 x_created_by NUMBER,
51 x_creation_date DATE,
52 x_last_updated_by NUMBER,
53 x_last_update_date DATE,
54 x_last_update_login NUMBER,
55 x_commit_flag VARCHAR2,
56 x_scope VARCHAR2,
57 x_location VARCHAR2,
58 x_measurement_criteria VARCHAR2,
59 x_called_from_api VARCHAR2 DEFAULT 'N') IS
60
61 v_debug_info VARCHAR2(100);
62
63 CURSOR c IS
64 SELECT ROWID
65 FROM GHG_transactions_all
66 WHERE transaction_id = x_emission_id;
67
68 BEGIN
69
70 v_debug_info := 'Inserting into GHG_transactions_all';
71
72 begin
73 INSERT INTO GHG_transactions_all (txn_type_lookup_code,
74 transaction_id,
75 transaction_batch_id,
76 transaction_source_id,
77 ghg_organization_id,
78 transaction_type_lookup_code,
79 usage_quantity,
80 unit_of_measure,
81 uom_conversion,
82 emission_quantity,
83 energy_quantity,
84 transaction_date_from,
85 transaction_date_to,
86 description,
87 org_id,
88 set_of_books_id,
89 invoice_id,
90 transaction_line_number,
91 distribution_line_number,
92 invoice_line_number,
93 vendor_id,
94 vendor_site_id,
95 inventory_item_id,
96 ghg_asset_id,
97 attribute1,
98 attribute2,
99 attribute3,
100 attribute4,
101 attribute5,
102 attribute6,
103 attribute7,
104 attribute8,
105 attribute9,
106 attribute10,
107 attribute11,
108 attribute12,
109 attribute13,
110 attribute14,
111 attribute15,
112 attribute16,
113 attribute17,
114 attribute18,
115 attribute19,
116 attribute20,
117 attribute_category,
118 created_by,
119 creation_date,
120 last_updated_by,
121 last_update_date,
122 last_update_login,
123 emission_scope_lookup_code,
124 location_code,
125 measurement_criteria)
126 VALUES (x_txn_type_lookup_code,
127 x_emission_id,
128 x_emission_batch_id,
129 x_emission_source_id,
130 x_facility_id,
131 x_emission_type_lookup_code,
132 x_usage_quantity,
133 x_unit_of_measure,
134 x_uom_conversion,
135 x_emission_quantity,
136 x_energy_quantity,
137 x_emission_date_from,
138 x_emission_date_to,
139 x_description,
140 x_org_id,
141 x_set_of_books_id,
142 x_invoice_id,
143 x_emission_line_number,
144 x_distribution_line_number,
145 x_invoice_line_number,
146 x_vendor_id,
147 x_vendor_site_id,
148 x_inventory_item_id,
149 x_ghg_asset_id,
150 x_attribute1,
151 x_attribute2,
152 x_attribute3,
153 x_attribute4,
154 x_attribute5,
155 x_attribute6,
156 x_attribute7,
157 x_attribute8,
158 x_attribute9,
159 x_attribute10,
160 x_attribute11,
161 x_attribute12,
162 x_attribute13,
163 x_attribute14,
164 x_attribute15,
165 x_attribute16,
166 x_attribute17,
167 x_attribute18,
168 x_attribute19,
169 x_attribute20,
170 x_attribute_category,
171 x_created_by,
172 x_creation_date,
173 x_last_updated_by,
174 x_last_update_date,
175 x_last_update_login,
176 x_scope,
177 x_location,
178 x_measurement_criteria);
179 --exception
180 --when others then
181 -- APP_EXCEPTION.RAISE_EXCEPTION;
182 end;
183
184 v_debug_info := 'Open cursor c';
185 OPEN c;
186
187 v_debug_info := 'Fetch cursor c';
188 FETCH c INTO x_rowid;
189
190 IF (c%notfound) THEN
191 v_debug_info := 'Close cursor c - ROW NOT FOUND';
192 CLOSE c;
193 RAISE no_data_found;
194 END IF;
195
196 v_debug_info := 'Close cursor c';
197 CLOSE c;
198
199 v_debug_info := 'At Calculate emissions';
200 GHG_EMISSION_RATES_PKG.calculate_emissions(x_emission_id);
201
202
203 v_debug_info := 'At Commit';
204 IF NVL(x_commit_flag, 'N') = 'Y' THEN
205 COMMIT;
206 END IF;
207
208
209 EXCEPTION
210 WHEN OTHERS THEN
211 IF x_called_from_api = 'Y' THEN
212 RAISE;
213 ELSE
214 IF (SQLCODE <> -20001) THEN
215 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
216 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
217 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
218 END IF;
219 APP_EXCEPTION.RAISE_EXCEPTION;
220 END IF;
221
222 END insert_row;
223
224 PROCEDURE update_row (x_rowid IN OUT NOCOPY VARCHAR2,
225 x_txn_type_lookup_code VARCHAR2,
226 x_emission_id NUMBER,
227 x_emission_batch_id NUMBER,
228 x_emission_source_id NUMBER,
229 x_facility_id NUMBER,
230 x_emission_type_lookup_code VARCHAR2,
231 x_usage_quantity NUMBER,
232 x_unit_of_measure VARCHAR2,
233 x_uom_conversion NUMBER,
234 x_emission_quantity NUMBER,
235 x_energy_quantity NUMBER,
236 x_emission_date_from DATE,
237 x_emission_date_to DATE,
238 x_description VARCHAR2,
239 x_org_id NUMBER,
240 x_set_of_books_id NUMBER,
241 x_invoice_id NUMBER,
242 x_emission_line_number NUMBER,
243 x_distribution_line_number NUMBER,
244 x_invoice_line_number NUMBER,
245 x_vendor_id NUMBER,
246 x_vendor_site_id NUMBER,
247 x_inventory_item_id NUMBER,
248 x_ghg_asset_id NUMBER,
249 x_attribute1 VARCHAR2,
250 x_attribute2 VARCHAR2,
251 x_attribute3 VARCHAR2,
252 x_attribute4 VARCHAR2,
253 x_attribute5 VARCHAR2,
254 x_attribute6 VARCHAR2,
255 x_attribute7 VARCHAR2,
256 x_attribute8 VARCHAR2,
257 x_attribute9 VARCHAR2,
258 x_attribute10 VARCHAR2,
259 x_attribute11 VARCHAR2,
260 x_attribute12 VARCHAR2,
261 x_attribute13 VARCHAR2,
262 x_attribute14 VARCHAR2,
263 x_attribute15 VARCHAR2,
264 x_attribute16 VARCHAR2,
265 x_attribute17 VARCHAR2,
266 x_attribute18 VARCHAR2,
267 x_attribute19 VARCHAR2,
268 x_attribute20 VARCHAR2,
269 x_attribute_category VARCHAR2,
270 x_created_by NUMBER,
271 x_creation_date DATE,
272 x_last_updated_by NUMBER,
273 x_last_update_date DATE,
274 x_last_update_login NUMBER,
275 x_scope VARCHAR2,
276 x_location VARCHAR2,
277 x_measurement_criteria VARCHAR2) IS
278
279 v_debug_info VARCHAR2(100);
280
281 BEGIN
282
283 v_debug_info := 'Updating GHG_transactions_all';
284
285 UPDATE GHG_transactions_all
286 SET txn_type_lookup_code = x_txn_type_lookup_code,
287 transaction_id = x_emission_id,
288 transaction_batch_id = x_emission_batch_id,
289 transaction_source_id = x_emission_source_id,
290 ghg_organization_id = x_facility_id,
291 transaction_type_lookup_code = x_emission_type_lookup_code,
292 usage_quantity = x_usage_quantity,
293 unit_of_measure = x_unit_of_measure,
294 uom_conversion = x_uom_conversion,
295 emission_quantity = x_emission_quantity,
296 energy_quantity = x_energy_quantity,
297 transaction_date_from = x_emission_date_from,
298 transaction_date_to = x_emission_date_to,
299 description = x_description,
300 org_id = x_org_id,
301 set_of_books_id = x_set_of_books_id,
302 invoice_id = x_invoice_id,
303 transaction_line_number = x_emission_line_number,
304 distribution_line_number = x_distribution_line_number,
305 invoice_line_number = x_invoice_line_number,
306 vendor_id = x_vendor_id,
307 vendor_site_id = x_vendor_site_id,
308 inventory_item_id = x_inventory_item_id,
309 ghg_asset_id = x_ghg_asset_id,
310 attribute1 = x_attribute1,
311 attribute2 = x_attribute2,
312 attribute3 = x_attribute3,
313 attribute4 = x_attribute4,
314 attribute5 = x_attribute5,
315 attribute6 = x_attribute6,
316 attribute7 = x_attribute7,
317 attribute8 = x_attribute8,
318 attribute9 = x_attribute9,
319 attribute10 = x_attribute10,
320 attribute11 = x_attribute11,
321 attribute12 = x_attribute12,
322 attribute13 = x_attribute13,
323 attribute14 = x_attribute14,
324 attribute15 = x_attribute15,
325 attribute16 = x_attribute16,
326 attribute17 = x_attribute17,
327 attribute18 = x_attribute18,
328 attribute19 = x_attribute19,
329 attribute20 = x_attribute20,
330 attribute_category = x_attribute_category,
331 created_by = x_created_by,
332 creation_date = x_creation_date,
333 last_updated_by = x_last_updated_by,
334 last_update_date = x_last_update_date,
335 last_update_login = x_last_update_login,
336 emission_scope_lookup_code = x_scope,
337 location_code = x_location,
338 measurement_criteria = x_measurement_criteria
339 WHERE rowid = x_rowid;
340
341 IF (SQL%NOTFOUND) THEN
342 RAISE NO_DATA_FOUND;
343 END IF;
344
345 GHG_EMISSION_RATES_PKG.calculate_emissions(x_emission_id);
346
347 EXCEPTION
348 WHEN OTHERS THEN
349 IF (SQLCODE <> -20001) THEN
350 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
351 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
352 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
353 END IF;
354 APP_EXCEPTION.RAISE_EXCEPTION;
355
356 END update_row;
357
358 PROCEDURE delete_row (x_emission_id NUMBER) IS
359
360 BEGIN
361
362 DELETE FROM GHG_transactions_all
363 WHERE transaction_id = x_emission_id;
364
365 DELETE FROM GHG_transaction_details_all
366 WHERE transaction_id = x_emission_id;
367
368 IF (sql%notfound) THEN
369 RAISE no_data_found;
370 END IF;
371
372 COMMIT;
373
374 END delete_row;
375
376 PROCEDURE lock_row (x_rowid IN OUT NOCOPY VARCHAR2,
377 x_txn_type_lookup_code VARCHAR2,
378 x_emission_id NUMBER,
379 x_emission_batch_id NUMBER,
380 x_emission_source_id NUMBER,
381 x_facility_id NUMBER,
382 x_emission_type_lookup_code VARCHAR2,
383 x_usage_quantity NUMBER,
384 x_unit_of_measure VARCHAR2,
385 x_uom_conversion NUMBER,
386 x_emission_quantity NUMBER,
387 x_energy_quantity NUMBER,
388 x_emission_date_from DATE,
389 x_emission_date_to DATE,
390 x_description VARCHAR2,
391 x_org_id NUMBER,
392 x_set_of_books_id NUMBER,
393 x_invoice_id NUMBER,
394 x_emission_line_number NUMBER,
395 x_distribution_line_number NUMBER,
396 x_invoice_line_number NUMBER,
397 x_vendor_id NUMBER,
398 x_vendor_site_id NUMBER,
399 x_inventory_item_id NUMBER,
400 x_ghg_asset_id NUMBER,
401 x_attribute1 VARCHAR2,
402 x_attribute2 VARCHAR2,
403 x_attribute3 VARCHAR2,
404 x_attribute4 VARCHAR2,
405 x_attribute5 VARCHAR2,
406 x_attribute6 VARCHAR2,
407 x_attribute7 VARCHAR2,
408 x_attribute8 VARCHAR2,
409 x_attribute9 VARCHAR2,
410 x_attribute10 VARCHAR2,
411 x_attribute11 VARCHAR2,
412 x_attribute12 VARCHAR2,
413 x_attribute13 VARCHAR2,
414 x_attribute14 VARCHAR2,
415 x_attribute15 VARCHAR2,
416 x_attribute16 VARCHAR2,
417 x_attribute17 VARCHAR2,
418 x_attribute18 VARCHAR2,
419 x_attribute19 VARCHAR2,
420 x_attribute20 VARCHAR2,
421 x_attribute_category VARCHAR2,
422 x_created_by NUMBER,
423 x_creation_date DATE,
424 x_last_updated_by NUMBER,
425 x_last_update_date DATE,
426 x_last_update_login NUMBER,
427 x_scope VARCHAR2,
428 x_location VARCHAR2,
429 x_measurement_criteria VARCHAR2) IS
430
431 v_debug_info VARCHAR2(100);
432
433 CURSOR c IS
434 SELECT txn_type_lookup_code,
435 transaction_id,
436 transaction_batch_id,
437 transaction_source_id,
438 ghg_organization_id,
439 transaction_type_lookup_code,
440 usage_quantity,
441 unit_of_measure,
442 uom_conversion,
443 emission_quantity,
444 energy_quantity,
445 transaction_date_from,
446 transaction_date_to,
447 description,
448 org_id,
449 set_of_books_id,
450 invoice_id,
451 transaction_line_number,
452 distribution_line_number,
453 invoice_line_number,
454 vendor_id,
455 vendor_site_id,
456 inventory_item_id,
457 ghg_asset_id,
458 attribute1,
459 attribute2,
460 attribute3,
461 attribute4,
462 attribute5,
463 attribute6,
464 attribute7,
465 attribute8,
466 attribute9,
467 attribute10,
468 attribute11,
469 attribute12,
470 attribute13,
471 attribute14,
472 attribute15,
473 attribute16,
474 attribute17,
475 attribute18,
476 attribute19,
477 attribute20,
478 attribute_category,
479 created_by,
480 creation_date,
481 last_updated_by,
482 last_update_date,
483 last_update_login,
484 emission_scope_lookup_code,
485 location_code
486 FROM GHG_transactions_all
487 WHERE rowid = x_rowid
488 FOR UPDATE of transaction_id NOWAIT;
489 recinfo C%ROWTYPE;
490
491 BEGIN
492
493 /* v_debug_info := 'Open cursor C';
494
495 OPEN C;
496
497 v_debug_info := 'Fetch cursor C';
498
499 FETCH C INTO recinfo;
500
501 IF (C%NOTFOUND) THEN
502 v_debug_info := 'Close cursor C - ROW NOT FOUND';
503 CLOSE C;
504 RAISE NO_DATA_FOUND;
505 END IF;
506
507 v_debug_info := 'Close cursor C';
508
509 CLOSE C;
510
511 IF (((recinfo.txn_type_lookup_code = x_txn_type_lookup_code) OR (recinfo.txn_type_lookup_code IS NULL AND x_txn_type_lookup_code IS NULL))
512 AND ((recinfo.transaction_id = x_emission_id) OR (recinfo.transaction_id IS NULL AND x_emission_id IS NULL))
513 AND ((recinfo.transaction_batch_id = x_emission_batch_id) OR (recinfo.transaction_batch_id IS NULL AND x_emission_batch_id IS NULL))
514 AND ((recinfo.transaction_source_id = x_emission_source_id) OR (recinfo.transaction_source_id IS NULL AND x_emission_source_id IS NULL))
515 AND ((recinfo.ghg_organization_id = x_facility_id) OR (recinfo.ghg_organization_id IS NULL AND x_facility_id IS NULL))
516 AND ((recinfo.transaction_type_lookup_code = x_emission_type_lookup_code) OR (recinfo.transaction_type_lookup_code IS NULL AND x_emission_type_lookup_code IS NULL))
517 AND ((recinfo.usage_quantity = x_usage_quantity) OR (recinfo.usage_quantity IS NULL AND x_usage_quantity IS NULL))
518 AND ((recinfo.unit_of_measure = x_unit_of_measure) OR (recinfo.unit_of_measure IS NULL AND x_unit_of_measure IS NULL))
519 AND ((recinfo.uom_conversion = x_uom_conversion) OR (recinfo.uom_conversion IS NULL AND x_uom_conversion IS NULL))
520 AND ((recinfo.emission_quantity = x_emission_quantity) OR (recinfo.emission_quantity IS NULL AND x_emission_quantity IS NULL))
521 AND ((recinfo.energy_quantity = x_energy_quantity) OR (recinfo.energy_quantity IS NULL AND x_energy_quantity IS NULL))
522 AND ((recinfo.transaction_date_from = x_emission_date_from) OR (recinfo.transaction_date_from IS NULL AND x_emission_date_from IS NULL))
523 AND ((recinfo.transaction_date_to = x_emission_date_to) OR (recinfo.transaction_date_to IS NULL AND x_emission_date_to IS NULL))
524 AND ((recinfo.description = x_description) OR (recinfo.description IS NULL AND x_description IS NULL))
525 AND ((recinfo.org_id = x_org_id) OR (recinfo.org_id IS NULL AND x_org_id IS NULL))
526 AND ((recinfo.set_of_books_id = x_set_of_books_id) OR (recinfo.set_of_books_id IS NULL AND x_set_of_books_id IS NULL))
527 AND ((recinfo.invoice_id = x_invoice_id) OR (recinfo.invoice_id IS NULL AND x_invoice_id IS NULL))
528 AND ((recinfo.transaction_line_number = x_emission_line_number) OR (recinfo.transaction_line_number IS NULL AND x_emission_line_number IS NULL))
529 AND ((recinfo.distribution_line_number = x_distribution_line_number) OR (recinfo.distribution_line_number IS NULL AND x_distribution_line_number IS NULL))
530 AND ((recinfo.invoice_line_number = x_invoice_line_number) OR (recinfo.invoice_line_number IS NULL AND x_invoice_line_number IS NULL))
531 AND ((recinfo.vendor_id = x_vendor_id) OR (recinfo.vendor_id IS NULL AND x_vendor_id IS NULL))
532 AND ((recinfo.vendor_site_id = x_vendor_site_id) OR (recinfo.vendor_site_id IS NULL AND x_vendor_site_id IS NULL))
533 AND ((recinfo.inventory_item_id = x_inventory_item_id) OR (recinfo.inventory_item_id IS NULL AND x_inventory_item_id IS NULL))
534 AND ((recinfo.ghg_asset_id = x_ghg_asset_id) OR (recinfo.ghg_asset_id IS NULL AND x_ghg_asset_id IS NULL))
535 AND ((recinfo.attribute1 = x_attribute1) OR (recinfo.attribute1 IS NULL AND x_attribute1 IS NULL))
536 AND ((recinfo.attribute2 = x_attribute2) OR (recinfo.attribute2 IS NULL AND x_attribute2 IS NULL))
537 AND ((recinfo.attribute3 = x_attribute3) OR (recinfo.attribute3 IS NULL AND x_attribute3 IS NULL))
538 AND ((recinfo.attribute4 = x_attribute4) OR (recinfo.attribute4 IS NULL AND x_attribute4 IS NULL))
539 AND ((recinfo.attribute5 = x_attribute5) OR (recinfo.attribute5 IS NULL AND x_attribute5 IS NULL))
540 AND ((recinfo.attribute6 = x_attribute6) OR (recinfo.attribute6 IS NULL AND x_attribute6 IS NULL))
541 AND ((recinfo.attribute7 = x_attribute7) OR (recinfo.attribute7 IS NULL AND x_attribute7 IS NULL))
542 AND ((recinfo.attribute8 = x_attribute8) OR (recinfo.attribute8 IS NULL AND x_attribute8 IS NULL))
543 AND ((recinfo.attribute9 = x_attribute9) OR (recinfo.attribute9 IS NULL AND x_attribute9 IS NULL))
544 AND ((recinfo.attribute10 = x_attribute10) OR (recinfo.attribute10 IS NULL AND x_attribute10 IS NULL))
545 AND ((recinfo.attribute11 = x_attribute11) OR (recinfo.attribute11 IS NULL AND x_attribute11 IS NULL))
546 AND ((recinfo.attribute12 = x_attribute12) OR (recinfo.attribute12 IS NULL AND x_attribute12 IS NULL))
547 AND ((recinfo.attribute13 = x_attribute13) OR (recinfo.attribute13 IS NULL AND x_attribute13 IS NULL))
548 AND ((recinfo.attribute14 = x_attribute14) OR (recinfo.attribute14 IS NULL AND x_attribute14 IS NULL))
549 AND ((recinfo.attribute15 = x_attribute15) OR (recinfo.attribute15 IS NULL AND x_attribute15 IS NULL))
550 AND ((recinfo.attribute16 = x_attribute16) OR (recinfo.attribute16 IS NULL AND x_attribute16 IS NULL))
551 AND ((recinfo.attribute17 = x_attribute17) OR (recinfo.attribute17 IS NULL AND x_attribute17 IS NULL))
552 AND ((recinfo.attribute18 = x_attribute18) OR (recinfo.attribute18 IS NULL AND x_attribute18 IS NULL))
553 AND ((recinfo.attribute19 = x_attribute19) OR (recinfo.attribute19 IS NULL AND x_attribute19 IS NULL))
554 AND ((recinfo.attribute20 = x_attribute20) OR (recinfo.attribute20 IS NULL AND x_attribute20 IS NULL))
555 AND ((recinfo.attribute_category = x_attribute_category) OR (recinfo.attribute_category IS NULL AND x_attribute_category IS NULL))
556 AND ((recinfo.created_by = x_created_by) OR (recinfo.created_by IS NULL AND x_created_by IS NULL))
557 AND ((recinfo.creation_date = x_creation_date) OR (recinfo.creation_date IS NULL AND x_creation_date IS NULL))
558 AND ((recinfo.last_updated_by = x_last_updated_by) OR (recinfo.last_updated_by IS NULL AND x_last_updated_by IS NULL))
559 AND ((recinfo.last_update_date = x_last_update_date) OR (recinfo.last_update_date IS NULL AND x_last_update_date IS NULL))
560 AND ((recinfo.last_update_login = x_last_update_login) OR (recinfo.last_update_login IS NULL AND x_last_update_login IS NULL)) ) THEN
561 NULL;
562 ELSE
563 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
564 APP_EXCEPTION.RAISE_EXCEPTION;
565 END IF;
566 */
567 NULL;
568
569 EXCEPTION
570 WHEN OTHERS THEN
571 IF (SQLCODE <> -20001) THEN
572 IF (SQLCODE = -54) THEN
573 FND_MESSAGE.SET_NAME('GHG', 'GHG_RESOURCE_BUSY');
574 ELSE
575 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
576 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
577 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', v_debug_info);
578 END IF;
579 END IF;
580 APP_EXCEPTION.RAISE_EXCEPTION;
581
582 END lock_row;
583
584
585 FUNCTION get_emission_total (x_emission_id NUMBER) RETURN NUMBER IS
586 v_total NUMBER;
587 BEGIN
588 SELECT sum(transaction_value)
589 INTO v_total
590 FROM GHG_transaction_details_all
591 where transaction_id = x_emission_id
592 AND transaction_type = 'E';
593
594 RETURN v_total;
595
596 EXCEPTION
597 WHEN NO_DATA_FOUND
598 THEN RETURN 0;
599 END;
600
601
602 FUNCTION get_energy_total (x_emission_id NUMBER) RETURN NUMBER IS
603 v_total NUMBER;
604 BEGIN
605 SELECT sum(transaction_value)
606 INTO v_total
607 FROM GHG_transaction_details_all
608 where transaction_id = x_emission_id
609 AND transaction_type = 'N';
610
611 RETURN v_total;
612
613 EXCEPTION
614 WHEN NO_DATA_FOUND
615 THEN RETURN 0;
616 END;
617
618
619 PROCEDURE derive_emissions (P_ORG_ID number,
620 p_invoice_id NUMBER,
621 p_emission_source VARCHAR2,
622 p_scope VARCHAR2,
623 p_location VARCHAR2,
624 p_usage NUMBER,
625 p_uom VARCHAR2,
626 p_from_date DATE,
627 p_to_date DATE,
628 p_description VARCHAR2,
629 p_m_criteria VARCHAR2) IS
630
631 CURSOR get_emissions (p_invoice_id NUMBER) IS
632 SELECT transaction_id
633 FROM GHG_transactions_v
634 WHERE invoice_id = p_invoice_id;
635
636 CURSOR get_invoice_distributions (p_invoice_id NUMBER) IS
637 SELECT ai.vendor_id ,
638 ai.vendor_site_id,
639 ai.invoice_num,
640 ai.gl_date,
641 aid.distribution_line_number,
642 aid.invoice_line_number,
643 aid.amount,
644 aid.dist_code_combination_id code_combination_id,
645 aid.project,
646 aid.task
647 FROM ap_invoices_all ai,
648 ap_invoice_distributions_v aid,
649 fnd_lookup_values xnlv
650 WHERE ai.invoice_id = p_invoice_id
651 AND aid.invoice_id = ai.invoice_id
652 AND xnlv.lookup_code = aid.line_type_lookup_code
653 AND xnlv.lookup_type = 'GHG_EMISSION_LINE_TYPES'
654 AND SYSDATE BETWEEN NVL(xnlv.start_date_active, SYSDATE - 1)
655 AND NVL(xnlv.end_date_active, SYSDATE + 1)
656 ORDER BY aid.distribution_line_number;
657
658 v_invoice_item_total NUMBER;
659 v_org_id NUMBER;
660 v_set_of_books_id NUMBER;
661 v_chart_of_accounts_id NUMBER;
662 v_map_accounts_flag VARCHAR2(5);
663 v_map_operating_unit_flag VARCHAR2(5);
664 v_map_projects_flag VARCHAR2(5);
665 v_facility_id_for_op_unit NUMBER;
666 v_facility VARCHAR2(100);
667 v_facility_id NUMBER;
668 v_invoice_msg VARCHAR2(255);
669
670 BEGIN
671
672 mo_global.set_policy_context_server(p_access_mode => 'S',p_org_id =>P_ORG_ID);
673
674 -- Get Invoice Item Total
675 SELECT sum(aid.amount)
676 INTO v_invoice_item_total
677 FROM ap_invoices_all ai,
678 ap_invoice_distributions_v aid,
679 fnd_lookup_values xnlv
680 WHERE ai.invoice_id = p_invoice_id
681 AND aid.invoice_id = ai.invoice_id
682 AND xnlv.lookup_code = aid.line_type_lookup_code
683 AND xnlv.lookup_type = 'GHG_EMISSION_LINE_TYPES'
684 AND SYSDATE BETWEEN NVL(xnlv.start_date_active, SYSDATE - 1)
685 AND NVL(xnlv.end_date_active, SYSDATE + 1);
686
687
688 -- If the current invoice has emissions, then archive them to history and then delete them.
689 FOR emission_rec IN get_emissions(p_invoice_id) LOOP
690 GHG_transactions_history_pkg.insert_row(x_transaction_id => emission_rec.transaction_id);
691 GHG_transactions_pkg.delete_row(x_emission_id => emission_rec.transaction_id);
692 delete from GHG_transaction_details_all where transaction_id = emission_rec.transaction_id;
693 END LOOP;
694
695 SELECT asp.org_id,
696 asp.set_of_books_id,
697 gsob.chart_of_accounts_id
698 INTO v_org_id,
699 v_set_of_books_id,
700 v_chart_of_accounts_id
701 FROM ap_system_parameters asp,
702 gl_sets_of_books gsob
703 WHERE gsob.set_of_books_id = asp.set_of_books_id;
704
705 -- Determine how we are mapping the facility.
706
707 SELECT map_accounts_flag,
708 map_operating_unit_flag,
709 map_projects_flag
710 INTO v_map_accounts_flag,
711 v_map_operating_unit_flag,
712 v_map_projects_flag
713 FROM GHG_segment_mappings_v;
714
715 /* Lakshmi Venkatraman 03/18/2011 - Commenting out this section of code as not required any more
716 -- If we are setup to map to operating unit then get the mapping value now.
717 IF v_map_operating_unit_flag = 'Y' THEN
718
719 SELECT facility_id
720 INTO v_facility_id_for_op_unit
721 FROM XX_GHGAS_facility_ou_map_v;
722
723 END IF;
724 */
725
726 FND_MESSAGE.SET_NAME('GHG','GHG_INVOICE_BATCH_NUM_PREFIX');
727 v_invoice_msg := FND_MESSAGE.GET;
728
729 -- Loop For Each DIST
730 FOR dist_rec in get_invoice_distributions(p_invoice_id) LOOP
731 BEGIN
732 IF v_map_accounts_flag = 'Y' THEN
733 IF v_map_projects_flag = 'Y' THEN
734 IF dist_rec.project IS NOT NULL THEN
735 -- Note that the package being called will issue an error message if it can't find a mapping, so there's no need
736 -- for this trigger to handle this exception.
737 v_facility_id := GHG_organization_mappings_pkg.find_project_mapping(dist_rec.distribution_line_number, dist_rec.project, dist_rec.task, dist_rec.gl_date);
738 ELSE
739 v_facility_id := GHG_organization_mappings_pkg.find_flexfield_mapping(dist_rec.distribution_line_number, dist_rec.code_combination_id, v_chart_of_accounts_id, dist_rec.gl_date);
740 END IF;
741 ELSE
742 v_facility_id := GHG_organization_mappings_pkg.find_flexfield_mapping(dist_rec.distribution_line_number, dist_rec.code_combination_id, v_chart_of_accounts_id, dist_rec.gl_date);
743 END IF;
744 ELSIF v_map_projects_flag = 'Y' THEN
745 IF dist_rec.project IS NOT NULL THEN
746 v_facility_id := GHG_organization_mappings_pkg.find_project_mapping(dist_rec.distribution_line_number, dist_rec.project, dist_rec.task, dist_rec.gl_date);
747 ELSIF v_map_operating_unit_flag = 'Y' THEN
748 v_facility_id := v_facility_id_for_op_unit;
749 ELSE
750 v_facility_id := -1;
751 END IF;
752 /* Lakshmi Venkatraman 03/18/2011 - Commenting out this section of code as not required any more
753 ELSIF v_map_operating_unit_flag = 'Y' THEN
754 v_facility_id := v_facility_id_for_op_unit;
755 */
756 ELSE
757 -- Sending to Bucket
758 -- To -1 Facfilty
759 v_facility_id := -1;
760 END IF;
761 EXCEPTION
762 WHEN OTHERS THEN
763 -- Sending to Bucket
764 -- To -1 Facfilty
765 v_facility_id := -1;
766 END ;
767
768 SELECT ghg_organization_code
769 into v_facility
770 FROM GHG_organizations
771 WHERE ghg_organization_id = v_facility_id;
772
773
774 GHG_TRANSACTIONS_API_PKG.create_transaction(
775 p_org_id => v_org_id,
776 p_batch_type => 'INT_INVOICE' ,
777 p_batch_sub_type => NULL ,
778 p_batch_number =>
779 v_invoice_msg || dist_rec.invoice_num,
780 p_batch_description => NULL,
781 p_facility => v_facility,
782 p_emission_source_name => p_emission_source,
783 p_emission_scope => p_scope,
784 p_emission_location => p_location,
785 p_emission_usage => p_usage * (dist_rec.amount/v_invoice_item_total),
786 p_emission_uom => p_uom,
787 p_emission_from_date => p_from_date,
788 p_emission_to_date => p_to_date,
789 p_emission_description => p_description,
790 p_supplier => dist_rec.vendor_id,
791 p_supplier_site_name => dist_rec.vendor_site_id,
792 p_item_number => NULL,
793 p_ghgas_asset => NULL,
794 p_invoice_id => p_invoice_id,
795 p_invoice_line_num => dist_rec.invoice_line_number,
796 p_invoice_dist_num => dist_rec.distribution_line_number,
797 p_m_criteria => p_m_criteria);
798
799
800
801 END LOOP ;
802 EXCEPTION
803 WHEN OTHERS THEN
804
805 FND_MESSAGE.SET_NAME('GHG', SQLERRM);
806 -- FND_MESSAGE.SET_NAME('GHG', 'GHG_NO_RATE_COVERAGE');
807 APP_EXCEPTION.RAISE_EXCEPTION;
808
809 END;
810
811 PROCEDURE cancel_invoice (x_invoice_id NUMBER,
812 x_mesg IN OUT NOCOPY VARCHAR2) is
813
814 CURSOR cancel_emissions (p_invoice_id number) IS
815 SELECT TRANSACTION_ID
816 FROM GHG_transactions
817 WHERE invoice_id = p_invoice_id;
818 BEGIN
819
820
821 FOR cancel_emission IN cancel_emissions(x_invoice_id) LOOP
822
823 -- Move emission to history
824 GHG_TRANSACTIONS_HISTORY_PKG.insert_row(cancel_emission.transaction_id);
825
826 delete from GHG_transactions where transaction_id = cancel_emission.transaction_id;
827 delete from GHG_transaction_details_all where transaction_id = cancel_emission.transaction_id;
828 commit;
829 END LOOP ;
830 END;
831
832 procedure derive_inv_emissions (p_material_trx_set_id number,
833 -- P_CCID NUMBER,
834 P_ITEM_ID NUMBER,
835 P_UOM VARCHAR2,
836 P_QUANTITY NUMBER,
837 P_TRANS_SOURCE VARCHAR2,
838 P_TRANS_DATE DATE,
839 p_TRANS_REF VARCHAR2,
840 p_org_id NUMBER,
841 p_m_criteria VARCHAR2) is
842
843 lp_org_id NUMBER;
844 v_org_id NUMBER;
845 v_set_of_books_id NUMBER;
846 v_chart_of_accounts_id NUMBER;
847 v_map_accounts_flag VARCHAR2(5);
848 v_map_operating_unit_flag VARCHAR2(5);
849 v_map_projects_flag VARCHAR2(5);
850 v_facility_id_for_op_unit NUMBER;
851 v_facility VARCHAR2(100);
852 v_facility_id NUMBER;
853 v_emission_source VARCHAR2(100);
854 v_scope_lookup_code VARCHAR2(10);
855 v_location_code VARCHAR2(30);
856 v_uom VARCHAR2(100);
857 v_item_number VARCHAR2(100);
858 v_count number;
859 vl_debug varchar2(4000);
860
861 BEGIN
862
863 --lvenkatr 07/06/2012: Modified code to obtain OU from Inv Org from Org_Organization_Definitions
864 -- instead of hr_organization_information
865
866 SELECT ood.operating_unit
867 INTO lp_org_id
868 FROM org_organization_definitions ood
869 WHERE ood.organization_id=p_org_id;
870
871 /** Commented on 07/06/2012
872 --ssurendr: Added code to obtain OU from Inv Org
873 SELECT org_information3
874 INTO lp_org_id
875 FROM hr_organization_information
876 WHERE organization_id=p_org_id
877 AND ORG_INFORMATION_CONTEXT='Accounting Information';
878 **/
879
880 mo_global.set_policy_context_server(p_access_mode => 'S',p_org_id =>lp_org_id);
881
882 select count(*)
883 into v_count
884 from GHG_transaction_batches b,
885 GHG_TRANSACTIONS e
886 where b.transaction_batch_id = e.transaction_batch_id
887 and b.transaction_batch_number = 'INV Issue:'||P_MATERIAL_TRX_SET_ID
888 and e.description = p_TRANS_REF;
889
890 IF v_count <> 0 THEN
891 --Bug 12646349 - SSURENDR
892
893 delete from ghg_transaction_details d
894 where exists (
895 select 1
896 from GHG_transaction_batches b,
897 GHG_TRANSACTIONS e
898 where b.transaction_batch_id = e.transaction_batch_id
899 and b.transaction_batch_number = 'INV Issue:'||P_MATERIAL_TRX_SET_ID
900 and e.description = p_TRANS_REF
901 and e.transaction_id = d.transaction_id
902 );
903
904 delete from ghg_transactions e
905 where e.description = p_TRANS_REF
906 and exists (
907 select 1
908 from GHG_transaction_batches b
909 where b.transaction_batch_id = e.transaction_batch_id
910 and b.transaction_batch_number = 'INV Issue:'||P_MATERIAL_TRX_SET_ID
911 );
912
913 END IF;
914
915 SELECT asp.org_id,
916 asp.set_of_books_id,
917 gsob.chart_of_accounts_id
918 into v_org_id,
919 v_set_of_books_id,
920 v_chart_of_accounts_id
921 FROM ap_system_parameters asp,
922 gl_sets_of_books gsob
923 WHERE gsob.set_of_books_id = asp.set_of_books_id;
924
925
926 -- Determine how we are mapping the facility.
927 vl_debug := 'Getting facility for asset '||P_trans_source;
928 select location_description
929 into v_facility
930 from ghg_assets_v
931 where asset_number = p_trans_source
932 and trunc(P_TRANS_DATE) between start_date and nvl(end_date,GHG_UTILITIES_PKG.end_date);
933
934
935 vl_debug := 'Org :'||v_org_id;
936
937 SELECT distinct src.source_name,
938 def.scope_lookup_code,
939 def.location_code,
940 vl.CONCATENATED_SEGMENTS item_code
941 INTO v_emission_source,
942 v_scope_lookup_code,
943 v_location_code,
944 v_item_number
945 from ghg_supplier_items def
946 , GHG_SOURCES src
947 , mtl_system_items_kfv vl
948 WHERE def.source_id = src.source_id
949 AND def.inventory_item_id = P_ITEM_ID
950 AND def.inventory_item_id = vl.inventory_item_id;
951 --AND vl.organization_id = v_org_id;
952
953 vl_debug := 'Getting UOM';
954 select unit_of_measure
955 into v_uom
956 from mtl_units_of_measure_vl
957 where uom_code = P_uom;
958
959 vl_debug := '1:';
960
961
962 GHG_TRANSACTIONS_API_PKG.create_transaction(
963 p_org_id => v_org_id,
964 p_batch_type => 'ITEM' ,
965 p_batch_sub_type => NULL ,
966 p_batch_number => 'INV Issue:'||P_MATERIAL_TRX_SET_ID,
967 p_batch_description => P_trans_source,
968 p_facility => v_facility,
969 p_emission_source_name => v_emission_source,
970 p_emission_scope => v_scope_lookup_code,
971 p_emission_location => v_location_code,
972 p_emission_usage => P_quantity ,
973 p_emission_uom => v_uom,
974 p_emission_from_date => P_trans_date,
975 p_emission_to_date => P_trans_date,
976 p_emission_description => p_TRANS_REF,
977 p_supplier => NULL,
978 p_supplier_site_name => NULL,
979 p_item_number => v_item_number,
980 p_ghgas_asset => P_trans_source ,
981 p_invoice_id => NULL,
982 p_invoice_line_num => NULL,
983 p_invoice_dist_num => NULL,
984 p_m_criteria => p_m_criteria,
985 p_commit_flag => 'N'
986 );
987
988 --END IF;
989
990 EXCEPTION
991 WHEN OTHERS THEN
992
993 FND_MESSAGE.SET_NAME('GHG', '*'||vl_debug||'*-'||SQLERRM);
994
995 APP_EXCEPTION.RAISE_EXCEPTION;
996
997 END;
998
999
1000 FUNCTION get_inv_reference (
1001 p_old_reference NUMBER --Bug 12646349 - SSURENDR
1002 ) RETURN NUMBER IS
1003 vl_nextval NUMBER;
1004 BEGIN
1005 IF p_old_reference IS NOT NULL THEN --Bug 12646349 - SSURENDR
1006 vl_nextval := p_old_reference;
1007 ELSE
1008 select GHG_TRANSACTIONS_S.NEXTVAL
1009 into vl_nextval
1010 from dual;
1011 END IF;
1012
1013 RETURN vl_nextval;
1014
1015 END;
1016
1017 FUNCTION get_inv_default_item (p_asset_num VARCHAR2) RETURN VARCHAR2 IS
1018 vl_item_number VARCHAR2(50);
1019 BEGIN
1020 SELECT min(xnir.inventory_item)
1021 into vl_item_number
1022 FROM GHG_item_factors_v xnir,
1023 GHG_assets xna
1024 WHERE xnir.item_type_lookup_code = 'I'
1025 AND sysdate BETWEEN NVL(xnir.start_date, sysdate - 1)
1026 AND NVL(xnir.end_date, GHG_UTILITIES_PKG.end_date)
1027 AND xna.asset_number = p_asset_num
1028 AND xna.transport_type_lookup_code = xnir.transport_type_lookup_code;
1029
1030 RETURN vl_item_number;
1031 END;
1032
1033
1034 END GHG_transactions_pkg;