[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_row (x_rowid IN OUT NOCOPY VARCHAR2,
x_txn_type_lookup_code VARCHAR2,
x_emission_id NUMBER,
x_emission_batch_id NUMBER,
x_emission_source_id NUMBER,
x_facility_id NUMBER,
x_emission_type_lookup_code VARCHAR2,
x_usage_quantity NUMBER,
x_unit_of_measure VARCHAR2,
x_uom_conversion NUMBER,
x_emission_quantity NUMBER,
x_energy_quantity NUMBER,
x_emission_date_from DATE,
x_emission_date_to DATE,
x_description VARCHAR2,
x_org_id NUMBER,
x_set_of_books_id NUMBER,
x_invoice_id NUMBER,
x_emission_line_number NUMBER,
x_distribution_line_number NUMBER,
x_invoice_line_number NUMBER,
x_vendor_id NUMBER,
x_vendor_site_id NUMBER,
x_inventory_item_id NUMBER,
x_ghg_asset_id NUMBER,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2,
x_attribute16 VARCHAR2,
x_attribute17 VARCHAR2,
x_attribute18 VARCHAR2,
x_attribute19 VARCHAR2,
x_attribute20 VARCHAR2,
x_attribute_category VARCHAR2,
x_created_by NUMBER,
x_creation_date DATE,
x_last_updated_by NUMBER,
x_last_update_date DATE,
x_last_update_login NUMBER,
x_commit_flag VARCHAR2,
x_scope VARCHAR2,
x_location VARCHAR2,
x_measurement_criteria VARCHAR2,
x_called_from_api VARCHAR2 DEFAULT 'N') IS
v_debug_info VARCHAR2(100);
SELECT ROWID
FROM GHG_transactions_all
WHERE transaction_id = x_emission_id;
v_debug_info := 'Inserting into GHG_transactions_all';
INSERT INTO GHG_transactions_all (txn_type_lookup_code,
transaction_id,
transaction_batch_id,
transaction_source_id,
ghg_organization_id,
transaction_type_lookup_code,
usage_quantity,
unit_of_measure,
uom_conversion,
emission_quantity,
energy_quantity,
transaction_date_from,
transaction_date_to,
description,
org_id,
set_of_books_id,
invoice_id,
transaction_line_number,
distribution_line_number,
invoice_line_number,
vendor_id,
vendor_site_id,
inventory_item_id,
ghg_asset_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute_category,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
emission_scope_lookup_code,
location_code,
measurement_criteria)
VALUES (x_txn_type_lookup_code,
x_emission_id,
x_emission_batch_id,
x_emission_source_id,
x_facility_id,
x_emission_type_lookup_code,
x_usage_quantity,
x_unit_of_measure,
x_uom_conversion,
x_emission_quantity,
x_energy_quantity,
x_emission_date_from,
x_emission_date_to,
x_description,
x_org_id,
x_set_of_books_id,
x_invoice_id,
x_emission_line_number,
x_distribution_line_number,
x_invoice_line_number,
x_vendor_id,
x_vendor_site_id,
x_inventory_item_id,
x_ghg_asset_id,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_attribute16,
x_attribute17,
x_attribute18,
x_attribute19,
x_attribute20,
x_attribute_category,
x_created_by,
x_creation_date,
x_last_updated_by,
x_last_update_date,
x_last_update_login,
x_scope,
x_location,
x_measurement_criteria);
END insert_row;
PROCEDURE update_row (x_rowid IN OUT NOCOPY VARCHAR2,
x_txn_type_lookup_code VARCHAR2,
x_emission_id NUMBER,
x_emission_batch_id NUMBER,
x_emission_source_id NUMBER,
x_facility_id NUMBER,
x_emission_type_lookup_code VARCHAR2,
x_usage_quantity NUMBER,
x_unit_of_measure VARCHAR2,
x_uom_conversion NUMBER,
x_emission_quantity NUMBER,
x_energy_quantity NUMBER,
x_emission_date_from DATE,
x_emission_date_to DATE,
x_description VARCHAR2,
x_org_id NUMBER,
x_set_of_books_id NUMBER,
x_invoice_id NUMBER,
x_emission_line_number NUMBER,
x_distribution_line_number NUMBER,
x_invoice_line_number NUMBER,
x_vendor_id NUMBER,
x_vendor_site_id NUMBER,
x_inventory_item_id NUMBER,
x_ghg_asset_id NUMBER,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2,
x_attribute16 VARCHAR2,
x_attribute17 VARCHAR2,
x_attribute18 VARCHAR2,
x_attribute19 VARCHAR2,
x_attribute20 VARCHAR2,
x_attribute_category VARCHAR2,
x_created_by NUMBER,
x_creation_date DATE,
x_last_updated_by NUMBER,
x_last_update_date DATE,
x_last_update_login NUMBER,
x_scope VARCHAR2,
x_location VARCHAR2,
x_measurement_criteria VARCHAR2) IS
v_debug_info VARCHAR2(100);
UPDATE GHG_transactions_all
SET txn_type_lookup_code = x_txn_type_lookup_code,
transaction_id = x_emission_id,
transaction_batch_id = x_emission_batch_id,
transaction_source_id = x_emission_source_id,
ghg_organization_id = x_facility_id,
transaction_type_lookup_code = x_emission_type_lookup_code,
usage_quantity = x_usage_quantity,
unit_of_measure = x_unit_of_measure,
uom_conversion = x_uom_conversion,
emission_quantity = x_emission_quantity,
energy_quantity = x_energy_quantity,
transaction_date_from = x_emission_date_from,
transaction_date_to = x_emission_date_to,
description = x_description,
org_id = x_org_id,
set_of_books_id = x_set_of_books_id,
invoice_id = x_invoice_id,
transaction_line_number = x_emission_line_number,
distribution_line_number = x_distribution_line_number,
invoice_line_number = x_invoice_line_number,
vendor_id = x_vendor_id,
vendor_site_id = x_vendor_site_id,
inventory_item_id = x_inventory_item_id,
ghg_asset_id = x_ghg_asset_id,
attribute1 = x_attribute1,
attribute2 = x_attribute2,
attribute3 = x_attribute3,
attribute4 = x_attribute4,
attribute5 = x_attribute5,
attribute6 = x_attribute6,
attribute7 = x_attribute7,
attribute8 = x_attribute8,
attribute9 = x_attribute9,
attribute10 = x_attribute10,
attribute11 = x_attribute11,
attribute12 = x_attribute12,
attribute13 = x_attribute13,
attribute14 = x_attribute14,
attribute15 = x_attribute15,
attribute16 = x_attribute16,
attribute17 = x_attribute17,
attribute18 = x_attribute18,
attribute19 = x_attribute19,
attribute20 = x_attribute20,
attribute_category = x_attribute_category,
created_by = x_created_by,
creation_date = x_creation_date,
last_updated_by = x_last_updated_by,
last_update_date = x_last_update_date,
last_update_login = x_last_update_login,
emission_scope_lookup_code = x_scope,
location_code = x_location,
measurement_criteria = x_measurement_criteria
WHERE rowid = x_rowid;
END update_row;
PROCEDURE delete_row (x_emission_id NUMBER) IS
BEGIN
DELETE FROM GHG_transactions_all
WHERE transaction_id = x_emission_id;
DELETE FROM GHG_transaction_details_all
WHERE transaction_id = x_emission_id;
END delete_row;
x_last_updated_by NUMBER,
x_last_update_date DATE,
x_last_update_login NUMBER,
x_scope VARCHAR2,
x_location VARCHAR2,
x_measurement_criteria VARCHAR2) IS
v_debug_info VARCHAR2(100);
SELECT txn_type_lookup_code,
transaction_id,
transaction_batch_id,
transaction_source_id,
ghg_organization_id,
transaction_type_lookup_code,
usage_quantity,
unit_of_measure,
uom_conversion,
emission_quantity,
energy_quantity,
transaction_date_from,
transaction_date_to,
description,
org_id,
set_of_books_id,
invoice_id,
transaction_line_number,
distribution_line_number,
invoice_line_number,
vendor_id,
vendor_site_id,
inventory_item_id,
ghg_asset_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute_category,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
emission_scope_lookup_code,
location_code
FROM GHG_transactions_all
WHERE rowid = x_rowid
FOR UPDATE of transaction_id NOWAIT;
AND ((recinfo.last_updated_by = x_last_updated_by) OR (recinfo.last_updated_by IS NULL AND x_last_updated_by IS NULL))
AND ((recinfo.last_update_date = x_last_update_date) OR (recinfo.last_update_date IS NULL AND x_last_update_date IS NULL))
AND ((recinfo.last_update_login = x_last_update_login) OR (recinfo.last_update_login IS NULL AND x_last_update_login IS NULL)) ) THEN
NULL;
SELECT sum(transaction_value)
INTO v_total
FROM GHG_transaction_details_all
where transaction_id = x_emission_id
AND transaction_type = 'E';
SELECT sum(transaction_value)
INTO v_total
FROM GHG_transaction_details_all
where transaction_id = x_emission_id
AND transaction_type = 'N';
SELECT transaction_id
FROM GHG_transactions_v
WHERE invoice_id = p_invoice_id;
SELECT ai.vendor_id ,
ai.vendor_site_id,
ai.invoice_num,
ai.gl_date,
aid.distribution_line_number,
aid.invoice_line_number,
aid.amount,
aid.dist_code_combination_id code_combination_id,
aid.project,
aid.task
FROM ap_invoices_all ai,
ap_invoice_distributions_v aid,
fnd_lookup_values xnlv
WHERE ai.invoice_id = p_invoice_id
AND aid.invoice_id = ai.invoice_id
AND xnlv.lookup_code = aid.line_type_lookup_code
AND xnlv.lookup_type = 'GHG_EMISSION_LINE_TYPES'
AND SYSDATE BETWEEN NVL(xnlv.start_date_active, SYSDATE - 1)
AND NVL(xnlv.end_date_active, SYSDATE + 1)
ORDER BY aid.distribution_line_number;
SELECT sum(aid.amount)
INTO v_invoice_item_total
FROM ap_invoices_all ai,
ap_invoice_distributions_v aid,
fnd_lookup_values xnlv
WHERE ai.invoice_id = p_invoice_id
AND aid.invoice_id = ai.invoice_id
AND xnlv.lookup_code = aid.line_type_lookup_code
AND xnlv.lookup_type = 'GHG_EMISSION_LINE_TYPES'
AND SYSDATE BETWEEN NVL(xnlv.start_date_active, SYSDATE - 1)
AND NVL(xnlv.end_date_active, SYSDATE + 1);
GHG_transactions_history_pkg.insert_row(x_transaction_id => emission_rec.transaction_id);
GHG_transactions_pkg.delete_row(x_emission_id => emission_rec.transaction_id);
delete from GHG_transaction_details_all where transaction_id = emission_rec.transaction_id;
SELECT asp.org_id,
asp.set_of_books_id,
gsob.chart_of_accounts_id
INTO v_org_id,
v_set_of_books_id,
v_chart_of_accounts_id
FROM ap_system_parameters asp,
gl_sets_of_books gsob
WHERE gsob.set_of_books_id = asp.set_of_books_id;
SELECT map_accounts_flag,
map_operating_unit_flag,
map_projects_flag
INTO v_map_accounts_flag,
v_map_operating_unit_flag,
v_map_projects_flag
FROM GHG_segment_mappings_v;
SELECT facility_id
INTO v_facility_id_for_op_unit
FROM XX_GHGAS_facility_ou_map_v;
SELECT ghg_organization_code
into v_facility
FROM GHG_organizations
WHERE ghg_organization_id = v_facility_id;
SELECT TRANSACTION_ID
FROM GHG_transactions
WHERE invoice_id = p_invoice_id;
GHG_TRANSACTIONS_HISTORY_PKG.insert_row(cancel_emission.transaction_id);
delete from GHG_transactions where transaction_id = cancel_emission.transaction_id;
delete from GHG_transaction_details_all where transaction_id = cancel_emission.transaction_id;
SELECT ood.operating_unit
INTO lp_org_id
FROM org_organization_definitions ood
WHERE ood.organization_id=p_org_id;
SELECT org_information3
INTO lp_org_id
FROM hr_organization_information
WHERE organization_id=p_org_id
AND ORG_INFORMATION_CONTEXT='Accounting Information';
select count(*)
into v_count
from GHG_transaction_batches b,
GHG_TRANSACTIONS e
where b.transaction_batch_id = e.transaction_batch_id
and b.transaction_batch_number = 'INV Issue:'||P_MATERIAL_TRX_SET_ID
and e.description = p_TRANS_REF;
delete from ghg_transaction_details d
where exists (
select 1
from GHG_transaction_batches b,
GHG_TRANSACTIONS e
where b.transaction_batch_id = e.transaction_batch_id
and b.transaction_batch_number = 'INV Issue:'||P_MATERIAL_TRX_SET_ID
and e.description = p_TRANS_REF
and e.transaction_id = d.transaction_id
);
delete from ghg_transactions e
where e.description = p_TRANS_REF
and exists (
select 1
from GHG_transaction_batches b
where b.transaction_batch_id = e.transaction_batch_id
and b.transaction_batch_number = 'INV Issue:'||P_MATERIAL_TRX_SET_ID
);
SELECT asp.org_id,
asp.set_of_books_id,
gsob.chart_of_accounts_id
into v_org_id,
v_set_of_books_id,
v_chart_of_accounts_id
FROM ap_system_parameters asp,
gl_sets_of_books gsob
WHERE gsob.set_of_books_id = asp.set_of_books_id;
select location_description
into v_facility
from ghg_assets_v
where asset_number = p_trans_source
and trunc(P_TRANS_DATE) between start_date and nvl(end_date,GHG_UTILITIES_PKG.end_date);
SELECT distinct src.source_name,
def.scope_lookup_code,
def.location_code,
vl.CONCATENATED_SEGMENTS item_code
INTO v_emission_source,
v_scope_lookup_code,
v_location_code,
v_item_number
from ghg_supplier_items def
, GHG_SOURCES src
, mtl_system_items_kfv vl
WHERE def.source_id = src.source_id
AND def.inventory_item_id = P_ITEM_ID
AND def.inventory_item_id = vl.inventory_item_id;
select unit_of_measure
into v_uom
from mtl_units_of_measure_vl
where uom_code = P_uom;
select GHG_TRANSACTIONS_S.NEXTVAL
into vl_nextval
from dual;
SELECT min(xnir.inventory_item)
into vl_item_number
FROM GHG_item_factors_v xnir,
GHG_assets xna
WHERE xnir.item_type_lookup_code = 'I'
AND sysdate BETWEEN NVL(xnir.start_date, sysdate - 1)
AND NVL(xnir.end_date, GHG_UTILITIES_PKG.end_date)
AND xna.asset_number = p_asset_num
AND xna.transport_type_lookup_code = xnir.transport_type_lookup_code;