VIEW: APPS.MSC_RELATED_EXC_DETAILS_V

Source

View Text - Preformatted

SELECT /*+ FIRST_ROWS */ MED.EXCEPTION_detail_ID , med.plan_id , 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 , mtp.organization_code, mic.category_set_id , mic.sr_category_id , mic.category_name , MIF.item_name , mif.description, mif.buyer_name , mif.planner_code , ml.meaning, nvl(mgr.project_id,0), nvl(mgr.task_id,0), nvl(mgr.planning_group,'0'), msc_get_name.PROJECT(MGR.PROJECT_ID,mgr.organization_id, mgr.plan_id,mgr.sr_instance_id), msc_get_name.TASK(MGR.TASK_ID,mgr.project_id,mgr.organization_id, mgr.plan_id,mgr.sr_instance_id), mgr.planning_group, null, null, mgr.using_assembly_demand_date, med.date1, MED.DATE2 , to_number(null), mgr.using_requirement_quantity, null, nvl(mgr.order_number, msc_get_name.designator(mgr.schedule_designator_id)), null, msc_get_name.item_name(mgr.inventory_item_id,null,null,null), null, nvl(mgr.order_number, msc_get_name.designator(mgr.schedule_designator_id)), DECODE (med.exception_type, 67, med.number1,to_number(null)), mgr.demand_id, to_number(null), to_number(null), null, null, to_number(null), null, DECODE (med.exception_type, 67, med.quantity,to_number(null)), to_number(null), null, to_number(null), null, mgr.customer_id, msc_get_name.customer(mgr.customer_id), mgr.customer_site_id , msc_get_name.customer_site(mgr.customer_site_id) , mgr.demand_priority, mgr.dmd_satisfied_date, null, null, null, to_number(null), to_number(null), null, to_date(null), to_number(null), mgr.using_requirement_quantity, mgr.old_demand_date, null, to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), mgr.quantity_by_due_date, to_number(null), to_number(null), to_number(null), to_number(null), round(greatest( mgr.dmd_satisfied_date - mgr.using_assembly_demand_date, 0.01),2), nvl(med.number4,1), msc_get_name.lookup_meaning('MSC_ORIGINATION_CODE_TYPE',nvl(med.number4,1)), mgr.demand_class, to_number (null), to_number (null), null, to_date(null), to_number(null), to_date(null), to_number(null), decode(med.exception_type, 67,med.quantity, to_number(null)), null FROM msc_exception_details MED, msc_system_items MIF, msc_item_categories mic, msc_demands MGR, msc_trading_partners mtp, mfg_lookups ml WHERE mic.organization_id = mif.organization_id AND mic.sr_instance_id = mif.sr_instance_id AND mic.inventory_item_id = mif.inventory_item_id AND MIF.inventory_item_id = MED.INVENTORY_ITEM_ID AND MIF.organization_id = MED.organization_id AND MIF.sr_instance_id = MED.sr_instance_id AND MIF.plan_id = MED.plan_id AND MGR.sr_instance_id = MED.sr_instance_id AND MGR.DEMAND_ID = MED.NUMBER1 AND MGR.plan_id = MED.plan_id and med.exception_type in (24,26,52,67) and ml.lookup_type = 'MRP_EXCEPTION_CODE_TYPE' and ml.lookup_code = med.exception_type and mtp.partner_type =3 and mtp.sr_instance_id = med.sr_instance_id and mtp.sr_tp_id = med.organization_id UNION ALL SELECT /*+ FIRST_ROWS */ MED.EXCEPTION_detail_ID , med.plan_id , 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 , mtp.organization_code, mic.category_set_id , mic.sr_category_id , mic.category_name , MIF.item_name , mif.description, mif.buyer_name , mif.planner_code , ml.meaning, nvl(mr.project_id,0), nvl(mr.task_id,0), nvl(mr.planning_group,'0'), msc_get_name.PROJECT(MR.PROJECT_ID,mr.organization_id, mr.plan_id,mr.sr_instance_id), msc_get_name.TASK(MR.TASK_ID,mr.project_id,mr.organization_id, mr.plan_id,mr.sr_instance_id), mr.planning_group, null, null, mr.new_schedule_date, med.date1, med.date2, decode(med.exception_type, 9, mr.schedule_compress_days, to_number(null)), mr.new_order_quantity, null, NVL(mr.order_number, to_char(mr.transaction_id)), null, null, null, null, mr.transaction_id, to_number(null), to_number(null), to_number(null), null, null, to_number(null), null, decode(med.exception_type, 37,med.quantity,54,med.quantity,to_number(null)), nvl(med.supplier_id,mr.supplier_id), msc_get_name.supplier(NVL(med.supplier_id, mr.supplier_id)), NVL(med.supplier_site_id, mr.supplier_site_id), msc_get_name.supplier_site(NVL(med.supplier_site_id, mr.supplier_site_id)), to_number(null), null, to_number(null), null, to_number(null), to_date(null), null, null, null, med.number2, med.number4, decode(med.exception_type, 54, msc_get_name.org_code(med.number2,med.number4), 57, msc_get_name.org_code(med.number2,med.number4), 59, msc_get_name.org_code(med.number2,med.number4), null), mr.new_schedule_date, to_number(null), to_number(null), mr.old_schedule_date, ml2.meaning, mr.order_type, to_number(null), to_number(null), to_number(null), to_number(null), decode(med.exception_type,37,med.number4,54,med.number4,to_number(null)), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), DECODE( med.exception_type, 62,med.quantity,66,med.quantity,to_number(null)), 1, ml3.meaning, null, to_number (null), to_number (null), DECODE( med.exception_type, 62, msc_get_name.lookup_meaning ('RESOURCE_FIRM_TYPE', med.number2), null), 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)), mr.new_dock_date, decode(med.exception_type,37,med.number4-med.quantity, 54,med.number4 - med.quantity, to_number(null)), to_number(null), decode(med.exception_type, 57, mr.ship_method, 59, mr.ship_method, null) FROM msc_exception_details MED, msc_system_items MIF, msc_item_categories mic, msc_supplies MR, msc_trading_partners mtp, mfg_lookups ml, mfg_lookups ml2, mfg_lookups ml3, msc_plans mp WHERE mic.organization_id = mif.organization_id AND mic.sr_instance_id = mif.sr_instance_id AND mic.inventory_item_id = mif.inventory_item_id AND MIF.inventory_item_id = MED.INVENTORY_ITEM_ID AND MIF.organization_id = MED.organization_id AND MIF.sr_instance_id = MED.sr_instance_id AND MIF.plan_id = MED.plan_id AND MR.sr_instance_id = MED.sr_instance_id AND MR.TRANSACTION_ID = MED.NUMBER1 AND MR.plan_id = MED.plan_id and med.exception_type in (9,36,37,54,57,59,60,62,63,66,67) and ml.lookup_type = 'MRP_EXCEPTION_CODE_TYPE' and ml.lookup_code = med.exception_type and mtp.partner_type =3 and mtp.sr_instance_id = med.sr_instance_id and mtp.sr_tp_id = med.organization_id and ml2.lookup_type = 'MRP_ORDER_TYPE' and ml2.lookup_code = mr.order_type and ml3.lookup_type = 'MSC_ORIGINATION_CODE_TYPE' and ml3.lookup_code = 1 and mp.plan_id = med.plan_id UNION ALL SELECT /*+ FIRST_ROWS */ MED.EXCEPTION_detail_ID , med.plan_id , MED.ORGANIZATION_ID , med.sr_instance_id , mr.inventory_item_id, MED.EXCEPTION_TYPE , MED.LAST_UPDATE_DATE , MED.LAST_UPDATED_BY , MED.CREATION_DATE , MED.CREATED_BY , MED.LAST_UPDATE_LOGIN , mtp.organization_code, to_number(null), to_number(null), null, msi.item_name, msi.description, msi.buyer_name, msi.planner_code, ml.meaning, nvl(mr.project_id,0), nvl(mr.task_id,0), nvl(mr.planning_group,'0'), msc_get_name.PROJECT(MR.PROJECT_ID,mr.organization_id, mr.plan_id,mr.sr_instance_id), msc_get_name.TASK(MR.TASK_ID,mr.project_id,mr.organization_id, mr.plan_id,mr.sr_instance_id), mr.planning_group, null, null, mr.new_schedule_date, MED.DATE1, MED.DATE2 , to_number(null), mr.new_order_quantity, null, nvl(mr.order_number,to_char(mr.transaction_id)), null, null, null, null, mr.transaction_id, to_number(null), mdr.department_id, mdr.resource_id, mdr.department_code, mdr.resource_code, mdr.resource_type, ml2.meaning, med.quantity, to_number(null), null, to_number(null), null, to_number(null), null, to_number(null), null, to_number(null), to_date(null), null, null, null, to_number(null), to_number(null), null, to_date(null), to_number(null), to_number(null), to_date(null), ml3.meaning, mr.order_type, mdr.batchable_flag, to_number(null), to_number(null), to_number(null), DECODE( med.exception_type, 36, med.number4, 53, med.number4, to_number(null)), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), decode(med.exception_type, 63, med.quantity,to_number(null)), 1, ml4.meaning, null, DECODE( med.exception_type, 36, med.number2, 53, med.number2, 58, med.number2, 60, med.number2, 63, med.number3, to_number(null)), DECODE( med.exception_type, 36, med.number2, 53, med.number3, 58, med.number3, 60, med.number3, 63, med.number4, to_number(null)), DECODE( med.exception_type, 63, msc_get_name.lookup_meaning('RESOURCE_FIRM_TYPE',med.number2),null), decode(med.exception_type, 58, mp.plan_start_date + med.number4, 60, mp.plan_start_date + med.number4, to_date(null)), decode(med.exception_type, 58,med.quantity,60,med.quantity,to_number(null)), mr.new_dock_date, DECODE( med.exception_type, 36, med.number4 - med.quantity, 53, med.number4 - med.quantity, to_number(null)), to_number(null), null FROM msc_exception_details MED, msc_department_resources mdr, msc_system_items msi, msc_supplies MR, msc_trading_partners mtp, mfg_lookups ml, mfg_lookups ml2, mfg_lookups ml3, mfg_lookups ml4, msc_plans mp 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 MR.sr_instance_id = MED.sr_instance_id AND MR.TRANSACTION_ID = MED.NUMBER1 AND MR.plan_id = MED.plan_id AND MSI.inventory_item_id = MR.INVENTORY_ITEM_ID AND MSI.organization_id = MR.organization_id AND MSI.sr_instance_id = MR.sr_instance_id AND MSI.plan_id = MR.plan_id and med.exception_type in (36,53,58,60,63) and ml.lookup_type = 'MRP_EXCEPTION_CODE_TYPE' and ml.lookup_code = med.exception_type and mtp.partner_type =3 and mtp.sr_instance_id = med.sr_instance_id and mtp.sr_tp_id = med.organization_id and ml2.lookup_type = 'BOM_RESOURCE_TYPE' and ml2.lookup_code = mdr.resource_type and ml3.lookup_type = 'MRP_ORDER_TYPE' and ml3.lookup_code = mr.order_type and ml4.lookup_type = 'MSC_ORIGINATION_CODE_TYPE' and ml4.lookup_code = 1 and mp.plan_id = med.plan_id UNION ALL SELECT /*+ FIRST_ROWS */ MED.EXCEPTION_detail_ID , med.plan_id , MED.ORGANIZATION_ID , med.sr_instance_id , mr.inventory_item_id, MED.EXCEPTION_TYPE , MED.LAST_UPDATE_DATE , MED.LAST_UPDATED_BY , MED.CREATION_DATE , MED.CREATED_BY , MED.LAST_UPDATE_LOGIN , mtp.organization_code, to_number(null), to_number(null), null, msi.item_name, msi.description, msi.buyer_name, msi.planner_code, ml.meaning, nvl(mr.project_id,0), nvl(mr.task_id,0), nvl(mr.planning_group,'0'), msc_get_name.PROJECT(MR.PROJECT_ID,mr.organization_id, mr.plan_id,mr.sr_instance_id), msc_get_name.TASK(MR.TASK_ID,mr.project_id,mr.organization_id, mr.plan_id,mr.sr_instance_id), mr.planning_group, null, null, mr.new_ship_date, MED.DATE1, MED.DATE2 , to_number(null), med.number3, null, nvl(mr.order_number,to_char(mr.transaction_id)), null, null, null, null, mr.transaction_id, to_number(null), to_number(null), to_number(null), null, null, to_number(null), null, med.quantity, to_number(null), null, to_number(null), null, to_number(null), null, to_number(null), null, to_number(null), to_date(null), null, null, null, med.number2, med.number4, msc_get_name.org_code(med.number2, med.number4), to_date(null), to_number(null), to_number(null), to_date(null), ml2.meaning, mr.order_type, to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), decode(med.exception_type, 55, msi.unit_weight * mr.new_order_quantity, 56, msi.unit_volume*mr.new_order_quantity,to_number(null)), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), 1, ml3.meaning, null, to_number(null), to_number(null), null, to_date(null), to_number(null), mr.new_dock_date, decode(med.exception_type, 55, msi.unit_weight * mr.new_order_quantity - med.quantity, 56, msi.unit_volume*mr.new_order_quantity - med.quantity, to_number(null)), to_number(null), mr.ship_method FROM msc_exception_details med, msc_interorg_ship_methods msim, msc_system_items msi, msc_supplies mr, msc_trading_partners mtp, mfg_lookups ml, mfg_lookups ml2, mfg_lookups ml3 WHERE msim.transaction_id = med.department_id AND msim.plan_id = med.plan_id AND mr.sr_instance_id = med.sr_instance_id AND mr.transaction_id = med.number1 AND mr.plan_id = med.plan_id AND MSI.inventory_item_id = MR.INVENTORY_ITEM_ID AND MSI.organization_id = MR.organization_id AND MSI.sr_instance_id = MR.sr_instance_id AND MSI.plan_id = MR.plan_id AND med.exception_type in (55,56) AND ml.lookup_type = 'MRP_EXCEPTION_CODE_TYPE' AND ml.lookup_code = med.exception_type AND mtp.partner_type = 3 AND mtp.sr_instance_id = med.sr_instance_id AND mtp.sr_tp_id = med.organization_id and ml2.lookup_type = 'MRP_ORDER_TYPE' and ml2.lookup_code = mr.order_type and ml3.lookup_type = 'MSC_ORIGINATION_CODE_TYPE' and ml3.lookup_code = 1

