[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sum(demand_quantity)/count(distinct demand_date)
into l_avg_demand
from mrp_kanban_demand
where kanban_plan_id = p_kanban_plan_id
and organization_id = p_org_id
and inventory_item_id = p_item_id
and subinventory = p_subinv
and nvl(locator_id,-10) = nvl(p_locator,-10)
and demand_quantity is not null
and trunc(demand_date) between trunc(sysdate) and trunc(sysdate + l_interval);
select application_column_name col_name,form_left_prompt prompt from FND_DESCR_FLEX_COL_USAGE_TL
where descriptive_flexfield_name = 'MTL_KANBAN_PULL_SEQUENCES'
and language = userenv('LANG')
and descriptive_flex_context_code = 'Global Data Elements';
select MTL_KANBAN_PULL_SEQUENCES_S.NEXTVAL into l_pull_seq_id from dual;
INSERT INTO MTL_KANBAN_PULL_SEQUENCES
(Pull_sequence_id,
Inventory_item_id,
Organization_id,
Subinventory_name,
Kanban_Plan_id,
Source_type,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Locator_id,
Calculate_Kanban_flag,
Kanban_size,
Number_of_cards,
Minimum_order_quantity,
Allocation_Percent,
Replenishment_lead_time,
Fixed_Lot_multiplier,
Safety_Stock_Days,
Attribute_Category,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
FUTURE_NO_OF_CARDS,
FUTURE_CARD_SIZE,
PLANNING_EFFECTIVITY)
select l_pull_seq_id,
Inventory_item_id,
Organization_id,
Subinventory_name,
0,
Source_type,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
Locator_id,
Calculate_Kanban_flag,
Kanban_size,
Number_of_cards,
Minimum_order_quantity,
Allocation_Percent,
Replenishment_lead_time,
Fixed_Lot_multiplier,
Safety_Stock_Days,
Attribute_Category,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
FUTURE_NO_OF_CARDS,
FUTURE_CARD_SIZE,
PLANNING_EFFECTIVITY
from MTL_KANBAN_PULL_SEQUENCES
where pull_sequence_id = p_pull_sequence_id;
l_updated_pullseq number;
update MTL_KANBAN_PULL_SEQUENCES
set Number_of_cards = decode(Calculate_Kanban_flag,2,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_CARDS(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,
KANBAN_SIZE,AVG_DEPENDENT_DEMAND,ALLOCATION_PERCENT,REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,
MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),number_of_cards ),
Kanban_size = decode(Calculate_Kanban_flag,1,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_SIZE(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,NUMBER_OF_CARDS,
AVG_DEPENDENT_DEMAND,ALLOCATION_PERCENT,REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),Kanban_size ),
FUTURE_NO_OF_CARDS = decode(Calculate_Kanban_flag,2,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_CARDS(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,KANBAN_SIZE,
AVG_DEPENDENT_DEMAND,ALLOCATION_PERCENT,REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),Number_of_cards ),
FUTURE_CARD_SIZE = decode(Calculate_Kanban_flag,1,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_SIZE(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,
NUMBER_OF_CARDS,AVG_DEPENDENT_DEMAND,ALLOCATION_PERCENT,REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,
FIXED_LOT_MULTIPLIER),Kanban_size ),
NO_OF_TEMP_CARDS = null,
NO_OF_TEMP_CYCLES = null,
TEMP_EXPIRY_DATE = null
where UPDATED_FLAG = p_request_id;
select min(HP.plan_date),max(HP.plan_date), min(HP.organization_id)
into l_plan_start_date,l_plan_end_date, l_org_id
from FLM_EKB_HORIZONTAL_PLAN_TEMP HP,MTL_KANBAN_PULL_SEQUENCES PS
where HP.PULL_SEQUENCE_ID = PS.PULL_SEQUENCE_ID
and PS.UPDATED_FLAG = p_request_id;
v_exp_date.delete;
v_exp_repl_qty.delete;
v_repl_no_of_cards.delete;
v_exp_pull_seq_id.delete;
v_repl_date.delete;
select pull_seq,
expected_date,
replenished_date,
sum(qty) expected_qty,
count(distinct card) replenished_card
bulk collect into v_exp_pull_seq_id,
v_exp_date,
v_repl_date,
v_exp_repl_qty,
v_repl_no_of_cards
from (select plan.pull_sequence_id pull_seq,
mkc.kanban_card_id card,
mkc.kanban_size qty,
min(mrp_calendar.NEXT_WORK_DAY(mkps.organization_id,1,
case when trunc(mkca.creation_date+mkps.replenishment_lead_time) < l_plan_start_date then l_plan_start_date
else trunc(mkca.creation_date+mkps.replenishment_lead_time) end )) expected_date,
min(mrp_calendar.NEXT_WORK_DAY(mkps.organization_id,1,trunc(mkca.creation_date))) replenished_date
from mtl_kanban_card_activity mkca,
mtl_kanban_cards mkc,
mtl_kanban_pull_sequences mkps,
mtl_kanban_pull_sequences plan
where mkps.organization_id = plan.organization_id
and mkps.inventory_item_id = plan.inventory_item_id
and mkps.subinventory_name = plan.subinventory_name
and nvl(mkps.locator_id,-100) = nvl(plan.locator_id,-100)
and mkps.kanban_plan_id = -1
and mkps.pull_sequence_id = mkc.pull_sequence_id
and trunc(mkc.supply_status) in (4,5,6,7)
and mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id
and mkc.KANBAN_CARD_ID = mkca.kanban_card_id
and plan.UPDATED_FLAG = p_request_id
group by plan.pull_sequence_id,mkc.kanban_card_id,mkc.kanban_size)
where expected_date between l_plan_start_date and l_plan_end_date
or replenished_date between l_plan_start_date and l_plan_end_date
group by pull_seq,expected_date,replenished_date;
v_expected_receipts.delete;
v_cards_replenished.delete;
select HP.PLANNING_TEMP_ID,HP.PULL_SEQUENCE_ID,HP.BEGINNING_ONHAND,0 EXPECTED_RECEIPTS,HP.DAILY_DEMAND,
HP.ADJUSTED_DAILY_DEMAND,HP.AVAILABLE_ONHAND,HP.ITEM_UNIT_COST,HP.INVENTORY_VALUE,HP.CARDS_ONHAND,
PS.SAFETY_STOCK_DAYS,PS.AVG_DEPENDENT_DEMAND ,HP.SAFETY_STOCK_TARGET,HP.ADJUSTED_SAFETY_STOCK_DAYS,
nvl(ceil(PS.REPLENISHMENT_LEAD_TIME),0) REPLENISHMENT_LEAD_TIME,decode( nvl(PS.KANBAN_SIZE,0),0,1,PS.KANBAN_SIZE),
trunc(HP.PLAN_DATE),0 CARDS_REPLENISHED
bulk collect into v_planning_temp_tbl
from FLM_EKB_HORIZONTAL_PLAN_TEMP HP,
MTL_KANBAN_PULL_SEQUENCES PS
where HP.PULL_SEQUENCE_ID = PS.PULL_SEQUENCE_ID
and PS.UPDATED_FLAG = p_request_id
order by HP.PULL_SEQUENCE_ID,HP.plan_date;
select decode(nvl(kanban_size,0),0,1,kanban_size),decode(nvl(number_of_cards,0),0,1,number_of_cards),kanban_plan_id
into l_kanban_size,l_no_of_cards,l_kanban_plan_id
from mtl_kanban_pull_sequences mkps
where PULL_SEQUENCE_ID = v_planning_temp_tbl(l_counter).PULL_SEQUENCE_ID;
update FLM_EKB_HORIZONTAL_PLAN_TEMP
set BEGINNING_ONHAND = v_planning_temp_tbl(cntr).BEGINNING_ONHAND,
AVAILABLE_ONHAND = v_planning_temp_tbl(cntr).AVAILABLE_ONHAND,
INVENTORY_VALUE = v_planning_temp_tbl(cntr).INVENTORY_VALUE,
CARDS_ONHAND = v_planning_temp_tbl(cntr).CARDS_ONHAND,
SAFETY_STOCK_DAYS = v_planning_temp_tbl(cntr).SAFETY_STOCK_DAYS,
DAILY_DEMAND = v_planning_temp_tbl(cntr).DAILY_DEMAND,
SAFETY_STOCK_TARGET = v_planning_temp_tbl(cntr).SAFETY_STOCK_TARGET,
EXPECTED_RECEIPTS = v_planning_temp_tbl(cntr).EXPECTED_RECEIPTS,
CARDS_REPLENISHED = v_planning_temp_tbl(cntr).CARDS_REPLENISHED
where PLANNING_TEMP_ID = v_planning_temp_tbl(cntr).PLANNING_TEMP_ID;
delete from MTL_KANBAN_PULL_SEQUENCES
where inventory_item_id = p_item_id and
organization_id = p_org_id and
Subinventory_name = p_subinv and
Locator_id = p_locator and
Kanban_Plan_id = 0;
select nvl(trunc(plan_start_date),trunc(sysdate)), nvl(trunc(plan_cutoff_date),nvl(trunc(plan_start_date),trunc(sysdate))+30)
into l_start_date,l_end_date
from mrp_kanban_plans where kanban_plan_id = p_kanban_plan;
delete from FLM_EKB_HORIZONTAL_PLAN_TEMP where Kanban_Plan_id = l_kanban_plan;
select *
from FLM_EKB_DEMAND_INTERFACE
where process_status = running
and organization_id = org_id;
select organization_code
into l_org_code
from mtl_parameters
where organization_id = p_organization_id;
update FLM_EKB_DEMAND_INTERFACE FEDI
set process_status = running,
organization_id = nvl(organization_id,p_organization_id),
request_id = fnd_global.conc_request_id,
program_id = fnd_global.conc_program_id,
program_application_id = fnd_global.prog_appl_id,
program_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
LAST_UPDATED_LOGIN = fnd_global.login_id
where
process_status = pending
and (organization_id = p_organization_id or
organization_code = (select mp.organization_code
from mtl_parameters mp
where mp.organization_id = p_organization_id) or
exists (select 1 from mtl_kanban_pull_sequences mkps
where mkps.organization_id = p_organization_id and
mkps.pull_sequence_id = fedi.pull_sequence_id and rownum = 1 ));
select inventory_item_id
into l_item_id
from mtl_system_items_kfv
where organization_id = p_org_id
and concatenated_segments = p_item_segments;
select inventory_location_id
into l_locator_id
from mtl_item_locations_kfv
where organization_id = p_org_id
and concatenated_segments = p_locator_segments;
select organization_id,inventory_item_id,subinventory_name,locator_id
into l_flm_kanban_demand_intf(l_counter).organization_id,
l_flm_kanban_demand_intf(l_counter).inventory_item_id,
l_flm_kanban_demand_intf(l_counter).subinventory,
l_flm_kanban_demand_intf(l_counter).locator_id
from mtl_kanban_pull_sequences where pull_sequence_id = l_flm_kanban_demand_intf(l_counter).PULL_SEQUENCE_ID
and organization_id = l_flm_kanban_demand_intf(l_counter).organization_id
and kanban_plan_id = -1;
select 1 into l_temp
from mtl_system_items_kfv
where organization_id = l_flm_kanban_demand_intf(l_counter).organization_id
and inventory_item_id = l_flm_kanban_demand_intf(l_counter).INVENTORY_ITEM_ID;
select 1 into l_temp
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_flm_kanban_demand_intf(l_counter).subinventory
AND organization_id = l_flm_kanban_demand_intf(l_counter).organization_id;
select 1 into l_temp
FROM mtl_item_locations_kfv
WHERE inventory_location_id = l_flm_kanban_demand_intf(l_counter).locator_id
AND organization_id = l_flm_kanban_demand_intf(l_counter).organization_id;
select pull_sequence_id into l_flm_kanban_demand_intf(l_counter).PULL_SEQUENCE_ID
FROM mtl_kanban_pull_sequences
WHERE organization_id = l_flm_kanban_demand_intf(l_counter).organization_id
AND inventory_item_id = l_flm_kanban_demand_intf(l_counter).inventory_item_id
and subinventory_name = l_flm_kanban_demand_intf(l_counter).subinventory
and nvl(locator_id,-1) = nvl(l_flm_kanban_demand_intf(l_counter).locator_id,-1)
and kanban_plan_id = -1;
select mrp_kanban_demand_s.nextval into v_flm_kanban_demand(ins_counter).DEMAND_ID from dual;
v_flm_kanban_demand(ins_counter).last_updated_by := fnd_global.user_id;
v_flm_kanban_demand(ins_counter).last_update_date := sysdate;
v_flm_kanban_demand(ins_counter).last_updated_login := fnd_global.login_id;
v_flm_kanban_demand(ins_counter).program_update_date := sysdate;
INSERT into MRP_KANBAN_DEMAND values v_flm_kanban_demand(ins_index);
update FLM_EKB_DEMAND_INTERFACE set
process_status = decode(v_process_status(upd_index),running,completed,v_process_status(upd_index)),
error = v_error(upd_index),
last_update_date = sysdate
where interface_id = v_interface_id(upd_index);
select MTL_KANBAN_PULL_SEQUENCES_S.NEXTVAL
into l_next_pull_seq_id
from dual;
select count(1)
into l_temp_count
from mtl_kanban_cards
where pull_sequence_id = p_pull_sequence_id and
card_status in (1,2) and ( disable_date is not null or max_replenishments is not null);
SELECT ( SUM(KANBAN_SIZE) / decode(l_range,0,1,l_range) )
into l_actual_demand
FROM FLM_EKB_ACT_SNAPSHOT
WHERE PULL_SEQUENCE_ID = p_pull_sequence_id
and REPLENISHMENT_DATE between l_start_date and l_end_date;
SELECT ( SUM(LEAD_TIME) / decode(count(1),0,1,count(1)) )
into l_act_lead_time
FROM FLM_EKB_ACT_SNAPSHOT
WHERE PULL_SEQUENCE_ID = p_pull_sequence_id
and LEAD_TIME > 0
and RECEIVED_DATE between l_start_date and l_end_date;
l_sql := 'delete from MTL_KANBAN_PULL_SEQUENCES mkps1 where mkps1.Kanban_Plan_id = 0 and mkps1.pull_sequence_id in'||
'(select syspullseq.pull_sequence_id from MTL_KANBAN_PULL_SEQUENCES syspullseq, MTL_KANBAN_PULL_SEQUENCES prodpullSeq'||
' where syspullseq.kanban_plan_id = 0 and prodpullSeq.kanban_plan_id = -1 '||
' and syspullseq.organization_id = prodpullSeq.organization_id '||
' and syspullseq.inventory_item_id = prodpullSeq.inventory_item_id '||
' and syspullseq.Subinventory_name = prodpullSeq.Subinventory_name '||
' and nvl(syspullseq.Locator_id,-1) = nvl(prodpullSeq.Locator_id ,-1) '||
' and prodpullSeq.pull_sequence_id in ( '|| p_pull_seq_ids || ' )) ';
l_sql := 'INSERT INTO MTL_KANBAN_PULL_SEQUENCES (Pull_sequence_id,Inventory_item_id,Organization_id,Subinventory_name,Kanban_Plan_id,'||
'Source_type,Last_Update_Date,Last_Updated_By,Creation_Date,Created_By,Last_Update_Login,Locator_id,Calculate_Kanban_flag,'||
'Kanban_size,Number_of_cards,Minimum_order_quantity,Allocation_Percent,Replenishment_lead_time,Fixed_Lot_multiplier,'||
'Safety_Stock_Days,Attribute_Category,Attribute1,Attribute2,Attribute3,Attribute4,Attribute5,Attribute6,Attribute7,Attribute8,'||
'Attribute9,Attribute10,Attribute11,Attribute12,Attribute13,Attribute14,Attribute15,'||
'PLANNING_EFFECTIVITY,UPDATED_FLAG, RELEASE_KANBAN_FLAG,AVG_DEPENDENT_DEMAND,FUTURE_CARD_SIZE,FUTURE_NO_OF_CARDS)'||
'select MTL_KANBAN_PULL_SEQUENCES_S.NEXTVAL,Inventory_item_id,Organization_id,Subinventory_name,0,'||
'Source_type,sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id,Locator_id,Calculate_Kanban_flag,'||
'decode(Calculate_Kanban_flag,1,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_SIZE(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,NUMBER_OF_CARDS,'||
'nvl(FLM_KANBAN_PLANNING_UTIL.get_average_demand(Organization_id,0,Inventory_item_id,Subinventory_name,Locator_id),nvl(AVG_DEPENDENT_DEMAND,0)),ALLOCATION_PERCENT,'||
'REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),Kanban_size),'||
'decode(Calculate_Kanban_flag,2,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_CARDS(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,KANBAN_SIZE,'||
'nvl(FLM_KANBAN_PLANNING_UTIL.get_average_demand(Organization_id,0,Inventory_item_id,Subinventory_name,Locator_id),nvl(AVG_DEPENDENT_DEMAND,0)),ALLOCATION_PERCENT,'||
'REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),Number_of_cards ),'||
'Minimum_order_quantity,Allocation_Percent,Replenishment_lead_time,Fixed_Lot_multiplier,'||
'Safety_Stock_Days,Attribute_Category,Attribute1,Attribute2,Attribute3,Attribute4,Attribute5,Attribute6,Attribute7,Attribute8,'||
'Attribute9,Attribute10,Attribute11,Attribute12,Attribute13,Attribute14,Attribute15,'||
'PLANNING_EFFECTIVITY,'||to_char(l_value)||',RELEASE_KANBAN_FLAG,'||
'nvl(FLM_KANBAN_PLANNING_UTIL.get_average_demand(Organization_id,0,Inventory_item_id,Subinventory_name,Locator_id),nvl(AVG_DEPENDENT_DEMAND,0)),'||
'decode(Calculate_Kanban_flag,1,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_SIZE(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,NUMBER_OF_CARDS,'||
'FLM_KANBAN_PLANNING_UTIL.get_average_demand(Organization_id,0,Inventory_item_id,Subinventory_name,Locator_id),'||
'ALLOCATION_PERCENT,REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),Kanban_size),'||
'decode(Calculate_Kanban_flag,2,FLM_KANBAN_PLANNING_UTIL.GET_CALCULATED_CARDS(PULL_SEQUENCE_ID,CALCULATE_KANBAN_FLAG,KANBAN_SIZE,'||
'nvl(FLM_KANBAN_PLANNING_UTIL.get_average_demand(Organization_id,0,Inventory_item_id,Subinventory_name,Locator_id),nvl(AVG_DEPENDENT_DEMAND,0)),'||
'ALLOCATION_PERCENT,REPLENISHMENT_LEAD_TIME,SAFETY_STOCK_DAYS,MINIMUM_ORDER_QUANTITY,FIXED_LOT_MULTIPLIER),Number_of_cards)'||
' from MTL_KANBAN_PULL_SEQUENCES where pull_sequence_id in ('||p_pull_seq_ids||')' ;
l_sql := 'update MTL_KANBAN_PULL_SEQUENCES set UPDATED_FLAG = '||l_value||
',FUTURE_NO_OF_CARDS = Number_of_cards,FUTURE_CARD_SIZE= Kanban_size where pull_sequence_id in ('||p_pull_seq_ids||')';
v_plan_pull_seq_tbl.delete;
select mkps.pull_sequence_id,mkps.organization_id,mkps.kanban_plan_id,mkps.inventory_item_id,mkps.Subinventory_name,mkps.locator_id,
mkps.Safety_Stock_Days,decode(nvl(mkps.Kanban_size,0),0,1,mkps.Kanban_size) Kanban_size,decode(nvl(mkps.Number_of_cards,0),0,1,Number_of_cards) Number_of_cards,
mkps.avg_dependent_demand,nvl(sum(nvl(moqd.primary_transaction_quantity,0)),0),nvl(ceil(mkps.replenishment_lead_time),0)
bulk collect into v_plan_pull_seq_tbl
FROM mtl_onhand_quantities_detail moqd,
mtl_kanban_pull_sequences mkps
WHERE moqd.inventory_item_id(+) = mkps.inventory_item_id
AND moqd.organization_id(+) = mkps.organization_id
AND moqd.subinventory_code(+) = mkps.subinventory_name
and nvl(moqd.locator_id(+),-100) = nvl(mkps.locator_id,-100)
and mkps.UPDATED_FLAG = l_value and mkps.kanban_plan_id =l_kanban_plan_id
group by mkps.pull_sequence_id,mkps.organization_id,mkps.kanban_plan_id,mkps.inventory_item_id,mkps.Subinventory_name,mkps.locator_id,
mkps.Safety_Stock_Days,decode(nvl(mkps.Kanban_size,0),0,1,mkps.Kanban_size),decode(nvl(mkps.Number_of_cards,0),0,1,Number_of_cards),mkps.avg_dependent_demand,
nvl(ceil(mkps.replenishment_lead_time),0);
select mrp_calendar.NEXT_WORK_DAY(organization_id,1,nvl(greatest(trunc(plan_start_date),trunc(sysdate)),trunc(sysdate))),
mrp_calendar.NEXT_WORK_DAY(organization_id,1,trunc(plan_cutoff_date))
into l_start_date,l_end_date
from mrp_kanban_plans where kanban_plan_id = l_kanban_plan_id;
v_exp_date.delete;
v_exp_repl_qty.delete;
v_repl_no_of_cards.delete;
v_exp_pull_seq_id.delete;
v_repl_date.delete;
select pull_seq,
expected_date,
replenished_date,
sum(qty) expected_qty,
count(distinct card) replenished_card
bulk collect into v_exp_pull_seq_id,
v_exp_date,
v_repl_date,
v_exp_repl_qty,
v_repl_no_of_cards
from (select plan.pull_sequence_id pull_seq,
mkc.kanban_card_id card,
mkc.kanban_size qty,
min(mrp_calendar.NEXT_WORK_DAY(mkps.organization_id,1,
case when trunc(mkca.creation_date+mkps.replenishment_lead_time) < l_start_date then l_start_date
else trunc(mkca.creation_date+mkps.replenishment_lead_time) end)) expected_date,
min(mrp_calendar.NEXT_WORK_DAY(mkps.organization_id,1,trunc(mkca.creation_date))) replenished_date
from mtl_kanban_card_activity mkca,
mtl_kanban_cards mkc,
mtl_kanban_pull_sequences mkps,
mtl_kanban_pull_sequences plan
where mkps.organization_id = plan.organization_id
and mkps.inventory_item_id = plan.inventory_item_id
and mkps.subinventory_name = plan.subinventory_name
and nvl(mkps.locator_id,-100) = nvl(plan.locator_id,-100)
and mkps.kanban_plan_id = -1
and mkps.pull_sequence_id = mkc.pull_sequence_id
and trunc(mkc.supply_status) in (4,5,6,7)
and mkc.current_replnsh_cycle_id = mkca.replenishment_cycle_id
and mkc.KANBAN_CARD_ID = mkca.kanban_card_id
and plan.UPDATED_FLAG = l_value
and plan.kanban_plan_id = l_kanban_plan_id
group by plan.pull_sequence_id,mkc.kanban_card_id,mkc.kanban_size)
where expected_date between l_start_date and l_end_date
or replenished_date between l_start_date and l_end_date
group by pull_seq,expected_date,replenished_date;
v_expected_receipts.delete;
v_cards_replenished.delete;
v_dem_pull_seq_id.delete;
v_demand_date.delete;
v_demand_qty.delete;
select plan.pull_sequence_id,
mrp_calendar.NEXT_WORK_DAY(plan.organization_id,1,trunc(demand_date)),
sum(nvl(demand_quantity,0))
bulk collect into v_dem_pull_seq_id,
v_demand_date,
v_demand_qty
from mrp_kanban_demand mkd,
mtl_kanban_pull_sequences plan
where mkd.kanban_plan_id = l_kanban_plan_id
and mkd.organization_id = plan.organization_id
and mkd.inventory_item_id = plan.inventory_item_id
and mkd.subinventory = plan.subinventory_name
and nvl(mkd.locator_id,-100) = nvl(plan.locator_id,-100)
and plan.UPDATED_FLAG = l_value and plan.kanban_plan_id = mkd.kanban_plan_id
and mrp_calendar.NEXT_WORK_DAY(plan.organization_id,1,trunc(demand_date)) between l_start_date and l_end_date
group by plan.pull_sequence_id,mrp_calendar.NEXT_WORK_DAY(plan.organization_id,1,trunc(demand_date));
v_demand_quantity.delete;
v_flm_kanban_planning_temp.delete;
select FLM_KANBAN_PLANNING_TEMP_S.nextval into v_flm_kanban_planning_temp(l_counter).Planning_temp_id from dual;
v_flm_kanban_planning_temp(l_counter).LAST_UPDATE_DATE := sysdate;
v_flm_kanban_planning_temp(l_counter).LAST_UPDATED_BY := g_user_id;
v_flm_kanban_planning_temp(l_counter).LAST_UPDATED_LOGIN := g_user_login_id;
delete from FLM_EKB_HORIZONTAL_PLAN_TEMP where kanban_plan_id = l_kanban_plan_id;
INSERT into FLM_EKB_HORIZONTAL_PLAN_TEMP values v_flm_kanban_planning_temp(cntr);
procedure update_production(p_plan_pull_seq_ids IN VARCHAR2,
p_effective_date IN DATE,
x_ret_status OUT NOCOPY Number)
IS
l_sql varchar2(4000);
l_sql := 'select plan.pull_sequence_id,plan.Number_of_cards,plan.Kanban_size, '||
'plan.Calculate_Kanban_flag,null,0,null,null, '||
'plan.LAST_IMPLEMENTED_PLAN,plan.plan_start_date,plan.plan_end_date,prod.pull_sequence_id, '||
'plan.avg_dependent_demand,plan.minimum_order_quantity,plan.allocation_percent,plan.replenishment_lead_time,plan.fixed_lot_multiplier,plan.safety_stock_days, '||
'plan.attribute1,plan.attribute2,plan.attribute3,plan.attribute4,plan.attribute5,plan.attribute6,plan.attribute7,plan.attribute8, '||
'plan.attribute9,plan.attribute10,plan.attribute11,plan.attribute12,plan.attribute13,plan.attribute14,plan.attribute15,prod.release_kanban_flag '||
'from mtl_kanban_pull_sequences plan,mtl_kanban_pull_sequences prod '||
'where plan.pull_sequence_id in ( '||p_plan_pull_seq_ids||' ) '||
'and plan.organization_id = prod.organization_id '||
'and plan.inventory_item_id = prod.inventory_item_id '||
'and plan.subinventory_name = prod.subinventory_name '||
'and nvl(plan.locator_id,-5) = nvl(prod.locator_id,-5) '||
'and prod.kanban_plan_id = -1 '||
'and plan.kanban_plan_id <> -1 ';
update mtl_kanban_pull_sequences
set future_no_of_cards = Number_of_cards,
future_card_size = Kanban_size,
planning_effectivity = null,
no_of_temp_cards = null,
no_of_temp_cycles = null,
temp_expiry_date = null
where pull_sequence_id = l_plan_pull_seq_id;
update mtl_kanban_pull_sequences
set number_of_cards = decode(Calculate_Kanban_flag,2,nvl(l_no_of_cards,number_of_cards),number_of_cards),
future_no_of_cards = decode(l_eff_date,null,null,nvl(l_future_cards,number_of_cards)),
kanban_size = decode(Calculate_Kanban_flag,1,nvl(l_card_size,kanban_size),kanban_size),
future_card_size = decode(l_eff_date,null,null,nvl(l_future_size,kanban_size)),
planning_effectivity = l_eff_date,
last_implemented_plan = l_imp_plan,
plan_start_date = l_plan_st_dt,
plan_end_date = l_plan_end_dt,
avg_dependent_demand = l_avg_demand,
minimum_order_quantity = l_moq,
allocation_percent = l_alloc_perc,
replenishment_lead_time = l_lead_time,
fixed_lot_multiplier = l_lot_multiplier,
safety_stock_days = l_safety_stock,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
attribute1 =l_attr_1,
attribute2 =l_attr_2,
attribute3 =l_attr_3,
attribute4 =l_attr_4,
attribute5 =l_attr_5,
attribute6 =l_attr_6,
attribute7 =l_attr_7,
attribute8 =l_attr_8,
attribute9 =l_attr_9,
attribute10=l_attr_10,
attribute11=l_attr_11,
attribute12=l_attr_12,
attribute13=l_attr_13,
attribute14=l_attr_14,
attribute15=l_attr_15
where pull_sequence_id = l_prod_pull_seq_id
returning source_type into l_source_type;
select count(1)
into l_curr_card_count
from mtl_kanban_cards
where pull_sequence_id = l_prod_pull_seq_id
and max_replenishments is null
and disable_date is null
and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
and card_status in (INV_Kanban_PVT.G_Card_Status_Active,INV_Kanban_PVT.G_Card_Status_Hold,INV_Kanban_PVT.G_Card_Status_Planned);
SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID,SUBINVENTORY_NAME,SOURCE_TYPE,LOCATOR_ID,SOURCE_ORGANIZATION_ID,SOURCE_SUBINVENTORY,SOURCE_LOCATOR_ID,WIP_LINE_ID
INTO l_item_id,l_org_id,l_subinv,l_src_type,l_locator,l_src_org_id,l_src_subinv,l_src_locator,l_wip_line_id
FROM MTL_KANBAN_PULL_SEQUENCES
WHERE PULL_SEQUENCE_ID = l_prod_pull_seq_id;
r_kanban_card_rec.last_update_date := sysdate;
r_kanban_card_rec.last_update_by := fnd_global.user_id;
r_kanban_card_rec.last_update_login := fnd_global.login_id;
r_kanban_card_rec.program_update_date := null;
select count(1)
into l_curr_card_count
from mtl_kanban_cards
where pull_sequence_id = l_prod_pull_seq_id
and max_replenishments is null
and disable_date is null
and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
and card_status in (INV_Kanban_PVT.G_Card_Status_Active,INV_Kanban_PVT.G_Card_Status_Hold);
select
kanban_card_id
, kanban_card_number
, pull_sequence_id
, inventory_item_id
, organization_id
, subinventory_name
, supply_status
, INV_Kanban_PVT.G_Card_Status_Active
, INV_Kanban_Pvt.g_card_type_replenishable
, source_type
, kanban_size
, creation_date
, created_by
, last_update_login
, last_print_date
, locator_id
, supplier_id
, supplier_site_id
, source_organization_id
, source_subinventory
, source_locator_id
, wip_line_id
, current_replnsh_cycle_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, replenishment_count
, max_replenishments
, disable_date
, replacement_flag
INTO l_kanban_card_rec.kanban_card_id
, l_kanban_card_rec.kanban_card_number
, l_kanban_card_rec.pull_sequence_id
, l_kanban_card_rec.inventory_item_id
, l_kanban_card_rec.organization_id
, l_kanban_card_rec.subinventory_name
, l_kanban_card_rec.supply_status
, l_kanban_card_rec.card_status
, l_kanban_card_rec.kanban_card_type
, l_kanban_card_rec.source_type
, l_kanban_card_rec.kanban_size
, l_kanban_card_rec.creation_date
, l_kanban_card_rec.created_by
, l_kanban_card_rec.last_update_login
, l_kanban_card_rec.last_print_date
, l_kanban_card_rec.locator_id
, l_kanban_card_rec.supplier_id
, l_kanban_card_rec.supplier_site_id
, l_kanban_card_rec.source_organization_id
, l_kanban_card_rec.source_subinventory
, l_kanban_card_rec.source_locator_id
, l_kanban_card_rec.wip_line_id
, l_kanban_card_rec.current_replnsh_cycle_id
, l_kanban_card_rec.attribute_category
, l_kanban_card_rec.attribute1
, l_kanban_card_rec.attribute2
, l_kanban_card_rec.attribute3
, l_kanban_card_rec.attribute4
, l_kanban_card_rec.attribute5
, l_kanban_card_rec.attribute6
, l_kanban_card_rec.attribute7
, l_kanban_card_rec.attribute8
, l_kanban_card_rec.attribute9
, l_kanban_card_rec.attribute10
, l_kanban_card_rec.attribute11
, l_kanban_card_rec.attribute12
, l_kanban_card_rec.attribute13
, l_kanban_card_rec.attribute14
, l_kanban_card_rec.attribute15
, l_kanban_card_rec.replenishment_count
, l_kanban_card_rec.max_replenishments
, l_kanban_card_rec.disable_date
, l_kanban_card_rec.replacement_flag
from mtl_kanban_cards
where pull_sequence_id = l_prod_pull_seq_id
and card_status = INV_Kanban_PVT.G_Card_Status_Planned
and max_replenishments is null
and disable_date is null
and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
and rownum = 1;
inv_kanbancard_pkg.update_row(
x_return_status => l_ret_status
, p_kanban_card_id => l_kanban_card_rec.kanban_card_id
, p_kanban_card_number => l_kanban_card_rec.kanban_card_number
, p_pull_sequence_id => l_kanban_card_rec.pull_sequence_id
, p_inventory_item_id => l_kanban_card_rec.inventory_item_id
, p_organization_id => l_kanban_card_rec.organization_id
, p_subinventory_name => l_kanban_card_rec.subinventory_name
, p_supply_status => l_kanban_card_rec.supply_status
, p_card_status => l_kanban_card_rec.card_status
, p_kanban_card_type => l_kanban_card_rec.kanban_card_type
, p_source_type => l_kanban_card_rec.source_type
, p_kanban_size => l_kanban_card_rec.kanban_size
, p_last_update_date => sysdate
, p_last_updated_by => fnd_global.user_id
, p_creation_date => l_kanban_card_rec.creation_date
, p_created_by => l_kanban_card_rec.created_by
, p_last_update_login => fnd_global.login_id
, p_last_print_date => l_kanban_card_rec.last_print_date
, p_locator_id => l_kanban_card_rec.locator_id
, p_supplier_id => l_kanban_card_rec.supplier_id
, p_supplier_site_id => l_kanban_card_rec.supplier_site_id
, p_source_organization_id => l_kanban_card_rec.source_organization_id
, p_source_subinventory => l_kanban_card_rec.source_subinventory
, p_source_locator_id => l_kanban_card_rec.source_locator_id
, p_wip_line_id => l_kanban_card_rec.wip_line_id
, p_current_replnsh_cycle_id => l_kanban_card_rec.current_replnsh_cycle_id
, p_document_type => null
, p_document_header_id => null
, p_document_detail_id => null
, p_error_code => null
, p_attribute_category => l_kanban_card_rec.attribute_category
, p_attribute1 => l_kanban_card_rec.attribute1
, p_attribute2 => l_kanban_card_rec.attribute2
, p_attribute3 => l_kanban_card_rec.attribute3
, p_attribute4 => l_kanban_card_rec.attribute4
, p_attribute5 => l_kanban_card_rec.attribute5
, p_attribute6 => l_kanban_card_rec.attribute6
, p_attribute7 => l_kanban_card_rec.attribute7
, p_attribute8 => l_kanban_card_rec.attribute8
, p_attribute9 => l_kanban_card_rec.attribute9
, p_attribute10 => l_kanban_card_rec.attribute10
, p_attribute11 => l_kanban_card_rec.attribute11
, p_attribute12 => l_kanban_card_rec.attribute12
, p_attribute13 => l_kanban_card_rec.attribute13
, p_attribute14 => l_kanban_card_rec.attribute14
, p_attribute15 => l_kanban_card_rec.attribute15
, p_replenishment_count => l_kanban_card_rec.replenishment_count
, p_max_replenishments => l_kanban_card_rec.max_replenishments
, p_disable_date => l_kanban_card_rec.disable_date
, p_replacement_flag => l_kanban_card_rec.replacement_flag);
End update_production;
procedure update_production(p_plan_pull_seq_id IN NUMBER,
x_ret_status OUT NOCOPY Number)
IS
l_sql varchar2(4000);
select plan.pull_sequence_id,nvl(plan.FUTURE_NO_OF_CARDS,plan.Number_of_cards),nvl(plan.FUTURE_CARD_SIZE,plan.Kanban_size),
plan.Calculate_Kanban_flag,plan.PLANNING_EFFECTIVITY,nvl(plan.NO_OF_TEMP_CARDS,0),plan.NO_OF_TEMP_CYCLES,plan.TEMP_EXPIRY_DATE,
plan.LAST_IMPLEMENTED_PLAN,plan.plan_start_date,plan.plan_end_date,prod.pull_sequence_id,
plan.avg_dependent_demand,plan.minimum_order_quantity,plan.allocation_percent,plan.replenishment_lead_time,plan.fixed_lot_multiplier,plan.safety_stock_days,
plan.attribute1,plan.attribute2,plan.attribute3,plan.attribute4,plan.attribute5,plan.attribute6,plan.attribute7,plan.attribute8,
plan.attribute9,plan.attribute10,plan.attribute11,plan.attribute12,plan.attribute13,plan.attribute14,plan.attribute15,prod.release_kanban_flag
into l_plan_pull_seq_id,l_no_of_cards,l_card_size,l_calc_flag,l_eff_date,l_no_of_temp_cards,l_no_of_cycles,
l_temp_exp_date,l_imp_plan,l_plan_st_dt,l_plan_end_dt,l_prod_pull_seq_id, l_avg_demand, l_moq, l_alloc_perc,
l_lead_time,l_lot_multiplier,l_safety_stock, l_attr_1, l_attr_2, l_attr_3, l_attr_4, l_attr_5, l_attr_6, l_attr_7,
l_attr_8, l_attr_9, l_attr_10, l_attr_11, l_attr_12, l_attr_13, l_attr_14, l_attr_15,l_release_kanban_flag
from mtl_kanban_pull_sequences plan,mtl_kanban_pull_sequences prod
where plan.pull_sequence_id = p_plan_pull_seq_id
and plan.organization_id = prod.organization_id
and plan.inventory_item_id = prod.inventory_item_id
and plan.subinventory_name = prod.subinventory_name
and nvl(plan.locator_id,-5) = nvl(prod.locator_id,-5)
and prod.kanban_plan_id = -1
and plan.kanban_plan_id <> -1;
update mtl_kanban_pull_sequences
set future_no_of_cards = Number_of_cards,
future_card_size = Kanban_size,
planning_effectivity = null,
no_of_temp_cards = null,
no_of_temp_cycles = null,
temp_expiry_date = null
where pull_sequence_id = l_plan_pull_seq_id;
update mtl_kanban_pull_sequences
set number_of_cards = decode(Calculate_Kanban_flag,2,nvl(l_no_of_cards,number_of_cards),number_of_cards),
future_no_of_cards = decode(l_eff_date,null,null,nvl(l_future_cards,number_of_cards)),
kanban_size = decode(Calculate_Kanban_flag,1,nvl(l_card_size,kanban_size),kanban_size),
future_card_size = decode(l_eff_date,null,null,nvl(l_future_size,kanban_size)),
planning_effectivity = l_eff_date,
last_implemented_plan = l_imp_plan,
plan_start_date = l_plan_st_dt,
plan_end_date = l_plan_end_dt,
avg_dependent_demand = l_avg_demand,
minimum_order_quantity = l_moq,
allocation_percent = l_alloc_perc,
replenishment_lead_time = l_lead_time,
fixed_lot_multiplier = l_lot_multiplier,
safety_stock_days = l_safety_stock,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
attribute1 =l_attr_1,
attribute2 =l_attr_2,
attribute3 =l_attr_3,
attribute4 =l_attr_4,
attribute5 =l_attr_5,
attribute6 =l_attr_6,
attribute7 =l_attr_7,
attribute8 =l_attr_8,
attribute9 =l_attr_9,
attribute10=l_attr_10,
attribute11=l_attr_11,
attribute12=l_attr_12,
attribute13=l_attr_13,
attribute14=l_attr_14,
attribute15=l_attr_15
where pull_sequence_id = l_prod_pull_seq_id
returning source_type into l_source_type;
select count(1)
into l_curr_card_count
from mtl_kanban_cards
where pull_sequence_id = l_prod_pull_seq_id
and max_replenishments is null
and disable_date is null
and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
and card_status in (INV_Kanban_PVT.G_Card_Status_Active,INV_Kanban_PVT.G_Card_Status_Hold,INV_Kanban_PVT.G_Card_Status_Planned);
SELECT INVENTORY_ITEM_ID,ORGANIZATION_ID,SUBINVENTORY_NAME,SOURCE_TYPE,LOCATOR_ID,SOURCE_ORGANIZATION_ID,SOURCE_SUBINVENTORY,SOURCE_LOCATOR_ID,WIP_LINE_ID
INTO l_item_id,l_org_id,l_subinv,l_src_type,l_locator,l_src_org_id,l_src_subinv,l_src_locator,l_wip_line_id
FROM MTL_KANBAN_PULL_SEQUENCES
WHERE PULL_SEQUENCE_ID = l_prod_pull_seq_id;
r_kanban_card_rec.last_update_date := sysdate;
r_kanban_card_rec.last_update_by := fnd_global.user_id;
r_kanban_card_rec.last_update_login := fnd_global.login_id;
r_kanban_card_rec.program_update_date := null;
select count(1)
into l_curr_card_count
from mtl_kanban_cards
where pull_sequence_id = l_prod_pull_seq_id
and max_replenishments is null
and disable_date is null
and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
and card_status in (INV_Kanban_PVT.G_Card_Status_Active,INV_Kanban_PVT.G_Card_Status_Hold);
select
kanban_card_id
, kanban_card_number
, pull_sequence_id
, inventory_item_id
, organization_id
, subinventory_name
, supply_status
, INV_Kanban_PVT.G_Card_Status_Active
, INV_Kanban_Pvt.g_card_type_replenishable
, source_type
, kanban_size
, creation_date
, created_by
, last_update_login
, last_print_date
, locator_id
, supplier_id
, supplier_site_id
, source_organization_id
, source_subinventory
, source_locator_id
, wip_line_id
, current_replnsh_cycle_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, replenishment_count
, max_replenishments
, disable_date
, replacement_flag
INTO l_kanban_card_rec.kanban_card_id
, l_kanban_card_rec.kanban_card_number
, l_kanban_card_rec.pull_sequence_id
, l_kanban_card_rec.inventory_item_id
, l_kanban_card_rec.organization_id
, l_kanban_card_rec.subinventory_name
, l_kanban_card_rec.supply_status
, l_kanban_card_rec.card_status
, l_kanban_card_rec.kanban_card_type
, l_kanban_card_rec.source_type
, l_kanban_card_rec.kanban_size
, l_kanban_card_rec.creation_date
, l_kanban_card_rec.created_by
, l_kanban_card_rec.last_update_login
, l_kanban_card_rec.last_print_date
, l_kanban_card_rec.locator_id
, l_kanban_card_rec.supplier_id
, l_kanban_card_rec.supplier_site_id
, l_kanban_card_rec.source_organization_id
, l_kanban_card_rec.source_subinventory
, l_kanban_card_rec.source_locator_id
, l_kanban_card_rec.wip_line_id
, l_kanban_card_rec.current_replnsh_cycle_id
, l_kanban_card_rec.attribute_category
, l_kanban_card_rec.attribute1
, l_kanban_card_rec.attribute2
, l_kanban_card_rec.attribute3
, l_kanban_card_rec.attribute4
, l_kanban_card_rec.attribute5
, l_kanban_card_rec.attribute6
, l_kanban_card_rec.attribute7
, l_kanban_card_rec.attribute8
, l_kanban_card_rec.attribute9
, l_kanban_card_rec.attribute10
, l_kanban_card_rec.attribute11
, l_kanban_card_rec.attribute12
, l_kanban_card_rec.attribute13
, l_kanban_card_rec.attribute14
, l_kanban_card_rec.attribute15
, l_kanban_card_rec.replenishment_count
, l_kanban_card_rec.max_replenishments
, l_kanban_card_rec.disable_date
, l_kanban_card_rec.replacement_flag
from mtl_kanban_cards
where pull_sequence_id = l_prod_pull_seq_id
and card_status = INV_Kanban_PVT.G_Card_Status_Planned
and max_replenishments is null
and disable_date is null
and kanban_card_type = INV_Kanban_Pvt.g_card_type_replenishable
and rownum = 1;
inv_kanbancard_pkg.update_row(
x_return_status => l_ret_status
, p_kanban_card_id => l_kanban_card_rec.kanban_card_id
, p_kanban_card_number => l_kanban_card_rec.kanban_card_number
, p_pull_sequence_id => l_kanban_card_rec.pull_sequence_id
, p_inventory_item_id => l_kanban_card_rec.inventory_item_id
, p_organization_id => l_kanban_card_rec.organization_id
, p_subinventory_name => l_kanban_card_rec.subinventory_name
, p_supply_status => l_kanban_card_rec.supply_status
, p_card_status => l_kanban_card_rec.card_status
, p_kanban_card_type => l_kanban_card_rec.kanban_card_type
, p_source_type => l_kanban_card_rec.source_type
, p_kanban_size => l_kanban_card_rec.kanban_size
, p_last_update_date => sysdate
, p_last_updated_by => fnd_global.user_id
, p_creation_date => l_kanban_card_rec.creation_date
, p_created_by => l_kanban_card_rec.created_by
, p_last_update_login => fnd_global.login_id
, p_last_print_date => l_kanban_card_rec.last_print_date
, p_locator_id => l_kanban_card_rec.locator_id
, p_supplier_id => l_kanban_card_rec.supplier_id
, p_supplier_site_id => l_kanban_card_rec.supplier_site_id
, p_source_organization_id => l_kanban_card_rec.source_organization_id
, p_source_subinventory => l_kanban_card_rec.source_subinventory
, p_source_locator_id => l_kanban_card_rec.source_locator_id
, p_wip_line_id => l_kanban_card_rec.wip_line_id
, p_current_replnsh_cycle_id => l_kanban_card_rec.current_replnsh_cycle_id
, p_document_type => null
, p_document_header_id => null
, p_document_detail_id => null
, p_error_code => null
, p_attribute_category => l_kanban_card_rec.attribute_category
, p_attribute1 => l_kanban_card_rec.attribute1
, p_attribute2 => l_kanban_card_rec.attribute2
, p_attribute3 => l_kanban_card_rec.attribute3
, p_attribute4 => l_kanban_card_rec.attribute4
, p_attribute5 => l_kanban_card_rec.attribute5
, p_attribute6 => l_kanban_card_rec.attribute6
, p_attribute7 => l_kanban_card_rec.attribute7
, p_attribute8 => l_kanban_card_rec.attribute8
, p_attribute9 => l_kanban_card_rec.attribute9
, p_attribute10 => l_kanban_card_rec.attribute10
, p_attribute11 => l_kanban_card_rec.attribute11
, p_attribute12 => l_kanban_card_rec.attribute12
, p_attribute13 => l_kanban_card_rec.attribute13
, p_attribute14 => l_kanban_card_rec.attribute14
, p_attribute15 => l_kanban_card_rec.attribute15
, p_replenishment_count => l_kanban_card_rec.replenishment_count
, p_max_replenishments => l_kanban_card_rec.max_replenishments
, p_disable_date => l_kanban_card_rec.disable_date
, p_replacement_flag => l_kanban_card_rec.replacement_flag);
End update_production;
SELECT category_set_id
INTO l_category_set_id
FROM mtl_default_sets_view
WHERE functional_area_id=3
AND category_set_name = Nvl(p_category_set, category_set_name);
UPDATE mrp_kanban_plans SET request_id = l_request_id WHERE kanban_plan_id = p_kanban_plan_id;