[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| DESCRIPTION : CZ_BOM_CONFIG_EXPLOSIONS_PKG, will insert mandatory
| and optional components of a selected configuration
| into BOM Tables.
| PARAMETERS
|
| NOTES
|
| MODIFIED (MM/DD/YY)
|
| 09/03/99 Rahul Chitko Initial Creation
| 12/22/99 Rahul Chitko The BOM_INS_MODEL_Mandatory procedure is
| changed to insert Mandatory items and
| also the option items chosen for a
| configuration. This procedure was
| modified to also inherit the op-seq's
| based on a profile option.
|
| Modified on 24-AUG-2001 by Sushant Sawant: BUG #1957336
| Added a new functionality for preconfi
gure bom.
| Modified on 15-OCT-2001 by Sushant Sawant: BUG#2048023
| Fixed matched_item_id variable in
| perform_match procedure.
|
| Modified on 18-OCT-2001 by Sushant Sawant: BUG#2048023
| Fixed perform_match logic
| loop is continued for all models
|
|
| Modified on 19-NOV-2001 By Renga Kannan : Modified the sequence
| add a new sequence called
| BOM_CTO_ORDER_LINES_S1
| this sequence generates -ve nos
| This is because now, BCOL table
| is used for pre-config bom functionality
| We don't want any +ve seq, so that it won't
| converge with line_id field. There is a dependecy
| with bmobmsc.odf version 115.37
|
| Modified on 10-APR-2002 By Sushant Sawant: Fixed BUG 2310435 and BUG 2312199
| BUG 2310435 CUSTOMER BUG is similar to 2312199 INTERNAL BUG
| The process_configurator_data code was dependent on the
| parent_config_item_id being populated in the
| cz_config_details_v table. This dependency has been
| removed and the code has been changed to properly
| identify the top model and the relationships to the
| appropriate components.This bug was identified as a
| common bom issue at agilent, however it is generic
| in nature as CZ has started populating this field
| randomly.
|
| Modified on 23-APR-2002 By Sushant Sawant:
| schedule_Ship_date populated as sysdate
| instead of trunc(sysdate). This was causing
| issues related to bom and routings if they
| were created on the same day.
|
| Modified on 15-MAY-2002 By Sushant Sawant:
| Fixed bug 2372939
| Error Message not propagated properly
| from other cto routines to front end.
|
|
| Modified on 17-JUL-2002 By Kiran Konada
| Fixed bug 2457660
| changed the cursor C1 to pick quantity from CZ table rather than BIC
| changed the debug message to print p_cz_config_hdr_id
|
| Modified on 09-SEP-2002 By Kundan Sarkar:
| Fixed bug 2550121 ( Customer bug 2394597 )
| Preconfiguration fails when pre-config item is created
| without any catalog group id but its base model has an
| item catalog attached to it.
|
|
| Modified on 21-APR-2004 By Sushant Sawant
| Front Port for bug 3262450
| Instead of hard coding UOM , we need to get base model's
| UOM for pre-config item.
|
|
|
|
| Modified on 21-APR-2004 By Sushant Sawant
| Fixed bug 3285539. The Front Port bug 3262450 has been revisited.
|
|************************************************************************
|Following changes were pulled in while overloading old BOM_INS_MODEL_AND_MANDAT
ORY for backward compatibility.
|
|
| 03/12/02 Refai Farook Changes to the operation sequence number
inheritance.
| Inheritance should occur from the near p
arent which has valid op.seq
| 03/26/02 Refai Farook Operation sequence number inheritance lo
gic has been changed
| 03/27/02 Refai Farook Club component quantitites will be using
rowid to identify the unique
| row from bom_explosion_temp
|
|************************************************************************
|
|
|
| Modified on 26-DEC-2002 by Sushant Sawant: BUG #2726217
| Replicated Overloading Changes to main
|
| Modified on 28-JAN-2003 by Sushant Sawant: BUG #2756186
| Added additional out parameter
| x_routing_exists to create_preconfig_item_ml
| to indicate whether routing already
| exists for the preconfigured item.
|
| Modified on 14-MAR-2003 By Sushant Sawant
| Decimal-Qty Support for Option Items.
+=======================================================================
*/
TYPE bcol_tbl_type is table of bom_cto_order_lines%rowtype INDEX by BINARY_INTEGER ;
PROCEDURE INSERT_INTO_BCOL (
p_bcol_tab bcol_tbl_type
);
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
x_bill_sequence_id NUMBER := NULL,
X_Top_Bill_Sequence_Id NUMBER,
X_Organization_Id NUMBER,
X_Group_Id NUMBER,
X_Effectivity_Date DATE,
X_Sort_Order VARCHAR2,
X_Select_Flag VARCHAR2,
X_Select_Quantity NUMBER,
X_Session_Id NUMBER,
X_Context VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM BOM_CONFIG_EXPLOSIONS
WHERE group_id = X_Group_Id
AND sort_order = X_Sort_Order;
-- values to this procedure and only the Option Classes and the option selected from the
-- option classes will be inserted into BOM_INVENTORY_COMPONENTS when this statement
-- executes.
--
INSERT INTO BOM_INVENTORY_COMPONENTS
(
bill_sequence_id,
component_sequence_id,
component_item_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
component_quantity,
component_yield_factor,
planning_factor,
quantity_related,
include_in_cost_rollup,
so_basis,
optional,
mutually_exclusive_options,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
pick_components,
bom_item_type,
operation_seq_num,
item_num,
effectivity_date,
disable_date,
implementation_date,
wip_supply_type
)
SELECT
x_bill_sequence_id,
BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
be.component_item_id,
be.creation_date,
be.created_by,
be.last_update_date,
be.last_updated_by,
be.attribute1,
be.attribute2,
be.attribute3,
be.attribute4,
be.attribute5,
be.attribute6,
be.attribute7,
be.attribute8,
be.attribute9,
be.attribute10,
be.attribute11,
be.attribute12,
be.attribute13,
be.attribute14,
be.attribute15,
round( be.component_quantity,7 ), /* Support Decimal Qty for Option Items */
bic.component_yield_factor,
bic.planning_factor,
bic.quantity_related,
bic.include_in_cost_rollup,
be.so_basis,
be.optional,
be.mutually_exclusive_options,
be.check_atp,
be.shipping_allowed,
be.required_to_ship,
be.required_for_revenue,
be.include_on_ship_docs,
be.include_on_bill_docs,
be.low_quantity,
be.high_quantity,
be.pick_components,
be.bom_item_type,
be.operation_seq_num,
be.item_num,
be.effectivity_date,
be.disable_date,
be.implementation_date,
bic.wip_supply_type
FROM BOM_EXPLOSIONS be,
bom_inventory_components bic
WHERE be.TOP_BILL_SEQUENCE_ID = X_Top_Bill_Sequence_Id
AND be.ORGANIZATION_ID = X_Organization_Id
AND be.EXPLOSION_TYPE = 'OPTIONAL'
AND be.SORT_ORDER = X_Sort_Order
AND be.EFFECTIVITY_DATE <= X_Effectivity_Date
AND be.DISABLE_DATE > X_Effectivity_Date
AND bic.component_sequence_id = be.component_sequence_id;
END Insert_Row;
INSERT INTO BOM_INVENTORY_COMPONENTS
(
bill_sequence_id,
component_sequence_id,
component_item_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
component_quantity,
component_yield_factor,
planning_factor,
quantity_related,
include_in_cost_rollup,
so_basis,
optional,
mutually_exclusive_options,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
pick_components,
bom_item_type,
operation_seq_num,
item_num,
effectivity_date,
disable_date,
implementation_date,
wip_supply_type,
From_End_Item_Unit_Number,
To_End_Item_Unit_Number
)
SELECT
bill_sequence_id,
BOM_INVENTORY_COMPONENTS_S.nextval,
be.Component_Item_Id,
SYSDATE,
1,
SYSDATE,
1,
be.Attribute1,
be.Attribute2,
be.Attribute3,
be.Attribute4,
be.Attribute5,
be.Attribute6,
be.Attribute7,
be.Attribute8,
be.Attribute9,
be.Attribute10,
be.Attribute11,
be.Attribute12,
be.Attribute13,
be.Attribute14,
be.Attribute15,
round( be.Component_Quantity, 7 ), /* Support Decimal-Qty for Option Items */
1, /* Component Yield*/
be.planning_factor, /*Component Planning factor */
NVL(to_number(be.so_transactions_flag),2), /* used for Quantity Related */
be.include_in_rollup_flag, /* Include in Cost Rollup */
be.so_basis, /* SO Basis */
be.optional, /* Optional */
be.mutually_exclusive_options, /*Mutually_Exclusive_Options */
be.check_atp, /*Check_ATP*/
2, /*Shipping Allowed */
2, /*Required to ship */
2, /*Required_For_Revenue*/
2, /*Include on Ship Docs */
2, /*Include_On_Bill_Docs */
be.Low_Quantity,
be.High_Quantity,
DECODE(be.pick_components_flag, 'Y', 1, 2), /* Pick_Components */
be.Bom_Item_Type,
be.operation_seq_num, /*Operation Sequence Num */
be.item_num, /*Item_Num */
SYSDATE,
be.disable_date, /*Disable_Date*/
SYSDATE, /* Implementation Date */
be.wip_supply_type, /* wip_supply_type */
substr(be.pricing_attribute1,1,30), /* Used for From_Unit_Number */
substr(be.pricing_attribute2,1,30) /* Used for To_Unit_Number */
FROM bom_explosion_temp be
WHERE be.bill_sequence_id = p_Bill_Sequence_id;
* will be deleted.
****************************************************************************************/
Procedure Club_Component_Quantities
( p_bill_sequence_id NUMBER )
IS
CURSOR c_Club_Comps IS
SELECT bet.bill_sequence_id
, bet.component_item_id
, bet.operation_seq_num
, bet.component_sequence_id
, round( bet.component_quantity, 7 ) component_quantity /* Support Decimal-Qty for Option Items */
, rowid /* Sushant added on 19-Aug-2002 */
FROM bom_explosion_temp bet
WHERE bill_sequence_id = p_bill_sequence_id
ORDER BY bet.bill_sequence_id,
bet.component_item_id,
bet.operation_seq_num;
select component_item_id, operation_seq_num, component_sequence_id
from bom_explosion_temp
where bill_sequence_id = p_bill_sequence_id
order by component_item_id;
-- And then delete the component
DELETE FROM Bom_Explosion_Temp
WHERE rowid = c_components.rowid; /* changed by Sushant on 19-AUG-2002 */
-- Update the Component and then reset the local variables
UPDATE BOM_EXPLOSION_TEMP
SET component_quantity = l_quantity
WHERE rowid = l_rowid; /* Changed by Sushant on 19-Aug-2002 */
/* Update the last component which will be left out in the loop */
/* Added by Sushant on 19-Aug-2002 */
UPDATE BOM_EXPLOSION_TEMP
SET component_quantity = l_quantity
WHERE rowid = l_rowid;
SELECT bic.bill_sequence_id
, bic.component_sequence_id
, bic.operation_seq_num
, bic.component_item_id
FROM bom_inventory_components bic,
bom_bill_of_materials bom
WHERE -- bic.operation_seq_num = 1 AND /* Changed by Sushant on 19-Aug-2002*/
bic.bill_sequence_id = DECODE(bom.common_bill_sequence_id, NULL,
bom.bill_sequence_id, bom.common_bill_sequence_id)
AND bom.assembly_item_id = p_component_item_id
AND bom.organization_id = p_organization_id
AND bom.alternate_bom_designator IS NULL;
UPDATE bom_explosion_temp
SET operation_seq_num = l_operation_seq_num /* Changed by Sushant on 19-Aug-2002 */
WHERE component_sequence_id =
c_comps_of_options.component_sequence_id;
-- OC was not selected for the configured bill
END;
** the user has chosen and the option classes that those options belong to and insert
** them in a temporary table. Then it will take all the mandatory components that
** are associated with the option classes from which a user has chosen atleast 1
** Option and insert the data in a temporary table.
** Once the required data is gathered under one group id, the process will check if
** the Profile "BOM:CONFIG_INHERIT_OP_SEQ" is set.If YES then the procedure will
** loop through the option classes and assign the operation sequence to its children
** if the children have an op_seq of 1. This process will recursively loop through
** its children and perform the operation sequence inheritance for all the children.
** Once the records have been assigned the proper op-seq's the process will then
** proceed to consolidate the components. Components quantities for components with
** the same op-seq and component_item_id will be added and only 1 record for that
** combination will exist and the duplicates will be deleted. The final data will be
** moved from the temporary table to the production table and the data in the temp
** table will be cleaned up.
********************************************************************************************/
PROCEDURE BOM_INS_MODEL_AND_MANDATORY(p_group_id IN NUMBER,
p_bill_sequence_id IN NUMBER,
p_top_bill_sequence_id IN NUMBER,
p_top_predefined_item_id IN NUMBER,
p_validation_org_id IN NUMBER,
p_current_org_id IN NUMBER,
p_cz_config_hdr_id IN NUMBER,
p_cz_config_rev_num IN NUMBER,
x_top_ato_line_id OUT NOCOPY NUMBER,
x_top_matched_item_id OUT NOCOPY NUMBER,
x_match_profile_on OUT NOCOPY VARCHAR2,
x_match_found OUT NOCOPY VARCHAR2,
x_message IN OUT NOCOPY VARCHAR2) IS
BEGIN
/* Temporary fix, might need assembly item id for which the bill is being
configured */
/* BUG #1957336 Temporary change for preconfigure bom by Sushant Sawant */
-- insert into my_debug_messages values ( ' header_id ' || to_char( p_cz_config_hdr_id ) ) ;
select
config_item_id ,
parent_config_item_id,
cz.inventory_item_id ,
cz.component_code,
msi.segment1,
bic.component_item_id,
nvl( bic.component_sequence_id , p_top_bill_sequence_id ) ,
bic.bom_item_type,
bic.wip_supply_type,
--bic.component_quantity
cz.quantity, --bugfix 2457660
msi.config_orgs,
msi.config_match,
cz.uom_code --bugfix 4605114
from cz_config_details_v cz ,
bom_inventory_components bic,
mtl_system_items msi
where bic.component_sequence_id(+) = cz.component_sequence_id
AND msi.inventory_item_id = cz.inventory_item_id
AND cz.config_hdr_id = p_cz_config_hdr_id
AND cz.config_rev_nbr = p_cz_config_rev_num
AND msi.organization_id = p_validation_org_id ;
select assembly_item_id
into v_top_model_item_id
from bom_bill_of_materials
where bill_sequence_id = p_top_bill_sequence_id ;
select bom_cto_order_lines_s1.nextval
into bcol_tab(bcol_index).line_id
from dual ;
bcol_tab(bcol_index).last_update_date := sysdate ;
bcol_tab(bcol_index).last_updated_by := gUserId ;
insert_into_bcol( bcol_tab ) ;
select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
perform_match , config_item_id into x_match_found , x_top_matched_item_id
from bom_cto_order_lines_gt
where line_id = v_ato_line_id ;
select line_id, ato_line_id, inventory_item_id, config_item_id , perform_match
into v_ck_line_id, v_ck_ato_line_id, v_ck_inventory_item_id,
v_ck_config_item_id, v_ck_perform_match
from bom_cto_order_lines
where line_id = v_ato_line_id ;
select line_id, inventory_item_id , parent_ato_line_id , perform_match
from bom_cto_order_lines
where bom_item_type = '1'
and ato_line_id = p_top_ato_line_id
and nvl(wip_supply_type,0) <> 6
order by plan_level desc;
select line_id, ato_line_id, inventory_item_id, config_item_id , perform_match
into v_ck_line_id, v_ck_ato_line_id, v_ck_inventory_item_id,
v_ck_config_item_id, v_ck_perform_match
from bom_cto_order_lines
where line_id = p_top_ato_line_id ;
update bom_cto_order_lines set config_item_id = p_top_predefined_item_id
, perform_match = 'U' where line_id = p_top_ato_line_id ;
update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt
set config_item_id = p_top_predefined_item_id
, perform_match = 'U'
where line_id = p_top_ato_line_id ;
update bom_cto_order_lines set config_item_id = p_top_predefined_item_id
, perform_match = 'N' where line_id = p_top_ato_line_id ;
update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt
set config_item_id = p_top_predefined_item_id
, perform_match = 'N'
where line_id = p_top_ato_line_id ;
select inventory_item_id , nvl(program_id,0) ,config_item_id
into l_top_model_id,lprogram_id,lconfig_item_id
from bom_cto_order_lines
where line_id=p_top_ato_line_id ;
select nvl(item_catalog_group_id,0)
into licg_id
from mtl_system_items
where inventory_item_id = l_top_model_id
and organization_id = lValidationOrg;
update mtl_system_items
set item_catalog_group_id = licg_id
where inventory_item_id = lconfig_item_id
and nvl(item_catalog_group_id,0) = 0; --Bugfix 6043798
oe_debug_pub.add ('Updated catalog group id of preconfig item ' , 2) ;
select line_id, ato_line_id, inventory_item_id, config_item_id , perform_match
into v_ck_line_id, v_ck_ato_line_id, v_ck_inventory_item_id,
v_ck_config_item_id, v_ck_perform_match
from bom_cto_order_lines
where line_id = p_top_ato_line_id ;
select 'Y' into x_routing_exists
from dual
where EXISTS (
select routing_sequence_id
from bom_operational_routings bor, bom_cto_src_orgs bcso
where bor.assembly_item_id = bcso.config_item_id
and bor.organization_id = bcso.organization_id
and bor.alternate_routing_designator is null
and bcso.line_id = p_top_ato_line_id
and bcso.create_bom = 'Y'
) ;
delete from bom_ato_configurations where config_item_id =
p_top_matched_item_id ;
CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' delete top matched item id from bom_ato_configurations for item ' || to_char( p_top_matched_item_id ) ) ;
select perform_match
into lPerformMatch
from bom_cto_order_lines
where line_id = lNextRec.line_id;
select bill_sequence_id into x_bill_sequence_id
from bom_bill_of_materials
where assembly_item_id = p_top_matched_item_id
and organization_id = p_current_org_id ;
select decode( count(*) , 0 , 'N' , 'Y' ) into x_mlmo_item_created
from bom_cto_src_orgs
where organization_id <> nvl(rcv_org_id, organization_id)
and top_model_line_id = p_top_ato_line_id ;
delete from bom_cto_order_lines
where ato_line_id = p_top_ato_line_id ;
CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' deleted bcol ' || to_char(sql%rowcount)) ;
delete from bom_cto_src_orgs_b
where top_model_line_id = p_top_ato_line_id ;
CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' deleted bcso_b ' || to_char(sql%rowcount)) ;
p_t_bcol.delete ;
p_t_bcol.delete ;
v_raw_line_id.delete ; /* remove all elements as they have been resolved */
v_raw_line_id.delete ; /* remove all elements as they have been resolved */
procedure insert_into_bcol (
p_bcol_tab bcol_tbl_type
)
IS
v_step number := 0 ;
CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( ' came into insert into bcol: ' , 1 ) ;
CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( ' insert into bcol: ' ||
' line_id ' || p_bcol_tab(i).line_id ||
' parent line_id ' || p_bcol_tab(i).parent_ato_line_id ||
' qty ' || p_bcol_tab(i).ordered_quantity , 1) ;
CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( ' insert into bcol: ' ||
' line_id ' || p_bcol_tab(i).line_id ||
' parent line_id ' || p_bcol_tab(i).parent_ato_line_id ||
' parent qty ' || p_bcol_tab(p_bcol_tab(i).parent_ato_line_id ).ordered_quantity , 1) ;
Insert into bom_cto_order_lines_gt(
HEADER_ID ,
LINE_ID ,
LINK_TO_LINE_ID ,
ATO_LINE_ID ,
PARENT_ATO_LINE_ID ,
TOP_MODEL_LINE_ID ,
PLAN_LEVEL ,
WIP_SUPPLY_TYPE ,
PERFORM_MATCH ,
BOM_ITEM_TYPE ,
COMPONENT_CODE ,
COMPONENT_SEQUENCE_ID ,
CONFIG_ITEM_ID ,
INVENTORY_ITEM_ID ,
ITEM_TYPE_CODE ,
BATCH_ID ,
ORDERED_QUANTITY ,
ORDER_QUANTITY_UOM ,
SCHEDULE_SHIP_DATE ,
SHIP_FROM_ORG_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
qty_per_parent_model,
option_specific ,
reuse_config ,
config_creation )
VALUES (
nvl( p_bcol_tab(i).header_id , 1 ) ,
p_bcol_tab(i).line_id,
p_bcol_tab(i).link_to_line_id,
p_bcol_tab(i).ato_line_id,
p_bcol_tab(i).parent_ato_line_id,
nvl( p_bcol_tab(i).top_model_line_id,1 ),
p_bcol_tab(i).plan_level,
p_bcol_tab(i).wip_supply_type,
p_bcol_tab(i).perform_match,
p_bcol_tab(i).bom_item_type,
p_bcol_tab(i).component_code,
p_bcol_tab(i).component_sequence_id,
p_bcol_tab(i).config_item_id,
p_bcol_tab(i).inventory_item_id,
nvl( p_bcol_tab(i).item_type_code, decode( p_bcol_tab(i).line_id, p_bcol_tab(i).ato_line_id , 'MODEL' , decode( p_bcol_tab(i).bom_item_type , '4' , 'OPTION' , 'CLASS' ) )),
p_bcol_tab(i).batch_id,
p_bcol_tab(i).ordered_quantity,
p_bcol_tab(i).order_quantity_uom,
p_bcol_tab(i).schedule_ship_date,
p_bcol_tab(i).ship_from_org_id,
p_bcol_tab(i).last_update_date,
p_bcol_tab(i).last_updated_by,
p_bcol_tab(i).creation_date,
p_bcol_tab(i).created_by ,
p_bcol_tab(i).last_update_login ,
p_bcol_tab(i).request_id ,
p_bcol_tab(i).program_application_id ,
p_bcol_tab(i).program_id ,
p_bcol_tab(i).program_update_date ,
p_bcol_tab(i).ordered_quantity / p_bcol_tab(p_bcol_Tab(i).parent_ato_line_id).ordered_quantity ,
p_bcol_tab(i).option_specific ,
p_bcol_tab(i).reuse_config,
p_bcol_tab(i).config_creation ) ;
CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( ' insert into bcol: ' ||
' line_id ' || p_bcol_tab(i).line_id ||
' parent line_id ' || p_bcol_tab(i).parent_ato_line_id ||
' ato line_id ' || p_bcol_tab(i).ato_line_id ||
' bom_item_type ' || nvl( p_bcol_tab(i).bom_item_type, -7) ||
' wip_supply_type ' || nvl( p_bcol_tab(i).wip_supply_type , -7) ||
' config_item_id ' || nvl( p_bcol_tab(i).config_item_id , -7) ||
' count ' || SQL%ROWCOUNT , 2);
END INSERT_INTO_BCOL ;
select line_id, parent_ato_line_id
from bom_cto_order_lines
where bom_item_type = '1'
and ato_line_id = p_ato_line_id
and nvl(wip_supply_type,0) <> 6
order by plan_level desc;
select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
perform_match into l_perform_match
from bom_cto_order_lines_gt
where line_id = l_next_rec.line_id ;
update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt set perform_match = 'N'
where perform_match = 'Y'
and line_id = l_next_rec.parent_ato_line_id ;
/* update the perform match column to 'N' so that this item is canned */
begin
update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt
set perform_match = 'N'
where line_id = l_next_rec.line_id
and perform_match = 'Y';
/* update the perform match column to 'N' so that no match
is attempted against its parent and it is canned
*/
begin
update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt
set perform_match = 'N'
where line_id = l_next_rec.parent_ato_line_id
and perform_match = 'Y';
update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt
set config_item_id = l_x_config_id
where line_id = l_next_rec.line_id;
** the user has chosen and the option classes that those options belong to and insert
** them in a temporary table. Then it will take all the mandatory components that
** are associated with the option classes from which a user has chosen atleast 1
** Option and insert the data in a temporary table.
** Once the required data is gathered under one group id, the process will check if
** the Profile "BOM:CONFIG_INHERIT_OP_SEQ" is set.If YES then the procedure will
** loop through the option classes and assign the operation sequence to its children
** if the children have an op_seq of 1. This process will recursively loop through
** its children and perform the operation sequence inheritance for all the children.
** Once the records have been assigned the proper op-seq's the process will then
** proceed to consolidate the components. Components quantities for components with
** the same op-seq and component_item_id will be added and only 1 record for that
** combination will exist and the duplicates will be deleted. The final data will be
** moved from the temporary table to the production table and the data in the temp
** table will be cleaned up.
********************************************************************************************/
PROCEDURE BOM_INS_MODEL_AND_MANDATORY(x_group_id IN NUMBER,
x_bill_sequence_id IN NUMBER,
x_top_bill_sequence_id IN NUMBER,
x_cz_config_hdr_id IN NUMBER,
x_cz_config_rev_num IN NUMBER,
x_message IN OUT NOCOPY VARCHAR2) IS
X_Stmt_Num NUMBER;
/* Cursor will select the options that the user had chosen from the option classes
on the Model
*/
CURSOR cz_options_chosen IS
SELECT bic.bill_sequence_id,
x_top_bill_sequence_id top_bill_sequence_id,
bic.operation_seq_num,
bic.component_item_id,
bic.last_update_date,
bic.last_updated_by,
bic.creation_date,
bic.created_by,
bic.item_num,
round( to_number(cz.quantity), 7) component_quantity, /* Support Decimal-Qty for Option Items */
bic.component_yield_factor,
bic.effectivity_date,
bic.implementation_date,
bic.planning_factor,
bic.quantity_related,
bic.so_basis,
bic.optional,
bic.mutually_exclusive_options,
bic.include_in_cost_rollup,
bic.check_atp,
bic.required_to_ship,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
bic.low_quantity,
bic.high_quantity,
bic.wip_supply_type,
bic.pick_components,
bic.bom_item_type,
bic.component_sequence_id,
bic.From_End_Item_Unit_Number,
bic.To_End_Item_Unit_Number,
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,
cz.component_code
FROM bom_inventory_components bic
, cz_config_details_v cz
WHERE bic.bom_item_type NOT IN('1', '2')
AND bic.component_sequence_id = cz.component_sequence_id
AND cz.config_hdr_id = x_cz_config_hdr_id
AND cz.config_rev_nbr = x_cz_config_rev_num;
SELECT bic.bill_sequence_id,
x_top_bill_sequence_id,
bic.operation_seq_num,
bic.component_item_id,
bic.last_update_date,
bic.last_updated_by,
bic.creation_date,
bic.created_by,
bic.item_num,
round( (bic.component_quantity * to_number(cz.quantity)), 7) component_quantity, /* Support Decimal-Qty for Option Items */
bic.component_yield_factor,
bic.effectivity_date,
bic.implementation_date,
bic.planning_factor,
bic.quantity_related,
bic.so_basis,
bic.optional,
bic.mutually_exclusive_options,
bic.include_in_cost_rollup,
bic.check_atp,
bic.required_to_ship,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
bic.low_quantity,
bic.high_quantity,
bic.wip_supply_type,
bic.pick_components,
bic.bom_item_type,
bic.component_sequence_id,
bic.From_End_Item_Unit_Number,
bic.To_End_Item_Unit_Number,
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
FROM bom_inventory_components bic
, bom_inventory_components mod_oc
, bom_bill_of_materials bom
, cz_config_details_v cz
WHERE cz.config_hdr_id = x_cz_config_hdr_id
AND cz.config_rev_nbr = x_cz_config_rev_num
AND mod_oc.component_sequence_id = cz.component_sequence_id
AND mod_oc.bom_item_type IN (1,2)
AND bom.assembly_item_id = mod_oc.component_item_id
AND bom.organization_id = cz.organization_id
AND bom.alternate_bom_designator IS NULL
AND bic.bill_sequence_id = DECODE(bom.common_bill_sequence_id, bom.bill_sequence_id,
bom.bill_sequence_id, bom.common_bill_sequence_id
)
AND bic.optional = 2
AND bic.bom_item_type NOT IN (1,2)
AND bic.effectivity_date <= SYSDATE
AND nvl(bic.disable_date,SYSDATE+1) > SYSDATE;
SELECT bic.bill_sequence_id,
x_top_bill_sequence_id,
bic.operation_seq_num,
bet.operation_seq_num parent_operation_seq_num,
bic.component_item_id,
bic.last_update_date,
bic.last_updated_by,
bic.creation_date,
bic.created_by,
bic.item_num,
round( (bic.component_quantity * to_number(cz.quantity)) , 7) component_quantity, /* Support Decimal-Qty for Option Items */
bic.component_yield_factor,
bic.effectivity_date,
bic.implementation_date,
bic.planning_factor,
bic.quantity_related,
bic.so_basis,
bic.optional,
bic.mutually_exclusive_options,
bic.include_in_cost_rollup,
bic.check_atp,
bic.required_to_ship,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
bic.low_quantity,
bic.high_quantity,
bic.wip_supply_type,
bic.pick_components,
bic.bom_item_type,
bic.component_sequence_id,
bic.From_End_Item_Unit_Number,
bic.To_End_Item_Unit_Number,
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
FROM bom_inventory_components bic
, bom_inventory_components mod_oc
, bom_bill_of_materials bom
, cz_config_details_v cz
, bom_explosion_temp bet
WHERE cz.config_hdr_id = x_cz_config_hdr_id
AND cz.config_rev_nbr = x_cz_config_rev_num
AND cz.component_code = bet.component_code
AND mod_oc.component_sequence_id = cz.component_sequence_id
AND mod_oc.bom_item_type IN (1,2)
AND bom.assembly_item_id = mod_oc.component_item_id
AND bom.organization_id = cz.organization_id
AND bom.alternate_bom_designator IS NULL
AND bic.bill_sequence_id = DECODE(bom.common_bill_sequence_id, bom.bill_sequence_id,
bom.bill_sequence_id, bom.common_bill_sequence_id
)
AND bic.optional = 2
AND bic.bom_item_type NOT IN (1,2)
AND bic.effectivity_date <= SYSDATE
AND nvl(bic.disable_date,SYSDATE+1) > SYSDATE;
SELECT * from bom_explosion_temp WHERE component_code IS NOT NULL
ORDER BY component_code;
SELECT bic.component_sequence_id
, bic.component_item_id
, bic.operation_seq_num
, cz.organization_id
FROM bom_inventory_components bic,
cz_config_details_v cz
WHERE cz.component_sequence_id = bic.component_sequence_id
AND bic.bom_item_type IN (1,2)
AND bic.bill_sequence_id = l_bill_sequence_id
AND cz.config_hdr_id = x_cz_config_hdr_id
AND cz.config_rev_nbr = x_cz_config_rev_num;
SELECT bet.bill_sequence_id
, bet.component_item_id
, bet.operation_seq_num
, bet.component_sequence_id
FROM bom_explosion_temp bet
WHERE bill_sequence_id = x_bill_sequence_id
ORDER BY bet.bill_sequence_id,
bet.component_item_id,
bet.operation_seq_num,
bet.component_sequence_id;
SELECT DECODE(common_bill_sequence_id, x_top_bill_sequence_id,
bill_sequence_id, common_bill_sequence_id
),
DECODE(nvl(common_organization_id, organization_id), organization_id,
organization_id, common_organization_id)
INTO l_bill_sequence_id,
l_organization_id
FROM bom_bill_of_materials
WHERE bill_sequence_id = x_top_bill_sequence_id;
DELETE from bom_explosion_temp;
/* Insert Model */
INSERT INTO BOM_INVENTORY_COMPONENTS
(
bill_sequence_id,
component_sequence_id,
component_item_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
component_quantity,
component_yield_factor,
planning_factor,
quantity_related,
include_in_cost_rollup,
so_basis,
optional,
mutually_exclusive_options,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
pick_components,
bom_item_type,
operation_seq_num,
item_num,
effectivity_date,
disable_date,
implementation_date,
wip_supply_type
)
SELECT x_bill_sequence_id,
BOM_INVENTORY_COMPONENTS_S.nextval,
be.Component_Item_Id,
SYSDATE,
1,
SYSDATE,
1,
be.Attribute1,
be.Attribute2,
be.Attribute3,
be.Attribute4,
be.Attribute5,
be.Attribute6,
be.Attribute7,
be.Attribute8,
be.Attribute9,
be.Attribute10,
be.Attribute11,
be.Attribute12,
be.Attribute13,
be.Attribute14,
be.Attribute15,
round( be.Component_Quantity, 7 ), /* Support Decimal-Qty for Option Items */
1, /* Component Yield*/
100, /*Component Planning factor */
2, /* Quantity Related */
2, /* Include in Cost Rollup */
2, /* SO Basis */
1, /* Optional */
2, /*Mutually_Exclusive_Options */
2, /*Check_ATP*/
2, /*Shipping Allowed */
2, /*Required to ship */
2, /*Required_For_Revenue*/
2, /*Include on Ship Docs */
2, /*Include_On_Bill_Docs */
be.Low_Quantity,
be.High_Quantity,
1, /* Pick_Components */
be.Bom_Item_Type,
1, /*Operation Sequence Num */
1, /*Item_Num */
SYSDATE,
NULL /*Disable_Date*/,
SYSDATE, /* Implementation Date */
6 /* wip_supply_type */
FROM bom_explosions be
WHERE be.top_bill_sequence_id = X_top_Bill_Sequence_id
AND be.explosion_type = 'OPTIONAL'
AND be.effectivity_date <= SYSDATE
AND nvl(be.disable_date,SYSDATE+1) > SYSDATE
AND be.plan_level = 0;
/* Insert the Mandatory Components of the Model */
INSERT INTO BOM_EXPLOSION_TEMP
( top_bill_sequence_id,
organization_id,
plan_level,
sort_order,
bill_sequence_id,
component_sequence_id,
component_item_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
component_quantity,
component_yield_factor,
planning_factor,
include_in_rollup_flag,
so_transactions_flag, /* Used for Quantity Related */
so_basis,
optional,
mutually_exclusive_options,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
pick_components,
bom_item_type,
operation_seq_num,
item_num,
effectivity_date,
disable_date,
implementation_date,
wip_supply_type,
pricing_attribute1, /** used for from unit item number **/
pricing_attribute2 /** used for to unit item number **/
)
SELECT x_top_bill_sequence_id,
l_organization_id, -- resolved at the begining
1, /*Plan Level */
'1', /* Sort Order */
x_bill_sequence_id,
component_sequence_id,
Component_Item_Id,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
Component_Quantity,
component_yield_factor,
planning_factor, /*Component Planning factor */
include_in_cost_rollup, /* Include in Cost Rollup */
NVL(to_char(quantity_related),'2'),
so_basis, /* SO Basis */
optional, /* Optional */
Mutually_Exclusive_Options, /*Mutually_Exclusive_Options */
check_atp, /*Check_ATP*/
shipping_allowed, /*Shipping Allowed */
required_to_ship, /*Required to ship */
required_for_revenue, /*Required_For_Revenue*/
include_on_ship_docs, /*Include on Ship Docs */
include_on_bill_docs, /*Include_On_Bill_Docs */
Low_Quantity,
High_Quantity,
pick_components, /* Pick_Components */
Bom_Item_Type,
operation_seq_num, /*Operation Sequence Num */
item_num, /*Item_Num */
effectivity_date,
disable_date, /*Disable_Date*/
implementation_date, /* Implementation Date */
wip_supply_type,/* wip_supply_type */
from_end_item_unit_number,
to_end_item_unit_number
FROM bom_inventory_components
WHERE bill_sequence_id = l_Bill_Sequence_id -- Sequence_id resolved at the begining
AND effectivity_date <= SYSDATE
AND nvl(disable_date,SYSDATE+1) > SYSDATE
AND optional = 2
AND bom_item_type NOT IN (1,2);
/* Insert the Option Classes from which user has chosen atleast one option item along with
the component code*/
INSERT INTO BOM_EXPLOSION_TEMP
( top_bill_sequence_id,
organization_id,
plan_level,
sort_order,
bill_sequence_id,
component_sequence_id,
component_item_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
component_quantity,
component_yield_factor,
planning_factor,
include_in_rollup_flag,
so_transactions_flag, /* Used for Quantity Related */
so_basis,
optional,
mutually_exclusive_options,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
pick_components,
bom_item_type,
operation_seq_num,
item_num,
effectivity_date,
disable_date,
implementation_date,
wip_supply_type,
pricing_attribute1, /** used for from unit item number **/
pricing_attribute2, /** used for to unit item number **/
component_code
)
SELECT x_top_bill_sequence_id,
l_organization_id, -- resolved at the begining
1, /*Plan Level */
'1', /* Sort Order */
x_bill_sequence_id,
bic.component_sequence_id,
bic.Component_Item_Id,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
--Component_Quantity,
round( cz.quantity,7), /* Support Decimal-Qty for option items */
component_yield_factor,
planning_factor, /*Component Planning factor */
include_in_cost_rollup, /* Include in Cost Rollup */
NVL(to_char(quantity_related),'2'),
so_basis, /* SO Basis */
optional, /* Optional */
Mutually_Exclusive_Options, /*Mutually_Exclusive_Options */
check_atp, /*Check_ATP*/
shipping_allowed, /*Shipping Allowed */
required_to_ship, /*Required to ship */
required_for_revenue, /*Required_For_Revenue*/
include_on_ship_docs, /*Include on Ship Docs */
include_on_bill_docs, /*Include_On_Bill_Docs */
Low_Quantity,
High_Quantity,
pick_components, /* Pick_Components */
bic.Bom_Item_Type,
operation_seq_num, /*Operation Sequence Num */
item_num, /*Item_Num */
effectivity_date,
disable_date, /*Disable_Date*/
implementation_date, /* Implementation Date */
wip_supply_type,/* wip_supply_type */
from_end_item_unit_number,
to_end_item_unit_number,
cz.component_code
FROM bom_inventory_components bic,
cz_config_details_v cz
WHERE bic.component_sequence_id = cz.component_sequence_id
AND bic.bom_item_type IN (1,2) /* Model, Option Classes */
AND cz.config_hdr_id = x_cz_config_hdr_id
AND cz.config_rev_nbr = x_cz_config_rev_num;
/* Insert Mandatory Components for the options selected if inheritance is OFF */
IF l_op_seq_profile <> 1 THEN
FOR cz_mandatory_items IN c_cz_required_items
LOOP
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,
effectivity_date,
implementation_date,
planning_factor,
so_transactions_flag, /** used for quantity_related **/
so_basis,
optional,
mutually_exclusive_options,
include_in_rollup_flag,
check_atp,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
component_sequence_id,
bill_sequence_id,
wip_supply_type,
pick_components,
bom_item_type,
pricing_attribute1, /** Used for From_End_Item_Unit_Number **/
pricing_attribute2, /** Used for To_End_Item_Unit_Number **/
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES
( x_top_bill_sequence_id
, l_organization_id
, 1 /* Plan Level */
, '1' /* Sort Order */
, cz_mandatory_items.OPERATION_SEQ_NUM
, cz_mandatory_items.COMPONENT_ITEM_ID
, Decode(cz_mandatory_items.item_num, NULL,
1, cz_mandatory_items.ITEM_NUM)
, round( cz_mandatory_items.COMPONENT_QUANTITY , 7) /* Support Decimal-Qty for Option Items */
, cz_mandatory_items.component_yield_factor /* component_yield_factor */
, cz_mandatory_items.EFFECTIVITY_DATE
, cz_mandatory_items.IMPLEMENTATION_DATE
, 100 /* planning_factor */
, '2' /* quantity_related */
, cz_mandatory_items.SO_BASIS
, cz_mandatory_items.OPTIONAL
, cz_mandatory_items.MUTUALLY_EXCLUSIVE_OPTIONS
, cz_mandatory_items.include_in_cost_rollup
, cz_mandatory_items.CHECK_ATP
, cz_mandatory_items.REQUIRED_TO_SHIP
, cz_mandatory_items.REQUIRED_FOR_REVENUE
, cz_mandatory_items.INCLUDE_ON_SHIP_DOCS
, cz_mandatory_items.INCLUDE_ON_BILL_DOCS
, cz_mandatory_items.LOW_QUANTITY
, cz_mandatory_items.HIGH_QUANTITY
, cz_mandatory_items.component_sequence_id
, X_BILL_SEQUENCE_ID
, decode(cz_mandatory_items.bom_item_type, 2, 6, 1, 6,
nvl(cz_mandatory_items.wip_supply_type,1)) /* wip_supply_type */
, cz_mandatory_items.PICK_COMPONENTS
, cz_mandatory_items.BOM_ITEM_TYPE
, cz_mandatory_items.From_End_Item_Unit_Number
, cz_mandatory_items.To_End_Item_Unit_Number
, cz_mandatory_items.attribute1
, cz_mandatory_items.attribute2
, cz_mandatory_items.attribute3
, cz_mandatory_items.attribute4
, cz_mandatory_items.attribute5
, cz_mandatory_items.attribute6
, cz_mandatory_items.attribute7
, cz_mandatory_items.attribute8
, cz_mandatory_items.attribute9
, cz_mandatory_items.attribute10
, cz_mandatory_items.attribute11
, cz_mandatory_items.attribute12
, cz_mandatory_items.attribute13
, cz_mandatory_items.attribute14
, cz_mandatory_items.attribute15
);
/* Insert all the selected Options along with the component code */
FOR cz_options IN cz_options_chosen
LOOP
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,
effectivity_date,
implementation_date,
planning_factor,
so_transactions_flag,
so_basis,
optional,
mutually_exclusive_options,
include_in_rollup_flag,
check_atp,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
component_sequence_id,
bill_sequence_id,
wip_supply_type,
pick_components,
bom_item_type,
pricing_attribute1, /** Used for From_End_Item_Unit_Number **/
pricing_attribute2, /** Used for To_End_Item_Unit_Number **/
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
component_code
)
VALUES
( x_top_bill_sequence_id
, l_organization_id
, 1 /* Plan Level */
, '1' /* Sort Order */
, cz_options.OPERATION_SEQ_NUM
, cz_options.COMPONENT_ITEM_ID
, Decode(cz_options.item_num, NULL,
1, cz_options.ITEM_NUM)
, cz_options.COMPONENT_QUANTITY
, cz_options.component_yield_factor /* component_yield_factor */
, cz_options.EFFECTIVITY_DATE
, cz_options.IMPLEMENTATION_DATE
, 100 /* planning_factor */
, '2' /* quantity_related */
, cz_options.SO_BASIS
, cz_options.OPTIONAL
, cz_options.MUTUALLY_EXCLUSIVE_OPTIONS
, cz_options.include_in_cost_rollup
, cz_options.CHECK_ATP
, cz_options.REQUIRED_TO_SHIP
, cz_options.REQUIRED_FOR_REVENUE
, cz_options.INCLUDE_ON_SHIP_DOCS
, cz_options.INCLUDE_ON_BILL_DOCS
, cz_options.LOW_QUANTITY
, cz_options.HIGH_QUANTITY
, cz_options.component_sequence_id
, X_BILL_SEQUENCE_ID
, decode(cz_options.bom_item_type, 2, 6, 1, 6,
nvl(cz_options.wip_supply_type,1)) /* wip_supply_type */
, cz_options.PICK_COMPONENTS
, cz_options.BOM_ITEM_TYPE
, cz_options.From_End_Item_Unit_Number
, cz_options.To_End_Item_Unit_Number
, cz_options.attribute1
, cz_options.attribute2
, cz_options.attribute3
, cz_options.attribute4
, cz_options.attribute5
, cz_options.attribute6
, cz_options.attribute7
, cz_options.attribute8
, cz_options.attribute9
, cz_options.attribute10
, cz_options.attribute11
, cz_options.attribute12
, cz_options.attribute13
, cz_options.attribute14
, cz_options.attribute15
, cz_options.component_code
);
/** Finished inserting the chosen options **/
/* Proceed to operation sequence number inheritance.
Inheritance will be performed for the following
1. All option classes choosen for the config item (this does not include the option classes
that are directly under the top model. Those should already have the valid op.seq number.
Inheritance starts from the second level. First level components under the top model
will always have the op.seq number.)
2. All the choosen options
The above two are identified by a valid value for the component_code.
3. Mandatory components that are directly under the model will already have the op.seq number
4. At this point we have not yet inserted the mandatory components for the choosen options of this
config item if the inherit op.seq is ON. They will be inserted along with the inherited value.
*/
IF l_op_seq_profile = 1
THEN
FOR r1 IN c_Comps_With_ComponentCode
LOOP
IF r1.operation_seq_num = 1 AND Instr(r1.component_code,'-',1,2) <> 0
/* If operation seq number is 1 and the component is not the first level comp. under the top model */
THEN
/* Get the op.seq number from it's immediate parent */
UPDATE bom_explosion_temp btemp
SET btemp.operation_seq_num = (SELECT operation_seq_num FROM
bom_explosion_temp WHERE component_code =
substr(btemp.component_code,1,to_number(instr(btemp.component_code,'-',-1,1))-1))
WHERE component_code = r1.component_code;
/* Insert Mandatory Components for the choosen options along with inherited value*/
IF l_op_seq_profile = 1 THEN
FOR cz_mandatory_items IN c_cz_req_items_with_Inherit
LOOP
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,
effectivity_date,
implementation_date,
planning_factor,
so_transactions_flag, /** used for quantity_related **/
so_basis,
optional,
mutually_exclusive_options,
include_in_rollup_flag,
check_atp,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
component_sequence_id,
bill_sequence_id,
wip_supply_type,
pick_components,
bom_item_type,
pricing_attribute1, /** Used for From_End_Item_Unit_Number **/
pricing_attribute2, /** Used for To_End_Item_Unit_Number **/
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES
( x_top_bill_sequence_id
, l_organization_id
, 1 /* Plan Level */
, '1' /* Sort Order */
, decode(cz_mandatory_items.OPERATION_SEQ_NUM,1,
cz_mandatory_items.PARENT_OPERATION_SEQ_NUM,cz_mandatory_items.OPERATION_SEQ_NUM)
, cz_mandatory_items.COMPONENT_ITEM_ID
, Decode(cz_mandatory_items.item_num, NULL,
1, cz_mandatory_items.ITEM_NUM)
, cz_mandatory_items.COMPONENT_QUANTITY
, cz_mandatory_items.component_yield_factor /* component_yield_factor */
, cz_mandatory_items.EFFECTIVITY_DATE
, cz_mandatory_items.IMPLEMENTATION_DATE
, 100 /* planning_factor */
, '2' /* quantity_related */
, cz_mandatory_items.SO_BASIS
, cz_mandatory_items.OPTIONAL
, cz_mandatory_items.MUTUALLY_EXCLUSIVE_OPTIONS
, cz_mandatory_items.include_in_cost_rollup
, cz_mandatory_items.CHECK_ATP
, cz_mandatory_items.REQUIRED_TO_SHIP
, cz_mandatory_items.REQUIRED_FOR_REVENUE
, cz_mandatory_items.INCLUDE_ON_SHIP_DOCS
, cz_mandatory_items.INCLUDE_ON_BILL_DOCS
, cz_mandatory_items.LOW_QUANTITY
, cz_mandatory_items.HIGH_QUANTITY
, cz_mandatory_items.component_sequence_id
, X_BILL_SEQUENCE_ID
, decode(cz_mandatory_items.bom_item_type, 2, 6, 1, 6,
nvl(cz_mandatory_items.wip_supply_type,1)) /* wip_supply_type */
, cz_mandatory_items.PICK_COMPONENTS
, cz_mandatory_items.BOM_ITEM_TYPE
, cz_mandatory_items.From_End_Item_Unit_Number
, cz_mandatory_items.To_End_Item_Unit_Number
, cz_mandatory_items.attribute1
, cz_mandatory_items.attribute2
, cz_mandatory_items.attribute3
, cz_mandatory_items.attribute4
, cz_mandatory_items.attribute5
, cz_mandatory_items.attribute6
, cz_mandatory_items.attribute7
, cz_mandatory_items.attribute8
, cz_mandatory_items.attribute9
, cz_mandatory_items.attribute10
, cz_mandatory_items.attribute11
, cz_mandatory_items.attribute12
, cz_mandatory_items.attribute13
, cz_mandatory_items.attribute14
, cz_mandatory_items.attribute15
);
DELETE from bom_explosion_temp;
DELETE from bom_explosion_temp;
x_message := 'BOM_CONFIG_EXPLOSIONS_PKG.Insert_Mandatory_Components('
|| to_char(X_Stmt_Num) || '):';