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
|
-- Lecture de la base
dbms_output.put_line('Lecture de la base');
begin
for curPro in
(
select
substr(pro.codpro, 2, 8) codpro,
replace(pro.design3, ';', ' ') libelle,
null codepere,
null monnaie,
null prix1,
null prix2,
null prix3,
'LITRE' unite1,
pru.coefuv coeff1,
pro.coduni unite2,
null coeff2,
null unite3,
null coeff3,
null unite4,
null crit1,
pro.coduni crit2,
pro.codzn8 crit3,
pro.fampro crit4,
pro.codzn2 crit5,
pro128.valzod crit6,
null crit7,
pro.codzn5 crit8,
pro.codzn11 crit9,
pro130.valzod crit10,
pro.codzn3 crit11,
null crit12,
null crit13,
null crit14,
pro.codzn13 crit15,
pro129.valzod crit16
from pro
left outer join pru on pru.codsoc = pro.codsoc and pru.codpro = pro.codpro and pru.coduni = pro.coduni
left outer join zod pro131 on pro131.codsoc = pro.codsoc and pro131.typzod = 'PRO' and pro131.clezod = pro.codpro and pro131.numzod = 131 and pro131.datzod = ' '
left outer join zod pro128 on pro128.codsoc = pro.codsoc and pro128.typzod = 'PRO' and pro128.clezod = pro.codpro and pro128.numzod = 128 and pro128.datzod = ' '
left outer join zod pro130 on pro130.codsoc = pro.codsoc and pro130.typzod = 'PRO' and pro130.clezod = pro.codpro and pro130.numzod = 130 and pro130.datzod = ' '
left outer join zod pro129 on pro129.codsoc = pro.codsoc and pro129.typzod = 'PRO' and pro129.clezod = pro.codpro and pro129.numzod = 129 and pro129.datzod = ' '
/*
where pro.codsoc = 100
and pro.codpro = pro.prostk
and pro.coduni not like 'C%'
and pro.codpro not like '%00'
and nvl(pro131.valzod, 'O') = 'O'
*/
where pro.codsoc = 100
and (substr(pro.codpro, 2) in ('11050030', '11060030', '11070030', '11110000', '11220020', '11240001', '11310030', '11310031', '11320030', '11320031', '11810001', '11840022')
or substr(pro.codpro, 2) in ('11895201', '11895215', '11895220', '11945320', '11955220', '12540020', '12545231', '12545240', '12545330', '12580030', '12615230', '12700020')
or substr(pro.codpro, 2) in ('12880020', '13130001', '13420000', '13450000', '14000001', '14040001', '14110020', '14110030', '14142801', '14142830', '14220001', '14380030')
or substr(pro.codpro, 2) in ('14651001', '14730020', '14935320', '14945320', '14960020', '14960040', '15140001', '15140020', '15140030', '15300030', '15535220', '15565220')
or substr(pro.codpro, 2) in ('15655101', '15655201', '15723320', '15723330', '15730020', '15746220', '16100030', '16260001', '16380030', '16390001', '16450000', '16450001')
or substr(pro.codpro, 2) in ('16470001', '16470013', '16470026', '16515201', '16515215', '16515228', '16515301', '16515320', '16515330', '16516201', '16516220', '16516230')
or substr(pro.codpro, 2) in ('16516301', '16516320', '', '16516330', '16525030', '16640001', '16640002', '16755001', '16810001', '16825301', '16830001', '16845201')
or substr(pro.codpro, 2) in ('16845222', '16845301', '16870001', '16873300', '16873301', '16915201', '16915301', '16940027', '17010030', '17090022', '17205220', '17290130')
or substr(pro.codpro, 2) in ('17290140', '17290630', '17290640', '17292840', '17310101', '17312801', '17452801', '17460101', '17462801', '17510131', '17512801', '17512831')
or substr(pro.codpro, 2) in ('17630001', '17650001', '17670030', '17740030', '17740031', '18002830', '18002840', '18010030', '18060031', '18060032', '18130030', '18140030')
or substr(pro.codpro, 2) in ('18140040', '18145240', '18160001', '18180001', '18180030', '18180040', '18200001', '18200030', '18200040', '18200041', '18380001', '18395201')
or substr(pro.codpro, 2) in ('18395220', '18395222', '18425030', '18620030', '18620031', '18650030', '18650031', '18731830', '18740130', '18741130', '18775230', '18775330')
or substr(pro.codpro, 2) in ('18900030', '18910030', '18920030', '18980030', '18990030', '19010030', '19010040', '19040030', '19040040', '19080013', '19245230', '19440130')
or substr(pro.codpro, 2) in ('19441130', '19450131', '19450231', '19451131', '19451831', '19550001', '19551001', '19551020', '19551030', '19555420', '19585215', '19585228')
or substr(pro.codpro, 2) in ('19585320', '19655201', '19655301', '19830001', '19831001', '19831011', '19831024', '19833320', '19833330', '19875201', '19875251', '19875401')
or substr(pro.codpro, 2) in ('19885330', '19890001', '20600000', '21500000', '21600000', '21900000', '24600000', '31400000', '31500000', '36030030', '36070030', '36130030')
or substr(pro.codpro, 2) in ('36220030', '36380030', '36390030', '36480030', '36490030', '36580031', '36700031', '37010030', '38350030', '38470030', '38500030', '39100000')
or substr(pro.codpro, 2) in ('42000030', '42010030', '42110030', '42200030', '42210030', '43210030', '47620020', '48170030', '58060001', '58065002', '58065301', '60300031')
or substr(pro.codpro, 2) in ('70130001', '70395220', '70420001', '70445001', '70445401', '70455121', '70455330', '70510001', '70520001', '70530001', '70555201', '70555301')
or substr(pro.codpro, 2) in ('70565301', '70620020', '70740030', '70760001', '71540050', '71910020', '71940030', '71950030', '71980020', '72005020', '72015030', '72015230')
or substr(pro.codpro, 2) in ('72090020', '72100020', '72120020', '72140020', '72230014', '72270020', '72280020', '72580030', '72640031', '72720030', '72910001', '73040001')
or substr(pro.codpro, 2) in ('73050020', '73150020', '73250020', '73260020', '73270020', '73340020', '73670030', '73680030', '73910050', '74235020', '74390027', '74430030')
or substr(pro.codpro, 2) in ('74450030', '74450035', '74565120', '74580029', '74580039', '74650001', '74780033', '75250030', '75260030', '75430001', '75650037', '75830001')
or substr(pro.codpro, 2) in ('75910030', '75920030', '75980001', '75980030', '76560034', '76600034', '77181020', '77186030', '77186031', '77187031', '77340034', '77850001')
or substr(pro.codpro, 2) in ('77850031', '77850040', '77880030', '77880040', '77900031', '77920031', '77930031', '77930040', '77960030', '77990002', '77990030', '78000000')
or substr(pro.codpro, 2) in ('78090020', '78130000', '78260020', '78760020', '78770020', '78780020', '78800030', '78810030', '78820030', '78830030', '78840030', '78850030')
or substr(pro.codpro, 2) in ('87202830', '87210030', '87400030', 'PRT AVEC HISO A CLASSER'))
)
loop
dbms_output.put_line('Produit : ' || curPro.codpro);
-- Insertion de la ligne dans le fichier
utl_file.putf(Spoint, '%s;%s;%s;%s;%s;', curPro.codpro, curPro.libelle, curPro.codepere, curPro.monnaie, curPro.prix1);
utl_file.putf(Spoint, '%s;%s;%s;%s;%s;', curPro.prix2, curPro.prix3, curPro.unite1, curPro.coeff1, curPro.unite2);
utl_file.putf(Spoint, '%s;%s;%s;%s;%s;', curPro.coeff2, curPro.unite3, curPro.coeff3, curPro.unite4, curPro.crit1);
utl_file.putf(Spoint, '%s;%s;%s;%s;%s;', curPro.crit2, curPro.crit3, curPro.crit4, curPro.crit5, curPro.crit6);
utl_file.putf(Spoint, '%s;%s;%s;%s;%s;', curPro.crit7, curPro.crit8, curPro.crit9, curPro.crit10, curPro.crit11);
utl_file.putf(Spoint, '%s;%s;%s;%s;%s\n', curPro.crit12, curPro.crit13, curPro.crit14, curPro.crit15, curPro.crit16);
end loop;
dbms_output.put_line('Fin de lecture de la base');
exception
when NO_DATA_FOUND then
dbms_output.put_line('Aucune donnée trouvée');
when OTHERS then
dbms_output.put_line(sqlerrm);
end; |
Partager