select
wjsiv.group_id,
wjsiv.process_phase_meaning process_phase,
wjsiv.process_status_meaning process_status,
wie.error,
xxen_util.meaning(wie.error_type,'WIP_ML_ERROR_TYPE',700) error_type,
wjsiv.request_id,
--
wjsiv.source_code,
wjsiv.source_line_id,
wjsiv.load_type_meaning load_type,
wjsiv.organization_code,
wjsiv.job_name,
wjsiv.line_code line,
(select msiv.concatenated_segments
from mtl_system_items_vl msiv
where msiv.organization_id = wjsiv.organization_id
and msiv.inventory_item_id = wjsiv.primary_item_id
) assembly,
--
wjsiv.start_quantity,
wjsiv.net_quantity,
wjsiv.daily_production_rate,
wjsiv.processing_work_days,
wjsiv.total_quantity,
--
wjsiv.class_code,
wjsiv.lot_number,
wjsiv.schedule_group_name,
wjsiv.build_sequence,
wjsiv.project_number,
wjsiv.task_number,
--
wjsiv.status_type_meaning status,
wjsiv.firm_planned_meaning firm,
wjsiv.demand_class,
wjsiv.scheduling_method_meaning scheduling_method,
wjsiv.end_item_unit_number,
--
wjsiv.first_unit_start_date,
wjsiv.first_unit_completion_date,
wjsiv.last_unit_start_date,
wjsiv.last_unit_completion_date,
--
(select msiv.concatenated_segments
from mtl_system_items_vl msiv
where msiv.organization_id = wjsiv.organization_id
and msiv.inventory_item_id = wjsiv.bom_reference_id
) bom_reference,
wjsiv.alternate_bom_designator,
wjsiv.bom_revision,
wjsiv.bom_revision_date,
wjsiv.wip_supply_type_meaning supply_type,
--
(select msiv.concatenated_segments
from mtl_system_items_vl msiv
where msiv.organization_id = wjsiv.organization_id
and msiv.inventory_item_id = wjsiv.routing_reference_id
) routing_reference,
wjsiv.alternate_routing_designator,
wjsiv.routing_revision,
wjsiv.routing_revision_date,
wjsiv.completion_subinventory,
wjsiv.locator completion_locator,
--
wjsiv.description,
wjsiv.asset_number,
(select msiv.concatenated_segments
from mtl_system_items_vl msiv
where msiv.organization_id = wjsiv.organization_id
and msiv.inventory_item_id = wjsiv.rebuild_item_id
) rebuild_item,
wjsiv.serial_number rebuild_serial_number,
wjsiv.attribute_category,
wjsiv.attribute1,
wjsiv.attribute2,
wjsiv.attribute3,
wjsiv.attribute4,
wjsiv.attribute5,
wjsiv.attribute6,
wjsiv.attribute7,
wjsiv.attribute8,
wjsiv.attribute9,
wjsiv.attribute10,
wjsiv.attribute11,
wjsiv.attribute12,
wjsiv.attribute13,
wjsiv.attribute14,
wjsiv.attribute15,
--
wjsiv.created_by_name,
wjsiv.last_updated_by_name,
wjsiv.creation_date,
wjsiv.last_update_date,
--
wjsi.class_code wjsi_class_code,
wjsi.job_name wjsi_job_name,
wjsi.description wjsi_description,
wjsi.lot_number wjsi_lot_number,
wjsi.explode_routing wjsi_explode_routing,
wjsi.explode_bom wjsi_explode_bom,
wjsi.notification_required wjsi_notification_required,
wjsi.work_order_type wjsi_work_order_type,
wjsi.owning_department wjsi_owning_department,
wjsi.owning_department_code wjsi_owning_department_code,
wjsi.activity_type wjsi_activity_type,
wjsi.activity_cause wjsi_activity_cause,
wjsi.tagout_required wjsi_tagout_required,
wjsi.plan_maintenance wjsi_plan_maintenance,
wjsi.date_released wjsi_date_released,
wjsi.requested_start_date wjsi_requested_start_date,
wjsi.activity_source wjsi_activity_source,
wjsi.maintenance_object_id wjsi_maintenance_object_id,
wjsi.maintenance_object_type wjsi_maintenance_object_type,
wjsi.maintenance_object_source wjsi_maintenance_object_sourc,
wjsi.serialization_start_op wjsi_serialization_start_op,
wjsi.material_issue_by_mo wjsi_material_issue_by_mo,
wjsi.last_update_date wjsi_last_update_date,
wjsi.last_updated_by wjsi_last_updated_by,
wjsi.last_updated_by_name wjsi_last_updated_by_name,
wjsi.creation_date wjsi_creation_date,
wjsi.created_by wjsi_created_by,
wjsi.created_by_name wjsi_created_by_name,
wjsi.last_update_login wjsi_last_update_login,
wjsi.request_id wjsi_request_id,
wjsi.program_id wjsi_program_id,
wjsi.program_application_id wjsi_program_application_id,
wjsi.program_update_date wjsi_program_update_date,
wjsi.process_phase wjsi_process_phase,
wjsi.process_status wjsi_process_status,
wjsi.organization_code wjsi_organization_code,
wjsi.organization_id wjsi_organization_id,
wjsi.load_type wjsi_load_type,
wjsi.status_type wjsi_status_type,
wjsi.last_unit_completion_date wjsi_last_unit_compl_date,
wjsi.processing_work_days wjsi_processing_work_days,
wjsi.daily_production_rate wjsi_daily_production_rate,
wjsi.line_id wjsi_line_id,
wjsi.primary_item_id wjsi_primary_item_id,
wjsi.bom_reference_id wjsi_bom_reference_id,
wjsi.routing_reference_id wjsi_routing_reference_id,
wjsi.bom_revision_date wjsi_bom_revision_date,
wjsi.routing_revision_date wjsi_routing_revision_date,
wjsi.wip_supply_type wjsi_wip_supply_type,
wjsi.firm_planned_flag wjsi_firm_planned_flag,
wjsi.alternate_routing_designator wjsi_alt_routing_designator,
wjsi.alternate_bom_designator wjsi_alt_bom_designator,
wjsi.demand_class wjsi_demand_class,
wjsi.start_quantity wjsi_start_quantity,
wjsi.wip_entity_id wjsi_wip_entity_id,
wjsi.repetitive_schedule_id wjsi_repetitive_schedule_id,
wjsi.attribute_category wjsi_attribute_category,
wjsi.attribute1 wjsi_attribute1,
wjsi.attribute2 wjsi_attribute2,
wjsi.attribute3 wjsi_attribute3,
wjsi.attribute4 wjsi_attribute4,
wjsi.attribute5 wjsi_attribute5,
wjsi.attribute6 wjsi_attribute6,
wjsi.attribute7 wjsi_attribute7,
wjsi.attribute8 wjsi_attribute8,
wjsi.attribute9 wjsi_attribute9,
wjsi.attribute10 wjsi_attribute10,
wjsi.attribute11 wjsi_attribute11,
wjsi.attribute12 wjsi_attribute12,
wjsi.attribute13 wjsi_attribute13,
wjsi.attribute14 wjsi_attribute14,
wjsi.attribute15 wjsi_attribute15,
wjsi.interface_id wjsi_interface_id,
wjsi.first_unit_start_date wjsi_first_unit_start_date,
wjsi.first_unit_completion_date wjsi_first_unit_compl_date,
wjsi.last_unit_start_date wjsi_last_unit_start_date,
wjsi.scheduling_method wjsi_scheduling_method,
wjsi.line_code wjsi_line_code,
wjsi.primary_item_segments wjsi_primary_item_segments,
wjsi.bom_reference_segments wjsi_bom_reference_segments,
wjsi.routing_reference_segments wjsi_routing_reference_segs,
wjsi.routing_revision wjsi_routing_revision,
wjsi.bom_revision wjsi_bom_revision,
wjsi.completion_subinventory wjsi_completion_subinventory,
wjsi.completion_locator_id wjsi_completion_locator_id,
wjsi.completion_locator_segments wjsi_completion_locator_segs,
wjsi.schedule_group_id wjsi_schedule_group_id,
wjsi.schedule_group_name wjsi_schedule_group_name,
wjsi.build_sequence wjsi_build_sequence,
wjsi.project_id wjsi_project_id,
wjsi.task_id wjsi_task_id,
wjsi.net_quantity wjsi_net_quantity,
wjsi.project_number wjsi_project_number,
wjsi.task_number wjsi_task_number,
wjsi.project_costed wjsi_project_costed,
wjsi.end_item_unit_number wjsi_end_item_unit_number,
wjsi.overcompletion_tolerance_type wjsi_overcompletion_tol_type,
wjsi.overcompletion_tolerance_value wjsi_overcompletion_tol_value,
wjsi.kanban_card_id wjsi_kanban_card_id,
wjsi.priority wjsi_priority,
wjsi.due_date wjsi_due_date,
wjsi.allow_explosion wjsi_allow_explosion,
wjsi.delivery_id wjsi_delivery_id,
wjsi.coproducts_supply wjsi_coproducts_supply,
wjsi.due_date_penalty wjsi_due_date_penalty,
wjsi.due_date_tolerance wjsi_due_date_tolerance,
wjsi.xml_document_id wjsi_xml_document_id,
wjsi.parent_wip_entity_id wjsi_parent_wip_entity_id,
wjsi.parent_job_name wjsi_parent_job_name,
wjsi.asset_number wjsi_asset_number,
wjsi.asset_group_id wjsi_asset_group_id,
wjsi.asset_group_segments wjsi_asset_group_segments,
wjsi.pm_schedule_id wjsi_pm_schedule_id,
wjsi.rebuild_item_id wjsi_rebuild_item_id,
wjsi.rebuild_item_segments wjsi_rebuild_item_segments,
wjsi.rebuild_serial_number wjsi_rebuild_serial_number,
wjsi.manual_rebuild_flag wjsi_manual_rebuild_flag,
wjsi.shutdown_type wjsi_shutdown_type,
--
wjdi.component_sequence_id wjdi_component_sequence_id,
wjdi.firm_flag wjdi_firm_flag,
wjdi.resource_serial_number wjdi_resource_serial_number,
wjdi.group_sequence_id wjdi_group_sequence_id,
wjdi.group_sequence_number wjdi_group_sequence_number,
wjdi.resource_instance_id wjdi_resource_instance_id,
wjdi.charge_number wjdi_charge_number,
wjdi.parent_seq_num wjdi_parent_seq_num,
wjdi.component_yield_factor wjdi_component_yield_factor,
wjdi.interface_id wjdi_interface_id,
wjdi.group_id wjdi_group_id,
wjdi.wip_entity_id wjdi_wip_entity_id,
wjdi.organization_id wjdi_organization_id,
wjdi.operation_seq_num wjdi_operation_seq_num,
wjdi.resource_seq_num wjdi_resource_seq_num,
wjdi.resource_id_old wjdi_resource_id_old,
wjdi.resource_id_new wjdi_resource_id_new,
wjdi.usage_rate_or_amount wjdi_usage_rate_or_amount,
wjdi.scheduled_flag wjdi_scheduled_flag,
wjdi.assigned_units wjdi_assigned_units,
wjdi.applied_resource_units wjdi_applied_resource_units,
wjdi.applied_resource_value wjdi_applied_resource_value,
wjdi.uom_code wjdi_uom_code,
wjdi.basis_type wjdi_basis_type,
wjdi.activity_id wjdi_activity_id,
wjdi.autocharge_type wjdi_autocharge_type,
wjdi.standard_rate_flag wjdi_standard_rate_flag,
wjdi.start_date wjdi_start_date,
wjdi.completion_date wjdi_completion_date,
wjdi.inventory_item_id_old wjdi_inventory_item_id_old,
wjdi.inventory_item_id_new wjdi_inventory_item_id_new,
wjdi.quantity_per_assembly wjdi_quantity_per_assembly,
wjdi.department_id wjdi_department_id,
wjdi.wip_supply_type wjdi_wip_supply_type,
wjdi.date_required wjdi_date_required,
wjdi.required_quantity wjdi_required_quantity,
wjdi.quantity_issued wjdi_quantity_issued,
wjdi.supply_subinventory wjdi_supply_subinventory,
wjdi.supply_locator_id wjdi_supply_locator_id,
wjdi.mrp_net_flag wjdi_mrp_net_flag,
wjdi.mps_required_quantity wjdi_mps_required_quantity,
wjdi.mps_date_required wjdi_mps_date_required,
wjdi.load_type wjdi_load_type,
wjdi.substitution_type wjdi_substitution_type,
wjdi.process_phase wjdi_process_phase,
wjdi.process_status wjdi_process_status,
wjdi.last_update_date wjdi_last_update_date,
wjdi.last_updated_by wjdi_last_updated_by,
wjdi.creation_date wjdi_creation_date,
wjdi.created_by wjdi_created_by,
wjdi.last_update_login wjdi_last_update_login,
wjdi.request_id wjdi_request_id,
wjdi.program_application_id wjdi_program_application_id,
wjdi.program_id wjdi_program_id,
wjdi.program_update_date wjdi_program_update_date,
wjdi.parent_header_id wjdi_parent_header_id,
wjdi.description wjdi_description,
wjdi.standard_operation_id wjdi_standard_operation_id,
wjdi.first_unit_start_date wjdi_first_unit_start_date,
wjdi.first_unit_completion_date wjdi_first_unit_compl_date,
wjdi.last_unit_start_date wjdi_last_unit_start_date,
wjdi.last_unit_completion_date wjdi_last_unit_compl_date,
wjdi.minimum_transfer_quantity wjdi_minimum_transfer_qty,
wjdi.backflush_flag wjdi_backflush_flag,
wjdi.count_point_type wjdi_count_point_type,
wjdi.attribute_category wjdi_attribute_category,
wjdi.attribute1 wjdi_attribute1,
wjdi.attribute2 wjdi_attribute2,
wjdi.attribute3 wjdi_attribute3,
wjdi.attribute4 wjdi_attribute4,
wjdi.attribute5 wjdi_attribute5,
wjdi.attribute6 wjdi_attribute6,
wjdi.attribute7 wjdi_attribute7,
wjdi.attribute8 wjdi_attribute8,
wjdi.attribute9 wjdi_attribute9,
wjdi.attribute10 wjdi_attribute10,
wjdi.attribute11 wjdi_attribute11,
wjdi.attribute12 wjdi_attribute12,
wjdi.attribute13 wjdi_attribute13,
wjdi.attribute14 wjdi_attribute14,
wjdi.attribute15 wjdi_attribute15,
wjdi.schedule_seq_num wjdi_schedule_seq_num,
wjdi.substitute_group_num wjdi_substitute_group_num,
wjdi.replacement_group_num wjdi_replacement_group_num,
wjdi.setup_id wjdi_setup_id,
wjdi.batch_id wjdi_batch_id,
wjdi.next_network_op_seq_num wjdi_next_network_op_seq_num,
wjdi.comments wjdi_comments,
wjdi.serial_number_new wjdi_serial_number_new,
wjdi.serial_number_old wjdi_serial_number_old,
wjdi.long_description wjdi_long_description,
wjdi.auto_request_material wjdi_auto_request_material,
wjdi.maximum_assigned_units wjdi_maximum_assigned_units
from
wip_job_schedule_interface_v wjsiv,
wip_job_schedule_interface wjsi,
wip_job_dtls_interface wjdi,
wip_interface_errors wie
where
1=1 and
wjsiv.group_id = wjsi.group_id and
nvl(wjsiv.interface_id,-1) = nvl(wjsi.interface_id,-1) and
nvl2(:p_show_details,wjsi.group_id,null) = wjdi.group_id (+) and
nvl2(:p_show_details,wjsi.header_id,null) = wjdi.parent_header_id (+) and
wjsi.interface_id = wie.interface_id (+)
order by
wjsiv.group_id,
wjsiv.source_code,
wjsiv.source_line_id |
