Originally posted by: SystemAdmin

want to create a report from tmgr which consists info about IB 834's (401095A1) TLinks configured in partner manager.
It should have
TP Name,
ISA*05 sender i/c qual
ISA*06 sender i/c id
ISA*07 rcvr i/c qual
ISA*08 rcvr i/c id
GS*02 appl sender code
GS*03 appl recvr code

Custom reports dont give all of these in one report , but in separate reports. Any suggestions welcome.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Would a SQL statement returning those help? Note that there are many application partners to one trading partner, so a nicely formatted report would take some effort. You'll get the trading partner repeated for each application partner it has from the SQL statement.

If a SQL statement helps, please advise if:

1) this is limited to X12 only (I presume given the field names; but need to ask if EDIFACT/TRADACOMS are of interest)

2) you don't mind Internal and External partners returned together (or would you prefer two SQL statements?)
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Correction; missed the part that you only wanted IB trade links. The detailed questions (1 and 2) don't apply then!
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

ya I worked around with a sql in update.mdq & got the result. But would you mind sharing the sql ? Just wanted to compare the results that i am getting...
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

Here's the SQL. Note some limitations/changes:

1. I included all four names (From/To Trading and Application Partner Names); you were probably just interested in "FromPartner".

2. It does not include "Unknown" Trade Links; those have to be "unioned" together with this query. Reports X12 Inbound Only.

3. It made the query much smaller and faster if you manually converted your Transaction Set ID (your '834') into a Functional Group ID ('BE' in this case). I could re-work it to use the transaction set.

Usage:

Change the last two quoted values as needed (GS_VersionRelease and GS_FunctionalID). I added an 'X'
to your version (4010X095A1); remove that if you use custom versions.

If you'd like to modify the query to report all X12 Inbound 'known' links, remove the last two conditions and add them as returned values instead.

SELECT DISTINCT X12TradingPartners.PartnerName AS  FromPartner, X12TradingPartners.IchgQual AS ISA05, X12TradingPartners.IchgID AS ISA06, X12AppPartners.ApplicationName AS FromApPartner, X12AppPartners.GS_ApplicationID AS GS02, X12TRADPRTNERS_1.PartnerName AS ToPartner, X12TRADPRTNERS_1.IchgQual AS ISA07, X12TRADPRTNERS_1.IchgID AS  ISA08, X12AppPartners_1.ApplicationName AS ToApPartner, X12AppPartners.GS_ApplicationID AS GS03 FROM X12InboundGroups, X12IBTradeLink, X12AppPartners, X12TradingPartners, X12AppPartners  X12AppPartners_1, X12TradingPartners  X12TRADPRTNERS_1 WHERE X12InboundGroups.X12IBTradeLinkNo = X12IBTradeLink.X12IBTradeLinkNo AND X12IBTradeLink.FromExtAPNo = X12AppPartners.ApplicationNo AND X12AppPartners.PartnerNo = X12TradingPartners.PartnerNo AND X12IBTradeLink.ToIntAPNo = X12AppPartners_1.ApplicationNo  AND X12AppPartners_1.PartnerNo = X12TRADPRTNERS_1.PartnerNo   AND X12InboundGroups.TradeUnknownInd =
'T'   AND X12InboundGroups.GS_VersionRelease = 
'4010X095A1' AND X12InboundGroups.GS_FunctionalID = 
'BE'

Jim Hobler
IBM Software Engineer
The postings on this site are my own and do not necessarily represent the positions, strategies, or opinions of IBM.
SQL is provided 'as-is', and is not a part of the released Trading Manager product. The code has not been subject to regression testing and should be throughly tested before placing into production.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Thanks Jim.
I see a difference of few rows when i run that against my test d/b.
This is what i am using.

