eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MSC_ITEM_EXCEPTION_V

Source

View Text - Preformatted


SELECT mie.EXCEPTION_TYPE , to_char(null) , mie.exception_group , 2 , mp.COMPILE_DESIGNATOR , mie.plan_id , decode(mie.exception_type, 86, decode(mie.organization_id,-1, mp.organization_id,mie.organization_id), mie.ORGANIZATION_ID) , decode(mie.exception_type, 86, decode(mie.organization_id,-1, mp.sr_instance_id,mie.sr_instance_id), mie.sr_instance_id) , mie.PROJECT_ID , msc_get_name.project(mie.project_id, mie.organization_id, mie.plan_id, mie.sr_instance_id) , mie.TASK_ID , msc_get_name.task(mie.task_id, mie.project_id, mie.organization_id, mie.plan_id, mie.sr_instance_id) , mie.VERSION , to_char(mie.VERSION) , mie.EXCEPTION_COUNT , mie.INVENTORY_ITEM_ID , mic.CATEGORY_set_ID , mic.sr_category_id , mic.category_name , mie.PLANNING_GROUP , msi.ITEM_name , NVL(msi.product_family_id,msi.base_item_id) , msc_get_name.item_name(NVL(msi.product_family_id,msi.base_item_id), msi.organization_id, msi.plan_id, msi.sr_instance_id) , msc_get_name.org_code(mie.organization_id,mie.sr_instance_id) , mie.DEPARTMENT_ID , mie.RESOURCE_ID , null , null , null , null , to_number(null) , null , mie.DISPLAY , rowidtochar(mie.rowid) , msi.planner_code , msi.buyer_id , msi.buyer_name , msi.abc_class , mie.supplier_id , msc_get_name.supplier(mie.supplier_id) , mie.supplier_site_id , nvl(mie.new_exception_count, mie.exception_count) , to_date(null) FROM msc_item_exceptions mie, msc_plans mp, msc_SYSTEM_ITEMS msi, msc_item_categories mic WHERE NVL(mie.DISPLAY,1)=1 and mie.INVENTORY_ITEM_ID = mic.INVENTORY_ITEM_ID AND mie.sr_instance_id = mic.sr_instance_id AND mie.ORGANIZATION_ID = mic.ORGANIZATION_ID AND mie.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID AND mie.sr_instance_id = msi.sr_instance_id AND mie.ORGANIZATION_ID = msi.ORGANIZATION_ID AND mie.plan_id = msi.plan_id AND mie.inventory_item_id <> -1 AND mp.plan_id = mie.plan_id UNION ALL SELECT mie.EXCEPTION_TYPE , to_char(null) , mie.exception_group , 2 , mp.COMPILE_DESIGNATOR , mie.plan_id , decode(mie.exception_type, 86, decode(mie.organization_id,-1, mp.organization_id,mie.organization_id), mie.ORGANIZATION_ID) , decode(mie.exception_type, 86, decode(mie.organization_id,-1, mp.sr_instance_id,mie.sr_instance_id), mie.sr_instance_id) , mie.PROJECT_ID , msc_get_name.project(mie.project_id, mie.organization_id, mie.plan_id, mie.sr_instance_id) , mie.TASK_ID , msc_get_name.task(mie.task_id, mie.project_id, mie.organization_id, mie.plan_id, mie.sr_instance_id) , mie.VERSION , to_char(mie.VERSION) , mie.EXCEPTION_COUNT , mie.INVENTORY_ITEM_ID , to_number(null) , to_number(null) , null , mie.PLANNING_GROUP , null , to_number(null) , null , msc_get_name.org_code(mie.organization_id, mie.sr_instance_id) , mie.DEPARTMENT_ID , mie.RESOURCE_ID , dept.department_code , dept.resource_code , dept.department_class , dept.resource_group_name , dept.resource_type , msc_get_name.lookup_meaning('BOM_RESOURCE_TYPE',dept.resource_type) , mie.DISPLAY , rowidtochar(mie.rowid) , null , to_number(null) , null , to_number(null) , mie.supplier_id , msc_get_name.supplier(mie.supplier_id) , mie.supplier_site_id , nvl(mie.new_exception_count, mie.exception_count) , to_date(null) FROM msc_item_exceptions mie, msc_plans mp, msc_department_resources dept WHERE mp.plan_id = mie.plan_id AND dept.department_id = mie.department_id AND dept.resource_id= mie.resource_id AND dept.plan_id = mie.plan_id AND dept.organization_id = mie.organization_id AND dept.sr_instance_id = mie.sr_instance_id AND NVL(MIE.DISPLAY,1)=1 AND mie.inventory_item_id = -1 UNION ALL SELECT msc_get_name.implement_as(s.order_type, s.organization_id,s.source_organization_id,s.source_supplier_id,i.build_in_wip_flag,i.planning_make_buy_code,i.purchasing_enabled_flag,s.cfm_routing_flag), to_char(null), 10, 2, p.compile_designator, s.plan_id, s.ORGANIZATION_ID, s.sr_instance_id, s.project_id, msc_get_name.project(s.project_id, s.organization_id, s.plan_id, s.sr_instance_id), s.task_id, msc_get_name.task(s.task_id, s.project_id, s.organization_id, s.plan_id, s.sr_instance_id), to_number(NULL), to_char(null), 1, i.inventory_item_id, ic.category_set_id, ic.sr_category_id, ic.category_name, s.planning_group, i.item_name, NVL(i.product_family_id, i.base_item_id), msc_get_name.item_name(NVL(i.product_family_id,i.base_item_id), i.organization_id, i.plan_id, i.sr_instance_id), i.organization_code, to_number(null), to_number(null), null, null, null, null, to_number(null), null, 1, null, i.planner_code, i.buyer_id, i.buyer_name, i.abc_class, s.source_supplier_id, msc_get_name.supplier(s.source_supplier_id), s.source_supplier_site_id, 1, s.new_order_placement_date from msc_item_categories ic, msc_plans p, msc_supplies s, msc_system_items i WHERE i.inventory_item_id = ic.inventory_item_id AND i.sr_instance_id = ic.sr_instance_id AND i.organization_id = ic.organization_id AND i.inventory_item_id = s.inventory_item_id AND i.plan_id = s.plan_id AND i.sr_instance_id = s.sr_instance_id AND i.organization_id = s.organization_id AND s.plan_id = p.plan_id AND ((order_type = 13) OR (order_type = 5 AND nvl(s.implemented_quantity,0)+nvl(s.quantity_in_process,0) < nvl(s.firm_quantity,s.new_order_quantity) and (nvl(i.lots_exist,0) <> 2 or s.new_order_quantity =0) AND (((s.source_organization_id <> s.organization_id OR s.source_sr_instance_id <> s.sr_instance_id OR s.source_supplier_id IS NOT NULL) AND i.purchasing_enabled_flag = 1) OR (s.source_organization_id IS NULL AND s.source_supplier_id IS NULL AND i.planning_make_buy_code = 2 AND i.purchasing_enabled_flag = 1) OR (s.source_organization_id = s.organization_id AND s.source_sr_instance_id = s.sr_instance_id AND i.build_in_wip_flag = 1) OR (s.source_organization_id IS NULL AND s.source_supplier_id IS NULL AND i.planning_make_buy_code = 1 AND i.build_in_wip_flag = 1))))

