CREATE OR REPLACE FUNCTION xi_set_sales_invoice_delivered(bigint, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$

DECLARE
	pTenantId			ALIAS FOR $1;
	pSessionId			ALIAS FOR $2;
	pProcessNo			ALIAS FOR $3;
	
	pRefPayloadData		REFCURSOR := 'refPayloadData';
	vOuCode				character varying;
	vSoId				bigint;
	vParameterCode 		character varying:='API.UPDATE.PI.NON.BE.URL';
	vUrlApi				character varying;
	vParameterCodeApiKey 		character varying:='API.KEY.FOR.API.KEMITRAAN';
	vApiKey				character varying;
	vApiName			character varying:='Update Pi Non BE';
	
	vProcessId			bigint;
	vPengirimanId		bigint;
	vDoId				bigint;
	vUserId				bigint;
	vDatetime			character varying(14);
	
BEGIN

	SELECT A.process_message_id INTO vProcessId
	FROM t_process_message A
	WHERE A.tenant_id = pTenantId AND
		A.process_name = 'xi_set_sales_invoice_delivered' AND
		A.process_no = pProcessNo;
		
	SELECT CAST(A.process_parameter_value AS bigint) INTO vPengirimanId
	FROM t_process_parameter A
	WHERE A.process_message_id = vProcessId AND
		A.process_parameter_key = 'pengirimanId';
	
	SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
	FROM t_process_parameter A
	WHERE A.process_message_id = vProcessId AND
		A.process_parameter_key = 'userId';

	SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
	FROM t_process_parameter A
	WHERE A.process_message_id = vProcessId AND
		A.process_parameter_key = 'datetime';	
	
	SELECT f_get_value_system_config_by_param_code(pTenantId, vParameterCode) INTO vUrlApi;
	SELECT f_get_value_system_config_by_param_code(pTenantId, vParameterCodeApiKey) INTO vApiKey;
	
	Open pRefPayloadData FOR
		SELECT row_to_json(A)::character varying
		FROM(
			SELECT pSessionId AS session_uuid, vUrlApi AS url, vApiName AS api_name, vApiKey AS api_key, B.tenant_code AS tenant_code, 
					A.update_datetime AS datetime, f_get_username(A.update_user_id) AS username, vUserId AS user_id,
					(
						SELECT array_to_json(array_agg(row_to_json(A)))
						FROM (
							SELECT f_get_ou_code(A.ou_id) AS ou_code, 
								A.doc_no AS doc_no, A.doc_date AS doc_date,
								B.doc_no AS so_doc_no, B.doc_date AS so_doc_date, A.due_date AS due_date
							FROM sl_invoice_temp A
							JOIN sl_so B ON A.ref_id = B.so_id AND A.tenant_id = B.tenant_id AND B.ref_doc_type_id = 370
							JOIN sl_do C ON B.so_id = C.ref_id AND B.doc_type_id = C.ref_doc_type_id
							JOIN in_pengiriman_item D ON C.do_id = D.ref_id AND C.doc_type_id = D.ref_doc_type_id
							WHERE A.status_doc <> 'V'
							AND A.tenant_id = pTenantId
							AND D.pengiriman_id = vPengirimanId
						) A
					) AS pi_list
			FROM in_pengiriman A
			JOIN t_tenant B ON A.tenant_id = B.tenant_id
			WHERE A.pengiriman_id = vPengirimanId
			AND A.tenant_id = pTenantId
		) A;
	RETURN NEXT pRefPayloadData;
	
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /