Consents

Package for managing user consents

example

SET SERVEROUTPUT ON;

DECLARE
    c CLOB;
    id app_consents.id%TYPE;
BEGIN
    c := pck_api_consents.lookup();
    DBMS_OUTPUT.PUT_LINE('Consents: ' || SUBSTR(c,1,200) || '...');

    id := JSON_VALUE(c, '$[0].id');
    DBMS_OUTPUT.PUT_LINE('First Consent ID: ' || id);

    c:= pck_api_consents.download(id);
    DBMS_OUTPUT.PUT_LINE('Consent Content: ' || SUBSTR(c,1,200) || '...');

END;
/

specification

CREATE OR REPLACE PACKAGE odbvue.pck_api_consents AS -- Package for managing user consents

    FUNCTION lookup ( -- Function to retrieve consent list
        p_language IN app_consents.language_id%TYPE DEFAULT NULL, -- Language filter for consents
        p_active   IN app_consents.active%TYPE DEFAULT 'Y' -- Active status filter
    ) RETURN CLOB; -- Returns consent list as CLOB

    FUNCTION verify ( -- Function to check if user has given consent
        p_user_id    IN app_user_consents.user_id%TYPE, -- User identifier
        p_consent_id IN app_user_consents.consent_id%TYPE -- Consent identifier
    ) RETURN CHAR; -- Returns 'Y' if consent given, 'N' otherwise

    PROCEDURE give ( -- Procedure to record user consent
        p_user_id    IN app_user_consents.user_id%TYPE, -- User identifier
        p_consent_id IN app_user_consents.consent_id%TYPE -- Consent identifier
    );

    PROCEDURE withdraw ( -- Procedure to revoke user consent
        p_user_id    IN app_user_consents.user_id%TYPE, -- User identifier
        p_consent_id IN app_user_consents.consent_id%TYPE -- Consent identifier
    );

    FUNCTION download ( -- Function to download consent content
        p_consent_id IN app_consents.id%TYPE -- Consent identifier
    ) RETURN CLOB; -- Returns consent content as CLOB
END pck_api_consents;
/


-- sqlcl_snapshot {"hash":"fa920dda844ba385830346e16dc63ef6b3a6c7ff","type":"PACKAGE_SPEC","name":"PCK_API_CONSENTS","schemaName":"ODBVUE","sxml":""}

implementation

CREATE OR REPLACE PACKAGE BODY odbvue.pck_api_consents AS

    FUNCTION lookup (
        p_language IN app_consents.language_id%TYPE DEFAULT NULL,
        p_active   IN app_consents.active%TYPE DEFAULT 'Y'
    ) RETURN CLOB IS
        v_result CLOB;
    BEGIN
        SELECT
            JSON_ARRAYAGG(
                JSON_OBJECT(
                    'id' VALUE ac.id,
                            'language' VALUE ac.language_id,
                            'name' VALUE ac.name,
                            'created' VALUE to_char(ac.created, 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
                            'active' VALUE ac.active
                )
            )
        INTO v_result
        FROM
            app_consents ac
        WHERE
            ( p_language IS NULL
              OR ac.language_id = p_language )
            AND ac.active = p_active;

        RETURN v_result;
    END lookup;

    FUNCTION verify (
        p_user_id    IN app_user_consents.user_id%TYPE,
        p_consent_id IN app_user_consents.consent_id%TYPE
    ) RETURN CHAR IS
        v_consent_status CHAR(1 CHAR);
    BEGIN
        SELECT
            CASE
                WHEN COUNT(*) > 0 THEN
                    'Y'
                ELSE
                    'N'
            END
        INTO v_consent_status
        FROM
            app_user_consents
        WHERE
                user_id = p_user_id
            AND consent_id = p_consent_id;

        RETURN v_consent_status;
    END verify;

    PROCEDURE give (
        p_user_id    IN app_user_consents.user_id%TYPE,
        p_consent_id IN app_user_consents.consent_id%TYPE
    ) IS
    BEGIN
        INSERT INTO app_user_consents (
            user_id,
            consent_id,
            given,
            revoked
        ) VALUES ( p_user_id,
                   p_consent_id,
                   systimestamp,
                   NULL );

    END give;

    PROCEDURE withdraw (
        p_user_id    IN app_user_consents.user_id%TYPE,
        p_consent_id IN app_user_consents.consent_id%TYPE
    ) IS
    BEGIN
        UPDATE app_user_consents
        SET
            revoked = systimestamp
        WHERE
                user_id = p_user_id
            AND consent_id = p_consent_id;

    END withdraw;

    FUNCTION download (
        p_consent_id IN app_consents.id%TYPE
    ) RETURN CLOB IS
        v_content CLOB;
    BEGIN
        SELECT
            content
        INTO v_content
        FROM
            app_consents
        WHERE
            id = p_consent_id;

        RETURN v_content;
    END download;

END pck_api_consents;
/


-- sqlcl_snapshot {"hash":"b12762d97edc1a9378706229616f4d36f7a55782","type":"PACKAGE_BODY","name":"PCK_API_CONSENTS","schemaName":"ODBVUE","sxml":""}

Dependencies:

Referenced type	Referenced name
TABLE	APP_CONSENTS
TABLE	APP_USER_CONSENTS

DOWNLOAD

Function to download consent content

Argument name	In Out	Data type	Default value	Description
	OUT	CLOB		Returns consent content as CLOB
P_CONSENT_ID	IN	CHAR		Consent identifier

GIVE

Procedure to record user consent

Argument name	In Out	Data type	Default value	Description
P_USER_ID	IN	CHAR		User identifier
P_CONSENT_ID	IN	CHAR		Consent identifier

LOOKUP

Function to retrieve consent list

Argument name	In Out	Data type	Default value	Description
	OUT	CLOB		Returns consent list as CLOB
P_LANGUAGE	IN	CHAR	NULL	Language filter for consents
P_ACTIVE	IN	CHAR	'Y'	Active status filter

VERIFY

Function to check if user has given consent

Argument name	In Out	Data type	Default value	Description
	OUT	CHAR		Returns 'Y' if consent given, 'N' otherwise
P_USER_ID	IN	CHAR		User identifier
P_CONSENT_ID	IN	CHAR		Consent identifier

WITHDRAW

Procedure to revoke user consent

Argument name	In Out	Data type	Default value	Description
P_USER_ID	IN	CHAR		User identifier
P_CONSENT_ID	IN	CHAR		Consent identifier