View Text - HTML Formatted

SELECT /*+ FIRST_ROWS */ MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, 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
, MTP.ORGANIZATION_CODE
, MIC.CATEGORY_SET_ID
, MIC.SR_CATEGORY_ID
, MIC.CATEGORY_NAME
, MIF.ITEM_NAME
, MIF.DESCRIPTION
, MIF.BUYER_NAME
, MIF.PLANNER_CODE
, ML.MEANING
, NVL(MGR.PROJECT_ID
, 0)
, NVL(MGR.TASK_ID
, 0)
, NVL(MGR.PLANNING_GROUP
, '0')
, MSC_GET_NAME.PROJECT(MGR.PROJECT_ID
, MGR.ORGANIZATION_ID
, MGR.PLAN_ID
, MGR.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(MGR.TASK_ID
, MGR.PROJECT_ID
, MGR.ORGANIZATION_ID
, MGR.PLAN_ID
, MGR.SR_INSTANCE_ID)
, MGR.PLANNING_GROUP
, NULL
, NULL
, MGR.USING_ASSEMBLY_DEMAND_DATE
, MED.DATE1
, MED.DATE2
, TO_NUMBER(NULL)
, MGR.USING_REQUIREMENT_QUANTITY
, NULL
, NVL(MGR.ORDER_NUMBER
, MSC_GET_NAME.DESIGNATOR(MGR.SCHEDULE_DESIGNATOR_ID))
, NULL
, MSC_GET_NAME.ITEM_NAME(MGR.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL)
, NULL
, NVL(MGR.ORDER_NUMBER
, MSC_GET_NAME.DESIGNATOR(MGR.SCHEDULE_DESIGNATOR_ID))
, DECODE (MED.EXCEPTION_TYPE
, 67
, MED.NUMBER1
, TO_NUMBER(NULL))
, MGR.DEMAND_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, DECODE (MED.EXCEPTION_TYPE
, 67
, MED.QUANTITY
, TO_NUMBER(NULL))
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, MGR.CUSTOMER_ID
, MSC_GET_NAME.CUSTOMER(MGR.CUSTOMER_ID)
, MGR.CUSTOMER_SITE_ID
, MSC_GET_NAME.CUSTOMER_SITE(MGR.CUSTOMER_SITE_ID)
, MGR.DEMAND_PRIORITY
, MGR.DMD_SATISFIED_DATE
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, MGR.USING_REQUIREMENT_QUANTITY
, MGR.OLD_DEMAND_DATE
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MGR.QUANTITY_BY_DUE_DATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ROUND(GREATEST( MGR.DMD_SATISFIED_DATE - MGR.USING_ASSEMBLY_DEMAND_DATE
, 0.01)
, 2)
, NVL(MED.NUMBER4
, 1)
, MSC_GET_NAME.LOOKUP_MEANING('MSC_ORIGINATION_CODE_TYPE'
, NVL(MED.NUMBER4
, 1))
, MGR.DEMAND_CLASS
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, DECODE(MED.EXCEPTION_TYPE
, 67
, MED.QUANTITY
, TO_NUMBER(NULL))
, NULL
FROM MSC_EXCEPTION_DETAILS MED
, MSC_SYSTEM_ITEMS MIF
, MSC_ITEM_CATEGORIES MIC
, MSC_DEMANDS MGR
, MSC_TRADING_PARTNERS MTP
, MFG_LOOKUPS ML
WHERE MIC.ORGANIZATION_ID = MIF.ORGANIZATION_ID
AND MIC.SR_INSTANCE_ID = MIF.SR_INSTANCE_ID
AND MIC.INVENTORY_ITEM_ID = MIF.INVENTORY_ITEM_ID
AND MIF.INVENTORY_ITEM_ID = MED.INVENTORY_ITEM_ID
AND MIF.ORGANIZATION_ID = MED.ORGANIZATION_ID
AND MIF.SR_INSTANCE_ID = MED.SR_INSTANCE_ID
AND MIF.PLAN_ID = MED.PLAN_ID
AND MGR.SR_INSTANCE_ID = MED.SR_INSTANCE_ID
AND MGR.DEMAND_ID = MED.NUMBER1
AND MGR.PLAN_ID = MED.PLAN_ID
AND MED.EXCEPTION_TYPE IN (24
, 26
, 52
, 67)
AND ML.LOOKUP_TYPE = 'MRP_EXCEPTION_CODE_TYPE'
AND ML.LOOKUP_CODE = MED.EXCEPTION_TYPE
AND MTP.PARTNER_TYPE =3
AND MTP.SR_INSTANCE_ID = MED.SR_INSTANCE_ID
AND MTP.SR_TP_ID = MED.ORGANIZATION_ID UNION ALL SELECT /*+ FIRST_ROWS */ MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, 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
, MTP.ORGANIZATION_CODE
, MIC.CATEGORY_SET_ID
, MIC.SR_CATEGORY_ID
, MIC.CATEGORY_NAME
, MIF.ITEM_NAME
, MIF.DESCRIPTION
, MIF.BUYER_NAME
, MIF.PLANNER_CODE
, ML.MEANING
, NVL(MR.PROJECT_ID
, 0)
, NVL(MR.TASK_ID
, 0)
, NVL(MR.PLANNING_GROUP
, '0')
, MSC_GET_NAME.PROJECT(MR.PROJECT_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(MR.TASK_ID
, MR.PROJECT_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_ID)
, MR.PLANNING_GROUP
, NULL
, NULL
, MR.NEW_SCHEDULE_DATE
, MED.DATE1
, MED.DATE2
, DECODE(MED.EXCEPTION_TYPE
, 9
, MR.SCHEDULE_COMPRESS_DAYS
, TO_NUMBER(NULL))
, MR.NEW_ORDER_QUANTITY
, NULL
, NVL(MR.ORDER_NUMBER
, TO_CHAR(MR.TRANSACTION_ID))
, NULL
, NULL
, NULL
, NULL
, MR.TRANSACTION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, DECODE(MED.EXCEPTION_TYPE
, 37
, MED.QUANTITY
, 54
, MED.QUANTITY
, TO_NUMBER(NULL))
, NVL(MED.SUPPLIER_ID
, MR.SUPPLIER_ID)
, MSC_GET_NAME.SUPPLIER(NVL(MED.SUPPLIER_ID
, MR.SUPPLIER_ID))
, NVL(MED.SUPPLIER_SITE_ID
, MR.SUPPLIER_SITE_ID)
, MSC_GET_NAME.SUPPLIER_SITE(NVL(MED.SUPPLIER_SITE_ID
, MR.SUPPLIER_SITE_ID))
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, MED.NUMBER2
, MED.NUMBER4
, DECODE(MED.EXCEPTION_TYPE
, 54
, MSC_GET_NAME.ORG_CODE(MED.NUMBER2
, MED.NUMBER4)
, 57
, MSC_GET_NAME.ORG_CODE(MED.NUMBER2
, MED.NUMBER4)
, 59
, MSC_GET_NAME.ORG_CODE(MED.NUMBER2
, MED.NUMBER4)
, NULL)
, MR.NEW_SCHEDULE_DATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, MR.OLD_SCHEDULE_DATE
, ML2.MEANING
, MR.ORDER_TYPE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(MED.EXCEPTION_TYPE
, 37
, MED.NUMBER4
, 54
, MED.NUMBER4
, TO_NUMBER(NULL))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE( MED.EXCEPTION_TYPE
, 62
, MED.QUANTITY
, 66
, MED.QUANTITY
, TO_NUMBER(NULL))
, 1
, ML3.MEANING
, NULL
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, DECODE( MED.EXCEPTION_TYPE
, 62
, MSC_GET_NAME.LOOKUP_MEANING ('RESOURCE_FIRM_TYPE'
, MED.NUMBER2)
, NULL)
, 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))
, MR.NEW_DOCK_DATE
, DECODE(MED.EXCEPTION_TYPE
, 37
, MED.NUMBER4-MED.QUANTITY
, 54
, MED.NUMBER4 - MED.QUANTITY
, TO_NUMBER(NULL))
, TO_NUMBER(NULL)
, DECODE(MED.EXCEPTION_TYPE
, 57
, MR.SHIP_METHOD
, 59
, MR.SHIP_METHOD
, NULL)
FROM MSC_EXCEPTION_DETAILS MED
, MSC_SYSTEM_ITEMS MIF
, MSC_ITEM_CATEGORIES MIC
, MSC_SUPPLIES MR
, MSC_TRADING_PARTNERS MTP
, MFG_LOOKUPS ML
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, MSC_PLANS MP
WHERE MIC.ORGANIZATION_ID = MIF.ORGANIZATION_ID
AND MIC.SR_INSTANCE_ID = MIF.SR_INSTANCE_ID
AND MIC.INVENTORY_ITEM_ID = MIF.INVENTORY_ITEM_ID
AND MIF.INVENTORY_ITEM_ID = MED.INVENTORY_ITEM_ID
AND MIF.ORGANIZATION_ID = MED.ORGANIZATION_ID
AND MIF.SR_INSTANCE_ID = MED.SR_INSTANCE_ID
AND MIF.PLAN_ID = MED.PLAN_ID
AND MR.SR_INSTANCE_ID = MED.SR_INSTANCE_ID
AND MR.TRANSACTION_ID = MED.NUMBER1
AND MR.PLAN_ID = MED.PLAN_ID
AND MED.EXCEPTION_TYPE IN (9
, 36
, 37
, 54
, 57
, 59
, 60
, 62
, 63
, 66
, 67)
AND ML.LOOKUP_TYPE = 'MRP_EXCEPTION_CODE_TYPE'
AND ML.LOOKUP_CODE = MED.EXCEPTION_TYPE
AND MTP.PARTNER_TYPE =3
AND MTP.SR_INSTANCE_ID = MED.SR_INSTANCE_ID
AND MTP.SR_TP_ID = MED.ORGANIZATION_ID
AND ML2.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND ML2.LOOKUP_CODE = MR.ORDER_TYPE
AND ML3.LOOKUP_TYPE = 'MSC_ORIGINATION_CODE_TYPE'
AND ML3.LOOKUP_CODE = 1
AND MP.PLAN_ID = MED.PLAN_ID UNION ALL SELECT /*+ FIRST_ROWS */ MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MR.INVENTORY_ITEM_ID
, MED.EXCEPTION_TYPE
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
, MTP.ORGANIZATION_CODE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, MSI.ITEM_NAME
, MSI.DESCRIPTION
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, ML.MEANING
, NVL(MR.PROJECT_ID
, 0)
, NVL(MR.TASK_ID
, 0)
, NVL(MR.PLANNING_GROUP
, '0')
, MSC_GET_NAME.PROJECT(MR.PROJECT_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(MR.TASK_ID
, MR.PROJECT_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_ID)
, MR.PLANNING_GROUP
, NULL
, NULL
, MR.NEW_SCHEDULE_DATE
, MED.DATE1
, MED.DATE2
, TO_NUMBER(NULL)
, MR.NEW_ORDER_QUANTITY
, NULL
, NVL(MR.ORDER_NUMBER
, TO_CHAR(MR.TRANSACTION_ID))
, NULL
, NULL
, NULL
, NULL
, MR.TRANSACTION_ID
, TO_NUMBER(NULL)
, MDR.DEPARTMENT_ID
, MDR.RESOURCE_ID
, MDR.DEPARTMENT_CODE
, MDR.RESOURCE_CODE
, MDR.RESOURCE_TYPE
, ML2.MEANING
, MED.QUANTITY
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, ML3.MEANING
, MR.ORDER_TYPE
, MDR.BATCHABLE_FLAG
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE( MED.EXCEPTION_TYPE
, 36
, MED.NUMBER4
, 53
, MED.NUMBER4
, TO_NUMBER(NULL))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(MED.EXCEPTION_TYPE
, 63
, MED.QUANTITY
, TO_NUMBER(NULL))
, 1
, ML4.MEANING
, NULL
, DECODE( MED.EXCEPTION_TYPE
, 36
, MED.NUMBER2
, 53
, MED.NUMBER2
, 58
, MED.NUMBER2
, 60
, MED.NUMBER2
, 63
, MED.NUMBER3
, TO_NUMBER(NULL))
, DECODE( MED.EXCEPTION_TYPE
, 36
, MED.NUMBER2
, 53
, MED.NUMBER3
, 58
, MED.NUMBER3
, 60
, MED.NUMBER3
, 63
, MED.NUMBER4
, TO_NUMBER(NULL))
, DECODE( MED.EXCEPTION_TYPE
, 63
, MSC_GET_NAME.LOOKUP_MEANING('RESOURCE_FIRM_TYPE'
, MED.NUMBER2)
, NULL)
, DECODE(MED.EXCEPTION_TYPE
, 58
, MP.PLAN_START_DATE + MED.NUMBER4
, 60
, MP.PLAN_START_DATE + MED.NUMBER4
, TO_DATE(NULL))
, DECODE(MED.EXCEPTION_TYPE
, 58
, MED.QUANTITY
, 60
, MED.QUANTITY
, TO_NUMBER(NULL))
, MR.NEW_DOCK_DATE
, DECODE( MED.EXCEPTION_TYPE
, 36
, MED.NUMBER4 - MED.QUANTITY
, 53
, MED.NUMBER4 - MED.QUANTITY
, TO_NUMBER(NULL))
, TO_NUMBER(NULL)
, NULL
FROM MSC_EXCEPTION_DETAILS MED
, MSC_DEPARTMENT_RESOURCES MDR
, MSC_SYSTEM_ITEMS MSI
, MSC_SUPPLIES MR
, MSC_TRADING_PARTNERS MTP
, MFG_LOOKUPS ML
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, MFG_LOOKUPS ML4
, MSC_PLANS MP
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 MR.SR_INSTANCE_ID = MED.SR_INSTANCE_ID
AND MR.TRANSACTION_ID = MED.NUMBER1
AND MR.PLAN_ID = MED.PLAN_ID
AND MSI.INVENTORY_ITEM_ID = MR.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MR.ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID = MR.SR_INSTANCE_ID
AND MSI.PLAN_ID = MR.PLAN_ID
AND MED.EXCEPTION_TYPE IN (36
, 53
, 58
, 60
, 63)
AND ML.LOOKUP_TYPE = 'MRP_EXCEPTION_CODE_TYPE'
AND ML.LOOKUP_CODE = MED.EXCEPTION_TYPE
AND MTP.PARTNER_TYPE =3
AND MTP.SR_INSTANCE_ID = MED.SR_INSTANCE_ID
AND MTP.SR_TP_ID = MED.ORGANIZATION_ID
AND ML2.LOOKUP_TYPE = 'BOM_RESOURCE_TYPE'
AND ML2.LOOKUP_CODE = MDR.RESOURCE_TYPE
AND ML3.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND ML3.LOOKUP_CODE = MR.ORDER_TYPE
AND ML4.LOOKUP_TYPE = 'MSC_ORIGINATION_CODE_TYPE'
AND ML4.LOOKUP_CODE = 1
AND MP.PLAN_ID = MED.PLAN_ID UNION ALL SELECT /*+ FIRST_ROWS */ MED.EXCEPTION_DETAIL_ID
, MED.PLAN_ID
, MED.ORGANIZATION_ID
, MED.SR_INSTANCE_ID
, MR.INVENTORY_ITEM_ID
, MED.EXCEPTION_TYPE
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
, MTP.ORGANIZATION_CODE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, MSI.ITEM_NAME
, MSI.DESCRIPTION
, MSI.BUYER_NAME
, MSI.PLANNER_CODE
, ML.MEANING
, NVL(MR.PROJECT_ID
, 0)
, NVL(MR.TASK_ID
, 0)
, NVL(MR.PLANNING_GROUP
, '0')
, MSC_GET_NAME.PROJECT(MR.PROJECT_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_ID)
, MSC_GET_NAME.TASK(MR.TASK_ID
, MR.PROJECT_ID
, MR.ORGANIZATION_ID
, MR.PLAN_ID
, MR.SR_INSTANCE_ID)
, MR.PLANNING_GROUP
, NULL
, NULL
, MR.NEW_SHIP_DATE
, MED.DATE1
, MED.DATE2
, TO_NUMBER(NULL)
, MED.NUMBER3
, NULL
, NVL(MR.ORDER_NUMBER
, TO_CHAR(MR.TRANSACTION_ID))
, NULL
, NULL
, NULL
, NULL
, MR.TRANSACTION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, MED.QUANTITY
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, MED.NUMBER2
, MED.NUMBER4
, MSC_GET_NAME.ORG_CODE(MED.NUMBER2
, MED.NUMBER4)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_DATE(NULL)
, ML2.MEANING
, MR.ORDER_TYPE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(MED.EXCEPTION_TYPE
, 55
, MSI.UNIT_WEIGHT * MR.NEW_ORDER_QUANTITY
, 56
, MSI.UNIT_VOLUME*MR.NEW_ORDER_QUANTITY
, TO_NUMBER(NULL))
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 1
, ML3.MEANING
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, MR.NEW_DOCK_DATE
, DECODE(MED.EXCEPTION_TYPE
, 55
, MSI.UNIT_WEIGHT * MR.NEW_ORDER_QUANTITY - MED.QUANTITY
, 56
, MSI.UNIT_VOLUME*MR.NEW_ORDER_QUANTITY - MED.QUANTITY
, TO_NUMBER(NULL))
, TO_NUMBER(NULL)
, MR.SHIP_METHOD
FROM MSC_EXCEPTION_DETAILS MED
, MSC_INTERORG_SHIP_METHODS MSIM
, MSC_SYSTEM_ITEMS MSI
, MSC_SUPPLIES MR
, MSC_TRADING_PARTNERS MTP
, MFG_LOOKUPS ML
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
WHERE MSIM.TRANSACTION_ID = MED.DEPARTMENT_ID
AND MSIM.PLAN_ID = MED.PLAN_ID
AND MR.SR_INSTANCE_ID = MED.SR_INSTANCE_ID
AND MR.TRANSACTION_ID = MED.NUMBER1
AND MR.PLAN_ID = MED.PLAN_ID
AND MSI.INVENTORY_ITEM_ID = MR.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MR.ORGANIZATION_ID
AND MSI.SR_INSTANCE_ID = MR.SR_INSTANCE_ID
AND MSI.PLAN_ID = MR.PLAN_ID
AND MED.EXCEPTION_TYPE IN (55
, 56)
AND ML.LOOKUP_TYPE = 'MRP_EXCEPTION_CODE_TYPE'
AND ML.LOOKUP_CODE = MED.EXCEPTION_TYPE
AND MTP.PARTNER_TYPE = 3
AND MTP.SR_INSTANCE_ID = MED.SR_INSTANCE_ID
AND MTP.SR_TP_ID = MED.ORGANIZATION_ID
AND ML2.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND ML2.LOOKUP_CODE = MR.ORDER_TYPE
AND ML3.LOOKUP_TYPE = 'MSC_ORIGINATION_CODE_TYPE'
AND ML3.LOOKUP_CODE = 1