VIEW: APPS.MST_SUPPLIER_DETAILS_V

Source

View Text - Preformatted

SELECT MP.PLAN_ID, POV.VENDOR_id SUPPLIER_ID, POV.VENDOR_NAME SUPPLIER_NAME, MP.COMPILE_DESIGNATOR , MP.DESCRIPTION , SUBSTR(MST_AGG_PKG.GET_TOTAL_TRIP_COUNT_PARTNER (MP.PLAN_ID, POV.VENDOR_id, 1),1, 200) LOAD_COUNT_STR, MST_AGG_PKG.GET_TOTAL_COST_SUPP (MP.PLAN_ID, POV.VENDOR_id) TOTAL_COST, MST_AGG_PKG.get_total_weight_supp (MP.PLAN_ID, POV.VENDOR_id ) TOTAL_WEIGHT, MST_AGG_PKG.get_total_cube_supp (MP.PLAN_ID, POV.VENDOR_id ) TOTAL_CUBE, MST_AGG_PKG.get_total_pallets_supp (MP.PLAN_ID, POV.VENDOR_id ) TOTAL_PALLETS, MST_AGG_PKG.get_total_pieces_supp (MP.PLAN_ID, POV.VENDOR_id ) TOTAL_PIECES, MST_AGG_PKG.get_total_orders_supp (MP.PLAN_ID, POV.VENDOR_id ) TOTAL_ORDERS, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM FROM MST_PLANS MP, PO_VENDORS POV, WSH_LOCATION_OWNERS WLO, WSH_LOCATIONS WL, hz_relationships hzr, hz_parties hz WHERE WL.WSH_LOCATION_ID = WLO.WSH_LOCATION_ID AND WLO.OWNER_TYPE = 4 AND WLO.OWNER_PARTY_ID = hz.party_id AND pov.vendor_id = hzr.subject_id AND hzr.object_id = hz.party_id AND hzr.relationship_type = 'POS_VENDOR_PARTY' AND pov.vendor_id IN (SELECT mdd.supplier_id FROM MST_DELIVERY_DETAILS MDD WHERE MDD.PLAN_ID = MP.PLAN_ID)

View Text - HTML Formatted

SELECT MP.PLAN_ID
, POV.VENDOR_ID SUPPLIER_ID
, POV.VENDOR_NAME SUPPLIER_NAME
, MP.COMPILE_DESIGNATOR
, MP.DESCRIPTION
, SUBSTR(MST_AGG_PKG.GET_TOTAL_TRIP_COUNT_PARTNER (MP.PLAN_ID
, POV.VENDOR_ID
, 1)
, 1
, 200) LOAD_COUNT_STR
, MST_AGG_PKG.GET_TOTAL_COST_SUPP (MP.PLAN_ID
, POV.VENDOR_ID) TOTAL_COST
, MST_AGG_PKG.GET_TOTAL_WEIGHT_SUPP (MP.PLAN_ID
, POV.VENDOR_ID ) TOTAL_WEIGHT
, MST_AGG_PKG.GET_TOTAL_CUBE_SUPP (MP.PLAN_ID
, POV.VENDOR_ID ) TOTAL_CUBE
, MST_AGG_PKG.GET_TOTAL_PALLETS_SUPP (MP.PLAN_ID
, POV.VENDOR_ID ) TOTAL_PALLETS
, MST_AGG_PKG.GET_TOTAL_PIECES_SUPP (MP.PLAN_ID
, POV.VENDOR_ID ) TOTAL_PIECES
, MST_AGG_PKG.GET_TOTAL_ORDERS_SUPP (MP.PLAN_ID
, POV.VENDOR_ID ) TOTAL_ORDERS
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_PLANS MP
, PO_VENDORS POV
, WSH_LOCATION_OWNERS WLO
, WSH_LOCATIONS WL
, HZ_RELATIONSHIPS HZR
, HZ_PARTIES HZ
WHERE WL.WSH_LOCATION_ID = WLO.WSH_LOCATION_ID
AND WLO.OWNER_TYPE = 4
AND WLO.OWNER_PARTY_ID = HZ.PARTY_ID
AND POV.VENDOR_ID = HZR.SUBJECT_ID
AND HZR.OBJECT_ID = HZ.PARTY_ID
AND HZR.RELATIONSHIP_TYPE = 'POS_VENDOR_PARTY'
AND POV.VENDOR_ID IN (SELECT MDD.SUPPLIER_ID
FROM MST_DELIVERY_DETAILS MDD
WHERE MDD.PLAN_ID = MP.PLAN_ID)