1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
| ALTER PROCEDURE [dbo].[sp_ins_tb_OOEP_OBJ_pourcent_art]
@OBJ_NO AS INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DATE_DU_JOUR AS DATETIME
SET @DATE_DU_JOUR = getdate()
TRUNCATE TABLE tb_OOEP_OBJ_pourcent_art
EXEC sp_OOEP_OBJ_Extraire_semaine
INSERT INTO tb_OOEP_OBJ_pourcent_art ( pa_art_cod,
pa_famille,
pa_qte_fam,
pa_obj_no,
pa_pourcentage,
pa_obj_fam_no,
pa_art_prev_oui_non,
pa_art_sem_deb,
pa_qte_art_man,
pa_calcule_le,
pa_modifie_le,
pa_qte_fam_res,
pa_art_lib,
pa_fam_lib,
pa_fam_nom,
pa_fam_genre,
pa_fam_type,
pa_qte_mode_calc,
pa_qte_12_mois_passe,
pa_qte_csa_passe,
pa_pourcent_csa_passe)
SELECT rq_OOEP_OBJ_pourcentage_article.art_cod,
rq_OOEP_OBJ_pourcentage_article.art_famille,
tb_ooep_obj_fam.obj_fam_montant,
tb_ooep_obj_fam.obj_fam_obj_no,
rq_OOEP_OBJ_pourcentage_article.art_pourcentage_de_famille,
tb_ooep_obj_fam.obj_fam_no,
CASE WHEN tb_ooep_obj_fam.obj_fam_prev_oui_non = 1 THEN
1
ELSE
0
END AS prev_oui_non,
CASE WHEN len(rtrim(tb_ooep_objectif.obj_semaine)) = 1 THEN
rtrim(tb_ooep_objectif.obj_annee) + '0' + rtrim(tb_OOEP_objectif.obj_semaine)
ELSE
rtrim(tb_ooep_objectif.obj_annee) + rtrim(tb_OOEP_objectif.obj_semaine)
END AS semaine_debut,
NULL AS qte_art_man,
GETDATE() AS Expr1,
GETDATE() AS Expr2,
NULL AS qte_fam_res,
tb_articles.art_lib,
rq_OOEP_OBJ_pourcentage_article.fam_description,
rq_OOEP_OBJ_pourcentage_article.fam_nom,
rq_OOEP_OBJ_pourcentage_article.fam_genre,
rq_OOEP_OBJ_pourcentage_article.fam_type,
'C' AS mode_calc,
rq_OOEP_OBJ_FINALE.qte_12_mois_passe,
rq_OOEP_OBJ_FINALE.qte_csa_passe,
rq_OOEP_OBJ_FINALE.pourcent_csa_passe
FROM tb_ooep_objectif INNER JOIN tb_ooep_obj_fam ON
tb_ooep_objectif.obj_no = tb_ooep_obj_fam.obj_fam_obj_no
INNER JOIN rq_OOEP_OBJ_pourcentage_article ON
tb_ooep_obj_fam.obj_fam_fa_no = rq_OOEP_OBJ_pourcentage_article.art_famille
INNER JOIN tb_articles ON
rq_OOEP_OBJ_pourcentage_article.art_cod = tb_articles.art_cod
LEFT OUTER JOIN rq_OOEP_OBJ_FINALE ON
rq_OOEP_OBJ_pourcentage_article.art_cod = rq_OOEP_OBJ_FINALE.art_cod
WHERE (tb_ooep_obj_fam.obj_fam_obj_no = @OBJ_NO)
ORDER BY tb_ooep_obj_fam.obj_fam_obj_no, rq_OOEP_OBJ_pourcentage_article.art_famille
END |
Partager