SELECT X12TP_0.PARTNERNAME AS Partner_Name,
X12TP_0.IchgQual AS sender_tp_qual,
X12TP_0.IchgID AS sender_tp_id,
X12AppPartners_0.GS_ApplicationID AS sender_app_id,
X12TP_1.IchgQual AS rcvr_tp_qual,
X12TP_1.IchgID AS rcvr_tp_id,
X12AppPartners_1.GS_ApplicationID AS rcvr_app_id,
X12AppPartners_2.GS_ApplicationID AS
ack_to_app_id,
X12InboundGroups.ValidateByInd
FROM X12TradingPartners X12TP_0,
X12IBTradeLink,
X12AppPartners X12AppPartners_0,
X12AppPartners X12AppPartners_1,
X12TradingPartners X12TP_1,
X12InboundGroups,
X12AppPartners X12AppPartners_2,
X12TradingPartners X12TP_2,
PostOffices WHERE X12IBTradeLink.FromExtAPNo = X12AppPartners_0.ApplicationNo
AND X12IBTradeLink.ToIntAPNo = X12AppPartners_1.ApplicationNo
AND X12AppPartners_0.PartnerNo = X12TP_0.PartnerNo
AND X12TP_1.PartnerNo = X12AppPartners_1.PartnerNo
AND X12IBTradeLink.X12IBTradeLinkNo = X12InboundGroups.X12IBTradeLinkNo
AND X12InboundGroups.AckToAPNo = X12AppPartners_2.ApplicationNo
AND X12AppPartners_2.PartnerNo = X12TP_2.PartnerNo
AND X12InboundGroups.ProdPutPONo = PostOffices.PONo
AND X12InboundGroups.GS_VersionRelease = '4010X095A1'
ORDER BY 1,4,5,6,2,3,7
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Also looks like its returning incorrect values for GS*03...
( basically the same values are repeating for GS*03 as GS*02)
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

Re same GS values: good catch; slip o' the fingers there; copied the same correlation name creating that line. Re differences in # of rows: I have a DISTINCT and not returning anything from InboundGroups; you are. Mine has just the partners; by you returning the field X12InboundGroups.ValidateByInd, you will get more rows. (Don't think I'm suggesting you add a DISTINCT to yours, btw... mine was just partners so it needed it).

Note too you didn't add the FuncID (BE), so that's another reason the row count may be different (if you have some inbound FA's for example)

Here's the corrected correlation name if you want just partners. If you need ValidateByInd, then yours seems ok, but will give duplicate partners. Thanks for the challenge and the catch!

SELECT DISTINCT X12TradingPartners.PartnerName AS  FromPartner, X12TradingPartners.IchgQual AS ISA05, X12TradingPartners.IchgID AS ISA06, X12AppPartners.ApplicationName AS FromApPartner, X12AppPartners.GS_ApplicationID AS GS02, X12TRADPRTNERS_1.PartnerName AS ToPartner, X12TRADPRTNERS_1.IchgQual AS ISA07, X12TRADPRTNERS_1.IchgID AS  ISA08, X12AppPartners_1.ApplicationName AS ToApPartner, X12AppPartners_1.GS_ApplicationID AS GS03 FROM X12InboundGroups, X12IBTradeLink, X12AppPartners, X12TradingPartners, X12AppPartners  X12AppPartners_1, X12TradingPartners  X12TRADPRTNERS_1 WHERE X12InboundGroups.X12IBTradeLinkNo = X12IBTradeLink.X12IBTradeLinkNo AND X12IBTradeLink.FromExtAPNo = X12AppPartners.ApplicationNo AND X12AppPartners.PartnerNo = X12TradingPartners.PartnerNo AND X12IBTradeLink.ToIntAPNo = X12AppPartners_1.ApplicationNo  AND X12AppPartners_1.PartnerNo = X12TRADPRTNERS_1.PartnerNo   AND X12InboundGroups.TradeUnknownInd =
'T'   AND X12InboundGroups.GS_VersionRelease = 
'4010X095A1' AND X12InboundGroups.GS_FunctionalID = 
'BE'

#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange