CREATE OR REPLACE FUNCTION fs_close_mlm_process_admin(bigint,character varying,bigint,character varying,bigint)
  RETURNS void AS
$BODY$
DECLARE

	pTenantId				ALIAS FOR $1;
	pPeriod 		    	ALIAS FOR $2;
	pUserId 		    	ALIAS FOR $3;
	pDatetime 		    	ALIAS FOR $4;
	pOuId 			    	ALIAS FOR $5;


	vYes					character varying;
	vDocDraft				character varying;
	vDocReleased			character varying;
	v1						character varying;
	vProcessCode			character varying;
	vDbName			text;
	vDbUser			text;
	vDbPassword		text;
	vDbPort			text;
	vDbHostAddr		text;

BEGIN


	SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.NAME')::text INTO vDbName;
	SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.USER')::text INTO vDbUser;
	SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.PASSWORD')::text INTO vDbPassword;
	SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.PORT')::text INTO vDbPort;
	SELECT f_get_value_system_config_by_param_code(pTenantId, 'PALOMAWEB.DB.HOSTADDR')::text INTO vDbHostAddr;

	vDocDraft 		:='D';
	vDocReleased 	:='R';
	vYes			:='Y';
	v1				:='1';
	vProcessCode	:='TUTUP_PELAPORAN';

	--member sales
	PERFORM dblink_exec(
	'dbname='||vDbName||' port='||vDbPort||' hostaddr= '||vDbHostAddr||' user= '||vDbUser||' password= '||vDbPassword||''::text,
	'UPDATE mlm_member_sales SET version = version+1, status_doc = '||vDocReleased||',update_user_id= '||pUserId||',update_datetime='||pDatetime||
		' WHERE SUBSTR(doc_date,1,6)='||pPeriod||' AND status_doc='||vDocDraft||'');


	--Admin Process
	PERFORM dblink_exec(
	'dbname='||vDbName||' port='||vDbPort||' hostaddr='||vDbHostAddr||' user='||vDbUser||' password='||vDbPassword||''::text,
	'UPDATE m_admin_process SET version = version+1,status_ledger = '||v1||',update_user_id= '||pUserId||',update_datetime='||pDatetime||
		' WHERE year_month='||pPeriod||'');

	UPDATE mlm_admin_process_monthly
	SET flag_process = vYes,
		process_datetime = pDatetime,
		process_user_id =pUserId,
		update_datetime =pDatetime,
		update_user_id=pUserId,
		version = version+1
	WHERE date_year_month = pPeriod
	AND tenant_id = pTenantId
	AND process_code =vProcessCode
	AND ou_id = pOuId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
/