[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| is updated accordingly. |
| |
| |
| HISTORY : |
| 06/13/93 Chung Wei Lee Initial version |
| 08/16/93 Chung Wei Lee Added more comments |
| 08/23/93 Chung Wei Lee Added codes to check dup new config |
| 11/08/93 Randy Roupp Added sql_stmt_num logic |
| 11/09/93 Randy Roupp Changed is_base_demand_row function |
| 01/14/94 Nagaraj Handle the case if d1.primary_uom_ |
| quantity is zero |
| 02/21/94 Manish Modi Moved bomfcdec_ch_du_ext_config to |
| BOMPCEDC. |
| 11/01/95 Edward Lee Re-wrote package to use a matching |
| function similar to the one in |
| BOMPMCFG which drives off of so_lines|
| Also added a check for existing |
| configurations in BOM ATO Configs. |
=============================================================================*/
function is_base_demand_row(
input_demand_id in number,
error_message out VARCHAR2, /* 70 bytes to hold returned msg */
message_name out VARCHAR2, /* 30 bytes to hold returned name*/
table_name out VARCHAR2 /* 30 bytes to hold returned tbl */
)
return integer
is
l_demand_count number;
select duplicated_config_demand_id, duplicated_config_item_id into
l_dup_demand_id, l_dup_item_id
from mtl_demand
where demand_id = input_demand_id
and config_group_id = USERENV('SESSIONID')
and config_status = 20
and rownum = 1; /* demand_id is a duplicated_config_demand_id
select demand_id, demand_source_line
from mtl_demand D,
mtl_sales_orders S
where D.config_group_id = USERENV('SESSIONID')
and D.duplicated_config_item_id is NULL
and D.demand_source_header_id = S.sales_order_id
order by S.segment1 desc,D.user_line_num desc;
select nvl(substr(profile_option_value,1,30),'0') into match_profile
from fnd_profile_option_values val,fnd_profile_options op
where op.profile_option_name = 'BOM:CHECK_DUPL_CONFIG'
and val.level_id = 10001
and val.profile_option_id = op.profile_option_id;
update mtl_demand
set duplicated_config_item_id= l_config_item_id
where demand_id = l_demand_id;
select NVL(cfm_routing_flag,0) into cfm_value
from mtl_demand md,
bom_operational_routings bor
where md.inventory_item_id = bor.assembly_item_id(+)
and md.organization_id = bor.organization_id(+)
and bor.alternate_routing_designator(+) is NULL
and md.demand_id = input_demand_id;
update mtl_demand m
set m.duplicated_config_item_id = (
select BAC1.config_item_id
from BOM_ATO_CONFIGURATIONS BAC1, /* the duplicate */
so_lines_all solp, /* Parent of ATO Model if any */
so_lines_all sol1, /* processing */
mtl_system_items msi,
bom_operational_routings bor,
bom_parameters bp
where BAC1.base_model_id = sol1.inventory_item_id
and BAC1.organization_id = sol1.warehouse_id
and BAC1.component_item_id = sol1.inventory_item_id
and bp.organization_id = BAC1.organization_id
and NVL(BAC1.cfm_routing_flag,0) = cfm_value
and solp.line_id = nvl(sol1.link_to_line_id,sol1.line_id)
and msi.organization_id = BAC1.organization_id
and msi.inventory_item_id = BAC1.config_item_id
and msi.inventory_item_status_code <> bp.bom_delete_status_code
and not exists (select 'X'
from so_lines_all sol5 /* current */
where sol5.ato_line_id = sol1.line_id
and sol5.ordered_quantity > nvl(sol5.cancelled_quantity,0)
and sol5.inventory_item_id not in
(select BAC2.component_item_id
from BOM_ATO_CONFIGURATIONS BAC2 /* duplicates */
where BAC2.config_item_id = BAC1.config_item_id
and BAC2.component_item_id <> BAC1.component_item_id
and BAC2.component_item_id = sol5.inventory_item_id
and BAC2.component_code =
decode(sol1.link_to_line_id,NULL,sol5.component_code,
substrb(sol5.component_code,
lengthb(solp.component_code)+2))
and BAC2.COMPONENT_QUANTITY =
((sol5.ordered_quantity-nvl(sol5.cancelled_quantity,0))/
(sol1.ordered_quantity-nvl(sol1.cancelled_quantity,0)))
)
)
and exists(select 'X'
from BOM_ATO_CONFIGURATIONS BAC3 /* duplicates */
where BAC3.config_item_id = BAC1.config_item_id
and BAC3.component_item_id <> BAC1.component_item_id
having count(*) = (select count (*)
from so_lines_all sol7 /* processing */
where sol7.ato_line_id = sol1.line_id
and sol7.ordered_quantity>nvl(sol7.cancelled_quantity,0)
)
)
and sol1.line_id = m.demand_source_line
and rownum = 1
)
where m.demand_id = input_demand_id
and m.config_group_id = USERENV('SESSIONID')
and m.demand_type = 1
and m.duplicated_config_item_id is NULL;
select duplicated_config_item_id into match_results
from mtl_demand
where config_group_id = USERENV('SESSIONID')
and demand_id = input_demand_id;
/* **** is this REALLY necessary? just updated it*** */
if (match_results is not null) then
stmt_num := 70;
update bom_ato_configurations
set last_referenced_date = SYSDATE
where config_item_id = match_results;
update mtl_demand m
set m.duplicated_config_item_id = temp_item_id,
m.duplicated_config_demand_id = dupl_demand_id
where m.config_group_id = USERENV('SESSIONID')
and m.demand_id in (
select m1.demand_id
from so_lines_all soldp, /* parent of duplicate */
so_lines_all solp, /* parent of other lines */
so_lines_all sol1, /* processing other lines */
so_lines_all sold, /* current -- duplicate */
mtl_demand m1
where sol1.line_id=m1.demand_source_line
and m1.config_group_id = USERENV('SESSIONID')
and m1.demand_id <> input_demand_id
and sold.line_id = copy_line_id
and sold.inventory_item_id = sol1.inventory_item_id +0
and sold.warehouse_id = sol1.warehouse_id
and solp.line_id = nvl(sol1.link_to_line_id,sol1.line_id)
and soldp.line_id = nvl(sold.link_to_line_id,sold.line_id)
and m1.config_status = 20
and m1.duplicated_config_item_id is null
and m1.duplicated_config_demand_id is null
and exists(select 'X'
from so_lines_all sold3 /* duplicates */
where sold3.ato_line_id = sold.line_id
and sold3.ordered_quantity>nvl(sold3.cancelled_quantity,0)
having count(*) = (select count (*)
from so_lines_all sol7 /* processing */
where sol7.ato_line_id = sol1.line_id
and sol7.ordered_quantity>nvl(sol7.cancelled_quantity,0)
)
)
and not exists (select 'X'
from so_lines_all sol5 /* current */
where sol5.ato_line_id = sol1.line_id
and sol5.ordered_quantity > nvl(sol5.cancelled_quantity,0)
and sol5.inventory_item_id not in
(select sold2.inventory_item_id
from so_lines_all sold2 /* duplicates */
where sold2.ato_line_id = sold.line_id
and sold2.component_code = sol5.component_code
and decode(sold.link_to_line_id,NULL,sold2.component_code,
substrb(sold2.component_code,
lengthb(soldp.component_code)+2)) =
decode(sol1.link_to_line_id,NULL,sol5.component_code,
substrb(sol5.component_code,
lengthb(solp.component_code)+2))
and
((sold2.ordered_quantity-nvl(sold2.cancelled_quantity,0))/(sold.ordered_quantity-nvl(sold.cancelled_quantity,0))) =
((sol5.ordered_quantity-nvl(sol5.cancelled_quantity,0))/
(sol1.ordered_quantity-nvl(sol1.cancelled_quantity,0)))
)
)
);