Bonjour,
merci pour ta réponse. Donc voici le code; par contre, c'est peut-être pas le meilleur code qu'il peut y avoir, mais bon, le problème n'est pas là. Sur la partie de code en C/C tout est bien récupéré jusqu'à dr.CLE_RIB = ...
While MonReader.Read
' On remplit le datarow avec les infos récupérées de la BDD
l_row = Me.RemplirRowAdh(MonReader)
End While
'Fonction RemplirRowAdh
Private Function RemplirRowAdh(ByVal dr_adh As OracleDataReader) As MajMut.MAJ_MUTUELLESRow
'la variable dr est une row de dataset que l'on remplit
Try
[...]
'RIB
dr.TIT_COMPTE = dr_adh.GetValue(dr_adh.GetOrdinal("libtitban")).ToString().Trim
dr.DOM_BANCAIRE = dr_adh.GetValue(dr_adh.GetOrdinal("libdomban")).ToString().Trim
dr.CODE_BANQUE = dr_adh.GetValue(dr_adh.GetOrdinal("codban")).ToString().Trim
dr.CODE_GUICHET = dr_adh.GetValue(dr_adh.GetOrdinal("codgui")).ToString().Trim
dr.NUM_COMPTE = dr_adh.GetValue(dr_adh.GetOrdinal("numcomban")).ToString().Trim
dr.CLE_RIB = dr_adh.GetValue(dr_adh.GetOrdinal("cleribrip")).ToString().Trim
dr.CODPAYSISO = dr_adh.GetValue(dr_adh.GetOrdinal("Codpayiso")).ToString()
dr.CLEIBAN = dr_adh.GetValue(dr_adh.GetOrdinal("cleiban")).ToString()
dr.NUMBBAN = dr_adh.GetValue(dr_adh.GetOrdinal("numbban")).ToString()
dr.CODBIC = dr_adh.GetValue(dr_adh.GetOrdinal("codbic")).ToString()
[...]
Et voici la fonction PL/SQL :
PROCEDURE GetStock(p_dt_trait IN VARCHAR2,
p_compagnie IN VARCHAR2,
P_CURSOR OUT r_cursor) IS
str_cmd VARCHAR2(10000);
str_comp VARCHAR2(200);
BEGIN
str_cmd := '
SELECT ass.codliefam,
ctr.numconadh,
ctr.numadhuti,
max_reg.codcaisec,
max_adr.libvoiadr,
max_adr.libcomadr,
max_adr.codposadr,
max_adr.libburadr,
max_rib.libtitban,
max_rib.libdomban,
max_rib.codban,
max_rib.codgui,
max_rib.numcomban,
max_rib.cleribrip,
per.numinsper,
per.cleinsper,
per.numper,
per.nomper,
per.preper,
to_char((TO_DATE(''01011900'',''ddMMyyyy'') + per.datnaiper),''yyyyMMdd'') datnaiper,
per.numrannai,
per.datdecper,
num.Idexterne id_ext,
prd.codstd,
to_char((TO_DATE(''01011900'',''ddMMyyyy'') + pera.debperass),''yyyyMMdd'') debperass,
pera.finperass,
max_noe.per_valide,
I.Codpayiso,
I.cleiban,
I.numbban,
I.codbic
FROM prdstd prd,
optstd std,
optpro pro,
ibanbic I,
ribpesoc ribp,
contrcol con
-- Jointure sur Numintext
LEFT OUTER JOIN Numintext num ON num.Numobj = con.numsoucon
AND num.Numsysext=3
AND num.Indtypobj=2,
personne per
-- Jointure sur regssper
LEFT OUTER JOIN (
SELECT r.codcaisec,
r.numper
FROM regssper r
INNER JOIN(
SELECT MAX(reg.debregsec)deb,reg.numper
FROM regssper reg
GROUP BY reg.numper
)rr ON rr.deb = r.debregsec
AND rr.numper = r.numper
) max_reg ON max_reg.numper = per.numper
-- Jointure sur adrpesoc
LEFT OUTER JOIN (
SELECT a.numpersoc,
a.libvoiadr,
a.libcomadr,
a.codposadr,
a.libburadr
FROM adrpesoc a
INNER JOIN(
SELECT MAX(adr.numadr)deb,adr.numpersoc
FROM adrpesoc adr
WHERE adr.BOOVALADR <> 0
GROUP BY adr.numpersoc
) aa ON aa.deb = a.numadr
AND aa.numpersoc = a.numpersoc
) max_adr ON max_adr.numpersoc = per.numper
-- Jointure sur ribpesoc
LEFT OUTER JOIN (
SELECT ri.numpersoc,
ri.libtitban,
ri.libdomban,
ri.codban,
ri.codgui,
ri.numcomban,
ri.cleribrip
FROM ribpesoc ri
INNER JOIN(
SELECT MAX(rib.numribrip)deb,rib.numpersoc
FROM ribpesoc rib
WHERE rib.BOOVALRIB <> 0
GROUP BY rib.numpersoc
) rri ON rri.deb = ri.numribrip
AND rri.numpersoc = ri.numpersoc
) max_rib ON max_rib.numpersoc = per.numper
-- Jointure sur perionoe
LEFT OUTER JOIN (
SELECT max(noe.finpernoe)per_valide,
noe.numper
FROM perionoe noe
WHERE TO_DATE(''01011900'',''ddMMyyyy'') + noe.debpernoe <= to_date(''' || p_dt_trait || ''',''dd/MM/yyyy'')
AND noe.debpernoe <> -222222
AND (
noe.finpernoe = 3333333 OR
TO_DATE(''01011900'',''ddMMyyyy'') + noe.finpernoe >= to_date(''' || p_dt_trait || ''',''dd/MM/yyyy'')
)
GROUP BY noe.numper
)max_noe ON max_noe.numper = per.numper,
contradh ctr,
assure ass,
perassur pera
/*INNER JOIN (select MAX(p.debperass)deb ,
p.numconadh
FROM perassur p
GROUP BY p.numconadh
) max_p
ON max_p.deb = pera.debperass
AND max_p.numconadh = pera.numconadh*/
WHERE prd.numpro = std.numprostd
AND std.datfingen = 3333333
AND std.numgrp = 0
AND std.numpro = pro.numpro
AND std.numoptpro = pro.numoptpro
AND pro.numpro = con.numpro
AND con.NUMCONcol = ctr.numconcol
AND (
TO_DATE(''01011900'',''ddMMyyyy'') + ctr.finconadh > to_date(''' || p_dt_trait || ''',''dd/MM/yyyy'') OR
ctr.finconadh = 3333333
)
AND ctr.indvaladh = ''V''
AND ctr.numconadh = pera.numconadh
AND (
TO_DATE(''01011900'',''ddMMyyyy'') + pera.finperass > to_date(''' || p_dt_trait || ''',''dd/MM/yyyy'') OR
pera.finperass = 3333333
)
AND ass.numperass = pera.numperass
AND ass.numperass = per.numper
AND ass.numconadh = pera.numconadh
AND I.NUMOBJ1 = ribp.NUMPERSOC
AND I.INDTYPOBJ = 1
AND I.NUMOBJ2 = ribp.NUMRIBRIP
'
;
IF p_compagnie = 'MMA' THEN
str_comp := 'AND prd.codstd IN (''MMA01ACT'', ''MMA01RET'', ''MMA01EXT'')';
ELSIF p_compagnie = 'MOCEN' THEN
str_comp :'AND prd.codstd ''MOC01ACT''';
ELSIF p_compagnie = 'AGF' THEN
str_comp := 'AND prd.codstd IN (''AGF01ACT'', ''AGF01RET'', ''AGF01EXT'')';
ELSE
RAISE_APPLICATION_ERROR(-22222,'Code Compagnie erroné : ' || TRIM(p_compagnie));
END IF;
str_cmd := str_cmd || str_comp || '
GROUP BY ass.codliefam,
ctr.numconadh,
ctr.numadhuti,
max_reg.codcaisec,
max_adr.libvoiadr,
max_adr.libcomadr,
max_adr.codposadr,
max_adr.libburadr,
max_rib.libtitban,
max_rib.libdomban,
max_rib.codban,
max_rib.codgui,
max_rib.numcomban,
max_rib.cleribrip,
per.numinsper,
per.cleinsper,
per.numper,
per.nomper,
per.preper,
per.datnaiper,
per.numrannai,
per.datdecper,
num.Idexterne,
prd.codstd,
pera.debperass,
pera.finperass,
max_noe.per_valide,
I.Codpayiso,
I.cleiban,
I.numbban,
I.codbic';
OPEN P_CURSOR FOR str_cmd;
END GetStock;