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_EXC_DETAIL_RESOURCE_V

Source

View Text - Preformatted


SELECT med.exception_type, med.exception_detail_id, med.plan_id, med.organization_id, med.sr_instance_id, med.department_id, mdr.department_code, med.resource_id, mdr.resource_code, mtp.organization_code, mdr.department_class, mdr.resource_group_name, med.date1, med.date2, med.quantity, med.number1, med.number2, null, to_date(null), to_number(null), to_number(null), null, to_number(null), null, null, null, med.last_update_date, med.last_updated_by, med.creation_date, med.created_by, med.last_update_login from msc_exception_details med, msc_department_resources mdr, msc_trading_partners mtp WHERE mdr.department_id=med.department_id and mdr.resource_id=med.resource_id and mdr.organization_id=med.organization_id and mdr.sr_instance_id=med.sr_instance_id and mdr.plan_id=med.plan_id and mtp.partner_type=3 and mtp.sr_tp_id=med.organization_id and mtp.sr_instance_id=med.sr_instance_id and med.exception_type in (21,22,45,46) union all select med.exception_type, med.exception_detail_id, med.plan_id, med.organization_id, med.sr_instance_id, med.department_id, mdr.department_code, med.resource_id, mdr.resource_code, mtp.organization_code, mdr.department_class, mdr.resource_group_name, med.date1, med.date2, med.quantity, to_number(null), to_number(null), nvl(ms.order_number,ms.transaction_id), ms.new_schedule_date, ms.new_order_quantity, ms.order_type, ml.meaning, ms.purch_line_num, mi.item_name, null, null, med.last_update_date, med.last_updated_by, med.creation_date, med.created_by, med.last_update_login from msc_exception_details med, msc_department_resources mdr, msc_trading_partners mtp, msc_supplies ms, msc_items mi, mfg_lookups ml where mdr.department_id=med.department_id and mdr.resource_id=med.resource_id and mdr.organization_id=med.organization_id and mdr.sr_instance_id=med.sr_instance_id and mdr.plan_id=med.plan_id and mtp.partner_type=3 and mtp.sr_tp_id=med.organization_id and mtp.sr_instance_id=med.sr_instance_id and ms.plan_id=med.plan_id and ms.transaction_id=med.transaction_id and ms.sr_instance_id=med.sr_instance_id and ms.inventory_item_id=mi.inventory_item_id and ml.lookup_code = ms.order_type and ml.lookup_type = 'MRP_ORDER_TYPE' and med.exception_type=36 union all select med.exception_type, med.exception_detail_id, med.plan_id, med.organization_id, med.sr_instance_id, med.department_id, mdr.department_code, med.resource_id, mdr.resource_code, mtp.organization_code, mdr.department_class, mdr.resource_group_name, med.date1, med.date2, med.quantity, to_number(null), to_number(null), nvl(md.order_number,msc_get_name.designator(md.schedule_designator_id)), md.using_assembly_demand_date, md.using_requirement_quantity, md.origination_type, ml.meaning, md.sales_order_line_id, mi.item_name, mi.buyer_name, mi.planner_code, med.last_update_date, med.last_updated_by, med.creation_date, med.created_by, med.last_update_login from msc_exception_details med, msc_department_resources mdr, msc_trading_partners mtp, msc_demands md, msc_system_items mi, mfg_lookups ml where mdr.department_id=med.department_id and mdr.resource_id=med.resource_id and mdr.organization_id=med.organization_id and mdr.sr_instance_id=med.sr_instance_id and mdr.plan_id=med.plan_id and mtp.partner_type=3 and mtp.sr_tp_id=med.organization_id and mtp.sr_instance_id=med.sr_instance_id and md.demand_id=med.demand_id and md.sr_instance_id=med.sr_instance_id and med.plan_id=md.plan_id and md.organization_id=mi.organization_id and md.sr_instance_id=mi.sr_instance_id and md.plan_id=mi.plan_id and md.inventory_item_id=mi.inventory_item_id and ml.lookup_code = md.origination_type and ml.lookup_type = 'MSC_DEMAND_ORIGINATION' and med.exception_type in (23,35) union all select med.exception_type, med.exception_detail_id, med.plan_id, med.organization_id, med.sr_instance_id, med.department_id, mism.ship_method, -1, null, mtp.organization_code, null, null, med.date1, med.date2, med.quantity, to_number(null), to_number(null), null, to_date(null), to_number(null), to_number(null), null, to_number(null), null, null, null, med.last_update_date, med.last_updated_by, med.creation_date, med.created_by, med.last_update_login from msc_exception_details med, msc_interorg_ship_methods mism, msc_trading_partners mtp where mism.transaction_id=med.department_id and mism.plan_id=med.plan_id and mtp.partner_type=3 and mtp.sr_tp_id=mism.from_organization_id and mtp.sr_instance_id=mism.sr_instance_id and med.exception_type in (38,39,40,50,51)

View Text - HTML Formatted

