CREATE OR REPLACE FUNCTION r_report_realization_visit_plan_for_all_salesman(
    character varying,
    bigint,
    bigint,
    character varying,
    character varying,
    bigint,
    bigint,
    character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              		REFCURSOR := 'refHeader';
    pRefDetail             	 	REFCURSOR := 'refDetail';

    	pSessionId					ALIAS FOR $1;
    	pTenantId					ALIAS FOR $2;
	pOuId						ALIAS FOR $3;
	pDateFrom					ALIAS FOR $4;
	pDateTo						ALIAS FOR $5;
	pUserId						ALIAS FOR $6;
	pRoleId						ALIAS FOR $7;
	pDatetime					ALIAS FOR $8;

    vEmptyId					bigint := -99;
    vEmptyValue					character varying := '';

    vFilterDateFrom				character varying := '';
    vFilterDateTo				character varying := '';


BEGIN

	IF pDateFrom <> vEmptyValue THEN
		vFilterDateFrom := ' AND A.visit_date >= '''|| pDateFrom ||'''';
	END IF;

	IF pDateTo <> vEmptyValue THEN
		vFilterDateTo := ' AND A.visit_date <= '''|| pDateTo ||'''';
	END IF;

	OPEN pRefHeader FOR
		SELECT	pDateFrom AS date_from, pDateTo AS date_to, f_get_ou_name(pOuId) AS ou_name, f_get_ou_code(pOuId) AS ou_code,
			pDatetime AS datetime, f_get_role_name(pRoleId) AS role_name, f_get_user_fullname(pUserId) AS fullname;
	RETURN NEXT pRefHeader;

	OPEN pRefDetail FOR
		EXECUTE '
			SELECT
			C.partner_code as salesman_code,
			A.visit_date,
			A.check_in_time_server,
			A.check_out_time_server,
			COALESCE(TRUNC((A.check_out_time_server - A.check_in_time_server)/60), 0) as visit_duration,
			E.partner_code as customer_code,
			E.partner_name as customer_name,
			F.address_desc as customer_address,
			F.city,
			G.idwil,
			E.price_level,
			CASE
				WHEN (COUNT(A) = 0) THEN $1
				WHEN (A.check_in_time_server is not null) AND (A.check_out_time_server is null) THEN $2
				WHEN (A.check_in_time_server is not null) AND (A.check_out_time_server is not null) THEN A.check_out_status
			END AS status_visit,
			CASE
				WHEN (A.check_in_time_server is not null) AND (A.check_out_time_server is not null) THEN A.check_out_remark
				ELSE ''
			END AS remark,
			CASE
				WHEN (B.flg_adhoc = $3) AND (COUNT(A) != 0) THEN $4
				ELSE $5
			END AS flg_adjusment_visit_plan,
			CASE
				WHEN (I.flg_input_by_salesman = $6) AND (I.status != $7) AND (J.status_doc != $7) THEN SUM(I.grand_total_order)
				ELSE 0
			END AS order_amount,
			CASE
				WHEN (I.flg_input_by_salesman = $6) AND (I.status != $7) AND (J.status_doc != $7)
				AND  (K.doc_date = A.visit_date) THEN SUM(K.total_gross_amount)
				ELSE 0
			END AS pi_amount
			FROM sl_salesman_visit_realization A
			JOIN sl_salesman_visit_plan B
			ON A.salesman_visit_plan_id = B.salesman_visit_plan_id
			JOIN m_partner C
			ON A.salesman_id = C.partner_id AND A.tenant_id = C.tenant_id
			JOIN m_partner_ou D
			ON C.partner_id = D.partner_id
			JOIN m_partner E
			ON A.customer_id = E.partner_id AND A.tenant_id = E.tenant_id
			JOIN m_partner_address F
			ON E.partner_id = F.partner_id
			JOIN m_customer_ext_for_sas G
			ON E.partner_id =  G.partner_id AND A.tenant_id = G.tenant_id
			JOIN m_user_partner H
			ON A.salesman_id = H.partner_id AND A.tenant_id = H.tenant_id
			LEFT JOIN sl_order I
			ON A.visit_date = I.doc_date     AND A.tenant_id = I.tenant_id
					AND H.user_id = I.salesman_user_id
					AND A.customer_id = I.toko_partner_id

			LEFT JOIN sl_order_external J
			ON I.order_id = J.ref_id
			LEFT JOIN sl_pro_invoice_external K
			ON J.order_external_id = K.ref_id
			WHERE EXISTS(
				SELECT 1
				FROM t_user X, t_user_role Y, t_policy_ou Z
				WHERE X.tenant_id = A.tenant_id AND
				    X.user_id = $8 AND
				    X.user_id = Y.user_id AND
				    X.policy_default_id = Z.policy_id AND
				    D.ou_id = Z.ou_id
			) AND A.tenant_id = $9 AND B.active = $6 '|| vFilterDateFrom || vFilterDateTo ||'
			ORDER BY salesman_code, customer_name, A.visit_date
			GROUP BY
			C.partner_code,
			A.visit_date,
			A.check_in_time_server,
			A.check_out_time_server,
			A.check_out_status,
			A.check_out_remark,
			B.flg_adhoc,
			E.partner_code,
			E.partner_name,
			F.address_desc,
			F.city,
			G.idwil,
			E.price_level,
			I.flg_input_by_salesman,
			I.status,
			J.status_doc,
			K.doc_date'
		USING 'TIDAK DIKUNJUNGI', 'SEDANG DIKUNJUNGI', 'N', 'YA', 'TIDAK', 'Y', 'C', pUserId, pTenantId;
	RETURN NEXT pRefDetail;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1000
  ROWS 1000;