[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(nvl(end_date+1,start_date)) --ssurendr: Changed GHG_UTILITIES_PKG.end_date to start_date so that duplicate record validation correctly happens.
into v_max_date
from GHG_source_combinations_all
where /*code_combination_id = x_code_combination_id --Lakshmi 04/27/11 - nvl is removed so that correct start date is returned.
ssurendr 05/06 - code combination removed from join
and*/ source_id = x_emission_source_id
and nvl(supplier_id, -1) = nvl(x_supplier_id, -1)
and nvl(supplier_site_id, -1) = nvl(x_supplier_site_id, -1)
and nvl(item_id, -1) = nvl(x_item_id, -1)
and nvl(item_type, -1) = nvl(x_item_type, -1)
and nvl(ghg_organization_id, -1) = nvl(x_facility_id, -1)
and nvl(scope_lookup_code, -1) = nvl(x_scope_lookup_code, -1)
and nvl(location_code, -1) = nvl(x_location_code, -1)
and nvl(transport_type_lookup_code, -1) = nvl(x_transport_type_lookup_code, -1);
select count(*)
into v_count
from GHG_source_combinations_all
where code_combination_id = x_code_combination_id
and source_id = x_emission_source_id
and nvl(supplier_id,-1) = nvl(x_supplier_id,-1)
and nvl(supplier_site_id,-1) = nvl(x_supplier_site_id,-1)
and nvl(item_id,-1) = nvl(x_item_id,-1)
and nvl(item_type,-1) = nvl(x_item_type,-1)
and nvl(ghg_organization_id,-1) = nvl(x_facility_id,-1)
and x_date < start_date;
select GHG_source_combinations_s.NEXTVAL
into v_rate_id
from dual;
select count(*)
into v_row_count
from GHG_transaction_details_all
where source_combination_id = x_rate_id;
insert into ghg_txn_details_history_all(TRANSACTION_ID,
TRANSACTION_DATE,
TRANSACTION_TYPE,
TRANSACTION_COMPONENT_TYPE,
TRANSACTION_VALUE,
SOURCE_COMBINATION_ID,
ORG_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID)
select a.transaction_id,
a.transaction_date,
a.transaction_type,
a.transaction_component_type,
a.transaction_value,
a.source_combination_id,
a.org_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_request_id
from ghg_transaction_details_all a
where a.transaction_id = x_emission_id;
delete from GHG_transaction_details_all
where transaction_id = x_emission_id;
select (trunc(transaction_date_to) - trunc(transaction_date_from))+ 1,
usage_quantity/((trunc(transaction_date_to) - trunc(transaction_date_from))+ 1),
trunc(transaction_date_from),
uom_conversion,
emission_scope_lookup_code,
location_code,
vendor_id,
vendor_site_id,
inventory_item_id,
ghg_asset_id,
ghg_organization_id,
transaction_source_id,
unit_of_measure,
org_id
into v_emission_date_factor,
v_emission_quantity,
v_emission_date_from,
v_uom_conversion,
v_emission_scope,
v_location,
v_supplier_id,
v_supplier_site_id,
v_item_id,
v_asset_id,
v_facility_id,
v_source_id,
v_uom,
v_org_id
from GHG_transactions_all
where transaction_id = x_emission_id;
select uom_class
into v_uom_class
from mtl_units_of_measure_vl
where unit_of_measure = v_uom;
update GHG_transactions_all
set emission_quantity = GHG_transactions_pkg.get_emission_total(x_emission_id)
, energy_quantity = GHG_transactions_pkg.get_energy_total(x_emission_id)
, emission_SCOPE_LOOKUP_CODE =
(SELECT decode(emission_SCOPE_LOOKUP_CODE,
'-99999', SCOPE_LOOKUP_CODE,
emission_SCOPE_LOOKUP_CODE)
FROM GHG_source_combinations_all
where SOURCE_COMBINATION_ID=v_emission_factor_id)
where transaction_id = x_emission_id;
SELECT source_combination_id,
location_code -- 12565482 : ssurendr
INTO v_emission_factor_id,
x_out_location -- 12565482 : ssurendr
FROM
(SELECT source_combination_id,
location_code -- 12565482 : ssurendr
FROM GHG_source_combinations_all
WHERE source_id = x_source_id
AND scope_lookup_code = decode(x_scope,
'-99999', scope_lookup_code,
null, scope_lookup_code,
x_scope)
AND decode(location_code,'ALL',x_location,location_code) = x_location
AND decode(transport_type_lookup_code,
'DEFAULT', nvl(x_transport_type,'DEFAULT'),
transport_type_lookup_code)
= nvl(x_transport_type,'DEFAULT')
AND NVL(ghg_organization_id,nvl(x_facility_id,-1)) = nvl(x_facility_id,-1)
AND NVL(item_id,nvl(x_item_id,-1)) = nvl(x_item_id,-1)
AND NVL(supplier_id,NVL(x_supplier_id,-1)) = NVL(x_supplier_id,-1)
AND NVL(supplier_site_id,NVL(x_supplier_site_id,-1)) = NVL(x_supplier_site_id,-1)
AND x_emission_date BETWEEN TRUNC(start_date)
AND TRUNC(NVL(end_date,GHG_UTILITIES_PKG.end_date))
ORDER BY
scope_lookup_code,
CASE
WHEN item_id IS NOT NULL
THEN '1'
WHEN supplier_id IS NOT NULL
THEN '2'
WHEN ghg_organization_id IS NOT NULL
THEN '3'
ELSE '9'
END
||
CASE
WHEN TRANSPORT_TYPE_LOOKUP_CODE <> 'DEFAULT'
THEN '1'
ELSE '9'
END
||
CASE
WHEN LOCATION_CODE <> 'ALL'
THEN '1'
ELSE '9'
END,
METHOD_LOOKUP_CODE desc
)
WHERE ROWNUM = 1;
SELECT TRANSPORT_TYPE_LOOKUP_CODE
INTO v_transport_type
FROM GHG_assets
WHERE ghg_asset_id = x_asset_id;
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_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 = x_facility_id
and fac.location_id = hr.location_id)
AND upper(end_user_column_name) = fnd_profile.value('GHG_FACTOR_BREAK_REGION');
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_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 = x_facility_id
and fac.location_id = hr.location_id)
AND upper(end_user_column_name) = fnd_profile.value('GHG_FACTOR_BREAK_REGION');
SELECT source_combination_id,
location_code -- 12565482 : ssurendr
INTO v_emission_factor_id,
x_out_location -- 12565482 : ssurendr
FROM
(SELECT source_combination_id,
location_code -- 12565482 : ssurendr
FROM GHG_source_combinations_all
WHERE source_id = x_source_id
AND scope_lookup_code = decode(x_scope,
'-99999', scope_lookup_code,
null, scope_lookup_code,
x_scope)
AND decode(location_code,'ALL',x_location,location_code) = x_location
AND decode(transport_type_lookup_code,
'DEFAULT', nvl(x_transport_type,'DEFAULT'),
transport_type_lookup_code)
= nvl(x_transport_type,'DEFAULT')
AND NVL(ghg_organization_id,nvl(x_facility_id,-1)) = nvl(x_facility_id,-1)
AND NVL(item_id,nvl(x_item_id,-1)) = nvl(x_item_id,-1)
AND NVL(supplier_id,NVL(x_supplier_id,-1)) = NVL(x_supplier_id,-1)
AND NVL(supplier_site_id,NVL(x_supplier_site_id,-1)) = NVL(x_supplier_site_id,-1)
AND x_emission_date BETWEEN TRUNC(start_date)
AND TRUNC(NVL(end_date,GHG_UTILITIES_PKG.end_date))
ORDER BY
scope_lookup_code,
CASE
WHEN item_id IS NOT NULL
THEN '1'
WHEN supplier_id IS NOT NULL
THEN '2'
WHEN ghg_organization_id IS NOT NULL
THEN '3'
ELSE '9'
END
||
CASE
WHEN TRANSPORT_TYPE_LOOKUP_CODE <> 'DEFAULT'
THEN '1'
ELSE '9'
END
||
CASE
WHEN LOCATION_CODE <> 'ALL'
THEN '1'
ELSE '9'
END,
METHOD_LOOKUP_CODE desc
)
WHERE ROWNUM = 1;
SELECT TRANSPORT_TYPE_LOOKUP_CODE
INTO v_transport_type
FROM GHG_assets
WHERE ghg_asset_id = x_asset_id;
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_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 = x_facility_id
and fac.location_id = hr.location_id)
AND upper(end_user_column_name) = fnd_profile.value('GHG_FACTOR_BREAK_REGION');
select gas_type_lookup_code
, factor_value
, factor_type
from GHG_factors_all
where source_combination_id = x_factor_id
and factor_type ='E';
select decode(x_uom_class, fnd_profile.value('GHG_UOM_CLASS'), nvl(ef.secondary_formula_id,ef.formula_id), ef.formula_id)
into vl_formula_id
from ghg_sources s
, ghg_source_combinations_all f
, ghg_emission_formulas ef
where s.source_id = f.source_id
and f.source_combination_id = x_factor_id
and s.formula_id = ef.formula_id;
select 'select '||formula_content||' from dual'
into vl_formula_contents
from ghg_emission_formulas
where formula_id = vl_formula_id;
select FACTOR_VALUE
into vl_ec
from GHG_FACTORS_ALL xx
where source_combination_id = x_factor_id
and factor_type ='N';
insert into GHG_transaction_details_all (transaction_id,transaction_date,transaction_type,transaction_component_type,transaction_value,source_combination_id,org_id,created_by,creation_date,last_updated_by,last_update_date,last_update_login)
values(
x_emission_id,
x_emission_date,
'N',
'ENERGY',
x_quantity*decode(x_uom_class, fnd_profile.value('GHG_UOM_CLASS'), 1, vl_ec) ,
x_factor_id,
x_org_id,
fnd_profile.value('USER_ID'), sysdate, fnd_profile.value('USER_ID'), sysdate, null
);
insert into GHG_transaction_details_all (transaction_id,transaction_date,transaction_type,transaction_component_type,transaction_value,source_combination_id,org_id,created_by,creation_date,last_updated_by,last_update_date,last_update_login)
values(
x_emission_id,
x_emission_date,
gas_type.factor_type,
gas_type.gas_type_lookup_code,
vl_co2e,
x_factor_id,
x_org_id,
fnd_profile.value('USER_ID'), sysdate, fnd_profile.value('USER_ID'), sysdate, null
);