CREATE OR REPLACE FUNCTION rpt.r_report_sales(character varying, bigint, bigint, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefDetail              REFCURSOR := 'refDetail';
    pSessionId              ALIAS FOR $1;
    pRecordOwnerId          ALIAS FOR $2;
    pSalesmanId		        ALIAS FOR $3;
    pStartDate              ALIAS FOR $4;
    pEndDate                ALIAS FOR $5;
   
    vEmpty                  CHARACTER VARYING := '';
    vNo                     CHARACTER VARYING := 'N';
    vYes                    CHARACTER VARYING := 'Y';
    vFilterRecordOwner		character varying := '';
    vFilterSalesman			character varying := '';
    
    vPosDocTypeId	        BIGINT := 390;
    vEmptyId                BIGINT := -99;

BEGIN

	OPEN pRefHeader FOR
		SELECT pStartDate AS date_from, pEndDate AS date_to, f_get_record_owner_name(pRecordOwnerId) AS record_owner_name, 
			f_get_salesman_name(pSalesmanId) AS salesman_name;
	RETURN NEXT pRefHeader;
	
	IF pRecordOwnerId <> vEmptyId THEN
		vFilterRecordOwner := ' AND A.record_owner_id = '''|| pRecordOwnerId ||'''';
	END IF;
	
	IF pSalesmanId <> vEmptyId THEN 
		vFilterSalesman := ' AND A.salesman_id = '''|| pSalesmanId ||'''';
	END IF;
	
	OPEN pRefDetail FOR
		EXECUTE '
			SELECT A.doc_date, f_get_record_owner_name(A.record_owner_id) AS store_name, A.doc_date, f_get_salesman_name(A.salesman_id) AS salesman_name,  
				D.top_days, COALESCE(SUM(B.item_amount - B.item_discount_amount), 0) AS total_amount_sales,
				COALESCE(SUM(C.amount_total), 0) AS hpp_amount,
				COALESCE(SUM(B.item_amount - B.item_discount_amount), 0) - COALESCE(SUM(C.amount_total), 0) AS profit_amount
			FROM trx_pos A
			INNER JOIN trx_pos_item B ON A.pos_id = B.pos_id
			INNER JOIN m_customer D ON A.customer_id = D.customer_id
			LEFT JOIN in_summary_cogs C ON A.record_owner_id = C.record_owner_id
				AND substring(A.doc_date, 1, 6) = C.year_month
				AND C.product_id = B.product_id
			WHERE A.doc_type_id = $1 
				AND A.doc_date BETWEEN $2 AND $3
				' || vFilterRecordOwner || vFilterSalesman || '
			GROUP BY A.doc_date, A.record_owner_id, A.doc_date, D.top_days, A.salesman_id'
		USING vPosDocTypeId, pStartDate, pEndDate;
	RETURN NEXT pRefDetail;
	
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  SET search_path = public, mstr, ptg, htg, pb, pj, batch
  COST 100;
  /