VIEW: APPS.MST_CUSTOMER_DETAILS_V

Source

View Text - Preformatted

SELECT MP.PLAN_ID, HZC.cust_account_id CUSTOMER_ID, HZP.PARTY_NAME CUSTOMER_NAME, MP.COMPILE_DESIGNATOR, MP.DESCRIPTION , SUBSTR (MST_AGG_PKG.GET_TOTAL_TRIP_COUNT_PARTNER (MP.PLAN_ID, HZC.cust_account_id, 0),1, 200) LOAD_COUNT_STR, MST_AGG_PKG.GET_TOTAL_COST_CUST (MP.PLAN_ID, HZC.cust_account_id ) TOTAL_COST, MST_AGG_PKG.get_total_weight_cust (MP.PLAN_ID, HZC.cust_account_id) TOTAL_WEIGHT, MST_AGG_PKG.get_total_cube_cust (MP.PLAN_ID, HZC.cust_account_id) TOTAL_CUBE, MST_AGG_PKG.get_total_PALLETS_cust (MP.PLAN_ID, HZC.cust_account_id ) TOTAL_PALLETS, MST_AGG_PKG.get_total_Pieces_cust (MP.PLAN_ID, HZC.cust_account_id ) TOTAL_PIECES, MST_AGG_PKG.get_total_Orders_cust (MP.PLAN_ID, HZC.cust_account_id ) TOTAL_ORDERS, MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM FROM MST_PLANS MP, HZ_PARTIES HZP, WSH_LOCATION_OWNERS WLO, HZ_CUST_ACCOUNTS HZC WHERE WLO.OWNER_TYPE = 2 AND wlo.owner_party_id = HZP.PARTY_ID AND HZP.PARTY_ID = HZC.PARTY_ID AND HZC.cust_account_id IN (SELECT MD.CUSTOMER_ID FROM MST_DELIVERIES MD WHERE MD.PLAN_ID = MP.PLAN_ID) GROUP BY mp.PLAN_ID, HZC.cust_account_id , HZP.PARTY_NAME , MP.COMPILE_DESIGNATOR, MP.DESCRIPTION , MP.CURRENCY_UOM, MP.WEIGHT_UOM, MP.VOLUME_UOM

View Text - HTML Formatted

SELECT MP.PLAN_ID
, HZC.CUST_ACCOUNT_ID CUSTOMER_ID
, HZP.PARTY_NAME CUSTOMER_NAME
, MP.COMPILE_DESIGNATOR
, MP.DESCRIPTION
, SUBSTR (MST_AGG_PKG.GET_TOTAL_TRIP_COUNT_PARTNER (MP.PLAN_ID
, HZC.CUST_ACCOUNT_ID
, 0)
, 1
, 200) LOAD_COUNT_STR
, MST_AGG_PKG.GET_TOTAL_COST_CUST (MP.PLAN_ID
, HZC.CUST_ACCOUNT_ID ) TOTAL_COST
, MST_AGG_PKG.GET_TOTAL_WEIGHT_CUST (MP.PLAN_ID
, HZC.CUST_ACCOUNT_ID) TOTAL_WEIGHT
, MST_AGG_PKG.GET_TOTAL_CUBE_CUST (MP.PLAN_ID
, HZC.CUST_ACCOUNT_ID) TOTAL_CUBE
, MST_AGG_PKG.GET_TOTAL_PALLETS_CUST (MP.PLAN_ID
, HZC.CUST_ACCOUNT_ID ) TOTAL_PALLETS
, MST_AGG_PKG.GET_TOTAL_PIECES_CUST (MP.PLAN_ID
, HZC.CUST_ACCOUNT_ID ) TOTAL_PIECES
, MST_AGG_PKG.GET_TOTAL_ORDERS_CUST (MP.PLAN_ID
, HZC.CUST_ACCOUNT_ID ) TOTAL_ORDERS
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM
FROM MST_PLANS MP
, HZ_PARTIES HZP
, WSH_LOCATION_OWNERS WLO
, HZ_CUST_ACCOUNTS HZC
WHERE WLO.OWNER_TYPE = 2
AND WLO.OWNER_PARTY_ID = HZP.PARTY_ID
AND HZP.PARTY_ID = HZC.PARTY_ID
AND HZC.CUST_ACCOUNT_ID IN (SELECT MD.CUSTOMER_ID
FROM MST_DELIVERIES MD
WHERE MD.PLAN_ID = MP.PLAN_ID) GROUP BY MP.PLAN_ID
, HZC.CUST_ACCOUNT_ID
, HZP.PARTY_NAME
, MP.COMPILE_DESIGNATOR
, MP.DESCRIPTION
, MP.CURRENCY_UOM
, MP.WEIGHT_UOM
, MP.VOLUME_UOM