[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cii.serial_number serial_number,
ct.transaction_quantity transaction_quantity,
ct.transaction_id transaction_id,
'TO_PROJECT' issue_type
FROM csi_item_instances cii,
csi_item_instances_h ciih,
csi_transactions ct
WHERE cii.instance_id = ciih.instance_id
AND ciih.transaction_id = ct.transaction_id
AND ct.inv_material_transaction_id = c_txn_id
AND ct.transaction_type_id IN (
cse_util_pkg.get_txn_type_id('MISC_ISSUE_TO_PROJECT','INV'),
cse_util_pkg.get_txn_type_id('MOVE_ORDER_ISSUE_TO_PROJECT','INV'))
AND NVL(ciih.new_location_type_code, cii.location_type_code)= 'PROJECT'
UNION
SELECT cii.serial_number serial_number,
ct.transaction_quantity transaction_quantity,
ct.transaction_id transaction_id,
'FROM_PROJECT' issue_type
FROM csi_item_instances cii,
csi_item_instances_h ciih,
csi_transactions ct
WHERE cii.instance_id = ciih.instance_id
AND ciih.transaction_id = ct.transaction_id
AND ct.inv_material_transaction_id = c_txn_id
AND ct.transaction_type_id IN(
cse_util_pkg.get_txn_type_id('MISC_RECEIPT_FROM_PROJECT','INV'))
AND NVL(ciih.new_location_type_code, cii.location_type_code) = 'INVENTORY'
ORDER BY 1 DESC;
SELECT mmt.transaction_quantity ,
mtt.transaction_source_type_id,
mtt.type_class,
mtt.transaction_action_id
FROM mtl_material_transactions mmt,
mtl_trx_types_view mtt
WHERE mmt.transaction_id = l_transaction_id
AND ((mmt.transaction_action_id = 1 AND mmt.transaction_source_type_id = 4)
OR
(mmt.transaction_action_id = 1 AND mmt.transaction_source_type_id = 13)
OR
(mmt.transaction_action_id = 27 AND mmt.transaction_source_type_id = 13))
AND mtt.transaction_type_id = mmt.transaction_type_id
AND mtt.type_class = 1;
SELECT name
FROM hr_organization_units hr
WHERE hr.organization_id = l_exp_org_id;
SELECT segment1
FROM pa_projects_all
WHERE project_id = l_source_project_id;
SELECT task_number
FROM pa_tasks task
WHERE task_id = l_source_task_id
AND project_id = l_source_project_id;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT schedule_close_date
FROM org_acct_periods oap
WHERE oap.organization_id = l_organization_id
AND oap.acct_period_id = l_acct_period_id;
SELECT NVL(FND_PROFILE.VALUE('CSE_EIB_COSTING_USED'),'Y')
FROM sys.dual;
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 ;
l_pa_interface_tbl(i).last_update_date := SYSDATE;
l_pa_interface_tbl(i).last_updated_by := l_user_id;
SELECT ct.transaction_id
FROM csi_transactions ct
WHERE ct.transaction_status_code = 'PENDING' ;
SELECT cii.serial_number,
cii.inventory_item_id,
cii.instance_id,
cii.inv_master_organization_id,
ct.transaction_id,
ct.object_version_number,
ct.transaction_date
FROM csi_item_instances cii,
csi_item_instances_h ciih,
csi_transactions ct
WHERE ct.transaction_id = c_transaction_id
AND ct.transaction_id = ciih.transaction_id
AND cii.instance_id = ciih.instance_id
AND cii.serial_number IS NOT NULL
AND ciih.old_inst_usage_code = 'INSTALLED'
AND ciih.new_location_type_code = 'INVENTORY' ;
SELECT old_pa_project_id,
old_pa_project_task_id
FROM csi_item_instances_h ciih
WHERE ciih.instance_id = c_instance_id
---We are looking for the immediate PROJ/TAsk info of the Receipt from Field Location transaction
AND ciih.transaction_id < c_transaction_id
AND old_location_type_code = 'PROJECT'
AND new_inst_usage_code = 'INSTALLED'
ORDER BY transaction_id DESC ;
SELECT org.name organization_name,
exp.expenditure_ending_date,
proj.segment1 project_number,
task.task_number,
item.org_id,
item.expenditure_type,
item.expenditure_item_date,
item.denom_currency_code,
item.attribute6,
item.attribute7,
item.quantity ,
item.raw_cost ,
item.denom_raw_cost ,
round(item.denom_raw_cost,2) unit_denom_raw_cost,
item.raw_cost_rate,
item.burden_cost,
round(item.burden_cost,2) burden_cost_rate,
dist.dr_code_combination_id,
dist.cr_code_combination_id,
dist.gl_date,
dist.acct_raw_cost,
item.transaction_source
FROM pa_expenditure_items_all item,
pa_cost_distribution_lines_all dist,
pa_expenditure_groups_all grp,
pa_expenditures_all exp,
pa_projects_all proj,
pa_tasks task,
hr_organization_units org
WHERE org.organization_id = NVL(item.override_to_organization_id,
exp.incurred_by_organization_id)
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 dist.line_type = 'R'
AND item.expenditure_item_id = dist.expenditure_item_id
AND grp.transaction_source IN ('CSE_PO_RECEIPT','CSE_INV_ISSUE')
AND grp.expenditure_group = exp.expenditure_group
AND exp.expenditure_id = item.expenditure_id
AND item.attribute6 = c_item_name
AND NVL(item.attribute7, 'xyz') = c_serial_number
AND item.attribute8 IS NULL
AND item.attribute9 IS NULL
AND item.attribute10 IS NULL
AND item.billable_flag = 'Y'
AND task.project_id = c_project_id
AND item.task_id = c_task_id
AND task.task_id=item.task_id
AND proj.project_id = task.project_id ;
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 sysdate INTO l_sysdate FROM sys.dual ;
l_nl_pa_interface_tbl.DELETE ;
SELECT concatenated_segments
INTO l_item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = csi_pending_txn_rec.inventory_item_id
AND organization_id = csi_pending_txn_rec.inv_master_organization_id
AND ROWNUM = 1;
SELECT csi_pa_interface_s.NEXTVAL
INTO l_ref_suffix
FROM DUAL;
l_nl_pa_interface_tbl(i).last_update_date := l_sysdate;
l_nl_pa_interface_tbl(i).last_updated_by := l_user_id;
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);