Discussion :

[mina-info]

Bonjour tous le monde

Mon client souhaite avoir la liste de tous les fournisseurs créés dans la base de données (Le Système est un ERP : Oracle e-Business Suite 12.1.3).

J'ai fait une requete qui pourrait me ramener les informations fournisseur demandées par mon client (Vendor name, Site, Compte liability, compte PRepayment, Contact, cordonnées bancaire.....etc)

Je vous explique le problème : quand je fait un select sur la table fournisseur, je trouve 958 fournisseurs.
Alors que la requete que j ai faite me donne 382 fournisseurs.........

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
select * from AP_SUPPLIERS where VENDOR_NAME = 'ALGERIAN ENGINEERING SERVICES COMPANY -ALGESCO- SPA';
SELECT  aps.vendor_name "vendor name",
        aps.VENDOR_NAME_ALT "Altenate Name",
        aps.SEGMENT1 "Supplier Number",
        aps.VENDOR_TYPE_LOOKUP_CODE "Vendor Type",
        aps.ATTRIBUTE2 "NUM REGISTRE DE COMMERCE",
        aps.NUM_1099 "Identification Fiscal",
        apss.vendor_site_code "vendor site code",
        apss.COUNTRY "country",
        apss.ADDRESS_LINE1 "ADDRESS LINE1",
        apss.ADDRESS_LINE2 "ADDRESS LINE2",
        apss.ADDRESS_LINE3 "ADDRESS LINE3",
        apss.CITY "CITY",
        apss.ZIP "POSTAL CODE",
        b1.bank_name "name bank",
        b2.bank_branch_name "bank branch name",
        b3.BANK_ACCOUNT_NUM "bank account number",
        b3.CURRENCY_CODE "Currency",
        T.NAME "TERM",
        aps.INVOICE_CURRENCY_CODE as "Invoice Currency",
        aps.PAYMENT_CURRENCY_CODE as "Payment Currency",
        A3.SEGMENT1 as compagnie,
        A3.SEGMENT2 as gisement,
        A3.SEGMENT3 as compte_comptable,
        A3.SEGMENT4 as activite,
        A3.SEGMENT5 as compte_analytique,
        A3.SEGMENT6 as nature_operation,
        A3.SEGMENT7 as partenaire,
        aps.ALLOW_AWT_FLAG as "IBS",
        apss.EMAIL_ADDRESS "ADRESS EMAIL",
        apss.PHONE "PHONE NUMBER"
       -- O.PAYMENT_METHOD_CODE "Payment Method"
FROM    ap_suppliers aps,
        ap_supplier_sites_all apss,
        apps.iby_ext_bank_accounts b3,
        apps.iby_account_owners b4,
        apps.iby_ext_banks_v b1,
        apps.iby_ext_bank_branches_v b2,
        GL_CODE_COMBINATIONS A3,
        AP_TERMS T
       -- IBY_EXT_PARTY_PMT_MTHDS O,
       -- iby_external_payees_all IE        
WHERE   aps.vendor_id = apss.vendor_id       
AND  b4.account_owner_party_id = aps.party_id
AND  b3.ext_bank_account_id = b4.ext_bank_account_id
AND  b1.bank_party_id = b2.bank_party_id
AND  b3.branch_id = b2.branch_party_id
AND  b3.bank_id = b1.bank_party_id
--AND  apss.ACCTS_PAY_CODE_COMBINATION_ID=A3.CODE_COMBINATION_ID 
AND apss.PREPAY_CODE_COMBINATION_ID=A3.CODE_COMBINATION_ID
--AND Z.PARTY_SITE_ID=apss.PARTY_SITE_ID
AND apss.TERMS_ID= T.TERM_ID
--AND MP.EXT_PMT_PARTY_ID=aps.VENDOR_ID
--AND IE.SUPPLIER_SITE_ID = apss.VENDOR_SITE_ID
--AND IE.EXT_PAYEE_ID = O.EXT_PMT_PARTY_ID
ORDER by  aps.VENDOR_id asc;

