1. Tables and Views for SCM
  2. WIS_COMMON_WORK_DEFINITIONS_V

WIS_COMMON_WORK_DEFINITIONS_V

Details

  • Schema: FUSION

  • Object owner: WIS

  • Object type: VIEW

Columns

Name

WORK_DEFINITION_ID

WORK_METHOD_CODE

ORGANIZATION_ID

ORGANIZATION_CODE

INVENTORY_ITEM_ID

ITEM_NUMBER

WORK_DEFINITION_CODE

Query

SQL_Statement

SELECT wd.work_definition_id work_definition_id,

wm.work_method_code work_method_code,

wd.organization_id organization_id,

iop.organization_code organization_code,

wd.inventory_item_id inventory_item_id,

esi.item_number item_number,

wdn.work_definition_code work_definition_code

FROM wis_work_definitions wd,

wis_work_methods_b wm,

inv_org_parameters iop,

egp_system_items_bv esi,

wis_work_definition_names_b wdn

WHERE wm.work_method_code IN ('DISCRETE_MANUFACTURING', 'PROCESS_MANUFACTURING')

AND wd.work_method_id = wm.work_method_id

AND wd.system_generated_flag = 'N'

AND wd.status_code <> 'INACTIVE'

AND iop.organization_id = wd.organization_id

AND esi.inventory_item_id = wd.inventory_item_id

AND esi.organization_id = wd.organization_id

AND wdn.work_definition_name_id = wd.work_definition_name_id

AND ( EXISTS(

SELECT 'Y' FROM wis_wd_versions wdv,

edr_signature_organization eso,

edr_signature_definitions esd

WHERE wdv.work_definition_id = wd.work_definition_id

AND eso.organization_id = wdv.organization_id

AND esd.signature_organization_id = eso.signature_organization_id

AND esd.transaction_type_code = 'WORK_DEFINITION'

AND esd.signature_enabled_flag = 'Y'

AND wdv.approval_status_code = 'DRAFT')

OR

NOT EXISTS (

SELECT 'Y' FROM edr_signature_organization eso1,

edr_signature_definitions esd1

WHERE eso1.organization_id = wd.organization_id

AND esd1.signature_organization_id = eso1.signature_organization_id

AND esd1.signature_enabled_flag = 'Y'))

UNION

SELECT wd.work_definition_id,

wm.work_method_code,

wd.organization_id,

iop.organization_code,

null,

null,

wdmn.maintenance_wd_code

FROM wis_work_definitions wd,

wis_work_methods_b wm,

inv_org_parameters iop,

wis_wd_maintenance_names_b wdmn

WHERE wm.work_method_code = 'MAINTENANCE'

AND wd.work_method_id = wm.work_method_id

AND iop.organization_id = wd.organization_id

AND wdmn.wd_mnt_name_id = wd.wd_mnt_name_id