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;
