[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT instance_asset_id,
instance_id,
fa_asset_id,
fa_book_type_code,
fa_location_id
FROM csi_i_assets
WHERE instance_id = p_instance_id
AND fa_asset_id = p_asset_id
AND asset_quantity > 0
AND sysdate between nvl(active_start_date, sysdate-1) and nvl(active_end_date, sysdate+1);
debug('Operational Update :-');
SELECT location_type_code,
operational_status_code,
instance_usage_code,
accounting_class_code,
quantity,
last_vld_organization_id,
object_version_number
INTO l_location_type_code,
l_operational_status_code,
l_instance_usage_code,
l_accounting_class_code,
l_quantity,
l_last_vld_organization_id,
l_object_version_number
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT object_version_number
INTO l_u_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_u_instance_rec.instance_id;
debug('Calling API csi_item_instance_pub.update_item_instance');
csi_item_instance_pub.update_item_instance(
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_rec => l_u_instance_rec,
p_party_tbl => l_u_parties_tbl,
p_account_tbl => l_u_pty_accts_tbl,
p_org_assignments_tbl => l_u_org_units_tbl,
p_ext_attrib_values_tbl => l_u_ea_values_tbl,
p_pricing_attrib_tbl => l_u_pricing_tbl,
p_asset_assignment_tbl => l_u_assets_tbl,
p_txn_rec => px_txn_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT item.expenditure_item_id,
item.project_id,
item.task_id,
item.transaction_source,
item.org_id,
item.expenditure_type,
item.expenditure_item_date,
item.denom_currency_code,
item.attribute6,
item.attribute7,
item.quantity quantity,
item.raw_cost raw_cost,
item.denom_raw_cost denom_raw_cost,
item.denom_raw_cost/item.quantity unit_denom_raw_cost,
item.raw_cost_rate,
item.burden_cost burden_cost,
item.burden_cost/item.quantity burden_cost_rate,
item.override_to_organization_id,
item.system_linkage_function,
item.orig_transaction_reference,
dist.dr_code_combination_id,
dist.cr_code_combination_id,
dist.gl_date,
dist.acct_raw_cost,
dist.system_reference1,
dist.system_reference2,
dist.system_reference3,
dist.system_reference4,
dist.system_reference5,
exp.expenditure_id,
exp.expenditure_ending_date,
exp.incurred_by_organization_id
FROM pa_expenditure_items_all item,
pa_cost_distribution_lines_all dist,
pa_expenditures_all exp
WHERE item.transaction_source IN ('CSE_PO_RECEIPT','CSE_INV_ISSUE')
AND item.project_id = p_trf_pa_attr_rec.project_id
AND item.task_id = p_trf_pa_attr_rec.task_id
AND item.attribute8 IS null
AND item.attribute9 IS null
AND item.attribute10 IS null
AND item.quantity > 0
AND item.attribute6 = l_item_name
AND nvl(item.attribute7, '**xyz**') = NVL(p_trf_pa_attr_rec.serial_number, '**xyz**')
AND nvl(item.net_zero_adjustment_flag, 'N') <> 'Y'
AND dist.expenditure_item_id = item.expenditure_item_id
AND dist.line_type = 'R'
AND nvl(dist.reversed_flag, 'N') <> 'Y'
AND dist.cr_code_combination_id IS NOT NULL
AND dist.dr_code_combination_id IS NOT NULL
AND exp.expenditure_id = item.expenditure_id;
SELECT transaction_source,
batch_name,
expenditure_ending_date,
employee_number,
organization_name,
expenditure_item_date,
project_number,
task_number,
expenditure_type,
non_labor_resource,
non_labor_resource_org_name,
quantity, raw_cost,
expenditure_comment,
transaction_status_code,
transaction_rejection_code,
expenditure_id,
orig_transaction_reference,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
raw_cost_rate,
interface_id,
unmatched_negative_txn_flag,
expenditure_item_id,
org_id,
dr_code_combination_id,
cr_code_combination_id,
cdl_system_reference1,
cdl_system_reference2,
cdl_system_reference3,
cdl_system_reference4,
cdl_system_reference5,
gl_date,
burdened_cost,
burdened_cost_rate,
system_linkage,
txn_interface_id,
user_transaction_source,
created_by,
creation_date,
last_updated_by,
last_update_date,
receipt_currency_amount,
receipt_currency_code,
receipt_exchange_rate,
denom_currency_code,
denom_raw_cost,
denom_burdened_cost,
acct_rate_date,
acct_rate_type,
acct_exchange_rate,
acct_raw_cost,
acct_burdened_cost,
acct_exchange_rounding_limit,
project_currency_code,
project_rate_date,
project_rate_type,
project_exchange_rate,
orig_exp_txn_reference1,
orig_exp_txn_reference2,
orig_exp_txn_reference3,
orig_user_exp_txn_reference,
vendor_number,
override_to_organization_name,
reversed_orig_txn_reference,
billable_flag,
person_business_group_name,
override_to_organization_id,
denom_raw_cost/quantity unit_denom_raw_cost
FROM pa_transaction_interface_all
WHERE transaction_source IN ('CSE_PO_RECEIPT','CSE_INV_ISSUE')
AND project_number = l_from_project_number
AND task_number = l_from_task_number
AND attribute8 IS NULL
AND attribute9 IS NULL
AND attribute10 IS NULL
AND quantity > 0
AND attribute6 = l_item_name
AND nvl(attribute7, '**xyz**') = NVL(p_trf_pa_attr_rec.serial_number, '**xyz**')
AND ROWNUM=1;
SELECT ho.name
FROM hr_all_organization_units ho, hr_all_organization_units hoc
WHERE hoc.organization_id = c_org_id
AND ho.organization_id = hoc.business_group_id ;
SELECT concatenated_segments
INTO l_item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_trf_pa_attr_rec.item_id
AND organization_id = p_trf_pa_attr_rec.inv_master_org_id;
SELECT segment1
INTO l_project_number
FROM pa_projects_all
WHERE project_id = p_trf_pa_attr_rec.to_project_id;
SELECT task_number
INTO l_task_number
FROM pa_tasks
WHERE task_id = p_trf_pa_attr_rec.to_task_id;
SELECT name
INTO l_organization_name
FROM hr_organization_units
WHERE organization_id =
nvl(ei_rec.override_to_organization_id, ei_rec.incurred_by_organization_id);
SELECT csi_pa_interface_s.nextval
INTO l_ref_sufix
FROM sys.dual;
SELECT segment1
INTO l_nl_pa_interface_tbl(i).vendor_number
FROM po_vendors
WHERE vendor_id = ei_rec.system_reference1;
l_nl_pa_interface_tbl(i).last_update_date := l_sysdate;
l_nl_pa_interface_tbl(i).last_updated_by := l_user_id;
SELECT csi_pa_interface_s.nextval
INTO l_ref_sufix
FROM sys.dual;
SELECT segment1
INTO l_nl_pa_interface_tbl(i).project_number
FROM pa_projects_all
WHERE project_id = p_trf_pa_attr_rec.project_id;
SELECT task_number
INTO l_nl_pa_interface_tbl(i).task_number
FROM pa_tasks
WHERE task_id = p_trf_pa_attr_rec.task_id;
SELECT segment1
INTO l_from_project_number
FROM pa_projects_all
WHERE project_id = p_trf_pa_attr_rec.project_id;
SELECT task_number
INTO l_from_task_number
FROM pa_tasks
WHERE task_id = p_trf_pa_attr_rec.task_id;
SELECT csi_pa_interface_s.nextval
INTO l_ref_sufix
FROM sys.dual;
l_nl_pa_interface_tbl(i).last_update_date := l_sysdate;
l_nl_pa_interface_tbl(i).last_updated_by := l_user_id;
SELECT csi_pa_interface_s.nextval
INTO l_ref_sufix
FROM sys.dual;
SELECT segment1
INTO l_nl_pa_interface_tbl(i).project_number
FROM pa_projects_all
WHERE project_id = p_trf_pa_attr_rec.to_project_id;
SELECT task_number
INTO l_nl_pa_interface_tbl(i).task_number
FROM pa_tasks
WHERE task_id = p_trf_pa_attr_rec.to_task_id;
select object_version_number
into l_txn_rec.object_version_number
from csi_transactions
where transaction_id = l_txn_rec.transaction_id;
debug('Calling API csi_transactions_pvt.update_transactions');
csi_transactions_pvt.update_transactions(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => l_validation_level,
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 sysdate INTO l_sysdate FROM sys.dual;
SELECT inventory_item_id,
last_vld_organization_id,
serial_number,
lot_number,
inventory_revision,
operational_status_code,
unit_of_measure,
pa_project_id,
pa_project_task_id,
last_pa_project_id,
last_pa_task_id,
owner_party_id,
owner_party_account_id,
accounting_class_code,
location_type_code,
location_id,
instance_usage_code,
operational_status_code
INTO l_t_inst_tbl(1).inventory_item_id ,
l_t_inst_tbl(1).vld_organization_id ,
l_t_inst_tbl(1).serial_number,
l_t_inst_tbl(1).lot_number,
l_t_inst_tbl(1).inventory_revision,
l_t_inst_tbl(1).operational_status_code,
l_t_inst_tbl(1).unit_of_measure,
l_project_id,
l_task_id,
l_last_project_id,
l_last_task_id,
l_owner_party_id,
l_owner_party_account_id,
l_acct_class_code,
l_location_type_code,
l_location_id,
l_instance_usage_code,
l_operational_status_code
FROM csi_item_instances
WHERE instance_id = p_instance_tbl(si_ind).instance_id;
select INV_MASTER_ORGANIZATION_ID into l_trf_pa_attr_rec.inv_master_org_id
from csi_item_instances where INSTANCE_ID = l_trf_pa_attr_rec.instance_id;