[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fa_location_id
FROM csi_a_locations
WHERE location_table in (p_location_table , 'LOCATION_CODES')
AND location_id = p_location_id
AND sysdate BETWEEN nvl(active_start_date, sysdate - 1)
AND nvl(active_end_date, sysdate + 1);
SELECT 'Y' INTO l_hz_or_hr
FROM hz_locations
WHERE location_id = p_location_id;
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,
item.vendor_id,
item.po_line_id,
item.inventory_item_id,
item.document_type,
item.document_distribution_type
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_in_srv_pa_attr_rec.project_id
AND item.task_id = p_in_srv_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_in_srv_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
AND item.expenditure_item_id NOT IN (SELECT NVL(adjusted_expenditure_item_id,0) --Added for bug 9263804 expenditures already reversed should not be considered
FROM pa_transaction_interface_all
WHERE transaction_source IN ('CSE_PO_RECEIPT','CSE_INV_ISSUE')
AND attribute8 IS null
AND attribute9 IS null
AND attribute10 IS null);
SELECT csi_pa_interface_s.nextval
INTO l_ref_suffix_inner -- Changes for bug 7368371
FROM sys.dual;
SELECT segment1
INTO x_nl_pa_interface_rec.vendor_number
FROM po_vendors
WHERE vendor_id = p_ei_rec.system_reference1;
x_nl_pa_interface_rec.last_update_date := sysdate;
x_nl_pa_interface_rec.last_updated_by := fnd_global.user_id;
SELECT concatenated_segments,
serial_number_control_code
INTO l_item_name,
l_serial_code
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_in_srv_pa_attr_rec.item_id
AND organization_id = p_in_srv_pa_attr_rec.inv_master_org_id;
SELECT segment1, org_id -- Bug 6492235, changed to support multiple FA book
INTO l_project_number, l_ou_id -- Bug 6492235, changed to support multiple FA book
FROM pa_projects_all
WHERE project_id = p_in_srv_pa_attr_rec.project_id;
SELECT task_number
INTO l_task_number
FROM pa_tasks
WHERE task_id = p_in_srv_pa_attr_rec.task_id;
SELECT source_transaction_date
INTO l_dpis
FROM csi_transactions
WHERE transaction_id = p_in_srv_pa_attr_rec.transaction_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;
--update transaction record with new txn_status_code = 'INTERFACE_TO_PA'
l_txn_rec := CSE_UTIL_PKG.init_txn_rec;
SELECT object_version_number
INTO l_txn_rec.object_version_number
FROM csi_transactions
WHERE transaction_id = p_in_srv_pa_attr_rec.transaction_id;
debug('Inside 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);