[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 02-14-2000 Sajani Sheth Code changes for visible demand flag update
| 02-15-2000 Sajani Sheth Added code to support WIP parameter
| "Respond to sales order changes"
| 06-01-2000 Sajani Sheth Added code to support
| Multilevel/Multi-org CTO functionality
| 11-06-2000 Kiran Konada Added code to support
| Multilevel/Multi-org CTO functionality
| of changing visible demand flag IN
| BOM_CTO_ORDER_DEMAND table while creation
| of Configuration item
| 01/03/2001 Renga Kannan Removed the raise statements in the
| exception block. This fix is done as
| part of the bug fix #1509712
| 02/02/2001 Renga Kannan Added code to work for Change order
| In the case of model cancelation delink should not
| check for workflow status. Code is changed on 02/02/2001
| 02/03/2001 Renga Kannan Modifed the code in delink_item.
| 03/13/2001 Renga Kannan Added code for the notification part
| This change is part of the Change order project
| 04/04/2001 Renga Kannan Modified the link_item code for the performance issue.
| This is part of the bug fix # 1690095. The process order call in
| in link_item code is replaced with direct update statement.
| 05/30/2001 Sushant Sawant Modified the load_mandatory_comps and
| load_mandatory_components procedure
| for bug fix 1518894.
| 06-10-2001 SBhaskar Bugfix 1811007
| Added code to calculate weight/volume
| of the configuration item in the shipping org.
| 07-18-2001 Kundan Sarkar Bugfix 1876997 .
| Add code to improve performance.
| Modify query to replace an expensive view
| (wsh_delivery_line_status_v) with its base tables.
|
| 08-24-2001 Sushant Sawant: BUG #1957336
| Added a new functionality for preconfigure bom.
|
| 08-31-01 Renga Kannan
| Modified the create_item procedure to call
| CTO_CUSTOM_LIST_PRICE_PK.get_list_price to calculate
| the price list for configuration items.
| For More details please look at Procuring config Phse I
| Design document.
|
| 09-05-2001 Kundan Sarkar Bugfix 1988939 ( Base Bug 1962820 ).
| Truncate effectivity_date for correct selection of
| eligible rows during bom_explosion.
|
| 09-11-2001 Kundan Sarkar Bugfix 1988946 ( Base bug 1968318 ).
| No longer checking activity_label while selecting
| activity_status_code of an item.
|
| 09-25-2001 Kundan Sarkar Bugfix 2034342 ( Base bug 1985793 ).
| Checking greater of sysdate and calendar date while
| checking for item effectivity so that planning will get
| components effective till sysdate.
|
| 09-27-2001 Kundan Sarkar Bugfix 2034342 ( Base bug 1998386 ).
| Passing PRIMARY_QUANTITY_UOM to ATP
|
| 10-02-2001 Kundan Sarkar Bugfix 2041612 ( Base bugs 2034419 and 1997355)
| Calling MRP API MRP_OM_API_PK to insert the config items
| line id during linking and delinking of config item.
|
| 10-29-2001 Kundan Sarkar Bugfix 2133816 ( Base bug 2047428 ).
| Loading Mandatory components even if Top Model ATP components
| flag is set to 'N'.Christine of Planning team has confirmed
| that we should honor the ATP components flag of top model and
| explosion need not take place if the ATP components flag on
| model is set to 'N'. Fixed the code accordingly .
|
| Modified on 08-JAN-2002 by Sushant Sawant: BUG #2172057
| Added a restriction for numbering method to be used while creating
| configuration due to preconfigure bom limitation.
|
| 01-28-2002 Kundan Sarkar Bugfix 2202633 ( Base bug 2197842 ).
| Config item created is not assigned to Purchasing default
| category and hence cannot be entered in a purchase order.
|
| 01-28-2002 Kundan Sarkar Bugfix 2186114 ( Base bug 2162912 ).
| In load_mandatory_comps and load_mandatory_components :
| 1) Truncate disable_date to correctly compare it with calendar_date.
| 2) Replace >= with > while comparing disable_date and calendar_date.
| 3) Replace to_date and to_char conversion functions for eff_date and
| disable_date check since they are compared with calendar_date which
| does not have timestamp.
|
| 03-08-2002 Sushant Sawant BUG#2234858
| Added new functionality for Drop Shipment
| Changed functions link_item
| Changed functions delink_item
|
|
| 03-25-2002 Renga Kannan Removed the custom API call for list price.
| This custom API will be called as part of List price rollup
|
|
|
| 03/31/2002 Renga Kannan Bugfix 2288258 : Make the update visible_demand_flag statement more restrictive
while it is performing update during delink action of ML/MO or BUY Model.
|
| 04/08/2002 Sushant Sawant BugFix 2300006 : Multiple Instantiation Usability Issues.
| This fix will align each configuration next to the model
| it corresponds to.
|
| 04-09-2002 Kundan Sarkar Bugfix 2292466 ( Base bug 2267646 ).
| Added NVL function .
|
| 04-18-2002 Kundan Sarkar Bugfix 2337353 ( Base bug 2157740 ).
| Copying Base Model attachement to Config item.
|
|
| 05-14-2002 Renga Kannan Removed the attribute list_price_per_unit
| from copying it to config item in mtl_system_items
| Look at the bug details 2370307
|
|
| 06-03-2002 Sushant Sawant Bugfix 2401654 [duplicate of 2400948
| and bug 2378556 ]
| Changed query in load_mandatory_components
| made changes to get only standard
| mandatory components.
|
| 06-20-2002 Sushant Sawant Bugfix 2420865(a.k.a.BUG2428214)
| get_mandatory_components was fixed to
| handle arrival sets. In case of Arrival Sets
| requested_arrival_date is populated instead of
| requested_ship_date.
|
| 08-29-2002 Kundan Sarkar Bugfix 2458338 ( Base bug 2395525 ).
| Not copying sales and Mktg category set
| from base model to config item.
|
| 08-29-2002 Kundan Sarkar Bugfix 2454401 ( Base bug 2425667 ).
| Infinite Loops in load_mandatory_comps and
| load_mandatory_components during splitting of
| an ATO model within a PTO with Tools - Debug
| set to off.
|
| 08-29-2002 Kundan Sarkar Bugfix 2541088 ( Base bug 2457514 ).
| Config item description picks up model desc.
| of base language instead of base and installed
| languages.
|
| 09-05-2002 Kundan Sarkar Bugfix 2547219 ( Base bug 2461574 ).
| Configured items are not inheriting transaction
| defaults subinventory location from the model.
|
| 09-25-2002 Kundan Sarkar Bugfix 2587307 ( Base bug 2576422 ).
| Configured item weights are incorrect for MLMO
| structure.
|
| 11-27-2002 Kundan Sarkar Bugfix 2503104
| Passing model's user_item_description to config
| item .
|
| 12-09-2002 Kundan Sarkar Bugfix 2701338 ( Base bug 2652379 )
| Incorrect numbering sequence of configured items
| in a multi - level structure.
|
| 12-26-2002 Kiran Konada bugfix 2727983
| web_status filed is being copied for configuration item
| from its base model.This field is a mandatory one for
inventory from 11.5.9 onwards
|
|
| 12-31-2002 Kiran Konada bugfix2730055
| insert into MTL_ITEM_REVISIONS was changed to MTL_ITEM_REVISIONS_B
|
|
| 01-23-2003 Kundan Sarkar Bugfix 2503104
| Revert fix as OM will populate user_item_description
| of config item
| Bugfix 2764811 ( In branch 2745590 )
| Remove reference on flow schedule when config item
| is delinked.
|
| 02-03-2003 Kundan Sarkar Bugfix 2781022 ( In branch 2663450 )
| New custom hook to generate custom item numbers for
| pre configured items and autocreated configurations.
|
| Bugfix 2784045 ( no bug logged for main ...
| Propagate this fix to main with fix of 2781022 )
| Error in delink configuration when no routing is
| defined for the model.
|
|
| 02/04/2003 Kiran Konada
| Added a new paramter to pass conifg/ato item id
| to start_work_flow
| bugfix 2782394
|
| Modified on 14-FEB-2003 By Kundan Sarkar
| Bugfix 2804321 : Propagating customer bugfix 2774570
| and 2786934 to main.
|
| Modified on 24-MAR-2003 By Kundan Sarkar
| Bugfix 2867676 : Propagating customer bugfix 2858080
| to main.
|
| Modified on 14-APR-2003 By Kundan Sarkar
| Bugfix 2904203 : Propagating customer bugfix 2898851
| to main.
|
| 08-JUL-2003 Sushant Sawant Replicated Bugfix 2897132
| (a.k.a. 2913695[actual aru]) to J Main as 3037613
| Fixed bug related to get_mandatory_components
| Following scenarios were addressed
| 1) pass correct line id for mandatory components
| 2) handle cancel case properly
| 3) handle delete case properly (respect params)
| 4) handle reschedule case properly
|
|
| Modified on 14-MAR-2003 By Sushant Sawant
| Decimal-Qty Support for Option Items.
| Replicated 3037613 for mandatory
| components
| ( a.k.a 2913695, 2897132)
|
| 17-JUL-2003 Sushant Sawant Replicated Bugfix 2483920
| using 3056491
|
| 30-DEC-2003 Kiran Konada Bugfix
| 3340844
| inserted value into revision_label
| of mtl_item_revisions_b
|
| 3338108
| a) In 11.5.9 in the dynamic sql part
| of inserting into mtl_item_revisions_b
| we were inserting same revision_id for
| an item in all orgs. Revision_id was a
| null column
|
| As items team has decide to create a
| unique index on it, the revision_id needs
| to be different in each org
|
| b) where condition for insert into mtl_item_revisions_tl
| is also changed to get the corresponding revisionId
| info from table mtl_item_revisions_b table
|
| c) fixed by sawant
| it is decided to leave blank the uom_code and revision_id
| in mtl_cross_references
|
| 30-DEC-2003 Sushant Sawant Fixed Bug# 3358194
| Weight/Vol calculations were not updated if model has no base uom.
|
|
| ssawant 15-JAN-04 Bugfix 3379296
| changes evaluate_atp_attributes for model attribute Y, N scenario.
|
| KKONADA 02-03-2004 bugfix 2828588
| Inserted the configuration data into table MTL_ABC_ASSIGNMENTS
|
|
|
| KKONADA 03-02-2004 front port bugfix#3473737
|
| branch Bugfix 3463999 : Updating config_id in bcol to null
| if config is matched.
|
|
| KKONADA 03-15-2004 Corrected the comments
| bug#3340844 was entered for 3338108 at few places , corrected
| the error
|
|
| Kkkonada 03-26-2004 Kiran Konada
| Bugfix 3536085
| assignment_group_id and inventory_item_id should be unique
|
|
| 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 on 23-Jun-2006 by Kiran Konada
| Revreted bugfix 3473737, branch fix 3463999
|
| modified on 01-jul-2004 kiran konada
| aru 3737772 (for FP 3473737)
| added pconfigid parameter to ATO_WEIGHT_VOL api
| and changed the de_code statment in wt_vol api
|
|
|
| Modified on 18-APR-2005 By Sushant Sawant
| Fixed Bug#4172300
| Cost in validation org is not copied properly from model to config item.
|
| Modified on 05-Jul-2005 by Renga Kannan
| Change for MOAC
| As per OM
s recommendation changing the process order Public API call to Group API call
for Link item
|
|
| Modified on 08-Aug-2005 Kiran Konada
| bug# 4539578
| In R12, mtl_cross_references datamodel has been changed to
| mtl_cross_references_b and mtl_cross_references_tl
|
| Modified on 15-Sep-2005 Renga Kannan
| Made Code changes for ATG Performance
| Project
|
| Modified on 03-feb-2006 Kiran Konada
| bugfix FP 4861996
| should check if a specific item is present
| in specificy category set
|
| 07-Mar-2006 Kiran Konada
| performance bug#4905845
| Removed comments to reduce shared memory
|
+-----------------------------------------------------------------------------*/
PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
for using in insert to cst_quantity_layers
+---------------------------------------------*/
select nvl(costing_group_id,1)
into l_cst_grp
from pjm_project_parameters ppp
where ppp.project_id = ( select project_id
from oe_order_lines_all ol
where ol.line_id = pLineId )
and ppp.organization_id = pOrgId;
select distinct POV.profile_option_value
from fnd_profile_options PO ,
fnd_profile_option_values POV,
fnd_responsibility FR,
fnd_profile_options PO2,
fnd_profile_option_values POV2
where PO.profile_option_name = 'SO_ORGANIZATION_ID'
and POV.application_id = PO.application_id
and POV.profile_option_id = PO.profile_option_id
and POV.level_id = 10003
and FR.application_id = POV.level_value_application_id
and FR.responsibility_id = POV.level_value
and PO2.profile_option_name = 'ORG_ID'
and POV2.application_id = PO2.application_id
and POV2.profile_option_id = PO2.profile_option_id
and POV2.level_id = 10003
and POV2.profile_option_value = to_char(opunit)
and POV2.level_value_Application_id = 660 -- ONT
and FR.application_id = POV2.level_value_application_id
and FR.responsibility_id = POV2.level_value;
select wip_entity_id
into lWipEntityId
from wip_flow_schedules wfs,
oe_order_lines_all oel,
oe_order_headers_all oeh,
oe_transaction_types_all ota,
oe_transaction_types_tl otl,
mtl_sales_orders mso
where wfs.demand_source_line = oel.line_id --config line id
and oel.line_id = pLineId
and oeh.header_id = oel.header_id
and oeh.order_type_id = ota.transaction_type_id
and ota.transaction_type_code='ORDER'
and ota.transaction_type_id = otl.transaction_type_id
and oeh.order_number = mso.segment1
and otl.name = mso.segment2
and otl.language = (select language_code
from fnd_languages
where installed_flag = 'B')
and mso.sales_order_id = wfs.demand_source_header_id
and oel.inventory_item_id = wfs.primary_item_id
and rownum = 1;
select reservation_id
into lReserveId
from mtl_reservations mr,
oe_order_lines_all oel,
oe_order_headers_all oeh,
oe_transaction_types_all ota,
oe_transaction_types_tl otl,
mtl_sales_orders mso
where mr.demand_source_line_id = oel.line_id --config line id
and oel.line_id = pLineId
and oeh.header_id = oel.header_id
and oeh.order_type_id = ota.transaction_type_id
and ota.transaction_type_code = 'ORDER'
and ota.transaction_type_id = otl.transaction_type_id
and oeh.order_number = mso.segment1
and otl.name = mso.segment2
and otl.language = (select language_code
from fnd_languages
where installed_flag = 'B')
and mso.sales_order_id = mr.demand_source_header_id
and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10,
INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
INV_RESERVATION_GLOBAL.g_source_type_oe) --bugfix 1799874
and mr.reservation_quantity > 0
and supply_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_wip
and rownum = 1;
select distinct reservation_id
from mtl_reservations mr,
oe_order_lines_all oel,
oe_order_headers_all oeh,
oe_transaction_types_all ota,
oe_transaction_types_tl otl,
mtl_sales_orders mso
where mr.demand_source_line_id = oel.line_id --config line id
and oel.line_id = lConfigLineId
and oeh.header_id = oel.header_id
and oeh.order_type_id = ota.transaction_type_id
and ota.transaction_type_code='ORDER'
and ota.transaction_type_id = otl.transaction_type_id
and oeh.order_number = mso.segment1
and otl.name = mso.segment2
and otl.language = (select language_code
from fnd_languages
where installed_flag = 'B')
and mso.sales_order_id = mr.demand_source_header_id
and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10,
INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
INV_RESERVATION_GLOBAL.g_source_type_oe) --bugfix 1799874
and mr.reservation_quantity > 0;
select line_id
from oe_order_lines_all
where ato_line_id = pModelLineId;
select source_type_code into v_source_type_code
from oe_order_lines_all
where line_id = pModelLineId ;
select line_id, ship_from_org_id,header_id
into lConfigLineId, lOrgId,l_header_id
from oe_order_lines_all oel
where ato_line_id = pModelLineId
and inventory_item_id = pConfigid
and item_type_code = 'CONFIG';
select distinct 'TRUE'
into lShipConfirmed
from wsh_delivery_details_ob_grp_v wdd -- Added By Renga Kannan on 11/03/03 for wsh data model changes
where wdd.source_line_id = lConfigLineId
and wdd.source_code = 'OE'
and wdd.released_status in ('Y', 'C'); -- Staged [Y], Closed [C]
select mso.sales_order_id,
oel.ship_from_org_id,
oeh.header_id
into lSalesOrderId, --header id fro rsv api
lOrgId, --ship from org id
lHeaderId -- header id in oeh, for wip api
from oe_order_lines_all oel,
oe_order_headers_all oeh,
oe_transaction_types_tl oet,
mtl_sales_orders mso,
mtl_system_items msi
where oel.line_id = lConfigLineId
and item_type_code = 'CONFIG'
and oeh.header_id = oel.header_id
and oet.transaction_type_id = oeh.order_type_id
and mso.segment1 = to_char(oeh.order_number)
and mso.segment2 = oet.name
and oet.language = (select language_code
from fnd_languages
where installed_flag = 'B')
and mso.segment3 = lSourceCode
and oel.inventory_item_id = pConfigId
and msi.inventory_item_id = oel.inventory_item_id
and msi.organization_id = oel.ship_from_org_id
and msi.base_item_id is not NULL;
-- 2784045 : Select stmt is enclosed in block to handle exception
begin
select nvl(cfm_routing_flag, 2)
into lcfm_routing_flag
from bom_operational_routings
where assembly_item_id = pConfigid
and organization_id = lOrgId
and alternate_routing_designator is NULL;
select count(*)
into lflow_schedule_exist
from wip_flow_schedules
where demand_source_line = to_char(lConfigLineId)
and primary_item_id = pConfigid
and demand_source_type = 2 ;
-- call INV delete_reservations API
INV_RESERVATION_PUB.delete_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_rsv
, p_serial_number => l_dummy_sn
);
oe_debug_pub.add ('delink_item: ' || 'reservations deleted', 2);
oe_debug_pub.add ('delink_item: ' || 'Error in rsv delete', 1);
oe_debug_pub.add ('delink_item: ' || 'Error in rsv delete', 1);
cto_change_order_pk.delete_from_req_interface(
p_line_id => lConfigLineId,
p_item_id => pConfigId,
x_return_status => l_return_status ) ;
SELECT nvl(cancelled_flag,'N')
INTO lcancel_flag --- Reusing the variable
FROM OE_ORDER_LINES_ALL
WHERE line_id = pModelLineId;
select activity_status
into lWfStat
from wf_item_activity_statuses was
where was.item_type = 'OEOL'
and was.item_key = to_char(pModelLineId)
and was.activity_status = 'NOTIFIED'
and was.process_activity in
(SELECT wpa.instance_id
FROM wf_process_activities wpa
WHERE wpa.activity_name = 'WAIT_FOR_CTO');
function and a delete line request from form and
over-ride or honor security constraints accordingly.
That can be done by the private api but not with
the public process order api
+------------------------------------------------------*/
lstmtNumber := 523;
-- update visible demand flag to 'Y' for model and options
--
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add ('delink_item: ' || 'delink : visible demand flag : before selecting options ', 2);
-- We should not update visible demand flag to 'Y' for
-- unscheduled orders. Added a condition to check
-- if the line is scheduled or not
UPDATE OE_ORDER_LINES_ALL
SET visible_demand_flag = 'Y'
WHERE ato_line_id = pModelLineId
and header_id = l_header_id
and open_flag = 'Y'
and schedule_status_code is not null; -- 5470466
delete from bom_cto_order_demand
where ato_line_id=pModelLineId
and inventory_item_id=pConfigId;
update bom_cto_order_demand
set demand_visible = 'Y'
where ato_line_id =pModelLineId;
SELECT Order_number
INTO l_order_number
FROM OE_ORDER_HEADERS_ALL A,
OE_ORDER_LINES_ALL B
WHERE B.line_id = pModelLineId
AND A.Header_id = B.Header_Id;
/* Since oe_config_util.delink_config deleted lConfigLineId from oe_order_lines_all before
opening delink_lines cursor , lConfigLineId needs to be passed explicitely */
p_config_line_arr_delink(index_counter_delink) := lConfigLineId;
select bet.top_bill_sequence_id,
bet.bill_sequence_id,
bet.organization_id,
bet.sort_order,
bet.plan_level,
bet.line_id,
substrb(msi.concatenated_segments,1,50)
from bom_explosion_temp bet,mtl_system_items_kfv msi
where bet.group_id = xGrpId
and bet.organization_id = msi.organization_id
and bet.component_item_id = msi.inventory_item_id;
Insert the Model row details from oe_order_lines
with plan_level =0
The lines have a common group_id, have line_id reference
of oe_order_lines. Top_bill_sequence_id and bill_sequence_id
are not needed however, as they are not null coloumns,
all rows have model's bill_sequence_id in these fields.
+----------------------------------------------------*/
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add ('load_mandatory_comps: ' || ' load mandatory comps ' ,1);
select NVL(msi.atp_components_flag,'N')
into latpcompflag
from mtl_system_items msi , oe_order_lines_all oel
where oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id
and oel.line_id = pLineId
and oel.ordered_quantity > 0;
select bom_explosion_temp_s.nextval
into xGrpId
from dual;
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_sequence_id,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code, -- 1998386
top_item_id,
line_id,
group_id)
select
nvl(oel.component_sequence_id,1), -- Top bill sequence id --1902818
nvl(oel.component_sequence_id,1), -- Bill_sequence_id --1902818
oel.ship_from_org_id,
2, -- Sort Order --BUG no 1288823 modification
nvl(oel.component_sequence_id,1), --1902818
oel.inventory_item_id,
0, -- Plan level
-- 1998386
CTO_UTILITY_PK.convert_uom(
oel.order_quantity_uom,
msi.primary_uom_code,
oel.ordered_quantity,
oel.inventory_item_id),
msi.primary_uom_code,
oel.inventory_item_id,
oel.Line_Id,
xGrpId
from
oe_order_lines_all oel,
mtl_system_items msi
where oel.line_id = pLineId
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id
and oel.ordered_quantity > 0;
Insert all oe_order_lines row for this model
with corrosponding plan_levels
+------------------------------------------------*/
rowcount := 1 ;
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_sequence_id,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code, -- Bugfix 1998386
top_item_id,
line_id,
group_id)
-- 3893281 : Commenting Old SELECT
/*
select
bet.top_bill_sequence_id,
bet.bill_sequence_id,
oel.ship_from_org_id, --changed from org_id,
2, -- BUG no 1288823 modification
oel.component_sequence_id,
oel.inventory_item_id,
level_number,
-- Bugfix 1998386 This function multiplies the ordered quantity
-- with conversion factor and returns converted quantity
-- if the order UOM is different from primary UOM of the item .
-- If ordered UOM and primary UOM are same,the function
-- returns the ordered quantity.
CTO_UTILITY_PK.convert_uom(
oel.order_quantity_uom,
msi.primary_uom_code,
oel.ordered_quantity,
oel.inventory_item_id),
msi.primary_uom_code,
bet.top_item_id,
oel.line_id,
xGrpId
from
oe_order_lines_all oel,
bom_explosion_temp bet,
mtl_system_items msi
where oel.ato_line_id = pLineId
and oel.line_id <> pLineId
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id
and oel.ordered_quantity > 0
and nvl(oel.cancelled_flag,'N') <> 'Y'
and oel.link_to_line_id = bet.line_id
and oel.item_type_code <> 'CONFIG'
and bet.group_id = xGrpId
and bet.plan_level = level_number -1 ;
select
bet.top_bill_sequence_id,
bet.bill_sequence_id,
oel.ship_from_org_id, --changed from org_id,
2, -- BUG no 1288823 modification
oel.component_sequence_id,
oel.inventory_item_id,
level_number,
-- Bugfix 1998386 This function multiplies the ordered quantity
-- with conversion factor and returns converted quantity
-- if the order UOM is different from primary UOM of the item .
-- If ordered UOM and primary UOM are same,the function
-- returns the ordered quantity.
CTO_UTILITY_PK.convert_uom(
oel.order_quantity_uom,
msi.primary_uom_code,
oel.ordered_quantity,
oel.inventory_item_id),
msi.primary_uom_code,
bet.top_item_id,
oel.line_id,
xGrpId
from
oe_order_lines_all oel,
bom_explosion_temp bet,
mtl_system_items msi,
bom_bill_of_materials bbm,
bom_inventory_components bic
where oel.ato_line_id = pLineId
and oel.line_id <> pLineId
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id
and oel.ordered_quantity > 0
and nvl(oel.cancelled_flag,'N') <> 'Y'
and oel.link_to_line_id = bet.line_id
and oel.item_type_code <> 'CONFIG' /* BUG 2483920 */
and bet.group_id = xGrpId
and bet.plan_level = level_number -1
and bet.component_item_id = bbm.assembly_item_id
and bet.organization_id = bbm.organization_id
and bbm.alternate_bom_designator is null
and bbm.common_bill_sequence_id = bic.bill_sequence_id
and bic.component_item_id = msi.inventory_item_id
and bic.optional = 1
and ( msi.bom_item_type in (1,2)
OR (msi.bom_item_type = 4 and bic.wip_supply_type = 6 )
OR (msi.bom_item_type = 4 and msi.replenish_to_order_flag = 'Y' ));
oe_debug_pub.add ('BET picture after Model and its children are inserted ..' ,1);
select msi.fixed_lead_time , nvl(msi.variable_lead_time,0)
into v_msi_fixed_lead_time, v_msi_variable_lead_time
from mtl_system_items msi , bom_explosion_temp be
where be.organization_id = msi.organization_id
and be.component_item_id = msi.inventory_item_id
and be.line_id = pLineId
and be.group_id = xGrpId; -- bugfix 1876997
-- Bug 1985793 Selecting greater of sysdate and calendar date
-- while checking for item effectivity so that planning will get
-- components effective till sysdate.
--apps performance bugfix 4905845, sql id 16103327
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_sequence_id,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code, -- 1998386
top_item_id,
component_quantity,
check_atp,
atp_components_flag,
atp_flag,
bom_item_type,
assembly_item_id,
parent_bom_item_type,
line_id,
wip_supply_type, -- 3254039
group_id)
select
-1 , -- 2897132
bic.bill_sequence_id,
be.organization_id,
evaluate_order( msi2.atp_flag, msi2.atp_components_flag , msi2.bom_item_type ) , /* BUG # 1518894, 1288823 */
-- 1288823
bic.component_sequence_id,
bic.component_item_id,
level_number + 1,
be.extended_quantity * bic.component_quantity,
msi2.primary_uom_code, -- 1998386
be.top_item_id,
bic.component_quantity,
bic.check_atp,
-- 2378556
msi2.atp_components_flag,
msi2.atp_flag,
msi2.bom_item_type,
bom.assembly_item_id,
msi.bom_item_type,
be.line_id , -- 2897132
nvl(bic.wip_supply_type, msi2.wip_supply_type), -- 3254039 , 3298244
xGrpId
from
bom_calendar_dates cal,
mtl_system_items msi, /* PARENT */
mtl_system_items msi2, /* CHILD */
bom_inventory_components bic,
eng_revised_items eri,
bom_bill_of_materials bom,
mtl_parameters mp,
bom_explosion_temp be
where be.sort_order <> 3 -- 1288823
and be.group_id = xGrpId
and nvl(be.plan_level,0) = level_number
and be.organization_id = bom.organization_id
and be.component_item_id = bom.assembly_item_id
and bic.component_quantity <> 0
and bic.revised_item_sequence_id = eri.revised_item_sequence_id (+)
and bic.component_item_id = msi2.inventory_item_id -- 1518894
and bom.organization_id = msi2.organization_id -- 1518894
and bom.alternate_bom_designator is null
and bic.bill_sequence_id = bom.common_bill_sequence_id
and be.organization_id = msi.organization_id
and be.component_item_id = msi.inventory_item_id --BUG#2378556
and mp.organization_id = be.organization_id
and cal.calendar_code = mp.calendar_code
and cal.exception_set_id = mp.calendar_exception_set_id
and cal.calendar_date =
( select c.calendar_date
from bom_calendar_dates C
where C.calendar_code = mp.calendar_code
and c.exception_set_id = mp.calendar_exception_set_id
and C.seq_num =
(select c2.prior_seq_num -
ceil( nvl( v_msi_fixed_lead_time,0)+
(be.extended_quantity *
v_msi_variable_lead_time ))
from bom_calendar_dates c2
where c2.calendar_code = mp.calendar_code
and c2.exception_set_id = mp.calendar_exception_set_id
and c2.calendar_date = trunc(pReqDate)
)
)
-- 2162912
and TRUNC(bic.effectivity_date) <= greatest(nvl(cal.calendar_date,sysdate),sysdate)
and nvl(TRUNC(bic.disable_date),(nvl(cal.calendar_date,sysdate) + 1)) > nvl(cal.calendar_date,sysdate)
and bic.effectivity_date =
(select
max(effectivity_date)
from bom_inventory_components bic1,
eng_revised_items eri
where bic1.bill_sequence_id = bic.bill_sequence_id
and bic1.component_item_id = bic.component_item_id
and bic1.revised_item_sequence_id =
eri.revised_item_sequence_id (+)
and (decode(bic1.implementation_date, NULL,
bic1.old_component_sequence_id,
bic1.component_sequence_id) =
decode(bic.implementation_date, NULL,
bic.old_component_sequence_id,
bic.component_sequence_id)
OR
bic1.operation_seq_num = bic.operation_seq_num)
-- 2162912
and TRUNC(bic1.effectivity_date) <= greatest(nvl(cal.calendar_date,sysdate),sysdate)
and nvl(TRUNC(bic1.disable_date),(nvl(cal.calendar_date,sysdate) + 1)) > nvl(cal.calendar_date,sysdate)
-- 2162912
and ( nvl(eri.status_type,6) IN (4,6,7))
and not exists
(select
'X'
from bom_inventory_components bicn, eng_revised_items eri1
where bicn.bill_sequence_id + 0 = bic.bill_sequence_id
and bicn.old_component_sequence_id =
bic.component_sequence_id
and bicn.acd_type in (2,3)
and eri1.revised_item_sequence_id = bicn.revised_item_sequence_id
and trunc(bicn.disable_date) <= cal.calendar_date
and ( nvl(eri1.status_type,6) in (4,6,7))
)
)
and bic.optional = 2 /* NOT OPTIONAL */
and msi2.bom_item_type = 4 /* 2400948 */
-- Model or Option Class or ATO ITEM * * BUG#2378556 commented for bug 3314297 mandatory comps should be
-- exploded for standard items
and msi.pick_components_flag <> 'Y' ;
Insert the Model row details from oe_order_lines
with plan_level =0
The lines have a common group_id, have line_id reference
of oe_order_lines. Top_bill_sequence_id and bill_sequence_id
are not needed however, as they are not null coloumns,
all rows have model's bill_sequence_id in these fields.
+----------------------------------------------------*/
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add ('load_mandatory_comps_pds: ' || ' load mandatory comps PDS ' ,1);
select NVL(msi.atp_components_flag,'N')
into latpcompflag
from mtl_system_items msi , oe_order_lines_all oel
where oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id
and oel.line_id = pLineId
and oel.ordered_quantity > 0;
select bom_explosion_temp_s.nextval
into xGrpId
from dual;
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_sequence_id,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code,
top_item_id,
line_id,
group_id)
select
nvl(oel.component_sequence_id,1),
nvl(oel.component_sequence_id,1),
oel.ship_from_org_id,
2,
nvl(oel.component_sequence_id,1),
oel.inventory_item_id,
0,
CTO_UTILITY_PK.convert_uom(
oel.order_quantity_uom,
msi.primary_uom_code,
oel.ordered_quantity,
oel.inventory_item_id),
msi.primary_uom_code,
oel.inventory_item_id,
oel.Line_Id,
xGrpId
from
oe_order_lines_all oel,
mtl_system_items msi
where oel.line_id = pLineId
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id
and oel.ordered_quantity > 0;
Insert all oe_order_lines row for this model
with corrosponding plan_levels
+------------------------------------------------*/
rowcount := 1 ;
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_sequence_id,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code,
top_item_id,
line_id,
group_id)
select
bet.top_bill_sequence_id,
bet.bill_sequence_id,
oel.ship_from_org_id,
2,
oel.component_sequence_id,
oel.inventory_item_id,
level_number,
CTO_UTILITY_PK.convert_uom(
oel.order_quantity_uom,
msi.primary_uom_code,
oel.ordered_quantity,
oel.inventory_item_id),
msi.primary_uom_code,
bet.top_item_id,
oel.line_id,
xGrpId
from
oe_order_lines_all oel,
bom_explosion_temp bet,
mtl_system_items msi
where oel.ato_line_id = pLineId
and oel.line_id <> pLineId
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id
and msi.bom_item_type in ( 1, 2 ) -- only sub-models and option classes
and oel.ordered_quantity > 0
and nvl(oel.cancelled_flag,'N') <> 'Y'
and oel.link_to_line_id = bet.line_id
and oel.item_type_code <> 'CONFIG'
and bet.group_id = xGrpId
and bet.plan_level = level_number -1 ;
select msi.fixed_lead_time , nvl(msi.variable_lead_time,0)
into v_msi_fixed_lead_time, v_msi_variable_lead_time
from mtl_system_items msi , bom_explosion_temp be
where be.organization_id = msi.organization_id
and be.component_item_id = msi.inventory_item_id
and be.line_id = pLineId
and be.group_id = xGrpId;
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_sequence_id,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code,
top_item_id,
component_quantity,
check_atp,
atp_components_flag,
atp_flag,
bom_item_type,
assembly_item_id,
parent_bom_item_type,
line_id,
wip_supply_type,
group_id)
select
-1 ,
bic.bill_sequence_id,
be.organization_id,
evaluate_order( msi2.atp_flag, msi2.atp_components_flag , msi2.bom_item_type ) ,
bic.component_sequence_id,
bic.component_item_id,
level_number + 1,
be.extended_quantity * bic.component_quantity,
msi2.primary_uom_code,
be.top_item_id,
bic.component_quantity,
bic.check_atp,
msi2.atp_components_flag,
msi2.atp_flag,
msi2.bom_item_type,
bom.assembly_item_id,
msi.bom_item_type,
be.line_id ,
nvl(bic.wip_supply_type, msi2.wip_supply_type),
xGrpId
from
bom_calendar_dates cal,
mtl_system_items msi,
mtl_system_items msi2,
bom_inventory_components bic,
eng_revised_items eri,
bom_bill_of_materials bom,
mtl_parameters mp,
bom_explosion_temp be
where be.sort_order <> 3
and be.group_id = xGrpId
and nvl(be.plan_level,0) = level_number
and be.organization_id = bom.organization_id
and be.component_item_id = bom.assembly_item_id
and bic.component_quantity <> 0
and bic.revised_item_sequence_id = eri.revised_item_sequence_id (+)
and bic.component_item_id = msi2.inventory_item_id
and bom.organization_id = msi2.organization_id
and bom.alternate_bom_designator is null
and bic.bill_sequence_id = bom.common_bill_sequence_id
and be.organization_id = msi.organization_id
and be.component_item_id = msi.inventory_item_id
and mp.organization_id = be.organization_id
and cal.calendar_code = mp.calendar_code
and cal.exception_set_id = mp.calendar_exception_set_id
and cal.calendar_date =
( select c.calendar_date
from bom_calendar_dates C
where C.calendar_code = mp.calendar_code
and c.exception_set_id = mp.calendar_exception_set_id
and C.seq_num =
(select c2.prior_seq_num -
ceil( nvl( v_msi_fixed_lead_time,0)+
(be.extended_quantity *
v_msi_variable_lead_time ))
from bom_calendar_dates c2
where c2.calendar_code = mp.calendar_code
and c2.exception_set_id = mp.calendar_exception_set_id
and c2.calendar_date = trunc(pReqDate)
)
)
and TRUNC(bic.effectivity_date) <= greatest(nvl(cal.calendar_date,sysdate),sysdate)
and nvl(TRUNC(bic.disable_date),(nvl(cal.calendar_date,sysdate) + 1)) > nvl(cal.calendar_date,sysdate)
and bic.effectivity_date =
(select
max(effectivity_date)
from bom_inventory_components bic1,
eng_revised_items eri
where bic1.bill_sequence_id = bic.bill_sequence_id
and bic1.component_item_id = bic.component_item_id
and bic1.revised_item_sequence_id =
eri.revised_item_sequence_id (+)
and (decode(bic1.implementation_date, NULL,
bic1.old_component_sequence_id,
bic1.component_sequence_id) =
decode(bic.implementation_date, NULL,
bic.old_component_sequence_id,
bic.component_sequence_id)
OR
bic1.operation_seq_num = bic.operation_seq_num)
and TRUNC(bic1.effectivity_date) <= greatest(nvl(cal.calendar_date,sysdate),sysdate)
and nvl(TRUNC(bic1.disable_date),(nvl(cal.calendar_date,sysdate) + 1)) > nvl(cal.calendar_date,sysdate)
and ( nvl(eri.status_type,6) IN (4,6,7))
and not exists
(select
'X'
from bom_inventory_components bicn, eng_revised_items eri1
where bicn.bill_sequence_id + 0 = bic.bill_sequence_id
and bicn.old_component_sequence_id =
bic.component_sequence_id
and bicn.acd_type in (2,3)
and eri1.revised_item_sequence_id = bicn.revised_item_sequence_id
and trunc(bicn.disable_date) <= cal.calendar_date
and ( nvl(eri1.status_type,6) in (4,6,7))
)
)
and bic.optional = 2
and msi2.bom_item_type = 4
and msi.bom_item_type in (1,2) /*Model or Option Class */
and msi.pick_components_flag <> 'Y' ;
select bet.top_bill_sequence_id,
bet.bill_sequence_id,
bet.organization_id,
bet.sort_order,
bet.plan_level,
bet.line_id,
substrb(msi.concatenated_segments,1,50)
from bom_explosion_temp bet,mtl_system_items_kfv msi
where bet.group_id = xGrpId
and bet.organization_id = msi.organization_id
and bet.component_item_id = msi.inventory_item_id;
select NVL(msi.atp_components_flag,'N')
into latpcompflag
from mtl_system_items msi
where msi.inventory_item_id = p_ship_set.inventory_item_id(p_model_index)
and msi.organization_id = p_ship_set.source_organization_id(p_model_index);
select bom_explosion_temp_s.nextval
into lGroupid2
from dual;
select bom_explosion_temp_s.nextval
into xGrpId
from dual;
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code, -- Bugfix 1998386
sort_order,
group_id)
-- Bugfix 1998386 Change this to select statement to select
-- primary_uom_code from mtl_system_items.
-- Also CONVERT_UOM function is used to convert
-- p_ship_set.quantity_ordered(i) if the ordered UOM
-- is different from primary UOM.
/*values (
1,
1,
p_ship_set.source_organization_id(i),
p_ship_set.inventory_item_id(i),
1,
p_ship_set.quantity_ordered(i),
2, -- BUG no 1288823 modification
lGroupid2);*/
select
1,
1,
p_ship_set.source_organization_id(i),
p_ship_set.inventory_item_id(i),
1,
CTO_UTILITY_PK.convert_uom(
p_ship_set.quantity_uom(i),
msi.primary_uom_code,
p_ship_set.quantity_ordered(i),
p_ship_set.inventory_item_id(i)),
msi.primary_uom_code,
2, -- BUG no 1288823 modification
lGroupid2
from mtl_system_items msi
where msi.inventory_item_id = p_ship_set.inventory_item_id(i)
and msi.organization_id = p_ship_set.source_organization_id(i);
-- insert the top model into bom_explosion_temp
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code, -- Bugfix 1998386
top_item_id,
line_id,
group_id)
-- Bugfix 1998386 Change this to select statement to
-- select primary_uom_code from mtl_system_items.
-- Also CONVERT_UOM function is used to convert
-- p_ship_set.quantity_ordered(i) if the ordered UOM is
-- different from primary UOM.
select
1,
1,
p_ship_set.source_organization_id(p_model_index),
2, -- BUG no 1288823 modification
p_ship_set.inventory_item_id(p_model_index),
0,
CTO_UTILITY_PK.convert_uom(
p_ship_set.quantity_uom(p_model_index),
msi.primary_uom_code,
p_ship_set.quantity_ordered(p_model_index),
p_ship_set.inventory_item_id(p_model_index)),
msi.primary_uom_code,
p_ship_set.inventory_item_id(p_model_index),
1,
xGrpId
from mtl_system_items msi
where msi.inventory_item_id = p_ship_set.inventory_item_id(p_model_index)
and msi.organization_id = p_ship_set.source_organization_id(p_model_index);
Insert all selections for the top model
with corrosponding plan_levels
+------------------------------------------------*/
rowcount := 1 ;
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code, -- Bugfix 1998386
top_item_id,
line_id,
group_id)
select
1,
1,
bet.organization_id,
2, -- BUG no 1288823 modification
bic.component_item_id,
level_number,
/* bet2.extended_quantity,*/
-- Bugfix 1998386 This function multiplies the ordered quantity
-- with conversion factor and returns the converted
-- quantity if the order UOM is different from primary UOM of the item.
-- If ordered UOM and primary UOM are same
-- the function returns the ordered quantity.
CTO_UTILITY_PK.convert_uom(
bet2.primary_uom_code,
msi.primary_uom_code,
bet2.extended_quantity,
bic.component_item_id),
msi.primary_uom_code,
bet.top_item_id,
1,
xGrpId
from
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items msi, -- bugfix 1998386
bom_explosion_temp bet,
bom_explosion_temp bet2 /* ship set */
where bet.group_id = xGrpId
and bet.plan_level = level_number -1
and bic.component_item_id = msi.inventory_item_id -- bugfix 1998386
and bet.component_item_id = bom.assembly_item_id
and bet.organization_id = bom.organization_id
and bet.organization_id = msi.organization_id -- bugfix 1998386
and bom.alternate_bom_designator is null
and bom.common_bill_sequence_id = bic.bill_sequence_id
and bic.component_item_id = bet2.component_item_id
and bet2.group_id = lGroupid2
-- bugfix 3893281 : Add following filter conditions to get
-- sub models , option classes , ato items and phantom option items
and bic.optional = 1
and ( msi.bom_item_type in (1,2)
OR (msi.bom_item_type = 4 and bic.wip_supply_type = 6 )
OR (msi.bom_item_type = 4 and msi.replenish_to_order_flag = 'Y' ));
oe_debug_pub.add ('BET picture after Model and its children are inserted ..' ,1);
delete from bom_explosion_temp
where group_id = lGroupid2;
select msi.fixed_lead_time , nvl(msi.variable_lead_time,0)
into v_msi_fixed_lead_time, v_msi_variable_lead_time
from mtl_system_items msi , bom_explosion_temp be
where be.organization_id = msi.organization_id
and be.component_item_id = msi.inventory_item_id
and be.component_item_id = p_ship_set.inventory_item_id(p_model_index);
-- Bug 1985793 Selecting greater of sysdate and calendar date
-- while checking for item effectivity so that planning will get
-- components effective till sysdate.
--apps performance bug#4905845, sql id 16103671
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_sequence_id,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code, -- 1998386
top_item_id,
component_quantity,
check_atp,
atp_components_flag,
atp_flag,
bom_item_type,
assembly_item_id,
parent_bom_item_type,
line_id,
group_id)
select
be.top_bill_sequence_id,
bic.bill_sequence_id,
be.organization_id,
evaluate_order( msi2.atp_flag, msi2.atp_components_flag , msi2.bom_item_type ) , /* BUG# 1518894 , 1288823 */
bic.component_sequence_id,
bic.component_item_id,
level_number + 1,
be.extended_quantity * bic.component_quantity,
msi2.primary_uom_code,
be.top_item_id,
bic.component_quantity,
bic.check_atp,
--2378556
msi2.atp_components_flag,
msi2.atp_flag,
msi2.bom_item_type,
bom.assembly_item_id,
msi.bom_item_type,
NULL,
xGrpId
from
bom_calendar_dates cal,
mtl_system_items msi, /* PARENT */
mtl_system_items msi2, /* CHILD [BUG#1518894] */
bom_inventory_components bic,
eng_revised_items eri,
bom_bill_of_materials bom,
mtl_parameters mp,
bom_explosion_temp be
where be.sort_order <> 3
and be.group_id = xGrpId
and nvl(be.plan_level,0) = level_number
and be.organization_id = bom.organization_id
and be.component_item_id = bom.assembly_item_id
and bic.component_quantity <> 0
and bic.revised_item_sequence_id = eri.revised_item_sequence_id (+)
and bic.component_item_id = msi2.inventory_item_id -- 1518894
and bom.organization_id = msi2.organization_id --1518894
and be.organization_id = msi.organization_id
and be.component_item_id = msi.inventory_item_id
and bom.alternate_bom_designator is null
and bic.bill_sequence_id = bom.common_bill_sequence_id
and mp.organization_id = be.organization_id
and cal.calendar_code = mp.calendar_code
and cal.exception_set_id = mp.calendar_exception_set_id
and cal.calendar_date =
( select c.calendar_date
from bom_calendar_dates C
where C.calendar_code = mp.calendar_code
and c.exception_set_id = mp.calendar_exception_set_id
and C.seq_num =
(select c2.prior_seq_num -
ceil(nvl(v_msi_fixed_lead_time,0)+
(be.extended_quantity *
nvl(v_msi_variable_lead_time,0)))
from bom_calendar_dates c2
where c2.calendar_code = mp.calendar_code
and c2.exception_set_id = mp.calendar_exception_set_id
and c2.calendar_date = trunc(pReqDate)
)
)
-- 2162912
and TRUNC(bic.effectivity_date) <= greatest(nvl(cal.calendar_date,sysdate),sysdate)
and nvl(TRUNC(bic.disable_date),(nvl(cal.calendar_date,sysdate) + 1)) > nvl(cal.calendar_date,sysdate)
and bic.effectivity_date =
(select
max(effectivity_date)
from bom_inventory_components bic1,
eng_revised_items eri
where bic1.bill_sequence_id = bic.bill_sequence_id
and bic1.component_item_id = bic.component_item_id
and bic1.revised_item_sequence_id =
eri.revised_item_sequence_id (+)
and (decode(bic1.implementation_date, NULL,
bic1.old_component_sequence_id,
bic1.component_sequence_id) =
decode(bic.implementation_date, NULL,
bic.old_component_sequence_id,
bic.component_sequence_id)
OR
bic1.operation_seq_num = bic.operation_seq_num)
-- 2162912
and TRUNC(bic1.effectivity_date) <= greatest(nvl(cal.calendar_date,sysdate),sysdate)
and nvl(TRUNC(bic1.disable_date),(nvl(cal.calendar_date,sysdate) + 1)) > nvl(cal.calendar_date,sysdate)
and ( nvl(eri.status_type,6) IN (4,6,7))
and not exists
(select
'X'
from bom_inventory_components bicn, eng_revised_items eri1
where bicn.bill_sequence_id + 0 = bic.bill_sequence_id
and bicn.old_component_sequence_id =
bic.component_sequence_id
and bicn.acd_type in (2,3)
and eri1.revised_item_sequence_id =
bicn.revised_item_sequence_id
and trunc(bicn.disable_date) <= cal.calendar_date
and ( nvl(eri1.status_type,6) in (4,6,7))
)
)
and bic.optional = 2 /* NOT OPTIONAL */
and msi2.bom_item_type = 4 /* BUGFIX 2400948 */
-- Model or Option Class or ATO ITEM * * BUG#2378556 bug 3314297 mandatory comps should be exploded for
-- standard items
and msi.pick_components_flag <> 'Y' ;
select NVL(msi.atp_components_flag,'N')
into latpcompflag
from mtl_system_items msi
where msi.inventory_item_id = p_ship_set.inventory_item_id(p_model_index)
and msi.organization_id = p_ship_set.source_organization_id(p_model_index);
select bom_explosion_temp_s.nextval
into lGroupid2
from dual;
select bom_explosion_temp_s.nextval
into xGrpId
from dual;
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code,
sort_order,
group_id)
select
1,
1,
p_ship_set.source_organization_id(i),
p_ship_set.inventory_item_id(i),
1,
CTO_UTILITY_PK.convert_uom(
p_ship_set.quantity_uom(i),
msi.primary_uom_code,
p_ship_set.quantity_ordered(i),
p_ship_set.inventory_item_id(i)),
msi.primary_uom_code,
2,
lGroupid2
from mtl_system_items msi
where msi.inventory_item_id = p_ship_set.inventory_item_id(i)
and msi.organization_id = p_ship_set.source_organization_id(i);
-- insert the top model into bom_explosion_temp
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code,
top_item_id,
line_id,
group_id)
select
1,
1,
p_ship_set.source_organization_id(p_model_index),
2, -- BUG no 1288823 modification
p_ship_set.inventory_item_id(p_model_index),
0,
CTO_UTILITY_PK.convert_uom(
p_ship_set.quantity_uom(p_model_index),
msi.primary_uom_code,
p_ship_set.quantity_ordered(p_model_index),
p_ship_set.inventory_item_id(p_model_index)),
msi.primary_uom_code,
p_ship_set.inventory_item_id(p_model_index),
1,
xGrpId
from mtl_system_items msi
where msi.inventory_item_id = p_ship_set.inventory_item_id(p_model_index)
and msi.organization_id = p_ship_set.source_organization_id(p_model_index);
Insert all selections for the top model
with corrosponding plan_levels
+------------------------------------------------*/
rowcount := 1 ;
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code,
top_item_id,
line_id,
group_id)
select
1,
1,
bet.organization_id,
2,
bic.component_item_id,
level_number,
CTO_UTILITY_PK.convert_uom(
bet2.primary_uom_code,
msi.primary_uom_code,
bet2.extended_quantity,
bic.component_item_id),
msi.primary_uom_code,
bet.top_item_id,
1,
xGrpId
from
bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items msi,
bom_explosion_temp bet,
bom_explosion_temp bet2
where bet.group_id = xGrpId
and bet.plan_level = level_number -1
and bic.component_item_id = msi.inventory_item_id
and msi.bom_item_type in ( 1, 2 ) /* Only Sub-models and Option Classes */
and bet.component_item_id = bom.assembly_item_id
and bet.organization_id = bom.organization_id
and bet.organization_id = msi.organization_id
and bom.alternate_bom_designator is null
and bom.common_bill_sequence_id = bic.bill_sequence_id
and bic.component_item_id = bet2.component_item_id
and bet2.group_id = lGroupid2;
delete from bom_explosion_temp
where group_id = lGroupid2;
select msi.fixed_lead_time , nvl(msi.variable_lead_time,0)
into v_msi_fixed_lead_time, v_msi_variable_lead_time
from mtl_system_items msi , bom_explosion_temp be
where be.organization_id = msi.organization_id
and be.component_item_id = msi.inventory_item_id
and be.component_item_id = p_ship_set.inventory_item_id(p_model_index);
insert into bom_explosion_temp(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
sort_order,
component_sequence_id,
component_item_id,
plan_level,
extended_quantity,
primary_uom_code,
top_item_id,
component_quantity,
check_atp,
atp_components_flag,
atp_flag,
bom_item_type,
assembly_item_id,
parent_bom_item_type,
line_id,
group_id)
select
be.top_bill_sequence_id,
bic.bill_sequence_id,
be.organization_id,
evaluate_order( msi2.atp_flag, msi2.atp_components_flag , msi2.bom_item_type ) ,
bic.component_sequence_id,
bic.component_item_id,
level_number + 1,
be.extended_quantity * bic.component_quantity,
msi2.primary_uom_code,
be.top_item_id,
bic.component_quantity,
bic.check_atp,
msi2.atp_components_flag,
msi2.atp_flag,
msi2.bom_item_type,
bom.assembly_item_id,
msi.bom_item_type,
NULL,
xGrpId
from
bom_calendar_dates cal,
mtl_system_items msi,
mtl_system_items msi2,
bom_inventory_components bic,
eng_revised_items eri,
bom_bill_of_materials bom,
mtl_parameters mp,
bom_explosion_temp be
where be.sort_order <> 3
and be.group_id = xGrpId
and nvl(be.plan_level,0) = level_number
and be.organization_id = bom.organization_id
and be.component_item_id = bom.assembly_item_id
and bic.component_quantity <> 0
and bic.revised_item_sequence_id = eri.revised_item_sequence_id (+)
and bic.component_item_id = msi2.inventory_item_id
and bom.organization_id = msi2.organization_id
and be.organization_id = msi.organization_id
and be.component_item_id = msi.inventory_item_id
and bom.alternate_bom_designator is null
and bic.bill_sequence_id = bom.common_bill_sequence_id
and mp.organization_id = be.organization_id
and cal.calendar_code = mp.calendar_code
and cal.exception_set_id = mp.calendar_exception_set_id
and cal.calendar_date =
( select c.calendar_date
from bom_calendar_dates C
where C.calendar_code = mp.calendar_code
and c.exception_set_id = mp.calendar_exception_set_id
and C.seq_num =
(select c2.prior_seq_num -
ceil(nvl(v_msi_fixed_lead_time,0)+
(be.extended_quantity *
nvl(v_msi_variable_lead_time,0)))
from bom_calendar_dates c2
where c2.calendar_code = mp.calendar_code
and c2.exception_set_id = mp.calendar_exception_set_id
and c2.calendar_date = trunc(pReqDate)
)
)
and TRUNC(bic.effectivity_date) <= greatest(nvl(cal.calendar_date,sysdate),sysdate)
and nvl(TRUNC(bic.disable_date),(nvl(cal.calendar_date,sysdate) + 1)) > nvl(cal.calendar_date,sysdate)
and bic.effectivity_date =
(select
max(effectivity_date)
from bom_inventory_components bic1,
eng_revised_items eri
where bic1.bill_sequence_id = bic.bill_sequence_id
and bic1.component_item_id = bic.component_item_id
and bic1.revised_item_sequence_id =
eri.revised_item_sequence_id (+)
and (decode(bic1.implementation_date, NULL,
bic1.old_component_sequence_id,
bic1.component_sequence_id) =
decode(bic.implementation_date, NULL,
bic.old_component_sequence_id,
bic.component_sequence_id)
OR
bic1.operation_seq_num = bic.operation_seq_num)
and TRUNC(bic1.effectivity_date) <= greatest(nvl(cal.calendar_date,sysdate),sysdate)
and nvl(TRUNC(bic1.disable_date),(nvl(cal.calendar_date,sysdate) + 1)) > nvl(cal.calendar_date,sysdate)
and ( nvl(eri.status_type,6) IN (4,6,7))
and not exists
(select
'X'
from bom_inventory_components bicn, eng_revised_items eri1
where bicn.bill_sequence_id + 0 = bic.bill_sequence_id
and bicn.old_component_sequence_id =
bic.component_sequence_id
and bicn.acd_type in (2,3)
and eri1.revised_item_sequence_id =
bicn.revised_item_sequence_id
and trunc(bicn.disable_date) <= cal.calendar_date
and ( nvl(eri1.status_type,6) in (4,6,7))
)
)
and bic.optional = 2
and msi2.bom_item_type = 4
and msi.bom_item_type in (1,2) /*Model or Option Class */
and msi.pick_components_flag <> 'Y' ;
select component_item_id cid,
component_sequence_id cseq,
component_quantity cq,
extended_quantity eq,
primary_uom_code uom, -- Bugfix 1998386
plan_level pl,
line_id, -- Bugfix 2897132
wip_supply_type -- Bugfix 3254039
from bom_explosion_temp be
where be.group_id = lGrpId
and ( be.line_id is null or top_bill_sequence_id = -1 ) -- Bugfix 2897132
and be.sort_order <> 2 ;
select nvl(fixed_lead_time,0),
nvl(variable_lead_time,0)
into lFixedLt,
lVarLt
from mtl_system_items msi
where msi.inventory_item_id = lItem_id
and msi.organization_id = lOrg_id;
select count(*)
into chk
from bom_explosion_temp
where group_id = lGrpId
and ( Line_id is null or top_bill_sequence_id = -1) ; /* BugFix 2897132 */
delete from bom_explosion_temp
where group_id = lGrpId;
select msi.organization_id src_org_id
from mtl_system_items msi
where msi.inventory_item_id = pConfigId
and not exists
(SELECT 'x'
FROM FND_ATTACHED_DOCUMENTS
WHERE pk1_value = to_char(msi.organization_id) -- 2774571
AND pk2_value = to_char(msi.inventory_item_id) -- 2774571
AND entity_name = 'MTL_SYSTEM_ITEMS');
select NVL(program_id,0) , ato_line_id into v_program_id , v_ato_line_id
from bom_cto_order_lines where line_id = pLineId ;
select nvl(master_organization_id,-99) -- bugfix 2646849: master_organization_id can be 0
into lValidationOrg
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 lValidationOrg from oe_order_lines_all oel
where oel.line_id = pLineId ;
select config_segment_name,
config_number_method_type
into lConfigSegName,
lNumberMethod
from bom_parameters
where organization_id = lValidationOrg;
-- Bugfix 1736339 : sql%notfound will not be raised in case of SELECT, instead NO_DATA_FOUND
-- exception is raised.
-- Added outer join to the query so that we'll default the maximum_size to 40
-- Merged stmt# 40 in the same query.
-- Since lConfigSegName is available, we will use this instead of joining bom_parameters.
select nvl(fv.maximum_size,40), fs.application_column_name
into lFndSize, app_column
from -- bom_parameters p,
fnd_id_flex_segments fs,
fnd_flex_value_sets fv
where --p.organization_id = lValidationOrg and
fs.id_flex_code = 'MSTK'
and fs.id_flex_num = 101
and fs.segment_name = lConfigSegName --p.config_segment_name
and fs.application_id = 401 -- INV
and fs.flex_value_set_id = fv.flex_value_set_id(+);
select
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20
into
seg(1),
seg(2),
seg(3),
seg(4),
seg(5),
seg(6),
seg(7),
seg(8),
seg(9),
seg(10),
seg(11),
seg(12),
seg(13),
seg(14),
seg(15),
seg(16),
seg(17),
seg(18),
seg(19),
seg(20)
from mtl_system_items msi
where inventory_item_id = pModelId
and organization_id = lValidationOrg;
select mtl_system_items_B_S.nextval
into lNextNum
from dual;
select ( substrb(seg(app_col_ind),1, decode(greatest(lFndSize,40),40,lFndSize -1-length(lNextNum),39 - length(lNextNum))) || lCiDel || to_char(lNextNum))
into new_item_num
from dual;
select to_char(mtl_system_items_B_S.nextval)
into new_item_num
from dual;
select oeh.order_number,
oel.line_number,
oel.shipment_number,
oel.option_number -- 2652379 : new column
into lOrderNum,
lLineNum,
lDeliveryNum,
lOptionNum -- 2652379 : new variable
from oe_order_lines_all oel,
oe_order_headers_all oeh
where oel.line_id = pLineId
and oel.header_id = oeh.header_id;
select decode(lDeliveryNum, NULL, lOrderNum || lCiDel || lLineNum,
lOrderNum || lCiDel || lLineNum || lCiDel|| lDeliveryNum )
into new_item_num
from dual;
select decode (lOptionNum, NULL, lOrderNum || lCiDel || lLineNum || lCiDel|| lDeliveryNum,
lOrderNum || lCiDel || lLineNum || lCiDel|| lDeliveryNum || lCiDel || lOptionNum )
into new_item_num
from dual;
select to_char(mtl_system_items_B_S.nextval)
into lNextNum
from dual;
select to_char(mtl_system_items_B_S.nextval)
into lNextNum
from dual;
select to_char(mtl_system_items_b_S.nextval) into pConfigId from dual;
select distinct
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20
into
seg(1),
seg(2),
seg(3),
seg(4),
seg(5),
seg(6),
seg(7),
seg(8),
seg(9),
seg(10),
seg(11),
seg(12),
seg(13),
seg(14),
seg(15),
seg(16),
seg(17),
seg(18),
seg(19),
seg(20)
from mtl_system_items msi
where inventory_item_id = pConfigId;
Insert a row into the mtl_system_items table.
+------------------------------------------------------------*/
--xTableName := 'MTL_SYSTEM_ITEMS';
insert into mtl_system_items_b
(inventory_item_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
summary_flag,
enabled_flag,
start_date_active,
end_date_active,
description,
buyer_id,
accounting_rule_id,
invoicing_rule_id,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
purchasing_item_flag,
shippable_item_flag,
customer_order_flag,
internal_order_flag,
service_item_flag,
inventory_item_flag,
eng_item_flag,
inventory_asset_flag,
purchasing_enabled_flag,
customer_order_enabled_flag,
internal_order_enabled_flag,
so_transactions_flag,
mtl_transactions_enabled_flag,
stock_enabled_flag,
bom_enabled_flag,
build_in_wip_flag,
revision_qty_control_code,
item_catalog_group_id,
catalog_status_flag,
returnable_flag,
default_shipping_org,
collateral_flag,
taxable_flag,
allow_item_desc_update_flag,
inspection_required_flag,
receipt_required_flag,
market_price,
hazard_class_id,
rfq_required_flag,
qty_rcv_tolerance,
un_number_id,
price_tolerance_percent,
asset_category_id,
rounding_factor,
unit_of_issue,
enforce_ship_to_location_code,
allow_substitute_receipts_flag,
allow_unordered_receipts_flag,
allow_express_delivery_flag,
days_early_receipt_allowed,
days_late_receipt_allowed,
receipt_days_exception_code,
receiving_routing_id,
invoice_close_tolerance,
receive_close_tolerance,
auto_lot_alpha_prefix,
start_auto_lot_number,
lot_control_code,
shelf_life_code,
shelf_life_days,
serial_number_control_code,
start_auto_serial_number,
auto_serial_alpha_prefix,
source_type,
source_organization_id,
source_subinventory,
expense_account,
encumbrance_account,
restrict_subinventories_code,
unit_weight,
weight_uom_code,
volume_uom_code,
unit_volume,
restrict_locators_code,
location_control_code,
shrinkage_rate,
acceptable_early_days,
planning_time_fence_code,
demand_time_fence_code,
lead_time_lot_size,
std_lot_size,
cum_manufacturing_lead_time,
overrun_percentage,
acceptable_rate_increase,
acceptable_rate_decrease,
cumulative_total_lead_time,
planning_time_fence_days,
demand_time_fence_days,
end_assembly_pegging_flag,
planning_exception_set,
bom_item_type,
pick_components_flag,
replenish_to_order_flag,
base_item_id,
atp_components_flag,
atp_flag,
fixed_lead_time,
variable_lead_time,
wip_supply_locator_id,
wip_supply_type,
wip_supply_subinventory,
primary_uom_code,
primary_unit_of_measure,
allowed_units_lookup_code,
cost_of_sales_account,
sales_account,
default_include_in_rollup_flag,
inventory_item_status_code,
inventory_planning_code,
planner_code,
planning_make_buy_code,
fixed_lot_multiplier,
rounding_control_type,
carrying_cost,
postprocessing_lead_time,
preprocessing_lead_time,
full_lead_time,
order_cost,
mrp_safety_stock_percent,
mrp_safety_stock_code,
min_minmax_quantity,
max_minmax_quantity,
minimum_order_quantity,
fixed_order_quantity,
fixed_days_supply,
maximum_order_quantity,
atp_rule_id,
picking_rule_id,
reservable_type,
positive_measurement_error,
negative_measurement_error,
engineering_ecn_code,
engineering_item_id,
engineering_date,
service_starting_delay,
vendor_warranty_flag,
serviceable_component_flag,
serviceable_product_flag,
base_warranty_service_id,
payment_terms_id,
preventive_maintenance_flag,
primary_specialist_id,
secondary_specialist_id,
serviceable_item_class_id,
time_billable_flag,
material_billable_flag,
expense_billable_flag,
prorate_service_flag,
coverage_schedule_id,
service_duration_period_code,
service_duration,
max_warranty_amount,
response_time_period_code,
response_time_value,
new_revision_code,
tax_code,
must_use_approved_vendor_flag,
safety_stock_bucket_days,
auto_reduce_mps,
costing_enabled_flag,
invoiceable_item_flag,
invoice_enabled_flag,
outside_operation_flag,
outside_operation_uom_type,
auto_created_config_flag,
cycle_count_enabled_flag,
item_type,
model_config_clause_name,
ship_model_complete_flag,
mrp_planning_code,
repetitive_planning_flag,
return_inspection_requirement,
effectivity_control,
request_id,
program_application_id,
program_id,
program_update_date,
comms_nl_trackable_flag, -- bugfix 2200256
default_so_source_type,
create_supply_flag,
-- 2336548
lot_status_enabled,
default_lot_status_id,
serial_status_enabled,
default_serial_status_id,
lot_split_enabled,
lot_merge_enabled,
bulk_picked_flag,
-- 2400609
FINANCING_ALLOWED_FLAG,
EAM_ITEM_TYPE ,
EAM_ACTIVITY_TYPE_CODE,
EAM_ACTIVITY_CAUSE_CODE,
EAM_ACT_NOTIFICATION_FLAG,
EAM_ACT_SHUTDOWN_STATUS,
SUBSTITUTION_WINDOW_CODE,
SUBSTITUTION_WINDOW_DAYS,
PRODUCT_FAMILY_ITEM_ID,
CHECK_SHORTAGES_FLAG,
PLANNED_INV_POINT_FLAG,
OVER_SHIPMENT_TOLERANCE,
UNDER_SHIPMENT_TOLERANCE,
OVER_RETURN_TOLERANCE,
UNDER_RETURN_TOLERANCE,
PURCHASING_TAX_CODE,
OVERCOMPLETION_TOLERANCE_TYPE,
OVERCOMPLETION_TOLERANCE_VALUE,
INVENTORY_CARRY_PENALTY,
OPERATION_SLACK_PENALTY,
UNIT_LENGTH,
UNIT_WIDTH,
UNIT_HEIGHT,
LOT_TRANSLATE_ENABLED,
CONTAINER_ITEM_FLAG,
VEHICLE_ITEM_FLAG,
DIMENSION_UOM_CODE,
SECONDARY_UOM_CODE,
MAXIMUM_LOAD_WEIGHT,
MINIMUM_FILL_PERCENT,
CONTAINER_TYPE_CODE,
INTERNAL_VOLUME,
EQUIPMENT_TYPE,
INDIVISIBLE_FLAG,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
DUAL_UOM_CONTROL,
DUAL_UOM_DEVIATION_HIGH,
DUAL_UOM_DEVIATION_LOW,
CONTRACT_ITEM_TYPE_CODE,
SUBSCRIPTION_DEPEND_FLAG,
SERV_REQ_ENABLED_CODE,
SERV_BILLING_ENABLED_FLAG,
RELEASE_TIME_FENCE_CODE, -- 2898851
RELEASE_TIME_FENCE_DAYS, -- 2898851
DEFECT_TRACKING_ON_FLAG, -- 2858080
SERV_IMPORTANCE_LEVEL,
WEB_STATUS , --2727983
tracking_quantity_ind, --Attribute for Item in patchset J
ont_pricing_qty_source,
approval_status ,
vmi_minimum_units,
vmi_minimum_days,
vmi_maximum_units,
vmi_maximum_days,
vmi_fixed_order_quantity,
so_authorization_flag,
consigned_flag,
asn_autoexpire_flag,
vmi_forecast_type,
forecast_horizon,
days_tgt_inv_supply,
days_tgt_inv_window,
days_max_inv_supply,
days_max_inv_window,
critical_component_flag,
drp_planned_flag,
exclude_from_budget_flag,
convergence,
continous_transfer,
divergence,
--r12 4574899
lot_divisible_flag,
grade_control_flag,
child_lot_flag,
child_lot_validation_flag,
copy_lot_attribute_flag,
recipe_enabled_flag,
process_quality_enabled_flag,
process_execution_enabled_flag,
process_costing_enabled_flag,
hazardous_material_flag,
preposition_point,
repair_program,
outsourced_assembly
)
select distinct
pConfigId,
m.organization_id,
sysdate,
gUserId,
sysdate,
gUserId,
gLoginId ,
m.summary_flag,
m.enabled_flag,
m.start_date_active,
m.end_date_active,
m.description,
m.buyer_id,
m.accounting_rule_id,
m.invoicing_rule_id,
seg(1),
seg(2),
seg(3),
seg(4),
seg(5),
seg(6),
seg(7),
seg(8),
seg(9),
seg(10),
seg(11),
seg(12),
seg(13),
seg(14),
seg(15),
seg(16),
seg(17),
seg(18),
seg(19),
seg(20),
m.attribute_category,
m.attribute1,
m.attribute2,
m.attribute3,
m.attribute4,
m.attribute5,
m.attribute6,
m.attribute7,
m.attribute8,
m.attribute9,
m.attribute10,
m.attribute11,
m.attribute12,
m.attribute13,
m.attribute14,
m.attribute15,
'Y',
'Y',
'Y',
'Y',
m.service_item_flag,
'Y',
m.eng_item_flag,
m.inventory_asset_flag,
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
m.revision_qty_control_code,
m.item_catalog_group_id,
m.catalog_status_flag,
m.returnable_flag,
m.default_shipping_org,
m.collateral_flag,
m.taxable_flag,
m.allow_item_desc_update_flag,
m.inspection_required_flag,
m.receipt_required_flag,
m.market_price,
m.hazard_class_id,
m.rfq_required_flag,
m.qty_rcv_tolerance,
m.un_number_id,
m.price_tolerance_percent,
m.asset_category_id,
m.rounding_factor,
m.unit_of_issue,
m.enforce_ship_to_location_code,
m.allow_substitute_receipts_flag,
m.allow_unordered_receipts_flag,
m.allow_express_delivery_flag,
m.days_early_receipt_allowed,
m.days_late_receipt_allowed,
m.receipt_days_exception_code,
m.receiving_routing_id,
m.invoice_close_tolerance,
m.receive_close_tolerance,
m.auto_lot_alpha_prefix,
m.start_auto_lot_number,
m.lot_control_code,
m.shelf_life_code,
m.shelf_life_days,
m.serial_number_control_code,
m.start_auto_serial_number,
m.auto_serial_alpha_prefix,
m.source_type,
m.source_organization_id,
m.source_subinventory,
m.expense_account,
m.encumbrance_account,
m.restrict_subinventories_code,
-- 2301167 : we will calculate the unit weight/vol later..
null,
null,
null,
null,
m.restrict_locators_code,
m.location_control_code,
m.shrinkage_rate,
m.acceptable_early_days,
m.planning_time_fence_code,
m.demand_time_fence_code,
m.lead_time_lot_size,
m.std_lot_size,
m.cum_manufacturing_lead_time,
m.overrun_percentage,
m.acceptable_rate_increase,
m.acceptable_rate_decrease,
m.cumulative_total_lead_time,
m.planning_time_fence_days,
m.demand_time_fence_days,
m.end_assembly_pegging_flag,
m.planning_exception_set,
4, -- BOM_ITEM_TYPE : standard
'N',
'Y',
pModelId,
evaluate_atp_attributes( m.atp_flag, m.atp_components_flag ),
get_atp_flag,
m.fixed_lead_time,
m.variable_lead_time,
m.wip_supply_locator_id,
m.wip_supply_type,
m.wip_supply_subinventory,
m.primary_uom_code,
m.primary_unit_of_measure,
m.allowed_units_lookup_code,
m.cost_of_sales_account,
m.sales_account,
'Y',
m.inventory_item_status_code,
m.inventory_planning_code,
m.planner_code,
m.planning_make_buy_code,
m.fixed_lot_multiplier,
m.rounding_control_type,
m.carrying_cost,
m.postprocessing_lead_time,
m.preprocessing_lead_time,
m.full_lead_time,
m.order_cost,
m.mrp_safety_stock_percent,
m.mrp_safety_stock_code,
m.min_minmax_quantity,
m.max_minmax_quantity,
m.minimum_order_quantity,
m.fixed_order_quantity,
m.fixed_days_supply,
m.maximum_order_quantity,
m.atp_rule_id,
m.picking_rule_id,
1,
m.positive_measurement_error,
m.negative_measurement_error,
m.engineering_ecn_code,
m.engineering_item_id,
m.engineering_date,
m.service_starting_delay,
m.vendor_warranty_flag,
m.serviceable_component_flag,
m.serviceable_product_flag,
m.base_warranty_service_id,
m.payment_terms_id,
m.preventive_maintenance_flag,
m.primary_specialist_id,
m.secondary_specialist_id,
m.serviceable_item_class_id,
m.time_billable_flag,
m.material_billable_flag,
m.expense_billable_flag,
m.prorate_service_flag,
m.coverage_schedule_id,
m.service_duration_period_code,
m.service_duration,
m.max_warranty_amount,
m.response_time_period_code,
m.response_time_value,
m.new_revision_code,
m.tax_code,
m.must_use_approved_vendor_flag,
m.safety_stock_bucket_days,
m.auto_reduce_mps,
m.costing_enabled_flag,
m.invoiceable_item_flag, -- 'N' Changed for international dropship
m.invoice_enabled_flag, -- 'N' Changed on OM's request
m.outside_operation_flag,
m.outside_operation_uom_type,
'Y',
m.cycle_count_enabled_flag,
lItemType,
m.model_config_clause_name,
m.ship_model_complete_flag,
m.mrp_planning_code, -- earlier it was always from one org only
m.repetitive_planning_flag, -- earlier it was always from one org only
m.return_inspection_requirement,
nvl(m.effectivity_control, 1),
null,
null,
null,
sysdate,
m.comms_nl_trackable_flag, -- 2200256
nvl(m.default_so_source_type,'INTERNAL'),
nvl(m.create_supply_flag, 'Y'),
-- begin bugfix 2336548
m.lot_status_enabled,
m.default_lot_status_id,
m.serial_status_enabled,
m.default_serial_status_id,
m.lot_split_enabled,
m.lot_merge_enabled,
m.bulk_picked_flag,
-- end bugfix 2336548
-- begin bugfix 2400609
m.FINANCING_ALLOWED_FLAG,
m.EAM_ITEM_TYPE ,
m.EAM_ACTIVITY_TYPE_CODE,
m.EAM_ACTIVITY_CAUSE_CODE,
m.EAM_ACT_NOTIFICATION_FLAG,
m.EAM_ACT_SHUTDOWN_STATUS,
m.SUBSTITUTION_WINDOW_CODE,
m.SUBSTITUTION_WINDOW_DAYS,
null, --m.PRODUCT_FAMILY_ITEM_ID, 5385901
m.CHECK_SHORTAGES_FLAG,
m.PLANNED_INV_POINT_FLAG,
m.OVER_SHIPMENT_TOLERANCE,
m.UNDER_SHIPMENT_TOLERANCE,
m.OVER_RETURN_TOLERANCE,
m.UNDER_RETURN_TOLERANCE,
m.PURCHASING_TAX_CODE,
m.OVERCOMPLETION_TOLERANCE_TYPE,
m.OVERCOMPLETION_TOLERANCE_VALUE,
m.INVENTORY_CARRY_PENALTY,
m.OPERATION_SLACK_PENALTY,
m.UNIT_LENGTH,
m.UNIT_WIDTH,
m.UNIT_HEIGHT,
m.LOT_TRANSLATE_ENABLED,
m.CONTAINER_ITEM_FLAG,
m.VEHICLE_ITEM_FLAG,
m.DIMENSION_UOM_CODE,
m.SECONDARY_UOM_CODE,
m.MAXIMUM_LOAD_WEIGHT,
m.MINIMUM_FILL_PERCENT,
m.CONTAINER_TYPE_CODE,
m.INTERNAL_VOLUME,
m.EQUIPMENT_TYPE,
m.INDIVISIBLE_FLAG,
m.GLOBAL_ATTRIBUTE_CATEGORY,
m.GLOBAL_ATTRIBUTE1,
m.GLOBAL_ATTRIBUTE2,
m.GLOBAL_ATTRIBUTE3,
m.GLOBAL_ATTRIBUTE4,
m.GLOBAL_ATTRIBUTE5,
m.GLOBAL_ATTRIBUTE6,
m.GLOBAL_ATTRIBUTE7,
m.GLOBAL_ATTRIBUTE8,
m.GLOBAL_ATTRIBUTE9,
m.GLOBAL_ATTRIBUTE10,
m.DUAL_UOM_CONTROL,
m.DUAL_UOM_DEVIATION_HIGH,
m.DUAL_UOM_DEVIATION_LOW,
m.CONTRACT_ITEM_TYPE_CODE,
m.SUBSCRIPTION_DEPEND_FLAG,
m.SERV_REQ_ENABLED_CODE,
m.SERV_BILLING_ENABLED_FLAG,
m.RELEASE_TIME_FENCE_CODE, -- 2898851
m.RELEASE_TIME_FENCE_DAYS, -- 2898851
m.DEFECT_TRACKING_ON_FLAG, -- 2858080
m.SERV_IMPORTANCE_LEVEL,
-- end bugfix 2400609
m.web_status , -- 2727983
nvl( tracking_quantity_ind , 'P' ),
nvl( m.ont_pricing_qty_source, 'P') ,
m.approval_status,
m.vmi_minimum_units,
m.vmi_minimum_days,
m.vmi_maximum_units,
m.vmi_maximum_days,
m.vmi_fixed_order_quantity,
m.so_authorization_flag,
m.consigned_flag,
m.asn_autoexpire_flag,
m.vmi_forecast_type,
m.forecast_horizon,
m.days_tgt_inv_supply,
m.days_tgt_inv_window,
m.days_max_inv_supply,
m.days_max_inv_window,
m.critical_component_flag,
m.drp_planned_flag,
m.exclude_from_budget_flag,
m.convergence,
m.continous_transfer,
m.divergence,
-- r12,4574899
nvl(m.lot_divisible_flag, 'N'), --Bugfix 6343429
'N',
'N',
'N',
'N',
'N',
'N',
'N',
'N',
'N',
'N',
3,
2
from
mtl_system_items_b m, -- Model
bom_cto_src_orgs bcso,
bom_cto_order_lines bcol
where m.inventory_item_id = pModelId
and bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and bcol.line_id = bcso.line_id
and m.organization_id = bcso.organization_id
and NOT EXISTS
(select NULL
from mtl_system_items_b
where inventory_item_id = pConfigId
and organization_id = m.organization_id);
oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_b',2);
oe_debug_pub.add('Create_Item: ' || 'Inserted '||sql%rowcount||' rows in mtl_system_items_b',2);
X_last_update_login => fnd_global.USER_ID,
X_program_application_id => fnd_global.PROG_APPL_ID,
X_program_id => fnd_global.CONC_REQUEST_ID,
X_request_id => fnd_global.USER_ID,
X_automatically_added_flag => NULL
);
select i.weight_uom_code, i.volume_uom_code
into o_weight_uom, o_volume_uom
from mtl_system_items i,
bom_cto_order_lines l
where l.line_id = pLineId -- model line id
and l.inventory_item_id = i.inventory_item_id
and i.organization_id = lValidationOrg;
select uom_code
into o_weight_uom
from mtl_units_of_measure
where uom_class = (select uom1.uom_class
from mtl_units_of_measure uom1,
mtl_system_items i,
bom_cto_order_lines l
where l.parent_ato_line_id = pLineId
-- mbsk: replaced ato_line_id with parent_ato_line_id
and l.item_type_code not in ('INCLUDED', 'CONFIG')
and l.inventory_item_id = i.inventory_item_id
and i.organization_id = lValidationOrg
and i.weight_uom_code is not null
and i.weight_uom_code = uom1.uom_code
and rownum = 1 )
and base_uom_flag = 'Y';
select uom_code
into o_volume_uom
from mtl_units_of_measure
where uom_class = (select uom1.uom_class
from mtl_units_of_measure uom1,
mtl_system_items i,
bom_cto_order_lines l
where l.parent_ato_line_id = pLineId
-- mbsk: replaced ato_line_id with parent_ato_line_id
and l.item_type_code not in ('INCLUDED', 'CONFIG')
and l.inventory_item_id = i.inventory_item_id
and i.organization_id = lValidationOrg
and i.volume_uom_code is not null
and i.volume_uom_code = uom1.uom_code
and rownum = 1 )
and base_uom_flag = 'Y';
-- We will update the wt/vol for other orgs after the IF clause.
-- If the lower level config's wt or vol was not calculated, then, we should not calculate
-- the wt or vol for the top level config.
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('Create_Item: ' || 'Calling Ato_Weight_Volume API with following parameters..',2);
-- Update the config's weight and volume.
-- begin bugfix 2905835: Before updating the weight in other organizations, we will convert
-- the weight of the config in the model's weight UOM in that organization.
lStmtNumber := 95;
-- bugfix 4143695: Update MSI only if the weight UOM is not null.
-- Added IF clause
-- bugfix 5623437:
-- Added "and a.unit_weight is null" condition so that weight/vol is updated
-- only if it is not already calculated.
if (o_weight_uom is not null) then
update mtl_system_items a
set (unit_weight, weight_uom_code) =
(select CTO_UTILITY_PK.convert_uom(
o_weight_uom,
nvl( b.weight_uom_code, o_weight_uom) , -- bug# 3358194
o_weight,
b.inventory_item_id)
,nvl(b.weight_uom_code, o_weight_uom) -- bug# 3358194
from mtl_system_items b
where b.inventory_item_id = a.base_item_id
and b.organization_id = a.organization_id)
where a.inventory_item_id = pConfigId
and a.unit_weight is null; -- bugfix 5623437
update mtl_system_items a
set (unit_volume, volume_uom_code) =
(select CTO_UTILITY_PK.convert_uom(
o_volume_uom,
nvl(b.volume_uom_code, o_volume_uom), -- bug# 3358194
o_volume,
b.inventory_item_id)
,nvl(b.volume_uom_code, o_volume_uom) -- bug# 3358194
from mtl_system_items b
where b.inventory_item_id = a.base_item_id
and b.organization_id = a.organization_id)
where a.inventory_item_id = pConfigId
and a.unit_volume is null; -- bugfix 5623437;
oe_debug_pub.add('Create_Item: ' || 'going to insert:mtl_system_items_tl',2);
insert into mtl_system_items_tl (
inventory_item_id,
organization_id,
language,
source_lang,
description,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
select distinct
pConfigId,
m.organization_id,
l.language_code,
userenv('LANG'),
m.description,
sysdate,
gUserId, --last_updated_by
sysdate,
gUserId, --created_by
gLoginId --last_update_login
/*
commented for reintroduction of bcso
from
mtl_system_items_tl m, -- 2457514
fnd_languages l
where m.inventory_item_id = pModelId
and l.installed_flag In ('I', 'B')
and l.language_code = m.language -- 2457514
and NOT EXISTS
(select NULL
from mtl_system_items_tl t
where t.inventory_item_id = pConfigId
and t.organization_id = m.organization_id
and t.language = m.language );
(select NULL
from mtl_system_items_tl t
where t.inventory_item_id = pConfigId
and t.organization_id = m.organization_id
and t.language = l.language_code );
oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_system_items_tl',2);
lStatus := CTO_UTILITY_PK.Update_Order_Lines(
pLineId => pLineId,
pModelId => pModelId,
pConfigId => pConfigId);
oe_debug_pub.add ('Create_Item: ' || 'Failed in Update_Order_Lines function',1);
lStatus := CTO_UTILITY_PK.Update_Src_Orgs(
pLineId => pLineId,
pModelId => pModelId,
pConfigId => pConfigId);
oe_debug_pub.add ('Create_Item: ' || 'Failed in Update_Src_Orgs function',1);
SELECT SUM( NVL(msi.unit_weight, 0) *
CTO_UTILITY_PK.convert_uom(l.order_quantity_uom,
msi.primary_uom_code,
Round( ( l.ordered_quantity / l_model.ordered_quantity), 7) , /* Support Decimal-Qty for Option Items */
l.inventory_item_id) ) weight,
msi.weight_uom_code uom,
msi.inventory_item_id
FROM bom_cto_order_lines l,
bom_cto_order_lines l_model,
mtl_system_items msi
WHERE (l.parent_ato_line_id = x_a_line_id
-- MLMO:replaced ato_line_id with parent_ato_line_id and added OR condn
or
l.line_id = x_a_line_id)
-- Bugfix 2576422
-- joining l_model.line_id to l.parent_ato_line_id so that
-- qty per will be calculated correctly for multi-level configuration
-- AND l_model.line_id = l.top_model_line_id
-- MLMO:replaced x_a_line_id with l.top_model_line_id
AND l_model.line_id = l.parent_ato_line_id
-- End bugfix 2576422
AND l.item_type_code not in ('INCLUDED', 'CONFIG')
AND msi.inventory_item_id = decode(l.config_item_id, null,l.inventory_item_id,
--3737772 (FP 3473737)
pConfigId,l.inventory_item_id, l.config_item_id )
-- MLMO: added decode/config_item_id condn
AND msi.organization_id = p_orgn_id
AND msi.weight_uom_code is not null
AND nvl(msi.unit_weight, 0) <> 0 -- bugfix 2905835: changed "is not null" to <> 0
GROUP BY msi.inventory_item_id,
msi.weight_uom_code;
SELECT SUM( NVL(msi.unit_volume, 0) *
CTO_UTILITY_PK.convert_uom(l.order_quantity_uom,
msi.primary_uom_code,
Round( ( l.ordered_quantity / l_model.ordered_quantity) , 7) , /* Support Decimal-Qty for Option Items */
l.inventory_item_id) ) volume,
msi.volume_uom_code uom,
msi.inventory_item_id
FROM bom_cto_order_lines l,
bom_cto_order_lines l_model,
mtl_system_items msi
WHERE (l.parent_ato_line_id = x_a_line_id -- MLMO: replaced ato_line_id with parent_ato_line_id
or
l.line_id = x_a_line_id)
-- Bugfix 2576422
-- joining l_model.line_id to l.parent_ato_line_id so that
-- qty per will be calculated correctly for multi-level configuration
-- AND l_model.line_id = l.top_model_line_id
-- MLMO:replaced x_a_line_id with l.top_model_line_id
AND l_model.line_id = l.parent_ato_line_id
-- End bugfix 2576422
AND l.item_type_code not in ('INCLUDED', 'CONFIG')
AND msi.inventory_item_id = decode(l.config_item_id, null, l.inventory_item_id,
--3737772 (FP 3473737)
pConfigId,l.inventory_item_id,l.config_item_id )
-- MLMO: added decode/config_item_id condn
AND msi.organization_id = p_orgn_id
AND msi.volume_uom_code is not null
AND nvl(msi.unit_volume,0) <> 0 -- bugfix 2905835 : changed "is not null" to <> 0
GROUP BY msi.inventory_item_id,
msi.volume_uom_code;
-- we don't want to update the weight of the config incorrectly, so set it to 0.
weight := 0;
-- weight has been updated to 0
CTO_CONFIG_ITEM_PK.gWtStatus := -1; -- -1 means error
-- we don't want to update the volume of the config incorrectly, so set it to 0.
volume := 0;
-- volume has been updated to 0
CTO_CONFIG_ITEM_PK.gVolStatus := -1; -- -1 means error
select distinct
MP1.organization_id org_id,
DECODE(MP1.ORGANIZATION_ID, lShipFromOrg ,l_cost_group_id,1) cost_group_id
/*
commented due to reintroduction of bcso
from mtl_parameters mp1,
cst_item_costs c,
mtl_system_items msi
where c.organization_id = mp1.organization_id
and c.inventory_item_id = pModelId
and C.COST_TYPE_ID = 2 -- Average Costing
and msi.organization_id = mp1.organization_id
and MP1.Primary_cost_method = 2 -- Create only in Avg costing org
and NOT EXISTS
(select NULL
from cst_quantity_layers
where inventory_item_id = pConfigId
and organization_id = mp1.organization_id);
(select NULL
from cst_quantity_layers
where inventory_item_id = pConfigId
and organization_id = mp1.organization_id);
l_cost_update number; --Bugfix 6363308
SELECT organization_id
, item_cost
FROM cst_item_costs
WHERE inventory_item_id = p_config_item_id
AND cost_type_id = 1;
select nvl( program_id , 0 ) into v_program_id
from bom_cto_order_lines
where line_id = pLineId ;
select nvl(master_organization_id, -99) --bugfix 2646849: added nvl.
into lValidationOrg
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 lValidationOrg from oe_order_lines_all oel
where oel.line_id = pLineId ;
insert into MTL_PENDING_ITEM_STATUS (
inventory_item_id,
organization_id,
status_code,
effective_date,
pending_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id)
select distinct
pConfigId,
m.organization_id,
m.inventory_item_status_code,
sysdate,
'N',
sysdate,
gUserId,
sysdate,
gUserId,
gLoginId,
null,
null,
sysdate,
null -- req_id
from mtl_system_items m,
bom_cto_src_orgs bcso
where m.inventory_item_id = pModelId
and bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and m.organization_id = bcso.organization_id
and NOT EXISTS
(select NULL
from MTL_PENDING_ITEM_STATUS
where inventory_item_id = pConfigId
and organization_id = m.organization_id);
oe_debug_pub.add('Create_Item: ' || 'after insert:MTL_PENDING_ITEM_STATUS',2);
Insert Item revision information
+-------------------------------------------*/
lStmtNumber := 210;
-- We will first try to insert into MIR. If this fails, we will insert into MIR_b and _tl using dynamic stmt.
-- We need to use dynamic stmt to avoid compilation errors in pre-I instances.
-- We did not use ALL_OBJECTS since this *may* not accessible from APPS schema.
-- This change supersedes bugfix 2730055
--
DECLARE
non_key_preserved_error EXCEPTION;
insert into mtl_item_revisions
(inventory_item_id,
organization_id,
revision,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
implementation_date,
effectivity_date
)
select distinct
pConfigId,
m.organization_id,
mp1.starting_revision,
sysdate,
gUserId, -- last_updated_by
sysdate,
gUserId, -- created_by
gLoginId, -- last_update_login
sysdate,
sysdate
from
mtl_system_items m,
bom_cto_src_orgs bcso,
mtl_parameters mp1
where m.inventory_item_id = pModelId
and bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and m.organization_id = bcso.organization_id
and mp1.organization_id = bcso.organization_id
and NOT EXISTS
(select NULL
from MTL_ITEM_REVISIONS
where inventory_item_id = pConfigId
and organization_id = m.organization_id);
oe_debug_pub.add('Create_Item_Data: ' || 'after insert:MTL_ITEM_REVISIONS',2);
oe_debug_pub.add('Create_item_data: Failed while inserting into MTL_ITEM_REVISIONS: '||substrb(sqlerrm,1,60),2);
Insert cost records for config items
The cost organization id is either the organization id
or the master organization id
+----------------------------------------------------------*/
lStmtNumber := 213;
select C.organization_id,
C.cost_type_id,
nvl(sum(decode( cicd.cost_element_id, 1 , nvl(cicd.item_cost, 0 ) )) , 0 ),
nvl( sum(decode( cicd.cost_element_id,2 , nvl( cicd.item_cost, 0 ) )) , 0 ),
nvl( sum(decode( cicd.cost_element_id,3 , nvl( cicd.item_cost, 0 ) )) , 0 ),
nvl( sum(decode( cicd.cost_element_id,4 , nvl( cicd.item_cost, 0 ) )) , 0 ),
nvl( sum(decode( cicd.cost_element_id,5 , nvl( cicd.item_cost, 0 ) )) , 0 )
BULK COLLECT INTO
l_rt_cicd_summary.cost_organization_id,
l_rt_cicd_summary.cost_type_id,
l_rt_cicd_summary.material_cost,
l_rt_cicd_summary.material_overhead_cost,
l_rt_cicd_summary.resource_cost,
l_rt_cicd_summary.outside_processing_cost,
l_rt_cicd_summary.overhead_cost
from
mtl_parameters MP1,
cst_item_costs C,
cst_item_cost_details CICD,
mtl_system_items S -- 4172300
where S.organization_id = C.organization_id
and S.inventory_item_id = C.inventory_item_id
and C.organization_id = MP1.organization_id
and C.inventory_item_id = pModelId
and C.inventory_item_id = S.inventory_item_id
and C.COST_TYPE_ID IN ( MP1.primary_cost_method, MP1.avg_rates_cost_type_id)
and C.inventory_item_id = CICD.inventory_item_id(+)
and C.organization_id = CICD.organization_id(+)
and C.cost_type_id = CICD.cost_type_id(+)
and CICD.rollup_source_type(+) = 1 -- User Defined
--4172300
and MP1.organization_id in ( select distinct MP2.cost_organization_id
from mtl_parameters mp2, mtl_parameters mp3, bom_cto_src_orgs bcso
where bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and MP3.organization_id = bcso.organization_id
and ((mp2.organization_id = bcso.organization_id) OR
(mp2.organization_id = mp3.master_organization_id))
)
and NOT EXISTS
(select NULL
from CST_ITEM_COSTS
where inventory_item_id = pConfigId
and organization_id = mp1.cost_organization_id
and cost_type_id in (mp1.primary_cost_method, mp1.avg_rates_cost_type_id))
group by C.organization_id, C.cost_type_id; -- 4172300
Insert a row into the cst_item_costs_table
+------------------------------------------------------- */
lStmtNumber := 220;
insert into CST_ITEM_COSTS
(inventory_item_id,
organization_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inventory_asset_flag,
lot_size,
based_on_rollup_flag,
shrinkage_rate,
defaulted_flag,
cost_update_id,
pl_material,
pl_material_overhead,
pl_resource,
pl_outside_processing,
pl_overhead,
tl_material,
tl_material_overhead,
tl_resource,
tl_outside_processing,
tl_overhead,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost ,
overhead_cost,
pl_item_cost,
tl_item_cost,
item_cost,
unburdened_cost ,
burden_cost,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
select distinct
pConfigId, -- INVENTORY_ITEM_ID
mp1.cost_organization_id,
c.cost_type_id,
sysdate, -- last_update_date
-1, -- last_updated_by
sysdate, -- creation_date
-1, -- created_by
-1, -- last_update_login
C.inventory_asset_flag,
C.lot_size,
C.based_on_rollup_flag,
C.shrinkage_rate,
C.defaulted_flag,
NULL, -- cost_update_id
0, -- C.pl_material,
0, -- C.pl_material_overhead,
0, -- C.pl_resource,
0, -- C.pl_outside_processing,
0, -- C.pl_overhead,
v_material_cost, -- C.tl_material,
v_material_overhead_cost, -- C.tl_material_overhead,
v_resource_cost, -- C.tl_resource,
v_outside_processing_cost, -- C.tl_outside_processing,
v_overhead_cost, --C.tl_overhead,
v_material_cost, -- material_cost
v_material_overhead_cost, -- material_overhead_cost
v_resource_cost, -- resource_cost
v_outside_processing_cost, -- outside_processing_cost
v_overhead_cost, -- overhead_cost
0, -- C.pl_item_cost,
v_item_cost, -- C.tl_item_cost,
v_item_cost, -- C.item_cost,
0, -- C.unburdened_cost ,
v_material_overhead_cost, -- C.burden_cost, /* check with rixin */
C.attribute_category,
C.attribute1,
C.attribute2,
C.attribute3,
C.attribute4,
C.attribute5,
C.attribute6,
C.attribute7,
C.attribute8,
C.attribute9,
C.attribute10,
C.attribute11,
C.ATTRIBUTE12,
C.attribute13,
C.attribute14,
C.attribute15
from
mtl_parameters MP1,
cst_item_costs C,
mtl_system_items S,
bom_cto_src_orgs bcso
where S.organization_id = C.organization_id
and S.inventory_item_id = C.inventory_item_id
and C.inventory_item_id = pModelId
and C.inventory_item_id = S.inventory_item_id
and bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and C.cost_type_id in ( mp1.primary_cost_method, mp1.avg_rates_cost_type_id)
and C.organization_id = MP1.organization_id
and mp1.organization_id = bcso.organization_id
and NOT EXISTS
(select NULL
from CST_ITEM_COSTS
where inventory_item_id = pConfigId
and organization_id = mp1.organization_id
and cost_type_id in (mp1.primary_cost_method, mp1.avg_rates_cost_type_id));
oe_debug_pub.add('Create_Item: ' || ' config item ' || pConfigId || ' after insert:CST_ITEM_COSTS inserted '|| to_char(lCnt),2);
UPDATE cst_item_costs
set material_cost = l_rt_cicd_summary.material_cost(j),
material_overhead_cost = l_rt_cicd_summary.material_overhead_cost(j),
resource_cost = l_rt_cicd_summary.resource_cost(j),
outside_processing_cost = l_rt_cicd_summary.outside_processing_cost(j),
overhead_cost = l_rt_cicd_summary.overhead_cost(j),
tl_material = l_rt_cicd_summary.material_cost(j),
tl_material_overhead = l_rt_cicd_summary.material_overhead_cost(j),
tl_resource = l_rt_cicd_summary.resource_cost(j),
tl_outside_processing = l_rt_cicd_summary.outside_processing_cost(j),
tl_overhead = l_rt_cicd_summary.overhead_cost(j),
tl_item_cost = l_rt_cicd_summary.item_cost(j),
item_cost = l_rt_cicd_summary.item_cost(j),
burden_cost = l_rt_cicd_summary.material_overhead_cost(j)
where inventory_item_id = pConfigId
and organization_id = l_rt_cicd_summary.cost_organization_id(j)
and cost_type_id = l_rt_cicd_summary.cost_type_id(j) ;
oe_debug_pub.add('Create_Item: ' || 'after update:CST_ITEM_COSTS '|| to_char(sql%rowcount),2);
oe_debug_pub.add( 'No update required to CST_ITEM_COSTS as no new records inserted ' , 1 ) ;
select cost_type_id into l_cto_cost_type_id
from cst_cost_types
where cost_type = 'CTO' ;
select cost_type into v_cto_cost_type_name
from cst_cost_types
where cost_type_id = l_cto_cost_type_id ;
insert into CST_ITEM_COSTS
(inventory_item_id,
organization_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inventory_asset_flag,
lot_size,
based_on_rollup_flag,
shrinkage_rate,
defaulted_flag,
cost_update_id,
pl_material,
pl_material_overhead,
pl_resource,
pl_outside_processing,
pl_overhead,
tl_material,
tl_material_overhead,
tl_resource,
tl_outside_processing,
tl_overhead,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost ,
overhead_cost,
pl_item_cost,
tl_item_cost,
item_cost,
unburdened_cost ,
burden_cost,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
select distinct
pConfigId, -- INVENTORY_ITEM_ID
mp1.cost_organization_id,
l_cto_cost_type_id, -- CTO cost_type_id,
sysdate, -- last_update_date
-1, -- last_updated_by
sysdate, -- creation_date
-1, -- created_by
-1, -- last_update_login
C.inventory_asset_flag,
C.lot_size,
C.based_on_rollup_flag,
C.shrinkage_rate,
C.defaulted_flag,
NULL, -- cost_update_id
0, -- C.pl_material,
0, -- C.pl_material_overhead,
0, -- C.pl_resource,
0, -- C.pl_outside_processing,
0, -- C.pl_overhead,
v_material_cost, -- C.tl_material,
v_material_overhead_cost, -- C.tl_material_overhead,
v_resource_cost, -- C.tl_resource,
v_outside_processing_cost, -- C.tl_outside_processing,
v_overhead_cost, -- C.tl_overhead,
v_material_cost, -- material cost
v_material_overhead_cost, -- material overhead cost
v_resource_cost, -- resource cost
v_outside_processing_cost, -- outside processing cost
v_overhead_cost, -- overhead cost
0, -- C.pl_item_cost,
v_item_cost, -- C.tl_item_cost,
v_item_cost, -- total item cost
0, -- C.unburdened_cost ,
v_material_overhead_cost, -- C.burden_cost,
C.attribute_category,
C.attribute1,
C.attribute2,
C.attribute3,
C.attribute4,
C.attribute5,
C.attribute6,
C.attribute7,
C.attribute8,
C.attribute9,
C.attribute10,
C.attribute11,
C.ATTRIBUTE12,
C.attribute13,
C.attribute14,
C.attribute15
from
mtl_parameters MP1,
cst_item_costs C,
mtl_system_items S,
bom_cto_src_orgs bcso
where S.organization_id = C.organization_id
and S.inventory_item_id = C.inventory_item_id
and C.inventory_item_id = pModelId
and C.inventory_item_id = S.inventory_item_id
and bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and C.cost_type_id = mp1.primary_cost_method
and C.cost_type_id = 1
and C.organization_id = MP1.organization_id
and mp1.organization_id = bcso.organization_id
and NOT EXISTS
(select NULL
from CST_ITEM_COSTS
where inventory_item_id = pConfigId
and organization_id = mp1.organization_id
and cost_type_id = l_cto_cost_type_id);
oe_debug_pub.add('Create_Item: ' || ' config item ' || pConfigId || ' after insert UD cost into CTO cost type inserted '|| to_char(sql%rowcount),2);
UPDATE cst_item_costs
set material_cost = l_rt_cicd_summary.material_cost(j),
material_overhead_cost = l_rt_cicd_summary.material_overhead_cost(j),
resource_cost = l_rt_cicd_summary.resource_cost(j),
outside_processing_cost = l_rt_cicd_summary.outside_processing_cost(j),
overhead_cost = l_rt_cicd_summary.overhead_cost(j),
tl_material = l_rt_cicd_summary.material_cost(j),
tl_material_overhead = l_rt_cicd_summary.material_overhead_cost(j),
tl_resource = l_rt_cicd_summary.resource_cost(j),
tl_outside_processing = l_rt_cicd_summary.outside_processing_cost(j),
tl_overhead = l_rt_cicd_summary.overhead_cost(j),
tl_item_cost = l_rt_cicd_summary.item_cost(j),
item_cost = l_rt_cicd_summary.item_cost(j),
burden_cost = l_rt_cicd_summary.material_overhead_cost(j)
where inventory_item_id = pConfigId
and organization_id = l_rt_cicd_summary.cost_organization_id(j)
and cost_type_id = l_cto_cost_type_id ;
oe_debug_pub.add('Create_Item: ' || 'after update:cst_item_costs for CTO cost type '||to_char(sql%rowcount),2);
oe_debug_pub.add( 'No update required to CST_ITEM_COSTS for CTO cost type as no new records inserted ' , 1 ) ;
Insert rows into the cst_item_cost_details table
+-----------------------------------------------------*/
lStmtNumber := 230;
insert into cst_item_cost_details
(inventory_item_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
operation_sequence_id,
operation_seq_num,
department_id,
level_type,
activity_id,
resource_seq_num,
resource_id,
resource_rate,
item_units,
activity_units,
usage_rate_or_amount,
basis_type,
basis_resource_id,
basis_factor,
net_yield_or_shrinkage_factor,
item_cost,
cost_element_id,
rollup_source_type,
activity_context,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
select distinct
pConfigId, -- inventory_item_id
c.cost_type_id,
sysdate, -- last_update_date
-1, -- last_updated_by
sysdate, -- creation_date
-1, -- created_by
-1, -- last_update_login
mp1.cost_organization_id,
c.operation_sequence_id,
c.operation_seq_num,
c.department_id,
c.level_type,
c.activity_id,
c.resource_seq_num,
c.resource_id,
c.resource_rate,
c.item_units,
c.activity_units,
c.usage_rate_or_amount,
c.basis_type,
c.basis_resource_id,
c.basis_factor,
c.net_yield_or_shrinkage_factor,
c.item_cost,
c.cost_element_id,
C.rollup_source_type,
C.activity_context,
C.attribute_category,
C.attribute1,
C.attribute2,
C.attribute3,
C.attribute4,
C.attribute5,
C.attribute6,
C.attribute7,
C.attribute8,
C.attribute9,
C.attribute10,
C.attribute11,
C.attribute12,
C.attribute13,
C.attribute14,
C.attribute15
from
mtl_parameters MP1,
cst_item_cost_details C,
mtl_system_items S,
bom_cto_src_orgs bcso
where S.organization_id = C.organization_id
and S.inventory_item_id = C.inventory_item_id
and bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and C.organization_id = MP1.organization_id
and C.inventory_item_id = pModelId
and C.inventory_item_id = S.inventory_item_id
and C.rollup_source_type = 1 -- User Defined
and C.COST_TYPE_ID IN ( MP1.primary_cost_method, MP1.avg_rates_cost_type_id)
and mp1.organization_id = bcso.organization_id
and NOT EXISTS
(select NULL
from cst_item_cost_details
where inventory_item_id = pConfigId
and organization_id = mp1.organization_id
and COST_TYPE_ID IN (MP1.primary_cost_method, MP1.avg_rates_cost_type_id));
oe_debug_pub.add('Create_Item: ' || 'after insert:cst_item_cost_details inserted '||to_char(sql%rowcount),2);
insert into cst_item_cost_details
(inventory_item_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
operation_sequence_id,
operation_seq_num,
department_id,
level_type,
activity_id,
resource_seq_num,
resource_id,
resource_rate,
item_units,
activity_units,
usage_rate_or_amount,
basis_type,
basis_resource_id,
basis_factor,
net_yield_or_shrinkage_factor,
item_cost,
cost_element_id,
rollup_source_type,
activity_context,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
select distinct
pConfigId, -- inventory_item_id
l_cto_cost_type_id, -- CTO cost_type_id,
sysdate, -- last_update_date
-1, -- last_updated_by
sysdate, -- creation_date
-1, -- created_by
-1, -- last_update_login
mp1.cost_organization_id,
c.operation_sequence_id,
c.operation_seq_num,
c.department_id,
c.level_type,
c.activity_id,
c.resource_seq_num,
c.resource_id,
c.resource_rate,
c.item_units,
c.activity_units,
c.usage_rate_or_amount,
c.basis_type,
c.basis_resource_id,
c.basis_factor,
c.net_yield_or_shrinkage_factor,
c.item_cost,
c.cost_element_id,
C.rollup_source_type,
C.activity_context,
C.attribute_category,
C.attribute1,
C.attribute2,
C.attribute3,
C.attribute4,
C.attribute5,
C.attribute6,
C.attribute7,
C.attribute8,
C.attribute9,
C.attribute10,
C.attribute11,
C.attribute12,
C.attribute13,
C.attribute14,
C.attribute15
from
mtl_parameters MP1,
cst_item_cost_details C,
mtl_system_items S,
bom_cto_src_orgs bcso
where S.organization_id = C.organization_id
and S.inventory_item_id = C.inventory_item_id
and bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and C.organization_id = MP1.organization_id
and C.inventory_item_id = pModelId
and C.inventory_item_id = S.inventory_item_id
and C.rollup_source_type = 1 -- User Defined
and C.COST_TYPE_ID = MP1.primary_cost_method
and C.cost_type_id = 1
and mp1.organization_id = bcso.organization_id
and NOT EXISTS
(select NULL
from cst_item_cost_details
where inventory_item_id = pConfigId
and organization_id = mp1.organization_id
and COST_TYPE_ID = l_cto_cost_type_id);
oe_debug_pub.add('Create_Item: ' || 'after insert UD cost into CICD for CTO cost type inserted '||to_char(sql%rowcount),2);
oe_debug_pub.add('Create_Item: ' || 'No need to populate csc and cec as no new record inserted in cic and cicd',2);
Select cst_lists_s.nextval
into l_cost_update
From DUAL;
UPDATE CST_ITEM_COSTS
SET cost_update_id = l_cost_update
WHERE ORGANIZATION_ID = v_organization_id
AND INVENTORY_ITEM_ID = pConfigId
AND COST_TYPE_ID = 1;
oe_debug_pub.add('Create_Item: ' || 'Updated cost_update_id to value ' || to_char(l_cost_update),2);
oe_debug_pub.add('Create_Item: ' || 'Inserting records in csc and cec',2);
INSERT INTO cst_standard_costs
(cost_update_id,
organization_id,
inventory_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
standard_cost_revision_date,
standard_cost
)
SELECT l_cost_update,
v_organization_id,
pConfigId,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
SYSDATE,
NVL(SUM(item_cost),0)
FROM cst_item_cost_details
WHERE ORGANIZATION_ID = v_organization_id
AND INVENTORY_ITEM_ID = pConfigId
AND COST_TYPE_ID = 1;
oe_debug_pub.add('Create_Item: ' || 'after insert:cst_standard_costs ' || sql%rowcount ,2);
INSERT INTO cst_elemental_costs
(cost_update_id,
organization_id,
inventory_item_id,
cost_element_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
standard_cost
)
SELECT l_cost_update,
v_organization_id,
pConfigId,
cost_element_id,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
NVL(SUM(item_cost),0)
FROM cst_item_cost_details
WHERE ORGANIZATION_ID = v_organization_id
AND INVENTORY_ITEM_ID = pConfigId
AND COST_TYPE_ID = 1
GROUP BY cost_element_id;
oe_debug_pub.add('Create_Item: ' || 'after insert:cst_elemental_costs ' || sql%rowcount ,2);
select ship_from_org_id
into lShipFromOrg
from bom_cto_order_lines bcol
where line_id = pLineID;
-- This costing API will insert a row into cst_quantity_layers
-- for a unique layer_id and the given parameters.
-- It will return 0 if failed, layer_id if succeeded
--
l_layer_id := cstpaclm.create_layer (
i_org_id => v_layer.org_id,
i_item_id => pConfigId,
i_cost_group_id => v_layer.cost_group_id,
i_user_id => gUserId,
i_request_id => NULL,
i_prog_id => NULL,
i_prog_appl_id => NULL,
i_txn_id => -1,
o_err_num => x_err_num,
o_err_code => x_msg_name,
o_err_msg => lMsgData
);
oe_debug_pub.add('Create_Item: ' || 'Inserted row into cql for '||to_char(l_layer_id)||', '||to_char(v_layer.org_id)||', '||
to_char(v_layer.cost_group_id),1);
oe_debug_pub.add('Create_Item: ' || 'after insert:cst_quantity_layers ',2);
Insert rows into the mtl_desc_elem_val_interface table
Descriptive elements are not organization controlled
Using validation org to get values
+---------------------------------------------------------*/
lStmtNumber := 250;
insert into MTL_DESCR_ELEMENT_VALUES
(inventory_item_id,
element_name,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
element_value,
default_element_flag,
element_sequence,
program_application_id,
program_id,
program_update_date,
request_id
)
select distinct
pConfigId, -- Inventory_item_id
E.element_name, -- element_name
sysdate, -- last_update_date
gUserId, -- last_updated_by
gLoginId, -- last_update_login
sysdate, -- creation_date
gUserId, -- created_by
D.element_value, -- element_value
E.default_element_flag, -- default_element_flag
E.element_sequence, -- element_sequence
NULL, -- program_application_id
NULL, -- program_id
SYSDATE, -- program_update_date
NULL -- request_id
from mtl_system_items s,
mtl_descr_element_values D,
mtl_descriptive_elements E
where D.inventory_item_id = S.inventory_item_id
and s.inventory_item_id = pModelid
and s.organization_id = lValidationOrg
and E.item_catalog_group_id = S.item_catalog_group_id
and E.element_name = D.element_name
and NOT EXISTS
(select NULL
from mtl_descr_element_values
where inventory_item_id = pConfigId
and organization_id = lValidationOrg);
oe_debug_pub.add('Create_Item: ' || 'after insert: MTL_DESCR_ELEMENT_VALUES',2);
Insert into mtl_item_categories
+--------------------------------------*/
lStmtNumber := 260;
insert into MTL_ITEM_CATEGORIES
(inventory_item_id,
category_set_id,
category_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
organization_id
)
select distinct
pConfigId,
ic.category_set_id,
ic.category_id,
sysdate, -- last_update_date
gUserId, -- last_updated_by
sysdate, --creation_date
gUserId, -- created_by
gLoginId, -- last_update_login
NULL, -- request_id
NULL, -- program_application_id
NULL, -- program_id
SYSDATE, -- program_update_date
ic.organization_id
from
mtl_item_categories ic,
bom_cto_src_orgs bcso
where ic.inventory_item_id = pModelId --bugfix 2706981: swapped the positions
and ic.organization_id = bcso.organization_id
and bcso.model_item_id = ic.inventory_item_id --bugfix 2706981: replaced pModelId with col join
and bcso.line_id = pLineId -- as in bugfix 2215274
--
-- bugfix 2619501 (butler mfg):
-- We will call custom hook to see which category_set needs to be inserted.
-- If the custom hook returns 1 for a particular category_set_id, then, we will insert that category set.
-- By default, the custom hook will return 1 for all category sets except sales and mktg category set.
--
-- and ic.category_set_id <> 5 -- bugfix 2395525
and CTO_CUSTOM_CATEGORY_PK.Copy_Category (ic.category_set_id , ic.organization_id) = 1
--
-- end bugfix 2619501:
--
and NOT EXISTS
(select NULL
from MTL_ITEM_CATEGORIES ic1 -- bugfix 2706981: added alias
where ic1.inventory_item_id = pConfigId
and ic1.organization_id = bcso.organization_id
and ic1.category_set_id = ic.category_set_id
);
oe_debug_pub.add('Create_Item: ' || 'after insert: MTL_ITEM_CATEGORIES',2);
insert into MTL_ITEM_CATEGORIES
(inventory_item_id,
category_set_id,
category_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
organization_id
)
select distinct
pConfigId,
mcsb.category_set_id,
mcsb.default_category_id,
sysdate, -- last_update_date
gUserId, -- last_updated_by
sysdate, --creation_date
gUserId, -- created_by
gLoginId, -- last_update_login
NULL, -- request_id
NULL, -- program_application_id
NULL, -- program_id
SYSDATE, -- program_update_date
ic.organization_id
from
mtl_item_categories ic,
mtl_category_sets_b mcsb,
mtl_default_category_sets mdcs,
bom_cto_src_orgs bcso
where pModelId = ic.inventory_item_id
and ic.organization_id = bcso.organization_id
and bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and mcsb.category_set_id = mdcs.category_set_id
and mdcs.functional_area_id = 2
and NOT EXISTS
(
select NULL
from MTL_ITEM_CATEGORIES
where inventory_item_id = pConfigId
and organization_id = bcso.organization_id
and category_set_id = mcsb.category_set_id
);
oe_debug_pub.add('Create_Item: ' || 'after insert: MTL_ITEM_CATEGORIES FOR DEFAULT CATEGORIES',2);
insert into MTL_RELATED_ITEMS
(
inventory_item_id,
related_item_id,
relationship_type_id,
reciprocal_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
organization_id
)
select distinct
pConfigId,
ri.related_item_id,
ri.relationship_type_id,
ri.reciprocal_flag,
sysdate, -- last_update_date
gUserId, -- last_updated_by
sysdate, --creation_date
gUserId, -- created_by
gLoginId, -- last_update_login
NULL, -- request_id
NULL, -- program_application_id
NULL, -- program_id
SYSDATE, -- program_update_date
ri.organization_id
from mtl_related_items ri,
bom_cto_src_orgs bcso
where ri.inventory_item_id = pModelId
and bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and ri.organization_id = bcso.organization_id
and NOT EXISTS
(select NULL
from mtl_related_items
where inventory_item_id = pConfigId
and organization_id = bcso.organization_id);
oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_related_items',2);
insert into mtl_item_sub_inventories
(
inventory_item_id,
organization_id,
secondary_inventory,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
primary_subinventory_flag ,
picking_order,
min_minmax_quantity,
max_minmax_quantity,
inventory_planning_code,
fixed_lot_multiple,
minimum_order_quantity,
maximum_order_quantity,
source_type,
source_organization_id,
source_subinventory,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id ,
program_id,
program_update_date,
encumbrance_account
)
select distinct
pConfigId,
isi.ORGANIZATION_ID,
isi.SECONDARY_INVENTORY,
sysdate, -- last_update_date
gUserId, -- last_updated_by
sysdate, -- creation_date
gUserId, -- created_by
gLoginId, -- last_update_login
isi.PRIMARY_SUBINVENTORY_FLAG ,
isi.PICKING_ORDER,
isi.MIN_MINMAX_QUANTITY,
isi.MAX_MINMAX_QUANTITY,
isi.INVENTORY_PLANNING_CODE,
isi.FIXED_LOT_MULTIPLE,
isi.MINIMUM_ORDER_QUANTITY,
isi.MAXIMUM_ORDER_QUANTITY,
isi.SOURCE_TYPE,
isi.SOURCE_ORGANIZATION_ID,
isi.SOURCE_SUBINVENTORY,
isi.ATTRIBUTE_CATEGORY,
isi.ATTRIBUTE1,
isi.ATTRIBUTE2,
isi.ATTRIBUTE3,
isi.ATTRIBUTE4,
isi.ATTRIBUTE5,
isi.ATTRIBUTE6,
isi.ATTRIBUTE7,
isi.ATTRIBUTE8,
isi.ATTRIBUTE9,
isi.ATTRIBUTE10,
isi.ATTRIBUTE11,
isi.ATTRIBUTE12,
isi.ATTRIBUTE13,
isi.ATTRIBUTE14,
isi.ATTRIBUTE15,
NULL, -- request_id
NULL, -- program_application_id
NULL, -- program_id
SYSDATE, -- program_update_date
isi.ENCUMBRANCE_ACCOUNT
from
mtl_item_sub_inventories isi,
bom_cto_src_orgs bcso
where isi.organization_id = bcso.organization_id
and isi.inventory_item_id = pModelId
and bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and NOT EXISTS
(select NULL
from mtl_item_sub_inventories
where inventory_item_id = pConfigId
and organization_id = bcso.organization_id);
oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_item_sub_inventories',2);
insert into mtl_secondary_locators
(
inventory_item_id,
organization_id,
secondary_locator,
primary_locator_flag,
picking_order,
subinventory_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
select distinct
pConfigId,
sl.organization_id,
sl.secondary_locator,
sl.primary_locator_flag,
sl.picking_order,
sl.subinventory_code,
sysdate, -- last_update_date
gUserId, -- last_updated_by
sysdate, -- creation_date
gUserId, -- created_by
gLoginId, -- last_update_login
NULL, -- request_id
NULL, -- program_application_id
NULL, -- program_id
SYSDATE -- program_update_date
from
mtl_secondary_locators sl,
bom_cto_src_orgs bcso
where sl.organization_id = bcso.organization_id
and pModelId = sl.inventory_item_id
and bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and NOT EXISTS
(select NULL
from mtl_secondary_locators
where inventory_item_id = pConfigId
and organization_id = bcso.organization_id);
oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_secondary_locators',2);
SELECT DISTINCT
CR.ORGANIZATION_ID
,CR.CROSS_REFERENCE_TYPE
,CR.CROSS_REFERENCE
,CR.ORG_INDEPENDENT_FLAG
BULK COLLECT INTO
t_organization_id,
t_cross_ref_type,
t_cross_ref,
t_org_independent_flag
FROM MTL_CROSS_REFERENCES_B CR,
BOM_CTO_SRC_ORGS BCSO
WHERE (CR.ORGANIZATION_ID = bcso.ORGANIZATION_ID OR CR.ORGANIZATION_ID IS NULL)
AND CR.INVENTORY_ITEM_ID = PMODELID
AND BCSO.MODEL_ITEM_ID = PMODELID
AND BCSO.LINE_ID = PLINEID
AND NOT EXISTS
( SELECT NULL
FROM MTL_CROSS_REFERENCES_B
WHERE INVENTORY_ITEM_ID = PCONFIGID
AND ( ORGANIZATION_ID = bcso.ORGANIZATION_ID OR ORGANIZATION_ID IS NULL) -- bugfix 1960994: added OR condition
);
INSERT INTO MTL_CROSS_REFERENCES_B
(
INVENTORY_ITEM_ID
,ORGANIZATION_ID
,CROSS_REFERENCE_TYPE
,CROSS_REFERENCE
,ORG_INDEPENDENT_FLAG
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,SOURCE_SYSTEM_ID
,OBJECT_VERSION_NUMBER
,UOM_CODE
,REVISION_ID
,CROSS_REFERENCE_ID
,EPC_GTIN_SERIAL
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE_CATEGORY
)
VALUES
(
pConfigId
,t_organization_id(i)
,t_cross_ref_type(i)
,t_cross_ref(i)
,t_org_independent_flag(i)
,SYSDATE
,GUSERID
,SYSDATE
,GUSERID
,GLOGINID
,NULL --REQUEST_ID
,NULL --PROGRAM_APPLICATION_ID
,NULL --PROGRAM_ID
,SYSDATE --PROGRAM_UPDATE_DATE
,NULL --SOURCE_SYSTEM_ID
,1 --OBJECT_VERSION_NUMBER
,NULL --UOM_CODE due to ER#3215422. do not copy uom_code and revision_id attribute for mtl_cross_references
,NULL --REVISION_ID due to ER#3215422. do not copy uom_code and revision_id attribute for mtl_cross_references
,MTL_CROSS_REFERENCES_B_S.NEXTVAL --CROSS_REFERENCE_ID
,0 --EPC_GTIN_SERIAL
,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
,NULL --ATTRIBUTE_CATEGORY
);
oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_cross_references_b',2);
oe_debug_pub.add('Create_Item: ' || '# of inserted rows mtl_cross_references_b'||sql%rowcount,2);
INSERT INTO mtl_cross_references_tl (
last_update_login
,description
,creation_date
,created_by
,last_update_date
,last_updated_by
,cross_reference_id
,language
,source_lang)
SELECT
gloginid,
mtl.description,
sysdate,
guserid,
sysdate,
guserid,
mtl_cross.cross_reference_id,
l.language_code,
userenv('lang')
FROM fnd_languages l,
mtl_cross_references_b mtl_cross,
mtl_system_items_tl mtl
WHERE mtl_cross.inventory_item_id = pConfigId
AND mtl_cross.inventory_item_id = mtl.inventory_item_id
AND mtl_cross.organization_id = mtl.organization_id
AND l.language_code = mtl.language
AND l.installed_flag in ('I', 'B')
AND NOT EXISTS (SELECT null
FROM mtl_cross_references_tl t
WHERE t.cross_reference_id = mtl_cross.cross_reference_id
AND t.language = l.language_code);
oe_debug_pub.add('Create_Item: ' || 'after insert:mtl_cross_references_tl',2);
oe_debug_pub.add('Create_Item: ' || '# of inserted rows mtl_cross_references_tl'||sql%rowcount,2);
insert into mtl_item_sub_defaults
(
inventory_item_id,
organization_id,
subinventory_code,
default_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
select distinct
pConfigId,
sd.organization_id,
sd.subinventory_code,
sd.default_type,
sysdate, -- last_update_date
gUserId, -- last_updated_by
sysdate, -- creation_date
gUserId, -- created_by
gLoginId, -- last_update_login
NULL, -- request_id
NULL, -- program_application_id
NULL, -- program_id
SYSDATE -- program_update_date
from
mtl_item_sub_defaults sd,
bom_cto_src_orgs bcso
where sd.organization_id = bcso.organization_id
and sd.inventory_item_id = pModelId
and bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and NOT EXISTS
(select NULL
from mtl_item_sub_defaults
where inventory_item_id = pConfigId
and organization_id = bcso.organization_id);
oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_item_sub_defaults',2);
insert into mtl_item_loc_defaults
(
inventory_item_id,
organization_id,
locator_id,
default_type,
subinventory_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
select distinct
pConfigId,
ld.organization_id,
ld.locator_id,
ld.default_type,
ld.subinventory_code,
sysdate, -- last_update_date
gUserId, -- last_updated_by
sysdate, -- creation_date
gUserId, -- created_by
gLoginId, -- last_update_login
NULL, -- request_id
NULL, -- program_application_id
NULL, -- program_id
SYSDATE -- program_update_date
from
mtl_item_loc_defaults ld,
bom_cto_src_orgs bcso
where ld.organization_id = bcso.organization_id
and ld.inventory_item_id = bcso.model_item_id
and bcso.model_item_id = pModelId
and bcso.line_id = pLineId
and NOT EXISTS
(select NULL
from mtl_item_loc_defaults
where inventory_item_id = pConfigId
and organization_id = ld.organization_id);
oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_item_loc_defaults',2);
INSERT INTO mtl_abc_assignments
(
inventory_item_id,
assignment_group_id,
abc_class_id,
last_update_date,
last_updated_by,
creation_date,
created_by
)
select pConfigId,
maa.assignment_group_id,
maa.abc_class_id,
sysdate,
gUserId,
sysdate,
gUserId
FROM mtl_abc_assignments maa
WHERE maa.inventory_item_id = pModelId
--bugfix3536085 not exists condition
AND NOT EXISTS
(SELECT 'X'
FROM mtl_abc_assignments
WHERE inventory_item_id = pConfigId
AND assignment_group_id = maa.assignment_group_id );
oe_debug_pub.add('Create_Item: ' || 'inserted '||sql%rowcount||' in mtl_abc_assignments',2);
oe_debug_pub.add('Create_Item: ' || 'after insert: mtl_abc_assignments',2);
Select concatenated_segments
into l_model_tab(1).ordered_item
from mtl_system_items_b_kfv
where inventory_item_id = pConfigId
and organization_id = pOrgId;
Setting visible demand flag to 'N' for the selected model and options.
+-----------------------------------------------------------*/
--
-- selecting all rows to be updated into l_upd_line_tbl
--
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add ('link_item: ' || 'link : visible demand flag : before selecting options ', 2);
UPDATE OE_ORDER_LINES_ALL
SET visible_demand_flag = 'N'
WHERE ato_line_id = pLineId;
oe_debug_pub.add('link_item: ' || 'No of rows updated = '||sql%rowcount,1);
select nvl(component_code, oel.inventory_item_id) || '-'|| to_char(pConfigId),
substrb(user_item_Description,1,240)
into l_model_tab(1).component_code,
l_model_tab(1).user_item_description
from oe_order_lines_all oel
where oel.line_id = pLineId
and oel.ship_from_org_id = pOrgId;
select nvl(component_code, oel.inventory_item_id) || '-'|| to_char(pConfigId)
into l_model_tab(1).component_code
from oe_order_lines_all oel
where oel.line_id = pLineId
and oel.ship_from_org_id = pOrgId;
update bom_cto_order_demand
set demand_visible = 'N'
where ato_line_id =pLineId;
insert into bom_cto_order_demand(
bcod_line_id,
oe_line_id,
ato_line_id,
inventory_item_id,
organization_id,
required_date,
required_qty,
order_quantity_uom,
parent_demand_type,
header_id,
forecast_visible,
demand_visible,
created_by,
last_updated_by,
last_updated_date,
last_update_login,
program_application_id,
program_update_date
)
select
BOM_CTO_ORDER_DEMAND_S1.nextval,
line_id,
pLineId,
pConfigId,
pOrgId,
schedule_ship_date,
ordered_quantity,
order_quantity_uom,
1,
header_id,
'N',
'Y',
gUserId,
gUserId,
sysdate,
gLoginId,
null,
sysdate
from oe_order_lines_all
where ato_line_id=pLineId
and inventory_item_id=pConfigId;
select line_id, header_id -- bugfix 2840801 : added header_id
into l_config_line_id, l_header_id
from oe_order_lines_all oel
where ato_line_id = pLineId
and inventory_item_id = pConfigId
and item_type_code = 'CONFIG';
select schedule_status_code , booked_flag into v_schedule_Status_code , v_booked_flag
from oe_order_lines_all
where line_id = pLineId ;
select oe_line_id into l_dummy
from bom_cto_order_demand
where oe_line_id=pLineId;
sql_str := 'select 1 from mtl_system_items msi where 1=1 ';
--sql_stmt := 'SELECT MTL_ITEM_REVISIONS_B_S.nextval FROM dual';
insert into mtl_item_revisions_b
(inventory_item_id,
organization_id,
revision,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
implementation_date,
effectivity_date,
OBJECT_VERSION_NUMBER,
REVISION_ID,
REVISION_LABEL --3340844
)
select -- distinct
pConfigId, --Bugfix 5851244: Removing bind variables to make sql static
mp1.organization_id,
mp1.starting_revision,
sysdate,
gUserId, -- last_updated_by
sysdate,
gUserId, -- created_by
gLoginId, -- last_update_login
sysdate,
sysdate,
1, --would be 1 for initial creation of item
MTL_ITEM_REVISIONS_B_S.nextval, -- 3338108 --:x_item_rev_seq --revision_id is generated from sequence
mp1.starting_revision --3340844
from
mtl_parameters mp1,
mtl_system_items m
where m.inventory_item_id = pConfigId
and m.organization_id = mp1.organization_id
and NOT EXISTS
(select NULL
from MTL_ITEM_REVISIONS_B
where inventory_item_id = pConfigId
and organization_id = mp1.organization_id);
oe_debug_pub.add('Inserted into mtl_item_revisions_b for item ' || pConfigId || ' rows ' || SQL%ROWCOUNT );
insert into mtl_item_revisions_tl (
inventory_item_id,
organization_id,
revision_id,
language,
source_lang,
description,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
)
select distinct pConfigId, --Bugfix 6063990: Removing bind variables to make sql static
mp1.organization_id,
mr.revision_id,
l.language_code,
userenv('LANG'),
m.description,
sysdate,
gUserId, --last_updated_by
sysdate,
gUserId, --created_by
gLoginId --last_update_login
from
mtl_parameters mp1,
mtl_system_items_tl m,
bom_cto_src_orgs bcso,
fnd_languages l,
mtl_item_revisions_b mr, --3338108
mtl_parameters mp2 --4109427
where m.inventory_item_id = pModelId
and bcso.model_item_id = m.inventory_item_id
and bcso.line_id = pLineId
and m.organization_id = mp1.organization_id
and mp2.organization_id = bcso.organization_id --4109427
and ((mp1.organization_id = bcso.organization_id) --4109427
or (mp1.organization_id = mp2.master_organization_id)) --4109427
and l.installed_flag In ('I', 'B')
and l.language_code = m.language
and mr.inventory_item_id = pConfigId --3338108
and mr.organization_id = mp1.organization_id --3338108
and NOT EXISTS
(select NULL
from mtl_item_revisions_tl t
where t.inventory_item_id = pConfigId
and t.organization_id = mp1.organization_id
and t.revision_id = mr.revision_id --3338108
and t.language = l.language_code );
oe_debug_pub.add('Inserted into mtl_system_items_tl.');
select substr( attribute_name, instr( attribute_name, '.' )+ 1 ) , control_level
BULK COLLECT
INTO g_attribute_name_tab, g_control_level_tab
from mtl_item_attributes
where control_level = 1 ;
insert into mtl_system_items_b
(inventory_item_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
summary_flag,
enabled_flag,
start_date_active,
end_date_active,
description,
buyer_id,
accounting_rule_id,
invoicing_rule_id,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
purchasing_item_flag,
shippable_item_flag,
customer_order_flag,
internal_order_flag,
service_item_flag,
inventory_item_flag,
eng_item_flag,
inventory_asset_flag,
purchasing_enabled_flag,
customer_order_enabled_flag,
internal_order_enabled_flag,
so_transactions_flag,
mtl_transactions_enabled_flag,
stock_enabled_flag,
bom_enabled_flag,
build_in_wip_flag,
revision_qty_control_code,
item_catalog_group_id,
catalog_status_flag,
returnable_flag,
default_shipping_org,
collateral_flag,
taxable_flag,
allow_item_desc_update_flag,
inspection_required_flag,
receipt_required_flag,
market_price,
hazard_class_id,
rfq_required_flag,
qty_rcv_tolerance,
un_number_id,
price_tolerance_percent,
asset_category_id,
rounding_factor,
unit_of_issue,
enforce_ship_to_location_code,
allow_substitute_receipts_flag,
allow_unordered_receipts_flag,
allow_express_delivery_flag,
days_early_receipt_allowed,
days_late_receipt_allowed,
receipt_days_exception_code,
receiving_routing_id,
invoice_close_tolerance,
receive_close_tolerance,
auto_lot_alpha_prefix,
start_auto_lot_number,
lot_control_code,
shelf_life_code,
shelf_life_days,
serial_number_control_code,
start_auto_serial_number,
auto_serial_alpha_prefix,
source_type,
source_organization_id,
source_subinventory,
expense_account,
encumbrance_account,
restrict_subinventories_code,
unit_weight,
weight_uom_code,
volume_uom_code,
unit_volume,
restrict_locators_code,
location_control_code,
shrinkage_rate,
acceptable_early_days,
planning_time_fence_code,
demand_time_fence_code,
lead_time_lot_size,
std_lot_size,
cum_manufacturing_lead_time,
overrun_percentage,
acceptable_rate_increase,
acceptable_rate_decrease,
cumulative_total_lead_time,
planning_time_fence_days,
demand_time_fence_days,
end_assembly_pegging_flag,
planning_exception_set,
bom_item_type,
pick_components_flag,
replenish_to_order_flag,
base_item_id,
atp_components_flag,
atp_flag,
fixed_lead_time,
variable_lead_time,
wip_supply_locator_id,
wip_supply_type,
wip_supply_subinventory,
primary_uom_code,
primary_unit_of_measure,
allowed_units_lookup_code,
cost_of_sales_account,
sales_account,
default_include_in_rollup_flag,
inventory_item_status_code,
inventory_planning_code,
planner_code,
planning_make_buy_code,
fixed_lot_multiplier,
rounding_control_type,
carrying_cost,
postprocessing_lead_time,
preprocessing_lead_time,
full_lead_time,
order_cost,
mrp_safety_stock_percent,
mrp_safety_stock_code,
min_minmax_quantity,
max_minmax_quantity,
minimum_order_quantity,
fixed_order_quantity,
fixed_days_supply,
maximum_order_quantity,
atp_rule_id,
picking_rule_id,
reservable_type,
positive_measurement_error,
negative_measurement_error,
engineering_ecn_code,
engineering_item_id,
engineering_date,
service_starting_delay,
vendor_warranty_flag,
serviceable_component_flag,
serviceable_product_flag,
base_warranty_service_id,
payment_terms_id,
preventive_maintenance_flag,
primary_specialist_id,
secondary_specialist_id,
serviceable_item_class_id,
time_billable_flag,
material_billable_flag,
expense_billable_flag,
prorate_service_flag,
coverage_schedule_id,
service_duration_period_code,
service_duration,
max_warranty_amount,
response_time_period_code,
response_time_value,
new_revision_code,
tax_code,
must_use_approved_vendor_flag,
safety_stock_bucket_days,
auto_reduce_mps,
costing_enabled_flag,
invoiceable_item_flag,
invoice_enabled_flag,
outside_operation_flag,
outside_operation_uom_type,
auto_created_config_flag,
cycle_count_enabled_flag,
item_type,
model_config_clause_name,
ship_model_complete_flag,
mrp_planning_code,
repetitive_planning_flag,
return_inspection_requirement,
effectivity_control,
request_id,
program_application_id,
program_id,
program_update_date,
comms_nl_trackable_flag, -- bugfix 2200256
default_so_source_type,
create_supply_flag,
-- begin bugfix 2336548
lot_status_enabled,
default_lot_status_id,
serial_status_enabled,
default_serial_status_id,
lot_split_enabled,
lot_merge_enabled,
bulk_picked_flag,
-- end bugfix 2336548
-- begin bugfix 2400609
FINANCING_ALLOWED_FLAG,
EAM_ITEM_TYPE ,
EAM_ACTIVITY_TYPE_CODE,
EAM_ACTIVITY_CAUSE_CODE,
EAM_ACT_NOTIFICATION_FLAG,
EAM_ACT_SHUTDOWN_STATUS,
SUBSTITUTION_WINDOW_CODE,
SUBSTITUTION_WINDOW_DAYS,
PRODUCT_FAMILY_ITEM_ID,
CHECK_SHORTAGES_FLAG,
PLANNED_INV_POINT_FLAG,
OVER_SHIPMENT_TOLERANCE,
UNDER_SHIPMENT_TOLERANCE,
OVER_RETURN_TOLERANCE,
UNDER_RETURN_TOLERANCE,
PURCHASING_TAX_CODE,
OVERCOMPLETION_TOLERANCE_TYPE,
OVERCOMPLETION_TOLERANCE_VALUE,
INVENTORY_CARRY_PENALTY,
OPERATION_SLACK_PENALTY,
UNIT_LENGTH,
UNIT_WIDTH,
UNIT_HEIGHT,
LOT_TRANSLATE_ENABLED,
CONTAINER_ITEM_FLAG,
VEHICLE_ITEM_FLAG,
DIMENSION_UOM_CODE,
SECONDARY_UOM_CODE,
MAXIMUM_LOAD_WEIGHT,
MINIMUM_FILL_PERCENT,
CONTAINER_TYPE_CODE,
INTERNAL_VOLUME,
EQUIPMENT_TYPE,
INDIVISIBLE_FLAG,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
DUAL_UOM_CONTROL,
DUAL_UOM_DEVIATION_HIGH,
DUAL_UOM_DEVIATION_LOW,
CONTRACT_ITEM_TYPE_CODE,
SUBSCRIPTION_DEPEND_FLAG,
SERV_REQ_ENABLED_CODE,
SERV_BILLING_ENABLED_FLAG,
RELEASE_TIME_FENCE_CODE, -- 2898851
RELEASE_TIME_FENCE_DAYS, -- 2898851
DEFECT_TRACKING_ON_FLAG, -- 2858080
SERV_IMPORTANCE_LEVEL,
-- end bugfix 2400609
WEB_STATUS , --bugfix 2727983
tracking_quantity_ind, /* Additional Attributes for Item in patchset J */
ont_pricing_qty_source,
approval_status ,
vmi_minimum_units,
vmi_minimum_days,
vmi_maximum_units,
vmi_maximum_days,
vmi_fixed_order_quantity,
so_authorization_flag,
consigned_flag,
asn_autoexpire_flag,
vmi_forecast_type,
forecast_horizon,
days_tgt_inv_supply,
days_tgt_inv_window,
days_max_inv_supply,
days_max_inv_window,
critical_component_flag,
drp_planned_flag,
exclude_from_budget_flag,
convergence,
continous_transfer,
divergence,
--begin r12,4574899
lot_divisible_flag,
grade_control_flag,
child_lot_flag,
child_lot_validation_flag,
copy_lot_attribute_flag,
recipe_enabled_flag,
process_quality_enabled_flag,
process_execution_enabled_flag,
process_costing_enabled_flag,
hazardous_material_flag,
preposition_point,
repair_program,
outsourced_assembly
-- end rl2,4574899
)
select /*+ USE_NL(MP1) */
distinct
p_config_id,
m.organization_id,
sysdate,
gUserId, -- last_updated_by
sysdate,
gUserId, -- created_by
gLoginId , -- last_update_login
decode( get_attribute_control( 'summary_flag') , 1 , config.summary_flag, m.summary_flag),
decode( get_attribute_control( 'enabled_flag' ) , 1 , config.enabled_flag , m.enabled_flag),
decode( get_attribute_control( 'start_date_active'), 1 , config.start_date_active, m.start_date_active) ,
decode( get_attribute_control( 'end_date_active'), 1 , config.end_date_active, m.end_date_active) ,
decode( get_attribute_control( 'description' ) , 1 , config.description, m.description) ,
decode( get_attribute_control( 'buyer_id') , 1 , config.buyer_id, m.buyer_id) ,
decode( get_attribute_control( 'accounting_rule_id' ) , 1 , config.accounting_rule_id, m.accounting_rule_id) ,
decode( get_attribute_control( 'invoicing_rule_id' ) , 1 , config.invoicing_rule_id, m.invoicing_rule_id) ,
config.segment1,
config.segment2,
config.segment3,
config.segment4,
config.segment5,
config.segment6,
config.segment7,
config.segment8,
config.segment9,
config.segment10,
config.segment11,
config.segment12,
config.segment13,
config.segment14,
config.segment15,
config.segment16,
config.segment17,
config.segment18,
config.segment19,
config.segment20,
decode( get_attribute_control( 'attribute_category'), 1 , config.attribute_category, m.attribute_category),
m.attribute1,
m.attribute2,
m.attribute3,
m.attribute4,
m.attribute5,
m.attribute6,
m.attribute7,
m.attribute8,
m.attribute9,
m.attribute10,
m.attribute11,
m.attribute12,
m.attribute13,
m.attribute14,
m.attribute15,
'Y', -- purchasing_item_flag,
'Y', -- Shippable Flag
'Y', -- CUSTOMER_ORDER_FLAG
'Y', -- INTERNAL_ORDER_FLAG
decode( get_attribute_control( 'service_item_flag' ), 1, config.service_item_flag , m.service_item_flag) ,
'Y', -- INVENTORY_ITEM_FLAG
decode( get_attribute_control( 'eng_item_flag' ) , 1 , config.eng_item_flag , m.eng_item_flag) ,
decode( get_attribute_control( 'inventory_asset_flag' ) , 1 , config.inventory_asset_flag , m.inventory_asset_flag) ,
'Y', -- purchasing_enabled_flag,
'Y', -- CUSTOMER_ORDER_ENABLED_FLAG
'Y', -- INTERNAL_ORDER_ENABLED_FLAG
'Y', -- SO_TRANSACTIONS_FLAG
'Y', -- MTL_TRANSACTIONS_ENABLED_FLAG
'Y', -- STOCK_ENABLED_FLAG
'Y', -- BOM_ENABLED_FLAG
'Y', -- BUILD_IN_WIP_FLAG
decode( get_attribute_control( 'revision_qty_control_code' ) , 1 , config.revision_qty_control_code , m.revision_qty_control_code) ,
decode( get_attribute_control( 'item_catalog_group_id' ) , 1 , config.item_catalog_group_id, m.item_catalog_group_id) , -- check, earlier it was always from mfg org
decode( get_attribute_control( 'catalog_status_flag' ) , 1 , config.catalog_status_flag, m.catalog_status_flag) ,
decode( get_attribute_control( 'returnable_flag' ) , 1 , config.returnable_flag, m.returnable_flag) ,
decode( get_attribute_control( 'default_shipping_org' ) , 1, config.default_shipping_org, m.default_shipping_org),
decode( get_attribute_control( 'collateral_flag') , 1 , config.collateral_flag , m.collateral_flag) ,
decode( get_attribute_control( 'taxable_flag' ) , 1 , config.taxable_flag, m.taxable_flag) ,
decode( get_attribute_control( 'allow_item_desc_update_flag' ) , 1, config.allow_item_desc_update_flag, m.allow_item_desc_update_flag),
decode( get_attribute_control( 'inspection_required_flag' ), 1 , config.inspection_required_flag , m.inspection_required_flag),
decode( get_attribute_control( 'receipt_required_flag' ), 1, config.receipt_required_flag, m.receipt_required_flag) ,
decode( get_attribute_control( 'market_price' ) , 1 , config.market_price, m.market_price) ,
decode( get_attribute_control( 'hazard_class_id' ), 1 , config.hazard_class_id, m.hazard_class_id),
decode( get_attribute_control( 'rfq_required_flag'), 1 , config.rfq_required_flag, m.rfq_required_flag),
decode( get_attribute_control( 'qty_rcv_tolerance'), 1, config.qty_rcv_tolerance, m.qty_rcv_tolerance),
decode( get_attribute_control( 'un_number_id' ), 1 , config.un_number_id, m.un_number_id),
decode( get_attribute_control( 'price_tolerance_percent'), 1 , config.price_tolerance_percent, m.price_tolerance_percent) ,
decode( get_attribute_control( 'asset_category_id') , 1 , config.asset_category_id, m.asset_category_id) ,
decode( get_attribute_control( 'rounding_factor' ) , 1 , config.rounding_factor, m.rounding_factor) ,
decode( get_attribute_control( 'unit_of_issue') , 1 , config.unit_of_issue, m.unit_of_issue) ,
decode( get_attribute_control( 'enforce_ship_to_location_code' ) , 1 , config.enforce_ship_to_location_code , m.enforce_ship_to_location_code),
decode( get_attribute_control( 'allow_substitute_receipts_flag' ) , 1 , config.allow_substitute_receipts_flag, m.allow_substitute_receipts_flag) ,
decode( get_attribute_control( 'allow_unordered_receipts_flag' ) , 1 , config.allow_unordered_receipts_flag, m.allow_unordered_receipts_flag) ,
decode( get_attribute_control( 'allow_express_delivery_flag' ) ,1 , config.allow_express_delivery_flag, m.allow_express_delivery_flag) ,
decode( get_attribute_control( 'days_early_receipt_allowed') , 1, config.days_early_receipt_allowed, m.days_early_receipt_allowed) ,
decode( get_attribute_control( 'days_late_receipt_allowed' ) , 1 , config.days_late_receipt_allowed , m.days_late_receipt_allowed) ,
decode( get_attribute_control( 'receipt_days_exception_code') , 1 , config.receipt_days_exception_code, m.receipt_days_exception_code) ,
decode( get_attribute_control( 'receiving_routing_id' ) , 1 , config.receiving_routing_id, m.receiving_routing_id),
decode( get_attribute_control( 'invoice_close_tolerance'), 1, config.invoice_close_tolerance, m.invoice_close_tolerance) ,
decode( get_attribute_control( 'receive_close_tolerance') , 1 , config.receive_close_tolerance , m.receive_close_tolerance) ,
decode( get_attribute_control( 'auto_lot_alpha_prefix') , 1, config.auto_lot_alpha_prefix, m.auto_lot_alpha_prefix) ,
decode( get_attribute_control( 'start_auto_lot_number') , 1, config.start_auto_lot_number, m.start_auto_lot_number) ,
decode( get_attribute_control( 'lot_control_code') ,1 , config.lot_control_code, m.lot_control_code) ,
decode( get_attribute_control( 'shelf_life_code'), 1 , config.shelf_life_code, m.shelf_life_code) ,
decode( get_attribute_control( 'shelf_life_days') , 1, config.shelf_life_days, m.shelf_life_days) ,
decode( get_attribute_control( 'serial_number_control_code' ) ,1, config.serial_number_control_code, m.serial_number_control_code) ,
decode( get_attribute_control( 'start_auto_serial_number' ) , 1 , config.start_auto_serial_number, m.start_auto_serial_number) ,
decode( get_attribute_control( 'auto_serial_alpha_prefix') ,1 , config.auto_serial_alpha_prefix, m.auto_serial_alpha_prefix) ,
decode( get_attribute_control( 'source_type' ) ,1 , config.source_type, m.source_type) ,
decode( get_attribute_control( 'source_organization_id') , 1 , config.source_organization_id, m.source_organization_id) ,
decode( get_attribute_control( 'source_subinventory') ,1 , config.source_subinventory, m.source_subinventory) ,
decode( get_attribute_control( 'expense_account') , 1, config.expense_account, m.expense_account) ,
decode( get_attribute_control( 'encumbrance_account') , 1 , config.encumbrance_account, m.encumbrance_account) ,
decode( get_attribute_control( 'restrict_subinventories_code' ) , 1 , config.restrict_subinventories_code, m.restrict_subinventories_code) ,
-- bugfix 2301167 : we will calculate the unit weight/vol later..
null, -- m.unit_weight,
null, -- m.weight_uom_code,
null, -- m.volume_uom_code,
null, -- m.unit_volume,
-- end bugfix 2301167
decode( get_attribute_control( 'restrict_locators_code'), 1, config.restrict_locators_code, m.restrict_locators_code) ,
decode( get_attribute_control( 'location_control_code') , 1 , config.location_control_code, m.location_control_code) ,
decode( get_attribute_control( 'shrinkage_rate' ) , 1, config.shrinkage_rate, m.shrinkage_rate) ,
decode( get_attribute_control( 'acceptable_early_days') , 1 , config.acceptable_early_days, m.acceptable_early_days) ,
decode( get_attribute_control( 'planning_time_fence_code' ) , 1 , config.planning_time_fence_code, m.planning_time_fence_code) ,
decode( get_attribute_control( 'demand_time_fence_code') , 1 , config.demand_time_fence_code, m.demand_time_fence_code) ,
decode( get_attribute_control( 'lead_time_lot_size') ,1, config.lead_time_lot_size, m.lead_time_lot_size) ,
decode( get_attribute_control( 'std_lot_size' ) , 1, config.std_lot_size, m.std_lot_size) ,
decode( get_attribute_control( 'cum_manufacturing_lead_time' ) , 1 , config.cum_manufacturing_lead_time, m.cum_manufacturing_lead_time) ,
decode( get_attribute_control( 'overrun_percentage') , 1, config.overrun_percentage, m.overrun_percentage) ,
decode( get_attribute_control( 'acceptable_rate_increase'), 1, config.acceptable_rate_increase, m.acceptable_rate_increase) ,
decode( get_attribute_control( 'acceptable_rate_decrease') , 1 , config.acceptable_rate_decrease, m.acceptable_rate_decrease) ,
decode( get_attribute_control( 'cumulative_total_lead_time' ) , 1 , config.cumulative_total_lead_time, m.cumulative_total_lead_time) ,
decode( get_attribute_control( 'planning_time_fence_days' ) , 1, config.planning_time_fence_days, m.planning_time_fence_days) ,
decode( get_attribute_control( 'demand_time_fence_days') , 1, config.demand_time_fence_days, m.demand_time_fence_days) ,
decode( get_attribute_control( 'end_assembly_pegging_flag') ,1 , config.end_assembly_pegging_flag , m.end_assembly_pegging_flag) ,
decode( get_attribute_control( 'planning_exception_set' ) , 1 , config.planning_exception_set, m.planning_exception_set) ,
4, -- BOM_ITEM_TYPE : standard
'N', -- PICK_COMPONENTS_FLAG
'Y', -- REPLENISH_TO_ORDER_FLAG
p_model_id, -- Base Model ID
decode( get_attribute_control( 'atp_components_flag') , 1, config.atp_components_flag, evaluate_atp_attributes( m.atp_flag, m.atp_components_flag )) ,
decode( get_attribute_control( 'atp_flag') , 1, config.atp_flag, get_atp_flag) ,
decode( get_attribute_control( 'fixed_lead_time') ,1 , config.fixed_lead_time, m.fixed_lead_time) ,
decode( get_attribute_control( 'variable_lead_time') , 1 , config.variable_lead_time, m.variable_lead_time) ,
decode( get_attribute_control( 'wip_supply_locator_id' ) , 1, config.wip_supply_locator_id, m.wip_supply_locator_id) ,
decode( get_attribute_control( 'wip_supply_type' ) , 1 , config.wip_supply_type , m.wip_supply_type) ,
decode( get_attribute_control( 'wip_supply_subinventory' ) , 1 , config.wip_supply_subinventory, m.wip_supply_subinventory) ,
decode( get_attribute_control( 'primary_uom_code' ) , 1 , config.primary_uom_code, m.primary_uom_code) ,
decode( get_attribute_control( 'primary_unit_of_measure' ) , 1 , config.primary_unit_of_measure, m.primary_unit_of_measure) ,
decode( get_attribute_control( 'allowed_units_lookup_code' ) , 1 , config.allowed_units_lookup_code, m.allowed_units_lookup_code) ,
decode( get_attribute_control( 'cost_of_sales_account' ) , 1 , config.cost_of_sales_account, m.cost_of_sales_account) ,
decode( get_attribute_control( 'sales_account' ) , 1, config.sales_account, m.sales_account) ,
'Y', -- DEFAULT_INCLUDE_IN_ROLLUP_FLAG
decode( get_attribute_control( 'inventory_item_status_code' ) , 1 , config.inventory_item_status_code, m.inventory_item_status_code) ,
decode( get_attribute_control( 'inventory_planning_code') , 1, config.inventory_planning_code, m.inventory_planning_code) ,
decode( get_attribute_control( 'planner_code') , 1 , config.planner_code, m.planner_code) ,
decode( get_attribute_control( 'planning_make_buy_code' ) , 1 , config.planning_make_buy_code, m.planning_make_buy_code) ,
decode( get_attribute_control( 'fixed_lot_multiplier' ) , 1 , config.fixed_lot_multiplier, m.fixed_lot_multiplier) ,
decode( get_attribute_control( 'rounding_control_type' ) , 1, config.rounding_control_type, m.rounding_control_type) ,
decode( get_attribute_control( 'carrying_cost' ) ,1 , config.carrying_cost, m.carrying_cost) ,
decode( get_attribute_control( 'postprocessing_lead_time') , 1, config.postprocessing_lead_time, m.postprocessing_lead_time) ,
decode( get_attribute_control( 'preprocessing_lead_time' ) , 1 , config.preprocessing_lead_time, m.preprocessing_lead_time) ,
decode( get_attribute_control( 'full_lead_time') , 1, config.full_lead_time, m.full_lead_time) ,
decode( get_attribute_control( 'order_cost') , 1, config.order_cost, m.order_cost) ,
decode( get_attribute_control( 'mrp_safety_stock_percent') , 1, config.mrp_safety_stock_percent, m.mrp_safety_stock_percent) ,
decode( get_attribute_control( 'mrp_safety_stock_code' ) , 1, config.mrp_safety_stock_code, m.mrp_safety_stock_code) ,
decode( get_attribute_control( 'min_minmax_quantity' ) , 1, config.min_minmax_quantity, m.min_minmax_quantity) ,
decode( get_attribute_control( 'max_minmax_quantity' ) , 1 , config.max_minmax_quantity, m.max_minmax_quantity) ,
decode( get_attribute_control( 'minimum_order_quantity' ) , 1 , config.minimum_order_quantity , m.minimum_order_quantity) ,
decode( get_attribute_control( 'fixed_order_quantity' ) , 1 , config.fixed_order_quantity, m.fixed_order_quantity) ,
decode( get_attribute_control( 'fixed_days_supply' ) , 1 , config.fixed_days_supply, m.fixed_days_supply) ,
decode( get_attribute_control( 'maximum_order_quantity' ) , 1, config.maximum_order_quantity, m.maximum_order_quantity) ,
decode( get_attribute_control( 'atp_rule_id' ) , 1, config.atp_rule_id, m.atp_rule_id) ,
decode( get_attribute_control( 'picking_rule_id' ) , 1, config.picking_rule_id, m.picking_rule_id) ,
1, -- m.reservable_type
decode( get_attribute_control( 'positive_measurement_error' ) , 1, config.positive_measurement_error, m.positive_measurement_error) ,
decode( get_attribute_control( 'negative_measurement_error' ) , 1, config.negative_measurement_error, m.negative_measurement_error) ,
decode( get_attribute_control( 'engineering_ecn_code' ) , 1 , config.engineering_ecn_code, m.engineering_ecn_code) ,
decode( get_attribute_control( 'engineering_item_id' ) , 1 , config.engineering_item_id, m.engineering_item_id) ,
decode( get_attribute_control( 'engineering_date' ) , 1, config.engineering_date, m.engineering_date) ,
decode( get_attribute_control( 'service_starting_delay') , 1 , config.service_starting_delay, m.service_starting_delay) ,
decode( get_attribute_control( 'vendor_warranty_flag') , 1 , config.vendor_warranty_flag, m.vendor_warranty_flag) ,
decode( get_attribute_control( 'serviceable_component_flag' ) , 1, config.serviceable_component_flag , m.serviceable_component_flag) ,
decode( get_attribute_control( 'serviceable_product_flag' ) , 1, config.serviceable_product_flag , m.serviceable_product_flag) ,
decode( get_attribute_control( 'base_warranty_service_id' ) ,1 , config.base_warranty_service_id, m.base_warranty_service_id) ,
decode( get_attribute_control( 'payment_terms_id' ) , 1 , config.payment_terms_id, m.payment_terms_id) ,
decode( get_attribute_control( 'preventive_maintenance_flag') , 1, config.preventive_maintenance_flag, m.preventive_maintenance_flag) ,
decode( get_attribute_control( 'primary_specialist_id') , 1 , config.primary_specialist_id, m.primary_specialist_id),
decode( get_attribute_control( 'secondary_specialist_id') , 1 , config.secondary_specialist_id, m.secondary_specialist_id) ,
decode( get_attribute_control( 'serviceable_item_class_id') , 1, config.serviceable_item_class_id, m.serviceable_item_class_id) ,
decode( get_attribute_control( 'time_billable_flag' ) , 1 , config.time_billable_flag, m.time_billable_flag) ,
decode( get_attribute_control( 'material_billable_flag' ) , 1, config.material_billable_flag, m.material_billable_flag) ,
decode( get_attribute_control( 'expense_billable_flag' ) , 1 , config.expense_billable_flag , m.expense_billable_flag) ,
decode( get_attribute_control( 'prorate_service_flag' ) , 1, config.prorate_service_flag, m.prorate_service_flag) ,
decode( get_attribute_control( 'coverage_schedule_id' ) , 1, config.coverage_schedule_id, m.coverage_schedule_id) ,
decode( get_attribute_control( 'service_duration_period_code' ) , 1, config.service_duration_period_code, m.service_duration_period_code) ,
decode( get_attribute_control( 'service_duration') , 1, config.service_duration, m.service_duration) ,
decode( get_attribute_control( 'max_warranty_amount' ) , 1 , config.max_warranty_amount, m.max_warranty_amount) ,
decode( get_attribute_control( 'response_time_period_code' ) , 1, config.response_time_period_code, m.response_time_period_code) ,
decode( get_attribute_control( 'response_time_value') , 1, config.response_time_value, m.response_time_value) ,
decode( get_attribute_control( 'new_revision_code' ) , 1 , config.new_revision_code, m.new_revision_code) ,
decode( get_attribute_control( 'tax_code') , 1, config.tax_code, m.tax_code) ,
decode( get_attribute_control( 'must_use_approved_vendor_flag' ) , 1, config.must_use_approved_vendor_flag, m.must_use_approved_vendor_flag) ,
decode( get_attribute_control( 'safety_stock_bucket_days' ) , 1, config.safety_stock_bucket_days, m.safety_stock_bucket_days) ,
decode( get_attribute_control( 'auto_reduce_mps') , 1, config.auto_reduce_mps, m.auto_reduce_mps) ,
decode( get_attribute_control( 'costing_enabled_flag' ) , 1, config.costing_enabled_flag, m.costing_enabled_flag) ,
decode( get_attribute_control( 'invoiceable_item_flag' ) , 1, config.invoiceable_item_flag, m.invoiceable_item_flag ) ,
decode( get_attribute_control( 'invoice_enabled_flag' ) , 1 , config.invoice_enabled_flag, m.invoice_enabled_flag ) ,
decode( get_attribute_control( 'outside_operation_flag') , 1, config.outside_operation_flag, m.outside_operation_flag) ,
decode( get_attribute_control( 'outside_operation_uom_type' ) , 1, config.outside_operation_uom_type, m.outside_operation_uom_type) ,
'Y', -- auto created config flag
decode( get_attribute_control( 'cycle_count_enabled_flag') , 1 , config.cycle_count_enabled_flag, m.cycle_count_enabled_flag) ,
p_lItemType,
decode( get_attribute_control( 'model_config_clause_name') ,1 , config.model_config_clause_name, m.model_config_clause_name) ,
decode( get_attribute_control( 'ship_model_complete_flag') ,1 , config.ship_model_complete_flag, m.ship_model_complete_flag) ,
decode( get_attribute_control( 'mrp_planning_code' ) , 1 , config.mrp_planning_code, m.mrp_planning_code) , -- earlier it was always from one org only
decode( get_attribute_control( 'repetitive_planning_flag' ) , 1, config.repetitive_planning_flag, m.repetitive_planning_flag) , -- earlier it was always from one org only
decode( get_attribute_control( 'return_inspection_requirement' ) , 1 , config.return_inspection_requirement, m.return_inspection_requirement) ,
nvl( decode( get_attribute_control( 'effectivity_control') , 1, config.effectivity_control, m.effectivity_control) , 1),
null, -- req_id
null, -- prg_appid
null, -- prg_id
sysdate,
decode( get_attribute_control( 'comms_nl_trackable_flag') , 1, config.comms_nl_trackable_flag, m.comms_nl_trackable_flag) , -- bugfix 2200256
nvl( decode( get_attribute_control( 'default_so_source_type') , 1 , config.default_so_source_type, m.default_so_source_type) ,'INTERNAL'),
nvl( decode( get_attribute_control( 'create_supply_flag') , 1, config.create_supply_flag, m.create_supply_flag) , 'Y'),
-- begin bugfix 2336548
decode( get_attribute_control( 'lot_status_enabled') , 1, config.lot_status_enabled, m.lot_status_enabled) ,
decode( get_attribute_control( 'default_lot_status_id' ) , 1, config.default_lot_status_id, m.default_lot_status_id) ,
decode( get_attribute_control( 'serial_status_enabled') , 1, config.serial_status_enabled, m.serial_status_enabled) ,
decode( get_attribute_control( 'default_serial_status_id') ,1 , config.default_serial_status_id, m.default_serial_status_id) ,
decode( get_attribute_control( 'lot_split_enabled') , 1, config.lot_split_enabled, m.lot_split_enabled) ,
decode( get_attribute_control( 'lot_merge_enabled') ,1 , config.lot_merge_enabled, m.lot_merge_enabled) ,
decode( get_attribute_control( 'bulk_picked_flag' ) , 1 , config.bulk_picked_flag, m.bulk_picked_flag) ,
-- end bugfix 2336548
-- begin bugfix 2400609
decode( get_attribute_control( 'financing_allowed_flag') , 1, config.financing_allowed_flag, m.FINANCING_ALLOWED_FLAG) ,
decode( get_attribute_control( 'eam_item_type') , 1 , config.eam_item_type, m.EAM_ITEM_TYPE ) ,
decode( get_attribute_control( 'eam_activity_type_code') , 1 , config.eam_activity_type_code, m.EAM_ACTIVITY_TYPE_CODE) ,
decode( get_attribute_control( 'eam_activity_cause_code') , 1, config.eam_activity_cause_code, m.EAM_ACTIVITY_CAUSE_CODE) ,
decode( get_attribute_control( 'eam_act_notification_flag') , 1, config.eam_act_notification_flag, m.EAM_ACT_NOTIFICATION_FLAG) ,
decode( get_attribute_control( 'eam_act_shutdown_status') , 1, config.eam_act_shutdown_status, m.EAM_ACT_SHUTDOWN_STATUS) ,
decode( get_attribute_control( 'substitution_window_code') , 1, config.substitution_window_code, m.SUBSTITUTION_WINDOW_CODE) ,
decode( get_attribute_control( 'substitution_window_days') , 1, config.substitution_window_days, m.SUBSTITUTION_WINDOW_DAYS) ,
null, --5385901 decode( get_attribute_control( 'product_family_item_id') , 1, config.product_family_item_id, m.PRODUCT_FAMILY_ITEM_ID) ,
decode( get_attribute_control( 'check_shortages_flag') , 1, config.check_shortages_flag, m.CHECK_SHORTAGES_FLAG) ,
decode( get_attribute_control( 'planned_inv_point_flag') , 1, config.planned_inv_point_flag, m.PLANNED_INV_POINT_FLAG) ,
decode( get_attribute_control( 'over_shipment_tolerance') , 1, config.over_shipment_tolerance, m.OVER_SHIPMENT_TOLERANCE) ,
decode( get_attribute_control( 'under_shipment_tolerance') , 1, config.under_shipment_tolerance, m.UNDER_SHIPMENT_TOLERANCE) ,
decode( get_attribute_control( 'over_return_tolerance') , 1, config.over_return_tolerance, m.OVER_RETURN_TOLERANCE) ,
decode( get_attribute_control( 'under_return_tolerance') , 1, config.under_return_tolerance, m.UNDER_RETURN_TOLERANCE) ,
decode( get_attribute_control( 'purchasing_tax_code') , 1, config.purchasing_tax_code, m.PURCHASING_TAX_CODE) ,
decode( get_attribute_control( 'overcompletion_tolerance_type') , 1, config.overcompletion_tolerance_type, m.OVERCOMPLETION_TOLERANCE_TYPE) ,
decode( get_attribute_control( 'overcompletion_tolerance_value') , 1, config.overcompletion_tolerance_value, m.OVERCOMPLETION_TOLERANCE_VALUE) ,
decode( get_attribute_control( 'inventory_carry_penalty'), 1, config.inventory_carry_penalty, m.INVENTORY_CARRY_PENALTY) ,
decode( get_attribute_control( 'operation_slack_penalty') ,1, config.operation_slack_penalty, m.OPERATION_SLACK_PENALTY) ,
decode( get_attribute_control( 'unit_length') , 1, config.unit_length, m.UNIT_LENGTH) ,
decode( get_attribute_control( 'unit_width' ) , 1, config.unit_width, m.UNIT_WIDTH) ,
decode( get_attribute_control( 'unit_height') , 1, config.unit_height, m.UNIT_HEIGHT) ,
decode( get_attribute_control( 'lot_translate_enabled') , 1, config.lot_translate_enabled, m.LOT_TRANSLATE_ENABLED) ,
decode( get_attribute_control( 'container_item_flag') , 1, config.container_item_flag, m.CONTAINER_ITEM_FLAG) ,
decode( get_attribute_control( 'vehicle_item_flag') , 1, config.vehicle_item_flag, m.VEHICLE_ITEM_FLAG) ,
decode( get_attribute_control( 'dimension_uom_code') , 1, config.dimension_uom_code, m.DIMENSION_UOM_CODE) ,
decode( get_attribute_control( 'secondary_uom_code') , 1, config.secondary_uom_code, m.SECONDARY_UOM_CODE) ,
decode( get_attribute_control( 'maximum_load_weight') , 1, config.maximum_load_weight, m.MAXIMUM_LOAD_WEIGHT) ,
decode( get_attribute_control( 'minimum_fill_percent') , 1, config.minimum_fill_percent, m.MINIMUM_FILL_PERCENT) ,
decode( get_attribute_control( 'container_type_code') , 1, config.container_type_code, m.CONTAINER_TYPE_CODE) ,
decode( get_attribute_control( 'internal_volume') , 1, config.internal_volume, m.INTERNAL_VOLUME) ,
decode( get_attribute_control( 'equipment_type') , 1, config.equipment_type , m.EQUIPMENT_TYPE) ,
decode( get_attribute_control( 'indivisible_flag') , 1, config.indivisible_flag, m.INDIVISIBLE_FLAG) ,
decode( get_attribute_control( 'global_attribute_category'), 1, config.global_attribute_category, m.GLOBAL_ATTRIBUTE_CATEGORY) ,
m.GLOBAL_ATTRIBUTE1,
m.GLOBAL_ATTRIBUTE2,
m.GLOBAL_ATTRIBUTE3,
m.GLOBAL_ATTRIBUTE4,
m.GLOBAL_ATTRIBUTE5,
m.GLOBAL_ATTRIBUTE6,
m.GLOBAL_ATTRIBUTE7,
m.GLOBAL_ATTRIBUTE8,
m.GLOBAL_ATTRIBUTE9,
m.GLOBAL_ATTRIBUTE10,
decode( get_attribute_control( 'dual_uom_control') , 1, config.dual_uom_control, m.DUAL_UOM_CONTROL) ,
decode( get_attribute_control( 'dual_uom_deviation_high') , 1, config.dual_uom_deviation_high, m.DUAL_UOM_DEVIATION_HIGH) ,
decode( get_attribute_control( 'dual_uom_deviation_low') , 1, config.dual_uom_deviation_low, m.DUAL_UOM_DEVIATION_LOW) ,
decode( get_attribute_control( 'contract_item_type_code') , 1, config.contract_item_type_code, m.CONTRACT_ITEM_TYPE_CODE) ,
decode( get_attribute_control( 'subscription_depend_flag') , 1 , config.subscription_depend_flag, m.SUBSCRIPTION_DEPEND_FLAG) ,
decode( get_attribute_control( 'serv_req_enabled_code' ) , 1, config.serv_req_enabled_code, m.SERV_REQ_ENABLED_CODE) ,
decode( get_attribute_control( 'serv_billing_enabled_flag') , 1, config.serv_billing_enabled_flag, m.SERV_BILLING_ENABLED_FLAG) ,
decode( get_attribute_control( 'release_time_fence_code') , 1, config.release_time_fence_code, m.RELEASE_TIME_FENCE_CODE) ,
decode( get_attribute_control( 'release_time_fence_days' ) ,1, config.release_time_fence_days, m.RELEASE_TIME_FENCE_DAYS) ,
decode( get_attribute_control( 'defect_tracking_on_flag') , 1, config.defect_tracking_on_flag, m.DEFECT_TRACKING_ON_FLAG) ,
decode( get_attribute_control( 'serv_importance_level'), 1, config.serv_importance_level, m.SERV_IMPORTANCE_LEVEL) ,
decode( get_attribute_control( 'web_status') , 1, config.web_status, m.web_status) , -- bugfix 2727983
decode( get_attribute_control( 'tracking_quantity_ind' ) , 1 , config.tracking_quantity_ind, nvl( m.tracking_quantity_ind , 'P' )),
decode( get_attribute_control( 'ont_pricing_qty_source' ) , 1 , config.ont_pricing_qty_source, nvl( m.ont_pricing_qty_source, 'P')) ,
decode( get_attribute_control( 'approval_status' ) , 1 , config.approval_status, m.approval_status) ,
decode( get_attribute_control( 'vmi_minimum_units' ) , 1, config.vmi_minimum_units, m.vmi_minimum_units) ,
decode( get_attribute_control( 'vmi_minimum_days' ) ,1 , config.vmi_minimum_days, m.vmi_minimum_days) ,
decode( get_attribute_control( 'vmi_maximum_units' ) , 1 , config.vmi_maximum_units, m.vmi_maximum_units) ,
decode( get_attribute_control( 'vmi_maximum_days' ) , 1 , config.vmi_maximum_days, m.vmi_maximum_days ) ,
decode( get_attribute_control( 'vmi_fixed_order_quantity' ) , 1 , config.vmi_fixed_order_quantity, m.vmi_fixed_order_quantity) ,
decode( get_attribute_control( 'so_authorization_flag' ) , 1, config.so_authorization_flag, m.so_authorization_flag ) ,
decode( get_attribute_control( 'consigned_flag' ) , 1, config.consigned_flag, m.consigned_flag) ,
decode( get_attribute_control( 'asn_autoexpire_flag' ) , 1 , config.asn_autoexpire_flag, m.asn_autoexpire_flag ) ,
decode( get_attribute_control( 'vmi_forecast_type' ) , 1 , config.vmi_forecast_type, m.vmi_forecast_type) ,
decode( get_attribute_control( 'forecast_horizon' ) , 1, config.forecast_horizon, m.forecast_horizon ) ,
decode( get_attribute_control( 'days_tgt_inv_supply' ) , 1, config.days_tgt_inv_supply, m.days_tgt_inv_supply ) ,
decode( get_attribute_control( 'days_tgt_inv_window' ) , 1 , config.days_tgt_inv_window, m.days_tgt_inv_window ) ,
decode( get_attribute_control( 'days_max_inv_supply' ) , 1, config.days_max_inv_supply, m.days_max_inv_supply ) ,
decode( get_attribute_control( 'days_max_inv_window' ) , 1 , config.days_max_inv_window, m.days_max_inv_window ) ,
decode( get_attribute_control( 'critical_component_flag' ) , 1, config.critical_component_flag, m.critical_component_flag) ,
decode( get_attribute_control( 'drp_planned_flag' ) ,1 , config.drp_planned_flag, m.drp_planned_flag ) ,
decode( get_attribute_control( 'exclude_from_budget_flag' ) , 1 , config.exclude_from_budget_flag, m.exclude_from_budget_flag) ,
decode( get_attribute_control( 'convergence' ) , 1 , config.convergence, m.convergence ) ,
decode( get_attribute_control( 'continous_transfer' ) , 1, config.continous_transfer, m.continous_transfer ) ,
decode( get_attribute_control( 'divergence' ) , 1 , config.divergence, m.divergence ),
--begin r12,4574899
nvl(m.lot_divisible_flag, 'N'), --Bugfix 6343429
'N',
'N',
'N',
'N',
'N',
'N',
'N',
'N',
'N',
'N',
3, --repair_program
2 --outsourced_assembly
--end r12,4574899
from
mtl_system_items_b m, -- Model
mtl_system_items_b config,
bom_cto_order_lines bcol,
bom_cto_src_orgs bcso
where m.inventory_item_id = p_model_id
and bcso.model_item_id = p_model_id
and bcso.line_id = p_line_id
and bcso.line_id = bcol.line_id
and bcol.config_item_id = config.inventory_item_id
and config.organization_id = bcol.ship_from_org_id
and m.organization_id = bcso.organization_id
and NOT EXISTS
(select NULL
from mtl_system_items_b
where inventory_item_id = p_config_id
and organization_id = bcso.organization_id);