J'ai essayé de comprendre le problème, et la je me suis rendue compte que le problème est au niveau des cordonnées bancaire
Je m'explique : si un fournisseur n'a pas de cordonnées bancaire dans ce cas, au niveau de la table de la banque y a pas traçabilité de ce fournisseur !!

Donc le total : j'ai 958 fournisseurs dont 576 n'ont pas de cordonnées bancaire .

La question est : Comment je dois m'y prendre pour avoir le résultat pour tout les fournisseur ?

Merci :p

[bstevy]

normalisez vos jointures et utilisez des left join pour tout récupérer.


http://sqlpro.developpez.com/cours/s...ntures/#LIII-C

[vttvolant]

essaies ce qui suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
SELECT  aps.vendor_name "vendor name",
        aps.VENDOR_NAME_ALT "Altenate Name",
        aps.SEGMENT1 "Supplier Number",
        aps.VENDOR_TYPE_LOOKUP_CODE "Vendor Type",
        aps.ATTRIBUTE2 "NUM REGISTRE DE COMMERCE",
        aps.NUM_1099 "Identification Fiscal",
        apss.vendor_site_code "vendor site code",
        apss.COUNTRY "country",
        apss.ADDRESS_LINE1 "ADDRESS LINE1",
        apss.ADDRESS_LINE2 "ADDRESS LINE2",
        apss.ADDRESS_LINE3 "ADDRESS LINE3",
        apss.CITY "CITY",
        apss.ZIP "POSTAL CODE",
        b1.bank_name "name bank",
        b2.bank_branch_name "bank branch name",
        b3.BANK_ACCOUNT_NUM "bank account number",
        b3.CURRENCY_CODE "Currency",
        T.NAME "TERM",
        aps.INVOICE_CURRENCY_CODE as "Invoice Currency",
        aps.PAYMENT_CURRENCY_CODE as "Payment Currency",
        A3.SEGMENT1 as compagnie,
        A3.SEGMENT2 as gisement,
        A3.SEGMENT3 as compte_comptable,
        A3.SEGMENT4 as activite,
        A3.SEGMENT5 as compte_analytique,
        A3.SEGMENT6 as nature_operation,
        A3.SEGMENT7 as partenaire,
        aps.ALLOW_AWT_FLAG as "IBS",
        apss.EMAIL_ADDRESS "ADRESS EMAIL",
        apss.PHONE "PHONE NUMBER"
       -- O.PAYMENT_METHOD_CODE "Payment Method"
FROM    ap_suppliers aps,
        ap_supplier_sites_all apss,
        apps.iby_ext_bank_accounts b3,
        apps.iby_account_owners b4,
        apps.iby_ext_banks_v b1,
        apps.iby_ext_bank_branches_v b2,
        GL_CODE_COMBINATIONS A3,
        AP_TERMS T
       -- IBY_EXT_PARTY_PMT_MTHDS O,
       -- iby_external_payees_all IE        
WHERE   aps.vendor_id = apss.vendor_id(+)       
AND     b4.account_owner_party_id(+) = aps.party_id
AND     b3.ext_bank_account_id(+)    = b4.ext_bank_account_id
AND     b1.bank_party_id = b2.bank_party_id
AND     b3.branch_id = b2.branch_party_id(+)
AND     b3.bank_id   = b1.bank_party_id(+)
--AND  apss.ACCTS_PAY_CODE_COMBINATION_ID=A3.CODE_COMBINATION_ID 
AND apss.PREPAY_CODE_COMBINATION_ID=A3.CODE_COMBINATION_ID(+)
--AND Z.PARTY_SITE_ID=apss.PARTY_SITE_ID
AND apss.TERMS_ID= T.TERM_ID(+)
--AND MP.EXT_PMT_PARTY_ID=aps.VENDOR_ID
--AND IE.SUPPLIER_SITE_ID = apss.VENDOR_SITE_ID
--AND IE.EXT_PAYEE_ID = O.EXT_PMT_PARTY_ID
ORDER by  aps.VENDOR_id asc;