[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 1: Merge and update |
| 2: New cost only |
| 3: remove and replace |
| p_range: |
| 1: All items |
| 2: Specific Item |
| 5: Category Items |
| |
| This procedure defaults the following values: |
| |
| CST_ITEM_COSTS.defaulted_flag = 2 (Do not use default cost controls) |
| CST_ITEM_COSTS.cost_update_id = NULL |
| CST_ITEM_COSTS.based_on_rollup_flag = 1 |
| CST_ITEM_COST_DETAILS.basis_factor = 1 |
| CST_ITEM_COST_DETAILS.rollup_source_type = 1(User defined) |
| |
*----------------------------------------------------------------------------*/
PROCEDURE copy_item_period_cost(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_legal_entity IN NUMBER,
p_from_cost_type_id IN NUMBER,
p_from_cost_group_id IN NUMBER,
p_period_id IN NUMBER,
p_to_org_id IN NUMBER,
p_to_cost_type_id IN NUMBER,
p_material IN NUMBER,
p_material_overhead IN NUMBER,
p_resource IN NUMBER,
p_outside_processing IN NUMBER,
p_overhead IN NUMBER,
p_copy_option IN NUMBER,
p_range IN NUMBER,
p_item_dummy IN NUMBER,
p_category_dummy IN NUMBER,
p_specific_item_id IN NUMBER,
p_category_set_id IN NUMBER,
p_category_validate_flag IN VARCHAR2,
p_category_structure IN NUMBER,
p_category_id IN NUMBER,
p_last_updated_by IN NUMBER,
p_full_validate IN NUMBER)
IS
-- Local PL/SQL variable
--
cst_fail_uomconvert EXCEPTION;
SELECT nvl(process_enabled_flag,'N')
, organization_code
INTO l_process_enabled_flag
, l_organization_code
FROM mtl_parameters
WHERE organization_id = p_to_org_id;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Last Updated by: '
|| TO_CHAR(p_last_updated_by));
SELECT 1
INTO l_valid
FROM cst_le_cost_types
WHERE legal_entity = p_legal_entity;
select 1
into l_valid
from cst_cost_types cct
where cost_type_id = p_from_cost_type_id
and cost_type_id in
(select distinct clct.cost_type_id
from cst_le_cost_types clct
where clct.legal_entity = p_legal_entity
)
and nvl(cct.disable_date, sysdate+1) > sysdate;
select 1
into l_valid
from dual
where p_from_cost_group_id in
(select distinct cost_group
from cst_cost_groups
where legal_entity = p_legal_entity
)
and exists
(select 1
from cst_pac_periods cpp
where legal_entity = p_legal_entity
and cost_type_id = p_from_cost_type_id
and cpp.pac_period_id in
(select cppp.pac_period_id
from cst_pac_process_phases cppp
where cppp.cost_group_id = p_from_cost_group_id
and cppp.pac_period_id = cpp.pac_period_id
and process_phase = 5
and process_status = 4
)
);
select 1
into l_valid
from dual
where p_period_id in
(select distinct pac_period_id
from cst_pac_process_phases
where cost_group_id = p_from_cost_group_id
and process_phase = 5
and process_status = 4
);
select 1
into l_valid
from dual
where p_to_org_id in
(
(select distinct CCGA.organization_id
from cst_cost_group_assignments CCGA
where CCGA.cost_group_id = p_from_cost_group_id
)
UNION
(select distinct CCG.organization_id
from cst_cost_groups CCG
where CCG.cost_group_id = p_from_cost_group_id
)
);
select 1
into l_valid
from dual
where p_to_cost_type_id in
(select distinct cost_type_id
from cst_cost_types
where organization_id = p_to_org_id
and cost_type_id not in (1,2,3)
and nvl(allow_updates_flag,1) = 1
and nvl(disable_date, sysdate+1) > sysdate
)
and p_to_cost_type_id not in
(select distinct cost_type_id
from cst_le_cost_types
);
select 1
into l_valid
from dual
where p_material in
(select resource_id
from bom_resources
where organization_id = p_to_org_id
and cost_element_id = 1
and default_basis_type = 1
and nvl(disable_date, sysdate+1) > sysdate
and nvl(allow_costs_flag,1) = 1
);
select 1
into l_valid
from dual
where p_material_overhead in
(select resource_id
from bom_resources
where organization_id = p_to_org_id
and cost_element_id = 2
and default_basis_type = 1
and nvl(disable_date, sysdate+1) > sysdate
and nvl(allow_costs_flag,1) = 1
);
select 1
into l_valid
from dual
where p_resource in
(select resource_id
from bom_resources
where organization_id = p_to_org_id
and cost_element_id = 3
and default_basis_type = 1
and nvl(disable_date, sysdate+1) > sysdate
and nvl(allow_costs_flag,1) = 1
);
select 1
into l_valid
from dual
where p_outside_processing in
(select resource_id
from bom_resources
where organization_id = p_to_org_id
and cost_element_id = 4
and default_basis_type = 1
and nvl(disable_date, sysdate+1) > sysdate
and nvl(allow_costs_flag,1) = 1
);
select 1
into l_valid
from dual
where p_overhead in
(select resource_id
from bom_resources
where organization_id = p_to_org_id
and cost_element_id = 5
and default_basis_type = 1
and nvl(disable_date, sysdate+1) > sysdate
and nvl(allow_costs_flag,1) = 1
);
select 1
into l_valid
from dual
where p_copy_option in (1,2,3);
select 1
into l_valid
from dual
where p_range in (1,2,5);
SELECT organization_id
INTO l_master_org_id
FROM cst_cost_groups ccg
WHERE ccg.cost_group_id = p_from_cost_group_id;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Merge and Update Costs');
DELETE cst_item_cost_details CICD
WHERE CICD.cost_type_id = p_to_cost_type_id
AND CICD.organization_id = p_to_org_id
AND (p_range = 1
OR
(p_range = 2 AND CICD.inventory_item_id = p_specific_item_id)
OR
EXISTS
(SELECT NULL
FROM mtl_item_categories MIC
WHERE MIC.organization_id = l_master_org_id
AND MIC.category_id = p_category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.inventory_item_id = CICD.inventory_item_id
AND p_range = 5)
)
AND EXISTS
(SELECT NULL
FROM cst_pac_item_cost_details cpicd,
cst_pac_item_costs cpic
WHERE cpicd.cost_layer_id = cpic.cost_layer_id
AND cpic.cost_group_id = p_from_cost_group_id
AND cpic.pac_period_id = p_period_id
AND cpic.inventory_item_id = cicd.inventory_item_id);
||' Rows deleted from CICD');
SELECT CST_LISTS_S.NEXTVAL INTO l_grp_id
FROM dual;
INSERT INTO cst_item_costs_interface
( inventory_item_id
, cost_type_id
, based_on_rollup_flag
, group_id
)
SELECT
inventory_item_id
, p_to_cost_type_id
, based_on_rollup_flag
, l_grp_id
FROM cst_item_costs CIC
WHERE CIC.cost_type_id = p_to_cost_type_id
AND CIC.organization_id = p_to_org_id
AND (p_range = 1
OR
(p_range = 2 AND CIC.inventory_item_id = p_specific_item_id)
OR
EXISTS
(SELECT NULL
FROM mtl_item_categories MIC
WHERE MIC.organization_id = l_master_org_id
AND MIC.category_id = p_category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.inventory_item_id = CIC.inventory_item_id
AND p_range = 5)
)
AND EXISTS
(SELECT NULL
FROM cst_pac_item_costs cpic
WHERE cpic.cost_group_id = p_from_cost_group_id
AND cpic.pac_period_id = p_period_id
AND cpic.inventory_item_id = cic.inventory_item_id);
DELETE cst_item_costs CIC
WHERE CIC.cost_type_id = p_to_cost_type_id
AND CIC.organization_id = p_to_org_id
AND (p_range = 1
OR
(p_range = 2 AND CIC.inventory_item_id = p_specific_item_id)
OR
EXISTS
(SELECT NULL
FROM mtl_item_categories MIC
WHERE MIC.organization_id = l_master_org_id
AND MIC.category_id = p_category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.inventory_item_id = CIC.inventory_item_id
AND p_range = 5)
)
AND EXISTS
(SELECT NULL
FROM cst_pac_item_costs cpic
WHERE cpic.cost_group_id = p_from_cost_group_id
AND cpic.pac_period_id = p_period_id
AND cpic.inventory_item_id = cic.inventory_item_id);
||' Rows deleted from CIC');
INSERT INTO cst_item_costs
( inventory_item_id
, organization_id
, cost_type_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, inventory_asset_flag
, lot_size
, based_on_rollup_flag
, shrinkage_rate
, defaulted_flag
, cost_update_id
, pl_material
, pl_material_overhead
, pl_resource
, pl_outside_processing
, pl_overhead
, tl_material
, tl_material_overhead
, tl_resource
, tl_outside_processing
, tl_overhead
, material_cost
, material_overhead_cost
, resource_cost
, outside_processing_cost
, overhead_cost
, pl_item_cost
, tl_item_cost
, item_cost
, unburdened_cost
, burden_cost
, request_id
, program_application_id
, program_id
, program_update_date
)
SELECT
CPIC.inventory_item_id
, p_to_org_id
, p_to_cost_type_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id -- last update login
, decode(MSI.inventory_asset_flag , 'Y', 1, 2)
, nvl(MSI.std_lot_size,1)
, nvl(BORF.based_on_rollup_flag, nvl(dBORF.based_on_rollup_flag,1)) -- set the borf to pre-existing value/default
, nvl(MSI.shrinkage_rate , 0)
, 2 -- defaulted_flag
, NULL -- cost_update_id
, CPIC.pl_material
, CPIC.pl_material_overhead
, CPIC.pl_resource
, CPIC.pl_outside_processing
, CPIC.pl_overhead
, CPIC.tl_material
, CPIC.tl_material_overhead
, CPIC.tl_resource
, CPIC.tl_outside_processing
, CPIC.tl_overhead
, CPIC.material_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.material_overhead_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.resource_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.outside_processing_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.overhead_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.pl_item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.tl_item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.unburdened_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.burden_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, l_request_id
, 702
, l_program_id
, SYSDATE
FROM cst_pac_item_costs CPIC,
mtl_system_items MSI,
(SELECT inventory_item_id, based_on_rollup_flag
FROM cst_item_costs_interface
WHERE group_id = l_grp_id) BORF, -- based_on_rollup_flag backed up from destination cost type
(SELECT cic.inventory_item_id, cic.based_on_rollup_flag
FROM cst_cost_types cct, cst_item_costs cic
WHERE cic.organization_id = p_to_org_id
AND cct.cost_type_id = p_to_cost_type_id
AND cic.cost_type_id = cct.default_cost_type_id) dBORF -- based_on_rollup_flag from default cost type
WHERE CPIC.pac_period_id = p_period_id
AND CPIC.cost_group_id = p_from_cost_group_id
AND CPIC.inventory_item_id = MSI.inventory_item_id
AND MSI.organization_id = p_to_org_id
AND BORF.inventory_item_id(+) = CPIC.inventory_item_id
AND dBORF.inventory_item_id(+) = CPIC.inventory_item_id
AND (p_range = 1
OR
(p_range = 2 AND CPIC.inventory_item_id = p_specific_item_id)
OR
EXISTS
(SELECT NULL
FROM mtl_item_categories MIC
WHERE MIC.organization_id = l_master_org_id
AND MIC.category_id = p_category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.inventory_item_id = CPIC.inventory_item_id
AND p_range = 5)
);
||' Rows Inserted into CIC');
DELETE cst_item_costs_interface
WHERE group_id = l_grp_id;
||' Backup Rows deleted from CICI');
INSERT INTO cst_item_cost_details
( inventory_item_id
, organization_id
, cost_type_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, level_type
, resource_id
, usage_rate_or_amount
, basis_type
, basis_resource_id
, basis_factor
, net_yield_or_shrinkage_factor
, item_cost
, cost_element_id
, rollup_source_type
, request_id
, program_application_id
, program_id
, program_update_date
)
SELECT
CPIC.inventory_item_id
, p_to_org_id
, p_to_cost_type_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, CPICD.level_type
, decode(CPICD.cost_element_id, -- resource id
1, p_material,
2, p_material_overhead,
3, p_resource,
4, p_outside_processing,
5, p_overhead)
, CPICD.item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id) --usage_rate
, 1
, NULL -- Basis resource_id
, 1 -- basis_factor
, 1 -- net_yield_or_shrinkage_factor
, CPICD.item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPICD.cost_element_id
, 1 -- rollup_source_type
, l_request_id
, 702
, l_program_id
, SYSDATE
FROM cst_pac_item_costs CPIC,
cst_pac_item_cost_details CPICD,
mtl_system_items MSI -- Bug 2570867 - joined with MSI to select only to_org items
WHERE CPIC.pac_period_id = p_period_id
AND CPIC.cost_group_id = p_from_cost_group_id
AND CPIC.cost_layer_id = CPICD.cost_layer_id
AND MSI.inventory_item_id = CPIC.inventory_item_id
AND MSI.organization_id = p_to_org_id
AND (p_range = 1
OR
(p_range = 2 AND CPIC.inventory_item_id = p_specific_item_id)
OR
EXISTS
(SELECT NULL
FROM mtl_item_categories MIC
WHERE MIC.organization_id = l_master_org_id
AND MIC.category_id = p_category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.inventory_item_id = CPIC.inventory_item_id
AND p_range = 5)
);
||' Rows Inserted into CICD: ');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert New Cost Information Only');
INSERT INTO cst_item_costs
( inventory_item_id
, organization_id
, cost_type_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, inventory_asset_flag
, lot_size
, based_on_rollup_flag
, shrinkage_rate
, defaulted_flag
, cost_update_id
, pl_material
, pl_material_overhead
, pl_resource
, pl_outside_processing
, pl_overhead
, tl_material
, tl_material_overhead
, tl_resource
, tl_outside_processing
, tl_overhead
, material_cost
, material_overhead_cost
, resource_cost
, outside_processing_cost
, overhead_cost
, pl_item_cost
, tl_item_cost
, item_cost
, unburdened_cost
, burden_cost
, request_id
, program_application_id
, program_id
, program_update_date
)
SELECT
CPIC.inventory_item_id
, p_to_org_id
, p_to_cost_type_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, decode(MSI.inventory_asset_flag,'Y',1,2)
, nvl(MSI.std_lot_size,1)
, nvl(dBORF.based_on_rollup_flag,1) -- default the borf to 1 if not in default cost type
, nvl(MSI.shrinkage_rate,0)
, 2 -- defaulted flag
, NULL -- cost update id
, CPIC.pl_material
, CPIC.pl_material_overhead
, CPIC.pl_resource
, CPIC.pl_outside_processing
, CPIC.pl_overhead
, CPIC.tl_material
, CPIC.tl_material_overhead
, CPIC.tl_resource
, CPIC.tl_outside_processing
, CPIC.tl_overhead
, CPIC.material_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.material_overhead_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.resource_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.outside_processing_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.overhead_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.pl_item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.tl_item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.unburdened_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.burden_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, l_request_id
, 702
, l_program_id
, SYSDATE
FROM cst_pac_item_costs CPIC,
mtl_system_items MSI,
(SELECT cic.inventory_item_id, based_on_rollup_flag
FROM cst_cost_types cct, cst_item_costs cic
WHERE cic.organization_id = p_to_org_id
AND cct.cost_type_id = p_to_cost_type_id
AND cic.cost_type_id = cct.default_cost_type_id) dBORF -- based_on_rollup_flag from default cost type
WHERE CPIC.pac_period_id = p_period_id
AND CPIC.cost_group_id = p_from_cost_group_id
AND CPIC.inventory_item_id = MSI.inventory_item_id
AND MSI.organization_id = p_to_org_id
AND dBORF.inventory_item_id(+) = CPIC.inventory_item_id
AND (p_range = 1
OR
(p_range = 2 AND CPIC.inventory_item_id = p_specific_item_id)
OR
EXISTS
(SELECT NULL
FROM mtl_item_categories MIC
WHERE MIC.organization_id = l_master_org_id
AND MIC.category_id = p_category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.inventory_item_id = CPIC.inventory_item_id
AND p_range = 5)
)
AND NOT EXISTS
(SELECT 'x'
FROM cst_item_costs CIC
WHERE CIC.cost_type_id = p_to_cost_type_id
AND CIC.organization_id = p_to_org_id
AND CIC.inventory_item_id = CPIC.inventory_item_id);
||' Rows Inserted into CIC');
INSERT INTO cst_item_cost_details
( inventory_item_id
, organization_id
, cost_type_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, level_type
, resource_id
, usage_rate_or_amount
, basis_type
, basis_resource_id
, basis_factor
, net_yield_or_shrinkage_factor
, item_cost
, cost_element_id
, rollup_source_type
, request_id
, program_application_id
, program_id
, program_update_date
)
SELECT
CPIC.inventory_item_id
, p_to_org_id
, p_to_cost_type_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, CPICD.level_type
, decode(CPICD.cost_element_id, -- resource id
1, p_material,
2, p_material_overhead,
3, p_resource,
4, p_outside_processing,
5, p_overhead)
, CPICD.item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id) --usage_rate
, 1 -- basis_type
, NULL -- basis_resource_id
, 1 -- basis_factor
, 1 -- net_yield_or_shrinkage_factor
, CPICD.item_cost*
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPICD.cost_element_id
, 1 -- rollup_source_type
, l_request_id
, 702
, l_program_id
, SYSDATE
FROM cst_pac_item_costs CPIC,
cst_pac_item_cost_details CPICD,
mtl_system_items MSI -- Bug 2570867 - joined with MSI to select only to_org items
WHERE CPIC.pac_period_id = p_period_id
AND CPIC.cost_group_id = p_from_cost_group_id
AND CPIC.cost_layer_id = CPICD.cost_layer_id
AND MSI.inventory_item_id = CPIC.inventory_item_id
AND MSI.organization_id = p_to_org_id
AND (p_range = 1
OR
(p_range = 2 AND CPIC.inventory_item_id = p_specific_item_id)
OR
EXISTS
(SELECT NULL
FROM mtl_item_categories MIC
WHERE MIC.organization_id = l_master_org_id
AND MIC.category_id = p_category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.inventory_item_id = CPIC.inventory_item_id
AND p_range = 5)
)
-- Bug 2619991 - Commented out this NOT EXISTS check.
-- Statment 70 already makes this check and then inserts to CIC, so this always fails.
/*
AND NOT EXISTS
(SELECT 'x'
FROM cst_item_costs CIC
WHERE CIC.cost_type_id = p_to_cost_type_id
AND CIC.organization_id = p_to_org_id
AND CIC.inventory_item_id = CPIC.inventory_item_id)
*/
AND NOT EXISTS
(SELECT 'x'
FROM cst_item_cost_details CICD
WHERE CICD.cost_type_id = p_to_cost_type_id
AND CICD.organization_id = p_to_org_id
AND CICD.inventory_item_id = CPIC.inventory_item_id);
||' Rows Inserted into CICD');
DELETE cst_item_cost_details CICD
WHERE CICD.cost_type_id = p_to_cost_type_id
AND CICD.organization_id = p_to_org_id
AND (p_range = 1
OR
(p_range = 2 AND CICD.inventory_item_id = p_specific_item_id)
OR
EXISTS
(SELECT NULL
FROM mtl_item_categories MIC
WHERE MIC.organization_id = l_master_org_id
AND MIC.category_id = p_category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.inventory_item_id = CICD.inventory_item_id
AND p_range = 5)
);
||' Rows deleted from CICD');
SELECT CST_LISTS_S.NEXTVAL INTO l_grp_id
FROM dual;
INSERT INTO cst_item_costs_interface
( inventory_item_id
, cost_type_id
, based_on_rollup_flag
, group_id
)
SELECT
inventory_item_id
, p_to_cost_type_id
, based_on_rollup_flag
, l_grp_id
FROM cst_item_costs CIC
WHERE CIC.cost_type_id = p_to_cost_type_id
AND CIC.organization_id = p_to_org_id
AND (p_range = 1
OR
(p_range = 2 AND CIC.inventory_item_id = p_specific_item_id)
OR
EXISTS
(SELECT NULL
FROM mtl_item_categories MIC
WHERE MIC.organization_id = l_master_org_id
AND MIC.category_id = p_category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.inventory_item_id = CIC.inventory_item_id
AND p_range = 5)
)
AND EXISTS
(SELECT NULL
FROM cst_pac_item_costs cpic
WHERE cpic.cost_group_id = p_from_cost_group_id
AND cpic.pac_period_id = p_period_id
AND cpic.inventory_item_id = cic.inventory_item_id);
DELETE cst_item_costs CIC
WHERE CIC.cost_type_id = p_to_cost_type_id
AND CIC.organization_id = p_to_org_id
AND (p_range = 1
OR
(p_range = 2 AND CIC.inventory_item_id = p_specific_item_id)
OR
EXISTS
(SELECT NULL
FROM mtl_item_categories MIC
WHERE MIC.organization_id = l_master_org_id
AND MIC.category_id = p_category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.inventory_item_id = CIC.inventory_item_id
AND p_range = 5)
);
||' Rows deleted from CIC');
INSERT INTO cst_item_costs
( inventory_item_id
, organization_id
, cost_type_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, inventory_asset_flag
, lot_size
, based_on_rollup_flag
, shrinkage_rate
, defaulted_flag
, cost_update_id
, pl_material
, pl_material_overhead
, pl_resource
, pl_outside_processing
, pl_overhead
, tl_material
, tl_material_overhead
, tl_resource
, tl_outside_processing
, tl_overhead
, material_cost
, material_overhead_cost
, resource_cost
, outside_processing_cost
, overhead_cost
, pl_item_cost
, tl_item_cost
, item_cost
, unburdened_cost
, burden_cost
, request_id
, program_application_id
, program_id
, program_update_date
)
SELECT
CPIC.inventory_item_id
, p_to_org_id
, p_to_cost_type_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, decode(MSI.inventory_asset_flag, 'Y', 1, 2) --inventory_asset_flag
, nvl(MSI.std_lot_size,1)
, nvl(BORF.based_on_rollup_flag, nvl(dBORF.based_on_rollup_flag,1)) -- set the borf to pre-existing value/default
, nvl(MSI.shrinkage_rate,0)
, 2 -- defaulted_flag
, NULL -- cost_update_id
, CPIC.pl_material
, CPIC.pl_material_overhead
, CPIC.pl_resource
, CPIC.pl_outside_processing
, CPIC.pl_overhead
, CPIC.tl_material
, CPIC.tl_material_overhead
, CPIC.tl_resource
, CPIC.tl_outside_processing
, CPIC.tl_overhead
, CPIC.material_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.material_overhead_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.resource_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.outside_processing_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.overhead_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.pl_item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.tl_item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.unburdened_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPIC.burden_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, l_request_id
, 702
, l_program_id
, SYSDATE
FROM cst_pac_item_costs CPIC,
mtl_system_items MSI,
(SELECT inventory_item_id, based_on_rollup_flag
FROM cst_item_costs_interface
WHERE group_id = l_grp_id) BORF, -- based_on_rollup_flag backed up from destination cost type
(SELECT cic.inventory_item_id, cic.based_on_rollup_flag
FROM cst_cost_types cct, cst_item_costs cic
WHERE cic.organization_id = p_to_org_id
AND cct.cost_type_id = p_to_cost_type_id
AND cic.cost_type_id = cct.default_cost_type_id) dBORF -- based_on_rollup_flag from default cost type
WHERE CPIC.pac_period_id = p_period_id
AND CPIC.cost_group_id = p_from_cost_group_id
AND CPIC.inventory_item_id = MSI.inventory_item_id
AND MSI.organization_id = p_to_org_id
AND BORF.inventory_item_id(+) = CPIC.inventory_item_id
AND dBORF.inventory_item_id(+) = CPIC.inventory_item_id
AND (p_range = 1
OR
(p_range = 2 AND CPIC.inventory_item_id = p_specific_item_id)
OR
EXISTS
(SELECT NULL
FROM mtl_item_categories MIC
WHERE MIC.organization_id = l_master_org_id
AND MIC.category_id = p_category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.inventory_item_id = CPIC.inventory_item_id
AND p_range = 5)
);
||' Rows Inserted into CIC');
DELETE cst_item_costs_interface
WHERE group_id = l_grp_id;
||' Backup Rows deleted from CICI');
INSERT INTO cst_item_cost_details
( inventory_item_id
, organization_id
, cost_type_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, level_type
, resource_id
, usage_rate_or_amount
, basis_type
, basis_resource_id
, basis_factor
, net_yield_or_shrinkage_factor
, item_cost
, cost_element_id
, rollup_source_type
, request_id
, program_application_id
, program_id
, program_update_date
)
SELECT
CPIC.inventory_item_id
, p_to_org_id
, p_to_cost_type_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_login_id
, CPICD.level_type
, decode(CPICD.cost_element_id, -- For resource id
1, p_material,
2, p_material_overhead,
3, p_resource,
4, p_outside_processing,
5, p_overhead)
, CPICD.item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id) --usage_rate
, 1 -- basis_type
, NULL -- basis_resource_id
, 1 -- basis_factor
, 1 -- net_yield_or_shrinkage_factor
, CPICD.item_cost *
get_uom_conv_rate(CPIC.inventory_item_id,
l_master_org_id,
p_to_org_id)
, CPICD.cost_element_id
, 1 -- rollup_source_type
, l_request_id
, 702
, l_program_id
, SYSDATE
FROM cst_pac_item_costs CPIC,
cst_pac_item_cost_details CPICD,
mtl_system_items MSI -- Bug 2570867 - joined with MSI to select only to_org items
WHERE CPIC.pac_period_id = p_period_id
AND CPIC.cost_group_id = p_from_cost_group_id
AND CPIC.cost_layer_id = CPICD.cost_layer_id
AND MSI.inventory_item_id = CPIC.inventory_item_id
AND MSI.organization_id = p_to_org_id
AND (p_range = 1
OR
(p_range = 2 AND CPIC.inventory_item_id = p_specific_item_id)
OR
EXISTS
(SELECT NULL
FROM mtl_item_categories MIC
WHERE MIC.organization_id = l_master_org_id
AND MIC.category_id = p_category_id
AND MIC.category_set_id = p_category_set_id
AND MIC.inventory_item_id = CPIC.inventory_item_id
AND p_range = 5)
);
||' Rows Inserted into CICD');