获取供应商相关信息SQL

SELECT ASP.VENDOR_ID AS 供应商ID,
       ASP.VENDOR_NAME AS 供应商名称,
       ASP.SEGMENT1 AS 供应商编号,
       DECODE(ASP.VENDOR_TYPE_LOOKUP_CODE,
              'EMPLOYEE',
              '员工供应商',
              '外部供应商') AS 供应商类型,
       IEB.BANK_NAME AS 银行,
       IEB.BANK_BRANCH_NAME AS 分行,
       IEB.BRANCH_PARTY_ID,
       IEB.BANK_ACCOUNT_NUMBER AS 银行账户,
       (SELECT T.DESCRIPTION
          FROM FND_FLEX_VALUES_VL T, FND_FLEX_VALUE_SETS S
         WHERE 1 = 1
           AND T.FLEX_VALUE_SET_ID = S.FLEX_VALUE_SET_ID
           AND S.FLEX_VALUE_SET_NAME LIKE '%COMPANY'
           AND T.FLEX_VALUE =
               (SELECT SUBSTR(HOU.SHORT_CODE, 1, 6)
                  FROM HR_OPERATING_UNITS HOU
                 WHERE HOU.ORGANIZATION_ID = ASS.ORG_ID)
           AND T.ENABLED_FLAG = 'Y'
           AND SYSDATE < NVL(T.END_DATE_ACTIVE, SYSDATE + 1)) AS 业务实体名称,
       GCC.SEGMENT3 预付款会计科目,
       GCC1.SEGMENT3 负债会计科目
  FROM AP_SUPPLIERS             ASP,
       IBY_EXT_BANK_ACCOUNTS_V  IEB,
       IBY_EXTERNAL_PAYEES_ALL  IEP,
       IBY_PMT_INSTR_USES_ALL   USES,
       IBY_ACCOUNT_OWNERS       IAO,
       AP_SUPPLIER_SITES_ALL    ASS,
       GL_CODE_COMBINATIONS_KFV GCC,
       GL_CODE_COMBINATIONS_KFV GCC1
 WHERE 1 = 1
   AND IEP.EXT_PAYEE_ID = USES.EXT_PMT_PARTY_ID
   AND GCC.CODE_COMBINATION_ID = ASS.PREPAY_CODE_COMBINATION_ID
   AND GCC1.CODE_COMBINATION_ID = ASS.ACCTS_PAY_CODE_COMBINATION_ID
   AND IEP.PAYMENT_FUNCTION = 'PAYABLES_DISB'
   AND USES.INSTRUMENT_ID = IEB.EXT_BANK_ACCOUNT_ID
   AND IEP.PAYEE_PARTY_ID = ASP.PARTY_ID
   AND ASP.ENABLED_FLAG = 'Y'
   AND ASP.SUMMARY_FLAG = 'N'
   AND IEP.PARTY_SITE_ID IS NULL
   AND IEP.SUPPLIER_SITE_ID IS NULL
   AND IEP.ORG_ID IS NULL
   AND (ASP.END_DATE_ACTIVE IS NULL OR ASP.END_DATE_ACTIVE > SYSDATE)
   AND (ASS.INACTIVE_DATE IS NULL OR ASS.INACTIVE_DATE > SYSDATE)
   AND ASS.VENDOR_ID = ASP.VENDOR_ID
   AND USES.INSTRUMENT_TYPE = 'BANKACCOUNT'
   AND IAO.ACCOUNT_OWNER_PARTY_ID = ASP.PARTY_ID
   AND IAO.EXT_BANK_ACCOUNT_ID(+) = IEB.EXT_BANK_ACCOUNT_ID
 ORDER BY ASP.VENDOR_TYPE_LOOKUP_CODE;

《获取供应商相关信息SQL》上的一个想法

发表回复

您的电子邮箱地址不会被公开。