VIEW: APPS.WIP_RES_INST_EQUIPMENTS_V

Source

View Text - Preformatted

SELECT bd.department_id, bd.department_code, bd.organization_id, br.resource_id, br.resource_code, breq.instance_id, msik.concatenated_segments, msn.serial_number, bdr.capacity_units, bdr.resource_group_name, bdr.ctp_flag, bdr.exception_set_name, bdr.atp_rule_id, nvl(bdr.resource_description, ' ') "RESOURCE_DESCRIPTION", nvl(bdr.share_from_department, bd.department_code) "SHARE_FROM_DEPARTMENT", br.cost_element_id, br.purchase_item_id, br.cost_code_type, br.functional_currency_flag, br.unit_of_measure, br.default_activity_id, br.autocharge_type , br.standard_rate_flag, br.default_basis_type, br.absorption_account, br.allow_costs_flag, br.rate_variance_account, br.expenditure_type, ml1.meaning "RESOURCE_TYPE", ml2.meaning "AVAILABLE_24_HRS_FLAG", ml3.meaning "BASIS_TYPE", msik.concatenated_segments || ':' || msn.serial_number "NODE_LABEL" from mfg_lookups ml1, mfg_lookups ml2, mfg_lookups ml3, bom_resources br, bom_departments bd, bom_department_resources_v bdr, bom_resource_equipments breq, mtl_system_items_kfv msik, mtl_serial_numbers msn WHERE br.resource_id = bdr.resource_id and bd.department_id = bdr.department_id and ml1.lookup_code = br.resource_type and ml1.lookup_type = 'BOM_RESOURCE_TYPE' and ml2.lookup_code = bdr.available_24_hours_flag and ml2.lookup_type = 'SYS_YES_NO' and ml3.lookup_code = br.default_basis_type and ml3.lookup_type = 'CST_BASIS' and breq.resource_id = bdr.resource_id and breq.organization_id = bdr.organization_id and br.resource_type = 1 and msik.inventory_item_id = breq.inventory_item_id and msik.organization_id = breq.organization_id and msn.inventory_item_id = breq.inventory_item_id

View Text - HTML Formatted

SELECT BD.DEPARTMENT_ID
, BD.DEPARTMENT_CODE
, BD.ORGANIZATION_ID
, BR.RESOURCE_ID
, BR.RESOURCE_CODE
, BREQ.INSTANCE_ID
, MSIK.CONCATENATED_SEGMENTS
, MSN.SERIAL_NUMBER
, BDR.CAPACITY_UNITS
, BDR.RESOURCE_GROUP_NAME
, BDR.CTP_FLAG
, BDR.EXCEPTION_SET_NAME
, BDR.ATP_RULE_ID
, NVL(BDR.RESOURCE_DESCRIPTION
, ' ') "RESOURCE_DESCRIPTION"
, NVL(BDR.SHARE_FROM_DEPARTMENT
, BD.DEPARTMENT_CODE) "SHARE_FROM_DEPARTMENT"
, BR.COST_ELEMENT_ID
, BR.PURCHASE_ITEM_ID
, BR.COST_CODE_TYPE
, BR.FUNCTIONAL_CURRENCY_FLAG
, BR.UNIT_OF_MEASURE
, BR.DEFAULT_ACTIVITY_ID
, BR.AUTOCHARGE_TYPE
, BR.STANDARD_RATE_FLAG
, BR.DEFAULT_BASIS_TYPE
, BR.ABSORPTION_ACCOUNT
, BR.ALLOW_COSTS_FLAG
, BR.RATE_VARIANCE_ACCOUNT
, BR.EXPENDITURE_TYPE
, ML1.MEANING "RESOURCE_TYPE"
, ML2.MEANING "AVAILABLE_24_HRS_FLAG"
, ML3.MEANING "BASIS_TYPE"
, MSIK.CONCATENATED_SEGMENTS || ':' || MSN.SERIAL_NUMBER "NODE_LABEL"
FROM MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, BOM_RESOURCES BR
, BOM_DEPARTMENTS BD
, BOM_DEPARTMENT_RESOURCES_V BDR
, BOM_RESOURCE_EQUIPMENTS BREQ
, MTL_SYSTEM_ITEMS_KFV MSIK
, MTL_SERIAL_NUMBERS MSN
WHERE BR.RESOURCE_ID = BDR.RESOURCE_ID
AND BD.DEPARTMENT_ID = BDR.DEPARTMENT_ID
AND ML1.LOOKUP_CODE = BR.RESOURCE_TYPE
AND ML1.LOOKUP_TYPE = 'BOM_RESOURCE_TYPE'
AND ML2.LOOKUP_CODE = BDR.AVAILABLE_24_HOURS_FLAG
AND ML2.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML3.LOOKUP_CODE = BR.DEFAULT_BASIS_TYPE
AND ML3.LOOKUP_TYPE = 'CST_BASIS'
AND BREQ.RESOURCE_ID = BDR.RESOURCE_ID
AND BREQ.ORGANIZATION_ID = BDR.ORGANIZATION_ID
AND BR.RESOURCE_TYPE = 1
AND MSIK.INVENTORY_ITEM_ID = BREQ.INVENTORY_ITEM_ID
AND MSIK.ORGANIZATION_ID = BREQ.ORGANIZATION_ID
AND MSN.INVENTORY_ITEM_ID = BREQ.INVENTORY_ITEM_ID