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_DRP_EXC_DETAILS_V

Source

View Text - Preformatted


SELECT MED.EXCEPTION_detail_ID , med.plan_id , decode(med.exception_type,28,to_number(NULL),MED.ORGANIZATION_ID) , med.sr_instance_id , med.inventory_item_id, MED.EXCEPTION_TYPE , MED.LAST_UPDATE_DATE , MED.LAST_UPDATED_BY , MED.CREATION_DATE , MED.CREATED_BY , MED.LAST_UPDATE_LOGIN , decode(med.exception_type,28,NULL, msc_get_name.org_code(med.organization_id, med.sr_instance_id)) , mic.category_set_id , mic.sr_category_id , mic.category_name , decode(med.exception_type, 82, decode(med.organization_id, -1, msc_get_name.item_name(med.inventory_item_id, null,null,null), msi.item_name),msi.item_name), msi.description, msi.buyer_name , msi.planner_code , msc_get_name.lookup_meaning( decode(med.exception_type, 37, 'MSC_DRP_CHANGED_EXCEPTION_NAME', 31, 'MSC_DRP_CHANGED_EXCEPTION_NAME', 2, 'MSC_DRP_CHANGED_EXCEPTION_NAME', 'MRP_EXCEPTION_CODE_TYPE'), MED.EXCEPTION_TYPE) , decode(med.exception_type, 81, med.date2,113, med.date2, nvl(ms.new_schedule_date,md.using_assembly_demand_date)), DECODE(MED.EXCEPTION_TYPE, 54, ms.new_dock_date, 72,ms.new_ship_date, 76, ms.new_ship_date,79,msp.ship_date, 80,msp.ship_date, med.date1) , MED.DATE2, DECODE(MED.EXCEPTION_TYPE, 28, to_number(NULl), 49,msc_get_name.demand_quantity( med.plan_id,med.sr_instance_id, med.supplier_id), 52,md.using_requirement_quantity, 54,to_number(null), 57,ms.new_order_quantity, 59,ms.new_order_quantity, 62,to_number(null), 64,to_number(null), 67,md.using_requirement_quantity, 70,md.using_requirement_quantity, 71, decode(med.number2, 1, ms.new_order_quantity, md.using_requirement_quantity), 76,ms.new_order_quantity, 77,ms.new_order_quantity, 96,md.original_quantity - md.unmet_quantity, 111,md.original_quantity - md.unmet_quantity, MED.QUANTITY) , DECODE(MED.EXCEPTION_TYPE, 12, ms.LOT_NUMBER, NULL) , DECODE(med.exception_type, 14, msc_drp_util.forecast_name(md.plan_id, md.sr_instance_id, md.organization_id, md.schedule_designator_id,md.forecast_set_id), 26, msc_drp_util.forecast_name(md.plan_id, md.sr_instance_id, md.organization_id, md.schedule_designator_id,md.forecast_set_id), 27, msc_drp_util.forecast_name(md.plan_id, md.sr_instance_id, md.organization_id, md.schedule_designator_id,md.forecast_set_id), 49, msc_get_name.demand_order_number(med.plan_id, med.sr_instance_id,med.supplier_id), 52,NVL(md.order_number, msc_drp_util.forecast_name(md.plan_id, md.sr_instance_id, md.organization_id, md.schedule_designator_id,md.forecast_set_id)), 67,NVL(md.order_number, msc_drp_util.forecast_name(md.plan_id, md.sr_instance_id, md.organization_id, md.schedule_designator_id,md.forecast_set_id)), 114,NVL(md.order_number, msc_drp_util.forecast_name(md.plan_id, md.sr_instance_id, md.organization_id, md.schedule_designator_id,md.forecast_set_id)), 113, med.number1, nvl(md.order_number, nvl(ms.order_number, ms.transaction_id))), msc_get_name.item_name(md.inventory_item_id,null,null,null), decode(med.exception_type, 49, med.supplier_site_id,ms.transaction_id), decode(med.exception_type, 49, med.supplier_id,md.demand_id), decode(mp.plan_type, 5, DECODE(MED.EXCEPTION_TYPE, 28, MED.QUANTITY, 37, MED.QUANTITY, 54, med.quantity, 67, med.quantity, to_number(NULL)) ,to_number(null)), decode(med.exception_type,48,decode(med.number2,1,null, NVL(med.supplier_id, ms.supplier_id)), NVL(med.supplier_id, ms.supplier_id)), decode(med.exception_type,48,decode(med.number2,1,null, msc_get_name.supplier(NVL(med.supplier_id, ms.supplier_id))), msc_get_name.supplier(NVL(med.supplier_id, ms.supplier_id))), decode(med.exception_type,48,decode(med.number2,1,null, NVL(med.supplier_site_id, ms.supplier_site_id)), NVL(med.supplier_site_id, ms.supplier_site_id)), decode(med.exception_type,48,decode(med.number2,1,null, msc_get_name.supplier_site(NVL(med.supplier_site_id, ms.supplier_site_id))), msc_get_name.supplier_site(NVL(med.supplier_site_id, ms.supplier_site_id))), md.customer_id, msc_get_name.customer(md.customer_id), md.customer_site_id, msc_get_name.customer_site(md.customer_site_id), md.demand_priority, md.dmd_satisfied_date, decode(med.exception_type, 33, msc_get_name.item_name(med.number2,null,null,null), null), decode(med.exception_type, 48,decode(med.number2,1,med.supplier_id,null), 79,msp.from_organization_id, 80,msp.from_organization_id, 81,nvl(msp.from_organization_id,med.organization_id), 82,med.organization_id, nvl(ms.source_organization_id, md.source_organization_id)), decode(med.exception_type, 48,decode(med.number2,1,med.supplier_site_id,null), 79,msp.sr_instance_id, 80,msp.sr_instance_id, 81,nvl(msp.sr_instance_id,med.sr_instance_id), 82,med.sr_instance_id, nvl(ms.source_sr_instance_id, md.source_org_instance_id)), decode(med.exception_type, 48, decode(med.number2, 1,msc_get_name.org_code(med.supplier_id,med.supplier_site_id),null), 70, msc_get_name.org_code(md.organization_id, md.sr_instance_id), 79, msc_get_name.org_code(msp.from_organization_id, msp.sr_instance_id), 80, msc_get_name.org_code(msp.from_organization_id, msp.sr_instance_id), 81,msc_get_name.org_code(nvl(msp.from_organization_id,med.organization_id), nvl(msp.sr_instance_id,med.sr_instance_id)), 82, msc_get_name.org_code(med.organization_id, med.sr_instance_id), nvl(msc_get_name.org_code( ms.source_organization_id, ms.source_sr_instance_id), msc_get_name.org_code( md.source_organization_id,md.source_org_instance_id))), DECODE(med.exception_type, 10, med.date1,113, med.date1, nvl(md.old_demand_date,ms.old_schedule_date)), nvl(msc_get_name.lookup_by_plan( 'MSC_DEMAND_ORIGINATION',md.origination_type,mp.plan_type), msc_get_name.lookup_by_plan('MRP_ORDER_TYPE',ms.order_type, mp.plan_type)), nvl(md.origination_type, ms.order_type ), decode(med.exception_type,28, med.number2, 112, med.number2, 54, med.number4,to_number(NULL)) , md.quantity_by_due_date, decode(med.exception_type,48,med.quantity, 82, decode(nvl(med.number4,0), 0, to_number(null), 100*round(med.quantity/med.number4,2)), to_number(null)), decode(med.exception_type,48,med.number1,to_number(null)), decode(med.exception_type,48, abs(med.quantity-med.number1),to_number(null)), nvl(med.action_taken,2), med.rowid, round(decode(med.exception_type, 24, GREATEST( md.dmd_satisfied_date - md.using_assembly_demand_date, 0.01), 26, GREATEST(md.dmd_satisfied_date - md.using_assembly_demand_date, 0.01), 25, LEAST( md.dmd_satisfied_date - md.using_assembly_demand_date,-0.01), 27, LEAST( md.dmd_satisfied_date - md.using_assembly_demand_date,-0.01), 62, med.quantity, 64, med.quantity * -1, 68, GREATEST( md.dmd_satisfied_date - md.using_assembly_demand_date, 0.01), to_number(null)),2), decode(med.exception_type,49,med.number1,to_number(null)), decode(med.exception_type, 49,msc_get_name.item_name(med.number1, med.number2,med.plan_id, med.sr_instance_id),null), decode(med.exception_type,49,med.number2,to_number(null)), decode(med.exception_type, 49,msc_get_name.org_code(med.number2, med.sr_instance_id),null), decode(med.exception_type,49,med.quantity,to_number(null)), decode(med.exception_type,67,med.quantity,68,med.number3, to_number(null)), md.demand_class, decode(med.exception_type, 57, mp.plan_start_date + med.number4, 59, mp.plan_start_date + med.number4, to_date(null)), decode(med.exception_type, 57, med.quantity,59, med.quantity, to_number(null)), nvl(msp.dock_date, ms.new_dock_date), decode(med.exception_type, 28, med.number3, 112, med.number3, 54, (med.number4 -med.quantity),to_number(null)), decode(med.exception_type, 67, med.quantity, to_number(null)), round(decode(med.exception_type, 57, med.number5, 24*(med.date2 - med.date1)),2), decode(med.exception_type, 48,decode(med.number2,1,med.supplier_id,null), 81,nvl(msp.to_organization_id, med.number3), 82,med.number1, nvl(msp.to_organization_id,ms.organization_id)), decode(med.exception_type, 81,nvl(msp.to_sr_instance_id,med.number4), 48,decode(med.number2,1,med.supplier_site_id,null), 82,med.number7, nvl(msp.to_sr_instance_id,ms.sr_instance_id)), decode(med.exception_type, 48, decode(med.number2, 1,msc_get_name.org_code(med.supplier_id,med.supplier_site_id),null), 70, msc_get_name.org_code(md.organization_id, md.sr_instance_id), 79, msc_get_name.org_code(msp.to_organization_id, msp.to_sr_instance_id), 80, msc_get_name.org_code(msp.to_organization_id, msp.to_sr_instance_id), 81, msc_get_name.org_code(nvl(msp.to_organization_id, med.number3), nvl(msp.to_sr_instance_id,med.number4)), 82, msc_get_name.org_code(med.number1, med.number7), msc_get_name.org_code(ms.organization_id, ms.sr_instance_id)), decode(med.exception_type, 82, med.number2,md.using_requirement_quantity), NVL(md.order_number,msc_drp_util.forecast_name(md.plan_id, md.sr_instance_id, md.organization_id, md.schedule_designator_id,md.forecast_set_id)), md.selling_price, decode(nvl(md.original_quantity,md.using_requirement_quantity), 0, to_number(null), round(md.quantity_by_due_date/ nvl(md.original_quantity,md.using_requirement_quantity),4)*100), decode(med.exception_type, 57, med.quantity - round(med.number5,2),to_number(null)), decode(med.exception_type,54, med.number4/med.quantity, to_number(NULL)), decode(sign(md.LATEST_ACCEPTABLE_DATE -md.DMD_SATISFIED_DATE), 1, greatest(md.LATEST_ACCEPTABLE_DATE - md.DMD_SATISFIED_DATE, 0.01), to_number(null)), msc_get_name.get_days_on_arrival(med.plan_id, med.exception_detail_id, med.exception_type, md.demand_id, mp.schedule_by, -1), round(mp.plan_start_date - DECODE(med.exception_type, 10, med.date1,13,md.old_demand_date, 14, md.old_demand_date, 113, med.date1, ms.old_schedule_date),2), decode(sign(md.DMD_SATISFIED_DATE -md.LATEST_ACCEPTABLE_DATE), 1, greatest(md.DMD_SATISFIED_DATE - md.LATEST_ACCEPTABLE_DATE, 0.01), to_number(null)), msc_get_name.get_days_on_arrival(med.plan_id, med.exception_detail_id, med.exception_type, md.demand_id, mp.schedule_by, 1), round(MED.DATE2 - DECODE(MED.EXCEPTION_TYPE, 2, MED.DATE1, 3, MED.DATE1, 6, MED.DATE1, 7, MED.DATE1, 20, MED.DATE1, 28, MED.DATE1,37,DATE1, 48,MED.DATE1,54, ms.new_dock_date, 57, med.date1, 59, med.date1, to_date(NULL)),2), round(DECODE(med.exception_type, 10, med.date1,13,md.old_demand_date, 14, md.old_demand_date, ms.old_schedule_date) - MED.DATE2,2), round(MED.DATE2 - DECODE(med.exception_type, 10, med.date1,13,md.old_demand_date, 14, md.old_demand_date, ms.old_schedule_date),2), decode(md.using_requirement_quantity,0, to_number(null), decode(med.exception_type, 67, (med.quantity/md.using_requirement_quantity)/ 100, 52, (med.quantity/md.using_requirement_quantity)/ 100, to_number(null))), decode(med.exception_type, 37, med.number2, 20, med.number3, to_number(null)), round(MED.DATE2 - DECODE(MED.EXCEPTION_TYPE, 2, MED.DATE1, 3, MED.DATE1, 6, MED.DATE1, 7, MED.DATE1, 20, MED.DATE1, 28, MED.DATE1,37, MED.DATE1, 48,MED.DATE1,54, ms.new_dock_date, 57, med.date1, 59, med.date1, to_date(NULL)),2), nvl(md.using_assembly_demand_date,ms.new_schedule_date), md.schedule_ship_date, md.dmd_satisfied_date, md.schedule_arrival_date, md.planned_arrival_date, decode(med.exception_type, 71, decode(med.number2, 1, ms.orig_ship_method, md.orig_shipping_method_code), md.orig_shipping_method_code), decode(med.exception_type, 71, decode(med.number2, 1, ms.ship_method, md.ship_method), nvl(msp.ship_method,nvl(ms.ship_method,md.ship_method))), decode(med.exception_type, 71, decode(med.number2, 1, ms.orig_intransit_lead_time, md.orig_intransit_lead_time), md.orig_intransit_lead_time), decode(med.exception_type, 71, decode(med.number2, 1, ms.intransit_lead_time, md.intransit_lead_time), md.intransit_lead_time), md.request_ship_date, md.request_date, md.promise_ship_date, md.promise_date, msc_get_name.lookup_meaning('SYS_YES_NO', md.atp_override_flag), md.latest_acceptable_date, msc_get_name.org_code(md.original_org_id,md.original_inst_id), decode(med.exception_type, 82, med.quantity,to_number(null)), decode(med.exception_type, 82, med.number3,to_number(null)), decode(med.exception_type, 82, med.number4,to_number(null)), decode(med.exception_type, 82, med.number5,to_number(null)), decode(med.exception_type, 82, msc_drp_util.alloc_rule_name(med.number8),null), decode(med.exception_type, 82, msc_get_name.lookup_meaning('MSC_DRP_ALLOC_RULE_TYPE', med.number6), null), nvl(msp.shipment_id,ms.shipment_id), med.date3, med.date1, nvl(msp.ship_date,ms.new_ship_date), decode(med.exception_type, 82, med.date1,to_date(null)), decode(med.exception_type, 82, med.date2,to_date(null)), null, msp.weight, msp.volume, decode(med.exception_type, 79, med.number4, 80, med.number4, to_number(null)), decode(med.exception_type, 80, decode(sign(med.number2-msp.weight), -1, to_number(null), round((1-msp.weight/med.number2)*100,2)), to_number(null)), decode(med.exception_type, 78, med.number1, 79, round((msp.weight/med.number2)*100,2), to_number(null)), decode(med.exception_type, 80, decode(sign(med.number3-msp.volume), -1,to_number(null), round((1-msp.volume/med.number3)*100,2)), to_number(null)), decode(med.exception_type, 78, med.number2, 79, round((msp.volume/med.number3)*100,2), to_number(null)), decode(med.exception_type, 80, med.number2,to_number(null)), decode(med.exception_type, 80, med.number3,to_number(null)), decode(med.exception_type, 79, med.number2,to_number(null)), decode(med.exception_type, 79, med.number3,to_number(null)), decode(med.exception_type, 2, med.quantity, 3, med.quantity, 20, med.quantity, 73, med.quantity, 74, med.quantity, 75, med.quantity, to_number(null)), nvl(ms.old_order_quantity,md.original_quantity), decode(med.exception_type, 77, msc_drp_util.sourcing_rule_name(ms.plan_id, ms.inventory_item_id, ms.source_organization_id, ms.source_sr_instance_id, ms.organization_id, ms.sr_instance_id, med.number2), null), decode(med.exception_type, 37, med.number3,to_number(null)), decode(med.exception_type, 37, med.number1,73, med.number4, 74, med.number4, to_number(null)), msi.product_family_id, msc_get_name.item_name(msi.product_family_id,null,null,null), md.order_date_type_code, decode(med.exception_type, 82, med.number8, to_number(null)), round(decode(med.date2 - med.date1,0,1, med.date2-med.date1), 2), nvl(trunc(md.using_assembly_demand_date), trunc(ms.new_schedule_date)) - trunc(sysdate), 1, decode(med.exception_type, 37, med.quantity,to_number(null)), decode(med.exception_type, 94, med.quantity,to_number(null)), decode(med.exception_type, 95, med.quantity,to_number(null)), decode(med.exception_type, 95, med.date1,to_date(null)), md.unmet_quantity, decode(nvl(md.original_quantity,md.using_requirement_quantity), 0, to_number(null), 100*round(md.unmet_quantity/ nvl(md.original_quantity,md.using_requirement_quantity),4)), ms.new_order_placement_date, ms.new_wip_start_date, decode(med.exception_type, 72, msc_drp_util.get_cal_violation(to_char(med.number2)), null), msc_get_name.org_code(md.organization_id, md.sr_instance_id), decode(med.exception_type, 3, med.quantity*msi.standard_cost, to_number(null)), DECODE ( med.exception_type, 62, msc_get_name.lookup_meaning('SYS_YES_NO', med.number2), 64, msc_get_name.lookup_meaning('SYS_YES_NO', med.number2), null), decode(med.exception_type, 57, msc_get_name.lookup_meaning('SYS_YES_NO', NVL(med.number2,2)), null), decode(med.exception_type, 31, msc_get_name.alternate_bom( med.plan_id,med.sr_instance_id,med.number2), null), decode(med.exception_type,75,med.number5, to_number(null)), decode(med.exception_type, 49,( med.quantity/ msc_get_name.demand_quantity(med.plan_id, med.sr_instance_id, med.supplier_id)) * 100, to_number(null)), decode(med.exception_type, 20, decode(nvl(med.number3,0), 0, 1000000, round(100*(med.quantity/med.number3),2)) , to_number(null)), med.date2, med.date1, med.number2, decode(med.exception_type, 73, decode(nvl(med.number4,0), 0, 1000000, round(100*(med.quantity/med.number4),2)) , to_number(null)), decode(med.exception_type, 74, decode(nvl(med.number4,0), 0, 1000000, round(100*(med.quantity/med.number4),2)) , to_number(null)), decode(med.exception_type, 75, decode(nvl(med.number5,0), 0, 1000000, round(100*(med.quantity/med.number5),2)) , to_number(null)), decode(med.exception_type, 28,decode(mp.plan_type, 5, med.number2 - med.number3, to_number(null)),to_number(null)), decode(med.exception_type, 28, decode(mp.plan_type, 5, (med.number2 - med.number3)/med.number3*100, med.quantity), 112, med.quantity, 37, med.quantity, to_number(null)), decode(med.exception_type, 114, med.number2, to_number(null)), decode(med.exception_type, 114, med.number2*msi.standard_cost, to_number(null)), decode(med.exception_type, 114, round(med.number2/md.using_requirement_quantity*100, 2), to_number(null)), decode(med.exception_type, 115, med.number2, to_number(null)), decode(med.exception_type, 115, med.number3, to_number(null)), decode(med.exception_type, 115, med.number2*msi.standard_cost, to_number(null)), decode(med.number3, 0, to_number(null), decode(med.exception_type, 115, (med.number2-med.number3)/med.number3 *100, to_number(null))), decode(med.exception_type, 115, med.number1, md.original_item_id), decode(mp.plan_type, 8, msc_get_name.lookup_meaning('MSC_PART_CONDITION',med.part_condition), 9, msc_get_name.lookup_meaning('MSC_PART_CONDITION',med.part_condition), null), msc_get_name.item_name( decode(med.exception_type, 115, med.number1, md.original_item_id),null,null,null), med.part_condition, med.action_taken_date, med.first_generated_date, round(sysdate - med.first_generated_date,2) FROM msc_exception_details MED, msc_system_items msi, msc_item_categories mic, msc_supplies ms, msc_demands md, msc_shipments msp, msc_plans mp WHERE mic.organization_id(+) = msi.organization_id AND mic.sr_instance_id(+) = msi.sr_instance_id AND mic.inventory_item_id(+) = msi.inventory_item_id AND 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 MS.sr_instance_id (+) = MED.sr_instance_id AND MS.TRANSACTION_ID (+) = MED.NUMBER1 AND MS.plan_id (+) = MED.plan_id AND MD.sr_instance_id (+) = MED.sr_instance_id AND MD.DEMAND_ID (+) = MED.NUMBER1 AND MD.plan_id (+) = MED.plan_id AND MSP.SHIPMENT_ID (+) = MED.NUMBER1 AND MSP.plan_id (+) = MED.plan_id and mp.plan_id = med.plan_id

