(SELECT
tsl.rtl_loc_id store_id,
loc_rtl_loc.store_name store_name,
hrs_employee.login_id Emp_id,
COALESCE(crm_party.first_name,' ')+' '+ COALESCE(crm_party.middle_name,' ')+' '+COALESCE(crm_party.last_name,' ') Emp_name,
convert(datetime, tsl.business_date, 103) bus_date,
tsl.business_date bus_time
	  FROM trn_trans tsl -- transaction header
--join transaction details
	  INNER JOIN trl_sale_lineitm tr_det
	  ON tr_det.organization_id=tsl.organization_id
	  AND tr_det.rtl_loc_id=tsl.rtl_loc_id
	  AND tr_det.wkstn_id=tsl.wkstn_id
	  AND tr_det.trans_seq=tsl.trans_seq
--join for price override extraction
	  INNER JOIN trl_rtl_price_mod prc_chg
	  ON prc_chg.organization_id=tsl.organization_id
	  AND tr_det.rtl_loc_id=prc_chg.rtl_loc_id
	  AND tr_det.wkstn_id=prc_chg.wkstn_id
	  AND tr_det.trans_seq=prc_chg.trans_seq
	  AND tr_det.rtrans_lineitm_seq=prc_chg.rtrans_lineitm_seq
	  AND rtl_price_mod_reascode='PRICE_OVERRIDE'
	  AND void_flag=0
--join for login extraction
	  INNER JOIN hrs_employee
	  ON hrs_employee.organization_id=tsl.organization_id
	  AND hrs_employee.party_id=tsl.create_user_id
--join for employee name extraction
	  LEFT JOIN crm_party
	  ON crm_party.organization_id=tsl.organization_id
	  AND crm_party.party_id=hrs_employee.party_id
	  LEFT JOIN loc_rtl_loc
	  ON loc_rtl_loc.organization_id=tsl.organization_id
	  AND loc_rtl_loc.rtl_loc_id=tsl.rtl_loc_id
	  WHERE
	   tsl.trans_typcode = 'RETAIL_SALE'
	   AND tsl.trans_statcode='COMPLETE'
	  -- tsl.rtl_loc_id = (COALESCE(null,tsl.rtl_loc_id))
	 -- AND tsl.return_flag = '0'
	--  AND tsl.QUANTITY <> 0
	 -- AND tsl.business_date>$P{Date_from}
	 -- AND tsl.business_date< $P{Date_to}
)