f_get_report_partner_address_for_vat_out INDOCOM 20171123

-- Function: f_get_report_partner_address_for_vat_out(bigint, bigint)

-- DROP FUNCTION f_get_report_partner_address_for_vat_out(bigint, bigint);

CREATE OR REPLACE FUNCTION f_get_report_partner_address_for_vat_out(bigint, bigint)
  RETURNS text AS
$BODY$
DECLARE
    pTenantId       alias for $1;
    pPartnerId      alias for $2;

    vResult         text;
    vYes            character varying;

BEGIN

    vResult := ' | | ';
    vYes := 'Y';

    IF EXISTS(SELECT 1 FROM m_partner_npwp WHERE tenant_id = pTenantId AND partner_id = pPartnerId) THEN
        SELECT address1 || '|' || address2 || '|' || address3 INTO vResult
        FROM m_partner_npwp
        WHERE tenant_id = pTenantId
            AND partner_id = pPartnerId;
    ELSE
        SELECT address1 || '|' || address2 || '|' || address3 INTO vResult
        FROM m_partner_address
        WHERE tenant_id = pTenantId
            AND partner_id = pPartnerId
            AND flg_official = vYes;
    END IF;

    return vResult;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION f_get_report_partner_address_for_vat_out(bigint, bigint)
  OWNER TO sts;