View Text - HTML Formatted

SELECT MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, DECODE(MED.EXCEPTION_TYPE
, 28
, TO_NUMBER(NULL)
, MED.ORGANIZATION_ID)
, MED.SR_INSTANCE_ID
, MED.INVENTORY_ITEM_ID
, MED.EXCEPTION_TYPE
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
, DECODE(MED.EXCEPTION_TYPE
, 28
, NULL
, MSC_GET_NAME.ORG_CODE(MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID))
, MIC.CATEGORY_SET_ID
, MIC.SR_CATEGORY_ID
, MIC.CATEGORY_NAME
, DECODE(MED.EXCEPTION_TYPE
, 82
, DECODE(MED.ORGANIZATION_ID
, -1
, MSC_GET_NAME.ITEM_NAME(MED.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, MSI.ITEM_NAME)
, MSI.ITEM_NAME)
, MSI.DESCRIPTION
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, MSC_GET_NAME.LOOKUP_MEANING( DECODE(MED.EXCEPTION_TYPE
, 37
, 'MSC_DRP_CHANGED_EXCEPTION_NAME'
, 31
, 'MSC_DRP_CHANGED_EXCEPTION_NAME'
, 2
, 'MSC_DRP_CHANGED_EXCEPTION_NAME'
, 'MRP_EXCEPTION_CODE_TYPE')
, MED.EXCEPTION_TYPE)
, DECODE(MED.EXCEPTION_TYPE
, 81
, MED.DATE2
, 113
, MED.DATE2
, NVL(MS.NEW_SCHEDULE_DATE
, MD.USING_ASSEMBLY_DEMAND_DATE))
, DECODE(MED.EXCEPTION_TYPE
, 54
, MS.NEW_DOCK_DATE
, 72
, MS.NEW_SHIP_DATE
, 76
, MS.NEW_SHIP_DATE
, 79
, MSP.SHIP_DATE
, 80
, MSP.SHIP_DATE
, MED.DATE1)
, MED.DATE2
, DECODE(MED.EXCEPTION_TYPE
, 28
, TO_NUMBER(NULL)
, 49
, MSC_GET_NAME.DEMAND_QUANTITY( MED.PLAN_ID
, MED.SR_INSTANCE_ID
, MED.SUPPLIER_ID)
, 52
, MD.USING_REQUIREMENT_QUANTITY
, 54
, TO_NUMBER(NULL)
, 57
, MS.NEW_ORDER_QUANTITY
, 59
, MS.NEW_ORDER_QUANTITY
, 62
, TO_NUMBER(NULL)
, 64
, TO_NUMBER(NULL)
, 67
, MD.USING_REQUIREMENT_QUANTITY
, 70
, MD.USING_REQUIREMENT_QUANTITY
, 71
, DECODE(MED.NUMBER2
, 1
, MS.NEW_ORDER_QUANTITY
, MD.USING_REQUIREMENT_QUANTITY)
, 76
, MS.NEW_ORDER_QUANTITY
, 77
, MS.NEW_ORDER_QUANTITY
, 96
, MD.ORIGINAL_QUANTITY - MD.UNMET_QUANTITY
, 111
, MD.ORIGINAL_QUANTITY - MD.UNMET_QUANTITY
, MED.QUANTITY)
, DECODE(MED.EXCEPTION_TYPE
, 12
, MS.LOT_NUMBER
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 14
, MSC_DRP_UTIL.FORECAST_NAME(MD.PLAN_ID
, MD.SR_INSTANCE_ID
, MD.ORGANIZATION_ID
, MD.SCHEDULE_DESIGNATOR_ID
, MD.FORECAST_SET_ID)
, 26
, MSC_DRP_UTIL.FORECAST_NAME(MD.PLAN_ID
, MD.SR_INSTANCE_ID
, MD.ORGANIZATION_ID
, MD.SCHEDULE_DESIGNATOR_ID
, MD.FORECAST_SET_ID)
, 27
, MSC_DRP_UTIL.FORECAST_NAME(MD.PLAN_ID
, MD.SR_INSTANCE_ID
, MD.ORGANIZATION_ID
, MD.SCHEDULE_DESIGNATOR_ID
, MD.FORECAST_SET_ID)
, 49
, MSC_GET_NAME.DEMAND_ORDER_NUMBER(MED.PLAN_ID
, MED.SR_INSTANCE_ID
, MED.SUPPLIER_ID)
, 52
, NVL(MD.ORDER_NUMBER
, MSC_DRP_UTIL.FORECAST_NAME(MD.PLAN_ID
, MD.SR_INSTANCE_ID
, MD.ORGANIZATION_ID
, MD.SCHEDULE_DESIGNATOR_ID
, MD.FORECAST_SET_ID))
, 67
, NVL(MD.ORDER_NUMBER
, MSC_DRP_UTIL.FORECAST_NAME(MD.PLAN_ID
, MD.SR_INSTANCE_ID
, MD.ORGANIZATION_ID
, MD.SCHEDULE_DESIGNATOR_ID
, MD.FORECAST_SET_ID))
, 114
, NVL(MD.ORDER_NUMBER
, MSC_DRP_UTIL.FORECAST_NAME(MD.PLAN_ID
, MD.SR_INSTANCE_ID
, MD.ORGANIZATION_ID
, MD.SCHEDULE_DESIGNATOR_ID
, MD.FORECAST_SET_ID))
, 113
, MED.NUMBER1
, NVL(MD.ORDER_NUMBER
, NVL(MS.ORDER_NUMBER
, MS.TRANSACTION_ID)))
, MSC_GET_NAME.ITEM_NAME(MD.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 49
, MED.SUPPLIER_SITE_ID
, MS.TRANSACTION_ID)
, DECODE(MED.EXCEPTION_TYPE
, 49
, MED.SUPPLIER_ID
, MD.DEMAND_ID)
, DECODE(MP.PLAN_TYPE
, 5
, DECODE(MED.EXCEPTION_TYPE
, 28
, MED.QUANTITY
, 37
, MED.QUANTITY
, 54
, MED.QUANTITY
, 67
, MED.QUANTITY
, TO_NUMBER(NULL))
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, NULL
, NVL(MED.SUPPLIER_ID
, MS.SUPPLIER_ID))
, NVL(MED.SUPPLIER_ID
, MS.SUPPLIER_ID))
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, NULL
, MSC_GET_NAME.SUPPLIER(NVL(MED.SUPPLIER_ID
, MS.SUPPLIER_ID)))
, MSC_GET_NAME.SUPPLIER(NVL(MED.SUPPLIER_ID
, MS.SUPPLIER_ID)))
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, NULL
, NVL(MED.SUPPLIER_SITE_ID
, MS.SUPPLIER_SITE_ID))
, NVL(MED.SUPPLIER_SITE_ID
, MS.SUPPLIER_SITE_ID))
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, NULL
, MSC_GET_NAME.SUPPLIER_SITE(NVL(MED.SUPPLIER_SITE_ID
, MS.SUPPLIER_SITE_ID)))
, MSC_GET_NAME.SUPPLIER_SITE(NVL(MED.SUPPLIER_SITE_ID
, MS.SUPPLIER_SITE_ID)))
, MD.CUSTOMER_ID
, MSC_GET_NAME.CUSTOMER(MD.CUSTOMER_ID)
, MD.CUSTOMER_SITE_ID
, MSC_GET_NAME.CUSTOMER_SITE(MD.CUSTOMER_SITE_ID)
, MD.DEMAND_PRIORITY
, MD.DMD_SATISFIED_DATE
, DECODE(MED.EXCEPTION_TYPE
, 33
, MSC_GET_NAME.ITEM_NAME(MED.NUMBER2
, NULL
, NULL
, NULL)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, MED.SUPPLIER_ID
, NULL)
, 79
, MSP.FROM_ORGANIZATION_ID
, 80
, MSP.FROM_ORGANIZATION_ID
, 81
, NVL(MSP.FROM_ORGANIZATION_ID
, MED.ORGANIZATION_ID)
, 82
, MED.ORGANIZATION_ID
, NVL(MS.SOURCE_ORGANIZATION_ID
, MD.SOURCE_ORGANIZATION_ID))
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, MED.SUPPLIER_SITE_ID
, NULL)
, 79
, MSP.SR_INSTANCE_ID
, 80
, MSP.SR_INSTANCE_ID
, 81
, NVL(MSP.SR_INSTANCE_ID
, MED.SR_INSTANCE_ID)
, 82
, MED.SR_INSTANCE_ID
, NVL(MS.SOURCE_SR_INSTANCE_ID
, MD.SOURCE_ORG_INSTANCE_ID))
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, MSC_GET_NAME.ORG_CODE(MED.SUPPLIER_ID
, MED.SUPPLIER_SITE_ID)
, NULL)
, 70
, MSC_GET_NAME.ORG_CODE(MD.ORGANIZATION_ID
, MD.SR_INSTANCE_ID)
, 79
, MSC_GET_NAME.ORG_CODE(MSP.FROM_ORGANIZATION_ID
, MSP.SR_INSTANCE_ID)
, 80
, MSC_GET_NAME.ORG_CODE(MSP.FROM_ORGANIZATION_ID
, MSP.SR_INSTANCE_ID)
, 81
, MSC_GET_NAME.ORG_CODE(NVL(MSP.FROM_ORGANIZATION_ID
, MED.ORGANIZATION_ID)
, NVL(MSP.SR_INSTANCE_ID
, MED.SR_INSTANCE_ID))
, 82
, MSC_GET_NAME.ORG_CODE(MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID)
, NVL(MSC_GET_NAME.ORG_CODE( MS.SOURCE_ORGANIZATION_ID
, MS.SOURCE_SR_INSTANCE_ID)
, MSC_GET_NAME.ORG_CODE( MD.SOURCE_ORGANIZATION_ID
, MD.SOURCE_ORG_INSTANCE_ID)))
, DECODE(MED.EXCEPTION_TYPE
, 10
, MED.DATE1
, 113
, MED.DATE1
, NVL(MD.OLD_DEMAND_DATE
, MS.OLD_SCHEDULE_DATE))
, NVL(MSC_GET_NAME.LOOKUP_BY_PLAN( 'MSC_DEMAND_ORIGINATION'
, MD.ORIGINATION_TYPE
, MP.PLAN_TYPE)
, MSC_GET_NAME.LOOKUP_BY_PLAN('MRP_ORDER_TYPE'
, MS.ORDER_TYPE
, MP.PLAN_TYPE))
, NVL(MD.ORIGINATION_TYPE
, MS.ORDER_TYPE )
, DECODE(MED.EXCEPTION_TYPE
, 28
, MED.NUMBER2
, 112
, MED.NUMBER2
, 54
, MED.NUMBER4
, TO_NUMBER(NULL))
, MD.QUANTITY_BY_DUE_DATE
, DECODE(MED.EXCEPTION_TYPE
, 48
, MED.QUANTITY
, 82
, DECODE(NVL(MED.NUMBER4
, 0)
, 0
, TO_NUMBER(NULL)
, 100*ROUND(MED.QUANTITY/MED.NUMBER4
, 2))
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 48
, MED.NUMBER1
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 48
, ABS(MED.QUANTITY-MED.NUMBER1)
, TO_NUMBER(NULL))
, NVL(MED.ACTION_TAKEN
, 2)
, MED.ROWID
, ROUND(DECODE(MED.EXCEPTION_TYPE
, 24
, GREATEST( MD.DMD_SATISFIED_DATE - MD.USING_ASSEMBLY_DEMAND_DATE
, 0.01)
, 26
, GREATEST(MD.DMD_SATISFIED_DATE - MD.USING_ASSEMBLY_DEMAND_DATE
, 0.01)
, 25
, LEAST( MD.DMD_SATISFIED_DATE - MD.USING_ASSEMBLY_DEMAND_DATE
, -0.01)
, 27
, LEAST( MD.DMD_SATISFIED_DATE - MD.USING_ASSEMBLY_DEMAND_DATE
, -0.01)
, 62
, MED.QUANTITY
, 64
, MED.QUANTITY * -1
, 68
, GREATEST( MD.DMD_SATISFIED_DATE - MD.USING_ASSEMBLY_DEMAND_DATE
, 0.01)
, TO_NUMBER(NULL))
, 2)
, DECODE(MED.EXCEPTION_TYPE
, 49
, MED.NUMBER1
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 49
, MSC_GET_NAME.ITEM_NAME(MED.NUMBER1
, MED.NUMBER2
, MED.PLAN_ID
, MED.SR_INSTANCE_ID)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 49
, MED.NUMBER2
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 49
, MSC_GET_NAME.ORG_CODE(MED.NUMBER2
, MED.SR_INSTANCE_ID)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 49
, MED.QUANTITY
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 67
, MED.QUANTITY
, 68
, MED.NUMBER3
, TO_NUMBER(NULL))
, MD.DEMAND_CLASS
, DECODE(MED.EXCEPTION_TYPE
, 57
, MP.PLAN_START_DATE + MED.NUMBER4
, 59
, MP.PLAN_START_DATE + MED.NUMBER4
, TO_DATE(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 57
, MED.QUANTITY
, 59
, MED.QUANTITY
, TO_NUMBER(NULL))
, NVL(MSP.DOCK_DATE
, MS.NEW_DOCK_DATE)
, DECODE(MED.EXCEPTION_TYPE
, 28
, MED.NUMBER3
, 112
, MED.NUMBER3
, 54
, (MED.NUMBER4 -MED.QUANTITY)
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 67
, MED.QUANTITY
, TO_NUMBER(NULL))
, ROUND(DECODE(MED.EXCEPTION_TYPE
, 57
, MED.NUMBER5
, 24*(MED.DATE2 - MED.DATE1))
, 2)
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, MED.SUPPLIER_ID
, NULL)
, 81
, NVL(MSP.TO_ORGANIZATION_ID
, MED.NUMBER3)
, 82
, MED.NUMBER1
, NVL(MSP.TO_ORGANIZATION_ID
, MS.ORGANIZATION_ID))
, DECODE(MED.EXCEPTION_TYPE
, 81
, NVL(MSP.TO_SR_INSTANCE_ID
, MED.NUMBER4)
, 48
, DECODE(MED.NUMBER2
, 1
, MED.SUPPLIER_SITE_ID
, NULL)
, 82
, MED.NUMBER7
, NVL(MSP.TO_SR_INSTANCE_ID
, MS.SR_INSTANCE_ID))
, DECODE(MED.EXCEPTION_TYPE
, 48
, DECODE(MED.NUMBER2
, 1
, MSC_GET_NAME.ORG_CODE(MED.SUPPLIER_ID
, MED.SUPPLIER_SITE_ID)
, NULL)
, 70
, MSC_GET_NAME.ORG_CODE(MD.ORGANIZATION_ID
, MD.SR_INSTANCE_ID)
, 79
, MSC_GET_NAME.ORG_CODE(MSP.TO_ORGANIZATION_ID
, MSP.TO_SR_INSTANCE_ID)
, 80
, MSC_GET_NAME.ORG_CODE(MSP.TO_ORGANIZATION_ID
, MSP.TO_SR_INSTANCE_ID)
, 81
, MSC_GET_NAME.ORG_CODE(NVL(MSP.TO_ORGANIZATION_ID
, MED.NUMBER3)
, NVL(MSP.TO_SR_INSTANCE_ID
, MED.NUMBER4))
, 82
, MSC_GET_NAME.ORG_CODE(MED.NUMBER1
, MED.NUMBER7)
, MSC_GET_NAME.ORG_CODE(MS.ORGANIZATION_ID
, MS.SR_INSTANCE_ID))
, DECODE(MED.EXCEPTION_TYPE
, 82
, MED.NUMBER2
, MD.USING_REQUIREMENT_QUANTITY)
, NVL(MD.ORDER_NUMBER
, MSC_DRP_UTIL.FORECAST_NAME(MD.PLAN_ID
, MD.SR_INSTANCE_ID
, MD.ORGANIZATION_ID
, MD.SCHEDULE_DESIGNATOR_ID
, MD.FORECAST_SET_ID))
, MD.SELLING_PRICE
, DECODE(NVL(MD.ORIGINAL_QUANTITY
, MD.USING_REQUIREMENT_QUANTITY)
, 0
, TO_NUMBER(NULL)
, ROUND(MD.QUANTITY_BY_DUE_DATE/ NVL(MD.ORIGINAL_QUANTITY
, MD.USING_REQUIREMENT_QUANTITY)
, 4)*100)
, DECODE(MED.EXCEPTION_TYPE
, 57
, MED.QUANTITY - ROUND(MED.NUMBER5
, 2)
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 54
, MED.NUMBER4/MED.QUANTITY
, TO_NUMBER(NULL))
, DECODE(SIGN(MD.LATEST_ACCEPTABLE_DATE -MD.DMD_SATISFIED_DATE)
, 1
, GREATEST(MD.LATEST_ACCEPTABLE_DATE - MD.DMD_SATISFIED_DATE
, 0.01)
, TO_NUMBER(NULL))
, MSC_GET_NAME.GET_DAYS_ON_ARRIVAL(MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID
, MED.EXCEPTION_TYPE
, MD.DEMAND_ID
, MP.SCHEDULE_BY
, -1)
, ROUND(MP.PLAN_START_DATE - DECODE(MED.EXCEPTION_TYPE
, 10
, MED.DATE1
, 13
, MD.OLD_DEMAND_DATE
, 14
, MD.OLD_DEMAND_DATE
, 113
, MED.DATE1
, MS.OLD_SCHEDULE_DATE)
, 2)
, DECODE(SIGN(MD.DMD_SATISFIED_DATE -MD.LATEST_ACCEPTABLE_DATE)
, 1
, GREATEST(MD.DMD_SATISFIED_DATE - MD.LATEST_ACCEPTABLE_DATE
, 0.01)
, TO_NUMBER(NULL))
, MSC_GET_NAME.GET_DAYS_ON_ARRIVAL(MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID
, MED.EXCEPTION_TYPE
, MD.DEMAND_ID
, MP.SCHEDULE_BY
, 1)
, ROUND(MED.DATE2 - DECODE(MED.EXCEPTION_TYPE
, 2
, MED.DATE1
, 3
, MED.DATE1
, 6
, MED.DATE1
, 7
, MED.DATE1
, 20
, MED.DATE1
, 28
, MED.DATE1
, 37
, DATE1
, 48
, MED.DATE1
, 54
, MS.NEW_DOCK_DATE
, 57
, MED.DATE1
, 59
, MED.DATE1
, TO_DATE(NULL))
, 2)
, ROUND(DECODE(MED.EXCEPTION_TYPE
, 10
, MED.DATE1
, 13
, MD.OLD_DEMAND_DATE
, 14
, MD.OLD_DEMAND_DATE
, MS.OLD_SCHEDULE_DATE) - MED.DATE2
, 2)
, ROUND(MED.DATE2 - DECODE(MED.EXCEPTION_TYPE
, 10
, MED.DATE1
, 13
, MD.OLD_DEMAND_DATE
, 14
, MD.OLD_DEMAND_DATE
, MS.OLD_SCHEDULE_DATE)
, 2)
, DECODE(MD.USING_REQUIREMENT_QUANTITY
, 0
, TO_NUMBER(NULL)
, DECODE(MED.EXCEPTION_TYPE
, 67
, (MED.QUANTITY/MD.USING_REQUIREMENT_QUANTITY)/ 100
, 52
, (MED.QUANTITY/MD.USING_REQUIREMENT_QUANTITY)/ 100
, TO_NUMBER(NULL)))
, DECODE(MED.EXCEPTION_TYPE
, 37
, MED.NUMBER2
, 20
, MED.NUMBER3
, TO_NUMBER(NULL))
, ROUND(MED.DATE2 - DECODE(MED.EXCEPTION_TYPE
, 2
, MED.DATE1
, 3
, MED.DATE1
, 6
, MED.DATE1
, 7
, MED.DATE1
, 20
, MED.DATE1
, 28
, MED.DATE1
, 37
, MED.DATE1
, 48
, MED.DATE1
, 54
, MS.NEW_DOCK_DATE
, 57
, MED.DATE1
, 59
, MED.DATE1
, TO_DATE(NULL))
, 2)
, NVL(MD.USING_ASSEMBLY_DEMAND_DATE
, MS.NEW_SCHEDULE_DATE)
, MD.SCHEDULE_SHIP_DATE
, MD.DMD_SATISFIED_DATE
, MD.SCHEDULE_ARRIVAL_DATE
, MD.PLANNED_ARRIVAL_DATE
, DECODE(MED.EXCEPTION_TYPE
, 71
, DECODE(MED.NUMBER2
, 1
, MS.ORIG_SHIP_METHOD
, MD.ORIG_SHIPPING_METHOD_CODE)
, MD.ORIG_SHIPPING_METHOD_CODE)
, DECODE(MED.EXCEPTION_TYPE
, 71
, DECODE(MED.NUMBER2
, 1
, MS.SHIP_METHOD
, MD.SHIP_METHOD)
, NVL(MSP.SHIP_METHOD
, NVL(MS.SHIP_METHOD
, MD.SHIP_METHOD)))
, DECODE(MED.EXCEPTION_TYPE
, 71
, DECODE(MED.NUMBER2
, 1
, MS.ORIG_INTRANSIT_LEAD_TIME
, MD.ORIG_INTRANSIT_LEAD_TIME)
, MD.ORIG_INTRANSIT_LEAD_TIME)
, DECODE(MED.EXCEPTION_TYPE
, 71
, DECODE(MED.NUMBER2
, 1
, MS.INTRANSIT_LEAD_TIME
, MD.INTRANSIT_LEAD_TIME)
, MD.INTRANSIT_LEAD_TIME)
, MD.REQUEST_SHIP_DATE
, MD.REQUEST_DATE
, MD.PROMISE_SHIP_DATE
, MD.PROMISE_DATE
, MSC_GET_NAME.LOOKUP_MEANING('SYS_YES_NO'
, MD.ATP_OVERRIDE_FLAG)
, MD.LATEST_ACCEPTABLE_DATE
, MSC_GET_NAME.ORG_CODE(MD.ORIGINAL_ORG_ID
, MD.ORIGINAL_INST_ID)
, DECODE(MED.EXCEPTION_TYPE
, 82
, MED.QUANTITY
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 82
, MED.NUMBER3
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 82
, MED.NUMBER4
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 82
, MED.NUMBER5
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 82
, MSC_DRP_UTIL.ALLOC_RULE_NAME(MED.NUMBER8)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 82
, MSC_GET_NAME.LOOKUP_MEANING('MSC_DRP_ALLOC_RULE_TYPE'
, MED.NUMBER6)
, NULL)
, NVL(MSP.SHIPMENT_ID
, MS.SHIPMENT_ID)
, MED.DATE3
, MED.DATE1
, NVL(MSP.SHIP_DATE
, MS.NEW_SHIP_DATE)
, DECODE(MED.EXCEPTION_TYPE
, 82
, MED.DATE1
, TO_DATE(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 82
, MED.DATE2
, TO_DATE(NULL))
, NULL
, MSP.WEIGHT
, MSP.VOLUME
, DECODE(MED.EXCEPTION_TYPE
, 79
, MED.NUMBER4
, 80
, MED.NUMBER4
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 80
, DECODE(SIGN(MED.NUMBER2-MSP.WEIGHT)
, -1
, TO_NUMBER(NULL)
, ROUND((1-MSP.WEIGHT/MED.NUMBER2)*100
, 2))
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 78
, MED.NUMBER1
, 79
, ROUND((MSP.WEIGHT/MED.NUMBER2)*100
, 2)
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 80
, DECODE(SIGN(MED.NUMBER3-MSP.VOLUME)
, -1
, TO_NUMBER(NULL)
, ROUND((1-MSP.VOLUME/MED.NUMBER3)*100
, 2))
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 78
, MED.NUMBER2
, 79
, ROUND((MSP.VOLUME/MED.NUMBER3)*100
, 2)
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 80
, MED.NUMBER2
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 80
, MED.NUMBER3
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 79
, MED.NUMBER2
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 79
, MED.NUMBER3
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 2
, MED.QUANTITY
, 3
, MED.QUANTITY
, 20
, MED.QUANTITY
, 73
, MED.QUANTITY
, 74
, MED.QUANTITY
, 75
, MED.QUANTITY
, TO_NUMBER(NULL))
, NVL(MS.OLD_ORDER_QUANTITY
, MD.ORIGINAL_QUANTITY)
, DECODE(MED.EXCEPTION_TYPE
, 77
, MSC_DRP_UTIL.SOURCING_RULE_NAME(MS.PLAN_ID
, MS.INVENTORY_ITEM_ID
, MS.SOURCE_ORGANIZATION_ID
, MS.SOURCE_SR_INSTANCE_ID
, MS.ORGANIZATION_ID
, MS.SR_INSTANCE_ID
, MED.NUMBER2)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 37
, MED.NUMBER3
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 37
, MED.NUMBER1
, 73
, MED.NUMBER4
, 74
, MED.NUMBER4
, TO_NUMBER(NULL))
, MSI.PRODUCT_FAMILY_ID
, MSC_GET_NAME.ITEM_NAME(MSI.PRODUCT_FAMILY_ID
, NULL
, NULL
, NULL)
, MD.ORDER_DATE_TYPE_CODE
, DECODE(MED.EXCEPTION_TYPE
, 82
, MED.NUMBER8
, TO_NUMBER(NULL))
, ROUND(DECODE(MED.DATE2 - MED.DATE1
, 0
, 1
, MED.DATE2-MED.DATE1)
, 2)
, NVL(TRUNC(MD.USING_ASSEMBLY_DEMAND_DATE)
, TRUNC(MS.NEW_SCHEDULE_DATE)) - TRUNC(SYSDATE)
, 1
, DECODE(MED.EXCEPTION_TYPE
, 37
, MED.QUANTITY
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 94
, MED.QUANTITY
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 95
, MED.QUANTITY
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 95
, MED.DATE1
, TO_DATE(NULL))
, MD.UNMET_QUANTITY
, DECODE(NVL(MD.ORIGINAL_QUANTITY
, MD.USING_REQUIREMENT_QUANTITY)
, 0
, TO_NUMBER(NULL)
, 100*ROUND(MD.UNMET_QUANTITY/ NVL(MD.ORIGINAL_QUANTITY
, MD.USING_REQUIREMENT_QUANTITY)
, 4))
, MS.NEW_ORDER_PLACEMENT_DATE
, MS.NEW_WIP_START_DATE
, DECODE(MED.EXCEPTION_TYPE
, 72
, MSC_DRP_UTIL.GET_CAL_VIOLATION(TO_CHAR(MED.NUMBER2))
, NULL)
, MSC_GET_NAME.ORG_CODE(MD.ORGANIZATION_ID
, MD.SR_INSTANCE_ID)
, DECODE(MED.EXCEPTION_TYPE
, 3
, MED.QUANTITY*MSI.STANDARD_COST
, TO_NUMBER(NULL))
, DECODE ( MED.EXCEPTION_TYPE
, 62
, MSC_GET_NAME.LOOKUP_MEANING('SYS_YES_NO'
, MED.NUMBER2)
, 64
, MSC_GET_NAME.LOOKUP_MEANING('SYS_YES_NO'
, MED.NUMBER2)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 57
, MSC_GET_NAME.LOOKUP_MEANING('SYS_YES_NO'
, NVL(MED.NUMBER2
, 2))
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 31
, MSC_GET_NAME.ALTERNATE_BOM( MED.PLAN_ID
, MED.SR_INSTANCE_ID
, MED.NUMBER2)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 75
, MED.NUMBER5
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 49
, ( MED.QUANTITY/ MSC_GET_NAME.DEMAND_QUANTITY(MED.PLAN_ID
, MED.SR_INSTANCE_ID
, MED.SUPPLIER_ID)) * 100
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 20
, DECODE(NVL(MED.NUMBER3
, 0)
, 0
, 1000000
, ROUND(100*(MED.QUANTITY/MED.NUMBER3)
, 2))
, TO_NUMBER(NULL))
, MED.DATE2
, MED.DATE1
, MED.NUMBER2
, DECODE(MED.EXCEPTION_TYPE
, 73
, DECODE(NVL(MED.NUMBER4
, 0)
, 0
, 1000000
, ROUND(100*(MED.QUANTITY/MED.NUMBER4)
, 2))
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 74
, DECODE(NVL(MED.NUMBER4
, 0)
, 0
, 1000000
, ROUND(100*(MED.QUANTITY/MED.NUMBER4)
, 2))
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 75
, DECODE(NVL(MED.NUMBER5
, 0)
, 0
, 1000000
, ROUND(100*(MED.QUANTITY/MED.NUMBER5)
, 2))
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 28
, DECODE(MP.PLAN_TYPE
, 5
, MED.NUMBER2 - MED.NUMBER3
, TO_NUMBER(NULL))
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 28
, DECODE(MP.PLAN_TYPE
, 5
, (MED.NUMBER2 - MED.NUMBER3)/MED.NUMBER3*100
, MED.QUANTITY)
, 112
, MED.QUANTITY
, 37
, MED.QUANTITY
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 114
, MED.NUMBER2
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 114
, MED.NUMBER2*MSI.STANDARD_COST
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 114
, ROUND(MED.NUMBER2/MD.USING_REQUIREMENT_QUANTITY*100
, 2)
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 115
, MED.NUMBER2
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 115
, MED.NUMBER3
, TO_NUMBER(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 115
, MED.NUMBER2*MSI.STANDARD_COST
, TO_NUMBER(NULL))
, DECODE(MED.NUMBER3
, 0
, TO_NUMBER(NULL)
, DECODE(MED.EXCEPTION_TYPE
, 115
, (MED.NUMBER2-MED.NUMBER3)/MED.NUMBER3 *100
, TO_NUMBER(NULL)))
, DECODE(MED.EXCEPTION_TYPE
, 115
, MED.NUMBER1
, MD.ORIGINAL_ITEM_ID)
, DECODE(MP.PLAN_TYPE
, 8
, MSC_GET_NAME.LOOKUP_MEANING('MSC_PART_CONDITION'
, MED.PART_CONDITION)
, 9
, MSC_GET_NAME.LOOKUP_MEANING('MSC_PART_CONDITION'
, MED.PART_CONDITION)
, NULL)
, MSC_GET_NAME.ITEM_NAME( DECODE(MED.EXCEPTION_TYPE
, 115
, MED.NUMBER1
, MD.ORIGINAL_ITEM_ID)
, NULL
, NULL
, NULL)
, MED.PART_CONDITION
, MED.ACTION_TAKEN_DATE
, MED.FIRST_GENERATED_DATE
, ROUND(SYSDATE - MED.FIRST_GENERATED_DATE
, 2)
FROM MSC_EXCEPTION_DETAILS MED
, MSC_SYSTEM_ITEMS MSI
, MSC_ITEM_CATEGORIES MIC
, MSC_SUPPLIES MS
, MSC_DEMANDS MD
, MSC_SHIPMENTS MSP
, MSC_PLANS MP
WHERE MIC.ORGANIZATION_ID(+) = MSI.ORGANIZATION_ID
AND MIC.SR_INSTANCE_ID(+) = MSI.SR_INSTANCE_ID
AND MIC.INVENTORY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID
AND 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 MS.SR_INSTANCE_ID (+) = MED.SR_INSTANCE_ID
AND MS.TRANSACTION_ID (+) = MED.NUMBER1
AND MS.PLAN_ID (+) = MED.PLAN_ID
AND MD.SR_INSTANCE_ID (+) = MED.SR_INSTANCE_ID
AND MD.DEMAND_ID (+) = MED.NUMBER1
AND MD.PLAN_ID (+) = MED.PLAN_ID
AND MSP.SHIPMENT_ID (+) = MED.NUMBER1
AND MSP.PLAN_ID (+) = MED.PLAN_ID
AND MP.PLAN_ID = MED.PLAN_ID