[Home] [Help]
PACKAGE BODY: APPS.GHG_TRANSACTIONS_API_PKG
Source
1 PACKAGE BODY GHG_TRANSACTIONS_API_PKG AS
2 /*$Header: ghgtxnab.pls 120.3.12020000.4 2013/05/21 06:49:15 neechand ship $ */
3
4 raise_error EXCEPTION; --Bug 13507113 (sasuren): Moved exception to package body context
5 /* lvenkatr 12/6/2011 - Added validations for batch sub type, supplier and
6 * supplier site as part of fix for ER 12919641. This procedure will be called
7 * in create_transaction procedure. */
8 PROCEDURE validate_transaction_inputs(
9 p_batch_type VARCHAR2 DEFAULT NULL,
10 p_batch_sub_type VARCHAR2 DEFAULT NULL,
11 p_item_number VARCHAR2 DEFAULT NULL,
12 p_m_criteria VARCHAR2 DEFAULT NULL,
13 p_emission_scope VARCHAR DEFAULT NULL,
14 p_emission_usage NUMBER DEFAULT NULL,
15 p_emission_from_date DATE DEFAULT NULL,
16 p_emission_to_date DATE DEFAULT NULL,
17 p_emission_location VARCHAR DEFAULT NULL,
18 p_supplier_id VARCHAR2 DEFAULT NULL,
19 p_supplier_site_id VARCHAR2 DEFAULT NULL) AS
20
21 v_row_count NUMBER := 0;
22 --raise_error EXCEPTION; --Bug 13507113 (sasuren): Moved exception to package body context
23 v_msg_code varchar2(100); -- Re-used from (Bug 12434320 - ssurendr)
24 v_debug varchar2(1000);
25
26 procedure x_debug (x_text varchar2) is
27 begin
28 v_debug := x_text;
29 -- insert into sailen
30 -- select sailen_s.nextval, x_text from dual;
31 -- fnd_message.debug(x_text);
32 -- dbms_output.put_line(x_text);
33 null;
34 end;
35
36 BEGIN
37
38 x_debug('Inside validate_transaction_inputs');
39 -- Internal Invoices Batches Cannot be entered through API.
40 x_debug('Check Internal Invoices Batches Cannot be entered through API.');
41 SELECT count(*)
42 INTO v_row_count
43 FROM fnd_lookup_values_vl
44 WHERE upper(lookup_type) = 'GHG_INTERNAL_TXN_TYPE'
45 AND TRUNC(SYSDATE) BETWEEN start_date_active
46 AND NVL(end_date_active,GHG_utilities_pkg.end_date)
47 AND lookup_code = p_batch_type;
48
49 IF v_row_count > 0 THEN
50 FND_MESSAGE.SET_NAME('GHG', 'GHG_INTERNAL_BATCH_TYPE_USED');
51 RAISE raise_error;
52 ELSE
53 v_row_count := 0;
54 END IF;
55
56
57 -- Check Input Batch SubType
58 x_debug('Check Input Batch SubType');
59 IF p_batch_sub_type IS NOT NULL THEN
60 SELECT count(*)
61 INTO v_row_count
62 FROM fnd_lookup_values_vl
63 WHERE upper(lookup_type) = 'GHG_TXN_SUB_TYPE'
64 AND TRUNC(SYSDATE) BETWEEN start_date_active
65 AND NVL(end_date_active,GHG_utilities_pkg.end_date)
66 AND lookup_code = p_batch_sub_type;
67
68 IF v_row_count = 0 THEN
69 FND_MESSAGE.SET_NAME('GHG', 'GHG_SUB_BATCH_UNKNOWN');
70 RAISE raise_error;
71 ELSE
72 v_row_count := 0;
73 END IF;
74 END IF;
75
76 -- Check if item is null for ITEM batch type
77 x_debug('Check if item is null for ITEM batch type');
78 IF p_batch_type = 'ITEM' THEN
79 x_debug('item ' || p_item_number);
80 IF p_item_number is NULL THEN
81 x_debug('item is null for ITEM batch type');
82 FND_MESSAGE.SET_NAME('GHG', 'GHG_INVALID_ITEM');
83 RAISE raise_error;
84 END IF;
85
86 END IF;
87
88 x_debug('Check for Supplier and Supplier Site for External Invoice batch type');
89 IF p_batch_type = 'EXTERNAL INVOICE' THEN
90 -- Check Input Supplier ID
91 x_debug('Check Input Supplier ID');
92 SELECT COUNT(*)
93 INTO v_row_count
94 FROM po_vendors pv
95 WHERE pv.enabled_flag = 'Y'
96 AND pv.vendor_id = nvl(p_supplier_id, -1)
97 AND EXISTS (SELECT 1
98 FROM po_vendor_sites
99 WHERE vendor_id = pv.vendor_id
100 AND TRUNC(NVL(inactive_date, sysdate + 1)) > TRUNC(sysdate)
101 AND pay_site_flag = 'Y');
102
103 IF v_row_count = 0 THEN
104 FND_MESSAGE.SET_NAME('GHG', 'GHG_INVALID_SUPPLIER_ID');
105 RAISE raise_error;
106 ELSE
107 v_row_count := 0;
108 END IF;
109
110 -- Check Input Supplier Site ID
111 x_debug('Check Input Supplier Site ID');
112 SELECT COUNT(*)
113 INTO v_row_count
114 FROM po_vendor_sites
115 WHERE vendor_id = nvl(p_supplier_id, -1)
116 AND vendor_site_id = nvl(p_supplier_site_id, -1)
117 AND TRUNC(NVL(inactive_date, sysdate + 1)) > TRUNC(sysdate)
118 AND pay_site_flag = 'Y';
119
120 IF v_row_count = 0 THEN
121 FND_MESSAGE.SET_NAME('GHG', 'GHG_INVALID_SUPPLIER_SITE_ID');
122 RAISE raise_error;
123 ELSE
124 v_row_count := 0;
125 END IF;
126
127 END IF;
128
129 -- Check Emission Scope
130 x_debug(' Check Emission Scope');
131 SELECT count(*)
132 INTO v_row_count
133 FROM fnd_lookup_values_vl
134 WHERE upper(lookup_type) = 'GHG_EMISSION_SCOPE'
135 AND TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active,GHG_utilities_pkg.end_date)
136 AND lookup_code = nvl(p_emission_scope, 'NA');
137
138 IF v_row_count = 0 THEN
139 FND_MESSAGE.SET_NAME('GHG','GHG_SCOPE_UNKNOWN');
140 RAISE raise_error;
141 ELSE
142 v_row_count := 0;
143 END IF;
144
145 -- Check Measurement Criteria
146 x_debug('Check Measurement Criteria');
147 SELECT count(*)
148 INTO v_row_count
149 FROM fnd_lookup_values_vl
150 WHERE upper(lookup_type) = 'GHG_MEASUREMENT_CRITERIA'
151 AND TRUNC(SYSDATE) BETWEEN start_date_active
152 AND NVL(end_date_active,GHG_utilities_pkg.end_date)
153 AND lookup_code = nvl(p_m_criteria,'-1');
154
155 IF v_row_count = 0 THEN
156 FND_MESSAGE.SET_NAME('GHG', 'GHG_INVALID_MEASURE_CRITERIA');
157 RAISE raise_error;
158 ELSE
159 v_row_count := 0;
160 END IF;
161
162 -- Check Emission Usage
163 x_debug('Check Emission Usage');
164 IF p_emission_usage is NULL THEN
165 FND_MESSAGE.SET_NAME('GHG', 'GHG_NULL_USAGE_QUANTITY');
166 RAISE raise_error;
167 END IF;
168
169 /*******************************************************************
170 * Bug 13511580 (lvenkatr 12/16/2011) : This validation was added assuming the check for
171 * creating emissions for the same dates is done through the UI (Manual Entry)
172 * as well. But the UI allows to create emissions for the same dates (multiple
173 * transactions) within one batch as well as across different batches. This
174 * validation is being commented now to be consistent with UI behavior but
175 * need to check with PM on the expected behavior for such scenarios.
176 *
177 -- Check if transactions exist for the Emission Dates
178 x_debug('Check if transactions exist for the Emission Dates');
179 SELECT COUNT(*)
180 INTO v_row_count
181 FROM GHG_TRANSACTIONS_V txn
182 WHERE (p_emission_from_date between txn.transaction_date_from
183 and txn.transaction_date_to )
184 OR (p_emission_to_date between txn.transaction_date_from
185 and txn.transaction_date_to )
186 OR (txn.transaction_date_from between p_emission_from_date
187 and p_emission_to_date )
188 OR (txn.transaction_date_to between p_emission_from_date
189 and p_emission_to_date );
190
191 IF v_row_count > 0 THEN
192 FND_MESSAGE.SET_NAME('GHG', 'GHG_TRANSACTIONS_EXIST');
193 RAISE raise_error;
194 ELSE
195 v_row_count := 0;
196 END IF;
197
198 ******************************************************************/
199
200 IF p_emission_location is NOT NULL THEN
201 x_debug('Validating emission location');
202 SELECT count(*)
203 INTO v_row_count
204 FROM fnd_lookup_values_vl
205 WHERE upper(lookup_type) = 'GHG_LOCATION'
206 AND TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active,GHG_utilities_pkg.end_date)
207 AND lookup_code = p_emission_location;
208
209 IF v_row_count = 0 THEN
210 FND_MESSAGE.SET_NAME('GHG','GHG_LOCATION_UNKNOWN');
211 RAISE raise_error;
212 ELSE
213 v_row_count := 0;
214 END IF;
215 END IF;
216
217 x_debug('After all validations ');
218 /* Bug 13507113 (sasuren) : removed exception block
219 EXCEPTION
220 WHEN raise_error THEN
221
222 -- Re-used from Bug 12434320
223 v_msg_code := FND_MESSAGE.GET;
224
225 -- dbms_output.put_line ('Error is ' || v_msg_code);
226 if v_msg_code is not null then
227 v_msg_code := substr(v_msg_code,5);
228 v_msg_code := substr(v_msg_code,1,length(v_msg_code)-1);
229 else
230 v_msg_code := 'GHG_VALIDATE_TXN_INPUT_ERROR';
231 end if;
232
233 FND_MESSAGE.SET_NAME('GHG',v_msg_code);
234 RAISE_APPLICATION_ERROR(-20002, v_msg_code || '~');
235
236 when others then
237
238 FND_MESSAGE.SET_NAME('GHG','GHG_VALIDATE_TXN_INPUT_ERROR');
239 RAISE_APPLICATION_ERROR(-20002, 'GHG_VALIDATE_TXN_INPUT_ERROR');
240 */
241
242 END validate_transaction_inputs;
243
244 /* End of Procedure validate_transaction_inputs */
245
246
247 PROCEDURE create_transaction (p_org_id NUMBER DEFAULT FND_PROFILE.value('ORG_ID'),
248 p_batch_type VARCHAR2 DEFAULT NULL,
249 p_batch_sub_type VARCHAR2 DEFAULT NULL,
250 p_batch_number VARCHAR2 DEFAULT NULL,
251 p_batch_description VARCHAR2 DEFAULT NULL,
252 p_facility VARCHAR2 DEFAULT NULL,
253 p_emission_source_name VARCHAR2 DEFAULT NULL ,
254 p_emission_scope VARCHAR DEFAULT NULL,
255 p_emission_location VARCHAR2 DEFAULT NULL ,
256 p_emission_usage NUMBER DEFAULT NULL,
257 p_emission_uom VARCHAR2 DEFAULT NULL,
258 p_emission_from_date DATE DEFAULT NULL,
259 p_emission_to_date DATE DEFAULT NULL,
260 p_emission_description VARCHAR2 DEFAULT NULL,
261 p_supplier VARCHAR2 DEFAULT NULL,
262 p_supplier_site_name VARCHAR2 DEFAULT NULL,
263 p_item_number VARCHAR2 DEFAULT NULL,
264 p_ghgas_asset VARCHAR2 DEFAULT NULL,
265 p_invoice_id NUMBER DEFAULT NULL,
266 p_invoice_line_num NUMBER DEFAULT NULL,
267 p_invoice_dist_num NUMBER DEFAULT NULL,
268 p_m_criteria VARCHAR2 DEFAULT NULL,
269 p_commit_flag VARCHAR2 DEFAULT 'Y',
270 p_manual_entry_flag VARCHAR2 DEFAULT 'N') AS
271
272
273 --raise_error EXCEPTION; --Bug 13507113 (sasuren): Moved exception to package body context
274
275 v_emission_batch_id number ;
276 v_batch_status varchar2(10);
277 v_batch_rowid varchar2(1000);
278 v_debug varchar2(1000);
279 v_facility_id number;
280 v_emission_source_id number;
281 v_emission_id number;
282 v_uom_conversion number;
283 v_set_of_books_id number;
284 v_max_line_no number;
285 v_status varchar2(5);
286 v_rates_exist_manual_loc varchar2(5);
287 v_message varchar2(1000);
288 v_check number := 0;
289 v_batch_type varchar2(100);
290 v_supplier_id number;
291 v_supplier_site_id number;
292 v_item_id number ;
293 v_asset_id number ;
294 v_emission_source_name varchar2(150);
295 v_emission_scope number;
296 v_emission_location varchar2(150);
297
298 v_msg_code varchar2(100); -- Bug 12434320 - ssurendr
299
300 procedure x_debug (x_text varchar2) is
301 begin
302 v_debug := x_text;
303 -- insert into sailen
304 -- select sailen_s.nextval, x_text from dual;
305 -- fnd_message.debug(x_text);
306 -- dbms_output.put_line(x_text);
307 null;
308 end;
309
310
311 BEGIN
312
313 -- Set Org Context
314 -----------------------------------------------------------------------------------------
315 mo_global.set_policy_context_server(p_access_mode => 'S',p_org_id => p_org_id);
316
317 -- Check Inputs to make sure that the data is correct
318 -----------------------------------------------------------------------------------------
319
320 x_debug('In Org_id :'||p_org_id);
321
322 -- Check Inputs Batch Type
323 IF p_batch_type IS NULL THEN
324 FND_MESSAGE.SET_NAME('GHG', 'GHG_BATCH_TYPE_MISSING');
325 RAISE raise_error;
326 ELSE
327 SELECT count(*)
328 INTO v_check
329 FROM fnd_lookup_values_vl
330 WHERE upper(lookup_type) = 'GHG_TXN_TYPE'
331 AND TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active,GHG_utilities_pkg.end_date)
332 AND lookup_code = p_batch_type;
333
334 IF v_check = 0 THEN
335 -- This is to cater for the fact that teh API has been called from PO
336 IF p_batch_type = 'INT_INVOICE' THEN
337 NULL;
338 ELSE
339 FND_MESSAGE.SET_NAME('GHG', 'GHG_BATCH_UNKNOWN');
340 RAISE raise_error;
341 END IF;
342 ELSE
343 v_check := 0;
344 END IF;
345 END IF;
346
347 --lvenkatr 12/6/2011 - Validate Batch Sub Type and for batch type EXTERNAL INVOICE
348 -- validate Supplier ID and Supplier Site ID for bug12919641
349 -- BEGIN
350 IF p_manual_entry_flag = 'Y' THEN
351 x_debug('Call validations if manual entry is Y');
352 validate_transaction_inputs( p_batch_type,
353 p_batch_sub_type,
354 p_item_number,
355 p_m_criteria,
356 p_emission_scope,
357 p_emission_usage,
358 p_emission_from_date,
359 p_emission_to_date,
360 p_emission_location,
361 p_supplier,
362 p_supplier_site_name);
363 END IF;
364
365 /* EXCEPTION
366 WHEN OTHERS THEN
367 -- Re-used from Bug 12434320
368 v_msg_code := FND_MESSAGE.GET;
369
370 if v_msg_code is not null then
371 v_msg_code := substr(v_msg_code,5);
372 v_msg_code := substr(v_msg_code,1,length(v_msg_code)-1);
373 else
374 v_msg_code := 'GHG_VALIDATE_TXN_INPUT_ERROR';
375 end if;
376
377 FND_MESSAGE.SET_NAME('GHG',v_msg_code);
378
379 RAISE raise_error;
380 END; */
381
382 -- Check Inputs Batch Number
383 if P_BATCH_NUMBER is null then
384 FND_MESSAGE.SET_NAME('GHG', 'GHG_BATCH_NUM_MISSING');
385 RAISE raise_error;
386 ELSE
387 SELECT set_of_books_id
388 INTO v_set_of_books_id
389 FROM ap_system_parameters;
390
391 --- Check Batch
392 BEGIN
393 SELECT transaction_batch_id,
394 status,
395 batch_type
396 INTO v_emission_batch_id
397 , v_batch_status
398 , v_batch_type
399 FROM GHG_TRANSACTION_BATCHES
400 WHERE transaction_batch_number = p_batch_number;
401
402 IF v_batch_status = 'A' THEN
403 IF v_batch_type = 'INT_INVOICE' THEN
404 NULL;
405 ELSE
406 FND_MESSAGE.SET_NAME('GHG', 'GHG_BATCH_APPROVED');
407 RAISE raise_error;
408 END IF;
409 END IF;
410
411 IF v_batch_type <> p_batch_type THEN
412 FND_MESSAGE.SET_NAME('GHG','GHG_BATCH_TYPE_DIFFER');
413 RAISE raise_error;
414 END IF;
415
416 EXCEPTION
417 WHEN NO_DATA_FOUND THEN
418 NULL;
419 END;
420 END IF;
421
422 x_debug('Pre Item with :'||p_item_number);
423 -- Check the an Item has been entered. If so, then lookup the default source/scope/location for item
424 IF p_item_number is NOT NULL THEN
425 BEGIN
426 /* ssurendr - changed query to get source details
427 SELECT INVENTORY_ITEM_ID
428 , SOURCE_ID
429 , source_name
430 , LOCATION_CODE
431 , EMISSION_SCOPE_LOOKUP_CODE
432 INTO v_item_id
433 , v_emission_source_id
434 , v_emission_source_name
435 , v_emission_location
436 , v_emission_scope
437 FROM GHG_ITEM_FACTORS_V
438 WHERE TRANSPORT_TYPE_LOOKUP_CODE = 'DEFAULT'
439 AND INVENTORY_ITEM = p_item_number;
440 */
441 v_emission_scope := '-99999';
442
443 SELECT distinct gsi.INVENTORY_ITEM_ID
444 , gsi.SOURCE_ID
445 , s.source_name
446 INTO v_item_id
447 , v_emission_source_id
448 , v_emission_source_name
449 FROM GHG_SUPPLIER_ITEMS gsi,
450 mtl_system_items_kfv msi,
451 ghg_sources s
452 WHERE msi.CONCATENATED_SEGMENTS = p_item_number
453 AND msi.INVENTORY_ITEM_ID = gsi.INVENTORY_ITEM_ID
454 and gsi.SOURCE_ID = s.SOURCE_ID;
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 FND_MESSAGE.SET_NAME('GHG','GHG_ITEM_NO_DEFAULT');
458 RAISE raise_error;
459 END;
460 END IF;
461
462 x_debug('After Item :'||v_item_id||':'||v_emission_source_id||':'||v_emission_source_name);
463 -- Check GHG Asset Code
464 IF p_ghgas_asset IS NOT NULL THEN
465 BEGIN
466 SELECT ghg_asset_id
467 INTO v_asset_id
468 FROM GHG_ASSETS_V
469 where asset_number = p_ghgas_asset
470 and trunc(p_emission_from_date) >= start_date
471 and p_emission_to_date <= nvl(end_date,GHG_utilities_pkg.end_date);
472
473 EXCEPTION
474 WHEN NO_DATA_FOUND THEN
475 v_message := 'GHG Asset not defined';
476 RAISE raise_error;
477 END;
478 END IF;
479
480 -- Check Source
481 IF nvl(p_emission_source_name, v_emission_source_name) IS NULL THEN
482 v_message := 'Emission Source Is Missing';
483 RAISE raise_error;
484 ELSE
485 BEGIN
486 SELECT source_id
487 into v_emission_source_id
488 from GHG_SOURCES
489 where source_name = nvl(p_emission_source_name, v_emission_source_name);
490 EXCEPTION
491 when no_data_found then
492 FND_MESSAGE.SET_NAME('GHG','GHG_SOURCE_UNKNOWN');
493 RAISE raise_error;
494 END;
495 END IF;
496
497 /* ssurendr : removed validation for scope
498 -- Check Emission Scope
499 IF nvl(p_emission_scope,v_emission_scope) IS NULL THEN
500 v_message := 'Emission Scope Is Missing';
501 RAISE raise_error;
502 ELSE
503 SELECT count(*)
504 INTO v_check
505 FROM fnd_lookup_values_vl
506 WHERE upper(lookup_type) = 'GHG_EMISSION_SCOPE'
507 AND TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active,GHG_utilities_pkg.end_date)
508 AND lookup_code = nvl(p_emission_scope,v_emission_scope);
509
510 IF v_check = 0 THEN
511 FND_MESSAGE.SET_NAME('GHG','GHG_SCOPE_UNKNOWN');
512 RAISE raise_error;
513 ELSE
514 v_check := 0;
515 END IF;
516 END IF;
517 */
518
519 -- Check Facility
520 IF p_facility IS NULL THEN
521 FND_MESSAGE.SET_NAME('GHG','GHG_FACILITY_MISSING');
522 RAISE raise_error;
523 ELSE
524 BEGIN
525 SELECT ghg_organization_id
526 INTO v_facility_id
527 FROM GHG_ORGANIZATIONS_V
528 WHERE ghg_organization_code = p_facility
529 AND allow_transactions = 'Y';
530
531 EXCEPTION
532 WHEN NO_DATA_FOUND THEN
533 FND_MESSAGE.SET_NAME('GHG','GHG_FACILITY_UNKNOWN');
534 RAISE raise_error;
535 END;
536
537 BEGIN
538 SELECT ghg_organization_id
539 INTO v_facility_id
540 FROM GHG_ORGANIZATIONS_V
541 WHERE ghg_organization_code = p_facility
542 AND p_emission_from_date BETWEEN start_date AND nvl(end_date, GHG_utilities_pkg.end_date)
543 AND p_emission_to_date BETWEEN start_date AND nvl(end_date, GHG_utilities_pkg.end_date)
544 AND allow_transactions = 'Y';
545
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN
548 FND_MESSAGE.SET_NAME('GHG','GHG_FACILITY_INVALID');
549 RAISE raise_error;
550 END;
551
552 END IF;
553
554 x_debug('After facility check');
555 -- ssurendr: Added code to get location from facility
556 BEGIN
557 select decode(upper(fnd.application_column_name),
558 'REGION_1', hr.region_1,
559 'REGION_2', hr.region_2,
560 'REGION_2', hr.region_3,
561 'COUNTRY', hr.country, --14119516 (sasuren)
562 'ALL') STATE
563 into v_emission_location
564 from hr_locations hr
565 , FND_DESCR_FLEX_COL_USAGE_VL fnd
566 WHERE fnd.APPLICATION_ID =800
567 AND fnd.DESCRIPTIVE_FLEXFIELD_NAME='Address Location'
568 AND fnd.DESCRIPTIVE_FLEX_CONTEXT_CODE = hr.style
569 AND exists (select 1
570 from GHG_organizations fac
571 where fac.ghg_organization_id = v_facility_id
572 and fac.location_id = hr.location_id)
573 AND upper(end_user_column_name) = fnd_profile.value('GHG_FACTOR_BREAK_REGION');
574 EXCEPTION
575 WHEN NO_DATA_FOUND THEN
576 v_emission_location := 'ALL';
577 END;
578
579 -- Check Emission Location
580 --IF nvl(p_emission_location,v_emission_location) IS NULL THEN
581 --Bug 12546495: ssurendr. Use location from organization
582 IF v_emission_location IS NULL THEN
583 FND_MESSAGE.SET_NAME('GHG','GHG_LOCATION_MISSING');
584 RAISE raise_error;
585 ELSE
586 SELECT count(*)
587 INTO v_check
588 FROM fnd_lookup_values_vl
589 WHERE upper(lookup_type) = 'GHG_LOCATION'
590 AND TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active,GHG_utilities_pkg.end_date)
591 AND lookup_code = v_emission_location;
592 --AND lookup_code = nvl(p_emission_location,v_emission_location);
593 --Bug 12546495: ssurendr. Use location from organization
594
595 IF v_check = 0 THEN
596 FND_MESSAGE.SET_NAME('GHG','GHG_LOCATION_UNKNOWN');
597 RAISE raise_error;
598 ELSE
599 v_check := 0;
600 END IF;
601 END IF;
602
603 -- Check Emission UOM
604 IF p_emission_uom IS NULL THEN
605 FND_MESSAGE.SET_NAME('GHG','GHG_UOM_MISSING');
606 RAISE raise_error;
607 ELSE
608 SELECT count(*)
609 INTO v_check
610 FROM GHG_SOURCES xnes,
611 mtl_units_of_measure_tl mum1,
612 mtl_units_of_measure_tl mum2
613 WHERE mum1.unit_of_measure = xnes.unit_of_measure
614 AND mum2.uom_class = mum1.uom_class
615 AND NVL(mum2.disable_date, sysdate + 1) > sysdate
616 AND source_id = v_emission_source_id
617 AND mum2.unit_of_measure = p_emission_uom;
618
619 IF v_check = 0 THEN
620 FND_MESSAGE.SET_NAME('GHG','GHG_UOM_UNKNOWN');
621 RAISE raise_error;
622 ELSE
623 v_check := 0;
624 END IF;
625 END IF;
626
627 -- Check Emission Dates
628 IF p_emission_from_date IS NULL OR p_emission_to_date IS NULL THEN
629 FND_MESSAGE.SET_NAME('GHG','GHG_DATE_MISSING');
630 RAISE raise_error;
631 ELSE
632 IF p_emission_from_date > p_emission_to_date THEN
633 FND_MESSAGE.SET_NAME('GHG','GHG_DATE_INVALID');
634 RAISE raise_error;
635 END IF;
636
637 END IF;
638
639 -- x_debug('Im Hereeee:'||p_batch_type);
640
641 -- Check Batch Type Specific Data
642 -----------------------------------------------------------------------------------------
643 CASE p_batch_type
644 WHEN 'ITEM' THEN
645 BEGIN
646 v_supplier_id := NULL;
647 v_supplier_site_id := NULL;
648 END ;
649 WHEN 'EXTERNAL INVOICE' THEN
650 BEGIN
651 v_item_id := NULL;
652 v_asset_id := NULL;
653 END ;
654 WHEN 'INT_INVOICE' THEN
655 BEGIN
656 v_supplier_id:= p_supplier;
657 v_supplier_site_id := p_supplier_site_name;
658 -- v_item_id := p_item_number;
659 END;
660 ELSE
661 BEGIN
662 -- For all others ignore supplier/item/Asset
663 v_supplier_id := NULL;
664 v_supplier_site_id := NULL;
665 v_item_id := NULL;
666 v_asset_id := NULL;
667 END ;
668
669 END CASE;
670
671 v_rates_exist_manual_loc := 'N';
672
673 x_debug('Before rate check');
674 -- Check to see if there are any valid rates
675 -----------------------------------------------------------------------------------------
676 IF p_manual_entry_flag = 'Y' AND p_emission_location IS NOT NULL THEN
677 IF p_emission_location = 'ALL' THEN
678 NULL;
679 ELSE
680 IF GHG_emission_rates_pkg.are_there_any_rates_location(v_emission_source_id,
681 nvl(p_emission_scope,v_emission_scope),
682 v_emission_location,
683 p_emission_location,
684 --nvl(p_emission_location,v_emission_location),
685 --Bug 12546495: ssurendr. Use location from organization
686 p_emission_from_date,
687 p_emission_to_date,
688 v_supplier_id ,
689 v_supplier_site_id ,
690 v_item_id ,
691 v_asset_id ,
692 v_facility_id) THEN
693 v_rates_exist_manual_loc := 'Y';
694 ELSE
695 FND_MESSAGE.SET_NAME('GHG','GHG_RATE_UNAVAILABLE');
696 RAISE raise_error;
697 END IF;
698 END IF;
699 ELSE
700 IF GHG_emission_rates_pkg.are_there_any_rates (v_emission_source_id,
701 nvl(p_emission_scope,v_emission_scope),
702 v_emission_location,
703 --nvl(p_emission_location,v_emission_location),
704 --Bug 12546495: ssurendr. Use location from organization
705 p_emission_from_date,
706 p_emission_to_date,
707 v_supplier_id ,
708 v_supplier_site_id ,
709 v_item_id ,
710 v_asset_id ,
711 v_facility_id) THEN
712 NULL;
713 ELSE
714 FND_MESSAGE.SET_NAME('GHG','GHG_RATE_UNAVAILABLE');
715 RAISE raise_error;
716 END IF;
717 END IF;
718
719 x_debug('After rate check');
720
721 -- Get Key Data
722 -----------------------------------------------------------------------------------------
723 SELECT GHG_TRANSACTIONS_S.NEXTVAL
724 into v_emission_id
725 FROM dual;
726
727 v_uom_conversion := GHG_UTILITIES_PKG.get_conversion(p_emission_uom, v_emission_source_id, 'GENERAL','API');
728
729 SELECT NVL(MAX(transaction_line_number),0)+1
730 INTO v_max_line_no
731 FROM GHG_TRANSACTIONS
732 WHERE transaction_batch_id = v_emission_batch_id;
733
734 IF p_batch_type <> 'INT_INVOICE' THEN
735 v_status := 'U';
736 ELSE
737 v_status := 'A';
738 END IF;
739
740
741 IF p_manual_entry_flag = 'Y' AND v_rates_exist_manual_loc = 'Y' THEN
742 v_emission_location := p_emission_location;
743 END IF;
744
745 -- Insert Row
746
747 IF v_emission_batch_id is NULL THEN
748
749 SELECT GHG_TRANSACTION_BATCHES_s.NEXTVAL
750 INTO v_emission_batch_id
751 FROM DUAL;
752
753 GHG_TRANSACTION_BATCHES_PKG.insert_row (x_rowid => v_batch_rowid,
754 x_transaction_batch_id => v_emission_batch_id,
755 x_transaction_batch_number => p_batch_number,
756 x_batch_type => p_batch_type,
757 x_batch_sub_type => p_batch_sub_type,
758 x_vendor_id => v_supplier_id,
759 x_vendor_site_id => v_supplier_site_id,
760 x_description => p_batch_description||'-'||p_emission_from_date||'-'||p_emission_to_date,
761 x_status => v_status,
762 x_reversal_date => NULL,
763 x_reversed_transact_batch_id => NULL,
764 x_org_id => p_org_id,
765 x_set_of_books_id => v_set_of_books_id,
766 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
767 x_creation_date => SYSDATE,
768 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
769 x_last_update_date => SYSDATE,
770 x_last_update_login => -1,
771 x_called_from_api => 'Y');
772
773 END IF;
774
775 IF p_batch_type = 'EXTERNAL INVOICE' THEN
776 v_supplier_id:= p_supplier;
777 v_supplier_site_id := p_supplier_site_name;
778 END IF;
779
780 GHG_TRANSACTIONS_PKG.insert_row (x_rowid => v_batch_rowid,
781 x_txn_type_lookup_code => p_batch_type,
782 x_emission_id => v_emission_id,
783 x_emission_batch_id => v_emission_batch_id,
784 x_facility_id => v_facility_id,
785 x_emission_source_id => v_emission_source_id,
786 x_scope => nvl(p_emission_scope,v_emission_scope),
787 x_location => v_emission_location,
788 --x_location => nvl(p_emission_location,v_emission_location),
789 --Bug 12546495: ssurendr. Use location from organization
790 x_emission_type_lookup_code => 'C',
791 x_usage_quantity => p_emission_usage,
792 x_unit_of_measure => p_emission_uom,
793 x_uom_conversion => v_uom_conversion,
794 x_emission_date_from => p_emission_from_date,
795 x_emission_date_to => p_emission_to_date,
796 x_description => p_emission_description,
797 x_org_id => p_org_id,
798 x_set_of_books_id => v_set_of_books_id,
799 x_emission_line_number => v_max_line_no,
800 x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
801 x_creation_date => SYSDATE,
802 x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
803 x_last_update_date => SYSDATE,
804 x_last_update_login => -1,
805 x_commit_flag => p_commit_flag,
806 -- x_emission_rate_id => -1,
807 -- x_emission_factor_id => NULL,
808 x_energy_quantity => NULL,
809 x_emission_quantity => NULL,
810 x_invoice_id => p_invoice_id,
811 x_distribution_line_number => p_invoice_line_num,
812 x_invoice_line_number => p_invoice_dist_num,
813 x_vendor_id => v_supplier_id,
814 x_vendor_site_id => v_supplier_site_id,
815 x_inventory_item_id => v_item_id,
816 x_ghg_asset_id => v_asset_id,
817 x_measurement_criteria => p_m_criteria ,
818 x_attribute1 => NULL,
819 x_attribute2 => NULL,
820 x_attribute3 => NULL,
821 x_attribute4 => NULL,
822 x_attribute5 => NULL,
823 x_attribute6 => NULL,
824 x_attribute7 => NULL,
825 x_attribute8 => NULL,
826 x_attribute9 => NULL,
827 x_attribute10 => NULL,
828 x_attribute11 => NULL,
829 x_attribute12 => NULL,
830 x_attribute13 => NULL,
831 x_attribute14 => NULL,
832 x_attribute15 => NULL,
833 x_attribute16 => NULL,
834 x_attribute17 => NULL,
835 x_attribute18 => NULL,
836 x_attribute19 => NULL,
837 x_attribute20 => NULL,
838 x_attribute_category => NULL,
839 x_called_from_api => 'Y');
840
841 EXCEPTION
842 WHEN raise_error THEN
843 x_debug('Raise Error 1');
844
845 -- Bug 12434320
846 v_msg_code := FND_MESSAGE.GET_ENCODED;
847
848 if v_msg_code is not null then
849 v_msg_code := substr(v_msg_code,5);
850 v_msg_code := substr(v_msg_code,1,length(v_msg_code)-1);
851 FND_MESSAGE.SET_NAME('GHG',v_msg_code);
852 RAISE_APPLICATION_ERROR(-20002, v_msg_code || '~');
853 else
854 FND_MESSAGE.SET_NAME('GHG', 'GHG_DEBUG');
855 FND_MESSAGE.SET_TOKEN('ERROR', substr(SQLERRM,1,75));
856 --FND_MESSAGE.SET_TOKEN('DEBUG_INFO', NULL);
857 RAISE_APPLICATION_ERROR(-20002, 'GHG_DEBUG~' || SQLERRM);
858 end if;
859
860
861
862 when others then
863
864 FND_MESSAGE.SET_NAME('GHG', 'GHG_SQLERRM');
865 FND_MESSAGE.SET_TOKEN('ERROR', substr(SQLERRM,1,75));
866 RAISE_APPLICATION_ERROR(-20002, 'GHG_SQLERRM~' || SQLERRM);
867
868 END create_transaction;
869
870
871 PROCEDURE rebuild_emissions (errbuf OUT NOCOPY VARCHAR2,
872 retcode OUT NOCOPY NUMBER,
873 p_org_id IN NUMBER,
874 p_supplier_id IN NUMBER DEFAULT NULL,
875 p_invoice_id IN NUMBER DEFAULT NULL,
876 p_facility_id IN NUMBER DEFAULT NULL,
877 p_source_id IN NUMBER DEFAULT NULL,
878 p_emssion_from IN VARCHAR2 DEFAULT NULL,
879 p_emssion_to IN VARCHAR2 DEFAULT NULL
880 ) IS
881
882 raise_error EXCEPTION;
883 v_message varchar2(1000);
884 v_emission_from_date date;
885 v_emission_to_date date;
886 v_count number :=0 ; --bug 14741401--neechand
887 v_msg varchar2(500) ; --bug 14741401--neechand
888
889 CURSOR emissions_records IS
890 SELECT *
891 FROM GHG_TRANSACTIONS_all em
892 WHERE org_id = p_org_id
893 AND nvl(em.vendor_id,-1) = nvl(p_supplier_id, nvl(em.vendor_id,-1))
894 AND nvl(em.invoice_id,-1) = nvl(p_invoice_id, nvl(em.invoice_id,-1) )
895 AND em.ghg_organization_id = nvl(p_facility_id, em.ghg_organization_id)
896 AND em.transaction_source_id = nvl(p_source_id, em.transaction_source_id)
897 AND (trunc(em.transaction_date_from) between nvl(v_emission_from_date, em.transaction_date_from) and nvl(v_emission_to_date, em.transaction_date_to)
898 OR trunc(em.transaction_date_to) between nvl(v_emission_from_date, em.transaction_date_from) and nvl(v_emission_to_date, em.transaction_date_to))
899 UNION ALL
900 SELECT *
901 FROM GHG_TRANSACTIONS_all em
902 WHERE org_id = p_org_id
903 AND nvl(em.vendor_id,-1) = nvl(p_supplier_id, nvl(em.vendor_id,-1))
904 AND nvl(em.invoice_id,-1) = nvl(p_invoice_id, nvl(em.invoice_id,-1) )
905 AND em.ghg_organization_id = nvl(p_facility_id, em.ghg_organization_id)
906 AND em.transaction_source_id = nvl(p_source_id, em.transaction_source_id)
907 AND (trunc(em.transaction_date_from) between nvl(v_emission_from_date, em.transaction_date_from) and nvl(v_emission_to_date, em.transaction_date_to)
908 OR trunc(em.transaction_date_to) between nvl(v_emission_from_date, em.transaction_date_from) and nvl(v_emission_to_date, em.transaction_date_to))
909 AND EXISTS (SELECT 1
910 FROM ap_invoices_all ap
911 WHERE ap.invoice_id = em.invoice_id
912 AND ap.vendor_id = p_supplier_id);
913
914
915 BEGIN
916 mo_global.set_policy_context_server(p_access_mode => 'S',p_org_id => p_org_id);
917 -- Check that all the parameters are populated
918
919 IF (p_supplier_id IS NULL AND
920 p_invoice_id IS NULL AND
921 p_facility_id IS NULL AND
922 p_source_id IS NULL AND
923 (p_emssion_from IS NULL OR p_emssion_to IS NULL))
924 THEN
925 v_message := 'Global emissions recalculation is not permitted';
926 RAISE raise_error;
927 ELSE
928 v_emission_from_date :=
929 fnd_date.canonical_to_date(p_emssion_from);
930 v_emission_to_date :=
931 fnd_date.canonical_to_date(p_emssion_to);
932 FOR emissions in emissions_records LOOP
933 v_count := v_count+1; ---Added by neechand as per Bug 14741401 on 23rd jan 2013
934 GHG_EMISSION_RATES_PKG.calculate_emissions(emissions.transaction_id);
935 END LOOP;
936
937 --Start of Code--Added by neechand as per Bug 14741401 on 23rd jan 2013
938 /* v_msg:=fnd_message.get_string('GHG','GHG_LOG_INFO_MSG');
939 v_msg := v_msg || v_count;
940 FND_FILE.put_line(FND_FILE.log, v_msg);*/
941
942 FND_MESSAGE.SET_NAME('GHG', 'GHG_LOG_INFO_MSG');
943 FND_MESSAGE.SET_TOKEN('ROW_COUNT',v_count);
944 FND_FILE.put_line(FND_FILE.log, FND_MESSAGE.GET);
945 --End of Code--Added by neechand as per Bug 14741401 on 23rd jan 2013
946 END IF;
947
948 EXCEPTION
949 WHEN raise_error THEN
950 FND_FILE.put_line(FND_FILE.log, v_message);
951 ERRBUF := 'UserERROR :'||v_message;
952 retcode := 2;
953
954 WHEN OTHERS THEN
955 FND_FILE.put_line(FND_FILE.log, SQLERRM);
956 ERRBUF := 'SQLERROR :'||SQLERRM;
957 retcode := 2;
958
959
960 END;
961
962 PROCEDURE run_etl (errbuf out nocopy varchar2,
963 retcode out nocopy number,
964 p_org_id in number) is
965
966 v_message varchar2(255);
967 v_stmt VARCHAR2(4000);
968 raise_error EXCEPTION;
969 v_dblink VARCHAR2(64);
970 v_language VARCHAR2(30);
971 v_uom VARCHAR2(20) DEFAULT 'KG'; -- added for bug 11924018
972 v_uom_class VARCHAR2(20);
973 v_final_conv_rate NUMBER DEFAULT 1;
974 v_conv_rate NUMBER;
975 v_emission_conv_rate NUMBER;
976 v_emission_uom_class VARCHAR2(20) :=fnd_profile.value('GHG_EMISSION_UOM_CLASS');
977 v_emission_uom VARCHAR2(20) := fnd_profile.value('GHG_EMISSION_UOM');
978
979 procedure write_to_log(msg varchar2) is
980 begin
981 fnd_file.put_line(fnd_file.log, msg);
982 end;
983
984 procedure clear_table (v_table VARCHAR2, p_dblink VARCHAR2) is
985 v_smt varchar2(1000) := 'delete from ';
986 begin
987 v_smt:=v_smt||v_table || '@' || p_dblink || ' WHERE SOURCE_APPLICATION_CODE='''|| p_org_id ||'''' ;
988
989
990 write_to_log(v_smt);
991 execute immediate v_smt;
992
993 EXECUTE IMMEDIATE 'commit';
994 end;
995 begin
996 -- Set Org Context
997 -----------------------------------------------------------------------------------------
998 -- mo_global.set_policy_context_server(p_access_mode => 'S',p_org_id => p_org_id);
999
1000 v_dblink := fnd_profile.value('GHG_DW_DBLINK');
1001
1002 if v_dblink is null then
1003 FND_MESSAGE.SET_NAME('GHG', 'GHG_DW_DB_LINK_MISSING');
1004 RAISE raise_error;
1005 END IF;
1006
1007 SELECT USERENV('LANG') INTO v_language from dual;
1008
1009
1010 write_to_log('Cleardown Data Warehouse');
1011
1012 clear_table('GHG_ASSETS_D', v_dblink);
1013 clear_table('GHG_EMISSION_SCOPES_D', v_dblink);
1014 clear_table('GHG_SOURCES_D', v_dblink);
1015 clear_table('GHG_ITEMS_D', v_dblink);
1016 clear_table('GHG_SUPPLIERS_D', v_dblink);
1017 clear_table('GHG_ORGANIZATION_HIERARCHIES_D', v_dblink);
1018 clear_table('GHG_ORG_HIERARCHY_STRUCTURES_D', v_dblink);
1019 clear_table('GHG_ORGANIZATIONS_D', v_dblink);
1020
1021
1022 clear_table('GHG_TRANSACTIONS_D', v_dblink);
1023 clear_table('GHG_TRANSACTION_DETAILS_F', v_dblink);
1024 clear_table('GHG_TRANSACTIONS_BY_MONTH_F', v_dblink);
1025 clear_table('GHG_ORGANIZATION_INTERESTS_D', v_dblink);
1026 clear_table('GHG_ORGANIZATION_CONTROL_D', v_dblink);
1027 clear_table('GHG_KPI_TRANSACTIONS_F', v_dblink);
1028 clear_table('GHG_KPI_DEFINITIONS_D', v_dblink);
1029
1030 write_to_log('Load Data into Data Warehouse ');
1031
1032 write_to_log('Inserting into GHG_ASSETS_D');
1033 v_stmt :=
1034 'INSERT INTO GHG_ASSETS_D@' || v_dblink ||
1035 ' ( ' ||
1036 'SOURCE_APPLICATION_CODE, ' ||
1037 'GHG_ASSET_ID, ' ||
1038 'ASSET_IDENTIFIER1, ' ||
1039 'ASSET_IDENTIFIER2, ' ||
1040 'ASSET_DESCRIPTION_LINE1, ' ||
1041 'ASSET_DESCRIPTION_LINE2, ' ||
1042 'ASSET_TYPE, ' ||
1043 'TRANSPORT_TYPE ' ||
1044 ') ' ||
1045 'SELECT ' || p_org_id || ', ' ||
1046 'GHG_ASSET_ID, ' ||
1047 'ASSET_NUMBER, ' ||
1048 'ASSET_ID, ' ||
1049 'DESCRIPTION, ' ||
1050 '''Oracle eBs Item ID'', ' ||
1051 'ASSET_TYPE_LOOKUP_CODE, ' ||
1052 'TRANSPORT_TYPE_LOOKUP_CODE ' ||
1053 'FROM GHG_ASSETS';
1054
1055 execute immediate v_stmt;
1056
1057
1058 write_to_log('Inserting into GHG_EMISSION_SCOPES_D');
1059 v_stmt :=
1060 'INSERT INTO GHG_EMISSION_SCOPES_D@' || v_dblink ||
1061 ' ( ' ||
1062 'SOURCE_APPLICATION_CODE, ' ||
1063 'EMISSION_SCOPE_ID, ' ||
1064 'EMISSION_SCOPE_CODE, ' ||
1065 'EMISSION_SCOPE_DESCRIPTION ' ||
1066 ') ' ||
1067 'SELECT ' || p_org_id || ', ' ||
1068 'LOOKUP_CODE, ' ||
1069 'LOOKUP_CODE, ' ||
1070 'DESCRIPTION ' ||
1071 'FROM FND_LOOKUP_VALUES' ||
1072 ' WHERE LOOKUP_TYPE = ''GHG_EMISSION_SCOPE''' || ' AND LANGUAGE='||concat(''''||v_language||'','''');
1073
1074 execute immediate v_stmt;
1075
1076 write_to_log('Inserting into GHG_SOURCES_D');
1077 v_stmt :=
1078 'INSERT INTO GHG_SOURCES_D@' || v_dblink ||
1079 ' ( ' ||
1080 'SOURCE_APPLICATION_CODE, ' ||
1081 'SOURCE_ID, ' ||
1082 'SOURCE_CODE, ' ||
1083 'SOURCE_DESCRIPTION, ' ||
1084 'SOURCE_INFO_CODE_1, ' ||
1085 'SOURCE_INFO_DESC_1, ' ||
1086 'GRANDPARENT_SOURCE_CODE, ' ||
1087 'GRANDPARENT_SOURCE_DESC, ' ||
1088 'PARENT_SOURCE_CODE, ' ||
1089 'PARENT_SOURCE_DESC, ' ||
1090 'SOURCE_TYPE_CODE, ' ||
1091 'SOURCE_TYPE_DESC ' ||
1092 ') ' ||
1093 'select src.org_id, ' ||
1094 'fac.SOURCE_COMBINATION_ID, ' ||
1095 'fac.SOURCE_COMBINATION_CODE, ' ||
1096 'nvl(fac.SOURCE_COMBINATION_DESCRIPTION, fac.SOURCE_COMBINATION_CODE), ' ||
1097 'src.source_id, ' ||
1098 'src.source_name, ' ||
1099 'rcc.segment1,
1100 (SELECT fltl.description
1101 FROM FND_ID_FLEX_SEGMENTS idf ,FND_ID_FLEX_SEGMENTS_TL tl
1102 , fnd_flex_values val , fnd_flex_values_tl fltl
1103 WHERE idf.ID_FLEX_CODE = '||'''GHG#''' ||
1104 ' AND idf.id_flex_num = rcc.chart_of_accounts_id
1105 and idf.flex_value_set_id = val.flex_value_set_id
1106 and idf.ID_FLEX_CODE = tl.ID_FLEX_CODE
1107 and idf.application_column_name = tl.APPLICATION_COLUMN_NAME
1108 AND idf.id_flex_num = tl.id_flex_num
1109 and val.flex_value_id = fltl.flex_value_id
1110 and val.flex_value = rcc.segment1 and tl.language= '||concat(''''||v_language||'','''')
1111 || ' and fltl.language= '||concat(''''||v_language||'','''')
1112 || ' and idf.application_column_name = '||'''SEGMENT1''' ||') segment1_desc,
1113 rcc.segment2,
1114 (SELECT fltl.description
1115 FROM FND_ID_FLEX_SEGMENTS idf ,FND_ID_FLEX_SEGMENTS_TL tl
1116 , fnd_flex_values val , fnd_flex_values_tl fltl
1117 WHERE idf.ID_FLEX_CODE = '||'''GHG#''' ||
1118 ' AND idf.id_flex_num = rcc.chart_of_accounts_id
1119 and idf.flex_value_set_id = val.flex_value_set_id
1120 and idf.ID_FLEX_CODE = tl.ID_FLEX_CODE
1121 and idf.application_column_name = tl.APPLICATION_COLUMN_NAME
1122 AND idf.id_flex_num = tl.id_flex_num
1123 and val.flex_value_id = fltl.flex_value_id
1124 and val.flex_value = rcc.segment2 and tl.language= '||concat(''''||v_language||'','''')
1125 || ' and fltl.language= '||concat(''''||v_language||'','''')
1126 || ' and idf.application_column_name = '||'''SEGMENT2''' ||') segment2_desc,
1127 rcc.segment3,
1128 (SELECT fltl.description
1129 FROM FND_ID_FLEX_SEGMENTS idf ,FND_ID_FLEX_SEGMENTS_TL tl
1130 , fnd_flex_values val , fnd_flex_values_tl fltl
1131 WHERE idf.ID_FLEX_CODE = '||'''GHG#''' ||
1132 ' AND idf.id_flex_num = rcc.chart_of_accounts_id
1133 and idf.flex_value_set_id = val.flex_value_set_id
1134 and idf.ID_FLEX_CODE = tl.ID_FLEX_CODE
1135 and idf.application_column_name = tl.APPLICATION_COLUMN_NAME
1136 AND idf.id_flex_num = tl.id_flex_num
1137 and val.flex_value_id = fltl.flex_value_id
1138 and val.flex_value = rcc.segment3 and tl.language= '||concat(''''||v_language||'','''')
1139 || ' and fltl.language= '||concat(''''||v_language||'','''')
1140 || ' and idf.application_column_name = '||'''SEGMENT3''' ||') segment3_desc '||
1141 'from GHG_SOURCES_all src ' ||
1142 ', GHG_SOURCE_COMBINATIONS_ALL fac ' ||
1143 ', GHG_REPORT_COMBINATIONS rcc ' ||
1144 'where fac.source_id = src.source_id ' ||
1145 'and fac.code_combination_id = rcc.code_combination_id (+)';
1146
1147
1148 IF p_org_id IS NOT NULL THEN
1149 v_stmt :=v_stmt || ' AND src.org_id='|| p_org_id ||'' ;
1150 END IF;
1151
1152 execute immediate v_stmt;
1153
1154
1155 write_to_log('Inserting into GHG_ITEMS_D');
1156 v_stmt :=
1157 'INSERT INTO GHG_ITEMS_D@' || v_dblink ||
1158 ' ( ' ||
1159 'SOURCE_APPLICATION_CODE, ' ||
1160 'ITEM_ID, ' ||
1161 'ITEM_CODE, ' ||
1162 'ITEM_DESCRIPTION ' ||
1163 ') ' ||
1164 'SELECT ghgi.ORG_ID, ' ||
1165 'ghgi.INVENTORY_ITEM_ID, ' ||
1166 'i.SEGMENT1, ' ||
1167 'i.DESCRIPTION ' ||
1168 'FROM GHG_SUPPLIER_ITEMS_ALL GHGI, ' ||
1169 'MTL_SYSTEM_ITEMS I ' ||
1170 'WHERE i.INVENTORY_ITEM_ID = ghgi.INVENTORY_ITEM_ID ' ||
1171 'AND i.organization_id = ghgi.ORG_ID';
1172
1173 IF p_org_id IS NOT NULL THEN
1174 v_stmt :=v_stmt || ' AND GHGI.org_id='|| p_org_id ||'' ;
1175 END IF;
1176
1177 execute immediate v_stmt;
1178
1179
1180 write_to_log('Inserting into GHG_SUPPLIERS_D');
1181 v_stmt :=
1182 'INSERT INTO GHG_SUPPLIERS_D@' || v_dblink ||
1183 ' ( ' ||
1184 'SOURCE_APPLICATION_CODE, ' ||
1185 'SUPPLIER_ID, ' ||
1186 'SUPPLIER_NAME ' ||
1187 ') ' ||
1188 'SELECT distinct ' ||
1189 'ghgs.ORG_ID, ' ||
1190 'ghgs.SUPPLIER_ID, ' ||
1191 'sup.vendor_name ' ||
1192 'FROM GHG_SUPPLIER_ITEMS_ALL ghgs ' ||
1193 ', AP_SUPPLIERS sup ' ||
1194 'WHERE ghgs.supplier_id = sup.vendor_id';
1195
1196 IF p_org_id IS NOT NULL THEN
1197 v_stmt :=v_stmt || ' AND GHGS.org_id='|| p_org_id ||'' ;
1198 END IF;
1199
1200 execute immediate v_stmt;
1201
1202
1203 write_to_log('Inserting into GHG_ORGANIZATION_HIERARCHIES_D');
1204 v_stmt :=
1205 'INSERT INTO GHG_ORGANIZATION_HIERARCHIES_D@' || v_dblink ||
1206 '( ' ||
1207 'SOURCE_APPLICATION_CODE, ' ||
1208 'ORGANIZATION_HIERARCHY_ID, ' ||
1209 'ORGANIZATION_HIERARCHY_CODE, ' ||
1210 'ORGANIZATION_HIERARCHY_NAME, ' ||
1211 'ORGANIZATION_HIERARCHY_TYPE, ' ||
1212 'DATE_FROM, ' ||
1213 'DATE_TO ' ||
1214 ') ' ||
1215 'SELECT ' || p_org_id || ', ' ||
1216 'v.hierarchy_version_id, ' ||
1217 'v.hierarchy_version_id, ' ||
1218 'h.name, ' ||
1219 '''MANAGEMENT'', ' ||
1220 'v.date_from, ' ||
1221 'v.date_to ' ||
1222 'FROM PER_GEN_HIERARCHY h ' ||
1223 ', PER_GEN_HIERARCHY_VERSIONS v ' ||
1224 'WHERE h.hierarchy_id = v.hierarchy_id ' ||
1225 'and h.type = ''GHG_ORGANIZATION_STRUCTURE''' ||
1226 'AND v.status = ''A''';
1227
1228 execute immediate v_stmt;
1229
1230
1231
1232 write_to_log('Inserting into GHG_ORG_HIERARCHY_STRUCTURES_D');
1233 declare
1234 cursor hierarchy_versions is
1235 select v.hierarchy_version_id
1236 from PER_GEN_HIERARCHY h,
1237 PER_GEN_HIERARCHY_VERSIONS v
1238 where h.hierarchy_id = v.hierarchy_id
1239 and h.type = 'GHG_ORGANIZATION_STRUCTURE'
1240 and v.status = 'A';
1241
1242 cursor node_members_treewalk (p_version_id number) is
1243 select *
1244 from (
1245 select level lvl, parent_hierarchy_node_id, hierarchy_node_id, node_type, entity_id
1246 from (select * from per_gen_hierarchy_nodes where hierarchy_version_id = p_version_id )
1247 start with hierarchy_node_id IN (select hierarchy_node_id from per_gen_hierarchy_nodes where hierarchy_version_id = p_version_id and parent_hierarchy_node_id is null)
1248 connect by prior hierarchy_node_id = parent_hierarchy_node_id )
1249 order by lvl desc ;
1250
1251 cursor node_reverse_treewalk (p_version_id number, p_start_point number) is
1252 select *
1253 from (
1254 select level lvl, parent_hierarchy_node_id, hierarchy_node_id, node_type, entity_id
1255 from (select * from per_gen_hierarchy_nodes where hierarchy_version_id = p_version_id)
1256 start with hierarchy_node_id = p_start_point
1257 connect by prior parent_hierarchy_node_id = hierarchy_node_id )
1258 order by lvl;
1259
1260 vl_text varchar2(4000) ;
1261 p_version_id number;
1262 vl_tmp varchar2(4000);
1263 vl_levels number := 12;
1264
1265
1266 begin
1267 for hierarchy_version in hierarchy_versions loop
1268 p_version_id := hierarchy_version.hierarchy_version_id;
1269 for node_members in node_members_treewalk (p_version_id) loop
1270
1271 vl_text := 'insert into GHG_ORG_HIERARCHY_STRUCTURES_D@' || v_dblink || ' VALUES (' || p_org_id || ', ' || p_version_id || ',';
1272
1273 for reverse_node in node_reverse_treewalk(p_version_id, node_members.hierarchy_node_id) loop
1274 vl_tmp := reverse_node.entity_id||','||vl_tmp;
1275 end loop;
1276
1277 for i in 1..vl_levels-node_members.lvl loop
1278 vl_tmp:=vl_tmp||node_members.entity_id||',';
1279 end loop;
1280
1281 vl_tmp:=node_members.entity_id||','||vl_tmp||'''N'')';
1282 vl_text:=vl_text||vl_tmp;
1283
1284 vl_tmp := vl_text;
1285
1286 write_to_log(vl_text);
1287 EXECUTE IMMEDIATE vl_text;
1288 vl_tmp := null;
1289
1290 end loop;
1291 end loop;
1292
1293 write_to_log('Deleting from GHG_ORG_HIERARCHY_STRUCTURES_D');
1294 v_stmt :=
1295 'delete from GHG_ORG_HIERARCHY_STRUCTURES_D@' || v_dblink || ' str ' ||
1296 'where not exists ( select 1 from GHG_ORGANIZATIONS_ALL fac ' ||
1297 'where allow_transactions = ''Y''' ||
1298 ' and str.ORGANIZATION_id = fac.ghg_organization_id)';
1299
1300 EXECUTE IMMEDIATE v_stmt;
1301
1302 exception
1303 when others then
1304 write_to_log(substr(SQLERRM, 1, 200));
1305 end;
1306
1307
1308 write_to_log('Inserting into GHG_ORGANIZATIONS_D_TMP');
1309 INSERT INTO GHG_ORGANIZATIONS_D_TMP
1310 ( SOURCE_APPLICATION_CODE,
1311 ORGANIZATION_ID,
1312 ORGANIZATION_EXTERNAL_ID,
1313 ORGANIZATION_CODE,
1314 ORGANIZATION_NAME,
1315 CEO_NAME,
1316 COMPANY_IDENTIFIER,
1317 DATE_EFFECTIVE_FROM,
1318 DATE_EFFECTIVE_TO,
1319 SIC_GROUP_CODE,
1320 SIC_GROUP_DESC,
1321 SIC_DIVISION_CODE,
1322 SIC_DIVISION_DESC,
1323 ORGANIZATION_SIZE,
1324 ORGANIZATION_TYPE,
1325 ADDRESS_LINE_1,
1326 ADDRESS_LINE_2,
1327 ADDRESS_LINE_3,
1328 ADDRESS_LINE_4,
1329 ADDRESS_SUBURB,
1330 ADDRESS_STATE,
1331 ADDRESS_COUNTRY,
1332 ADDRESS_POSTCODE,
1333 ADDRESS_LATITUDE,
1334 address_longitude,
1335 ADDRESS_LOCATION)
1336 select fac.org_id,
1337 fac.ghg_organization_id,
1338 fac.ghg_organization_id,
1339 fac.GHG_ORGANIZATION_CODE,
1340 fac.description,
1341 fac.ceo_name,
1342 fac.COMPANY_IDENTIFIER,
1343 fac.start_date,
1344 fac.end_date,
1345 sic.sic_code,
1346 anz.meaning,
1347 sic.division_code,
1348 div.meaning,
1349 fac.ORGANIZATION_SIZE_LOOKUP_CODE,
1350 fac.ORGANIZATION_TYPE,
1351 hr.address_line_1,
1352 hr.address_line_2,
1353 hr.address_line_3,
1354 null,
1355 hr.town_or_city,
1356 nvl(hr.region_2, hr.region_1),
1357 tr.territory_short_name,
1358 postal_code,
1359 fac.latitude,
1360 fac.longitude,
1361 location_code
1362 from GHG_ORGANIZATIONS_ALL fac
1363 , GHG_ORG_SIC_MAPPINGS_ALL sic
1364 , fnd_lookup_values anz
1365 , fnd_lookup_values div
1366 , hr_locations hr
1367 , fnd_territories_vl tr
1368 where fac.ghg_organization_id = sic.ghg_organization_id(+)
1369 and anz.lookup_type(+) = 'GHG_SIC_CODES'
1370 and div.lookup_type(+) = 'GHG_DIVISION_CODES'
1371 and sic.sic_code = anz.lookup_code(+)
1372 and sic.division_code = div.lookup_code(+)
1373 and hr.location_id = fac.location_id
1374 and hr.country = tr.territory_code
1375 and anz.language(+)=v_language
1376 and div.language(+)=v_language
1377 and fac.org_id=p_org_id;
1378
1379
1380
1381
1382 write_to_log('Inserting into GHG_ORGANIZATIONS_D');
1383 v_stmt :=
1384 'INSERT INTO GHG_ORGANIZATIONS_D@' || v_dblink ||
1385 '( SOURCE_APPLICATION_CODE, ' ||
1386 'ORGANIZATION_ID, ' ||
1387 'ORGANIZATION_EXTERNAL_ID, ' ||
1388 'ORGANIZATION_CODE, ' ||
1389 'ORGANIZATION_NAME, ' ||
1390 'CEO_NAME, ' ||
1391 'COMPANY_IDENTIFIER, ' ||
1392 'DATE_EFFECTIVE_FROM, ' ||
1393 'DATE_EFFECTIVE_TO, ' ||
1394 'SIC_GROUP_CODE, ' ||
1395 'SIC_GROUP_DESC, ' ||
1396 'SIC_DIVISION_CODE, ' ||
1397 'SIC_DIVISION_DESC, ' ||
1398 'ORGANIZATION_SIZE, ' ||
1399 'ORGANIZATION_TYPE, ' ||
1400 'ADDRESS_LINE_1, ' ||
1401 'ADDRESS_LINE_2, ' ||
1402 'ADDRESS_LINE_3, ' ||
1403 'ADDRESS_LINE_4, ' ||
1404 'ADDRESS_SUBURB, ' ||
1405 'ADDRESS_STATE, ' ||
1406 'ADDRESS_COUNTRY, ' ||
1407 'ADDRESS_POSTCODE, ' ||
1408 'ADDRESS_LATITUDE, ' ||
1409 'address_longitude, ' ||
1410 'ADDRESS_LOCATION) ' ||
1411 'select SOURCE_APPLICATION_CODE, ' ||
1412 'ORGANIZATION_ID, ' ||
1413 'ORGANIZATION_EXTERNAL_ID, ' ||
1414 'ORGANIZATION_CODE, ' ||
1415 'ORGANIZATION_NAME, ' ||
1416 'CEO_NAME, ' ||
1417 'COMPANY_IDENTIFIER, ' ||
1418 'DATE_EFFECTIVE_FROM, ' ||
1419 'DATE_EFFECTIVE_TO, ' ||
1420 'SIC_GROUP_CODE, ' ||
1421 'SIC_GROUP_DESC, ' ||
1422 'SIC_DIVISION_CODE, ' ||
1423 'SIC_DIVISION_DESC, ' ||
1424 'ORGANIZATION_SIZE, ' ||
1425 'ORGANIZATION_TYPE, ' ||
1426 'ADDRESS_LINE_1, ' ||
1427 'ADDRESS_LINE_2, ' ||
1428 'ADDRESS_LINE_3, ' ||
1429 'ADDRESS_LINE_4, ' ||
1430 'ADDRESS_SUBURB, ' ||
1431 'ADDRESS_STATE, ' ||
1432 'ADDRESS_COUNTRY, ' ||
1433 'ADDRESS_POSTCODE, ' ||
1434 'ADDRESS_LATITUDE, ' ||
1435 'address_longitude, ' ||
1436 'ADDRESS_LOCATION ' ||
1437 'from GHG_ORGANIZATIONS_D_TMP WHERE SOURCE_APPLICATION_CODE='|| p_org_id ||'';
1438
1439
1440 EXECUTE IMMEDIATE v_stmt;
1441
1442
1443 write_to_log('Inserting into GHG_ORG_HIERARCHY_STRUCTURES_D');
1444 v_stmt :=
1445 'insert INTO GHG_ORG_HIERARCHY_STRUCTURES_D@' || v_dblink ||
1446 ' ( ' ||
1447 'SOURCE_APPLICATION_CODE, ' ||
1448 'ORGANIZATION_HIERARCHY_ID, ' ||
1449 'ORGANIZATION_ID, ' ||
1450 'LEVEL_01_ORGANIZATION_ID, ' ||
1451 'LEVEL_02_ORGANIZATION_ID, ' ||
1452 'LEVEL_03_ORGANIZATION_ID, ' ||
1453 'LEVEL_04_ORGANIZATION_ID, ' ||
1454 'LEVEL_05_ORGANIZATION_ID, ' ||
1455 'LEVEL_06_ORGANIZATION_ID, ' ||
1456 'LEVEL_07_ORGANIZATION_ID, ' ||
1457 'LEVEL_08_ORGANIZATION_ID, ' ||
1458 'LEVEL_09_ORGANIZATION_ID, ' ||
1459 'LEVEL_10_ORGANIZATION_ID, ' ||
1460 'LEVEL_11_ORGANIZATION_ID, ' ||
1461 'LEVEL_12_ORGANIZATION_ID, ' ||
1462 'ORPHAN_FLAG ' ||
1463 ') ' ||
1464 'select source_application_code, ' ||
1465 '0, ' ||
1466 'ORGANIZATION_id, ' ||
1467 'ORGANIZATION_id, ' ||
1468 'ORGANIZATION_id, ' ||
1469 'ORGANIZATION_id, ' ||
1470 'ORGANIZATION_id, ' ||
1471 'ORGANIZATION_id, ' ||
1472 'ORGANIZATION_id, ' ||
1473 'ORGANIZATION_id, ' ||
1474 'ORGANIZATION_id, ' ||
1475 'ORGANIZATION_id, ' ||
1476 'ORGANIZATION_id, ' ||
1477 'ORGANIZATION_id, ' ||
1478 'ORGANIZATION_id, ' ||
1479 '''N'' ' ||
1480 'from GHG_ORGANIZATIONS_D_TMP org ' ||
1481 'where not exists (select 1 ' ||
1482 'from GHG_ORG_HIERARCHY_STRUCTURES_D@' || v_dblink || ' st ' ||
1483 'where org.ORGANIZATION_id = st.ORGANIZATION_id) AND SOURCE_APPLICATION_CODE='|| p_org_id ||'';
1484
1485 EXECUTE IMMEDIATE v_stmt;
1486 -- fix for bug 11924018
1487 IF v_emission_uom_class IS NOT NULL THEN
1488 IF v_emission_uom IS NOT NULL THEN
1489 select uom_class into v_uom_class from mtl_units_of_measure_tl where UOM_CODE=v_uom and language=userenv('LANG');
1490
1491
1492 IF v_uom_class = v_emission_uom_class THEN
1493 IF v_uom <> v_emission_uom THEN
1494 --Getting conversion rate for KG with respect base unit of measure
1495 SELECT muc.conversion_rate
1496 INTO v_conv_rate
1497 FROM mtl_units_of_measure_tl mum1,
1498 mtl_uom_conversions muc
1499 WHERE mum1.uom_code = v_uom
1500 AND muc.unit_of_measure = mum1.unit_of_measure AND mum1.language=USERENV('LANG') AND muc.INVENTORY_ITEM_ID=0;
1501
1502 --Getting conversion rate for User defined UOM with respect to base unit of measure
1503 SELECT muc.conversion_rate
1504 INTO v_emission_conv_rate
1505 FROM mtl_units_of_measure_tl mum1,
1506 mtl_uom_conversions muc
1507 WHERE mum1.uom_code = v_emission_uom
1508 AND muc.unit_of_measure = mum1.unit_of_measure and mum1.language=USERENV('LANG') AND muc.INVENTORY_ITEM_ID=0;
1509 -- Final rate of conversion
1510 v_final_conv_rate := (v_conv_rate/v_emission_conv_rate);
1511 -- assigning emission UOM to UOM
1512 v_uom:= v_emission_uom;
1513 END IF;
1514 ELSE
1515 write_to_log('UOM Conversion Not Found');
1516
1517 END IF;
1518 END IF;
1519 END IF;
1520
1521
1522
1523
1524
1525
1526 write_to_log('Inserting into GHG_TRANSACTIONS_D_TMP');
1527 v_stmt :=
1528 'INSERT INTO GHG_TRANSACTIONS_D_TMP ' ||
1529 '( ' ||
1530 'SOURCE_APPLICATION_CODE, ' ||
1531 'TRANSACTION_ID, ' ||
1532 'DOCUMENT_TYPE, ' ||
1533 'DOCUMENT_IDENTIFIER1, ' ||
1534 'TRANSACTION_DATE, ' ||
1535 'TRANSACTION_VALUE, ' ||
1536 'TRANSACTION_CURRENCY_CODE, ' ||
1537 'TRANSACTION_CURRENCY_DESC, ' ||
1538 'emission_source_id, ' ||
1539 'EMISSION_SOURCE_USAGE_QUANTITY, ' ||
1540 'EMISSION_SOURCE_USAGE_UOM, ' ||
1541 'EMISSION_SCOPE_ID, ' ||
1542 'ENERGY_TYPE, ' ||
1543 'ENERGY_QUANTITY, ' ||
1544 'ENERGY_UOM, ' ||
1545 'CO2_E_QUANTITY, ' ||
1546 'CO2_E_UOM, ' ||
1547 'GHG_ASSET_ID, ' ||
1548 'SUPPLIER_ID, ' ||
1549 'ITEM_ID, ' ||
1550 'ACTUAL_OR_ESTIMATE, ' ||
1551 'LOCATION_STATE, ' ||
1552 'location_county, ' ||
1553 'ORGANIZATION_id, ' ||
1554 'measurement_criteria_code, ' ||
1555 'measurement_criteria_desc, ' ||
1556 'ENTERED_USAGE_QUANTITY, ' ||
1557 'ENTERED_USAGE_UOM ' ||
1558 ') ' ||
1559 'SELECT tr.org_id SOURCE_APPLICATION_CODE ' ||
1560 ', tr.transaction_id transaction_id ' ||
1561 ', (select meaning from fnd_lookup_values_vl lv where lookup_type in (''GHG_TXN_TYPE'', ''GHG_INTERNAL_TXN_TYPE'') and tr.txn_type_lookup_code = lv.lookup_code) document_type ' ||
1562 ', decode(tr.txn_type_lookup_code, ''INT_INVOICE'', ap.invoice_num, tr.transaction_id) document_identifier1 ' ||
1563 ', rp.actual_date transaction_date ' ||
1564 ', nvl(ap.invoice_amount,0)/((tr.transaction_date_to-tr.transaction_date_from)+1) transaction_value ' ||
1565 ', ap.invoice_currency_code transaction_currency_code ' ||
1566 ', cur.name transaction_currency_desc ' ||
1567 ', em.source_combination_id transaction_source_id ' ||
1568 ', ((nvl(tr.usage_quantity,0)* ghg_utilities_pkg.get_conversion(tr.unit_of_measure, tr.transaction_source_id, ''STDUOM'', ''ETL''))/((tr.transaction_date_to-tr.transaction_date_from)+1)) emission_source_usage_quantity ' ||
1569 ', (select cl.STANDARD_UOM from GHG_SOURCES src, GHG_UOM_CLASSES cl where src.source_id = tr.transaction_source_id and src.ghg_uom_class_code = cl.ghg_uom_class_code) emission_source_usage_uom ' ||
1570 ', tr.emission_scope_lookup_code emission_scope_id ' ||
1571 ', (select meaning from fnd_lookup_values_vl lv where lookup_type = ''GHG_EMISSION_TYPE'' and tr.transaction_type_lookup_code = lv.lookup_code) energy_type ' ||
1572 ', nvl(tr.energy_quantity,0)/((tr.transaction_date_to-tr.transaction_date_from)+1) energy_quantity ' ||
1573 ', ''GJ'' energy_uom ' ||
1574 ', (nvl(tr.emission_quantity,0) /((tr.transaction_date_to-tr.transaction_date_from)+1))* '||fnd_number.NUMBER_TO_CANONICAL(v_final_conv_rate) || 'co2_e_quantity ' ||
1575 ', ''' || v_uom || ''' co2_e_uom ' ||
1576 ', tr.ghg_asset_id asset_id ' ||
1577 ', tr.vendor_id supplier_id ' ||
1578 ', tr.inventory_item_id item_id ' ||
1579 ', ''A'' actual_or_estimate ' ||
1580 ', (select address_state from GHG_ORGANIZATIONS_D_TMP org where org.ORGANIZATION_id = tr.ghg_organization_id) location_state ' ||
1581 ', (select address_country from GHG_ORGANIZATIONS_D_TMP org where org.ORGANIZATION_id = tr.ghg_organization_id) location_county ' ||
1582 ', tr.ghg_organization_id ORGANIZATION_id ' ||
1583 ', tr.measurement_criteria measurement_criteria_code ' ||
1584 ', tr.measurement_criteria measurement_criteria_desc ' ||
1585 ', nvl(tr.usage_quantity,0) ' ||
1586 ', tr.unit_of_measure ' ||
1587 'FROM GHG_TRANSACTIONS_ALL tr ' ||
1588 ', ap_invoices ap ' ||
1589 ', GHG_PERIODS_D@' || v_dblink || ' rp ' ||
1590 ', fnd_currencies_tl cur ' ||
1591 ', GHG_TRANSACTION_DETAILS_ALL em ' ||
1592 'where tr.invoice_id = ap.invoice_id(+) ' ||
1593 'and rp.actual_date between tr.transaction_date_from and tr.transaction_date_to ' ||
1594 'and ap.invoice_currency_code = cur.currency_code(+) ' ||
1595 'and em.transaction_id = tr.transaction_id ' ||
1596 'and em.transaction_date = rp.actual_date ' ||
1597 'and em.transaction_type = ''N'' ' ||
1598 'and tr.org_id= ' || p_org_id || ' ' ||
1599 'and nvl(cur.language,''' || v_language || ''') = ''' || v_language || '''';
1600
1601
1602 EXECUTE IMMEDIATE v_stmt;
1603
1604 write_to_log('Inserting into GHG_TRANSACTIONS_D');
1605 v_stmt :=
1606 'INSERT INTO GHG_TRANSACTIONS_D@' || v_dblink ||
1607 ' ( ' ||
1608 'SOURCE_APPLICATION_CODE, ' ||
1609 'TRANSACTION_ID, ' ||
1610 'DOCUMENT_TYPE, ' ||
1611 'DOCUMENT_IDENTIFIER1, ' ||
1612 'TRANSACTION_DATE, ' ||
1613 'TRANSACTION_VALUE, ' ||
1614 'TRANSACTION_CURRENCY_CODE, ' ||
1615 'TRANSACTION_CURRENCY_DESC, ' ||
1616 'emission_source_id, ' ||
1617 'EMISSION_SOURCE_USAGE_QUANTITY, ' ||
1618 'EMISSION_SOURCE_USAGE_UOM, ' ||
1619 'EMISSION_SCOPE_ID, ' ||
1620 'ENERGY_TYPE, ' ||
1621 'ENERGY_QUANTITY, ' ||
1622 'ENERGY_UOM, ' ||
1623 'CO2_E_QUANTITY, ' ||
1624 'CO2_E_UOM, ' ||
1625 'GHG_ASSET_ID, ' ||
1626 'SUPPLIER_ID, ' ||
1627 'ITEM_ID, ' ||
1628 'ACTUAL_OR_ESTIMATE, ' ||
1629 'LOCATION_STATE, ' ||
1630 'location_county, ' ||
1631 'ORGANIZATION_id, ' ||
1632 'measurement_criteria_code, ' ||
1633 'measurement_criteria_desc, ' ||
1634 'ENTERED_USAGE_QUANTITY, ' ||
1635 'ENTERED_USAGE_UOM' ||
1636 ') ' ||
1637 'SELECT ' ||
1638 'SOURCE_APPLICATION_CODE, ' ||
1639 'TRANSACTION_ID, ' ||
1640 'DOCUMENT_TYPE, ' ||
1641 'DOCUMENT_IDENTIFIER1, ' ||
1642 'TRANSACTION_DATE, ' ||
1643 'TRANSACTION_VALUE, ' ||
1644 'TRANSACTION_CURRENCY_CODE, ' ||
1645 'TRANSACTION_CURRENCY_DESC, ' ||
1646 'emission_source_id, ' ||
1647 'EMISSION_SOURCE_USAGE_QUANTITY, ' ||
1648 'EMISSION_SOURCE_USAGE_UOM, ' ||
1649 'EMISSION_SCOPE_ID, ' ||
1650 'ENERGY_TYPE, ' ||
1651 'ENERGY_QUANTITY, ' ||
1652 'ENERGY_UOM, ' ||
1653 'CO2_E_QUANTITY, ' ||
1654 'CO2_E_UOM, ' ||
1655 'GHG_ASSET_ID, ' ||
1656 'SUPPLIER_ID, ' ||
1657 'ITEM_ID, ' ||
1658 'ACTUAL_OR_ESTIMATE, ' ||
1659 'LOCATION_STATE, ' ||
1660 'location_county, ' ||
1661 'ORGANIZATION_id, ' ||
1662 'measurement_criteria_code, ' ||
1663 'measurement_criteria_desc, ' ||
1664 'ENTERED_USAGE_QUANTITY, ' ||
1665 'ENTERED_USAGE_UOM ' ||
1666 'FROM GHG_TRANSACTIONS_D_TMP WHERE SOURCE_APPLICATION_CODE='|| p_org_id ||'';
1667
1668
1669
1670 EXECUTE IMMEDIATE v_stmt;
1671
1672 write_to_log('Inserting into GHG_TRANSACTION_DETAILS_F');
1673 v_stmt :=
1674 'insert into GHG_TRANSACTION_DETAILS_F@' || v_dblink ||
1675 ' ( source_application_code, ' ||
1676 'transaction_id, ' ||
1677 'emission_gas_type, ' ||
1678 'EMISSION_QUANTITY, ' ||
1679 'EMISSION_uom, ' ||
1680 'TRANSACTION_DATE, ' ||
1681 'source_id, ' ||
1682 'EMISSION_SCOPE_ID, ' ||
1683 'ghg_asset_id, ' ||
1684 'SUPPLIER_ID, ' ||
1685 'ITEM_ID, ' ||
1686 'ORGANIZATION_id ' ||
1687 ') ' ||
1688 'select em.org_id ' ||
1689 ', em.transaction_id ' ||
1690 ', ld.transaction_component_type ' ||
1691 ', ld.transaction_value * '||fnd_number.NUMBER_TO_CANONICAL(v_final_conv_rate) ||
1692 ', ''' || v_uom || '''' ||
1693 ', ld.transaction_date ' ||
1694 ', ld.source_combination_id ' ||
1695 ', em.emission_scope_lookup_code ' ||
1696 ', em.ghg_asset_id ' ||
1697 ', em.vendor_id ' ||
1698 ', em.inventory_item_id ' ||
1699 ', em.ghg_organization_id ' ||
1700 'from GHG_TRANSACTIONS_all em ' ||
1701 ', GHG_TRANSACTION_DETAILS_all ld ' ||
1702 'where em.transaction_id = ld.transaction_id ' ||
1703 'and ld.transaction_type = ''E''';
1704
1705 IF p_org_id IS NOT NULL THEN
1706 v_stmt :=v_stmt || ' AND em.org_id='|| p_org_id ||'' ;
1707 END IF;
1708
1709
1710 EXECUTE IMMEDIATE v_stmt;
1711
1712 write_to_log('Inserting into GHG_TRANSACTION_DETAILS_F');
1713 v_stmt :=
1714 'insert INTO GHG_TRANSACTIONS_BY_MONTH_F@' || v_dblink ||
1715 '( ' ||
1716 'LEVEL3_ID, ' ||
1717 'SOURCE_APPLICATION_CODE, ' ||
1718 'TRANSACTION_VALUE, ' ||
1719 'TRANSACTION_CURRENCY_CODE, ' ||
1720 'TRANSACTION_CURRENCY_DESC, ' ||
1721 'emission_source_id, ' ||
1722 'EMISSION_SOURCE_USAGE_QUANTITY, ' ||
1723 'EMISSION_SOURCE_USAGE_UOM, ' ||
1724 'EMISSION_SCOPE_ID, ' ||
1725 'ENERGY_TYPE, ' ||
1726 'ENERGY_QUANTITY, ' ||
1727 'ENERGY_UOM, ' ||
1728 'CO2_E_QUANTITY, ' ||
1729 'CO2_E_UOM, ' ||
1730 'GHG_ASSET_ID, ' ||
1731 'SUPPLIER_ID, ' ||
1732 'ITEM_ID, ' ||
1733 'ACTUAL_OR_ESTIMATE, ' ||
1734 'LOCATION_STATE, ' ||
1735 'LOCATION_COUNTY, ' ||
1736 'GHG_ORGANIZATION_ID, ' ||
1737 'MEASUREMENT_CRITERIA_CODE, ' ||
1738 'measurement_criteria_desc) ' ||
1739 'select rp.level3_id, ' ||
1740 'source_application_code, ' ||
1741 'transaction_value, ' ||
1742 'transaction_currency_code, ' ||
1743 'transaction_currency_desc, ' ||
1744 'emission_source_id, ' ||
1745 'usage_qty, ' ||
1746 'EMISSION_SOURCE_USAGE_UOM, ' ||
1747 'EMISSION_SCOPE_ID, ' ||
1748 'energy_type, ' ||
1749 'energy_qty, ' ||
1750 'energy_uom, ' ||
1751 'co2e_qty, ' ||
1752 'CO2_E_UOM, ' ||
1753 'ghg_asset_id, ' ||
1754 'supplier_id, ' ||
1755 'ITEM_ID, ' ||
1756 'actual_or_estimate, ' ||
1757 'LOCATION_STATE, ' ||
1758 'location_county, ' ||
1759 'ORGANIZATION_ID, ' ||
1760 'measurement_criteria_code, ' ||
1761 'measurement_criteria_desc ' ||
1762 'from GHG_PERIODS_D@' || v_dblink || ' rp ' ||
1763 ', (select source_application_code, ' ||
1764 'trunc(transaction_date) transaction_date, ' ||
1765 'transaction_value, ' ||
1766 'transaction_currency_code, ' ||
1767 'transaction_currency_desc, ' ||
1768 'emission_source_id, ' ||
1769 'sum(nvl(emission_source_usage_quantity,0)) usage_qty, ' ||
1770 'EMISSION_SOURCE_USAGE_UOM, ' ||
1771 'EMISSION_SCOPE_ID, ' ||
1772 'energy_type, ' ||
1773 'sum(nvl(energy_quantity,0)) energy_qty, ' ||
1774 'energy_uom, ' ||
1775 'sum(nvl(co2_e_quantity,0)) co2e_qty, ' ||
1776 'CO2_E_UOM, ' ||
1777 'ghg_asset_id, ' ||
1778 'supplier_id, ' ||
1779 'ITEM_ID, ' ||
1780 'actual_or_estimate, ' ||
1781 'LOCATION_STATE, ' ||
1782 'location_county, ' ||
1783 'ORGANIZATION_ID, ' ||
1784 'measurement_criteria_code, ' ||
1785 'measurement_criteria_desc ' ||
1786 'from GHG_TRANSACTIONS_D@' || v_dblink || ' tr ' ||
1787 'group by source_application_code, trunc(transaction_date), ' ||
1788 'transaction_value, transaction_currency_code, ' ||
1789 'transaction_currency_desc, emission_source_id, ' ||
1790 'emission_source_usage_uom, emission_scope_id, energy_type, ' ||
1791 'energy_uom, co2_e_uom, ghg_asset_id, supplier_id, item_id, ' ||
1792 'actual_or_estimate, location_state, location_county, ' ||
1793 'ORGANIZATION_id, measurement_criteria_code, ' ||
1794 'measurement_criteria_desc) trs ' ||
1795 'where rp.actual_date = trunc(trs.transaction_date)';
1796
1797
1798
1799 EXECUTE IMMEDIATE v_stmt;
1800
1801 write_to_log('Inserting into GHG_ORGANIZATION_INTERESTS_D');
1802 v_stmt :=
1803 'INSERT INTO GHG_ORGANIZATION_INTERESTS_D@' || v_dblink ||
1804 ' ( SOURCE_APPLICATION_CODE, ' ||
1805 'ORGANIZATION_INTERESTS_ID, ' ||
1806 'INTERESTED_ORGANIZATION_ID, ' ||
1807 'ORGANIZATION_ID, ' ||
1808 'DATE_FROM, ' ||
1809 'DATE_TO, ' ||
1810 'INTEREST_TYPE_CODE, ' ||
1811 'INTEREST_TYPE_DESCRIPTION, ' ||
1812 'INTEREST_PERCENT, ' ||
1813 'ORGANIZATION_CODE, ' ||
1814 'ORGANIZATION_NAME, ' ||
1815 'CEO_NAME, ' ||
1816 'company_identifier, ' ||
1817 'ADDRESS_LINE_1, ' ||
1818 'ADDRESS_LINE_2, ' ||
1819 'ADDRESS_LINE_3, ' ||
1820 'ADDRESS_LINE_4, ' ||
1821 'ADDRESS_SUBURB, ' ||
1822 'ADDRESS_STATE, ' ||
1823 'ADDRESS_COUNTY, ' ||
1824 'address_country, ' ||
1825 'ADDRESS_POSTCODE) ' ||
1826 'select org_id, ' ||
1827 'a.INTERESTED_PARTY_ID, ' ||
1828 'b.ORGANIZATION_id, ' ||
1829 'a.ghg_organization_id, ' ||
1830 'a.start_date, ' ||
1831 'a.end_date, ' ||
1832 'a.operational_control, ' ||
1833 '(select meaning from fnd_lookup_values lv where lv.language='||concat(''''||v_language||'','''') || ' and lv.lookup_type = ''GHG_INTERESTED_TYPES'' and a.operational_control = lv.lookup_code), ' ||
1834 'a.equity_share , ' ||
1835 'b.ORGANIZATION_id, ' ||
1836 'a.CONTROLLING_ORGANIZATION_ID, ' ||
1837 'b.ceo_name, ' ||
1838 'b.company_identifier, ' ||
1839 'b.address_line_1, ' ||
1840 'b.address_line_2, ' ||
1841 'b.address_line_3, ' ||
1842 'b.address_line_4, ' ||
1843 'b.address_suburb, ' ||
1844 'b.address_state, ' ||
1845 'b.address_county, ' ||
1846 'b.address_country, ' ||
1847 'b.ADDRESS_POSTCODE ' ||
1848 'from GHG_INTERESTED_PARTIES_ALL a ' ||
1849 ', GHG_ORGANIZATIONS_D_TMP b ' ||
1850 'where org_id = source_application_code ' ||
1851 'and a.CONTROLLING_ORGANIZATION_ID = b.ORGANIZATION_code ' ||
1852 ' and a.operational_control <> ''OPC''';
1853 IF p_org_id IS NOT NULL THEN
1854 v_stmt :=v_stmt || ' AND a.org_id='|| p_org_id ||'' ;
1855 END IF;
1856
1857
1858
1859 EXECUTE IMMEDIATE v_stmt;
1860
1861
1862
1863 write_to_log('Inserting into GHG_ORGANIZATION_CONTROL_D');
1864 v_stmt :=
1865 'INSERT ' ||
1866 'INTO GHG_ORGANIZATION_CONTROL_D@' || v_dblink ||
1867 ' ( ' ||
1868 'SOURCE_APPLICATION_CODE, ' ||
1869 'ORGANIZATION_CONTROL_ID, ' ||
1870 'CONTROLLING_ORGANIZATION_ID, ' ||
1871 'ORGANIZATION_ID, ' ||
1872 'DATE_FROM, ' ||
1873 'DATE_TO, ' ||
1874 'CONTROL_TYPE_CODE, ' ||
1875 'CONTROL_TYPE_DESCRIPTION, ' ||
1876 'CONTROL_PERCENT, ' ||
1877 'ORGANIZATION_CODE, ' ||
1878 'ORGANIZATION_NAME, ' ||
1879 'CEO_NAME, ' ||
1880 'company_identifier, ' ||
1881 'ADDRESS_LINE_1, ' ||
1882 'ADDRESS_LINE_2, ' ||
1883 'ADDRESS_LINE_3, ' ||
1884 'ADDRESS_LINE_4, ' ||
1885 'ADDRESS_SUBURB, ' ||
1886 'ADDRESS_STATE, ' ||
1887 'ADDRESS_COUNTY, ' ||
1888 'ADDRESS_COUNTRY, ' ||
1889 'ADDRESS_POSTCODE ' ||
1890 ') ' ||
1891 'select org_id, ' ||
1892 'a.INTERESTED_PARTY_ID, ' ||
1893 'b.ORGANIZATION_id, ' ||
1894 'a.ghg_organization_id, ' ||
1895 'a.start_date, ' ||
1896 'a.end_date, ' ||
1897 'a.operational_control, ' ||
1898 '(select meaning from fnd_lookup_values lv where lv.language='||concat(''''||v_language||'','''') || ' and lv.lookup_type = ''GHG_INTERESTED_TYPES'' and a.operational_control = lv.lookup_code), ' ||
1899 'a.equity_share , ' ||
1900 'b.ORGANIZATION_id, ' ||
1901 'a.CONTROLLING_ORGANIZATION_ID, ' ||
1902 'b.ceo_name, ' ||
1903 'b.company_identifier, ' ||
1904 'b.address_line_1, ' ||
1905 'b.address_line_2, ' ||
1906 'b.address_line_3, ' ||
1907 'b.address_line_4, ' ||
1908 'b.address_suburb, ' ||
1909 'b.address_state, ' ||
1910 'b.address_county, ' ||
1911 'b.address_country, ' ||
1912 'b.ADDRESS_POSTCODE ' ||
1913 'from GHG_INTERESTED_PARTIES_ALL a ' ||
1914 ', GHG_ORGANIZATIONS_D_TMP b ' ||
1915 'where org_id = source_application_code ' ||
1916 'and a.CONTROLLING_ORGANIZATION_ID = b.ORGANIZATION_code ' ||
1917 'and a.operational_control = ''OPC''';
1918 IF p_org_id IS NOT NULL THEN
1919 v_stmt :=v_stmt || ' AND a.org_id='|| p_org_id ||'' ;
1920 END IF;
1921
1922 EXECUTE IMMEDIATE v_stmt;
1923
1924
1925 write_to_log('Inserting into GHG_KPI_TRANSACTIONS_F');
1926 v_stmt :=
1927 'INSERT INTO GHG_KPI_TRANSACTIONS_F@' || v_dblink || ' ' ||
1928 ' ( ' ||
1929 'SOURCE_APPLICATION_CODE, ' ||
1930 'KPI_ID, ' ||
1931 'KPI_DATE, ' ||
1932 'ORGANIZATION_ID, ' ||
1933 'NUMERATOR_UOM, ' ||
1934 'NUMERATOR_VALUE, ' ||
1935 'NUMERATOR_ACTION, ' ||
1936 'DENOMINATOR_UOM, ' ||
1937 'DENOMINATOR_VALUE, ' ||
1938 'DENOMINATOR_ACTION, ' ||
1939 'NUMERATOR_VALUE_TYPE, ' ||
1940 'DENOMINATOR_VALUE_TYPE, ' ||
1941 'TRANSACTION_TYPE ' ||
1942 ') ' ||
1943 'select kpd.org_id source_application_code, ' ||
1944 'kpd.kpi_id kpi_id, ' ||
1945 'trx.transaction_date kpi_date, ' ||
1946 'trx.ORGANIZATION_id ORGANIZATION_id, ' ||
1947 'decode( kpd.num_type, ' ||
1948 '''USAGE'', umn.STANDARD_UOM, ' ||
1949 '''ENERGY'', trx.energy_uom, ' ||
1950 '''EMISSIONS'', trx.co2_e_uom, ' ||
1951 '''VALUE'', trx.transaction_currency_code, '''') numerator_uom, ' ||
1952 'decode( kpd.num_type, ' ||
1953 '''USAGE'', nvl(trx.emission_source_usage_quantity,0), ' ||
1954 '''ENERGY'', nvl(trx.energy_quantity,0), ' ||
1955 '''EMISSIONS'', nvl(trx.co2_e_quantity,0), ' ||
1956 '''VALUE'', nvl(trx.transaction_value,0),0) numerator_value, ' ||
1957 'kpd.num_function numerator_action, ' ||
1958 'decode( kpd.den_type, ' ||
1959 '''USAGE'', umd.STANDARD_UOM, ' ||
1960 '''ENERGY'', trx.energy_uom, ' ||
1961 '''EMISSIONS'', trx.co2_e_uom, ' ||
1962 '''VALUE'', trx.transaction_currency_code, '''') denominator_uom, ' ||
1963 '0 denominator_value, ' ||
1964 'kpd.den_function denominator_action, ' ||
1965 'initcap(kpd.num_type) numerator_value_type, ' ||
1966 'initcap(kpd.den_type) denominator_value_type, ' ||
1967 ' (select meaning from fnd_lookup_values lv where lv.language='||concat(''''||v_language||'','''') || ' and lv.lookup_type = ''GHG_BI_TRANSACTION_TYPE'' and lv.lookup_code=''ACT'') transaction_type ' ||
1968 'from GHG_KPI_DEFINITIONS_all kpd ' ||
1969 ', GHG_KPI_COMPONENTS kpc ' ||
1970 ', GHG_SOURCES_D@' || v_dblink || ' src ' ||
1971 ', GHG_TRANSACTIONS_D@' || v_dblink || ' trx ' ||
1972 ', GHG_UOM_CLASSES_all umn ' ||
1973 ', GHG_UOM_CLASSES_all umd ' ||
1974 'where kpd.kpi_id = kpc.kpi_id ' ||
1975 'and kpc.source_id = src.source_info_code_1 ' ||
1976 'and kpc.COMPONENT_TYPE = ''N'' ' ||
1977 'and trx.emission_source_id = src.source_id ' ||
1978 ' and kpd.org_id='|| p_org_id ||'' ||
1979 ' and umn.ghg_uom_class_code = kpd.num_uom_class_code ' ||
1980 'and umd.ghg_uom_class_code = kpd.den_uom_class_code ' ||
1981 'union all ' ||
1982 'select kpd.org_id source_application_code, ' ||
1983 'kpd.kpi_id kpi_id, ' ||
1984 'trx.transaction_date kpi_date, ' ||
1985 'trx.ORGANIZATION_id ORGANIZATION_id, ' ||
1986 'decode( kpd.num_type, ' ||
1987 '''USAGE'', umn.STANDARD_UOM, ' ||
1988 '''ENERGY'', trx.energy_uom, ' ||
1989 '''EMISSIONS'', trx.co2_e_uom, ' ||
1990 '''VALUE'', trx.transaction_currency_code, '''') numerator_uom, ' ||
1991 '0 numerator_value, ' ||
1992 'kpd.num_function numerator_action, ' ||
1993 'decode( kpd.den_type, ' ||
1994 '''USAGE'', umd.STANDARD_UOM, ' ||
1995 '''ENERGY'', trx.energy_uom, ' ||
1996 '''EMISSIONS'', trx.co2_e_uom, ' ||
1997 '''VALUE'', trx.transaction_currency_code, '''') denominator_uom, ' ||
1998 'decode( kpd.den_type, ' ||
1999 '''USAGE'', nvl(trx.emission_source_usage_quantity,0), ' ||
2000 '''ENERGY'', nvl(trx.energy_quantity,0), ' ||
2001 '''EMISSIONS'', nvl(trx.co2_e_quantity,0), ' ||
2002 '''VALUE'', nvl(trx.transaction_value,0),0) denominator_value, ' ||
2003 'kpd.den_function denominator_action, ' ||
2004 'initcap(kpd.num_type) numerator_value_type, ' ||
2005 'initcap(kpd.den_type) denominator_value_type, ' ||
2006 '''Actuals'' transaction_type ' ||
2007 'from GHG_KPI_DEFINITIONS_all kpd ' ||
2008 ', GHG_KPI_COMPONENTS kpc ' ||
2009 ', GHG_SOURCES_D@' || v_dblink || ' src ' ||
2010 ', GHG_TRANSACTIONS_D@' || v_dblink || ' trx ' ||
2011 ', GHG_UOM_CLASSES_all umn ' ||
2012 ', GHG_UOM_CLASSES_all umd ' ||
2013 'where kpd.kpi_id = kpc.kpi_id ' ||
2014 'and kpc.source_id = src.source_info_code_1 ' ||
2015 'and kpc.COMPONENT_TYPE = ''D'' ' ||
2016 'and trx.emission_source_id = src.source_id ' ||
2017 'and kpd.org_id='|| p_org_id ||'' ||
2018 ' and umn.ghg_uom_class_code = kpd.num_uom_class_code ' ||
2019 'and umd.ghg_uom_class_code = kpd.den_uom_class_code';
2020
2021 EXECUTE IMMEDIATE v_stmt;
2022
2023
2024
2025 write_to_log('Inserting into GHG_KPI_DEFINITIONS_D');
2026 v_stmt :=
2027 'INSERT ' ||
2028 'INTO GHG_KPI_DEFINITIONS_D@' || v_dblink ||
2029 ' ( ' ||
2030 'KPI_ID, ' ||
2031 'KPI_CODE, ' ||
2032 'KPI_NAME, ' ||
2033 'SOURCE_APPLICATION_CODE ' ||
2034 ') ' ||
2035 'SELECT KPI_ID, ' ||
2036 'KPI_CODE, ' ||
2037 'DESCRIPTION, ' ||
2038 'ORG_ID ' ||
2039 'FROM GHG_KPI_DEFINITIONS_all WHERE org_id='|| p_org_id ||'';
2040
2041 EXECUTE IMMEDIATE v_stmt;
2042
2043
2044
2045 EXCEPTION
2046
2047 WHEN OTHERS THEN
2048 write_to_log(sqlerrm);
2049 errbuf := 'SQLERROR :'||sqlerrm;
2050 retcode := 2;
2051
2052 END;
2053
2054
2055 PROCEDURE SEED_FORMULA_TO_ORGS (errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY NUMBER) IS
2056
2057 /* Cursor to get all organizations from EBS for which formula is not defined in GHG*/
2058 Cursor orgs_master_list IS
2059 SELECT hr.organization_id ORG_ID
2060 FROM hr_operating_units hr,
2061 gl_sets_of_books gsob,
2062 financials_system_params_all fsp
2063 WHERE hr.set_of_books_id = gsob.set_of_books_id
2064 AND mo_global.check_access(hr.organization_id) = 'Y'
2065 AND hr.organization_id NOT IN
2066 ( SELECT DISTINCT ORG_ID FROM GHG_EMISSION_FORMULAS_ALL)
2067 AND hr.organization_id = fsp.org_id;
2068
2069 /* Cursor to get the formulas for ORG_ID -1 */
2070 Cursor seed_formula IS
2071 SELECT FORMULA_ID,
2072 DESCRIPTION,
2073 FORMULA_CONTENT,
2074 SECONDARY_FORMULA_ID
2075 FROM GHG_EMISSION_FORMULAS_ALL
2076 WHERE ORG_ID = -1;
2077
2078 /* Variables */
2079 v_seed_row_count NUMBER := 0;
2080
2081
2082 /* Procedure for writing to Concurrent program log file */
2083 Procedure write_to_log(msg varchar2) is
2084 begin
2085 fnd_file.put_line(fnd_file.log, msg);
2086 end write_to_log;
2087
2088
2089 BEGIN
2090
2091 /* Check if the formulas have been seeded for ORG_ID -1. If not then raise an exception */
2092 write_to_log('Checking if formula exists for ORG_ID -1');
2093 SELECT COUNT(*)
2094 INTO v_seed_row_count
2095 FROM GHG_EMISSION_FORMULAS_ALL
2096 WHERE ORG_ID = -1;
2097
2098 IF v_seed_row_count = 0 THEN --Formula not seeded for ORG_ID -1
2099 write_to_log('No formula found for ORG_ID -1');
2100 RAISE NO_DATA_FOUND;
2101 END IF;
2102
2103 /* Copy the formula for ORG_ID -1 into the master list of organizations and
2104 insert into GHG_EMISSION_FORMULAS_ALL table */
2105 write_to_log('Copy the formula for ORG_ID -1 into the master list of organizations');
2106 For orgs_rec IN orgs_master_list
2107 LOOP
2108 FOR seed_formula_rec in seed_formula
2109 LOOP
2110 write_to_log('Inserting into GHG_EMISSION_FORMULAS_ALL table');
2111 INSERT INTO GHG_EMISSION_FORMULAS_ALL ( FORMULA_ID,
2112 DESCRIPTION,
2113 FORMULA_CONTENT,
2114 ORG_ID,
2115 SECONDARY_FORMULA_ID)
2116 VALUES (seed_formula_rec.formula_id,
2117 seed_formula_rec.description,
2118 seed_formula_rec.formula_content,
2119 orgs_rec.ORG_ID,
2120 seed_formula_rec.secondary_formula_id);
2121
2122 END LOOP; /* End loop for formula */
2123
2124 END LOOP; /* End loop for Orgs */
2125
2126 EXCEPTION
2127 WHEN OTHERS THEN
2128 write_to_log(SQLERRM);
2129 errbuf := 'SQLERROR : ' || SQLERRM;
2130 retcode := 2;
2131
2132 END seed_formula_to_orgs; /* End of Procedure seed_formula_to_orgs */
2133
2134
2135
2136
2137
2138 END GHG_TRANSACTIONS_API_PKG;