Djourdain

SELECT  lp.numcdebes, 
            lp.numlgnbes, 
            lp.cdpdt, 
            ISNULL(lp.qteservie,0), 
            SUM(ISNULL(la.qterecptmag,0)), 
            c.cdorigcde,		 
            c.cdtyppricde	
    FROM	cde_bes_client c,lgn_cde_bes_client_pdt lp,lgn_appro la
    WHERE c.cdorigcde = 'CLP'
            AND cdcdeexp = 0 

Djourdain

/* *************************************************************** *\
*	Date de modif : 11/08/2011		Auteur : LB						*
*	Sujet : Si produit soldé, on récupère son cdtypvente si possible*
=====================================================================
*	Date de modif : 27/01/2012		Auteur : DJ						*
*	Sujet : Récupération de l'ecotaxe pour chaque ligne produit 	*
=====================================================================
*	Date de modif : 11/04/2012 		Auteur : GB						*
*	Sujet : cdcompta passe en varchar 8								*
=====================================================================

Djourdain

SELECT f_drop_function('calcul_ca_insert_calculc_jourj');
CREATE FUNCTION calcul_ca_insert_calculc_jourj(
   v_cdsite   CHARACTER VARYING,
   v_dtdeb    TIMESTAMP WITHOUT TIME ZONE,
   v_dtfin    TIMESTAMP WITHOUT TIME ZONE,
   v_e_cdoper CHARACTER VARYING,
   v_e_type   CHARACTER VARYING,
   v_e_rech   INTEGER)
   RETURNS VOID
LANGUAGE plpgsql

Djourdain

CREATE OR REPLACE FUNCTION calcul_ca(v_dtdebut TIMESTAMP WITHOUT TIME ZONE, v_dtfin TIMESTAMP WITHOUT TIME ZONE, v_nb_jour_a_enlever INTEGER,
                                     v_e_typestat CHARACTER VARYING, v_e_cdoper CHARACTER VARYING, v_type_select INTEGER) RETURNS VOID
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_dtdebut_n_1           TIMESTAMP;
    v_dtfin_n_1             TIMESTAMP;
    v_max_dtvaleur          TIMESTAMP;
    v_max_dtvaleur_n_1      TIMESTAMP;

Djourdain

SELECT * FROM f_drop_function('calcul_ca_insert_calculca_jour_nonj');
create function calcul_ca_insert_calculca_jour_nonj(v_dtdebut timestamp without time zone, v_dtfin timestamp without time zone, v_e_cdoper character varying, v_e_type character varying) returns void
LANGUAGE plpgsql
AS $$
begin

if v_e_type = 'N' then 
			insert INTO tempdb.calcul_ca_n(
			cdoper,			cdsite,			dtvaleur,			jour,			ca_net_HT,			panier,			nb_tickets,			nb_chq_cad,			ca_net_HT_cca,			ca_net_TTC,			ca_brut_TTC,
			ca_brut_HT,			ca_brut_HT_lib,			ca_brut_HT_pap,			qte,			qte_lib,			qte_pap,			nbcartesach,			mtcartesach,			nbcartesrenouv,			mtcartesrenouv,

Djourdain

SELECT f_drop_function('calcul_ca_insert_calculc_jourj');
CREATE FUNCTION calcul_ca_insert_calculc_jourj(
	v_cdsite   CHARACTER VARYING,
	v_dtdeb    TIMESTAMP WITHOUT TIME ZONE,
	v_dtfin    TIMESTAMP WITHOUT TIME ZONE,
	v_e_cdoper CHARACTER VARYING,
	v_e_type   CHARACTER VARYING,
	v_e_rech   INTEGER)
	RETURNS VOID
LANGUAGE plpgsql

Djourdain

SELECT TRIM(f.cdfam) as code_famille,
       TRIM(ssf.cdssfam) as code_rayon,
       TRIM(ssf.libssfam) as libelle_rayon,
       NULLIF(f.cdtva,'E') as code_tva
FROM famille f
INNER JOIN sous_famille sf
    ON sf.cdfam = f.cdfam
INNER JOIN sous_sous_famille ssf
    ON ssf.cdsfam = sf.cdsfam
    AND ssf.libssfam IS NOT NULL

Djourdain

WITH COLLECTION_SAUVE AS (
        SELECT numcollec, MAX(cdmaj) AS cdmaj, MAX(dtsave)
        FROM ever_collection_sauve ecs
        WHERE ecs.dtsave >= :dateDernierTraitement
        GROUP BY numcollec
    )
SELECT DISTINCT ecs.numcollec as numero_collection,
                TRIM(c.nomcollec) AS libelle_collection,
                COALESCE(cf.code_produit_fdn,'LIVRE') AS code_produit,
                COALESCE(fdc.numero_sequence,0)::VARCHAR AS numero_sequence,

Djourdain

WITH EDITEUR_SAUVE AS
         (
             SELECT cdeditr, MAX(dtsave), max(cdmaj) AS cdmaj FROM ever_editeur_sauve
             WHERE nummarqueeditoriale IS NULL
               AND dtsave >= :dateDernierTraitement
             GROUP BY cdeditr
         ),
CATEGORIE_EDITEUR AS (
    SELECT string_agg( DISTINCT code_produit_fdn,',') AS categorie, es.cdeditr
    FROM  EDITEUR_SAUVE es

Djourdain

          WITH FAMILLE_SAUVE AS(
        SELECT cdfam, MAX(cdmaj) AS cdmaj, MAX(dtsave)
            FROM ever_famille_sauve
            WHERE dtsave >= :dateDernierTraitement
            GROUP BY cdfam
        )
        SELECT efs.cdfam as code_famille,
               f.libfam as libelle_famille,
               m.id_centre_profit AS centre_profit,
               efs.cdmaj = 'S' AS a_supprimer