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             xml          := "Xml"::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         uuid;
    c_ftload          varchar(6)   := 'FTLoad';
BEGIN
    IF (p_xml IS NULL) THEN
        v_ownerid := p_id;
        v_operationtype := c_ftload;
        
        
        SELECT '<ADDITIONAL_FACTORS_REQUEST_CATALOG><header><TYPE>' || c_ftload ||
               '</TYPE></header><body><ADDITIONAL_FACTORS_REQUEST_CATALOGS>' ||
               COALESCE(STRING_AGG(
                            XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                            ''), '')
                   ||
               '</ADDITIONAL_FACTORS_REQUEST_CATALOGS></body></ADDITIONAL_FACTORS_REQUEST_CATALOG>'
        INTO v_result
        FROM (SELECT XMLELEMENT(
                         NAME "ADDITIONAL_FACTORS_REQUEST_CATALOG",
                         XMLFOREST(
                             afda.additionalfactorsdrugapplicationitemid AS "ID",
                             afda.drugapplicationlistitem AS "DrugApplicationListItem",
                             b.benefitviews AS "BenefitViews",
                             b.benefitcode AS "BenefitCode"
                         )
                     ) AS xml_elem
              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
             ) tt;
        
        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;
$$;