Skip to Main Content

Live SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Best way to join two SQL codes to get the value on "Trading Partner Name and Trading Partner Items"

IAM AlexSep 30 2025

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'

Comments
Post Details
Added on Sep 30 2025
3 comments
30 views