/* Formatted on 20/07/2018 10:57:18 (QP5 v5.115.810.9015)  extraction fournisseur sql gilles */
SELECT   ORG.E1_DFO_BRANCHE AS CD_BRANCHE,
         ORG.BRANCH_DESCR AS DESCR_BRANCHE,
         ORG.E1_DFO_DIRECTION AS CD_DIRECTION,
         ORG.E1_DFO_DIR_R_DESCR AS DIRECTION,
         CC.E1_BRANCHE AS BRANCHE,
         CC.BUSINESS_UNIT AS SOCIETE,
         CC.E1_NOM_COMM AS NOM_COM_SOCIETE,
         CC.DESCR AS DESCR_SOCIETE,
         CC.E1_FOR_JUR AS FORM_JURIDIQUE,
         CC.ADDRESS1 AS ADDR1_SOC,
         CC.ADDRESS2 AS ADDR2_SOC,
         CC.POSTAL AS POST_SOC,
         CC.CITY AS CITY_SOC,
         CC.COUNTRY AS COUNT_SOC,
         CC.COUNTRY_2CHAR || ' ' || CC.VAT_RGSTRN_ID AS TVA_INTRACOM_SOC,
         A.SETID AS ENTITE,
         AA.E1_SIRET AS SIRET,
         AA.E1_NOM_COMM AS NOM_COM_ENTITE,
         AA.DESCR AS DESCR_ENTITE,
         AA.E1_DATE_FERM AS DATE_FERMETURE,
         AA.E1_ETAB_PRINC AS ETAB_PRINCIPAL,
         AA.ADDRESS1 AS ADDR1_ENTITE,
         AA.ADDRESS2 AS ADDR2_ENTITE,
         AA.POSTAL AS POST_ENTITE,
         AA.CITY AS CITY_ENTITE,
         AA.COUNTRY AS COUNT_ENTITE,
         A.VENDOR_ID AS FOURNISSEUR,
         AP.VCHR_APPRVL_FLG AS FLAG_DEMAT,
         ED.E1_EDI_OPT AS FLAG_EDI,
         (SELECT   DISTINCT A1.E1_PERIM_GEST_EGS
            FROM   PS_E1_ORGA_EGS_TBL A1
           WHERE   A1.BUSINESS_UNIT = A.SETID
                   AND A1.E1_PERIM_GEST_EGS LIKE '%NDF%')
            AS NDF,
         (SELECT   DISTINCT A2.E1_PERIM_GEST_EGS
            FROM   PS_E1_ORGA_EGS_TBL A2
           WHERE   A2.BUSINESS_UNIT = A.SETID
                   AND A2.E1_PERIM_GEST_EGS LIKE '%CFOUR%')
            AS CFOUR,
         CASE
            WHEN (SELECT   COUNT ( * )
                    FROM   PS_PO_HDR HD
                   WHERE       HD.BUSINESS_UNIT = A.SETID
                           AND HD.VENDOR_ID = A.VENDOR_ID
                           AND HD.ORIGIN = 'CST') >= 1
            THEN
               'Y'
            ELSE
               'N'
         END
            AS CSST,
         A.NAME1 AS NOM,
         B.STD_ID_NUM AS SIRET_FOUR,
         ABB.VAT_RGSTRN_ID AS TVA_INTRACOM,
         V1.E1_FOR_JUR AS FORME_JURIDIQUE,
         JU.E1_ENTREPRISE AS ENTREPRISE,
         ADDR.ADDRESS1,
         ADDR.ADDRESS2,
         ADDR.POSTAL,
         ADDR.CITY,
         ADDR.COUNTRY,
         BK.BENEFICIARY_BANK,
         BK.IBAN_ID,
         TMP.FACTURE
  FROM   ((((PS_VENDOR A
                      LEFT OUTER JOIN
                         PS_VENDOR_ID_NBRS B
                      ON (    A.SETID = B.SETID
                          AND A.VENDOR_ID = B.VENDOR_ID
                          AND B.STD_ID_NUM_QUAL = 'SRT'))
                  LEFT OUTER JOIN
                     PS_VDR_FACT_TMP TMP
                  ON (A.SETID = TMP.BUSINESS_UNIT
                      AND A.VENDOR_ID = TMP.VENDOR_ID))
              LEFT OUTER JOIN
                 PS_E1_VND_EDI_PRCS ED
              ON (    ED.E1_EDI_PRCS = 'AP_VCHR'
                  AND ED.SETID = A.SETID
                  AND ED.VENDOR_ID = A.VENDOR_ID))
          LEFT OUTER JOIN
             PS_VNDR_BANK_ACCT BK
          ON (    BK.SETID = A.SETID
              AND BK.VENDOR_ID = A.VENDOR_ID
              AND BK.VNDR_LOC = A.DEFAULT_LOC
              AND BK.DEFAULT_IND = 'Y'
              AND BK.EFFDT =
                    (SELECT   MAX (BK_ED.EFFDT)
                       FROM   PS_VNDR_BANK_ACCT BK_ED
                      WHERE       BK.SETID = BK_ED.SETID
                              AND BK.VENDOR_ID = BK_ED.VENDOR_ID
                              AND BK.VNDR_LOC = BK_ED.VNDR_LOC
                              AND BK_ED.EFFDT <= SYSDATE))),
         PS_E1_BUTBL_GL_ETA AA,
         PS_E1_BUTBL_GL_SOC CC,
         PS_E1_DFO_TREE_ORG ORG,
         (   PS_VENDOR_ADDR ADDR
          LEFT OUTER JOIN
             PS_VNDR_VAT_RGSTRN ABB
          ON (    ADDR.SETID = ABB.SETID
              AND ADDR.VENDOR_ID = ABB.VENDOR_ID
              AND ADDR.COUNTRY = ABB.COUNTRY)),
         PS_E1_VENDOR V1,
         PS_E1_FOR_JUR_TBL JU,
         PS_BUS_UNIT_OPT_AP AP
 WHERE       A.VENDOR_STATUS = 'A'
         AND CC.BUSINESS_UNIT = AA.IU_LEGAL_ENT_BU
         AND AA.E1_COMPTA_ETAB = 'Y'
         AND AA.EFFDT =
               (SELECT   MAX (BB.EFFDT)
                  FROM   PS_E1_BUTBL_GL_ETA BB
                 WHERE   BB.BUSINESS_UNIT = AA.BUSINESS_UNIT
                         AND BB.EFFDT <= SYSDATE)
         AND CC.EFFDT =
               (SELECT   MAX (DD.EFFDT)
                  FROM   PS_E1_BUTBL_GL_SOC DD
                 WHERE   DD.BUSINESS_UNIT = CC.BUSINESS_UNIT
                         AND DD.EFFDT <= SYSDATE)
         AND ORG.SETID = 'GROUP'
         AND AA.BUSINESS_UNIT = ORG.BUSINESS_UNIT
         AND A.SETID = AA.BUSINESS_UNIT
         AND A.SETID = ADDR.SETID
         AND A.VENDOR_ID = ADDR.VENDOR_ID
         AND ADDR.ADDRESS_SEQ_NUM = '1'
         AND ADDR.EFFDT =
               (SELECT   MAX (ADDRR.EFFDT)
                  FROM   PS_VENDOR_ADDR ADDRR
                 WHERE       ADDR.SETID = ADDRR.SETID
                         AND ADDR.VENDOR_ID = ADDRR.VENDOR_ID
                         AND ADDR.ADDRESS_SEQ_NUM = ADDRR.ADDRESS_SEQ_NUM
                         AND ADDRR.EFFDT <= SYSDATE)
         AND A.SETID = AP.SETID
         AND A.SETID = V1.SETID
         AND A.VENDOR_ID = V1.VENDOR_ID
         AND JU.SETID = 'GROUP'
         AND JU.E1_FOR_JUR = V1.E1_FOR_JUR
         AND JU.EFFDT =
               (SELECT   MAX (JU_ED.EFFDT)
                  FROM   PS_E1_FOR_JUR_TBL JU_ED
                 WHERE       JU_ED.SETID = JU.SETID
                         AND JU_ED.E1_FOR_JUR = JU.E1_FOR_JUR
                         AND JU_ED.EFFDT <= SYSDATE)