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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
CREATE OR REPLACE PROCEDURE statstock
IS
utf varchar2(255) := '21416';
j number;
taille varchar2(255) := '';
coloris varchar2(255) := '';
codeBarre varchar2(255) := '';
temp varchar2(255) := '';
cursor getStock IS
SELECT UTF_IDTUTF, UTF_LIB, UTF_DESCR, ART_TYPE, fab_raisoc, utf_idtutf_pere, fou_lib, mar_lib, art_reffab, art_idtart, art_lib, ray_lib, tym_lib, fmm_lib, mam_lib, srt_physique, srt_reserve, NVL((SRT_PHYSIQUE - SRT_RESERVE), 0) as dispo, srt_qtepeniche, srt_qtepropfour, srt_qtepropfourreserv, srt_qtecdefour, srt_qtecdefourreserv, srt_pamp
FROM ref_tg_lien_art_sto_utf
INNER JOIN ref_tg_article ON ART_IDTART = ART_STO_UTF_IDTART
INNER JOIN REF_TG_STOART ON SRT_IDTART = ART_STO_UTF_IDTART
AND SRT_IDTSTO = ART_STO_UTF_IDTSTO
AND SRT_IDTUTF = ART_STO_UTF_IDTUTF
INNER JOIN ref_tg_unitefonctionnelle ON UTF_IDTUTF = ART_STO_UTF_IDTUTF
INNER JOIN ref_op_fabricant ON art_idtfab = fab_idtfab
INNER JOIN ref_op_lien_fou_ucd_art_tarif ON fat_idtart = art_idtart
AND fat_ordre = 1
AND fat_valide = 1
AND fat_datedeb <= SYSDATE
AND (fat_datefin >= SYSDATE OR fat_datefin IS NULL)
INNER JOIN ref_tg_fournisseur ON fou_idtfou = fat_idtfou
LEFT JOIN ref_op_marque ON art_idtmar = mar_idtmar
LEFT JOIN ref_op_rayon ON art_idtray = ray_idtray
LEFT JOIN ref_op_typemonture ON art_idttym = tym_idttym
LEFT JOIN ref_op_famillemonture ON art_idtfmm = fmm_idtfmm
LEFT JOIN ref_op_materiaumonture ON art_idtmam = mam_idtmam
WHERE art_sto_utf_idtutf IN(utf)
AND art_type IN ('MON', 'DIV')
AND (art_datefinvie >= SYSDATE OR art_datefinvie IS NULL)
AND fmm_lib IN ('Percée');
BEGIN
dbms_output.enable(10000000000000);
dbms_output.put_line( 'Code entité' || ';' || 'Lib. Entité' || ';' || 'Type Art.' || ';' || 'Fab.' || ';' || 'Fou.' || ';' || 'Marque' || ';' || 'Réf. Fab.' || ';' || 'Réf. PVO' || ';' || 'Désignation' || ';' || 'Rayon' || ';' || 'Type Monture' || ';' || 'Famille' || ';' || 'Taille' || ';' || 'Coloris' || ';' || 'Matériau' || ';' || 'Code-Barres' || ';' || 'Qté phys.' || ';' || 'Qté réservée' || ';' || 'Qté dispo' || ';' || 'Qté en péniche' || ';' || 'Prop. cde qté' || ';' || 'Prop cde réservée' || ';' || 'Cde qté' || ';' || 'Cde réservée' || ';' || 'Val.' );
For j in getStock loop
if to_char(j.ART_TYPE) = 'MON' then
SELECT NVL(CAA_ART_LIBVAL, '')
INTO taille
FROM REF_OP_CARA_ARTICLE, REF_OP_LIEN_CAA_ART
WHERE CAA_ART_IDTCAA = CAA_IDTCAA
AND CAA_ART_VALIDE = 1
AND CAA_VALIDE = 1
AND CAA_LIBCOURT='TAILLEMON'
AND CAA_ART_IDTART= j.art_idtart;
else
taille := '';
end if;
begin
SELECT CAA_ART_LIBVAL
INTO coloris
FROM REF_OP_CARA_ARTICLE, REF_OP_LIEN_CAA_ART
WHERE CAA_ART_IDTCAA = CAA_IDTCAA
AND CAA_ART_VALIDE = 1
AND CAA_VALIDE = 1
AND CAA_LIBCOURT='COLORIMON'
AND CAA_ART_IDTART = j.art_idtart;
exception
when no_data_found then
coloris := '';
end;
begin
SELECT MAX(PRD_IDTPRD)
INTO codeBarre
FROM REF_OP_PRODUIT
WHERE PRD_VALIDE = 1
AND PRD_DATEDEB <= SYSDATE
AND (PRD_DATEFIN >= SYSDATE OR PRD_DATEFIN IS NULL)
AND PRD_IDTART = j.art_idtart
AND PRD_IDTUTF = j.UTF_IDTUTF;
exception
when no_data_found then
codeBarre := '';
end;
if codebarre IS NULL or codebarre = '' then
begin
SELECT MAX(PRD_IDTPRD)
INTO codeBarre
FROM REF_OP_PRODUIT
WHERE PRD_VALIDE = 1
AND PRD_DATEDEB <= SYSDATE
AND (PRD_DATEFIN >= SYSDATE OR PRD_DATEFIN IS NULL)
AND PRD_IDTART = j.art_idtart
AND PRD_IDTUTF = j.UTF_IDTUTF_PERE;
exception
when no_data_found then
codeBarre := '';
end;
if codebarre IS NULL or codebarre = '' then
begin
SELECT UTF_IDTUTF_PERE
INTO temp
FROM ref_tg_unitefonctionnelle
WHERE UTF_IDTUTF = j.UTF_IDTUTF_PERE;
begin
SELECT MAX(PRD_IDTPRD)
INTO codeBarre
FROM REF_OP_PRODUIT
WHERE PRD_VALIDE = 1
AND PRD_DATEDEB <= SYSDATE
AND (PRD_DATEFIN >= SYSDATE OR PRD_DATEFIN IS NULL)
AND PRD_IDTART = j.art_idtart
AND PRD_IDTUTF = temp;
exception
when no_data_found then
codeBarre := '';
end;
exception
when no_data_found then
codeBarre := '';
end;
end if;
end if;
dbms_output.put_line( replace(to_char(j.UTF_IDTUTF), ';', ' ') || ';' || replace(to_char(j.UTF_DESCR), ';', ' ') || ';' || replace(to_char(j.ART_TYPE), ';', ' ') || ';' || replace(to_char(j.FAB_RAISOC), ';', ' ') || ';' || replace(to_char(j.fou_lib), ';', ' ') || ';' || replace(to_char(j.mar_lib), ';', ' ') || ';' || replace(to_char(j.art_reffab), ';', ' ') || ';' || replace(to_char(j.art_idtart), ';', ' ') || ';' || replace(to_char(j.art_lib), ';', ' ') || ';' || replace(to_char(j.ray_lib), ';', ' ') || ';' || replace(to_char(j.tym_lib), ';', ' ') || ';' || replace(to_char(j.fmm_lib), ';', ' ') || ';' || replace(taille, ';', ' ') || ';' || replace(coloris, ';', ' ') || ';' || replace(to_char(j.mam_lib), ';', ' ') || ';' || replace(codebarre, ';', ' ') || ';' || replace(to_char(j.srt_physique), ';', ' ') || ';' || replace(to_char(j.srt_reserve), ';', ' ') || ';' || replace(to_char(j.dispo), ';', ' ') || ';' || replace(to_char(j.srt_qtepeniche), ';', ' ') || ';' || replace(to_char(j.srt_qtepropfour), ';', ' ') || ';' || replace(to_char(j.srt_qtepropfourreserv), ';', ' ') || ';' || replace(to_char(j.srt_qtecdefour), ';', ' ') || ';' || replace(to_char(j.srt_qtecdefourreserv), ';', ' ') || ';' || replace(to_char(j.srt_pamp), ';', ' ') );
End Loop;
null;
END; |
Problème PL/SQL Oracle 8
Répondre avec citation
Partager