[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT kanban_plan_id,
input_type,
input_designator,
bom_effectivity_date,
plan_start_date,
plan_cutoff_date
INTO l_plan_id,
g_kanban_info_rec.input_type,
g_kanban_info_rec.input_designator,
l_bom_effectivity,
l_start_date,
l_cutoff_date
FROM mrp_kanban_plans
WHERE kanban_plan_id = g_kanban_info_rec.kanban_plan_id;
DELETE FROM mrp_low_level_codes
WHERE plan_id = g_kanban_info_rec.kanban_plan_id
AND organization_id = g_kanban_info_rec.organization_id
AND (levels_below <> 1 OR
assembly_item_id = component_item_id OR
assembly_item_id = -1 );
UPDATE mrp_low_level_codes
SET low_level_code = null
WHERE plan_id = g_kanban_info_rec.kanban_plan_id
AND organization_id = g_kanban_info_rec.organization_id;
DELETE FROM mrp_low_level_codes
WHERE plan_id = g_kanban_info_rec.kanban_plan_id
AND organization_id = g_kanban_info_rec.organization_id;
DELETE FROM mtl_kanban_pull_sequences
WHERE kanban_plan_id = g_kanban_info_rec.kanban_plan_id;
DELETE FROM mrp_kanban_demand
WHERE kanban_plan_id = g_kanban_info_rec.kanban_plan_id
AND organization_id = g_kanban_info_rec.organization_id;
UPDATE mrp_kanban_plans
SET plan_start_date = g_kanban_info_rec.start_date,
plan_completion_date = NULL,
bom_effectivity_date = g_kanban_info_rec.bom_effectivity,
plan_cutoff_date = g_kanban_info_rec.cutoff_date
WHERE kanban_plan_id = g_kanban_info_rec.kanban_plan_id;
UPDATE mrp_kanban_plans
SET plan_completion_date = sysdate
WHERE kanban_plan_id = g_kanban_info_rec.kanban_plan_id;
SELECT count(*) count
FROM bom_calendar_dates bcd,
mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND bcd.calendar_code = mp.calendar_code
AND bcd.exception_set_id = mp.calendar_exception_set_id
AND bcd.calendar_date between
p_schedule_date and p_rate_end_date
AND bcd.seq_num IS NOT NULL;
SELECT count(*)
INTO l_total_workdays
FROM bom_calendar_dates cd,
bom_cal_week_start_dates ws,
mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND ws.calendar_code = mp.calendar_code
AND ws.exception_set_id = mp.calendar_exception_set_id
AND ws.week_start_date = l_demand_date
AND cd.calendar_code = ws.calendar_code
AND cd.exception_set_id = ws.exception_set_id
AND (cd.calendar_date BETWEEN ws.week_start_date AND
ws.next_date)
AND cd.seq_num IS NOT NULL;
SELECT count(*)
INTO l_total_workdays
FROM bom_calendar_dates cd,
bom_period_start_dates ps,
mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND ps.calendar_code = mp.calendar_code
AND ps.exception_set_id = mp.calendar_exception_set_id
AND ps.period_start_date = l_demand_date
AND cd.calendar_code = ps.calendar_code
AND cd.exception_set_id = ps.exception_set_id
AND (cd.calendar_date BETWEEN ps.period_start_date AND
ps.next_date)
AND cd.seq_num IS NOT NULL;
SELECT bw.next_date
INTO l_next_date
FROM bom_cal_week_start_dates bw,
mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND bw.calendar_code = mp.calendar_code
AND bw.exception_set_id = mp.calendar_exception_set_id
AND bw.week_start_date <= l_demand_date
AND bw.next_date >= l_demand_date;
SELECT bp.next_date
INTO l_next_date
FROM bom_period_start_dates bp,
mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND bp.calendar_code = mp.calendar_code
AND bp.exception_set_id = mp.calendar_exception_set_id
AND bp.period_start_date <= l_demand_date
AND bp.next_date >= l_demand_date;
SELECT count(*)
INTO l_current_workdays
FROM bom_calendar_dates cd,
mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND cd.calendar_code = mp.calendar_code
AND cd.exception_set_id = mp.calendar_exception_set_id
AND (cd.calendar_date BETWEEN l_demand_date AND l_next_date)
AND cd.seq_num IS NOT NULL;
insert_or_cascade IN boolean)
RETURN BOOLEAN IS
l_rate_end_date date;
SELECT bp.period_start_date, bp.next_date
FROM bom_period_start_dates bp, mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND bp.calendar_code = mp.calendar_code
AND bp.exception_set_id = mp.calendar_exception_set_id
AND (bp.period_start_date BETWEEN p_demand_date AND
p_rate_end_date);
SELECT bw.week_start_date, bw.next_date
FROM bom_cal_week_start_dates bw, mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND bw.calendar_code = mp.calendar_code
AND bw.exception_set_id = mp.calendar_exception_set_id
AND (bw.week_start_date BETWEEN p_demand_date AND
p_rate_end_date);
SELECT bcd.calendar_date
FROM bom_calendar_dates bcd, mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND bcd.calendar_code = mp.calendar_code
AND bcd.exception_set_id = mp.calendar_exception_set_id
AND (bcd.calendar_date BETWEEN l_rate_start_date AND
l_rate_end_date)
AND bcd.seq_num is not null;
SELECT line_id
INTO l_line_id
FROM bom_operational_routings
WHERE alternate_routing_designator is NULL
AND assembly_item_id = p_inventory_item_id
AND organization_id = g_kanban_info_rec.organization_id;
g_log_message := 'Inserting into MRP_KANBAN_DEMAND';
IF INSERT_OR_CASCADE = TRUE THEN
INSERT INTO MRP_KANBAN_DEMAND (
DEMAND_ID,
KANBAN_PLAN_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY,
LOCATOR_ID,
ASSEMBLY_ITEM_ID,
ASSEMBLY_ORG_ID,
ASSEMBLY_SUBINVENTORY,
ASSEMBLY_LOCATOR_ID,
DEMAND_DATE,
DEMAND_QUANTITY,
ORDER_TYPE,
KANBAN_ITEM_FLAG,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY )
SELECT
mrp_kanban_demand_s.nextval,
g_kanban_info_rec.kanban_plan_id,
g_kanban_info_rec.organization_id,
p_inventory_item_id,
ps.subinventory_name,
ps.locator_id,
NULL,
NULL,
NULL,
NULL,
l_demand_date,
(NVL(ps.allocation_percent, 100) *
l_demand_quantity/ 100),
p_demand_type,
'Y',
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM
mtl_kanban_pull_sequences ps
WHERE ps.wip_line_id = l_line_id
AND ps.source_type = G_PRODUCTION_SOURCE_TYPE
AND ps.kanban_plan_id = decode (g_kanban_info_rec.replan_flag,
2, G_PRODUCTION_KANBAN,
1, g_kanban_info_rec.kanban_plan_id,
G_PRODUCTION_KANBAN)
AND ps.inventory_item_id = p_inventory_item_id
AND ps.organization_id = g_kanban_info_rec.organization_id;
INSERT INTO MRP_KANBAN_DEMAND (
DEMAND_ID,
KANBAN_PLAN_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY,
LOCATOR_ID,
ASSEMBLY_ORG_ID,
ASSEMBLY_ITEM_ID,
ASSEMBLY_SUBINVENTORY,
ASSEMBLY_LOCATOR_ID,
DEMAND_DATE,
DEMAND_QUANTITY,
ORDER_TYPE,
KANBAN_ITEM_FLAG,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY )
SELECT
mrp_kanban_demand_s.nextval,
g_kanban_info_rec.kanban_plan_id,
g_kanban_info_rec.organization_id,
p_inventory_item_id,
p_item_sub_inventory,
p_item_locator,
g_kanban_info_rec.organization_id,
p_parent_item_id,
p_parent_sub_inventory,
p_parent_locator,
l_demand_date,
l_demand_quantity,
p_demand_type,
p_kanban_item_flag,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM
DUAL;
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
l_planning_factor number;
/* End of Update */
l_kanban_item_flag varchar2(1);
SELECT current_forecast_quantity,
forecast_date,
rate_end_date,
bucket_type
FROM mrp_forecast_dates
WHERE organization_id = g_kanban_info_rec.organization_id
AND ((forecast_designator = g_kanban_info_rec.input_designator) or
(forecast_designator in ( -- forecast set
select forecast_designator
from mrp_forecast_designators
where forecast_set = g_kanban_info_rec.input_designator)
)
)
AND inventory_item_id = p_top_item_id
AND origination_type = p_demand_type
AND nvl(line_id,0) = nvl(p_parent_line_id,0)
AND ((rate_end_date IS NULL AND
forecast_date BETWEEN Get_Offset_Date(
g_kanban_info_rec.start_date,
bucket_type )
AND g_kanban_info_rec.cutoff_date) OR
(rate_end_date is NOT NULL AND NOT
(rate_end_date < Get_Offset_Date(
g_kanban_info_rec.start_date,
bucket_type ) OR
forecast_date > g_kanban_info_rec.cutoff_date)));
SELECT DISTINCT
bic.component_item_id,
decode(bic.supply_subinventory, NULL, msi.wip_supply_subinventory,
bic.supply_subinventory),
decode(bic.supply_locator_id, NULL, msi.wip_supply_locator_id,
bic.supply_locator_id),
bic.component_quantity,
bic.component_yield_factor,
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
bic.planning_factor,
bic.item_num,
/* End of Update */
mllc.operation_yield,
mllc.net_planning_percent,
mllc.kanban_item_flag,
/* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
mllc.wip_supply_type,
mllc.basis_type,
nvl(msi.fixed_order_quantity, nvl(msi.minimum_order_quantity, nvl(msi.maximum_order_quantity,1)))
FROM mtl_system_items msi,
mrp_low_level_codes mllc,
bom_inventory_components bic,
bom_bill_of_materials bbom,
bom_operational_routings bor,
mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND bor.line_id (+) = p_line_id
AND bor.assembly_item_id (+) = p_assembly_item_id
AND bor.organization_id (+) = mp.organization_id
AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority (
bor.assembly_item_id,
bor.organization_id,
bor.line_id,
bor.alternate_routing_designator)
/* BUG: 1668867 Double kanban demand */
AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_assy_cfgitem (
p_assembly_item_id,
bic.component_item_id,
mp.organization_id)
AND bbom.assembly_item_id = p_assembly_item_id
AND bbom.organization_id = mp.organization_id
AND nvl(bbom.alternate_bom_designator, 'xxx') =
nvl(bor.alternate_routing_designator, 'xxx')
AND bic.bill_sequence_id = bbom.common_bill_sequence_id
AND nvl(bic.disable_date, g_kanban_info_rec.bom_effectivity + 1)
>= g_kanban_info_rec.bom_effectivity
AND bic.effectivity_date <= g_kanban_info_rec.bom_effectivity
AND NOT EXISTS (
SELECT NULL
FROM bom_inventory_components bic2
WHERE bic2.bill_sequence_id = bic.bill_sequence_id
AND bic2.component_item_id = bic.component_item_id
AND (decode(bic2.implementation_date, null,
bic2.old_component_sequence_id,
bic2.component_sequence_id) =
decode(bic.implementation_date, null,
bic.old_component_sequence_id,
bic.component_sequence_id)
OR bic2.operation_seq_num = bic.operation_seq_num)
AND bic2.effectivity_date <=
g_kanban_info_rec.bom_effectivity
AND bic2.effectivity_date > bic.effectivity_date
AND (bic2.implementation_date is not null OR
(bic2.implementation_date is null AND EXISTS
(SELECT NULL
FROM eng_revised_items eri
WHERE bic2.revised_item_sequence_id =
eri.revised_item_sequence_id
AND eri.mrp_active = 1 ))))
AND (bic.implementation_date is not null OR
(bic.implementation_date is null AND EXISTS
(SELECT NULL
FROM eng_revised_items eri
WHERE bic.revised_item_sequence_id =
eri.revised_item_sequence_id
AND eri.mrp_active = 1 )))
AND mllc.plan_id = g_kanban_info_rec.kanban_plan_id
AND mllc.organization_id = bbom.organization_id
AND mllc.assembly_item_id = bbom.assembly_item_id
AND mllc.component_item_id = bic.component_item_id
AND nvl(mllc.alternate_designator, 'xxx') =
nvl(bbom.alternate_bom_designator, 'xxx')
AND msi.inventory_item_id = mllc.component_item_id
AND msi.organization_id = mllc.organization_id
AND ((nvl(msi.ato_forecast_control, G_NO_FCST_CONTROL) = G_NO_FCST_CONTROL)
OR p_explode_always = 'Y'
OR (p_sales_order_demand = 'Y' AND msi.bom_item_type = 4));
SELECT DISTINCT
mllc.component_item_id,
mllc.from_subinventory,
mllc.from_locator_id,
mllc.component_usage,
mllc.component_yield,
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
mllc.planning_factor,
mllc.item_num,
/* End of Update */
mllc.operation_yield,
mllc.net_planning_percent,
mllc.kanban_item_flag,
/* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
mllc.wip_supply_type,
mllc.basis_type,
nvl(msi.fixed_order_quantity, nvl(msi.minimum_order_quantity, nvl(msi.maximum_order_quantity,1)))
FROM mtl_system_items msi,
mrp_low_level_codes mllc
WHERE mllc.plan_id = g_kanban_info_rec.kanban_plan_id
AND mllc.organization_id + 0 = g_kanban_info_rec.organization_id
AND mllc.assembly_item_id = p_assembly_item_id
AND ((mllc.to_subinventory = p_subinventory
AND nvl(mllc.to_locator_id,-1) = nvl(p_locator_id,-1)) OR
(mllc.to_subinventory is NULL and p_bill_or_ps = 1) )
AND msi.inventory_item_id = mllc.component_item_id
AND msi.organization_id = mllc.organization_id
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority (
mllc.assembly_item_id,
mllc.organization_id,
null,
mllc.alternate_designator)
/* End of Update */
/* BUG 1668867, Double Kanban demand problem */
AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_assy_cfgitem (
p_assembly_item_id,
mllc.component_item_id,
mllc.organization_id)
AND ((nvl(msi.ato_forecast_control, G_NO_FCST_CONTROL) = G_NO_FCST_CONTROL)
OR p_explode_always = 'Y'
OR (p_sales_order_demand = 'Y' AND msi.bom_item_type = 4));
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
l_planning_factor,
l_item_num,
/* End of Update */
l_operation_yield,
l_net_planning_percent,
l_kanban_item_flag,
/* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
l_wip_supply_type,
l_basis_type,
l_comp_foq;
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
l_planning_factor,
l_item_num,
/* End of Update */
l_operation_yield,
l_net_planning_percent,
l_kanban_item_flag,
/* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
l_wip_supply_type,
l_basis_type,
l_comp_foq;
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
/* l_demand_quantity := ROUND((l_forecast_quantity* nvl(l_component_usage, 1) *
nvl(p_cumulative_usage,1)*
(nvl(l_net_planning_percent, 100) /100)) /
(nvl(l_operation_yield, 1) * nvl(l_component_yield, 1))); */
/* End of Update */
IF g_debug THEN
g_log_message := 'Deamnd Quantity:'||to_char(l_demand_quantity);
INSERT INTO MRP_KANBAN_DEMAND (
DEMAND_ID,
KANBAN_PLAN_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY,
LOCATOR_ID,
ASSEMBLY_ORG_ID,
ASSEMBLY_ITEM_ID,
ASSEMBLY_SUBINVENTORY,
ASSEMBLY_LOCATOR_ID,
DEMAND_DATE,
DEMAND_QUANTITY,
ORDER_TYPE,
KANBAN_ITEM_FLAG,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY )
SELECT
mrp_kanban_demand_s.nextval,
g_kanban_info_rec.kanban_plan_id,
g_kanban_info_rec.organization_id,
l_component_id,
l_subinventory,
l_locator_id,
g_kanban_info_rec.organization_id,
p_assembly_item_id,
p_subinventory,
p_locator_id,
l_forecast_date,
l_demand_quantity,
8,
l_kanban_item_flag,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM
DUAL;
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
/* l_demand_quantity := ROUND((l_forecast_quantity* nvl(l_component_usage, 1) *
nvl(p_cumulative_usage,1)*
(nvl(l_net_planning_percent, 100) /100)) /
(nvl(l_operation_yield, 1) * nvl(l_component_yield, 1))); */
/* End of Update */
IF g_debug THEN
g_log_message := 'Deamnd Quantity:'||to_char(l_demand_quantity);
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
-- l_cumulative_usage := p_cumulative_usage * l_component_usage;
/* End of Update */
IF ( l_running_total_quantity > 0) THEN
IF g_debug THEN
g_log_message := 'Calling Cascade_Forecast_Demand in recursive mode';
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
l_planning_factor number;
/* End of Update */
l_kanban_item_flag varchar2(1);
SELECT sum(planned_quantity) PQ,
scheduled_completion_date
FROM mrp_kanban_actual_prod_v
WHERE organization_id = g_kanban_info_rec.organization_id
AND scheduled_completion_date between g_kanban_info_rec.start_date AND
g_kanban_info_rec.cutoff_date
AND primary_item_id IN
( select COMPONENT_ITEM_ID from mrp_low_level_codes
WHERE ORGANIZATION_ID = g_kanban_info_rec.organization_id
AND PLAN_ID = g_kanban_info_rec.kanban_plan_id )
AND primary_item_id = p_top_item_id
AND nvl(alternate_bom_designator, 'NONE') = nvl(p_top_alt, 'NONE')
AND nvl(line_id,0)=nvl(p_parent_line_id,0)
group by scheduled_completion_date,schedule_type,line_id;
SELECT DISTINCT
bic.component_item_id,
decode(bic.supply_subinventory, NULL, msi.wip_supply_subinventory,
bic.supply_subinventory),
decode(bic.supply_subinventory, NULL, msi.wip_supply_locator_id,
bic.supply_locator_id),
bic.component_quantity,
bic.component_yield_factor,
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
bic.planning_factor,
bic.item_num,
/* End of Update */
mllc.operation_yield,
mllc.net_planning_percent,
mllc.kanban_item_flag,
/* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
mllc.wip_supply_type,
mllc.basis_type,
nvl(msi.fixed_order_quantity, nvl(msi.minimum_order_quantity, nvl(msi.maximum_order_quantity,1)))
FROM mtl_system_items msi,
mrp_low_level_codes mllc,
bom_inventory_components bic,
bom_bill_of_materials bbom
WHERE
bbom.assembly_item_id = p_assembly_item_id
AND bbom.organization_id = g_kanban_info_rec.organization_id
AND nvl(bbom.alternate_bom_designator, 'NONE') = nvl(p_top_alt, 'NONE')
/* Bug 2279877, not pick up discrete jobs w/o line_id
AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority (
bor.assembly_item_id,
bor.organization_id,
bor.line_id,
bor.alternate_routing_designator)
*/
/* BUG: 1668867 , Fix for double demand */
AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_assy_cfgitem (
p_assembly_item_id,
bic.component_item_id,
g_kanban_info_rec.organization_id)
AND bic.bill_sequence_id = bbom.common_bill_sequence_id
AND nvl(bic.disable_date, g_kanban_info_rec.bom_effectivity + 1)
>= g_kanban_info_rec.bom_effectivity
AND bic.effectivity_date <= g_kanban_info_rec.bom_effectivity
AND NOT EXISTS (
SELECT NULL
FROM bom_inventory_components bic2
WHERE bic2.bill_sequence_id = bic.bill_sequence_id
AND bic2.component_item_id = bic.component_item_id
AND (decode(bic2.implementation_date, null,
bic2.old_component_sequence_id,
bic2.component_sequence_id) =
decode(bic.implementation_date, null,
bic.old_component_sequence_id,
bic.component_sequence_id)
OR bic2.operation_seq_num = bic.operation_seq_num)
AND bic2.effectivity_date <=
g_kanban_info_rec.bom_effectivity
AND bic2.effectivity_date > bic.effectivity_date
AND (bic2.implementation_date is not null OR
(bic2.implementation_date is null AND EXISTS
(SELECT NULL
FROM eng_revised_items eri
WHERE bic2.revised_item_sequence_id =
eri.revised_item_sequence_id
AND eri.mrp_active = 1 ))))
AND (bic.implementation_date is not null OR
(bic.implementation_date is null AND EXISTS
(SELECT NULL
FROM eng_revised_items eri
WHERE bic.revised_item_sequence_id =
eri.revised_item_sequence_id
AND eri.mrp_active = 1 )))
AND mllc.plan_id = g_kanban_info_rec.kanban_plan_id
AND mllc.organization_id = bbom.organization_id
AND mllc.assembly_item_id = bbom.assembly_item_id
AND mllc.component_item_id = bic.component_item_id
AND nvl(mllc.alternate_designator, 'xxx') =
nvl(bbom.alternate_bom_designator, 'xxx')
AND msi.inventory_item_id = mllc.component_item_id
AND msi.organization_id = mllc.organization_id
AND ((nvl(msi.ato_forecast_control, G_NO_FCST_CONTROL) = G_NO_FCST_CONTROL)
OR p_explode_always = 'Y'
OR (p_sales_order_demand = 'Y' AND msi.bom_item_type = 4));
SELECT DISTINCT
mllc.component_item_id,
mllc.from_subinventory,
mllc.from_locator_id,
mllc.component_usage,
mllc.component_yield,
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
mllc.planning_factor,
mllc.item_num,
/* End of Update */
mllc.operation_yield,
mllc.net_planning_percent,
mllc.kanban_item_flag,
/* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
mllc.wip_supply_type,
mllc.basis_type,
nvl(msi.fixed_order_quantity, nvl(msi.minimum_order_quantity, nvl(msi.maximum_order_quantity,1)))
FROM mtl_system_items msi,
mrp_low_level_codes mllc
WHERE mllc.plan_id = g_kanban_info_rec.kanban_plan_id
AND mllc.organization_id + 0 = g_kanban_info_rec.organization_id
AND mllc.assembly_item_id = p_assembly_item_id
AND ((mllc.to_subinventory = p_subinventory
AND nvl(mllc.to_locator_id,-1) = nvl(p_locator_id,-1)) OR
(mllc.to_subinventory is NULL and p_bill_or_ps = 1) )
AND msi.inventory_item_id = mllc.component_item_id
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
/* Bug 2279877, not pick up discrete jobs w/o line_id
AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority (
mllc.assembly_item_id,
mllc.organization_id,
null,
mllc.alternate_designator)
*/
/* End of Update */
/* Bug 1668867 : Double Kanban demand */
AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_assy_cfgitem (
p_assembly_item_id,
mllc.component_item_id,
mllc.organization_id)
AND msi.organization_id = mllc.organization_id
AND ((nvl(msi.ato_forecast_control, G_NO_FCST_CONTROL) = G_NO_FCST_CONTROL)
OR p_explode_always = 'Y'
OR (p_sales_order_demand = 'Y' AND msi.bom_item_type = 4));
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
l_planning_factor,
l_item_num,
/* End of Update */
l_operation_yield,
l_net_planning_percent,
l_kanban_item_flag,
/* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
l_wip_supply_type,
l_basis_type,
l_comp_foq;
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
l_planning_factor,
l_item_num,
/* End of Update */
l_operation_yield,
l_net_planning_percent,
l_kanban_item_flag,
/* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
l_wip_supply_type,
l_basis_type,
l_comp_foq;
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
/* l_demand_quantity := ROUND((l_schedule_quantity* nvl(l_component_usage, 1) *
nvl(p_cumulative_usage,1)*
(nvl(l_net_planning_percent, 100) /100)) /
(nvl(l_operation_yield, 1) * nvl(l_component_yield, 1))); */
/* End of Update */
IF g_debug THEN
g_log_message := 'Deamnd Quantity:'||to_char(l_demand_quantity);
INSERT INTO MRP_KANBAN_DEMAND (
DEMAND_ID,
KANBAN_PLAN_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY,
LOCATOR_ID,
ASSEMBLY_ORG_ID,
ASSEMBLY_ITEM_ID,
ASSEMBLY_SUBINVENTORY,
ASSEMBLY_LOCATOR_ID,
DEMAND_DATE,
DEMAND_QUANTITY,
ORDER_TYPE,
KANBAN_ITEM_FLAG,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY )
SELECT
mrp_kanban_demand_s.nextval,
g_kanban_info_rec.kanban_plan_id,
g_kanban_info_rec.organization_id,
l_component_id,
l_subinventory,
l_locator_id,
g_kanban_info_rec.organization_id,
p_assembly_item_id,
p_subinventory,
p_locator_id,
l_schedule_date,
l_demand_quantity,
8,
l_kanban_item_flag,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM
DUAL;
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
--l_cumulative_usage := p_cumulative_usage * l_component_usage;
/* End of Update */
IF ( l_running_total_quantity > 0) THEN
IF g_debug THEN
g_log_message := 'Calling Cascade_Ap_Demand in recursive mode';
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
l_planning_factor number;
/* End of Update */
l_kanban_item_flag varchar2(1);
SELECT
decode(schedule_quantity,NULL,MRP_KANBAN_PLAN_PK.Get_Repetitive_Demand(
schedule_date,rate_end_date,repetitive_daily_rate),schedule_quantity),
schedule_date
FROM mrp_schedule_dates
WHERE organization_id = g_kanban_info_rec.organization_id
AND schedule_designator = g_kanban_info_rec.input_designator
AND schedule_level = 2
AND schedule_date BETWEEN g_kanban_info_rec.start_date AND
g_kanban_info_rec.cutoff_date
AND inventory_item_id = p_top_item_id
AND nvl(line_id,0)=nvl(p_parent_line_id,0)
AND schedule_origination_type = p_demand_type ;
SELECT DISTINCT
bic.component_item_id,
decode(bic.supply_subinventory, NULL, msi.wip_supply_subinventory,
bic.supply_subinventory),
decode(bic.supply_locator_id, NULL, msi.wip_supply_locator_id,
bic.supply_locator_id),
bic.component_quantity,
bic.component_yield_factor,
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
bic.planning_factor,
bic.item_num,
/* End of Update */
mllc.operation_yield,
mllc.net_planning_percent,
mllc.kanban_item_flag,
/* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
mllc.wip_supply_type,
mllc.basis_type,
nvl(msi.fixed_order_quantity, nvl(msi.minimum_order_quantity, nvl(msi.maximum_order_quantity,1)))
FROM mtl_system_items msi,
mrp_low_level_codes mllc,
bom_inventory_components bic,
bom_bill_of_materials bbom,
bom_operational_routings bor,
mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND bor.line_id (+) = p_line_id
AND bor.assembly_item_id (+) = p_assembly_item_id
AND bor.organization_id (+) = mp.organization_id
AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority (
bor.assembly_item_id,
bor.organization_id,
bor.line_id,
bor.alternate_routing_designator)
AND bbom.assembly_item_id = p_assembly_item_id
AND bbom.organization_id = mp.organization_id
AND nvl(bbom.alternate_bom_designator, 'xxx') =
nvl(bor.alternate_routing_designator, 'xxx')
AND bic.bill_sequence_id = bbom.common_bill_sequence_id
AND nvl(bic.disable_date, g_kanban_info_rec.bom_effectivity + 1)
>= g_kanban_info_rec.bom_effectivity
AND bic.effectivity_date <= g_kanban_info_rec.bom_effectivity
/* BUG: 1821216 Double kanban demand */
AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_assy_cfgitem (
p_assembly_item_id,
bic.component_item_id,
mp.organization_id)
AND NOT EXISTS (
SELECT NULL
FROM bom_inventory_components bic2
WHERE bic2.bill_sequence_id = bic.bill_sequence_id
AND bic2.component_item_id = bic.component_item_id
AND (decode(bic2.implementation_date, null,
bic2.old_component_sequence_id,
bic2.component_sequence_id) =
decode(bic.implementation_date, null,
bic.old_component_sequence_id,
bic.component_sequence_id)
OR bic2.operation_seq_num = bic.operation_seq_num)
AND bic2.effectivity_date <=
g_kanban_info_rec.bom_effectivity
AND bic2.effectivity_date > bic.effectivity_date
AND (bic2.implementation_date is not null OR
(bic2.implementation_date is null AND EXISTS
(SELECT NULL
FROM eng_revised_items eri
WHERE bic2.revised_item_sequence_id =
eri.revised_item_sequence_id
AND eri.mrp_active = 1 ))))
AND (bic.implementation_date is not null OR
(bic.implementation_date is null AND EXISTS
(SELECT NULL
FROM eng_revised_items eri
WHERE bic.revised_item_sequence_id =
eri.revised_item_sequence_id
AND eri.mrp_active = 1 )))
AND mllc.plan_id = g_kanban_info_rec.kanban_plan_id
AND mllc.organization_id = bbom.organization_id
AND mllc.assembly_item_id = bbom.assembly_item_id
AND mllc.component_item_id = bic.component_item_id
AND nvl(mllc.alternate_designator, 'xxx') =
nvl(bbom.alternate_bom_designator, 'xxx')
AND msi.inventory_item_id = mllc.component_item_id
AND msi.organization_id = mllc.organization_id
AND ((nvl(msi.ato_forecast_control, G_NO_FCST_CONTROL) = G_NO_FCST_CONTROL)
OR p_explode_always = 'Y'
OR (p_sales_order_demand = 'Y' AND msi.bom_item_type = 4));
SELECT DISTINCT
mllc.component_item_id,
mllc.from_subinventory,
mllc.from_locator_id,
mllc.component_usage,
mllc.component_yield,
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
mllc.planning_factor,
mllc.item_num,
/* End of Update */
mllc.operation_yield,
mllc.net_planning_percent,
mllc.kanban_item_flag,
/* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
mllc.wip_supply_type,
mllc.basis_type,
nvl(msi.fixed_order_quantity, nvl(msi.minimum_order_quantity, nvl(msi.maximum_order_quantity,1)))
FROM mtl_system_items msi,
mrp_low_level_codes mllc
WHERE mllc.plan_id = g_kanban_info_rec.kanban_plan_id
AND mllc.organization_id + 0 = g_kanban_info_rec.organization_id
AND mllc.assembly_item_id = p_assembly_item_id
AND ((mllc.to_subinventory = p_subinventory
AND nvl(mllc.to_locator_id,-1) = nvl(p_locator_id,-1)) OR
(mllc.to_subinventory is NULL and p_bill_or_ps = 1) )
/* Bug 1668867 : Double Kanban demand */
AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_assy_cfgitem (
p_assembly_item_id,
mllc.component_item_id,
mllc.organization_id)
AND msi.inventory_item_id = mllc.component_item_id
AND msi.organization_id = mllc.organization_id
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
AND 1 = MRP_KANBAN_SNAPSHOT_PK.Check_Min_Priority (
mllc.assembly_item_id,
mllc.organization_id,
null,
mllc.alternate_designator)
/* End of Update */
AND ((nvl(msi.ato_forecast_control, G_NO_FCST_CONTROL) = G_NO_FCST_CONTROL)
OR p_explode_always = 'Y'
OR (p_sales_order_demand = 'Y' AND msi.bom_item_type = 4));
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
l_planning_factor,
l_item_num,
/* End of Update */
l_operation_yield,
l_net_planning_percent,
l_kanban_item_flag,
/* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
l_wip_supply_type,
l_basis_type,
l_comp_foq;
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
l_planning_factor,
l_item_num,
/* End of Update */
l_operation_yield,
l_net_planning_percent,
l_kanban_item_flag,
/* Added for lot based material support. Need to query wip_supply_type, basis_type and fixed_order_qty */
l_wip_supply_type,
l_basis_type,
l_comp_foq;
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
/* l_demand_quantity := ROUND((l_schedule_quantity* nvl(l_component_usage, 1) *
nvl(p_cumulative_usage,1)*
(nvl(l_net_planning_percent, 100) /100)) /
(nvl(l_operation_yield, 1) * nvl(l_component_yield, 1)));*/
/* End of Update */
IF g_debug THEN
g_log_message := 'Deamnd Quantity:'||to_char(l_demand_quantity);
INSERT INTO MRP_KANBAN_DEMAND (
DEMAND_ID,
KANBAN_PLAN_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY,
LOCATOR_ID,
ASSEMBLY_ORG_ID,
ASSEMBLY_ITEM_ID,
ASSEMBLY_SUBINVENTORY,
ASSEMBLY_LOCATOR_ID,
DEMAND_DATE,
DEMAND_QUANTITY,
ORDER_TYPE,
KANBAN_ITEM_FLAG,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY )
SELECT
mrp_kanban_demand_s.nextval,
g_kanban_info_rec.kanban_plan_id,
g_kanban_info_rec.organization_id,
l_component_id,
l_subinventory,
l_locator_id,
g_kanban_info_rec.organization_id,
p_assembly_item_id,
p_subinventory,
p_locator_id,
l_schedule_date,
l_demand_quantity,
8,
l_kanban_item_flag,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM
DUAL;
/* Updated by Liye Ma 4/30/2001 for bug 1757798 and 1745046*/
--l_cumulative_usage := p_cumulative_usage * l_component_usage;
/* End of Update */
IF ( l_running_total_quantity > 0) THEN
IF g_debug THEN
g_log_message := 'Calling Cascade_Mds_Mps_Demand in recursive mode';
FUNCTION Insert_Fcst_Demand(
p_inventory_item_id IN number,
p_demand_type IN number,
p_line_id IN number )
RETURN BOOLEAN IS
l_line_id number;
SELECT current_forecast_quantity,
forecast_date,
rate_end_date,
bucket_type,
origination_type,
line_id
FROM mrp_forecast_dates
WHERE organization_id = g_kanban_info_rec.organization_id
/*
AND ((forecast_designator = g_kanban_info_rec.input_designator) or
(forecast_designator in ( -- forecast set
select forecast_designator
from mrp_forecast_designators
where forecast_set = g_kanban_info_rec.input_designator)
)
)
*/ --bug 5237549
AND FORECAST_DESIGNATOR in (
select g_kanban_info_rec.input_designator from dual
union all
SELECT FORECAST_DESIGNATOR
FROM MRP_FORECAST_DESIGNATORS
WHERE FORECAST_SET = g_kanban_info_rec.input_designator )
AND inventory_item_id = p_inventory_item_id
AND origination_type = p_demand_type
AND nvl(line_id,0) = nvl(p_line_id,0)
AND ((rate_end_date IS NULL AND
forecast_date BETWEEN Get_Offset_Date(
g_kanban_info_rec.start_date,
bucket_type )
AND g_kanban_info_rec.cutoff_date) OR
(rate_end_date is NOT NULL AND NOT
(rate_end_date < Get_Offset_Date(
g_kanban_info_rec.start_date,
bucket_type ) OR
forecast_date > g_kanban_info_rec.cutoff_date)));
g_log_message := 'Inserting Demand For :';
SELECT line_id
INTO l_line_id
FROM bom_operational_routings
WHERE alternate_routing_designator is NULL
AND assembly_item_id = p_inventory_item_id
AND organization_id = g_kanban_info_rec.organization_id;
INSERT INTO MRP_KANBAN_DEMAND (
DEMAND_ID,
KANBAN_PLAN_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY,
LOCATOR_ID,
ASSEMBLY_ITEM_ID,
ASSEMBLY_ORG_ID,
ASSEMBLY_SUBINVENTORY,
ASSEMBLY_LOCATOR_ID,
DEMAND_DATE,
DEMAND_QUANTITY,
ORDER_TYPE,
KANBAN_ITEM_FLAG,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY )
SELECT
mrp_kanban_demand_s.nextval,
g_kanban_info_rec.kanban_plan_id,
g_kanban_info_rec.organization_id,
p_inventory_item_id,
ps.subinventory_name,
ps.locator_id,
NULL,
NULL,
NULL,
NULL,
l_forecast_date,
(NVL(ps.allocation_percent, 100) *
l_forecast_quantity/ 100),
l_origination_type,
'Y',
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM
mtl_kanban_pull_sequences ps
WHERE ps.wip_line_id = l_line_id
AND ps.source_type = G_PRODUCTION_SOURCE_TYPE
AND ps.kanban_plan_id = decode (g_kanban_info_rec.replan_flag,
2, G_PRODUCTION_KANBAN,
1, g_kanban_info_rec.kanban_plan_id,
G_PRODUCTION_KANBAN)
AND ps.inventory_item_id = p_inventory_item_id
AND ps.organization_id = g_kanban_info_rec.organization_id;
SELECT bom_item_type,
nvl(fixed_order_quantity, nvl(minimum_order_quantity, nvl(maximum_order_quantity,1)))
INTO l_bom_item_type,
l_foq
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = g_kanban_info_rec.organization_id;
g_log_message := 'INSERT_FCST_DEMAND Sql Error ';
END Insert_Fcst_Demand;
FUNCTION Insert_Ap_Demand(
p_inventory_item_id IN number,
p_alt_bom IN varchar,
p_line_id IN number )
RETURN BOOLEAN IS
l_item_id number;
SELECT sum(planned_quantity) PQ,
scheduled_completion_date,
schedule_type,
line_id
FROM mrp_kanban_actual_prod_v
WHERE organization_id = g_kanban_info_rec.organization_id
AND scheduled_completion_date between g_kanban_info_rec.start_date AND
g_kanban_info_rec.cutoff_date
AND primary_item_id IN
( select COMPONENT_ITEM_ID from mrp_low_level_codes
WHERE ORGANIZATION_ID = g_kanban_info_rec.organization_id
AND PLAN_ID = g_kanban_info_rec.kanban_plan_id )
AND primary_item_id = p_inventory_item_id
AND nvl(alternate_bom_designator, 'NONE') = nvl(p_alt_bom , 'NONE')
AND nvl(line_id,0)=nvl(p_line_id,0)
group by scheduled_completion_date,schedule_type,line_id;
g_log_message := 'Inserting Demand For :';
SELECT line_id
INTO l_line_id
FROM bom_operational_routings
WHERE alternate_routing_designator is NULL
AND assembly_item_id = p_inventory_item_id
AND organization_id = g_kanban_info_rec.organization_id;
INSERT INTO MRP_KANBAN_DEMAND (
DEMAND_ID,
KANBAN_PLAN_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY,
LOCATOR_ID,
ASSEMBLY_ITEM_ID,
ASSEMBLY_ORG_ID,
ASSEMBLY_SUBINVENTORY,
ASSEMBLY_LOCATOR_ID,
DEMAND_DATE,
DEMAND_QUANTITY,
ORDER_TYPE,
KANBAN_ITEM_FLAG,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY )
SELECT
mrp_kanban_demand_s.nextval,
g_kanban_info_rec.kanban_plan_id,
g_kanban_info_rec.organization_id,
p_inventory_item_id,
ps.subinventory_name,
ps.locator_id,
NULL,
NULL,
NULL,
NULL,
l_schedule_date,
(NVL(ps.allocation_percent, 100) *
l_schedule_quantity/ 100),
l_schedule_type,
'Y',
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM
mtl_kanban_pull_sequences ps
WHERE ps.wip_line_id = l_line_id
AND ps.source_type = G_PRODUCTION_SOURCE_TYPE
AND ps.kanban_plan_id = decode (g_kanban_info_rec.replan_flag,
2, G_PRODUCTION_KANBAN,
1, g_kanban_info_rec.kanban_plan_id,
G_PRODUCTION_KANBAN)
AND ps.inventory_item_id = p_inventory_item_id
AND ps.organization_id = g_kanban_info_rec.organization_id;
SELECT bom_item_type,
nvl(fixed_order_quantity, nvl(minimum_order_quantity, nvl(maximum_order_quantity,1)))
INTO l_bom_item_type,
l_foq
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = g_kanban_info_rec.organization_id;
g_log_message := 'INSERT_AP_DEMAND Sql Error ';
END Insert_Ap_Demand;
FUNCTION Insert_Mds_Mps_Demand(
p_inventory_item_id IN number,
p_demand_type IN number,
p_line_id IN number )
RETURN BOOLEAN IS
l_item_id number;
SELECT
decode(schedule_quantity,NULL,MRP_KANBAN_PLAN_PK.Get_Repetitive_Demand(
schedule_date,rate_end_date,repetitive_daily_rate),schedule_quantity),
schedule_date,
schedule_origination_type,
line_id
FROM mrp_schedule_dates
WHERE organization_id = g_kanban_info_rec.organization_id
AND schedule_designator = g_kanban_info_rec.input_designator
AND schedule_level = 2
AND schedule_date BETWEEN g_kanban_info_rec.start_date AND
g_kanban_info_rec.cutoff_date
AND inventory_item_id = p_inventory_item_id
AND nvl(line_id,0)=nvl(p_line_id,0)
AND schedule_origination_type = p_demand_type ;
g_log_message := 'Inserting Demand For :';
SELECT line_id
INTO l_line_id
FROM bom_operational_routings
WHERE alternate_routing_designator is NULL
AND assembly_item_id = p_inventory_item_id
AND organization_id = g_kanban_info_rec.organization_id;
INSERT INTO MRP_KANBAN_DEMAND (
DEMAND_ID,
KANBAN_PLAN_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY,
LOCATOR_ID,
ASSEMBLY_ITEM_ID,
ASSEMBLY_ORG_ID,
ASSEMBLY_SUBINVENTORY,
ASSEMBLY_LOCATOR_ID,
DEMAND_DATE,
DEMAND_QUANTITY,
ORDER_TYPE,
KANBAN_ITEM_FLAG,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY )
SELECT
mrp_kanban_demand_s.nextval,
g_kanban_info_rec.kanban_plan_id,
g_kanban_info_rec.organization_id,
p_inventory_item_id,
ps.subinventory_name,
ps.locator_id,
NULL,
NULL,
NULL,
NULL,
l_schedule_date,
(NVL(ps.allocation_percent, 100) *
l_schedule_quantity/ 100),
l_schedule_origination_type,
'Y',
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM
mtl_kanban_pull_sequences ps
WHERE ps.wip_line_id = l_line_id
AND ps.source_type = G_PRODUCTION_SOURCE_TYPE
AND ps.kanban_plan_id = decode (g_kanban_info_rec.replan_flag,
2, G_PRODUCTION_KANBAN,
1, g_kanban_info_rec.kanban_plan_id,
G_PRODUCTION_KANBAN)
AND ps.inventory_item_id = p_inventory_item_id
AND ps.organization_id = g_kanban_info_rec.organization_id;
SELECT bom_item_type,
nvl(fixed_order_quantity, nvl(minimum_order_quantity, nvl(maximum_order_quantity,1)))
INTO l_bom_item_type,
l_foq
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = g_kanban_info_rec.organization_id;
g_log_message := 'INSERT_DEMAND Sql Error ';
END Insert_Mds_Mps_Demand;
SELECT /*+ first_rows */ bw.week_start_date --bug 5237549
INTO l_offset_date
FROM bom_cal_week_start_dates bw,
mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND bw.calendar_code = mp.calendar_code
AND bw.exception_set_id = mp.calendar_exception_set_id
AND bw.week_start_date <= p_start_date
AND bw.next_date >= p_start_date;
SELECT bp.period_start_date
INTO l_offset_date
FROM bom_period_start_dates bp,
mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND bp.calendar_code = mp.calendar_code
AND bp.exception_set_id = mp.calendar_exception_set_id
AND bp.period_start_date <= p_start_date
AND bp.next_date >= p_start_date;
SELECT inventory_item_id,
origination_type,
line_id
FROM mrp_forecast_dates
WHERE organization_id = g_kanban_info_rec.organization_id
/*
AND ((forecast_designator = g_kanban_info_rec.input_designator) or
(forecast_designator in ( -- forecast set
select forecast_designator
from mrp_forecast_designators
where forecast_set = g_kanban_info_rec.input_designator)
)
)
*/ --bug 5237549
AND FORECAST_DESIGNATOR in (
select g_kanban_info_rec.input_designator from dual
union all
SELECT FORECAST_DESIGNATOR
FROM MRP_FORECAST_DESIGNATORS
WHERE FORECAST_SET = g_kanban_info_rec.input_designator )
AND ((rate_end_date IS NULL AND
forecast_date BETWEEN Get_Offset_Date(
g_kanban_info_rec.start_date,
bucket_type )
AND g_kanban_info_rec.cutoff_date) OR
(rate_end_date is NOT NULL AND NOT
(rate_end_date < Get_Offset_Date(
g_kanban_info_rec.start_date,
bucket_type ) OR
forecast_date > g_kanban_info_rec.cutoff_date)))
GROUP BY inventory_item_id,origination_type,line_id;
SELECT inventory_item_id,
schedule_origination_type,
line_id
FROM mrp_schedule_dates
WHERE organization_id = g_kanban_info_rec.organization_id
AND schedule_designator = g_kanban_info_rec.input_designator
AND schedule_level = 2
AND schedule_date BETWEEN g_kanban_info_rec.start_date AND
g_kanban_info_rec.cutoff_date
GROUP BY inventory_item_id,schedule_origination_type,line_id;
SELECT primary_item_id,
alternate_bom_designator,
line_id
FROM mrp_kanban_actual_prod_v
WHERE organization_id = g_kanban_info_rec.organization_id
AND scheduled_completion_date between g_kanban_info_rec.start_date AND
g_kanban_info_rec.cutoff_date
AND primary_item_id IN
( select COMPONENT_ITEM_ID from mrp_low_level_codes
where ORGANIZATION_ID = g_kanban_info_rec.organization_id
AND PLAN_ID = g_kanban_info_rec.kanban_plan_id )
group by primary_item_id,alternate_bom_designator,line_id;
g_log_message := 'Calling Insert Demand function';
l_ret_val := Insert_Fcst_Demand( l_demand_rec.inventory_item_id,
l_demand_type,
l_line_id );
l_ret_val := Insert_Mds_Mps_Demand( l_demand_rec.inventory_item_id,
l_demand_type,
l_line_id );
l_ret_val := Insert_Ap_Demand( l_demand_rec.inventory_item_id,
l_alt_bom,
l_line_id );
SELECT (sum(demand_quantity)/p_total_workdays),
inventory_item_id,
subinventory,
locator_id
FROM mrp_kanban_demand
WHERE kanban_plan_id = g_kanban_info_rec.kanban_plan_id
AND organization_id = g_kanban_info_rec.organization_id
AND (demand_date >= g_kanban_info_rec.start_date
AND demand_date <= g_kanban_info_rec.cutoff_date )
AND kanban_item_flag = 'Y'
GROUP BY
inventory_item_id,
subinventory,
locator_id;
SELECT source_type,
supplier_id,
supplier_site_id,
source_organization_id,
source_subinventory,
source_locator_id,
wip_line_id,
replenishment_lead_time,
calculate_kanban_flag,
kanban_size,
fixed_lot_multiplier,
safety_stock_days,
number_of_cards,
minimum_order_quantity,
aggregation_type,
allocation_percent,
release_kanban_flag
FROM mtl_kanban_pull_sequences
WHERE kanban_plan_id = decode (g_kanban_info_rec.replan_flag,
2, G_PRODUCTION_KANBAN,
1, g_kanban_info_rec.kanban_plan_id,
G_PRODUCTION_KANBAN)
AND organization_id = g_kanban_info_rec.organization_id
AND inventory_item_id = l_item_id
AND subinventory_name = l_subinventory
AND nvl(locator_id,-1) = nvl(l_locator_id,-1);
SELECT
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
INTO
l_pull_sequence_rec.request_id,
l_pull_sequence_rec.program_application_id,
l_pull_sequence_rec.program_id,
l_pull_sequence_rec.program_update_date,
l_pull_sequence_rec.last_update_date,
l_pull_sequence_rec.last_updated_by,
l_pull_sequence_rec.creation_date,
l_pull_sequence_rec.created_by
FROM dual;
INV_Kanban_PVT.Insert_pull_sequence
(l_return_status,
l_pull_sequence_rec);
INV_Kanban_PVT.Update_pull_sequence
(l_return_status,
l_pull_sequence_rec);
g_log_message := 'Error in Inventory Insert/Update API';
SELECT count(*)
INTO l_total_workdays
FROM bom_calendar_dates bcd,
mtl_parameters mp
WHERE mp.organization_id = g_kanban_info_rec.organization_id
AND bcd.calendar_code = mp.calendar_code
AND bcd.seq_num IS NOT NULL
AND (bcd.calendar_date BETWEEN g_kanban_info_rec.start_date AND
g_kanban_info_rec.cutoff_date );