Trading Partner Cross Referenced Inventory Items.sql

SELECT
	PartyPEO.PARTY_NAME
  , PartyPEO.Party_id
  , CustomerItemPEO.TP_ITEM_ID
  , CustomerItemPEO.TRADING_PARTNER_ID
  , CustomerItemPEO.TP_TYPE
  , CustomerItemPEO.TP_ITEM_NUMBER
  , esib.item_number
  , CustomerItemXrefPEO.INVENTORY_ITEM_ID
  , CustomerItemXrefPEO.MASTER_ORGANIZATION_ID
  , esib.ORGANIZATION_ID
  , PartyUsageAssignmentPEO.STATUS_FLAG
  , CustomerItemXrefPEO.DESCRIPTION
  , CustomerItemXrefPEO.START_DATE_ACTIVE
  , CustomerItemXrefPEO.END_DATE_ACTIVE
  , CustomerItemXrefPEO.INACTIVE_FLAG
FROM
	fusion.EGP_CUSTOMER_ITEM_XREFS   CustomerItemXrefPEO
  , fusion.EGP_TRADING_PARTNER_ITEMS CustomerItemPEO
  , fusion.HZ_PARTIES                PartyPEO
  , fusion.HZ_PARTY_USG_ASSIGNMENTS  PartyUsageAssignmentPEO
  , fusion.egp_system_items_b        esib
WHERE
	PartyPEO.PARTY_ID                            = PartyUsageAssignmentPEO.PARTY_ID
	AND PartyUsageAssignmentPEO.PARTY_USAGE_CODE = 'CUSTOMER'
	AND PartyUsageAssignmentPEO.STATUS_FLAG      = 'A'
	AND CustomerItemXrefPEO.CUSTOMER_ITEM_ID     = CustomerItemPEO.TP_ITEM_ID
	AND CustomerItemPEO.TRADING_PARTNER_ID       = PartyPEO.PARTY_ID
	AND CustomerItemXrefPEO.INVENTORY_ITEM_ID    = esib.INVENTORY_ITEM_ID
	AND upper(PartyPEO.PARTY_NAME)            LIKE upper('&CUSTOMER_ACCOUNT_NAME%')
	--        AND Upper(ESIB.ITEM_NUMBER) LIKE upper('&ITEM_NUMBER%')