CREATE OR REPLACE FUNCTION APS.fn_RecipeLSName(
    p_RecipeID uuid
)
    RETURNS VARCHAR(1000)
AS
$$
DECLARE
    v_Agg     VARCHAR(1000);
    v_TypeRec INTEGER;

BEGIN

    SELECT
        r.Status,
        COALESCE(trn.NameRus, mnn.NameRus)
    INTO
        v_TypeRec,
        v_Agg
    FROM APS.T_RECIPE r
    LEFT JOIN PHARM.T_C_TRN trn ON trn.CTrnID = r.CTrn
    LEFT JOIN PHARM.T_C_MNN mnn ON mnn.CMnnID = r.CMnn
    WHERE
        r.RecipeID = p_RecipeID;

    IF v_TypeRec = 1 THEN
        SELECT
            TRIM(SUBSTRING((SELECT
                                STRING_AGG
                                (TRIM(CASE
                                          WHEN trn.product_type = 0 THEN
                                              COALESCE(trn.name_rus, '') || ' ' ||
                                              COALESCE(mf.name, '') || ' ' ||
                                              COALESCE(dv.value, '') || ', ' ||
                                              COALESCE(mp.name, '')
                                          ELSE
                                              COALESCE(trn.name_rus, '') || ' ' ||
                                              COALESCE(dv.value, '') || ', ' ||
                                              COALESCE(mp.name, '')
                                    END
                                 ),
                                 ', '
                                )
                            FROM PHARM.T_BILL_OF_GOODS bg
                            JOIN PHARM.T_C_TRN trn ON trn.c_trn_id = bg.c_trn
                            LEFT JOIN PHARM.T_MEDICATION_FORMS mf ON mf.medication_forms_id = bg.medication_forms
                            LEFT JOIN PHARM.S_DLO_DOSEVIEW dv ON dv.id = bg.dose_view
                            LEFT JOIN PHARM.T_MEDICAMENT_PRODUCER mp
                            ON mp.medicament_producer_id = bg.medicament_producer
                            WHERE
                                EXISTS (SELECT
                                            1
                                        FROM APS.T_RECIPE_ELEMENTS re
                                        JOIN STOR.T_REMAINS rem ON rem.remains_id = re.remains
                                        WHERE
                                              re.recipe = p_RecipeID
                                          AND rem.bill_of_goods = bg.bill_of_goods_id))::TEXT,
                           1,
                           NULL
                 )
            )::varchar(1000)
        INTO v_Agg;
    END IF;

    RETURN v_Agg;

END;
$$
    LANGUAGE plpgsql;