VIEW: APPS.MSC_EXC_DETAIL_ITEM_V

Source

View Text - Preformatted

SELECT med.exception_type, med.exception_detail_id, med.plan_id, med.organization_id, med.sr_instance_id, med.inventory_item_id, msi.item_name, mtp.organization_code, msi.buyer_name, msi.planner_code, decode(med.exception_type, 17, med.number1, 18, med.number1, to_number(null)), decode(med.exception_type, 17, msc_get_name.PROJECT(med.number1, med.organization_id, med.plan_id, med.sr_instance_id), 18, msc_get_name.PROJECT(med.number1, med.organization_id, med.plan_id, med.sr_instance_id),null), decode(med.exception_type, 17, med.number2, 18, med.number2, to_number(null)), decode(med.exception_type, 17, msc_get_name.TASK(med.number2, med.number1, med.organization_id, med.plan_id, med.sr_instance_id), 18, msc_get_name.TASK(med.number2, med.number1, med.organization_id, med.plan_id, med.sr_instance_id), null), to_number(null), null, med.supplier_id, decode(med.exception_type, 28, msc_get_name.supplier( med.supplier_id)), decode(med.exception_type, 28, msc_get_name.supplier_site( med.supplier_site_id)), decode(med.exception_type, 17, med.quantity, 18, med.quantity, 28, med.quantity, to_number(null)), decode(med.exception_type, 17, med.quantity, 18, med.quantity, 28, to_number(null), med.quantity), med.date1, med.date2, to_date(null), to_date(null), to_date(null), to_number(null), null, null, to_number(null), to_number(null), null, to_number(null), null, null, msi.description, med.last_update_date, med.last_updated_by, med.creation_date, med.created_by, med.last_update_login from msc_exception_details med, msc_system_items msi, msc_trading_partners mtp WHERE msi.inventory_item_id=med.inventory_item_id and msi.organization_id=med.organization_id and msi.sr_instance_id=med.sr_instance_id and msi.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 (1,2,3,4,5,11,17,18,20,28) union all select med.exception_type, med.exception_detail_id, med.plan_id, med.organization_id, med.sr_instance_id, med.inventory_item_id, msi.item_name, mtp.organization_code, msi.buyer_name, msi.planner_code, ms.project_id, msc_get_name.project(ms.project_id, ms.organization_id, ms.plan_id, ms.sr_instance_id), ms.task_id, msc_get_name.task(ms.task_id, ms.project_id, ms.organization_id, ms.plan_id, ms.sr_instance_id), to_number(null), null, nvl(med.supplier_id, ms.supplier_id), msc_get_name.supplier(nvl(med.supplier_id, ms.supplier_id)), decode(med.exception_type, 37, msc_get_name.supplier_site( med.supplier_site_id), 44, msc_get_name.supplier_site( med.supplier_site_id), null), med.quantity, ms.new_order_quantity, med.date1, med.date2, ms.new_schedule_date, ms.old_schedule_date, to_date(null), ms.order_type, ml.meaning, nvl(ms.order_number, ms.transaction_id), ms.purch_line_num, to_number( decode(med.exception_type, 9, ms.schedule_compress_days, 10, round(abs(sysdate - ms.old_schedule_date), 2), 6, ms.reschedule_days, 7, ms.reschedule_days, 0)), decode(med.exception_type, 12, ms.lot_number, 31, msc_get_name.alternate_bom(med.plan_id, med.sr_instance_id, med.number2), 32, msc_get_name.alternate_rtg(med.plan_id, med.sr_instance_id, med.number2), 33, msc_get_name.item_name(med.number2,null,null,null), 34, msc_get_name.resource_code(med.resource_id, med.department_id, med.organization_id, med.plan_id, med.sr_instance_id), 43, msc_get_name.org_code(med.number2, med.number3), 44, msc_get_name.supplier(med.supplier_id)), to_number(null), null, null, msi.description, med.last_update_date, med.last_updated_by, med.creation_date, med.created_by, med.last_update_login from msc_exception_details med, msc_system_items msi, msc_trading_partners mtp, msc_supplies ms, mfg_lookups ml where msi.inventory_item_id=med.inventory_item_id and msi.organization_id=med.organization_id and msi.sr_instance_id=med.sr_instance_id and msi.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 ml.lookup_code = ms.order_type and ml.lookup_type = 'MRP_ORDER_TYPE' and med.exception_type in (6,7,8,9,10,12,31,32,33,34,37,43,44) union all select med.exception_type, med.exception_detail_id, med.plan_id, med.organization_id, med.sr_instance_id, med.inventory_item_id, msi.item_name, mtp.organization_code, msi.buyer_name, msi.planner_code, md.project_id, msc_get_name.project(md.project_id, md.organization_id, md.plan_id, md.sr_instance_id), md.task_id, msc_get_name.task(md.task_id, md.project_id, md.organization_id, md.plan_id, md.sr_instance_id), md.customer_id, msc_get_name.customer(md.customer_id), med.supplier_id, msc_get_name.supplier(med.supplier_id), msc_get_name.supplier_site(med.supplier_site_id), decode(med.exception_type, 41, med.quantity, 42, med.quantity, to_number(null)), md.using_requirement_quantity, med.date1, med.date2, md.using_assembly_demand_date, md.old_demand_date, md.dmd_satisfied_date, md.origination_type, ml.meaning, nvl(md.order_number, msc_get_name.designator(md.schedule_designator_id)), md.sales_order_line_id, round(abs(decode(med.exception_type, 13,md.old_demand_date, 14, md.old_demand_date, md.using_assembly_demand_date) -decode(med.exception_type, 13, sysdate, 14, sysdate, 15, med.date1, 16, med.date1, md.dmd_satisfied_date)),2), null, md.demand_priority, null, null, msi.description, med.last_update_date, med.last_updated_by, med.creation_date, med.created_by, med.last_update_login from msc_exception_details med, msc_system_items msi, msc_trading_partners mtp, msc_demands md, mfg_lookups ml where msi.inventory_item_id=med.inventory_item_id and msi.organization_id=med.organization_id and msi.sr_instance_id=med.sr_instance_id and msi.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.plan_id=med.plan_id and md.demand_id=med.demand_id and md.sr_instance_id=med.sr_instance_id and ml.lookup_code = md.origination_type and ml.lookup_type = 'MSC_DEMAND_ORIGINATION' and med.exception_type in (13,14,15,16,24,25,26,27,41,42) union all select med.exception_type, med.exception_detail_id, med.plan_id, med.organization_id, med.sr_instance_id, med.inventory_item_id, msi.item_name, mtp.organization_code, msi.buyer_name, msi.planner_code, nvl(ms.project_id, med.number4), msc_get_name.project(nvl(ms.project_id, med.number4), md.organization_id, md.plan_id, md.sr_instance_id), nvl(ms.task_id, med.number1), msc_get_name.task(nvl(ms.task_id, med.number1), nvl(ms.project_id, med.number4), md.organization_id, md.plan_id, md.sr_instance_id), to_number(null), null, to_number(null), null, null, med.quantity, md.using_requirement_quantity, med.date1, med.date2, md.using_assembly_demand_date, md.old_demand_date, md.dmd_satisfied_date, md.origination_type, null, nvl(md.order_number, msc_get_name.designator(md.schedule_designator_id)), md.sales_order_line_id, round(abs( md.using_assembly_demand_date - md.dmd_satisfied_date),2), null, md.demand_priority, msc_get_name.project(nvl(md.project_id,med.number3), md.organization_id, md.plan_id, md.sr_instance_id), msc_get_name.task(nvl(md.task_id, med.number2), nvl(md.project_id,med.number3), md.organization_id, md.plan_id, md.sr_instance_id), msi.description, med.last_update_date, med.last_updated_by, med.creation_date, med.created_by, med.last_update_login from msc_exception_details med, msc_system_items msi, msc_trading_partners mtp, msc_demands md, msc_supplies ms where msi.inventory_item_id=med.inventory_item_id and msi.organization_id=med.organization_id and msi.sr_instance_id=med.sr_instance_id and msi.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.plan_id(+)=med.plan_id and md.demand_id(+)=med.demand_id and md.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 med.exception_type =19

