select ap.M_Offer_Product_ID as id, 

ap.M_Offer_ID as priceAdjustment, 

p.id as product, 

p.searchKey as searchKey, 

ap.obdiscIsGift as obdiscIsGift,

ap.obdiscQty as obdiscQty, 

ap.obdiscGifqty as obdiscGifqty, 

concat(ap.priceAdjustment.name, ' - ', p.name) as _identifier, 

p.name as productIdentifier, 

case when ap.active = 'Y' and ap.priceAdjustment.active = 'Y' and p.active = 'Y' and coalesce(pl.active, 'Y') = 'Y' then true else false end as active, 

ap.priceAdjustmentType as priceAdjustmentType,

ap.discount as discount, 

ap.discountAmount as discountAmount, 

ap.fixedPrice as fixedPrice, 

ap.startingDate as startingDate, 

ap.endingDate as endingDate  from PricingAdjustmentProduct ap inner join ap.product as p

 inner join p.oBRETCOProlProductList pl with (pl.obretcoProductlist.id in ('A09F7C5B7B8947D4A9103E4A289C2A79'))  

  where ((ap.active = 'Y') AND (ap.priceAdjustment.active = 'Y') AND (p.active = 'Y') AND (pl.active = 'Y')) and

  exists (select 1 from PricingAdjustment p 
  	where p.client.id = '39363B0921BB4293B48383844325E84C' and ((includePriceLists='Y'   and 
  	not exists (select 1          from PricingAdjustmentPriceList pl        
  		where active = true          and pl.priceAdjustment = p          and pl.priceList.id ='496CF965DF9744D2A41248392D1DE407'))    
  		
      or (includePriceLists='N'   and  
  		
      exists (select 1          from PricingAdjustmentPriceList pl        where active = true          and pl.priceAdjustment = 

      p          and pl.priceList.id ='496CF965DF9744D2A41248392D1DE407'))     ) and ((p.includedProducts = 'Y' ) or (p.includedProducts = 'N' and (   exists (select 1       from PricingAdjustmentProduct pap, 
        OBRETCO_Prol_Product ppl      

  		where pap.priceAdjustment = p     and pap.active = true       and pap.product.id = ppl.product.id       and ppl.active = true and pap.product.active = true       and ppl.obretcoProductlist.id ='A09F7C5B7B8947D4A9103E4A289C2A79')))   )  

      and 
  		( p.organization.id in ('0')) and ((p.includedOrganizations='Y'   and not exists (select 1          from 

      PricingAdjustmentOrganization o        where active = true          and o.priceAdjustment = p          and o.startingDate is null           and o.endingDate is null           and o.organization.id ='D270A5AC50874F8BA67A88EE977F8E3B'))

          or (p.includedOrganizations='N'   and  exists (select 1          from PricingAdjustmentOrganization o        where active = true          and o.priceAdjustment = p          and o.organization.id ='D270A5AC50874F8BA67A88EE977F8E3B'))

               )  and (oBDISCCCurrency is null or      oBDISCCCurrency.id = '102')   and ap.priceAdjustment.id = p.id ) and 1=1 order by ap.id, active