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
|
CREATE OR REPLACE VIEW V_MA_VUE
(CD_PROD, ID_DOSS, CD_FOUR, LB_FOUR, CD_EDITEUR, LB_PROD,
LB_PROD_COMP, LB_AUTEUR, CD_CAT, CD_TYPE, CD_ISBN,
NB_PAGES, IMG_PRINC, IMG_SECOND, IMG_LOGO_MARQ, IMG_DETAIL_PROD_1,
IMG_DETAIL_PROD_2, LB_COMMERCIAL, LB_ACCROCHE, LB_DESCRIPTIF, LB_CARACT_TECH_REGL,
ECOPART, LONGUEUR, LARGEUR, HAUTEUR, POIDS,
STOCK_INDISPO_WEB)
AS
SELECT DISTINCT
P.CD_PROD,
DP.ID_DOSS ID_DOSS,
nvl(P.CD_FOUR,' ') CD_FOUR,
nvl(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(F.LB_FOUR),CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') LB_FOUR,
nvl(P.CD_EDIT,' ') CD_EDIT,
nvl(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(P.DESI_PROD),CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') LB_PROD,
nvl(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(P.LB_PROD_COMP),CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') LB_PROD_COMP,
nvl(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(P.LB_AUTE),CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') LB_AUTEUR,
nvl(P.CAT_PROD2,' ') CD_CAT,
P.CAT_PROD CD_TYPE,
nvl(P.CD_ISBN,' ') CD_ISBN,
P.NB_PAGE NB_PAGES,
MAX(case when MAX(DECODE (DP.CD_DESIPIEC, 10, DP.LB_LIEN, '')) IS NULL then NULL else 'http://www.monurl.com/images/' || P.CD_PROD || '_' || MAX(DECODE (DP.CD_DESIPIEC, 10, DP.LB_LIEN, '')) end) as IMG_PRINC,
MAX(case when MAX(DECODE (DP.CD_DESIPIEC, 11, DP.LB_LIEN, '')) IS NULL then NULL else 'http://www.monurl.com/images/' || P.CD_PROD || '_' || MAX(DECODE (DP.CD_DESIPIEC, 11, DP.LB_LIEN, '')) end) as IMG_SECOND,
MAX(case when MAX(DECODE (DP.CD_DESIPIEC, 12, DP.LB_LIEN, '')) IS NULL then NULL else 'http://www.monurl.com/images/' || P.CD_PROD || '_' || MAX(DECODE (DP.CD_DESIPIEC, 12, DP.LB_LIEN, '')) end) as IMG_LOGO_MARQ,
MAX(case when MAX(DECODE (DP.CD_DESIPIEC, 13, DP.LB_LIEN, '')) IS NULL then NULL else 'http://www.monurl.com/images/' || P.CD_PROD || '_' || MAX(DECODE (DP.CD_DESIPIEC, 13, DP.LB_LIEN, '')) end) as IMG_DETAIL_PROD_1,
MAX(case when MAX(DECODE (DP.CD_DESIPIEC, 14, DP.LB_LIEN, '')) IS NULL then NULL else 'http://www.monurl.com/images/' || P.CD_PROD || '_' || MAX(DECODE (DP.CD_DESIPIEC, 14, DP.LB_LIEN, '')) end) as IMG_DETAIL_PROD_2,
nvl(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(D.LB_WEBCIAL),CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') LB_COMMERCIAL,
nvl(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(D.LB_WEBACCR),CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') LB_ACCROCHE,
nvl(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(D.LB_WEBDESC),CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') LB_DESCRIPTIF,
nvl(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(rtrim(D.LB_WEBTECH),CHR(10),''),CHR(13),''),CHR(146),''''),CHR(34),''),CHR(162),''),CHR(236),''),CHR(190),''),CHR(178),''),'¹',''''),CHR(38),'ET'),' ') LB_CARACT_TECH_REGL,
P.MT_ECOP ECOPART,
nvl(D.NM_LONG,0) LONGUEUR,
nvl(D.NM_LARG,0) LARGEUR,
nvl(D.NM_HAUT,0) HAUTEUR,
nvl(D.NM_POID,0) POIDS,
D.NM_STOCDISPO STOCK_INDISPO_WEB
FROM PRODUIT P, FOURNISSEUR F, DOSSIER D, ASSO_OFFRE_PROD A, DOSSIER_PIECE DP
WHERE P.CD_PROD is not null
AND P.CD_FOUR = F.CD_FOUR(+)
AND P.CD_PROD = D.CD_PROD(+)
AND P.CD_PROD = A.CD_PROD(+)
AND D.ID_DOSS = DP.ID_DOSS(+)
AND A.CAMP>=79
GROUP BY
P.CD_PROD, DP.ID_DOSS , P.CD_FOUR, F.LB_FOUR, P.CD_EDIT, P.DESI_PROD, P.LB_PROD_COMP,
P.LB_AUTE, P.CAT_PROD2, P.CAT_PROD, P.CD_ISBN, P.NB_PAGE,
DECODE (DP.CD_DESIPIEC, 10, DP.LB_LIEN, ''), P.CD_PROD,
DECODE (DP.CD_DESIPIEC, 11, DP.LB_LIEN, ''), P.CD_PROD,
DECODE (DP.CD_DESIPIEC, 12, DP.LB_LIEN, ''), P.CD_PROD,
DECODE (DP.CD_DESIPIEC, 13, DP.LB_LIEN, ''), P.CD_PROD,
DECODE (DP.CD_DESIPIEC, 14, DP.LB_LIEN, ''), P.CD_PROD,
D.LB_WEBCIAL, D.LB_WEBACCR, D.LB_WEBDESC, D.LB_WEBTECH,
P.MT_ECOP, D.NM_LONG, D.NM_LARG, D.NM_HAUT,D.NM_POID, D.NM_STOCDISPO |
Partager