View Text - HTML Formatted

SELECT MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MED.INVENTORY_ITEM_ID
, MSI.ITEM_NAME
, MTP.ORGANIZATION_CODE
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, DECODE(MED.EXCEPTION_TYPE
, 17
, MED.NUMBER1
, 18
, MED.NUMBER1
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 17
, MSC_GET_NAME.PROJECT(MED.NUMBER1
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, 18
, MSC_GET_NAME.PROJECT(MED.NUMBER1
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 17
, MED.NUMBER2
, 18
, MED.NUMBER2
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 17
, MSC_GET_NAME.TASK(MED.NUMBER2
, MED.NUMBER1
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, 18
, MSC_GET_NAME.TASK(MED.NUMBER2
, MED.NUMBER1
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, NULL)
, TO_NUMBER(NULL)
, NULL
, MED.SUPPLIER_ID
, DECODE(MED.EXCEPTION_TYPE
, 28
, MSC_GET_NAME.SUPPLIER( MED.SUPPLIER_ID))
, DECODE(MED.EXCEPTION_TYPE
, 28
, MSC_GET_NAME.SUPPLIER_SITE( MED.SUPPLIER_SITE_ID))
, DECODE(MED.EXCEPTION_TYPE
, 17
, MED.QUANTITY
, 18
, MED.QUANTITY
, 28
, MED.QUANTITY
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 17
, MED.QUANTITY
, 18
, MED.QUANTITY
, 28
, TO_NUMBER(NULL)
, MED.QUANTITY)
, MED.DATE1
, MED.DATE2
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, MSI.DESCRIPTION
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
FROM MSC_EXCEPTION_DETAILS MED
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
WHERE MSI.INVENTORY_ITEM_ID=MED.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=MED.ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MSI.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 (1
, 2
, 3
, 4
, 5
, 11
, 17
, 18
, 20
, 28) UNION ALL SELECT MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MED.INVENTORY_ITEM_ID
, MSI.ITEM_NAME
, MTP.ORGANIZATION_CODE
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, MS.PROJECT_ID
, MSC_GET_NAME.PROJECT(MS.PROJECT_ID
, MS.ORGANIZATION_ID
, MS.PLAN_ID
, MS.SR_INSTANCE_ID)
, MS.TASK_ID
, MSC_GET_NAME.TASK(MS.TASK_ID
, MS.PROJECT_ID
, MS.ORGANIZATION_ID
, MS.PLAN_ID
, MS.SR_INSTANCE_ID)
, TO_NUMBER(NULL)
, NULL
, NVL(MED.SUPPLIER_ID
, MS.SUPPLIER_ID)
, MSC_GET_NAME.SUPPLIER(NVL(MED.SUPPLIER_ID
, MS.SUPPLIER_ID))
, DECODE(MED.EXCEPTION_TYPE
, 37
, MSC_GET_NAME.SUPPLIER_SITE( MED.SUPPLIER_SITE_ID)
, 44
, MSC_GET_NAME.SUPPLIER_SITE( MED.SUPPLIER_SITE_ID)
, NULL)
, MED.QUANTITY
, MS.NEW_ORDER_QUANTITY
, MED.DATE1
, MED.DATE2
, MS.NEW_SCHEDULE_DATE
, MS.OLD_SCHEDULE_DATE
, TO_DATE(NULL)
, MS.ORDER_TYPE
, ML.MEANING
, NVL(MS.ORDER_NUMBER
, MS.TRANSACTION_ID)
, MS.PURCH_LINE_NUM
, TO_NUMBER( DECODE(MED.EXCEPTION_TYPE
, 9
, MS.SCHEDULE_COMPRESS_DAYS
, 10
, ROUND(ABS(SYSDATE - MS.OLD_SCHEDULE_DATE)
, 2)
, 6
, MS.RESCHEDULE_DAYS
, 7
, MS.RESCHEDULE_DAYS
, 0))
, DECODE(MED.EXCEPTION_TYPE
, 12
, MS.LOT_NUMBER
, 31
, MSC_GET_NAME.ALTERNATE_BOM(MED.PLAN_ID
, MED.SR_INSTANCE_ID
, MED.NUMBER2)
, 32
, MSC_GET_NAME.ALTERNATE_RTG(MED.PLAN_ID
, MED.SR_INSTANCE_ID
, MED.NUMBER2)
, 33
, MSC_GET_NAME.ITEM_NAME(MED.NUMBER2
, NULL
, NULL
, NULL)
, 34
, MSC_GET_NAME.RESOURCE_CODE(MED.RESOURCE_ID
, MED.DEPARTMENT_ID
, MED.ORGANIZATION_ID
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, 43
, MSC_GET_NAME.ORG_CODE(MED.NUMBER2
, MED.NUMBER3)
, 44
, MSC_GET_NAME.SUPPLIER(MED.SUPPLIER_ID))
, TO_NUMBER(NULL)
, NULL
, NULL
, MSI.DESCRIPTION
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
FROM MSC_EXCEPTION_DETAILS MED
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
, MSC_SUPPLIES MS
, MFG_LOOKUPS ML
WHERE MSI.INVENTORY_ITEM_ID=MED.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=MED.ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MSI.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 ML.LOOKUP_CODE = MS.ORDER_TYPE
AND ML.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND MED.EXCEPTION_TYPE IN (6
, 7
, 8
, 9
, 10
, 12
, 31
, 32
, 33
, 34
, 37
, 43
, 44) UNION ALL SELECT MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MED.INVENTORY_ITEM_ID
, MSI.ITEM_NAME
, MTP.ORGANIZATION_CODE
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, MD.PROJECT_ID
, MSC_GET_NAME.PROJECT(MD.PROJECT_ID
, MD.ORGANIZATION_ID
, MD.PLAN_ID
, MD.SR_INSTANCE_ID)
, MD.TASK_ID
, MSC_GET_NAME.TASK(MD.TASK_ID
, MD.PROJECT_ID
, MD.ORGANIZATION_ID
, MD.PLAN_ID
, MD.SR_INSTANCE_ID)
, MD.CUSTOMER_ID
, MSC_GET_NAME.CUSTOMER(MD.CUSTOMER_ID)
, MED.SUPPLIER_ID
, MSC_GET_NAME.SUPPLIER(MED.SUPPLIER_ID)
, MSC_GET_NAME.SUPPLIER_SITE(MED.SUPPLIER_SITE_ID)
, DECODE(MED.EXCEPTION_TYPE
, 41
, MED.QUANTITY
, 42
, MED.QUANTITY
, TO_NUMBER(NULL))
, MD.USING_REQUIREMENT_QUANTITY
, MED.DATE1
, MED.DATE2
, MD.USING_ASSEMBLY_DEMAND_DATE
, MD.OLD_DEMAND_DATE
, MD.DMD_SATISFIED_DATE
, MD.ORIGINATION_TYPE
, ML.MEANING
, NVL(MD.ORDER_NUMBER
, MSC_GET_NAME.DESIGNATOR(MD.SCHEDULE_DESIGNATOR_ID))
, MD.SALES_ORDER_LINE_ID
, ROUND(ABS(DECODE(MED.EXCEPTION_TYPE
, 13
, MD.OLD_DEMAND_DATE
, 14
, MD.OLD_DEMAND_DATE
, MD.USING_ASSEMBLY_DEMAND_DATE) -DECODE(MED.EXCEPTION_TYPE
, 13
, SYSDATE
, 14
, SYSDATE
, 15
, MED.DATE1
, 16
, MED.DATE1
, MD.DMD_SATISFIED_DATE))
, 2)
, NULL
, MD.DEMAND_PRIORITY
, NULL
, NULL
, MSI.DESCRIPTION
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
FROM MSC_EXCEPTION_DETAILS MED
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
, MSC_DEMANDS MD
, MFG_LOOKUPS ML
WHERE MSI.INVENTORY_ITEM_ID=MED.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=MED.ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MSI.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.PLAN_ID=MED.PLAN_ID
AND MD.DEMAND_ID=MED.DEMAND_ID
AND MD.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND ML.LOOKUP_CODE = MD.ORIGINATION_TYPE
AND ML.LOOKUP_TYPE = 'MSC_DEMAND_ORIGINATION'
AND MED.EXCEPTION_TYPE IN (13
, 14
, 15
, 16
, 24
, 25
, 26
, 27
, 41
, 42) UNION ALL SELECT MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MED.INVENTORY_ITEM_ID
, MSI.ITEM_NAME
, MTP.ORGANIZATION_CODE
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, NVL(MS.PROJECT_ID
, MED.NUMBER4)
, MSC_GET_NAME.PROJECT(NVL(MS.PROJECT_ID
, MED.NUMBER4)
, MD.ORGANIZATION_ID
, MD.PLAN_ID
, MD.SR_INSTANCE_ID)
, NVL(MS.TASK_ID
, MED.NUMBER1)
, MSC_GET_NAME.TASK(NVL(MS.TASK_ID
, MED.NUMBER1)
, NVL(MS.PROJECT_ID
, MED.NUMBER4)
, MD.ORGANIZATION_ID
, MD.PLAN_ID
, MD.SR_INSTANCE_ID)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, MED.QUANTITY
, MD.USING_REQUIREMENT_QUANTITY
, MED.DATE1
, MED.DATE2
, MD.USING_ASSEMBLY_DEMAND_DATE
, MD.OLD_DEMAND_DATE
, MD.DMD_SATISFIED_DATE
, MD.ORIGINATION_TYPE
, NULL
, NVL(MD.ORDER_NUMBER
, MSC_GET_NAME.DESIGNATOR(MD.SCHEDULE_DESIGNATOR_ID))
, MD.SALES_ORDER_LINE_ID
, ROUND(ABS( MD.USING_ASSEMBLY_DEMAND_DATE - MD.DMD_SATISFIED_DATE)
, 2)
, NULL
, MD.DEMAND_PRIORITY
, MSC_GET_NAME.PROJECT(NVL(MD.PROJECT_ID
, MED.NUMBER3)
, MD.ORGANIZATION_ID
, MD.PLAN_ID
, MD.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(NVL(MD.TASK_ID
, MED.NUMBER2)
, NVL(MD.PROJECT_ID
, MED.NUMBER3)
, MD.ORGANIZATION_ID
, MD.PLAN_ID
, MD.SR_INSTANCE_ID)
, MSI.DESCRIPTION
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
FROM MSC_EXCEPTION_DETAILS MED
, MSC_SYSTEM_ITEMS MSI
, MSC_TRADING_PARTNERS MTP
, MSC_DEMANDS MD
, MSC_SUPPLIES MS
WHERE MSI.INVENTORY_ITEM_ID=MED.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=MED.ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID=MED.SR_INSTANCE_ID
AND MSI.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.PLAN_ID(+)=MED.PLAN_ID
AND MD.DEMAND_ID(+)=MED.DEMAND_ID
AND MD.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 MED.EXCEPTION_TYPE =19