SELECT DISTINCT
hp.party_id,
hp.party_number,
hp.party_name,
hp.party_type,
etpi.tp_item_desc
FROM
hz_parties hp
JOIN
egp_trading_partner_items etpi
ON hp.party_id = etpi.trading_partner_id
WHERE
hp.party_type = 'ORGANIZATION'
Join with SQL below
SELECT
IOP.ORGANIZATION_CODE AS ORG_CODE,
ESIB.ITEM_NUMBER AS ITEM_NUM,
ESIT.DESCRIPTION AS ITEM_DESC,
ESIB.ATTRIBUTE4 AS NON_INV_FLG,
ESIB.ATTRIBUTE2 AS INV_MAJ,
ESIB.ATTRIBUTE3 AS INV_MIN,
ESIB.ATTRIBUTE7 AS HCPCS,
REL.CROSS_REFERENCE AS CHG_NUM,
DECODE(ESIB.ATTRIBUTE5, 'N', 2, 'Y', 1) AS CHRG_IND,
ESIB.ATTRIBUTE1 AS IMPL_FLG,
TO_CHAR(ESIB.LIST_PRICE_PER_UNIT, '$99,990.00') AS UNIT_COST
FROM
EGP_SYSTEM_ITEMS_B ESIB
JOIN
EGP_SYSTEM_ITEMS_TL ESIT
ON ESIB.ORGANIZATION_ID = ESIT.ORGANIZATION_ID
AND ESIB.INVENTORY_ITEM_ID = ESIT.INVENTORY_ITEM_ID
JOIN
INV_ORG_PARAMETERS IOP
ON ESIB.ORGANIZATION_ID = IOP.ORGANIZATION_ID
AND ESIB.MASTER_ORG_ID = IOP.MASTER_ORGANIZATION_ID
LEFT JOIN (
SELECT
CROSS_REFERENCE,
INVENTORY_ITEM_ID,
ORGANIZATION_ID
FROM (
SELECT
CROSS_REFERENCE,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ROW_NUMBER() OVER (
PARTITION BY INVENTORY_ITEM_ID, ORGANIZATION_ID
ORDER BY CROSS_REFERENCE
) AS RN
FROM
EGP_ITEM_RELATIONSHIPS_B
WHERE
SUB_TYPE = 'Charge Master'
)
WHERE RN = 1
) REL
ON REL.ORGANIZATION_ID = ESIB.ORGANIZATION_ID
AND REL.INVENTORY_ITEM_ID = ESIB.INVENTORY_ITEM_ID
WHERE
IOP.ORGANIZATION_CODE IN (:P_ORG)
AND ESIB.ENABLED_FLAG = 'Y'
AND ESIT.LANGUAGE = 'US'