[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sum(cic.material_cost)
INTO l_items_cost
FROM cst_item_costs cic,
eng_revised_items eri
WHERE eri.change_notice = p_change_notice
AND eri.organization_id = p_org_id
AND cic.inventory_item_id = eri.revised_item_id
AND cic.organization_id = eri.organization_id
AND cic.cost_type_id = 1;
deleted, then the cost will be deducted from the cost of the assembly.
Cost of an item will be ( planning % of Quantity ) * material cost of that item.
Parameters: change_notice varchar2(10)
org_id number;
SELECT eri.revised_item_id,
sum (cic.material_cost * (
( DECODE( (bic1.planning_factor - bic2.planning_factor), 0,
bic1.planning_factor,
(bic1.planning_factor - bic2.planning_factor)
) *
( decode( bic1.acd_type,
1, -bic1.component_quantity,
2, (bic2.component_quantity - bic1.component_quantity),
3, bic2.component_quantity
)
)
) /100
)
) item_cost
from bom_inventory_components bic1,
bom_inventory_components bic2,
eng_revised_items eri,
mtl_system_items msi,
cst_item_costs cic
where eri.change_notice = p_change_notice
and eri.organization_id = p_org_id
and bic1.bill_sequence_id = eri.bill_sequence_id
and bic1.revised_item_sequence_id = eri.revised_item_sequence_id
and bic1.change_notice = eri.change_notice
and bic1.implementation_date is null
and bic2.bill_sequence_id = eri.bill_sequence_id
and ( ( bic1.acd_type in (2,3) and
bic2.component_sequence_id = bic1.old_component_sequence_id
)
or
bic1.acd_type = 1 and
bic2.component_sequence_id = bic1.component_sequence_id
)
and msi.inventory_item_id = bic1.component_item_id
and msi.organization_id = eri.organization_id
and cic.inventory_item_id = bic1.component_item_id
and cic.organization_id = eri.organization_id
and cic.cost_type_id = 1
group by eri.revised_item_id;
l_repetative_cost_table.DELETE(idx);
/* Procedure: Insert_Into_Temp_Table
Description:
Will insert the passed data into the ENG_BIS_ECO_COST_TEMP table.
Return: None
*/
PROCEDURE Insert_Into_Temp_Table (p_eco_cost IN NUMBER,
p_total_cost_saving IN NUMBER,
p_demand_quantity IN NUMBER,
p_period_name_year IN VARCHAR2,
p_period_start_date IN DATE,
p_change_notice IN VARCHAR2,
p_org_id IN NUMBER ,
p_query_id IN NUMBER
)
IS
BEGIN
INSERT INTO BOM_FORM_QUERY
( number1,
number2,
number4,
char1,
date1,
char2,
number3,
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by
)
VALUES ( p_eco_cost,
p_demand_quantity,
p_total_cost_saving,
p_period_name_year,
p_period_start_date,
p_change_notice,
p_org_id,
p_query_id,
sysdate,
1,
sysdate,
1
);
END Insert_Into_Temp_Table;
-- Cursor will select periods between the demand_date and demand completion date
-- This will then be used to spread the rate based demand.
--
CURSOR cal_periods IS
SELECT period_start_date, next_date, period_name
FROM bom_org_cal_periods_view
WHERE organization_id = p_org_id
AND next_date > p_demand_date
AND period_start_date <= p_comp_date;
SELECT nvl(eco.estimated_mfg_cost, 0) mfg_cost,
nvl(eco.estimated_eng_cost, 0) eng_cost,
bom.assembly_type
FROM eng_engineering_changes eco,
eng_revised_items eri,
bom_bill_of_materials bom
WHERE eco.change_notice = p_change_notice
AND eco.organization_id = p_org_id
AND eri.change_notice = eco.change_notice
AND eri.organization_id = eco.organization_id
AND bom.bill_sequence_id = eri.bill_sequence_id;
SELECT period_start_date, next_date, period_name
FROM bom_org_cal_periods_view
WHERE organization_id = p_org_id
AND next_date >= p_start_date
AND period_start_date <= p_end_date;
SELECT using_requirements_quantity,
using_assembly_demand_date,
assembly_demand_comp_date,
daily_demand_rate,
revised_item_id
FROM eng_revised_items eri,
mrp_gross_requirements mgr
WHERE eri.change_notice = cp_change_notice
AND eri.organization_id = cp_org_id
AND mgr.compile_designator = cp_plan_name
AND mgr.organization_id = eri.organization_id
AND mgr.inventory_item_id = eri.revised_item_id
AND mgr.using_assembly_demand_date >= cp_start_date
AND mgr.using_assembly_demand_date < cp_end_date;
--dbms_output.put_line('Inserting data . . .');
Insert_Into_Temp_Table(p_eco_cost => l_cost_of_changes,
p_total_cost_saving => (l_estimated_cost + l_cost_of_changes),
p_demand_quantity => l_required_quantity,
p_period_name_year => (c_calendar_periods.period_name) || '/' ||
to_char(c_calendar_periods.period_start_date,
'YYYY'),
p_period_start_date => c_calendar_periods.period_start_date,
p_change_notice => p_change_notice,
p_org_id => p_org_id,
p_query_id => p_query_id
);