SQL:
****
select ap.M_Offer_Product_ID as id, 

ap.M_Offer_ID as priceAdjustment,

p.m_product_id as product,

p.Value as searchKey,

ap.EM_Obdisc_Is_Gift as obdiscIsGift,

ap.EM_Obdisc_Qty as obdiscQty,

ap.EM_Obdisc_Gifqty as obdiscGifqty,

concat(ap.Price_Adjustment_Type, ' - ', p.Name) as _identifier,

p.Name as productIdentifier,

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

from M_Offer_Product ap 

inner join m_product p on ap.m_product_id = p.m_product_id 

inner join OBRETCO_Prol_Product pl on ap.m_product_id = pl.m_product_id and (pl.obretco_productlist_id in ('A09F7C5B7B8947D4A9103E4A289C2A79')) 

cross join M_Offer mo where ap.M_Offer_ID = mo.M_Offer_ID and 

(exists (select 1 from M_Offer p where p.AD_Client_ID = '39363B0921BB4293B48383844325E84C' 

and (p.Pricelist_Selection = 'Y' and

not (exists (select 1 from M_Offer_PriceList pl where pl.Isactive ='Y' and 

pl.M_Offer_ID = mo.M_Offer_ID and pl.M_PriceList_ID='496CF965DF9744D2A41248392D1DE407'))

or p.Pricelist_Selection = 'N' and (exists (select 1 from M_Offer_PriceList pl where pl.Isactive='Y' and pl.M_Offer_ID=mo.M_Offer_ID and pl.M_PriceList_ID='496CF965DF9744D2A41248392D1DE407'))) 

and (p.Product_Selection='Y' or p.Product_Selection='N' and (exists ( select 1 from M_Offer_Product pop 

cross join OBRETCO_Prol_Product ppl cross join M_Product pm where pop.M_Product_ID=pm.M_Product_ID 

and pop.M_Offer_ID=p.M_Offer_ID and pop.M_Product_ID=ppl.M_Product_ID and ppl.Isactive='Y' and pm.Isactive='Y' and 
ppl.Obretco_Productlist_ID='A09F7C5B7B8947D4A9103E4A289C2A79')))

and (p.AD_Org_ID in ('0')) and (p.Org_Selection='Y' and not (exists (select 1 from 

M_Offer_Organization o where o.Isactive='Y' and o.M_Offer_ID=p.M_Offer_ID 

and (o.StartDate is null) and (o.EndDate is null) and o.AD_Org_ID='D270A5AC50874F8BA67A88EE977F8E3B')) 

or p.Org_Selection='N' and (exists (select 1 from M_Offer_Organization o where o.Isactive='Y' and o.M_Offer_ID=p.M_Offer_ID and o.AD_Org_ID='D270A5AC50874F8BA67A88EE977F8E3B')))

 and (p.EM_OBDISC_C_Currency_ID is null or p.EM_OBDISC_C_Currency_ID='102') and ap.M_Offer_ID=p.M_Offer_ID))
 
  and 1=1 order by ap.M_Offer_Product_ID