FA:SQL: Oralce Fusion Manufacturing Manage Scheduels/Schedule Exceptions/ Work Order Definition

 REM Manage Schedules
SELECT DISTINCT cal.schedule_name
              , zssp.schedule_id calendar_code
              , zssv.shift_id shift_num
              , zssv.shift_name
              , zssv.shift_desc description
              , zssv.start_time_ms_num from_time
              , zssv.end_time_ms_num to_time
              , zssv.shift_type_code
  FROM fusion.zmm_sr_shifts_vl zssv
     , fusion.zmm_sr_pattern_dtls zspd
     , fusion.zmm_sr_schedule_patterns zssp
     , fusion.zmm_sr_schedules_vl cal
 WHERE zspd.pattern_id (+) = zssp.pattern_id
   AND zspd.child_shift_id = zssv.shift_id
   AND zssp.schedule_id = cal.schedule_id;
   
 REM Manage Schedule exceptions
SELECT cal.schedule_name
     , zssae.schedule_id calendar_code
     , zssae.avl_exception_id
     , zsaev.avl_excp_name
     , zsaev.avl_excp_desc
     , zsaev.start_date_time
     , zsaev.end_date_time
     , zsaev.short_txt
     , zsaev.category_code
     , zsaev.whole_day_flag
     , zsaev.availability_code
  FROM fusion.zmm_sr_sched_avl_excps zssae
     , fusion.zmm_sr_avl_exceptions_vl zsaev
     , fusion.zmm_sr_schedules_vl cal
 WHERE zsaev.avl_exception_id (+) = zssae.avl_exception_id
   AND zssae.schedule_id = cal.schedule_id
   AND cal.schedule_name = 'US Planning Schedules';

REM Work Order Definitions
SELECT a.organization_id
     , iop.organization_code
     , b.inventory_item_id
     , b.item_number
     , f.alternate_bom_designator
     , d.work_definition_name_id
     , d.work_def_name
     , d.work_def_name_description
     , d.work_definition_code
     , d.work_definition_type
     , e.meaning
     , a.object_version_number
     , a.production_priority
     , a.costing_priority
     , a.work_definition_id
     , a.status_code
     , a.work_method_id
     , c.work_method_name
     , c.work_method_code
  FROM fusion.wis_work_definitions a
     , fusion.egp_system_items_b b
     , fusion.wis_work_methods_vl c
     , fusion.wis_work_definition_names_vl d
     , fusion.FND_LOOKUP_VALUES_VL e
     , fusion.egp_structures_b f
     , fusion.inv_org_parameters iop
 WHERE a.organization_id = b.organization_id
   AND a.inventory_item_id = b.inventory_item_id
   AND a.work_method_id = c.work_method_id
   AND a.organization_id = iop.organization_id
   AND iop.organization_code = 'FHK'
   and a.work_definition_name_id = d.work_definition_name_id
   and e.lookup_type = 'ORA_WIS_WORK_DEFINITION_TYPE'
   and e.lookup_code = d.work_definition_type
   and e.enabled_flag = 'Y'
   and a.bill_sequence_id = f.bill_sequence_id;