[Home] [Help]
PACKAGE BODY: APPS.GHG_EMISSION_RATES_PKG
Source
1 PACKAGE BODY GHG_EMISSION_RATES_PKG AS
2 /*$Header: ghgemrtb.pls 120.3.12020000.4 2013/03/01 07:49:00 mgijare ship $ */
3
4 FUNCTION get_max_factor_date (x_emission_source_id number ,
5 x_code_combination_id number,
6 x_scope_lookup_code number,
7 x_location_code varchar2,
8 x_transport_type_lookup_code varchar2,
9 x_supplier_id number,
10 x_supplier_site_id number,
11 x_item_id number,
12 x_item_type varchar2,
13 x_facility_id number
14 ) return date IS
15 v_max_date date;
16 BEGIN
17 select max(nvl(end_date+1,start_date)) --ssurendr: Changed GHG_UTILITIES_PKG.end_date to start_date so that duplicate record validation correctly happens.
18 into v_max_date
19 from GHG_source_combinations_all
20 where /*code_combination_id = x_code_combination_id --Lakshmi 04/27/11 - nvl is removed so that correct start date is returned.
21 ssurendr 05/06 - code combination removed from join
22 and*/ source_id = x_emission_source_id
23 and nvl(supplier_id, -1) = nvl(x_supplier_id, -1)
24 and nvl(supplier_site_id, -1) = nvl(x_supplier_site_id, -1)
25 and nvl(item_id, -1) = nvl(x_item_id, -1)
26 and nvl(item_type, -1) = nvl(x_item_type, -1)
27 and nvl(ghg_organization_id, -1) = nvl(x_facility_id, -1)
28 and nvl(scope_lookup_code, -1) = nvl(x_scope_lookup_code, -1)
29 and nvl(location_code, -1) = nvl(x_location_code, -1)
30 and nvl(transport_type_lookup_code, -1) = nvl(x_transport_type_lookup_code, -1);
31
32 return nvl(v_max_date,GHG_UTILITIES_PKG.start_date);
33
34 END;
35
36 FUNCTION lock_date (x_emission_source_id number,
37 x_code_combination_id number,
38 x_date date,
39 x_supplier_id number,
40 x_supplier_site_id number,
41 x_item_id number,
42 x_item_type varchar2,
43 x_facility_id number) return boolean IS
44 v_count number;
45 BEGIN
46 select count(*)
47 into v_count
48 from GHG_source_combinations_all
49 where code_combination_id = x_code_combination_id
50 and source_id = x_emission_source_id
51 and nvl(supplier_id,-1) = nvl(x_supplier_id,-1)
52 and nvl(supplier_site_id,-1) = nvl(x_supplier_site_id,-1)
53 and nvl(item_id,-1) = nvl(x_item_id,-1)
54 and nvl(item_type,-1) = nvl(x_item_type,-1)
55 and nvl(ghg_organization_id,-1) = nvl(x_facility_id,-1)
56 and x_date < start_date;
57
58 IF v_count > 0 THEN
59 RETURN TRUE;
60 ELSE
61 RETURN FALSE;
62 END IF;
63 END;
64
65
66 FUNCTION generate_rate_id RETURN NUMBER IS
67 v_rate_id NUMBER;
68 BEGIN
69 select GHG_source_combinations_s.NEXTVAL
70 into v_rate_id
71 from dual;
72
73 RETURN v_rate_id;
74 END;
75
76 FUNCTION rate_in_use (x_rate_id NUMBER) RETURN BOOLEAN IS
77 v_row_count NUMBER;
78 BEGIN
79
80 select count(*)
81 into v_row_count
82 from GHG_transaction_details_all
83 where source_combination_id = x_rate_id;
84
85 if v_row_count > 1 THEN
86 RETURN TRUE;
87 else
88 RETURN FALSE;
89 end if;
90 END;
91
92 PROCEDURE calculate_emissions ( x_emission_id number ) IS
93
94 v_emission_date_factor number;
95 v_emission_quantity number;
96 v_emission_date_from date;
97 v_emission_factor_id number;
98 v_uom_conversion number;
99 v_emission_scope number;
100 v_location varchar2(20);
101 v_transport_type varchar2(20);
102 v_supplier_id number;
103 v_supplier_site_id number;
104 v_item_id number;
105 v_asset_id number;
106 v_facility_id number;
107 v_source_id number;
108 v_uom_class varchar2(100);
109 v_uom varchar2(100);
110 v_org_id number;
111
112 BEGIN
113
114 --Start of Code--Added by neechand as per Bug 14741401 on 23rd jan 2013
115 insert into ghg_txn_details_history_all(TRANSACTION_ID,
116 TRANSACTION_DATE,
117 TRANSACTION_TYPE,
118 TRANSACTION_COMPONENT_TYPE,
119 TRANSACTION_VALUE,
120 SOURCE_COMBINATION_ID,
121 ORG_ID,
122 CREATED_BY,
123 CREATION_DATE,
124 LAST_UPDATED_BY,
125 LAST_UPDATE_DATE,
126 LAST_UPDATE_LOGIN,
127 REQUEST_ID)
128 select a.transaction_id,
129 a.transaction_date,
130 a.transaction_type,
131 a.transaction_component_type,
132 a.transaction_value,
133 a.source_combination_id,
134 a.org_id,
135 fnd_global.user_id,
136 sysdate,
137 fnd_global.user_id,
138 sysdate,
139 fnd_global.user_id,
140 fnd_global.conc_request_id
141 from ghg_transaction_details_all a
142 where a.transaction_id = x_emission_id;
143
144 --End of Code--Added by neechand as per Bug 14741401 on 23rd jan 2013
145
146 delete from GHG_transaction_details_all
147 where transaction_id = x_emission_id;
148
149 -- dbms_output.put_line('Create Emissions:'||x_emission_id);
150
151 -- Get Emission Date Range
152 select (trunc(transaction_date_to) - trunc(transaction_date_from))+ 1,
153 usage_quantity/((trunc(transaction_date_to) - trunc(transaction_date_from))+ 1),
154 trunc(transaction_date_from),
155 uom_conversion,
156 emission_scope_lookup_code,
157 location_code,
158 vendor_id,
159 vendor_site_id,
160 inventory_item_id,
161 ghg_asset_id,
162 ghg_organization_id,
163 transaction_source_id,
164 unit_of_measure,
165 org_id
166 into v_emission_date_factor,
167 v_emission_quantity,
168 v_emission_date_from,
169 v_uom_conversion,
170 v_emission_scope,
171 v_location,
172 v_supplier_id,
173 v_supplier_site_id,
174 v_item_id,
175 v_asset_id,
176 v_facility_id,
177 v_source_id,
178 v_uom,
179 v_org_id
180 from GHG_transactions_all
181 where transaction_id = x_emission_id;
182
183 -- dbms_output.put_line('Create Emissions: Get UOM :');
184 -- Get UOM Class off Unit Of Measure..
185 select uom_class
186 into v_uom_class
187 from mtl_units_of_measure_vl
188 where unit_of_measure = v_uom;
189
190 -- dbms_output.put_line('Create Emissions: Get UOM :'||v_uom_class);
191
192 for days in 0..v_emission_date_factor-1 loop
193
194 -- Get Rate
195 -- dbms_output.put_line('Create Emissions: Get for date :'||to_char(v_emission_date_from+days));
196
197 v_emission_factor_id:=get_factor(v_source_id,
198 v_emission_scope,
199 v_location,
200 v_emission_date_from+days,
201 v_supplier_id,
202 v_supplier_site_id,
203 v_item_id,
204 v_asset_id,
205 v_facility_id) ;
206
207
208
209 create_ledger_entry ( x_emission_id, v_emission_factor_id, v_emission_quantity*nvl(v_uom_conversion,1),v_emission_date_from+days, v_uom_class,v_org_id);
210
211 end loop;
212
213 update GHG_transactions_all
214 set emission_quantity = GHG_transactions_pkg.get_emission_total(x_emission_id)
215 , energy_quantity = GHG_transactions_pkg.get_energy_total(x_emission_id)
216 , emission_SCOPE_LOOKUP_CODE =
217 (SELECT decode(emission_SCOPE_LOOKUP_CODE,
218 '-99999', SCOPE_LOOKUP_CODE,
219 emission_SCOPE_LOOKUP_CODE)
220 FROM GHG_source_combinations_all
221 where SOURCE_COMBINATION_ID=v_emission_factor_id)
222 where transaction_id = x_emission_id;
223
224
225 END calculate_emissions;
226
227
228 FUNCTION are_there_any_rates (x_source_id NUMBER,
229 x_scope VARCHAR2,
230 x_location VARCHAR2,
231 x_start_date DATE,
232 x_end_date DATE,
233 x_supplier_id NUMBER,
234 x_supplier_site_id NUMBER,
235 x_item_id NUMBER,
236 x_asset_id NUMBER,
237 x_facility_id NUMBER
238 ) RETURN BOOLEAN IS
239 v_factor_id NUMBER;
240 BEGIN
241 -- make sure there is a rate that will cover the complete range of the transaction
242 FOR days in 0..(trunc(x_end_date)-trunc(x_start_date)) LOOP
243 v_factor_id:=get_factor(x_source_id,
244 x_scope,
245 x_location,
246 x_start_date+days,
247 x_supplier_id,
248 x_supplier_site_id,
249 x_item_id,
250 x_asset_id,
251 x_facility_id);
252
253 IF v_factor_id = -1 THEN
254 RETURN FALSE;
255 END IF;
256 END LOOP;
257 RETURN TRUE;
258 END;
259
260
261 FUNCTION get_factor (x_source_id NUMBER,
262 x_scope VARCHAR2,
263 x_location VARCHAR2,
264 x_emission_date DATE,
265 x_supplier_id NUMBER ,
266 x_supplier_site_id NUMBER ,
267 x_item_id NUMBER ,
268 x_asset_id NUMBER ,
269 x_facility_id NUMBER
270 ) RETURN NUMBER IS
271
272 v_factor_id NUMBER := -1;
273 v_transport_type VARCHAR2(100);
274 v_debug varchar2(4000);
275 v_location varchar2(100);
276 v_out_location varchar2(100) := 'ALL';
277
278 FUNCTION get_factor_id
279 (x_source_id NUMBER,
280 x_scope VARCHAR2,
281 x_location VARCHAR2,
282 x_emission_date DATE,
283 x_supplier_id NUMBER DEFAULT NULL ,
284 x_supplier_site_id NUMBER DEFAULT NULL,
285 x_item_id NUMBER DEFAULT NULL,
286 x_transport_type VARCHAR2 DEFAULT 'DEFAULT',
287 x_facility_id NUMBER DEFAULT NULL,
288 x_out_location OUT NOCOPY VARCHAR2 -- 12565482 : ssurendr
289 ) RETURN NUMBER IS
290
291 v_emission_factor_id NUMBER;
292
293 BEGIN
294
295 SELECT source_combination_id,
296 location_code -- 12565482 : ssurendr
297 INTO v_emission_factor_id,
298 x_out_location -- 12565482 : ssurendr
299 FROM
300 (SELECT source_combination_id,
301 location_code -- 12565482 : ssurendr
302 FROM GHG_source_combinations_all
303 WHERE source_id = x_source_id
304 AND scope_lookup_code = decode(x_scope,
305 '-99999', scope_lookup_code,
306 null, scope_lookup_code,
307 x_scope)
308 AND decode(location_code,'ALL',x_location,location_code) = x_location
309 AND decode(transport_type_lookup_code,
310 'DEFAULT', nvl(x_transport_type,'DEFAULT'),
311 transport_type_lookup_code)
312 = nvl(x_transport_type,'DEFAULT')
313 AND NVL(ghg_organization_id,nvl(x_facility_id,-1)) = nvl(x_facility_id,-1)
314 AND NVL(item_id,nvl(x_item_id,-1)) = nvl(x_item_id,-1)
315 AND NVL(supplier_id,NVL(x_supplier_id,-1)) = NVL(x_supplier_id,-1)
316 AND NVL(supplier_site_id,NVL(x_supplier_site_id,-1)) = NVL(x_supplier_site_id,-1)
317 AND x_emission_date BETWEEN TRUNC(start_date)
318 AND TRUNC(NVL(end_date,GHG_UTILITIES_PKG.end_date))
319 ORDER BY
320 scope_lookup_code,
321 CASE
322 WHEN item_id IS NOT NULL
323 THEN '1'
324 WHEN supplier_id IS NOT NULL
325 THEN '2'
326 WHEN ghg_organization_id IS NOT NULL
327 THEN '3'
328 ELSE '9'
329 END
330 ||
331 CASE
332 WHEN TRANSPORT_TYPE_LOOKUP_CODE <> 'DEFAULT'
333 THEN '1'
334 ELSE '9'
335 END
336 ||
337 CASE
338 WHEN LOCATION_CODE <> 'ALL'
339 THEN '1'
340 ELSE '9'
341 END,
342 METHOD_LOOKUP_CODE desc
343 )
344 WHERE ROWNUM = 1;
345
346 RETURN v_emission_factor_id;
347
348 EXCEPTION
349 WHEN NO_DATA_FOUND THEN
350 x_out_location := 'ALL'; -- 12565482 : ssurendr
351 RETURN -1;
352
353 WHEN OTHERS THEN
354 x_out_location := 'ALL'; -- 12565482 : ssurendr
355 RETURN -1;
356 END;
357
358
359 BEGIN
360
361 IF x_asset_id is NOT NULL THEN
362
363 SELECT TRANSPORT_TYPE_LOOKUP_CODE
364 INTO v_transport_type
365 FROM GHG_assets
366 WHERE ghg_asset_id = x_asset_id;
367
368 else
369
370 v_transport_type := 'DEFAULT';
371
372 end if;
373
374 if (nvl(x_location,'ALL') <> 'ALL') then -- 12565482 : ssurendr
375
376 v_factor_id:= get_factor_id(x_source_id => x_source_id,
377 x_scope => x_scope,
378 x_location => x_location,
379 x_emission_date => x_emission_date,
380 x_supplier_id => x_supplier_id,
381 x_supplier_site_id => x_supplier_site_id,
382 x_item_id => x_item_id,
383 x_transport_type => v_transport_type,
384 x_facility_id => x_facility_id,
385 x_out_location => v_out_location); -- 12565482 : ssurendr
386
387 end if;
388
389 if (v_factor_id = -1 OR v_out_location = 'ALL') then
390
391 BEGIN
392 select decode(upper(fnd.application_column_name),
393 'REGION_1', hr.region_1,
394 'REGION_2', hr.region_2,
395 'REGION_2', hr.region_3,
396 'COUNTRY', hr.country, --14119516 (sasuren)
397 'ALL') STATE
398 into v_location
399 from hr_locations hr
400 , FND_DESCR_FLEX_COL_USAGE_VL fnd
401 WHERE fnd.APPLICATION_ID =800
402 AND fnd.DESCRIPTIVE_FLEXFIELD_NAME='Address Location'
403 AND fnd.DESCRIPTIVE_FLEX_CONTEXT_CODE = hr.style
404 AND exists (select 1
405 from GHG_organizations fac
406 where fac.ghg_organization_id = x_facility_id
407 and fac.location_id = hr.location_id)
408 AND upper(end_user_column_name) = fnd_profile.value('GHG_FACTOR_BREAK_REGION');
409 EXCEPTION
410 WHEN NO_DATA_FOUND THEN
411 v_location := 'ALL';
412 END;
413
414
415 v_factor_id:= get_factor_id(x_source_id => x_source_id,
416 x_scope => x_scope,
417 x_location => v_location,
418 x_emission_date => x_emission_date,
419 x_supplier_id => x_supplier_id,
420 x_supplier_site_id => x_supplier_site_id,
421 x_item_id => x_item_id,
422 x_transport_type => v_transport_type,
423 x_facility_id => x_facility_id,
424 x_out_location => v_out_location);
425 end if;
426
427
428 RETURN v_factor_id;
429
430 /*
431
432 IF x_item_id is NOT NULL THEN
433 v_factor_id:= get_factor_id(x_source_id => x_source_id,
434 x_scope => x_scope,
435 x_location => x_location,
436 x_emission_date => x_emission_date,
437 x_item_id => x_item_id,
438 x_transport_type => v_transport_type);
439
440 -- if there is no item specific rate, then lookup rate for transport type
441 IF v_factor_id = -1 THEN
442 v_factor_id:= get_factor_id(x_source_id => x_source_id,
443 x_scope => x_scope,
444 x_location => x_location,
445 x_emission_date => x_emission_date,
446 x_transport_type => v_transport_type);
447 END IF;
448 ELSE
449 v_factor_id:= get_factor_id(x_source_id => x_source_id,
450 x_scope => x_scope,
451 x_location => x_location,
452 x_emission_date => x_emission_date,
453 x_transport_type => v_transport_type);
454 END IF;
455 END IF;
456
457 -- Check if ITEM only if we don't have a rate
458 IF v_factor_id = -1 AND x_item_id is NOT NULL THEN
459 v_factor_id:= get_factor_id(x_source_id => x_source_id,
460 x_scope => x_scope,
461 x_location => x_location,
462 x_emission_date => x_emission_date,
463 x_item_id => x_item_id );
464 END IF;
465
466 -- ssurendr: check for all location if location specific not found
467 IF v_factor_id = -1 AND x_item_id is NOT NULL THEN
468 v_factor_id:= get_factor_id(x_source_id => x_source_id,
469 x_scope => x_scope,
470 x_location => 'ALL',
471 x_emission_date => x_emission_date,
472 x_item_id => x_item_id );
473 END IF;
474
475 IF v_factor_id = -1 AND x_supplier_id is NOT NULL THEN
476 v_factor_id:= get_factor_id(x_source_id => x_source_id,
477 x_scope => x_scope,
478 x_location => x_location,
479 x_emission_date => x_emission_date,
480 x_supplier_id => x_supplier_id,
481 x_supplier_site_id => x_supplier_site_id,
482 x_transport_type => v_transport_type );
483 END IF;
484
485 IF v_factor_id = -1 AND x_facility_id is NOT NULL THEN
486 v_factor_id:= get_factor_id(x_source_id => x_source_id,
487 x_scope => x_scope,
488 x_location => x_location,
489 x_emission_date => x_emission_date,
490 x_facility_id => x_facility_id );
491 END IF;
492
493 -- dbms_output.put_line('Before Value : ' ||x_source_id||'-'||x_scope||'-'||x_location||'-'||x_emission_date);
494
495 IF v_factor_id = -1 THEN
496 -- dbms_output.put_line('Before Value : ' ||x_source_id||'-'||x_scope||'-'||x_location||'-'||x_emission_date);
497
498 -- See if there is a Facility Location Specific Rate, otherwise use ALL
499 -- State Value from Location
500
501 BEGIN
502 select decode(upper(fnd.application_column_name),
503 'REGION_1', hr.region_1,
504 'REGION_2', hr.region_2,
505 'REGION_2', hr.region_3,
506 'COUNTRY', hr.country, --14119516 (sasuren)
507 'ALL') STATE
508 into v_location
509 from hr_locations hr
510 , FND_DESCR_FLEX_COL_USAGE_VL fnd
511 WHERE fnd.APPLICATION_ID =800
512 AND fnd.DESCRIPTIVE_FLEXFIELD_NAME='Address Location'
513 AND fnd.DESCRIPTIVE_FLEX_CONTEXT_CODE = hr.style
514 AND exists (select 1
515 from GHG_organizations fac
516 where fac.ghg_organization_id = x_facility_id
517 and fac.location_id = hr.location_id)
518 AND upper(end_user_column_name) = fnd_profile.value('GHG_FACTOR_BREAK_REGION');
519 EXCEPTION
520 WHEN NO_DATA_FOUND THEN
521 v_location := 'ALL';
522 END;
523
524 v_factor_id:= get_factor_id(x_source_id => x_source_id,
525 x_scope => x_scope,
526 x_location => v_location,
527 x_emission_date => x_emission_date);
528
529 -- There are no State Specific rates, get general rate
530 IF v_factor_id = -1 THEN
531 v_factor_id:= get_factor_id(x_source_id => x_source_id,
532 x_scope => x_scope,
533 x_location => x_location,
534 x_emission_date => x_emission_date);
535 END IF;
536
537 -- dbms_output.put_line('Found : '||v_factor_id);
538
539 END IF;
540
541 RETURN v_factor_id;
542 */
543 EXCEPTION
544 WHEN OTHERS THEN
545 RETURN SQLCODE;
546 END;
547
548 FUNCTION are_there_any_rates_location (x_source_id NUMBER,
549 x_scope VARCHAR2,
550 x_location VARCHAR2,
551 x_user_location VARCHAR2,
552 x_start_date DATE,
553 x_end_date DATE,
554 x_supplier_id NUMBER,
555 x_supplier_site_id NUMBER,
556 x_item_id NUMBER,
557 x_asset_id NUMBER,
558 x_facility_id NUMBER
559 ) RETURN BOOLEAN IS
560 v_factor_id NUMBER;
561
562 BEGIN
563 -- make sure there is a rate that will cover the complete range of the transaction
564 FOR days in 0..(trunc(x_end_date)-trunc(x_start_date)) LOOP
565 v_factor_id:=get_factor_user_location(x_source_id,
566 x_scope,
567 x_location,
568 x_user_location,
569 x_start_date+days,
570 x_supplier_id,
571 x_supplier_site_id,
572 x_item_id,
573 x_asset_id,
574 x_facility_id);
575
576 IF v_factor_id = -1 THEN
577 RETURN FALSE;
578 END IF;
579 END LOOP;
580 RETURN TRUE;
581 END;
582
583
584 FUNCTION get_factor_user_location (x_source_id NUMBER,
585 x_scope VARCHAR2,
586 x_location VARCHAR2,
587 x_user_location VARCHAR2,
588 x_emission_date DATE,
589 x_supplier_id NUMBER ,
590 x_supplier_site_id NUMBER ,
591 x_item_id NUMBER ,
592 x_asset_id NUMBER ,
593 x_facility_id NUMBER
594 ) RETURN NUMBER IS
595
596 v_factor_id NUMBER := -1;
597 v_transport_type VARCHAR2(100);
598 v_debug varchar2(4000);
599 v_location varchar2(100);
600 v_out_location varchar2(100) := 'ALL';
601
602 FUNCTION get_factor_id_location
603 (x_source_id NUMBER,
604 x_scope VARCHAR2,
605 x_location VARCHAR2,
606 x_emission_date DATE,
607 x_supplier_id NUMBER DEFAULT NULL ,
608 x_supplier_site_id NUMBER DEFAULT NULL,
609 x_item_id NUMBER DEFAULT NULL,
610 x_transport_type VARCHAR2 DEFAULT 'DEFAULT',
611 x_facility_id NUMBER DEFAULT NULL,
612 x_out_location OUT NOCOPY VARCHAR2 -- 12565482 : ssurendr
613 ) RETURN NUMBER IS
614
615 v_emission_factor_id NUMBER;
616
617 BEGIN
618
619 SELECT source_combination_id,
620 location_code -- 12565482 : ssurendr
621 INTO v_emission_factor_id,
622 x_out_location -- 12565482 : ssurendr
623 FROM
624 (SELECT source_combination_id,
625 location_code -- 12565482 : ssurendr
626 FROM GHG_source_combinations_all
627 WHERE source_id = x_source_id
628 AND scope_lookup_code = decode(x_scope,
629 '-99999', scope_lookup_code,
630 null, scope_lookup_code,
631 x_scope)
632 AND decode(location_code,'ALL',x_location,location_code) = x_location
633
634 AND decode(transport_type_lookup_code,
635 'DEFAULT', nvl(x_transport_type,'DEFAULT'),
636 transport_type_lookup_code)
637 = nvl(x_transport_type,'DEFAULT')
638 AND NVL(ghg_organization_id,nvl(x_facility_id,-1)) = nvl(x_facility_id,-1)
639 AND NVL(item_id,nvl(x_item_id,-1)) = nvl(x_item_id,-1)
640 AND NVL(supplier_id,NVL(x_supplier_id,-1)) = NVL(x_supplier_id,-1)
641 AND NVL(supplier_site_id,NVL(x_supplier_site_id,-1)) = NVL(x_supplier_site_id,-1)
642 AND x_emission_date BETWEEN TRUNC(start_date)
643 AND TRUNC(NVL(end_date,GHG_UTILITIES_PKG.end_date))
644 ORDER BY
645 scope_lookup_code,
646 CASE
647 WHEN item_id IS NOT NULL
648 THEN '1'
649 WHEN supplier_id IS NOT NULL
650 THEN '2'
651 WHEN ghg_organization_id IS NOT NULL
652 THEN '3'
653 ELSE '9'
654 END
655 ||
656 CASE
657 WHEN TRANSPORT_TYPE_LOOKUP_CODE <> 'DEFAULT'
658 THEN '1'
659 ELSE '9'
660 END
661 ||
662 CASE
663 WHEN LOCATION_CODE <> 'ALL'
664 THEN '1'
665 ELSE '9'
666 END,
667 METHOD_LOOKUP_CODE desc
668 )
669 WHERE ROWNUM = 1;
670
671 RETURN v_emission_factor_id;
672
673 EXCEPTION
674 WHEN NO_DATA_FOUND THEN
675 x_out_location := 'ALL'; -- 12565482 : ssurendr
676 RETURN -1;
677
678 WHEN OTHERS THEN
679 x_out_location := 'ALL'; -- 12565482 : ssurendr
680 RETURN -1;
681 END;
682
683
684 BEGIN
685
686 IF x_asset_id is NOT NULL THEN
687
688 SELECT TRANSPORT_TYPE_LOOKUP_CODE
689 INTO v_transport_type
690 FROM GHG_assets
691 WHERE ghg_asset_id = x_asset_id;
692
693 else
694
695 v_transport_type := 'DEFAULT';
696
697 end if;
698
699 if (nvl(x_user_location,'ALL') <> 'ALL') then -- 12565482 : ssurendr
700
701 v_factor_id:= get_factor_id_location(x_source_id => x_source_id,
702 x_scope => x_scope,
703 x_location => x_user_location,
704 x_emission_date => x_emission_date,
705 x_supplier_id => x_supplier_id,
706 x_supplier_site_id => x_supplier_site_id,
707 x_item_id => x_item_id,
708 x_transport_type => v_transport_type,
709 x_facility_id => x_facility_id,
710 x_out_location => v_out_location); -- 12565482 : ssurendr
711
712 end if;
713
714 if ((v_factor_id = -1 OR v_out_location = 'ALL') AND (nvl(x_location,'ALL') <> 'ALL')) then
715
716 BEGIN
717
718 v_factor_id:= get_factor_id_location(x_source_id => x_source_id,
719 x_scope => x_scope,
720 x_location => x_location,
721 x_emission_date => x_emission_date,
722 x_supplier_id => x_supplier_id,
723 x_supplier_site_id => x_supplier_site_id,
724 x_item_id => x_item_id,
725 x_transport_type => v_transport_type,
726 x_facility_id => x_facility_id,
727 x_out_location => v_out_location);
728
729 END;
730 END IF;
731
732 if (v_factor_id = -1 OR v_out_location = 'ALL') then
733
734 BEGIN
735 select decode(upper(fnd.application_column_name),
736 'REGION_1', hr.region_1,
737 'REGION_2', hr.region_2,
738 'REGION_2', hr.region_3,
739 'COUNTRY', hr.country, --14119516 (sasuren)
740 'ALL') STATE
741 into v_location
742 from hr_locations hr
743 , FND_DESCR_FLEX_COL_USAGE_VL fnd
744 WHERE fnd.APPLICATION_ID =800
745 AND fnd.DESCRIPTIVE_FLEXFIELD_NAME='Address Location'
746 AND fnd.DESCRIPTIVE_FLEX_CONTEXT_CODE = hr.style
747 AND exists (select 1
748 from GHG_organizations fac
749 where fac.ghg_organization_id = x_facility_id
750 and fac.location_id = hr.location_id)
751 AND upper(end_user_column_name) = fnd_profile.value('GHG_FACTOR_BREAK_REGION');
752 EXCEPTION
753 WHEN NO_DATA_FOUND THEN
754 v_location := 'ALL';
755 END;
756
757 v_factor_id:= get_factor_id_location(x_source_id => x_source_id,
758 x_scope => x_scope,
759 x_location => v_location,
760 x_emission_date => x_emission_date,
761 x_supplier_id => x_supplier_id,
762 x_supplier_site_id => x_supplier_site_id,
763 x_item_id => x_item_id,
764 x_transport_type => v_transport_type,
765 x_facility_id => x_facility_id,
766 x_out_location => v_out_location);
767 END IF;
768
769 RETURN v_factor_id;
770
771 EXCEPTION
772 WHEN OTHERS THEN
773 RETURN SQLCODE;
774 END;
775
776 procedure create_ledger_entry ( x_emission_id NUMBER,
777 x_factor_id NUMBER,
778 x_quantity NUMBER,
779 x_emission_date DATE,
780 x_uom_class VARCHAR2,
781 x_org_id NUMBER) IS
782
783 CURSOR per_gas IS
784 select gas_type_lookup_code
785 , factor_value
786 , factor_type
787 from GHG_factors_all
788 where source_combination_id = x_factor_id
789 and factor_type ='E';
790
791 vl_formula_id number;
792 vl_ec number;
793 vl_ef number;
794 vl_co2e number;
795 vl_formula_contents clob;
796 vl_formula_contents_tmp clob;
797
798
799
800 BEGIN
801
802 -- dbms_output.put_line('In create_ledger_entry:start:factor_id:'||x_factor_id||':'||x_uom_class);
803
804 -- Get Formula Id
805 -- if the x_uom_class is as per the value set in the profile option 'GHG_UOM_CLASS', then we use the secondary energy formula only
806
807 select decode(x_uom_class, fnd_profile.value('GHG_UOM_CLASS'), nvl(ef.secondary_formula_id,ef.formula_id), ef.formula_id)
808 into vl_formula_id
809 from ghg_sources s
810 , ghg_source_combinations_all f
811 , ghg_emission_formulas ef
812 where s.source_id = f.source_id
813 and f.source_combination_id = x_factor_id
814 and s.formula_id = ef.formula_id;
815
816 -- dbms_output.put_line('In create_ledger_entry:get formula'||':'||x_emission_id);
817
818 select 'select '||formula_content||' from dual'
819 into vl_formula_contents
820 from ghg_emission_formulas
821 where formula_id = vl_formula_id;
822
823 -- Get the Value of EC
824 BEGIN
825 select FACTOR_VALUE
826 into vl_ec
827 from GHG_FACTORS_ALL xx
828 where source_combination_id = x_factor_id
829 and factor_type ='N';
830 EXCEPTION
831 when no_data_found then
832 vl_ec := 0;
833 END;
834 -- Substritue Variables
835 -- Q
836 vl_formula_contents := replace(vl_formula_contents, 'Q', fnd_number.NUMBER_TO_CANONICAL(x_quantity));
837 -- EC
838 vl_formula_contents := replace(vl_formula_contents, 'EC', fnd_number.NUMBER_TO_CANONICAL(vl_ec));
839
840 -- Insert energy row
841 insert into GHG_transaction_details_all (transaction_id,transaction_date,transaction_type,transaction_component_type,transaction_value,source_combination_id,org_id,created_by,creation_date,last_updated_by,last_update_date,last_update_login)
842 values(
843 x_emission_id,
844 x_emission_date,
845 'N',
846 'ENERGY',
847 x_quantity*decode(x_uom_class, fnd_profile.value('GHG_UOM_CLASS'), 1, vl_ec) ,
848 x_factor_id,
849 x_org_id,
850 fnd_profile.value('USER_ID'), sysdate, fnd_profile.value('USER_ID'), sysdate, null
851 );
852
853 -- For all Gas Types
854 for gas_type in per_gas loop
855
856 vl_formula_contents_tmp := replace(vl_formula_contents, 'EF', fnd_number.NUMBER_TO_CANONICAL(gas_type.factor_value));
857
858 EXECUTE IMMEDIATE vl_formula_contents_tmp INTO vl_co2e;
859
860 insert into GHG_transaction_details_all (transaction_id,transaction_date,transaction_type,transaction_component_type,transaction_value,source_combination_id,org_id,created_by,creation_date,last_updated_by,last_update_date,last_update_login)
861 values(
862 x_emission_id,
863 x_emission_date,
864 gas_type.factor_type,
865 gas_type.gas_type_lookup_code,
866 vl_co2e,
867 x_factor_id,
868 x_org_id,
869 fnd_profile.value('USER_ID'), sysdate, fnd_profile.value('USER_ID'), sysdate, null
870 );
871 end loop;
872 END;
873
874 END GHG_emission_rates_pkg;