[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select mgq.row_index
from msc_gantt_query mgq
where query_id = p_query_id
and row_flag = sys_yes
order by 1;
select mrr.firm_flag, mrr.firm_Start_date, mrr.firm_end_date, mrr.batch_number,
mrr.group_sequence_id, mrr.group_sequence_number
from msc_resource_requirements mrr
where mrr.plan_id = p_plan
and mrr.sr_instancE_id = p_inst
and mrr.transaction_id = p_trx
and nvl(mrr.parent_id, 2) = 2;
select mrir.batch_number
from msc_resource_instance_reqs mrir
where mrir.plan_id = p_plan
and mrir.sr_instancE_id = p_inst
and mrir.res_inst_transaction_id = p_trx
and nvl(mrir.parent_id, 2) = 2;
select mrr2.transaction_id, mrr2.sr_instance_id
from msc_resource_requirements mrr1,
msc_resource_requirements mrr2
where mrr1.plan_id = p_plan
and mrr1.transaction_id = p_trx
and mrr1.sr_instance_id = p_inst
and mrr2.plan_id = mrr1.plan_id
and mrr2.sr_instance_id = mrr1.sr_instance_id
and mrr2.supply_id = mrr1.supply_id
and mrr2.operation_seq_num = mrr1.operation_seq_num
and mrr2.resource_seq_num = mrr1.resource_seq_num
and mrr2.alternate_num = mrr1.alternate_num
and mrr2.transaction_id <> mrr1.transaction_id
and mrr1.setup_id is null -- only non setup rows
and mrr1.schedule_flag = 1 -- only run req rows
and nvl(mrr2.parent_id,2) = 2;
select mdr.owning_department_id department_id,
mdri.res_instance_id,
mdri.serial_number
from msc_dept_res_instances mdri,
msc_department_resources mdr
where mdr.plan_id = p_plan
and mdr.sr_instance_id = p_inst
and mdr.organization_id = p_org
and mdr.department_id = p_dept
and mdr.resource_id = p_res
and mdri.plan_id = mdr.plan_id
and mdri.sr_instance_id = mdr.sr_instance_id
and mdri.organization_id = mdr.organization_id
and mdri.department_id = nvl(mdr.owning_department_id, mdr.department_id)
and mdri.resource_id = mdr.resource_id;
select distinct mdr.department_id department_id
from msc_dept_res_instances mdri,
msc_department_resources mdr
where mdr.plan_id = p_plan
and mdr.sr_instance_id = p_inst
and mdr.organization_id = p_org
and mdr.department_id = p_dept
and mdr.resource_id = p_res;
select msi.item_name,
nvl(decode(mrr.setup_id, to_number(null), null,
msc_gantt_utils.getSetupCode(mrr.plan_id,
mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)), null) setup_type,
mtp.organization_code org_code,
ms.new_order_quantity qty,
mrr.batch_number,
msc_get_name.alternate_rtg(mrr.plan_id, mrr.sr_instance_id, mrr.routing_sequence_id) alt_rtg,
mrr.assigned_units,
msc_get_name.lookup_meaning('MRP_ORDER_TYPE', ms.order_type) order_type,
mro.operation_description op_sdesc,
mrr.operation_seq_num,
mrr.resource_seq_num,
ms.requested_completion_date req_comp_date,
msc_get_name.supply_order_number(ms.order_type, ms.order_number, ms.plan_id,
ms.sr_instance_id, ms.transaction_id, ms.disposition_id) order_number,
mdr.department_code,
mdr.resource_code
from msc_resource_requirements mrr,
msc_supplies ms,
msc_system_items msi,
msc_department_resources mdr,
msc_routing_operations mro,
msc_trading_partners mtp
where mrr.plan_id = ms.plan_id
and mrr.sr_instance_id = ms.sr_instance_id
and mrr.supply_id = ms.transaction_id
and ms.plan_id = msi.plan_id
and ms.sr_instance_id = msi.sr_instance_id
and ms.organization_id = msi.organization_id
and ms.inventory_item_id = msi.inventory_item_id
and ms.sr_instance_id = mtp.sr_instance_id
and ms.organization_id = mtp.sr_tp_id
and mtp.partner_type = 3
and mdr.plan_id = mrr.plan_id
and mdr.organization_id = mrr.organization_id
and mdr.sr_instance_id = mrr.sr_instance_id
and mdr.department_id = mrr.department_id
and mdr.resource_id = mrr.resource_id
and mrr.plan_id = mro.plan_id (+)
and mrr.sr_instance_id = mro.sr_instance_id (+)
and mrr.routing_sequence_id = mro.routing_sequence_id (+)
and mrr.operation_sequence_id = mro.operation_sequence_id (+)
and mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.transaction_id = p_trx_id;
select mrir.res_inst_transaction_id
from msc_resource_requirements mrr,
msc_resource_instance_reqs mrir
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mrr.plan_id = mrir.plan_id
and mrr.sr_instance_id = mrir.sr_instance_id
and mrr.organization_id = mrir.organization_id
and mrr.department_id = mrir.department_id
and mrr.resource_id = mrir.resource_id
and mrr.supply_id = mrir.supply_id
and mrr.operation_seq_num = mrir.operation_seq_num
and mrr.resource_seq_num = mrir.resource_seq_num
and nvl(mrr.orig_resource_seq_num, mbp_null_value) = nvl(mrir.orig_resource_seq_num, mbp_null_value)
and nvl(mrr.parent_seq_num, mbp_null_value) = nvl(mrir.parent_seq_num, mbp_null_value)
and nvl(mrr.parent_id, mbp_null_value) = nvl(mrir.parent_id, mbp_null_value)
and nvl(mrr.firm_start_date, mrr.start_date) = mrir.start_date
and nvl(mrr.firm_end_date, mrr.end_date) = mrir.end_date
and nvl(mrir.parent_id,2) = 2;
select mrr.transaction_id
from msc_resource_requirements mrr,
msc_resource_instance_reqs mrir
where mrir.plan_id = p_plan
and mrir.sr_instance_id = p_inst
and mrir.res_inst_transaction_id = p_trx
and mrir.plan_id = mrr.plan_id
and mrir.sr_instance_id = mrr.sr_instance_id
and mrir.organization_id = mrr.organization_id
and mrir.department_id = mrr.department_id
and mrir.resource_id = mrr.resource_id
and mrir.supply_id = mrr.supply_id
and mrir.operation_seq_num = mrr.operation_seq_num
and mrir.resource_seq_num = mrr.resource_seq_num
and nvl(mrir.orig_resource_seq_num, mbp_null_value) = nvl(mrr.orig_resource_seq_num, mbp_null_value)
and nvl(mrir.parent_seq_num, mbp_null_value) = nvl(mrr.parent_seq_num, mbp_null_value)
and nvl(mrir.parent_id, mbp_null_value) = nvl(mrr.parent_id, mbp_null_value)
and mrir.start_date = nvl(mrr.firm_start_date, mrr.start_date)
and mrir.end_date = nvl(mrr.firm_end_date, mrr.end_date)
and nvl(mrr.parent_id,2) = 2;
insert into msc_gantt_query (query_id,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, row_index, node_path, node_type, transaction_id,
sr_instance_id, organization_id, department_id, resource_id, critical_flag,
op_seq_id, op_seq_num, op_desc, parent_link, node_level )
values (p_query_id,
trunc(sysdate), -1, trunc(sysdate), -1, -1,
p_index, p_node_path, p_node_type, p_transaction_id, p_inst_id, p_org_id,
p_dept_id, p_res_id, p_critical_flag, p_op_seq_id, p_op_seq_num, p_op_desc,
p_parent_link, p_node_level);
insert into msc_form_query (query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number1)
select
p_query_id,
sysdate, -1, sysdate, -1,-1,
mfp.end_pegging_id
from msc_full_pegging mfp
where mfp.plan_id = p_plan_id
and mfp.demand_id = p_end_demand_id;
select distinct
ms.sr_instance_id,
ms.organization_id,
ms.transaction_id,
decode(ms.order_type,
14, COPROD_NODE,
15, COPROD_NODE,
16, COPROD_NODE,
17, COPROD_NODE,
28, COPROD_NODE,
JOB_NODE) nodetype
from msc_form_query mfq,
msc_supplies ms
where mfq.query_id = p_mfq_query_id
and mfq.NUMBER2 = ms.sr_instance_id
and mfq.NUMBER1 = ms.transaction_id
and ms.plan_id = p_plan_id
union all
select distinct
md.sr_instance_id,
md.organization_id,
md.demand_id,
END_DEMAND_NODE nodetype
from msc_form_query mfq,
msc_demands md
where mfq.query_id = p_mfq_query_id
and mfq.NUMBER2 = md.sr_instance_id
and mfq.NUMBER1 = md.demand_id
and md.plan_id = p_plan_id;
insert into msc_gantt_query (
query_id, last_update_date, last_updated_by, creation_date, created_by,
last_update_login, row_index, node_level, node_path, node_type,
sr_instance_id, organization_id, transaction_id )
values (
p_query_id, trunc(sysdate), -1, trunc(sysdate), -1, -1,
l_row_index, 0, l_row_index, l_node_type, l_inst_id, l_org_id, l_trx_id );
insert into msc_form_query
(query_id, last_update_date, last_updated_by, creation_date, created_by,
last_update_login, number1, number2)
values
( p_query_id, sysdate, -1, sysdate, -1, -1, v_transaction_id, v_instance_id);
insert into msc_gantt_query ( query_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
res_instance_id, serial_number, is_fetched )
select p_query_id,
sysdate, -1, sysdate, -1,-1,
row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
res_instance_id, serial_number, sys_no
from msc_gantt_query
where query_id = to_number(p_list);
insert into msc_gantt_query ( query_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
res_instance_id, serial_number, is_fetched )
values ( p_query_id,
sysdate, -1, sysdate, -1,-1,
p_row_index, p_plan_id, v_inst_id, v_org_id, v_dept_id, v_res_id,
nvl(v_res_instance_id,mbp_null_value), nvl(v_serial_number,mbp_null_value_char), sys_no );
insert into msc_gantt_query ( query_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
res_instance_id, serial_number, is_fetched )
values ( p_query_id,
sysdate, -1, sysdate, -1,-1,
p_row_index, p_plan_id, v_inst_id, v_org_id, c_res_inst.department_id, v_res_id,
nvl(c_res_inst.res_instance_id,mbp_null_value),
nvl(c_res_inst.serial_number,mbp_null_value_char), sys_no );
insert into msc_gantt_query ( query_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
res_instance_id, serial_number, is_fetched )
values ( p_query_id,
sysdate, -1, sysdate, -1,-1,
p_row_index, p_plan_id, v_inst_id, v_org_id, c_inst_res.department_id, v_res_id,
mbp_null_value, mbp_null_value_char, sys_no );
insert into msc_gantt_query ( query_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
row_index, plan_id, sr_instance_id, organization_id, department_id, resource_id,
res_instance_id, serial_number, is_fetched )
values ( p_query_id,
sysdate, -1, sysdate, -1,-1,
p_row_index, p_plan_id, v_inst_id, v_org_id, v_dept_id, v_res_id,
nvl(v_res_instance_id,mbp_null_value), nvl(v_serial_number,mbp_null_value_char), sys_no );
select number1, number2, number3, number4, number5, char1
bulk collect into v_inst_id, v_org_id, v_dept_id, v_res_id,
v_res_instance_id, v_serial_number
from msc_form_query
where query_id = to_number(p_list);
insert into msc_form_query ( query_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
number1, number2, char1, char9, number3)
values ( p_query_id,
sysdate, -1, sysdate, -1,-1,
p_res_id, p_res_instance_id, p_serial_number, p_res_name, p_alt_number);
select bom_item_type, base_item_id
from msc_system_items
where plan_id = p_plan_id
and inventory_item_id = p_item_id;
insert into msc_gantt_query (
query_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
row_index, plan_id, inventory_item_id, supplier_id,
supplier_site_id, is_fetched, dependency_type, department_id)
values (
p_query_id,
sysdate, -1, sysdate, -1,-1,
p_row_index, p_plan_id, v_item_id, v_supplier_id,
nvl(v_supplier_site_id, -23453), sys_no, v_bom_item_type, v_base_item_id);
select number1, number2, number3, number4, number5
bulk collect into v_inst_id, v_org_id, v_item_id, v_supplier_id, v_supplier_site_id
from msc_form_query
where query_id = to_number(p_list);
insert into msc_gantt_dtl_query ( query_id, row_type, row_index, parent_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
start_date, end_date, resource_units, resource_hours, schedule_flag, display_flag)
values ( p_query_id, p_row_type, p_row_index, p_detail_type,
sysdate, -1, sysdate, -1,-1,
p_start_date, p_end_date, p_resource_units, p_resource_hours, p_schedule_flag, p_display_type);
insert into msc_gantt_dtl_query (
query_id, row_type, row_index, parent_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
start_date, end_date, resource_units, resource_hours, schedule_flag,
display_flag)
values (
p_query_id, p_row_type, p_row_index(i), p_detail_type,
sysdate, -1, sysdate, -1,-1,
p_start_date(i), p_end_date(i), l_res_units, l_res_hours,
p_schedule_flag(i), l_display_type );
insert into msc_gantt_dtl_query ( query_id, row_type, row_index, parent_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
start_date, supp_avail_qty, supp_overload_qty, supp_consume_qty)
values ( p_query_id, p_row_type, p_row_index, p_detail_type,
sysdate, -1, sysdate, -1,-1,
p_start_date, p_avail_qty, p_overload_qty, p_consume_qty);
insert into msc_gantt_dtl_query (
query_id, row_type, row_index, parent_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
start_date, resource_units, resource_hours)
values (
p_query_id, p_row_type, p_row_index(i), p_detail_type,
sysdate, -1, sysdate, -1,-1,
p_start_date(i), p_units(i), l_consumed_qty);
insert into msc_gantt_dtl_query (
query_id, row_type, row_index, parent_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
sr_instance_id, organization_id, supply_id, transaction_id, status, applied,
res_firm_flag, sup_firm_flag, start_date, end_date, schedule_flag, late_flag,
supply_qty, batch_number, resource_units, group_sequence_id, group_sequence_number,
cepst, cepct, ulpst, ulpct, uepst, uepct, eact, inventory_item_id, bar_label,
order_number, op_seq_num, resource_seq_num, resource_description, item_name, assembly_item_desc, schedule_qty,
from_setup_code, to_setup_code, std_op_code, changeover_time, changeover_penalty, supp_avail_qty)
values (
p_query_id, p_row_type, p_row_index(i), p_detail_type,
sysdate, -1, sysdate, -1,-1,
p_sr_instance_id(i), p_organization_id(i), p_supply_id(i), p_transaction_id(i),
p_status(i), p_applied(i), p_res_firm_flag(i), p_sup_firm_flag(i),
p_start_date(i), p_end_date(i), p_schedule_flag(i), p_res_constraint(i),
p_qty(i), p_batch_number(i), p_resource_units(i), p_group_sequence_id(i),
p_group_sequence_number(i), p_cepst(i), p_cepct(i), p_ulpst(i), p_ulpct(i),
p_uepst(i), p_uepct(i), p_eact(i), p_item_id(i), p_bar_text(i),
p_order_number(i), p_op_seq(i), p_res_seq(i), p_res_desc(i), p_item_name(i),
p_assy_item_desc(i), p_schedule_qty(i),
p_from_setup_code(i), p_to_setup_code(i), p_std_op_code(i), p_changeover_time(i),
p_changeover_penalty(i), p_overload_flag(i));
insert into msc_gantt_dtl_query (
query_id, row_type, row_index, parent_id,
last_update_date, last_updated_by, creation_date, created_by, last_update_login,
sr_instance_id, organization_id, start_date, end_date, schedule_flag,
batch_number, supply_qty, bar_label,
resource_description, min_capacity, max_capacity, capacity_used)
values (
p_query_id, p_row_type, p_row_index(i), p_detail_type,
sysdate, -1, sysdate, -1,-1,
p_sr_instance_id(i), p_organization_id(i),
p_start_date(i), p_end_date(i), p_schedule_flag(i),
p_batch_number(i), p_qty(i), p_bar_text(i),
l_res_desc, l_min_capacity, l_max_capacity, l_capacity_used);
select setup_code
into l_setup_code
from msc_resource_setups
where plan_id = p_plan_id
and sr_instance_id = p_inst_id
and resource_id = p_resource_id
and setup_id = p_setup_id;
select mro.MINIMUM_TRANSFER_QUANTITY,
mrr.cummulative_quantity, ms.new_order_quantity
into l_mtq,l_cumm_quan, l_order_quan
from msc_routing_operations mro,
msc_resource_requirements mrr,
msc_supplies ms
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_instance_id
and mrr.transaction_id = p_transaction_id
and mro.routing_sequence_id = mrr.routing_sequence_id
and mro.operation_sequence_id = mrr.operation_sequence_id
and mro.plan_id = p_plan_id
and mro.sr_instance_id = p_instance_id
and ms.plan_id = p_plan_id
and ms.sr_instance_id = p_instance_id
and ms.transaction_id = mrr.supply_id;
select 'EXISTS'
from msc_exception_details
where number1 = p_transaction_id
and sr_instance_id = p_instance_id
and plan_id = p_plan_id
and exception_type = 53
and organization_id = p_organization_id
--and inventory_item_id = p_inventory_item_id
and department_id = p_department_id
and resource_id = p_resource_id;
select 'EXISTS'
from msc_exception_details
where number1 = p_transaction_id
and sr_instance_id = p_instance_id
and plan_id = p_plan_id
and exception_type =36
and organization_id = p_organization_id
--and inventory_item_id = p_inventory_item_id
and department_id = p_department_id
and resource_id = p_resource_id;
select 'EXISTS'
from msc_exception_details
where plan_id = p_plan_id
and sr_instance_id = p_instance_id
and organization_id = p_organization_id
and department_id = p_department_id
and resource_id = p_resource_id
and exception_type = p_exception_type;
select getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
msi.planning_time_fence_date
into l_prev_start_date, l_timefence_date
from msc_system_items msi,
msc_resource_requirements mrr,
msc_supplies ms
where mrr.plan_id = p_plan_id
and mrr.transaction_id = p_transaction_id
and mrr.sr_instance_id = p_instance_id
and ms.plan_id = mrr.plan_id
and ms.transaction_id = mrr.supply_id
and ms.sr_instance_id = mrr.sr_instance_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id;
select nvl(mdr.batchable_flag, 2)
into v_flag
from msc_resource_requirements mrr,
msc_department_resources mdr
where mrr.plan_id = p_plan_id
and mrr.transaction_id = p_transaction_id
and mrr.sr_instance_id = p_instance_id
AND mdr.plan_id = mrr.plan_id
AND mdr.organization_id = mrr.organization_id
AND mdr.sr_instance_id = mrr.sr_instance_id
AND mdr.department_id = mrr.department_id
AND mdr.resource_id = mrr.resource_id;
select nvl(batchable_flag, SYS_NO)
into l_flag
from msc_department_resources
where plan_id = p_plan_id
and sr_instance_id = p_inst_id
and organization_id = p_org_id
and department_id = p_dept_id
and resource_id = p_res_id;
select sys_yes
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_instance_id
and mrr.organization_id = p_organization_id
and mrr.department_id = p_department_id
and mrr.resource_id = p_resource_id
and mrr.parent_id = 2
and batch_number is null
and rownum = 1;
select sys_yes
from msc_gantt_query mgq,
msc_department_resources mdr
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and g_plan_id = mdr.plan_id
and mgq.sr_instance_id = mdr.sr_instance_id
and mgq.organization_id = mdr.organization_id
and mgq.department_id = mdr.department_id
and mgq.resource_id = mdr.resource_id
and ( ( p_res_instance_id = sys_yes and mgq.res_instance_id <> mbp_null_value )
or ( p_res_instance_id = sys_no and mgq.res_instance_id = mbp_null_value ) )
and ( nvl(mdr.batchable_flag, sys_no) = p_batchable or nvl(p_ignore_batch_flag,2) = sys_yes)
and rownum = 1;
select count(*)
into l_temp
from msc_gantt_query mgq,
msc_department_resources mdr
where mgq.query_id = p_query_id
and mgq.row_flag = sys_yes
and g_plan_id = mdr.plan_id
and mgq.sr_instance_id = mdr.sr_instance_id
and mgq.organization_id = mdr.organization_id
and mgq.department_id = mdr.department_id
and mgq.resource_id = mdr.resource_id
and mgq.res_instance_id = mbp_null_value
and ( ( nvl(mdr.batchable_flag, sys_no) = sys_no and p_batch_flag is null)
or (p_batch_flag = sys_no)
or isBtchResWithoutBatch(mdr.plan_id, mdr.sr_instance_id, mdr.organization_id,
mdr.department_id, mdr.resource_id, to_number(null)) = sys_yes );
select count(*)
into l_temp
from msc_gantt_query mgq,
msc_department_resources mdr
where mgq.query_id = p_query_id
and mgq.row_flag = sys_yes
and g_plan_id = mdr.plan_id
and mgq.sr_instance_id = mdr.sr_instance_id
and mgq.organization_id = mdr.organization_id
and mgq.department_id = mdr.department_id
and mgq.resource_id = mdr.resource_id
and mgq.res_instance_id <> mbp_null_value
and ( ( nvl(mdr.batchable_flag, sys_no) = sys_no and p_batch_flag is null)
or (p_batch_flag = sys_no)
or isBtchResWithoutBatch(mdr.plan_id, mdr.sr_instance_id, mdr.organization_id,
mdr.department_id, mdr.resource_id, to_number(null)) = sys_yes );
select count(*)
into l_temp
from msc_gantt_query mgq,
msc_department_resources mdr
where mgq.query_id = p_query_id
and mgq.row_flag = sys_yes
and g_plan_id = mdr.plan_id
and mgq.sr_instance_id = mdr.sr_instance_id
and mgq.organization_id = mdr.organization_id
and mgq.department_id = mdr.department_id
and mgq.resource_id = mdr.resource_id
and mgq.res_instance_id = mbp_null_value
and nvl(mdr.batchable_flag, sys_no) = sys_yes;
select count(*)
into l_temp
from msc_gantt_query mgq,
msc_department_resources mdr
where mgq.query_id = p_query_id
and mgq.row_flag = sys_yes
and g_plan_id = mdr.plan_id
and mgq.sr_instance_id = mdr.sr_instance_id
and mgq.organization_id = mdr.organization_id
and mgq.department_id = mdr.department_id
and mgq.resource_id = mdr.resource_id
and mgq.res_instance_id <> mbp_null_value
and nvl(mdr.batchable_flag, sys_no) = sys_yes;
select count(*)
from msc_gantt_query mgq
where mgq.query_id = p_query
and mgq.plan_id = p_plan
and mgq.sr_instance_id = p_inst
and mgq.organization_id = p_org
and mgq.department_id = p_dept
and mgq.resource_id = p_res
and nvl(mgq.res_instance_id, MBP_NULL_VALUE) = nvl(p_res_inst, MBP_NULL_VALUE)
and nvl(mgq.serial_number, MBP_NULL_VALUE_CHAR) = nvl(p_serial_num, MBP_NULL_VALUE_CHAR)
and rownum = 1;
select decode(p_order_type,
5, decode(p_order_number, null, to_char(p_trx_id),
p_order_number|| null_space ||to_char(p_trx_id)),
14, decode(substr(msc_get_name.get_order_number(p_inst_id, p_plan_id,
p_disposition_id, 1),1,240), null, to_char(p_disposition_id),
substr(msc_get_name.get_order_number(p_inst_id, p_plan_id,
p_disposition_id, 1),1,240)
|| null_space || to_char(p_disposition_id)),
17, decode(substr(msc_get_name.get_order_number(p_inst_id, p_plan_id,
p_disposition_id, 1),1,240), null, to_char(p_disposition_id),
substr(msc_get_name.get_order_number(p_inst_id, p_plan_id,
p_disposition_id, 1),1,240) || null_space || to_char(p_disposition_id)),
51,to_char(p_trx_id),
52,to_char(p_trx_id),
15,to_char(p_trx_id),
16,to_char(p_trx_id),
28,to_char(p_trx_id),
p_order_number)
from dual;*/
select mtp.organization_code
||':'||mdr.department_code || ':' || mdr.resource_code
from msc_department_resources mdr,
msc_trading_partners mtp
where mdr.department_id = p_dept_id
and mdr.resource_id = p_res_id
and mdr.plan_id = p_plan_id
and mdr.organization_id = p_org_id
and mdr.sr_instance_id = p_instance_id
and mtp.partner_type =3
and mtp.sr_tp_id = mdr.organization_id
and mtp.sr_instance_id = mdr.sr_instance_id;
select msc_get_name.item_name(mdri.equipment_item_id, null, null, null)
||decode(mdri.serial_number, null, null_space, COLON_SEPARATOR || mdri.serial_number)
from msc_dept_res_instances mdri,
msc_department_resources mdr,
msc_trading_partners mtp
where mdr.plan_id = p_plan_id
and mdr.sr_instance_id = p_instance_id
and mdr.organization_id = p_org_id
and mdr.department_id = p_dept_id
and mdr.resource_id = p_res_id
and mdri.plan_id = mdr.plan_id
and mdri.sr_instance_id = mdr.sr_instance_id
and mdri.organization_id = mdr.organization_id
and mdri.department_id = nvl(mdr.owning_department_id, mdr.department_id)
and mdri.resource_id = mdr.resource_id
and mdri.res_instance_id = p_res_instance_id
and nvl(mdri.serial_number, MBP_NULL_VALUE_CHAR) = nvl(p_serial_number, MBP_NULL_VALUE_CHAR)
and mtp.partner_type = 3
and mtp.sr_tp_id = mdr.organization_id
and mtp.sr_instance_id = mdr.sr_instance_id;
select mdr.resource_description,
nvl(mrr.minimum_capacity, mdr.min_capacity),
nvl(mrr.maximum_capacity, mdr.max_capacity),
mrr.capacity_consumed_ratio * nvl(mrr.maximum_capacity, mdr.max_capacity) capacity_used
from msc_resource_requirements mrr,
msc_department_resources mdr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_instance_id
and mrr.batch_number = p_batch_number
and mdr.plan_id = mrr.plan_id
and mdr.organization_id = mrr.organization_id
and mdr.sr_instance_id = mrr.sr_instance_id
and mdr.department_id = mrr.department_id
and mdr.resource_id = mrr.resource_id;
select
mst.transition_time,
mst.transition_uom,
mst.transition_penalty,
mst.standard_operation_code
from
msc_setup_transitions mst
where mst.plan_id = p_plan_id
and mst.sr_instance_id = p_instance_id
and mst.resource_id = p_res_id
and mst.organization_id = p_org_id
and mst.from_setup_id = p_from_setup_id
and mst.to_setup_id = p_to_setup_id;
select min(md.demand_priority)
from msc_demands md,
msc_full_pegging mfp2,
msc_full_pegging mfp1
where mfp1.plan_id = p_plan_id
and mfp1.transaction_id = p_transaction_id
and mfp1.sr_instance_id = p_instance_id
and mfp2.pegging_id = mfp1.end_pegging_id
and mfp2.plan_id = mfp1.plan_id
and mfp2.sr_instance_id = mfp1.sr_instance_id
and md.plan_id = mfp2.plan_id
and md.sr_instance_id = mfp2.sr_instance_id
and md.demand_id = mfp2.demand_id
and md.demand_priority is not null;
select chargeable_flag
from msc_department_resources mdr
where mdr.plan_id = p_plan_id
and mdr.sr_instance_id = p_instance_id
and mdr.organization_id = p_org_id
and mdr.department_id = p_dept_id
and mdr.resource_id = p_res_id;
select 1
from msc_exception_details
where number1 = p_transaction_id
and sr_instance_id = p_instance_id
and plan_id = p_plan_id
and exception_type =36
and organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id;
select nvl(path_number,1)
from msc_critical_paths
where plan_id = p_plan_id
and sr_instance_id = p_inst_id
and supply_id = p_transaction_id
-- and routing_sequence_id is null
and demand_id = p_end_demand_id;
select nvl(path_number,1)
from msc_critical_paths
where plan_id = p_plan_id
and supply_id = p_transaction_id
and sr_instance_id = p_inst_id
and demand_id = p_end_demand_id
and nvl(routing_sequence_id,-1) = nvl(p_routing_seq_id,-1)
and operation_sequence_id = p_operation_seq_id;
select curr_start_date, curr_cutoff_date,
nvl(min_cutoff_bucket,0)+nvl(hour_cutoff_bucket,0)+data_start_date daybkt_start,
nvl(min_cutoff_bucket,0)+data_start_date hrbkt_start,
decode(nvl(hour_cutoff_bucket,0),0, to_date(null),data_start_date) minbkt_start,
sr_instance_id,
organization_id
from msc_plans
where plan_id = p_plan_id;
select min(mpb.bkt_start_date), max(mpb.bkt_end_date)
from msc_plan_buckets mpb,
msc_plans mp
where mp.plan_id =p_plan_id
and mpb.plan_id = mp.plan_id
and mpb.organization_id = mp.organization_id
and mpb.sr_instance_id = mp.sr_instance_id
and mpb.bucket_type =1;
select max(mpb.bkt_end_date)
from msc_plan_buckets mpb,
msc_plans mp
where mp.plan_id =p_plan_id
and mpb.plan_id = mp.plan_id
and mpb.organization_id = mp.organization_id
and mpb.sr_instance_id = mp.sr_instance_id
and mpb.bucket_type = p_bkt_type;
select mtp.calendar_code, mtp.sr_instance_id,
mtp.calendar_exception_set_id, plan_type
from msc_trading_partners mtp,
msc_plans mp
where mp.plan_id = p_plan_id
and mp.sr_instance_id = mtp.sr_instance_id
and mp.organization_id = mtp.sr_tp_id
and mtp.partner_type = 3;
select organization_id,
nvl(curr_frozen_horizon_days, frozen_horizon_days) frozen_days
from msc_plan_organizations
where plan_id = p_plan_id
and nvl(curr_ds_enabled_flag, ds_enabled_flag) = 1;
select greatest(mpsd.period_start_date, mp.data_start_date)
bulk collect into v_date
from msc_trading_partners tp,
msc_period_start_dates mpsd,
msc_plans mp
where mpsd.calendar_code = tp.calendar_code
and mpsd.sr_instance_id = tp.sr_instance_id
and mpsd.exception_set_id = tp.calendar_exception_set_id
and tp.sr_instance_id = mp.sr_instance_id
and tp.sr_tp_id = mp.organization_id
and tp.partner_type =3
and mp.plan_id = p_plan_id
and (mpsd.period_start_date
between mp.data_start_date and mp.curr_cutoff_date
or mpsd.next_date between mp.data_start_date and mp.curr_cutoff_date)
order by mpsd.period_start_date;
select nvl(min_cutoff_bucket, 0),
nvl(hour_cutoff_bucket, 0),
nvl(daily_cutoff_bucket, 0)
into v_min_day, v_hour_day, v_date_day
from msc_plans
where plan_id = p_plan_id;
oneBigRecord.delete;
oneBigRecord.delete;
oneBigRecord.delete;
select mgq.row_index,
mrr.shift_date + mrr.from_time/86400 start_date,
decode(sign(mrr.to_time - mrr.from_time), 1,
mrr.shift_date + mrr.to_time/86400,
mrr.shift_date + 1 + mrr.to_time/86400) end_date,
(decode(sign(to_time - from_time), 1,
shift_date + to_time/86400,
shift_date + 1 + to_time/86400) -
(shift_date + from_time/86400)) * 24 * capacity_units res_hours,
mrr.capacity_units,
to_number(null) schdule_flag
bulk collect into v_row_index, v_avail_start, v_avail_end,
v_resource_hours, v_resource_units, v_schdule_flag
from msc_net_resource_avail mrr,
msc_gantt_query mgq
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id = MBP_NULL_VALUE
and mrr.plan_id = mgq.plan_id
and mrr.sr_instance_id = mgq.sr_instance_id
and mrr.organization_id = mgq.organization_id
and mrr.department_id = mgq.department_id
and mrr.resource_id = mgq.resource_id
and nvl(mrr.parent_id,0) <> -1
and mrr.capacity_units > 0
and mrr.shift_date between p_start_date and p_end_date;
select mgq.row_index,
mrr.shift_date + mrr.from_time/86400 start_date,
decode(sign(mrr.to_time - mrr.from_time), 1,
mrr.shift_date + mrr.to_time/86400,
mrr.shift_date + 1 + mrr.to_time/86400) end_date,
(decode(sign(to_time - from_time), 1,
shift_date + to_time/86400,
shift_date + 1 + to_time/86400) - (shift_date + from_time/86400)) * 24 res_hours,
1 capacity_units,
to_number(null) schdule_flag
bulk collect into v_row_index, v_avail_start, v_avail_end,
v_resource_hours, v_resource_units, v_schdule_flag
from msc_net_res_inst_avail mrr,
msc_gantt_query mgq
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id <> MBP_NULL_VALUE
and mrr.plan_id = mgq.plan_id
and mrr.sr_instance_id = mgq.sr_instance_id
and mrr.organization_id = mgq.organization_id
and mrr.department_id = mgq.department_id
and mrr.resource_id = mgq.resource_id
and mrr.res_instance_id = mgq.res_instance_id
and nvl(mrr.serial_number, MBP_NULL_VALUE_CHAR) = nvl(mgq.serial_number, MBP_NULL_VALUE_CHAR)
and nvl(mrr.parent_id,0) <> -1
and nvl(mrr.capacity_units,1) > 0
and mrr.shift_date between p_start_date and p_end_date;
select mrr.ulpsd
from msc_resource_requirements mrr
where mrr.plan_id = p_plan
and mrr.sr_instancE_id = p_inst
and mrr.organization_id = p_org
and mrr.department_id = p_dept
and mrr.resource_id = p_res
and mrr.supply_id = p_supply
and mrr.operation_seq_num = p_op_seq
and mrr.resource_seq_num = p_res_seq
and nvl(mrr.orig_resource_seq_num, mbp_null_value) = nvl(p_orig_res_seq, mbp_null_value)
and nvl(mrr.parent_seq_num, mbp_null_value) = nvl(p_parent_seq, mbp_null_value)
and nvl(parent_id,2) = 2;
select mgq.row_index,
msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
mrr.assigned_units,
mrr.resource_hours,
mrr.batch_number,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
msc_gantt_utils.getDisplayType(p_display_type,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
mrr.orig_resource_seq_num, mrr.parent_seq_num),
mrr.firm_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id,
mrr.sr_instance_id, mrr.organization_id,
ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
g_gantt_rh_toler_days_early,
g_gantt_rh_toler_days_late) display_type
from msc_gantt_query mgq,
msc_resource_requirements mrr,
msc_supplies ms
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id = MBP_NULL_VALUE
and mrr.plan_id = mgq.plan_id
and mrr.sr_instance_id = mgq.sr_instance_id
and mrr.organization_id = mgq.organization_id
and mrr.department_id = mgq.department_id
and mrr.resource_id = mgq.resource_id
and mrr.plan_id = ms.plan_id
and mrr.sr_instance_id = ms.sr_instance_id
and mrr.supply_id = ms.transaction_id
and mrr.batch_number is null and mrr.end_date is not null and mrr.resource_hours > 0
and nvl(mrr.parent_id, 2) = 2
and nvl(mrr.status,-1) = 0
and nvl(mrr.applied,-1) = 2
and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date
)
or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date )
or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
)
union all
select mgq.row_index,
msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
mrr.assigned_units,
mrr.resource_hours,
mrr.batch_number,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
msc_gantt_utils.getDisplayType(p_display_type,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
mrr.orig_resource_seq_num, mrr.parent_seq_num),
mrr.firm_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id,
mrr.sr_instance_id, mrr.organization_id,
ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
g_gantt_rh_toler_days_early,
g_gantt_rh_toler_days_late) display_type
from msc_gantt_query mgq,
msc_resource_requirements mrr,
msc_resource_requirements mrr1,
msc_supplies ms
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id = MBP_NULL_VALUE
and mrr.plan_id = mgq.plan_id
and mrr.sr_instance_id = mgq.sr_instance_id
and mrr.organization_id = mgq.organization_id
and mrr.department_id = mgq.department_id
and mrr.resource_id = mgq.resource_id
and mrr.plan_id = ms.plan_id
and mrr.sr_instance_id = ms.sr_instance_id
and mrr.supply_id = ms.transaction_id
and mrr.plan_id = mrr1.plan_id
and mrr.sr_instancE_id = mrr1.sr_instancE_id
and mrr.organization_id = mrr1.organization_id
and mrr.department_id = mrr1.department_id
and mrr.resource_id = mrr1.resource_id
and mrr.supply_id = mrr1.supply_id
and mrr.operation_seq_num = mrr1.operation_seq_num
and mrr.resource_seq_num = mrr1.resource_seq_num
and nvl(mrr.orig_resource_seq_num, mbp_null_value) = nvl(mrr1.orig_resource_seq_num, mbp_null_value)
and nvl(mrr.parent_seq_num, mbp_null_value) = nvl(mrr1.parent_seq_num, mbp_null_value)
and mrr.batch_number is null and mrr.end_date is not null and mrr.resource_hours > 0
and mrr1.batch_number is null and mrr1.end_date is not null and mrr1.resource_hours > 0
and nvl(mrr1.parent_id, 2) = 2
and nvl(mrr1.status,-1) <> 0
and nvl(mrr1.applied,-1) <> 2
and nvl(mrr.parent_id,-1) = 1
and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date
)
or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date )
or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
) ;
select mgq.row_index,
msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
1 assigned_units,
mrir.resource_instance_hours,
mrr.batch_number,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
msc_gantt_utils.getDisplayType(p_display_type,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
mrr.orig_resource_seq_num, mrr.parent_seq_num),
mrr.firm_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id,
mrr.sr_instance_id, mrr.organization_id,
ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
g_gantt_rh_toler_days_early,
g_gantt_rh_toler_days_late) display_type
from msc_gantt_query mgq,
msc_resource_requirements mrr,
msc_resource_instance_reqs mrir,
msc_supplies ms
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id <> MBP_NULL_VALUE
and mrir.plan_id = mgq.plan_id
and mrir.sr_instance_id = mgq.sr_instance_id
and mrir.organization_id = mgq.organization_id
and mrir.department_id = mgq.department_id
and mrir.resource_id = mgq.resource_id
and mrir.res_instance_id = mgq.res_instance_id
and nvl(mrir.serial_number, MBP_NULL_VALUE_CHAR) = nvl(mgq.serial_number, MBP_NULL_VALUE_CHAR)
and mrir.end_date is not null
and mrir.plan_id = mrr.plan_id
and mrir.sr_instance_id = mrr.sr_instance_id
and mrir.organization_id = mrr.organization_id
and mrir.department_id = mrr.department_id
and mrir.resource_id = mrr.resource_id
and mrir.supply_id = mrr.supply_id
and mrir.operation_seq_num = mrr.operation_seq_num
and mrir.resource_seq_num = mrr.resource_seq_num
and nvl(mrir.orig_resource_seq_num, mbp_null_value) = nvl(mrr.orig_resource_seq_num, mbp_null_value)
and nvl(mrir.parent_seq_num, mbp_null_value) = nvl(mrr.parent_seq_num, mbp_null_value)
and nvl(mrir.parent_id, mbp_null_value) = nvl(mrr.parent_id, mbp_null_value)
and mrir.start_date = nvl(mrr.firm_start_date, mrr.start_date)
and mrir.end_date = nvl(mrr.firm_end_date, mrr.end_date)
and mrr.plan_id = ms.plan_id
and mrr.sr_instance_id = ms.sr_instance_id
and mrr.supply_id = ms.transaction_id
and mrr.batch_number is null and mrr.end_date is not null and mrr.resource_hours > 0
and nvl(mrr.parent_id, 2) = 2
and nvl(mrr.status,-1) = 0
and nvl(mrr.applied,-1) = 2
and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date
)
or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date )
or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
)
union all
select mgq.row_index,
msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
1 assigned_units,
mrir.resource_instance_hours,
mrr.batch_number,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
msc_gantt_utils.getDisplayType(p_display_type,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
mrr.orig_resource_seq_num, mrr.parent_seq_num),
mrr.firm_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id,
mrr.sr_instance_id, mrr.organization_id,
ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
g_gantt_rh_toler_days_early,
g_gantt_rh_toler_days_late) display_type
from msc_gantt_query mgq,
msc_resource_requirements mrr,
msc_resource_requirements mrr1,
msc_resource_instance_reqs mrir,
msc_supplies ms
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id <> MBP_NULL_VALUE
and mrir.plan_id = mgq.plan_id
and mrir.sr_instance_id = mgq.sr_instance_id
and mrir.organization_id = mgq.organization_id
and mrir.department_id = mgq.department_id
and mrir.resource_id = mgq.resource_id
and mrir.res_instance_id = mgq.res_instance_id
and nvl(mrir.serial_number, MBP_NULL_VALUE_CHAR) = nvl(mgq.serial_number, MBP_NULL_VALUE_CHAR)
and mrir.end_date is not null
and mrir.plan_id = mrr.plan_id
and mrir.sr_instance_id = mrr.sr_instance_id
and mrir.organization_id = mrr.organization_id
and mrir.department_id = mrr.department_id
and mrir.resource_id = mrr.resource_id
and mrir.supply_id = mrr.supply_id
and mrir.operation_seq_num = mrr.operation_seq_num
and mrir.resource_seq_num = mrr.resource_seq_num
and nvl(mrir.orig_resource_seq_num, mbp_null_value) = nvl(mrr.orig_resource_seq_num, mbp_null_value)
and nvl(mrir.parent_seq_num, mbp_null_value) = nvl(mrr.parent_seq_num, mbp_null_value)
and nvl(mrir.parent_id, mbp_null_value) = nvl(mrr.parent_id, mbp_null_value)
and mrir.start_date = nvl(mrr.firm_start_date, mrr.start_date)
and mrir.end_date = nvl(mrr.firm_end_date, mrr.end_date)
and mrr.plan_id = ms.plan_id
and mrr.sr_instance_id = ms.sr_instance_id
and mrr.supply_id = ms.transaction_id
and mrr.plan_id = mrr1.plan_id
and mrr.sr_instancE_id = mrr1.sr_instancE_id
and mrr.organization_id = mrr1.organization_id
and mrr.department_id = mrr1.department_id
and mrr.resource_id = mrr1.resource_id
and mrr.supply_id = mrr1.supply_id
and mrr.operation_seq_num = mrr1.operation_seq_num
and mrr.resource_seq_num = mrr1.resource_seq_num
and nvl(mrr.orig_resource_seq_num, mbp_null_value) = nvl(mrr1.orig_resource_seq_num, mbp_null_value)
and nvl(mrr.parent_seq_num, mbp_null_value) = nvl(mrr1.parent_seq_num, mbp_null_value)
and mrr.batch_number is null and mrr.end_date is not null and mrr.resource_hours > 0
and mrr1.batch_number is null and mrr1.end_date is not null and mrr1.resource_hours > 0
and nvl(mrr1.parent_id, 2) = 2
and nvl(mrr1.status,-1) <> 0
and nvl(mrr1.applied,-1) <> 2
and nvl(mrr.parent_id,-1) = 1
and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date
)
or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date )
or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
);
select mgq.row_index,
min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
sum(mrr.capacity_consumed_ratio) assigned_units,
avg(mrr.resource_hours) resource_hours,
mrr.batch_number,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
decode(p_display_type, DISPLAY_NONE, sys_no,
msc_gantt_utils.getDisplayType(p_display_type,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
mrr.orig_resource_seq_num, mrr.parent_seq_num),
mrr.firm_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id,
mrr.sr_instance_id, mrr.organization_id,
ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
g_gantt_rh_toler_days_early,
g_gantt_rh_toler_days_late)) display_type
from msc_gantt_query mgq,
msc_resource_requirements mrr,
msc_resource_batches mrb,
msc_supplies ms
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id = MBP_NULL_VALUE
and mrr.plan_id = mgq.plan_id
and mrr.sr_instance_id = mgq.sr_instance_id
and mrr.organization_id = mgq.organization_id
and mrr.department_id = mgq.department_id
and mrr.resource_id = mgq.resource_id
and mrb.plan_id = mrr.plan_id
and mrb.sr_instance_id = mrr.sr_instance_id
and mrb.organization_id= mrr.organization_id
and mrb.department_id = mrr.department_id
and mrb.resource_id = mrr.resource_id
and mrb.batch_number = mrr.batch_number
and mrr.plan_id = ms.plan_id
and mrr.sr_instance_id = ms.sr_instance_id
and mrr.supply_id = ms.transaction_id
and mrr.batch_number is not null and mrr.end_date is not null and mrr.resource_hours > 0
and nvl(mrr.parent_id, 2) = 2
and nvl(mrr.status,-1) = 0
and nvl(mrr.applied,-1) = 2
and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date
)
or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date )
or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
)
group by
mgq.row_index,
mrr.batch_number,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
msc_gantt_utils.getDisplayType(p_display_type,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
mrr.orig_resource_seq_num, mrr.parent_seq_num),
mrr.firm_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id,
mrr.sr_instance_id, mrr.organization_id,
ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
g_gantt_rh_toler_days_early,
g_gantt_rh_toler_days_late)
union all
select mgq.row_index,
min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date,
mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
sum(mrr.capacity_consumed_ratio) assigned_units,
avg(mrr.resource_hours) resource_hours,
mrr.batch_number,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
decode(p_display_type, DISPLAY_NONE, sys_no,
msc_gantt_utils.getDisplayType(p_display_type,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
mrr.orig_resource_seq_num, mrr.parent_seq_num),
mrr.firm_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id,
mrr.sr_instance_id, mrr.organization_id,
ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
g_gantt_rh_toler_days_early,
g_gantt_rh_toler_days_late)) display_type
from msc_gantt_query mgq,
msc_resource_requirements mrr,
msc_resource_requirements mrr1,
msc_resource_batches mrb,
msc_supplies ms
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id = MBP_NULL_VALUE
and mrr.plan_id = mgq.plan_id
and mrr.sr_instance_id = mgq.sr_instance_id
and mrr.organization_id = mgq.organization_id
and mrr.department_id = mgq.department_id
and mrr.resource_id = mgq.resource_id
and mrb.plan_id = mrr.plan_id
and mrb.sr_instance_id = mrr.sr_instance_id
and mrb.organization_id= mrr.organization_id
and mrb.department_id = mrr.department_id
and mrb.resource_id = mrr.resource_id
and mrb.batch_number = mrr.batch_number
and mrr.plan_id = ms.plan_id
and mrr.sr_instance_id = ms.sr_instance_id
and mrr.supply_id = ms.transaction_id
and mrr.plan_id = mrr1.plan_id
and mrr.sr_instancE_id = mrr1.sr_instancE_id
and mrr.organization_id = mrr1.organization_id
and mrr.department_id = mrr1.department_id
and mrr.resource_id = mrr1.resource_id
and mrr.supply_id = mrr1.supply_id
and mrr.operation_seq_num = mrr1.operation_seq_num
and mrr.resource_seq_num = mrr1.resource_seq_num
and nvl(mrr.orig_resource_seq_num, mbp_null_value) = nvl(mrr1.orig_resource_seq_num, mbp_null_value)
and nvl(mrr.parent_seq_num, mbp_null_value) = nvl(mrr1.parent_seq_num, mbp_null_value)
and mrr.batch_number is not null and mrr.end_date is not null and mrr.resource_hours > 0
and mrr1.batch_number is not null and mrr1.end_date is not null and mrr1.resource_hours > 0
and nvl(mrr1.parent_id, 2) = 2
and nvl(mrr1.status,-1) <> 0
and nvl(mrr1.applied,-1) <> 2
and nvl(mrr.parent_id,-1) = 1
and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date
)
or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date )
or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
)
group by
mgq.row_index,
mrr.batch_number,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
msc_gantt_utils.getDisplayType(p_display_type,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
mrr.orig_resource_seq_num, mrr.parent_seq_num),
mrr.firm_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id,
mrr.sr_instance_id, mrr.organization_id,
ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
g_gantt_rh_toler_days_early,
g_gantt_rh_toler_days_late);
select mgq.row_index,
min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date,
mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
sum(mrir.capacity_consumed_ratio) assigned_units,
avg(mrir.resource_instance_hours) resource_hours,
mrir.batch_number,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
decode(p_display_type, DISPLAY_NONE, sys_no,
msc_gantt_utils.getDisplayType(p_display_type,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
mrr.orig_resource_seq_num, mrr.parent_seq_num),
mrr.firm_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id,
mrr.sr_instance_id, mrr.organization_id,
ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
g_gantt_rh_toler_days_early,
g_gantt_rh_toler_days_late)) display_type
from msc_gantt_query mgq,
msc_resource_requirements mrr,
msc_resource_instance_reqs mrir,
msc_resource_batches mrb,
msc_supplies ms
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id <> MBP_NULL_VALUE
and mrir.plan_id = mgq.plan_id
and mrir.sr_instance_id = mgq.sr_instance_id
and mrir.organization_id = mgq.organization_id
and mrir.department_id = mgq.department_id
and mrir.resource_id = mgq.resource_id
and mrir.res_instance_id = mgq.res_instance_id
and nvl(mrir.serial_number, MBP_NULL_VALUE_CHAR) = nvl(mgq.serial_number, MBP_NULL_VALUE_CHAR)
and mrir.end_date is not null
and mrir.plan_id = mrr.plan_id
and mrir.sr_instance_id = mrr.sr_instance_id
and mrir.organization_id = mrr.organization_id
and mrir.department_id = mrr.department_id
and mrir.resource_id = mrr.resource_id
and mrir.supply_id = mrr.supply_id
and mrir.resource_seq_num = mrr.resource_seq_num
and mrir.operation_seq_num = mrr.operation_seq_num
and nvl(mrir.orig_resource_seq_num, mbp_null_value) = nvl(mrr.orig_resource_seq_num, mbp_null_value)
and nvl(mrir.parent_seq_num, mbp_null_value) = nvl(mrr.parent_seq_num, mbp_null_value)
and nvl(mrir.parent_id, mbp_null_value) = nvl(mrr.parent_id, mbp_null_value)
and mrir.start_date = nvl(mrr.firm_start_date, mrr.start_date)
and mrir.end_date = nvl(mrr.firm_end_date, mrr.end_date)
and mrb.plan_id = mrr.plan_id
and mrb.sr_instance_id = mrr.sr_instance_id
and mrb.organization_id= mrr.organization_id
and mrb.department_id = mrr.department_id
and mrb.resource_id = mrr.resource_id
and mrb.batch_number = mrr.batch_number
and mrr.plan_id = ms.plan_id
and mrr.sr_instance_id = ms.sr_instance_id
and mrr.supply_id = ms.transaction_id
and mrr.batch_number is not null and mrr.end_date is not null and mrr.resource_hours > 0
and nvl(mrr.parent_id, 2) = 2
and nvl(mrr.status,-1) = 0
and nvl(mrr.applied,-1) = 2
and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date
)
or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date )
or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
)
group by
mgq.row_index,
mrir.batch_number,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
msc_gantt_utils.getDisplayType(p_display_type,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
mrr.orig_resource_seq_num, mrr.parent_seq_num),
mrr.firm_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id,
mrr.sr_instance_id, mrr.organization_id,
ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
g_gantt_rh_toler_days_early,
g_gantt_rh_toler_days_late)
union all
select mgq.row_index,
min(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) start_date,
max(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date,
mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)) end_date,
sum(mrir.capacity_consumed_ratio) assigned_units,
avg(mrir.resource_instance_hours) resource_hours,
mrir.batch_number,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
decode(p_display_type, DISPLAY_NONE, sys_no,
msc_gantt_utils.getDisplayType(p_display_type,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
mrr.orig_resource_seq_num, mrr.parent_seq_num),
mrr.firm_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id,
mrr.sr_instance_id, mrr.organization_id,
ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
g_gantt_rh_toler_days_early,
g_gantt_rh_toler_days_late)) display_type
from msc_gantt_query mgq,
msc_resource_requirements mrr,
msc_resource_requirements mrr1,
msc_resource_batches mrb,
msc_resource_instance_reqs mrir,
msc_supplies ms
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id <> MBP_NULL_VALUE
and mrir.plan_id = mgq.plan_id
and mrir.sr_instance_id = mgq.sr_instance_id
and mrir.organization_id = mgq.organization_id
and mrir.department_id = mgq.department_id
and mrir.resource_id = mgq.resource_id
and mrir.res_instance_id = mgq.res_instance_id
and nvl(mrir.serial_number, MBP_NULL_VALUE_CHAR) = nvl(mgq.serial_number, MBP_NULL_VALUE_CHAR)
and mrir.end_date is not null
and mrir.plan_id = mrr.plan_id
and mrir.sr_instance_id = mrr.sr_instance_id
and mrir.organization_id = mrr.organization_id
and mrir.department_id = mrr.department_id
and mrir.resource_id = mrr.resource_id
and mrir.supply_id = mrr.supply_id
and mrir.resource_seq_num = mrr.resource_seq_num
and mrir.operation_seq_num = mrr.operation_seq_num
and nvl(mrir.orig_resource_seq_num, mbp_null_value) = nvl(mrr.orig_resource_seq_num, mbp_null_value)
and nvl(mrir.parent_seq_num, mbp_null_value) = nvl(mrr.parent_seq_num, mbp_null_value)
and nvl(mrir.parent_id, mbp_null_value) = nvl(mrr.parent_id, mbp_null_value)
and mrir.start_date = nvl(mrr.firm_start_date, mrr.start_date)
and mrir.end_date = nvl(mrr.firm_end_date, mrr.end_date)
and mrb.plan_id = mrr.plan_id
and mrb.sr_instance_id = mrr.sr_instance_id
and mrb.organization_id= mrr.organization_id
and mrb.department_id = mrr.department_id
and mrb.resource_id = mrr.resource_id
and mrb.batch_number = mrr.batch_number
and mrr.plan_id = ms.plan_id
and mrr.sr_instance_id = ms.sr_instance_id
and mrr.supply_id = ms.transaction_id
and mrr.plan_id = mrr1.plan_id
and mrr.sr_instancE_id = mrr1.sr_instancE_id
and mrr.organization_id = mrr1.organization_id
and mrr.department_id = mrr1.department_id
and mrr.resource_id = mrr1.resource_id
and mrr.supply_id = mrr1.supply_id
and mrr.operation_seq_num = mrr1.operation_seq_num
and mrr.resource_seq_num = mrr1.resource_seq_num
and nvl(mrr.orig_resource_seq_num, mbp_null_value) = nvl(mrr1.orig_resource_seq_num, mbp_null_value)
and nvl(mrr.parent_seq_num, mbp_null_value) = nvl(mrr1.parent_seq_num, mbp_null_value)
and mrr.batch_number is not null and mrr.end_date is not null and mrr.resource_hours > 0
and mrr1.batch_number is not null and mrr1.end_date is not null and mrr1.resource_hours > 0
and nvl(mrr1.parent_id, 2) = 2
and nvl(mrr1.status,-1) <> 0
and nvl(mrr1.applied,-1) <> 2
and nvl(mrr.parent_id,-1) = 1
and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date
)
or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date )
or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
)
group by
mgq.row_index,
mrir.batch_number,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
msc_gantt_utils.getDisplayType(p_display_type,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqUlpsd(mrr.plan_id, mrr.sr_instancE_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.supply_id, mrr.operation_seq_num, mrr.resource_seq_num,
mrr.orig_resource_seq_num, mrr.parent_seq_num),
mrr.firm_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id,
mrr.sr_instance_id, mrr.organization_id,
ms.inventory_item_id, mrr.department_id, mrr.resource_id, mrr.supply_id),
g_gantt_rh_toler_days_early,
g_gantt_rh_toler_days_late);
select sys_yes
from msc_plan_organizations mpo,
msc_resource_requirements mrr,
msc_department_resources mdr,
msc_plans mp
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.organization_id = p_org
and mrr.department_id = p_dept
and mrr.resource_id = p_res
and mdr.plan_id = mrr.plan_id
and mdr.organization_id = mrr.organization_id
and mdr.sr_instance_id = mrr.sr_instance_id
and mdr.department_id = mrr.department_id
and mdr.resource_id = mrr.resource_id
and mrr.plan_id = mpo.plan_id
and mrr.sr_instance_id = mpo.sr_instance_id
and mrr.organization_id = mpo.organization_id
and mrr.plan_id = mp.plan_id
and ( ( nvl(curr_ds_enabled_flag, ds_enabled_flag) = 2 or nvl(mdr.schedule_to_instance, sys_no) = sys_no )
or ( nvl(curr_ds_enabled_flag, ds_enabled_flag) = 1
and ( nvl(mdr.schedule_to_instance, sys_no) = sys_yes
and p_start_date >= nvl(mp.min_cutoff_bucket,0)+mp.data_start_date )
));
select mgq.row_index,
mrr.sr_instance_id,
mrr.organization_id,
mrr.supply_id,
mrr.transaction_id,
nvl(mrr.status,0) status,
nvl(mrr.applied,0) applied,
nvl(mrr.firm_flag,0) res_firm_flag,
ms.firm_planned_type sup_firm_flag,
msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
mrr.organization_id, ms.inventory_item_id,
mrr.department_id, mrr.resource_id, mrr.supply_id) res_constraint,
ms.new_order_quantity qty,
nvl(mrr.batch_number, mbp_null_value),
mrr.assigned_units,
nvl(mrr.group_sequence_id, mbp_null_value),
nvl(mrr.group_sequence_number, mbp_null_value),
mrr.earliest_start_date,
mrr.earliest_completion_date,
mrr.ulpsd,
mrr.ulpcd,
mrr.uepsd,
mrr.uepcd,
mrr.eacd,
msc_gantt_utils.getResActResNodeLabel(mrr.plan_id, mrr.sr_instance_id, mrr.transaction_id) bar_text,
ms.inventory_item_id,
msc_get_name.supply_order_number(ms.order_type, ms.order_number, ms.plan_id,
ms.sr_instance_id, ms.transaction_id, ms.disposition_id) order_number,
mrr.operation_seq_num,
mrr.resource_seq_num,
mdr.resource_description,
mi.item_name item,
mi2.description assembly_item_desc,
decode(mrr.resource_hours, 0, to_number(null),
nvl(mrr.cummulative_quantity,ms.new_order_quantity)) schedule_qty,
decode(mrr.from_setup_id,
to_number(null), null,
msc_get_name.setup_code(mrr.plan_id, mrr.sr_instance_id, mrr.resource_id,
mrr.organization_id, mrr.from_setup_id)) from_setup_code,
decode(mrr.setup_id,
to_number(null), null,
msc_get_name.setup_code(mrr.plan_id, mrr.sr_instance_id, mrr.resource_id,
mrr.organization_id, mrr.setup_id)) to_setup_code,
decode(mrr.from_setup_id,
to_number(null), null,
msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id,
mrr.organization_id, mrr.department_id, mrr.resource_id, mrr.from_setup_id,
mrr.setup_id, 'STANDARD_OPERATION_CODE')) std_op_code,
decode(mrr.from_setup_id,
to_number(null), null,
msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.from_setup_id,
mrr.setup_id, 'TRANSITION_TIME')) changeover_time,
decode(mrr.from_setup_id,
to_number(null), null,
msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.from_setup_id,
mrr.setup_id, 'TRANSITION_PENALTY')) changeover_penalty,
msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
mrr.organization_id, ms.inventory_item_id,
mrr.department_id, mrr.resource_id, mrr.supply_id) res_overload
from msc_resource_requirements mrr,
msc_department_resources mdr,
msc_supplies ms,
msc_items mi,
msc_items mi2,
msc_gantt_query mgq,
msc_plan_organizations mpo,
msc_plans mp
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and ( ( p_batched_res_act = RES_REQ_ROW_TYPE )
or ( p_batched_res_act = RES_ACT_BATCHED_ROW_TYPE and ( nvl(mdr.batchable_flag,2) = 2 or mrr.batch_number is null) ) )
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id = MBP_NULL_VALUE
and mrr.plan_id = mgq.plan_id
and mrr.sr_instance_id = mgq.sr_instance_id
and mrr.organization_id = mgq.organization_id
and mrr.department_id = mgq.department_id
and mrr.resource_id = mgq.resource_id
and mrr.end_date is not null
and nvl(mrr.parent_id,2) =2
and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date
)
or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date )
or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
)
and mrr.plan_id = mdr.plan_id
and mrr.organization_id = mdr.organization_id
and mrr.sr_instance_id = mdr.sr_instance_id
and mrr.department_id = mdr.department_id
and mrr.resource_id = mdr.resource_id
and ms.plan_id = mrr.plan_id
and ms.transaction_id = mrr.supply_id
and ms.sr_instance_id = mrr.sr_instance_id
and ms.inventory_item_id = mi.inventory_item_id
and mrr.assembly_item_id = mi2.inventory_item_id
and mrr.plan_id = mpo.plan_id
and mrr.sr_instance_id = mpo.sr_instance_id
and mrr.organization_id = mpo.organization_id
and mrr.plan_id = mp.plan_id
and ( ( nvl(mpo.curr_ds_enabled_flag, mpo.ds_enabled_flag) = 2 or nvl(mdr.schedule_to_instance, sys_no) = sys_no )
or ( nvl(mpo.curr_ds_enabled_flag, mpo.ds_enabled_flag) = 1
and ( nvl(mdr.schedule_to_instance, sys_no) = sys_yes
and p_start_date >= nvl(mp.min_cutoff_bucket,0)+mp.data_start_date )
));
select mgq.row_index,
mrr.sr_instance_id,
mrr.organization_id,
mrr.supply_id,
mrir.res_inst_transaction_id,
nvl(mrr.status,0) status,
nvl(mrr.applied,0) applied,
nvl(mrr.firm_flag,0) res_firm_flag,
ms.firm_planned_type sup_firm_flag,
msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
msc_gantt_utils.isResConstraint(mrr.plan_id, mrr.sr_instance_id,
mrr.organization_id, ms.inventory_item_id,
mrr.department_id, mrr.resource_id, mrr.supply_id) res_constraint,
ms.new_order_quantity qty,
nvl(mrir.batch_number, mbp_null_value),
1 assigned_units,
nvl(mrr.group_sequence_id, mbp_null_value),
nvl(mrr.group_sequence_number, mbp_null_value),
mrr.earliest_start_date,
mrr.earliest_completion_date,
mrr.ulpsd,
mrr.ulpcd,
mrr.uepsd,
mrr.uepcd,
mrr.eacd,
msc_gantt_utils.getResActResNodeLabel(mrr.plan_id, mrr.sr_instance_id, mrr.transaction_id) bar_text,
ms.inventory_item_id,
msc_get_name.supply_order_number(ms.order_type, ms.order_number, ms.plan_id,
ms.sr_instance_id, ms.transaction_id, ms.disposition_id) order_number,
mrr.operation_seq_num,
mrr.resource_seq_num,
mdr.resource_description,
mi.item_name item,
mi2.description assembly_item_desc,
decode(mrr.resource_hours, 0, to_number(null),
nvl(mrr.cummulative_quantity,ms.new_order_quantity)) schedule_qty,
decode(mrr.from_setup_id,
to_number(null), null,
msc_get_name.setup_code(mrr.plan_id, mrr.sr_instance_id, mrr.resource_id,
mrr.organization_id, mrr.from_setup_id)) from_setup_code,
decode(mrr.setup_id,
to_number(null), null,
msc_get_name.setup_code(mrr.plan_id, mrr.sr_instance_id, mrr.resource_id,
mrr.organization_id, mrr.setup_id)) to_setup_code,
decode(mrr.from_setup_id,
to_number(null), null,
msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id,
mrr.organization_id, mrr.department_id, mrr.resource_id, mrr.from_setup_id,
mrr.setup_id, 'STANDARD_OPERATION_CODE')) std_op_code,
decode(mrr.from_setup_id,
to_number(null), null,
msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.from_setup_id,
mrr.setup_id, 'TRANSITION_TIME')) changeover_time,
decode(mrr.from_setup_id,
to_number(null), null,
msc_gantt_utils.getTansitionValue(mrr.plan_id, mrr.sr_instance_id, mrr.organization_id,
mrr.department_id, mrr.resource_id, mrr.from_setup_id,
mrr.setup_id, 'TRANSITION_PENALTY')) changeover_penalty,
msc_gantt_utils.isResOverload(mrr.plan_id, mrr.sr_instance_id,
mrr.organization_id, ms.inventory_item_id,
mrr.department_id, mrr.resource_id, mrr.supply_id) res_overload
from msc_resource_instance_reqs mrir,
msc_resource_requirements mrr,
msc_department_resources mdr,
msc_supplies ms,
msc_items mi,
msc_items mi2,
msc_gantt_query mgq
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and ( ( p_batched_res_act = RES_REQ_ROW_TYPE )
or ( p_batched_res_act = RES_ACT_BATCHED_ROW_TYPE and ( nvl(mdr.batchable_flag,2) = 2 or mrir.batch_number is null )) )
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id <> MBP_NULL_VALUE
and mrir.plan_id = mgq.plan_id
and mrir.sr_instance_id = mgq.sr_instance_id
and mrir.organization_id = mgq.organization_id
and mrir.department_id = mgq.department_id
and mrir.resource_id = mgq.resource_id
and mrir.res_instance_id = mgq.res_instance_id
and nvl(mrir.serial_number, MBP_NULL_VALUE_CHAR) = nvl(mgq.serial_number, MBP_NULL_VALUE_CHAR)
and mrir.end_date is not null
and nvl(mrir.parent_id,2) =2
and mrir.plan_id = mrr.plan_id
and mrir.sr_instance_id = mrr.sr_instance_id
and mrir.organization_id = mrr.organization_id
and mrir.department_id = mrr.department_id
and mrir.resource_id = mrr.resource_id
and mrir.supply_id = mrr.supply_id
and mrir.operation_seq_num = mrr.operation_seq_num
and mrir.resource_seq_num = mrr.resource_seq_num
and nvl(mrir.orig_resource_seq_num, mbp_null_value) = nvl(mrr.orig_resource_seq_num, mbp_null_value)
and nvl(mrir.parent_seq_num, mbp_null_value) = nvl(mrr.parent_seq_num, mbp_null_value)
and nvl(mrir.parent_id, mbp_null_value) = nvl(mrr.parent_id, mbp_null_value)
and mrir.start_date = nvl(mrr.firm_start_date, mrr.start_date)
and mrir.end_date = nvl(mrr.firm_end_date, mrr.end_date)
and nvl(mrr.parent_id, 2) = 2
and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date
)
or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date )
or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
)
and ms.plan_id = mrr.plan_id
and ms.transaction_id = mrr.supply_id
and ms.sr_instance_id = mrr.sr_instance_id
and mrr.plan_id = mdr.plan_id
and mrr.organization_id = mdr.organization_id
and mrr.sr_instance_id = mdr.sr_instance_id
and mrr.department_id = mdr.department_id
and mrr.resource_id = mdr.resource_id
and ms.inventory_item_id = mi.inventory_item_id
and mrr.assembly_item_id = mi2.inventory_item_id;
select mgq.row_index,
mrr.sr_instance_id,
mrr.organization_id,
msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
sum(ms.new_order_quantity) qty,
mrr.batch_number,
msc_gantt_utils.getResBatchNodeLabel(
nvl(decode(mrr.setup_id,
to_number(null), null_space,
msc_gantt_utils.getSetupCode(mrr.plan_id,
mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)),
null_space),
msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
to_char(sum(ms.new_order_quantity)),
to_char(mrr.batch_number),
to_char(sum(mrr.capacity_consumed_ratio))) bar_text
from msc_resource_requirements mrr,
msc_supplies ms,
msc_gantt_query mgq,
msc_plan_organizations mpo,
msc_department_resources mdr,
msc_plans mp
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id = MBP_NULL_VALUE
and mrr.plan_id = mgq.plan_id
and mrr.sr_instance_id = mgq.sr_instance_id
and mrr.organization_id = mgq.organization_id
and mrr.department_id = mgq.department_id
and mrr.resource_id = mgq.resource_id
and mrr.batch_number is not null
and mrr.end_date is not null
and nvl(mrr.parent_id,2) = 2
and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date
)
or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date )
or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
)
and ms.plan_id = mrr.plan_id
and ms.transaction_id = mrr.supply_id
and ms.sr_instance_id = mrr.sr_instance_id
and mdr.plan_id = mrr.plan_id
and mdr.organization_id = mrr.organization_id
and mdr.sr_instance_id = mrr.sr_instance_id
and mdr.department_id = mrr.department_id
and mdr.resource_id = mrr.resource_id
and mrr.plan_id = mpo.plan_id
and mrr.sr_instance_id = mpo.sr_instance_id
and mrr.organization_id = mpo.organization_id
and mrr.plan_id = mp.plan_id
and ( ( nvl(mpo.curr_ds_enabled_flag, mpo.ds_enabled_flag) = 2 or nvl(mdr.schedule_to_instance, sys_no) = sys_no )
or ( nvl(mpo.curr_ds_enabled_flag, mpo.ds_enabled_flag) = 1
and ( nvl(mdr.schedule_to_instance, sys_no) = sys_yes
and p_start_date >= nvl(mp.min_cutoff_bucket,0)+mp.data_start_date )
))
group by mgq.row_index,
mrr.plan_id,
mrr.sr_instance_id,
mrr.organization_id,
msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
mrr.resource_id,
mrr.batch_number,
msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
mrr.setup_id;
select mgq.row_index,
mrr.sr_instance_id,
mrr.organization_id,
msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) schedule_flag,
sum(ms.new_order_quantity) qty,
mrir.batch_number,
msc_gantt_utils.getResBatchNodeLabel(
nvl(decode(mrr.setup_id,
to_number(null), null_space,
msc_gantt_utils.getSetupCode(mrr.plan_id,
mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)),
null_space),
msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
to_char(sum(ms.new_order_quantity)),
to_char(mrir.batch_number),
to_char(sum(mrir.capacity_consumed_ratio))) bar_text
from msc_resource_instance_reqs mrir,
msc_resource_requirements mrr,
msc_supplies ms,
msc_gantt_query mgq
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.res_instance_id <> MBP_NULL_VALUE
and mrir.plan_id = mgq.plan_id
and mrir.sr_instance_id = mgq.sr_instance_id
and mrir.organization_id = mgq.organization_id
and mrir.department_id = mgq.department_id
and mrir.resource_id = mgq.resource_id
and mrir.res_instance_id = mgq.res_instance_id
and nvl(mrir.serial_number, MBP_NULL_VALUE_CHAR) = nvl(mgq.serial_number, MBP_NULL_VALUE_CHAR)
and mrir.end_date is not null
and mrir.batch_number is not null
and nvl(mrir.parent_id,2) =2
and mrir.plan_id = mrr.plan_id
and mrir.sr_instance_id = mrr.sr_instance_id
and mrir.organization_id = mrr.organization_id
and mrir.department_id = mrr.department_id
and mrir.resource_id = mrr.resource_id
and mrir.supply_id = mrr.supply_id
and mrir.operation_seq_num = mrr.operation_seq_num
and mrir.resource_seq_num = mrr.resource_seq_num
and nvl(mrir.orig_resource_seq_num, mbp_null_value) = nvl(mrr.orig_resource_seq_num, mbp_null_value)
and nvl(mrir.parent_seq_num, mbp_null_value) = nvl(mrr.parent_seq_num, mbp_null_value)
and nvl(mrir.parent_id, mbp_null_value) = nvl(mrr.parent_id, mbp_null_value)
and mrir.start_date = nvl(mrr.firm_start_date, mrr.start_date)
and mrir.end_date = nvl(mrr.firm_end_date, mrr.end_date)
and nvl(mrr.parent_id, 2) = 2
and ( ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date
)
or ( msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
between p_start_date and p_end_date )
or ( msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) <= p_start_date
and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrir.start_date, mrir.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) >= p_end_date )
)
and ms.plan_id = mrr.plan_id
and ms.transaction_id = mrr.supply_id
and ms.sr_instance_id = mrr.sr_instance_id
group by mgq.row_index,
mrr.plan_id,
mrr.sr_instance_id,
mrr.organization_id,
msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id),
mrr.resource_id,
mrir.batch_number,
msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrir.start_date, mrir.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied),
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id),
mrr.setup_id;
select nvl(mgdq.sr_instance_id, mbp_null_value),
nvl(mgdq.organization_id, mbp_null_value),
nvl(mgdq.supply_id, mbp_null_value),
nvl(mgdq.transaction_id, mbp_null_value),
nvl(mgdq.status, mbp_null_value),
nvl(mgdq.applied, mbp_null_value),
nvl(mgdq.res_firm_flag, mbp_null_value),
nvl(mgdq.sup_firm_flag, mbp_null_value),
mgdq.start_date,
mgdq.end_date,
nvl(mgdq.schedule_flag, mbp_null_value),
nvl(mgdq.late_flag, mbp_null_value),
nvl(mgdq.supply_qty, mbp_null_value),
nvl(mgdq.batch_number, mbp_null_value),
nvl(mgdq.resource_units, mbp_null_value),
nvl(mgdq.group_sequence_id, mbp_null_value),
nvl(mgdq.group_sequence_number, mbp_null_value),
nvl(mgdq.bar_label, null_space),
msc_gantt_utils.getDisplayType(p_display_type, mgdq.end_date, mgdq.ulpst,
mgdq.res_firm_flag, mgdq.late_flag, g_gantt_ra_toler_days_early,
g_gantt_ra_toler_days_late) display_type,
mgdq.cepst,
mgdq.cepct,
mgdq.ulpst,
mgdq.ulpct,
mgdq.uepst,
mgdq.uepct,
mgdq.eact,
mgq.department_id,
mgq.resource_id,
mgdq.inventory_item_id,
mgdq.order_number,
mgdq.op_seq_num,
mgdq.resource_seq_num,
mgdq.resource_description,
mgdq.item_name,
mgdq.assembly_item_desc,
mgdq.schedule_qty,
mgdq.from_setup_code,
mgdq.to_setup_code,
mgdq.std_op_code,
mgdq.changeover_time,
mgdq.changeover_penalty,
mgdq.min_capacity,
mgdq.max_capacity,
mgdq.capacity_used,
mgdq.supp_avail_qty
bulk collect into l_sr_instance_id, l_organization_id,
l_supply_id, l_transaction_id, l_status, l_applied, l_res_firm_flag,
l_sup_firm_flag, l_start_date, l_end_date, l_schedule_flag, l_res_constraint,
l_qty, l_batch_number, l_resource_units,
l_group_sequence_id, l_group_sequence_number, l_bar_text, l_display_type,
l_cepst, l_cepct, l_ulpst, l_ulpct, l_uepst, l_uepct, l_eacd,
l_dept_id, l_res_id, l_inventory_item_id, l_order_number,
l_op_seq, l_res_seq, l_res_desc, l_item_name, l_assy_item_desc, l_schedule_qty,
l_from_setup_code, l_to_setup_code, l_std_op_code, l_changeover_time,
l_changeover_penalty, l_min_capacity, l_max_capacity, l_capacity_used,
l_overload_flag
from msc_gantt_dtl_query mgdq,
msc_gantt_query mgq
where mgq.query_id = p_query_id
and mgq.row_index = active_row.row_index
and mgq.query_id = mgdq.query_id
and mgq.row_index = mgdq.row_index
and mgdq.row_type in (RES_REQ_ROW_TYPE, RES_ACT_BATCHED_ROW_TYPE)
and mgdq.parent_id = SUMMARY_DATA
order by mgdq.start_date;
select row_index, inventory_item_id, supplier_id, supplier_site_id
from msc_gantt_query
where query_id = p_query_id;
select sr_instance_id, organization_id
from msc_item_suppliers
where plan_id = l_plan
and inventory_item_id = l_item
and supplier_id = l_supp
and supplier_site_id = l_supp_site;
select mgq.row_index, mca.calendar_date, msc.capacity
bulk collect into v_row_index, v_start_date, v_qty
from msc_calendar_dates mca,
msc_plans mp,
msc_supplier_capacities msc,
msc_item_suppliers mis,
msc_gantt_query mgq
where mgq.query_id = p_query_id
and mgq.row_index = l_row_index
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and msc.plan_id = mgq.plan_id
and msc.sr_instance_id = l_inst_id
and msc.organization_id = l_org_id
and msc.inventory_item_id = mgq.inventory_item_id
and msc.supplier_id = mgq.supplier_id
and msc.supplier_site_id = mgq.supplier_site_id
and msc.capacity >= 0 --4476899 bugfix
and mp.plan_id = msc.plan_id
and msc.plan_id = mis.plan_id
and msc.sr_instance_id = mis.sr_instance_id
and msc.organization_id = mis.organization_id
and msc.inventory_item_id = mis.inventory_item_id
and msc.supplier_id = mis.supplier_id
and msc.supplier_site_id = mis.supplier_site_id
and mca.calendar_date between p_start_date and p_end_date
and mca.calendar_date between trunc(msc.from_date) and trunc(nvl(msc.to_date,p_end_date))
and mca.calendar_date >= nvl(trunc(mis.supplier_lead_time_date+1),trunc(mp.plan_start_date+2))
and mca.calendar_code = mis.delivery_calendar_code
and mis.delivery_calendar_code is not null
and mca.sr_instance_id = g_plan_cal_inst_id
and mca.exception_set_id = g_plan_cal_excp_id
and mca.seq_num is not null;
select mgq.row_index, mfq.date1, msc.capacity
bulk collect into v_row_index, v_start_date, v_qty
from msc_form_query mfq,
msc_plans mp,
msc_supplier_capacities msc,
msc_item_suppliers mis,
msc_gantt_query mgq
where mgq.query_id = p_query_id
and mgq.row_index = l_row_index
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and msc.plan_id = mgq.plan_id
and msc.sr_instance_id = l_inst_id
and msc.organization_id = l_org_id
and msc.inventory_item_id = mgq.inventory_item_id
and msc.supplier_id = mgq.supplier_id
and msc.supplier_site_id = mgq.supplier_site_id
and msc.capacity >= 0 --4476899 bugfix
and mp.plan_id = msc.plan_id
and msc.plan_id = mis.plan_id
and msc.sr_instance_id = mis.sr_instance_id
and msc.organization_id = mis.organization_id
and msc.inventory_item_id = mis.inventory_item_id
and msc.supplier_id = mis.supplier_id
and msc.supplier_site_id = mis.supplier_site_id
and mis.delivery_calendar_code is null
and mfq.query_id = l_mfg_cal_query_id
and mfq.date1 between p_start_date and p_end_date
and mfq.date1 between trunc(msc.from_date) and trunc(nvl(msc.to_date,p_end_date))
and mfq.date1 >= nvl(trunc(mis.supplier_lead_time_date+1),trunc(mp.plan_start_date+2));
select mgq.row_index,
msr.consumption_date,
sum(msr.overloaded_capacity) overload_qty,
sum(msr.consumed_quantity) consumed_qty
bulk collect into v_row_index, v_start_date, v_overload_qty, v_consumed_qty
from msc_gantt_query mgq,
msc_supplier_requirements msr
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.dependency_type <> 1 -- bom_item_type, all the items except MODELs
and msr.plan_id = mgq.plan_id
--and msr.sr_instance_id = mgq.sr_instance_id
--and msr.organization_id = mgq.organization_id
and msr.inventory_item_id = mgq.inventory_item_id
and msr.supplier_id = mgq.supplier_id
and msr.supplier_site_id = mgq.supplier_site_id
group by mgq.row_index, msr.consumption_date;
select mgq.row_index,
msr.consumption_date,
sum(msr.overloaded_capacity) overload_qty,
sum(msr.consumed_quantity) consumed_qty
bulk collect into v_row_index, v_start_date, v_overload_qty, v_consumed_qty
from msc_gantt_query mgq,
msc_supplier_requirements msr,
msc_system_items msi
where mgq.query_id = p_query_id
and mgq.row_flag = SYS_YES
and mgq.is_fetched = SYS_NO
and mgq.dependency_type = 1 -- bom_item_type, MODELs
and msi.plan_id = mgq.plan_id
--and msi.sr_instance_id = mgq.sr_instance_id
--and msi.organization_id = mgq.organization_id
and ( msi.base_item_id = mgq.inventory_item_id or msi.inventory_item_id = mgq.inventory_item_id) --5220804 bugfix
and msr.plan_id = mgq.plan_id
and msr.sr_instance_id = msi.sr_instance_id
and msr.organization_id = msi.organization_id
and msr.inventory_item_id = msi.inventory_item_id
and msr.supplier_id = mgq.supplier_id
and msr.supplier_site_id = mgq.supplier_site_id
group by mgq.row_index, msr.consumption_date;
select max(from_date) max_bkt_start_date,
max(nvl(to_date,p_plan_end)) max_bkt_end_date
from msc_gantt_query mgq,
msc_supplier_capacities msc1
where mgq.query_id = p_query
and mgq.row_index = p_row_index
and msc1.plan_id = p_plan
and msc1.supplier_id = mgq.supplier_id
and msc1.supplier_site_id = mgq.supplier_site_id
--and msc1.organization_id = mgq.organization_id
--and msc1.sr_instance_id = mgq.sr_instance_id
and msc1.inventory_item_id = mgq.inventory_item_id;
select trunc(start_date), nvl(resource_units,0)
bulk collect into v_avail_start, v_avail_qty
from msc_gantt_dtl_query
where query_id = p_query_id
and row_index = active_row.row_index
and row_type = SUPP_AVAIL_ROW_TYPE
and parent_id = SUMMARY_DATA;
select trunc(start_date), nvl(resource_units,0), nvl(resource_hours,0)
bulk collect into v_consume_start, v_overload_qty, v_consume_qty
from msc_gantt_dtl_query
where query_id = p_query_id
and row_index = active_row.row_index
and row_type = SUPP_ALL_ROW_TYPE
and parent_id = SUMMARY_DATA
order by start_date;
select trunc(start_date),
round(supp_avail_qty, ROUND_FACTOR),
round(supp_overload_qty, ROUND_FACTOR),
round(supp_consume_qty, ROUND_FACTOR)
bulk collect into v_start_date, v_avail_qty, v_overload_qty, v_consume_qty
from msc_gantt_dtl_query
where query_id = p_query_id
and row_index = active_row.row_index
and row_type = SUPP_ALL_ROW_TYPE
and parent_id = DETAIL_DATA
order by start_date;
select start_date, end_date,
round(resource_hours, ROUND_FACTOR) resource_hours,
to_number(null) resource_units,
schedule_flag,
display_flag
bulk collect into v_start, v_end, v_resource_hours, v_resource_units,
v_schedule_flag, v_display_flag
from msc_gantt_dtl_query
where query_id = p_query_id
and row_index = active_row.row_index
and row_type = RES_REQ_ROW_TYPE
and ( ( parent_id = SUMMARY_DATA and p_isBucketed = SYS_NO )
or (parent_id = DETAIL_DATA and p_isBucketed = SYS_YES ) )
--and display_flag = sys_yes
order by start_date, end_date;
select start_date, end_date,
round(resource_hours, ROUND_FACTOR) resource_hours,
round(resource_units, ROUND_FACTOR) resource_units,
schedule_flag,
display_flag
bulk collect into v_start, v_end, v_resource_hours, v_resource_units,
v_schedule_flag, v_display_flag
from msc_gantt_dtl_query
where query_id = p_query_id
and row_index = active_row.row_index
and row_type = RES_REQ_ROW_TYPE
and ( ( parent_id = SUMMARY_DATA and p_isBucketed = SYS_NO )
or (parent_id = DETAIL_DATA and p_isBucketed = SYS_YES ) )
order by start_date, end_date;
select start_date, end_date,
round(resource_hours, ROUND_FACTOR) resource_hours,
round(resource_units, ROUND_FACTOR) resource_units,
schedule_flag
bulk collect into v_start, v_end, v_resource_hours, v_resource_units, v_schedule_flag
from msc_gantt_dtl_query
where query_id = p_query_id
and row_index = active_row.row_index
and row_type = RES_AVAIL_ROW_TYPE
and ( ( parent_id = SUMMARY_DATA and p_isBucketed = SYS_NO )
or (parent_id = DETAIL_DATA and p_isBucketed = SYS_YES ) )
order by start_date, end_date;
v_bkt_start.delete;
v_bkt_end.delete;
insert into msc_form_query
( query_id, last_update_date, last_updated_by, creation_date, created_by, last_update_login,
date1, date2)
values
( p_query_id, sysdate, -1, sysdate, -1, -1, p_temp_start, p_temp_start+1);
select mdr.max_rate
from msc_department_resources mdr,
msc_gantt_query mgq
where mgq.query_id = l_query_id
and mgq.row_index = l_row_index
and mgq.organization_id = mdr.organization_id
and mgq.sr_instance_id = mdr.sr_instance_id
and mgq.department_id = mdr.department_id
and mgq.resource_id = mdr.resource_id
and mdr.plan_id = -1;
select 1
from msc_net_resource_avail mnra,
msc_gantt_query mgq
where mgq.query_id = l_query_id
and mgq.row_index = l_row_index
and mgq.organization_id = mnra.organization_id
and mgq.sr_instance_id = mnra.sr_instance_id
and mgq.department_id = mnra.department_id
and mgq.resource_id = mnra.resource_id
and mgq.plan_id = mnra.plan_id
and nvl(mnra.parent_id, 0) <> -1;
select start_date, end_date, resource_hours, nvl(display_flag, sys_no)
bulk collect into v_setup_start, v_setup_end, v_setup_hours, v_setup_display_type
from msc_gantt_dtl_query
where query_id = p_query_id
and row_index = active_row.row_index
and row_type = RES_REQ_ROW_TYPE
and parent_id = SUMMARY_DATA
and schedule_flag in (RES_SETUP_ROW_TYPE, RES_SETUP_FIXED_ROW_TYPE)
order by start_date; --only setup time
select start_date, end_date, resource_hours, nvl(display_flag, sys_no)
bulk collect into v_req_start, v_req_end, v_req_hours, v_req_display_type
from msc_gantt_dtl_query
where query_id = p_query_id
and row_index = active_row.row_index
and row_type = RES_REQ_ROW_TYPE
and parent_id = SUMMARY_DATA
and schedule_flag in (RES_REQ_ROW_TYPE, RES_REQ_SDS_ROW_TYPE)
order by start_date; --only run time
select start_date, end_date, resource_hours
bulk collect into v_avail_start, v_avail_end, v_avail_hours
from msc_gantt_dtl_query
where query_id = p_query_id
and row_index = active_row.row_index
and parent_id = SUMMARY_DATA
and row_type = RES_AVAIL_ROW_TYPE
order by start_date; --avail time
select count(*)
from msc_among_values
where folder_id = p_folder
and ltrim(rtrim(field_name)) = p_res_field;
supp_where_stat := 'select distinct '
||' inventory_item_id, supplier_id, supplier_site_id '
||' from msc_item_supplier_v '
||' where plan_id = :1 '
||' and category_set_id = '|| g_category_set_id
||' and 1=1 '||p_where;
orders_where_stat := 'select sr_instance_id, transaction_id '
||' from msc_orders_v '
||' where plan_id = :1 '
||' and category_set_id = '|| g_category_set_id
||' and source_table = ''MSC_SUPPLIES'' '
||' and 1=1 '||p_where;
res_where_stat := 'select sr_instance_id, organization_id, '
||' department_id, resource_id, res_instance_id, serial_number '
||' from msc_res_and_inst_v '
||' where plan_id = :1 '
--||' and res_instance_id is null '
||' and 1=1 '||p_where;
res_where_stat := 'insert into msc_form_query(query_id, number1, number2, '
||' number3, number4, number5, char1, '
||' last_update_date, last_updated_by, creation_date, created_by) '
||' select '||l_among_mfq_query_id
||' ,sr_instance_id, organization_id, '
||' department_id, resource_id, res_instance_id, serial_number, '
||' sysdate, -1, sysdate, -1 '
||' from msc_res_and_inst_v '
||' where plan_id = '||p_plan_id
--||' and res_instance_id is null '
||' and 1=1 '||p_where;
res_where_stat := 'select number1, number2, number3, number4, number5, char1 '
||' from msc_form_query mfq, '
||' msc_among_values mav '
||' where mfq.query_id = :p_mfq_query '
||' and mav.folder_id = :p_folder '
||' and ltrim(rtrim(mav.field_name)) = :p_res_field '
||' and mfq.number4 = mav.hidden_values '
||' order by mav.order_by_sequence ';
sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
' mrr.supply_id ' ||
' FROM msc_resource_requirements mrr, ' ||
' msc_department_resources mdr ' ||
' WHERE mrr.plan_id = '||p_plan_id ||
' AND mdr.plan_id = mrr.plan_id '||
' AND mdr.organization_id = mrr.organization_id ' ||
' AND mdr.sr_instance_id = mrr.sr_instance_id'||
' AND mdr.department_id = mrr.department_id'||
' AND mdr.resource_id = mrr.resource_id '||
' AND mdr.aggregate_resource_flag =2 '||
' and mrr.end_date is not null '||
' and nvl(mrr.parent_id,2) =2 '||
p_where;
sql_stat := 'SELECT mrr.sr_instance_id, '||
' mrr.transaction_id '||
' FROM msc_supplies mrr, '||
' msc_exception_details med '||
' WHERE mrr.plan_id = '||p_plan_id ||
' and mrr.plan_id = med.plan_id '||
' and ( (mrr.transaction_id = med.number1 and '||
' med.exception_type in (6,7,8,9,10,32,34,53,54,58))'||
' or (mrr.transaction_id = med.number2 and '||
' med.exception_type = 37)) '|| p_where ||
' union select mrr.sr_instance_id, '||
' mrr.supply_id transaction_id '||
' FROM msc_resource_requirements mrr, '||
' msc_exception_details med '||
' where med.exception_type in (21,22,36,45,46) '||
' AND med.plan_id = mrr.plan_id ' ||
' AND med.organization_id = mrr.organization_id ' ||
' AND med.sr_instance_id = mrr.sr_instance_id ' ||
' AND med.department_id = mrr.department_id '||
' AND med.plan_id = '||p_plan_id ||
' AND med.resource_id = mrr.resource_id '||
' and mrr.end_date is not null '||
p_where;
sql_stat := 'SELECT mrr.sr_instance_id, '||
' mrr.transaction_id '||
'FROM msc_supplies mrr '||
' WHERE mrr.plan_id = '||p_plan_id ||p_where;
sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
' mrr.organization_id, '||
' mrr.department_id, '||
' mrr.resource_id '||
' FROM msc_resource_requirements mrr, ' ||
' msc_department_resources mdr, ' ||
' msc_exception_details med ' ||
' WHERE mrr.plan_id = :1 '||
' AND mdr.plan_id = mrr.plan_id '||
' AND mdr.organization_id = mrr.organization_id ' ||
' AND mdr.sr_instance_id = mrr.sr_instance_id'||
' AND mdr.department_id = mrr.department_id'||
' AND mdr.resource_id = mrr.resource_id '||
' AND mdr.aggregate_resource_flag =2 '||
' AND med.plan_id = mrr.plan_id ' ||
' AND med.organization_id = mrr.organization_id ' ||
' AND med.sr_instance_id = mrr.sr_instance_id ' ||
' AND decode(med.department_id, -1, mrr.department_id,'||
' med.department_id) = mrr.department_id ' ||
' AND decode(med.resource_id, -1, mrr.resource_id, '||
' med.resource_id) = mrr.resource_id '||
' AND decode(med.inventory_item_id, -1, '||
' mrr.assembly_item_id, '||
' med.inventory_item_id) = mrr.assembly_item_id '||
' and ( (mrr.supply_id = med.number1 and '||
' med.exception_type in (6,7,8,9,10,32,34,53,54,58))'||
' or (mrr.supply_id = med.number2 and '||
' med.exception_type = 37) '||
' or (med.exception_type in (21,22,36,45,46)))'||
' and nvl(mrr.parent_id,2) =2 ' ||
' and mrr.end_date is not null '||
' and nvl(mrr.firm_end_date,mrr.end_date) <= :2 '||
p_where;
sql_stat := ' SELECT distinct mrr.sr_instance_id, ' ||
' mrr.organization_id, '||
' mrr.department_id, '||
' mrr.resource_id '||
' FROM msc_resource_requirements mrr, ' ||
' msc_department_resources mdr ' ||
' WHERE mrr.plan_id = :1 '||
' AND mdr.plan_id = mrr.plan_id '||
' AND mdr.organization_id = mrr.organization_id ' ||
' AND mdr.sr_instance_id = mrr.sr_instance_id'||
' AND mdr.department_id = mrr.department_id'||
' AND mdr.resource_id = mrr.resource_id '||
' AND mdr.aggregate_resource_flag =2 '||
' and mrr.end_date is not null '||
' and nvl(mrr.parent_id,2) =2 ' ||
' and nvl(mrr.firm_end_date,mrr.end_date) <= :2 '||
p_where;
delete from msc_form_query where query_id = p_query_id;
select msc_form_query_s.nextval
into l_query_id
from dual;
delete from msc_gantt_query where query_id = p_query_id;
select msc_gantt_query_s.nextval
into l_query_id
from dual;
select mrrb.sr_instance_id, mrrb.transaction_id
from msc_resource_requirements mrr,
msc_resource_requirements mrrb
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mrr.plan_id = mrrb.plan_id
and mrr.sr_instance_id = mrrb.plan_id
and mrr.batch_number = mrrb.batch_number;
select count(*)
from msc_resource_instance_reqs
where plan_id = p_plan
and resource_id = p_res
and res_instance_id = p_res_inst
and nvl(serial_number, mbp_null_value_char) = nvl(p_serial, mbp_null_value_char)
and supply_id = p_supply
and operation_seq_num = p_op_seq_num;
select mrr.resource_id, mrr.routing_sequence_id,
mrr.operation_sequence_id,
mrr.operation_seq_num,
mrr.resource_seq_num,
mrr.supply_id,
mrr.basis_type,
mors.activity_group_id,
ms.new_order_quantity qty,
mrr.assigned_units,
mrr.firm_flag
from msc_resource_requirements mrr,
msc_operation_resource_seqs mors,
msc_supplies ms
where mrr.plan_id = p_plan
and mrr.transaction_id = p_trx
and mrr.sr_instance_id = p_inst
and mors.plan_id = mrr.plan_id
and mors.sr_instance_id = mrr.sr_instance_id
and mors.routing_sequence_id = mrr.routing_sequence_id
and mors.operation_sequence_id = mrr.operation_sequence_id
and mors.resource_seq_num = mrr.resource_seq_num
and ms.plan_id = mrr.plan_id
and ms.sr_instance_id = mrr.sr_instance_id
and ms.transaction_id = mrr.supply_id
union all --5478582 bugfix
select mrr.resource_id, mrr.routing_sequence_id,
mrr.operation_sequence_id,
mrr.operation_seq_num,
mrr.resource_seq_num,
mrr.supply_id,
mrr.basis_type,
mjor.activity_group_id,
ms.new_order_quantity qty,
mrr.assigned_units,
mrr.firm_flag
from msc_resource_requirements mrr,
msc_job_op_resources mjor,
msc_supplies ms
where mrr.plan_id = p_plan
and mrr.transaction_id = p_trx
and mrr.sr_instance_id = p_inst
and mrr.routing_sequence_id is null
and mjor.plan_id = mrr.plan_id
and mjor.sr_instance_id = mrr.sr_instance_id
and mjor.transaction_id = mrr.supply_id
and mjor.resource_id = mrr.resource_id
and mjor.operation_seq_num = mrr.operation_seq_num
and mjor.resource_seq_num = mrr.resource_seq_num
and ms.plan_id = mrr.plan_id
and ms.sr_instance_id = mrr.sr_instance_id
and ms.transaction_id = mrr.supply_id;
select count(*)
from (select distinct mors.resource_seq_num
from msc_operation_resource_seqs mors,
msc_operation_resources mor
where mors.plan_id = p_plan
and mors.sr_instance_id = p_inst
and mors.operation_sequence_id = p_op_seq
and mors.routing_sequence_id = p_rout_seq
and mors.activity_group_id = p_act_group
and mor.plan_id = mors.plan_id
and mor.sr_instance_id = mors.sr_instance_id
and mor.routing_sequence_id = mors.routing_sequence_id
and mor.operation_sequence_id = mors.operation_sequence_id
and mor.resource_seq_num = mors.resource_seq_num
and mor.alternate_number = p_alternate_num);
select
distinct mors.resource_seq_num
from msc_operation_resource_seqs mors
where mors.plan_id = p_plan
and mors.sr_instance_id = p_inst
and mors.routing_sequence_id = p_rout_seq
and mors.operation_sequence_id = p_op_seq
and mors.activity_group_id = p_act_group;
select distinct mrr.transaction_id,
mor.principal_flag,
mrr.resource_id
from msc_resource_requirements mrr,
msc_operation_resources mor
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.routing_sequence_id = p_rout_seq
and mrr.operation_sequence_id = p_op_seq
and mrr.resource_seq_num = p_res_seq
and mor.plan_id = mrr.plan_id
and mor.sr_instance_id = mrr.sr_instance_id
and mor.routing_sequence_id = mrr.routing_sequence_id
and mor.operation_sequence_id = mrr.operation_sequence_id
and mor.resource_seq_num = mrr.resource_seq_num
and mor.resource_id = mrr.resource_id
and mor.alternate_number <> p_alternate_num
and mrr.parent_id = 2
and mrr.supply_id = p_supply_id
union all --5478582 bugfix
select distinct mrr.transaction_id,
mjor.principal_flag,
mrr.resource_id
from msc_resource_requirements mrr,
msc_job_op_resources mjor
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.routing_sequence_id is null
and mrr.operation_seq_num = p_op_seq_num
and mrr.resource_seq_num = p_res_seq
and mjor.plan_id = mrr.plan_id
and mjor.sr_instance_id = mrr.sr_instance_id
and mjor.transaction_id = mrr.supply_id
and mjor.operation_seq_num = mrr.operation_seq_num
and mjor.resource_seq_num = mrr.resource_seq_num
and mjor.resource_id = mrr.resource_id
and mjor.alternate_num <> p_alternate_num
and mrr.parent_id = 2
and mrr.supply_id = p_supply_id
order by 2;
select mor.resource_usage,
mor.resource_units,
mor.resource_id,
mor.alternate_number,
mor.principal_flag,
mor.basis_type,
mor.orig_resource_seq_num
from msc_operation_resources mor
where mor.plan_id = p_plan
and mor.sr_instance_id = p_inst
and mor.routing_sequence_id = p_rout_seq
and mor.operation_sequence_id = p_op_seq
and mor.resource_seq_num = p_res_seq
and mor.alternate_number = p_alternate_num
union all --5478582 bugfix
select mjor.usage_rate_or_amount resource_usage,
mjor.assigned_units resource_units,
mjor.resource_id,
mjor.alternate_num,
mjor.principal_flag,
mjor.basis_type,
mjor.orig_resource_seq_num
from msc_job_op_resources mjor
where mjor.plan_id = p_plan
and mjor.sr_instance_id = p_inst
and mjor.transaction_id = p_supply_id
and mjor.operation_seq_num = p_op_seq_num
and mjor.resource_seq_num = p_res_seq
and mjor.alternate_num = p_alternate_num
and p_rout_seq is null
order by 5;
select nvl(assembly_quantity,1) assembly_quantity
from msc_boms mb,
msc_supplies ms,
msc_process_effectivity mpe,
msc_resource_requirements mrr
where ms.plan_id = p_plan
and ms.transaction_id = p_supply
and ms.plan_id = mrr.plan_id
and ms.sr_instance_id = mrr.sr_instance_id
and ms.transaction_id = mrr.supply_id
and nvl(mrr.parent_id,2) =2
and ms.plan_id = mpe.plan_id
and ms.sr_instance_id = mpe.sr_instance_id
and ms.process_seq_id = mpe.process_sequence_id
and mpe.plan_id = mb.plan_id
and mpe.sr_instance_id = mb.sr_instance_id
and mpe.bill_sequence_id = mb.bill_sequence_id
and mrr.assembly_item_id = mb.assembly_item_id;
select count(*)
into l_alt_count
from msc_form_query
where query_id = to_number(l_dummy_id_data)
and number1 = p_alt_resource
and nvl(number2, mbp_null_value) = nvl(p_alt_resource_inst, mbp_null_value)
and nvl(char1, mbp_null_value_char) = nvl(p_serial_number, mbp_null_value_char)
and number3 = p_alt_num;
msc_gantt_utils.updateReqInstFromAlt(p_plan_id, p_instance_id,
ll_res_inst_trx_id, p_alt_resource, p_alt_resource_inst, p_serial_number,
null, p_alt_resource, null);
msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
put_line(' in i > l_simu_res.last - inserting alt res into mrr ');
l_dummy_id := msc_gantt_utils.insertReqFromAlt(p_plan_id, p_instance_id,
l_simu_res(1).transaction_id , l_alt_res(i).resource_id, v_hours,
l_alt_res(i).alternate_number, l_alt_res(i).basis_type,
l_alt_res(i).orig_resource_seq_num);
DeleteReqInstFromAlt(p_plan_id, p_instance_id, ll_temp_res_inst_trx_id);
elsif i > l_alt_res.last then -- delete the extra res
put_line(' in i > l_alt_res.last - deleting alt res into mrr ');
msc_gantt_utils.DeleteReqFromAlt(p_plan_id, p_instance_id, l_simu_res(i).transaction_id);
DeleteReqInstFromAlt(p_plan_id, p_instance_id, ll_temp_res_inst_trx_id);
else -- update the res to alt_res
put_line(' in else simu trx id '||l_simu_res(i).transaction_id||' updating alt res into mrr ');
msc_gantt_utils.updateReqFromAlt(p_plan_id, p_instance_id,
l_simu_res(i).transaction_id, l_alt_res(i).resource_id,
v_hours, l_alt_res(i).alternate_number, l_alt_res(i).basis_type,
l_alt_res(i).orig_resource_seq_num);
DeleteReqInstFromAlt(p_plan_id, p_instance_id, ll_temp_res_inst_trx_id);
put_line(' in res inst update insert');
put_line(' res inst insert');
l_temp_trx_id := msc_gantt_utils.insertReqInstFromAlt(p_plan_id,
p_instance_id, ll_res_trx_id, p_alt_resource, p_alt_resource_inst,
p_serial_number, v_hours, p_alt_num, RES_NODE,
l_alt_res(i).orig_resource_seq_num);
put_line(' res inst update');
msc_gantt_utils.updateReqInstFromAlt(p_plan_id, p_instance_id,
ll_res_inst_trx_id, p_alt_resource, p_alt_resource_inst, p_serial_number,
v_hours, l_alt_res(i).alternate_number,
l_alt_res(i).orig_resource_seq_num);
msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
select mrir.supply_id, mrir.start_date, mrir.end_date
into p_supply_id, p_start_date, p_end_date
from msc_resource_instance_reqs mrir
where mrir.plan_id = p_plan_id
and mrir.sr_instance_id = p_inst_id
and mrir.res_inst_transaction_id = p_trx_id
for update of mrir.supply_id nowait;
select mrr.firm_start_date, mrr.firm_end_date,
mrr.start_date, mrr.end_date, mrr.supply_id, mrr.firm_flag
into p_firm_start_date, p_firm_end_date,
p_start_date, p_end_date, p_supply_id, p_firm_type
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.transaction_id = p_trx_id
for update of mrr.firm_flag nowait;
procedure updateBatchReq(p_plan_id number, p_inst_id number, p_batch_number number,
p_start_date date, p_end_date date, p_firm_flag number, p_update_mode number,
p_return_status in OUT NOCOPY varchar2, p_out in OUT NOCOPY varchar2) is
l_resource_id number_arr;
select mrr.resource_id
bulk collect into l_resource_id
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.batch_number = p_batch_number
for update of mrr.resource_id nowait;
select count(*)
into l_temp
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.batch_number = p_batch_number
and nvl(firm_flag,0) <> 0;
update msc_resource_requirements
set status =0,
applied=2,
firm_flag = decode( p_update_mode, FIRM_MRR, p_firm_flag, firm_flag),
start_date = decode( p_update_mode, MOVE_MRR, p_start_date, start_date),
end_date = decode( p_update_mode, MOVE_MRR, p_end_date, end_date)
where plan_id = p_plan_id
and sr_instance_id = p_inst_id
and batch_number = p_batch_number;
update msc_supplies
set status = 0,
applied = 2
where plan_id = p_plan_id
and sr_instance_id = p_inst_id
and transaction_id in
( select distinct mrr.supply_id
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.batch_number = p_batch_number );
end updateBatchReq;
procedure updateBatchInstReq(p_plan_id number, p_inst_id number, p_batch_number number,
p_start_date date, p_end_date date, p_firm_flag number, p_update_mode number,
p_return_status in OUT NOCOPY varchar2, p_out in OUT NOCOPY varchar2) is
l_resource_id number_arr;
select mrir.resource_id
bulk collect into l_resource_id
from msc_resource_instance_reqs mrir
where mrir.plan_id = p_plan_id
and mrir.sr_instance_id = p_inst_id
and mrir.batch_number = p_batch_number
for update of mrir.resource_id nowait;
select count(*)
into l_temp
from msc_resource_requirements mrr,
msc_resource_instance_reqs mrir
where mrir.plan_id = p_plan_id
and mrir.sr_instance_id = p_inst_id
and mrir.batch_number = p_batch_number
and mrir.plan_id = mrr.plan_id
and mrir.sr_instance_id = mrr.sr_instance_id
and mrir.organization_id = mrr.organization_id
and mrir.department_id = mrr.department_id
and mrir.resource_id = mrr.resource_id
and mrir.supply_id = mrr.supply_id
and mrir.operation_seq_num = mrr.operation_seq_num
and mrir.resource_seq_num = mrr.resource_seq_num
and nvl(mrir.orig_resource_seq_num, mbp_null_value) = nvl(mrr.orig_resource_seq_num, mbp_null_value)
and nvl(mrir.parent_seq_num, mbp_null_value) = nvl(mrr.parent_seq_num, mbp_null_value)
and nvl(mrir.parent_id, mbp_null_value) = nvl(mrr.parent_id, mbp_null_value)
and mrir.start_date = nvl(mrr.firm_start_date, mrr.start_date)
and mrir.end_date = nvl(mrr.firm_end_date, mrr.end_date)
and nvl(mrir.parent_id,2) = 2
and nvl(mrr.firm_flag,0) <> 0;
update msc_resource_instance_reqs
set status =0,
applied=2,
start_date = p_start_date,
end_date = p_end_date
where plan_id = p_plan_id
and sr_instance_id = p_inst_id
and batch_number = p_batch_number;
update msc_supplies
set status = 0,
applied = 2
where plan_id = p_plan_id
and sr_instance_id = p_inst_id
and transaction_id in
( select distinct mrir.supply_id
from msc_resource_instance_reqs mrir
where mrir.plan_id = p_plan_id
and mrir.sr_instance_id = p_inst_id
and mrir.batch_number = p_batch_number );
end updateBatchInstReq;
procedure updateResSeq(p_plan_id number, p_inst_id number,
p_group_sequence_id number, p_duration varchar2,
p_plan_start_date date, p_plan_end_date date,
p_return_status in OUT NOCOPY varchar2, p_out in OUT NOCOPY varchar2,
p_validate_flag boolean, p_node_type number ) is
l_resource_id number_arr;
select mrr.transaction_id, mrr.start_date, mrr.end_date,
mrr.firm_flag, mrr.firm_start_date, mrr.firm_end_date
from msc_resource_requirements mrr
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.group_sequence_id = p_group_seq ;
put_line(' updateResSeq in');
select mrr.resource_id
bulk collect into l_resource_id
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.group_sequence_id = p_group_sequence_id
for update of mrr.resource_id nowait;
msc_gantt_utils.updateReqSimu(p_plan_id, p_inst_id, l_trx_id,
l_firm_flag, l_start_date, l_end_date, l_firm_start_date, l_firm_end_date,
MOVE_MRR, p_return_status, p_out);
update msc_supplies
set status = 0,
applied = 2
where plan_id = p_plan_id
and sr_instance_id = p_inst_id
and transaction_id in
( select distinct mrr.supply_id
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.group_sequence_id = p_group_sequence_id );
put_line(' updateResSeq out');
end updateResSeq;
procedure updateReqFromAlt(p_plan_id number, p_inst_id number, p_simu_res_trx number,
p_alt_res_id number, p_alt_res_hours number, p_alt_res_alt_num number, p_alt_res_basis_type number,
p_alt_orig_res_seq_num number) is
l_firm_flag number;
select mrr.firm_flag
into l_firm_flag
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.transaction_id = p_simu_res_trx
and mrr.sr_instance_id = p_inst_id
for update of mrr.firm_flag nowait;
put_line(' updateReqFromAlt updating to alt_res_id '||p_alt_res_id);
update msc_resource_requirements
set status = 0,
applied=2,
resource_id = p_alt_res_id,
alternate_num = p_alt_res_alt_num,
--firm_flag = l_firm_flag,
resource_hours = p_alt_res_hours,
orig_resource_seq_num = p_alt_orig_res_seq_num,
basis_type = p_alt_res_basis_type
where plan_id = p_plan_id
and transaction_id = p_simu_res_trx
and sr_instance_id = p_inst_id;
end updateReqFromAlt;
procedure updateReqInstFromAlt(p_plan_id number, p_inst_id number, p_simu_res_trx number,
p_alt_res_id number, p_alt_res_instance_id number, p_serial_number varchar2,
p_alt_res_hours number, p_alt_res_alt_num number,
p_alt_orig_res_seq_num number) is
l_resource_id number;
select mrir.resource_id, resource_instance_hours
into l_resource_id, l_hours
from msc_resource_instance_reqs mrir
where mrir.plan_id = p_plan_id
and mrir.sr_instance_id = p_inst_id
and mrir.res_inst_transaction_id = p_simu_res_trx
for update of mrir.resource_id nowait;
update msc_resource_instance_reqs
set status = 0,
applied=2,
resource_id = p_alt_res_id,
res_instance_id = p_alt_res_instance_id,
serial_number = p_serial_number,
orig_resource_seq_num = nvl(p_alt_orig_res_seq_num,orig_resource_seq_num),
resource_instance_hours = nvl(p_alt_res_hours,resource_instance_hours)
where plan_id = p_plan_id
and res_inst_transaction_id = p_simu_res_trx
and sr_instance_id = p_inst_id;
end updateReqInstFromAlt;
procedure DeleteReqFromAlt(p_plan_id number, p_inst_id number, p_simu_res_trx number) is
l_firm_flag number;
select mrr.firm_flag
into l_firm_flag
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.transaction_id = p_simu_res_trx
for update of mrr.firm_flag nowait;
delete msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.transaction_id = p_simu_res_trx;
end DeleteReqFromAlt;
procedure DeleteReqInstFromAlt(p_plan_id number,
p_inst_id number, p_res_inst_trx number) is
l_resource_id number;
put_line('DeleteReqInstFromAlt in '||p_plan_id||' '||p_inst_id||' '||p_res_inst_trx);
select mrir.resource_id
into l_resource_id
from msc_resource_instance_reqs mrir
where mrir.plan_id = p_plan_id
and mrir.sr_instance_id = p_inst_id
and mrir.res_inst_transaction_id = p_res_inst_trx
for update of mrir.resource_id nowait;
delete msc_resource_instance_reqs mrir
where mrir.plan_id = p_plan_id
and mrir.sr_instance_id = p_inst_id
and mrir.res_inst_transaction_id = p_res_inst_trx;
end DeleteReqInstFromAlt;
function insertReqInstFromAlt(p_plan_id number, p_inst_id number,
p_simu_res_inst_trx number, p_alt_res_id number, p_alt_res_instance_id number,
p_serial_number varchar2, p_alt_res_hours number, p_alt_res_alt_num number,
p_from_node number,
p_alt_orig_res_seq_num number) return number is
l_trx_id number;
select count(*)
from msc_resource_instance_reqs
where plan_id = p_plan_id
and res_inst_transaction_id = l_trx_id;
select equipment_item_id
from msc_dept_res_instances
where plan_id = p_plan_id
and resource_id = p_alt_res_id
and res_instance_id = p_alt_res_instance_id
and nvl(serial_number,mbp_null_value_char) = nvl(p_serial_number,mbp_null_value_char);
put_line(' insertReqInstFromAlt plan inst trx '|| p_plan_id ||' '|| p_inst_id||' '|| p_simu_res_inst_trx);
select msc_resource_instance_reqs_s.nextval
into l_trx_id
from dual;
put_line(' insertReqInstFromAlt : trying to insert a row in mrir from mrir l_trx_id '||l_trx_id);
insert into msc_resource_instance_reqs(
RES_INST_TRANSACTION_ID, PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID,
SUPPLY_ID, DEPARTMENT_ID, RESOURCE_ID, RES_INSTANCE_ID, SERIAL_NUMBER, EQUIPMENT_ITEM_ID,
PARENT_ID, PARENT_SEQ_NUM, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM, ORIG_RESOURCE_SEQ_NUM,
SETUP_SEQUENCE_NUM, WIP_ENTITY_ID, START_DATE, END_DATE,
RESOURCE_INSTANCE_HOURS, CAPACITY_CONSUMED, CAPACITY_CONSUMED_RATIO, BATCH_NUMBER,
STATUS, APPLIED, UPDATED,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,LAST_UPDATE_LOGIN, REFRESH_NUMBER
)
select
l_trx_id, PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID,
SUPPLY_ID, DEPARTMENT_ID, p_alt_res_id, p_alt_res_instance_id, p_serial_number, l_equip_item_id,
PARENT_ID, PARENT_SEQ_NUM, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM, p_alt_orig_res_seq_num,
SETUP_SEQUENCE_NUM, WIP_ENTITY_ID, START_DATE, END_DATE,
p_alt_res_hours, CAPACITY_CONSUMED, CAPACITY_CONSUMED_RATIO, BATCH_NUMBER,
STATUS, APPLIED, UPDATED,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,LAST_UPDATE_LOGIN, REFRESH_NUMBER
from msc_resource_instance_reqs mrir
where mrir.plan_id = p_plan_id
and mrir.sr_instance_id = p_inst_id
and mrir.res_inst_transaction_id = p_simu_res_inst_trx;
put_line(' inserted '||l_temp||' rows ');
put_line(' insertReqInstFromAlt : trying to insert a row in mrir from mrr l_trx_id '||l_trx_id);
insert into msc_resource_instance_reqs(
RES_INST_TRANSACTION_ID, PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID,
SUPPLY_ID, DEPARTMENT_ID, RESOURCE_ID, RES_INSTANCE_ID, SERIAL_NUMBER, EQUIPMENT_ITEM_ID,
PARENT_ID, PARENT_SEQ_NUM, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM, ORIG_RESOURCE_SEQ_NUM,
--SETUP_SEQUENCE_NUM,
WIP_ENTITY_ID, START_DATE, END_DATE,
RESOURCE_INSTANCE_HOURS,
--CAPACITY_CONSUMED, CAPACITY_CONSUMED_RATIO,
BATCH_NUMBER, STATUS, APPLIED, UPDATED,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,LAST_UPDATE_LOGIN, REFRESH_NUMBER
)
select
l_trx_id, PLAN_ID, SR_INSTANCE_ID, ORGANIZATION_ID,
SUPPLY_ID, DEPARTMENT_ID, p_alt_res_id, p_alt_res_instance_id, p_serial_number, l_equip_item_id,
PARENT_ID, PARENT_SEQ_NUM, OPERATION_SEQ_NUM, RESOURCE_SEQ_NUM, ORIG_RESOURCE_SEQ_NUM,
--SETUP_SEQUENCE_NUM,
WIP_ENTITY_ID, START_DATE, END_DATE, p_alt_res_hours,
--CAPACITY_CONSUMED, CAPACITY_CONSUMED_RATIO,
BATCH_NUMBER, STATUS, APPLIED, UPDATED,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,LAST_UPDATE_LOGIN, REFRESH_NUMBER
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.transaction_id = p_simu_res_inst_trx;
put_line(' inserted '||l_temp||' rows ');
end insertReqInstFromAlt;
function insertReqFromAlt(p_plan_id number, p_inst_id number,
p_simu_res_trx number,
p_alt_res_id number,
p_alt_res_hours number,
p_alt_res_alt_num number,
p_alt_res_basis_type number,
p_alt_orig_res_seq_num number) return number is
l_trx_id number;
select msc_resource_instance_reqs_s.nextval
into l_trx_id
from dual;
insert into msc_resource_requirements(
TRANSACTION_ID, PLAN_ID, SUPPLY_ID, ORGANIZATION_ID, SR_INSTANCE_ID,
ROUTING_SEQUENCE_ID, OPERATION_SEQUENCE_ID, RESOURCE_SEQ_NUM, RESOURCE_ID,
DEPARTMENT_ID, ALTERNATE_NUM, START_DATE, END_DATE, BKT_START_DATE,
RESOURCE_HOURS, SET_UP, BKT_END_DATE, TEAR_DOWN, AGGREGATE_RESOURCE_ID,
SCHEDULE_FLAG, PARENT_ID, STD_OP_CODE, WIP_ENTITY_ID, ASSIGNED_UNITS, BASIS_TYPE,
OPERATION_SEQ_NUM, LOAD_RATE, DAILY_RESOURCE_HOURS, STATUS, APPLIED, UPDATED,
SUBST_RES_FLAG, REFRESH_NUMBER,
LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN,
SOURCE_ITEM_ID, ASSEMBLY_ITEM_ID, SUPPLY_TYPE, FIRM_START_DATE, FIRM_END_DATE, FIRM_FLAG,
ORIG_RESOURCE_SEQ_NUM
)
select l_trx_id, PLAN_ID, SUPPLY_ID, ORGANIZATION_ID, SR_INSTANCE_ID,
ROUTING_SEQUENCE_ID, OPERATION_SEQUENCE_ID,
RESOURCE_SEQ_NUM, p_alt_res_id, DEPARTMENT_ID, p_alt_res_alt_num,
START_DATE, END_DATE, BKT_START_DATE, p_alt_res_hours, SET_UP, BKT_END_DATE, TEAR_DOWN,
AGGREGATE_RESOURCE_ID, SCHEDULE_FLAG, PARENT_ID, STD_OP_CODE, WIP_ENTITY_ID, ASSIGNED_UNITS,
p_alt_res_basis_type, OPERATION_SEQ_NUM, LOAD_RATE, DAILY_RESOURCE_HOURS, 0, 2, UPDATED,
SUBST_RES_FLAG, REFRESH_NUMBER,
LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN,
SOURCE_ITEM_ID, ASSEMBLY_ITEM_ID, SUPPLY_TYPE, FIRM_START_DATE, FIRM_END_DATE, FIRM_RESOURCE,
p_alt_orig_res_seq_num
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.transaction_id = p_simu_res_trx
and mrr.sr_instance_id = p_inst_id;
end insertReqFromAlt;
procedure updateReq(p_plan_id number, p_inst_id number, p_trx_id number,
p_firm_type number, p_start_date date, p_end_date date,
p_firm_start_date date, p_firm_end_date date,
p_update_mode number) is
begin
-- 5153956 bugfix, dont update start_date, end_date
-- 4673468 bugfix
-- valid firm_type values
-- 0 Un Firm
-- 1 Firm Start Date
-- 2 Firm End Date
-- 3 Firm Resource
-- 4 Firm Start Date and End Date
-- 5 Firm Start Date and Resource
-- 6 Firm End Date and Resource
-- 7 Firm All
update msc_resource_requirements
set status =0,
applied=2,
firm_flag = decode(p_update_mode,
MOVE_MRR, firm_flag,
FIRM_MRR, p_firm_type,
firm_flag),
firm_start_date = decode(p_update_mode,
FIRM_MRR, decode(p_firm_type,
0, to_date(null),
3, to_date(null),
nvl(firm_start_date, start_date)),
MOVE_MRR, p_start_date,
firm_start_date),
firm_end_date = decode(p_update_mode,
FIRM_MRR, decode(p_firm_type,
0, to_date(null),
3, to_date(null),
nvl(firm_end_date, end_date)),
MOVE_MRR, p_end_date,
firm_end_date),
--start_date = p_start_date,
--end_date = p_end_date,
resource_hours = decode(p_update_mode,
FIRM_MRR, resource_hours,
MOVE_MRR, resource_hours + (assigned_units *
(((p_end_date - p_start_date) * 24) - ((end_date - start_date) * 24))),
resource_hours),
batch_number = decode(p_update_mode, MOVE_MRR, to_number(null), batch_number)
where plan_id = p_plan_id
and transaction_id = p_trx_id
and sr_instance_id = p_inst_id;
end updateReq;
procedure updateReqSimu(p_plan_id number, p_inst_id number, p_trx_id number,
p_firm_type number, p_start_date in out nocopy date, p_end_date in out nocopy date,
p_firm_start_date date, p_firm_end_date date,
p_update_mode number,
p_return_status in OUT NOCOPY varchar2,
p_out in OUT NOCOPY varchar2) is
l_transaction_id number;
select mrr.resource_id
into l_res_id
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.transaction_id = l_transaction_id
and mrr.sr_instance_id = l_instance_id
for update of mrr.firm_flag nowait;
if ( p_update_mode = FIRM_MRR ) then
-- update requirement
msc_gantt_utils.updateReq(p_plan_id, l_instance_id, l_transaction_id,
p_firm_type, p_start_date, p_end_date, p_firm_start_date, p_firm_end_date, p_update_mode);
elsif ( p_update_mode = MOVE_MRR ) then
moveOneResource(p_plan_id, l_transaction_id, l_instance_id,
p_start_date, p_end_date, p_return_status, p_out, RES_NODE);
end updateReqSimu;
procedure updateReqInst(p_plan_id number, p_inst_id number, p_trx_id number,
p_start_date date, p_end_date date) is
begin
update msc_resource_instance_reqs
set status =0,
applied=2,
start_date = p_start_date,
end_date = p_end_date,
batch_number = to_number(null),
resource_instance_hours = resource_instance_hours
+ (((p_end_date - p_start_date) * 24) - ((end_date - start_date) * 24))
where plan_id = p_plan_id
and res_inst_transaction_id = p_trx_id
and sr_instance_id = p_inst_id;
end updateReqInst;
select nvl(mrr.firm_start_date, mrr.start_date),
nvl(mrr.firm_end_date, mrr.end_date)
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.transaction_id = p_res_trx_id;
update msc_resource_instance_reqs
set status =0,
applied=2,
start_date = l_start_date,
end_date = l_end_date
where plan_id = p_plan_id
and res_inst_transaction_id = p_res_inst_trx_id
and sr_instance_id = p_inst_id;
procedure updateSupplies(p_plan_id number,
p_trx_id number, p_update_type number,
p_firm_type number default null,
p_firm_date date default null,
p_firm_qty number default null) is
begin
update msc_supplies
set status = 0,
applied = 2,
firm_planned_type = decode(p_update_type,
FIRM_SUPPLY, p_firm_type,
FIRM_ALL_SUPPLY, p_firm_type,
firm_planned_type),
firm_date = decode(p_update_type,
FIRM_ALL_SUPPLY, p_firm_date,
firm_date),
firm_quantity = decode(p_update_type,
FIRM_ALL_SUPPLY, p_firm_qty,
firm_quantity)
where plan_id = p_plan_id
and transaction_id = p_trx_id;
end updateSupplies;
select mrr.operation_seq_num,
nvl(mrr.schedule_flag, 0) schedule_flag,
decode(ms.order_type, 27, 1, ms.firm_planned_type) firm_planned_type,
sysdate theDate,
getMTQTime(p_transaction_id, p_plan_id, p_instance_id) mtq_time
from msc_resource_requirements mrr,
msc_supplies ms
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_instance_id
and mrr.transaction_id = p_transaction_id
and ms.plan_id = mrr.plan_id
and ms.transaction_id = mrr.supply_id
and ms.sr_instance_id = mrr.sr_instance_id;
select mrr2.operation_seq_num,
mrr2.resource_seq_num,
mrr2.transaction_id,
nvl(mrr2.schedule_flag, 0) schedule_flag,
decode(nvl(mrr2.firm_flag,0),
NO_FIRM, mrr2.start_date,
FIRM_RESOURCE, mrr2.start_date,
FIRM_END, mrr2.firm_end_date -
(nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
FIRM_END_RES, mrr2.firm_end_date -
(nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
nvl(mrr2.firm_start_date, mrr2.start_date)) start_date,
decode(nvl(mrr2.firm_flag,0),
NO_FIRM, nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24),
FIRM_RESOURCE, nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24),
FIRM_START, mrr2.firm_start_date +
(nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
FIRM_START_RES, mrr2.firm_start_date +
(nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date,
msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
from msc_resource_requirements mrr1,
msc_resource_requirements mrr2,
msc_routings mr
where mrr1.plan_id = p_plan_id
and mrr1.transaction_id = p_transaction_id
and mrr1.sr_instance_id = p_instance_id
and mrr2.plan_id = mrr1.plan_id
and mrr2.supply_id = mrr1.supply_id
and mrr2.sr_instance_id = mrr1.sr_instance_id
and nvl(mrr2.parent_id,2) =2
and mr.plan_id = mrr1.plan_id
and mr.sr_instance_id = mrr1.sr_instance_id
and mr.routing_sequence_id = mrr1.routing_sequence_id
and (((nvl(mr.cfm_routing_flag,2) <> 3 and mrr2.operation_seq_num < mrr1.operation_seq_num)
or ( nvl(mr.cfm_routing_flag,2) = 3
and mrr2.operation_sequence_id in (
select mon.from_op_seq_id
from msc_operation_networks mon
where mon.plan_id = mrr1.plan_id
and mon.sr_instance_id = mrr1.sr_instance_id
and mon.routing_sequence_id = mrr1.routing_sequence_id
and mon.to_op_seq_id = mrr1.operation_sequence_id
))) or
(mrr2.operation_seq_num = mrr1.operation_seq_num and
mrr2.resource_seq_num < mrr1.resource_seq_num))
and (mrr2.firm_start_date is not null or mrr2.firm_end_date is not null )
and mrr2.firm_flag in (FIRM_START,FIRM_END,FIRM_START_END,FIRM_START_RES,FIRM_END_RES,FIRM_ALL)
order by mrr2.operation_seq_num desc, mrr2.resource_seq_num desc;
select mrr2.operation_seq_num,
mrr2.resource_seq_num,
mrr2.transaction_id,
nvl(mrr2.schedule_flag, 0) schedule_flag,
decode(nvl(mrr2.firm_flag,0),
NO_FIRM, mrr2.start_date,
FIRM_RESOURCE, mrr2.start_date,
FIRM_END, mrr2.firm_end_date -
(nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
FIRM_END_RES, mrr2.firm_end_date -
(nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
nvl(mrr2.firm_start_date, mrr2.start_date)) start_date,
decode(nvl(mrr2.firm_flag,0),
NO_FIRM, nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24),
FIRM_RESOURCE, nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24),
FIRM_START, mrr2.firm_start_date +
(nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
FIRM_START_RES, mrr2.firm_start_date +
(nvl(mrr2.end_date, mrr2.start_date+mrr2.resource_hours/24) - mrr2.start_date),
nvl(nvl(mrr2.firm_end_date, mrr2.end_date), mrr2.start_date+mrr2.resource_hours/24)) end_date
from msc_resource_requirements mrr1,
msc_resource_requirements mrr2,
msc_routings mr
where mrr1.plan_id = p_plan_id
and mrr1.transaction_id = p_transaction_id
and mrr1.sr_instance_id = p_instance_id
and mrr2.plan_id = mrr1.plan_id
and mrr2.supply_id = mrr1.supply_id
and mrr2.sr_instance_id = mrr1.sr_instance_id
and nvl(mrr2.parent_id,2) =2
and mr.plan_id = mrr1.plan_id
and mr.sr_instance_id = mrr1.sr_instance_id
and mr.routing_sequence_id = mrr1.routing_sequence_id
and (((nvl(mr.cfm_routing_flag,2) <> 3 and mrr2.operation_seq_num > mrr1.operation_seq_num)
or ( nvl(mr.cfm_routing_flag,2) = 3 and mrr2.operation_sequence_id in (
select mon.to_op_seq_id from msc_operation_networks mon
where mon.plan_id = mrr1.plan_id
and mon.sr_instance_id = mrr1.sr_instance_id
and mon.routing_sequence_id = mrr1.routing_sequence_id
and mon.from_op_seq_id = mrr1.operation_sequence_id
))) or
(mrr2.operation_seq_num = mrr1.operation_seq_num and
mrr2.resource_seq_num > mrr1.resource_seq_num))
and (mrr2.firm_start_date is not null or
mrr2.firm_end_date is not null )
and mrr2.firm_flag in (FIRM_START,FIRM_END,FIRM_START_END,FIRM_START_RES,
FIRM_END_RES,FIRM_ALL)
order by mrr2.operation_seq_num, mrr2.resource_seq_num;
select mrr.operation_seq_num,
nvl(mrr.schedule_flag, 0) schedule_flag,
decode(ms.order_type, 27, 1, ms.firm_planned_type) firm_planned_type,
sysdate theDate,
getMTQTime(p_transaction_id, p_plan_id, p_instance_id) mtq_time
from msc_resource_requirements mrr,
msc_resource_instance_reqs mrir,
msc_supplies ms
where mrir.plan_id = p_plan_id
and mrir.sr_instance_id = p_instance_id
and mrir.res_inst_transaction_id = p_transaction_id
and mrir.plan_id = mrr.plan_id
and mrir.sr_instance_id = mrr.sr_instance_id
and mrir.organization_id = mrr.organization_id
and mrir.department_id = mrr.department_id
and mrir.resource_id = mrr.resource_id
and mrir.supply_id = mrr.supply_id
and mrir.operation_seq_num = mrr.operation_seq_num
and mrir.resource_seq_num = mrr.resource_seq_num
and nvl(mrir.orig_resource_seq_num, mbp_null_value) = nvl(mrr.orig_resource_seq_num, mbp_null_value)
and nvl(mrir.parent_seq_num, mbp_null_value) = nvl(mrr.parent_seq_num, mbp_null_value)
and nvl(mrir.parent_id, mbp_null_value) = nvl(mrr.parent_id, mbp_null_value)
and mrir.start_date = nvl(mrr.firm_start_date, mrr.start_date)
and mrir.end_date = nvl(mrr.firm_end_date, mrr.end_date)
and ms.plan_id = mrr.plan_id
and ms.transaction_id = mrr.supply_id
and ms.sr_instance_id = mrr.sr_instance_id;
select mrr2.operation_seq_num,
mrr2.resource_seq_num,
mrr2.transaction_id,
nvl(mrr2.schedule_flag, 0) schedule_flag,
decode(nvl(mrr2.firm_flag,0),
NO_FIRM, mrir2.start_date,
FIRM_RESOURCE, mrir2.start_date,
FIRM_END, mrr2.firm_end_date -
(nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
FIRM_END_RES, mrr2.firm_end_date -
(nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
nvl(mrr2.firm_start_date, mrir2.start_date)) start_date,
decode(nvl(mrr2.firm_flag,0),
NO_FIRM, nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24),
FIRM_RESOURCE, nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24),
FIRM_START, mrr2.firm_start_date +
(nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
FIRM_START_RES, mrr2.firm_start_date +
(nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
nvl(nvl(mrr2.firm_end_date, mrir2.end_date), mrir2.start_date+mrir2.resource_instance_hours/24)) end_date,
msc_gantt_utils.getMTQTime(mrr2.transaction_id, p_plan_id, p_instance_id) mtq_time
from msc_resource_requirements mrr1,
msc_resource_instance_reqs mrir1,
msc_resource_requirements mrr2,
msc_resource_instance_reqs mrir2,
msc_routings mr
where mrir1.plan_id = p_plan_id
and mrir1.res_inst_transaction_id = p_transaction_id
and mrir1.sr_instance_id = p_instance_id
and mrir1.plan_id = mrr1.plan_id
and mrir1.sr_instance_id = mrr1.sr_instance_id
and mrir1.organization_id = mrr1.organization_id
and mrir1.department_id = mrr1.department_id
and mrir1.resource_id = mrr1.resource_id
and mrir1.supply_id = mrr1.supply_id
and mrir1.operation_seq_num = mrr1.operation_seq_num
and mrir1.resource_seq_num = mrr1.resource_seq_num
and nvl(mrir1.orig_resource_seq_num, mbp_null_value) = nvl(mrr1.orig_resource_seq_num, mbp_null_value)
and nvl(mrir1.parent_seq_num, mbp_null_value) = nvl(mrr1.parent_seq_num, mbp_null_value)
and nvl(mrir1.parent_id, mbp_null_value) = nvl(mrr1.parent_id, mbp_null_value)
and mrir1.start_date = nvl(mrr1.firm_start_date, mrr1.start_date)
and mrir1.end_date = nvl(mrr1.firm_end_date, mrr1.end_date)
and mrr2.plan_id = mrr1.plan_id
and mrr2.supply_id = mrr1.supply_id
and mrr2.sr_instance_id = mrr1.sr_instance_id
and nvl(mrr2.parent_id,2) =2
and mrir2.plan_id = mrr2.plan_id
and mrir2.sr_instance_id = mrr2.sr_instance_id
and mrir2.organization_id = mrr2.organization_id
and mrir2.department_id = mrr2.department_id
and mrir2.resource_id = mrr2.resource_id
and mrir2.supply_id = mrr2.supply_id
and mrir2.operation_seq_num = mrr2.operation_seq_num
and mrir2.resource_seq_num = mrr2.resource_seq_num
and nvl(mrir2.orig_resource_seq_num, mbp_null_value) = nvl(mrr2.orig_resource_seq_num, mbp_null_value)
and nvl(mrir2.parent_seq_num, mbp_null_value) = nvl(mrr2.parent_seq_num, mbp_null_value)
and nvl(mrir2.parent_id, mbp_null_value) = nvl(mrr2.parent_id, mbp_null_value)
and mrir2.start_date = nvl(mrr2.firm_start_date, mrr2.start_date)
and mrir2.end_date = nvl(mrr2.firm_end_date, mrr2.end_date)
and mr.plan_id = mrr1.plan_id
and mr.sr_instance_id = mrr1.sr_instance_id
and mr.routing_sequence_id = mrr1.routing_sequence_id
and (((nvl(mr.cfm_routing_flag,2) <> 3 and mrr2.operation_seq_num < mrr1.operation_seq_num)
or ( nvl(mr.cfm_routing_flag,2) = 3
and mrr2.operation_sequence_id in (
select mon.from_op_seq_id
from msc_operation_networks mon
where mon.plan_id = mrr1.plan_id
and mon.sr_instance_id = mrr1.sr_instance_id
and mon.routing_sequence_id = mrr1.routing_sequence_id
and mon.to_op_seq_id = mrr1.operation_sequence_id
))) or
(mrr2.operation_seq_num = mrr1.operation_seq_num and
mrr2.resource_seq_num < mrr1.resource_seq_num))
and (mrr2.firm_start_date is not null or mrr2.firm_end_date is not null )
and mrr2.firm_flag in (FIRM_START,FIRM_END,FIRM_START_END,FIRM_START_RES,FIRM_END_RES,FIRM_ALL)
order by mrr2.operation_seq_num desc, mrr2.resource_seq_num desc;
select mrr2.operation_seq_num,
mrr2.resource_seq_num,
mrr2.transaction_id,
nvl(mrr2.schedule_flag, 0) schedule_flag,
decode(nvl(mrr2.firm_flag,0),
NO_FIRM, mrir2.start_date,
FIRM_RESOURCE, mrir2.start_date,
FIRM_END, mrr2.firm_end_date -
(nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
FIRM_END_RES, mrr2.firm_end_date -
(nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
nvl(mrr2.firm_start_date, mrir2.start_date)) start_date,
decode(nvl(mrr2.firm_flag,0),
NO_FIRM, nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24),
FIRM_RESOURCE, nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24),
FIRM_START, mrr2.firm_start_date +
(nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
FIRM_START_RES, mrr2.firm_start_date +
(nvl(mrir2.end_date, mrir2.start_date+mrir2.resource_instance_hours/24) - mrir2.start_date),
nvl(nvl(mrr2.firm_end_date, mrir2.end_date), mrir2.start_date+mrir2.resource_instance_hours/24)) end_date
from msc_resource_requirements mrr1,
msc_resource_instance_reqs mrir1,
msc_resource_requirements mrr2,
msc_resource_instance_reqs mrir2,
msc_routings mr
where mrir1.plan_id = p_plan_id
and mrir1.res_inst_transaction_id = p_transaction_id
and mrir1.sr_instance_id = p_instance_id
and mrir1.plan_id = mrr1.plan_id
and mrir1.sr_instance_id = mrr1.sr_instance_id
and mrir1.organization_id = mrr1.organization_id
and mrir1.department_id = mrr1.department_id
and mrir1.resource_id = mrr1.resource_id
and mrir1.supply_id = mrr1.supply_id
and mrir1.resource_seq_num = mrr1.resource_seq_num
and mrir1.operation_seq_num = mrr1.operation_seq_num
and nvl(mrir1.orig_resource_seq_num, mbp_null_value) = nvl(mrr1.orig_resource_seq_num, mbp_null_value)
and nvl(mrir1.parent_seq_num, mbp_null_value) = nvl(mrr1.parent_seq_num, mbp_null_value)
and nvl(mrir1.parent_id, mbp_null_value) = nvl(mrr1.parent_id, mbp_null_value)
and mrir1.start_date = nvl(mrr1.firm_start_date, mrr1.start_date)
and mrir1.end_date = nvl(mrr1.firm_end_date, mrr1.end_date)
and mrr2.plan_id = mrr1.plan_id
and mrr2.supply_id = mrr1.supply_id
and mrr2.sr_instance_id = mrr1.sr_instance_id
and nvl(mrr2.parent_id,2) =2
and mrir2.plan_id = mrr2.plan_id
and mrir2.sr_instance_id = mrr2.sr_instance_id
and mrir2.organization_id = mrr2.organization_id
and mrir2.department_id = mrr2.department_id
and mrir2.resource_id = mrr2.resource_id
and mrir2.supply_id = mrr2.supply_id
and mrir2.operation_seq_num = mrr2.operation_seq_num
and mrir2.resource_seq_num = mrr2.resource_seq_num
and nvl(mrir2.orig_resource_seq_num, mbp_null_value) = nvl(mrr2.orig_resource_seq_num, mbp_null_value)
and nvl(mrir2.parent_seq_num, mbp_null_value) = nvl(mrr2.parent_seq_num, mbp_null_value)
and nvl(mrir2.parent_id, mbp_null_value) = nvl(mrr2.parent_id, mbp_null_value)
and mrir2.start_date = nvl(mrr2.firm_start_date, mrr2.start_date)
and mrir2.end_date = nvl(mrr2.firm_end_date, mrr2.end_date)
and mr.plan_id = mrr1.plan_id
and mr.sr_instance_id = mrr1.sr_instance_id
and mr.routing_sequence_id = mrr1.routing_sequence_id
and (((nvl(mr.cfm_routing_flag,2) <> 3 and mrr2.operation_seq_num > mrr1.operation_seq_num)
or ( nvl(mr.cfm_routing_flag,2) = 3 and mrr2.operation_sequence_id in (
select mon.to_op_seq_id from msc_operation_networks mon
where mon.plan_id = mrr1.plan_id
and mon.sr_instance_id = mrr1.sr_instance_id
and mon.routing_sequence_id = mrr1.routing_sequence_id
and mon.from_op_seq_id = mrr1.operation_sequence_id
))) or
(mrr2.operation_seq_num = mrr1.operation_seq_num and
mrr2.resource_seq_num > mrr1.resource_seq_num))
and (mrr2.firm_start_date is not null or
mrr2.firm_end_date is not null )
and mrr2.firm_flag in (FIRM_START,FIRM_END,FIRM_START_END,FIRM_START_RES,
FIRM_END_RES,FIRM_ALL)
order by mrr2.operation_seq_num, mrr2.resource_seq_num;
msc_gantt_utils.updateReq(p_plan_id, p_instance_id, ll_res_trx_id,
p_firm_type, l_start, l_end, l_firm_start, l_firm_end, FIRM_MRR);
msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
msc_gantt_utils.updateReqSimu(p_plan_id, p_instance_id, ll_res_trx_id,
p_firm_type, l_start, l_end, l_firm_start, l_firm_end, FIRM_MRR, p_return_status, p_out);
select max(nvl(mrr.group_sequence_id,0))+1
into p_new_group_seq_id
from msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = l_inst_id;
update msc_resource_requirements
set group_sequence_id = decode(p_firm_type,
0, to_number(null),
p_new_group_seq_id),
group_sequence_number = decode(p_firm_type,
0, to_number(null),
p_new_group_seq_number)
where plan_id = p_plan_id
and sr_instance_id = l_inst_id
and transaction_id = ll_res_trx_id ;
msc_gantt_utils.updateBatchReq(p_plan_id, p_instance_id, l_batch_number,
to_date(null), to_date(null), p_firm_type, FIRM_MRR, p_return_status, p_out);
msc_gantt_utils.updateBatchInstReq(p_plan_id, p_instance_id, l_batch_number,
to_date(null), to_date(null), p_firm_type, FIRM_MRR, p_return_status, p_out);
msc_gantt_utils.updateSupplies(p_plan_id, p_transaction_id, FIRM_SUPPLY, p_firm_type);
select distinct mor.alternate_number || COLON_SEPARATOR ||
msc_get_name.resource_code(mor.resource_id, mrr.department_id,
mrr.organization_id, mrr.plan_id, mrr.sr_instance_id),
mor.resource_id,
mbp_null_value res_instance_id,
mbp_null_value_char serial_number,
mor.alternate_number
from msc_operation_resources mor,
msc_resource_requirements mrr
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mor.plan_id = mrr.plan_id
and mor.routing_sequence_id = mrr.routing_sequence_id
and mor.sr_instance_id = mrr.sr_instance_id
and mor.operation_sequence_id = mrr.operation_sequence_id
and mor.resource_seq_num = mrr.resource_seq_num
and mor.alternate_number <> mrr.alternate_num
and mor.principal_flag <> 2
union all --5478582 bugfix
select distinct mjor.alternate_num || COLON_SEPARATOR ||
msc_get_name.resource_code(mjor.resource_id, mrr.department_id,
mrr.organization_id, mrr.plan_id, mrr.sr_instance_id),
mjor.resource_id,
mbp_null_value res_instance_id,
mbp_null_value_char serial_number,
mjor.alternate_num
from msc_job_op_resources mjor,
msc_resource_requirements mrr
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mrr.routing_sequence_id is null
and mjor.plan_id = mrr.plan_id
and mjor.transaction_id = mrr.supply_id
and mjor.sr_instance_id = mrr.sr_instance_id
and mjor.operation_seq_num = mrr.operation_seq_num
and mjor.resource_seq_num = mrr.resource_seq_num
and mjor.alternate_num <> mrr.alternate_num
and mjor.resource_id <> -1
and mjor.principal_flag <> 2;
select distinct mor.alternate_number
|| COLON_SEPARATOR || msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
mdri.resource_id,
mdri.res_instance_id,
mdri.serial_number,
mor.alternate_number
from msc_operation_resources mor,
msc_resource_requirements mrr,
msc_dept_res_instances mdri
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mrr.assigned_units = 1
and mor.plan_id = mrr.plan_id
and mor.routing_sequence_id = mrr.routing_sequence_id
and mor.sr_instance_id = mrr.sr_instance_id
and mor.operation_sequence_id = mrr.operation_sequence_id
and mor.resource_seq_num = mrr.resource_seq_num
and mor.alternate_number <> mrr.alternate_num
and mor.principal_flag <> 2
and mor.plan_id = mdri.plan_id
and mor.sr_instance_id = mdri.sr_instance_id
and mor.resource_id = mdri.resource_id
union all --5478582 bugfix
select distinct mjor.alternate_num
|| COLON_SEPARATOR || msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
mdri.resource_id,
mdri.res_instance_id,
mdri.serial_number,
mjor.alternate_num
from msc_job_op_resources mjor,
msc_resource_requirements mrr,
msc_dept_res_instances mdri
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mrr.routing_sequence_id is null
and mrr.assigned_units = 1
and mjor.plan_id = mrr.plan_id
and mjor.transaction_id = mrr.supply_id
and mjor.sr_instance_id = mrr.sr_instance_id
and mjor.operation_seq_num = mrr.operation_seq_num
and mjor.resource_seq_num = mrr.resource_seq_num
and mjor.alternate_num <> mrr.alternate_num
and mjor.resource_id <> -1
and mjor.principal_flag <> 2
and mjor.plan_id = mdri.plan_id
and mjor.sr_instance_id = mdri.sr_instance_id
and mjor.resource_id = mdri.resource_id;
select distinct mor.alternate_number
|| COLON_SEPARATOR ||msc_get_name.resource_code(mor.resource_id, mrr.department_id,
mrr.organization_id, mrr.plan_id, mrr.sr_instance_id),
mor.resource_id,
mbp_null_value res_instance_id,
mbp_null_value_char serial_number,
mor.alternate_number
from msc_operation_resources mor,
msc_resource_requirements mrr
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mor.plan_id = mrr.plan_id
and mor.routing_sequence_id = mrr.routing_sequence_id
and mor.sr_instance_id = mrr.sr_instance_id
and mor.operation_sequence_id = mrr.operation_sequence_id
and mor.resource_seq_num = mrr.resource_seq_num
and mor.alternate_number <> mrr.alternate_num
and mor.principal_flag <> 2
union all --5478582 bugfix
select distinct mjor.alternate_num
|| COLON_SEPARATOR ||msc_get_name.resource_code(mjor.resource_id, mrr.department_id,
mrr.organization_id, mrr.plan_id, mrr.sr_instance_id),
mjor.resource_id,
mbp_null_value res_instance_id,
mbp_null_value_char serial_number,
mjor.alternate_num
from msc_job_op_resources mjor,
msc_resource_requirements mrr
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mrr.routing_sequence_id is null
and mjor.plan_id = mrr.plan_id
and mjor.transaction_id = mrr.supply_id
and mjor.sr_instance_id = mrr.sr_instance_id
and mjor.operation_seq_num = mrr.operation_seq_num
and mjor.resource_seq_num = mrr.resource_seq_num
and mjor.alternate_num <> mrr.alternate_num
and mjor.resource_id <> -1
and mjor.principal_flag <> 2;
select distinct mor.alternate_number ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
mdri.resource_id,
mdri.res_instance_id,
mdri.serial_number,
mor.alternate_number
from msc_operation_resources mor,
msc_resource_requirements mrr,
msc_dept_res_instances mdri
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mor.plan_id = mrr.plan_id
and mor.routing_sequence_id = mrr.routing_sequence_id
and mor.sr_instance_id = mrr.sr_instance_id
and mor.operation_sequence_id = mrr.operation_sequence_id
and mor.resource_seq_num = mrr.resource_seq_num
and mor.alternate_number <> mrr.alternate_num
and mor.principal_flag <> 2
and mor.plan_id = mdri.plan_id
and mor.sr_instance_id = mdri.sr_instance_id
and mor.resource_id = mdri.resource_id
union all --5513960 bugfix
select distinct mrr.alternate_num ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
mdri.resource_id,
mdri.res_instance_id,
mdri.serial_number,
mrr.alternate_num
from msc_resource_requirements mrr,
msc_dept_res_instances mdri
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mrr.plan_id = mdri.plan_id
and mrr.sr_instance_id = mdri.sr_instance_id
and mrr.organization_id = mdri.organization_id
and mrr.department_id = mdri.department_id
and mrr.resource_id = mdri.resource_id
and nvl(mdri.serial_number,mbp_null_value_char) <> nvl(p_serial_number,mbp_null_value_char)
--and nvl(mdri.equipment_item_id,mbp_null_value) <> nvl(p_equipment_item_id,mbp_null_value)
union all --5478582 bugfix
select distinct mjor.alternate_num ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
mdri.resource_id, mdri.res_instance_id, mdri.serial_number),
mdri.resource_id,
mdri.res_instance_id,
mdri.serial_number,
mjor.alternate_num
from msc_job_op_resources mjor,
msc_resource_requirements mrr,
msc_dept_res_instances mdri
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mrr.routing_sequence_id is null
and mjor.plan_id = mrr.plan_id
and mjor.transaction_id = mrr.supply_id
and mjor.sr_instance_id = mrr.sr_instance_id
and mjor.operation_seq_num = mrr.operation_seq_num
and mjor.resource_seq_num = mrr.resource_seq_num
and mjor.alternate_num <> mrr.alternate_num
and mjor.resource_id <> -1
and mjor.principal_flag <> 2
and mjor.plan_id = mdri.plan_id
and mjor.sr_instance_id = mdri.sr_instance_id
and mjor.resource_id = mdri.resource_id;
select nvl(mrr.firm_flag,no_firm)
from msc_resource_requirements mrr
where mrr.plan_id = p_plan
and mrr.transaction_id = p_trx
and mrr.sr_instance_id = p_inst;
select mors.activity_group_id, mrr.routing_sequence_id,
mrr.operation_sequence_id, mrr.resource_seq_num
from msc_operation_resource_seqs mors,
msc_resource_requirements mrr
where mrr.plan_id = p_plan
and mrr.transaction_id = p_trx
and mrr.sr_instance_id = p_inst
and mors.plan_id = mrr.plan_id
and mors.routing_sequence_id = mrr.routing_sequence_id
and mors.operation_sequence_id = mrr.operation_sequence_id
and mors.resource_seq_num = mrr.resource_seq_num
and mors.sr_instance_id = mrr.sr_instance_id;
select mrir.res_instance_id, mrir.serial_number, mrir.equipment_item_id
from msc_resource_instance_reqs mrir
where mrir.plan_id = p_plan
and mrir.sr_instance_id = p_inst
and mrir.res_inst_transaction_id = p_trx;
select count(*)
into l_total_seqs
from (select distinct mors.resource_seq_num
from msc_operation_resource_seqs mors
where mors.plan_id = p_plan_id
and mors.routing_sequence_id = l_rout_seq
and mors.operation_sequence_id = l_op_seq
and mors.sr_instance_id = p_instance_id
and mors.activity_group_id = l_act_group);
select count(*)
into l_avail_seqs
from (select distinct mors.resource_seq_num
from msc_operation_resource_seqs mors,
msc_operation_resources mor
where mors.plan_id = p_plan_id
and mors.sr_instance_id = p_instance_id
and mors.operation_sequence_id = l_op_seq
and mors.routing_sequence_id = l_rout_seq
and mors.activity_group_id = l_act_group
and mor.plan_id = p_plan_id
and mor.routing_sequence_id = mors.routing_sequence_id
and mor.operation_sequence_id = mors.operation_sequence_id
and mor.sr_instance_id = p_instance_id
and mor.resource_seq_num = mors.resource_seq_num
and mor.alternate_number = alt_number);
select msc_get_name.resource_code(mor.resource_id, mrr.department_id,
mrr.organization_id, mrr.plan_id, mrr.sr_instance_id) resource_code,
mor.resource_id resource_id,
mbp_null_value res_instance_id,
mbp_null_value_char serial_number
from msc_operation_resources mor,
msc_resource_requirements mrr
where mrr.plan_id = p_plan_id
and mrr.transaction_id = p_trx_id
and mrr.sr_instance_id = p_inst_id
and mor.plan_id = mrr.plan_id
and mor.sr_instance_id = mrr.sr_instance_id
and mor.routing_sequence_id = mrr.routing_sequence_id
and mor.operation_sequence_id = mrr.operation_sequence_id
and mor.resource_seq_num = mrr.resource_seq_num
and mor.alternate_number = mrr.alternate_num
and mor.resource_id <> mrr.resource_id;
select mor.alternate_number ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
mdri.sr_instance_id, mdri.organization_id, mdri.department_id,
mdri.resource_id, mdri.res_instance_id, mdri.serial_number) resource_code,
mdri.resource_id,
mdri.res_instance_id,
mdri.serial_number
from msc_operation_resources mor,
msc_resource_requirements mrr,
msc_dept_res_instances mdri
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mor.plan_id = mrr.plan_id
and mor.sr_instance_id = mrr.sr_instance_id
and mor.routing_sequence_id = mrr.routing_sequence_id
and mor.operation_sequence_id = mrr.operation_sequence_id
and mor.resource_seq_num = mrr.resource_seq_num
and mor.alternate_number = mrr.alternate_num
and mor.resource_id <> mrr.resource_id
and mor.plan_id = mdri.plan_id
and mor.sr_instance_id = mdri.sr_instance_id
and mor.resource_id = mdri.resource_id;
select mor.alternate_number ||':'||msc_get_name.resource_code(mor.resource_id, mrr.department_id,
mrr.organization_id, mrr.plan_id, mrr.sr_instance_id) resource_code,
mor.resource_id,
mbp_null_value res_instance_id,
mbp_null_value_char serial_number
from msc_operation_resources mor,
msc_resource_requirements mrr
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mor.plan_id = mrr.plan_id
and mor.sr_instance_id = mrr.sr_instance_id
and mor.routing_sequence_id = mrr.routing_sequence_id
and mor.operation_sequence_id = mrr.operation_sequence_id
and mor.resource_seq_num = mrr.resource_seq_num
and mor.alternate_number = mrr.alternate_num
and mor.resource_id <> mrr.resource_id;
select mor.alternate_number ||':'||msc_gantt_utils.getDeptResInstCode(mdri.plan_id,
mdri.sr_instance_id, mdri.organization_id,
mdri.department_id, mdri.resource_id, mdri.res_instance_id, mdri.serial_number) resource_code,
mdri.resource_id,
mdri.res_instance_id,
mdri.serial_number
from msc_operation_resources mor,
msc_resource_requirements mrr,
msc_dept_res_instances mdri
where mrr.plan_id = p_plan
and mrr.sr_instance_id = p_inst
and mrr.transaction_id = p_trx
and mor.plan_id = mrr.plan_id
and mor.routing_sequence_id = mrr.routing_sequence_id
and mor.sr_instance_id = mrr.sr_instance_id
and mor.operation_sequence_id = mrr.operation_sequence_id
and mor.resource_seq_num = mrr.resource_seq_num
and mor.alternate_number = mrr.alternate_num
and mor.resource_id <> mrr.resource_id
and mor.plan_id = mdri.plan_id
and mor.sr_instance_id = mdri.sr_instance_id
and mor.resource_id = mdri.resource_id;
select nvl(new_order_quantity,0)
into l_quan
from msc_supplies
where plan_id = p_plan_id
and transaction_id = p_supply_id
for update of firm_date nowait;
msc_gantt_utils.updateSupplies(p_plan_id, p_supply_id,
FIRM_ALL_SUPPLY, SYS_YES, p_end_date, l_quan);
msc_gantt_utils.updateReq(p_plan_id, p_instance_id, ll_res_trx_id,
l_firm_flag, l_start, l_end, l_firm_start, l_firm_end, MOVE_MRR);
msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
msc_gantt_utils.updateReqInst(p_plan_id, p_instance_id, ll_res_inst_trx_id, l_start, l_end);
msc_gantt_utils.updateSupplies(p_plan_id, l_supply_id, TOUCH_SUPPLY);
msc_gantt_utils.updateBatchReq(p_plan_id, p_instance_id, l_batch_number,
p_start_date, p_end_date, l_firm_flag, MOVE_MRR, p_return_status, p_out);
msc_gantt_utils.updateBatchInstReq(p_plan_id, p_instance_id, l_batch_number,
p_start_date, p_end_date, l_firm_flag, MOVE_MRR, p_return_status, p_out);
msc_gantt_utils.updateResSeq(p_plan_id, p_instance_id,
l_group_sequence_id, p_duration, p_plan_start_date, p_plan_end_date,
p_return_status, p_out, p_validate_flag, p_node_type);
msc_gantt_utils.updateReqSimu(p_plan_id, p_instance_id, ll_res_trx_id,
l_firm_flag, l_start, l_end, l_firm_start, l_firm_end, MOVE_MRR, p_return_status, p_out);
select msi.item_name item,
ms.new_order_quantity qty,
nvl(to_char(ms.firm_date,format_mask), null_space) firm_date,
to_char(ms.new_schedule_date,format_mask) sugg_due_date,
nvl(to_char(ms.need_by_date,format_mask), null_space) needby,
nvl(ms.unit_number,null_space) unit_number,
nvl(msc_get_name.project(ms.project_id,ms.organization_id,
ms.plan_id, ms.sr_instance_id), null_space) project,
nvl(msc_get_name.task(ms.task_id, ms.project_id, ms.organization_id,
ms.plan_id, ms.sr_instance_id),null_space) task,
msc_get_name.org_code(ms.organization_id, ms.sr_instance_id) org,
decode(ms.order_type,
5, decode(ms.order_number,
null, to_char(ms.transaction_id),
replace(ms.order_number,'~','^')||' '||to_char(ms.transaction_id)),
nvl(replace(ms.order_number,'~','^'), to_char(ms.transaction_id))) job_name,
ms.firm_planned_type,
nvl(ms.alternate_bom_designator, null_space) alternate_bom_designator,
nvl(ms.alternate_routing_designator, null_space) alternate_routing_designator,
ms.organization_id org_id,
nvl(to_char(msi.planning_time_fence_date, format_mask),null_space) time_fence,
nvl(msc_get_name.supply_type(ms.transaction_id, p_plan_id), null_space) supply_type,
decode(msc_gantt_utils.getSupplyType(ms.order_type, msi.planning_make_buy_code,
ms.organization_id, ms.source_organization_id),
BUY_SUPPLY, l_buy_text,
TRANSFER_SUPPLY, l_transfer_text,
MAKE_SUPPLY, l_make_text) item_type,
msi.description,
nvl(msc_get_name.supplier(nvl(ms.source_supplier_id, ms.supplier_id)), null_space) supplier,
nvl(msc_get_name.org_code(ms.source_organization_id, ms.source_sr_instance_id),null_space) source_org,
nvl(ms.ship_method, null_space) ship_method,
msc_get_name.lookup_meaning('SYS_YES_NO', decode(ms.supply_is_shared,1,1,2)) share_supply,
nvl(to_char(ms.EARLIEST_START_DATE,format_mask),null_space) EPSD,
nvl(to_char(ms.EARLIEST_COMPLETION_DATE,format_mask),null_space) EPCD,
nvl(to_char(ms.UEPSD,format_mask),null_space) UEPSD,
nvl(to_char(ms.UEPCD,format_mask),null_space) UEPCD,
nvl(to_char(ms.ULPSD,format_mask),null_space) ULPSD,
nvl(to_char(ms.ULPCD,format_mask),null_space) ULPCD
from msc_supplies ms,
msc_system_items msi
where ms.plan_id = p_plan_id
and ms.transaction_id = p_transaction_id
and ms.sr_instance_id = p_instance_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id;
select msc_get_name.item_name(mrr.assembly_item_id,null,null,null) item,
nvl(mrr.operation_seq_num,0) op_seq,
msc_get_name.org_code(mrr.organization_id, mrr.sr_instance_id) org,
msc_get_name.department_code(decode(mrr.resource_id, -1, 1,2),
mrr.department_id, mrr.organization_id, mrr.plan_id, mrr.sr_instance_id) dept_code,
nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id), to_char(mrr.supply_id)) job_name,
nvl(mrr.assigned_units,0) assigned_units,
msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE', nvl(mrr.firm_flag,0)) firm_flag,
nvl(mrr.alternate_num,0) alternate_num,
nvl(mrr.resource_seq_num,0) res_seq_num,
nvl(msc_get_name.resource_code(mrr.resource_id, mrr.department_id,
mrr.organization_id, mrr.plan_id, mrr.sr_instance_id), null_space) res_code,
nvl(mrr.resource_hours,0) resource_hours,
ms.organization_id org_id,
ms.transaction_id trans_id,
0 mtq_time, -- getMTQTime(p_transaction_id, p_plan_id, p_instance_id) mtq_time,
nvl(mdr.batchable_flag,2) batchable,
nvl(to_char(mrr.batch_number), null_space) batch_number,
nvl(mdr.unit_of_measure,null_space) uom,
nvl(decode(mrr.basis_type,
null, null_space,
msc_get_name.lookup_meaning('MSC_RES_BASIS_TYPE',mrr.basis_type)),null_space) basis_type,
nvl(decode(mrr.schedule_flag,
null, null_space,
msc_get_name.lookup_meaning('BOM_RESOURCE_SCHEDULE_TYPE',mrr.schedule_flag)),null_space) schedule_flag,
nvl(to_char(mrr.EARLIEST_START_DATE,format_mask),null_space) EPSD,
nvl(to_char(mrr.EARLIEST_COMPLETION_DATE,format_mask),null_space) EPCD,
nvl(to_char(mrr.UEPSD,format_mask),null_space) UEPSD,
nvl(to_char(mrr.UEPCD,format_mask),null_space) UEPCD,
nvl(to_char(mrr.ULPSD,format_mask),null_space) ULPSD,
nvl(to_char(mrr.ULPCD,format_mask),null_space) ULPCD
from msc_resource_requirements mrr,
msc_supplies ms,
msc_department_resources mdr
where mrr.plan_id = p_plan_id
and mrr.transaction_id = p_transaction_id
and mrr.sr_instance_id = p_instance_id
and ms.sr_instance_id = mrr.sr_instance_id
and ms.plan_id = p_plan_id
and ms.transaction_id = mrr.supply_id
and mdr.plan_id = mrr.plan_id
and mdr.organization_id = mrr.organization_id
and mdr.sr_instance_id = mrr.sr_instance_id
and mdr.department_id = mrr.department_id
and mdr.resource_id = mrr.resource_id;
select sum(decode(mfp.demand_id, -1, mfp.allocated_quantity, 0)),
sum(mfp.allocated_quantity)
from msc_full_pegging mfp
where mfp.plan_id = p_plan_id
AND mfp.transaction_id = p_transaction_id
and mfp.end_pegging_id = p_end_peg_id;
select mfp.end_pegging_id
bulk collect into v_end_peg_id
from msc_full_pegging mfp
where mfp.plan_id = p_plan_id
and mfp.demand_id = p_end_demand_id
and mfp.pegging_id = mfp.end_pegging_id;
select msc_get_name.item_name(ms.inventory_item_id,null,null,null) item,
mrr.operation_seq_num,
ms.new_order_quantity qty,
nvl(to_char(ms.firm_date,format_mask),null_space) firm_date,
to_char(ms.new_schedule_date,format_mask) sugg_due_date,
nvl(to_char(ms.need_by_date,format_mask),null_space) needby,
nvl(ms.unit_number,null_space) unit_number,
nvl(msc_get_name.project(ms.project_id,ms.organization_id,
ms.plan_id, ms.sr_instance_id), null_space) project,
nvl(msc_get_name.task(ms.task_id, ms.project_id, ms.organization_id,
ms.plan_id, ms.sr_instance_id),null_space) task,
ms.transaction_id,
ms.organization_id,
msc_get_name.org_code(mdr.organization_id, mdr.sr_instance_id) org,
mdr.department_code,
decode(ms.order_type,
5, decode(ms.order_number,
null,to_char(ms.transaction_id),
replace(ms.order_number,'~','^')||' '||to_char(ms.transaction_id)),
nvl(replace(ms.order_number,'~','^'),to_char(ms.transaction_id))) job_name,
mrr.assigned_units,
nvl(msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE',nvl(mrr.firm_flag,NO_FIRM)),
msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE',0))
firm_flag,
ms.firm_planned_type,
nvl(mrr.alternate_num,0) alternate_num,
mrr.resource_seq_num,
nvl(mdr.resource_code, null_space) resource_code,
mrr.resource_hours,
nvl(msc_get_name.alternate_bom(pe.plan_id, pe.sr_instance_id,
pe.bill_sequence_id),null_space) alternate_bom_designator,
nvl(msc_get_name.alternate_bom(pe.plan_id, pe.sr_instance_id,
pe.bill_sequence_id),null_space) alternate_routing_designator,
nvl(to_char(msi.planning_time_fence_date, format_mask),null_space) time_fence,
0 mtq_time, --getMTQTime(p_transaction_id, p_plan_id, p_instance_id) mtq_time,
nvl(mdr.batchable_flag, 2) batchable,
nvl(to_char(mrr.batch_number), null_space) batch_number,
nvl(mdr.unit_of_measure,null_space) uom,
nvl(decode(to_char(mrr.basis_type),
null, null_space,
msc_get_name.lookup_meaning('MSC_RES_BASIS_TYPE',mrr.basis_type)),null_space) basis_type,
nvl(decode(to_char(mrr.schedule_flag),
null, null_space,
msc_get_name.lookup_meaning('BOM_RESOURCE_SCHEDULE_TYPE',mrr.schedule_flag)),null_space) schedule_flag,
nvl(to_char(mrr.EARLIEST_START_DATE,format_mask),null_space) EPSD,
nvl(to_char(mrr.EARLIEST_COMPLETION_DATE,format_mask),null_space) EPCD,
nvl(to_char(mrr.UEPSD,format_mask),null_space) UEPSD,
nvl(to_char(mrr.UEPCD,format_mask),null_space) UEPCD,
nvl(to_char(mrr.ULPSD,format_mask),null_space) ULPSD,
nvl(to_char(mrr.ULPCD,format_mask),null_space) ULPCD
from msc_supplies ms,
msc_resource_requirements mrr,
msc_department_resources mdr,
msc_system_items msi,
msc_process_effectivity pe
where pe.plan_id(+) = ms.plan_id
and pe.sr_instance_id(+) = ms.sr_instance_id
and pe.process_sequence_id(+) = ms.process_seq_id
and mrr.plan_id = p_plan_id
and mrr.transaction_id = p_transaction_id
and mrr.sr_instance_id = p_instance_id
and ms.plan_id = mrr.plan_id
and ms.transaction_id = mrr.supply_id
and ms.sr_instance_id = mrr.sr_instance_id
and mdr.plan_id = mrr.plan_id
and mdr.organization_id = mrr.organization_id
and mdr.sr_instance_id = mrr.sr_instance_id
and mdr.department_id = mrr.department_id
and mdr.resource_id = mrr.resource_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id;
select mfp2.demand_id, mfp2.sr_instance_id,
sum(nvl(mfp1.allocated_quantity,0)),
mfp2.demand_quantity,
mfp2.demand_date,
mfp2.inventory_item_id
from msc_full_pegging mfp1,
msc_full_pegging mfp2
where mfp1.plan_id = p_plan_id
and mfp1.organization_id = v_org_id
and mfp1.sr_instance_id = p_instance_id
and mfp1.transaction_id = v_transaction_id
and mfp2.plan_id = mfp1.plan_id
and mfp2.sr_instance_id = mfp1.sr_instance_id
and mfp2.pegging_id = mfp1.end_pegging_id
group by mfp2.demand_id,
mfp2.sr_instance_id,
mfp2.demand_quantity,
mfp2.demand_date,
mfp2.inventory_item_id;
select nvl(v_demand_quantity,0) qty,
nvl(to_char(v_demand_date,format_mask), null_space) demand_date,
msc_get_name.lookup_meaning('MRP_FLP_SUPPLY_DEMAND_TYPE', v_demand_id) type,
item_name item
from msc_items
where inventory_item_id = v_item_id;
select md.using_requirement_quantity qty,
to_char(md.using_assembly_demand_date, format_mask) demand_date,
msc_get_name.demand_order_number (md.plan_id,md.sr_instance_id,md.demand_id) name,
msc_get_name.lookup_meaning('MRP_DEMAND_ORIGINATION', md.origination_type) type,
msc_get_name.item_name(md.inventory_item_id, null,null,null) item,
nvl(md.demand_priority,0) priority,
nvl(replace(msc_get_name.customer(md.customer_id),'&','*'), null_space) customer,
nvl(replace(msc_get_name.customer_site(md.customer_site_id),'&','*'), null_space) customer_site,
nvl(to_char(md.dmd_satisfied_date,format_mask), null_space) satisfied_date,
decode(sign(md.dmd_satisfied_date - md.using_assembly_demand_date),
1, GREATEST(round(md.dmd_satisfied_date - md.using_assembly_demand_date,2), 0.01), 0) days_late,
nvl(to_char(md.quantity_by_due_date),null_space) qty_by_due_date,
msc_get_name.org_code(md.organization_id, md.sr_instance_id) org,
nvl(md.demand_class,null_space) demand_class
from msc_demands md
where md.plan_id = p_plan_id
and md.demand_id = v_demand_id
and md.sr_instance_id = v_instance_id;
select
to_char(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) start_date,
to_char(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) end_date,
nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id), to_char(mrr.supply_id)) job_name,
msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
mrr.assigned_units,
mrr.transaction_id,
mrr.sr_instance_id
from msc_resource_requirements mrr
where mrr.sr_instance_id = p_instance_id
and mrr.plan_id = p_plan_id
and mrr.organization_id = p_org_id
and mrr.end_date is not null
and nvl(mrr.parent_id,2) =2
and nvl(mrr.firm_start_date,mrr.start_date) <= p_plan_end_date
and mrr.department_id = p_dept_id
and mrr.resource_id = p_res_id
and to_date(p_time, format_mask)
between msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
and msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date, mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied)
order by start_date;
select
to_char(msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) start_date,
to_char(msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied), format_mask) end_date,
nvl(msc_get_name.job_name(mrr.supply_id, p_plan_id), to_char(mrr.supply_id)) job_name,
msc_get_name.supply_type(mrr.supply_id, mrr.plan_id) supply_type,
mrr.assigned_units,
mrr.transaction_id,
mrr.sr_instance_id
from msc_resource_requirements mrr
where mrr.sr_instance_id = p_instance_id
and mrr.plan_id = p_plan_id
and mrr.transaction_id = p_transaction_id
and mrr.end_date is not null
and mrr.department_id <> -1
and nvl(mrr.parent_id,2) =2
and nvl(mrr.firm_start_date,mrr.start_date) <= p_plan_end_date
order by start_date;
select distinct mon.dependency_type
from msc_resource_requirements mrr,
msc_operation_networks mon
where mrr.plan_id = p_plan_id
and mrr.sr_instance_id = p_inst_id
and mrr.supply_id = p_trans_id
and mon.plan_id = mrr.plan_id
and mon.sr_instance_id = mrr.sr_instance_id
and mon.routing_sequence_id = mrr.routing_sequence_id
and mon.transition_type = 1
and nvl(mon.from_op_seq_id, MBP_NULL_VALUE) = nvl(p_op_seq_id, MBP_NULL_VALUE)
and nvl(mon.to_op_seq_id, MBP_NULL_VALUE) = nvl(c_op_seq_id, MBP_NULL_VALUE);
select mjon.dependency_type
from msc_job_operation_networks mjon
where mjon.plan_id = p_plan_id
and mjon.sr_instance_id = p_inst_id
and mjon.transaction_id = p_trans_id
and mjon.to_transaction_id = c_trans_id
and mjon.transition_type = 1
and nvl(mjon.from_op_seq_id, MBP_NULL_VALUE) = nvl(p_op_seq_id, MBP_NULL_VALUE)
and nvl(mjon.to_op_seq_id, MBP_NULL_VALUE) = nvl(c_op_seq_id, MBP_NULL_VALUE);
select producer_trans_id,
producer_sr_instance_id,
from_operation_seq_id,
from_operation_sequence,
from_resource_sequence,
consumer_trans_id,
consumer_sr_instance_id,
to_operation_seq_id,
to_operation_sequence,
to_resource_sequence,
from_start_date,
from_end_date,
from_quantity,
to_start_date,
to_end_date,
-23453 allocation_type,
nvl(dependency_type_id, -1) dependency_type,
minimum_time_offset,
maximum_time_offset,
actual_time_offset
from msc_material_flow_details_v
where plan_id = p_plan
and ( ( producer_sr_instance_id = p_inst and producer_trans_id = p_trx )
or ( consumer_sr_instance_id = p_inst and consumer_trans_id = p_trx ) );
select charge_start_datetime, charge_end_datetime, charge_quantity
bulk collect into v_start_date, v_end_date, v_qty
from msc_resource_charges
where plan_id = p_plan_id
and sr_instance_id = l_inst_id
and res_transaction_id = l_trx_id
order by 1;
update msc_gantt_query
set row_flag = p_row_flag
where query_id = p_query_id
and row_index = p_node_id;
sql_stmt := ' select distinct mgq.row_index,
mis.supplier_id,
mis.supplier_site_id,
mis.inventory_item_id,
mtp.partner_name SUPPLIER,
mtps.tp_site_code SUPPLIER_SITE,
--mtp2.organization_code ORGANIZATION,
null ORGANIZATION,
msi.item_name ITEM,
null BUYER,
null PLANNER
FROM msc_trading_partners mtp,
--msc_trading_partners mtp2,
msc_trading_partner_sites mtps,
msc_system_items msi,
msc_item_suppliers mis,
msc_gantt_query mgq
WHERE mgq.query_id = :p_query_id
and mis.plan_id = mgq.plan_id
and mis.supplier_id = mgq.supplier_id
and mis.supplier_site_id = mgq.supplier_site_id
and mis.inventory_item_id = mgq.inventory_item_id
and mis.plan_id = msi.plan_id
and mis.organization_id = msi.organization_id
and mis.sr_instance_id = msi.sr_instance_id
and mis.inventory_item_id = msi.inventory_item_id
and mtp.partner_id = mis.supplier_id
and mtp.partner_type = 1
and mtps.partner_id(+) = mis.supplier_id
and mtps.partner_site_id(+) = mis.supplier_site_id
--and mtp2.sr_tp_id = mis.organization_id
--and mtp2.sr_instance_id = mis.sr_instance_id
--and mtp2.partner_type = 3
';
p_name_data.delete;
oneBigRecord.delete;
sql_stmt := ' select mgq.row_index,
mgq.sr_instance_id,
mgq.organization_id,
mgq.department_id,
mgq.resource_id,
mgq.res_instance_id,
mgq.serial_number,
msc_get_name.org_code(res.organization_id, res.sr_instance_id) ORGANIZATION,
res.department_code DEPARTMENT,
decode(res.resource_id,-1, null,
msc_get_name.department_code(2, res.owning_department_id,
res.organization_id, res.plan_id,res.sr_instance_id)) OWN_DEPT,
res.resource_code RESOURCE_CODE,
msc_get_name.lookup_meaning(''BOM_RESOURCE_TYPE'',res.resource_type) RES_TYPE,
msc_gantt_utils.getDeptResInstCode(res.plan_id, res.sr_instance_id, res.organization_id,
res.department_id, res.resource_id, mgq.res_instance_id, mgq.serial_number) EQUIP_NUMBER
from msc_department_resources res,
msc_gantt_query mgq
where mgq.query_id = :p_query_id
and res.plan_id = mgq.plan_id
and res.sr_instance_id = mgq.sr_instance_id
and res.organization_id = mgq.organization_id
and res.department_id = mgq.department_id
and res.resource_id = mgq.resource_id ';
p_name_data.delete;
oneBigRecord.delete;
select distinct department_line_code
from msc_resource_requirements_v
where plan_id = p_plan
and source_transaction_id = p_supply;
select mupv.key pref_key, nvl(mupv.value, mupk.default_value) pref_value
from msc_user_preferences mup,
msc_user_preference_keys mupk,
msc_user_preference_values mupv
where p_from_flag <> 0
and mup.user_id = p_user
and mup.preference_id = p_pref_id
and mup.preference_id = mupv.preference_id
and mupv.key = mupk.preference_key
and mupk.plan_type = 1 -- look at only p_plan_type 1, per emily
and mupv.key in ('CATEGORY_SET_ID',
'GANTT_RA_ACT_LBL_1', 'GANTT_RA_ACT_LBL_2', 'GANTT_RA_ACT_LBL_3',
'GANTT_RA_BATCH_LBL_1', 'GANTT_RA_BATCH_LBL_2', 'GANTT_RA_BATCH_LBL_3',
'GANTT_SUP_ORD_LBL_1', 'GANTT_SUP_ORD_LBL_2', 'GANTT_SUP_ORD_LBL_3',
'GANTT_OPR_LBL_1', 'GANTT_OPR_LBL_2', 'GANTT_OPR_LBL_3',
'GANTT_RES_ACT_LBL_1', 'GANTT_RES_ACT_LBL_2', 'GANTT_RES_ACT_LBL_3',
'GANTT_RA_TOL_DAYS_EARLY', 'GANTT_RA_TOL_DAYS_LATE', 'SUMMARY_DECIMAL_PLACES',
'GANTT_RH_TOL_DAYS_EARLY', 'GANTT_RH_TOL_DAYS_LATE')
union all
select mupk.preference_key pref_key,
mupk.default_value pref_value
from msc_user_preference_keys mupk
where p_from_flag = 0
and mupk.plan_type = 1 -- look at only p_plan_type 1, per emily
and mupk.preference_key in ('CATEGORY_SET_ID',
'GANTT_RA_ACT_LBL_1', 'GANTT_RA_ACT_LBL_2', 'GANTT_RA_ACT_LBL_3',
'GANTT_RA_BATCH_LBL_1', 'GANTT_RA_BATCH_LBL_2', 'GANTT_RA_BATCH_LBL_3',
'GANTT_SUP_ORD_LBL_1', 'GANTT_SUP_ORD_LBL_2', 'GANTT_SUP_ORD_LBL_3',
'GANTT_OPR_LBL_1', 'GANTT_OPR_LBL_2', 'GANTT_OPR_LBL_3',
'GANTT_RES_ACT_LBL_1', 'GANTT_RES_ACT_LBL_2', 'GANTT_RES_ACT_LBL_3',
'GANTT_RA_TOL_DAYS_EARLY', 'GANTT_RA_TOL_DAYS_LATE', 'SUMMARY_DECIMAL_PLACES',
'GANTT_RH_TOL_DAYS_EARLY', 'GANTT_RH_TOL_DAYS_LATE');
select count(*)
from msc_user_preference_values mupv
where mupv.preference_id = p_pref_id;
select category_set_id
from msc_category_sets
where default_flag = 1;
insert into msc_form_query(
query_id, last_update_date, last_updated_by, creation_date, created_by,
last_update_login, number1)
values (
pQueryId, trunc(sysdate), -1, trunc(sysdate), -1, -1, token );
insert into msc_form_query(
query_id, last_update_date, last_updated_by, creation_date, created_by,
last_update_login, number1)
values (
pQueryId, trunc(sysdate), -1, trunc(sysdate), -1, -1, token);
select mgq.row_index,
sum(nvl(mfp1.allocated_quantity,0)) allocated_quantity
from msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_gantt_query mgq,
msc_form_query mfq
where mfp1.plan_id = p_plan
and mfp1.transaction_id = p_trx
and mfq.query_id = p_mfq_query_id
and mgq.query_id = p_mgq_query_id
and mgq.row_index = mfq.number1
and mfp2.plan_id = mfp1.plan_id
and mfp2.end_pegging_id = mfp1.end_pegging_id
and mfp2.transaction_id = mgq.transaction_id
group by mgq.row_index
union all
select mgq.row_index,
nvl(mfp1.allocated_quantity,0) allocated_quantity
from msc_full_pegging mfp1,
msc_gantt_query mgq,
msc_form_query mfq
where mfp1.plan_id = p_plan
and mfp1.transaction_id = p_trx
and mfq.query_id = p_mfq_query_id
and mgq.query_id = p_mgq_query_id
and mgq.row_index = mfq.number1
and mfp1.demand_id = mgq.transaction_id ;
sql_stmt := ' select mgq.row_index,
msc_get_name.demand_order_number ( md.plan_id, md.sr_instance_id, md.demand_id )
||'' for ''||msi.item_name ||'' in '' || mtp.organization_code ORDERS,
mgq.node_type,
mgq.node_path,
mgq.parent_link,
nvl(mgq.critical_flag,0) critical_flag,
msi.item_name ITEM,
mtp.organization_code ORGANIZATION,
md.origination_type order_type_id,
msc_get_name.lookup_meaning(''MRP_DEMAND_ORIGINATION'', md.origination_type) ORDER_TYPE,
msc_get_name.demand_order_number ( md.plan_id, md.sr_instance_id, md.demand_id )
ORDER_NUMBER,
decode(md.customer_id, null,
msc_get_name.get_other_customers(md.plan_id, md.schedule_designator_id),
msc_get_name.customer(md.customer_id)) CUSTOMER,
decode(md.customer_site_id, null,
msc_get_name.get_other_customers(md.plan_id,md.schedule_designator_id),
msc_get_name.customer_site(md.customer_site_id)) CUSTOMER_SITE,
md.using_assembly_demand_date start_date,
nvl(md.dmd_satisfied_date,md.using_assembly_demand_date) end_date,
demand_priority,
--md.quantity_by_due_date QUANTITY,
md.using_requirement_quantity QUANTITY,
msi.description ITEM_DESC,
mic.category_name ITEM_CATEGORY,
msc_get_name.item_name(msi.product_family_id,null,null,null) PRODUCT_FAMILY,
msi.planner_code PLANNER,
md.planning_group PLN_GROUP,
decode(md.project_id, null, null, msc_get_name.project(md.project_id,
md.organization_id, md.plan_id, md.sr_instance_id)) PROJECT,
decode(md.task_id, null, null, msc_get_name.task(md.task_id,
md.project_id,md.organization_id, md.plan_id, md.sr_instance_id)) TASK,
md.planned_ship_date SUGG_SHIP_DATE,
md.using_assembly_demand_date SUGG_DUE_DATE,
md.ship_method SHIP_METHOD,
md.demand_id transaction_id,
md.sr_instance_id,
md.organization_id,
md.inventory_item_id,
md.demand_class,
md.dmd_satisfied_date material_avail_date
from msc_demands md,
msc_system_items msi,
msc_item_categories mic,
msc_trading_partners mtp,
msc_gantt_query mgq
where mgq.query_id = :p_query_id
and mgq.node_type = :END_DEMAND_NODE
and mgq.row_index between :p_from_index and :p_to_index
and md.plan_id = :p_plan_id
and md.sr_instance_id = mgq.sr_instance_id
and md.demand_id = mgq.transaction_id
and md.plan_id = msi.plan_id
and md.sr_instance_id = msi.sr_instance_id
and md.organization_id = msi.organization_id
and md.inventory_item_id = msi.inventory_item_id
and msi.sr_instance_id = mic.sr_instance_id
and msi.organization_id = mic.organization_id
and msi.inventory_item_id = mic.inventory_item_id
and mic.category_set_id = :g_category_set_id
and mtp.partner_type = 3
and mtp.sr_tp_id = md.organization_id
and mtp.sr_instance_id = md.sr_instance_id ';
sql_stmt := ' select mgq.row_index,
decode(ms.order_type, 18, msc_get_name.lookup_meaning(''MRP_ORDER_TYPE'', 18),
msc_get_name.supply_order_number(ms.order_type, ms.order_number, ms.plan_id,
ms.sr_instance_id, ms.transaction_id, ms.disposition_id)) ||'' for ''||
msi.item_name ||'' in '' || mtp.organization_code ||''(''|| round(ms.new_order_quantity,:round_factor)||'')'' ORDERS,
mgq.node_type,
mgq.node_path,
mgq.parent_link,
mgq.critical_flag,
msi.item_name ITEM,
mtp.organization_code ORGANIZATION,
to_char(least(to_date(msc_gantt_utils.getActualStartDate(ms.order_type, msi.planning_make_buy_code,
ms.organization_id, ms.source_organization_id, ms.new_dock_date,
ms.new_wip_start_date, ms.new_ship_date,ms.new_schedule_date, ms.source_supplier_id), :format_mask),
nvl(ms.firm_date, ms.new_schedule_date)),:format_mask) start_date,
nvl(ms.firm_date, ms.new_schedule_date) end_date,
ms.order_type order_type_id,
mfg.meaning ORDER_TYPE,
msc_get_name.supply_order_number(ms.order_type, ms.order_number, ms.plan_id,
ms.sr_instance_id, ms.transaction_id, ms.disposition_id) order_number,
ms.new_order_quantity QUANTITY,
ms.earliest_start_date,
ms.earliest_completion_date,
ms.ulpsd,
ms.ulpcd,
ms.uepsd,
ms.uepcd,
ms.min_start_date,
ms.requested_start_date REQ_START_DATE,
ms.requested_completion_date REQ_COMPL_DATE,
msi.planning_make_buy_code item_type,
nvl(ms.firm_planned_type,2) supply_firm_type,
nvl(ms.status, 0) status,
nvl(ms.applied, 0) applied,
nvl(ms.supplier_id, 0) supplier_id,
nvl(ms.supplier_site_id, 0) supplier_site_id,
msi.description ITEM_DESC,
mic.category_name ITEM_CATEGORY,
msc_get_name.item_name(msi.product_family_id,null,null,null) PRODUCT_FAMILY,
msi.planner_code PLANNER,
ms.planning_group PLN_GROUP,
decode(ms.project_id, null, null, msc_get_name.project(ms.project_id,
ms.organization_id, ms.plan_id, ms.sr_instance_id)) PROJECT,
decode(ms.task_id, null, null, msc_get_name.task(ms.task_id,
ms.project_id, ms.organization_id, ms.plan_id, ms.sr_instance_id)) TASK,
ms.new_order_placement_date SUGG_ORDER_DATE,
ms.new_wip_start_date SUGG_START_DATE,
ms.new_ship_date SUGG_SHIP_DATE,
ms.new_dock_date SUGG_DOCK_DATE,
ms.new_schedule_date SUGG_DUE_DATE,
msc_get_name.org_code(ms.source_organization_id, ms.source_sr_instance_id) SR_ORG,
msc_get_name.supplier(ms.supplier_id) SUPPLIER,
msc_get_name.supplier_site(ms.supplier_site_id) SUPPLIER_SITE,
ms.ship_method SHIP_METHOD,
ms.schedule_group_name SCHEDULE_GROUP,
ms.transaction_id transaction_id,
ms.sr_instance_id,
ms.organization_id,
ms.inventory_item_id,
ms.schedule_priority,
msi.description,
msc_get_name.lookup_meaning(''WIP_JOB_STATUS'', ms.wip_status_code) wip_status_text,
ms.new_schedule_date,
ms.actual_start_date
from msc_supplies ms,
msc_system_items msi,
msc_item_categories mic,
msc_trading_partners mtp,
mfg_lookups mfg,
msc_gantt_query mgq
where mgq.query_id = :p_query_id
and mgq.node_type in (:JOB_NODE, :COPROD_NODE)
and mgq.row_index between :p_from_index and :p_to_index
and ms.plan_id = :p_plan_id
and ms.sr_instance_id = mgq.sr_instance_id
and ms.transaction_id = mgq.transaction_id
and ms.plan_id = msi.plan_id
and ms.organization_id = msi.organization_id
and ms.sr_instance_id = msi.sr_instance_id
and ms.inventory_item_id = msi.inventory_item_id
and msi.sr_instance_id = mic.sr_instance_id
and msi.organization_id = mic.organization_id
and msi.inventory_item_id = mic.inventory_item_id
and mic.category_set_id = :g_category_set_id
and mfg.lookup_type = ''MRP_ORDER_TYPE''
and mfg.lookup_code = ms.order_type
and mtp.partner_type = 3
and mtp.sr_tp_id = ms.organization_id
and mtp.sr_instance_id = ms.sr_instance_id ';
sql_stmt := ' select mgq.row_index,
mgq.op_seq_num,
mgq.node_type,
mgq.node_path,
decode(mgq.dependency_type,
null, null,
mgq.dependency_type||:FIELD_SEPERATOR || mgq.parent_link),
mgq.op_desc,
mgq.transaction_id
from msc_gantt_query mgq
where mgq.query_id = :p_query_id
and mgq.node_type = :OP_NODE
and substr(mgq.node_path ,1, instr(mgq.node_path, :COLON_SEPARATOR)-1)
between :p_from_index and :p_to_index '|| node_level_stmt || '
order by to_number(substr(mgq.node_path ,1, instr(mgq.node_path, :COLON_SEPARATOR)-1)),
to_number(substr(mgq.node_path , instr(mgq.node_path, :COLON_SEPARATOR)+1))';
sql_stmt := ' select mgq.row_index,
to_char(mrr.operation_seq_num)||''/''||to_char(mrr.resource_seq_num)||
''(''||msc_get_name.department_resource_code(mrr.resource_id,
mrr.department_id, mrr.organization_id, mrr.plan_id, mrr.sr_instance_id)||'')'',
mgq.node_type,
mgq.node_path,
mgq.parent_link,
mdr.department_code,
mdr.resource_code,
msc_gantt_utils.getResReqStartDate(nvl(mrr.firm_flag,0),
mrr.start_date, mrr.end_date,mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) start_date,
msc_gantt_utils.getResReqEndDate(nvl(mrr.firm_flag,0), mrr.start_date, mrr.end_date,
mrr.firm_start_date, mrr.firm_end_date, mrr.status, mrr.applied) end_date,
ms.new_order_quantity QUANTITY,
mrr.resource_hours adjusted_res_hours,
mrr.batch_number,
mrr.assigned_units,
msc_get_name.alternate_rtg(mrr.plan_id, mrr.sr_instance_id, mrr.routing_sequence_id),
msc_get_name.lookup_meaning(''WIP_JOB_STATUS'', ms.wip_status_code) wip_status_text,
nvl(ms.firm_planned_type,2) supply_firm_type,
nvl(mrr.firm_flag,0) res_firm_type,
nvl(mrr.status, 0) status,
nvl(mrr.applied, 0) applied,
msc_gantt_utils.getResReqType(mrr.plan_id, mrr.schedule_flag, mrr.parent_seq_num, mrr.setup_id) res_req_type,
mrr.supply_id transaction_id,
mrr.transaction_id res_transaction_id,
mrr.resource_seq_num,
decode(mrr.setup_id, to_number(null), null,
msc_gantt_utils.getSetupCode(mrr.plan_id, mrr.sr_instance_id, mrr.resource_id, mrr.setup_id)),
mrr.earliest_start_date,
mrr.earliest_completion_date,
mrr.ulpsd,
mrr.ulpcd,
mrr.uepsd,
mrr.uepcd,
mrr.eacd,
mrr.sr_instance_id,
mrr.organization_id,
mrr.department_id,
mrr.resource_id,
ms.inventory_item_id,
msc_get_name.supply_order_number(ms.order_type, ms.order_number, ms.plan_id,
ms.sr_instance_id, ms.transaction_id, ms.disposition_id) order_number,
mrr.operation_seq_num,
mdr.resource_description,
mi.item_name item,
mi2.description assembly_item_desc,
decode(mrr.resource_hours, 0, to_number(null),
nvl(mrr.cummulative_quantity,ms.new_order_quantity)) schedule_qty,
msc_gantt_utils.getOrderViewResNodeLabel(mrr.plan_id, mrr.sr_instance_id,
mrr.transaction_id) bar_text,
mrr.alternate_num,
mrr.actual_start_date,
mgq.critical_flag
from msc_resource_requirements mrr,
msc_department_resources mdr,
msc_supplies ms,
msc_items mi,
msc_items mi2,
msc_trading_partners mtp,
msc_gantt_query mgq
where mgq.query_id = :p_query_id
and mgq.node_type = :RES_NODE
and ( ( nvl(:p_res_nodes_only,2) = 2
and substr(mgq.node_path ,1, instr(mgq.node_path, :COLON_SEPARATOR)-1)
between :p_from_index and :p_to_index)
or ( nvl(:p_res_nodes_only,2) = 1
and mgq.row_index between :p_from_index and :p_to_index)
)
and mrr.plan_id = :p_plan_id
and mrr.sr_instance_id = mgq.sr_instance_id
and mrr.organization_id = mgq.organization_id
and mrr.transaction_id = mgq.transaction_id
and mrr.parent_id = 2
and mrr.end_date is not null
and mrr.department_id <> -1
and mrr.plan_id = mdr.plan_id
and mrr.organization_id = mdr.organization_id
and mrr.sr_instance_id = mdr.sr_instance_id
and mrr.department_id = mdr.department_id
and mrr.resource_id = mdr.resource_id
and mrr.plan_id = ms.plan_id
and mrr.supply_id = ms.transaction_id
and mrr.sr_instance_id = ms.sr_instance_id
and mtp.partner_type = 3
and mtp.sr_tp_id = ms.organization_id
and mtp.sr_instance_id = ms.sr_instance_id
and ms.inventory_item_id = mi.inventory_item_id
and mrr.assembly_item_id = mi2.inventory_item_id '|| node_level_stmt || '
order by to_number(substr(mgq.node_path ,1, instr(mgq.node_path, :COLON_SEPARATOR)-1)),
to_number(substr(mgq.node_path, instr(mgq.node_path, :COLON_SEPARATOR,1,1)+1,
instr(mgq.node_path, :COLON_SEPARATOR,1,2)-instr(mgq.node_path, :COLON_SEPARATOR,1,1)-1)),
to_number(substr(mgq.node_path , instr(mgq.node_path,:COLON_SEPARATOR,1,2)+1)) ';