[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Added delete from from bom_inventory_comps_interface to avoid corrupt data.
|
Modified on 21-APR-2004 By Renga Kannan ( Bug 3543547)
* Autocreate config is dropping
* components from bill
* *
* since dropped component logic is based
* on bill
* *
* sequence id instead of common bill
* sequence id .
*
* Modified on 19-NOV-2004 Sushant Sawant
* Fixed Bug 3877317 front port for bug 3764447
* This bug has been front ported with some modifications
* to account for 11.5.10 features.
*
* BUG 3877317.
*
* old_behavior:
* Organizations where Cost rollup needs to be performed were determined using
* RCV_ORG_ID and ORGANIZATION_ID columns in bom_cto_src_orgs view.
*
* new behavior:
* Organizations where cost rollup needs to be performed will now be determined using
* only ORGANIZATION_ID column in bom_cto_src_orgs view.
*
* procedure CREATE_IN_SRC_ORGS has changed
*
* 1) change to cursor cSrcOrgs
* columns create_bom, cost_rollup and organization_type have been removed as they
* will now be queried in the cursor loop
* 2) added new variables v_create_bom, v_perform_cost_rollup
* 3) cost_rollup flag needs to be queried again as the flag can be updated in the loop
* 4) create_bom flag needs to be queried in the loop
*
* procedure OVERRIDE_BCSO_COST_ROLLUP has changed.
*
* 1) query fixed to get v_organization_type.
* 2) added new too_many_rows exception handler due to query in 1 above
* is now dependent only on organization_id
* 3) SQL added in too_many_rows exception handler declared in 2 above to check whether
* organization_id is make org. query is now based only on organization_id and hence
* needs to check whether it is manufacturing org using the following sql.
* The code to check whether cost rollup should not be performed needs to know whether
* the organization is make organization. In 11.5.9 this check was not required as
* create_bom flag was set only for manufacturing org.
*
* All changes are marked with bug 3877317.
*
*
*
***************************************************************************************/
/*-------------------------------------------------------------+
Name : Create_all_boms_and_routings
This procedure loops through all the configuration
items in bom_cto_order_lines and calls create_in_src_orgs
for each item.
+-------------------------------------------------------------*/
PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
select bcol.line_id, bcol.inventory_item_id,
bcol.config_item_id
from bom_cto_order_lines bcol
--where bcol.top_model_line_id = pTopModelLineId
where bcol.ato_line_id = pAtoLineId
and bcol.bom_item_type = 1
and nvl(bcol.wip_supply_type,0) <> 6
and bcol.config_item_id is not null
and bcol.ato_line_id is not null
order by plan_level desc;
select count(*) into v_bcol_count from bom_cto_order_lines
where ato_line_id = pAtoLineId ;
select ato_line_id, header_id
into x_ato_line_id, x_header_id
from bom_cto_order_lines
where line_id = p_line_id ;
select distinct bcso.organization_id,
mp.cost_organization_id,
bcol.perform_match,
bcol.option_specific,
-- bcso.create_bom bom_create, bug 3877317 column will be queried in the cursor
bcso.model_item_id,
bcso.config_item_id,
bcso.group_reference_id
-- bcso.cost_rollup, bug 3877317 column will be queried in the cursor
-- bcso.organization_type bug 3877317 column will be queried in the cursor
from bom_cto_src_orgs bcso, bom_cto_order_lines bcol, mtl_parameters mp
where bcso.line_id = pLineId
and bcso.model_item_id = pModelId
and bcso.config_item_id is not null
and bcso.line_id = bcol.line_id
and bcso.organization_id = mp.organization_id ;
/* bugfix 2986192 Cursor to select dropped items during match */
/* Effectivity date bug fix : 4147224
Need to validate the dropped component for Estimated relase date
window. Added a xEstRelDate parameter to cursor and
added effectivity window condition for bom_inventory_comps_interface
Sql
*/
cursor mismatched_items ( xlineid number,
xconfigbillid number,
xEstRelDate Date) is
select inventory_item_id
from bom_cto_order_lines
where parent_ato_line_id=xlineid
and parent_ato_line_id <> line_id /* to avoid selecting top model */
and NOT ( bom_item_type = 1 and wip_supply_type <> 6 and line_id <> xlineid ) /* to avoid selecting lower level models */
minus
select component_item_id
from bom_inventory_comps_interface
where bill_sequence_id = xconfigbillid
and greatest(sysdate, xEstRelDate ) >= effectivity_date
and (( disable_date is null ) or ( disable_date is not null and disable_date >= greatest(sysdate, xEstRelDate) )) ;
select program_id
into v_program_id
from bom_cto_order_lines
where line_id = pLineId;
select count(*) into v_bcol_count from bom_cto_order_lines
where ato_line_id = pLineId ;
select count(*) into v_bcol_count from bom_cto_order_lines
where ato_line_id = pLineId and option_specific = 'N' ;
cost_rollup flag needs to be queried again as the flag can be updated in the loop
*/
v_perform_cost_rollup := 'N' ;
select 'Y' into v_perform_cost_rollup from dual
where exists ( select * from bom_cto_src_orgs
where line_id = pLineId
and cost_rollup = 'Y'
and organization_id = lNextRec.cost_organization_id ) ;
select 'Y' into v_create_bom from dual
where exists ( select * from bom_cto_src_orgs
where line_id = pLineId
and create_bom = 'Y'
and organization_id = lNextRec.organization_id ) ;
select common_bill_sequence_id
into lComItmBillId
from bom_bill_of_materials
where bill_sequence_id = lItmBillId;
oe_debug_pub.add('Inserting into BICI ... ' ,1);
insert into bom_inventory_comps_interface(component_item_id,bill_sequence_id, batch_id,
effectivity_date,disable_date)
select component_item_id, bill_sequence_id,
cto_msutil_pub.bom_batch_id,effectivity_date,disable_date
from bom_inventory_components
where bill_sequence_id = lComItmBillId; -- 3543547 lItmBillId;
oe_debug_pub.add('inserting into bici'|| SQL%ROWCOUNT || ' for bill ' || lItmBillId ,1);
oe_debug_pub.add('Inserting child base model into BICI for matched cases... ' ,1);
insert into bom_inventory_comps_interface(component_item_id,bill_sequence_id, batch_id,
effectivity_date,disable_date)
select distinct a.base_model_id, b.bill_sequence_id,
cto_msutil_pub.bom_batch_id,effectivity_date,disable_date
from bom_ato_configurations a,bom_inventory_components b
where a.config_item_id = b.component_item_id
and b.bill_sequence_id = lComItmBillId; -- 3543547 lItmBillId
oe_debug_pub.add('Failed to insert into bom_inventory_comps_interface with error '||sqlerrm);
select (ceil(nvl(msi.fixed_lead_time,0)
+ nvl(msi.variable_lead_time,0) * bcol.ordered_quantity))
into lLeadTime
from mtl_system_items msi,
bom_cto_order_lines bcol
where bcol.line_id = pLineId
and msi.inventory_item_id = bcol.inventory_item_id
and msi.organization_id = CTO_UTILITY_PK.PC_BOM_VALIDATION_ORG;
select (ceil(nvl(msi.fixed_lead_time,0)
+ nvl(msi.variable_lead_time,0) * oel.ordered_quantity))
into lLeadTime
from mtl_system_items msi,
oe_order_lines_all oel
where oel.line_id = pLineId
and msi.inventory_item_id = oel.inventory_item_id
and msi.organization_id = oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id);
select CAL.CALENDAR_DATE
into lEstRelDate
from bom_calendar_dates cal,
mtl_system_items msi,
bom_cto_order_lines bcol,
mtl_parameters mp
where msi.organization_id = lNextRec.organization_id
and msi.inventory_item_id = pModelId
and bcol.line_id = pLineId
and bcol.inventory_item_id = msi.inventory_item_id
and mp.organization_id = msi.organization_id
and cal.calendar_code = mp.calendar_code
and cal.exception_set_id = mp.calendar_exception_set_id
and cal.seq_num =
(select cal2.prior_seq_num - lLeadTime
from bom_calendar_dates cal2
where cal2.calendar_code = mp.calendar_code
and cal2.exception_set_id = mp.calendar_exception_set_id
and cal2.calendar_date = trunc(bcol.schedule_ship_date));
oe_debug_pub.add('Select missed component details.. ' ,1);
select substrb(msi.concatenated_segments,1,50),
'Not Available' ,
-1
into v_missed_item,
v_missed_line_number,
v_order_number
from mtl_system_items_kfv msi,
bom_cto_order_lines bcol
where msi.organization_id = bcol.ship_from_org_id
and msi.inventory_item_id = bcol.inventory_item_id
and bcol.parent_ato_line_id = pLineId
and bcol.inventory_item_id = l_missed_item_id
and rownum = 1;
select substrb(concatenated_segments,1,50),
to_char(oel.line_number)||'.'||to_char(oel.shipment_number) ||
decode(oel.option_number,NULL,NULL,'.'||to_char(option_number)),
oeh.order_number
into v_missed_item,
v_missed_line_number,
v_order_number
from mtl_system_items_kfv msi,
oe_order_lines_all oel,
oe_order_headers_all oeh
,bom_cto_order_lines bcol
where msi.organization_id = oel.ship_from_org_id
and msi.inventory_item_id = oel.inventory_item_id
and oel.header_id = oeh.header_id
and oel.inventory_item_id = l_missed_item_id
and oel.line_id = bcol.line_id
and bcol.parent_ato_line_id = pLineId
and rownum =1;
oe_debug_pub.add('Select model.. ' ,1);
select substrb(concatenated_segments,1,50)
into l_model
from mtl_system_items_kfv
where organization_id = lNextRec.organization_id
and inventory_item_id = pModelId ;
oe_debug_pub.add('Select Org.. ' ,1);
select organization_code
into lOrg_code
from mtl_parameters
where organization_id = lNextRec.organization_id ;
select segment1
into v_problem_model
from mtl_system_items
where inventory_item_id = pModelId
and rownum = 1 ;
select segment1
into v_problem_config
from mtl_system_items
where inventory_item_id = pConfigId
and rownum = 1 ;
select organization_name
into v_error_org
from inv_organization_name_v
where organization_id = lNextRec.organization_id ;
select segment1
into v_problem_model
from mtl_system_items
where inventory_item_id = pModelId
and rownum = 1 ;
select segment1
into v_problem_config
from mtl_system_items
where inventory_item_id = pConfigId
and rownum = 1 ;
select organization_name
into v_error_org
from inv_organization_name_v
where organization_id = lNextRec.organization_id ;
select segment1
into v_problem_model
from mtl_system_items
where inventory_item_id = pModelId
and rownum = 1 ;
select segment1
into v_problem_config
from mtl_system_items
where inventory_item_id = pConfigId
and rownum = 1 ;
select organization_name
into v_error_org
from inv_organization_name_v
where organization_id = lNextRec.organization_id ;
select oel.line_number || '.' || oel.shipment_number
into v_problem_model_line_num
from oe_order_lines_all oel
where line_id = pLineId ;
SELECT u.user_name
INTO lplanner_code
FROM mtl_system_items_vl item
,mtl_planners p
,fnd_user u
WHERE item.inventory_item_id = pModelId
and item.organization_id = lNextRec.organization_id
and p.organization_id = item.organization_id
and p.planner_code = item.planner_code
and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
select segment1
into v_problem_model
from mtl_system_items
where inventory_item_id = pModelId
and rownum = 1 ;
select segment1
into v_problem_config
from mtl_system_items
where inventory_item_id = pConfigId
and rownum = 1 ;
select organization_name
into v_error_org
from inv_organization_name_v
where organization_id = lNextRec.organization_id ;
select oel.line_number || '.' || oel.shipment_number
into v_problem_model_line_num
from oe_order_lines_all oel
where line_id = pLineId ;
SELECT u.user_name
INTO lplanner_code
FROM mtl_system_items_vl item
,mtl_planners p
,fnd_user u
WHERE item.inventory_item_id = pModelId
and item.organization_id = lNextRec.organization_id
and p.organization_id = item.organization_id
and p.planner_code = item.planner_code
and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
delete from bom_inventory_comps_interface
where bill_sequence_id = lComItmBillId
and batch_id = cto_msutil_pub.bom_batch_id;
delete from bom_inventory_comps_interface
where bill_sequence_id = lCfgBillId;
delete from bom_inventory_comps_interface
where bill_sequence_id = lCfgBillId;
delete from bom_inventory_comps_interface
where bill_sequence_id = lCfgBillId;
select concatenated_segments into v_model_item_name
from mtl_system_items_kfv
where inventory_item_id = pModelId
and rownum = 1 ;
function update_atp( pLineId in number,
xErrorMessage out NOCOPY varchar2,
xMessageName out NOCOPY varchar2,
xTableName out NOCOPY varchar2)
return integer
is
p_atp_table MRP_ATP_PUB.ATP_Rec_Typ;
select oel.inventory_item_id,
oel.ship_from_org_id,
oel.line_id,
oel.ordered_quantity,
oel.order_quantity_uom,
oel.request_date,
oel.demand_class_code,
temp, -- calling module
temp, -- customer_id
temp, -- customer_site_id
temp, -- destination_time_zone
oel.schedule_arrival_date,
temp1, -- latest acceptable_date
oel.delivery_lead_time, -- delivery lead time
temp, -- Freight_Carrier
temp, -- Ship_Method
temp, --Ship_Set_Name
temp, -- Arrival_Set_Name
1, -- Override_Flag
temp, -- Action
temp1, -- Ship_date
temp, -- available_quantity
temp, -- requested_date_quantity
temp1, -- group_ship_date
temp1, -- group_arrival_date
temp, -- vendor_id
temp, -- vendor_site_id
temp, -- insert_flag
temp, -- error_code
temp -- Message
bulk collect into
p_atp_table.Inventory_Item_Id ,
p_atp_table.Source_Organization_Id ,
p_atp_table.Identifier ,
p_atp_table.Quantity_Ordered ,
p_atp_table.Quantity_UOM ,
p_atp_table.Requested_Ship_Date ,
p_atp_table.Demand_Class ,
p_atp_table.Calling_Module ,
p_atp_table.Customer_Id ,
p_atp_table.Customer_Site_Id ,
p_atp_table.Destination_Time_Zone ,
p_atp_table.Requested_Arrival_Date ,
p_atp_table.Latest_Acceptable_Date ,
p_atp_table.Delivery_Lead_Time ,
p_atp_table.Freight_Carrier ,
p_atp_table.Ship_Method ,
p_atp_table.Ship_Set_Name ,
p_atp_table.Arrival_Set_Name ,
p_atp_table.Override_Flag ,
p_atp_table.Action ,
p_atp_table.Ship_Date ,
p_atp_table.Available_Quantity ,
p_atp_table.Requested_Date_Quantity ,
p_atp_table.Group_Ship_Date ,
p_atp_table.Group_Arrival_Date ,
p_atp_table.Vendor_Id ,
p_atp_table.Vendor_Site_Id ,
p_atp_table.Insert_Flag ,
p_atp_table.Error_Code ,
p_atp_table.Message
from oe_order_lines_all oel,
oe_order_lines_all oel1,
mtl_system_items msi
where msi.inventory_item_id = oel.inventory_item_id
and msi.organization_id = oel.ship_from_org_id
and msi.bom_item_type = 1
and oel.line_id = pLineId
--and oel.item_type_code = 'MODEL'
and oel1.item_type_code = 'CONFIG'
--and oel1.top_model_line_id = pLineId
and oel1.ato_line_id = pLineId
and oel1.link_to_line_id = pLineId
and oel1.ordered_quantity > 0 ;
oe_debug_pub.add('update_atp: ' || ' Line Id ' || p_atp_table.identifier(1));
oe_debug_pub.add('update_atp: ' || ' Inventory Id ' || p_atp_table.inventory_item_id(1));
oe_debug_pub.add('update_atp: ' || ' Req Date ' || p_atp_table.requested_ship_date(1));
oe_debug_pub.add('update_atp: ' || ' qty ' || p_atp_table.quantity_ordered(1));
oe_debug_pub.add('update_atp: ' || 'From output record ---> ',1);
oe_debug_pub.add('update_atp: ' || ' Line Id '
|| l_smc_table.identifier(i));
oe_debug_pub.add('update_atp: ' || ' Inventory Id '
|| l_smc_table.inventory_item_id(i));
oe_debug_pub.add('update_atp: ' || ' Req Date '
|| l_smc_table.requested_ship_date(i));
oe_debug_pub.add('update_atp: ' || ' qty '
|| l_smc_table.quantity_ordered(i));
oe_debug_pub.add('update_atp: ' || 'Calling ATP ---> ',1);
oe_debug_pub.add('update_atp: ' || 'ATP returned ' || l_return_status);
oe_debug_pub.add('update_atp: ' || 'No Mandatory components for ATP found' );
xErrormessage := 'update_atp:'||to_char(lStmt)||':'||' ATP API returned Error';
xErrorMessage := 'update_atp:'||to_char(lStmt)||':'||substrb(sqlerrm,1,150) ;
end update_atp;
Updateable VARCHAR2(10) := null ;
Cursor Check_Updateable is
Select 'YES'
From MTL_MATERIAL_TRANSACTIONS t
Where Inventory_Item_Id = p_inventory_item_id
And Exists
(Select 'all these org have the org as costing org'
From MTL_PARAMETERS
Where Cost_Organization_Id = p_organization_id
AND Organization_Id = t.Organization_Id);
Cursor Check_Updateable_2 is
Select 'YES'
From MTL_MATERIAL_TRANSACTIONS_TEMP t
Where Inventory_Item_Id = p_inventory_item_id
And Exists
(Select 'all these org have the org as costing org'
From MTL_PARAMETERS
Where Cost_Organization_Id = p_organization_id
AND Organization_Id = t.Organization_Id);
IF (Updateable is NULL) THEN
Open Check_Updateable;
Fetch Check_Updateable into Updateable;
Close Check_Updateable;
IF (Updateable is Null) THEN
Open Check_Updateable_2;
Fetch Check_Updateable_2 into Updateable;
Close Check_Updateable_2;
IF (Updateable is NULL) THEN
select count(*)
into intransit_count
from mtl_supply m
where m.item_id = p_inventory_item_id
and m.intransit_owning_org_id = p_organization_id
and m.to_organization_id = p_organization_id ;
Updateable := 'YES';
IF (Updateable = 'YES') THEN
-- fnd_message.Set_Name('BOM', 'CST_ITEM_USED_IN_TXN');
This procedure updates cost_rollup_flag in bcso to avoid cost rollup.
+-------------------------------------------------------------*/
procedure override_bcso_cost_rollup(
pLineId in number, -- Current Model Line ID
pModelId in number,
pConfigId in number,
p_cost_organization_id in number,
p_organization_id in number,
p_group_reference_id in number,
xReturnStatus out NOCOPY varchar2,
xMsgCount out NOCOPY number,
xMsgData out NOCOPY varchar2
)
IS
lStmtNum number;
select mp1.primary_cost_method into v_primary_cost_method
from mtl_parameters mp1
where mp1.organization_id = p_cost_organization_id ; -- 3116778
select cost_type_id into v_cto_cost_type_id
from cst_cost_types
where cost_type = 'CTO' ;
select cost_type_id into v_buy_cost_type_id
from cst_cost_types
where cost_type = lBuyCostType ;
select item_cost into v_cto_cost from cst_item_costs
where inventory_item_id = pConfigId
and organization_id = p_cost_organization_id -- 3116778
and cost_type_id = v_cto_cost_type_id ;
select sum(item_cost) into v_cto_cost_xudc from cst_item_cost_details
where inventory_item_id = pConfigId
and organization_id = p_cost_organization_id -- 3116778
and cost_type_id = v_cto_cost_type_id
and rollup_source_type = 3 ; -- bugfix 2808704
select item_cost into v_valuation_cost from cst_item_costs
where inventory_item_id = pConfigId
and organization_id = p_cost_organization_id -- 3116778
and cost_type_id = v_primary_cost_method ;
select count(*) , sum(item_cost) into v_rolledup_cost_count, v_rolledup_cost
from cst_item_cost_details
where inventory_item_id = pConfigId
and organization_id = p_cost_organization_id -- 3116778l
and cost_type_id = v_primary_cost_method
and rollup_source_type = 3 ;
select item_cost into v_buy_cost from cst_item_costs
where inventory_item_id = pConfigId
and organization_id = p_cost_organization_id -- 3116778
and cost_type_id = v_buy_cost_type_id ;
select nvl( organization_type , 1 ) into v_organization_type
from bom_cto_src_orgs
where line_id = pLineId
and cost_rollup = 'Y'
and organization_id = p_organization_id ; -- added for bug 3877317 copied from fp.
select organization_type into v_organization_type
from bom_cto_src_orgs
where line_id = pLineId
and rcv_org_id = p_organization_id
and organization_id = p_organization_id
and organization_type = '2'
and cost_rollup = 'Y' ;
update bom_cto_src_orgs_b
set cost_rollup = 'N'
where line_id = pLineId
and organization_id = p_organization_id ;
oe_debug_pub.add( ' updated bcso cost rollup N for line ' || pLineId
|| ' org ' || p_organization_id
|| ' count ' || to_char(sql%rowcount)
, 1 ) ;
update bom_cto_model_orgs
set cost_rollup = 'N'
where group_reference_id = p_group_reference_id
and organization_id = p_organization_id ;
oe_debug_pub.add( ' updated bcmo cost rollup N for group_ref ' || p_group_reference_id
|| ' org ' || p_organization_id
|| ' count ' || to_char(sql%rowcount)
, 1 ) ;
update bom_cto_src_orgs_b
set cost_rollup = 'N'
where line_id = pLineId
and organization_id = p_organization_id ;
oe_debug_pub.add( ' updated bcso cost rollup N for line ' || pLineId
|| ' org ' || p_organization_id
|| ' count ' || to_char(sql%rowcount)
, 1 ) ;
update bom_cto_model_orgs
set cost_rollup = 'N'
where group_reference_id = p_group_reference_id
and organization_id = p_organization_id ;
oe_debug_pub.add( ' updated bcmo cost rollup N for group_ref ' || p_group_reference_id
|| ' org ' || p_organization_id
|| ' count ' || to_char(sql%rowcount)
, 1 ) ;
update bom_cto_src_orgs_b
set cost_rollup = 'N'
where line_id = pLineId
and organization_id = p_organization_id ;
oe_debug_pub.add( ' updated bcso cost rollup N for line ' || pLineId
|| ' org ' || p_organization_id
|| ' count ' || to_char(sql%rowcount)
, 1 ) ;
update bom_cto_model_orgs
set cost_rollup = 'N'
where group_reference_id = p_group_reference_id
and organization_id = p_organization_id ;
oe_debug_pub.add( ' updated bcmo cost rollup N for group_ref ' || p_group_reference_id
|| ' org ' || p_organization_id
|| ' count ' || to_char(sql%rowcount)
, 1 ) ;
update bom_cto_src_orgs_b
set cost_rollup = 'N'
where line_id = pLineId
and organization_id = p_organization_id ;
oe_debug_pub.add( ' updated bcso cost rollup N for line ' || pLineId
|| ' org ' || p_organization_id
|| ' count ' || to_char(sql%rowcount)
, 1 ) ;
update bom_cto_model_orgs
set cost_rollup = 'N'
where group_reference_id = p_group_reference_id
and organization_id = p_organization_id ;
oe_debug_pub.add( ' updated bcmo cost rollup N for group_ref ' || p_group_reference_id
|| ' org ' || p_organization_id
|| ' count ' || to_char(sql%rowcount)
, 1 ) ;