[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_updated_by NUMBER;
/* Get Last_Updated_By from cst_sc_rollup_history instead of FND_GLOBAL structure */
l_stmt_num := 5;
SELECT LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
INTO l_last_updated_by,
l_login_id
FROM cst_sc_rollup_history
WHERE rollup_id = p_rollup_id
AND ROWNUM=1;
SELECT DEFAULT_COST_TYPE_ID
INTO default_cost_type_id
FROM CST_COST_TYPES
WHERE COST_TYPE_ID = p_dest_cost_type_id;
| Delete item costs that were generated by the previous cost |
| rollup. |
+------------------------------------------------------------*/
DELETE CST_ITEM_COST_DETAILS cicd
WHERE cicd.COST_TYPE_ID = p_dest_cost_type_id
AND (cicd.ROLLUP_SOURCE_TYPE = 2
OR cicd.ROLLUP_SOURCE_TYPE = 3
)
AND (cicd.inventory_item_id, cicd.organization_id) IN
( SELECT
csllc.inventory_item_id,
csllc.organization_id
FROM cst_sc_low_level_codes csllc,
cst_item_costs cia
WHERE
csllc.rollup_id = p_rollup_id
AND csllc.inventory_item_id = cia.inventory_item_id
AND cia.organization_id = csllc.organization_id
AND cia.cost_type_id = p_dest_cost_type_id
AND (cia.BASED_ON_ROLLUP_FLAG = 1 OR cia.DEFAULTED_FLAG = 1) -- Added for 7237848
);
UPDATE cst_sc_low_level_codes CSLLC
SET CSLLC.lot_size =
(SELECT CIC.lot_size
FROM cst_item_costs CIC
WHERE CIC.cost_type_id = p_dest_cost_type_id
AND CIC.inventory_item_id = CSLLC.inventory_item_id
AND CIC.organization_id = CSLLC.organization_id
)
WHERE CSLLC.rollup_id = p_rollup_id;
| Delete item attributes that were generated by the cost |
| rollup. |
+------------------------------------------------------------*/
DELETE CST_ITEM_COSTS cia
WHERE
COST_TYPE_ID = p_dest_cost_type_id
AND DEFAULTED_FLAG = 1 /* YES */
AND default_cost_type_id <> p_dest_cost_type_id
AND (cia.INVENTORY_ITEM_ID, cia.ORGANIZATION_ID) IN
(SELECT CSLLC.INVENTORY_ITEM_ID, CSLLC.ORGANIZATION_ID
FROM CST_SC_LOW_LEVEL_CODES CSLLC
WHERE CSLLC.ROLLUP_ID = p_rollup_id
);
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,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
cia.INVENTORY_ITEM_ID,
cia.ORGANIZATION_ID,
p_dest_cost_type_id,
l_rollup_date,
l_last_updated_by,
l_rollup_date,
l_last_updated_by,
l_login_id,
cia.INVENTORY_ASSET_FLAG,
NVL(csllc.LOT_SIZE, NVL( cia.LOT_SIZE, 1 )), -- SCAPI: use existing lot size
1, -- YES
NVL( cia.SHRINKAGE_RATE, 0 ),
1, -- YES
DECODE(p_conc_flag, 1, req_id, NULL),
DECODE(p_conc_flag, 1, prgm_appl_id, NULL),
DECODE(p_conc_flag, 1, prgm_id, NULL),
DECODE(p_conc_flag, 1,
l_rollup_date, NULL)
FROM
CST_SC_LOW_LEVEL_CODES csllc,
CST_ITEM_COSTS cia,
MTL_PARAMETERS mp
WHERE cia.ORGANIZATION_ID = csllc.organization_id
AND csllc.ROLLUP_ID = p_rollup_id
AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND cia.BASED_ON_ROLLUP_FLAG = 1 -- YES
AND mp.ORGANIZATION_ID = csllc.organization_id
AND cia.COST_TYPE_ID IN (default_cost_type_id, mp.PRIMARY_COST_METHOD) -- Added for 5678464
AND ( cia.COST_TYPE_ID = default_cost_type_id
OR
(
(cia.COST_TYPE_ID = mp.PRIMARY_COST_METHOD)
AND
(NOT EXISTS
(SELECT 'X'
FROM CST_ITEM_COSTS cia2
WHERE cia2.INVENTORY_ITEM_ID = cia.INVENTORY_ITEM_ID
AND cia2.ORGANIZATION_ID = cia.ORGANIZATION_ID
AND cia2.COST_TYPE_ID = default_cost_type_id)
)
)
) /* Supply chain enhancement: default valuation cost type */
AND NOT EXISTS
(SELECT 'X'
FROM CST_ITEM_COSTS cia1
WHERE cia1.INVENTORY_ITEM_ID = cia.INVENTORY_ITEM_ID
AND cia1.ORGANIZATION_ID = cia.ORGANIZATION_ID
AND cia1.COST_TYPE_ID = p_dest_cost_type_id
);
INSERT INTO CST_ITEM_COST_DETAILS (
INVENTORY_ITEM_ID,
COST_TYPE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ORGANIZATION_ID,
SOURCE_ORGANIZATION_ID,
OPERATION_SEQUENCE_ID,
OPERATION_SEQ_NUM,
DEPARTMENT_ID,
LEVEL_TYPE,
ACTIVITY_ID,
RESOURCE_SEQ_NUM,
RESOURCE_ID,
RESOURCE_RATE,
ITEM_UNITS,
ACTIVITY_UNITS,
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
cicd.INVENTORY_ITEM_ID,
p_dest_cost_type_id,
l_rollup_date,
l_last_updated_by,
l_rollup_date,
l_last_updated_by,
l_login_id,
--l_last_updated_by,
cicd.ORGANIZATION_ID,
cicd.ORGANIZATION_ID,
cicd.OPERATION_SEQUENCE_ID,
cicd.OPERATION_SEQ_NUM,
cicd.DEPARTMENT_ID,
1, -- This level
cicd.ACTIVITY_ID,
cicd.RESOURCE_SEQ_NUM,
cicd.RESOURCE_ID,
cicd.RESOURCE_RATE,
cicd.ITEM_UNITS,
cicd.ACTIVITY_UNITS,
cicd.USAGE_RATE_OR_AMOUNT,
cicd.BASIS_TYPE,
cicd.BASIS_RESOURCE_ID,
decode(cicd.BASIS_TYPE, 2, NVL(1/cia.LOT_SIZE, cicd.BASIS_FACTOR),
cicd.BASIS_FACTOR), -- SCAPI: use existing lot size
cicd.NET_YIELD_OR_SHRINKAGE_FACTOR,
decode(cicd.BASIS_TYPE, 2, NVL(cicd.ITEM_COST/(cicd.BASIS_FACTOR*cia.LOT_SIZE),
cicd.ITEM_COST), cicd.ITEM_COST), -- SCAPI: use existing lot size
cicd.COST_ELEMENT_ID,
2, -- Default
DECODE(p_conc_flag, 1, req_id, NULL),
DECODE(p_conc_flag, 1, prgm_appl_id, NULL),
DECODE(p_conc_flag, 1, prgm_id, NULL),
DECODE(p_conc_flag, 1,
l_rollup_date, NULL)
FROM
CST_SC_LOW_LEVEL_CODES csllc,
CST_ITEM_COSTS cia,
CST_ITEM_COST_DETAILS cicd,
MTL_PARAMETERS mp
WHERE csllc.ROLLUP_ID = p_rollup_id
AND cia.ORGANIZATION_ID = csllc.organization_id
AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND cia.COST_TYPE_ID = p_dest_cost_type_id
AND cia.INVENTORY_ASSET_FLAG = 1 -- YES
AND cia.DEFAULTED_FLAG = 1 -- YES
AND cia.BASED_ON_ROLLUP_FLAG = 1 -- YES
AND cicd.ORGANIZATION_ID = csllc.organization_id
AND cicd.INVENTORY_ITEM_ID = cia.INVENTORY_ITEM_ID
AND cicd.ROLLUP_SOURCE_TYPE in (1,2)
AND mp.ORGANIZATION_ID = csllc.organization_id
AND cicd.COST_TYPE_ID IN (default_cost_type_id, mp.PRIMARY_COST_METHOD) -- Added for 5678464
AND ( cicd.COST_TYPE_ID = default_cost_type_id
OR
(
(cicd.COST_TYPE_ID = mp.PRIMARY_COST_METHOD)
AND
(NOT EXISTS
(SELECT 'X'
FROM CST_ITEM_COSTS cia2
WHERE cia2.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND cia2.ORGANIZATION_ID = csllc.ORGANIZATION_ID
AND cia2.COST_TYPE_ID = default_cost_type_id)
)
)
) /* Supply chain enhancement: default valuation cost type */
AND cicd.COST_TYPE_ID NOT IN (2, 5, 6); -- Bug 2288462
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,
ITEM_COST,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
csllc.INVENTORY_ITEM_ID,
csllc.ORGANIZATION_ID,
p_dest_cost_type_id,
l_rollup_date,
l_last_updated_by,
l_rollup_date,
l_last_updated_by,
l_login_id,
--l_last_updated_by,
DECODE(msi.INVENTORY_ASSET_FLAG,'Y',1,2),
NVL(csllc.LOT_SIZE, NVL( msi.STD_LOT_SIZE, 1 )), -- SCAPI: use existing lot size
1, -- YES
NVL( msi.SHRINKAGE_RATE, 0 ),
1, -- YES
0, -- ITEM_COST
DECODE(p_conc_flag, 1, req_id, NULL),
DECODE(p_conc_flag, 1, prgm_appl_id, NULL),
DECODE(p_conc_flag, 1, prgm_id, NULL),
DECODE(p_conc_flag, 1,
l_rollup_date, NULL)
FROM
CST_SC_LOW_LEVEL_CODES csllc,
MTL_SYSTEM_ITEMS msi
WHERE
msi.ORGANIZATION_ID = csllc.organization_id
AND csllc.ROLLUP_ID = p_rollup_id
AND msi.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND NOT EXISTS
(SELECT 'X'
FROM CST_ITEM_COSTS cia1, MTL_PARAMETERS mp
WHERE cia1.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND cia1.ORGANIZATION_ID = csllc.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = csllc.ORGANIZATION_ID
AND cia1.COST_TYPE_ID
in (default_cost_type_id,p_dest_cost_type_id,mp.PRIMARY_COST_METHOD));
UPDATE cst_item_costs CIC
SET CIC.lot_size =
(SELECT decode(p_lot_size_option, 2, nvl(p_lot_size_setting, CIC.lot_size),
3, nvl(p_lot_size_setting*MSI.std_lot_size, CIC.lot_size))
FROM mtl_system_items MSI
WHERE MSI.inventory_item_id = CIC.inventory_item_id
AND MSI.organization_id = CIC.organization_id)
WHERE CIC.cost_type_id = p_dest_cost_type_id
AND CIC.based_on_rollup_flag = 1
AND (CIC.inventory_item_id, CIC.organization_id) IN
(SELECT CSL.inventory_item_id, CSL.organization_id
FROM cst_sc_lists CSL
WHERE CSL.rollup_id = p_rollup_id);
UPDATE cst_item_cost_details CICD
SET (CICD.basis_factor, CICD.item_cost) =
(SELECT nvl(1/CIC.lot_size, CICD.basis_factor),
nvl(CICD.item_cost/(CICD.basis_factor*CIC.lot_size), CICD.item_cost)
FROM cst_item_costs CIC
WHERE CIC.inventory_item_id = CICD.inventory_item_id
AND CIC.organization_id = CICD.organization_id
AND CIC.cost_type_id = CICD.cost_type_id)
WHERE CICD.cost_type_id = p_dest_cost_type_id
AND CICD.basis_type = 2
AND CICD.level_type = 1
AND (CICD.inventory_item_id, CICD.organization_id) IN
(SELECT CSL.inventory_item_id, CSL.organization_id
FROM cst_sc_lists CSL
WHERE CSL.rollup_id = p_rollup_id)
AND EXISTS
(SELECT 'x' FROM cst_item_costs CIC2
WHERE CIC2.inventory_item_id = CICD.inventory_item_id
AND CIC2.organization_id = CICD.organization_id
AND CIC2.cost_type_id = CICD.cost_type_id
AND CIC2.based_on_rollup_flag = 1);
SELECT
CIA.INVENTORY_ITEM_ID
FROM
CST_ITEM_COSTS CIA,
CST_SC_LOW_LEVEL_CODES CSLLC
WHERE CSLLC.ROLLUP_ID = l_rollup_id
AND CIA.INVENTORY_ITEM_ID = CSLLC.INVENTORY_ITEM_ID
AND CIA.ORGANIZATION_ID = CSLLC.organization_id
AND CIA.COST_TYPE_ID = l_dest_cost_type_id
FOR UPDATE OF CIA.INVENTORY_ITEM_ID NOWAIT; /* Modified for Bug 6467821 */
SELECT CICD.INVENTORY_ITEM_ID
FROM CST_SC_LOW_LEVEL_CODES CSLLC,
CST_ITEM_COST_DETAILS CICD
WHERE CSLLC.ROLLUP_ID = l_rollup_id
AND CICD.INVENTORY_ITEM_ID = CSLLC.INVENTORY_ITEM_ID
AND CICD.ORGANIZATION_ID = CSLLC.organization_id
AND CICD.COST_TYPE_ID = l_dest_cost_type_id
FOR UPDATE OF CICD.INVENTORY_ITEM_ID NOWAIT; /* Modified for Bug 6467821 */
SELECT
CIA.INVENTORY_ITEM_ID
FROM
CST_ITEM_COSTS CIA,
CST_SC_LOW_LEVEL_CODES CSLLC
WHERE CSLLC.ROLLUP_ID = l_rollup_id
AND CIA.INVENTORY_ITEM_ID = CSLLC.INVENTORY_ITEM_ID
AND CIA.ORGANIZATION_ID = CSLLC.organization_id
AND CIA.COST_TYPE_ID = l_dest_cost_type_id
FOR UPDATE OF CIA.LAST_UPDATED_BY;
SELECT CICD.INVENTORY_ITEM_ID
FROM CST_SC_LOW_LEVEL_CODES CSLLC,
CST_ITEM_COST_DETAILS CICD
WHERE CSLLC.ROLLUP_ID = l_rollup_id
AND CICD.INVENTORY_ITEM_ID = CSLLC.INVENTORY_ITEM_ID
AND CICD.ORGANIZATION_ID = CSLLC.organization_id
AND CICD.COST_TYPE_ID = l_dest_cost_type_id
FOR UPDATE OF CICD.LAST_UPDATED_BY;
l_last_updated_by IN NUMBER,
conc_flag IN NUMBER,
unimp_flag IN NUMBER,
locking_flag IN NUMBER,
rollup_date IN VARCHAR2,
revision_date IN VARCHAR2,
alt_bom_designator IN VARCHAR2,
alt_rtg_designator IN VARCHAR2,
rollup_option IN NUMBER,
report_option IN NUMBER,
l_mfg_flag IN NUMBER,
err_buf OUT NOCOPY VARCHAR2,
buy_cost_detail IN NUMBER -- SCAPI: option to preserve buy cost details
)
RETURN INTEGER
IS
l_count NUMBER;
SELECT DISTINCT
organization_id
FROM CST_SC_LOW_LEVEL_CODES
WHERE rollup_id = l_rollup_id;
SELECT DISTINCT
organization_id,
ext_precision
FROM CST_SC_LOW_LEVEL_CODES
WHERE rollup_id = l_rollup_id
AND low_level_code = p_cur_level;
SELECT inventory_item_id
FROM CST_SC_LOW_LEVEL_CODES
WHERE rollup_id = l_rollup_id
AND organization_id = p_org_id;
SELECT inventory_item_id,
organization_id,
round_unit,
precision,
ext_precision
FROM CST_SC_LOW_LEVEL_CODES csllc
WHERE csllc.rollup_id = l_rollup_id;
SELECT inventory_item_id,
organization_id,
round_unit,
precision,
ext_precision
FROM CST_SC_LOW_LEVEL_CODES csllc
WHERE csllc.rollup_id = l_rollup_id;
SELECT inventory_item_id,
organization_id,
round_unit,
precision,
ext_precision
FROM CST_SC_LOW_LEVEL_CODES csllc
WHERE csllc.rollup_id = p_rollup_id;
SELECT inventory_item_id,
organization_id,
round_unit,
precision,
ext_precision
FROM cst_sc_low_level_codes
WHERE rollup_id = l_rollup_id
AND low_level_code = current_level;
SELECT csllc.inventory_item_id AS inventory_item_id,
csllc.organization_id AS organization_id,
csllc.round_unit AS round_unit,
csllc.precision AS precision,
csllc.ext_precision AS ext_precision,
mp.primary_cost_method AS primary_cost_method
FROM cst_sc_low_level_codes csllc,
mtl_parameters mp
WHERE csllc.rollup_id = l_rollup_id
AND csllc.low_level_code = current_level
AND mp.organization_id = csllc.organization_id;
select LAST_UPDATE_LOGIN
into l_login_id
from cst_sc_rollup_history
where rollup_id = l_rollup_id
and rownum=1;
SELECT DEFAULT_COST_TYPE_ID,
1,
BOM_SNAPSHOT_FLAG,
ALTERNATE_BOM_DESIGNATOR,
COMPONENT_YIELD_FLAG,
PL_OPERATION_FLAG,
PL_ACTIVITY_FLAG,
PL_RESOURCE_FLAG,
PL_ELEMENT_FLAG
INTO default_cost_type_id,
item_shrinkage_flag,
bom_snapshot_flag,
l_snapshot_designator,
comp_yield_flag,
pl_operation_flag,
pl_activity_flag,
pl_cost_code_flag,
pl_element_flag
FROM CST_COST_TYPES
WHERE COST_TYPE_ID = l_dest_cost_type_id;
UPDATE CST_SC_LOW_LEVEL_CODES CSLLC
SET
CSLLC.ROUND_UNIT = l_round_unit,
CSLLC.PRECISION = l_precision,
CSLLC.EXT_PRECISION = l_ext_precision
WHERE CSLLC.organization_id = l_org_id
AND CSLLC.ROLLUP_ID = l_rollup_id;
INSERT INTO CST_ITEM_COST_DETAILS (
INVENTORY_ITEM_ID,
COST_TYPE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ORGANIZATION_ID,
SOURCE_ORGANIZATION_ID,
OPERATION_SEQUENCE_ID,
OPERATION_SEQ_NUM,
DEPARTMENT_ID,
LEVEL_TYPE,
ACTIVITY_ID,
RESOURCE_SEQ_NUM,
RESOURCE_ID,
RESOURCE_RATE,
USAGE_RATE_OR_AMOUNT,
BASIS_TYPE,
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 /*+ ORDERED INDEX(br BOM_RESOURCES_U2)*/
bor.ASSEMBLY_ITEM_ID,
l_dest_cost_type_id,
l_rollup_date,
l_last_updated_by,
l_rollup_date,
l_last_updated_by,
l_login_id,
csllc.organization_id,
csllc.organization_id,
bos.OPERATION_SEQUENCE_ID,
bos.OPERATION_SEQ_NUM,
bos.DEPARTMENT_ID,
CM_THIS_LEVEL,
bomres.ACTIVITY_ID,
bomres.RESOURCE_SEQ_NUM,
bomres.RESOURCE_ID,
DECODE(br.FUNCTIONAL_CURRENCY_FLAG,
1,1,NVL(crc.RESOURCE_RATE,0)),
bomres.USAGE_RATE_OR_AMOUNT,
bomres.BASIS_TYPE,
DECODE(bomres.BASIS_TYPE,1,1,2,
1/DECODE(cia.LOT_SIZE,NULL,1,0,1,cia.LOT_SIZE),1),
DECODE(item_shrinkage_flag,1,
DECODE(bomres.BASIS_TYPE,4,1,5,1,6,1,
1/(1-NVL(cia.SHRINKAGE_RATE,0))),1),
ROUND((DECODE(br.FUNCTIONAL_CURRENCY_FLAG,
1,1,
NVL(crc.RESOURCE_RATE,0)) *
bomres.USAGE_RATE_OR_AMOUNT *
DECODE(bomres.BASIS_TYPE,1,1,2,
1/DECODE(cia.LOT_SIZE,NULL,1,0,1,cia.LOT_SIZE),
1) *
DECODE(item_shrinkage_flag,1,
DECODE(bomres.BASIS_TYPE,4,1,5,1,6,1,
1/(1-NVL(cia.SHRINKAGE_RATE,0))),1)),
csllc.ext_precision),
br.COST_ELEMENT_ID, /* Resource cost element */
3, /* Rolled up */
DECODE(conc_flag, 1, req_id, NULL),
DECODE(conc_flag, 1, prgm_appl_id, NULL),
DECODE(conc_flag, 1, prgm_id, NULL),
DECODE(conc_flag, 1,
l_rollup_date, NULL)
FROM
CST_SC_LOW_LEVEL_CODES csllc,
MTL_PARAMETERS mp,
CST_ITEM_COSTS cia,
BOM_OPERATIONAL_ROUTINGS bor,
BOM_OPERATION_SEQUENCES bos,
BOM_OPERATION_RESOURCES bomres,
BOM_RESOURCES br,
CST_RESOURCE_COSTS crc
WHERE csllc.ROLLUP_ID = l_rollup_id
AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND cia.ORGANIZATION_ID = csllc.organization_id
AND cia.COST_TYPE_ID = l_dest_cost_type_id
AND cia.BASED_ON_ROLLUP_FLAG = 1 /* Yes */
AND cia.INVENTORY_ASSET_FLAG = 1
AND bor.ASSEMBLY_ITEM_ID = cia.INVENTORY_ITEM_ID
AND bor.ORGANIZATION_ID = cia.organization_id
AND ((l_mfg_flag = 1
AND
bor.ROUTING_TYPE = 1)
OR
(l_mfg_flag = 2)
)
AND( NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, 'none')
=NVL(alt_rtg_designator, 'none')
OR (
(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 = csllc.organization_id
AND bor1.ALTERNATE_ROUTING_DESIGNATOR =
alt_rtg_designator
AND ((l_mfg_flag = 1
AND
bor1.ROUTING_TYPE = 1)
OR
(l_mfg_flag = 2)
)
)
)
)
AND bos.ROUTING_SEQUENCE_ID = bor.COMMON_ROUTING_SEQUENCE_ID
/* Fix for BUG 1608765 */
AND bos.EFFECTIVITY_DATE <= l_rev_datetime
AND NVL( bos.DISABLE_DATE,
l_rev_datetime + 1)
>= l_rev_datetime /*Changed > to >= for bug 6389605*/
/* Right now, ECO does not support Op Yield */
AND ( NVL( bos.include_in_rollup, 1 ) = 1 OR
bos.change_notice is not null )
/* This section takes care of Unimplemented ECO Routings */
AND (
(
unimp_flag = 2 AND
bos.implementation_date is not null
)
OR
(
unimp_flag = 1 AND
bos.effectivity_date =
(
select max( bos2.effectivity_date )
from bom_operation_sequences bos2
where bos2.routing_sequence_id = bos.routing_sequence_id
and NVL( bos2.operation_type, 1 ) = NVL( bos.operation_type, 1 )
and bos2.operation_seq_num = bos.operation_seq_num
/* Fix for BUG 1607662 */
and bos2.EFFECTIVITY_DATE <=
fnd_date.canonical_to_date( revision_date )
)
)
)
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 = csllc.organization_id
AND br.ALLOW_COSTS_FLAG = 1
AND crc.RESOURCE_ID = bomres.RESOURCE_ID
AND (
crc.COST_TYPE_ID = l_dest_cost_type_id
OR
( crc.COST_TYPE_ID = default_cost_type_id
AND NOT EXISTS (SELECT 'X'
FROM CST_RESOURCE_COSTS crc1
WHERE crc1.RESOURCE_ID = bomres.RESOURCE_ID
AND crc1.COST_TYPE_ID = l_dest_cost_type_id)
)
OR
( crc.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
AND NOT EXISTS (SELECT 'X'
FROM CST_RESOURCE_COSTS crc2
WHERE crc2.RESOURCE_ID = bomres.RESOURCE_ID
AND crc2.COST_TYPE_ID in (l_dest_cost_type_id,default_cost_type_id))
)
) /* Supply chain enhancement: default valuation cost type */
AND mp.ORGANIZATION_ID = csllc.organization_id;
INSERT INTO CST_ITEM_COST_DETAILS (
inventory_item_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
source_organization_id,
operation_sequence_id,
operation_seq_num,
department_id,
level_type,
activity_id,
resource_seq_num,
resource_id,
resource_rate,
usage_rate_or_amount,
basis_type,
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 /*+ ORDERED INDEX(br BOM_RESOURCES_U2)*/
bor.assembly_item_id,
l_dest_cost_type_id,
l_rollup_date,
l_last_updated_by,
l_rollup_date,
l_last_updated_by,
l_login_id,
csllc.organization_id,
csllc.organization_id,
bos.operation_sequence_id,
bos.operation_seq_num,
bos.department_id,
cm_this_level,
bomres.activity_id,
bomres.resource_seq_num,
bomres.resource_id,
0,
bomres.usage_rate_or_amount,
bomres.basis_type,
DECODE(bomres.BASIS_TYPE,1,1,2, 1/DECODE(cia.lot_size,NULL,1,0,1,cia.lot_size),1),
DECODE(item_shrinkage_flag,1,
DECODE(bomres.basis_type,4,1,5,1,6,1,
1/(1-NVL(cia.shrinkage_rate,0))),1),
0,
br.cost_element_id, /* resource cost element */
3, /* Rolled up */
DECODE(conc_flag, 1, req_id, NULL),
DECODE(conc_flag, 1, prgm_appl_id, NULL),
-100,
DECODE(conc_flag, 1, l_rollup_date, NULL)
FROM
CST_SC_LOW_LEVEL_CODES csllc,
MTL_PARAMETERS mp,
CST_ITEM_COSTS cia,
BOM_OPERATIONAL_ROUTINGS bor,
BOM_OPERATION_SEQUENCES bos,
BOM_OPERATION_RESOURCES bomres,
BOM_RESOURCES br
WHERE csllc.rollup_id = l_rollup_id
AND cia.inventory_item_id = csllc.inventory_item_id
AND cia.organization_id = csllc.organization_id
AND cia.cost_type_id = l_dest_cost_type_id
AND cia.based_on_rollup_flag = 1 /* yes */
AND cia.inventory_asset_flag = 1
AND bor.assembly_item_id = cia.inventory_item_id
AND bor.organization_id = cia.organization_id
AND ((l_mfg_flag = 1 AND bor.ROUTING_TYPE = 1) OR (l_mfg_flag = 2))
AND( NVL(bor.alternate_routing_designator, 'none') = NVL(alt_rtg_designator, 'none')
OR (
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 = csllc.organization_id
AND bor1.alternate_routing_designator = alt_rtg_designator
AND ((l_mfg_flag = 1 AND bor1.routing_type = 1) OR (l_mfg_flag = 2))
)
)
)
AND bos.routing_sequence_id = bor.common_routing_sequence_id
/* Fix for BUG 1608765 */
AND bos.effectivity_date <= l_rev_datetime
AND NVL( bos.disable_date, l_rev_datetime + 1) >= l_rev_datetime /*Changed > to >= for bug 6389605*/
/* Right now, ECO does not support Op Yield */
AND ( NVL( bos.include_in_rollup, 1 ) = 1 OR bos.change_notice is not null )
/* This section takes care of Unimplemented ECO Routings */
AND (
(unimp_flag = 2 AND bos.implementation_date is not null)
OR
(unimp_flag = 1 AND bos.effectivity_date =
(
SELECT MAX( bos2.effectivity_date )
FROM bom_operation_sequences bos2
WHERE bos2.routing_sequence_id = bos.routing_sequence_id
AND NVL( bos2.operation_type, 1 ) = NVL( bos.operation_type, 1 )
AND bos2.operation_seq_num = bos.operation_seq_num
/* Fix for BUG 1607662 */
AND bos2.effectivity_date <= fnd_date.canonical_to_date(revision_date)
)
)
)
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 = csllc.organization_id
AND br.ALLOW_COSTS_FLAG = 1
-- To Exclude resource records that have rates defined
AND NOT EXISTS (SELECT 'resource rate defined'
FROM cst_resource_costs crc
WHERE crc.resource_id = bomres.resource_id
AND (crc.cost_type_id = l_dest_cost_type_id
OR
crc.COST_TYPE_ID = default_cost_type_id
OR
/* Supply chain enhancement: default valuation cost type */
crc.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
)
)
-- To get Overhead records of Basis Type = 3 (i.e Resource Unit based OH) only
AND EXISTS (SELECT 'overhead exists for resource rate not defined'
FROM cst_resource_overheads cro,
cst_department_overheads cdo
WHERE cro.resource_id = bomres.resource_id
AND (cro.cost_type_id = l_dest_cost_type_id
OR
cro.cost_type_id = default_cost_type_id
OR
cro.COST_TYPE_ID = DECODE(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
)
AND cdo.department_id = bos.department_id
AND cdo.overhead_id = cro.overhead_id
AND cdo.basis_type = 3 --only for resource unit based OH
AND cdo.rate_or_amount <> 0
AND (
cdo.cost_type_id = l_dest_cost_type_id
OR
cdo.cost_type_id = default_cost_type_id
OR
cdo.cost_type_id = DECODE(mp.primary_cost_method, 1, 1, mp.avg_rates_cost_type_id)
)
)
AND mp.organization_id = csllc.organization_id;
INSERT INTO CST_ITEM_COST_DETAILS (
INVENTORY_ITEM_ID,
COST_TYPE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
SOURCE_ORGANIZATION_ID,
ORGANIZATION_ID,
OPERATION_SEQUENCE_ID,
OPERATION_SEQ_NUM,
DEPARTMENT_ID,
LEVEL_TYPE,
ACTIVITY_ID,
RESOURCE_SEQ_NUM,
RESOURCE_ID,
RESOURCE_RATE,
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 cicd.INVENTORY_ITEM_ID,
l_dest_cost_type_id,
l_rollup_date,
l_last_updated_by,
l_rollup_date,
l_last_updated_by,
l_login_id,
csllc.organization_id,
csllc.organization_id,
cicd.OPERATION_SEQUENCE_ID,
cicd.OPERATION_SEQ_NUM,
cicd.DEPARTMENT_ID,
CM_THIS_LEVEL,
cdo.ACTIVITY_ID,
cicd.RESOURCE_SEQ_NUM,
cdo.OVERHEAD_ID,
NULL,
cdo.RATE_OR_AMOUNT,
cdo.BASIS_TYPE,
cro.RESOURCE_ID,
/* Modified for bug 6821381 */
DECODE(cdo.BASIS_TYPE,
3, DECODE(cicd.USAGE_RATE_OR_AMOUNT * cicd.BASIS_FACTOR,
0, 1,
cicd.USAGE_RATE_OR_AMOUNT * cicd.BASIS_FACTOR),
4, DECODE(cicd.ITEM_COST, 0, 1, cicd.ITEM_COST)),
DECODE(cdo.BASIS_TYPE,3,
DECODE(item_shrinkage_flag,1,
1/(1-NVL(cia.SHRINKAGE_RATE,0)),1),1),
ROUND((cdo.RATE_OR_AMOUNT *
DECODE(cdo.BASIS_TYPE,
3, cicd.USAGE_RATE_OR_AMOUNT * cicd.BASIS_FACTOR,
4, cicd.ITEM_COST) *
DECODE(cdo.BASIS_TYPE,3,
DECODE(item_shrinkage_flag,1,
1/(1-NVL(cia.SHRINKAGE_RATE,0)),1),1)),
csllc.ext_precision),
5, /* Overhead cost element */
3, /* Rolled up */
DECODE(conc_flag, 1, req_id, NULL),
DECODE(conc_flag, 1, prgm_appl_id, NULL),
DECODE(conc_flag, 1, prgm_id, NULL),
DECODE(conc_flag, 1,
l_rollup_date, NULL)
FROM
CST_SC_LOW_LEVEL_CODES csllc,
CST_ITEM_COSTS cia,
CST_ITEM_COST_DETAILS cicd,
CST_RESOURCE_OVERHEADS cro,
CST_DEPARTMENT_OVERHEADS cdo,
MTL_PARAMETERS mp
WHERE csllc.ROLLUP_ID = l_rollup_id
AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND cia.ORGANIZATION_ID = csllc.organization_id
AND cia.COST_TYPE_ID = l_dest_cost_type_id
AND cia.BASED_ON_ROLLUP_FLAG = 1 /* YES */
AND cia.INVENTORY_ASSET_FLAG = 1
AND cicd.ORGANIZATION_ID = csllc.organization_id
AND cicd.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND cicd.COST_TYPE_ID = l_dest_cost_type_id
AND cicd.RESOURCE_ID = cro.RESOURCE_ID
AND (
cro.COST_TYPE_ID = l_dest_cost_type_id
OR
( cro.COST_TYPE_ID = default_cost_type_id
AND NOT EXISTS (SELECT 'X'
FROM CST_RESOURCE_OVERHEADS cro1
WHERE cro1.RESOURCE_ID = cicd.RESOURCE_ID
AND cro1.COST_TYPE_ID = l_dest_cost_type_id)
)
OR
( cro.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
AND NOT EXISTS (SELECT 'X'
FROM CST_RESOURCE_OVERHEADS cro2
WHERE cro2.RESOURCE_ID = cicd.RESOURCE_ID
AND cro2.COST_TYPE_ID in (l_dest_cost_type_id,default_cost_type_id))
)
) /* Supply chain enhancement: default valuation cost type */
AND cro.COST_TYPE_ID IN (l_dest_cost_type_id,default_cost_type_id,decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)) -- Added for 5678464
AND cdo.COST_TYPE_ID IN (l_dest_cost_type_id,default_cost_type_id,decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)) -- Added for 5678464
AND mp.ORGANIZATION_ID = csllc.organization_id
AND cdo.DEPARTMENT_ID = cicd.DEPARTMENT_ID
AND cdo.OVERHEAD_ID = cro.OVERHEAD_ID
AND cdo.BASIS_TYPE IN (3,4)
AND cdo.RATE_OR_AMOUNT <> 0
AND (
cdo.COST_TYPE_ID = l_dest_cost_type_id
OR
( cdo.COST_TYPE_ID = default_cost_type_id
AND NOT EXISTS
(SELECT 'X'
FROM CST_DEPARTMENT_OVERHEADS cdo1
WHERE cdo1.DEPARTMENT_ID = cicd.DEPARTMENT_ID
AND cdo1.COST_TYPE_ID = l_dest_cost_type_id)
)
OR
( cdo.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
AND NOT EXISTS
(SELECT 'X'
FROM CST_DEPARTMENT_OVERHEADS cdo2
WHERE cdo2.DEPARTMENT_ID = cicd.DEPARTMENT_ID
AND cdo2.COST_TYPE_ID in (l_dest_cost_type_id,default_cost_type_id))
)
); /* Supply chain enhancement: default valuation cost type */
INSERT INTO CST_ITEM_COST_DETAILS (
INVENTORY_ITEM_ID,
COST_TYPE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
SOURCE_ORGANIZATION_ID,
ORGANIZATION_ID,
OPERATION_SEQUENCE_ID,
OPERATION_SEQ_NUM,
DEPARTMENT_ID,
LEVEL_TYPE,
ACTIVITY_ID,
RESOURCE_SEQ_NUM,
RESOURCE_ID,
RESOURCE_RATE,
USAGE_RATE_OR_AMOUNT,
BASIS_TYPE,
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
bor.ASSEMBLY_ITEM_ID,
l_dest_cost_type_id,
l_rollup_date,
l_last_updated_by,
l_rollup_date,
l_last_updated_by,
l_login_id,
csllc.organization_id,
csllc.organization_id,
bos.OPERATION_SEQUENCE_ID,
bos.OPERATION_SEQ_NUM,
bos.DEPARTMENT_ID,
CM_THIS_LEVEL,
cdo.ACTIVITY_ID,
NULL,
cdo.OVERHEAD_ID,
NULL,
cdo.RATE_OR_AMOUNT,
cdo.BASIS_TYPE,
DECODE(cdo.BASIS_TYPE,1,1,2,
1/DECODE(cia.LOT_SIZE,NULL,1,0,1,cia.LOT_SIZE),0),
DECODE(item_shrinkage_flag,1,
DECODE(cdo.BASIS_TYPE,4,1,5,1,6,1,
1/(1-NVL(cia.SHRINKAGE_RATE,0))),1),
ROUND((cdo.RATE_OR_AMOUNT *
DECODE(cdo.BASIS_TYPE,1,1,2,
1/DECODE(cia.LOT_SIZE,NULL,1,0,1,cia.LOT_SIZE),0) *
DECODE(item_shrinkage_flag,1,
DECODE(cdo.BASIS_TYPE,4,1,5,1,6,1,
1/(1-NVL(cia.SHRINKAGE_RATE,0))),1)),
csllc.ext_precision),
5, /* overhead cost element */
3, /* Rolled up */
DECODE(conc_flag, 1, req_id, NULL),
DECODE(conc_flag, 1, prgm_appl_id, NULL),
DECODE(conc_flag, 1, prgm_id, NULL),
DECODE(conc_flag, 1,
l_rollup_date, NULL)
FROM
CST_SC_LOW_LEVEL_CODES csllc,
CST_ITEM_COSTS cia,
BOM_OPERATIONAL_ROUTINGS bor,
BOM_OPERATION_SEQUENCES bos,
CST_DEPARTMENT_OVERHEADS cdo,
MTL_PARAMETERS mp
WHERE csllc.ROLLUP_ID = l_rollup_id
AND cia.BASED_ON_ROLLUP_FLAG (+) = 1 /* YES */
AND cia.INVENTORY_ASSET_FLAG (+) = 1
AND cia.INVENTORY_ITEM_ID (+) = csllc.INVENTORY_ITEM_ID
AND cia.ORGANIZATION_ID (+) = csllc.organization_id
AND cia.COST_TYPE_ID (+) = l_dest_cost_type_id
AND cia.INVENTORY_ITEM_ID is not null
AND bor.ASSEMBLY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND bor.ORGANIZATION_ID = csllc.organization_id
AND ((l_mfg_flag = 1
AND
bor.ROUTING_TYPE = 1)
OR
(l_mfg_flag = 2)
)
AND( NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, 'none')
=NVL(alt_rtg_designator, 'none')
OR (
(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 = csllc.organization_id
AND bor1.ALTERNATE_ROUTING_DESIGNATOR =
alt_rtg_designator
AND ((l_mfg_flag = 1
AND
bor1.ROUTING_TYPE = 1)
OR
(l_mfg_flag = 2)
)
)
)
)
AND bos.ROUTING_SEQUENCE_ID = bor.COMMON_ROUTING_SEQUENCE_ID
/* Fix for BUG 1608765 */
AND bos.EFFECTIVITY_DATE <= l_rev_datetime
AND NVL( bos.DISABLE_DATE,
l_rev_datetime + 1)
>= l_rev_datetime /*Changed > to >= for bug 6389605*/
/* Right now, ECO does not support Op Yield */
AND ( NVL( bos.include_in_rollup, 1 ) = 1 OR
bos.change_notice is not null )
/* This section takes care of Unimplemented ECO Routings */
AND (
(
unimp_flag = 2 AND
bos.implementation_date is not null
)
OR
(
unimp_flag = 1 AND
bos.effectivity_date =
(
select max( bos2.effectivity_date )
from bom_operation_sequences bos2
where bos2.routing_sequence_id = bos.routing_sequence_id
and NVL( bos2.operation_type, 1 ) = NVL( bos.operation_type, 1 )
and bos2.operation_seq_num = bos.operation_seq_num
/* Fix for BUG 1607662 */
and bos2.EFFECTIVITY_DATE <=
fnd_date.canonical_to_date( revision_date )
)
)
)
AND NVL( bos.eco_for_production, 2 ) = 2
AND cdo.DEPARTMENT_ID = bos.DEPARTMENT_ID
AND cdo.BASIS_TYPE IN (1,2)
AND cdo.RATE_OR_AMOUNT <> 0
AND (
cdo.COST_TYPE_ID = l_dest_cost_type_id
OR
( cdo.COST_TYPE_ID = default_cost_type_id
AND NOT EXISTS
(SELECT 'X'
FROM CST_DEPARTMENT_OVERHEADS cdo1
WHERE cdo1.DEPARTMENT_ID = cdo.DEPARTMENT_ID
AND cdo1.COST_TYPE_ID = l_dest_cost_type_id)
)
OR
( cdo.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
AND NOT EXISTS
(SELECT 'X'
FROM CST_DEPARTMENT_OVERHEADS cdo2
WHERE cdo2.DEPARTMENT_ID = cdo.DEPARTMENT_ID
AND cdo2.COST_TYPE_ID in (l_dest_cost_type_id,default_cost_type_id))
)
) /* Supply chain enhancement: default valuation cost type */
AND cdo.COST_TYPE_ID IN (l_dest_cost_type_id, default_cost_type_id, decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)) --Added for 5678464
AND mp.ORGANIZATION_ID = csllc.organization_id
/* Fix for bug 2142170 */
/* -----------------------------------------------------------+
| If the routing is a flow routing, then the operation type |
| should be an event. |
+-------------------------------------------------------------*/
AND ((nvl(bor.cfm_routing_flag, 2) <> 1)
OR (nvl(bor.cfm_routing_flag, 2) = 1 and nvl(bos.operation_type, 1) = 1))
;
UPDATE CST_ITEM_COST_DETAILS cicd
SET (BASIS_FACTOR,
ITEM_COST) =
(SELECT DECODE(cicd.BASIS_TYPE,
3,NVL(SUM(ROUND(cicd1.USAGE_RATE_OR_AMOUNT*
cicd1.BASIS_FACTOR,
l_ext_tbl(i))),0),
4,NVL(SUM(cicd1.ITEM_COST),0)),
DECODE(cicd.BASIS_TYPE,
3,ROUND(cicd.USAGE_RATE_OR_AMOUNT*
cicd.NET_YIELD_OR_SHRINKAGE_FACTOR *
NVL(SUM(ROUND(cicd1.USAGE_RATE_OR_AMOUNT*
cicd1.BASIS_FACTOR,
l_ext_tbl(i))),0),
l_ext_tbl(i)),
4,ROUND(cicd.USAGE_RATE_OR_AMOUNT*
NVL(SUM(cicd1.ITEM_COST),0),l_ext_tbl(i)))
FROM
CST_ITEM_COST_DETAILS cicd1,
CST_RESOURCE_OVERHEADS cro
WHERE cicd1.INVENTORY_ITEM_ID = cicd.INVENTORY_ITEM_ID
AND cicd1.ORGANIZATION_ID = l_org_id_tbl(i)
AND cicd1.COST_TYPE_ID = l_dest_cost_type_id
AND cicd1.RESOURCE_ID = cro.RESOURCE_ID
AND cicd1.LEVEL_TYPE = CM_THIS_LEVEL
AND cicd1.COST_ELEMENT_ID in (3,4)
AND cro.OVERHEAD_ID = cicd.RESOURCE_ID
AND cro.COST_TYPE_ID = l_dest_cost_type_id)
WHERE cicd.INVENTORY_ITEM_ID = l_inv_item_tbl(i)
AND cicd.ORGANIZATION_ID = l_org_id_tbl(i)
AND cicd.ROLLUP_SOURCE_TYPE = 1 /* user entered */
AND cicd.COST_ELEMENT_ID = 2 /* material overhead */
AND cicd.BASIS_TYPE in (3,4) /* resource units, value */
AND cicd.COST_TYPE_ID = l_dest_cost_type_id;
l_last_updated_by,
rollup_date,
req_id,
prgm_appl_id,
prgm_id,
err_buf);
SELECT NVL(MAX(LOW_LEVEL_CODE),0)
INTO max_level
FROM CST_SC_LOW_LEVEL_CODES
WHERE ROLLUP_ID = l_rollup_id;
INSERT INTO cst_item_cost_details
(INVENTORY_ITEM_ID,
COST_TYPE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
SOURCE_ORGANIZATION_ID,
ORGANIZATION_ID,
OPERATION_SEQ_NUM,
LEVEL_TYPE,
ACTIVITY_ID,
RESOURCE_ID,
RESOURCE_RATE,
USAGE_RATE_OR_AMOUNT,
BASIS_TYPE,
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,
BASIS_RESOURCE_ID,
OPERATION_SEQUENCE_ID,
RESOURCE_SEQ_NUM
)
SELECT
l_inv_item_tbl(i),
l_dest_cost_type_id,
TO_DATE(rollup_date,'YYYY/MM/DD HH24:MI:SS'),
l_last_updated_by,
TO_DATE(rollup_date,'YYYY/MM/DD HH24:MI:SS'),
l_last_updated_by,
l_login_id,
l_org_id_tbl(i),
l_org_id_tbl(i),
DECODE(pl_operation_flag, 1, max(bic.operation_seq_num), NULL),
-- If subassembly is phantom, and use_phatom_routings
-- for resources, overhead and OSP, should be this level
-- rather than previous level
DECODE(cicd.level_type, CM_THIS_LEVEL,
DECODE(NVL(bp.use_phantom_routings,2), 1,
DECODE(nvl(bic.WIP_SUPPLY_TYPE, Nvl(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(cicd.cost_element_id,3,CM_THIS_LEVEL,4,CM_THIS_LEVEL,5,CM_THIS_LEVEL,CM_PREVIOUS_LEVEL),
CM_PREVIOUS_LEVEL),
CM_PREVIOUS_LEVEL),
CM_PREVIOUS_LEVEL),
DECODE(pl_activity_flag, 1, max(cicd.ACTIVITY_ID), NULL),
DECODE(pl_cost_code_flag, 1, max(cicd.RESOURCE_ID), NULL),
DECODE(pl_cost_code_flag, 1, max(cicd.RESOURCE_RATE), NULL),
/* Usage Rate or amount Start*/
DECODE(pl_cost_code_flag, 1, SUM(cicd.USAGE_RATE_OR_AMOUNT*
cicd.BASIS_FACTOR*
DECODE(NVL(bp.use_phantom_routings,2), 1,
DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(cicd.cost_element_id,
1, DECODE(cicd.basis_type, 2, bic.COMPONENT_QUANTITY/cicd.basis_factor, bic.COMPONENT_QUANTITY),
2, DECODE(cicd.basis_type, 2, bic.COMPONENT_QUANTITY/cicd.basis_factor, bic.COMPONENT_QUANTITY),
3, DECODE(cicd.basis_type, 2, 1/cicd.basis_factor, bic.COMPONENT_QUANTITY),
4, DECODE(cicd.basis_type, 2, 1/cicd.basis_factor, bic.COMPONENT_QUANTITY),
5, DECODE(cicd.basis_type, 2, 1/cicd.basis_factor,
3, 1/cicd.basis_factor,
4, 1/cicd.basis_factor, bic.COMPONENT_QUANTITY)), bic.COMPONENT_QUANTITY),
bic.COMPONENT_QUANTITY)/
DECODE(comp_yield_flag,1,bic.COMPONENT_YIELD_FACTOR,1)*
NVL(bic.PLANNING_FACTOR / 100,1)*
cicd.NET_YIELD_OR_SHRINKAGE_FACTOR),
SUM(cicd.ITEM_COST*
1/(1-NVL(cia_assy.SHRINKAGE_RATE,0))*
DECODE(NVL(bp.use_phantom_routings,2), 1,
DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(cicd.cost_element_id, 3,
DECODE(cicd.basis_type, 2, 1, bic.COMPONENT_QUANTITY),
4, DECODE(cicd.basis_type, 2, 1, bic.COMPONENT_QUANTITY),
5, DECODE(cicd.basis_type, 2, 1,
3, DECODE(bomres.basis_type, 2, 1, bic.COMPONENT_QUANTITY),
4, DECODE(bomres.basis_type, 2, 1, bic.COMPONENT_QUANTITY),
bic.COMPONENT_QUANTITY), bic.COMPONENT_QUANTITY),
bic.COMPONENT_QUANTITY), bic.COMPONENT_QUANTITY)*
NVL(bic.PLANNING_FACTOR / 100,1)/
DECODE(comp_yield_flag,1,bic.COMPONENT_YIELD_FACTOR,1))), /* Usage Rate or amount END*/
/* Update basis_type if phantom - Bug 2076990*/
/* Start changes for LBM */
DECODE(PL_COST_CODE_FLAG, 1,
DECODE(BP.USE_PHANTOM_ROUTINGS, 1,
DECODE(NVL(BIC.WIP_SUPPLY_TYPE, NVL(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(CICD.BASIS_TYPE, 2, 2, 3, 3, 4, 4,1),
DECODE(bic.basis_type,2,2,1)),
DECODE(bic.basis_type,2,2,1)),
DECODE(bic.basis_type,2,2,1)),
/*basis_factor changed for LBM project */
DECODE(pl_cost_code_flag, 1,
DECODE(NVL(bp.use_phantom_routings,2), 1,
DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(cicd.cost_element_id,
1, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
2, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
3, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
4, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
5, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE,
3, DECODE(bomres.basis_type,
2, (cicd.basis_factor*cia_comp.lot_size)/cia_assy.lot_size,
cicd.basis_factor),
4, DECODE(bomres.basis_type,
2, (cicd.basis_factor*cia_comp.lot_size)/cia_assy.lot_size,
cicd.basis_factor),1)),
DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
/* Net Yield or Shrinkage factor not changed */
max(1/(1-NVL(cia_assy.SHRINKAGE_RATE,0))),
/* If subassembly is a phantom which uses a lot based resource,
then number of such subassemblies is not used in the calculation
of the final cost of the assembly due to the lot based resource
- Bug 2076990*/
/* Item Cost calculation changed for LBM */
ROUND((SUM(cicd.ITEM_COST*1/(1-NVL(cia_assy.SHRINKAGE_RATE,0))/
DECODE(comp_yield_flag,1,bic.COMPONENT_YIELD_FACTOR,1)*NVL(bic.PLANNING_FACTOR / 100,1)*
DECODE(NVL(bp.use_phantom_routings,2), 1,
DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(cicd.cost_element_id,
1, DECODE(cicd.basis_type, 2, cia_comp.lot_size/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY),
2, DECODE(cicd.basis_type, 2, cia_comp.lot_size/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY),
3, DECODE(cicd.basis_type, 2, 1/(cia_assy.LOT_SIZE*cicd.basis_factor), bic.COMPONENT_QUANTITY),
4, DECODE(cicd.basis_type, 2, 1/(cia_assy.LOT_SIZE*cicd.basis_factor), bic.COMPONENT_QUANTITY),
5, DECODE(cicd.basis_type,
2, 1/(cia_assy.LOT_SIZE*cicd.basis_factor),
3, DECODE(bomres.basis_type, 2, cia_comp.lot_size/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY),
4, DECODE(bomres.basis_type, 2, cia_comp.lot_size/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY),
bic.COMPONENT_QUANTITY), bic.COMPONENT_QUANTITY),
DECODE(bic.basis_type,2, bic.COMPONENT_QUANTITY/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY)),
DECODE(bic.basis_type,2, bic.COMPONENT_QUANTITY/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY)))) , l_ext_tbl(i)),
/* end changes for LBM */
DECODE(pl_element_flag, 1,max(cicd.COST_ELEMENT_ID),1),
3,
DECODE(conc_flag, 1, req_id, NULL),
DECODE(conc_flag, 1, prgm_appl_id, NULL),
DECODE(conc_flag, 1, prgm_id, NULL),
DECODE(conc_flag, 1, TO_DATE(rollup_date,'YYYY/MM/DD HH24:MI:SS'), NULL),
DECODE(pl_cost_code_flag, 1,
DECODE(NVL(bp.use_phantom_routings,2), 1,
DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(cicd.cost_element_id, 3, cicd.basis_resource_id,
4, cicd.basis_resource_id,
5, cicd.basis_resource_id, null), null), null), null),
DECODE(pl_cost_code_flag, 1,
DECODE(NVL(bp.use_phantom_routings,2), 1,
DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(cicd.cost_element_id, 3, cicd.operation_sequence_id,
4, cicd.operation_sequence_id,
5, cicd.operation_sequence_id, null), null), null), null),
DECODE(pl_cost_code_flag, 1,
DECODE(NVL(bp.use_phantom_routings,2), 1,
DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(cicd.cost_element_id, 3, cicd.resource_seq_num,
4, cicd.resource_seq_num,
5, cicd.resource_seq_num, null), null), null), null)
FROM BOM_BILL_OF_MATERIALS bbom,
BOM_INVENTORY_COMPONENTS bic,
CST_ITEM_COSTS cia_assy,
CST_ITEM_COST_DETAILS cicd,
CST_ITEM_COSTS cia_comp,
BOM_PARAMETERS bp,
mtl_system_items msi,
BOM_OPERATION_RESOURCES bomres
WHERE cia_assy.ORGANIZATION_ID = l_org_id_tbl(i)
AND cia_assy.COST_TYPE_ID = l_dest_cost_type_id
AND cia_assy.BASED_ON_ROLLUP_FLAG = 1
AND cia_assy.INVENTORY_ASSET_FLAG = 1
AND cia_assy.INVENTORY_ITEM_ID = l_inv_item_tbl(i)
AND bbom.ORGANIZATION_ID = l_org_id_tbl(i)
AND bbom.ASSEMBLY_ITEM_ID = l_inv_item_tbl(i)
AND((l_mfg_flag = 1
AND
bbom.ASSEMBLY_TYPE = 1)
OR
(l_mfg_flag = 2)
)
AND((bbom.ALTERNATE_BOM_DESIGNATOR IS NULL
AND
alt_bom_designator IS NULL)
OR
(alt_bom_designator IS NOT NULL
AND
bbom.ALTERNATE_BOM_DESIGNATOR = alt_bom_designator)
OR ((alt_bom_designator IS NOT NULL)
AND (bbom.ALTERNATE_BOM_DESIGNATOR IS NULL)
AND NOT EXISTS
(SELECT /*+ PUSH_SUBQ */ 'X' -- Added hint for 5678464
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 = alt_bom_designator
AND((l_mfg_flag = 1
AND
bbom1.ASSEMBLY_TYPE = 1)
OR
(l_mfg_flag = 2)
)
)))
-- Added for 5678464
AND (bbom.ALTERNATE_BOM_DESIGNATOR IS NULL
OR
bbom.ALTERNATE_BOM_DESIGNATOR = alt_bom_designator
)
AND bic.BILL_SEQUENCE_ID = bbom.COMMON_BILL_SEQUENCE_ID
AND bic.INCLUDE_IN_COST_ROLLUP = 1
AND NVL( bic.eco_for_production, 2 ) = 2
/* Fix for BUG 1604207 */
AND NVL( bic.acd_type, 1 ) <> 3
AND (bic.EFFECTIVITY_DATE <= l_rev_datetime)
AND NVL(bic.DISABLE_DATE, l_rev_datetime+1) > l_rev_datetime
AND (
( unimp_flag = 2 AND (bic.IMPLEMENTATION_DATE IS NOT NULL))
OR (
unimp_flag = 1
AND bic.EFFECTIVITY_DATE =
(SELECT /*+ PUSH_SUBQ */ MAX(bic1.EFFECTIVITY_DATE) -- Added hint for 5678464
FROM BOM_INVENTORY_COMPONENTS bic1
WHERE bic1.COMPONENT_ITEM_ID = bic.COMPONENT_ITEM_ID
AND NVL( bic1.eco_for_production, 2 ) = 2
AND bic1.BILL_SEQUENCE_ID = bic.BILL_SEQUENCE_ID
AND ((bic1.OPERATION_SEQ_NUM = bic.OPERATION_SEQ_NUM)
OR
(decode(bic1.IMPLEMENTATION_DATE, NULL,
bic1.OLD_COMPONENT_SEQUENCE_ID,
bic1.COMPONENT_SEQUENCE_ID) =
decode(bic.IMPLEMENTATION_DATE, NULL,
bic.OLD_COMPONENT_SEQUENCE_ID,
bic.COMPONENT_SEQUENCE_ID)
)
)
AND bic1.EFFECTIVITY_DATE <= l_rev_datetime
)
)
)
-- Bug 2381452 and 3063633, phantom's TL resource/overhead/osp controlled by the use_phantom_routings flag
-- phantom's TL material/moh costs controlled by the profile
-- Bug 2455770. Do not rollup the TL yielded resource costs for a phantom subassembly since the resources are used at the parent
AND bp.ORGANIZATION_ID(+) = bbom.organization_id
AND msi.ORGANIZATION_ID = l_org_id_tbl(i)
AND msi.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
/* Bug 4547027 - Added the check to ignore the cost of inactive items. */
AND NVL(msi.inventory_item_status_code, 'NOT'||bp.bom_delete_status_code) <> NVL(bp.bom_delete_status_code,' ')
AND (
( nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)) <> 6)
OR
( nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)) = 6
AND cicd.yielded_cost is null
AND ((NVL(bp.use_phantom_routings,2) = 1 and cicd.cost_element_id NOT in (1,2))
OR
(l_phantom_mat = 1 and cicd.cost_element_id in (1,2))
OR
cicd.level_type = 2
)
)
)
AND cia_comp.ORGANIZATION_ID = l_org_id_tbl(i)
AND cia_comp.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
AND (
cia_comp.COST_TYPE_ID = l_dest_cost_type_id
OR
( cia_comp.COST_TYPE_ID = default_cost_type_id
AND NOT EXISTS (
SELECT /*+ PUSH_SUBQ */ 'X' -- Added hint for 5678464
FROM CST_ITEM_COSTS cia1
WHERE cia1.ORGANIZATION_ID = l_org_id_tbl(i)
AND cia1.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
AND cia1.COST_TYPE_ID = l_dest_cost_type_id)
)
OR
( cia_comp.COST_TYPE_ID = l_pcm_tbl(i)
AND NOT EXISTS (
SELECT /*+ PUSH_SUBQ */ 'X' -- Added hint for 5678464
FROM CST_ITEM_COSTS cia2
WHERE cia2.ORGANIZATION_ID = l_org_id_tbl(i)
AND cia2.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
AND cia2.COST_TYPE_ID in (l_dest_cost_type_id, default_cost_type_id))
)
)
AND cia_comp.COST_TYPE_ID IN (l_dest_cost_type_id,default_cost_type_id,l_pcm_tbl(i)) -- Added for 5678464
AND cia_comp.INVENTORY_ASSET_FLAG = 1
AND cicd.ORGANIZATION_ID = l_org_id_tbl(i)
AND cicd.INVENTORY_ITEM_ID = cia_comp.INVENTORY_ITEM_ID
AND cicd.COST_TYPE_ID = cia_comp.COST_TYPE_ID
AND cicd.operation_sequence_id = bomres.operation_sequence_id (+)
AND cicd.resource_seq_num = bomres.resource_seq_num (+)
AND cicd.basis_resource_id = bomres.resource_id (+)
GROUP BY
l_inv_item_tbl(i),
l_org_id_tbl(i),
l_ext_tbl(i),
DECODE(pl_operation_flag, 1, bic.operation_seq_num, NULL),
DECODE(pl_cost_code_flag, 1, cicd.RESOURCE_ID, NULL),
DECODE(pl_cost_code_flag, 1, DECODE(cicd.COST_ELEMENT_ID, 1, NULL, cicd.RESOURCE_RATE), NULL),
DECODE(pl_element_flag, 1, cicd.COST_ELEMENT_ID, NULL),
DECODE(pl_activity_flag, 1, cicd.ACTIVITY_ID, NULL),
/*Start changes for LBM */
/* basis type */
DECODE(PL_COST_CODE_FLAG, 1,
DECODE(BP.USE_PHANTOM_ROUTINGS, 1,
DECODE(NVL(BIC.WIP_SUPPLY_TYPE, NVL(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(CICD.BASIS_TYPE, 2, 2, 3, 3, 4, 4,1),
DECODE(bic.basis_type,2,2,1)),
DECODE(bic.basis_type,2,2,1)),
DECODE(bic.basis_type,2,2,1)),
/* basis_factor changed */
DECODE(pl_cost_code_flag, 1,
DECODE(NVL(bp.use_phantom_routings,2), 1,
DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(cicd.cost_element_id,
1, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
2, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
3, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
4, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
5, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE,
3, DECODE(bomres.basis_type,
2, (cicd.basis_factor*cia_comp.lot_size)/cia_assy.lot_size,
cicd.basis_factor),
4, DECODE(bomres.basis_type,
2, (cicd.basis_factor*cia_comp.lot_size)/cia_assy.lot_size,
cicd.basis_factor),1)),
DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
/* end changes for LBM */
DECODE(pl_cost_code_flag, 1,
DECODE(NVL(bp.use_phantom_routings,2), 1,
DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(cicd.cost_element_id, 3, cicd.basis_resource_id,
4, cicd.basis_resource_id,
5, cicd.basis_resource_id, null), null), null), null),
DECODE(pl_cost_code_flag, 1,
DECODE(NVL(bp.use_phantom_routings,2), 1,
DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(cicd.cost_element_id, 3, cicd.operation_sequence_id,
4, cicd.operation_sequence_id,
5, cicd.operation_sequence_id, null), null), null), null),
DECODE(pl_cost_code_flag, 1,
DECODE(NVL(bp.use_phantom_routings,2), 1,
DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(cicd.cost_element_id, 3, cicd.resource_seq_num,
4, cicd.resource_seq_num,
5, cicd.resource_seq_num, null), null), null), null),
1/(1-NVL(cia_assy.SHRINKAGE_RATE,0)),
DECODE(cicd.level_type, CM_THIS_LEVEL,
DECODE(NVL(bp.use_phantom_routings,2), 1,
DECODE(nvl(bic.WIP_SUPPLY_TYPE, Nvl(msi.WIP_SUPPLY_TYPE,1)),6,
DECODE(cicd.cost_element_id,3,CM_THIS_LEVEL,4,CM_THIS_LEVEL,5,CM_THIS_LEVEL,CM_PREVIOUS_LEVEL),
CM_PREVIOUS_LEVEL),
CM_PREVIOUS_LEVEL), CM_PREVIOUS_LEVEL);
select UPPER(wsm_enabled_flag)
into l_wsm_enabled
from mtl_parameters
where organization_id = csllc_level_org.organization_id;
l_last_updated_by,
alt_rtg_designator,
/* Bug 2305807. Need Effectivity Date */
revision_date,
csllc_level_org.organization_id,
cur_level,
l_dest_cost_type_id,
-- Obtain error message for bug 3097347
err_buf);
UPDATE CST_ITEM_COST_DETAILS cicd
SET (ITEM_COST,BASIS_FACTOR) =
(SELECT ROUND((cicd.USAGE_RATE_OR_AMOUNT *
NVL(SUM(cicd1.ITEM_COST),0)),inv_cursor.ext_precision),
NVL(SUM(cicd1.ITEM_COST),0) /
NVL(cicd.net_yield_or_shrinkage_factor,1) /
DECODE(cicd.resource_rate,0 , 1 , NVL(cicd.resource_rate,1))
FROM CST_ITEM_COST_DETAILS cicd1
WHERE cicd1.ORGANIZATION_ID = inv_cursor.organization_id
AND cicd1.COST_TYPE_ID = l_dest_cost_type_id
AND cicd1.INVENTORY_ITEM_ID = cicd.INVENTORY_ITEM_ID
AND cicd1.BASIS_TYPE <> 5 /* Total Value */
AND NOT (cicd1.LEVEL_TYPE = CM_THIS_LEVEL
AND
cicd1.COST_ELEMENT_ID = 2))
WHERE cicd.INVENTORY_ITEM_ID = inv_cursor.inventory_item_id
AND cicd.ORGANIZATION_ID = inv_cursor.organization_id
AND cicd.COST_TYPE_ID = l_dest_cost_type_id
AND cicd.BASIS_TYPE = 5 /* Total Value */
AND cicd.COST_ELEMENT_ID = 2;
UPDATE CST_ITEM_COST_DETAILS cicd
SET (OPERATION_SEQUENCE_ID,
DEPARTMENT_ID) =
(SELECT bos.OPERATION_SEQUENCE_ID,
bos.DEPARTMENT_ID
FROM BOM_OPERATIONAL_ROUTINGS bor,
BOM_OPERATION_SEQUENCES bos
WHERE bor.ASSEMBLY_ITEM_ID = cicd.INVENTORY_ITEM_ID
AND bor.ORGANIZATION_ID = l_org_id_tbl(i)
AND ((l_mfg_flag = 1 AND bor.ROUTING_TYPE = 1)
OR
(l_mfg_flag = 2)
)
AND bor.COMMON_ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID
AND bos.OPERATION_SEQ_NUM = cicd.OPERATION_SEQ_NUM
AND (NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, 'none') =NVL(alt_rtg_designator, 'none')
OR(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 = l_org_id_tbl(i)
AND bor1.ALTERNATE_ROUTING_DESIGNATOR =alt_rtg_designator
AND ((l_mfg_flag = 1 AND bor1.ROUTING_TYPE = 1)
OR
(l_mfg_flag = 2)
)
)
)
)
/* Fix for BUG 1608765 */
AND bos.EFFECTIVITY_DATE <= l_rev_datetime
AND NVL( bos.DISABLE_DATE, l_rev_datetime + 1) >= l_rev_datetime /*Changed > to >= for bug 6389605*/
-- This extra clause is because for
-- Flow Manufacturing a new column has been added to the
-- primary key of bos
AND nvl(bos.operation_type, 1) = 1
/* Right now, ECO does not support Op Yield */
AND ( NVL( bos.include_in_rollup, 1 ) = 1
OR bos.change_notice is not null
)
/* This section takes care of Unimplemented ECO Routings */
AND ((unimp_flag = 2 AND bos.implementation_date is not null)
OR
( unimp_flag = 1
AND bos.effectivity_date =
(SELECT MAX(bos2.effectivity_date )
FROM bom_operation_sequences bos2
WHERE bos2.routing_sequence_id = bos.routing_sequence_id
AND NVL(bos2.operation_type, 1) = NVL(bos.operation_type, 1)
AND bos2.operation_seq_num = bos.operation_seq_num
/* Fix for BUG 1607662 */
AND bos2.EFFECTIVITY_DATE <= fnd_date.canonical_to_date( revision_date )
)
)
)
AND NVL( bos.eco_for_production, 2 ) = 2
-- Added for Bug: 1078491 by ADEY
-- No to select disabled rows with same op seq num.
AND NVL(DISABLE_DATE, SYSDATE+1) >= SYSDATE /*Changed > to >= for bug 6389605*/
)
WHERE cicd.INVENTORY_ITEM_ID = l_inv_item_tbl(i)
AND cicd.COST_TYPE_ID = l_dest_cost_type_id
AND cicd.ORGANIZATION_ID = l_org_id_tbl(i)
AND cicd.LEVEL_TYPE = CM_PREVIOUS_LEVEL;
| Update rows in CST_ITEM_COSTS which had costs altered. |
| Because we need to include denormalized cost information. |
+------------------------------------------------------------*/
OPEN cllc_cur;
UPDATE CST_ITEM_COSTS cic
SET ( 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 ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY,
ROLLUP_ID,
ASSIGNMENT_SET_ID) =
( SELECT
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,
cicd.ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,2,
cicd.ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,3,
cicd.ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,4,
cicd.ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,5,
cicd.ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,1,
cicd.ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,2,
cicd.ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,3,
cicd.ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,4,
cicd.ITEM_COST,0),0)),
SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,5,
cicd.ITEM_COST,0),0)),
SUM(DECODE(COST_ELEMENT_ID,1,cicd.ITEM_COST)),
SUM(DECODE(COST_ELEMENT_ID,2,cicd.ITEM_COST)),
SUM(DECODE(COST_ELEMENT_ID,3,cicd.ITEM_COST)),
SUM(DECODE(COST_ELEMENT_ID,4,cicd.ITEM_COST)),
SUM(DECODE(COST_ELEMENT_ID,5,cicd.ITEM_COST)),
SUM(DECODE(LEVEL_TYPE,2,cicd.ITEM_COST,0)),
SUM(DECODE(LEVEL_TYPE,1,cicd.ITEM_COST,0)),
NVL(SUM(cicd.ITEM_COST),0),
SUM(DECODE(COST_ELEMENT_ID, 2,DECODE(LEVEL_TYPE,2,
cicd.ITEM_COST,0),
cicd.ITEM_COST)),
SUM(DECODE(COST_ELEMENT_ID, 2,DECODE(LEVEL_TYPE,1,
cicd.ITEM_COST,0),0)),
DECODE(conc_flag, 1, req_id, NULL),
DECODE(conc_flag, 1, prgm_appl_id, NULL),
DECODE(conc_flag, 1, prgm_id, NULL),
DECODE(conc_flag, 1, l_rollup_date, NULL),
l_rollup_date,
l_last_updated_by,
l_rollup_id,
l_assignment_set_id
FROM CST_ITEM_COST_DETAILS cicd
WHERE cicd.INVENTORY_ITEM_ID = cic.INVENTORY_ITEM_ID
AND cicd.ORGANIZATION_ID = l_org_id_tbl(i)
AND cicd.COST_TYPE_ID = l_dest_cost_type_id)
WHERE cic.INVENTORY_ITEM_ID = l_inv_item_tbl(i)
AND COST_TYPE_ID = l_dest_cost_type_id
AND cic.ORGANIZATION_ID = l_org_id_tbl(i);
SELECT
CSSR.source_organization_id
FROM
CST_SC_SOURCING_RULES CSSR
WHERE
CSSR.ROLLUP_ID = l_rollup_id
AND CSSR.inventory_item_id = l_inventory_item_id
AND CSSR.organization_id = l_dest_organization_id
AND CSSR.assignment_set_id = l_assignment_set_id
AND CSSR.source_type = 1;
UPDATE CST_SC_SOURCING_RULES CSSR
SET CSSR.MARKUP = l_markup,
CSSR.MARKUP_CODE = l_markup_code
WHERE CSSR.ROLLUP_ID = l_rollup_id
AND CSSR.inventory_item_id = l_item_id
AND CSSR.organization_id = l_org_id
AND CSSR.assignment_set_id = l_assignment_set_id
AND CSSR.source_type = 1
AND CSSR.source_organization_id = l_src_org_id
AND l_markup_code <> -1
AND l_markup_code IN (2,3); -- Req value or percent only
UPDATE CST_SC_SOURCING_RULES
SET (MARKUP,
MARKUP_CODE) =
(SELECT
INTERORG_TRNSFR_CHARGE_PERCENT,
MATL_INTERORG_TRANSFER_CODE
FROM MTL_INTERORG_PARAMETERS MIP
WHERE MIP.from_organization_id = l_src_org_id
AND MIP.to_organization_id = l_org_id
AND MIP.MATL_INTERORG_TRANSFER_CODE = 4
-- to Support only predefined %
)
WHERE
ROLLUP_ID = l_rollup_id
AND inventory_item_id = l_item_id
AND organization_id = l_org_id
AND assignment_set_id = l_assignment_set_id
AND source_type = 1
AND source_organization_id = l_src_org_id
AND markup_code IS NULL
AND markup IS NULL;
SELECT
CSSR.source_organization_id,
CSSR.ship_method
FROM
CST_SC_SOURCING_RULES CSSR
WHERE
CSSR.ROLLUP_ID = l_rollup_id
AND CSSR.inventory_item_id = l_inventory_item_id
AND CSSR.organization_id = l_dest_organization_id
AND CSSR.assignment_set_id = l_assignment_set_id
AND CSSR.source_type = 1;
UPDATE CST_SC_SOURCING_RULES CSSR
SET CSSR.SHIP_CHARGE = x_ship_charge,
CSSR.SHIP_CHARGE_CODE = x_ship_charge_code
WHERE CSSR.ROLLUP_ID = l_rollup_id
AND CSSR.inventory_item_id = l_item_id
AND CSSR.organization_id = l_org_id
AND CSSR.assignment_set_id = l_assignment_set_id
AND CSSR.source_type = 1
AND CSSR.source_organization_id = l_src_org_id
AND x_ship_charge <> -1
AND x_ship_charge_code IN (2,3); -- Req value or percent only
SELECT
CSSR.ROWID,
CSSR.vendor_id,
CSSR.vendor_site_id,
CSSR.ship_method
FROM
CST_SC_SOURCING_RULES CSSR
WHERE
CSSR.ROLLUP_ID = l_rollup_id
AND CSSR.inventory_item_id = l_inventory_item_id
AND CSSR.organization_id = l_dest_organization_id
AND CSSR.assignment_set_id = l_assignment_set_id
AND CSSR.source_type = 3
FOR UPDATE;
UPDATE CST_SC_SOURCING_RULES CSSR
SET CSSR.ITEM_COST = l_buy_cost,
CSSR.BUY_COST_FLAG = 'Y'
WHERE CSSR.ROLLUP_ID = l_rollup_id
AND CSSR.inventory_item_id = l_item_id
AND CSSR.organization_id = l_org_id
AND CSSR.assignment_set_id = l_assignment_set_id
AND CSSR.source_type = 3
AND l_buy_cost <> -1
AND CSSR.ROWID = l_curr_rowid;
SELECT DEFAULT_COST_TYPE_ID
INTO default_cost_type_id
FROM CST_COST_TYPES
WHERE COST_TYPE_ID = l_buy_cost_type_id;
UPDATE CST_SC_SOURCING_RULES
SET (ITEM_COST,
BUY_COST_FLAG) =
(SELECT NVL(SUM(CICD.ITEM_COST),0),
'Y'
FROM CST_ITEM_COST_DETAILS CICD, MTL_PARAMETERS MP
WHERE CICD.inventory_item_id = l_item_id
AND CICD.organization_id = l_org_id
AND MP.organization_id = l_org_id
AND (
CICD.cost_type_id = l_buy_cost_type_id
OR
(
CICD.cost_type_id = default_cost_type_id
AND NOT EXISTS (
SELECT 'X'
FROM CST_ITEM_COSTS cia1
WHERE cia1.inventory_item_id = l_item_id
AND cia1.organization_id = l_org_id
AND cia1.cost_type_id = l_buy_cost_type_id)
)
OR
(
CICD.cost_type_id = MP.primary_cost_method
AND NOT EXISTS (
SELECT 'X'
FROM CST_ITEM_COSTS cia2
WHERE cia2.inventory_item_id = l_item_id
AND cia2.organization_id = l_org_id
AND cia2.cost_type_id in (l_buy_cost_type_id, default_cost_type_id))
)
)
) /* Supply chain enhancement: default valuation cost type */
WHERE
ROLLUP_ID = l_rollup_id
AND inventory_item_id = l_item_id
AND organization_id = l_org_id
AND assignment_set_id = l_assignment_set_id
AND source_type = 3
AND item_cost IS NULL
AND buy_cost_flag IS NULL;
l_last_updated_by IN NUMBER,
alt_rtg_designator IN VARCHAR2,
rollup_date IN VARCHAR2,
l_organization_id IN NUMBER,
l_level IN NUMBER,
l_cost_type_id IN NUMBER,
-- Output error message for bug 3097347
x_err_buf OUT NOCOPY VARCHAR2)
return NUMBER IS
x_err_num NUMBER;
SELECT cllc.inventory_item_id INVENTORY_ITEM_ID,
bos.department_id DEPARTMENT_ID,
bos.operation_sequence_id OPERATION_SEQUENCE_ID,
bos.operation_seq_num OPERATION_SEQ_NUM,
((1/NVL(bos.yield,1))-1) OPERATION_YIELD_FACTOR
FROM bom_operation_sequences bos,
bom_operational_routings bor,
cst_sc_low_level_codes cllc
WHERE cllc.rollup_id = l_rollup_id
AND cllc.low_level_code = l_level
AND bor.assembly_item_id = cllc.inventory_item_id
/* Bug 3152221: use primary routing when we specify an alternate routing which
has not been defined */
AND ( NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, 'none')
= NVL(alt_rtg_designator, 'none')
OR (
(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 = l_organization_id
AND bor1.ALTERNATE_ROUTING_DESIGNATOR =
alt_rtg_designator
)
)
)
and bor.organization_id = l_organization_id
/* Bug 2379908. Use common_routing_sequence_id */
and bos.routing_sequence_id = bor.common_routing_sequence_id
and fnd_date.canonical_to_date(rollup_date) >= bos.effectivity_date
and NVL( bos.disable_date,
fnd_date.canonical_to_date(rollup_date) + 1 )
>= fnd_date.canonical_to_date(rollup_date)
/* Right now, ECO does not support Op Yield */
AND bos.implementation_date is not null
AND NVL( bos.include_in_rollup, 1 ) = 1
AND NVL( bos.eco_for_production, 2 ) = 2
and bos.operation_yield_enabled = 1
and bos.yield <> 1
/* Added join to remove extra rows for op yields in CICD */
and cllc.organization_id = bor.organization_id
order by inventory_item_id,
operation_seq_num;
select nvl(LAST_UPDATE_LOGIN ,-1 )
into l_login_id
from cst_sc_rollup_history
where rollup_id = l_rollup_id
and rownum =1;
INSERT INTO CST_ITEM_COST_DETAILS (
inventory_item_id,
organization_id,
SOURCE_ORGANIZATION_ID,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
operation_sequence_id,
operation_seq_num,
department_id,
level_type,
usage_rate_or_amount,
basis_type,
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,
yielded_cost,
resource_id)
SELECT l_inv_item_tbl(i),
l_organization_id,
l_organization_id,
l_cost_type_id,
l_rollup_date,
l_last_updated_by,
l_rollup_date,
l_last_updated_by,
l_login_id,
l_op_seq_id_tbl(i),
l_op_seq_num_tbl(i),
l_dep_id_tbl(i),
decode(cost_element_id,1,2,2,2,1),
round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
1,
1,
1,
round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
cost_element_id,
3,
decode(conc_flag,1,req_id,NULL),
decode(conc_flag,1,prgm_appl_id,NULL),
decode(conc_flag,1,prgm_id,NULL),
decode(conc_flag,1,sysdate,NULL),
round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
NULL
FROM cst_item_cost_details
WHERE organization_id = l_organization_id
AND inventory_item_id = l_inv_item_tbl(i)
AND cost_type_id = l_cost_type_id
AND operation_seq_num <= l_op_seq_num_tbl(i)
GROUP BY cost_element_id;
INSERT into cst_item_cost_details (
inventory_item_id,
organization_id,
SOURCE_ORGANIZATION_ID,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
operation_sequence_id,
operation_seq_num,
department_id,
level_type,
usage_rate_or_amount,
basis_type,
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,
yielded_cost,
resource_id)
SELECT l_inv_item_tbl(i),
l_organization_id,
l_organization_id,
l_cost_type_id,
l_rollup_date,
l_last_updated_by,
l_rollup_date,
l_last_updated_by,
l_login_id,
l_op_seq_id_tbl(i),
l_op_seq_num_tbl(i),
l_dep_id_tbl(i),
decode(cost_element_id,1,2,2,2,level_type),
round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
1,
1,
1,
round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
cost_element_id,
3,
decode(conc_flag,1,req_id,NULL),
decode(conc_flag,1,prgm_appl_id,NULL),
decode(conc_flag,1,prgm_id,NULL),
decode(conc_flag,1,sysdate,NULL),
round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
NULL
FROM cst_item_cost_details
WHERE organization_id = l_organization_id
AND inventory_item_id = l_inv_item_tbl(i)
AND cost_type_id = l_cost_type_id
AND operation_seq_num <= l_op_seq_num_tbl(i)
GROUP BY cost_element_id, level_type;
SELECT count(*)
INTO l_invalid_rows
FROM cst_item_cost_details
WHERE organization_id = l_organization_id
AND inventory_item_id = l_inv_item_tbl(dummy)
AND cost_type_id = l_cost_type_id
AND yielded_cost IS NOT NULL
AND yielded_cost <> item_cost
AND rownum=1;
l_last_updated_by in number,
l_rollup_date in varchar2,
req_id in number,
p_prg_appl_id in number,
p_prg_id in number,
err_buf out NOCOPY varchar2)
return integer
is
return_code NUMBER;
l_prg_update_date DATE;
l_prg_update_Date := p_rollup_date;
l_prg_update_date := NULL;
select alternate_bom_designator
into l_snapshot_designator
from cst_cost_types
where cost_type_id = l_cost_type_id;
DELETE BOM_INVENTORY_COMPONENTS bic
WHERE bic.BILL_SEQUENCE_ID IN
(SELECT bic2.BILL_SEQUENCE_ID
FROM BOM_INVENTORY_COMPONENTS bic2,
BOM_BILL_OF_MATERIALS bbom,
CST_SC_LOW_LEVEL_CODES csllc,
cst_sc_sourcing_rules cssr
WHERE csllc.ROLLUP_ID = l_rollup_id
AND cssr.rollup_id (+)= l_rollup_id
AND cssr.inventory_item_id (+)= csllc.inventory_item_id
AND cssr.organization_id (+)= csllc.organization_id
AND decode(cssr.source_type,2,1,NULL,1,0)=1
AND decode(cssr.allocation_percent,0,0,NULL,1,1)=1
AND bbom.ASSEMBLY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND bbom.ORGANIZATION_ID = csllc.organization_id
AND bbom.ALTERNATE_BOM_DESIGNATOR =l_snapshot_designator
AND bic2.BILL_SEQUENCE_ID = bbom.BILL_SEQUENCE_ID
);
DELETE BOM_REFERENCE_DESIGNATORS brd
WHERE NOT EXISTS
(SELECT 'Component Header exists'
FROM BOM_INVENTORY_COMPONENTS bic
WHERE bic.COMPONENT_SEQUENCE_ID = brd.COMPONENT_SEQUENCE_ID);
DELETE BOM_SUBSTITUTE_COMPONENTS bsc
WHERE NOT EXISTS
(SELECT 'Component Header exists'
FROM BOM_INVENTORY_COMPONENTS bic
WHERE bic.COMPONENT_SEQUENCE_ID = bsc.COMPONENT_SEQUENCE_ID);
/* ADD SQL TO DELETE FROM THE BOM_BILL_OF_MATERIALS. */
/*This should be done to update the WHO column of the table*/
if l_snapshot_designator <> alt_bom_designator THEN
sql_stmt_num:=25;
Delete BOM_BILL_OF_MATERIALS bbom
WHERE bbom.BILL_SEQUENCE_ID IN
(SELECT bbom2.BILL_SEQUENCE_ID
FROM BOM_BILL_OF_MATERIALS bbom2,
CST_SC_LOW_LEVEL_CODES csllc,
cst_item_costs cia,
cst_sc_sourcing_rules cssr
WHERE csllc.ROLLUP_ID = l_rollup_id
AND cssr.rollup_id (+)= l_rollup_id
AND cssr.inventory_item_id (+)= csllc.inventory_item_id
AND cssr.organization_id (+)= csllc.organization_id
AND decode(cssr.source_type,2,1,NULL,1,0)=1
AND decode(cssr.allocation_percent,0,0,NULL,1,1)=1
AND bbom2.ASSEMBLY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND bbom2.ORGANIZATION_ID = csllc.organization_id
AND bbom2.ALTERNATE_BOM_DESIGNATOR =l_snapshot_designator
AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND cia.ORGANIZATION_ID = csllc.organization_id
AND cia.COST_TYPE_ID = l_cost_type_id
AND cia.BASED_ON_ROLLUP_FLAG = 1
);
INSERT INTO BOM_BILL_OF_MATERIALS(
assembly_item_id,
organization_id,
alternate_bom_designator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
specific_assembly_comment,
pending_from_ecn,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
assembly_type,
bill_sequence_id,
common_bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
pk1_value,
pk2_value,
source_bill_sequence_id
)
select
bbom.assembly_item_id,
bbom.organization_id,
l_snapshot_designator,
p_rollup_date,
l_last_updated_by,
p_rollup_date,
l_last_updated_by,
l_last_updated_by,
bbom.specific_assembly_comment,
bbom.pending_from_ecn,
bbom.attribute_category,
bbom.attribute1,
bbom.attribute2,
bbom.attribute3,
bbom.attribute4,
bbom.attribute5,
bbom.attribute6,
bbom.attribute7,
bbom.attribute8,
bbom.attribute9,
bbom.attribute10,
bbom.attribute11,
bbom.attribute12,
bbom.attribute13,
bbom.attribute14,
bbom.attribute15,
bbom.assembly_type,
BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
bbom.common_bill_sequence_id,
l_req_id,
l_prg_appl_id,
l_prg_id,
l_prg_update_date,
bbom.assembly_item_id,
bbom.organization_id,
BOM_INVENTORY_COMPONENTS_S.NEXTVAL
FROM BOM_BILL_OF_MATERIALS bbom,
CST_ITEM_COSTS cia,
CST_SC_LOW_LEVEL_CODES csllc,
cst_sc_sourcing_rules cssr
WHERE csllc.ROLLUP_ID = l_rollup_id
AND cssr.rollup_id(+) = l_rollup_id
AND cssr.inventory_item_id (+)= csllc.inventory_item_id
AND cssr.organization_id (+)= csllc.organization_id
AND decode(cssr.source_type,2,1,NULL,1,0)=1
AND decode(cssr.allocation_percent,0,0,NULL,1,1)=1
AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND cia.ORGANIZATION_ID = csllc.organization_id
AND cia.COST_TYPE_ID = l_cost_type_id
AND cia.BASED_ON_ROLLUP_FLAG = 1
AND bbom.ASSEMBLY_ITEM_ID = cia.INVENTORY_ITEM_ID
AND bbom.ORGANIZATION_ID = csllc.organization_id
AND((l_mfg_flag = 1
AND
bbom.ASSEMBLY_TYPE = 1)
OR
(l_mfg_flag = 2)
)
AND( (NVL(bbom.ALTERNATE_BOM_DESIGNATOR, 'NONE') = NVL(alt_bom_designator, 'NONE'))
OR ((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 bbom.ASSEMBLY_ITEM_ID = bbom1.ASSEMBLY_ITEM_ID
AND bbom.ORGANIZATION_ID = bbom1.ORGANIZATION_ID
AND bbom1.ALTERNATE_BOM_DESIGNATOR = alt_bom_designator
AND((l_mfg_flag = 1
AND
bbom1.ASSEMBLY_TYPE = 1)
OR
(l_mfg_flag = 2)
)
))
)
AND EXISTS(
select 'ALternate Exist in the organizations'
from bom_alternate_designators bad
where bad.organization_id = csllc.organization_id
and bad.alternate_designator_code = l_snapshot_designator
)
AND NOT EXISTS(
select 'Bom exixts with alternate as snapshot'
from bom_bill_of_materials bbom2
where bbom2.assembly_item_id = cia.inventory_item_id
and bbom2.organization_id = csllc.organization_id
and bbom2.alternate_bom_designator = l_snapshot_designator
);
INSERT INTO BOM_INVENTORY_COMPONENTS
(
OPERATION_SEQ_NUM,
COMPONENT_ITEM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ITEM_NUM,
BASIS_TYPE,
COMPONENT_QUANTITY,
COMPONENT_YIELD_FACTOR,
COMPONENT_REMARKS,
EFFECTIVITY_DATE,
CHANGE_NOTICE,
IMPLEMENTATION_DATE,
DISABLE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PLANNING_FACTOR,
QUANTITY_RELATED,
SO_BASIS,
OPTIONAL,
MUTUALLY_EXCLUSIVE_OPTIONS,
INCLUDE_IN_COST_ROLLUP,
CHECK_ATP,
SHIPPING_ALLOWED,
REQUIRED_TO_SHIP,
REQUIRED_FOR_REVENUE,
INCLUDE_ON_SHIP_DOCS,
INCLUDE_ON_BILL_DOCS,
LOW_QUANTITY,
HIGH_QUANTITY,
ACD_TYPE,
OLD_COMPONENT_SEQUENCE_ID,
COMPONENT_SEQUENCE_ID,
BILL_SEQUENCE_ID,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
WIP_SUPPLY_TYPE,
OPERATION_LEAD_TIME_PERCENT,
REVISED_ITEM_SEQUENCE_ID,
SUPPLY_LOCATOR_ID,
SUPPLY_SUBINVENTORY,
PICK_COMPONENTS,
BOM_ITEM_TYPE)
SELECT
bic.OPERATION_SEQ_NUM,
bic.COMPONENT_ITEM_ID,
p_rollup_date,
l_last_updated_by,
p_rollup_date,
l_last_updated_by,
l_last_updated_by,
bic.ITEM_NUM,
bic.BASIS_TYPE,
bic.COMPONENT_QUANTITY,
bic.COMPONENT_YIELD_FACTOR,
bic.COMPONENT_REMARKS,
p_revision_date,
NULL,
p_revision_date,
NULL,
bic.ATTRIBUTE_CATEGORY,
bic.ATTRIBUTE1,
bic.ATTRIBUTE2,
bic.ATTRIBUTE3,
bic.ATTRIBUTE4,
bic.ATTRIBUTE5,
bic.ATTRIBUTE6,
bic.ATTRIBUTE7,
bic.ATTRIBUTE8,
bic.ATTRIBUTE9,
bic.ATTRIBUTE10,
bic.ATTRIBUTE11,
bic.ATTRIBUTE12,
bic.ATTRIBUTE13,
bic.ATTRIBUTE14,
bic.ATTRIBUTE15,
bic.PLANNING_FACTOR,
bic.QUANTITY_RELATED,
bic.SO_BASIS,
bic.OPTIONAL,
bic.MUTUALLY_EXCLUSIVE_OPTIONS,
bic.INCLUDE_IN_COST_ROLLUP,
bic.CHECK_ATP,
bic.SHIPPING_ALLOWED,
bic.REQUIRED_TO_SHIP,
bic.REQUIRED_FOR_REVENUE,
bic.INCLUDE_ON_SHIP_DOCS,
bic.INCLUDE_ON_BILL_DOCS,
bic.LOW_QUANTITY,
bic.HIGH_QUANTITY,
NULL, /* ACD_TYPE */
NULL,
BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
bbom2.BILL_SEQUENCE_ID,
DECODE(l_conc_flag, 1, req_id, NULL),
DECODE(l_conc_flag, 1, p_prg_appl_id, NULL),
DECODE(l_conc_flag, 1, p_prg_id, NULL),
DECODE(l_conc_flag, 1,
p_rollup_date, NULL),
bic.WIP_SUPPLY_TYPE,
bic.OPERATION_LEAD_TIME_PERCENT,
NULL,
bic.SUPPLY_LOCATOR_ID,
bic.SUPPLY_SUBINVENTORY,
bic.PICK_COMPONENTS,
bic.BOM_ITEM_TYPE
FROM BOM_BILL_OF_MATERIALS bbom,
BOM_BILL_OF_MATERIALS bbom2,
bom_inventory_components bic,
CST_ITEM_COSTS cia,
CST_SC_LOW_LEVEL_CODES csllc,
cst_sc_sourcing_rules cssr
WHERE csllc.ROLLUP_ID = l_rollup_id
AND cssr.rollup_id(+) = l_rollup_id
AND cssr.inventory_item_id (+)= csllc.inventory_item_id
AND cssr.organization_id (+)= csllc.organization_id
AND decode(cssr.source_type,2,1,NULL,1,0)=1
AND decode(cssr.allocation_percent,0,0,NULL,1,1)=1
AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
AND cia.ORGANIZATION_ID = csllc.organization_id
AND cia.COST_TYPE_ID = l_cost_type_id
AND cia.BASED_ON_ROLLUP_FLAG = 1
AND bbom.ASSEMBLY_ITEM_ID = cia.INVENTORY_ITEM_ID
AND bbom.ORGANIZATION_ID = csllc.organization_id
AND( (NVL(bbom.ALTERNATE_BOM_DESIGNATOR, 'NONE') = NVL(alt_bom_designator, 'NONE')
AND (nvl(l_snapshot_designator,'NONE') <> NVL(alt_bom_designator,'NONE')))
OR ((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 bbom.ASSEMBLY_ITEM_ID = bbom1.ASSEMBLY_ITEM_ID
AND bbom.ORGANIZATION_ID = bbom1.ORGANIZATION_ID
AND bbom1.ALTERNATE_BOM_DESIGNATOR =
alt_bom_designator
AND((l_mfg_flag = 1
AND
bbom1.ASSEMBLY_TYPE = 1)
OR
(l_mfg_flag = 2)
)
)
or (NVL(l_snapshot_designator,'NONE') = NVL(alt_bom_designator,'NONE'))))
)
AND bic.bill_sequence_id = bbom.common_bill_sequence_id
AND NVL( bic.eco_for_production, 2 ) = 2
/* Fix for BUG 1604207 */
AND NVL( bic.acd_type, 1 ) <> 3
AND bbom2.ASSEMBLY_ITEM_ID = cia.INVENTORY_ITEM_ID
AND bbom2.ORGANIZATION_ID = csllc.organization_id
AND bbom2.ALTERNATE_BOM_DESIGNATOR = l_snapshot_designator
AND bic.EFFECTIVITY_DATE <=fnd_date.canonical_to_date(revision_date)
AND NVL(bic.DISABLE_DATE,fnd_date.canonical_to_date(revision_date)+1)> fnd_date.canonical_to_date(revision_date)
AND(((l_unimp_flag = 2)AND(bic.IMPLEMENTATION_DATE IS NOT NULL))
OR((l_unimp_flag = 1)
AND bic.EFFECTIVITY_DATE = (
SELECT MAX(bic1.EFFECTIVITY_DATE)
FROM BOM_INVENTORY_COMPONENTS bic1
WHERE ((bic1.OPERATION_SEQ_NUM = bic.OPERATION_SEQ_NUM)
OR(decode(bic1.IMPLEMENTATION_DATE, NULL,bic1.OLD_COMPONENT_SEQUENCE_ID,bic1.COMPONENT_SEQUENCE_ID) =decode(bic.IMPLEMENTATION_DATE, NULL,bic.OLD_COMPONENT_SEQUENCE_ID,bic.COMPONENT_SEQUENCE_ID))
)
AND bic1.BILL_SEQUENCE_ID = bic.BILL_SEQUENCE_ID
AND bic1.COMPONENT_ITEM_ID = bic.COMPONENT_ITEM_ID
AND bic1.EFFECTIVITY_DATE <=fnd_date.canonical_to_date(revision_date)
AND NVL( bic1.eco_for_production, 2 ) = 2
)
)
)
AND EXISTS (
select 'ALternate Exist in the organization'
from bom_alternate_designators bad
where bad.organization_id = csllc.organization_id
and bad.alternate_designator_code = l_snapshot_designator
)
AND NOT EXISTS(
select 'Bom exists with alternate as snapshot'
from bom_inventory_components bic2,
bom_bill_of_materials bbom3
where bbom3.organization_id = csllc.organization_id
and bbom3.assembly_item_id = cia.inventory_item_id
and bbom3.alternate_bom_designator = l_snapshot_designator
and bic2.bill_sequence_id = bbom3.bill_sequence_id
);
/* Update the common_bill_sequence_id to bill_sequence_id for the all */
/* the asemblies headers were created for */
sql_stmt_num := 60;
update bom_bill_of_materials bbom
set bbom.common_bill_sequence_id = bbom.bill_sequence_id
where EXISTS(
select 1
from cst_sc_low_level_codes csllc,
cst_sc_sourcing_rules cssr
where csllc.rollup_id = l_rollup_id
and cssr.rollup_id(+) = l_rollup_id
and cssr.inventory_item_id (+)= csllc.inventory_item_id
and cssr.organization_id (+)= csllc.organization_id
and decode(cssr.source_type,2,1,null,1,0)=1
and decode(cssr.allocation_percent,0,0,null,1,1)=1
and bbom.organization_id = csllc.organization_id
and bbom.assembly_item_id = csllc.inventory_item_id
)
and bbom.alternate_bom_designator = l_snapshot_designator
and (nvl(l_snapshot_designator,'NONE') <> nvl(alt_bom_designator,'NONE')
or
bbom.common_bill_sequence_id = (select common_bill_sequence_id
from bom_bill_of_materials bbom2
where bbom2.assembly_item_id = bbom.assembly_item_id
and bbom2.organization_id = bbom.organization_id
and bbom2.alternate_bom_designator is null
)
);