[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_row (x_rowid IN OUT NOCOPY VARCHAR2,
x_transaction_batch_id NUMBER,
x_transaction_batch_number VARCHAR2,
x_batch_type VARCHAR2,
x_batch_sub_type VARCHAR2,
x_vendor_id NUMBER,
x_vendor_site_id NUMBER,
x_description VARCHAR2,
x_status VARCHAR2,
x_reversal_date DATE,
x_reversed_transact_batch_id NUMBER,
x_org_id NUMBER,
x_set_of_books_id NUMBER,
x_created_by NUMBER,
x_creation_date DATE,
x_last_updated_by NUMBER,
x_last_update_date DATE,
x_last_update_login NUMBER,
x_called_from_api VARCHAR2 DEFAULT 'N') IS
v_debug_info VARCHAR2(100);
SELECT ROWID
FROM GHG_TRANSACTION_BATCHES_ALL
WHERE transaction_batch_id = x_transaction_batch_id;
v_debug_info := 'Inserting into GHG_TRANSACTION_BATCHES_ALL';
INSERT INTO GHG_TRANSACTION_BATCHES_ALL (transaction_batch_id,
transaction_batch_number,
batch_type,
batch_sub_type,
vendor_id,
vendor_site_id,
description,
status,
reversal_date,
reversed_transaction_batch_id,
org_id,
set_of_books_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (x_transaction_batch_id,
x_transaction_batch_number,
x_batch_type,
x_batch_sub_type,
x_vendor_id,
x_vendor_site_id,
x_description,
x_status,
x_reversal_date,
x_reversed_transact_batch_id,
x_org_id,
x_set_of_books_id,
x_created_by,
x_creation_date,
x_last_updated_by,
x_last_update_date,
x_last_update_login);
END insert_row;
PROCEDURE update_row (x_rowid IN OUT NOCOPY VARCHAR2,
x_transaction_batch_id NUMBER,
x_transaction_batch_number VARCHAR2,
x_batch_type VARCHAR2,
x_batch_sub_type VARCHAR2,
x_vendor_id NUMBER,
x_vendor_site_id NUMBER,
x_description VARCHAR2,
x_status VARCHAR2,
x_reversal_date DATE,
x_reversed_transact_batch_id NUMBER,
x_org_id NUMBER,
x_set_of_books_id NUMBER,
x_created_by NUMBER,
x_creation_date DATE,
x_last_updated_by NUMBER,
x_last_update_date DATE,
x_last_update_login NUMBER) IS
v_debug_info VARCHAR2(100);
UPDATE GHG_TRANSACTION_BATCHES_ALL
SET transaction_batch_id = x_transaction_batch_id,
transaction_batch_number = x_transaction_batch_number,
batch_type = x_batch_type,
batch_sub_type = x_batch_sub_type,
vendor_id = x_vendor_id,
vendor_site_id = x_vendor_site_id,
description = x_description,
status = x_status,
reversal_date = x_reversal_date,
reversed_transaction_batch_id = x_reversed_transact_batch_id,
org_id = x_org_id,
set_of_books_id = x_set_of_books_id,
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
WHERE rowid = x_rowid;
END update_row;
PROCEDURE delete_row (x_transaction_batch_id NUMBER) IS
v_row_count NUMBER(15);
DELETE FROM GHG_TRANSACTIONS_ALL
WHERE transaction_batch_id = x_transaction_batch_id;
DELETE FROM GHG_TRANSACTION_DETAILS_ALL a
WHERE not exists (select 1
from GHG_TRANSACTIONS_ALL b
where a.transaction_id = b.transaction_id);
DELETE FROM GHG_TRANSACTION_BATCHES_ALL
WHERE transaction_batch_id = x_transaction_batch_id;
END delete_row;
x_last_updated_by NUMBER,
x_last_update_date DATE,
x_last_update_login NUMBER) IS
v_debug_info VARCHAR2(100);
SELECT transaction_batch_id,
transaction_batch_number,
batch_type,
batch_sub_type,
vendor_id,
vendor_site_id,
description,
status,
reversal_date,
reversed_transaction_batch_id,
org_id,
set_of_books_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
FROM GHG_TRANSACTION_BATCHES_ALL
WHERE rowid = x_rowid
FOR UPDATE of transaction_batch_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;
x_last_updated_by NUMBER,
x_last_update_date DATE,
x_last_update_login NUMBER) IS
BEGIN
INSERT INTO GHG_TRANSACTIONS_ALL
(txn_type_lookup_code,
transaction_id,
transaction_batch_id,
--emission_rate_id,
transaction_source_id,
--emission_factor_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,
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)
SELECT txn_type_lookup_code,
GHG_TRANSACTIONS_S.NEXTVAL transaction_id,
x_transaction_batch_id,
--emission_rate_id,
transaction_source_id,
--emission_factor_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,
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,
x_created_by,
x_creation_date,
x_last_updated_by,
x_last_update_date,
x_last_update_login,
emission_scope_lookup_code,
location_code,
measurement_criteria
FROM GHG_TRANSACTIONS_V
WHERE transaction_batch_id = x_copy_transaction_batch_id;
FOR calc_emissions IN (SELECT transaction_id from ghg_transactions_v where
transaction_batch_id = x_transaction_batch_id)
LOOP
GHG_EMISSION_RATES_PKG.calculate_emissions(calc_emissions.transaction_id);
x_last_updated_by NUMBER,
x_last_update_date DATE,
x_last_update_login NUMBER) IS
BEGIN
INSERT INTO GHG_TRANSACTIONS_ALL
(txn_type_lookup_code,
transaction_id,
transaction_batch_id,
--emission_rate_id,
transaction_source_id,
--emission_factor_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,
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)
SELECT txn_type_lookup_code,
GHG_TRANSACTIONS_S.NEXTVAL transaction_id,
x_transaction_batch_id,
--emission_rate_id,
transaction_source_id,
--emission_factor_id,
ghg_organization_id,
transaction_type_lookup_code,
NVL(usage_quantity, 0) * -1,
unit_of_measure,
uom_conversion,
NVL(emission_quantity, 0) * -1,
NVL(energy_quantity, 0) * -1,
transaction_date_from,
transaction_date_to,
description,
org_id,
set_of_books_id,
invoice_id,
transaction_line_number,
distribution_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,
x_created_by,
x_creation_date,
x_last_updated_by,
x_last_update_date,
x_last_update_login,
emission_scope_lookup_code,
location_code,
measurement_criteria
FROM GHG_TRANSACTIONS_V
WHERE transaction_batch_id = x_reversed_transact_batch_id;
FOR calc_emissions IN (SELECT transaction_id from GHG_transactions_v where
transaction_batch_id = x_transaction_batch_id)
LOOP
GHG_EMISSION_RATES_PKG.calculate_emissions(calc_emissions.transaction_id);