VIEW: APPS.MST_CARR_COMMIT_UNDERUTIL_V

Source

View Text - Preformatted

SELECT med.plan_id , med.exception_type , med.exception_detail_id , med.carrier_id , med.mode_of_transport , wcs.service_level , wlk1.meaning , wc.freight_code , wlk2.meaning , fl.lane_number , decode ( mst_wb_util.get_rule_type ( med.number1 ) , 'SPEND' , to_char ( med.number3, fnd_currency.get_format_mask ( fsl.uom_code, 67 ) ) , to_char( round ( med.number3 ), mst_wb_util.get_format_string ( 'NUMBER' ) ) ) , decode ( mst_wb_util.get_rule_type ( med.number1 ) , 'SPEND' , to_char ( med.number2, fnd_currency.get_format_mask ( fsl.uom_code, 67 ) ) , to_char( round ( med.number2 ), mst_wb_util.get_format_string ( 'NUMBER' ) ) ) , decode ( mst_wb_util.get_rule_type ( med.number1 ) , 'SPEND' , to_char ( ( med.number3 - med.number2 ) , fnd_currency.get_format_mask ( fsl.uom_code, 67 ) ) , to_char( round ( med.number3 ) - round ( med.number2 ), mst_wb_util.get_format_string ( 'NUMBER' ) ) ) , fsl.uom_code , fsl.frequency , med.date1 , med.date2 , to_char(null) , to_char(null) , ml.meaning , substr ( mst_wb_util.get_threshold_value ( med.exception_type ), 1, 20 ) , substr ( mst_wb_util.get_workflow_status ( med.plan_id, med.exception_detail_id ), 1, 20 ) from mst_exception_details med , wsh_lookups wlk1 , wsh_carriers wc , wsh_lookups wlk2 , wsh_carrier_services wcs , fte_lanes fl , fte_sel_rules fsl , mfg_lookups ml WHERE wlk1.lookup_type = 'WSH_MODE_OF_TRANSPORT' and med.mode_of_transport = wlk1.lookup_code and med.carrier_id = wc.carrier_id and med.carrier_service_id = wcs.carrier_service_id (+) and wlk2.lookup_type (+) = 'WSH_SERVICE_LEVELS' and wcs.service_level = wlk2.lookup_code (+) and med.lane_id = fl.lane_id and med.number1 = fsl.rule_id and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.status = ml.lookup_code union all select med.plan_id , med.exception_type , med.exception_detail_id , med.carrier_id , med.mode_of_transport , wcs.service_level , wlk1.meaning , wc.freight_code , wlk2.meaning , flg.name , decode ( mst_wb_util.get_rule_type ( med.number1 ) , 'SPEND' , to_char ( med.number3, fnd_currency.get_format_mask ( fsl.uom_code, 67 ) ) , to_char( round ( med.number3 ), mst_wb_util.get_format_string ( 'NUMBER' ) ) ) , decode ( mst_wb_util.get_rule_type ( med.number1 ) , 'SPEND' , to_char ( med.number2, fnd_currency.get_format_mask ( fsl.uom_code, 67 ) ) , to_char( round ( med.number2 ), mst_wb_util.get_format_string ( 'NUMBER' ) ) ) , decode ( mst_wb_util.get_rule_type ( med.number1 ) , 'SPEND' , to_char ( ( med.number3 - med.number2 ) , fnd_currency.get_format_mask ( fsl.uom_code, 67 ) ) , to_char( round ( med.number3 ) - round ( med.number2 ), mst_wb_util.get_format_string ( 'NUMBER' ) ) ) , fsl.uom_code , fsl.frequency , med.date1 , med.date2 , to_char(null) , to_char(null) , ml.meaning , substr ( mst_wb_util.get_threshold_value ( med.exception_type ), 1, 20 ) , substr ( mst_wb_util.get_workflow_status ( med.plan_id, med.exception_detail_id ), 1, 20 ) from mst_exception_details med , wsh_lookups wlk1 , wsh_carriers wc , wsh_lookups wlk2 , wsh_carrier_services wcs , fte_lane_groups flg , fte_sel_rules fsl , mfg_lookups ml where wlk1.lookup_type = 'WSH_MODE_OF_TRANSPORT' and med.mode_of_transport = wlk1.lookup_code and med.carrier_id = wc.carrier_id and med.carrier_service_id = wcs.carrier_service_id (+) and wlk2.lookup_type (+) = 'WSH_SERVICE_LEVELS' and wcs.service_level = wlk2.lookup_code (+) and med.lane_set_id = flg.lane_group_id and med.number1 = fsl.rule_id and ml.lookup_type = 'MST_EXCEPTION_STATUS' and med.status = ml.lookup_code

View Text - HTML Formatted

SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.CARRIER_ID
, MED.MODE_OF_TRANSPORT
, WCS.SERVICE_LEVEL
, WLK1.MEANING
, WC.FREIGHT_CODE
, WLK2.MEANING
, FL.LANE_NUMBER
, DECODE ( MST_WB_UTIL.GET_RULE_TYPE ( MED.NUMBER1 )
, 'SPEND'
, TO_CHAR ( MED.NUMBER3
, FND_CURRENCY.GET_FORMAT_MASK ( FSL.UOM_CODE
, 67 ) )
, TO_CHAR( ROUND ( MED.NUMBER3 )
, MST_WB_UTIL.GET_FORMAT_STRING ( 'NUMBER' ) ) )
, DECODE ( MST_WB_UTIL.GET_RULE_TYPE ( MED.NUMBER1 )
, 'SPEND'
, TO_CHAR ( MED.NUMBER2
, FND_CURRENCY.GET_FORMAT_MASK ( FSL.UOM_CODE
, 67 ) )
, TO_CHAR( ROUND ( MED.NUMBER2 )
, MST_WB_UTIL.GET_FORMAT_STRING ( 'NUMBER' ) ) )
, DECODE ( MST_WB_UTIL.GET_RULE_TYPE ( MED.NUMBER1 )
, 'SPEND'
, TO_CHAR ( ( MED.NUMBER3 - MED.NUMBER2 )
, FND_CURRENCY.GET_FORMAT_MASK ( FSL.UOM_CODE
, 67 ) )
, TO_CHAR( ROUND ( MED.NUMBER3 ) - ROUND ( MED.NUMBER2 )
, MST_WB_UTIL.GET_FORMAT_STRING ( 'NUMBER' ) ) )
, FSL.UOM_CODE
, FSL.FREQUENCY
, MED.DATE1
, MED.DATE2
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, ML.MEANING
, SUBSTR ( MST_WB_UTIL.GET_THRESHOLD_VALUE ( MED.EXCEPTION_TYPE )
, 1
, 20 )
, SUBSTR ( MST_WB_UTIL.GET_WORKFLOW_STATUS ( MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID )
, 1
, 20 )
FROM MST_EXCEPTION_DETAILS MED
, WSH_LOOKUPS WLK1
, WSH_CARRIERS WC
, WSH_LOOKUPS WLK2
, WSH_CARRIER_SERVICES WCS
, FTE_LANES FL
, FTE_SEL_RULES FSL
, MFG_LOOKUPS ML
WHERE WLK1.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND MED.MODE_OF_TRANSPORT = WLK1.LOOKUP_CODE
AND MED.CARRIER_ID = WC.CARRIER_ID
AND MED.CARRIER_SERVICE_ID = WCS.CARRIER_SERVICE_ID (+)
AND WLK2.LOOKUP_TYPE (+) = 'WSH_SERVICE_LEVELS'
AND WCS.SERVICE_LEVEL = WLK2.LOOKUP_CODE (+)
AND MED.LANE_ID = FL.LANE_ID
AND MED.NUMBER1 = FSL.RULE_ID
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.STATUS = ML.LOOKUP_CODE UNION ALL SELECT MED.PLAN_ID
, MED.EXCEPTION_TYPE
, MED.EXCEPTION_DETAIL_ID
, MED.CARRIER_ID
, MED.MODE_OF_TRANSPORT
, WCS.SERVICE_LEVEL
, WLK1.MEANING
, WC.FREIGHT_CODE
, WLK2.MEANING
, FLG.NAME
, DECODE ( MST_WB_UTIL.GET_RULE_TYPE ( MED.NUMBER1 )
, 'SPEND'
, TO_CHAR ( MED.NUMBER3
, FND_CURRENCY.GET_FORMAT_MASK ( FSL.UOM_CODE
, 67 ) )
, TO_CHAR( ROUND ( MED.NUMBER3 )
, MST_WB_UTIL.GET_FORMAT_STRING ( 'NUMBER' ) ) )
, DECODE ( MST_WB_UTIL.GET_RULE_TYPE ( MED.NUMBER1 )
, 'SPEND'
, TO_CHAR ( MED.NUMBER2
, FND_CURRENCY.GET_FORMAT_MASK ( FSL.UOM_CODE
, 67 ) )
, TO_CHAR( ROUND ( MED.NUMBER2 )
, MST_WB_UTIL.GET_FORMAT_STRING ( 'NUMBER' ) ) )
, DECODE ( MST_WB_UTIL.GET_RULE_TYPE ( MED.NUMBER1 )
, 'SPEND'
, TO_CHAR ( ( MED.NUMBER3 - MED.NUMBER2 )
, FND_CURRENCY.GET_FORMAT_MASK ( FSL.UOM_CODE
, 67 ) )
, TO_CHAR( ROUND ( MED.NUMBER3 ) - ROUND ( MED.NUMBER2 )
, MST_WB_UTIL.GET_FORMAT_STRING ( 'NUMBER' ) ) )
, FSL.UOM_CODE
, FSL.FREQUENCY
, MED.DATE1
, MED.DATE2
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, ML.MEANING
, SUBSTR ( MST_WB_UTIL.GET_THRESHOLD_VALUE ( MED.EXCEPTION_TYPE )
, 1
, 20 )
, SUBSTR ( MST_WB_UTIL.GET_WORKFLOW_STATUS ( MED.PLAN_ID
, MED.EXCEPTION_DETAIL_ID )
, 1
, 20 )
FROM MST_EXCEPTION_DETAILS MED
, WSH_LOOKUPS WLK1
, WSH_CARRIERS WC
, WSH_LOOKUPS WLK2
, WSH_CARRIER_SERVICES WCS
, FTE_LANE_GROUPS FLG
, FTE_SEL_RULES FSL
, MFG_LOOKUPS ML
WHERE WLK1.LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND MED.MODE_OF_TRANSPORT = WLK1.LOOKUP_CODE
AND MED.CARRIER_ID = WC.CARRIER_ID
AND MED.CARRIER_SERVICE_ID = WCS.CARRIER_SERVICE_ID (+)
AND WLK2.LOOKUP_TYPE (+) = 'WSH_SERVICE_LEVELS'
AND WCS.SERVICE_LEVEL = WLK2.LOOKUP_CODE (+)
AND MED.LANE_SET_ID = FLG.LANE_GROUP_ID
AND MED.NUMBER1 = FSL.RULE_ID
AND ML.LOOKUP_TYPE = 'MST_EXCEPTION_STATUS'
AND MED.STATUS = ML.LOOKUP_CODE