WITH A AS
  (SELECT Cii.Instance_Status_Id Ib_Number,
    Cis.Name Ib_Status,
    Okhb.Contract_Number CONTRACTNO,
    Okhb.Contract_Number_Modifier Contractmodifier,
    Hp.Party_Number Contractpartyno,
    Hp.Party_Name Contractpartyname
  FROM csi.Csi_Item_Instances Cii,
    Csi.Csi_Instance_Statuses Cis,
    csi.csi_i_parties cip,
    Hz_Parties Hp,
    Okc.Okc_K_Items Oki,
    Okc.Okc_K_Lines_B Oklb,
    Okc.Okc_K_Headers_B Okhb,
    okc.Okc_K_Party_Roles_B Okprb
  WHERE Hp.Party_Id         =Cip.Party_Id
  AND Cip.Instance_Id       =Cii.Instance_Id
  AND Cii.Instance_Status_Id=Cis.Instance_Status_Id
  AND Cii.Instance_Id       =Oki.Object1_Id1
  AND Oki.Cle_Id            =Oklb.Id
  AND Oklb.Dnz_Chr_Id       =Okhb.Id
  AND Okhb.Id               =Okprb.Dnz_Chr_Id
  AND Okprb.Object1_Id1     =Hp.Party_Id
  AND Okhb.Sts_Code NOT    IN('ENTERED','CANCELLED','TERMINATED')
  And Okprb.Rle_Code        ='CUSTOMER'
  --And Okhb.End_Date in (Select Max(End_Date) From Okc.Okc_K_Headers_B)
   ),
  B AS
  (SELECT Hp1.Party_Number Ibowner1,
    Hp1.Party_Name Ibowner2,
    Hca.Account_Number Ibowner3,
    Hca.ACCOUNT_NAME IBOWNER4,
    Hl.ADDRESS1 ADDRESS
  FROM HZ_PARTIES HP1,
    Hz_Cust_Accounts Hca,
    Hz_Locations Hl,
    Hz_Party_Sites Hps
  WHERE Hp1.Party_Id =Hca.Party_Id
  AND Hp1.Party_Id   =Hps.Party_Id
  AND Hps.Location_Id=Hl.Location_Id
  ),
  C AS
  (SELECT Hp3.Party_Number Originalibownerpartyno,
    Hp3.Party_Name Originalibownerpartyname,
    Hca1.Account_Name Originalibowneracctname,
    hca1.account_number Originalibowneracctno
  FROM Hz_Parties Hp3,
    Hz_Cust_Accounts Hca1,
    Csi.Csi_I_Parties Cip1,
    Csi.Csi_Item_Instances Cii1,
    CSI.Csi_Item_Instances_H Ciih
  WHERE Ciih.Instance_History_Id =Cii1.Instance_Id
  AND Cii1.Instance_Id           =Cip1.Instance_Id
  AND Cip1.Party_Id              =Hp3.Party_Id
  AND Hp3.Party_Id               =Hca1.Party_Id
  AND Cip1.Relationship_Type_Code='OWNER'
    )
SELECT Aa.Ib_Number,
  Aa.Ib_Status,
  Aa.CONTRACTNO,
  Aa.Contractmodifier,
  Aa.Contractpartyno,
  Aa.Contractpartyname, 
  Bb.Ibowner1,
  Bb.Ibowner2,
  Bb.Ibowner3,
  Bb.IBOWNER4,
  Bb.Address,
  Cc.Originalibownerpartyno,
  Cc.Originalibownerpartyname,
  Cc.Originalibowneracctname,
  Cc.Originalibowneracctno
FROM A Aa,
  B Bb,
  C Cc
WHERE Ibowner3 IN(590476,16602,1570260,677709,690443,607679,589563,613589,730529,
743266,666079,3469260,584829,708846,727584,1633010,670079,747694,
661430,741094,759191)