SELECT MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MED.DEPARTMENT_ID
, MDR.DEPARTMENT_CODE
, MED.RESOURCE_ID
, MDR.RESOURCE_CODE
, MTP.ORGANIZATION_CODE
, MDR.DEPARTMENT_CLASS
, MDR.RESOURCE_GROUP_NAME
, MED.DATE1
, MED.DATE2
, MED.QUANTITY
, MED.NUMBER1
, MED.NUMBER2
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
FROM MSC_EXCEPTION_DETAILS MED
, MSC_DEPARTMENT_RESOURCES MDR
, MSC_TRADING_PARTNERS MTP
WHERE MDR.DEPARTMENT_ID=MED.DEPARTMENT_ID
AND MDR.RESOURCE_ID=MED.RESOURCE_ID
AND MDR.ORGANIZATION_ID=MED.ORGANIZATION_ID
AND MDR.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MDR.PLAN_ID=MED.PLAN_ID
AND MTP.PARTNER_TYPE=3
AND MTP.SR_TP_ID=MED.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MED.EXCEPTION_TYPE IN (21
, 22
, 45
, 46) UNION ALL SELECT MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MED.DEPARTMENT_ID
, MDR.DEPARTMENT_CODE
, MED.RESOURCE_ID
, MDR.RESOURCE_CODE
, MTP.ORGANIZATION_CODE
, MDR.DEPARTMENT_CLASS
, MDR.RESOURCE_GROUP_NAME
, MED.DATE1
, MED.DATE2
, MED.QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NVL(MS.ORDER_NUMBER
, MS.TRANSACTION_ID)
, MS.NEW_SCHEDULE_DATE
, MS.NEW_ORDER_QUANTITY
, MS.ORDER_TYPE
, ML.MEANING
, MS.PURCH_LINE_NUM
, MI.ITEM_NAME
, NULL
, NULL
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
FROM MSC_EXCEPTION_DETAILS MED
, MSC_DEPARTMENT_RESOURCES MDR
, MSC_TRADING_PARTNERS MTP
, MSC_SUPPLIES MS
, MSC_ITEMS MI
, MFG_LOOKUPS ML
WHERE MDR.DEPARTMENT_ID=MED.DEPARTMENT_ID
AND MDR.RESOURCE_ID=MED.RESOURCE_ID
AND MDR.ORGANIZATION_ID=MED.ORGANIZATION_ID
AND MDR.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MDR.PLAN_ID=MED.PLAN_ID
AND MTP.PARTNER_TYPE=3
AND MTP.SR_TP_ID=MED.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MS.PLAN_ID=MED.PLAN_ID
AND MS.TRANSACTION_ID=MED.TRANSACTION_ID
AND MS.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MS.INVENTORY_ITEM_ID=MI.INVENTORY_ITEM_ID
AND ML.LOOKUP_CODE = MS.ORDER_TYPE
AND ML.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND MED.EXCEPTION_TYPE=36 UNION ALL SELECT MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MED.DEPARTMENT_ID
, MDR.DEPARTMENT_CODE
, MED.RESOURCE_ID
, MDR.RESOURCE_CODE
, MTP.ORGANIZATION_CODE
, MDR.DEPARTMENT_CLASS
, MDR.RESOURCE_GROUP_NAME
, MED.DATE1
, MED.DATE2
, MED.QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NVL(MD.ORDER_NUMBER
, MSC_GET_NAME.DESIGNATOR(MD.SCHEDULE_DESIGNATOR_ID))
, MD.USING_ASSEMBLY_DEMAND_DATE
, MD.USING_REQUIREMENT_QUANTITY
, MD.ORIGINATION_TYPE
, ML.MEANING
, MD.SALES_ORDER_LINE_ID
, MI.ITEM_NAME
, MI.BUYER_NAME
, MI.PLANNER_CODE
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
FROM MSC_EXCEPTION_DETAILS MED
, MSC_DEPARTMENT_RESOURCES MDR
, MSC_TRADING_PARTNERS MTP
, MSC_DEMANDS MD
, MSC_SYSTEM_ITEMS MI
, MFG_LOOKUPS ML
WHERE MDR.DEPARTMENT_ID=MED.DEPARTMENT_ID
AND MDR.RESOURCE_ID=MED.RESOURCE_ID
AND MDR.ORGANIZATION_ID=MED.ORGANIZATION_ID
AND MDR.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MDR.PLAN_ID=MED.PLAN_ID
AND MTP.PARTNER_TYPE=3
AND MTP.SR_TP_ID=MED.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MD.DEMAND_ID=MED.DEMAND_ID
AND MD.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MED.PLAN_ID=MD.PLAN_ID
AND MD.ORGANIZATION_ID=MI.ORGANIZATION_ID
AND MD.SR_INSTANCE_ID=MI.SR_INSTANCE_ID
AND MD.PLAN_ID=MI.PLAN_ID
AND MD.INVENTORY_ITEM_ID=MI.INVENTORY_ITEM_ID
AND ML.LOOKUP_CODE = MD.ORIGINATION_TYPE
AND ML.LOOKUP_TYPE = 'MSC_DEMAND_ORIGINATION'
AND MED.EXCEPTION_TYPE IN (23
, 35) UNION ALL SELECT MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MED.DEPARTMENT_ID
, MISM.SHIP_METHOD
, -1
, NULL
, MTP.ORGANIZATION_CODE
, NULL
, NULL
, MED.DATE1
, MED.DATE2
, MED.QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
FROM MSC_EXCEPTION_DETAILS MED
, MSC_INTERORG_SHIP_METHODS MISM
, MSC_TRADING_PARTNERS MTP
WHERE MISM.TRANSACTION_ID=MED.DEPARTMENT_ID
AND MISM.PLAN_ID=MED.PLAN_ID
AND MTP.PARTNER_TYPE=3
AND MTP.SR_TP_ID=MISM.FROM_ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID=MISM.SR_INSTANCE_ID
AND MED.EXCEPTION_TYPE IN (38
, 39
, 40
, 50
, 51)