BizTalk Sql Query to Lookup Party/Profile based on an identifier

You might open a file like this:


ISA*00*          *00*          *ZZ*123000013      *ZZ*ABC3001        *160719*1600*U*00401*201160030*0*P*;~

and then want to know what is company with ID=123000013? I don’t know of any any way in the BizTalk Admin console to do a reverse lookup like this.

<pre>

select 
       P.PartnerId as 'ParterID', 
       P.Name as 'PartyName', 
       BP.Name as 'ProfileName', 
     BP.ProfileID,
     BI.Qualifier, 
     BI.Description, 
     BI.Value, 
       P2.PartnerId as 'PartnerID', 
     P2.Name as 'OtherPartyName', 
     BP2.Name as 'OtherProfileName' ,
     BP2.ProfileID,
     BI2.Qualifier, 
     BI2.Description, 
     BI2.Value, 
     * 
from tpm.BusinessIdentity  BI 
inner join tpm.BusinessProfile BP   on BI.ProfileId = BP.ProfileId
inner join tpm.Partner P            on P.PartnerID = BP.PartnerID 
inner join tpm.Partnership ps       on P.PartnerID = ps.PartnerAId 
inner join tpm.Partner P2           on P2.PartnerID = ps.PartnerBId
inner join tpm.BusinessProfile BP2  on P2.PartnerID = BP2.PartnerID 
inner join tpm.BusinessIdentity BI2 on BI2.ProfileId = BP2.ProfileId
where BI.Value = '123000013' 
-- and BI2.Value = 'xxxxxxx'
order by P.Name, BP.Name 
</pre>

Leave a Reply

Recent Posts

Archives

Categories

Meta

All Rights Reserved Theme by 404 THEME.