[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| update config line status |
| after matched item is linked |
| 05/09/2002 Sushant Sawant |
| BUGFIX#2367720 |
| match_inquiry should return |
| available qty as 0 for |
| dropshipped items |
| |
| 10/25/2002 Kundan Sarkar Bugfix 2644849 (2620282 in br)|
| Passing bom revision info |
| |
| 10/31/2002 Sushant Sawant Added Enhanced costing functionality
| for matched items .
|
|
|
| Modified : 13-APR-2004 Sushant Sawant
| Fixed Bug 3523260
| Match and Reserve should work for unbooked orders that are scheduled.
| No reservation should take place for unbooked orders.
|
|
| Modified : 14-MAY-2004 Sushant Sawant
| Fixed bug 3484511.
|
| Modified : Kiran Konada
| Fixed bug 3692727
| ship_from_org_id was bein inserted during call to match_configured_item
| (-->calls CTOMCFGB.insert_into_bcol_gt)
| as ship_from_org_id attribute was not initialzed , during runtime
| we were landing into datafound at element(1) of ship_from_org_id attr
| Modified the insert statment to populate null vale for attr shiP-from_org_id
*****************************************************************************
Dependencies introduced
Date : Patchset : Introduced by : File : Reason
10/31/02 11.5.9 Kundan Sarkar CTORCFGS.pls 2620282
10/31/02 11.5.9 Kundan Sarkar CTORCFGB.pls
=============================================================================*/
/*****************************************************************************
Function: match_inquiry
Description:
This function is called from the 'Match' action from the
Sales Order Pad form.
p_model_line_id - top model line id from oe_order_lines
p_automatic_reservation - true if reservation is done
automatically, without user
intervention. used by order import.
p_quantity_to_reserve - quantity to be reserved. used only
when p_automatic_reservation is true
p_reservation_uom_code - uom in which to make the reservation.
the x_available_qty returned is
in this uom.
x_match_config_id - config id of the matching configuration
from bom_ato_configurations
x_available_qty - available quantity for reservation
in p_reservation_uom_code.
x_error_message - error message if match function fails
x_message_name - name of error message if match
function fails
match_inquiry returns TRUE if the process is successful
(no process errors). If a match is found,
x_config_match_id is populated with the inventory item
id of the matching config item.
If a match is not found, match_inquiry returns true and
x_config_match_id is NULL.
x_available_qty is the quantity available to reserve for
the configuration item. If it is zero, the user is not
given the option to reserve.
x_quantity_reserved returns the total quantity reserved.
match_inquiry returns FALSE if the process encounters
any errors.
if p_automatic_reseravation is true, match_inquiry returns
TRUE if a reservation is successful. otherwise, it returns
FALSE.
12/2/99: Product Management wants Match and Reserve to do
a link to the matching item even if reservation cannot
be made due to insufficient available quantity.
The change has been made. Match_inquiry now performs
a link if a matching item is found.
05/01/00: Modifying match_inquiry to work for multilevel
configurations.
*****************************************************************************/
PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
select line_id, parent_ato_line_id
from bom_cto_order_lines
where bom_item_type = 1
--and top_model_line_id = p_model_line_id
and ato_line_id = p_model_line_id
and nvl(wip_supply_type,0) <> 6
and ato_line_id is not null
order by plan_level desc;
select top_model_line_id, inventory_item_id
into l_top_model_line_id, l_top_model_item_id
from oe_order_lines_all
where line_id = p_model_line_id;
delete from bom_cto_order_lines where ato_line_id = p_model_line_id ;
oe_debug_pub.add('CTOMCRSB: deleted bcol: ' || to_char(SQL%ROWCOUNT));
delete from bom_cto_src_orgs_b where top_model_line_id = p_model_line_id ;
oe_debug_pub.add('CTOMCRSB: deleted bcso_b : ' || to_char(SQL%ROWCOUNT));
select bcol.inventory_item_id, bcol.ship_from_org_id, perform_match
into l_model_id, l_org_id , l_perform_match
from bom_cto_order_lines bcol
where bcol.line_id = p_model_line_id;
select concatenated_segments into v_model_item_name
from mtl_system_items_kfv
where inventory_item_id = l_top_model_item_id
and rownum = 1 ;
delete from bom_cto_order_lines
where top_model_line_id = l_top_model_line_id;
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 = p_model_line_id ;
select
line_id,
link_to_line_id,
ato_line_id,
top_model_line_id,
inventory_item_id,
component_code,
component_sequence_id,
lValidationOrg,
qty_per_parent_model,
ordered_quantity,
order_quantity_uom,
parent_ato_line_id,
perform_match,
plan_level,
bom_item_type,
wip_supply_type,
null --bugfix 3692727 ,null as shippig org doesnot matter
--during matching
bulk collect into
v_cto_match_rec.line_id,
v_cto_match_rec.link_to_line_id,
v_cto_match_rec.ato_line_id,
v_cto_match_rec.top_model_line_id,
v_cto_match_rec.inventory_item_id,
v_cto_match_rec.component_code,
v_cto_match_rec.component_sequence_id,
v_cto_match_rec.validation_org,
v_cto_match_rec.qty_per_parent_model,
v_cto_match_rec.ordered_quantity,
v_cto_match_rec.order_quantity_uom,
v_cto_match_rec.parent_ato_line_id,
v_cto_match_rec.perform_match,
v_cto_match_rec.plan_level,
v_cto_match_rec.bom_item_type,
v_cto_match_rec.wip_supply_type,
v_cto_match_rec.ship_from_org_id --bugfix 3692727
from bom_cto_order_lines
where ato_line_id = p_model_line_id
order by plan_level ;
delete from bom_cto_order_lines
where top_model_line_id = l_top_model_line_id;
select line_id, header_id , source_type_code , booked_flag
into l_config_line_id, l_header_id , l_source_type_code , l_booked_flag
from oe_order_lines_all
where ato_line_id = p_model_line_id
and item_type_code = 'CONFIG';
OE_Order_WF_Util.Update_Flow_Status_Code(
p_header_id => l_header_id,
p_line_id => l_config_line_id,
p_flow_status_code => 'BOM_AND_RTG_CREATED',
x_return_status => l_return_status);
select msi.primary_uom_code
into l_primary_uom_code
from mtl_system_items msi
where msi.inventory_item_id = x_config_id
and msi.organization_id = l_org_id;
/* 2620282 : Selecting bom revision date to pass it in the
call to BOM_REVISIONS.get_item_revision_fn while getting config line
information to perform reservation */
/* 4162494 : Join with wip_parameters assumes mfg org is the distribution org
which is incorrect. */
l_stmt_num := 139;
select trunc(greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
'MI')+1/(60*24)
into l_rev_date
from bom_calendar_dates cal,
mtl_parameters mp,
-- 4162494 wip_parameters wp,
mtl_system_items msi,
oe_order_lines_all oel
where oel.line_id = l_config_line_id
and mp.organization_id = oel.ship_from_org_id
-- 4162494 and wp.organization_id = mp.organization_id
and msi.organization_id = oel.ship_from_org_id
and msi.inventory_item_id = oel.inventory_item_id
and cal.calendar_code = mp.calendar_code
and cal.exception_set_id = mp.calendar_exception_set_id
and cal.seq_num =
(select greatest(1, (cal2.prior_seq_num -
(ceil(nvl(msi.fixed_lead_time,0) +
nvl(msi.variable_lead_time,0) *
p_quantity_to_reserve
))))
from bom_calendar_dates cal2
where cal2.calendar_code = mp.calendar_code
and cal2.exception_set_id =
mp.calendar_exception_set_id
and cal2.calendar_date =
trunc(oel.schedule_ship_date)
);
select mso.sales_order_id,
oel.line_id, -- config line id
oel.ship_from_org_id,
oel.inventory_item_id,
oel.order_quantity_uom,
p_quantity_to_reserve,
inv_reservation_global.g_source_type_inv,
NULL,
oel.schedule_ship_date,
oeh.source_document_type_id, -- bugfix 1799874: to check if it is an internal SO or regular
-- 2776026: Pass revision only if item is revision contol.
-- 2620282: Selecting bom revision information
decode( nvl(msi.revision_qty_control_code, 1), 1, NULL ,
BOM_REVISIONS.get_item_revision_fn (
'ALL',
'ALL',
oel.ship_from_org_id,
oel.inventory_item_id,
l_rev_date
))
into l_rec_reserve
from oe_order_lines_all oel,
oe_order_headers_all oeh,
--oe_order_types_v oet,
oe_transaction_types_tl oet,
mtl_sales_orders mso,
mtl_system_items msi
where oel.line_id = l_config_line_id
and oel.open_flag = 'Y'
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 mso.segment3 = 'ORDER ENTRY'
and oel.inventory_item_id = p_config_item_id
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;
select oel.line_id, oel.inventory_item_id
into x_config_line_id, x_config_item_id
from oe_order_lines_all oel,
mtl_system_items msi
where oel.link_to_line_id = p_model_line_id
and oel.item_type_code = 'CONFIG'
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id
and msi.base_item_id is not null
and msi.bom_item_type = 4; --standard item