[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select cplb.parts_loop_id,
cfrb.period_size,
cfrb.history_periods
from csp_parts_loops_b cplb,
csp_forecast_rules_b cfrb
where cplb.forecast_rule_id = cfrb.forecast_rule_id;
select decode(mmt.transaction_type_id,52,33,mmt.transaction_type_id) transaction_type_id,
mmt.inventory_item_id,
mmt.organization_id,
trunc(sysdate) - round((to_number(trunc(sysdate) - mmt.transaction_date)/l_period_size+0.5)) * l_period_size period_start_date,
mmt.subinventory_code,
sum(mmt.primary_quantity) * -1 primary_quantity
from mtl_material_transactions mmt,
csp_sec_inventories csi
where csi.parts_loop_id = l_parts_loop_id
and mmt.subinventory_code = csi.secondary_inventory_name
and mmt.organization_id = csi.organization_id
and mmt.transaction_date >= l_start_date
and mmt.transaction_type_id in (52,93)
and mmt.primary_quantity < 0
group by mmt.transaction_type_id,
mmt.inventory_item_id,
mmt.organization_id,
trunc(sysdate) - round((to_number(trunc(sysdate) - mmt.transaction_date)/l_period_size+0.5)) * l_period_size,
mmt.subinventory_code;
select cuh.inventory_item_id,
cuh.period_start_date,
cuh.transaction_type_id,
cuh.parts_loop_id,
cuh.organization_id,
sum(cuh.quantity) quantity
from csp_usage_histories cuh
where nvl(cuh.history_data_type,0) = 0
group by cuh.inventory_item_id,
cuh.period_start_date,
cuh.transaction_type_id,
cuh.parts_loop_id,
cuh.organization_id;
EXECUTE IMMEDIATE 'DELETE FROM CSP_USAGE_HISTORIES WHERE HISTORY_DATA_TYPE = 0' ;
csp_usage_histories_pkg.insert_row(
px_usage_id => l_usage_id,
p_created_by => fnd_global.user_id,
p_creation_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => null,
p_inventory_item_id => ct.inventory_item_id,
p_organization_id => ct.organization_id,
p_period_type => 2,
p_period_start_date => ct.period_start_date,
p_quantity => ct.primary_quantity,
p_request_id => null,
p_program_application_id => null,
p_program_id => null,
p_program_update_date => null,
p_subinventory_code => ct.subinventory_code,
p_transaction_type_id => ct.transaction_type_id,
p_hierarchy_node_id => null,
p_parts_loop_id => curs.parts_loop_id,
p_history_data_type => 0,
p_attribute_category => null,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null);
csp_usage_histories_pkg.insert_row(
px_usage_id => l_usage_id,
p_created_by => fnd_global.user_id,
p_creation_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => null,
p_inventory_item_id => cr.inventory_item_id,
p_organization_id => cr.organization_id,
p_period_type => 2,
p_period_start_date => cr.period_start_date,
p_quantity => cr.quantity,
p_request_id => null,
p_program_application_id => null,
p_program_id => null,
p_program_update_date => null,
p_subinventory_code => '-',
p_transaction_type_id => cr.transaction_type_id,
p_hierarchy_node_id => null,
p_parts_loop_id => cr.parts_loop_id,
p_history_data_type => 0,
p_attribute_category => null,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null);