VIEW: APPS.MSC_X_EXCEPTION_SEARCH_V

Source

View Text - Preformatted

SELECT ex.PLAN_ID, ex.SR_INSTANCE_ID, ex.EXCEPTION_GROUP, ex.EXCEPTION_GROUP_NAME, ex.COMPANY_ID, ex.COMPANY_NAME, ex.COMPANY_SITE_ID, ex.SUPPLIER_ID, ex.SUPPLIER_SITE_ID, ex.CUSTOMER_ID, ex.CUSTOMER_SITE_ID, ex.TRANSACTION_ID1, ex.TRANSACTION_ID2, ex.TRANSACTION_ID3, ex.VERSION, ex.CREATED_BY, ex.CREATION_DATE, ex.LAST_UPDATED_BY, ex.LAST_UPDATE_DATE, ex.LAST_UPDATE_LOGIN, ex.TRADING_PARTNER_ITEM_NAME, ex.company_site_name, ex.customer_name, ex.customer_site_name, ex.supplier_name, ex.supplier_site_name, ex.item_name, ex.item_description, ex.customer_item_name, ex.supplier_item_name, ex.date1, ex.date2, ex.date3, ex.date4, ex.date5, ex.date6, ex.date7, ex.order_creation_date1, ex.order_creation_date2, round(ex.item_min_qty,6) item_min_qty, round(ex.item_max_qty,6) item_max_qty, round(ex.number1,6) number1, round(ex.number2,6) number2, round(ex.number3,2) number3, round(ex.number4,6) number4, round(ex.number5,6) number5, round(ex.number6,6) number6, round(ex.number7,6) number7, round(ex.number8,6) number8, round(ex.number9,6) number9, round(ex.number10,6) number10, round(ex.threshold,2) threshold, round(ex.lead_time,2) lead_time, ex.order_number, ex.line_number, ex.release_number, ex.end_order_number, ex.end_order_line_number, ex.end_order_rel_number, round(abs(ex.date2-ex.date1),2) days_difference, round(abs(sysdate-ex.date1),2) days_past_due, round(abs((ex.order_creation_date1-ex.date1)+nvl(ex.lead_time,0)),2) compression_days,round(abs(ex.number2-ex.number1),6) quantity_difference, round(abs(ex.number1+ex.number2-ex.item_min_qty),6) vmi_shortage, round(abs(ex.number1+ex.number2-ex.item_max_qty),6) vmi_excess, ex.exception_type, ex.exception_detail_id, ex.inventory_item_id, ex.replenishment_method, ex.exception_basis, ex.exception_type_name, ex.OWNING_COMPANY_ID, msi.planner_code, msi.buyer_name FROM MSC_X_EXCEPTION_DETAILS EX, MSC_SYSTEM_ITEMS MSI, MSC_COMPANY_SITES S, MSC_TRADING_PARTNER_MAPS MAPS, MSC_TRADING_PARTNERS MTPS WHERE s.company_site_id = decode(nvl(ex.customer_id,ex.company_id),1,nvl(ex.customer_site_id,ex.company_site_id),nvl(ex.supplier_site_id,ex.company_site_id)) AND maps.map_type = 2 AND maps.company_key = s.company_site_id AND mtps.partner_id = maps.tp_key AND msi.organization_id = mtps.sr_tp_id AND msi.sr_instance_id = mtps.sr_instance_id AND msi.plan_id = -1 AND msi.inventory_item_id = ex.inventory_item_id

View Text - HTML Formatted

