[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_assembly_items (
i_rollup_id in number,
i_user_id in number,
i_login_id in number,
i_request_id in number,
i_prog_id in number,
i_prog_appl_id in number,
o_error_code out NOCOPY number,
o_error_msg out NOCOPY varchar2
)
is
l_stmt_num NUMBER(15);
** Delete process org/item combinations, if any
*/
l_stmt_num := 5;
delete cst_sc_lists csl
where exists (select 'process org'
from mtl_parameters mp
where mp.organization_id = csl.organization_id
and NVL(mp.process_enabled_flag, 'N') = 'Y')
;
insert into cst_sc_bom_explosion
(
ROLLUP_ID,
ASSEMBLY_ITEM_ID,
ASSEMBLY_ORGANIZATION_ID,
COMPONENT_SEQUENCE_ID,
COMPONENT_ITEM_ID,
COMPONENT_ORGANIZATION_ID,
COMPONENT_QUANTITY,
DELETED_FLAG,
EXPLODED_FLAG,
PLAN_LEVEL,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
select
i_rollup_id, -- ROLLUP_ID
-1, -- ASSEMBLY_ITEM_ID
-1, -- ASSEMBLY_ORGANIZATION_ID
null, -- COMPONENT_SEQUENCE_ID
CSL.inventory_item_id, -- COMPONENT_ITEM_ID
CSL.organization_id, -- COMPONENT_ORGANIZATION_ID
1, -- COMPONENT_QUANTITY
'N', -- DELETED_FLAG
'N', -- EXPLODED_FLAG
1, -- PLAN_LEVEL
sysdate, -- LAST_UPDATE_DATE
i_user_id, -- LAST_UPDATED_BY
i_login_id, -- LAST_UPDATE_LOGIN
sysdate, -- CREATION_DATE
i_user_id, -- CREATED_BY
i_request_id, -- REQUEST_ID
i_prog_appl_id, -- PROGRAM_APPLICATION_ID
i_prog_id, -- PROGRAM_ID
sysdate -- PROGRAM_UPDATE_DATE
from
cst_sc_lists CSL
where
CSL.rollup_id = i_rollup_id;
o_error_msg := 'CSTPSCEX.insert_assembly_items():' ||
to_char(l_stmt_num) || ':' ||
substrb(SQLERRM, 1, 1000);
end insert_assembly_items;
select min(MSV.rank)
into l_min_rank
from mrp_sources_v MSV
where
MSV.assignment_set_id = i_assignment_set_id and
MSV.inventory_item_id = i_inventory_item_id and
MSV.organization_id = i_organization_id and
MSV.allocation_percent is not null and
MSV.source_type is not null and
MSV.effective_date <= i_effective_date and
nvl( MSV.disable_date, i_effective_date + 1 ) > i_effective_date;
insert into CST_SC_SOURCING_RULES
(
ROLLUP_ID,
ASSIGNMENT_SET_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SOURCE_ORGANIZATION_ID,
VENDOR_ID,
VENDOR_SITE_ID,
SOURCE_TYPE,
SHIP_METHOD,
ALLOCATION_PERCENT,
MARKUP_CODE,
MARKUP,
ITEM_COST,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
SOURCING_RULE_NAME
)
select
i_rollup_id, -- ROLLUP_ID
MSV.assignment_set_id, -- ASSIGNMENT_SET_ID
MSV.inventory_item_id, -- INVENTORY_ITEM_ID
MSV.organization_id, -- ORGANIZATION_ID
MSV.source_organization_id, -- SOURCE_ORGANIZATION_ID
MSV.vendor_id, -- VENDOR_ID
MSV.vendor_site_id, -- VENDOR_SITE_ID
MSV.source_type, -- SOURCE_TYPE
MSV.ship_method, -- SHIP_METHOD
MSV.allocation_percent, -- ALLOCATION_PERCENT
null, -- MARKUP_CODE
null, -- MARKUP
null, -- ITEM_COST
sysdate, -- LAST_UPDATE_DATE
i_user_id, -- LAST_UPDATED_BY
i_login_id, -- LAST_UPDATE_LOGIN
sysdate, -- CREATION_DATE
i_user_id, -- CREATED_BY
i_request_id, -- REQUEST_ID
i_prog_appl_id, -- PROGRAM_APPLICATION_ID
i_prog_id, -- PROGRAM_ID
sysdate, -- PROGRAM_UPDATE_DATE
msv.sourcing_rule_name
from
mrp_sources_v MSV
where
MSV.assignment_set_id = i_assignment_set_id and
MSV.inventory_item_id = i_inventory_item_id and
MSV.organization_id = i_organization_id and
MSV.rank = l_min_rank and -- SCAPI: use minimum rank
MSV.allocation_percent is not null and
MSV.source_type is not null and
MSV.effective_date <= i_effective_date and
nvl( MSV.disable_date, i_effective_date + 1 ) > i_effective_date
and exists (select 1
from mtl_system_items msi
where msi.inventory_item_id = i_inventory_item_id
and msi.organization_id = nvl(MSV.source_organization_id,msi.organization_id));
select cssr.sourcing_rule_name, mp.organization_code
into l_sourcing_rule_name, l_organization_code
from cst_sc_sourcing_rules cssr, mtl_parameters mp
where rollup_id = i_rollup_id
and cssr.inventory_item_id = i_inventory_item_id
and cssr.organization_id = i_organization_id
and cssr.assignment_set_id = i_assignment_set_id
and mp.organization_id = cssr.source_organization_id
and NVL(mp.process_enabled_flag, 'N') = 'Y'
;
select distinct
CSSR.source_organization_id,
SOB_FROM.currency_code from_currency,
CSSR.organization_id,
SOB_TO.currency_code to_currency
from
cst_sc_sourcing_rules CSSR,
hr_organization_information OOD_FROM,
gl_sets_of_books SOB_FROM,
hr_organization_information OOD_TO,
gl_sets_of_books SOB_TO
where
CSSR.rollup_id = i_rollup_id and
CSSR.source_organization_id is not null and
CSSR.organization_id is not null and
OOD_FROM.organization_id = CSSR.source_organization_id AND
OOD_FROM.org_information_context = 'Accounting Information' AND
SOB_FROM.set_of_books_id = OOD_FROM.org_information1 and
OOD_TO.organization_id = CSSR.organization_id AND
OOD_TO.org_information_context = 'Accounting Information' AND
SOB_TO.set_of_books_id = OOD_TO.org_information1;
update cst_sc_sourcing_rules CSSR
set
CSSR.conversion_type = i_conversion_type,
CSSR.conversion_rate =
gl_currency_api.get_rate
(
rate.from_currency,
rate.to_currency,
sysdate,
i_conversion_type
)
where
CSSR.rollup_id = i_rollup_id and
CSSR.organization_id = rate.organization_id and
CSSR.source_organization_id = rate.source_organization_id;
select /*+ INDEX (CSBE CST_SC_BOM_EXPLOSION_N1)*/
CSBE.component_item_id,
CSBE.component_organization_id,
min( CSBE.plan_level ) prior_plan_level
from
cst_sc_bom_explosion CSBE
where
CSBE.rollup_id = i_rollup_id and
CSBE.exploded_flag = 'N' and
CSBE.plan_level <= decode( i_explosion_levels, null, CSBE.plan_level+1,
i_explosion_levels ) and
not exists
(
select /*+ INDEX (CSBE2 CST_SC_BOM_EXPLOSION_N1)*/ 'x'
from cst_sc_bom_explosion CSBE2
where
CSBE2.rollup_id = CSBE.rollup_id and
CSBE2.component_item_id = CSBE.component_item_id and
CSBE2.component_organization_id = CSBE.component_organization_id and
CSBE2.exploded_flag <> 'N'
)
group by
CSBE.component_item_id,
CSBE.component_organization_id;
select decode(nvl(msi.inventory_item_status_code,'NOT'||bp.bom_delete_status_code),
nvl(bp.bom_delete_status_code,' '),2,1)
into l_active_flag
from mtl_system_items msi,
bom_parameters bp
where msi.inventory_item_id = CSBE.component_item_id
and msi.organization_id = CSBE.component_organization_id
and bp.organization_id (+) = msi.organization_id;
insert into cst_sc_bom_explosion
(
ROLLUP_ID,
ASSEMBLY_ITEM_ID,
ASSEMBLY_ORGANIZATION_ID,
OPERATION_SEQ_NUM,
COMPONENT_SEQUENCE_ID,
COMPONENT_ITEM_ID,
COMPONENT_ORGANIZATION_ID,
COMPONENT_QUANTITY,
DELETED_FLAG,
EXPLODED_FLAG,
PLAN_LEVEL,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
select
i_rollup_id, -- ROLLUP_ID
CSSR.inventory_item_id, -- ASSEMBLY_ITEM_ID
CSSR.organization_id, -- ASSEMBLY_ORGANIZATION_ID
to_number( null ), -- OPERATION_SEQ_NUM
to_number( null ), -- COMPONENT_SEQUENCE_ID
CSSR.inventory_item_id, -- COMPONENT_ITEM_ID
CSSR.source_organization_id, -- COMPONENT_ORGANIZATION_ID
CSSR.allocation_percent / 100, -- COMPONENT_QUANTITY
'N', -- DELETED_FLAG
'N', -- EXPLODED_FLAG
CSBE.prior_plan_level + 1, -- PLAN_LEVEL
sysdate, -- LAST_UPDATE_DATE
i_user_id, -- LAST_UPDATED_BY
i_login_id, -- LAST_UPDATE_LOGIN
sysdate, -- CREATION_DATE
i_user_id, -- CREATED_BY
i_request_id, -- REQUEST_ID
i_prog_appl_id, -- PROGRAM_APPLICATION_ID
i_prog_id, -- PROGRAM_ID
sysdate -- PROGRAM_UPDATE_DATE
from
cst_sc_sourcing_rules CSSR, mtl_system_items msi /* Bug 6124274 */
where
CSSR.rollup_id = i_rollup_id and
msi.inventory_item_id = cssr.inventory_item_id and
msi.organization_id = cssr.organization_id and
CSSR.inventory_item_id = CSBE.component_item_id and
CSSR.organization_id = CSBE.component_organization_id and
CSSR.source_type = 1 -- Transfer items only
-- all we need is a UNION ALL, but I'm using UNION to
-- force an implicit sort so that the resulting connect by
-- select will (usually) be sorted by op_seq_num
union
select
i_rollup_id, -- ROLLUP_ID
BOM.assembly_item_id, -- ASSEMBLY_ITEM_ID
BOM.organization_id, -- ASSEMBLY_ORGANIZATION_ID
BIC.operation_seq_num, -- OPERATION_SEQ_NUM
BIC.component_sequence_id, -- COMPONENT_SEQUENCE_ID
BIC.component_item_id, -- COMPONENT_ITEM_ID
BOM.organization_id, -- COMPONENT_ORGANIZATION_ID
BIC.component_quantity, -- COMPONENT_QUANTITY
'N', -- DELETED_FLAG
'N', -- EXPLODED_FLAG
CSBE.prior_plan_level + 1, -- PLAN_LEVEL
sysdate, -- LAST_UPDATE_DATE
i_user_id, -- LAST_UPDATED_BY
i_login_id, -- LAST_UPDATE_LOGIN
sysdate, -- CREATION_DATE
i_user_id, -- CREATED_BY
i_request_id, -- REQUEST_ID
i_prog_appl_id, -- PROGRAM_APPLICATION_ID
i_prog_id, -- PROGRAM_ID
sysdate -- PROGRAM_UPDATE_DATE
from
bom_bill_of_materials BOM,
bom_inventory_components BIC
where
BOM.common_bill_sequence_id = BIC.bill_sequence_id and
BOM.assembly_item_id = CSBE.component_item_id and
BOM.organization_id = CSBE.component_organization_id and
----------------------------
--- effectivity checking
----------------------------
BIC.effectivity_date <= i_effective_date and
nvl( BIC.disable_date, i_effective_date + 1 ) > i_effective_date and
----------------------------
--- alternate bom designator
----------------------------
BOM.assembly_type =
decode( i_inc_eng_bill, 1, BOM.assembly_type, 1 ) AND
(
(
i_alt_bom_desg IS NULL AND
BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
)
OR
(
i_alt_bom_desg IS NOT NULL AND
BOM.ALTERNATE_BOM_DESIGNATOR = i_alt_bom_desg
)
OR
( i_alt_bom_desg IS NOT NULL AND
BOM.ALTERNATE_BOM_DESIGNATOR IS NULL AND
NOT EXISTS
(
SELECT 'X'
FROM BOM_BILL_OF_MATERIALS BOM2
WHERE BOM2.ORGANIZATION_ID = BOM.ORGANIZATION_ID AND
BOM2.ASSEMBLY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID AND
BOM2.ALTERNATE_BOM_DESIGNATOR = i_alt_bom_desg AND
BOM2.assembly_type =
decode( i_inc_eng_bill, 1, BOM2.assembly_type, 1 )
)
)
) AND
( BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
OR
BOM.ALTERNATE_BOM_DESIGNATOR = i_alt_bom_desg
) AND
----------------------------
--- implementation option
----------------------------
(
(
i_inc_unimpl_ecn = 2 AND
BIC.IMPLEMENTATION_DATE IS NOT NULL
)
OR
(
i_inc_unimpl_ecn = 1 AND
BIC.EFFECTIVITY_DATE =
(
SELECT MAX(EFFECTIVITY_DATE)
FROM BOM_INVENTORY_COMPONENTS BIC2
WHERE
BIC2.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID AND
BIC2.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID AND
(
decode( BIC2.IMPLEMENTATION_DATE,
NULL, BIC2.OLD_COMPONENT_SEQUENCE_ID,
BIC2.COMPONENT_SEQUENCE_ID ) =
decode( BIC.IMPLEMENTATION_DATE,
NULL, BIC.OLD_COMPONENT_SEQUENCE_ID,
BIC.COMPONENT_SEQUENCE_ID )
OR
BIC2.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
)
AND
BIC2.EFFECTIVITY_DATE <= i_effective_date
AND
NVL( BIC2.eco_for_production, 2 ) = 2
) -- end of subquery
)
) AND
----------------------------------------------------
-- This should take care of excluding model and oc
----------------------------------------------------
BIC.INCLUDE_IN_COST_ROLLUP = 1 and
----------------------------------------------------
-- This is for ECO changes in 11i.4
----------------------------------------------------
NVL( BIC.eco_for_production, 2 ) = 2 and
/* Fix for BUG 1604207 */
NVL( bic.acd_type, 1 ) <> 3 and
----------------------------------------------------
-- only insert BOM if there is a Make rule
----------------------------------------------------
0 < (
select nvl( sum( decode( CSSR.source_type, 2,
CSSR.allocation_percent, 0 ) ), 100 )
from cst_sc_sourcing_rules CSSR
where
CSSR.rollup_id = i_rollup_id and
CSSR.inventory_item_id = CSBE.component_item_id and
CSSR.organization_id = CSBE.component_organization_id
);
update cst_sc_bom_explosion
set exploded_flag = 'Y'
where rollup_id = i_rollup_id and
component_item_id = CSBE.component_item_id and
component_organization_id = CSBE.component_organization_id;
update cst_sc_bom_explosion CSBE
set CSBE.component_quantity
= (
select CSBE.component_quantity *
nvl( sum( decode( CSSR.source_type, 2,
CSSR.allocation_percent, 0 ) ) / 100, 1 )
from cst_sc_sourcing_rules CSSR
where CSSR.rollup_id = CSBE.rollup_id and
CSSR.inventory_item_id = CSBE.assembly_item_id and
CSSR.organization_id = CSBE.assembly_organization_id
)
where CSBE.rollup_id = i_rollup_id and
CSBE.assembly_organization_id = component_organization_id;
update /*+ INDEX (CSBE CST_SC_BOM_EXPLOSION_N1)*/
cst_sc_bom_explosion CSBE
set exploded_flag = 'Y'
where
rollup_id = i_rollup_id and
exists (
select /*+ INDEX (CSBE2 CST_SC_BOM_EXPLOSION_N1)*/ 'x'
from cst_sc_bom_explosion CSBE2
where
CSBE2.rollup_id = CSBE.rollup_id and
CSBE2.component_item_id = CSBE.component_item_id and
CSBE2.component_organization_id = CSBE.component_organization_id and
CSBE2.exploded_flag = 'Y'
);
select
CSBS.rowid,
decode( CIC.inventory_asset_flag, 2, 2,
decode( CIC.based_on_rollup_flag, 2, 2, 1 ) ) new_ext_cost_flag
from
cst_sc_bom_structures CSBS,
cst_item_costs CIC
where
CSBS.rollup_id = i_rollup_id and
CSBS.assembly_item_id = -1 and
CIC.inventory_item_id = CSBS.component_item_id and
CIC.organization_id = CSBS.component_organization_id and
CIC.cost_type_id = i_cost_type_id;
select
CSBS.top_inventory_item_id top_inventory_item_id,
CSBS.top_organization_id top_organization_id,
CSBS.sort_order sort_order,
CSBS.rowid,
CSBS.bom_level,
BIC.basis_type,
/* LBM Project 3926918: Changes made to support Lot Based Materials. Added decode to check for basis type
of the component. If lot based then component qty becomes compt_qty/lot_size else it is unchanged. */
DECODE(BIC.basis_type, 2, CSBS.component_quantity/nvl(CIC.lot_size, 1), CSBS.component_quantity) component_quantity,
-- Bug 5139919; nvl is added to CIC.lot_size
decode(nvl(msi.inventory_item_status_code,'NOT'||bp.bom_delete_status_code),nvl(bp.bom_delete_status_code,' '),2,1) active_flag
from
cst_sc_bom_structures CSBS,
cst_item_costs CIC,
mtl_system_items MSI,
bom_inventory_components BIC,
bom_parameters bp /* Bug 4547027 */
where
CSBS.rollup_id = i_rollup_id and
CSBS.assembly_item_id = -1 and
CIC.inventory_item_id (+) = CSBS.top_inventory_item_id and
CIC.organization_id (+) = CSBS.top_organization_id and
CIC.cost_type_id (+) = i_cost_type_id and
MSI.inventory_item_id = CSBS.component_item_id and
MSI.organization_id = CSBS.component_organization_id and
bp.organization_id (+) = CSBS.component_organization_id and /* Bug 4547027 */
BIC.component_sequence_id (+) = CSBS.component_sequence_id
UNION ALL
select
CSBS.top_inventory_item_id top_inventory_item_id,
CSBS.top_organization_id top_organization_id,
CSBS.sort_order sort_order,
CSBS.rowid,
CSBS.bom_level,
BIC.basis_type,
/* LBM Project 3926918: Changes made to support Lot Based Materials. Added decode to check for basis type
of the component. If lot based then component qty becomes compt_qty/lot_size else it is unchanged. */
DECODE(BIC.basis_type, 2, CSBS.component_quantity/nvl(CIC.lot_size, 1), CSBS.component_quantity) component_quantity,
-- Bug 5139919; nvl is added to CIC.lot_size
decode(nvl(msi.inventory_item_status_code,'NOT'||bp.bom_delete_status_code),nvl(bp.bom_delete_status_code,' '),2,1) active_flag
from
cst_sc_bom_structures CSBS,
cst_item_costs CIC,
mtl_system_items MSI,
bom_inventory_components BIC,
bom_parameters bp /* Bug 4547027 */
where
CSBS.rollup_id = i_rollup_id and
CSBS.assembly_item_id <> -1 and
CIC.inventory_item_id (+) = CSBS.assembly_item_id and
CIC.organization_id (+) = CSBS.assembly_organization_id and
CIC.cost_type_id (+) = i_cost_type_id and
MSI.inventory_item_id = CSBS.component_item_id and
MSI.organization_id = CSBS.component_organization_id and
bp.organization_id (+) = CSBS.component_organization_id and /* Bug 4547027 */
BIC.component_sequence_id (+) = CSBS.component_sequence_id
order by
top_inventory_item_id,
top_organization_id,
sort_order;
select component_yield_flag
into l_comp_yield_flag
from cst_cost_types
where cost_type_id = i_cost_type_id;
update cst_sc_bom_structures CSBS
set CSBS.extend_cost_flag
= assm.new_ext_cost_flag
where CSBS.rowid = assm.rowid;
select ext_qty_stack(comp.bom_level)* (cic1.lot_size/cic2.lot_size)
into ext_qty_stack( comp.bom_level )
from cst_item_costs cic1,
cst_item_costs cic2,
cst_sc_bom_structures csbs
where csbs.rowid = comp.rowid
and CIC1.inventory_item_id (+) = CSBS.assembly_item_id
and CIC1.organization_id (+) = CSBS.assembly_organization_id
and CIC1.cost_type_id (+) = i_cost_type_id
and CIC2.inventory_item_id (+) = CSBS.top_inventory_item_id
and CIC2.organization_id (+) = CSBS.top_organization_id
and CIC2.cost_type_id (+) = i_cost_type_id ;
update cst_sc_bom_structures CSBS
set
CSBS.component_quantity = comp.component_quantity,
CSBS.extended_quantity = ext_qty_stack( comp.bom_level ),
CSBS.include_in_cost_rollup = comp.include_in_cost_rollup,
CSBS.extend_cost_flag = ext_cost_flag_stack( comp.bom_level ),
CSBS.phantom_flag = comp.phantom_flag,
CSBS.phantom_sub_assy_flag = phtm_sub_assy_stack( comp.bom_level )
where CSBS.rowid = comp.rowid;
select
CSBE.component_item_id,
CSBE.component_organization_id
from
cst_sc_bom_explosion CSBE
where
CSBE.rollup_id = i_rollup_id and
CSBE.assembly_item_id = -1 and
CSBE.deleted_flag = 'Y'; -- Bug 3665428: make snapshot only for valid items without loop
delete cst_sc_bom_structures
where rollup_id in (i_rollup_id, -1*i_rollup_id);
insert into cst_sc_bom_structures
(
ROLLUP_ID,
TOP_INVENTORY_ITEM_ID,
TOP_ORGANIZATION_ID,
SORT_ORDER,
BOM_LEVEL,
ASSEMBLY_ITEM_ID,
ASSEMBLY_ORGANIZATION_ID,
COMPONENT_SEQUENCE_ID,
COMPONENT_ITEM_ID,
COMPONENT_ORGANIZATION_ID,
COMPONENT_QUANTITY,
EXTENDED_QUANTITY,
INCLUDE_IN_COST_ROLLUP,
EXTEND_COST_FLAG,
PHANTOM_FLAG,
PHANTOM_SUB_ASSY_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
select
i_rollup_id, -- ROLLUP_ID
top_assm.component_item_id, -- TOP_INVENTORY_ITEM_ID
top_assm.component_organization_id, -- TOP_ORGANIZATION_ID
rownum, -- SORT_ORDER
level, -- BOM_LEVEL
CSBE.assembly_item_id, -- ASSEMBLY_ITEM_ID
CSBE.assembly_organization_id, -- ASSEMBLY_ORGANIZATION_ID
CSBE.component_sequence_id, -- COMPONENT_SEQUENCE_ID
CSBE.component_item_id, -- COMPONENT_ITEM_ID
CSBE.component_organization_id, -- COMPONENT_ORGANIZATION_ID
CSBE.component_quantity, -- COMPONENT_QUANTITY
1, -- EXTENDED_QUANTITY
1, -- INCLUDE_IN_COST_ROLLUP
1, -- EXTEND_COST_FLAG
2, -- PHANTOM_FLAG
2, -- PHANTOM_SUB_ASSY_FLAG
sysdate, -- LAST_UPDATE_DATE
i_user_id, -- LAST_UPDATED_BY
i_login_id, -- LAST_UPDATE_LOGIN
sysdate, -- CREATION_DATE
i_user_id, -- CREATED_BY
i_request_id, -- REQUEST_ID
i_prog_appl_id, -- PROGRAM_APPLICATION_ID
i_prog_id, -- PROGRAM_ID
sysdate -- PROGRAM_UPDATE_DATE
from
cst_sc_bom_explosion CSBE
start with
rollup_id = i_rollup_id and
assembly_item_id = -1 and
component_item_id = top_assm.component_item_id and
component_organization_id = top_assm.component_organization_id
connect by
prior rollup_id = rollup_id and
prior component_item_id = assembly_item_id and
prior component_organization_id = assembly_organization_id and
level <= i_report_levels;
insert into cst_sc_bom_structures
(
ROLLUP_ID,
TOP_INVENTORY_ITEM_ID,
TOP_ORGANIZATION_ID,
SORT_ORDER,
BOM_LEVEL,
ASSEMBLY_ITEM_ID,
ASSEMBLY_ORGANIZATION_ID,
COMPONENT_SEQUENCE_ID,
COMPONENT_ITEM_ID,
COMPONENT_ORGANIZATION_ID,
COMPONENT_QUANTITY,
EXTENDED_QUANTITY,
INCLUDE_IN_COST_ROLLUP,
EXTEND_COST_FLAG,
PHANTOM_FLAG,
PHANTOM_SUB_ASSY_FLAG,
ERROR_CODE,
ERROR_MESG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
values
(
i_rollup_id, -- ROLLUP_ID
top_assm.component_item_id, -- TOP_INVENTORY_ITEM_ID
top_assm.component_organization_id, -- TOP_ORGANIZATION_ID
0, -- SORT_ORDER
0, -- BOM_LEVEL
-1, -- ASSEMBLY_ITEM_ID
-1, -- ASSEMBLY_ORGANIZATION_ID
null, -- COMPONENT_SEQUENCE_ID
top_assm.component_item_id, -- COMPONENT_ITEM_ID
top_assm.component_organization_id, -- COMPONENT_ORGANIZATION_ID
0, -- COMPONENT_QUANTITY
0, -- EXTENDED_QUANTITY
2, -- INCLUDE_IN_COST_ROLLUP
2, -- EXTEND_COST_FLAG
2, -- PHANTOM_FLAG
2, -- PHANTOM_SUB_ASSY_FLAG
l_err_code, -- ERROR_CODE
l_err_mesg, -- ERROR_MESG
sysdate, -- LAST_UPDATE_DATE
i_user_id, -- LAST_UPDATED_BY
i_login_id, -- LAST_UPDATE_LOGIN
sysdate, -- CREATION_DATE
i_user_id, -- CREATED_BY
i_request_id, -- REQUEST_ID
i_prog_appl_id, -- PROGRAM_APPLICATION_ID
i_prog_id, -- PROGRAM_ID
sysdate -- PROGRAM_UPDATE_DATE
);
update cst_sc_bom_structures CSBS
set CSBS.component_revision =
(
select
substr( max( to_char( MIR.effectivity_date, 'YYYY/MM/DD HH24:MI:SS' ) ||
MIR.revision ), 20 )
from
mtl_item_revisions MIR
where
MIR.inventory_item_id = CSBS.component_item_id and
MIR.organization_id = CSBS.component_organization_id and
MIR.effectivity_date <= i_effective_date
)
where CSBS.rollup_id = i_rollup_id;
insert into cst_sc_bom_structures
(
ROLLUP_ID,
TOP_INVENTORY_ITEM_ID,
TOP_ORGANIZATION_ID,
SORT_ORDER,
BOM_LEVEL,
ASSEMBLY_ITEM_ID,
ASSEMBLY_ORGANIZATION_ID,
COMPONENT_SEQUENCE_ID,
COMPONENT_ITEM_ID,
COMPONENT_ORGANIZATION_ID,
COMPONENT_QUANTITY,
EXTENDED_QUANTITY,
INCLUDE_IN_COST_ROLLUP,
EXTEND_COST_FLAG,
PHANTOM_FLAG,
PHANTOM_SUB_ASSY_FLAG,
COMPONENT_REVISION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
select
-1*i_rollup_id, -- ROLLUP_ID
CSBS.top_inventory_item_id, -- TOP_INVENTORY_ITEM_ID
CSBS.top_organization_id, -- TOP_ORGANIZATION_ID
max(CSBS.sort_order), -- SORT_ORDER
max(CSBS.bom_level), -- BOM_LEVEL
max(CSBS.assembly_item_id), -- ASSEMBLY_ITEM_ID
CSBS.assembly_organization_id, -- ASSEMBLY_ORGANIZATION_ID
null, -- COMPONENT_SEQUENCE_ID
CSBS.component_item_id, -- COMPONENT_ITEM_ID
CSBS.component_organization_id, -- COMPONENT_ORGANIZATION_ID
sum(CSBS.component_quantity), -- COMPONENT_QUANTITY
sum(CSBS.extended_quantity), -- EXTENDED_QUANTITY
null, -- INCLUDE_IN_COST_ROLLUP
CSBS.extend_cost_flag, -- EXTEND_COST_FLAG
CSBS.phantom_flag, -- PHANTOM_FLAG
max(CSBS.phantom_sub_assy_flag), -- PHANTOM_SUB_ASSY_FLAG
CSBS.component_revision, -- COMPONENT_REVISION
sysdate, -- LAST_UPDATE_DATE
i_user_id, -- LAST_UPDATED_BY
i_login_id, -- LAST_UPDATE_LOGIN
sysdate, -- CREATION_DATE
i_user_id, -- CREATED_BY
i_request_id, -- REQUEST_ID
i_prog_appl_id, -- PROGRAM_APPLICATION_ID
i_prog_id, -- PROGRAM_ID
sysdate -- PROGRAM_UPDATE_DATE
from
cst_sc_bom_structures CSBS
where
rollup_id = i_rollup_id
group by
CSBS.top_inventory_item_id,
CSBS.top_organization_id,
CSBS.assembly_organization_id,
CSBS.component_item_id,
CSBS.component_organization_id,
CSBS.extend_cost_flag,
CSBS.phantom_flag,
CSBS.component_revision;
update cst_sc_bom_explosion CSBE
set deleted_flag = 'Y'
where
CSBE.rollup_id = i_rollup_id and
CSBE.deleted_flag = 'N' and
not exists ( select 'Item in List'
from cst_sc_lists CSL
where CSL.rollup_id = i_rollup_id
and CSL.inventory_item_id = CSBE.component_item_id
and CSL.organization_id = CSBE.component_organization_id );
insert into cst_sc_low_level_codes
(
ROLLUP_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LOW_LEVEL_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
select distinct
i_rollup_id, -- ROLLUP_ID
CSBE.component_item_id, -- INVENTORY_ITEM_ID
CSBE.component_organization_id, -- ORGANIZATION_ID
l_low_level_code, -- LOW_LEVEL_CODE
sysdate, -- LAST_UPDATE_DATE
i_user_id, -- LAST_UPDATED_BY
i_login_id, -- LAST_UPDATE_LOGIN
sysdate, -- CREATION_DATE
i_user_id, -- CREATED_BY
i_request_id, -- REQUEST_ID
i_prog_appl_id, -- PROGRAM_APPLICATION_ID
i_prog_id, -- PROGRAM_ID
sysdate -- PROGRAM_UPDATE_DATE
from
cst_sc_bom_explosion CSBE
where
CSBE.rollup_id = i_rollup_id and
CSBE.deleted_flag = 'N' and
not exists
(
select 'x'
from cst_sc_bom_explosion CSBE2
where
CSBE2.rollup_id = CSBE.rollup_id and
CSBE2.assembly_item_id = CSBE.component_item_id and
CSBE2.assembly_organization_id = CSBE.component_organization_id and
CSBE2.deleted_flag = 'N'
);
update cst_sc_bom_explosion CSBE
set deleted_flag = 'Y'
where
CSBE.rollup_id = i_rollup_id and
CSBE.deleted_flag = 'N' and
not exists
(
select 'x'
from cst_sc_bom_explosion CSBE2
where
CSBE2.rollup_id = CSBE.rollup_id and
CSBE2.assembly_item_id = CSBE.component_item_id and
CSBE2.assembly_organization_id = CSBE.component_organization_id and
CSBE2.deleted_flag = 'N'
);
select CCT.frozen_standard_flag
into l_frozen_standard_flag
from cst_cost_types CCT
where CCT.cost_type_id = i_cost_type_id;
delete cst_sc_low_level_codes CSLLC
where
CSLLC.rollup_id = i_rollup_id and
exists
(
select 'x'
from mtl_material_transactions MMT
where MMT.inventory_item_id = CSLLC.inventory_item_id and
MMT.organization_id = CSLLC.organization_id
);
'Cannot update standard cost for ' || to_char(SQL%ROWCOUNT) ||
' items due to existing MMT transactions';
select cst_lists_s.nextval
into l_rollup_id
from dual;
insert into cst_sc_rollup_history
(
rollup_id,
explosion_level,
report_level,
assignment_set_id,
conversion_type,
cost_type_id,
buy_cost_type_id,
revision_date,
INC_UNIMP_ECN_FLAG,
ENG_BILL_FLAG,
alt_bom_desg,
alt_rtg_desg,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
select
l_rollup_id,
i_explosion_levels,
l_report_levels,
i_assignment_set_id,
i_conversion_type,
i_cost_type_id,
i_buy_cost_type_id,
i_effective_date,
l_include_unimpl_eco,
l_include_eng,
i_alt_bom_desg,
i_alt_rtg_desg,
sysdate, -- LAST_UPDATE_DATE
i_user_id, -- LAST_UPDATED_BY
i_login_id, -- LAST_UPDATE_LOGIN
sysdate, -- CREATION_DATE
i_user_id, -- CREATED_BY
i_request_id, -- REQUEST_ID
i_prog_appl_id, -- PROGRAM_APPLICATION_ID
i_prog_id, -- PROGRAM_ID
sysdate -- PROGRAM_UPDATE_DATE
from dual
where not exists
(
select 'x'
from cst_sc_rollup_history
where rollup_id = l_rollup_id
);
CSTPSCEX.insert_assembly_items
(
l_rollup_id,
i_user_id,
i_login_id,
i_request_id,
i_prog_id,
i_prog_appl_id,
o_error_code,
o_error_msg
);
update cst_sc_rollup_history CSRH
set CSRH.explosion_time = (SYSDATE - l_timestamp) * 86400
where CSRH.rollup_id = l_rollup_id;
select max(low_level_code)+2
into l_report_levels
from cst_sc_low_level_codes
where rollup_id = l_rollup_id;
update cst_sc_rollup_history CSRH
set CSRH.low_level_code_time = (SYSDATE - l_timestamp) * 86400
where CSRH.rollup_id = l_rollup_id;
update cst_sc_rollup_history CSRH
set CSRH.remove_costs_time = (SYSDATE - l_timestamp) * 86400
where CSRH.rollup_id = l_rollup_id;
i_user_id, -- L_LAST_UPDATED_BY NUMBER IN
1, -- CONC_FLAG NUMBER IN
l_include_unimpl_eco, -- UNIMP_FLAG NUMBER IN
i_lock_flag, -- LOCKING_FLAG NUMBER IN
to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'), -- ROLLUP_DATE VARCHAR2 IN
/* Bug 2305807. Need Effectivity Date. Bug 3098303: pass full time components */
to_char(i_effective_date, 'YYYY/MM/DD HH24:MI:SS'),
i_alt_bom_desg, -- ALT_BOM_DESIGNATOR VARCHAR2 IN
i_alt_rtg_desg, -- ALT_RTG_DESIGNATOR VARCHAR2 IN
l_rollup_option, -- ROLLUP_OPTION NUMBER IN
1, -- REPORT_OPTION NUMBER IN
i_exclude_eng, -- L_MFG_FLAG NUMBER IN
o_error_msg, -- ERR_BUF VARCHAR2 OUT
i_buy_cost_detail -- BUY_COST_DETAIL NUMBER IN
);
update cst_sc_rollup_history CSRH
set CSRH.rollup_time = (SYSDATE - l_timestamp) * 86400
where CSRH.rollup_id = l_rollup_id;
update cst_sc_rollup_history CSRH
set CSRH.bom_structure_time = (SYSDATE - l_timestamp) * 86400
where CSRH.rollup_id = l_rollup_id;