[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(week_start_date)
INTO l_buckets
FROM bom_cal_week_start_dates cal
WHERE cal.exception_set_id = x_exception_set_id
AND cal.calendar_code = x_calendar_code
AND cal.week_start_date between x_start_date
and x_end_date;
SELECT count(period_start_date)
INTO l_buckets
FROM bom_period_start_dates cal
WHERE cal.exception_set_id = x_exception_set_id
AND cal.calendar_code = x_calendar_code
AND cal.period_start_date between x_start_date
and x_end_date;
SELECT count(*)
INTO l_days
FROM bom_calendar_dates
WHERE calendar_code = x_calendar_code
AND exception_set_id = x_exception_set_id
AND calendar_date BETWEEN x_start_date AND x_end_date
AND seq_num IS NOT NULL;
SELECT decode(next_date, week_start_date,
x_last_calendar_date, (next_date -1)) last_day
FROM bom_cal_week_start_dates
WHERE calendar_code = x_calendar_code
AND exception_set_id = x_exception_set_id
AND week_start_date = nvl(x_rate_end_date, x_forecast_date);
SELECT decode(next_date, period_start_date,
x_last_calendar_date, (next_date -1)) last_day
FROM bom_period_start_dates
WHERE calendar_code = x_calendar_code
AND exception_set_id = x_exception_set_id
AND period_start_date = nvl(x_rate_end_date, x_forecast_date);
SELECT bopr.resource_id id , bosv.department_id dept_id
FROM bom_operation_resources bopr,
bom_resources br,
bom_operation_sequences_v bosv,
bom_operational_routings bor
WHERE bopr.operation_sequence_id= bosv.operation_sequence_id
AND bopr.resource_id = br.resource_id
AND br.resource_type = p_resource_type
AND bor.mixed_model_map_flag = 1
AND bor.line_id = p_line_id
AND bor.assembly_item_id = p_assembly_item_id
AND bor.organization_id = p_org_id
AND bor.common_routing_sequence_id = bosv.routing_sequence_id
AND bosv.operation_type = 1
AND bosv.line_op_code = p_op_code;
SELECT bopr.resource_id id , bosv.department_id dept_id
FROM bom_operation_resources bopr,
bom_resources br,
bom_operation_sequences_v bosv,
bom_operational_routings bor
WHERE bopr.operation_sequence_id= bosv.operation_sequence_id
AND bopr.resource_id = br.resource_id
AND br.resource_type = p_resource_type
AND bor.mixed_model_map_flag = 1
AND bor.line_id = p_line_id
AND bor.assembly_item_id = p_assembly_item_id
AND bor.organization_id = p_org_id
AND bor.common_routing_sequence_id = bosv.routing_sequence_id
AND bosv.operation_type = 1
AND bosv.process_code = p_op_code;
SELECT bdr.capacity_units CU
FROM bom_department_resources bdr
WHERE bdr.department_id = p_dept_table(i)
AND bdr.resource_id = p_resource_table(i);
SELECT distinct bso.operation_code OC, bso.sequence_num SN
FROM bom_standard_operations bso,
bom_operation_sequences bos,
bom_operational_routings bor
WHERE bor.mixed_model_map_flag = 1
AND bor.line_id = p_line_id
AND bor.organization_id = p_org_id
AND bor.common_routing_sequence_id = bos.routing_sequence_id
AND bos.operation_type = p_operation_type
AND NVL(bos.eco_for_production,2) = 2
AND bso.standard_operation_id = bos.standard_operation_id
AND (p_family_item_id is null
OR
EXISTS (SELECT 'x'
FROM mtl_system_items msi
WHERE msi.inventory_item_id = bor.assembly_item_id
AND msi.organization_id = p_org_id
AND msi.product_family_item_id = p_family_item_id))
ORDER BY bso.sequence_num;
SELECT distinct bso.operation_code OC, bso.sequence_num SN
FROM bom_standard_operations bso,
bom_operation_sequences bos,
bom_operational_routings bor
WHERE bor.mixed_model_map_flag = 1
AND bor.line_id = p_line_id
AND bor.organization_id = p_org_id
AND bor.common_routing_sequence_id = bos.routing_sequence_id
AND bos.operation_type = p_operation_type
AND NVL(bos.eco_for_production,2) = 2
AND bso.standard_operation_id = bos.standard_operation_id
AND (p_family_item_id is null
OR
EXISTS (SELECT 'x'
FROM mtl_system_items msi
WHERE msi.inventory_item_id = bor.assembly_item_id
AND msi.organization_id = p_org_id
AND msi.product_family_item_id = p_family_item_id))
ORDER BY bso.operation_code;
INSERT INTO bom_mixed_model_map_processes
(MIXED_MODEL_MAP_ID,
GROUP_NUMBER,
SEQUENCE_ID,
OPERATION_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(p_mmm_id,
l_group_number,
l_seq_id,
c1rec.OC,
p_user_id,
sysdate,
p_user_id,
sysdate);
INSERT INTO bom_mixed_model_map_processes
(MIXED_MODEL_MAP_ID,
GROUP_NUMBER,
SEQUENCE_ID,
OPERATION_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(p_mmm_id,
l_group_number,
l_seq_id,
c2rec.OC,
p_user_id,
sysdate,
p_user_id,
sysdate);
SELECT sum(planned_quantity) PQ
FROM mrp_line_schedules_v
WHERE organization_id = p_org_id
AND line_id = p_line_id
AND primary_item_id = p_assembly_item_id
AND trunc(scheduled_completion_date) between
p_start_date and p_end_date;
SELECT sum(planned_quantity) PQ
FROM
(
SELECT
REP.ORGANIZATION_ID,
REPITEM.PRIMARY_ITEM_ID,
REP.DAILY_PRODUCTION_RATE planned_quantity,
REP.LINE_ID,
BOM.CALENDAR_DATE scheduled_completion_date
FROM MFG_LOOKUPS WST,
MFG_LOOKUPS ML,
MFG_LOOKUPS WJS,
WIP_ENTITIES ENTITIES,
MTL_SYSTEM_ITEMS_KFV KFV,
MTL_PARAMETERS MP,
BOM_CALENDAR_DATES BOM,
WIP_REPETITIVE_ITEMS REPITEM,
WIP_LINES LINE,
WIP_REPETITIVE_SCHEDULES REP
WHERE WST.LOOKUP_CODE = 3
AND WST.LOOKUP_TYPE = 'MRP_WIP_SCHEDULE_TYPE'
AND ML.LOOKUP_TYPE = 'MRP_WORKBENCH_IMPLEMENT_AS'
AND ML.LOOKUP_CODE = 4
AND WJS.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND WJS.LOOKUP_CODE = REP.STATUS_TYPE
AND ENTITIES.WIP_ENTITY_ID = REP.WIP_ENTITY_ID
AND KFV.INVENTORY_ITEM_ID = REPITEM.PRIMARY_ITEM_ID
AND KFV.ORGANIZATION_ID = REPITEM.ORGANIZATION_ID
AND BOM.CALENDAR_DATE BETWEEN TRUNC(REP.FIRST_UNIT_COMPLETION_DATE) AND
TRUNC(REP.LAST_UNIT_COMPLETION_DATE)
AND BOM.SEQ_NUM IS NOT NULL
AND MP.CALENDAR_CODE = BOM.CALENDAR_CODE
AND MP.CALENDAR_EXCEPTION_SET_ID = BOM.EXCEPTION_SET_ID
AND MP.ORGANIZATION_ID = REP.ORGANIZATION_ID
AND REPITEM.LINE_ID = REP.LINE_ID
AND REPITEM.WIP_ENTITY_ID = REP.WIP_ENTITY_ID
AND REPITEM.ORGANIZATION_ID = REP.ORGANIZATION_ID
AND LINE.ORGANIZATION_ID = REP.ORGANIZATION_ID
AND LINE.LINE_ID = REP.LINE_ID
UNION ALL
SELECT
JOB.ORGANIZATION_ID,
JOB.PRIMARY_ITEM_ID,
JOB.START_QUANTITY planned_quantity,
JOB.LINE_ID,
JOB.SCHEDULED_COMPLETION_DATE
FROM MFG_LOOKUPS WST,
OE_ORDER_LINES_ALL SO_LINES,
WIP_SO_ALLOCATIONS WSA,
WIP_SCHEDULE_GROUPS SCH,
MFG_LOOKUPS WJS,
WIP_ENTITIES ENTITIES,
MTL_SYSTEM_ITEMS_KFV KFV,
WIP_DISCRETE_JOBS JOB,
WIP_LINES LINE
WHERE WST.LOOKUP_CODE = 1
AND WST.LOOKUP_TYPE = 'MRP_WIP_SCHEDULE_TYPE'
AND SO_LINES.LINE_ID(+) = TO_NUMBER(WSA.DEMAND_SOURCE_LINE)
AND WSA.WIP_ENTITY_ID(+) = JOB.WIP_ENTITY_ID
AND WSA.ORGANIZATION_ID(+) = JOB.ORGANIZATION_ID
AND SCH.SCHEDULE_GROUP_ID(+) = JOB.SCHEDULE_GROUP_ID
AND SCH.ORGANIZATION_ID(+) = JOB.ORGANIZATION_ID
AND WJS.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND WJS.LOOKUP_CODE = JOB.STATUS_TYPE
AND ENTITIES.ORGANIZATION_ID = JOB.ORGANIZATION_ID
AND ENTITIES.WIP_ENTITY_ID = JOB.WIP_ENTITY_ID
AND KFV.INVENTORY_ITEM_ID = JOB.PRIMARY_ITEM_ID
AND KFV.ORGANIZATION_ID = JOB.ORGANIZATION_ID
AND JOB.LINE_ID = LINE.LINE_ID
AND JOB.ORGANIZATION_ID = LINE.ORGANIZATION_ID
UNION ALL
SELECT
FLOW.ORGANIZATION_ID,
FLOW.PRIMARY_ITEM_ID,
FLOW.PLANNED_QUANTITY planned_quantity,
FLOW.LINE_ID,
FLOW.SCHEDULED_COMPLETION_DATE
FROM MFG_LOOKUPS WST,
OE_ORDER_LINES_ALL SO_LINES,
WIP_SCHEDULE_GROUPS SCH,
MTL_SYSTEM_ITEMS_KFV KFV,
WIP_FLOW_SCHEDULES FLOW,
WIP_LINES LINE,
MFG_LOOKUPS ST
WHERE WST.LOOKUP_CODE = 2
AND WST.LOOKUP_TYPE = 'MRP_WIP_SCHEDULE_TYPE'
AND SO_LINES.LINE_ID(+) = TO_NUMBER(FLOW.DEMAND_SOURCE_LINE)
AND SCH.SCHEDULE_GROUP_ID(+) = FLOW.SCHEDULE_GROUP_ID
AND SCH.ORGANIZATION_ID(+) = FLOW.ORGANIZATION_ID
AND KFV.INVENTORY_ITEM_ID = FLOW.PRIMARY_ITEM_ID
AND KFV.ORGANIZATION_ID = FLOW.ORGANIZATION_ID
AND FLOW.LINE_ID = LINE.LINE_ID
AND FLOW.ORGANIZATION_ID = LINE.ORGANIZATION_ID
AND ST.LOOKUP_TYPE = 'WIP_FLOW_SCHEDULE_STATUS'
AND ST.LOOKUP_CODE = FLOW.STATUS
)
WHERE organization_id = p_org_id
AND line_id = p_line_id
AND primary_item_id = p_assembly_item_id
AND trunc(scheduled_completion_date) between
p_start_date and p_end_date;
END LOOP; -- SELECTING ACTUAL PRODUCTION RECORDS
SELECT sum(order_quantity) PQ
FROM mrp_unscheduled_orders_v
WHERE organization_id = p_org_id
AND line_id = p_line_id
AND inventory_item_id = p_assembly_item_id
AND trunc(order_date) between p_start_date and p_end_date
AND unscheduled_order_option = i_option;
SELECT sum(order_quantity) PQ
FROM
(
SELECT
GREATEST((INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SL1.SHIP_FROM_ORG_ID,
SL1.INVENTORY_ITEM_ID,
SL1.ORDER_QUANTITY_UOM,
SL1.ORDERED_QUANTITY) -
MRP_FLOW_SCHEDULE_UTIL.GET_FLOW_QUANTITY(SL1.LINE_ID,
2,
TO_CHAR(NULL),
MSI1.REPLENISH_TO_ORDER_FLAG) -
MRP_FLOW_SCHEDULE_UTIL.GET_RESERVATION_QUANTITY(SL1.SHIP_FROM_ORG_ID,
SL1.INVENTORY_ITEM_ID,
SL1.LINE_ID,
MSI1.REPLENISH_TO_ORDER_FLAG)),
0) order_quantity,
sl1.inventory_item_id,
sl1.ship_from_org_id organization_id,
wl.line_id,
sl1.schedule_ship_date order_date
FROM
OE_ORDER_LINES_ALL SL1,
MTL_SYSTEM_ITEMS_KFV MSI1,
WIP_LINES WL,
(select sl2.line_id,
decode((select 1
from oe_order_holds_all oh
where oh.header_id = sl2.header_id
and rownum = 1
and oh.released_flag = 'N'),
null,
0,
decode(sl2.ato_line_id,
null,
mrp_flow_schedule_util.check_holds(sl2.header_id,
sl2.line_id,
'OEOL',
'LINE_SCHEDULING'),
mrp_flow_schedule_util.check_holds(sl2.header_id,
sl2.line_id,
null,
null))) hold
from oe_order_lines_all sl2) line_holds,
(select sl2.line_id,
CTO_WIP_WORKFLOW_API_PK.workflow_build_status(sl2.LINE_ID) status
from oe_order_lines_all sl2) line_build
WHERE
line_build.line_id = sl1.line_id
AND 1 = decode(MSI1.REPLENISH_TO_ORDER_FLAG, 'N', 1, line_build.status)
AND MSI1.BUILD_IN_WIP_FLAG = 'Y'
AND MSI1.PICK_COMPONENTS_FLAG = 'N'
AND MSI1.BOM_ITEM_TYPE = 4
AND MSI1.ORGANIZATION_ID = SL1.SHIP_FROM_ORG_ID
AND MSI1.INVENTORY_ITEM_ID = SL1.INVENTORY_ITEM_ID
AND SL1.ORDERED_QUANTITY > 0
AND SL1.VISIBLE_DEMAND_FLAG = 'Y'
AND SL1.OPEN_FLAG = 'Y'
AND SL1.ITEM_TYPE_CODE in ('STANDARD', 'CONFIG', 'INCLUDED', 'OPTION')
AND OE_INSTALL.GET_ACTIVE_PRODUCT = 'ONT'
AND wl.organization_id = sl1.ship_from_org_id
AND wl.line_id in (select line_id
from bom_operational_routings bor2
where bor2.assembly_item_id = sl1.inventory_item_id
and bor2.organization_id = sl1.ship_from_org_id
and bor2.cfm_routing_flag = 1)
AND SL1.SHIPPED_QUANTITY is NULL
and sl1.line_id = line_holds.line_id
and line_holds.hold = 0
AND NVL(SL1.FULFILLED_FLAG, 'N') <> 'Y'
)
WHERE organization_id = p_org_id
AND line_id = p_line_id
AND inventory_item_id = p_assembly_item_id
AND trunc(order_date) between p_start_date and p_end_date;
END LOOP; -- SELECTING UNSCHEDULED ORDERS RECORDS
SELECT sum(order_quantity) PQ
FROM
(
SELECT
MR1.ORGANIZATION_ID,
MR1.INVENTORY_ITEM_ID,
WL.LINE_ID,
NVL(MR1.FIRM_DATE, MR1.NEW_SCHEDULE_DATE) order_date,
GREATEST((NVL(MR1.FIRM_QUANTITY, MR1.NEW_ORDER_QUANTITY) -
MRP_FLOW_SCHEDULE_UTIL.GET_FLOW_QUANTITY(TO_CHAR(MR1.TRANSACTION_ID),
100,
NULL,
NULL)),
0) order_quantity
FROM MTL_SYSTEM_ITEMS_B KFV,
MRP_SYSTEM_ITEMS RSI1,
MRP_PLANS MP1,
MRP_RECOMMENDATIONS MR1,
WIP_LINES WL
WHERE MP1.PLAN_COMPLETION_DATE IS NOT NULL
AND MP1.DATA_COMPLETION_DATE IS NOT NULL
AND MP1.COMPILE_DESIGNATOR = MR1.COMPILE_DESIGNATOR
AND (MP1.ORGANIZATION_ID = MR1.ORGANIZATION_ID OR
(MP1.ORGANIZATION_ID IN
(SELECT ORGANIZATION_ID
FROM MRP_PLAN_ORGANIZATIONS
WHERE COMPILE_DESIGNATOR = MR1.COMPILE_DESIGNATOR
AND PLANNED_ORGANIZATION = MR1.ORGANIZATION_ID)))
AND MR1.ORGANIZATION_ID = MR1.SOURCE_ORGANIZATION_ID
AND KFV.INVENTORY_ITEM_ID = RSI1.INVENTORY_ITEM_ID
AND KFV.ORGANIZATION_ID = RSI1.ORGANIZATION_ID
AND NVL(KFV.RELEASE_TIME_FENCE_CODE, -1) <> 6 /* KANBAN ITEM */
AND MR1.ORDER_TYPE = 5 /* PLANNED ORDER */
AND MR1.ORGANIZATION_ID = RSI1.ORGANIZATION_ID
AND MR1.COMPILE_DESIGNATOR = RSI1.COMPILE_DESIGNATOR
AND MR1.INVENTORY_ITEM_ID = RSI1.INVENTORY_ITEM_ID
AND MR1.COMPILE_DESIGNATOR =
(SELECT DESIGNATOR
FROM MRP_DESIGNATORS_VIEW
WHERE PRODUCTION = 1
AND ORGANIZATION_ID = MP1.ORGANIZATION_ID
AND DESIGNATOR = MR1.COMPILE_DESIGNATOR)
AND RSI1.BUILD_IN_WIP_FLAG = 1 /* YES */
AND RSI1.BOM_ITEM_TYPE = 4
AND (RSI1.IN_SOURCE_PLAN = 2 OR RSI1.IN_SOURCE_PLAN IS NULL)
AND wl.organization_id = MR1.ORGANIZATION_ID
AND wl.line_id in (select line_id
from bom_operational_routings bor2
where bor2.assembly_item_id = MR1.INVENTORY_ITEM_ID
and bor2.organization_id = MR1.ORGANIZATION_ID
and bor2.cfm_routing_flag = 1)
)
WHERE organization_id = p_org_id
AND line_id = p_line_id
AND inventory_item_id = p_assembly_item_id
AND trunc(order_date) between p_start_date and p_end_date;
END LOOP; -- SELECTING UNSCHEDULED ORDERS RECORDS
SELECT current_forecast_quantity, rate_end_date, bucket_type,
forecast_date
FROM mrp_forecast_dates
WHERE organization_id = p_org_id
AND forecast_designator = p_demand_code
AND inventory_item_id = p_assembly_item_id
AND (line_id = p_line_id OR line_id is NULL);
END LOOP; -- SELECTING FORECAST RECORDS
SELECT repetitive_daily_rate, rate_end_date,
schedule_quantity, schedule_date
FROM mrp_schedule_dates
WHERE organization_id = p_org_id
AND schedule_designator = p_demand_code
AND (line_id = p_line_id OR line_id is NULL)
AND schedule_level = 2
AND inventory_item_id = p_assembly_item_id;
SELECT assembly_item_id item_id
FROM bom_operational_routings bor
WHERE mixed_model_map_flag = 1
AND line_id = p_line_id
AND organization_id = p_org_id
AND (p_family_item_id is null
OR
EXISTS (SELECT 'x'
FROM mtl_system_items msi
WHERE msi.inventory_item_id = bor.assembly_item_id
AND msi.organization_id = p_org_id
AND msi.product_family_item_id = p_family_item_id))
ORDER BY assembly_item_id;
select bor.resource_id resource_id,
nvl(bor.activity_id, -1) activity_id,
bos1.standard_operation_id std_op_id,
brtg.assembly_item_id assy_item_id,
sum(bor.usage_rate_or_amount * nvl(con.conversion_rate, 0) /
nvl(l_hour_conv, 1) *
nvl(bos1.net_planning_percent, 100) / 100 /
decode(bos1.reverse_cumulative_yield, '', 1,
'0', 1, bos1.reverse_cumulative_yield)) resource_needed
from bom_operation_resources bor,
bom_resources br,
bom_operational_routings brtg,
bom_operation_sequences bos1,
bom_operation_sequences bos2,
mtl_uom_conversions con
where brtg.line_id = p_line_id
and brtg.mixed_model_map_flag = 1
and bos1.routing_sequence_id = brtg.common_routing_sequence_id
and bos2.routing_sequence_id = brtg.common_routing_sequence_id
and br.resource_id = bor.resource_id
and con.uom_code (+) = br.unit_of_measure
and con.inventory_item_id (+) = 0
and (( bos1.operation_type = 3
and bos2.line_op_seq_id = bos1.operation_sequence_id
and p_operation_type = 3) or
( bos1.operation_type = 2
and bos2.process_op_seq_id = bos1.operation_sequence_id
and p_operation_type = 2))
and bos2.operation_sequence_id = bor.operation_sequence_id
group by bor.resource_id, nvl(bor.activity_id, -1), bos1.standard_operation_id, brtg.assembly_item_id
order by bor.resource_id, nvl(bor.activity_id, -1), bos1.standard_operation_id, brtg.assembly_item_id;
l_demand_table.DELETE;
insert into lm_temp (
text
)values(
l_text
);
l_process_lop_table.DELETE;
select bos.standard_operation_id op,
bos.reverse_cumulative_yield yld,
bos.net_planning_percent pct
from bom_operation_sequences bos,
bom_operational_routings bor
where bor.assembly_item_id = l_assy_id
and bor.mixed_model_map_flag = 1
and bor.common_routing_sequence_id = bos.routing_sequence_id
and bos.operation_type = p_operation_type;
insert into lm_temp (
text
)values(
l_text
);
SELECT nvl(conversion_rate, 0)
INTO l_hour_conv
FROM mtl_uom_conversions
WHERE uom_code = l_hour_uom
AND inventory_item_id = 0;
insert into BOM_MIXED_MODEL_MAP_RES (
mixed_model_map_id,
resource_id,
resource_code,
activity_id,
activity,
standard_operation_id,
operation_code,
resource_type,
organization_id,
resource_needed
)
select
p_mmm_id,
l_res_detail_table(l_index).resource_id,
br.resource_code,
l_res_detail_table(l_index).activity_id,
ca.activity,
l_res_detail_table(l_index).standard_operation_id,
bso.operation_code,
br.resource_type,
p_org_id,
l_res_detail_table(l_index).resource_needed
from cst_activities ca,
bom_standard_operations bso,
bom_resources br
where ca.activity_id = l_res_detail_table(l_index).activity_id
and bso.standard_operation_id = l_res_detail_table(l_index).standard_operation_id
and br.resource_id = l_res_detail_table(l_index).resource_id;
insert into BOM_MIXED_MODEL_MAP_RES (
mixed_model_map_id,
resource_id,
resource_code,
activity_id,
activity,
standard_operation_id,
operation_code,
resource_type,
organization_id,
resource_needed
)
select
p_mmm_id,
l_res_detail_table(l_index).resource_id,
br.resource_code,
null,
null,
l_res_detail_table(l_index).standard_operation_id,
bso.operation_code,
br.resource_type,
p_org_id,
l_res_detail_table(l_index).resource_needed
from bom_standard_operations bso,
bom_resources br
where bso.standard_operation_id = l_res_detail_table(l_index).standard_operation_id
and br.resource_id = l_res_detail_table(l_index).resource_id;
SELECT assembly_item_id AII
FROM bom_operational_routings bor
WHERE mixed_model_map_flag = 1
AND line_id = p_line_id
AND organization_id = p_org_id
AND (p_family_item_id is null
OR
EXISTS (SELECT 'x'
FROM mtl_system_items msi
WHERE msi.inventory_item_id = bor.assembly_item_id
AND msi.organization_id = p_org_id
AND msi.product_family_item_id = p_family_item_id))
ORDER BY assembly_item_id;
SELECT mixed_model_map_id
FROM bom_mixed_model_map_header
WHERE line_id = p_line_id
AND ((p_family_item_id is NULL AND family_item_id is NULL)
OR (family_item_id = p_family_item_id))
AND organization_id = p_org_id
AND process_or_lineop = G_PROCESS;
SELECT mixed_model_map_id
FROM bom_mixed_model_map_header
WHERE line_id = p_line_id
AND ((p_family_item_id is NULL AND family_item_id is NULL)
OR (family_item_id = p_family_item_id))
AND organization_id = p_org_id
AND process_or_lineop = G_LINEOP;
SELECT sum(decode(p_time_type, G_USER_CALC, machine_time_user,
machine_time_calc)) machine_time,
sum(decode(p_time_type, G_USER_CALC, labor_time_user,
labor_time_calc)) labor_time,
sum(decode(p_time_type, G_USER_CALC, total_time_user,
total_time_calc)) total_time,
((1 + nvl((p_boost_percent * .01), 0)) *
nvl((1/(avg(decode(nvl(reverse_cumulative_yield, 1),0,1,nvl(reverse_cumulative_yield,1))))),1) *
nvl(avg(nvl((net_planning_percent * .01), 1)),1) * l_demand)
process_volume
FROM bom_standard_operations bso,
bom_operation_sequences bos,
bom_operational_routings bor
WHERE bor.assembly_item_id = c1rec.AII
AND bor.mixed_model_map_flag = 1
AND bor.organization_id = p_org_id
AND bor.line_id = p_line_id
AND bor.common_routing_sequence_id = bos.routing_sequence_id
AND bos.standard_operation_id = bso.standard_operation_id
AND NVL(bos.eco_for_production,2) = 2
AND bso.operation_code = l_op_code
AND bso.organization_id = p_org_id
AND bso.line_id = p_line_id
ANd bso.operation_type = p_operation_type;
GOTO insert_record;
<>
l_stmt_num := 68;
INSERT INTO bom_mixed_model_map_cells
(MIXED_MODEL_MAP_ID,
GROUP_NUMBER,
SEQUENCE_ID,
PRODUCT_ITEM_ID,
DEMAND,
MACHINE_TIME,
LABOR_TIME,
TOTAL_TIME,
PROCESS_VOLUME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(p_mmm_id,
p_group_number,
i,
c1rec.AII,
ROUND(nvl(l_demand_for_display, 0), G_DECIMAL),
ROUND(nvl(l_machine_time, 0), G_DECIMAL),
ROUND(nvl(l_labor_time, 0), G_DECIMAL),
ROUND(nvl(l_total_time, 0), G_DECIMAL),
ROUND(nvl(l_process_volume_for_display, 0), G_DECIMAL),
p_user_id,
sysdate,
p_user_id,
sysdate);
INSERT INTO bom_mixed_model_map_cells
(MIXED_MODEL_MAP_ID,
GROUP_NUMBER,
SEQUENCE_ID,
PRODUCT_ITEM_ID,
DEMAND,
MACHINE_TIME,
LABOR_TIME,
TOTAL_TIME,
PROCESS_VOLUME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES
(p_mmm_id,
p_group_number,
i,
c1rec.AII,
ROUND(nvl(l_demand_for_display, 0), G_DECIMAL),
null,
null,
null,
null,
p_user_id,
sysdate,
p_user_id,
sysdate);
SELECT machines_needed MN, labor_needed LN,
in_process_kanban IPK
FROM bom_mixed_model_map_processes
WHERE mixed_model_map_id = l_mmm_id
AND operation_code = p_op_code1;
SELECT machines_needed MN, labor_needed LN,
in_process_kanban IPK
FROM bom_mixed_model_map_processes
WHERE mixed_model_map_id = l_mmm_id
AND operation_code = p_op_code2;
SELECT machines_needed MN, labor_needed LN,
in_process_kanban IPK
FROM bom_mixed_model_map_processes
WHERE mixed_model_map_id = l_mmm_id
AND operation_code = p_op_code3;
SELECT machines_needed MN, labor_needed LN,
in_process_kanban IPK
FROM bom_mixed_model_map_processes
WHERE mixed_model_map_id = l_mmm_id
AND operation_code = p_op_code4;
SELECT machines_needed MN, labor_needed LN,
in_process_kanban IPK
FROM bom_mixed_model_map_processes
WHERE mixed_model_map_id = l_mmm_id
AND operation_code = p_op_code5;
UPDATE bom_mixed_model_map_processes
SET machine_weighted_time = ROUND(nvl(l_machine_wt1, 0), G_DECIMAL),
labor_weighted_time = ROUND(nvl(l_labor_wt1, 0), G_DECIMAL),
total_weighted_time = ROUND(nvl(l_total_wt1, 0), G_DECIMAL),
machines_needed = ROUND(nvl(l_machines_needed1, 0), G_DECIMAL),
machines_assigned = ROUND(nvl(l_mc_assigned1, 0), G_DECIMAL),
labor_needed = ROUND(nvl(l_labor_needed1, 0), G_DECIMAL),
labor_assigned = ROUND(nvl(l_lb_assigned1, 0), G_DECIMAL),
takt_time = ROUND(nvl(l_takt1, 0), G_DECIMAL),
takt_time_for_assigned = ROUND(nvl(l_takt_time_for_assigned1, 0), G_DECIMAL),
in_process_kanban = ROUND(nvl(l_ipk1, 0), G_DECIMAL),
machines_needed_delta = ROUND(l_mn_delta1, G_DECIMAL),
labor_needed_delta = ROUND(l_ln_delta1, G_DECIMAL),
in_process_kanban_delta = ROUND(l_ipk_delta1, G_DECIMAL)
WHERE mixed_model_map_id = p_mmm_id
AND group_number = p_group_number
AND sequence_id = 1 ;
UPDATE bom_mixed_model_map_processes
SET machine_weighted_time = ROUND(nvl(l_machine_wt2, 0), G_DECIMAL),
labor_weighted_time = ROUND(nvl(l_labor_wt2, 0), G_DECIMAL),
total_weighted_time = ROUND(nvl(l_total_wt2, 0), G_DECIMAL),
machines_needed = ROUND(nvl(l_machines_needed2, 0), G_DECIMAL),
machines_assigned = ROUND(nvl(l_mc_assigned2, 0), G_DECIMAL),
labor_needed = ROUND(nvl(l_labor_needed2, 0), G_DECIMAL),
labor_assigned = ROUND(nvl(l_lb_assigned2, 0), G_DECIMAL),
takt_time = ROUND(nvl(l_takt2, 0), G_DECIMAL),
takt_time_for_assigned = ROUND(nvl(l_takt_time_for_assigned2, 0), G_DECIMAL),
in_process_kanban = ROUND(nvl(l_ipk2, 0), G_DECIMAL),
machines_needed_delta = ROUND(l_mn_delta2, G_DECIMAL),
labor_needed_delta = ROUND(l_ln_delta2, G_DECIMAL),
in_process_kanban_delta = ROUND(l_ipk_delta2, G_DECIMAL)
WHERE mixed_model_map_id = p_mmm_id
AND group_number = p_group_number
AND sequence_id = 2 ;
UPDATE bom_mixed_model_map_processes
SET machine_weighted_time = ROUND(nvl(l_machine_wt3, 0), G_DECIMAL),
labor_weighted_time = ROUND(nvl(l_labor_wt3, 0), G_DECIMAL),
total_weighted_time = ROUND(nvl(l_total_wt3, 0), G_DECIMAL),
machines_needed = ROUND(nvl(l_machines_needed3, 0), G_DECIMAL),
machines_assigned = ROUND(nvl(l_mc_assigned3, 0), G_DECIMAL),
labor_needed = ROUND(nvl(l_labor_needed3, 0), G_DECIMAL),
labor_assigned = ROUND(nvl(l_lb_assigned3, 0), G_DECIMAL),
takt_time = ROUND(nvl(l_takt3, 0), G_DECIMAL),
takt_time_for_assigned = ROUND(nvl(l_takt_time_for_assigned3, 0), G_DECIMAL),
in_process_kanban = ROUND(nvl(l_ipk3, 0), G_DECIMAL),
machines_needed_delta = ROUND(l_mn_delta3, G_DECIMAL),
labor_needed_delta = ROUND(l_ln_delta3, G_DECIMAL),
in_process_kanban_delta = ROUND(l_ipk_delta3, G_DECIMAL)
WHERE mixed_model_map_id = p_mmm_id
AND group_number = p_group_number
AND sequence_id = 3 ;
UPDATE bom_mixed_model_map_processes
SET machine_weighted_time = ROUND(nvl(l_machine_wt4, 0), G_DECIMAL),
labor_weighted_time = ROUND(nvl(l_labor_wt4, 0), G_DECIMAL),
total_weighted_time = ROUND(nvl(l_total_wt4, 0), G_DECIMAL),
machines_needed = ROUND(nvl(l_machines_needed4, 0), G_DECIMAL),
machines_assigned = ROUND(nvl(l_mc_assigned4, 0), G_DECIMAL),
labor_needed = ROUND(nvl(l_labor_needed4, 0), G_DECIMAL),
labor_assigned = ROUND(nvl(l_lb_assigned4, 0), G_DECIMAL),
takt_time = ROUND(nvl(l_takt4, 0), G_DECIMAL),
takt_time_for_assigned = ROUND(nvl(l_takt_time_for_assigned4, 0), G_DECIMAL),
in_process_kanban = ROUND(nvl(l_ipk4, 0), G_DECIMAL),
machines_needed_delta = ROUND(l_mn_delta4, G_DECIMAL),
labor_needed_delta = ROUND(l_ln_delta4, G_DECIMAL),
in_process_kanban_delta = ROUND(l_ipk_delta4, G_DECIMAL)
WHERE mixed_model_map_id = p_mmm_id
AND group_number = p_group_number
AND sequence_id = 4 ;
UPDATE bom_mixed_model_map_processes
SET machine_weighted_time = ROUND(nvl(l_machine_wt5, 0), G_DECIMAL),
labor_weighted_time = ROUND(nvl(l_labor_wt5, 0), G_DECIMAL),
total_weighted_time = ROUND(nvl(l_total_wt5, 0), G_DECIMAL),
machines_needed = ROUND(nvl(l_machines_needed5, 0), G_DECIMAL),
machines_assigned = ROUND(nvl(l_mc_assigned5, 0), G_DECIMAL),
labor_needed = ROUND(nvl(l_labor_needed5, 0), G_DECIMAL),
labor_assigned = ROUND(nvl(l_lb_assigned5, 0), G_DECIMAL),
takt_time = ROUND(nvl(l_takt5, 0), G_DECIMAL),
takt_time_for_assigned = ROUND(nvl(l_takt_time_for_assigned5, 0), G_DECIMAL),
in_process_kanban = ROUND(nvl(l_ipk5, 0), G_DECIMAL),
machines_needed_delta = ROUND(l_mn_delta5, G_DECIMAL),
labor_needed_delta = ROUND(l_ln_delta5, G_DECIMAL),
in_process_kanban_delta = ROUND(l_ipk_delta5, G_DECIMAL)
WHERE mixed_model_map_id = p_mmm_id
AND group_number = p_group_number
AND sequence_id = 5 ;