[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT object_version_number
INTO l_txn_rec.object_version_number
FROM csi_transactions
WHERE transaction_id = l_txn_rec.transaction_id;
csi_transactions_pvt.update_transactions(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_transaction_rec => l_txn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT project_id
FROM pa_projects_all
WHERE segment1 = p_project_num;
SELECT task_id
FROM pa_tasks
WHERE project_id = p_project_id
AND task_number = p_task_num;
SELECT transaction_id,
transaction_date,
transacted_by,
transaction_quantity,
source_transaction_date,
object_version_number,
message_id,
source_header_ref_id project_id,
source_line_ref_id task_id
FROM csi_transactions
WHERE transaction_type_id = 108
AND transaction_status_code = cse_datastructures_pub.g_pending
AND source_header_ref_id = nvl(p_project_id, source_header_ref_id)
AND source_line_ref_id = nvl(p_task_id, source_line_ref_id);
SELECT ciih.instance_id,
cii.inventory_item_id,
cii.last_vld_organization_id,
cii.lot_number,
cii.serial_number,
cii.inventory_revision,
cii.last_pa_project_id,
cii.last_pa_task_id,
cii.quantity,
cii.location_type_code,
cii.location_id,
cii.operational_status_code
FROM csi_item_instances_h ciih,
csi_item_instances cii
WHERE ciih.transaction_id = p_csi_txn_id
AND cii.instance_id = ciih.instance_id
AND (cii.operational_status_code = 'IN_SERVICE' OR ciih.new_operational_status_code = 'IN_SERVICE');
SELECT org_id
INTO l_in_srv_pa_attr_rec.org_id
FROM pa_projects_all
WHERE project_id = l_project_id;
SELECT transaction_error_id
INTO l_txn_error_id
FROM csi_txn_errors
WHERE transaction_id = inservice_txn_rec.transaction_id
AND source_type = 'CSENIISEI'
AND rownum = 1;
UPDATE csi_txn_errors
SET error_text = l_error_message,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE transaction_error_id = l_txn_error_id;
SELECT transaction_error_id
INTO l_txn_error_id
FROM csi_txn_errors
WHERE transaction_id = inservice_txn_rec.transaction_id
AND source_type = 'CSENIISEI'
AND rownum = 1;
UPDATE csi_txn_errors
SET error_text = l_error_message,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE transaction_error_id = l_txn_error_id;
SELECT pei.expenditure_item_id expenditure_item_id,
pei.quantity quantity,
pei.Task_Id task_id,
pei.attribute6 attribute6,
pei.attribute7 attribute7,
pei.attribute8 attribute8,
pei.attribute9 attribute9,
pei.attribute10 attribute10
FROM pa_expenditure_items_all pei
WHERE pei.project_id = p_project_id
AND pei.task_Id = p_task_id
AND pei.transaction_source IN ('CSE_PO_RECEIPT', 'CSE_INV_ISSUE')
AND substr(pei.orig_transaction_reference,1,
instr(pei.orig_transaction_reference,'-') -1) = to_char(p_instance_id)
AND (pei.Attribute8 IS NOT NULL AND pei.Attribute9 IS NOT NULL)
AND pei.billable_flag ='Y'
AND nvl(pei.crl_asset_creation_status_code,'N') <> 'Y'
AND not exists (
SELECT 'This CDL was summarized before'
FROM pa_project_asset_line_details pald,
pa_project_asset_lines pal
WHERE pald.expenditure_item_id = pei.expenditure_item_id
AND pald.project_asset_line_detail_id = pal.project_asset_line_detail_id
AND pal.project_asset_id >= 1);
SELECT segment1,
name,
org_id
INTO l_project_num,
l_project_name,
l_org_id
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT task_number,
task_name,
attribute10
INTO l_task_num,
l_task_name,
l_task_attribute10
FROM pa_tasks
WHERE project_Id = p_project_id
AND task_id = p_task_id;
SELECT asset_name,
asset_description1,
asset_description2,
asset_description3,
asset_desc_separator,
asset_location,
asset_category
INTO l_anc_name,
l_anc_desc1,
l_anc_desc2,
l_anc_desc3,
l_anc_sep,
l_anc_loc,
l_anc_cat
FROM ipa_asset_naming_convents_all
WHERE org_id = l_org_id;
SELECT ppa.project_asset_id,
ppa.date_placed_in_service,
ppa.asset_units
INTO l_pa_project_asset_id,
l_date_placed_in_service,
l_asset_units
FROM pa_project_asset_assignments ppaa,
pa_project_assets_all ppa
WHERE ppaa.project_id = p_project_id
AND ppaa.task_Id = p_task_id
AND ppaa.project_asset_id = ppa.project_asset_id
AND nvl(ppaa.Attribute6, '**##**') = nvl(exp_line_rec.attribute6, '**##**')
AND nvl(ppaa.Attribute7, '**##**') = nvl(exp_line_rec.attribute7, '**##**')
AND nvl(ppaa.Attribute8, '**##**') = nvl(exp_line_rec.attribute8, '**##**')
AND nvl(ppaa.Attribute9, '**##**') = nvl(exp_line_rec.attribute9, '**##**')
AND nvl(ppaa.Attribute10,'**##**') = nvl(exp_line_rec.attribute10,'**##**');
SELECT csi_pa_interface_s.nextval
INTO l_suffix
FROM sys.dual;
SELECT decode(l_anc_desc1,
'ADT',l_task_name,
'ADP',l_project_name,
'ADGE1',exp_line_rec.attribute8,
'ADGE2',exp_line_rec.attribute9,
'ADGE3',exp_line_rec.attribute10)||
decode(l_anc_desc2,'None',null,l_anc_sep)||
decode(l_anc_desc2,
'ADT',l_task_name,
'ADP',l_project_name,
'ADGE1',exp_line_rec.attribute8,
'ADGE2',exp_line_rec.attribute9,
'ADGE3',exp_line_rec.attribute10)||
decode(l_anc_desc3,'None',null,l_anc_sep)||
decode(l_anc_desc3,
'ADT',l_task_name,
'ADP',l_project_name,
'ADGE1',exp_line_rec.attribute8,
'ADGE2',exp_line_rec.attribute9,
'ADGE3',exp_line_rec.attribute10)||
decode(exp_line_rec.attribute6,null,null,l_anc_sep||exp_line_rec.attribute6)||
decode(exp_line_rec.attribute7,null,null,l_anc_sep||exp_line_rec.attribute7)
INTO l_asset_description
FROM SYS.dual;
SELECT category_id
INTO l_asset_category_id
FROM fa_categories
WHERE upper(segment1||segment2||segment3||segment4||segment5||segment6||segment7) =
upper(l_asset_category);
SELECT location_id
INTO l_asset_location_id
FROM fa_locations
WHERE upper(segment1||segment2||segment3||segment4||segment5||segment6||segment7) =
upper(l_asset_location);
SELECT accounting_flex_structure
INTO l_acc_flex_structure
FROM fa_book_controls
WHERE book_type_code = l_book_type_code;
debug('processing_mode : UPDATE');
UPDATE pa_project_assets_all
SET asset_units = asset_units + exp_line_rec.quantity,
date_placed_in_service = nvl(date_placed_in_service, l_date_placed_in_service),
project_asset_type = 'AS-BUILT'
WHERE project_asset_id = l_pa_project_asset_id;
UPDATE pa_expenditure_items_all
SET crl_asset_creation_status_code = 'Y'
WHERE expenditure_item_id = exp_line_rec.expenditure_item_id;
SELECT transaction_id,
transacted_by,
transaction_quantity,
source_transaction_date dpis,
source_header_ref_id project_id,
source_line_ref_id task_id,
object_version_number csi_txn_ovn
FROM csi_transactions
WHERE transaction_type_id = 108 -- PROJECT_ITEM_IN_SERVICE
AND transaction_status_code = 'INTERFACED_TO_PA'
AND source_header_ref_id = nvl(p_project_id, source_header_ref_id)
AND source_line_ref_id = nvl(p_task_id, source_line_ref_id) ;
SELECT cii.instance_id,
cii.serial_number,
cii.last_pa_project_id, --Added for Bug 9326077
cii.last_pa_task_id --Added for Bug 9326077
FROM csi_item_instances_h ciih,
csi_item_instances cii
WHERE ciih.transaction_id = p_csi_txn_id
AND cii.instance_id = ciih.instance_id
AND (ciih.new_operational_status_code = 'IN_SERVICE' OR cii.operational_status_code = 'IN_SERVICE');
SELECT start_date
INTO l_dpis
FROM fa_book_controls fbc,
fa_calendar_periods fcp
WHERE fbc.book_type_code = l_book_type_code
AND fcp.calendar_type = fbc.deprn_calendar
AND trunc(insrv_txn_rec.dpis,'DDD') BETWEEN fcp.start_date AND fcp.end_date;
PROCEDURE update_units(
x_return_status OUT nocopy varchar2,
x_error_message OUT nocopy varchar2,
p_conc_request_id IN number)
IS
l_asset_id number;
SELECT transaction_error_id,
transaction_type_id,
message_string
FROM csi_txn_errors
WHERE processed_flag = 'B'
AND error_stage = 'FA_UPDATE'
AND source_type = 'FA_UNIT_ADJUSTMENT_NORMAL';
debug('inside api cse_fac_inservice_pkg.update_units');
SELECT instance_asset_id,
asset_quantity,
object_version_number
INTO l_instance_asset_id,
l_asset_quantity,
l_object_version_number
FROM csi_i_assets
WHERE instance_id = l_instance_id
AND fa_asset_id = l_asset_id
AND sysdate between nvl(active_start_date, sysdate-1) and nvl(active_end_date, sysdate+1);
l_inst_asset_rec.update_status := cse_datastructures_pub.g_in_service;
debug('calling csi_asset_pvt.update_instance_asset');
csi_asset_pvt.update_instance_asset(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_asset_rec => l_inst_asset_rec,
p_txn_rec => l_csi_txn_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_lookup_tbl => l_lookup_tbl,
p_asset_count_rec => l_asset_count_rec,
p_asset_id_tbl => l_asset_id_tbl,
p_asset_loc_tbl => l_asset_loc_tbl);
UPDATE csi_txn_errors
SET processed_flag = 'S',
source_group_ref_id = fnd_global.conc_request_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE transaction_error_id = txn_rec.transaction_error_id;
UPDATE csi_txn_errors
SET error_text = x_error_message,
source_group_ref_id = fnd_global.conc_request_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
WHERE transaction_error_id = txn_rec.transaction_error_id;
END Update_Units;