View Text - HTML Formatted

SELECT MIE.EXCEPTION_TYPE
, TO_CHAR(NULL)
, MIE.EXCEPTION_GROUP
, 2
, MP.COMPILE_DESIGNATOR
, MIE.PLAN_ID
, DECODE(MIE.EXCEPTION_TYPE
, 86
, DECODE(MIE.ORGANIZATION_ID
, -1
, MP.ORGANIZATION_ID
, MIE.ORGANIZATION_ID)
, MIE.ORGANIZATION_ID)
, DECODE(MIE.EXCEPTION_TYPE
, 86
, DECODE(MIE.ORGANIZATION_ID
, -1
, MP.SR_INSTANCE_ID
, MIE.SR_INSTANCE_ID)
, MIE.SR_INSTANCE_ID)
, MIE.PROJECT_ID
, MSC_GET_NAME.PROJECT(MIE.PROJECT_ID
, MIE.ORGANIZATION_ID
, MIE.PLAN_ID
, MIE.SR_INSTANCE_ID)
, MIE.TASK_ID
, MSC_GET_NAME.TASK(MIE.TASK_ID
, MIE.PROJECT_ID
, MIE.ORGANIZATION_ID
, MIE.PLAN_ID
, MIE.SR_INSTANCE_ID)
, MIE.VERSION
, TO_CHAR(MIE.VERSION)
, MIE.EXCEPTION_COUNT
, MIE.INVENTORY_ITEM_ID
, MIC.CATEGORY_SET_ID
, MIC.SR_CATEGORY_ID
, MIC.CATEGORY_NAME
, MIE.PLANNING_GROUP
, MSI.ITEM_NAME
, NVL(MSI.PRODUCT_FAMILY_ID
, MSI.BASE_ITEM_ID)
, MSC_GET_NAME.ITEM_NAME(NVL(MSI.PRODUCT_FAMILY_ID
, MSI.BASE_ITEM_ID)
, MSI.ORGANIZATION_ID
, MSI.PLAN_ID
, MSI.SR_INSTANCE_ID)
, MSC_GET_NAME.ORG_CODE(MIE.ORGANIZATION_ID
, MIE.SR_INSTANCE_ID)
, MIE.DEPARTMENT_ID
, MIE.RESOURCE_ID
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, MIE.DISPLAY
, ROWIDTOCHAR(MIE.ROWID)
, MSI.PLANNER_CODE
, MSI.BUYER_ID
, MSI.BUYER_NAME
, MSI.ABC_CLASS
, MIE.SUPPLIER_ID
, MSC_GET_NAME.SUPPLIER(MIE.SUPPLIER_ID)
, MIE.SUPPLIER_SITE_ID
, NVL(MIE.NEW_EXCEPTION_COUNT
, MIE.EXCEPTION_COUNT)
, TO_DATE(NULL)
FROM MSC_ITEM_EXCEPTIONS MIE
, MSC_PLANS MP
, MSC_SYSTEM_ITEMS MSI
, MSC_ITEM_CATEGORIES MIC
WHERE NVL(MIE.DISPLAY
, 1)=1
AND MIE.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MIE.SR_INSTANCE_ID = MIC.SR_INSTANCE_ID
AND MIE.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MIE.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIE.SR_INSTANCE_ID = MSI.SR_INSTANCE_ID
AND MIE.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIE.PLAN_ID = MSI.PLAN_ID
AND MIE.INVENTORY_ITEM_ID <> -1
AND MP.PLAN_ID = MIE.PLAN_ID UNION ALL SELECT MIE.EXCEPTION_TYPE
, TO_CHAR(NULL)
, MIE.EXCEPTION_GROUP
, 2
, MP.COMPILE_DESIGNATOR
, MIE.PLAN_ID
, DECODE(MIE.EXCEPTION_TYPE
, 86
, DECODE(MIE.ORGANIZATION_ID
, -1
, MP.ORGANIZATION_ID
, MIE.ORGANIZATION_ID)
, MIE.ORGANIZATION_ID)
, DECODE(MIE.EXCEPTION_TYPE
, 86
, DECODE(MIE.ORGANIZATION_ID
, -1
, MP.SR_INSTANCE_ID
, MIE.SR_INSTANCE_ID)
, MIE.SR_INSTANCE_ID)
, MIE.PROJECT_ID
, MSC_GET_NAME.PROJECT(MIE.PROJECT_ID
, MIE.ORGANIZATION_ID
, MIE.PLAN_ID
, MIE.SR_INSTANCE_ID)
, MIE.TASK_ID
, MSC_GET_NAME.TASK(MIE.TASK_ID
, MIE.PROJECT_ID
, MIE.ORGANIZATION_ID
, MIE.PLAN_ID
, MIE.SR_INSTANCE_ID)
, MIE.VERSION
, TO_CHAR(MIE.VERSION)
, MIE.EXCEPTION_COUNT
, MIE.INVENTORY_ITEM_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, MIE.PLANNING_GROUP
, NULL
, TO_NUMBER(NULL)
, NULL
, MSC_GET_NAME.ORG_CODE(MIE.ORGANIZATION_ID
, MIE.SR_INSTANCE_ID)
, MIE.DEPARTMENT_ID
, MIE.RESOURCE_ID
, DEPT.DEPARTMENT_CODE
, DEPT.RESOURCE_CODE
, DEPT.DEPARTMENT_CLASS
, DEPT.RESOURCE_GROUP_NAME
, DEPT.RESOURCE_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('BOM_RESOURCE_TYPE'
, DEPT.RESOURCE_TYPE)
, MIE.DISPLAY
, ROWIDTOCHAR(MIE.ROWID)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, MIE.SUPPLIER_ID
, MSC_GET_NAME.SUPPLIER(MIE.SUPPLIER_ID)
, MIE.SUPPLIER_SITE_ID
, NVL(MIE.NEW_EXCEPTION_COUNT
, MIE.EXCEPTION_COUNT)
, TO_DATE(NULL)
FROM MSC_ITEM_EXCEPTIONS MIE
, MSC_PLANS MP
, MSC_DEPARTMENT_RESOURCES DEPT
WHERE MP.PLAN_ID = MIE.PLAN_ID
AND DEPT.DEPARTMENT_ID = MIE.DEPARTMENT_ID
AND DEPT.RESOURCE_ID= MIE.RESOURCE_ID
AND DEPT.PLAN_ID = MIE.PLAN_ID
AND DEPT.ORGANIZATION_ID = MIE.ORGANIZATION_ID
AND DEPT.SR_INSTANCE_ID = MIE.SR_INSTANCE_ID
AND NVL(MIE.DISPLAY
, 1)=1
AND MIE.INVENTORY_ITEM_ID = -1 UNION ALL SELECT MSC_GET_NAME.IMPLEMENT_AS(S.ORDER_TYPE
, S.ORGANIZATION_ID
, S.SOURCE_ORGANIZATION_ID
, S.SOURCE_SUPPLIER_ID
, I.BUILD_IN_WIP_FLAG
, I.PLANNING_MAKE_BUY_CODE
, I.PURCHASING_ENABLED_FLAG
, S.CFM_ROUTING_FLAG)
, TO_CHAR(NULL)
, 10
, 2
, P.COMPILE_DESIGNATOR
, S.PLAN_ID
, S.ORGANIZATION_ID
, S.SR_INSTANCE_ID
, S.PROJECT_ID
, MSC_GET_NAME.PROJECT(S.PROJECT_ID
, S.ORGANIZATION_ID
, S.PLAN_ID
, S.SR_INSTANCE_ID)
, S.TASK_ID
, MSC_GET_NAME.TASK(S.TASK_ID
, S.PROJECT_ID
, S.ORGANIZATION_ID
, S.PLAN_ID
, S.SR_INSTANCE_ID)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, 1
, I.INVENTORY_ITEM_ID
, IC.CATEGORY_SET_ID
, IC.SR_CATEGORY_ID
, IC.CATEGORY_NAME
, S.PLANNING_GROUP
, I.ITEM_NAME
, NVL(I.PRODUCT_FAMILY_ID
, I.BASE_ITEM_ID)
, MSC_GET_NAME.ITEM_NAME(NVL(I.PRODUCT_FAMILY_ID
, I.BASE_ITEM_ID)
, I.ORGANIZATION_ID
, I.PLAN_ID
, I.SR_INSTANCE_ID)
, I.ORGANIZATION_CODE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, 1
, NULL
, I.PLANNER_CODE
, I.BUYER_ID
, I.BUYER_NAME
, I.ABC_CLASS
, S.SOURCE_SUPPLIER_ID
, MSC_GET_NAME.SUPPLIER(S.SOURCE_SUPPLIER_ID)
, S.SOURCE_SUPPLIER_SITE_ID
, 1
, S.NEW_ORDER_PLACEMENT_DATE
FROM MSC_ITEM_CATEGORIES IC
, MSC_PLANS P
, MSC_SUPPLIES S
, MSC_SYSTEM_ITEMS I
WHERE I.INVENTORY_ITEM_ID = IC.INVENTORY_ITEM_ID
AND I.SR_INSTANCE_ID = IC.SR_INSTANCE_ID
AND I.ORGANIZATION_ID = IC.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND I.PLAN_ID = S.PLAN_ID
AND I.SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
AND S.PLAN_ID = P.PLAN_ID
AND ((ORDER_TYPE = 13) OR (ORDER_TYPE = 5
AND NVL(S.IMPLEMENTED_QUANTITY
, 0)+NVL(S.QUANTITY_IN_PROCESS
, 0) < NVL(S.FIRM_QUANTITY
, S.NEW_ORDER_QUANTITY)
AND (NVL(I.LOTS_EXIST
, 0) <> 2 OR S.NEW_ORDER_QUANTITY =0)
AND (((S.SOURCE_ORGANIZATION_ID <> S.ORGANIZATION_ID OR S.SOURCE_SR_INSTANCE_ID <> S.SR_INSTANCE_ID OR S.SOURCE_SUPPLIER_ID IS NOT NULL)
AND I.PURCHASING_ENABLED_FLAG = 1) OR (S.SOURCE_ORGANIZATION_ID IS NULL
AND S.SOURCE_SUPPLIER_ID IS NULL
AND I.PLANNING_MAKE_BUY_CODE = 2
AND I.PURCHASING_ENABLED_FLAG = 1) OR (S.SOURCE_ORGANIZATION_ID = S.ORGANIZATION_ID
AND S.SOURCE_SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND I.BUILD_IN_WIP_FLAG = 1) OR (S.SOURCE_ORGANIZATION_ID IS NULL
AND S.SOURCE_SUPPLIER_ID IS NULL
AND I.PLANNING_MAKE_BUY_CODE = 1
AND I.BUILD_IN_WIP_FLAG = 1))))