[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO v_row_count
FROM fnd_lookup_values_vl
WHERE upper(lookup_type) = 'GHG_INTERNAL_TXN_TYPE'
AND TRUNC(SYSDATE) BETWEEN start_date_active
AND NVL(end_date_active,GHG_utilities_pkg.end_date)
AND lookup_code = p_batch_type;
SELECT count(*)
INTO v_row_count
FROM fnd_lookup_values_vl
WHERE upper(lookup_type) = 'GHG_TXN_SUB_TYPE'
AND TRUNC(SYSDATE) BETWEEN start_date_active
AND NVL(end_date_active,GHG_utilities_pkg.end_date)
AND lookup_code = p_batch_sub_type;
SELECT COUNT(*)
INTO v_row_count
FROM po_vendors pv
WHERE pv.enabled_flag = 'Y'
AND pv.vendor_id = nvl(p_supplier_id, -1)
AND EXISTS (SELECT 1
FROM po_vendor_sites
WHERE vendor_id = pv.vendor_id
AND TRUNC(NVL(inactive_date, sysdate + 1)) > TRUNC(sysdate)
AND pay_site_flag = 'Y');
SELECT COUNT(*)
INTO v_row_count
FROM po_vendor_sites
WHERE vendor_id = nvl(p_supplier_id, -1)
AND vendor_site_id = nvl(p_supplier_site_id, -1)
AND TRUNC(NVL(inactive_date, sysdate + 1)) > TRUNC(sysdate)
AND pay_site_flag = 'Y';
SELECT count(*)
INTO v_row_count
FROM fnd_lookup_values_vl
WHERE upper(lookup_type) = 'GHG_EMISSION_SCOPE'
AND TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active,GHG_utilities_pkg.end_date)
AND lookup_code = nvl(p_emission_scope, 'NA');
SELECT count(*)
INTO v_row_count
FROM fnd_lookup_values_vl
WHERE upper(lookup_type) = 'GHG_MEASUREMENT_CRITERIA'
AND TRUNC(SYSDATE) BETWEEN start_date_active
AND NVL(end_date_active,GHG_utilities_pkg.end_date)
AND lookup_code = nvl(p_m_criteria,'-1');
SELECT COUNT(*)
INTO v_row_count
FROM GHG_TRANSACTIONS_V txn
WHERE (p_emission_from_date between txn.transaction_date_from
and txn.transaction_date_to )
OR (p_emission_to_date between txn.transaction_date_from
and txn.transaction_date_to )
OR (txn.transaction_date_from between p_emission_from_date
and p_emission_to_date )
OR (txn.transaction_date_to between p_emission_from_date
and p_emission_to_date );
SELECT count(*)
INTO v_row_count
FROM fnd_lookup_values_vl
WHERE upper(lookup_type) = 'GHG_LOCATION'
AND TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active,GHG_utilities_pkg.end_date)
AND lookup_code = p_emission_location;
SELECT count(*)
INTO v_check
FROM fnd_lookup_values_vl
WHERE upper(lookup_type) = 'GHG_TXN_TYPE'
AND TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active,GHG_utilities_pkg.end_date)
AND lookup_code = p_batch_type;
SELECT set_of_books_id
INTO v_set_of_books_id
FROM ap_system_parameters;
SELECT transaction_batch_id,
status,
batch_type
INTO v_emission_batch_id
, v_batch_status
, v_batch_type
FROM GHG_TRANSACTION_BATCHES
WHERE transaction_batch_number = p_batch_number;
SELECT INVENTORY_ITEM_ID
, SOURCE_ID
, source_name
, LOCATION_CODE
, EMISSION_SCOPE_LOOKUP_CODE
INTO v_item_id
, v_emission_source_id
, v_emission_source_name
, v_emission_location
, v_emission_scope
FROM GHG_ITEM_FACTORS_V
WHERE TRANSPORT_TYPE_LOOKUP_CODE = 'DEFAULT'
AND INVENTORY_ITEM = p_item_number;
SELECT distinct gsi.INVENTORY_ITEM_ID
, gsi.SOURCE_ID
, s.source_name
INTO v_item_id
, v_emission_source_id
, v_emission_source_name
FROM GHG_SUPPLIER_ITEMS gsi,
mtl_system_items_kfv msi,
ghg_sources s
WHERE msi.CONCATENATED_SEGMENTS = p_item_number
AND msi.INVENTORY_ITEM_ID = gsi.INVENTORY_ITEM_ID
and gsi.SOURCE_ID = s.SOURCE_ID;
SELECT ghg_asset_id
INTO v_asset_id
FROM GHG_ASSETS_V
where asset_number = p_ghgas_asset
and trunc(p_emission_from_date) >= start_date
and p_emission_to_date <= nvl(end_date,GHG_utilities_pkg.end_date);
SELECT source_id
into v_emission_source_id
from GHG_SOURCES
where source_name = nvl(p_emission_source_name, v_emission_source_name);
SELECT count(*)
INTO v_check
FROM fnd_lookup_values_vl
WHERE upper(lookup_type) = 'GHG_EMISSION_SCOPE'
AND TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active,GHG_utilities_pkg.end_date)
AND lookup_code = nvl(p_emission_scope,v_emission_scope);
SELECT ghg_organization_id
INTO v_facility_id
FROM GHG_ORGANIZATIONS_V
WHERE ghg_organization_code = p_facility
AND allow_transactions = 'Y';
SELECT ghg_organization_id
INTO v_facility_id
FROM GHG_ORGANIZATIONS_V
WHERE ghg_organization_code = p_facility
AND p_emission_from_date BETWEEN start_date AND nvl(end_date, GHG_utilities_pkg.end_date)
AND p_emission_to_date BETWEEN start_date AND nvl(end_date, GHG_utilities_pkg.end_date)
AND allow_transactions = 'Y';
select decode(upper(fnd.application_column_name),
'REGION_1', hr.region_1,
'REGION_2', hr.region_2,
'REGION_2', hr.region_3,
'COUNTRY', hr.country, --14119516 (sasuren)
'ALL') STATE
into v_emission_location
from hr_locations hr
, FND_DESCR_FLEX_COL_USAGE_VL fnd
WHERE fnd.APPLICATION_ID =800
AND fnd.DESCRIPTIVE_FLEXFIELD_NAME='Address Location'
AND fnd.DESCRIPTIVE_FLEX_CONTEXT_CODE = hr.style
AND exists (select 1
from GHG_organizations fac
where fac.ghg_organization_id = v_facility_id
and fac.location_id = hr.location_id)
AND upper(end_user_column_name) = fnd_profile.value('GHG_FACTOR_BREAK_REGION');
SELECT count(*)
INTO v_check
FROM fnd_lookup_values_vl
WHERE upper(lookup_type) = 'GHG_LOCATION'
AND TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active,GHG_utilities_pkg.end_date)
AND lookup_code = v_emission_location;
SELECT count(*)
INTO v_check
FROM GHG_SOURCES xnes,
mtl_units_of_measure_tl mum1,
mtl_units_of_measure_tl mum2
WHERE mum1.unit_of_measure = xnes.unit_of_measure
AND mum2.uom_class = mum1.uom_class
AND NVL(mum2.disable_date, sysdate + 1) > sysdate
AND source_id = v_emission_source_id
AND mum2.unit_of_measure = p_emission_uom;
SELECT GHG_TRANSACTIONS_S.NEXTVAL
into v_emission_id
FROM dual;
SELECT NVL(MAX(transaction_line_number),0)+1
INTO v_max_line_no
FROM GHG_TRANSACTIONS
WHERE transaction_batch_id = v_emission_batch_id;
SELECT GHG_TRANSACTION_BATCHES_s.NEXTVAL
INTO v_emission_batch_id
FROM DUAL;
GHG_TRANSACTION_BATCHES_PKG.insert_row (x_rowid => v_batch_rowid,
x_transaction_batch_id => v_emission_batch_id,
x_transaction_batch_number => p_batch_number,
x_batch_type => p_batch_type,
x_batch_sub_type => p_batch_sub_type,
x_vendor_id => v_supplier_id,
x_vendor_site_id => v_supplier_site_id,
x_description => p_batch_description||'-'||p_emission_from_date||'-'||p_emission_to_date,
x_status => v_status,
x_reversal_date => NULL,
x_reversed_transact_batch_id => NULL,
x_org_id => p_org_id,
x_set_of_books_id => v_set_of_books_id,
x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
x_creation_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
x_last_update_date => SYSDATE,
x_last_update_login => -1,
x_called_from_api => 'Y');
GHG_TRANSACTIONS_PKG.insert_row (x_rowid => v_batch_rowid,
x_txn_type_lookup_code => p_batch_type,
x_emission_id => v_emission_id,
x_emission_batch_id => v_emission_batch_id,
x_facility_id => v_facility_id,
x_emission_source_id => v_emission_source_id,
x_scope => nvl(p_emission_scope,v_emission_scope),
x_location => v_emission_location,
--x_location => nvl(p_emission_location,v_emission_location),
--Bug 12546495: ssurendr. Use location from organization
x_emission_type_lookup_code => 'C',
x_usage_quantity => p_emission_usage,
x_unit_of_measure => p_emission_uom,
x_uom_conversion => v_uom_conversion,
x_emission_date_from => p_emission_from_date,
x_emission_date_to => p_emission_to_date,
x_description => p_emission_description,
x_org_id => p_org_id,
x_set_of_books_id => v_set_of_books_id,
x_emission_line_number => v_max_line_no,
x_created_by => nvl(fnd_profile.value('USER_ID'),-1),
x_creation_date => SYSDATE,
x_last_updated_by => nvl(fnd_profile.value('USER_ID'),-1),
x_last_update_date => SYSDATE,
x_last_update_login => -1,
x_commit_flag => p_commit_flag,
-- x_emission_rate_id => -1,
-- x_emission_factor_id => NULL,
x_energy_quantity => NULL,
x_emission_quantity => NULL,
x_invoice_id => p_invoice_id,
x_distribution_line_number => p_invoice_line_num,
x_invoice_line_number => p_invoice_dist_num,
x_vendor_id => v_supplier_id,
x_vendor_site_id => v_supplier_site_id,
x_inventory_item_id => v_item_id,
x_ghg_asset_id => v_asset_id,
x_measurement_criteria => p_m_criteria ,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_attribute_category => NULL,
x_called_from_api => 'Y');
SELECT *
FROM GHG_TRANSACTIONS_all em
WHERE org_id = p_org_id
AND nvl(em.vendor_id,-1) = nvl(p_supplier_id, nvl(em.vendor_id,-1))
AND nvl(em.invoice_id,-1) = nvl(p_invoice_id, nvl(em.invoice_id,-1) )
AND em.ghg_organization_id = nvl(p_facility_id, em.ghg_organization_id)
AND em.transaction_source_id = nvl(p_source_id, em.transaction_source_id)
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)
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))
UNION ALL
SELECT *
FROM GHG_TRANSACTIONS_all em
WHERE org_id = p_org_id
AND nvl(em.vendor_id,-1) = nvl(p_supplier_id, nvl(em.vendor_id,-1))
AND nvl(em.invoice_id,-1) = nvl(p_invoice_id, nvl(em.invoice_id,-1) )
AND em.ghg_organization_id = nvl(p_facility_id, em.ghg_organization_id)
AND em.transaction_source_id = nvl(p_source_id, em.transaction_source_id)
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)
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))
AND EXISTS (SELECT 1
FROM ap_invoices_all ap
WHERE ap.invoice_id = em.invoice_id
AND ap.vendor_id = p_supplier_id);
v_smt varchar2(1000) := 'delete from ';
SELECT USERENV('LANG') INTO v_language from dual;
write_to_log('Inserting into GHG_ASSETS_D');
'INSERT INTO GHG_ASSETS_D@' || v_dblink ||
' ( ' ||
'SOURCE_APPLICATION_CODE, ' ||
'GHG_ASSET_ID, ' ||
'ASSET_IDENTIFIER1, ' ||
'ASSET_IDENTIFIER2, ' ||
'ASSET_DESCRIPTION_LINE1, ' ||
'ASSET_DESCRIPTION_LINE2, ' ||
'ASSET_TYPE, ' ||
'TRANSPORT_TYPE ' ||
') ' ||
'SELECT ' || p_org_id || ', ' ||
'GHG_ASSET_ID, ' ||
'ASSET_NUMBER, ' ||
'ASSET_ID, ' ||
'DESCRIPTION, ' ||
'''Oracle eBs Item ID'', ' ||
'ASSET_TYPE_LOOKUP_CODE, ' ||
'TRANSPORT_TYPE_LOOKUP_CODE ' ||
'FROM GHG_ASSETS';
write_to_log('Inserting into GHG_EMISSION_SCOPES_D');
'INSERT INTO GHG_EMISSION_SCOPES_D@' || v_dblink ||
' ( ' ||
'SOURCE_APPLICATION_CODE, ' ||
'EMISSION_SCOPE_ID, ' ||
'EMISSION_SCOPE_CODE, ' ||
'EMISSION_SCOPE_DESCRIPTION ' ||
') ' ||
'SELECT ' || p_org_id || ', ' ||
'LOOKUP_CODE, ' ||
'LOOKUP_CODE, ' ||
'DESCRIPTION ' ||
'FROM FND_LOOKUP_VALUES' ||
' WHERE LOOKUP_TYPE = ''GHG_EMISSION_SCOPE''' || ' AND LANGUAGE='||concat(''''||v_language||'','''');
write_to_log('Inserting into GHG_SOURCES_D');
'INSERT INTO GHG_SOURCES_D@' || v_dblink ||
' ( ' ||
'SOURCE_APPLICATION_CODE, ' ||
'SOURCE_ID, ' ||
'SOURCE_CODE, ' ||
'SOURCE_DESCRIPTION, ' ||
'SOURCE_INFO_CODE_1, ' ||
'SOURCE_INFO_DESC_1, ' ||
'GRANDPARENT_SOURCE_CODE, ' ||
'GRANDPARENT_SOURCE_DESC, ' ||
'PARENT_SOURCE_CODE, ' ||
'PARENT_SOURCE_DESC, ' ||
'SOURCE_TYPE_CODE, ' ||
'SOURCE_TYPE_DESC ' ||
') ' ||
'select src.org_id, ' ||
'fac.SOURCE_COMBINATION_ID, ' ||
'fac.SOURCE_COMBINATION_CODE, ' ||
'nvl(fac.SOURCE_COMBINATION_DESCRIPTION, fac.SOURCE_COMBINATION_CODE), ' ||
'src.source_id, ' ||
'src.source_name, ' ||
'rcc.segment1,
(SELECT fltl.description
FROM FND_ID_FLEX_SEGMENTS idf ,FND_ID_FLEX_SEGMENTS_TL tl
, fnd_flex_values val , fnd_flex_values_tl fltl
WHERE idf.ID_FLEX_CODE = '||'''GHG#''' ||
' AND idf.id_flex_num = rcc.chart_of_accounts_id
and idf.flex_value_set_id = val.flex_value_set_id
and idf.ID_FLEX_CODE = tl.ID_FLEX_CODE
and idf.application_column_name = tl.APPLICATION_COLUMN_NAME
AND idf.id_flex_num = tl.id_flex_num
and val.flex_value_id = fltl.flex_value_id
and val.flex_value = rcc.segment1 and tl.language= '||concat(''''||v_language||'','''')
|| ' and fltl.language= '||concat(''''||v_language||'','''')
|| ' and idf.application_column_name = '||'''SEGMENT1''' ||') segment1_desc,
rcc.segment2,
(SELECT fltl.description
FROM FND_ID_FLEX_SEGMENTS idf ,FND_ID_FLEX_SEGMENTS_TL tl
, fnd_flex_values val , fnd_flex_values_tl fltl
WHERE idf.ID_FLEX_CODE = '||'''GHG#''' ||
' AND idf.id_flex_num = rcc.chart_of_accounts_id
and idf.flex_value_set_id = val.flex_value_set_id
and idf.ID_FLEX_CODE = tl.ID_FLEX_CODE
and idf.application_column_name = tl.APPLICATION_COLUMN_NAME
AND idf.id_flex_num = tl.id_flex_num
and val.flex_value_id = fltl.flex_value_id
and val.flex_value = rcc.segment2 and tl.language= '||concat(''''||v_language||'','''')
|| ' and fltl.language= '||concat(''''||v_language||'','''')
|| ' and idf.application_column_name = '||'''SEGMENT2''' ||') segment2_desc,
rcc.segment3,
(SELECT fltl.description
FROM FND_ID_FLEX_SEGMENTS idf ,FND_ID_FLEX_SEGMENTS_TL tl
, fnd_flex_values val , fnd_flex_values_tl fltl
WHERE idf.ID_FLEX_CODE = '||'''GHG#''' ||
' AND idf.id_flex_num = rcc.chart_of_accounts_id
and idf.flex_value_set_id = val.flex_value_set_id
and idf.ID_FLEX_CODE = tl.ID_FLEX_CODE
and idf.application_column_name = tl.APPLICATION_COLUMN_NAME
AND idf.id_flex_num = tl.id_flex_num
and val.flex_value_id = fltl.flex_value_id
and val.flex_value = rcc.segment3 and tl.language= '||concat(''''||v_language||'','''')
|| ' and fltl.language= '||concat(''''||v_language||'','''')
|| ' and idf.application_column_name = '||'''SEGMENT3''' ||') segment3_desc '||
'from GHG_SOURCES_all src ' ||
', GHG_SOURCE_COMBINATIONS_ALL fac ' ||
', GHG_REPORT_COMBINATIONS rcc ' ||
'where fac.source_id = src.source_id ' ||
'and fac.code_combination_id = rcc.code_combination_id (+)';
write_to_log('Inserting into GHG_ITEMS_D');
'INSERT INTO GHG_ITEMS_D@' || v_dblink ||
' ( ' ||
'SOURCE_APPLICATION_CODE, ' ||
'ITEM_ID, ' ||
'ITEM_CODE, ' ||
'ITEM_DESCRIPTION ' ||
') ' ||
'SELECT ghgi.ORG_ID, ' ||
'ghgi.INVENTORY_ITEM_ID, ' ||
'i.SEGMENT1, ' ||
'i.DESCRIPTION ' ||
'FROM GHG_SUPPLIER_ITEMS_ALL GHGI, ' ||
'MTL_SYSTEM_ITEMS I ' ||
'WHERE i.INVENTORY_ITEM_ID = ghgi.INVENTORY_ITEM_ID ' ||
'AND i.organization_id = ghgi.ORG_ID';
write_to_log('Inserting into GHG_SUPPLIERS_D');
'INSERT INTO GHG_SUPPLIERS_D@' || v_dblink ||
' ( ' ||
'SOURCE_APPLICATION_CODE, ' ||
'SUPPLIER_ID, ' ||
'SUPPLIER_NAME ' ||
') ' ||
'SELECT distinct ' ||
'ghgs.ORG_ID, ' ||
'ghgs.SUPPLIER_ID, ' ||
'sup.vendor_name ' ||
'FROM GHG_SUPPLIER_ITEMS_ALL ghgs ' ||
', AP_SUPPLIERS sup ' ||
'WHERE ghgs.supplier_id = sup.vendor_id';
write_to_log('Inserting into GHG_ORGANIZATION_HIERARCHIES_D');
'INSERT INTO GHG_ORGANIZATION_HIERARCHIES_D@' || v_dblink ||
'( ' ||
'SOURCE_APPLICATION_CODE, ' ||
'ORGANIZATION_HIERARCHY_ID, ' ||
'ORGANIZATION_HIERARCHY_CODE, ' ||
'ORGANIZATION_HIERARCHY_NAME, ' ||
'ORGANIZATION_HIERARCHY_TYPE, ' ||
'DATE_FROM, ' ||
'DATE_TO ' ||
') ' ||
'SELECT ' || p_org_id || ', ' ||
'v.hierarchy_version_id, ' ||
'v.hierarchy_version_id, ' ||
'h.name, ' ||
'''MANAGEMENT'', ' ||
'v.date_from, ' ||
'v.date_to ' ||
'FROM PER_GEN_HIERARCHY h ' ||
', PER_GEN_HIERARCHY_VERSIONS v ' ||
'WHERE h.hierarchy_id = v.hierarchy_id ' ||
'and h.type = ''GHG_ORGANIZATION_STRUCTURE''' ||
'AND v.status = ''A''';
write_to_log('Inserting into GHG_ORG_HIERARCHY_STRUCTURES_D');
select v.hierarchy_version_id
from PER_GEN_HIERARCHY h,
PER_GEN_HIERARCHY_VERSIONS v
where h.hierarchy_id = v.hierarchy_id
and h.type = 'GHG_ORGANIZATION_STRUCTURE'
and v.status = 'A';
select *
from (
select level lvl, parent_hierarchy_node_id, hierarchy_node_id, node_type, entity_id
from (select * from per_gen_hierarchy_nodes where hierarchy_version_id = p_version_id )
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)
connect by prior hierarchy_node_id = parent_hierarchy_node_id )
order by lvl desc ;
select *
from (
select level lvl, parent_hierarchy_node_id, hierarchy_node_id, node_type, entity_id
from (select * from per_gen_hierarchy_nodes where hierarchy_version_id = p_version_id)
start with hierarchy_node_id = p_start_point
connect by prior parent_hierarchy_node_id = hierarchy_node_id )
order by lvl;
vl_text := 'insert into GHG_ORG_HIERARCHY_STRUCTURES_D@' || v_dblink || ' VALUES (' || p_org_id || ', ' || p_version_id || ',';
'delete from GHG_ORG_HIERARCHY_STRUCTURES_D@' || v_dblink || ' str ' ||
'where not exists ( select 1 from GHG_ORGANIZATIONS_ALL fac ' ||
'where allow_transactions = ''Y''' ||
' and str.ORGANIZATION_id = fac.ghg_organization_id)';
write_to_log('Inserting into GHG_ORGANIZATIONS_D_TMP');
INSERT INTO GHG_ORGANIZATIONS_D_TMP
( SOURCE_APPLICATION_CODE,
ORGANIZATION_ID,
ORGANIZATION_EXTERNAL_ID,
ORGANIZATION_CODE,
ORGANIZATION_NAME,
CEO_NAME,
COMPANY_IDENTIFIER,
DATE_EFFECTIVE_FROM,
DATE_EFFECTIVE_TO,
SIC_GROUP_CODE,
SIC_GROUP_DESC,
SIC_DIVISION_CODE,
SIC_DIVISION_DESC,
ORGANIZATION_SIZE,
ORGANIZATION_TYPE,
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
ADDRESS_LINE_4,
ADDRESS_SUBURB,
ADDRESS_STATE,
ADDRESS_COUNTRY,
ADDRESS_POSTCODE,
ADDRESS_LATITUDE,
address_longitude,
ADDRESS_LOCATION)
select fac.org_id,
fac.ghg_organization_id,
fac.ghg_organization_id,
fac.GHG_ORGANIZATION_CODE,
fac.description,
fac.ceo_name,
fac.COMPANY_IDENTIFIER,
fac.start_date,
fac.end_date,
sic.sic_code,
anz.meaning,
sic.division_code,
div.meaning,
fac.ORGANIZATION_SIZE_LOOKUP_CODE,
fac.ORGANIZATION_TYPE,
hr.address_line_1,
hr.address_line_2,
hr.address_line_3,
null,
hr.town_or_city,
nvl(hr.region_2, hr.region_1),
tr.territory_short_name,
postal_code,
fac.latitude,
fac.longitude,
location_code
from GHG_ORGANIZATIONS_ALL fac
, GHG_ORG_SIC_MAPPINGS_ALL sic
, fnd_lookup_values anz
, fnd_lookup_values div
, hr_locations hr
, fnd_territories_vl tr
where fac.ghg_organization_id = sic.ghg_organization_id(+)
and anz.lookup_type(+) = 'GHG_SIC_CODES'
and div.lookup_type(+) = 'GHG_DIVISION_CODES'
and sic.sic_code = anz.lookup_code(+)
and sic.division_code = div.lookup_code(+)
and hr.location_id = fac.location_id
and hr.country = tr.territory_code
and anz.language(+)=v_language
and div.language(+)=v_language
and fac.org_id=p_org_id;
write_to_log('Inserting into GHG_ORGANIZATIONS_D');
'INSERT INTO GHG_ORGANIZATIONS_D@' || v_dblink ||
'( SOURCE_APPLICATION_CODE, ' ||
'ORGANIZATION_ID, ' ||
'ORGANIZATION_EXTERNAL_ID, ' ||
'ORGANIZATION_CODE, ' ||
'ORGANIZATION_NAME, ' ||
'CEO_NAME, ' ||
'COMPANY_IDENTIFIER, ' ||
'DATE_EFFECTIVE_FROM, ' ||
'DATE_EFFECTIVE_TO, ' ||
'SIC_GROUP_CODE, ' ||
'SIC_GROUP_DESC, ' ||
'SIC_DIVISION_CODE, ' ||
'SIC_DIVISION_DESC, ' ||
'ORGANIZATION_SIZE, ' ||
'ORGANIZATION_TYPE, ' ||
'ADDRESS_LINE_1, ' ||
'ADDRESS_LINE_2, ' ||
'ADDRESS_LINE_3, ' ||
'ADDRESS_LINE_4, ' ||
'ADDRESS_SUBURB, ' ||
'ADDRESS_STATE, ' ||
'ADDRESS_COUNTRY, ' ||
'ADDRESS_POSTCODE, ' ||
'ADDRESS_LATITUDE, ' ||
'address_longitude, ' ||
'ADDRESS_LOCATION) ' ||
'select SOURCE_APPLICATION_CODE, ' ||
'ORGANIZATION_ID, ' ||
'ORGANIZATION_EXTERNAL_ID, ' ||
'ORGANIZATION_CODE, ' ||
'ORGANIZATION_NAME, ' ||
'CEO_NAME, ' ||
'COMPANY_IDENTIFIER, ' ||
'DATE_EFFECTIVE_FROM, ' ||
'DATE_EFFECTIVE_TO, ' ||
'SIC_GROUP_CODE, ' ||
'SIC_GROUP_DESC, ' ||
'SIC_DIVISION_CODE, ' ||
'SIC_DIVISION_DESC, ' ||
'ORGANIZATION_SIZE, ' ||
'ORGANIZATION_TYPE, ' ||
'ADDRESS_LINE_1, ' ||
'ADDRESS_LINE_2, ' ||
'ADDRESS_LINE_3, ' ||
'ADDRESS_LINE_4, ' ||
'ADDRESS_SUBURB, ' ||
'ADDRESS_STATE, ' ||
'ADDRESS_COUNTRY, ' ||
'ADDRESS_POSTCODE, ' ||
'ADDRESS_LATITUDE, ' ||
'address_longitude, ' ||
'ADDRESS_LOCATION ' ||
'from GHG_ORGANIZATIONS_D_TMP WHERE SOURCE_APPLICATION_CODE='|| p_org_id ||'';
write_to_log('Inserting into GHG_ORG_HIERARCHY_STRUCTURES_D');
'insert INTO GHG_ORG_HIERARCHY_STRUCTURES_D@' || v_dblink ||
' ( ' ||
'SOURCE_APPLICATION_CODE, ' ||
'ORGANIZATION_HIERARCHY_ID, ' ||
'ORGANIZATION_ID, ' ||
'LEVEL_01_ORGANIZATION_ID, ' ||
'LEVEL_02_ORGANIZATION_ID, ' ||
'LEVEL_03_ORGANIZATION_ID, ' ||
'LEVEL_04_ORGANIZATION_ID, ' ||
'LEVEL_05_ORGANIZATION_ID, ' ||
'LEVEL_06_ORGANIZATION_ID, ' ||
'LEVEL_07_ORGANIZATION_ID, ' ||
'LEVEL_08_ORGANIZATION_ID, ' ||
'LEVEL_09_ORGANIZATION_ID, ' ||
'LEVEL_10_ORGANIZATION_ID, ' ||
'LEVEL_11_ORGANIZATION_ID, ' ||
'LEVEL_12_ORGANIZATION_ID, ' ||
'ORPHAN_FLAG ' ||
') ' ||
'select source_application_code, ' ||
'0, ' ||
'ORGANIZATION_id, ' ||
'ORGANIZATION_id, ' ||
'ORGANIZATION_id, ' ||
'ORGANIZATION_id, ' ||
'ORGANIZATION_id, ' ||
'ORGANIZATION_id, ' ||
'ORGANIZATION_id, ' ||
'ORGANIZATION_id, ' ||
'ORGANIZATION_id, ' ||
'ORGANIZATION_id, ' ||
'ORGANIZATION_id, ' ||
'ORGANIZATION_id, ' ||
'ORGANIZATION_id, ' ||
'''N'' ' ||
'from GHG_ORGANIZATIONS_D_TMP org ' ||
'where not exists (select 1 ' ||
'from GHG_ORG_HIERARCHY_STRUCTURES_D@' || v_dblink || ' st ' ||
'where org.ORGANIZATION_id = st.ORGANIZATION_id) AND SOURCE_APPLICATION_CODE='|| p_org_id ||'';
select uom_class into v_uom_class from mtl_units_of_measure_tl where UOM_CODE=v_uom and language=userenv('LANG');
SELECT muc.conversion_rate
INTO v_conv_rate
FROM mtl_units_of_measure_tl mum1,
mtl_uom_conversions muc
WHERE mum1.uom_code = v_uom
AND muc.unit_of_measure = mum1.unit_of_measure AND mum1.language=USERENV('LANG') AND muc.INVENTORY_ITEM_ID=0;
SELECT muc.conversion_rate
INTO v_emission_conv_rate
FROM mtl_units_of_measure_tl mum1,
mtl_uom_conversions muc
WHERE mum1.uom_code = v_emission_uom
AND muc.unit_of_measure = mum1.unit_of_measure and mum1.language=USERENV('LANG') AND muc.INVENTORY_ITEM_ID=0;
write_to_log('Inserting into GHG_TRANSACTIONS_D_TMP');
'INSERT INTO GHG_TRANSACTIONS_D_TMP ' ||
'( ' ||
'SOURCE_APPLICATION_CODE, ' ||
'TRANSACTION_ID, ' ||
'DOCUMENT_TYPE, ' ||
'DOCUMENT_IDENTIFIER1, ' ||
'TRANSACTION_DATE, ' ||
'TRANSACTION_VALUE, ' ||
'TRANSACTION_CURRENCY_CODE, ' ||
'TRANSACTION_CURRENCY_DESC, ' ||
'emission_source_id, ' ||
'EMISSION_SOURCE_USAGE_QUANTITY, ' ||
'EMISSION_SOURCE_USAGE_UOM, ' ||
'EMISSION_SCOPE_ID, ' ||
'ENERGY_TYPE, ' ||
'ENERGY_QUANTITY, ' ||
'ENERGY_UOM, ' ||
'CO2_E_QUANTITY, ' ||
'CO2_E_UOM, ' ||
'GHG_ASSET_ID, ' ||
'SUPPLIER_ID, ' ||
'ITEM_ID, ' ||
'ACTUAL_OR_ESTIMATE, ' ||
'LOCATION_STATE, ' ||
'location_county, ' ||
'ORGANIZATION_id, ' ||
'measurement_criteria_code, ' ||
'measurement_criteria_desc, ' ||
'ENTERED_USAGE_QUANTITY, ' ||
'ENTERED_USAGE_UOM ' ||
') ' ||
'SELECT tr.org_id SOURCE_APPLICATION_CODE ' ||
', tr.transaction_id transaction_id ' ||
', (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 ' ||
', decode(tr.txn_type_lookup_code, ''INT_INVOICE'', ap.invoice_num, tr.transaction_id) document_identifier1 ' ||
', rp.actual_date transaction_date ' ||
', nvl(ap.invoice_amount,0)/((tr.transaction_date_to-tr.transaction_date_from)+1) transaction_value ' ||
', ap.invoice_currency_code transaction_currency_code ' ||
', cur.name transaction_currency_desc ' ||
', em.source_combination_id transaction_source_id ' ||
', ((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 ' ||
', (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 ' ||
', tr.emission_scope_lookup_code emission_scope_id ' ||
', (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 ' ||
', nvl(tr.energy_quantity,0)/((tr.transaction_date_to-tr.transaction_date_from)+1) energy_quantity ' ||
', ''GJ'' energy_uom ' ||
', (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 ' ||
', ''' || v_uom || ''' co2_e_uom ' ||
', tr.ghg_asset_id asset_id ' ||
', tr.vendor_id supplier_id ' ||
', tr.inventory_item_id item_id ' ||
', ''A'' actual_or_estimate ' ||
', (select address_state from GHG_ORGANIZATIONS_D_TMP org where org.ORGANIZATION_id = tr.ghg_organization_id) location_state ' ||
', (select address_country from GHG_ORGANIZATIONS_D_TMP org where org.ORGANIZATION_id = tr.ghg_organization_id) location_county ' ||
', tr.ghg_organization_id ORGANIZATION_id ' ||
', tr.measurement_criteria measurement_criteria_code ' ||
', tr.measurement_criteria measurement_criteria_desc ' ||
', nvl(tr.usage_quantity,0) ' ||
', tr.unit_of_measure ' ||
'FROM GHG_TRANSACTIONS_ALL tr ' ||
', ap_invoices ap ' ||
', GHG_PERIODS_D@' || v_dblink || ' rp ' ||
', fnd_currencies_tl cur ' ||
', GHG_TRANSACTION_DETAILS_ALL em ' ||
'where tr.invoice_id = ap.invoice_id(+) ' ||
'and rp.actual_date between tr.transaction_date_from and tr.transaction_date_to ' ||
'and ap.invoice_currency_code = cur.currency_code(+) ' ||
'and em.transaction_id = tr.transaction_id ' ||
'and em.transaction_date = rp.actual_date ' ||
'and em.transaction_type = ''N'' ' ||
'and tr.org_id= ' || p_org_id || ' ' ||
'and nvl(cur.language,''' || v_language || ''') = ''' || v_language || '''';
write_to_log('Inserting into GHG_TRANSACTIONS_D');
'INSERT INTO GHG_TRANSACTIONS_D@' || v_dblink ||
' ( ' ||
'SOURCE_APPLICATION_CODE, ' ||
'TRANSACTION_ID, ' ||
'DOCUMENT_TYPE, ' ||
'DOCUMENT_IDENTIFIER1, ' ||
'TRANSACTION_DATE, ' ||
'TRANSACTION_VALUE, ' ||
'TRANSACTION_CURRENCY_CODE, ' ||
'TRANSACTION_CURRENCY_DESC, ' ||
'emission_source_id, ' ||
'EMISSION_SOURCE_USAGE_QUANTITY, ' ||
'EMISSION_SOURCE_USAGE_UOM, ' ||
'EMISSION_SCOPE_ID, ' ||
'ENERGY_TYPE, ' ||
'ENERGY_QUANTITY, ' ||
'ENERGY_UOM, ' ||
'CO2_E_QUANTITY, ' ||
'CO2_E_UOM, ' ||
'GHG_ASSET_ID, ' ||
'SUPPLIER_ID, ' ||
'ITEM_ID, ' ||
'ACTUAL_OR_ESTIMATE, ' ||
'LOCATION_STATE, ' ||
'location_county, ' ||
'ORGANIZATION_id, ' ||
'measurement_criteria_code, ' ||
'measurement_criteria_desc, ' ||
'ENTERED_USAGE_QUANTITY, ' ||
'ENTERED_USAGE_UOM' ||
') ' ||
'SELECT ' ||
'SOURCE_APPLICATION_CODE, ' ||
'TRANSACTION_ID, ' ||
'DOCUMENT_TYPE, ' ||
'DOCUMENT_IDENTIFIER1, ' ||
'TRANSACTION_DATE, ' ||
'TRANSACTION_VALUE, ' ||
'TRANSACTION_CURRENCY_CODE, ' ||
'TRANSACTION_CURRENCY_DESC, ' ||
'emission_source_id, ' ||
'EMISSION_SOURCE_USAGE_QUANTITY, ' ||
'EMISSION_SOURCE_USAGE_UOM, ' ||
'EMISSION_SCOPE_ID, ' ||
'ENERGY_TYPE, ' ||
'ENERGY_QUANTITY, ' ||
'ENERGY_UOM, ' ||
'CO2_E_QUANTITY, ' ||
'CO2_E_UOM, ' ||
'GHG_ASSET_ID, ' ||
'SUPPLIER_ID, ' ||
'ITEM_ID, ' ||
'ACTUAL_OR_ESTIMATE, ' ||
'LOCATION_STATE, ' ||
'location_county, ' ||
'ORGANIZATION_id, ' ||
'measurement_criteria_code, ' ||
'measurement_criteria_desc, ' ||
'ENTERED_USAGE_QUANTITY, ' ||
'ENTERED_USAGE_UOM ' ||
'FROM GHG_TRANSACTIONS_D_TMP WHERE SOURCE_APPLICATION_CODE='|| p_org_id ||'';
write_to_log('Inserting into GHG_TRANSACTION_DETAILS_F');
'insert into GHG_TRANSACTION_DETAILS_F@' || v_dblink ||
' ( source_application_code, ' ||
'transaction_id, ' ||
'emission_gas_type, ' ||
'EMISSION_QUANTITY, ' ||
'EMISSION_uom, ' ||
'TRANSACTION_DATE, ' ||
'source_id, ' ||
'EMISSION_SCOPE_ID, ' ||
'ghg_asset_id, ' ||
'SUPPLIER_ID, ' ||
'ITEM_ID, ' ||
'ORGANIZATION_id ' ||
') ' ||
'select em.org_id ' ||
', em.transaction_id ' ||
', ld.transaction_component_type ' ||
', ld.transaction_value * '||fnd_number.NUMBER_TO_CANONICAL(v_final_conv_rate) ||
', ''' || v_uom || '''' ||
', ld.transaction_date ' ||
', ld.source_combination_id ' ||
', em.emission_scope_lookup_code ' ||
', em.ghg_asset_id ' ||
', em.vendor_id ' ||
', em.inventory_item_id ' ||
', em.ghg_organization_id ' ||
'from GHG_TRANSACTIONS_all em ' ||
', GHG_TRANSACTION_DETAILS_all ld ' ||
'where em.transaction_id = ld.transaction_id ' ||
'and ld.transaction_type = ''E''';
write_to_log('Inserting into GHG_TRANSACTION_DETAILS_F');
'insert INTO GHG_TRANSACTIONS_BY_MONTH_F@' || v_dblink ||
'( ' ||
'LEVEL3_ID, ' ||
'SOURCE_APPLICATION_CODE, ' ||
'TRANSACTION_VALUE, ' ||
'TRANSACTION_CURRENCY_CODE, ' ||
'TRANSACTION_CURRENCY_DESC, ' ||
'emission_source_id, ' ||
'EMISSION_SOURCE_USAGE_QUANTITY, ' ||
'EMISSION_SOURCE_USAGE_UOM, ' ||
'EMISSION_SCOPE_ID, ' ||
'ENERGY_TYPE, ' ||
'ENERGY_QUANTITY, ' ||
'ENERGY_UOM, ' ||
'CO2_E_QUANTITY, ' ||
'CO2_E_UOM, ' ||
'GHG_ASSET_ID, ' ||
'SUPPLIER_ID, ' ||
'ITEM_ID, ' ||
'ACTUAL_OR_ESTIMATE, ' ||
'LOCATION_STATE, ' ||
'LOCATION_COUNTY, ' ||
'GHG_ORGANIZATION_ID, ' ||
'MEASUREMENT_CRITERIA_CODE, ' ||
'measurement_criteria_desc) ' ||
'select rp.level3_id, ' ||
'source_application_code, ' ||
'transaction_value, ' ||
'transaction_currency_code, ' ||
'transaction_currency_desc, ' ||
'emission_source_id, ' ||
'usage_qty, ' ||
'EMISSION_SOURCE_USAGE_UOM, ' ||
'EMISSION_SCOPE_ID, ' ||
'energy_type, ' ||
'energy_qty, ' ||
'energy_uom, ' ||
'co2e_qty, ' ||
'CO2_E_UOM, ' ||
'ghg_asset_id, ' ||
'supplier_id, ' ||
'ITEM_ID, ' ||
'actual_or_estimate, ' ||
'LOCATION_STATE, ' ||
'location_county, ' ||
'ORGANIZATION_ID, ' ||
'measurement_criteria_code, ' ||
'measurement_criteria_desc ' ||
'from GHG_PERIODS_D@' || v_dblink || ' rp ' ||
', (select source_application_code, ' ||
'trunc(transaction_date) transaction_date, ' ||
'transaction_value, ' ||
'transaction_currency_code, ' ||
'transaction_currency_desc, ' ||
'emission_source_id, ' ||
'sum(nvl(emission_source_usage_quantity,0)) usage_qty, ' ||
'EMISSION_SOURCE_USAGE_UOM, ' ||
'EMISSION_SCOPE_ID, ' ||
'energy_type, ' ||
'sum(nvl(energy_quantity,0)) energy_qty, ' ||
'energy_uom, ' ||
'sum(nvl(co2_e_quantity,0)) co2e_qty, ' ||
'CO2_E_UOM, ' ||
'ghg_asset_id, ' ||
'supplier_id, ' ||
'ITEM_ID, ' ||
'actual_or_estimate, ' ||
'LOCATION_STATE, ' ||
'location_county, ' ||
'ORGANIZATION_ID, ' ||
'measurement_criteria_code, ' ||
'measurement_criteria_desc ' ||
'from GHG_TRANSACTIONS_D@' || v_dblink || ' tr ' ||
'group by source_application_code, trunc(transaction_date), ' ||
'transaction_value, transaction_currency_code, ' ||
'transaction_currency_desc, emission_source_id, ' ||
'emission_source_usage_uom, emission_scope_id, energy_type, ' ||
'energy_uom, co2_e_uom, ghg_asset_id, supplier_id, item_id, ' ||
'actual_or_estimate, location_state, location_county, ' ||
'ORGANIZATION_id, measurement_criteria_code, ' ||
'measurement_criteria_desc) trs ' ||
'where rp.actual_date = trunc(trs.transaction_date)';
write_to_log('Inserting into GHG_ORGANIZATION_INTERESTS_D');
'INSERT INTO GHG_ORGANIZATION_INTERESTS_D@' || v_dblink ||
' ( SOURCE_APPLICATION_CODE, ' ||
'ORGANIZATION_INTERESTS_ID, ' ||
'INTERESTED_ORGANIZATION_ID, ' ||
'ORGANIZATION_ID, ' ||
'DATE_FROM, ' ||
'DATE_TO, ' ||
'INTEREST_TYPE_CODE, ' ||
'INTEREST_TYPE_DESCRIPTION, ' ||
'INTEREST_PERCENT, ' ||
'ORGANIZATION_CODE, ' ||
'ORGANIZATION_NAME, ' ||
'CEO_NAME, ' ||
'company_identifier, ' ||
'ADDRESS_LINE_1, ' ||
'ADDRESS_LINE_2, ' ||
'ADDRESS_LINE_3, ' ||
'ADDRESS_LINE_4, ' ||
'ADDRESS_SUBURB, ' ||
'ADDRESS_STATE, ' ||
'ADDRESS_COUNTY, ' ||
'address_country, ' ||
'ADDRESS_POSTCODE) ' ||
'select org_id, ' ||
'a.INTERESTED_PARTY_ID, ' ||
'b.ORGANIZATION_id, ' ||
'a.ghg_organization_id, ' ||
'a.start_date, ' ||
'a.end_date, ' ||
'a.operational_control, ' ||
'(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), ' ||
'a.equity_share , ' ||
'b.ORGANIZATION_id, ' ||
'a.CONTROLLING_ORGANIZATION_ID, ' ||
'b.ceo_name, ' ||
'b.company_identifier, ' ||
'b.address_line_1, ' ||
'b.address_line_2, ' ||
'b.address_line_3, ' ||
'b.address_line_4, ' ||
'b.address_suburb, ' ||
'b.address_state, ' ||
'b.address_county, ' ||
'b.address_country, ' ||
'b.ADDRESS_POSTCODE ' ||
'from GHG_INTERESTED_PARTIES_ALL a ' ||
', GHG_ORGANIZATIONS_D_TMP b ' ||
'where org_id = source_application_code ' ||
'and a.CONTROLLING_ORGANIZATION_ID = b.ORGANIZATION_code ' ||
' and a.operational_control <> ''OPC''';
write_to_log('Inserting into GHG_ORGANIZATION_CONTROL_D');
'INSERT ' ||
'INTO GHG_ORGANIZATION_CONTROL_D@' || v_dblink ||
' ( ' ||
'SOURCE_APPLICATION_CODE, ' ||
'ORGANIZATION_CONTROL_ID, ' ||
'CONTROLLING_ORGANIZATION_ID, ' ||
'ORGANIZATION_ID, ' ||
'DATE_FROM, ' ||
'DATE_TO, ' ||
'CONTROL_TYPE_CODE, ' ||
'CONTROL_TYPE_DESCRIPTION, ' ||
'CONTROL_PERCENT, ' ||
'ORGANIZATION_CODE, ' ||
'ORGANIZATION_NAME, ' ||
'CEO_NAME, ' ||
'company_identifier, ' ||
'ADDRESS_LINE_1, ' ||
'ADDRESS_LINE_2, ' ||
'ADDRESS_LINE_3, ' ||
'ADDRESS_LINE_4, ' ||
'ADDRESS_SUBURB, ' ||
'ADDRESS_STATE, ' ||
'ADDRESS_COUNTY, ' ||
'ADDRESS_COUNTRY, ' ||
'ADDRESS_POSTCODE ' ||
') ' ||
'select org_id, ' ||
'a.INTERESTED_PARTY_ID, ' ||
'b.ORGANIZATION_id, ' ||
'a.ghg_organization_id, ' ||
'a.start_date, ' ||
'a.end_date, ' ||
'a.operational_control, ' ||
'(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), ' ||
'a.equity_share , ' ||
'b.ORGANIZATION_id, ' ||
'a.CONTROLLING_ORGANIZATION_ID, ' ||
'b.ceo_name, ' ||
'b.company_identifier, ' ||
'b.address_line_1, ' ||
'b.address_line_2, ' ||
'b.address_line_3, ' ||
'b.address_line_4, ' ||
'b.address_suburb, ' ||
'b.address_state, ' ||
'b.address_county, ' ||
'b.address_country, ' ||
'b.ADDRESS_POSTCODE ' ||
'from GHG_INTERESTED_PARTIES_ALL a ' ||
', GHG_ORGANIZATIONS_D_TMP b ' ||
'where org_id = source_application_code ' ||
'and a.CONTROLLING_ORGANIZATION_ID = b.ORGANIZATION_code ' ||
'and a.operational_control = ''OPC''';
write_to_log('Inserting into GHG_KPI_TRANSACTIONS_F');
'INSERT INTO GHG_KPI_TRANSACTIONS_F@' || v_dblink || ' ' ||
' ( ' ||
'SOURCE_APPLICATION_CODE, ' ||
'KPI_ID, ' ||
'KPI_DATE, ' ||
'ORGANIZATION_ID, ' ||
'NUMERATOR_UOM, ' ||
'NUMERATOR_VALUE, ' ||
'NUMERATOR_ACTION, ' ||
'DENOMINATOR_UOM, ' ||
'DENOMINATOR_VALUE, ' ||
'DENOMINATOR_ACTION, ' ||
'NUMERATOR_VALUE_TYPE, ' ||
'DENOMINATOR_VALUE_TYPE, ' ||
'TRANSACTION_TYPE ' ||
') ' ||
'select kpd.org_id source_application_code, ' ||
'kpd.kpi_id kpi_id, ' ||
'trx.transaction_date kpi_date, ' ||
'trx.ORGANIZATION_id ORGANIZATION_id, ' ||
'decode( kpd.num_type, ' ||
'''USAGE'', umn.STANDARD_UOM, ' ||
'''ENERGY'', trx.energy_uom, ' ||
'''EMISSIONS'', trx.co2_e_uom, ' ||
'''VALUE'', trx.transaction_currency_code, '''') numerator_uom, ' ||
'decode( kpd.num_type, ' ||
'''USAGE'', nvl(trx.emission_source_usage_quantity,0), ' ||
'''ENERGY'', nvl(trx.energy_quantity,0), ' ||
'''EMISSIONS'', nvl(trx.co2_e_quantity,0), ' ||
'''VALUE'', nvl(trx.transaction_value,0),0) numerator_value, ' ||
'kpd.num_function numerator_action, ' ||
'decode( kpd.den_type, ' ||
'''USAGE'', umd.STANDARD_UOM, ' ||
'''ENERGY'', trx.energy_uom, ' ||
'''EMISSIONS'', trx.co2_e_uom, ' ||
'''VALUE'', trx.transaction_currency_code, '''') denominator_uom, ' ||
'0 denominator_value, ' ||
'kpd.den_function denominator_action, ' ||
'initcap(kpd.num_type) numerator_value_type, ' ||
'initcap(kpd.den_type) denominator_value_type, ' ||
' (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 ' ||
'from GHG_KPI_DEFINITIONS_all kpd ' ||
', GHG_KPI_COMPONENTS kpc ' ||
', GHG_SOURCES_D@' || v_dblink || ' src ' ||
', GHG_TRANSACTIONS_D@' || v_dblink || ' trx ' ||
', GHG_UOM_CLASSES_all umn ' ||
', GHG_UOM_CLASSES_all umd ' ||
'where kpd.kpi_id = kpc.kpi_id ' ||
'and kpc.source_id = src.source_info_code_1 ' ||
'and kpc.COMPONENT_TYPE = ''N'' ' ||
'and trx.emission_source_id = src.source_id ' ||
' and kpd.org_id='|| p_org_id ||'' ||
' and umn.ghg_uom_class_code = kpd.num_uom_class_code ' ||
'and umd.ghg_uom_class_code = kpd.den_uom_class_code ' ||
'union all ' ||
'select kpd.org_id source_application_code, ' ||
'kpd.kpi_id kpi_id, ' ||
'trx.transaction_date kpi_date, ' ||
'trx.ORGANIZATION_id ORGANIZATION_id, ' ||
'decode( kpd.num_type, ' ||
'''USAGE'', umn.STANDARD_UOM, ' ||
'''ENERGY'', trx.energy_uom, ' ||
'''EMISSIONS'', trx.co2_e_uom, ' ||
'''VALUE'', trx.transaction_currency_code, '''') numerator_uom, ' ||
'0 numerator_value, ' ||
'kpd.num_function numerator_action, ' ||
'decode( kpd.den_type, ' ||
'''USAGE'', umd.STANDARD_UOM, ' ||
'''ENERGY'', trx.energy_uom, ' ||
'''EMISSIONS'', trx.co2_e_uom, ' ||
'''VALUE'', trx.transaction_currency_code, '''') denominator_uom, ' ||
'decode( kpd.den_type, ' ||
'''USAGE'', nvl(trx.emission_source_usage_quantity,0), ' ||
'''ENERGY'', nvl(trx.energy_quantity,0), ' ||
'''EMISSIONS'', nvl(trx.co2_e_quantity,0), ' ||
'''VALUE'', nvl(trx.transaction_value,0),0) denominator_value, ' ||
'kpd.den_function denominator_action, ' ||
'initcap(kpd.num_type) numerator_value_type, ' ||
'initcap(kpd.den_type) denominator_value_type, ' ||
'''Actuals'' transaction_type ' ||
'from GHG_KPI_DEFINITIONS_all kpd ' ||
', GHG_KPI_COMPONENTS kpc ' ||
', GHG_SOURCES_D@' || v_dblink || ' src ' ||
', GHG_TRANSACTIONS_D@' || v_dblink || ' trx ' ||
', GHG_UOM_CLASSES_all umn ' ||
', GHG_UOM_CLASSES_all umd ' ||
'where kpd.kpi_id = kpc.kpi_id ' ||
'and kpc.source_id = src.source_info_code_1 ' ||
'and kpc.COMPONENT_TYPE = ''D'' ' ||
'and trx.emission_source_id = src.source_id ' ||
'and kpd.org_id='|| p_org_id ||'' ||
' and umn.ghg_uom_class_code = kpd.num_uom_class_code ' ||
'and umd.ghg_uom_class_code = kpd.den_uom_class_code';
write_to_log('Inserting into GHG_KPI_DEFINITIONS_D');
'INSERT ' ||
'INTO GHG_KPI_DEFINITIONS_D@' || v_dblink ||
' ( ' ||
'KPI_ID, ' ||
'KPI_CODE, ' ||
'KPI_NAME, ' ||
'SOURCE_APPLICATION_CODE ' ||
') ' ||
'SELECT KPI_ID, ' ||
'KPI_CODE, ' ||
'DESCRIPTION, ' ||
'ORG_ID ' ||
'FROM GHG_KPI_DEFINITIONS_all WHERE org_id='|| p_org_id ||'';
SELECT hr.organization_id ORG_ID
FROM hr_operating_units hr,
gl_sets_of_books gsob,
financials_system_params_all fsp
WHERE hr.set_of_books_id = gsob.set_of_books_id
AND mo_global.check_access(hr.organization_id) = 'Y'
AND hr.organization_id NOT IN
( SELECT DISTINCT ORG_ID FROM GHG_EMISSION_FORMULAS_ALL)
AND hr.organization_id = fsp.org_id;
SELECT FORMULA_ID,
DESCRIPTION,
FORMULA_CONTENT,
SECONDARY_FORMULA_ID
FROM GHG_EMISSION_FORMULAS_ALL
WHERE ORG_ID = -1;
SELECT COUNT(*)
INTO v_seed_row_count
FROM GHG_EMISSION_FORMULAS_ALL
WHERE ORG_ID = -1;
insert into GHG_EMISSION_FORMULAS_ALL table */
write_to_log('Copy the formula for ORG_ID -1 into the master list of organizations');
write_to_log('Inserting into GHG_EMISSION_FORMULAS_ALL table');
INSERT INTO GHG_EMISSION_FORMULAS_ALL ( FORMULA_ID,
DESCRIPTION,
FORMULA_CONTENT,
ORG_ID,
SECONDARY_FORMULA_ID)
VALUES (seed_formula_rec.formula_id,
seed_formula_rec.description,
seed_formula_rec.formula_content,
orgs_rec.ORG_ID,
seed_formula_rec.secondary_formula_id);