Select *, ROW_NUMBER() OVER (ORDER BY brand_name, product_name, icrt_code asc) AS RowNum FROM (n select distinct aa.id, aa.country_id, aa.product_categories_product_id, aa.product_categories_product_id product_category_product_id, isnull(aa.segment,'No Segment') segment_name, aa.brand_name, aa.product_name, aa.key_attributes, aa.icrt_code, aa.batch_number, aa.ttr,n dd.qty_prc, dd.qty_shp, aa.publishable, aa.active, aa.collect_online, aa.collect_offline, aa.publish, aa.product_id, aa.packaging_valuen from ( select tpl.id, tpl.country_id, pcp.id product_categories_product_id, b.name brand_name, p.name product_name, p.key_attributes,n replace(replace(replace(replace(replace((select distinct i.coden from icrtcodes in left join icrtcodes_test_programs itp on itp.icrtcode_id = i.idn where i.product_id = p.idn and itp.test_program_id = :test_program_idn order by i.code for xml raw('Z')),n '<Z code="',''),'"/>','; '),'>','>'),'"','"'),'<=','<=') icrt_code,n replace(replace(replace(replace(replace(replace((select distinct rtrim(ltrim(convert(char,round(((case when isnumeric(i.ttr_value) = 1 then convert(float,i.ttr_value) else null end - 0.5) * 20),2)))) as ttr_valuen from icrtcodes in left join icrtcodes_test_programs itp on itp.icrtcode_id = i.idn where i.product_id = p.idn and itp.test_program_id = :test_program_idn for xml raw('Z')),n '<Z ttr_value="',''),'"/>','; '),'>','>'),'"','"'),'<=','<='),'<Z/>','') ttr,n replace(replace(replace(replace(replace(replace((select distinct tb.batch_numbern from icrtcodes in left join icrtcodes_test_programs itp on itp.icrtcode_id = i.idn left join test_batches tb on tb.id = i.test_batch_idn where i.product_id = p.idn and itp.test_program_id = :test_program_idn order by 1 for xml raw('Z')),n '<Z batch_number="',''),'"/>','; '),'>','>'),'"','"'),'<=','<='),'<Z/>','') batch_number,n replace(replace(replace(replace(replace((select distinct ps.name from product_segments_products psp, product_segments psn where psp.product_id = pcp.product_id and psp.product_segment_id = ps.id andn ps.type in ('rule','EvalDB','Score-SLS') order by 1 for xml raw('Z')),n '<Z name="',''),'"/>','; '),'>','>'),'"','"'),'<=','<=') segment,n pcp.product_id, ppv.value packaging_value, pcp.publishable publishable, tpl.active,n tpl.collect_online, tpl.collect_offline, tpl.publish,n (select count(*) from countries_languages where country_id = :country_id) qty_languagesn from product_categories pcn join product_categories_products pcp on pcp.product_category_id = pc.idn join products p on p.id = pcp.product_idn left join ( select distinct i.product_id, itp.test_program_idn from icrtcodes in left join icrtcodes_test_programs itp on itp.icrtcode_id = i.id ) tp on tp.product_id = p.idn left join product_properties pp on pp.product_category_id = pc.id and pp.is_packaging = 1n left join product_property_values ppv on ppv.product_property_id = pp.idn left join brands b on b.id = p.brand_idn left join total_product_lists tpl on tpl.product_categories_product_id = pcp.idn where isnull(tpl.country_id, :country_id) = :country_idn and pc.id = :category_idn and tp.test_program_id = :test_program_idn ) aan left join ( select pcp.id, count(distinct(pp.shop_id)) qty_shp, count(pp.id) qty_prcn from total_product_lists tpln join product_categories_products pcp on tpl.product_categories_product_id = pcp.idn join price_publishes pp on pp.total_product_list_id = tpl.idn where tpl.country_id = :country_idn group by pcp.id ) dd on dd.id = aa.product_categories_product_id ) as project_product_listn WHERE 1 = 1n n n ORDER BY brand_name, product_name, icrt_code asc
