req_usr_pro_pdt_en_cours_de_num_tl@().sql

create OR REPLACE function req_usr_pro_pdt_en_cours_de_num_tl() returns TABLE("Ean" character varying, "Titre" character varying, "Type" character varying, "Prix ttc" numeric, "Diffuseur" character varying, "Fournisseur" character varying, "Editeur" character varying, "Grande Image" character varying, "Petite Image" character varying, "Résumé" character varying, "Sommaire" character varying, "Biographie" character varying, "Réceptionné le" character varying, "A " character varying, "Site" character varying, "Enrichi le " character varying, "A" character varying, "Vérrouille commande" boolean)
	language sql
as $$
WITH PDT_A_NUM AS ( SELECT code_produit,
                           date_envoi_numerisation,
                           verouille_commande_client,
                           site_commande,
                           date_enrichissement
                    FROM produit_a_numeriser
                    WHERE date_reintegration IS NULL
                      AND numero_bon_accompagnement IS NULL),
     FICHIERS AS (SELECT (array_agg(cdtypfichier)) AS type_fichiers,
                         cdpdt
                  FROM pdt_typefichier
                           INNER JOIN PDT_A_NUM
                                      ON cdpdt = code_produit
                  GROUP BY cdpdt)
SELECT DISTINCT p.cdpdt :: VARCHAR(21),
                p.libpdt :: VARCHAR(255),
                p.cdtyppdt :: VARCHAR(2),
                pdp.pvttc::NUMERIC(19, 4),
                d.nomdiff:: VARCHAR(255),
                f.nomfour :: VARCHAR(30),
                e.nomeditr :: VARCHAR(255),
                ('GI'=ANY(FICHIERS.type_fichiers)) :: INTEGER :: VARCHAR,
                ('PI'=ANY(FICHIERS.type_fichiers)) :: INTEGER :: VARCHAR,
                ('RE'=ANY(FICHIERS.type_fichiers)) :: INTEGER :: VARCHAR,
                ('SO'=ANY(FICHIERS.type_fichiers)) :: INTEGER :: VARCHAR,
                ('BI'=ANY(FICHIERS.type_fichiers)) :: INTEGER :: VARCHAR,
                TO_CHAR(pan.date_envoi_numerisation, 'dd/mm/yyyy') :: VARCHAR(10),
                TO_CHAR(pan.date_envoi_numerisation, 'HH24:MI:SS') :: VARCHAR(8),
                (SELECT nomsite FROM site WHERE cdsiteenc = pan.site_commande) :: VARCHAR(255),
                TO_CHAR(pan.date_enrichissement, 'dd/mm/yyyy') :: VARCHAR(10),
                TO_CHAR(pan.date_enrichissement, 'HH24:MI:SS') :: VARCHAR(8),
                pan.verouille_commande_client
FROM PDT_A_NUM pan
         LEFT JOIN FICHIERS ON FICHIERS.cdpdt = pan.code_produit
         INNER JOIN produit p
                    ON p.cdpdt = pan.code_produit
         LEFT JOIN pdt_dateprix pdp
                   ON pdp.cdpdt = pan.code_produit
                       AND CURRENT_DATE BETWEEN dtdebvali1 AND dtfinval
         LEFT JOIN editeur e
                   ON e.cdeditr = p.cdeditr
         LEFT JOIN resadec.public.diff_editr de
                   ON de.cdeditr = p.cdeditr
                       AND CURRENT_DATE BETWEEN de.dtdebvali1 AND de.dtfinval
         LEFT JOIN diffuseur d
                   ON d.cddiff = de.cddiff
         LEFT JOIN pdt_four pf
                   ON pf.cdpdt = pan.code_produit
                       AND pf.cdfourprinc
                       AND CURRENT_DATE BETWEEN pf.dtdebvali1 AND pf.dtfinval
         LEFT JOIN fournisseur f
                   ON f.cdfour = pf.cdfour
         LEFT JOIN (SELECT MAX(dtenrich)AS dtenrich,seb.cdpdt as cdpdt
                    FROM stat_enrichissement_bdd seb
                    GROUP BY seb.cdpdt) as seb ON seb.cdpdt = pan.code_produit;
$$;

alter function req_usr_pro_pdt_en_cours_de_num_tl() owner to decitre;