DROP PROCEDURE IF EXISTS public.sp_getsetadditionalfactorsrequestcatalog;

CREATE OR REPLACE PROCEDURE public.sp_getsetadditionalfactorsrequestcatalog(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT NULL,
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    p_xml text := "Xml";
    p_isnew boolean := "IsNew";
    p_id uuid := "ID";
    p_queuename varchar(100) := "QueueName";
    p_datetime timestamp := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user uuid := "User";
    
    v_result text;
    v_contentencoding varchar(255) := 'utf-8';
    v_type varchar(255) := 'ADDITIONAL_FACTORS_REQUEST_CATALOG';
    v_operationtype varchar(255);
    v_ownerid varchar(255);
    c_ftload varchar(6) := 'FTLoad';
BEGIN
    IF (p_xml IS NULL) THEN
        v_ownerid := p_id;
        v_operationtype := c_ftload;


        SELECT XMLELEMENT(
            NAME "ADDITIONAL_FACTORS_REQUEST_CATALOG",
            XMLELEMENT(
                NAME "header",
                XMLFOREST(
                    v_operationtype AS "TYPE"
                )
            ),
            XMLELEMENT(
                NAME "body",
                XMLELEMENT(
                    NAME "ADDITIONAL_FACTORS_REQUEST_CATALOGS",
                    (SELECT XMLAGG(
                        XMLELEMENT(
                            NAME "ADDITIONAL_FACTORS_REQUEST_CATALOG",
                            XMLFOREST(
                                afda.additionalfactorsdrugapplicationitemid AS "ID",
                                afda.drugapplicationlistitem AS "DrugApplicationListItem",
                                afda.additionalfinanc AS "AdditionalFinanc",
                                afda.palliative AS "Palliative",
                                b.benefitviews AS "BenefitViews",
                                b.benefitcode AS "BenefitCode"
                            )
                        )
                    ) FROM rds.t_additional_factors_drug_application_item afda
                    JOIN rds.t_drug_application_list_item dali ON dali.drugapplicationlistitemid = afda.drugapplicationlistitem
                    JOIN rds.t_drug_application_list dal ON dal.drugapplicationlistid = dali.drugapplicationlist
                    LEFT JOIN rds.t_benefits b ON b.benefitsid = afda.benefits
                    WHERE EXTRACT(YEAR FROM dal.enddate) >= EXTRACT(YEAR FROM p_datetime) 
                    AND dal.isactive = true)
                )
            )
        )::text INTO v_result;

        INSERT INTO rds.t_rabbit_send_object (
            rabbitsendobjectid, queuename, content, messageid, headers,
            contentencoding, type, userid, state, createdatetime, ownerid
        )
        SELECT 
            gen_random_uuid(),
            s.servername,
            public.converttexttobinaryzip(v_result, ''),
            NULL,
            NULL,
            v_contentencoding,
            v_type,
            p_user,
            0,
            p_datetime,
            v_ownerid
        FROM rds.t_sync_servers s
        WHERE (p_queuename IS NOT NULL AND s.servername = p_queuename)
           OR (p_queuename IS NULL AND s.servertype IN (0, 1));
    END IF;
END;
$$;