[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
rows_deleted number ;
g_cto_shipset.delete ;
g_cto_sparse_shipset.delete ;
g_cto_shipset.delete ;
g_cto_sparse_shipset.delete ;
** 1) evaluate source of call ( CZ or OM ) and type of action ( enquiry, schedule, reschedule, cancel, delete )
** 2) Retrieve relevant information required for processing according to the request
** 3) Return if no models found in the shipset
** 4) Filter out mandatory components(permanent) and standard/ATO items( temporary ) for model processing.
** 5) Populate plan level
** 6) Populate Parent ATO
** 7) Unscramble the data and reorder it
** 8) Evaluate sourcing for the entire shipset
** 9) If model not sourced check step 10 else check step 11
** 10) if model not multilevel return without any further processing
** 11) Populate Sparse shipset for BOM creation
** 12) Create BOM for as model is Multi-level/Multi-Org
** 13) Reinstate WIP information for phantom models and option classes to non phantom in BOM structure as they
** have been exploded in the Pre-ATP process. ATP should not be exploding these models and option classes again.
** 14) Identify components under ATO models to be eliminated from p_shipset as they are part of sourced shipset
** 15) Start eliminating components under ATO models from p_shipset
** 16) Send Enhanced shipset back to caller
*************
** Caution **
** A shipset may be SMC( Ship Model Complete ) or NonSMC. In the case of NonSMC the PTO model
** may not be passed with the ATO models under it. The code needs to handle both scenarios from a
** Top Down Model processing perspective.
*/
PROCEDURE evaluate_shipset(
p_shipset in out NOCOPY MRP_ATP_PUB.ATP_REC_TYP
, p_auto_generated in boolean
, p_process_demand in boolean
, p_atp_bom_rec out NOCOPY MRP_ATP_PUB.ATP_BOM_REC_TYP
, p_model_sourced out boolean
, x_return_status out varchar2
)
is
v_orig_shipset_tracker CTO_SHIPSET_TBL_TYPE;
local_cto_shipset.delete ;
oe_debug_pub.add('evaluate_shipset: ' || 'Filtering PTO, deleted comp for line_id:: ' || local_cto_shipset(j).line_id, 2 );
local_cto_shipset.delete(j) ;
oe_debug_pub.add('evaluate_shipset: ' || 'Filtering PTO, deleted comp for line_id::' || local_cto_shipset(j).line_id , 2 );
local_cto_shipset.delete(j) ;
local_cto_shipset.delete(i) ;
final set before they were deleted from the original set.
New strategy requires the collection in g_cto_shipset to be delayed
till complete complex shipset processing is done.
*/
l_stmt_num := 700 ;
local_cto_shipset.delete ;
select Item_key, quantity, UOM_CODE, ship_To_date
from cz_atp_requests where configurator_session_key = c_session_key ;
g_requests_tab.delete ;
select
atp_request_id /* line to be commented if noncz */
, /* line to be commented if noncz */
configurator_session_key
, seq_no
, item_key
, quantity
, uom_code
, ship_to_date
, cz_atp_callback_util.inv_item_id_from_item_key( item_key )
/* line to be commented if noncz */
, config_item_id /*BUG#2250621 Multiple Instantiation Code Change */
, parent_config_item_id /*BUG#2250621 Multiple Instantiation Code Change */
into
g_requests_tab(v_index).line_id /* line to be commented if noncz */
, /* line to be commented if noncz */
g_requests_tab(v_index).configurator_session_key
, g_requests_tab(v_index).seq_no
, g_requests_tab(v_index).item_key
, g_requests_tab(v_index).quantity
, g_requests_tab(v_index).UOM_CODE
, g_requests_tab(v_index).ship_To_date
, g_requests_tab(v_index).inventory_item_id
/* line to be commented if noncz */
, g_requests_tab(v_index).config_item_id /*BUG#2250621 Multiple Instantiation Code Change */
, g_requests_tab(v_index).parent_config_item_id /*BUG#2250621 Multiple Instantiation Code Change */
from cz_atp_requests
where atp_request_id = p_shipset.identifier(v_location) ;
select MSYI.bom_item_type
, MSYI.replenish_to_order_flag
, MSYI.pick_components_flag
, MSYI.base_item_id
, MSYI.build_in_wip_flag
, MSYI.atp_flag --2462661
, MSYI.atp_components_flag --2462661
into
g_requests_tab(j).bom_item_type
, g_requests_tab(j).replenish_to_order_flag
, g_requests_tab(j).pick_components_flag
, g_requests_tab(j).base_item_id
, g_requests_tab(j).build_in_wip_flag
, g_requests_tab(j).atp_flag
, g_requests_tab(j).atp_components_flag
from Mtl_system_items MSYI
where MSYI.inventory_item_id = g_requests_tab(j).inventory_item_id
and MSYI.organization_id = l_validation_org ;
select common_bill_sequence_id
into v_bill_sequence_id
from bom_bill_of_materials
where assembly_item_id = g_requests_tab( nvl( g_requests_tab(j).parent_location, j ) ).inventory_item_id
AND organization_id = l_validation_org ;
select wip_supply_type
into g_requests_tab(j).wip_supply_type
from bom_inventory_components
where bill_sequence_id = v_bill_sequence_id
AND component_item_id = g_requests_tab(j).inventory_item_id
AND rownum < 1 ;
select nvl(master_organization_id,-99) --bugfix 2646849: master_organization_id can be 0
into l_validation_org
from oe_order_lines_all oel,
oe_system_parameters_all ospa
where oel.line_id = p_shipset.identifier(1)
and nvl(oel.org_id,-1) = nvl(ospa.org_id,-1) --bug 1531691
and oel.inventory_item_id = p_shipset.inventory_item_id(1) ;
select OEOL.header_id
, OEOL.line_id
, OEOL.top_model_line_id
, OEOL.ato_line_id
, OEOL.link_to_line_id
, OEOL.inventory_item_id
, OEOL.item_type_code
, OEOL.ordered_quantity
, OEOL.ship_from_org_id
, decode( OEOL.line_id, OEOL.ato_line_id , null ,
BIC.wip_supply_type )
, MSYI.bom_item_type
, MSYI.replenish_to_order_flag
, MSYI.pick_components_flag
, MSYI.base_item_id
, MSYI.build_in_wip_flag
, MSYI.atp_flag --2462661
, MSYI.atp_components_flag --2462661
, 'N' -- 2723674 : Initializing MLMO flag
INTO
p_orig_shipset_tracker(j).header_id
, p_orig_shipset_tracker(j).line_id
, p_orig_shipset_tracker(j).top_model_line_id
, p_orig_shipset_tracker(j).ato_line_id
, p_orig_shipset_tracker(j).link_to_line_id
, p_orig_shipset_tracker(j).inventory_item_id
, p_orig_shipset_tracker(j).item_type_code
, p_orig_shipset_tracker(j).ordered_quantity
, p_orig_shipset_tracker(j).sourcing_org
, p_orig_shipset_tracker(j).wip_supply_type
, p_orig_shipset_tracker(j).bom_item_type
, p_orig_shipset_tracker(j).replenish_to_order_flag
, p_orig_shipset_tracker(j).pick_components_flag
, p_orig_shipset_tracker(j).base_item_id
, p_orig_shipset_tracker(j).build_in_wip_flag
, p_orig_shipset_tracker(j).atp_flag
, p_orig_shipset_tracker(j).atp_components_flag
, p_orig_shipset_tracker(j).mlmo_flag -- 2723674 : Initializing MLMO flag
from oe_order_lines_all OEOL , bom_inventory_components BIC , mtl_system_items MSYI
where line_id = p_shipset.identifier(j)
and OEOL.component_sequence_id = BIC.component_sequence_id(+)
and MSYI.inventory_item_id = p_shipset.inventory_item_id(j)
and MSYI.organization_id = l_validation_org
order by line_id ;
SELECT planning_make_buy_code
INTO l_make_buy_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = local_cto_shipset(p_location).inventory_item_id
AND organization_id = p_org;
v_orgs_tbl.delete ; /* reinitialize table to check circular sourcing */
select distinct
source_organization_id,
sourcing_rule_id,
nvl(source_type,1) ,
nvl( avg_transit_lead_time , 0 )
into
p_sourcing_org
, v_sourcing_rule_id
, v_source_type
, p_transit_lead_time
from mrp_sources_v msv
where msv.assignment_set_id = gMrpAssignmentSet
and msv.inventory_item_id = p_inventory_item_id
and msv.organization_id = p_organization_id
and nvl(effective_date,sysdate) <= nvl(disable_date, sysdate)
and nvl(disable_date, sysdate+1) > sysdate;
SELECT planning_make_buy_code
INTO l_make_buy_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
select count(*)
into v_sourcing_rule_count
from mrp_sources_v msv
where msv.assignment_set_id = gMrpAssignmentSet
and msv.inventory_item_id = p_inventory_item_id
and msv.organization_id = p_organization_id
and nvl(msv.source_type,1) <> 3
and nvl(effective_date,sysdate) <= nvl(disable_date, sysdate)
and nvl(disable_date, sysdate+1) > sysdate;
v_raw_line_id.delete ; /* remove all elements as they have been resolved */
v_raw_line_id.delete ;
g_cto_shipset.delete ;
g_cto_sparse_shipset.delete ;
p_atp_rec.Row_Id.delete ;
p_atp_rec.Instance_Id.delete ;
p_atp_rec.Inventory_Item_Id.delete ;
p_atp_rec.Inventory_Item_Name.delete ;
p_atp_rec.Source_Organization_Id.delete ;
p_atp_rec.Source_Organization_Code.delete ;
p_atp_rec.Organization_Id.delete ;
p_atp_rec.Identifier.delete ;
p_atp_rec.Demand_Source_Header_Id.delete ;
p_atp_rec.Demand_Source_Delivery.delete ;
p_atp_rec.Demand_Source_Type.delete ;
p_atp_rec.Scenario_Id.delete ;
p_atp_rec.Calling_Module.delete ;
p_atp_rec.Customer_Id.delete ;
p_atp_rec.Customer_Site_Id.delete ;
p_atp_rec.Destination_Time_Zone.delete ;
p_atp_rec.Quantity_Ordered.delete ;
p_atp_rec.Quantity_UOM.delete ;
p_atp_rec.Requested_Ship_Date.delete ;
p_atp_rec.Requested_Arrival_Date.delete ;
p_atp_rec.Earliest_Acceptable_Date.delete ;
p_atp_rec.Latest_Acceptable_Date.delete ;
p_atp_rec.Delivery_Lead_Time.delete ;
p_atp_rec.Freight_Carrier.delete ;
p_atp_rec.Ship_Method.delete ;
p_atp_rec.Demand_Class.delete ;
p_atp_rec.Ship_Set_Name.delete ;
p_atp_rec.Arrival_Set_Name.delete ;
p_atp_rec.Override_Flag.delete ;
p_atp_rec.Action.delete ;
p_atp_rec.Ship_Date.delete ;
p_atp_rec.Available_Quantity.delete ;
p_atp_rec.Requested_Date_Quantity.delete ;
p_atp_rec.Group_Ship_Date.delete ;
p_atp_rec.Group_Arrival_Date.delete ;
p_atp_rec.Vendor_Id.delete ;
p_atp_rec.Vendor_Name.delete ;
p_atp_rec.Vendor_Site_Id.delete ;
p_atp_rec.Vendor_Site_Name.delete ;
p_atp_rec.Insert_Flag.delete ;
p_atp_rec.OE_Flag.delete ;
p_atp_rec.Atp_Lead_Time.delete ;
p_atp_rec.Error_Code.delete ;
p_atp_rec.Message.delete ;
p_atp_rec.End_Pegging_Id.delete ;
p_atp_rec.Order_Number.delete ;
p_atp_rec.Old_Source_Organization_Id.delete ;
p_atp_rec.Old_Demand_Class.delete ;
p_bom_rec.assembly_identifier.delete ;
p_bom_rec.assembly_item_id.delete ;
p_bom_rec.component_identifier.delete ;
p_bom_rec.component_item_id.delete ;
p_bom_rec.quantity.delete ;
p_bom_rec.fixed_lt.delete ;
p_bom_rec.variable_lt.delete ;
p_bom_rec.pre_process_lt.delete ;
IF( p_shipset.ato_delete_flag.count > 0 ) then
if( p_shipset.ato_delete_flag.exists(j) ) then
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add( '+++Ato delete flag:: ' || p_shipset.ato_delete_flag(j) , 2 ) ;
' Insert_Flag ' || p_shipset.insert_flag.count ||
' OE_Flag ' || p_shipset.oe_flag.count ||
' Atp_Lead_Time ' || p_shipset.atp_lead_time.count , 5 );
' ATO DELETE FLAG ' || p_shipset.ato_delete_flag.count ||
' Old_Demand_Class ' || p_shipset.old_demand_class.count , 5 );
select min(calendar_date), max(calendar_date)
into l_eff_date, l_disable_date
from bom_calendar_dates cal,
mtl_parameters mp
where mp.organization_id = CTO_ATP_INTERFACE_PK.G_OE_VALIDATION_ORG
and mp.calendar_code = cal.calendar_code
and mp.calendar_exception_set_id = cal.exception_set_id;
-- Insert all items into BOM structure except top level ATO models
--
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('create_atp_bom: ' || 'Processing line_id '||to_char(l_index), 2);
oe_debug_pub.add('**create_atp_bom: ' || 'Not top ato model and not buy item, so insert into BOM', 2);
oe_debug_pub.add('**create_atp_bom: ' || 'Insert record # '||to_char(i), 2);
select nvl(fixed_lead_time, 0), nvl(variable_lead_time, 0), nvl(preprocessing_lead_time, 0)
into l_fixed_lt, l_variable_lt, l_preproc_lt
from mtl_system_items
where inventory_item_id = g_cto_sparse_shipset(l_parent_id).Inventory_Item_Id
and organization_id = g_cto_sparse_shipset(l_parent_id).Sourcing_Org;
select atp_flag, atp_components_flag, bom_item_type
into l_atp_flag, l_atp_comps_flag, l_bom_item_type
from mtl_system_items
where inventory_item_id = g_cto_sparse_shipset(l_index).Inventory_Item_Id
and organization_id = g_cto_sparse_shipset(l_index).Sourcing_Org;
is no way of selecting the correct component, we
are joining with rownum = 1*/
lStmtNum := 85;
select bic.check_atp
, bic.wip_supply_type
into p_atp_bom.atp_check(i)
, p_atp_bom.wip_supply_type(i)
from bom_bill_of_materials bbom,
bom_inventory_components bic
where bbom.assembly_item_id = g_cto_sparse_shipset(l_link_id).Inventory_Item_Id
and bbom.organization_id = g_cto_sparse_shipset(l_link_id).Sourcing_Org
and bbom.alternate_bom_designator is NULL
and bbom.common_bill_sequence_id = bic.bill_sequence_id
and bic.component_item_id = g_cto_sparse_shipset(l_index).Inventory_Item_Id
and rownum = 1;
oe_debug_pub.add('**create_atp_bom: ' || 'Top ato model is buy model, so insert dummy row in BOM', 2);
oe_debug_pub.add('**create_atp_bom: ' || 'Insert record# '||to_char(i), 2);
select bic.component_item_id component_item_id,
bic.component_quantity component_quantity,
bic.effectivity_date eff_date,
bic.disable_date disable_date,
bic.check_atp check_atp,
bic.wip_supply_type
from bom_bill_of_materials bbom,
bom_inventory_components bic
where bbom.assembly_item_id = g_cto_sparse_shipset(p_index).Inventory_Item_Id
and bbom.organization_id = g_cto_sparse_shipset(p_index).Sourcing_Org
and bbom.alternate_bom_designator is NULL
and bbom.common_bill_sequence_id = bic.bill_sequence_id
and bic.optional = 2
and nvl(bic.disable_date, sysdate) >= sysdate
and bic.implementation_date is not null
and bic.bom_item_type = 4;
select bic.wip_supply_type
into l_wip_supply_type
from bom_bill_of_materials bbom,
bom_inventory_components bic
where bbom.assembly_item_id = g_cto_sparse_shipset(l_link_line_id).Inventory_Item_Id
and bbom.organization_id = g_cto_sparse_shipset(l_link_line_id).Sourcing_Org
and bbom.alternate_bom_designator is NULL
and bbom.common_bill_sequence_id = bic.bill_sequence_id
and bic.component_item_id = g_cto_sparse_shipset(l_model_line_id).Inventory_Item_Id
and rownum = 1;
select max(calendar_date)
into l_disable_date
from bom_calendar_dates cal,
mtl_parameters mp
where mp.organization_id = CTO_ATP_INTERFACE_PK.G_OE_VALIDATION_ORG
and mp.calendar_code = cal.calendar_code
and mp.calendar_exception_set_id = cal.exception_set_id;
select nvl(fixed_lead_time, 0), nvl(variable_lead_time, 0), nvl(preprocessing_lead_time, 0)
into l_fixed_lt, l_variable_lt, l_preproc_lt
from mtl_system_items
where inventory_item_id = g_cto_sparse_shipset(l_new_model_line_id).Inventory_Item_Id
and organization_id = g_cto_sparse_shipset(l_new_model_line_id).Sourcing_Org;
select atp_flag, atp_components_flag
into l_atp_flag, l_atp_comps_flag
from mtl_system_items
where inventory_item_id = nxtrec.component_item_id
and organization_id = g_cto_sparse_shipset(l_new_model_line_id).Sourcing_Org;
MRP_ATP_DETAILS_TEMP and insert the entier row
into the table BOM_CTO_ORDER_DEMAND.
Input :
p_ship_set - This is the IN OUT Parameter. When it is called
from ATP it will have the Reduced Ship set in it.
And the end of this procedure it will be having
the full ship set(Transformed).
p_success_flag - This will have 'Y' or 'N' based on the scheduling Succeeds or fails.
p_session_id - session_id is used to identify the set of records
in the peggin tree
Process : The records in the table BOM_CTO_ORDER_DEMAND is
populated
Output :
xreturn_status - Return FND_API.G_RET_STS_SUCCESS if the procedure
is executed successfully
Return FND_API.G_RET_STS_ERROR if the procedure
is completed with expected error
Return FND_API.G_RET_STS_UNEXP_ERROR if the
procedure is completed with unexpected error
The Logic for CREATE_CTO_MODEL_DEMAND is as follows:
1. Scan through the records in the Shared shipset
2. For each record in the shared shipset find out the shipset name
3. If the shipset is not sourced and the action is rescheduling
THEN delete the information for this top model from BCOD table.
------------------------------------------------------------------------------*/
/*****************************************************************************************
09-JAN-2001
1. When the shipset is having a ATO model for which the configuration item
is alerady created THEN the only action possible is rescheduling.
2. In this case delete the existing demand from bcod for this line_id.
3. And insert the new demands for all the model,option class and option item
including config item with the proper visible_forecast_flag and visible_demand_flag
*******************************************************************************************/
PROCEDURE CREATE_CTO_MODEL_DEMAND(
p_shipset IN OUT NOCOPY MRP_ATP_PUB.ATP_REC_TYP,
p_session_id IN number,
p_shipset_status IN MRP_ATP_PUB.SHIPSET_STATUS_REC_TYPE,
xreturn_status OUT varchar2,
xMsgCount OUT number,
xMsgData OUT varchar2) is
i number; -- Loop counter
When the model option class or option item is deleted OM will indicate this
With ATO_DELETE_FLAG in shipset. In this case CTO will simply delete the demand for that
line_id in bcod and return the shipset back to ATP. ATP introduced a new flag ATO_DELETE_FLAG
in shipset for this. For this delete case ATP will not call out pre ATP procedure.
The following are the decision points in this design.
1. When the option class or Option item is getting deleted, OM will pas only that
perticular line to ATP with the appropirate flag. So at any time the no of records in the
shipset will be 1.
2. The action code in this case will be 120.
*/
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('ato delete flag check',2);
oe_debug_pub.add('ato delete flag count '|| p_shipset.ato_delete_flag.count,2);
oe_debug_pub.add('ato delete flag first '|| p_shipset.ato_delete_flag.first ,2);
IF ( ( p_shipset.ato_delete_flag.count > 0 ) AND
( nvl (p_shipset.ato_delete_flag(p_shipset.ato_delete_flag.first),'N')
= 'Y' ))
THEN
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('create_cto_model_demand: ' || 'One of the ATO line is deleted...',5);
DELETE
FROM BOM_CTO_ORDER_DEMAND
WHERE OE_LINE_ID = l_line_id;
oe_debug_pub.add('create_cto_model_demand: ' || 'No of records deleted..'||sql%rowcount,2);
oe_debug_pub.add('ato delete flag check done',2);
delete from bom_cto_order_demand
where ato_line_id = slso_shipset(slso_index).ato_line_id;
rows_deleted := sql%rowcount;
oe_debug_pub.add('Deleted '||to_char(rows_deleted)||' rows. ', 2);
DELETE FROM BOM_CTO_ORDER_DEMAND
WHERE ato_line_id = g_final_cto_shipset(i).ato_line_id;
oe_debug_pub.add('create_cto_model_demand: ' || 'Inserting a row in bcod for config line...',1);
oe_debug_pub.add('create_cto_model_demand: ' || 'Config line demand is inserted successfully..',1);
select oel.line_id,
oel.inventory_item_id,
msi.bom_item_type
into l_par_line_id,
l_par_inventory_item_id,
l_par_bom_item_type
from oe_order_lines_all oel,
mtl_system_items msi
where oel.line_id = g_final_cto_shipset(i).link_to_line_id
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id;
select order_quantity_uom,ordered_quantity
into l_order_quantity_uom,l_order_qty
from oe_order_lines_all
where line_id = g_final_cto_shipset(i).line_id;
select MAX(atp.supply_demand_date), -- required_date
SUM(atp.supply_demand_quantity), -- required_qty
MAX(oel.order_quantity_uom), -- ordered_quantity_uom
MAX(oel.header_id), -- Header_id
COUNT(*) -- To get the no of rows selected
-- The above count(*) is added by renga on 12/22/00 to error out
-- in the case of zero rows.
into l_required_date,
l_required_qty,
l_order_quantity_uom,
l_header_id,
l_record_count
from MRP_ATP_DETAILS_TEMP ATP,
OE_ORDER_LINES_ALL OEL
where ATP.session_id = p_session_id
and ATP.component_identifier = g_final_cto_shipset(i).link_to_line_id
and ATP.inventory_item_id = l_par_inventory_item_id
and ATP.organization_id = g_final_cto_shipset(i).sourcing_org
and ATP.supply_demand_type = 1 -- Demand
and OEL.line_id = ATP.component_identifier;
select nvl(fixed_lead_time,0),
nvl(variable_lead_time,0)
into l_fixed_lead_time,
l_variable_lead_time
from mtl_system_items
where inventory_item_id = l_par_inventory_item_id
and organization_id = g_final_cto_shipset(i).sourcing_org;
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)
VALUES(
BOM_CTO_ORDER_DEMAND_S1.nextval, -- bcod_line_id
g_final_cto_shipset(i).line_id, -- oe_line_id
g_final_cto_shipset(i).ato_line_id, -- ato_line_id
g_final_cto_shipset(i).inventory_item_id, -- Inventory_item_id
g_final_cto_shipset(i).sourcing_org, -- organization_id
l_required_date, -- required_date
l_order_qty, -- required_qty
l_order_quantity_uom, -- ordered_quantity_uom
1, -- parent_demand_type
g_final_cto_shipset(i).header_id, -- header_id
l_vis_forecast_flag, -- forecast_visible
l_vis_demand_flag, -- demand_visible
guserid, -- Created_by
guserid, -- Last_updated_by
sysdate, -- last_updated_date
gloginid, -- Last update_login
null, -- program_application_id
sysdate); -- program_update_date
SELECT 'Y'
INTO x_config_exists
FROM OE_ORDER_LINES_ALL
WHERE ato_line_id = p_ato_line_id
AND ITEM_TYPE_CODE = 'CONFIG';
SELECT line_id,
inventory_item_id,
ship_from_org_id
INTO
l_line_id,
l_inv_item_id,
l_org_id
FROM OE_ORDER_LINES_ALL
WHERE
ato_line_id = p_ato_line_id
AND item_type_code = 'CONFIG';
SELECT /* added required date for BUG#2465370 */
MAX(nvl( atp.required_date, atp.supply_demand_date)), -- required_date
SUM(atp.supply_demand_quantity), -- required_qty
MAX(oel.order_quantity_uom), -- ordered_quantity_uom
MAX(oel.header_id), -- Header_id
COUNT(*) -- To get the no of rows selected
-- The above count(*) is added by renga on 12/22/00 to error out
-- in the case of zero rows.
INTO
l_required_date,
l_required_qty,
l_order_quantity_uom,
l_header_id,
l_record_count
FROM
MRP_ATP_DETAILS_TEMP ATP,
OE_ORDER_LINES_ALL OEL
WHERE
ATP.session_id = p_session_id
AND ATP.component_identifier = p_line_id
AND ATP.inventory_item_id = p_inventory_item_id
AND ATP.organization_id = l_org_id
AND ATP.supply_demand_type = 1 -- Demand
AND ATP.component_identifier = OEL.line_id;
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)
VALUES(
BOM_CTO_ORDER_DEMAND_S1.nextval, -- bcod_line_id
l_line_id, -- oe_line_id
p_ato_line_id, -- ato_line_id
l_inv_item_id, -- Inventory_item_id
l_org_id, -- organization_id
l_required_date, -- required_date
l_required_qty, -- required_qty
l_order_quantity_uom, -- ordered_quantity_uom
1, -- parent_demand_type
l_header_id, -- header_id
p_forecast_flag, -- forecast_visible
p_demand_flag, -- demand_visible
guserid, -- Created_by
guserid, -- Last_updated_by
sysdate, -- last_updated_date
gloginid, -- Last update_login
null, -- program_application_id
sysdate); -- program_update_date
oe_debug_pub.add('create_demand_line: ' || 'Error in select :: Other:: in the ATP Pegging Tree for line_id ..'|| to_char(l_line_id)||
'Item id ....'||
to_char(l_inv_item_id)
||' And Sourcing org ...'
||to_char(l_org_id)||sqlerrm,1);
SELECT
oeol.header_id,
oeol.line_id,
oeol.inventory_item_id,
oeol.ship_from_org_id,
oeol.ordered_quantity,
oeol.schedule_ship_date,
oeol.order_quantity_uom
FROM oe_order_lines_all oeol
WHERE
oeol.open_flag = 'Y' /* SRS added for performance to retrieve only open orde
rs */
AND oeol.ship_from_org_id is not null
AND nvl(oeol.visible_demand_flag,'N') = 'Y'
AND ( oeol.item_type_code = 'STANDARD' OR oeol.item_type_code = 'OPTION' )
/* added item_code = 'OPTION' to support ATO ITEMs under PTO Models per
BUG#1874380 */
AND oeol.line_id = oeol.ato_line_id
AND nvl(oeol.source_document_type_id,0) <> 10; -- This is confirmed with OM
SELECT instance_id
INTO ginstance_id
FROM MRP_AP_APPS_INSTANCES;
DELETE
FROM
BOM_CTO_ORDER_DEMAND
WHERE parent_demand_type = '2'; --- The rows belongs to ato items
oe_debug_pub.add('create_cto_item_demand: ' || 'Inserting Config line info into bcod...',2);
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)
VALUES (
bom_cto_order_demand_s1.nextval, -- bcod_line_id
myrec.line_id, -- oe_line_id
myrec.line_id, -- ato_line_id
myrec.inventory_item_id, -- inventory_item_id
myrec.ship_from_org_id, -- organization_id
myrec.schedule_ship_date, -- Required_date
Round( myrec.ordered_quantity, 6 ) ,-- Ordered Quantity /* Decimal-Qty Support for Option Items */
myrec.order_quantity_uom, -- Order_quantity_uom
2, -- parent_demand_type
myrec.header_id, -- Header_id
'N', -- Forecast_visible
'Y', -- Demand_visible
guserid, -- Created_by
guserid, -- Last_updated_by
sysdate, -- Last_updated_date
gloginid, -- Last_update_login
null, -- program_application_id
sysdate); -- Program_update_date
SELECT component_item_id,component_quantity
FROM bom_inventory_components bic,
bom_bill_of_materials bom,
mtl_system_items mtl
WHERE bom.assembly_item_id = p_item_id
AND bom.organization_id = x_source_org
AND bom.bill_sequence_id = bic.bill_sequence_id
AND bic.bom_item_type = 4
AND mtl.inventory_item_id = bic.component_item_id
AND mtl.organization_id = x_source_org
AND mtl.base_item_id is not null;
select nvl(fixed_lead_time,0),nvl(variable_lead_time,0)
into l_fixed_lead_time,l_variable_lead_time
from mtl_system_items
where inventory_item_id = p_item_id
and organization_id = x_source_org;
/* First level model and Option class rows are inserted in to the bcod table.*/
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, -- bcod_line_id
p_line_id, -- oe_line_id
p_line_id, -- ato_line_id
bic.component_item_id, -- inventory_item_id
p_org_id, -- organization_id
p_req_date, -- Required_date
Round( p_qty*bic.component_quantity, 6) ,-- required_qty /* Decimal-Qty Support for Option Items */
mtl.primary_uom_code, -- Order_quantity_uom
2, -- Parent_demand_type
p_header_id, -- Header_id
'Y', -- Forecast_visible
'N', -- Demand_visible
guserid, -- Created_by
guserid, -- Last_updated_by
sysdate, -- Last_updated_date
gloginid, -- Last_update_login
null, -- Program_application_id
sysdate
From
bom_inventory_components bic,
bom_bill_of_materials bom,
mtl_system_items mtl
where bom.assembly_item_id = p_item_id
and bom.organization_id = p_org_id
and bom.bill_sequence_id = bic.bill_sequence_id
and bic.bom_item_type in (1,2)
and mtl.inventory_item_id= bic.component_item_id
and mtl.organization_id = p_org_id;
/* Insert the option items for the first level models and option classes */
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, -- bcod_line_id
p_line_id, -- oe_line_id
p_line_id, -- ato_line_id
bic.component_item_id, -- inventory_item_id
p_org_id, -- organization_id
p_req_date, -- Required_date
Round( p_qty*bic.component_quantity, 6) ,-- required_qty /* Decimal-Qty Support for Option Items */
mtl.primary_uom_code, -- Order_quantity_uom
2, -- Parent_demand_type
p_header_id, -- Header_id
'Y', -- Forecast_visible
'N', -- Demand_visible
guserid, -- Created_by
guserid, -- Last_updated_by
sysdate, -- Last_updated_date
gloginid, -- Last_update_login
null, -- Program_application_id
sysdate
from bom_inventory_components bic,
bom_bill_of_materials bom,
mtl_system_items mtl
where bom.assembly_item_id = p_item_id
and bom.organization_id = p_org_id
and bom.bill_sequence_id = bic.bill_sequence_id
and bic.bom_item_type = 4
and bic.component_item_id in (
select
bic2.component_item_id
from bom_bill_of_materials bom1,
bom_bill_of_materials bom2,
bom_inventory_components bic1,
bom_inventory_components bic2
where bom1.assembly_item_id = p_item_id
and bom1.organization_id = p_org_id
and bom1.bill_sequence_id = bic1.bill_sequence_id
and bic1.bom_item_type in(1, 2)
and bom2.assembly_item_id = bic1.component_item_id
and bom2.organization_id = p_org_id
and bic2.bill_sequence_id = bom2.bill_sequence_id
and bic2.bom_item_type = 4
and bic2.optional = 1)
and mtl.inventory_item_id = bic.component_item_id
and mtl.organization_id = p_org_id;
select component_item_id
from bom_bill_of_materials bom,
bom_inventory_components bic,
mtl_system_items mtl
where bom.assembly_item_id = p_item_id
and bom.organization_id = p_org_id
and bom.bill_sequence_id = bic.bill_sequence_id
and bic.bom_item_type = 4
and mtl.inventory_item_id = bic.component_item_id
and mtl.organization_id = p_org_id
and mtl.base_item_id is not null;
g_cto_shipset.delete ;
g_final_cto_shipset.delete ;
g_cto_sparse_shipset.delete ;
g_shipset_status_tbl.delete ;
local_cto_shipset.delete ;
g_auto_generated_shipset.delete;
select assignment_set_name into gMrpAssignmentSetName
from mrp_Assignment_sets
where assignment_set_id = gMrpAssignmentSet ;