SELECT EX.PLAN_ID
, EX.SR_INSTANCE_ID
, EX.EXCEPTION_GROUP
, EX.EXCEPTION_GROUP_NAME
, EX.COMPANY_ID
, EX.COMPANY_NAME
, EX.COMPANY_SITE_ID
, EX.SUPPLIER_ID
, EX.SUPPLIER_SITE_ID
, EX.CUSTOMER_ID
, EX.CUSTOMER_SITE_ID
, EX.TRANSACTION_ID1
, EX.TRANSACTION_ID2
, EX.TRANSACTION_ID3
, EX.VERSION
, EX.CREATED_BY
, EX.CREATION_DATE
, EX.LAST_UPDATED_BY
, EX.LAST_UPDATE_DATE
, EX.LAST_UPDATE_LOGIN
, EX.TRADING_PARTNER_ITEM_NAME
, EX.COMPANY_SITE_NAME
, EX.CUSTOMER_NAME
, EX.CUSTOMER_SITE_NAME
, EX.SUPPLIER_NAME
, EX.SUPPLIER_SITE_NAME
, EX.ITEM_NAME
, EX.ITEM_DESCRIPTION
, EX.CUSTOMER_ITEM_NAME
, EX.SUPPLIER_ITEM_NAME
, EX.DATE1
, EX.DATE2
, EX.DATE3
, EX.DATE4
, EX.DATE5
, EX.DATE6
, EX.DATE7
, EX.ORDER_CREATION_DATE1
, EX.ORDER_CREATION_DATE2
, ROUND(EX.ITEM_MIN_QTY
, 6) ITEM_MIN_QTY
, ROUND(EX.ITEM_MAX_QTY
, 6) ITEM_MAX_QTY
, ROUND(EX.NUMBER1
, 6) NUMBER1
, ROUND(EX.NUMBER2
, 6) NUMBER2
, ROUND(EX.NUMBER3
, 2) NUMBER3
, ROUND(EX.NUMBER4
, 6) NUMBER4
, ROUND(EX.NUMBER5
, 6) NUMBER5
, ROUND(EX.NUMBER6
, 6) NUMBER6
, ROUND(EX.NUMBER7
, 6) NUMBER7
, ROUND(EX.NUMBER8
, 6) NUMBER8
, ROUND(EX.NUMBER9
, 6) NUMBER9
, ROUND(EX.NUMBER10
, 6) NUMBER10
, ROUND(EX.THRESHOLD
, 2) THRESHOLD
, ROUND(EX.LEAD_TIME
, 2) LEAD_TIME
, EX.ORDER_NUMBER
, EX.LINE_NUMBER
, EX.RELEASE_NUMBER
, EX.END_ORDER_NUMBER
, EX.END_ORDER_LINE_NUMBER
, EX.END_ORDER_REL_NUMBER
, ROUND(ABS(EX.DATE2-EX.DATE1)
, 2) DAYS_DIFFERENCE
, ROUND(ABS(SYSDATE-EX.DATE1)
, 2) DAYS_PAST_DUE
, ROUND(ABS((EX.ORDER_CREATION_DATE1-EX.DATE1)+NVL(EX.LEAD_TIME
, 0))
, 2) COMPRESSION_DAYS
, ROUND(ABS(EX.NUMBER2-EX.NUMBER1)
, 6) QUANTITY_DIFFERENCE
, ROUND(ABS(EX.NUMBER1+EX.NUMBER2-EX.ITEM_MIN_QTY)
, 6) VMI_SHORTAGE
, ROUND(ABS(EX.NUMBER1+EX.NUMBER2-EX.ITEM_MAX_QTY)
, 6) VMI_EXCESS
, EX.EXCEPTION_TYPE
, EX.EXCEPTION_DETAIL_ID
, EX.INVENTORY_ITEM_ID
, EX.REPLENISHMENT_METHOD
, EX.EXCEPTION_BASIS
, EX.EXCEPTION_TYPE_NAME
, EX.OWNING_COMPANY_ID
, MSI.PLANNER_CODE
, MSI.BUYER_NAME
FROM MSC_X_EXCEPTION_DETAILS EX
, MSC_SYSTEM_ITEMS MSI
, MSC_COMPANY_SITES S
, MSC_TRADING_PARTNER_MAPS MAPS
, MSC_TRADING_PARTNERS MTPS
WHERE S.COMPANY_SITE_ID = DECODE(NVL(EX.CUSTOMER_ID
, EX.COMPANY_ID)
, 1
, NVL(EX.CUSTOMER_SITE_ID
, EX.COMPANY_SITE_ID)
, NVL(EX.SUPPLIER_SITE_ID
, EX.COMPANY_SITE_ID))
AND MAPS.MAP_TYPE = 2
AND MAPS.COMPANY_KEY = S.COMPANY_SITE_ID
AND MTPS.PARTNER_ID = MAPS.TP_KEY
AND MSI.ORGANIZATION_ID = MTPS.SR_TP_ID
AND MSI.SR_INSTANCE_ID = MTPS.SR_INSTANCE_ID
AND MSI.PLAN_ID = -1
AND MSI.INVENTORY_ITEM_ID = EX.INVENTORY_ITEM_ID