[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT resource_type
INTO l_resource_type
FROM bom_resources
WHERE organization_id = p_org_id
AND resource_id = p_resource_id;
SELECT def_eam_cost_element_id
into l_eam_cost_element
FROM wip_eam_parameters
WHERE organization_id = p_org_id;
SELECT maint_cost_category,
organization_id
INTO l_dept_cost_catg,
l_organization_id
FROM bom_departments
WHERE department_id = p_department_id;
SELECT def_maint_cost_category
INTO l_dept_cost_catg
FROM wip_eam_parameters
WHERE organization_id = l_organization_id;
SELECT bos.operation_seq_num operation_seq_num,
decode(br.functional_currency_flag,
1, 1,
NVL(crc.resource_rate,0))
* bomres.usage_rate_or_amount
* decode(bomres.basis_type,
1, l_lot_size,
2, 1,
1) raw_resource_value,
ROUND(decode(br.functional_currency_flag,
1, 1,
NVL(crc.resource_rate,0))
* bomres.usage_rate_or_amount
* decode(bomres.basis_type,
1, l_lot_size,
2, 1,
1) ,l_ext_precision) resource_value,
bomres.resource_id resource_id,
bomres.resource_seq_num resource_seq_num,
bomres.basis_type basis_type,
bomres.usage_rate_or_amount
* decode(bomres.basis_type,
1, l_lot_size,
2, 1,
1) usage_rate_or_amount,
bomres.standard_rate_flag standard_flag,
bos.department_id department_id,
br.functional_currency_flag functional_currency_flag,
br.cost_element_id cost_element_id,
br.resource_type resource_type
FROM bom_operational_routings bor,
bom_operation_resources bomres,
bom_operation_sequences bos,
bom_resources br,
cst_resource_costs crc
WHERE
bor.assembly_item_id = p_activity_item_id
AND bor.organization_id = p_organization_id
AND bor.pending_from_ecn IS NULL
AND bor.routing_type = 1
AND ( NVL(bor.alternate_routing_designator, 'none')
=NVL(p_alt_rtg_designator, 'none')
OR (
(p_alt_rtg_designator IS NOT NULL)
AND (bor.alternate_routing_designator IS NULL)
AND NOT EXISTS
(SELECT 'X'
FROM bom_operational_routings bor1
WHERE bor1.assembly_item_id = bor.assembly_item_id
AND bor1.organization_id = p_organization_id
AND bor1.pending_from_ecn is NULL
AND bor1.alternate_routing_designator =
p_alt_rtg_designator
AND bor1.routing_type = 1
)
)
)
AND bos.implementation_date IS NOT NULL
AND bos.routing_sequence_id =
bor.common_routing_sequence_id
AND bos.effectivity_date <= l_effective_datetime
AND NVL( bos.disable_date, l_effective_datetime + 1)
> l_effective_datetime
AND NVL( bos.eco_for_production, 2 ) = 2
AND bomres.operation_sequence_id = bos.operation_sequence_id
AND NVL( bomres.acd_type, 1 ) <> 3
AND br.resource_id = bomres.resource_id
AND br.organization_id = p_organization_id
AND br.allow_costs_flag = 1
AND crc.resource_id = bomres.resource_id
AND crc.cost_type_id = l_rates_ct;
SELECT cdo.overhead_id ovhd_id,
cdo.rate_or_amount actual_cost,
cdo.basis_type basis_type,
ROUND(cdo.rate_or_amount *
decode(cdo.basis_type,
3, p_res_units,
p_res_value), l_ext_precision) rbo_value,
cdo.department_id
FROM cst_resource_overheads cro,
cst_department_overheads cdo
WHERE cdo.department_id = p_dept_id
AND cdo.organization_id = p_org_id
AND cdo.cost_type_id = l_rates_ct
AND cdo.basis_type IN (3,4)
AND cro.cost_type_id = cdo.cost_type_id
AND cro.resource_id = p_resource_id
AND cro.overhead_id = cdo.overhead_id
AND cro.organization_id = cdo.organization_id;
SELECT bic.operation_seq_num operation_seq_num,
bos.department_id department_id,
ROUND (SUM(NVL(component_quantity,0) *
DECODE(msi.stock_enabled_flag,
'N',decode(msi.eam_item_type,
3,decode(wep.issue_zero_cost_flag,
'Y', 0,
NVL(bic.unit_price,0)),
NVL(bic.unit_price,0)),
decode(msi.eam_item_type,
3,decode(wep.issue_zero_cost_flag,
'Y', 0,
NVL(ccicv.item_cost,0)),
NVL(ccicv.item_cost,0))
)
), l_ext_precision
) mat_value
FROM bom_bill_of_materials bbom,
bom_inventory_components bic,
cst_cg_item_costs_view ccicv,
bom_operational_routings bor,
bom_operation_sequences bos,
mtl_system_items_b msi,
wip_eam_parameters wep
WHERE bbom.organization_id = p_organization_id
AND bbom.assembly_item_id = p_activity_item_id
AND bbom.assembly_type = 1
AND ( (bbom.Alternate_bom_designator IS NULL
AND p_alt_bom_designator IS NULL)
OR
(p_alt_bom_designator IS NOT NULL
AND
bbom.alternate_bom_designator = p_alt_bom_designator)
OR ((p_alt_bom_designator IS NOT NULL)
AND (bbom.alternate_bom_designator IS NULL)
AND NOT EXISTS
(SELECT 'X'
FROM bom_bill_of_materials bbom1
WHERE bbom1.assembly_item_id = bbom.assembly_item_id
AND bbom1.organization_id = bbom.organization_id
AND bbom1.alternate_bom_designator
= p_alt_bom_designator)
)
)
AND bor.organization_id = p_organization_id
AND bor.assembly_item_id = p_activity_item_id
AND bor.pending_from_ecn IS NULL
AND bor.routing_type = 1
AND ( NVL(bor.alternate_routing_designator, 'none')
=NVL(p_alt_rtg_designator, 'none')
OR (
(p_alt_rtg_designator IS NOT NULL)
AND (bor.alternate_routing_designator IS NULL)
AND NOT EXISTS
(SELECT 'X'
FROM bom_operational_routings bor1
WHERE bor1.assembly_item_id = bor.assembly_item_id
AND bor1.organization_id = p_organization_id
AND bor1.pending_from_ecn is NULL
AND bor1.alternate_routing_designator =
p_alt_rtg_designator
AND bor1.routing_type = 1
)
)
)
AND bos.implementation_date IS NOT NULL
AND bos.routing_sequence_id =
bor.common_routing_sequence_id
AND bos.effectivity_date <= l_effective_datetime
AND NVL( bos.disable_date, l_effective_datetime + 1)
> l_effective_datetime
AND NVL( bos.eco_for_production, 2 ) = 2
AND bos.operation_seq_num = bic.operation_seq_num
AND bic.bill_sequence_id = bbom.common_bill_sequence_id
AND NVL(bic.acd_type,1) <> 3
AND NVL(bic.eco_for_production,2) = 2
AND bic.wip_supply_type IN (1,4)
AND (bic.effectivity_date <=
fnd_date.canonical_to_date(p_effective_datetime))
AND NVL(bic.disable_date,
fnd_date.canonical_to_date(p_effective_datetime)+1) >
fnd_date.canonical_to_date(p_effective_datetime)
AND ccicv.inventory_item_id(+) = bic.component_item_id
AND ccicv.organization_id(+) = p_organization_id
AND ccicv.cost_group_id(+) = decode(l_primary_cost_method,1,1,
l_cost_group_id)
AND msi.inventory_item_id = bic.component_item_id
AND msi.organization_id = p_organization_id
AND wep.organization_id = p_organization_id
GROUP BY bic.operation_seq_num, bos.department_id;
SELECT NVL(eam_item_type,-1)
INTO l_eam_item_type
FROM mtl_system_items msi
WHERE msi.organization_id = p_organization_id
AND msi.inventory_item_id = p_activity_item_id;
SELECT NVL(default_cost_group_id,-1)
INTO l_cost_group_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT decode (mp.primary_cost_method,
1, mp.primary_cost_method,
NVL(mp.avg_rates_cost_type_id,-1)),
mp.primary_cost_method
INTO l_rates_ct,
l_primary_cost_method
FROM mtl_parameters mp
WHERE mp.organization_id = p_organization_id;
SELECT lot_size
INTO l_lot_size
FROM cst_item_costs cic
WHERE cic.organization_id = p_organization_id
AND cic.inventory_item_id = p_activity_item_id
AND cic.cost_type_id = l_rates_ct;
SELECT cst_eam_activity_estimates_s.nextval
INTO l_activity_estimate_record_id
FROM DUAL;
INSERT INTO cst_eam_activity_estimates (
activity_estimate_record_id,
record_type,
organization_id,
activity_item_id,
eam_cost_element,
maint_cost_category,
cost_value)
VALUES (
l_activity_estimate_record_id,
'D',
l_ActivityEstimateTable(j).organization_id,
l_ActivityEstimateTable(j).activity_item_id,
l_ActivityEstimateTable(j).eam_cost_element,
l_ActivityEstimateTable(j).maint_cost_catg,
l_ActivityEstimateTable(j).cost_value
);
INSERT INTO cst_eam_activity_estimates (
activity_estimate_record_id,
record_type,
organization_id,
activity_item_id,
eam_cost_element,
maint_cost_category,
cost_value)
SELECT l_activity_estimate_record_id,
'S' record_type,
organization_id organization_id,
activity_item_id activity_item_id,
eam_cost_element eam_cost_element,
maint_cost_category maint_cost_category,
SUM(cost_value) cost_value
FROM cst_eam_activity_estimates caet
GROUP BY l_activity_estimate_record_id,
record_type,
organization_id,
activity_item_id,
eam_cost_element,
maint_cost_category;