[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| We do not need to select component_sequence_id through a subquery.
| The bill_sequence_id of the option class and join condition
| ic1.component_item_id = bcol1.inventory_item_id will select unique components
| for the option class.
|
| ksarkar 01-JUN-01 Bugfix 1812159
| Date operations make a disabled item effective and increases the
| component usage in configured item.
|
| sbhaskar 16-JUN-01 Bugfix 1835357
| Replaced fnd_file calls with oe_debug_pub
|
| ksarkar 19-JUL-01 Bugfix 1845141
| mtl_system_items_tl is not getting updated with correct description .
| Added message in lines 1486-87 and 1669-70 for better understanding of
| "WHEN OTHERS" exception .
|
| ksarkar 19-JUL-01 Bugfix 1876998
| Remove semicolon from comment to improve performance.
|
| Modified on 24-AUG-2001 by Sushant Sawant: BUG #1957336
| Added a new functionality for preconfigure bom.
|
| ksarkar 13-NOV-01 Bugfix 2086234
| Add condition "Implementation_date is not null" in Inherit_op_seq_ml
|
| ksarkar 26-NOV-01 Bugfix 2115056
| Copy base model attributes ( DFF's) to configured item.
|
| ksarkar 04-JAN-02 Bugfix 2171807 ( Bugfix 2163311 in main )
| Catalog description is not getting updated in Master Org.
|
| sbhaskar 07-FEB-02 Bugfix 2215274 (bugfix 2221008 in main)
| Performance : Replaced bind variables with column join.
|
| ksarkar 21-FEB-02 Bugfix 2222518 (bugfix 2236844 in main )
| Option Class operation seq not getting inherited to child
| included items.
|
| ksarkar 28-FEB-02 Bugfix 2244856 (bugfix 2246663 in main )
| Unable to handle no_data_found error
|
| ksarkar 09-APR-02 Bugfix 1912376 (bugfix 2292468 in main )
| Checking item effectivity till schedule ship date
|
| ksarkar 17-MAY-02 Bugfix 2307936 (bugfix 2379051 in main )
| New logic of operation seq inheritence
|
| ssawant 28-MAY-02 Bugfix 2312199 (Refix for bug1912376 )
| bug 1912376 could still fail in case of sourced lower level models
|
| ksarkar 04-JUN-02 Bugfix 2374246 (Bugfix 2402935 in main )
| Config item created with no BOM
|
| ksarkar 04-JUN-02 Bugfix 2389283 (Bugfix 2402935 in main )
| Included Item under a non-phantom sub model gets attached to top
| model in config item bill.
|
| ksarkar 26-JUN-02 Bugfix 2433862 ( Bugfix 2435855 in main )
| Failed to insert rows with null op seq num in bom_inventory_components
| when ATO under PTO has no routing but inherit_op_seq profile is
| set to YES.
|
| ksarkar 10-OCT-02 Bugfix 2590966 ( Bugfix 2618752 in main )
| Catalog descriptions not rolled up correctly for multi -level
| configurations.
|
| ksarkar 21-NOV-02 Bugfix 2524562 ( Bugfix 2652271 in main )
| Inconsistent use of order dates in validating BOM effectivity.
|
| ksarkar 18-FEB-03 Bugfix 2765635 ( Bugfix 2807548 in main )
| New custom hook for catalog description of multi-level model .
|
| ksarkar 23-FEB-03 Bugfix 2814257 ( Bugfix 2817041 in main )
| Fix for 2524562 not working when opseq profile is turned ON.
|
| ksarkar 02-JUL-03 Bugfix 2929861 ( Bugfix 2986192 in main )
| Config item creation will now depend upon the value of
| profile BOM:CONFIG_EXCEPTION
|
| Modified on 14-MAR-2003 By Sushant Sawant
| Decimal-Qty Support for Option Items.
|
| ksarkar 20-NOV-03 Bugfix 3222932
| Inserting actual eff and disable dates for config components
| New consolidation logic
|
|
| ssawant 09-JAN-04 Bugfix 3358160
| Error Message Added CTO_ZERO_BOM_COMP for option item with zero qty on config bom.
|
|
| ssawant 15-JAN-04 Bugfix 3374548
| Added bill_sequence_id to condition to avoid corrupt data from bom_inventory_comps_interface.
|
|
| ssawant 29-JAN-04 Bugfix 3367823
| Accounted for UOM conversion in bom_inventory_components.
|
|
| ssawant 05-FEB-04 Bugfix 3389846
| Accounted for disable date greater than EstRelDate, sysdate
|
| ssawant 05-FEB-04 Bugfix 3389846
| Accounted for disable date greater than EstRelDate, sysdate. Disable date clause has been changed to compare
| only if it is not null. This improves the query as well.
|
|
| Modified on 26-Mar-2004 By Sushant Sawant
| Fixed Bug#3484511
| all queries referencing oe_system_parameters_all
| should be replaced with a function call to oe_sys_parameters.value
|
| Modified : 21-JUN-2004 Sushant Sawant
| Fixed bug 3710032.
| Substitute components were not copied correctly.
|
|
| Modified : 12-AUG-2004 Sushant Sawant
| Fixed bug 3793286.
| Front Ported bug 3674833
|
|
| Modified : 13-AUG-2004 Kiran Konada
| bug fix 3759118,FP 3810243
| Added implemenation_date to BOM_BOM
| as sysdate
|
| Modified : 11-05-2004 Kiran Konada
| Fixed issue with bug 3793286.(Front Ported bug 3674833)
| added abs() in where clause as model_comp_seq in
| pl/sql record was a -ve value
|
|
|
| Modified : 12-08-2004 Sushant Sawant
| Fixed issue for bug 3793286
| commented "IF prev_comp_item_id <> component_item_id_arr(x1) then"
| This bug was not fixed properly for components with
| multiple effectivity date windows.
|
|
| Modified : 02-02-2005 Kiran Konada
| bug#4092184 FP:11.5.9 - 11.5.10 :I
| customer bug#4081613
| if custom package CTO_CUSTOM_CATALOG_DESC.catalog_desc_method is
| set to 'C' to use custom api AND if model item is not assigned
| to a catalog group. Create configuration process fails
|
| Fix has been made not to honor the custom package if a ato model
| is not assigned to a catalog gtroup or there are no descrptive elements
| defined for a catalog group. In fumction create_bom_data_ml
|
|
| Modified : 01-APR-2005 Sushant Sawant
| Fixed issue for bug4271269.
| populate structure_type_id and effectivity_control columns in
| bom_bill_of_materials view.
|
| Modified by Renga Kannan on 09/01/06 for bug 4542461
| Modified : 09-02-2005 Renga Kannan
| Fixed the following issues in LBM and effecitivity
| part of code
|
| 1.) LBM code does not handle null value for basis type
| Added nvl clause for all insert stmt from bom_inventory_components
| to bom_inventory_components_interface
|
| 2.) for overlapping effectivity dates with components having
| having different basis type the message is not raised
| properly. fixd that code
|
| 3.) Clubbing component code is inserting null qty value into
| bic interface. Fixed the code not to insert these rows.
|
| Modified by Renga Kannan on 09/07/2005
| Bug Fix 4595162
| Modified the code that populates basis type to
| bom_inventory_components table. As per bom team
| basis_type should have null for 'ITEM' and 2 for 'LOT'
|
|
*============================================================================*/
-- Bug 1912376 Declaring Global variable to hold the value of Schedule Ship Date
g_SchShpDate Date;
PROCEDURE update_item_num(
p_parent_bill_seq_id IN NUMBER,
p_item_num IN OUT NOCOPY NUMBER, /* NOCOPY project */
p_org_id IN NUMBER,
p_seq_increment IN NUMBER);
/* Cursor to select dropped lines */
cursor missed_lines ( xlineid number,
xconfigbillid number,
xEstRelDate date ) is /* Effectivity_date changes */
select line_id
from bom_cto_order_lines
where parent_ato_line_id=xlineid
and parent_ato_line_id <> line_id /* to avoid selecting top model */
minus
select revised_item_sequence_id /* new column used to store line_id */
from bom_inventory_comps_interface
where bill_sequence_id = xconfigbillid
and greatest(sysdate, xEstRelDate ) >= effectivity_date
and (( disable_date is null ) or ( disable_date is not null and greatest(sysdate, xEstRelDate) <= disable_date )) ;
select assembly_item_id,component_item_id,operation_seq_num,max(disable_date) disable_date
from bom_inventory_comps_interface
where bill_sequence_id = lconfigbillid
group by assembly_item_id,component_item_id,operation_seq_num;
select component_item_id,component_sequence_id,operation_seq_num,effectivity_date,disable_date
from bom_inventory_comps_interface
where bill_sequence_id = lConfigBillId --Bugfix 6603382: So that components belonging to this bill only are picked up
order by component_item_id,operation_seq_num,effectivity_Date,disable_date;
select ato_line_id, program_id , header_id
into v_ato_line_id, v_program_id , v_header_id
from bom_cto_order_lines
where line_id = pLineId ;
select bom_inventory_components_s.nextval
into lConfigBillId
from dual;
delete from bom_inventory_comps_interface where bill_sequence_id = lConfigBillId ;
oe_debug_pub.add('create_bom_ml: ' || 'deleted from bici ' || to_char(sql%rowcount) , 1);
Selecting Schedule_ship_date of ATO Model and assigning
this to a Global variable
+--------------------------------------------*/
lStmtNum := 21;
select nvl(schedule_ship_date,sysdate)
into g_SchShpDate
from bom_cto_order_lines
where line_id = pLineId ;
select nvl(master_organization_id,-99) --bugfix 2646849: master_organization_id can be 0
into lOEValidationOrg
from oe_order_lines_all oel,
oe_system_parameters_all ospa
where oel.line_id = pLineid
and nvl(oel.org_id, -1) = nvl(ospa.org_id, -1) --bug 1531691
and oel.inventory_item_id = pModelId;
select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
into lOEValidationOrg from oe_order_lines_all oel
where oel.line_id = pLineId ;
select bcol.line_id, bcol.inventory_item_id, bcol.parent_ato_line_id,
bcol.ordered_quantity
into lLineId, lModelId, lParentAtoLineId, lOrderedQty
from bom_cto_order_lines bcol
where bcol.line_id = lParentAtoLineId;
select CAL.CALENDAR_DATE
into lEstRelDate
from bom_calendar_dates cal,
mtl_system_items msi,
bom_cto_order_lines bcol,
mtl_parameters mp
where msi.organization_id = pOrgId
and msi.inventory_item_id = pModelId
and bcol.line_id = pLineId
and bcol.inventory_item_id = msi.inventory_item_id
and mp.organization_id = msi.organization_id
and cal.calendar_code = mp.calendar_code
and cal.exception_set_id = mp.calendar_exception_set_id
and cal.seq_num =
(select cal2.prior_seq_num - lTotLeadTime
from bom_calendar_dates cal2
where cal2.calendar_code = mp.calendar_code
and cal2.exception_set_id = mp.calendar_exception_set_id
and cal2.calendar_date = trunc(bcol.schedule_ship_date));
In new code , we will check op seq profile before insert into bic interface.
If op seq = 1 , we will insert into bet and then to bic interface
If op seq != 1 , we will do direct insert into bic interface
+----------------------------------------------------------------------------*/
/*-------------------------------------------------------------------------+
Check profile option 'Inherit Operation_sequence_number'. If it is set
to 'Yes', ensure that the childern default the operation sequence number
from its parent, if not already assigned.
Open : As in prev releases, this does not cover non-ATPable SMCs because
they are not in oe_order_lines. Do we need to ?
+--------------------------------------------------------------------------*/
lOpseqProfile := FND_PROFILE.Value('BOM:CONFIG_INHERIT_OP_SEQ');
insert into BOM_INVENTORY_COMPS_INTERFACE
(
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
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,
pick_components,
model_comp_seq_id,
supply_subinventory,
supply_locator_id,
bom_item_type,
optional_on_model, -- New columns for configuration
parent_bill_seq_id, -- BOM restructure project
plan_level -- Used by CTO only
,revised_item_sequence_id /* 2524562 : New column added to store line_id */
,Assembly_item_id /* Bug fix: 4147224 */
, basis_type, /* LBM project */
batch_id
)
select
nvl(ic1.operation_seq_num,1),
decode(bcol1.config_item_id, NULL, ic1.component_item_id, -- new
bcol1.config_item_id),
SYSDATE, -- last_updated_date
1, -- last_updated_by
SYSDATE, -- creation_date
1, -- created_by
1, -- last_update_login
ic1.item_num,
Round(
CTO_UTILITY_PK.convert_uom( bcol1.order_quantity_uom, msi_child.primary_uom_code, bcol1.ordered_quantity , msi_child.inventory_item_id )
/ CTO_UTILITY_PK.convert_uom(bcol2.order_quantity_uom, msi_parent.primary_uom_code, NVL(bcol2.ordered_quantity,1) , msi_parent.inventory_item_id )
, 7) , -- qty = comp_qty / model_qty /* Decimal-Qty Support for Option Items */
ic1.component_yield_factor,
ic1.component_remarks, --Bugfix 7188428
--NULL, --ic1.component_remark
-- 3222932 TRUNC(SYSDATE), -- effective date
-- 3222932 If eff_date > sysdate , insert eff_Date else insert sysdate
decode(
greatest(ic1.effectivity_date,sysdate),
ic1.effectivity_date ,
ic1.effectivity_date ,
sysdate ),
NULL, -- change notice
SYSDATE, -- implementation_date
-- 3222932 NULL, -- disable date
nvl(ic1.disable_date,g_futuredate), -- 3222932
ic1.attribute_category,
ic1.attribute1,
ic1.attribute2,
ic1.attribute3,
ic1.attribute4,
ic1.attribute5,
ic1.attribute6,
ic1.attribute7,
ic1.attribute8,
ic1.attribute9,
ic1.attribute10,
ic1.attribute11,
ic1.attribute12,
ic1.attribute13,
ic1.attribute14,
ic1.attribute15,
100, -- planning_factor */
2, -- quantity_related */
decode(bcol1.config_item_id, NULL,
decode(ic1.bom_item_type,4,ic1.so_basis,2),
2), -- so_basis */
2, -- optional */
2, -- mutually_exclusive_options */
decode(bcol1.config_item_id, NULL,
decode(ic1.bom_item_type,4, ic1.include_in_cost_rollup, 2),
1), -- Cost_rollup */
decode(bcol1.config_item_id, NULL, decode(ic1.bom_item_type,4, ic1.check_atp, 2),
2), -- check_atp */
2, -- shipping_allowed = NO */
2, -- required_to_ship = NO */
ic1.required_for_revenue,
ic1.include_on_ship_docs,
ic1.include_on_bill_docs,
NULL, -- low_quantity */
NULL, -- high_quantity */
NULL, -- acd_type */
NULL, --old_component_sequence_id */
bom_inventory_components_s.nextval, -- component sequence id */
lConfigBillId, -- bill sequence id */
NULL, -- request_id */
NULL, -- program_application_id */
NULL, -- program_id */
NULL, -- program_update_date */
ic1.wip_supply_type,
2, -- pick_components = NO */
decode(bcol1.config_item_id, NULL, (-1)*ic1.component_sequence_id, ic1.component_sequence_id), -- saved model comp seq for later use. If config item, then saved model comp seq id as positive, otherwise negative.
ic1.supply_subinventory,
ic1.supply_locator_id,
--ic1.bom_item_type
decode(bcol1.config_item_id, NULL, ic1.bom_item_type, 4), -- new
1, --optional_on_model,
ic1.bill_sequence_id, --parent_bill_seq_id,
(bcol1.plan_level-bcol2.plan_level) --plan_level
,bcol1.line_id /* 2524562 Storing line_id */
,bcol3.inventory_item_id /* Bug fix: 4863055 */
, nvl(ic1.basis_type,1), /* LBM project */
cto_msutil_pub.bom_batch_id
from
bom_inventory_components ic1,
bom_cto_order_lines bcol1, --Option
bom_cto_order_lines bcol2, -- Parent-Model
bom_cto_order_lines bcol3, -- Parent-component
mtl_system_items msi_child ,
mtl_system_items msi_parent
-- begin bugfix 1653881
where ic1.bill_sequence_id = ( -- this we find the assembly to which
select common_bill_sequence_id -- d1.component_seq_id belongs and then find
from bom_bill_of_materials bbm -- bill for it in Mfg org.We find equivalent
where organization_id = pOrgId -- compnent in this bill by joining
and alternate_bom_designator is null -- on component_item_id. Each component
and assembly_item_id =( --is assumed to be used at one operation only
select distinct assembly_item_id -- Operation_Seq_num must be same in bills in
from bom_bill_of_materials bbm1, -- all organizations for that assembly
bom_inventory_components bic1
where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
and component_sequence_id = bcol1.component_sequence_id
and bbm1.assembly_item_id = bcol3.inventory_item_id ))
and ic1.component_item_id = bcol1.inventory_item_id
and msi_child.inventory_item_id = bcol1.inventory_item_id
and msi_child.organization_id = pOrgId
and msi_parent.inventory_item_id = bcol2.inventory_item_id
and msi_parent.organization_id = pOrgId
-- end bugfix 1653881
-- begin bugfix 1912376
-- and ic1.effectivity_date <= g_SchShpDate /* New Approach for Effectivity Dates */
and ic1.implementation_date is not null --bug 4122212
-- and NVL(ic1.disable_date, (lEstRelDate + 1)) >= greatest( nvl( lEstRelDate, sysdate ) , sysdate ) /* bug #3389846 */
-- end bugfix 1912376
and ( ic1.disable_date is null or
(ic1.disable_date is not null and ic1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
and (( ic1.optional = 1 and ic1.bom_item_type = 4)
or
( ic1.bom_item_type in (1,2)))
and bcol1.ordered_quantity <> 0
and bcol1.line_id <> bcol2.line_id -- not the top ato model
and bcol1.parent_ato_line_id = bcol2.line_id
and bcol1.parent_ato_line_id is not null
and bcol1.link_to_line_id is not null
and bcol2.line_id = pLineId
and bcol2.ship_from_org_id = bcol1.ship_from_org_id
and (bcol3.parent_ato_line_id = bcol1.parent_ato_line_id
or
bcol3.line_id = bcol1.parent_ato_line_id)
-- new condition to include parent model
-- in a sub-assy since its
-- ato_line_id is not equal
-- to itself, unlike a top
-- model.
and bcol3.line_id = bcol1.link_to_line_id;
oe_debug_pub.add ('create_bom_ml: ' || 'First -- Inserted ' || lCnt ||' rows into BOM_INVENTORY_COMPS_INTERFACE.',1);
select count(*) into v_zero_qty_count from bom_inventory_comps_interface
where bill_sequence_id = lConfigBillId and component_quantity = 0 ;
select concatenated_segments into v_model_item_name
from mtl_system_items_kfv
where inventory_item_id = pModelId
and rownum = 1 ;
insert into BOM_INVENTORY_COMPS_INTERFACE
(
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
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,
pick_components,
model_comp_seq_id,
supply_subinventory,
supply_locator_id,
bom_item_type,
optional_on_model, -- New columns for configuration
parent_bill_seq_id, -- BOM restructure project.
plan_level -- Used by CTO only.
, basis_type, /* LBM project */
batch_id
)
select
nvl(ic1.operation_seq_num,1),
ic1.component_item_id,
SYSDATE, -- last_updated_date
1, -- last_updated_by
SYSDATE, -- creation_date
1, -- created_by
1, -- last_update_login
ic1.item_num,
decode( nvl(ic1.basis_type,1), 1 , Round( ( ic1.component_quantity * ( bcol1.ordered_quantity
/ bcol2.ordered_quantity)), 7 ) , Round(ic1.component_quantity , 7 ) ) , /* Decimal-Qty Support for Option Items, LBM project */
ic1.component_yield_factor,
ic1.component_remarks, --Bugfix 7188428
--NULL, -- ic1.component_remark
-- 3222932 TRUNC(SYSDATE), -- effective date
decode( -- 3222932
greatest(ic1.effectivity_date,sysdate),
ic1.effectivity_date ,
ic1.effectivity_date ,
sysdate ),
NULL, -- change notice
SYSDATE, -- implementation_date
-- 3222932 NULL, -- disable date
nvl(ic1.disable_date,g_futuredate), -- 3222932
ic1.attribute_category,
ic1.attribute1,
ic1.attribute2,
ic1.attribute3,
ic1.attribute4,
ic1.attribute5,
ic1.attribute6,
ic1.attribute7,
ic1.attribute8,
ic1.attribute9,
ic1.attribute10,
ic1.attribute11,
ic1.attribute12,
ic1.attribute13,
ic1.attribute14,
ic1.attribute15,
100, -- planning_factor
2, -- quantity_related
ic1.so_basis,
2, -- optional
2, -- mutually_exclusive_options
ic1.include_in_cost_rollup,
ic1.check_atp,
2, -- shipping_allowed = NO
2, -- required_to_ship = NO
ic1.required_for_revenue,
ic1.include_on_ship_docs,
ic1.include_on_bill_docs,
NULL, -- low_quantity
NULL, -- high_quantity
NULL, -- acd_type
NULL, -- old_component_sequence_id
bom_inventory_components_s.nextval, -- component sequence id
lConfigBillId, -- bill sequence id
NULL, -- request_id
NULL, -- program_application_id
NULL, -- program_id
NULL, -- program_update_date
ic1.wip_supply_type,
2, -- pick_components = NO
(-1)*ic1.component_sequence_id, -- model comp seq for later use
ic1.supply_subinventory,
ic1.supply_locator_id,
ic1.bom_item_type,
2, --optional_on_model,
ic1.bill_sequence_id, --parent_bill_seq_id,
bcol1.plan_level+1-bcol2.plan_level --plan_level
, nvl(ic1.basis_type,1), /* LBM project */
cto_msutil_pub.bom_batch_id
from
bom_cto_order_lines bcol1, -- component
bom_cto_order_lines bcol2, -- Model
mtl_system_items si1,
mtl_system_items si2,
bom_bill_of_materials b,
bom_inventory_components ic1
where si1.organization_id = pOrgId
and bcol1.inventory_item_id = si1.inventory_item_id
and si1.bom_item_type in (1,2) -- model, option class
and si2.inventory_item_id = bcol2.inventory_item_id
and si2.organization_id = si1.organization_id
and si2.bom_item_type = 1
and ((bcol1.parent_ato_line_id = bcol2.line_id
-- bugfix 2215274: replaced bind variable with column join to improve performance.
and ( bcol1.bom_item_type <> 1
or
(bcol1.bom_item_type = 1 and nvl(bcol1.wip_supply_type, 0) = 6))
)
or bcol1.line_id = bcol2.line_id
) -- new condition to get the parent itself
-- bugfix 2215274: replaced bind variable with column join to improve performance.
and bcol2.line_id = pLineId
and si1.organization_id = b.organization_id
and bcol1.inventory_item_id = b.assembly_item_id
and b.alternate_bom_designator is NULL
and b.common_bill_sequence_id = ic1.bill_sequence_id
and ic1.optional = 2 -- optional = no
-- inserted code for checking bugfix 1522647
-- and ic1.effectivity_date <= greatest( NVL(lEstRelDate,sysdate),sysdate)
-- begin bugfix 1912376
-- and ic1.effectivity_date <= greatest( NVL(g_SchShpDate,sysdate),sysdate) /* New approach for effectivity dates */
-- end bugfix 1912376
and ic1.implementation_date is not null
-- and NVL(ic1.disable_date,NVL(lEstRelDate, SYSDATE)+1) > NVL(lEstRelDate,SYSDATE) /* NEW approach for effectivity */
-- and NVL(ic1.disable_date,SYSDATE) >= SYSDATE /* New approach for effectivity */
and ( ic1.disable_date is null or
(ic1.disable_date is not null and ic1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
-- code of bugfix 1522647 ends here
and ic1.bom_item_type = 4;
oe_debug_pub.add ('create_bom_ml: ' || 'Second -- Inserted ' || lCnt ||' rows',1);
update bom_inventory_comps_interface
set disable_date = g_futuredate
where (component_item_id, nvl(assembly_item_id,-1),disable_date)
in ( select
component_item_id,nvl(assembly_item_id,-1),max(disable_date)
from bom_inventory_comps_interface
where bill_sequence_id = lConfigBillId
group by component_item_id, assembly_item_id
)
and bill_sequence_id = lConfigBillId
and disable_date <> g_futuredate ;
select 1 into v_overlap_check
from dual
where exists
( select * from bom_inventory_comps_interface
where bill_sequence_id = lConfigBillId
group by component_item_id, assembly_item_id
having count(distinct operation_seq_num) > 1
);
select s1.component_item_id,
s1.operation_seq_num, s1.effectivity_date, s1.disable_date,
s2.operation_Seq_num , s2.effectivity_date, s2.disable_date
BULK COLLECT INTO
v_t_overlap_comp_item_id,
v_t_overlap_src_op_seq_num, v_t_overlap_src_eff_date, v_t_overlap_src_disable_date ,
v_t_overlap_dest_op_seq_num , v_t_overlap_dest_eff_date, v_t_overlap_dest_disable_date
from bom_inventory_comps_interface s1 , bom_inventory_comps_interface s2
where s1.component_item_id = s2.component_item_id and s1.assembly_item_id = s2.assembly_item_id
--and s1.effectivity_date between s2.effectivity_date and s2.disable_date
and s1.effectivity_date > s2.effectivity_date --Bugfix 6603382
and s1.effectivity_date < s2.disable_date --Bugfix 6603382
and s1.bill_sequence_id = lConfigBillId --Bugfix 6603382
and s2.bill_sequence_id = lConfigBillId --Bugfix 6603382
and s1.component_sequence_id <> s2.component_sequence_id ;
select segment1
into
l_model_name
from mtl_system_items
where inventory_item_id=pModelId
and rownum=1;
oe_debug_pub.add('Select missed component details.. ' ,1);
select substrb(msi.concatenated_segments,1,50),
'Not Available' ,
-1
into v_missed_item,v_missed_line_number,v_order_number
from mtl_system_items_kfv msi, bom_cto_order_lines bcol
where msi.organization_id = bcol.ship_from_org_id
and msi.inventory_item_id = bcol.inventory_item_id
and bcol.line_id = v_missed_line_id;
select substrb(msi.concatenated_segments,1,50),
to_char(oel.line_number)||'.'||to_char(oel.shipment_number) ||decode(oel.option_number,NULL,NULL,'.'
||to_char(option_number)),
oeh.order_number
into v_missed_item,v_missed_line_number,v_order_number
from mtl_system_items_kfv msi, oe_order_lines_all oel,oe_order_headers_all oeh
where msi.organization_id = oel.ship_from_org_id
and msi.inventory_item_id = oel.inventory_item_id
and oel.header_id = oeh.header_id
and oel.line_id = v_missed_line_id;
oe_debug_pub.add('Select model.. ' ,1);
select substrb(concatenated_segments,1,50)
into v_model
from mtl_system_items_kfv
where organization_id = pOrgId
and inventory_item_id = pModelId ;
oe_debug_pub.add('Select Org.. ' ,1);
select organization_code
into lOrg_code
from mtl_parameters
where organization_id =pOrgId ;
select segment1 into v_problem_model from mtl_system_items
where inventory_item_id = pModelId and rownum = 1 ;
select segment1 into v_problem_config from mtl_system_items
where inventory_item_id = pConfigId and rownum = 1 ;
select organization_name into v_error_org from inv_organization_name_v
where organization_id = pOrgId ;
select segment1 into v_problem_model from mtl_system_items
where inventory_item_id = pModelId and rownum = 1 ;
select segment1 into v_problem_config from mtl_system_items
where inventory_item_id = pConfigId and rownum = 1 ;
select organization_name into v_error_org from inv_organization_name_v
where organization_id = pOrgId ;
select segment1 into v_problem_model from mtl_system_items
where inventory_item_id = pModelId and rownum = 1 ;
select segment1 into v_problem_config from mtl_system_items
where inventory_item_id = pConfigId and rownum = 1 ;
select organization_name into v_error_org from inv_organization_name_v
where organization_id = pOrgId ;
select oel.line_number || '.' || oel.shipment_number
into v_problem_model_line_num
from oe_order_lines_all oel
where line_id = pLineId ;
SELECT u.user_name
INTO lplanner_code
FROM mtl_system_items_vl item
,mtl_planners p
,fnd_user u
WHERE item.inventory_item_id = pModelId
and item.organization_id = pOrgId
and p.organization_id = item.organization_id
and p.planner_code = item.planner_code
and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
select segment1 into v_problem_model from mtl_system_items
where inventory_item_id = pModelId and rownum = 1 ;
select segment1 into v_problem_config from mtl_system_items
where inventory_item_id = pConfigId and rownum = 1 ;
select organization_name into v_error_org from inv_organization_name_v
where organization_id = pOrgId ;
select oel.line_number || '.' || oel.shipment_number
into v_problem_model_line_num
from oe_order_lines_all oel
where line_id = pLineId ;
SELECT u.user_name
INTO lplanner_code
FROM mtl_system_items_vl item
,mtl_planners p
,fnd_user u
WHERE item.inventory_item_id = pModelId
and item.organization_id = pOrgId
and p.organization_id = item.organization_id
and p.planner_code = item.planner_code
and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
insert into BOM_INVENTORY_COMPS_INTERFACE
(
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
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,
pick_components,
model_comp_seq_id,
bom_item_type,
optional_on_model, -- New columns for configuration
parent_bill_seq_id, -- BOM restructure project.
plan_level -- Used by CTO only.
, basis_type, /* LBM project */
batch_id
)
select
1, -- operation_seq_num
bcol.inventory_item_id,
SYSDATE, -- last_updated_date
1, -- last_updated_by
SYSDATE, -- creation_date
1, -- created_by
1, -- last_update_login
9, -- item_num
1, -- comp_qty
1, -- yield_factor
NULL, --ic1.component_remark
SYSDATE, -- effective date --bug4150255: Removed the trunc so that time is also populated.
NULL, -- change notice
SYSDATE, -- implementation_date
NULL, -- disable date
NULL, -- attribute_category
NULL, -- attribute1
NULL, -- attribute2
NULL, -- attribute3
NULL, -- attribute4
NULL, -- attribute5
NULL, -- attribute6
NULL, -- attribute7
NULL, -- attribute8
NULL, -- attribute9
NULL, -- attribute10
NULL, -- attribute11
NULL, -- attribute12
NULL, -- attribute13
NULL, -- attribute14
NULL, -- attribute15
100, -- planning_factor
2, -- quantity_related
2, -- so_basis
2, -- optional
2, -- mutually_exclusive_options
2, -- include_in_cost_rollup
2, -- check_atp
2, -- shipping_allowed = NO
2, -- required_to_ship = NO
2, -- required_for_revenue
2, -- include_on_ship_docs
2, -- include_on_bill_docs
NULL, -- low_quantity
NULL, -- high_quantity
NULL, -- acd_type
NULL, -- old_component_sequence_id
bom_inventory_components_s.nextval, -- component sequence id
lConfigBillId, -- bill sequence id
NULL, -- request_id
NULL, -- program_application_id
NULL, -- program_id
NULL, -- program_update_date
6, -- wip_supply_type
2, -- pick_components = NO
NULL, -- model comp seq id for later use
1, -- bom_item_type
1, --optional_on_model,
0, --parent_bill_seq_id,
0 --plan_level
, 1, -- basis_type /* LBM project */
cto_msutil_pub.bom_batch_id
from
bom_cto_order_lines bcol
where bcol.line_id = pLineId
and bcol.ordered_quantity <> 0
and bcol.inventory_item_id = pModelId;
oe_debug_pub.add ('create_bom_ml: ' || 'Third -- Inserted ' || lCnt ||' rows',1);
delete from bom_inventory_comps_interface
where bill_sequence_id = xBillId ;
oe_debug_pub.add ('create_bom_ml: ' || 'deleted records from bici ::'||to_char(sql%rowcount) ,1);
select distinct b1.component_item_id item_id, b1.operation_seq_num
from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
where b1.bill_sequence_id = b2.bill_sequence_id
and b1.component_sequence_id <> b2.component_sequence_id
and b1.operation_seq_num = b2.operation_seq_num
and b1.component_item_id = b2.component_item_id
and b1.bill_sequence_id = pConfigBillId ; /* Sushant Made a change */
select effectivity_date eff_date,
nvl (disable_date,g_SchShpDate) dis_date,
component_quantity cmp_qty,
basis_type
from bom_inventory_comps_interface
where bill_sequence_id = pConfigBillId
and component_item_id = xItemId
and operation_seq_num = xOperation_seq_num; --4244576
select bic.component_sequence_id comp_seq_id
from bom_inventory_components bic,
bom_bill_of_materials bom
where bom.assembly_item_id = pConfigId
and bom.organization_id = pOrgId
and bic.bill_sequence_id = bom.bill_sequence_id
and bic.component_item_id = xComponentItemId
and bic.operation_seq_num = xOperation_seq_num; --4244576
select b1.model_comp_seq_id, b1.component_item_id, b1.operation_seq_num
BULK COLLECT INTO model_comp_seq_id_arr, component_item_id_arr, operation_seq_num_arr
from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
where b1.bill_sequence_id = b2.bill_sequence_id
and b1.component_sequence_id <> b2.component_sequence_id
and b1.operation_seq_num = b2.operation_seq_num
and b1.component_item_id = b2.component_item_id
and b1.bill_sequence_id = pConfigBillId
UNION
select b2.model_comp_seq_id, b2.component_item_id, b2.operation_seq_num
from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
where b1.bill_sequence_id = b2.bill_sequence_id
and b1.component_sequence_id <> b2.component_sequence_id
and b1.operation_seq_num = b2.operation_seq_num
and b1.component_item_id = b2.component_item_id
and b2.bill_sequence_id = pConfigBillId
ORDER by 2;
select distinct effectivity_date
BULK COLLECT INTO asc_date_arr
from bom_inventory_comps_interface
where bill_sequence_id = pConfigBillId
and component_item_id = club_comp_rec.item_id
and operation_seq_num = club_comp_rec.operation_seq_num --4244576
UNION
select distinct disable_date
from bom_inventory_comps_interface
where bill_sequence_id = pConfigBillId
and component_item_id = club_comp_rec.item_id
and operation_seq_num = club_comp_rec.operation_seq_num --4244576
order by 1;
select b1.model_comp_seq_id, b1.component_item_id
BULK COLLECT INTO
basis_model_comp_seq_id_arr, basis_component_item_id_arr
from
bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
where b1.bill_sequence_id = b2.bill_sequence_id
and b1.component_sequence_id <> b2.component_sequence_id
and b1.operation_seq_num = b2.operation_seq_num
and b1.component_item_id = b2.component_item_id
and b1.bill_sequence_id = pConfigBillId
and b1.basis_type <> b2.basis_type
and b1.effectivity_date <= club_tab_arr(x4).eff_dt
and nvl(b1.disable_date,g_SchShpDate) >= club_tab_arr(x4).dis_dt
and b1.bill_sequence_id = pConfigBillId
and b1.component_item_id = club_comp_rec.item_id
and b1.operation_seq_num = club_comp_rec.operation_seq_num
and b2.effectivity_date <= club_tab_arr(x4).eff_dt
and nvl(b2.disable_date,g_schshpdate) >= club_tab_arr(x4).dis_dt;
select segment1 into
l_model_name
from mtl_system_items
where inventory_item_id = pmodelid
and organization_id = porgid;
select segment1 into
l_comp_name
from mtl_system_items
where inventory_item_id = club_comp_rec.item_id
and organization_id = porgid;
select organization_name
into l_org_name
from inv_organization_name_v
where organization_id = porgid;
select max(rowid), sum(decode(nvl(basis_type,1), 1, component_quantity, 0))
+ max(decode(nvl(basis_type,1), 2, component_quantity, 0)) /* LBM Project */
into club_tab_arr(x4).row_id,club_tab_arr(x4).qty
from bom_inventory_comps_interface
where effectivity_date <= club_tab_arr(x4).eff_dt
and nvl(disable_date,g_SchShpDate) >= club_tab_arr(x4).dis_dt
and bill_sequence_id = pConfigBillId
and component_item_id = club_comp_rec.item_id
and operation_seq_num = club_comp_rec.operation_seq_num; --4244576
insert into bom_inventory_comps_interface
(
component_item_id,
bill_sequence_id,
effectivity_date,
disable_date,
component_quantity,
creation_date,
created_by,
last_update_date,
last_updated_by,
operation_seq_num,
last_update_login,
item_num,
component_yield_factor,
component_remarks,
change_notice,
implementation_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,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
pick_components,
model_comp_seq_id,
supply_subinventory,
supply_locator_id,
bom_item_type,
optional_on_model,
parent_bill_seq_id,
plan_level,
revised_item_sequence_id
, basis_type, /* LBM change */
batch_id
)
select
club_comp_rec.item_id,
pConfigBillId,
club_tab_arr(x6).eff_dt,
club_tab_arr(x6).dis_dt,
round(club_tab_arr(x6).qty,7), -- to maintain decimal qty support of option items
SYSDATE,
pConfigBillId, -- CREATED_BY is set to pConfigBillId to identify rows from clubbing
SYSDATE,
1,
operation_seq_num,
last_update_login,
item_num,
component_yield_factor,
component_remarks,
change_notice,
implementation_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,
bom_inventory_components_s.nextval,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
pick_components,
model_comp_seq_id,
supply_subinventory,
supply_locator_id,
bom_item_type,
optional_on_model,
parent_bill_seq_id,
plan_level,
revised_item_sequence_id
, nvl(basis_type,1), /* LBM project */
cto_msutil_pub.bom_batch_id
from bom_inventory_comps_interface
where component_item_id = club_comp_rec.item_id
and operation_seq_num = club_comp_rec.operation_seq_num --4244576
and bill_sequence_id = pConfigBillId
and rowid = club_tab_arr(x6).row_id;
-- Delete original option item rows from bici
delete from bom_inventory_comps_interface
where component_item_id = club_comp_rec.item_id
and operation_seq_num = club_comp_rec.operation_seq_num --4244576
and bill_sequence_id = pConfigBillId
and created_by <> pConfigBillId;
delete from bom_inventory_comps_interface
where component_item_id = club_comp_rec.item_id
and operation_seq_num = club_comp_rec.operation_seq_num --4244576
and bill_sequence_id = pConfigBillId
and created_by = pConfigBillId
and component_quantity = 0;
club_tab_arr.DELETE(x7);
asc_date_arr.DELETE(x8);
Update item sequence id.
To address configuration BOM restructure enhancements,
item sequence is being updated such that there are no
duplicate sequences, and in the logical order of components
selection from the parent model BOM.
The Item Sequence Increment is based on the profile
"BOM:Item Sequence Increment".
+----------------------------------------------*/
--
-- Get item sequence increment
--
p_seq_increment := fnd_profile.value('BOM:ITEM_SEQUENCE_INCREMENT');
update bom_inventory_comps_interface
set item_num = p_item_num
where bill_sequence_id = pConfigBillId and parent_bill_seq_id = 0; -- Sushant Fixed bug #3374548
oe_debug_pub.add('create_bom_data_ml: ' || 'Updated model row::'||sql%rowcount, 2);
select common_bill_sequence_id
into p_bill_seq_id
from bom_bill_of_materials
where assembly_item_id =
(select component_item_id
from bom_inventory_comps_interface
where bill_sequence_id = pConfigBillId and parent_bill_seq_id = 0) -- Sushant Fixed bug #3374548
and organization_id = pOrgId
and alternate_bom_designator is null;
oe_debug_pub.add('create_bom_data_ml: ' || 'Calling update_item_num will p_bill_seq_id::'||to_char(p_bill_seq_id)||' and p_item_num::'||to_char(p_item_num), 2);
update_item_num(
p_bill_seq_id,
p_item_num,
pOrgId,
p_seq_increment);
select organization_id, assembly_item_id , creation_date
into v_bom_organization_id, v_bom_assembly_item_id, v_bom_creation_date
from bom_bill_of_materials where bill_sequence_id = pConfigBillId ;
oe_debug_pub.add('create_bom_data_ml: ' || 'Before first insert into bill_of_materials.' ,2);
select structure_type_id into g_structure_type_id from bom_alternate_designators
where alternate_designator_code is null ;
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,
source_bill_sequence_id, /* COMMON BOM Project 12.0 */
request_id,
program_application_id,
program_id,
program_update_date,
implementation_date, -- bug fix 3759118,FP 3810243
structure_type_id, -- bugfix 4271269
effectivity_control, -- bugfix 4271269
pk1_value,
pk2_value
)
select
pConfigId, -- assembly_item_id
pOrgId, -- organization_id
NULL, -- alternate_bom_designator
sysdate, -- last_update_date
1, -- last_update_by
sysdate, -- creation date
1, -- created by
1, -- last_update_login
b.specific_assembly_comment, -- specific assembly comment /*Bugfix 2115056*/
NULL, -- pending from ecn
-- Begin Bugfix 2115056
b.attribute_category, -- attribute category
b.attribute1, -- attribute1
b.attribute2, -- attribute2
b.attribute3, -- attribute3
b.attribute4, -- attribute4
b.attribute5, -- attribute5
b.attribute6, -- attribute6
b.attribute7, -- attribute7
b.attribute8, -- attribute8
b.attribute9, -- attribute9
b.attribute10, -- attribute10
b.attribute11, -- attribute11
b.attribute12, -- attribute12
b.attribute13, -- attribute13
b.attribute14, -- attribute14
b.attribute15, -- attribute15
-- End Bugfix 2115056
b.assembly_type, -- assembly_type
pConfigBillId,
pConfigBillId,
pConfigBillId, -- source_bill_sequence_id COMMON BOM Project 12.0
NULL, -- request id
NULL, -- program_application_id
NULL, -- program id
NULL, -- program date
SYSDATE, -- implementation date bug fix 3759118,FP 3810243
g_structure_type_id, -- bugfix 4271269 structure_type_id
1, -- bugfix 4271269 effectivity_control
pconfigid,
porgid
from bom_bill_of_materials b
where b.assembly_item_id = pModelId
and b.organization_id = pOrgId
and b.alternate_bom_designator is NULL;
oe_debug_pub.add('create_bom_data_ml: ' || 'Before second insert into bom_inventory_components. ', 2);
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,
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,
common_component_sequence_id, /* COMMON BOM Project 12.0 */
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,
optional_on_model, --isp bom
parent_bill_seq_id, --isp bom
plan_level, --isp bom
model_comp_seq_id --isp bom
, basis_type /* LBM change */
)
select
b.operation_seq_num,
b.component_item_id,
b.last_update_date,
1, /* last_updated_by */
b.creation_date,
1, /* created_by */
b.last_update_login,
b.item_num,
b.component_quantity,
b.component_yield_factor,
b.component_remarks,
b.effectivity_date,
b.change_notice,
b.implementation_date,
-- 3222932 Chg g_futuredate back to NULL
decode(b.disable_date,g_futuredate,to_date(NULL), b.disable_date),
b.attribute_category,
b.attribute1,
b.attribute2,
b.attribute3,
b.attribute4,
b.attribute5,
b.attribute6,
b.attribute7,
b.attribute8,
b.attribute9,
b.attribute10,
b.attribute11,
b.attribute12,
b.attribute13,
b.attribute14,
b.attribute15,
b.planning_factor,
b.quantity_related,
b.so_basis,
b.optional,
b.mutually_exclusive_options,
b.include_in_cost_rollup,
decode( msi.bom_item_type , 1 , decode( msi.atp_flag , 'Y' , 1 , b.check_atp ) , b.check_atp ) , /* ATP changes for Model component */
b.shipping_allowed,
b.required_to_ship,
b.required_for_revenue,
b.include_on_ship_docs,
b.include_on_bill_docs,
b.low_quantity,
b.high_quantity,
b.acd_type,
b.old_component_sequence_id,
b.component_sequence_id,
b.component_sequence_id, -- common_component_sequence_id COMMON BOM Project 12.0
b.bill_sequence_id,
NULL, /* request_id */
NULL, /* program_application_id */
NULL, /* program_id */
sysdate, /* program_update_date */
b.wip_supply_type,
b.operation_lead_time_percent,
NULL, -- 2524562
b.supply_locator_id,
b.supply_subinventory,
b.pick_components,
b.bom_item_type,
b.optional_on_model, --isp bom
b.parent_bill_seq_id, --isp bom
b.plan_level, --isp bom
b.model_comp_seq_id --isp bom
, decode(b.basis_type,1,null,b.basis_type) /* LBM Change */
from bom_inventory_comps_interface b , mtl_system_items msi
where b.bill_sequence_id = pConfigBillId
and b.component_item_id = msi.inventory_item_id
and msi.organization_id = pOrgId ;
oe_debug_pub.add('create_bom_data_ml: ' || 'Before second insert into bom_inventory_components. ', 2);
insert into bom_substitute_components (
substitute_component_id
,substitute_item_quantity
,component_sequence_id
,acd_type
,change_notice
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,original_system_reference
,enforce_int_requirements
,request_id
,program_application_id
,program_id
,program_update_date
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
)
select
s.substitute_component_id -- substitute_component_id
,s.substitute_item_quantity
,b.component_sequence_id
,s.acd_type
,s.change_notice
,s.attribute_category
,s.attribute1
,s.attribute2
,s.attribute3
,s.attribute4
,s.attribute5
,s.attribute6
,s.attribute7
,s.attribute8
,s.attribute9
,s.attribute10
,s.attribute11
,s.attribute12
,s.attribute13
,s.attribute14
,s.attribute15
,s.original_system_reference
,s.enforce_int_requirements
,FND_GLOBAL.CONC_REQUEST_ID /* REQUEST_ID */
,FND_GLOBAL.PROG_APPL_ID /* PROGRAM_APPLICATION_ID */
,FND_GLOBAL.CONC_PROGRAM_ID /* PROGRAM_ID */
,sysdate /* PROGRAM_UPDATE_DATE */
,sysdate /* LAST_UPDATE_DATE */
,gUserId /* LAST_UPDATED_BY */
,sysdate /* CREATION_DATE */
,gUserId /* CREATED_BY */
,gLoginId /* LAST_UPDATE_LOGIN */
/*
,request_id
,program_application_id
,program_id
,program_update_date
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
*/
from bom_inventory_comps_interface b , bom_inventory_components bic, bom_substitute_components s
where b.bill_sequence_id = pConfigBillId
and ABS(b.model_comp_seq_id) = bic.component_sequence_id
and bic.optional = 2 /* only mandatory components */
and bic.component_sequence_id = s.component_sequence_id ;
Insert into BOM_REFERENCE_DESIGNATORS table
+--------------------------------------------------------------------------*/
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('create_bom_data_ml: ' || 'Before third insert into bom_reference_designators. ', 2);
insert into BOM_REFERENCE_DESIGNATORS
(
component_reference_designator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
ref_designator_comment,
change_notice,
component_sequence_id,
acd_type,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
select
r.component_reference_designator,
SYSDATE,
1,
SYSDATE,
1,
1,
r.REF_DESIGNATOR_COMMENT,
NULL,
ic.COMPONENT_SEQUENCE_ID,
r.ACD_TYPE,
NULL,
NULL,
NULL,
NULL,
r.ATTRIBUTE_CATEGORY,
r.ATTRIBUTE1,
r.ATTRIBUTE2,
r.ATTRIBUTE3,
r.ATTRIBUTE4,
r.ATTRIBUTE5,
r.ATTRIBUTE6,
r.ATTRIBUTE7,
r.ATTRIBUTE8,
r.ATTRIBUTE9,
r.ATTRIBUTE10,
r.ATTRIBUTE11,
r.ATTRIBUTE12,
r.ATTRIBUTE13,
r.ATTRIBUTE14,
r.ATTRIBUTE15
from
bom_inventory_components ic,
bom_reference_designators r,
bom_bill_of_materials b
where b.assembly_item_id = pConfigId
and b.organization_id = pOrgId
and ic.bill_sequence_id = b.bill_sequence_id
and r.component_sequence_id = abs(ic.model_comp_seq_id) -- previously last_update_login
and nvl(r.acd_type,0) <> 3;
select
bic.component_sequence_id into club_component_sequence_id
from
bom_inventory_components bic,
bom_bill_of_materials bom
where bom.assembly_item_id = pConfigId
and bom.organization_id = pOrgId
and bic.bill_sequence_id = bom.bill_sequence_id
and bic.component_item_id = component_item_id_arr(x1);
oe_debug_pub.add ('Trying to insert into BOM_REFERENCE_DESIGNATORS', 1);
insert into BOM_REFERENCE_DESIGNATORS
(
component_reference_designator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
ref_designator_comment,
change_notice,
component_sequence_id,
acd_type,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
select
r.component_reference_designator,
SYSDATE,
1,
SYSDATE,
1,
1,
r.REF_DESIGNATOR_COMMENT,
NULL,
club_component_sequence_id,
r.ACD_TYPE,
NULL,
NULL,
NULL,
NULL,
r.ATTRIBUTE_CATEGORY,
r.ATTRIBUTE1,
r.ATTRIBUTE2,
r.ATTRIBUTE3,
r.ATTRIBUTE4,
r.ATTRIBUTE5,
r.ATTRIBUTE6,
r.ATTRIBUTE7,
r.ATTRIBUTE8,
r.ATTRIBUTE9,
r.ATTRIBUTE10,
r.ATTRIBUTE11,
r.ATTRIBUTE12,
r.ATTRIBUTE13,
r.ATTRIBUTE14,
r.ATTRIBUTE15
from
bom_reference_designators r
--added abs() was model_comp_seq would be -ve value
where r.component_sequence_id = abs(model_comp_seq_id_arr(x1))
and nvl(r.acd_type,0) <> 3;
oe_debug_pub.add ('For this record '||sql%rowcount||' records are inserted in bom_reference_designators', 1);
Update MTL_DESCR_ELEMENT_VALUES table
+------------------------------------------------------------*/
xTableName := 'MTL_DESCR_ELEMENT_VALUES';
select ELEMENT_NAME
from mtl_descr_element_values
where inventory_item_id = pConfigId;
update MTL_DESCR_ELEMENT_VALUES i
set i.element_value = l_catalog_dtls(k).cat_element_value
where i.inventory_item_id = pConfigId
and i.element_name = l_catalog_dtls(k).cat_element_name;
update MTL_DESCR_ELEMENT_VALUES i
set i.element_value =
( select /*+ ORDERED */
NVL(max(v.element_value),i.element_value)
from
bom_bill_of_materials bi,
bom_inventory_components bc1,
bom_inventory_components bc2,
bom_dependent_desc_elements be,
mtl_descr_element_values v
where bi.assembly_item_id = pConfigId
and bi.organization_id = pOrgId
and bi.alternate_bom_Designator is null
and bc1.bill_sequence_id = bi.bill_sequence_id
and bc2.component_sequence_id = abs(bc1.model_comp_seq_id) -- previously last_update_login
and be.bill_sequence_id = bc2.bill_sequence_id
and be.element_name = i.element_name
and v.inventory_item_id = bc1.component_item_id
and v.element_name = i.element_name
)
where i.inventory_item_id = pConfigId;
update MTL_DESCR_ELEMENT_VALUES i
set i.element_value =
( select /*+ ORDERED */
NVL(max(v.element_value),i.element_value)
from
bom_bill_of_materials bi,
bom_inventory_components bc1,
bom_inventory_components bc2,
bom_dependent_desc_elements be,
mtl_descr_element_values v
where bi.assembly_item_id = pConfigId
and bi.organization_id = pOrgId
and bi.alternate_bom_Designator is null
and bc1.bill_sequence_id = bi.bill_sequence_id
and bc2.component_sequence_id = abs(bc1.model_comp_seq_id) -- previously last_update_login
and be.bill_sequence_id = bc2.bill_sequence_id
and be.element_name = i.element_name
and v.inventory_item_id = bc1.component_item_id
and v.element_name = i.element_name
-- bugfix 2590966
-- Following code eliminates lower level configurations
-- FP Bug Fix 4761813
-- Tuned the query to user not exists for perfomance reason
and not exists
(
SELECT 'x' FROM MTL_SYSTEM_ITEMS
WHERE ORGANIZATION_ID = pOrgId
AND BC1.COMPONENT_ITEM_ID = INVENTORY_ITEM_ID
AND BASE_ITEM_ID IS NOT NULL
AND BOM_ITEM_TYPE = 4
AND REPLENISH_TO_ORDER_FLAG = 'Y'
)
-- end bugfix 2590966
)
where i.inventory_item_id = pConfigId;
Update descriptions of the config items in
the MTL_SYSTEM_ITEMS
+----------------------------------------------------------------------*/
lStmtNum := 350;
status := bmlupid_update_item_desc(pConfigid,
pOrgId,
xErrorMessage);
oe_debug_pub.add('create_bom_data_ml: ' || 'bmlupid_update_item_desc returned ' || status,1 );
select common_bill_sequence_id
into l_from_sequence_id
from bom_bill_of_materials
where assembly_item_id = pModelId
and organization_id = pOrgId
and alternate_bom_designator is NULL;
X_last_update_login => '',
X_program_application_id=> '',
X_program_id => '',
X_request_id => ''
);
delete from bom_inventory_comps_interface
where bill_sequence_id = pConfigBillId;
delete from bom_inventory_comps_interface
where bill_sequence_id = pConfigBillId;
delete from bom_inventory_comps_interface
where bill_sequence_id = pConfigBillId;
delete from bom_inventory_comps_interface
where bill_sequence_id = pConfigBillId;
delete from bom_inventory_comps_interface
where bill_sequence_id = pConfigBillId;
This procedure is called in a loop to update the
Item Sequence Number on the components of the configuration
BOM such that there are no duplicates, and the logical order
in which they are selected from the model BOM is maintained.
+------------------------------------------------*/
PROCEDURE update_item_num(
p_parent_bill_seq_id IN NUMBER,
p_item_num IN OUT NOCOPY NUMBER, /* NOCOPY Project */
p_org_id IN NUMBER,
p_seq_increment IN NUMBER)
IS
CURSOR c_update_item_num(p_parent_bill_seq_id number) IS
select component_sequence_id,
component_item_id
from bom_inventory_comps_interface
where parent_bill_seq_id = p_parent_bill_seq_id
FOR UPDATE OF item_num;
FOR v_update_item_num IN c_update_item_num(p_parent_bill_seq_id)
LOOP
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('update_item_num: ' || 'In update loop for item '||to_char(v_update_item_num.component_item_id), 2);
-- update item_num of child of this model
--
update bom_inventory_comps_interface
set item_num = p_item_num
where current of c_update_item_num;
oe_debug_pub.add('update_item_num: ' || 'Updated item '||to_char(v_update_item_num.component_item_id)|| ' with item num '||to_char(p_item_num), 2);
select common_bill_sequence_id
into p_bill_seq_id
from bom_bill_of_materials
where assembly_item_id = v_update_item_num.component_item_id
and organization_id = p_org_id
and alternate_bom_designator is null;
oe_debug_pub.add('update_item_num: ' || 'Calling update_item_num will p_bill_seq_id::'||to_char(p_bill_seq_id)||' and p_item_num::'||to_char(p_item_num), 2);
update_item_num(
p_bill_seq_id,
p_item_num,
p_org_id,
p_seq_increment);
oe_debug_pub.add('update_item_num: ' || 'This component '||to_char(v_update_item_num.component_item_id)||' does not have a BOM', 2);
END update_item_num;
select bbm.organization_id,
nvl(bic.operation_seq_num,1) operation_seq_num , -- 2433862
nvl(bet.operation_seq_num,1) parent_op_seq_num, -- 2433862
bic.component_item_id,
bic.item_num,
decode(nvl(bic.basis_type,1),1,bic.component_quantity * (bcol1.ordered_quantity / bcol2.ordered_quantity ),bic.component_quantity) component_qty,
bic.component_yield_factor,
bic.component_remarks, --Bugfix 7188428
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.so_basis,
bic.include_in_cost_rollup,
bic.check_atp,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
bic.wip_supply_type,
bic.component_sequence_id, -- model comp seq for later use
bic.supply_subinventory,
bic.supply_locator_id,
bic.bom_item_type,
bic.bill_sequence_id, -- parent_bill_seq_id
bcol1.plan_level+1 plan_level,
decode( -- 3222932
greatest(bic.effectivity_date,sysdate),
bic.effectivity_date ,
bic.effectivity_date ,
sysdate ) eff_date,
nvl(bic.disable_date,g_futuredate) dis_date -- 3222932
, nvl(bic.basis_type,1) basis_type /* LBM project */
from bom_cto_order_lines bcol1, -- COMPONENT
bom_cto_order_lines bcol2, -- MODEL
mtl_system_items si1,
mtl_system_items si2,
bom_bill_of_materials bbm,
bom_inventory_components bic, -- Components
bom_inventory_components bic1, -- Parent
bom_explosion_temp bet
/*-----------------------------------------------------------------------------------------------------+
For a multilevel model , ato_line_id=xLineId will not fetch included items of lower level
non-phantom models so Parent_ATO_Line_id is used in the join condition.
e.g. For a bill like this :
MODEL1
..OC1
...MODEL2 ( Phantom Model )
....OC3
.....MAND2
..OC2
...MODEL3 ( Non Phantom Model )
....OC4
.....MAND2
Line id data in BCOL is as under :
ITEM LINE_ID LNK_TO_LINE_ID PRNT_ATO_LINE_ID ATO_LINE_ID
---------- ------- -------------- ---------------- -----------
MODEL1 1 1 1
..OC1 2 1 1 1
...MODEL2 3 2 1 1
....OC3 4 3 1 1
..OC2 5 1 1 1
...MODEL3 6 5 1 1
....OC4 7 6 6 1
FOR join condition ato_line_id = xLine_id , MAND2 under OC4 will not be picked up while
configuring MODEL3. So parent_atoline_id = xLine_id is used.
+------------------------------------------------------------------------------------------------------------*/
where bcol1.parent_ato_line_id = xLineId
and bcol1.component_code = bet.component_code
and si1.organization_id = xOrgId
and bcol1.inventory_item_id = si1.inventory_item_id
and si1.bom_item_type in (1,2) -- model, option class
and si2.inventory_item_id = bcol2.inventory_item_id
and si2.organization_id = si1.organization_id
and si2.bom_item_type = 1
-- Bugfix 2389283 : Commented bcol1.line_id = bcol2.line_id condition
and (bcol1.parent_ato_line_id = bcol2.line_id
and ( bcol1.bom_item_type <> 1
or ( bcol1.bom_item_type = 1
and nvl(bcol1.wip_supply_type, 0) = 6
)
)
)
-- or bcol1.line_id = bcol2.line_id )
and bet.bill_sequence_id = xConfigBillId
and bet.top_bill_sequence_id = xConfigBillId
and bic1.component_sequence_id = bcol1.component_sequence_id
and bic1.bom_item_type in (1,2)
and bbm.assembly_item_id = bic1.component_item_id
and bbm.organization_id = si1.organization_id
and bbm.alternate_bom_designator is NULL
and bic.bill_sequence_id = DECODE(bbm.common_bill_sequence_id,bbm.bill_sequence_id,bbm.bill_sequence_id,bbm.common_bill_sequence_id)
and bic.optional = 2
and bic.bom_item_type = 4
-- and bic.effectivity_date <= greatest( NVL(xSchShpdt,sysdate),sysdate) /* New Approach for effectivity dates */
and bic.implementation_date is not null
-- and NVL(bic.disable_date,NVL(xEstReldt, SYSDATE)+1) > NVL(xEstReldt,SYSDATE) /* NEw Approach for effectivity dates*/
-- and NVL(bic.disable_date,SYSDATE) >= SYSDATE; /* New approach for effectivity dates */
SELECT /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
nvl(operation_seq_num,1) operation_seq_num, -- 2433862
component_code,
rowid
from bom_explosion_temp
where bill_sequence_id = xConfigBillId
and component_code IS NOT NULL
ORDER BY component_code;
-- Insert Option Classes and Option Items
-- Compare to last insert , here we have an addl column
-- component_code to insert comp_code of classes /items
-- from bcol
--
INSERT INTO BOM_EXPLOSION_TEMP
( top_bill_sequence_id,
organization_id,
plan_level,
sort_order,
operation_seq_num,
component_item_id,
item_num,
component_quantity,
component_yield_factor,
component_remarks, --Bugfix 7188428
context, -- mapped to attribute_category in bic interface
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
select_quantity, -- mapped to quantity_related of bic interface
so_basis,
optional, -- mapped to optional_on_model of bic interface
mutually_exclusive_options,
include_in_rollup_flag, -- mapped to include_in_cost rollup of bic interface
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
component_sequence_id,
bill_sequence_id,
wip_supply_type,
pick_components,
base_item_id, -- mapped to model_comp_seq_id of bic_interface
supply_subinventory,
supply_locator_id,
bom_item_type,
component_code, -- Additional
line_id, -- 2814257
top_item_id,
effectivity_date, -- 3222932
disable_date -- 3222932-- mapped to parent_bill_seq_id of bic interface
, basis_type /* LBM project */
,assembly_item_id /* Bug Fix: 4147224 */
)
select pConfigBillId, -- top bill sequence id
bcol2.ship_from_org_id, -- Model's organization_id
(bcol1.plan_level-bcol2.plan_level), -- Plan Level
'1', -- Sort Order
nvl(ic1.operation_seq_num,1),
decode(bcol1.config_item_id, NULL, ic1.component_item_id,bcol1.config_item_id),
ic1.item_num,
Round(
CTO_UTILITY_PK.convert_uom( bcol1.order_quantity_uom, msi_child.primary_uom_code, bcol1.ordered_quantity , msi_child.inventory_item_id )
/ CTO_UTILITY_PK.convert_uom(bcol2.order_quantity_uom, msi_parent.primary_uom_code, NVL(bcol2.ordered_quantity,1) , msi_parent.inventory_item_id )
, 7) , -- qty = comp_qty / model_qty /* Decimal-Qty Support for Option Items */
ic1.component_yield_factor,
ic1.component_remarks, --Bugfix 7188428
ic1.attribute_category,
ic1.attribute1,
ic1.attribute2,
ic1.attribute3,
ic1.attribute4,
ic1.attribute5,
ic1.attribute6,
ic1.attribute7,
ic1.attribute8,
ic1.attribute9,
ic1.attribute10,
ic1.attribute11,
ic1.attribute12,
ic1.attribute13,
ic1.attribute14,
ic1.attribute15,
100, -- planning_factor
2, -- quantity_related
decode(bcol1.config_item_id, NULL,
decode(ic1.bom_item_type,4,ic1.so_basis,2),2), -- so_basis
1, -- optional
2, -- mutually_exclusive_options
decode(bcol1.config_item_id, NULL,
decode(ic1.bom_item_type,4,
ic1.include_in_cost_rollup, 2),1), -- Cost_rollup
decode(bcol1.config_item_id, NULL,
decode(ic1.bom_item_type,4,
ic1.check_atp, 2),2), -- check_atp
2, -- shipping_allowed = NO
2, -- required_to_ship = NO
ic1.required_for_revenue,
ic1.include_on_ship_docs,
ic1.include_on_bill_docs,
bom_inventory_components_s.nextval, -- component sequence id
pConfigBillId, -- bill sequence id
ic1.wip_supply_type,
2, -- pick_components = NO
decode(bcol1.config_item_id, NULL, (-1)*ic1.component_sequence_id, ic1.component_sequence_id), -- saved model comp seq for later use. If config item, then save model comp seq id as positive, otherwise negative.
ic1.supply_subinventory,
ic1.supply_locator_id,
decode(bcol1.config_item_id, NULL, ic1.bom_item_type, 4),
bcol1.component_code,
bcol1.line_id, -- 2814257
ic1.bill_sequence_id,
decode( -- 3222932
greatest(ic1.effectivity_date,sysdate),
ic1.effectivity_date ,
ic1.effectivity_date ,
sysdate ),
nvl(ic1.disable_date,g_futuredate) -- 3222932
, nvl(ic1.basis_type,1) /* LBM project */
,bcol3.inventory_item_id /* Bug Fix : 4147224 */
from bom_inventory_components ic1,
bom_cto_order_lines bcol1, -- Option
bom_cto_order_lines bcol2, -- Parent-Model
bom_cto_order_lines bcol3 , -- Parent-component
mtl_system_items msi_child,
mtl_system_items msi_parent
where ic1.bill_sequence_id = (
select common_bill_sequence_id
from bom_bill_of_materials bbm
where organization_id = pOrgId
and alternate_bom_designator is null
and assembly_item_id =(
select distinct assembly_item_id
from bom_bill_of_materials bbm1,
bom_inventory_components bic1
where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
and component_sequence_id = bcol1.component_sequence_id
and bbm1.assembly_item_id = bcol3.inventory_item_id ))
and ic1.component_item_id = bcol1.inventory_item_id
and msi_child.inventory_item_id = bcol1.inventory_item_id
and msi_child.organization_id = pOrgId
and msi_parent.inventory_item_id = bcol2.inventory_item_id
and msi_parent.organization_id = pOrgId
-- and ic1.effectivity_date <= g_SchShpDate /* New Approach for effectivity dates */
and ic1.implementation_date is not null --bug 4244147
-- and NVL(ic1.disable_date, (g_EstRelDate + 1)) >= greatest( nvl( g_EstRelDate, sysdate) , sysdate) /* bug 3389846 */
/*
and ( ic1.disable_date is null or
(ic1.disable_date is not null and ic1.disable_date >= greatest( nvl( g_EstRelDate, sysdate ) , sysdate )) #3389846
)
*/
and ( ic1.disable_date is null or
(ic1.disable_date is not null and ic1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
and (( ic1.optional = 1 and ic1.bom_item_type = 4)
or
( ic1.bom_item_type in (1,2)))
and bcol1.ordered_quantity <> 0
and bcol1.line_id <> bcol2.line_id -- not the top ato model
and bcol1.parent_ato_line_id = bcol2.line_id
and bcol1.parent_ato_line_id is not null
and bcol1.link_to_line_id is not null
and bcol2.line_id = pLineId
and bcol2.ship_from_org_id = bcol1.ship_from_org_id
and (bcol3.parent_ato_line_id = bcol1.parent_ato_line_id
or
bcol3.line_id = bcol1.parent_ato_line_id)
and bcol3.line_id = bcol1.link_to_line_id;
oe_debug_pub.add ('inherit_op_seq_ml: ' || 'Second -- Inserted in BE Temp ' || lCnt ||' Option item/Option class rows with bill seq id as '|| pConfigBillId,1);
select /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
count(*) into v_zero_qty_count from bom_explosion_temp
where bill_sequence_id = pConfigBillId and component_quantity = 0 ;
select concatenated_segments into v_model_item_name
from mtl_system_items_kfv
where inventory_item_id = pModelId
and rownum = 1 ;
update bom_explosion_temp set disable_date = g_futuredate
where ( component_item_id , operation_seq_num, nvl(assembly_item_id,-1) , disable_date) in
( select component_item_id, operation_seq_num, nvl(assembly_item_id,-1), max(disable_date)
from bom_inventory_comps_interface
where bill_sequence_id = pConfigBillId
group by component_item_id, operation_seq_num, assembly_item_id)
and disable_date <> g_futuredate ;
/* moved Mandatory comps code to insert components after ordered items */
lStmtNumber := 510;
/*Insert Incl. items under Base Model */
INSERT INTO bom_explosion_temp
(
top_bill_sequence_id,
organization_id,
plan_level,
sort_order,
operation_seq_num,
component_item_id,
item_num,
component_quantity,
component_yield_factor,
component_remarks, --Bugfix 7188428
context, -- mapped to attribute_category in bic interface
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
select_quantity, -- mapped to quantity_related of bic interface
so_basis,
optional, -- mapped to optional_on_model in bic interface
mutually_exclusive_options,
include_in_rollup_flag, -- mapped to include_in_cost rollup of bic interface
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
component_sequence_id,
bill_sequence_id,
wip_supply_type,
pick_components,
base_item_id, -- mapped to model_comp_seq_id of bic_interface
supply_subinventory,
supply_locator_id,
bom_item_type,
top_item_id,
effectivity_date, -- 3222932
disable_date -- 3222932-- mapped to parent_bill_seq_id in bic interface
, basis_type /* LBM project */
)
select pConfigBillId, -- top bill sequence id
bbm.organization_id, -- Model's organization_id
1, -- Plan Level, should be 0+1 for model's smc's
'1', -- Sort Order
nvl(bic.operation_seq_num,1),
bic.component_item_id,
bic.item_num,
bic.component_quantity component_qty,
/*
please check whether this change is rquired
decode( nvl(bic.basis_type,1), 1 , Round( ( bic.component_quantity * ( bcol1.ordered_quantity
/ bcol2.ordered_quantity)), 7 ) , Round(bic.component_quantity , 7 ) ) , * Decimal-Qty Support for Option Items, LBM project
*/
bic.component_yield_factor,
bic.component_remarks, --Bugfix 7188428
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,
100, -- planning_factor
2, -- quantity_related
bic.so_basis,
2, -- optional
2, -- mutually_exclusive_options
bic.include_in_cost_rollup,
bic.check_atp,
2, -- shipping_allowed = NO
2, -- required_to_ship = NO
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
bom_inventory_components_s.nextval, -- component sequence id
pConfigBillId, -- bill sequence id
bic.wip_supply_type,
2, -- pick_components = NO
(-1)*bic.component_sequence_id, -- model comp seq for later use
bic.supply_subinventory,
bic.supply_locator_id,
bic.bom_item_type,
bic.bill_sequence_id,
decode( -- 3222932
greatest(bic.effectivity_date,sysdate),
bic.effectivity_date ,
bic.effectivity_date ,
sysdate ),
nvl(bic.disable_date,g_futuredate) -- 3222932
, nvl(bic.basis_type,1) /* LBM project */
from bom_cto_order_lines bcol,
bom_bill_of_materials bbm,
bom_inventory_components bic
where bcol.line_id = pLineId
and bcol.ordered_quantity <> 0
-- bugfix 2389283 and instr(bcol.component_code,'-',1,1) = 0 /* To identify Top Model */
and bcol.inventory_item_id = pModelId
and bbm.organization_id = pOrgId
and bcol.inventory_item_id = bbm.assembly_item_id
and bbm.alternate_bom_designator is NULL
and bbm.common_bill_sequence_id = bic.bill_sequence_id
and bic.optional = 2
and bic.bom_item_type = 4
-- and bic.effectivity_date <= greatest( NVL(g_SchShpDate,sysdate),sysdate) /* New Approach for effectivity dates */
and bic.implementation_date is not null
/*
and NVL(bic.disable_date,NVL(g_EstRelDate, SYSDATE)+1) > NVL(g_EstRelDate,SYSDATE) NEW approach for effectivity dates
and NVL(bic.disable_date,SYSDATE) >= SYSDATE; New approach for effectivity dates
oe_debug_pub.add ('inherit_op_seq_ml: ' || 'First -- Inserted in BE Temp ' || lCnt ||' Incl Item rows with bill seq id as '|| pConfigBillId,1);
Open cursor c_model_oc_oi_rows(xConfigBillId) for rows inserted in bet
This will update all Option Class and Option Item rows
Mandatory items directly under model will already have op_seq_num. For these mandatory items we don't need to
inherit the op_seq_num since they are directly under model.
The component_code for these mand items are NULL as they are not in BCOL.
so , mandatory item rows from bet will not be selected by c_model_oc_oi_rows cursor and will not be updated
Explanation :
For a Bill structure like this :
55631 1.1.0 KS-ATO-MODEL1*6389
55627 1.1 KS-ATO-MODEL1
55628 1.1.1 KS-ATO-MODEL3
55629 1.1.2 KS-ATO-OC1
55630 1.1.3 KS-ATO-OI1
BCOL.LINE_ID BCOL.COMP_SEQ_ID BCOL.COMPONENT_CODE
---------- ---------------- ---------------
55627 21053 6280
55628 21322 6280-6376
55629 21303 6280-6376-6282
55630 21035 6280-6376-6282-6288
Now , instr( bet.component_code,'-',1,2 ) will select line_id 55629 and 55630 as those rows are actual candidates for
op_seq_num update. 55627 was not inserted in bet as it is the base model row and we are not selecting 55628 since this
is directly under the top model and inheritence logic does not apply to this line.
Inheritence starts from second level . First level components under top model will always have op_seq_num.
+------------------------------------------------------------------------------------------------------------+*/
FOR r1 in c_model_oc_oi_rows(pConfigBillId) LOOP
IF r1.operation_seq_num = 1 AND instr(r1.component_code,'-',1,2)<>0 THEN
UPDATE bom_explosion_temp bet
SET bet.operation_seq_num = (
SELECT /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
nvl(operation_seq_num,1) -- 2433862
FROM bom_explosion_temp
WHERE component_code = substr(bet.component_code,1,to_number(instr(bet.component_code,'-',-1,1))-1)
AND bill_sequence_id = pConfigBillId
AND top_bill_sequence_id = pConfigBillId)
WHERE component_code = r1.component_code
AND rowid = r1.rowid;
INSERT INTO bom_explosion_temp
( top_bill_sequence_id,
organization_id,
plan_level,
sort_order,
operation_seq_num,
component_item_id,
item_num,
component_quantity,
component_yield_factor,
component_remarks, --Bugfix 7188428
context, -- mapped to attribute_category in bic interface
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
select_quantity, -- mapped to quantity_related of bic interface
so_basis,
optional, -- mapped to optional_on_model of bic interface
mutually_exclusive_options,
include_in_rollup_flag, -- mapped to include_in_cost rollup of bic interface
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
component_sequence_id,
bill_sequence_id,
wip_supply_type,
pick_components,
base_item_id, -- mapped to model_comp_seq_id of bic_interface
supply_subinventory,
supply_locator_id,
bom_item_type,
top_item_id, -- mapped to parent_bill_seq_id of bic interface
effectivity_date, -- 3222932
disable_date -- 3222932
, basis_type /* LBM project */
)
VALUES
( pConfigBillId, -- top bill sequence id
r2.organization_id, -- Model's organization_id
r2.plan_level, -- Plan Level
'1', -- Sort Order
DECODE(r2.operation_seq_num,1,r2.parent_op_seq_num,r2.operation_seq_num),
r2.component_item_id,
r2.item_num,
r2.component_qty,
r2.component_yield_factor,
r2.component_remarks, --Bugfix 7188428
r2.attribute_category,
r2.attribute1,
r2.attribute2,
r2.attribute3,
r2.attribute4,
r2.attribute5,
r2.attribute6,
r2.attribute7,
r2.attribute8,
r2.attribute9,
r2.attribute10,
r2.attribute11,
r2.attribute12,
r2.attribute13,
r2.attribute14,
r2.attribute15,
100, -- planning_factor
2, -- quantity_related
r2.so_basis,
2, -- optional
2, -- mutually_exclusive_options
r2.include_in_cost_rollup,
r2.check_atp,
2, -- shipping_allowed = NO
2, -- required_to_ship = NO
r2.required_for_revenue,
r2.include_on_ship_docs,
r2.include_on_bill_docs,
bom_inventory_components_s.nextval, -- component sequence id
pConfigBillId, -- bill sequence id
r2.wip_supply_type,
2, -- pick_components = NO
(-1)*r2.component_sequence_id, -- model comp seq for later use
r2.supply_subinventory,
r2.supply_locator_id,
r2.bom_item_type,
r2.bill_sequence_id, -- parent_bill_seq_id
r2.eff_date, -- 3222932
r2.dis_date -- 3222932
, r2.basis_type /* LBM project */
);
oe_debug_pub.add ('inherit_op_seq_ml: ' || 'INSIDE Loop : Inserted in BE Temp ' || lCnt ||' manadatory item rows with bill seq id as '|| pConfigBillId,1);
/*Insert into bic interface*/
insert into BOM_INVENTORY_COMPS_INTERFACE
( operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
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,
pick_components,
model_comp_seq_id,
supply_subinventory,
supply_locator_id,
bom_item_type,
revised_item_sequence_id, -- 2814257
optional_on_model,
plan_level,
parent_bill_seq_id,
assembly_item_id /* Bug Fix: 4147224 */
, basis_type, /* LBM changes */
batch_id
)
select /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
nvl(operation_seq_num,1), -- 2433862
component_item_id,
SYSDATE, -- last_updated_date
1, -- last_updated_by
SYSDATE, -- creation_date
1, -- created_by
1, -- last_update_login
item_num,
component_quantity,
component_yield_factor,
component_remarks, --Bugfix 7188428
--NULL, -- component_remark
-- 3222932 TRUNC(SYSDATE), -- effective date
effectivity_date,
NULL, -- change notice
SYSDATE, -- implementation_date
-- 3222932 NULL, -- disable date
disable_date,
context, -- mapped to attribute_category in bic interface
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
select_quantity, -- mapped to quantity_related of bic interface
so_basis,
2, -- optional
mutually_exclusive_options,
include_in_rollup_flag, -- mapped to include_in_cost rollup of bic interface
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
NULL, -- low_quantity
NULL, -- high_quantity
NULL, -- acd_type
NULL, -- old_component_sequence_id
component_sequence_id,
bill_sequence_id,
NULL, -- request_id
NULL, -- program_application_id
NULL, -- program_id
NULL, -- program_update_date
wip_supply_type,
pick_components,
base_item_id, -- mapped to model_comp_seq_id of bic_interface
supply_subinventory,
supply_locator_id,
bom_item_type,
line_id, -- 2814257
optional,
plan_level,
top_item_id,
assembly_item_id /* Bug Fix: 4147224 */
, nvl(basis_type,1), /* LBM project */
cto_msutil_pub.bom_batch_id
from bom_explosion_temp
where bill_sequence_id = pConfigBillId;
oe_debug_pub.add ('inherit_op_seq_ml: ' || 'Final - Inserted in BIC Interface ' || lCnt ||' rows from BET',1);
select 1 into v_overlap_check
from dual
where exists
( select * from bom_inventory_comps_interface
where bill_sequence_id = pConfigBillId
group by component_item_id, assembly_item_id
having count(distinct operation_seq_num) > 1
);
select s1.component_item_id,
s1.operation_seq_num, s1.effectivity_date, s1.disable_date,
s2.operation_Seq_num , s2.effectivity_date, s2.disable_date
BULK COLLECT INTO
v_t_overlap_comp_item_id,
v_t_overlap_src_op_seq_num, v_t_overlap_src_eff_date, v_t_overlap_src_disable_date ,
v_t_overlap_dest_op_seq_num , v_t_overlap_dest_eff_date, v_t_overlap_dest_disable_date
from bom_inventory_comps_interface s1 , bom_inventory_comps_interface s2
where s1.component_item_id = s2.component_item_id and s1.assembly_item_id = s2.assembly_item_id
--and s1.effectivity_date between s2.effectivity_date and s2.disable_date
and s1.effectivity_date > s2.effectivity_date --Bugfix 6603382
and s1.effectivity_date < s2.disable_date --Bugfix 6603382
and s1.bill_sequence_id = pConfigBillId --Bugfix 6603382
and s2.bill_sequence_id = pConfigBillId --Bugfix 6603382
and s1.component_sequence_id <> s2.component_sequence_id ;
select segment1
into
l_model_name
from mtl_system_items
where inventory_item_id=pModelId
and rownum=1;
DELETE /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
from bom_explosion_temp
WHERE bill_sequence_id = pConfigBillId;
select bill_sequence_id
into xBillId
from bom_bill_of_materials
where assembly_item_id = pItemId
and organization_id = pOrgId
and alternate_bom_designator is null;
select (ceil(nvl(msi.fixed_lead_time,0)
+ nvl(msi.variable_lead_time,0) * pQty))
into pLeadTime
from mtl_system_items msi
where inventory_item_id = pModelId
and organization_id = pOrgId;
select nvl(substr(profile_option_value,1,30),'N')
from fnd_profile_option_values val,fnd_profile_options op
where op.application_id = 401
and op.profile_option_name = 'USE_NAME_ICG_DESC'
and val.level_id = 10001 /* This is for site level */
and val.application_id = op.application_id
and val.profile_option_id = op.profile_option_id;
/* Let us select the catalog group name from mtl_catalog_groups
** At some point in time we need to call the inventory function
** to do this, so we can centralize this stuff
*/
lStmtNum :=260;
SELECT MICGK.concatenated_segments
INTO group_name
FROM mtl_item_catalog_groups_kfv MICGK
WHERE MICGK.item_catalog_group_id = group_id;
SELECT MICG.description
INTO group_name
FROM mtl_item_catalog_groups MICG
WHERE MICG.item_catalog_group_id = group_id;
Name : bmlupid_update_item_desc
+------------------------------------------------------------------*/
function bmlupid_update_item_desc
(
item_id NUMBER,
org_id NUMBER,
err_buf out NOCOPY VARCHAR2
)
return integer
is
/*
** Create cursor to retrieve all descriptive element values for the item
*/
CURSOR cc is
select element_value
from mtl_descr_element_values
where inventory_item_id = item_id
and element_value is not NULL
and default_element_flag = 'Y'
order by element_sequence;
oe_debug_pub.add('bmlupid_update_item_desc: ' || ' In bmlupid_update_item_desc ',2);
oe_debug_pub.add('bmlupid_update_item_desc: ' || ' item id ' || item_id ,2);
oe_debug_pub.add('bmlupid_update_item_desc: ' || ' org id ' || org_id ,2);
select concatenated_segment_delimiter into delimiter
from fnd_id_flex_structures
where id_flex_code = 'MICG'
and application_id = 401;
select item_catalog_group_id into group_id
from mtl_system_items
where inventory_item_id = item_id
and organization_id = org_id;
oe_debug_pub.add('bmlupid_update_item_desc: ' || ' item_catalog_group_id : ' || group_id,2);
oe_debug_pub.add('bmlupid_update_item_desc: ' || 'cat_value :' || cat_value,1);
update mtl_system_items
set description = cat_value
where inventory_item_id = item_id;
update mtl_system_items_tl
set description = cat_value
where inventory_item_id = item_id;
oe_debug_pub.add ('bmlupid_update_item_desc: ' || err_buf, 1);
oe_debug_pub.add ('bmlupid_update_item_desc: ' || err_buf, 1);
END bmlupid_update_item_desc;
g_t_dropped_item_type.delete ;